JA India - RG 23 D - draft

Description
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
JA India - RG 23 D - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
Copy
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