JA India - RG 23 D - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - RG 23 D Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAIN23D_XML
DB package: JA_JAIN23D_XMLP_PKG
SELECT        '1' query, a.register_id, a.fin_year,a.slno, a.transaction_type,
              a.comm_invoice_no||' '||a.receipt_boe_num||' '||a.comm_invoice_date  COL2,
               SUBSTR(b.vendor_name||' '||c.address_line1||' '||address_line2||' '||address_line3||' '||d.excise_duty_range||''||d.excise_duty_division||' '||d.excise_duty_comm, 1,255) COL3,
               a.quantity_received QTY,
               a.excise_duty_rate,
               a.rate_per_unit COL6,
               ROUND(a.duty_amount) COL7,
               NULL COL8,
               0 COL11,
               0 COL12,
               0 COL13,
              a.closing_balance_qty,
              a.remarks,
              a.consignee,
              jain_mtl.item_tariff,
              jain_mtl.item_folio,
              mtl.segment1,
              org.organization_name,
              loc.description loc_desc,
              loc.address_line_1,
              loc.address_line_2,
              loc.address_line_3,
              a.organization_id,
              a.Manufacturer_name  ,
              a.Manufacturer_Address ,
              a.Manufacturer_Rate_Amt_per_unit ,
              a.Qty_received_from_Manufacturer,
              a.Tot_amt_paid_to_Manufacturer,
              a.qty_to_adjust receipt_remaining_qty,
              a.reference_line_id, 
	JA_JAIN23D_XMLP_PKG.cf_sob_nameformula(a.organization_id) CF_sob_name, 
	JA_JAIN23D_XMLP_PKG.cf_col9formula(a.register_id, a.transaction_type, SUBSTR ( b.vendor_name || ' ' || c.address_line1 || ' ' || address_line2 || ' ' || address_line3 || ' ' || d.excise_duty_range || ' ' || d.excise_duty_division || ' ' || d.excise_duty_comm , 1 , 255 ), '1') CF_col9, 
	JA_JAIN23D_XMLP_PKG.cf_col10formula(a.register_id, a.transaction_type, SUBSTR ( b.vendor_name || ' ' || c.address_line1 || ' ' || address_line2 || ' ' || address_line3 || ' ' || d.excise_duty_range || ' ' || d.excise_duty_division || ' ' || d.excise_duty_comm , 1 , 255 )) CF_col10, 
	JA_JAIN23D_XMLP_PKG.cf_2formula(a.transaction_type, a.register_id, a.qty_to_adjust, '1', SUBSTR ( b.vendor_name || ' ' || c.address_line1 || ' ' || address_line2 || ' ' || address_line3 || ' ' || d.excise_duty_range || ' ' || d.excise_duty_division || ' ' || d.excise_duty_comm , 1 , 255 )) CF_2, 
	JA_JAIN23D_XMLP_PKG.cf_matched_shipped_qtyformula('1', a.register_id, a.qty_to_adjust, a.transaction_type) CF_Matched_Shipped_qty, 
	JA_JAIN23D_XMLP_PKG.cf_manu_nameformula(a.register_id, JA_JAIN23D_XMLP_PKG.cf_2formula(a.transaction_type, a.register_id, a.qty_to_adjust, '1', SUBSTR ( b.vendor_name || ' ' || c.address_line1 || ' ' || address_line2 || ' ' || address_line3 || ' ' || d.excise_duty_range || ' ' || d.excise_duty_division || ' ' || d.excise_duty_comm , 1 , 255 )), a.quantity_received, a.excise_duty_rate, a.rate_per_unit, ROUND ( a.duty_amount ), a.comm_invoice_no || ' ' || a.receipt_boe_num || ' ' || a.comm_invoice_date, a.Manufacturer_Address, a.Qty_received_from_Manufacturer, a.Manufacturer_Rate_Amt_per_unit, a.Tot_amt_paid_to_Manufacturer, a.Manufacturer_name) CF_manu_name, 
	JA_JAIN23D_XMLP_PKG.cf_issue_cess_amtformula(a.register_id) CF_ISSUE_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_issue_cess_amtformula(a.register_id),0) CF_ISSUE_CESS_AMT1, 
	JA_JAIN23D_XMLP_PKG.cf_issue_sh_cess_amtformula(a.register_id) CF_ISSUE_SH_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_issue_sh_cess_amtformula(a.register_id),0) CF_ISSUE_SH_CESS_AMT1,
	JA_JAIN23D_XMLP_PKG.cf_receipt_cess_amtformula(a.register_id) CF_RECEIPT_CESS_AMT, 
	nvl(JA_JAIN23D_XMLP_PKG.cf_receipt_cess_amtformula(a.register_id),0) CF_RECEIPT_CESS_AMT1, 
	JA_JAIN23D_XMLP_PKG.cf_receipt_sh_cess_amtformula(a.register_id) CF_RECEIPT_SH_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_receipt_sh_cess_amtformula(a.register_id),0) CF_RECEIPT_SH_CESS_AMT1, 
	JA_JAIN23D_XMLP_PKG.CP_SUPPLIER_TYPE_p CP_SUPPLIER_TYPE,
	JA_JAIN23D_XMLP_PKG.CP_manu_address_p CP_manu_address,
	JA_JAIN23D_XMLP_PKG.CP_qty_received_from_manu_p CP_qty_received_from_manu,
	JA_JAIN23D_XMLP_PKG.CP_manu_rate_amt_per_unit_p CP_manu_rate_amt_per_unit,
	JA_JAIN23D_XMLP_PKG.CP_tot_amt_paid_to_manu_p CP_tot_amt_paid_to_manu
