AP_DOCUMENTS_PAYABLE

Details

• Schema: FUSION

• Object owner: AP

• Object type: VIEW

Columns

Name

PAY_PROC_TRXN_TYPE_CODE CALLING_APP_ID CALLING_APP_DOC_UNIQUE_REF1 CALLING_APP_DOC_UNIQUE_REF2 CALLING_APP_DOC_UNIQUE_REF3 CALLING_APP_DOC_UNIQUE_REF4 CALLING_APP_DOC_UNIQUE_REF5 CALLING_APP_DOC_REF_NUMBER CALL_APP_PAY_SERVICE_REQ_CODE PAYMENT_FUNCTION PAYMENT_DATE DOCUMENT_DATE DOCUMENT_TYPE DOCUMENT_CURRENCY_CODE DOCUMENT_AMOUNT PAYMENT_CURRENCY_CODE PAYMENT_AMOUNT PAYMENT_METHOD_CODE EXCLUSIVE_PAYMENT_FLAG PAYEE_PARTY_ID PARTY_SITE_ID SUPPLIER_SITE_ID BENEFICIARY_PARTY LEGAL_ENTITY_ID ORG_ID ORG_TYPE INVOICING_ORG_ID INVOICING_ORG_TYPE ALLOW_REMOVING_DOCUMENT_FLAG CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN OBJECT_VERSION_NUMBER ANTICIPATED_VALUE_DATE PO_NUMBER DOCUMENT_DESCRIPTION DOCUMENT_CURRENCY_TAX_AMOUNT DOCUMENT_CURR_CHARGE_AMOUNT AMOUNT_WITHHELD PAYMENT_CURR_DISCOUNT_TAKEN DISCOUNT_DATE PAYMENT_DUE_DATE PAYMENT_PROFILE_ID INTERNAL_BANK_ACCOUNT_ID EXTERNAL_BANK_ACCOUNT_ID BANK_CHARGE_BEARER INTEREST_RATE PAYMENT_GROUPING_NUMBER PAYMENT_REASON_CODE PAYMENT_REASON_COMMENTS SETTLEMENT_PRIORITY REMITTANCE_MESSAGE1 REMITTANCE_MESSAGE2 REMITTANCE_MESSAGE3 UNIQUE_REMITTANCE_IDENTIFIER URI_CHECK_DIGIT DELIVERY_CHANNEL_CODE PAYMENT_FORMAT_CODE DOCUMENT_SEQUENCE_ID DOCUMENT_SEQUENCE_VALUE DOCUMENT_CATEGORY_CODE BANK_ASSIGNED_REF_CODE REMIT_TO_LOCATION_ID ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ADDRESS_SOURCE EMPLOYEE_ADDRESS_CODE EMPLOYEE_PAYMENT_FLAG EMPLOYEE_PERSON_ID EMPLOYEE_ADDRESS_ID PAY_GROUP_CODE DOC_CURRENCY_PAYMENT_AMOUNT EXT_REMIT_PAYEE_ID REMIT_PAYEE_PARTY_ID REMIT_ADDRESS_ID REMIT_SUPPLIER_SITE_ID REMIT_BENEFICIARY_PARTY RELATIONSHIP_ID DOC_CURR_DISCOUNT_TAKEN DOC_CURR_AMOUNT_WITHHELD GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE_DATE1 GLOBAL_ATTRIBUTE_DATE2 GLOBAL_ATTRIBUTE_DATE3 GLOBAL_ATTRIBUTE_DATE4 GLOBAL_ATTRIBUTE_DATE5 GLOBAL_ATTRIBUTE_NUMBER1 GLOBAL_ATTRIBUTE_NUMBER2 GLOBAL_ATTRIBUTE_NUMBER3 GLOBAL_ATTRIBUTE_NUMBER4 GLOBAL_ATTRIBUTE_NUMBER5 BALANCING_SEGMENT_VALUE ROUTING_ATTRIBUTE1 ROUTING_ATTRIBUTE2 ROUTING_ATTRIBUTE3 ROUTING_ATTRIBUTE4 ROUTING_ATTRIBUTE5 SET_OF_BOOKS_ID PAYMENT_MODE_CODE PAYMENT_INSTRUMENT_ID

