[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sqlstr := 'SELECT DISTINCT person_id
FROM per_people_f ppf,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id +0 = ppf.business_group_id
ORDER BY ppf.person_id';
SELECT paf.assignment_id
FROM per_people_f ppf,
per_assignments_f paf,
per_periods_of_service pos
WHERE ppf.business_group_id = paf.business_group_id
AND pos.period_of_service_id = paf.period_of_service_id
AND paf.person_id =ppf.person_id
AND paf.person_id BETWEEN p_stperson AND p_endperson
AND (
g_end_date BETWEEN paf.effective_start_date AND paf.effective_end_date
OR
(
pos.final_process_date BETWEEN g_start_date AND g_end_date AND
pos.final_process_date BETWEEN paf.effective_start_date AND paf.effective_end_date
)
)
AND (
( nvl(pos.final_process_date,g_end_date) >= g_end_date
AND g_end_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date)
OR
(
pos.final_process_date BETWEEN g_start_date AND g_end_date AND
pos.final_process_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
)
)
AND ppf.business_group_id = g_bg_id
AND paf.business_group_id =g_bg_id
AND EXISTS (SELECT 1
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_id =paf.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type in('R','Q')
AND decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date) BETWEEN g_start_date AND g_end_date
);
select distinct(to_CHAR(ppa.effective_date,'YYYY')||TO_CHAR(ppa.effective_date,'MM')||ppf.payroll_id) pnum
from pay_payroll_Actions ppa,
pay_assignment_actions paa,
pay_payrolls_f ppf
where paa.payroll_action_id = ppa.payroll_Action_id
and ppa.action_type in('R','Q')
and ppa.payroll_id = ppf.payroll_id
and paa.assignment_id = p_assignment_id
and paa.source_action_id is null
and decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date) BETWEEN g_start_date AND g_end_date
and g_end_date between ppf.effective_start_date and ppf.effective_end_date
ORDER BY pnum;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
UPDATE pay_assignment_actions
SET serial_number = q.pnum
WHERE assignment_action_id = l_actid
AND assignment_id = i.assignment_id
AND payroll_action_id = p_pactid;
SELECT legislative_parameters
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT to_char(ptp.period_num) pno,
to_CHAR(ptp.start_date,'YYYYMMDD') strt,
to_CHAR(ptp.end_date,'YYYYMMDD') ende,
to_CHAR(ptp.regular_payment_date,'YYYY') yr
FROM per_time_periods ptp,
pay_payrolls_f ppf
WHERE ptp.payroll_id = ppf.payroll_id
AND decode(g_trfr_date_used,'E',ptp.end_date,ptp.regular_payment_date) BETWEEN g_start_date and g_end_date
AND EXISTS(SELECT 1
FROM per_assignments_f paf
WHERE paf.payroll_id = ppf.payroll_id
AND paf.business_group_id = g_bg_id)
order by ppf.payroll_name,yr,ptp.period_num;
SELECT DISTINCT pap.payroll_name payroll_name,
fnd_number.canonical_to_number(hoi.org_information3) element_id,
nvl(petl.reporting_name,petl.element_name) element_name,
get_cost_alloc_key_flex(hoi.org_information3) exp_cat_code
FROM hr_organization_information hoi
,hr_organization_units hou
,pay_payrolls_f pap
,pay_element_types_f_tl petl
,pay_element_types_f pet
WHERE hoi.org_information_context = 'PER_CNAO_ORG_INFO'
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND pap.payroll_id = fnd_number.canonical_to_number(hoi.org_information2)
AND EXISTS (SELECT 1
FROM per_assignments_f paf
WHERE paf.payroll_id = pap.payroll_id
and paf.effective_end_date >= g_start_date
and paf.effective_start_date <= g_end_date)
AND EXISTS (SELECT 1
FROM per_assignments_f paf,
hr_soft_coding_keyflex scl
WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = hou.organization_id
AND paf.business_group_id = g_bg_id
)
AND petl.element_type_id = fnd_number.canonical_to_number(hoi.org_information3)
AND pet.element_type_id = petl.element_type_id
AND petl.language = userenv('LANG')
AND hou.date_from <= g_end_date
AND NVL(hou.date_to,TO_DATE('4712/12/31','YYYY/MM/DD')) >= g_start_date
AND g_end_date >= fnd_date.canonical_to_date(hoi.org_information4)
AND g_start_date <= NVL(fnd_date.canonical_to_date(hoi.org_information5),TO_DATE('4712/12/31','YYYY/MM/DD'))
AND g_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND g_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date
UNION
SELECT DISTINCT pap.payroll_name payroll_name,
fnd_number.canonical_to_number(hoi.org_information2) element_id,
nvl(PBT_TL.REPORTING_NAME,PBT_TL.BALANCE_NAME) element_name,
'X' exp_cat_code
FROM hr_organization_information hoi
,hr_organization_units hou
,pay_payrolls_f pap
,PAY_BALANCE_TYPES PBT, PAY_BALANCE_TYPES_TL PBT_TL
WHERE hoi.org_information_context = 'PER_CNAO_BAL_INFO'
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND pap.payroll_id = fnd_number.canonical_to_number(hoi.org_information4)
AND EXISTS (SELECT 1
FROM per_assignments_f paf
WHERE paf.payroll_id = pap.payroll_id
and paf.effective_end_date >= g_start_date
and paf.effective_start_date <= g_end_date)
AND EXISTS (SELECT 1
FROM per_assignments_f paf,
hr_soft_coding_keyflex scl
WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = hou.organization_id
AND paf.business_group_id = g_bg_id
)
AND PBT_TL.balance_type_id = fnd_number.canonical_to_number(hoi.org_information2)
AND PBT.balance_type_id = PBT_TL.balance_type_id
AND PBT_TL.language = userenv('LANG')
AND g_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information5) AND NVL(fnd_date.canonical_to_date(hoi.org_information6),TO_DATE('4712/12/31','YYYY/MM/DD'));
SELECT paf.assignment_id ,
paf.assignment_number,
max(paa.assignment_action_id) assignment_action_id,
ppf.payroll_name
FROM per_assignments_f paf,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_payrolls_f ppf
WHERE paf.effective_start_date <= g_end_date
AND paf.effective_end_date >= g_start_date
AND paf.business_group_id = g_bg_id
AND paa.assignment_id =paf.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type in('R','Q')
and ppa.action_status='C'
and ppf.payroll_id = ppa.payroll_id
AND paf.payroll_id = ppa.payroll_id
AND decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date)
BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date)
BETWEEN g_start_date AND g_end_date
group by paf.assignment_id,paf.assignment_number,ppf.payroll_name, to_CHAR(ppa.effective_date,'YYYY')||TO_CHAR(ppa.effective_date,'MM')
order by to_number(to_CHAR(ppa.effective_date,'YYYY')||TO_CHAR(ppa.effective_date,'MM')),ppf.payroll_name, paf.assignment_number;
SELECT lookup_code,meaning FROM fnd_lookup_values
WHERE lookup_type = p_type
AND enabled_flag = 'Y'
AND lookup_code LIKE p_code
AND language = USERENV('LANG')
ORDER BY lookup_code;
SELECT TO_CHAR(get_employee_number(paf.person_id,ppa.effective_date)) eno,
per_cn_shared_info.get_lookup_meaning(paf.employee_category,'EMPLOYEE_CATG') asg_cat ,
get_employee_name(paf.person_id,ppa.effective_date) emp_name,
to_char(paf.organization_id) asg_org_id,
(select distinct p.payroll_name from pay_payrolls_f p where p.payroll_id = paf.payroll_id) pname,
to_CHAR(ppa.effective_date,'YYYY') yr,
to_number(TO_CHAR(ppa.effective_date,'MM')) pno,
to_CHAR(decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date) ,'YYYY') acct_yr,
to_number(to_CHAR(decode(g_trfr_date_used,'E',ppa.date_earned,ppa.effective_date) ,'MM')) accnt_prd,
'CNY' currency
from per_all_assignments_f paf,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paf.assignment_id = p_assignment_id
and paa.assignment_id = paf.assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_action_id = p_assignment_action_id;
select distinct payroll_id
from pay_payrolls_f
where payroll_name = p_payroll_name;
result.DELETE;
payrec.DELETE;
v_pay_prd.DELETE;
v_ptp_rec.DELETE;
select arch_paa.assignment_action_id asg_action_id ,
ppa.payroll_id payroll_id,
ppa.date_earned earn_date,
ppa.effective_date eff_date
from pay_payroll_actions arch_ppa,
pay_assignment_actions arch_paa,
pay_action_interlocks intl,
pay_payroll_Actions ppa,
pay_assignment_actions paa
where arch_paa.assignment_action_id = intl.locking_action_id
and arch_paa.payroll_Action_id = arch_ppa.payroll_action_id
and arch_paa.source_action_id is not null
and arch_ppa.action_type='X'
and ARCH_ppa.report_type='CN_PAYSLIP_ARCHIVE'
and arch_paa.assignment_id = p_assignment_id
and intl.locked_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_Action_id
and ppa.action_type in('R','Q')
and ppa.payroll_id = p_payroll_id
and paa.assignment_id = p_assignment_id
and paa.source_action_id is null
and to_number(to_char(ppa.effective_date,'MM')) = p_pprd_num
and to_number(to_char(ppa.effective_date,'YYYY')) = p_pyear
order by payroll_id,asg_action_id;
select person.employee_number eno,
pname.payroll_name pay_name,
to_char(p_effective_date,'YYYY') pyear,
to_number(to_char(p_effective_date,'MM')) pnum,
pet.element_type_id eid,
trim(to_char(fnd_number.canonical_to_number(pai.action_information5),'999999990.99')) current_amount
from pay_action_information pai,
(select pap.payroll_name
from pay_all_payrolls_f pap
where pap.payroll_id = p_payroll_id
AND g_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date) pname,
(SELECT ppf.employee_number
FROM per_all_people_f ppf,
per_all_assignments_f paf
WHERE paf.person_id = ppf.person_id
AND paf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND p_date_earned BETWEEN paf.effective_start_date and paf.effective_end_date) person,
hr_organization_information hoi,
hr_organization_units hou,
pay_element_types_f_tl petl,
pay_element_types_f pet
where action_context_id = p_action_context_id
and pai.action_information_category = 'APAC ELEMENTS'
and pai.action_information1 = nvl(petl.reporting_name,petl.element_name)
and hoi.org_information_context = 'PER_CNAO_ORG_INFO'
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND hoi.org_information2 = p_payroll_id
and petl.element_type_id = hoi.org_information3
and pet.element_type_id = petl.element_type_id
and petl.language = userenv('LANG')
AND g_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('4712/12/31','YYYY/MM/DD'))
AND g_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information4) AND NVL(fnd_date.canonical_to_date(hoi.org_information5),TO_DATE('4712/12/31','YYYY/MM/DD'))
AND g_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
select person.employee_number eno,
pname.payroll_name pay_name,
to_char(p_effective_date,'YYYY') pyear,
to_number(to_char(p_effective_date,'MM')) pnum,
pbt.balance_type_id eid,
trim(to_char(fnd_number.canonical_to_number(pai.action_information6),'999999990.99')) current_amount
from pay_action_information pai,
(select pap.payroll_name
from pay_all_payrolls_f pap
where pap.payroll_id = p_payroll_id
AND g_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date) pname,
(SELECT ppf.employee_number
FROM per_all_people_f ppf,
per_all_assignments_f paf
WHERE paf.person_id = ppf.person_id
AND paf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND p_date_earned BETWEEN paf.effective_start_date and paf.effective_end_date) person,
hr_organization_information hoi,
hr_organization_units hou,
PAY_BALANCE_TYPES PBT, PAY_BALANCE_TYPES_TL PBT_TL
where action_context_id = p_action_context_id
and pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'
and pai.action_information4 = (select nvl(h.ORG_INFORMATION7,PBT_TL.BALANCE_NAME)
from hr_organization_information h
where h.org_information_context = 'Business Group:Payslip Info'
and h.organization_id = g_bg_id
and h.ORG_INFORMATION1 = 'BALANCE'
and h.ORG_INFORMATION4 = PBT_TL.BALANCE_TYPE_ID
and h.ORG_INFORMATION5 = (select pbd.balance_dimension_id
from pay_balance_dimensions pbd
where legislation_code = 'CN'
and dimension_name = '_ASG_PTD')
)
and pai.action_information5 = 'ASG_PTD'
and hoi.org_information_context = 'PER_CNAO_BAL_INFO'
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND hoi.org_information4 = p_payroll_id
and PBT_TL.balance_type_id = hoi.org_information2
and PBT.balance_type_id = PBT_TL.balance_type_id
and PBT_TL.language = userenv('LANG')
AND g_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('4712/12/31','YYYY/MM/DD'))
AND g_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information5) AND NVL(fnd_date.canonical_to_date(hoi.org_information6),TO_DATE('4712/12/31','YYYY/MM/DD'));
select person.employee_number eno,
pname.payroll_name pay_name,
to_char(p_effective_date,'YYYY') pyear,
to_number(to_char(p_effective_date,'MM')) pnum,
pbt.balance_type_id eid,
trim(to_char(fnd_number.canonical_to_number(pai.action_information5),'999999990.99')) current_amount
from pay_action_information pai,
(select pap.payroll_name
from pay_all_payrolls_f pap
where pap.payroll_id = p_payroll_id
AND g_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date) pname,
(SELECT ppf.employee_number
FROM per_all_people_f ppf,
per_all_assignments_f paf
WHERE paf.person_id = ppf.person_id
AND paf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN ppf.effective_start_date and ppf.effective_end_date
AND p_date_earned BETWEEN paf.effective_start_date and paf.effective_end_date) person,
hr_organization_information hoi,
hr_organization_units hou,
PAY_BALANCE_TYPES PBT, PAY_BALANCE_TYPES_TL PBT_TL
where action_context_id = p_action_context_id
and pai.action_information_category = 'APAC BALANCES'
and pai.action_information1 = nvl(PBT_TL.REPORTING_NAME,PBT_TL.BALANCE_NAME)
and hoi.org_information_context = 'PER_CNAO_BAL_INFO'
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND hoi.org_information4 = p_payroll_id
and PBT_TL.balance_type_id = hoi.org_information2
and PBT.balance_type_id = PBT_TL.balance_type_id
and PBT_TL.language = userenv('LANG')
AND g_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('4712/12/31','YYYY/MM/DD'))
AND g_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information5) AND NVL(fnd_date.canonical_to_date(hoi.org_information6),TO_DATE('4712/12/31','YYYY/MM/DD'));
select lookup_code,meaning from fnd_lookup_values
where lookup_type = p_type
and enabled_flag = 'Y'
and lookup_code like p_code
and language = userenv('lang')
order by lookup_code;
select assignment_id,payroll_action_id,serial_number
into l_assignment_id,l_pact,l_serial_number
from
pay_assignment_Actions
where assignment_Action_id = p_assignment_action_id;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name ='TGL_DATE_USED';
SELECT hoi.org_information16
FROM hr_organization_information hoi
,hr_organization_units hou
WHERE hoi.org_information_context = 'PER_CORPORATE_INFO_CN'
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND EXISTS (SELECT 1
FROM per_assignments_f paf,
hr_soft_coding_keyflex scl
WHERE paf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = hou.organization_id
AND paf.business_group_id = g_bg_id
)
ORDER BY org_information16;
SELECT employee_number
FROM per_all_people_f
WHERE person_id = p_person_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT full_name
FROM per_all_people_f
WHERE person_id = p_person_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
select ORG_INFORMATION2
from HR_ORGANIZATION_INFORMATION
where ORG_INFORMATION_CONTEXT='PER_CNAO_EXPEND_CODE_INFO'
and ORGANIZATION_ID = g_bg_id;
select COST_ALLOCATION_KEYFLEX_ID
from pay_element_links_f
where business_group_id = g_bg_id
and element_type_id = p_element_id
and effective_start_date =
(select min(effective_start_date) from pay_element_links_f where business_group_id = g_bg_id
and element_type_id = p_element_id);
statem := 'select '||l_cost_seg||' FROM PAY_COST_ALLOCATION_KEYFLEX '||' where COST_ALLOCATION_KEYFLEX_ID = :l_cost ';
SELECT 1
FROM pay_action_information
WHERE action_context_id = p_pactid;
SELECT USERENV('LANG')
into l_lang
from dual;
SELECT
request_id
INTO
ln_reqid_gle
FROM
pay_payroll_actions
WHERE payroll_action_id = p_pactid;
sqlstr := ' select paa.rowid
from pay_assignment_actions paa,
pay_payroll_actions ppa ,
per_people_f ppf,
pay_payrolls_f p
where ppa.payroll_action_id = :pactid
and paa.payroll_action_id = ppa.payroll_action_id
AND '''||g_end_date||''' between p.effective_start_date and p.effective_end_date
and '''||g_end_date||''' BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND substr(paa.serial_number,7) = p.payroll_id
and ppf.person_id = (select paf.person_id
from per_assignments_f paf
where paf.assignment_id = paa.assignment_id
and rownum =1)
order by to_number(substr(paa.serial_number,1,6)),p.payroll_name,ppf.employee_number
for update of paa.assignment_id';