View: AP_DOCUMENTS_PAYABLE

Product:	AP - Payables
Description:
Implementation/DBA Data:	APPS.AP_DOCUMENTS_PAYABLE

View Text

SELECT NVL(AI.PAY_PROC_TRXN_TYPE_CODE
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, 'EMPLOYEE_EXP'
, 'PAYABLES_DOC'))
, 200
, AC.CHECK_ID
, APS.INVOICE_ID
, APS.PAYMENT_NUM
, AIP.INVOICE_PAYMENT_ID
, NULL
, AI.INVOICE_NUM
, AC.CHECKRUN_NAME
, /* 5982788 */ NVL(AI.PAYMENT_FUNCTION
, 'PAYABLES_DISB')
, AC.CHECK_DATE
, AI.INVOICE_DATE
, AI.INVOICE_TYPE_LOOKUP_CODE
, /* BUG 5170881 */ AI.INVOICE_CURRENCY_CODE
, AI.INVOICE_AMOUNT
, AC.CURRENCY_CODE
, AIP.AMOUNT
, AC.PAYMENT_METHOD_CODE
, 'N' EXCLUSIVE_PAYMENT_FLAG
, /* BUG 12578916 */ AC.PARTY_ID
, AC.PARTY_SITE_ID
, DECODE(SIGN(AC.VENDOR_SITE_ID)
, -1
, NULL
, AC.VENDOR_SITE_ID)
, NULL
, AC.LEGAL_ENTITY_ID
, AC.ORG_ID
, 'OPERATING_UNIT'
, 'N'
, AC.CREATED_BY
, AC.CREATION_DATE
, AC.LAST_UPDATED_BY
, AC.LAST_UPDATE_DATE
, NULL
, 1
, AC.ANTICIPATED_VALUE_DATE
, AP_INVOICES_PKG.GET_PO_NUMBER(AI.INVOICE_ID) PO_NUMBER
, /* BUG 5170881 */ AI.DESCRIPTION
, NULL
, NULL
, (SELECT(0 -SUM(NVL(AMOUNT
, 0)))
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = AI.INVOICE_ID
AND LINE_TYPE_LOOKUP_CODE = 'AWT'
AND AWT_INVOICE_PAYMENT_ID = AIP.INVOICE_PAYMENT_ID ) AMOUNT_WITHHELD
, /*BUG 7245723*/ AIP.DISCOUNT_TAKEN
, DECODE(PVS.ALWAYS_TAKE_DISC_FLAG
, 'Y'
, TRUNC(APS.DUE_DATE)
, DECODE(SIGN(AC.CHECK_DATE - NVL(APS.DISCOUNT_DATE
, AC.CHECK_DATE +1)-1)
, -1
, APS.DISCOUNT_DATE
, DECODE(SIGN(AC.CHECK_DATE - NVL(APS.SECOND_DISCOUNT_DATE
, AC.CHECK_DATE +1)-1)
, -1
, APS.SECOND_DISCOUNT_DATE
, DECODE(SIGN(AC.CHECK_DATE - NVL(APS.THIRD_DISCOUNT_DATE
, AC.CHECK_DATE+1)-1)
, -1
, APS.THIRD_DISCOUNT_DATE
, TRUNC(APS.DUE_DATE))))) DISCOUNT_DATE
, APS.DUE_DATE
, AC.PAYMENT_PROFILE_ID
, BAU.BANK_ACCOUNT_ID
, AC.EXTERNAL_BANK_ACCOUNT_ID
, AC.BANK_CHARGE_BEARER
, AP_PAYMENT_UTIL_PKG.GET_INTEREST_RATE(AC.CHECK_DATE) INTEREST_RATE
, /*BUG 5170881 */ 1
, /* BUG 12840668 */ AI.PAYMENT_REASON_CODE
, AI.PAYMENT_REASON_COMMENTS
, AC.SETTLEMENT_PRIORITY
, AI.REMITTANCE_MESSAGE1
, AI.REMITTANCE_MESSAGE2
, AI.REMITTANCE_MESSAGE3
, AI.UNIQUE_REMITTANCE_IDENTIFIER
, AI.URI_CHECK_DIGIT
, AI.DELIVERY_CHANNEL_CODE
, NULL
, AI.DOC_SEQUENCE_ID
, AI.DOC_SEQUENCE_VALUE
, AI.DOC_CATEGORY_CODE
, NULL
, /* BUG 10184282 */ ( SELECT HPS.LOCATION_ID
FROM HZ_PARTY_SITES HPS /*
WHERE HPS.PARTY_SITE_ID=NVL(PVS1.PARTY_SITE_ID
, PVS.PARTY_SITE_ID) COMMENTED FOR BUG#11728587 */
WHERE HPS.PARTY_SITE_ID = NVL(NVL (PVS1.PARTY_SITE_ID
, PVS.PARTY_SITE_ID)
, AC.PARTY_SITE_ID) /* BUG#12628407 */ ) LOCATION_ID
, AI.ATTRIBUTE_CATEGORY
, AI.ATTRIBUTE1
, AI.ATTRIBUTE2
, AI.ATTRIBUTE3
, AI.ATTRIBUTE4
, AI.ATTRIBUTE5
, AI.ATTRIBUTE6
, AI.ATTRIBUTE7
, AI.ATTRIBUTE8
, AI.ATTRIBUTE9
, AI.ATTRIBUTE10
, AI.ATTRIBUTE11
, AI.ATTRIBUTE12
, AI.ATTRIBUTE13
, AI.ATTRIBUTE14
, AI.ATTRIBUTE15
, DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, 'HR'
, 'TCA')
, DECODE(PVS.VENDOR_SITE_CODE
, 'HOME'
, PVS.VENDOR_SITE_CODE
, 'OFFICE'
, PVS.VENDOR_SITE_CODE
, 'PROVISIONAL'
, PVS.VENDOR_SITE_CODE
, NULL)
, DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, 'Y'
, 'N')
, DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, ASUP.EMPLOYEE_ID
, NULL)
, NULL
, /* BUG 8345877 */ NVL(AC.REMIT_TO_SUPPLIER_ID
, AC.VENDOR_ID) EXT_REMIT_PAYEE_ID
, NVL(ASUP1.PARTY_ID
, AC.PARTY_ID) REMIT_PAYEE_PARTY_ID
, /* NVL(PVS1.PARTY_SITE_ID
, AC.PARTY_SITE_ID) REMIT_PARTY_SITE_ID
, COMMENTED FOR BUG#9677941 */ /* ADDED FOR BUG#9677941 START */ DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, PVS.PARTY_SITE_ID
, NVL(PVS1.PARTY_SITE_ID
, AC.PARTY_SITE_ID) ) REMIT_PARTY_SITE_ID
, /* ADDED FOR BUG#9677941 END */ DECODE(SIGN(NVL(AC.REMIT_TO_SUPPLIER_SITE_ID
, AC.VENDOR_SITE_ID))
, -1
, NULL
, NVL(AC.REMIT_TO_SUPPLIER_SITE_ID
, AC.VENDOR_SITE_ID)) REMIT_SUPPLIER_SITE_ID
, AC.PARTY_ID REMIT_BENEFICIARY_PARTY
, NVL(AC.RELATIONSHIP_ID
, -1) RELATIONSHIP_ID
, /* BUG 8345877 */ AI.GLOBAL_ATTRIBUTE_CATEGORY
, /*BUG7608109*/ AI.GLOBAL_ATTRIBUTE1
, AI.GLOBAL_ATTRIBUTE2
, AI.GLOBAL_ATTRIBUTE3
, AI.GLOBAL_ATTRIBUTE4
, AI.GLOBAL_ATTRIBUTE5
, AI.GLOBAL_ATTRIBUTE6
, AI.GLOBAL_ATTRIBUTE7
, AI.GLOBAL_ATTRIBUTE8
, AI.GLOBAL_ATTRIBUTE9
, AI.GLOBAL_ATTRIBUTE10
, AI.GLOBAL_ATTRIBUTE11
, AI.GLOBAL_ATTRIBUTE12
, AI.GLOBAL_ATTRIBUTE13
, AI.GLOBAL_ATTRIBUTE14
, AI.GLOBAL_ATTRIBUTE15
, AI.GLOBAL_ATTRIBUTE16
, AI.GLOBAL_ATTRIBUTE17
, AI.GLOBAL_ATTRIBUTE18
, AI.GLOBAL_ATTRIBUTE19
, AI.GLOBAL_ATTRIBUTE20
, /*BUG7608109*/ 'N' /*BUG12349626*/
FROM AP_CHECKS_ALL AC
, AP_INVOICES_ALL AI
, AP_INVOICE_PAYMENTS_ALL AIP
, AP_PAYMENT_SCHEDULES_ALL APS
, CE_BANK_ACCT_USES_ALL BAU
, AP_SUPPLIER_SITES_ALL PVS
, /* BUG 10184282 HZ_PARTY_SITES HZ
, */ AP_SUPPLIERS ASUP
, /* BUG 7535348 */ AP_SUPPLIER_SITES_ALL PVS1
, AP_SUPPLIERS ASUP1 /* BUG 7535348 */
WHERE AC.CHECK_ID = AIP.CHECK_ID
AND AIP.INVOICE_ID = AI.INVOICE_ID /*BUG 9814939*/ --AND AI.PARTY_SITE_ID = HZ.PARTY_SITE_ID(+)
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+) /*BUG 9814939*/ --AND AI.PARTY_ID = ASUP.PARTY_ID(+) /* BUG 5161809. BUG 5612834 ADDED OUTER JOIN */
AND AI.VENDOR_ID = ASUP.VENDOR_ID(+) /*BUG 6764075 */ /* BUG 7535348
AND AI.REMIT_TO_SUPPLIER_SITE_ID = PVS1.VENDOR_SITE_ID(+)
AND AI.REMIT_TO_SUPPLIER_ID = ASUP1.VENDOR_ID(+) BUG 7535348 */ /* ADDED FOR BUG 10377540 START */
AND AC.REMIT_TO_SUPPLIER_SITE_ID = PVS1.VENDOR_SITE_ID(+)
AND AC.REMIT_TO_SUPPLIER_ID = ASUP1.VENDOR_ID(+) /* ADDED FOR BUG 10377540 END */
AND AI.INVOICE_ID = APS.INVOICE_ID
AND APS.PAYMENT_NUM = AIP.PAYMENT_NUM
AND AC.CE_BANK_ACCT_USE_ID = BAU.BANK_ACCT_USE_ID
AND AC.ORG_ID = BAU.ORG_ID
AND AI.ORG_ID = AC.ORG_ID /*BUG 9814939*/ /* BUG 10184282
AND NVL(PVS1.PARTY_SITE_ID
, PVS.PARTY_SITE_ID) = HZ.PARTY_SITE_ID */ UNION SELECT NVL(AI.PAY_PROC_TRXN_TYPE_CODE
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, 'EMPLOYEE_EXP'
, 'PAYABLES_DOC'))
, 200
, AISC.CHECKRUN_ID
, ASI.INVOICE_ID
, ASI.PAYMENT_NUM
, NULL
, NULL
, AI.INVOICE_NUM
, AISC.CHECKRUN_NAME
, /* NVL(FV.PAYEE_PAYMENT_FUNCTION
, NVL(AI.PAYMENT_FUNCTION
, 'PAYABLES_DISB'))
, BUG :8691645*/ NVL(AI.PAYMENT_FUNCTION
, 'PAYABLES_DISB')
, AISC.CHECK_DATE
, AI.INVOICE_DATE
, AI.INVOICE_TYPE_LOOKUP_CODE
, /* BUG 5170881 */ AI.INVOICE_CURRENCY_CODE
, AI.INVOICE_AMOUNT
, ASI.PAYMENT_CURRENCY_CODE
, ASI.PAYMENT_AMOUNT
, APS.PAYMENT_METHOD_CODE
, ASI.EXCLUSIVE_PAYMENT_FLAG
, /* BUG 8345877 STARTS NVL(FV.PAYEE_PARTY_ID
, AI.PARTY_ID)
, DECODE(FV.PAYEE_PARTY_ID
, NULL
, AI.PARTY_SITE_ID
, FV.PAYEE_PARTY_SITE_ID)
, DECODE(FV.PAYEE_PARTY_ID
, NULL
, DECODE(SIGN(AI.VENDOR_SITE_ID)
, -1
, NULL
, AI.VENDOR_SITE_ID)
, FV.PAYEE_SUPPLIER_SITE_ID)
, FV.BENEFICIARY_PARTY_ID
, */ AI.PARTY_ID
, AI.PARTY_SITE_ID
, DECODE(SIGN(AI.VENDOR_SITE_ID)
, -1
, NULL
, AI.VENDOR_SITE_ID)
, AI.PARTY_ID
, /* BUG 8345877 ENDS*/ AI.LEGAL_ENTITY_ID
, ASI.ORG_ID
, /* NVL(FV.PAYEE_ORG_TYPE
, 'OPERATING_UNIT')
, BUG :8691645*/ 'OPERATING_UNIT'
, DECODE(AISC.DOCUMENT_REJECTION_LEVEL_CODE
, 'REQUEST'
, 'N'
, 'Y')
, ASI.CREATED_BY
, ASI.CREATION_DATE
, ASI.LAST_UPDATED_BY
, ASI.LAST_UPDATE_DATE
, ASI.LAST_UPDATE_LOGIN
, 1
, AISC.ANTICIPATED_VALUE_DATE
, AP_INVOICES_PKG.GET_PO_NUMBER(AI.INVOICE_ID) PO_NUMBER
, /* BUG 5170881 */ ASI.INVOICE_DESCRIPTION
, NULL
, NULL
, ASI.WITHHOLDING_AMOUNT
, ASI.DISCOUNT_AMOUNT
, ASI.DISCOUNT_DATE
, ASI.DUE_DATE
, AISC.PAYMENT_PROFILE_ID
, AISC.BANK_ACCOUNT_ID
, /* DECODE(FV.PAYEE_PARTY_ID
, NULL
, ASI.EXTERNAL_BANK_ACCOUNT_ID
, FV.PAYEE_EXT_BANK_ACCT_ID)
, BUG :8691645*/ ASI.EXTERNAL_BANK_ACCOUNT_ID
, NVL(AISC.BANK_CHARGE_BEARER
, AI.BANK_CHARGE_BEARER)
, /* BUG 9867779 */ AP_PAYMENT_UTIL_PKG.GET_INTEREST_RATE(AISC.CHECK_DATE) INTEREST_RATE
, /*BUG 5170881*/ ASI.PAYMENT_GROUPING_NUMBER
, AI.PAYMENT_REASON_CODE
, AI.PAYMENT_REASON_COMMENTS
, NVL(AISC.SETTLEMENT_PRIORITY
, AI.SETTLEMENT_PRIORITY) SETTLEMENT_PRIORITY
, /* BUG 7282558 */ AI.REMITTANCE_MESSAGE1
, AI.REMITTANCE_MESSAGE2
, AI.REMITTANCE_MESSAGE3
, AI.UNIQUE_REMITTANCE_IDENTIFIER
, AI.URI_CHECK_DIGIT
, AI.DELIVERY_CHANNEL_CODE
, NULL
, AI.DOC_SEQUENCE_ID
, AI.DOC_SEQUENCE_VALUE
, AI.DOC_CATEGORY_CODE
, NULL
, /* DECODE(FV.PAYEE_PARTY_ID
, NULL
, HZ.LOCATION_ID
, NULL)
, BUG :8691645*/ /* BUG 10184282 */ ( SELECT HPS.LOCATION_ID
FROM HZ_PARTY_SITES HPS /*
WHERE HPS.PARTY_SITE_ID=NVL(PVS1.PARTY_SITE_ID
, PVS.PARTY_SITE_ID) COMMENTED FOR BUG#11728587 */ /*
WHERE HPS.PARTY_SITE_ID = NVL (NVL(PVS2.PARTY_SITE_ID
, PVS1.PARTY_SITE_ID)
, PVS.PARTY_SITE_ID) COMMENTED FOR BUG#12628407 */ /* ADDED FOR BUG#11728587 */
WHERE HPS.PARTY_SITE_ID = NVL ( NVL (NVL (PVS2.PARTY_SITE_ID
, PVS1.PARTY_SITE_ID)
, PVS.PARTY_SITE_ID)
, AI.PARTY_SITE_ID ) /* BUG#12628407 */ ) LOCATION_ID
, AI.ATTRIBUTE_CATEGORY
, AI.ATTRIBUTE1
, AI.ATTRIBUTE2
, AI.ATTRIBUTE3
, AI.ATTRIBUTE4
, AI.ATTRIBUTE5
, AI.ATTRIBUTE6
, AI.ATTRIBUTE7
, AI.ATTRIBUTE8
, AI.ATTRIBUTE9
, AI.ATTRIBUTE10
, AI.ATTRIBUTE11
, AI.ATTRIBUTE12
, AI.ATTRIBUTE13
, AI.ATTRIBUTE14
, AI.ATTRIBUTE15
, DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, 'HR'
, 'TCA')
, DECODE(PVS.VENDOR_SITE_CODE
, 'HOME'
, PVS.VENDOR_SITE_CODE
, 'OFFICE'
, PVS.VENDOR_SITE_CODE
, 'PROVISIONAL'
, PVS.VENDOR_SITE_CODE
, NULL)
, DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, 'Y'
, 'N')
, DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, ASUP.EMPLOYEE_ID
, NULL)
, NULL
, /* 8345877 STARTS NVL(FV.PAYEE_SUPPLIER_ID
, NVL(AI.REMIT_TO_SUPPLIER_ID
, AI.VENDOR_ID)) EXT_REMIT_PAYEE_ID
, NVL(FV.PAYEE_PARTY_ID
, NVL(ASUP1.PARTY_ID
, AI.PARTY_ID)) REMIT_PAYEE_PARTY_ID
, NVL(FV.PAYEE_PARTY_SITE_ID
, NVL(PVS1.PARTY_SITE_ID
, AI.PARTY_SITE_ID)) REMIT_PARTY_SITE_ID
, DECODE(FV.PAYEE_SUPPLIER_SITE_ID
, NULL
, DECODE(SIGN(NVL(AI.REMIT_TO_SUPPLIER_SITE_ID
, AI.VENDOR_SITE_ID))
, -1
, NULL
, NVL(AI.REMIT_TO_SUPPLIER_SITE_ID
, AI.VENDOR_SITE_ID))
, FV.PAYEE_SUPPLIER_SITE_ID) REMIT_SUPPLIER_SITE_ID
, NVL(FV.BENEFICIARY_PARTY_ID
, AI.PARTY_ID) REMIT_BENEFICIARY_PARTY
, DECODE(FV.PAYEE_PARTY_ID
, NULL
, -1
, NVL(AI.RELATIONSHIP_ID
, -1)) RELATIONSHIP_ID
, 8345877 ENDS */ /*BUG:8691645*/ /* NVL(AI.REMIT_TO_SUPPLIER_ID
, AI.VENDOR_ID) EXT_REMIT_PAYEE_ID
, COMMENTED FOR BUG#11728587 */ NVL (NVL(APS.REMIT_TO_SUPPLIER_ID
, AI.REMIT_TO_SUPPLIER_ID)
, AI.VENDOR_ID) EXT_REMIT_PAYEE_ID /* ADDED FOR BUG#11728587 */
, NVL (NVL(ASUP2.PARTY_ID
, ASUP1.PARTY_ID)
, AI.PARTY_ID) REMIT_PAYEE_PARTY_ID
, /* NVL(PVS1.PARTY_SITE_ID
, AI.PARTY_SITE_ID) REMIT_PARTY_SITE_ID
, COMMENTED FOR BUG#9677941 */ /* ADDED FOR BUG#9677941 START */ DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, PVS.PARTY_SITE_ID
, NVL (NVL(PVS2.PARTY_SITE_ID
, PVS1.PARTY_SITE_ID)
, AI.PARTY_SITE_ID) ) REMIT_PARTY_SITE_ID
, /* ADDED FOR BUG#9677941 END */ /* DECODE(SIGN(NVL(AI.REMIT_TO_SUPPLIER_SITE_ID
, AI.VENDOR_SITE_ID))
, -1
, NULL
, NVL(AI.REMIT_TO_SUPPLIER_SITE_ID
, AI.VENDOR_SITE_ID)) REMIT_SUPPLIER_SITE_ID
, COMMENTED FOR BUG#11728587 */ DECODE (SIGN (NVL (NVL(APS.REMIT_TO_SUPPLIER_SITE_ID
, AI.REMIT_TO_SUPPLIER_SITE_ID)
, AI.VENDOR_SITE_ID))
, - 1
, NULL
, NVL (NVL(APS.REMIT_TO_SUPPLIER_SITE_ID
, AI.REMIT_TO_SUPPLIER_SITE_ID)
, AI.VENDOR_SITE_ID) ) REMIT_SUPPLIER_SITE_ID
, /* ADDED FOR BUG#11728587 */ AI.PARTY_ID REMIT_BENEFICIARY_PARTY
, NVL(AI.RELATIONSHIP_ID
, -1) RELATIONSHIP_ID
, AI.GLOBAL_ATTRIBUTE_CATEGORY
, /*BUG7608109*/ AI.GLOBAL_ATTRIBUTE1
, AI.GLOBAL_ATTRIBUTE2
, AI.GLOBAL_ATTRIBUTE3
, AI.GLOBAL_ATTRIBUTE4
, AI.GLOBAL_ATTRIBUTE5
, AI.GLOBAL_ATTRIBUTE6
, AI.GLOBAL_ATTRIBUTE7
, AI.GLOBAL_ATTRIBUTE8
, AI.GLOBAL_ATTRIBUTE9
, AI.GLOBAL_ATTRIBUTE10
, AI.GLOBAL_ATTRIBUTE11
, AI.GLOBAL_ATTRIBUTE12
, AI.GLOBAL_ATTRIBUTE13
, AI.GLOBAL_ATTRIBUTE14
, AI.GLOBAL_ATTRIBUTE15
, AI.GLOBAL_ATTRIBUTE16
, AI.GLOBAL_ATTRIBUTE17
, AI.GLOBAL_ATTRIBUTE18
, AI.GLOBAL_ATTRIBUTE19
, AI.GLOBAL_ATTRIBUTE20
, /*BUG7608109*/ ASI.AFFECTS_REJECTION_LEVEL /*BUG12349626*/
FROM AP_INVOICES_ALL AI
, AP_SELECTED_INVOICES_ALL ASI
, AP_INV_SELECTION_CRITERIA_ALL AISC
, AP_PAYMENT_SCHEDULES_ALL APS
, /* BUG 10184282 HZ_PARTY_SITES HZ
, */ /* FV_TPP_ASSIGNMENTS_V FV
, BUG:8691645*/ AP_SUPPLIERS ASUP
, AP_SUPPLIER_SITES_ALL PVS
, /* BUG 7535348 */ AP_SUPPLIERS ASUP1
, AP_SUPPLIER_SITES_ALL PVS1 /* BUG 7535348 */ /* ADDED FOR BUG#11728587 START */
, AP_SUPPLIERS ASUP2
, AP_SUPPLIER_SITES_ALL PVS2 /* ADDED FOR BUG#11728587 END */
WHERE AI.INVOICE_ID = ASI.INVOICE_ID
AND ASI.CHECKRUN_NAME = AISC.CHECKRUN_NAME
AND APS.INVOICE_ID = ASI.INVOICE_ID
AND APS.PAYMENT_NUM = ASI.PAYMENT_NUM /*BUG 9814939*/ --AND HZ.PARTY_SITE_ID(+) = AI.PARTY_SITE_ID /*AND FV.BENEFICIARY_SUPPLIER_ID(+) = AI.VENDOR_ID
AND FV.BENEFICIARY_SUPPLIER_SITE_ID(+) = AI.VENDOR_SITE_ID BUG:8691645*/ /*BUG 9814939*/ --AND AI.PARTY_ID = ASUP.PARTY_ID(+)
AND AI.VENDOR_ID = ASUP.VENDOR_ID(+) /*BUG 6764075 */
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+) /* BUG 7535348 */
AND AI.REMIT_TO_SUPPLIER_SITE_ID = PVS1.VENDOR_SITE_ID(+)
AND AI.REMIT_TO_SUPPLIER_ID = ASUP1.VENDOR_ID(+) /* BUG 7535348 */ /* BUG 11728587 */
AND APS.REMIT_TO_SUPPLIER_SITE_ID = PVS2.VENDOR_SITE_ID(+)
AND APS.REMIT_TO_SUPPLIER_ID = ASUP2.VENDOR_ID(+) /* BUG 11728587 */ /*BUG 9814939*/ /* BUG 10184282
AND NVL(PVS1.PARTY_SITE_ID
, PVS.PARTY_SITE_ID) = HZ.PARTY_SITE_ID */ UNION SELECT NVL(AI.PAY_PROC_TRXN_TYPE_CODE
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'EXPENSE REPORT'
, 'EMPLOYEE_EXP'
, 'PAYABLES_DOC'))
, 200
, AISC.CHECKRUN_ID
, ASI2.INVOICE_ID
, ASI2.PAYMENT_NUM
, NULL
, NULL
, ASI2.INVOICE_NUM
, /* BUG 5334421 WAS AI.INVOICE_NUM */ AISC.CHECKRUN_NAME
, /* NVL(FV.PAYEE_PAYMENT_FUNCTION
, NVL(AI.PAYMENT_FUNCTION
, 'PAYABLES_DISB'))
, BUG:8691645 */ NVL(AI.PAYMENT_FUNCTION
, 'PAYABLES_DISB')
, AISC.CHECK_DATE
, AI.INVOICE_DATE
, 'INTEREST'
, /*BUG 5334421 WAS AI.INVOICE_TYPE_LOOKUP_CODE */ AI.INVOICE_CURRENCY_CODE
, ASI2.INVOICE_AMOUNT
, /* BUG 5334421 WAS AI.INVOICE_AMOUNT */ ASI2.PAYMENT_CURRENCY_CODE
, ASI2.PAYMENT_AMOUNT
, APS.PAYMENT_METHOD_CODE
, ASI2.EXCLUSIVE_PAYMENT_FLAG
, /* BUG 8345877 STARTS NVL(FV.PAYEE_PARTY_ID
, AI.PARTY_ID)
, DECODE(FV.PAYEE_PARTY_ID
, NULL
, AI.PARTY_SITE_ID
, FV.PAYEE_PARTY_SITE_ID)
, DECODE(FV.PAYEE_PARTY_ID
, NULL
, DECODE(SIGN(AI.VENDOR_SITE_ID)
, -1
, NULL
, AI.VENDOR_SITE_ID)
, FV.PAYEE_SUPPLIER_SITE_ID)
, FV.BENEFICIARY_PARTY_ID
, */ AI.PARTY_ID
, AI.PARTY_SITE_ID
, DECODE(SIGN(AI.VENDOR_SITE_ID)
, -1
, NULL
, AI.VENDOR_SITE_ID)
, AI.PARTY_ID
, /* BUG 8345877 ENDS*/ AI.LEGAL_ENTITY_ID
, ASI2.ORG_ID
, /* NVL(FV.PAYEE_ORG_TYPE
, 'OPERATING_UNIT')
, BUG:8691645 */ 'OPERATING_UNIT'
, DECODE(AISC.DOCUMENT_REJECTION_LEVEL_CODE
, 'REQUEST'
, 'N'
, 'Y')
, ASI2.CREATED_BY
, ASI2.CREATION_DATE
, ASI2.LAST_UPDATED_BY
, ASI2.LAST_UPDATE_DATE
, ASI2.LAST_UPDATE_LOGIN
, 1
, AISC.ANTICIPATED_VALUE_DATE
, AP_INVOICES_PKG.GET_PO_NUMBER(AI.INVOICE_ID) PO_NUMBER
, /* BUG 5170881 */ ASI.INVOICE_DESCRIPTION
, NULL
, NULL
, ASI2.WITHHOLDING_AMOUNT
, ASI2.DISCOUNT_AMOUNT
, ASI2.DISCOUNT_DATE
, ASI2.DUE_DATE
, AISC.PAYMENT_PROFILE_ID
, AISC.BANK_ACCOUNT_ID
, /* DECODE(FV.PAYEE_PARTY_ID
, NULL
, ASI2.EXTERNAL_BANK_ACCOUNT_ID
, FV.PAYEE_EXT_BANK_ACCT_ID)
, BUG:8691645 */ ASI2.EXTERNAL_BANK_ACCOUNT_ID
, NVL(AISC.BANK_CHARGE_BEARER
, AI.BANK_CHARGE_BEARER)
, /* BUG 9867779 */ AP_PAYMENT_UTIL_PKG.GET_INTEREST_RATE(AISC.CHECK_DATE) INTEREST_RATE
, /*BUG 5170881 */ ASI2.PAYMENT_GROUPING_NUMBER
, AI.PAYMENT_REASON_CODE
, AI.PAYMENT_REASON_COMMENTS
, NVL(AISC.SETTLEMENT_PRIORITY
, AI.SETTLEMENT_PRIORITY) SETTLEMENT_PRIORITY
, /* BUG 7282558 */ AI.REMITTANCE_MESSAGE1
, AI.REMITTANCE_MESSAGE2
, AI.REMITTANCE_MESSAGE3
, AI.UNIQUE_REMITTANCE_IDENTIFIER
, AI.URI_CHECK_DIGIT
, AI.DELIVERY_CHANNEL_CODE
, NULL
, AI.DOC_SEQUENCE_ID
, AI.DOC_SEQUENCE_VALUE
, AI.DOC_CATEGORY_CODE
, NULL
, /* DECODE(FV.PAYEE_PARTY_ID
, NULL
, HZ.LOCATION_ID
, NULL)
, BUG:8691645*/ /* BUG 10184282 */ ( SELECT HPS.LOCATION_ID
FROM HZ_PARTY_SITES HPS /*
WHERE HPS.PARTY_SITE_ID = NVL (NVL(PVS2.PARTY_SITE_ID
, PVS1.PARTY_SITE_ID)
, PVS.PARTY_SITE_ID) COMMENTED FOR BUG#12628407 */
WHERE HPS.PARTY_SITE_ID = NVL ( NVL (NVL (PVS2.PARTY_SITE_ID
, PVS1.PARTY_SITE_ID)
, PVS.PARTY_SITE_ID)
, AI.PARTY_SITE_ID ) /* BUG#12628407 */ ) LOCATION_ID
, AI.ATTRIBUTE_CATEGORY
, AI.ATTRIBUTE1
, AI.ATTRIBUTE2
, AI.ATTRIBUTE3
, AI.ATTRIBUTE4
, AI.ATTRIBUTE5
, AI.ATTRIBUTE6
, AI.ATTRIBUTE7
, AI.ATTRIBUTE8
, AI.ATTRIBUTE9
, AI.ATTRIBUTE10
, AI.ATTRIBUTE11
, AI.ATTRIBUTE12
, AI.ATTRIBUTE13
, AI.ATTRIBUTE14
, AI.ATTRIBUTE15
, DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, 'HR'
, 'TCA')
, DECODE(PVS.VENDOR_SITE_CODE
, 'HOME'
, PVS.VENDOR_SITE_CODE
, 'OFFICE'
, PVS.VENDOR_SITE_CODE
, 'PROVISIONAL'
, PVS.VENDOR_SITE_CODE
, NULL)
, DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, 'Y'
, 'N')
, DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, ASUP.EMPLOYEE_ID
, NULL)
, NULL
, /* 8345877 STARTS NVL(FV.PAYEE_SUPPLIER_ID
, NVL(AI.REMIT_TO_SUPPLIER_ID
, AI.VENDOR_ID)) EXT_REMIT_PAYEE_ID
, NVL(FV.PAYEE_PARTY_ID
, NVL(ASUP1.PARTY_ID
, AI.PARTY_ID)) REMIT_PAYEE_PARTY_ID
, NVL(FV.PAYEE_PARTY_SITE_ID
, NVL(PVS1.PARTY_SITE_ID
, AI.PARTY_SITE_ID)) REMIT_PARTY_SITE_ID
, DECODE(FV.PAYEE_SUPPLIER_SITE_ID
, NULL
, DECODE(SIGN(NVL(AI.REMIT_TO_SUPPLIER_SITE_ID
, AI.VENDOR_SITE_ID))
, -1
, NULL
, NVL(AI.REMIT_TO_SUPPLIER_SITE_ID
, AI.VENDOR_SITE_ID))
, FV.PAYEE_SUPPLIER_SITE_ID) REMIT_SUPPLIER_SITE_ID
, NVL(FV.BENEFICIARY_PARTY_ID
, AI.PARTY_ID) REMIT_BENEFICIARY_PARTY
, DECODE(FV.PAYEE_PARTY_ID
, NULL
, -1
, NVL(AI.RELATIONSHIP_ID
, -1)) RELATIONSHIP_ID
, 8345877 ENDS */ /*BUG8691645*/ /* NVL(AI.REMIT_TO_SUPPLIER_ID
, AI.VENDOR_ID) EXT_REMIT_PAYEE_ID
, COMMENTED FOR BUG#11728587 */ NVL (NVL(APS.REMIT_TO_SUPPLIER_ID
, AI.REMIT_TO_SUPPLIER_ID)
, AI.VENDOR_ID) EXT_REMIT_PAYEE_ID /* ADDED FOR BUG#11728587 */
, NVL (NVL(ASUP2.PARTY_ID
, ASUP1.PARTY_ID)
, AI.PARTY_ID) REMIT_PAYEE_PARTY_ID
, /* NVL(PVS1.PARTY_SITE_ID
, AI.PARTY_SITE_ID) REMIT_PARTY_SITE_ID
, COMMENTED FOR BUG#9677941 */ /* ADDED FOR BUG#9677941 START */ DECODE(ASUP.VENDOR_TYPE_LOOKUP_CODE
, 'EMPLOYEE'
, PVS.PARTY_SITE_ID
, NVL (NVL(PVS2.PARTY_SITE_ID
, PVS1.PARTY_SITE_ID)
, AI.PARTY_SITE_ID) ) REMIT_PARTY_SITE_ID
, /* ADDED FOR BUG#9677941 END */ /* DECODE(SIGN(NVL(AI.REMIT_TO_SUPPLIER_SITE_ID
, AI.VENDOR_SITE_ID))
, -1
, NULL
, NVL(AI.REMIT_TO_SUPPLIER_SITE_ID
, AI.VENDOR_SITE_ID)) REMIT_SUPPLIER_SITE_ID
, COMMENTED FOR BUG#11728587 */ DECODE (SIGN (NVL (NVL(APS.REMIT_TO_SUPPLIER_SITE_ID
, AI.REMIT_TO_SUPPLIER_SITE_ID)
, AI.VENDOR_SITE_ID))
, - 1
, NULL
, NVL (NVL(APS.REMIT_TO_SUPPLIER_SITE_ID
, AI.REMIT_TO_SUPPLIER_SITE_ID)
, AI.VENDOR_SITE_ID) ) REMIT_SUPPLIER_SITE_ID
, /* ADDED FOR BUG#11728587 */ AI.PARTY_ID REMIT_BENEFICIARY_PARTY
, NVL(AI.RELATIONSHIP_ID
, -1) RELATIONSHIP_ID
, AI.GLOBAL_ATTRIBUTE_CATEGORY
, /*BUG7608109*/ AI.GLOBAL_ATTRIBUTE1
, AI.GLOBAL_ATTRIBUTE2
, AI.GLOBAL_ATTRIBUTE3
, AI.GLOBAL_ATTRIBUTE4
, AI.GLOBAL_ATTRIBUTE5
, AI.GLOBAL_ATTRIBUTE6
, AI.GLOBAL_ATTRIBUTE7
, AI.GLOBAL_ATTRIBUTE8
, AI.GLOBAL_ATTRIBUTE9
, AI.GLOBAL_ATTRIBUTE10
, AI.GLOBAL_ATTRIBUTE11
, AI.GLOBAL_ATTRIBUTE12
, AI.GLOBAL_ATTRIBUTE13
, AI.GLOBAL_ATTRIBUTE14
, AI.GLOBAL_ATTRIBUTE15
, AI.GLOBAL_ATTRIBUTE16
, AI.GLOBAL_ATTRIBUTE17
, AI.GLOBAL_ATTRIBUTE18
, AI.GLOBAL_ATTRIBUTE19
, AI.GLOBAL_ATTRIBUTE20
, /*BUG7608109*/ ASI.AFFECTS_REJECTION_LEVEL /*BUG12349626*/
FROM AP_INVOICES_ALL AI
, AP_SELECTED_INVOICES_ALL ASI
, AP_INV_SELECTION_CRITERIA_ALL AISC
, AP_PAYMENT_SCHEDULES_ALL APS
, AP_SELECTED_INVOICES_ALL ASI2
, /* BUG 10184282 HZ_PARTY_SITES HZ
, */ /* FV_TPP_ASSIGNMENTS_V FV
, BUG :8691645*/ AP_SUPPLIERS ASUP
, AP_SUPPLIER_SITES_ALL PVS
, /* BUG 7535348 */ AP_SUPPLIERS ASUP1
, AP_SUPPLIER_SITES_ALL PVS1 /* BUG 7535348 */ /* ADDED FOR BUG#11728587 START */
, AP_SUPPLIERS ASUP2
, AP_SUPPLIER_SITES_ALL PVS2 /* ADDED FOR BUG#11728587 END */
WHERE AI.INVOICE_ID = ASI.INVOICE_ID
AND ASI.CHECKRUN_NAME = AISC.CHECKRUN_NAME
AND ASI2.CHECKRUN_NAME = ASI.CHECKRUN_NAME /* 5982788 */
AND APS.INVOICE_ID = ASI.INVOICE_ID
AND APS.PAYMENT_NUM = ASI.PAYMENT_NUM
AND ASI2.ORIGINAL_INVOICE_ID = ASI.INVOICE_ID /*
AND ASI2.PAYMENT_NUM = ASI.PAYMENT_NUM COMMENTED FOR 9840622 */
AND ASI2.ORIGINAL_PAYMENT_NUM = ASI.PAYMENT_NUM /*BUG 9328924*/ /*BUG 9814939*/ --AND AI.PARTY_SITE_ID = HZ.PARTY_SITE_ID(+) /*AND FV.BENEFICIARY_SUPPLIER_ID(+) = AI.VENDOR_ID
AND FV.BENEFICIARY_SUPPLIER_SITE_ID(+) = AI.VENDOR_SITE_ID BUG:8691645 */ /*BUG 9814939*/ --AND AI.PARTY_ID = ASUP.PARTY_ID (+)
AND AI.VENDOR_ID = ASUP.VENDOR_ID(+) /*BUG 6764075 */
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+) /* BUG 7535348 */
AND AI.REMIT_TO_SUPPLIER_SITE_ID = PVS1.VENDOR_SITE_ID(+)
AND AI.REMIT_TO_SUPPLIER_ID = ASUP1.VENDOR_ID(+) /* BUG 11728587 */
AND APS.REMIT_TO_SUPPLIER_SITE_ID = PVS2.VENDOR_SITE_ID(+)
AND APS.REMIT_TO_SUPPLIER_ID = ASUP2.VENDOR_ID(+) /* BUG 11728587 */ /*BUG 9814939*/ /* BUG 10184282
AND NVL(PVS1.PARTY_SITE_ID
, PVS.PARTY_SITE_ID) = HZ.PARTY_SITE_ID */