ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F0AC3869E053B46B63588994 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 Y JA JAIN23D_XML US JA India - RG 23 D - draft 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 ZHS JA 印度 - RG 23 D 报表- 不支持：已保留供将来使用 Application: 亚太地区本地化 Source: 印度 - RG 23 D 报表 (XML) - 不支持：已保留供将来使用 Short Name: JAIN23D_XML DB package: JA_JAIN23D_XMLP_PKG BI Publisher :cp_manu_address :cp_manu_rate_amt_per_unit :cp_qty_received_from_manu :cp_supplier_type :cp_tot_amt_paid_to_manu :cp_trn_from_date :cp_trn_to_date :last_page :p_conc_request_id :p_inventory_item_id :p_location_id :p_organization_id :p_trn_from_date :p_trn_to_date :prev_page :v_last_page :validation_flag 1 10 :p_organization_id LOV Oracle JA_IN_TRD_ORGANIZATION_NAME Y select hru.organization_id id, hru.name value, null description from hr_all_organization_units hru where exists ( select '1' from jai_cmn_inventory_orgs jhru where jhru.organization_id=hru.organization_id and nvl(jhru.trading,'N')='Y') order by hru.name Y US Organization ZHS 组织 2 20 :p_location_id LOV Oracle JA_IN_TRD_LOCATION_NAME Y select hrl.location_id id, hrl.description value, null description from hr_locations hrl where exists ( select '1' from jai_cmn_inventory_orgs jhru where jhru.organization_id=:$flex$.ja_in_trd_organization_name and jhru.location_id=hrl.location_id and nvl(jhru.trading,'N')='Y' ) order by hrl.description Y US Location ZHS 地点 3 30 :p_trn_from_date Date US From Date ZHS 自日期 4 40 :p_trn_to_date Date US To Date ZHS 至日期 5 50 :p_inventory_item_id LOV Oracle JA_IN_TRD_INV_ITEM Y select msik.inventory_item_id id, msik.concatenated_segments value, null description from mtl_system_items_kfv msik where exists ( select '1' from jai_inv_itm_setups jmsi where jmsi.organization_id=:$flex$.ja_in_trd_organization_name and jmsi.organization_id=msik.organization_id and jmsi.inventory_item_id=msik.inventory_item_id ) order by concatenated_segments US Inventory Item ZHS 库存项目