[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PJEDPJV.assignment_id
,PJEDPJV.company_name
,DECODE(PJEDPJV.employee_category,'REHIRE','*','')
||TO_CHAR(PJEDPJV.start_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''') start_date
,DECODE(TO_CHAR(PJEDPJV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial'''),'H24.12.31',NULL
,TO_CHAR(PJEDPJV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''')) end_date
FROM per_jp_empdet_prev_job_v PJEDPJV
WHERE PJEDPJV.assignment_action_id = p_mag_asg_action_id
AND (PJEDPJV.employee_category IS NULL OR PJEDPJV.employee_category <> 'REHIRE')
ORDER BY PJEDPJV.end_date;
SELECT fnd_number.canonical_to_number(pay_core_utils.get_parameter('PACTID',legislative_parameters)) payroll_action_id
,fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASETID',legislative_parameters)) assignment_set_id
,pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id
,pay_core_utils.get_parameter('ORG',legislative_parameters) organization_id
,pay_core_utils.get_parameter('LOC',legislative_parameters) location_id
,TO_DATE(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') effective_date
,NVL(pay_core_utils.get_parameter('IOH',legislative_parameters),'Y') include_org_hierarchy
,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_date_from
,TO_DATE(pay_core_utils.get_parameter('TEDT',legislative_parameters),'YYYY/MM/DD') term_eff_date_to
,pay_core_utils.get_parameter('IMG',legislative_parameters) img_display
,pay_core_utils.get_parameter('S1',legislative_parameters) sort_order_1
,pay_core_utils.get_parameter('S2',legislative_parameters) sort_order_2
,pay_core_utils.get_parameter('S3',legislative_parameters) sort_order_3
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 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 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_date_from||'''
AND '''||gr_parameters.term_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'',PPF.full_name
,''ORGANIZATION_CODE'',HAOU.name
,PPF.employee_number
)
,DECODE('''||gr_parameters.sort_order_2||''',''EMPLOYEE_NAME'',PPF.full_name
,''ORGANIZATION_CODE'',HAOU.name
,PPF.employee_number
)
,DECODE('''||gr_parameters.sort_order_3||''',''EMPLOYEE_NAME'',PPF.full_name
,''ORGANIZATION_CODE'',HAOU.name
,PPF.employee_number
)';
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
ORDER BY DECODE('''||gr_parameters.sort_order_1||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name)
,''ORGANIZATION_CODE'',UPPER(HAOUT.name)
,UPPER(PPF.employee_number)
)
,DECODE('''||gr_parameters.sort_order_2||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name)
,''ORGANIZATION_CODE'',UPPER(HAOUT.name)
,UPPER(PPF.employee_number)
)
,DECODE('''||gr_parameters.sort_order_3||''',''EMPLOYEE_NAME'',UPPER(PPF.last_name || '' '' || PPF.first_name)
,''ORGANIZATION_CODE'',UPPER(HAOUT.name)
,UPPER(PPF.employee_number)
)';
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'RANGE_PERSON_ID';
SELECT PJEDV.assignment_id
,PJEDV.effective_date
FROM per_assignments_f PAA
,per_people_f PAP
,pay_assignment_actions PAS
,per_jp_empdet_emp_v PJEDV
,per_periods_of_service PPOF
,pay_population_ranges PPR
,pay_payroll_actions PPA
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 PAS.payroll_action_id = p_payroll_action_id_arch
AND PPOF.person_id = PAP.person_id
AND PJEDV.assignment_action_id = PAS.assignment_action_id
AND PJEDV.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( (PJEDV.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 ( PJEDV.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 PJEDV.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 PJEDV.assignment_id
,PJEDV.effective_date
FROM per_assignments_f PAA
,per_people_f PAP
,pay_assignment_actions PAS
,per_periods_of_service PPS
,per_jp_empdet_emp_v PJEDV
WHERE PAA.person_id = PAP.person_id
AND PAA.person_id BETWEEN p_start_person_id
AND p_end_person_id
AND PAP.person_id = PPS.person_id
AND PPS.period_of_service_id = PAA.period_of_service_id
AND PAS.assignment_id = PAA.assignment_id
AND PAS.payroll_action_id = p_payroll_action_id
AND PJEDV.assignment_action_id = PAS.assignment_action_id
AND PJEDV.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(PPS.actual_termination_date),p_effective_date) BETWEEN PAP.effective_start_date
AND PAP.effective_end_date
AND NVL(TRUNC(PPS.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(( ( (PPS.actual_termination_date IS NULL AND p_effective_date > = PPS.DATE_START)
OR (PPS.actual_termination_date > = p_effective_date AND p_effective_date > = PPS.DATE_START ))
AND PJEDV.terminate_flag = 'C'
)
OR
(TRUNC(PPS.actual_termination_date) BETWEEN p_term_eff_date_from
AND p_term_eff_date_to
AND PJEDV.terminate_flag = 'T'
)
)
)
OR
( NVL(p_include_term_flag,'N') = 'N'
AND ((PPS.actual_termination_date IS NULL AND p_effective_date > = PPS.DATE_START)
OR (PPS.actual_termination_date > = p_effective_date AND p_effective_date > = PPS.DATE_START ))
AND PJEDV.terminate_flag = 'C'
)
);
SELECT legislative_parameters
INTO lc_legislative_parameters
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT PPA.business_group_id
INTO gn_bg_id
FROM pay_payroll_actions PPA
WHERE PPA.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 PJEDV.full_name_kana
,PJEDV.full_name_kanji
,PJEDV.date_of_birth
,DECODE(PJEDV.gender,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'SEX' AND lookup_code=PJEDV.gender)) gender
,SUBSTR(PJEDV.postal_code,1,3)||NVL2(PJEDV.postal_code,'-',' ')|| SUBSTR(PJEDV.postal_code,4) postal_code -- Changed by rdarasi for Bug# 8814075
,PJEDV.address_line1||' '||PJEDV.address_line2||' '||PJEDV.address_line3 address
,PJEDV.region1||' '||PJEDV.region2||' '||PJEDV.region3 address_kana
,PJEDV.address_line1
,PJEDV.address_line2
,PJEDV.address_line3
,PJEDV.country
,PJEDV.hire_date
,PJEDV.kind_of_business
,PJEDV.termination_date
,DECODE(PJEDV.termination_reason,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'LEAV_REAS' AND lookup_code= PJEDV.termination_reason)) termination_reason -- 8740607
,PJEDV.hi_num
,SUBSTR(PJEDV.wp_num,1,4)||NVL2(PJEDV.wp_num,'-',' ')|| SUBSTR(PJEDV.wp_num,5) wp_num -- changed by rdarasi for Bug# 8765317
,PJEDV.wpf_num
,SUBSTR(PJEDV.ui_num,1,4)||NVL2(PJEDV.ui_num,'-',' ')|| SUBSTR(PJEDV.ui_num,5,6)||NVL2(PJEDV.ui_num,'-',' ')|| SUBSTR(PJEDV.ui_num,11) ui_num -- changed by rdarasi for bug# 8765317
,TO_CHAR(PJEDV.term_allowance_amt,'99G999G999') term_allowance_amt
,PJEDV.payment_date_term
,PJEDV.hi_qualified_date
,PJEDV.wp_qualified_date
,PJEDV.wpf_qualified_date
,PJEDV.ui_qualified_date
,PJEDV.terminate_flag
,PJEDV.employee_number
,PJEDV.effective_date
FROM per_jp_empdet_emp_v PJEDV
WHERE PJEDV.assignment_action_id = p_mag_asg_action_id;
SELECT PJEDPJV.company_name
,DECODE(PJEDPJV.employee_category,'REHIRE','*','')
||TO_CHAR(PJEDPJV.start_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''') start_date -- Changed By RDARASI as per the Bug 8740649
,DECODE(TO_CHAR(PJEDPJV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial'''),'H24.12.31',NULL
,TO_CHAR(PJEDPJV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''')) end_date
FROM per_jp_empdet_prev_job_v PJEDPJV
WHERE PJEDPJV.assignment_action_id = p_mag_asg_action_id
ORDER BY PJEDPJV.start_date;
SELECT PJEDPV.phone_home
,PJEDPV.phone_mobile
,PJEDPV.phone_work
FROM per_jp_empdet_phone_v PJEDPV
WHERE PJEDPV.assignment_action_id = p_mag_asg_action_id;
SELECT PJEEV.school_name
,PJEEV.school_name_kana
,PJEEV.faculty_name
,PJEEV.faculty_name_kana
,PJEEV.department_name
,PJEEV.graduation_date graduation_date
FROM per_jp_empdet_education_det_v PJEEV
WHERE PJEEV.assignment_action_id = p_mag_asg_action_id
ORDER BY PJEEV.graduation_date;
SELECT PJEQV.type
,PJEQV.title
,PJEQV.status
,PJEQV.grade
,PJEQV.establishment
,PJEQV.license_number
,TO_CHAR(PJEQV.start_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''') start_date
,DECODE(TO_CHAR(PJEQV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial'''),'H24.12.31',NULL
,TO_CHAR(PJEQV.end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''')) end_date
FROM per_jp_empdet_qualifications_v PJEQV
WHERE PJEQV.assignment_action_id = p_mag_asg_action_id
ORDER BY PJEQV.start_date;
SELECT PJEAV.organization_name
,PJEAV.job
,PJEAV.position
,PJEAV.grade
,PJEAV.start_date
,DECODE(PJEAV.end_date,TO_DATE('12/31/4712','mm/dd/yyyy'),null,PJEAV.end_date) end_date
,PJEAV.assignment_number
FROM per_jp_empdet_assignments_v PJEAV
WHERE PJEAV.assignment_action_id = p_mag_asg_action_id
ORDER BY PJEAV.start_date;
SELECT PJECIV.full_name_kana
,PJECIV.full_name_kanji
,DECODE(PJECIV.relationship,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'CONTACT' AND lookup_code=PJECIV.relationship)) relationship
,DECODE(PJECIV.gender,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'SEX' AND lookup_code=PJECIV.gender)) gender
,TO_CHAR(PJECIV.birth_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''') birth_date
,PJECIV.age
,DECODE(PJECIV.primary_contact,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'YES_NO' AND lookup_code=PJECIV.primary_contact)) primary_contact
,DECODE(PJECIV.dependent,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'YES_NO' AND lookup_code=PJECIV.dependent)) dependent
,DECODE(PJECIV.shared_residence,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'YES_NO' AND lookup_code=PJECIV.shared_residence)) shared_residence
,PJECIV.sequence
,DECODE(PJECIV.household_head,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'YES_NO' AND lookup_code=PJECIV.household_head)) household_head
,DECODE(PJECIV.si_itax,NULL,NULL,(SELECT meaning FROM hr_lookups WHERE lookup_type = 'YES_NO' AND lookup_code=PJECIV.si_itax)) si_itax
FROM per_jp_empdet_contact_info_v PJECIV
WHERE PJECIV.assignment_action_id = p_mag_asg_action_id
ORDER BY PJECIV.full_name_kana
,PJECIV.full_name_kanji;
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);
SELECT PJEDAI.additional_information1
,PJEDAI.additional_information2
,PJEDAI.additional_information3
,PJEDAI.additional_information4
,PJEDAI.additional_information5
,PJEDAI.additional_information6
,PJEDAI.additional_information7
,PJEDAI.additional_information8
,PJEDAI.additional_information9
,PJEDAI.additional_information10
,PJEDAI.additional_information11
,PJEDAI.additional_information12
,PJEDAI.additional_information13
,PJEDAI.additional_information14
,PJEDAI.additional_information15
,PJEDAI.additional_information16
,PJEDAI.additional_information17
,PJEDAI.additional_information18
,PJEDAI.additional_information19
,PJEDAI.additional_information20
,PJEDAI.additional_information21
,PJEDAI.additional_information22
,PJEDAI.additional_information23
,PJEDAI.additional_information24
,PJEDAI.additional_information25
,PJEDAI.additional_information26
,PJEDAI.additional_information27
,PJEDAI.additional_information28
,PJEDAI.additional_information29
,PJEDAI.additional_information30
FROM per_jp_wrkreg_extra_info_v PJEDAI
WHERE PJEDAI.assignment_action_id = p_mag_asg_action_id;
vXMLTable.DELETE;
SELECT PIMG.parent_id
,PIMG.image
FROM per_images PIMG
WHERE PIMG.parent_id = (SELECT DISTINCT PPF.person_id
FROM per_assignments_f PPF
WHERE PPF.assignment_id = p_assignment_id
);
SELECT PJEDV.assignment_id
FROM per_jp_empdet_emp_v PJEDV
WHERE PJEDV.assignment_action_id = p_mag_asg_action_id;
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