SELECT b.*,
(nvl(b.opening_balance_qty,
0) + b.bal_qty) balance_qty,
c.tax_type_name,
c.tax_amount,
c.type_num,
c.row_num
FROM (SELECT a.*,
ROWNUM row_n,
decode(a.trx_flag,
'I',
-1,
'R',
1,
1) * a.quantity qty,
SUM(decode(a.trx_flag,
'I',
-1,
'R',
1,
1) * a.quantity) OVER(PARTITION BY a.itme_id, a.organization_id ORDER BY ROWNUM, a.trx_date) bal_qty
FROM (SELECT c.rep_context_entity_name operating_unit,
d.first_party_primary_reg_num tax_registration_num,
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.product_description item_description,
d.product_id itme_id,
NULL item_tariff_no,
NULL item_folio_no,
d.product_description item_desc,
CASE
WHEN d.applied_from_entity_code = 'BILL_OF_ENTRY' THEN
d.tax_invoice_number || ' ' || d.APPLIED_FROM_TRX_ID || ' ' ||
d.tax_invoice_date
ELSE
d.tax_invoice_number || ' ' || d.tax_invoice_date
END manu_invinfo,
d.party_name || ' ' || d.party_address party_info,
--edit by hongjun.wu for bug#19528742 begin--
/*SUM(nvl(d.trx_line_quantity,
0)) quantity,*/
nvl(d.trx_line_quantity, 0) quantity,
--edit by hongjun.wu for bug#19528742 end--
d.trx_number,
d.TRX_TYPE,
d.trx_id,
trunc(d.trx_date) trx_date,
d.entity_code entity_code,
nvl(e.numeric4,
0) opening_balance_qty,
nvl(e.numeric1,
0) qty_remaining,
nvl(e.numeric2,
0) qty_matched,
e.numeric3 sl_no,
decode(d.entity_code,
'SALES_ORDER_ISSUE',
'I',
'OE_ORDER_HEADERS',
'I',
'RCV_TRANSACTION',
decode(d.TRX_TYPE,
'RETURN TO VENDOR',
'I',
'R'),
'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 d.request_id = e.request_id(+)
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,
d.first_party_primary_reg_num,
c.rep_organization_id,
c.rep_organization_name,
c.rep_location_id,
c.rep_location_name,
d.product_description,
d.product_id,
d.product_description,
d.applied_from_entity_code,
d.tax_invoice_number,
d.APPLIED_FROM_TRX_ID,
d.tax_invoice_date,
d.party_name,
d.party_address,
nvl(d.trx_line_quantity, 0), --add by hongjun.wu for bug#19528742
d.trx_number,
d.trx_type,
d.trx_id,
trunc(d.trx_date),
d.entity_code,
e.numeric4,
e.numeric1,
e.numeric2,
e.numeric3
ORDER BY /*e.numeric3*/trunc(d.trx_date)/*edit by hongjun.wu for bug#19528742*/) a) b,
(SELECT d.tax_type_name tax_type_name,
d.trx_id trx_id,
d.entity_code entity_code,
d.trx_type trx_type,
COUNT(d.tax_type_name) OVER(PARTITION BY d.trx_id, d.entity_code, d.trx_type ORDER BY d.trx_id) type_num,
ROW_NUMBER() OVER(PARTITION BY d.trx_id, d.entity_code, d.trx_type ORDER BY d.trx_id) row_num,
SUM(nvl(d.tax_amt_funcl_curr,
0)) tax_amount
FROM jai_rep_trx_detail_t d
WHERE d.request_id = :p_conc_request_id
GROUP BY d.tax_type_name, d.trx_id, d.entity_code, d.trx_type) c
WHERE c.trx_id = b.trx_id
AND c.entity_code = b.entity_code
AND nvl(c.trx_type,
'#') = nvl(c.TRX_TYPE,
nvl(b.trx_type,
'#'))
AND b.trx_date BETWEEN nvl(:p_trx_date_from, b.trx_date) AND nvl(:p_trx_date_to, b.trx_date) --add by hongjun.wu for bug#19528742
ORDER BY b.organization_id, b.itme_id, /*b.trx_id, comment by hongjun.wu for bug#19528742*/ b.row_n, b.sl_no, b.trx_date, c.row_num |
