ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5B33D18D70F24759E0630100007FE55FY select z.ledger, z.operating_unit, z.supplier, z.supplier_number, z.supplier_type, z.supplier_site, z.invoice_type, z.prepayment_type, z.prepayment_status, z.invoice_number, z.voucher_number, z.invoice_date, z.invoice_gl_date, z.earliest_settlement_date, z.invoice_description, z.payment_status, z.invoice_currency, z.payment_currency, z.payment_cross_rate, z.invoice_amount, z.withheld_amount, z.amount_remaining, z.net_amount_remaining, z.line_number, z.line_type, z.line_description, z.line_amount, z.line_amount_remaining, z.po_number, z.receipt_number, z.created_by, z.creation_date, z.last_updated_by, z.last_update_date from ( select y.*, ( select sum(nvl(aida.prepay_amount_remaining,aida.total_dist_amount)) from ap_invoice_distributions_all aida where y.invoice_id=aida.invoice_id and y.line_number=aida.invoice_line_number and aida.line_type_lookup_code in ('ITEM','ACCRUAL','REC_TAX','NONREC_TAX') and nvl(aida.reversal_flag,'N')<>'Y' ) line_amount_remaining from ( select x.*, case when x.invoice_type_lookup_code<>'PREPAYMENT' then x.amount_remaining when x.payment_status_flag<>'Y' then 0 when x.earliest_settlement_date is null then x.amount_remaining else -x.amount_remaining end net_amount_remaining, max(case when x.invoice_type_lookup_code='PREPAYMENT' then 1 end) over (partition by x.vendor_id, x.org_id) has_prepayment, ail.line_number, xxen_util.meaning(ail.line_type_lookup_code,'INVOICE LINE TYPE',200) line_type, ail.description line_description, ail.amount line_amount, pha.segment1 po_number, rsh.receipt_num receipt_number from ( select gl.name ledger, haouv.name operating_unit, aps.vendor_name supplier, aps.segment1 supplier_number, xxen_util.meaning(aps.vendor_type_lookup_code,'VENDOR TYPE',201) supplier_type, assa.vendor_site_code supplier_site, xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type, case when aia.invoice_type_lookup_code='PREPAYMENT' then xxen_util.meaning(nvl2(aia.earliest_settlement_date,'TEMPORARY','PERMANENT'),'PREPAY TYPES',200) end prepayment_type, case when aia.invoice_type_lookup_code='PREPAYMENT' then xxen_util.meaning( case when aia.payment_status_flag<>'Y' then 'UNPAID' when aia.earliest_settlement_date is null then 'PERMANENT' else 'AVAILABLE' end,'PREPAY STATUS',200) end prepayment_status, aia.invoice_num invoice_number, nvl(to_char(aia.doc_sequence_value),aia.voucher_num) voucher_number, aia.invoice_date, aia.gl_date invoice_gl_date, aia.earliest_settlement_date, aia.description invoice_description, xxen_util.meaning(aia.payment_status_flag,'INVOICE PAYMENT STATUS',200) payment_status, aia.invoice_currency_code invoice_currency, aia.payment_currency_code payment_currency, aia.payment_cross_rate, aia.invoice_amount, (select -sum(aida.amount) from ap_invoice_distributions_all aida where aia.invoice_id=aida.invoice_id and aida.line_type_lookup_code='AWT') withheld_amount, case when aia.invoice_type_lookup_code='PREPAYMENT' then ( select sum(nvl(aida.prepay_amount_remaining,aida.total_dist_amount)) from ap_invoice_lines_all ail, ap_invoice_distributions_all aida where aia.invoice_id=ail.invoice_id and ail.line_type_lookup_code<>'TAX' and nvl(ail.line_selected_for_appl_flag,'N')<>'Y' and ail.invoice_id=aida.invoice_id and ail.line_number=aida.invoice_line_number and aida.line_type_lookup_code in ('ITEM','ACCRUAL','REC_TAX','NONREC_TAX') and nvl(aida.reversal_flag,'N')<>'Y' ) else ap_utilities_pkg.ap_round_currency((select sum(apsa.amount_remaining) from ap_payment_schedules_all apsa where aia.invoice_id=apsa.invoice_id)/aia.payment_cross_rate,aia.invoice_currency_code) end amount_remaining, xxen_util.user_name(aia.created_by) created_by, xxen_util.client_time(aia.creation_date) creation_date, xxen_util.user_name(aia.last_updated_by) last_updated_by, xxen_util.client_time(aia.last_update_date) last_update_date, aia.invoice_id, aia.invoice_type_lookup_code, aia.payment_status_flag, aia.vendor_id, aia.org_id from ap_invoices_all aia, ap_suppliers aps, ap_supplier_sites_all assa, gl_ledgers gl, hr_all_organization_units_vl haouv where 1=1 and aia.cancelled_date is null and (aia.invoice_type_lookup_code='PREPAYMENT' and 2=2 or aia.invoice_type_lookup_code<>'PREPAYMENT' and aia.payment_status_flag in ('N','P') and 3=3) and aia.org_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) and aia.vendor_id=aps.vendor_id and aia.vendor_site_id=assa.vendor_site_id(+) and aia.set_of_books_id=gl.ledger_id and aia.org_id=haouv.organization_id ) x, ap_invoice_lines_all ail, po_headers_all pha, rcv_transactions rt, rcv_shipment_headers rsh where (x.invoice_type_lookup_code<>'PREPAYMENT' or x.amount_remaining>0) and x.invoice_id=ail.invoice_id(+) and ail.line_type_lookup_code(+)=case when :show_prepayment_lines='Y' and x.invoice_type_lookup_code='PREPAYMENT' and x.payment_status_flag='Y' then 'ITEM' end and ail.po_header_id=pha.po_header_id(+) and ail.rcv_transaction_id=rt.transaction_id(+) and rt.shipment_header_id=rsh.shipment_header_id(+) ) y ) z where z.has_prepayment=1 and (z.line_number is null or z.line_amount_remaining>0) order by z.supplier, z.invoice_date, z.invoice_number, z.line_number Initial version, converted from the Oracle standard Prepayments Status Report (APXINPSR_XML). AR AP تقرير موقف الدفعات المقدمة (بتنسيق XML) Application: المدفوعات Source: تقرير موقف الدفعات المقدمة (بتنسيق XML) Short Name: APXINPSR_XML DB package: AP_APXINPSR_XMLP_PKG D AP Vorauszahlungen - Status Application: Kreditoren Source: Vorauszahlungen - Status (XML) Short Name: APXINPSR_XML DB package: AP_APXINPSR_XMLP_PKG F AP Etat des acomptes rapprochés Application: Payables Source: Etat des acomptes rapprochés (XML) Short Name: APXINPSR_XML DB package: AP_APXINPSR_XMLP_PKG US AP Prepayments Status Prepayments with an unapplied amount and the open, that is unpaid or partially paid, invoices, credit and debit memos of the suppliers holding such a prepayment, one row per invoice. Equivalent to the Oracle standard Prepayments Status Report (APXINPSR). Amount Remaining is in the invoice currency: for a prepayment the amount not yet applied, for other invoices the unpaid payment schedule amount converted at the payment cross rate. Net Amount Remaining nets paid temporary prepayments (negative) against open invoices and permanent prepayments; an unpaid prepayment counts as zero. Show Prepayment Lines adds one row per item line of a paid prepayment that still has an amount available to apply, with its purchase order and receipt. ZHS AP 预付款状态报表 Application: 应付帐款 Source: 预付款状态报表 (XML) Short Name: APXINPSR_XML DB package: AP_APXINPSR_XMLP_PKG Enginatics 1=1 2=2 3=3 :show_prepayment_lines 1 10 1=1 gl.name=:ledger LOV GL Ledger 8E2FF36EDEB879D2E0530100007F1FF2 Y select gl.name value, fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4), gl.name coalesce(xxen_util.previous_parameter_value(:parameter_id),xxen_util.default_ledger) AR تضمين الفواتير D Mit Rechnungen F Inclure les factures US Ledger ZHS 包括发票 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 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 coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) AR تضمين إشعارات دائنة/مدينة D Mit Gutschriften/Belastungen F Inclure notes débit/avoirs US Operating Unit ZHS 包括贷项/借项通知单 3 30 1=1 aps.vendor_name=:supplier 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 AR نوع المورد D Lieferantenart F Type de fournisseur US Supplier ZHS 供应商类型 4 40 1=1 aps.vendor_type_lookup_code=xxen_util.lookup_code(:supplier_type,'VENDOR TYPE',201) LOV AP Supplier Type 98567A11CE2765ADE0530100007F6157 Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='VENDOR TYPE' and flvv.view_application_id=201 and flvv.security_group_id=0 order by flvv.lookup_code AR اسم المورد D Lieferantenname F Nom du fournisseur US Supplier Type ZHS 供应商名称 5 50 2=2 aia.earliest_settlement_date is not null LOV custom Y select flvv.lookup_code id, flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='PREPAY TYPES' and flvv.view_application_id=200 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.meaning TEMPORARY TEMPORARY AR من تاريخ D Von Datum F Date de début US Prepayment Type ZHS 自日期 6 2=2 aia.earliest_settlement_date is null PERMANENT US Prepayment Type 7 60 3=3 aia.invoice_type_lookup_code in ('CREDIT','DEBIT') LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N Y AR إلى تاريخ D Bis Datum F Date de fin US Include Invoices ZHS 至日期 8 70 3=3 aia.invoice_type_lookup_code not in ('CREDIT','DEBIT') LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N Y AR نوع الدفعة المقدمة D Vorauszahlungsart F Type d'acompte US Include Credit/Debit Memos ZHS 预付款类型 9 80 1=1 aia.invoice_date>=:invoice_date_from Date AR إظهار سطور الدفعات المقدمة D Vorauszahlungspositionen anzeigen F Afficher les lignes d'acompte US Invoice Date From ZHS 显示预付款行 10 90 1=1 aia.invoice_date<:invoice_date_to+1 Date last_day(:$flex$.invoice_date_from) US Invoice Date To 11 100 :show_prepayment_lines LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Prepayment Lines