JA India - OSP Stock Register - draft
Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - OSP Stock Register (XML) - Not Supported: Reserved For Future Use
Short Name: JAINF4RG_XML
DB package: JA_JAINF4RG_XMLP_PKG
Source: India - OSP Stock Register (XML) - Not Supported: Reserved For Future Use
Short Name: JAINF4RG_XML
DB package: JA_JAINF4RG_XMLP_PKG
Run
JA India - OSP Stock Register - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT hou.name NAME , nvl(hou.internal_address_line,'NIL') INTADRS , loc.address_line_1||','||loc.address_line_2 ||','||loc.address_line_3||','||loc.town_or_city||','|| loc.postal_code CITY , nvl(loc.country,'NIL') COUNTRY , j57f4.location_id LOC , jhou.ec_code ECC_NO , to_char(j57f4.issue_date,'DD-MON-YY') issue_date , msi.description DESCRIPTION , j57f4l.tariff_code TCODE , j57f4l.despatch_qty QUANTITY , j57f4l.scrap_qty SCRAP_QUANTITY , j57f4l.item_uom IUOM , j57f4l.identification IDENTIFICATION , pov.vendor_name||','|| povs.address_line1||','|| povs.address_line2||','|| povs.address_line3||','|| povs.city||','|| povs.country VENDOR , j57f4.form_number CHALLAN_NO , to_char(j57f4.CREATION_DATE,'DD-MON-YY') FRM_DATE , j57f4l.process_reqd PROCESS_REQD , (j57f4l.despatch_qty * j57f4l.item_value) ASSES_VALUE , j57f4l.excise_rate ED_RATE , ((j57f4l.despatch_qty * j57f4l.item_value * j57f4l.excise_rate) / 100) DUTY_AMT , j57f4.dr_register DR_REGISTER_NAME , j57f4.dr_reg_slno DR_SLNO , to_char(j57f4.dr_reg_entry_date,'DD-MON-YY') DR_DATE , j57f4.receipt_date RX_DATE , nvl(j57f4l.return_qty,0) RX_QTY , nvl(( j57f4l.despatch_qty -nvl( j57f4l.return_qty,0) ),0) PENDING_QTY , j57f4.cr_register CR_REGISTER_NAME , j57f4.cr_reg_slno CR_SLNO , j57f4.cr_reg_entry_date CR_DATE , ( nvl(p2.cr_basic_ed,0) + nvl(p2.cr_additional_ed,0) + nvl(p2.cr_other_ed,0) ) RE_CREDIT_AMT FROM JAI_PO_OSP_HDRS j57f4 , JAI_PO_OSP_LINES j57f4L , hr_organization_units HOU , JAI_CMN_INVENTORY_ORGS JHOU , hr_locations LOC , po_vendors POV , po_vendor_sites_all POVS , mtl_system_items MSI , JAI_CMN_RG_23AC_II_TRXS P2 WHERE j57f4.organization_id = :p_organization_id AND j57f4.location_id = nvl(:p_location_id,j57f4.location_id) AND NVL(j57f4.cancel_flag ,'N') = 'N' AND trunc(j57f4.issue_date) >= trunc(:lp_from_date) AND trunc(j57f4.issue_date) <= trunc(NVL(:lp_to_date,sysdate)) AND upper(j57f4.issue_approved) = 'Y' AND j57f4l.form_id = j57f4.form_id AND hou.organization_id = j57f4.organization_id AND jhou.organization_id (+) = j57f4.organization_id AND jhou.location_id (+) = j57f4.location_id AND loc.location_id = j57f4.location_id AND pov.vendor_id = j57f4.vendor_id AND povs.vendor_site_id (+) = j57f4.vendor_site_id AND msi.organization_id = j57f4.organization_id AND msi.inventory_item_id = j57f4l.item_id AND p2.organization_id (+) = j57f4.organization_id AND p2.location_id (+) = j57f4.location_id AND p2.register_type (+) = 'A' AND p2.slno (+) = trunc(j57f4.cr_reg_slno) ORDER BY j57f4.ISSUE_DATE |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Organization |
|
LOV Oracle | |
Location |
|
LOV Oracle | |
From Date |
|
Date | |
To Date |
|
Date |