JA India - OSP Pending Despatch

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - OSP Pending Despatch Report (XML)
Short Name: JAIN57PI_XML
DB package: JA_JAIN57PI_XMLP_PKG
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
Parameter Name SQL text Validation
To Date
 
Date
From Date
 
Date
Vendor Name (Skip for all)
 
LOV Oracle
Location
 
LOV Oracle
Organization
 
LOV Oracle