FROM          JAI_CMN_RG_23D_TRXS a,
               po_vendors b,
               po_vendor_sites_all c,
               JAI_CMN_VENDOR_SITES d,
               mtl_system_items MTL,
               JAI_INV_ITM_SETUPS JAIN_MTL,
               org_organization_definitions org,
              hr_locations loc
WHERE a.vendor_id = b.vendor_id
AND       a.vendor_id = c.vendor_id
AND      a.vendor_site_id =  c.vendor_site_id
AND     a.vendor_id =  d.vendor_id
AND       a.vendor_site_id = d.vendor_site_id
AND       a.organization_id = mtl.organization_id
AND       a.inventory_item_id = mtl.inventory_item_id
AND       NVL(mtl.enabled_flag, 'Y')   = 'Y'
AND       a.organization_id = jain_mtl.organization_id
AND       a.inventory_item_id = jain_mtl.inventory_item_id
AND       a.organization_id = org.organization_id
AND       a.location_id = loc.location_id
AND       NVL(org.operating_unit, 0) = NVL(c.org_id, 0)
AND       a.transaction_type IN ('R', 'MR', 'CR', 'MCR')             
AND       a.inventory_item_id =NVL(:p_inventory_item_id, a.inventory_item_id)
AND       a.organization_id = NVL(:p_organization_id, a.organization_id)
AND       a.location_id = NVL(:p_location_id, a.location_id)
AND       TRUNC(a.creation_date) BETWEEN NVL(TRUNC(:cp_trn_from_date), TRUNC(a.creation_date)) AND NVL(TRUNC(:cp_trn_to_date), TRUNC(a.creation_date))
AND        a.vendor_id > 0
UNION
SELECT DISTINCT '2' query, a.register_id, a.fin_year,a.slno,  a.transaction_type,
              NULL COL2,
              SUBSTR(e.party_name||' '||g.address1||' '||g.address2||' '||g.address3||' '||g.address4||' '||g.city||' '||g.province||' '||g.country||' '||f.excise_duty_range||' '||f.excise_duty_division||' '||f.excise_duty_comm,1,255) COL3,
              0 QTY,
              a.excise_duty_rate,
              a.rate_per_unit COL6,
              0 COL7,
              a.comm_invoice_no||' '||a.comm_invoice_date COL8,
              DECODE(a.transaction_type, 'MI', a.quantity_issued,'MCR',a.oth_receipt_quantity,NULL) COL11,
              DECODE(a.transaction_type, 'MI', a.rate_per_unit, NULL) COL12,
              ROUND(DECODE(a.transaction_type, 'I', nvl(a.duty_amount,a.rate_per_unit * a.QUANTITY_ISSUED), 'MI', a.duty_amount, NULL)) COL13,
              a.closing_balance_qty,
              a.remarks,
              a.consignee,
              jain_mtl.item_tariff,
              jain_mtl.item_folio,
              mtl.segment1,
              org.organization_name,
              loc.description loc_desc,
              loc.address_line_1,
              loc.address_line_2,
              loc.address_line_3,
              a.organization_id,
              a.Manufacturer_name,
              a.Manufacturer_Address ,
              a.Manufacturer_Rate_Amt_per_unit ,
              a.Qty_received_from_Manufacturer,
              a.Tot_amt_paid_to_Manufacturer,
              a.qty_to_adjust receipt_remaining_qty,
              a.reference_line_id, 
	JA_JAIN23D_XMLP_PKG.cf_sob_nameformula(a.organization_id) CF_sob_name, 
	JA_JAIN23D_XMLP_PKG.cf_col9formula(a.register_id, a.transaction_type, SUBSTR(e.party_name||' '||g.address1||' '||g.address2||' '||g.address3||' '||g.address4||' '||g.city||' '||g.province||' '||g.country||' '||f.excise_duty_range||' '||f.excise_duty_division||' '||f.excise_duty_comm,1,255), '2') CF_col9, 
	JA_JAIN23D_XMLP_PKG.cf_col10formula(a.register_id, a.transaction_type,SUBSTR(e.party_name||' '||g.address1||' '||g.address2||' '||g.address3||' '||g.address4||' '||g.city||' '||g.province||' '||g.country||' '||f.excise_duty_range||' '||f.excise_duty_division||' '||f.excise_duty_comm,1,255)) CF_col10, 
	JA_JAIN23D_XMLP_PKG.cf_2formula(a.transaction_type, a.register_id, a.qty_to_adjust, '2',SUBSTR(e.party_name||' '||g.address1||' '||g.address2||' '||g.address3||' '||g.address4||' '||g.city||' '||g.province||' '||g.country||' '||f.excise_duty_range||' '||f.excise_duty_division||' '||f.excise_duty_comm,1,255)) CF_2, 
	JA_JAIN23D_XMLP_PKG.cf_matched_shipped_qtyformula('2', a.register_id, a.qty_to_adjust, a.transaction_type) CF_Matched_Shipped_qty, 
    JA_JAIN23D_XMLP_PKG.cf_manu_nameformula(a.register_id, JA_JAIN23D_XMLP_PKG.cf_2formula(a.transaction_type, a.register_id, a.qty_to_adjust, '2',SUBSTR(e.party_name||' '||g.address1||' '||g.address2||' '||g.address3||' '||g.address4||' '||g.city||' '||g.province||' '||g.country||' '||f.excise_duty_range||' '||f.excise_duty_division||' '||f.excise_duty_comm,1,255)), 0, a.excise_duty_rate, a.rate_per_unit, 0,NULL, a.Manufacturer_Address, a.Qty_received_from_Manufacturer, a.Manufacturer_Rate_Amt_per_unit, a.Tot_amt_paid_to_Manufacturer, a.Manufacturer_name) CF_manu_name, 
	JA_JAIN23D_XMLP_PKG.cf_issue_cess_amtformula(a.register_id) CF_ISSUE_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_issue_cess_amtformula(a.register_id),0) CF_ISSUE_CESS_AMT1,
	JA_JAIN23D_XMLP_PKG.cf_issue_sh_cess_amtformula(a.register_id) CF_ISSUE_SH_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_issue_sh_cess_amtformula(a.register_id),0) CF_ISSUE_SH_CESS_AMT1,
	JA_JAIN23D_XMLP_PKG.cf_receipt_cess_amtformula(a.register_id) CF_RECEIPT_CESS_AMT, 
	nvl(JA_JAIN23D_XMLP_PKG.cf_receipt_cess_amtformula(a.register_id),0) CF_RECEIPT_CESS_AMT1, 
	JA_JAIN23D_XMLP_PKG.cf_receipt_sh_cess_amtformula(a.register_id) CF_RECEIPT_SH_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_receipt_sh_cess_amtformula(a.register_id),0) CF_RECEIPT_SH_CESS_AMT1, 
	JA_JAIN23D_XMLP_PKG.CP_SUPPLIER_TYPE_p CP_SUPPLIER_TYPE,
	JA_JAIN23D_XMLP_PKG.CP_manu_address_p CP_manu_address,
	JA_JAIN23D_XMLP_PKG.CP_qty_received_from_manu_p CP_qty_received_from_manu,
	JA_JAIN23D_XMLP_PKG.CP_manu_rate_amt_per_unit_p CP_manu_rate_amt_per_unit,
	JA_JAIN23D_XMLP_PKG.CP_tot_amt_paid_to_manu_p CP_tot_amt_paid_to_manu
