SELECT
hou.name NAME, hou.internal_address_line IADRS,
loc.location_code LOC_CODE, loc.location_code||','|| loc.address_line_1||','|| loc.address_line_2
||','||loc.address_line_3||','||loc.town_or_city||','||loc.country ADDRS, hou.location_id LOCID,
pov.vendor_name VENDOR, povs.address_line1||','|| povs.address_line2||','|| povs.address_line3||','||povs.city||','|| povs.country VENDOR_ADRS,
j57f4.form_number CHALLAN_NO,
j57f4.creation_date FRM_DATE,
j57f4.creation_date CREATION_DATE,
msi.description DESCRIPTION,
j57f4l.despatch_qty QUANTITY,
j57f4l.item_uom IUOM,
((j57f4l.despatch_qty * j57f4l.item_value * j57f4l.excise_rate) / 100) DUTY_AMT,
j57f4l.excise_rate ,
j57f4l.item_value * j57f4l.despatch_qty item_value,
nvl(j57f4l.return_qty,0) RETURN_QTY,
(j57f4l.despatch_qty - nvl(j57f4l.return_qty,0)) PENDING_QTY,
(j57f4.creation_date + j57f4.process_time) DUE_DATE,
JA_JAIN57PR_XMLP_PKG.cf_pending_dutyformula(( ( j57f4l.despatch_qty * j57f4l.item_value * j57f4l.excise_rate ) / 100 ), ( j57f4l.despatch_qty - nvl ( j57f4l.return_qty , 0 ) )) CF_PENDING_DUTY
FROM
JAI_PO_OSP_HDRS j57f4,
JAI_PO_OSP_LINES j57f4L,
hr_organization_units HOU,
hr_locations LOC,
po_vendors POV,
po_vendor_sites_all POVS,
mtl_system_items MSI
WHERE
NVL(:P_OVERDUE,'N') ='N'
AND 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 j57f4l.form_id = j57f4.form_id
AND hou.organization_id = j57f4.organization_id
AND loc.location_id = j57f4.location_id
AND msi.inventory_item_id = j57f4l.item_id
AND j57f4.vendor_id = nvl(:P_VENDOR_ID,j57f4.vendor_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 (trunc(j57f4.creation_date) BETWEEN nvl(:P_FROM_DATE,j57f4.creation_date)
AND nvl(:P_TO_DATE,j57f4.creation_date))
AND upper(j57f4.issue_approved) = 'Y'
AND upper(j57f4.receipt_approved) <> 'Y'
AND (j57f4l.despatch_qty - nvl(j57f4l.return_qty,0)) > 0
UNION
SELECT
hou.name NAME, hou.internal_address_line IADRS,
loc.location_code LOC_CODE, loc.location_code||','|| loc.address_line_1||','|| loc.address_line_2
||','||loc.address_line_3||','||loc.town_or_city||','||loc.country ADDRS, hou.location_id LOCID,
pov.vendor_name VENDOR, povs.address_line1||','|| povs.address_line2||','|| povs.address_line3||','||povs.city||','|| povs.country VENDOR_ADRS,
j57f4.form_number CHALLAN_NO,
j57f4.creation_date FRM_DATE,
j57f4.creation_date CREATION_DATE,
msi.description DESCRIPTION,
j57f4l.despatch_qty QUANTITY,
j57f4l.item_uom IUOM,
((j57f4l.despatch_qty * j57f4l.item_value * j57f4l.excise_rate) / 100) DUTY_AMT,
j57f4l.excise_rate ,
j57f4l.item_value * j57f4l.despatch_qty item_value,
nvl(j57f4l.return_qty,0) RETURN_QTY,
(j57f4l.despatch_qty - nvl(j57f4l.return_qty,0)) PENDING_QTY,
(j57f4.creation_date + j57f4.process_time) DUE_DATE,
JA_JAIN57PR_XMLP_PKG.cf_pending_dutyformula(( ( j57f4l.despatch_qty * j57f4l.item_value * j57f4l.excise_rate ) / 100 ), ( j57f4l.despatch_qty - nvl ( j57f4l.return_qty , 0 ) )) CF_PENDING_DUTY
FROM
JAI_PO_OSP_HDRS j57f4,
JAI_PO_OSP_LINES j57f4L,
hr_organization_units HOU,
hr_locations LOC,
po_vendors POV,
po_vendor_sites_all POVS,
mtl_system_items MSI
WHERE
NVL(:P_OVERDUE,'N') ='Y'
AND j57f4.organization_id = :P_ORGANIZATION_ID
AND j57f4.location_id = nvl(:P_LOCATION_ID,j57f4.location_id)
AND j57f4l.form_id = j57f4.form_id
AND NVL(j57f4.cancel_flag,'N') = 'N'
AND msi.inventory_item_id = j57f4l.item_id
AND hou.organization_id = j57f4.organization_id
AND loc.location_id = j57f4.location_id
AND j57f4.vendor_id = nvl(:P_VENDOR_ID,j57f4.vendor_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 (j57f4.creation_date + j57f4.process_time) < NVL((:P_DUE_ASON_DATE),SYSDATE)
AND upper(j57f4.issue_approved) = 'Y'
AND upper(j57f4.receipt_approved) <> 'Y'
AND (j57f4l.despatch_qty - nvl(j57f4l.return_qty,0)) > 0
ORDER BY 7 ASC |