[Home] [Help]
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 LPAD(org_information4,9,'0')
FROM hr_organization_information
WHERE organization_id = p_employer_id
AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
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 = 'KW'
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 org_information1
INTO l_local_nationality
FROM hr_organization_information
WHERE org_information_context = 'KW_BG_DETAILS'
AND organization_id = p_business_group_id;
vXMLTable.DELETE;
SELECT distinct asg.assignment_id
,paa.assignment_action_id
FROM per_all_assignments_f asg /*per_assignments_f asg*/
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_all_people_f ppf /*per_people_f ppf*/
WHERE asg.assignment_id = paa.assignment_id
AND asg.person_id = ppf.person_id
AND ppf.nationality = l_local_nationality
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status IN ('C','S') --10375683
AND trunc(ppa.date_earned,'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 trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(p_employer_id);
SELECT NVL(SUM(rrv.RESULT_VALUE),0)
INTO l_deduction_amt
FROM pay_element_entries_f pee
,pay_run_results prr
,pay_run_result_values rrv
,pay_input_values_f piv
WHERE rrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prr.assignment_action_id = rec_get_assacts.assignment_action_id
AND prr.element_entry_id = pee.element_entry_id
AND pee.assignment_id = rec_get_assacts.assignment_id
AND TRUNC(l_effective_date,'MM') between trunc(pee.effective_start_date,'MM') and nvl(pee.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND pee.entry_information3 is not null
AND pee.entry_information3 IN ('71','65','72','999','82','73','85')
AND rrv.result_value IS NOT NULL
AND rrv.input_value_id = piv.input_value_id
AND piv.name = 'Pay Value'
AND prr.element_type_id = piv.element_type_id
AND pee.element_type_id = piv.element_type_id
AND TRUNC(l_effective_date,'MM') between trunc(piv.effective_start_date,'MM') and nvl(piv.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'));
SELECT LPAD(org_information4,9,'0')
FROM hr_organization_information
WHERE organization_id = p_employer_id
AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
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 = 'KW'
AND u.business_group_id is null
AND u.creator_type = 'B';
SELECT distinct asg.person_id
,paa.assignment_action_id
,hscl.segment2
FROM per_assignments_f asg /*per_all_assignments_f asg*/
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_people_f ppf /*per_all_people_f ppf*/
WHERE asg.assignment_id = paa.assignment_id
AND asg.person_id = ppf.person_id
AND ppf.nationality = l_local_nationality
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status IN ('C','S') --10375683
AND trunc(ppa.date_earned,'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 trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(p_employer_id);
SELECT --SUBSTR(full_name,1,30)
hr_person_name.get_person_name
(p_person_id
,l_effective_date
,'DISPLAY_NAME'
,l_user_format)
FROM per_people_f ppf --per_all_people_f ppf
WHERE person_id = p_person_id
AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
SELECT org_information1
INTO l_local_nationality
FROM hr_organization_information
WHERE org_information_context = 'KW_BG_DETAILS'
AND organization_id = p_business_group_id;
SELECT LPAD(org_information4,9,'0')
FROM hr_organization_information
WHERE organization_id = p_employer_id
AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
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 = 'KW'
AND u.business_group_id is null
AND u.creator_type = 'B';
SELECT distinct asg.person_id
,paa.assignment_action_id
,hscl.segment2
,asg.assignment_id
FROM per_assignments_f asg /*per_all_assignments_f asg*/
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_people_f ppf /*per_all_people_f ppf*/
WHERE asg.assignment_id = paa.assignment_id
AND asg.person_id = ppf.person_id
AND ppf.nationality = l_local_nationality
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status IN ('C','S') --10375683
AND trunc(ppa.date_earned,'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 trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(p_employer_id);
SELECT --SUBSTR(full_name,1,30)
hr_person_name.get_person_name
(p_person_id
,l_effective_date
,'DISPLAY_NAME'
,l_user_format)
FROM per_people_f ppf --per_all_people_f ppf
WHERE person_id = p_person_id
AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT NVL(SUM(rrv.RESULT_VALUE),0)
FROM pay_element_entries_f pee
,pay_run_results prr
,pay_run_result_values rrv
,pay_input_values_f piv
WHERE rrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prr.assignment_action_id = rec_get_emp.assignment_action_id
AND prr.element_entry_id = pee.element_entry_id
AND pee.assignment_id = rec_get_emp.assignment_id
AND TRUNC(l_effective_date,'MM') between trunc(pee.effective_start_date,'MM') and nvl(pee.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
AND pee.entry_information3 is not null
AND pee.entry_information3 IN (p_deduction_code)
/*( SELECT i.value
FROM pay_user_column_instances_f i
,pay_user_rows_f r
,pay_user_columns c
,pay_user_tables t
WHERE (i.legislation_code = 'KW' AND i.business_group_id IS NULL)
AND (r.legislation_code = 'KW' AND r.business_group_id IS NULL)
AND c.legislation_code = 'KW'
AND t.legislation_code = 'KW'
AND UPPER(t.user_table_name) = UPPER('KW_DEDUCTION_MAPPING')
AND t.user_table_id = r.user_table_id
AND t.user_table_id = c.user_table_id
AND r.row_low_range_or_name = p_deduction_code
AND r.user_row_id = i.user_row_id
AND UPPER(c.user_column_name) = UPPER('DEDUCTION_TYPE')
AND c.user_column_id = i.user_column_id
AND TRUNC(l_effective_date,'MM') BETWEEN r.effective_start_date AND r.effective_end_date
AND TRUNC(l_effective_date,'MM') BETWEEN i.effective_start_date AND i.effective_end_date
)*/
AND rrv.result_value IS NOT NULL
AND rrv.input_value_id = piv.input_value_id
AND piv.name = 'Pay Value'
AND prr.element_type_id = piv.element_type_id
AND pee.element_type_id = piv.element_type_id
AND TRUNC(l_effective_date,'MM') between trunc(piv.effective_start_date,'MM') and nvl(piv.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'));
SELECT address_line_1 || decode(address_line_2,null,null,',') || address_line_2 || decode(postal_code,null,null,',')|| postal_code
FROM hr_locations hl
, hr_organization_units hou /*hr_all_organization_units hou*/
WHERE hl.location_id = hou.location_id
AND hou.organization_id = p_employer_id;
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
SELECT org_information1
INTO l_local_nationality
FROM hr_organization_information
WHERE org_information_context = 'KW_BG_DETAILS'
AND organization_id = p_business_group_id;
SELECT LPAD(org_information4,9,'0')
FROM hr_organization_information
WHERE organization_id = p_employer_id
AND org_information_context = 'KW_LEGAL_EMPLOYER_DETAILS';
SELECT name
FROM hr_organization_units
WHERE organization_id = p_employer_id;
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 = 'KW'
AND u.business_group_id is null
AND u.creator_type = 'B';
SELECT distinct asg.person_id
,paa.assignment_action_id
,pos.date_start
FROM per_assignments_f asg /*per_all_assignments_f asg*/
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_people_f ppf /*per_all_people_f ppf*/
WHERE asg.assignment_id = paa.assignment_id
AND asg.person_id = ppf.person_id
AND ppf.nationality = l_local_nationality
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 IN ('C','S') --10375683
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 trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(p_employer_id);
SELECT distinct asg.person_id
,paa.assignment_action_id
,pos.actual_termination_date
,ppa.date_earned
FROM per_assignments_f asg /*per_all_assignments_f asg*/
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_people_f ppf /*per_all_people_f ppf*/
WHERE asg.assignment_id = paa.assignment_id
AND asg.person_id = ppf.person_id
AND ppf.nationality = l_local_nationality
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 IN ('C','S') --10375683
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 trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(p_employer_id);
SELECT date_earned, paa.assignment_action_id
FROM per_assignments_f asg /*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 IN ('C','S') --10375683
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
,asg.assignment_id
,paa.assignment_action_id
,date_earned
FROM per_assignments_f asg /*per_all_assignments_f asg*/
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_periods_of_service pos
,per_people_f ppf /*per_all_people_f ppf*/
WHERE asg.assignment_id = paa.assignment_id
AND asg.person_id = ppf.person_id
AND ppf.nationality = l_local_nationality
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 IN ('C','S') --10375683
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(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 trunc(l_effective_date, 'MM') between trunc(ppf.effective_start_date,'MM') and ppf.effective_end_date
AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
AND hscl.segment1 = to_char(p_employer_id);
SELECT /*full_name
hr_person_name.get_person_name
(p_person_id
,l_effective_date
,'DISPLAY_NAME'
,l_user_format)*/
national_identifier
FROM per_people_f ppf /*per_all_people_f ppf*/
WHERE person_id = p_person_id
AND l_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT ORG_INFORMATION1
FROM HR_ORGANIZATION_INFORMATION
WHERE Organization_id = l_emp_id
AND org_information_context = 'KW_SI_DETAILS';
select paa.assignment_action_id
from pay_payroll_actions ppa, pay_assignment_actions paa
Where paa.assignment_id = l_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and ppa.action_status = 'C'
and paa.action_status IN ('C','S') --10375683
and trunc(ppa.date_earned,'MM') = trunc(l_date,'MM') ;
SELECT distinct asg.person_id
,asg.assignment_id
,paa.assignment_action_id
FROM per_assignments_f asg
,pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hscl
,per_people_f ppf
WHERE asg.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type in ('R','Q')
AND ppa.action_status = 'C'
AND paa.action_status in ('C','S') --10375683
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_id)
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.nationality = l_nat;
INSERT INTO fnd_sessions (session_id, effective_date)
VALUES (userenv('sessionid'), l_effective_date);
SELECT org_information1
INTO l_local_nationality
FROM hr_organization_information
WHERE org_information_context = 'KW_BG_DETAILS'
AND organization_id = p_business_group_id;
vXMLTable.DELETE;
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 per_gb_xdo_templates where file_name like '%PAY_R166_ar_KW.pdf'
and effective_start_date between to_date('01-01-2005','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_166_10_ar_KW.pdf'
and effective_start_date between to_date('01-08-2010','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_166_12_ar_KW.pdf'
and effective_start_date between to_date('01-10-2012','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAYR167_ar_KW.rtf'
and effective_start_date between to_date('01-01-2005','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_167_06_ar_KW.rtf'
and effective_start_date between to_date('01-01-2006','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_R168_ar_KW.pdf'
and effective_start_date between to_date('01-01-2005','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
Select file_data
Into p_pdf_blob
From fnd_lobs
Where file_id = (select max(file_id) from per_gb_xdo_templates where file_name like '%PAY_168_12_ar_KW.pdf'
and effective_start_date between to_date('01-10-2012','DD-MM-YYYY') and to_date('31-12-4712','DD-MM-YYYY') );
SELECT value
INTO l_audit_log_dir
FROM v$parameter
WHERE LOWER(name) = 'utl_file_dir';