[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ACTION_ID',legislative_parameters)) payroll_action_id
,fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASS_SETID',legislative_parameters)) ass_setid
,pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id
,NVL(pay_core_utils.get_parameter('IOH',legislative_parameters),'Y') include_org_hierarchy
,pay_core_utils.get_parameter('ORG',legislative_parameters) organization_id
,pay_core_utils.get_parameter('LOC',legislative_parameters) location_id
,FND_DATE.canonical_to_date(pay_core_utils.get_parameter('EFFDATE',legislative_parameters)) effective_date
,NVL(pay_core_utils.get_parameter('S1',legislative_parameters),'ZZ') sort_order_1
,NVL(pay_core_utils.get_parameter('S2',legislative_parameters),'ZZ') sort_order_2
,NVL(pay_core_utils.get_parameter('S3',legislative_parameters),'ZZ') sort_order_3
,pay_core_utils.get_parameter('ITE',legislative_parameters) incl_term_emp
,TO_DATE(pay_core_utils.get_parameter('TEDF',legislative_parameters),'YYYY/MM/DD') term_eff_date_from
,TO_DATE(pay_core_utils.get_parameter('TEDT',legislative_parameters),'YYYY/MM/DD') term_eff_date_to
FROM pay_payroll_actions PPA
WHERE PPA.payroll_action_id = p_payroll_action_id;
SELECT TO_DATE(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD')
INTO gr_parameters.effective_date
FROM pay_payroll_actions PPA
WHERE PPA.payroll_action_id = gr_parameters.payroll_action_id;
p_sqlstr := ' select distinct p.person_id'||
' from per_people_f p,'||
' pay_payroll_actions pa'||
' where pa.payroll_action_id = :payroll_action_id'||
' and p.business_group_id = pa.business_group_id'||
' order by p.person_id ';
sqlstr := ' SELECT PAA.rowid
FROM per_assignments_f PAF
,pay_assignment_actions PAA
,per_people_f PPF
,hr_all_organization_units_tl HAOUT
,hr_all_organization_units HAOU
,per_periods_of_service PPS
WHERE PAA.payroll_action_id = :pactid
AND PAF.assignment_id = PAA.assignment_id
AND PPF.person_id = PAF.person_id
AND PAF.organization_id = HAOU.organization_id
AND HAOUT.organization_id = HAOU.organization_id
AND HAOUT.language = USERENV(''LANG'')
AND PPS.period_of_service_id = PAF.period_of_service_id
AND NVL(TRUNC(PPS.actual_termination_date),'''||gr_parameters.effective_date||''') BETWEEN PPF.effective_start_date
AND PPF.effective_end_date
AND NVL(TRUNC(PPS.actual_termination_date),'''||gr_parameters.effective_date||''') BETWEEN PAF.effective_start_date
AND PAF.effective_end_date
/*AND (( NVL('''||gr_parameters.incl_term_emp||''',''N'') = ''Y''
AND( PPS.actual_termination_date IS NULL
OR (TRUNC(PPS.actual_termination_date) BETWEEN '''||gr_parameters.term_eff_date_from||'''
AND '''||gr_parameters.term_eff_date_to||''')
)
)
OR
( NVL('''||gr_parameters.incl_term_emp||''',''N'') = ''N''
AND PPS.actual_termination_date IS NULL
)
)*/
ORDER BY DECODE('''||gr_parameters.sort_order_1||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name) -- changed by RDARASI for BUG# 8774489
,''ORGANIZATION_CODE'',UPPER(HAOUT.name) -- Added UPPER by RDARASI for BUG# 8774489
,UPPER(PPF.employee_number) -- Added UPPER by RDARASI for BUG# 8774489
)
,DECODE('''||gr_parameters.sort_order_2||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name) -- changed by RDARASI for BUG# 8774489
,''ORGANIZATION_CODE'',UPPER(HAOUT.name) -- Added UPPER by RDARASI for BUG# 8774489
,UPPER(PPF.employee_number) -- Added UPPER by RDARASI for BUG# 8774489
)
,DECODE('''||gr_parameters.sort_order_3||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name) -- changed by RDARASI for BUG# 8774489
,''ORGANIZATION_CODE'',UPPER(HAOUT.name) -- Added UPPER by RDARASI for BUG# 8774489
,UPPER(PPF.employee_number) -- Added UPPER by RDARASI for BUG# 8774489
)';
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'RANGE_PERSON_ID';
SELECT PJWREV.assignment_id
,PJWREV.effective_date
FROM per_assignments_f PAA
,per_people_f PAP
,pay_assignment_actions PAS
,per_jp_wrkreg_emp_v PJWREV
,per_periods_of_service PPOF
,pay_population_ranges PPR
,pay_payroll_actions PPA
,hr_all_organization_units HAOU
WHERE PAA.person_id = PAP.person_id
AND PPA.payroll_action_id = PPR.payroll_action_id
AND PPA.payroll_action_id = p_payroll_action_id
AND PPR.chunk_number = p_chunk
AND PPR.person_id = PAP.person_id
AND PAS.assignment_id = PAA.assignment_id
AND HAOU.organization_id = PAA.organization_id
AND PAS.payroll_action_id = p_payroll_action_id_arch
AND PPOF.person_id = PAP.person_id
AND PJWREV.assignment_action_id = PAS.assignment_action_id
AND PJWREV.assignment_id = PAS.assignment_id
AND PAA.business_group_id = p_business_group_id
AND PAA.organization_id = NVL(p_organization_id,PAA.organization_id)
AND NVL(PAA.location_id,0) = NVL(p_location_id,NVL( PAA.location_id,0))
AND PAA.primary_flag = 'Y'
AND NVL(TRUNC(PPOF.actual_termination_date),p_effective_date) BETWEEN PAP.effective_start_date
AND PAP.effective_end_date
AND NVL(TRUNC(PPOF.actual_termination_date),p_effective_date) BETWEEN PAA.effective_start_date
AND PAA.effective_end_date
AND (( NVL(p_include_term_flag,'N') = 'Y'
AND( (PJWREV.terminate_flag = 'C'
AND ((PPOF.actual_termination_date IS NULL AND p_effective_date > = PPOF.DATE_START)
OR (PPOF.actual_termination_date > = p_effective_date AND p_effective_date > = PPOF.DATE_START )))
OR ( PJWREV.terminate_flag = 'T'
AND TRUNC(PPOF.actual_termination_date) BETWEEN p_term_eff_date_from
AND p_term_eff_date_to)
)
)
OR
( NVL(p_include_term_flag,'N') = 'N'
AND PJWREV.terminate_flag = 'C'
AND ((PPOF.actual_termination_date IS NULL AND p_effective_date > = PPOF.DATE_START)
OR (PPOF.actual_termination_date > = p_effective_date AND p_effective_date > = PPOF.DATE_START ))
)
);
SELECT PJWREV.assignment_id
,PJWREV.effective_date
FROM per_assignments_f PAA
,per_people_f PAP
,pay_assignment_actions PAS
,per_jp_wrkreg_emp_v PJWREV
,per_periods_of_service PPOF
,hr_all_organization_units HAOU
WHERE PAA.person_id = PAP.person_id
AND PAA.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND PAS.assignment_id = PAA.assignment_id
AND PAS.payroll_action_id = p_payroll_action_id_arch
AND PPOF.person_id = PAP.person_id
AND PJWREV.assignment_action_id = PAS.assignment_action_id
AND PJWREV.assignment_id = PAS.assignment_id
AND HAOU.organization_id = PAA.organization_id
AND PAA.business_group_id = p_business_group_id
AND PAA.organization_id = NVL(p_organization_id,PAA.organization_id)
AND NVL(PAA.location_id,0) = NVL(p_location_id,NVL( PAA.location_id,0))
AND PAA.primary_flag = 'Y'
AND NVL(TRUNC(PPOF.actual_termination_date),p_effective_date) BETWEEN PAP.effective_start_date
AND PAP.effective_end_date
AND NVL(TRUNC(PPOF.actual_termination_date),p_effective_date) BETWEEN PAA.effective_start_date
AND PAA.effective_end_date
AND (( NVL(p_include_term_flag,'N') = 'Y'
AND( (PJWREV.terminate_flag = 'C'
AND ((PPOF.actual_termination_date IS NULL AND p_effective_date > = PPOF.DATE_START)
OR (PPOF.actual_termination_date > = p_effective_date AND p_effective_date > = PPOF.DATE_START )))
OR ( PJWREV.terminate_flag = 'T'
AND TRUNC(PPOF.actual_termination_date) BETWEEN p_term_eff_date_from
AND p_term_eff_date_to)
)
)
OR
( NVL(p_include_term_flag,'N') = 'N'
AND PJWREV.terminate_flag = 'C'
AND ((PPOF.actual_termination_date IS NULL AND p_effective_date > = PPOF.DATE_START)
OR (PPOF.actual_termination_date > = p_effective_date AND p_effective_date > = PPOF.DATE_START ))
)
);
SELECT legislative_parameters
INTO lc_legislative_parameters
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT pay_assignment_actions_s.nextval
INTO ln_assact
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO ln_assact
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO ln_assact
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO ln_assact
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO ln_assact
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO ln_assact
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO ln_assact
FROM dual;
SELECT pay_assignment_actions_s.nextval
INTO ln_assact
FROM dual;
SELECT PJWREV.FULL_NAME_KANA
,PJWREV.FULL_NAME_KANJI
,PJWREV.DATE_OF_BIRTH
,DECODE(PJWREV.GENDER,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'SEX' AND lookup_code= PJWREV.GENDER)) GENDER
,SUBSTR(PJWREV.POSTAL_CODE,1,3)||NVL2(PJWREV.POSTAL_CODE,'-',' ')|| SUBSTR(PJWREV.POSTAL_CODE,4) POSTAL_CODE -- added by rdarasi for Bug #8814071
,PJWREV.ADDRESS_LINE1||' '||pjwrev.ADDRESS_LINE2||' '||pjwrev.ADDRESS_LINE3 Address
,PJWREV.REGION1||PJWREV.REGION2||PJWREV.REGION3 Address_kana
,PJWREV.KIND_OF_BUSINESS
,PJWREV.HIRE_DATE
,PJWREV.TERMINATION_DATE
,DECODE(PJWREV.TERMINATION_REASON,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'LEAV_REAS' AND lookup_code= PJWREV.TERMINATION_REASON)) TERMINATION_REASON
,PJWREV.DATE_OF_DEATH
,PJWREV.EMPLOYEE_NUMBER
,PJWREV.EFFECTIVE_DATE
FROM per_jp_wrkreg_emp_v PJWREV
WHERE PJWREV.assignment_action_id = p_mag_asg_action_id;
SELECT PJWRGHV.POSITION
,PJWRGHV.JOB
,PJWRGHV.START_DATE
,DECODE(PJWRGHV.END_DATE,TO_DATE('12/31/4712','mm/dd/yyyy'),null,PJWRGHV.END_DATE) END_DATE
,PJWRGHV.ORGANIZATION
FROM per_jp_wrkreg_job_v PJWRGHV
WHERE PJWRGHV.assignment_action_id = p_mag_asg_action_id
ORDER BY PJWRGHV.START_DATE DESC,PJWRGHV.END_DATE DESC;
SELECT PJWPJV.COMPANY_NAME
,PJWPJV.START_DATE
,PJWPJV.END_DATE
,PJWPJV.JOB
FROM per_jp_wrkreg_prev_job_v PJWPJV
WHERE pjwpjv.assignment_action_id = p_mag_asg_action_id
ORDER BY PJWPJV.START_DATE DESC,PJWPJV.END_DATE DESC;
SELECT PJWRAI.ADDITIONAL_INFORMATION1
,PJWRAI.ADDITIONAL_INFORMATION2
,PJWRAI.ADDITIONAL_INFORMATION3
,PJWRAI.ADDITIONAL_INFORMATION4
,PJWRAI.ADDITIONAL_INFORMATION5
,PJWRAI.ADDITIONAL_INFORMATION6
,PJWRAI.ADDITIONAL_INFORMATION7
,PJWRAI.ADDITIONAL_INFORMATION8
,PJWRAI.ADDITIONAL_INFORMATION9
,PJWRAI.ADDITIONAL_INFORMATION10
,PJWRAI.ADDITIONAL_INFORMATION11
,PJWRAI.ADDITIONAL_INFORMATION12
,PJWRAI.ADDITIONAL_INFORMATION13
,PJWRAI.ADDITIONAL_INFORMATION14
,PJWRAI.ADDITIONAL_INFORMATION15
,PJWRAI.ADDITIONAL_INFORMATION16
,PJWRAI.ADDITIONAL_INFORMATION17
,PJWRAI.ADDITIONAL_INFORMATION18
,PJWRAI.ADDITIONAL_INFORMATION19
,PJWRAI.ADDITIONAL_INFORMATION20
,PJWRAI.ADDITIONAL_INFORMATION21
,PJWRAI.ADDITIONAL_INFORMATION22
,PJWRAI.ADDITIONAL_INFORMATION23
,PJWRAI.ADDITIONAL_INFORMATION24
,PJWRAI.ADDITIONAL_INFORMATION25
,PJWRAI.ADDITIONAL_INFORMATION26
,PJWRAI.ADDITIONAL_INFORMATION27
,PJWRAI.ADDITIONAL_INFORMATION28
,PJWRAI.ADDITIONAL_INFORMATION29
,PJWRAI.ADDITIONAL_INFORMATION30
FROM per_jp_wrkreg_extra_info_v PJWRAI
WHERE PJWRAI.assignment_action_id = p_mag_asg_action_id;
SELECT HOI.org_information3
,HOI.org_information4
,HOI.org_information7
FROM hr_organization_information HOI
WHERE HOI.org_information_context = 'JP_REPORTS_ADDITIONAL_INFO'
AND HOI.org_information1 = 'JPEMPLDETAILSREPORT'
AND HOI.organization_id = p_business_group_id
AND HOI.org_information3 = p_info_type
AND p_effective_date BETWEEN fnd_date.canonical_to_date(HOI.org_information5)
AND fnd_date.canonical_to_date(HOI.org_information6);
vXMLTable.DELETE;
SELECT legislative_parameters
INTO lc_legislative_parameters
FROM pay_payroll_actions
WHERE payroll_action_id = ln_cur_pact;
SELECT PAA1.assignment_action_id
INTO ln_old_assact_id
FROM pay_assignment_actions PAA,
pay_assignment_actions PAA1
WHERE PAA.assignment_action_id = ln_cur_assact
AND PAA.assignment_id = PAA1.assignment_id
AND PAA1.payroll_action_id = ln_pact_id;
vxmltable.DELETE; -- delete the pl/sql table