[Home] [Help]
PACKAGE BODY: APPS.CST_PACEAMCOST_GRP
Source
1 PACKAGE BODY CST_PacEamCost_GRP AS
2 /* $Header: CSTPPEAB.pls 120.13 2011/05/12 19:40:27 fayang ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CST_PacEamCost_GRP';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 -- Start of comments
8 -- API name : Estimate_PAC_WipJobs
9 -- Type : Public.
10 -- Function : This API is called from SRS to estimate eAM WorkOrders in PAC
11 -- Flow:
12 -- |-- Insert into CST_PAC_EAM_WO_EST_STATUSES all WIP entities not yet
13 -- | estimated for the given cost type.
14 -- |-- For the job/Jobs to be estimated for the given cost type.
15 -- | |-- Update est flag to a -ve no for the jobs to be processed.
16 -- | |-- Call Delete_PAC_EamPerBal to delete prior estimation columns
17 -- | |-- Compute the estimates, call Compute_PAC_JobEstimates API
18 -- | |-- Update the est status to 7 if successfull or to 3 if errors out
19 -- | End Loop;
20 -- Update Estimation status of unprocessed jobs to Pending for any other
21 -- exception so that they can be processed in the next run.
22 -- Pre-reqs : None.
23 -- Parameters :
24 -- IN : errbuf OUT NOCOPY VARCHAR2 Conc req param
25 -- retcode OUT NOCOPY NUMBER Conc req param
26 -- p_legal_entity_id IN NUMBER Required
27 -- p_cost_type_id IN NUMBER Required
28 -- p_period_id IN NUMBER Required
29 -- p_cost_group_id IN NUMBER Required
30 -- p_entity_type IN NUMBER Optional DEFAULT 6
31 -- p_job_option IN NUMBER Optional DEFAULT 1
32 -- p_job_dummy IN NUMBER Optional DEFAULT NULL
33 -- p_wip_entity_id IN NUMBER Optional DEFAULT NULL
34 -- OUT :
35 -- Version : Current version 1.0
36 --
37 -- Notes : This procedure is called as a concurrent program to estiamte work orders
38 -- p_job_otion :
39 -- 1: All Jobs
40 -- 2: Specific job
41 --
42 -- Estimation Status:
43 -- NULL,1: Pending
44 -- -ve: Running
45 -- 3: Error
46 -- 7: Complete
47 --
48 -- End of comments
49
50 PROCEDURE Estimate_PAC_WipJobs(
51 errbuf OUT NOCOPY VARCHAR2,
52 retcode OUT NOCOPY NUMBER,
53 p_legal_entity_id IN NUMBER,
54 p_cost_type_id IN NUMBER,
55 p_period_id IN NUMBER,
56 p_cost_group_id IN NUMBER,
57 p_entity_type IN NUMBER DEFAULT 6,
58 p_job_option IN NUMBER DEFAULT 1,
59 p_job_dummy IN NUMBER DEFAULT NULL,
60 p_wip_entity_id IN NUMBER DEFAULT NULL
61 ) IS
62 l_api_name CONSTANT VARCHAR2(30) := 'Estimate_PAC_WipJobs';
63 l_api_version CONSTANT NUMBER := 1.0;
64 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
65 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
66
67 /* Log Severities*/
68 /* 6- UNEXPECTED */
69 /* 5- ERROR */
70 /* 4- EXCEPTION */
71 /* 3- EVENT */
72 /* 2- PROCEDURE */
73 /* 1- STATEMENT */
74
75 /* In general, we should use the following:
76 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
77 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
78 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
79 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
80 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
81 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
82 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
83 */
84
85 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
86 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
87 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
88
89 l_dummy NUMBER;
90 l_count NUMBER;
91 l_index NUMBER;
92 l_return_status VARCHAR(1);
93 l_msg_return_status VARCHAR2(1);
94 l_msg_count NUMBER := 0;
95 l_msg_data VARCHAR2(8000);
96 l_api_message VARCHAR2(1000);
97 l_stmt_num NUMBER := 0;
98 l_request_id NUMBER := 0;
99 l_user_id NUMBER := 0;
100 l_prog_id NUMBER := 0;
101 l_prog_app_id NUMBER := 0;
102 l_login_id NUMBER := 0;
103 l_conc_program_id NUMBER := 0;
104
105 l_estimation_group_id NUMBER := 0;
106 l_organization_id NUMBER := 0;
107
108 CONC_STATUS BOOLEAN;
109 PROCESS_ERROR EXCEPTION;
110
111 l_conc_warning_flag VARCHAR2(1);
112
113 l_wip_entity_id_tab CST_PacEamCost_GRP.G_WIP_ENTITY_TYP;
114 l_entity_id_tab CSTPECEP.wip_entity_id_type;
115
116 return_val BOOLEAN;
117 phase VARCHAR2(300);
118 status VARCHAR2(300);
119 dev_phase VARCHAR2(300);
120 dev_status VARCHAR2(300);
121 message VARCHAR2(300);
122
123 BEGIN
124
125 /* Procedure level log message for Entry point */
126 IF (l_pLog) THEN
127 FND_LOG.STRING(
128 FND_LOG.LEVEL_PROCEDURE,
129 l_module || '.begin',
130 'Estimate_PAC_WipJobs <<');
131 END IF;
132
133 -- Initializing Variables
134 l_conc_warning_flag := FND_API.G_FALSE;
135
136 -- Initialize API return status to success
137 l_return_status := FND_API.G_RET_STS_SUCCESS;
138 l_msg_return_status := FND_API.G_RET_STS_SUCCESS;
139 l_stmt_num := 5;
140
141 -- retrieving concurrent program information
142 l_request_id := FND_GLOBAL.conc_request_id;
143 l_user_id := FND_GLOBAL.user_id;
144 l_prog_id := FND_GLOBAL.conc_program_id;
145 l_prog_app_id := FND_GLOBAL.prog_appl_id;
146 l_login_id := FND_GLOBAL.conc_login_id;
147 l_conc_program_id := FND_GLOBAL.conc_program_id;
148
149 l_api_message := 'CST_PacEamCost_GRP.Estimate_PAC_WipJobs() params:'
150 || ' l_request_id ' || to_char(l_request_id)
151 || ' l_user_id ' || to_char(l_user_id)
152 || ' l_prog_id ' || to_char(l_prog_id)
153 || ' l_prog_app_id ' || to_char(l_prog_app_id)
154 || ' l_login_id ' || to_char(l_login_id)
155 || ' l_conc_program_id ' || to_char(l_conc_program_id)
156 || ' p_job_option ' || to_char(p_job_option)
157 || ' p_wip_entity_id ' || to_char(p_wip_entity_id);
158
159 -- statement level logging
160 IF (l_sLog) THEN
161 FND_LOG.STRING(
162 FND_LOG.LEVEL_STATEMENT,
163 l_module || '.' || l_stmt_num,
164 l_api_message);
165 END IF;
166
167 l_stmt_num := 10;
168
169 IF ((p_job_option = 2 AND p_wip_entity_id IS NULL)
170 OR (p_entity_type <> 6)) THEN
171
172 l_api_message := ' ( ' || to_char(l_stmt_num) || ' ): '
173 || 'Invalid Program Argument Combination';
174
175 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
176 l_api_name,
177 l_api_message);
178
179 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
180 'CST_PacEamCost_GRP.Estimate_PAC_WipJobs '
181 || l_api_message);
182
183 ELSE -- All parameters are valid
184
185 l_stmt_num := 15;
186
187 l_count := 0;
188
189 -- Check for concurrency. At one time only one estimation processor should be running
190 -- For a given Organization/legal entity/Cost type combination
191
192 SELECT count(*)
193 INTO l_count
194 FROM fnd_concurrent_requests FCR
195 WHERE FCR.program_application_id = l_prog_app_id
196 AND FCR.concurrent_program_id = l_prog_id
197 AND FCR.argument1 = to_char(p_legal_entity_id)
198 AND FCR.argument2 = to_char(p_cost_type_id)
199 AND FCR.argument4 = to_char(p_cost_group_id) -- Also adding CG for check
200 -- Adding the condions as estimating all jobs will not estimate an already
201 -- estimated job. So estimaing a specific job should have no concurreny problem.
202 AND FCR.argument6 = to_char(p_job_option)
203 AND nvl(FCR.argument8,-999) = to_char(nvl(p_wip_entity_id,-999))
204 AND FCR.phase_code = 'R';
205
206 IF l_count > 1 then -- more than 1 concurrent request running with same parameter combination
207
208 -- If More than 1 then error out
209 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
210 fnd_message.get_string('BOM','CST_REQ_ERROR'));
211
212 ELSE
213
214 l_dummy := 0;
215
216 -- Select estimate number, take -ve to denote estimation status is running
217 SELECT -1 * cst_wip_cost_estimates_s.NEXTVAL
218 INTO l_estimation_group_id
219 FROM DUAL;
220
221 l_stmt_num := 20;
222
223 /* Insert all discrete jobs not present in estimation table i.e. All
224 WorkOrders that are being estimated for a cost type for the first time
225 Also the stsus of WorkOrder should
226 AND then
227 Update status flag in PAC estimate status table for all jobs to be
228 estimated for each job option */
229
230
231 IF p_job_option=1 then
232
233 l_stmt_num := 25;
234
235
236 INSERT INTO CST_PAC_EAM_WO_EST_STATUSES es
237 ( legal_entity_id,
238 cost_group_id,
239 wip_entity_id,
240 organization_id,
241 cost_type_id,
242 estimation_status,
243 creation_date,
244 created_by,
245 last_update_date,
246 last_updated_by,
247 last_estimation_req_id,
248 LAST_ESTIMATION_DATE
249 )
250 ( SELECT
251 p_legal_entity_id,
252 p_cost_group_id,
253 wdj.wip_entity_id,
254 wdj.organization_id,
255 p_cost_type_id,
256 NULL,
257 SYSDATE,
258 l_user_id,
259 SYSDATE,
260 l_user_id,
261 l_request_id,
262 SYSDATE
263 FROM wip_discrete_jobs wdj,
264 wip_entities we,
265 cst_cost_group_assignments ccga
266 WHERE wdj.wip_entity_id = we.wip_entity_id
267 AND wdj.organization_id = ccga.organization_id
268 AND ccga.cost_group_id = p_cost_group_id
269 AND we.entity_type = 6
270 AND NOT EXISTS ( SELECT 'Not existing jobs'
271 FROM CST_PAC_EAM_WO_EST_STATUSES es1
272 WHERE es1.wip_entity_id = wdj.wip_entity_id
273 AND es1.legal_entity_id = p_legal_entity_id
274 AND es1.cost_type_id = p_cost_type_id
275 AND es1.cost_group_id = p_cost_group_id
276 )
277 AND wdj.status_type IN (1,3,4,6,17)
278 AND p_job_option = 1
279 AND p_entity_type = 6
280 AND EXISTS ( SELECT 'X'
281 FROM wip_entities we
282 WHERE we.wip_entity_id = wdj.wip_entity_id
283 AND we.entity_type = p_entity_type)
284 );
285
286 l_stmt_num := 30;
287
288 UPDATE CST_PAC_EAM_WO_EST_STATUSES es
289 SET es.estimation_status = l_estimation_group_id,
290 es.last_update_date = SYSDATE,
291 es.last_updated_by = l_user_id,
292 es.last_estimation_req_id = l_request_id
293 WHERE es.legal_entity_id = p_legal_entity_id
294 AND es.cost_type_id = p_cost_type_id
295 AND es.cost_group_id = p_cost_group_id
296 AND p_job_option = 1
297 AND p_entity_type = 6
298 AND NVL(es.estimation_status,1) <> 7 -- for all jobs do not re-estimate
299 AND EXISTS ( SELECT 'X'
300 FROM wip_entities we
301 WHERE we.wip_entity_id = es.wip_entity_id
302 AND we.entity_type = p_entity_type
303 )
304 AND NVL(es.estimation_status,1) > 0
305 AND EXISTS ( SELECT 'Status Check for WO'
306 FROM wip_discrete_jobs wdj
307 WHERE wdj.status_type IN (1,3,4,6,17)
308 AND wdj.wip_entity_id = nvl(p_wip_entity_id,wdj.wip_entity_id)
309 AND wdj.wip_entity_id = es.wip_entity_id
310 )
311 RETURNING es.wip_entity_id BULK COLLECT INTO l_wip_entity_id_tab;
312
313 l_stmt_num := 35;
314
315 COMMIT; -- COMMIT is imp here to maintain concurrency. This makes sure
316 -- that the same records are not picked up again.
317
318 ELSIF p_job_option=2 then
319
320 l_stmt_num := 40;
321
322 INSERT INTO CST_PAC_EAM_WO_EST_STATUSES es
323 ( legal_entity_id,
324 cost_group_id,
325 wip_entity_id,
326 organization_id,
327 cost_type_id,
328 estimation_status,
329 creation_date,
330 created_by,
331 last_update_date,
332 last_updated_by,
333 last_estimation_req_id,
334 LAST_ESTIMATION_DATE
335 )
336 ( SELECT p_legal_entity_id,
337 p_cost_group_id,
338 wdj.wip_entity_id,
339 wdj.organization_id,
340 p_cost_type_id,
341 NULL,
342 SYSDATE,
343 l_user_id,
344 SYSDATE,
345 l_user_id,
346 l_request_id,
347 SYSDATE
348 FROM wip_discrete_jobs wdj, wip_entities we
349 WHERE wdj.wip_entity_id = we.wip_entity_id
350 AND we.entity_type = 6
351 AND NOT EXISTS
352 ( SELECT 'Not existing jobs'
353 FROM CST_PAC_EAM_WO_EST_STATUSES es1
354 WHERE es1.wip_entity_id = p_wip_entity_id
355 AND es1.legal_entity_id = p_legal_entity_id
356 AND es1.cost_type_id = p_cost_type_id
357 AND es1.cost_group_id = p_cost_group_id
358 )
359 AND wdj.status_type IN (1,3,4,6,17)
360 AND p_job_option = 2
361 AND wdj.wip_entity_id = p_wip_entity_id );
362
363 l_stmt_num := 45;
364
365 UPDATE CST_PAC_EAM_WO_EST_STATUSES es
366 SET es.estimation_status = l_estimation_group_id,
367 es.last_update_date = SYSDATE,
368 es.last_updated_by = l_user_id,
369 es.last_estimation_req_id = l_request_id
370 WHERE es.legal_entity_id = p_legal_entity_id
371 AND es.cost_group_id = p_cost_group_id
372 AND es.cost_type_id = p_cost_type_id
373 AND p_job_option = 2
374 AND es.wip_entity_id = p_wip_entity_id
375 AND NVL(es.estimation_status,1) > 0
376 AND EXISTS ( SELECT 'Status Check for WO'
377 FROM wip_discrete_jobs wdj
378 WHERE wdj.status_type IN (1,3,4,6,17)
379 AND wdj.wip_entity_id = nvl(p_wip_entity_id,wdj.wip_entity_id)
380 AND wdj.wip_entity_id = es.wip_entity_id
381 )
382 RETURNING es.wip_entity_id BULK COLLECT INTO l_wip_entity_id_tab;
383
384 l_stmt_num := 50;
385
386 COMMIT; -- COMMIT is imp here to maintain concurrency. This makes sure
387 -- that the same records are not picked up again.
388
389 END IF;
390
391 -- statement level logging
392 IF (l_sLog) THEN
393 FND_LOG.STRING(
394 FND_LOG.LEVEL_STATEMENT,
395 l_module || '.' || l_stmt_num,
396 TO_CHAR(SQL%ROWCOUNT) ||' Job Record(s) Updated with Group Id: '
397 ||TO_CHAR(l_estimation_group_id));
398 END IF;
399
400 l_stmt_num := 55;
401
402 -- Default savepoint in the begining before starting processing jobs.
403 SAVEPOINT Estimate_PAC_WipJobs_PUB;
404
405 -----------------------
406 -- Processs WIP Jobs --
407 -----------------------
408
409 IF l_wip_entity_id_tab.COUNT > 0 THEN
410
411 l_stmt_num := 60;
412
413 -- Delete existing estimates
414 Delete_PAC_EamPerBal(
415 p_api_version => 1.0,
416 p_wip_entity_id_tab => l_wip_entity_id_tab,
417 p_legal_entity_id => p_legal_entity_id,
418 p_cost_group_id => p_cost_group_id,
419 p_cost_type_id => p_cost_type_id,
420 x_return_status => l_return_status,
421 x_msg_count => l_msg_count,
422 x_msg_data => l_msg_data);
423
424 l_stmt_num := 65;
425
426 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
427 l_api_message := 'CST_PacEamCost_GRP.delete_PacEamPerBal() failed';
428 l_msg_data := l_api_message;
429 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
430 l_api_name,
431 '('|| to_char(l_stmt_num) || '): '|| l_api_message);
432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- ERROR rollback and exit
433 END IF;
434
435 -- statement level logging
436 IF (l_sLog) THEN
437 l_api_message := 'CST_PacEamCost_GRP.Estimate_PAC_WipJobs('
438 || to_char(l_stmt_num) || '): '
439 || 'Delete/Update successful in delete_eamperbal';
440 FND_LOG.STRING(
441 FND_LOG.LEVEL_STATEMENT,
442 l_module || '.' || l_stmt_num,
443 l_api_message);
444 END IF;
445
446 /* Added the call to Delete_PAC_eamBalAcct as part of
447 eAM enhancements Project - R12 */
448
449 Delete_PAC_eamBalAcct (
450 p_api_version => 1.0,
451 p_init_msg_list => FND_API.G_FALSE,
452 p_commit => FND_API.G_FALSE,
453 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
454 p_wip_entity_id_tab => l_wip_entity_id_tab,
455 p_legal_entity_id => p_legal_entity_id,
456 p_cost_group_id => p_cost_group_id,
457 p_cost_type_id => p_cost_type_id,
458 x_return_status => l_return_status,
459 x_msg_count => l_msg_count,
460 x_msg_data => l_msg_data);
461
462 l_stmt_num := 66;
463
464 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
465 l_api_message := 'CST_PacEamCost_GRP.delete_PAC_EamBalAcct() failed';
466 l_msg_data := l_api_message;
467 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,l_api_name,
468 '('|| to_char(l_stmt_num) || '): '|| l_api_message);
469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- ERROR rollback and exit
470 END IF;
471
472 -- statement level logging
473 IF (l_sLog) THEN
474 l_api_message := 'CST_PacEamCost_GRP. delete_PAC_EamBalAcct ('
475 || to_char(l_stmt_num) || '): '
476 || 'Delete/Update successful in delete_pac_eambalacct';
477 FND_LOG.STRING(
478 FND_LOG.LEVEL_STATEMENT,
479 l_module || '.' || l_stmt_num,
480 l_api_message);
481 END IF;
482
483 l_stmt_num := 67;
484
485 /* Delete from the global temp table just to make sure it is empty */
486 DELETE FROM cst_eam_direct_items_temp;
487
488 l_stmt_num := 68;
489
490 /* Copying data to another table type as need to call perpetual est package */
491 For i in l_wip_entity_id_tab.FIRST..l_wip_entity_id_tab.LAST LOOP
492 l_entity_id_tab(i) := l_wip_entity_id_tab(i);
493 END LOOP;
494
495 /* Populate the Global Temp Table that replaces wip_eam_direct_items WEDIV
496 Thereafter in this file cst_eam_direct_items_temp CEDIT replaces WEDIV
497 This is done to improve the performance of the cursor queries in estimation*/
498 CST_eamCost_PUB.Insert_tempEstimateDetails (
499 p_api_version => 1.0,
500 x_return_status => l_return_status,
501 x_msg_count => l_msg_count,
502 x_msg_data => l_msg_data,
503 p_entity_id_tab => l_entity_id_tab
504 );
505
506 IF l_return_status <> FND_API.g_ret_sts_success THEN
507
508 l_api_message := 'Error: CST_eamCost_PUB.Insert_tempEstimateDetails()';
509
510 FND_MSG_PUB.ADD_EXC_MSG('CSTPECEP', 'ESTIMATE_WORKORDER('
511 ||TO_CHAR(l_stmt_num)
512 ||'): ', l_api_message);
513 RAISE FND_API.g_exc_error;
514
515 END IF;
516
517 -- statement level logging
518 IF (l_sLog) THEN
519 l_api_message := 'CST_eamCost_PUB.Insert_tempEstimateDetails ('
520 || to_char(l_stmt_num) || '): '
521 || 'Insert int CEDIV successful Insert_tempEstimateDetails';
522 FND_LOG.STRING(
523 FND_LOG.LEVEL_STATEMENT,
524 l_module || '.' || l_stmt_num,
525 l_api_message);
526 END IF;
527
528 l_stmt_num := 69;
529
530 -- Initializing the var to first record of PL/SQL table
531 l_index := l_wip_entity_id_tab.FIRST;
532
533 -- Looping thru the records which have to be processed
534 WHILE (l_index IS NOT NULL) LOOP
535
536 l_stmt_num := 70;
537
538 SAVEPOINT Estimate_PAC_WipJobs_PUB;
539
540 -- statement level logging
541 IF (l_sLog) THEN
542 FND_LOG.STRING(
543 FND_LOG.LEVEL_STATEMENT,
544 l_module || '.' || l_stmt_num,
545 'Processing Job:' || TO_CHAR(l_wip_entity_id_tab(l_index)));
546 END IF;
547
548 BEGIN
549
550 l_stmt_num := 75;
551
552 -- Estimate the Job
553 CST_PacEamCost_GRP.Compute_PAC_JobEstimates(
554 p_api_version => 1.0,
555 x_return_status => l_return_status,
556 x_msg_count => l_msg_count,
557 x_msg_data => l_msg_data,
558 p_legal_entity_id => p_legal_entity_id,
559 p_cost_group_id => p_cost_group_id,
560 p_cost_type_id => p_cost_type_id,
561 p_period_id => p_period_id,
562 p_wip_entity_id => l_wip_entity_id_tab(l_index),
563 p_user_id => l_user_id,
564 p_request_id => l_request_id,
565 p_prog_id => l_prog_id,
566 p_prog_app_id => l_prog_app_id,
567 p_login_id => l_login_id);
568
569 l_stmt_num := 80;
570
571 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
572 l_api_message := 'CST_PacEamCost_GRP.Compute_PAC_JobEstimates failed';
573 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
574 l_api_name,
575 '('|| to_char(l_stmt_num) || '): '|| l_api_message);
576 RAISE PROCESS_ERROR;
577 END IF;
578
579 -- set the status of successfully estimated job to to 7(complete)
580 UPDATE CST_PAC_EAM_WO_EST_STATUSES
581 SET estimation_status = 7,
582 last_estimation_date = SYSDATE,
583 last_estimation_req_id = l_request_id,
584 last_update_date = SYSDATE
585 WHERE wip_entity_id = l_wip_entity_id_tab(l_index)
586 AND legal_entity_id = p_legal_entity_id
587 AND cost_type_id = p_cost_type_id
588 AND cost_group_id = p_cost_group_id;
589
590 l_stmt_num := 85;
591
592 -- statement level logging
593 IF (l_sLog) THEN
594 l_api_message := 'Estimation complete for wip_entity_id = '
595 || to_char(l_wip_entity_id_tab(l_index));
596 FND_LOG.STRING(
597 FND_LOG.LEVEL_STATEMENT,
598 l_module || '.' || l_stmt_num,
599 l_api_message);
600 END IF;
601
602 EXCEPTION
603
604 WHEN PROCESS_ERROR THEN
605
606 ROLLBACK TO Estimate_PAC_WipJobs_PUB;
607
608 -- set the status of job for which estimation failed to 3(error)
609 UPDATE CST_PAC_EAM_WO_EST_STATUSES
610 SET estimation_status = 3,
611 last_update_date = SYSDATE,
612 last_estimation_date = SYSDATE,
613 last_estimation_req_id = l_request_id
614 WHERE wip_entity_id = l_wip_entity_id_tab(l_index)
615 AND legal_entity_id = p_legal_entity_id
616 AND cost_type_id = p_cost_type_id
617 AND cost_group_id = p_cost_group_id;
618
619 l_conc_warning_flag := FND_API.G_TRUE; -- When even one has failed. Display a warning.
620
621 l_stmt_num := 90;
622
623 -- statement level logging
624 IF (l_sLog) THEN
625 l_api_message := 'Estimation failed for wip_entity_id = '
626 || to_char(l_wip_entity_id_tab(l_index));
627 FND_LOG.STRING(
628 FND_LOG.LEVEL_STATEMENT,
629 l_module || '.' || l_stmt_num,
630 l_api_message);
631 END IF;
632
633 END;
634
635 -- Get the next index
636 l_index := l_wip_entity_id_tab.NEXT(l_index);
637
638 END LOOP; -- WHILE (l_index IS NOT NULL) LOOP
639
640 -- Set status to warning if even one failed. If no Error, default is Success.
641 IF FND_API.to_boolean(l_conc_warning_flag) THEN
642 l_stmt_num := 95;
643 IF p_job_option = 2 THEN
644 l_api_message := 'Estimation of the job failed';
645 ELSE
646 l_api_message := 'Estimation of one or more jobs failed';
647 END IF;
648
649 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', l_api_message);
650
651 END IF;
652
653 END IF; -- IF l_wip_entity_id_tab.COUNT > 0 THEN
654
655 END IF; -- IF l_dummy > 1
656
657 END IF; -- IF ((p_job_option = 2 AND p_wip_entity_id IS NULL) OR (p_entity_type <> 6)) THEN
658
659
660 -- Commit now as processing is complete.
661 COMMIT;
662
663 -- Procedure level log message for exit point
664 IF (l_pLog) THEN
665 FND_LOG.STRING(
666 FND_LOG.LEVEL_PROCEDURE,
667 l_module || '.end',
668 'Estimate_PAC_WipJobs >>'
669 );
670 END IF;
671
672 EXCEPTION
673
674 WHEN OTHERS THEN -- Error in delete is caught here too
675
676 ROLLBACK TO Estimate_PAC_WipJobs_PUB;
677
678 -- Change status of unprocessed jobs to 1 (Pending) so that they can be processed next time.
679 FORALL l_index IN l_wip_entity_id_tab.FIRST..l_wip_entity_id_tab.LAST
680 UPDATE CST_PAC_EAM_WO_EST_STATUSES
681 SET estimation_status = 1,
682 last_update_date = SYSDATE,
683 last_estimation_date = SYSDATE,
684 last_estimation_req_id = l_request_id
685 WHERE estimation_status = l_estimation_group_id
686 AND wip_entity_id = l_wip_entity_id_tab(l_index)
687 AND legal_entity_id = p_legal_entity_id
688 AND cost_type_id = p_cost_type_id
689 AND cost_group_id = p_cost_group_id;
690
691 IF (l_uLog) THEN
692 FND_LOG.STRING(
693 FND_LOG.LEVEL_UNEXPECTED,
694 l_module || '.' || l_stmt_num ,
695 l_msg_data); -- show the message of where it failed.
696 END IF;
697
698 l_api_message := '(' || TO_CHAR(l_stmt_num) || ') : '|| SUBSTRB (SQLERRM , 1 , 240);
699
700 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
701 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
702 l_api_name,
703 l_api_message);
704 END IF;
705
706 -- Set status of conc process to Error.
707 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
708 'CST_PacEamCost_GRP.Estimate_PAC_WipJobs ' ||l_api_message);
709
710 COMMIT;
711
712 END Estimate_PAC_WipJobs;
713
714
715 -- Start of comments
716 -- API name : Delete_PAC_EamPerBal
717 -- Type : Public.
718 -- Function : This API is called from Estimate_PAC_WipJobs
719 -- Flow:
720 -- |-- Get estimation details of the wip_entity LOOP
721 -- | |--Update amount in cst_pac_eam_asset_per_balances
722 -- | End Loop;
723 -- |-- Update estimation columns of cst_pac_eam_period_balances to 0
724 -- |-- Delete the row in cst_pac_eam_period_balances if estimation and
725 -- | actual cost columns are 0 or null
726 -- |-- Similarly delete the row in cst_pac_eam_asset_per_balances if
727 -- | estimation and actual cost columns are 0 or null
728 --
729 -- Pre-reqs : None.
730 -- Parameters :
731 -- IN : p_api_version IN NUMBER Required
732 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
733 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
734 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
735 -- p_legal_entity_id IN NUMBER Required
736 -- p_cost_group_id IN NUMBER Required
737 -- p_cost_type_id IN NUMBER Required
738 -- p_organization_id IN NUMBER Required
739 -- p_wip_entity_id_tab IN CST_PacEamCost_GRP.G_WIP_ENTITY_TYP Required
740 -- OUT : x_return_status OUT VARCHAR2(1)
741 -- x_msg_count OUT NUMBER
742 -- x_msg_data OUT VARCHAR2(2000)
743 -- Version : Current version 1.0
744 --
745 -- Notes : This procedure does bulk deletes and bulk updates of the prior estimation
746 -- data for the particular Legal Entity/Cost Group/Cost Type using the PL/SQL table
747 --
748 -- End of comments
749
750 PROCEDURE Delete_PAC_EamPerBal (
751 p_api_version IN NUMBER,
752 p_init_msg_list IN VARCHAR2,
753 p_commit IN VARCHAR2,
754 p_validation_level IN VARCHAR2,
755 x_return_status OUT NOCOPY VARCHAR2,
756 x_msg_count OUT NOCOPY NUMBER,
757 x_msg_data OUT NOCOPY VARCHAR2,
758 p_legal_entity_id IN NUMBER,
759 p_cost_group_id IN NUMBER,
760 p_cost_type_id IN NUMBER,
761 p_wip_entity_id_tab IN CST_PacEamCost_GRP.G_WIP_ENTITY_TYP
762 ) IS
763
764 l_api_name CONSTANT VARCHAR2(30) := 'Delete_PAC_EamPerBal';
765 l_api_version CONSTANT NUMBER := 1.0;
766
767 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
768 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
769
770 l_return_status VARCHAR2(1);
771 l_msg_count NUMBER;
772 l_msg_data VARCHAR2(8000);
773 l_stmt_num NUMBER;
774 l_api_message VARCHAR2(1000);
775
776 l_index NUMBER;
777 l_asset_group_id NUMBER;
778 l_asset_number VARCHAR2(30);
779 l_asset_count NUMBER;
780 l_act_mat_cost NUMBER;
781 l_act_lab_cost NUMBER;
782 l_act_eqp_cost NUMBER;
783 l_sys_mat_est NUMBER;
784 l_sys_lab_est NUMBER;
785 l_sys_eqp_est NUMBER;
786
787 l_txn_date VARCHAR2(21) := to_char(sysdate,'YYYY/MM/DD HH24:MI:SS');
788 l_organization_id NUMBER;
789
790 /* Log Severities*/
791 /* 6- UNEXPECTED */
792 /* 5- ERROR */
793 /* 4- EXCEPTION */
794 /* 3- EVENT */
795 /* 2- PROCEDURE */
796 /* 1- STATEMENT */
797
798 /* In general, we should use the following:
799 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
800 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
801 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
802 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
803 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
804 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
805 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
806 */
807
808 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
809 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
810 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
811
812 CURSOR v_est_csr(c_organization_id NUMBER,
813 c_wip_entity_id NUMBER) IS
814 SELECT period_set_name,
815 period_name,
816 maint_cost_category,
817 sum(NVL(system_estimated_mat_cost,0)) sys_mat,
818 sum(NVL(system_estimated_lab_cost,0)) sys_lab,
819 sum(NVL(system_estimated_eqp_cost,0)) sys_eqp
820 FROM cst_pac_eam_period_balances
821 WHERE wip_entity_id = c_wip_entity_id
822 AND organization_id = c_organization_id
823 AND legal_entity_id = p_legal_entity_id
824 AND cost_group_id = p_cost_group_id
825 AND cost_type_id = p_cost_type_id
826 GROUP BY period_set_name,
827 period_name,
828 maint_cost_category;
829
830 BEGIN
831
832 -- Procedure level log message for Entry point
833 IF (l_pLog) THEN
834 FND_LOG.STRING(
835 FND_LOG.LEVEL_PROCEDURE,
836 l_module || '.begin',
837 'Delete_PAC_EamPerBal <<');
838 END IF;
839
840 /* Standard Start of API savepoint */
841 SAVEPOINT Delete_PAC_EamPerBal_PUB;
842
843 /* Standard call to check for call compatibility */
844 IF NOT FND_API.Compatible_API_Call (l_api_version,
845 p_api_version,
846 l_api_name,
847 G_PKG_NAME ) THEN
848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
849 END IF;
850
851 /* Initialize message list if p_init_msg_list is set to TRUE */
852 IF FND_API.to_Boolean(p_init_msg_list) THEN
853 FND_MSG_PUB.initialize;
854 END IF;
855
856 /* Initialize API return status to success */
857 l_return_status := FND_API.G_RET_STS_SUCCESS;
858 x_return_status := FND_API.G_RET_STS_SUCCESS;
859
860 /* Get asset group and asset number of job */
861 l_stmt_num := 100;
862
863 IF p_wip_entity_id_tab.COUNT > 0 THEN -- Process only if records exist
864
865 -- Initializing the var to first record of PL/SQL table
866 l_index := p_wip_entity_id_tab.FIRST;
867
868 -- Looping thru the records which have to be processed
869 WHILE (l_index IS NOT NULL) LOOP
870
871 l_stmt_num := 105;
872
873 SELECT asset_group_id,
874 asset_number,
875 organization_id
876 INTO l_asset_group_id,
877 l_asset_number,
878 l_organization_id
879 FROM wip_discrete_jobs
880 WHERE wip_entity_id = p_wip_entity_id_tab(l_index);
881
882 l_stmt_num := 110;
883
884 FOR v_est_rec IN v_est_csr(l_organization_id,
885 p_wip_entity_id_tab(l_index)) LOOP
886
887 -- Update system estimates in cst_pac_eam_asset_per_balances
888 IF ( v_est_rec.sys_mat <> 0
889 OR v_est_rec.sys_lab <> 0
890 OR v_est_rec.sys_eqp <> 0) THEN
891
892 l_stmt_num := 120;
893
894 UPDATE cst_pac_eam_asset_per_balances
895 SET system_estimated_mat_cost = system_estimated_mat_cost -
896 v_est_rec.sys_mat,
897 system_estimated_lab_cost = system_estimated_lab_cost -
898 v_est_rec.sys_lab,
899 system_estimated_eqp_cost = system_estimated_eqp_cost -
900 v_est_rec.sys_eqp
901 WHERE legal_entity_id = p_legal_entity_id
902 AND cost_group_id = p_cost_group_id
903 AND cost_type_id = p_cost_type_id
904 AND period_set_name = v_est_rec.period_set_name
905 AND period_name = v_est_rec.period_name
906 AND inventory_item_id = l_asset_group_id
907 AND serial_number = l_asset_number
908 AND maint_cost_category = v_est_rec.maint_cost_category;
909
910 END IF;
911
912 END LOOP;
913
914 -- Delete cpeapb rows with zeros in ALL value columns
915 DELETE from cst_pac_eam_asset_per_balances
916 WHERE NVL(actual_mat_cost,0) = 0
917 AND NVL(actual_lab_cost,0) = 0
918 AND NVL(actual_eqp_cost,0) = 0
919 AND NVL(system_estimated_mat_cost,0) = 0
920 AND NVL(system_estimated_lab_cost,0) = 0
921 AND NVL(system_estimated_eqp_cost,0) = 0
922 AND inventory_item_id = l_asset_group_id
923 AND serial_number = l_asset_number
924 AND legal_entity_id = p_legal_entity_id
925 AND cost_group_id = p_cost_group_id
926 AND cost_type_id = p_cost_type_id ;
927
928 -- statement level logging
929 IF (l_sLog) THEN
930 FND_LOG.STRING(
931 FND_LOG.LEVEL_STATEMENT,
932 l_module || '.' || l_stmt_num,
933 'Delete/Update CPEAPB successful for ' || TO_CHAR(p_wip_entity_id_tab(l_index)));
934 END IF;
935
936 -- Get the next index
937 l_index := p_wip_entity_id_tab.NEXT(l_index);
938
939 END LOOP; -- WHILE (l_index IS NOT NULL) LOOP
940
941
942 l_stmt_num := 130;
943
944 -- statement level logging
945 IF (l_sLog) THEN
946 FND_LOG.STRING(
947 FND_LOG.LEVEL_STATEMENT,
948 l_module || '.' || l_stmt_num,
949 'CPEAPB Updation completed successfully.');
950 END IF;
951
952 -- Update cpepb estimates to zeros
953 FORALL l_index IN p_wip_entity_id_tab.FIRST..p_wip_entity_id_tab.LAST
954 UPDATE cst_pac_eam_period_balances
955 SET system_estimated_mat_cost = 0,
956 system_estimated_lab_cost = 0,
957 system_estimated_eqp_cost = 0
958 WHERE wip_entity_id = p_wip_entity_id_tab(l_index)
959 AND legal_entity_id = p_legal_entity_id
960 AND cost_group_id = p_cost_group_id
961 AND cost_type_id = p_cost_type_id ;
962
963 l_stmt_num := 140;
964
965 -- statement level logging
966 IF (l_sLog) THEN
967 FND_LOG.STRING(
968 FND_LOG.LEVEL_STATEMENT,
969 l_module || '.' || l_stmt_num,
970 'CPEPB Updation completed successfully.');
971 END IF;
972
973 -- Delete cpepb rows with zeros in ALL value columns
974 FORALL l_index IN p_wip_entity_id_tab.FIRST..p_wip_entity_id_tab.LAST
975 DELETE FROM cst_pac_eam_period_balances
976 WHERE actual_mat_cost = 0
977 AND NVL(actual_lab_cost,0) = 0
978 AND NVL(actual_eqp_cost,0) = 0
979 AND NVL(system_estimated_mat_cost,0) = 0
980 AND NVL(system_estimated_lab_cost,0) = 0
981 AND NVL(system_estimated_eqp_cost,0) = 0
982 AND wip_entity_id = p_wip_entity_id_tab(l_index)
983 AND legal_entity_id = p_legal_entity_id
984 AND cost_group_id = p_cost_group_id
985 AND cost_type_id = p_cost_type_id ;
986
987 l_stmt_num := 150;
988
989 -- statement level logging
990 IF (l_sLog) THEN
991 FND_LOG.STRING(
992 FND_LOG.LEVEL_STATEMENT,
993 l_module || '.' || l_stmt_num,
994 'Delted from CPEPB successfully.');
995 END IF;
996
997 END IF; -- end check count of records
998
999 -- Procedure level log message for exit point
1000 IF (l_pLog) THEN
1001 FND_LOG.STRING(
1002 FND_LOG.LEVEL_PROCEDURE,
1003 l_module || '.end',
1004 'Delete_PAC_EamPerBal >>'
1005 );
1006 END IF;
1007
1008 EXCEPTION
1009
1010 WHEN FND_API.G_EXC_ERROR THEN
1011 ROLLBACK TO Delete_PAC_EamPerBal_PUB;
1012 x_return_status := FND_API.g_ret_sts_error;
1013 /* Get message count and data */
1014 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
1015 p_data => x_msg_data);
1016
1017 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1018 ROLLBACK TO Delete_PAC_EamPerBal_PUB;
1019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1020
1021 /* Get message count and data */
1022 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
1023 p_data => x_msg_data);
1024
1025 WHEN OTHERS THEN
1026 ROLLBACK TO Delete_PAC_EamPerBal_PUB;
1027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1028
1029 IF (l_uLog) THEN
1030 FND_LOG.STRING(
1031 FND_LOG.LEVEL_UNEXPECTED,
1032 l_module || '.' || l_stmt_num ,
1033 SUBSTRB (SQLERRM , 1 , 240));
1034 END IF;
1035
1036 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1037 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1038 l_api_name,
1039 '(' || TO_CHAR(l_stmt_num) || ') : '
1040 || SUBSTRB (SQLERRM , 1 , 240));
1041 END IF;
1042
1043 /* Get message count and data */
1044 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
1045 p_data => x_msg_data);
1046
1047 END Delete_PAC_EamPerBal;
1048
1049
1050 -- Start of comments
1051 -- API name : Compute_PAC_JobEstimates
1052 -- Type : Public.
1053 -- Function : This API is called from Estimate_PAC_WipJobs
1054 -- Flow:
1055 -- |-- Check Entity Type is eAM
1056 -- |-- Get charge asset using API
1057 -- |-- Get the period set name and period name
1058 -- | |-- if scheduled date is in current PAC period use CST_PAC_PERIODS
1059 -- | |-- else if its in a future period use GL_PERIODS
1060 -- | End IF
1061 -- |-- Derive the currency extended precision for the organization
1062 -- |-- Derive valuation rates cost type based on organization's cost method
1063 -- |-- For Resources, open c_wor cursor LOOP
1064 -- | |-- Get_MaintCostCat (Get category, owning dept and operating dept)
1065 -- | |-- Get_eamCostElement
1066 -- | |-- InsertUpdate_PAC_eamPerBal (send asset number, category, wip entity id, eAM cost element, departments etc.)
1067 -- | |-- For Resource based Overheads open c_rbo cursor LOOP
1068 -- | | |-- InsertUpdate_PAC_eamPerBal
1069 -- | | END LOOP for c_rbo
1070 -- | |-- ADD value for the total resource based Overheads for this resource and the resource value
1071 -- | END LOOP for c_wor
1072 -- |-- Compute Material Costs, open c_wro cursor LOOP
1073 -- | |--Get_MaintCostCat (Get category, owning dept and operating dept)
1074 -- | |--Get_eamCostElement
1075 -- | |--InsertUpdate_PAC_eamPerBal
1076 -- | END LOOP
1077 -- |-- For 'Non-stockable' Direct Items open c_wrodi cursor LOOP
1078 -- | |--Get_MaintCostCat (Get category, owning dept and operating dept)
1079 -- | |--Get_eamCostElement
1080 -- | |--InsertUpdate_PAC_eamPerBal
1081 -- | END LOOP
1082 -- |-- For 'Description based' Direct Items open c_wedi cursor LOOP
1083 -- | |--Get_MaintCostCat (Get category, owning dept and operating dept)
1084 -- | |--Get Cost Element from CST_CAT_ELE_EXP_ASSOCS table (not from API)
1085 -- | |--InsertUpdate_PAC_eamPerBal
1086 -- | END LOOP
1087 -- |-- For PO and REQ open c_pda cursor LOOP
1088 -- | |--Get_MaintCostCat (Get category, owning dept and operating dept)
1089 -- | |--Get Cost Element from cst_CAT_ELE_EXP_ASSOCS table (not from API)
1090 -- | |--InsertUpdate_PAC_eamPerBal
1091 -- | END LOOP
1092 --
1093 --
1094 -- Pre-reqs : None.
1095 -- Parameters :
1096 -- IN : p_api_version IN NUMBER Required
1097 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
1098 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1099 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1100 -- p_cost_group_id IN NUMBER Required
1101 -- p_legal_entity_id IN NUMBER Required
1102 -- p_Period_id IN NUMBER Required
1103 -- p_wip_entity_id IN NUMBER Required
1104 -- p_user_id IN NUMBER Required
1105 -- p_request_id IN NUMBER Required
1106 -- p_prog_id IN NUMBER Required
1107 -- p_prog_app_id IN NUMBER Required
1108 -- p_login_id IN NUMBER Required
1109 -- OUT : x_return_status OUT VARCHAR2(1)
1110 -- x_msg_count OUT NUMBER
1111 -- x_msg_data OUT VARCHAR2(2000)
1112 -- Version : Current version 1.0
1113 --
1114 -- Notes : This procedure calculates the estimates for the Work Order for the
1115 -- Legal Entity/Cost Group/Cost Type association
1116 --
1117 -- End of comments
1118
1119 PROCEDURE Compute_PAC_JobEstimates (
1120 p_api_version IN NUMBER,
1121 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1122 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1123 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1124 x_return_status OUT NOCOPY VARCHAR2,
1125 x_msg_count OUT NOCOPY NUMBER,
1126 x_msg_data OUT NOCOPY VARCHAR2,
1127 p_legal_entity_id IN NUMBER,
1128 p_cost_group_id IN NUMBER,
1129 p_cost_type_id IN NUMBER,
1130 p_Period_id IN NUMBER,
1131 p_wip_entity_id IN NUMBER,
1132 p_user_id IN NUMBER,
1133 p_request_id IN NUMBER,
1134 p_prog_id IN NUMBER,
1135 p_prog_app_id IN NUMBER,
1136 p_login_id IN NUMBER
1137 ) IS
1138
1139 l_api_name CONSTANT VARCHAR2(30) := 'Compute_PAC_JobEstimates';
1140 l_api_version CONSTANT NUMBER := 1.0;
1141
1142 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1143 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
1144
1145 l_return_status VARCHAR(1) := FND_API.G_RET_STS_SUCCESS;
1146 l_msg_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1147 l_msg_count NUMBER := 0;
1148 l_msg_data VARCHAR2(8000);
1149
1150 l_api_message VARCHAR2(250);
1151 l_stmt_num NUMBER;
1152
1153 l_lot_size NUMBER;
1154 l_scheduled_completion_date DATE;
1155 l_entity_type NUMBER;
1156 l_organization_id NUMBER;
1157 l_asset_group_item_id NUMBER;
1158 l_asset_number VARCHAR2(30);
1159 l_mnt_obj_id NUMBER;
1160 l_trunc_le_sched_comp_date DATE;
1161 l_dummy NUMBER;
1162 l_period_set_name VARCHAR2(80);
1163 l_period_name VARCHAR2(80);
1164 l_acct_period_id NUMBER;
1165 l_round_unit NUMBER;
1166 l_precision NUMBER;
1167 l_ext_precision NUMBER;
1168 l_prior_period_id NUMBER;
1169 l_pac_rates_id NUMBER;
1170 l_operation_dept_id NUMBER;
1171 l_owning_dept_id NUMBER;
1172 l_dept_id NUMBER;
1173 l_maint_cost_category NUMBER;
1174 l_eam_cost_element NUMBER;
1175 l_sum_rbo NUMBER;
1176
1177 l_mfg_cost_element_id NUMBER;
1178 l_period_start_date DATE;
1179
1180 l_acct_id NUMBER;
1181 l_material_account NUMBER;
1182 l_material_overhead_account NUMBER;
1183 l_resource_account NUMBER;
1184 l_osp_account NUMBER;
1185 l_overhead_account NUMBER;
1186 l_wip_acct_class VARCHAR2(11);
1187
1188 l_exec_flag NUMBER;
1189 l_index_var NUMBER;
1190 l_value NUMBER;
1191 l_account NUMBER;
1192
1193
1194 /* Log Severities*/
1195 /* 6- UNEXPECTED */
1196 /* 5- ERROR */
1197 /* 4- EXCEPTION */
1198 /* 3- EVENT */
1199 /* 2- PROCEDURE */
1200 /* 1- STATEMENT */
1201
1202 /* In general, we should use the following:
1203 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1204 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
1205 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1206 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1207 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
1208 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1209 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1210 */
1211
1212 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
1213 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1214 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1215
1216
1217 /* Cursor to fetch all resources and their rates for a wip entity */
1218
1219 CURSOR c_wor IS
1220 SELECT wor.operation_seq_num operation_seq_num,
1221 crc.resource_rate resource_rate,
1222 wor.uom_code uom,
1223 wor.usage_rate_or_amount resource_usage,
1224 DECODE(br.functional_currency_flag,
1225 1, 1,
1226 NVL(crc.resource_rate,0))
1227 * wor.usage_rate_or_amount
1228 * DECODE(wor.basis_type,
1229 1, l_lot_size, 2, 1, 1) raw_resource_value,
1230 ROUND(DECODE(br.functional_currency_flag,
1231 1, 1,
1232 NVL(crc.resource_rate,0))
1233 * wor.usage_rate_or_amount
1234 * DECODE(wor.basis_type,
1235 1, l_lot_size,
1236 2, 1, 1) ,l_ext_precision) resource_value,
1237 wor.resource_id resource_id,
1238 wor.resource_seq_num resource_seq_num,
1239 wor.basis_type basis_type,
1240 wor.usage_rate_or_amount
1241 * DECODE(wor.basis_type,
1242 1, l_lot_size,
1243 2, 1, 1) usage_rate_or_amount,
1244 wor.standard_rate_flag standard_flag,
1245 wor.department_id department_id,
1246 br.functional_currency_flag functional_currency_flag,
1247 br.cost_element_id cost_element_id,
1248 br.resource_type resource_type
1249 FROM wip_operation_resources wor,
1250 bom_resources br,
1251 cst_resource_costs crc
1252 WHERE wor.wip_entity_id = p_wip_entity_id
1253 AND br.resource_id = wor.resource_id
1254 AND br.organization_id = wor.organization_id
1255 AND crc.resource_id = wor.resource_id
1256 AND crc.cost_type_id = l_pac_rates_id;
1257
1258
1259 /* Overheads associated with the resource that would be fetched by
1260 the above cursor */
1261
1262 CURSOR c_rbo (p_resource_id NUMBER,
1263 p_dept_id NUMBER,
1264 p_organization_id NUMBER,
1265 p_res_units NUMBER,
1266 p_res_value NUMBER) IS
1267 SELECT cdo.overhead_id ovhd_id,
1268 cdo.rate_or_amount actual_cost,
1269 cdo.basis_type basis_type,
1270 ROUND(cdo.rate_or_amount
1271 * DECODE(cdo.basis_type,
1272 3, p_res_units, p_res_value),
1273 l_ext_precision) rbo_value,
1274 cdo.department_id
1275 FROM cst_resource_overheads cro,
1276 cst_department_overheads cdo
1277 WHERE cdo.department_id = p_dept_id
1278 AND cdo.organization_id = p_organization_id
1279 AND cdo.cost_type_id = l_pac_rates_id
1280 AND cdo.basis_type IN (3,4)
1281 AND cro.cost_type_id = cdo.cost_type_id
1282 AND cro.resource_id = p_resource_id
1283 AND cro.overhead_id = cdo.overhead_id
1284 AND cro.organization_id = cdo.organization_id;
1285
1286
1287 /* Select the materials reqt from WRO for the wip Entity */
1288
1289 CURSOR c_wro IS
1290 SELECT wro.operation_seq_num operation_seq_num,
1291 wro.department_id department_id,
1292 ROUND(SUM(NVL(wro.required_quantity,0)
1293 * DECODE(msi.eam_item_type,
1294 3, decode(wdj.issue_zero_cost_flag,
1295 'Y',0,
1296 nvl(cpic.item_cost,0)),
1297 NVL(cpic.item_cost,0))), l_ext_precision) mat_value,
1298 ROUND(SUM(NVL(wro.required_quantity,0) *
1299 decode(msi.eam_item_type,
1300 3,decode(wdj.issue_zero_cost_flag,
1301 'Y',0,
1302 nvl(cpic.material_cost,0)),
1303 NVL(cpic.material_cost,0))), l_ext_precision) material_cost,
1304 ROUND(SUM(NVL(wro.required_quantity,0) *
1305 decode(msi.eam_item_type,
1306 3,decode(wdj.issue_zero_cost_flag,
1307 'Y',0,
1308 nvl(cpic.material_overhead_cost,0)),
1309 NVL(cpic.material_overhead_cost,0))), l_ext_precision)
1310 material_overhead_cost,
1311 ROUND(SUM(NVL(wro.required_quantity,0) *
1312 decode(msi.eam_item_type,
1313 3,decode(wdj.issue_zero_cost_flag,
1314 'Y',0,
1315 nvl(cpic.resource_cost,0)),
1316 NVL(cpic.resource_cost,0))), l_ext_precision)
1317 resource_cost,
1318 ROUND(SUM(NVL(wro.required_quantity,0) *
1319 decode(msi.eam_item_type,
1320 3,decode(wdj.issue_zero_cost_flag,
1321 'Y',0,
1322 nvl(cpic.outside_processing_cost,0)),
1323 NVL(cpic.outside_processing_cost,0))), l_ext_precision)
1324 outside_processing_cost,
1325 ROUND(SUM(NVL(wro.required_quantity,0) *
1326 decode(msi.eam_item_type,
1327 3,decode(wdj.issue_zero_cost_flag,
1328 'Y',0,
1329 nvl(cpic.overhead_cost,0)),
1330 NVL(cpic.overhead_cost,0))), l_ext_precision) overhead_cost
1331 FROM wip_requirement_operations wro,
1332 cst_pac_item_costs cpic,
1333 mtl_system_items_b msi,
1334 wip_discrete_jobs wdj
1335 WHERE wro.wip_entity_id = p_wip_entity_id
1336 AND wdj.wip_entity_id = wro.wip_entity_id
1337 AND cpic.inventory_item_id = wro.inventory_item_id
1338 AND cpic.cost_group_id = p_cost_group_id
1339 AND cpic.pac_period_id = l_prior_period_id /* Prior period id */
1340 AND wro.wip_supply_type IN (1,4)
1341 AND nvl(wro.released_quantity,-1) <> 0
1342 /* Non stockable items will be included in c_wrodi */
1343 AND msi.organization_id = wro.organization_id
1344 AND msi.inventory_item_id = wro.inventory_item_id
1345 AND msi.stock_enabled_flag = 'Y'
1346 GROUP BY wro.operation_seq_num,
1347 wro.department_id;
1348
1349 /* Cursor to select any non-stockable based direct items, exclude
1350 those which have REQ or PO to be picked by c_pda*/
1351
1352 CURSOR c_wrodi IS
1353 SELECT wro.operation_seq_num operation_seq_num,
1354 wro.department_id department_id,
1355 msi.inventory_item_id item_id,
1356 mic.category_id category_id,
1357 ROUND(SUM(DECODE(SIGN(NVL(wro.required_quantity,0)
1358 - NVL(cediv.quantity_ordered,0)),
1359 1, NVL(wro.required_quantity,0)
1360 - NVL(cediv.quantity_ordered,0),
1361 0)
1362 * NVL(wro.unit_price,0)), l_ext_precision) mat_value
1363 FROM wip_requirement_operations wro,
1364 (SELECT ced.work_order_number,
1365 ced.organization_id,
1366 ced.task_number,
1367 ced.item_id,
1368 SUM(inv_convert.inv_um_convert(ced.item_id,
1369 NULL,
1370 ced.quantity_ordered,
1371 ced.uom_code,
1372 msi.primary_uom_code,
1373 NULL,
1374 NULL)
1375 ) quantity_ordered
1376 /* We convert to primary_uom because the required_quantity in WRO is
1377 always in the primary unit of measure. Sum is needed because there
1378 could be multiple POs/Reqs for the same non-stockable item */
1379 FROM cst_eam_direct_items_temp ced,
1380 mtl_system_items_b msi
1381 WHERE ced.item_id = msi.inventory_item_id
1382 AND ced.organization_id = msi.organization_id
1383 AND ced.work_order_number = p_wip_entity_id
1384 GROUP BY ced.work_order_number,
1385 ced.organization_id,
1386 ced.task_number,
1387 ced.item_id
1388 ) cediv,
1389 mtl_system_items_b msi,
1390 mtl_item_categories mic,
1391 mtl_default_category_sets mdcs
1392 WHERE wro.wip_entity_id = p_wip_entity_id
1393 AND cediv.work_order_number(+) = wro.wip_entity_id
1394 AND cediv.item_id(+) = wro.inventory_item_id
1395 AND cediv.organization_id(+) = wro.organization_id
1396 AND cediv.task_number(+) = wro.operation_seq_num
1397 AND wro.wip_supply_type IN (1,4)
1398 AND msi.organization_id = wro.organization_id
1399 AND msi.inventory_item_id = wro.inventory_item_id
1400 AND msi.stock_enabled_flag = 'N'
1401 AND msi.inventory_item_id = mic.inventory_item_id
1402 AND mic.category_set_id = mdcs.category_set_id
1403 AND mic.organization_id = wro.organization_id
1404 AND mdcs.functional_area_id = 2
1405 GROUP BY wro.operation_seq_num,
1406 wro.department_id,
1407 msi.inventory_item_id,
1408 mic.category_id;
1409
1410 /* Cursor to select any description based direct items, exclude those which have
1411 REQ or PO to be picked by c_pda*/
1412
1413 CURSOR c_wedi IS
1414 SELECT wedi.operation_seq_num operation_seq_num,
1415 wedi.department_id department_id,
1416 wedi.purchasing_category_id category_id,
1417 wedi.direct_item_sequence_id direct_item_id,
1418 ROUND(
1419 DECODE(cedit.order_type_lookup_code,
1420 'FIXED PRICE', NVL(wedi.amount,0) * NVL(cedit.currency_rate,1) - sum( NVL(cedit.amount_delivered ,0)),
1421 'RATE', NVL(wedi.amount,0) * NVL(cedit.currency_rate,1) - sum(NVL(cedit.amount_delivered ,0)),
1422 DECODE(SIGN(NVL(wedi.required_quantity,0)
1423 - SUM(inv_convert.inv_um_convert(NULL,
1424 NULL,
1425 NVL(cedit.quantity_ordered,0),
1426 NVL(cedit.uom_code, wedi.uom),
1427 wedi.uom,
1428 NULL,
1429 NULL))),
1430 1, (NVL(wedi.required_quantity,0)
1431 - SUM(inv_convert.inv_um_convert(NULL,
1432 NULL,
1433 NVL(cedit.quantity_ordered,0),
1434 NVL(cedit.uom_code, wedi.uom),
1435 wedi.uom,
1436 NULL,
1437 NULL))),
1438 0) * NVL(wedi.unit_price, 0) * NVL(cedit.currency_rate,1)), l_ext_precision) wedi_value
1439 FROM wip_eam_direct_items wedi,
1440 cst_eam_direct_items_temp cedit
1441 WHERE wedi.wip_entity_id = p_wip_entity_id
1442 AND cedit.work_order_number(+) = wedi.wip_entity_id
1443 AND cedit.organization_id(+) = wedi.organization_id
1444 AND cedit.direct_item_sequence_id(+) = wedi.direct_item_sequence_id
1445 AND cedit.task_number(+) = wedi.operation_seq_num
1446 /* AND cedit.category_id(+) = wedi.purchasing_category_id Commented for bug 5478136 */
1447 GROUP BY wedi.operation_seq_num,
1448 wedi.department_id,
1449 wedi.purchasing_category_id,
1450 wedi.direct_item_sequence_id,
1451 NVL(wedi.required_quantity,0),
1452 NVL(wedi.unit_price,0),
1453 cedit.order_type_lookup_code,
1454 NVL(wedi.amount,0),
1455 NVL(cedit.currency_rate,1);
1456
1457
1458 /* Cursor to pick-up value of direct items for which REQ/PO was created */
1459
1460 CURSOR c_pda IS
1461 SELECT ROUND(SUM(decode(NVL(pla.order_type_lookup_code,'QUANTITY'),
1462 'RATE',NVL(cedit.amount,0) - (NVL(pda.amount_cancelled,0)
1463 + /* Tax */ PO_TAX_SV.get_tax('PO',pda.po_distribution_id))* NVL(cedit.currency_rate,1),
1464 'FIXED PRICE',NVL(cedit.amount,0) - (NVL(pda.amount_cancelled,0)
1465 + /* Tax */ PO_TAX_SV.get_tax('PO',pda.po_distribution_id))* NVL(cedit.currency_rate,1),
1466 NVL(plla.price_override,0) *
1467 (NVL(pda.quantity_ordered,0) - NVL(pda.quantity_cancelled,0)
1468 + /* Tax */ PO_TAX_SV.get_tax('PO',pda.po_distribution_id)) * NVL(cedit.currency_rate,1))
1469 ), l_ext_precision
1470 ) pda_value,
1471 pda.wip_operation_seq_num operation_seq_num,
1472 pla.category_id category_id,
1473 nvl(pha.approved_date, pha.last_update_date) category_date
1474 FROM po_distributions_all pda,
1475 po_line_locations_all plla,
1476 po_headers_all pha,
1477 po_lines_all pla,
1478 cst_eam_direct_items_temp cedit
1479 WHERE cedit.work_order_number = p_wip_entity_id
1480 AND cedit.organization_id = l_organization_id
1481 AND cedit.task_number = pda.wip_operation_seq_num
1482 AND cedit.category_id = pla.category_id
1483 AND pha.po_header_id = cedit.po_header_id
1484 AND pla.po_line_id = cedit.po_line_id
1485 AND pda.wip_entity_id = cedit.work_order_number
1486 AND pda.po_header_id = cedit.po_header_id
1487 AND pda.destination_organization_id = cedit.organization_id
1488 AND pda.po_line_id = pla.po_line_id
1489 AND plla.line_location_id = pda.line_location_id
1490 GROUP BY pda.wip_operation_seq_num,
1491 pla.category_id,
1492 pha.approved_date,
1493 pha.last_update_date,
1494 cedit.currency_rate
1495 UNION ALL
1496 SELECT ROUND(SUM(
1497 DECODE(NVL(prla.order_type_lookup_code,'QUANTITY'),
1498 'RATE', NVL(cedit.amount,NVL(prla.amount * cedit.currency_rate,0)),
1499 'FIXED PRICE', NVL(cedit.amount,NVL(prla.amount * cedit.currency_rate,0)),
1500 NVL(prla.unit_price,0) * NVL(prla.quantity,0))
1501 * NVL(cedit.currency_rate,1)), l_ext_precision) pda_value,
1502 prla.wip_operation_seq_num operation_seq_num,
1503 prla.category_id category_id,
1504 prha.last_update_date category_date
1505 FROM po_requisition_lines_all prla,
1506 po_requisition_headers_all prha,
1507 cst_eam_direct_items_temp cedit
1508 WHERE cedit.work_order_number = p_wip_entity_id
1509 AND cedit.organization_id = l_organization_id
1510 AND cedit.task_number = prla.wip_operation_seq_num
1511 AND cedit.category_id = prla.category_id
1512 /*to ensure that we do not double count*/
1513 AND cedit.po_header_id IS NULL
1514 AND prha.requisition_header_id = cedit.requisition_header_id
1515 AND prla.destination_organization_id = cedit.organization_id
1516 AND prla.wip_entity_id = cedit.work_order_number
1517 AND prla.requisition_line_id = cedit.requisition_line_id
1518 GROUP BY prla.wip_operation_seq_num,
1519 prla.category_id,
1520 prha.last_update_date,
1521 cedit.currency_rate;
1522
1523
1524 /* Cursor added for Budgeting and Forecasting Requirements - R12 */
1525 cursor c_acct (p_wip_entity_id NUMBER) is
1526 select material_account,
1527 material_overhead_account,
1528 resource_account,
1529 outside_processing_account,
1530 overhead_account,
1531 class_code wip_acct_class
1532 from wip_discrete_jobs
1533 where wip_entity_id = p_wip_entity_id;
1534
1535 BEGIN
1536
1537 -- Procedure level log message for Entry point
1538 IF (l_pLog) THEN
1539 FND_LOG.STRING(
1540 FND_LOG.LEVEL_PROCEDURE,
1541 l_module || '.begin',
1542 'Compute_PAC_JobEstimates <<');
1543 END IF;
1544
1545 -- standard start of API savepoint
1546 SAVEPOINT Compute_PAC_JobEstimates_PUB;
1547
1548 -- standard call to check for call compatibility
1549 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version,
1550 p_api_version,
1551 l_api_name,
1552 G_PKG_NAME ) THEN
1553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1554 END IF;
1555
1556 -- Initialize message list if p_init_msg_list is set to TRUE
1557 IF FND_API.to_Boolean(p_init_msg_list) THEN
1558 FND_MSG_PUB.initialize;
1559 END IF;
1560
1561 -- initialize api return status to success
1562 x_return_status := FND_API.G_RET_STS_SUCCESS;
1563
1564 -- assign to local variables
1565 l_stmt_num := 200;
1566
1567 -- Check Entity Type is eAM befor continuing
1568 SELECT entity_type,
1569 organization_id
1570 INTO l_entity_type,
1571 l_organization_id
1572 FROM wip_entities we
1573 WHERE we.wip_entity_id = p_wip_entity_id;
1574
1575 l_stmt_num := 205;
1576
1577 IF (l_entity_type <> 6 ) THEN
1578 l_msg_data := 'Invalid WIP entity type: ' || TO_CHAR(l_entity_type)
1579 ||' WIP Entity: ' || TO_CHAR(p_wip_entity_id);
1580 RAISE FND_API.G_EXC_ERROR;
1581 END IF;
1582
1583 SELECT start_quantity,
1584 scheduled_completion_date
1585 INTO l_lot_size,
1586 l_scheduled_completion_date
1587 FROM wip_discrete_jobs wdj
1588 WHERE wdj.wip_entity_id = p_wip_entity_id;
1589
1590 l_stmt_num := 210;
1591
1592 -- Get charge asset using API
1593 BEGIN
1594 CST_EAMCOST_PUB.get_charge_asset (
1595 p_api_version => 1.0,
1596 p_wip_entity_id => p_wip_entity_id,
1597 x_inventory_item_id => l_asset_group_item_id,
1598 x_serial_number => l_asset_number,
1599 x_maintenance_object_id => l_mnt_obj_id,
1600 x_return_status => l_return_status,
1601 x_msg_count => l_msg_count,
1602 x_msg_data => l_msg_data);
1603 EXCEPTION
1604 WHEN OTHERS THEN
1605 l_msg_data := 'CST_EAMCOST_PUB.get_charge_asset() failed';
1606 RAISE FND_API.G_EXC_ERROR;
1607 END;
1608
1609 l_stmt_num := 215;
1610
1611 l_api_message := 'l_asset_group_item_id : '|| TO_CHAR(l_asset_group_item_id)
1612 || ' l_asset_number : '|| l_asset_number;
1613
1614 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1615 l_api_message := 'CST_EAMCOST_PUB.get_charge_asset() returned error ' || l_api_message;
1616 l_msg_data := l_api_message;
1617 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1618 l_api_name,
1619 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
1620 || SUBSTRB (SQLERRM , 1 , 240));
1621 RAISE FND_API.G_EXC_ERROR;
1622 END IF;
1623
1624 -- statement level logging
1625 IF (l_sLog) THEN
1626 FND_LOG.STRING(
1627 FND_LOG.LEVEL_STATEMENT,
1628 l_module || '.' || l_stmt_num,
1629 l_api_message);
1630 END IF;
1631
1632 l_trunc_le_sched_comp_date := INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(
1633 l_scheduled_completion_date,
1634 l_organization_id);
1635 l_stmt_num := 220;
1636
1637 SELECT count(*)
1638 INTO l_dummy
1639 FROM cst_pac_periods cpp
1640 WHERE cpp.pac_period_id = p_period_id
1641 AND LEGAL_ENTITY = p_legal_entity_id
1642 AND COST_TYPE_ID = p_cost_type_id
1643 AND l_trunc_le_sched_comp_date BETWEEN cpp.PERIOD_START_DATE
1644 AND cpp.PERIOD_END_DATE;
1645 l_stmt_num := 225;
1646
1647 IF (NVL(l_dummy,0) = 1) THEN
1648
1649 l_stmt_num := 227;
1650
1651 -- Get period info if completion date is in current open period
1652 SELECT cpp.PAC_PERIOD_ID,
1653 cpp.period_set_name,
1654 cpp.period_name,
1655 cpp.period_start_date
1656 INTO l_acct_period_id,
1657 l_period_set_name,
1658 l_period_name,
1659 l_period_start_date
1660 FROM CST_PAC_periods cpp
1661 WHERE cpp.pac_period_id = p_period_id
1662 AND l_trunc_le_sched_comp_date BETWEEN cpp.period_start_date
1663 AND cpp.period_end_date;
1664
1665 l_stmt_num := 230;
1666
1667 ELSE -- Get period info from Gl_periods, if completion date is in future period
1668
1669 l_stmt_num := 232;
1670
1671 /* The following query will be modified to refer to
1672 cst_organization_definitions as an impact of the HR-PROFILE option. */
1673
1674 SELECT gp.period_set_name, gp.period_name,
1675 gp.start_date
1676 INTO l_period_set_name, l_period_name,
1677 l_period_start_date
1678 FROM gl_periods gp,
1679 gl_sets_of_books gsob,
1680 cst_organization_definitions ood
1681 WHERE ood.organization_id = l_organization_id
1682 AND gsob.set_of_books_id = ood.set_of_books_id
1683 AND gp.period_set_name = gsob.period_set_name
1684 AND gp.adjustment_period_flag = 'N'
1685 AND gp.period_type = gsob.accounted_period_type
1686 AND l_trunc_le_sched_comp_date BETWEEN gp.start_date
1687 AND gp.end_date;
1688 l_stmt_num := 235;
1689
1690 END IF; -- check for l_dummy
1691
1692 -- statement level logging
1693 IF (l_sLog) THEN
1694 FND_LOG.STRING(
1695 FND_LOG.LEVEL_STATEMENT,
1696 l_module || '.' || l_stmt_num,
1697 'Period Details- l_acct_period_id: ' || TO_CHAR(l_acct_period_id)
1698 || ' l_period_set_name: '|| TO_CHAR(l_period_set_name)
1699 || ' l_period_name: ' || TO_CHAR(l_period_name));
1700 END IF;
1701
1702 IF (l_acct_period_id IS NULL
1703 AND (l_period_set_name IS NULL OR l_period_name IS NULL)) THEN
1704
1705 l_msg_data := 'Cannot Find Period for Date: '
1706 ||TO_CHAR(l_trunc_le_sched_comp_date);
1707 RAISE FND_API.G_EXC_ERROR;
1708 END IF;
1709
1710 -- Derive the currency extended precision for the organization
1711 CSTPUTIL.CSTPUGCI(l_organization_id,
1712 l_round_unit,
1713 l_precision,
1714 l_ext_precision);
1715
1716 l_stmt_num := 240;
1717
1718 /* Request submission should be with proper inputs like Legal Entity,
1719 Pac Cost type and Cost group. Now Populate prior Period ID */
1720
1721 SELECT NVL(MAX(cpp.pac_period_id), -1)
1722 INTO l_prior_period_id
1723 FROM cst_pac_periods cpp
1724 WHERE cpp.cost_type_id = p_cost_type_id
1725 AND cpp.legal_entity = p_legal_entity_id
1726 AND cpp.pac_period_id < p_period_id;
1727
1728 l_stmt_num := 245;
1729
1730 -- Derive valuation PAC rates cost type for the PAC cost type
1731
1732 SELECT nvl(max(pac_rates_cost_type_id),-1)
1733 INTO l_pac_rates_id
1734 FROM cst_le_cost_types
1735 WHERE legal_entity = p_legal_entity_id
1736 AND cost_type_id = p_cost_type_id;
1737
1738 l_stmt_num := 250;
1739
1740 IF (l_pac_rates_id = -1) THEN
1741 l_msg_data := 'PAC Rates Type is not defined for Cost Type: '|| TO_CHAR(p_cost_type_id);
1742 RAISE FND_API.G_EXC_ERROR;
1743 END IF;
1744
1745 /* Fetch the WAC account information for this wip job */
1746 open c_acct(p_wip_entity_id);
1747 fetch c_acct into
1748 l_material_account,
1749 l_material_overhead_account,
1750 l_resource_account,
1751 l_osp_account,
1752 l_overhead_account,
1753 l_wip_acct_class;
1754 close c_acct;
1755
1756
1757 --------------------------------------------
1758 -- Open cursor c_wor to get the resources --
1759 --------------------------------------------
1760 FOR c_wor_rec IN c_wor LOOP
1761
1762 l_stmt_num := 255;
1763
1764 -- get the maintenance cost category by callling the API
1765 CST_EAMCOST_PUB.Get_MaintCostCat(
1766 p_txn_mode => 2 ,
1767 p_wip_entity_id => p_wip_entity_id,
1768 p_opseq_num => c_wor_rec.operation_seq_num,
1769 p_resource_id => c_wor_rec.resource_id,
1770 p_res_seq_num => c_wor_rec.resource_seq_num,
1771 x_return_status => l_return_status,
1772 x_operation_dept => l_operation_dept_id,
1773 x_owning_dept => l_owning_dept_id,
1774 x_maint_cost_cat => l_maint_cost_category);
1775
1776 l_stmt_num := 260;
1777 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1778 l_api_message := 'CST_EAMCOST_PUB.Get_MaintCostCat() returned error';
1779 l_msg_data := l_api_message;
1780 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1781 l_api_name,
1782 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
1783 || SUBSTRB (SQLERRM , 1 , 240));
1784 RAISE FND_API.G_EXC_ERROR;
1785 END IF;
1786
1787 -- Get the eAM cost element by calling API
1788 l_eam_cost_element := CST_EAMCOST_PUB.Get_eamCostElement(
1789 p_txn_mode => 2,
1790 p_org_id => l_organization_id,
1791 p_resource_id => c_wor_rec.resource_id);
1792
1793 l_stmt_num := 265;
1794
1795 IF l_eam_cost_element = 0 THEN
1796 l_api_message := 'CST_EAMCOST_PUB.Get_eamCostElement() returned error';
1797 l_msg_data := l_api_message;
1798 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1799 l_api_name,
1800 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
1801 || SUBSTRB (SQLERRM , 1 , 240));
1802 RAISE FND_API.G_EXC_ERROR;
1803 END IF;
1804
1805 /* Insert estimated resource values into WPEPB and CPEAPB */
1806 InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
1807 x_return_status => l_return_status,
1808 x_msg_count => l_msg_count,
1809 x_msg_data => l_msg_data,
1810 p_legal_entity_id => p_legal_entity_id,
1811 p_cost_group_id => p_cost_group_id,
1812 p_cost_type_id => p_cost_type_id,
1813 p_period_id => l_acct_period_id,
1814 p_period_set_name => l_period_set_name,
1815 p_period_name => l_period_name,
1816 p_organization_id => l_organization_id,
1817 p_wip_entity_id => p_wip_entity_id,
1818 p_owning_dept_id => l_owning_dept_id,
1819 p_dept_id => l_operation_dept_id,
1820 p_maint_cost_cat => l_maint_cost_category,
1821 p_opseq_num => c_wor_rec.operation_seq_num,
1822 p_eam_cost_element => l_eam_cost_element,
1823 p_asset_group_id => l_asset_group_item_id,
1824 p_asset_number => l_asset_number,
1825 p_value_type => 2,
1826 p_value => c_wor_rec.resource_value,
1827 p_user_id => p_user_id,
1828 p_request_id => p_request_id,
1829 p_prog_id => p_prog_id,
1830 p_prog_app_id => p_prog_app_id,
1831 p_login_id => p_login_id);
1832
1833 l_stmt_num := 270;
1834
1835 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1836 l_api_message := 'insertupdate_PAC_eamperbal() returned error ';
1837 l_msg_data := l_api_message;
1838 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1839 l_api_name,
1840 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
1841 || SUBSTRB (SQLERRM , 1 , 240));
1842 RAISE FND_API.G_EXC_ERROR;
1843 END IF;
1844
1845 IF c_wor_rec.resource_value <> 0 then
1846
1847 l_stmt_num := 273;
1848
1849 case(c_wor_rec.cost_element_id)
1850 when 3 then
1851 l_acct_id := l_resource_account;
1852 when 4 then
1853 l_acct_id := l_osp_account;
1854 else
1855 l_acct_id := l_resource_account;
1856 end case;
1857
1858 Insert_PAC_eamBalAcct(
1859 p_api_version => 1.0,
1860 p_init_msg_list => FND_API.G_FALSE,
1861 p_commit => FND_API.G_FALSE,
1862 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1863 x_return_status => l_return_status,
1864 x_msg_count => l_msg_count,
1865 x_msg_data => l_msg_data,
1866 p_legal_entity_id => p_legal_entity_id,
1867 p_cost_group_id => p_cost_group_id,
1868 p_cost_type_id => p_cost_type_id,
1869 p_period_id => l_acct_period_id,
1870 p_period_set_name => l_period_set_name,
1871 p_period_name => l_period_name,
1872 p_org_id => l_organization_id,
1873 p_wip_entity_id => p_wip_entity_id,
1874 p_owning_dept_id => l_owning_dept_id,
1875 p_dept_id => l_operation_dept_id,
1876 p_maint_cost_cat => l_maint_cost_category,
1877 p_opseq_num => c_wor_rec.operation_seq_num,
1878 p_period_start_date => l_period_start_date,
1879 p_account_ccid => l_acct_id,
1880 p_value => c_wor_rec.resource_value,
1881 p_txn_type => l_eam_cost_element,
1882 p_wip_acct_class => l_wip_acct_class,
1883 p_mfg_cost_element_id => c_wor_rec.cost_element_id,
1884 p_user_id => p_user_id,
1885 p_request_id => p_request_id,
1886 p_prog_id => p_prog_id,
1887 p_prog_app_id => p_prog_app_id,
1888 p_login_id => p_login_id);
1889
1890 IF l_return_status <> FND_API.g_ret_sts_success THEN
1891
1892 l_api_message := 'Insert_PAC_eamBalAcct error';
1893 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
1894 ||TO_CHAR(l_stmt_num)
1895 ||'): ', l_api_message);
1896 RAISE FND_API.g_exc_error;
1897
1898 END IF;
1899
1900 END IF; -- if c_wor_rec.resource_value !=0
1901
1902
1903 -- Compute Resource Based Overheads Costs (WOR)
1904
1905 -- set sum variable that calculates the total Overhead for the resource to 0
1906 l_sum_rbo := 0;
1907
1908 FOR c_rbo_rec IN c_rbo(c_wor_rec.resource_id,
1909 l_owning_dept_id,
1910 l_organization_id,
1911 c_wor_rec.usage_rate_or_amount,
1912 c_wor_rec.raw_resource_value) LOOP
1913
1914 l_stmt_num := 275;
1915
1916 -- sum the total resource based overheads
1917 l_sum_rbo := l_sum_rbo + NVL(c_rbo_rec.rbo_value,0);
1918
1919 InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
1920 x_return_status => l_return_status,
1921 x_msg_count => l_msg_count,
1922 x_msg_data => l_msg_data,
1923 p_legal_entity_id => p_legal_entity_id,
1924 p_cost_group_id => p_cost_group_id,
1925 p_cost_type_id => p_cost_type_id,
1926 p_period_id => l_acct_period_id,
1927 p_period_set_name => l_period_set_name,
1928 p_period_name => l_period_name,
1929 p_organization_id => l_organization_id,
1930 p_wip_entity_id => p_wip_entity_id,
1931 p_owning_dept_id => l_owning_dept_id,
1932 p_dept_id => l_operation_dept_id,
1933 p_maint_cost_cat => l_maint_cost_category,
1934 p_opseq_num => c_wor_rec.operation_seq_num,
1935 p_eam_cost_element => l_eam_cost_element,
1936 p_asset_group_id => l_asset_group_item_id,
1937 p_asset_number => l_asset_number,
1938 p_value_type => 2,
1939 p_value => c_rbo_rec.rbo_value,
1940 p_user_id => p_user_id,
1941 p_request_id => p_request_id,
1942 p_prog_id => p_prog_id,
1943 p_prog_app_id => p_prog_app_id,
1944 p_login_id => p_login_id);
1945
1946 l_stmt_num := 280;
1947
1948 IF l_return_status <> FND_API.g_ret_sts_success THEN
1949 l_api_message := 'insertupdate_PAC_eamperbal() returned error';
1950 l_msg_data := l_api_message;
1951 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1952 l_api_name,
1953 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
1954 || SUBSTRB (SQLERRM , 1 , 240));
1955 RAISE FND_API.G_EXC_ERROR;
1956 END IF;
1957
1958 END LOOP; /* c_rbo_rec */
1959
1960 /* Insert Resource based overheads only if the value is greater than 0 */
1961 IF ( l_sum_rbo <> 0 ) THEN
1962
1963 l_stmt_num := 283;
1964
1965 Insert_PAC_eamBalAcct(
1966 p_api_version => 1.0,
1967 p_init_msg_list => FND_API.G_FALSE,
1968 p_commit => FND_API.G_FALSE,
1969 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1970 x_return_status => l_return_status,
1971 x_msg_count => l_msg_count,
1972 x_msg_data => l_msg_data,
1973 p_legal_entity_id => p_legal_entity_id,
1974 p_cost_group_id => p_cost_group_id,
1975 p_cost_type_id => p_cost_type_id,
1976 p_period_id => l_acct_period_id,
1977 p_period_set_name => l_period_set_name,
1978 p_period_name => l_period_name,
1979 p_org_id => l_organization_id,
1980 p_wip_entity_id => p_wip_entity_id,
1981 p_owning_dept_id => l_owning_dept_id,
1982 p_dept_id => l_operation_dept_id,
1983 p_maint_cost_cat => l_maint_cost_category,
1984 p_opseq_num => c_wor_rec.operation_seq_num,
1985 p_period_start_date => l_period_start_date,
1986 p_account_ccid => l_overhead_account,
1987 p_value => l_sum_rbo,
1988 p_txn_type => l_eam_cost_element,
1989 p_wip_acct_class => l_wip_acct_class,
1990 p_mfg_cost_element_id => 5, /* Overhead cost Element*/
1991 p_user_id => p_user_id,
1992 p_request_id => p_request_id,
1993 p_prog_id => p_prog_id,
1994 p_prog_app_id => p_prog_app_id,
1995 p_login_id => p_login_id);
1996
1997 IF l_return_status <> FND_API.g_ret_sts_success THEN
1998
1999 l_api_message := 'Insert_PAC_eamBalAcct error';
2000 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
2001 ||TO_CHAR(l_stmt_num)
2002 ||'): ', l_api_message);
2003 RAISE FND_API.g_exc_error;
2004
2005 END IF;
2006
2007 END IF; -- if l_sum_rbo != 0
2008
2009 END LOOP; /* c_wor_rec */
2010
2011 l_stmt_num := 285;
2012
2013 -- statement level logging
2014 IF (l_sLog) THEN
2015 FND_LOG.STRING(
2016 FND_LOG.LEVEL_STATEMENT,
2017 l_module || '.' || l_stmt_num,
2018 'Resource Cost Calc completed successfully');
2019 END IF;
2020
2021 -------------------------------------------------
2022 -- Compute Material Costs (WRO + WRODI + WEDI) --
2023 -------------------------------------------------
2024 FOR c_wro_rec IN c_wro LOOP
2025
2026 -- Get maint cost category for the material - call API
2027 CST_EAMCOST_PUB.Get_MaintCostCat(p_txn_mode => 1 ,
2028 p_wip_entity_id => p_wip_entity_id,
2029 p_opseq_num => c_wro_rec.operation_seq_num,
2030 x_return_status => l_return_status,
2031 x_operation_dept => l_operation_dept_id,
2032 x_owning_dept => l_owning_dept_id,
2033 x_maint_cost_cat => l_maint_cost_category);
2034
2035 l_stmt_num := 290;
2036
2037 IF l_return_status <> FND_API.g_ret_sts_success THEN
2038 l_api_message := 'CST_EAMCOST_PUB.Get_MaintCostCat() returned error';
2039 l_msg_data := l_api_message;
2040 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2041 l_api_name,
2042 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
2043 || SUBSTRB (SQLERRM , 1 , 240));
2044 RAISE FND_API.G_EXC_ERROR;
2045 END IF;
2046
2047 l_stmt_num := 295;
2048
2049 -- Get eam cost element by calling API
2050 l_eam_cost_element := CST_EAMCOST_PUB.Get_eamCostElement(
2051 p_txn_mode => 1,
2052 p_org_id => l_organization_id);
2053
2054 l_stmt_num := 300;
2055
2056 IF l_eam_cost_element = 0 THEN
2057 l_api_message := 'CST_EAMCOST_PUB.Get_eamCostElement() returned error';
2058 l_msg_data := l_api_message;
2059 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2060 l_api_name,
2061 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
2062 || SUBSTRB (SQLERRM , 1 , 240));
2063 RAISE FND_API.G_EXC_ERROR;
2064 END IF;
2065
2066 -- Insert estimated material values into WPEPB and CPEAPB
2067 InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
2068 x_return_status => l_return_status,
2069 x_msg_count => l_msg_count,
2070 x_msg_data => l_msg_data,
2071 p_legal_entity_id => p_legal_entity_id,
2072 p_cost_group_id => p_cost_group_id,
2073 p_cost_type_id => p_cost_type_id,
2074 p_period_id => l_acct_period_id,
2075 p_period_set_name => l_period_set_name,
2076 p_period_name => l_period_name,
2077 p_organization_id => l_organization_id,
2078 p_wip_entity_id => p_wip_entity_id,
2079 p_owning_dept_id => l_owning_dept_id,
2080 p_dept_id => c_wro_rec.department_id,
2081 p_maint_cost_cat => l_maint_cost_category,
2082 p_opseq_num => c_wro_rec.operation_seq_num,
2083 p_eam_cost_element => l_eam_cost_element,
2084 p_asset_group_id => l_asset_group_item_id,
2085 p_asset_number => l_asset_number,
2086 p_value_type => 2,
2087 p_value => c_wro_rec.mat_value,
2088 p_user_id => p_user_id,
2089 p_request_id => p_request_id,
2090 p_prog_id => p_prog_id,
2091 p_prog_app_id => p_prog_app_id,
2092 p_login_id => p_login_id);
2093
2094 l_stmt_num := 305;
2095
2096 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2097 l_api_message := 'insertupdate_PAC_eamperbal() returned error';
2098 l_msg_data := l_api_message;
2099 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2100 l_api_name,
2101 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
2102 || SUBSTRB (SQLERRM , 1 , 240));
2103 RAISE FND_API.G_EXC_ERROR;
2104 END IF;
2105
2106 l_stmt_num := 308;
2107
2108 /* Enter Estimation details for all the manufacturing cost elements where cost is
2109 non-zero - Eam Enhancements Project R12 */
2110
2111 for l_index_var in 1..5 loop
2112
2113 IF (l_sLog) THEN
2114
2115 l_api_message :=' Calling Insert_eamBalAcct... ';
2116 l_api_message :=l_api_message|| ' mfg_cost_element_id = l_mfg_cost_element_id,' ;
2117 l_api_message :=l_api_message|| ' account_id = ' || TO_CHAR(l_account) || ',';
2118 l_api_message :=l_api_message|| ' eam_cost_element_id = '||TO_CHAR(l_eam_cost_element);
2119 FND_LOG.STRING(
2120 FND_LOG.LEVEL_STATEMENT,
2121 l_module || '.' || l_stmt_num,
2122 l_api_message);
2123 END IF;
2124
2125 case (l_index_var)
2126 when 1 then
2127 If c_wro_rec.material_cost <> 0 then
2128 l_mfg_cost_element_id := 1;
2129 l_account := l_material_account;
2130 l_value := c_wro_rec.material_cost;
2131 l_exec_flag := 1;
2132 Else
2133 l_exec_flag := 0;
2134 End If;
2135 when 2 then
2136 If c_wro_rec.material_overhead_cost <> 0 then
2137 l_mfg_cost_element_id := 2;
2138 l_account := l_material_overhead_account;
2139 l_value := c_wro_rec.material_overhead_cost;
2140 l_exec_flag := 1;
2141 Else
2142 l_exec_flag := 0;
2143 End If;
2144 when 3 then
2145 If c_wro_rec.resource_cost <> 0 then
2146 l_mfg_cost_element_id := 3;
2147 l_account := l_resource_account;
2148 l_value := c_wro_rec.resource_cost;
2149 l_exec_flag := 1;
2150 Else
2151 l_exec_flag := 0;
2152 End If;
2153 when 4 then
2154 If c_wro_rec.outside_processing_cost <> 0 then
2155 l_mfg_cost_element_id := 4;
2156 l_account := l_osp_account;
2157 l_value := c_wro_rec.outside_processing_cost;
2158 l_exec_flag := 1;
2159 Else
2160 l_exec_flag := 0;
2161 End If;
2162 when 5 then
2163 If c_wro_rec.overhead_cost <> 0 then
2164 l_mfg_cost_element_id := 5;
2165 l_account := l_overhead_account;
2166 l_value := c_wro_rec.overhead_cost;
2167 l_exec_flag := 1;
2168 Else
2169 l_exec_flag := 0;
2170 End If;
2171 end case;
2172
2173 If (l_exec_flag = 1) then
2174
2175 Insert_PAC_eamBalAcct(
2176 p_api_version => 1.0,
2177 p_init_msg_list => FND_API.G_FALSE,
2178 p_commit => FND_API.G_FALSE,
2179 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2180 x_return_status => l_return_status,
2181 x_msg_count => l_msg_count,
2182 x_msg_data => l_msg_data,
2183 p_legal_entity_id => p_legal_entity_id,
2184 p_cost_group_id => p_cost_group_id,
2185 p_cost_type_id => p_cost_type_id,
2186 p_period_id => l_acct_period_id,
2187 p_period_set_name => l_period_set_name,
2188 p_period_name => l_period_name,
2189 p_org_id => l_organization_id,
2190 p_wip_entity_id => p_wip_entity_id,
2191 p_owning_dept_id => l_owning_dept_id,
2192 p_dept_id => l_operation_dept_id,
2193 p_maint_cost_cat => l_maint_cost_category,
2194 p_opseq_num => c_wro_rec.operation_seq_num,
2195 p_period_start_date => l_period_start_date,
2196 p_account_ccid => l_account,
2197 p_value => l_value,
2198 p_txn_type => l_eam_cost_element,
2199 p_wip_acct_class => l_wip_acct_class,
2200 p_mfg_cost_element_id => l_mfg_cost_element_id,
2201 p_user_id => p_user_id,
2202 p_request_id => p_request_id,
2203 p_prog_id => p_prog_id,
2204 p_prog_app_id => p_prog_app_id,
2205 p_login_id => p_login_id);
2206
2207 IF l_return_status <> FND_API.g_ret_sts_success THEN
2208
2209 l_api_message := 'Insert_PAC_eamBalAcct error';
2210 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
2211 ||TO_CHAR(l_stmt_num)
2212 ||'): ', l_api_message);
2213 RAISE FND_API.g_exc_error;
2214
2215 END IF;
2216 End If;
2217
2218 end Loop; /* End For Loop for l_index_var */
2219
2220
2221 END LOOP; -- end c_wro_rec
2222
2223 l_stmt_num := 310;
2224
2225 -- statement level logging
2226 IF (l_sLog) THEN
2227 FND_LOG.STRING(
2228 FND_LOG.LEVEL_STATEMENT,
2229 l_module || '.' || l_stmt_num,
2230 'WRO Cost Calc completed successfully');
2231 END IF;
2232
2233 ------------------------------------
2234 -- Get non-stockable direct items --
2235 ------------------------------------
2236 FOR c_wrodi_rec IN c_wrodi LOOP
2237
2238 l_stmt_num := 315;
2239
2240 CST_EAMCOST_PUB.Get_MaintCostCat(
2241 p_txn_mode => 1 ,
2242 p_wip_entity_id => p_wip_entity_id,
2243 p_opseq_num => c_wrodi_rec.operation_seq_num,
2244 x_return_status => l_return_status,
2245 x_operation_dept => l_operation_dept_id,
2246 x_owning_dept => l_owning_dept_id,
2247 x_maint_cost_cat => l_maint_cost_category);
2248
2249 l_stmt_num := 320;
2250
2251 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2252 l_api_message := 'CST_EAMCOST_PUB.Get_MaintCostCat() returned error';
2253 l_msg_data := l_api_message;
2254 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2255 l_api_name,
2256 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
2257 || SUBSTRB (SQLERRM , 1 , 240));
2258 RAISE FND_API.g_exc_error;
2259 END IF;
2260
2261 BEGIN
2262 select cceea.mnt_cost_element_id, cceea.mfg_cost_element_id
2263 into l_eam_cost_element, l_mfg_cost_element_id
2264 from cst_cat_ele_exp_assocs cceea
2265 where cceea.category_id = c_wrodi_rec.category_id
2266 and NVL(cceea.end_date, SYSDATE) + 1 > SYSDATE
2267 and cceea.start_date <= sysdate;
2268 exception
2269 when no_data_found then
2270 l_eam_cost_element := 3;
2271 l_mfg_cost_element_id := 1;
2272 end;
2273
2274
2275 l_stmt_num := 325;
2276
2277 IF l_eam_cost_element = 0 THEN
2278 l_api_message := 'Invalid cost element for the direct item';
2279 l_msg_data := l_api_message;
2280 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2281 l_api_name,
2282 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
2283 || SUBSTRB (SQLERRM , 1 , 240));
2284 RAISE FND_API.g_exc_error;
2285 END IF;
2286
2287 l_stmt_num := 330;
2288
2289 /* Insert estimated material values into WPEPB and CPEAPB */
2290 InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
2291 x_return_status => l_return_status,
2292 x_msg_count => l_msg_count,
2293 x_msg_data => l_msg_data,
2294 p_legal_entity_id => p_legal_entity_id,
2295 p_cost_group_id => p_cost_group_id,
2296 p_cost_type_id => p_cost_type_id,
2297 p_period_id => l_acct_period_id,
2298 p_period_set_name => l_period_set_name,
2299 p_period_name => l_period_name,
2300 p_organization_id => l_organization_id,
2301 p_wip_entity_id => p_wip_entity_id,
2302 p_owning_dept_id => l_owning_dept_id,
2303 p_dept_id => c_wrodi_rec.department_id,
2304 p_maint_cost_cat => l_maint_cost_category,
2305 p_opseq_num => c_wrodi_rec.operation_seq_num,
2306 p_eam_cost_element => l_eam_cost_element,
2307 p_asset_group_id => l_asset_group_item_id,
2308 p_asset_number => l_asset_number,
2309 p_value_type => 2,
2310 p_value => c_wrodi_rec.mat_value,
2311 p_user_id => p_user_id,
2312 p_request_id => p_request_id,
2313 p_prog_id => p_prog_id,
2314 p_prog_app_id => p_prog_app_id,
2315 p_login_id => p_login_id);
2316
2317 l_stmt_num := 335;
2318
2319 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2320 l_api_message := 'insertupdate_PAC_eamperbal() returned error';
2321 l_msg_data := l_api_message;
2322 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2323 l_api_name,
2324 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
2325 || SUBSTRB (SQLERRM , 1 , 240));
2326 RAISE FND_API.G_EXC_ERROR;
2327 END IF;
2328
2329 IF c_wrodi_rec.mat_value <> 0 THEN
2330
2331 l_stmt_num := 338;
2332
2333 case(l_mfg_cost_element_id)
2334 when 1 then
2335 l_acct_id := l_material_account;
2336 when 3 then
2337 l_acct_id := l_resource_account;
2338 when 4 then
2339 l_acct_id := l_osp_account;
2340 when 5 then
2341 l_acct_id := l_overhead_account;
2342 else
2343 l_acct_id := l_material_account;
2344 end case;
2345
2346 Insert_PAC_eamBalAcct(
2347 p_api_version => 1.0,
2348 p_init_msg_list => FND_API.G_FALSE,
2349 p_commit => FND_API.G_FALSE,
2350 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2351 x_return_status => l_return_status,
2352 x_msg_count => l_msg_count,
2353 x_msg_data => l_msg_data,
2354 p_legal_entity_id => p_legal_entity_id,
2355 p_cost_group_id => p_cost_group_id,
2356 p_cost_type_id => p_cost_type_id,
2357 p_period_id => l_acct_period_id,
2358 p_period_set_name => l_period_set_name,
2359 p_period_name => l_period_name,
2360 p_org_id => l_organization_id,
2361 p_wip_entity_id => p_wip_entity_id,
2362 p_owning_dept_id => l_owning_dept_id,
2363 p_dept_id => l_operation_dept_id,
2364 p_maint_cost_cat => l_maint_cost_category,
2365 p_opseq_num => c_wrodi_rec.operation_seq_num,
2366 p_period_start_date => l_period_start_date,
2367 p_account_ccid => l_acct_id,
2368 p_value => c_wrodi_rec.mat_value,
2369 p_txn_type => l_eam_cost_element,
2370 p_wip_acct_class => l_wip_acct_class,
2371 p_mfg_cost_element_id => l_mfg_cost_element_id,
2372 p_user_id => p_user_id,
2373 p_request_id => p_request_id,
2374 p_prog_id => p_prog_id,
2375 p_prog_app_id => p_prog_app_id,
2376 p_login_id => p_login_id);
2377
2378 IF l_return_status <> FND_API.g_ret_sts_success THEN
2379
2380 l_api_message := 'Insert_PAC_eamBalAcct error';
2381 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
2382 ||TO_CHAR(l_stmt_num)
2383 ||'): ', l_api_message);
2384 RAISE FND_API.g_exc_error;
2385
2386 END IF;
2387 End If;
2388
2389 END LOOP; /* end c_wrodi_rec */
2390
2391
2392 l_stmt_num := 340;
2393
2394 -- statement level logging
2395 IF (l_sLog) THEN
2396 FND_LOG.STRING(
2397 FND_LOG.LEVEL_STATEMENT,
2398 l_module || '.' || l_stmt_num,
2399 'WRODI Cost Calc completed successfully');
2400 END IF;
2401
2402 --------------------------------------------
2403 -- Get all description based direct items --
2404 --------------------------------------------
2405 FOR c_wedi_rec IN c_wedi LOOP
2406
2407 l_stmt_num := 345;
2408
2409 CST_EAMCOST_PUB.Get_MaintCostCat(p_txn_mode => 1 ,
2410 p_wip_entity_id => p_wip_entity_id,
2411 p_opseq_num => c_wedi_rec.operation_seq_num,
2412 x_return_status => l_return_status,
2413 x_operation_dept => l_operation_dept_id,
2414 x_owning_dept => l_owning_dept_id,
2415 x_maint_cost_cat => l_maint_cost_category);
2416
2417 l_stmt_num := 350;
2418
2419 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2420 l_api_message := 'CST_EAMCOST_PUB.Get_MaintCostCat() returned error';
2421 l_msg_data := l_api_message;
2422 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2423 l_api_name,
2424 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
2425 || SUBSTRB (SQLERRM , 1 , 240));
2426 RAISE FND_API.G_EXC_ERROR;
2427 END IF;
2428
2429 BEGIN
2430 SELECT cceea.mnt_cost_element_id, cceea.mfg_cost_element_id
2431 INTO l_eam_cost_element, l_mfg_cost_element_id
2432 FROM cst_cat_ele_exp_assocs cceea
2433 WHERE cceea.category_id = c_wedi_rec.category_id
2434 AND NVL(cceea.end_date, SYSDATE) + 1 > SYSDATE
2435 and cceea.start_date <= sysdate;
2436 EXCEPTION
2437 WHEN NO_DATA_FOUND THEN
2438 l_eam_cost_element := 3;
2439 l_mfg_cost_element_id := 1;
2440 END;
2441
2442 l_stmt_num := 355;
2443
2444 InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
2445 x_return_status => l_return_status,
2446 x_msg_count => l_msg_count,
2447 x_msg_data => l_msg_data,
2448 p_legal_entity_id => p_legal_entity_id,
2449 p_cost_group_id => p_cost_group_id,
2450 p_cost_type_id => p_cost_type_id,
2451 p_period_id => l_acct_period_id,
2452 p_period_set_name => l_period_set_name,
2453 p_period_name => l_period_name,
2454 p_organization_id => l_organization_id,
2455 p_wip_entity_id => p_wip_entity_id,
2456 p_owning_dept_id => l_owning_dept_id,
2457 p_dept_id => c_wedi_rec.department_id,
2458 p_maint_cost_cat => l_maint_cost_category,
2459 p_opseq_num => c_wedi_rec.operation_seq_num,
2460 p_eam_cost_element => l_eam_cost_element,
2461 p_asset_group_id => l_asset_group_item_id,
2462 p_asset_number => l_asset_number,
2463 p_value_type => 2,
2464 p_value => c_wedi_rec.wedi_value,
2465 p_user_id => p_user_id,
2466 p_request_id => p_request_id,
2467 p_prog_id => p_prog_id,
2468 p_prog_app_id => p_prog_app_id,
2469 p_login_id => p_login_id);
2470
2471 l_stmt_num := 360;
2472
2473 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2474 l_api_message := 'insertupdate_PAC_eamperbal() returned error';
2475 l_msg_data := l_api_message;
2476 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2477 l_api_name,
2478 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
2479 || SUBSTRB (SQLERRM , 1 , 240));
2480 RAISE FND_API.G_EXC_ERROR;
2481 END IF;
2482
2483
2484 If c_wedi_rec.wedi_value <> 0 then
2485
2486 l_stmt_num := 363;
2487
2488 case(l_mfg_cost_element_id)
2489 when 1 then
2490 l_acct_id := l_material_account;
2491 when 3 then
2492 l_acct_id := l_resource_account;
2493 when 4 then
2494 l_acct_id := l_osp_account;
2495 when 5 then
2496 l_acct_id := l_overhead_account;
2497 else
2498 l_acct_id := l_material_account;
2499 end case;
2500
2501 Insert_PAC_eamBalAcct(
2502 p_api_version => 1.0,
2503 p_init_msg_list => FND_API.G_FALSE,
2504 p_commit => FND_API.G_FALSE,
2505 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2506 x_return_status => l_return_status,
2507 x_msg_count => l_msg_count,
2508 x_msg_data => l_msg_data,
2509 p_legal_entity_id => p_legal_entity_id,
2510 p_cost_group_id => p_cost_group_id,
2511 p_cost_type_id => p_cost_type_id,
2512 p_period_id => l_acct_period_id,
2513 p_period_set_name => l_period_set_name,
2514 p_period_name => l_period_name,
2515 p_org_id => l_organization_id,
2516 p_wip_entity_id => p_wip_entity_id,
2517 p_owning_dept_id => l_owning_dept_id,
2518 p_dept_id => l_operation_dept_id,
2519 p_maint_cost_cat => l_maint_cost_category,
2520 p_opseq_num => c_wedi_rec.operation_seq_num,
2521 p_period_start_date => l_period_start_date,
2522 p_account_ccid => l_acct_id,
2523 p_value => c_wedi_rec.wedi_value,
2524 p_txn_type => l_eam_cost_element,
2525 p_wip_acct_class => l_wip_acct_class,
2526 p_mfg_cost_element_id => l_mfg_cost_element_id,
2527 p_user_id => p_user_id,
2528 p_request_id => p_request_id,
2529 p_prog_id => p_prog_id,
2530 p_prog_app_id => p_prog_app_id,
2531 p_login_id => p_login_id);
2532
2533 IF l_return_status <> FND_API.g_ret_sts_success THEN
2534
2535 l_api_message := 'Insert_PAC_eamBalAcct error';
2536 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
2537 ||TO_CHAR(l_stmt_num)
2538 ||'): ', l_api_message);
2539 RAISE FND_API.g_exc_error;
2540
2541 END IF;
2542 End If;
2543 END LOOP; /* end c_wedi_rec */
2544
2545
2546 l_stmt_num := 365;
2547
2548 -- statement level logging
2549 IF (l_sLog) THEN
2550 FND_LOG.STRING(
2551 FND_LOG.LEVEL_STATEMENT,
2552 l_module || '.' || l_stmt_num,
2553 'CST_PacEamCost_GRP.Compute_PAC_JobEstimatess(' || to_char(l_stmt_num)
2554 || '): WEDI Cost Calc completed successfully');
2555 END IF;
2556
2557 ----------------------------------------------
2558 -- Get all info of direct items with REQ/PO --
2559 ----------------------------------------------
2560 FOR c_pda_rec IN c_pda LOOP
2561
2562 l_stmt_num := 370;
2563
2564 SELECT department_id
2565 INTO l_dept_id
2566 FROM wip_operations wo
2567 WHERE wo.wip_entity_id = p_wip_entity_id
2568 AND wo.operation_seq_num = c_pda_rec.operation_seq_num;
2569
2570 l_stmt_num := 375;
2571
2572 CST_EAMCOST_PUB.Get_MaintCostCat(p_txn_mode => 1 ,
2573 p_wip_entity_id => p_wip_entity_id,
2574 p_opseq_num => c_pda_rec.operation_seq_num,
2575 x_return_status => l_return_status,
2576 x_operation_dept => l_operation_dept_id,
2577 x_owning_dept => l_owning_dept_id,
2578 x_maint_cost_cat => l_maint_cost_category);
2579
2580 l_stmt_num := 380;
2581
2582 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2583 l_api_message := 'CST_EAMCOST_PUB.Get_MaintCostCat() returned error';
2584 l_msg_data := l_api_message;
2585 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2586 l_api_name,
2587 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
2588 || SUBSTRB (SQLERRM , 1 , 240));
2589 RAISE FND_API.G_EXC_ERROR;
2590 END IF;
2591
2592 BEGIN
2593 SELECT cceea.mnt_cost_element_id, cceea.mfg_cost_element_id
2594 INTO l_eam_cost_element, l_mfg_cost_element_id
2595 FROM cst_cat_ele_exp_assocs cceea
2596 WHERE cceea.category_id = c_pda_rec.category_id
2597 AND c_pda_rec.category_date >= cceea.start_date
2598 AND c_pda_rec.category_date < (nvl(cceea.end_date, sysdate) + 1);
2599 EXCEPTION
2600 WHEN NO_DATA_FOUND THEN
2601 l_eam_cost_element := 3;
2602 l_mfg_cost_element_id := 1;
2603 END;
2604
2605 l_stmt_num := 385;
2606
2607 /* Insert estimated material values into WPEPB and CPEAPB */
2608
2609 InsertUpdate_PAC_eamPerBal(p_api_version => 1.0,
2610 x_return_status => l_return_status,
2611 x_msg_count => l_msg_count,
2612 x_msg_data => l_msg_data,
2613 p_legal_entity_id => p_legal_entity_id,
2614 p_cost_group_id => p_cost_group_id,
2615 p_cost_type_id => p_cost_type_id,
2616 p_period_id => l_acct_period_id,
2617 p_period_set_name => l_period_set_name,
2618 p_period_name => l_period_name,
2619 p_organization_id => l_organization_id,
2620 p_wip_entity_id => p_wip_entity_id,
2621 p_owning_dept_id => l_owning_dept_id,
2622 p_dept_id => l_dept_id,
2623 p_maint_cost_cat => l_maint_cost_category,
2624 p_opseq_num => c_pda_rec.operation_seq_num,
2625 p_eam_cost_element => l_eam_cost_element,
2626 p_asset_group_id => l_asset_group_item_id,
2627 p_asset_number => l_asset_number,
2628 p_value_type => 2,
2629 p_value => c_pda_rec.pda_value,
2630 p_user_id => p_user_id,
2631 p_request_id => p_request_id,
2632 p_prog_id => p_prog_id,
2633 p_prog_app_id => p_prog_app_id,
2634 p_login_id => p_login_id);
2635
2636 l_stmt_num := 390;
2637
2638 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2639 l_api_message := 'insertupdate_PAC_eamperbal() returned error';
2640 l_msg_data := l_api_message;
2641 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2642 l_api_name,
2643 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
2644 || SUBSTRB (SQLERRM , 1 , 240));
2645 RAISE FND_API.G_EXC_ERROR;
2646 END IF;
2647
2648 If c_pda_rec.pda_value <> 0 then
2649
2650 l_stmt_num := 393;
2651
2652 case(l_mfg_cost_element_id)
2653 when 1 then
2654 l_acct_id := l_material_account;
2655 when 3 then
2656 l_acct_id := l_resource_account;
2657 when 4 then
2658 l_acct_id := l_osp_account;
2659 when 5 then
2660 l_acct_id := l_overhead_account;
2661 else
2662 l_acct_id := l_material_account;
2663 end case;
2664
2665 Insert_PAC_eamBalAcct(
2666 p_api_version => 1.0,
2667 p_init_msg_list => FND_API.G_FALSE,
2668 p_commit => FND_API.G_FALSE,
2669 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2670 x_return_status => l_return_status,
2671 x_msg_count => l_msg_count,
2672 x_msg_data => l_msg_data,
2673 p_legal_entity_id => p_legal_entity_id,
2674 p_cost_group_id => p_cost_group_id,
2675 p_cost_type_id => p_cost_type_id,
2676 p_period_id => l_acct_period_id,
2677 p_period_set_name => l_period_set_name,
2678 p_period_name => l_period_name,
2679 p_org_id => l_organization_id,
2680 p_wip_entity_id => p_wip_entity_id,
2681 p_owning_dept_id => l_owning_dept_id,
2682 p_dept_id => l_operation_dept_id,
2683 p_maint_cost_cat => l_maint_cost_category,
2684 p_opseq_num => c_pda_rec.operation_seq_num,
2685 p_period_start_date => l_period_start_date,
2686 p_account_ccid => l_acct_id,
2687 p_value => c_pda_rec.pda_value,
2688 p_txn_type => l_eam_cost_element,
2689 p_wip_acct_class => l_wip_acct_class,
2690 p_mfg_cost_element_id => l_mfg_cost_element_id,
2691 p_user_id => p_user_id,
2692 p_request_id => p_request_id,
2693 p_prog_id => p_prog_id,
2694 p_prog_app_id => p_prog_app_id,
2695 p_login_id => p_login_id);
2696
2697 IF l_return_status <> FND_API.g_ret_sts_success THEN
2698
2699 l_api_message := 'Insert_PAC_eamBalAcct error';
2700 FND_MSG_PUB.ADD_EXC_MSG('CST_EAMCOST_PUB', 'Insert_PAC_eamBalAcct('
2701 ||TO_CHAR(l_stmt_num)
2702 ||'): ', l_api_message);
2703 RAISE FND_API.g_exc_error;
2704
2705 END IF;
2706 End If;
2707
2708 END LOOP; -- end c_pda_rec
2709
2710 l_stmt_num := 395;
2711
2712 -- statement level logging
2713 IF (l_sLog) THEN
2714 FND_LOG.STRING(
2715 FND_LOG.LEVEL_STATEMENT,
2716 l_module || '.' || l_stmt_num,
2717 'CST_PacEamCost_GRP.Compute_PAC_JobEstimatess(' || to_char(l_stmt_num)
2718 || '): PO/REQ Cost Calc completed successfully');
2719 END IF;
2720
2721 -- Standard check of p_commit
2722 IF FND_API.to_Boolean(p_commit) THEN
2723 COMMIT WORK;
2724 END IF;
2725
2726 -- Standard Call to get message count and if count = 1, get message info
2727 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2728 p_data => x_msg_data );
2729
2730 -- Procedure level log message for exit point
2731 IF (l_pLog) THEN
2732 FND_LOG.STRING(
2733 FND_LOG.LEVEL_PROCEDURE,
2734 l_module || '.end',
2735 'Compute_PAC_JobEstimates >>'
2736 );
2737 END IF;
2738
2739 EXCEPTION
2740
2741 WHEN FND_API.G_EXC_ERROR THEN
2742 ROLLBACK TO Compute_PAC_JobEstimates_PUB;
2743 x_return_status := FND_API.G_RET_STS_ERROR;
2744
2745 -- statement level logging
2746 IF (l_uLog) THEN
2747 FND_LOG.STRING(
2748 FND_LOG.LEVEL_UNEXPECTED,
2749 l_module || '.' || l_stmt_num ,
2750 l_msg_data);
2751 END IF;
2752
2753 -- Get message count and data
2754 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
2755 p_data => x_msg_data);
2756
2757 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2758 ROLLBACK TO Compute_PAC_JobEstimates_PUB;
2759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2760
2761 -- statement level logging
2762 IF (l_uLog) THEN
2763 FND_LOG.STRING(
2764 FND_LOG.LEVEL_UNEXPECTED,
2765 l_module || '.' || l_stmt_num ,
2766 l_msg_data);
2767 END IF;
2768
2769 -- Get message count and data
2770 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
2771 p_data => x_msg_data);
2772
2773 WHEN OTHERS THEN
2774 ROLLBACK TO Compute_PAC_JobEstimates_PUB;
2775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2776
2777 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2778 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2779 l_api_name,
2780 '(' || TO_CHAR(l_stmt_num) || ') : '
2781 || SUBSTRB (SQLERRM , 1 , 240));
2782 END IF;
2783
2784 IF (l_uLog) THEN
2785 FND_LOG.STRING(
2786 FND_LOG.LEVEL_UNEXPECTED,
2787 l_module || '.' || l_stmt_num ,
2788 l_msg_data || SUBSTRB (SQLERRM , 1 , 240));
2789 END IF;
2790
2791 -- Get message count and data
2792 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
2793 p_data => x_msg_data);
2794
2795 END Compute_PAC_JobEstimates;
2796
2797 -- Start of comments
2798 -- API name : InsertUpdate_pac_eamPerBal
2799 -- Type : Public.
2800 -- Function : This API is called from Compute_PAC_JobEstimates and Compute_PAC_JobActuals
2801 -- Flow:
2802 -- |-- Identify column to update value
2803 -- |-- IF p_value_type = 1 THEN ==> actual_cost
2804 -- | |-- IF p_eam_cost_element = 1 THEN --> equipment
2805 -- | | |-- l_column := 'actual_eqp_cost';
2806 -- | | |-- l_col_type := 11;
2807 -- | |-- ELSIF p_eam_cost_element = 2 THEN --> labor
2808 -- | | |-- l_column := 'actual_lab_cost';
2809 -- | | |-- l_col_type := 12;
2810 -- | |-- ELSE --> material
2811 -- | | |-- l_column := 'actual_mat_cost';
2812 -- | | |-- l_col_type := 13;
2813 -- | | END IF;
2814 -- |-- ELSE ==> system estimated
2815 -- | |-- IF p_eam_cost_element = 1 THEN --> equipment
2816 -- | | |-- l_column := 'system_estimated_eqp_cost';
2817 -- | | |-- l_col_type := 21;
2818 -- | |-- ELSIF p_eam_cost_element = 2 THEN --> labor
2819 -- | | |-- l_column := 'system_estimated_lab_cost';
2820 -- | | |-- l_col_type := 22;
2821 -- | |-- ELSE --> material
2822 -- | | |-- l_column := 'system_estimated_mat_cost';
2823 -- | | |-- l_col_type := 23;
2824 -- | | END IF;
2825 -- | END IF;
2826 -- |-- Insert/update CST_PAC_EAM_PERIOD_BALANCES
2827 -- | |-- Check if txn record already existing CST_PAC_EAM_PERIOD_BALANCES
2828 -- | | |-- If yes then UPDATE estimation details
2829 -- | | |-- Else Insert estimation details
2830 -- |-- Insert into asset period balances, call InsertUpdate_pac_assetPerBal
2831 --
2832 -- Pre-reqs : None.
2833 -- Parameters :
2834 -- IN : p_api_version IN NUMBER Required
2835 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
2836 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2837 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2838 -- p_legal_entity_id IN NUMBER
2839 -- p_cost_group_id IN NUMBER
2840 -- p_cost_type_id IN NUMBER
2841 -- p_period_id IN NUMBER Optional Default = null
2842 -- p_period_set_name IN VARCHAR2 Optional Default = null
2843 -- p_period_name IN VARCHAR2 Optional Default = null
2844 -- p_organization_id IN NUMBER Required
2845 -- p_wip_entity_id IN NUMBER Required
2846 -- p_owning_dept_id IN NUMBER Required
2847 -- p_dept_id IN NUMBER Required
2848 -- p_maint_cost_cat IN NUMBER Required
2849 -- p_opseq_num IN NUMBER Required
2850 -- p_eam_cost_element IN NUMBER Required
2851 -- p_asset_group_id IN NUMBER Required
2852 -- p_asset_number IN VARCHAR2 Required
2853 -- p_value_type IN NUMBER Required
2854 -- p_value IN NUMBER Required
2855 -- p_user_id IN NUMBER Required
2856 -- p_request_id IN NUMBER Required
2857 -- p_prog_id IN NUMBER Required
2858 -- p_prog_app_id IN NUMBER Required
2859 -- p_login_id IN NUMBER Required
2860 -- OUT : x_return_status OUT VARCHAR2(1)
2861 -- x_msg_count OUT NUMBER
2862 -- x_msg_data OUT VARCHAR2(2000)
2863 -- Version : Current version 1.0
2864 --
2865 -- Notes : This procedure inserts actuals (p_value_type = 1) or estimated (p_value_type = 2)
2866 -- values into CST_PAC_EAM_PERIOD_BALANCES
2867 --
2868 -- End of comments
2869
2870 PROCEDURE InsertUpdate_PAC_eamPerBal (
2871 p_api_version IN NUMBER,
2872 p_init_msg_list IN VARCHAR2,
2873 p_commit IN VARCHAR2,
2874 p_validation_level IN VARCHAR2,
2875 x_return_status OUT NOCOPY VARCHAR2,
2876 x_msg_count OUT NOCOPY NUMBER,
2877 x_msg_data OUT NOCOPY VARCHAR2,
2878 p_legal_entity_id IN NUMBER,
2879 p_cost_group_id IN NUMBER,
2880 p_cost_type_id IN NUMBER,
2881 p_period_id IN NUMBER := null,
2882 p_period_set_name IN VARCHAR2 := null,
2883 p_period_name IN VARCHAR2 := null,
2884 p_organization_id IN NUMBER,
2885 p_wip_entity_id IN NUMBER,
2886 p_owning_dept_id IN NUMBER,
2887 p_dept_id IN NUMBER,
2888 p_maint_cost_cat IN NUMBER,
2889 p_opseq_num IN NUMBER,
2890 p_eam_cost_element IN NUMBER,
2891 p_asset_group_id IN NUMBER,
2892 p_asset_number IN VARCHAR2,
2893 p_value_type IN NUMBER,
2894 p_value IN NUMBER,
2895 p_user_id IN NUMBER,
2896 p_request_id IN NUMBER,
2897 p_prog_id IN NUMBER,
2898 p_prog_app_id IN NUMBER,
2899 p_login_id IN NUMBER
2900 ) IS
2901 l_api_name CONSTANT VARCHAR2(30) := 'InsertUpdate_PAC_eamPerBal';
2902 l_api_version CONSTANT NUMBER := 1.0;
2903
2904 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2905 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
2906
2907 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2908 l_msg_count NUMBER := 0;
2909 l_msg_data VARCHAR2(8000);
2910 l_stmt_num NUMBER;
2911 l_api_message VARCHAR2(1000);
2912
2913 l_wepb_row_exists NUMBER;
2914 l_ceapb_row_exists NUMBER;
2915 l_count NUMBER;
2916
2917 l_column VARCHAR2(80);
2918 l_col_type NUMBER;
2919 l_statement VARCHAR2(2000);
2920
2921 l_period_id NUMBER;
2922 l_period_set_name VARCHAR2(15);
2923 l_period_name VARCHAR2(15);
2924 l_period_start_date DATE;
2925 l_open_period VARCHAR2(1) := FND_API.G_TRUE;
2926 l_maint_obj_id NUMBER;
2927 l_maint_obj_type NUMBER;
2928
2929 /* Log Severities*/
2930 /* 6- UNEXPECTED */
2931 /* 5- ERROR */
2932 /* 4- EXCEPTION */
2933 /* 3- EVENT */
2934 /* 2- PROCEDURE */
2935 /* 1- STATEMENT */
2936
2937 /* In general, we should use the following:
2938 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2939 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
2940 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
2941 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
2942 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
2943 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2944 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2945 */
2946
2947 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
2948 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2949 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2950
2951 BEGIN
2952
2953 -- Procedure level log message for Entry point
2954 IF (l_pLog) THEN
2955 FND_LOG.STRING(
2956 FND_LOG.LEVEL_PROCEDURE,
2957 l_module || '.begin',
2958 'InsertUpdate_PAC_eamPerBal <<');
2959 END IF;
2960
2961 -- Standard Start of API savepoint
2962 SAVEPOINT InsertUpdate_PAC_eamPerBal_PUB;
2963
2964
2965 -- Standard call to check for call compatibility
2966 IF NOT FND_API.Compatible_API_Call(l_api_version,
2967 p_api_version,
2968 l_api_name,
2969 G_PKG_NAME ) THEN
2970 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2971 END IF;
2972
2973 -- Initialize message list if p_init_msg_list is set to TRUE
2974 IF FND_API.to_Boolean(p_init_msg_list) THEN
2975 FND_MSG_PUB.initialize;
2976 END IF;
2977
2978 -- Initialize API return status to success
2979 x_return_status := FND_API.G_RET_STS_SUCCESS;
2980
2981 l_stmt_num := 400;
2982
2983 -------------------------------------------------------------------------------
2984 -- Get period id if period set name and period name is passed and vice versa --
2985 -------------------------------------------------------------------------------
2986
2987 -- Calling program must pass period id or period set and period name.
2988 IF (p_period_id is null OR p_period_id = 0)
2989 AND (p_period_set_name is null OR p_period_name is null) THEN
2990 l_msg_data := 'Must pass period id, or period set name and period name. '
2991 || 'Job id: ' || TO_CHAR(p_wip_entity_id);
2992 RAISE FND_API.G_EXC_ERROR;
2993 END IF;
2994
2995
2996 BEGIN
2997 l_stmt_num := 405;
2998
2999 SELECT pac_period_id,
3000 period_set_name,
3001 period_name,
3002 period_start_date
3003 INTO l_period_id,
3004 l_period_set_name,
3005 l_period_name,
3006 l_period_start_date
3007 FROM CST_PAC_PERIODS
3008 WHERE cost_type_id = p_cost_type_id
3009 AND legal_entity = p_legal_entity_id
3010 AND (pac_period_id = p_period_id
3011 OR (period_set_name = p_period_set_name
3012 AND period_name = p_period_name));
3013 EXCEPTION
3014 WHEN NO_DATA_FOUND THEN -- no open period
3015 l_open_period := FND_API.G_FALSE;
3016 END;
3017
3018 -- Get data from gl_periods if it is a future period.
3019 IF NOT FND_API.to_boolean(l_open_period) THEN
3020 l_stmt_num := 410;
3021
3022 l_period_set_name := p_period_set_name;
3023 l_period_name := p_period_name;
3024
3025 SELECT 0,
3026 period_set_name,
3027 period_name,
3028 start_date
3029 INTO l_period_id,
3030 l_period_set_name,
3031 l_period_name,
3032 l_period_start_date
3033 FROM gl_periods
3034 WHERE period_set_name = l_period_set_name
3035 AND period_name = l_period_name;
3036 END IF;
3037
3038 ---------------------------------------
3039 -- Identify column to update value. --
3040 ---------------------------------------
3041 IF p_value_type = 1 THEN -- actual_cost
3042 IF p_eam_cost_element = 1 THEN -- equiptment
3043 l_column := 'actual_eqp_cost';
3044 l_col_type := 11;
3045 ELSIF p_eam_cost_element = 2 THEN -- labor
3046 l_column := 'actual_lab_cost';
3047 l_col_type := 12;
3048 ELSE -- material
3049 l_column := 'actual_mat_cost';
3050 l_col_type := 13;
3051 END IF;
3052 ELSE -- system estimated
3053 IF p_eam_cost_element = 1 THEN -- equiptment
3054 l_column := 'system_estimated_eqp_cost';
3055 l_col_type := 21;
3056 ELSIF p_eam_cost_element = 2 THEN -- labor
3057 l_column := 'system_estimated_lab_cost';
3058 l_col_type := 22;
3059 ELSE -- material
3060 l_column := 'system_estimated_mat_cost';
3061 l_col_type := 23;
3062 END IF;
3063 END IF;
3064
3065 -----------------------------------------------
3066 -- Insert/update cst_pac_eam_period_balances --
3067 -----------------------------------------------
3068 SELECT count(*)
3069 INTO l_count
3070 FROM cst_pac_eam_period_balances
3071 WHERE period_set_name = l_period_set_name
3072 AND period_name = l_period_name
3073 AND pac_period_id = l_period_id
3074 AND organization_id = p_organization_id
3075 AND wip_entity_id = p_wip_entity_id
3076 AND maint_cost_category = p_maint_cost_cat
3077 AND owning_dept_id = p_owning_dept_id
3078 AND operations_dept_id = p_dept_id
3079 AND operation_seq_num = p_opseq_num
3080 AND cost_group_id = p_cost_group_id
3081 AND cost_type_id = p_cost_type_id
3082 AND legal_entity_id = p_legal_entity_id;
3083
3084 l_stmt_num := 415;
3085
3086 IF l_count <> 0 THEN /* If records already exist, Update */
3087
3088 l_stmt_num := 420;
3089
3090 -- Building the statement before to improve performance
3091 l_statement := 'UPDATE cst_pac_eam_period_balances SET '
3092 || l_column || '=' || 'nvl('|| l_column || ',0) + nvl(:p_value,0)'
3093 || ', last_update_date = sysdate'
3094 || ', last_updated_by = :p_user_id'
3095 || ', last_update_login = :p_login_id'
3096 || ' WHERE period_set_name = :l_period_set_name'
3097 || ' AND cost_type_id = :p_cost_type_id'
3098 || ' AND cost_group_id = :p_cost_group_id'
3099 || ' AND legal_entity_id = :p_legal_entity_id'
3100 || ' AND period_name = :l_period_name'
3101 || ' AND organization_id = :p_organization_id'
3102 || ' AND wip_entity_id = :p_wip_entity_id'
3103 || ' AND maint_cost_category = :p_maint_cost_cat'
3104 || ' AND owning_dept_id = :p_owning_dept_id'
3105 || ' AND operations_dept_id = :p_dept_id'
3106 || ' AND operation_seq_num = :p_opseq_num';
3107
3108 EXECUTE IMMEDIATE l_statement
3109 USING p_value, p_user_id, p_login_id, l_period_set_name, p_cost_type_id, p_cost_group_id,
3110 p_legal_entity_id, l_period_name, p_organization_id, p_wip_entity_id,p_maint_cost_cat,
3111 p_owning_dept_id, p_dept_id, p_opseq_num ;
3112
3113 l_stmt_num := 425;
3114
3115 -- statement level logging
3116 IF (l_sLog) THEN
3117 FND_LOG.STRING(
3118 FND_LOG.LEVEL_STATEMENT,
3119 l_module || '.' || l_stmt_num,
3120 'Update Successful for Job id: ' || TO_CHAR(p_wip_entity_id));
3121 END IF;
3122
3123 ELSE -- Else, no records found, so Insert
3124
3125 l_stmt_num := 430;
3126
3127 INSERT INTO cst_pac_eam_period_balances (
3128 legal_entity_id,
3129 cost_group_id,
3130 cost_type_id,
3131 period_set_name,
3132 period_name,
3133 pac_period_id,
3134 wip_entity_id,
3135 organization_id,
3136 owning_dept_id,
3137 operations_dept_id,
3138 operation_seq_num,
3139 maint_cost_category,
3140 actual_mat_cost,
3141 actual_lab_cost,
3142 actual_eqp_cost,
3143 system_estimated_mat_cost,
3144 system_estimated_lab_cost,
3145 system_estimated_eqp_cost,
3146 period_start_date,
3147 last_update_date,
3148 last_updated_by,
3149 creation_date,
3150 created_by,
3151 last_update_login,
3152 request_id,
3153 program_application_id,
3154 program_id
3155 )
3156 VALUES (
3157 p_legal_entity_id,
3158 p_cost_group_id,
3159 p_cost_type_id,
3160 l_period_set_name,
3161 l_period_name,
3162 l_period_id,
3163 p_wip_entity_id,
3164 p_organization_id,
3165 p_owning_dept_id,
3166 p_dept_id,
3167 p_opseq_num,
3168 p_maint_cost_cat,
3169 DECODE(l_col_type, 13, NVL(p_value,0),0), -- actual mat
3170 DECODE(l_col_type, 12, NVL(p_value,0),0), -- actual lab
3171 DECODE(l_col_type, 11, NVL(p_value,0),0), -- actual eqp
3172 DECODE(l_col_type, 23, NVL(p_value,0),0), -- sys est
3173 DECODE(l_col_type, 22, NVL(p_value,0),0), -- sys est
3174 DECODE(l_col_type, 21, NVL(p_value,0),0), -- sys est
3175 l_period_start_date,
3176 sysdate,
3177 p_user_id,
3178 sysdate,
3179 p_user_id,
3180 p_login_id,
3181 p_request_id,
3182 p_prog_app_id,
3183 p_prog_id
3184 );
3185
3186 l_stmt_num := 435;
3187
3188 -- statement level logging
3189 IF (l_sLog) THEN
3190 FND_LOG.STRING(
3191 FND_LOG.LEVEL_STATEMENT,
3192 l_module || '.' || l_stmt_num,
3193 'Insert Successful for Job id: ' || TO_CHAR(p_wip_entity_id));
3194 END IF;
3195
3196 END IF; -- end checking job balance row
3197
3198 /* Obtain Maintenance_Object_id and Maintenance_Object_Type from
3199 WIP_DISCRETE_JOBS. eAM enhancements project - R12 */
3200 select maintenance_object_id, maintenance_object_type
3201 into l_maint_obj_id, l_maint_obj_type
3202 from wip_discrete_jobs
3203 where wip_entity_id = p_wip_entity_id
3204 and organization_id = p_organization_id;
3205
3206 /*------------------------------------------------------------
3207 Check for Asset Route is not added in this enhancement.
3208 So directly insert into asset_per_bal table
3209 ------------------------------------------------------------*/
3210 l_stmt_num := 440;
3211
3212 InsertUpdate_pac_assetPerBal(p_legal_entity_id => p_legal_entity_id,
3213 p_cost_group_id => p_cost_group_id,
3214 p_cost_type_id => p_cost_type_id,
3215 p_api_version => 1.0,
3216 x_return_status => l_return_status,
3217 x_msg_count => l_msg_count,
3218 x_msg_data => l_msg_data,
3219 p_period_id => l_period_id,
3220 p_period_set_name => l_period_set_name,
3221 p_period_name => l_period_name,
3222 p_organization_id => p_organization_id,
3223 p_maint_cost_cat => p_maint_cost_cat,
3224 p_asset_group_id => p_asset_group_id,
3225 p_asset_number => p_asset_number,
3226 p_value => p_value,
3227 p_column => l_column,
3228 p_col_type => l_col_type,
3229 p_period_start_date => l_period_start_date,
3230 p_maintenance_object_id => l_maint_obj_id,
3231 p_maintenance_object_type => l_maint_obj_type,
3232 p_user_id => p_user_id,
3233 p_request_id => p_request_id,
3234 p_prog_id => p_prog_id,
3235 p_prog_app_id => p_prog_app_id,
3236 p_login_id => p_login_id);
3237
3238 l_stmt_num := 445;
3239
3240 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3241 l_api_message := 'insertupdate_PAC_assetperbal() returned error';
3242 l_msg_data := l_api_message;
3243 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
3244 l_api_name,
3245 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
3246 || SUBSTRB (SQLERRM , 1 , 240));
3247 RAISE FND_API.G_EXC_ERROR;
3248 END IF;
3249
3250 -- Standard check of p_commit
3251 IF FND_API.to_Boolean(p_commit) THEN
3252 COMMIT WORK;
3253 END IF;
3254
3255 -- Standard Call to get message count and if count = 1, get message info
3256 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3257 p_data => x_msg_data );
3258
3259 -- Procedure level log message for exit point
3260 IF (l_pLog) THEN
3261 FND_LOG.STRING(
3262 FND_LOG.LEVEL_PROCEDURE,
3263 l_module || '.end',
3264 'InsertUpdate_PAC_eamPerBal >>'
3265 );
3266 END IF;
3267
3268 EXCEPTION
3269
3270 WHEN FND_API.g_exc_error THEN
3271 ROLLBACK TO InsertUpdate_PAC_eamPerBal_PUB;
3272 x_return_status := FND_API.G_RET_STS_ERROR;
3273
3274 IF (l_uLog) THEN
3275 FND_LOG.STRING(
3276 FND_LOG.LEVEL_UNEXPECTED,
3277 l_module || '.' || l_stmt_num ,
3278 l_msg_data);
3279 END IF;
3280
3281 -- Get message count and data
3282 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
3283 p_data => x_msg_data);
3284
3285 WHEN FND_API.g_exc_unexpected_error THEN
3286 ROLLBACK TO InsertUpdate_PAC_eamPerBal_PUB;
3287 x_return_status := FND_API.g_ret_sts_unexp_error ;
3288
3289 IF (l_uLog) THEN
3290 FND_LOG.STRING(
3291 FND_LOG.LEVEL_UNEXPECTED,
3292 l_module || '.' || l_stmt_num ,
3293 l_msg_data);
3294 END IF;
3295
3296 -- Get message count and data
3297 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
3298 p_data => x_msg_data);
3299
3300 WHEN OTHERS THEN
3301 ROLLBACK TO InsertUpdate_PAC_eamPerBal_PUB;
3302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3303
3304 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3305 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
3306 l_api_name,
3307 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
3308 || SUBSTRB (SQLERRM , 1 , 240));
3309 END IF;
3310
3311 IF (l_uLog) THEN
3312 FND_LOG.STRING(
3313 FND_LOG.LEVEL_UNEXPECTED,
3314 l_module || '.' || l_stmt_num ,
3315 l_msg_data || SUBSTRB (SQLERRM , 1 , 240));
3316 END IF;
3317
3318 -- Get message count and data
3319 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
3320 p_data => x_msg_data);
3321
3322 END InsertUpdate_PAC_eamPerBal;
3323
3324
3325 -- Start of comments
3326 -- API name : InsertUpdate_pac_assetPerBal
3327 -- Type : Public.
3328 -- Function : This API is called from InsertUpdate_PAC_eamPerBal
3329 -- Flow:
3330 -- Check if records already exist in CST_EAM_PAC_ASSET_PER_BALANCES
3331 -- |-- If yes then Update CST_PAC_EAM_ASSET_PER_BALANCES
3332 -- |-- Else Insert into CST_PAC_EAM_ASSET_PER_BALANCES
3333 -- End if
3334 --
3335 -- Pre-reqs : None.
3336 -- Parameters :
3337 -- IN : p_api_version IN NUMBER Required
3338 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
3339 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
3340 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
3341 -- p_legal_entity_id IN NUMBER,
3342 -- p_cost_group_id IN NUMBER,
3343 -- p_cost_type_id IN NUMBER,
3344 -- p_period_id IN NUMBER Default = null,
3345 -- p_period_set_name IN VARCHAR2 Default = null,
3346 -- p_period_name IN VARCHAR2 Default = null,
3347 -- p_organization_id IN NUMBER,
3348 -- p_maint_cost_cat IN NUMBER,
3349 -- p_asset_group_id IN NUMBER,
3350 -- p_asset_number IN VARCHAR2,
3351 -- p_value IN NUMBER,
3352 -- p_column IN VARCHAR2,
3353 -- p_col_type IN NUMBER,
3354 -- p_period_start_date IN DATE,
3355 -- p_user_id IN NUMBER,
3356 -- p_request_id IN NUMBER,
3357 -- p_prog_id IN NUMBER,
3358 -- p_prog_app_id IN NUMBER,
3359 -- p_login_id IN NUMBER,
3360 -- p_maintenance_object_id IN NUMBER, -- Added for eAM enhancements project R12
3361 -- p_maintenance_object_type IN NUMBER -- Added for eAM enhancements project R12
3362 -- OUT : x_return_status OUT VARCHAR2(1)
3363 -- x_msg_count OUT NUMBER
3364 -- x_msg_data OUT VARCHAR2(2000)
3365 -- Version : Current version 1.0
3366 --
3367 -- Notes : This procedure insets or Updates Actual/Estimate details at the Asset Group/Serial Number level
3368 --
3369 -- End of comments
3370
3371 PROCEDURE InsertUpdate_PAC_assetPerBal (
3372 p_api_version IN NUMBER,
3373 p_init_msg_list IN VARCHAR2,
3374 p_commit IN VARCHAR2,
3375 p_validation_level IN VARCHAR2,
3376 x_return_status OUT NOCOPY VARCHAR2,
3377 x_msg_count OUT NOCOPY NUMBER,
3378 x_msg_data OUT NOCOPY VARCHAR2,
3379 p_legal_entity_id IN NUMBER,
3380 p_cost_group_id IN NUMBER,
3381 p_cost_type_id IN NUMBER,
3382 p_period_id IN NUMBER := null,
3383 p_period_set_name IN VARCHAR2 := null,
3384 p_period_name IN VARCHAR2 := null,
3385 p_organization_id IN NUMBER,
3386 p_maint_cost_cat IN NUMBER,
3387 p_asset_group_id IN NUMBER,
3388 p_asset_number IN VARCHAR2,
3389 p_value IN NUMBER,
3390 p_column IN VARCHAR2,
3391 p_col_type IN NUMBER,
3392 p_period_start_date IN DATE,
3393 p_maintenance_object_id IN NUMBER,
3394 p_maintenance_object_type IN NUMBER,
3395 p_user_id IN NUMBER,
3396 p_request_id IN NUMBER,
3397 p_prog_id IN NUMBER,
3398 p_prog_app_id IN NUMBER,
3399 p_login_id IN NUMBER
3400 ) IS
3401
3402 l_api_name CONSTANT VARCHAR2(30) := 'InsertUpdate_PAC_assetPerBal';
3403 l_api_version CONSTANT NUMBER := 1.0;
3404
3405 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
3406 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
3407
3408 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3409 l_msg_count NUMBER := 0;
3410 l_msg_data VARCHAR2(8000);
3411 l_api_message VARCHAR2(1000);
3412
3413 l_statement VARCHAR2(2000);
3414
3415 l_stmt_num NUMBER := 10;
3416 l_count NUMBER := 0;
3417
3418 /* Log Severities*/
3419 /* 6- UNEXPECTED */
3420 /* 5- ERROR */
3421 /* 4- EXCEPTION */
3422 /* 3- EVENT */
3423 /* 2- PROCEDURE */
3424 /* 1- STATEMENT */
3425
3426 /* In general, we should use the following:
3427 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3428 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
3429 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
3430 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
3431 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
3432 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
3433 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
3434 */
3435
3436 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
3437 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
3438 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
3439
3440 BEGIN
3441
3442 -- Procedure level log message for Entry point
3443 IF (l_pLog) THEN
3444 FND_LOG.STRING(
3445 FND_LOG.LEVEL_PROCEDURE,
3446 l_module || '.begin',
3447 'InsertUpdate_PAC_assetPerBal <<');
3448 END IF;
3449
3450 -- Standard Start of API savepoint
3451 SAVEPOINT InsertUpdate_PAC_astPerBal_PUB;
3452
3453 -- Standard call to check for call compatibility
3454 IF NOT FND_API.Compatible_API_Call (l_api_version,
3455 p_api_version,
3456 l_api_name,
3457 G_PKG_NAME ) THEN
3458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3459 END IF;
3460
3461 -- Initialize message list if p_init_msg_list is set to TRUE
3462 IF FND_API.to_Boolean(p_init_msg_list) THEN
3463 FND_MSG_PUB.initialize;
3464 END IF;
3465
3466 -- Initialize API return status to success
3467 x_return_status := FND_API.G_RET_STS_SUCCESS;
3468
3469 -- Check if records already exist for the asset
3470 SELECT count(*)
3471 INTO l_count
3472 FROM cst_pac_eam_asset_per_balances
3473 WHERE period_set_name = p_period_set_name
3474 AND period_name = p_period_name
3475 AND organization_id = p_organization_id
3476 AND inventory_item_id = p_asset_group_id
3477 AND serial_number = p_asset_number
3478 AND maint_cost_category = p_maint_cost_cat
3479 AND cost_group_id = p_cost_group_id
3480 AND cost_type_id = p_cost_type_id
3481 AND legal_entity_id = p_legal_entity_id;
3482
3483 l_stmt_num := 500;
3484
3485 IF l_count > 0 THEN -- If records already exist then Update
3486
3487 l_stmt_num := 505;
3488
3489 l_statement := 'UPDATE cst_pac_eam_asset_per_balances SET '
3490 || p_column || '='
3491 || 'nvl('|| p_column || ',0) + nvl(:p_value,0)'
3492 || ', last_update_date = sysdate'
3493 || ', last_updated_by = :p_user_id'
3494 || ' WHERE period_set_name = :p_period_set_name'
3495 || ' AND period_name = :p_period_name'
3496 || ' AND organization_id = :p_organization_id'
3497 || ' AND inventory_item_id = :p_asset_group_id'
3498 || ' AND serial_number = :p_asset_number'
3499 || ' AND maint_cost_category = :p_maint_cost_cat'
3500 || ' AND cost_group_id = :p_cost_group_id'
3501 || ' AND cost_type_id = :p_cost_type_id'
3502 || ' AND legal_entity_id = :p_legal_entity_id';
3503
3504 EXECUTE IMMEDIATE l_statement
3505 USING p_value, p_user_id, p_period_set_name, p_period_name, p_organization_id, p_asset_group_id,
3506 p_asset_number, p_maint_cost_cat, p_cost_group_id, p_cost_type_id, p_legal_entity_id;
3507
3508 l_stmt_num := 510;
3509
3510 -- statement level logging
3511 IF (l_sLog) THEN
3512 FND_LOG.STRING(
3513 FND_LOG.LEVEL_STATEMENT,
3514 l_module || '.' || l_stmt_num,
3515 'Update Successful for Serial Number ' || TO_CHAR(p_asset_number));
3516 END IF;
3517
3518 ELSE -- If no records exist, then Insert
3519
3520 l_stmt_num := 515;
3521
3522 INSERT INTO cst_pac_eam_asset_per_balances (
3523 legal_entity_id,
3524 cost_group_id,
3525 cost_type_id,
3526 period_set_name,
3527 period_name,
3528 pac_period_id,
3529 organization_id,
3530 inventory_item_id,
3531 serial_number,
3532 maint_cost_category,
3533 actual_mat_cost,
3534 actual_lab_cost,
3535 actual_eqp_cost,
3536 system_estimated_mat_cost,
3537 system_estimated_lab_cost,
3538 system_estimated_eqp_cost,
3539 period_start_date,
3540 maintenance_object_id,
3541 maintenance_object_type,
3542 last_update_date,
3543 last_updated_by,
3544 creation_date,
3545 created_by,
3546 request_id,
3547 program_application_id
3548 )
3549 VALUES (
3550 p_legal_entity_id,
3551 p_cost_group_id,
3552 p_cost_type_id,
3553 p_period_set_name,
3554 p_period_name,
3555 p_period_id,
3556 p_organization_id,
3557 p_asset_group_id,
3558 p_asset_number,
3559 p_maint_cost_cat,
3560 DECODE(p_col_type, 13, NVL(p_value,0),0), -- actual mat
3561 DECODE(p_col_type, 12, NVL(p_value,0),0), -- actual lab
3562 DECODE(p_col_type, 11, NVL(p_value,0),0), -- actual eqp
3563 DECODE(p_col_type, 23, NVL(p_value,0),0), -- sys est
3564 DECODE(p_col_type, 22, NVL(p_value,0),0), -- sys est
3565 DECODE(p_col_type, 21, NVL(p_value,0),0), -- sys est
3566 p_period_start_date,
3567 p_maintenance_object_id,
3568 p_maintenance_object_type,
3569 sysdate,
3570 p_user_id,
3571 sysdate,
3572 p_user_id,
3573 p_request_id,
3574 p_prog_app_id
3575 );
3576
3577 l_stmt_num := 520;
3578
3579 -- statement level logging
3580 IF (l_sLog) THEN
3581 FND_LOG.STRING(
3582 FND_LOG.LEVEL_STATEMENT,
3583 l_module || '.' || l_stmt_num,
3584 'Insert Successful for Serial Number ' || TO_CHAR(p_asset_number));
3585 END IF;
3586
3587 END IF; -- end checking asset balance rowcount
3588
3589 l_stmt_num := 525;
3590
3591 -- Standard check of p_commit
3592 IF FND_API.to_Boolean(p_commit) THEN
3593 COMMIT WORK;
3594 END IF;
3595
3596 -- Standard Call to get message count and if count = 1, get message info
3597 FND_MSG_PUB.COUNT_AND_GET (p_count => x_msg_count,
3598 p_data => x_msg_data );
3599
3600 -- Procedure level log message for exit point
3601 IF (l_pLog) THEN
3602 FND_LOG.STRING(
3603 FND_LOG.LEVEL_PROCEDURE,
3604 l_module || '.end',
3605 'InsertUpdate_PAC_assetPerBal >>'
3606 );
3607 END IF;
3608
3609 EXCEPTION
3610
3611 WHEN FND_API.g_exc_error THEN
3612 ROLLBACK TO InsertUpdate_PAC_astPerBal_PUB;
3613 x_return_status := FND_API.G_RET_STS_ERROR;
3614
3615 IF (l_uLog) THEN
3616 FND_LOG.STRING(
3617 FND_LOG.LEVEL_UNEXPECTED,
3618 l_module || '.' || l_stmt_num ,
3619 l_msg_data);
3620 END IF;
3621
3622 -- Get message count and data
3623 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
3624 p_data => x_msg_data);
3625
3626 WHEN FND_API.g_exc_unexpected_error THEN
3627 ROLLBACK TO InsertUpdate_PAC_astPerBal_PUB;
3628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3629
3630 IF (l_uLog) THEN
3631 FND_LOG.STRING(
3632 FND_LOG.LEVEL_UNEXPECTED,
3633 l_module || '.' || l_stmt_num ,
3634 l_msg_data);
3635 END IF;
3636
3637 -- Get message count and data
3638 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
3639 p_data => x_msg_data);
3640
3641 WHEN OTHERS THEN
3642 ROLLBACK TO InsertUpdate_PAC_astPerBal_PUB;
3643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3644
3645 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3646 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
3647 l_api_name,
3648 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
3649 || SUBSTRB (SQLERRM , 1 , 240));
3650
3651 END IF;
3652
3653 IF (l_uLog) THEN
3654 FND_LOG.STRING(
3655 FND_LOG.LEVEL_UNEXPECTED,
3656 l_module || '.' || l_stmt_num ,
3657 l_msg_data || SUBSTRB (SQLERRM , 1 , 240));
3658 END IF;
3659
3660 -- Get message count and data
3661 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
3662 p_data => x_msg_data);
3663
3664 END InsertUpdate_PAC_assetPerBal;
3665
3666
3667
3668 -- Start of comments
3669 -- API name : Compute_PAC_JobActuals
3670 -- Type : Public.
3671 -- Function : This API is called from CSTPPWRO.process_wip_resovhd_txns and
3672 -- CSTPPWMT.charge_wip_material
3673 -- Flow:
3674 -- |-- Get Period set name and Period name from Period ID passed
3675 -- |-- Get asset group, asset number and maint obj for the wip_entity_id
3676 -- |-- Derive the currency extended precision for the organization
3677 -- |-- Get maint cost category
3678 -- |-- Get eAM cost element
3679 -- | |-- If Direct Items use get_CostEle_for_DirectItem
3680 -- | |-- Else use Get_eamCostElement
3681 -- |-- End If
3682 -- |-- Call API InsertUpdate_PAC_eamPerBal to update eAM PAC tables.
3683 --
3684 -- Pre-reqs : None.
3685 -- Parameters :
3686 -- IN : p_api_version IN NUMBER Required
3687 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
3688 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
3689 -- p_validation_level IN NUMBER Optional Default =
3690 -- FND_API.G_VALID_LEVEL_FULL
3691 -- p_legal_entity_id IN NUMBER,
3692 -- p_cost_group_id IN NUMBER,
3693 -- p_cost_type_id IN NUMBER,
3694 -- p_period_id IN NUMBER Default = null,
3695 -- p_organization_id IN NUMBER,
3696 -- p_txn_mode IN NUMBER,
3697 -- p_txn_id IN NUMBER,
3698 -- p_value IN NUMBER,
3699 -- p_entity_id IN NUMBER,
3700 -- p_op_seq IN NUMBER,
3701 -- p_resource_id IN NUMBER,
3702 -- p_resource_seq_num IN NUMBER,
3703 -- p_user_id IN NUMBER,
3704 -- p_request_id IN NUMBER,
3705 -- p_prog_id IN NUMBER,
3706 -- p_prog_app_id IN NUMBER,
3707 -- p_login_id IN NUMBER
3708 -- OUT : x_return_status OUT VARCHAR2(1)
3709 -- x_msg_count OUT NUMBER
3710 -- x_msg_data OUT VARCHAR2(2000)
3711 -- Version : Current version 1.0
3712 --
3713 -- Notes : This procedure gets asset, cost element and category associations
3714 -- for the actual txns and then calls API's to update PAC_EAM tables
3715 --
3716 -- End of comments
3717
3718 PROCEDURE Compute_PAC_JobActuals(
3719 p_api_version IN NUMBER,
3720 p_init_msg_list IN VARCHAR2,
3721 p_commit IN VARCHAR2,
3722 p_validation_level IN NUMBER,
3723 x_return_status OUT NOCOPY VARCHAR2,
3724 x_msg_count OUT NOCOPY NUMBER,
3725 x_msg_data OUT NOCOPY VARCHAR2,
3726 p_legal_entity_id IN NUMBER,
3727 p_cost_group_id IN NUMBER,
3728 p_cost_type_id IN NUMBER,
3729 p_pac_period_id IN NUMBER,
3730 p_pac_ct_id IN NUMBER,
3731 p_organization_id IN NUMBER,
3732 p_txn_mode IN NUMBER, -- To indicate Resource/Direct Item Txn
3733 p_txn_id IN NUMBER,
3734 p_value IN NUMBER,
3735 p_wip_entity_id IN NUMBER,
3736 p_op_seq IN NUMBER,
3737 p_resource_id IN NUMBER,
3738 p_resource_seq_num IN NUMBER,
3739 p_user_id IN NUMBER,
3740 p_request_id IN NUMBER,
3741 p_prog_app_id IN NUMBER,
3742 p_prog_id IN NUMBER,
3743 p_login_id IN NUMBER
3744 ) IS
3745
3746 l_api_name CONSTANT VARCHAR2(30) := 'Compute_PAC_JobActuals';
3747 l_api_version CONSTANT NUMBER := 1.0;
3748
3749 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
3750 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
3751
3752 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3753 l_msg_count NUMBER := 0;
3754 l_msg_data VARCHAR2(8000);
3755 l_api_message VARCHAR2(1000);
3756
3757 l_stmt_num NUMBER := 10;
3758
3759 l_pac_period_id NUMBER := 0;
3760 l_period_set_name VARCHAR2(1000) := null;
3761 l_period_name VARCHAR2(1000) := null;
3762 l_owning_dept_id NUMBER := 0;
3763 l_operation_dept_id NUMBER := 0;
3764 l_maint_cost_category NUMBER := 0;
3765 l_mnt_obj_id NUMBER := 0;
3766 l_eam_cost_element NUMBER := 0;
3767 l_asset_group_item_id NUMBER := 0;
3768 l_asset_number VARCHAR2(30);
3769 l_round_unit NUMBER := 0;
3770 l_precision NUMBER := 0;
3771 l_ext_precision NUMBER := 0;
3772
3773 /* Log Severities*/
3774 /* 6- UNEXPECTED */
3775 /* 5- ERROR */
3776 /* 4- EXCEPTION */
3777 /* 3- EVENT */
3778 /* 2- PROCEDURE */
3779 /* 1- STATEMENT */
3780
3781 /* In general, we should use the following:
3782 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3783 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
3784 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
3785 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
3786 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
3787 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
3788 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
3789 */
3790
3791 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
3792 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
3793 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
3794
3795 BEGIN
3796
3797 -- Procedure level log message for Entry point
3798 IF (l_pLog) THEN
3799 FND_LOG.STRING(
3800 FND_LOG.LEVEL_PROCEDURE,
3801 l_module || '.begin',
3802 'Compute_PAC_JobActuals <<');
3803 END IF;
3804
3805 -- Standard Start of API savepoint
3806 SAVEPOINT Compute_PAC_JobActuals_PUB;
3807
3808 -- Standard call to check for call compatibility
3809 IF NOT FND_API.Compatible_API_Call (l_api_version,
3810 p_api_version,
3811 l_api_name,
3812 G_PKG_NAME ) THEN
3813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3814 END IF;
3815
3816 -- Initialize message list if p_init_msg_list is set to TRUE
3817 IF FND_API.to_Boolean(p_init_msg_list) THEN
3818 FND_MSG_PUB.initialize;
3819 END IF;
3820
3821 -- Initialize API return status to success
3822 x_return_status := FND_API.G_RET_STS_SUCCESS;
3823 l_stmt_num := 0;
3824
3825 -- Get period details from period id
3826 SELECT cpp.PAC_PERIOD_ID,
3827 cpp.period_set_name,
3828 cpp.period_name
3829 INTO l_pac_period_id,
3830 l_period_set_name,
3831 l_period_name
3832 FROM CST_PAC_periods cpp
3833 WHERE cpp.pac_period_id = p_pac_period_id;
3834
3835 -- statement level logging
3836 IF (l_sLog) THEN
3837 FND_LOG.STRING(
3838 FND_LOG.LEVEL_STATEMENT,
3839 l_module || '.' || l_stmt_num,
3840 'Period details retreived');
3841 END IF;
3842
3843 l_stmt_num := 5;
3844
3845 IF (l_pac_period_id IS NULL
3846 AND (l_period_set_name IS NULL OR l_period_name IS NULL)) THEN
3847
3848 l_api_message := 'Cannot Find Period for the period_id ' || TO_CHAR(p_pac_period_id);
3849 l_msg_data := l_api_message;
3850 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
3851 l_api_name,
3852 '(' || TO_CHAR(l_stmt_num) ||'): ' || l_api_message);
3853 RAISE FND_API.G_EXC_ERROR;
3854 END IF;
3855
3856 -- Get the associated asset item
3857 CST_EAMCOST_PUB.GET_CHARGE_ASSET (
3858 p_api_version => 1.0,
3859 p_wip_entity_id => p_wip_entity_id,
3860 x_inventory_item_id => l_asset_group_item_id,
3861 x_serial_number => l_asset_number,
3862 x_maintenance_object_id => l_mnt_obj_id,
3863 x_return_status => l_return_status,
3864 x_msg_count => l_msg_count,
3865 x_msg_data => l_msg_data);
3866
3867 l_stmt_num := 10;
3868
3869 IF (L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
3870 l_api_message := 'CST_EAMCOST_PUB.GET_CHARGE_ASSET() returned error';
3871 l_msg_data := l_api_message;
3872 FND_MESSAGE.SET_NAME ('BOM', 'CST_API_MESSAGE');
3873 FND_MESSAGE.set_token('TEXT', 'CST_PacEamCost_GRP.Compute_PAC_JobActuals('
3874 || to_char(l_stmt_num) || '): '|| l_api_message);
3875 FND_MSG_PUB.ADD;
3876 RAISE FND_API.G_EXC_ERROR;
3877 END IF;
3878
3879 -- statement level logging
3880 IF (l_sLog) THEN
3881 FND_LOG.STRING(
3882 FND_LOG.LEVEL_STATEMENT,
3883 l_module || '.' || l_stmt_num,
3884 'Got associated Asset Item');
3885 END IF;
3886
3887
3888 -- Derive the currency extended precision for the organization
3889 CSTPUTIL.CSTPUGCI(p_organization_id,
3890 l_round_unit,
3891 l_precision,
3892 l_ext_precision);
3893
3894 l_stmt_num := 15;
3895
3896 IF (p_txn_mode = 17 ) then -- For Direct Item txns
3897
3898 -- statement level logging
3899 IF (l_sLog) THEN
3900 FND_LOG.STRING(
3901 FND_LOG.LEVEL_STATEMENT,
3902 l_module || '.' || l_stmt_num,
3903 'Processing for Direct Item');
3904 END IF;
3905
3906 -- Get the associated maintainence cost category set by the user
3907 CST_EAMCOST_PUB.Get_MaintCostCat(
3908 p_txn_mode => 1,
3909 p_wip_entity_id => p_wip_entity_id,
3910 p_opseq_num => p_op_seq,
3911 p_resource_id => p_resource_id,
3912 p_res_seq_num => p_resource_seq_num,
3913 x_return_status => l_return_status,
3914 x_operation_dept => l_operation_dept_id,
3915 x_owning_dept => l_owning_dept_id,
3916 x_maint_cost_cat => l_maint_cost_category);
3917
3918 l_stmt_num := 20;
3919
3920 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3921 l_api_message := 'CST_EAMCOST_PUB.Get_MaintCostCat returned error';
3922 l_msg_data := l_api_message;
3923 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
3924 l_api_name,
3925 '(' || TO_CHAR(l_stmt_num) ||'): ' || l_api_message);
3926 RAISE FND_API.G_EXC_ERROR;
3927 END IF;
3928
3929
3930 -- Get direct item cost element
3931 CST_EAMCOST_PUB.get_CostEle_for_DirectItem (
3932 p_api_version => 1.0,
3933 p_init_msg_list => p_init_msg_list,
3934 p_commit => p_commit,
3935 p_validation_level => p_validation_level,
3936 x_return_status => l_return_status,
3937 x_msg_count => l_msg_count,
3938 x_msg_data => l_msg_data,
3939 p_txn_id => p_txn_id,
3940 p_mnt_or_mfg => 1,
3941 p_pac_or_perp => 1, -- PAC calling
3942 x_cost_element_id => l_eam_cost_element);
3943
3944 l_stmt_num := 25;
3945
3946 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3947 l_api_message := 'CST_EAMCOST_PUB.get_CostEle_for_DirectItem returned error';
3948 l_msg_data := l_api_message;
3949 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
3950 FND_MESSAGE.set_token('TEXT', 'CST_PacEamCost_GRP.Compute_PAC_JobActuals('
3951 || to_char(l_stmt_num) || '): ' || l_api_message);
3952 FND_MSG_PUB.ADD;
3953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3954 END IF;
3955
3956 -- statement level logging
3957 IF (l_sLog) THEN
3958 FND_LOG.STRING(
3959 FND_LOG.LEVEL_STATEMENT,
3960 l_module || '.' || l_stmt_num,
3961 'Maint cost cat and cost element got for Direct Item');
3962 END IF;
3963
3964 ELSE -- Not a direct Item
3965
3966 -- statement level logging
3967 IF (l_sLog) THEN
3968 FND_LOG.STRING(
3969 FND_LOG.LEVEL_STATEMENT,
3970 l_module || '.' || l_stmt_num,
3971 'Not a Direct Item');
3972 END IF;
3973
3974 -- Get the associated maintainence cost category set by the user
3975 CST_EAMCOST_PUB.Get_MaintCostCat(
3976 p_txn_mode => p_txn_mode ,
3977 p_wip_entity_id => p_wip_entity_id,
3978 p_opseq_num => p_op_seq,
3979 p_resource_id => p_resource_id,
3980 p_res_seq_num => p_resource_seq_num,
3981 x_return_status => l_return_status,
3982 x_operation_dept => l_operation_dept_id,
3983 x_owning_dept => l_owning_dept_id,
3984 x_maint_cost_cat => l_maint_cost_category);
3985
3986 l_stmt_num := 20;
3987
3988 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3989 l_api_message := 'CST_EAMCOST_PUB.Get_MaintCostCat returned error';
3990 l_msg_data := l_api_message;
3991 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
3992 l_api_name,
3993 '(' || TO_CHAR(l_stmt_num) ||'): ' || l_api_message);
3994 RAISE FND_API.G_EXC_ERROR;
3995 END IF;
3996
3997 -- Get eam cost element
3998 l_eam_cost_element := CST_EAMCOST_PUB.Get_eamCostElement(
3999 p_txn_mode => p_txn_mode,
4000 p_org_id => p_organization_id,
4001 p_resource_id => p_resource_id);
4002
4003 l_stmt_num := 25;
4004
4005 IF l_eam_cost_element = 0 THEN
4006 l_api_message := 'Get_eamCostElement returned error';
4007 l_msg_data := l_api_message;
4008 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
4009 l_api_name,
4010 '(' || TO_CHAR(l_stmt_num) ||'): ' || l_api_message);
4011 RAISE FND_API.G_EXC_ERROR;
4012 END IF;
4013
4014 -- statement level logging
4015 IF (l_sLog) THEN
4016 FND_LOG.STRING(
4017 FND_LOG.LEVEL_STATEMENT,
4018 l_module || '.' || l_stmt_num,
4019 'Got Maint cost cat and cost element values');
4020 END IF;
4021
4022 END IF; -- end direct item check
4023
4024 l_stmt_num := 30;
4025
4026 InsertUpdate_PAC_eamPerBal(
4027 p_api_version => 1.0,
4028 x_return_status => l_return_status,
4029 x_msg_count => l_msg_count,
4030 x_msg_data => l_msg_data,
4031 p_legal_entity_id => p_legal_entity_id,
4032 p_cost_group_id => p_cost_group_id,
4033 p_cost_type_id => p_cost_type_id,
4034 p_period_id => l_pac_period_id,
4035 p_period_set_name => l_period_set_name,
4036 p_period_name => l_period_name,
4037 p_organization_id => p_organization_id,
4038 p_wip_entity_id => p_wip_entity_id,
4039 p_owning_dept_id => l_owning_dept_id,
4040 p_dept_id => l_operation_dept_id,
4041 p_maint_cost_cat => l_maint_cost_category,
4042 p_opseq_num => p_op_seq,
4043 p_eam_cost_element => l_eam_cost_element,
4044 p_asset_group_id => l_asset_group_item_id,
4045 p_asset_number => l_asset_number,
4046 p_value_type => 1, --Actuals
4047 p_value => p_value,
4048 p_user_id => p_user_id,
4049 p_request_id => p_request_id,
4050 p_prog_id => p_prog_id,
4051 p_prog_app_id => p_prog_app_id,
4052 p_login_id => p_login_id);
4053
4054 l_stmt_num := 35;
4055
4056 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4057 l_api_message := 'insertupdate_PAC_eamperbal() returned error';
4058 l_msg_data := l_api_message;
4059 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
4060 l_api_name,
4061 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
4062 || SUBSTRB (SQLERRM , 1 , 240));
4063 RAISE FND_API.G_EXC_ERROR;
4064 END IF;
4065
4066 -- statement level logging
4067 IF (l_sLog) THEN
4068 FND_LOG.STRING(
4069 FND_LOG.LEVEL_STATEMENT,
4070 l_module || '.' || l_stmt_num,
4071 'Insert/Update successful for Actuals');
4072 END IF;
4073
4074 IF FND_API.to_Boolean(p_commit) THEN
4075 COMMIT WORK;
4076 END IF;
4077 l_stmt_num := 40;
4078
4079 -- Standard Call to get message count and if count = 1, get message info
4080 FND_MSG_PUB.COUNT_AND_GET (p_count => x_msg_count,
4081 p_data => x_msg_data );
4082
4083 -- Procedure level log message for exit point
4084 IF (l_pLog) THEN
4085 FND_LOG.STRING(
4086 FND_LOG.LEVEL_PROCEDURE,
4087 l_module || '.end',
4088 'Compute_PAC_JobActuals >>'
4089 );
4090 END IF;
4091
4092 EXCEPTION
4093
4094 WHEN FND_API.g_exc_error THEN
4095 ROLLBACK TO Compute_PAC_JobActuals_PUB;
4096 x_return_status := FND_API.G_RET_STS_ERROR;
4097
4098 IF (l_uLog) THEN
4099 FND_LOG.STRING(
4100 FND_LOG.LEVEL_UNEXPECTED,
4101 l_module || '.' || l_stmt_num ,
4102 l_msg_data);
4103 END IF;
4104
4105 -- Get message count and data
4106 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
4107 p_data => x_msg_data);
4108
4109 WHEN FND_API.g_exc_unexpected_error THEN
4110 ROLLBACK TO Compute_PAC_JobActuals_PUB;
4111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4112
4113 IF (l_uLog) THEN
4114 FND_LOG.STRING(
4115 FND_LOG.LEVEL_UNEXPECTED,
4116 l_module || '.' || l_stmt_num ,
4117 l_msg_data);
4118 END IF;
4119
4120 -- Get message count and data
4121 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
4122 p_data => x_msg_data);
4123
4124 WHEN OTHERS THEN
4125 ROLLBACK TO Compute_PAC_JobActuals_PUB;
4126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4127
4128 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4129 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
4130 l_api_name,
4131 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
4132 || SUBSTRB (SQLERRM , 1 , 240));
4133 END IF;
4134
4135 IF (l_uLog) THEN
4136 FND_LOG.STRING(
4137 FND_LOG.LEVEL_UNEXPECTED,
4138 l_module || '.' || l_stmt_num ,
4139 l_msg_data || SUBSTRB (SQLERRM , 1 , 240));
4140 END IF;
4141
4142 -- Get message count and data
4143 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
4144 p_data => x_msg_data);
4145
4146
4147 END Compute_PAC_JobActuals;
4148
4149
4150 -- Start of comments
4151 -- API name : Insert_PAC_eamBalAcct
4152 -- Type : Public.
4153 -- Function : This API is called from CST_PacEamCost_GRP.Estimate_PAC_WipJobs.
4154 -- The procedure inserts/updates data into CST_PAC_EAM_BALANCE_BY_ACCTS
4155 -- table.
4156 -- Flow:
4157 -- |-- Verify if the estimation data already exists for the wip job
4158 -- | and GL Account for the given cost group and cost type.
4159 -- | |--If data already exists add the new acct_value to existing
4160 -- | acct_value
4161 -- | |--Else insert a new row into the table
4162 --
4163 -- Pre-reqs : None.
4164 -- Parameters :
4165 -- IN : p_api_version IN NUMBER
4166 -- p_init_msg_list IN VARCHAR2
4167 -- p_commit IN VARCHAR2
4168 -- p_validation_level IN NUMBER
4169 -- p_legal_entity_id IN NUMBER,
4170 -- p_cost_group_id IN NUMBER,
4171 -- p_cost_type_id IN NUMBER,
4172 -- p_period_id IN NUMBER,
4173 -- p_period_set_name IN VARCHAR2,
4174 -- p_period_name IN VARCHAR2,
4175 -- p_org_id IN NUMBER,
4176 -- p_wip_entity_id IN NUMBER,
4177 -- p_owning_dept_id IN NUMBER,
4178 -- p_dept_id IN NUMBER,
4179 -- p_maint_cost_cat IN NUMBER,
4180 -- p_opseq_num IN NUMBER,
4181 -- p_period_start_date IN DATE,
4182 -- p_account_ccid IN NUMBER,
4183 -- p_value IN NUMBER,
4184 -- p_txn_type IN NUMBER,
4185 -- p_wip_acct_class IN VARCHAR2,
4186 -- p_mfg_cost_element_id IN NUMBER,
4187 -- p_user_id IN NUMBER,
4188 -- p_request_id IN NUMBER,
4189 -- p_prog_id IN NUMBER,
4190 -- p_prog_app_id IN NUMBER,
4191 -- p_login_id IN NUMBER
4192 -- OUT : x_return_status OUT VARCHAR2(1)
4193 -- x_msg_count OUT NUMBER
4194 -- x_msg_data OUT VARCHAR2(2000)
4195 -- Version : Current version 1.0
4196 --
4197 --
4198 -- End of comments
4199 PROCEDURE Insert_PAC_eamBalAcct
4200 (
4201 p_api_version IN NUMBER,
4202 p_init_msg_list IN VARCHAR2,
4203 p_commit IN VARCHAR2,
4204 p_validation_level IN NUMBER,
4205 x_return_status OUT NOCOPY VARCHAR2,
4206 x_msg_count OUT NOCOPY NUMBER,
4207 x_msg_data OUT NOCOPY VARCHAR2,
4208 p_legal_entity_id IN NUMBER,
4209 p_cost_group_id IN NUMBER,
4210 p_cost_type_id IN NUMBER,
4211 p_period_id IN NUMBER,
4212 p_period_set_name IN VARCHAR2,
4213 p_period_name IN VARCHAR2,
4214 p_org_id IN NUMBER,
4215 p_wip_entity_id IN NUMBER,
4216 p_owning_dept_id IN NUMBER,
4217 p_dept_id IN NUMBER,
4218 p_maint_cost_cat IN NUMBER,
4219 p_opseq_num IN NUMBER,
4220 p_period_start_date IN DATE,
4221 p_account_ccid IN NUMBER,
4222 p_value IN NUMBER,
4223 p_txn_type IN NUMBER,
4224 p_wip_acct_class IN VARCHAR2,
4225 p_mfg_cost_element_id IN NUMBER,
4226 p_user_id IN NUMBER,
4227 p_request_id IN NUMBER,
4228 p_prog_id IN NUMBER,
4229 p_prog_app_id IN NUMBER,
4230 p_login_id IN NUMBER
4231 )
4232 IS
4233 l_api_name CONSTANT VARCHAR2(30) := 'Insert_PAC_eamBalAcct';
4234 l_api_version CONSTANT NUMBER := 1.0;
4235
4236 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
4237 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
4238
4239 /* Log Severities*/
4240 /* 6- UNEXPECTED */
4241 /* 5- ERROR */
4242 /* 4- EXCEPTION */
4243 /* 3- EVENT */
4244 /* 2- PROCEDURE */
4245 /* 1- STATEMENT */
4246
4247 /* In general, we should use the following:
4248 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4249 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
4250 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
4251 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
4252 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
4253 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
4254 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
4255 */
4256
4257 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND
4258 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
4259 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
4260 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
4261
4262 l_cnt_cebba NUMBER;
4263 l_stmt_num NUMBER;
4264
4265 BEGIN
4266 -- Standard Start of API savepoint
4267 SAVEPOINT Insert_PAC_eamBalAcct_PUB;
4268
4269 if( l_pLog ) then
4270 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4271 l_module || '.begin',
4272 'Start of ' || l_full_name || '(' ||
4273 'p_user_id=' || p_user_id || ',' ||
4274 'p_login_id=' || p_login_id ||',' ||
4275 'p_prog_app_id=' || p_prog_app_id ||',' ||
4276 'p_prog_id=' || p_prog_id ||',' ||
4277 'p_request_id=' || p_request_id ||',' ||
4278 'p_legal_entity_id=' || p_legal_entity_id ||',' ||
4279 'p_cost_group_id=' || p_cost_group_id ||',' ||
4280 'p_cost_type_id=' || p_cost_type_id ||',' ||
4281 'p_wip_entity_id=' || p_wip_entity_id ||',' ||
4282 'p_org_id=' || p_org_id ||',' ||
4283 'p_wip_acct_class=' || p_wip_acct_class ||',' ||
4284 'p_account_ccid=' || p_account_ccid ||',' ||
4285 'p_maint_cost_cat =' || p_maint_cost_cat ||',' ||
4286 'p_opseq_num=' || p_opseq_num ||',' ||
4287 'p_mfg_cost_element_id=' || p_mfg_cost_element_id ||',' ||
4288 'p_dept_id=' || p_dept_id ||',' ||
4289 'p_value=' || p_value ||',' ||
4290 ')');
4291 end if;
4292
4293 -- Standard call to check for call compatibility.
4294 IF NOT FND_API.Compatible_API_Call ( l_api_version,
4295 p_api_version,
4296 l_api_name ,
4297 'CST_eamCost_PUB')
4298 THEN
4299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4300 END IF;
4301 -- Initialize message list if p_init_msg_list is set to TRUE.
4302 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4303 FND_MSG_PUB.initialize;
4304 END IF;
4305
4306 l_stmt_num := 10;
4307
4308 -- Initialize API return status to success
4309 x_return_status := FND_API.G_RET_STS_SUCCESS;
4310
4311
4312 /* Update the record if already exists else insert a new one */
4313
4314 MERGE INTO CST_PAC_EAM_BALANCE_BY_ACCTS cebba
4315 USING
4316 (
4317 SELECT NULL FROM DUAL
4318 ) temp
4319 ON
4320 (
4321 cebba.legal_entity_id = p_legal_entity_id AND
4322 cebba.cost_group_id = p_cost_group_id AND
4323 cebba.cost_type_id = p_cost_type_id AND
4324 cebba.period_set_name = p_period_set_name AND
4325 cebba.period_name = p_period_name AND
4326 cebba.wip_entity_id = p_wip_entity_id AND
4327 cebba.organization_id = p_org_id AND
4328 cebba.maint_cost_category = p_maint_cost_cat AND
4329 cebba.owning_dept_id = p_owning_dept_id AND
4330 cebba.period_start_date = p_period_start_date AND
4331 cebba.account_id = p_account_ccid AND
4332 cebba.txn_type = p_txn_type AND
4333 cebba.wip_acct_class_code = p_wip_acct_class AND
4334 cebba.mfg_cost_element_id = p_mfg_cost_element_id
4335 )
4336 WHEN MATCHED THEN
4337 UPDATE
4338 SET cebba.acct_value = cebba.acct_value + p_value,
4339 cebba.LAST_UPDATE_DATE = sysdate,
4340 cebba.LAST_UPDATED_BY = p_user_id,
4341 cebba.LAST_UPDATE_LOGIN = p_login_id
4342 WHEN NOT MATCHED THEN
4343 INSERT
4344 (
4345 LEGAL_ENTITY_ID,
4346 COST_GROUP_ID,
4347 COST_TYPE_ID,
4348 PERIOD_SET_NAME,
4349 PERIOD_NAME,
4350 ACCT_PERIOD_ID,
4351 WIP_ENTITY_ID,
4352 ORGANIZATION_ID,
4353 OPERATIONS_DEPT_ID,
4354 OPERATIONS_SEQ_NUM,
4355 MAINT_COST_CATEGORY,
4356 OWNING_DEPT_ID,
4357 PERIOD_START_DATE,
4358 ACCOUNT_ID,
4359 ACCT_VALUE,
4360 TXN_TYPE,
4361 WIP_ACCT_CLASS_CODE,
4362 MFG_COST_ELEMENT_ID,
4363 LAST_UPDATE_DATE,
4364 LAST_UPDATED_BY,
4365 CREATION_DATE,
4366 CREATED_BY,
4367 LAST_UPDATE_LOGIN
4368 )VALUES
4369 (
4370 p_legal_entity_id,
4371 p_cost_group_id,
4372 p_cost_type_id,
4373 p_period_set_name,
4374 p_period_name ,
4375 p_period_id ,
4376 p_wip_entity_id,
4377 p_org_id ,
4378 p_dept_id,
4379 p_opseq_num ,
4380 p_maint_cost_cat,
4381 p_owning_dept_id,
4382 p_period_start_date,
4383 p_account_ccid,
4384 p_value ,
4385 p_txn_type,
4386 p_wip_acct_class,
4387 p_mfg_cost_element_id,
4388 sysdate,
4389 p_user_id ,
4390 sysdate,
4391 p_prog_app_id ,
4392 p_login_id
4393 );
4394
4395 if( l_sLog ) then
4396 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4397 l_module || '.' || to_char(l_stmt_num),
4398 '.updated/inserted the record for :' || to_char(p_wip_entity_id)
4399 );
4400 end if;
4401
4402 -- Standard check of p_commit.
4403 IF FND_API.To_Boolean( p_commit ) THEN
4404 COMMIT WORK;
4405 END IF;
4406
4407
4408 /* Procedure level log message for Exit point */
4409 IF (l_pLog) THEN
4410 FND_LOG.STRING(
4411 FND_LOG.LEVEL_PROCEDURE,
4412 l_module || '.end',
4413 'End of ' || l_full_name
4414 );
4415 END IF;
4416
4417 -- Standard call to get message count and if count is 1, get message info.
4418 FND_MSG_PUB.Count_And_Get
4419 ( p_count => x_msg_count ,
4420 p_data => x_msg_data
4421 );
4422
4423 EXCEPTION
4424
4425 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4426 ROLLBACK TO Insert_PAC_eamBalAcct_PUB;
4427
4428 IF (l_uLog) THEN
4429 FND_LOG.STRING(
4430 FND_LOG.LEVEL_UNEXPECTED,
4431 l_module || '.' || l_stmt_num,
4432 l_full_name ||'('|| l_stmt_num ||') :' || SUBSTRB (SQLERRM , 1 , 240)
4433 );
4434 END IF;
4435
4436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4437 FND_MSG_PUB.Count_And_Get
4438 ( p_count => x_msg_count ,
4439 p_data => x_msg_data
4440 );
4441 WHEN OTHERS THEN
4442 ROLLBACK TO Insert_PAC_eamBalAcct_PUB;
4443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4444
4445 IF (l_uLog) THEN
4446 FND_LOG.STRING(
4447 FND_LOG.LEVEL_UNEXPECTED,
4448 l_module || '.' || l_stmt_num,
4449 l_full_name ||'('|| l_stmt_num ||') :' || SUBSTRB (SQLERRM , 1 , 240)
4450 );
4451 END IF;
4452
4453 IF FND_MSG_PUB.Check_Msg_Level
4454 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4455 THEN
4456 FND_MSG_PUB.Add_Exc_Msg
4457 ( 'CST_eamCost_PUB' ,
4458 l_api_name
4459 );
4460 END IF;
4461 FND_MSG_PUB.Count_And_Get
4462 ( p_count => x_msg_count ,
4463 p_data => x_msg_data
4464 );
4465 END Insert_PAC_eamBalAcct;
4466
4467
4468
4469 -- Start of comments
4470 -- API name : Delete_PAC_eamBalAcct
4471 -- Type : Public.
4472 -- Function : This API is called from CST_PacEamCost_GRP.Estimate_PAC_WipJobs
4473 -- Flow:
4474 -- |-- Delete estimation data from CST_EAM_BALANCE_BY_ACCTS table for the
4475 -- | given legal entity id, cost group, cost type and wip job
4476 --
4477 -- Pre-reqs : None.
4478 -- Parameters :
4479 -- IN : p_api_version IN NUMBER
4480 -- p_init_msg_list IN VARCHAR2
4481 -- p_commit IN VARCHAR2
4482 -- p_validation_level IN NUMBER
4483 --
4484 -- p_legal_entity_id IN NUMBER,
4485 -- p_cost_group_id IN NUMBER,
4486 -- p_cost_type_id IN NUMBER,
4487 -- p_organization_id IN NUMBER,
4488 -- p_wip_entity_id_tab IN CST_PacEamCost_GRP.WIP_ENTITY_TYP,
4489 -- OUT : x_return_status OUT VARCHAR2(1)
4490 -- x_msg_count OUT NUMBER
4491 -- x_msg_data OUT VARCHAR2(2000)
4492 -- Version : Current version 1.0
4493 --
4494 -- End of comments
4495 PROCEDURE Delete_PAC_eamBalAcct
4496 (
4497 p_api_version IN NUMBER,
4498 p_init_msg_list IN VARCHAR2,
4499 p_commit IN VARCHAR2,
4500 p_validation_level IN NUMBER ,
4501 x_return_status OUT NOCOPY VARCHAR2,
4502 x_msg_count OUT NOCOPY VARCHAR2,
4503 x_msg_data OUT NOCOPY VARCHAR2,
4504 p_wip_entity_id_tab IN CST_PacEamCost_GRP.G_WIP_ENTITY_TYP,
4505 p_legal_entity_id IN NUMBER,
4506 p_cost_group_id IN NUMBER,
4507 p_cost_type_id IN NUMBER
4508
4509 )
4510 IS
4511 l_api_name CONSTANT VARCHAR2(30) := 'Delete_PAC_eamBalAcct';
4512 l_api_version CONSTANT NUMBER := 1.0;
4513
4514 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
4515 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
4516
4517 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND
4518 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
4519 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
4520
4521 l_stmt_num NUMBER;
4522 BEGIN
4523 -- Standard Start of API savepoint
4524 SAVEPOINT Delete_PAC_eamBalAcct_PUB;
4525 -- Standard call to check for call compatibility.
4526 IF NOT FND_API.Compatible_API_Call (l_api_version,
4527 p_api_version,
4528 l_api_name ,
4529 'CST_eamCost_PUB')
4530 THEN
4531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4532 END IF;
4533 -- Initialize message list if p_init_msg_list is set to TRUE.
4534 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4535 FND_MSG_PUB.initialize;
4536 END IF;
4537
4538 if( l_pLog ) then
4539 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4540 l_module || '.begin',
4541 'Start of ' || l_full_name);
4542 end if;
4543
4544
4545 -- Initialize API return status to success
4546 x_return_status := FND_API.G_RET_STS_SUCCESS;
4547
4548 l_stmt_num := 10;
4549
4550 /* Delete data from CST_PAC_EAM_BALANCE_BY_ACCTS */
4551 FORALL l_index IN p_wip_entity_id_tab.FIRST..p_wip_entity_id_tab.LAST
4552 Delete from CST_PAC_EAM_BALANCE_BY_ACCTS
4553 where wip_entity_id = p_wip_entity_id_tab(l_index)
4554 -- and organization_id=p_org_id -- sikhanna not required
4555 and legal_entity_id = p_legal_entity_id
4556 and cost_group_id = p_cost_group_id
4557 and cost_type_id = p_cost_type_id;
4558
4559 -- Standard check of p_commit.
4560 IF FND_API.To_Boolean( p_commit ) THEN
4561 COMMIT WORK;
4562 END IF;
4563
4564 /* Procedure level log message for Exit point */
4565 IF (l_pLog) THEN
4566 FND_LOG.STRING(
4567 FND_LOG.LEVEL_PROCEDURE,
4568 l_module || '.end',
4569 'End of ' || l_full_name
4570 );
4571 END IF;
4572
4573 -- Standard call to get message count and if count is 1, get message info.
4574 FND_MSG_PUB.Count_And_Get
4575 ( p_count => x_msg_count ,
4576 p_data => x_msg_data
4577 );
4578
4579 EXCEPTION
4580
4581 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4582 ROLLBACK TO Delete_PAC_eamBalAcct_PUB;
4583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4584
4585 IF (l_uLog) THEN
4586 FND_LOG.STRING(
4587 FND_LOG.LEVEL_UNEXPECTED,
4588 l_module || '.' || l_stmt_num,
4589 l_full_name ||'('|| l_stmt_num ||') :' ||
4590 SUBSTRB (SQLERRM , 1 , 240));
4591 END IF;
4592
4593 FND_MSG_PUB.Count_And_Get
4594 ( p_count => x_msg_count ,
4595 p_data => x_msg_data
4596 );
4597 WHEN OTHERS THEN
4598 ROLLBACK TO Delete_PAC_eamBalAcct_PUB;
4599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4600
4601 IF (l_uLog) THEN
4602 FND_LOG.STRING(
4603 FND_LOG.LEVEL_UNEXPECTED,
4604 l_module || '.' || l_stmt_num,
4605 l_full_name ||'('|| l_stmt_num ||') :' ||
4606 SUBSTRB (SQLERRM , 1 , 240));
4607 END IF;
4608
4609 IF FND_MSG_PUB.Check_Msg_Level
4610 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4611 THEN
4612 FND_MSG_PUB.Add_Exc_Msg
4613 ( 'CST_eamCost_PUB' ,
4614 l_api_name
4615 );
4616 END IF;
4617 FND_MSG_PUB.Count_And_Get
4618 ( p_count => x_msg_count ,
4619 p_data => x_msg_data
4620 );
4621 END Delete_PAC_eamBalAcct;
4622
4623 END CST_PacEamCost_GRP;