[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT INCLUDE_OR_EXCLUDE
INTO l_inc_or_exc
FROM HR_ASSIGNMENT_SET_AMENDMENTS
WHERE ASSIGNMENT_ID = p_assignment_id
AND ASSIGNMENT_SET_ID = p_assignment_set_id;
SELECT 1
INTO l_dummy
FROM HR_ASSIGNMENT_SET_AMENDMENTS
WHERE ASSIGNMENT_SET_ID = p_assignment_set_id
AND ROWNUM = 1;
SELECT 0
INTO l_dummy
FROM HR_ASSIGNMENT_SET_AMENDMENTS
WHERE ASSIGNMENT_SET_ID = p_assignment_set_id
AND INCLUDE_OR_EXCLUDE = 'I'
AND ROWNUM = 1;
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 pjiav.assignment_id, pjiav.effective_date
FROM per_all_assignments_f paa,
per_all_people_f pap,
pay_assignment_actions pas,
pay_jp_isdf_assact_v pjiav
WHERE paa.person_id between p_start_person_id and p_end_person_id
AND paa.person_id = pap.person_id
AND sysdate between pap.effective_start_date and pap.effective_end_date
AND sysdate between paa.effective_start_date and paa.effective_end_date
AND pas.assignment_id = paa.assignment_id
AND pas.payroll_action_id = pay_act_id
AND pjiav.assignment_action_id = pas.assignment_action_id
AND pjiav.assignment_id = pas.assignment_id
AND (pjiav.transaction_status = decode(trans_stat,'N','A')
or pjiav.transaction_status = decode(trans_stat,'N','F')
or pjiav.transaction_status = decode(trans_stat,'Y','A')
or pjiav.transaction_status = decode(trans_stat,'Y','F')
or pjiav.transaction_status = decode(trans_stat,'Y','N')
or pjiav.transaction_status = decode(trans_stat,'Y','U')); -- Last condition in where clause added for Bug Fix:5487428
SELECT legislative_parameters
INTO l_legislative_parameters
FROM pay_payroll_actions
WHERE payroll_action_id = P_PAYROLL_ACTION_ID;
Select p.business_group_id into g_bg_id
from pay_payroll_actions p
where p.payroll_action_id = p_payroll_action_id ;
SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;
SELECT formula_id INTO l_formula_id
FROM hr_assignment_sets
WHERE assignment_set_id = l_ass_set_id;
SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;
SELECT pay_assignment_actions_s.nextval INTO l_assact FROM dual;
SELECT pay_assignment_actions_s.nextval into l_assact from dual;
SELECT to_char(pjip.effective_date, 'EEYY', 'NLS_CALENDAR=''Japanese Imperial''') year,
pjip.effective_date,
pjip.tax_office_name,
pjip.salary_payer_name,
pjip.salary_payer_address
FROM pay_jp_isdf_pact_v pjip,
pay_assignment_actions paa
WHERE paa.assignment_action_id = p_mag_asg_action_id
AND paa.payroll_action_id = pjip.payroll_action_id;
SELECT pjie.last_name_kana,
pjie.first_name_kana,
pjie.last_name,
pjie.first_name,
pjie.address,
pjie.employee_number /* Enh:5671124 : Employee_number addition */
FROM pay_jp_isdf_emp_v pjie
WHERE pjie.assignment_action_id = p_mag_asg_action_id;
select decode(pjicd.life_gen_ins_prem, 0,null,pjicd.life_gen_ins_prem) life_gen_ins_prem,
decode(pjicd.life_pens_ins_prem, 0,null,pjicd.life_pens_ins_prem) life_pens_ins_prem,
decode(pjicd.life_gen_ins_calc_prem, 0,null,pjicd.life_gen_ins_calc_prem) life_gen_ins_calc_prem,
decode(pjicd.life_pens_ins_calc_prem, 0,null,pjicd.life_pens_ins_calc_prem) life_pens_ins_calc_prem,
decode(pjicd.life_gen_new_ins_prem, 0,null,pjicd.life_gen_new_ins_prem) life_gen_new_ins_prem,
decode(pjicd.life_pens_new_ins_prem, 0,null,pjicd.life_pens_new_ins_prem) life_pens_new_ins_prem,
decode(pjicd.life_gen_new_ins_calc_prem, 0,null,pjicd.life_gen_new_ins_calc_prem) life_gen_new_ins_calc_prem,
decode(pjicd.life_pens_new_ins_calc_prem, 0,null,pjicd.life_pens_new_ins_calc_prem) life_pens_new_ins_calc_prem,
decode(pjicd.life_ins_deduction, 0,null,pjicd.life_ins_deduction) life_ins_deduction,
decode(pjicd.earthquake_ins_prem, 0,null,pjicd.earthquake_ins_prem) earthquake_ins_prem,
decode(pjicd.nonlife_long_ins_prem, 0,null,pjicd.nonlife_long_ins_prem) nonlife_long_ins_prem,
decode(pjicd.nonlife_short_ins_prem, 0,null,pjicd.nonlife_short_ins_prem) nonlife_short_ins_prem,
decode(pjicd.earthquake_ins_calc_prem, 0,null,pjicd.earthquake_ins_calc_prem) earthquake_ins_calc_prem,
decode(pjicd.nonlife_long_ins_calc_prem, 0,null,pjicd.nonlife_long_ins_calc_prem) nonlife_long_ins_calc_prem,
decode(pjicd.nonlife_short_ins_calc_prem, 0,null,pjicd.nonlife_short_ins_calc_prem) nonlife_short_ins_calc_prem,
decode(pjicd.nonlife_ins_deduction, 0,null,pjicd.nonlife_ins_deduction) nonlife_ins_deduction,
decode(pjicd.social_ins_deduction, 0,null,pjicd.social_ins_deduction) social_ins_deduction,
decode(pjicd.mutual_aid_deduction, 0,null,pjicd.mutual_aid_deduction) mutual_aid_deduction,
decode(pjicd.sp_earned_income_calc, 0,null,pjicd.sp_earned_income_calc) sp_earned_income_calc,
decode(pjicd.sp_business_income_calc, 0,null,pjicd.sp_business_income_calc) sp_business_income_calc,
decode(pjicd.sp_miscellaneous_income_calc,0,null,pjicd.sp_miscellaneous_income_calc) sp_miscellaneous_income_calc,
decode(pjicd.sp_dividend_income_calc, 0,null,pjicd.sp_dividend_income_calc) sp_dividend_income_calc,
decode(pjicd.sp_real_estate_income_calc, 0,null,pjicd.sp_real_estate_income_calc) sp_real_estate_income_calc,
decode(pjicd.sp_retirement_income_calc, 0,null,pjicd.sp_retirement_income_calc) sp_retirement_income_calc,
decode(pjicd.sp_other_income_calc, 0,null,pjicd.sp_other_income_calc) sp_other_income_calc,
decode(pjicd.sp_income_calc, 0,null,pjicd.sp_income_calc) sp_income_calc,
decode(pjicd.spouse_income, 0,null,pjicd.spouse_income) spouse_income,
decode(pjicd.spouse_deduction, 0,null,pjicd.spouse_deduction) spouse_deduction
from pay_jp_isdf_calc_dct_v pjicd
where pjicd.assignment_action_id=p_mag_asg_action_id
and pjicd.status <> 'D';
select decode(pjicd1.care_med_new_ins_prem, 0,null,pjicd1.care_med_new_ins_prem) care_med_new_ins_prem,
decode(pjicd1.care_med_new_ins_calc_prem, 0,null,pjicd1.care_med_new_ins_calc_prem) care_med_new_ins_calc_prem
from pay_jp_isdf_calc_dct_v2 pjicd1
where pjicd1.assignment_action_id=p_mag_asg_action_id
and pjicd1.status <> 'D';
SELECT pjima.enterprise_contract_prem,
pjima.pension_prem,
pjima.disable_sup_contract_prem
FROM pay_jp_isdf_mutual_aid_v pjima
WHERE pjima.assignment_action_id=p_mag_asg_action_id
and pjima.status <> 'D';
SELECT pjis.full_name_kana,
pjis.full_name,
pjis.address,
pjis.emp_income
FROM pay_jp_isdf_spouse_v pjis
WHERE pjis.assignment_action_id=p_mag_asg_action_id
and pjis.status <> 'D';
SELECT pjisi.sp_earned_income,
pjisi.sp_business_income,
pjisi.sp_business_income_exp,
pjisi.sp_miscellaneous_income,
pjisi.sp_miscellaneous_income_exp,
pjisi.sp_dividend_income,
pjisi.sp_dividend_income_exp,
pjisi.sp_real_estate_income,
pjisi.sp_real_estate_income_exp,
pjisi.sp_retirement_income,
pjisi.sp_retirement_income_exp,
pjisi.sp_other_income,
pjisi.sp_other_income_exp,
pjisi.sp_other_income_exp_dct
FROM pay_jp_isdf_spouse_inc_v pjisi
WHERE pjisi.assignment_action_id=p_mag_asg_action_id
and pjisi.status <> 'D';
SELECT pjilg.ins_company_name,
pjilg.ins_type,
pjilg.ins_period,
pjilg.contractor_name,
pjilg.beneficiary_name,
pjilg.beneficiary_relship,
pjilg.annual_prem,
pjilg.gen_ins_class_old_new
FROM pay_jp_isdf_life_gen_v pjilg
WHERE pjilg.assignment_action_id=p_mag_act_info_id
and pjilg.status <> 'D';
SELECT pjcmi.ins_company_name,
pjcmi.ins_type,
pjcmi.ins_period,
pjcmi.contractor_name,
pjcmi.beneficiary_name,
pjcmi.beneficiary_relship,
pjcmi.annual_prem
FROM pay_jp_isdf_care_med_v pjcmi
WHERE pjcmi.assignment_action_id=p_mag_act_info_id
and pjcmi.status <> 'D';
SELECT pjilp.ins_company_name,
pjilp.ins_type,
pjilp.ins_period_start_date,
pjilp.ins_period,
pjilp.contractor_name,
pjilp.beneficiary_name,
pjilp.beneficiary_relship,
pjilp.annual_prem,
pjilp.pens_ins_class_old_new
FROM pay_jp_isdf_life_pens_v pjilp
WHERE pjilp.assignment_action_id=p_mag_act_info_id
and pjilp.status <> 'D';
select pjin.nonlife_ins_term_type,
pjin.ins_company_name,
pjin.ins_type,
pjin.ins_period,
pjin.contractor_name,
pjin.beneficiary_name,
pjin.beneficiary_relship,
pjin.maturity_repayment,
pjin.annual_prem
from pay_jp_isdf_nonlife_v pjin
where pjin.assignment_action_id=p_mag_act_info_id
and pjin.status <> 'D';
SELECT pjis.ins_type,
pjis.ins_payee_name,
pjis.debtor_name,
pjis.beneficiary_relship,
pjis.annual_prem
FROM pay_jp_isdf_social_v pjis
WHERE pjis.assignment_action_id=p_mag_act_info_id
and pjis.status <> 'D';
vXMLTable.DELETE;
SELECT count(DISTINCT(action_information_id))
INTO count_life_gen
FROM pay_jp_isdf_life_gen_v pjilg
WHERE pjilg.assignment_action_id = l_mag_asg_action_id
and pjilg.status <> 'D';
SELECT count(DISTINCT(action_information_id))
INTO count_life_pens
FROM pay_jp_isdf_life_pens_v pjilp
WHERE pjilp.assignment_action_id = l_mag_asg_action_id
and pjilp.status <> 'D';
SELECT count(DISTINCT(action_information_id))
INTO count_nonlife
FROM pay_jp_isdf_nonlife_v pjin
WHERE pjin.assignment_action_id = l_mag_asg_action_id
and pjin.status <> 'D';
SELECT count(DISTINCT(action_information_id))
INTO count_social
FROM pay_jp_isdf_social_v pjis
WHERE pjis.assignment_action_id = l_mag_asg_action_id
and pjis.status <> 'D';
SELECT count(DISTINCT(action_information_id))
INTO count_care_med
FROM pay_jp_isdf_care_med_v pjicm
WHERE pjicm.assignment_action_id = l_mag_asg_action_id
and pjicm.status <> 'D';
select to_char(ins_period_start_date_lp(d),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
into ps_date
from dual;
select to_char(ins_period_start_date_lp(e),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
into ps_date
from dual;
select to_char(ins_period_start_date_lp(g),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
into ps_date
from dual;
select to_char(ins_period_start_date_lp(h),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
into ps_date
from dual;
select to_char(ins_period_start_date_lp(j),'EEYYMMDD"','NLS_CALENDAR=''Japanese Imperial''')
into ps_date
from dual;
Description : This procedure selects the xml from vXMLTable and writes it
into a clob variable. This clob variable is then returned.
*****************************************************************************/
PROCEDURE WritetoCLOB (p_write_xml OUT NOCOPY CLOB)
IS
l_xfdf_string CLOB;
SELECT legislative_parameters
INTO l_legislative_parameters
FROM pay_payroll_actions
WHERE payroll_action_id = l_cur_pact;
SELECT paa1.assignment_action_id
INTO l_old_assact_id
FROM pay_assignment_actions paa,
pay_assignment_actions paa1
WHERE paa.assignment_action_id = l_cur_assact
AND paa.assignment_id = paa1.assignment_id
AND paa1.payroll_action_id = l_pact_id;
vXMLTable.DELETE; -- delete the pl/sql table