[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
07-Nov-2005 sudedas 115.102 4391218 Updated Format_Record function , updated with 2 new input params ,
pay_us_mmrf_w2_format_record.format_W2_RW_record
17-Feb-2006 sudedas 115.103 4425800 Added Functions Get_Employee_Count and Get_Total_Wages
30-May-2006 sackumar 115.104 5089997 Added Functions Get_Employee_Count_Monthwise and Get_Wages.
14-Jun-2006 sackumar 115.105 5089997 Modified get_wages to get the workers comp values..
17-Aug-2006 sudedas 115.106 5256745 Updated Format_Record Function with 2 Optional Input
Parameters p_input_43 and p_input_44 and
Updated pay_us_mmrf_w2_format_record.format_W2_RW_record.
22-Nov-2006 sudedas 115.107 5640748 Enhanced Get_Total_Wages Function.
01-Dec-2006 sudedas 115.108 Changed Cursors cur_employee_count and cur_summed_balance
in Function Get_Total_Wages (Added Jurisdiction Check)
25-Jun-2007 sjawid 115.109 5621099 Added function GET_SUI_WAGES.
07-Dec-2007 vmkulkar 115.110 6644795 Added call to format_W2_RV_record
6648007 get_item_data can now handle 'CS_PERSON' used
for capturing contact persons title.
18-Feb-2008 sjawid 115.111 6677736 Modified function GET_SUI_WAGES to ignore
-ve Wages employees for Florida SQWL xml format.
============================================================================*/
-- Global Variable
g_number NUMBER;
SELECT fdi.user_entity_id
FROM ff_database_items fdi,
ff_user_entities fue
WHERE fue.legislation_code = 'US'
AND fue.user_entity_id = fdi.user_entity_id
AND fdi.user_name = c_user_name;
SELECT target.value
FROM ff_archive_item_contexts con2,
ff_contexts fc2,
ff_archive_items target
WHERE target.user_entity_id = c_user_entity_id
AND target.context1 = to_char(c_assignment_action_id)
/* context assignment action id */
AND fc2.context_name = 'TAX_UNIT_ID'
and con2.archive_item_id = target.archive_item_id
and con2.context_id = fc2.context_id
and ltrim(rtrim(con2.context)) = to_char(c_tax_unit_id);
SELECT target.value
FROM ff_archive_item_contexts con2,
ff_archive_item_contexts con3,
ff_contexts fc2,
ff_contexts fc3,
ff_archive_items target
WHERE target.user_entity_id = c_user_entity_id
AND target.context1 = to_char(c_assignment_action_id)
/* context assignment action id */
AND fc2.context_name = 'TAX_UNIT_ID'
and con2.archive_item_id = target.archive_item_id
and con2.context_id = fc2.context_id
and ltrim(rtrim(con2.context)) = to_char(c_tax_unit_id)
/*context of tax_unit_id */
and fc3.context_name = 'JURISDICTION_CODE'
and con3.archive_item_id = target.archive_item_id
and con3.context_id = fc3.context_id
and substr(con3.context,1,2) = substr(c_jurisdiction_code,1,2);
SELECT 'Y'
FROM ff_formulas_f ff,
pay_magnetic_blocks pmb,
pay_magnetic_records pmr
WHERE pmb.report_format = c_report_format
AND pmr.magnetic_block_id = pmb.magnetic_block_id
AND pmr.formula_id = ff.formula_id
AND ff.formula_name = c_formula_name;
select substr(translate(upper(o.short_name),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
{["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||
select substr(translate(upper(o.short_name),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
{["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||
select substr(translate(upper(o.short_name),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
{["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||
select substr(translate(upper(o.short_name),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~`!@#$%^\&*()_-+=|\}]
{["'':;?/>.<, ', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),1,6)||
SELECT to_number(UE.creator_id)
FROM ff_user_entities UE,
ff_database_items DI
WHERE DI.user_name = p_db_item_name
AND UE.user_entity_id = DI.user_entity_id
AND Ue.creator_type = 'B'
AND UE.legislation_code = 'US';
select add_months(effective_date,-12)+1,effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = c_assignment_action_id;
select '1' from pay_balance_feeds_f pbf,
pay_balance_types pbt
where pbf.balance_type_id = pbt.balance_type_id
and pbt.balance_name = c_balance_name
and pbf.effective_start_date <= c_end_date
and pbf.effective_end_date >= c_start_date;
select count(*)
into lv_employee_count
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = p_payroll_action_id
and paa.tax_unit_id = p_tax_unit_id
and ppa.payroll_action_id = paa.payroll_action_id ;
Select paa.assignment_action_id
from pay_assignment_actions paa
where payroll_action_id = cur_payroll_action_id
and tax_unit_id = cur_tax_unit_id;
Select fai.value
into lv_employee_count
from ff_archive_items fai,
ff_database_items fdi
where fdi.user_name = p_database_item_name /*eg A_SQWL_MONTH1_COUNT */
and fai.user_entity_id = fdi.user_entity_id
and fai.context1 = i.assignment_action_id ;
SELECT count(*)
FROM ff_archive_item_contexts faic,
ff_archive_items fai,
ff_database_items fdi,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and fdi.user_name = 'A_STATE_ABBREV'
and fdi.user_entity_id = fai.user_entity_id
and fai.archive_item_id = faic.archive_item_id
and fai.context1 = paa.assignment_action_id
and fai.value = p_state
and paa.tax_unit_id = p_tax_unit_id
and paa.action_status = 'C'
and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
'A_W2_STATE_WAGES',
paa.tax_unit_id,
faic.context , 2),0) > 0
and not exists
(
select 'x'
from hr_organization_information hoi
WHERE hoi.organization_id = paa.tax_unit_id
and hoi.org_information_context ='1099R Magnetic Report Rules'
) ;
SELECT fdi1.user_name,
sum(to_number(nvl(fai1.value,'0')))
FROM ff_archive_item_contexts faic,
ff_archive_items fai,
ff_database_items fdi,
pay_assignment_actions paa,
pay_payroll_actions ppa,
ff_archive_items fai1,
ff_database_items fdi1,
ff_archive_item_contexts faic1,
ff_contexts fc,
pay_us_states pus
WHERE
ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and fdi.user_name = 'A_STATE_ABBREV'
and fdi.user_entity_id = fai.user_entity_id
and fai.archive_item_id = faic.archive_item_id
and fai.context1 = paa.assignment_action_id
and fai.value = p_state
and paa.tax_unit_id = p_tax_unit_id
and paa.action_status = 'C'
and nvL(hr_us_w2_rep.get_w2_arch_bal(paa.assignment_action_id,
'A_W2_STATE_WAGES',
paa.tax_unit_id,
faic.context , 2),0) > 0
and not exists
(
select 'x'
from hr_organization_information hoi
WHERE hoi.organization_id = paa.tax_unit_id
and hoi.org_information_context ='1099R Magnetic Report Rules'
)
and fdi1.user_name in (
'A_SIT_WITHHELD_PER_JD_GRE_YTD',
'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD',
'A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD',
'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_YTD',
'A_W2_STATE_PICKUP_PER_GRE_YTD')
and fdi1.user_entity_id = fai1.user_entity_id
and fai1.context1 = paa.assignment_action_id
and fai1.archive_item_id = faic1.archive_item_id
and (
(
faic1.context_id = fc.context_id
and fc.context_name = 'JURISDICTION_CODE'
and substr(faic1.context,1,2) = pus.state_code
and pus.state_abbrev = p_state
)
or
not exists (select 'x'
from ff_archive_items fai2,
ff_archive_item_contexts faic2,
ff_contexts fc2
where fai2.user_entity_id = fdi1.user_entity_id
and fai2.context1 = fai1.context1
and fai2.archive_item_id = fai1.archive_item_id
and fai2.archive_item_id = faic2.archive_item_id
and faic2.context_id = fc2.context_id
and fc2.context_name = 'JURISDICTION_CODE')
)
group by fdi1.user_name ;
select sum(to_number(nvl(fai.value, '0')) - to_number(nvl(fai1.value, '0')))
into lv_total_wages
from pay_payroll_actions ppa,
pay_assignment_actions paa,
ff_archive_items fai,
ff_archive_items fai1,
ff_database_items fdi,
ff_database_items fdi1
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = p_tax_unit_id
and fai.context1 = paa.assignment_action_id
and fai.user_entity_id = fdi.user_entity_id
and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
and fai1.context1 = fai.context1
and fai1.user_entity_id = fdi1.user_entity_id
and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD' ;
select nvl(sum(to_number(nvl(fai.value, '0'))
- to_number(nvl(fai1.value, '0'))
- to_number(nvl(fai2.value, '0'))
),0)
into p_excess_wages
from pay_payroll_actions ppa,
pay_assignment_actions paa,
ff_archive_items fai,
ff_archive_items fai1,
ff_archive_items fai2,
ff_database_items fdi,
ff_database_items fdi1,
ff_database_items fdi2
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = p_tax_unit_id
and fai.context1 = paa.assignment_action_id
and fai.user_entity_id = fdi.user_entity_id
and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
and fai1.context1 = fai.context1
and fai1.user_entity_id = fdi1.user_entity_id
and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD'
and fai2.context1 = fai1.context1
and fai2.user_entity_id = fdi2.user_entity_id
and fdi2.user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD' ;
select nvl(sum(to_number(nvl(fai.value, '0'))),0)
into p_withholding
from pay_payroll_actions ppa,
pay_assignment_actions paa,
ff_archive_items fai,
ff_database_items fdi
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = p_tax_unit_id
and fai.context1 = paa.assignment_action_id
and fai.user_entity_id = fdi.user_entity_id
and fdi.user_name = 'A_SIT_WITHHELD_PER_JD_GRE_QTD';
select nvl(sum(to_number(nvl(fai.value, '0'))
+ to_number(nvl(fai1.value, '0'))
+ to_number(nvl(fai2.value, '0'))
),0)
into p_workerscomp
from pay_payroll_actions ppa,
pay_assignment_actions paa,
ff_archive_items fai,
ff_archive_items fai1,
ff_archive_items fai2,
ff_database_items fdi,
ff_database_items fdi1,
ff_database_items fdi2
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = p_tax_unit_id
and fai.context1 = paa.assignment_action_id
and fai.user_entity_id = fdi.user_entity_id
and fdi.user_name = 'A_WORKERS_COMPENSATION2_ER_PER_JD_GRE_QTD'
and fai1.context1 = fai.context1
and fai1.user_entity_id = fdi1.user_entity_id
and fdi1.user_name = 'A_WORKERS_COMP2_WITHHELD_PER_JD_GRE_QTD'
and fai2.context1 = fai1.context1
and fai2.user_entity_id = fdi2.user_entity_id
and fdi2.user_name = 'A_WORKERS_COMP_WITHHELD_PER_JD_GRE_QTD' ;
select nvl(sum(to_number(nvl(fai.value, '0'))),0),
nvl(sum(to_number(nvl(fai1.value, '0'))),0),
nvl(sum(to_number(nvl(fai2.value, '0'))),0),
nvl(sum(to_number(nvl(fai3.value, '0'))),0)
into p_sui_subj,
p_sui_pre_tax,
p_sui_taxable,
p_sui_gross
from pay_payroll_actions ppa,
pay_assignment_actions paa,
ff_archive_items fai,
ff_archive_items fai1,
ff_archive_items fai2,
ff_archive_items fai3,
ff_database_items fdi,
ff_database_items fdi1,
ff_database_items fdi2,
ff_database_items fdi3
where ppa.payroll_action_id = p_payroll_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = p_tax_unit_id
and fai.context1 = paa.assignment_action_id
and fai.user_entity_id = fdi.user_entity_id
and fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD'
and fai1.context1 = fai.context1
and fai1.user_entity_id = fdi1.user_entity_id
and fdi1.user_name = 'A_SUI_ER_PRE_TAX_REDNS_PER_JD_GRE_QTD'
and fai2.context1 = fai1.context1
and fai2.user_entity_id = fdi2.user_entity_id
and fdi2.user_name = 'A_SUI_ER_TAXABLE_PER_JD_GRE_QTD'
and fai3.context1 = fai1.context1
and fai3.user_entity_id = fdi3.user_entity_id
and fdi3.user_name = 'A_SUI_ER_GROSS_PER_JD_GRE_QTD'
and length(translate(trim(fai.value),' .0123456789',' ')) is null
and length(translate(trim(fai2.value),' .0123456789',' ')) is null ;