JA India - OSP Pending Receipt - draft

➜ Back to Library
Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - OSP Pending Receipt Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAIN57PR_XML
DB package: JA_JAIN57PR_XMLP_PKG
Run JA India - OSP Pending Receipt - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Organization 
 
 LOV Oracle
Location 
 
 LOV Oracle
From Date 
 
 Date
To Date 
 
 Date
Vendor Name(Skip for all) 
 
 LOV Oracle
Over Due Yes/No 
 
 LOV Oracle
Over Due Date 
 
 Date
Download
Related Blitz Reports
JA India - Outside Processing Challan - draft JA India - OSP Stock Register - draft JA India - OSP Pending Despatch JA India - TDS at Lower Rates - draft CAC PO Price vs. Costing Method Comparison CAC Open Purchase Orders CAC Receiving Value (Period-End) CAC Purchase Price Variance CAC Intercompany SO Price List vs. Item Cost Comparison
Blitz Report™

Blitz Report™ provides multiple benefits: