ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN BA0148F9C1CACD7DE0530100007F27FAY select x.* from ( select gl.name ledger , hou.name operating_unit , aia.source invoice_source , aps.vendor_name , aps.segment1 vendor_num , assa.vendor_site_code , aia.invoice_num , xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type , 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.client_time(aia.creation_date) invoice_creation_date , aida.accounting_date accounting_date , aida.period_name period , aia.invoice_amount invoice_amount , aia.total_tax_amount invoice_tax_amount , aia.amount_paid invoice_paid_amount , aia.invoice_currency_code , aia.exchange_rate , aia.exchange_rate_type , aia.exchange_date , nvl(aia.base_amount,aia.invoice_amount) invoice_acctd_amount , aia.cancelled_date invoice_cancelled_date , aia.cancelled_amount invoice_cancelled_amount , aila.line_number , msik.product , msik.product_description , aila.description invoice_line_description , aila.quantity_invoiced , nvl( ( select mufm.uom_code from mtl_units_of_measure mufm where mufm.unit_of_measure = aila.unit_meas_lookup_code and rownum=1 ) , aila.unit_meas_lookup_code ) unit_meas_lookup_code , aila.unit_price , aila.amount line_amount , aida.distribution_line_number , gcck.concatenated_segments distribution_account , aida.amount distribution_amount , aida.base_amount distribution_acctd_amount , aia.invoice_id , aia.reference_1 customer_trx_id , aila.reference_1 customer_trx_line_id &il_tax_columns_inner_amt &il_tax_columns_inner_rate &il_tax_columns_inner_tp from ap_invoices_all aia , ap_invoice_lines_all aila , ap_invoice_distributions_all aida , ap_suppliers aps , ap_supplier_sites_all assa , hr_operating_units hou , gl_ledgers gl , gl_code_combinations_kfv gcck , ( select msik.inventory_item_id , msik.concatenated_segments product , msik.description product_description from mtl_system_items_b_kfv msik where msik.organization_id = (select mp.master_organization_id from mtl_parameters mp where rownum=1 ) ) msik &il_from where aia.invoice_id = aila.invoice_id and aila.invoice_id = aida.invoice_id (+) and aila.line_number = aida.invoice_line_number (+) 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 = hou.organization_id and aila.inventory_item_id = msik.inventory_item_id (+) and aida.dist_code_combination_id = gcck.code_combination_id (+) and aia.source = 'Intercompany' and aila.line_type_lookup_code = 'ITEM' and nvl(aila.discarded_flag,'N') = 'N' and 1=1 &il_where ) x order by x.ledger , x.operating_unit , x.vendor_name , x.vendor_site_code , x.invoice_date , x.invoice_num , x.line_number , x.distribution_line_number Added India GST columns, displayed only when India Localization is installed. SQLAP (:Ledger is not null or :Operating_Unit is not null) AR AP Intercompany Invoice Details تفاصيل فاتورة AP Intercompany D AP Intercompany Invoice Details AP Intercompany-Rechnungsdetails E AP Intercompany Invoice Details Detalles de la factura interempresarial AP F AP Intercompany Invoice Details Détails de la facture interentreprises AP I AP Intercompany Invoice Details Dettagli fattura intercompany AP JA AP Intercompany Invoice Details AP社間請求書の詳細 KO AP Intercompany Invoice Details AP 내부 거래 송장 세부 정보 PTB AP Intercompany Invoice Details Detalhes da Nota Fiscal Intercompanhia AP RU AP Intercompany Invoice Details AP Детали счета компании S AP Intercompany Invoice Details AP Intercompany fakturauppgifter TR AP Intercompany Invoice Details AP Şirketler Arası Fatura Detayları US AP Intercompany Invoice Details AP Intercompany Invoice Details When India Localization is installed, the following GST columns are also included: CGST Amount, SGST Amount, IGST Amount, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code, CGST Rate, SGST Rate, IGST Rate, GST Supplier Registration No, GST Registration No, GST PAN No, GST TAN No. You must select a Ledger and/or Operating Unit ZHS AP Intercompany Invoice Details AP公司间发票详情 Enginatics &il_from &il_tax_columns_inner_amt &il_tax_columns_inner_rate &il_tax_columns_inner_tp &il_where 1=1 1 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &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, 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, max(jtlv.self_assessed_flag) reverse_charge_flag, 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, 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 where jrav.entity_id(+)=jtlv.tax_type_id and jrav.entity_code(+)='TAX_TYPE' and jrav.reporting_type_code(+)='TAX_TYPES_CLASSIFICATION' 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 3 &il_tax_columns_inner_amt ,il_tax.cgst_amount "CGST Amount", il_tax.sgst_amount "SGST Amount", il_tax.igst_amount "IGST Amount", il_tax.custom_amount, il_tax.unclassified_amount "Unclassified Tax Amount", il_tax.taxable_value, il_tax.hsn_sac_code "HSN SAC Code" Y US India Localization 4 &il_tax_columns_inner_rate ,il_tax.cgst_tax_rate "CGST Rate", il_tax.sgst_tax_rate "SGST Rate", il_tax.igst_tax_rate "IGST Rate" Y US India Localization 5 &il_tax_columns_inner_tp ,nvl(il_reg_site.gstin,il_reg_hdr.gstin_hdr) "GST Supplier Registration No", 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 6 &il_where and aia.vendor_site_id=il_reg_site.party_site_id(+) and aia.vendor_id=il_reg_hdr.party_id(+) and aia.invoice_id=il_tax.trx_id(+) Y US India Localization 7 10 1=1 gl.name=:ledger 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) 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) F Grand livre US Ledger 8 20 1=1 hou.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 F Unité d'exploitation US Operating Unit 9 30 1=1 aps.vendor_name=:supplier_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 10 40 1=1 aia.invoice_num=:invoice_number 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 11 50 1=1 aia.invoice_type_lookup_code=xxen_util.lookup_code(:invoice_type,'INVOICE TYPE',200) LOV AP Invoice Type B20C1D394FC36EDDE0530100007F5621 Y select flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type(+)='INVOICE TYPE' and flvv.view_application_id(+)=200 and flvv.security_group_id(+)=0 order by flvv.meaning F Type de facture US Invoice Type 12 60 1=1 aia.invoice_date>=:invoice_date_from Date F Date de facturation de US Invoice Date From 13 70 1=1 aia.invoice_date<:invoice_date_to+1 Date last_day(:$flex$.Invoice_Date_From) F Date de facturation au US Invoice Date To 14 80 1=1 aida.accounting_date>=:acc_date_from Date F Date comptable du US Accounting Date From 15 90 1=1 aida.accounting_date<:acc_date_to+1 Date last_day(:$flex$.Accounting_Date_From) F Date de comptabilisation au US Accounting Date To 16 100 1=1 aia.cancelled_date is null 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 select 'Y' from dual where :$flex$.invoice_number is null F Exclure Annulé US Exclude Cancelled