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 |
