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 |