[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pdb.defined_balance_id
FROM pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_defined_balances pdb
WHERE pbd.database_item_suffix = p_dimension_suffix
AND pbd.business_group_id IS NULL
AND pbd.legislation_code = 'DK'
AND pbt.balance_name = p_balance_name
AND pbt.business_group_id IS NULL
AND pbt.legislation_code = 'DK'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.business_group_id IS NULL
AND pdb.legislation_code = 'DK';
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pbt.legislation_code='DK'
AND pbt.balance_name = p_balance_name
AND pbd.legislation_code = 'DK'
AND pbd.database_item_suffix = p_balance_dim
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
SELECT PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'PAYROLL')
-- ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'PAYROLL_PERIOD')
,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'PAYROLL_TYPE') /* 9489806 */
,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'START_DATE') /* 9489806 */
,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'END_DATE') /* 9489806 */
,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'TEST_SUBMISSION')
,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'COMPANY_TERMINATING')
,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'SENDER_ID')
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS' ;
select * from hr_organization_information
where org_information_context = 'DK_SERVICE_PROVIDER_DETAILS'
and organization_id in (
select organization_id from hr_organization_units
where business_group_id= l_business_group_id);
SELECT ptp.start_date, ptp.end_date, ptp.period_name,
default_dd_date,
decode(PERIOD_TYPE
,'Calendar Month','1'
,'Bi-Week' ,'2'
,'Week' ,'3'
,'Lunar Month' ,'4') PAYROLL_PERIOD
from per_time_periods ptp
WHERE payroll_id = p_payroll_id
AND time_period_id = p_payroll_period;
select payroll_name from pay_payrolls_f
where payroll_id=id;
SELECT DISTINCT
ppf.payroll_name,
ppf.payroll_id,
ptp.start_date,
ptp.end_date,
ptp.period_name,
ptp.default_dd_date,
decode(ptp.PERIOD_TYPE
,'Calendar Month','1'
,'Bi-Week' ,'2'
,'Week' ,'3'
,'Lunar Month' ,'4') PAYROLL_PERIOD,
ptp.TIME_PERIOD_ID
FROM
pay_payrolls_f ppf,
per_assignments_f paf,
hr_organization_units hou,
hr_organization_information hoi,
hr_soft_coding_keyflex scl,
per_time_periods ptp
WHERE hou.business_group_id = l_business_group_id
AND hou.organization_id = g_legal_employer_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
AND paf.payroll_id = ppf.payroll_id
AND ppf.payroll_id=NVL(g_payroll_id,ppf.payroll_id)
AND ppf.effective_start_date <= l_canonical_start_date and ppf.effective_end_date >= l_canonical_end_date
AND ptp.payroll_id=ppf.payroll_id
AND ptp.end_date BETWEEN l_canonical_start_date AND l_canonical_end_date
AND paf.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
AND scl.segment1 = to_char(hou.organization_id)
AND ppf.business_group_id = l_business_group_id;
p_sql := '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 = ppf.business_group_id ORDER BY ppf.person_id';
SELECT count(*)
INTO l_count
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYDKEINA'
AND action_context_id = p_payroll_action_id;
SELECT to_char(sysdate,'yyyymmdd') INTO l_date FROM dual;
SELECT to_char(sysdate,'hhmiss') INTO l_time FROM dual;
SELECT as1.person_id person_id,
act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id
FROM pay_payroll_actions ppa
,pay_payroll_actions appa
,pay_payroll_actions appa2
,pay_assignment_actions act
,pay_assignment_actions act1
,pay_action_interlocks pai
,per_all_assignments_f as1
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
AND as1.person_id BETWEEN p_start_person
AND p_end_person
AND appa.action_type IN ('R','Q')
-- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
--aapa table add time period check
-- AND appa.time_period_id = p_payroll_period
AND appa.effective_date between l_canonical_start_date and l_canonical_end_date
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
-- Add payroll id
AND as1.payroll_id = nvl(p_payroll_id,as1.payroll_id) /* 9489806 */
-- Commenting Code to Include Terminated Assignments
-- AND ppa.effective_date BETWEEN as1.effective_start_date
-- AND as1.effective_end_date
AND act.action_status IN ('C','S') -- 10229494
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status IN ('C','S') -- 10229494
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
-- Prepayments or Quickpay Prepayments
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
ORDER BY as1.person_id , act.assignment_id;
select start_date, end_date from per_time_periods
where payroll_id = p_payroll_id
and time_period_id = p_payroll_period;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pap.national_identifier cpr , pap.person_id , pac.assignment_id, pap.full_name, to_char(pap.date_of_birth,'yyyymmdd') dob,
pap.first_name||' '||pap.middle_names||' '||pap.last_name pname, pap.sex, pap.start_date, pap.business_group_id,
assign.hourly_salaried_code HOURLY_SALARIED_CODE, assign.assignment_number, assign.organization_id, assign.primary_flag,
assign.location_id
FROM
pay_assignment_actions pac,
per_all_assignments_f assign,
per_all_people_f pap
WHERE pac.assignment_action_id = p_asg_act_id
AND assign.assignment_id = pac.assignment_id
AND assign.person_id = pap.person_id
AND pap.per_information_category = 'DK'
AND p_asg_effective_date BETWEEN assign.effective_start_date
AND assign.effective_end_date
AND p_asg_effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT MAX(date_start) lhd FROM per_periods_of_service
WHERE person_id=pid
AND date_start <= p_end_date;
SELECT PEI_INFORMATION1 yes_no ,PEI_INFORMATION2 stationed_abroad FROM per_people_extra_info
WHERE person_id=pid
AND information_type='DK_EINCOME_FORIEGN_IND';
SELECT *
FROM per_addresses_v
WHERE person_id =pid
and primary_flag='Y';
SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4,
hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =bg_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = hr_org_id
AND hoi1.org_information1 = 'HR_ORG'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='DK_EMPLOYMENT_DEFAULTS' ;
SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(g_payroll_action_id)
AND pai.action_information3 IN ('2001')
AND pai.action_information4 = p_se_no;
SELECT ptp.start_date, ptp.end_date,
default_dd_date,
decode(PERIOD_TYPE
,'Calendar Month','1'
,'Bi-Week' ,'2'
,'Week' ,'3'
,'Lunar Month' ,'4') PAYROLL_PERIOD
from per_time_periods ptp
WHERE payroll_id = p_payroll_id
AND time_period_id = p_payroll_period;
SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =g_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS' ;
SELECT lei_information1
FROM hr_location_extra_info
WHERE location_id = p_location_id
AND information_type='DK_LOCATION_INFO';
SELECT DISTINCT
ppf.payroll_id,
ptp.time_period_id,
ppf.payroll_name,
ptp.period_name
FROM
pay_payrolls_f ppf,
per_assignments_f paf,
per_time_periods ptp
WHERE paf.payroll_id = ppf.payroll_id
AND paf.assignment_id=c_assignment_id
AND ppf.payroll_id=NVL(g_payroll_id,ppf.payroll_id)
AND ppf.effective_start_date <= c_canonical_start_date and ppf.effective_end_date >= c_canonical_end_date
AND ptp.payroll_id=ppf.payroll_id
AND ptp.end_date BETWEEN c_canonical_start_date AND c_canonical_end_date
AND ppf.business_group_id = g_business_group_id
AND ptp.end_date between paf.effective_start_date and paf.effective_end_date;
SELECT eev1.screen_entry_value screen_entry_value
FROM per_all_assignments_f asg1
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND per.person_id = asg1.person_id
AND et.element_name = 'Employee ATP'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'ATP Table'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.element_link_id = el.element_link_id
AND ee.assignment_id = asg1.assignment_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND asg1.effective_end_date >= p_start_date
AND asg1.effective_start_date <= p_end_date
AND per.effective_end_date >= p_start_date
AND per.effective_start_date <= p_end_date
AND ee.effective_end_date >= p_start_date
AND ee.effective_start_date <= p_end_date
AND ((eev1.effective_start_date <= p_start_date
AND eev1.effective_end_date >= p_start_date )
OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
AND eev1.effective_end_date >= p_end_date ));
SELECT ee.effective_start_date, eev1.screen_entry_value
FROM per_all_assignments_f asg1
,per_all_people_f per
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND per.person_id = asg1.person_id
AND et.element_name = 'Tax Card'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'Tax Card Type'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.element_link_id = el.element_link_id
AND ee.assignment_id = asg1.assignment_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND asg1.effective_end_date >= p_start_date
AND asg1.effective_start_date <= p_end_date
AND per.effective_end_date >= p_start_date
AND per.effective_start_date <= p_end_date
AND ee.effective_end_date >= p_start_date
AND ee.effective_start_date <= p_end_date
AND ((eev1.effective_start_date <= p_start_date
AND eev1.effective_end_date >= p_start_date )
OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
AND eev1.effective_end_date >= p_end_date ));
SELECT MAX( EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
FROM per_all_assignments_f paa
,pay_assignment_actions pac
WHERE pac.assignment_action_id = p_asg_act_id
AND paa.assignment_id = pac.assignment_id
-- AND paa.EFFECTIVE_START_DATE <= p_end_date 10322958
AND paa.EFFECTIVE_END_DATE > = p_start_date
AND assignment_status_type_id IN
(select assignment_status_type_id
from per_assignment_status_types
where per_system_status = 'ACTIVE_ASSIGN'
and active_flag = 'Y'
and (( legislation_code is null
and business_group_id is null)
OR (BUSINESS_GROUP_ID = p_business_group_id)));
SELECT ue.creator_id
FROM ff_user_entities ue, ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'DK'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
select MAX(pac.ASSIGNMENT_ACTION_ID) id
from pay_assignment_actions pac, pay_payroll_actions ppa
where ppa.action_type IN ('R','Q')
and pac.payroll_action_id = ppa.payroll_action_id
and pac.assignment_id= assg_id
and ppa.payroll_id=pid
and ppa.time_period_id=tid
and pac.tax_unit_id=le_id;
SELECT *
FROM per_assignment_extra_info
WHERE information_type = 'DK_EINCOME_INFO'
AND assignment_id = p_assgt_id;
SELECT 1
FROM pay_payroll_actions ppa
,pay_payroll_actions appa
,pay_payroll_actions appa2
,pay_assignment_actions act
,pay_assignment_actions act1
,pay_action_interlocks pai
,per_all_assignments_f as1
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
AND appa.action_type IN ('R','Q')
AND act.payroll_action_id = appa.payroll_action_id
AND appa.time_period_id = p_payroll_period
AND appa.effective_date between l_canonical_start_date and l_canonical_end_date
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
AND as1.payroll_id = p_payroll_id
AND act.action_status IN ('C','S') -- 10229494
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status IN ('C','S') -- 10229494
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
-- Prepayments or Quickpay Prepayments
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
AND as1.assignment_id = l_assignment_id;
SELECT NVL(org_information5,'N')
FROM hr_organization_information
WHERE organization_id = p_org_id
and org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
SELECT PRL_INFORMATION1
FROM pay_payrolls_f ppf
WHERE PAYROLL_ID = p_payroll_id
AND p_date_earned BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE;
SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) asg_action_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id
and ppa.action_type in ('R','Q','I','B','V')
and paa.source_action_id is null
AND ppa.effective_date between p_start_date AND p_end_date;
SELECT 'Y'
FROM pay_payroll_actions ppa
, pay_user_tables put
, pay_user_columns puc
, pay_user_column_instances_f puci
, pay_user_rows_f pur
, pay_user_columns puc1
, pay_user_column_instances_f puci1
, pay_user_rows_f pur1
WHERE ppa.payroll_action_id = p_payroll_action_id
AND put.user_table_name = 'DK_PBS_DATA'
AND put.LEGISLATION_CODE ='DK'
AND puc.user_table_id = put.user_table_id
AND puc.user_column_name = 'Information Type'
AND puci.user_column_id = puc.user_column_id
AND ppa.effective_date between puci.effective_start_date and puci.effective_end_date
AND pur.user_row_id = puci.user_row_id
AND ppa.effective_date between pur.effective_start_date and pur.effective_end_date
and puc1.user_table_id = put.user_table_id
and puc1.user_column_name = 'PBS Number'
and puci1.user_column_id = puc1.user_column_id
and puci1.business_group_id =ppa.business_group_id
and ppa.effective_date between puci1.effective_start_date and puci1.effective_end_date
and pur1.user_row_id = puci1.user_row_id
and pur1.user_row_id = pur.user_row_id
and ppa.effective_date between pur1.effective_start_date and pur1.effective_end_date
and puci.value = '400';
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information3 IN ('5000')
AND pai.action_information25 =p_payroll_id
AND pai.action_information24 =p_period_id
AND pai.action_information9='00'; /* 9587046 */
SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information3 IN ('5000')
AND pai.action_information25 =p_payroll_id
AND pai.action_information9 =p_employement_type
AND pai.action_information24 =p_period_id
AND pai.action_information8 =p_green_land_code
and pai.action_information11 =p_se_no; /* 8847591 */
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information3 IN ('5000')
AND pai.action_information25 =p_payroll_id
AND pai.action_information24 =p_period_id
AND pai.action_information9='00'; /* 9587046 */
SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information3 IN ('5000R')
AND pai.action_information25 =p_payroll_id
AND pai.action_information9 =p_employement_type
AND pai.action_information24 =p_period_id
AND pai.action_information8 =p_green_land_code
and pai.action_information11 =p_se_no; /* 8847591 */
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_context_id = to_char(p_assignment_action_id)
AND pai.action_information3 IN ('6000')
AND pai.action_information25 =p_payroll_id
AND pai.action_information24 =p_period_id;
SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_assignment_action_id)
AND pai.action_information3 IN ('6000R')
AND pai.action_information25 =p_payroll_id
AND pai.action_information26 =p_employement_type /* 8847591check */
AND pai.action_information24 =p_period_id
AND pai.action_information27 =p_green_land_code
and pai.action_information11 =p_se_no ; /* 8847591 */
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_context_id = to_char(p_assignment_action_id)
AND pai.action_information4=to_char(p_person_id)
AND pai.action_information3 IN ('8001')
AND pai.action_information25 =p_payroll_id
AND pai.action_information24 =p_period_id;
SELECT 1
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_assignment_action_id)
AND pai.action_information3 IN ('8001R')
AND pai.action_information25 =p_payroll_id
AND pai.action_information26 =p_employement_type /* 8847591check */
AND pai.action_information24 =p_period_id
AND pai.action_information27 =p_green_land_code; /* 8847591 */
SELECT payroll_action_id, assignment_id
INTO l_payroll_action_id,l_ass_id /* 9489806 */
FROM pay_assignment_actions
WHERE assignment_action_id=p_assignment_action_id;
select to_char(HIRE_DATE) into l_hd from PER_PEOPLE_V where NATIONAL_IDENTIFIER=rg_csr_get_person_details.cpr ;
fnd_file.put_line(fnd_file.log,'Inserting terminators');
select DECODE(l_sex,'M','1','F','2','3') into l_sex from dual;
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 16: Inserted 6002');
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 17: Inserted 6003');
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 18: Inserted 6004');
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 19: Inserted 6005');
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 19-1: Inserted 6005:0203');
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted 6102-1');
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted 6102-2');
fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted 6102-3');
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
-- AND pai.action_information3 IN ('1000','2001','2101','5000','6000')
AND pai.action_information3 IN ('1000','2001') -- 9489806
ORDER BY pai.action_information3,action_context_id DESC
FOR UPDATE;
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
-- AND pai.action_information3 IN ('1000','2001','2101','5000','6000')
AND pai.action_information3 IN ('5000') -- 9489806
AND pai.action_information11=p_se_no
ORDER BY pai.action_information3,action_context_id DESC
FOR UPDATE;
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.ACTION_CONTEXT_TYPE = 'AAP'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information3 IN ('2101') --13063867
AND pai.action_information11= p_se_no
ORDER BY pai.action_information3,action_context_id DESC
FOR UPDATE;
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information3 IN ('6000')
AND pai.action_information25=to_char(p_payroll_id)
AND pai.action_information26=p_employement_type
AND pai.action_information24=p_time_period_id
AND pai.action_information27=p_green_land_code /* 8847591 */
AND pai.action_information11=p_se_no
ORDER BY pai.action_information3,action_context_id DESC
FOR UPDATE;
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information3 IN ('6000R')
AND pai.action_information25=to_char(p_payroll_id)
AND pai.action_information26=p_employement_type
AND pai.action_information24=p_time_period_id
AND pai.action_information27=p_green_land_code /* 8847591 */
AND pai.action_information11=p_se_no
ORDER BY pai.action_information3,action_context_id DESC
FOR UPDATE;
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information4 = to_char(p_person_id)
AND pai.action_information3 IN ('6001','6002','6003','6004','6005','6102') --13463885
AND nvl(pai.action_information29, 'N') <> 'Y'
AND pai.action_information25=to_char(p_payroll_id)
AND pai.action_information26=p_employement_type
AND pai.action_information24=p_time_period_id
AND pai.action_information27=p_green_land_code /* 8847591 */
ORDER BY pai.action_information3,action_context_id,pai.action_information6 DESC
FOR UPDATE;
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information4 = to_char(p_person_id)
AND pai.action_information3 IN ('6001')
AND nvl(pai.action_information29, 'N') = 'Y'
AND pai.action_information25=to_char(p_payroll_id)
AND pai.action_information26=p_employement_type
AND pai.action_information24=p_time_period_id
AND pai.action_information27=p_green_land_code /* 8847591 */
ORDER BY pai.action_information3,action_context_id,pai.action_information6 DESC
FOR UPDATE;
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information4 = to_char(p_person_id)
AND pai.action_information3 = '8001'
AND pai.action_information25=to_char(p_payroll_id)
AND pai.action_information26=p_employement_type
AND pai.action_information24=p_time_period_id
AND pai.action_information27=p_green_land_code /* 8847591 */
FOR UPDATE;
SELECT *
FROM pay_action_information pai
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information4 = to_char(p_person_id)
AND pai.action_information3 = '8001R'
AND pai.action_information25=to_char(p_payroll_id)
AND pai.action_information26=p_employement_type
AND pai.action_information24=p_time_period_id
AND pai.action_information27=p_green_land_code /* 8847591 */
FOR UPDATE;
l_5000_updated VARCHAR2(1):='N';
l_5000R_updated VARCHAR2(1):='N';
l_6000R_updated VARCHAR2(1):='N'; /* 9587046R */
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE CURRENT OF csr_all_rec_2001;
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE CURRENT OF csr_all_rec_2001;
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE CURRENT OF csr_all_rec_2101;
l_5000_updated:='N';
IF l_5000_updated='N' THEN
l_end_code:=l_end_code + 1 ;
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE CURRENT OF csr_all_rec;
l_5000_updated:='Y';
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE CURRENT OF csr_all_rec_6000;
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE CURRENT OF csr_asg_8001;
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE CURRENT OF csr_asg_all_rec;
l_5000R_updated:='N';
l_6000R_updated:='N'; /* 9587046R */
IF l_5000R_updated='N' THEN
fnd_file.put_line(fnd_file.log,'entered iff l_5000R_updated');
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE pai.action_information3 = '5000R'
AND pai.action_information2= to_char(p_payroll_action_id)
AND pai.action_information9=rg_csr_all_rec.action_information9
AND pai.action_information8=rg_csr_all_rec.action_information8 /* 9587046 */
AND pai.action_information25=rg_csr_all_rec.action_information25
AND pai.action_information24=rg_csr_all_rec.action_information24
and pai.action_information11=rg_csr_all_rec_2001.action_information4;
l_5000R_updated:='Y';
IF l_6000R_updated='N' THEN
l_end_code:=l_end_code + 1 ;
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE pai.action_information3 = '6000R'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information4 = to_char(rg_csr_all_rec_6000.action_information4)
AND pai.action_information25=rg_csr_all_rec_6000.action_information25
AND pai.action_information27=rg_csr_all_rec_6000.action_information27 /* 9587046 */
AND pai.action_information26=rg_csr_all_rec_6000.action_information26
AND pai.action_information24=rg_csr_all_rec_6000.action_information24
and pai.action_information11=rg_csr_all_rec_2001.action_information4;
l_6000R_updated:='Y';
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE CURRENT OF csr_asg_8001R;
fnd_file.put_line(fnd_file.log,'l_5000R_updated'|| l_5000R_updated);
UPDATE pay_action_information pai
SET pai.action_information30 =LPAD(l_end_code,7,'0')
WHERE CURRENT OF csr_asg_all_rec_corr;
UPDATE pay_action_information pai
SET pai.action_information9='09'
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information3 ='5000'; */
/* UPDATE pay_action_information pai
SET pai.action_information9='05'
WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information1 = 'PYDKEINA'
AND pai.action_information2 = to_char(p_payroll_action_id)
AND pai.action_information3 ='5000'; */