2011年7月12日星期二

Script to control sequence assignment // Script pour vérifier les affectations des séquences dans Oracle applications

SELECT distinct fds.name,fds.creation_date, fu.user_name, fdsc.name "Category",fda.method_code
FROM FND_DOC_SEQUENCE_ASSIGNMENTS FDA
,FND_DOCUMENT_SEQUENCES FDS
, fnd_doc_sequence_categories fdsc
, FND_USER FU
WHERE
FDA.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID
and fda.category_code = fdsc.code
AND FDS.created_by= fu.user_id
AND FDA.start_date = to_date('01/'||to_char(sysdate-30, 'MM/YY')) -- début période N-1
AND FDA.end_date = last_day(trunc(sysdate-30))-- Fin période N-1
AND not exists (select 1
from FND_DOCUMENT_SEQUENCES FDS2,
FND_DOC_SEQUENCE_ASSIGNMENTS FDA2
WHERE 1=1
AND FDA2.DOC_SEQUENCE_ID = FDS2.DOC_SEQUENCE_ID
AND fda.method_code = fda2.method_code
AND fda.category_code= fda2.category_code
AND fda.set_of_books_id = fda2.set_of_books_id
-- AND length(fds.initial_value) = length(fds2.initial_value)
AND FDA2.start_date = to_date('01/'||to_char(sysdate, 'MM/YY'))-- Début Période N
)
order by fds.name;