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 |
