[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'RANGE_PERSON_ID';
SELECT pay_core_utils.get_parameter('PAYROLL_ACTION_ID',legislative_parameters) payroll_action_id
,pay_core_utils.get_parameter('ASSETID',legislative_parameters) assignment_set_id
,pay_core_utils.get_parameter('REP_GROUP',legislative_parameters) rep_group
,pay_core_utils.get_parameter('REP_CAT',legislative_parameters) rep_cat
,pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id
,pay_core_utils.get_parameter('SUB',legislative_parameters) subject_year
,TO_DATE(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD') effective_date
,pay_core_utils.get_parameter('PAY_ARCH',legislative_parameters) payroll_arch
,pay_core_utils.get_parameter('SORT1',legislative_parameters) sort_order
FROM pay_payroll_actions PPA
WHERE PPA.payroll_action_id = p_payroll_action_id;
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;
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 ';
SELECT DISTINCT PJUIEV.assignment_id
,PJUIEV.termination_date
FROM per_assignments_f PAA
,per_people_f PAP
,pay_assignment_actions PAS
,pay_jp_uite_emp_v PJUIEV
,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 PPR.person_id = PAP.person_id
AND PAS.assignment_id = PAA.assignment_id
AND HAOU.organization_id = PAA.organization_id
AND PPOF.person_id = PAP.person_id
AND PJUIEV.assignment_action_id = PAS.assignment_action_id
AND PJUIEV.assignment_id = PAS.assignment_id
AND PPA.payroll_action_id = p_payroll_action_id
AND PPR.chunk_number = p_chunk
AND PAS.payroll_action_id = p_payroll_action_id_arch
AND PAA.business_group_id = p_business_group_id
AND TRUNC(NVL(PPOF.actual_termination_date,PPOF.projected_termination_date)) BETWEEN PAP.effective_start_date
AND PAP.effective_end_date
AND TRUNC(NVL(PPOF.actual_termination_date,PPOF.projected_termination_date)) BETWEEN PAA.effective_start_date
AND PAA.effective_end_date; -- Bug 9728577
SELECT DISTINCT PJUIEV.assignment_id
,PJUIEV.termination_date
FROM per_assignments_f PAA
,per_people_f PAP
,pay_assignment_actions PAS
,pay_jp_uite_emp_v PJUIEV
,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 PJUIEV.assignment_action_id = PAS.assignment_action_id
AND PJUIEV.assignment_id = PAS.assignment_id
AND HAOU.organization_id = PAA.organization_id
AND PAA.business_group_id = p_business_group_id
AND TRUNC(NVL(PPOF.actual_termination_date,PPOF.projected_termination_date)) BETWEEN PAP.effective_start_date
AND PAP.effective_end_date
AND TRUNC(NVL(PPOF.actual_termination_date,PPOF.projected_termination_date)) BETWEEN PAA.effective_start_date
AND PAA.effective_end_date; -- Bug 9728577
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 PJUIEV.employee_number
, PJUIEV.insured_number INSURED_NUMBER
, PJUIEV.last_name_kana
, PJUIEV.first_name_kana
, PJUIEV.last_name
, PJUIEV.first_name
, TO_CHAR(PJUIEV.termination_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE
, TO_CHAR(PJUIEV.termination_date, 'YY', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE_YY
, TO_CHAR(PJUIEV.termination_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE_MM
, TO_CHAR(PJUIEV.termination_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE_DD
, TO_CHAR(PJUIEV.termination_date + 1, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') FOLLOWING_DATE_TERM
, TO_CHAR(PJUIEV.termination_date + 1, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') FOLLOWING_DATE_TERM_MM
, TO_CHAR(PJUIEV.termination_date + 1, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') FOLLOWING_DATE_TERM_DD
, SUBSTR(PJUIEV.postal_code,1,3)||NVL2(PJUIEV.postal_code,'-',' ')|| SUBSTR(PJUIEV.postal_code,4) POSTAL_CODE -- changed as per the bug#9648063
, PJUIEV.address_line1
, PJUIEV.address_line2
, PJUIEV.address_line3
, PJUIEV.phone_number
FROM pay_jp_uite_emp_v PJUIEV
WHERE PJUIEV.assignment_action_id = p_mag_asg_action_id;
SELECT PJUIOV.location_number LOCATION_NUMBER
, PJUIOV.location_name
, PJUIOV.loc_address_line1
, PJUIOV.loc_address_line2
, PJUIOV.loc_address_line3
, PJUIOV.loc_phone_number
, PJUIOV.employer_full_name
, PJUIOV.employer_address_line1
, PJUIOV.employer_address_line2
, PJUIOV.employer_address_line3
, PJUIOV.employer_name -- Added as per the bug#9648053
FROM pay_jp_uite_office_v PJUIOV
WHERE PJUIOV.payroll_action_id = p_payroll_action_id;
SELECT TO_CHAR(PJUISV.payment_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') PAYMENT_DATE
, TO_CHAR(PJUISV.ins_period_start_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_START_DATE
, TO_CHAR(PJUISV.ins_period_start_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_START_DATE_MM
, TO_CHAR(PJUISV.ins_period_start_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_START_DATE_DD
, TO_CHAR(PJUISV.ins_period_end_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_END_DATE
, TO_CHAR(PJUISV.ins_period_end_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_END_DATE_MM
, TO_CHAR(PJUISV.ins_period_end_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') INS_PERIOD_END_DATE_DD
, PJUISV.ins_period_base_days
, TO_CHAR(PJUISV.pay_period_start_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_START_DATE
, TO_CHAR(PJUISV.pay_period_start_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_START_DATE_MM
, TO_CHAR(PJUISV.pay_period_start_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_START_DATE_DD
, TO_CHAR(PJUISV.pay_period_end_date, 'YY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_END_DATE
, TO_CHAR(PJUISV.pay_period_end_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_END_DATE_MM
, TO_CHAR(PJUISV.pay_period_end_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') PAY_PERIOD_END_DATE_DD
, PJUISV.pay_period_base_days
, PJUISV.wage_amount_a
, PJUISV.wage_amount_b
, PJUISV.wage_amount_total
, PJUISV.remarks
, PJUISV.exclude_period
, PJUISV.line_number
FROM pay_jp_uite_sal_v PJUISV
WHERE PJUISV.assignment_action_id = p_mag_asg_action_id
AND PJUISV.exclude_period = 'N'
ORDER BY PJUISV.line_number; -- Added for the Bug# 9648278
SELECT PJUITV.termination_reason
, PJUITV.concrete_circumstance1
, PJUITV.concrete_circumstance2
FROM pay_jp_uite_term_v PJUITV
WHERE PJUITV.assignment_action_id = p_mag_asg_action_id;
SELECT PJUIIV.wage_instruction1
, PJUIIV.wage_instruction2
FROM PAY_JP_UITE_INSTR_V PJUIIV
WHERE PJUIIV.assignment_action_id = p_mag_asg_action_id;
vXMLTable.DELETE;
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 TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PPF.effective_start_date
AND PPF.effective_end_date
AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PAF.effective_start_date
AND PAF.effective_end_date
ORDER BY '||lc_order_by; -- Bug 9728577