JA India - RG23C Part I Report - draft - draft
Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - RG23C Part I Report (XML) - Not Supported: Reserved For Future Use - Not Supported: Reserved For Future Use
Short Name: JAI23C_1_XML
DB package: JA_JAIN23C1_XMLP_PKG
Source: India - RG23C Part I Report (XML) - Not Supported: Reserved For Future Use - Not Supported: Reserved For Future Use
Short Name: JAI23C_1_XML
DB package: JA_JAIN23C1_XMLP_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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.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))) 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, JAI_CMN_VENDOR_SITES 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 NVL(a.vendor_site_id,0)=NVL(d.vendor_site_id,0) and NVL(a.vendor_site_id,0)=0 and hr_org.organization_id=a.organization_id and hr_org.location_id=a.location_id AND e.organization_id(+)=a.vendor_id 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, 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,'I',SUBSTR(c.party_name,1,20)||' '|| SUBSTR(i.address1,1,10)||' '|| SUBSTR(i.address2,1,10)||' '|| SUBSTR(i.city,1,9)||' '|| SUBSTR(i.state,1,5)||' '|| SUBSTR(i.postal_code,1,6)||' '|| SUBSTR(i.province,1,10)||' '|| SUBSTR(i.country,1,10), 'Y',SUBSTR(c.party_name,1,20)||' '|| SUBSTR(i.address1,1,10)||' '|| SUBSTR(i.address2,1,10)||' '|| SUBSTR(i.city,1,9)||' '|| SUBSTR(i.state,1,5)||' '|| SUBSTR(i.postal_code,1,6)||' '|| SUBSTR(i.province,1,10)||' '|| SUBSTR(i.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,'I',SUBSTR(c.party_name,1,20)||' '|| SUBSTR(i.address1,1,10)||' '|| SUBSTR(i.address2,1,10)||' '|| SUBSTR(i.city,1,9)||' '|| SUBSTR(i.state,1,5)||' '|| SUBSTR(i.postal_code,1,6)||' '|| SUBSTR(i.province,1,10)||' '|| SUBSTR(i.country,1,10), 'Y',SUBSTR(c.party_name,1,20)||' '|| SUBSTR(i.address1,1,10)||' '|| SUBSTR(i.address2,1,10)||' '|| SUBSTR(i.city,1,9)||' '|| SUBSTR(i.state,1,5)||' '|| SUBSTR(i.postal_code,1,6)||' '|| SUBSTR(i.province,1,10)||' '|| SUBSTR(i.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, hz_parties c, hz_cust_accounts hzca, hz_cust_site_uses_all d, org_organization_definitions org, hr_locations loc, JAI_CMN_INVENTORY_ORGS hr_org ,org_organization_definitions E, hz_locations i,hz_party_sites hzps,hz_cust_acct_sites_all hzcas, 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 nvl(b.enabled_flag,'Y')='Y' and a.transaction_date between nvl(b.start_date_active,a.transaction_date) and nvl(b.end_date_active,a.transaction_date) AND c.party_id=hzca.party_id and a.customer_id=hzca.cust_account_id and NVL(a.customer_site_id,d.site_use_id)=NVL(d.site_use_id,0) and d.cust_acct_site_id=hzcas.cust_acct_site_id AND hzcas.party_site_id=hzps.party_site_id AND hzps.location_id=i.location_id and hr_org.organization_id=a.organization_id and hr_org.location_id=a.location_id AND e.organization_id(+)=a.customer_id 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,'I',SUBSTR(c.party_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_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_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.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,'I',SUBSTR(c.party_name,1,20))) 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, hz_parties c,hz_cust_accounts hzca, 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 nvl(b.enabled_flag,'Y')='Y' and a.transaction_date between nvl(b.start_date_active,a.transaction_date) and nvl(b.end_date_active,a.transaction_date) AND hzca.party_id=c.party_id and a.customer_id=hzca.cust_account_id and NVL(a.customer_site_id,0)=0 and hr_org.organization_id=a.organization_id and hr_org.location_id=a.location_id AND e.organization_id(+)=a.customer_id 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',NULL, 'Y',NULL, 'V',NULL,NULL ) 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.sal |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Organization |
|
LOV Oracle | |
| Location |
|
LOV Oracle | |
| Transaction From Date |
|
Date | |
| Transaction To Date |
|
Date |
