JA India - RG1 Register Report(XML Publisher)

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - RG1 Register Report(XML Publisher)
Short Name: JAINRGRR_XML
DB package: JAI_EXTRACT_PKG
	 SELECT t3.sl_num sl_num2,
       t3.operating_unit operating_unit2,
       t3.rep_entity_id rep_entity_id2,
       t3.tax_registration_num tax_registration_num2,
       t3.organization_id organization_id2,
       t3.organization_name organization_name2,
       t3.location_id location_id2, 
       t3.location_name location_name2,
	   t3.item_reporting_code item_class_code2,
       t3.item_uom item_uom2,
       t3.item_id item_id2,
       t3.item_code item_code2,
       t3.item_description||'->'||t3.item_classification_code item_description2,
       t3.manuf_qty manuf_qty2,
       t3.home_use_qty + t3.export_under_cl_qty+ 
       t3.export_under_bond_qty+t3.other_under_bond_qty+
       t3.other_purpose_qty qty_cleared2,
       t3.trx_date_closing_balance trx_date_closing_balance2,
       NVL(t3.next_date_open_bal,
           t3.opening_balance) date_open_bal2,
       t3.closing_balance closing_balance2,
	   t3.assessable_value_desc assessable_value_desc2,
       b.tax_type_name tax_type_name2,
       nvl(b.type_num,
           1) type_num2,
       nvl(b.row_num,
           1) row_num2,
       b.tax_amount tax_amount2,
	   null assessable_value2,
	   JAI_EXTRACT_PKG.get_clearence_type(
	                         pn_org_id => t3.rep_entity_id ,
                             pn_organization_id => t3.organization_id,
                             pn_location_id => t3.location_id,
                             pn_item_id => t3.item_id,
                             pn_request_id  => :p_conc_request_id) type_of_clear2,
       b.actual_tax_rate actual_tax_rate2,
       b.tax_rate_uom_rate tax_rate_uom_rate2
  FROM (SELECT t2.*,
               t2.open_bal + t2.opening_balance closing_balance,
               ROW_NUMBER() OVER(PARTITION BY t2.organization_id, t2.tax_registration_num, t2.location_id
ORDER BY t2.organization_id, t2.tax_registration_num, t2.location_id) sl_num,
               LAG(t2.open_bal + t2.opening_balance,
                   1) OVER(PARTITION BY t2.tax_registration_num, t2.organization_id, t2.location_id, t2.item_id ORDER BY t2.tax_registration_num, t2.organization_id, t2.location_id, t2.item_id) AS next_date_open_bal
          FROM (SELECT a.*,
                       SUM(a.trx_date_closing_balance) OVER(PARTITION BY a.tax_registration_num, a.item_id, a.organization_id, a.location_id ORDER BY a.tax_registration_num, a.organization_id, a.location_id, a.item_id) AS open_bal
                  FROM (SELECT t1.*,
                               (manuf_qty - t1.home_use_qty -
                               t1.export_under_cl_qty - t1.export_under_bond_qty -
                               t1.other_under_bond_qty - t1.other_purpose_qty) trx_date_closing_balance
                          FROM (SELECT t.operating_unit,
                                       t.rep_entity_id,
                                       t.tax_registration_num,
                                       t.organization_id,
                                       t.organization_name,
                                       t.location_id,
                                       t.location_name,
                                       t.item_uom,
                                       t.item_id,
                                       t.item_code,
									   t.ITEM_REPORTING_CODE,
                                       t.ITEM_DESCRIPTION,
                                       t.item_classification_code, --Added by Junjian for bug#19426429 
									   t.ASSESSABLE_VALUE_DESC,
                                       t.OPENING_BALANCE,
                                       SUM(CASE
                                             WHEN t.entity_code IN
                                                  ('RCV_TRANSACTION',
                                                   'RECEIPT') THEN
                                              CASE
                                                WHEN t.trx_type IN ('RETURN TO RECEIVING') THEN
                                                 0
                                                ELSE
                                                 t.TRX_LINE_QUANTITY
                                              END
                                             ELSE
                                              0
                                           END) MANUF_QTY,
                                       SUM(CASE
                                             WHEN t.INTENDED_USE = 'HOME_USE' AND
                                                  t.entity_code NOT IN
                                                  ('RCV_TRANSACTION',
                                                   'RECEIPT') THEN
                                              t.TRX_LINE_QUANTITY
                                             ELSE
                                              0
                                           END) home_use_qty,
                                       --Modified by Junjian for bug#19416777 begin
                                       /*SUM(CASE
                                             WHEN t.INTENDED_USE = 'HOME_USE' AND
                                                  t.entity_code NOT IN
                                                  ('RCV_TRANSACTION',
                                                   'RECEIPT') THEN
                                              nvl(t.ASSESSABLE_VALUE,
                                                  0)
                                             ELSE
                                              0
                                           END) home_use_values, */
                                      --Modified by Junjian for bug#19416777 end   
                                       SUM(CASE
                                             WHEN t.INTENDED_USE = 'EXPORT_UNDER_CLAIM' AND
                                                  t.entity_code NOT IN
                                                  ('RCV_TRANSACTION',
                                                   'RECEIPT') THEN
                                              t.TRX_LINE_QUANTITY
                                             ELSE
                                              0
                                           END) export_under_cl_qty,
                                     --Modified by Junjian for bug#19416777 begin
                                     /*   SUM(CASE
                                             WHEN t.INTENDED_USE = 'EXPORT_UNDER_CLAIM' AND
                                                  t.entity_code NOT IN
                                                  ('RCV_TRANSACTION',
                                                   'RECEIPT') THEN
                                              nvl(t.ASSESSABLE_VALUE,
                                                  0)
                                             ELSE
                                              0
                                           END) export_under_cl_values, */
                                     --Modified by Junjian for bug#19416777 end
                                       SUM(CASE
                                             WHEN t.INTENDED_USE = 'EXPORT_UNDER_BOND' AND
                                                  t.entity_code NOT IN
                                                  ('RCV_TRANSACTION',
                                                   'RECEIPT') THEN
                                              t.TRX_LINE_QUANTITY
                                             ELSE
                                              0
                                           END) export_under_bond_qty,
                                       SUM(CASE
                                             WHEN t.INTENDED_USE = 'OHTER_UNDER_BOND' AND
                                                  t.entity_code NOT IN
                                                  ('RCV_TRANSACTION',
                                                   'RECEIPT') THEN
                                              t.TRX_LINE_QUANTITY
                                             ELSE
                                              0
                                           END) other_under_bond_qty,
                                       SUM(CASE
                                             WHEN t.entity_code NOT IN
                                                  ('RCV_TRANSACTION',
                                                   'RECEIPT') THEN
                                              CASE
                                                WHEN t.INTENDED_USE = 'HOME_USE' THEN
                                                 0
                                                WHEN t.INTENDED_USE = 'EXPORT_UNDER_CLAIM' THEN
                                                 0
                                                WHEN t.INTENDED_USE = 'EXPORT_UNDER_BOND' THEN
                                                 0
                                                WHEN t.INTENDED_USE = 'OHTER_UNDER_BOND' THEN
                                                 0
                                                ELSE
                                                 nvl(t.TRX_LINE_QUANTITY,
                                                     0)
                                              END
											WHEN t.entity_code IN ('RCV_TRANSACTION',
                                                     'RECEIPT') THEN
		                                         CASE
				                                    WHEN t.trx_type in ('RETURN TO RECEIVING') then
				                                     nvl(t.TRX_LINE_QUANTITY,
                                                         0)
			                                     END
                                           ELSE
                                              0
                                           END) other_purpose_qty,
                                       NULL other_purpose
                                  FROM (SELECT c.rep_context_entity_name operating_unit,
                                               c.rep_entity_id REP_ENTITY_ID,
                                               c.tax_reg_number 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.ITEM_CLASSIFICATION_CODE, --Added by Junjian for bug#19426429 
                                               d.entity_code,
                                               d.trx_type,
                                               d.INTENDED_USE,
                                               --d.ASSESSABLE_VALUE, --Modified by Junjian for bug#19416777 
                                               d.SUBINVENTORY_CODE,
                                               NULL ITEM_TARIFF_NO,
                                               NULL ITEM_FOLIO_NO,
                                               d.ITEM_UOM ITEM_UOM,
                                               d.PRODUCT_ID ITEM_ID,
                                               d.PRODUCT_CODE ITEM_CODE,
											   d.ITEM_REPORTING_CODE item_reporting_code,
                                               e.numeric1 OPENING_BALANCE,
                                               e.attribute2 ASSESSABLE_VALUE_DESC, --Modified by Junjian for bug#19416777 
                                               d.TRX_LINE_QUANTITY,
                                               NULL REMARKS,
                                               NULL SIGNATURE
                                          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 d.detail_tax_line_id =
                                               e.detail_tax_line_id
                                           AND d.entity_code NOT IN
                                               ('ISSUE',
                                                'RECEIPT')
                                           AND c.request_id = :p_conc_request_id
                                         GROUP BY c.rep_context_entity_name,
                                                  c.rep_entity_id,
                                                  c.tax_reg_number,
                                                  c.rep_organization_id,
                                                  c.rep_organization_name,
                                                  c.rep_location_id,
                                                  c.rep_location_name,
                                                  d.item_uom,
                                                  d.entity_code,
                                                  d.trx_type,
                                                  d.INTENDED_USE,
                                                  --d.ASSESSABLE_VALUE, --Modified by Junjian for bug#19416777 
                                                  d.SUBINVENTORY_CODE,
                                                  d.trx_line_quantity,
                                                  d.trx_line_id,
                                                  d.product_id,
                                                  d.PRODUCT_CODE,
												  d.ITEM_REPORTING_CODE,
                                                  d.PRODUCT_DESCRIPTION,
                                                  d.ITEM_CLASSIFICATION_CODE, --Added by Junjian for bug#19426429 
                                                  e.attribute2, --Modified by Junjian for bug#19416777 
                                                  e.numeric1
                                        UNION ALL
                                        SELECT c.rep_context_entity_name operating_unit,
                                               c.rep_entity_id REP_ENTITY_ID,
                                               c.tax_reg_number 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.ITEM_CLASSIFICATION_CODE, --Added by Junjian for bug#19426429 
                                               d.entity_code,
                                               d.trx_type,
                                               d.INTENDED_USE,
                                               --d.ASSESSABLE_VALUE,  --Modified by Junjian for bug#19416777 
                                               d.SUBINVENTORY_CODE,
                                               NULL ITEM_TARIFF_NO,
                                               NULL ITEM_FOLIO_NO,
                                               d.ITEM_UOM ITEM_UOM,
                                               d.PRODUCT_ID ITEM_ID,
                                               d.PRODUCT_CODE ITEM_CODE,
											   d.ITEM_REPORTING_CODE item_reporting_code,
                                               e.numeric1 OPENING_BALANCE,
                                               e.attribute2 ASSESSABLE_VALUE_DESC, --Modified by Junjian for bug#19416777 
                                               d.TRX_LINE_QUANTITY,
                                               NULL REMARKS,
                                               NULL SIGNATURE
                                          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 d.detail_tax_line_id =
                                               e.detail_tax_line_id
                                           AND d.entity_code IN
                                               ('ISSUE',
                                                'RECEIPT')
                                           AND c.request_id = :p_conc_request_id) t
                                 GROUP BY t.operating_unit,
                                          t.rep_entity_id,
                                          t.tax_registration_num,
                                          t.organization_id,
                                          t.organization_name,
                                          t.location_id,
                                          t.location_name,
                                          t.item_uom,
                                          t.item_id,
                                          t.ITEM_CODE,
										  t.ITEM_REPORTING_CODE,
                                          t.ASSESSABLE_VALUE_DESC,
                                          t.ITEM_DESCRIPTION,
                                          t.ITEM_CLASSIFICATION_CODE, --Added by Junjian for bug#19426429 
                                          t.OPENING_BALANCE
                                 ORDER BY t.tax_registration_num,
                                          t.organization_id,
                                          t.location_id,
                                          t.ITEM_ID) t1) a) t2) t3,
       (SELECT d.tax_type_name tax_type_name,
               d.item_uom item_uom,
               d.product_id product_id,
               d.first_party_primary_reg_num reg_number,
               d.organization_id organization_id,
               d.location_id location_id,
			   d.item_classification_code, --for report 'India - RG1 Register Report(XML Publisher)' warning
               AVG(d.actual_tax_rate) actual_tax_rate,
               AVG(d.tax_rate_uom_rate) tax_rate_uom_rate,
               COUNT(d.tax_type_name) OVER(PARTITION BY d.organization_id,d.location_id, d.first_party_primary_reg_num, d.product_id ORDER BY d.organization_id,d.location_id, d.product_id, d.first_party_primary_reg_num) type_num,
               ROW_NUMBER() OVER(PARTITION BY d.organization_id, d.location_id,d.first_party_primary_reg_num, d.product_id ORDER BY d.organization_id,d.location_id, d.product_id, d.first_party_primary_reg_num) 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
           AND (d.entity_code NOT IN ('RCV_TRANSACTION')
		     OR (d.entity_code IN ('RCV_TRANSACTION')
			     AND d.trx_type IN ('RETURN TO RECEIVING'))
		   )
           AND d.tax_type_id IS NOT NULL
         GROUP BY d.tax_type_name,
                  d.item_uom,
                  d.first_party_primary_reg_num,
                  d.product_id,
                  d.location_id,
                  d.organization_id
          /* Bug#20273717, Dejiang.Liu/150915, remove unwanted comma. */
				  --,d.item_classification_code, --for report 'India - RG1 Register Report(XML Publisher)' warning
          ,d.item_classification_code --for report 'India - RG1 Register Report(XML Publisher)' warning
				  ) b
 WHERE t3.organization_id = b.organization_id(+)
   AND t3.location_id = b.location_id(+)
   AND t3.tax_registration_num = b.reg_number(+)
   AND t3.item_id = b.product_id(+)
   AND t3.item_classification_code = b.item_classification_code(+) --for report 'India - RG1 Register Report(XML Publisher)' warning
   AND t3.organization_id = :organization_id3
   AND t3.location_id = :location_id3
   AND t3.tax_registration_num = :tax_reg_number3
 ORDER BY t3.tax_registration_num,
          t3.organization_id,
          t3.location_id,
          t3.item_id,
          b.type_num,
          b.row_num
Parameter Name SQL text Validation
Report Level
 
LOV Oracle
Show All Items Stock
 
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