[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('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
,TO_DATE(pay_core_utils.get_parameter('EFFDATE',legislative_parameters),'YYYY/MM/DD') effective_date
,pay_core_utils.get_parameter('SUB',legislative_parameters) subject_year
,pay_core_utils.get_parameter('ITWA',legislative_parameters) withholding_agent
,pay_core_utils.get_parameter('SORT_ORDER',legislative_parameters) sort_order
,TO_DATE(pay_core_utils.get_parameter('TEDF',legislative_parameters),'YYYY/MM/DD') termination_date_from
,TO_DATE(pay_core_utils.get_parameter('TEDT',legislative_parameters),'YYYY/MM/DD') termination_date_to
,pay_core_utils.get_parameter('ASS_SETID',legislative_parameters) ass_setid
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 assignment_id
INTO ln_assignment_id
FROM pay_assignment_actions PAA
WHERE PAA.assignment_action_id = ln_cur_assact;
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 PJIWTV.assignment_id
,PJIWTV.term_payment_date
FROM per_assignments_f PAA
,per_people_f PAP
,pay_assignment_actions PAS
,pay_jp_iwht_tax_v PJIWTV
,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 PJIWTV.assignment_action_id = PAS.assignment_action_id
AND PJIWTV.assignment_id = PAS.assignment_id
AND PPR.chunk_number = p_chunk
AND PAA.business_group_id = p_business_group_id
AND NVL(TRUNC(PPOF.actual_termination_date),PJIWTV.term_payment_date) BETWEEN PAP.effective_start_date
AND PAP.effective_end_date
AND NVL(TRUNC(PPOF.actual_termination_date),PJIWTV.term_payment_date) BETWEEN PAA.effective_start_date
AND PAA.effective_end_date
AND TO_CHAR(PJIWTV.term_payment_date,'YYYY') = p_subject_year
AND NVL(PAY_JP_IWHT_ARCH_PKG.get_with_hold_agent(PAA.assignment_id,PJIWTV.term_payment_date),-999) = NVL(p_withholding_agent,NVL(PAY_JP_IWHT_ARCH_PKG.get_with_hold_agent(PAA.assignment_id,PJIWTV.term_payment_date),-999))
AND ( TRUNC(PPOF.actual_termination_date) BETWEEN NVL(p_termination_date_from,PPOF.actual_termination_date)
AND NVL(p_termination_date_to,PPOF.actual_termination_date)
OR --PPOF.actual_termination_date IS NULL -- commented for the bug #9527198
(p_termination_date_from IS NULL AND p_termination_date_to IS NULL));
SELECT DISTINCT PJIWTV.assignment_id
,PJIWTV.term_payment_date
FROM per_assignments_f PAA
,per_people_f PAP
,pay_assignment_actions PAS
,pay_jp_iwht_tax_v PJIWTV
,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 PPOF.person_id = PAP.person_id
AND PJIWTV.assignment_action_id = PAS.assignment_action_id
AND PJIWTV.assignment_id = PAS.assignment_id
AND HAOU.organization_id = PAA.organization_id
AND PAA.business_group_id = p_business_group_id
AND NVL(TRUNC(PPOF.actual_termination_date),PJIWTV.term_payment_date) BETWEEN PAP.effective_start_date
AND PAP.effective_end_date
AND NVL(TRUNC(PPOF.actual_termination_date),PJIWTV.term_payment_date) BETWEEN PAA.effective_start_date
AND PAA.effective_end_date
AND TO_CHAR(PJIWTV.term_payment_date,'YYYY') = p_subject_year
AND NVL(PAY_JP_IWHT_ARCH_PKG.get_with_hold_agent(PAA.assignment_id,PJIWTV.term_payment_date),-999) = NVL(p_withholding_agent,NVL(PAY_JP_IWHT_ARCH_PKG.get_with_hold_agent(PAA.assignment_id,PJIWTV.term_payment_date),-999))
AND ( TRUNC(PPOF.actual_termination_date) BETWEEN NVL(p_termination_date_from,PPOF.actual_termination_date)
AND NVL(p_termination_date_to,PPOF.actual_termination_date)
OR --PPOF.actual_termination_date IS NULL -- commented for the bug #9527198
(p_termination_date_from IS NULL AND p_termination_date_to IS NULL));
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 PJIWEV.employee_number
, PJIWEV.last_name_kana
, PJIWEV.first_name_kana
, PJIWEV.last_name
, PJIWEV.first_name
, PJIWEV.district_code
, PJIWEV.address_line1
, PJIWEV.address_line2
, PJIWEV.address_line3
, PJIWEV.district_code_1stjan
, PJIWEV.address_line1_1stjan
, PJIWEV.address_line2_1stjan
, PJIWEV.address_line3_1stjan
, TO_CHAR(PJIWEV.hire_date, 'EYY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') HIRE_DATE
, TO_CHAR(PJIWEV.hire_date, 'EYY', 'NLS_CALENDAR=''Japanese Imperial''') HIRE_DATE_YY
, TO_CHAR(PJIWEV.hire_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') HIRE_DATE_MM
, TO_CHAR(PJIWEV.hire_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') HIRE_DATE_DD
, TO_CHAR(PJIWEV.termination_date, 'EYY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE
, TO_CHAR(PJIWEV.termination_date, 'EYY', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE_YY
, TO_CHAR(PJIWEV.termination_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE_MM
, TO_CHAR(PJIWEV.termination_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') TERMINATION_DATE_DD
, PJIWEV.service_years
, PJIWEV.itax_organization_id
, PJIWEV.employer_name
, PJIWEV.employer_address_line1
, PJIWEV.employer_address_line2
, PJIWEV.employer_address_line3
, PJIWEV.employer_phone_number
, PJIWEV.description1
, PJIWEV.description2
, PJIWEV.assignment_action_id
FROM pay_jp_iwht_emp_v PJIWEV
WHERE PJIWEV.assignment_id = p_assignment_id;
SELECT PJIWTV.notification_submited
, PJIWTV.termination_payment_amount
, PJIWTV.with_holding_tax
, PJIWTV.muncipal_tax
, PJIWTV.prefectural_tax
, (PJIWTV.termination_income_deduction/10000) TERMINATION_INCOME_DEDUCTION
, TO_CHAR(PJIWTV.term_payment_date, 'EYY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') TERM_PAYMENT_DATE
, TO_CHAR(PJIWTV.term_payment_date, 'YY', 'NLS_CALENDAR=''Japanese Imperial''') TERM_PAYMENT_DATE_YY
, TO_CHAR(PJIWTV.term_payment_date, 'MM', 'NLS_CALENDAR=''Japanese Imperial''') TERM_PAYMENT_DATE_MM
, TO_CHAR(PJIWTV.term_payment_date, 'DD', 'NLS_CALENDAR=''Japanese Imperial''') TERM_PAYMENT_DATE_DD
, TO_CHAR(PJIWTV.date_earned, 'EYY MM DD', 'NLS_CALENDAR=''Japanese Imperial''') DATE_EARNED
FROM pay_jp_iwht_tax_v PJIWTV
WHERE PJIWTV.assignment_id = p_assignment_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
,pay_jp_iwht_emp_v PJIWEV
,pay_jp_iwht_tax_v PJIWTV
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),PJIWTV.term_payment_date) BETWEEN PPF.effective_start_date
AND PPF.effective_end_date
AND NVL(TRUNC(PPS.actual_termination_date),PJIWTV.term_payment_date) BETWEEN PAF.effective_start_date
AND PAF.effective_end_date
AND PJIWEV.assignment_id = PAA.assignment_id
AND PJIWTV.assignment_id = PAA.assignment_id
ORDER BY '||lc_order_by; -- changed for the Bug# 9569078