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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
JA India - RG23C Part I Report - draft - draft JA India - Cenvat Recovery(On Capital Goods) Report- Not Supported: Reserved For Future Use JA India - Cenvat Monthly Return Inputs-Textile - draft JA India - Cenvat Monthly Return Capital Goods-Textile - draft JA India - Return to Vendor - draft JA India - RG 23 D - draft JA India - Outside Processing Challan - draft JA India - OSP Pending Receipt - draft JA India - OSP Pending Despatch
Blitz Report™

Blitz Report™ provides multiple benefits: