SELECT
hou.name NAME,
hou.internal_address_line IADRS,
loc.address_line_1||','||loc.address_line_2||','||loc.address_line_3||','||loc.town_or_city||','||loc.country ADDRS, hou.location_id LOCID,
loc.location_code LOC_CODE,
pov.vendor_name VENDOR_NAME,
we.wip_entity_name WO_NO,
j57f4.creation_date CREATION_DATE,
j57f4l.process_reqd PROCESS,
msi.description DESCRIPTION,
j57f4l.despatch_qty QUANTITY,
muom.uom_code IUOM,
null C_ITEM_FLEXDATA,
j57f4l.item_id ITEM_ID,
((j57f4l.despatch_qty * j57f4l.item_value * j57f4l.excise_rate) / 100) DUTY_AMT,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_flexfield', 'INV', 'MSTK', 101, msi.ORGANIZATION_ID, msi.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ITEM_FLEXFIELD
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_units_of_measure MUOM,
wip_entities WE,
mtl_system_items MSI
WHERE
j57f4.organization_id = :P_ORGANIZATION_ID
AND j57f4.location_id = nvl(:P_LOCATION_ID,j57f4.location_id)
AND trunc(j57f4.creation_date)<= trunc(nvl(trunc(:CP_TO_DATE),trunc(sysdate)))
AND trunc(j57f4.creation_date)>= trunc(nvl(trunc(:CP_FROM_DATE),trunc(j57f4.creation_date)))
AND upper(j57f4.issue_approved) <> 'Y'
AND j57f4.vendor_id = nvl(:P_VENDOR_ID,j57f4.vendor_id)
AND j57f4l.form_id = j57f4.form_id
AND hou.organization_id = j57f4.organization_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 muom.uom_code = j57f4l.item_uom
AND we.wip_entity_id = j57f4l.wip_entity_id
ORDER BY
pov.vendor_name,
j57f4.form_id,
j57f4.creation_date,
msi.description |
