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 |
