[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Above_Threshold_Lines;
PROCEDURE Insert_Above_Threshold_Lines;
SELECT freeze_indicator_flag
FROM je_it_setup_header_all
WHERE year_of_declaration = p_year
AND vat_reporting_entity_id = p_vat_reporting_entity_id;
SELECT report_mode
FROM je_it_above_thr_hdr_all
WHERE year_of_declaration = p_year
AND vat_reporting_entity_id = p_vat_reporting_entity_id
AND type_of_upload = p_type_of_upload;
g_last_updated_by := NVL(fnd_global.USER_ID,1);
g_last_update_date := sysdate;
g_last_update_login := 1;
SELECT legal_entity_id
INTO g_legal_entity_id
FROM jg_zz_vat_rep_entities
WHERE VAT_REPORTING_ENTITY_ID = g_vat_reporting_entity_id;
SELECT owner
INTO g_table_schema
FROM all_tables
WHERE table_name = 'JE_IT_TRX_LINES_ALL'
AND OWNER = l_schema_name;
SELECT meaning INTO l_type_of_upload_mng
FROM fnd_lookups
WHERE lookup_type='JEIT_UPLOAD_TYPE'
AND lookup_code=p_type_of_upload;
SELECT COUNT(*)
INTO l_count
FROM je_it_above_thr_hdr_all
WHERE year_of_declaration = p_year
AND vat_reporting_entity_id = p_vat_reporting_entity_id
AND type_of_upload in (0,2)
AND report_mode = 'F';
SELECT COUNT(*)
INTO l_count
FROM je_it_above_thr_hdr_all
WHERE year_of_declaration = p_year
AND vat_reporting_entity_id = p_vat_reporting_entity_id
AND type_of_upload in (0,1)
AND report_mode = 'F';
SELECT default_payment_mode
INTO g_default_payment_mode
FROM je_it_setup_header_all
WHERE year_of_declaration = p_year
AND vat_reporting_entity_id = p_vat_reporting_entity_id
AND ROWNUM = 1; -- form has validation. only one record exists for declaration year.
INSERT INTO je_it_above_thr_hdr_all (vat_reporting_entity_id,
year_of_declaration, -- no need of storing the company details in hdr table. write a separate query in xml file.
org_id,
report_mode,
type_of_upload,
transmission_code,
protocol_num,
province_code,
declr_of_incorp_comp,
trx_accounting_status,
end_date_to_extr_cm_dm,
b2c_invoice_threshold,
b2b_invoice_threshold,
start_date_to_extr_b2c_trx,
no_of_detailed_records,
request_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by )
SELECT p_vat_reporting_entity_id,
p_year,
g_org_id,
'P',
p_type_of_upload,
p_transmission_code,
p_protocol_num,
p_province_code,
p_decl_inc_company,
p_inv_acctg_status,
g_end_date_for_cm_dm,
p_b2c_threshold,
p_b2b_threshold,
g_b2c_trx_start_date,
p_max_no_of_records,
g_conc_request_id,
g_last_update_date,
g_last_updated_by,
g_last_update_login,
g_creation_date,
g_created_by
FROM dual;
errbuf := 'Error while inserting data into JE_IT_ABOVE_THR_HDR_ALL table '||SQLCODE||'-'||SQLERRM;
FND_LOG.STRING(G_LEVEL_EXCEPTION,G_MODULE_NAME||l_api_name,'Error while inserting data into je_it_list_hdr_all table');
debug_message('Error while inserting data into hdr table'||SQLCODE||'-'||SQLERRM);
Extract_AP_Trx_data; -- Extract AP data and inserts into je_it_trx_lines_all table.
Update_Above_Threshold_Lines;
Insert_Above_Threshold_Lines;
SELECT LOWER(FL.iso_language)
,FL.iso_territory
INTO l_template_language
,l_template_territory
FROM NLS_SESSION_PARAMETERS NSP
,fnd_languages FL
WHERE NSP.parameter = 'NLS_LANGUAGE'
AND NSP.value = FL.nls_language;
SELECT REQUEST_ID
INTO l_conc_request_id
FROM je_it_above_thr_hdr_all
WHERE year_of_declaration = p_year
AND vat_reporting_entity_id = p_vat_reporting_entity_id
AND type_of_upload = p_type_of_upload;
DELETE je_it_trx_lines_all
WHERE REQUEST_ID = l_conc_request_id;
DELETE je_it_trx_above_thr_all
WHERE REQUEST_ID = l_conc_request_id;
DELETE je_it_above_thr_hdr_all
WHERE REQUEST_ID = l_conc_request_id;
debug_message('after delete');
INSERT INTO je_it_trx_lines_all (REQUEST_ID,
VAT_REPORTING_ENTITY_ID,
ORG_ID,
APPLICATION_ID,
PARTY_ID,
PARTY_NAME,
VAT_REGISTRATION_NUM,
TAX_PAYER_ID,
COUNTRY,
TRX_ID,
TRX_NUM,
TRX_DATE,
TRX_GL_DATE,
TRX_TYPE,
PAYMENT_METHOD,
TRX_ACCOUNTING_STATUS,
trx_line_id,
TRX_LINE_NUM,
TRX_LINE_TYPE,
ASSESSABLE_AMT,
VAT_AMT,
PAYMENT_MODE,
BELOW_THRESHOLD_FLAG,
REPORT_EXCLUSION_FLAG,
CONTRACT_IDENTIFICATION,
ADJ_INV_FLAG,
ORIG_TRX_ID,
PARTY_TYPE,
INDV_PARTY_LAST_NAME, --not required. Same as vendor name
INDV_PARTY_FIRST_NAME,
INDV_PARTY_DOB,
INDV_PARTY_CITY,
INDV_PARTY_PROVINCE,
COMPANY_CITY,
COMPANY_ADDRESS,
INCONST_CM_DM_APPL_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY)
select g_conc_request_id,
g_vat_reporting_entity_id,
null,
200,
pv.vendor_id,
substr(pv.vendor_name,1,60),
substr(NVL(pvs.vat_registration_num,pv.vat_registration_num),1,11),
NULL, -- taxpayer_id not required for payables as there is no need of B2C transactions
pvs.country,
ai.invoice_id,
ai.invoice_num,
ai.invoice_date,
ai.gl_date,
ai.invoice_type_lookup_code trx_type,
--alc.displayed_field
alc.payment_method_name payment_method,
DECODE(NVL(aid.posted_flag,'N'),'Y','ACCOUNTED','UNACCOUNTED'),
aid.invoice_distribution_id,
ail.line_number,
aid.line_type_lookup_code line_type_lookup_code,
DECODE(aid.line_type_lookup_code, 'REC_TAX',0
, 'NONREC_TAX',0
, Decode(ai.invoice_currency_code,g_currency_code,aid.amount, aid.base_amount)) assessable_amt,
DECODE(aid.line_type_lookup_code,'REC_TAX',Decode(ai.invoice_currency_code,g_currency_code,aid.Amount, aid.base_amount)
,'NONREC_TAX',Decode(ai.invoice_currency_code,g_currency_code,aid.amount, aid.base_amount)
,0) vat_amt,
NVL(ail.global_attribute11,g_default_payment_mode) payment_mode,
NVL(ail.global_attribute9,'N') below_threshold_flag,
NVL(ail.global_attribute10,'N') report_exclusion_flag,
ail.global_attribute8 contract_ident,
NVL(ail.global_attribute6,'N') adj_inv_flag,
decode(ai.invoice_type_lookup_code,'CREDIT',NVL(aid.parent_invoice_id,ail.global_attribute7),
'DEBIT',aid.parent_invoice_id,ail.global_attribute7)ORIG_TRX_ID,
DECODE(pvs.country,'IT',null,hp.party_type) party_type,
DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_last_name,1,24),null)) indv_last_name,
DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_first_name,1,20),null)) indv_first_name,
DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'PERSON',to_date(pv.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),null)) INDV_PARTY_DOB,
DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(pv.global_attribute3,1,40),null)) INDV_PARTY_CITY,
DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'PERSON',pv.global_attribute4,null)) INDV_PARTY_PROVINCE,
DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(pvs.city,1,40),null)) company_city,
DECODE(pvs.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION', substr(pvs.address_line1||' '||pvs.address_line2||' '||pvs.address_line3,1,40),null)) company_address,
is_cm_dm_line_consistent(200,DECODE(ai.invoice_type_lookup_code,'CREDIT',NVL(aid.parent_invoice_id,ail.global_attribute7),
'DEBIT',aid.parent_invoice_id,
ail.global_attribute7),
aid.line_type_lookup_code,ail.global_attribute9, ail.global_attribute10,
ail.global_attribute8,ail.global_attribute11) INCONST_CM_DM_APPL_FLAG,
g_last_update_date,
g_last_updated_by,
g_last_update_login,
g_creation_date,
g_created_by
FROM ap_invoices_All ai,
ap_invoice_distributions_all aid,
ap_invoice_lines_all ail,
po_vendors pv,
po_vendor_sites_all pvs,
iby_payment_methods_vl alc, -- ap_lookup_codes alc,
(SELECT distinct person_id ,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf,
je_it_setup_doc_seqs_all ds,
jg_zz_vat_rep_entities repent,
hz_parties hp,
ap_invoice_distributions_all itemdist,
ap_invoice_lines_all itemline
WHERE repent.vat_reporting_entity_id = g_vat_reporting_entity_id
AND ( ( repent.entity_type_code = 'LEGAL'
AND ai.legal_entity_id = repent.legal_entity_id ))
AND ai.invoice_id = aid.invoice_id
AND ai.invoice_id = ail.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ai.vendor_id = pv.vendor_id
AND pv.vendor_id = pvs.vendor_id
AND ai.vendor_site_id = pvs.vendor_site_id
/* --Changed for performance
AND repent.tax_regime_code = (select zx.tax_regime_code
From ap_invoice_distributions_all aida1,
zx_rates_b zx
Where aida1.invoice_id = Ai.invoice_id
And ( aida1.charge_applicable_to_dist_id= aid.invoice_distribution_id or
aida1.invoice_distribution_id = aid.invoice_distribution_id )
And zx.Tax_Rate_Id = aida1.tax_code_id
and rownum = 1)
*/
AND
(
repent.tax_regime_code =
(SELECT zx.tax_regime_code
FROM ap_invoice_distributions_all aida1a,
zx_rates_b zx
WHERE aida1a.invoice_id = ai.invoice_id
AND aida1a.charge_applicable_to_dist_id = aid.invoice_distribution_id
AND zx.tax_rate_id = aida1a.tax_code_id
AND rownum = 1)
OR
repent.tax_regime_code =
(SELECT zx.tax_regime_code
FROM ap_invoice_distributions_all aida1b,
zx_rates_b zx
WHERE aida1b.invoice_id = ai.invoice_id
AND aida1b.invoice_distribution_id = aid.invoice_distribution_id
AND zx.tax_rate_id = aida1b.tax_code_id
AND rownum = 1)
OR
repent.tax_regime_code =
(SELECT zx.tax_regime_code
FROM ap_invoice_distributions_all aida1c,
zx_rates_b zx
WHERE aida1c.invoice_id = ai.invoice_id
AND aida1c.charge_applicable_to_dist_id = aid.related_id
AND zx.tax_rate_id = aida1c.tax_code_id
AND rownum = 1)
)
AND aid.line_type_lookup_code <> 'AWT'
AND NOT EXISTS (SELECT 1
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = ai.invoice_id
AND NVL(aid1.match_status_flag,'N') <>'A')
AND (aid.tax_code_id IS not NULL or exists (select 1
from ap_invoice_distributions_all aida
WHERE aida.invoice_id = ai.invoice_id
AND aida.charge_applicable_to_dist_id= aid.invoice_distribution_id)
or exists (select 1
from ap_invoice_distributions_all aida
WHERE aida.invoice_id = ai.invoice_id
AND aida.charge_applicable_to_dist_id= aid.related_id))
AND (pv.vat_registration_num IS NOT NULL OR pvs.vat_registration_num IS NOT NULL) -- only B2B invoices for payables.
--AND ai.payment_method_code = alc.lookup_code
--AND alc.lookup_type = 'PAYMENT METHOD'
AND ai.payment_method_code = alc.payment_method_code
AND (pvs.country ='IT' OR pvs.country IN ( select territory_code from FND_Territories where alternate_territory_code IS NOT NULL))
-- we need to pick only IT countries and EU countries transactions.
AND NVL(pv.employee_id, -99) = papf.person_id (+)
AND hp.party_id =pv.party_id
AND itemline.invoice_id = ai.invoice_id
AND itemdist.invoice_distribution_id (+) = aid.charge_applicable_to_dist_id
AND itemline.line_number = NVL(itemdist.invoice_line_number,aid.invoice_line_number)
AND (TRUNC(aid.accounting_date) BETWEEN g_start_date AND g_end_date
OR ((ai.invoice_type_lookup_code in ('CREDIT','DEBIT') OR NVL(ail.global_attribute6,'N')='Y' OR
(NVL(itemline.global_attribute6,'N')='Y' AND aid.line_type_lookup_code in('REC_TAX','NONREC_TAX') ))
AND TRUNC(aid.accounting_date) BETWEEN g_start_date AND g_end_date_for_cm_dm))
AND ai.doc_sequence_id = ds.document_sequence_id
AND ds.vat_reporting_entity_id = g_vat_reporting_entity_id
AND ds.year_of_declaration = g_year
AND ds.application_id = 200
AND ((g_inv_acctg_status = 'ACCOUNTED' AND nvl(aid.posted_flag,'N') = 'Y') --TRX_ACCOUNTING_STATUS check
OR (g_inv_acctg_status = 'UNACCOUNTED' AND nvl(aid.posted_flag,'N') = 'N')
OR g_inv_acctg_status = 'BOTH')
AND NOT EXISTS (SELECT 1 -- Black listed countries
FROM je_it_country_rep_codes cc
WHERE pvs.country = cc.country_code
AND aid.accounting_date BETWEEN NVL(cc.rep_exclusion_date_from,sysdate)
AND NVL(cc.rep_exclusion_date_to,sysdate));
debug_message('Number of records inserted :'||SQL%ROWCOUNT);
UPDATE je_it_trx_lines_all trx_lines
SET (payment_mode,
below_threshold_flag,
report_exclusion_flag,
contract_identification,
adj_inv_flag,
orig_trx_id,
inconst_cm_dm_appl_flag) = (SELECT
payment_mode,
below_threshold_flag,
report_exclusion_flag,
contract_identification,
adj_inv_flag,
orig_trx_id,
inconst_cm_dm_appl_flag
FROM je_it_trx_lines_all item_lines
WHERE item_lines.trx_id = trx_lines.trx_id
AND item_lines.REQUEST_ID = g_conc_request_id
AND item_lines.trx_line_id = (SELECT adl.charge_applicable_to_dist_id FROM
ap_invoice_distributions_all adl
WHERE adl.invoice_distribution_id =trx_lines.trx_line_id
AND adl.invoice_id =item_lines.trx_id)
)
WHERE trx_lines.application_id = 200
and trx_lines.trx_line_type like '%TAX'
AND trx_lines.REQUEST_ID = g_conc_request_id;
UPDATE je_it_trx_lines_all trx_lines
SET (payment_mode,
below_threshold_flag,
report_exclusion_flag,
contract_identification,
adj_inv_flag,
orig_trx_id,
inconst_cm_dm_appl_flag)
=
(SELECT payment_mode,
below_threshold_flag,
report_exclusion_flag,
contract_identification,
adj_inv_flag,
orig_trx_id,
inconst_cm_dm_appl_flag
FROM je_it_trx_lines_all item_lines,
ap_invoice_distributions_all adl
WHERE item_lines.trx_id = trx_lines.trx_id
AND item_lines.request_id = g_conc_request_id
AND item_lines.trx_line_id = adl.charge_applicable_to_dist_id
AND adl.invoice_distribution_id = trx_lines.trx_line_id
AND adl.invoice_id = item_lines.trx_id)
WHERE trx_lines.application_id = 200
and trx_lines.trx_line_type like '%TAX'
AND trx_lines.REQUEST_ID = g_conc_request_id;
UPDATE je_it_trx_lines_all
SET record_type = DECODE(country,'IT',2,3)
WHERE application_id = 200
AND request_id = g_conc_request_id
AND trx_type <> 'CREDIT'
AND trx_type <> 'DEBIT'
AND adj_inv_flag = 'N';
UPDATE je_it_trx_lines_all a
SET record_type = DECODE(country,'IT',2,3)
WHERE application_id = 200
AND request_id = g_conc_request_id
AND (a.trx_type = 'CREDIT' OR a.trx_type = 'DEBIT' OR a.adj_inv_flag = 'Y')
AND a.orig_trx_id IS NOT NULL
AND exists (SELECT 1
FROM je_it_trx_lines_all b
WHERE b.request_id = g_conc_request_id
AND b.application_id = 200
AND b.trx_id = a.orig_trx_id);
UPDATE je_it_trx_lines_all a
SET a.record_type = DECODE(a.country,'IT',4,5)
WHERE a.application_id = 200
AND a.request_id = g_conc_request_id
AND (a.trx_type = 'CREDIT' OR a.trx_type = 'DEBIT' OR a.adj_inv_flag = 'Y')
AND (a.orig_trx_id IS NULL
OR EXISTS (SELECT 1
FROM je_it_trx_lines_all b,
je_it_above_thr_hdr_all h
WHERE h.year_of_declaration in (g_year-1,g_year-2)
AND h.request_id = b.request_id
AND b.application_id = 200
AND b.trx_id = a.orig_trx_id
AND b.is_above_threshold = 'Y'));
debug_message('Error while inserting data in extract_ap_trx_data -'||SQLERRM);
SELECT count(*)
INTO l_count
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = p_parent_trx_id
And ail.line_number In ( select aid1.invoice_line_number from ap_invoice_distributions_all aid1,ap_invoice_distributions_all aid2
Where aid1.invoice_id = p_parent_trx_id
and aid2.invoice_id = p_parent_trx_id
and aid2.charge_applicable_to_dist_id=aid1.invoice_distribution_id)
--AND aid.line_type_lookup_code = p_line_type
AND ail.line_type_lookup_code <> 'TAX'
AND NVL(ail.global_attribute9,'N') = NVL(p_below_threshold,'N')
AND NVL(ail.global_attribute10,'N') = NVL(p_report_exclusion,'N')
AND NVL(ail.global_attribute8,'ZZZ') = NVL(p_contract_ident,'ZZZ')
AND NVL(ail.global_attribute11,g_default_payment_mode) = NVL(p_payment_mode,g_default_payment_mode);
SELECT count(*)
INTO l_count
FROM ra_customer_trx_lines_all rcl
WHERE rcl.customer_trx_id = p_parent_trx_id
AND rcl.line_type = p_line_type
AND exists (select 1 from ra_customer_trx_lines_all rc2
where rc2.link_to_cust_trx_line_id = rcl.customer_trx_line_id)
AND rcl.line_type <> 'TAX'
AND NVL(rcl.global_attribute9,'N') = NVL(p_below_threshold,'N')
AND NVL(rcl.global_attribute10,'N') = NVL(p_report_exclusion,'N')
AND NVL(rcl.global_attribute8,'ZZZ') = NVL(p_contract_ident,'ZZZ')
AND NVL(rcl.global_attribute11,g_default_payment_mode) = NVL(p_payment_mode,g_default_payment_mode);
SELECT count(*)
INTO l_count
FROM ar_receivable_applications_all
WHERE customer_trx_id = p_cust_trx_id;
SELECT applied_customer_trx_id
INTO l_orig_trx_id
FROM ar_receivable_applications_all
WHERE customer_trx_id = p_cust_trx_id;
cursor c_update_gdf(p_request_id NUMBER)
is select trx_id,trx_line_id from
je_it_trx_lines_all
where application_id = 222
and trx_line_type = 'TAX'
AND REQUEST_ID = p_request_id;
INSERT INTO je_it_trx_lines_all (request_id,
vat_reporting_entity_id,
org_id,
application_id,
party_id,
party_name,
vat_registration_num,
tax_payer_id,
country,
trx_id,
trx_num,
trx_date,
trx_type,
payment_method,
trx_accounting_status,
trx_line_id,
trx_line_num,
trx_line_type,
assessable_amt,
vat_amt,
payment_mode,
below_threshold_flag,
report_exclusion_flag,
contract_identification,
adj_inv_flag,
orig_trx_id,
party_type,
indv_party_last_name,
indv_party_first_name,
indv_party_dob,
indv_party_city,
indv_party_province,
company_city,
company_address,
inconst_cm_dm_appl_flag,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
SELECT g_conc_request_id,
g_vat_reporting_entity_id,
g_org_id,
222,
hca.cust_account_id,
substr(hp.party_name,1,60),
substr(NVL(hcsu.tax_reference,hp.tax_reference),1,11) vat_registration_num,
-- NVL(hcsu.tax_reference,hp.tax_reference) vat_registration_num, --bug 13897492
hp.jgzz_fiscal_code taxpayer_id,
hl.country,
rct.customer_trx_id,
rct.trx_number,
rct.trx_date,
rctt.type,
arm.name,
DECODE(nvl(rctd.posting_control_id,-3),-3,'UNACCOUNTED','ACCOUNTED') accounting_status,
rctl.customer_trx_line_id,
rctl.line_number,
rctl.line_type,
DECODE(rctl.line_type,'TAX',0,sum(rctd.amount * nvl(rct.exchange_rate,1))) assessable_amt,
DECODE(rctl.line_type,'TAX',sum(rctd.amount * nvl(rct.exchange_rate,1)),0) vat_amt,
nvl(rctl.global_attribute11,g_default_payment_mode) payment_mode,
nvl(rctl.global_attribute9,'N') below_thre_flag,
nvl(rctl.global_attribute10,'N') report_excl_flag,
rctl.global_attribute8 contact_ident,
NVL(rctl.global_attribute6,'N') adj_inv_flag,
DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7) ORIG_TRX_ID,
--DECODE(rctt.type,'CM',rctl.previous_customer_trx_id,rctl.global_attribute7) ORIG_TRX_ID,
DECODE(hl.country,'IT',null,hp.party_type) party_type,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_last_name,1,24),null)) ind_party_last_name,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_first_name,1,20),null)) ind_party_first_name,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',to_date(hca.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),null)) ind_party_dob,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hca.global_attribute3,1,40),null)) ind_party_city,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',hca.global_attribute5,null)) ind_party_province,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.city,1,40),null)) company_city,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.address1||hl.address2||hl.address3,1,40),null)) company_address,
is_cm_dm_line_consistent(222,DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),rctl.line_type,
rctl.global_attribute9,rctl.global_attribute10,rctl.global_attribute8,rctl.global_attribute11) inconsistent_app,
g_last_update_date,
g_last_updated_by,
g_last_update_login,
g_creation_date,
g_created_by
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctd,
ra_cust_trx_types_all rctt,
ar_receipt_methods arm,
hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_locations hl,
je_it_setup_doc_seqs_all ds,
jg_zz_vat_rep_entities repent,
zx_lines_v zx,
ra_customer_trx_lines_all itemgdf
where --rct.customer_trx_id = 665616 AND
zx.trx_id = rct.customer_trx_id
and zx.trx_line_id = rctl.customer_trx_line_id
and zx.tax_regime_code = repent.tax_regime_code
and repent.vat_reporting_entity_id = g_vat_reporting_entity_id AND
( ( repent.entity_type_code = 'LEGAL'
AND rctt.legal_entity_id = repent.legal_entity_id ))
and rct.customer_trx_id = rctl.customer_trx_id
AND rctl.customer_trx_id = rctd.customer_trx_id
AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
AND NVL(rct.complete_flag,'N') = 'Y'
AND itemgdf.customer_trx_line_id (+) = rctl.link_to_cust_trx_line_id
AND (TRUNC(rctd.gl_date) BETWEEN NVL2(NVL(hcsu.tax_reference,hp.tax_reference),g_start_date,g_b2c_trx_date) AND g_end_date
OR ((rctt.type = 'CM' OR nvl(rctl.global_attribute6,'N')='Y' OR (rctl.line_type='TAX' and nvl(itemgdf.global_attribute6,'N')='Y' ))
AND trunc(rctd.gl_date) BETWEEN NVL2(NVL(hcsu.tax_reference,hp.tax_reference),g_start_date,g_b2c_trx_date) AND g_end_date_for_cm_dm))
AND rct.receipt_method_id = arm.receipt_method_id(+)
AND rct.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rct.bill_to_site_use_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND (NVL(hcsu.tax_reference,hp.tax_reference) IS NULL OR hl.country ='IT' OR hl.country IN (select territory_code from FND_Territories where alternate_territory_code IS NOT NULL))
AND ( rctl.vat_tax_id IS not NULL or exists (select 1 from
ra_customer_trx_lines_all rctla
where rctla.customer_trx_id = rct.customer_trx_id
and rctla.link_to_cust_trx_line_id= rctl.customer_trx_line_id))
AND rct.doc_sequence_id = ds.document_sequence_id
AND ds.vat_reporting_entity_id = g_vat_reporting_entity_id
AND ds.year_of_declaration = g_year
AND ds.application_id = 222
AND ((g_inv_acctg_status = 'ACCOUNTED' AND nvl(rctd.posting_control_id,-3) <> -3) --TRX_ACCOUNTING_STATUS check
OR (g_inv_acctg_status = 'UNACCOUNTED' AND nvl(rctd.posting_control_id,-3) = -3)
OR g_inv_acctg_status = 'BOTH')
AND NOT EXISTS (SELECT 1 -- Black listed countries
FROM je_it_country_rep_codes cc
WHERE hl.country = cc.country_code
AND rctd.gl_date BETWEEN NVL(cc.rep_exclusion_date_from,sysdate)
AND NVL(cc.rep_exclusion_date_to,sysdate))
group by 222,
hca.cust_account_id,
substr(hp.party_name,1,60),
substr(NVL(hcsu.tax_reference,hp.tax_reference),1,11),
--NVL(hcsu.tax_reference,hp.tax_reference),
hp.jgzz_fiscal_code,
hl.country,
rct.customer_trx_id,
rct.trx_number,
rct.trx_date,
rctt.type,
arm.name,
DECODE(nvl(rctd.posting_control_id,-3),-3,'UNACCOUNTED','ACCOUNTED'),
rctl.customer_trx_line_id,
rctl.line_number,
rctl.line_type,
nvl(rctl.global_attribute11,g_default_payment_mode),
nvl(rctl.global_attribute9,'N'),
nvl(rctl.global_attribute10,'N'),
rctl.global_attribute8,
NVL(rctl.global_attribute6,'N'),
-- DECODE(rctt.type,'CM',rctl.previous_customer_trx_id,rctl.global_attribute7),
DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),
DECODE(hl.country,'IT',null,hp.party_type),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_last_name,1,24),null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_first_name,1,20),null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',to_date(hca.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hca.global_attribute3,1,40),null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',hca.global_attribute5,null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.city,1,40),null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.address1||hl.address2||hl.address3,1,40),null)),
is_cm_dm_line_consistent(222,DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),rctl.line_type,
rctl.global_attribute9,rctl.global_attribute10,rctl.global_attribute8,rctl.global_attribute11)
UNION
SELECT g_conc_request_id,
g_vat_reporting_entity_id,
g_org_id,
222,
hca.cust_account_id,
substr(hp.party_name,1,60),
substr(NVL(hcsu.tax_reference,hp.tax_reference),1,11) vat_registration_num,
-- NVL(hcsu.tax_reference,hp.tax_reference) vat_registration_num, --bug 13897492
hp.jgzz_fiscal_code taxpayer_id,
hl.country,
rct.customer_trx_id,
rct.trx_number,
rct.trx_date,
rctt.type,
arm.name,
DECODE(nvl(rctd.posting_control_id,-3),-3,'UNACCOUNTED','ACCOUNTED') accounting_status,
rctl.customer_trx_line_id,
rctl.line_number,
rctl.line_type,
DECODE(rctl.line_type,'TAX',0,sum(rctd.amount * nvl(rct.exchange_rate,1))) assessable_amt,
DECODE(rctl.line_type,'TAX',sum(rctd.amount * nvl(rct.exchange_rate,1)),0) vat_amt,
nvl(rctl.global_attribute11,g_default_payment_mode) payment_mode,
nvl(rctl.global_attribute9,'N') below_thre_flag,
nvl(rctl.global_attribute10,'N') report_excl_flag,
rctl.global_attribute8 contact_ident,
NVL(rctl.global_attribute6,'N') adj_inv_flag,
DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7) ORIG_TRX_ID,
--DECODE(rctt.type,'CM',rctl.previous_customer_trx_id,rctl.global_attribute7) ORIG_TRX_ID,
DECODE(hl.country,'IT',null,hp.party_type) party_type,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_last_name,1,24),null)) ind_party_last_name,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_first_name,1,20),null)) ind_party_first_name,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',to_date(hca.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),null)) ind_party_dob,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hca.global_attribute3,1,40),null)) ind_party_city,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',hca.global_attribute5,null)) ind_party_province,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.city,1,40),null)) company_city,
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.address1||hl.address2||hl.address3,1,40),null)) company_address,
is_cm_dm_line_consistent(222,DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),rctl.line_type,
rctl.global_attribute9,rctl.global_attribute10,rctl.global_attribute8,rctl.global_attribute11) inconsistent_app,
g_last_update_date,
g_last_updated_by,
g_last_update_login,
g_creation_date,
g_created_by
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctd,
ra_cust_trx_types_all rctt,
ar_receipt_methods arm,
hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_locations hl,
je_it_setup_doc_seqs_all ds,
jg_zz_vat_rep_entities repent,
zx_lines_v zx,
ra_customer_trx_lines_all itemgdf
where --rct.customer_trx_id = 665616 AND
zx.trx_id = rct.customer_trx_id
and zx.tax_line_id=rctl.tax_line_id
and zx.tax_regime_code = repent.tax_regime_code
and repent.vat_reporting_entity_id = g_vat_reporting_entity_id AND
( ( repent.entity_type_code = 'LEGAL'
AND rctt.legal_entity_id = repent.legal_entity_id ))
and rct.customer_trx_id = rctl.customer_trx_id
AND rctl.customer_trx_id = rctd.customer_trx_id
AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
AND NVL(rct.complete_flag,'N') = 'Y'
AND itemgdf.customer_trx_line_id (+) = rctl.link_to_cust_trx_line_id
AND (TRUNC(rctd.gl_date) BETWEEN NVL2(NVL(hcsu.tax_reference,hp.tax_reference),g_start_date,g_b2c_trx_date) AND g_end_date
OR ((rctt.type = 'CM' OR nvl(rctl.global_attribute6,'N')='Y' OR (rctl.line_type='TAX' and nvl(itemgdf.global_attribute6,'N')='Y' ))
AND trunc(rctd.gl_date) BETWEEN NVL2(NVL(hcsu.tax_reference,hp.tax_reference),g_start_date,g_b2c_trx_date) AND g_end_date_for_cm_dm))
AND rct.receipt_method_id = arm.receipt_method_id(+)
AND rct.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rct.bill_to_site_use_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND (NVL(hcsu.tax_reference,hp.tax_reference) IS NULL OR hl.country ='IT' OR hl.country IN (select territory_code from FND_Territories where alternate_territory_code IS NOT NULL))
AND ( rctl.vat_tax_id IS not NULL or exists (select 1 from
ra_customer_trx_lines_all rctla
where rctla.customer_trx_id = rct.customer_trx_id
and rctla.link_to_cust_trx_line_id= rctl.customer_trx_line_id))
AND rct.doc_sequence_id = ds.document_sequence_id
AND ds.vat_reporting_entity_id = g_vat_reporting_entity_id
AND ds.year_of_declaration = g_year
AND ds.application_id = 222
AND ((g_inv_acctg_status = 'ACCOUNTED' AND nvl(rctd.posting_control_id,-3) <> -3) --TRX_ACCOUNTING_STATUS check
OR (g_inv_acctg_status = 'UNACCOUNTED' AND nvl(rctd.posting_control_id,-3) = -3)
OR g_inv_acctg_status = 'BOTH')
AND NOT EXISTS (SELECT 1 -- Black listed countries
FROM je_it_country_rep_codes cc
WHERE hl.country = cc.country_code
AND rctd.gl_date BETWEEN NVL(cc.rep_exclusion_date_from,sysdate)
AND NVL(cc.rep_exclusion_date_to,sysdate))
group by 222,
hca.cust_account_id,
substr(hp.party_name,1,60),
substr(NVL(hcsu.tax_reference,hp.tax_reference),1,11),
--NVL(hcsu.tax_reference,hp.tax_reference),
hp.jgzz_fiscal_code,
hl.country,
rct.customer_trx_id,
rct.trx_number,
rct.trx_date,
rctt.type,
arm.name,
DECODE(nvl(rctd.posting_control_id,-3),-3,'UNACCOUNTED','ACCOUNTED'),
rctl.customer_trx_line_id,
rctl.line_number,
rctl.line_type,
nvl(rctl.global_attribute11,g_default_payment_mode),
nvl(rctl.global_attribute9,'N'),
nvl(rctl.global_attribute10,'N'),
rctl.global_attribute8,
NVL(rctl.global_attribute6,'N'),
-- DECODE(rctt.type,'CM',rctl.previous_customer_trx_id,rctl.global_attribute7),
DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),
DECODE(hl.country,'IT',null,hp.party_type),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_last_name,1,24),null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hp.person_first_name,1,20),null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',to_date(hca.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',substr(hca.global_attribute3,1,40),null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'PERSON',hca.global_attribute5,null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.city,1,40),null)),
DECODE(hl.country,'IT',null,DECODE(hp.party_type,'ORGANIZATION',substr(hl.address1||hl.address2||hl.address3,1,40),null)),
is_cm_dm_line_consistent(222,DECODE(rctt.type,'CM',get_orig_trx_id(rct.customer_trx_id,rctl.previous_customer_trx_id),rctl.global_attribute7),rctl.line_type,
rctl.global_attribute9,rctl.global_attribute10,rctl.global_attribute8,rctl.global_attribute11);
debug_message('Number of records inserted :'||SQL%ROWCOUNT);
debug_message('After insert in Extract_AR_Trx_data');
UPDATE je_it_trx_lines_all trx_lines
SET (payment_mode,
below_threshold_flag,
report_exclusion_flag,
contract_identification,
adj_inv_flag,
orig_trx_id,
inconst_cm_dm_appl_flag) = (SELECT
payment_mode,
below_threshold_flag,
report_exclusion_flag,
contract_identification,
adj_inv_flag,
orig_trx_id,
inconst_cm_dm_appl_flag
FROM je_it_trx_lines_all item_lines
WHERE item_lines.trx_id = trx_lines.trx_id
AND item_lines.REQUEST_ID = g_conc_request_id
AND item_lines.trx_line_id = (SELECT rctl.link_to_cust_trx_line_id FROM
ra_customer_trx_lines_all rctl
WHERE rctl.customer_trx_line_id =trx_lines.trx_line_id
AND rctl.customer_trx_id =item_lines.trx_id)
)
WHERE trx_lines.application_id = 222
and trx_lines.trx_line_type = 'TAX'
AND trx_lines.REQUEST_ID = g_conc_request_id;
UPDATE je_it_trx_lines_all trx_lines
SET (payment_mode,
below_threshold_flag,
report_exclusion_flag,
contract_identification,
adj_inv_flag,
orig_trx_id,
inconst_cm_dm_appl_flag)
=
(SELECT
payment_mode,
below_threshold_flag,
report_exclusion_flag,
contract_identification,
adj_inv_flag,
orig_trx_id,
inconst_cm_dm_appl_flag
FROM je_it_trx_lines_all item_lines
,ra_customer_trx_lines_all rctl
WHERE item_lines.trx_id = trx_lines.trx_id
AND item_lines.REQUEST_ID = g_conc_request_id
AND item_lines.trx_line_id = rctl.link_to_cust_trx_line_id
AND rctl.customer_trx_line_id =trx_lines.trx_line_id
AND rctl.customer_trx_id =item_lines.trx_id)
WHERE trx_lines.application_id = 222
and trx_lines.trx_line_type = 'TAX'
AND trx_lines.REQUEST_ID = g_conc_request_id;
UPDATE je_it_trx_lines_all trx_lines
SET trx_lines.trx_gl_date = (SELECT gl_date
FROM ra_cust_trx_line_gl_dist_all dist
WHERE dist.customer_trx_id = trx_lines.trx_id
AND dist.account_class = 'REC'
AND dist.latest_rec_flag = 'Y')
WHERE trx_lines.application_id = 222
AND trx_lines.REQUEST_ID = g_conc_request_id;
debug_message('After update1 in Extract_AR_Trx_data');
UPDATE je_it_trx_lines_all
SET record_type = DECODE(country,'IT',NVL2(vat_registration_num,2,1),3)
WHERE application_id = 222
AND request_id = g_conc_request_id
AND trx_type <> 'CM'
AND trx_type <> 'DM'
AND adj_inv_flag = 'N';
UPDATE je_it_trx_lines_all a
SET record_type = DECODE(country,'IT',NVL2(vat_registration_num,2,1),3)
WHERE application_id = 222
AND request_id = g_conc_request_id
AND (a.trx_type = 'CM' OR a.trx_type = 'DM' OR a.adj_inv_flag = 'Y')
AND a.orig_trx_id IS NOT NULL
AND exists (SELECT 1
FROM je_it_trx_lines_all b
WHERE b.request_id = g_conc_request_id
AND b.application_id = 222
AND b.trx_id = a.orig_trx_id);
UPDATE je_it_trx_lines_all a
SET a.record_type = DECODE(a.country,'IT',4,5)
WHERE a.application_id = 222
AND a.request_id = g_conc_request_id
AND (a.trx_type = 'CM' OR a.trx_type = 'DM' OR a.adj_inv_flag = 'Y')
AND (a.orig_trx_id IS NULL
OR EXISTS (SELECT 1
FROM je_it_trx_lines_all b,
je_it_above_thr_hdr_all h
WHERE h.year_of_declaration in (g_year-1,g_year-2)
AND h.request_id = b.request_id
AND b.application_id = 222
AND b.trx_id = a.orig_trx_id
AND b.is_above_threshold = 'Y'));
debug_message('After record type update in Extract_AR_Trx_data');
UPDATE je_it_trx_lines_all
SET PAYMENT_MODE_ERR_FLAG = 'Y'
WHERE (application_id,trx_id) IN (SELECT application_id,trx_id
FROM je_it_trx_lines_all
WHERE request_id = g_conc_request_id
AND trx_line_type not in ('REC_TAX','NONREC_TAX','TAX') -- Tax line GDF are not considered in R12
GROUP BY application_id,trx_id
HAVING COUNT(DISTINCT payment_mode) > 1)
AND request_id = g_conc_request_id;
UPDATE je_it_trx_lines_all a
SET inconst_cm_dm_appl_flag = 'Y'
WHERE request_id = g_conc_request_id
AND exists (SELECT 1
FROM je_it_trx_lines_all b
WHERE b.request_id = g_conc_request_id
AND b.trx_id = a.orig_trx_id
AND b.payment_mode_err_flag = 'Y');
UPDATE je_it_trx_lines_all
SET PAYMENT_MODE_ERR_FLAG = 'Y'
WHERE (application_id,party_id,contract_identification) IN (SELECT application_id,party_id,contract_identification
FROM je_it_trx_lines_all
WHERE request_id = g_conc_request_id
AND trx_line_type not in ('REC_TAX','NONREC_TAX','TAX') -- Tax line GDF are not considered in R12
GROUP BY application_id,party_id,contract_identification
HAVING COUNT(DISTINCT payment_mode) > 1)
AND request_id = g_conc_request_id;
UPDATE je_it_trx_lines_all
SET partial_adj_inv_flag = 'Y'
WHERE (application_id,trx_id) IN (SELECT application_id,trx_id
FROM je_it_trx_lines_all
WHERE request_id = g_conc_request_id
AND trx_line_type not in ('REC_TAX','NONREC_TAX','TAX') -- Tax line GDF are not considered in R12
GROUP BY application_id,trx_id
HAVING COUNT(DISTINCT adj_inv_flag) > 1)
AND request_id = g_conc_request_id;
UPDATE je_it_trx_lines_all
SET ORIG_TRX_MISSING_FLAG = 'Y'
WHERE (application_id,ORIG_TRX_ID) not in (SELECT application_id,trx_id
FROM je_it_trx_lines_all
WHERE request_id = g_conc_request_id
)
AND (application_id,ORIG_TRX_ID) not in (SELECT application_id,trx_id
FROM je_it_trx_lines_all trx_lines,
je_it_above_thr_hdr_all hdr
WHERE trx_lines.request_id = hdr.request_id
AND hdr.year_of_declaration in (g_year-1,g_year-2)
AND is_above_threshold = 'Y')
AND request_id = g_conc_request_id;
UPDATE je_it_trx_lines_all
SET orig_trx_missing_flag = 'Y'
WHERE rowid NOT IN
(SELECT t2.rowid
FROM je_it_trx_lines_all t1,
je_it_trx_lines_all t2
WHERE t1.request_id = g_conc_request_id
AND t2.request_id = g_conc_request_id
AND t2.application_id = t1.application_id
AND t2.orig_trx_id = t1.trx_id
AND t2.orig_trx_id IS NOT NULL
UNION
SELECT t2.rowid
FROM je_it_trx_lines_all t2,
je_it_trx_lines_all trx_lines,
je_it_above_thr_hdr_all hdr
WHERE trx_lines.request_id = hdr.request_id
AND hdr.year_of_declaration IN(g_year -1,g_year-2)
AND trx_lines.is_above_threshold = 'Y'
AND t2.request_id = g_conc_request_id
AND t2.application_id = trx_lines.application_id
AND t2.orig_trx_id = trx_lines.trx_id
AND t2.orig_trx_id IS NOT NULL)
AND orig_trx_id IS NOT NULL;
PROCEDURE Update_Above_Threshold_Lines IS
l_api_name VARCHAR2(50) := 'Update_above_threshold_lines';
debug_message('Start of Update_Above_Threshold_Lines');
UPDATE je_it_trx_lines_all a -- For normal grouping
SET a.is_above_threshold = 'Y'
WHERE a.request_id = g_conc_request_id
AND (a.application_id,NVL(a.ORIG_TRX_ID,a.trx_id)) IN (SELECT b.application_id,
NVL(b.ORIG_TRX_ID,b.trx_id)
FROM je_it_trx_lines_all b
WHERE b.request_id = g_conc_request_id
AND b.below_threshold_flag = 'N'
AND NVL(b.report_exclusion_flag,'N') = 'N'
AND NVL(b.inconst_cm_dm_appl_flag,'N') = 'N'
AND NVL(b.payment_mode_err_flag,'N') = 'N'
AND NVL(b.orig_trx_missing_flag,'N') = 'N'
AND NVL(b.partial_adj_inv_flag,'N') = 'N'
AND b.trx_id in (select c.trx_id
from je_it_trx_lines_all c
where c.request_id = g_conc_request_id
and c.contract_identification is null)
AND b.record_type in(1,2,3)
GROUP BY b.application_id,
NVL(b.ORIG_TRX_ID,b.trx_id),
b.vat_registration_num
HAVING ((b.vat_registration_num IS NOT NULL AND SUM(b.assessable_amt) >= g_b2b_threshold)
OR (b.vat_registration_num IS NULL AND SUM(b.assessable_amt + b.vat_amt) >= g_b2c_threshold )))
AND a.below_threshold_flag = 'N'
AND NVL(a.report_exclusion_flag,'N') = 'N'
AND NVL(a.inconst_cm_dm_appl_flag,'N') = 'N'
AND NVL(a.payment_mode_err_flag,'N') = 'N'
AND NVL(a.orig_trx_missing_flag,'N') = 'N'
AND NVL(a.partial_adj_inv_flag,'N') = 'N'
AND a.record_type in (1,2,3);
UPDATE je_it_trx_lines_all a -- FOR Contractor grouping.
SET a.is_above_threshold = 'Y'
WHERE a.request_id = g_conc_request_id
AND (a.application_id,a.party_id,a.contract_identification)
IN (SELECT b.application_id,
b.party_id,
b.contract_identification
FROM je_it_trx_lines_all b
WHERE b.request_id = g_conc_request_id
AND b.below_threshold_flag = 'N'
AND NVL(b.report_exclusion_flag,'N') = 'N'
AND NVL(b.inconst_cm_dm_appl_flag,'N') = 'N'
AND NVL(b.payment_mode_err_flag,'N') = 'N'
AND NVL(b.orig_trx_missing_flag,'N') = 'N'
AND NVL(b.partial_adj_inv_flag,'N') = 'N'
AND b.contract_identification IS NOT NULL
AND b.record_type in (1,2,3)
GROUP BY b.application_id,
b.party_id,
b.contract_identification,
b.vat_registration_num
HAVING ((b.vat_registration_num IS NOT NULL AND SUM(b.assessable_amt) >= g_b2b_threshold)
OR (b.vat_registration_num IS NULL AND SUM(b.assessable_amt + b.vat_amt) >= g_b2c_threshold )))
AND a.below_threshold_flag = 'N'
AND NVL(a.report_exclusion_flag,'N') = 'N'
AND NVL(a.inconst_cm_dm_appl_flag,'N') = 'N'
AND NVL(a.payment_mode_err_flag,'N') = 'N'
AND NVL(a.orig_trx_missing_flag,'N') = 'N'
AND NVL(a.partial_adj_inv_flag,'N') = 'N'
AND a.contract_identification IS NOT NULL
AND a.record_type in (1,2,3);
UPDATE je_it_trx_lines_all trx_lines -- if below_threshold flag is 'Y' then that lines has to be reported without checking against threshold
SET is_above_threshold = 'Y'
WHERE trx_lines.request_id = g_conc_request_id
AND NVL(trx_lines.below_threshold_flag,'N') = 'Y'
AND NVL(trx_lines.report_exclusion_flag,'N') = 'N'
AND NVL(trx_lines.inconst_cm_dm_appl_flag,'N') = 'N'
AND NVL(trx_lines.payment_mode_err_flag,'N') = 'N'
AND NVL(trx_lines.orig_trx_missing_flag,'N') = 'N'
AND NVL(trx_lines.partial_adj_inv_flag,'N') = 'N';
UPDATE je_it_trx_lines_all trx_lines
SET is_above_threshold = 'Y'
WHERE trx_lines.request_id = g_conc_request_id
AND trx_lines.record_type in (4,5)
AND NVL(trx_lines.report_exclusion_flag,'N') = 'N'
AND NVL(trx_lines.inconst_cm_dm_appl_flag,'N') = 'N'
AND NVL(trx_lines.payment_mode_err_flag,'N') = 'N'
AND NVL(trx_lines.orig_trx_missing_flag,'N') = 'N'
AND NVL(trx_lines.partial_adj_inv_flag,'N') = 'N'
AND (trx_lines.orig_trx_id IS NULL
OR NOT EXISTS (SELECT 1 --to filter credit memo lines already reported.
FROM je_it_trx_lines_all a,
je_it_above_thr_hdr_all b
WHERE a.request_id = b.request_id
AND b.year_of_declaration in (g_year-1,g_year-2)
AND a.trx_id = trx_lines.trx_id
AND nvl(a.trx_line_id,0) = nvl(trx_lines.trx_line_id,0)
AND nvl(a.trx_line_num,0) = nvl(trx_lines.trx_line_num,0) ));
debug_message('End of Update_Above_Threshold_Lines');
END Update_Above_Threshold_Lines;
PROCEDURE Insert_Above_Threshold_Lines IS
l_api_name VARCHAR2(50) := 'Insert_Above_Threshold_Lines';
cursor update_seq_no_cur IS
SELECT record_type,
application_id,
party_name,
party_id,
trx_gl_date,
trx_id,
contract_identification,
below_threshold_flag
FROM je_it_trx_above_thr_all
WHERE request_id = g_conc_request_id
ORDER BY record_type,
application_id desc,
party_name,
party_id,
trx_gl_date,
trx_id,
contract_identification,
below_threshold_flag;
debug_message('Start of Insert_Above_Threshold_Lines');
INSERT INTO je_it_trx_above_thr_all
(
request_id ,
vat_reporting_entity_id,
org_id,
application_id,
record_type,
party_id,
party_name,
vat_registration_num,
tax_payer_id,
country,
trx_id,
-- trx_num,
-- trx_date,
-- trx_gl_date,
-- payment_method,
-- trx_accounting_status,
assessable_amt,
vat_amt,
payment_mode,
below_threshold_flag,
contract_identification,
party_type,
indv_party_last_name,
indv_party_first_name,
indv_party_dob,
indv_party_city,
indv_party_province,
company_city,
company_address,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by
)
SELECT
g_conc_request_id ,
g_vat_reporting_entity_id,
g_org_id,
application_id,
record_type,
party_id,
party_name,
vat_registration_num,
tax_payer_id,
country,
trx_id,
-- trx_num,
-- trx_date,
-- trx_gl_date,
-- payment_method,
-- trx_accounting_status,
assessable_amt,
vat_amt,
payment_mode,
below_threshold_flag,
contract_identification,
party_type,
indv_party_last_name,
indv_party_first_name,
indv_party_dob,
indv_party_city,
indv_party_province,
company_city,
company_address,
g_last_update_date,
g_last_updated_by,
g_last_update_login,
g_creation_date,
g_created_by
FROM
(SELECT b.application_id ,
b.record_type ,
b.party_id ,
b.party_name ,
b.country ,
b.vat_registration_num ,
b.tax_payer_id ,
NVL(b.orig_trx_id, b.trx_id) trx_id ,
ROUND(nvl2(b.vat_registration_num, SUM(b.assessable_amt), SUM(b.assessable_amt + b.vat_amt))) assessable_amt,
ROUND(nvl2(b.vat_registration_num, SUM(b.vat_amt), 0)) vat_amt ,
b.payment_mode ,
b.below_threshold_flag ,
NULL contract_identification,
MAX(b.party_type) party_type ,
MAX(b.indv_party_last_name) indv_party_last_name ,
MAX(b.indv_party_first_name) indv_party_first_name ,
MAX(b.indv_party_dob) indv_party_dob ,
MAX(b.indv_party_city) indv_party_city ,
MAX(b.indv_party_province) indv_party_province ,
MAX(b.company_city) company_city ,
MAX(b.company_address) company_address
FROM je_it_trx_lines_all b
WHERE b.request_id = g_conc_request_id
AND NVL(b.is_above_threshold, 'N') = 'Y'
AND b.record_type IN(1, 2, 3)
AND EXISTS
(SELECT
/*+ UNNEST INDEX(a XXFN_JE_IT_TRX_LINES_N101) */
'exists'
FROM je_it_trx_lines_all a
WHERE a.request_id = g_conc_request_id
AND a.trx_id = b.trx_id
AND a.record_type IN(1, 2, 3)
AND a.contract_identification IS NULL
)
GROUP BY b.application_id ,
b.record_type ,
b.party_id ,
b.party_name ,
b.country ,
b.vat_registration_num ,
b.tax_payer_id ,
nvl(b.orig_trx_id, b.trx_id),
b.payment_mode ,
b.below_threshold_flag
UNION ALL
SELECT b.application_id ,
b.record_type ,
b.party_id ,
b.party_name ,
b.country ,
b.vat_registration_num ,
b.tax_payer_id ,
NULL ,
ROUND(nvl2(b.vat_registration_num, SUM(b.assessable_amt), SUM(b.assessable_amt + b.vat_amt))) assessable_amt,
ROUND(nvl2(b.vat_registration_num, SUM(b.vat_amt), 0)) vat_amt ,
b.payment_mode ,
b.below_threshold_flag ,
b.contract_identification ,
MAX(b.party_type) party_type ,
MAX(b.indv_party_last_name) indv_party_last_name ,
MAX(b.indv_party_first_name) indv_party_first_name ,
MAX(b.indv_party_dob) indv_party_dob ,
MAX(b.indv_party_city) indv_party_city ,
MAX(b.indv_party_province) indv_party_province ,
MAX(b.company_city) company_city ,
MAX(b.company_address) company_address
FROM je_it_trx_lines_all b
WHERE b.request_id = g_conc_request_id
AND b.contract_identification IS NOT NULL
AND NVL(b.is_above_threshold, 'N') = 'Y'
AND b.record_type IN(1, 2, 3)
AND NOT EXISTS
(SELECT
/*+ UNNEST INDEX(a XXFN_JE_IT_TRX_LINES_N101) */
'exists'
FROM je_it_trx_lines_all a
WHERE a.request_id = g_conc_request_id
AND a.trx_id = b.trx_id
AND a.record_type IN(1, 2, 3)
AND a.contract_identification IS NULL
)
GROUP BY b.application_id ,
b.record_type ,
b.party_id ,
b.party_name ,
b.country ,
b.vat_registration_num ,
b.tax_payer_id ,
b.contract_identification,
b.payment_mode ,
b.below_threshold_flag
);
debug_message('In between of Insert_Above_Threshold_Lines');
g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while inserting record type 1,2,3 data');
BEGIN -- For Contract Identification, update the trx_gl_date with the latest invoice gl_date
debug_message('Update for trx_gl_date with the latest invoice gl_date');
UPDATE je_it_trx_above_thr_all trx
SET trx_gl_date = (SELECT MAX(trx_gl_date)
FROM je_it_trx_lines_all trx_lines
WHERE request_id = g_conc_request_id
AND trx_lines.contract_identification = trx.contract_identification
AND trx_lines.party_id = trx.party_id
AND trx_lines.application_id = trx.application_id
AND NVL(trx_lines.is_above_threshold,'N')='Y'
AND trx_lines.record_type in (1,2,3))
WHERE request_id = g_conc_request_id
AND trx.contract_identification is not null
AND trx.record_type in (1,2,3);
g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while updating the trx_gl_date for contract grouping');
BEGIN -- For Contract Identification, update the trx_number with the latest invoice number
debug_message('Update the trx_number with the latest invoice number');
UPDATE je_it_trx_above_thr_all trx
SET trx_num = (SELECT MAX(trx_num)
FROM je_it_trx_lines_all trx_lines
WHERE request_id = g_conc_request_id
AND trx_lines.contract_identification = trx.contract_identification
AND trx_lines.party_id = trx.party_id
AND trx_lines.application_id = trx.application_id
AND trx_lines.trx_gl_date = trx.trx_gl_date
AND NVL(trx_lines.is_above_threshold,'N')='Y'
AND trx_lines.record_type in (1,2,3))
WHERE request_id = g_conc_request_id
AND trx.contract_identification is not null
AND trx.record_type in (1,2,3);
g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while updating the trx_number for contract grouping');
debug_message('Update for normal invoice grouping');
UPDATE je_it_trx_above_thr_all a
SET (trx_num,trx_date,trx_gl_date,
payment_method,trx_accounting_status) = (SELECT MAX(trx_num),MAX(trx_date),
MAX(trx_gl_date),MAX(payment_method),
MAX(trx_accounting_status)
FROM je_it_trx_lines_all b
WHERE request_id = g_conc_request_id
AND contract_identification IS NULL
AND b.trx_id = a.trx_id
AND b.application_id=a.application_id )
WHERE a.request_id = g_conc_request_id
AND a.contract_identification IS NULL;
For rec IN update_seq_no_cur
LOOP
UPDATE je_it_trx_above_thr_all
SET eft_progression_num = decode(mod(i,g_max_no_of_records),0,trunc(i/g_max_no_of_records),trunc(i/g_max_no_of_records)+1),
eft_sequential_num =decode(mod(i,g_max_no_of_records),0,g_max_no_of_records,mod(i,g_max_no_of_records))
WHERE request_id = g_conc_request_id
AND record_type = rec.record_type
AND application_id = rec.application_id
AND party_id = rec.party_id
AND party_name = rec.party_name
AND nvl(trx_id,9999999999) = NVL(rec.trx_id,9999999999)
AND trx_gl_date = rec.trx_gl_date
AND nvl(contract_identification,'ZZZZZZ') = NVL(rec.contract_identification,'ZZZZZZ')
AND below_threshold_flag = rec.below_threshold_flag;
BEGIN -- get the number of records inserted into je_it_trx_above_threshold table for record types 1,2,3.
-- Required to derive eft_transmission_number and sequential_number
SELECT MAX(eft_progression_num)
INTO l_prog_cnt
FROM je_it_trx_above_thr_all
WHERE request_id = g_conc_request_id;
SELECT MAX(eft_sequential_num)
INTO l_seq_cnt
FROM je_it_trx_above_thr_all
WHERE request_id = g_conc_request_id
AND eft_progression_num = l_prog_cnt;
g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while calculating l_std_inv_cnt');
INSERT INTO je_it_trx_above_thr_all (request_id,
vat_reporting_entity_id,
org_id,
application_id,
record_type,
eft_progression_num,
eft_sequential_num,
country,
party_id,
party_name,
vat_registration_num,
tax_payer_id,
trx_id,
trx_num,
trx_type,
trx_date,
trx_gl_date,
payment_method,
trx_accounting_status,
assessable_amt,
vat_amt,
payment_mode,
party_type,
indv_party_last_name,
indv_party_first_name,
indv_party_dob,
indv_party_city,
indv_party_province,
company_city,
company_address,
orig_trx_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by)
SELECT g_conc_request_id,
g_vat_reporting_entity_id,
g_org_id,
application_id,
record_type,
decode(mod(l_std_inv_cnt+rownum,g_max_no_of_records),0,trunc((l_std_inv_cnt+ROWNUM)/g_max_no_of_records),trunc((l_std_inv_cnt+ROWNUM)/g_max_no_of_records)+1),
decode(mod(l_std_inv_cnt+ROWNUM,g_max_no_of_records),0,g_max_no_of_records,mod(l_std_inv_cnt+rownum,g_max_no_of_records)),
country,
party_id,
party_name,
vat_registration_num,
tax_payer_id,
trx_id,
trx_num,
trx_type,
trx_date,
trx_gl_date,
payment_method,
trx_accounting_status,
assessable_amt,
vat_amt,
payment_mode,
party_type,
indv_party_last_name,
indv_party_first_name,
indv_party_dob,
indv_party_city,
indv_party_province,
company_city,
company_address,
orig_trx_id,
g_last_update_date,
g_last_updated_by,
g_last_update_login,
g_creation_date,
g_created_by
FROM (SELECT application_id,
record_type,
country,
party_id,
party_name,
vat_registration_num,
tax_payer_id,
trx_id,
trx_num,
trx_type,
MAX(trx_date) trx_date,
MAX(trx_gl_date) trx_gl_date,
MAX(payment_method) payment_method,
MAX(trx_accounting_status) trx_accounting_status,
SUM(assessable_amt) assessable_amt,
SUM(vat_amt) vat_amt,
MAX(payment_mode) payment_mode,
MAX(party_type) party_type,
MAX(indv_party_last_name) indv_party_last_name,
MAX(indv_party_first_name) indv_party_first_name,
MAX(indv_party_dob) indv_party_dob,
MAX(indv_party_city) indv_party_city,
MAX(indv_party_province) indv_party_province,
MAX(company_city) company_city,
MAX(company_address) company_address,
orig_trx_id
FROM je_it_trx_lines_all
WHERE request_id = g_conc_request_id
AND record_type in (4,5)
AND NVL(is_above_threshold,'N') = 'Y'
GROUP BY application_id,
record_type,
country,
party_id,
party_name,
vat_registration_num,
tax_payer_id,
trx_id,
trx_num,
trx_type,
orig_trx_id
ORDER BY application_id desc,
record_type,
party_name,
trx_gl_date,
trx_id,
orig_trx_id);
debug_message('in between-2 of Insert_Above_Threshold_Lines');
g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while inserting record type 4 and 5 data');
UPDATE je_it_trx_above_thr_all trx
SET (orig_trx_num,orig_trx_date,
orig_trx_gl_date,orig_trx_acctg_status,
orig_trx_payment_method) = (SELECT MAX(trx_num),NVL(MAX(trx_date),g_end_date),NVL(MAX(trx_gl_date),g_end_date),
MAX(trx_lines.trx_accounting_status),MAX(payment_method)
FROM je_it_trx_lines_all trx_lines,
je_it_above_thr_hdr_all hdr
WHERE trx_lines.request_id = hdr.request_id
AND year_of_declaration in (g_year,g_year-1,g_year-2)
AND trx_lines.application_id = trx.application_id
AND trx_lines.trx_id = trx.orig_trx_id
AND trx_lines.record_type in (1,2,3)
AND NVL(trx_lines.is_above_threshold,'N')= 'Y')
WHERE trx.REQUEST_ID = g_conc_request_id
AND trx.record_type in (4,5) ;
debug_message('End of Insert_Above_Threshold_Lines');
g_errbuf :='Exception in JE_IT_INVOICES_ABOVE_THRESHOLD.Insert_Above_Threshold_Lines';
FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_api_name,SQLCODE||' - Exception in PROCEDURE Insert_Above_Threshold_Lines while updating cm/dm original invoice amounts.');
END Insert_Above_Threshold_Lines;
SELECT count(1)
INTO l_exist
FROM je_it_trx_above_thr_all
WHERE request_id = p_concurrent_request_id
AND eft_progression_num = p_progression_number+1;
UPDATE je_it_above_thr_hdr_all
SET report_mode = 'F'
WHERE request_id = p_concurrent_request_id;