JA India - RG23D Register Report(XML Publisher)

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - RG23D Register Report(XML Publisher)
Short Name: JAINRG23_XML
DB package: JAI_EXTRACT_PKG
          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
Parameter Name SQL text Validation
Inventory Item
 
LOV Oracle
Date To
 
Date
Date From
 
Date
Location
 
LOV Oracle
Inventory Organization
 
LOV Oracle
Tax Registration Number
 
LOV Oracle
Tax Regime
 
LOV Oracle
Operating Unit
 
LOV Oracle
Ask a question