JA India - OSP Stock Register - draft

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

Blitz Report™ provides multiple benefits: