JA India - RG23D Register Report(XML Publisher)

          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