FROM          JAI_CMN_RG_23D_TRXS a,
              hz_parties e, hz_cust_accounts hzca,
              JAI_CMN_CUS_ADDRESSES f,
              hz_locations g, hz_party_sites hzps, hz_cust_acct_sites_all hzcas,
              mtl_system_items MTL,
              JAI_INV_ITM_SETUPS JAIN_MTL,
              org_organization_definitions org,
              hr_locations loc,
              hz_cust_site_uses_all h
WHERE     a.customer_id =   hzca.cust_account_id
AND       hzca.party_id =   e.party_id
AND       a.customer_id =   hzcas.cust_account_id
AND       a.customer_id =   f.customer_id
AND       a.organization_id = mtl.organization_id
AND       hzcas.cust_acct_site_id = h.cust_acct_site_id
AND       hzps.party_site_id = hzcas.party_site_id
AND       g.location_id  = hzps.location_id
AND       h.site_use_id =   a.ship_to_site_id
AND       f.address_id = hzcas.cust_acct_site_id
AND       a.inventory_item_id = mtl.inventory_item_id
AND       NVL(mtl.enabled_flag, 'Y')   = 'Y'
AND       a.organization_id = jain_mtl.organization_id
AND       a.inventory_item_id = jain_mtl.inventory_item_id
AND       a.organization_id = org.organization_id
AND       a.location_id = loc.location_id
AND       NVL(org.operating_unit, 0) = NVL(hzcas.org_id, 0)
AND       a.transaction_type  = 'MI'			
AND       NVL(A.CUSTOMER_ID,0) > 0
AND       a.inventory_item_id = NVL(:p_inventory_item_id, a.inventory_item_id)
AND       a.organization_id = NVL(:p_organization_id, a.organization_id)
AND       a.location_id = NVL(:p_location_id, a.location_id)
AND       TRUNC(a.creation_date) BETWEEN NVL(TRUNC(:cp_trn_from_date), TRUNC(a.creation_date)) AND NVL(TRUNC(:cp_trn_to_date), TRUNC(a.creation_date))
UNION
SELECT DISTINCT '3' query, a.register_id, a.fin_year, a.slno,  a.transaction_type,
              NULL COL2,
              SUBSTR(e.party_name||' '||g.address1||' '||g.address2||' '||g.address3||' '||g.address4||' '||g.city||' '||g.province||' '||g.country||' '||f.excise_duty_range||' '||f.excise_duty_division||' '||f.excise_duty_comm,1,255) COL3,
              0 QTY,
              a.excise_duty_rate,
              a.rate_per_unit COL6,
              0 COL7,
              a.comm_invoice_no||' '||a.comm_invoice_date COL8,
              group_line.quantity_issued COL11,
              a.rate_per_unit   COL12,
              ROUND( nvl(group_line.duty_amount, group_line.duty_amt_alternative) ) COL13,
              a.closing_balance_qty,
              a.remarks,
              a.consignee,
              jain_mtl.item_tariff,
              jain_mtl.item_folio,
              mtl.segment1,
              org.organization_name,
              loc.description loc_desc,
              loc.address_line_1,
              loc.address_line_2,
              loc.address_line_3,
              a.organization_id,
              a.Manufacturer_name,
              a.Manufacturer_Address ,
              a.Manufacturer_Rate_Amt_per_unit ,
              a.Qty_received_from_Manufacturer,
              a.Tot_amt_paid_to_Manufacturer,
              a.qty_to_adjust receipt_remaining_qty,
              a.reference_line_id, 
	JA_JAIN23D_XMLP_PKG.cf_sob_nameformula(a.organization_id) CF_sob_name, 
	JA_JAIN23D_XMLP_PKG.cf_col9formula(a.register_id, a.transaction_type,SUBSTR(e.party_name||' '||g.address1||' '||g.address2||' '||g.address3||' '||g.address4||' '||g.city||' '||g.province||' '||g.country||' '||f.excise_duty_range||' '||f.excise_duty_division||' '||f.excise_duty_comm,1,255), '3') CF_col9,
	JA_JAIN23D_XMLP_PKG.cf_col10formula(a.register_id, a.transaction_type,SUBSTR(e.party_name||' '||g.address1||' '||g.address2||' '||g.address3||' '||g.address4||' '||g.city||' '||g.province||' '||g.country||' '||f.excise_duty_range||' '||f.excise_duty_division||' '||f.excise_duty_comm,1,255)) CF_col10, 
	JA_JAIN23D_XMLP_PKG.cf_2formula(a.transaction_type, a.register_id, a.qty_to_adjust, '3',SUBSTR(e.party_name||' '||g.address1||' '||g.address2||' '||g.address3||' '||g.address4||' '||g.city||' '||g.province||' '||g.country||' '||f.excise_duty_range||' '||f.excise_duty_division||' '||f.excise_duty_comm,1,255)) CF_2, 
	JA_JAIN23D_XMLP_PKG.cf_matched_shipped_qtyformula('3', a.register_id, a.qty_to_adjust, a.transaction_type) CF_Matched_Shipped_qty, 
	JA_JAIN23D_XMLP_PKG.cf_manu_nameformula(a.register_id, JA_JAIN23D_XMLP_PKG.cf_2formula(a.transaction_type, a.register_id, a.qty_to_adjust, '3',SUBSTR(e.party_name||' '||g.address1||' '||g.address2||' '||g.address3||' '||g.address4||' '||g.city||' '||g.province||' '||g.country||' '||f.excise_duty_range||' '||f.excise_duty_division||' '||f.excise_duty_comm,1,255)), 0, a.excise_duty_rate, a.rate_per_unit,0, NULL, a.Manufacturer_Address, a.Qty_received_from_Manufacturer, a.Manufacturer_Rate_Amt_per_unit, a.Tot_amt_paid_to_Manufacturer, a.Manufacturer_name) CF_manu_name, 
	JA_JAIN23D_XMLP_PKG.cf_issue_cess_amtformula(a.register_id) CF_ISSUE_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_issue_cess_amtformula(a.register_id),0) CF_ISSUE_CESS_AMT1,
	JA_JAIN23D_XMLP_PKG.cf_issue_sh_cess_amtformula(a.register_id) CF_ISSUE_SH_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_issue_sh_cess_amtformula(a.register_id),0) CF_ISSUE_SH_CESS_AMT1,
	JA_JAIN23D_XMLP_PKG.cf_receipt_cess_amtformula(a.register_id) CF_RECEIPT_CESS_AMT, 
	nvl(JA_JAIN23D_XMLP_PKG.cf_receipt_cess_amtformula(a.register_id),0) CF_RECEIPT_CESS_AMT1, 
	JA_JAIN23D_XMLP_PKG.cf_receipt_sh_cess_amtformula(a.register_id) CF_RECEIPT_SH_CESS_AMT,
	NVL(JA_JAIN23D_XMLP_PKG.cf_receipt_sh_cess_amtformula(a.register_id),0) CF_RECEIPT_SH_CESS_AMT1, 
	JA_JAIN23D_XMLP_PKG.CP_SUPPLIER_TYPE_p CP_SUPPLIER_TYPE,
	JA_JAIN23D_XMLP_PKG.CP_manu_address_p CP_manu_address,
	JA_JAIN23D_XMLP_PKG.CP_qty_received_from_manu_p CP_qty_received_from_manu,
	JA_JAIN23D_XMLP_PKG.CP_manu_rate_amt_per_unit_p CP_manu_rate_amt_per_unit,
	JA_JAIN23D_XMLP_PKG.CP_tot_amt_paid_to_manu_p CP_tot_amt_paid_to_manu
