Search This Blog

Monday, May 13, 2013

Find invalid invoice distributions for a ‘DIST ACCT INVALID’ hold


Find invalid invoice distributions for a ‘DIST ACCT INVALID’ hold

How to find invalid invoice distributions for a ‘DIST ACCT INVALID’ hold


select INVOICE_DISTRIBUTION_ID,DISTRIBUTION_LINE_NUMBER, d.*
FROM apps.ap_invoice_distributions_all D
where D.INVOICE_ID = 261319
AND D.posted_flag in ('N', 'P')
AND ((EXISTS (select 'x'
from gl_code_combinations C
where D.dist_code_combination_id = C.code_combination_id (+)
and (C.code_combination_id is null
or C.detail_posting_allowed_flag = 'N'
or C.start_date_active > D.accounting_date
or C.end_date_active < D.accounting_date
or C.template_id is not null
or C.enabled_flag <> 'Y'
or C.summary_flag <> 'N'
)))
or
(D.dist_code_combination_id = -1));

select * from  GL_CODE_COMBINATIONS_KFV
where 1=1
and code_combination_id in (select distinct DIST_CODE_COMBINATION_ID
FROM apps.ap_invoice_distributions_all D
where D.INVOICE_ID = 261319)


select START_DATE_ACTIVE,CONCATENATED_SEGMENTS,CODE_COMBINATION_ID from  GL_CODE_COMBINATIONS_KFV
where 1=1
and code_combination_id in (select distinct DIST_CODE_COMBINATION_ID
FROM apps.ap_invoice_distributions_all D
where D.INVOICE_ID = 261319)