[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
10-JUL-2002 tclewis 115.12 modified load_prepay, when inserting data into
pay_us_rpt_totals also load pay_pre_payments.pre_payment_id
into the pay_us_rpt_totals.location_id (an indexed column).
New logic to look for the Pre_payment_id in the table
before inserting. This will eliminate duplicates.
added same functionality to load_mesg_line for the count
of unpaid pre-payments.
29-APR-2003 rsirigir 115.13 Bug 1937448,modified the appropriate select
statements in the cursor ee_tax, cursor er_tax
to include state name,school_district_name,county_name,
city_name to user reporting name
10-JUN-2002 tclewis 115.15 modified the load_er_liab procedures cursor
to join to pet.business_group_id.
04-AUG-2003 irgonzal 115.16 Bug fix 3046274. Amended cursor prepay
and added new condition to process only
one "run" asg action.
18-NOV-2003 rmonge 115.20 Fix for bug 3168646. Modified the load_prepay
program. Added new query to select the maximum
assignment_action_id for the payroll runs being
processed including the ones for Suplemental
runs with a separate check set to yes.
20-NOV-2003 irgonzal 115.22 Added the School District code and state abreviation
when displaying SD withheld (3271447).
6-NOV-2003 tlewis 115.17 Added code to Load EE Credit to handle State EIC.
14-APR-2004 schauhan 115.25 Modified the appropiate select statement in the cursor
ee_tax to include state_name,school_district_name,county_name
and city_name to user_reporting_name.Bug3553730
16-APR-2004 schauhan 115.26 Bug 3543649. Changed the query for the cursor prepay in the procedure
load_prepay so that query also returns third party payments.
04-MAY-2004 irgonzal 115.27 Bug fix 3270485. Modified load_data procedure and commented
out the insert into rpt totals.
05-May-2004 irgonzal 115.28 Fixed GSCC errors.
22-JUL-2004 saurgupt 115.29 Bug 3369218: Modified cursor er_liab in procedure load_er_liab
to remove FTS on pay_element_types_f
29-SEP-2004 saurgupt 115.30 Bug 3873069: Modified cursor er_liab of procedure load_er_liab
and cursor wc_er_liab of procedure load_wc_er_liab. The condition
pet.element_name = pbt.balance_name is modified to
pet.element_information10 = pbt.balance_type_id. This condition will
work even if balance_name of primary balance of element is not
equal to element_name.
09-DEC-2004 sgajula 115.31 Changed the procedures to implement BRA.
11-DEC-2004 sgajula 115.32 Changed the bulk insert block to Simple Insert
09-FEB-2005 rdhingra 115.33 Reset varibale l_status to 0 in deduction region
05-Mar-2005 rdhingra 115.34 Changed ee_or_er_code = 'ER' in load_er_tax
01-JUL-2005 tclewis 115.35 On Behalf of sackumar and saurgupt. Implemened
changes for bug 3774591. First change in the
load_mesg_line added code to check for the existance
of a pre-payment assignment action before counting
a payroll run as a unprocessed prepayment.
The second issue is to modify the load_prepay
In the code to determine the max_action_sequence
assignment action, added a check for the existence
of run results when pulling the max_action_sequece.
18-Jul-2005 sackumar 115.36 For Bug No 4429173. Change the condition for checking the
source_action_id in load_prepay procedure.
29-Aug-2005 rdhingra 115.37 For Bug No 4568652. Modified cursor cv of procedure
load_er_liab.
The condition pet.element_name = pbt.balance_name is modified to
pet.element_information10 = pbt.balance_type_id.
29-Aug-2005 sackumar 115.38 For Performance Bug No 4344971.
Introduced Index Hint in the SQL ID 12201224 and 12201189
12-SEP-2005 pragupta 115.39 Bug 4534407: Changed the attribute1 in the g_totals_table in
the load_er_liab procedure from 'EE-CREDIT' to 'ER-LIAB'. Also
added an extra condition in the l_er_liab_where variable.
16-SEP-2005 rdhingra 115.40 Added a distinct clause in cursor cv of procedure load_er_liab
02-FEB-2006 schauhan 115.41 Changed the dimension for FUTA CREDIT from ASG_GRE_RUN to ASG_JD_GRE_RUN
and passed jurisdiction_code to balance call. Bug 4443935.
21-MAR-2006 schauhan 115.42 Bug 5021468.
10-May-2006 sackumar 115.43 Bug 5068645. modified the dynamic query in load_er_tax procedure.
24-May-2006 sackumar 115.44 Bug 5244469. modified the dynamic query in load_er_tax procedure.
11-AUG-2006 saurgupt 115.45 Bug 5409416: Modified the procedure load_er_credit. Removed
prr.jurisdiction_code from select clause as this will fail if l_futa_from
is pay_run_balances table.
16-OCT-2006 jdevasah 115.46 Bug 4942114: Dynamic cursors in procedures load_deductions, load_earnings,
load_ee_tax, load_er_tax, load_ee_credit, load_er_credit, load_er_liab
and load_wc_er_liab are replaced by static procedures. Input parameters
to all the above procedures are changed to status flags instead
from respective view names.
16-OCT-2006 jdevasah 115.46 Bug 6998211: Restricted GRE Name to 228 chars as report showing
blank when we give gre_name more than 228 chars.
*/
------------------------------------- Global Varaibles ---------------------------
l_start_date pay_payroll_actions.start_date%type;
select classification_name,
decode(classification_name,'Pre-Tax Deductions','1','Involuntary Deductions','2','Voluntary Deductions','3','9')subclass,
element_name,
RUN_VALUE cash_value
from PAY_US_GTN_DEDUCT_V
where assignment_action_id =l_assignment_action_id
and classification_name in ('Pre-Tax Deductions',
'Involuntary Deductions',
'Voluntary Deductions');
select classification_name,
decode(classification_name,'Pre-Tax Deductions','1','Involuntary Deductions','2','Voluntary Deductions','3','9')subclass,
element_name,
RUN_VALUE cash_value
from PAY_US_ASG_RUN_DED_RBR_V
where assignment_action_id =l_assignment_action_id
and classification_name in ('Pre-Tax Deductions',
'Involuntary Deductions',
'Voluntary Deductions');
'select classification_name,
decode(classification_name,'||'''Pre-Tax Deductions'''||','||'''1'''||','||'''Involuntary Deductions'''||','||'''2'''||','||'''Voluntary Deductions'''||','||'''3'''||','||'''9'''||')subclass,
element_name,
RUN_VALUE cash_value
from '||l_ded_view_name||
' where assignment_action_id ='|| l_assignment_action_id||
' and classification_name in ('||'''Pre-Tax Deductions'''||','
||'''Involuntary Deductions'''||','||
'''Voluntary Deductions'''||')';
insert into pay_us_rpt_totals
(tax_unit_id,
gre_name,
organization_name,
location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
organization_id,
business_group_id,
attribute12)
values
(l_payroll_action_id,
l_gre_name,
l_org_name,
l_location_code,
'DEDUCTIONS',
l_payroll_action_id,
'4',
l_subclass,
l_classification_name,
l_element_name,
l_cash_value,
l_assignment_action_id,
l_person_id,
l_full_name
);
select classification_name,
decode(classification_name,'Earnings','1',
'Imputed Earnings','2',
'Supplemental Earnings','3',
'Non-payroll Payments','4',
'9')subclass,
element_name,
cash_value cash_value,
hours_value hours_value
from PAY_US_ASG_RUN_EARN_AMT_RBR_V
where assignment_action_id = l_assignment_action_id
and classification_name in ('Earnings',
'Imputed Earnings',
'Supplemental Earnings',
'Non-payroll Payments');
select /*+ index(pay_us_gtn_earnings_v.ernv.pec , pay_element_classification_pk)
INDEX(pay_us_gtn_earnings_v.ernv.PETTL PAY_ELEMENT_TYPES_F_TL_PK)
INDEX(pay_us_gtn_earnings_v.ernv.pet PAY_ELEMENT_TYPES_F_pk)
*/
classification_name,
decode(classification_name,'Earnings','1',
'Imputed Earnings','2',
'Supplemental Earnings','3',
'Non-payroll Payments','4',
'9')subclass,
element_name,
cash_value cash_value,
hours_value hours_value
from PAY_US_GTN_EARNINGS_V
where assignment_action_id = l_assignment_action_id
and classification_name in ('Earnings',
'Imputed Earnings',
'Supplemental Earnings',
'Non-payroll Payments');
insert into pay_us_rpt_totals
(tax_unit_id,
gre_name,
organization_name,
location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
value3,
organization_id,
business_group_id,
attribute12)
values
(l_payroll_action_id,
l_gre_name,
l_org_name,
l_location_code,
'EARNINGS',
l_payroll_action_id,
'1',
l_subclass,
l_classification_name,
l_element_name,
l_cash_value,
l_hours_value,
l_assignment_action_id,
l_person_id,
l_full_name);
select /*+ index(pay_us_earnings_amounts_v.pet , pay_element_types_f_pk)*/
classification_name,
5 sub_class,
element_name,
run_val cash_value,
hours_run_val hours_value
from pay_us_earnings_amounts_v
where assignment_action_id = l_assignment_action_id
and classification_name = 'Alien/Expat Earnings';
insert into pay_us_rpt_totals
(tax_unit_id, gre_name, organization_name, location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
value3,
organization_id)
values
(l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
'EARNINGS',
l_payroll_action_id,
'1',
ee_earn_rec.sub_class,
ee_earn_rec.classification_name,
ee_earn_rec.element_name,
ee_earn_rec.cash_value,
ee_earn_rec.hours_value,
l_assignment_action_id);
select user_reporting_name,'1' sub_class,run_val,null,null
from PAY_US_ASG_RUN_FED_TAX_RBR_V
where assignment_action_id = l_assignment_action_id
and ee_or_er_code = 'EE'
and tax_type_code <> 'EIC'
UNION ALL
select user_reporting_name ||' '|| state_name,'2' sub_class,run_val,TAX_TYPE_CODE,jurisdiction_code
from PAY_US_ASG_RUN_STATE_TAX_RBR_V
where assignment_action_id =l_assignment_action_id
and ee_or_er_code = 'EE'
and tax_type_code <> 'STEIC'
UNION ALL
select user_reporting_name||' '||(decode(state_name,'INVALID',null,state_name))||
' '|| nvl((decode(school_district_name,'INVALID',null, school_district_name)),
nvl((decode(county_name,'INVALID',null,county_name)),
(decode(city_name,'INVALID',null,city_name))
)
),
'3' sub_class,run_val,
TAX_TYPE_CODE,
jurisdiction_code
from PAY_US_ASG_RUN_LOCAL_TAX_RBR_V
where assignment_action_id = l_assignment_action_id
and ee_or_er_code = 'EE'
UNION ALL
SELECT 'EE Non W2 FIT Withheld',
'4' sub_class,
pqp_us_ff_functions.get_nonw2_bal('Non W2 FIT Withheld','run',paa.assignment_action_id,null,paa.tax_unit_id) run_value,
null,
null
FROM pay_assignment_actions paa
WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = 'TRUE'
AND assignment_action_id = l_assignment_action_id
UNION ALL
SELECT 'EE Non W2 SIT Withheld',
'4',
pqp_us_ff_functions.get_nonw2_bal('SIT Alien Withheld','run',paa.assignment_action_id,state.jurisdiction_code,paa.tax_unit_id) run_value,
null,
null
FROM pay_assignment_actions paa,
pay_us_emp_state_tax_rules_f state,
pay_payroll_actions ppa
WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = 'TRUE'
AND state.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id =l_assignment_action_id
AND ppa.effective_date BETWEEN state.effective_start_date AND state.effective_end_date;
select user_reporting_name,'1' sub_class,run_val,null,null
from PAY_US_FED_TAXES_V
where assignment_action_id = l_assignment_action_id
and ee_or_er_code = 'EE'
and tax_type_code <> 'EIC'
UNION ALL
select user_reporting_name ||' '|| state_name,'2' sub_class,run_val,TAX_TYPE_CODE,jurisdiction_code
from PAY_US_STATE_TAXES_V
where assignment_action_id =l_assignment_action_id
and ee_or_er_code = 'EE'
and tax_type_code <> 'STEIC'
UNION ALL
select user_reporting_name||' '||(decode(state_name,'INVALID',null,state_name))||
' '|| nvl((decode(school_district_name,'INVALID',null, school_district_name)),
nvl((decode(county_name,'INVALID',null,county_name)),
(decode(city_name,'INVALID',null,city_name))
)
),
'3' sub_class,run_val,
TAX_TYPE_CODE,
jurisdiction_code
from PAY_US_LOCAL_TAXES_V
where assignment_action_id = l_assignment_action_id
and ee_or_er_code = 'EE'
UNION ALL
SELECT 'EE Non W2 FIT Withheld',
'4' sub_class,
pqp_us_ff_functions.get_nonw2_bal('Non W2 FIT Withheld','run',paa.assignment_action_id,null,paa.tax_unit_id) run_value,
null,
null
FROM pay_assignment_actions paa
WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = 'TRUE'
AND assignment_action_id = l_assignment_action_id
UNION ALL
SELECT 'EE Non W2 SIT Withheld',
'4',
pqp_us_ff_functions.get_nonw2_bal('SIT Alien Withheld','run',paa.assignment_action_id,state.jurisdiction_code,paa.tax_unit_id) run_value,
null,
null
FROM pay_assignment_actions paa,
pay_us_emp_state_tax_rules_f state,
pay_payroll_actions ppa
WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = 'TRUE'
AND state.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id =l_assignment_action_id
AND ppa.effective_date BETWEEN state.effective_start_date AND state.effective_end_date;
select STATE_ABBREV||'-'||SCHOOL_DST_NAME
from pay_us_school_dsts DS
,pay_us_states st
where DS.STATE_CODE = p_state_code
and DS.SCHOOL_DST_CODE = p_sd_code
and ST.state_code = DS.state_code;
'select user_reporting_name,''1'' sub_class,run_val,null,null
from '||l_fed_view_name||
' where assignment_action_id ='|| l_assignment_action_id||
' and ee_or_er_code = ''EE''
and tax_type_code <> ''EIC'' UNION ALL
select user_reporting_name ||'' ''|| state_name,''2'' sub_class,run_val,TAX_TYPE_CODE,jurisdiction_code
from '||l_state_view_name||
' where assignment_action_id ='||l_assignment_action_id||
' and ee_or_er_code = ''EE''
and tax_type_code <> ''STEIC'' UNION ALL
select user_reporting_name||'' ''||(decode(state_name,''INVALID'',null,state_name))||
'' ''|| nvl((decode(school_district_name,''INVALID'',null, school_district_name)),
nvl(
(decode(county_name,''INVALID'',null,county_name)),
(decode(city_name,''INVALID'',null,city_name))
)),''3'' sub_class,run_val,TAX_TYPE_CODE,jurisdiction_code
from '|| l_local_view_name||
' where assignment_action_id = '||l_assignment_action_id||
' and ee_or_er_code = ''EE''
UNION ALL
SELECT ''EE Non W2 FIT Withheld'' ,
''4'' sub_class,
pqp_us_ff_functions.get_nonw2_bal(''Non W2 FIT Withheld'',''run'',paa.assignment_action_id,null,paa.tax_unit_id) run_value,
null,
null
FROM pay_assignment_actions paa
WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = ''TRUE''
AND assignment_action_id = '||l_assignment_action_id ||' UNION ALL
SELECT ''EE Non W2 SIT Withheld'' ,
''4'',
pqp_us_ff_functions.get_nonw2_bal(''SIT Alien Withheld'',''run'',paa.assignment_action_id,state.jurisdiction_code,paa.tax_unit_id) run_value,
null,
null
FROM
pay_assignment_actions paa,
pay_us_emp_state_tax_rules_f state,
pay_payroll_actions ppa
WHERE pqp_us_ff_functions.is_windstar(null,paa.assignment_id) = ''TRUE''
AND state.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id ='|| l_assignment_action_id||
' AND ppa.effective_date BETWEEN state.effective_start_date AND state.effective_end_date';
insert into pay_us_rpt_totals
(tax_unit_id, gre_name, organization_name, location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
organization_id,
business_group_id,
attribute12)
values
(l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
'EE-TAX',
l_payroll_action_id,
'2',
'1', --l_sub_class,
'Tax Deductions',
l_user_reporting_name,
l_run_val,
l_assignment_action_id,
l_person_id,
l_full_name);
select user_reporting_name,'1' sub_class,run_val
from PAY_US_ASG_RUN_FED_LIAB_RBR_V
where assignment_action_id = l_assignment_action_id
and ee_or_er_code = 'ER'
and database_item_suffix = decode(upper(user_reporting_name),
'ER FUTA LIABILITY' ,'_ASG_JD_GRE_RUN' ,
'_ASG_GRE_RUN')
UNION ALL
select user_reporting_name ||' '|| state_name,'2' sub_class,run_val
from PAY_US_ASG_RUN_ST_LIAB_RBR_V
where assignment_action_id =l_assignment_action_id
and ee_or_er_code = 'ER'
UNION ALL
select user_reporting_name||' '||(decode(state_name,'INVALID',null,state_name))||
' '|| nvl((decode(school_district_name,'INVALID',null, school_district_name)),
nvl((decode(county_name,'INVALID',null,county_name)),
(decode(city_name,'INVALID',null,city_name))
)
),
'3' sub_class,run_val
from PAY_US_ASG_RUN_LOCAL_TAX_RBR_V
where assignment_action_id = l_assignment_action_id
and ee_or_er_code = 'ER';
select user_reporting_name,'1' sub_class,run_val
from PAY_US_FED_LIABILITIES_V
where assignment_action_id = l_assignment_action_id
and ee_or_er_code = 'ER'
UNION ALL
select user_reporting_name ||' '|| state_name,'2' sub_class,run_val
from PAY_US_STATE_LIABILITIES_V
where assignment_action_id =l_assignment_action_id
and ee_or_er_code = 'ER'
UNION ALL
select user_reporting_name||' '||(decode(state_name,'INVALID',null,state_name))||
' '|| nvl((decode(school_district_name,'INVALID',null, school_district_name)),
nvl((decode(county_name,'INVALID',null,county_name)),
(decode(city_name,'INVALID',null,city_name))
)
),
'3' sub_class,run_val
from PAY_US_LOCAL_TAXES_V
where assignment_action_id = l_assignment_action_id
and ee_or_er_code = 'ER';
lv_sql_query := 'select user_reporting_name,''1'' sub_class,run_val
from ' || l_fed_liab_view_name ||
' where assignment_action_id ='|| l_assignment_action_id||
' and ee_or_er_code = ''ER'' ';
select user_reporting_name ||'' ''|| state_name,''2'' sub_class,run_val
from '||l_state_liab_view_name||
' where assignment_action_id ='||l_assignment_action_id||
' and ee_or_er_code = ''ER''
UNION ALL
select user_reporting_name||'' ''||(decode(state_name,''INVALID'',null,state_name))||
'' ''|| nvl((decode(school_district_name,''INVALID'',null, school_district_name)),
nvl(
(decode(county_name,''INVALID'',null,county_name)),
(decode(city_name,''INVALID'',null,city_name))
)),''3'' sub_class,run_val
from '|| l_local_liab_view_name||
' where assignment_action_id = '||l_assignment_action_id||
' and ee_or_er_code = ''ER''';
insert into pay_us_rpt_totals
( tax_unit_id, gre_name, organization_name, location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
organization_id,
business_group_id,
attribute12)
values
(l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
'ER-TAX',
l_payroll_action_id,
'6',
'1', --l_sub_class,
'Employer Taxes',
l_user_reporting_name,
l_run_val,
l_assignment_action_id,
l_person_id,
l_full_name);
select user_reporting_name,run_val
from PAY_US_ASG_RUN_FED_TAX_RBR_V
where assignment_action_id = l_assignment_action_id
and ee_or_er_code = 'EE'
and tax_type_code = 'EIC'
UNION ALL
select user_reporting_name ,run_val
from PAY_US_ASG_RUN_STATE_TAX_RBR_V
where assignment_action_id =l_assignment_action_id
and ee_or_er_code = 'EE'
and tax_type_code = 'STEIC';
select user_reporting_name,run_val
from PAY_US_FED_TAXES_V
where assignment_action_id = l_assignment_action_id
and ee_or_er_code = 'EE'
and tax_type_code = 'EIC'
UNION ALL
select user_reporting_name ,run_val
from PAY_US_STATE_TAXES_V
where assignment_action_id =l_assignment_action_id
and ee_or_er_code = 'EE'
and tax_type_code = 'STEIC';
'select user_reporting_name,run_val
from '||l_fed_view_name||
' where assignment_action_id ='|| l_assignment_action_id||
' and ee_or_er_code = ''EE''
and tax_type_code = ''EIC'' UNION ALL
select user_reporting_name ,run_val
from '||l_state_view_name||
' where assignment_action_id ='||l_assignment_action_id||
' and ee_or_er_code = ''EE''
and tax_type_code = ''STEIC''';
insert into pay_us_rpt_totals
(tax_unit_id, gre_name, organization_name, location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
organization_id,
business_group_id,
attribute12)
values
(l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
'EE-CREDIT',
l_payroll_action_id,
'3',
'1',
'Tax Credits',
l_user_reporting_name,
-1*l_run_val,
l_assignment_action_id,
l_person_id,
l_full_name);
select distinct 'ER Tax Credits' classification_name ,'FUTA CREDIT' balance_name ,
pet.element_name element_name, prb.jurisdiction_code jurisdiction_code
from pay_element_types_f pet,
pay_run_balances prb
where l_effective_date between pet.effective_start_date and pet.effective_end_date
and pet.element_name ='FUTA CREDIT'
and prb.defined_balance_id = p_futa_def_bal_id
AND prb.assignment_action_id = l_assignment_action_id;
select distinct 'ER Tax Credits' classification_name ,'FUTA CREDIT' balance_name ,
pet.element_name element_name, prr.jurisdiction_code jurisdiction_code
from pay_element_types_f pet,
pay_run_results prr
where l_effective_date between pet.effective_start_date and pet.effective_end_date
and pet.element_name ='FUTA CREDIT'
and prr.status in ('P','PA')
and pet.element_type_id = prr.element_type_id
and prr.assignment_action_id = l_assignment_action_id;
' select distinct'||'''ER Tax Credits'''||' classification_name ,'||'''FUTA CREDIT'''||' balance_name ,
pet.element_name element_name,'|| l_tname||'.jurisdiction_code jurisdiction_code
from pay_element_types_f pet,'
|| l_futa_from ||
' where '''||l_effective_date||''' between pet.effective_start_date and pet.effective_end_date
and pet.element_name ='||'''FUTA CREDIT'''||
' and '||l_futa_where;
insert into pay_us_rpt_totals
(tax_unit_id, gre_name, organization_name, location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
organization_id,
business_group_id,
attribute12)
values
(l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
'ER-CREDIT',
l_payroll_action_id,
'7',
'1',
'Employer Tax Credits',
l_element_name,
l_bal_value,
l_assignment_action_id,
l_person_id,
l_full_name);
select distinct pec.classification_name classification_name,
pbt.balance_name balance_name,
pet.element_name element_name
from pay_balance_types pbt,
pay_element_types_f pet,
pay_element_classifications pec,
pay_run_balances prb,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pec.classification_name ='Employer Liabilities'
and pec.legislation_code ='US'
and pet.classification_id = pec.classification_id
and pet.business_group_id = l_business_group_id
and pet.element_type_id >= 0
and l_effective_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_information10 = pbt.balance_type_id
and pbt.business_group_id = l_business_group_id
and prb.defined_balance_id = pdb.defined_balance_id
and (pdb.business_group_id = l_business_group_id
or pbd.legislation_code ='US')
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.legislation_code = 'US'
and pbd.database_item_suffix = '_ASG_GRE_RUN'
and prb.assignment_action_id = l_assignment_action_id;
select distinct pec.classification_name classification_name,
pbt.balance_name balance_name,
pet.element_name element_name
from pay_balance_types pbt,
pay_element_types_f pet,
pay_element_classifications pec,
pay_run_results prr
where pec.classification_name ='Employer Liabilities'
and pec.legislation_code ='US'
and pet.classification_id = pec.classification_id
and pet.business_group_id = l_business_group_id
and pet.element_type_id >= 0
and l_effective_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_information10 = pbt.balance_type_id
and pbt.business_group_id =l_business_group_id
and prr.element_type_id +0 = pet.element_type_id
and prr.status in ('P','PA')
and prr.assignment_action_id = l_assignment_action_id;
'select distinct pec.classification_name classification_name,
pbt.balance_name balance_name,
pet.element_name element_name
from
pay_balance_types pbt,
pay_element_types_f pet,
pay_element_classifications pec,'
|| l_er_liab_from||
' where pec.classification_name ='||'''Employer Liabilities'''||
' and pec.legislation_code ='||'''US'''||
' and pet.classification_id = pec.classification_id
and pet.business_group_id = '||l_business_group_id||
' and pet.element_type_id >= 0 -- Bug 3369218: Added to enforce index to
and '''|| l_effective_date||''' between pet.effective_start_date -- remove FTS on pay_element_types_f
and pet.effective_end_date
and pet.element_information10 = pbt.balance_type_id
and pbt.business_group_id ='|| l_business_group_id ||
' and '||l_er_liab_where;
insert into pay_us_rpt_totals
(tax_unit_id, gre_name, organization_name, location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
organization_id,
business_group_id,
attribute12)
values
(l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
'ER-LIAB',
l_payroll_action_id,
'5',
'1',
l_classification_name,
l_element_name,
l_bal_value,
l_assignment_action_id,
l_person_id,
l_full_name);
select ppf.full_name
from per_all_people_f ppf
where ppf.person_id = l_person_id
and l_effective_date
between ppf.effective_start_date and ppf.effective_end_date ;
select popm.org_payment_method_name Payment_Method_Name,
fcl.meaning,
decode(pea.segment3,null,null,'*****'||substr(pea.segment3,-4,4)),
substr(ltrim(pea.segment4),1,9)
from fnd_common_lookups fcl,
pay_external_accounts pea,
pay_personal_payment_methods_f pppm,
pay_org_payment_methods_f popm,
pay_payment_types ppt,
pay_pre_payments ppp
where fcl.application_id(+) = 800
and fcl.lookup_type(+) = 'US_ACCOUNT_TYPE'
and pea.segment2 = fcl.lookup_code(+)
and pea.external_account_id(+) = pppm.external_account_id
and pppm.personal_payment_method_id(+) = ppp.personal_payment_method_id
and popm.org_payment_method_id = ppp.org_payment_method_id
and ppt.payment_type_name in ('NACHA','Check')
and l_effective_date
between popm.effective_start_date and popm.effective_end_date
and popm.payment_type_id = ppt.payment_type_id
and l_effective_date
between
nvl(pppm.effective_start_date, l_effective_date )
and
nvl(pppm.effective_end_date, l_effective_date)
and ppp.pre_payment_id = l_ppp_pre_payment_id;
select ppp.pre_payment_id pre_payment_id
from pay_pre_payments ppp,
pay_payroll_actions ppa_ppp,
pay_assignment_actions paa_ppp,
pay_action_interlocks pai
where pai.locked_action_id = l_assignment_action_id
and paa_ppp.assignment_action_id = pai.locking_action_id
and paa_ppp.action_status = 'C'
and ppa_ppp.payroll_action_id = paa_ppp.payroll_action_id
and ppa_ppp.action_type in ('U','P')
and ppa_ppp.action_status = 'C'
and ppp.assignment_action_id = paa_ppp.assignment_action_id;
select '1' found
from pay_payroll_actions ppa_chk,
pay_assignment_actions paa_chk
where paa_chk.pre_payment_id = l_ppp_pre_payment_id
and ppa_chk.payroll_action_id = paa_chk.payroll_action_id
and ppa_chk.action_type in ('H','M','E')
and ppa_chk.action_status = 'C';
SELECT 'X'
INTO l_dummy_var
from pay_us_rpt_totals
where location_id = l_ppp_pre_payment_id
and tax_unit_id = t_payroll_action_id
and attribute4 = 'Unpaid Payments' ;
insert into pay_us_rpt_totals
(tax_unit_id, gre_name, organization_name, location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
value2,
organization_id,
location_id)
values
(l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
'MESG-LINE',
l_payroll_action_id,
'10',
'2',
'Unpaid Payments',
'Incomplete Payments',
l_full_name,
l_assignment_number,
l_payment_method_name,
l_account_type,
l_account_number,
l_routing_number,
1,
l_assignment_action_id,
l_ppp_pre_payment_id);
select 'X'
into l_dummy_var
from pay_payroll_actions ppa_ppp,
pay_assignment_actions paa_ppp,
pay_action_interlocks pai
where pai.locked_action_id = l_assignment_action_id
and paa_ppp.assignment_action_id = pai.locking_action_id
and paa_ppp.action_status = 'C'
and ppa_ppp.payroll_action_id = paa_ppp.payroll_action_id
and ppa_ppp.action_type in ('U','P')
and ppa_ppp.action_status = 'C'
and rownum=1; -- Bug 5021468
insert into pay_us_rpt_totals
(tax_unit_id, gre_name, organization_name, location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
value2,
organization_id)
values
(l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
'MESG-LINE',
l_payroll_action_id,
'10',
'1',
'Unprocessed Pre-Payments',
'Number of runs w/o Pre-payments',
l_full_name,
l_assignment_number,
1,l_assignment_action_id);
select PAA_PPP.ASSIGNMENT_ACTION_ID PRE_PAY_AAID,
POPM.ORG_PAYMENT_METHOD_NAME PMT_NAME,
PPP.VALUE VALUE,
PPP.PRE_PAYMENT_ID PMT_ID
from PAY_PAYROLL_ACTIONS PPA_PPP,
PAY_ASSIGNMENT_ACTIONS PAA_PPP,
PAY_ACTION_INTERLOCKS PAI_RUN,
PAY_PAYROLL_ACTIONS PPA_CHK,
PAY_ASSIGNMENT_ACTIONS PAA_CHK,
PAY_ACTION_INTERLOCKS PAI_CHK,
PAY_ORG_PAYMENT_METHODS_F POPM,
PAY_PRE_PAYMENTS PPP
WHERE PAI_RUN.LOCKED_ACTION_ID = p_max_seq_aaid
AND PAI_RUN.LOCKING_ACTION_ID = PAA_PPP.ASSIGNMENT_ACTION_ID
AND PAA_PPP.ACTION_STATUS = 'C'
AND PAA_PPP.PAYROLL_ACTION_ID = PPA_PPP.PAYROLL_ACTION_ID
AND PPA_PPP.ACTION_STATUS = 'C'
AND PAA_PPP.ASSIGNMENT_ACTION_ID = PPP.ASSIGNMENT_ACTION_ID
AND POPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID
AND PPA_PPP.EFFECTIVE_DATE BETWEEN
POPM.EFFECTIVE_START_DATE AND POPM.EFFECTIVE_END_DATE
-- AND POPM.DEFINED_BALANCE_ID IS NOT NULL --Bug 3543649
AND PAI_CHK.LOCKED_ACTION_ID = PAA_PPP.ASSIGNMENT_ACTION_ID
AND PAI_CHK.LOCKING_ACTION_ID = PAA_CHK.ASSIGNMENT_ACTION_ID
AND PAA_CHK.ACTION_STATUS = 'C'
AND PAA_CHK.PRE_PAYMENT_ID = PPP.PRE_PAYMENT_ID
AND PPA_CHK.PAYROLL_ACTION_ID = PAA_CHK.PAYROLL_ACTION_ID
AND PPA_CHK.ACTION_STATUS = 'C'
AND PPA_CHK.ACTION_TYPE IN ('H', 'M')
AND NOT EXISTS
(SELECT NULL
FROM PAY_PAYROLL_ACTIONS PPA_VOID,
PAY_ASSIGNMENT_ACTIONS PAA_VOID,
PAY_ACTION_INTERLOCKS PAI_VOID
WHERE PAI_VOID.LOCKED_ACTION_ID = PAA_CHK.ASSIGNMENT_ACTION_ID
AND PAA_VOID.ASSIGNMENT_ACTION_ID = PAI_VOID.LOCKING_ACTION_ID
AND PAA_VOID.ACTION_STATUS = 'C'
AND PPA_VOID.PAYROLL_ACTION_ID = PAA_VOID.PAYROLL_ACTION_ID
AND PPA_VOID.ACTION_TYPE = 'D'
AND PPA_VOID.ACTION_STATUS = 'C' )
;
select paa_outer.assignment_action_id
into l_max_sequence_aaid
from pay_assignment_actions paa_outer
where (paa_outer.payroll_action_id, paa_outer.action_sequence) =
(select paa1.payroll_action_id,
max(paa1.action_sequence)
from pay_assignment_actions paa1,
pay_assignment_actions paa2
where paa1.payroll_action_id = paa2.payroll_action_id
and paa2.assignment_action_id =p_assignment_action_id
and paa1.assignment_id = paa2.assignment_id
-- Bug No 4429173 and paa1.source_action_id is not null
and ((paa1.run_type_id is not null and paa1.source_action_id is not null)
or(paa1.run_type_id is null and paa1.source_action_id is null))
and exists (
select 'Y'
from pay_run_result_values rrv,
pay_input_values_F iv,
pay_run_results rr
where nvl(rrv.result_value,0) <> to_char(0)
and iv.input_value_id = rrv.input_value_id
and iv.name = 'Pay Value'
and rr.run_result_id = rrv.run_result_id
and rr.assignment_action_id = paa1.assignment_action_id
)
group by paa1.payroll_action_id);
SELECT 'X'
INTO l_dummy_val
FROM pay_us_rpt_totals
where location_id = l_pre_pay_id
and tax_unit_id = t_payroll_action_id
and attribute4 = 'Disbursements';
insert into pay_us_rpt_totals
(tax_unit_id, gre_name, organization_name, location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
organization_id,
location_id)
values
(t_payroll_action_id, l_gre_name, l_org_name, l_location_code,
'PREPAY',
t_payroll_action_id,
'8',
'1',
'Disbursements',
l_pmt_name,
l_pmt_value,
l_pre_pay_aaid,
l_pre_pay_id);
insert into pay_us_rpt_totals
(tax_unit_id, gre_name, organization_name, location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,organization_id)
values
(l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
'REVERSAL',
l_payroll_action_id,
'9',
'1',
'Reversals',
'Reversals',
l_reverse_amt, l_assignment_action_id);
select pec.classification_name classification_name,
pbt.balance_name balance_name,
pet.element_name element_name,
pftr.sui_jurisdiction_code jurisdiction_code,
pst.state_name state_name
from
pay_balance_types pbt,
pay_element_types_f pet,
pay_element_classifications pec,
pay_assignment_actions paa,
per_all_assignments_f paf,
pay_us_emp_fed_tax_rules_F pftr,
pay_us_states pst,
pay_run_balances prb,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where pec.classification_name ='Employer Taxes'
and pec.legislation_code ='US'
and pet.classification_id = pec.classification_id
and l_effective_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_information10 = pbt.balance_type_id
and pet.element_name in ('Workers Compensation',
'Workers Compensation2 ER',
'Workers Compensation3 ER')
and l_assignment_action_id = paa.assignment_action_id
and paa.assignment_id = paf.assignment_id
and paf.assignment_id = pftr.assignment_id
and l_effective_date between paf.effective_start_date
and paf.effective_end_date
and l_business_group_id = paf.business_group_id
and l_effective_date between pftr.effective_start_date
and pftr.effective_end_date
and pst.state_code = substr(pftr.sui_jurisdiction_code,1,2)
and prb.defined_balance_id = pdb.defined_balance_id
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbd.legislation_code = 'US'
AND pbd.database_item_suffix ='_ASG_JD_GRE_RUN'
AND (pdb.legislation_code ='US'
OR pdb.business_group_id =l_business_group_id)
and prb.assignment_action_id = paa.assignment_action_id
and prb.tax_unit_id = paa.tax_unit_id
and prb.jurisdiction_code = pst.state_code
and prb.tax_unit_id = paa.tax_unit_id;
select pec.classification_name classification_name,
pbt.balance_name balance_name,
pet.element_name element_name,
pftr.sui_jurisdiction_code jurisdiction_code,
pst.state_name state_name
from
pay_balance_types pbt,
pay_element_types_f pet,
pay_element_classifications pec,
pay_assignment_actions paa,
per_all_assignments_f paf,
pay_us_emp_fed_tax_rules_F pftr,
pay_us_states pst,
pay_run_results prr
where pec.classification_name ='Employer Taxes'
and pec.legislation_code ='US'
and pet.classification_id = pec.classification_id
and l_effective_date between pet.effective_start_date
and pet.effective_end_date
and pet.element_information10 = pbt.balance_type_id
and pet.element_name in ('Workers Compensation',
'Workers Compensation2 ER',
'Workers Compensation3 ER')
and l_assignment_action_id = paa.assignment_action_id
and paa.assignment_id = paf.assignment_id
and paf.assignment_id = pftr.assignment_id
and l_effective_date between paf.effective_start_date
and paf.effective_end_date
and l_business_group_id = paf.business_group_id
and l_effective_date between pftr.effective_start_date
and pftr.effective_end_date
and pst.state_code = substr(pftr.sui_jurisdiction_code,1,2)
and prr.element_type_id +0 = pet.element_type_id
and prr.assignment_action_id = paa.assignment_action_id;
'select pec.classification_name classification_name,
pbt.balance_name balance_name,
pet.element_name element_name,
pftr.sui_jurisdiction_code jurisdiction_code,
pst.state_name state_name
from
pay_balance_types pbt,
pay_element_types_f pet,
pay_element_classifications pec,
pay_assignment_actions paa,
per_all_assignments_f paf,
pay_us_emp_fed_tax_rules_F pftr,
pay_us_states pst,'
||l_wc_er_liab_from||
' where pec.classification_name ='||'''Employer Taxes'''
||' and pec.legislation_code ='||'''US'''
||' and pet.classification_id = pec.classification_id
and '''||l_effective_date||''' between pet.effective_start_date
and pet.effective_end_date
and pet.element_information10 = pbt.balance_type_id
and pet.element_name in ('|| '''Workers Compensation'''||','
|| '''Workers Compensation2 ER'''||','
|| '''Workers Compensation3 ER'''||')
and '||l_assignment_action_id ||' = paa.assignment_action_id
and paa.assignment_id = paf.assignment_id
and paf.assignment_id = pftr.assignment_id
and '''||l_effective_date||''' between paf.effective_start_date
and paf.effective_end_date
and '||l_business_group_id ||'= paf.business_group_id
and '''||l_effective_date||''' between pftr.effective_start_date
and pftr.effective_end_date
and pst.state_code = substr(pftr.sui_jurisdiction_code,1,2)
and '|| l_wc_er_liab_where;
insert into pay_us_rpt_totals
(tax_unit_id, gre_name, organization_name, location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
organization_id,
business_group_id,
attribute12)
values
(l_payroll_action_id, l_gre_name, l_org_name, l_location_code,
'ER-TAX',
l_payroll_action_id,
'6',
'1',
'Employer Taxes',
l_element_name||' '||l_state_name,
l_bal_value,
l_assignment_action_id,
l_person_id,
l_full_name);
select
ppa_arch.start_date start_date,
ppa_arch.effective_date end_date,
ppa_arch.business_group_id business_group_id,
ppa_arch.payroll_action_id payroll_action_id,
ppa.effective_date effective_date,
ppa.action_type action_type,
paa1.assignment_action_id assignment_action_id,
paa1.assignment_id assignment_id,
paa1.tax_unit_id tax_unit_id,
substr(hou.name,1,228) gre_name, /*bug6998211*/
paf.organization_id organization_id,
substr(hou1.name,1,228) organization_name,
paf.location_id location_id,
hrl.location_code location_code
,paf.assignment_number assignment_number -- #1937448
,paf.person_id person_id
from hr_locations_all hrl,
hr_all_organization_units hou1,
hr_all_organization_units hou,
per_assignments_f paf,
pay_payroll_actions ppa,
pay_assignment_actions paa1,
pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa_arch
where ppa_arch.payroll_action_id = l_pactid
and paa.payroll_action_id = ppa_arch.payroll_action_id
and paa.chunk_number = l_chnkno
and pai.locking_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = paa1.payroll_action_id
and paf.assignment_id = paa1.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and hrl.location_id = paf.location_id
and hou1.organization_id = paf.organization_id
and hou.organization_id = paa1.tax_unit_id;
select ppa.legislative_parameters,
ppa.business_group_id,
ppa.start_date,
ppa.effective_date,
pay_paygtn_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
pay_paygtn_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
pay_paygtn_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
ppa.payroll_action_id
into l_leg_param,
l_business_group_id,
l_leg_start_date,
l_leg_end_date,
t_consolidation_set_id,
t_payroll_id,
t_gre_id,
t_payroll_action_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select to_number(ue.creator_id)
into l_defined_balance_id
from ff_user_entities ue,
ff_database_items di
where di.user_name = 'PAYMENTS_ASG_GRE_RUN'
and ue.user_entity_id = di.user_entity_id
and ue.creator_type = 'B'
and nvl(ue.legislation_code,'US') = 'US';
INSERT INTO pay_us_rpt_totals(tax_unit_id, gre_name,
organization_name, location_name,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
value3
) values
(g_totals_table(x).tax_unit_id, g_totals_table(x).gre_name,
g_totals_table(x).organization_name, g_totals_table(x).location_name,
g_totals_table(x).attribute1,
g_totals_table(x).attribute2,
g_totals_table(x).attribute3,
g_totals_table(x).attribute4,
g_totals_table(x).attribute5,
g_totals_table(x).value2,
g_totals_table(x).value3
);
insert into pay_us_rpt_totals
values
g_totals_table(x);*/
g_totals_table.DELETE;
select
ppa_arch.start_date start_date,
ppa_arch.effective_date end_date,
ppa_arch.business_group_id business_group_id,
ppa_arch.payroll_action_id payroll_action_id,
ppa.effective_date effective_date,
ppa.action_type action_type,
paa1.assignment_action_id assignment_action_id,
paa1.assignment_id assignment_id,
paa1.tax_unit_id tax_unit_id,
substr(hou.name,1,228) gre_name, /*bug6998211*/
paf.organization_id organization_id,
substr(hou1.name,1,228) organization_name,
paf.location_id location_id,
hrl.location_code location_code
,paf.assignment_number assignment_number -- #1937448
,paf.person_id person_id
,paa.chunk_number chunk_number
from hr_locations_all hrl,
hr_all_organization_units hou1,
hr_all_organization_units hou,
per_assignments_f paf,
pay_payroll_actions ppa,
pay_assignment_actions paa1,
pay_temp_object_actions paa,
pay_payroll_actions ppa_arch
where paa.payroll_action_id = l_pactid
and paa.chunk_number = l_chunk_no
and paa.payroll_action_id = ppa_arch.payroll_action_id
and paa.object_id = paa1.assignment_action_id
and ppa.payroll_action_id = paa1.payroll_action_id
and paf.assignment_id = paa1.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and hrl.location_id = paf.location_id
and hou1.organization_id = paf.organization_id
and hou.organization_id = paa1.tax_unit_id;
select
ppf.full_name
from per_all_people_f ppf
where ppf.person_id = l_person_id
and l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
select to_number(ue.creator_id)
into l_defined_balance_id
from ff_user_entities ue,
ff_database_items di
where di.user_name = 'PAYMENTS_ASG_GRE_RUN'
and ue.user_entity_id = di.user_entity_id
and ue.creator_type = 'B'
and nvl(ue.legislation_code,'US') = 'US';
select ppa.legislative_parameters,
ppa.start_date,
ppa.effective_date,
pay_paygtn_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
pay_paygtn_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
pay_paygtn_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
ppa.payroll_action_id
into l_leg_param,
l_leg_start_date,
l_leg_end_date,
t_consolidation_set_id,
t_payroll_id,
t_gre_id,
t_payroll_action_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = l_payroll_action_id;
INSERT INTO pay_us_rpt_totals(tax_unit_id, gre_name,
organization_name, location_name,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
value2,
value3
) values
(g_totals_table(x).tax_unit_id, g_totals_table(x).gre_name,
g_totals_table(x).organization_name, g_totals_table(x).location_name,
g_totals_table(x).attribute1,
g_totals_table(x).attribute2,
g_totals_table(x).attribute3,
g_totals_table(x).attribute4,
g_totals_table(x).attribute5,
g_totals_table(x).value2,
g_totals_table(x).value3
);
insert into pay_us_rpt_totals
values
g_totals_table(x);*/
g_totals_table.DELETE;