1 PACKAGE BODY PA_PAXMGPSD_XMLP_PKG AS
2 /* $Header: PAXMGPSDB.pls 120.2 2008/01/03 12:12:39 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 ORG_ID 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 ORG_ID = ORGANIZATION_ID;
42 END IF;
43 C_ORG := P_ORG;
44 IF PROJECT_MANAGER_ID IS NOT NULL THEN
45 SELECT
46 FULL_NAME
47 INTO P_MGR
48 FROM
49 PER_PEOPLE_F
50 WHERE PROJECT_MANAGER_ID = PERSON_ID
51 AND sysdate between EFFECTIVE_START_DATE
52 AND NVL(EFFECTIVE_END_DATE
53 ,SYSDATE + 1)
54 AND ( CURRENT_NPW_FLAG = 'Y'
55 OR CURRENT_EMPLOYEE_FLAG = 'Y' )
56 AND DECODE(CURRENT_NPW_FLAG
57 ,'Y'
58 ,NPW_NUMBER
59 ,EMPLOYEE_NUMBER) IS NOT NULL;
60 END IF;
61 C_MGR := P_MGR;
62 IF PROJ IS NOT NULL THEN
63 SELECT
64 SEGMENT1,
65 NAME
66 INTO P_NUMBER,P_NAME
67 FROM
68 PA_PROJECTS
69 WHERE PROJ = PROJECT_ID;
70 END IF;
71 C_PROJ_NUM := P_NUMBER;
72 C_PROJ_NAME := P_NAME;
73 IF (GET_COMPANY_NAME <> TRUE) THEN
74 RAISE INIT_FAILURE;
75 END IF;
76 IF (NO_DATA_FOUND_FUNC <> TRUE) THEN
77 RAISE INIT_FAILURE;
78 END IF;
79 IF (COST_BGT_CODE IS NULL) THEN
80 SELECT
81 BUDGET_TYPE_CODE,
82 BUDGET_TYPE
83 INTO P_COST_BGT_CODE,P_COST_BGT_TYPE
84 FROM
85 PA_BUDGET_TYPES
86 WHERE BUDGET_AMOUNT_CODE = 'C'
87 AND PREDEFINED_FLAG = 'Y'
88 AND BUDGET_TYPE_CODE = 'AC';
89 C_COST_BGT_CODE := P_COST_BGT_CODE;
90 C_COST_BGT_NAME := P_COST_BGT_TYPE;
91 ELSE
92 SELECT
93 BUDGET_TYPE
94 INTO P_COST_BGT_TYPE
95 FROM
96 PA_BUDGET_TYPES
97 WHERE BUDGET_TYPE_CODE = COST_BGT_CODE;
98 C_COST_BGT_CODE := COST_BGT_CODE;
99 C_COST_BGT_NAME := P_COST_BGT_TYPE;
100 END IF;
101 IF (REV_BGT_CODE IS NULL) THEN
102 SELECT
103 BUDGET_TYPE_CODE,
104 BUDGET_TYPE
105 INTO P_REV_BGT_CODE,P_REV_BGT_TYPE
106 FROM
107 PA_BUDGET_TYPES
108 WHERE BUDGET_AMOUNT_CODE = 'R'
109 AND PREDEFINED_FLAG = 'Y'
110 AND BUDGET_TYPE_CODE = 'AR';
111 C_REV_BGT_CODE := P_REV_BGT_CODE;
112 C_REV_BGT_NAME := P_REV_BGT_TYPE;
113 ELSE
114 SELECT
115 BUDGET_TYPE
116 INTO P_REV_BGT_TYPE
117 FROM
118 PA_BUDGET_TYPES
119 WHERE BUDGET_TYPE_CODE = REV_BGT_CODE;
120 C_REV_BGT_CODE := REV_BGT_CODE;
121 C_REV_BGT_NAME := P_REV_BGT_TYPE;
122 END IF;
123 EXCEPTION
124 WHEN OTHERS THEN
125 /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
126 END;
127 RETURN (TRUE);
128 END BEFOREREPORT;
129
130 FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
131 L_NAME GL_SETS_OF_BOOKS.NAME%TYPE;
132 BEGIN
133 SELECT
134 GL.NAME
135 INTO L_NAME
136 FROM
137 GL_SETS_OF_BOOKS GL,
138 PA_IMPLEMENTATIONS PI
139 WHERE GL.SET_OF_BOOKS_ID = PI.SET_OF_BOOKS_ID;
140 C_COMPANY_NAME_HEADER := L_NAME;
141 RETURN (TRUE);
142 EXCEPTION
143 WHEN OTHERS THEN
144 RETURN (FALSE);
145 END GET_COMPANY_NAME;
146
147 FUNCTION NO_DATA_FOUND_FUNC RETURN BOOLEAN IS
148 MESSAGE_NAME VARCHAR2(80);
149 BEGIN
150 SELECT
151 MEANING
152 INTO MESSAGE_NAME
153 FROM
154 PA_LOOKUPS
155 WHERE LOOKUP_TYPE = 'MESSAGE'
156 AND LOOKUP_CODE = 'NO_DATA_FOUND';
157 C_NO_DATA_FOUND := MESSAGE_NAME;
158 RETURN (TRUE);
159 EXCEPTION
160 WHEN OTHERS THEN
161 RETURN (FALSE);
162 END NO_DATA_FOUND_FUNC;
163
164 FUNCTION GET_PERIOD(SD IN DATE) RETURN VARCHAR2 IS
165 PERNAME VARCHAR2(30);
166 ST_DATE DATE;
167 E_DATE DATE;
168 BEGIN
169 IF (PA_PERIOD IS NULL) THEN
170 SELECT
171 PERIOD_NAME,
172 START_DATE,
173 END_DATE
174 INTO PERNAME,ST_DATE,E_DATE
175 FROM
176 PA_PERIODS
177 WHERE TRUNC(SYSDATE) between TRUNC(START_DATE)
178 AND TRUNC(END_DATE);
179 ELSE
180 SELECT
181 PERIOD_NAME,
182 START_DATE,
183 END_DATE
184 INTO PERNAME,ST_DATE,E_DATE
185 FROM
186 PA_PERIODS
187 WHERE PERIOD_NAME = PA_PERIOD;
188 END IF;
189 C_START_DATE := ST_DATE;
190 C_END_DATE := E_DATE;
191 RETURN (PERNAME);
192 EXCEPTION
193 WHEN OTHERS THEN
194 RETURN (NULL);
195 END GET_PERIOD;
196
197 FUNCTION PERIOD_NAMEFORMULA RETURN VARCHAR2 IS
198 BEGIN
199 RETURN (GET_PERIOD(SYSDATE));
200 END PERIOD_NAMEFORMULA;
201
202 FUNCTION UNBILLED_RECEIVABLESFORMULA(RETN_ACCOUNTING_FLAG IN VARCHAR2
203 ,TOTAL_REVENUE_AMOUNT IN NUMBER
204 ,PFC_TOTAL_INVOICE_AMOUNT IN NUMBER
205 ,UNBILLED_RETENTION IN NUMBER) RETURN NUMBER IS
206 BEGIN
207 IF (RETN_ACCOUNTING_FLAG = 'Y') THEN
208 RETURN (GREATEST((TOTAL_REVENUE_AMOUNT - PFC_TOTAL_INVOICE_AMOUNT)
209 ,0));
210 ELSE
211 RETURN (GREATEST((TOTAL_REVENUE_AMOUNT - PFC_TOTAL_INVOICE_AMOUNT - UNBILLED_RETENTION)
212 ,0));
213 END IF;
214 END UNBILLED_RECEIVABLESFORMULA;
215
216 FUNCTION UNEARNED_REVENUEFORMULA(RETN_ACCOUNTING_FLAG IN VARCHAR2
217 ,PFC_TOTAL_INVOICE_AMOUNT IN NUMBER
218 ,TOTAL_REVENUE_AMOUNT IN NUMBER
219 ,UNBILLED_RETENTION IN NUMBER) RETURN NUMBER IS
220 BEGIN
221 IF (RETN_ACCOUNTING_FLAG = 'Y') THEN
222 RETURN (GREATEST((PFC_TOTAL_INVOICE_AMOUNT - TOTAL_REVENUE_AMOUNT)
223 ,0));
224 ELSE
225 RETURN (GREATEST((PFC_TOTAL_INVOICE_AMOUNT + UNBILLED_RETENTION - TOTAL_REVENUE_AMOUNT)
226 ,0));
227 END IF;
228 END UNEARNED_REVENUEFORMULA;
229
230 FUNCTION AFTERREPORT RETURN BOOLEAN IS
231 BEGIN
232 BEGIN
233 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
234 END;
235 RETURN (TRUE);
236 END AFTERREPORT;
237
238 FUNCTION C_PROJECT_ACTUAL_COSTFORMULA RETURN NUMBER IS
239 BEGIN
240 RETURN NULL;
241 END C_PROJECT_ACTUAL_COSTFORMULA;
242
243 FUNCTION C_ACT_PER_REVFORMULA(RESOURCE_LIST_MEMBER_ID IN NUMBER
244 ,PERIOD_NAME IN VARCHAR2
245 ,PROJECT_ID7 IN NUMBER) RETURN NUMBER IS
246 BEGIN
247 RETURN (GET_PERIOD_AMT('REVENUE'
248 ,resource_list_member_id
249 ,PROJECT_ID7
250 ,PERIOD_NAME));
251 END C_ACT_PER_REVFORMULA;
252
253 FUNCTION GET_PERIOD_AMT(X_TYPE IN VARCHAR2
254 ,X_RSRC_LIST_MEMBER_ID IN NUMBER,project_id7 IN NUMBER,PERIOD_NAME IN VARCHAR2) RETURN NUMBER IS
255 X_REVENUE NUMBER;
256 X_RAW_COST NUMBER;
257 X_BURDENED_COST NUMBER;
258 X_QUANTITY NUMBER;
259 X_LABOR_HOURS NUMBER;
260 X_BILLABLE_RAW_COST NUMBER;
261 X_BILLABLE_BURDENED_COST NUMBER;
262 X_BILLABLE_QUANTITY NUMBER;
263 X_BILLABLE_LABOR_HOURS NUMBER;
264 X_CMT_RAW_COST NUMBER;
265 X_CMT_BURDENED_COST NUMBER;
266 X_UNIT_OF_MEASURE VARCHAR2(100);
267 X_ERR_STAGE VARCHAR2(100);
268 X_ERR_CODE NUMBER;
269 L_PRD_START_DATE DATE;
270 L_PRD_END_DATE DATE;
271 BEGIN
272 X_REVENUE := 0;
273 X_RAW_COST := 0;
274 X_BURDENED_COST := 0;
275 X_QUANTITY := 0;
276 X_LABOR_HOURS := 0;
277 X_BILLABLE_RAW_COST := 0;
278 X_BILLABLE_BURDENED_COST := 0;
279 X_BILLABLE_QUANTITY := 0;
280 X_BILLABLE_LABOR_HOURS := 0;
281 X_CMT_RAW_COST := 0;
282 X_CMT_BURDENED_COST := 0;
283 X_UNIT_OF_MEASURE := NULL;
284 X_ERR_STAGE := NULL;
285 X_ERR_CODE := 0;
286 L_PRD_START_DATE := C_START_DATE;
287 L_PRD_END_DATE := C_END_DATE;
288 PA_ACCUM_API.GET_PROJ_ACCUM_ACTUALS(PROJECT_ID7
289 ,NULL
290 ,X_RSRC_LIST_MEMBER_ID
291 ,'P'
292 ,PERIOD_NAME
293 ,L_PRD_START_DATE
294 ,L_PRD_END_DATE
295 ,X_REVENUE
296 ,X_RAW_COST
297 ,X_BURDENED_COST
298 ,X_QUANTITY
299 ,X_LABOR_HOURS
300 ,X_BILLABLE_RAW_COST
301 ,X_BILLABLE_BURDENED_COST
302 ,X_BILLABLE_QUANTITY
303 ,X_BILLABLE_LABOR_HOURS
304 ,X_CMT_RAW_COST
305 ,X_CMT_BURDENED_COST
306 ,X_UNIT_OF_MEASURE
307 ,X_ERR_STAGE
308 ,X_ERR_CODE);
309 IF (X_ERR_CODE = 0) THEN
310 IF (X_TYPE = 'REVENUE') THEN
311 RETURN (X_REVENUE);
312 ELSE
313 RETURN (X_BURDENED_COST);
314 END IF;
315 ELSE
316 RETURN (-1);
317 END IF;
318 EXCEPTION
319 WHEN OTHERS THEN
320 RETURN (NULL);
321 END GET_PERIOD_AMT;
322
323 FUNCTION C_ACT_PER_COSTFORMULA(RESOURCE_LIST_MEMBER_ID1 IN NUMBER
324 ,PERIOD_NAME IN VARCHAR2
325 ,PROJECT_ID7 IN NUMBER) RETURN NUMBER IS
326 BEGIN
327 RETURN (GET_PERIOD_AMT('COST'
328 ,resource_list_member_id1
329 ,PROJECT_ID7
330 ,PERIOD_NAME));
331 END C_ACT_PER_COSTFORMULA;
332
333 FUNCTION C_ACT_PER_REV_SUMFORMULA(C_ACT_PER_REV IN NUMBER
334 ,MEMBER_LEVEL2 IN NUMBER) RETURN NUMBER IS
335 BEGIN
336 RETURN (GET_PERIOD_AMT_SUM(C_ACT_PER_REV
337 ,MEMBER_LEVEL2));
338 END C_ACT_PER_REV_SUMFORMULA;
339
340 FUNCTION GET_RSRC_NAME_DISP(X_ALIAS IN VARCHAR2
341 ,X_LEVEL IN NUMBER) RETURN VARCHAR2 IS
342 LNAME VARCHAR2(30);
343 BEGIN
344 LNAME := NULL;
345 LNAME := (LPAD(' '
346 ,2 * (X_LEVEL - 1)) || X_ALIAS);
347 RETURN (LNAME);
348 END GET_RSRC_NAME_DISP;
349
350 FUNCTION C_ACT_PER_COST_SUMFORMULA(C_ACT_PER_COST IN NUMBER
351 ,MEMBER_LEVEL1 IN NUMBER) RETURN NUMBER IS
352 BEGIN
353 RETURN (GET_PERIOD_AMT_SUM(C_ACT_PER_COST
354 ,MEMBER_LEVEL1));
355 END C_ACT_PER_COST_SUMFORMULA;
356
357 FUNCTION GET_PERIOD_AMT_SUM(X_AMT IN NUMBER
358 ,X_LEVEL IN NUMBER) RETURN NUMBER IS
359 BEGIN
360 IF (X_LEVEL = 1) THEN
361 RETURN (X_AMT);
362 ELSE
363 RETURN (0);
364 END IF;
365 END GET_PERIOD_AMT_SUM;
366
367 FUNCTION CF_CURRENCY_CODEFORMULA RETURN VARCHAR2 IS
368 BEGIN
369 RETURN (PA_MULTI_CURRENCY.GET_ACCT_CURRENCY_CODE);
370 END CF_CURRENCY_CODEFORMULA;
371
372 FUNCTION UNBILLED_RETNFORMULA(RETN_ACCOUNTING_FLAG IN VARCHAR2
373 ,UNBILLED_RETENTION IN NUMBER) RETURN NUMBER IS
374 BEGIN
375 IF (RETN_ACCOUNTING_FLAG = 'Y') THEN
376 RETURN (-UNBILLED_RETENTION);
377 ELSE
378 RETURN (0);
379 END IF;
380 END UNBILLED_RETNFORMULA;
381
382 FUNCTION AFTERPFORM RETURN BOOLEAN IS
383 BEGIN
384 IF PROJ IS NOT NULL THEN
385 P_PROJECT := 'AND p.project_id = :proj ';
386 ELSE
387 P_PROJECT := ' AND 1=1';
388 END IF;
389 IF PROJ_TYPE IS NOT NULL THEN
390 P_PROJECT_TYPE := 'AND p.project_type = :proj_type';
391 ELSE
392 P_PROJECT_TYPE := 'AND 1=1';
393 END IF;
394 IF PROJECT_MANAGER_ID IS NOT NULL THEN
395 P_PROJECT_MANAGER := 'AND NVL(p.manager_person_id,-999) =:project_manager_id';
396 ELSE
397 P_PROJECT_MANAGER := 'AND 1=1 ';
398 END IF;
399 RETURN (TRUE);
400 END AFTERPFORM;
401
402 FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
403 BEGIN
404 RETURN C_COMPANY_NAME_HEADER;
405 END C_COMPANY_NAME_HEADER_P;
406
407 FUNCTION C_NO_DATA_FOUND_P RETURN VARCHAR2 IS
408 BEGIN
409 RETURN C_NO_DATA_FOUND;
410 END C_NO_DATA_FOUND_P;
411
412 FUNCTION C_ORG_P RETURN VARCHAR2 IS
413 BEGIN
414 RETURN C_ORG;
415 END C_ORG_P;
416
417 FUNCTION C_PROJ_NUM_P RETURN VARCHAR2 IS
418 BEGIN
419 RETURN C_PROJ_NUM;
420 END C_PROJ_NUM_P;
421
422 FUNCTION C_PROJ_NAME_P RETURN VARCHAR2 IS
423 BEGIN
424 RETURN C_PROJ_NAME;
425 END C_PROJ_NAME_P;
426
427 FUNCTION C_MGR_P RETURN VARCHAR2 IS
428 BEGIN
429 RETURN C_MGR;
430 END C_MGR_P;
431
432 FUNCTION C_START_DATE_P RETURN DATE IS
433 BEGIN
434 RETURN C_START_DATE;
435 END C_START_DATE_P;
436
437 FUNCTION C_END_DATE_P RETURN DATE IS
438 BEGIN
439 RETURN C_END_DATE;
440 END C_END_DATE_P;
441
442 FUNCTION C_COST_BGT_NAME_P RETURN VARCHAR2 IS
443 BEGIN
444 RETURN C_COST_BGT_NAME;
445 END C_COST_BGT_NAME_P;
446
447 FUNCTION C_REV_BGT_NAME_P RETURN VARCHAR2 IS
448 BEGIN
449 RETURN C_REV_BGT_NAME;
450 END C_REV_BGT_NAME_P;
451
452 FUNCTION C_COST_BGT_CODE_P RETURN VARCHAR2 IS
453 BEGIN
454 RETURN C_COST_BGT_CODE;
455 END C_COST_BGT_CODE_P;
456
457 FUNCTION C_REV_BGT_CODE_P RETURN VARCHAR2 IS
458 BEGIN
459 RETURN C_REV_BGT_CODE;
460 END C_REV_BGT_CODE_P;
461
462 END PA_PAXMGPSD_XMLP_PKG;
463
[Home] [Help]