JA India - Inward Stock Register Report(XML Publisher)

         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