JA India - Inward Stock Register Report(XML Publisher)

➜ Back to Library
Description
Categories: BI Publisher
Columns: Operating Unit, Organization Id, Organization Name, Location Id, Location Name, Trx Date, Item Name, Document Type, Entity Code, Document Number ...
Application: Asia/Pacific Localizations
Source: India - Inward Stock Register Report(XML Publisher)
Short Name: JAINIOSR_XML
DB package: JAI_EXTRACT_PKG 
         SELECT b.*, (b.opening_balance_qty + b.bal_qty) balance_qty
		  FROM (SELECT a.*,
		               decode(a.trx_flag,
								  'I',
								  -1,
								  'R',
								  1,
								  1) * a.qty_received qty,
					   SUM(decode(a.trx_flag,
								  'I',
								  -1,
								  'R',
								  1,
								  1) * a.qty_received) OVER(PARTITION BY a.item_name ORDER BY a.trx_date, rownum) bal_qty
				  FROM (SELECT c.rep_context_entity_name operating_unit,
							   c.rep_organization_id organization_id,
							   c.rep_organization_name organization_name,
							   c.rep_location_id location_id,
							   c.rep_location_name location_name,
							   d.trx_date trx_date,
							   d.PRODUCT_CODE item_name,
							   d.document_type document_type,
							   d.entity_code entity_code,
							   d.trx_number document_number,
							   d.trx_line_quantity qty_received,
							   d.party_name party_name,
							   NULL ecc_code,
							   d.PRODUCT_CODE item_folio_no,
							   /*Modified by Junjian for bug#19456854 begin*/
							   /*CASE
								 WHEN d.applied_to_entity_code = 'BILL_OF_ENTRY' THEN
								  d.APPLIED_TO_TRX_ID || ' ' || d.APPLIED_TO_TRX_DATE*/
							   /*Modified by Junjian for bug#19456854 end*/
							   /*END par_boe_info,*/
							   /*Modified by Hongjun for bug#20362697 begin*/
							   CASE
							   WHEN d.applied_to_entity_code = 'BILL_OF_ENTRY' THEN
								 d.tax_invoice_number || ' ' || d.tax_invoice_date || d.applied_to_trx_id || ' ' || d.applied_to_trx_date
							   ELSE
								 d.tax_invoice_number || ' ' || d.tax_invoice_date                 
							   END par_boe_info,
							   /*Modified by Hongjun for bug#20362697 end*/
							   d.party_name || ' ' || d.party_address party_info,
							   --NULL input_rec_info,  -- commented out by zhiwei.xin for bug#19456668
							   e.ATTRIBUTE3 || e.ATTRIBUTE4  INPUT_REC_INFO, -- added by zhiwei.xin for bug#19456668
							   d.intended_use_desc usage,
							   NULL other_reference,
							   e.numeric1 opening_balance_qty,
							   decode(d.entity_code,
									  'SALES_ORDER_ISSUE',
									  'I',
									  'OE_ORDER_HEADERS',
									  'I',
									  'ISSUE',
									  'I',
									  'RECEIPT',
									  'R',
									  'RCV_TRANSACTION',
									  --'R', -- commented out by zhiwei.xin for bug#19456182
									  decode(d.TRX_TYPE, 'RETURN TO RECEIVING', 'I', 'R'), -- added by zhiwei.xin for bug#19456182
									  'R') trx_flag
						  FROM jai_rep_context_t    c,
							   jai_rep_trx_detail_t d,
							   jai_rep_trx_jx_ext_t e
						 WHERE c.rep_context_id = d.rep_context_id
						   AND c.request_id = d.request_id
						   AND c.extract_summary_code = 'H'
						   AND d.trx_line_quantity <> 0
						   AND d.detail_tax_line_id = e.detail_tax_line_id
						   AND c.request_id = :p_conc_request_id
						 GROUP BY c.rep_context_entity_name,
								  c.rep_organization_id,
								  c.rep_organization_name,
								  c.rep_location_id,
								  c.rep_location_name,
								  d.trx_date,
								  d.PRODUCT_CODE,
								  d.document_type,
								  d.entity_code,
								  d.trx_type,  -- added by zhiwei.xin for bug#19456182
								  d.trx_number,
								  d.trx_line_quantity,
								  d.trx_line_id,
								  d.intended_use_desc,
								  d.party_name,
								  d.party_address,
								  -- added by zhiwei.xin for bug#19456668 begin
								  e.ATTRIBUTE3,
								  e.ATTRIBUTE4,
								  -- added by zhiwei.xin for bug#19456668 end.
								  d.party_site_name,
								  /*Modified by Junjian for bug#19456854 begin*/
								  d.applied_to_entity_code,
								  d.applied_to_trx_id,
								  d.applied_to_trx_date,
								  /*Modified by Junjian for bug#19456854 end*/
								  /*Modified by Hongjun for bug#20362697 begin*/
								  d.tax_invoice_number,
								  d.tax_invoice_date,
								  /*Modified by Hongjun for bug#20362697 end*/
								  e.numeric1) a
						) b
		 ORDER BY b.organization_id, b.location_id, b.item_name, b.trx_date
Parameter Name SQL text Validation
Operating Unit 
 
 LOV Oracle
Inventory Orgnization 
 
 LOV Oracle
Location 
 
 LOV Oracle
Date From 
 
 Date
Date To 
 
 Date
Item Class 
 
 LOV Oracle
Download
Related Blitz Reports
JA India - RG1 Register Report(XML Publisher) JA India - RG23D Register Report(XML Publisher) JA India - Tax Recovery Register Report(XML Publisher) JA India - ST Forms Report(XML Publisher) JA India - Settlement Detail Report (XML Publisher) JA India - Tax Exemptions Report(XML Publisher) JA India - Tax Register Report(XML Publisher) JA India - Tax Liability Register Report(XML Publisher) JA India GSTR-2 Return (1)
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: