ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEB979D2E0530100007F1FF2 HR Operating Unit 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 Fixed where clause duplication AR وحدة التشغيل مقيدة بالتحكم في الوصول متعدد المنظمات (MOAC) D Bedieneinheit eingeschränkt durch Multi Org Access Control (MOAC) E Unidad operativa restringida por el control de acceso multiorgánico (MOAC) F Unité opérationnelle restreinte par un contrôle d'accès multi-organismes (MOAC) I Unità operativa limitata dal controllo di accesso multiorgano (MOAC) JA 多機関アクセス制御(MOAC)で制限された操作ユニット KO 다중 조직 액세스 제어 (MOAC)에 의해 제한되는 운영 단위 PTB Unidade operacional restrita por controle de acesso multi org (MOAC) RU Операционное устройство, ограниченное мультиорганизованным контролем доступа (MOAC) S Styrenhet begränsad av multi-org access control (MOAC) TR Çoklu kuruluş erişim kontrolü (MOAC) tarafından kısıtlanan işletim birimi US Operating unit restricted by multi org access control (MOAC) ZHS 受多机构访问控制（MOAC）限制的操作单元。 41D8695AB15FCFB1E06362FB09051993 select haouv.name operating_unit, x.* from ( -- Branch 1: AP Invoice driven (outer join to GRN) select (select max(jprl.registration_number) from jai_party_reg_lines_v jprl, jai_party_regs_v jpr where jpr.party_reg_id=jprl.party_reg_id and jprl.regime_code='GST_India' and jprl.registration_type_name='GST Registration Number' and jpr.party_class_name='Supplier' and jpr.reg_class_code='THIRD_PARTY' and jprl.effective_to is null and jpr.party_id=pvsa1.vendor_id and jpr.party_site_id=pvsa1.vendor_site_id and jpr.org_id=pvsa1.org_id ) gstin_of_supplier, pgd.inputs_capital_goods, (select max(jtx.hsn_code) from jai_trx_lines_v jtx, jai_tax_lines_v jtlv2 where jtx.det_factor_id=jtlv2.det_factor_id and jtx.entity_code='AP_INVOICES' and jtlv2.tax_regime_code='GST_India' and jtx.trx_id=aila.invoice_id and jtx.trx_line_id=aila.line_number) hsn_code, (select max(jtx.sac_code) from jai_trx_lines_v jtx, jai_tax_lines_v jtlv2 where jtx.det_factor_id=jtlv2.det_factor_id and jtx.entity_code='AP_INVOICES' and jtlv2.tax_regime_code='GST_India' and jtx.trx_id=aila.invoice_id and jtx.trx_line_id=aila.line_number) sac_code, decode( (select jrc.reporting_code from jai_reporting_codes jrc, jai_tax_det_factors jtdf1 where jtdf1.trx_id=pgd.po_header_id and jtdf1.trx_line_id=pgd.po_line_id and jrc.reporting_code_id=jtdf1.sac_code_id and jtdf1.hsn_code_id is not null), null,decode( (select jra.reporting_code from jai_item_templ_hdr jith, mtl_system_items_b msib2, jai_reporting_associations jra, po_headers_all pha1, po_lines_all pla1 where jith.inventory_item_id=msib2.inventory_item_id and jith.organization_id=msib2.organization_id and jith.entity_type_code in ('ITEM_TEMPL_ASGN','ITEM') and jra.entity_id=jith.template_hdr_id and jra.effective_to is null and jra.reporting_type_name='HSN Code Type for GST' and jith.inventory_item_id=pla1.item_id and pha1.po_header_id=pla1.po_header_id and pha1.segment1=pgd.po_header_id and pla1.po_line_id=pgd.po_line_id and pha1.org_id=aia.org_id and rownum<=1), null,null,'Item Template'),'PO')|| decode( (select jrc.reporting_code from jai_reporting_codes jrc, jai_tax_det_factors jtdf where jrc.reporting_code_id=jtdf.sac_code_id and jtdf.trx_number=pgd.po_number and jtdf.trx_line_number=pgd.po_line_num and rownum<=1), null,decode( (select jra.reporting_code from jai_item_templ_hdr jith, mtl_system_items_b msib2, jai_reporting_associations jra, po_headers_all pha1, po_lines_all pla1 where jith.inventory_item_id=msib2.inventory_item_id and jith.organization_id=msib2.organization_id and jith.entity_type_code in ('ITEM_TEMPL_ASGN','ITEM') and jra.entity_id=jith.template_hdr_id and jra.effective_to is null and jra.reporting_type_name='SAC Code Type of Item for GST' and jith.inventory_item_id=pla1.item_id and pha1.po_header_id=pla1.po_header_id and pha1.po_header_id=pgd.po_header_id and pla1.po_line_id=pgd.po_line_id and pha1.org_id=aia.org_id and rownum<=1), null,null,'Item Template'),'PO') hsn_sac_type, to_char(aia.gl_date,'MON-YY') period_name, nvl(pgd.transaction_type,'AP_INVOICES') transaction_type, decode(pvsa1.country,'IN','Indigenous','Imported') type_of_purchase, pgd.project_number, pgd.task_number, pgd.receipt_num, pgd.receipt_creation_date, pgd.po_number, pgd.po_qty, pgd.po_line_num, pgd.po_creation_date, pgd.item, pgd.item_category, pgd.six_month_avg, pgd.one_year_avg, pgd.recd_qty, pgd.unit_price, pgd.unit_meas_lookup_code, pgd.po_amount, pgd.amount_local_currency, pgd.exchange_rate, pgd.transaction_date, -- Tax columns: DECODE(country,IN=AP_INVOICES,else=RCV_TRANSACTION) decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.recoverable_flag='Y' and jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.recoverable_flag='Y' and jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) rec_tax, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_CUSTOM' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_CUSTOM' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) basic_custom_duty, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code in ('SOCIAL_WELFARE_SURCHARGE','SOCIAL_WELFARE_SURCHARGE_DEALER') and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code in ('SOCIAL_WELFARE_SURCHARGE','SOCIAL_WELFARE_SURCHARGE_DEALER') and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) social_welfare_surcharge, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.party_type='Supplier' and jtlv.tax_type_code='GST_IGST_CUSTOM' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_IGST_CUSTOM' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) igst_on_custom, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_IMPORT_CLEARING_CHARGE' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_IMPORT_CLEARING_CHARGE' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) import_clearing_charges, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='FREIGHT_CHARGE' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='FREIGHT_CHARGE' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) freight_charges, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_LANDING_CHARGES' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_LANDING_CHARGES' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) landing_charges, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_OTHER_CHARGES' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_OTHER_CHARGES' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) other_c_f_charges, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code in ('CHARGES_TYPE','SERVICE_CHARGES_GST') and upper(jtlv.tax_rate_name) like '%SERVICE%CHARGES%' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code in ('CHARGES_TYPE','SERVICE_CHARGES_GST') and upper(jtlv.tax_rate_name) like '%SERVICE%CHARGES%' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) service_charges, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.party_type='Supplier' and jtlv.tax_type_code in ('CUSTOM_EDU_CESS','GST_CUS_EDU_CESS_DEALER') and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.party_type='Supplier' and jtlv.tax_type_code in ('CUSTOM_EDU_CESS','GST_CUS_EDU_CESS_DEALER') and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) custom_edu_cess, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.party_type='Supplier' and jtlv.tax_type_code in ('CUSTOM_SH_EDU_CESS','GST_CUS_SHE_EDU_CESS_DEALER') and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.party_type='Supplier' and jtlv.tax_type_code in ('CUSTOM_SH_EDU_CESS','GST_CUS_SHE_EDU_CESS_DEALER') and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) custom_sh_edu_cess, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.party_type='Supplier' and jtlv.tax_type_code not in ('TCS SECTION 206 C (1H) - Purchase','GST_IMPORT_CLEARING_CHARGE','FREIGHT_CHARGE','GST_LANDING_CHARGES','SOCIAL_WELFARE_SURCHARGE','SOCIAL_WELFARE_SURCHARGE_DEALER','GST_OTHER_CHARGES','CHARGES_TYPE','SERVICE_CHARGES_GST','CUSTOM_EDU_CESS','CUSTOM_SH_EDU_CESS','GST_CUSTOM','GST_IGST_CUSTOM','IGST','CGST','SGST','GST_CUS_SHE_EDU_CESS_DEALER','GST_CUS_EDU_CESS_DEALER') and upper(jtlv.tax_type_code) not like '%COMPENSATION%CESS%' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.party_type='Supplier' and jtlv.tax_type_code not in ('TCS SECTION 206 C (1H) - Purchase','GST_IMPORT_CLEARING_CHARGE','FREIGHT_CHARGE','GST_LANDING_CHARGES','SOCIAL_WELFARE_SURCHARGE','SOCIAL_WELFARE_SURCHARGE_DEALER','GST_OTHER_CHARGES','CHARGES_TYPE','SERVICE_CHARGES_GST','CUSTOM_EDU_CESS','CUSTOM_SH_EDU_CESS','GST_CUSTOM','GST_IGST_CUSTOM','IGST','CGST','SGST','GST_CUS_SHE_EDU_CESS_DEALER','GST_CUS_EDU_CESS_DEALER') and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) other_amount, nvl(pgd.item_description,aila.description) item_description, pgd.organization_id, nvl(pgd.org_name,(select haouv2.name from hr_all_organization_units_vl haouv2 where haouv2.organization_id=( select max(jtlv.organization_id) from jai_tax_lines_v jtlv where jtlv.trx_line_id=aila.line_number and jtlv.trx_id=aila.invoice_id and jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India'))) org_name, aia.org_id, aia.invoice_num, aia.doc_sequence_value ap_document_num, to_char(aia.invoice_date,'DD-MON-YYYY') invoice_date, to_char(aia.gl_date,'DD-MON-YYYY') gl_date, aia.invoice_type_lookup_code, aia.invoice_amount, aila.amount, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0)+aila.amount total_line_amount, aia.invoice_currency_code, pv1.vendor_name, pv1.segment1 vendor_code, pvsa1.vendor_site_code, pv1.vendor_type_lookup_code, (select max(jtlv.tax_invoice_num) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number and jtlv.tax_invoice_num is not null) supplier_invoice_number, aia.supplier_tax_invoice_number, to_char(aia.supplier_tax_invoice_date,'DD-MON-YYYY') supplier_tax_invoice_date, pgd.boe_invoice_number, -- IGST/CGST/SGST rate and amount: DECODE(country,IN=AP,else=RCV) decode(pvsa1.country,'IN', nvl((select sum(jtlv.actual_tax_rate) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='IGST' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(nvl(jtlv.actual_tax_rate,0)) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='IGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) igst_rate, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='IGST' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='IGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) igst_amount, decode(pvsa1.country,'IN', nvl((select sum(jtlv.actual_tax_rate) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='CGST' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.actual_tax_rate) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='CGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) cgst_rate, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='CGST' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='CGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) cgst_amount, decode(pvsa1.country,'IN', nvl((select sum(jtlv.actual_tax_rate) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='SGST' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.actual_tax_rate) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='SGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) sgst_rate, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='SGST' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='SGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) sgst_amount, -- Compensation cess decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and upper(jtlv.tax_type_code) like '%COMPENSATION%CESS%' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and upper(jtlv.tax_type_code) like '%COMPENSATION%CESS%' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) compensation_cess, -- TCS rate and amount decode(pvsa1.country,'IN', nvl((select sum(jtlv.actual_tax_rate) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='TCS SECTION 206 C (1H) - Purchase' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.actual_tax_rate) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='TCS SECTION 206 C (1H) - Purchase' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) tcs_rate, decode(pvsa1.country,'IN', nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='AP_INVOICES' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='TCS SECTION 206 C (1H) - Purchase' and jtlv.trx_id=aila.invoice_id and jtlv.trx_line_id=aila.line_number),0), nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='TCS SECTION 206 C (1H) - Purchase' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) ) tcs_amount, -- TDS columns (AP invoice only) (select jti.tds_tax_rate from jai_ap_tds_invoices jti, jai_ap_wthld_inv_taxes jawit where jti.invoice_id=jawit.invoice_id and jawit.actual_tax_id=jti.tds_tax_id and jawit.invoice_id=aila.invoice_id and jawit.invoice_line_number=aila.line_number and rownum<=1) tds_rate, (select sum(jawit.tax_amount) from jai_ap_tds_invoices jti, jai_ap_wthld_inv_taxes jawit where jti.invoice_id=jawit.invoice_id and jawit.actual_tax_id=jti.tds_tax_id and jawit.invoice_id=aila.invoice_id and jawit.invoice_line_number=aila.line_number) tds_amount, (select jti.tds_section from jai_ap_tds_invoices jti, jai_ap_wthld_inv_taxes jawit where jti.invoice_id=jawit.invoice_id and jawit.actual_tax_id=jti.tds_tax_id and jawit.invoice_id=aila.invoice_id and jawit.invoice_line_number=aila.line_number and rownum<=1) tds_section, pgd.import_cth_number, old_tax.old_bcd, old_tax.old_cst, old_tax.old_custom_cess, old_tax.old_custom_sh_edu_cess, old_tax.old_cvd, old_tax.old_excise, old_tax.old_import_clearing_charges, old_tax.old_kk_cess, old_tax.old_non_cvd, old_tax.old_non_excise, old_tax.old_sb_cess, old_tax.old_service_tax, old_tax.old_service_charges, old_tax.old_transition_other_charge, old_tax.old_vat, old_tax.old_vat_non_recoverable from ap_invoices_all aia, ap_invoice_lines_all aila, (select (select jl2.meaning from ja_lookups jl2 where jl2.lookup_type='JAI_ITEM_CLASS_CD' and jl2.enabled_flag='Y' and sysdate between nvl(jl2.start_date_active,sysdate) and nvl(jl2.end_date_active,sysdate) and jl2.lookup_code in (select jith2.item_classification from jai_item_templ_hdr jith2 where jith2.inventory_item_id=pla.item_id and jith2.organization_id=plla.ship_to_organization_id and rownum=1) ) inputs_capital_goods, rt.transaction_type, decode(pvsa.country,'IN','Indigenous','Imported') type_of_purchase, (select listagg(distinct ppa.segment1,', ') within group (order by ppa.segment1) from po_distributions_all pda, pa_projects_all ppa where pda.project_id=ppa.project_id and pda.po_line_id=pla.po_line_id) project_number, (select listagg(distinct pt.task_number,', ') within group (order by pt.task_number) from po_distributions_all pda, pa_tasks pt where pda.task_id=pt.task_id and pda.po_line_id=pla.po_line_id and pda.task_id is not null) task_number, rsh.receipt_num, to_char(rsh.creation_date,'DD-MON-YYYY') receipt_creation_date, pha.segment1 po_number, pla.quantity po_qty, pla.line_num po_line_num, to_char(pha.creation_date,'DD-MON-YYYY') po_creation_date, (select distinct msib2.segment1 from mtl_system_items_b msib2 where msib2.inventory_item_id=pla.item_id and rownum=1) item, (select mc.segment1 from mtl_item_categories mic, mtl_categories mc, mtl_category_sets_all_v mcsa where mic.category_id=mc.category_id and mic.category_set_id=mcsa.category_set_id and mcsa.category_set_name='XXI PRODUCT CLASS - BM2' and mic.inventory_item_id=pla.item_id and mic.organization_id=rsl.to_organization_id) item_category, (select round(nvl(cicv.item_cost,0),2) from cst_item_cost_type_v cicv where cicv.inventory_item_id=pla.item_id and cicv.organization_id=284 and cicv.cost_type='AVG'||'SEP'||substr((select gp.period_year from gl_periods gp where trunc(:date_from,'MM') between gp.start_date and gp.end_date and gp.period_set_name=(select gl.period_set_name from gl_ledgers gl where gl.ledger_id=fnd_profile.value('GL_SET_OF_BKS_ID')) and gp.period_type=(select gl.accounted_period_type from gl_ledgers gl where gl.ledger_id=fnd_profile.value('GL_SET_OF_BKS_ID')) and rownum=1),3) ) six_month_avg, (select round(nvl(cicv.item_cost,0),2) from cst_item_cost_type_v cicv where cicv.inventory_item_id=pla.item_id and cicv.organization_id=284 and cicv.cost_type='AVG'||'MAR'||substr((select gp.period_year from gl_periods gp where trunc(:date_from,'MM') between gp.start_date and gp.end_date and gp.period_set_name=(select gl.period_set_name from gl_ledgers gl where gl.ledger_id=fnd_profile.value('GL_SET_OF_BKS_ID')) and gp.period_type=(select gl.accounted_period_type from gl_ledgers gl where gl.ledger_id=fnd_profile.value('GL_SET_OF_BKS_ID')) and rownum=1)-1,3) ) one_year_avg, rt.quantity recd_qty, pla.unit_price, pla.unit_meas_lookup_code, pla.unit_price*pla.quantity po_amount, decode(pha.currency_code,'INR',rt.quantity*pla.unit_price,rt.quantity*pla.unit_price*rt.currency_conversion_rate) amount_local_currency, rt.currency_conversion_rate exchange_rate, rt.transaction_date, pla.item_description, plla.ship_to_organization_id organization_id, (select ood2.organization_name from org_organization_definitions ood2 where ood2.organization_id=plla.ship_to_organization_id) org_name, pha.org_id, pv.vendor_name, pv.segment1 vendor_code, pvsa.vendor_site_code, pv.vendor_type_lookup_code, (select distinct jcbh.invoice_num from jai_cmn_boe_hdrs_all jcbh, jai_cmn_boe_matchings jcbm where jcbh.boe_id=jcbm.boe_id and jcbm.shipment_header_id=rt.shipment_header_id and jcbm.shipment_line_id=rt.shipment_line_id) boe_invoice_number, (select distinct jcbh.import_sr_num from jai_cmn_boe_hdrs_all jcbh, jai_cmn_boe_matchings jcbm where jcbh.boe_id=jcbm.boe_id and jcbm.shipment_header_id=rt.shipment_header_id and jcbm.shipment_line_id=rt.shipment_line_id) import_cth_number, pha.po_header_id, pla.po_line_id, rt.transaction_id, rsl.shipment_line_id, rsh.shipment_header_id, pla.item_id, rsl.to_organization_id from rcv_transactions rt, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_vendors pv, po_vendor_sites_all pvsa, po_headers_all pha, po_lines_all pla, po_line_locations_all plla where rt.shipment_header_id=rsh.shipment_header_id and rt.shipment_line_id=rsl.shipment_line_id and rsh.shipment_header_id=rsl.shipment_header_id and pv.vendor_id=pvsa.vendor_id and rt.vendor_site_id=pvsa.vendor_site_id and rt.vendor_id=pv.vendor_id and rt.po_header_id=pha.po_header_id and rt.po_line_id=pla.po_line_id and rt.po_line_location_id=plla.line_location_id and pha.po_header_id=pla.po_header_id and plla.po_line_id=pla.po_line_id and plla.po_header_id=pla.po_header_id and pha.org_id=(select hou.organization_id from hr_operating_units hou where hou.name=:operating_unit) and rt.transaction_type='RECEIVE' and rt.source_document_code='PO' and rt.transaction_date>=:date_from and rt.transaction_date<:date_to+1 and not exists ( select null from rcv_transactions rtchk where rtchk.shipment_line_id=rsl.shipment_line_id and (rtchk.parent_transaction_id=rt.transaction_id and rtchk.transaction_type='CORRECT' or rtchk.transaction_type='RETURN TO VENDOR') ) ) pgd, po_vendors pv1, po_vendor_sites_all pvsa1, (select * from (select jtl.trx_id, jtl.trx_line_id, jtt.tax_type_code, jtl.trx_type, jtl.rounded_tax_amt_tax_curr from jai_tax_types jtt, jai_tax_lines_all jtl where jtt.regime_id=10020 and jtt.tax_type_id=jtl.tax_type_id) pivot (sum(rounded_tax_amt_tax_curr) for tax_type_code in ( 'BCD' as old_bcd,'CST' as old_cst,'CUSTOM_CESS' as old_custom_cess, 'CUSTOM_SH_EDU_CESS' as old_custom_sh_edu_cess,'CVD' as old_cvd,'Excise' as old_excise, 'Import Clearing charges' as old_import_clearing_charges,'KK_CESS' as old_kk_cess, 'NON_CVD' as old_non_cvd,'Non Excise' as old_non_excise, 'SB_CESS' as old_sb_cess,'Service Tax' as old_service_tax, 'Service_charges' as old_service_charges,'Transition_Other Charge' as old_transition_other_charge, 'VAT' as old_vat,'VAT_NON_RECOVERABLE' as old_vat_non_recoverable)) ) old_tax where aia.invoice_id=aila.invoice_id and aila.line_type_lookup_code<>'MISCELLANEOUS' and aila.line_type_lookup_code<>'PREPAY' and aia.cancelled_date is null and aia.org_id=(select hou.organization_id from hr_operating_units hou where hou.name=:operating_unit) and aia.gl_date>=:date_from and aia.gl_date<:date_to+1 and aila.rcv_transaction_id=pgd.transaction_id(+) and pv1.vendor_id=pvsa1.vendor_id and aia.vendor_id=pv1.vendor_id and aia.vendor_site_id=pvsa1.vendor_site_id and aia.org_id=pvsa1.org_id and aia.invoice_num not like '%TDS%' and aia.invoice_type_lookup_code not in ('PREPAYMENT','EXPENSE REPORT','PAYMENT REQUEST') and nvl(pv1.vendor_type_lookup_code,'VENDOR') in ('VENDOR','INDIA CUSTOMS AUTHORITY') and aila.invoice_id=old_tax.trx_id(+) and aila.line_number=old_tax.trx_line_id(+) union all -- Branch 2: GRN only (no AP invoice distribution) select pgd.gstin_of_supplier, (select jl2.meaning from ja_lookups jl2 where jl2.lookup_type='JAI_ITEM_CLASS_CD' and jl2.enabled_flag='Y' and sysdate between nvl(jl2.start_date_active,sysdate) and nvl(jl2.end_date_active,sysdate) and jl2.lookup_code in (select jith2.item_classification from jai_item_templ_hdr jith2 where jith2.inventory_item_id=pgd.item_id and jith2.organization_id=pgd.to_organization_id and rownum=1) ) inputs_capital_goods, to_char(nvl( (select jrc.reporting_code from jai_reporting_codes jrc, jai_tax_det_factors jtdf1 where jtdf1.trx_id=pgd.po_header_id and jtdf1.trx_line_id=pgd.po_line_id and jrc.reporting_code_id=jtdf1.sac_code_id and jtdf1.hsn_code_id is not null), (select jra.reporting_code from jai_item_templ_hdr jith, mtl_system_items_b msib2, jai_reporting_associations jra, po_headers_all pha1, po_lines_all pla1 where jith.inventory_item_id=msib2.inventory_item_id and jith.organization_id=msib2.organization_id and jith.entity_type_code in ('ITEM_TEMPL_ASGN','ITEM') and jra.entity_id=jith.template_hdr_id and jra.effective_to is null and jra.reporting_type_name='HSN Code Type for GST' and jith.inventory_item_id=pla1.item_id and pha1.po_header_id=pla1.po_header_id and pha1.po_header_id=pgd.po_header_id and pla1.po_line_id=pgd.po_line_id and pha1.org_id=pgd.org_id and rownum<=1) )) hsn_code, to_char(nvl( (select jrc.reporting_code from jai_reporting_codes jrc, jai_tax_det_factors jtdf1 where jtdf1.trx_id=pgd.po_header_id and jtdf1.trx_line_id=pgd.po_line_id and jrc.reporting_code_id=jtdf1.sac_code_id and jtdf1.hsn_code_id is not null), (select jra.reporting_code from jai_item_templ_hdr jith, mtl_system_items_b msib2, jai_reporting_associations jra, po_headers_all pha1, po_lines_all pla1 where jith.inventory_item_id=msib2.inventory_item_id and jith.organization_id=msib2.organization_id and jith.entity_type_code in ('ITEM_TEMPL_ASGN','ITEM') and jra.entity_id=jith.template_hdr_id and jra.effective_to is null and jra.reporting_type_name='SAC Code Type of Item for GST' and jith.inventory_item_id=pla1.item_id and pha1.po_header_id=pla1.po_header_id and pha1.po_header_id=pgd.po_header_id and pla1.po_line_id=pgd.po_line_id and pha1.org_id=pgd.org_id and rownum<=1) )) sac_code, decode( (select jrc.reporting_code from jai_reporting_codes jrc, jai_tax_det_factors jtdf1 where jtdf1.trx_id=pgd.po_header_id and jtdf1.trx_line_id=pgd.po_line_id and jrc.reporting_code_id=jtdf1.sac_code_id and jtdf1.hsn_code_id is not null), null,decode( (select jra.reporting_code from jai_item_templ_hdr jith, mtl_system_items_b msib2, jai_reporting_associations jra, po_headers_all pha1, po_lines_all pla1 where jith.inventory_item_id=msib2.inventory_item_id and jith.organization_id=msib2.organization_id and jith.entity_type_code in ('ITEM_TEMPL_ASGN','ITEM') and jra.entity_id=jith.template_hdr_id and jra.effective_to is null and jra.reporting_type_name='HSN Code Type for GST' and jith.inventory_item_id=pla1.item_id and pha1.po_header_id=pla1.po_header_id and pha1.po_header_id=pgd.po_header_id and pla1.po_line_id=pgd.po_line_id and pha1.org_id=pgd.org_id and rownum<=1), null,null,'Item Template'),'PO')|| decode( (select jrc.reporting_code from jai_reporting_codes jrc, jai_tax_det_factors jtdf where jrc.reporting_code_id=jtdf.sac_code_id and jtdf.trx_id=pgd.po_header_id and jtdf.trx_line_id=pgd.po_line_id and rownum<=1), null,decode( (select jra.reporting_code from jai_item_templ_hdr jith, mtl_system_items_b msib2, jai_reporting_associations jra, po_headers_all pha1, po_lines_all pla1 where jith.inventory_item_id=msib2.inventory_item_id and jith.organization_id=msib2.organization_id and jith.entity_type_code in ('ITEM_TEMPL_ASGN','ITEM') and jra.entity_id=jith.template_hdr_id and jra.effective_to is null and jra.reporting_type_name='SAC Code Type of Item for GST' and jith.inventory_item_id=pla1.item_id and pha1.po_header_id=pla1.po_header_id and pha1.po_header_id=pgd.po_header_id and pla1.po_line_id=pgd.po_line_id and pha1.org_id=pgd.org_id and rownum<=1), null,null,'Item Template'),'PO') hsn_sac_type, to_char(pgd.transaction_date,'MON-YY') period_name, pgd.transaction_type, pgd.type_of_purchase, pgd.project_number, pgd.task_number, pgd.receipt_num, pgd.receipt_creation_date, pgd.po_number, pgd.po_qty, pgd.po_line_num, pgd.po_creation_date, pgd.item, pgd.item_category, pgd.six_month_avg, pgd.one_year_avg, pgd.recd_qty, pgd.unit_price, pgd.unit_meas_lookup_code, pgd.po_amount, pgd.amount_local_currency, pgd.exchange_rate, pgd.transaction_date, -- Tax columns: all RCV_TRANSACTION entity nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.recoverable_flag='Y' and jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) rec_tax, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_CUSTOM' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) basic_custom_duty, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code in ('SOCIAL_WELFARE_SURCHARGE','SOCIAL_WELFARE_SURCHARGE_DEALER') and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) social_welfare_surcharge, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_IGST_CUSTOM' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) igst_on_custom, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_IMPORT_CLEARING_CHARGE' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) import_clearing_charges, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='FREIGHT_CHARGE' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) freight_charges, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_LANDING_CHARGES' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) landing_charges, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='GST_OTHER_CHARGES' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) other_c_f_charges, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code in ('CHARGES_TYPE','SERVICE_CHARGES_GST') and upper(jtlv.tax_rate_name) like '%SERVICE%CHARGES%' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) service_charges, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.party_type='Supplier' and jtlv.tax_type_code in ('CUSTOM_EDU_CESS','GST_CUS_EDU_CESS_DEALER') and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) custom_edu_cess, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.party_type='Supplier' and jtlv.tax_type_code in ('CUSTOM_SH_EDU_CESS','GST_CUS_SHE_EDU_CESS_DEALER') and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) custom_sh_edu_cess, nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.party_type='Supplier' and jtlv.tax_type_code not in ('TCS SECTION 206 C (1H) - Purchase','GST_IMPORT_CLEARING_CHARGE','FREIGHT_CHARGE','GST_LANDING_CHARGES','SOCIAL_WELFARE_SURCHARGE','SOCIAL_WELFARE_SURCHARGE_DEALER','GST_OTHER_CHARGES','CHARGES_TYPE','SERVICE_CHARGES_GST','CUSTOM_EDU_CESS','CUSTOM_SH_EDU_CESS','GST_CUSTOM','GST_IGST_CUSTOM','IGST','CGST','SGST','GST_CUS_SHE_EDU_CESS_DEALER','GST_CUS_EDU_CESS_DEALER') and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) other_amount, pgd.item_description, pgd.organization_id, pgd.org_name, pgd.org_id, to_char(null) invoice_num, to_number(null) ap_document_num, to_char(null) invoice_date, to_char(null) gl_date, to_char(null) invoice_type_lookup_code, to_number(null) invoice_amount, to_number(null) amount, 0 total_line_amount, to_char(null) invoice_currency_code, pgd.vendor_name, pgd.vendor_code, pgd.vendor_site_code, pgd.vendor_type_lookup_code, (select max(jtlv.tax_invoice_num) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id and jtlv.tax_invoice_num is not null and jtlv.trx_type=pgd.transaction_type) supplier_invoice_number, to_char(null) supplier_tax_invoice_number, to_char(null) supplier_tax_invoice_date, pgd.boe_invoice_number, -- IGST/CGST/SGST rate and amount: all RCV_TRANSACTION nvl((select sum(nvl(jtlv.actual_tax_rate,0)) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='IGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) igst_rate, nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='IGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) igst_amount, nvl((select sum(jtlv.actual_tax_rate) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='CGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) cgst_rate, nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='CGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) cgst_amount, nvl((select sum(jtlv.actual_tax_rate) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='SGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) sgst_rate, nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='SGST' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) sgst_amount, -- Compensation cess nvl((select sum(jtlv.rounded_tax_amt_tax_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and upper(jtlv.tax_type_code) like '%COMPENSATION%CESS%' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) compensation_cess, -- TCS rate and amount nvl((select sum(jtlv.actual_tax_rate) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='TCS SECTION 206 C (1H) - Purchase' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) tcs_rate, nvl((select sum(jtlv.rounded_tax_amt_trx_curr) from jai_tax_lines_v jtlv where jtlv.entity_code='RCV_TRANSACTION' and jtlv.tax_regime_code='GST_India' and jtlv.tax_type_code='TCS SECTION 206 C (1H) - Purchase' and jtlv.trx_type=pgd.transaction_type and jtlv.trx_id=pgd.shipment_header_id and jtlv.trx_line_id=pgd.shipment_line_id),0) tcs_amount, -- TDS columns: null for GRN-only to_number(null) tds_rate, to_number(null) tds_amount, to_char(null) tds_section, pgd.import_cth_number, old_tax.old_bcd, old_tax.old_cst, old_tax.old_custom_cess, old_tax.old_custom_sh_edu_cess, old_tax.old_cvd, old_tax.old_excise, old_tax.old_import_clearing_charges, old_tax.old_kk_cess, old_tax.old_non_cvd, old_tax.old_non_excise, old_tax.old_sb_cess, old_tax.old_service_tax, old_tax.old_service_charges, old_tax.old_transition_other_charge, old_tax.old_vat, old_tax.old_vat_non_recoverable from (select (select max(jprl.registration_number) from jai_party_reg_lines_v jprl, jai_party_regs_v jpr where jpr.party_reg_id=jprl.party_reg_id and jprl.regime_code='GST_India' and jprl.registration_type_name='GST Registration Number' and jpr.party_class_name='Supplier' and jpr.reg_class_code='THIRD_PARTY' and jprl.effective_to is null and jpr.party_id=pvsa.vendor_id and jpr.party_site_id=pvsa.vendor_site_id and jpr.org_id=pvsa.org_id ) gstin_of_supplier, rt.transaction_type, decode(pvsa.country,'IN','Indigenous','Imported') type_of_purchase, (select listagg(distinct ppa.segment1,', ') within group (order by ppa.segment1) from po_distributions_all pda, pa_projects_all ppa where pda.project_id=ppa.project_id and pda.po_line_id=pla.po_line_id) project_number, (select listagg(distinct pt.task_number,', ') within group (order by pt.task_number) from po_distributions_all pda, pa_tasks pt where pda.task_id=pt.task_id and pda.po_line_id=pla.po_line_id and pda.task_id is not null) task_number, rsh.receipt_num, to_char(rsh.creation_date,'DD-MON-YYYY') receipt_creation_date, pha.segment1 po_number, pla.quantity po_qty, pla.line_num po_line_num, to_char(pha.creation_date,'DD-MON-YYYY') po_creation_date, (select distinct msib2.segment1 from mtl_system_items_b msib2 where msib2.inventory_item_id=pla.item_id and rownum=1) item, (select mc.segment1 from mtl_item_categories mic, mtl_categories mc, mtl_category_sets_all_v mcsa where mic.category_id=mc.category_id and mic.category_set_id=mcsa.category_set_id and mcsa.category_set_name='XXI PRODUCT CLASS - BM2' and mic.inventory_item_id=pla.item_id and mic.organization_id=rsl.to_organization_id) item_category, (select round(nvl(cicv.item_cost,0),2) from cst_item_cost_type_v cicv where cicv.inventory_item_id=pla.item_id and cicv.organization_id=284 and cicv.cost_type='AVG'||'SEP'||substr((select gp.period_year from gl_periods gp where trunc(:date_from,'MM') between gp.start_date and gp.end_date and gp.period_set_name=(select gl.period_set_name from gl_ledgers gl where gl.ledger_id=fnd_profile.value('GL_SET_OF_BKS_ID')) and gp.period_type=(select gl.accounted_period_type from gl_ledgers gl where gl.ledger_id=fnd_profile.value('GL_SET_OF_BKS_ID')) and rownum=1),3) ) six_month_avg, (select round(nvl(cicv.item_cost,0),2) from cst_item_cost_type_v cicv where cicv.inventory_item_id=pla.item_id and cicv.organization_id=284 and cicv.cost_type='AVG'||'MAR'||substr((select gp.period_year from gl_periods gp where trunc(:date_from,'MM') between gp.start_date and gp.end_date and gp.period_set_name=(select gl.period_set_name from gl_ledgers gl where gl.ledger_id=fnd_profile.value('GL_SET_OF_BKS_ID')) and gp.period_type=(select gl.accounted_period_type from gl_ledgers gl where gl.ledger_id=fnd_profile.value('GL_SET_OF_BKS_ID')) and rownum=1)-1,3) ) one_year_avg, rt.quantity recd_qty, pla.unit_price, pla.unit_meas_lookup_code, pla.unit_price*pla.quantity po_amount, decode(pha.currency_code,'INR',rt.quantity*pla.unit_price,rt.quantity*pla.unit_price*rt.currency_conversion_rate) amount_local_currency, rt.currency_conversion_rate exchange_rate, rt.transaction_date, pla.item_description, plla.ship_to_organization_id organization_id, (select ood2.organization_name from org_organization_definitions ood2 where ood2.organization_id=plla.ship_to_organization_id) org_name, pha.org_id, pv.vendor_name, pv.segment1 vendor_code, pvsa.vendor_site_code, pv.vendor_type_lookup_code, (select distinct jcbh.invoice_num from jai_cmn_boe_hdrs_all jcbh, jai_cmn_boe_matchings jcbm where jcbh.boe_id=jcbm.boe_id and jcbm.shipment_header_id=rt.shipment_header_id and jcbm.shipment_line_id=rt.shipment_line_id) boe_invoice_number, (select distinct jcbh.import_sr_num from jai_cmn_boe_hdrs_all jcbh, jai_cmn_boe_matchings jcbm where jcbh.boe_id=jcbm.boe_id and jcbm.shipment_header_id=rt.shipment_header_id and jcbm.shipment_line_id=rt.shipment_line_id) import_cth_number, pha.po_header_id, pla.po_line_id, rt.transaction_id, rsl.shipment_line_id, rsh.shipment_header_id, pla.item_id, rsl.to_organization_id from rcv_transactions rt, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_vendors pv, po_vendor_sites_all pvsa, po_headers_all pha, po_lines_all pla, po_line_locations_all plla where rt.shipment_header_id=rsh.shipment_header_id and rt.shipment_line_id=rsl.shipment_line_id and rsh.shipment_header_id=rsl.shipment_header_id and pv.vendor_id=pvsa.vendor_id and rt.vendor_site_id=pvsa.vendor_site_id and rt.vendor_id=pv.vendor_id and rt.po_header_id=pha.po_header_id and rt.po_line_id=pla.po_line_id and rt.po_line_location_id=plla.line_location_id and pha.po_header_id=pla.po_header_id and plla.po_line_id=pla.po_line_id and plla.po_header_id=pla.po_header_id and pha.org_id=(select hou.organization_id from hr_operating_units hou where hou.name=:operating_unit) and rt.transaction_type='RECEIVE' and rt.source_document_code='PO' and rt.transaction_date>=:date_from and rt.transaction_date<:date_to+1 and not exists ( select null from rcv_transactions rtchk where rtchk.shipment_line_id=rsl.shipment_line_id and (rtchk.parent_transaction_id=rt.transaction_id and rtchk.transaction_type='CORRECT' or rtchk.transaction_type='RETURN TO VENDOR') ) ) pgd, (select * from (select jtl.trx_id, jtl.trx_line_id, jtt.tax_type_code, jtl.trx_type, jtl.rounded_tax_amt_tax_curr from jai_tax_types jtt, jai_tax_lines_all jtl where jtt.regime_id=10020 and jtt.tax_type_id=jtl.tax_type_id) pivot (sum(rounded_tax_amt_tax_curr) for tax_type_code in ( 'BCD' as old_bcd,'CST' as old_cst,'CUSTOM_CESS' as old_custom_cess, 'CUSTOM_SH_EDU_CESS' as old_custom_sh_edu_cess,'CVD' as old_cvd,'Excise' as old_excise, 'Import Clearing charges' as old_import_clearing_charges,'KK_CESS' as old_kk_cess, 'NON_CVD' as old_non_cvd,'Non Excise' as old_non_excise, 'SB_CESS' as old_sb_cess,'Service Tax' as old_service_tax, 'Service_charges' as old_service_charges,'Transition_Other Charge' as old_transition_other_charge, 'VAT' as old_vat,'VAT_NON_RECOVERABLE' as old_vat_non_recoverable)) ) old_tax where pgd.transaction_date>=:date_from and pgd.transaction_date<:date_to+1 and pgd.shipment_header_id=old_tax.trx_id(+) and pgd.shipment_line_id=old_tax.trx_line_id(+) and pgd.transaction_type=old_tax.trx_type(+) and not exists (select null from ap_invoice_distributions_all aida where aida.rcv_transaction_id=pgd.transaction_id) ) x, hr_all_organization_units_vl haouv where x.org_id=haouv.organization_id(+) and 1=1 v6: Full UNION ALL rewrite matching original RDF structure. Branch 1=AP invoice driven with DECODE(country) tax pattern. Branch 2=GRN only with RCV_TRANSACTION tax. Includes HSN_SAC_TYPE, compensation cess, TCS, TDS, OLD_TAX pivot. Y US TMEIC Purchase Register Purchase Register showing GRN, PO, AP invoice and GST tax details with UNION ALL structure matching TMIND_PURREG_REP. 1=1 :date_from :date_to 1 10 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)) Y US Operating Unit 2 20 :date_from Date Y US Date From 3 30 :date_to Date last_day(:$flex$.date_from) Y US Date To :$flex$.date_from Date From Y Date To