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
Source: India - RG1 Register Report(XML Publisher)
Short Name: JAINRGRR_XML
DB package: JAI_EXTRACT_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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 | |
|---|---|---|---|
| Operating Unit |
|
LOV Oracle | |
| Tax Regime |
|
LOV Oracle | |
| Tax Registration Number |
|
LOV Oracle | |
| Inventory Organization |
|
LOV Oracle | |
| Location |
|
LOV Oracle | |
| Date From |
|
Date | |
| Date To |
|
Date | |
| Show All Items Stock |
|
LOV Oracle | |
| Report Level |
|
LOV Oracle |