FROM     JAI_CMN_RG_23D_TRXS a,
	( select max(register_id) register_id, sum(s.quantity_issued) quantity_issued,
			sum(s.duty_amount) duty_amount, sum(s.rate_per_unit * s.quantity_issued) duty_amt_alternative
		FROM JAI_CMN_RG_23D_TRXS s
		WHERE s.transaction_type = 'I'
		AND reference_line_id IS NOT NULL
		GROUP BY s.organization_id, s.location_id, s.inventory_item_id, s.comm_invoice_no, s.fin_year
	)  group_line,
               hz_parties e, hz_cust_accounts hzca,
               JAI_CMN_CUS_ADDRESSES f,
               hz_locations g, hz_party_sites hzps, hz_cust_acct_sites_all hzcas,
               mtl_system_items MTL,
               JAI_INV_ITM_SETUPS JAIN_MTL,
               org_organization_definitions org,
               hr_locations loc,
               hz_cust_site_uses_all h
WHERE     a.register_id = group_line.register_id
AND       a.customer_id = hzca.cust_account_id
AND       hzca.party_id = e.party_id 
AND       a.customer_id = hzcas.cust_account_id
AND       a.customer_id =   f.customer_id
AND       a.organization_id = mtl.organization_id
AND       g.location_id  = hzps.location_id
AND       hzps.party_site_id = hzcas.party_site_id
AND       hzcas.cust_acct_site_id = h.cust_acct_site_id
AND       h.site_use_id =   a.ship_to_site_id
AND       f.address_id = hzcas.cust_acct_site_id
AND       a.inventory_item_id = mtl.inventory_item_id
AND       NVL(mtl.enabled_flag, 'Y')   = 'Y'
AND       a.organization_id = jain_mtl.organization_id
AND       a.inventory_item_id = jain_mtl.inventory_item_id
AND       a.organization_id = org.organization_id
AND       a.location_id = loc.location_id
AND       NVL(org.operating_unit, 0) = NVL(hzcas.org_id, 0)
AND       a.transaction_type = 'I'
AND       NVL(A.CUSTOMER_ID,0) > 0
AND       a.inventory_item_id = NVL(:p_inventory_item_id, a.inventory_item_id)
AND       a.organization_id = NVL(:p_organization_id, a.organization_id)
AND       a.location_id = NVL(:p_location_id, a.location_id)
AND       TRUNC(a.creation_date) BETWEEN NVL(TRUNC(:cp_trn_from_date), TRUNC(a.creation_date)) AND NVL(TRUNC(:cp_trn_to_date), TRUNC(a.creation_date))
UNION
SELECT '4' query, a.register_id, a.fin_year,  a.slno, a.transaction_type,
               a.comm_invoice_no||' '||a.receipt_boe_num||' '||a.comm_invoice_date COL2,
               null COL3,
              DECODE(a.transaction_type, 'R',nvl(a.quantity_received,a.oth_receipt_quantity), 'MR',  a.quantity_received , 'CR', a.oth_receipt_quantity, 'MCR',  a.oth_receipt_quantity,'I',decode(a.quantity_issued,null,a.goods_issue_quantity,a.quantity_issued),'MRTV',a.quantity_received, 'RTV',a.quantity_received, 'MI',a.quantity_issued,NULL) QTY,    
              a.excise_duty_rate,
              a.rate_per_unit COL6,
              round(a.duty_amount) COL7,
              a.comm_invoice_no||' '||a.comm_invoice_date COL8,
              DECODE(a.transaction_type, 'I',abs(nvl(a.quantity_issued,A.GOODS_ISSUE_QUANTITY)), 'MI', a.quantity_issued,'MRTV',abs(a.QUANTITY_RECEIVED), 'RTV',abs(a.QUANTITY_RECEIVED), NULL) COL11,   
              a.rate_per_unit COL12,
              ROUND(a.duty_amount) COL13,
              a.closing_balance_qty,
              a.remarks,
              a.consignee,
              jain_mtl.item_tariff,
              jain_mtl.item_folio,
              mtl.segment1,
              org.organization_name,
              loc.description loc_desc,
              loc.address_line_1,
              loc.address_line_2,
              loc.address_line_3,
              a.organization_id,
              a.Manufacturer_name,
              a.Manufacturer_Address ,
              a.Manufacturer_Rate_Amt_per_unit ,
              a.Qty_received_from_Manufacturer,
              a.Tot_amt_paid_to_Manufacturer,
              a.qty_to_adjust receipt_remaining_qty,
              a.reference_line_id, 
	JA_JAIN23D_XMLP_PKG.cf_sob_nameformula(a.organization_id) CF_sob_name, 
	JA_JAIN23D_XMLP_PKG.cf_col9formula(a.register_id, a.transaction_type, null, '4') CF_col9, 
	JA_JAIN23D_XMLP_PKG.cf_col10formula(a.register_id, a.transaction_type, null) CF_col10, 
	JA_JAIN23D_XMLP_PKG.cf_2formula(a.transaction_type, a.register_id, a.qty_to_adjust, '4', null) CF_2, 
	JA_JAIN23D_XMLP_PKG.cf_matched_shipped_qtyformula('4', a.register_id, a.qty_to_adjust, a.transaction_type) CF_Matched_Shipped_qty, 
	JA_JAIN23D_XMLP_PKG.cf_manu_nameformula(a.register_id,JA_JAIN23D_XMLP_PKG.cf_2formula(a.transaction_type, a.register_id, a.qty_to_adjust, '4', null),DECODE(a.transaction_type, 'R',nvl(a.quantity_received,a.oth_receipt_quantity), 'MR',  a.quantity_received , 'CR', a.oth_receipt_quantity, 'MCR',  a.oth_receipt_quantity,'I',decode(a.quantity_issued,null,a.goods_issue_quantity,a.quantity_issued),'MRTV',a.quantity_received, 'RTV',a.quantity_received, 'MI',a.quantity_issued,NULL) , a.excise_duty_rate, a.rate_per_unit, ROUND ( a.duty_amount ), a.comm_invoice_no||' '||a.receipt_boe_num||' '||a.comm_invoice_date, a.Manufacturer_Address, a.Qty_received_from_Manufacturer, a.Manufacturer_Rate_Amt_per_unit, a.Tot_amt_paid_to_Manufacturer, a.Manufacturer_name) CF_manu_name, 
	JA_JAIN23D_XMLP_PKG.cf_issue_cess_amtformula(a.register_id) CF_ISSUE_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_issue_cess_amtformula(a.register_id),0) CF_ISSUE_CESS_AMT1,
	JA_JAIN23D_XMLP_PKG.cf_issue_sh_cess_amtformula(a.register_id) CF_ISSUE_SH_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_issue_sh_cess_amtformula(a.register_id),0) CF_ISSUE_SH_CESS_AMT1,
	JA_JAIN23D_XMLP_PKG.cf_receipt_cess_amtformula(a.register_id) CF_RECEIPT_CESS_AMT, 
	nvl(JA_JAIN23D_XMLP_PKG.cf_receipt_cess_amtformula(a.register_id),0) CF_RECEIPT_CESS_AMT1, 
	JA_JAIN23D_XMLP_PKG.cf_receipt_sh_cess_amtformula(a.register_id) CF_RECEIPT_SH_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_receipt_sh_cess_amtformula(a.register_id),0) CF_RECEIPT_SH_CESS_AMT1, 
	JA_JAIN23D_XMLP_PKG.CP_SUPPLIER_TYPE_p CP_SUPPLIER_TYPE,
	JA_JAIN23D_XMLP_PKG.CP_manu_address_p CP_manu_address,
	JA_JAIN23D_XMLP_PKG.CP_qty_received_from_manu_p CP_qty_received_from_manu,
	JA_JAIN23D_XMLP_PKG.CP_manu_rate_amt_per_unit_p CP_manu_rate_amt_per_unit,
	JA_JAIN23D_XMLP_PKG.CP_tot_amt_paid_to_manu_p CP_tot_amt_paid_to_manu
