[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_DGI_CURR_CODE
(
P_REQUEST_ID IN NUMBER
);
DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND DET.TAX <> P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX;
/* DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
NOT EXISTS
(SELECT 1
FROM jl_zz_ar_tx_categ_all catg,
jl_zz_ar_tx_att_cls_all attcls,
jl_zz_ar_tx_att_val_all val
WHERE attcls.tax_attribute_value = val.tax_attribute_value
AND attcls.tax_category_id = val.tax_category_id
AND attcls.tax_attribute_name = val.tax_attribute_name
AND val.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
-- nipatel we should add join to attcls.TAX_ATTRIBUTE_TYPE, attcls.TAX_ATTR_CLASS_TYPE,
-- attcls.TAX_ATTR_CLASS_CODE for proper use of index
AND attcls.tax_category_id = catg.tax_category_id
AND catg.org_id = det.internal_organization_id
AND catg.org_id = attcls.org_id
AND catg.org_id = val.org_id
AND attcls.tax_attr_class_code = det.TRX_BUSINESS_CATEGORY
AND det.tax = P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX);
SELECT min(itf.detail_tax_line_id),
itf.trx_id,
null,
null
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_tax_rate_tbl,
l_document_sub_type_tbl
FROM zx_rep_trx_detail_t itf,
ap_invoices apinv
WHERE itf.request_id = p_request_id
AND itf.trx_id = apinv.invoice_id
AND itf.tax_type_code = P_TAX_TYPE_CODE
AND apinv.global_attribute12 >= P_TRANSACTION_LETTER_FROM
AND apinv.global_attribute12 <= P_TRANSACTION_LETTER_TO
GROUP BY itf.trx_id;
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
numeric9,
gdf_ap_invoices_att13,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_vat_amt_tbl(i),
l_dgi_doc_type_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
'After insertion into zx_rep_trx_jx_ext_t ');
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
SELECT min(itf.detail_tax_line_id),
itf.trx_line_id,
itf.trx_id,
itf.tax_rate,
null
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_line_id_tbl,
l_trx_id_tbl,
l_tax_rate_tbl,
l_document_sub_type_tbl
FROM zx_rep_trx_detail_t itf,
ap_invoices_all apinv --Bug 5415028
WHERE itf.request_id = P_REQUEST_ID
AND itf.tax_type_code = P_VAT_TAX_TYPE
-- OR itf.tax_rate = 0)
--itf.tax_type_code = 'Exempt')
AND itf.trx_id = apinv.invoice_id
AND apinv.global_attribute12 <> NVL(P_EXCLUDING_TRX_LETTER,'$') --Bug 5415028
AND nvl(itf.reverse_flag,'N') <> 'Y'
GROUP BY itf.tax_rate,
itf.trx_line_id,
itf.trx_id;
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
numeric9,
numeric10,
numeric8,
numeric2,
numeric3,
numeric12,
gdf_ap_invoices_att11,
gdf_ap_invoices_att12,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_vat_amt_tbl(i),
l_taxable_amt_tbl(i),
l_non_taxable_amt_tbl(i),
l_vat_exempt_amt_tbl(i),
l_vat_perc_amt_tbl(i),
nvl(l_non_taxable_amt_tbl(i),0)+nvl(l_vat_perc_amt_tbl(i),0)
+nvl(l_taxable_amt_tbl(i),0)+nvl(l_vat_amt_tbl(i),0), --Bug 5415028
l_gdf_ap_inv_att11_tbl(i),
l_gdf_ap_inv_att12_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
SELECT detail_tax_line_id,
trx_line_id,
trx_id,
currency_conversion_rate,
-- trx_currency_code,
tax_rate,
tax_rate_id,
document_sub_type,
tax_regime_code,
bill_from_site_tax_prof_id,
-- shipping_tp_address_id,
-- billing_tp_address_id
bill_from_site_id,
bill_from_party_id
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_line_id_tbl,
l_trx_id_tbl,
l_exchange_rate_tbl,
--l_trx_currency_code_tbl,
l_tax_rate_tbl,
l_tax_rate_id_tbl,
l_document_sub_type_tbl,
l_tax_regime_code_tbl,
l_bill_from_site_prof_id_tbl,
l_bill_from_site_id_tbl,
l_bill_from_tp_id_tbl
FROM
( SELECT min(itf1.detail_tax_line_id) detail_tax_line_id,
itf1.trx_line_id,
itf1.trx_id,
null currency_conversion_rate,
-- itf1.trx_currency_code,
itf1.tax_rate,
itf1.tax_rate_id,
null document_sub_type,
itf1.tax_regime_code,
itf1.bill_from_site_tax_prof_id,
NVL(itf1.shipping_tp_address_id,
itf1.billing_tp_address_id) bill_from_site_id,
NVL(itf1.billing_trading_partner_id,
itf1.shipping_trading_partner_id) bill_from_party_id
FROM zx_rep_trx_detail_t itf1,
ap_invoices apinv
WHERE itf1.request_id = P_REQUEST_ID
AND apinv.invoice_id = itf1.trx_id
-- AND itf1.posted_flag = 'Y'
AND itf1.tax_type_code = P_VAT_TAX_TYPE
AND apinv.global_attribute12 <> NVL(p_excluding_trx_letter, '$')
GROUP BY itf1.trx_id,
itf1.trx_line_id,
itf1.tax_rate,
itf1.tax_rate_id,
-- itf1.trx_currency_code,
itf1.tax_regime_code,
itf1.bill_from_site_tax_prof_id,
itf1.shipping_tp_address_id,
itf1.billing_tp_address_id,
itf1.billing_trading_partner_id,
itf1.shipping_trading_partner_id)
UNION
( SELECT min(itf1.detail_tax_line_id) detail_tax_line_id,
itf1.trx_line_id,
itf1.trx_id,
null currency_conversion_rate,
-- itf1.trx_currency_code,
itf1.tax_rate,
itf1.tax_rate_id,
null document_sub_type,
itf1.tax_regime_code,
itf1.bill_from_site_tax_prof_id,
NVL(itf1.shipping_tp_address_id,
itf1.billing_tp_address_id) bill_from_site_id,
NVL(itf1.billing_trading_partner_id,
itf1.shipping_trading_partner_id) bill_from_party_id
FROM zx_rep_trx_detail_t itf1,
ap_invoices apinv
WHERE itf1.request_id = P_REQUEST_ID
AND apinv.invoice_id = itf1.trx_id
-- AND itf1.posted_flag = 'Y'
AND itf1.tax_type_code = 'Exempt'
AND apinv.global_attribute12 <> NVL(p_excluding_trx_letter, '$')
GROUP BY itf1.trx_id,
itf1.trx_line_id,
itf1.tax_rate,
itf1.tax_rate_id,
-- itf1.trx_currency_code,
itf1.tax_regime_code,
itf1.bill_from_site_tax_prof_id,
itf1.shipping_tp_address_id,
itf1.billing_tp_address_id,
itf1.billing_trading_partner_id,
itf1.shipping_trading_partner_id)
UNION
( SELECT min(itf1.detail_tax_line_id) detail_tax_line_id,
itf1.trx_line_id,
itf1.trx_id,
null currency_conversion_rate,
-- itf1.trx_currency_code,
NULL tax_rate,
itf1.tax_rate_id,
--null tax_rate_id,
null document_sub_type,
itf1.tax_regime_code,
itf1.bill_from_site_tax_prof_id,
NVL(itf1.shipping_tp_address_id,
itf1.billing_tp_address_id) bill_from_site_id,
NVL(itf1.billing_trading_partner_id,
itf1.shipping_trading_partner_id) bill_from_party_id
FROM zx_rep_trx_detail_t itf1,
ap_invoices apinv
WHERE itf1.request_id = P_REQUEST_ID
AND apinv.invoice_id = itf1.trx_id
-- AND itf1.posted_flag = 'Y'
AND itf1.tax_type_code = P_NON_TAXAB_TAX_TYPE
AND apinv.global_attribute12 <> NVL(p_excluding_trx_letter, '$')
GROUP BY itf1.trx_id,
itf1.trx_line_id,
-- itf1.tax_rate,
itf1.tax_rate_id,
-- itf1.trx_currency_code,
itf1.tax_regime_code,
itf1.bill_from_site_tax_prof_id,
itf1.shipping_tp_address_id,
itf1.billing_tp_address_id,
itf1.billing_trading_partner_id,
itf1.shipping_trading_partner_id);
SELECT min(itf2.detail_tax_line_id) detail_tax_line_id,
itf2.trx_id,
null currency_conversion_rate,
null tax_rate,
null tax_rate_id,
null document_sub_type
FROM zx_rep_trx_detail_t itf2,
ap_invoices apinv2
WHERE itf2.request_id = P_REQUEST_ID
AND apinv2.invoice_id = itf2.trx_id
AND itf2.tax_type_code = P_NON_TAXAB_TAX_TYPE
GROUP BY itf2.trx_id
);*/
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
numeric9,
numeric10,
numeric8,
numeric2,
numeric3,
numeric7,
numeric4,
numeric5,
numeric6,
numeric12,
attribute4,
attribute19,
attribute23,
attribute20,
gdf_ap_invoices_att13,
attribute21,
attribute22,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_vat_amt_tbl(i),
l_taxable_amt_tbl(i),
l_non_taxable_amt_tbl(i),
l_vat_exempt_amt_tbl(i),
l_vat_perc_amt_tbl(i),
l_other_fed_perc_amt_tbl(i),
l_prov_perc_amt_tbl(i),
l_munic_perc_amt_tbl(i),
l_excise_amt_tbl(i),
l_total_doc_amt_tbl(i),
l_dgi_trx_code_tbl(i),
l_cai_number_tbl(i),
l_cai_due_date_tbl(i),
l_fiscal_printer_tbl(i),
l_dgi_doc_type_tbl(i),
l_taxpayerid_type_tbl(i),
l_reg_status_code_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
UPDATE_DGI_CURR_CODE(p_request_id);
SELECT min(itf1.detail_tax_line_id),
itf1.trx_line_id,
trx_id,
tax_rate_id tax_rate,
document_sub_type
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_line_id_tbl,
l_trx_id_tbl,
l_tax_rate_tbl,
l_document_sub_type_tbl
FROM zx_rep_trx_detail_t itf1 ,
zx_fc_codes_denorm_b fc
WHERE itf1.request_id = P_REQUEST_ID
AND itf1.cancel_flag = 'N'
-- AND itf1.posted_flag = 'Y' --Bug 5413860
AND ( itf1.reverse_flag IS NULL OR itf1.reverse_flag <>'Y')
AND itf1.document_sub_type = fc.concat_classif_code
AND fc.classification_type_code = 'DOCUMENT_SUBTYPE'
GROUP BY trx_id,
itf1.trx_line_id,
tax_rate_id ,
document_sub_type;
/* SELECT min(itf1.detail_tax_line_id),
trx_id,
null tax_rate,
document_sub_type
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_tax_rate_tbl,
l_document_sub_type_tbl
FROM zx_rep_trx_detail_t itf1
WHERE itf1.request_id = P_REQUEST_ID
AND itf1.cancel_flag = 'N'
AND itf1.posted_flag = 'Y'
AND ( itf1.reverse_flag IS NULL OR itf1.reverse_flag <>'Y')
AND itf1.document_sub_type IN ( 'DOCUMENT TYPE.JL_CL_FOREIGN_INVOICE',
'DOCUMENT TYPE.JL_CL_DOMESTIC_INVOICE',
'DOCUMENT TYPE.JL_CL_CREDIT_MEMO',
'DOCUMENT TYPE.JL_CL_DEBIT_MEMO')
GROUP BY itf1.trx_id,document_sub_type;
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
numeric9,--l_vat_amt_tbl
numeric10,--l_taxable_amt_tbl
numeric2,--l_vat_exempt_amt_tbl
numeric7,--l_other_tax_amt_tbl
numeric3,--l_cl_num_of_doc_tbl
numeric4,--l_cl_total_exempt_tbl
numeric5,--l_cl_total_effective_tbl
numeric6,--l_cl_total_vat_tax_tbl
numeric11,--l_cl_total_other_tax_tbl
numeric1,--total doc amt
numeric8,--l_total_doc_taxab_amt_tbl
attribute14,--l_order_by_doc_type_tbl
document_sub_type_mng,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_vat_amt_tbl(i),
l_taxable_amt_tbl(i),
l_vat_exempt_amt_tbl(i),
l_other_tax_amt_tbl(i),
l_cl_num_of_doc_tbl(i),
l_cl_total_exempt_tbl(i),
l_cl_total_effective_tbl(i),
l_cl_total_vat_tax_tbl(i),
l_cl_total_other_tax_tbl(i),
NVL(l_vat_exempt_amt_tbl(i),0)+NVL(l_taxable_amt_tbl(i),0)+NVL(l_vat_amt_tbl(i),0)+NVL(l_other_tax_amt_tbl(i),0),
-- l_total_doc_taxab_amt_tbl(i),
nvl(l_cl_total_exempt_tbl(i),0)+nvl(l_cl_total_effective_tbl(i),0)+nvl(l_cl_total_vat_tax_tbl(i),0)+nvl(l_cl_total_other_tax_tbl(i),0),
l_order_by_doc_type_tbl(i),
l_jlcl_ap_doc_type_mng_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
'After insertion into zx_rep_trx_jx_ext_t ');
-- Delete Unwanted lines from Detail ITF
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND (NVL(itf.reverse_flag,'N') = 'Y'
OR itf.tax_type_code <> NVL(p_trl_global_variables_rec.per_tax_type_code_high,
p_trl_global_variables_rec.per_tax_type_code_low));
/*AND NOT EXISTS ( SELECT 1
FROM zx_rep_actg_ext_t actg
WHERE actg.detail_tax_line_id = itf.detail_tax_line_id
AND actg.accounting_date between p_trl_global_variables_rec.gl_date_low
AND p_trl_global_variables_rec.gl_date_high)*/
DELETE from zx_rep_actg_ext_t actg
WHERE actg.request_id = p_request_id
AND NOT EXISTS ( SELECT 1 FROM zx_rep_trx_detail_t itf
WHERE actg.detail_tax_line_id = itf.detail_tax_line_id);
SELECT detail_tax_line_id,
trx_line_id,
trx_id,
tax_rate,
tax_rate_id,
tax_regime_code
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_line_id_tbl,
l_trx_id_tbl,
l_tax_rate_tbl,
l_tax_rate_id_tbl,
l_tax_regime_code_tbl
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
attribute10,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_tax_auth_categ_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
'After insertion into zx_rep_trx_jx_ext_t ');
SELECT min(detail_tax_line_id) detail_tax_line_id,
trx_id,
internal_organization_id,
null tax_rate,
null document_sub_type
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_internal_org_id_tbl,
l_tax_rate_tbl,
l_document_sub_type_tbl
FROM zx_rep_trx_detail_t itf,
ra_customer_trx_all rcta
WHERE itf.request_id = P_REQUEST_ID
AND rcta.customer_trx_id = itf.trx_id
AND itf.extract_source_ledger = 'AR'
AND itf.trx_line_class = 'CREDIT_MEMO'
AND rcta.global_attribute19 is NULL
AND substr(itf.trx_number,1,1) BETWEEN P_TRX_LETTER_FROM AND P_TRX_LETTER_TO
AND nvl(itf.tax_type_code, 'VAT') = 'VAT'
GROUP BY itf.internal_organization_id, itf.trx_id;
SELECT min(detail_tax_line_id) detail_tax_line_id,
trx_id,
trx_number,
internal_organization_id,
null tax_rate,
null document_sub_type
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_trx_number_tbl,
l_internal_org_id_tbl,
l_tax_rate_tbl,
l_document_sub_type_tbl
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = P_REQUEST_ID
AND itf.extract_source_ledger = 'AR'
AND itf.trx_line_class = 'CREDIT_MEMO'
AND NVL(itf.application_doc_status,'$')<>'VD'
AND substr(itf.trx_number,1,1) BETWEEN P_TRX_LETTER_FROM AND P_TRX_LETTER_TO
AND nvl(itf.tax_type_code, 'VAT') = 'VAT'
GROUP BY itf.internal_organization_id, itf.trx_id,
itf.trx_number ;
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
numeric9,
attribute11,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_vat_amt_tbl(i),
l_dgi_code_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
SELECT detail_tax_line_id,
itf1.trx_line_id,
trx_id,
internal_organization_id,
tax_rate,
document_sub_type,
currency_conversion_rate
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_line_id_tbl,
l_trx_id_tbl,
l_internal_org_id_tbl,
l_tax_rate_tbl,
l_document_sub_type_tbl,
l_exchange_rate_tbl
FROM zx_rep_trx_detail_t itf1
-- ra_customer_trx rct
WHERE itf1.request_id = P_REQUEST_ID
-- AND rct.customer_trx_id = itf1.trx_id
AND itf1.trx_line_class in ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
AND NVL(itf1.application_doc_status,'$') <>'VD'
AND itf1.tax =P_TRL_GLOBAL_VARIABLES_REC.VAT_PERCEPTION_TAX ;
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
numeric9,
numeric3,
numeric12,
attribute10,
attribute7,
attribute25,
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_taxable_amt_tbl(i),
l_vat_perc_amt_tbl(i),
l_total_doc_amt_tbl(i),
l_tax_authority_code,
l_cust_condition_code_tbl(i),
l_dgi_tax_regime_code_tbl(i),
p_request_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
'After insertion into zx_rep_trx_jx_ext_t ');
-- Delete Unwanted lines from Detail ITF
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
SELECT detail_tax_line_id,
trx_line_id,
trx_id,
internal_organization_id,
trx_number,
nvl(currency_conversion_rate,1), -- Bug 5374021
trx_type_id,
tax_rate,
tax_rate_id,
document_sub_type
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_line_id_tbl,
l_trx_id_tbl,
l_internal_org_id_tbl,
l_trx_number_tbl,
l_exchange_rate_tbl,
l_trx_type_id_tbl,
l_tax_rate_tbl,
l_tax_rate_id_tbl,
l_document_sub_type_tbl
FROM
( SELECT min(itf1.detail_tax_line_id) detail_tax_line_id,
itf1.trx_line_id,
itf1.trx_id trx_id,
itf1.internal_organization_id internal_organization_id,
itf1.trx_number trx_number,
itf1.currency_conversion_rate,
itf1.trx_type_id,
itf1.tax_rate tax_rate,
itf1.tax_rate_id,
null document_sub_type
FROM zx_rep_trx_detail_t itf1
WHERE itf1.request_id = P_REQUEST_ID
AND itf1.tax = p_vat_tax
AND nvl(itf1.tax_type_code,'VAT') = 'VAT'
AND itf1.tax_rate <> 0
GROUP BY itf1.internal_organization_id,
itf1.trx_line_id,
itf1.trx_id,
itf1.trx_number,
itf1.currency_conversion_rate,
itf1.trx_type_id,
itf1.tax_rate,
itf1.tax_rate_id
UNION
SELECT itf2.detail_tax_line_id,
itf2.trx_line_id,
itf2.trx_id trx_id,
itf2.internal_organization_id internal_organization_id,
itf2.trx_number trx_number,
itf2.currency_conversion_rate,
itf2.trx_type_id,
0 tax_rate,
itf2.tax_rate_id,
null document_sub_type
FROM zx_rep_trx_detail_t itf2
WHERE itf2.request_id = P_REQUEST_ID
AND NVL(itf2.tax_type_code, 'VAT') = 'VAT'
AND nvl(itf2.TAX_RATE,0) = 0
AND itf2.tax = p_vat_tax
AND not exists (SELECT 1
FROM zx_rep_trx_detail_t itf3
WHERE itf3.request_id = P_REQUEST_ID
AND itf2.trx_id = itf3.trx_id
AND NVL(itf3.tax_type_code, 'VAT') = 'VAT'
AND nvl(itf3.tax_rate,0) <> 0
AND itf3.tax = p_vat_tax)
/*AND rownum = 1 for GSI Bug# 7170003*/
UNION
--Query 3 : To pick once row per trx which have non-VAT taxes
SELECT itf4.detail_tax_line_id,
itf4.trx_line_id,
itf4.trx_id trx_id,
itf4.internal_organization_id internal_organization_id,
itf4.trx_number trx_number,
itf4.currency_conversion_rate,
itf4.trx_type_id,
itf4.tax_rate,
itf4.tax_rate_id,
null document_sub_type
FROM zx_rep_trx_detail_t itf4
WHERE itf4.request_id = P_REQUEST_ID
AND not exists ( SELECT 1
FROM zx_rep_trx_detail_t itf5
WHERE itf5.request_id = P_REQUEST_ID
AND itf5.trx_id = itf4.trx_id
AND itf5.tax = p_vat_tax )
AND itf4.ROWID = ( SELECT Min(itf6.ROWID)
FROM zx_rep_trx_detail_t itf6
WHERE itf4.trx_id = itf6.trx_id
AND itf6.request_id = P_REQUEST_ID )
);
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
numeric9,
numeric10,
numeric8,
numeric7,
numeric2,
numeric3,
numeric12,
attribute11,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_vat_amt_tbl(i),
l_taxable_amt_tbl(i),
l_non_taxable_amt_tbl(i),
l_vat_additional_amt_tbl(i),
l_vat_exempt_amt_tbl(i),
l_vat_perc_amt_tbl(i),
l_total_doc_amt_tbl(i),
l_dgi_code_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
'After insertion into zx_rep_trx_jx_ext_t ');
-- Delete Unwanted lines from Detail ITF
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
SELECT detail_tax_line_id,
trx_id,
trx_line_id,
trx_batch_source_id,
internal_organization_id,
trx_number,
currency_conversion_rate,
trx_type_id,
tax_rate,
tax_rate_id,
document_sub_type ,
tax_regime_code
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_trx_line_id_tbl,
l_batch_source_id_tbl,
l_internal_org_id_tbl,
l_trx_number_tbl,
l_exchange_rate_tbl,
l_trx_type_id_tbl,
l_tax_rate_tbl,
l_tax_rate_id_tbl,
l_document_sub_type_tbl,
l_tax_regime_code_tbl
FROM (SELECT min(dtl.detail_tax_line_id) detail_tax_line_id,
dtl.trx_id,
dtl.trx_line_id,
dtl.trx_batch_source_id,
dtl.internal_organization_id,
dtl.trx_number,
dtl.currency_conversion_rate,
dtl.trx_type_id,
dtl.tax_rate,
dtl.tax_rate_id,
null document_sub_type,
dtl.tax_regime_code
FROM zx_rep_trx_detail_t dtl,
ar_vat_tax_all vat
WHERE dtl.request_id = P_REQUEST_ID
AND nvl(vat.tax_type,'VAT') = 'VAT'
AND dtl.tax_rate_id = vat.vat_tax_id
-- AND dtl.doc_event_status = 'FROZEN_FOR_TAX'
AND dtl.tax_regime_code = p_tax_regime
AND dtl.tax = P_VAT_TAX
GROUP BY dtl.internal_organization_id,
dtl.trx_id,
dtl.trx_line_id,
dtl.trx_batch_source_id,
dtl.trx_number,
dtl.currency_conversion_rate,
dtl.trx_type_id, dtl.tax_rate,
dtl.tax_rate_id,
dtl.tax_regime_code
/*UNION
SELECT min(itf2.detail_tax_line_id) detail_tax_line_id,
trx_id,
trx_line_id,
internal_organization_id,
trx_number,
currency_conversion_rate,
trx_type_id,
null tax_rate,
null document_sub_type
FROM zx_rep_trx_detail_t itf2
WHERE itf2.request_id = P_REQUEST_ID
AND nvl(itf2.tax_type_code,'VAT') = 'VAT'
GROUP BY itf2.internal_organization_id,
itf2.trx_id, trx_line_id, trx_number, currency_conversion_rate,trx_type_id */
UNION
SELECT min(itf3.detail_tax_line_id) detail_tax_line_id,
itf3.trx_id,
itf3.trx_line_id,
itf3.trx_batch_source_id,
itf3.internal_organization_id,
itf3.trx_number,
itf3.currency_conversion_rate,
itf3.trx_type_id,
null tax_rate,
itf3.tax_rate_id,
null document_sub_type,
itf3.tax_regime_code
FROM zx_rep_trx_detail_t itf3
WHERE itf3.request_id = P_REQUEST_ID
AND itf3.tax = P_VAT_NON_TAXAB_TAX
AND itf3.tax_rate = 0
GROUP BY itf3.internal_organization_id,
itf3.trx_id, itf3.trx_line_id,itf3.trx_batch_source_id,
itf3.trx_number,
itf3.currency_conversion_rate,
itf3.trx_type_id,
itf3.tax_rate_id,
itf3.tax_regime_code);
SELECT min(detail_tax_line_id),
trx_line_id,
trx_id,
internal_organization_id,
trx_number,
trx_type_id,
to_number(null) tax_rate,
to_number(null) tax_rate_id,
to_char(null) document_sub_type
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_line_id_tbl,
l_trx_id_tbl,
l_internal_org_id_tbl,
l_trx_number_tbl,
l_trx_type_id_tbl,
l_tax_rate_tbl,
l_tax_rate_id_tbl,
l_document_sub_type_tbl
FROM zx_rep_trx_detail_t
WHERE request_id = P_REQUEST_ID
AND nvl(tax_type_code,'VAT') = 'VAT'
AND doc_event_status = 'FROZEN_FOR_TAX'
AND tax_regime_code = p_tax_regime
AND tax in (P_VAT_TAX,P_VAT_NON_TAXAB_TAX)
GROUP BY internal_organization_id,
trx_line_id,
trx_id,
trx_number,
trx_type_id;
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
numeric9,
numeric10,
numeric8,
-- numeric7,
numeric2,
numeric3,
numeric1,
numeric4,
numeric5,
numeric6,
numeric12,
numeric11,
numeric13,
attribute11,
attribute4,
attribute8,
gdf_ra_batch_sources_att7,
attribute19,
attribute23,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_vat_amt_tbl(i),
l_taxable_amt_tbl(i),
l_non_taxable_amt_tbl(i),
-- l_vat_additional_amt_tbl(i),
l_vat_exempt_amt_tbl(i),
l_vat_perc_amt_tbl(i),
l_not_reg_tax_amt_tbl(i),
l_prov_perc_amt_tbl(i),
l_munic_perc_amt_tbl(i),
l_excise_amt_tbl(i),
l_total_doc_amt_tbl(i),
l_rec_count_tbl(i),
l_rate_count_tbl(i),
l_dgi_code_tbl(i),
l_dgi_trx_code_tbl(i),
l_vat_reg_stat_code_tbl(i),
l_fiscal_printer_tbl(i),
l_cai_number_tbl(i),
l_cai_due_date_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
-- Delete Unwanted lines from Detail ITF
/* DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
UPDATE_DGI_CURR_CODE(p_request_id);
SELECT detail_tax_line_id,
trx_id,
internal_organization_id,
trx_number,
trx_type_id,
tax_rate,
tax_rate_id,
tax_regime_code,
document_sub_type
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_internal_org_id_tbl,
l_trx_number_tbl,
l_trx_type_id_tbl,
l_tax_rate_tbl,
l_tax_rate_id_tbl,
l_tax_regime_code_tbl,
l_document_sub_type_tbl
FROM zx_rep_trx_detail_t itf1,
jl_zz_ar_tx_categ categ
WHERE itf1.request_id = P_REQUEST_ID
-- AND itf1.tax_regime_code
AND itf1.tax = categ.tax_category
AND categ.tax_regime in (P_PROV_TAX_REGIME,
P_MUN_TAX_REGIME);
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
numeric4,
numeric5,
attribute11,
attribute1,
attribute3,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_prov_perc_amt_tbl(i),
l_munic_perc_amt_tbl(i),
l_dgi_code_tbl(i),
l_prov_juris_code_tbl(i),
l_mun_juris_code_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
-- Delete Unwanted lines from Detail ITF
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
SELECT min(itf.detail_tax_line_id),
itf.trx_line_id,
itf.trx_id,
itf.internal_organization_id,
itf.trx_number,
itf.tax_rate_id,
itf.DOC_SEQ_NAME document_sub_type
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_line_id_tbl,
l_trx_id_tbl,
l_internal_org_id_tbl,
l_trx_number_tbl,
l_tax_rate_tbl,
l_document_sub_type_tbl
FROM zx_rep_trx_detail_t itf,
zx_rates_b rates,
-- zx_report_codes_assoc repc,
-- zx_reporting_types_b reptypes,--Bug 5438742
ra_customer_trx_all ratrx,
ra_cust_trx_types_all types
WHERE itf.request_id = P_REQUEST_ID
AND itf.trx_id = ratrx.customer_trx_id
AND itf.tax_rate_id = rates.tax_rate_id
AND ratrx.cust_trx_type_id = types.cust_trx_type_id
-- AND repc.entity_id = rates.tax_rate_id
-- AND repc.entity_code = 'ZX_RATES'
AND itf.extract_source_ledger = 'AR'
AND itf.trx_line_class IN ('INVOICE', 'CREDIT_MEMO', 'DEBIT_MEMO')
AND (types.global_attribute7 = 'Y'
OR (types.global_attribute7 = 'N' AND types.global_attribute6 = 'Y' ))
-- AND repc.reporting_code_char_value is NOT NULL
GROUP BY itf.trx_id,
itf.trx_line_id,
itf.internal_organization_id,
itf.trx_number,
itf.tax_rate_id,
itf.DOC_SEQ_NAME; --Bug 5438742
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
numeric9,
numeric10,
numeric2,
numeric7,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_vat_amt_tbl(i),
l_taxable_amt_tbl(i),
l_vat_exempt_amt_tbl(i),
l_other_tax_amt_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
'After insertion into zx_rep_trx_jx_ext_t ');
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
SELECT detail_tax_line_id,
trx_line_id,
trx_id,
nvl(currency_conversion_rate,1),
tax_rate,
tax_rate_id,
document_sub_type
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_line_id_tbl,
l_trx_id_tbl,
l_exchange_rate_tbl,
l_tax_rate_tbl,
l_tax_rate_id_tbl,
l_document_sub_type_tbl
FROM zx_rep_trx_detail_t dtl, --Bug 5396444
ra_cust_trx_types_all tt
WHERE dtl.request_id = P_REQUEST_ID
AND dtl.trx_type_id = tt.cust_trx_type_id
AND tt.accounting_affect_flag = 'Y';
INSERT INTO zx_rep_trx_jx_ext_t(detail_tax_line_ext_id,
detail_tax_line_id,
numeric15,--total doc amt
numeric12,--extended amt
numeric9,--vat amt
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
(l_total_doc_amt_tbl(i) + l_vat_amt_tbl(i)) * l_exchange_rate_tbl(i), --Bug 5396444
l_extended_amt_tbl(i) * l_exchange_rate_tbl(i),--Bug 5396444
l_vat_amt_tbl(i) * l_exchange_rate_tbl(i),--Bug 5396444
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
'After insertion into zx_rep_trx_jx_ext_t ');
DELETE from zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND NOT EXISTS ( SELECT 1
FROM zx_rep_trx_jx_ext_t ext
WHERE ext.detail_tax_line_id = itf.detail_tax_line_id);
SELECT jtc.description
INTO l_tax_catg_desc
FROM JL_ZZ_AR_TX_CATEGRY jtc
WHERE jtc.tax_category = P_TRL_GLOBAL_VARIABLES_REC.VAT_TAX;
INSERT INTO zx_rep_trx_jx_ext_t
(detail_tax_line_ext_id,
detail_tax_line_id,
attribute1,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
(SELECT zx_rep_trx_jx_ext_t_s.nextval,
dtl.detail_tax_line_id,
l_tax_catg_desc,
dtl.created_by,
dtl.creation_date,
dtl.last_updated_by,
dtl.last_update_date,
dtl.last_update_login
FROM zx_rep_trx_detail_t dtl
WHERE dtl.request_id = p_request_id
);
'After insertion into zx_rep_trx_jx_ext_t ');
SELECT SUM(itf.tax_amt)
INTO l_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.tax_rate = p_tax_rate_tbl(i)
AND itf.tax_type_code = p_vat_tax;
SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
INTO l_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND (itf.reverse_flag IS NULL OR itf.reverse_flag <> 'Y')
AND itf.tax_type_code = 'VAT'
AND itf.tax_rate <> 0;
SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
INTO l_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.tax_type_code = 'VAT';
SELECT sum(nvl(itf.tax_amt_funcl_curr, itf.tax_amt))
INTO l_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021
AND itf.tax = p_vat_tax
AND nvl(itf.tax_type_code, 'VAT') = 'VAT'
AND nvl(itf.tax_rate,0) <> 0 ;
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx_all
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
INTO l_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.tax_type_code = 'VAT'
AND itf.tax_rate <> 0;
SELECT sum(nvl(itf.tax_amt,0))
INTO l_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.trx_id = p_trx_id_tbl(i);
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
--SELECT SUM(nvl(itf.tax_amt_funcl_curr,0))
SELECT SUM(nvl(itf.tax_amt,0))
INTO l_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.reverse_flag is null
AND itf.tax_type_code = p_vat_tax;
SELECT sum(itf.tax_amt)
INTO l_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
-- AND itf.posted_flag = 'Y'
AND itf.tax_type_code = p_vat_tax;
SELECT sum(nvl(itf.tax_amt,0))
INTO l_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.tax_regime_code = p_tax_regime
AND itf.tax = p_vat_tax
AND nvl(itf.tax_type_code, 'VAT') = 'VAT';
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx_all
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT sum(nvl(itf.tax_amt_funcl_curr, itf.tax_amt))
INTO l_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf,
ar_vat_tax_all vat
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.tax_regime_code = p_tax_regime
AND itf.tax = p_vat_tax
AND nvl(vat.tax_type,'VAT') = 'VAT'
AND itf.tax_rate = p_tax_rate_tbl(i)
AND itf.tax_rate_id = vat.vat_tax_id;
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT sum(nvl(itf.tax_amt,0))
INTO l_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
-- AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.tax_regime_code = p_tax_regime
AND itf.tax = p_vat_tax
AND nvl(itf.tax_type_code, 'VAT') = 'VAT'
AND nvl(itf.tax_rate,0) <> 0;
SELECT SUM(NVL(itf.taxable_amt,0))
INTO l_taxable_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.trx_line_id = p_trx_line_id(i)
AND itf.tax_type_code = p_vat_tax
AND itf.tax_rate = p_tax_rate_tbl(i)
AND itf.tax_rate <> 0;
SELECT SUM(nvl(itf.taxable_amt_funcl_curr,itf.taxable_amt))
INTO l_taxable_amt_tbl(p_trx_line_id(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.trx_line_id = p_trx_line_id(i)
--AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021
--AND itf.tax = p_vat_tax
and itf.tax_rate <> 0
AND itf.tax_rate = p_tax_rate_tbl(i);
SELECT SUM(nvl(itf.taxable_amt_funcl_curr,nvl(itf.taxable_amt,0)))
INTO l_taxable_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.trx_line_id = p_trx_line_id(i)
AND (itf.reverse_flag IS NULL OR itf.reverse_flag <> 'Y')
AND itf.tax_rate_id = P_TAX_RATE_TBL(i) --Bug 5413860
AND itf.tax_rate <> 0;
SELECT SUM(nvl(itf.taxable_amt_funcl_curr,nvl(itf.taxable_amt,0)))
INTO l_taxable_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.trx_line_id = p_trx_line_id(i)
AND itf.tax_rate_id = P_TAX_RATE_TBL(i) --Bug 5413860
AND itf.tax_rate <> 0;
SELECT (SUM(DECODE(ctl.line_type,'LINE', NVL(ctl.extended_amount,0),0))
+ SUM(DECODE(ctl.line_type,'FREIGHT',NVL(ctl.extended_amount,0),0))
+ SUM(DECODE(ctl.line_type,'CHARGE',NVL(ctl.extended_amount,0),0))) --* p_exchange_rate_tbl(i)
INTO l_taxable_amt_tbl(p_trx_line_id(i))
FROM ra_customer_trx_lines_all ctl
WHERE ctl.customer_trx_line_id = p_trx_line_id(i)
AND ctl.customer_trx_id = p_trx_id_tbl(i);
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_line_id(i))
FROM ra_customer_trx_all
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT SUM(nvl(itf.taxable_amt_funcl_curr,nvl(itf.taxable_amt,0)))
INTO l_taxable_amt_tbl(p_trx_line_id(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.trx_line_id = p_trx_line_id(i)
AND itf.tax_rate_id = P_TAX_RATE_TBL(i) --Bug 5413860
AND itf.tax_rate <> 0;
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT abs(nvl(SUM(itf.taxable_amt),0))
INTO l_taxable_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.trx_line_id = p_trx_line_id(i)
AND itf.tax_type_code = p_vat_tax
AND itf.tax_rate = p_tax_rate_tbl(i)
AND itf.tax_rate <> 0;
SELECT ABS(SUM(itf.taxable_amt_funcl_curr))
INTO l_taxable_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.trx_line_id = p_trx_line_id(i)
AND itf.trx_id = p_trx_id_tbl(i);
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx_all
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT NVL(sum(nvl(itf.taxable_amt_funcl_curr,itf.taxable_amt)),0)
INTO l_taxable_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.detail_tax_line_id = p_detail_tax_line_id(i)
AND itf.trx_line_id = p_trx_line_id(i)
AND itf.tax_regime_code = p_tax_regime
AND itf.tax = p_vat_tax
AND itf.tax_rate = p_tax_rate_tbl(i)
AND nvl(itf.tax_rate,0) <> 0;
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT SUM(itf.taxable_amt)
INTO l_non_taxable_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.trx_line_id = p_trx_line_id(i)
AND itf.tax_type_code = p_non_taxab_tax;
SELECT SUM(itf.taxable_amt_funcl_curr)
INTO l_non_taxable_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_line_id(i)
AND itf.tax NOT IN (p_vat_tax, p_vat_addit_tax,p_vat_percep_tax);
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT sum(nvl(itf.tax_amt ,0))
INTO l_non_taxable_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND nvl(itf.tax_type_code, 'VAT') = 'VAT'
AND itf.tax = p_non_taxab_tax;
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT SUM(NVL(itf.tax_amt_funcl_curr,itf.tax_amt))
INTO l_vat_additional_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
-- AND itf.tax_rate_id = p_tax_rate_id_tbl(i)
ANd itf.tax = p_vat_addit_tax;
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT nvl(SUM(itf.taxable_amt_funcl_curr),0)
INTO l_vat_exempt_amt_tbl(k)
FROM ZX_REP_TRX_DETAIL_T itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.trx_line_id = p_trx_line_id_tbl(i)
-- AND itf.tax_type_code = 'Exempt'
AND itf.tax_type_code = P_VAT_TAX
AND itf.tax_rate = 0;
SELECT SUM(nvl(itf.taxable_amt_funcl_curr,nvl(itf.taxable_amt,0)))
INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND (itf.reverse_flag IS NULL OR itf.reverse_flag <> 'Y')
AND itf.tax_rate = 0;
SELECT SUM(nvl(itf.taxable_amt_funcl_curr,nvl(itf.taxable_amt,0)))
INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_rate = 0;
SELECT sum(nvl(itf.taxable_amt_funcl_curr,itf.taxable_amt))
INTO l_vat_0_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND nvl(itf.tax_type_code,'VAT') = 'VAT'
AND itf.tax_rate = 0
AND itf.tax IN (p_vat_tax,p_vat_addit_tax,p_vat_percep_tax);
SELECT sum(nvl(itf.taxable_amt_funcl_curr,0))
INTO l_no_vat_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_rate = 0
AND ( itf.tax_regime_code <> p_tax_regime OR itf.tax <> p_vat_tax )
AND nvl(itf.tax_type_code,'VAT') = 'VAT';
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT NVL(sum(nvl(itf.taxable_amt_funcl_curr,0)),0)
INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
--INTO l_vat_exempt_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.trx_line_id = p_trx_line_id_tbl(i) -- new join
AND nvl(itf.tax_type_code,'VAT') = 'VAT'
AND itf.tax_rate = 0
AND itf.tax = p_vat_tax;
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx_all
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT Nvl(
Sum((itf.EXEMPT_RATE_MODIFIER * coalesce(itf.tax_amt_funcl_curr,itf.tax_amt,0))
/(100 - itf.EXEMPT_RATE_MODIFIER)) ,0)
INTO l_vat_exempt_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.TAX_EXEMPTION_ID IS NOT NULL ;
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
INTO l_vat_perc_amt_tbl(p_trx_line_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.trx_line_id = p_trx_line_id_tbl(i)
AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021
AND itf.tax = p_vat_perc_tax
AND nvl(itf.tax_type_code, 'VAT') = 'VAT';
SELECT nvl(SUM(itf.tax_amt),0)
INTO l_vat_perc_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_type_code >= P_VAT_PERC_TAX_TYPE_FROM
AND itf.tax_type_code <= NVL(P_VAT_PERC_TAX_TYPE_TO, P_VAT_PERC_TAX_TYPE_FROM);
SELECT sum(nvl(itf.tax_amt_funcl_curr,itf.tax_amt))
INTO l_vat_perc_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021
AND itf.tax = p_vat_perc_tax
AND nvl(itf.tax_type_code, 'VAT') = 'VAT';
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(k)
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
INTO l_vat_perc_amt_tbl(k)
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_regime_code = p_tax_regime
AND itf.tax = p_vat_perc_tax
AND nvl(itf.tax_type_code, 'VAT') = 'VAT';
SELECT nvl(SUM(itf.tax_amt),0)
INTO l_vat_perc_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_type_code >= P_VAT_PERC_TAX_TYPE_FROM
AND itf.tax_type_code <= NVL(P_VAT_PERC_TAX_TYPE_TO, P_VAT_PERC_TAX_TYPE_FROM);
SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
INTO l_vat_perc_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_regime_code = nvl(p_tax_regime,itf.tax_regime_code)--Bug 5374021
AND itf.tax = p_vat_perc_tax
AND nvl(itf.tax_type_code, 'VAT') = 'VAT';
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT sum(nvl(itf.tax_amt_funcl_curr,0))
INTO l_vat_perc_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_regime_code = p_tax_regime
AND itf.tax = p_vat_perc_tax
AND nvl(itf.tax_type_code, 'VAT') = 'VAT';
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT NVL(SUM(itf.tax_amt),0)
INTO l_other_fed_perc_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_type_code >= P_FED_PERC_TAX_TYPE_FROM
AND itf.tax_type_code <= NVL(P_FED_PERC_TAX_TYPE_TO, P_FED_PERC_TAX_TYPE_FROM);
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
INTO l_other_tax_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = P_REQUEST_ID
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_type_code <> 'VAT'
AND (itf.reverse_flag IS NULL OR itf.reverse_flag <> 'Y');
SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
INTO l_other_tax_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_type_code <> 'VAT';
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx_all
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT SUM(nvl(itf.tax_amt_funcl_curr,nvl(itf.tax_amt,0)))
INTO l_other_tax_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = P_REQUEST_ID
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_type_code <> 'VAT' ;
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx_all
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT sum(nvl(itf.tax_amt,0))
INTO l_vat_addit_tax_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_regime_code = p_tax_regime
AND itf.tax = p_vat_addit_tax
AND nvl(itf.tax_type_code, 'VAT') = 'VAT';
SELECT sum(nvl(itf.tax_amt,0))
INTO l_not_categ_tax_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_regime_code = p_tax_regime
AND itf.tax = p_vat_not_categ_tax
AND nvl(itf.tax_type_code, 'VAT') = 'VAT';
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT NVL(SUM(itf.tax_amt),0)
INTO l_provincial_perc_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_type_code >= P_PROV_TAX_TYPE_FROM
AND itf.tax_type_code <= P_PROV_TAX_TYPE_TO;
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT sum(nvl(itf.tax_amt,0))
INTO l_provincial_perc_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_regime_code = p_prov_tax_regime
AND nvl(itf.tax_type_code,'VAT') = 'VAT';
SELECT sum(nvl(itf.tax_amt,0))
INTO l_provincial_perc_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf,
jl_zz_ar_tx_categ categ
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
-- AND itf.tax_regime_code = p_prov_tax_regime
AND nvl(itf.tax_type_code,'VAT') = 'VAT'
AND itf.tax_rate_id = p_tax_rate_id_tbl(i)
AND categ.tax_category = itf.tax
AND categ.tax_regime = p_prov_tax_regime
AND categ.org_id = itf.internal_organization_id;
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT nvl(SUM(itf.tax_amt),0)
INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_type_code >= P_MUN_TAX_TYPE_FROM
AND itf.tax_type_code <= P_MUN_TAX_TYPE_TO;
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT sum(nvl(itf.tax_amt,0))
INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_regime_code = p_mun_tax_regime
AND nvl(itf.tax_type_code,'VAT') = 'VAT';
SELECT sum(nvl(itf.tax_amt,0))
INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf,
jl_zz_ar_tx_categ categ
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
-- AND itf.tax_regime_code = p_mun_tax_regime
AND nvl(itf.tax_type_code,'VAT') = 'VAT'
AND itf.tax_rate_id = p_tax_rate_id_tbl(i)
AND categ.tax_category = itf.tax
AND categ.tax_regime = p_mun_tax_regime
AND categ.org_id = itf.internal_organization_id;
/* SELECT sum(nvl(itf.tax_amt,0))
INTO l_municipal_perc_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_regime_code = p_mun_tax_regime
AND nvl(itf.tax_type_code,'VAT') = 'VAT'
AND itf.tax_rate_id = p_tax_rate_id_tbl(i);
SELECT count(distinct trx_id)
INTO l_trx_counter
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT nvl(SUM(itf.tax_amt),0)
INTO l_excise_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
AND itf.tax_type_code >= P_EXC_TAX_TYPE_FROM
AND itf.tax_type_code <= P_EXC_TAX_TYPE_TO;
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT sum(nvl(itf.tax_amt,0))
INTO l_excise_amt_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_request_id
AND itf.trx_id = p_trx_id_tbl(i)
--AND itf.tax_regime_code = p_exc_tax_regime
AND itf.tax = p_exc_tax_regime
AND nvl(itf.tax_type_code,'VAT') = 'VAT';
SELECT COUNT(DISTINCT DET.TRX_NUMBER),
/* SUM(DECODE(DET.TAX_RATE,0,
coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0),
0)),
SUM(DECODE(DET.TAX_RATE,0,
0,
coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0))),*/
SUM(DECODE(DET.TAX_TYPE_CODE,'VAT',
coalesce(DET.TAX_AMT_FUNCL_CURR,DET.TAX_AMT,0),
0)),
SUM(DECODE(DET.TAX_TYPE_CODE,'VAT',
0,
coalesce(DET.TAX_AMT_FUNCL_CURR,DET.TAX_AMT,0)))
INTO X_CL_NUM_OF_DOC_TBL(i),
-- X_CL_TOTAL_EXEMPT_TBL(i),
-- X_CL_TOTAL_EFFECTIVE_TBL(i),
X_CL_TOTAL_VAT_TAX_TBL(i),
X_CL_TOTAL_OTHER_TAX_TBL(i)
FROM ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_REQUEST_ID
AND DET.DOCUMENT_SUB_TYPE = P_DOCUMENT_SUB_TYPE_TBL(i)
GROUP BY DET.DOCUMENT_SUB_TYPE;
SELECT SUM(DECODE(DET.TAX_RATE,0,
coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0),
0)),
SUM(DECODE(DET.TAX_RATE,0,
0,
coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0)))
INTO
X_CL_TOTAL_EXEMPT_TBL(i),
X_CL_TOTAL_EFFECTIVE_TBL(i)
FROM ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_REQUEST_ID
AND DET.DOCUMENT_SUB_TYPE = P_DOCUMENT_SUB_TYPE_TBL(i)
AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1
WHERE det1.trx_id = det.trx_id
AND det1.request_id = P_REQUEST_ID
AND nvl(det1.trx_line_id,1) = nvl(det.trx_line_id,1) )--check if trx_line_id should be populated at TRANSACTION Level
GROUP BY DET.DOCUMENT_SUB_TYPE;
SELECT COUNT(DISTINCT DET.TRX_NUMBER),
SUM(DECODE(DET.TAX_TYPE_CODE,'VAT',
coalesce(DET.TAX_AMT_FUNCL_CURR,DET.TAX_AMT,0),
0)),
SUM(DECODE(DET.TAX_TYPE_CODE,'VAT',
0,
coalesce(DET.TAX_AMT_FUNCL_CURR,DET.TAX_AMT,0)))
INTO X_CL_NUM_OF_DOC_TBL(i),
X_CL_TOTAL_VAT_TAX_TBL(i),
X_CL_TOTAL_OTHER_TAX_TBL(i)
FROM ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_REQUEST_ID
AND DET.doc_seq_name = P_DOCUMENT_SUB_TYPE_TBL(i)
GROUP BY DET.DOCUMENT_SUB_TYPE;
SELECT
SUM(DECODE(DET.TAX_RATE,0,
0,
coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0)))
INTO
X_CL_TOTAL_EFFECTIVE_TBL(i)
FROM ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_REQUEST_ID
AND DET.DOC_SEQ_NAME = P_DOCUMENT_SUB_TYPE_TBL(i)
AND det.ROWID = ( SELECT min(det1.rowid) FROM zx_rep_trx_detail_t det1
WHERE det1.trx_id = det.trx_id
AND det1.request_id = P_REQUEST_ID
AND nvl(det1.trx_line_id,1) = nvl(det.trx_line_id,1) )--check if trx_line_id should be populated at TRANSACTION Level
GROUP BY DET.DOCUMENT_SUB_TYPE;
SELECT
fl.meaning,
DECODE(fl.lookup_code, 'JL_CL_DOMESTIC_INVOICE','1',
'JL_CL_FOREIGN_INVOICE','2',
'JL_CL_DEBIT_MEMO','3',
'JL_CL_CREDIT_MEMO','4',
'5')
INTO
x_jlcl_ap_doc_type_meaning_tbl(i),
x_order_by_doc_type_tbl(i)
FROM
fnd_lookups fl
WHERE
fl.lookup_type = 'JLCL_AP_DOCUMENT_TYPE'
AND
fl.lookup_code = substr(P_DOCUMENT_SUB_TYPE_TBL(i),15); --Bug 5413860
SELECT tax_authority_code
INTO l_tax_authority_code
FROM jl_zz_ar_tx_categ
WHERE tax_category = p_vat_tax
AND org_id = p_org_id;
SELECT dgi_code
INTO x_dgi_code_tbl(i)
FROM jl_ar_ap_trx_dgi_codes dgi,
ra_cust_trx_types_all rctt
WHERE trx_letter = substr(p_trx_number_tbl(i),1,1)
AND rctt.cust_trx_type_id = P_TRX_CATEGORY_TBL(i)
AND rctt.org_id = p_org_id_tbl(i)
AND trx_category = rctt.type;
SELECT count(distinct trx_id)
INTO l_counted_trx_number
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT add1.cust_acct_site_id,
add1.global_attribute8,
NVL(add1.global_attribute9,'N')
INTO l_address_id,
l_contributor_class,
l_use_site_prof
FROM ra_customer_trx_all trx,
hz_cust_accounts cust,
hz_cust_acct_sites_all add1,
hz_cust_site_uses_all site
WHERE trx.customer_trx_id = p_trx_id_tbl(i)
AND cust.cust_account_id = nvl(trx.ship_to_customer_id,trx.bill_to_customer_id)
AND cust.cust_account_id = add1.cust_account_id
AND site.cust_acct_site_id = add1.cust_acct_site_id
AND site.site_use_id = nvl(trx.bill_to_site_use_id, ship_to_site_use_id);
SELECT VAL.TAX_ATTR_VALUE_CODE
INTO l_cust_condition_code_tbl(p_trx_id_tbl(i))
FROM JL_ZZ_AR_TX_CUS_CLS_ALL cust,
JL_ZZ_AR_TX_CATEG_ALL categ,
JL_ZZ_AR_TX_ATT_VAL_ALL val
WHERE CUST.TAX_ATTRIBUTE_VALUE = VAL.TAX_ATTRIBUTE_VALUE and
CUST.TAX_CATEGORY_ID = VAL.TAX_CATEGORY_ID and
CUST.TAX_CATEGORY_ID = CATEG.TAX_CATEGORY_ID and
CUST.TAX_ATTRIBUTE_NAME = VAL.TAX_ATTRIBUTE_NAME and
VAL.TAX_ATTRIBUTE_TYPE = 'CONTRIBUTOR_ATTRIBUTE' and
CATEG.TAX_CATEGORY = P_VAT_PERCEP_TAX and
CUST.TAX_ATTR_CLASS_CODE = l_contributor_class and
CUST.ADDRESS_ID = L_ADDRESS_ID AND
CUST.ORG_ID = CATEG.ORG_ID AND
CATEG.ORG_ID = VAL.ORG_ID AND
VAL.ORG_ID = P_INTERNAL_ORG_ID_TBL(I);
SELECT VAL.TAX_ATTR_VALUE_CODE
INTO l_cust_condition_code_tbl(p_trx_id_tbl(i))
FROM JL_ZZ_AR_TX_ATT_CLS_ALL cust,
JL_ZZ_AR_TX_CATEG_ALL categ,
JL_ZZ_AR_TX_ATT_VAL val
WHERE CUST.TAX_ATTRIBUTE_VALUE = VAL.TAX_ATTRIBUTE_VALUE and
CUST.TAX_CATEGORY_ID = VAL.TAX_CATEGORY_ID and
CUST.TAX_CATEGORY_ID = CATEG.TAX_CATEGORY_ID and
CUST.TAX_ATTRIBUTE_NAME = VAL.TAX_ATTRIBUTE_NAME and
VAL.TAX_ATTRIBUTE_TYPE = 'CUSTOMER_ATTRIBUTE' and
CATEG.TAX_CATEGORY = P_VAT_PERCEP_TAX and
CUST.TAX_ATTR_CLASS_TYPE = 'CONTRIBUTOR_CLASS' and
CUST.TAX_ATTR_CLASS_CODE = L_CONTRIBUTOR_CLASS AND
CUST.ORG_ID = CATEG.ORG_ID AND
CATEG.ORG_ID = VAL.ORG_ID AND
VAL.ORG_ID = P_INTERNAL_ORG_ID_TBL(I);
SELECT count(distinct trx_id)
INTO l_counted_trx_number
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT val.tax_attr_value_code
INTO l_dgi_tax_regime_code_tbl(p_trx_line_id_tbl(i))
FROM zx_lines lines,
jl_zz_ar_tx_categ_all catg,
jl_zz_ar_tx_att_cls_all attcls,
jl_zz_ar_tx_att_val_all val
WHERE catg.tax_category = P_VAT_PERCEP_TAX
ANd catg.tax_category = lines.tax
AND attcls.tax_attribute_value = val.tax_attribute_value
AND attcls.tax_category_id = val.tax_category_id
AND attcls.tax_attribute_name = val.tax_attribute_name
AND val.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
AND attcls.tax_category_id = catg.tax_category_id
AND attcls.tax_attr_class_code = lines.global_attribute3
AND lines.trx_id = p_trx_id_tbl(i)
AND lines.trx_line_id = p_trx_line_id_tbl(i)
AND attcls.ORG_ID = CATG.ORG_ID
AND CATG.ORG_ID = VAL.ORG_ID
AND lines.internal_organization_id = val.org_id
AND VAL.ORG_ID = P_INTERNAL_ORG_ID_TBL(i);
SELECT count(distinct trx_id)
INTO l_counted_trx_number
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id;
SELECT add1.cust_acct_site_id,
add1.global_attribute8,
NVL(add1.global_attribute9,'N')
INTO l_address_id,
l_class_code,
l_use_site_prof
FROM ra_customer_trx_all trx,
hz_cust_accounts cust,
hz_cust_acct_sites_all add1,
hz_cust_site_uses_all site
WHERE trx.customer_trx_id = p_trx_id_tbl(i)
AND cust.cust_account_id = NVL(trx.ship_to_customer_id,trx.bill_to_customer_id)
AND cust.cust_account_id = add1.cust_account_id
AND site.cust_acct_site_id = add1.cust_acct_site_id
AND site.site_use_id = NVL(trx.bill_to_site_use_id, ship_to_site_use_id);
SELECT lkp.meaning
INTO l_vat_reg_stat_code_tbl(p_trx_id_tbl(i))
FROM jl_zz_ar_tx_cus_cls_all cls,
jl_zz_ar_tx_categ_all catg,
fnd_lookups lkp
WHERE cls.tax_attr_class_code = l_class_code
AND cls.tax_attribute_name = 'VAT CONT STATUS'
AND cls.tax_category_id = catg.tax_category_id
AND cls.address_id = l_address_id
AND lkp.lookup_type = 'JLZZ_AR_TX_ATTR_VALUE'
AND lkp.lookup_code = cls.tax_attribute_value
AND cls.enabled_flag = 'Y'
AND cls.org_id = catg.org_id
AND cls.org_id = p_internal_org_id_tbl(i)
AND catg.tax_category = p_vat_tax;
SELECT lkp.meaning
INTO l_vat_reg_stat_code_tbl(p_trx_id_tbl(i))
FROM jl_zz_ar_tx_att_cls_all cls,
jl_zz_ar_tx_categ_all catg,
fnd_lookups lkp
WHERE cls.tax_attr_class_type = 'CONTRIBUTOR_CLASS'
AND cls.tax_attr_class_code = l_class_code
AND cls.tax_attribute_name = 'VAT CONT STATUS'
AND lkp.lookup_type = 'JLZZ_AR_TX_ATTR_VALUE'
AND lkp.lookup_code = cls.tax_attribute_value
AND cls.tax_attribute_type = 'CONTRIBUTOR_ATTRIBUTE'
AND cls.enabled_flag = 'Y'
AND cls.tax_category_id = catg.tax_category_id
AND catg.tax_category = p_vat_tax
AND cls.org_id = catg.org_id
AND cls.org_id = p_internal_org_id_tbl(i);
SELECT count(distinct tax_rate)
INTO l_rec_count_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t
WHERE request_id = p_request_id
AND trx_id = p_trx_id_tbl(i)
AND tax_regime_code = p_tax_regime
AND tax = p_vat_tax;
SELECT global_attribute11,
global_attribute12,
global_attribute13
INTO l_gdf_ap_inv_att11_tbl(p_trx_id_tbl(i)),
l_gdf_ap_inv_att12_tbl(p_trx_id_tbl(i)),
l_dgi_doc_type_tbl(p_trx_id_tbl(i))
FROM ap_invoices_all
WHERE invoice_id = p_trx_id_tbl(i);
PROCEDURE UPDATE_DGI_CURR_CODE
(
P_REQUEST_ID IN NUMBER
)
IS
BEGIN
IF (g_level_procedure >= g_current_runtime_level ) THEN
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.UPDATE_DGI_CURR_CODE.END',
'UPDATE_DGI_CURR_CODE(+)');
UPDATE zx_rep_trx_detail_t dtl
SET gdf_fnd_currencies_att1= (SELECT global_attribute1
FROM fnd_currencies
WHERE currency_code = dtl.trx_currency_code)
WHERE request_id = p_request_id;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.ZX_JL_EXTRACT_PKG.UPDATE_DGI_CURR_CODE.END',
'UPDATE_DGI_CURR_CODE(-)');
SELECT count(distinct t1.tax_rate)
INTO l_rate_count_tbl(p_trx_id_tbl(i))
FROM zx_rep_trx_detail_t t1
WHERE t1.request_id = p_request_id
AND t1.trx_id = p_trx_id_tbl(i)
AND NVL(t1.TAX_TYPE_CODE, 'VAT') = 'VAT'
AND tax_regime_code = p_tax_regime
AND tax in (p_vat_tax, p_vat_non_tax)
AND EXISTS ( SELECT 1
FROM zx_rep_trx_detail_t t2
WHERE t2.request_id = p_request_id
AND t2.trx_id = t1.trx_id
AND t2.tax_rate = 0
AND t2.tax_regime_code = p_tax_regime
AND t2.tax = p_vat_non_tax);
SELECT abs(sum(nvl(extended_amount,0) * nvl(p_exchange_rate_tbl(i),1)))
INTO l_total_doc_amt_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT (SUM(DECODE(ctl.line_type,'LINE', NVL(ctl.extended_amount,0),0))
+ SUM(DECODE(ctl.line_type,'FREIGHT',NVL(ctl.extended_amount,0),0))
+ SUM(DECODE(ctl.line_type,'CHARGE',NVL(ctl.extended_amount,0),0)))-- * p_exchange_rate_tbl(i)
INTO l_total_doc_amt_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx_lines_all ctl --Bug 5396444
WHERE ctl.customer_trx_id = p_trx_id_tbl(i);
SELECT (nvl(SUM(NVL(L.GROSS_EXTENDED_AMOUNT, nvl(L.EXTENDED_AMOUNT,0))),0) * p_exchange_rate_tbl(i))
INTO l_total_doc_amt_tbl(p_trx_id_tbl(i))
FROM RA_CUSTOMER_TRX_LINES L
WHERE L.CUSTOMER_TRX_ID = p_trx_id_tbl(i);
SELECT decode(global_attribute19,NULL,'IS_NULL','NOT_NULL')
INTO l_gdf_ra_cust_trx_att19_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx_all
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT nvl(SUM(abs(NVL(GROSS_EXTENDED_AMOUNT, nvl(EXTENDED_AMOUNT,0)))),0)
INTO l_total_doc_amt_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT (nvl(SUM(NVL(L.GROSS_EXTENDED_AMOUNT, nvl(L.EXTENDED_AMOUNT,0))),0)
* nvl(p_exchange_rate_tbl(i),1))
INTO l_total_doc_amt_tbl(p_trx_id_tbl(i))
FROM RA_CUSTOMER_TRX_LINES L
WHERE L.CUSTOMER_TRX_ID = p_trx_id_tbl(i);
SELECT abs(nvl(SUM(NVL(aid.base_amount, aid.amount)),0))
INTO l_total_doc_amt_tbl(p_trx_id_tbl(i))
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = p_trx_id_tbl(i)
AND aid.line_type_lookup_code <> 'AWT';
SELECT rep_ass.reporting_code_char_value
INTO l_dgi_trx_code_tbl(p_tax_rate_id_tbl(i))
FROM zx_reporting_types_b rep_type,
zx_report_codes_assoc rep_ass
WHERE rep_type.reporting_type_code = 'AR_DGI_TRX_CODE'
AND rep_ass.reporting_type_id = rep_type.reporting_type_id
AND rep_ass.entity_code = 'ZX_RATES'
AND rep_ass.entity_id =P_TAX_RATE_ID_TBL(i)
AND rep_type.tax_regime_code =P_TAX_REGIME_CODE_TBL(i);
SELECT rep_ass.reporting_code_char_value
INTO l_tax_auth_categ_tbl(p_tax_rate_id_tbl(i))
FROM zx_reporting_types_b rep_type,
zx_report_codes_assoc rep_ass
WHERE rep_type.reporting_type_code = 'AR_TAX_AUTHORITY_CATEG'
AND rep_ass.reporting_type_id = rep_type.reporting_type_id
AND rep_ass.entity_code = 'ZX_RATES'
AND rep_ass.entity_id =P_TAX_RATE_ID_TBL(i)
AND rep_type.tax_regime_code =P_TAX_REGIME_CODE_TBL(i);
/* SELECT rep_ass.reporting_code_char_value
INTO l_prov_juris_code_tbl(p_tax_rate_id_tbl(i))
FROM zx_reporting_types_b rep_type,
zx_report_codes_assoc rep_ass
WHERE rep_type.reporting_type_code = 'AR_TURN_OVER_JUR_CODE'
AND rep_ass.reporting_type_id = rep_type.reporting_type_id
AND rep_ass.entity_code = 'ZX_RATES'
AND rep_ass.entity_id =P_TAX_RATE_ID_TBL(i)
AND rep_type.tax_regime_code =P_TAX_REGIME_CODE_TBL(i);
SELECT global_attribute5
INTO l_prov_juris_code_tbl(p_tax_rate_id_tbl(i))
FROM ar_vat_tax_all
WHERE VAT_TAX_ID = P_TAX_RATE_ID_TBL(i);
/* SELECT rep_ass.reporting_code_char_value
INTO l_mun_juris_code_tbl(p_tax_rate_id_tbl(i))
FROM zx_reporting_types_b rep_type,
zx_report_codes_assoc rep_ass
WHERE rep_type.reporting_type_code = 'AR_MUNICIPAL_JUR'
AND rep_ass.reporting_type_id = rep_type.reporting_type_id
AND rep_ass.entity_code = 'ZX_RATES'
AND rep_ass.entity_id =P_TAX_RATE_ID_TBL(i)
AND rep_type.tax_regime_code =P_TAX_REGIME_CODE_TBL(i);
SELECT global_attribute6
INTO l_mun_juris_code_tbl(p_tax_rate_id_tbl(i))
FROM ar_vat_tax_all
WHERE VAT_TAX_ID = P_TAX_RATE_ID_TBL(i);
SELECT rep_ass.reporting_code_char_value
INTO l_cai_num_tbl(p_trx_id_tbl(i))
FROM zx_reporting_types_b rep_type,
zx_report_codes_assoc rep_ass
WHERE rep_type.reporting_type_code = 'CAI NUMBER'
AND rep_ass.reporting_type_id = rep_type.reporting_type_id
AND rep_ass.entity_code = 'ZX_PARTY_TAX_PROFILE'
AND rep_ass.entity_id = P_BILL_FROM_SITE_PROF_ID_TBL(i)
AND rep_type.tax_regime_code = P_TAX_REGIME_CODE_TBL(i);
SELECT nvl(global_attribute19,0),nvl(global_attribute20,' ')
INTO l_cai_num_tbl(p_trx_id_tbl(i)),
l_cai_due_date_tbl(p_trx_id_tbl(i))
FROM ap_supplier_sites_all
WHERE vendor_site_id = P_BILL_FROM_SITE_ID_TBL(i);
SELECT global_attribute17,global_attribute18
INTO l_cai_num_tbl(p_trx_id_tbl(i)),
l_cai_due_date_tbl(p_trx_id_tbl(i))
FROM ra_customer_trx
WHERE customer_trx_id = p_trx_id_tbl(i);
SELECT rep_ass.reporting_code_char_value
INTO l_rep_fiscal_printer_tbl(i)
FROM zx_reporting_types_b rep_type,
zx_report_codes_assoc rep_ass
WHERE rep_type.reporting_type_code = 'FISCAL PRINTER'
AND rep_ass.reporting_type_id = rep_type.reporting_type_id
AND rep_ass.entity_code = 'ZX_PARTY_TAX_PROFILE'
AND rep_ass.entity_id = P_BILL_FROM_SITE_PROF_ID_TBL(i)
AND rep_type.tax_regime_code = P_TAX_REGIME_CODE_TBL(i);
SELECT global_attribute18
INTO l_fiscal_printer_tbl(i)
FROM ap_supplier_sites_all
WHERE vendor_site_id = P_BILL_FROM_SITE_ID_TBL(i);
SELECT global_attribute10, global_attribute1
INTO l_taxpayerid_type_tbl(p_trx_id_tbl(i)),
l_reg_status_code_tbl(p_trx_id_tbl(i))
FROM ap_suppliers
WHERE vendor_id = P_BILL_FROM_TP_ID_TBL(i);
SELECT global_attribute7
INTO l_fiscal_printer_tbl(i)
FROM ra_batch_sources_all
WHERE batch_source_id = P_BATCH_SOURCE_ID_TBL(i);
SELECT SUM(DECODE(DET.TAX_RATE,0,
0,
coalesce(DET.TAXABLE_AMT_FUNCL_CURR,DET.TAXABLE_AMT,0)))
INTO l_total_doc_taxab_amt_tbl(p_trx_id_tbl(i))
FROM ZX_REP_TRX_DETAIL_T DET
WHERE REQUEST_ID = P_REQUEST_ID
AND TRX_ID = p_trx_id_tbl(i);