[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_1099_data
( p_calling_module IN varchar2,
p_sob_id IN number,
p_tax_entity_id IN number,
p_combined_flag IN varchar2,
p_start_date IN date,
p_end_date IN date,
p_vendor_id IN number,
p_query_driver IN varchar2,
p_min_reportable_flag IN varchar2,
p_federal_reportable_flag in varchar2,
p_region varchar2) is
l_chart_of_accounts_id number;
SELECT fnd.application_column_name, gl.chart_of_accounts_id
INTO l_app_column_name, l_chart_of_accounts_id
FROM fnd_segment_attribute_values fnd, gl_sets_of_books gl
WHERE segment_attribute_type = 'GL_BALANCING'
AND fnd.attribute_value = 'Y'
AND fnd.id_flex_code = 'GL#'
AND fnd.id_flex_num = gl.chart_of_accounts_id
AND gl.set_of_books_id = p_sob_id;
'INSERT INTO ap_1099_tape_data '
|| '(vendor_id,region_code,'
|| 'misc1,misc2,misc3,misc4,misc5,'
|| ' misc6,misc7,misc8,misc9,misc10,'
|| 'misc13, misc14, misc15aNT, misc15aT, misc15b,org_id) ' -- Bug 4946930
|| 'SELECT P.vendor_id,';
|| ' select invoice_id, count(*) ip_count '
|| ' from ap_invoice_payments_all aip '
|| ' where aip.amount <> 0 '
|| ' and aip.accounting_date BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''' '
|| ' group by invoice_id '
|| ' ) ip2, '
|| '( select AI.invoice_id , nvl(AIL1.amount+AI.invoice_amount , AI.invoice_amount) as netamount ' --Bug5943123
|| ' from ap_invoices_all AI, ( select AIL.invoice_id, SUM(AIL.amount) as amount '
|| ' FROM ap_invoice_lines_all AIL ' --Bug6064614
|| ' WHERE (nvl(AIL.invoice_includes_prepay_flag,''N'') = ''N'' ' --Bug6052333
|| ' AND (AIL.line_type_lookup_code = ''PREPAY'' '
|| ' OR (AIL.line_type_lookup_code =''TAX'' '
|| ' AND AIL.prepay_invoice_id IS NOT NULL '
|| ' AND AIL.prepay_line_number IS NOT NULL))) '
|| ' OR AIL.line_type_lookup_code = ''AWT'' '
|| ' GROUP by AIL.invoice_id ) AIL1 ' --Bug5943123
|| ' where AI.invoice_id = AIL1.invoice_id (+) ) INV_NET_AMT '
|| 'WHERE ID.income_tax_region = ITR.region_short_name (+) '
|| 'AND P.vendor_id=I.vendor_id '
|| 'AND (AC.void_date is null '
|| ' OR AC.void_date NOT BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''') ' --4480766, 8925235
|| 'AND I.invoice_id=IP.invoice_id '
|| 'AND IP.invoice_id = ip2.invoice_id(+) '
|| 'AND I.invoice_id=ID.invoice_id '
|| 'AND INV_NET_AMT.invoice_id = I.invoice_id '
|| 'AND IP.accounting_date BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''' '
--|| 'AND ID.accounting_date BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''' ' --Bug 11845743 --Bug12940711
|| 'AND ID.type_1099 is not null '
---- || 'AND AC.bank_account_id = ABA.bank_account_id ' Commeted this for bug 6275528 - as in ap_checks_all
---bank_account_id is not getting stamped in R12.bank_account_anme is there so we will use it.
--|| 'AND AC.bank_account_name = ABA.bank_account_name ' --added for bug 6275528 for the aobve explained reason.
-- Commented for Bug 6604204.
|| 'AND AC.ce_bank_acct_use_id = BAU.bank_acct_use_id ' -- Bug 6604204. Please refer bug for details.
|| 'AND BAU.bank_account_id = ABA.bank_account_id ' -- Bug 6604204. Please refer bug for details.
|| 'AND AC.org_id = mo_global.get_current_org_id ' --added for bug 6275528
|| 'AND IP.check_id = AC.check_id '
|| 'AND REL.tax_entity_id = ' || p_tax_entity_id|| ' '
|| 'AND CC.chart_of_accounts_id = ' || l_chart_of_accounts_id || ' ';
select count(*) into x from ap_1099_tape_data;
end insert_1099_data;
PROCEDURE do_awt_withholding_update
( p_calling_module IN varchar2,
p_sob_id IN number,
p_tax_entity_id IN number,
p_combined_flag IN varchar2,
p_start_date IN date,
p_end_date IN date,
p_vendor_id IN number,
p_query_driver IN varchar2,
p_min_reportable_flag IN varchar2,
p_federal_reportable_flag in varchar2,
p_region varchar2) is
l_chart_of_accounts_id number;
SELECT fnd.application_column_name, gl.chart_of_accounts_id
INTO l_app_column_name, l_chart_of_accounts_id
FROM fnd_segment_attribute_values fnd, gl_sets_of_books gl
WHERE segment_attribute_type = 'GL_BALANCING'
AND fnd.attribute_value = 'Y'
AND fnd.id_flex_code = 'GL#'
AND fnd.id_flex_num = gl.chart_of_accounts_id
AND gl.set_of_books_id = p_sob_id;
'SELECT P.vendor_id,';
|| ' select invoice_id, count(*) ip_count '
|| ' from ap_invoice_payments_all aip '
|| ' where aip.amount <> 0 '
|| ' and aip.accounting_date BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''' '
|| ' group by invoice_id '
|| ' ) ip2, '
|| '( select AI.invoice_id , nvl(AIL1.amount+AI.invoice_amount , AI.invoice_amount) as netamount ' --Bug5943123
|| ' from ap_invoices_all AI, ( select AIL.invoice_id, SUM(AIL.amount) as amount '
|| ' FROM ap_invoice_lines_all AIL ' --Bug6064614
|| ' WHERE (nvl(AIL.invoice_includes_prepay_flag,''N'') = ''N'' ' --Bug6052333
|| ' AND (AIL.line_type_lookup_code = ''PREPAY'' '
|| ' OR (AIL.line_type_lookup_code =''TAX'' '
|| ' AND AIL.prepay_invoice_id IS NOT NULL '
|| ' AND AIL.prepay_line_number IS NOT NULL))) '
|| ' OR AIL.line_type_lookup_code = ''AWT'' '
|| ' GROUP by AIL.invoice_id ) AIL1 ' --Bug5943123
|| ' where AI.invoice_id = AIL1.invoice_id (+) ) INV_NET_AMT '
|| 'WHERE ID.income_tax_region = ITR.region_short_name (+) '
|| 'AND P.vendor_id=I.vendor_id '
|| 'AND (AC.void_date is null '
|| ' OR AC.void_date NOT BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''') ' --4480766, 8925235
|| 'AND I.invoice_id=IP.invoice_id '
|| 'AND IP.invoice_id = ip2.invoice_id(+) '
|| 'AND I.invoice_id=ID.invoice_id '
|| 'AND exists '
|| ' ( '
|| ' select 1 '
|| ' from ap_invoice_distributions_all AID '
|| ' where AID.invoice_id = I.invoice_id '
|| ' AND AID.type_1099 = ''MISC4'' '
|| ' ) '
|| 'AND INV_NET_AMT.invoice_id = I.invoice_id '
|| 'AND IP.accounting_date BETWEEN '''|| p_start_date || ''' AND '''|| p_end_date || ''' '
|| 'AND ID.type_1099 is not null '
---- || 'AND AC.bank_account_id = ABA.bank_account_id ' Commeted this for bug 6275528 - as in ap_checks_all
---bank_account_id is not getting stamped in R12.bank_account_anme is there so we will use it.
--|| 'AND AC.bank_account_name = ABA.bank_account_name ' --added for bug 6275528 for the aobve explained reason.
-- Commented for Bug 6604204.
|| 'AND AC.ce_bank_acct_use_id = BAU.bank_acct_use_id ' -- Bug 6604204. Please refer bug for details.
|| 'AND BAU.bank_account_id = ABA.bank_account_id ' -- Bug 6604204. Please refer bug for details.
|| 'AND AC.org_id = mo_global.get_current_org_id ' --added for bug 6275528
|| 'AND IP.check_id = AC.check_id '
|| 'AND REL.tax_entity_id = ' || p_tax_entity_id|| ' '
|| 'AND CC.chart_of_accounts_id = ' || l_chart_of_accounts_id || ' ';
update ap_1099_tape_data atd
set misc1 = decode(atd.misc1, 0, l_backup_awt_rec.misc1, atd.misc1),
misc2 = decode(atd.misc2, 0, l_backup_awt_rec.misc2, atd.misc2),
misc3 = decode(atd.misc3, 0, l_backup_awt_rec.misc3, atd.misc3),
misc5 = decode(atd.misc5, 0, l_backup_awt_rec.misc5, atd.misc5),
misc6 = decode(atd.misc6, 0, l_backup_awt_rec.misc6, atd.misc6),
misc7 = decode(atd.misc7, 0, l_backup_awt_rec.misc7, atd.misc7),
misc8 = decode(atd.misc8, 0, l_backup_awt_rec.misc8, atd.misc8),
misc9 = decode(atd.misc9, 0, l_backup_awt_rec.misc9, atd.misc9),
misc10 = decode(atd.misc10, 0, l_backup_awt_rec.misc10, atd.misc10),
misc13 = decode(atd.misc13, 0, l_backup_awt_rec.misc13, atd.misc13),
misc14 = decode(atd.misc14, 0, l_backup_awt_rec.misc14, atd.misc14),
misc15aNT = decode(atd.misc15aNT, 0, l_backup_awt_rec.misc15aNT, atd.misc15aNT),
misc15aT = decode(atd.misc15aT, 0, l_backup_awt_rec.misc15aT, atd.misc15aT),
misc15b = decode(atd.misc15b, 0, l_backup_awt_rec.misc15b, atd.misc15b)
where atd.vendor_id = l_backup_awt_rec.vendor_id
and nvl(atd.region_code, -99) = nvl(l_backup_awt_rec.region_code, -99)
and atd.misc4 > 0 ;
end do_awt_withholding_update;
SELECT pvw.vendor_name vendor_name,
TD.vendor_id vendor_id,
decode(pvw.address_line1, '', 'No Address Line 1. ') ||
decode(substr(pvw.organization_type_lookup_code, 1, 7),
'FOREIGN',
decode(pvw.country, '', 'No country. '),
nvl(decode(length(replace(replace(nvl(pvw.national_identifier,
nvl(pvw.individual_1099,
pvw.num_1099)),
'-',
''),
' ',
'')),
0,
'',
9,
'',
'TIN not 9 digits. '),
decode(ltrim(translate(nvl(pvw.national_identifier,
nvl(pvw.individual_1099, pvw.num_1099)),
'1234567890- ',
' ')),
'',
'',
'TIN contains non-numeric digit(s). ')) ||
decode(pvw.city, '', 'No city. ') ||
decode(nvl(pvw.province, pvw.state), '', 'No state. ') ||
decode(replace(replace(pvw.zip, '-', ''), ' ', ''),
'',
'No postal code. ')) current_error_text
FROM ap_1099_tape_data TD,
(SELECT P.vendor_id VENDOR_ID,
P.vendor_name VENDOR_NAME,
P.tax_reporting_name TAX_REPORTING_NAME,
p.organization_type_lookup_code ORGANIZATION_TYPE_LOOKUP_CODE,
NULL NATIONAL_IDENTIFIER,
P.individual_1099 INDIVIDUAL_1099,
P.num_1099 NUM_1099,
pvs.province PROVINCE,
pvs.address_line1 ADDRESS_LINE1,
pvs.address_line2 ADDRESS_LINE2,
pvs.address_line3 ADDRESS_LINE3,
pvs.state STATE,
pvs.city CITY,
pvs.zip ZIP,
pvs.COUNTRY COUNTRY
FROM po_vendors P,
po_vendor_sites pvs
WHERE pvs.vendor_id = P.vendor_id
AND nvl(p.vendor_type_lookup_code, 'DUMMY') <> 'EMPLOYEE' /*Bug 9247826*/
AND (NVL(pvs.tax_reporting_site_flag,'N') = 'Y' OR
(pvs.vendor_site_code =
(select min(vendor_site_code)
from po_vendor_sites pvs2
where pvs2.vendor_id = pvs.vendor_id
and nvl(inactive_date, sysdate + 9000) =
(select max(decode(inactive_date,
'',
sysdate + 9000,
inactive_date))
from po_vendor_sites pvs3
where pvs3.vendor_id = pvs.vendor_id))
AND not exists
(SELECT 'A tax reporting site exists for this vendor'
FROM po_vendor_sites pvs4
WHERE NVL(pvs4.tax_reporting_site_flag,'N') = 'Y'
AND pvs4.vendor_id = pvs.vendor_id)))
UNION ALL
SELECT distinct PV.vendor_id VENDOR_ID,
Pv.vendor_name VENDOR_NAME,
Pv.tax_reporting_name TAX_REPORTING_NAME,
pv.organization_type_lookup_code ORGANIZATION_TYPE_LOOKUP_CODE,
papf.national_identifier NATIONAL_IDENTIFIER,
NULL INDIVIDUAL_1099,
NULL NUM_1099,
NULL PROVINCE,
CASE pvs.vendor_site_code when 'HOME' then
per_addr.address_line1
when 'OFFICE' then
per_loc.address_line_1
when 'PROVISIONAL' then
per_addr_prov.address_line1
else case when per_addr.address_id is NULL then
per_loc.address_line_1
else
per_addr.address_line1
end
end ADDRESS_LINE1,
CASE pvs.vendor_site_code when 'HOME' then
per_addr.address_line2
when 'OFFICE' then
per_loc.address_line_2
when 'PROVISIONAL' then
per_addr_prov.address_line2
else case when per_addr.address_id is NULL then
per_loc.address_line_2
else
per_addr.address_line2
end
end ADDRESS_LINE2,
CASE pvs.vendor_site_code when 'HOME' then
per_addr.address_line3
when 'OFFICE' then
per_loc.address_line_3
when 'PROVISIONAL' then
per_addr_prov.address_line3
else case when per_addr.address_id is NULL then
per_loc.address_line_3
else
per_addr.address_line3
end
end ADDRESS_LINE3,
CASE pvs.vendor_site_code when 'HOME' then
case per_addr.style when 'CA' then
''
when 'CA_GLB' then
''
else
NVL(per_addr.region_2, '')
end
when 'OFFICE' then
case per_loc.style when 'CA' then
''
when 'CA_GLB' then
''
else
NVL(per_loc.region_2, '')
end
when 'PROVISIONAL' then
case per_addr_prov.style when 'CA' then
''
when 'CA_GLB' then
''
else
NVL(per_addr_prov.region_2, '')
end
else
case when per_addr.address_id is NULL then
case per_loc.style when 'CA' then
''
when 'CA_GLB' then
''
else
NVL(per_loc.region_2, '')
end
else
case per_addr.style when 'CA' then
''
when 'CA_GLB' then
''
else
NVL(per_addr.region_2, '')
end
end
end STATE,
CASE pvs.vendor_site_code when 'HOME' then
per_addr.town_or_city
when 'OFFICE' then
per_loc.town_or_city
when 'PROVISIONAL' then
per_addr_prov.town_or_city
else
case when per_addr.address_id is NULL then
per_loc.town_or_city
else
per_addr.town_or_city
end
end CITY,
CASE pvs.vendor_site_code when 'HOME' then
per_addr.postal_code
when 'OFFICE' then
per_loc.postal_code
when 'PROVISIONAL' then
per_addr_prov.postal_code
else
case when per_addr.address_id is NULL then
per_loc.postal_code
else
per_addr.postal_code
end
end ZIP,
CASE pvs.vendor_site_code when 'HOME' then
per_addr.country
when 'OFFICE' then
per_loc.country
when 'PROVISIONAL' then
per_addr_prov.country
else
case when per_addr.address_id is NULL then
per_loc.country
else
per_addr.country
end
end COUNTRY
FROM HR_LOCATIONS per_loc,
PER_ADDRESSES per_addr,
PER_ALL_ASSIGNMENTS_F per_assgn,
PER_ADDRESSES per_addr_prov,
(SELECT distinct papf1.person_id, papf1.national_identifier
FROM PER_ALL_PEOPLE_F papf1
WHERE NVL(papf1.effective_end_date,sysdate +9000) =
(SELECT MAX(DECODE(papf2.effective_end_date,'',sysdate+9000,papf2.effective_end_date))
FROM PER_ALL_PEOPLE_F papf2
WHERE papf1.person_id=papf2.person_id)) papf,
PO_VENDOR_SITES pvs,
PO_VENDORS pv
WHERE pv.employee_id = papf.person_id
AND nvl(pv.vendor_type_lookup_code, 'DUMMY') = 'EMPLOYEE' /*Bug 9247826*/
AND pv.vendor_id = pvs.vendor_id
AND pv.employee_id = per_addr.person_id(+)
AND per_addr.primary_flag(+) = 'Y'
AND SYSDATE BETWEEN per_addr.date_from(+) AND
NVL(per_addr.date_to(+), SYSDATE + 1)
AND pv.employee_id = per_addr_prov.person_id(+)
AND nvl(per_addr_prov.address_type(+),'N') = 'M'
AND SYSDATE BETWEEN per_addr_prov.date_from(+) AND
NVL(per_addr_prov.date_to(+), SYSDATE + 1)
AND pv.employee_id = per_assgn.person_id(+)
AND per_assgn.location_id = per_loc.location_id(+)
AND per_assgn.assignment_type(+) = 'E'
AND per_assgn.primary_flag(+) = 'Y'
AND (TRUNC(SYSDATE) BETWEEN per_assgn.effective_start_date(+) AND
per_assgn.effective_end_date(+))
AND (NVL(pvs.tax_reporting_site_flag,'N') = 'Y' OR
(pvs.vendor_site_code =
(select min(vendor_site_code)
from po_vendor_sites pvs2
where pvs2.vendor_id = pvs.vendor_id
and nvl(inactive_date, sysdate + 9000) =
(select max(decode(inactive_date,
'',
sysdate + 9000,
inactive_date))
from po_vendor_sites pvs3
where pvs3.vendor_id = pvs.vendor_id)) AND not exists
(SELECT 'A tax reporting site exists for this vendor'
FROM po_vendor_sites pvs4
WHERE NVL(pvs4.tax_reporting_site_flag,'N') = 'Y'
AND pvs4.vendor_id = pvs.vendor_id)))) pvw /*Bug 8768541 :added the inline view for emp address from HR*/
where pvw.vendor_id = TD.vendor_id
GROUP BY pvw.vendor_name,
TD.vendor_id,
nvl(pvw.national_identifier, nvl(pvw.individual_1099, pvw.num_1099)),
pvw.tax_reporting_name,
pvw.organization_type_lookup_code,
pvw.country,
pvw.address_line1,
pvw.address_line2,
pvw.address_line3,
pvw.city,
pvw.province,
pvw.state,
pvw.zip;
UPDATE ap_1099_tape_data
SET
misc1 =null,
misc2 =null,
misc3 =null,
misc4 =null,
misc5 =null,
misc6 =null,
misc7 =null,
misc15aT =null,
misc15b =null,
misc8 =null,
misc9 =null,
misc10 =null,
misc13 =null,
misc14 =null,
misc15aNT =null
WHERE vendor_id=rec_1099_curs.vendor_id;