1 PACKAGE BODY HRI_APL_DGNSTC_WBM AS
2 /* $Header: hridgwbm.pkb 120.6 2006/08/23 15:39:06 rlpatil noship $ */
3
4 FUNCTION GET_CTRL_HDCNT_BDGTS
5 RETURN VARCHAR2
6 IS
7 l_sql_stmt VARCHAR2 (32000);
8 BEGIN
9 l_sql_stmt :=
10 '
11 SELECT /*+ ordered */
12 PBG.NAME,
13 PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
14 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE),
15 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
16 CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT1_VALUE
17 WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT2_VALUE
18 WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT3_VALUE
19 END BUDGET_VALUE
20 FROM (Select BUDGET_NAME,
21 BUSINESS_GROUP_ID,
22 BUDGET_ID,
23 BUDGET_UNIT1_ID,
24 BUDGET_UNIT2_ID,
25 BUDGET_UNIT3_ID,
26 BUDGET_START_DATE,
27 BUDGET_END_DATE
28 from PQH_BUDGETS
29 WHERE POSITION_CONTROL_FLAG = ''Y''
30 AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'', ''POSITION'')
31 AND BUDGET_START_DATE <= :p_end_date
32 AND BUDGET_END_DATE >= :p_start_date )PB,
33 (SELECT SHARED_TYPE_ID
34 FROM PER_SHARED_TYPES
35 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
36 AND SYSTEM_TYPE_CD =''HEAD'' ) PST,
37 PQH_BUDGET_VERSIONS PBV,
38 PER_BUSINESS_GROUPS_PERF PBG
39 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
40 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
41 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
42 FROM PQH_BUDGET_VERSIONS
43 WHERE BUDGET_ID = PB.BUDGET_ID )
44 AND PB.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
45 ORDER BY PBG.NAME
46 ';
47 RETURN l_sql_stmt;
48 END GET_CTRL_HDCNT_BDGTS;
49
50
51 FUNCTION GET_NO_CTRL_HDCNT_BDGTS_PRDS
52 RETURN VARCHAR2
53 IS
54 l_sql_stmt VARCHAR2(32000);
55 BEGIN
56 l_sql_stmt :=
57
58 'SELECT PBG.NAME,
59 FND_DATE.DATE_TO_DISPLAYDATE(PRDS.START_DATE),
60 FND_DATE.DATE_TO_DISPLAYDATE(PRDS.END_DATE),
61 NULL COL4,
62 NULL COL5
63 FROM
64 (
65 (SELECT ID,
66 CASE WHEN (LEAD(DATE_FROM, 1) OVER
67 (PARTITION BY ID ORDER BY DATE_FROM)) IS NULL
68 AND DATE_FROM > :p_start_date
69 AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
70 THEN :p_start_date
71 WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
72 THEN :p_start_date
73 END START_DATE,
74 CASE WHEN (LEAD(DATE_FROM, 1) OVER
75 (PARTITION BY ID ORDER BY DATE_FROM)) IS NULL
76 AND DATE_FROM > :p_start_date
77 AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
78 THEN DATE_FROM -1
79 WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
80 THEN DATE_FROM -1
81 END END_DATE
82 FROM
83 ( SELECT PB.BUSINESS_GROUP_ID ID,
84 PB.BUDGET_START_DATE DATE_FROM,
85 PB.BUDGET_END_DATE DATE_TO
86 FROM PQH_BUDGETS PB,
87 (SELECT SHARED_TYPE_ID
88 FROM PER_SHARED_TYPES
89 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
90 AND SYSTEM_TYPE_CD=''HEAD'' ) PST
91 WHERE PB.POSITION_CONTROL_FLAG = ''Y''
92 AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
93 AND PB.BUDGET_START_DATE <= :p_end_date
94 AND PB.BUDGET_END_DATE >= :p_start_date
95 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
96 ORDER BY DATE_FROM ))
97 UNION
98 (SELECT ID,
99 CASE WHEN DATE_TO+1 < NVL((LEAD(DATE_FROM, 1) OVER
100 (PARTITION BY ID ORDER BY DATE_FROM)), :p_end_date)
101 THEN DATE_TO +1
102 WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
103 THEN :p_start_date
104 END START_DATE,
105 CASE WHEN DATE_TO+1 < NVL((LEAD(DATE_FROM, 1) OVER
106 (PARTITION BY ID ORDER BY DATE_FROM)), :p_end_date)
107 THEN NVL((LEAD(DATE_FROM, 1) OVER
108 (PARTITION BY ID ORDER BY DATE_FROM)) - 1, :p_end_date)
109 WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
110 THEN DATE_FROM -1
111 END END_DATE
112 FROM
113 ( SELECT PB.BUSINESS_GROUP_ID ID,
114 PB.BUDGET_START_DATE DATE_FROM,
115 PB.BUDGET_END_DATE DATE_TO
116 FROM PQH_BUDGETS PB,
117 (SELECT SHARED_TYPE_ID
118 FROM PER_SHARED_TYPES
119 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
120 AND SYSTEM_TYPE_CD=''HEAD'' ) PST
121 WHERE PB.POSITION_CONTROL_FLAG = ''Y''
122 AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
123 AND PB.BUDGET_START_DATE <= :p_end_date
124 AND PB.BUDGET_END_DATE >= :p_start_date
125 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
126 ORDER BY DATE_FROM ))
127 )PRDS,
128 PER_BUSINESS_GROUPS PBG
129 WHERE PRDS.START_DATE IS NOT NULL
130 AND PRDS.ID = PBG.BUSINESS_GROUP_ID
131 ORDER BY PBG.NAME';
132 RETURN l_sql_stmt;
133 END GET_NO_CTRL_HDCNT_BDGTS_PRDS;
134
135
136 FUNCTION GET_NO_ORG_HDCNT_BDGTS
137 RETURN VARCHAR2
138 IS
139 l_sql_stmt VARCHAR2 (32000);
140 BEGIN
141 l_sql_stmt :=
142 '
143 SELECT PBG.NAME,
144 HR_GENERAL.DECODE_ORGANIZATION(ORG.ORGANIZATION_ID),
145 NULL COL3,
146 NULL COL4,
147 NULL COL5
148 FROM
149 (
150 SELECT ORGANIZATION_ID,
151 BUSINESS_GROUP_ID ID
152 FROM HR_ALL_ORGANIZATION_UNITS
153 WHERE DATE_FROM <= :p_end_date
154 AND NVL(DATE_TO,hr_general.end_of_time) >= :p_start_date
155 AND (ORGANIZATION_ID,BUSINESS_GROUP_ID) NOT IN
156 (
157 SELECT /*+ ordered*/
158 PBD.ORGANIZATION_ID,
159 PB.BUSINESS_GROUP_ID
160 FROM (Select BUSINESS_GROUP_ID,
161 BUDGET_ID,
162 BUDGET_UNIT1_ID,
163 BUDGET_UNIT2_ID,
164 BUDGET_UNIT3_ID
165 from PQH_BUDGETS
166 WHERE POSITION_CONTROL_FLAG = ''Y''
167 AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'', ''POSITION'')
168 AND BUDGET_START_DATE <= :p_end_date
169 AND BUDGET_END_DATE >= :p_start_date )PB,
170 (SELECT SHARED_TYPE_ID
171 FROM PER_SHARED_TYPES
172 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
173 AND SYSTEM_TYPE_CD =''HEAD'' ) PST,
174 PQH_BUDGET_VERSIONS PBV,
175 PQH_BUDGET_DETAILS PBD
176 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
177 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
178 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
179 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
180 FROM PQH_BUDGET_VERSIONS
181 WHERE BUDGET_ID = PB.BUDGET_ID )
182 ) )ORG,
183 PER_BUSINESS_GROUPS_PERF PBG
184 WHERE ORG.ID = PBG.BUSINESS_GROUP_ID
185 ORDER BY PBG.NAME
186 ';
187 RETURN l_sql_stmt;
188 END GET_NO_ORG_HDCNT_BDGTS;
189
190
191 FUNCTION GET_NO_POS_HDCNT_BDGTS
192 RETURN VARCHAR2
193 IS
194 l_sql_stmt VARCHAR2 (32000);
195 BEGIN
196 l_sql_stmt :=
197 '
198 SELECT PBG.NAME,
199 HR_GENERAL.DECODE_POSITION_LATEST_NAME(POSITION_ID),
200 NULL COL3,
201 NULL COL4,
202 NULL COL5
203 FROM
204 (
205 SELECT POSITION_ID,
206 BUSINESS_GROUP_ID ID
207 FROM HR_ALL_POSITIONS_F
208 WHERE EFFECTIVE_START_DATE <= :p_end_date
209 AND NVL(EFFECTIVE_END_DATE, hr_general.end_of_time) >= :p_start_date
210 AND (POSITION_ID, BUSINESS_GROUP_ID) NOT IN
211 (
212 SELECT /*+ ordered*/
213 PBD.POSITION_ID,
214 PB.BUSINESS_GROUP_ID ID
215 FROM (Select BUSINESS_GROUP_ID,
216 BUDGET_ID,
217 BUDGET_UNIT1_ID,
218 BUDGET_UNIT2_ID,
219 BUDGET_UNIT3_ID
220 from PQH_BUDGETS
221 WHERE POSITION_CONTROL_FLAG = ''Y''
222 AND BUDGETED_ENTITY_CD = ''POSITION''
223 AND BUDGET_START_DATE <= :p_end_date
224 AND BUDGET_END_DATE >= :p_start_date )PB,
225 (SELECT SHARED_TYPE_ID
226 FROM PER_SHARED_TYPES
227 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
228 AND SYSTEM_TYPE_CD =''HEAD'' ) PST,
229 PQH_BUDGET_VERSIONS PBV,
230 PQH_BUDGET_DETAILS PBD
231 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
232 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
233 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
234 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
235 FROM PQH_BUDGET_VERSIONS
236 WHERE BUDGET_ID = PB.BUDGET_ID )
237 ) )POS,
238 PER_BUSINESS_GROUPS_PERF PBG
239 WHERE POS.ID = PBG.BUSINESS_GROUP_ID
240 ORDER BY PBG.NAME
241 ';
242 RETURN l_sql_stmt;
243 END GET_NO_POS_HDCNT_BDGTS;
244
245
246 FUNCTION GET_CTRL_LBRCST_BDGTS
247 RETURN VARCHAR2
248 IS
249 l_sql_stmt VARCHAR2 (32000);
250 BEGIN
251 l_sql_stmt :=
252 '
253 SELECT /*+ ordered */
254 PBG.NAME,
255 PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
256 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE),
257 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
258 NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID))||'' ''||
259 TO_CHAR(
260 CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT1_VALUE
261 WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT2_VALUE
262 WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT3_VALUE
263 END,
264 FND_CURRENCY.GET_FORMAT_MASK(NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID)),30)
265 ) BUDGET_VALUE
266 FROM (Select BUDGET_NAME,
267 BUSINESS_GROUP_ID,
268 BUDGET_ID,
269 BUDGET_UNIT1_ID,
270 BUDGET_UNIT2_ID,
271 BUDGET_UNIT3_ID,
272 BUDGET_START_DATE,
273 BUDGET_END_DATE,
274 CURRENCY_CODE
275 from PQH_BUDGETS
276 WHERE POSITION_CONTROL_FLAG = ''Y''
277 AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'', ''POSITION'')
278 AND BUDGET_START_DATE <= :p_end_date
279 AND BUDGET_END_DATE >= :p_start_date )PB,
280 (SELECT SHARED_TYPE_ID
281 FROM PER_SHARED_TYPES
282 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
283 AND SYSTEM_TYPE_CD =''MONEY'' ) PST,
284 PQH_BUDGET_VERSIONS PBV,
285 PER_BUSINESS_GROUPS_PERF PBG
286 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
287 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
288 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
289 FROM PQH_BUDGET_VERSIONS
290 WHERE BUDGET_ID = PB.BUDGET_ID )
291 AND PBG.BUSINESS_GROUP_ID = PB.BUSINESS_GROUP_ID
292 ORDER BY PBG.NAME
293 ';
294 RETURN l_sql_stmt;
295 END GET_CTRL_LBRCST_BDGTS;
296
297
298 FUNCTION GET_NO_CTRL_LBRCST_BDGTS_PRDS
299 RETURN VARCHAR2
300 IS
301 l_sql_stmt VARCHAR2 (32000);
302 BEGIN
303 l_sql_stmt :=
304
305 'SELECT PBG.NAME,
306 FND_DATE.DATE_TO_DISPLAYDATE(PRDS.EFFECTIVE_START_DATE),
307 FND_DATE.DATE_TO_DISPLAYDATE(PRDS.EFFECTIVE_END_DATE),
308 NULL COL4,
309 NULL COL5
310 FROM
311 (
312 (SELECT ID,
313 CASE WHEN (LEAD(DATE_FROM, 1) OVER
314 (PARTITION BY ID ORDER BY DATE_FROM)) IS NULL
315 AND DATE_FROM > :p_start_date
316 AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
317 THEN :p_start_date
318 WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
319 THEN :p_start_date
320 END EFFECTIVE_START_DATE,
321 CASE WHEN (LEAD(DATE_FROM, 1) OVER
322 (PARTITION BY ID ORDER BY DATE_FROM)) IS NULL
323 AND DATE_FROM > :p_start_date
324 AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
325 THEN DATE_FROM -1
326 WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
327 THEN DATE_FROM -1
328 END EFFECTIVE_END_DATE
329 FROM
330 ( SELECT PB.BUSINESS_GROUP_ID ID,
331 PB.BUDGET_START_DATE DATE_FROM,
332 PB.BUDGET_END_DATE DATE_TO
333 FROM PQH_BUDGETS PB,
334 (SELECT SHARED_TYPE_ID
335 FROM PER_SHARED_TYPES
336 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
337 AND SYSTEM_TYPE_CD=''MONEY'' ) PST
338 WHERE PB.POSITION_CONTROL_FLAG = ''Y''
339 AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
340 AND PB.BUDGET_START_DATE <= :p_end_date
341 AND PB.BUDGET_END_DATE >= :p_start_date
342 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
343 ORDER BY DATE_FROM ))
344 UNION
345 (SELECT ID,
346 CASE WHEN DATE_TO+1 < NVL((LEAD(DATE_FROM, 1) OVER
347 (PARTITION BY ID ORDER BY DATE_FROM)), :p_end_date)
348 THEN DATE_TO +1
349 WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
350 THEN :p_start_date
351 END EFFECTIVE_START_DATE,
352
353 CASE WHEN DATE_TO+1 < NVL((LEAD(DATE_FROM, 1) OVER
354 (PARTITION BY ID ORDER BY DATE_FROM)), :p_end_date)
355 THEN NVL((LEAD(DATE_FROM, 1) OVER
356 (PARTITION BY ID ORDER BY DATE_FROM)) - 1, :p_end_date)
357 WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
358 THEN DATE_FROM -1
359 END EFFECTIVE_END_DATE
360 FROM
361 ( SELECT PB.BUSINESS_GROUP_ID ID,
362 PB.BUDGET_START_DATE DATE_FROM,
363 PB.BUDGET_END_DATE DATE_TO
364 FROM PQH_BUDGETS PB,
365 (SELECT SHARED_TYPE_ID
366 FROM PER_SHARED_TYPES
367 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
368 AND SYSTEM_TYPE_CD=''MONEY'' ) PST
369 WHERE PB.POSITION_CONTROL_FLAG = ''Y''
370 AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
371 AND PB.BUDGET_START_DATE <= :p_end_date
372 AND PB.BUDGET_END_DATE >= :p_start_date
373 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
374 ORDER BY DATE_FROM ))
375 )PRDS,
376 PER_BUSINESS_GROUPS PBG
377 WHERE EFFECTIVE_START_DATE IS NOT NULL
378 AND PRDS.ID = PBG.BUSINESS_GROUP_ID
379 ORDER BY PBG.NAME';
380 RETURN l_sql_stmt;
381 END GET_NO_CTRL_LBRCST_BDGTS_PRDS;
382
383 FUNCTION GET_NO_ORG_LBRCST_BDGTS
384 RETURN VARCHAR2
385 IS
386 l_sql_stmt VARCHAR2 (32000);
387 BEGIN
388 l_sql_stmt :=
389 '
390 SELECT PBG.NAME,
391 HR_GENERAL.DECODE_ORGANIZATION(ORG.ORGANIZATION_ID),
392 NULL COL3,
393 NULL COL4,
394 NULL COL5
395 FROM
396 (
397 SELECT ORGANIZATION_ID,
398 BUSINESS_GROUP_ID ID
399 FROM HR_ALL_ORGANIZATION_UNITS
403 (
400 WHERE DATE_FROM <= :p_end_date
401 AND NVL(DATE_TO,hr_general.end_of_time) >= :p_start_date
402 AND (ORGANIZATION_ID, BUSINESS_GROUP_ID) NOT IN
404 SELECT /*+ ordered*/
405 PBD.ORGANIZATION_ID,
406 PB.BUSINESS_GROUP_ID ID
407 FROM (Select BUSINESS_GROUP_ID,
408 BUDGET_ID,
409 BUDGET_UNIT1_ID,
410 BUDGET_UNIT2_ID,
411 BUDGET_UNIT3_ID
412 from PQH_BUDGETS
413 WHERE POSITION_CONTROL_FLAG = ''Y''
414 AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
415 AND BUDGET_START_DATE <= :p_end_date
416 AND BUDGET_END_DATE >= :p_start_date )PB,
417 (SELECT SHARED_TYPE_ID
418 FROM PER_SHARED_TYPES
419 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
420 AND SYSTEM_TYPE_CD =''MONEY'' ) PST,
421 PQH_BUDGET_VERSIONS PBV,
422 PQH_BUDGET_DETAILS PBD
423 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
424 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
425 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
426 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
427 FROM PQH_BUDGET_VERSIONS
428 WHERE BUDGET_ID = PB.BUDGET_ID )
429 )) ORG,
430 PER_BUSINESS_GROUPS_PERF PBG
431 WHERE ORG.ID = PBG.BUSINESS_GROUP_ID
432 ORDER BY PBG.NAME
433 ';
434
435 RETURN l_sql_stmt;
436 END GET_NO_ORG_LBRCST_BDGTS;
437
438
439 FUNCTION GET_NO_POS_LBRCST_BDGTS
440 RETURN VARCHAR2
441 IS
442 l_sql_stmt VARCHAR2 (32000);
443 BEGIN
444 l_sql_stmt :=
445 '
446 SELECT PBG.NAME,
447 HR_GENERAL.DECODE_POSITION_LATEST_NAME(POS.POSITION_ID),
448 NULL COL3,
449 NULL COL4,
450 NULL COL5
451 FROM
452 (
453 SELECT POSITION_ID,
454 BUSINESS_GROUP_ID ID
455 FROM HR_ALL_POSITIONS_F
456 WHERE EFFECTIVE_START_DATE <= :p_end_date
457 AND NVL(EFFECTIVE_END_DATE, hr_general.end_of_time) >= :p_start_date
458 AND (POSITION_ID, BUSINESS_GROUP_ID) NOT IN
459 (
460 SELECT /*+ ordered*/
461 PBD.POSITION_ID,
462 PB.BUSINESS_GROUP_ID ID
463 FROM (Select BUSINESS_GROUP_ID,
464 BUDGET_ID,
465 BUDGET_UNIT1_ID,
466 BUDGET_UNIT2_ID,
467 BUDGET_UNIT3_ID
468 from PQH_BUDGETS
469 WHERE POSITION_CONTROL_FLAG = ''Y''
470 AND BUDGETED_ENTITY_CD = ''POSITION''
471 AND BUDGET_START_DATE <= :p_end_date
472 AND BUDGET_END_DATE >= :p_start_date )PB,
473 (SELECT SHARED_TYPE_ID
474 FROM PER_SHARED_TYPES
475 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
476 AND SYSTEM_TYPE_CD =''MONEY'' ) PST,
477 PQH_BUDGET_VERSIONS PBV,
478 PQH_BUDGET_DETAILS PBD
479 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
480 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
481 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
482 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
483 FROM PQH_BUDGET_VERSIONS
484 WHERE BUDGET_ID = PB.BUDGET_ID )
485 ) )POS,
486 PER_BUSINESS_GROUPS_PERF PBG
487 WHERE POS.ID = PBG.BUSINESS_GROUP_ID
488 ORDER BY PBG.NAME
489 ';
490 RETURN l_sql_stmt;
491 END GET_NO_POS_LBRCST_BDGTS;
492
493 FUNCTION GET_BSET_NO_FUND_SCR
494 RETURN VARCHAR2
495 IS
496 l_sql_stmt VARCHAR2 (32000);
497 BEGIN
498 l_sql_stmt :=
499 '
500 SELECT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
501 PDBS.DFLT_BUDGET_SET_NAME,
502 HRI_APL_DGNSTC_WBM.GET_ELE_NAME(ELE.BUDGET_ELEMENT_ID,:p_start_date),
503 NULL COL4,
504 NULL COL5
505 FROM PQH_BUDGETS PB,
506 PQH_BUDGET_VERSIONS PBV,
507 PQH_BUDGET_DETAILS PBD,
508 (SELECT SHARED_TYPE_ID
509 FROM PER_SHARED_TYPES
510 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
511 AND SYSTEM_TYPE_CD=''MONEY'' ) PST,
512 PQH_BUDGET_DETAILS DET,
513 PQH_BUDGET_PERIODS PRD,
514 PER_TIME_PERIODS PTPS,
515 PER_TIME_PERIODS PTPE,
516 PQH_BUDGET_SETS BSET,
517 PQH_BUDGET_ELEMENTS ELE,
518 PQH_BUDGET_FUND_SRCS SRC,
519 PQH_DFLT_BUDGET_SETS PDBS
520 WHERE PB.POSITION_CONTROL_FLAG = ''Y''
521 AND PB.BUDGET_ID = PBV.BUDGET_ID
522 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
523 AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
524 AND PB.BUDGET_START_DATE <= :p_end_date
525 AND PB.BUDGET_END_DATE >= :p_start_date
526 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
527 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
528 FROM PQH_BUDGET_VERSIONS
529 WHERE BUDGET_ID = PB.BUDGET_ID )
530 AND DET.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
531 AND DET.BUDGET_DETAIL_ID = PRD.BUDGET_DETAIL_ID
532 AND PRD.BUDGET_PERIOD_ID = BSET.BUDGET_PERIOD_ID
533 AND PRD.START_TIME_PERIOD_ID = PTPS.TIME_PERIOD_ID
534 AND PRD.END_TIME_PERIOD_ID = PTPE.TIME_PERIOD_ID
535 AND BSET.BUDGET_SET_ID = ELE.BUDGET_SET_ID
536 AND ELE.BUDGET_ELEMENT_ID = SRC.BUDGET_ELEMENT_ID
537 AND SRC.COST_ALLOCATION_KEYFLEX_ID IS NULL
538 AND BSET.DFLT_BUDGET_SET_ID = PDBS.DFLT_BUDGET_SET_ID
542 END GET_BSET_NO_FUND_SCR;
539 AND PB.BUSINESS_GROUP_ID = PDBS.BUSINESS_GROUP_ID
540 ';
541 RETURN l_sql_stmt;
543
544
545 FUNCTION GET_BSET_PRCT_DSTR
546 RETURN VARCHAR2
547 IS
548 l_sql_stmt VARCHAR2 (32000);
549 BEGIN
550 l_sql_stmt :=
551 '
552 SELECT BSET.BUDGET,
553 HRI_APL_DGNSTC_WBM.GET_BSET_NAME(BSET.BUDGET_SET_ID,BSET.BUSINESS_GROUP_ID),
554 SUM( CASE WHEN BSET.BUDGET_UNIT1_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
555 WHEN BSET.BUDGET_UNIT2_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
556 WHEN BSET.BUDGET_UNIT3_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
557 END ),
558 NULL COL4,
559 NULL COL5
560 FROM
561 ( SELECT DISTINCT BSET.BUDGET_SET_ID,
562 PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'' BUDGET,
563 PB.BUSINESS_GROUP_ID,
564 PB.BUDGET_UNIT1_ID,
565 PB.BUDGET_UNIT2_ID,
566 PB.BUDGET_UNIT3_ID,
567 PST.SHARED_TYPE_ID
568 FROM PQH_BUDGETS PB,
569 PQH_BUDGET_VERSIONS PBV,
570 PQH_BUDGET_DETAILS PBD,
571 (SELECT SHARED_TYPE_ID
572 FROM PER_SHARED_TYPES
573 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
574 AND SYSTEM_TYPE_CD=''MONEY'' ) PST,
575 PQH_BUDGET_DETAILS DET,
576 PQH_BUDGET_PERIODS PRD,
577 PER_TIME_PERIODS PTPS,
578 PER_TIME_PERIODS PTPE,
579 PQH_BUDGET_SETS BSET
580 WHERE PB.POSITION_CONTROL_FLAG = ''Y''
581 AND PB.BUDGET_ID = PBV.BUDGET_ID
582 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
583 AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
584 AND PB.BUDGET_START_DATE <= :p_end_date
585 AND PB.BUDGET_END_DATE >= :p_start_date
586 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
587 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
588 FROM PQH_BUDGET_VERSIONS
589 WHERE BUDGET_ID = PB.BUDGET_ID )
590 AND DET.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
591 AND DET.BUDGET_DETAIL_ID = PRD.BUDGET_DETAIL_ID
592 AND PRD.BUDGET_PERIOD_ID = BSET.BUDGET_PERIOD_ID
593 AND PRD.START_TIME_PERIOD_ID = PTPS.TIME_PERIOD_ID
594 AND PRD.END_TIME_PERIOD_ID = PTPE.TIME_PERIOD_ID ) BSET,
595 PQH_BUDGET_ELEMENTS ELE
596 WHERE BSET.BUDGET_SET_ID = ELE.BUDGET_SET_ID
597 GROUP BY BSET.BUDGET_SET_ID,
598 BSET.BUSINESS_GROUP_ID,
599 BSET.BUDGET
600 HAVING SUM(
601 CASE WHEN BSET.BUDGET_UNIT1_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
602 WHEN BSET.BUDGET_UNIT2_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
603 WHEN BSET.BUDGET_UNIT3_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
604 END )
605 <> 100
606 ';
607 RETURN l_sql_stmt;
608 END GET_BSET_PRCT_DSTR;
609
610 FUNCTION GET_BDGTS_CMNTS_ELE
611 RETURN VARCHAR2
612 IS
613 l_sql_stmt VARCHAR2 (32000);
614 BEGIN
615 l_sql_stmt :=
616 '
617 SELECT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
618 ELE.ELEMENT_NAME,
619 PIVF.NAME,
620 PBCE.SALARY_BASIS_FLAG,
621 PBCE.FORMULA_ID
622 FROM PQH_BDGT_CMMTMNT_ELMNTS PBCE,
623 PQH_BUDGETS PB,
624 PQH_BUDGET_VERSIONS PBV,
625 PAY_ELEMENT_TYPES_F ELE,
626 PAY_INPUT_VALUES_F PIVF
627 WHERE PBCE.BUDGET_ID = PB.BUDGET_ID
628 AND PB.BUDGET_ID = PBV.BUDGET_ID
629 AND ELE.ELEMENT_TYPE_ID = PBCE.ELEMENT_TYPE_ID
630 AND PB.BUDGET_START_DATE <= :p_end_date
631 AND PB.BUDGET_END_DATE >= :p_start_date
632 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
633 FROM PQH_BUDGET_VERSIONS
634 WHERE BUDGET_ID = PB.BUDGET_ID )
635 AND PIVF.INPUT_VALUE_ID = PBCE.ELEMENT_INPUT_VALUE_ID
636 AND :p_start_date BETWEEN ELE.EFFECTIVE_START_DATE AND ELE.EFFECTIVE_END_DATE
637 AND :p_start_date BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
638 ORDER BY PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')''
639 ';
640 RETURN l_sql_stmt;
641 END GET_BDGTS_CMNTS_ELE;
642
643
644 FUNCTION GET_BDGTS_CMNTS_RUN
645 RETURN VARCHAR2
646 IS
647 l_sql_stmt VARCHAR2 (32000);
648 BEGIN
649 l_sql_stmt :=
650 '
651 SELECT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
652 FND_DATE.DATE_TO_DISPLAYDATE(PEC.COMMITMENT_START_DATE),
653 FND_DATE.DATE_TO_DISPLAYDATE(PEC.COMMITMENT_END_DATE),
654 ELE.ELEMENT_NAME,
655 NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID))||'' ''||
656 TO_CHAR(
657 SUM(PEC.COMMITMENT_AMOUNT),
658 FND_CURRENCY.GET_FORMAT_MASK(NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID)),30)
659 ) COMMITMENT_AMOUNT
660 FROM PQH_ELEMENT_COMMITMENTS PEC,
661 PQH_BUDGETS PB,
662 PQH_BUDGET_VERSIONS PBV,
663 PAY_ELEMENT_TYPES_F ELE
664 WHERE PEC.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
665 AND PB.BUDGET_ID = PBV.BUDGET_ID
666 AND ELE.ELEMENT_TYPE_ID = PEC.ELEMENT_TYPE_ID
667 AND PB.BUDGET_START_DATE <= :p_end_date
668 AND PB.BUDGET_END_DATE >= :p_start_date
669 AND PEC.COMMITMENT_START_DATE <= :p_end_date
670 AND PEC.COMMITMENT_END_DATE >= :p_start_date
674 AND :p_start_date BETWEEN ele.EFFECTIVE_START_DATE AND ele.EFFECTIVE_END_DATE
671 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
672 FROM PQH_BUDGET_VERSIONS
673 WHERE BUDGET_ID = PB.BUDGET_ID )
675 GROUP BY PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
676 PB.CURRENCY_CODE,
677 PB.BUDGET_ID,
678 PEC.COMMITMENT_START_DATE,
679 PEC.COMMITMENT_END_DATE,
680 ELE.ELEMENT_NAME
681 ORDER BY PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')''
682 ';
683 RETURN l_sql_stmt;
684 END GET_BDGTS_CMNTS_RUN;
685
686 FUNCTION GET_BDGTS_CMNTS_NOT_RUN
687 RETURN VARCHAR2
688 IS
689 l_sql_stmt VARCHAR2 (32000);
690 BEGIN
691 l_sql_stmt :=
692 '
693 SELECT BUDGET_NAME,
694 FND_DATE.DATE_TO_DISPLAYDATE(DATE_FROM),
695 FND_DATE.DATE_TO_DISPLAYDATE(DATE_TO),
696 NULL COL4,
697 NULL COL5
698 FROM
699 (
700 SELECT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'' BUDGET_NAME,
701 PBV.DATE_FROM,
702 PBV.DATE_TO
703 FROM PQH_BUDGETS PB,
704 PQH_BUDGET_VERSIONS PBV
705 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
706 AND PB.BUDGET_START_DATE <= :p_end_date
707 AND PB.BUDGET_END_DATE >= :p_start_date
708 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
709 FROM PQH_BUDGET_VERSIONS
710 WHERE BUDGET_ID = PB.BUDGET_ID )
711 MINUS
712
713 SELECT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'' BUDGET_NAME,
714 PBV.DATE_FROM,
715 PBV.DATE_TO
716 FROM PQH_ELEMENT_COMMITMENTS PEC,
717 PQH_BUDGETS PB,
718 PQH_BUDGET_VERSIONS PBV
719 WHERE PEC.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
720 AND PB.BUDGET_ID = PBV.BUDGET_ID
721 AND PEC.COMMITMENT_START_DATE <= :p_end_date
722 AND PEC.COMMITMENT_END_DATE >= :p_start_date
723 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
724 FROM PQH_BUDGET_VERSIONS
725 WHERE BUDGET_ID = PB.BUDGET_ID )
726 )
727 ORDER BY BUDGET_NAME
728 ';
729 RETURN l_sql_stmt;
730 END GET_BDGTS_CMNTS_NOT_RUN;
731
732 FUNCTION GET_CMNTS_BY_ASGN
733 RETURN VARCHAR2
734 IS
735 l_sql_stmt VARCHAR2 (32000);
736 BEGIN
737 l_sql_stmt :=
738 '
739 SELECT HCPV.VALUE,
740 FND_DATE.DATE_TO_DISPLAYDATE(PEC.COMMITMENT_START_DATE),
741 FND_DATE.DATE_TO_DISPLAYDATE(PEC.COMMITMENT_END_DATE),
742 ELE.ELEMENT_NAME,
743 PEC.COMMITMENT_AMOUNT
744 FROM PQH_ELEMENT_COMMITMENTS PEC,
745 per_all_assignments_f PAAF,
746 HRI_CL_PER_V HCPV,
747 PAY_ELEMENT_TYPES_F ELE
748 WHERE PEC.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
749 AND PAAF.PERSON_ID = HCPV.ID
750 AND ELE.ELEMENT_TYPE_ID = PEC.ELEMENT_TYPE_ID
751 AND PEC.COMMITMENT_START_DATE <= :p_end_date
752 AND PEC.COMMITMENT_END_DATE >= :p_start_date
753 AND :p_start_date BETWEEN ele.EFFECTIVE_START_DATE AND ele.EFFECTIVE_END_DATE
754 AND :p_start_date BETWEEN hcpv.EFFECTIVE_START_DATE AND hcpv.EFFECTIVE_END_DATE
755 AND :p_start_date BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
756 ORDER BY HCPV.VALUE
757 ';
758 RETURN l_sql_stmt;
759 END GET_CMNTS_BY_ASGN;
760
761
762 FUNCTION GET_NO_CMNTS_PRDS
763 RETURN VARCHAR2
764 IS
765 l_sql_stmt VARCHAR2 (32000);
766 BEGIN
767 l_sql_stmt :=
768 '
769 SELECT PRDS.VALUE,
770 ELE.ELEMENT_NAME,
771 FND_DATE.DATE_TO_DISPLAYDATE(PRDS.START_DATE),
772 FND_DATE.DATE_TO_DISPLAYDATE(PRDS.END_DATE),
773 NULL COL5
774 FROM
775 (
776 (SELECT VALUE,
777 ELEMENT_TYPE_ID,
778 CASE WHEN (LEAD(START_DATE, 1) OVER
779 (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) IS NULL
780 AND START_DATE > :p_start_date
781 AND (row_number() over (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) =1
782 THEN :p_start_date
783 WHEN START_DATE > :p_start_date AND (ROW_NUMBER() OVER (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) = 1
784 THEN :p_start_date
785 END START_DATE,
786 CASE WHEN (LEAD(START_DATE, 1) OVER
787 (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) IS NULL
788 AND START_DATE > :p_start_date
789 AND (row_number() over (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) =1
790 THEN START_DATE -1
791 WHEN START_DATE > :p_start_date AND (ROW_NUMBER() OVER (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) = 1
792 THEN START_DATE -1
793 END END_DATE
794 FROM
795 (
796 SELECT HCPV.VALUE,
797 PEC.ELEMENT_TYPE_ID,
798 PEC.COMMITMENT_START_DATE START_DATE,
799 PEC.COMMITMENT_END_DATE END_DATE
800 FROM PQH_ELEMENT_COMMITMENTS PEC,
801 per_all_assignments_f PAAF,
802 HRI_CL_PER_V HCPV
803 WHERE PEC.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
804 AND PAAF.PERSON_ID = HCPV.ID
805 AND PEC.COMMITMENT_START_DATE <= :p_end_date
809 ORDER BY PEC.COMMITMENT_START_DATE ))
806 AND PEC.COMMITMENT_END_DATE >= :p_start_date
807 AND :p_start_date BETWEEN hcpv.EFFECTIVE_START_DATE AND hcpv.EFFECTIVE_END_DATE
808 AND :p_start_date BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
810 UNION
811 (SELECT VALUE,
812 ELEMENT_TYPE_ID,
813 CASE WHEN END_DATE +1 < NVL((LEAD(START_DATE, 1) OVER
814 (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)), :p_end_date)
815 THEN END_DATE +1
816 WHEN START_DATE > :p_start_date AND (ROW_NUMBER() OVER (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) = 1
817 THEN :p_start_date
818 END EFFECTIVE_START_DATE,
819 CASE WHEN END_DATE +1 < NVL((LEAD(START_DATE, 1) OVER
820 (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)), :p_end_date)
821 THEN NVL((LEAD(START_DATE, 1) OVER
822 (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) - 1, :p_end_date)
823 WHEN START_DATE > :p_start_date AND (ROW_NUMBER() OVER (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) = 1
824 THEN START_DATE -1
825 END EFFECTIVE_END_DATE
826 FROM
827 (
828 SELECT HCPV.VALUE,
829 PEC.ELEMENT_TYPE_ID,
830 PEC.COMMITMENT_START_DATE START_DATE,
831 PEC.COMMITMENT_END_DATE END_DATE
832 FROM PQH_ELEMENT_COMMITMENTS PEC,
833 per_all_assignments_f PAAF,
834 HRI_CL_PER_V HCPV
835 WHERE PEC.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
836 AND PAAF.PERSON_ID = HCPV.ID
837 AND PEC.COMMITMENT_START_DATE <= :p_end_date
838 AND PEC.COMMITMENT_END_DATE >= :p_start_date
839 AND :p_start_date BETWEEN hcpv.EFFECTIVE_START_DATE AND hcpv.EFFECTIVE_END_DATE
840 AND :p_start_date BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
841 ORDER BY PEC.COMMITMENT_START_DATE )) ) PRDS,
842 PAY_ELEMENT_TYPES_F ELE
843 WHERE PRDS.START_DATE IS NOT NULL
844 AND ELE.ELEMENT_TYPE_ID = PRDS.ELEMENT_TYPE_ID
845 AND :p_start_date BETWEEN ele.EFFECTIVE_START_DATE AND ele.EFFECTIVE_END_DATE
846 ORDER BY PRDS.VALUE';
847
848 RETURN l_sql_stmt;
849 END GET_NO_CMNTS_PRDS;
850
851
852 FUNCTION GET_WBM_ORG_MGR
853 RETURN VARCHAR2
854 IS
855 l_sql_stmt VARCHAR2 (32000);
856 BEGIN
857 l_sql_stmt :=
858 '
859 SELECT DISTINCT FULL_NAME,
860 NAME,
861 FND_DATE.DATE_TO_DISPLAYDATE(START_DATE),
862 FND_DATE.DATE_TO_DISPLAYDATE(END_DATE),
863 NULL COL5
864 FROM
865 (
866 SELECT PPF.FULL_NAME,
867 ORG_TL.NAME,
868 FND_DATE.CANONICAL_TO_DATE(ORG_INFO2.ORG_INFORMATION3) START_DATE,
869 FND_DATE.CANONICAL_TO_DATE(ORG_INFO2.ORG_INFORMATION4) END_DATE
870 FROM HR_ALL_ORGANIZATION_UNITS ORG,
871 HR_ALL_ORGANIZATION_UNITS_TL ORG_TL,
872 HR_ORGANIZATION_INFORMATION ORG_INFO2,
873 PER_ALL_PEOPLE_F PPF
874 WHERE ORG_INFO2.ORGANIZATION_ID = ORG.ORGANIZATION_ID
875 AND ORG_INFO2.ORG_INFORMATION_CONTEXT = ''Organization Name Alias''
876 AND TO_NUMBER(ORG_INFO2.ORG_INFORMATION2) = PPF.PERSON_ID
877 AND ORG.ORGANIZATION_ID = ORG_TL.ORGANIZATION_ID
878 AND ORG_TL.LANGUAGE = USERENV(''LANG'')
879 AND EXISTS (SELECT NULL
880 FROM HR_ORG_INFO_TYPES_BY_CLASS OITBC,
881 HR_ORGANIZATION_INFORMATION ORG_INFO
882 WHERE ORG_INFO.ORGANIZATION_ID = ORG.ORGANIZATION_ID
883 AND ORG_INFO.ORG_INFORMATION_CONTEXT = ''CLASS''
884 AND ORG_INFO.ORG_INFORMATION2 = ''Y''
885 AND OITBC.ORG_CLASSIFICATION = ORG_INFO.ORG_INFORMATION1
886 AND OITBC.ORG_INFORMATION_TYPE = ''Organization Name Alias'')
887 )
888 WHERE START_DATE <= :p_end_date
889 AND NVL(END_DATE,:p_start_date) >= :p_start_date
890 ORDER BY FULL_NAME
891 ';
892 RETURN l_sql_stmt;
893 END GET_WBM_ORG_MGR;
894
895
896 FUNCTION GET_ORG_WITHOUT_MGR
897 RETURN VARCHAR2
898 IS
899 l_sql_stmt VARCHAR2 (32000);
900 BEGIN
901 l_sql_stmt :=
902 '
903 SELECT PBG.NAME,
904 HR_GENERAL.DECODE_ORGANIZATION(ORGS.ORGANIZATION_ID),
905 NULL COL3,
906 NULL COL4,
907 NULL COL5
908 FROM
909 (
910 SELECT /*+ ordered*/
911 PBD.ORGANIZATION_ID,
912 PB.BUSINESS_GROUP_ID ID
913 FROM (Select BUSINESS_GROUP_ID,
914 BUDGET_ID,
915 BUDGET_UNIT1_ID,
916 BUDGET_UNIT2_ID,
917 BUDGET_UNIT3_ID
918 from PQH_BUDGETS
919 WHERE POSITION_CONTROL_FLAG = ''Y''
920 AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
921 AND BUDGET_START_DATE <= :p_end_date
922 AND BUDGET_END_DATE >= :p_start_date )PB,
923 (SELECT SHARED_TYPE_ID
924 FROM PER_SHARED_TYPES
925 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
926 AND SYSTEM_TYPE_CD IN (''MONEY'',''HEAD'')) PST,
927 PQH_BUDGET_VERSIONS PBV,
928 PQH_BUDGET_DETAILS PBD
929 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
930 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
931 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
932 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
933 FROM PQH_BUDGET_VERSIONS
934 WHERE BUDGET_ID = PB.BUDGET_ID )
935 MINUS
936
940 (
937 SELECT ORGANIZATION_ID,
938 BUSINESS_GROUP_ID ID
939 FROM
941 SELECT ORG.ORGANIZATION_ID,
942 ORG.BUSINESS_GROUP_ID,
943 FND_DATE.CANONICAL_TO_DATE(ORG_INFO2.ORG_INFORMATION3) START_DATE,
944 FND_DATE.CANONICAL_TO_DATE(ORG_INFO2.ORG_INFORMATION4) END_DATE
945 FROM HR_ALL_ORGANIZATION_UNITS ORG,
946 HR_ORGANIZATION_INFORMATION ORG_INFO2,
947 PER_ALL_PEOPLE_F PPF
948 WHERE ORG_INFO2.ORGANIZATION_ID = ORG.ORGANIZATION_ID
949 AND ORG_INFO2.ORG_INFORMATION_CONTEXT = ''Organization Name Alias''
950 AND TO_NUMBER(ORG_INFO2.ORG_INFORMATION2) = PPF.PERSON_ID
951 AND EXISTS (SELECT NULL
952 FROM HR_ORG_INFO_TYPES_BY_CLASS OITBC,
953 HR_ORGANIZATION_INFORMATION ORG_INFO
954 WHERE ORG_INFO.ORGANIZATION_ID = ORG.ORGANIZATION_ID
955 AND ORG_INFO.ORG_INFORMATION_CONTEXT = ''CLASS''
956 AND ORG_INFO.ORG_INFORMATION2 = ''Y''
957 AND OITBC.ORG_CLASSIFICATION = ORG_INFO.ORG_INFORMATION1
958 AND OITBC.ORG_INFORMATION_TYPE = ''Organization Name Alias'')
959 )
960 WHERE START_DATE <= :p_end_date
961 AND NVL(END_DATE,:p_start_date) >= :p_start_date
962 ) ORGS,
963 PER_BUSINESS_GROUPS_PERF PBG
964 WHERE ORGS.ID = PBG.BUSINESS_GROUP_ID
965 ORDER BY PBG.NAME
966 ';
967 RETURN l_sql_stmt;
968 END GET_ORG_WITHOUT_MGR;
969
970 FUNCTION GET_WBM_USER_VALID_SETUP
971 RETURN VARCHAR2
972 IS
973 l_sql_stmt VARCHAR2 (32000);
974 BEGIN
975 l_sql_stmt :=
976 '
977 SELECT DISTINCT usr.user_name,
978 FND_DATE.DATE_TO_DISPLAYDATE(usr.start_date),
979 NULL COL3,
980 NULL COL4,
981 NULL COL5
982 FROM FND_USER usr,
983 WF_USER_ROLE_ASSIGNMENTS waur,
984 WF_LOCAL_ROLES wlr,
985 FND_RESPONSIBILITY resp
986 WHERE resp.responsibility_id = wlr.orig_system_id
987 AND resp.responsibility_key = ''HRI_DBI_WRKFC_BDGT_MANAGER''
988 AND wlr.orig_system = ''FND_RESP''
989 AND usr.user_name = waur.user_name
990 AND waur.role_name = wlr.name
991 AND sysdate BETWEEN usr.start_date AND NVL(usr.end_date,hr_general.end_of_time)
992 AND sysdate BETWEEN resp.start_date and NVL(resp.end_date, hr_general.end_of_time)
993 ORDER BY usr.user_name
994 ';
995 RETURN l_sql_stmt;
996 END GET_WBM_USER_VALID_SETUP;
997
998 FUNCTION GET_WBM_PRSN_NOT_ASSIGNED
999 RETURN VARCHAR2
1000 IS
1001 l_sql_stmt VARCHAR2 (32000);
1002 BEGIN
1003 l_sql_stmt :=
1004 '
1005 SELECT DISTINCT usr.user_name,
1006 FND_DATE.DATE_TO_DISPLAYDATE(usr.start_date),
1007 null col3,
1008 null col4,
1009 null col5
1010 FROM FND_USER usr,
1011 WF_USER_ROLE_ASSIGNMENTS waur,
1012 WF_LOCAL_ROLES wlr,
1013 FND_RESPONSIBILITY resp
1014 WHERE resp.responsibility_id = wlr.orig_system_id
1015 AND resp.responsibility_key = ''HRI_DBI_WRKFC_BDGT_MANAGER''
1016 AND wlr.orig_system = ''FND_RESP''
1017 AND usr.user_name = waur.user_name
1018 AND waur.role_name = wlr.name
1019 AND usr.employee_id IS NULL
1020 AND sysdate BETWEEN usr.start_date AND NVL(usr.end_date,hr_general.end_of_time)
1021 AND sysdate BETWEEN resp.start_date and NVL(resp.end_date, hr_general.end_of_time)
1022 ORDER BY usr.user_name
1023 ';
1024 RETURN l_sql_stmt;
1025 END GET_WBM_PRSN_NOT_ASSIGNED;
1026
1027
1028 FUNCTION GET_WBM_PRSN_NOT_IN_SUP_HRCHY
1029 RETURN VARCHAR2
1030 IS
1031 l_sql_stmt VARCHAR2 (32000);
1032 BEGIN
1033 l_sql_stmt :=
1034 '
1035 SELECT DISTINCT usr.user_name,
1036 FND_DATE.DATE_TO_DISPLAYDATE(usr.start_date),
1037 null col3,
1038 null col4,
1039 null col5
1040 FROM FND_USER usr,
1041 WF_USER_ROLE_ASSIGNMENTS waur,
1042 WF_LOCAL_ROLES wlr,
1043 FND_RESPONSIBILITY resp
1044 WHERE resp.responsibility_id = wlr.orig_system_id
1045 AND resp.responsibility_key = ''HRI_DBI_WRKFC_BDGT_MANAGER''
1046 AND wlr.orig_system = ''FND_RESP''
1047 AND usr.user_name = waur.user_name
1048 AND waur.role_name = wlr.name
1049 AND usr.employee_id IS NOT NULL
1050 AND NOT EXISTS (SELECT null FROM hri_cs_suph sup WHERE sup.sup_person_id = usr.employee_id)
1051 AND sysdate BETWEEN usr.start_date AND NVL(usr.end_date,hr_general.end_of_time)
1052 AND sysdate BETWEEN resp.start_date and NVL(resp.end_date, hr_general.end_of_time)
1053 ORDER BY usr.user_name
1054 ';
1055 RETURN l_sql_stmt;
1056 END GET_WBM_PRSN_NOT_IN_SUP_HRCHY;
1057
1058
1059 FUNCTION GET_WBM_LAST_PAY_RUN_DATE
1060 RETURN VARCHAR2
1061 IS
1062 l_sql_stmt VARCHAR2 (32000);
1063 BEGIN
1064 l_sql_stmt :=
1065 '
1066 SELECT PBG.NAME,
1067 PAPF.PAYROLL_NAME,
1068 FND_DATE.DATE_TO_DISPLAYDATE(MAX(PPA.EFFECTIVE_DATE)),
1069 NULL COL4,
1070 NULL COL5
1071 FROM
1072 (
1073 SELECT BUSINESS_GROUP_ID, PAYROLL_ID, EFFECTIVE_DATE
1074 FROM PAY_PAYROLL_ACTIONS
1075 WHERE ACTION_TYPE = ''R''
1076 AND ACTION_STATUS = ''C''
1077 ) PPA,
1078 PER_BUSINESS_GROUPS PBG,
1079 PAY_ALL_PAYROLLS_F PAPF
1080 WHERE PPA.BUSINESS_GROUP_ID =PBG.BUSINESS_GROUP_ID
1081 AND PAPF.PAYROLL_ID = PPA.PAYROLL_ID
1082 AND PPA.EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
1083 GROUP BY PBG.NAME,
1084 PAPF.PAYROLL_NAME
1085 ';
1086 RETURN l_sql_stmt;
1087 END GET_WBM_LAST_PAY_RUN_DATE;
1088
1089
1090 FUNCTION GET_HDCNT_BDGTS_ORG
1094 BEGIN
1091 RETURN VARCHAR2
1092 IS
1093 l_sql_stmt VARCHAR2 (32000);
1095 l_sql_stmt :=
1096 '
1097 SELECT /*+ ordered*/
1098 PBG.NAME,
1099 PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
1100 HR_GENERAL.DECODE_ORGANIZATION(PBD.ORGANIZATION_ID),
1101 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE) ||'' '' ||
1102 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
1103 SUM (
1104 CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT1_VALUE
1105 WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT2_VALUE
1106 WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT3_VALUE
1107 END )BUDGET_VALUE
1108 FROM (Select BUSINESS_GROUP_ID,
1109 BUDGET_NAME,
1110 BUDGET_START_DATE,
1111 BUDGET_END_DATE,
1112 BUDGET_ID,
1113 BUDGET_UNIT1_ID,
1114 BUDGET_UNIT2_ID,
1115 BUDGET_UNIT3_ID
1116 from PQH_BUDGETS
1117 WHERE POSITION_CONTROL_FLAG = ''Y''
1118 AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
1119 AND BUDGET_START_DATE <= :p_end_date
1120 AND BUDGET_END_DATE >= :p_start_date )PB,
1121 (SELECT SHARED_TYPE_ID
1122 FROM PER_SHARED_TYPES
1123 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
1124 AND SYSTEM_TYPE_CD =''HEAD'' ) PST,
1125 PQH_BUDGET_VERSIONS PBV,
1126 PQH_BUDGET_DETAILS PBD,
1127 PER_BUSINESS_GROUPS_PERF PBG
1128 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
1129 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
1130 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
1131 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
1132 FROM PQH_BUDGET_VERSIONS
1133 WHERE BUDGET_ID = PB.BUDGET_ID )
1134 AND PB.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
1135 GROUP BY PBG.NAME,
1136 PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
1137 HR_GENERAL.DECODE_ORGANIZATION(PBD.ORGANIZATION_ID),
1138 PB.BUDGET_START_DATE,
1139 PB.BUDGET_END_DATE
1140 ORDER BY PBG.NAME
1141 ';
1142 RETURN l_sql_stmt;
1143 END GET_HDCNT_BDGTS_ORG;
1144
1145
1146 FUNCTION GET_HDCNT_BDGTS_POS
1147 RETURN VARCHAR2
1148 IS
1149 l_sql_stmt VARCHAR2 (32000);
1150 BEGIN
1151 l_sql_stmt :=
1152 '
1153 SELECT /*+ ordered*/
1154 PBG.NAME,
1155 PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
1156 HR_GENERAL.DECODE_POSITION_LATEST_NAME(PBD.POSITION_ID),
1157 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE) ||'' '' ||
1158 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
1159 CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT1_VALUE
1160 WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT2_VALUE
1161 WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT3_VALUE
1162 END BUDGET_VALUE
1163 FROM (Select BUSINESS_GROUP_ID,
1164 BUDGET_NAME,
1165 BUDGET_START_DATE,
1166 BUDGET_END_DATE,
1167 BUDGET_ID,
1168 BUDGET_UNIT1_ID,
1169 BUDGET_UNIT2_ID,
1170 BUDGET_UNIT3_ID
1171 from PQH_BUDGETS
1172 WHERE POSITION_CONTROL_FLAG = ''Y''
1173 AND BUDGETED_ENTITY_CD = ''POSITION''
1174 AND BUDGET_START_DATE <= :p_end_date
1175 AND BUDGET_END_DATE >= :p_start_date )PB,
1176 (SELECT SHARED_TYPE_ID
1177 FROM PER_SHARED_TYPES
1178 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
1179 AND SYSTEM_TYPE_CD =''HEAD'' ) PST,
1180 PQH_BUDGET_VERSIONS PBV,
1181 PQH_BUDGET_DETAILS PBD,
1182 PER_BUSINESS_GROUPS_PERF PBG
1183 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
1184 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
1185 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
1186 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
1187 FROM PQH_BUDGET_VERSIONS
1188 WHERE BUDGET_ID = PB.BUDGET_ID )
1189 AND PB.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
1190 ORDER BY PBG.NAME
1191 ';
1192 RETURN l_sql_stmt;
1193 END GET_HDCNT_BDGTS_POS;
1194
1195
1196 FUNCTION GET_LBRCST_BDGTS_ORG
1197 RETURN VARCHAR2
1198 IS
1199 l_sql_stmt VARCHAR2 (32000);
1200 BEGIN
1201 l_sql_stmt :=
1202 '
1203 SELECT /*+ ordered*/
1204 PBG.NAME,
1205 PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
1206 HR_GENERAL.DECODE_ORGANIZATION(PBD.ORGANIZATION_ID),
1207 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE) ||'' '' ||
1208 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
1209 NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID))||'' ''||
1210 TO_CHAR(
1211 SUM(
1212 CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT1_VALUE
1213 WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT2_VALUE
1214 WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT3_VALUE
1215 END),
1216 FND_CURRENCY.GET_FORMAT_MASK(NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID)),30)
1217 ) BUDGET_VALUE
1218 FROM (Select BUSINESS_GROUP_ID,
1219 BUDGET_NAME,
1220 CURRENCY_CODE,
1221 BUDGET_START_DATE,
1222 BUDGET_END_DATE,
1223 BUDGET_ID,
1224 BUDGET_UNIT1_ID,
1225 BUDGET_UNIT2_ID,
1229 AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
1226 BUDGET_UNIT3_ID
1227 from PQH_BUDGETS
1228 WHERE POSITION_CONTROL_FLAG = ''Y''
1230 AND BUDGET_START_DATE <= :p_end_date
1231 AND BUDGET_END_DATE >= :p_start_date )PB,
1232 (SELECT SHARED_TYPE_ID
1233 FROM PER_SHARED_TYPES
1234 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
1235 AND SYSTEM_TYPE_CD =''MONEY'' ) PST,
1236 PQH_BUDGET_VERSIONS PBV,
1237 PQH_BUDGET_DETAILS PBD,
1238 PER_BUSINESS_GROUPS_PERF PBG
1239 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
1240 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
1241 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
1242 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
1243 FROM PQH_BUDGET_VERSIONS
1244 WHERE BUDGET_ID = PB.BUDGET_ID )
1245 AND PB.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
1246 GROUP BY PBG.NAME,
1247 PB.CURRENCY_CODE,
1248 PB.BUDGET_ID,
1249 PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
1250 HR_GENERAL.DECODE_ORGANIZATION(PBD.ORGANIZATION_ID),
1251 PB.BUDGET_START_DATE,
1252 PB.BUDGET_END_DATE
1253 ORDER BY PBG.NAME
1254 ';
1255 RETURN l_sql_stmt;
1256 END GET_LBRCST_BDGTS_ORG;
1257
1258
1259 FUNCTION GET_LBRCST_BDGTS_POS
1260 RETURN VARCHAR2
1261 IS
1262 l_sql_stmt VARCHAR2 (32000);
1263 BEGIN
1264 l_sql_stmt :=
1265 '
1266 SELECT /*+ ordered*/
1267 PBG.NAME,
1268 PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
1269 HR_GENERAL.DECODE_POSITION_LATEST_NAME(PBD.POSITION_ID),
1270 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE) ||'' '' ||
1271 FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
1272 NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID))||'' ''||
1273 TO_CHAR(
1274 CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT1_VALUE
1275 WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT2_VALUE
1276 WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT3_VALUE
1277 END,
1278 FND_CURRENCY.GET_FORMAT_MASK(NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID)),30)
1279 ) BUDGET_VALUE
1280 FROM (Select BUSINESS_GROUP_ID,
1281 BUDGET_NAME,
1282 CURRENCY_CODE,
1283 BUDGET_START_DATE,
1284 BUDGET_END_DATE,
1285 BUDGET_ID,
1286 BUDGET_UNIT1_ID,
1287 BUDGET_UNIT2_ID,
1288 BUDGET_UNIT3_ID
1289 from PQH_BUDGETS
1290 WHERE POSITION_CONTROL_FLAG = ''Y''
1291 AND BUDGETED_ENTITY_CD =''POSITION''
1292 AND BUDGET_START_DATE <= :p_end_date
1293 AND BUDGET_END_DATE >= :p_start_date )PB,
1294 (SELECT SHARED_TYPE_ID
1295 FROM PER_SHARED_TYPES
1296 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
1297 AND SYSTEM_TYPE_CD =''MONEY'' ) PST,
1298 PQH_BUDGET_VERSIONS PBV,
1299 PQH_BUDGET_DETAILS PBD,
1300 PER_BUSINESS_GROUPS_PERF PBG
1301 WHERE PB.BUDGET_ID = PBV.BUDGET_ID
1302 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
1303 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
1304 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
1305 FROM PQH_BUDGET_VERSIONS
1306 WHERE BUDGET_ID = PB.BUDGET_ID )
1307 AND PB.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
1308 ORDER BY PBG.NAME
1309 ';
1310 RETURN l_sql_stmt;
1311 END GET_LBRCST_BDGTS_POS;
1312
1313
1314 FUNCTION GET_BDGTS_BSET
1315 RETURN VARCHAR2
1316 IS
1317 l_sql_stmt VARCHAR2 (32000);
1318 BEGIN
1319 l_sql_stmt :=
1320 '
1321 SELECT DISTINCT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'' ,
1322 HRI_APL_DGNSTC_WBM.GET_BSET_NAME(BSET.BUDGET_SET_ID,PB.BUSINESS_GROUP_ID) ,
1323 HRI_APL_DGNSTC_WBM.GET_ELE_NAME(ELE.ELEMENT_TYPE_ID,:p_start_date) ,
1324 HRI_APL_DGNSTC_WBM.GET_FSC_NAME(SRC.COST_ALLOCATION_KEYFLEX_ID) ,
1325 (src.distribution_percentage * ele.distribution_percentage / 100 )
1326 FROM PQH_BUDGETS PB,
1327 PQH_BUDGET_VERSIONS PBV,
1328 PQH_BUDGET_DETAILS PBD,
1329 (SELECT SHARED_TYPE_ID
1330 FROM PER_SHARED_TYPES
1331 WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
1332 AND SYSTEM_TYPE_CD=''MONEY'' ) PST,
1333 PQH_BUDGET_DETAILS DET,
1334 PQH_BUDGET_PERIODS PRD,
1335 PER_TIME_PERIODS PTPS,
1336 PER_TIME_PERIODS PTPE,
1337 PQH_BUDGET_SETS BSET,
1338 PQH_BUDGET_ELEMENTS ELE,
1339 PQH_BUDGET_FUND_SRCS SRC
1340 WHERE PB.POSITION_CONTROL_FLAG = ''Y''
1341 AND PB.BUDGET_ID = PBV.BUDGET_ID
1342 AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
1343 AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
1344 AND PB.BUDGET_START_DATE <= :p_end_date
1345 AND PB.BUDGET_END_DATE >= :p_start_date
1346 AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
1347 AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
1348 FROM PQH_BUDGET_VERSIONS
1349 WHERE BUDGET_ID = PB.BUDGET_ID )
1350 AND DET.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
1351 AND DET.BUDGET_DETAIL_ID = PRD.BUDGET_DETAIL_ID
1352 AND PRD.BUDGET_PERIOD_ID = BSET.BUDGET_PERIOD_ID
1356 AND ELE.BUDGET_ELEMENT_ID = SRC.BUDGET_ELEMENT_ID
1353 AND PRD.START_TIME_PERIOD_ID = PTPS.TIME_PERIOD_ID
1354 AND PRD.END_TIME_PERIOD_ID = PTPE.TIME_PERIOD_ID
1355 AND BSET.BUDGET_SET_ID = ELE.BUDGET_SET_ID
1357 ORDER BY HRI_APL_DGNSTC_WBM.GET_BSET_NAME(BSET.BUDGET_SET_ID,PB.BUSINESS_GROUP_ID) ,
1358 HRI_APL_DGNSTC_WBM.GET_ELE_NAME(ELE.ELEMENT_TYPE_ID,:p_start_date)
1359 ';
1360 RETURN l_sql_stmt;
1361 END GET_BDGTS_BSET;
1362
1363
1364 FUNCTION GET_ELE_NOT_IN_ACTLS
1365 RETURN VARCHAR2
1366 IS
1367 l_sql_stmt VARCHAR2 (32000);
1368 BEGIN
1369 l_sql_stmt :=
1370 '
1371 SELECT HRI_APL_DGNSTC_WBM.GET_ELE_NAME(ELEMENT_TYPE_ID,:p_start_date),
1372 NULL COL2,
1373 NULL COL3,
1374 NULL COL4,
1375 NULL COL5
1376 FROM
1377 (
1378 SELECT ELEMENT_TYPE_ID
1379 FROM HRI_MB_BDGTS_CT
1380 WHERE BUDGET_START_DATE <= :p_end_date
1381 AND BUDGET_END_DATE >= :p_start_date
1382 AND BUDGET_MEASUREMENT_TYPE = ''MONEY''
1383 MINUS
1384 SELECT ELEMENT_TYPE_ID
1385 FROM HRI_MB_ACTLS_CT
1386 WHERE EFFECTIVE_DATE <= :p_end_date
1387 AND EFFECTIVE_DATE >= :p_start_date
1388 )
1389 ORDER BY HRI_APL_DGNSTC_WBM.GET_ELE_NAME(ELEMENT_TYPE_ID,:p_start_date)
1390 ';
1391 RETURN l_sql_stmt;
1392 END GET_ELE_NOT_IN_ACTLS;
1393
1394 FUNCTION GET_WBM_LAST_COST_RUN_DATE
1395 RETURN VARCHAR2
1396 IS
1397 l_sql_stmt VARCHAR2 (32000);
1398 BEGIN
1399 l_sql_stmt :=
1400 '
1401 SELECT PBG.NAME,
1402 PAPF.PAYROLL_NAME,
1403 FND_DATE.DATE_TO_DISPLAYDATE(MAX(PPA.EFFECTIVE_DATE)),
1404 NULL COL4,
1405 NULL COL5
1406 FROM
1407 (
1408 SELECT BUSINESS_GROUP_ID,
1409 PAYROLL_ID,
1410 EFFECTIVE_DATE
1411 FROM PAY_PAYROLL_ACTIONS
1412 WHERE ACTION_TYPE = ''C''
1413 AND ACTION_STATUS = ''C''
1414 ) PPA,
1415 PER_BUSINESS_GROUPS PBG,
1416 PAY_ALL_PAYROLLS_F PAPF
1417 WHERE PPA.BUSINESS_GROUP_ID =PBG.BUSINESS_GROUP_ID
1418 AND PAPF.PAYROLL_ID = PPA.PAYROLL_ID
1419 AND PPA.EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
1420 GROUP BY PBG.NAME,
1421 PAPF.PAYROLL_NAME
1422 ';
1423 RETURN l_sql_stmt;
1424 END GET_WBM_LAST_COST_RUN_DATE;
1425
1426 FUNCTION GET_BSET_NAME(p_bset_id IN NUMBER,p_business_group_id IN NUMBER)
1427 RETURN VARCHAR2
1428 IS
1429 l_bset_name VARCHAR2 (500);
1430 BEGIN
1431
1432 SELECT PDBS.DFLT_BUDGET_SET_NAME INTO l_bset_name
1433 FROM PQH_DFLT_BUDGET_SETS PDBS,
1434 PQH_BUDGET_SETS PBS
1435 WHERE PBS.BUDGET_SET_ID = p_bset_id
1436 AND PDBS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
1437 AND PBS.DFLT_BUDGET_SET_ID = PDBS.DFLT_BUDGET_SET_ID;
1438
1439 RETURN l_bset_name;
1440 END GET_BSET_NAME;
1441
1442
1443 FUNCTION GET_ELE_NAME(p_element_id IN NUMBER ,p_effective_date IN DATE)
1444 RETURN VARCHAR2
1445 IS
1446 l_ele_name VARCHAR2 (500);
1447 BEGIN
1448
1449 SELECT ELEMENT_NAME INTO l_ele_name
1450 FROM PAY_ELEMENT_TYPES_F_TL
1451 WHERE ELEMENT_TYPE_ID = p_element_id
1452 AND LANGUAGE = USERENV('LANG') ;
1453
1454 RETURN l_ele_name;
1455 END GET_ELE_NAME;
1456
1457
1458 FUNCTION GET_FSC_NAME(p_cost_allocation_keyflex_id IN NUMBER )
1459 RETURN VARCHAR2
1460 IS
1461 l_fsc_name VARCHAR2 (500);
1462 BEGIN
1463
1464 SELECT CONCATENATED_SEGMENTS INTO l_fsc_name
1465 FROM PAY_COST_ALLOCATION_KEYFLEX
1466 WHERE COST_ALLOCATION_KEYFLEX_ID = p_cost_allocation_keyflex_id;
1467
1468 RETURN l_fsc_name;
1469 END GET_FSC_NAME;
1470
1471
1472 END HRI_APL_DGNSTC_WBM;
[Home] [Help]