1 PACKAGE BODY PA_PAXMGTSD_XMLP_PKG AS
2 /* $Header: PAXMGTSDB.pls 120.0 2008/01/02 11:38:18 krreddy noship $ */
3 FUNCTION GET_COVER_PAGE_VALUES RETURN BOOLEAN IS
4 BEGIN
5 RETURN (TRUE);
6 EXCEPTION
7 WHEN OTHERS THEN
8 RETURN (FALSE);
9 END GET_COVER_PAGE_VALUES;
10
11 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
12 BEGIN
13 DECLARE
14 INIT_FAILURE EXCEPTION;
15 P_ORG HR_ORGANIZATION_UNITS.NAME%TYPE;
16 P_MGR VARCHAR2(30);
17 P_NUMBER VARCHAR2(30);
18 P_NAME VARCHAR2(30);
19 TSK_NUM VARCHAR2(30);
20 TSK_NAME VARCHAR2(30);
21 P_COST_BGT_CODE VARCHAR2(30);
22 P_COST_BGT_TYPE VARCHAR2(30);
23 P_REV_BGT_CODE VARCHAR2(30);
24 P_REV_BGT_TYPE VARCHAR2(30);
25 BEGIN
26 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
27 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
28 /*SRW.USER_EXIT('FND GETPROFILE
29 NAME="PA_RULE_BASED_OPTIMIZER"
30 FIELD=":p_rule_optimizer"
31 PRINT_ERROR="N"')*/NULL;
32 P_DEBUG_MODE := FND_PROFILE.VALUE('PA_DEBUG_MODE');
33 IF TASK_ORG IS NOT NULL THEN
34 SELECT
35 SUBSTR(NAME
36 ,1
37 ,60)
38 INTO P_ORG
39 FROM
40 HR_ORGANIZATION_UNITS
41 WHERE TASK_ORG = ORGANIZATION_ID;
42 END IF;
43 C_ORG := P_ORG;
44 IF TASK_MGR IS NOT NULL THEN
45 SELECT
46 FULL_NAME
47 INTO P_MGR
48 FROM
49 PER_PEOPLE_F
50 WHERE TASK_MGR = PERSON_ID
51 AND sysdate between EFFECTIVE_START_DATE
52 AND NVL(EFFECTIVE_END_DATE
53 ,SYSDATE + 1)
54 AND EMPLOYEE_NUMBER IS NOT NULL;
55 END IF;
56 C_MGR := P_MGR;
57 IF PROJ IS NOT NULL THEN
58 SELECT
59 SEGMENT1,
60 NAME
61 INTO P_NUMBER,P_NAME
62 FROM
63 PA_PROJECTS
64 WHERE PROJ = PROJECT_ID;
65 END IF;
66 C_PROJ_NUM := P_NUMBER;
67 C_PROJ_NAME := P_NAME;
68 IF TASK IS NOT NULL THEN
69 SELECT
70 TASK_NUMBER,
71 TASK_NAME
72 INTO TSK_NUM,TSK_NAME
73 FROM
74 PA_TASKS
75 WHERE TASK = TASK_ID;
76 END IF;
77 C_TASK_NUM := TSK_NUM;
78 C_TASK_NAME := TSK_NAME;
79 IF (GET_COMPANY_NAME <> TRUE) THEN
80 RAISE INIT_FAILURE;
81 END IF;
82 IF (NO_DATA_FOUND_FUNC <> TRUE) THEN
83 RAISE INIT_FAILURE;
84 END IF;
85 IF (COST_BGT_CODE IS NULL) THEN
86 SELECT
87 BUDGET_TYPE_CODE,
88 BUDGET_TYPE
89 INTO P_COST_BGT_CODE,P_COST_BGT_TYPE
90 FROM
91 PA_BUDGET_TYPES
92 WHERE BUDGET_AMOUNT_CODE = 'C'
93 AND PREDEFINED_FLAG = 'Y'
94 AND BUDGET_TYPE_CODE = 'AC';
95 C_COST_BGT_CODE := P_COST_BGT_CODE;
96 C_COST_BGT_NAME := P_COST_BGT_TYPE;
97 ELSE
98 SELECT
99 BUDGET_TYPE
100 INTO P_COST_BGT_TYPE
101 FROM
102 PA_BUDGET_TYPES
103 WHERE BUDGET_TYPE_CODE = COST_BGT_CODE;
104 C_COST_BGT_CODE := COST_BGT_CODE;
105 C_COST_BGT_NAME := P_COST_BGT_TYPE;
106 END IF;
107 IF (REV_BGT_CODE IS NULL) THEN
108 SELECT
109 BUDGET_TYPE_CODE,
110 BUDGET_TYPE
111 INTO P_REV_BGT_CODE,P_REV_BGT_TYPE
112 FROM
113 PA_BUDGET_TYPES
114 WHERE BUDGET_AMOUNT_CODE = 'R'
115 AND PREDEFINED_FLAG = 'Y'
116 AND BUDGET_TYPE_CODE = 'AR';
117 C_REV_BGT_CODE := P_REV_BGT_CODE;
118 C_REV_BGT_NAME := P_REV_BGT_TYPE;
119 ELSE
120 SELECT
121 BUDGET_TYPE
122 INTO P_REV_BGT_TYPE
123 FROM
124 PA_BUDGET_TYPES
125 WHERE BUDGET_TYPE_CODE = REV_BGT_CODE;
126 C_REV_BGT_CODE := REV_BGT_CODE;
127 C_REV_BGT_NAME := P_REV_BGT_TYPE;
128 END IF;
129 EXCEPTION
130 WHEN OTHERS THEN
131 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
132 END;
133 RETURN (TRUE);
134 END BEFOREREPORT;
135
136 FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
137 L_NAME GL_SETS_OF_BOOKS.NAME%TYPE;
138 BEGIN
139 SELECT
140 GL.NAME
141 INTO L_NAME
142 FROM
143 GL_SETS_OF_BOOKS GL,
144 PA_IMPLEMENTATIONS PI
145 WHERE GL.SET_OF_BOOKS_ID = PI.SET_OF_BOOKS_ID;
146 C_COMPANY_NAME_HEADER := L_NAME;
147 RETURN (TRUE);
148 EXCEPTION
149 WHEN OTHERS THEN
150 RETURN (FALSE);
151 END GET_COMPANY_NAME;
152
153 FUNCTION NO_DATA_FOUND_FUNC RETURN BOOLEAN IS
154 MESSAGE_NAME VARCHAR2(80);
155 BEGIN
156 SELECT
157 MEANING
158 INTO MESSAGE_NAME
159 FROM
160 PA_LOOKUPS
161 WHERE LOOKUP_TYPE = 'MESSAGE'
162 AND LOOKUP_CODE = 'NO_DATA_FOUND';
163 C_NO_DATA_FOUND := MESSAGE_NAME;
164 RETURN (TRUE);
165 EXCEPTION
166 WHEN OTHERS THEN
167 RETURN (FALSE);
168 END NO_DATA_FOUND_FUNC;
169
170 FUNCTION GET_PERIOD(SD IN DATE) RETURN VARCHAR2 IS
171 PERNAME VARCHAR2(30);
172 ST_DATE DATE;
173 E_DATE DATE;
174 BEGIN
175 IF (PA_PERIOD IS NULL) THEN
176 SELECT
177 PERIOD_NAME,
178 START_DATE,
179 END_DATE
180 INTO PERNAME,ST_DATE,E_DATE
181 FROM
182 PA_PERIODS
183 WHERE TRUNC(SYSDATE) between TRUNC(START_DATE)
184 AND TRUNC(END_DATE);
185 ELSE
186 SELECT
187 PERIOD_NAME,
188 START_DATE,
189 END_DATE
190 INTO PERNAME,ST_DATE,E_DATE
191 FROM
192 PA_PERIODS
193 WHERE PERIOD_NAME = PA_PERIOD;
194 END IF;
195 C_START_DATE := to_char(ST_DATE,'DD-MON-YY');
196 C_END_DATE := to_char(E_DATE,'DD-MON-YY');
197 RETURN (PERNAME);
198 EXCEPTION
199 WHEN OTHERS THEN
200 RETURN (NULL);
201 END GET_PERIOD;
202
203 FUNCTION PERIOD_NAMEFORMULA RETURN VARCHAR2 IS
204 BEGIN
205 RETURN (GET_PERIOD(SYSDATE));
206 END PERIOD_NAMEFORMULA;
207
208 FUNCTION AFTERREPORT RETURN BOOLEAN IS
209 BEGIN
210 BEGIN
211 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
212 END;
213 RETURN (TRUE);
214 END AFTERREPORT;
215
216 FUNCTION C_TASK_ACTUAL_COSTFORMULA RETURN NUMBER IS
217 BEGIN
218 RETURN NULL;
219 END C_TASK_ACTUAL_COSTFORMULA;
220
221 FUNCTION C_ACT_PER_REVFORMULA(RESOURCE_LIST_MEMBER_ID IN NUMBER
222 ,TASK_ID3 IN NUMBER
223 ,PERIOD_NAME IN VARCHAR2
224 ,PROJECT_ID2 IN NUMBER) RETURN NUMBER IS
225 BEGIN
226 RETURN (GET_PERIOD_AMT('REVENUE'
227 ,RESOURCE_LIST_MEMBER_ID
228 ,TASK_ID3
229 ,PA_PERIOD
230 ,PROJECT_ID2));
231 END C_ACT_PER_REVFORMULA;
232
233 FUNCTION GET_PERIOD_AMT(X_TYPE IN VARCHAR2
234 ,X_RSRC_LIST_MEMBER_ID IN NUMBER
235 ,X_TASK_ID IN NUMBER
236 ,PERIOD_NAME IN VARCHAR2
237 ,PROJECT_ID2 IN NUMBER
238 ) RETURN NUMBER IS
239 X_REVENUE NUMBER;
240 X_RAW_COST NUMBER;
241 X_BURDENED_COST NUMBER;
242 X_QUANTITY NUMBER;
243 X_LABOR_HOURS NUMBER;
244 X_BILLABLE_RAW_COST NUMBER;
245 X_BILLABLE_BURDENED_COST NUMBER;
246 X_BILLABLE_QUANTITY NUMBER;
247 X_BILLABLE_LABOR_HOURS NUMBER;
248 X_CMT_RAW_COST NUMBER;
249 X_CMT_BURDENED_COST NUMBER;
250 X_UNIT_OF_MEASURE VARCHAR2(100);
251 X_ERR_STAGE VARCHAR2(100);
252 X_ERR_CODE NUMBER;
253 L_PRD_START_DATE DATE;
254 L_PRD_END_DATE DATE;
255 BEGIN
256 X_REVENUE := 0;
257 X_RAW_COST := 0;
258 X_BURDENED_COST := 0;
259 X_QUANTITY := 0;
260 X_LABOR_HOURS := 0;
261 X_BILLABLE_RAW_COST := 0;
262 X_BILLABLE_BURDENED_COST := 0;
263 X_BILLABLE_QUANTITY := 0;
264 X_BILLABLE_LABOR_HOURS := 0;
265 X_CMT_RAW_COST := 0;
266 X_CMT_BURDENED_COST := 0;
267 X_UNIT_OF_MEASURE := NULL;
268 X_ERR_STAGE := NULL;
269 X_ERR_CODE := 0;
270 L_PRD_START_DATE := NULL;
271 L_PRD_END_DATE := NULL;
272 SELECT
273 START_DATE,
274 END_DATE
275 INTO L_PRD_START_DATE,L_PRD_END_DATE
276 FROM
277 PA_PERIODS
278 WHERE PERIOD_NAME = GET_PERIOD_AMT.PERIOD_NAME
279 AND PERIOD_NAME IS NOT NULL;
280 PA_ACCUM_API.GET_PROJ_ACCUM_ACTUALS(PROJECT_ID2
281 ,X_TASK_ID
282 ,X_RSRC_LIST_MEMBER_ID
283 ,'P'
284 ,PERIOD_NAME
285 ,L_PRD_START_DATE
286 ,L_PRD_END_DATE
287 ,X_REVENUE
288 ,X_RAW_COST
289 ,X_BURDENED_COST
290 ,X_QUANTITY
291 ,X_LABOR_HOURS
292 ,X_BILLABLE_RAW_COST
293 ,X_BILLABLE_BURDENED_COST
294 ,X_BILLABLE_QUANTITY
295 ,X_BILLABLE_LABOR_HOURS
296 ,X_CMT_RAW_COST
297 ,X_CMT_BURDENED_COST
298 ,X_UNIT_OF_MEASURE
299 ,X_ERR_STAGE
300 ,X_ERR_CODE);
301 IF (X_ERR_CODE = 0) THEN
302 IF (X_TYPE = 'REVENUE') THEN
303 RETURN (X_REVENUE);
304 ELSE
305 RETURN (X_BURDENED_COST);
306 END IF;
307 ELSE
308 RETURN (-1);
309 END IF;
310 EXCEPTION
311 WHEN OTHERS THEN
312 RETURN (NULL);
313 END GET_PERIOD_AMT;
314
315 FUNCTION C_ACT_PER_COSTFORMULA(RESOURCE_LIST_MEMBER_ID1 IN NUMBER
316 ,TASK_ID5 IN NUMBER
317 ,PERIOD_NAME IN VARCHAR2
318 ,PROJECT_ID2 IN NUMBER) RETURN NUMBER IS
319 BEGIN
320 RETURN (GET_PERIOD_AMT('COST'
321 ,RESOURCE_LIST_MEMBER_ID1
322 ,TASK_ID5
323 ,PA_PERIOD
324 ,PROJECT_ID2));
325 END C_ACT_PER_COSTFORMULA;
326
327 FUNCTION C_ACT_PER_REV_SUMFORMULA(C_ACT_PER_REV IN NUMBER
328 ,MEMBER_LEVEL2 IN NUMBER) RETURN NUMBER IS
329 BEGIN
330 RETURN (GET_PERIOD_AMT_SUM(C_ACT_PER_REV
331 ,MEMBER_LEVEL2));
332 END C_ACT_PER_REV_SUMFORMULA;
333
334 FUNCTION GET_RSRC_NAME_DISP(X_ALIAS IN VARCHAR2
335 ,X_LEVEL IN NUMBER) RETURN VARCHAR2 IS
336 LNAME VARCHAR2(30);
337 BEGIN
338 LNAME := NULL;
339 LNAME := (LPAD(' '
340 ,2 * (X_LEVEL - 1)) || X_ALIAS);
341 RETURN (LNAME);
342 END GET_RSRC_NAME_DISP;
343
344 FUNCTION C_ACT_PER_COST_SUMFORMULA(C_ACT_PER_COST IN NUMBER
345 ,MEMBER_LEVEL1 IN NUMBER) RETURN NUMBER IS
346 BEGIN
347 RETURN (GET_PERIOD_AMT_SUM(C_ACT_PER_COST
348 ,MEMBER_LEVEL1));
349 END C_ACT_PER_COST_SUMFORMULA;
350
351 FUNCTION GET_PERIOD_AMT_SUM(X_AMT IN NUMBER
352 ,X_LEVEL IN NUMBER) RETURN NUMBER IS
353 BEGIN
354 IF (X_LEVEL = 1) THEN
355 RETURN (X_AMT);
356 ELSE
357 RETURN (0);
358 END IF;
359 END GET_PERIOD_AMT_SUM;
360
361 FUNCTION C_SUM_RPT_TOTALSFORMULA(PARENT_TASK_ID IN NUMBER) RETURN VARCHAR2 IS
362 BEGIN
363 RETURN (CHECK_SUM_RPT_TOTALS(PARENT_TASK_ID));
364 END C_SUM_RPT_TOTALSFORMULA;
365
366 FUNCTION CHECK_SUM_RPT_TOTALS(PARENT_TASK_ID IN NUMBER) RETURN VARCHAR2 IS
367 BEGIN
368 IF (TASK IS NULL) THEN
369 IF (PARENT_TASK_ID IS NULL) THEN
370 C_SUM_RPT_TOTALS:='YES';
371 RETURN ('YES');
372 ELSE
373 C_SUM_RPT_TOTALS:='NO';
374 RETURN ('NO');
375 END IF;
376 ELSE
377 C_SUM_RPT_TOTALS:='YES';
378 RETURN ('YES');
379 END IF;
380 END CHECK_SUM_RPT_TOTALS;
381
382 FUNCTION C_RPT_BGT_COST_SUMFORMULA(BGT_COST_SUM IN NUMBER) RETURN NUMBER IS
383 BEGIN
384 RETURN (GET_REPORT_LINE_TOTAL(BGT_COST_SUM));
385 END C_RPT_BGT_COST_SUMFORMULA;
386
387 FUNCTION GET_REPORT_LINE_TOTAL(X_AMOUNT IN NUMBER) RETURN NUMBER IS
388 BEGIN
389 IF (C_SUM_RPT_TOTALS = 'YES') THEN
390 RETURN (X_AMOUNT);
391 ELSE
392 RETURN (0);
393 END IF;
394 END GET_REPORT_LINE_TOTAL;
395
396 FUNCTION C_RPT_ACT_COST_SUMFORMULA(ACT_COST_SUM IN NUMBER) RETURN NUMBER IS
397 BEGIN
398 RETURN (GET_REPORT_LINE_TOTAL(ACT_COST_SUM));
399 END C_RPT_ACT_COST_SUMFORMULA;
400
401 FUNCTION C_RPT_ACT_PER_COST_SUMFORMULA(C_ACT_PER_COST_SUM IN NUMBER) RETURN NUMBER IS
402 BEGIN
403 RETURN (GET_REPORT_LINE_TOTAL(C_ACT_PER_COST_SUM));
404 END C_RPT_ACT_PER_COST_SUMFORMULA;
405
406 FUNCTION C_RPT_ACT_REV_SUMFORMULA(ACT_REV_SUM IN NUMBER) RETURN NUMBER IS
407 BEGIN
408 RETURN (GET_REPORT_LINE_TOTAL(ACT_REV_SUM));
409 END C_RPT_ACT_REV_SUMFORMULA;
410
411 FUNCTION C_RPT_ACT_PER_REV_SUMFORMULA(C_ACT_PER_REV_SUM IN NUMBER) RETURN NUMBER IS
412 BEGIN
413 RETURN (GET_REPORT_LINE_TOTAL(C_ACT_PER_REV_SUM));
414 END C_RPT_ACT_PER_REV_SUMFORMULA;
415
416 FUNCTION C_RPT_BGT_REV_SUMFORMULA(BGT_REV_SUM IN NUMBER) RETURN NUMBER IS
417 BEGIN
418 RETURN (GET_REPORT_LINE_TOTAL(BGT_REV_SUM));
419 END C_RPT_BGT_REV_SUMFORMULA;
420
421 FUNCTION CF_CURRENCY_CODEFORMULA RETURN CHAR IS
422 BEGIN
423 RETURN (PA_MULTI_CURRENCY.GET_ACCT_CURRENCY_CODE);
424 END CF_CURRENCY_CODEFORMULA;
425
426 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
427 BEGIN
428 RETURN C_COMPANY_NAME_HEADER;
429 END C_COMPANY_NAME_HEADER_P;
430
431 FUNCTION C_NO_DATA_FOUND_P RETURN VARCHAR2 IS
432 BEGIN
433 RETURN C_NO_DATA_FOUND;
434 END C_NO_DATA_FOUND_P;
435
436 FUNCTION C_ORG_P RETURN VARCHAR2 IS
437 BEGIN
438 RETURN C_ORG;
439 END C_ORG_P;
440
441 FUNCTION C_PROJ_NUM_P RETURN VARCHAR2 IS
442 BEGIN
443 RETURN C_PROJ_NUM;
444 END C_PROJ_NUM_P;
445
446 FUNCTION C_PROJ_NAME_P RETURN VARCHAR2 IS
447 BEGIN
448 RETURN C_PROJ_NAME;
449 END C_PROJ_NAME_P;
450
451 FUNCTION C_MGR_P RETURN VARCHAR2 IS
452 BEGIN
453 RETURN C_MGR;
454 END C_MGR_P;
455
456 FUNCTION C_START_DATE_P RETURN DATE IS
457 BEGIN
458 RETURN C_START_DATE;
459 END C_START_DATE_P;
460
461 FUNCTION C_END_DATE_P RETURN DATE IS
462 BEGIN
463 RETURN C_END_DATE;
464 END C_END_DATE_P;
465
466 FUNCTION C_COST_BGT_NAME_P RETURN VARCHAR2 IS
467 BEGIN
468 RETURN C_COST_BGT_NAME;
469 END C_COST_BGT_NAME_P;
470
471 FUNCTION C_REV_BGT_NAME_P RETURN VARCHAR2 IS
472 BEGIN
473 RETURN C_REV_BGT_NAME;
474 END C_REV_BGT_NAME_P;
475
476 FUNCTION C_COST_BGT_CODE_P RETURN VARCHAR2 IS
477 BEGIN
478 RETURN C_COST_BGT_CODE;
479 END C_COST_BGT_CODE_P;
480
481 FUNCTION C_REV_BGT_CODE_P RETURN VARCHAR2 IS
482 BEGIN
483 RETURN C_REV_BGT_CODE;
484 END C_REV_BGT_CODE_P;
485
486 FUNCTION C_TASK_NUM_P RETURN VARCHAR2 IS
487 BEGIN
488 RETURN C_TASK_NUM;
489 END C_TASK_NUM_P;
490
491 FUNCTION C_TASK_NAME_P RETURN VARCHAR2 IS
492 BEGIN
493 RETURN C_TASK_NAME;
494 END C_TASK_NAME_P;
495
496 END PA_PAXMGTSD_XMLP_PKG;
497
[Home] [Help]