FROM     JAI_CMN_RG_23D_TRXS a,
               mtl_system_items MTL,
               JAI_INV_ITM_SETUPS JAIN_MTL,
               org_organization_definitions org,
              hr_locations loc
WHERE     a.organization_id = mtl.organization_id
AND       a.inventory_item_id = mtl.inventory_item_id
AND       NVL(mtl.enabled_flag, 'Y')   = 'Y'
AND       a.organization_id = jain_mtl.organization_id
AND       a.inventory_item_id = jain_mtl.inventory_item_id
AND       a.organization_id = org.organization_id
AND       a.location_id = loc.location_id
AND       (
           (a.transaction_type = 'MR' and (NVL(a.vendor_id, 0) = 0 OR a.vendor_id < 0 ))
           OR (a.transaction_type = 'R' and oth_receipt_id_ref IS NOT NULL)
           OR (a.transaction_type = 'R' AND a.vendor_id IS NULL
                 AND EXISTS (SELECT 1 FROM rcv_transactions
                    WHERE transaction_id = a.receipt_ref
                    AND requisition_line_id IS NULL AND source_document_code <> 'REQ')
            )
           OR (a.transaction_type IN ('RTV', 'MRTV' ))                           
           OR (a.transaction_type = 'I' AND NVL(A.CUSTOMER_ID, 0) = 0)
           OR (a.transaction_type IN ('MI','MCR'))
          )
