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

This sql query help you to get Ap invoice interface rejection records. You can get complete details for the rejected records using this sql query. It helps you to understand the invoice rejection reason in the AP invoice interface and can work on this. We cannot get those details in oracle apps but using this sql query we can get the rejected records with the detailed reason for the rejection. This is quite useful in the Payables Invoice 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


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

0 comments:

Post a Comment