JA India - RG23C Part I Report - draft - draft

SELECT a.slno,a.register_id,a.transaction_type,a.vendor_id,a.vendor_site_id,a.receipt_ref receipt_id,
a.organization_id,
a.location_id,
org.organization_name,
loc.description,
loc.address_line_1,
loc.address_line_2,
loc.address_line_3,
hr_org.excise_duty_comm,
hr_org.excise_duty_division,
hr_org.excise_duty_range,
hr_org.ec_code,
hr_org.excise_duty_circle,
a.slno,
a.transaction_date,
a.inventory_item_id,
b.description descr,
substr(a.identification_no,1,10)||' '||
substr(a.identification_mark,1,10)||' '||
substr(a.brand_name,1,10) Ident_Dtls,
DECODE(a.transaction_type,'R',a.quantity_received,'PR',a.quantity_received,'IOR',a.quantity_received,'CR',a.quantity_received,'RA',a.quantity_received,'Y',a.quantity_received) rec_qty,
DECODE(a.transaction_type,'R',a.oth_receipt_id_ref,'PR',a.oth_receipt_id_ref,'IOR',a.oth_receipt_id_ref,'CR',a.oth_receipt_id_ref,'RA',a.oth_receipt_id_ref,'Y',a.oth_receipt_id_ref) excise_invoice_id,
DECODE(a.transaction_type,'R',a.oth_receipt_date,'PR',a.oth_receipt_date,'IOR',a.oth_receipt_date,'CR',a.oth_receipt_date,'RA',a.oth_receipt_date,'Y',a.oth_receipt_date) excise_invoice_date,
DECODE(a.transaction_type,'R',SUBSTR(c.vendor_name,1,20)||' '||
SUBSTR(d.address_line1,1,10)||' '||
SUBSTR(d.address_line2,1,10)||' '||
SUBSTR(d.city,1,9)||' '||
SUBSTR(d.state,1,5)||' '||
SUBSTR(d.zip,1,6)||' '||
SUBSTR(d.province,1,10)||' '||
SUBSTR(d.country,1,10),
'RTV',SUBSTR(c.vendor_name,1,20)||' '||
SUBSTR(d.address_line1,1,10)||' '||
SUBSTR(d.address_line2,1,10)||' '||
SUBSTR(d.city,1,9)||' '||
SUBSTR(d.state,1,5)||' '||
SUBSTR(d.zip,1,6)||' '||
SUBSTR(d.province,1,10)||' '||
SUBSTR(d.country,1,10),
'V',Substr(e.organization_name,1,25)) Vend_Dtls,
SUBSTR(a.range_no,1,10) Range1,
SUBSTR(a.division_no,1,10) Division1,
a.date_of_verification,
DECODE(a.transaction_type,'I',SUBSTR(a.goods_issue_id_ref,1,10),'IOI',SUBSTR(a.goods_issue_id_ref,1,10),'PI',SUBSTR(a.goods_issue_id_ref,1,10),'RI',SUBSTR(a.goods_issue_id_ref,1,10),'RTV',SUBSTR(a.goods_issue_id_ref,1,10),'X',SUBSTR(a.goods_issue_id_ref,1,10),
'V',SUBSTR(a.goods_issue_id_ref,1,10)) goods_issue_id_ref,
DECODE(a.transaction_type,'I',a.goods_issue_date,'IOI',a.goods_issue_date,'PI',a.goods_issue_date,'RI',a.goods_issue_date,'RTV',a.goods_issue_date,'X',a.goods_issue_date,'V',a.goods_issue_date) goods_issue_date,
DECODE(a.transaction_type,'I',a.goods_issue_quantity,'PI',a.goods_issue_quantity,'IOI',a.goods_issue_quantity,
'RI',a.goods_issue_quantity,'RTV',a.goods_issue_quantity) goods_issue_qty,
SUBSTR(a.place_of_install,1,10) place_of_install,
a.date_of_installation,
a.date_of_commission,
DECODE(a.transaction_type,'I',SUBSTR(a.sales_invoice_no,1,10),'IOI',SUBSTR(a.sales_invoice_no,1,10),'PI',SUBSTR(a.sales_invoice_no,1,10),'RI',SUBSTR(a.sales_invoice_no,1,10),'RTV',SUBSTR(a.sales_invoice_no,1,10),
'X',SUBSTR(a.sales_invoice_no,1,10),
'V',SUBSTR(a.sales_invoice_no,1,10)) sales_invoice_no,
DECODE(a.transaction_type,'I',a.sales_invoice_date,'IOI',a.sales_invoice_date,'PI',a.sales_invoice_date,'RI',a.sales_invoice_date,'RTV',a.sales_invoice_date,'X',a.sales_invoice_date,'V',a.sales_invoice_date) sales_invoice_date,
DECODE(a.transaction_type,'I',a.sales_invoice_quantity,'IOI',a.sales_invoice_quantity,'PI',a.sales_invoice_quantity,
'RI',a.sales_invoice_quantity,'RTV',a.sales_invoice_quantity,'X',a.sales_invoice_quantity,
'V',a.sales_invoice_quantity) sales_invoice_qty,
DECODE(a.transaction_type,'I',SUBSTR(a.oth_receipt_id_ref,1,10),'IOI',SUBSTR(a.oth_receipt_id_ref,1,10),'PI',SUBSTR(a.oth_receipt_id_ref,1,10),'RI',SUBSTR(a.oth_receipt_id_ref,1,10),'RTV',SUBSTR(a.oth_receipt_id_ref,1,10),
'X',SUBSTR(a.oth_receipt_id_ref,1,10),
'V',SUBSTR(a.oth_receipt_id_ref,1,10)) oth_receipt_id,
DECODE(a.transaction_type,'I',a.oth_receipt_date,'IOI',a.oth_receipt_date,'PI',a.oth_receipt_date,'RI',a.oth_receipt_date,
'RTV',a.oth_receipt_date,'X',a.oth_receipt_date,
'V',a.oth_receipt_date) oth_receipt_date,
decode(a.transaction_type,'I',a.oth_receipt_quantity,'PI',a.oth_receipt_quantity,'RI',a.oth_receipt_quantity,'IOI',a.oth_receipt_quantity,'RTV',a.oth_receipt_quantity,
'X',a.oth_receipt_quantity,
'V',a.oth_receipt_quantity) oth_receipt_qty,
a.closing_balance_qty,
a2.slno a2slno2,
a2.inventory_item_id||'/'||a2.slno a2foliono2,
substr(a.remarks,1,10) remarks,a.opening_balance_qty,
decode(NVL(a.basic_ed,0) + NVL(a.additional_ed,0) + NVL(a.other_ed,0),0,a.sales_invoice_no) col17p_1,
decode(NVL(a.basic_ed,0) + NVL(a.additional_ed,0) + NVL(a.other_ed,0),0,a.sales_invoice_date) col17p_2,
decode(NVL(a.basic_ed,0) + NVL(a.additional_ed,0) + NVL(a.other_ed,0),0,a.sales_invoice_quantity) col18,a.primary_uom_code,
substr(rcv.receipt_num,1,15) Recipt_num,
JA_JAIN23C1_XMLP_PKG.cf_1formula(a.register_id,a.opening_balance_qty,a.closing_balance_qty,a.inventory_item_id,a.slno) CF_Folio,
JA_JAIN23C1_XMLP_PKG.cf_folio_a2formula(a2.inventory_item_id || '/' || a2.slno) CF_folio_a2,
JA_JAIN23C1_XMLP_PKG.cf_item_tarrifformula(a.inventory_item_id) CF_item_tarrif,
JA_JAIN23C1_XMLP_PKG.cf_item_folioformula(a.inventory_item_id) CF_Item_folio,
JA_JAIN23C1_XMLP_PKG.cf_vend_dtlsformula(a.vendor_id,a.vendor_site_id,a.receipt_ref,DECODE (a.transaction_type ,'R' ,SUBSTR (c.vendor_name ,1 ,20 ) || ' ' || SUBSTR (d.address_line1 ,1 ,10 ) || ' ' || SUBSTR (d.address_line2 ,1 ,10 ) || ' ' || SUBSTR (d.city ,1 ,9 ) || ' ' || SUBSTR (d.state ,1 ,5 ) || ' ' || SUBSTR (d.zip ,1 ,6 ) || ' ' || SUBSTR (d.province ,1 ,10 ) || ' ' || SUBSTR (d.country ,1 ,10 ) ,'RTV' ,SUBSTR (c.vendor_name ,1 ,20 ) || ' ' || SUBSTR (d.address_line1 ,1 ,10 ) || ' ' || SUBSTR (d.address_line2 ,1 ,10 ) || ' ' || SUBSTR (d.city ,1 ,9 ) || ' ' || SUBSTR (d.state ,1 ,5 ) || ' ' || SUBSTR (d.zip ,1 ,6 ) || ' ' || SUBSTR (d.province ,1 ,10 ) || ' ' || SUBSTR (d.country ,1 ,10 ) ,'V' ,Substr (e.organization_name ,1 ,25 ) )) CF_vend_dtls,
JA_JAIN23C1_XMLP_PKG.cf_range1formula(a.vendor_id,a.vendor_site_id,a.receipt_ref,SUBSTR (a.range_no ,1 ,10 )) CF_range1,
JA_JAIN23C1_XMLP_PKG.cf_division1formula(a.vendor_id,a.vendor_site_id,a.receipt_ref,SUBSTR (a.division_no ,1 ,10 )) CF_division1,
JA_JAIN23C1_XMLP_PKG.cf_qty_receivedformula(a.slno,a.inventory_item_id,a.transaction_date,a.transaction_type) CF_qty_received,
JA_JAIN23C1_XMLP_PKG.cf_inv_idformula(a.slno,a.inventory_item_id,a.transaction_date) CF_inv_id,
JA_JAIN23C1_XMLP_PKG.cf_inv_dtformula(a.slno,a.inventory_item_id,a.transaction_date) CF_inv_dt,
JA_JAIN23C1_XMLP_PKG.cf_sales_inv_idformula(a.slno,a.inventory_item_id,a.transaction_date) CF_sales_inv_id,
JA_JAIN23C1_XMLP_PKG.cf_sales_inv_dtformula(a.slno,a.inventory_item_id,a.transaction_date) CF_sales_inv_dt,
JA_JAIN23C1_XMLP_PKG.cf_sales_inv_qtyformula(a.slno,a.inventory_item_id,a.transaction_date,a.transaction_type) CF_sales_inv_qty
from JAI_CMN_RG_23AC_I_TRXS a,
JAI_CMN_RG_23AC_II_TRXS a2,
mtl_system_items b,
po_vendors c,
po_vendor_sites_all d,
org_organization_definitions org,
hr_locations loc,
JAI_CMN_INVENTORY_ORGS hr_org ,org_organization_definitions E,
rcv_shipment_headers rcv,rcv_transactions rtran
where a.organization_id=:p_organization_id
and a.location_id=:p_location_id
AND TO_DATE(a.transaction_date,'DD-MON-YYYY') >= NVL(:P_TRN_FROM_DATE_1,TO_DATE(A.transaction_DATE,'DD-MON-YYYY'))
AND TO_DATE(A.transaction_DATE,'DD-MON-YYYY') <= NVL(:P_TRN_TO_DATE_1,TO_DATE(sysdate,'DD-MON-YYYY'))
and a.organization_id=b.organization_id
AND org.organization_id=a.organization_id
AND loc.location_id=a.location_id
AND a.register_id_part_ii=a2.register_id(+)
and a.register_type='C'
and a.inventory_item_id=b.inventory_item_id
and a.vendor_id=c.vendor_id
and nvl(c.enabled_flag,'Y')='Y'
and a.transaction_date between nvl(c.start_date_active,a.transaction_date)
and nvl(c.end_date_active,a.transaction_date)
and a.vendor_id=d.vendor_id
and a.vendor_site_id=d.vendor_site_id
and hr_org.organization_id=a.organization_id
and hr_org.location_id=a.location_id
AND e.organization_id(+)=a.vendor_id
AND NVL(d.org_id,0)=NVL(org.operating_unit,0)
AND RTRAN.transaction_id(+)=A.receipt_ref
AND RCV.shipment_header_id(+)=RTRAN.shipment_Header_id
UNION
SELECT a.slno,a.register_id,a.transaction_type,a.vendor_id,a.vendor_site_id,a.receipt_ref receipt_id,
a.organization_id,
a.location_id,
org.organization_name,
loc.description,
loc.address_line_1,
loc.address_line_2,
loc.address_line_3,
hr_org.excise_duty_comm,
hr_org.excise_duty_division,
hr_org.excise_duty_range,
hr_org.ec_code,
hr_org.excise_duty_circle,
a.slno,
a.transaction_date,
a.inventory_item_id,
b.description descr,
substr(a.identification_no,1,10)||' '||
substr(a.identification_mark,1,10)||' '||
substr(a.brand_name,1,10) Ident_Dtls,
DECODE(a.transaction_type,'R',a.quantity_received,'PR',a.quantity_received,'IOR',a.quantity_received,'CR',a.quantity_received,'RA',a.quantity_received,'Y',a.quantity_received) rec_qty,
DECODE(a.transaction_type,'R',a.oth_receipt_id_ref,'PR',a.oth_receipt_id_ref,'IOR',a.oth_receipt_id_ref,'CR',a.oth_receipt_id_ref,'RA',a.oth_receipt_id_ref,'Y',a.oth_receipt_id_ref) excise_invoice_id,
DECODE(a.transaction_type,'R',a.oth_receipt_date,'PR',a.oth_receipt_date,'IOR',a.oth_receipt_date,'CR',a.oth_receipt_date,'RA',a.oth_receipt_date,'Y',a.oth_receipt_date) excise_invoice_date,
DECODE(a.transaction_type,'R',SUBSTR(c.vendor_name,1,20)) Vend_Dtls,
SUBSTR(a.range_no,1,10) Range1,
SUBSTR(a.division_no,1,10) Division1,
a.date_of_verification,
DECODE(a.transaction_type,'I',SUBSTR(a.goods_issue_id_ref,1,10),'IOI',SUBSTR(a.goods_issue_id_ref,1,10),'PI',SUBSTR(a.goods_issue_id_ref,1,10),'RI',SUBSTR(a.goods_issue_id_ref,1,10),'RTV',SUBSTR(a.goods_issue_id_ref,1,10),'X',SUBSTR(a.goods_issue_id_ref,1,10),
'V',SUBSTR(a.goods_issue_id_ref,1,10)) goods_issue_id,
DECODE(a.transaction_type,'I',a.goods_issue_date,'IOI',a.goods_issue_date,'PI',a.goods_issue_date,'RI',a.goods_issue_date,'RTV',a.goods_issue_date,'X',a.goods_issue_date,'V',a.goods_issue_date) goods_issue_date,
DECODE(a.transaction_type,'I',a.goods_issue_quantity,'PI',a.goods_issue_quantity,'IOI',a.goods_issue_quantity,
'RI',a.goods_issue_quantity,'RTV',a.goods_issue_quantity) goods_issue_qty,
SUBSTR(a.place_of_install,1,10) place_of_install,
a.date_of_installation,
a.date_of_commission,
DECODE(a.transaction_type,'I',SUBSTR(a.sales_invoice_no,1,10),'IOI',SUBSTR(a.sales_invoice_no,1,10),'PI',SUBSTR(a.sales_invoice_no,1,10),'RI',SUBSTR(a.sales_invoice_no,1,10),'RTV',SUBSTR(a.sales_invoice_no,1,10),
'X',SUBSTR(a.sales_invoice_no,1,10),
'V',SUBSTR(a.sales_invoice_no,1,10)) sales_invoice_id,
DECODE(a.transaction_type,'I',a.sales_invoice_date,'IOI',a.sales_invoice_date,'PI',a.sales_invoice_date,'RI',a.sales_invoice_date,'RTV',a.sales_invoice_date,'X',a.sales_invoice_date,'V',a.sales_invoice_date) sales_invoice_date,
DECODE(a.transaction_type,'I',a.sales_invoice_quantity,'IOI',a.sales_invoice_quantity,'PI',a.sales_invoice_quantity,
'RI',a.sales_invoice_quantity,'RTV',a.sales_invoice_quantity,'X',a.sales_invoice_quantity,
'V',a.sales_invoice_quantity) sales_invoice_qty,
DECODE(a.transaction_type,'I',SUBSTR(a.oth_receipt_id_ref,1,10),'IOI',SUBSTR(a.oth_receipt_id_ref,1,10),'PI',SUBSTR(a.oth_receipt_id_ref,1,10),'RI',SUBSTR(a.oth_receipt_id_ref,1,10),'RTV',SUBSTR(a.oth_receipt_id_ref,1,10),
'X',SUBSTR(a.oth_receipt_id_ref,1,10),
'V',SUBSTR(a.oth_receipt_id_ref,1,10)) oth_receipt_id,
DECODE(a.transaction_type,'I',a.oth_receipt_date,'IOI',a.oth_receipt_date,'PI',a.oth_receipt_date,'RI',a.oth_receipt_date,
'RTV',a.oth_receipt_date,'X',a.oth_receipt_date,
'V',a.oth_receipt_date) oth_receipt_date,
decode(a.transaction_type,'I',a.oth_receipt_quantity,'PI',a.oth_receipt_quantity,'RI',a.oth_receipt_quantity,'IOI',a.oth_receipt_quantity,'RTV',a.oth_receipt_quantity,
'X',a.oth_receipt_quantity,
'V',a.oth_receipt_quantity) oth_receipt_qty,
a.closing_balance_qty,
a2.slno a2slno2,
a2.inventory_item_id||'/'||a2.slno a2foliono2,
substr(a.remarks,1,10) remarks,a.opening_balance_qty,
decode(NVL(a.basic_ed,0) + NVL(a.additional_ed,0) + NVL(a.other_ed,0),0,a.sales_invoice_no) col17p_1,
decode(NVL(a.basic_ed,0) + NVL(a.additional_ed,0) + NVL(a.other_ed,0),0,a.sales_invoice_date) col17p_2,
decode(NVL(a.basic_ed,0) + NVL(a.additional_ed,0) + NVL(a.other_ed,0),0,a.sales_invoice_quantity) col18,a.primary_uom_code,
substr(rcv.receipt_num,1,15) Recipt_num,
JA_JAIN23C1_XMLP_PKG.