[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** 02-Dec-2002 puchil 2689191 115.9 Changed the select statement for Cursor 'csr_Hire_date'.
** 02-Dec-2002 srrajago 2689229 115.10 Included 'nocopy' option in all the 'out' parameters of the
** function get_emoluments.
** 14-Mar-2003 srrajago 2850738 115.11 Included the join paa.period_of_service_id = pps.period_of_service_id
** in the cursor csr_hire_date so as to pick up the correct hire date
** incase of rehire.
** 30-May-2003 kaverma 2920731 115.12 Replaced tables per_all_assignments_f and per_all_people_f by secured views
** per_assignments_f and per_people_f respectively form the queries
** 24-Jul-2003 srrajago 3062419 115.13 Added two variables l_fin_start_date and l_fin_end_date for storing the
** financial year start and end date respectively. p_periods value ( end_date
** value only) modified so that it returns different values for IR56B and
** IR56F and IR56G.
** 12-Dec-2003 srrajago 3193217 115.14 Modified the entire logic in the function 'get_emoluments'. Introduced a new
** procedure 'populate_defined_balance_ids'.
** 12-Dec-2003 srrajago 3193217 115.15 Function 'get_emoluments' modified. Check for assignment_action_id passed being 0 or NULL
** has been included.
** 17-Dec-2003 srrajago 3193217 115.16 In the function 'get_emoluments' -> IF check -> Replaced '!=' with '<>' to remove GSCC error.
** 09-Feb-2003 avenkatk 3417275 115.17 In the procedure 'populate_defined_balance_ids',removed references to the 4 IR56_Q quarter balances.
** 14-JUN-2004 abhkumar 3626489 115.18 Removed gscc warnings.
** 15-JUN-2004 abhkumar 3626489 115.19 Added hr_utility.debug_enabled to each of three functions.
** 15-JUN-2004 abhkumar 3626489 115.20 Commented hr_utility.debug_enabled and initialised g_debug to FALSE.
** 31-JAN-2005 JLin 3609072 115.21 Modified to be able to run the balance retrieval batch mode.
** 14-Dec-2005 snimmala 4864213 115.22 Added a new function get_quarters_start_date and is used in the view
** pay_hk_ir56_quarters_info_v.
** 09-Jan-2005 vborhade 4688776 115.23 Modified procedure get_emoluments for period end date.
** 27-Sep-2007 skshin 6432592 115.24 Modified function get_emoluments to display indirect result for IR56_B
** 20-Mar-2009 pmatamsr 8348781 115.25 Added condition in 'get_emoluments' function to fetch null into period dates when IR56 balance
** contains a zero value.
** 03-Apr-2009 pmatamsr 8406450 115.26 Removed code fix done as part of bug 4688776 for non-recurring processing type in 'get_emoluments' function.
** 04-Apr-2009 pmatamsr 8406450 115.27 Modified the code fix comments.
** 16-Apr-2009 avenkatk 8406450 115.28 Added check for Balance Adjustments. If any IR56 is Balance adjusted, the periods dates are fetched
** like Non recurring entries.
** 16-Apr-2009 avenkatk 8406450 115.29 Resolved gscc failure
**
**
*/
as
g_debug boolean;
SELECT min(pee.effective_start_date),
max(pee.effective_end_date),
min(pet.processing_type)
FROM pay_element_types_f pet,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_balance_types pbt,
pay_balance_feeds_f pbf,
pay_input_values_f piv
WHERE pee.assignment_id = p_assignment_id
AND pee.element_link_id = pel.element_link_id
AND pbf.balance_type_id = pbt.balance_type_id
AND pbf.input_value_id = piv.input_value_id
AND piv.element_type_id = pel.element_type_id
AND pel.element_type_id = pet.element_type_id
AND pbt.balance_name = p_balance_name
AND ((pbf.legislation_code = 'HK' and pbf.business_group_id IS NULL) OR
(pbf.business_group_id = piv.business_group_id AND pbf.legislation_code IS NULL))
AND pee.effective_start_date <= p_fin_end_date
AND pee.effective_end_date >= p_fin_start_date;
SELECT hrl.description
FROM hr_lookups hrl
WHERE hrl.lookup_type = 'HK_IR56_BOX_DESC'
AND hrl.lookup_code = p_balance_name
AND to_date('3103'||p_reporting_year,'DDMMYYYY')
BETWEEN nvl(start_date_active,to_date('01010001','DDMMYYYY'))
AND nvl(end_date_active,to_date('31124712','DDMMYYYY'));
SELECT pps.date_start,
pps.actual_termination_date
FROM per_periods_of_service pps,
per_people_f ppf,
per_assignments_f paf
WHERE paf.person_id = ppf.person_id
AND pps.person_id = paf.person_id
AND paf.assignment_id = p_assignment_id
AND paf.period_of_service_id = pps.period_of_service_id; /* Bug No : 2850738 */
SELECT COUNT(pivf.input_value_id)
FROM pay_element_entries_f pee,
pay_element_types_f pet,
pay_input_values_f pivf,
pay_balance_types pbt,
pay_balance_feeds_f pbf
WHERE pee.assignment_id = p_assignment_id
AND pee.entry_type = 'B'
AND pee.element_type_id = pet.element_type_id
AND pet.element_type_id = pivf.element_type_id
AND pbf.input_value_id = pivf.input_value_id
AND pbf.balance_type_id = pbt.balance_type_id
AND pbt.balance_name = p_balance_name
AND pbt.legislation_code = 'HK'
AND ((pbf.legislation_code = 'HK' and pbf.business_group_id IS NULL) OR
(pbf.business_group_id = pivf.business_group_id AND pbf.legislation_code IS NULL))
AND pee.effective_start_date <= p_fin_end_date
AND pee.effective_end_date >= p_fin_start_date;
SELECT decode(pbt.balance_name,'IR56_A',1,'IR56_B',2,'IR56_C',3,'IR56_D',4,'IR56_E',5,
'IR56_F',6,'IR56_G',7,'IR56_H',8,'IR56_I',9,'IR56_J',10,
'IR56_K1',11,'IR56_K2',12,'IR56_K3',13,'IR56_L',14,'IR56_M',15
) sort_index,
pbt.balance_name,
pdb.defined_balance_id defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name IN ('IR56_A','IR56_B','IR56_C','IR56_D','IR56_E','IR56_F','IR56_G','IR56_H',
'IR56_I','IR56_J','IR56_K1','IR56_K2','IR56_K3','IR56_L','IR56_M')
AND pbd.database_item_suffix = '_ASG_LE_YTD'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'HK'
ORDER BY sort_index;
p_balance_value_tab.delete;
g_emol_details.delete;
select min(start_date)
from pay_hk_ir56_quarters_actions_v
where assignment_id = p_assignment_id
and l_source_id = p_source_id;