[Home] [Help]
91: ,per_all_assignments_f asg2
92: ,per_all_people_f per
93: ,pay_element_links_f el
94: ,pay_element_types_f et
95: ,pay_input_values_f iv1
96: ,pay_element_entries_f ee
97: ,pay_element_entry_values_f eev1
98: WHERE asg1.assignment_id = l_asg_id
99: AND l_eff_dt BETWEEN asg1.effective_start_date AND asg1.effective_end_date
121: FROM pay_element_types_f pet
122: , pay_element_entries_f pee
123: , pay_run_results prr
124: , pay_run_result_values prrv
125: , pay_input_values_f piv
126: WHERE pet.element_type_id = pee.element_type_id
127: AND pet.element_type_id = prr.element_type_id
128: AND pee.element_entry_id = prr.element_entry_id
129: AND prr.run_result_id = prrv.run_result_id
203: /* Cursor to get the initial absence start date for sickness */
204: CURSOR csr_get_intial_sick_abs_st_dt(p_abs_attn_id IN VARCHAR2 ) IS
205: SELECT nvl(peef2.effective_start_date,peef1.effective_start_date)
206: FROM pay_element_entry_values_f peevf
207: ,pay_input_values_f pivf
208: ,pay_element_entries_f peef1
209: ,pay_element_entries_f peef2
210: WHERE peevf.screen_entry_value = p_abs_attn_id
211: AND pivf.input_value_id = peevf.input_value_id
6660: ,per_all_assignments_f asg2
6661: ,per_all_people_f per
6662: ,pay_element_links_f el
6663: ,pay_element_types_f et
6664: ,pay_input_values_f iv1
6665: ,pay_element_entries_f ee
6666: ,pay_element_entry_values_f eev1
6667: WHERE asg1.assignment_id = p_assignment_id
6668: AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
6688: ORDER BY ee.element_entry_id;
6689: --
6690: TYPE l_record is record (eeid pay_element_entries_f.element_entry_id%TYPE,
6691: eevalue pay_element_entry_values_f.screen_entry_value%TYPE,
6692: eename pay_input_values_f.name%TYPE );
6693: l_rec l_record;
6694: TYPE l_table is table of l_record index by BINARY_INTEGER;
6695: l_tab l_table;
6696:
6954: ,per_all_assignments_f asg2
6955: ,per_all_people_f per
6956: ,pay_element_links_f el
6957: ,pay_element_types_f et
6958: ,pay_input_values_f iv1
6959: ,pay_element_entries_f ee
6960: ,pay_element_entry_values_f eev1
6961: WHERE asg1.assignment_id = p_assignment_id
6962: AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
6982: ORDER BY ee.element_entry_id;
6983: --
6984: TYPE l_record is record (eeid pay_element_entries_f.element_entry_id%TYPE,
6985: eevalue pay_element_entry_values_f.screen_entry_value%TYPE,
6986: eename pay_input_values_f.name%TYPE );
6987: l_rec l_record;
6988: TYPE l_table is table of l_record index by BINARY_INTEGER;
6989: l_tab l_table;
6990:
7008: SELECT MAX(ee.effective_end_date)
7009: INTO l_max_date
7010: FROM pay_element_types_f et
7011: ,pay_element_links_f el
7012: ,pay_input_values_f iv1
7013: ,pay_element_entries_f ee
7014: ,pay_element_entry_values_f eev1
7015: WHERE et.element_name = 'Sickness Unpaid'
7016: AND et.legislation_code = 'NO'
7120: ,per_all_assignments_f asg2
7121: ,per_all_people_f per
7122: ,pay_element_links_f el
7123: ,pay_element_types_f et
7124: ,pay_input_values_f iv1
7125: ,pay_element_entries_f ee
7126: ,pay_element_entry_values_f eev1
7127: WHERE asg1.assignment_id = p_assignment_id
7128: AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
7148: ORDER BY ee.element_entry_id;
7149: --
7150: TYPE l_record is record (eeid pay_element_entries_f.element_entry_id%TYPE,
7151: eevalue pay_element_entry_values_f.screen_entry_value%TYPE,
7152: eename pay_input_values_f.name%TYPE );
7153: l_rec l_record;
7154: TYPE l_table is table of l_record index by BINARY_INTEGER;
7155: l_tab l_table;
7156:
7193: SELECT MAX(ee.effective_end_date)
7194: INTO l_max_date
7195: FROM pay_element_types_f et
7196: ,pay_element_links_f el
7197: ,pay_input_values_f iv1
7198: ,pay_element_entries_f ee
7199: ,pay_element_entry_values_f eev1
7200: WHERE et.element_name = 'Sickness Unpaid'
7201: AND et.legislation_code = 'NO'
7415: WHERE element_entry_id = p_element_entry_id;
7416:
7417: CURSOR csr_get_input_value_id(p_element_type_id NUMBER) IS
7418: SELECT input_value_id from
7419: pay_input_values_f
7420: WHERE element_type_id = p_element_type_id
7421: AND NAME = 'CREATOR_ID';
7422:
7423: CURSOR csr_get_screen_value(p_element_entry_id NUMBER
7513: WHERE element_entry_id = p_element_entry_id;
7514:
7515: CURSOR csr_get_input_value_id(p_element_type_id NUMBER) IS
7516: SELECT input_value_id from
7517: pay_input_values_f
7518: WHERE element_type_id = p_element_type_id
7519: AND NAME = 'CREATOR_ID';
7520:
7521: CURSOR csr_get_screen_value(p_element_entry_id NUMBER
7589: /* Cursor to get the absence category based on the given element entry id*/
7590: CURSOR csr_abs_category (p_element_entry_id number) is
7591: SELECT screen_entry_value
7592: FROM pay_element_entries_f peef,
7593: pay_input_values_f pivf,
7594: pay_element_entry_values_f peevf
7595: WHERE peef.element_entry_id = p_element_entry_id
7596: AND pivf.element_type_id = peef.element_type_id
7597: AND pivf.name = 'Absence Category'
7845:
7846: CURSOR csr_get_intial_abs_st_date(p_abs_attn_id IN VARCHAR2 ) IS
7847: SELECT nvl(peef2.effective_start_date,peef1.effective_start_date)
7848: FROM pay_element_entry_values_f peevf
7849: ,pay_input_values_f pivf
7850: ,pay_element_entries_f peef1
7851: ,pay_element_entries_f peef2
7852: WHERE peevf.screen_entry_value = p_abs_attn_id
7853: AND pivf.input_value_id = peevf.input_value_id