[Home] [Help]
PACKAGE BODY: APPS.PA_ACCUM_PROJ_LIST
Source
1 PACKAGE BODY pa_accum_proj_list AS
2 --$Header: PAPRJACB.pls 120.2.12010000.2 2008/09/24 11:06:36 admarath ship $
3
4 --
5 -- Name: Insert_Accum
6 -- Type: PL/SQL Procedure
7 --
8 -- Description: For a given project, this procedure inserts one project-level row
9 -- for each of the following summarization tables:
10 -- 1) pa_project_accum_headers
11 -- 2) pa_project_accum_actuals
12 -- 3) pa_project_accum_commitments
13 --
14 -- For the pa_project_accum_budgets, one row each is inserted for
15 -- budget_type_codes AC and AR (Approved Cost and Approved Revenue,
16 -- respectively).
17 --
18 -- Rows are only inserted if they do not already exist.
19 -- Zeros are populated for all amount columns.
20 --
21 -- This API does not peform any validation. Error messaging
22 -- is limited to the first ORA error encountered. If ORA errors
23 -- are not encountered, then x_return_status returns S(uccess).
24 --
25 -- Note:
26 -- This API assumes that the appropriate
27 -- dbms_application_info.set_client_info(org_id) and
28 -- responsibility and userid FND_GLOBALS environment has
29 -- set up prior to running this API.
30 --
31
32 --
33 -- Called Subprograms: None.
34 --
35 -- History:
36 -- 31-OCT-2001 jwhite Created.
37 --
38 g_org_id NUMBER ;
39
40 PROCEDURE Insert_Accum
41 (p_project_id IN NUMBER
42 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
43 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
44 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
45 )
46
47 IS
48
49
50 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Accum';
51 l_project_id NUMBER := 0;
52 l_project_accum_id pa_project_accum_headers.project_accum_id%TYPE := 0;
53 l_accum_period_type pa_implementations_all.accumulation_period_type%TYPE := NULL;
54 l_period_name pa_periods_all.period_name%TYPE := NULL;
55
56 l_err_code NUMBER := 0;
57 l_err_stage VARCHAR2(2000) := NULL;
58 l_err_stack VARCHAR2(2000) := NULL;
59
60
61 CURSOR header_csr
62 IS
63 SELECT project_accum_id
64 FROM pa_project_accum_headers
65 WHERE project_id = l_project_id
66 AND task_id = 0
67 AND resource_list_member_id = 0;
68
69 CURSOR periodtype_csr
70 IS
71 SELECT accumulation_period_type
72 FROM pa_implementations_all
73 where org_id = g_org_id; /*removed nvl() from nvl(org_id,-99) for bug 6327647*/
74
75 CURSOR currperiod_csr
76 IS
77 SELECT decode(l_accum_period_type, 'GL', GL_PERIOD_NAME, PERIOD_NAME)
78 FROM pa_periods_all
79 WHERE current_pa_period_flag = 'Y'
80 and org_id = g_org_id; /*removed nvl() from nvl(org_id,-99) for bug 6327647*/
81
82
83
84 BEGIN
85
86 SAVEPOINT Insert_Accum_Pvt;
87
88 select NVL(org_id,-99) into g_org_id
89 from pa_projects_all
90 where project_id = p_project_id; /*4704130 */
91
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93
94 l_project_id := p_project_id;
95
96
97 -- Create Header Project-Level Header Record, if NOT Already Exists --------------
98 -- Most of the time, this API will be called from Copy_Project. So, the
99 -- project-level header record will not already exist.
100
101 -- First, Get Necessary Input Parameters for Record Creation.
102 -- These queries should always return result sets, providing this API is run
103 -- under the appropriate dbms_application_info.set_client_info(org_id) environment.
104
105 OPEN periodtype_csr;
106 FETCH periodtype_csr INTO l_accum_period_type;
107 CLOSE periodtype_csr;
108
109 OPEN currperiod_csr;
110 FETCH currperiod_csr INTO l_period_name;
111 CLOSE currperiod_csr;
112
113 SELECT PA_PROJECT_ACCUM_HEADERS_S.Nextval
114 INTO l_project_accum_id
115 FROM dual;
116
117 -- Insert Header Record
118
119 Insert into PA_PROJECT_ACCUM_HEADERS
120 (PROJECT_ACCUM_ID,PROJECT_ID,TASK_ID,ACCUM_PERIOD,RESOURCE_ID,
121 RESOURCE_LIST_ID,RESOURCE_LIST_ASSIGNMENT_ID,
122 RESOURCE_LIST_MEMBER_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,
123 CREATION_DATE,REQUEST_ID,CREATED_BY,LAST_UPDATE_LOGIN )
124 SELECT l_project_accum_id,l_project_id,0,
125 l_period_name,
126 0,0,0,0,G_last_updated_by,Trunc(sysdate),trunc(sysdate),
127 G_request_id,G_created_by,
128 G_last_update_login
129 FROM dual
130 WHERE NOT EXISTS (select 'X'
131 from pa_project_accum_headers
132 where project_id = l_project_id
133 AND task_id = 0
134 AND resource_list_member_id = 0
135 );
136
137 IF (SQL%ROWCOUNT < 1)
138 THEN
139 -- A Header Row Was NOT Created Becuase the Header Already Exists.
140 -- So, find the project_accum_id of the existing record and process the detail records.
141 OPEN header_csr;
142 FETCH header_csr INTO l_project_accum_id;
143 CLOSE header_csr;
144 END IF;
145
146
147
148 -- As Necessary, Create Detail Records -------------------------
149
150 -- Actuals Record, If NOT Already Exist
151
152 Insert into PA_PROJECT_ACCUM_ACTUALS (
153 PROJECT_ACCUM_ID,RAW_COST_ITD,RAW_COST_YTD,RAW_COST_PP,RAW_COST_PTD,
154 BILLABLE_RAW_COST_ITD,BILLABLE_RAW_COST_YTD,BILLABLE_RAW_COST_PP,
155 BILLABLE_RAW_COST_PTD,BURDENED_COST_ITD,BURDENED_COST_YTD,
156 BURDENED_COST_PP,BURDENED_COST_PTD,BILLABLE_BURDENED_COST_ITD,
157 BILLABLE_BURDENED_COST_YTD,BILLABLE_BURDENED_COST_PP,
158 BILLABLE_BURDENED_COST_PTD,QUANTITY_ITD,QUANTITY_YTD,QUANTITY_PP,
159 QUANTITY_PTD,LABOR_HOURS_ITD,LABOR_HOURS_YTD,LABOR_HOURS_PP,
160 LABOR_HOURS_PTD,BILLABLE_QUANTITY_ITD,BILLABLE_QUANTITY_YTD,
161 BILLABLE_QUANTITY_PP,BILLABLE_QUANTITY_PTD,
162 BILLABLE_LABOR_HOURS_ITD,BILLABLE_LABOR_HOURS_YTD,
163 BILLABLE_LABOR_HOURS_PP,BILLABLE_LABOR_HOURS_PTD,REVENUE_ITD,
164 REVENUE_YTD,REVENUE_PP,REVENUE_PTD,TXN_UNIT_OF_MEASURE,
165 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
166 LAST_UPDATE_LOGIN) SELECT
167 l_project_accum_id,0,0,0,0,
168 0,0,0,
169 0,0,0,
170 0,0,0,
171 0,0,0,
172 0,0,0,0,0,0,0,0,
173 0,0,0,0,
174 0,0,0,
175 0,0,0,0,
176 0,NULL,G_request_id,G_last_updated_by,Trunc(sysdate),
177 Trunc(Sysdate),G_created_by,G_last_update_login
178 FROM dual
179 WHERE NOT EXISTS (select 'X'
180 from pa_project_accum_actuals
181 where project_accum_id = l_project_accum_id
182 );
183
184
185 -- Commitments Record, If NOT Already Exist
186
187 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
188 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
189 CMT_RAW_COST_PTD,
190 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
191 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
192 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,
193 CMT_QUANTITY_PP,CMT_QUANTITY_PTD,
194 CMT_UNIT_OF_MEASURE,
195 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
196 LAST_UPDATE_LOGIN)
197 SELECT l_project_accum_id,
198 0,
199 0,
200 0,
201 0,
202 0,
203 0,
204 0,
205 0,
206 0,
207 0,
208 0,
209 0,
210 NULL,G_request_id,G_last_updated_by,Trunc(sysdate),
211 Trunc(Sysdate),G_created_by,
212 G_last_update_login
213 FROM dual
214 WHERE NOT EXISTS (select 'X'
215 from pa_project_accum_commitments
216 where project_accum_id = l_project_accum_id
217 );
218
219 -- Approved Cost Budget Record, If NOT Already Exist
220
221 Insert into PA_PROJECT_ACCUM_BUDGETS (
222 PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
223 BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
224 BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
225 BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
226 ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
227 ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
228 ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
229 ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
230 BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
231 BASE_QUANTITY_PTD,
232 ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
233 ORIG_QUANTITY_PTD,
234 BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
235 BASE_LABOR_HOURS_PTD,
236 ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
237 ORIG_LABOR_HOURS_PTD,
238 BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
239 ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
240 BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
241 BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
242 ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
243 BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
244 ORIG_QUANTITY_TOT,
245 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
246 LAST_UPDATE_LOGIN)
247 SELECT l_project_accum_id,'AC',
248 0,0,0,0,
249 0,0,
250 0,0,
251 0,0,0,0,
252 0,0,
253 0,0,
254 0,0,0,0,
255 0,0,0,0,
256 0,0,0,0,
257 0,0,0,0,
258 0,0,0,0,
259 0,0,0,0,
260 NULL,NULL,
261 0,0,0,0,0,0,0,0,0,0,
262 G_request_id,G_last_updated_by,Trunc(sysdate),
263 Trunc(Sysdate),G_created_by,G_last_update_login
264 FROM dual
265 WHERE NOT EXISTS (select 'X'
266 from pa_project_accum_budgets
267 where project_accum_id = l_project_accum_id
268 and budget_type_code = 'AC'
269 );
270
271
272 -- Approved Revenue Budget Record, If NOT Already Exist
273
274 Insert into PA_PROJECT_ACCUM_BUDGETS (
275 PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
276 BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
277 BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
278 BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
279 ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
280 ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
281 ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
282 ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
283 BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
284 BASE_QUANTITY_PTD,
285 ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
286 ORIG_QUANTITY_PTD,
287 BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
288 BASE_LABOR_HOURS_PTD,
289 ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
290 ORIG_LABOR_HOURS_PTD,
291 BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
292 ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
293 BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
294 BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
298 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
295 ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
296 BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
297 ORIG_QUANTITY_TOT,
299 LAST_UPDATE_LOGIN)
300 SELECT l_project_accum_id,'AR',
301 0,0,0,0,
302 0,0,
303 0,0,
304 0,0,0,0,
305 0,0,
306 0,0,
307 0,0,0,0,
308 0,0,0,0,
309 0,0,0,0,
310 0,0,0,0,
311 0,0,0,0,
312 0,0,0,0,
313 NULL,NULL,
314 0,0,0,0,0,0,0,0,0,0,
315 G_request_id,G_last_updated_by,Trunc(sysdate),
316 Trunc(Sysdate),G_created_by,G_last_update_login
317 FROM dual
318 WHERE NOT EXISTS (select 'X'
319 from pa_project_accum_budgets
320 where project_accum_id = l_project_accum_id
321 and budget_type_code = 'AR'
322 );
323
324
325
326
327
328 EXCEPTION
329
330
331 WHEN dup_val_on_index THEN
332 null; -- OK if dup record exists.
333
334 WHEN OTHERS THEN
335 x_msg_count := 1;
336 x_msg_data := SUBSTR(SQLERRM, 1, 240);
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
338 ROLLBACK TO Insert_Accum_Pvt;
339 FND_MSG_PUB.add_Exc_msg(
340 p_pkg_name => G_PKG_NAME,
341 p_procedure_name => l_api_name);
342
343
344
345 END Insert_Accum;
346
347
348 --
349 -- Name: Upgrade_Accum
350 -- Type: PL/SQL Procedure
351 --
352 -- Description: For a given project, this procedure inserts one project-level row
353 -- for each of the following summarization tables:
354 -- 1) pa_project_accum_headers
355 -- 2) pa_project_accum_actuals
356 -- 3) pa_project_accum_commitments
357 --
358 -- For the pa_project_accum_budgets, one row each is inserted for
359 -- budget_type_codes AC and AR (Approved Cost and Approved Revenue,
360 -- respectively).
361 --
362 -- Rows are only inserted if they do not already exist.
363 -- Zeros are populated for all amount columns.
364 --
365 -- This API does not peform any validation. Error messaging
366 -- is limited to the first ORA error encountered. If ORA errors
367 -- are not encountered, then x_return_status returns S(uccess).
368 --
369 -- Note:
370 -- This API assumes that the appropriate
371 -- dbms_application_info.set_client_info(org_id) and
372 -- responsibility and userid FND_GLOBALS environment has
373 -- set up prior to running this API.
374 --
375
376 --
377 -- Called Subprograms: None.
378 --
379 -- History:
380 -- 31-OCT-2001 jwhite Created.
381 --
382
383 PROCEDURE Upgrade_Accum
384 (p_project_id IN NUMBER
385 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
386 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
387 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
388 )
389
390 IS
391
392
393 l_api_name CONSTANT VARCHAR2(30) := 'Upgrade_Accum';
394 l_project_id NUMBER := 0;
395 l_project_accum_id pa_project_accum_headers.project_accum_id%TYPE := 0;
396 l_accum_period_type pa_implementations_all.accumulation_period_type%TYPE := NULL;
397 l_period_name pa_periods_all.period_name%TYPE := NULL;
398
399 l_err_code NUMBER := 0;
400 l_err_stage VARCHAR2(2000) := NULL;
401 l_err_stack VARCHAR2(2000) := NULL;
402
403
404 CURSOR header_csr
405 IS
406 SELECT project_accum_id
407 FROM pa_project_accum_headers
408 WHERE project_id = l_project_id
409 AND task_id = 0
410 AND resource_list_member_id = 0;
411
412 CURSOR periodtype_csr
413 IS
414 SELECT accumulation_period_type
415 FROM pa_implementations_all
416 where org_id = g_org_id; /*removed nvl() from nvl(org_id,-99) for bug 6327647*/
417
418
419
420 CURSOR currperiod_csr
421 IS
422 SELECT decode(l_accum_period_type, 'GL', GL_PERIOD_NAME, PERIOD_NAME)
423 FROM pa_periods_all
424 WHERE current_pa_period_flag = 'Y'
425 and org_id = g_org_id; /*removed nvl() from nvl(org_id,-99) for bug 6327647*/
426
427
428
429 BEGIN
430
431 select NVL(org_id,-99) into g_org_id
432 from pa_projects_all
433 where project_id = p_project_id;
434
435 SAVEPOINT Upgrade_Accum_Pvt;
436
437 x_return_status := FND_API.G_RET_STS_SUCCESS;
438
439 l_project_id := p_project_id;
440
441
442 -- Does the Project-Level Row Already Exist for the Input Project?
443 -- If NOT, then create it. Otherwise, proceed with creation of
444 -- detail records.
445
446 OPEN header_csr;
450
447 FETCH header_csr INTO l_project_accum_id;
448 IF (header_csr%NOTFOUND)
449 THEN
451 -- Create Header Project-Level Header Record ------------------
452
453 -- First, Get Necessary Input Parameters for Record Creation.
454 -- These queries should always return result sets, providing this API is run
455 -- under the appropriate dbms_application_info.set_client_info(org_id) environment.
456
457 OPEN periodtype_csr;
458 FETCH periodtype_csr INTO l_accum_period_type;
459 CLOSE periodtype_csr;
460
461 OPEN currperiod_csr;
462 FETCH currperiod_csr INTO l_period_name;
463 CLOSE currperiod_csr;
464
465 SELECT PA_PROJECT_ACCUM_HEADERS_S.Nextval
466 INTO l_project_accum_id
467 FROM dual;
468 -- Insert Header Record
469
470 Insert into PA_PROJECT_ACCUM_HEADERS
471 (PROJECT_ACCUM_ID,PROJECT_ID,TASK_ID,ACCUM_PERIOD,RESOURCE_ID,
472 RESOURCE_LIST_ID,RESOURCE_LIST_ASSIGNMENT_ID,
473 RESOURCE_LIST_MEMBER_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,
474 CREATION_DATE,REQUEST_ID,CREATED_BY,LAST_UPDATE_LOGIN )
475 Values (l_project_accum_id,l_project_id,0,
476 l_period_name,
477 0,0,0,0,G_last_updated_by,Trunc(sysdate),trunc(sysdate),
478 G_request_id,G_created_by,
479 G_last_update_login);
480
481
482 END IF; -- (header_csr%NOTFOUND)
483
484 CLOSE header_csr;
485
486
487 -- As Necessary, Create Detail Records -------------------------
488
489 -- Actuals Record, If NOT Already Exist
490
491 Insert into PA_PROJECT_ACCUM_ACTUALS (
492 PROJECT_ACCUM_ID,RAW_COST_ITD,RAW_COST_YTD,RAW_COST_PP,RAW_COST_PTD,
493 BILLABLE_RAW_COST_ITD,BILLABLE_RAW_COST_YTD,BILLABLE_RAW_COST_PP,
494 BILLABLE_RAW_COST_PTD,BURDENED_COST_ITD,BURDENED_COST_YTD,
495 BURDENED_COST_PP,BURDENED_COST_PTD,BILLABLE_BURDENED_COST_ITD,
496 BILLABLE_BURDENED_COST_YTD,BILLABLE_BURDENED_COST_PP,
497 BILLABLE_BURDENED_COST_PTD,QUANTITY_ITD,QUANTITY_YTD,QUANTITY_PP,
498 QUANTITY_PTD,LABOR_HOURS_ITD,LABOR_HOURS_YTD,LABOR_HOURS_PP,
499 LABOR_HOURS_PTD,BILLABLE_QUANTITY_ITD,BILLABLE_QUANTITY_YTD,
500 BILLABLE_QUANTITY_PP,BILLABLE_QUANTITY_PTD,
501 BILLABLE_LABOR_HOURS_ITD,BILLABLE_LABOR_HOURS_YTD,
502 BILLABLE_LABOR_HOURS_PP,BILLABLE_LABOR_HOURS_PTD,REVENUE_ITD,
503 REVENUE_YTD,REVENUE_PP,REVENUE_PTD,TXN_UNIT_OF_MEASURE,
504 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
505 LAST_UPDATE_LOGIN) SELECT
506 l_project_accum_id,0,0,0,0,
507 0,0,0,
508 0,0,0,
509 0,0,0,
510 0,0,0,
511 0,0,0,0,0,0,0,0,
512 0,0,0,0,
513 0,0,0,
514 0,0,0,0,
515 0,NULL,G_request_id,G_last_updated_by,Trunc(sysdate),
516 Trunc(Sysdate),G_created_by,G_last_update_login
517 FROM dual
518 WHERE NOT EXISTS (select 'X'
519 from pa_project_accum_actuals
520 where project_accum_id = l_project_accum_id
521 );
522
523
524 -- Commitments Record, If NOT Already Exist
525
526 Insert into PA_PROJECT_ACCUM_COMMITMENTS (
527 PROJECT_ACCUM_ID,CMT_RAW_COST_ITD,CMT_RAW_COST_YTD,CMT_RAW_COST_PP,
528 CMT_RAW_COST_PTD,
529 CMT_BURDENED_COST_ITD,CMT_BURDENED_COST_YTD,
530 CMT_BURDENED_COST_PP,CMT_BURDENED_COST_PTD,
531 CMT_QUANTITY_ITD,CMT_QUANTITY_YTD,
532 CMT_QUANTITY_PP,CMT_QUANTITY_PTD,
533 CMT_UNIT_OF_MEASURE,
534 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
535 LAST_UPDATE_LOGIN)
536 SELECT l_project_accum_id,
537 0,
538 0,
539 0,
540 0,
541 0,
542 0,
543 0,
544 0,
545 0,
546 0,
547 0,
548 0,
549 NULL,G_request_id,G_last_updated_by,Trunc(sysdate),
550 Trunc(Sysdate),G_created_by,
551 G_last_update_login
552 FROM dual
553 WHERE NOT EXISTS (select 'X'
554 from pa_project_accum_commitments
555 where project_accum_id = l_project_accum_id
556 );
557
558 -- Approved Cost Budget Record, If NOT Already Exist
559
560 Insert into PA_PROJECT_ACCUM_BUDGETS (
561 PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
562 BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
563 BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
564 BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
565 ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
566 ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
567 ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
568 ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
569 BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
570 BASE_QUANTITY_PTD,
571 ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
572 ORIG_QUANTITY_PTD,
573 BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
574 BASE_LABOR_HOURS_PTD,
575 ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
576 ORIG_LABOR_HOURS_PTD,
577 BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
578 ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
579 BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
580 BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
581 ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
582 BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
583 ORIG_QUANTITY_TOT,
584 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
585 LAST_UPDATE_LOGIN)
586 SELECT l_project_accum_id,'AC',
587 0,0,0,0,
588 0,0,
589 0,0,
590 0,0,0,0,
591 0,0,
592 0,0,
593 0,0,0,0,
594 0,0,0,0,
595 0,0,0,0,
596 0,0,0,0,
597 0,0,0,0,
598 0,0,0,0,
599 NULL,NULL,
600 0,0,0,0,0,0,0,0,0,0,
601 G_request_id,G_last_updated_by,Trunc(sysdate),
602 Trunc(Sysdate),G_created_by,G_last_update_login
603 FROM dual
604 WHERE NOT EXISTS (select 'X'
605 from pa_project_accum_budgets
606 where project_accum_id = l_project_accum_id
607 and budget_type_code = 'AC'
608 );
609
610
611 -- Approved Revenue Budget Record, If NOT Already Exist
612
613 Insert into PA_PROJECT_ACCUM_BUDGETS (
614 PROJECT_ACCUM_ID,BUDGET_TYPE_CODE,BASE_RAW_COST_ITD,BASE_RAW_COST_YTD,
615 BASE_RAW_COST_PP, BASE_RAW_COST_PTD,
616 BASE_BURDENED_COST_ITD,BASE_BURDENED_COST_YTD,
617 BASE_BURDENED_COST_PP,BASE_BURDENED_COST_PTD,
618 ORIG_RAW_COST_ITD,ORIG_RAW_COST_YTD,
619 ORIG_RAW_COST_PP, ORIG_RAW_COST_PTD,
620 ORIG_BURDENED_COST_ITD,ORIG_BURDENED_COST_YTD,
621 ORIG_BURDENED_COST_PP,ORIG_BURDENED_COST_PTD,
622 BASE_QUANTITY_ITD,BASE_QUANTITY_YTD,BASE_QUANTITY_PP,
623 BASE_QUANTITY_PTD,
624 ORIG_QUANTITY_ITD,ORIG_QUANTITY_YTD,ORIG_QUANTITY_PP,
625 ORIG_QUANTITY_PTD,
626 BASE_LABOR_HOURS_ITD,BASE_LABOR_HOURS_YTD,BASE_LABOR_HOURS_PP,
627 BASE_LABOR_HOURS_PTD,
628 ORIG_LABOR_HOURS_ITD,ORIG_LABOR_HOURS_YTD,ORIG_LABOR_HOURS_PP,
629 ORIG_LABOR_HOURS_PTD,
630 BASE_REVENUE_ITD,BASE_REVENUE_YTD,BASE_REVENUE_PP,BASE_REVENUE_PTD,
631 ORIG_REVENUE_ITD,ORIG_REVENUE_YTD,ORIG_REVENUE_PP,ORIG_REVENUE_PTD,
632 BASE_UNIT_OF_MEASURE,ORIG_UNIT_OF_MEASURE,
633 BASE_RAW_COST_TOT,BASE_BURDENED_COST_TOT,ORIG_RAW_COST_TOT,
634 ORIG_BURDENED_COST_TOT,BASE_REVENUE_TOT,ORIG_REVENUE_TOT,
635 BASE_LABOR_HOURS_TOT,ORIG_LABOR_HOURS_TOT,BASE_QUANTITY_TOT,
636 ORIG_QUANTITY_TOT,
637 REQUEST_ID,LAST_UPDATED_BY,LAST_UPDATE_DATE,CREATION_DATE,CREATED_BY,
638 LAST_UPDATE_LOGIN)
639 SELECT l_project_accum_id,'AR',
640 0,0,0,0,
641 0,0,
642 0,0,
643 0,0,0,0,
644 0,0,
645 0,0,
646 0,0,0,0,
647 0,0,0,0,
648 0,0,0,0,
649 0,0,0,0,
650 0,0,0,0,
651 0,0,0,0,
652 NULL,NULL,
653 0,0,0,0,0,0,0,0,0,0,
654 G_request_id,G_last_updated_by,Trunc(sysdate),
655 Trunc(Sysdate),G_created_by,G_last_update_login
656 FROM dual
657 WHERE NOT EXISTS (select 'X'
658 from pa_project_accum_budgets
659 where project_accum_id = l_project_accum_id
660 and budget_type_code = 'AR'
661 );
662
663
664 EXCEPTION
665
666
667 WHEN dup_val_on_index THEN
668 null; -- Although should not get this, OK if dup record exists.
669
670
671 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
672 x_msg_count := 1;
673 x_msg_data := SUBSTR(SQLERRM, 1, 240);
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675 ROLLBACK TO Upgrade_Accum_Pvt;
676 FND_MSG_PUB.add_Exc_msg(
677 p_pkg_name => G_PKG_NAME,
678 p_procedure_name => l_api_name);
679
680 WHEN OTHERS THEN
681 x_msg_count := 1;
682 x_msg_data := SUBSTR(SQLERRM, 1, 240);
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
684 ROLLBACK TO Upgrade_Accum_Pvt;
685 FND_MSG_PUB.add_Exc_msg(
686 p_pkg_name => G_PKG_NAME,
687 p_procedure_name => l_api_name);
688
689
690
691 END Upgrade_Accum;
692
693 END pa_accum_proj_list;