[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Delete EXTERNAL transactions */
PROCEDURE del_trans_x ( -- p_org_name IN VARCHAR2,-- Bug 5207771 org_id removed
p_legal_entity_name IN VARCHAR2,
p_fin_ind IN VARCHAR2) IS
bad_parameters EXCEPTION;
select legal_entity_id
into current_legal_entity_id
from XLE_FIRSTPARTY_INFORMATION_V
where upper(name) = upper(p_legal_entity_name);
DELETE je_es_modelo_190_all
WHERE legal_entity_id = current_legal_entity_id
and fin_ind = p_fin_ind;
select organization_id
into current_org_id
from hr_organization_units
where UPPER(name) = UPPER(p_org_name);
DELETE je_es_modelo_190_all
WHERE org_id = current_org_id
and fin_ind = p_fin_ind;
/* Delete Oracle Payables Hard Copy transactions */
PROCEDURE del_trans_s ( p_conc_req_id IN NUMBER,
p_legal_entity_id IN NUMBER,
p_org_id IN NUMBER ) IS
BEGIN
DELETE je_es_modelo_190_all
WHERE fin_ind = 'S'
and conc_req_id = p_conc_req_id
and legal_entity_id = p_legal_entity_id;
/* Delete Oracle Payables Magnetic transactions */
PROCEDURE del_trans_m ( p_legal_entity_id IN NUMBER,
p_org_id IN NUMBER) IS
BEGIN
DELETE je_es_modelo_190_all
WHERE fin_ind = 'S'
and conc_req_id is NULL
and legal_entity_id = p_legal_entity_id;
/* Insert EXTERNAL PAID transactions */
PROCEDURE ins_trans ( p_legal_entity_name IN VARCHAR2,
-- p_org_name IN VARCHAR2, -- Bug 5207771 org_id removed
p_fin_ind IN VARCHAR2,
p_remun_type IN VARCHAR2,
p_vendor_nif IN VARCHAR2,
p_vendor_name IN VARCHAR2,
p_date_paid IN VARCHAR2,
p_net_amount IN NUMBER,
p_withholding_tax_amount IN NUMBER,
p_zip_electronic IN VARCHAR2,
p_num_children IN NUMBER,
p_sign IN VARCHAR2,
p_tax_rate IN NUMBER,
p_year_due IN NUMBER,
p_sub_remun_type IN VARCHAR2,
p_withholdable_amt_in_kind IN NUMBER,
p_withheld_amt_in_kind IN NUMBER,
p_withheld_pymt_amt_in_kind IN NUMBER,
p_earned_amounts IN NUMBER,
p_contract_type IN NUMBER,
p_birth_year IN NUMBER,
p_disabled IN NUMBER,
p_family_situation IN NUMBER,
p_partner_fiscal_code IN VARCHAR2,
p_descendant_lt_3 IN NUMBER,
p_descendant_bt_3_16 IN NUMBER,
p_descendant_bt_16_25 IN NUMBER,
p_disable_desc_bt_33_65 IN NUMBER,
p_disable_desc_gt_65 IN NUMBER,
p_descendant_total IN NUMBER,
p_deductions IN NUMBER,
p_expenses IN NUMBER,
p_spouse_maintenance_amt IN NUMBER,
p_children_maintenance_amt IN NUMBER
) IS
bad_num_children EXCEPTION;
select legal_entity_id
into current_legal_entity_id
from XLE_FIRSTPARTY_INFORMATION_V
where upper(name) = upper(p_legal_entity_name);
select organization_id
into current_org_id
from hr_organization_units
where UPPER(name) = UPPER(p_org_name);
INSERT INTO je_es_modelo_190_all(
legal_entity_id,
-- org_id, -- Bug 5207771 org_id removed
fin_ind,
remun_type,
vendor_nif,
vendor_name,
date_paid,
net_amount,
withholding_tax_amount,
zip_electronic,
num_children,
sign,
tax_rate,
year_due,
sub_remun_type ,
withholdable_amt_in_kind ,
withholdable_amt_in_kind_sign ,
withheld_amt_in_kind ,
withheld_pymt_amt_in_kind ,
earned_amounts ,
contract_type ,
birth_year ,
disabled ,
family_situation ,
partner_fiscal_code ,
descendant_lt_3 ,
descendant_bt_3_16 ,
descendant_bt_16_25 ,
disable_desc_bt_33_65 ,
disable_desc_gt_65 ,
descendant_total ,
deductions ,
expenses ,
spouse_maintenance_amt ,
children_maintenance_amt
)
values( current_legal_entity_id,
-- current_org_id, -- Bug 5207771 org_id removed
p_fin_ind,
p_remun_type,
p_vendor_nif,
substr(p_vendor_name,1,80) , -- AP UTF8 Changes 2398166
p_date_paid,
p_net_amount,
p_withholding_tax_amount,
p_zip_electronic,
p_num_children,
p_sign,
p_tax_rate,
p_year_due,
p_sub_remun_type ,
p_withholdable_amt_in_kind ,
decode(p_withholdable_amt_in_kind,NULL,NULL,
decode(sign(p_withholdable_amt_in_kind),-1,'N',' ')),
p_withheld_amt_in_kind ,
p_withheld_pymt_amt_in_kind ,
p_earned_amounts ,
p_contract_type ,
p_birth_year ,
p_disabled ,
p_family_situation ,
p_partner_fiscal_code ,
p_descendant_lt_3 ,
p_descendant_bt_3_16 ,
p_descendant_bt_16_25 ,
p_disable_desc_bt_33_65 ,
p_disable_desc_gt_65 ,
p_descendant_total ,
p_deductions ,
p_expenses ,
p_spouse_maintenance_amt ,
p_children_maintenance_amt
);
dbmsmsg('Error: Please use the correct parameters when inserting FIN_IND = S transactions');
/* Insert EXTERNAL APPROVED transactions */
PROCEDURE ins_trans ( p_legal_entity_name IN VARCHAR2,
-- p_org_name IN VARCHAR2,-- Bug 5207771 org_id removed
p_fin_ind IN VARCHAR2,
p_remun_type IN VARCHAR2,
p_vendor_nif IN VARCHAR2,
p_vendor_name IN VARCHAR2,
p_gl_date IN VARCHAR2,
p_net_amount IN NUMBER,
p_withholding_tax_amount IN NUMBER,
p_zip_electronic IN VARCHAR2,
p_num_children IN NUMBER,
p_sign IN VARCHAR2,
p_tax_rate IN NUMBER,
p_year_due IN NUMBER,
p_sub_remun_type IN VARCHAR2,
p_withholdable_amt_in_kind IN NUMBER,
p_withheld_amt_in_kind IN NUMBER,
p_withheld_pymt_amt_in_kind IN NUMBER,
p_earned_amounts IN NUMBER,
p_contract_type IN NUMBER,
p_birth_year IN NUMBER,
p_disabled IN NUMBER,
p_family_situation IN NUMBER,
p_partner_fiscal_code IN VARCHAR2,
p_descendant_lt_3 IN NUMBER,
p_descendant_bt_3_16 IN NUMBER,
p_descendant_bt_16_25 IN NUMBER,
p_disable_desc_bt_33_65 IN NUMBER,
p_disable_desc_gt_65 IN NUMBER,
p_descendant_total IN NUMBER,
p_deductions IN NUMBER,
p_expenses IN NUMBER,
p_spouse_maintenance_amt IN NUMBER,
p_children_maintenance_amt IN NUMBER
) IS
bad_num_children EXCEPTION;
select legal_entity_id
into current_legal_entity_id
from XLE_FIRSTPARTY_INFORMATION_V
where upper(name) = upper(p_legal_entity_name);
select organization_id
into current_org_id
from hr_organization_units
where UPPER(name) = UPPER(p_org_name);
INSERT INTO je_es_modelo_190_all(
legal_entity_id,
-- org_id, -- Bug 5207771 org_id removed
fin_ind,
remun_type,
vendor_nif,
vendor_name,
gl_date,
net_amount,
withholding_tax_amount,
zip_electronic,
num_children,
sign,
tax_rate,
year_due,
sub_remun_type ,
withholdable_amt_in_kind ,
withholdable_amt_in_kind_sign ,
withheld_amt_in_kind ,
withheld_pymt_amt_in_kind ,
earned_amounts ,
contract_type ,
birth_year ,
disabled ,
family_situation ,
partner_fiscal_code ,
descendant_lt_3 ,
descendant_bt_3_16 ,
descendant_bt_16_25 ,
disable_desc_bt_33_65 ,
disable_desc_gt_65 ,
descendant_total ,
deductions ,
expenses ,
spouse_maintenance_amt ,
children_maintenance_amt
)
values( current_legal_entity_id,
-- current_org_id, -- Bug 5207771 org_id removed
p_fin_ind,
p_remun_type,
p_vendor_nif,
substr(p_vendor_name,1,80), -- AP UTF8 Changes 2398166
p_gl_date,
p_net_amount,
p_withholding_tax_amount,
p_zip_electronic,
p_num_children,
p_sign,
p_tax_rate,
p_year_due,
p_sub_remun_type ,
p_withholdable_amt_in_kind ,
decode(p_withholdable_amt_in_kind,NULL,NULL,
decode(sign(p_withholdable_amt_in_kind),-1,'N',' ')),
p_withheld_amt_in_kind ,
p_withheld_pymt_amt_in_kind ,
p_earned_amounts ,
p_contract_type ,
p_birth_year ,
p_disabled ,
p_family_situation ,
p_partner_fiscal_code ,
p_descendant_lt_3 ,
p_descendant_bt_3_16 ,
p_descendant_bt_16_25 ,
p_disable_desc_bt_33_65 ,
p_disable_desc_gt_65 ,
p_descendant_total ,
p_deductions ,
p_expenses ,
p_spouse_maintenance_amt ,
p_children_maintenance_amt
);
dbmsmsg('Error: Please use the correct parameters when inserting FIN_IND = S transactions');
/* Insert Oracle Payables transactions */
PROCEDURE ins_trans ( legal_entity_id NUMBER,
org_id NUMBER,
conc_req_id NUMBER,
remun_type VARCHAR2,
sub_remun_type VARCHAR2,
vendor_nif VARCHAR2,
vendor_name VARCHAR2,
invoice_id NUMBER,
invoice_num VARCHAR2,
inv_doc_seq_num VARCHAR2,
invoice_date VARCHAR2,
gl_date VARCHAR2,
invoice_payment_id NUMBER,
date_paid VARCHAR2,
net_amount NUMBER,
withholding_tax_amount NUMBER,
zip_electronic VARCHAR2,
zip_legal VARCHAR2,
city_legal VARCHAR2,
num_children NUMBER,
sign VARCHAR2,
tax_rate NUMBER,
tax_name VARCHAR2,
year_due NUMBER
) IS
BEGIN
INSERT INTO je_es_modelo_190_all( legal_entity_id,
org_id,
conc_req_id,
fin_ind,
remun_type,
vendor_nif,
vendor_name,
invoice_id,
invoice_num,
inv_doc_seq_num,
invoice_date,
gl_date,
invoice_payment_id,
date_paid,
net_amount,
withholding_tax_amount,
zip_electronic,
zip_legal,
city_legal,
num_children,
sign,
tax_rate,
tax_name,
year_due,
sub_remun_type
)
values( legal_entity_id,
org_id,
conc_req_id,
'S',
remun_type,
vendor_nif,
substr(vendor_name,1,80) , -- AP UTF8 Changes 2398166
invoice_id,
invoice_num,
inv_doc_seq_num,
invoice_date,
gl_date,
invoice_payment_id,
date_paid,
net_amount,
withholding_tax_amount,
zip_electronic,
zip_legal,
city_legal,
num_children,
sign,
tax_rate,
tax_name,
year_due,
sub_remun_type
);
select (0 - sum(nvl(dist.base_amount,nvl(dist.amount,0))))
into amount_withheld
from ap_invoice_distributions_all dist,
ap_invoice_lines_all line,
ap_invoices_all inv
where dist.invoice_id = l_invoice_id
and inv.legal_entity_id = nvl(l_legal_entity_id, inv.legal_entity_id)
-- Bug 5207771 : Org_id is removed
-- and inv.org_id = nvl(l_org_id, inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
-- and dist.distribution_line_number = line.line_number commented and added below logic for bug 7300332
and dist.invoice_line_number = line.line_number
and dist.line_type_lookup_code = 'AWT';
select (0 - sum(nvl(dist.base_amount,nvl(dist.amount,0))))
into prepaid_amount
from ap_invoice_distributions_all dist,
ap_invoice_lines_all line,
ap_invoices_all inv
where dist.invoice_id = l_invoice_id
and inv.legal_entity_id = nvl(l_legal_entity_id, inv.legal_entity_id)
-- Bug 5207771 : Org_id is removed
-- and inv.org_id = nvl(l_org_id, inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
-- and dist.distribution_line_number = line.line_number Commented and added below logic for Bug 7300332
and dist.invoice_line_number = line.line_number
and dist.line_type_lookup_code = 'PREPAY';
SELECT NVL(SUM(nvl(dist.base_amount,NVL(dist.amount,0))),0)
INTO l_awt_net_total
FROM ap_invoice_distributions_all dist,
ap_invoice_lines_all line,
ap_invoices_all inv
WHERE dist.invoice_id = l_invoice_id
and inv.legal_entity_id = nvl(l_legal_entity_id, inv.legal_entity_id)
-- Bug 5207771 : Org_id is removed
-- and inv.org_id = nvl(l_org_id, inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
-- and dist.distribution_line_number = line.line_number Commented and added below for bug 7300332
and dist.invoice_line_number = line.line_number
and dist.awt_group_id IS NOT NULL
and dist.line_type_lookup_code NOT IN ('AWT')
and trunc(dist.accounting_date) between trunc(l_Date_From) and trunc(l_Date_To);
SELECT COUNT(aip.invoice_payment_id)
INTO l_payments_count
FROM ap_invoice_payments_all aip,
ap_checks_all ac
WHERE aip.invoice_id = l_invoice_id
AND ac.legal_entity_id = nvl(l_legal_entity_id, ac.legal_entity_id)
-- bug 5207771: Removed org_id condition
--and ac.org_id = nvl(l_org_id,ac.org_id)
AND aip.check_id = ac.check_id
AND ac.void_date is null;
SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
sum(decode(dist.awt_group_id,NULL,0,
decode(dist.line_type_lookup_code,'AWT',0,
nvl(dist.base_amount,dist.amount)))) net_amount,
sum(decode(dist.line_type_lookup_code,'AWT',
nvl(dist.base_amount,dist.amount),0)) withholding_tax_amount
FROM po_vendors v,
po_vendor_sites_all vs,
fnd_lookups fl,
ap_invoices_all inv,
ap_invoice_lines_all line,
ap_invoice_distributions_all dist,
ap_tax_codes_all atc,
ap_awt_tax_rates_all awt,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE v.vendor_id = vs.vendor_id
AND nvl(v.employee_id,-99) = papf.person_id (+)
-- bug 8551359 - start
--AND vs.tax_reporting_site_flag = 'Y'
AND exists (select 'x'
from po_vendor_sites_all
where vendor_id = v.vendor_id
and tax_reporting_site_flag = 'Y'
and org_id = p_rep_site_ou)
-- bug 8551359 - end
AND vs.country = fl.lookup_code(+)
AND fl.lookup_type = 'JEES_EURO_COUNTRY_CODES'
AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
AND inv.vendor_id = v.vendor_id
and vs.vendor_site_id = inv.vendor_site_id
and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
-- bug 5207771: Removed org_id condition
--and inv.org_id = nvl(p_org_id,inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
and dist.invoice_line_number = line.line_number
-----and inv.cancelled_date is null -- Bug 2228008 )
AND dist.parent_reversal_id is null
-- bug 8496890
/*AND not exists ( select 1
from ap_invoice_distributions dist1, gl_period_statuses gl
where gl.application_id = 101
and dist1.invoice_id = inv.invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and gl.ledger_id = dist1.set_of_books_id
and dist.accounting_date between gl.start_date and gl.end_date
and dist1.accounting_date <= gl.end_date )
*/
AND not exists ( select 1
from ap_invoice_distributions dist1
where dist1.invoice_id = inv.invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To)
)
-- bug 8496890
AND trunc(dist.accounting_date,'DD')
between fnd_date.canonical_to_date(P_Date_From)
AND fnd_date.canonical_to_date(P_Date_To)
AND ((dist.line_type_lookup_code = 'AWT')
OR
(dist.awt_group_id is not NULL))
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
dist.ACCRUAL_POSTED_FLAG,
dist.CASH_POSTED_FLAG,
dist.POSTED_FLAG, inv.org_id) in ('Y','P')
-- bug 5207771 Added legal_entity id as 4th parameter, above line
AND dist.withholding_tax_code_id = atc.tax_id (+) -- bug 5102299
AND atc.name = awt.tax_name(+)
AND awt.vendor_id is null /* Ignore any Vendor Lines */
-- Bug 5207771 : Added to remove the duplicates WH lines
AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
OR (dist.awt_tax_rate_id is NULL) )
-- Ignore any invoices which do not have 'AWT' distribution lines
AND EXISTS ( select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.withholding_tax_code_id in
-- Bug 2019586: Column name should be tax_id.
-- (select tax_code_id from ap_tax_codes
(select tax_id from ap_tax_codes_all
where vat_transaction_type = p_wht_tax_type))
GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80) ,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3))
HAVING sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0
-- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
-- the invoices that are cancelled in different accounting period.
-- spanugan 17/12/2004
UNION
SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
sum(decode(dist.awt_group_id,NULL,0,
decode(dist.line_type_lookup_code,'AWT',0,
nvl(dist.base_amount,dist.amount)))) net_amount,
sum(decode(dist.line_type_lookup_code,'AWT',
nvl(dist.base_amount,dist.amount),0)) withholding_tax_amount
FROM fnd_lookups fl,
po_vendors v,
po_vendor_sites_all vs,
ap_tax_codes_all atc,
ap_awt_tax_rates_all awt,
ap_invoices_all inv,
ap_invoice_lines_all line,
ap_invoice_distributions_all dist,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE v.vendor_id = vs.vendor_id
AND nvl(v.employee_id,-99) = papf.person_id (+)
-- bug 8551359 - start
--AND vs.tax_reporting_site_flag = 'Y'
AND exists (select 'x'
from po_vendor_sites_all
where vendor_id = v.vendor_id
and tax_reporting_site_flag = 'Y'
and org_id = p_rep_site_ou)
-- bug 8551359 - end
AND vs.country = fl.lookup_code(+)
AND fl.lookup_type = 'JEES_EURO_COUNTRY_CODES'
AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
AND inv.vendor_id = v.vendor_id
and vs.vendor_site_id = inv.vendor_site_id
and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
-- bug 5207771: Removed org_id condition
--and inv.org_id = nvl(p_org_id,inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
and dist.invoice_line_number = line.line_number
-- BUG 3930123 : spanugan
/*AND inv.cancelled_date is not null
AND (
(dist.cancellation_flag is null
AND dist.accounting_date < (select distinct gl.start_date
from gl_period_statuses gl
where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
from ap_invoice_distributions_all dist1
where dist1.invoice_id = inv.invoice_id
and dist1.cancellation_flag = 'Y' )))
OR
(dist.cancellation_flag = 'Y'
AND dist.accounting_date > (select distinct gl.end_date
from gl_period_statuses gl
where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
from ap_invoice_distributions_all dist1
where dist1.invoice_id = inv.invoice_id
and dist1.cancellation_flag is null )))
)
-- END
-- Bug 3930123 JCHALL . Changed the subquery above from
-- a single row returned to accept mutiple rows.
--
*/
AND dist.parent_reversal_id is not null
-- bug 8496890
/*
AND dist.accounting_date > (select distinct gl.end_date
from ap_invoice_distributions dist1, gl_period_statuses gl
where gl.application_id = 101
and dist1.invoice_id = inv.invoice_id
and dist.parent_reversal_id = dist1.invoice_distribution_id
and gl.ledger_id = dist1.set_of_books_id
and dist1.accounting_date between gl.start_date and gl.end_date)
*/
AND not exists (select 1 from ap_invoice_distributions dist1
where dist1.invoice_id = inv.invoice_id
and dist.parent_reversal_id = dist1.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To))
-- bug 8496890
AND trunc(dist.accounting_date,'DD')
between fnd_date.canonical_to_date(P_Date_From)
AND fnd_date.canonical_to_date(P_Date_To)
AND ((dist.line_type_lookup_code = 'AWT')
OR
(dist.awt_group_id is not NULL))
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
dist.ACCRUAL_POSTED_FLAG,
dist.CASH_POSTED_FLAG,
dist.POSTED_FLAG, inv.org_id) in ('Y','P')
-- bug 5207771 Added legal_entity id as 4th parameter, above line
AND dist.withholding_tax_code_id = atc.tax_id(+)
AND atc.name = awt.tax_name(+)
AND awt.vendor_id is null /* Ignore any Vendor Lines */
-- Bug 5207771 : Added to remove the duplicates WH lines
AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
OR (dist.awt_tax_rate_id is NULL) )
-- Ignore any invoices which do not have 'AWT' distribution lines
AND EXISTS ( select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.withholding_tax_code_id in
-- Bug 2019586: Column name should be tax_id.
-- (select tax_code_id from ap_tax_codes
(select tax_id from ap_tax_codes_all
where vat_transaction_type = p_wht_tax_type))
GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80) ,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3))
HAVING sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0;
SELECT 'A',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
-- nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID),0) net_amount,
--bug10384931 nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
decode(seq.name || '-' ||
to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
dist.awt_invoice_payment_id,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'|| substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
0,
sum(nvl(dist.base_amount,dist.amount)),
dist.awt_flag dist_awt_flag, --bug 8709676
awt.tax_rate,
awt.tax_name,
'N'
FROM fnd_lookups fl,
po_vendors v,
po_vendor_sites_all vs,
ap_invoice_payments_all invpay,
ap_checks_all checks,
ap_tax_codes_all atc,
ap_awt_tax_rates_all awt,
fnd_document_sequences seq,
ap_invoices_all inv,
ap_invoice_lines_all line,
ap_invoice_distributions_all dist,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE vs.country = fl.lookup_code(+)
AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
---AND v.vendor_id = nvl(p_vendor_id,v.vendor_id)
AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
AND nvl(v.employee_id,-99) = papf.person_id (+)
AND inv.vendor_id = v.vendor_id
AND v.vendor_id = vs.vendor_id
-- bug 8551359 - start
--AND vs.tax_reporting_site_flag = 'Y'
AND exists (select 'x'
from po_vendor_sites_all
where vendor_id = v.vendor_id
and tax_reporting_site_flag = 'Y'
and org_id = p_rep_site_ou)
-- bug 8551359 - end
and vs.vendor_site_id = inv.vendor_site_id
AND nvl(inv.awt_flag,'N') = 'Y'
and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
-- bug 5207771: Removed org_id condition
--and inv.org_id = nvl(p_org_id,inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
and dist.invoice_line_number = line.line_number
---and inv.cancelled_date is null -- Bug 2228008
AND dist.parent_reversal_id is null
-- bug 8496890
/*
AND not exists ( select 1
from ap_invoice_distributions dist1, gl_period_statuses gl
where gl.application_id = 101
and dist1.invoice_id = inv.invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and gl.ledger_id = dist1.set_of_books_id
and dist.accounting_date between gl.start_date and gl.end_date
and dist1.accounting_date <= gl.end_date )
*/
AND not exists ( select 1
from ap_invoice_distributions dist1
where dist1.invoice_id = inv.invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To)
)
-- bug 8496890
AND inv.invoice_id = invpay.invoice_id
AND ( invpay.posted_flag in ('Y','P')
or invpay.cash_posted_flag in ('Y','P')
or invpay.accrual_posted_flag in ('Y','P'))
AND invpay.check_id = checks.check_id
AND checks.void_date is null
AND trunc(invpay.accounting_date,'DD')
between
nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
AND (dist.line_type_lookup_code = 'AWT')
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
DIST.ACCRUAL_POSTED_FLAG,
DIST.CASH_POSTED_FLAG,
dist.POSTED_FLAG, inv.org_id) in ('Y','P')
AND dist.withholding_tax_code_id = atc.tax_id(+)
AND atc.name = awt.tax_name(+)
AND awt.vendor_id is null /* Ignore any Vendor Lines */
AND invpay.accounting_date
between nvl(awt.start_date,invpay.accounting_date)
and nvl(awt.end_date, invpay.accounting_date)
AND inv.doc_sequence_id = seq.doc_sequence_id(+)
-- Bug 5207771 : Added to remove the duplicates WH lines
AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
OR (dist.awt_tax_rate_id is NULL) )
-- Ignore any invoices which do not have 'AWT' distribution lines
AND EXISTS (select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.withholding_tax_code_id in
-- Bug 2019586: Column name should be tax_id.
-- (select tax_code_id from ap_tax_codes
(select tax_id
from ap_tax_codes_all
where vat_transaction_type = p_wht_tax_type))
AND NOT EXISTS ( select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.awt_flag <> 'A')
GROUP BY 'A',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80) ,
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount) ,
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0),
--10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0),
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0),
decode(seq.name || '-' ||
to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) ,
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
dist.awt_invoice_payment_id,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
0,
dist.awt_flag, --bug 8709676
awt.tax_rate,
awt.tax_name,
'N'
HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
-- Bug 1212074
-- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
-- the invoices that are cancelled in different accounting period.
-- spanugan 17/12/2004
UNION
SELECT 'A',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
--bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
decode(seq.name || '-' ||
to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
dist.awt_invoice_payment_id,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
0,
sum(nvl(dist.base_amount,dist.amount)),
dist.awt_flag dist_awt_flag, --bug 8709676
awt.tax_rate,
awt.tax_name,
'Y'
FROM fnd_lookups fl,
po_vendors v,
po_vendor_sites_all vs,
ap_invoice_payments_all invpay,
ap_checks_all checks,
ap_tax_codes_all atc,
ap_awt_tax_rates_all awt,
fnd_document_sequences seq,
ap_invoices_all inv,
ap_invoice_lines_all line,
ap_invoice_distributions_all dist,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE vs.country = fl.lookup_code(+)
AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
---AND v.vendor_id = nvl(p_vendor_id,v.vendor_id)
AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
AND nvl(v.employee_id,-99) = papf.person_id (+)
AND inv.vendor_id = v.vendor_id
AND v.vendor_id = vs.vendor_id
-- bug 8551359 - start
--AND vs.tax_reporting_site_flag = 'Y'
AND exists (select 'x'
from po_vendor_sites_all
where vendor_id = v.vendor_id
and tax_reporting_site_flag = 'Y'
and org_id = p_rep_site_ou)
-- bug 8551359 - end
and vs.vendor_site_id = inv.vendor_site_id
AND nvl(inv.awt_flag,'N') = 'Y'
and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
-- bug 5207771: Removed org_id condition
--and inv.org_id = nvl(p_org_id,inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
and dist.invoice_line_number = line.line_number
-- BUG 3930123 : spanugan
/*AND inv.cancelled_date is not null
AND (
(dist.cancellation_flag is null
AND dist.accounting_date < (select distinct gl.start_date
from gl_period_statuses gl
where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
from ap_invoice_distributions_all dist1
where dist1.invoice_id = inv.invoice_id
and dist1.cancellation_flag = 'Y' )))
OR
(dist.cancellation_flag = 'Y'
AND dist.accounting_date > (select distinct gl.end_date
from gl_period_statuses gl
where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
from ap_invoice_distributions_all dist1
where dist1.invoice_id = inv.invoice_id
and dist1.cancellation_flag is null )))
)
-- END
*/
AND dist.parent_reversal_id is not null
-- bug 8496890
/*
AND dist.accounting_date > (select distinct gl.end_date
from ap_invoice_distributions dist1, gl_period_statuses gl
where gl.application_id = 101
and dist1.invoice_id = inv.invoice_id
and dist.parent_reversal_id = dist1.invoice_distribution_id
and gl.ledger_id = dist1.set_of_books_id
and dist1.accounting_date between gl.start_date and gl.end_date)
*/
AND not exists (select 1 from ap_invoice_distributions dist1
where dist1.invoice_id = inv.invoice_id
and dist.parent_reversal_id = dist1.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To))
-- bug 8496890
AND inv.invoice_id = invpay.invoice_id
AND ( invpay.posted_flag in ('Y','P')
or invpay.cash_posted_flag in ('Y','P')
or invpay.accrual_posted_flag in ('Y','P'))
AND invpay.check_id = checks.check_id
AND checks.void_date is null
AND trunc(invpay.accounting_date,'DD')
between
nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
AND (dist.line_type_lookup_code = 'AWT')
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
DIST.ACCRUAL_POSTED_FLAG,
DIST.CASH_POSTED_FLAG,
dist.POSTED_FLAG, inv.org_id) in ('Y','P')
AND dist.withholding_tax_code_id = atc.tax_id(+)
AND atc.name = awt.tax_name(+)
AND awt.vendor_id is null /* Ignore any Vendor Lines */
-- Bug 5207771 : Added to remove the duplicates WH lines
AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
OR (dist.awt_tax_rate_id is NULL) )
AND invpay.accounting_date
between nvl(awt.start_date,invpay.accounting_date)
and nvl(awt.end_date, invpay.accounting_date)
AND inv.doc_sequence_id = seq.doc_sequence_id(+)
-- Ignore any invoices which do not have 'AWT' distribution lines
AND EXISTS (select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.withholding_tax_code_id in
-- Bug 2019586: Column name should be tax_id.
-- (select tax_code_id from ap_tax_codes
(select tax_id
from ap_tax_codes_all
where vat_transaction_type = p_wht_tax_type))
AND NOT EXISTS ( select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.awt_flag <> 'A')
GROUP BY 'A',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80) ,
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount) ,
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0),
--bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0),
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0),
decode(seq.name || '-' ||
to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) ,
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
dist.awt_invoice_payment_id,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
0,
dist.awt_flag, --bug 8709676
awt.tax_rate,
awt.tax_name,
'Y'
HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
-- Bug 1212074
UNION
SELECT 'A',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
v.vendor_name,
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
--bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
dist.awt_invoice_payment_id,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'|| substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
--bug11891899 (nvl(dist.awt_gross_amount,0)) wht_net_amount,
(nvl(dist.awt_gross_amount,0)*nvl(dist.exchange_rate,1)) wht_net_amount, --bug11891899
--bug 12420102 (nvl(dist.base_amount,dist.amount)),
sum(nvl(dist.base_amount,dist.amount)), --bug 12420102
dist.awt_flag dist_awt_flag, -- bug 8709676
awt.tax_rate,
awt.tax_name,
'N'
FROM fnd_lookups fl,
po_vendors v,
po_vendor_sites_all vs,
ap_invoice_payments_all invpay,
ap_checks_all checks,
ap_tax_codes_all atc,
ap_awt_tax_rates_all awt,
fnd_document_sequences seq,
ap_invoices_all inv,
ap_invoice_lines_all line,
ap_invoice_distributions_all dist,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE vs.country = fl.lookup_code(+)
AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
AND nvl(v.employee_id,-99) = papf.person_id (+)
AND inv.vendor_id = v.vendor_id
AND v.vendor_id = vs.vendor_id
and vs.vendor_site_id = inv.vendor_site_id
-- bug 8551359 - start
--AND vs.tax_reporting_site_flag = 'Y'
AND exists (select 'x'
from po_vendor_sites_all
where vendor_id = v.vendor_id
and tax_reporting_site_flag = 'Y'
and org_id = p_rep_site_ou)
-- bug 8551359 - end
AND nvl(inv.awt_flag,'N') = 'N'
and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
-- bug 5207771: Removed org_id condition
--and inv.org_id = nvl(p_org_id,inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
and dist.invoice_line_number = line.line_number
---and inv.cancelled_date is null -- Bug 2228008
AND dist.parent_reversal_id is null
-- bug 8496890
/*
AND not exists ( select 1
from ap_invoice_distributions dist1, gl_period_statuses gl
where gl.application_id = 101
and dist1.invoice_id = inv.invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and gl.ledger_id = dist1.set_of_books_id
and dist.accounting_date between gl.start_date and gl.end_date
and dist1.accounting_date <= gl.end_date )
*/
AND not exists ( select 1
from ap_invoice_distributions dist1
where dist1.invoice_id = inv.invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To)
)
-- bug 8496890
AND inv.invoice_id = invpay.invoice_id
AND ( invpay.posted_flag in ('Y','P')
or invpay.cash_posted_flag in ('Y','P')
or invpay.accrual_posted_flag in ('Y','P'))
AND invpay.check_id = checks.check_id
AND checks.void_date is null
AND trunc(invpay.accounting_date,'DD')
between
nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
AND dist.awt_invoice_payment_id = invpay.invoice_payment_id
AND (dist.line_type_lookup_code = 'AWT')
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
DIST.ACCRUAL_POSTED_FLAG,
DIST.CASH_POSTED_FLAG,
dist.POSTED_FLAG, inv.org_id) in ('Y','P')
AND dist.withholding_tax_code_id = atc.tax_id(+)
AND atc.name = awt.tax_name(+)
AND awt.vendor_id is null
AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
AND nvl(awt.end_date, invpay.accounting_date)
AND inv.doc_sequence_id = seq.doc_sequence_id(+)
-- Bug 5207771 : Added to remove the duplicates WH lines
AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
OR (dist.awt_tax_rate_id is NULL) )
AND EXISTS (select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.withholding_tax_code_id in
-- Bug 2019586: Column name should be tax_id.
-- (select tax_code_id from ap_tax_codes
(select tax_id
from ap_tax_codes_all
where vat_transaction_type = p_wht_tax_type))
AND NOT EXISTS ( select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.awt_flag <> 'A')
-- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
-- the invoices that are cancelled in different accounting period.
-- spanugan 17/12/2004
-------Bug 12420102 Added below group by
GROUP BY 'A',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
v.vendor_name,
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount) ,
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)),
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0),
decode(seq.name || '-' ||
to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) ,
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
dist.awt_invoice_payment_id,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
nvl(dist.awt_gross_amount,0)*nvl(dist.exchange_rate,1),
dist.awt_flag,
awt.tax_rate,
awt.tax_name,
'N'
HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
--------bug 12420102
UNION
SELECT 'A',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
v.vendor_name,
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
-- nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID),0) net_amount,
--bug10384931 nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
decode(seq.name || '-' ||
to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
dist.awt_invoice_payment_id,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
--bug11891899 (nvl(dist.awt_gross_amount,0)) wht_net_amount,
(nvl(dist.awt_gross_amount,0)*nvl(dist.exchange_rate,1)) wht_net_amount, --bug11891899
--bug 12420102 (nvl(dist.base_amount,dist.amount)),
sum(nvl(dist.base_amount,dist.amount)), --bug 12420102
dist.awt_flag dist_awt_flag, -- bug 8709676
awt.tax_rate,
awt.tax_name,
'Y'
FROM fnd_lookups fl,
po_vendors v,
po_vendor_sites_all vs,
ap_invoice_payments_all invpay,
ap_checks_all checks,
ap_tax_codes_all atc,
ap_awt_tax_rates_all awt,
fnd_document_sequences seq,
ap_invoices_all inv,
ap_invoice_lines_all line,
ap_invoice_distributions_all dist,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE vs.country = fl.lookup_code(+)
AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
AND nvl(v.employee_id,-99) = papf.person_id (+)
AND inv.vendor_id = v.vendor_id
AND v.vendor_id = vs.vendor_id
and vs.vendor_site_id = inv.vendor_site_id
-- bug 8551359 - start
--AND vs.tax_reporting_site_flag = 'Y'
AND exists (select 'x'
from po_vendor_sites_all
where vendor_id = v.vendor_id
and tax_reporting_site_flag = 'Y'
and org_id = p_rep_site_ou)
-- bug 8551359 - end
AND nvl(inv.awt_flag,'N') = 'N'
and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
-- bug 5207771: Removed org_id condition
--and inv.org_id = nvl(p_org_id,inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
and dist.invoice_line_number = line.line_number
-- BUG 3930123 : spanugan
/*AND inv.cancelled_date is not null
AND
(
(dist.cancellation_flag is null
AND dist.accounting_date < (select distinct gl.start_date
from gl_period_statuses gl
where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
from ap_invoice_distributions_all dist1
where dist1.invoice_id = inv.invoice_id
and dist1.cancellation_flag = 'Y' )))
OR
(dist.cancellation_flag = 'Y'
AND dist.accounting_date > (select distinct gl.end_date
from gl_period_statuses gl
where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
from ap_invoice_distributions_all dist1
where dist1.invoice_id = inv.invoice_id
and dist1.cancellation_flag is null )))
)
-- END
*/
AND dist.parent_reversal_id is not null
-- bug 8496890
/*
AND dist.accounting_date > (select distinct gl.end_date
from ap_invoice_distributions dist1, gl_period_statuses gl
where gl.application_id = 101
and dist1.invoice_id = inv.invoice_id
and dist.parent_reversal_id = dist1.invoice_distribution_id
and gl.ledger_id = dist1.set_of_books_id
and dist1.accounting_date between gl.start_date and gl.end_date)
*/
AND not exists (select 1 from ap_invoice_distributions dist1
where dist1.invoice_id = inv.invoice_id
and dist.parent_reversal_id = dist1.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To))
-- bug 8496890
AND inv.invoice_id = invpay.invoice_id
AND ( invpay.posted_flag in ('Y','P')
or invpay.cash_posted_flag in ('Y','P')
or invpay.accrual_posted_flag in ('Y','P'))
AND invpay.check_id = checks.check_id
AND checks.void_date is null
AND trunc(invpay.accounting_date,'DD')
between
nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
AND dist.awt_invoice_payment_id = invpay.invoice_payment_id
AND (dist.line_type_lookup_code = 'AWT')
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
DIST.ACCRUAL_POSTED_FLAG,
DIST.CASH_POSTED_FLAG,
dist.POSTED_FLAG, inv.org_id) in ('Y','P')
AND dist.withholding_tax_code_id = atc.tax_id(+)
AND atc.name = awt.tax_name(+)
AND awt.vendor_id is null
-- Bug 5207771 : Added to remove the duplicates WH lines
AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
OR (dist.awt_tax_rate_id is NULL) )
AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
AND nvl(awt.end_date, invpay.accounting_date)
AND inv.doc_sequence_id = seq.doc_sequence_id(+)
AND EXISTS (select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.withholding_tax_code_id in
-- Bug 2019586: Column name should be tax_id.
-- (select tax_code_id from ap_tax_codes
(select tax_id
from ap_tax_codes_all
where vat_transaction_type = p_wht_tax_type))
AND NOT EXISTS ( select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.awt_flag <> 'A')
----------bug 12420102 Added below group by
GROUP BY 'A',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
v.vendor_name,
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount) ,
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)),
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0),
decode(seq.name || '-' ||
to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) ,
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
dist.awt_invoice_payment_id,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
nvl(dist.awt_gross_amount,0)*nvl(dist.exchange_rate,1),
dist.awt_flag,
awt.tax_rate,
awt.tax_name,
'Y'
HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
---------bug 12420102
UNION
SELECT 'M',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
v.vendor_name,
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
--bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
decode(seq.name || '-' ||
to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
0,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
0,
sum(nvl(dist.base_amount,dist.amount)),
dist.awt_flag dist_awt_flag, --bug 8709676
awt.tax_rate,
awt.tax_name,
'N'
FROM fnd_lookups fl,
po_vendors v,
po_vendor_sites_all vs,
ap_invoice_payments_all invpay,
ap_checks_all checks,
ap_tax_codes_all atc,
ap_awt_tax_rates_all awt,
fnd_document_sequences seq,
ap_invoices_all inv,
ap_invoice_lines_all line,
ap_invoice_distributions_all dist,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE vs.country = fl.lookup_code(+)
AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
AND nvl(v.employee_id,-99) = papf.person_id (+)
AND inv.vendor_id = v.vendor_id
AND v.vendor_id = vs.vendor_id
and vs.vendor_site_id = inv.vendor_site_id
-- bug 8551359 - start
--AND vs.tax_reporting_site_flag = 'Y'
AND exists (select 'x'
from po_vendor_sites_all
where vendor_id = v.vendor_id
and tax_reporting_site_flag = 'Y'
and org_id = p_rep_site_ou)
-- bug 8551359 - end
and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
-- bug 5207771: Removed org_id condition
--and inv.org_id = nvl(p_org_id,inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
and dist.invoice_line_number = line.line_number
---and inv.cancelled_date is null -- Bug 2228008
AND dist.parent_reversal_id is null
-- bug 8496890
/*
AND not exists ( select 1
from ap_invoice_distributions dist1, gl_period_statuses gl
where gl.application_id = 101
and dist1.invoice_id = inv.invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and gl.ledger_id = dist1.set_of_books_id
and dist.accounting_date between gl.start_date and gl.end_date
and dist1.accounting_date <= gl.end_date )
*/
AND not exists ( select 1
from ap_invoice_distributions dist1
where dist1.invoice_id = inv.invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To)
)
-- bug 8496890
AND inv.invoice_id = invpay.invoice_id
AND ( invpay.posted_flag in ('Y','P')
or invpay.cash_posted_flag in ('Y','P')
or invpay.accrual_posted_flag in ('Y','P'))
AND invpay.check_id = checks.check_id
AND checks.void_date is null
AND trunc(invpay.accounting_date,'DD')
between
nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
AND (dist.line_type_lookup_code = 'AWT')
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
DIST.ACCRUAL_POSTED_FLAG,
DIST.CASH_POSTED_FLAG,
dist.POSTED_FLAG, inv.org_id) in ('Y','P')
AND dist.withholding_tax_code_id = atc.tax_id(+)
AND atc.name = awt.tax_name(+)
AND awt.vendor_id is null
-- Bug 5207771 : Added to remove the duplicates WH lines
AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
OR (dist.awt_tax_rate_id is NULL) )
AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
AND nvl(awt.end_date, invpay.accounting_date)
AND inv.doc_sequence_id = seq.doc_sequence_id(+)
AND EXISTS (select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.withholding_tax_code_id in
-- Bug 2019586: Column name should be tax_id.
-- (select tax_code_id from ap_tax_codes
(select tax_id
from ap_tax_codes_all
where vat_transaction_type = p_wht_tax_type))
AND EXISTS ( select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.awt_flag <> 'A')
GROUP BY 'M',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
v.vendor_name,
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount),
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) ,
--bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) ,
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
decode(seq.name || '-' ||
to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0),
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
0,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
0,
dist.awt_flag, --bug 8709676
awt.tax_rate,
awt.tax_name,
'N'
HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
-- Bug 1212074
-- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
-- the invoices that are cancelled in different accounting period.
-- spanugan 17/12/2004
UNION
SELECT 'M',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
v.vendor_name,
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
--bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
decode(seq.name || '-' ||
to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
0,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
0,
sum(nvl(dist.base_amount,dist.amount)),
dist.awt_flag dist_awt_flag, --bug 8709676
awt.tax_rate,
awt.tax_name,
'Y'
FROM fnd_lookups fl,
po_vendors v,
po_vendor_sites_all vs,
ap_invoice_payments_all invpay,
ap_checks_all checks,
ap_tax_codes_all atc,
ap_awt_tax_rates_all awt,
fnd_document_sequences seq,
ap_invoices_all inv,
ap_invoice_lines_all line,
ap_invoice_distributions_all dist,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE vs.country = fl.lookup_code(+)
AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
AND nvl(v.employee_id,-99) = papf.person_id (+)
AND inv.vendor_id = v.vendor_id
AND v.vendor_id = vs.vendor_id
and vs.vendor_site_id = inv.vendor_site_id
-- bug 8551359 - start
--AND vs.tax_reporting_site_flag = 'Y'
AND exists (select 'x'
from po_vendor_sites_all
where vendor_id = v.vendor_id
and tax_reporting_site_flag = 'Y'
and org_id = p_rep_site_ou)
-- bug 8551359 - end
and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
-- bug 5207771: Removed org_id condition
--and inv.org_id = nvl(p_org_id,inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
and dist.invoice_line_number = line.line_number
-- BUG 3930123 : spanugan
/*AND inv.cancelled_date is not null
AND
(
(dist.cancellation_flag is null
AND dist.accounting_date < (select distinct gl.start_date
from gl_period_statuses gl
where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
from ap_invoice_distributions_all dist1
where dist1.invoice_id = inv.invoice_id
and dist1.cancellation_flag = 'Y' )))
OR
(dist.cancellation_flag = 'Y'
AND dist.accounting_date > (select distinct gl.end_date
from gl_period_statuses gl
where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
from ap_invoice_distributions_all dist1
where dist1.invoice_id = inv.invoice_id
and dist1.cancellation_flag is null )))
)
-- END
*/
AND dist.parent_reversal_id is not null
-- bug 8496890
/*
AND dist.accounting_date > (select distinct gl.end_date
from ap_invoice_distributions dist1, gl_period_statuses gl
where gl.application_id = 101
and dist1.invoice_id = inv.invoice_id
and dist.parent_reversal_id = dist1.invoice_distribution_id
and gl.ledger_id = dist1.set_of_books_id
and dist1.accounting_date between gl.start_date and gl.end_date)
*/
AND not exists (select 1 from ap_invoice_distributions dist1
where dist1.invoice_id = inv.invoice_id
and dist.parent_reversal_id = dist1.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To))
-- bug 8496890
AND inv.invoice_id = invpay.invoice_id
AND ( invpay.posted_flag in ('Y','P')
or invpay.cash_posted_flag in ('Y','P')
or invpay.accrual_posted_flag in ('Y','P'))
AND invpay.check_id = checks.check_id
AND checks.void_date is null
AND trunc(invpay.accounting_date,'DD')
between
nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
AND (dist.line_type_lookup_code = 'AWT')
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
DIST.ACCRUAL_POSTED_FLAG,
DIST.CASH_POSTED_FLAG,
dist.POSTED_FLAG, inv.org_id) in ('Y','P')
AND dist.withholding_tax_code_id = atc.tax_id(+)
AND atc.name = awt.tax_name(+)
AND awt.vendor_id is null
-- Bug 5207771 : Added to remove the duplicates WH lines
AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
OR (dist.awt_tax_rate_id is NULL) )
AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
AND nvl(awt.end_date, invpay.accounting_date)
AND inv.doc_sequence_id = seq.doc_sequence_id(+)
AND EXISTS (select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.withholding_tax_code_id in
-- Bug 2019586: Column name should be tax_id.
-- (select tax_code_id from ap_tax_codes
(select tax_id
from ap_tax_codes_all
where vat_transaction_type = p_wht_tax_type))
AND EXISTS ( select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.awt_flag <> 'A')
GROUP BY 'M',
decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
v.vendor_name,
inv.awt_flag,
inv.payment_status_flag,
inv.invoice_id,
inv.invoice_num,
nvl(inv.base_amount,inv.invoice_amount),
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0),
--bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0),
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
decode(seq.name || '-' ||
to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
to_char(inv.doc_sequence_value)),
trunc(inv.invoice_date,'DD'),
invpay.invoice_payment_id,
nvl(je_es_whtax.get_payments_count(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0),
nvl(invpay.payment_base_amount,invpay.amount),
nvl(invpay.discount_taken,0),
trunc(invpay.accounting_date,'DD'),
0,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
0,
dist.awt_flag, --bug 8709676
awt.tax_rate,
awt.tax_name,
'Y'
HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0));
SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80), -- AP UTF8 Changes 2398166
nvl(inv.base_amount,inv.invoice_amount),
decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-',null,
seq.name || '-' || to_char(inv.doc_sequence_value)),
inv.invoice_id,
inv.invoice_num,
trunc(inv.invoice_date,'DD'),
trunc(dist.accounting_date,'DD'),
dist.awt_flag, -- bug 8709676
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
-- bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
-- bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount, --bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
sum(nvl(dist.base_amount,dist.amount)) withholding_tax_amount,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
awt.tax_rate,
awt.tax_name,
'N'
FROM fnd_lookups fl,
po_vendors v,
po_vendor_sites_all vs,
ap_tax_codes_all atc,
ap_awt_tax_rates_all awt,
fnd_document_sequences seq,
ap_invoices_all inv,
ap_invoice_lines_all line,
ap_invoice_distributions_all dist,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE vs.country = fl.lookup_code(+)
AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
AND nvl(v.employee_id,-99) = papf.person_id (+)
AND inv.vendor_id = v.vendor_id
AND v.vendor_id = vs.vendor_id
and vs.vendor_site_id = inv.vendor_site_id
-- bug 8551359 - start
--AND vs.tax_reporting_site_flag = 'Y'
AND exists (select 'x'
from po_vendor_sites_all
where vendor_id = v.vendor_id
and tax_reporting_site_flag = 'Y'
and org_id = p_rep_site_ou)
-- bug 8551359 - end
and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
-- bug 5207771: Removed org_id condition
--and inv.org_id = nvl(p_org_id,inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
and dist.invoice_line_number = line.line_number
---and inv.cancelled_date is null -- Bug 2228008
AND dist.parent_reversal_id is null
-- bug 8496890
/*
AND not exists ( select 1
from ap_invoice_distributions dist1, gl_period_statuses gl
where gl.application_id = 101
and dist1.invoice_id = inv.invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and gl.ledger_id = dist1.set_of_books_id
and dist.accounting_date between gl.start_date and gl.end_date
and dist1.accounting_date <= gl.end_date )
*/
AND not exists ( select 1
from ap_invoice_distributions dist1
where dist1.invoice_id = inv.invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To)
)
-- bug 8496890
AND trunc(dist.accounting_date,'DD')
between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To)
AND dist.line_type_lookup_code = 'AWT'
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
DIST.ACCRUAL_POSTED_FLAG,
DIST.CASH_POSTED_FLAG,
dist.POSTED_FLAG, inv.org_id) in ('Y','P')
AND dist.withholding_tax_code_id = atc.tax_id(+)
AND atc.name = awt.tax_name(+)
/*AND dist.accounting_date
between nvl(awt.start_date, dist.accounting_date)
and nvl(awt.end_date, dist.accounting_date)*/ -- bug 16343633
AND awt.vendor_id is null /* Ignore any Vendor Lines */
-- Bug 5207771 : Added to remove the duplicates WH lines
AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
OR (dist.awt_tax_rate_id is NULL) )
AND inv.doc_sequence_id = seq.doc_sequence_id(+)
-- Ignore any invoices which do not have 'AWT' distribution lines
AND EXISTS ( select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.withholding_tax_code_id in
-- Bug 2019586: Column name should be tax_id.
-- (select tax_code_id from ap_tax_codes
(select tax_id from ap_tax_codes_all
where vat_transaction_type = p_wht_tax_type))
GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80),
nvl(inv.base_amount,inv.invoice_amount),
decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-', null,
seq.name || '-' || to_char(inv.doc_sequence_value)),
inv.invoice_id,
inv.invoice_num,
trunc(inv.invoice_date,'DD'),
trunc(dist.accounting_date,'DD'),
dist.awt_flag, -- bug 8709676
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) ,
--bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) ,
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
awt.tax_rate,
awt.tax_name,
'N'
HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
-- Bug 1212074
-- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
-- the invoices that are cancelled in different accounting period.
-- spanugan 17/12/2004
UNION
SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80), -- AP UTF8 Changes 2398166
nvl(inv.base_amount,inv.invoice_amount),
decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-',null,
seq.name || '-' || to_char(inv.doc_sequence_value)),
inv.invoice_id,
inv.invoice_num,
trunc(inv.invoice_date,'DD'),
trunc(dist.accounting_date,'DD'),
dist.awt_flag, -- bug 8709676
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
--bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id, date_from, date_to),0)) net_amount, --bug14114547
sum(nvl(dist.base_amount,dist.amount)) withholding_tax_amount,
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
awt.tax_rate,
awt.tax_name,
'Y'
FROM fnd_lookups fl,
po_vendors v,
po_vendor_sites_all vs,
ap_tax_codes_all atc,
ap_awt_tax_rates_all awt,
fnd_document_sequences seq,
ap_invoices_all inv,
ap_invoice_lines_all line,
ap_invoice_distributions_all dist,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE vs.country = fl.lookup_code(+)
AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
AND nvl(v.employee_id,-99) = papf.person_id (+)
AND inv.vendor_id = v.vendor_id
AND v.vendor_id = vs.vendor_id
and vs.vendor_site_id = inv.vendor_site_id
-- bug 8551359 - start
--AND vs.tax_reporting_site_flag = 'Y'
AND exists (select 'x'
from po_vendor_sites_all
where vendor_id = v.vendor_id
and tax_reporting_site_flag = 'Y'
and org_id = p_rep_site_ou)
-- bug 8551359 - end
and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
-- bug 5207771: Removed org_id condition
--and inv.org_id = nvl(p_org_id,inv.org_id)
and inv.invoice_id = line.invoice_id
and dist.invoice_id = line.invoice_id
and dist.invoice_line_number = line.line_number
-- BUG 3930123 : spanugan
/*AND inv.cancelled_date is not null
AND
(
(dist.cancellation_flag is null
AND dist.accounting_date < (select distinct gl.start_date
from gl_period_statuses gl
where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
from ap_invoice_distributions_all dist1
where dist1.invoice_id = inv.invoice_id
and dist1.cancellation_flag = 'Y' )))
OR
(dist.cancellation_flag = 'Y'
AND dist.accounting_date > (select distinct gl.end_date
from gl_period_statuses gl
where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
from ap_invoice_distributions_all dist1
where dist1.invoice_id = inv.invoice_id
and dist1.cancellation_flag is null )))
)
-- END
*/
AND dist.parent_reversal_id is not null
-- bug 8496890
/*
AND dist.accounting_date > (select distinct gl.end_date
from ap_invoice_distributions dist1, gl_period_statuses gl
where gl.application_id = 101
and dist1.invoice_id = inv.invoice_id
and dist.parent_reversal_id = dist1.invoice_distribution_id
and gl.ledger_id = dist1.set_of_books_id
and dist1.accounting_date between gl.start_date and gl.end_date)
*/
AND not exists (select 1 from ap_invoice_distributions dist1
where dist1.invoice_id = inv.invoice_id
and dist.parent_reversal_id = dist1.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To))
-- bug 8496890
AND trunc(dist.accounting_date,'DD')
between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To)
AND dist.line_type_lookup_code = 'AWT'
AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
DIST.ACCRUAL_POSTED_FLAG,
DIST.CASH_POSTED_FLAG,
dist.POSTED_FLAG, inv.org_id) in ('Y','P')
AND dist.withholding_tax_code_id = atc.tax_id(+)
AND atc.name = awt.tax_name(+)
/*AND dist.accounting_date
between nvl(awt.start_date, dist.accounting_date)
and nvl(awt.end_date, dist.accounting_date)*/ -- bug 16343633
AND awt.vendor_id is null /* Ignore any Vendor Lines */
-- Bug 5207771 : Added to remove the duplicates WH lines
AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
OR (dist.awt_tax_rate_id is NULL) )
AND inv.doc_sequence_id = seq.doc_sequence_id(+)
-- Ignore any invoices which do not have 'AWT' distribution lines
AND EXISTS ( select dist2.invoice_id
from ap_invoice_distributions_all dist2
where inv.invoice_id = dist2.invoice_id
and dist2.line_type_lookup_code = 'AWT'
and dist2.withholding_tax_code_id in
-- Bug 2019586: Column name should be tax_id.
-- (select tax_code_id from ap_tax_codes
(select tax_id from ap_tax_codes_all
where vat_transaction_type = p_wht_tax_type))
GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
decode(nvl(v.employee_id,-1),-1,'01','00'),
nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
substr(v.vendor_name,1,80),
nvl(inv.base_amount,inv.invoice_amount),
decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-', null,
seq.name || '-' || to_char(inv.doc_sequence_value)),
inv.invoice_id,
inv.invoice_num,
trunc(inv.invoice_date,'DD'),
trunc(dist.accounting_date,'DD'),
dist.awt_flag, -- bug 8709676
-- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) ,
--bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) ,
--bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
-- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
--nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
substr(vs.city,1,25), --bug12649867
awt.tax_rate,
awt.tax_name,
'Y'
HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0));
-- select min(dist.withholding_tax_code_id), max(invpay.accounting_date) -- Bug 12616975
select min(dist.withholding_tax_code_id),(select max(accounting_date) from ap_invoice_payments_all where invoice_id=p_fetch_invoice_id)
into l_tax_code_id, l_accounting_date
from -- ap_invoice_payments_all invpay,
ap_invoice_distributions_all dist
where dist.invoice_id = p_fetch_invoice_id
--and invpay.invoice_id = dist.invoice_id -- Bug 12616975 takes cross product
and dist.line_type_lookup_code = 'AWT'
and ((p_parent_reversal_flag = 'N' and dist.parent_reversal_id is null)
OR (p_parent_reversal_flag = 'Y' and dist.parent_reversal_id is not null))
and not exists ( select 1 -- Bug 12616975
from ap_invoice_distributions dist1
where dist1.invoice_id = p_fetch_invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To)
)
group by withholding_tax_code_id
having (sum(decode(dist.line_type_lookup_code,'AWT',
nvl(dist.base_amount,dist.amount),0)) = p_fetch_wht_amount);
select min(dist.withholding_tax_code_id), max(invpay.accounting_date)
into l_tax_code_id, l_accounting_date
from ap_invoice_payments_all invpay,
ap_invoice_distributions_all dist
where dist.invoice_id = p_fetch_invoice_id
and invpay.invoice_id = dist.invoice_id
and invpay.INVOICE_PAYMENT_ID = dist.AWT_INVOICE_PAYMENT_ID -- Bug 12616975
and dist.line_type_lookup_code = 'AWT'
and ((p_parent_reversal_flag = 'N' and dist.parent_reversal_id is null)
OR (p_parent_reversal_flag = 'Y' and dist.parent_reversal_id is not null))
and dist.awt_invoice_payment_id = p_awt_invoice_payment_id -- Bug 12616975
and not exists ( select 1
from ap_invoice_distributions dist1
where dist1.invoice_id = p_fetch_invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To)
)
group by withholding_tax_code_id
having /*((mod(count(dist.withholding_tax_code_id),2) = 1) and*/ --Bug 3930217
(sum(decode(dist.line_type_lookup_code,'AWT',
nvl(dist.base_amount,dist.amount),0)) = p_fetch_wht_amount);
select min(withholding_tax_code_id), max(accounting_date)
into l_tax_code_id, l_accounting_date
from ap_invoice_distributions_all dist
where dist.invoice_id = p_fetch_invoice_id
and dist.line_type_lookup_code = 'AWT'
and ((p_parent_reversal_flag = 'N' and dist.parent_reversal_id is null)
OR (p_parent_reversal_flag = 'Y' and dist.parent_reversal_id is not null))
and not exists ( select 1 -- Bug 12616975
from ap_invoice_distributions dist1
where dist1.invoice_id = p_fetch_invoice_id
and dist1.parent_reversal_id = dist.invoice_distribution_id
and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
and fnd_date.canonical_to_date(P_Date_To)
)
-- Bug 5207771
-- and accounting_date = date_paid1 -- Bug 3930217 : Spanugan 23/12/2004
group by withholding_tax_code_id
having /*((mod(count(withholding_tax_code_id),2) = 1) and*/ --Bug 3930217
(sum(decode(dist.line_type_lookup_code,'AWT',
nvl(dist.base_amount,amount),0)) = p_fetch_wht_amount)
;
select invoice_num into l_invoice_num
from ap_invoices_all
where invoice_id = p_fetch_invoice_id
and legal_entity_id = nvl(p_legal_entity_id, legal_entity_id);
select awt.tax_rate, awt.tax_name
into l_tax_rate, l_tax_name
from ap_tax_codes_all atc, ap_awt_tax_rates_all awt
where atc.name = awt.tax_name(+)
and atc.tax_id = l_tax_code_id
and l_accounting_date between nvl(awt.start_date,l_accounting_date)
and nvl(awt.end_date,l_accounting_date)
and atc.org_id = awt.org_id; -- bug 8401560
select invoice_num into l_invoice_num
from ap_invoices_all
where invoice_id = p_fetch_invoice_id
and legal_entity_id = nvl(p_legal_entity_id,legal_entity_id);
fnd_file.put_line( fnd_file.log,'Selection Criteria : ' || p_pay_inv_sel );
SELECT COUNT(*)
INTO l_le_id_count
FROM je_es_modelo_190_all
WHERE legal_entity_id IS NULL;
je_es_mod_le_update.update_main;
SELECT p.currency_code,
c.precision
INTO func_curr,
func_curr_precision
FROM gl_ledgers p,
fnd_currencies_vl c
WHERE p.currency_code = c.currency_code
AND p.ledger_id = (select distinct primary_ledger_id
from gl_ledger_le_v
where legal_entity_id = p_legal_entity_id);
plsqlmsg('Deleted Existing Rows');
plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
plsqlmsg('Deleted Existing Rows');
plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
plsqlmsg('Data inserted into table JE_ES_MODELO_190_all');
plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');