eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

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,
    '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 */

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
, '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 */