[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tag
FROM fnd_lookup_values
WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
INSTR(USERENV('LANGUAGE'), '.') + 1)
AND language = 'US';
select distinct hasa.include_or_exclude from hr_assignment_set_amendments hasa, hr_assignment_sets has
where hasa.assignment_set_id = has.assignment_set_id
and has.business_group_id = p_bg_id
and has.assignment_set_id = l_assignment_set;
SELECT distinct paa.payroll_action_id, paa.assignment_action_id,
nvl(SUBSTR(pactd.action_information1,1,9),' ') PPSN,
-- for bug 5301598
nvl(SUBSTR(pactd.action_information2,1,12),' ') Works,
pactd.action_information3 TotIWeeks,
pactd.action_information4 IClass,
pactd.action_information5 SClass,
pactd.action_information6 SWeeks,
pactd.action_information7 TClass,
pactd.action_information8 TWeeks,
pactd.action_information9 FClass,
pactd.action_information10 FWeeks,
substr(pactd.action_information11,1,instr(pactd.action_information11,'-',1)-1) FifthClass,
substr(pactd.action_information11,instr(pactd.action_information11,'-',1)+1,length(pactd.action_information11)) FifthWeek,
pactd.action_information12 NetTax,
pactd.action_information13 TaxPaid,
pactd.action_information14 EmpPRSI,
pactd.action_information15 TotPRSI,
pactd.action_information16 Pay,
pactd.action_information17 TaxBasis,
pactd.action_information18 SurName,
pactd.action_information19 FirstName,
to_char(to_date(trim(pactd.action_information20),'DD-MM-YYYY'),'DD/MM/YYYY') DOB,
pactd.action_information21 Address1,
pactd.action_information22 Address2,
pactd.action_information23 Address3,
to_char(to_date(trim(pactd.action_information24),'DD-MM-YYYY'),'DD/MM/YYYY') StartDate,
decode(to_char(to_date(trim(pactd.action_information25),'DD-MM-YYYY'),'DD/MM/YYYY'),'31/12/4712',null,to_char(to_date(trim(pactd.action_information25),'DD-MM-YYYY'),'DD/MM/YYYY')) EndDate,
pactd.action_information26 Credit,
pactd.action_information27
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_assignment_actions paad,
pay_action_information pactd,
pay_action_information pai,
per_assignments_f paaf,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex flex
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND ppa.action_type ='X'
AND ppa.business_group_id = p_bg_id
AND paa.source_action_id is null
AND pai.action_context_id = paa.assignment_action_id
AND pai.action_information_category = 'IE P35 DETAIL'
AND ppa.report_type = 'IEP35'
AND paa.assignment_id = pai.assignment_id
AND paaf.assignment_id = paa.assignment_id
AND paaf.business_group_id = ppa.business_group_id
--For Detail Record
AND paad.payroll_action_id = paa.payroll_action_id
AND pactd.action_information_category = 'IE P35 DETAIL'
AND pactd.action_context_type = 'AAP'
AND paad.assignment_action_id = pactd.action_context_id
AND paad.assignment_action_id = paa.assignment_action_id
--End of Detail Record
AND paaf.payroll_id = ppf.payroll_id
AND ppf.effective_start_date <= l_end_date
AND ppf.effective_end_date >= l_start_date
AND flex.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
AND flex.segment4 = p_emp_no
AND paaf.effective_start_date <= l_end_date
AND paaf.effective_end_date >= l_start_date
AND paaf.ASSIGNMENT_TYPE <> 'A' --16210193
AND TO_DATE (
pay_ie_p35.get_parameter (
ppa.payroll_action_id,
'END_DATE'
),
'YYYY/MM/DD'
) BETWEEN l_start_date AND l_end_date
AND (ppf.payroll_id in (select b.payroll_id from per_assignments_f a,per_assignments_f b
where a.payroll_id = l_p_payroll
and a.person_id = b.person_id
and a.person_id = paaf.person_id
--bug 6642916
and a.effective_start_date<= l_end_date
and a.effective_end_date >= l_start_date) or l_p_payroll is null)
AND ((l_assignment_set is not null
AND (l_set_flag ='I' AND EXISTS(SELECT 1
FROM hr_assignment_set_amendments hasa
, hr_assignment_sets has
, per_assignments_f paf
WHERE has.assignment_set_id = hasa.assignment_set_id
AND has.business_group_id = p_bg_id
AND has.assignment_set_id = l_assignment_set
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = paaf.person_id)
OR l_set_flag = 'E' AND NOT EXISTS(SELECT 1
FROM hr_assignment_set_amendments hasa
, hr_assignment_sets has
, per_assignments_f paf
WHERE has.assignment_set_id = hasa.assignment_set_id
AND has.business_group_id = p_bg_id
AND has.assignment_set_id = l_assignment_set
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id = paaf.person_id)))
OR l_assignment_set IS NULL)
ORDER BY SurName, FirstName;
SELECT count(decode(pact.action_information2,0,null,null,null,1)) EMP_RBS,
sum(to_number(pact.action_information2)) EMP_RBS_BAL,
count(decode(pact.action_information3,0,null,null,null,1)) EMPR_RBS,
sum(to_number(pact.action_information3)) EMPR_RBS_BAL,
count(decode(pact.action_information4,0,null,null,null,1)) EMP_PRSA,
sum(to_number(pact.action_information4)) EMP_PRSA_BAL,
count(decode(pact.action_information5,0,null,null,null,1)) EMPR_PRSA,
sum(to_number(pact.action_information5)) EMPR_PRSA_BAL,
count(decode(pact.action_information6,0,null,null,null,1)) EMP_RAC,
sum(to_number(pact.action_information6)) EMP_RAC_BAL,
sum(to_number(pact.action_information1)) TAXABLEBENEFITS,
count(decode(pact.action_information23,0,null,null,null,1)) EMP_PARKING, /* knadhan */
sum(to_number(pact.action_information23)) EMP_PARKING_BAL,
sum(to_number(pact.action_information19)) EMP_INCOME_LEVY_BAL,
sum(to_number(pact.action_information18)) EMP_GROSS_INCOME
--12382953
,sum(to_number(pact.action_information7)) EMP_USC_BAL,
sum(to_number(pact.action_information27)) EMP_GROSS_INCOME_USC,
sum(to_number(pact.action_information28)) EXCLUSIONORDER
--12382953
,sum(to_number(pact.action_information22)) EMP_IB --14656910
FROM pay_assignment_actions paa
,pay_action_information pact
WHERE paa.payroll_action_id = p_payroll_action_id
and paa.assignment_action_id = pact.action_context_id
and paa.assignment_action_id = p_assignment_action_id
and paa.source_action_id is null
and pact.action_information_category = 'IE P35 ADDITIONAL DETAILS'
and pact.action_context_type = 'AAP';
SELECT
to_char(ppa.request_id),
p_end_date,
to_char(ppa.effective_date,'dd-mm-yyyy'),
pact.action_information1,
pact.action_information26 ,
pact.action_information27 ,
pact.action_information28 ,
pact.action_information5 ,
pact.action_information6 ,
pact.action_information7 ,
decode(trim(p_weeks),'Y','1','0'),
'Oracle HRMS',
'E'
FROM pay_payroll_actions ppa
,pay_action_information pact
WHERE ppa.payroll_action_id = p_payroll_action_id
AND pact.action_context_id = ppa.payroll_action_id
AND pact.action_information_category = 'ADDRESS DETAILS'
AND pact.action_context_type = 'PA';
vXMLTable.DELETE;
SELECT fnd_date.canonical_to_date (
SUBSTR (fpov.profile_option_value, 1, 4)
|| '01/01 00:00:00'
)
INTO l_start_date
FROM fnd_profile_option_values fpov, fnd_profile_options fpo
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fpo.application_id = fpov.application_id
AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
AND fpov.level_id = 10001
AND fpov.level_value = 0;
SELECT fnd_date.canonical_to_date (
SUBSTR (fpov.profile_option_value, 1, 4)
|| '12/31 23:59:59'
)
INTO l_end_date
FROM fnd_profile_option_values fpov, fnd_profile_options fpo
WHERE fpo.profile_option_id = fpov.profile_option_id
AND fpo.application_id = fpov.application_id
AND fpo.profile_option_name = 'PAY_IE_P35_REPORTING_YEAR'
AND fpov.level_id = 10001
AND fpov.level_value = 0;