VIEW: APPS.AP_DOCUMENTS_PAYABLE

Source

View Text - Preformatted

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(+)

View Text - HTML Formatted

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(+)