1 PACKAGE BODY PER_ZA_EE_WORKFORCE_PROFILE AS
2 /* $Header: PERZAEWP.pkb 120.0.12010000.1 2009/12/09 06:18:34 nchinnam noship $ */
3
4 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
5 BEGIN
6 PER_ZA_EMPLOYMENT_EQUITY_PKG.POPULATE_EE_TABLE_EEWF_NEW(P_REPORT_DATE
7 ,P_BUSINESS_GROUP_ID
8 ,P_LEGAL_ENTITY_ID);
9
10 CP_TO_DATE := to_char(P_REPORT_DATE,'DD/MM/YYYY');
11 CP_FROM_DATE := to_char(ADD_MONTHS(P_REPORT_DATE,-12) + 1,'DD/MM/YYYY');
12 CP_SUBMISSION_DATE := to_char(P_SUBMISSION_DATE,'DD/MM/YYYY');
13 RETURN (TRUE);
14 END BEFOREREPORT;
15
16 FUNCTION GET_SETA_CLASSIFICATION(P_LEGAL_ENTITY_ID IN NUMBER,P_BUSINESS_GROUP_ID IN NUMBER) RETURN CHAR IS
17 CURSOR SETA_CLASS(P_ORG_ID IN NUMBER) IS
18 SELECT ORG_INFORMATION4 SETA_CLASSIFICATION
19 FROM HR_ORGANIZATION_INFORMATION HOI
20 WHERE HOI.ORGANIZATION_ID = P_ORG_ID
21 AND HOI.ORG_INFORMATION_CONTEXT = 'ZA_NQF_SETA_INFO';
22 BEGIN
23 FOR seta_rec IN SETA_CLASS(P_LEGAL_ENTITY_ID) LOOP
24 RETURN (SETA_REC.SETA_CLASSIFICATION);
25 END LOOP;
26 FOR seta_rec1 IN SETA_CLASS(P_BUSINESS_GROUP_ID) LOOP
27 RETURN (SETA_REC1.SETA_CLASSIFICATION);
28 END LOOP;
29 RETURN (NULL);
30 END GET_SETA_CLASSIFICATION;
31
32 FUNCTION CF_EQ2_ALL_TOTALFORMULA(REPORT_ID IN VARCHAR2,ALL_TOTAL IN NUMBER) RETURN NUMBER IS
33 BEGIN
34 IF REPORT_ID = 'EQ2' THEN
35 RETURN (ALL_TOTAL);
36 END IF;
37 RETURN (0);
38 END CF_EQ2_ALL_TOTALFORMULA;
39
40
41 FUNCTION CF_PAGE_NUMBERSFORMULA(REPORT_ID IN VARCHAR2) RETURN VARCHAR2 IS
42 V_PAGE_NO VARCHAR2(5);
43 BEGIN
44 IF REPORT_ID = 'EQ2' THEN
45 V_PAGE_NO := '3';
46 ELSIF REPORT_ID = 'EQ3' THEN
47 V_PAGE_NO := '4';
48 ELSIF REPORT_ID = 'EQ5' THEN
49 V_PAGE_NO := '5';
50 ELSIF REPORT_ID = 'EQ7' THEN
51 V_PAGE_NO := '6';
52 END IF;
53 RETURN (V_PAGE_NO);
54 END CF_PAGE_NUMBERSFORMULA;
55
56 FUNCTION CF_HEADINGFORMULA(REPORT_ID IN VARCHAR2) RETURN VARCHAR2 IS
57 BEGIN
58 IF REPORT_ID = 'EQ2' THEN
59 RETURN ('SECTION B: WORKFORCE PROFILE AND CORE & SUPPORT FUNCTIONS');
60 END IF;
61 IF REPORT_ID = 'EQ5' THEN
62 RETURN ('SECTION C: WORKFORCE MOVEMENT');
63 END IF;
64 RETURN NULL;
65 END CF_HEADINGFORMULA;
66
67 FUNCTION CF_MAIN_TABLE_NOFORMULA(REPORT_ID IN VARCHAR2) RETURN CHAR IS
68 BEGIN
69 IF (REPORT_ID = 'EQ2') THEN
70 RETURN ('1.');
71 END IF;
72 IF (REPORT_ID = 'EQ3') THEN
73 RETURN ('2.');
74 END IF;
75 IF (REPORT_ID = 'EQ5') THEN
76 RETURN ('3.');
77 END IF;
78 IF (REPORT_ID = 'EQ6') THEN
79 RETURN ('4.');
80 END IF;
81 IF (REPORT_ID = 'EQ7') THEN
82 RETURN ('5.');
83 END IF;
84 RETURN (NULL);
85 END CF_MAIN_TABLE_NOFORMULA;
86
87 FUNCTION CF_SUB_HEADINGFORMULA(REPORT_ID IN VARCHAR2) RETURN CHAR IS
88 BEGIN
89 IF REPORT_ID = 'EQ2' THEN
90 RETURN ('WORKFORCE PROFILE');
91 END IF;
92 IF REPORT_ID = 'EQ3' THEN
93 RETURN ('Core Operation Functions and Support Functions by Occupational level');
94 END IF;
95 IF REPORT_ID = 'EQ5' THEN
96 RETURN ('Recruitment');
97 END IF;
98 IF REPORT_ID = 'EQ6' THEN
99 RETURN ('Promotion');
100 END IF;
101 IF REPORT_ID = 'EQ7' THEN
102 RETURN ('Termination');
103 END IF;
104 RETURN (NULL);
105 END CF_SUB_HEADINGFORMULA;
106
107 FUNCTION CF_SUB_TABLE_NOFORMULA(REPORT_ID IN VARCHAR2) RETURN CHAR IS
108 BEGIN
109 IF (REPORT_ID = 'EQ2') THEN
110 RETURN ('1.1');
111 END IF;
112 IF (REPORT_ID = 'EQ3') THEN
113 RETURN ('2.1');
114 END IF;
115 IF (REPORT_ID = 'EQ4') THEN
116 RETURN ('2.2');
117 END IF;
118 IF (REPORT_ID = 'EQ5') THEN
119 RETURN ('3.1');
120 END IF;
121 IF (REPORT_ID = 'EQ6') THEN
122 RETURN ('4.1');
123 END IF;
124 IF (REPORT_ID = 'EQ7') THEN
125 RETURN ('5.1');
126 END IF;
127 IF (REPORT_ID = 'EQ8') THEN
128 RETURN ('5.2');
129 END IF;
130 RETURN (NULL);
131 END CF_SUB_TABLE_NOFORMULA;
132
133 FUNCTION CF_TITLEFORMULA(REPORT_ID IN VARCHAR2) RETURN VARCHAR2 IS
134 BEGIN
135 IF REPORT_ID = 'EQ2' THEN
136 RETURN ('Please report the total number of');
137 END IF;
138 IF REPORT_ID = 'EQ3' THEN
139 RETURN ('Please indicate the total number of employees (including people with disabilities), that are involved in');
140 END IF;
141 IF REPORT_ID = 'EQ4' THEN
142 RETURN ('Please indicate the total number of employees (including people with disabilities), that are involved in');
143 END IF;
144 IF REPORT_ID = 'EQ5' THEN
145 RETURN ('Please report the total number of new recruits, including people with disabilities.');
146 END IF;
147 IF REPORT_ID = 'EQ6' THEN
148 RETURN ('Please report the total number of promotions into each occupational level, including people with disabilities.');
149 END IF;
150 IF REPORT_ID = 'EQ7' THEN
151 RETURN ('Please report the total number of terminations in each occupational level, including people with disabilities.');
152 END IF;
153 IF REPORT_ID = 'EQ8' THEN
154 RETURN ('Please report the total number of terminations, including people with disabilities, in each');
155 END IF;
156 RETURN NULL;
157 END CF_TITLEFORMULA;
158
159 FUNCTION CF_TITLE1FORMULA(REPORT_ID IN VARCHAR2) RETURN CHAR IS
160 BEGIN
161 IF (REPORT_ID = 'EQ2') THEN
162 RETURN (' employees');
163 END IF;
164 IF (REPORT_ID = 'EQ3') THEN
165 RETURN (' Core Operation Function');
166 END IF;
167 IF (REPORT_ID = 'EQ4') THEN
168 RETURN (' Support Function');
169 END IF;
170 IF (REPORT_ID = 'EQ8') THEN
171 RETURN (' termination category');
172 END IF;
173 RETURN (NULL);
174 END CF_TITLE1FORMULA;
175
176 FUNCTION CF_TITLE2FORMULA(REPORT_ID IN VARCHAR2) RETURN CHAR IS
177 BEGIN
178 IF (REPORT_ID = 'EQ2') THEN
179 RETURN (' (including employees with disabilities) in each of the following');
180 END IF;
181 IF (REPORT_ID = 'EQ3') THEN
182 RETURN (' positions at each level in your organization only.');
183 END IF;
184 IF (REPORT_ID = 'EQ4') THEN
185 RETURN (' positions at each level in your organization.');
186 END IF;
187 IF (REPORT_ID = 'EQ8') THEN
188 RETURN (' below.');
189 END IF;
190 RETURN (NULL);
191 END CF_TITLE2FORMULA;
192
193 FUNCTION CF_TITLE3FORMULA(REPORT_ID IN VARCHAR2) RETURN CHAR IS
194 BEGIN
195 IF (REPORT_ID = 'EQ2') THEN
196 RETURN (' occupational levels:');
197 END IF;
198 IF (REPORT_ID = 'EQ2') THEN
199 RETURN (' occupational levels:');
200 END IF;
201 RETURN (NULL);
202 END CF_TITLE3FORMULA;
203
204 END PER_ZA_EE_WORKFORCE_PROFILE;