Monday, 22 January 2018

Oracle Apps SQL query to Fetch AP invoice Rejection Reason in AP Interface


Oracle Apps SQL query to Fetch AP invoice Rejection Reason in AP Interface


SELECT PARENT_TABLE, REJECT_LOOKUP_CODE, APIR.CREATION_DATE
 FROM APPS.AP_INTERFACE_REJECTIONS APIR
WHERE PARENT_TABLE = 'AP_INVOICES_INTERFACE' AND PARENT_ID IN
                                                               (SELECT INVOICE_ID
                                                                 FROM APPS.AP_INVOICES_INTERFACE
                                                                WHERE INVOICE_NUM = :INVOICE_NUM)
UNION
SELECT PARENT_TABLE, REJECT_LOOKUP_CODE, APIR.CREATION_DATE
 FROM APPS.AP_INTERFACE_REJECTIONS APIR
WHERE PARENT_TABLE = 'AP_INVOICE_LINES_INTERFACE' AND PARENT_ID IN (
                                                               SELECT APILI.INVOICE_LINE_ID
                                                                 FROM APPS.AP_INVOICES_INTERFACE APII,
                                                                   APPS.AP_INVOICE_LINES_INTERFACE APILI WHERE INVOICE_NUM = :INVOICE_NUM
                                                                   AND APILI.INVOICE_ID=APII.INVOICE_ID
                                                               )
ORDER BY CREATION_DATE DESC

0 comments:

Post a Comment