The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_information10
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'Business Group Information';
select meaning
from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'AE_BG_DETAILS';
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_employer_id
AND org_information_context = 'AE_LEGAL_EMPLOYER_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
SELECT org_information6
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_employer_id
AND hoi.org_information_context = 'AE_LEGAL_EMPLOYER_DETAILS';
SELECT loc.address_line_3, loc.LOC_INFORMATION15
FROM hr_locations loc
WHERE loc.location_id = l_loc_id;
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_code = l_emirate
AND hl.lookup_type = 'AE_EMIRATE'
AND hl.enabled_flag = 'Y';
SELECT hoi.org_information1,hoi.org_information2,hoi.org_information3,hoi.org_information4
FROM hr_organization_information hoi
WHERE hoi.organization_id = l_bg_id
AND hoi.org_information_context = 'AE_HR_BG_INFO';
SELECT pp.phone_number
FROM per_phones pp,per_all_people_f ppf
WHERE pp.parent_id = ppf.person_id
AND pp.phone_type = l_ph_type
AND ppf.person_id = l_person_id
AND l_effective_date between pp.date_from and nvl(pp.date_to,to_date('31-12-4712','DD-MM-YYYY'));
SELECT distinct asg.person_id
,paa.assignment_action_id
,ppa.date_earned
,pos.date_start
FROM per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_all_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(pos.date_start, 'MM') = trunc(l_effective_date, 'MM')
AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer_id)
AND ppf.person_id = asg.person_id
AND trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.per_information18 = l_nat_cd;
SELECT ppf.full_name,ppf.employee_number,ppf.date_of_birth,ppf.marital_status,ppf.sex,fnd_date.canonical_to_date(ppf.per_information16),ppf.per_information17
FROM per_all_people_f ppf
WHERE ppf.person_id = l_person_id
AND l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = 'SEX'
AND hl.lookup_code = l_sex
AND hl.enabled_flag = 'Y';
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = 'MAR_STATUS'
AND hl.lookup_code = l_mar_stat
AND hl.enabled_flag = 'Y';
SELECT hsck.segment2,hsck.segment3,fnd_date.canonical_to_date(hsck.segment4),hsck.segment5,paf.location_id
FROM per_all_assignments_f paf,hr_soft_coding_keyflex hsck
WHERE paf.person_id = l_person_id
AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.segment1 = p_employer_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT pjb.name
FROM per_all_assignments_f paf,per_jobs pjb
WHERE paf.person_id = l_person_id
AND pjb.job_id = paf.job_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT pq.title , pq.attendance_id , hl.meaning
FROM per_qualifications_v pq , per_subjects_taken pst ,hr_lookups hl
WHERE pq.person_id = l_person_id
AND pq.qualification_id = l_qual_id
AND pst.qualification_id = pq.qualification_id
AND pst.major = 'Y'
AND hl.lookup_type = 'PER_SUBJECTS'
AND hl.lookup_code = pst.subject
AND rownum < 2;
select est.name
from per_establishments est, per_establishment_attendances pea, per_qualifications pq
where pq.attendance_id = l_attendance_id
and pq.attendance_id = pea.attendance_id
and est.establishment_id = pea.establishment_id;
SELECT pr.employer_name ,pr.previous_employer_id,pr.employer_type, pr.PEM_INFORMATION1 , fnd_date.canonical_to_date(pr.PEM_INFORMATION2),pr.PEM_INFORMATION3,pr.PEM_INFORMATION4
FROM per_previous_employers pr
WHERE pr.person_id = l_person_id
AND pr.PEM_INFORMATION_CATEGORY ='AE'
AND nvl(pr.end_date,sysdate) in (SELECT nvl(max(end_date),sysdate)
FROM per_previous_employers
WHERE person_id = l_person_id ) order by nvl(start_date,sysdate) desc,employer_name desc ;
SELECT pr.employer_name ,pr.previous_employer_id
FROM per_previous_employers pr
WHERE pr.person_id = l_person_id
AND nvl(pr.end_date,sysdate) in (SELECT nvl(max(end_date),sysdate)
FROM per_previous_employers
WHERE person_id = l_person_id ) order by nvl(start_date,sysdate) desc,employer_name desc ;
SELECT pr.end_date
FROM per_previous_employers pr
WHERE pr.person_id = l_person_id
AND pr.previous_employer_id = l_ex_emp_id
AND pr.employer_type = 'FG'
AND pr.PEM_INFORMATION1 = 'EMP_TRANS';
SELECT substr(addr.ADDRESS_LINE1 || ' ' ||addr.address_line2,1,60)
FROM per_addresses addr
WHERE addr.person_id = l_person_id
AND l_effective_date between addr.date_from and nvl(addr.date_to,to_date('31-12-4712','dd-mm-yyyy'))
AND addr.primary_flag = 'Y';
SELECT substr(dis.reason,1,60),dis.degree,dis.effective_start_date
FROM per_disabilities_f dis
WHERE dis.person_id = l_person_id
AND l_effective_date between dis.effective_start_date and dis.effective_end_date
-- AND dis.status = 'A'
AND dis.dis_information1 = 'Y';
SELECT meaning
FROM hr_lookups
WHERE lookup_type = 'DISABILITY_REASON'
and lookup_code = l_lookup_code
and enabled_flag = 'Y';
SELECT CONTACT_PERSON_ID
FROM per_contact_relationships cont
WHERE cont.person_id = l_person_id
AND cont.contact_type = l_type;
SELECT pq.title , hl.meaning
FROM per_qualifications_v pq , per_subjects_taken pst ,hr_lookups hl
WHERE pq.person_id = l_spouse_person_id
AND pst.qualification_id = pq.qualification_id
AND pst.major = 'Y'
AND hl.lookup_type = 'PER_SUBJECTS'
AND hl.lookup_code = pst.subject
AND rownum < 2;
SELECT count(*)
FROM per_contact_relationships cont , per_all_people_f ppf
WHERE cont.person_id = l_person_id
AND cont.contact_type = l_type
AND ppf.person_id = cont.CONTACT_PERSON_ID
AND ppf.sex = l_gender
AND l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT ORG_INFORMATION1,ORG_INFORMATION2,ORG_INFORMATION3,ORG_INFORMATION4,ORG_INFORMATION5,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
FROM HR_ORGANIZATION_INFORMATION
WHERE Organization_id = l_bg_id
AND org_information_context = 'AE_SI_DETAILS';
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND u.legislation_code = 'AE'
AND u.business_group_id is null
AND u.creator_type = 'B';
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
t_store_def_bal.DELETE;
vXMLTable.DELETE;
SELECT nvl(decode(l_basic_val/(abs(decode(l_basic_val,0,1,l_basic_val))*-1),1,'-'||l_fm_l_basic_val,l_fm_l_basic_val),' ')
INTO l_fm_l_basic_val
FROM dual;
SELECT decode(l_accomodation_val/(abs(decode(l_accomodation_val,0,1,l_accomodation_val))*-1),1,'-'||l_fm_l_accomodation_val,l_fm_l_accomodation_val)
INTO l_fm_l_accomodation_val
FROM dual;
SELECT decode(l_allowance_1_val/(abs(decode(l_allowance_1_val,0,1,l_allowance_1_val))*-1),1,'-'||l_fm_l_allowance_1_val,l_fm_l_allowance_1_val)
INTO l_fm_l_allowance_1_val
FROM dual;
SELECT decode(l_allowance_2_val/(abs(decode(l_allowance_2_val,0,1,l_allowance_2_val))*-1),1,'-'||l_fm_l_allowance_2_val,l_fm_l_allowance_2_val)
INTO l_fm_l_allowance_2_val
FROM dual;
SELECT decode(l_allowance_3_val/(abs(decode(l_allowance_3_val,0,1,l_allowance_3_val))*-1),1,'-'||l_fm_l_allowance_3_val,l_fm_l_allowance_3_val)
INTO l_fm_l_allowance_3_val
FROM dual;
SELECT decode(l_allowance_4_val/(abs(decode(l_allowance_4_val,0,1,l_allowance_4_val))*-1),1,'-'||l_fm_l_allowance_4_val,l_fm_l_allowance_4_val)
INTO l_fm_l_allowance_4_val
FROM dual;
SELECT decode(l_allowance_5_val/(abs(decode(l_allowance_5_val,0,1,l_allowance_5_val))*-1),1,'-'||l_fm_l_allowance_5_val,l_fm_l_allowance_5_val)
INTO l_fm_l_allowance_5_val
FROM dual;
SELECT decode(l_total/(abs(decode(l_total,0,1,l_total))*-1),1,'-'||l_fm_total_val,l_fm_total_val)
INTO l_fm_total_val
FROM dual;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'AE_BG_DETAILS';
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_employer_id
AND org_information_context = 'AE_LEGAL_EMPLOYER_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
SELECT hoi.org_information1,hoi.org_information2,hoi.org_information3,hoi.org_information4
FROM hr_organization_information hoi
WHERE hoi.organization_id = l_bg_id
AND hoi.org_information_context = 'AE_HR_BG_INFO';
SELECT pp.phone_number
FROM per_phones pp,per_all_people_f ppf
WHERE pp.parent_id = ppf.person_id
AND pp.phone_type = l_ph_type
AND ppf.person_id = l_person_id
AND l_effective_date between pp.date_from and nvl(pp.date_to,to_date('31-12-4712','DD-MM-YYYY'));
SELECT substr(addr.ADDRESS_LINE1 || ' ' ||addr.address_line2,1,60)
FROM per_addresses addr
WHERE addr.person_id = l_person_id
AND l_effective_date between addr.date_from and nvl(addr.date_to,to_date('31-12-4712','dd-mm-yyyy'))
AND addr.primary_flag = 'Y';
SELECT distinct asg.person_id
,paa.assignment_action_id
,pos.actual_termination_date
,pos.LEAVING_REASON
,ppa.date_earned
FROM per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_all_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer_id)
AND ppf.person_id = asg.person_id
AND trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.per_information18 = l_nat_cd;
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = 'LEAV_REAS'
AND hl.lookup_code = l_code;
SELECT ppf.full_name,ppf.marital_status,ppf.sex
FROM per_all_people_f ppf
WHERE ppf.person_id = l_person_id
AND l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = 'MAR_STATUS'
AND hl.lookup_code = l_mar_stat
AND hl.enabled_flag = 'Y';
SELECT paf.assignment_id , hsck.segment3,paf.location_id
FROM per_all_assignments_f paf,hr_soft_coding_keyflex hsck
WHERE paf.person_id = l_person_id
AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.segment1 = p_employer_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT count(*)
FROM per_contact_relationships cont , per_all_people_f ppf
WHERE cont.person_id = l_person_id
AND cont.contact_type = l_type
AND ppf.person_id = cont.CONTACT_PERSON_ID
AND ppf.sex = l_gender
AND l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT external_account_id,org_payment_method_id
FROM pay_personal_payment_methods_f
WHERE assignment_id = l_assignment_id
AND l_effective_date between effective_start_date and effective_end_date
AND ppm_information_category = 'AE_AE DIRECT DEPOSIT AED'
AND ppm_information1 = 'Y';
SELECT external_account_id
FROM pay_org_payment_methods_f pom
WHERE pom.org_payment_method_id = l_org_paymeth_id
And l_effective_date between effective_start_date and effective_end_date;
SELECT segment1,segment2,segment4
FROM pay_external_accounts
WHERE external_account_id = l_external_account_id;
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = 'AE_BANK_NAMES'
AND hl.lookup_code = l_code
AND hl.enabled_flag = 'Y';
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = 'AE_BRANCH_NAMES'
AND hl.lookup_code = l_code
AND hl.enabled_flag = 'Y';
SELECT ORG_INFORMATION1,ORG_INFORMATION2,ORG_INFORMATION3,ORG_INFORMATION4,ORG_INFORMATION5,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10 ,ORG_INFORMATION11
FROM HR_ORGANIZATION_INFORMATION
WHERE Organization_id = l_bg_id
AND org_information_context = 'AE_SI_DETAILS';
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND u.legislation_code = 'AE'
AND u.business_group_id is null
AND u.creator_type = 'B';
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
vXMLTable.DELETE;
SELECT decode(l_basic_val/(abs(decode(l_basic_val,0,1,l_basic_val))*-1),1,'-'||l_fm_l_basic_val,l_fm_l_basic_val)
INTO l_fm_l_basic_val
FROM dual;
SELECT decode(l_accomodation_val/(abs(decode(l_accomodation_val,0,1,l_accomodation_val))*-1),1,'-'||l_fm_l_accomodation_val,l_fm_l_accomodation_val)
INTO l_fm_l_accomodation_val
FROM dual;
SELECT decode(l_allowance_1_val/(abs(decode(l_allowance_1_val,0,1,l_allowance_1_val))*-1),1,'-'||l_fm_l_allowance_1_val,l_fm_l_allowance_1_val)
INTO l_fm_l_allowance_1_val
FROM dual;
SELECT decode(l_allowance_2_val/(abs(decode(l_allowance_2_val,0,1,l_allowance_2_val))*-1),1,'-'||l_fm_l_allowance_2_val,l_fm_l_allowance_2_val)
INTO l_fm_l_allowance_2_val
FROM dual;
SELECT decode(l_allowance_3_val/(abs(decode(l_allowance_3_val,0,1,l_allowance_3_val))*-1),1,'-'||l_fm_l_allowance_3_val,l_fm_l_allowance_3_val)
INTO l_fm_l_allowance_3_val
FROM dual;
SELECT decode(l_allowance_4_val/(abs(decode(l_allowance_4_val,0,1,l_allowance_4_val))*-1),1,'-'||l_fm_l_allowance_4_val,l_fm_l_allowance_4_val)
INTO l_fm_l_allowance_4_val
FROM dual;
SELECT decode(l_total/(abs(decode(l_total,0,1,l_total))*-1),1,'-'||l_fm_total_val,l_fm_total_val)
INTO l_fm_total_val
FROM dual;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'AE_BG_DETAILS';
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_employer_id
AND org_information_context = 'AE_LEGAL_EMPLOYER_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
SELECT distinct asg.person_id
,paa.assignment_action_id
,ppa.date_earned
FROM per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_all_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer)
AND ppf.person_id = asg.person_id
AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.per_information18 = l_nat_cd;
SELECT ppf.full_name
FROM per_all_people_f ppf
WHERE ppf.person_id = l_person_id
AND l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT hsck.segment3
FROM per_all_assignments_f paf,hr_soft_coding_keyflex hsck
WHERE paf.person_id = l_person_id
AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.segment1 = p_employer_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT ORG_INFORMATION1,ORG_INFORMATION2,ORG_INFORMATION3,ORG_INFORMATION4,ORG_INFORMATION5,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
FROM HR_ORGANIZATION_INFORMATION
WHERE Organization_id = l_bg_id
AND org_information_context = 'AE_SI_DETAILS';
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND u.legislation_code = 'AE'
AND u.business_group_id is null
AND u.creator_type = 'B';
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
SELECT decode(l_basic_val/(abs(decode(l_basic_val,0,1,l_basic_val))*-1),1,'-'||l_fm_l_basic_val,l_fm_l_basic_val)
INTO l_fm_l_basic_val
FROM dual;
SELECT decode(l_accomodation_val/(abs(decode(l_accomodation_val,0,1,l_accomodation_val))*-1),1,'-'||l_fm_l_accomodation_val,l_fm_l_accomodation_val)
INTO l_fm_l_accomodation_val
FROM dual;
SELECT decode(l_allowance_1_val/(abs(decode(l_allowance_1_val,0,1,l_allowance_1_val))*-1),1,'-'||l_fm_l_allowance_1_val,l_fm_l_allowance_1_val)
INTO l_fm_l_allowance_1_val
FROM dual;
SELECT decode(l_allowance_2_val/(abs(decode(l_allowance_2_val,0,1,l_allowance_2_val))*-1),1,'-'||l_fm_l_allowance_2_val,l_fm_l_allowance_2_val)
INTO l_fm_l_allowance_2_val
FROM dual;
SELECT decode(l_allowance_3_val/(abs(decode(l_allowance_3_val,0,1,l_allowance_3_val))*-1),1,'-'||l_fm_l_allowance_3_val,l_fm_l_allowance_3_val)
INTO l_fm_l_allowance_3_val
FROM dual;
SELECT decode(l_allowance_4_val/(abs(decode(l_allowance_4_val,0,1,l_allowance_4_val))*-1),1,'-'||l_fm_l_allowance_4_val,l_fm_l_allowance_4_val)
INTO l_fm_l_allowance_4_val
FROM dual;
SELECT decode(l_total/(abs(decode(l_total,0,1,l_total))*-1),1,'-'||l_fm_total_val,l_fm_total_val)
INTO l_fm_total_val
FROM dual;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'AE_BG_DETAILS';
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_employer_id
AND org_information_context = 'AE_LEGAL_EMPLOYER_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
SELECT distinct asg.person_id
,paa.assignment_action_id
,pos.date_start
FROM per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_all_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') <> TRUNC(l_effective_date, 'MM')
AND trunc(pos.date_start, 'MM') = trunc(l_effective_date, 'MM')
AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(p_employer_id)
AND ppf.person_id = asg.person_id
AND trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.per_information18 = l_nat_cd;
SELECT distinct asg.person_id
,paa.assignment_action_id
,pos.actual_termination_date
,ppa.date_earned
FROM per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_all_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(p_employer_id)
AND ppf.person_id = asg.person_id
AND trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.per_information18 = l_nat_cd;
SELECT date_earned, paa.assignment_action_id
FROM per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,per_periods_of_service pos
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND trunc(ppa.date_earned, 'MM') < TRUNC(l_effective_date, 'MM')
AND asg.person_id = p_person_id
order by date_earned desc;
SELECT distinct asg.person_id
,paa.assignment_action_id
,date_earned
FROM per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_all_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
AND trunc(pos.date_start, 'MM') <> trunc(l_effective_date, 'MM')
AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') <> TRUNC(l_effective_date, 'MM')
AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(p_employer_id)
AND ppf.person_id = asg.person_id
AND trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.per_information18 = l_nat_cd;
SELECT ppf.full_name
FROM per_all_people_f ppf
WHERE ppf.person_id = l_person_id
AND l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT hsck.segment3
FROM per_all_assignments_f paf,hr_soft_coding_keyflex hsck
WHERE paf.person_id = l_person_id
AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.segment1 = p_employer_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT ORG_INFORMATION1,ORG_INFORMATION2,ORG_INFORMATION3,ORG_INFORMATION4,ORG_INFORMATION5,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
FROM HR_ORGANIZATION_INFORMATION
WHERE Organization_id = l_bg_id
AND org_information_context = 'AE_SI_DETAILS';
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND u.legislation_code = 'AE'
AND u.business_group_id is null
AND u.creator_type = 'B';
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
SELECT decode(l_cur_total/(abs(decode(l_cur_total,0,1,l_cur_total))*-1),1,'-'||l_fm_l_cur_total,l_fm_l_cur_total)
INTO l_fm_l_cur_total
FROM dual;
SELECT decode(l_prev_total/(abs(decode(l_prev_total,0,1,l_prev_total))*-1),1,'-'||l_fm_l_prev_total,l_fm_l_prev_total)
INTO l_fm_l_prev_total
FROM dual;
SELECT decode(l_diff_total/(abs(decode(l_diff_total,0,1,l_diff_total))*-1),1,'-'||l_fm_l_diff_total,l_fm_l_diff_total)
INTO l_fm_l_diff_total
FROM dual;
SELECT decode(l_basic_val/(abs(decode(l_basic_val,0,1,l_basic_val))*-1),1,'-'||l_fm_l_basic_val,l_fm_l_basic_val)
INTO l_fm_l_basic_val
FROM dual;
SELECT decode(l_accomodation_val/(abs(decode(l_accomodation_val,0,1,l_accomodation_val))*-1),1,'-'||l_fm_l_accomodation_val,l_fm_l_accomodation_val)
INTO l_fm_l_accomodation_val
FROM dual;
SELECT decode(l_allowance_1_val/(abs(decode(l_allowance_1_val,0,1,l_allowance_1_val))*-1),1,'-'||l_fm_l_allowance_1_val,l_fm_l_allowance_1_val)
INTO l_fm_l_allowance_1_val
FROM dual;
SELECT decode(l_allowance_2_val/(abs(decode(l_allowance_2_val,0,1,l_allowance_2_val))*-1),1,'-'||l_fm_l_allowance_2_val,l_fm_l_allowance_2_val)
INTO l_fm_l_allowance_2_val
FROM dual;
SELECT decode(l_allowance_3_val/(abs(decode(l_allowance_3_val,0,1,l_allowance_3_val))*-1),1,'-'||l_fm_l_allowance_3_val,l_fm_l_allowance_3_val)
INTO l_fm_l_allowance_3_val
FROM dual;
SELECT decode(l_allowance_4_val/(abs(decode(l_allowance_4_val,0,1,l_allowance_4_val))*-1),1,'-'||l_fm_l_allowance_4_val,l_fm_l_allowance_4_val)
INTO l_fm_l_allowance_4_val
FROM dual;
SELECT decode(l_total/(abs(decode(l_total,0,1,l_total))*-1),1,'-'||l_fm_total_val,l_fm_total_val)
INTO l_fm_total_val
FROM dual;
SELECT decode(l_subject_si_val/(abs(decode(l_subject_si_val,0,1,l_subject_si_val))*-1),1,'-'||l_fm_subject_si_val,l_fm_subject_si_val)
INTO l_fm_subject_si_val
FROM dual;
SELECT decode(l_total_basic/(abs(decode(l_total_basic,0,1,l_total_basic))*-1),1,'-'||l_fm_l_total_basic,l_fm_l_total_basic)
INTO l_fm_l_total_basic
FROM dual;
SELECT decode(L_TOTAL_HA/(abs(decode(L_TOTAL_HA,0,1,L_TOTAL_HA))*-1),1,'-'||l_fm_l_total_ha,l_fm_l_total_ha)
INTO l_fm_l_total_ha
FROM dual;
SELECT decode(L_TOTAL_A1/(abs(decode(L_TOTAL_A1,0,1,L_TOTAL_A1))*-1),1,'-'||l_fm_l_total_a1,l_fm_l_total_a1)
INTO l_fm_l_total_a1
FROM dual;
SELECT decode(L_TOTAL_A2/(abs(decode(L_TOTAL_A2,0,1,L_TOTAL_A2))*-1),1,'-'||l_fm_l_total_a2,l_fm_l_total_a2)
INTO l_fm_l_total_a2
FROM dual;
SELECT decode(L_TOTAL_A3/(abs(decode(L_TOTAL_A3,0,1,L_TOTAL_A3))*-1),1,'-'||l_fm_l_total_a3,l_fm_l_total_a3)
INTO l_fm_l_total_a3
FROM dual;
SELECT decode(L_TOTAL_A4/(abs(decode(L_TOTAL_A4,0,1,L_TOTAL_A4))*-1),1,'-'||l_fm_l_total_a4,l_fm_l_total_a4)
INTO l_fm_l_total_a4
FROM dual;
SELECT decode(L_TOTAL_TOTAL/(abs(decode(L_TOTAL_TOTAL,0,1,L_TOTAL_TOTAL))*-1),1,'-'||l_fm_l_total_total,l_fm_l_total_total)
INTO l_fm_l_total_total
FROM dual;
SELECT decode(l_total_basic/(abs(decode(l_total_basic,0,1,l_total_basic))*-1),1,'-'||l_fm_l_total_basic,l_fm_l_total_basic)
INTO l_fm_l_total_basic
FROM dual;
SELECT decode(L_TOTAL_HA/(abs(decode(L_TOTAL_HA,0,1,L_TOTAL_HA))*-1),1,'-'||l_fm_l_total_ha,l_fm_l_total_ha)
INTO l_fm_l_total_ha
FROM dual;
SELECT decode(L_TOTAL_A1/(abs(decode(L_TOTAL_A1,0,1,L_TOTAL_A1))*-1),1,'-'||l_fm_l_total_a1,l_fm_l_total_a1)
INTO l_fm_l_total_a1
FROM dual;
SELECT decode(L_TOTAL_A2/(abs(decode(L_TOTAL_A2,0,1,L_TOTAL_A2))*-1),1,'-'||l_fm_l_total_a2,l_fm_l_total_a2)
INTO l_fm_l_total_a2
FROM dual;
SELECT decode(L_TOTAL_A3/(abs(decode(L_TOTAL_A3,0,1,L_TOTAL_A3))*-1),1,'-'||l_fm_l_total_a3,l_fm_l_total_a3)
INTO l_fm_l_total_a3
FROM dual;
SELECT decode(L_TOTAL_A4/(abs(decode(L_TOTAL_A4,0,1,L_TOTAL_A4))*-1),1,'-'||l_fm_l_total_a4,l_fm_l_total_a4)
INTO l_fm_l_total_a4
FROM dual;
SELECT decode(L_TOTAL_TOTAL/(abs(decode(L_TOTAL_TOTAL,0,1,L_TOTAL_TOTAL))*-1),1,'-'||l_fm_l_total_total,l_fm_l_total_total)
INTO l_fm_l_total_total
FROM dual;
SELECT decode(l_subject_si_val/(abs(decode(l_subject_si_val,0,1,l_subject_si_val))*-1),1,'-'||l_fm_subject_si_val,l_fm_subject_si_val)
INTO l_fm_subject_si_val
FROM dual;
SELECT decode(l_total_term/(abs(decode(l_total_term,0,1,l_total_term))*-1),1,'-'||l_fm_l_total_term,l_fm_l_total_term)
INTO l_fm_l_total_term
FROM dual;
SELECT decode(l_total_term/(abs(decode(l_total_term,0,1,l_total_term))*-1),1,'-'||l_fm_l_total_term,l_fm_l_total_term)
INTO l_fm_l_total_term
FROM dual;
SELECT decode(l_subject_si_val/(abs(decode(l_subject_si_val,0,1,l_subject_si_val))*-1),1,'-'||l_fm_changed_salary,l_fm_changed_salary)
INTO l_fm_changed_salary
FROM dual;
SELECT decode(l_total_change/(abs(decode(l_total_change,0,1,l_total_change))*-1),1,'-'||l_fm_l_total_change,l_fm_l_total_change)
INTO l_fm_l_total_change
FROM dual;
SELECT decode(l_total_change/(abs(decode(l_total_change,0,1,l_total_change))*-1),1,'-'||l_fm_l_total_change,l_fm_l_total_change)
INTO l_fm_l_total_change
FROM dual;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'AE_BG_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
SELECT distinct asg.person_id
,paa.assignment_action_id
,ppa.date_earned
FROM per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_all_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer)
AND ppf.person_id = asg.person_id
AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.per_information18 = l_nat_cd;
SELECT ppf.full_name, ppf.employee_number
FROM per_all_people_f ppf
WHERE ppf.person_id = l_person_id
AND l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT hsck.segment3
FROM per_all_assignments_f paf,hr_soft_coding_keyflex hsck
WHERE paf.person_id = l_person_id
AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.segment1 = p_employer_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT ORG_INFORMATION1,ORG_INFORMATION2,ORG_INFORMATION3,ORG_INFORMATION4,ORG_INFORMATION5,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
FROM HR_ORGANIZATION_INFORMATION
WHERE Organization_id = l_bg_id
AND org_information_context = 'AE_SI_DETAILS';
SELECT u.creator_id
FROM ff_user_entities u,ff_database_items d
WHERE d.user_name = l_user_name
AND u.user_entity_id = d.user_entity_id
AND u.legislation_code = 'AE'
AND u.business_group_id is null
AND u.creator_type = 'B';
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
SELECT decode(l_ee_contribution/(abs(decode(l_ee_contribution,0,1,l_ee_contribution))*-1),1,'-'||l_fm_ee_contribution,l_fm_ee_contribution)
INTO l_fm_ee_contribution
FROM dual;
SELECT decode(l_er_contribution/(abs(decode(l_er_contribution,0,1,l_er_contribution))*-1),1,'-'||l_fm_er_contribution,l_fm_er_contribution)
INTO l_fm_er_contribution
FROM dual;
SELECT decode(l_total_contribution/(abs(decode(l_total_contribution,0,1,l_total_contribution))*-1),1,'-'||l_fm_total_contribution,l_fm_total_contribution)
INTO l_fm_total_contribution
FROM dual;
SELECT decode(l_basic_val/(abs(decode(l_basic_val,0,1,l_basic_val))*-1),1,'-'||l_fm_l_basic_val,l_fm_l_basic_val)
INTO l_fm_l_basic_val
FROM dual;
SELECT decode(l_accomodation_val/(abs(decode(l_accomodation_val,0,1,l_accomodation_val))*-1),1,'-'||l_fm_l_accomodation_val,l_fm_l_accomodation_val)
INTO l_fm_l_accomodation_val
FROM dual;
SELECT decode(l_allowance_1_val/(abs(decode(l_allowance_1_val,0,1,l_allowance_1_val))*-1),1,'-'||l_fm_l_allowance_1_val,l_fm_l_allowance_1_val)
INTO l_fm_l_allowance_1_val
FROM dual;
SELECT decode(l_allowance_2_val/(abs(decode(l_allowance_2_val,0,1,l_allowance_2_val))*-1),1,'-'||l_fm_l_allowance_2_val,l_fm_l_allowance_2_val)
INTO l_fm_l_allowance_2_val
FROM dual;
SELECT decode(l_allowance_3_val/(abs(decode(l_allowance_3_val,0,1,l_allowance_3_val))*-1),1,'-'||l_fm_l_allowance_3_val,l_fm_l_allowance_3_val)
INTO l_fm_l_allowance_3_val
FROM dual;
SELECT decode(l_allowance_4_val/(abs(decode(l_allowance_4_val,0,1,l_allowance_4_val))*-1),1,'-'||l_fm_l_allowance_4_val,l_fm_l_allowance_4_val)
INTO l_fm_l_allowance_4_val
FROM dual;
SELECT decode(l_total/(abs(decode(l_total,0,1,l_total))*-1),1,'-'||l_fm_total_val,l_fm_total_val)
INTO l_fm_total_val
FROM dual;
SELECT decode(l_total_basic/(abs(decode(l_total_basic,0,1,l_total_basic))*-1),1,'-'||l_fm_total_basic,l_fm_total_basic)
INTO l_fm_total_basic
FROM dual;
SELECT decode(l_total_aa/(abs(decode(l_total_aa,0,1,l_total_aa))*-1),1,'-'||l_fm_total_aa,l_fm_total_aa)
INTO l_fm_total_aa
FROM dual;
SELECT decode(l_total_a1/(abs(decode(l_total_a1,0,1,l_total_a1))*-1),1,'-'||l_fm_total_a1,l_fm_total_a1)
INTO l_fm_total_a1
FROM dual;
SELECT decode(l_total_a2/(abs(decode(l_total_a2,0,1,l_total_a2))*-1),1,'-'||l_fm_total_a2,l_fm_total_a2)
INTO l_fm_total_a2
FROM dual;
SELECT decode(l_total_a3/(abs(decode(l_total_a3,0,1,l_total_a3))*-1),1,'-'||l_fm_total_a3,l_fm_total_a3)
INTO l_fm_total_a3
FROM dual;
SELECT decode(l_total_ee/(abs(decode(l_total_ee,0,1,l_total_ee))*-1),1,'-'||l_fm_total_ee,l_fm_total_ee)
INTO l_fm_total_ee
FROM dual;
SELECT decode(l_total_er/(abs(decode(l_total_er,0,1,l_total_er))*-1),1,'-'||l_fm_total_er,l_fm_total_er)
INTO l_fm_total_er
FROM dual;
SELECT decode(l_total_total/(abs(decode(l_total_total,0,1,l_total_total))*-1),1,'-'||l_fm_total_total,l_fm_total_total)
INTO l_fm_total_total
FROM dual;
SELECT decode(l_total_basic/(abs(decode(l_total_basic,0,1,l_total_basic))*-1),1,'-'||l_fm_total_basic,l_fm_total_basic)
INTO l_fm_total_basic
FROM dual;
SELECT decode(l_total_aa/(abs(decode(l_total_aa,0,1,l_total_aa))*-1),1,'-'||l_fm_total_aa,l_fm_total_aa)
INTO l_fm_total_aa
FROM dual;
SELECT decode(l_total_a1/(abs(decode(l_total_a1,0,1,l_total_a1))*-1),1,'-'||l_fm_total_a1,l_fm_total_a1)
INTO l_fm_total_a1
FROM dual;
SELECT decode(l_total_a2/(abs(decode(l_total_a2,0,1,l_total_a2))*-1),1,'-'||l_fm_total_a2,l_fm_total_a2)
INTO l_fm_total_a2
FROM dual;
SELECT decode(l_total_a3/(abs(decode(l_total_a3,0,1,l_total_a3))*-1),1,'-'||l_fm_total_a3,l_fm_total_a3)
INTO l_fm_total_a3
FROM dual;
SELECT decode(l_total_ee/(abs(decode(l_total_ee,0,1,l_total_ee))*-1),1,'-'||l_fm_total_ee,l_fm_total_ee)
INTO l_fm_total_ee
FROM dual;
SELECT decode(l_total_er/(abs(decode(l_total_er,0,1,l_total_er))*-1),1,'-'||l_fm_total_er,l_fm_total_er)
INTO l_fm_total_er
FROM dual;
SELECT decode(l_total_total/(abs(decode(l_total_total,0,1,l_total_total))*-1),1,'-'||l_fm_total_total,l_fm_total_total)
INTO l_fm_total_total
FROM dual;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = p_business_group_id
AND org_information_context = 'AE_BG_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
SELECT distinct asg.person_id
,paa.assignment_action_id
,ppa.date_earned
FROM per_all_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_all_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND pos.period_of_service_id = asg.period_of_service_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND trunc(ppa.date_earned,'MM') = TRUNC(l_date, 'MM')
AND trunc(l_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(l_employer)
AND ppf.person_id = asg.person_id
AND trunc(l_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND ppf.per_information18 = l_nat_cd;
SELECT ppf.full_name, ppf.employee_number
FROM per_all_people_f ppf
WHERE ppf.person_id = l_person_id
AND l_effective_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT hsck.segment3
FROM per_all_assignments_f paf,hr_soft_coding_keyflex hsck
WHERE paf.person_id = l_person_id
AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.segment1 = p_employer_id
AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
SELECT ORG_INFORMATION1,ORG_INFORMATION2,ORG_INFORMATION3,ORG_INFORMATION4,ORG_INFORMATION5,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
FROM HR_ORGANIZATION_INFORMATION
WHERE Organization_id = l_bg_id
AND org_information_context = 'AE_SI_DETAILS';
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND (u.legislation_code = 'AE' )
AND (u.business_group_id IS NULL )
AND u.creator_type = 'B';
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
SELECT decode(l_basic_val/(abs(decode(l_basic_val,0,1,l_basic_val))*-1),1,'-'||l_fm_l_basic_val,l_fm_l_basic_val)
INTO l_fm_l_basic_val
FROM dual;
SELECT decode(l_cost_of_living_val/(abs(decode(l_cost_of_living_val,0,1,l_cost_of_living_val))*-1),1,'-'||l_fm_l_cost_of_living_val,l_fm_l_cost_of_living_val)
INTO l_fm_l_cost_of_living_val
FROM dual;
SELECT decode(l_social_allow_val/(abs(decode(l_social_allow_val,0,1,l_social_allow_val))*-1),1,'-'||l_fm_l_social_allow_val,l_fm_l_social_allow_val)
INTO l_fm_l_social_allow_val
FROM dual;
SELECT decode(l_child_allow_val/(abs(decode(l_child_allow_val,0,1,l_child_allow_val))*-1),1,'-'||l_fm_l_child_allow_val,l_fm_l_child_allow_val)
INTO l_fm_l_child_allow_val
FROM dual;
SELECT decode(l_accommodation_val/(abs(decode(l_accommodation_val,0,1,l_accommodation_val))*-1),1,'-'||l_fm_l_accommodation_val,l_fm_l_accommodation_val)
INTO l_fm_l_accommodation_val
FROM dual;
SELECT decode(l_total/(abs(decode(l_total,0,1,l_total))*-1),1,'-'||l_fm_total_val,l_fm_total_val)
INTO l_fm_total_val
FROM dual;
SELECT decode(l_employee_cont/(abs(decode(l_employee_cont,0,1,l_employee_cont))*-1),1,'-'||l_fm_ee_cont_total,l_fm_ee_cont_total)
INTO l_fm_ee_cont_total
FROM dual;
SELECT decode(l_employer_cont/(abs(decode(l_employer_cont,0,1,l_employer_cont))*-1),1,'-'||l_fm_er_cont_total,l_fm_er_cont_total)
INTO l_fm_er_cont_total
FROM dual;
SELECT decode(l_total_cont/(abs(decode(l_total_cont,0,1,l_total_cont))*-1),1,'-'||l_fm_total_cont,l_fm_total_cont)
INTO l_fm_total_cont
FROM dual;
SELECT decode(l_basic_total/(abs(decode(l_basic_total,0,1,l_basic_total))*-1),1,'-'||l_fm_l_basic_total,l_fm_l_basic_total)
INTO l_fm_l_basic_total
FROM dual;
SELECT decode(l_basic_total/(abs(decode(l_basic_total,0,1,l_basic_total))*-1),1,'-'||l_fm_l_col_total,l_fm_l_col_total)
INTO l_fm_l_col_total
FROM dual;
SELECT decode(l_social_total/(abs(decode(l_social_total,0,1,l_social_total))*-1),1,'-'||l_fm_l_social_total,l_fm_l_social_total)
INTO l_fm_l_social_total
FROM dual;
SELECT decode(l_child_total/(abs(decode(l_child_total,0,1,l_child_total))*-1),1,'-'||l_fm_l_child_total,l_fm_l_child_total)
INTO l_fm_l_child_total
FROM dual;
SELECT decode(l_acco_total/(abs(decode(l_acco_total,0,1,l_acco_total))*-1),1,'-'||l_fm_l_acco_total,l_fm_l_acco_total)
INTO l_fm_l_acco_total
FROM dual;
SELECT decode(l_total_allow_total/(abs(decode(l_total_allow_total,0,1,l_total_allow_total))*-1),1,'-'||l_fm_l_total_allow_total,l_fm_l_total_allow_total)
INTO l_fm_l_total_allow_total
FROM dual;
SELECT decode(l_total_ee_cont_total/(abs(decode(l_total_ee_cont_total,0,1,l_total_ee_cont_total))*-1),1,'-'||l_fm_total_ee_cont_total,l_fm_total_ee_cont_total)
INTO l_fm_total_ee_cont_total
FROM dual;
SELECT decode(l_total_er_cont_total/(abs(decode(l_total_er_cont_total,0,1,l_total_er_cont_total))*-1),1,'-'||l_fm_total_er_cont_total,l_fm_total_er_cont_total)
INTO l_fm_total_er_cont_total
FROM dual;
SELECT decode(l_total_cont_total/(abs(decode(l_total_cont_total,0,1,l_total_cont_total))*-1),1,'-'||l_fm_total_cont_total,l_fm_total_cont_total)
INTO l_fm_total_cont_total
FROM dual;
select userenv('LANGUAGE') into g_nls_db_char from dual;
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_F1_ar_AE.pdf');
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_F2_ar_AE.pdf');
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_F6_ar_AE.rtf');
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_MCP_ar_AE.rtf');
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_MCF_ar_AE.rtf');
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from fnd_lobs where file_name like '%PAY_F7_ar_AE.rtf');