[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR(legislative_parameters,
INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
INSTR(legislative_parameters,' ',
INSTR(legislative_parameters,p_token))
- (INSTR(legislative_parameters,p_token)+LENGTH(p_token)))
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT TRUNC(effective_date)
INTO g_system_date
FROM fnd_sessions
WHERE session_id = USERENV('sessionid');
SELECT FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
INTO g_bg_id
FROM dual;
SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
FROM per_all_assignments_f asg
,per_periods_of_service pos
WHERE asg.person_id = pos.person_id
AND asg.assignment_id = p_assignment_id
AND asg.business_group_id = pos.business_group_id
AND asg.business_group_id = g_bg_id
AND NVL(pos.actual_termination_date,(to_date('31-12-4712','DD-MM-YYYY')))
BETWEEN asg.effective_start_date AND asg.effective_end_date
ORDER BY 1 desc;
SELECT 1
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = p_assignment_id
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = TO_CHAR(g_gre_id)
AND g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT COUNT(DISTINCT scl.segment1)
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = p_assignment_id
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND ( asg.effective_start_date BETWEEN g_start_date AND g_end_date
OR
g_start_date BETWEEN asg.effective_start_date AND g_end_date
);
SELECT 1
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
WHERE asg.assignment_id = p_assignment_id
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = TO_CHAR(g_gre_id)
AND (asg.effective_start_date BETWEEN g_start_date AND g_end_date
OR
g_start_date BETWEEN asg.effective_start_date AND g_end_date
)
AND ROWNUM = 1;
SELECT paf.assignment_id assignment_id
FROM per_assignments_f paf
,pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paf.business_group_id = g_bg_id
AND paf.person_id BETWEEN p_start_person AND p_end_person
AND p_payroll_action_id IS NOT NULL
AND paa.tax_unit_id LIKE g_gre_id
AND paa.assignment_id =paf.assignment_id
AND ppa.action_type IN('P','U','I')
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_status = 'C'
AND ppa.effective_date BETWEEN g_start_date and g_end_date
AND paf.effective_start_date <= g_end_date
AND paf.effective_end_date >= g_start_date
AND ppa.business_group_id =g_bg_id
GROUP BY paf.assignment_id;
SELECT TRUNC(effective_date)
INTO g_system_date
FROM fnd_sessions
WHERE session_id = USERENV('sessionid');
SELECT FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
INTO g_bg_id
FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
SELECT pep.employee_number emp_no
,asg.person_id person_id
,DECODE(scl.segment9,'N',DECODE(scl.segment10,'N','N','Y'),'Y')interest
,DECODE(pep.per_information4,NULL,pep.per_information5,pep.per_information4) pan
,DECODE(pep.title,NULL,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
,SUBSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
,INSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title),' ',1)+1)) name
,pep.title title
,fnd_date.date_to_canonical(pep.date_of_birth) dob
,pep.sex gender
,pep.per_information7 residential_status
,pep.email_address emailAddr
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
,per_all_people_f pep
WHERE asg.assignment_id = p_assignment_id
AND pep.person_id = asg.person_id
AND pep.business_group_id = g_bg_id
AND asg.business_group_id = g_bg_id
AND asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = TO_CHAR(p_gre_id)
AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date ;
SELECT nvl(pos.name,job.name) name, job.name job
FROM per_all_positions pos
,per_assignments_f asg
,per_jobs job
WHERE asg.position_id=pos.position_id(+)
AND asg.job_id=job.job_id(+)
AND asg.assignment_id = p_assignment_id
AND asg.business_group_id = g_bg_id
AND p_effective_end_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND p_effective_end_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT DECODE(pea.title,NULL,hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
,SUBSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
,INSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title),' ',1)+1))father
,pea.title title
FROM per_all_people_f pep
,per_all_people_f pea
,per_contact_relationships con
WHERE pep.person_id = p_person_id
AND pea.person_id =con.contact_person_id
AND pep.business_group_id = g_bg_id
AND pea.business_group_id = g_bg_id
AND con.person_id=pep.person_id
AND con.contact_type='JP_FT'
AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
AND p_effective_end_date BETWEEN pea.effective_start_date AND pea.effective_end_date;
SELECT address_id
,address_type
FROM per_addresses
WHERE person_id = p_person_id
AND address_type = DECODE(address_type,'IN_P','IN_P','IN_C')
AND p_effective_end_date BETWEEN date_from AND nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'))
ORDER BY address_type DESC;
SELECT phone_number rep_phone_no
,phone_type
FROM per_phones
WHERE parent_id = p_person_id
AND phone_type = DECODE(phone_type,'H1','H1','M')
AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
ORDER BY phone_type ASC;
l_result_table1.DELETE;
l_result_table2.DELETE;
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN(
'F16 Deductions Sec 80D'
,'F16 Deductions Sec 80DD'
,'F16 Deductions Sec 80DDB'
,'F16 Deductions Sec 80G'
,'F16 Deductions Sec 80GGA'
,'F16 Deductions Sec 80CCF'
)
AND pbd.dimension_name='_ASG_LE_DE_PTD'
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
ORDER BY pbt.balance_name;
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN(
'F16 Deductions Sec 80CCE'
,'F16 Deductions Sec 80E'
,'F16 Deductions Sec 80GG'
,'F16 Deductions Sec 80U'
,'F16 Employee PF Contribution'
,'F16 Total Chapter VI A Deductions'
)
AND pbd.dimension_name='_ASG_LE_DE_PTD'
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
ORDER BY pbt.balance_name;
g_bal_name_tab.DELETE;
g_bal_name_tab.DELETE;
g_balance_value_tab.DELETE;
g_result_table.DELETE;
g_result_table1.DELETE;
g_balance_value_tab1.DELETE;
g_context_table.DELETE;
g_balance_value_tab.DELETE;
g_context_table.DELETE;
g_result_table1.DELETE;
g_result_table.DELETE;
g_context_table.DELETE;
g_result_table1.DELETE;
g_result_table2.DELETE;
g_result_table3.DELETE;
g_result_table.DELETE;
g_balance_value_tab.DELETE;
g_balance_value_tab1.DELETE;
g_context_table.DELETE;
g_balance_value_tab1.DELETE;
g_result_table1.DELETE;
g_context_table.DELETE;
g_balance_value_tab1.DELETE;
g_result_table1.DELETE;
g_context_table.DELETE;
g_balance_value_tab1.DELETE;
g_result_table1.DELETE;
g_context_table.DELETE;
SELECT action_information_id
,object_version_number
FROM pay_action_information
WHERE action_information_category = 'IN_EOY_ALLOW'
AND source_id = p_run_asg_action_id
AND action_context_id = p_arc_asg_action_id
AND action_information1 = 'House Rent Allowance'
ORDER BY action_information_id DESC;
SELECT pur.row_low_range_or_name name
FROM pay_user_rows_f pur,
pay_user_tables put
WHERE pur.user_table_id = put.user_table_id
AND put.user_table_name = 'IN_ALLOWANCES'
AND put.legislation_code = 'IN'
AND (pur.legislation_code = 'IN' OR pur.business_group_id = g_bg_id)
AND g_start_date BETWEEN pur.effective_start_date AND pur.effective_end_date
ORDER by name ASC;
pay_action_information_api.update_action_information
(
p_action_information_id => l_action_info_id
,p_object_version_number => l_ovn
,p_action_information5 => l_value
);
g_context_table.DELETE;
g_result_table.DELETE;
g_result_table1.DELETE;
g_result_table2.DELETE;
g_result_table3.DELETE;
g_balance_value_tab.DELETE;
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN('Long Term Capital Gains'
,'Short Term Capital Gains'
,'Capital Gains'
,'Loss From House Property'
,'Business and Profession Gains'
,'Other Sources of Income'
)
AND pbd.dimension_name='_ASG_DE_PTD'
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE((pbt.balance_name IN('F16 Education Cess till Date'
,'F16 Sec and HE Cess till Date'
,'F16 Surcharge till Date'
,'F16 Income Tax till Date'
,'F16 Education Cess'
,'F16 Sec and HE Cess'
,'F16 Employment Tax'
,'F16 Entertainment Allowance'
,'F16 Marginal Relief'
,'F16 Profit in lieu of Salary'
,'F16 Relief under Sec 89'
,'F16 Salary Under Section 17'
,'F16 Surcharge'
,'F16 Tax on Total Income'
,'F16 Value of Perquisites'
,'F16 Gross Salary'
,'F16 Gross Salary less Allowances'
,'F16 Income Chargeable Under head Salaries'
,'F16 Gross Total Income'
,'F16 Total Income'
,'F16 Total Tax payable'
,'F16 Balance Tax'
,'F16 Tax Refundable'
,'F16 Allowances Exempt'
,'F16 Other Income'
,'F16 Deductions under Sec 16'
)
AND pbd.dimension_name = '_ASG_LE_DE_PTD')
OR (pbt.balance_name = 'ER Paid Tax on Non Monetary Perquisite'
AND pbd.dimension_name = '_ASG_LE_YTD'))
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN(
'Excess Interest Amount'
,'Excess PF Amount'
,'TDS on Previous Employment'
,'CESS on Previous Employment'
,'Sec and HE Cess on Previous Employment'
,'SC on Previous Employment'
,'Previous Employment Earnings'
)
AND pbd.dimension_name='_ASG_YTD'
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;
g_bal_name_tab.DELETE;
g_bal_name_tab1.DELETE;
g_balance_value_tab1.DELETE;
pay_action_information_api.update_action_information
(p_action_information_id => l_cess_action_info_id
,p_object_version_number => l_cess_ov_id
,p_action_information1 => 'F16 Education Cess'
,p_action_information2 => l_total_cess
);
pay_action_information_api.update_action_information
(p_action_information_id => l_cess_td_action_info_id
,p_object_version_number => l_cess_td_ov_id
,p_action_information1 => 'F16 Education Cess till Date'
,p_action_information2 => l_total_cess_till_date
);
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN('Taxable Allowances'
,'Taxable Perquisites'
,'Monthly Furniture Cost'
,'Furniture Perquisite'
,'Cost and Rent of Furniture'
,'Perquisite Employee Contribution'
,'ER Paid Tax on Monetary Perquisite'
)
AND pbd.dimension_name='_ASG_YTD'
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;
g_context_table.DELETE;
g_bal_name_tab.DELETE;
g_balance_value_tab.DELETE;
SELECT hoi.org_information1 tan
,hoi.org_information2 ward
,hoi.org_information3 emplr_type
,hoi.org_information4 reg_org_id
,hoi.org_information5 tan_ack_no
,hoi.org_information16 income_tax_org_id
,hou.name org_name
,hou.location_id location_id
,hoi.org_information17 dig_sign
,hoi.org_information18 image_f16
FROM hr_organization_information hoi
,hr_organization_units hou
WHERE hoi.organization_id = p_gre_id
AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT hoi.org_information3 pan
,hoi.org_information4 legal_name
FROM hr_organization_information hoi
,hr_organization_units hou
WHERE hoi.organization_id = p_reg_org_id
AND hoi.org_information_context = 'PER_IN_COMPANY_DF'
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT location_id
FROM hr_organization_units
WHERE organization_id = p_income_tax_org_id
AND business_group_id = g_bg_id
AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT nvl(pos.name,job.name) name ,job.name job
FROM per_all_positions pos
,per_assignments_f asg
,per_jobs job
WHERE asg.position_id=pos.position_id(+)
AND asg.job_id=job.job_id(+)
AND asg.person_id = p_person_id
AND asg.primary_flag = 'Y'
AND asg.business_group_id = g_bg_id
AND p_effective_end_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND p_effective_end_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT DECODE(pea.title,NULL,hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
,SUBSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title)
,INSTR(hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title),' ',1)+1)) father
,pea.title title
FROM per_all_people_f pep
,per_all_people_f pea
,per_contact_relationships con
WHERE pep.person_id = p_person_id
AND pea.person_id =con.contact_person_id
AND pep.business_group_id = g_bg_id
AND pea.business_group_id = g_bg_id
AND con.person_id=pep.person_id
AND con.contact_type='JP_FT'
AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
AND p_effective_end_date BETWEEN pea.effective_start_date AND pea.effective_end_date;
SELECT hoi.org_information1 person_id
,DECODE(pep.title,NULL,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
,SUBSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title)
,INSTR(hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title),' ',1)+1)) rep_name
,pep.title title
FROM hr_organization_information hoi
,hr_organization_units hou
,per_all_people_f pep
WHERE hoi.org_information_context = 'PER_IN_INCOME_TAX_REP_DF'
AND hoi.organization_id = p_gre_id
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND pep.person_id = hoi.org_information1
AND pep.business_group_id = hou.business_group_id
AND p_effective_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
AND p_effective_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2)
AND NVL(fnd_date.canonical_to_date(hoi.org_information3),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT hou.location_id rep_location
FROM per_all_assignments_f asg
,hr_organization_units hou
WHERE asg.person_id = p_person_id
AND asg.primary_flag = 'Y'
AND asg.business_group_id = g_bg_id
AND hou.organization_id = asg.organization_id
AND hou.business_group_id = asg.business_group_id
AND p_effective_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT phone_number rep_phone_no
,phone_type
FROM per_phones
WHERE parent_id = p_person_id
AND phone_type = DECODE(phone_type,'H1','H1','M')
AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
ORDER BY phone_type ASC;
SELECT phone_number work_fax
FROM per_phones
WHERE parent_id = p_person_id
AND phone_type = 'WF'
AND p_effective_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT paa.assignment_id
,paa.payroll_action_id
FROM pay_assignment_actions paa
,per_all_assignments_f paf
WHERE paa.assignment_action_id = p_assignment_action_id
AND paa.assignment_id = paf.assignment_id
AND ROWNUM =1;
SELECT GREATEST(asg.effective_start_date,g_start_date) start_date
,LEAST(asg.effective_end_date,g_end_date) end_date
,scl.segment1
FROM per_all_assignments_f asg
,hr_soft_coding_keyflex scl
,pay_assignment_actions paa
WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND paa.assignment_action_id = p_assignment_action_id
AND asg.assignment_id = paa.assignment_id
AND scl.segment1 LIKE TO_CHAR(g_gre_id)
AND ( asg.effective_start_date BETWEEN g_start_date AND g_end_date
OR g_start_date BETWEEN asg.effective_start_date AND g_end_date
)
AND GREATEST(asg.effective_start_date,g_start_date) <= LEAST(asg.effective_end_date,g_end_date)
ORDER BY 1 asc;
SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) run_asg_action_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_assignments_f paf
WHERE paf.assignment_id = paa.assignment_id
AND paf.assignment_id = p_assignment_id
AND paa.tax_unit_id = p_tax_unit_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN('R','Q','I','B')
AND ppa.action_status ='C'
AND ppa.effective_date between p_start_date and p_end_date
AND paa.source_action_id IS NULL
AND ppa.payroll_id = paf.payroll_id
AND (1 = DECODE(ppa.action_type,'I',1,0)
OR EXISTS (SELECT ''
FROM pay_action_interlocks intk,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
WHERE intk.locked_action_id = paa.assignment_Action_id
AND intk.locking_action_id = paa1.assignment_action_id
AND paa1.payroll_action_id =ppa1.payroll_action_id
AND paa1.assignment_id = p_assignment_id
AND ppa1.action_type in('P','U')
AND ppa.action_type in('R','Q','B')
AND ppa1.action_status ='C'
AND ppa1.effective_date BETWEEN p_start_date and p_end_date
AND ROWNUM =1 ));
SELECT ppa.date_earned run_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = l_run_assact;
SELECT ppa.effective_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks intk
WHERE intk.locked_action_id = l_run_assact
AND intk.locking_action_id =paa.assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN('P','U');
SELECT 1
FROM pay_action_information
WHERE action_information_category = 'IN_EOY_ORG'
AND action_context_type = 'PA'
AND action_context_id = p_payroll_action_id
AND action_information1 = p_gre_id;
SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
FROM per_all_assignments_f asg
,per_periods_of_service pos
WHERE asg.person_id = pos.person_id
AND asg.assignment_id = p_assignment_id
AND asg.business_group_id = pos.business_group_id
AND asg.business_group_id = g_bg_id
AND NVL(pos.actual_termination_date,(to_date('31-12-4712','DD-MM-YYYY')))
BETWEEN asg.effective_start_date AND asg.effective_end_date
ORDER BY 1 desc;
g_asg_tab.DELETE;