eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PQH_PQIPED6_XMLP_PKG

Source


1 PACKAGE BODY PQH_PQIPED6_XMLP_PKG AS
2 /* $Header: PQIPED6B.pls 120.4 2008/04/17 06:29:40 amakrish noship $ */
3 
4 function line1Formula return Number is
5 temp_num number := 19;
6 
7 begin
8 
9    if line_num < 50 then
10      temp_num := line_num;
11      line_num:= line_num + 1;
12    elsif tmp_var between 49 and 54 then
13      temp_num := tmp_var;
14      tmp_var := tmp_var + 1;
15    elsif tmp_var1 between 54 and 61 then
16      temp_num := tmp_var1;
17      tmp_var1 := tmp_var1 + 1;
18    else
19      temp_num := tmp_var2;
20      tmp_var2 := tmp_var2 + 1;
21    end if;
22 
23   if line_num = 26 then
24      line_num := 27;
25   elsif line_num = 34 then
26      line_num := 35;
27   elsif line_num = 42 then
28      line_num := 43;
29 
30   end if;
31 
32 
33 
34   return temp_num;
35 end;
36 
37 function CF_GroupTotTitleFormula(JobCode in number) return Char is
38  l_total_title	VARCHAR2(200)	:= '';
39  l_job_code	Number	:= JobCode;
40 begin
41   IF 	l_job_code =  '5' THEN
42 	l_total_title	:= 'Total Exec/Administrative, and Managerial (sum of lines 19-25)';
43   ELSIF l_job_code 	= '6' THEN
44 	l_total_title	:= 'Total Other Administrative (sum of lines 27-33)';
45   ELSIF l_job_code 	= '7' THEN
46 	l_total_title	:= 'Total Other Professionals (sum of lines 35-41)';
47   ELSIF l_job_code 	= '8' THEN
48 	l_total_title	:= 'Total Technical and Paraprofessionals (sum of lines 43-47)';
49   ELSIF l_job_code 	= '9' THEN
50 	l_total_title	:= 'Total Clerical and Secretarial (sum of lines 49-53)';
51   ELSIF l_job_code 	= '10' THEN
52 	l_total_title	:= 'Total Skilled Crafts (sum of lines 55-59)';
53   ELSIF l_job_code 	= '11' THEN
54 	l_total_title	:= 'Total Service/Maintenance (sum of lines 61-65)';
55   END IF;
56 
57   return l_total_title;
58 end;
59 
60 function cf_group_linenoformula(JobCode in number) return number is
61 temp_num number;
62 begin
63    if (JobCode = 5) then
64          temp_num := 26;
65    elsif (JobCode = 6) then
66          temp_num := 34;
67    elsif (JobCode = 7) then
68          temp_num := 42;
69    elsif (JobCode = 8) then
70          temp_num := 48;
71    elsif (JobCode = 9) then
72          temp_num := 54;
73    elsif (JobCode = 10) then
74          temp_num := 60;
75    elsif (JobCode = 11) then
76          temp_num := 66;
77    end if;
78 
79   return temp_num;
80 
81 end;
82 
83 function BeforeReport return boolean is
84 l_fr	varchar2(2000);
85 l_ft	varchar2(2000);
86 l_pr	varchar2(2000);
87 l_pt	varchar2(2000);
88 l_query_text VARCHAR2(2000);
89                                   line VARCHAR2(32767);
90                                   sc VARCHAR2(32767);
91                                   salary_range VARCHAR2(32767);
92                                   l_nr_men NUMBER(10) := 0;
93                                   l_nr_wmen NUMBER(10) := 0;
94                                   l_bnh_men NUMBER(10) := 0;
95                                   l_bnh_wmen NUMBER(10) := 0;
96                                   l_amai_men NUMBER(10) := 0;
97                                   l_amai_wmen NUMBER(10) := 0;
98                                   l_ap_men NUMBER(10) := 0;
99                                   l_ap_wmen NUMBER(10) := 0;
100                                   l_h_men NUMBER(10) := 0;
101                                   l_h_wmen NUMBER(10) := 0;
102                                   l_wnh_men NUMBER(10) := 0;
103                                   l_wnh_wmen NUMBER(10) := 0;
104                                   l_ur_men NUMBER(10) := 0;
105                                   l_ur_wmen NUMBER(10) := 0;
106                                   l_tot_men NUMBER(10) := 0;
107                                   l_tot_wmen NUMBER(10) := 0;
108                                   CURSOR get_line_counts IS
109                                   SELECT 1 line,
110                                     hl.lookup_code job_category_name,
111 pqh_salary_class_intervals_pkg.get_job_sal_interval(pqh_employment_category.identify_empl_category(ass.employment_category,
112 cp_fr,   cp_ft,   cp_pr,   cp_pt),   hl.lookup_code,   nvl(ppp.proposed_salary_n,   0) * ppb.pay_annualization_factor) salary_range,
113 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   '1',   decode(peo.sex,   'M',   1,   NULL),   NULL)) nrmen,
114 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   '1',   decode(peo.sex,   'F',   1,   NULL),   NULL)) nrwmen,
115 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '2',   decode(peo.sex,   'M',   1,   NULL),   NULL)))) bnhmen,
116 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '2',   decode(peo.sex,   'F',   1,   NULL),   NULL)))) bnhwmen,
117 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '6',   decode(peo.sex,   'M',   1,   NULL),   NULL)))) am_almen,
118 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '6',   decode(peo.sex,   'F',   1,   NULL),   NULL)))) am_alwmen,
119 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '4',   decode(peo.sex,   'M',   1,   NULL),   '5',   decode(peo.sex,   'M',   1,   NULL),   NULL)))) a_pmen,
120 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '4',   decode(peo.sex,   'F',   1,   NULL),   '5',   decode(peo.sex,   'F',   1,   NULL),   NULL)))) a_pwmen,
121 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '3',   decode(peo.sex,   'M',   1,   NULL),   '9',   decode(peo.sex,   'M',   1,   NULL),   NULL)))) hmen,
122 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '3',   decode(peo.sex,   'F',   1,   NULL),   '9',   decode(peo.sex,   'F',   1,   NULL),   NULL)))) hwmen,
123 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '1',   decode(peo.sex,   'M',   1,   NULL),   NULL)))) wnhmen,
124 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   '1',   decode(peo.sex,   'F',   1,   NULL),   NULL)))) wnhwmen,
125 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   NULL,   decode(peo.sex,   'M',   1,   NULL),   NULL)),   NULL)) urmen,
126 COUNT(decode(pqh_nr_alien_pkg.get_count_nr_alien(peo.person_id,   p_report_date),   NULL,  (decode(peo.per_information1,   NULL,   decode(peo.sex,   'F',   1,   NULL),   NULL)),   NULL)) urwmen
127 FROM per_all_people_f peo,
128   per_all_assignments_f ass,
129   per_assignment_status_types ast,
130   per_jobs job,
131   per_pay_proposals ppp,
132   per_pay_bases ppb,
133   hr_lookups hl
134 WHERE peo.person_id = ass.person_id
135  AND peo.current_employee_flag = 'Y'
136  AND hl.lookup_code = job.job_information8
137  AND job.job_information8 NOT IN('1',   '2',   '3',   '4')
138  AND pqh_employment_category.identify_empl_category(ass.employment_category,   cp_fr,   cp_ft,   cp_pr,   cp_pt) IN('FR')
139  AND ppp.change_date =
140   (SELECT MAX(change_date)
141    FROM per_pay_proposals PRO
142   WHERE ppp.assignment_id = PRO.ASSIGNMENT_ID
143   AND PRO.CHANGE_DATE <= P_REPORT_DATE
144   AND PRO.APPROVED = 'Y' )
145   AND ass.pay_basis_id = ppb.pay_basis_id
146    AND ass.assignment_id = ppp.assignment_id
147    AND nvl(ppp.proposed_salary_n,    0) * ppb.pay_annualization_factor > 0
148    AND hl.lookup_type = 'US_IPEDS_JOB_CATEGORIES'
149    AND job.job_information_category = 'US'
150    AND p_report_date BETWEEN peo.effective_start_date
151    AND peo.effective_end_date
152    AND p_report_date BETWEEN ass.effective_start_date
153    AND ass.effective_end_date
154    AND ass.primary_flag = 'Y'
155    AND ass.assignment_status_type_id = ast.assignment_status_type_id
156    AND ast.per_system_status <> 'TERM_ASSIGN'
157    AND ass.job_id = job.job_id
158    AND ass.assignment_type = 'E'
159    AND ass.organization_id IN
160     (SELECT org.organization_id
161      FROM hr_all_organization_units org
162      WHERE business_group_id = p_business_group_id
163      AND pqh_inst_type_pkg.get_inst_type(org.organization_id) = 'NON-MED')
164   GROUP BY
165   pqh_salary_class_intervals_pkg.get_job_sal_interval(pqh_employment_category.identify_empl_category(ass.employment_category,
166   cp_fr,    cp_ft,    cp_pr,    cp_pt),    hl.lookup_code,    nvl(ppp.proposed_salary_n,    0) * ppb.pay_annualization_factor),
167      hl.lookup_code;
168 CURSOR get_line_tmraces_counts(c_salary_range IN VARCHAR2,    c_lookup_code IN NUMBER) IS
169 SELECT COUNT(decode(pei.pei_information5,    '2',    decode(peo.sex,    'M',    1,    NULL),    NULL)) bnhmen,
170 COUNT(decode(pei.pei_information5,    '2',    decode(peo.sex,    'F',    1,    NULL),    NULL)) bnhwmen,
171 COUNT(decode(pei.pei_information5,    '6',    decode(peo.sex,    'M',    1,    NULL),    NULL)) am_almen,
172 COUNT(decode(pei.pei_information5,    '6',    decode(peo.sex,    'F',    1,    NULL),    NULL)) am_alwmen,
173 COUNT(decode(pei.pei_information5,    '4',    decode(peo.sex,    'M',    1,    NULL),    '5',    decode(peo.sex,    'M',    1,    NULL),    NULL)) a_pmen,
174 COUNT(decode(pei.pei_information5,    '4',    decode(peo.sex,    'F',    1,    NULL),    '5',    decode(peo.sex,    'F',    1,    NULL),    NULL)) a_pwmen,
175 COUNT(decode(pei.pei_information5,    '3',    decode(peo.sex,    'M',    1,    NULL),    '9',    decode(peo.sex,    'M',    1,    NULL),    NULL)) hmen,
176 COUNT(decode(pei.pei_information5,    '3',    decode(peo.sex,    'F',    1,    NULL),    '9',    decode(peo.sex,    'F',    1,    NULL),    NULL)) hwmen,
177 COUNT(decode(pei.pei_information5,    '1',    decode(peo.sex,    'M',    1,    NULL),    NULL)) wnhmen,
178 COUNT(decode(pei.pei_information5,    '1',    decode(peo.sex,    'F',    1,    NULL),    NULL)) wnhwmen,
179 COUNT(decode(pei.pei_information5,    NULL,    decode(peo.sex,    'M',    1,    NULL),    NULL)) urmen,
180 COUNT(decode(pei.pei_information5,    NULL,    decode(peo.sex,    'F',    1,    NULL),    NULL)) urwmen
181   FROM per_all_people_f peo,
182     per_all_assignments_f ass,
183     per_assignment_status_types ast,
184     per_jobs job,
185     per_pay_proposals ppp,
186     per_pay_bases ppb,
187     hr_lookups hl,
188     per_people_extra_info pei
189   WHERE peo.person_id = ass.person_id
190   AND peo.current_employee_flag = 'Y'
191   AND peo.per_information1 = '13'
192   AND peo.person_id = pei.person_id(+)
193   AND(pei.information_type = 'PER_US_ADDL_ETHNIC_CAT' OR(pei.information_type <> 'PER_US_ADDL_ETHNIC_CAT'
194   AND NOT EXISTS
195    (SELECT 1
196     FROM per_people_extra_info pei2
197     WHERE pei2.information_type = 'PER_US_ADDL_ETHNIC_CAT'
198     AND pei2.person_id = pei.person_id)
199     AND pei.person_extra_info_id =
200      (SELECT MAX(pei1.person_extra_info_id)
201       FROM per_people_extra_info pei1
202       WHERE pei1.person_id = pei.person_id))
203    OR(NOT EXISTS
204      (SELECT person_extra_info_id
205       FROM per_people_extra_info pei3
206       WHERE pei3.person_id = pei.person_id))
207    )
208  AND hl.lookup_code = job.job_information8
209   AND job.job_information8 NOT IN('1',    '2',    '3',    '4')
210   AND pqh_employment_category.identify_empl_category(ass.employment_category,    cp_fr,    cp_ft,    cp_pr,    cp_pt) IN('FR')
211   AND ppp.change_date =
212    (SELECT MAX(change_date)
213     FROM per_pay_proposals PRO
214    WHERE ppp.assignment_id = PRO.ASSIGNMENT_ID
215    AND PRO.CHANGE_DATE <= P_REPORT_DATE
216    AND PRO.APPROVED = 'Y' )
217    AND ass.pay_basis_id = ppb.pay_basis_id
218     AND ass.assignment_id = ppp.assignment_id
219     AND nvl(ppp.proposed_salary_n,    0) * ppb.pay_annualization_factor > 0
220     AND hl.lookup_type = 'US_IPEDS_JOB_CATEGORIES'
221     AND job.job_information_category = 'US'
222     AND p_report_date BETWEEN peo.effective_start_date
223     AND peo.effective_end_date
224     AND p_report_date BETWEEN ass.effective_start_date
225     AND ass.effective_end_date
226     AND ass.primary_flag = 'Y'
227     AND ass.assignment_status_type_id = ast.assignment_status_type_id
228     AND ast.per_system_status <> 'TERM_ASSIGN'
229     AND ass.job_id = job.job_id
230     AND ass.assignment_type = 'E'
231     AND ass.organization_id IN
232      (SELECT org.organization_id
233      FROM hr_all_organization_units org
234      WHERE business_group_id = p_business_group_id
235      AND pqh_inst_type_pkg.get_inst_type(org.organization_id) = 'NON-MED')
236   AND pqh_salary_class_intervals_pkg.get_job_sal_interval(pqh_employment_category.identify_empl_category(ass.employment_category,    cp_fr,    cp_ft,    cp_pr,    cp_pt),    hl.lookup_code,
237 	 nvl(ppp.proposed_salary_n,    0) * ppb.pay_annualization_factor) = c_salary_range
238    AND hl.lookup_code = c_lookup_code;
239 
240 begin
241    --hr_standard.event('BEFORE REPORT');
242    LP_REPORT_DATE := to_Char(P_REPORT_DATE,'DD-MON-YYYY');
243 
244    pqh_employment_category.fetch_empl_categories(p_business_group_id,l_fr,l_ft,l_pr,l_pt);
245 
246    	cp_fr  := l_fr;
247 	cp_ft	:= l_ft;
248 	cp_pr	:= l_pr;
249 	cp_pt	:= l_pt;
250 FOR i IN get_line_counts
251  LOOP
252    line := i.line;
253    sc := i.job_category_name;
254    salary_range := i.salary_range;
255    l_nr_men := i.nrmen;
256    l_nr_wmen := i.nrwmen;
257    l_bnh_men := i.bnhmen;
258    l_bnh_wmen := i.bnhwmen;
259    l_amai_men := i.am_almen;
260    l_amai_wmen := i.am_alwmen;
261    l_ap_men := i.a_pmen;
262    l_ap_wmen := i.a_pwmen;
263    l_h_men := i.hmen;
264    l_h_wmen := i.hwmen;
265    l_wnh_men := i.wnhmen;
266    l_wnh_wmen := i.wnhwmen;
267    l_ur_men := i.urmen;
268    l_ur_wmen := i.urwmen;
269    l_tot_men := l_nr_men + l_bnh_men + l_amai_men + l_ap_men + l_h_men + l_wnh_men + l_ur_men;
270    l_tot_wmen := l_nr_wmen + l_bnh_wmen + l_amai_wmen + l_ap_wmen + l_h_wmen + l_wnh_wmen + l_ur_wmen;
271    FOR j IN get_line_tmraces_counts(salary_range,sc)
272     LOOP
273      l_bnh_men := l_bnh_men + j.bnhmen;
274      l_bnh_wmen := l_bnh_wmen + j.bnhwmen;
275      l_amai_men := l_amai_men + j.am_almen;
276      l_amai_wmen := l_amai_wmen + j.am_alwmen;
277      l_ap_men := l_ap_men + j.a_pmen;
278      l_ap_wmen := l_ap_wmen + j.a_pwmen;
279      l_h_men := l_h_men + j.hmen;
280      l_h_wmen := l_h_wmen + j.hwmen;
281      l_wnh_men := l_wnh_men + j.wnhmen;
282      l_wnh_wmen := l_wnh_wmen + j.wnhwmen;
283      l_ur_men := l_ur_men + j.urmen;
284      l_ur_wmen := l_ur_wmen + j.urwmen;
285      l_tot_men := l_tot_men + j.bnhmen + j.am_almen + j.a_pmen + j.hmen + j.wnhmen + j.urmen;
286      l_tot_wmen := l_tot_wmen + j.bnhwmen + j.am_alwmen + j.a_pwmen + j.hwmen + j.wnhwmen + j.urwmen;
287    END LOOP;
288 
289    INSERT
290     INTO pay_us_rpt_totals(session_id,    attribute1,    attribute2,    value1,    value2,    value3,    value4,
291 	   value5,    value6,    value7,    value8,    value9,    value10,    value11,    value12,    value13,    value14,    value15,    value16,    value17,    value18)
292     VALUES(userenv('sessionid'),    'IPED6',    salary_range,    line,    sc,
293 	   l_nr_men,    l_nr_wmen,    l_bnh_men,    l_bnh_wmen,    l_amai_men,    l_amai_wmen,    l_ap_men,    l_ap_wmen,    l_h_men,    l_h_wmen,    l_wnh_men,
294 	   l_wnh_wmen,    l_ur_men,    l_ur_wmen,    l_tot_men,    l_tot_wmen);
295    COMMIT;
296  END LOOP;
297 
298    return true;
299 end;
300 
301 function AfterReport return boolean is
302 begin
303   --hr_standard.event('AFTER REPORT');
304 EXECUTE IMMEDIATE 'DELETE FROM pay_us_rpt_totals
305                     WHERE attribute1 = ''IPED6''';
306   return (TRUE);
307 end;
308 
309 --Functions to refer Oracle report placeholders--
310 
311  Function line_num_p return number is
312 	Begin
313 	 return line_num;
314 	 END;
315  Function CP_FR_p return varchar2 is
316 	Begin
317 	 return CP_FR;
318 	 END;
319  Function CP_FT_p return varchar2 is
320 	Begin
321 	 return CP_FT;
322 	 END;
323  Function CP_PR_p return varchar2 is
324 	Begin
325 	 return CP_PR;
326 	 END;
327  Function CP_PT_p return varchar2 is
328 	Begin
329 	 return CP_PT;
330 	 END;
331  Function CP_lastLineNo_p return number is
332 	Begin
333 	 return CP_lastLineNo;
334 	 END;
335  Function CP_ReportTotTitle_p return varchar2 is
336 	Begin
337 	 return CP_ReportTotTitle;
338 	 END;
339  Function tmp_var_p return number is
340 	Begin
341 	 return tmp_var;
342 	 END;
343  Function tmp_var1_p return number is
344 	Begin
345 	 return tmp_var1;
346 	 END;
347  Function tmp_var2_p return number is
348 	Begin
349 	 return tmp_var2;
350 	 END;
351 END PQH_PQIPED6_XMLP_PKG ;