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
Source: India - RG 23 D Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAIN23D_XML
DB package: JA_JAIN23D_XMLP_PKG
Run
JA India - RG 23 D - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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( |