[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT glev.ledger_id
--, glp.year_start_date, glp.end_date --Commented as per bug#13442170
,glev.currency_code
INTO cp_set_of_books_id
-- ,cp_year_start_date --Commented as per bug#13442170
-- ,cp_year_end_date --Commented as per bug#13442170
,cp_currencycode
FROM gl_ledger_le_v glev
-- , gl_periods glp --Commented as per bug#13442170
WHERE glev.legal_entity_id = p_legal_entity_id
-- AND glev.period_set_name = glp.period_set_name --Commented as per bug#13442170
-- AND glev.accounted_period_type = glp.period_type --Commented as per bug#13442170
-- AND glp.period_year = p_year --Commented as per bug#13442170
-- AND glp.adjustment_period_flag = 'Y' --Commented as per bug#13442170
AND glev.relationship_enabled_flag = 'Y'
AND glev.ledger_category_code = 'PRIMARY';
SELECT NVL (xler.registration_number, '') commercial_number
INTO cp_comm_num
FROM xle_registrations xler,
xle_jurisdictions_b xlej,
xle_entity_profiles xlee
WHERE xlej.jurisdiction_id = xler.jurisdiction_id
AND xlej.legislative_cat_code = 'COMMERCIAL_LAW'
AND xler.source_id = xlee.legal_entity_id
AND xler.source_table = 'XLE_ENTITY_PROFILES'
AND xlee.legal_entity_id = p_legal_entity_id;
SELECT PRECISION
INTO cp_precision
FROM fnd_currencies
WHERE currency_code = lc_functcurr;
SELECT aps.vendor_name
, aps.vendor_id ,
NVL (papf.national_identifier,
NVL (aps.individual_1099, aps.num_1099)
) taxpayer_id,
aps.segment1
, apss.vendor_site_code ,
apss.vendor_site_id ,
nvl(apss.vat_registration_num,zpt.rep_registration_number) vat_registration_num, --Bug 14136822
apss.address_line1 ,
apss.address_line2 ,
apss.address_line3 ,
apss.address_line1
|| DECODE (apss.address_line2,
NULL, NULL,
', ' || apss.address_line2
)
|| DECODE (apss.address_line3,
NULL, NULL,
', ' || apss.address_line3
) supplier_address,
apss.city
, apss.zip ,
apss.province
, apss.country ,
inv.invoice_id ,
NVL (inv.base_amount, inv.invoice_amount) invoice_amount,
NVL (aip.payment_base_amount, aip.amount) amount_paid,
inv.invoice_num
,dist.exempt_amount
,inv.invoice_type_lookup_code
FROM ap_supplier_sites_all apss,
ap_suppliers aps,
(SELECT DISTINCT person_id, national_identifier
FROM per_all_people_f) papf
,(SELECT SUM (DECODE (dist1.line_type_lookup_code,
'ITEM', NVL (SIGN (dist1.awt_group_id) - 1, 1)
* NVL (dist1.base_amount, dist1.amount),
'PREPAY', NVL (SIGN (dist1.awt_group_id) - 1, 1)
* NVL (dist1.base_amount, dist1.amount),
0
)
) exempt_amount
,dist1.invoice_id
FROM ap_invoice_distributions_all dist1,ap_invoices_all inv1
WHERE dist1.invoice_id = inv1.invoice_id
AND dist1.line_type_lookup_code = 'ITEM'
-- AND dist1.awt_group_id IS NULL
group by dist1.invoice_id)dist
,ap_invoices_all inv,
ap_invoice_payments_all aip,
ap_checks_all checks,
zx_party_tax_profile zpt --Bug 14136822
WHERE inv.legal_entity_id = p_legal_entity_id
AND inv.invoice_id = dist.invoice_id
AND inv.invoice_id = aip.invoice_id
AND ( aip.posted_flag IN ('Y', 'P')
OR aip.cash_posted_flag IN ('Y', 'P')
OR aip.accrual_posted_flag IN ('Y', 'P')
)
AND inv.vendor_id = aps.vendor_id
AND inv.vendor_site_id = apss.vendor_site_id
AND aps.vendor_id = apss.vendor_id
AND apss.party_site_id = zpt.party_id --Bug 14136822
AND zpt.party_type_code = 'THIRD_PARTY_SITE' --Bug 14136822
AND NVL (aps.employee_id, -99) = papf.person_id(+)
AND aip.check_id = checks.check_id
AND checks.void_date IS NULL
AND aip.accounting_date BETWEEN cp_year_start_date AND cp_year_end_date
AND aip.invoice_payment_id =
(SELECT MAX (aip_sub.invoice_payment_id)
FROM ap_invoice_payments_all aip_sub
WHERE aip_sub.invoice_id = inv.invoice_id
AND aip_sub.accounting_date BETWEEN cp_year_start_date
AND cp_year_end_date
GROUP BY aip_sub.invoice_id)
AND EXISTS (
SELECT 1
FROM ap_invoice_payments_all aip_sub2,
ap_invoice_distributions_all dist
WHERE aip_sub2.invoice_payment_id =
DECODE (dist.line_type_lookup_code,
'AWT', dist.awt_invoice_payment_id,
aip_sub2.invoice_payment_id
)
AND aip_sub2.accounting_date BETWEEN cp_year_start_date
AND cp_year_end_date
)
GROUP BY aps.vendor_name,
aps.vendor_id,
NVL (papf.national_identifier,
NVL (aps.individual_1099, aps.num_1099)
),
aps.segment1,
apss.vendor_site_code,
apss.vendor_site_id,
nvl(apss.vat_registration_num,zpt.rep_registration_number), --Bug 14136822
apss.address_line1,
apss.address_line2,
apss.address_line3,
apss.address_line1
|| DECODE (apss.address_line2,
NULL, NULL,
', ' || apss.address_line2
)
|| DECODE (apss.address_line3,
NULL, NULL,
', ' || apss.address_line3
),
apss.city,
apss.zip,
apss.province,
apss.country,
inv.invoice_id,
NVL (inv.base_amount, inv.invoice_amount),
NVL (aip.payment_base_amount, aip.amount),
inv.invoice_num
,dist.exempt_amount
,inv.invoice_type_lookup_code
ORDER BY case p_order_by --- bug 14189687
when 'aps.vendor_name' then aps.vendor_name
when 'vat_registration_num' then vat_registration_num
when 'taxpayer_id' then taxpayer_id
end ;
SELECT count(1)rec_count
FROM ap_awt_group_taxes_all awt
WHERE awt.GROUP_ID IN (
SELECT DISTINCT dist.awt_origin_group_id
FROM ap_invoice_distributions_all dist,
ap_invoices_all inv
WHERE dist.invoice_id = inv.invoice_id
AND dist.line_type_lookup_code ='AWT'
AND inv.invoice_id = p_invoice_id
);
SELECT SUM (NVL (dist.base_amount, dist.amount))
INTO ln_ex_amount1
FROM ap_invoice_distributions_all dist
WHERE dist.invoice_id = cur_withholding_extract_rec.invoice_id
AND dist.line_type_lookup_code in ('ITEM','ACCRUAL')
AND dist.pay_awt_group_id IS NULL;
SELECT SUM (NVL (dist.base_amount, dist.amount))
INTO ln_ex_amount2
FROM ap_invoice_distributions_all dist
WHERE dist.invoice_id = cur_withholding_extract_rec.invoice_id
AND dist.line_type_lookup_code in ('ITEM', 'ACCRUAL') --bug14369763
AND nvl(dist.pay_awt_group_id, nvl(dist.awt_group_id,dist.awt_origin_group_id)) IN ( --bug14369763
SELECT awgt_sub.GROUP_ID
FROM ap_tax_codes_all atc_sub,
ap_awt_tax_rates_all awt_sub,
ap_awt_group_taxes_all awgt_sub
WHERE awgt_sub.tax_name = awt_sub.tax_name
AND atc_sub.NAME = awt_sub.tax_name
AND awt_sub.tax_rate = 0);
SELECT SUM (NVL (dist.base_amount, dist.amount))
INTO ln_tax_amount_one
FROM ap_invoice_distributions_all dist
WHERE dist.invoice_id = cur_withholding_extract_rec.invoice_id
AND dist.line_type_lookup_code = 'ITEM'
AND dist.awt_group_id IS NULL;
SELECT SUM (NVL (dist.base_amount, dist.amount))
INTO ln_tax_amount_two
FROM ap_invoice_distributions_all dist
, ap_awt_group_taxes_all awt
WHERE dist.invoice_id = invoice_id
AND NVL(dist.pay_awt_group_id,nvl(dist.awt_group_id, dist.awt_origin_group_id)) = awt.group_id --bug14369763
AND dist.org_id = awt.org_id
AND dist.invoice_id = cur_withholding_extract_rec.invoice_id
AND dist.line_type_lookup_code in ('ITEM','ACCRUAL') --bug14369763
AND NVL(dist.pay_awt_group_id, nvl(dist.awt_group_id, dist.awt_origin_group_id)) NOT IN ( --bug14369763
SELECT awgt_sub.GROUP_ID
FROM ap_tax_codes_all atc_sub,
ap_awt_tax_rates_all awt_sub,
ap_awt_group_taxes_all awgt_sub
WHERE awgt_sub.tax_name = awt_sub.tax_name
AND atc_sub.NAME = awt_sub.tax_name
AND awt_sub.tax_rate = 0);
select sum(amount),
sum(nvl(payment_base_amount, amount))
into l_amount_etrd,
l_amount_paid
from ap_invoice_payments
where invoice_id = cur_withholding_extract_rec.invoice_id
and accounting_date between cp_year_start_date AND cp_year_end_date
group by invoice_id;
select sum(decode(dist.line_type_lookup_code,'AWT',0,nvl(dist.base_amount,dist.amount))),
sum(decode(dist.line_type_lookup_code,'AWT',0,dist.amount)),
sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)),
sum(decode(dist.line_type_lookup_code,'AWT',dist.amount,0))
into l_inv_gctot_base,
l_inv_gctot_etrd,
l_inv_whtot_base,
l_inv_whtot_etrd
from ap_invoice_distributions dist
where dist.invoice_id = cur_withholding_extract_rec.invoice_id
and exists (select 1
from ap_invoice_payments pay
where pay.invoice_payment_id = decode(dist.line_type_lookup_code,'AWT',
dist.awt_invoice_payment_id,pay.invoice_payment_id)
and pay.accounting_date between cp_year_start_date AND cp_year_end_date);
select decode(inv.invoice_currency_code,inv.payment_currency_code,l_inv_gctot_etrd,l_inv_gctot_base),
decode(inv.invoice_currency_code,inv.payment_currency_code,l_inv_whtot_etrd,l_inv_whtot_base),
decode(inv.invoice_currency_code,inv.payment_currency_code, l_amount_etrd, l_amount_paid)
into l_inv_gctot_amt,
l_inv_whtot_amt,
l_pay_amount
from ap_invoices inv
where inv.invoice_id = cur_withholding_extract_rec.invoice_id;
SELECT awt.group_id
into ln_awt_group_id
FROM ap_awt_group_taxes_all awt
WHERE awt.GROUP_ID IN (
SELECT DISTINCT dist.awt_origin_group_id
FROM ap_invoice_distributions_all dist,
ap_invoices_all inv
WHERE dist.invoice_id = inv.invoice_id
AND dist.line_type_lookup_code ='AWT'
AND inv.invoice_id = cur_withholding_extract_rec.invoice_id
);
select sum(nvl(amount,0))
into ln_gross_amount
from
( select sum(nvl(dist.base_amount,nvl(dist.amount,0))) amount
from ap_invoice_distributions dist
where dist.invoice_id = cur_withholding_extract_rec.invoice_id
and dist.line_type_lookup_code <> 'AWT'
and nvl(dist.pay_awt_group_id,nvl(dist.awt_group_id,dist.awt_origin_group_id)) = ln_awt_group_id
UNION
select sum(nvl(c.base_amount,nvl(c.amount,0))) amount
from ap_invoice_distributions dist,
ap_invoice_distributions c
where dist.invoice_id = cur_withholding_extract_rec.invoice_id
and c.invoice_id = cur_withholding_extract_rec.invoice_id
and dist.line_type_lookup_code <> 'AWT'
and c.CHARGE_APPLICABLE_TO_DIST_ID = dist.invoice_distribution_id
and nvl(dist.pay_awt_group_id,nvl(dist.awt_group_id,dist.awt_origin_group_id)) = ln_awt_group_id
) chrg;
SELECT awt.tax_rate, awt.tax_name,
ROUND (SUM (NVL (dist.base_amount, dist.amount)) * (-1),
cp_precision
) wth_amount,
aps.vendor_name
INTO ln_irpef_rate, lc_irpef_taxname,
ln_irpef_wthamount,
lc_irpef_vendor_name
FROM ap_invoice_distributions dist,
ap_tax_codes atc,
ap_invoices inv1,
ap_awt_tax_rates awt,
ap_suppliers aps
WHERE dist.invoice_id = inv1.invoice_id
AND inv1.invoice_id = cur_withholding_extract_rec.invoice_id
AND dist.line_type_lookup_code = atc.tax_type
AND dist.line_type_lookup_code = 'AWT'
AND atc.NAME = awt.tax_name
AND awt.tax_rate_id = dist.awt_tax_rate_id
AND awt.vendor_id IS NULL
AND atc.awt_vendor_id = aps.vendor_id
AND aps.vendor_name = g_irpef
AND aps.vendor_type_lookup_code = 'TAX AUTHORITY'
GROUP BY awt.tax_rate, awt.tax_name, aps.vendor_name;
SELECT awt.tax_rate, awt.tax_name,
ROUND (SUM (NVL (dist.base_amount, dist.amount)) * (-1),
cp_precision
) wth_amount,
aps.vendor_name
INTO ln_inps_rate, lc_inps_taxname,
ln_inps_wthamount,
lc_inps_vendor_name
FROM ap_invoice_distributions dist,
ap_tax_codes atc,
ap_invoices inv1,
ap_awt_tax_rates awt,
ap_suppliers aps
WHERE dist.invoice_id = inv1.invoice_id
AND inv1.invoice_id = cur_withholding_extract_rec.invoice_id
AND dist.line_type_lookup_code = atc.tax_type
AND dist.line_type_lookup_code = 'AWT'
AND atc.NAME = awt.tax_name
AND awt.tax_rate_id = dist.awt_tax_rate_id
AND awt.vendor_id IS NULL
AND atc.awt_vendor_id = aps.vendor_id
AND aps.vendor_name = g_inps
AND aps.vendor_type_lookup_code = 'TAX AUTHORITY'
GROUP BY awt.tax_rate, awt.tax_name, aps.vendor_name;
SELECT DISTINCT apinv_sub.invoice_num
INTO ln_inv_num
FROM ap_invoices_all apinv_sub,
ap_invoice_distributions_all apdist1_sub,
ap_invoice_distributions_all apdist2_sub
WHERE apdist2_sub.invoice_id = cur_withholding_extract_rec.invoice_id
AND apdist2_sub.invoice_distribution_id =
apdist1_sub.prepay_distribution_id
AND apdist1_sub.invoice_id = apinv_sub.invoice_id
AND apdist1_sub.reversal_flag <> 'Y';
INSERT INTO JG_ZZ_VAT_TRX_GT
(jg_info_v1, jg_info_n1, jg_info_v2, jg_info_v3, jg_info_v4,
jg_info_n2, jg_info_v5, jg_info_v6, jg_info_v7, jg_info_v8,
jg_info_v9, jg_info_v10, jg_info_v11, jg_info_v12, jg_info_v13,
jg_info_n3, jg_info_n4, jg_info_n5, jg_info_v14, jg_info_n6,
jg_info_n7, jg_info_n8, jg_info_n9, jg_info_v15, jg_info_v16,
jg_info_n10, jg_info_n11, jg_info_n12, jg_info_n13, jg_info_n14
,jg_info_n15,jg_info_v17,jg_info_v18)
VALUES(
cur_withholding_extract_rec.vendor_name
,cur_withholding_extract_rec.vendor_id
,cur_withholding_extract_rec.taxpayer_id
,cur_withholding_extract_rec.segment1
,cur_withholding_extract_rec.vendor_site_code
,cur_withholding_extract_rec.vendor_site_id
,cur_withholding_extract_rec.vat_registration_num
,cur_withholding_extract_rec.address_line1
,cur_withholding_extract_rec.address_line2
,cur_withholding_extract_rec.address_line3
,cur_withholding_extract_rec.supplier_address
,cur_withholding_extract_rec.city
,cur_withholding_extract_rec.zip
,cur_withholding_extract_rec.province
,cur_withholding_extract_rec.country
,cur_withholding_extract_rec.invoice_id
,cur_withholding_extract_rec.invoice_amount
,cur_withholding_extract_rec.amount_paid
,ln_inv_num
,ln_inps_wthamount
,ln_irpef_wthamount
,ln_inps_rate
,ln_irpef_rate
,lc_inps_taxname
,lc_irpef_taxname
,ln_net_amount
,ln_ratio_paid
,ln_gross_amount
-- ,cur_withholding_extract_rec.amount_paid
-- ,ROUND( (cur_withholding_extract_rec.amount_paid +ln_inps_wthamount+ln_irpef_wthamount )
-- /DECODE(ln_ex_amount,0,1,decode(cur_withholding_extract_rec.exempt_amount,0,1,cur_withholding_extract_rec.exempt_amount)),10)
-- ,ROUND (ROUND( (cur_withholding_extract_rec.amount_paid +ln_inps_wthamount+ln_irpef_wthamount )
-- /DECODE(ln_ex_amount,0,1,decode(cur_withholding_extract_rec.exempt_amount,0,1,cur_withholding_extract_rec.exempt_amount)),10)
--*DECODE(ln_ex_amount,0,1,decode(cur_withholding_extract_rec.exempt_amount,0,1,cur_withholding_extract_rec.exempt_amount)), cp_precision)
,ln_ex_amount
,ln_tax_amount
,p_legal_entity_id
,lc_inps_vendor_name
,lc_irpef_vendor_name
);
fnd_file.put_line(fnd_file.log,'Unable to insert Record.'||SQLERRM);