AND       a.inventory_item_id =NVL(:p_inventory_item_id, a.inventory_item_id)
AND       a.organization_id = NVL(:p_organization_id, a.organization_id)
AND       a.location_id = NVL(:p_location_id, a.location_id)
AND       TRUNC(a.creation_date) BETWEEN NVL(TRUNC(:cp_trn_from_date), TRUNC(a.creation_date)) AND NVL(TRUNC(:cp_trn_to_date), TRUNC(a.creation_date))
union
SELECT 'ISO' query, a.register_id, a.fin_year, a.slno, a.transaction_type,
               a.comm_invoice_no||' '||a.receipt_boe_num||' '||a.comm_invoice_date COL2,
               null COL3,
              nvl(group_line.quantity_received, group_line.oth_receipt_quantity) QTY,
              a.excise_duty_rate,
              a.rate_per_unit COL6,
              round(group_line.duty_amount) COL7,
              a.comm_invoice_no||' '||a.comm_invoice_date COL8,
              0 COL11,
              a.rate_per_unit COL12,
              ROUND(group_line.duty_amount) COL13,
              a.closing_balance_qty,
              a.remarks,
              a.consignee,
              jain_mtl.item_tariff,
              jain_mtl.item_folio,
              mtl.segment1,
              org.organization_name,
              loc.description loc_desc,
              loc.address_line_1,
              loc.address_line_2,
              loc.address_line_3,
              a.organization_id,
              a.Manufacturer_name,
              a.Manufacturer_Address ,
              a.Manufacturer_Rate_Amt_per_unit ,
              a.Qty_received_from_Manufacturer,
              a.Tot_amt_paid_to_Manufacturer,
              group_line.qty_to_adjust receipt_remaining_qty,
              a.reference_line_id, 
	JA_JAIN23D_XMLP_PKG.cf_sob_nameformula(a.organization_id) CF_sob_name, 
	JA_JAIN23D_XMLP_PKG.cf_col9formula(a.register_id, a.transaction_type, null, 'ISO') CF_col9, 
	JA_JAIN23D_XMLP_PKG.cf_col10formula(a.register_id, a.transaction_type, null) CF_col10, 
	JA_JAIN23D_XMLP_PKG.cf_2formula(a.transaction_type, a.register_id, group_line.qty_to_adjust, 'ISO',null) CF_2, 
	JA_JAIN23D_XMLP_PKG.cf_matched_shipped_qtyformula('ISO', a.register_id, group_line.qty_to_adjust, a.transaction_type) CF_Matched_Shipped_qty, 
	JA_JAIN23D_XMLP_PKG.cf_manu_nameformula(a.register_id, JA_JAIN23D_XMLP_PKG.cf_2formula(a.transaction_type, a.register_id, group_line.qty_to_adjust, 'ISO',null),nvl(group_line.quantity_received, group_line.oth_receipt_quantity), a.excise_duty_rate, a.rate_per_unit, round(group_line.duty_amount),  a.comm_invoice_no||' '||a.receipt_boe_num||' '||a.comm_invoice_date, a.Manufacturer_Address, a.Qty_received_from_Manufacturer, a.Manufacturer_Rate_Amt_per_unit, a.Tot_amt_paid_to_Manufacturer, a.Manufacturer_name) CF_manu_name, 
	JA_JAIN23D_XMLP_PKG.cf_issue_cess_amtformula(a.register_id) CF_ISSUE_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_issue_cess_amtformula(a.register_id),0) CF_ISSUE_CESS_AMT1,
	JA_JAIN23D_XMLP_PKG.cf_issue_sh_cess_amtformula(a.register_id) CF_ISSUE_SH_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_issue_sh_cess_amtformula(a.register_id),0) CF_ISSUE_SH_CESS_AMT1,
	JA_JAIN23D_XMLP_PKG.cf_receipt_cess_amtformula(a.register_id) CF_RECEIPT_CESS_AMT, 
	nvl(JA_JAIN23D_XMLP_PKG.cf_receipt_cess_amtformula(a.register_id),0) CF_RECEIPT_CESS_AMT1, 
	JA_JAIN23D_XMLP_PKG.cf_receipt_sh_cess_amtformula(a.register_id) CF_RECEIPT_SH_CESS_AMT, 
	NVL(JA_JAIN23D_XMLP_PKG.cf_receipt_sh_cess_amtformula(a.register_id),0) CF_RECEIPT_SH_CESS_AMT1, 
	JA_JAIN23D_XMLP_PKG.CP_SUPPLIER_TYPE_p CP_SUPPLIER_TYPE,
	JA_JAIN23D_XMLP_PKG.CP_manu_address_p CP_manu_address,
	JA_JAIN23D_XMLP_PKG.CP_qty_received_from_manu_p CP_qty_received_from_manu,
	JA_JAIN23D_XMLP_PKG.CP_manu_rate_amt_per_unit_p CP_manu_rate_amt_per_unit,
	JA_JAIN23D_XMLP_PKG.CP_tot_amt_paid_to_manu_p CP_tot_amt_paid_to_manu
