ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN BE48D25B70F82C0AE053B46B63585D11Y with ap_inv as -- ap invoice data ( select gl.name ledger , haouv.name operating_unit , asup.vendor_name , asup.segment1 vendor_num , xxen_util.meaning(asup.vendor_type_lookup_code,'VENDOR TYPE',201) vendor_type , assa.vendor_site_code , aia.source , aia.invoice_id , aia.vendor_id , aia.vendor_site_id , aia.invoice_num , aia.invoice_date , xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) invoice_status , xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type , aia.description invoice_description , aia.invoice_currency_code , aia.invoice_amount , nvl(aia.base_amount,aia.invoice_amount) invoice_acctd_amount , aia.total_tax_amount , aia.amount_paid , aia.cancelled_date invoice_cancelled_date , aia.cancelled_amount invoice_cancelled_amount , aia.exchange_rate , gdct.user_conversion_type exchange_rate_type , aia.exchange_date , aila.line_number , xxen_util.meaning(aila.line_type_lookup_code,'INVOICE LINE TYPE',200) line_type , xxen_util.meaning(aila.line_source,'LINE SOURCE',200) line_source , xxen_util.yes(aila.discarded_flag) line_discarded_flag , msi.item , msi.item_type , msi.description item_description , aila.quantity_invoiced line_qty_invoiced , nvl(muom.uom_code ,aila.unit_meas_lookup_code) uom , aila.unit_price , aila.amount line_amount , nvl(aila.base_amount,aila.amount) line_acctd_amount , aila.description line_description , zl.tax tax_type , zl.tax_rate_code , zl.tax_rate , aida.invoice_distribution_id , aida.distribution_line_number dist_line_number , xxen_util.meaning(aida.line_type_lookup_code,'INVOICE DISTRIBUTION TYPE',200) dist_type , aida.quantity_invoiced dist_qty_invoiced , aida.amount dist_amount , nvl(aida.base_amount,aida.amount) dist_acctd_amount , aida.accounting_date dist_accounting_date , aida.period_name dist_period , aida.description dist_description , nvl(aida.dist_match_type, aila.match_type) dist_match_type , case aia.source when 'Intercompany' then (select min(rsh.receipt_num) from mtl_material_transactions mmt1 , mtl_material_transactions mmt2 , rcv_transactions rt , rcv_shipment_headers rsh where mmt1.transaction_id = to_number(aida.reference_2) and mmt2.transfer_transaction_id = mmt1.transaction_id and rt.transaction_id = mmt2.rcv_transaction_id and rsh.shipment_header_id = rt.shipment_header_id ) else (select rsh.receipt_num from rcv_transactions rt , rcv_shipment_headers rsh where rt.transaction_id = nvl(aida.rcv_transaction_id,aila.rcv_transaction_id) and rsh.shipment_header_id = rt.shipment_header_id ) end dist_matched_receipt_num , aida.set_of_books_id dist_ledger_id , aida.accounting_event_id dist_accounting_event_id , nvl(aida.po_distribution_id,aila.po_distribution_id) po_distribution_id , decode(aia.source,'Intercompany',to_number(aia.reference_1)) customer_trx_id , decode(aia.source,'Intercompany',to_number(aila.reference_1)) customer_trx_line_id , case aia.source when 'Intercompany' then (select min(mmt2.transaction_id) from mtl_material_transactions mmt1 , mtl_material_transactions mmt2 where mmt1.transaction_id = to_number(aida.reference_2) and mmt2.transfer_transaction_id = mmt1.transaction_id ) else (select min(mmt.transaction_id) from rcv_transactions rt1 , rcv_transactions rt2 , mtl_material_transactions mmt where rt1.transaction_id = nvl(aida.rcv_transaction_id,aila.rcv_transaction_id) and rt2.shipment_header_id = rt1.shipment_header_id and rt2.shipment_line_id = rt1.shipment_line_id and rt2.transaction_type = 'DELIVER' and mmt.rcv_transaction_id = rt2.transaction_id ) end sla_inv_transaction_id , msi.inventory_item_id , msi.organization_id from ap_invoices_all aia , ap_invoice_lines_all aila , ap_invoice_distributions_all aida , ap_suppliers asup , ap_supplier_sites_all assa , hr_all_organization_units_vl haouv , gl_ledgers gl , gl_daily_conversion_types gdct , mtl_units_of_measure muom , (select fspa.org_id , msiv.inventory_item_id , msiv.segment1 item , xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type , msiv.description , msiv.organization_id from financials_system_params_all fspa , mtl_system_items_vl msiv where msiv.organization_id = fspa.inventory_organization_id ) msi , ( select /*+ push_pred */ distinct zl1.trx_id , zl1.trx_line_id , listagg (zl1.tax,'/ ') within group (order by zl1.tax) over (partition by zl1.trx_id,zl1.trx_line_id) tax , listagg (zl1.tax_rate_code,'/ ') within group (order by zl1.tax, zl1.tax_rate_code) over (partition by zl1.trx_id,zl1.trx_line_id) tax_rate_code , listagg (to_char(zl1.tax_rate),'/ ') within group (order by zl1.tax, zl1.tax_rate_code) over (partition by zl1.trx_id,zl1.trx_line_id) tax_rate from (select zl2.trx_id , zl2.trx_line_id , zl2.tax , zl2.tax_rate_code , zl2.tax_rate , sum(lengthb(zl2.tax)+2) over (partition by zl2.trx_id,zl2.trx_line_id order by zl2.tax rows between unbounded preceding and current row) len1 , sum(lengthb(zl2.tax_rate_code)+2) over (partition by zl2.trx_id,zl2.trx_line_id order by zl2.tax_rate_code rows between unbounded preceding and current row) len2 from zx_lines zl2 where zl2.application_id = 200 and zl2.entity_code = 'AP_INVOICES' and zl2.trx_level_type = 'LINE' group by zl2.trx_id , zl2.trx_line_id , zl2.tax , zl2.tax_rate_code , zl2.tax_rate ) zl1 where zl1.len1 <= 4000 and zl1.len2 <= 4000 ) zl where aila.invoice_id = aia.invoice_id and aida.invoice_id = aila.invoice_id and aida.invoice_line_number = aila.line_number and aia.vendor_id = asup.vendor_id and aia.vendor_site_id = assa.vendor_site_id and haouv.organization_id = aia.org_id and gl.ledger_id = aia.set_of_books_id and gdct.conversion_type (+) = aia.exchange_rate_type and muom.unit_of_measure (+) = aila.unit_meas_lookup_code and msi.inventory_item_id (+) = aila.inventory_item_id and msi.org_id (+) = aila.org_id and zl.trx_id (+) = aila.invoice_id and zl.trx_line_id (+) = aila.line_number ) , po as ( select pda.po_distribution_id , haouv.name po_operating_unit , ( select prha.segment1 po_requisition from po_req_distributions_all prda , po_requisition_lines_all prla , po_requisition_headers_all prha where prda.requisition_line_id = prla.requisition_line_id and prla.requisition_header_id = prha.requisition_header_id and prda.distribution_id = pda.req_distribution_id ) po_requisition_num , pha.segment1 po_num , pra.release_num po_release_num , trunc(pha.creation_date) po_creation_date , pra.release_date po_release_date , pla.line_num || '.' || plla.shipment_num po_line_shipment_num , pltv.line_type po_line_type , msi.item , msi.item_type , msi.description item_description , plla.quantity - nvl(plla.quantity_cancelled,0) po_shipment_qty_ordered , plla.quantity_accepted po_shipment_qty_accepted , plla.quantity_rejected po_shipment_qty_rejected , plla.quantity_billed po_shipment_qty_billed , nvl( muom.uom_code, plla.unit_meas_lookup_code) po_uom , pda.distribution_num po_dist_num , pda.quantity_ordered - nvl(pda.quantity_cancelled,0) po_dist_qty_ordered , pda.quantity_delivered po_dist_qty_delivered , pda.quantity_billed po_dist_qty_billed , mp1.organization_code po_ship_to_organization , mp2.organization_code po_destination_organization , pdr.receipt_nums po_dist_receipt_nums , msi.inventory_item_id , msi.organization_id from po_distributions_all pda , po_line_locations_all plla , po_lines_all pla , po_headers_all pha , po_releases_all pra , po_line_types_v pltv , mtl_units_of_measure muom , mtl_parameters mp1 , mtl_parameters mp2 , hr_all_organization_units_vl haouv , (select fspa.org_id , msiv.inventory_item_id , msiv.segment1 item , xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type , msiv.description , msiv.organization_id from financials_system_params_all fspa , mtl_system_items_vl msiv where msiv.organization_id = fspa.inventory_organization_id ) msi , (select distinct po_line_location_id , po_distribution_id , listagg(receipt_num,',') within group (order by receipt_num) over (partition by po_line_location_id,po_distribution_id) receipt_nums from (select po_line_location_id , po_distribution_id , receipt_num , sum(lengthb(receipt_num)+1) over (partition by po_line_location_id,po_distribution_id order by receipt_num rows between unbounded preceding and current row) len from (select distinct rt.po_line_location_id , rt.po_distribution_id , rsh.receipt_num from rcv_transactions rt , rcv_shipment_headers rsh where rsh.shipment_header_id = rt.shipment_header_id and rt.po_line_location_id is not null and rt.po_distribution_id is not null ) ) where len <= 4000 ) pdr where pda.po_header_id = plla.po_header_id and pda.po_line_id = plla.po_line_id and pda.line_location_id = plla.line_location_id and plla.po_header_id = pla.po_header_id and plla.po_line_id = pla.po_line_id and pla.po_header_id = pha.po_header_id and haouv.organization_id = pha.org_id and pltv.line_type_id = pla.line_type_id and pra.po_release_id (+) = plla.po_release_id and msi.inventory_item_id (+) = pla.item_id and msi.org_id (+) = pla.org_id and muom.unit_of_measure (+) = plla.unit_meas_lookup_code and mp1.organization_id (+) = plla.ship_to_organization_id and mp2.organization_id (+) = pda.destination_organization_id and pdr.po_line_location_id (+) = pda.line_location_id and nvl(pdr.po_distribution_id,pda.po_distribution_id) = pda.po_distribution_id ) &l_ico_table &l_ap_sla_table &l_inv_sla_table -- ******************************************* -- ** Main Query Starts Here -- ******************************************* select ap_inv.ledger ledger , ap_inv.operating_unit operating_unit , ap_inv.vendor_name supplier_name , ap_inv.vendor_num supplier_num , ap_inv.vendor_type supplier_type , ap_inv.vendor_site_code vendor_site_code , ap_inv.source invoice_source , ap_inv.invoice_num invoice_num , ap_inv.invoice_date invoice_date , ap_inv.invoice_type invoice_type , ap_inv.invoice_status invoice_status , ap_inv.invoice_description invoice_description , ap_inv.invoice_currency_code currency , ap_inv.invoice_amount invoice_amount , ap_inv.invoice_acctd_amount invoice_accounted_amount , ap_inv.total_tax_amount invoice_tax_amoumt , ap_inv.amount_paid invoice_paid_amount , ap_inv.exchange_rate exchange_rate , ap_inv.exchange_rate_type exchange_rate_type , ap_inv.exchange_date exchange_rate_date , ap_inv.line_number invoice_line_num , ap_inv.line_type invoice_line_type , ap_inv.line_source invoice_line_source , ap_inv.line_discarded_flag invoice_line_discarded &l_item_sel , ( select distinct listagg(micv.category_concat_segs,', ') within group (order by micv.category_concat_segs) from mtl_item_categories_v micv where micv.category_set_id = :p_category_set_id &l_category_set_joins ) &l_category_set_name , ap_inv.line_description invoice_line_description , ap_inv.line_qty_invoiced invoice_line_qty , ap_inv.uom uom , ap_inv.unit_price unit_price , ap_inv.line_amount invoice_line_amount , ap_inv.line_acctd_amount invoice_line_accounted_amount , ap_inv.tax_type invoice_line_tax_type , ap_inv.tax_rate_code invoice_line_tax_rate_code , ap_inv.tax_rate invoice_line_tax_rate , ap_inv.dist_line_number invoice_dist_line_num , ap_inv.dist_type invoice_dist_type , ap_inv.dist_description invoice_dist_description , ap_inv.dist_qty_invoiced invoice_dist_qty , ap_inv.dist_amount invoice_dist_amount , ap_inv.dist_acctd_amount invoice_dist_accounted_amount , ap_inv.dist_accounting_date accounting_date , ap_inv.dist_period period_name , ap_inv.dist_match_type match_type , ap_inv.dist_matched_receipt_num matched_receipt_num &il_reg_columns &il_columns_amt &il_columns_rate &il_columns_rec &il_columns_tp -- &l_ap_sla_sel &l_inv_sla_sel -- , po.po_operating_unit , po.po_requisition_num , po.po_num , po.po_release_num , po.po_creation_date , po.po_release_date , po.po_line_shipment_num , po.po_line_type , po.po_shipment_qty_ordered , po.po_shipment_qty_accepted , po.po_shipment_qty_rejected , po.po_shipment_qty_billed , po.po_uom , po.po_dist_num , po.po_dist_qty_ordered , po.po_dist_qty_delivered , po.po_dist_qty_billed , po.po_ship_to_organization , po.po_destination_organization , po.po_dist_receipt_nums -- &l_ico_sel from ap_inv , po &l_ico_fr &l_ap_sla_fr &l_inv_sla_fr &il_from where 1=1 and po.po_distribution_id(+) = ap_inv.po_distribution_id &l_ico_join &l_ap_sla_join &l_inv_sla_join &il_where order by ap_inv.ledger , ap_inv.operating_unit , trunc(ap_inv.invoice_date) , ap_inv.invoice_num , ap_inv.line_number , ap_inv.dist_line_number Added India GST columns, displayed only when India Localization is installed. General (:Ledger is not null or :Operating_Unit is not null) and ((:Invoice_Date_From is not null and :Invoice_Date_To is not null) or (:Accounting_Date_From is not null and :Accounting_Date_To is not null)) US AP Invoices with PO, Intercompany and SLA Details AP Invoices with PO, Intercompany and SLA Details When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, GST TAN No, CGST Amount, SGST Amount, IGST Amount, CESS Amount, Custom Amount, Unclassified Tax Amount, Non-Recoverable GST, Taxable Value, HSN SAC Code, CGST Rate, SGST Rate, IGST Rate, CESS Rate, CGST Recoverable, CGST Non-Recoverable, SGST Recoverable, SGST Non-Recoverable, IGST Recoverable, IGST Non-Recoverable, Reverse Charge Flag, Place of Supply, Ship From State. Please specify an Ledger and/or Operating Unit, and an Invoice Date Range and/or an Accounting Date Range Enginatics &il_columns_amt &il_columns_rate &il_columns_rec &il_columns_tp &il_from &il_reg_columns &il_where &l_ap_sla_fr &l_ap_sla_join &l_ap_sla_sel &l_ap_sla_table &l_category_set_joins &l_category_set_name &l_ico_fr &l_ico_join &l_ico_sel &l_ico_table &l_inv_sla_fr &l_inv_sla_join &l_inv_sla_sel &l_inv_sla_table &l_item_sel 1=1 :p_category_set_id 1 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &il_columns_amt il_tax.cgst_amount "CGST Amount", il_tax.sgst_amount "SGST Amount", il_tax.igst_amount "IGST Amount", il_tax.cess_amount "CESS Amount", il_tax.custom_amount "Custom Amount", il_tax.unclassified_amount "Unclassified Tax Amount", il_tax.non_recoverable_gst "Non-Recoverable GST", il_tax.taxable_value "Taxable Value", il_tax.hsn_sac_code "HSN SAC Code", Y US India Localization 3 &il_columns_rate il_tax.cgst_tax_rate "CGST Rate", il_tax.sgst_tax_rate "SGST Rate", il_tax.igst_tax_rate "IGST Rate", il_tax.cess_tax_rate "CESS Rate", Y US India Localization 4 &il_columns_rec il_tax.cgst_rec_amount "CGST Recoverable", il_tax.cgst_nrec_amount "CGST Non-Recoverable", il_tax.sgst_rec_amount "SGST Recoverable", il_tax.sgst_nrec_amount "SGST Non-Recoverable", il_tax.igst_rec_amount "IGST Recoverable", il_tax.igst_nrec_amount "IGST Non-Recoverable", Y US India Localization 5 &il_columns_tp xxen_util.meaning(il_tax.reverse_charge_flag,'YES_NO',0) "Reverse Charge Flag", (select max(jtdf.ship_to_state) from jai_tax_det_factors jtdf where jtdf.trx_id=ap_inv.invoice_id and jtdf.application_id=200) "Place of Supply", (select max(jtdf.ship_from_state) from jai_tax_det_factors jtdf where jtdf.trx_id=ap_inv.invoice_id and jtdf.application_id=200) "Ship From State" Y US India Localization 6 &il_from ,(select jpr.party_id, jpr.party_site_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_site, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_site, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_site, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_site from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.supplier_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id, jpr.party_site_id) il_reg_site ,(select jpr.party_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin_hdr, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_hdr, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_hdr, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_hdr, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_hdr from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.supplier_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id) il_reg_hdr ,(select jtlv.trx_id, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_tax_amt_trx_curr)) cgst_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rounded_tax_amt_trx_curr)) sgst_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rounded_tax_amt_trx_curr)) igst_amount, sum(decode(jrav.reporting_code,'CESS',jtlv.rounded_tax_amt_trx_curr)) cess_amount, sum(decode(jrav.reporting_code,'CUSTOM',jtlv.rounded_tax_amt_trx_curr)) custom_amount, sum(decode(jrav.reporting_code,null,jtlv.rounded_tax_amt_trx_curr)) unclassified_amount, max(decode(jrav.reporting_code,'CGST',jtlv.tax_rate_code)) cgst_tax_rate, max(decode(jrav.reporting_code,'SGST',jtlv.tax_rate_code)) sgst_tax_rate, max(decode(jrav.reporting_code,'IGST',jtlv.tax_rate_code)) igst_tax_rate, max(decode(jrav.reporting_code,'CESS',jtlv.tax_rate_code)) cess_tax_rate, sum(decode(jrav.reporting_code,'CGST',jtlv.rec_tax_amt_trx_curr)) cgst_rec_amount, sum(decode(jrav.reporting_code,'CGST',jtlv.nrec_tax_amt_trx_curr)) cgst_nrec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rec_tax_amt_trx_curr)) sgst_rec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.nrec_tax_amt_trx_curr)) sgst_nrec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rec_tax_amt_trx_curr)) igst_rec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.nrec_tax_amt_trx_curr)) igst_nrec_amount, sum(case when jr.regime_type='T' then jtlv.nrec_tax_amt_trx_curr end) non_recoverable_gst, max(jtlv.self_assessed_flag) reverse_charge_flag, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_taxable_amt_trx_curr,'IGST',jtlv.rounded_taxable_amt_trx_curr)) taxable_value, max(jrc.reporting_code) hsn_sac_code from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav, jai_tax_det_factors jtdf, jai_reporting_codes jrc, jai_tax_types jtt, jai_regimes jr where jtlv.tax_type_id=jrav.entity_id(+) and jrav.entity_code(+)='TAX_TYPE' and jrav.reporting_type_code(+)='TAX_TYPES_CLASSIFICATION' and jtlv.tax_type_id=jtt.tax_type_id(+) and jtt.regime_id=jr.regime_id(+) and jtlv.det_factor_id=jtdf.det_factor_id(+) and jtdf.application_id(+)=200 and nvl(jtdf.hsn_code_id,jtdf.sac_code_id)=jrc.reporting_code_id(+) and jtlv.application_id=200 group by jtlv.trx_id ) il_tax Y US India Localization 7 &il_reg_columns ,nvl(il_reg_site.gstin,il_reg_hdr.gstin_hdr) "GST Registration No", nvl(il_reg_site.pan_site,nvl(il_reg_site.sec_pan_site,nvl(il_reg_hdr.pan_hdr,il_reg_hdr.sec_pan_hdr))) "GST PAN No", nvl(il_reg_site.tan_site,nvl(il_reg_site.sec_tan_site,nvl(il_reg_hdr.tan_hdr,il_reg_hdr.sec_tan_hdr))) "GST TAN No", Y US India Localization 8 &il_where and ap_inv.vendor_site_id=il_reg_site.party_site_id(+) and ap_inv.vendor_id=il_reg_hdr.party_id(+) and ap_inv.invoice_id=il_tax.trx_id(+) Y US India Localization 9 10 1=1 ap_inv.ledger=:p_ledger and ap_inv.operating_unit in (select hou.name from hr_operating_units hou where 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)) 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 F Grand livre US Ledger 10 20 1=1 ap_inv.operating_unit=:p_ou 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 F Unité d'exploitation US Operating Unit 11 30 1=1 ap_inv.invoice_date>=:p_inv_date_from Date F Date de facturation de US Invoice Date From 12 40 1=1 ap_inv.invoice_date<:p_inv_date_to + 1 Date last_day(:$flex$.Invoice_Date_From) F Date de facturation au US Invoice Date To 13 50 1=1 ap_inv.dist_accounting_date>=:p_accounting_date_from Date F Date comptable du US Accounting Date From 14 60 1=1 ap_inv.dist_accounting_date<:p_accounting_date_to+1 Date last_day(:$flex$.Accounting_Date_From) F Date de comptabilisation au US Accounting Date To 15 70 1=1 ap_inv.vendor_name=:p_vendor_name 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 F Fournisseur US Supplier 16 80 1=1 ap_inv.vendor_site_code=:p_vendor_site LOV custom Y select distinct assa.vendor_site_code value, null description from hr_all_organization_units_vl haouv, ap_suppliers asu, ap_supplier_sites_all assa where (:$flex$.operating_unit is null or haouv.name=:$flex$.operating_unit) and asu.vendor_name = :$flex$.supplier and haouv.organization_id=assa.org_id and asu.vendor_id=assa.vendor_id order by assa.vendor_site_code F Site du fournisseur US Supplier Site 17 90 1=1 ap_inv.source=:p_inv_source LOV custom Y select distinct source value , null description from ap_invoices_all order by source F Source de la facture US Invoice Source 18 100 1=1 ap_inv.invoice_num=:p_invoice_num LOV AP Invoice Number 8E2FF36EDF3079D2E0530100007F1FF2 Y Y select aia.invoice_num value, aps.vendor_name||' ('||aia.invoice_date||')' description from ap_invoices_all aia, ap_suppliers aps where (:$flex$.operating_unit is null or aia.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or aia.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and aia.vendor_id=aps.vendor_id order by aia.invoice_date desc F Numéro de facture US Invoice Number 19 110 1=1 ap_inv.line_type != :p_inv_line_type LOV custom Y select flvv.meaning value, flvv.description description from fnd_lookup_values_vl flvv where flvv.lookup_type = 'INVOICE LINE TYPE' and flvv.view_application_id = 200 and flvv.enabled_flag = 'Y' and sysdate between nvl(flvv.start_date_active,sysdate) and nvl(flvv.end_date_active,sysdate) order by flvv.meaning Tax;Withholding Tax F Types de lignes de facturation d'Exlude US Exlude Invoice Line Types 20 120 1=1 ap_inv.dist_type != :p_inv_dist_type LOV custom Y select flvv.meaning value, flvv.description description from fnd_lookup_values_vl flvv where flvv.lookup_type = 'INVOICE DISTRIBUTION TYPE' and flvv.view_application_id = 200 and flvv.enabled_flag = 'Y' and sysdate between nvl(flvv.start_date_active,sysdate) and nvl(flvv.end_date_active,sysdate) order by flvv.meaning US Exlude Invoice Dist Types 21 130 1=1 nvl(ap_inv.line_discarded_flag,'N') = 'N' 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 N F Inclure les lignes de factures rejetées US Include Discarded Invoice Lines 22 140 :p_category_set_id LOV Oracle INV_SRS_CATEGORY_SET Y select category_set_id id, category_set_name value, description description from mtl_category_sets order by value,description select nvl( (select mcs.category_set_id from mtl_category_sets mcs where mcs.category_set_name = 'Intrastat Commodity' ), (select mcs.category_set_id from mtl_default_category_sets mdcs , mtl_category_sets mcs where mdcs.functional_area_id = 1 and mdcs.category_set_id = mcs.category_set_id ) ) from dual Y F Afficher l'ensemble des catégories d'éléments US Display Item Category Set 23 &l_category_set_name select '"' || substr(mcs.category_set_name || ' Category',1,28) || '"' from mtl_category_sets mcs where mcs.category_set_id = :p_category_set_id F Afficher l'ensemble des catégories d'éléments US Display Item Category Set 24 150 &l_ap_sla_table , ap_sla as -- sla accounting for ap invoice ( select xdl.source_distribution_id_num_1 sla_ap_inv_dist_id , xal.ledger_id sla_ledger_id , xdl.event_id sla_event_id , xal.accounting_class_code sla_accounting_class , xal.accounted_dr sla_accounted_dr , xal.accounted_cr sla_accounted_cr , gcck.concatenated_segments sla_account from xla_distribution_links xdl , xla_ae_lines xal , gl_code_combinations_kfv gcck where xdl.source_distribution_type = 'AP_INV_DIST' and xal.ae_header_id = xdl.ae_header_id and xal.ae_line_num = xdl.ae_line_num and xal.accounting_class_code = 'ITEM EXPENSE' and gcck.code_combination_id = xal.code_combination_id ) 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 Y Y Y F Afficher la comptabilité AP SLA US Show AP SLA Accounting 25 &l_ap_sla_fr , ap_sla Y F Afficher la comptabilité AP SLA US Show AP SLA Accounting 26 &l_ap_sla_sel , ap_sla.sla_accounted_dr ap_sla_accounted_dr , ap_sla.sla_accounted_cr ap_sla_accounted_cr , ap_sla.sla_account ap_sla_account Y F Afficher la comptabilité AP SLA US Show AP SLA Accounting 27 &l_inv_sla_join and ap_sla.sla_ap_inv_dist_id(+)=ap_inv.invoice_distribution_id and ap_sla.sla_ledger_id(+)=ap_inv.dist_ledger_id and ap_sla.sla_event_id(+)=ap_inv.dist_accounting_event_id Y F Afficher la comptabilité AP SLA US Show AP SLA Accounting 28 160 &l_inv_sla_table , inv_sla as -- sla accounting for inventory transaction discrete ( select mta.transaction_id , xdl.event_id sla_event_id , xal.accounting_class_code sla_accounting_class , xal.accounted_dr sla_accounted_dr , xal.accounted_cr sla_accounted_cr , gcck.concatenated_segments sla_account from mtl_transaction_accounts mta , xla_distribution_links xdl , xla_ae_lines xal , gl_ledgers gl , gl_code_combinations_kfv gcck where mta.accounting_line_type != 1 -- inventory valuation and xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS' and xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id and xal.ae_header_id = xdl.ae_header_id and xal.ae_line_num = xdl.ae_line_num and xal.accounting_class_code != 'INVENTORY_VALUATION' and gl.ledger_id = xal.ledger_id and gl.ledger_category_code = 'PRIMARY' and gcck.code_combination_id = xal.code_combination_id ) , opm_sla as -- sla accounting for inventory transaction opm ( select gxeh.transaction_id , xdl.event_id sla_event_id , xal.accounting_class_code sla_accounting_class , xal.accounted_dr sla_accounted_dr , xal.accounted_cr sla_accounted_cr , gcck.concatenated_segments sla_account from gmf_xla_extract_headers gxeh , gmf_xla_extract_lines gxel , xla_distribution_links xdl , xla_ae_lines xal , gl_ledgers gl , gl_code_combinations_kfv gcck where gxel.header_id = gxeh.header_id and gxel.journal_line_type != 'INV' and xdl.source_distribution_type = gxeh.entity_code and xdl.source_distribution_id_num_1 = gxel.line_id and xdl.event_id = gxeh.event_id and xal.ae_header_id = xdl.ae_header_id and xal.ae_line_num = xdl.ae_line_num and xal.accounting_class_code != 'INVENTORY_VALUATION' and gl.ledger_id = xal.ledger_id and gl.ledger_category_code = 'PRIMARY' and gcck.code_combination_id = xal.code_combination_id ) 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 Y Y Y F Montrer la comptabilité INV SLA US Show INV SLA Accounting 29 &l_inv_sla_fr , inv_sla , opm_sla Y F Montrer la comptabilité INV SLA US Show INV SLA Accounting 30 &l_inv_sla_join and inv_sla.transaction_id(+)=ap_inv.sla_inv_transaction_id and opm_sla.transaction_id(+)=ap_inv.sla_inv_transaction_id Y F Montrer la comptabilité INV SLA US Show INV SLA Accounting 31 &l_inv_sla_sel , nvl(inv_sla.sla_accounting_class ,opm_sla.sla_accounting_class) inv_sla_accounting_class , case when inv_sla.sla_event_id is not null then inv_sla.sla_accounted_dr else opm_sla.sla_accounted_dr end inv_sla_accounted_dr , case when inv_sla.sla_event_id is not null then inv_sla.sla_accounted_cr else opm_sla.sla_accounted_cr end inv_sla_accounted_cr , case when inv_sla.sla_event_id is not null then inv_sla.sla_account else opm_sla.sla_account end inv_sla_account Y F Montrer la comptabilité INV SLA US Show INV SLA Accounting 32 170 &l_ico_table , ico as ( select rctla.customer_trx_id , rctla.customer_trx_line_id , haouv.name source_operating_unit , case rctla.interface_line_context when 'GLOBAL_PROCUREMENT' then 'Purchase Order' else case mmt.transaction_source_type_id when 8 then 'Internal Sales Order' else 'Sales Order' end end source_document , trunc(nvl( ooha.ordered_date, pha.creation_date)) source_document_date , nvl( to_char(ooha.order_number), pha.segment1) source_document_num , nvl( oe_order_misc_pub.get_concat_line_number(oola.line_id), pla.line_num || '.' || plla.shipment_num) source_document_line_num , nvl( oola.ordered_quantity, plla.quantity - nvl(plla.quantity_cancelled,0)) source_document_qty , nvl( oola.order_quantity_uom, nvl( mufm.uom_code, plla.unit_meas_lookup_code)) source_document_uom , case ooha.order_source_id when 10 then ooha.orig_sys_document_ref else null end int_requisition_num -- , hp.party_name customer_name , hca.account_number customer_num , hcsua.location customer_location , apsa.trx_number , apsa.trx_date , apsa.invoice_currency_code , case apsa.status when 'OP' then 'Open' when 'CL' then 'Closed' else apsa.status end status , apsa.amount_due_original , apsa.tax_original , apsa.exchange_rate , apsa.exchange_rate_type , apsa.exchange_date , rctla.line_number , msiv.segment1 item , xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type , msiv.description item_description , rctla.quantity_invoiced , rctla.uom_code , rctla.unit_selling_price , rctla.extended_amount , zl.tax tax_type , zl.tax_rate_code , zl.tax_rate , msiv.inventory_item_id , msiv.organization_id from ra_customer_trx_lines_all rctla , ra_customer_trx_all rcta , ar_payment_schedules_all apsa , hz_cust_accounts hca , hz_parties hp , hz_cust_site_uses_all hcsua , mtl_material_transactions mmt , oe_order_lines_all oola , oe_order_headers_all ooha , rcv_transactions rt , rcv_shipment_headers rsh , po_headers_all pha , po_lines_all pla , po_line_locations_all plla , mtl_system_items_vl msiv , mtl_units_of_measure mufm , hr_all_organization_units_vl haouv , (select distinct zl1.trx_id, zl1.trx_line_id , listagg (zl1.tax,'/ ') within group (order by zl1.tax) over (partition by zl1.trx_id,zl1.trx_line_id) tax , listagg (zl1.tax_rate_code,'/ ') within group (order by zl1.tax, zl1.tax_rate_code) over (partition by zl1.trx_id,zl1.trx_line_id) tax_rate_code , listagg (to_char(zl1.tax_rate),'/ ') within group (order by zl1.tax, zl1.tax_rate_code) over (partition by zl1.trx_id,zl1.trx_line_id) tax_rate from (select distinct zl2.trx_id, zl2.trx_line_id, zl2.tax, zl2.tax_rate_code, zl2.tax_rate from zx_lines zl2 where zl2.application_id = 222 and zl2.entity_code = 'TRANSACTIONS' and zl2.trx_level_type = 'LINE' ) zl1) zl where rctla.interface_line_context in ('INTERCOMPANY','GLOBAL_PROCUREMENT') and rctla.line_type = 'LINE' and rcta.customer_trx_id = rctla.customer_trx_id and apsa.customer_trx_id = rctla.customer_trx_id and hca.cust_account_id = rcta.bill_to_customer_id and hp.party_id = hca.party_id and hcsua.site_use_id = rcta.bill_to_site_use_id and haouv.organization_id = rcta.org_id and mmt.transaction_id = to_number(rctla.interface_line_attribute7) and msiv.organization_id = mmt.organization_id and msiv.inventory_item_id = mmt.inventory_item_id and oola.line_id (+) = decode(rctla.interface_line_context,'INTERCOMPANY',to_number(rctla.interface_line_attribute6),null) and ooha.header_id (+) = oola.header_id and rt.transaction_id (+) = mmt.rcv_transaction_id and rsh.shipment_header_id (+) = rt.shipment_header_id and pha.po_header_id (+) = rt.po_header_id and plla.line_location_id (+) = rt.po_line_location_id and pla.po_line_id (+) = rt.po_line_id and mufm.unit_of_measure (+) = plla.unit_meas_lookup_code and zl.trx_id (+) = rctla.customer_trx_id and zl.trx_line_id (+) = rctla.customer_trx_line_id ) 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 Y Y Y F Afficher les détails inter-sociétés US Show Intercompany Details 33 &l_category_set_joins and micv.organization_id = nvl(ap_inv.organization_id,po.organization_id) and micv.inventory_item_id = nvl(ap_inv.inventory_item_id,po.inventory_item_id) N F Afficher les détails inter-sociétés US Show Intercompany Details 34 &l_category_set_joins and micv.organization_id = nvl(nvl(ap_inv.organization_id,po.organization_id),ico.organization_id) and micv.inventory_item_id = nvl(nvl(ap_inv.inventory_item_id,po.inventory_item_id),ico.inventory_item_id) Y F Afficher les détails inter-sociétés US Show Intercompany Details 35 &l_ico_fr , ico Y F Afficher les détails inter-sociétés US Show Intercompany Details 36 &l_ico_join and ico.customer_trx_id(+)=ap_inv.customer_trx_id and ico.customer_trx_line_id(+)=ap_inv.customer_trx_line_id Y F Afficher les détails inter-sociétés US Show Intercompany Details 37 &l_ico_sel , ico.source_operating_unit ico_selling_procuring_ou , ico.customer_name ico_ar_customer_name , ico.customer_num ico_ar_customer_num , ico.customer_location ico_ar_customer_location , ico.trx_number ico_ar_inv_num , ico.trx_date ico_ar_inv_date , ico.invoice_currency_code ico_ar_inv_curr , ico.status ico_ar_inv_status , ico.amount_due_original ico_ar_inv_total_amount , ico.tax_original ico_ar_inv_tax_amount , ico.exchange_rate ico_ar_inv_exchange_rate , (select gdct.user_conversion_type from gl_daily_conversion_types gdct where ico.exchange_rate_type=gdct.conversion_type) ico_ar_inv_exchange_type , ico.exchange_date ico_ar_inv_exchange_date , ico.line_number ico_ar_inv_line_num , ico.quantity_invoiced ico_ar_inv_line_qty , ico.uom_code ico_ar_inv_line_uom , ico.unit_selling_price ico_ar_inv_line_unit_price , ico.extended_amount ico_ar_inv_line_amount , ico.tax_type ico_ar_inv_line_tax_type , ico.tax_rate_code ico_ar_inv_line_tax_rate_code , ico.tax_rate ico_ar_inv_line_tax_rate , ico.source_document ico_source_document_type , ico.source_document_date ico_source_document_date , ico.source_document_num ico_source_document_num , ico.source_document_line_num ico_source_document_line_num , ico.source_document_qty ico_source_document_qty , ico.source_document_uom ico_source_document_uom , ico.int_requisition_num ico_source_int_requisition_num Y F Afficher les détails inter-sociétés US Show Intercompany Details 38 &l_item_sel , nvl(ap_inv.item,po.item) item , nvl(ap_inv.item_type,po.item_type) item_type , nvl(ap_inv.item_description,po.item_description) item_description N F Afficher les détails inter-sociétés US Show Intercompany Details 39 &l_item_sel , nvl(nvl(ap_inv.item,po.item),ico.item) item , nvl(nvl(ap_inv.item_type,po.item_type),ico.item_type) item_type , nvl(nvl(ap_inv.item_description,po.item_description),ico.item_description) item_description Y F Afficher les détails inter-sociétés US Show Intercompany Details