ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 23A7E910787E469FB9B02D0CF25DACE1Y select null action_, null status_, null message_, null modified_columns_, x.operating_unit, x.payment_type, x.payment_date, x.trading_partner, x.supplier_num, x.supplier_site, x.bank_account, x.payment_method, x.payment_process_profile, x.payment_document, x.document_num, x.currency, x.exchange_rate_type, x.exchange_date, x.exchange_rate, x.payment_amount, x.payment_status, x.void_date, x.invoice_action, x.hold_name, x.hold_reason, x.invoice_num, x.invoice_date, x.payment_num, x.due_date, x.amount_remaining, x.discount_available, x.amount_paid, x.discount_taken, x.check_id, x.invoice_payment_id, null upload_row from ( select haouv.name operating_unit, xxen_util.meaning(aca.payment_type_flag,'PAYMENT TYPE',200) payment_type, aca.check_date payment_date, aps.vendor_name trading_partner, aps.segment1 supplier_num, assa.vendor_site_code supplier_site, xxen_ap_upload.bank_account_name(aca.ce_bank_acct_use_id) bank_account, xxen_ap_upload.payment_method_name(aca.payment_method_code) payment_method, xxen_ap_upload.payment_profile_name(aca.payment_profile_id) payment_process_profile, cpd.payment_document_name payment_document, aca.check_number document_num, aca.currency_code currency, gdct.user_conversion_type exchange_rate_type, aca.exchange_date, aca.exchange_rate, aca.amount payment_amount, xxen_util.meaning(aca.status_lookup_code,'CHECK STATE',200) payment_status, aca.void_date, null invoice_action, null hold_name, null hold_reason, aia.invoice_num, aia.invoice_date, aipa.payment_num, apsa.due_date, apsa.amount_remaining, to_number(null) discount_available, aipa.amount amount_paid, aipa.discount_taken, aca.check_id, aipa.invoice_payment_id from ap_checks_all aca, hr_all_organization_units_vl haouv, ap_suppliers aps, ap_supplier_sites_all assa, ce_payment_documents cpd, gl_daily_conversion_types gdct, ap_invoice_payments_all aipa, ap_invoices_all aia, ap_payment_schedules_all apsa where 1=1 and aca.payment_type_flag in ('M','Q') and aca.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and aca.org_id=haouv.organization_id and aca.vendor_id=aps.vendor_id and aca.vendor_site_id=assa.vendor_site_id and aca.payment_document_id=cpd.payment_document_id(+) and aca.exchange_rate_type=gdct.conversion_type(+) and aca.check_id=aipa.check_id and aipa.reversal_inv_pmt_id is null and aipa.invoice_id=aia.invoice_id and aipa.invoice_id=apsa.invoice_id and aipa.payment_num=apsa.payment_num union all select haouv.name operating_unit, null payment_type, to_date(null) payment_date, aps.vendor_name trading_partner, aps.segment1 supplier_num, assa.vendor_site_code supplier_site, null bank_account, xxen_ap_upload.payment_method_name(apsa.payment_method_code) payment_method, null payment_process_profile, null payment_document, to_number(null) document_num, aia.payment_currency_code currency, null exchange_rate_type, to_date(null) exchange_date, to_number(null) exchange_rate, to_number(null) payment_amount, null payment_status, to_date(null) void_date, null invoice_action, null hold_name, null hold_reason, aia.invoice_num, aia.invoice_date, apsa.payment_num, apsa.due_date, apsa.amount_remaining, to_number(null) discount_available, to_number(null) amount_paid, to_number(null) discount_taken, to_number(null) check_id, to_number(null) invoice_payment_id from ap_invoices_all aia, hr_all_organization_units_vl haouv, ap_suppliers aps, ap_supplier_sites_all assa, ap_payment_schedules_all apsa where 2=2 and :open_invoices='Y' and aia.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat) and aia.org_id=haouv.organization_id and aia.vendor_id=aps.vendor_id and aia.vendor_site_id=assa.vendor_site_id and aia.invoice_id=apsa.invoice_id and aia.cancelled_date is null and nvl(aia.payment_status_flag,'N')<>'Y' and aia.wfapproval_status in ('WFAPPROVED','NOT REQUIRED','MANUALLY APPROVED') and nvl(apsa.payment_status_flag,'N')<>'Y' and nvl(apsa.hold_flag,'N')='N' and apsa.checkrun_id is null and aia.invoice_id not in (select aha.invoice_id from ap_holds_all aha where aha.release_lookup_code is null) and aia.invoice_id not in (select asia.invoice_id from ap_selected_invoices_all asia) and ap_invoices_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code) not in ('NEVER APPROVED','NEEDS REAPPROVAL','UNAPPROVED') ) x Invoice Action, Hold Name and Hold Reason columns to put the invoices of a voided payment on hold or cancel them, as the Void action of the Payments window does. U A xxen_ap_upload.upload_invoice_payment US AP Invoice Payment Upload Records Manual and Quick payments of open Payables invoices and voids payments - the Excel equivalent of entering a payment and its invoices in the Payments window. Each row is one invoice installment paid, with the payment columns repeated on every row. Rows with identical payment columns are paid by one payment. Amount Paid and Discount Taken default to the full amount remaining less the discount available at the Payment Date, and can be reduced to pay an installment partially. Each payment is processed as a whole: when any of its rows fails validation, none of its rows are saved. A Manual payment records a document already issued and needs its Document Num. A Quick payment takes the next number of a printed Payment Document, and is submitted to Oracle Payments as a single payment, as from the Payments window. Set the Open Invoices parameter to download the open installments of the selected supplier and operating unit as new rows to pay. In Create, Update mode existing Manual and Quick payments are downloaded; enter a Void Date to void one. Voiding reverses all invoice payments of the payment and leaves the invoices unpaid, unless Invoice Action puts them on hold or cancels them. Cancelling invoices is committed by Oracle Payables as it happens, as in the Payments window. Invoices that would get withholding tax or an interest invoice at payment time cannot be paid by a Quick payment through the upload. Enginatics Upload 1=1 2=2 :open_invoices 1 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 2 20 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 3 2=2 haouv.name=:operating_unit US Operating Unit 4 30 1=1 aps.vendor_name=:trading_partner LOV AP Supplier B9847D20A0E4742FE0538931640A6379 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id US Trading Partner 5 2=2 aps.vendor_name=:trading_partner US Trading Partner 6 40 1=1 aca.check_date>=:payment_date_from Date US Payment Date From 7 50 1=1 aca.check_date<:payment_date_to+1 Date :$flex$.payment_date_from US Payment Date To 8 60 1=1 aca.check_number=:document_num Number US Document Num 9 70 :open_invoices LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Open Invoices Downloads the open installments of the Operating Unit and Trading Partner as new rows to pay. 73335D34806E42CCADCB23DE82AFE01F Default Enters Manual and Quick payments of open invoices and voids downloaded payments, with the fields of the Payments window. ENGINATICS 28 AMOUNT_PAID 26 AMOUNT_REMAINING 7 BANK_ACCOUNT 12 CURRENCY 27 DISCOUNT_AVAILABLE 29 DISCOUNT_TAKEN 11 DOCUMENT_NUM 25 DUE_DATE 14 EXCHANGE_DATE 15 EXCHANGE_RATE 13 EXCHANGE_RATE_TYPE 20 HOLD_NAME 21 HOLD_REASON 19 INVOICE_ACTION 23 INVOICE_DATE 22 INVOICE_NUM 1 OPERATING_UNIT 16 PAYMENT_AMOUNT 3 PAYMENT_DATE 10 PAYMENT_DOCUMENT 8 PAYMENT_METHOD 24 PAYMENT_NUM 9 PAYMENT_PROCESS_PROFILE 17 PAYMENT_STATUS 2 PAYMENT_TYPE Y 5 SUPPLIER_NUM 6 SUPPLIER_SITE 4 TRADING_PARTNER 18 VOID_DATE Upload Mode C S Site 8B82BEF75BB64A98807225AA0AC2C32D Pay Open Invoices Downloads the open installments of a supplier as rows to pay. Enter the payment columns on the rows to pay and delete the others. ENGINATICS 22 AMOUNT_PAID 20 AMOUNT_REMAINING 7 BANK_ACCOUNT 12 CURRENCY 21 DISCOUNT_AVAILABLE 23 DISCOUNT_TAKEN 11 DOCUMENT_NUM 19 DUE_DATE 14 EXCHANGE_DATE 15 EXCHANGE_RATE 13 EXCHANGE_RATE_TYPE 17 INVOICE_DATE 16 INVOICE_NUM 1 OPERATING_UNIT 3 PAYMENT_DATE 10 PAYMENT_DOCUMENT 8 PAYMENT_METHOD 18 PAYMENT_NUM 9 PAYMENT_PROCESS_PROFILE 2 PAYMENT_TYPE Y 5 SUPPLIER_NUM 6 SUPPLIER_SITE 4 TRADING_PARTNER Open Invoices Y Y Upload Mode C Y S Site Document Num Payment Date From Payment Date To ADEA571EE9BC46078677F17B1A7A5B06 Void Payments Downloads existing Manual and Quick payments. Enter a Void Date on a payment to void it, and an Invoice Action to put its invoices on hold or cancel them. ENGINATICS 18 AMOUNT_PAID 4 BANK_ACCOUNT 9 CURRENCY 19 DISCOUNT_TAKEN 6 DOCUMENT_NUM 14 HOLD_NAME 15 HOLD_REASON 13 INVOICE_ACTION 16 INVOICE_NUM 1 OPERATING_UNIT 10 PAYMENT_AMOUNT 8 PAYMENT_DATE 5 PAYMENT_DOCUMENT 17 PAYMENT_NUM 11 PAYMENT_STATUS 7 PAYMENT_TYPE Y 3 SUPPLIER_SITE 2 TRADING_PARTNER 12 VOID_DATE Upload Mode C,U Y S Site Open Invoices 73335D34806E42CCADCB23DE82AFE01F 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 OPERATING_UNIT LOV varchar2 240 HR Operating Unit for Upload F263DDA3FBF60F8FE0530100007F75CD Y select hou.organization_id id, hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.operating_unit is null or hou.name=:$flex$.operating_unit) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) order by hou.name :$flex$.operating_unit Y Y 6 PAYMENT_TYPE LOV custom varchar2 80 Y select alc.displayed_field value, null description from ap_lookup_codes alc where alc.lookup_type='PAYMENT TYPE' and alc.lookup_code in ('M','Q') order by alc.lookup_code xxen_util.meaning('M','PAYMENT TYPE',200) Manual records a payment issued outside Oracle Payables. Quick creates the payment document number from the Payment Document and submits the payment to Oracle Payments. Y 7 PAYMENT_DATE Date date trunc(sysdate) Y 8 TRADING_PARTNER LOV custom varchar2 240 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where aps.vendor_id in (select assa.vendor_id from ap_supplier_sites_all assa where assa.org_id=(select hou.organization_id from hr_operating_units hou where hou.name=:$column$.operating_unit) and assa.pay_site_flag='Y' and nvl(assa.inactive_date,sysdate+1)>sysdate) order by aps.vendor_name Y 9 SUPPLIER_NUM Char varchar2 30 select aps.segment1 from ap_suppliers aps where aps.vendor_name=:$column$.trading_partner Y 10 SUPPLIER_SITE LOV custom varchar2 15 Y select assa.vendor_site_code value, assa.address_line1||', '||assa.city description from ap_suppliers aps, ap_supplier_sites_all assa where aps.vendor_name=:$column$.trading_partner and aps.vendor_id=assa.vendor_id and assa.org_id=(select hou.organization_id from hr_operating_units hou where hou.name=:$column$.operating_unit) and assa.pay_site_flag='Y' and nvl(assa.inactive_date,sysdate+1)>sysdate order by assa.vendor_site_code select assa.vendor_site_code from ap_invoices_all aia, ap_suppliers aps, hr_operating_units hou, ap_supplier_sites_all assa where aia.invoice_num=:$column$.invoice_num and aps.vendor_name=:$column$.trading_partner and aia.vendor_id=aps.vendor_id and hou.name=:$column$.operating_unit and aia.org_id=hou.organization_id and aia.vendor_site_id=assa.vendor_site_id and assa.pay_site_flag='Y' Y 11 BANK_ACCOUNT LOV custom varchar2 200 Y select xxen_ap_upload.bank_account_name(cbaua.bank_acct_use_id) value, cba.currency_code||' '||cba.bank_account_num description from ce_bank_acct_uses_all cbaua, ce_bank_accounts cba where cbaua.org_id=(select hou.organization_id from hr_operating_units hou where hou.name=:$column$.operating_unit) and cbaua.ap_use_enable_flag='Y' and nvl(cbaua.end_date,sysdate+1)>sysdate and cbaua.bank_account_id=cba.bank_account_id and cba.account_classification='INTERNAL' and nvl(cba.end_date,sysdate+1)>sysdate order by 1 Y 12 PAYMENT_METHOD LOV custom varchar2 200 Y select xxen_ap_upload.payment_method_name(ipmv.payment_method_code) value, ipmv.description from iby_payment_methods_vl ipmv where nvl(ipmv.inactive_date,sysdate+1)>sysdate order by 1 select distinct xxen_ap_upload.payment_method_name(apsa.payment_method_code) from ap_invoices_all aia, ap_suppliers aps, hr_operating_units hou, ap_payment_schedules_all apsa where aia.invoice_num=:$column$.invoice_num and aps.vendor_name=:$column$.trading_partner and aia.vendor_id=aps.vendor_id and hou.name=:$column$.operating_unit and aia.org_id=hou.organization_id and aia.invoice_id=apsa.invoice_id and nvl(apsa.payment_status_flag,'N')<>'Y' Y 13 PAYMENT_PROCESS_PROFILE LOV custom varchar2 200 Y select distinct xxen_ap_upload.payment_profile_name(ipp.payment_profile_id) value, flvv.meaning description from ( select cbaua.org_id, cbaua.bank_account_id from ce_bank_acct_uses_all cbaua where cbaua.org_id=(select hou.organization_id from hr_operating_units hou where hou.name=:$column$.operating_unit) and cbaua.ap_use_enable_flag='Y' and xxen_ap_upload.bank_account_name(cbaua.bank_acct_use_id)=:$column$.bank_account ) x, iby_applicable_pmt_profs iapp1, iby_applicable_pmt_profs iapp2, iby_applicable_pmt_profs iapp3, iby_applicable_pmt_profs iapp4, iby_payment_profiles ipp, fnd_lookup_values_vl flvv where iapp1.applicable_type_code='PAYER_ORG' and (iapp1.applicable_value_from='OPERATING_UNIT' and iapp1.applicable_value_to=to_char(x.org_id) or iapp1.applicable_value_from is null and iapp1.applicable_value_to is null) and iapp2.applicable_type_code='PAYMENT_METHOD' and (iapp2.applicable_value_to in (select ipmv.payment_method_code from iby_payment_methods_vl ipmv where xxen_ap_upload.payment_method_name(ipmv.payment_method_code)=:$column$.payment_method) or iapp2.applicable_value_to is null) and iapp3.applicable_type_code='CURRENCY_CODE' and (iapp3.applicable_value_to=:$column$.currency or iapp3.applicable_value_to is null) and iapp4.applicable_type_code='INTERNAL_BANK_ACCOUNT' and (iapp4.applicable_value_to=to_char(x.bank_account_id) or iapp4.applicable_value_to is null) and iapp1.system_profile_code=iapp2.system_profile_code and iapp1.system_profile_code=iapp3.system_profile_code and iapp1.system_profile_code=iapp4.system_profile_code and iapp1.system_profile_code=ipp.system_profile_code and ipp.processing_type in ('PRINTED','ELECTRONIC') and (ipp.inactive_date is null or ipp.inactive_date>trunc(sysdate)) and flvv.lookup_type='IBY_PROCESSING_TYPES' and flvv.lookup_code=ipp.processing_type and flvv.view_application_id=0 and flvv.security_group_id=0 order by 1 Y 14 PAYMENT_DOCUMENT LOV custom varchar2 100 Y select cpd.payment_document_name value, ifv.format_name description from ce_bank_acct_uses_all cbaua, ce_payment_documents cpd, iby_payment_profiles ipp, iby_formats_vl ifv where cbaua.org_id=(select hou.organization_id from hr_operating_units hou where hou.name=:$column$.operating_unit) and cbaua.ap_use_enable_flag='Y' and xxen_ap_upload.bank_account_name(cbaua.bank_acct_use_id)=:$column$.bank_account and cbaua.bank_account_id=cpd.internal_bank_account_id and xxen_ap_upload.payment_profile_name(ipp.payment_profile_id)=:$column$.payment_process_profile and ipp.payment_format_code=cpd.format_code and cpd.format_code=ifv.format_code and (cpd.inactive_date is null or trunc(cpd.inactive_date)>trunc(sysdate)) and cpd.payment_instruction_id is null and (cpd.manual_payments_only_flag='N' or :$column$.payment_type=xxen_util.meaning('M','PAYMENT TYPE',200)) order by cpd.payment_document_name Required for a Quick payment with a printed Payment Process Profile. 15 DOCUMENT_NUM Number number The number of the payment document. Required for a Manual payment. For a Quick payment with a Payment Document leave it blank to take the next available number. 16 CURRENCY LOV varchar2 15 FND Currency 8E2FF36EDE9079D2E0530100007F1FF2 Y select fcv.currency_code value, fcv.description from fnd_currencies_vl fcv where fcv.enabled_flag='Y' order by fcv.currency_code select aia.payment_currency_code from ap_invoices_all aia, ap_suppliers aps, hr_operating_units hou where aia.invoice_num=:$column$.invoice_num and aps.vendor_name=:$column$.trading_partner and aia.vendor_id=aps.vendor_id and hou.name=:$column$.operating_unit and aia.org_id=hou.organization_id Y 17 EXCHANGE_RATE_TYPE LOV varchar2 30 GL Currency Conversion Type 91D3167A335F1204E053BB6B63585EE3 Y select gdct.user_conversion_type value, gdct.description from gl_daily_conversion_types gdct order by 1 select gdct.user_conversion_type from ap_system_parameters_all aspa, gl_daily_conversion_types gdct where aspa.org_id=(select hou.organization_id from hr_operating_units hou where hou.name=:$column$.operating_unit) and aspa.base_currency_code<>:$column$.currency and aspa.default_exchange_rate_type=gdct.conversion_type 18 EXCHANGE_DATE Date date Defaults to the Payment Date for a payment in a foreign currency. 19 EXCHANGE_RATE Number number Required for exchange rate type User. Derived from the daily rates for the other types. 20 PAYMENT_AMOUNT Number number Y 21 PAYMENT_STATUS Char varchar2 80 Y 22 VOID_DATE Date date Enter a date in an open period on a downloaded payment to void it. Voiding reverses all invoice payments of the payment. 23 INVOICE_ACTION LOV custom varchar2 80 Y select alc.displayed_field value, null description from ap_lookup_codes alc where alc.lookup_type='VOID ACTIONS' and xxen_ap_upload.void_action_allowed(alc.lookup_code)='Y' order by decode(alc.lookup_code,'NONE',1,'HOLD',2,3) Action on the paid invoices when voiding the payment. Put Invoices on Hold needs function Payment Invoice Holds and Cancel Invoices needs Payment Invoice Cancel, as in the Payments window. 24 HOLD_NAME LOV custom varchar2 80 Y select ahhnv.hold value, ahhnv.reason description from ap_holds_hold_name_v ahhnv where ahhnv.user_releaseable_flag='Y' and :$column$.invoice_action=(select alc.displayed_field from ap_lookup_codes alc where alc.lookup_type='VOID ACTIONS' and alc.lookup_code='HOLD') order by upper(ahhnv.hold) Required for Invoice Action Put Invoices on Hold. 25 HOLD_REASON Char varchar2 240 select ahhnv.reason from ap_holds_hold_name_v ahhnv where ahhnv.hold=:$column$.hold_name and ahhnv.user_releaseable_flag='Y' 26 INVOICE_NUM LOV custom varchar2 50 Y select aia.invoice_num value, assa.vendor_site_code||' '||aia.payment_currency_code||' '||aia.invoice_amount description from ap_suppliers aps, ap_invoices_all aia, ap_supplier_sites_all assa where aps.vendor_name=:$column$.trading_partner and aps.vendor_id=aia.vendor_id and aia.org_id=(select hou.organization_id from hr_operating_units hou where hou.name=:$column$.operating_unit) and aia.vendor_site_id=assa.vendor_site_id and aia.cancelled_date is null and nvl(aia.payment_status_flag,'N')<>'Y' and aia.wfapproval_status in ('WFAPPROVED','NOT REQUIRED','MANUALLY APPROVED') and aia.invoice_id in (select apsa.invoice_id from ap_payment_schedules_all apsa where nvl(apsa.payment_status_flag,'N')<>'Y' and nvl(apsa.hold_flag,'N')='N' and apsa.checkrun_id is null) and aia.invoice_id not in (select aha.invoice_id from ap_holds_all aha where aha.release_lookup_code is null) and ap_invoices_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code) not in ('NEVER APPROVED','NEEDS REAPPROVAL','UNAPPROVED') order by aia.invoice_num Y 27 INVOICE_DATE Date date select aia.invoice_date from ap_invoices_all aia, ap_suppliers aps, hr_operating_units hou where aia.invoice_num=:$column$.invoice_num and aps.vendor_name=:$column$.trading_partner and aia.vendor_id=aps.vendor_id and hou.name=:$column$.operating_unit and aia.org_id=hou.organization_id Y 28 PAYMENT_NUM Number number select apsa.payment_num from ap_invoices_all aia, ap_suppliers aps, hr_operating_units hou, ap_payment_schedules_all apsa where aia.invoice_num=:$column$.invoice_num and aps.vendor_name=:$column$.trading_partner and aia.vendor_id=aps.vendor_id and hou.name=:$column$.operating_unit and aia.org_id=hou.organization_id and aia.invoice_id=apsa.invoice_id and nvl(apsa.payment_status_flag,'N')<>'Y' The installment of the invoice. Defaults when the invoice has one open installment. Y 29 DUE_DATE Date date select apsa.due_date from ap_invoices_all aia, ap_suppliers aps, hr_operating_units hou, ap_payment_schedules_all apsa where aia.invoice_num=:$column$.invoice_num and aps.vendor_name=:$column$.trading_partner and aia.vendor_id=aps.vendor_id and hou.name=:$column$.operating_unit and aia.org_id=hou.organization_id and aia.invoice_id=apsa.invoice_id and apsa.payment_num=:$column$.payment_num Y 30 AMOUNT_REMAINING Number number select apsa.amount_remaining from ap_invoices_all aia, ap_suppliers aps, hr_operating_units hou, ap_payment_schedules_all apsa where aia.invoice_num=:$column$.invoice_num and aps.vendor_name=:$column$.trading_partner and aia.vendor_id=aps.vendor_id and hou.name=:$column$.operating_unit and aia.org_id=hou.organization_id and aia.invoice_id=apsa.invoice_id and apsa.payment_num=:$column$.payment_num Y 31 DISCOUNT_AVAILABLE Number number select xxen_ap_upload.discount_available(aia.invoice_id,apsa.payment_num,:$column$.payment_date) from ap_invoices_all aia, ap_suppliers aps, hr_operating_units hou, ap_payment_schedules_all apsa where aia.invoice_num=:$column$.invoice_num and aps.vendor_name=:$column$.trading_partner and aia.vendor_id=aps.vendor_id and hou.name=:$column$.operating_unit and aia.org_id=hou.organization_id and aia.invoice_id=apsa.invoice_id and apsa.payment_num=:$column$.payment_num Y 32 AMOUNT_PAID Number number select apsa.amount_remaining-nvl(xxen_ap_upload.discount_available(aia.invoice_id,apsa.payment_num,:$column$.payment_date),0) from ap_invoices_all aia, ap_suppliers aps, hr_operating_units hou, ap_payment_schedules_all apsa where aia.invoice_num=:$column$.invoice_num and aps.vendor_name=:$column$.trading_partner and aia.vendor_id=aps.vendor_id and hou.name=:$column$.operating_unit and aia.org_id=hou.organization_id and aia.invoice_id=apsa.invoice_id and apsa.payment_num=:$column$.payment_num Y 33 DISCOUNT_TAKEN Number number select xxen_ap_upload.discount_available(aia.invoice_id,apsa.payment_num,:$column$.payment_date) from ap_invoices_all aia, ap_suppliers aps, hr_operating_units hou, ap_payment_schedules_all apsa where aia.invoice_num=:$column$.invoice_num and aps.vendor_name=:$column$.trading_partner and aia.vendor_id=aps.vendor_id and hou.name=:$column$.operating_unit and aia.org_id=hou.organization_id and aia.invoice_id=apsa.invoice_id and apsa.payment_num=:$column$.payment_num 34 CHECK_ID Number number Y 35 INVOICE_PAYMENT_ID Number number Y 36 UPLOAD_ROW Char varchar2 Y Error SQL Order By upload_row, operating_unit, trading_partner, payment_date, document_num, invoice_num, payment_num Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, nvl(haouv.name,xu.operating_unit) operating_unit, nvl(xxen_util.meaning(aca.payment_type_flag,'PAYMENT TYPE',200),xu.payment_type) payment_type, nvl(aca.check_date,xu.payment_date) payment_date, nvl(aps.vendor_name,xu.trading_partner) trading_partner, nvl(aps.segment1,xu.supplier_num) supplier_num, nvl(assa.vendor_site_code,xu.supplier_site) supplier_site, nvl(xxen_ap_upload.bank_account_name(aca.ce_bank_acct_use_id),xu.bank_account) bank_account, nvl(xxen_ap_upload.payment_method_name(aca.payment_method_code),xu.payment_method) payment_method, nvl(xxen_ap_upload.payment_profile_name(aca.payment_profile_id),xu.payment_process_profile) payment_process_profile, nvl(cpd.payment_document_name,xu.payment_document) payment_document, nvl(aca.check_number,xu.document_num) document_num, nvl(aca.currency_code,xu.currency) currency, nvl(gdct.user_conversion_type,xu.exchange_rate_type) exchange_rate_type, nvl(aca.exchange_date,xu.exchange_date) exchange_date, nvl(aca.exchange_rate,xu.exchange_rate) exchange_rate, nvl(aca.amount,xu.payment_amount) payment_amount, nvl(xxen_util.meaning(aca.status_lookup_code,'CHECK STATE',200),xu.payment_status) payment_status, nvl(aca.void_date,xu.void_date) void_date, xu.invoice_action, xu.hold_name, xu.hold_reason, nvl(aia.invoice_num,xu.invoice_num) invoice_num, nvl(aia.invoice_date,xu.invoice_date) invoice_date, nvl(aipa.payment_num,xu.payment_num) payment_num, nvl(apsa.due_date,xu.due_date) due_date, nvl(apsa.amount_remaining,xu.amount_remaining) amount_remaining, xu.discount_available, nvl(aipa.amount,xu.amount_paid) amount_paid, nvl(aipa.discount_taken,xu.discount_taken) discount_taken, xu.check_id, xu.invoice_payment_id, xu.upload_row from xxen_ap_invoice_payment_0075_u xu, ap_checks_all aca, hr_all_organization_units_vl haouv, ap_suppliers aps, ap_supplier_sites_all assa, ce_payment_documents cpd, gl_daily_conversion_types gdct, ap_invoice_payments_all aipa, ap_invoices_all aia, ap_payment_schedules_all apsa where xu.status_code_=xxen_upload.status_success and xu.check_id=aca.check_id(+) and aca.org_id=haouv.organization_id(+) and aca.vendor_id=aps.vendor_id(+) and aca.vendor_site_id=assa.vendor_site_id(+) and aca.payment_document_id=cpd.payment_document_id(+) and aca.exchange_rate_type=gdct.conversion_type(+) and xu.invoice_payment_id=aipa.invoice_payment_id(+) and aipa.invoice_id=aia.invoice_id(+) and aipa.invoice_id=apsa.invoice_id(+) and aipa.payment_num=apsa.payment_num(+)