[Home] [Help]
PACKAGE BODY: APPS.PA_ADW_COLLECT_FACTS
Source
1 PACKAGE BODY PA_ADW_COLLECT_FACTS AS
2 /* $Header: PAADWCFB.pls 120.2 2005/08/19 16:15:22 mwasowic ship $ */
3
4 FUNCTION Initialize RETURN NUMBER IS
5 BEGIN
6 NULL;
7 END Initialize;
8
9 -- Procedure to collect actual cost and commitment costs
10
11 PROCEDURE get_fact_act_cmts
12 (x_project_num_from IN VARCHAR2,
13 x_project_num_to IN VARCHAR2,
14 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
15 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
16 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
17 IS
18
19 -- Define Cursor for sending $0 measure values for
20 -- the resources which were refreshed at the lowest task level
21
22 CURSOR sel_ref_lowest_act_cmts(x_project_id NUMBER) IS
23 SELECT
24 PTXN.TASK_ID,
25 PTXN.PA_PERIOD_KEY,
26 PTXN.EXPENSE_ORGANIZATION_ID,
27 PTXN.OWNER_ORGANIZATION_ID,
28 PTXN.RESOURCE_LIST_MEMBER_ID,
29 PTXN.SERVICE_TYPE_CODE,
30 PTXN.EXPENDITURE_TYPE,
31 PTXN.USER_COL1,
32 PTXN.USER_COL2,
33 PTXN.USER_COL3,
34 PTXN.USER_COL4,
35 PTXN.USER_COL5,
36 SUM(PTXN.USER_COL6) USER_COL6,
37 SUM(PTXN.USER_COL7) USER_COL7,
38 SUM(PTXN.USER_COL8) USER_COL8,
39 SUM(PTXN.USER_COL9) USER_COL9,
40 SUM(PTXN.USER_COL10) USER_COL10,
41 SUM(PTXN.ACCUME_REVENUE) ACCUME_REVENUE,
42 SUM(PTXN.ACCUME_RAW_COST) ACCUME_RAW_COST,
43 SUM(PTXN.ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
44 SUM(PTXN.ACCUME_QUANTITY) ACCUME_QUANTITY,
45 SUM(PTXN.ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
46 SUM(PTXN.ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
47 SUM(PTXN.ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
48 SUM(PTXN.ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
49 SUM(PTXN.ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
50 SUM(PTXN.ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
51 SUM(PTXN.ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
52 SUM(PTXN.ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
53 PTXN.UNIT_OF_MEASURE
54 FROM
55 PA_ADW_R_ACT_CMT_V PTXN,
56 PA_ADW_LOWEST_TASKS_V PT
57 WHERE
58 PTXN.TASK_ID = PT.TASK_ID
59 AND PTXN.PROJECT_ID = x_project_id
60 -- Exclude top tasks
61 AND PT.TASK_ID <> PT.TOP_TASK_ID
62 AND PTXN.RES_ADW_NOTIFY_FLAG = 'S'
63 GROUP BY
64 PTXN.TASK_ID,
65 PTXN.PA_PERIOD_KEY,
66 PTXN.EXPENSE_ORGANIZATION_ID,
67 PTXN.OWNER_ORGANIZATION_ID,
68 PTXN.RESOURCE_LIST_MEMBER_ID,
69 PTXN.SERVICE_TYPE_CODE,
70 PTXN.EXPENDITURE_TYPE,
71 PTXN.USER_COL1,
72 PTXN.USER_COL2,
73 PTXN.USER_COL3,
74 PTXN.USER_COL4,
75 PTXN.USER_COL5,
76 PTXN.UNIT_OF_MEASURE;
77
78 -- Define Cursor for sending $0 measure values for
79 -- the resources which were refreshed at the top task level
80
81 CURSOR sel_ref_top_act_cmts(x_project_id NUMBER) IS
82 SELECT
83 PTXN.TOP_TASK_ID,
84 PTXN.PA_PERIOD_KEY,
85 PTXN.EXPENSE_ORGANIZATION_ID,
86 PTXN.OWNER_ORGANIZATION_ID,
87 PTXN.RESOURCE_LIST_MEMBER_ID,
88 PTXN.SERVICE_TYPE_CODE,
89 PTXN.EXPENDITURE_TYPE,
90 PTXN.USER_COL1,
91 PTXN.USER_COL2,
92 PTXN.USER_COL3,
93 PTXN.USER_COL4,
94 PTXN.USER_COL5,
95 SUM(PTXN.USER_COL6) USER_COL6,
96 SUM(PTXN.USER_COL7) USER_COL7,
97 SUM(PTXN.USER_COL8) USER_COL8,
98 SUM(PTXN.USER_COL9) USER_COL9,
99 SUM(PTXN.USER_COL10) USER_COL10,
100 SUM(PTXN.ACCUME_REVENUE) ACCUME_REVENUE,
101 SUM(PTXN.ACCUME_RAW_COST) ACCUME_RAW_COST,
102 SUM(PTXN.ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
103 SUM(PTXN.ACCUME_QUANTITY) ACCUME_QUANTITY,
104 SUM(PTXN.ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
105 SUM(PTXN.ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
106 SUM(PTXN.ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
107 SUM(PTXN.ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
108 SUM(PTXN.ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
109 SUM(PTXN.ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
110 SUM(PTXN.ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
111 SUM(PTXN.ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
112 PTXN.UNIT_OF_MEASURE
113 FROM
114 PA_ADW_R_ACT_CMT_V PTXN,
115 PA_ADW_TOP_TASKS_V PT
116 WHERE
117 PTXN.TOP_TASK_ID = PT.TOP_TASK_ID
118 AND PTXN.PROJECT_ID = x_project_id
119 AND PTXN.RES_ADW_NOTIFY_FLAG = 'S'
120 GROUP BY
121 PTXN.TOP_TASK_ID,
122 PTXN.PA_PERIOD_KEY,
123 PTXN.EXPENSE_ORGANIZATION_ID,
124 PTXN.OWNER_ORGANIZATION_ID,
125 PTXN.RESOURCE_LIST_MEMBER_ID,
126 PTXN.SERVICE_TYPE_CODE,
127 PTXN.EXPENDITURE_TYPE,
128 PTXN.USER_COL1,
129 PTXN.USER_COL2,
130 PTXN.USER_COL3,
131 PTXN.USER_COL4,
132 PTXN.USER_COL5,
133 PTXN.UNIT_OF_MEASURE;
134
135 -- Define Cursor for sending $0 measure values for
136 -- the resources which were refreshed at the project Level
137
138 CURSOR sel_ref_prj_act_cmts(x_project_id NUMBER) IS
139 SELECT
140 PROJECT_ID,
141 PA_PERIOD_KEY,
142 EXPENSE_ORGANIZATION_ID,
143 OWNER_ORGANIZATION_ID,
144 RESOURCE_LIST_MEMBER_ID,
145 SERVICE_TYPE_CODE,
146 EXPENDITURE_TYPE,
147 USER_COL1,
148 USER_COL2,
149 USER_COL3,
150 USER_COL4,
151 USER_COL5,
152 SUM(USER_COL6) USER_COL6,
153 SUM(USER_COL7) USER_COL7,
154 SUM(USER_COL8) USER_COL8,
155 SUM(USER_COL9) USER_COL9,
156 SUM(USER_COL10) USER_COL10,
157 SUM(ACCUME_REVENUE) ACCUME_REVENUE,
158 SUM(ACCUME_RAW_COST) ACCUME_RAW_COST,
159 SUM(ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
160 SUM(ACCUME_QUANTITY) ACCUME_QUANTITY,
161 SUM(ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
162 SUM(ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
163 SUM(ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
164 SUM(ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
165 SUM(ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
166 SUM(ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
167 SUM(ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
168 SUM(ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
169 UNIT_OF_MEASURE
170 FROM
171 PA_ADW_R_ACT_CMT_V
172 WHERE
173 RES_ADW_NOTIFY_FLAG = 'S'
174 AND PROJECT_ID = x_project_id
175 GROUP BY
176 PROJECT_ID,
177 PA_PERIOD_KEY,
178 EXPENSE_ORGANIZATION_ID,
179 OWNER_ORGANIZATION_ID,
180 RESOURCE_LIST_MEMBER_ID,
181 SERVICE_TYPE_CODE,
182 EXPENDITURE_TYPE,
183 USER_COL1,
184 USER_COL2,
185 USER_COL3,
186 USER_COL4,
187 USER_COL5,
188 UNIT_OF_MEASURE;
189
190 -- Define Cursor for sending $0 measure values for
191 -- the lowest level tasks for which service type was changed
192
193 CURSOR sel_ref_lowest_stype_act_cmts(x_project_id NUMBER) IS
194 SELECT
195 PTXN.TASK_ID,
196 PTXN.PA_PERIOD_KEY,
197 PTXN.EXPENSE_ORGANIZATION_ID,
198 PTXN.OWNER_ORGANIZATION_ID,
199 PTXN.RESOURCE_LIST_MEMBER_ID,
200 PTXN.SERVICE_TYPE_CODE,
201 PTXN.EXPENDITURE_TYPE,
202 PTXN.USER_COL1,
203 PTXN.USER_COL2,
204 PTXN.USER_COL3,
205 PTXN.USER_COL4,
206 PTXN.USER_COL5,
207 SUM(PTXN.USER_COL6) USER_COL6,
208 SUM(PTXN.USER_COL7) USER_COL7,
209 SUM(PTXN.USER_COL8) USER_COL8,
210 SUM(PTXN.USER_COL9) USER_COL9,
211 SUM(PTXN.USER_COL10) USER_COL10,
212 SUM(PTXN.ACCUME_REVENUE) ACCUME_REVENUE,
213 SUM(PTXN.ACCUME_RAW_COST) ACCUME_RAW_COST,
214 SUM(PTXN.ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
215 SUM(PTXN.ACCUME_QUANTITY) ACCUME_QUANTITY,
216 SUM(PTXN.ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
217 SUM(PTXN.ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
218 SUM(PTXN.ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
219 SUM(PTXN.ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
220 SUM(PTXN.ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
221 SUM(PTXN.ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
222 SUM(PTXN.ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
223 SUM(PTXN.ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
224 PTXN.UNIT_OF_MEASURE
225 FROM
226 PA_ADW_R_ST_ACT_CMT_V PTXN,
227 PA_ADW_LOWEST_TASKS_V PT
228 WHERE
229 PTXN.TASK_ID = PT.TASK_ID
230 AND PTXN.PROJECT_ID = x_project_id
231 -- Exclude top tasks
232 AND PT.TASK_ID <> PT.TOP_TASK_ID
233 AND (PTXN.TSK_ADW_NOTIFY_FLAG = 'S' OR PTXN.TSK_ADW_NOTIFY_FLAG = 'P')
234 GROUP BY
235 PTXN.TASK_ID,
236 PTXN.PA_PERIOD_KEY,
237 PTXN.EXPENSE_ORGANIZATION_ID,
238 PTXN.OWNER_ORGANIZATION_ID,
239 PTXN.RESOURCE_LIST_MEMBER_ID,
240 PTXN.SERVICE_TYPE_CODE,
241 PTXN.EXPENDITURE_TYPE,
242 PTXN.USER_COL1,
243 PTXN.USER_COL2,
244 PTXN.USER_COL3,
245 PTXN.USER_COL4,
246 PTXN.USER_COL5,
247 PTXN.UNIT_OF_MEASURE;
248
249 -- Define Cursor for sending $0 measure values for
250 -- the top level tasks for which service type was changed
251
252 CURSOR sel_ref_top_ser_type_act_cmts(x_project_id NUMBER) IS
253 SELECT
254 PTXN.TOP_TASK_ID,
255 PTXN.PA_PERIOD_KEY,
256 PTXN.EXPENSE_ORGANIZATION_ID,
257 PTXN.OWNER_ORGANIZATION_ID,
258 PTXN.RESOURCE_LIST_MEMBER_ID,
259 PTXN.SERVICE_TYPE_CODE,
260 PTXN.EXPENDITURE_TYPE,
261 PTXN.USER_COL1,
262 PTXN.USER_COL2,
263 PTXN.USER_COL3,
264 PTXN.USER_COL4,
265 PTXN.USER_COL5,
266 SUM(PTXN.USER_COL6) USER_COL6,
267 SUM(PTXN.USER_COL7) USER_COL7,
268 SUM(PTXN.USER_COL8) USER_COL8,
269 SUM(PTXN.USER_COL9) USER_COL9,
270 SUM(PTXN.USER_COL10) USER_COL10,
271 SUM(PTXN.ACCUME_REVENUE) ACCUME_REVENUE,
272 SUM(PTXN.ACCUME_RAW_COST) ACCUME_RAW_COST,
273 SUM(PTXN.ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
274 SUM(PTXN.ACCUME_QUANTITY) ACCUME_QUANTITY,
275 SUM(PTXN.ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
276 SUM(PTXN.ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
277 SUM(PTXN.ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
278 SUM(PTXN.ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
279 SUM(PTXN.ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
280 SUM(PTXN.ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
281 SUM(PTXN.ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
282 SUM(PTXN.ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
283 PTXN.UNIT_OF_MEASURE
284 FROM
285 PA_ADW_R_ST_ACT_CMT_V PTXN,
286 PA_ADW_TOP_TASKS_V PT
287 WHERE
288 PTXN.TOP_TASK_ID = PT.TOP_TASK_ID
289 AND PTXN.PROJECT_ID = x_project_id
290 AND (PTXN.TSK_ADW_NOTIFY_FLAG = 'S' OR PTXN.TSK_ADW_NOTIFY_FLAG = 'P')
291 GROUP BY
292 PTXN.TOP_TASK_ID,
293 PTXN.PA_PERIOD_KEY,
294 PTXN.EXPENSE_ORGANIZATION_ID,
295 PTXN.OWNER_ORGANIZATION_ID,
296 PTXN.RESOURCE_LIST_MEMBER_ID,
297 PTXN.SERVICE_TYPE_CODE,
298 PTXN.EXPENDITURE_TYPE,
299 PTXN.USER_COL1,
300 PTXN.USER_COL2,
301 PTXN.USER_COL3,
302 PTXN.USER_COL4,
303 PTXN.USER_COL5,
304 PTXN.UNIT_OF_MEASURE;
305
306 -- Define Cursor for sending $0 measure values at
307 -- the project level for the service type changed at task level
308
309 CURSOR sel_ref_prj_ser_type_act_cmts(x_project_id NUMBER) IS
310 SELECT
311 PROJECT_ID,
312 PA_PERIOD_KEY,
313 EXPENSE_ORGANIZATION_ID,
314 OWNER_ORGANIZATION_ID,
315 RESOURCE_LIST_MEMBER_ID,
316 SERVICE_TYPE_CODE,
317 EXPENDITURE_TYPE,
318 USER_COL1,
319 USER_COL2,
320 USER_COL3,
321 USER_COL4,
322 USER_COL5,
323 SUM(USER_COL6) USER_COL6,
324 SUM(USER_COL7) USER_COL7,
325 SUM(USER_COL8) USER_COL8,
326 SUM(USER_COL9) USER_COL9,
327 SUM(USER_COL10) USER_COL10,
328 SUM(ACCUME_REVENUE) ACCUME_REVENUE,
329 SUM(ACCUME_RAW_COST) ACCUME_RAW_COST,
330 SUM(ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
331 SUM(ACCUME_QUANTITY) ACCUME_QUANTITY,
332 SUM(ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
333 SUM(ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
334 SUM(ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
335 SUM(ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
336 SUM(ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
337 SUM(ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
338 SUM(ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
339 SUM(ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
340 UNIT_OF_MEASURE
341 FROM
342 PA_ADW_R_ST_ACT_CMT_V
343 WHERE
344 (TSK_ADW_NOTIFY_FLAG = 'S' OR TSK_ADW_NOTIFY_FLAG = 'P')
345 AND PROJECT_ID = x_project_id
346 GROUP BY
347 PROJECT_ID,
348 PA_PERIOD_KEY,
349 EXPENSE_ORGANIZATION_ID,
350 OWNER_ORGANIZATION_ID,
351 RESOURCE_LIST_MEMBER_ID,
352 SERVICE_TYPE_CODE,
353 EXPENDITURE_TYPE,
354 USER_COL1,
355 USER_COL2,
356 USER_COL3,
357 USER_COL4,
358 USER_COL5,
359 UNIT_OF_MEASURE;
360
361 -- Define Cursor for selecting Actuals and Commitments
362 -- at lowest level of task.
363
364 CURSOR sel_lowest_act_cmts(x_project_id NUMBER) IS
365 SELECT
366 TASK_ID,
367 PA_PERIOD_KEY,
368 EXPENSE_ORGANIZATION_ID,
369 OWNER_ORGANIZATION_ID,
370 RESOURCE_LIST_MEMBER_ID,
371 SERVICE_TYPE_CODE,
372 EXPENDITURE_TYPE,
373 USER_COL1,
374 USER_COL2,
375 USER_COL3,
376 USER_COL4,
377 USER_COL5,
378 SUM(USER_COL6) USER_COL6,
379 SUM(USER_COL7) USER_COL7,
380 SUM(USER_COL8) USER_COL8,
381 SUM(USER_COL9) USER_COL9,
382 SUM(USER_COL10) USER_COL10,
383 SUM(ACCUME_REVENUE) ACCUME_REVENUE,
384 SUM(ACCUME_RAW_COST) ACCUME_RAW_COST,
385 SUM(ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
386 SUM(ACCUME_QUANTITY) ACCUME_QUANTITY,
387 SUM(ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
388 SUM(ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
389 SUM(ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
390 SUM(ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
391 SUM(ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
392 SUM(ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
393 SUM(ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
394 SUM(ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
395 UNIT_OF_MEASURE
396 FROM
397 PA_ADW_ACT_CMT_V
398 WHERE
399 PROJECT_ID = x_project_id
400 AND (TASK_ID,
401 PA_PERIOD_KEY,
402 NVL(EXPENSE_ORGANIZATION_ID,-99),
403 NVL(OWNER_ORGANIZATION_ID,-99),
407 NVL(USER_COL1,'X'),
404 NVL(RESOURCE_LIST_MEMBER_ID,-99),
405 NVL(SERVICE_TYPE_CODE,'X'),
406 NVL(EXPENDITURE_TYPE,'X'),
408 NVL(USER_COL2,'X'),
409 NVL(USER_COL3,'X'),
410 NVL(USER_COL4,'X'),
411 NVL(USER_COL5,'X'),
412 NVL(UNIT_OF_MEASURE,'X'))
413 IN
414 (SELECT
415 PTXN.TASK_ID,
416 PTXN.PA_PERIOD_KEY,
417 NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
418 NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
419 NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
420 NVL(PTXN.SERVICE_TYPE_CODE,'X'),
421 NVL(PTXN.EXPENDITURE_TYPE,'X'),
422 NVL(PTXN.USER_COL1,'X'),
423 NVL(PTXN.USER_COL2,'X'),
424 NVL(PTXN.USER_COL3,'X'),
425 NVL(PTXN.USER_COL4,'X'),
426 NVL(PTXN.USER_COL5,'X'),
427 NVL(PTXN.UNIT_OF_MEASURE,'X')
428 FROM
429 PA_ADW_ACT_CMT_V PTXN,
430 PA_ADW_LOWEST_TASKS_V PT
431 WHERE
432 PTXN.TASK_ID = PT.TASK_ID
433 AND PTXN.PROJECT_ID = x_project_id
434 -- Exclude the tasks which are top tasks
435 AND PT.TASK_ID <> PT.TOP_TASK_ID
436 AND (PTXN.RES_ADW_NOTIFY_FLAG = 'S' OR PTXN.TXN_ADW_NOTIFY_FLAG = 'S')
437 GROUP BY
438 PTXN.TASK_ID,
439 PTXN.PA_PERIOD_KEY,
440 NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
441 NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
442 NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
443 NVL(PTXN.SERVICE_TYPE_CODE,'X'),
444 NVL(PTXN.EXPENDITURE_TYPE,'X'),
445 NVL(PTXN.USER_COL1,'X'),
446 NVL(PTXN.USER_COL2,'X'),
447 NVL(PTXN.USER_COL3,'X'),
448 NVL(PTXN.USER_COL4,'X'),
449 NVL(PTXN.USER_COL5,'X'),
450 NVL(PTXN.UNIT_OF_MEASURE,'X'))
451 GROUP BY
452 TASK_ID,
453 PA_PERIOD_KEY,
454 EXPENSE_ORGANIZATION_ID,
455 OWNER_ORGANIZATION_ID,
456 RESOURCE_LIST_MEMBER_ID,
457 SERVICE_TYPE_CODE,
458 EXPENDITURE_TYPE,
459 USER_COL1,
460 USER_COL2,
461 USER_COL3,
462 USER_COL4,
463 USER_COL5,
464 UNIT_OF_MEASURE;
465
466 -- Define Cursor for selecting Actuals and Commitments
467 -- at top level of task.
468
469 CURSOR sel_top_act_cmts(x_project_id NUMBER) IS
470 SELECT
471 TOP_TASK_ID,
472 PA_PERIOD_KEY,
473 EXPENSE_ORGANIZATION_ID,
474 OWNER_ORGANIZATION_ID,
475 RESOURCE_LIST_MEMBER_ID,
476 SERVICE_TYPE_CODE,
477 EXPENDITURE_TYPE,
478 USER_COL1,
479 USER_COL2,
480 USER_COL3,
481 USER_COL4,
482 USER_COL5,
483 SUM(USER_COL6) USER_COL6,
484 SUM(USER_COL7) USER_COL7,
485 SUM(USER_COL8) USER_COL8,
486 SUM(USER_COL9) USER_COL9,
487 SUM(USER_COL10) USER_COL10,
488 SUM(ACCUME_REVENUE) ACCUME_REVENUE,
489 SUM(ACCUME_RAW_COST) ACCUME_RAW_COST,
490 SUM(ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
491 SUM(ACCUME_QUANTITY) ACCUME_QUANTITY,
492 SUM(ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
493 SUM(ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
494 SUM(ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
495 SUM(ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
496 SUM(ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
497 SUM(ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
498 SUM(ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
499 SUM(ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
500 UNIT_OF_MEASURE
501 FROM
502 PA_ADW_ACT_CMT_V
503 WHERE
504 PROJECT_ID = x_project_id
505 AND (TOP_TASK_ID,
506 PA_PERIOD_KEY,
507 NVL(EXPENSE_ORGANIZATION_ID,-99),
508 NVL(OWNER_ORGANIZATION_ID,-99),
509 NVL(RESOURCE_LIST_MEMBER_ID,-99),
510 NVL(SERVICE_TYPE_CODE,'X'),
511 NVL(EXPENDITURE_TYPE,'X'),
512 NVL(USER_COL1,'X'),
513 NVL(USER_COL2,'X'),
514 NVL(USER_COL3,'X'),
515 NVL(USER_COL4,'X'),
516 NVL(USER_COL5,'X'),
517 NVL(UNIT_OF_MEASURE,'X'))
518 IN
519 (SELECT
520 PTXN.TOP_TASK_ID,
521 PTXN.PA_PERIOD_KEY,
522 NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
523 NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
524 NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
525 NVL(PTXN.SERVICE_TYPE_CODE,'X'),
526 NVL(PTXN.EXPENDITURE_TYPE,'X'),
527 NVL(PTXN.USER_COL1,'X'),
528 NVL(PTXN.USER_COL2,'X'),
529 NVL(PTXN.USER_COL3,'X'),
530 NVL(PTXN.USER_COL4,'X'),
531 NVL(PTXN.USER_COL5,'X'),
532 NVL(PTXN.UNIT_OF_MEASURE,'X')
533 FROM
534 PA_ADW_ACT_CMT_V PTXN,
535 PA_ADW_TOP_TASKS_V PT
536 WHERE
537 PTXN.TOP_TASK_ID = PT.TOP_TASK_ID
538 AND PTXN.PROJECT_ID = x_project_id
539 AND (PTXN.RES_ADW_NOTIFY_FLAG = 'S' OR PTXN.TXN_ADW_NOTIFY_FLAG = 'S')
540 GROUP BY
541 PTXN.TOP_TASK_ID,
542 PTXN.PA_PERIOD_KEY,
546 NVL(PTXN.SERVICE_TYPE_CODE,'X'),
543 NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
544 NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
545 NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
547 NVL(PTXN.EXPENDITURE_TYPE,'X'),
548 NVL(PTXN.USER_COL1,'X'),
549 NVL(PTXN.USER_COL2,'X'),
550 NVL(PTXN.USER_COL3,'X'),
551 NVL(PTXN.USER_COL4,'X'),
552 NVL(PTXN.USER_COL5,'X'),
553 NVL(PTXN.UNIT_OF_MEASURE,'X'))
554 GROUP BY
555 TOP_TASK_ID,
556 PA_PERIOD_KEY,
557 EXPENSE_ORGANIZATION_ID,
558 OWNER_ORGANIZATION_ID,
559 RESOURCE_LIST_MEMBER_ID,
560 SERVICE_TYPE_CODE,
561 EXPENDITURE_TYPE,
562 USER_COL1,
563 USER_COL2,
564 USER_COL3,
565 USER_COL4,
566 USER_COL5,
567 UNIT_OF_MEASURE;
568
569 -- Define Cursor for selecting Actuals and Commitments
570 -- at project level.
571
572 CURSOR sel_prj_act_cmts(x_project_id NUMBER) IS
573 SELECT
574 PROJECT_ID,
575 PA_PERIOD_KEY,
576 EXPENSE_ORGANIZATION_ID,
577 OWNER_ORGANIZATION_ID,
578 RESOURCE_LIST_MEMBER_ID,
579 SERVICE_TYPE_CODE,
580 EXPENDITURE_TYPE,
581 USER_COL1,
582 USER_COL2,
583 USER_COL3,
584 USER_COL4,
585 USER_COL5,
586 SUM(USER_COL6) USER_COL6,
587 SUM(USER_COL7) USER_COL7,
588 SUM(USER_COL8) USER_COL8,
589 SUM(USER_COL9) USER_COL9,
590 SUM(USER_COL10) USER_COL10,
591 SUM(ACCUME_REVENUE) ACCUME_REVENUE,
592 SUM(ACCUME_RAW_COST) ACCUME_RAW_COST,
593 SUM(ACCUME_BURDENED_COST) ACCUME_BURDENED_COST,
594 SUM(ACCUME_QUANTITY) ACCUME_QUANTITY,
595 SUM(ACCUME_LABOR_HOURS) ACCUME_LABOR_HOURS,
596 SUM(ACCUME_BILLABLE_RAW_COST) ACCUME_BILLABLE_RAW_COST,
597 SUM(ACCUME_BILLABLE_BURDENED_COST) ACCUME_BILLABLE_BURDENED_COST,
598 SUM(ACCUME_BILLABLE_QUANTITY) ACCUME_BILLABLE_QUANTITY,
599 SUM(ACCUME_BILLABLE_LABOR_HOURS) ACCUME_BILLABLE_LABOR_HOURS,
600 SUM(ACCUME_CMT_RAW_COST) ACCUME_CMT_RAW_COST,
601 SUM(ACCUME_CMT_BURDENED_COST) ACCUME_CMT_BURDENED_COST,
602 SUM(ACCUME_CMT_QUANTITY) ACCUME_CMT_QUANTITY,
603 UNIT_OF_MEASURE
604 FROM
605 PA_ADW_ACT_CMT_V
606 WHERE
607 PROJECT_ID = x_project_id
608 AND (PROJECT_ID,
609 PA_PERIOD_KEY,
610 NVL(EXPENSE_ORGANIZATION_ID,-99),
611 NVL(OWNER_ORGANIZATION_ID,-99),
612 NVL(RESOURCE_LIST_MEMBER_ID,-99),
613 NVL(SERVICE_TYPE_CODE,'X'),
614 NVL(EXPENDITURE_TYPE,'X'),
615 NVL(USER_COL1,'X'),
616 NVL(USER_COL2,'X'),
617 NVL(USER_COL3,'X'),
618 NVL(USER_COL4,'X'),
619 NVL(USER_COL5,'X'),
620 NVL(UNIT_OF_MEASURE,'X'))
621 IN
622 (SELECT
623 PTXN.PROJECT_ID,
624 PTXN.PA_PERIOD_KEY,
625 NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
626 NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
627 NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
628 NVL(PTXN.SERVICE_TYPE_CODE,'X'),
629 NVL(PTXN.EXPENDITURE_TYPE,'X'),
630 NVL(PTXN.USER_COL1,'X'),
631 NVL(PTXN.USER_COL2,'X'),
632 NVL(PTXN.USER_COL3,'X'),
633 NVL(PTXN.USER_COL4,'X'),
634 NVL(PTXN.USER_COL5,'X'),
635 NVL(PTXN.UNIT_OF_MEASURE,'X')
636 FROM
637 PA_ADW_ACT_CMT_V PTXN
638 WHERE
639 PTXN.PROJECT_ID = x_project_id
640 AND (PTXN.RES_ADW_NOTIFY_FLAG = 'S' OR PTXN.TXN_ADW_NOTIFY_FLAG = 'S')
641 GROUP BY
642 PTXN.PROJECT_ID,
643 PTXN.PA_PERIOD_KEY,
644 NVL(PTXN.EXPENSE_ORGANIZATION_ID,-99),
645 NVL(PTXN.OWNER_ORGANIZATION_ID,-99),
646 NVL(PTXN.RESOURCE_LIST_MEMBER_ID,-99),
647 NVL(PTXN.SERVICE_TYPE_CODE,'X'),
648 NVL(PTXN.EXPENDITURE_TYPE,'X'),
649 NVL(PTXN.USER_COL1,'X'),
650 NVL(PTXN.USER_COL2,'X'),
651 NVL(PTXN.USER_COL3,'X'),
652 NVL(PTXN.USER_COL4,'X'),
653 NVL(PTXN.USER_COL5,'X'),
654 NVL(PTXN.UNIT_OF_MEASURE,'X'))
655 GROUP BY
656 PROJECT_ID,
657 PA_PERIOD_KEY,
658 EXPENSE_ORGANIZATION_ID,
659 OWNER_ORGANIZATION_ID,
660 RESOURCE_LIST_MEMBER_ID,
661 SERVICE_TYPE_CODE,
662 EXPENDITURE_TYPE,
663 USER_COL1,
664 USER_COL2,
665 USER_COL3,
666 USER_COL4,
667 USER_COL5,
668 UNIT_OF_MEASURE;
669
670 -- Cursor for selecting projects for processing
671
672 CURSOR sel_prjs IS
673 SELECT
674 PROJECT_ID,
675 SEGMENT1
676 FROM
677 PA_ADW_PROJECTS_V
678 WHERE segment1 BETWEEN NVL(x_project_num_from,segment1)
679 AND NVL(x_project_num_to,segment1);
680
681 -- define procedure variables
682
683 ref_lowest_act_cmts_r sel_ref_lowest_act_cmts%ROWTYPE;
684 ref_top_act_cmts_r sel_ref_top_act_cmts%ROWTYPE;
688 ref_prj_ser_type_act_cmts_r sel_ref_prj_ser_type_act_cmts%ROWTYPE;
685 ref_prj_act_cmts_r sel_ref_prj_act_cmts%ROWTYPE;
686 ref_lowest_ser_type_act_cmts_r sel_ref_lowest_stype_act_cmts%ROWTYPE;
687 ref_top_ser_type_act_cmts_r sel_ref_top_ser_type_act_cmts%ROWTYPE;
689
690 lowest_act_cmts_r sel_lowest_act_cmts%ROWTYPE;
691 top_act_cmts_r sel_top_act_cmts%ROWTYPE;
692 prj_act_cmts_r sel_prj_act_cmts%ROWTYPE;
693
694 sel_prjs_r sel_prjs%ROWTYPE;
695
696 x_old_err_stack VARCHAR2(1024);
697 txn_count NUMBER;
698 res_count NUMBER;
699 ref_res_count NUMBER; -- The resources to be refreshed because
700 -- the resources were refreshed
701 ref_ser_type_count NUMBER; -- The number of tasks to be refreshed for
702 -- service type change
703
704
705 BEGIN
706 x_err_code := 0;
707 x_err_stage := 'Collecting Actuals/Commitments';
708 x_old_err_stack := x_err_stack;
709 x_err_stack := x_err_stack || '-> get_fact_act_cmts';
710
711 pa_debug.debug(x_err_stage);
712
713 -- Process all projects one one by one
714
715 FOR sel_prjs_r IN sel_prjs LOOP
716
717 -- Mark the PA_TASK_HISTORY rows for latest service_type
718 -- These are the task for which the service type is latest
719
720 pa_debug.debug('Processing project_id ' || to_char(sel_prjs_r.project_id) || ' Project Number' || sel_prjs_r.segment1 );
721
722 UPDATE
723 PA_TASK_HISTORY PTH
724 SET
725 ADW_NOTIFY_FLAG = 'Y'
726 WHERE
727 PROJECT_ID = sel_prjs_r.project_id
728 AND TASK_HISTORY_ID IN
729 ( SELECT MAX(TASK_HISTORY_ID)
730 FROM PA_TASK_HISTORY PTHL
731 WHERE PTHL.TASK_ID = PTH.TASK_ID
732 );
733
734 pa_debug.debug('Marked ' || TO_CHAR(SQL%ROWCOUNT) || ' Task Rows for Transfer');
735
736 -- Update the task where the service type has changed from
737 -- the last time the tasks were interfaced to the interface table
738 -- and the task which are not refreshed earlier by the project refresh
739 -- cursor
740
741
742 UPDATE PA_ADW_INTERFACED_TASKS PTH
743 SET
744 ADW_NOTIFY_FLAG = 'S'
745 WHERE
746 ADW_INTERFACE_FLAG = 'Y'
747 AND PROJECT_ID = sel_prjs_r.project_id
748 AND EXISTS
749 ( SELECT 'Yes'
750 FROM PA_TASK_HISTORY PTHL
751 WHERE PTHL.TASK_ID = PTH.TASK_ID
752 AND (
753 PTHL.SERVICE_TYPE_CODE <> PTH.SERVICE_TYPE_CODE or
754 PTHL.CARRYING_OUT_ORGANIZATION_ID <> PTH.CARRYING_OUT_ORGANIZATION_ID -- fix for bug 1233570, created by DMPOTAPO
755 )
756 AND PTHL.TASK_HISTORY_ID > PTH.TASK_HISTORY_ID
757 )
758 AND NOT EXISTS
759 ( SELECT 'Yes'
760 FROM PA_OLD_RES_ACCUM_DTLS
761 WHERE PROJECT_ID = sel_prjs_r.project_id
762 AND TASK_ID = PTH.TASK_ID
763 AND ADW_NOTIFY_FLAG = 'Y'
764 );
765
766 ref_ser_type_count := SQL%ROWCOUNT;
767 pa_debug.debug('Marked ' || TO_CHAR(SQL%ROWCOUNT) || ' Task Rows for Service Type Change');
768
769 -- Mark the other tasks with this service type for refresh too
770
771 UPDATE PA_ADW_INTERFACED_TASKS PTH
772 SET
773 ADW_NOTIFY_FLAG = 'P'
774 WHERE
775 ADW_INTERFACE_FLAG = 'Y'
776 AND ADW_NOTIFY_FLAG <> 'S'
777 AND PROJECT_ID = sel_prjs_r.project_id
778 AND EXISTS
779 ( SELECT 'Yes'
780 FROM PA_ADW_INTERFACED_TASKS PTHL
781 WHERE PTHL.TASK_ID <> PTH.TASK_ID
782 AND (
783 PTHL.SERVICE_TYPE_CODE = PTH.SERVICE_TYPE_CODE or
784 PTHL.CARRYING_OUT_ORGANIZATION_ID = PTH.CARRYING_OUT_ORGANIZATION_ID -- fix for bug 1233570, created by DMPOTAPO
785 )
786 AND PTHL.PROJECT_ID = sel_prjs_r.project_id
787 AND PTHL.ADW_NOTIFY_FLAG = 'S'
788 )
789 AND NOT EXISTS
790 ( SELECT 'Yes'
791 FROM PA_OLD_RES_ACCUM_DTLS
792 WHERE PROJECT_ID = sel_prjs_r.project_id
793 AND TASK_ID = PTH.TASK_ID
794 AND ADW_NOTIFY_FLAG = 'Y'
795 );
796 pa_debug.debug('Marked ' || TO_CHAR(SQL%ROWCOUNT) || ' Task Rows for Service Type Change at other tasks');
797
798 -- Update all lowest level tasks if any top task got changed
799
800 UPDATE PA_ADW_INTERFACED_TASKS PTH
801 SET
802 ADW_NOTIFY_FLAG = 'P'
803 WHERE
804 ADW_INTERFACE_FLAG = 'Y'
805 AND ADW_NOTIFY_FLAG <> 'S'
806 AND PROJECT_ID = sel_prjs_r.project_id
807 AND EXISTS
808 ( SELECT 'Yes'
809 FROM PA_TASK_HISTORY PTHL
810 WHERE PTHL.TASK_ID = PTH.TOP_TASK_ID
811 AND PTHL.ADW_NOTIFY_FLAG = 'S'
812 )
813 AND NOT EXISTS
814 ( SELECT 'Yes'
815 FROM PA_OLD_RES_ACCUM_DTLS
819 );
816 WHERE PROJECT_ID = sel_prjs_r.project_id
817 AND TASK_ID = PTH.TASK_ID
818 AND ADW_NOTIFY_FLAG = 'Y'
820 pa_debug.debug('Marked ' || TO_CHAR(SQL%ROWCOUNT) || ' Task Rows due to the service type change at top task level');
821
822 -- Mark all the resource accum rows need to be refreshed
823
824 UPDATE
825 PA_OLD_RES_ACCUM_DTLS
826 SET
827 ADW_NOTIFY_FLAG = 'S'
828 WHERE
829 PROJECT_ID = sel_prjs_r.project_id
830 AND ADW_NOTIFY_FLAG = 'Y';
831
832 ref_res_count := SQL%ROWCOUNT;
833
834 pa_debug.debug('Marked ' || TO_CHAR(SQL%ROWCOUNT) || ' Old Resource accum details');
835
836 -- Check if the service type was changed for any task interfaced earlier
837 -- For all of these tasks we need to send adjustments. We will send $0
838 -- for the old service type and full amount to the new service type
839
840 IF ( ref_res_count <> 0 ) THEN
841
842 -- Check the profile option value for collecting lowest tasks
843
844 IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y') THEN
845
846 -- Refresh the lowest tasks numbers
847
848 x_err_stage := 'Refreshing txns at lowest task level';
849
850 pa_debug.debug(x_err_stage);
851
852 FOR ref_lowest_act_cmts_r IN sel_ref_lowest_act_cmts(sel_prjs_r.project_id) LOOP
853
854 pa_debug.debug('Sending $0** for lowest task_id ' || to_char(ref_lowest_act_cmts_r.task_id) || ' For Service Type ' || ref_lowest_act_cmts_r.service_type_code);
855
856 update_tasks_act_cmt
857 (ref_lowest_act_cmts_r.task_id,
858 ref_lowest_act_cmts_r.pa_period_key,
859 ref_lowest_act_cmts_r.expense_organization_id,
860 ref_lowest_act_cmts_r.owner_organization_id,
861 ref_lowest_act_cmts_r.resource_list_member_id,
862 ref_lowest_act_cmts_r.service_type_code,
863 ref_lowest_act_cmts_r.expenditure_type,
864 ref_lowest_act_cmts_r.user_col1,
865 ref_lowest_act_cmts_r.user_col2,
866 ref_lowest_act_cmts_r.user_col3,
867 ref_lowest_act_cmts_r.user_col4,
868 ref_lowest_act_cmts_r.user_col5,
869 ref_lowest_act_cmts_r.user_col6,
870 ref_lowest_act_cmts_r.user_col7,
871 ref_lowest_act_cmts_r.user_col8,
872 ref_lowest_act_cmts_r.user_col9,
873 ref_lowest_act_cmts_r.user_col10,
874 ref_lowest_act_cmts_r.accume_revenue,
875 ref_lowest_act_cmts_r.accume_raw_cost,
876 ref_lowest_act_cmts_r.accume_burdened_cost,
877 ref_lowest_act_cmts_r.accume_quantity,
878 ref_lowest_act_cmts_r.accume_labor_hours,
879 ref_lowest_act_cmts_r.accume_billable_raw_cost,
880 ref_lowest_act_cmts_r.accume_billable_burdened_cost,
881 ref_lowest_act_cmts_r.accume_billable_quantity,
882 ref_lowest_act_cmts_r.accume_billable_labor_hours,
883 ref_lowest_act_cmts_r.accume_cmt_raw_cost,
884 ref_lowest_act_cmts_r.accume_cmt_burdened_cost,
885 ref_lowest_act_cmts_r.accume_cmt_quantity,
886 ref_lowest_act_cmts_r.unit_of_measure,
887 x_err_stage,
888 x_err_stack,
889 x_err_code);
890
891 END LOOP; -- FOR ref_lowest_act_cmts_r IN sel_ref_lowest_act_cmts
892
893 END IF; -- IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y')
894
895 -- Check the profile option value for collecting top tasks
896
897 IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y') THEN
898
899 -- Refresh the top tasks numbers
900
901 x_err_stage := 'Refreshing txns at top task level';
902
903 pa_debug.debug(x_err_stage);
904
905 FOR ref_top_act_cmts_r IN sel_ref_top_act_cmts(sel_prjs_r.project_id) LOOP
906
907 pa_debug.debug('Sending $0** for top task_id ' || to_char(ref_top_act_cmts_r.top_task_id) || ' For Service Type ' || ref_top_act_cmts_r.service_type_code);
908
909 update_tasks_act_cmt
910 (ref_top_act_cmts_r.top_task_id,
911 ref_top_act_cmts_r.pa_period_key,
912 ref_top_act_cmts_r.expense_organization_id,
913 ref_top_act_cmts_r.owner_organization_id,
914 ref_top_act_cmts_r.resource_list_member_id,
915 ref_top_act_cmts_r.service_type_code,
916 ref_top_act_cmts_r.expenditure_type,
917 ref_top_act_cmts_r.user_col1,
918 ref_top_act_cmts_r.user_col2,
919 ref_top_act_cmts_r.user_col3,
920 ref_top_act_cmts_r.user_col4,
921 ref_top_act_cmts_r.user_col5,
922 ref_top_act_cmts_r.user_col6,
923 ref_top_act_cmts_r.user_col7,
924 ref_top_act_cmts_r.user_col8,
925 ref_top_act_cmts_r.user_col9,
926 ref_top_act_cmts_r.user_col10,
927 ref_top_act_cmts_r.accume_revenue,
928 ref_top_act_cmts_r.accume_raw_cost,
929 ref_top_act_cmts_r.accume_burdened_cost,
930 ref_top_act_cmts_r.accume_quantity,
931 ref_top_act_cmts_r.accume_labor_hours,
932 ref_top_act_cmts_r.accume_billable_raw_cost,
933 ref_top_act_cmts_r.accume_billable_burdened_cost,
934 ref_top_act_cmts_r.accume_billable_quantity,
935 ref_top_act_cmts_r.accume_billable_labor_hours,
936 ref_top_act_cmts_r.accume_cmt_raw_cost,
937 ref_top_act_cmts_r.accume_cmt_burdened_cost,
938 ref_top_act_cmts_r.accume_cmt_quantity,
942 x_err_code);
939 ref_top_act_cmts_r.unit_of_measure,
940 x_err_stage,
941 x_err_stack,
943
944 END LOOP; -- FOR ref_top_act_cmts_r IN sel_ref_top_act_cmts
945
946 END IF; -- IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y')
947
948 -- Refresh the project level Numbers
949
950 x_err_stage := 'Refreshing txns at Project level';
951
952 pa_debug.debug(x_err_stage);
953
954 FOR ref_prj_act_cmts_r IN sel_ref_prj_act_cmts(sel_prjs_r.project_id) LOOP
955
956 pa_debug.debug('Sending $0*** for project_id ' || to_char(ref_prj_act_cmts_r.project_id) || ' For Service Type ' || ref_prj_act_cmts_r.service_type_code);
957
958 update_prj_act_cmt
959 (ref_prj_act_cmts_r.project_id,
960 ref_prj_act_cmts_r.pa_period_key,
961 ref_prj_act_cmts_r.expense_organization_id,
962 ref_prj_act_cmts_r.owner_organization_id,
963 ref_prj_act_cmts_r.resource_list_member_id,
964 ref_prj_act_cmts_r.service_type_code,
965 ref_prj_act_cmts_r.expenditure_type,
966 ref_prj_act_cmts_r.user_col1,
967 ref_prj_act_cmts_r.user_col2,
968 ref_prj_act_cmts_r.user_col3,
969 ref_prj_act_cmts_r.user_col4,
970 ref_prj_act_cmts_r.user_col5,
971 ref_prj_act_cmts_r.user_col6,
972 ref_prj_act_cmts_r.user_col7,
973 ref_prj_act_cmts_r.user_col8,
974 ref_prj_act_cmts_r.user_col9,
975 ref_prj_act_cmts_r.user_col10,
976 ref_prj_act_cmts_r.accume_revenue,
977 ref_prj_act_cmts_r.accume_raw_cost,
978 ref_prj_act_cmts_r.accume_burdened_cost,
979 ref_prj_act_cmts_r.accume_quantity,
980 ref_prj_act_cmts_r.accume_labor_hours,
981 ref_prj_act_cmts_r.accume_billable_raw_cost,
982 ref_prj_act_cmts_r.accume_billable_burdened_cost,
983 ref_prj_act_cmts_r.accume_billable_quantity,
984 ref_prj_act_cmts_r.accume_billable_labor_hours,
985 ref_prj_act_cmts_r.accume_cmt_raw_cost,
986 ref_prj_act_cmts_r.accume_cmt_burdened_cost,
987 ref_prj_act_cmts_r.accume_cmt_quantity,
988 ref_prj_act_cmts_r.unit_of_measure,
989 x_err_stage,
990 x_err_stack,
991 x_err_code);
992
993 END LOOP; -- FOR ref_prj_act_cmts_r IN sel_ref_prj_act_cmts
994
995 -- Mark the project types as transferred to Interface table
996
997 -- PLEASE NOTE THAT WE ARE UPDATING THE BASE TABLE SINCE THE
998 -- PA_ADW_R_ACT_CMT_V IS DEFINED ON MULTIPLE TABLES
999
1000 UPDATE
1001 PA_OLD_RES_ACCUM_DTLS
1002 SET
1003 ADW_NOTIFY_FLAG = 'N'
1004 WHERE
1005 ADW_NOTIFY_FLAG = 'S';
1006
1007 END IF; -- IF ( ref_res_count <> 0 )
1008
1009 IF ( ref_ser_type_count <> 0 ) THEN
1010
1011 -- Check the profile option value for collecting lowest tasks
1012
1013 IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y') THEN
1014
1015 -- Refresh the lowest tasks numbers
1016
1017 x_err_stage := 'Refreshing txns at lowest task level for service type change';
1018
1019 pa_debug.debug(x_err_stage);
1020
1021 FOR ref_lowest_ser_type_act_cmts_r IN sel_ref_lowest_stype_act_cmts(sel_prjs_r.project_id) LOOP
1022
1023 pa_debug.debug('Sending $0**** for lowest task_id ' || to_char(ref_lowest_ser_type_act_cmts_r.task_id) || ' For Service Type ' || ref_lowest_ser_type_act_cmts_r.service_type_code);
1024
1025 update_tasks_act_cmt
1026 (ref_lowest_ser_type_act_cmts_r.task_id,
1027 ref_lowest_ser_type_act_cmts_r.pa_period_key,
1028 ref_lowest_ser_type_act_cmts_r.expense_organization_id,
1029 ref_lowest_ser_type_act_cmts_r.owner_organization_id,
1030 ref_lowest_ser_type_act_cmts_r.resource_list_member_id,
1031 ref_lowest_ser_type_act_cmts_r.service_type_code,
1032 ref_lowest_ser_type_act_cmts_r.expenditure_type,
1033 ref_lowest_ser_type_act_cmts_r.user_col1,
1034 ref_lowest_ser_type_act_cmts_r.user_col2,
1035 ref_lowest_ser_type_act_cmts_r.user_col3,
1036 ref_lowest_ser_type_act_cmts_r.user_col4,
1037 ref_lowest_ser_type_act_cmts_r.user_col5,
1038 ref_lowest_ser_type_act_cmts_r.user_col6,
1039 ref_lowest_ser_type_act_cmts_r.user_col7,
1040 ref_lowest_ser_type_act_cmts_r.user_col8,
1041 ref_lowest_ser_type_act_cmts_r.user_col9,
1042 ref_lowest_ser_type_act_cmts_r.user_col10,
1043 ref_lowest_ser_type_act_cmts_r.accume_revenue,
1044 ref_lowest_ser_type_act_cmts_r.accume_raw_cost,
1045 ref_lowest_ser_type_act_cmts_r.accume_burdened_cost,
1046 ref_lowest_ser_type_act_cmts_r.accume_quantity,
1047 ref_lowest_ser_type_act_cmts_r.accume_labor_hours,
1048 ref_lowest_ser_type_act_cmts_r.accume_billable_raw_cost,
1049 ref_lowest_ser_type_act_cmts_r.accume_billable_burdened_cost,
1050 ref_lowest_ser_type_act_cmts_r.accume_billable_quantity,
1051 ref_lowest_ser_type_act_cmts_r.accume_billable_labor_hours,
1052 ref_lowest_ser_type_act_cmts_r.accume_cmt_raw_cost,
1053 ref_lowest_ser_type_act_cmts_r.accume_cmt_burdened_cost,
1054 ref_lowest_ser_type_act_cmts_r.accume_cmt_quantity,
1055 ref_lowest_ser_type_act_cmts_r.unit_of_measure,
1056 x_err_stage,
1057 x_err_stack,
1058 x_err_code);
1059
1063
1060 END LOOP; -- FOR ref_lowest_ser_type_act_cmts_r IN sel_ref_lowest_stype_act_cmts
1061
1062 END IF; -- IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y')
1064 -- Check the profile option value for collecting top tasks
1065
1066 IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y') THEN
1067
1068 -- Refresh the top tasks numbers
1069
1070 x_err_stage := 'Refreshing txns at top task level for service type change';
1071
1072 pa_debug.debug(x_err_stage);
1073
1074 FOR ref_top_ser_type_act_cmts_r IN sel_ref_top_ser_type_act_cmts(sel_prjs_r.project_id) LOOP
1075
1076 pa_debug.debug('Sending $0**** for top task_id ' || to_char(ref_top_ser_type_act_cmts_r.top_task_id) || ' For Service Type ' || ref_top_ser_type_act_cmts_r.service_type_code);
1077
1078 update_tasks_act_cmt
1079 (ref_top_ser_type_act_cmts_r.top_task_id,
1080 ref_top_ser_type_act_cmts_r.pa_period_key,
1081 ref_top_ser_type_act_cmts_r.expense_organization_id,
1082 ref_top_ser_type_act_cmts_r.owner_organization_id,
1083 ref_top_ser_type_act_cmts_r.resource_list_member_id,
1084 ref_top_ser_type_act_cmts_r.service_type_code,
1085 ref_top_ser_type_act_cmts_r.expenditure_type,
1086 ref_top_ser_type_act_cmts_r.user_col1,
1087 ref_top_ser_type_act_cmts_r.user_col2,
1088 ref_top_ser_type_act_cmts_r.user_col3,
1089 ref_top_ser_type_act_cmts_r.user_col4,
1090 ref_top_ser_type_act_cmts_r.user_col5,
1091 ref_top_ser_type_act_cmts_r.user_col6,
1092 ref_top_ser_type_act_cmts_r.user_col7,
1093 ref_top_ser_type_act_cmts_r.user_col8,
1094 ref_top_ser_type_act_cmts_r.user_col9,
1095 ref_top_ser_type_act_cmts_r.user_col10,
1096 ref_top_ser_type_act_cmts_r.accume_revenue,
1097 ref_top_ser_type_act_cmts_r.accume_raw_cost,
1098 ref_top_ser_type_act_cmts_r.accume_burdened_cost,
1099 ref_top_ser_type_act_cmts_r.accume_quantity,
1100 ref_top_ser_type_act_cmts_r.accume_labor_hours,
1101 ref_top_ser_type_act_cmts_r.accume_billable_raw_cost,
1102 ref_top_ser_type_act_cmts_r.accume_billable_burdened_cost,
1103 ref_top_ser_type_act_cmts_r.accume_billable_quantity,
1104 ref_top_ser_type_act_cmts_r.accume_billable_labor_hours,
1105 ref_top_ser_type_act_cmts_r.accume_cmt_raw_cost,
1106 ref_top_ser_type_act_cmts_r.accume_cmt_burdened_cost,
1107 ref_top_ser_type_act_cmts_r.accume_cmt_quantity,
1108 ref_top_ser_type_act_cmts_r.unit_of_measure,
1109 x_err_stage,
1110 x_err_stack,
1111 x_err_code);
1112
1113 END LOOP; -- FOR ref_top_ser_type_act_cmts_r IN sel_ref_top_ser_type_act_cmts
1114
1115 END IF; -- IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y')
1116
1117 -- Refresh the project level Numbers
1118
1119 x_err_stage := 'Refreshing txns at Project level';
1120
1121 pa_debug.debug(x_err_stage);
1122
1123 FOR ref_prj_ser_type_act_cmts_r IN sel_ref_prj_ser_type_act_cmts(sel_prjs_r.project_id) LOOP
1124
1125 pa_debug.debug('Sending $0***** for project_id ' || to_char(ref_prj_ser_type_act_cmts_r.project_id) || ' For Service Type ' || ref_prj_ser_type_act_cmts_r.service_type_code);
1126
1127 update_prj_act_cmt
1128 (ref_prj_ser_type_act_cmts_r.project_id,
1129 ref_prj_ser_type_act_cmts_r.pa_period_key,
1130 ref_prj_ser_type_act_cmts_r.expense_organization_id,
1131 ref_prj_ser_type_act_cmts_r.owner_organization_id,
1132 ref_prj_ser_type_act_cmts_r.resource_list_member_id,
1133 ref_prj_ser_type_act_cmts_r.service_type_code,
1134 ref_prj_ser_type_act_cmts_r.expenditure_type,
1135 ref_prj_ser_type_act_cmts_r.user_col1,
1136 ref_prj_ser_type_act_cmts_r.user_col2,
1137 ref_prj_ser_type_act_cmts_r.user_col3,
1138 ref_prj_ser_type_act_cmts_r.user_col4,
1139 ref_prj_ser_type_act_cmts_r.user_col5,
1140 ref_prj_ser_type_act_cmts_r.user_col6,
1141 ref_prj_ser_type_act_cmts_r.user_col7,
1142 ref_prj_ser_type_act_cmts_r.user_col8,
1143 ref_prj_ser_type_act_cmts_r.user_col9,
1144 ref_prj_ser_type_act_cmts_r.user_col10,
1145 ref_prj_ser_type_act_cmts_r.accume_revenue,
1146 ref_prj_ser_type_act_cmts_r.accume_raw_cost,
1147 ref_prj_ser_type_act_cmts_r.accume_burdened_cost,
1148 ref_prj_ser_type_act_cmts_r.accume_quantity,
1149 ref_prj_ser_type_act_cmts_r.accume_labor_hours,
1150 ref_prj_ser_type_act_cmts_r.accume_billable_raw_cost,
1151 ref_prj_ser_type_act_cmts_r.accume_billable_burdened_cost,
1152 ref_prj_ser_type_act_cmts_r.accume_billable_quantity,
1153 ref_prj_ser_type_act_cmts_r.accume_billable_labor_hours,
1154 ref_prj_ser_type_act_cmts_r.accume_cmt_raw_cost,
1155 ref_prj_ser_type_act_cmts_r.accume_cmt_burdened_cost,
1156 ref_prj_ser_type_act_cmts_r.accume_cmt_quantity,
1157 ref_prj_ser_type_act_cmts_r.unit_of_measure,
1158 x_err_stage,
1159 x_err_stack,
1160 x_err_code);
1161
1162 END LOOP; -- FOR ref_prj_ser_type_act_cmts_r IN sel_ref_prj_ser_type_act_cmts
1163
1164 -- Mark the rows in PA_TXN_ACCUM table for re-transfer
1165
1166 UPDATE PA_TXN_ACCUM PTA
1167 SET ADW_NOTIFY_FLAG = 'Y'
1168 WHERE
1169 TASK_ID IN
1170 (SELECT TASK_ID
1171 FROM PA_TASK_HISTORY PTH
1172 WHERE PTH.TASK_ID = PTA.TASK_ID
1176 pa_debug.debug('Marked '||TO_CHAR(SQL%ROWCOUNT)||' Txn Accum rows for re-transfer due to service type change alone');
1173 AND PTH.ADW_NOTIFY_FLAG IN ('S','P')
1174 );
1175
1177
1178 UPDATE
1179 PA_TASK_HISTORY
1180 SET
1181 ADW_NOTIFY_FLAG = 'N'
1182 WHERE
1183 ADW_NOTIFY_FLAG = 'S';
1184
1185 UPDATE
1186 PA_TASK_HISTORY
1187 SET
1188 ADW_NOTIFY_FLAG = 'Y'
1189 WHERE
1190 ADW_NOTIFY_FLAG = 'P';
1191
1192 END IF; -- IF ( ref_ser_type_count <> 0 )
1193
1194
1195 /* Txns refresh completed */
1196
1197 -- First mark all the rows need to be transferred
1198
1199 UPDATE
1200 PA_RESOURCE_ACCUM_DETAILS PRAD
1201 SET
1202 PRAD.ADW_NOTIFY_FLAG = 'S'
1203 WHERE
1204 PRAD.PROJECT_ID = SEL_PRJS_R.PROJECT_ID
1205 AND PRAD.ADW_NOTIFY_FLAG = 'Y'
1206 AND EXISTS
1207 ( SELECT 'Yes'
1208 FROM
1209 PA_ADW_RES_LISTS_V PRL
1210 WHERE PRAD.RESOURCE_LIST_ID = PRL.RESOURCE_LIST_ID
1211 );
1212
1213 txn_count := SQL%ROWCOUNT;
1214
1215 UPDATE
1216 PA_TXN_ACCUM
1217 SET
1218 ADW_NOTIFY_FLAG = 'S'
1219 WHERE
1220 PROJECT_ID = sel_prjs_r.project_id
1221 AND ADW_NOTIFY_FLAG = 'Y';
1222
1223 res_count := SQL%ROWCOUNT;
1224
1225 IF ( txn_count <> 0 OR res_count <> 0 ) THEN
1226
1227 -- Check the profile option value for collecting lowest tasks
1228
1229 IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y') THEN
1230
1231 -- collect Actuals/Cmts at lowest tasks level
1232
1233 x_err_stage := 'Collecting Actuals/Cmts txns at lowest task level';
1234
1235 pa_debug.debug(x_err_stage);
1236
1237 FOR lowest_act_cmts_r IN sel_lowest_act_cmts(sel_prjs_r.project_id) LOOP
1238
1239 pa_debug.debug('Sending for lowest task_id ' || to_char(lowest_act_cmts_r.task_id) || ' For Service Type ' || lowest_act_cmts_r.service_type_code);
1240
1241
1242 update_tasks_act_cmt
1243 (lowest_act_cmts_r.task_id,
1244 lowest_act_cmts_r.pa_period_key,
1245 lowest_act_cmts_r.expense_organization_id,
1246 lowest_act_cmts_r.owner_organization_id,
1247 lowest_act_cmts_r.resource_list_member_id,
1248 lowest_act_cmts_r.service_type_code,
1249 lowest_act_cmts_r.expenditure_type,
1250 lowest_act_cmts_r.user_col1,
1251 lowest_act_cmts_r.user_col2,
1252 lowest_act_cmts_r.user_col3,
1253 lowest_act_cmts_r.user_col4,
1254 lowest_act_cmts_r.user_col5,
1255 lowest_act_cmts_r.user_col6,
1256 lowest_act_cmts_r.user_col7,
1257 lowest_act_cmts_r.user_col8,
1258 lowest_act_cmts_r.user_col9,
1259 lowest_act_cmts_r.user_col10,
1260 lowest_act_cmts_r.accume_revenue,
1261 lowest_act_cmts_r.accume_raw_cost,
1262 lowest_act_cmts_r.accume_burdened_cost,
1263 lowest_act_cmts_r.accume_quantity,
1264 lowest_act_cmts_r.accume_labor_hours,
1265 lowest_act_cmts_r.accume_billable_raw_cost,
1266 lowest_act_cmts_r.accume_billable_burdened_cost,
1267 lowest_act_cmts_r.accume_billable_quantity,
1268 lowest_act_cmts_r.accume_billable_labor_hours,
1269 lowest_act_cmts_r.accume_cmt_raw_cost,
1270 lowest_act_cmts_r.accume_cmt_burdened_cost,
1271 lowest_act_cmts_r.accume_cmt_quantity,
1272 lowest_act_cmts_r.unit_of_measure,
1273 x_err_stage,
1274 x_err_stack,
1275 x_err_code);
1276
1277 END LOOP; -- FOR lowest_act_cmts_r IN sel_lowest_act_cmts
1278
1279 END IF; -- IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y')
1280
1281 -- Check the profile option value for collecting top tasks
1282
1283 IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y') THEN
1284
1285 -- collect Actuals/Cmts at top tasks level
1286
1287 x_err_stage := 'Collecting Actuals/Cmts txns at top task level';
1288
1289 pa_debug.debug(x_err_stage);
1290
1291 FOR top_act_cmts_r IN sel_top_act_cmts(sel_prjs_r.project_id) LOOP
1292
1293 pa_debug.debug('Sending for top task_id ' || to_char(top_act_cmts_r.top_task_id) || ' For Service Type ' || top_act_cmts_r.service_type_code);
1294
1295 update_tasks_act_cmt
1296 (top_act_cmts_r.top_task_id,
1297 top_act_cmts_r.pa_period_key,
1298 top_act_cmts_r.expense_organization_id,
1299 top_act_cmts_r.owner_organization_id,
1300 top_act_cmts_r.resource_list_member_id,
1301 top_act_cmts_r.service_type_code,
1302 top_act_cmts_r.expenditure_type,
1303 top_act_cmts_r.user_col1,
1304 top_act_cmts_r.user_col2,
1305 top_act_cmts_r.user_col3,
1306 top_act_cmts_r.user_col4,
1307 top_act_cmts_r.user_col5,
1308 top_act_cmts_r.user_col6,
1309 top_act_cmts_r.user_col7,
1310 top_act_cmts_r.user_col8,
1311 top_act_cmts_r.user_col9,
1312 top_act_cmts_r.user_col10,
1313 top_act_cmts_r.accume_revenue,
1314 top_act_cmts_r.accume_raw_cost,
1315 top_act_cmts_r.accume_burdened_cost,
1316 top_act_cmts_r.accume_quantity,
1320 top_act_cmts_r.accume_billable_quantity,
1317 top_act_cmts_r.accume_labor_hours,
1318 top_act_cmts_r.accume_billable_raw_cost,
1319 top_act_cmts_r.accume_billable_burdened_cost,
1321 top_act_cmts_r.accume_billable_labor_hours,
1322 top_act_cmts_r.accume_cmt_raw_cost,
1323 top_act_cmts_r.accume_cmt_burdened_cost,
1324 top_act_cmts_r.accume_cmt_quantity,
1325 top_act_cmts_r.unit_of_measure,
1326 x_err_stage,
1327 x_err_stack,
1328 x_err_code);
1329
1330 END LOOP; -- FOR top_act_cmts_r IN sel_top_act_cmts
1331
1332 END IF; -- IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y')
1333
1334 -- collect Actuals/Cmts at Project level
1335
1336 x_err_stage := 'Collecting Actuals/Cmts txns at Project level';
1337
1338 pa_debug.debug(x_err_stage);
1339
1340 FOR prj_act_cmts_r IN sel_prj_act_cmts(sel_prjs_r.project_id) LOOP
1341 pa_debug.debug('Sending for project_id ' || to_char(prj_act_cmts_r.project_id) || ' For Service Type ' || prj_act_cmts_r.service_type_code);
1342
1343
1344 update_prj_act_cmt
1345 (prj_act_cmts_r.project_id,
1346 prj_act_cmts_r.pa_period_key,
1347 prj_act_cmts_r.expense_organization_id,
1348 prj_act_cmts_r.owner_organization_id,
1349 prj_act_cmts_r.resource_list_member_id,
1350 prj_act_cmts_r.service_type_code,
1351 prj_act_cmts_r.expenditure_type,
1352 prj_act_cmts_r.user_col1,
1353 prj_act_cmts_r.user_col2,
1354 prj_act_cmts_r.user_col3,
1355 prj_act_cmts_r.user_col4,
1356 prj_act_cmts_r.user_col5,
1357 prj_act_cmts_r.user_col6,
1358 prj_act_cmts_r.user_col7,
1359 prj_act_cmts_r.user_col8,
1360 prj_act_cmts_r.user_col9,
1361 prj_act_cmts_r.user_col10,
1362 prj_act_cmts_r.accume_revenue,
1363 prj_act_cmts_r.accume_raw_cost,
1364 prj_act_cmts_r.accume_burdened_cost,
1365 prj_act_cmts_r.accume_quantity,
1366 prj_act_cmts_r.accume_labor_hours,
1367 prj_act_cmts_r.accume_billable_raw_cost,
1368 prj_act_cmts_r.accume_billable_burdened_cost,
1369 prj_act_cmts_r.accume_billable_quantity,
1370 prj_act_cmts_r.accume_billable_labor_hours,
1371 prj_act_cmts_r.accume_cmt_raw_cost,
1372 prj_act_cmts_r.accume_cmt_burdened_cost,
1373 prj_act_cmts_r.accume_cmt_quantity,
1374 prj_act_cmts_r.unit_of_measure,
1375 x_err_stage,
1376 x_err_stack,
1377 x_err_code);
1378
1379 END LOOP; -- FOR prj_act_cmts_r IN sel_prj_act_cmts
1380
1381 -- Mark the PA_TASK_HISTORY rows which were transferred
1382 -- Both for Low level task as well as top level tasks
1383
1384 UPDATE
1385 PA_TASK_HISTORY PTH
1386 SET
1387 ADW_INTERFACE_FLAG = 'Y'
1388 WHERE
1389 ADW_NOTIFY_FLAG = 'Y'
1390 AND PTH.TASK_ID IN
1391 (SELECT TASK_ID
1392 FROM PA_RESOURCE_ACCUM_DETAILS
1393 WHERE ADW_NOTIFY_FLAG = 'S'
1394 UNION
1395 SELECT TASK_ID
1396 FROM PA_TXN_ACCUM
1397 WHERE ADW_NOTIFY_FLAG = 'S'
1398 );
1399 pa_debug.debug('Marked '||TO_CHAR(SQL%ROWCOUNT)||' task rows transferred to interface table');
1400
1401 UPDATE
1402 PA_TASK_HISTORY PTH
1403 SET
1404 ADW_INTERFACE_FLAG = 'Y'
1405 WHERE
1406 ADW_NOTIFY_FLAG = 'Y'
1407 AND PTH.TASK_ID IN
1408 (SELECT TOP_TASK_ID FROM PA_TASK_HISTORY PTHT
1409 WHERE
1410 PTHT.ADW_NOTIFY_FLAG = 'Y'
1411 AND PTHT.TASK_ID IN
1412 (SELECT TASK_ID
1413 FROM PA_RESOURCE_ACCUM_DETAILS
1414 WHERE ADW_NOTIFY_FLAG = 'S'
1415 UNION
1416 SELECT TASK_ID
1417 FROM PA_TXN_ACCUM
1418 WHERE ADW_NOTIFY_FLAG = 'S'
1419 )
1420 );
1421 pa_debug.debug('Marked '||TO_CHAR(SQL%ROWCOUNT)||' top task rows transferred to interface table');
1422
1423 -- PLEASE NOTE THAT WE ARE UPDATING THE BASE TABLE SINCE THE
1424 -- PA_ADW_R_ACT_CMT_V IS DEFINED ON MULTIPLE TABLES
1425
1426 UPDATE
1427 PA_RESOURCE_ACCUM_DETAILS
1428 SET
1429 ADW_NOTIFY_FLAG = 'N'
1430 WHERE
1431 ADW_NOTIFY_FLAG = 'S';
1432
1433 UPDATE
1434 PA_TXN_ACCUM
1435 SET
1436 ADW_NOTIFY_FLAG = 'N'
1437 WHERE
1438 ADW_NOTIFY_FLAG = 'S';
1439
1440 END IF; -- IF (txn_count <> 0 OR res_count <> 0)
1441
1442 UPDATE PA_TASK_HISTORY PTH
1443 SET ADW_NOTIFY_FLAG = 'N'
1444 WHERE
1445 ADW_NOTIFY_FLAG = 'Y';
1446
1447 pa_debug.debug('Marked ' || TO_CHAR(SQL%ROWCOUNT) || ' task rows Not transferred to interface table');
1448 -- Commit the project
1449 COMMIT;
1450 END LOOP; -- FOR sel_prjs_r IN sel_prjs
1451
1452 x_err_stack := x_old_err_stack;
1453
1454 pa_debug.debug('Completed ' || x_err_stage);
1455
1456 EXCEPTION
1457 WHEN OTHERS THEN
1458 x_err_code := SQLCODE;
1459 RAISE;
1460 END get_fact_act_cmts;
1461
1465 x_expense_organization_id IN NUMBER,
1462 PROCEDURE update_tasks_act_cmt
1463 (x_task_id IN NUMBER,
1464 x_pa_period_key IN VARCHAR2,
1466 x_owner_organization_id IN NUMBER,
1467 x_resource_list_member_id IN NUMBER,
1468 x_service_type_code IN VARCHAR2,
1469 x_expenditure_type IN VARCHAR2,
1470 x_user_col1 IN VARCHAR2,
1471 x_user_col2 IN VARCHAR2,
1472 x_user_col3 IN VARCHAR2,
1473 x_user_col4 IN VARCHAR2,
1474 x_user_col5 IN VARCHAR2,
1475 x_user_col6 IN VARCHAR2,
1476 x_user_col7 IN VARCHAR2,
1477 x_user_col8 IN VARCHAR2,
1478 x_user_col9 IN VARCHAR2,
1479 x_user_col10 IN VARCHAR2,
1480 x_accume_revenue IN NUMBER,
1481 x_accume_raw_cost IN NUMBER,
1482 x_accume_burdened_cost IN NUMBER,
1483 x_accume_quantity IN NUMBER,
1484 x_accume_labor_hours IN NUMBER,
1485 x_accume_billable_raw_cost IN NUMBER,
1486 x_acc_billable_burdened_cost IN NUMBER,
1487 x_accume_billable_quantity IN NUMBER,
1488 x_accume_billable_labor_hours IN NUMBER,
1489 x_accume_cmt_raw_cost IN NUMBER,
1490 x_accume_cmt_burdened_cost IN NUMBER,
1491 x_accume_cmt_quantity IN NUMBER,
1492 x_unit_of_measure IN VARCHAR2,
1493 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1494 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1495 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
1496 IS
1497 x_old_err_stack VARCHAR2(1024);
1498 BEGIN
1499 x_err_code := 0;
1500 x_err_stage := 'Creating Task Level Actuals and Commitments Table';
1501 x_old_err_stack := x_err_stack;
1502 x_err_stack := x_err_stack || '-> update_tasks_act_cmt';
1503
1504 -- First Try to Update the Row in the Interface Table
1505
1506 UPDATE
1507 PA_TSK_ACT_CMT_IT
1508 SET
1509 LAST_UPDATE_DATE = TRUNC(SYSDATE),
1510 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1511 CREATION_DATE = TRUNC(SYSDATE),
1512 CREATED_BY = X_CREATED_BY,
1513 USER_COL6 = X_USER_COL6,
1514 USER_COL7 = X_USER_COL7,
1515 USER_COL8 = X_USER_COL8,
1516 USER_COL9 = X_USER_COL9,
1517 USER_COL10 = X_USER_COL10,
1518 ACCUME_REVENUE = X_ACCUME_REVENUE,
1519 ACCUME_RAW_COST = X_ACCUME_RAW_COST,
1520 ACCUME_BURDENED_COST = X_ACCUME_BURDENED_COST,
1521 ACCUME_QUANTITY = X_ACCUME_QUANTITY,
1522 ACCUME_LABOR_HOURS = X_ACCUME_LABOR_HOURS,
1523 ACCUME_BILLABLE_RAW_COST = X_ACCUME_BILLABLE_RAW_COST,
1524 ACCUME_BILLABLE_BURDENED_COST = X_ACC_BILLABLE_BURDENED_COST,
1525 ACCUME_BILLABLE_QUANTITY = X_ACCUME_BILLABLE_QUANTITY,
1526 ACCUME_BILLABLE_LABOR_HOURS = X_ACCUME_BILLABLE_LABOR_HOURS,
1527 ACCUME_CMT_RAW_COST = X_ACCUME_CMT_RAW_COST,
1528 ACCUME_CMT_BURDENED_COST = X_ACCUME_CMT_BURDENED_COST,
1529 ACCUME_CMT_QUANTITY = X_ACCUME_CMT_QUANTITY,
1530 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1531 REQUEST_ID = X_REQUEST_ID,
1532 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1533 PROGRAM_ID = X_PROGRAM_ID,
1534 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
1535 STATUS_CODE = 'P'
1536 WHERE
1537 TASK_ID = X_TASK_ID
1538 AND PA_PERIOD_KEY = X_PA_PERIOD_KEY
1539 AND NVL(EXPENSE_ORGANIZATION_ID,-99) = NVL(X_EXPENSE_ORGANIZATION_ID,-99)
1540 AND NVL(OWNER_ORGANIZATION_ID,-99) = NVL(X_OWNER_ORGANIZATION_ID,-99)
1541 AND NVL(RESOURCE_LIST_MEMBER_ID,-99) = NVL(X_RESOURCE_LIST_MEMBER_ID,-99)
1542 AND NVL(SERVICE_TYPE_CODE,'X') = NVL(X_SERVICE_TYPE_CODE,'X')
1543 AND NVL(EXPENDITURE_TYPE,'X') = NVL(X_EXPENDITURE_TYPE,'X')
1544 AND NVL(USER_COL1,'X') = NVL(X_USER_COL1,'X')
1545 AND NVL(USER_COL2,'X') = NVL(X_USER_COL2,'X')
1546 AND NVL(USER_COL3,'X') = NVL(X_USER_COL3,'X')
1547 AND NVL(USER_COL4,'X') = NVL(X_USER_COL4,'X')
1548 AND NVL(USER_COL5,'X') = NVL(X_USER_COL5,'X')
1549 AND NVL(UNIT_OF_MEASURE,'X') = NVL(X_UNIT_OF_MEASURE,'X');
1550
1551 -- Check If Any row was updated
1552
1553 IF (SQL%ROWCOUNT = 0) THEN
1554
1555 -- No row was updated, So Insert a new row into the interface table
1556 INSERT INTO PA_TSK_ACT_CMT_IT
1557 (
1558 TASK_ACT_CMT_KEY,
1559 TASK_ID,
1560 PA_PERIOD_KEY,
1561 LAST_UPDATE_DATE,
1562 LAST_UPDATED_BY,
1563 CREATION_DATE,
1564 CREATED_BY,
1565 EXPENSE_ORGANIZATION_ID,
1566 OWNER_ORGANIZATION_ID,
1567 RESOURCE_LIST_MEMBER_ID,
1568 SERVICE_TYPE_CODE,
1569 EXPENDITURE_TYPE,
1570 USER_COL1,
1571 USER_COL2,
1572 USER_COL3,
1573 USER_COL4,
1574 USER_COL5,
1575 USER_COL6,
1576 USER_COL7,
1577 USER_COL8,
1578 USER_COL9,
1579 USER_COL10,
1580 ACCUME_REVENUE,
1581 ACCUME_RAW_COST,
1582 ACCUME_BURDENED_COST,
1583 ACCUME_QUANTITY,
1584 ACCUME_LABOR_HOURS,
1585 ACCUME_BILLABLE_RAW_COST,
1586 ACCUME_BILLABLE_BURDENED_COST,
1587 ACCUME_BILLABLE_QUANTITY,
1588 ACCUME_BILLABLE_LABOR_HOURS,
1589 ACCUME_CMT_RAW_COST,
1590 ACCUME_CMT_BURDENED_COST,
1591 ACCUME_CMT_QUANTITY,
1592 UNIT_OF_MEASURE,
1593 LAST_UPDATE_LOGIN,
1594 REQUEST_ID,
1598 STATUS_CODE
1595 PROGRAM_APPLICATION_ID,
1596 PROGRAM_ID,
1597 PROGRAM_UPDATE_DATE,
1599 )
1600 VALUES
1601 (
1602 X_TASK_ID || '-' || X_PA_PERIOD_KEY || '-' || --
1603 NVL(X_EXPENSE_ORGANIZATION_ID,-99) || '-' || --|
1604 NVL(X_OWNER_ORGANIZATION_ID,-99)|| '-' || --|
1605 NVL(X_RESOURCE_LIST_MEMBER_ID,-99)|| '-' || --|
1606 NVL(X_SERVICE_TYPE_CODE,'X')|| '-' || --| Dimension Keys
1607 NVL(X_EXPENDITURE_TYPE,'X')|| '-' || --|
1608 NVL(X_USER_COL1,'X')|| '-' || --|
1609 NVL(X_USER_COL2,'X')|| '-' || --|
1610 NVL(X_USER_COL3,'X')|| '-' || --|
1611 NVL(X_USER_COL4,'X')|| '-' || --|
1612 NVL(X_USER_COL5,'X'), --
1613 X_TASK_ID,
1614 X_PA_PERIOD_KEY,
1615 TRUNC(SYSDATE),
1616 X_LAST_UPDATED_BY,
1617 TRUNC(SYSDATE),
1618 X_CREATED_BY,
1619 X_EXPENSE_ORGANIZATION_ID,
1620 X_OWNER_ORGANIZATION_ID,
1621 X_RESOURCE_LIST_MEMBER_ID,
1622 X_SERVICE_TYPE_CODE,
1623 X_EXPENDITURE_TYPE,
1624 X_USER_COL1,
1625 X_USER_COL2,
1626 X_USER_COL3,
1627 X_USER_COL4,
1628 X_USER_COL5,
1629 X_USER_COL6,
1630 X_USER_COL7,
1631 X_USER_COL8,
1632 X_USER_COL9,
1633 X_USER_COL10,
1634 X_ACCUME_REVENUE,
1635 X_ACCUME_RAW_COST,
1636 X_ACCUME_BURDENED_COST,
1637 X_ACCUME_QUANTITY,
1638 X_ACCUME_LABOR_HOURS,
1639 X_ACCUME_BILLABLE_RAW_COST,
1640 X_ACC_BILLABLE_BURDENED_COST,
1641 X_ACCUME_BILLABLE_QUANTITY,
1642 X_ACCUME_BILLABLE_LABOR_HOURS,
1643 X_ACCUME_CMT_RAW_COST,
1644 X_ACCUME_CMT_BURDENED_COST,
1645 X_ACCUME_CMT_QUANTITY,
1646 X_UNIT_OF_MEASURE,
1647 X_LAST_UPDATE_LOGIN,
1648 X_REQUEST_ID,
1649 X_PROGRAM_APPLICATION_ID,
1650 X_PROGRAM_ID,
1651 TRUNC(SYSDATE),
1652 'P'
1653 );
1654
1655 END IF; -- IF ( SQL%ROWCOUNT = 0 )
1656
1657 x_err_stack := x_old_err_stack;
1658
1659 EXCEPTION
1660 WHEN OTHERS THEN
1661 x_err_code := SQLCODE;
1662 RAISE;
1663 END update_tasks_act_cmt;
1664
1665 -- Update the project level numbers
1666
1667 PROCEDURE update_prj_act_cmt
1668 (x_project_id IN NUMBER,
1669 x_pa_period_key IN VARCHAR2,
1670 x_expense_organization_id IN NUMBER,
1671 x_owner_organization_id IN NUMBER,
1672 x_resource_list_member_id IN NUMBER,
1673 x_service_type_code IN VARCHAR2,
1674 x_expenditure_type IN VARCHAR2,
1675 x_user_col1 IN VARCHAR2,
1676 x_user_col2 IN VARCHAR2,
1677 x_user_col3 IN VARCHAR2,
1678 x_user_col4 IN VARCHAR2,
1679 x_user_col5 IN VARCHAR2,
1680 x_user_col6 IN VARCHAR2,
1681 x_user_col7 IN VARCHAR2,
1682 x_user_col8 IN VARCHAR2,
1683 x_user_col9 IN VARCHAR2,
1684 x_user_col10 IN VARCHAR2,
1685 x_accume_revenue IN NUMBER,
1686 x_accume_raw_cost IN NUMBER,
1687 x_accume_burdened_cost IN NUMBER,
1688 x_accume_quantity IN NUMBER,
1689 x_accume_labor_hours IN NUMBER,
1690 x_accume_billable_raw_cost IN NUMBER,
1691 x_acc_billable_burdened_cost IN NUMBER,
1692 x_accume_billable_quantity IN NUMBER,
1693 x_accume_billable_labor_hours IN NUMBER,
1694 x_accume_cmt_raw_cost IN NUMBER,
1695 x_accume_cmt_burdened_cost IN NUMBER,
1696 x_accume_cmt_quantity IN NUMBER,
1697 x_unit_of_measure IN VARCHAR2,
1698 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1699 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1700 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
1701 IS
1702 x_old_err_stack VARCHAR2(1024);
1703 BEGIN
1704 x_err_code := 0;
1705 x_err_stage := 'Creating Project Level Actuals and Commitments Table';
1706 x_old_err_stack := x_err_stack;
1707 x_err_stack := x_err_stack || '-> update_prj_act_cmt';
1708
1709 -- First Try to Update the Row in the Interface Table
1710
1711 UPDATE
1712 PA_PRJ_ACT_CMT_IT
1713 SET
1714 LAST_UPDATE_DATE = TRUNC(SYSDATE),
1715 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1716 CREATION_DATE = TRUNC(SYSDATE),
1717 CREATED_BY = X_CREATED_BY,
1718 USER_COL6 = X_USER_COL6,
1719 USER_COL7 = X_USER_COL7,
1720 USER_COL8 = X_USER_COL8,
1721 USER_COL9 = X_USER_COL9,
1722 USER_COL10 = X_USER_COL10,
1723 ACCUME_REVENUE = X_ACCUME_REVENUE,
1724 ACCUME_RAW_COST = X_ACCUME_RAW_COST,
1725 ACCUME_BURDENED_COST = X_ACCUME_BURDENED_COST,
1726 ACCUME_QUANTITY = X_ACCUME_QUANTITY,
1727 ACCUME_LABOR_HOURS = X_ACCUME_LABOR_HOURS,
1728 ACCUME_BILLABLE_RAW_COST = X_ACCUME_BILLABLE_RAW_COST,
1729 ACCUME_BILLABLE_BURDENED_COST = X_ACC_BILLABLE_BURDENED_COST,
1730 ACCUME_BILLABLE_QUANTITY = X_ACCUME_BILLABLE_QUANTITY,
1731 ACCUME_BILLABLE_LABOR_HOURS = X_ACCUME_BILLABLE_LABOR_HOURS,
1732 ACCUME_CMT_RAW_COST = X_ACCUME_CMT_RAW_COST,
1736 REQUEST_ID = X_REQUEST_ID,
1733 ACCUME_CMT_BURDENED_COST = X_ACCUME_CMT_BURDENED_COST,
1734 ACCUME_CMT_QUANTITY = X_ACCUME_CMT_QUANTITY,
1735 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1737 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1738 PROGRAM_ID = X_PROGRAM_ID,
1739 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
1740 STATUS_CODE = 'P'
1741 WHERE
1742 PROJECT_ID = X_PROJECT_ID
1743 AND PA_PERIOD_KEY = X_PA_PERIOD_KEY
1744 AND NVL(EXPENSE_ORGANIZATION_ID,-99) = NVL(X_EXPENSE_ORGANIZATION_ID,-99)
1745 AND NVL(OWNER_ORGANIZATION_ID,-99) = NVL(X_OWNER_ORGANIZATION_ID,-99)
1746 AND NVL(RESOURCE_LIST_MEMBER_ID,-99) = NVL(X_RESOURCE_LIST_MEMBER_ID,-99)
1747 AND NVL(SERVICE_TYPE_CODE,'X') = NVL(X_SERVICE_TYPE_CODE,'X')
1748 AND NVL(EXPENDITURE_TYPE,'X') = NVL(X_EXPENDITURE_TYPE,'X')
1749 AND NVL(USER_COL1,'X') = NVL(X_USER_COL1,'X')
1750 AND NVL(USER_COL2,'X') = NVL(X_USER_COL2,'X')
1751 AND NVL(USER_COL3,'X') = NVL(X_USER_COL3,'X')
1752 AND NVL(USER_COL4,'X') = NVL(X_USER_COL4,'X')
1753 AND NVL(USER_COL5,'X') = NVL(X_USER_COL5,'X')
1754 AND NVL(UNIT_OF_MEASURE,'X') = NVL(X_UNIT_OF_MEASURE,'X');
1755
1756 -- Check If Any row was updated
1757
1758 IF (SQL%ROWCOUNT = 0) THEN
1759 -- No row was updated, So Insert a new row into the interface table
1760 INSERT INTO PA_PRJ_ACT_CMT_IT
1761 (
1762 PRJ_ACT_CMT_KEY,
1763 PROJECT_ID,
1764 PA_PERIOD_KEY,
1765 LAST_UPDATE_DATE,
1766 LAST_UPDATED_BY,
1767 CREATION_DATE,
1768 CREATED_BY,
1769 EXPENSE_ORGANIZATION_ID,
1770 OWNER_ORGANIZATION_ID,
1771 RESOURCE_LIST_MEMBER_ID,
1772 SERVICE_TYPE_CODE,
1773 EXPENDITURE_TYPE,
1774 USER_COL1,
1775 USER_COL2,
1776 USER_COL3,
1777 USER_COL4,
1778 USER_COL5,
1779 USER_COL6,
1780 USER_COL7,
1781 USER_COL8,
1782 USER_COL9,
1783 USER_COL10,
1784 ACCUME_REVENUE,
1785 ACCUME_RAW_COST,
1786 ACCUME_BURDENED_COST,
1787 ACCUME_QUANTITY,
1788 ACCUME_LABOR_HOURS,
1789 ACCUME_BILLABLE_RAW_COST,
1790 ACCUME_BILLABLE_BURDENED_COST,
1791 ACCUME_BILLABLE_QUANTITY,
1792 ACCUME_BILLABLE_LABOR_HOURS,
1793 ACCUME_CMT_RAW_COST,
1794 ACCUME_CMT_BURDENED_COST,
1795 ACCUME_CMT_QUANTITY,
1796 UNIT_OF_MEASURE,
1797 LAST_UPDATE_LOGIN,
1798 REQUEST_ID,
1799 PROGRAM_APPLICATION_ID,
1800 PROGRAM_ID,
1801 PROGRAM_UPDATE_DATE,
1802 STATUS_CODE
1803 )
1804 VALUES
1805 (
1806 X_PROJECT_ID || '-' || X_PA_PERIOD_KEY || '-' || --
1807 NVL(X_EXPENSE_ORGANIZATION_ID,-99) || '-' || --|
1808 NVL(X_OWNER_ORGANIZATION_ID,-99)|| '-' || --|
1809 NVL(X_RESOURCE_LIST_MEMBER_ID,-99)|| '-' || --|
1810 NVL(X_SERVICE_TYPE_CODE,'X')|| '-' || --| Dimension Keys
1811 NVL(X_EXPENDITURE_TYPE,'X')|| '-' || --|
1812 NVL(X_USER_COL1,'X')|| '-' || --|
1813 NVL(X_USER_COL2,'X')|| '-' || --|
1814 NVL(X_USER_COL3,'X')|| '-' || --|
1815 NVL(X_USER_COL4,'X')|| '-' || --|
1816 NVL(X_USER_COL5,'X'), --
1817 X_PROJECT_ID,
1818 X_PA_PERIOD_KEY,
1819 TRUNC(SYSDATE),
1820 X_LAST_UPDATED_BY,
1821 TRUNC(SYSDATE),
1822 X_CREATED_BY,
1823 X_EXPENSE_ORGANIZATION_ID,
1824 X_OWNER_ORGANIZATION_ID,
1825 X_RESOURCE_LIST_MEMBER_ID,
1826 X_SERVICE_TYPE_CODE,
1827 X_EXPENDITURE_TYPE,
1828 X_USER_COL1,
1829 X_USER_COL2,
1830 X_USER_COL3,
1831 X_USER_COL4,
1832 X_USER_COL5,
1833 X_USER_COL6,
1834 X_USER_COL7,
1835 X_USER_COL8,
1836 X_USER_COL9,
1837 X_USER_COL10,
1838 X_ACCUME_REVENUE,
1839 X_ACCUME_RAW_COST,
1840 X_ACCUME_BURDENED_COST,
1841 X_ACCUME_QUANTITY,
1842 X_ACCUME_LABOR_HOURS,
1843 X_ACCUME_BILLABLE_RAW_COST,
1844 X_ACC_BILLABLE_BURDENED_COST,
1845 X_ACCUME_BILLABLE_QUANTITY,
1846 X_ACCUME_BILLABLE_LABOR_HOURS,
1847 X_ACCUME_CMT_RAW_COST,
1848 X_ACCUME_CMT_BURDENED_COST,
1849 X_ACCUME_CMT_QUANTITY,
1850 X_UNIT_OF_MEASURE,
1851 X_LAST_UPDATE_LOGIN,
1852 X_REQUEST_ID,
1853 X_PROGRAM_APPLICATION_ID,
1854 X_PROGRAM_ID,
1855 TRUNC(SYSDATE),
1856 'P'
1857 );
1858
1859 END IF; -- IF ( SQL%ROWCOUNT = 0 )
1860
1861 x_err_stack := x_old_err_stack;
1862
1863 EXCEPTION
1864 WHEN OTHERS THEN
1865 x_err_code := SQLCODE;
1866 RAISE;
1867 END update_prj_act_cmt;
1868
1869 -- Procedure to collect budgets
1870
1871 PROCEDURE get_fact_budgets
1872 ( x_project_num_from IN VARCHAR2,
1873 x_project_num_to IN VARCHAR2,
1874 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1875 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1876 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
1877 IS
1878
1879 -- Cursor for refreshing budgets at lowest level of task
1880
1881 CURSOR sel_ref_lowest_budgets(x_project_id NUMBER) IS
1882 SELECT
1886 PBGT.RESOURCE_LIST_MEMBER_ID,
1883 PBGT.TASK_ID,
1884 PBGT.PA_PERIOD_KEY,
1885 PBGT.BUDGET_TYPE_CODE,
1887 PBGT.SERVICE_TYPE_CODE,
1888 PBGT.OWNER_ORGANIZATION_ID,
1889 PBGT.EXPENDITURE_TYPE,
1890 PBGT.USER_COL1,
1891 PBGT.USER_COL2,
1892 PBGT.USER_COL3,
1893 PBGT.USER_COL4,
1894 PBGT.USER_COL5,
1895 SUM(PBGT.USER_COL6) USER_COL6,
1896 SUM(PBGT.USER_COL7) USER_COL7,
1897 SUM(PBGT.USER_COL8) USER_COL8,
1898 SUM(PBGT.USER_COL9) USER_COL9,
1899 SUM(PBGT.USER_COL10) USER_COL10,
1900 SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
1901 SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
1902 SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
1903 SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
1904 SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
1905 PBGT.BGT_UNIT_OF_MEASURE
1906 FROM
1907 PA_ADW_R_BGT_LINES_V PBGT,
1908 PA_ADW_LOWEST_TASKS_V PT
1909 WHERE
1910 PBGT.TASK_ID = PT.TASK_ID
1911 AND PBGT.PROJECT_ID = x_project_id
1912 -- Exclude the tasks which are top tasks
1913 AND PT.TASK_ID <> PT.TOP_TASK_ID
1914 AND PBGT.ADW_NOTIFY_FLAG = 'R'
1915 GROUP BY
1916 PBGT.TASK_ID,
1917 PBGT.PA_PERIOD_KEY,
1918 PBGT.BUDGET_TYPE_CODE,
1919 PBGT.RESOURCE_LIST_MEMBER_ID,
1920 PBGT.SERVICE_TYPE_CODE,
1921 PBGT.OWNER_ORGANIZATION_ID,
1922 PBGT.EXPENDITURE_TYPE,
1923 PBGT.USER_COL1,
1924 PBGT.USER_COL2,
1925 PBGT.USER_COL3,
1926 PBGT.USER_COL4,
1927 PBGT.USER_COL5,
1928 PBGT.BGT_UNIT_OF_MEASURE;
1929
1930 -- Cursor for refreshing budgets at top level of task
1931
1932 CURSOR sel_ref_top_budgets(x_project_id NUMBER) IS
1933 SELECT
1934 PBGT.TOP_TASK_ID,
1935 PBGT.PA_PERIOD_KEY,
1936 PBGT.BUDGET_TYPE_CODE,
1937 PBGT.RESOURCE_LIST_MEMBER_ID,
1938 PBGT.SERVICE_TYPE_CODE,
1939 PBGT.OWNER_ORGANIZATION_ID,
1940 PBGT.EXPENDITURE_TYPE,
1941 PBGT.USER_COL1,
1942 PBGT.USER_COL2,
1943 PBGT.USER_COL3,
1944 PBGT.USER_COL4,
1945 PBGT.USER_COL5,
1946 SUM(PBGT.USER_COL6) USER_COL6,
1947 SUM(PBGT.USER_COL7) USER_COL7,
1948 SUM(PBGT.USER_COL8) USER_COL8,
1949 SUM(PBGT.USER_COL9) USER_COL9,
1950 SUM(PBGT.USER_COL10) USER_COL10,
1951 SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
1952 SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
1953 SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
1954 SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
1955 SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
1956 PBGT.BGT_UNIT_OF_MEASURE
1957 FROM
1958 PA_ADW_R_BGT_LINES_V PBGT,
1959 PA_ADW_TOP_TASKS_V PT
1960 WHERE
1961 PBGT.TOP_TASK_ID = PT.TOP_TASK_ID
1962 AND PBGT.PROJECT_ID = x_project_id
1963 AND PBGT.ADW_NOTIFY_FLAG = 'R'
1964 GROUP BY
1965 PBGT.TOP_TASK_ID,
1966 PBGT.PA_PERIOD_KEY,
1967 PBGT.BUDGET_TYPE_CODE,
1968 PBGT.RESOURCE_LIST_MEMBER_ID,
1969 PBGT.SERVICE_TYPE_CODE,
1970 PBGT.OWNER_ORGANIZATION_ID,
1971 PBGT.EXPENDITURE_TYPE,
1972 PBGT.USER_COL1,
1973 PBGT.USER_COL2,
1974 PBGT.USER_COL3,
1975 PBGT.USER_COL4,
1976 PBGT.USER_COL5,
1977 PBGT.BGT_UNIT_OF_MEASURE;
1978
1979 -- Cursor for refreshing budgets at project level
1980
1981 CURSOR sel_ref_prj_budgets(x_project_id NUMBER) IS
1982 SELECT
1983 PBGT.PROJECT_ID,
1984 PBGT.PA_PERIOD_KEY,
1985 PBGT.BUDGET_TYPE_CODE,
1986 PBGT.RESOURCE_LIST_MEMBER_ID,
1987 PBGT.SERVICE_TYPE_CODE,
1988 PBGT.OWNER_ORGANIZATION_ID,
1989 PBGT.EXPENDITURE_TYPE,
1990 PBGT.USER_COL1,
1991 PBGT.USER_COL2,
1992 PBGT.USER_COL3,
1993 PBGT.USER_COL4,
1994 PBGT.USER_COL5,
1995 SUM(PBGT.USER_COL6) USER_COL6,
1996 SUM(PBGT.USER_COL7) USER_COL7,
1997 SUM(PBGT.USER_COL8) USER_COL8,
1998 SUM(PBGT.USER_COL9) USER_COL9,
1999 SUM(PBGT.USER_COL10) USER_COL10,
2000 SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
2001 SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
2002 SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
2003 SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
2004 SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
2005 PBGT.BGT_UNIT_OF_MEASURE
2006 FROM
2007 PA_ADW_R_BGT_LINES_V PBGT,
2008 PA_ADW_PROJECTS_V PP
2009 WHERE
2010 PBGT.PROJECT_ID = PP.PROJECT_ID
2011 AND PBGT.PROJECT_ID = x_project_id
2012 AND PBGT.ADW_NOTIFY_FLAG = 'R'
2013 GROUP BY
2014 PBGT.PROJECT_ID,
2015 PBGT.PA_PERIOD_KEY,
2016 PBGT.BUDGET_TYPE_CODE,
2017 PBGT.RESOURCE_LIST_MEMBER_ID,
2018 PBGT.SERVICE_TYPE_CODE,
2019 PBGT.OWNER_ORGANIZATION_ID,
2020 PBGT.EXPENDITURE_TYPE,
2021 PBGT.USER_COL1,
2022 PBGT.USER_COL2,
2023 PBGT.USER_COL3,
2024 PBGT.USER_COL4,
2025 PBGT.USER_COL5,
2026 PBGT.BGT_UNIT_OF_MEASURE;
2027
2031 SELECT
2028 -- Define Cursor for selecting budgets at lowest level of task.
2029
2030 CURSOR sel_lowest_budgets(x_project_id NUMBER) IS
2032 PBGT.TASK_ID,
2033 PBGT.PA_PERIOD_KEY,
2034 PBGT.BUDGET_TYPE_CODE,
2035 PBGT.RESOURCE_LIST_MEMBER_ID,
2036 PBGT.SERVICE_TYPE_CODE,
2037 PBGT.OWNER_ORGANIZATION_ID,
2038 PBGT.EXPENDITURE_TYPE,
2039 PBGT.USER_COL1,
2040 PBGT.USER_COL2,
2041 PBGT.USER_COL3,
2042 PBGT.USER_COL4,
2043 PBGT.USER_COL5,
2044 SUM(PBGT.USER_COL6) USER_COL6,
2045 SUM(PBGT.USER_COL7) USER_COL7,
2046 SUM(PBGT.USER_COL8) USER_COL8,
2047 SUM(PBGT.USER_COL9) USER_COL9,
2048 SUM(PBGT.USER_COL10) USER_COL10,
2049 SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
2050 SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
2051 SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
2052 SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
2053 SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
2054 PBGT.BGT_UNIT_OF_MEASURE
2055 FROM
2056 PA_ADW_BGT_LINES_V PBGT,
2057 PA_ADW_LOWEST_TASKS_V PT
2058 WHERE
2059 PBGT.TASK_ID = PT.TASK_ID
2060 -- Exclude the tasks which are top tasks
2061 AND PT.TASK_ID <> PT.TOP_TASK_ID
2062 AND PBGT.PROJECT_ID = x_project_id
2063 AND PBGT.ADW_NOTIFY_FLAG = 'S'
2064 GROUP BY
2065 PBGT.TASK_ID,
2066 PBGT.PA_PERIOD_KEY,
2067 PBGT.BUDGET_TYPE_CODE,
2068 PBGT.RESOURCE_LIST_MEMBER_ID,
2069 PBGT.SERVICE_TYPE_CODE,
2070 PBGT.OWNER_ORGANIZATION_ID,
2071 PBGT.EXPENDITURE_TYPE,
2072 PBGT.USER_COL1,
2073 PBGT.USER_COL2,
2074 PBGT.USER_COL3,
2075 PBGT.USER_COL4,
2076 PBGT.USER_COL5,
2077 PBGT.BGT_UNIT_OF_MEASURE;
2078
2079 -- Define Cursor for selecting budgets at top level of task.
2080
2081 CURSOR sel_top_budgets(x_project_id NUMBER) IS
2082 SELECT
2083 PBGT.TOP_TASK_ID,
2084 PBGT.PA_PERIOD_KEY,
2085 PBGT.BUDGET_TYPE_CODE,
2086 PBGT.RESOURCE_LIST_MEMBER_ID,
2087 PBGT.SERVICE_TYPE_CODE,
2088 PBGT.OWNER_ORGANIZATION_ID,
2089 PBGT.EXPENDITURE_TYPE,
2090 PBGT.USER_COL1,
2091 PBGT.USER_COL2,
2092 PBGT.USER_COL3,
2093 PBGT.USER_COL4,
2094 PBGT.USER_COL5,
2095 SUM(PBGT.USER_COL6) USER_COL6,
2096 SUM(PBGT.USER_COL7) USER_COL7,
2097 SUM(PBGT.USER_COL8) USER_COL8,
2098 SUM(PBGT.USER_COL9) USER_COL9,
2099 SUM(PBGT.USER_COL10) USER_COL10,
2100 SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
2101 SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
2102 SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
2103 SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
2104 SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
2105 PBGT.BGT_UNIT_OF_MEASURE
2106 FROM
2107 PA_ADW_BGT_LINES_V PBGT,
2108 PA_ADW_TOP_TASKS_V PT
2109 WHERE
2110 PBGT.TOP_TASK_ID = PT.TOP_TASK_ID
2111 AND PBGT.PROJECT_ID = x_project_id
2112 AND PBGT.ADW_NOTIFY_FLAG = 'S'
2113 GROUP BY
2114 PBGT.TOP_TASK_ID,
2115 PBGT.PA_PERIOD_KEY,
2116 PBGT.BUDGET_TYPE_CODE,
2117 PBGT.RESOURCE_LIST_MEMBER_ID,
2118 PBGT.SERVICE_TYPE_CODE,
2119 PBGT.OWNER_ORGANIZATION_ID,
2120 PBGT.EXPENDITURE_TYPE,
2121 PBGT.USER_COL1,
2122 PBGT.USER_COL2,
2123 PBGT.USER_COL3,
2124 PBGT.USER_COL4,
2125 PBGT.USER_COL5,
2126 PBGT.BGT_UNIT_OF_MEASURE;
2127
2128 -- Define Cursor for selecting budgets at project level.
2129
2130 CURSOR sel_prj_budgets(x_project_id NUMBER) IS
2131 SELECT
2132 PBGT.PROJECT_ID,
2133 PBGT.PA_PERIOD_KEY,
2134 PBGT.BUDGET_TYPE_CODE,
2135 PBGT.RESOURCE_LIST_MEMBER_ID,
2136 PBGT.SERVICE_TYPE_CODE,
2137 PBGT.OWNER_ORGANIZATION_ID,
2138 PBGT.EXPENDITURE_TYPE,
2139 PBGT.USER_COL1,
2140 PBGT.USER_COL2,
2141 PBGT.USER_COL3,
2142 PBGT.USER_COL4,
2143 PBGT.USER_COL5,
2144 SUM(PBGT.USER_COL6) USER_COL6,
2145 SUM(PBGT.USER_COL7) USER_COL7,
2146 SUM(PBGT.USER_COL8) USER_COL8,
2147 SUM(PBGT.USER_COL9) USER_COL9,
2148 SUM(PBGT.USER_COL10) USER_COL10,
2149 SUM(PBGT.BGT_RAW_COST) BGT_RAW_COST,
2150 SUM(PBGT.BGT_BURDENED_COST) BGT_BURDENED_COST,
2151 SUM(PBGT.BGT_REVENUE) BGT_REVENUE,
2152 SUM(PBGT.BGT_QUANTITY) BGT_QUANTITY,
2153 SUM(PBGT.BGT_LABOR_QUANTITY) BGT_LABOR_QUANTITY,
2154 PBGT.BGT_UNIT_OF_MEASURE
2155 FROM
2156 PA_ADW_BGT_LINES_V PBGT,
2157 PA_ADW_PROJECTS_V PP
2158 WHERE
2159 PBGT.PROJECT_ID = PP.PROJECT_ID
2160 AND PBGT.PROJECT_ID = x_project_id
2161 AND PBGT.ADW_NOTIFY_FLAG = 'S'
2162 GROUP BY
2163 PBGT.PROJECT_ID,
2164 PBGT.PA_PERIOD_KEY,
2165 PBGT.BUDGET_TYPE_CODE,
2166 PBGT.RESOURCE_LIST_MEMBER_ID,
2167 PBGT.SERVICE_TYPE_CODE,
2168 PBGT.OWNER_ORGANIZATION_ID,
2169 PBGT.EXPENDITURE_TYPE,
2170 PBGT.USER_COL1,
2171 PBGT.USER_COL2,
2175 PBGT.BGT_UNIT_OF_MEASURE;
2172 PBGT.USER_COL3,
2173 PBGT.USER_COL4,
2174 PBGT.USER_COL5,
2176
2177 -- Cursor for selecting projects for processing
2178
2179 CURSOR sel_prjs IS
2180 SELECT
2181 PROJECT_ID,
2182 SEGMENT1
2183 FROM
2184 PA_ADW_PROJECTS_V
2185 WHERE segment1 BETWEEN NVL(x_project_num_from,segment1)
2186 AND NVL(x_project_num_to,segment1);
2187
2188 -- define procedure variables
2189
2190 ref_lowest_budgets_r sel_ref_lowest_budgets%ROWTYPE;
2191 ref_top_budgets_r sel_ref_top_budgets%ROWTYPE;
2192 ref_prj_budgets_r sel_ref_prj_budgets%ROWTYPE;
2193
2194 lowest_budgets_r sel_lowest_budgets%ROWTYPE;
2195 top_budgets_r sel_top_budgets%ROWTYPE;
2196 prj_budgets_r sel_prj_budgets%ROWTYPE;
2197
2198 sel_prjs_r sel_prjs%ROWTYPE;
2199
2200 x_old_err_stack VARCHAR2(1024);
2201
2202
2203 BEGIN
2204 x_err_code := 0;
2205 x_err_stage := 'Collecting Budgets';
2206 x_old_err_stack := x_err_stack;
2207 x_err_stack := x_err_stack || '-> get_fact_budgets';
2208
2209 pa_debug.debug(x_err_stage);
2210
2211 -- Process all projects one one by one
2212
2213 FOR sel_prjs_r IN sel_prjs LOOP
2214
2215 pa_debug.debug('Processing Budgets For Project ' || sel_prjs_r.segment1);
2216
2217 -- First mark all the budgets need to be transferred
2218 -- We will transfer the latest baselined budget only
2219
2220 UPDATE
2221 PA_BUDGET_VERSIONS PBV
2222 SET
2223 ADW_NOTIFY_FLAG = 'S'
2224 WHERE
2225 PROJECT_ID = SEL_PRJS_R.PROJECT_ID
2226 AND CURRENT_FLAG = 'Y'
2227 AND ADW_NOTIFY_FLAG = 'Y'
2228 AND EXISTS
2229 ( SELECT 'Yes'
2230 FROM
2231 PA_ADW_BGT_TYPES_V PBT
2232 WHERE PBT.BUDGET_TYPE_CODE = PBV.BUDGET_TYPE_CODE
2233 );
2234
2235 IF ( SQL%ROWCOUNT <> 0 ) THEN
2236
2237 -- Mark those budgets for refresh for which new version of the budgets
2238 -- were created. Only those budgets will be refreshed which were sent
2239 -- earlier
2240
2241 UPDATE
2242 PA_BUDGET_VERSIONS
2243 SET
2244 ADW_NOTIFY_FLAG = 'R'
2245 WHERE
2246 PROJECT_ID = sel_prjs_r.project_id
2247 AND (BUDGET_TYPE_CODE,VERSION_NUMBER) IN
2248 ( SELECT
2249 BUDGET_TYPE_CODE,
2250 MAX(VERSION_NUMBER)
2251 FROM
2252 PA_BUDGET_VERSIONS OB
2253 WHERE
2254 OB.PROJECT_ID = sel_prjs_r.project_id
2255 AND BUDGET_TYPE_CODE IN
2256 ( SELECT
2257 BUDGET_TYPE_CODE
2258 FROM
2259 PA_ADW_BGT_TYPES_V
2260 )
2261 AND OB.ADW_NOTIFY_FLAG = 'N'
2262 AND EXISTS
2263 -- Check if a new budget was baselined, since the time the this budget was sent
2264 ( SELECT
2265 'YES'
2266 FROM
2267 PA_BUDGET_VERSIONS NB
2268 WHERE
2269 NB.PROJECT_ID = sel_prjs_r.project_id
2270 AND NB.BUDGET_TYPE_CODE = OB.BUDGET_TYPE_CODE
2271 AND NB.ADW_NOTIFY_FLAG = 'S'
2272 )
2273 GROUP BY
2274 OB.BUDGET_TYPE_CODE
2275 );
2276
2277 -- Check the profile option value for collecting lowest tasks
2278
2279 IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y') THEN
2280
2281 -- refreshing Budgets at lowest tasks level
2282
2283 x_err_stage := 'Collecting Budgets at lowest task level';
2284
2285 FOR ref_lowest_budgets_r IN sel_ref_lowest_budgets(sel_prjs_r.project_id) LOOP
2286
2287 update_tasks_budgets
2288 (ref_lowest_budgets_r.task_id,
2289 ref_lowest_budgets_r.pa_period_key,
2290 ref_lowest_budgets_r.budget_type_code,
2291 ref_lowest_budgets_r.resource_list_member_id,
2292 ref_lowest_budgets_r.service_type_code,
2293 ref_lowest_budgets_r.owner_organization_id,
2294 ref_lowest_budgets_r.expenditure_type,
2295 ref_lowest_budgets_r.user_col1,
2296 ref_lowest_budgets_r.user_col2,
2297 ref_lowest_budgets_r.user_col3,
2298 ref_lowest_budgets_r.user_col4,
2299 ref_lowest_budgets_r.user_col5,
2300 ref_lowest_budgets_r.user_col6,
2301 ref_lowest_budgets_r.user_col7,
2302 ref_lowest_budgets_r.user_col8,
2303 ref_lowest_budgets_r.user_col9,
2304 ref_lowest_budgets_r.user_col10,
2305 ref_lowest_budgets_r.bgt_revenue,
2306 ref_lowest_budgets_r.bgt_raw_cost,
2307 ref_lowest_budgets_r.bgt_burdened_cost,
2308 ref_lowest_budgets_r.bgt_quantity,
2309 ref_lowest_budgets_r.bgt_labor_quantity,
2310 ref_lowest_budgets_r.bgt_unit_of_measure,
2311 x_err_stage,
2312 x_err_stack,
2313 x_err_code);
2314
2315 END LOOP; -- FOR ref_lowest_budgets_r IN sel_ref_lowest_budgets
2316
2317 END IF; -- IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y')
2321
2318
2319 -- Check the profile option value for collecting top tasks
2320 IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y') THEN
2322 -- refreshing Budgets at top tasks level
2323
2324 x_err_stage := 'Collecting Budgets at top task level';
2325
2326 FOR ref_top_budgets_r IN sel_ref_top_budgets(sel_prjs_r.project_id) LOOP
2327
2328 update_tasks_budgets
2329 (ref_top_budgets_r.top_task_id,
2330 ref_top_budgets_r.pa_period_key,
2331 ref_top_budgets_r.budget_type_code,
2332 ref_top_budgets_r.resource_list_member_id,
2333 ref_top_budgets_r.service_type_code,
2334 ref_top_budgets_r.owner_organization_id,
2335 ref_top_budgets_r.expenditure_type,
2336 ref_top_budgets_r.user_col1,
2337 ref_top_budgets_r.user_col2,
2338 ref_top_budgets_r.user_col3,
2339 ref_top_budgets_r.user_col4,
2340 ref_top_budgets_r.user_col5,
2341 ref_top_budgets_r.user_col6,
2342 ref_top_budgets_r.user_col7,
2343 ref_top_budgets_r.user_col8,
2344 ref_top_budgets_r.user_col9,
2345 ref_top_budgets_r.user_col10,
2346 ref_top_budgets_r.bgt_revenue,
2347 ref_top_budgets_r.bgt_raw_cost,
2348 ref_top_budgets_r.bgt_burdened_cost,
2349 ref_top_budgets_r.bgt_quantity,
2350 ref_top_budgets_r.bgt_labor_quantity,
2351 ref_top_budgets_r.bgt_unit_of_measure,
2352 x_err_stage,
2353 x_err_stack,
2354 x_err_code);
2355
2356 END LOOP; -- FOR ref_top_budgets_r IN sel_ref_top_budgets
2357
2358 END IF; -- IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y')
2359
2360 -- Refreshing Budgets at Project level
2361
2362 x_err_stage := 'Refreshing Budgets txns at Project level';
2363
2364 FOR ref_prj_budgets_r IN sel_ref_prj_budgets(sel_prjs_r.project_id) LOOP
2365
2366 update_prj_budgets
2367 (ref_prj_budgets_r.project_id,
2368 ref_prj_budgets_r.pa_period_key,
2369 ref_prj_budgets_r.budget_type_code,
2370 ref_prj_budgets_r.resource_list_member_id,
2371 ref_prj_budgets_r.service_type_code,
2372 ref_prj_budgets_r.owner_organization_id,
2373 ref_prj_budgets_r.expenditure_type,
2374 ref_prj_budgets_r.user_col1,
2375 ref_prj_budgets_r.user_col2,
2376 ref_prj_budgets_r.user_col3,
2377 ref_prj_budgets_r.user_col4,
2378 ref_prj_budgets_r.user_col5,
2379 ref_prj_budgets_r.user_col6,
2380 ref_prj_budgets_r.user_col7,
2381 ref_prj_budgets_r.user_col8,
2382 ref_prj_budgets_r.user_col9,
2383 ref_prj_budgets_r.user_col10,
2384 ref_prj_budgets_r.bgt_revenue,
2385 ref_prj_budgets_r.bgt_raw_cost,
2386 ref_prj_budgets_r.bgt_burdened_cost,
2387 ref_prj_budgets_r.bgt_quantity,
2388 ref_prj_budgets_r.bgt_labor_quantity,
2389 ref_prj_budgets_r.bgt_unit_of_measure,
2390 x_err_stage,
2391 x_err_stack,
2392 x_err_code);
2393
2394 END LOOP; -- FOR ref_prj_budgets_r IN sel_ref_prj_budgets
2395
2396 -- Budget refresh is complete
2397
2398 -- Check the profile option value for collecting lowest tasks
2399 IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y') THEN
2400
2401 -- Collect Budgets at lowest tasks level
2402
2403 x_err_stage := 'Collecting Budgets at lowest task level';
2404
2405 FOR lowest_budgets_r IN sel_lowest_budgets(sel_prjs_r.project_id) LOOP
2406
2407 update_tasks_budgets
2408 (lowest_budgets_r.task_id,
2409 lowest_budgets_r.pa_period_key,
2410 lowest_budgets_r.budget_type_code,
2411 lowest_budgets_r.resource_list_member_id,
2412 lowest_budgets_r.service_type_code,
2413 lowest_budgets_r.owner_organization_id,
2414 lowest_budgets_r.expenditure_type,
2415 lowest_budgets_r.user_col1,
2416 lowest_budgets_r.user_col2,
2417 lowest_budgets_r.user_col3,
2418 lowest_budgets_r.user_col4,
2419 lowest_budgets_r.user_col5,
2420 lowest_budgets_r.user_col6,
2421 lowest_budgets_r.user_col7,
2422 lowest_budgets_r.user_col8,
2423 lowest_budgets_r.user_col9,
2424 lowest_budgets_r.user_col10,
2425 lowest_budgets_r.bgt_revenue,
2426 lowest_budgets_r.bgt_raw_cost,
2427 lowest_budgets_r.bgt_burdened_cost,
2428 lowest_budgets_r.bgt_quantity,
2429 lowest_budgets_r.bgt_labor_quantity,
2430 lowest_budgets_r.bgt_unit_of_measure,
2431 x_err_stage,
2432 x_err_stack,
2433 x_err_code);
2434
2435 END LOOP; -- FOR lowest_budgets_r IN sel_lowest_budgets
2436
2437 END IF; -- IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y')
2438
2439 -- Check the profile option value for collecting top tasks
2440 IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y') THEN
2441
2442 -- Collect Budgets at top tasks level
2443
2444 x_err_stage := 'Collecting Budgets at top task level';
2445
2446 FOR top_budgets_r IN sel_top_budgets(sel_prjs_r.project_id) LOOP
2447
2448 update_tasks_budgets
2449 (top_budgets_r.top_task_id,
2450 top_budgets_r.pa_period_key,
2451 top_budgets_r.budget_type_code,
2452 top_budgets_r.resource_list_member_id,
2456 top_budgets_r.user_col1,
2453 top_budgets_r.service_type_code,
2454 top_budgets_r.owner_organization_id,
2455 top_budgets_r.expenditure_type,
2457 top_budgets_r.user_col2,
2458 top_budgets_r.user_col3,
2459 top_budgets_r.user_col4,
2460 top_budgets_r.user_col5,
2461 top_budgets_r.user_col6,
2462 top_budgets_r.user_col7,
2463 top_budgets_r.user_col8,
2464 top_budgets_r.user_col9,
2465 top_budgets_r.user_col10,
2466 top_budgets_r.bgt_revenue,
2467 top_budgets_r.bgt_raw_cost,
2468 top_budgets_r.bgt_burdened_cost,
2469 top_budgets_r.bgt_quantity,
2470 top_budgets_r.bgt_labor_quantity,
2471 top_budgets_r.bgt_unit_of_measure,
2472 x_err_stage,
2473 x_err_stack,
2474 x_err_code);
2475
2476 END LOOP; -- FOR top_budgets_r IN sel_top_budgets
2477
2478 END IF; -- IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y')
2479
2480 -- Collect Budgets at Project level
2481
2482 x_err_stage := 'Collecting Budgets txns at Project level';
2483
2484 FOR prj_budgets_r IN sel_prj_budgets(sel_prjs_r.project_id) LOOP
2485
2486 update_prj_budgets
2487 (prj_budgets_r.project_id,
2488 prj_budgets_r.pa_period_key,
2489 prj_budgets_r.budget_type_code,
2490 prj_budgets_r.resource_list_member_id,
2491 prj_budgets_r.service_type_code,
2492 prj_budgets_r.owner_organization_id,
2493 prj_budgets_r.expenditure_type,
2494 prj_budgets_r.user_col1,
2495 prj_budgets_r.user_col2,
2496 prj_budgets_r.user_col3,
2497 prj_budgets_r.user_col4,
2498 prj_budgets_r.user_col5,
2499 prj_budgets_r.user_col6,
2500 prj_budgets_r.user_col7,
2501 prj_budgets_r.user_col8,
2502 prj_budgets_r.user_col9,
2503 prj_budgets_r.user_col10,
2504 prj_budgets_r.bgt_revenue,
2505 prj_budgets_r.bgt_raw_cost,
2506 prj_budgets_r.bgt_burdened_cost,
2507 prj_budgets_r.bgt_quantity,
2508 prj_budgets_r.bgt_labor_quantity,
2509 prj_budgets_r.bgt_unit_of_measure,
2510 x_err_stage,
2511 x_err_stack,
2512 x_err_code);
2513
2514 END LOOP; -- FOR prj_budgets_r IN sel_prj_budgets
2515
2516 -- Mark the project types as transferred to Interface table
2517
2518 -- PLEASE NOTE THAT WE ARE UPDATING THE BASE TABLE SINCE THE
2519 -- PA_ADW_BUDGETS_V IS DEFINED ON MULTIPLE TABLES
2520
2521 UPDATE
2522 PA_BUDGET_VERSIONS
2523 SET
2524 ADW_NOTIFY_FLAG = 'N'
2525 WHERE
2526 ADW_NOTIFY_FLAG IN ('S','R');
2527
2528 END IF; --IF (SQL%ROWCOUNT <> 0)
2529 -- Commit the project
2530 COMMIT;
2531 END LOOP; -- FOR sel_prjs_r IN sel_prjs
2532
2533 x_err_stack := x_old_err_stack;
2534
2535 pa_debug.debug('Completed ' || x_err_stage);
2536
2537 EXCEPTION
2538 WHEN OTHERS THEN
2539 x_err_code := SQLCODE;
2540 RAISE;
2541 END get_fact_budgets;
2542
2543 PROCEDURE update_tasks_budgets
2544 (x_task_id IN NUMBER,
2545 x_pa_period_key IN VARCHAR2,
2546 x_budget_type_code IN VARCHAR2,
2547 x_resource_list_member_id IN NUMBER,
2548 x_service_type_code IN VARCHAR2,
2549 x_owner_organization_id IN NUMBER,
2550 x_expenditure_type IN VARCHAR2,
2551 x_user_col1 IN VARCHAR2,
2552 x_user_col2 IN VARCHAR2,
2553 x_user_col3 IN VARCHAR2,
2554 x_user_col4 IN VARCHAR2,
2555 x_user_col5 IN VARCHAR2,
2556 x_user_col6 IN VARCHAR2,
2557 x_user_col7 IN VARCHAR2,
2558 x_user_col8 IN VARCHAR2,
2559 x_user_col9 IN VARCHAR2,
2560 x_user_col10 IN VARCHAR2,
2561 x_bgt_revenue IN NUMBER,
2562 x_bgt_raw_cost IN NUMBER,
2563 x_bgt_burdened_cost IN NUMBER,
2564 x_bgt_quantity IN NUMBER,
2565 x_bgt_labor_quantity IN NUMBER,
2566 x_bgt_unit_of_measure IN VARCHAR2,
2567 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2568 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2569 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
2570 IS
2571 x_old_err_stack VARCHAR2(1024);
2572 BEGIN
2573 x_err_code := 0;
2574 x_err_stage := 'Creating Task Level Budgets';
2575 x_old_err_stack := x_err_stack;
2576 x_err_stack := x_err_stack || '-> update_tasks_budgets';
2577
2578 -- First Try to Update the Row in the Interface Table
2579
2580 UPDATE
2581 PA_TSK_BGT_LINES_IT
2582 SET
2583 LAST_UPDATE_DATE = TRUNC(SYSDATE),
2584 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2585 CREATION_DATE = TRUNC(SYSDATE),
2586 CREATED_BY = X_CREATED_BY,
2587 USER_COL6 = X_USER_COL6,
2588 USER_COL7 = X_USER_COL7,
2589 USER_COL8 = X_USER_COL8,
2590 USER_COL9 = X_USER_COL9,
2591 USER_COL10 = X_USER_COL10,
2592 BGT_REVENUE = X_BGT_REVENUE,
2593 BGT_RAW_COST = X_BGT_RAW_COST,
2594 BGT_BURDENED_COST = X_BGT_BURDENED_COST,
2595 BGT_QUANTITY = X_BGT_QUANTITY,
2599 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
2596 BGT_LABOR_QUANTITY = X_BGT_LABOR_QUANTITY,
2597 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2598 REQUEST_ID = X_REQUEST_ID,
2600 PROGRAM_ID = X_PROGRAM_ID,
2601 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
2602 STATUS_CODE = 'P'
2603 WHERE
2604 TASK_ID = X_TASK_ID
2605 AND PA_PERIOD_KEY = X_PA_PERIOD_KEY
2606 AND BUDGET_TYPE_CODE = X_BUDGET_TYPE_CODE
2607 AND NVL(RESOURCE_LIST_MEMBER_ID,-99) = NVL(X_RESOURCE_LIST_MEMBER_ID,-99)
2608 AND NVL(SERVICE_TYPE_CODE,'X') = NVL(X_SERVICE_TYPE_CODE,'X')
2609 AND NVL(OWNER_ORGANIZATION_ID,-99) = NVL(X_OWNER_ORGANIZATION_ID,-99)
2610 AND NVL(EXPENDITURE_TYPE,'X') = NVL(X_EXPENDITURE_TYPE,'X')
2611 AND NVL(USER_COL1,'X') = NVL(X_USER_COL1,'X')
2612 AND NVL(USER_COL2,'X') = NVL(X_USER_COL2,'X')
2613 AND NVL(USER_COL3,'X') = NVL(X_USER_COL3,'X')
2614 AND NVL(USER_COL4,'X') = NVL(X_USER_COL4,'X')
2615 AND NVL(USER_COL5,'X') = NVL(X_USER_COL5,'X')
2616 AND NVL(BGT_UNIT_OF_MEASURE,'X') = NVL(X_BGT_UNIT_OF_MEASURE,'X');
2617
2618 -- Check If Any row was updated
2619
2620 IF (SQL%ROWCOUNT = 0) THEN
2621
2622 -- No row was updated, So Insert a new row into the interface table
2623 INSERT INTO PA_TSK_BGT_LINES_IT
2624 (
2625 TASK_BUDGET_LINE_KEY,
2626 TASK_ID,
2627 PA_PERIOD_KEY,
2628 BUDGET_TYPE_CODE,
2629 LAST_UPDATE_DATE,
2630 LAST_UPDATED_BY,
2631 CREATION_DATE,
2632 CREATED_BY,
2633 RESOURCE_LIST_MEMBER_ID,
2634 SERVICE_TYPE_CODE,
2635 OWNER_ORGANIZATION_ID,
2636 EXPENDITURE_TYPE,
2637 USER_COL1,
2638 USER_COL2,
2639 USER_COL3,
2640 USER_COL4,
2641 USER_COL5,
2642 USER_COL6,
2643 USER_COL7,
2644 USER_COL8,
2645 USER_COL9,
2646 USER_COL10,
2647 BGT_REVENUE,
2648 BGT_RAW_COST,
2649 BGT_BURDENED_COST,
2650 BGT_QUANTITY,
2651 BGT_LABOR_QUANTITY,
2652 BGT_UNIT_OF_MEASURE,
2653 LAST_UPDATE_LOGIN,
2654 REQUEST_ID,
2655 PROGRAM_APPLICATION_ID,
2656 PROGRAM_ID,
2657 PROGRAM_UPDATE_DATE,
2658 STATUS_CODE
2659 )
2660 VALUES
2661 (
2662 X_TASK_ID || '-' || X_PA_PERIOD_KEY || '-' || --
2663 NVL(X_BUDGET_TYPE_CODE,'X') || '-' || --|
2664 NVL(X_OWNER_ORGANIZATION_ID,-99)|| '-' || --|
2665 NVL(X_RESOURCE_LIST_MEMBER_ID,-99)|| '-' || --|
2666 NVL(X_SERVICE_TYPE_CODE,'X')|| '-' || --| Dimension Keys
2667 NVL(X_EXPENDITURE_TYPE,'X')|| '-' || --|
2668 NVL(X_USER_COL1,'X')|| '-' || --|
2669 NVL(X_USER_COL2,'X')|| '-' || --|
2670 NVL(X_USER_COL3,'X')|| '-' || --|
2671 NVL(X_USER_COL4,'X')|| '-' || --|
2672 NVL(X_USER_COL5,'X'), --
2673 X_TASK_ID,
2674 X_PA_PERIOD_KEY,
2675 X_BUDGET_TYPE_CODE,
2676 TRUNC(SYSDATE),
2677 X_LAST_UPDATED_BY,
2678 TRUNC(SYSDATE),
2679 X_CREATED_BY,
2680 X_RESOURCE_LIST_MEMBER_ID,
2681 X_SERVICE_TYPE_CODE,
2682 X_OWNER_ORGANIZATION_ID,
2683 X_EXPENDITURE_TYPE,
2684 X_USER_COL1,
2685 X_USER_COL2,
2686 X_USER_COL3,
2687 X_USER_COL4,
2688 X_USER_COL5,
2689 X_USER_COL6,
2690 X_USER_COL7,
2691 X_USER_COL8,
2692 X_USER_COL9,
2693 X_USER_COL10,
2694 X_BGT_REVENUE,
2695 X_BGT_RAW_COST,
2696 X_BGT_BURDENED_COST,
2697 X_BGT_QUANTITY,
2698 X_BGT_LABOR_QUANTITY,
2699 X_BGT_UNIT_OF_MEASURE,
2700 X_LAST_UPDATE_LOGIN,
2701 X_REQUEST_ID,
2702 X_PROGRAM_APPLICATION_ID,
2703 X_PROGRAM_ID,
2704 TRUNC(SYSDATE),
2705 'P'
2706 );
2707
2708 END IF; -- IF ( SQL%ROWCOUNT = 0 )
2709
2710 x_err_stack := x_old_err_stack;
2711
2712 EXCEPTION
2713 WHEN OTHERS THEN
2714 x_err_code := SQLCODE;
2715 RAISE;
2716 END update_tasks_budgets;
2717
2718 -- Update the project level numbers
2719
2720 PROCEDURE update_prj_budgets
2721 (x_project_id IN NUMBER,
2722 x_pa_period_key IN VARCHAR2,
2723 x_budget_type_code IN VARCHAR2,
2724 x_resource_list_member_id IN NUMBER,
2725 x_service_type_code IN VARCHAR2,
2726 x_owner_organization_id IN NUMBER,
2727 x_expenditure_type IN VARCHAR2,
2728 x_user_col1 IN VARCHAR2,
2729 x_user_col2 IN VARCHAR2,
2730 x_user_col3 IN VARCHAR2,
2731 x_user_col4 IN VARCHAR2,
2732 x_user_col5 IN VARCHAR2,
2733 x_user_col6 IN VARCHAR2,
2734 x_user_col7 IN VARCHAR2,
2735 x_user_col8 IN VARCHAR2,
2736 x_user_col9 IN VARCHAR2,
2737 x_user_col10 IN VARCHAR2,
2738 x_bgt_revenue IN NUMBER,
2739 x_bgt_raw_cost IN NUMBER,
2740 x_bgt_burdened_cost IN NUMBER,
2741 x_bgt_quantity IN NUMBER,
2742 x_bgt_labor_quantity IN NUMBER,
2743 x_bgt_unit_of_measure IN VARCHAR2,
2744 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2748 x_old_err_stack VARCHAR2(1024);
2745 x_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2746 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
2747 IS
2749 BEGIN
2750 x_err_code := 0;
2751 x_err_stage := 'Creating Project Level Budgets';
2752 x_old_err_stack := x_err_stack;
2753 x_err_stack := x_err_stack || '-> update_prj_budgets';
2754
2755 -- First Try to Update the Row in the Interface Table
2756
2757 UPDATE
2758 PA_PRJ_BGT_LINES_IT
2759 SET
2760 LAST_UPDATE_DATE = TRUNC(SYSDATE),
2761 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2762 CREATION_DATE = TRUNC(SYSDATE),
2763 CREATED_BY = X_CREATED_BY,
2764 USER_COL6 = X_USER_COL6,
2765 USER_COL7 = X_USER_COL7,
2766 USER_COL8 = X_USER_COL8,
2767 USER_COL9 = X_USER_COL9,
2768 USER_COL10 = X_USER_COL10,
2769 BGT_REVENUE = X_BGT_REVENUE,
2770 BGT_RAW_COST = X_BGT_RAW_COST,
2771 BGT_BURDENED_COST = X_BGT_BURDENED_COST,
2772 BGT_QUANTITY = X_BGT_QUANTITY,
2773 BGT_LABOR_QUANTITY = X_BGT_LABOR_QUANTITY,
2774 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2775 REQUEST_ID = X_REQUEST_ID,
2776 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
2777 PROGRAM_ID = X_PROGRAM_ID,
2778 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
2779 STATUS_CODE = 'P'
2780 WHERE
2781 PROJECT_ID = X_PROJECT_ID
2782 AND PA_PERIOD_KEY = X_PA_PERIOD_KEY
2783 AND BUDGET_TYPE_CODE = X_BUDGET_TYPE_CODE
2784 AND NVL(RESOURCE_LIST_MEMBER_ID,-99) = NVL(X_RESOURCE_LIST_MEMBER_ID,-99)
2785 AND NVL(SERVICE_TYPE_CODE,'X') = NVL(X_SERVICE_TYPE_CODE,'X')
2786 AND NVL(OWNER_ORGANIZATION_ID,-99) = NVL(X_OWNER_ORGANIZATION_ID,-99)
2787 AND NVL(EXPENDITURE_TYPE,'X') = NVL(X_EXPENDITURE_TYPE,'X')
2788 AND NVL(USER_COL1,'X') = NVL(X_USER_COL1,'X')
2789 AND NVL(USER_COL2,'X') = NVL(X_USER_COL2,'X')
2790 AND NVL(USER_COL3,'X') = NVL(X_USER_COL3,'X')
2791 AND NVL(USER_COL4,'X') = NVL(X_USER_COL4,'X')
2792 AND NVL(USER_COL5,'X') = NVL(X_USER_COL5,'X')
2793 AND NVL(BGT_UNIT_OF_MEASURE,'X') = NVL(X_BGT_UNIT_OF_MEASURE,'X');
2794
2795 -- Check If Any row was updated
2796
2797 IF (SQL%ROWCOUNT = 0) THEN
2798
2799 -- No row was updated, So Insert a new row into the interface table
2800 INSERT INTO PA_PRJ_BGT_LINES_IT
2801 (
2802 PRJ_BUDGET_LINE_KEY,
2803 PROJECT_ID,
2804 PA_PERIOD_KEY,
2805 BUDGET_TYPE_CODE,
2806 LAST_UPDATE_DATE,
2807 LAST_UPDATED_BY,
2808 CREATION_DATE,
2809 CREATED_BY,
2810 RESOURCE_LIST_MEMBER_ID,
2811 SERVICE_TYPE_CODE,
2812 OWNER_ORGANIZATION_ID,
2813 EXPENDITURE_TYPE,
2814 USER_COL1,
2815 USER_COL2,
2816 USER_COL3,
2817 USER_COL4,
2818 USER_COL5,
2819 USER_COL6,
2820 USER_COL7,
2821 USER_COL8,
2822 USER_COL9,
2823 USER_COL10,
2824 BGT_REVENUE,
2825 BGT_RAW_COST,
2826 BGT_BURDENED_COST,
2827 BGT_QUANTITY,
2828 BGT_LABOR_QUANTITY,
2829 BGT_UNIT_OF_MEASURE,
2830 LAST_UPDATE_LOGIN,
2831 REQUEST_ID,
2832 PROGRAM_APPLICATION_ID,
2833 PROGRAM_ID,
2834 PROGRAM_UPDATE_DATE,
2835 STATUS_CODE
2836 )
2837 VALUES
2838 (
2839 X_PROJECT_ID || '-' || X_PA_PERIOD_KEY || '-' || --
2840 NVL(X_BUDGET_TYPE_CODE,'X') || '-' || --|
2841 NVL(X_OWNER_ORGANIZATION_ID,-99)|| '-' || --|
2842 NVL(X_RESOURCE_LIST_MEMBER_ID,-99)|| '-' || --|
2843 NVL(X_SERVICE_TYPE_CODE,'X')|| '-' || --| Dimension Keys
2844 NVL(X_EXPENDITURE_TYPE,'X')|| '-' || --|
2845 NVL(X_USER_COL1,'X')|| '-' || --|
2846 NVL(X_USER_COL2,'X')|| '-' || --|
2847 NVL(X_USER_COL3,'X')|| '-' || --|
2848 NVL(X_USER_COL4,'X')|| '-' || --|
2849 NVL(X_USER_COL5,'X'), --
2850 X_PROJECT_ID,
2851 X_PA_PERIOD_KEY,
2852 X_BUDGET_TYPE_CODE,
2853 TRUNC(SYSDATE),
2854 X_LAST_UPDATED_BY,
2855 TRUNC(SYSDATE),
2856 X_CREATED_BY,
2857 X_RESOURCE_LIST_MEMBER_ID,
2858 X_SERVICE_TYPE_CODE,
2859 X_OWNER_ORGANIZATION_ID,
2860 X_EXPENDITURE_TYPE,
2861 X_USER_COL1,
2862 X_USER_COL2,
2863 X_USER_COL3,
2864 X_USER_COL4,
2865 X_USER_COL5,
2866 X_USER_COL6,
2867 X_USER_COL7,
2868 X_USER_COL8,
2869 X_USER_COL9,
2870 X_USER_COL10,
2871 X_BGT_REVENUE,
2872 X_BGT_RAW_COST,
2873 X_BGT_BURDENED_COST,
2874 X_BGT_QUANTITY,
2875 X_BGT_LABOR_QUANTITY,
2876 X_BGT_UNIT_OF_MEASURE,
2877 X_LAST_UPDATE_LOGIN,
2878 X_REQUEST_ID,
2879 X_PROGRAM_APPLICATION_ID,
2880 X_PROGRAM_ID,
2881 TRUNC(SYSDATE),
2882 'P'
2883 );
2884
2885 END IF; -- IF ( SQL%ROWCOUNT = 0 )
2886
2887 x_err_stack := x_old_err_stack;
2888
2889 EXCEPTION
2890 WHEN OTHERS THEN
2891 x_err_code := SQLCODE;
2892 RAISE;
2893 END update_prj_budgets;
2894
2895 END PA_ADW_COLLECT_FACTS;