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 |
