R12.2.4 Invocie Import process - APXIIMPT - Payables Open Interface Invoices
Why the Invoice Import looks at the Payment table . This is impacting the performance. I see there is a patch for this on metalink. Looking for some workaround until we test and apply the patch in prod.
SELECT IBY_DOCS_PAYABLE_GT_S.NEXTVAL, 200, AI.INVOICE_ID, APS.PAYMENT_NUM, NVL(AI.PAY_PROC_TRXN_TYPE_CODE, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'EXPENSE REPORT', 'EMPLOYEE_EXP', 'PAYABLES_DOC')) , APS.PAYMENT_METHOD_CODE, APS.GROSS_AMOUNT, NVL(AI.EXCLUSIVE_PAYMENT_FLAG, 'N'), NVL(AI.PAYMENT_FUNCTION, 'PAYABLES_DISB'), AI.INVOICE_DATE, AI.INVOICE_TYPE_LOOKUP_CODE, AI.DESCRIPTION, APS.GROSS_AMOUNT , APS.EXTERNAL_BANK_ACCOUNT_ID, NVL(AI.PARTY_ID, PV.PARTY_ID), NVL(AI.PARTY_SITE_ID, PVS.PARTY_SITE_ID), DECODE(SIGN(AI.VENDOR_SITE_ID), 1, AI.VENDOR_SITE_ID, NULL), AI.LEGAL_ENTITY_ID, AI.ORG_ID , 'OPERATING_UNIT', AI.INVOICE_CURRENCY_CODE, AI.PAYMENT_CURRENCY_CODE, AI.BANK_CHARGE_BEARER , AI.PAYMENT_REASON_CODE , AI.PAYMENT_REASON_COMMENTS, AI.SETTLEMENT_PRIORITY , APS.REMITTANCE_MESSAGE1 , APS.REMITTANCE_MESSAGE2 , APS.REMITTANCE_MESSAGE3 , AI.UNIQUE_REMITTANCE_IDENTIFIER , AI.URI_CHECK_DIGIT , AI.DELIVERY_CHANNEL_CODE , APS.DISCOUNT_DATE, APS.CREATED_BY , SYSDATE , APS.LAST_UPDATED_BY , SYSDATE, 1, APS.IBY_HOLD_REASON, APS.HOLD_FLAG FROM AP_INVOICES_ALL AI, AP_PAYMENT_SCHEDULES_ALL APS, AP_SUPPLIERS PV, AP_SUPPLIER_SITES_ALL PVS WHERE AI.INVOICE_ID = :B2 AND AI.INVOICE_ID = APS.INVOICE_ID AND NVL(:B1 , APS.PAYMENT_NUM) = APS.PAYMENT_NUM AND APS.PAYMENT_STATUS_FLAG IN ('N', 'P') AND APS.CHECKRUN_ID IS NULL