FROM     JAI_CMN_RG_23D_TRXS a,
	( select max(register_id) register_id, sum(a.quantity_received) quantity_received, sum(a.qty_to_adjust) qty_to_adjust,
			sum(a.oth_receipt_quantity) oth_receipt_quantity, sum(a.duty_amount) duty_amount
		FROM JAI_CMN_RG_23D_TRXS a, rcv_transactions b
		WHERE a.receipt_ref= b.transaction_id
		AND a.transaction_type = 'R'
		AND b.source_document_code = 'REQ'
		AND b.transaction_type = 'RECEIVE'
		AND b.requisition_line_id IS NOT NULL
		GROUP BY a.organization_id, a.location_id, a.inventory_item_id, a.comm_invoice_no, a.fin_year
	)  group_line,
               mtl_system_items MTL,
               JAI_INV_ITM_SETUPS JAIN_MTL,
               org_organization_definitions org,
              hr_locations loc
WHERE    a.register_id = group_line.register_id
AND       a.organization_id = mtl.organization_id
AND       a.inventory_item_id = mtl.inventory_item_id
AND       NVL(mtl.enabled_flag, 'Y')   = 'Y'
AND       a.organization_id = jain_mtl.organization_id
AND       a.inventory_item_id = jain_mtl.inventory_item_id
AND       a.organization_id = org.organization_id
AND       a.location_id = loc.location_id
AND       a.transaction_type = 'R'
 and   ( NVL(a.vendor_id,0) = 0 OR a.vendor_id <0 )
AND       a.inventory_item_id =NVL(:p_inventory_item_id, a.inventory_item_id)
AND       a.organization_id = NVL(:p_organization_id, a.organization_id)
AND       a.location_id = NVL(:p_location_id, a.location_id)
AND       TRUNC(a.creation_date) BETWEEN NVL(TRUNC(:cp_trn_from_date), TRUNC(a.creation_date)) AND NVL(TRUNC(:cp_trn_to_date), TRUNC(a.creation_date))
ORDER BY  22 ASC,23 ASC,24 ASC,25 ASC,26 ASC,21 ASC,27 ASC,20 ASC,19 ASC ,3,4
Parameter Name SQL text Validation
Inventory Item
 
LOV Oracle
To Date
 
Date
From Date
 
Date
Location
 
LOV Oracle
Organization
 
LOV Oracle