7: p_xml OUT NOCOPY CLOB
8: ) IS
9: --Cursors needed for report
10: CURSOR csr_all_legal_employer (
11: csr_v_pa_id pay_action_information.action_context_id%TYPE,
12: p_curr_prev_std_hour VARCHAR2,
13: p_leagal_employer_id VARCHAR2
14: ) IS
15: SELECT action_information3 legal_employer_id, action_information4 legal_employer_name,
22: hours_per_year,
23: fnd_date.canonical_to_date (action_information10) start_date,
24: fnd_date.canonical_to_date (action_information11)
25: end_date
26: FROM pay_action_information
27: WHERE action_context_type = 'PA'
28: AND action_context_id = csr_v_pa_id
29: AND action_information_category = 'EMEA REPORT INFORMATION'
30: AND action_information1 = 'PYSESLSA'
31: AND action_information3 = nvl (p_leagal_employer_id, action_information3)
32: AND action_information2 = p_curr_prev_std_hour;
33:
34: CURSOR csr_all_legal_employer_l (
35: csr_v_pa_id pay_action_information.action_context_id%TYPE,
36: p_curr_prev_std_hour VARCHAR2,
37: p_leagal_employer_id VARCHAR2
38: ) IS
39: SELECT action_information3 legal_employer_id, action_information4 legal_employer_name,
46: hours_per_year,
47: fnd_date.canonical_to_date (action_information10) start_date,
48: fnd_date.canonical_to_date (action_information11)
49: end_date
50: FROM pay_action_information
51: WHERE action_context_type = 'PA'
52: AND action_context_id = csr_v_pa_id
53: AND action_information_category = 'EMEA REPORT INFORMATION'
54: AND action_information1 = 'PYSESLSA'
57:
58: l_all_legal_employer csr_all_legal_employer_l%ROWTYPE;
59:
60: CURSOR csr_get_report_data (
61: csr_v_pa_id pay_action_information.action_context_id%TYPE,
62: p_curr_prev_data VARCHAR2,
63: p_leagal_employer_id VARCHAR2
64: ) IS
65: SELECT fnd_number.canonical_to_number (action_information6) men_lower_age_count,
99: fnd_number.canonical_to_number (action_information28)
100: women_m_age_l_sick_leaves,
101: fnd_number.canonical_to_number (action_information29)
102: women_u_age_l_sick_leaves
103: FROM pay_action_information
104: WHERE action_context_type = 'PA'
105: AND action_context_id = csr_v_pa_id
106: AND action_information_category = 'EMEA REPORT INFORMATION'
107: AND action_information1 = 'PYSESLSA'
112: l_csr_get_prev_report_data csr_get_report_data%ROWTYPE;
113:
114: /*
115: CURSOR csr_get_report_stat (
116: csr_v_pa_id pay_action_information.action_context_id%TYPE,
117: p_curr_prev_stat VARCHAR2,
118: p_leagal_employer_id VARCHAR2
119: ) IS
120: SELECT fnd_number.canonical_to_number (action_information6) leave_to_work_hours,
126: fnd_number.canonical_to_number (action_information10) leave_to_work_hour_lower,
127: fnd_number.canonical_to_number (action_information11)
128: leave_to_work_hour_middle,
129: fnd_number.canonical_to_number (action_information12) leave_to_work_hour_upper
130: FROM pay_action_information
131: WHERE action_context_type = 'PA'
132: AND action_context_id = csr_v_pa_id
133: AND action_information_category = 'EMEA REPORT INFORMATION'
134: AND action_information1 = 'PYSESLSA'
134: AND action_information1 = 'PYSESLSA'
135: AND action_information3 = p_leagal_employer_id
136: AND action_information2 = p_curr_prev_stat;*/
137: CURSOR csr_get_report_stat (
138: csr_v_pa_id pay_action_information.action_context_id%TYPE,
139: p_curr_prev_stat VARCHAR2,
140: p_leagal_employer_id VARCHAR2
141: ) IS
142: SELECT fnd_number.canonical_to_number (action_information6) leave_to_work_hours,
148: fnd_number.canonical_to_number (action_information10) leave_to_work_hour_lower,
149: fnd_number.canonical_to_number (action_information11)
150: leave_to_work_hour_middle,
151: fnd_number.canonical_to_number (action_information12) leave_to_work_hour_upper
152: FROM pay_action_information
153: WHERE action_context_type = 'PA'
154: AND action_context_id = csr_v_pa_id
155: AND action_information_category = 'EMEA REPORT INFORMATION'
156: AND action_information1 = 'PYSESLSA'
158: AND action_information2 = p_curr_prev_stat;
159:
160: l_csr_get_curr_report_stat csr_get_report_stat%ROWTYPE;
161: l_csr_get_prev_report_stat csr_get_report_stat%ROWTYPE;
162: l_payroll_action_id pay_action_information.action_information1%TYPE;
163: l_counter NUMBER := 0;
164: BEGIN
165: IF p_payroll_action_id IS NULL THEN
166: BEGIN