[Home] [Help]
PACKAGE BODY: APPS.CST_PACEAMCOST_GRP
Source
1 PACKAGE BODY CST_PacEamCost_GRP AS
2 /* $Header: CSTPPEAB.pls 120.12 2006/08/25 09:44:20 arathee noship $ */
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 (pac_period_id = p_period_id
3010 OR (period_set_name = p_period_set_name
3011 AND period_name = p_period_name));
3012 EXCEPTION
3013 WHEN NO_DATA_FOUND THEN -- no open period
3014 l_open_period := FND_API.G_FALSE;
3015 END;
3016
3017 -- Get data from gl_periods if it is a future period.
3018 IF NOT FND_API.to_boolean(l_open_period) THEN
3019 l_stmt_num := 410;
3020
3021 l_period_set_name := p_period_set_name;
3022 l_period_name := p_period_name;
3023
3024 SELECT 0,
3025 period_set_name,
3026 period_name,
3027 start_date
3028 INTO l_period_id,
3029 l_period_set_name,
3030 l_period_name,
3031 l_period_start_date
3032 FROM gl_periods
3033 WHERE period_set_name = l_period_set_name
3034 AND period_name = l_period_name;
3035 END IF;
3036
3037 ---------------------------------------
3038 -- Identify column to update value. --
3039 ---------------------------------------
3040 IF p_value_type = 1 THEN -- actual_cost
3041 IF p_eam_cost_element = 1 THEN -- equiptment
3042 l_column := 'actual_eqp_cost';
3043 l_col_type := 11;
3044 ELSIF p_eam_cost_element = 2 THEN -- labor
3045 l_column := 'actual_lab_cost';
3046 l_col_type := 12;
3047 ELSE -- material
3048 l_column := 'actual_mat_cost';
3049 l_col_type := 13;
3050 END IF;
3051 ELSE -- system estimated
3052 IF p_eam_cost_element = 1 THEN -- equiptment
3053 l_column := 'system_estimated_eqp_cost';
3054 l_col_type := 21;
3055 ELSIF p_eam_cost_element = 2 THEN -- labor
3056 l_column := 'system_estimated_lab_cost';
3057 l_col_type := 22;
3058 ELSE -- material
3059 l_column := 'system_estimated_mat_cost';
3060 l_col_type := 23;
3061 END IF;
3062 END IF;
3063
3064 -----------------------------------------------
3065 -- Insert/update cst_pac_eam_period_balances --
3066 -----------------------------------------------
3067 SELECT count(*)
3068 INTO l_count
3069 FROM cst_pac_eam_period_balances
3070 WHERE period_set_name = l_period_set_name
3071 AND period_name = l_period_name
3072 AND pac_period_id = l_period_id
3073 AND organization_id = p_organization_id
3074 AND wip_entity_id = p_wip_entity_id
3075 AND maint_cost_category = p_maint_cost_cat
3076 AND owning_dept_id = p_owning_dept_id
3077 AND operations_dept_id = p_dept_id
3078 AND operation_seq_num = p_opseq_num
3079 AND cost_group_id = p_cost_group_id
3080 AND cost_type_id = p_cost_type_id
3081 AND legal_entity_id = p_legal_entity_id;
3082
3083 l_stmt_num := 415;
3084
3085 IF l_count <> 0 THEN /* If records already exist, Update */
3086
3087 l_stmt_num := 420;
3088
3089 -- Building the statement before to improve performance
3090 l_statement := 'UPDATE cst_pac_eam_period_balances SET '
3091 || l_column || '=' || 'nvl('|| l_column || ',0) + nvl(:p_value,0)'
3092 || ', last_update_date = sysdate'
3093 || ', last_updated_by = :p_user_id'
3094 || ', last_update_login = :p_login_id'
3095 || ' WHERE period_set_name = :l_period_set_name'
3096 || ' AND cost_type_id = :p_cost_type_id'
3097 || ' AND cost_group_id = :p_cost_group_id'
3098 || ' AND legal_entity_id = :p_legal_entity_id'
3099 || ' AND period_name = :l_period_name'
3100 || ' AND organization_id = :p_organization_id'
3101 || ' AND wip_entity_id = :p_wip_entity_id'
3102 || ' AND maint_cost_category = :p_maint_cost_cat'
3103 || ' AND owning_dept_id = :p_owning_dept_id'
3104 || ' AND operations_dept_id = :p_dept_id'
3105 || ' AND operation_seq_num = :p_opseq_num';
3106
3107 EXECUTE IMMEDIATE l_statement
3108 USING p_value, p_user_id, p_login_id, l_period_set_name, p_cost_type_id, p_cost_group_id,
3109 p_legal_entity_id, l_period_name, p_organization_id, p_wip_entity_id,p_maint_cost_cat,
3110 p_owning_dept_id, p_dept_id, p_opseq_num ;
3111
3112 l_stmt_num := 425;
3113
3114 -- statement level logging
3115 IF (l_sLog) THEN
3116 FND_LOG.STRING(
3117 FND_LOG.LEVEL_STATEMENT,
3118 l_module || '.' || l_stmt_num,
3119 'Update Successful for Job id: ' || TO_CHAR(p_wip_entity_id));
3120 END IF;
3121
3122 ELSE -- Else, no records found, so Insert
3123
3124 l_stmt_num := 430;
3125
3126 INSERT INTO cst_pac_eam_period_balances (
3127 legal_entity_id,
3128 cost_group_id,
3129 cost_type_id,
3130 period_set_name,
3131 period_name,
3132 pac_period_id,
3133 wip_entity_id,
3134 organization_id,
3135 owning_dept_id,
3136 operations_dept_id,
3137 operation_seq_num,
3138 maint_cost_category,
3139 actual_mat_cost,
3140 actual_lab_cost,
3141 actual_eqp_cost,
3142 system_estimated_mat_cost,
3143 system_estimated_lab_cost,
3144 system_estimated_eqp_cost,
3145 period_start_date,
3146 last_update_date,
3147 last_updated_by,
3148 creation_date,
3149 created_by,
3150 last_update_login,
3151 request_id,
3152 program_application_id,
3153 program_id
3154 )
3155 VALUES (
3156 p_legal_entity_id,
3157 p_cost_group_id,
3158 p_cost_type_id,
3159 l_period_set_name,
3160 l_period_name,
3161 l_period_id,
3162 p_wip_entity_id,
3163 p_organization_id,
3164 p_owning_dept_id,
3165 p_dept_id,
3166 p_opseq_num,
3167 p_maint_cost_cat,
3168 DECODE(l_col_type, 13, NVL(p_value,0),0), -- actual mat
3169 DECODE(l_col_type, 12, NVL(p_value,0),0), -- actual lab
3170 DECODE(l_col_type, 11, NVL(p_value,0),0), -- actual eqp
3171 DECODE(l_col_type, 23, NVL(p_value,0),0), -- sys est
3172 DECODE(l_col_type, 22, NVL(p_value,0),0), -- sys est
3173 DECODE(l_col_type, 21, NVL(p_value,0),0), -- sys est
3174 l_period_start_date,
3175 sysdate,
3176 p_user_id,
3177 sysdate,
3178 p_user_id,
3179 p_login_id,
3180 p_request_id,
3181 p_prog_app_id,
3182 p_prog_id
3183 );
3184
3185 l_stmt_num := 435;
3186
3187 -- statement level logging
3188 IF (l_sLog) THEN
3189 FND_LOG.STRING(
3190 FND_LOG.LEVEL_STATEMENT,
3191 l_module || '.' || l_stmt_num,
3192 'Insert Successful for Job id: ' || TO_CHAR(p_wip_entity_id));
3193 END IF;
3194
3195 END IF; -- end checking job balance row
3196
3197 /* Obtain Maintenance_Object_id and Maintenance_Object_Type from
3198 WIP_DISCRETE_JOBS. eAM enhancements project - R12 */
3199 select maintenance_object_id, maintenance_object_type
3200 into l_maint_obj_id, l_maint_obj_type
3201 from wip_discrete_jobs
3202 where wip_entity_id = p_wip_entity_id
3203 and organization_id = p_organization_id;
3204
3205 /*------------------------------------------------------------
3206 Check for Asset Route is not added in this enhancement.
3207 So directly insert into asset_per_bal table
3208 ------------------------------------------------------------*/
3209 l_stmt_num := 440;
3210
3211 InsertUpdate_pac_assetPerBal(p_legal_entity_id => p_legal_entity_id,
3212 p_cost_group_id => p_cost_group_id,
3213 p_cost_type_id => p_cost_type_id,
3214 p_api_version => 1.0,
3215 x_return_status => l_return_status,
3216 x_msg_count => l_msg_count,
3217 x_msg_data => l_msg_data,
3218 p_period_id => l_period_id,
3219 p_period_set_name => l_period_set_name,
3220 p_period_name => l_period_name,
3221 p_organization_id => p_organization_id,
3222 p_maint_cost_cat => p_maint_cost_cat,
3223 p_asset_group_id => p_asset_group_id,
3224 p_asset_number => p_asset_number,
3225 p_value => p_value,
3226 p_column => l_column,
3227 p_col_type => l_col_type,
3228 p_period_start_date => l_period_start_date,
3229 p_maintenance_object_id => l_maint_obj_id,
3230 p_maintenance_object_type => l_maint_obj_type,
3231 p_user_id => p_user_id,
3232 p_request_id => p_request_id,
3233 p_prog_id => p_prog_id,
3234 p_prog_app_id => p_prog_app_id,
3235 p_login_id => p_login_id);
3236
3237 l_stmt_num := 445;
3238
3239 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3240 l_api_message := 'insertupdate_PAC_assetperbal() returned error';
3241 l_msg_data := l_api_message;
3242 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
3243 l_api_name,
3244 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
3245 || SUBSTRB (SQLERRM , 1 , 240));
3246 RAISE FND_API.G_EXC_ERROR;
3247 END IF;
3248
3249 -- Standard check of p_commit
3250 IF FND_API.to_Boolean(p_commit) THEN
3251 COMMIT WORK;
3252 END IF;
3253
3254 -- Standard Call to get message count and if count = 1, get message info
3255 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3256 p_data => x_msg_data );
3257
3258 -- Procedure level log message for exit point
3259 IF (l_pLog) THEN
3260 FND_LOG.STRING(
3261 FND_LOG.LEVEL_PROCEDURE,
3262 l_module || '.end',
3263 'InsertUpdate_PAC_eamPerBal >>'
3264 );
3265 END IF;
3266
3267 EXCEPTION
3268
3269 WHEN FND_API.g_exc_error THEN
3270 ROLLBACK TO InsertUpdate_PAC_eamPerBal_PUB;
3271 x_return_status := FND_API.G_RET_STS_ERROR;
3272
3273 IF (l_uLog) THEN
3274 FND_LOG.STRING(
3275 FND_LOG.LEVEL_UNEXPECTED,
3276 l_module || '.' || l_stmt_num ,
3277 l_msg_data);
3278 END IF;
3279
3280 -- Get message count and data
3281 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
3282 p_data => x_msg_data);
3283
3284 WHEN FND_API.g_exc_unexpected_error THEN
3285 ROLLBACK TO InsertUpdate_PAC_eamPerBal_PUB;
3286 x_return_status := FND_API.g_ret_sts_unexp_error ;
3287
3288 IF (l_uLog) THEN
3289 FND_LOG.STRING(
3290 FND_LOG.LEVEL_UNEXPECTED,
3291 l_module || '.' || l_stmt_num ,
3292 l_msg_data);
3293 END IF;
3294
3295 -- Get message count and data
3296 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
3297 p_data => x_msg_data);
3298
3299 WHEN OTHERS THEN
3300 ROLLBACK TO InsertUpdate_PAC_eamPerBal_PUB;
3301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3302
3303 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3304 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
3305 l_api_name,
3306 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
3307 || SUBSTRB (SQLERRM , 1 , 240));
3308 END IF;
3309
3310 IF (l_uLog) THEN
3311 FND_LOG.STRING(
3312 FND_LOG.LEVEL_UNEXPECTED,
3313 l_module || '.' || l_stmt_num ,
3314 l_msg_data || SUBSTRB (SQLERRM , 1 , 240));
3315 END IF;
3316
3317 -- Get message count and data
3318 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
3319 p_data => x_msg_data);
3320
3321 END InsertUpdate_PAC_eamPerBal;
3322
3323
3324 -- Start of comments
3325 -- API name : InsertUpdate_pac_assetPerBal
3326 -- Type : Public.
3327 -- Function : This API is called from InsertUpdate_PAC_eamPerBal
3328 -- Flow:
3329 -- Check if records already exist in CST_EAM_PAC_ASSET_PER_BALANCES
3330 -- |-- If yes then Update CST_PAC_EAM_ASSET_PER_BALANCES
3331 -- |-- Else Insert into CST_PAC_EAM_ASSET_PER_BALANCES
3332 -- End if
3333 --
3334 -- Pre-reqs : None.
3335 -- Parameters :
3336 -- IN : p_api_version IN NUMBER Required
3337 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
3338 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
3339 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
3340 -- p_legal_entity_id IN NUMBER,
3341 -- p_cost_group_id IN NUMBER,
3342 -- p_cost_type_id IN NUMBER,
3343 -- p_period_id IN NUMBER Default = null,
3344 -- p_period_set_name IN VARCHAR2 Default = null,
3345 -- p_period_name IN VARCHAR2 Default = null,
3346 -- p_organization_id IN NUMBER,
3347 -- p_maint_cost_cat IN NUMBER,
3348 -- p_asset_group_id IN NUMBER,
3349 -- p_asset_number IN VARCHAR2,
3350 -- p_value IN NUMBER,
3351 -- p_column IN VARCHAR2,
3352 -- p_col_type IN NUMBER,
3353 -- p_period_start_date IN DATE,
3354 -- p_user_id IN NUMBER,
3355 -- p_request_id IN NUMBER,
3356 -- p_prog_id IN NUMBER,
3357 -- p_prog_app_id IN NUMBER,
3358 -- p_login_id IN NUMBER,
3359 -- p_maintenance_object_id IN NUMBER, -- Added for eAM enhancements project R12
3360 -- p_maintenance_object_type IN NUMBER -- Added for eAM enhancements project R12
3361 -- OUT : x_return_status OUT VARCHAR2(1)
3362 -- x_msg_count OUT NUMBER
3363 -- x_msg_data OUT VARCHAR2(2000)
3364 -- Version : Current version 1.0
3365 --
3366 -- Notes : This procedure insets or Updates Actual/Estimate details at the Asset Group/Serial Number level
3367 --
3368 -- End of comments
3369
3370 PROCEDURE InsertUpdate_PAC_assetPerBal (
3371 p_api_version IN NUMBER,
3372 p_init_msg_list IN VARCHAR2,
3373 p_commit IN VARCHAR2,
3374 p_validation_level IN VARCHAR2,
3375 x_return_status OUT NOCOPY VARCHAR2,
3376 x_msg_count OUT NOCOPY NUMBER,
3377 x_msg_data OUT NOCOPY VARCHAR2,
3378 p_legal_entity_id IN NUMBER,
3379 p_cost_group_id IN NUMBER,
3380 p_cost_type_id IN NUMBER,
3381 p_period_id IN NUMBER := null,
3382 p_period_set_name IN VARCHAR2 := null,
3383 p_period_name IN VARCHAR2 := null,
3384 p_organization_id IN NUMBER,
3385 p_maint_cost_cat IN NUMBER,
3386 p_asset_group_id IN NUMBER,
3387 p_asset_number IN VARCHAR2,
3388 p_value IN NUMBER,
3389 p_column IN VARCHAR2,
3390 p_col_type IN NUMBER,
3391 p_period_start_date IN DATE,
3392 p_maintenance_object_id IN NUMBER,
3393 p_maintenance_object_type IN NUMBER,
3394 p_user_id IN NUMBER,
3395 p_request_id IN NUMBER,
3396 p_prog_id IN NUMBER,
3397 p_prog_app_id IN NUMBER,
3398 p_login_id IN NUMBER
3399 ) IS
3400
3401 l_api_name CONSTANT VARCHAR2(30) := 'InsertUpdate_PAC_assetPerBal';
3402 l_api_version CONSTANT NUMBER := 1.0;
3403
3404 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
3405 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
3406
3407 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3408 l_msg_count NUMBER := 0;
3409 l_msg_data VARCHAR2(8000);
3410 l_api_message VARCHAR2(1000);
3411
3412 l_statement VARCHAR2(2000);
3413
3414 l_stmt_num NUMBER := 10;
3415 l_count NUMBER := 0;
3416
3417 /* Log Severities*/
3418 /* 6- UNEXPECTED */
3419 /* 5- ERROR */
3420 /* 4- EXCEPTION */
3421 /* 3- EVENT */
3422 /* 2- PROCEDURE */
3423 /* 1- STATEMENT */
3424
3425 /* In general, we should use the following:
3426 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3427 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
3428 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
3429 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
3430 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
3431 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
3432 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
3433 */
3434
3435 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
3436 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
3437 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
3438
3439 BEGIN
3440
3441 -- Procedure level log message for Entry point
3442 IF (l_pLog) THEN
3443 FND_LOG.STRING(
3444 FND_LOG.LEVEL_PROCEDURE,
3445 l_module || '.begin',
3446 'InsertUpdate_PAC_assetPerBal <<');
3447 END IF;
3448
3449 -- Standard Start of API savepoint
3450 SAVEPOINT InsertUpdate_PAC_astPerBal_PUB;
3451
3452 -- Standard call to check for call compatibility
3453 IF NOT FND_API.Compatible_API_Call (l_api_version,
3454 p_api_version,
3455 l_api_name,
3456 G_PKG_NAME ) THEN
3457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3458 END IF;
3459
3460 -- Initialize message list if p_init_msg_list is set to TRUE
3461 IF FND_API.to_Boolean(p_init_msg_list) THEN
3462 FND_MSG_PUB.initialize;
3463 END IF;
3464
3465 -- Initialize API return status to success
3466 x_return_status := FND_API.G_RET_STS_SUCCESS;
3467
3468 -- Check if records already exist for the asset
3469 SELECT count(*)
3470 INTO l_count
3471 FROM cst_pac_eam_asset_per_balances
3472 WHERE period_set_name = p_period_set_name
3473 AND period_name = p_period_name
3474 AND organization_id = p_organization_id
3475 AND inventory_item_id = p_asset_group_id
3476 AND serial_number = p_asset_number
3477 AND maint_cost_category = p_maint_cost_cat
3478 AND cost_group_id = p_cost_group_id
3479 AND cost_type_id = p_cost_type_id
3480 AND legal_entity_id = p_legal_entity_id;
3481
3482 l_stmt_num := 500;
3483
3484 IF l_count > 0 THEN -- If records already exist then Update
3485
3486 l_stmt_num := 505;
3487
3488 l_statement := 'UPDATE cst_pac_eam_asset_per_balances SET '
3489 || p_column || '='
3490 || 'nvl('|| p_column || ',0) + nvl(:p_value,0)'
3491 || ', last_update_date = sysdate'
3492 || ', last_updated_by = :p_user_id'
3493 || ' WHERE period_set_name = :p_period_set_name'
3494 || ' AND period_name = :p_period_name'
3495 || ' AND organization_id = :p_organization_id'
3496 || ' AND inventory_item_id = :p_asset_group_id'
3497 || ' AND serial_number = :p_asset_number'
3498 || ' AND maint_cost_category = :p_maint_cost_cat'
3499 || ' AND cost_group_id = :p_cost_group_id'
3500 || ' AND cost_type_id = :p_cost_type_id'
3501 || ' AND legal_entity_id = :p_legal_entity_id';
3502
3503 EXECUTE IMMEDIATE l_statement
3504 USING p_value, p_user_id, p_period_set_name, p_period_name, p_organization_id, p_asset_group_id,
3505 p_asset_number, p_maint_cost_cat, p_cost_group_id, p_cost_type_id, p_legal_entity_id;
3506
3507 l_stmt_num := 510;
3508
3509 -- statement level logging
3510 IF (l_sLog) THEN
3511 FND_LOG.STRING(
3512 FND_LOG.LEVEL_STATEMENT,
3513 l_module || '.' || l_stmt_num,
3514 'Update Successful for Serial Number ' || TO_CHAR(p_asset_number));
3515 END IF;
3516
3517 ELSE -- If no records exist, then Insert
3518
3519 l_stmt_num := 515;
3520
3521 INSERT INTO cst_pac_eam_asset_per_balances (
3522 legal_entity_id,
3523 cost_group_id,
3524 cost_type_id,
3525 period_set_name,
3526 period_name,
3527 pac_period_id,
3528 organization_id,
3529 inventory_item_id,
3530 serial_number,
3531 maint_cost_category,
3532 actual_mat_cost,
3533 actual_lab_cost,
3534 actual_eqp_cost,
3535 system_estimated_mat_cost,
3536 system_estimated_lab_cost,
3537 system_estimated_eqp_cost,
3538 period_start_date,
3539 maintenance_object_id,
3540 maintenance_object_type,
3541 last_update_date,
3542 last_updated_by,
3543 creation_date,
3544 created_by,
3545 request_id,
3546 program_application_id
3547 )
3548 VALUES (
3549 p_legal_entity_id,
3550 p_cost_group_id,
3551 p_cost_type_id,
3552 p_period_set_name,
3553 p_period_name,
3554 p_period_id,
3555 p_organization_id,
3556 p_asset_group_id,
3557 p_asset_number,
3558 p_maint_cost_cat,
3559 DECODE(p_col_type, 13, NVL(p_value,0),0), -- actual mat
3560 DECODE(p_col_type, 12, NVL(p_value,0),0), -- actual lab
3561 DECODE(p_col_type, 11, NVL(p_value,0),0), -- actual eqp
3562 DECODE(p_col_type, 23, NVL(p_value,0),0), -- sys est
3563 DECODE(p_col_type, 22, NVL(p_value,0),0), -- sys est
3564 DECODE(p_col_type, 21, NVL(p_value,0),0), -- sys est
3565 p_period_start_date,
3566 p_maintenance_object_id,
3567 p_maintenance_object_type,
3568 sysdate,
3569 p_user_id,
3570 sysdate,
3571 p_user_id,
3572 p_request_id,
3573 p_prog_app_id
3574 );
3575
3576 l_stmt_num := 520;
3577
3578 -- statement level logging
3579 IF (l_sLog) THEN
3580 FND_LOG.STRING(
3581 FND_LOG.LEVEL_STATEMENT,
3582 l_module || '.' || l_stmt_num,
3583 'Insert Successful for Serial Number ' || TO_CHAR(p_asset_number));
3584 END IF;
3585
3586 END IF; -- end checking asset balance rowcount
3587
3588 l_stmt_num := 525;
3589
3590 -- Standard check of p_commit
3591 IF FND_API.to_Boolean(p_commit) THEN
3592 COMMIT WORK;
3593 END IF;
3594
3595 -- Standard Call to get message count and if count = 1, get message info
3596 FND_MSG_PUB.COUNT_AND_GET (p_count => x_msg_count,
3597 p_data => x_msg_data );
3598
3599 -- Procedure level log message for exit point
3600 IF (l_pLog) THEN
3601 FND_LOG.STRING(
3602 FND_LOG.LEVEL_PROCEDURE,
3603 l_module || '.end',
3604 'InsertUpdate_PAC_assetPerBal >>'
3605 );
3606 END IF;
3607
3608 EXCEPTION
3609
3610 WHEN FND_API.g_exc_error THEN
3611 ROLLBACK TO InsertUpdate_PAC_astPerBal_PUB;
3612 x_return_status := FND_API.G_RET_STS_ERROR;
3613
3614 IF (l_uLog) THEN
3615 FND_LOG.STRING(
3616 FND_LOG.LEVEL_UNEXPECTED,
3617 l_module || '.' || l_stmt_num ,
3618 l_msg_data);
3619 END IF;
3620
3621 -- Get message count and data
3622 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
3623 p_data => x_msg_data);
3624
3625 WHEN FND_API.g_exc_unexpected_error THEN
3626 ROLLBACK TO InsertUpdate_PAC_astPerBal_PUB;
3627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3628
3629 IF (l_uLog) THEN
3630 FND_LOG.STRING(
3631 FND_LOG.LEVEL_UNEXPECTED,
3632 l_module || '.' || l_stmt_num ,
3633 l_msg_data);
3634 END IF;
3635
3636 -- Get message count and data
3637 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
3638 p_data => x_msg_data);
3639
3640 WHEN OTHERS THEN
3641 ROLLBACK TO InsertUpdate_PAC_astPerBal_PUB;
3642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3643
3644 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3645 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
3646 l_api_name,
3647 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
3648 || SUBSTRB (SQLERRM , 1 , 240));
3649
3650 END IF;
3651
3652 IF (l_uLog) THEN
3653 FND_LOG.STRING(
3654 FND_LOG.LEVEL_UNEXPECTED,
3655 l_module || '.' || l_stmt_num ,
3656 l_msg_data || SUBSTRB (SQLERRM , 1 , 240));
3657 END IF;
3658
3659 -- Get message count and data
3660 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
3661 p_data => x_msg_data);
3662
3663 END InsertUpdate_PAC_assetPerBal;
3664
3665
3666
3667 -- Start of comments
3668 -- API name : Compute_PAC_JobActuals
3669 -- Type : Public.
3670 -- Function : This API is called from CSTPPWRO.process_wip_resovhd_txns and
3671 -- CSTPPWMT.charge_wip_material
3672 -- Flow:
3673 -- |-- Get Period set name and Period name from Period ID passed
3674 -- |-- Get asset group, asset number and maint obj for the wip_entity_id
3675 -- |-- Derive the currency extended precision for the organization
3676 -- |-- Get maint cost category
3677 -- |-- Get eAM cost element
3678 -- | |-- If Direct Items use get_CostEle_for_DirectItem
3679 -- | |-- Else use Get_eamCostElement
3680 -- |-- End If
3681 -- |-- Call API InsertUpdate_PAC_eamPerBal to update eAM PAC tables.
3682 --
3683 -- Pre-reqs : None.
3684 -- Parameters :
3685 -- IN : p_api_version IN NUMBER Required
3686 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
3687 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
3688 -- p_validation_level IN NUMBER Optional Default =
3689 -- FND_API.G_VALID_LEVEL_FULL
3690 -- p_legal_entity_id IN NUMBER,
3691 -- p_cost_group_id IN NUMBER,
3692 -- p_cost_type_id IN NUMBER,
3693 -- p_period_id IN NUMBER Default = null,
3694 -- p_organization_id IN NUMBER,
3695 -- p_txn_mode IN NUMBER,
3696 -- p_txn_id IN NUMBER,
3697 -- p_value IN NUMBER,
3698 -- p_entity_id IN NUMBER,
3699 -- p_op_seq IN NUMBER,
3700 -- p_resource_id IN NUMBER,
3701 -- p_resource_seq_num IN NUMBER,
3702 -- p_user_id IN NUMBER,
3703 -- p_request_id IN NUMBER,
3704 -- p_prog_id IN NUMBER,
3705 -- p_prog_app_id IN NUMBER,
3706 -- p_login_id IN NUMBER
3707 -- OUT : x_return_status OUT VARCHAR2(1)
3708 -- x_msg_count OUT NUMBER
3709 -- x_msg_data OUT VARCHAR2(2000)
3710 -- Version : Current version 1.0
3711 --
3712 -- Notes : This procedure gets asset, cost element and category associations
3713 -- for the actual txns and then calls API's to update PAC_EAM tables
3714 --
3715 -- End of comments
3716
3717 PROCEDURE Compute_PAC_JobActuals(
3718 p_api_version IN NUMBER,
3719 p_init_msg_list IN VARCHAR2,
3720 p_commit IN VARCHAR2,
3721 p_validation_level IN NUMBER,
3722 x_return_status OUT NOCOPY VARCHAR2,
3723 x_msg_count OUT NOCOPY NUMBER,
3724 x_msg_data OUT NOCOPY VARCHAR2,
3725 p_legal_entity_id IN NUMBER,
3726 p_cost_group_id IN NUMBER,
3727 p_cost_type_id IN NUMBER,
3728 p_pac_period_id IN NUMBER,
3729 p_pac_ct_id IN NUMBER,
3730 p_organization_id IN NUMBER,
3731 p_txn_mode IN NUMBER, -- To indicate Resource/Direct Item Txn
3732 p_txn_id IN NUMBER,
3733 p_value IN NUMBER,
3734 p_wip_entity_id IN NUMBER,
3735 p_op_seq IN NUMBER,
3736 p_resource_id IN NUMBER,
3737 p_resource_seq_num IN NUMBER,
3738 p_user_id IN NUMBER,
3739 p_request_id IN NUMBER,
3740 p_prog_app_id IN NUMBER,
3741 p_prog_id IN NUMBER,
3742 p_login_id IN NUMBER
3743 ) IS
3744
3745 l_api_name CONSTANT VARCHAR2(30) := 'Compute_PAC_JobActuals';
3746 l_api_version CONSTANT NUMBER := 1.0;
3747
3748 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
3749 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
3750
3751 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3752 l_msg_count NUMBER := 0;
3753 l_msg_data VARCHAR2(8000);
3754 l_api_message VARCHAR2(1000);
3755
3756 l_stmt_num NUMBER := 10;
3757
3758 l_pac_period_id NUMBER := 0;
3759 l_period_set_name VARCHAR2(1000) := null;
3760 l_period_name VARCHAR2(1000) := null;
3761 l_owning_dept_id NUMBER := 0;
3762 l_operation_dept_id NUMBER := 0;
3763 l_maint_cost_category NUMBER := 0;
3764 l_mnt_obj_id NUMBER := 0;
3765 l_eam_cost_element NUMBER := 0;
3766 l_asset_group_item_id NUMBER := 0;
3767 l_asset_number VARCHAR2(30);
3768 l_round_unit NUMBER := 0;
3769 l_precision NUMBER := 0;
3770 l_ext_precision NUMBER := 0;
3771
3772 /* Log Severities*/
3773 /* 6- UNEXPECTED */
3774 /* 5- ERROR */
3775 /* 4- EXCEPTION */
3776 /* 3- EVENT */
3777 /* 2- PROCEDURE */
3778 /* 1- STATEMENT */
3779
3780 /* In general, we should use the following:
3781 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3782 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
3783 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
3784 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
3785 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
3786 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
3787 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
3788 */
3789
3790 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
3791 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
3792 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
3793
3794 BEGIN
3795
3796 -- Procedure level log message for Entry point
3797 IF (l_pLog) THEN
3798 FND_LOG.STRING(
3799 FND_LOG.LEVEL_PROCEDURE,
3800 l_module || '.begin',
3801 'Compute_PAC_JobActuals <<');
3802 END IF;
3803
3804 -- Standard Start of API savepoint
3805 SAVEPOINT Compute_PAC_JobActuals_PUB;
3806
3807 -- Standard call to check for call compatibility
3808 IF NOT FND_API.Compatible_API_Call (l_api_version,
3809 p_api_version,
3810 l_api_name,
3811 G_PKG_NAME ) THEN
3812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3813 END IF;
3814
3815 -- Initialize message list if p_init_msg_list is set to TRUE
3816 IF FND_API.to_Boolean(p_init_msg_list) THEN
3817 FND_MSG_PUB.initialize;
3818 END IF;
3819
3820 -- Initialize API return status to success
3821 x_return_status := FND_API.G_RET_STS_SUCCESS;
3822 l_stmt_num := 0;
3823
3824 -- Get period details from period id
3825 SELECT cpp.PAC_PERIOD_ID,
3826 cpp.period_set_name,
3827 cpp.period_name
3828 INTO l_pac_period_id,
3829 l_period_set_name,
3830 l_period_name
3831 FROM CST_PAC_periods cpp
3832 WHERE cpp.pac_period_id = p_pac_period_id;
3833
3834 -- statement level logging
3835 IF (l_sLog) THEN
3836 FND_LOG.STRING(
3837 FND_LOG.LEVEL_STATEMENT,
3838 l_module || '.' || l_stmt_num,
3839 'Period details retreived');
3840 END IF;
3841
3842 l_stmt_num := 5;
3843
3844 IF (l_pac_period_id IS NULL
3845 AND (l_period_set_name IS NULL OR l_period_name IS NULL)) THEN
3846
3847 l_api_message := 'Cannot Find Period for the period_id ' || TO_CHAR(p_pac_period_id);
3848 l_msg_data := l_api_message;
3849 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
3850 l_api_name,
3851 '(' || TO_CHAR(l_stmt_num) ||'): ' || l_api_message);
3852 RAISE FND_API.G_EXC_ERROR;
3853 END IF;
3854
3855 -- Get the associated asset item
3856 CST_EAMCOST_PUB.GET_CHARGE_ASSET (
3857 p_api_version => 1.0,
3858 p_wip_entity_id => p_wip_entity_id,
3859 x_inventory_item_id => l_asset_group_item_id,
3860 x_serial_number => l_asset_number,
3861 x_maintenance_object_id => l_mnt_obj_id,
3862 x_return_status => l_return_status,
3863 x_msg_count => l_msg_count,
3864 x_msg_data => l_msg_data);
3865
3866 l_stmt_num := 10;
3867
3868 IF (L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) THEN
3869 l_api_message := 'CST_EAMCOST_PUB.GET_CHARGE_ASSET() returned error';
3870 l_msg_data := l_api_message;
3871 FND_MESSAGE.SET_NAME ('BOM', 'CST_API_MESSAGE');
3872 FND_MESSAGE.set_token('TEXT', 'CST_PacEamCost_GRP.Compute_PAC_JobActuals('
3873 || to_char(l_stmt_num) || '): '|| l_api_message);
3874 FND_MSG_PUB.ADD;
3875 RAISE FND_API.G_EXC_ERROR;
3876 END IF;
3877
3878 -- statement level logging
3879 IF (l_sLog) THEN
3880 FND_LOG.STRING(
3881 FND_LOG.LEVEL_STATEMENT,
3882 l_module || '.' || l_stmt_num,
3883 'Got associated Asset Item');
3884 END IF;
3885
3886
3887 -- Derive the currency extended precision for the organization
3888 CSTPUTIL.CSTPUGCI(p_organization_id,
3889 l_round_unit,
3890 l_precision,
3891 l_ext_precision);
3892
3893 l_stmt_num := 15;
3894
3895 IF (p_txn_mode = 17 ) then -- For Direct Item txns
3896
3897 -- statement level logging
3898 IF (l_sLog) THEN
3899 FND_LOG.STRING(
3900 FND_LOG.LEVEL_STATEMENT,
3901 l_module || '.' || l_stmt_num,
3902 'Processing for Direct Item');
3903 END IF;
3904
3905 -- Get the associated maintainence cost category set by the user
3906 CST_EAMCOST_PUB.Get_MaintCostCat(
3907 p_txn_mode => 1,
3908 p_wip_entity_id => p_wip_entity_id,
3909 p_opseq_num => p_op_seq,
3910 p_resource_id => p_resource_id,
3911 p_res_seq_num => p_resource_seq_num,
3912 x_return_status => l_return_status,
3913 x_operation_dept => l_operation_dept_id,
3914 x_owning_dept => l_owning_dept_id,
3915 x_maint_cost_cat => l_maint_cost_category);
3916
3917 l_stmt_num := 20;
3918
3919 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3920 l_api_message := 'CST_EAMCOST_PUB.Get_MaintCostCat returned error';
3921 l_msg_data := l_api_message;
3922 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
3923 l_api_name,
3924 '(' || TO_CHAR(l_stmt_num) ||'): ' || l_api_message);
3925 RAISE FND_API.G_EXC_ERROR;
3926 END IF;
3927
3928
3929 -- Get direct item cost element
3930 CST_EAMCOST_PUB.get_CostEle_for_DirectItem (
3931 p_api_version => 1.0,
3932 p_init_msg_list => p_init_msg_list,
3933 p_commit => p_commit,
3934 p_validation_level => p_validation_level,
3935 x_return_status => l_return_status,
3936 x_msg_count => l_msg_count,
3937 x_msg_data => l_msg_data,
3938 p_txn_id => p_txn_id,
3939 p_mnt_or_mfg => 1,
3940 p_pac_or_perp => 1, -- PAC calling
3941 x_cost_element_id => l_eam_cost_element);
3942
3943 l_stmt_num := 25;
3944
3945 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
3946 l_api_message := 'CST_EAMCOST_PUB.get_CostEle_for_DirectItem returned error';
3947 l_msg_data := l_api_message;
3948 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
3949 FND_MESSAGE.set_token('TEXT', 'CST_PacEamCost_GRP.Compute_PAC_JobActuals('
3950 || to_char(l_stmt_num) || '): ' || l_api_message);
3951 FND_MSG_PUB.ADD;
3952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3953 END IF;
3954
3955 -- statement level logging
3956 IF (l_sLog) THEN
3957 FND_LOG.STRING(
3958 FND_LOG.LEVEL_STATEMENT,
3959 l_module || '.' || l_stmt_num,
3960 'Maint cost cat and cost element got for Direct Item');
3961 END IF;
3962
3963 ELSE -- Not a direct Item
3964
3965 -- statement level logging
3966 IF (l_sLog) THEN
3967 FND_LOG.STRING(
3968 FND_LOG.LEVEL_STATEMENT,
3969 l_module || '.' || l_stmt_num,
3970 'Not a Direct Item');
3971 END IF;
3972
3973 -- Get the associated maintainence cost category set by the user
3974 CST_EAMCOST_PUB.Get_MaintCostCat(
3975 p_txn_mode => p_txn_mode ,
3976 p_wip_entity_id => p_wip_entity_id,
3977 p_opseq_num => p_op_seq,
3978 p_resource_id => p_resource_id,
3979 p_res_seq_num => p_resource_seq_num,
3980 x_return_status => l_return_status,
3981 x_operation_dept => l_operation_dept_id,
3982 x_owning_dept => l_owning_dept_id,
3983 x_maint_cost_cat => l_maint_cost_category);
3984
3985 l_stmt_num := 20;
3986
3987 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3988 l_api_message := 'CST_EAMCOST_PUB.Get_MaintCostCat returned error';
3989 l_msg_data := l_api_message;
3990 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
3991 l_api_name,
3992 '(' || TO_CHAR(l_stmt_num) ||'): ' || l_api_message);
3993 RAISE FND_API.G_EXC_ERROR;
3994 END IF;
3995
3996 -- Get eam cost element
3997 l_eam_cost_element := CST_EAMCOST_PUB.Get_eamCostElement(
3998 p_txn_mode => p_txn_mode,
3999 p_org_id => p_organization_id,
4000 p_resource_id => p_resource_id);
4001
4002 l_stmt_num := 25;
4003
4004 IF l_eam_cost_element = 0 THEN
4005 l_api_message := 'Get_eamCostElement returned error';
4006 l_msg_data := l_api_message;
4007 FND_MSG_PUB.ADD_EXC_MSG(G_PKG_NAME,
4008 l_api_name,
4009 '(' || TO_CHAR(l_stmt_num) ||'): ' || l_api_message);
4010 RAISE FND_API.G_EXC_ERROR;
4011 END IF;
4012
4013 -- statement level logging
4014 IF (l_sLog) THEN
4015 FND_LOG.STRING(
4016 FND_LOG.LEVEL_STATEMENT,
4017 l_module || '.' || l_stmt_num,
4018 'Got Maint cost cat and cost element values');
4019 END IF;
4020
4021 END IF; -- end direct item check
4022
4023 l_stmt_num := 30;
4024
4025 InsertUpdate_PAC_eamPerBal(
4026 p_api_version => 1.0,
4027 x_return_status => l_return_status,
4028 x_msg_count => l_msg_count,
4029 x_msg_data => l_msg_data,
4030 p_legal_entity_id => p_legal_entity_id,
4031 p_cost_group_id => p_cost_group_id,
4032 p_cost_type_id => p_cost_type_id,
4033 p_period_id => l_pac_period_id,
4034 p_period_set_name => l_period_set_name,
4035 p_period_name => l_period_name,
4036 p_organization_id => p_organization_id,
4037 p_wip_entity_id => p_wip_entity_id,
4038 p_owning_dept_id => l_owning_dept_id,
4039 p_dept_id => l_operation_dept_id,
4040 p_maint_cost_cat => l_maint_cost_category,
4041 p_opseq_num => p_op_seq,
4042 p_eam_cost_element => l_eam_cost_element,
4043 p_asset_group_id => l_asset_group_item_id,
4044 p_asset_number => l_asset_number,
4045 p_value_type => 1, --Actuals
4046 p_value => p_value,
4047 p_user_id => p_user_id,
4048 p_request_id => p_request_id,
4049 p_prog_id => p_prog_id,
4050 p_prog_app_id => p_prog_app_id,
4051 p_login_id => p_login_id);
4052
4053 l_stmt_num := 35;
4054
4055 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4056 l_api_message := 'insertupdate_PAC_eamperbal() returned error';
4057 l_msg_data := l_api_message;
4058 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
4059 l_api_name,
4060 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
4061 || SUBSTRB (SQLERRM , 1 , 240));
4062 RAISE FND_API.G_EXC_ERROR;
4063 END IF;
4064
4065 -- statement level logging
4066 IF (l_sLog) THEN
4067 FND_LOG.STRING(
4068 FND_LOG.LEVEL_STATEMENT,
4069 l_module || '.' || l_stmt_num,
4070 'Insert/Update successful for Actuals');
4071 END IF;
4072
4073 IF FND_API.to_Boolean(p_commit) THEN
4074 COMMIT WORK;
4075 END IF;
4076 l_stmt_num := 40;
4077
4078 -- Standard Call to get message count and if count = 1, get message info
4079 FND_MSG_PUB.COUNT_AND_GET (p_count => x_msg_count,
4080 p_data => x_msg_data );
4081
4082 -- Procedure level log message for exit point
4083 IF (l_pLog) THEN
4084 FND_LOG.STRING(
4085 FND_LOG.LEVEL_PROCEDURE,
4086 l_module || '.end',
4087 'Compute_PAC_JobActuals >>'
4088 );
4089 END IF;
4090
4091 EXCEPTION
4092
4093 WHEN FND_API.g_exc_error THEN
4094 ROLLBACK TO Compute_PAC_JobActuals_PUB;
4095 x_return_status := FND_API.G_RET_STS_ERROR;
4096
4097 IF (l_uLog) THEN
4098 FND_LOG.STRING(
4099 FND_LOG.LEVEL_UNEXPECTED,
4100 l_module || '.' || l_stmt_num ,
4101 l_msg_data);
4102 END IF;
4103
4104 -- Get message count and data
4105 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
4106 p_data => x_msg_data);
4107
4108 WHEN FND_API.g_exc_unexpected_error THEN
4109 ROLLBACK TO Compute_PAC_JobActuals_PUB;
4110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4111
4112 IF (l_uLog) THEN
4113 FND_LOG.STRING(
4114 FND_LOG.LEVEL_UNEXPECTED,
4115 l_module || '.' || l_stmt_num ,
4116 l_msg_data);
4117 END IF;
4118
4119 -- Get message count and data
4120 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
4121 p_data => x_msg_data);
4122
4123 WHEN OTHERS THEN
4124 ROLLBACK TO Compute_PAC_JobActuals_PUB;
4125 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4126
4127 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4128 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
4129 l_api_name,
4130 '(' || TO_CHAR(l_stmt_num) || '): ' || l_api_message
4131 || SUBSTRB (SQLERRM , 1 , 240));
4132 END IF;
4133
4134 IF (l_uLog) THEN
4135 FND_LOG.STRING(
4136 FND_LOG.LEVEL_UNEXPECTED,
4137 l_module || '.' || l_stmt_num ,
4138 l_msg_data || SUBSTRB (SQLERRM , 1 , 240));
4139 END IF;
4140
4141 -- Get message count and data
4142 FND_MSG_PUB.COUNT_AND_GET(p_count => x_msg_count,
4143 p_data => x_msg_data);
4144
4145
4146 END Compute_PAC_JobActuals;
4147
4148
4149 -- Start of comments
4150 -- API name : Insert_PAC_eamBalAcct
4151 -- Type : Public.
4152 -- Function : This API is called from CST_PacEamCost_GRP.Estimate_PAC_WipJobs.
4153 -- The procedure inserts/updates data into CST_PAC_EAM_BALANCE_BY_ACCTS
4154 -- table.
4155 -- Flow:
4156 -- |-- Verify if the estimation data already exists for the wip job
4157 -- | and GL Account for the given cost group and cost type.
4158 -- | |--If data already exists add the new acct_value to existing
4159 -- | acct_value
4160 -- | |--Else insert a new row into the table
4161 --
4162 -- Pre-reqs : None.
4163 -- Parameters :
4164 -- IN : p_api_version IN NUMBER
4165 -- p_init_msg_list IN VARCHAR2
4166 -- p_commit IN VARCHAR2
4167 -- p_validation_level IN NUMBER
4168 -- p_legal_entity_id IN NUMBER,
4169 -- p_cost_group_id IN NUMBER,
4170 -- p_cost_type_id IN NUMBER,
4171 -- p_period_id IN NUMBER,
4172 -- p_period_set_name IN VARCHAR2,
4173 -- p_period_name IN VARCHAR2,
4174 -- p_org_id IN NUMBER,
4175 -- p_wip_entity_id IN NUMBER,
4176 -- p_owning_dept_id IN NUMBER,
4177 -- p_dept_id IN NUMBER,
4178 -- p_maint_cost_cat IN NUMBER,
4179 -- p_opseq_num IN NUMBER,
4180 -- p_period_start_date IN DATE,
4181 -- p_account_ccid IN NUMBER,
4182 -- p_value IN NUMBER,
4183 -- p_txn_type IN NUMBER,
4184 -- p_wip_acct_class IN VARCHAR2,
4185 -- p_mfg_cost_element_id IN NUMBER,
4186 -- p_user_id IN NUMBER,
4187 -- p_request_id IN NUMBER,
4188 -- p_prog_id IN NUMBER,
4189 -- p_prog_app_id IN NUMBER,
4190 -- p_login_id IN NUMBER
4191 -- OUT : x_return_status OUT VARCHAR2(1)
4192 -- x_msg_count OUT NUMBER
4193 -- x_msg_data OUT VARCHAR2(2000)
4194 -- Version : Current version 1.0
4195 --
4196 --
4197 -- End of comments
4198 PROCEDURE Insert_PAC_eamBalAcct
4199 (
4200 p_api_version IN NUMBER,
4201 p_init_msg_list IN VARCHAR2,
4202 p_commit IN VARCHAR2,
4203 p_validation_level IN NUMBER,
4204 x_return_status OUT NOCOPY VARCHAR2,
4205 x_msg_count OUT NOCOPY NUMBER,
4206 x_msg_data OUT NOCOPY VARCHAR2,
4207 p_legal_entity_id IN NUMBER,
4208 p_cost_group_id IN NUMBER,
4209 p_cost_type_id IN NUMBER,
4210 p_period_id IN NUMBER,
4211 p_period_set_name IN VARCHAR2,
4212 p_period_name IN VARCHAR2,
4213 p_org_id IN NUMBER,
4214 p_wip_entity_id IN NUMBER,
4215 p_owning_dept_id IN NUMBER,
4216 p_dept_id IN NUMBER,
4217 p_maint_cost_cat IN NUMBER,
4218 p_opseq_num IN NUMBER,
4219 p_period_start_date IN DATE,
4220 p_account_ccid IN NUMBER,
4221 p_value IN NUMBER,
4222 p_txn_type IN NUMBER,
4223 p_wip_acct_class IN VARCHAR2,
4224 p_mfg_cost_element_id IN NUMBER,
4225 p_user_id IN NUMBER,
4226 p_request_id IN NUMBER,
4227 p_prog_id IN NUMBER,
4228 p_prog_app_id IN NUMBER,
4229 p_login_id IN NUMBER
4230 )
4231 IS
4232 l_api_name CONSTANT VARCHAR2(30) := 'Insert_PAC_eamBalAcct';
4233 l_api_version CONSTANT NUMBER := 1.0;
4234
4235 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
4236 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
4237
4238 /* Log Severities*/
4239 /* 6- UNEXPECTED */
4240 /* 5- ERROR */
4241 /* 4- EXCEPTION */
4242 /* 3- EVENT */
4243 /* 2- PROCEDURE */
4244 /* 1- STATEMENT */
4245
4246 /* In general, we should use the following:
4247 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4248 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
4249 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
4250 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
4251 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
4252 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
4253 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
4254 */
4255
4256 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND
4257 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
4258 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
4259 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
4260
4261 l_cnt_cebba NUMBER;
4262 l_stmt_num NUMBER;
4263
4264 BEGIN
4265 -- Standard Start of API savepoint
4266 SAVEPOINT Insert_PAC_eamBalAcct_PUB;
4267
4268 if( l_pLog ) then
4269 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4270 l_module || '.begin',
4271 'Start of ' || l_full_name || '(' ||
4272 'p_user_id=' || p_user_id || ',' ||
4273 'p_login_id=' || p_login_id ||',' ||
4274 'p_prog_app_id=' || p_prog_app_id ||',' ||
4275 'p_prog_id=' || p_prog_id ||',' ||
4276 'p_request_id=' || p_request_id ||',' ||
4277 'p_legal_entity_id=' || p_legal_entity_id ||',' ||
4278 'p_cost_group_id=' || p_cost_group_id ||',' ||
4279 'p_cost_type_id=' || p_cost_type_id ||',' ||
4280 'p_wip_entity_id=' || p_wip_entity_id ||',' ||
4281 'p_org_id=' || p_org_id ||',' ||
4282 'p_wip_acct_class=' || p_wip_acct_class ||',' ||
4283 'p_account_ccid=' || p_account_ccid ||',' ||
4284 'p_maint_cost_cat =' || p_maint_cost_cat ||',' ||
4285 'p_opseq_num=' || p_opseq_num ||',' ||
4286 'p_mfg_cost_element_id=' || p_mfg_cost_element_id ||',' ||
4287 'p_dept_id=' || p_dept_id ||',' ||
4288 'p_value=' || p_value ||',' ||
4289 ')');
4290 end if;
4291
4292 -- Standard call to check for call compatibility.
4293 IF NOT FND_API.Compatible_API_Call ( l_api_version,
4294 p_api_version,
4295 l_api_name ,
4296 'CST_eamCost_PUB')
4297 THEN
4298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4299 END IF;
4300 -- Initialize message list if p_init_msg_list is set to TRUE.
4301 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4302 FND_MSG_PUB.initialize;
4303 END IF;
4304
4305 l_stmt_num := 10;
4306
4307 -- Initialize API return status to success
4308 x_return_status := FND_API.G_RET_STS_SUCCESS;
4309
4310
4311 /* Update the record if already exists else insert a new one */
4312
4313 MERGE INTO CST_PAC_EAM_BALANCE_BY_ACCTS cebba
4314 USING
4315 (
4316 SELECT NULL FROM DUAL
4317 ) temp
4318 ON
4319 (
4320 cebba.legal_entity_id = p_legal_entity_id AND
4321 cebba.cost_group_id = p_cost_group_id AND
4322 cebba.cost_type_id = p_cost_type_id AND
4323 cebba.period_set_name = p_period_set_name AND
4324 cebba.period_name = p_period_name AND
4325 cebba.wip_entity_id = p_wip_entity_id AND
4326 cebba.organization_id = p_org_id AND
4327 cebba.maint_cost_category = p_maint_cost_cat AND
4328 cebba.owning_dept_id = p_owning_dept_id AND
4329 cebba.period_start_date = p_period_start_date AND
4330 cebba.account_id = p_account_ccid AND
4331 cebba.txn_type = p_txn_type AND
4332 cebba.wip_acct_class_code = p_wip_acct_class AND
4333 cebba.mfg_cost_element_id = p_mfg_cost_element_id
4334 )
4335 WHEN MATCHED THEN
4336 UPDATE
4337 SET cebba.acct_value = cebba.acct_value + p_value,
4338 cebba.LAST_UPDATE_DATE = sysdate,
4339 cebba.LAST_UPDATED_BY = p_user_id,
4340 cebba.LAST_UPDATE_LOGIN = p_login_id
4341 WHEN NOT MATCHED THEN
4342 INSERT
4343 (
4344 LEGAL_ENTITY_ID,
4345 COST_GROUP_ID,
4346 COST_TYPE_ID,
4347 PERIOD_SET_NAME,
4348 PERIOD_NAME,
4349 ACCT_PERIOD_ID,
4350 WIP_ENTITY_ID,
4351 ORGANIZATION_ID,
4352 OPERATIONS_DEPT_ID,
4353 OPERATIONS_SEQ_NUM,
4354 MAINT_COST_CATEGORY,
4355 OWNING_DEPT_ID,
4356 PERIOD_START_DATE,
4357 ACCOUNT_ID,
4358 ACCT_VALUE,
4359 TXN_TYPE,
4360 WIP_ACCT_CLASS_CODE,
4361 MFG_COST_ELEMENT_ID,
4362 LAST_UPDATE_DATE,
4363 LAST_UPDATED_BY,
4364 CREATION_DATE,
4365 CREATED_BY,
4366 LAST_UPDATE_LOGIN
4367 )VALUES
4368 (
4369 p_legal_entity_id,
4370 p_cost_group_id,
4371 p_cost_type_id,
4372 p_period_set_name,
4373 p_period_name ,
4374 p_period_id ,
4375 p_wip_entity_id,
4376 p_org_id ,
4377 p_dept_id,
4378 p_opseq_num ,
4379 p_maint_cost_cat,
4380 p_owning_dept_id,
4381 p_period_start_date,
4382 p_account_ccid,
4383 p_value ,
4384 p_txn_type,
4385 p_wip_acct_class,
4386 p_mfg_cost_element_id,
4387 sysdate,
4388 p_user_id ,
4389 sysdate,
4390 p_prog_app_id ,
4391 p_login_id
4392 );
4393
4394 if( l_sLog ) then
4395 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4396 l_module || '.' || to_char(l_stmt_num),
4397 '.updated/inserted the record for :' || to_char(p_wip_entity_id)
4398 );
4399 end if;
4400
4401 -- Standard check of p_commit.
4402 IF FND_API.To_Boolean( p_commit ) THEN
4403 COMMIT WORK;
4404 END IF;
4405
4406
4407 /* Procedure level log message for Exit point */
4408 IF (l_pLog) THEN
4409 FND_LOG.STRING(
4410 FND_LOG.LEVEL_PROCEDURE,
4411 l_module || '.end',
4412 'End of ' || l_full_name
4413 );
4414 END IF;
4415
4416 -- Standard call to get message count and if count is 1, get message info.
4417 FND_MSG_PUB.Count_And_Get
4418 ( p_count => x_msg_count ,
4419 p_data => x_msg_data
4420 );
4421
4422 EXCEPTION
4423
4424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4425 ROLLBACK TO Insert_PAC_eamBalAcct_PUB;
4426
4427 IF (l_uLog) THEN
4428 FND_LOG.STRING(
4429 FND_LOG.LEVEL_UNEXPECTED,
4430 l_module || '.' || l_stmt_num,
4431 l_full_name ||'('|| l_stmt_num ||') :' || SUBSTRB (SQLERRM , 1 , 240)
4432 );
4433 END IF;
4434
4435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4436 FND_MSG_PUB.Count_And_Get
4437 ( p_count => x_msg_count ,
4438 p_data => x_msg_data
4439 );
4440 WHEN OTHERS THEN
4441 ROLLBACK TO Insert_PAC_eamBalAcct_PUB;
4442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4443
4444 IF (l_uLog) THEN
4445 FND_LOG.STRING(
4446 FND_LOG.LEVEL_UNEXPECTED,
4447 l_module || '.' || l_stmt_num,
4448 l_full_name ||'('|| l_stmt_num ||') :' || SUBSTRB (SQLERRM , 1 , 240)
4449 );
4450 END IF;
4451
4452 IF FND_MSG_PUB.Check_Msg_Level
4453 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4454 THEN
4455 FND_MSG_PUB.Add_Exc_Msg
4456 ( 'CST_eamCost_PUB' ,
4457 l_api_name
4458 );
4459 END IF;
4460 FND_MSG_PUB.Count_And_Get
4461 ( p_count => x_msg_count ,
4462 p_data => x_msg_data
4463 );
4464 END Insert_PAC_eamBalAcct;
4465
4466
4467
4468 -- Start of comments
4469 -- API name : Delete_PAC_eamBalAcct
4470 -- Type : Public.
4471 -- Function : This API is called from CST_PacEamCost_GRP.Estimate_PAC_WipJobs
4472 -- Flow:
4473 -- |-- Delete estimation data from CST_EAM_BALANCE_BY_ACCTS table for the
4474 -- | given legal entity id, cost group, cost type and wip job
4475 --
4476 -- Pre-reqs : None.
4477 -- Parameters :
4478 -- IN : p_api_version IN NUMBER
4479 -- p_init_msg_list IN VARCHAR2
4480 -- p_commit IN VARCHAR2
4481 -- p_validation_level IN NUMBER
4482 --
4483 -- p_legal_entity_id IN NUMBER,
4484 -- p_cost_group_id IN NUMBER,
4485 -- p_cost_type_id IN NUMBER,
4486 -- p_organization_id IN NUMBER,
4487 -- p_wip_entity_id_tab IN CST_PacEamCost_GRP.WIP_ENTITY_TYP,
4488 -- OUT : x_return_status OUT VARCHAR2(1)
4489 -- x_msg_count OUT NUMBER
4490 -- x_msg_data OUT VARCHAR2(2000)
4491 -- Version : Current version 1.0
4492 --
4493 -- End of comments
4494 PROCEDURE Delete_PAC_eamBalAcct
4495 (
4496 p_api_version IN NUMBER,
4497 p_init_msg_list IN VARCHAR2,
4498 p_commit IN VARCHAR2,
4499 p_validation_level IN NUMBER ,
4500 x_return_status OUT NOCOPY VARCHAR2,
4501 x_msg_count OUT NOCOPY VARCHAR2,
4502 x_msg_data OUT NOCOPY VARCHAR2,
4503 p_wip_entity_id_tab IN CST_PacEamCost_GRP.G_WIP_ENTITY_TYP,
4504 p_legal_entity_id IN NUMBER,
4505 p_cost_group_id IN NUMBER,
4506 p_cost_type_id IN NUMBER
4507
4508 )
4509 IS
4510 l_api_name CONSTANT VARCHAR2(30) := 'Delete_PAC_eamBalAcct';
4511 l_api_version CONSTANT NUMBER := 1.0;
4512
4513 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
4514 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
4515
4516 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND
4517 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
4518 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
4519
4520 l_stmt_num NUMBER;
4521 BEGIN
4522 -- Standard Start of API savepoint
4523 SAVEPOINT Delete_PAC_eamBalAcct_PUB;
4524 -- Standard call to check for call compatibility.
4525 IF NOT FND_API.Compatible_API_Call (l_api_version,
4526 p_api_version,
4527 l_api_name ,
4528 'CST_eamCost_PUB')
4529 THEN
4530 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4531 END IF;
4532 -- Initialize message list if p_init_msg_list is set to TRUE.
4533 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4534 FND_MSG_PUB.initialize;
4535 END IF;
4536
4537 if( l_pLog ) then
4538 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4539 l_module || '.begin',
4540 'Start of ' || l_full_name);
4541 end if;
4542
4543
4544 -- Initialize API return status to success
4545 x_return_status := FND_API.G_RET_STS_SUCCESS;
4546
4547 l_stmt_num := 10;
4548
4549 /* Delete data from CST_PAC_EAM_BALANCE_BY_ACCTS */
4550 FORALL l_index IN p_wip_entity_id_tab.FIRST..p_wip_entity_id_tab.LAST
4551 Delete from CST_PAC_EAM_BALANCE_BY_ACCTS
4552 where wip_entity_id = p_wip_entity_id_tab(l_index)
4553 -- and organization_id=p_org_id -- sikhanna not required
4554 and legal_entity_id = p_legal_entity_id
4555 and cost_group_id = p_cost_group_id
4556 and cost_type_id = p_cost_type_id;
4557
4558 -- Standard check of p_commit.
4559 IF FND_API.To_Boolean( p_commit ) THEN
4560 COMMIT WORK;
4561 END IF;
4562
4563 /* Procedure level log message for Exit point */
4564 IF (l_pLog) THEN
4565 FND_LOG.STRING(
4566 FND_LOG.LEVEL_PROCEDURE,
4567 l_module || '.end',
4568 'End of ' || l_full_name
4569 );
4570 END IF;
4571
4572 -- Standard call to get message count and if count is 1, get message info.
4573 FND_MSG_PUB.Count_And_Get
4574 ( p_count => x_msg_count ,
4575 p_data => x_msg_data
4576 );
4577
4578 EXCEPTION
4579
4580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4581 ROLLBACK TO Delete_PAC_eamBalAcct_PUB;
4582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4583
4584 IF (l_uLog) THEN
4585 FND_LOG.STRING(
4586 FND_LOG.LEVEL_UNEXPECTED,
4587 l_module || '.' || l_stmt_num,
4588 l_full_name ||'('|| l_stmt_num ||') :' ||
4589 SUBSTRB (SQLERRM , 1 , 240));
4590 END IF;
4591
4592 FND_MSG_PUB.Count_And_Get
4593 ( p_count => x_msg_count ,
4594 p_data => x_msg_data
4595 );
4596 WHEN OTHERS THEN
4597 ROLLBACK TO Delete_PAC_eamBalAcct_PUB;
4598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4599
4600 IF (l_uLog) THEN
4601 FND_LOG.STRING(
4602 FND_LOG.LEVEL_UNEXPECTED,
4603 l_module || '.' || l_stmt_num,
4604 l_full_name ||'('|| l_stmt_num ||') :' ||
4605 SUBSTRB (SQLERRM , 1 , 240));
4606 END IF;
4607
4608 IF FND_MSG_PUB.Check_Msg_Level
4609 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4610 THEN
4611 FND_MSG_PUB.Add_Exc_Msg
4612 ( 'CST_eamCost_PUB' ,
4613 l_api_name
4614 );
4615 END IF;
4616 FND_MSG_PUB.Count_And_Get
4617 ( p_count => x_msg_count ,
4618 p_data => x_msg_data
4619 );
4620 END Delete_PAC_eamBalAcct;
4621
4622 END CST_PacEamCost_GRP;