Monday 6 July 2020

Query to findout rejected message and records from Payables Open Interface Import program

SELECT aii.invoice_num,aii.invoice_id,           
listagg(decode(flv.meaning, null, null,flv.meaning ||' - '|| flv.description)  ,', ') within group(order by aii.invoice_id) error_message
FROM   apps.ap_invoices_interface aii
, apps.ap_interface_rejections apir
, fnd_lookup_values flv     
WHERE 1 = 1
AND aii.invoice_id = apir.parent_id
AND apir.reject_lookup_code = flv.lookup_code
AND apir.parent_table = 'AP_INVOICES_INTERFACE'
and aii.status ='REJECTED'
AND aii.invoice_id =4080532
group by aii.invoice_num,aii.invoice_id


SELECT aii.invoice_num,aii.invoice_id,
listagg(decode(flv.meaning, null, null,flv.meaning ||' - '|| flv.description) ,', ') within group(order by aii.invoice_id) error_message
FROM   apps.ap_invoices_interface aii
 , apps.ap_invoice_lines_interface aili
 , apps.ap_interface_rejections apir
 , fnd_lookup_values flv       
WHERE 1 = 1
and aii.invoice_id = aili.invoice_id
AND aili.invoice_line_id = apir.parent_id
AND apir.reject_lookup_code = flv.lookup_code
AND apir.parent_table = 'AP_INVOICE_LINES_INTERFACE'
AND aii.status ='REJECTED'
AND aii.invoice_id =4083297
group by aii.invoice_num,aii.invoice_id

No comments:

Post a Comment