[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sql := ' 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 business_group_id
,effective_date
,fnd_number.canonical_to_number(pay_core_utils.get_parameter('ITAX_ORGANIZATION_ID',legislative_parameters))
,fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters))
,fnd_date.canonical_to_date(pay_core_utils.get_parameter('TERMINATION_DATE_FROM', legislative_parameters))
,fnd_date.canonical_to_date(pay_core_utils.get_parameter('TERMINATION_DATE_TO',legislative_parameters))
,fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters))
,pay_core_utils.get_parameter('REARCHIVE_FLAG',legislative_parameters)
FROM pay_payroll_actions PPA
WHERE PPA.payroll_action_id = p_payroll_action_id;
SELECT meaning
FROM hr_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
PROCEDURE delete_assact ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE)
--***************************************************************************
-- PROCEDURE
-- DELETE_ASSACT
--
-- DESCRIPTION
-- This procedure is used to set global contexts
--
-- ACCESS
-- PUBLIC
--
-- PARAMETERS
--==========
-- NAME TYPE DESCRIPTION
------------------- -------- ---------------------------------------
-- p_payroll_action_id IN This parameter passes Payroll Action Id
-- p_assignment_id IN This parameter passes Assignment Id
--
-- PREREQUISITES
-- None
--
-- CALLED BY
-- None
--***********************************************************************
IS
-- Local Variables
CURSOR lcu_action_information_id(p_assignment_id per_all_assignments_f.assignment_id%TYPE)
IS
SELECT PAI.object_version_number
,PAI.action_information_id
,PAC.assignment_action_id
FROM pay_action_information PAI
,pay_assignment_actions PAC
,pay_payroll_actions PPA
WHERE PAI.action_context_id = PAC.assignment_action_id
AND PAC.assignment_id = p_assignment_id
AND PAC.payroll_action_id = PPA.payroll_action_id
AND PAI.action_context_type = 'AAP'
AND PPA.report_type = gc_report_type;
lc_procedure := gc_package||'delete_assact';
pay_action_information_api.delete_action_information
( p_validate => FALSE
,p_action_information_id => lr_emp_assignment_det.action_information_id
,p_object_version_number => lr_emp_assignment_det.object_version_number);
END delete_assact;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'RANGE_PERSON_ID';
SELECT PAF.assignment_id
,PPS.actual_termination_date
FROM per_assignments_f PAF
,per_people_f PPF
,per_periods_of_service PPS
,pay_population_ranges PPR
,pay_payroll_actions PPA
WHERE PAF.person_id = PPF.person_id
AND PPF.person_id = PPS.person_id
AND PPA.payroll_action_id = p_payroll_action_id
AND PPA.payroll_action_id = PPR.payroll_action_id
AND PPR.chunk_number = p_chunk
AND PPR.person_id = PPF.person_id
AND PAF.business_group_id = p_business_group_id
AND PPS.period_of_service_id = NVL(PAF.period_of_service_id,PPS.period_of_service_id)
AND NVL(PAF.payroll_id,-999) = NVL(p_payroll_id,NVL(PAF.payroll_id,-999))
AND NVL(get_with_hold_agent(PAF.assignment_id,NVL(PPS.actual_termination_date,p_effective_date)),-999) = NVL(p_with_hold_id,NVL(get_with_hold_agent(PAF.assignment_id,NVL(PPS.actual_termination_date,p_effective_date)),-999))
AND ( TRUNC(PPS.actual_termination_date) BETWEEN TRUNC(NVL(p_termination_date_from,PPS.actual_termination_date)) AND TRUNC(NVL(p_termination_date_to,PPS.actual_termination_date))
OR
(p_termination_date_from IS NULL AND p_termination_date_to IS NULL)) -- #Bug 9527179
AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
AND EXISTS(SELECT 1
FROM pay_jp_pre_tax PPT,
pay_assignment_actions PAA,
pay_payroll_actions PPA
WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
AND PAA.assignment_id = PAF.assignment_id
AND PPA.business_group_id + 0 = p_business_group_id
AND PPA.action_type in ('R', 'Q', 'B', 'I')
AND PAA.payroll_action_id = PPA.payroll_action_id
AND PAA.action_status = 'C'
AND PPT.assignment_action_id = PAA.assignment_action_id
AND PPT.action_status = 'C'
AND PPT.salary_category = 'TERM'
AND NVL(PPT.itax_organization_id,-999) = NVL(NVL(p_with_hold_id,PPT.itax_organization_id),-999)
AND NOT EXISTS(
SELECT null
FROM pay_payroll_actions PPAV,
pay_assignment_actions PAAV,
pay_action_interlocks PAI
WHERE PAI.locked_action_id = PAA.assignment_action_id
AND PAAV.assignment_action_id = PAI.locking_action_id
AND PPAV.payroll_action_id = PAAV.payroll_action_id
AND PPAV.action_type = 'V'))
ORDER BY PAF.assignment_id;
SELECT PAF.assignment_id
,PPS.actual_termination_date
FROM per_assignments_f PAF
,per_people_f PPF
,per_periods_of_service PPS
WHERE PAF.person_id = PPF.person_id
AND PPF.person_id = PPS.person_id
AND PAF.business_group_id = p_business_group_id
AND PPS.period_of_service_id = NVL(PAF.period_of_service_id,PPS.period_of_service_id)
AND PPF.person_id BETWEEN p_start_person_id AND p_end_person_id
AND NVL(PAF.payroll_id,-999) = NVL(p_payroll_id,NVL(PAF.payroll_id,-999))
AND NVL(get_with_hold_agent(PAF.assignment_id,NVL(PPS.actual_termination_date,p_effective_date)),-999) = NVL(p_with_hold_id,NVL(get_with_hold_agent(PAF.assignment_id,NVL(PPS.actual_termination_date,p_effective_date)),-999))
AND ( TRUNC(PPS.actual_termination_date) BETWEEN TRUNC(NVL(p_termination_date_from,PPS.actual_termination_date)) AND TRUNC(NVL(p_termination_date_to,PPS.actual_termination_date))
OR
(p_termination_date_from IS NULL AND p_termination_date_to IS NULL)) -- #Bug 9527179
AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
AND EXISTS(SELECT 1
FROM pay_jp_pre_tax PPT,
pay_assignment_actions PAA,
pay_payroll_actions PPA
WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
AND PAA.assignment_id = PAF.assignment_id
AND PPA.business_group_id + 0 = p_business_group_id
AND PPA.action_type in ('R', 'Q', 'B', 'I')
AND PAA.payroll_action_id = PPA.payroll_action_id
AND PAA.action_status = 'C'
AND PPT.assignment_action_id = PAA.assignment_action_id
AND PPT.action_status = 'C'
AND PPT.salary_category = 'TERM'
AND NVL(PPT.itax_organization_id,-999) = NVL(NVL(p_with_hold_id,PPT.itax_organization_id),-999)
AND NOT EXISTS(
SELECT null
FROM pay_payroll_actions PPAV,
pay_assignment_actions PAAV,
pay_action_interlocks PAI
WHERE PAI.locked_action_id = PAA.assignment_action_id
AND PAAV.assignment_action_id = PAI.locking_action_id
AND PPAV.payroll_action_id = PAAV.payroll_action_id
AND PPAV.action_type = 'V'))
ORDER BY PAF.assignment_id;
SELECT pay_assignment_actions_s.NEXTVAL
FROM dual;
SELECT assignment_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id;
SELECT PPF.person_id person_id
,PPF.employee_number employee_number
,PPF.first_name first_name_kana
,PPF.last_name last_name_kana
,PPF.per_information19 first_name_kanji
,PPF.per_information18 last_name_kanji
,PPS.date_start hire_date
,PPS.actual_termination_date termination_date
,PAF.assignment_id assignment_id
,PAF.payroll_id payroll_id
,get_with_hold_agent(p_assignment_id,NVL(PPS.actual_termination_date,p_effective_date)) withhold_agent_id
,DECODE(PADR.address_id,NULL,PADC.town_or_city,PADR.town_or_city) district_code
,DECODE(PADR.address_id,NULL,PADC.address_line1 ,PADR.address_line1) address_line1
,DECODE(PADR.address_id,NULL,PADC.address_line2 ,PADR.address_line2) address_line2
,DECODE(PADR.address_id,NULL,PADC.address_line3 ,PADR.address_line3) address_line3
FROM per_people_f PPF
, per_assignments_f PAF
, per_periods_of_service PPS
, per_addresses PADR
, per_addresses PADC
WHERE PAF.person_id = PPF.person_id
AND PPS.person_id = PPF.person_id
AND PAF.assignment_id = p_assignment_id
AND PADR.person_id(+) = PPF.person_id
AND PADR.address_type(+) = 'JP_R'
AND TRUNC(p_effective_date) BETWEEN TRUNC(NVL(PADR.date_from(+),p_effective_date)) AND NVL(PADR.date_to(+),TO_DATE('31/12/4712','DD/MM/YYYY')) --bug #9554515
AND PADC.person_id(+) = PPF.person_id
AND PADC.address_type(+) = 'JP_C'
AND TRUNC(p_effective_date) BETWEEN TRUNC(NVL(PADC.date_from(+),p_effective_date)) AND NVL(PADC.date_to(+),TO_DATE('31/12/4712','DD/MM/YYYY')) --bug #9554515
AND PPS.period_of_service_id = NVL(PAF.period_of_service_id,PPS.period_of_service_id) -- #Bug 9569078
AND EXISTS(SELECT 1
FROM pay_jp_pre_tax PPT,
pay_assignment_actions PAA,
pay_payroll_actions PPA
WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
AND PAA.assignment_id = PAF.assignment_id
AND PPA.action_type in ('R', 'Q', 'B', 'I')
AND PAA.payroll_action_id = PPA.payroll_action_id
AND PAA.action_status = 'C'
AND PPT.assignment_action_id = PAA.assignment_action_id
AND PPT.action_status = 'C'
AND PPT.salary_category = 'TERM'
AND TRUNC(NVL(PPS.actual_termination_date,PPA.effective_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
AND TRUNC(NVL(PPS.actual_termination_date,PPA.effective_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
AND NOT EXISTS(
SELECT null
FROM pay_payroll_actions PPAV,
pay_assignment_actions PAAV,
pay_action_interlocks PAI
WHERE PAI.locked_action_id = PAA.assignment_action_id
AND PAAV.assignment_action_id = PAI.locking_action_id
AND PPAV.payroll_action_id = PAAV.payroll_action_id
AND PPAV.action_type = 'V'))
ORDER BY PAF.assignment_id,PPF.effective_start_date;
SELECT HOI.org_information1
,HOI.org_information6
,HOI.org_information7
,HOI.org_information8
,HOI.org_information12
FROM hr_all_organization_units HOU,
hr_organization_information HOI
WHERE HOU.organization_id = p_itax_organization_id
AND HOI.organization_id(+) = hou.organization_id
AND HOI.org_information_context(+) = 'JP_TAX_SWOT_INFO';
SELECT PAD.town_or_city jan_1st_district_code
,PAD.address_line1 jan_1st_address_line1
,PAD.address_line2 jan_1st_address_line2
,PAD.address_line3 jan_1st_address_line3
FROM per_addresses PAD
WHERE PAD.person_id = p_person_id
AND ((PAD.address_type = 'JP_R')
OR
(PAD.address_type = 'JP_C'))
AND p_effective_date BETWEEN NVL(PAD.date_from,p_effective_date) AND NVL(PAD.date_to,TO_DATE('31/12/4712','DD/MM/YYYY'));
SELECT NVL(sum(ppt.taxable_sal_amt + ppt.taxable_mat_amt), 0) termination_money
,NVL(sum(ppt.itax), 0) withholding_tax
,NVL(sum(ppt.sp_ltax_shi), 0) muncipal_tax -- Bug 9525922
,NVL(sum(ppt.sp_ltax_to), 0) prefectural_tax -- Bug 9525922
FROM pay_jp_pre_tax PPT,
pay_assignment_actions PAA,
pay_payroll_actions PPA
WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
AND PPA.business_group_id + 0 = p_business_group_id
AND PAA.assignment_id = p_assignment_id
AND PPA.action_type in ('R', 'Q', 'B', 'I')
AND PAA.payroll_action_id = PPA.payroll_action_id
AND PAA.action_status = 'C'
AND PPT.assignment_action_id = PAA.assignment_action_id
AND PPT.action_status = 'C'
AND PPT.salary_category = 'TERM'
AND NOT EXISTS(
SELECT null
FROM pay_payroll_actions PPAV,
pay_assignment_actions PAAV,
pay_action_interlocks PAI
WHERE PAI.locked_action_id = PAA.assignment_action_id
AND PAAV.assignment_action_id = PAI.locking_action_id
AND PPAV.payroll_action_id = PAAV.payroll_action_id
AND PPAV.action_type = 'V');
SELECT PAA.assignment_action_id
,PPA.effective_date
,PPA.date_earned
FROM pay_jp_pre_tax PPT,
pay_assignment_actions PAA,
pay_payroll_actions PPA
WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
AND PPA.business_group_id + 0 = p_business_group_id
AND PAA.assignment_id = p_assignment_id
AND PPA.action_type in ('R', 'Q', 'B', 'I')
AND PAA.payroll_action_id = PPA.payroll_action_id
AND PAA.action_status = 'C'
AND PPT.assignment_action_id = PAA.assignment_action_id
AND PPT.action_status = 'C'
AND PPT.salary_category = 'TERM'
AND NOT EXISTS(
SELECT null
FROM pay_payroll_actions PPAV,
pay_assignment_actions PAAV,
pay_action_interlocks PAI
WHERE PAI.locked_action_id = PAA.assignment_action_id
AND PAAV.assignment_action_id = PAI.locking_action_id
AND PPAV.payroll_action_id = PAAV.payroll_action_id
AND PPAV.action_type = 'V');
SELECT PDB.defined_balance_id
FROM pay_balance_types PBT
,pay_balance_dimensions PBD
,pay_defined_balances PDB
WHERE PBT.balance_name = 'B_TRM_INCOME_EXM'
AND PBD.database_item_suffix = '_ASG_RUN'
AND PBT.balance_type_id = PDB.balance_type_id
AND PBD.balance_dimension_id = PDB.balance_dimension_id;
SELECT PDB.defined_balance_id
FROM pay_balance_types PBT
,pay_balance_dimensions PBD
,pay_defined_balances PDB
WHERE PBT.balance_name = 'B_TRM_INC_DEDN_SPC_EXC'
AND PBD.database_item_suffix = '_ASG_RUN'
AND PBT.balance_type_id = PDB.balance_type_id
AND PBD.balance_dimension_id = PDB.balance_dimension_id;
SELECT TO_CHAR(p_start_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''')
||' - '||
TO_CHAR(p_end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''')
FROM dual;
SELECT 'Y'
FROM pay_action_information PAI
,pay_assignment_actions PAC
,pay_payroll_actions PPA
WHERE PAI.action_context_id = PAC.assignment_action_id
AND PAC.assignment_id = p_assignment_id
AND PAC.payroll_action_id = PPA.payroll_action_id
AND PAI.action_context_type = 'AAP'
AND PPA.report_type = gc_report_type;
delete_assact(ln_assignment_id);
SELECT PAA.assignment_action_id
FROM pay_assignment_actions PAA
,pay_payroll_actions PPA
WHERE PAA.payroll_action_id = PPA.payroll_action_id
AND PAA.action_status = 'C'
AND PPA.report_type = gc_report_type
AND NOT EXISTS( SELECT NULL
FROM pay_action_information PAI
WHERE PAI.action_context_id = PAA.assignment_action_id
AND PAI.action_context_type = 'AAP');