[Home] [Help]
9: FUNCTION get_current_freq(p_assignment_id IN NUMBER) RETURN NUMBER IS
10: --
11: CURSOR get_freq IS
12: SELECT ptpt.number_per_fiscal_year
13: FROM per_all_assignments_f paaf, pay_all_payrolls_f pap, per_time_period_types ptpt, fnd_sessions fs
14: WHERE fs.session_id = userenv('sessionid')
15: AND paaf.assignment_id = p_assignment_id
16: AND fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_Date
17: AND pap.payroll_id = paaf.payroll_id
36: FUNCTION get_ni_process_type(p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
37:
38: CURSOR get_value IS
39: SELECT nvl(min(peev.screen_entry_value), 'NP')
40: FROM fnd_sessions fs,
41: pay_element_types_f pet,
42: pay_input_values_f piv,
43: pay_element_entries_f peef,
44: pay_element_entry_values_f peev
74:
75: CURSOR get_asg_tax_ref IS
76: SELECT scl.segment1
77: FROM hr_soft_coding_keyflex scl,
78: fnd_sessions fs,
79: pay_payrolls_f ppf,
80: per_all_assignments_f paaf
81: WHERE paaf.assignment_id = p_assignment_id
82: AND fs.session_id = userenv('sessionid')
88: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
89: --
90: CURSOR get_input_value_id(p_ele_name IN VARCHAR2) IS
91: SELECT piv.input_value_id
92: FROM fnd_sessions fs,
93: pay_element_types_f pet,
94: pay_input_values_f piv
95: WHERE fs.session_id = userenv('sessionid')
96: AND pet.element_name = p_ele_name
104: AND fs.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
105: --
106: CURSOR get_main_count(p_asg_tax_ref IN VARCHAR2) IS
107: SELECT count(*) cnt
108: FROM fnd_sessions fs,
109: per_all_assignments_f paaf1,
110: per_all_assignments_f paaf2,
111: pay_all_payrolls_f ppf,
112: hr_soft_coding_keyflex scl,
131: l_count NUMBER := 0;
132: --
133: CURSOR chk_prim_asg(p_asg_tax_ref IN VARCHAR2) IS
134: SELECT 1 cnt
135: FROM fnd_sessions fs,
136: per_all_assignments_f paaf1,
137: per_all_assignments_f paaf2,
138: pay_all_payrolls_f ppf,
139: hr_soft_coding_keyflex scl,
199:
200: CURSOR get_asg_tax_ref IS
201: SELECT scl.segment1, ppf.payroll_id
202: FROM hr_soft_coding_keyflex scl,
203: fnd_sessions fs,
204: pay_payrolls_f ppf,
205: per_all_assignments_f paaf
206: WHERE paaf.assignment_id = p_assignment_id
207: AND fs.session_id = userenv('sessionid')
215: l_asg_period_start_date per_time_periods.start_date%TYPE;
216: --
217: CURSOR get_asg_period_start_date IS
218: SELECT ptp.start_date
219: FROM per_time_periods ptp, fnd_sessions fs
220: WHERE fs.session_id = userenv('sessionid')
221: AND ptp.payroll_id = l_asg_payroll_id
222: AND fs.effective_date = ptp.regular_payment_date;
223: --
222: AND fs.effective_date = ptp.regular_payment_date;
223: --
224: CURSOR get_main_payroll_id IS
225: SELECT ppf.payroll_id
226: FROM fnd_sessions fs,
227: per_all_assignments_f paaf1,
228: per_all_assignments_f paaf2,
229: pay_all_payrolls_f ppf,
230: hr_soft_coding_keyflex scl,
247: AND nvl(SCREEN_ENTRY_VALUE, 'N') = 'Y';
248: --
249: CURSOR get_prim_payroll_id IS
250: SELECT ppf.payroll_id
251: FROM fnd_sessions fs,
252: per_all_assignments_f paaf1,
253: per_all_assignments_f paaf2,
254: pay_all_payrolls_f ppf,
255: hr_soft_coding_keyflex scl,
353:
354: CURSOR get_asg_tax_ref IS
355: SELECT scl.segment1
356: FROM hr_soft_coding_keyflex scl,
357: fnd_sessions fs,
358: pay_payrolls_f ppf,
359: per_all_assignments_f paaf
360: WHERE paaf.assignment_id = p_assignment_id
361: AND fs.session_id = userenv('sessionid')
367: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
368: --
369: CURSOR get_main_payroll_id IS
370: SELECT ppf.payroll_id
371: FROM fnd_sessions fs,
372: per_all_assignments_f paaf1,
373: per_all_assignments_f paaf2,
374: pay_all_payrolls_f ppf,
375: hr_soft_coding_keyflex scl,
392: AND nvl(SCREEN_ENTRY_VALUE, 'N') = 'Y';
393: --
394: CURSOR get_prim_payroll_id IS
395: SELECT ppf.payroll_id
396: FROM fnd_sessions fs,
397: per_all_assignments_f paaf1,
398: per_all_assignments_f paaf2,
399: pay_all_payrolls_f ppf,
400: hr_soft_coding_keyflex scl,
421: l_count NUMBER := 0;
422: --
423: CURSOR get_freq IS
424: SELECT number_per_fiscal_year
425: FROM per_time_periods ptp, per_time_period_types ptpt, fnd_sessions fs
426: WHERE fs.session_id = userenv('sessionid')
427: AND fs.effective_date BETWEEN ptp.start_date AND ptp.end_Date
428: AND ptp.payroll_id = l_payroll_id
429: AND ptp.period_type = ptpt.period_type;
498:
499: CURSOR get_asg_tax_ref IS
500: SELECT scl.segment1
501: FROM hr_soft_coding_keyflex scl,
502: fnd_sessions fs,
503: pay_payrolls_f ppf,
504: per_all_assignments_f paaf
505: WHERE paaf.assignment_id = p_assignment_id
506: AND fs.session_id = userenv('sessionid')
512: l_asg_tax_ref hr_soft_coding_keyflex.segment1%TYPE;
513: --
514: CURSOR get_input_value_id(p_ele_name IN VARCHAR2, p_iv_name IN VARCHAR2) IS
515: SELECT piv.input_value_id
516: FROM fnd_sessions fs,
517: pay_element_types_f pet,
518: pay_input_values_f piv
519: WHERE fs.session_id = userenv('sessionid')
520: AND pet.element_name = p_ele_name
531: l_eas_ntpp_iv_id NUMBER;
532: --
533: CURSOR get_main_entry_id IS
534: SELECT peef.element_entry_id
535: FROM fnd_sessions fs,
536: per_all_assignments_f paaf1,
537: per_all_assignments_f paaf2,
538: pay_all_payrolls_f ppf,
539: hr_soft_coding_keyflex scl,
557: AND peef.target_entry_id IS NULL;
558: --
559: CURSOR chk_prim_entry_id IS
560: SELECT peef.element_entry_id
561: FROM fnd_sessions fs,
562: per_all_assignments_f paaf1,
563: per_all_assignments_f paaf2,
564: pay_all_payrolls_f ppf,
565: hr_soft_coding_keyflex scl,
585: l_entry_id NUMBER;
586: --
587: CURSOR get_value IS
588: SELECT peev.screen_entry_value
589: FROM fnd_sessions fs, pay_element_entry_values_f peev
590: WHERE fs.session_id = userenv('sessionid')
591: AND fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
592: AND peev.element_entry_id = l_entry_id
593: AND peev.input_value_id IN (l_eas_iv_id, l_eas_ntpp_iv_id);