[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
2. Select all the GREs that lie under that business group and whose employees have PSD tax
deductions and check if 'PSD Reporting Rules' are defined for all such GREs.
Also check if these PSD Reporting Rules contain all the employer PSD codes of the employees
who have PSD tax deductions. If not then ist all such GREs and PSD codes in the log file.
3. Identify all the assignments to be reported and record an assignment action against
the payroll action for each one of them.
3. Run the "PSD Periodic Wage Listing Report" process to use this package.
History
Date Author Verion Bug Details
============================================================================
22-jun-2012 PRACAGRA 115.0 11712075 Initial Version Created
06-jun-2012 PRACAGRA 115.1 11712075 Added changes to include TCD
Code in report parameters.
09-jun-2012 PRACAGRA 115.3 11712075 Removed GSCC errors.
19-jun-2012 PRACAGRA 115.5 11712075 Removed the extra spaces from GRE name in the file name.
26-jun-2012 PRACAGRA 115.6 14373899 Modified hoi.org_information10 to
substr(hoi.org_information10,1,6)
because of the changes done to
'PAY_US_PSD_CODES' valueset ID.
24-aug-2012 PRACAGRA 115.7 14379256 Modified the code to accomodate
multiple jurisdiction codes for single asg.
27-Aug-2012 SKCHALLA 115.8 14379256 Removed the 'Continue' key word.
19-Sep-2012 PRACAGRA 115.9 14640336 Replaced asg_id with person_id
in get_jurisdiction cursors.
21-Sep-2012 PRACAGRA 115.10 Modified the report file name to omit
any special characters in the gre name.
28-Sep-2012 SKCHALLA 115.11 14690909 Modified the logic to get the person ID,
as archiver is failed to archive the employee
having assignment history.
28-Sep-2012 SKCHALLA 115.12 14690909 Modified the person_id logic used in the above version.
22-Nov-2012 SKCHALLA 115.13 14799833 Modified for the Yearly reporting.
05-Dec-2012 SKCHALLA 115.14 14799833 Modified range cursor and preprocess_check
for Yearly reporting.
05-Dec-2012 SKCHALLA 115.15 14799833 Modified preprocess_check for Yearly reporting.
06-Dec-2012 SKCHALLA 115.16 14799833 Modified preprocess_check for Yearly reporting.
11-Dec-2012 SKCHALLA 115.17 15944697 Modified the ranage cursor for Yearly reporting
============================================================================
*/
--
-- Global Variables
--
g_proc_name varchar2(240);
SELECT TO_NUMBER(UE.creator_id)
FROM ff_database_items DI,
ff_user_entities UE
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 user_entity_id
into l_user_entity_id
from ff_database_items
where user_name = p_dbi_name;
select pdb.defined_balance_id
from pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
where pbt.balance_name = cp_balance_name
and pbd.database_item_suffix= cp_balance_dimension
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and ((pbt.legislation_code = 'US' and
pbt.business_group_id is null)
or (pbt.legislation_code is null and
pbt.business_group_id = cp_business_group_id))
and ((pdb.legislation_code ='US' and
pdb.business_group_id is null)
or (pdb.legislation_code is null and
pdb.business_group_id = cp_business_group_id));
SELECT ppa.start_date,
ppa.effective_date,
ppa.business_group_id,
pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
'TRANSFER_STATE'),
ppa.report_type,
pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
'TRANSFER_PERIOD_TYPE'),
decode (pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
'TRANSFER_TAX_COLL_DIST_CODE'),'NULL',NULL, pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
'TRANSFER_TAX_COLL_DIST_CODE'))
INTO p_year_start,
p_year_end,
p_business_group_id,
p_state_abbrev,
p_report_type,
p_period_type,
p_tax_coll_dist_code
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_pactid;
select state_code into l_state_code
from pay_us_states pus
where pus.state_abbrev = p_state_abbrev;
/* SELECT fnd_date.canonical_to_date(pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
'TRANSFER_DATE'))
INTO p_period_end
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_pactid;*/
SELECT to_date(pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
'TRANSFER_DATE'), 'DD-MON-YYYY')
INTO p_period_end
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_pactid;
SELECT paa.tax_unit_id,
paa.payroll_action_id,
paa.assignment_action_id,
paa.assignment_id,
ppa.effective_date/*,
pay_us_psd_xml.get_parameter('TRANSFER_REPORTING_YEAR',ppa.legislative_parameters),
pay_us_psd_xml.get_parameter('TRANSFER_STATE',ppa.legislative_parameters),
pay_us_psd_xml.get_parameter('TRANSFER_PERIOD_TYPE',ppa.legislative_parameters) */ --Modified for Bug 14799833
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
-- and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
and ppa.report_type in ('PSD_MAG_XML')
and ppa.action_type = 'X'
and ppa.action_status = 'P';
SELECT paa.tax_unit_id,
paa.payroll_action_id,
paa.assignment_action_id,
paa.assignment_id,
ppa.effective_date
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = act_id--pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
and ppa.report_type ='YREND'
and ppa.action_type = 'X';
SELECT pay_us_psd_xml.get_parameter('TRANSFER_REPORTING_YEAR',ppa.legislative_parameters),
pay_us_psd_xml.get_parameter('TRANSFER_STATE',ppa.legislative_parameters),
pay_us_psd_xml.get_parameter('TRANSFER_PERIOD_TYPE',ppa.legislative_parameters)
FROM pay_payroll_actions ppa
where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
and ppa.report_type in ('PSD_MAG_XML')
and ppa.action_type = 'X'
and ppa.action_status = 'P';
/* SELECT AA1.assignment_action_id
FROM pay_assignment_actions AA,
pay_payroll_actions PA,
pay_assignment_actions AA1,
pay_payroll_actions PA1
WHERE AA.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
AND AA.payroll_action_id = PA.payroll_action_id
AND AA.tax_unit_id = AA1.tax_unit_id
AND AA1.assignment_id = AA.assignment_id
AND AA1.payroll_action_id = PA1.payroll_action_id
AND PA1.report_type = 'YREND'
and pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(PA.payroll_action_id,
'TRANSFER_PERIOD_TYPE')= 'Yearly'
and to_char(pa1.effective_date, 'YYYY') = pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(PA.payroll_action_id,
'TRANSFER_REPORTING_YEAR') */
SELECT pai.locked_action_id
FROM pay_action_interlocks pai
where pai.locking_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
select puar.jurisdiction_code
from pay_us_asg_reporting puar
where puar.assignment_id = p_assignment_id
and puar.tax_unit_id = p_tax_unit_id
and length(puar.jurisdiction_code)=16;*/
select pay_magtape_generic.get_parameter_value('TRANSFER_EE_JD_CODE')
into p_jurisdiction_code
from dual;
SELECT DISTINCT puar.tax_unit_id, hou.name
FROM pay_us_asg_reporting puar,
hr_all_organization_units hou
WHERE length(puar.jurisdiction_code)=16 and
puar.tax_unit_id = hou.organization_id and
hou.business_group_id = p_business_group_id and
substr(puar.jurisdiction_code, 11,6) not in ('880000','990000') and
substr(puar.jurisdiction_code, 11,2)=nvl(p_tax_coll_dist_code, substr(puar.jurisdiction_code, 11,2))
--Added for the Bug 14799833
AND NOT EXISTS (
SELECT 'Y'
FROM hr_organization_information
WHERE organization_id = hou.organization_id
AND org_information_context = '1099R Magnetic Report Rules')/*
AND ( ( pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid,
'TRANSFER_PERIOD_TYPE') <> 'Yearly'
) OR
( pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid,
'TRANSFER_PERIOD_TYPE') = 'Yearly'
AND EXISTS ( select null
from pay_payroll_actions ppa
where to_number(substr(LEGISLATIVE_PARAMETERS,1,instr(LEGISLATIVE_PARAMETERS,' ',1)-1)) = puar.tax_unit_id
and ppa.report_type = 'YREND'
and ppa.effective_date = pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid,
'TRANSFER_DATE')
)
)
)*/;
SELECT 'Y'
FROM hr_organization_information hoi
WHERE hoi.organization_id = l_tax_unit_id AND
hoi.org_information_context = 'PSD Reporting Rules'/* AND
NOT EXISTS (
SELECT 'Y'
FROM hr_organization_information
WHERE organization_id = hoi.organization_id
AND org_information_context = '1099R Magnetic Report Rules')*/;
SELECT puar.assignment_id, substr(puar.jurisdiction_code,11,6)
FROM pay_us_asg_reporting puar
WHERE puar.tax_unit_id =l_tax_unit_id and
length(puar.jurisdiction_code)=16 and
substr(puar.jurisdiction_code, 11,6) not in ('880000','990000') and
substr(puar.jurisdiction_code, 11,2)=nvl(p_tax_coll_dist_code, substr(puar.jurisdiction_code, 11,2))
order by substr(puar.jurisdiction_code,11,6);
SELECT paf.work_at_home
FROM per_all_assignments_f paf
WHERE assignment_id = l_asg_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT 'Y'
FROM hr_organization_information hoi
WHERE hoi.organization_id = l_tax_unit_id AND
hoi.org_information_context = 'PSD Reporting Rules' AND
substr(hoi.org_information10,1,6)=l_psd_code;
SELECT payroll_action_id
FROM pay_payroll_actions
WHERE report_type = 'YREND'
AND effective_date = pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid,
'TRANSFER_DATE')
AND business_group_id+0 = p_business_group_id
AND SUBSTR(legislative_parameters,
INSTR(legislative_parameters, 'TRANSFER_GRE=') +
LENGTH('TRANSFER_GRE=')) = TO_CHAR(cp_gre)
AND action_status = 'C';
select ppa.effective_date
into l_effective_date
from pay_payroll_actions ppa
where ppa.payroll_action_id=p_pactid;
select pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid
,'TRANSFER_PERIOD_TYPE') into l_period_type
from dual;
select pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid
,'TRANSFER_TRANS_LEGAL_CO_ID') into l_transmitter
from dual;
select '1' from hr_organization_information
where organization_id = cp_tax_unit_id
and org_information_context = 'W2 Reporting Rules';
select '1' from hr_organization_information
where organization_id = cp_tax_unit_id
and org_information_context = 'PSD Reporting Rules';
SELECT DISTINCT puar.tax_unit_id
FROM pay_us_asg_reporting puar,
hr_all_organization_units hou,
hr_organization_information hoi
WHERE length(puar.jurisdiction_code)=16 and
substr(puar.jurisdiction_code, 11,2)=nvl(l_tax_coll_dist_code, substr(puar.jurisdiction_code, 11,2)) and
puar.tax_unit_id = hou.organization_id and
hou.business_group_id = l_business_group_id and
hoi.organization_id= puar.tax_unit_id and
hoi.org_information_context = 'PSD Reporting Rules' AND
NOT EXISTS (
SELECT 'Y'
FROM hr_organization_information
WHERE organization_id = hoi.organization_id
AND org_information_context = '1099R Magnetic Report Rules');
update pay_payroll_actions
set effective_date = l_period_end
WHERE payroll_action_id = p_pactid;
SELECT pay_us_payroll_utils.get_parameter('TRANSFER_TRANS_LEGAL_CO_ID',legislative_parameters)
INTO l_trans_legal_co_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pactid;
SELECT name
INTO l_gre_name
FROM hr_all_organization_units
WHERE organization_id = l_trans_legal_co_id;
SELECT pay_us_payroll_utils.get_parameter('TRANSFER_MONTH/QUARTER',legislative_parameters)
INTO l_transfer_month_quarter
FROM pay_payroll_actions
WHERE payroll_action_id = p_pactid;
SELECT decode(l_tax_coll_dist_code,NULL,'ALL','0'||l_tax_coll_dist_code)
INTO l_tax_coll_dist_code_or_all
FROM dual;
SELECT report_file_name
INTO l_report_file_name
FROM pay_payroll_actions
WHERE payroll_action_id = p_pactid;
UPDATE pay_payroll_actions
SET report_file_name = l_report_file_name
WHERE payroll_action_id = p_pactid;
UPDATE pay_payroll_actions
SET magnetic_file_name = l_report_file_name
WHERE payroll_action_id = p_pactid;
p_sqlstr := 'SELECT distinct ASG.person_id
FROM hr_organization_information HOI,
per_all_assignments_f ASG,
pay_us_asg_reporting puar,
pay_state_rules SR
WHERE SR.state_code = '''||l_state_abbrev||'''
AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
AND substr(puar.jurisdiction_code, 11,2)=nvl('''||l_tax_coll_dist_code||''',substr(puar.jurisdiction_code, 11,2))
AND ASG.assignment_type = ''E''
AND ASG.effective_start_date <= ''' || l_period_end ||'''
AND ASG.effective_end_date >= ''' || l_period_start || '''
AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
AND HOI.organization_id = puar.tax_unit_id
AND HOI.ORG_INFORMATION_CONTEXT = ''PSD Reporting Rules''
AND asg.assignment_id = puar.assignment_id
AND not exists (select ''x''
from hr_organization_information HOI2
where HOI2.organization_id = puar.tax_unit_id
AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
AND HOI2.ORG_INFORMATION2 is not null)
AND ASG.payroll_id is not null
AND :payroll_action_id is not null
ORDER BY ASG.person_id';
p_sqlstr := 'SELECT DISTINCT to_number(paa.serial_number)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paf,
pay_payroll_actions ppa1,
pay_us_asg_reporting puar,
pay_state_rules SR
WHERE ppa1.payroll_action_id = :payroll_action_id
AND ppa.business_group_id+0 = ppa1.business_group_id
AND ppa1.effective_date = ppa.effective_date
AND ppa.report_type = ''YREND''
AND ppa.payroll_action_id = paa.payroll_action_id
AND paf.person_id = to_number(paa.serial_number)
AND paf.assignment_type = ''E''
AND SR.state_code = '''||l_state_abbrev||'''
AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
AND substr(puar.jurisdiction_code, 11,2)=nvl('''||l_tax_coll_dist_code||''',substr(puar.jurisdiction_code, 11,2))
AND puar.assignment_id = paf.assignment_id
AND paf.effective_start_date <= ppa.effective_date
AND paf.effective_end_date >= ppa.start_date
AND paa.action_status = ''C''
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 EXISTS ( SELECT ''x''
FROM hr_organization_information hoi
WHERE hoi.organization_id = paa.tax_unit_id
AND hoi.org_information_context = ''PSD Reporting Rules''
)
ORDER BY to_number(paa.serial_number)';
/* Variables used to hold the select columns from the SQL statement.*/
l_person_id per_people_f.person_id%type;
/* Local variables to select only those employees those PSD Withheld <>0*/
l_psd_def_bal_id pay_defined_balances.defined_balance_id%type;
SELECT
ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.effective_date between l_period_start
and l_period_end
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
and ASG.business_group_id + 0 = l_business_group_id
and ASG.person_id between p_stperson and p_endperson
and ASG.assignment_type = 'E'
and ASG.primary_flag = 'Y'
and ASG.business_group_id = ppa.business_group_id -- 5152728
and exists (select '1'
from pay_us_asg_reporting puar,
pay_state_rules SR
where SR.state_code = l_state_abbrev
and substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2)
and ASG.assignment_id = puar.assignment_id
)
ORDER BY 1, 3, 4 DESC, 2 ;
SELECT
ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_population_ranges ppr
WHERE ppa.effective_date between l_period_start
and l_period_end
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
and ASG.business_group_id + 0 = l_business_group_id
-- and ASG.person_id between stperson and endperson
and ppr.payroll_action_id = p_pactid
and ppr.chunk_number = p_chunk
and asg.person_id = ppr.person_id
and ASG.assignment_type = 'E'
and ASG.primary_flag = 'Y'
and ASG.business_group_id = ppa.business_group_id -- 5152728
and exists (select '1'
from pay_us_asg_reporting puar,
pay_state_rules SR
where SR.state_code = l_state_abbrev
and substr(SR.jurisdiction_code ,1,2) =
substr(puar.jurisdiction_code,1,2)
and ASG.assignment_id = puar.assignment_id
)
ORDER BY 1, 3, 4 DESC, 2 ;
SELECT ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date,
paa.assignment_action_id act_id
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.effective_date = l_period_end
and ppa.report_type = 'YREND'
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
and ASG.business_group_id + 0 = l_business_group_id
and ASG.person_id between p_stperson and p_endperson
and ASG.assignment_type = 'E'
and ASG.primary_flag = 'Y'
and ASG.business_group_id = ppa.business_group_id
and exists ( SELECT NULL
FROM ff_archive_items fai,
ff_archive_item_contexts faic,
FF_CONTEXTS FC
WHERE fai.context1 = paa.assignment_action_id
AND fai.archive_item_id = faic.archive_item_id
AND faic.CONTEXT_ID = FC.CONTEXT_ID
AND FC.CONTEXT_NAME='JURISDICTION_CODE'
AND length(FAIC.CONTEXT) = 16 )
ORDER BY 1, 3, 4 DESC, 2 ;
SELECT ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ppa.effective_date effective_end_date,
paa.assignment_action_id act_id
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_population_ranges ppr
WHERE ppa.effective_date= l_period_end
and ppa.report_type = 'YREND'
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = ASG.assignment_id
/*added to ignore skipped assignment */
and paa.action_status <> 'S'
and asg.effective_end_date >= l_period_start
and asg.effective_start_date <= l_period_end
and ASG.business_group_id + 0 = l_business_group_id
--and ASG.person_id between p_stperson and p_endperson
and ppr.payroll_action_id = p_pactid
and ppr.chunk_number = p_chunk
and asg.person_id = ppr.person_id
and ASG.assignment_type = 'E'
and ASG.primary_flag = 'Y'
and ASG.business_group_id = ppa.business_group_id
and exists ( SELECT NULL
FROM ff_archive_items fai,
ff_archive_item_contexts faic,
FF_CONTEXTS FC
WHERE fai.context1 = paa.assignment_action_id
AND fai.archive_item_id = faic.archive_item_id
AND faic.CONTEXT_ID = FC.CONTEXT_ID
AND FC.CONTEXT_NAME='JURISDICTION_CODE'
AND length(FAIC.CONTEXT) = 16 )
ORDER BY 1, 3, 4 DESC, 2 ;
select v.W2_LOCAL_INCOME_TAX
from PAY_US_LOCALITY_W2_V v
where v.assignment_action_id = l_assignment_action_id
and v.JURISDICTION = l_jd_code
and v.W2_LOCAL_INCOME_TAX <> 0;
SELECT /*+ORDERED*/
PAA.ASSIGNMENT_ACTION_ID,
PPA.EFFECTIVE_DATE
FROM PER_ALL_ASSIGNMENTS_F PAF,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA ,
PAY_ACTION_CLASSIFICATIONS PAC
WHERE PAF.PERSON_ID = l_person_id
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAA.TAX_UNIT_ID = l_tax_unit_id
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE = PAC.ACTION_TYPE
AND PAC.CLASSIFICATION_NAME = 'SEQUENCED'
AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN l_period_start AND l_period_end
AND ((NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NULL
AND PAA.SOURCE_ACTION_ID IS NULL)
OR (NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NOT NULL
AND PAA.SOURCE_ACTION_ID IS NOT NULL )
OR (PPA.ACTION_TYPE = 'V'
AND PPA.RUN_TYPE_ID IS NULL
AND PAA.RUN_TYPE_ID IS NOT NULL
AND PAA.SOURCE_ACTION_ID IS NULL))
ORDER BY PAA.ACTION_SEQUENCE DESC;
select distinct puar.jurisdiction_code
from pay_us_asg_reporting puar,
per_all_Assignments_f paf
where puar.assignment_id = paf.assignment_id
and paf.person_id = cp_person_id
and puar.tax_unit_id = cp_tax_unit_id
and length(puar.jurisdiction_code)=16
and substr(puar.jurisdiction_code,11,2)=nvl(l_tax_coll_dist_code,substr(puar.jurisdiction_code,11,2) );
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
/*SELECT pay_us_payroll_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
INTO l_transfer_gre_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pactid;
-- insert into pay_assignment_actions.
hr_utility.trace('In action_creation, lockingactid= '||lockingactid);
update pay_assignment_actions
set serial_number=l_person_id
where assignment_action_id=lockingactid;
select 'Y'
from dual
where exists (select null
from ff_archive_items fai
where fai.context1 = p_payroll_action_id
and archive_type = 'PPA');
select
substr(hoi.org_information10,1,6) value1,
hoi.org_information20 value2
from hr_organization_information hoi
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context || '' = 'PSD Reporting Rules';
select state_code
into l_state_code
from pay_us_states
where state_abbrev =
(select pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
'TRANSFER_STATE')
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id);
select hrl.country,
substr(hou.name,1,240),
substr(hoi.org_information1,1,240)
into l_value1,
l_value2,
l_value3
from hr_locations hrl,
hr_all_organization_units hou,
hr_organization_information hoi
where hou.organization_id = p_tax_unit_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context||'' = 'Employer Identification'
and hrl.location_id = hou.location_id;
l_user_entity_id_tab.delete;
l_value_tab.delete;
select
--hoi.org_information6 value1,
hoi.org_information8 value2,
hoi.org_information9 value3,
hoi.org_information10 value4,
hoi.org_information11 value5,
hoi.org_information12 value6,
hoi.org_information13 value7,
hoi.org_information14 value8,
hoi.org_information15 value9,
hoi.org_information16 value10,
--hoi.org_information2 value11,
--hoi.org_information3 value12,
--hoi.org_information4 value13,
--hoi.org_information5 value14,
--hoi.org_information7 value15, /* Job Development Fee (AL) */
hoi.org_information1 value16,
hoi.org_information19 value19, -- Bug 6928011 access code (PR)
hoi.org_information20 value20 --Bug 13255564
into
--l_value1,
l_value2,
l_value3,
l_value4,
l_value5,
l_value6,
l_value7,
l_value8,
l_value9,
l_value10,
--l_value11,
--l_value12,
--l_value13,
--l_value14,
--l_value15, /* Job Development Fee (AL) */
l_value16,
l_value19, -- Bug 6928011 access code (PR)
l_value20
from hr_organization_information hoi
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context || '' = 'W2 Reporting Rules';
l_user_entity_id_tab.delete;
l_value_tab.delete;
select hoi.org_information4 value1,
hoi.org_information8 value2
into l_value1,
l_value2
from hr_organization_information hoi
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context || '' = 'Federal Tax Rules';
l_user_entity_id_tab.delete;
l_value_tab.delete;
select to_char(rules.fips_code) value1,
ltrim(rtrim(target.org_information3)) value2,
ltrim(rtrim(target.org_information2)) value3
into l_value1,
l_value2,
l_value3
from pay_state_rules rules,
pay_us_states pus,
hr_organization_information target
where substr(rules.jurisdiction_code, 1, 2) =l_state_code
and target.organization_id = p_tax_unit_id
and target.org_information_context || '' = 'State Tax Rules'
and target.org_information1 = pus.state_abbrev
and pus.state_code = l_state_code;
l_user_entity_id_tab.delete;
l_value_tab.delete;
/*select
substr(hoi.org_information10,1,6) value1,
hoi.org_information20 value2
into
l_value1,
l_value2
from hr_organization_information hoi
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context || '' = 'PSD Reporting Rules';
l_user_entity_id_tab.delete;
l_value_tab.delete;
select min(paa.chunk_number)
from pay_assignment_actions paa
where paa.payroll_action_id = p_payroll_action_id;
select flv.meaning, pbt.balance_type_id
from pay_balance_types pbt,
fnd_application fa,
fnd_lookup_values flv,
fnd_lookup_types flt
where pbt.legislation_code = 'US'
and (
pbt.balance_name = substr(flv.meaning,
1 , decode(instr(flv.meaning, 'MONTH'),
0, length(flv.meaning),
instr(flv.meaning, 'MONTH')-2))
or
pbt.balance_name = substr(flv.meaning,
1 , decode(instr(flv.meaning, 'QTD'),
0, length(flv.meaning),
instr(flv.meaning, 'QTD')-2))
)
and fa.application_short_name = 'PAY'
and flv.lookup_type = flt.lookup_type
and flT.application_id = 801
and flt.lookup_type = 'PSD WAGE LISTING RPT BALANCES'
and flv.enabled_flag = 'Y'
and flv.language = 'US';
select USER_ENTITY_NAME
into pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).dbi_name
from ff_user_entities
where creator_id= (pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).month_def_bal_id)
and CREATOR_TYPE='B';
select USER_ENTITY_NAME
into pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).dbi_name
from ff_user_entities
where creator_id= (pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).qtd_def_bal_id)
and CREATOR_TYPE='B';
/* Local variables to select only those employees those PSD Withheld <>0*/
l_psd_def_bal_id pay_defined_balances.defined_balance_id%type;
select distinct puar.jurisdiction_code
from pay_us_asg_reporting puar,
per_all_Assignments_f paf
where puar.assignment_id = paf.assignment_id
and paf.person_id=cp_person_id
and puar.tax_unit_id = cp_tax_unit_id
and length(puar.jurisdiction_code)=16;
select distinct pcty.jurisdiction_code pcty
from pay_us_emp_city_tax_rules_f pcty,
per_assignments_f paf1,
per_assignments_f paf
where paf.assignment_id = l_asgid
and paf.effective_end_date >= l_period_start
and paf.effective_start_date <= l_period_end
and paf1.person_id = paf.person_id
and paf1.effective_end_date >= l_period_start
and paf1.effective_start_date <= l_period_end
and pcty.assignment_id = paf1.assignment_id
and pcty.effective_start_date <= l_period_end
and pcty.effective_end_date >= l_period_start;
SELECT /*+ORDERED*/
PAA.ASSIGNMENT_ACTION_ID,
PPA.EFFECTIVE_DATE
FROM PER_ALL_ASSIGNMENTS_F PAF1,
PER_ALL_ASSIGNMENTS_F PAF ,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA ,
PAY_ACTION_CLASSIFICATIONS PAC
WHERE PAF1.ASSIGNMENT_ID = p_assignment_id
AND PAF.PERSON_ID = PAF1.PERSON_ID
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAA.TAX_UNIT_ID = l_taxunitid
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE = PAC.ACTION_TYPE
AND PAC.CLASSIFICATION_NAME = 'SEQUENCED'
AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN l_period_start AND l_period_end
AND ((NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NULL
AND PAA.SOURCE_ACTION_ID IS NULL)
OR (NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NOT NULL
AND PAA.SOURCE_ACTION_ID IS NOT NULL )
OR (PPA.ACTION_TYPE = 'V'
AND PPA.RUN_TYPE_ID IS NULL
AND PAA.RUN_TYPE_ID IS NOT NULL
AND PAA.SOURCE_ACTION_ID IS NULL))
ORDER BY PAA.ACTION_SEQUENCE DESC;
select PEOPLE.person_id,
PEOPLE.first_name,
PEOPLE.middle_names,
PEOPLE.last_name,
PEOPLE.employee_number,
PEOPLE.suffix,
replace(PEOPLE.national_identifier,' ')
from per_all_assignments_f ASSIGN
,per_all_people_f PEOPLE
where ASSIGN.assignment_id =l_asgid
and PEOPLE.person_id = ASSIGN.person_id
and l_date_earned BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and l_date_earned BETWEEN PEOPLE.effective_start_date
AND PEOPLE.effective_end_date;
SELECT aa.assignment_id,
pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
aa.tax_unit_id,
aa.chunk_number,
aa.payroll_action_id
into l_asgid,
l_date_earned,
l_taxunitid,
l_chunk,
l_payroll_action_id
FROM pay_assignment_actions aa
WHERE aa.assignment_action_id = p_assactid;
/* select person_id into l_person_id
from per_all_assignments_f
where assignment_id=l_asgid;*/
select serial_number into l_person_id
from pay_assignment_actions
where assignment_action_id=p_assactid;
l_user_entity_id_tab.delete;
l_value_tab.delete;
hr_utility.trace('Problem in inserting employee data');