[Home] [Help]
View: 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
,
AI.EXCLUSIVE_PAYMENT_FLAG
,
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 */
NULL
,
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
,
AC.DOC_SEQUENCE_ID
,
AC.DOC_SEQUENCE_VALUE
,
AC.DOC_CATEGORY_CODE
,
NULL
,
HZ.LOCATION_ID
,
AC.ATTRIBUTE_CATEGORY
,
AC.ATTRIBUTE1
,
AC.ATTRIBUTE2
,
AC.ATTRIBUTE3
,
AC.ATTRIBUTE4
,
AC.ATTRIBUTE5
,
AC.ATTRIBUTE6
,
AC.ATTRIBUTE7
,
AC.ATTRIBUTE8
,
AC.ATTRIBUTE9
,
AC.ATTRIBUTE10
,
AC.ATTRIBUTE11
,
AI.ATTRIBUTE12
,
AC.ATTRIBUTE13
,
AC.ATTRIBUTE14
,
AC.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 7535348 */
NVL(ASUP1.PARTY_ID
, AC.PARTY_ID) REMIT_PAYEE_PARTY_ID
,
NVL(PVS1.PARTY_SITE_ID
, AC.PARTY_SITE_ID) REMIT_PARTY_SITE_ID
,
NVL(AC.REMIT_TO_SUPPLIER_SITE_ID
, AC.VENDOR_SITE_ID) REMIT_SUPPLIER_SITE_ID
,
NVL(ASUP1.PARTY_ID
, AC.PARTY_ID) REMIT_BENEFICIARY_PARTY
,
NVL(AC.RELATIONSHIP_ID
, -1) RELATIONSHIP_ID
, /* BUG 7535348 */
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*/
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
,
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
AND AI.PARTY_SITE_ID = HZ.PARTY_SITE_ID(+)
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
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 */
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
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'))
,
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
,
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.LEGAL_ENTITY_ID
,
ASI.ORG_ID
,
NVL(FV.PAYEE_ORG_TYPE
, '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)
,
AI.BANK_CHARGE_BEARER
,
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)
,
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 7535348 */
NVL(ASUP1.PARTY_ID
, AI.PARTY_ID) REMIT_PAYEE_PARTY_ID
,
NVL(PVS1.PARTY_SITE_ID
, AI.PARTY_SITE_ID) REMIT_PARTY_SITE_ID
,
NVL(AI.REMIT_TO_SUPPLIER_SITE_ID
, AI.VENDOR_SITE_ID) REMIT_SUPPLIER_SITE_ID
,
NVL(ASUP1.PARTY_ID
, AI.PARTY_ID) REMIT_BENEFICIARY_PARTY
,
NVL(AI.RELATIONSHIP_ID
, -1) RELATIONSHIP_ID
,
/* BUG 7535348 */
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*/
FROM AP_INVOICES_ALL AI
,
AP_SELECTED_INVOICES_ALL ASI
,
AP_INV_SELECTION_CRITERIA_ALL AISC
,
AP_PAYMENT_SCHEDULES_ALL APS
,
HZ_PARTY_SITES HZ
,
FV_TPP_ASSIGNMENTS_V FV
,
AP_SUPPLIERS ASUP
,
AP_SUPPLIER_SITES_ALL PVS
,
/* BUG 7535348 */
AP_SUPPLIERS ASUP1
,
AP_SUPPLIER_SITES_ALL PVS1
/* BUG 7535348 */
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
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
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 */
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'))
,
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
,
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.LEGAL_ENTITY_ID
,
ASI2.ORG_ID
,
NVL(FV.PAYEE_ORG_TYPE
, '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)
,
AI.BANK_CHARGE_BEARER
,
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)
,
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 7535348 */
NVL(ASUP1.PARTY_ID
, AI.PARTY_ID) REMIT_PAYEE_PARTY_ID
,
NVL(PVS1.PARTY_SITE_ID
, AI.PARTY_SITE_ID) REMIT_PARTY_SITE_ID
,
NVL(AI.REMIT_TO_SUPPLIER_SITE_ID
, AI.VENDOR_SITE_ID) REMIT_SUPPLIER_SITE_ID
,
NVL(ASUP1.PARTY_ID
, AI.PARTY_ID) REMIT_BENEFICIARY_PARTY
,
NVL(AI.RELATIONSHIP_ID
, -1) RELATIONSHIP_ID
,
/* BUG 7535348 */
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*/
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
,
HZ_PARTY_SITES HZ
,
FV_TPP_ASSIGNMENTS_V FV
,
AP_SUPPLIERS ASUP
,
AP_SUPPLIER_SITES_ALL PVS
,
/* BUG 7535348 */
AP_SUPPLIERS ASUP1
,
AP_SUPPLIER_SITES_ALL PVS1
/* BUG 7535348 */
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
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
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(+)