Query

SQL_Statement

SELECT nvl(ai.pay_proc_trxn_type_code, decode(ai.invoice_type_lookup_code,'EXPENSE REPORT', 'EMPLOYEE_EXP','PAYABLES_DOC')) PAY_PROC_TRXN_TYPE_CODE, 200 CALLING_APP_ID, ac.check_id CALLING_APP_DOC_UNIQUE_REF1, aps.invoice_id CALLING_APP_DOC_UNIQUE_REF2, aps.payment_num CALLING_APP_DOC_UNIQUE_REF3, aip.invoice_payment_id CALLING_APP_DOC_UNIQUE_REF4, NULL CALLING_APP_DOC_UNIQUE_REF5, ai.invoice_num CALLING_APP_DOC_REF_NUMBER, ac.checkrun_name CALL_APP_PAY_SERVICE_REQ_CODE, nvl(ai.payment_function, 'PAYABLES_DISB') PAYMENT_FUNCTION, ac.check_date PAYMENT_DATE, ai.invoice_date DOCUMENT_DATE, ai.invoice_type_lookup_code DOCUMENT_TYPE, ai.invoice_currency_code DOCUMENT_CURRENCY_CODE, ai.invoice_amount DOCUMENT_AMOUNT, ac.currency_code PAYMENT_CURRENCY_CODE, aip.amount PAYMENT_AMOUNT, ac.payment_method_code PAYMENT_METHOD_CODE, ai.exclusive_payment_flag EXCLUSIVE_PAYMENT_FLAG, ac.party_id PAYEE_PARTY_ID, NVL(ac.party_site_id, ai.party_site_id) PARTY_SITE_ID, decode(sign(ac.vendor_site_id),-1,null,ac.vendor_site_id) SUPPLIER_SITE_ID, NULL BENEFICIARY_PARTY, ai.legal_entity_id LEGAL_ENTITY_ID, ac.org_id ORG_ID, 'OPERATING_UNIT' ORG_TYPE, ai.org_id INVOICING_ORG_ID, 'OPERATING_UNIT' INVOICING_ORG_TYPE, 'N' ALLOW_REMOVING_DOCUMENT_FLAG, ac.created_by, ac.creation_date, ac.last_updated_by, ac.last_update_date, NULL LAST_UPDATE_LOGIN, 1 OBJECT_VERSION_NUMBER, ac.anticipated_value_date, NULL PO_NUMBER, ai.description DOCUMENT_DESCRIPTION, NULL DOCUMENT_CURRENCY_TAX_AMOUNT, NULL DOCUMENT_CURR_CHARGE_AMOUNT, (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, aip.discount_taken PAYMENT_CURR_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 PAYMENT_DUE_DATE, ac.payment_profile_id PAYMENT_PROFILE_ID, bau.bank_account_id INTERNAL_BANK_ACCOUNT_ID, ac.external_bank_account_id EXTERNAL_BANK_ACCOUNT_ID, NVL(ac.bank_charge_bearer, ai.bank_charge_bearer) BANK_CHARGE_BEARER, NULL INTEREST_RATE, 1 PAYMENT_GROUPING_NUMBER, ai.payment_reason_code, ai.payment_reason_comments, NVL(ac.settlement_priority, ai.settlement_priority) SETTLEMENT_PRIORITY, aps.remittance_message1, aps.remittance_message2, aps.remittance_message3, ai.unique_remittance_identifier, ai.uri_check_digit, ai.delivery_channel_code, NULL PAYMENT_FORMAT_CODE, ai.Doc_sequence_id DOCUMENT_SEQUENCE_ID, ai.Doc_sequence_value DOCUMENT_SEQUENCE_VALUE, ai.Doc_category_code DOCUMENT_CATEGORY_CODE, NULL BANK_ASSIGNED_REF_CODE, NVL(hz2.location_id,hz.location_id ) REMIT_TO_LOCATION_ID, aps.attribute_category, aps.ATTRIBUTE1, aps.ATTRIBUTE2, aps.ATTRIBUTE3, aps.ATTRIBUTE4, aps.ATTRIBUTE5, aps.ATTRIBUTE6, aps.ATTRIBUTE7, aps.ATTRIBUTE8, aps.ATTRIBUTE9, aps.ATTRIBUTE10, aps.ATTRIBUTE11, aps.ATTRIBUTE12, aps.ATTRIBUTE13, aps.ATTRIBUTE14, aps.ATTRIBUTE15, decode(ai.source, 'EMP_EXPENSE_REPORT', 'HR', 'EMP_CASH_ADVANCE', 'HR','TCA') ADDRESS_SOURCE, NVL(ac.employee_address_code, ai.employee_address_code) EMPLOYEE_ADDRESS_CODE, decode(ai.source, 'EMP_EXPENSE_REPORT', 'Y', 'EMP_CASH_ADVANCE', 'Y', 'N') EMPLOYEE_PAYMENT_FLAG, decode(ai.source, 'EMP_EXPENSE_REPORT', ai.paid_on_behalf_employee_id, 'EMP_CASH_ADVANCE', ai.paid_on_behalf_employee_id, NULL) EMPLOYEE_PERSON_ID, NULL EMPLOYEE_ADDRESS_ID, ai.pay_group_lookup_code pay_group_code, aip.amount_inv_curr DOC_CURRENCY_PAYMENT_AMOUNT, nvl(ac.remit_to_supplier_id, ac.vendor_id) EXT_REMIT_PAYEE_ID, nvl(pzot.party_id, ac.party_id) REMIT_PAYEE_PARTY_ID, nvl(ac.remit_to_address_id, NVL(ac.party_site_id, ai.party_site_id)) REMIT_ADDRESS_ID, decode(ac.remit_to_address_id, NULL, ac.vendor_site_id, NULL) REMIT_SUPPLIER_SITE_ID, ac.party_id REMIT_BENEFICIARY_PARTY, NVL(ac.relationship_id, -1) RELATIONSHIP_ID, aip.discount_taken_inv_curr DOC_CURR_DISCOUNT_TAKEN, (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) DOC_CURR_AMOUNT_WITHHELD, aps.GLOBAL_ATTRIBUTE_CATEGORY, aps.GLOBAL_ATTRIBUTE1, aps.GLOBAL_ATTRIBUTE2, aps.GLOBAL_ATTRIBUTE3, aps.GLOBAL_ATTRIBUTE4, aps.GLOBAL_ATTRIBUTE5, aps.GLOBAL_ATTRIBUTE6, aps.GLOBAL_ATTRIBUTE7, aps.GLOBAL_ATTRIBUTE8, aps.GLOBAL_ATTRIBUTE9, aps.GLOBAL_ATTRIBUTE10, aps.GLOBAL_ATTRIBUTE11, aps.GLOBAL_ATTRIBUTE12, aps.GLOBAL_ATTRIBUTE13, aps.GLOBAL_ATTRIBUTE14, aps.GLOBAL_ATTRIBUTE15, aps.GLOBAL_ATTRIBUTE16, aps.GLOBAL_ATTRIBUTE17, aps.GLOBAL_ATTRIBUTE18, aps.GLOBAL_ATTRIBUTE19, aps.GLOBAL_ATTRIBUTE20, aps.GLOBAL_ATTRIBUTE_DATE1, aps.GLOBAL_ATTRIBUTE_DATE2, aps.GLOBAL_ATTRIBUTE_DATE3, aps.GLOBAL_ATTRIBUTE_DATE4, aps.GLOBAL_ATTRIBUTE_DATE5, aps.GLOBAL_ATTRIBUTE_NUMBER1, aps.GLOBAL_ATTRIBUTE_NUMBER2, aps.GLOBAL_ATTRIBUTE_NUMBER3, aps.GLOBAL_ATTRIBUTE_NUMBER4, aps.GLOBAL_ATTRIBUTE_NUMBER5, NULL BALANCING_SEGMENT_VALUE, ai.ROUTING_ATTRIBUTE1, ai.ROUTING_ATTRIBUTE2, ai.ROUTING_ATTRIBUTE3, ai.ROUTING_ATTRIBUTE4, ai.ROUTING_ATTRIBUTE5, ai.set_of_books_id SET_OF_BOOKS_ID, ac.payment_mode_code, ac.payment_instrument_id 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_POZ_SITE_AND_ASSIGNMENT_V pvs, hz_party_sites hz, POZ_SUPPLIERS asup, hz_party_sites hz2, POZ_SUPPLIERS pzot 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(+) AND ac.remit_to_address_id = hz2.party_site_id(+) 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 NVL(ap_utilities_pkg.get_payment_bu_id(AI.org_id),AI.org_id) = AC.org_id/* X BU Project*/ AND AI.org_id = PVS.BU_ID (+) AND ac.remit_to_supplier_id = pzot.vendor_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(ai.payment_function, 'PAYABLES_DISB'), aisc.check_date, ai.invoice_date, ai.invoice_type_lookup_code, ai.invoice_currency_code, ai.invoice_amount, asi.payment_currency_code, asi.payment_amount, aps.payment_method_code, asi.exclusive_payment_flag, ai.party_id, ai.party_site_id, decode(sign(ai.vendor_site_id),-1,null,ai.vendor_site_id), NULL, ai.legal_entity_id, asi.org_id, 'OPERATING_UNIT', ai.org_id, '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, NULL, NULL, 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, asi.external_bank_account_id, NVL(aisc.bank_charge_bearer, ai.bank_charge_bearer), NULL, asi.payment_grouping_number, ai.payment_reason_code, ai.payment_reason_comments, NVL(aisc.settlement_priority, ai.settlement_priority), aps.remittance_message1, aps.remittance_message2, aps.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, NVL(hz2.location_id,hz.location_id ) REMIT_TO_LOCATION_ID, aps.ATTRIBUTE_CATEGORY, aps.ATTRIBUTE1, aps.ATTRIBUTE2, aps.ATTRIBUTE3, aps.ATTRIBUTE4, aps.ATTRIBUTE5, aps.ATTRIBUTE6, aps.ATTRIBUTE7, aps.ATTRIBUTE8, aps.ATTRIBUTE9, aps.ATTRIBUTE10, aps.ATTRIBUTE11, aps.ATTRIBUTE12, aps.ATTRIBUTE13, aps.ATTRIBUTE14, aps.ATTRIBUTE15, decode(ai.source, 'EMP_EXPENSE_REPORT', 'HR', 'EMP_CASH_ADVANCE', 'HR', 'TCA') ADDRESS_SOURCE, ai.employee_address_code EMPLOYEE_ADDRESS_CODE, decode(ai.source, 'EMP_EXPENSE_REPORT', 'Y', 'EMP_CASH_ADVANCE', 'Y', 'N') EMPLOYEE_PAYMENT_FLAG, decode(ai.source, 'EMP_EXPENSE_REPORT', ai.paid_on_behalf_employee_id, 'EMP_CASH_ADVANCE', ai.paid_on_behalf_employee_id, NULL) EMPLOYEE_PERSON_ID, NULL, ai.pay_group_lookup_code pay_group_code, asi.payment_amount_inv_curr DOC_CURRENCY_PAYMENT_AMOUNT, NVL (aps.remit_to_supplier_id, ai.vendor_id) EXT_REMIT_PAYEE_ID, NVL (pzot2.party_id, ai.party_id) REMIT_PAYEE_PARTY_ID , nvl(aps.remit_to_address_id, ai.party_site_id) REMIT_ADDRESS_ID, decode(aps.remit_to_address_id, NULL, ai.vendor_site_id, NULL) REMIT_SUPPLIER_SITE_ID, ai.party_id REMIT_BENEFICIARY_PARTY, NVL(aps.relationship_id, -1) RELATIONSHIP_ID, asi.discount_taken_inv_curr DOC_CURR_DISCOUNT_TAKEN, asi.wht_amount_inv_curr DOC_CURR_AMOUNT_WITHHELD, aps.GLOBAL_ATTRIBUTE_CATEGORY, aps.GLOBAL_ATTRIBUTE1, aps.GLOBAL_ATTRIBUTE2, aps.GLOBAL_ATTRIBUTE3, aps.GLOBAL_ATTRIBUTE4, aps.GLOBAL_ATTRIBUTE5, aps.GLOBAL_ATTRIBUTE6, aps.GLOBAL_ATTRIBUTE7, aps.GLOBAL_ATTRIBUTE8, aps.GLOBAL_ATTRIBUTE9, aps.GLOBAL_ATTRIBUTE10, aps.GLOBAL_ATTRIBUTE11, aps.GLOBAL_ATTRIBUTE12, aps.GLOBAL_ATTRIBUTE13, aps.GLOBAL_ATTRIBUTE14, aps.GLOBAL_ATTRIBUTE15, aps.GLOBAL_ATTRIBUTE16, aps.GLOBAL_ATTRIBUTE17, aps.GLOBAL_ATTRIBUTE18, aps.GLOBAL_ATTRIBUTE19, aps.GLOBAL_ATTRIBUTE20, aps.GLOBAL_ATTRIBUTE_DATE1, aps.GLOBAL_ATTRIBUTE_DATE2, aps.GLOBAL_ATTRIBUTE_DATE3, aps.GLOBAL_ATTRIBUTE_DATE4, aps.GLOBAL_ATTRIBUTE_DATE5, aps.GLOBAL_ATTRIBUTE_NUMBER1, aps.GLOBAL_ATTRIBUTE_NUMBER2, aps.GLOBAL_ATTRIBUTE_NUMBER3, aps.GLOBAL_ATTRIBUTE_NUMBER4, aps.GLOBAL_ATTRIBUTE_NUMBER5, asi.BALANCING_SEGMENT_VALUE, ai.ROUTING_ATTRIBUTE1, ai.ROUTING_ATTRIBUTE2, ai.ROUTING_ATTRIBUTE3, ai.ROUTING_ATTRIBUTE4, ai.ROUTING_ATTRIBUTE5, ai.set_of_books_id SET_OF_BOOKS_ID, aisc.payment_mode_code, aisc.payment_instrument_id 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, POZ_SUPPLIERS asup, hz_party_sites hz2, POZ_SUPPLIERS pzot2 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 aps.remit_to_address_id = hz2.party_site_id(+) and ai.party_id = asup.party_id(+) and aps.remit_to_supplier_id = pzot2.vendor_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, aisc.checkrun_name, nvl(ai.payment_function, 'PAYABLES_DISB'), aisc.check_date, ai.invoice_date, 'INTEREST', ai.invoice_currency_code, asi2.invoice_amount, asi2.payment_currency_code, asi2.payment_amount, aps.payment_method_code, asi2.exclusive_payment_flag, ai.party_id, ai.party_site_id, decode(sign(ai.vendor_site_id),-1,null,ai.vendor_site_id), NULL, ai.legal_entity_id, asi2.org_id, 'OPERATING_UNIT', ai.org_id, '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, NULL, NULL, 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, asi2.external_bank_account_id, NVL(aisc.bank_charge_bearer, ai.bank_charge_bearer), NULL, asi2.payment_grouping_number, ai.payment_reason_code, ai.payment_reason_comments, NVL(aisc.settlement_priority, ai.settlement_priority), aps.remittance_message1, aps.remittance_message2, aps.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, NVL(hz2.location_id,hz.location_id ) REMIT_TO_LOCATION_ID, aps.ATTRIBUTE_CATEGORY, aps.ATTRIBUTE1, aps.ATTRIBUTE2, aps.ATTRIBUTE3, aps.ATTRIBUTE4, aps.ATTRIBUTE5, aps.ATTRIBUTE6, aps.ATTRIBUTE7, aps.ATTRIBUTE8, aps.ATTRIBUTE9, aps.ATTRIBUTE10, aps.ATTRIBUTE11, aps.ATTRIBUTE12, aps.ATTRIBUTE13, aps.ATTRIBUTE14, aps.ATTRIBUTE15, decode(ai.source, 'EMP_EXPENSE_REPORT', 'HR', 'EMP_CASH_ADVANCE', 'HR', 'TCA') ADDRESS_SOURCE, ai.employee_address_code EMPLOYEE_ADDRESS_CODE, decode(ai.source, 'EMP_EXPENSE_REPORT', 'Y', 'EMP_CASH_ADVANCE', 'Y', 'N') EMPLOYEE_PAYMENT_FLAG, decode(ai.source, 'EMP_EXPENSE_REPORT', ai.paid_on_behalf_employee_id, 'EMP_CASH_ADVANCE', ai.paid_on_behalf_employee_id, NULL) EMPLOYEE_PERSON_ID, NULL, ai.pay_group_lookup_code pay_group_code, asi2.payment_amount_inv_curr DOC_CURRENCY_PAYMENT_AMOUNT, NVL (aps.remit_to_supplier_id, ai.vendor_id) EXT_REMIT_PAYEE_ID , NVL (pzot2.party_id, ai.party_id) REMIT_PAYEE_PARTY_ID , nvl(aps.remit_to_address_id, ai.party_site_id) REMIT_ADDRESS_ID, decode(aps.remit_to_address_id, NULL, ai.vendor_site_id, NULL) REMIT_SUPPLIER_SITE_ID, ai.party_id REMIT_BENEFICIARY_PARTY, NVL(aps.relationship_id, -1) RELATIONSHIP_ID, asi.discount_taken_inv_curr DOC_CURR_DISCOUNT_TAKEN, asi.wht_amount_inv_curr DOC_CURR_AMOUNT_WITHHELD, aps.GLOBAL_ATTRIBUTE_CATEGORY, aps.GLOBAL_ATTRIBUTE1, aps.GLOBAL_ATTRIBUTE2, aps.GLOBAL_ATTRIBUTE3, aps.GLOBAL_ATTRIBUTE4, aps.GLOBAL_ATTRIBUTE5, aps.GLOBAL_ATTRIBUTE6, aps.GLOBAL_ATTRIBUTE7, aps.GLOBAL_ATTRIBUTE8, aps.GLOBAL_ATTRIBUTE9, aps.GLOBAL_ATTRIBUTE10, aps.GLOBAL_ATTRIBUTE11, aps.GLOBAL_ATTRIBUTE12, aps.GLOBAL_ATTRIBUTE13, aps.GLOBAL_ATTRIBUTE14, aps.GLOBAL_ATTRIBUTE15, aps.GLOBAL_ATTRIBUTE16, aps.GLOBAL_ATTRIBUTE17, aps.GLOBAL_ATTRIBUTE18, aps.GLOBAL_ATTRIBUTE19, aps.GLOBAL_ATTRIBUTE20, aps.GLOBAL_ATTRIBUTE_DATE1, aps.GLOBAL_ATTRIBUTE_DATE2, aps.GLOBAL_ATTRIBUTE_DATE3, aps.GLOBAL_ATTRIBUTE_DATE4, aps.GLOBAL_ATTRIBUTE_DATE5, aps.GLOBAL_ATTRIBUTE_NUMBER1, aps.GLOBAL_ATTRIBUTE_NUMBER2, aps.GLOBAL_ATTRIBUTE_NUMBER3, aps.GLOBAL_ATTRIBUTE_NUMBER4, aps.GLOBAL_ATTRIBUTE_NUMBER5, asi2.BALANCING_SEGMENT_VALUE, ai.ROUTING_ATTRIBUTE1, ai.ROUTING_ATTRIBUTE2, ai.ROUTING_ATTRIBUTE3, ai.ROUTING_ATTRIBUTE4, ai.ROUTING_ATTRIBUTE5, ai.set_of_books_id SET_OF_BOOKS_ID, aisc.payment_mode_code, aisc.payment_instrument_id 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, POZ_SUPPLIERS asup, hz_party_sites hz2, POZ_SUPPLIERS pzot2 where ai.invoice_id = asi.invoice_id and asi.checkrun_name = aisc.checkrun_name and asi2.checkrun_name = asi.checkrun_name and aps.invoice_id = asi.invoice_id and aps.payment_num = asi.payment_num and aps.remit_to_address_id = hz2.party_site_id(+) and asi2.original_invoice_id = asi.invoice_id and asi2.original_payment_num = asi.payment_num and ai.party_site_id = hz.party_site_id(+) and ai.party_id = asup.party_id (+) and aps.remit_to_supplier_id = pzot2.vendor_id (+) UNION SELECT nvl( ai.pay_proc_trxn_type_code, decode( ai.invoice_type_lookup_code, 'EXPENSE REPORT', 'EMPLOYEE_EXP', 'PAYABLES_DOC' ) ) pay_proc_trxn_type_code, 200 calling_app_id, ac.check_id calling_app_doc_unique_ref1, aps.invoice_id calling_app_doc_unique_ref2, aps.payment_num calling_app_doc_unique_ref3, aip.invoice_payment_id calling_app_doc_unique_ref4, NULL calling_app_doc_unique_ref5, ai.invoice_num calling_app_doc_ref_number, ac.checkrun_name call_app_pay_service_req_code, nvl( ai.payment_function, 'PAYABLES_DISB' ) payment_function, ac.check_date payment_date, ai.invoice_date document_date, ai.invoice_type_lookup_code document_type, ai.invoice_currency_code document_currency_code, ai.invoice_amount document_amount, ac.currency_code payment_currency_code, aip.amount payment_amount, ac.payment_method_code payment_method_code, ai.exclusive_payment_flag exclusive_payment_flag, ac.party_id payee_party_id, nvl( ac.party_site_id, ai.party_site_id ) party_site_id, decode( sign( ac.vendor_site_id ), - 1, NULL, ac.vendor_site_id ) supplier_site_id, NULL beneficiary_party, ai.legal_entity_id legal_entity_id, ac.org_id org_id, 'OPERATING_UNIT' org_type, ai.org_id invoicing_org_id, 'OPERATING_UNIT' invoicing_org_type, 'N' allow_removing_document_flag, ac.created_by, ac.creation_date, ac.last_updated_by, ac.last_update_date, NULL last_update_login, 1 object_version_number, ac.anticipated_value_date, NULL po_number, ai.description document_description, NULL document_currency_tax_amount, NULL document_curr_charge_amount, ( 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, aip.discount_taken payment_curr_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 payment_due_date, ac.payment_profile_id payment_profile_id, cpi.bank_id internal_bank_account_id, ac.external_bank_account_id external_bank_account_id, nvl( ac.bank_charge_bearer, ai.bank_charge_bearer ) bank_charge_bearer, NULL interest_rate, 1 payment_grouping_number, ai.payment_reason_code, ai.payment_reason_comments, nvl( ac.settlement_priority, ai.settlement_priority ) settlement_priority, aps.remittance_message1, aps.remittance_message2, aps.remittance_message3, ai.unique_remittance_identifier, ai.uri_check_digit, ai.delivery_channel_code, NULL payment_format_code, ai.doc_sequence_id document_sequence_id, ai.doc_sequence_value document_sequence_value, ai.doc_category_code document_category_code, NULL bank_assigned_ref_code, nvl( hz2.location_id, hz.location_id ) remit_to_location_id, aps.attribute_category, aps.attribute1, aps.attribute2, aps.attribute3, aps.attribute4, aps.attribute5, aps.attribute6, aps.attribute7, aps.attribute8, aps.attribute9, aps.attribute10, aps.attribute11, aps.attribute12, aps.attribute13, aps.attribute14, aps.attribute15, decode( ai.source, 'EMP_EXPENSE_REPORT', 'HR', 'EMP_CASH_ADVANCE', 'HR', 'TCA' ) address_source, nvl( ac.employee_address_code, ai.employee_address_code ) employee_address_code, decode( ai.source, 'EMP_EXPENSE_REPORT', 'Y', 'EMP_CASH_ADVANCE', 'Y', 'N' ) employee_payment_flag, decode( ai.source, 'EMP_EXPENSE_REPORT', ai.paid_on_behalf_employee_id, 'EMP_CASH_ADVANCE', ai.paid_on_behalf_employee_id, NULL ) employee_person_id, NULL employee_address_id, ai.pay_group_lookup_code pay_group_code, aip.amount_inv_curr doc_currency_payment_amount, nvl( ac.remit_to_supplier_id, ac.vendor_id ) ext_remit_payee_id, nvl( pzot.party_id, ac.party_id ) remit_payee_party_id, nvl( ac.remit_to_address_id, nvl( ac.party_site_id, ai.party_site_id ) ) remit_address_id, decode( ac.remit_to_address_id, NULL, ac.vendor_site_id, NULL ) remit_supplier_site_id, ac.party_id remit_beneficiary_party, nvl( ac.relationship_id, - 1 ) relationship_id, aip.discount_taken_inv_curr doc_curr_discount_taken, ( 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 ) doc_curr_amount_withheld, aps.global_attribute_category, aps.global_attribute1, aps.global_attribute2, aps.global_attribute3, aps.global_attribute4, aps.global_attribute5, aps.global_attribute6, aps.global_attribute7, aps.global_attribute8, aps.global_attribute9, aps.global_attribute10, aps.global_attribute11, aps.global_attribute12, aps.global_attribute13, aps.global_attribute14, aps.global_attribute15, aps.global_attribute16, aps.global_attribute17, aps.global_attribute18, aps.global_attribute19, aps.global_attribute20, aps.global_attribute_date1, aps.global_attribute_date2, aps.global_attribute_date3, aps.global_attribute_date4, aps.global_attribute_date5, aps.global_attribute_number1, aps.global_attribute_number2, aps.global_attribute_number3, aps.global_attribute_number4, aps.global_attribute_number5, NULL balancing_segment_value, ai.routing_attribute1, ai.routing_attribute2, ai.routing_attribute3, ai.routing_attribute4, ai.routing_attribute5, ai.set_of_books_id set_of_books_id, ac.payment_mode_code, ac.payment_instrument_id FROM ap_checks_all ac, ap_invoices_all ai, ap_invoice_payments_all aip, ap_payment_schedules_all aps, ce_payment_instruments cpi, ap_poz_site_and_assignment_v pvs, hz_party_sites hz, poz_suppliers asup, hz_party_sites hz2, poz_suppliers pzot 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 (+) AND ac.remit_to_address_id = hz2.party_site_id (+) AND ai.invoice_id = aps.invoice_id AND aps.payment_num = aip.payment_num AND ac.payment_instrument_id = cpi.payment_instrument_id AND ac.legal_entity_id = cpi.legal_entity_id AND nvl( ap_utilities_pkg.get_payment_bu_id( ai.org_id ), ai.org_id ) = ac.org_id/* X BU Project*/ AND ai.org_id = pvs.bu_id (+) AND ac.remit_to_supplier_id = pzot.vendor_id (+)