[Home] [Help]
PACKAGE BODY: APPS.CSD_SOO_PVT
Source
1 PACKAGE BODY CSD_SOO_PVT AS
2 /* $Header: csdvsoob.pls 120.0.12020000.4 2013/03/12 03:57:17 vicli noship $ */
3
4
5 function update_count(id IN NUMBER) return varchar2
6 IS
7 BEGIN
8 g_updated_ids.extend();
9 g_updated_ids(g_updated_ids.count) := id;
10 return 'A';
11
12 end;
13
14 function get_updated_ids return JTF_NUMBER_TABLE
15 IS
16
17 BEGIN
18 return g_updated_ids;
19 END get_updated_ids;
20
21
22 procedure update_rma_ro_line_id(id IN NUMBER)
23 IS
24 BEGIN
25 if not g_rma_ro_line_ids.exists(id) then
26 g_rma_ro_line_ids(id) := 1;
27 end if;
28 END;
29
30 procedure update_ship_ro_line_id(id IN NUMBER)
31 IS
32 BEGIN
33 if not g_ship_ro_line_ids.exists(id) then
34 g_ship_ro_line_ids(id) := 1;
35 end if;
36 END;
37
38 procedure update_int_order_header_id(id IN NUMBER)
39 IS
40 BEGIN
41 if not g_int_order_header_ids.exists(id) then
42 g_int_order_header_ids(id) := 1;
43 end if;
44 END;
45
46 /*-------------------------------------------------------------------------------------*/
47 /* Function name: DEBUG */
48 /* Description : Logs the debug message */
49 /* Called from : Called from Update API */
50 /* */
51 /* STANDARD PARAMETERS */
52 /* In Parameters : */
53 /* p_message Required Debug message that needs to be logged */
54 /* p_mod_name Required Module name */
55 /* p_severity_level Required Severity level */
56 /* Output Parameters: */
57 /* NON-STANDARD PARAMETERS */
58 /* In Parameters */
59 /* Out parameters */
60 /* Change Hist : */
61 /* 09/20/03 vlakaman Initial Creation. */
62 /*-------------------------------------------------------------------------------------*/
63
64 Procedure DEBUG
65 (p_message in varchar2,
66 p_mod_name in varchar2,
67 p_severity_level in number
68 ) IS
69
70 -- Variables used in FND Log
71 l_stat_level number := FND_LOG.LEVEL_STATEMENT;
72 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
73 l_event_level number := FND_LOG.LEVEL_EVENT;
74 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
75 l_error_level number := FND_LOG.LEVEL_ERROR;
76 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
77
78 BEGIN
79
80 IF p_severity_level = 1 THEN
81 IF ( l_stat_level >= G_debug_level) THEN
82 FND_LOG.STRING(l_stat_level,p_mod_name,p_message);
83 END IF;
84 ELSIF p_severity_level = 2 THEN
85 IF ( l_proc_level >= G_debug_level) THEN
86 FND_LOG.STRING(l_proc_level,p_mod_name,p_message);
87 END IF;
88 ELSIF p_severity_level = 3 THEN
89 IF ( l_event_level >= G_debug_level) THEN
90 FND_LOG.STRING(l_event_level,p_mod_name,p_message);
91 END IF;
92 ELSIF p_severity_level = 4 THEN
93 IF ( l_excep_level >= G_debug_level) THEN
94 FND_LOG.STRING(l_excep_level,p_mod_name,p_message);
95 END IF;
96 ELSIF p_severity_level = 5 THEN
97 IF ( l_error_level >= G_debug_level) THEN
98 FND_LOG.STRING(l_error_level,p_mod_name,p_message);
99 END IF;
100 ELSIF p_severity_level = 6 THEN
101 IF ( l_unexp_level >= G_debug_level) THEN
102 FND_LOG.STRING(l_unexp_level,p_mod_name,p_message);
103 END IF;
104 END IF;
105
106 END DEBUG;
107
108 Function Get_Document_Status
109 (
110 p_document_type in VARCHAR2,
111 p_status_code in VARCHAR2 := fnd_api.g_miss_char,
112 p_status_id in NUMBER := fnd_api.g_miss_num
113 ) RETURN VARCHAR2 IS
114
115 l_api_version NUMBER := 1.0;
116 l_return_status varchar2(1);
117 l_msg_count number;
118 l_msg_data varchar2(4000);
119 x_status_name varchar2(500);
120
121 BEGIN
122 CSD_SOO_PVT.Get_Document_Status
123 (p_api_version => l_api_version,
124 p_commit => FND_API.G_FALSE,
125 p_init_msg_list => FND_API.G_TRUE,
126 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
127 x_return_status => l_return_status,
128 x_msg_count => l_msg_count,
129 x_msg_data => l_msg_data,
130 p_document_type => p_document_type,
131 p_status_id => p_status_id,
132 p_status_code => p_status_code,
133 x_status_name => x_status_name);
134
135 return x_status_name;
136
137 END; -- function get_document_status
138
139 Procedure Get_Document_Status(p_document_type in VARCHAR2,
140 p_status_code in VARCHAR2 := FND_API.G_MISS_CHAR,
141 p_status_id in NUMBER := FND_API.G_MISS_NUM,
142 x_status_name out NOCOPY VARCHAR2,
143 p_api_version IN NUMBER,
144 p_commit IN VARCHAR2,
145 p_init_msg_list IN VARCHAR2,
146 p_validation_level IN NUMBER,
147 x_return_status OUT NOCOPY VARCHAR2,
148 x_msg_count OUT NOCOPY NUMBER,
149 x_msg_data OUT NOCOPY VARCHAR2)
150 IS
151 -- Standard Variables
152 l_api_name CONSTANT VARCHAR2(20) := 'GET_DOCUMENT_STATUS';
153 l_api_version CONSTANT NUMBER := 1.0;
154
155 -- Variables used in FND Log
156 l_error_level number := FND_LOG.LEVEL_ERROR;
157 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.get_document_status';
158
159 l_status_name varchar2(500);
160
161 CURSOR c_sr_status_name(c_incident_status_id NUMBER) IS
162 SELECT name
163 from cs_incident_statuses
164 where trunc(sysdate) between nvl(start_date_active, sysdate - 1)
165 and nvl(end_date_active, sysdate + 1)
166 and incident_status_id = c_incident_status_id;
167
168 CURSOR c_wip_status_name(c_job_status_id NUMBER) IS
169 select ml.meaning status_name
170 from mfg_lookups ml
171 where ml.lookup_type = 'WIP_JOB_STATUS'
172 and ml.lookup_code = c_job_status_id
173 and enabled_flag = 'Y'
174 and trunc(sysdate) between nvl(start_date_active, sysdate - 1)
175 and nvl(end_date_active, sysdate + 1);
176
177 CURSOR c_task_status_name(c_task_status_id NUMBER) IS
178 select name
179 from jtf_task_statuses_vl
180 where task_status_id = c_task_status_id and
181 trunc(sysdate) between nvl(start_date_active, sysdate - 1)
182 and nvl(end_date_active, sysdate + 1);
183
184 CURSOR c_req_header_name(c_auth_status_code VARCHAR2) IS
185 select meaning
186 from fnd_lookup_values
187 where lookup_type = 'AUTHORIZATION STATUS'
188 AND LOOKUP_CODE = NVL(c_auth_status_code,'INCOMPLETE')
189 AND LANGUAGE = USERENV('LANG')
190 and enabled_flag = 'Y'
191 and trunc(sysdate) between nvl(start_date_active, sysdate - 1)
192 and nvl(end_date_active, sysdate + 1);
193
194 CURSOR c_OM_LINE_STATUS(c_om_line_status_code VARCHAR2) IS
195 select
196 lkp.meaning status_name
197 from
198 oe_lookups lkp
199 where
200 lkp.lookup_type = 'LINE_FLOW_STATUS'
201 and lkp.lookup_code = c_om_line_status_code
202 and enabled_flag = 'Y'
203 and trunc(sysdate) between nvl(start_date_active, sysdate - 1)
204 and nvl(end_date_active, sysdate + 1);
205
206 CURSOR c_wsh_delivery_status(c_status_code VARCHAR2) IS
207 select meaning
208 from wsh_lookups
209 where lookup_type = 'PICK_STATUS' and
210 lookup_code = c_status_code and
211 enabled_flag = 'Y' and
212 trunc(sysdate) between nvl(start_date_active, sysdate - 1)
213 and nvl(end_date_active, sysdate + 1);
214
215 BEGIN
216 -- Initialize API return status to success
217 x_return_status := FND_API.G_RET_STS_SUCCESS;
218
219 -- Log the api name in the log file
220 Debug('At the Beginning of Get_Document_Status',l_mod_name,1);
221 -- Standard call to check for call compatibility.
222 IF NOT FND_API.Compatible_API_Call (l_api_version,
223 p_api_version,
224 l_api_name ,
225 G_PKG_NAME ) THEN
226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
227 END IF;
228
229 if (p_status_id is null or p_status_id = FND_API.G_MISS_NUM) and
230 (p_status_code is null or p_status_code = FND_API.G_MISS_CHAR) THEN
231 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
232 THEN
233 Fnd_Message.Set_Name('CSD', 'CSD_STATUS_NOT_GIVEN');
234 Fnd_Msg_Pub.ADD;
235 END IF;
236
237 RAISE FND_API.G_EXC_ERROR;
238 end if;
239
240 -- Initialize message list if p_init_msg_list is set to TRUE.
241 IF FND_API.to_Boolean( p_init_msg_list ) THEN
242 FND_MSG_PUB.initialize;
243 END IF;
244
245 -- see CSD_DOCUMENT_TYPES lookup for codes
246 if p_document_type = 'SR' THEN
247 OPEN c_sr_status_name(p_status_id);
248 FETCH c_sr_status_name into l_status_name;
249 CLOSE c_sr_status_name;
250 elsif p_document_type = 'SR_TASK' THEN
251 OPEN c_task_status_name(p_status_id);
252 FETCH c_task_status_name into l_status_name;
253 CLOSE c_task_status_name;
254 elsif p_document_type = 'RMA' OR p_document_type = 'SHIP'
255 or p_document_type = 'RMA_TP' or p_document_type = 'SHIP_TP'
256 or p_document_type = 'INTSO' THEN
257 OPEN c_OM_LINE_STATUS(p_status_code);
258 FETCH c_OM_LINE_STATUS into l_status_name;
259 CLOSE c_OM_LINE_STATUS;
260 elsif p_document_type = 'DELIVERY' THEN
261 OPEN c_wsh_delivery_status(p_status_code);
262 FETCH c_wsh_delivery_status into l_status_name;
263 CLOSE c_wsh_delivery_status;
264 elsif p_document_type = 'JOB' THEN
265 OPEN c_wip_status_name(p_status_id);
266 FETCH c_wip_status_name into l_status_name;
267 CLOSE c_wip_status_name;
268 -- elsif p_document_type = 'PO' THEN
269 -- elsif p_document_type = 'SO_ACTUAL' THEN
270 elsif p_document_type = 'PR' or p_document_type = 'IR' THEN
271 OPEN c_req_header_name(p_status_code);
272 FETCH c_req_header_name into l_status_name;
273 CLOSE c_req_header_name;
274 end if; -- if document_type = ...
275
276 x_status_name := l_status_name;
277
278 if l_status_name is null or l_status_name = '' then
279 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
280 THEN
281 Fnd_Message.Set_Name('CSD', 'CSD_WRONG_STATUS_GIVEN');
282 Fnd_Msg_Pub.ADD;
283 END IF;
284
285 RAISE FND_API.G_EXC_ERROR;
286 end if;
287
288 x_msg_count := fnd_msg_pub.count_msg;
289
290 EXCEPTION
291 WHEN FND_API.G_EXC_ERROR THEN
292 Debug('In FND_API.G_EXC_ERROR exception',l_mod_name,4);
293 x_msg_count := fnd_msg_pub.count_msg;
294 x_return_status := FND_API.G_RET_STS_ERROR ;
295 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
296 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR exception',l_mod_name,4);
297 x_msg_count := fnd_msg_pub.count_msg;
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
299 WHEN OTHERS THEN
300 Debug('In OTHERS exception',l_mod_name,4);
301
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
303 IF FND_MSG_PUB.Check_Msg_Level
304 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
305 THEN
306 FND_MSG_PUB.Add_Exc_Msg
307 (G_PKG_NAME ,
308 l_api_name );
309 END IF;
310 x_msg_count := fnd_msg_pub.count_msg;
311
312 END Get_Document_Status;
313
314
315 PROCEDURE Refresh_Job(p_api_version IN NUMBER,
316 p_commit IN VARCHAR2,
317 p_init_msg_list IN VARCHAR2,
318 p_validation_level IN NUMBER,
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2,
322 p_wip_entity_id IN NUMBER := FND_API.G_MISS_NUM,
323 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
324 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
325 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
326 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
327 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
328 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
329 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
330 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
331 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
332 IS
333 -- Standard Variables
334 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH_JOB';
335 l_api_version CONSTANT NUMBER := 1.0;
336
337 -- Variables in FND log
338 l_error_level number := FND_LOG.LEVEL_ERROR;
339 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh_job';
340
341 l_user_id NUMBER;
342 l_login_id NUMBER;
343
344 l_updated_keys JTF_NUMBER_TABLE;
345
346 l_prev_status_id NUMBER;
347 l_status_id NUMBER;
348 l_repair_line_id NUMBER;
349 l_wip_entity_id NUMBER;
350 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
351 l_rule_id NUMBER;
352
353 CURSOR c_get_defaulting_info(c_orchestration_id NUMBER) IS
354 SELECT prev_status_id, status_id, repair_line_id, document_header_id
355 FROM CSD_SO_ORCHESTRATION
356 WHERE orchestration_id = c_orchestration_id;
357
358 BEGIN
359 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh_Job...');
360
361 -- Initialize API return status to success
362 x_return_status := FND_API.G_RET_STS_SUCCESS;
363
364 -- Log the api name in the log file
365 Debug('At the Beginning of Refresh_Job', l_mod_name, 1);
366
367 -- Standard call to check for call compatibility.
368 IF NOT FND_API.Compatible_API_Call (l_api_version,
369 p_api_version,
370 l_api_name ,
371 G_PKG_NAME)
372 THEN
373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
374 END IF;
375
376 -- Initialize message list if p_init_msg_list is set to TRUE.
377 IF FND_API.to_Boolean(p_init_msg_list) THEN
378 FND_MSG_PUB.initialize;
379 END IF;
380
381 -- Api body starts
382 l_user_id := NVL(fnd_global.USER_ID, -1);
383 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
384
385
386 g_updated_ids := JTF_NUMBER_TABLE();
387
388 MERGE INTO CSD_SO_ORCHESTRATION soo USING (
389 select
390 xref.repair_line_id,
391 wdj.wip_entity_id document_header_id,
392 null document_line_id,
393 we.wip_entity_name document_header_name,
394 null document_line_name,
395 'JOB' document_type,
396 wdj.status_type status_id,
397 null status_code,
398 we.organization_id org_id
399 from
400 csd_repairs dra,
401 wip_discrete_jobs wdj,
402 wip_entities we,
403 ORG_ORGANIZATION_DEFINITIONS orgd,
404 csd_repair_job_xref xref
405 where
406 xref.wip_entity_id = wdj.wip_entity_id and
407 xref.wip_entity_id = we.wip_entity_id and
408 xref.repair_line_id = dra.repair_line_id and
409 orgd.organization_id = we.organization_id and
410 we.wip_entity_id = decode(p_wip_entity_id,
411 fnd_api.g_miss_num, we.wip_entity_id,
412 p_wip_entity_id) and
413 dra.repair_line_id = decode(p_repair_line_id,
414 fnd_api.g_miss_num, dra.repair_line_id,
415 p_repair_line_id) and
416 nvl(orgd.operating_unit, fnd_api.g_miss_num) =
417 decode(p_operating_unit_id,
418 fnd_api.g_miss_num, nvl(orgd.operating_unit, fnd_api.g_miss_num),
419 p_operating_unit_id) and
420 we.organization_id =
421 decode(p_inventory_org_id,
422 fnd_api.g_miss_num, we.organization_id,
423 p_inventory_org_id) and
424 nvl(dra.resource_id, fnd_api.g_miss_num) =
425 decode(p_repair_owner_id,
426 fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
427 p_repair_owner_id) and
428 dra.flow_status_id =
429 decode(p_ro_status_id,
430 fnd_api.g_miss_num, dra.flow_status_id,
431 p_ro_status_id) and
432 dra.repair_type_id =
433 decode(p_repair_type_id,
434 fnd_api.g_miss_num, dra.repair_type_id,
435 p_repair_type_id) and
436 nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
437 decode(p_ro_priority_code,
438 fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
439 p_ro_priority_code) and
440 dra.creation_date >=
441 decode(p_ro_age,
442 fnd_api.g_miss_num, dra.creation_date,
443 sysdate - p_ro_age) and
444 nvl(dra.promise_date, G_MAX_DATE) <=
445 decode(p_ro_due_date_age,
446 fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
447 sysdate + p_ro_due_date_age)) OLTP
448 ON (soo.repair_line_id = OLTP.repair_line_id and
449 soo.document_header_id = OLTP.document_header_id and
450 soo.document_type = 'JOB')
451 WHEN MATCHED THEN
452 UPDATE SET
453 soo.prev_status_id = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_id end),
454 soo.status_id = OLTP.status_id,
455 soo.object_version_number = soo.object_version_number + 1,
456 soo.last_update_date = sysdate,
457 soo.last_updated_by = l_user_id,
458 soo.last_update_login = l_login_id
459 WHEN NOT MATCHED THEN
460 INSERT (ORCHESTRATION_ID,
461 REPAIR_LINE_ID,
462 OBJECT_VERSION_NUMBER,
463 CREATED_BY,
464 CREATION_DATE,
465 LAST_UPDATED_BY,
466 LAST_UPDATE_DATE,
467 LAST_UPDATE_LOGIN,
468 DOCUMENT_HEADER_ID,
469 DOCUMENT_LINE_ID,
470 DOCUMENT_HEADER_NAME,
471 DOCUMENT_LINE_NAME,
472 DOCUMENT_TYPE,
473 STATUS_ID,
474 STATUS_CODE,
475 PREV_STATUS_ID,
476 PREV_STATUS_CODE,
477 ATTRIBUTE_CATEGORY,
478 ATTRIBUTE1,
479 ATTRIBUTE2,
480 ATTRIBUTE3,
481 ATTRIBUTE4,
482 ATTRIBUTE5,
483 ATTRIBUTE6,
484 ATTRIBUTE7,
485 ATTRIBUTE8,
486 ATTRIBUTE9,
487 ATTRIBUTE10,
488 ATTRIBUTE11,
489 ATTRIBUTE12,
490 ATTRIBUTE13,
491 ATTRIBUTE14,
492 ATTRIBUTE15,
493 PARAMN1)
494 VALUES
495 (csd_so_orchestration_s1.nextval,
496 OLTP.repair_line_id,
497 1,
498 l_user_id,
499 sysdate,
500 l_user_id,
501 sysdate,
502 l_login_id,
503 OLTP.document_header_id,
504 null, -- document_line_id
505 OLTP.document_header_name,
506 null, -- document_line_name
507 'JOB',
508 OLTP.status_id,
509 null, --status_code
510 null, -- prev_status_id
511 null, -- prev_status_code
512 null, -- attribute_category
513 null, -- attribute1
514 null,
515 null,
516 null,
517 null,
518 null,
519 null,
520 null,
521 null,
522 null,
523 null,
524 null,
525 null,
526 null,
527 null,
528 OLTP.org_id); --paramn1
529
530 -- Save changes to CSD_SO_ORCHESTRATION table even
531 -- if SOO rules engine fails.
532 IF FND_API.To_Boolean( p_commit ) THEN
533 COMMIT WORK;
534 END IF;
535
536 SAVEPOINT REFRESH_JOB;
537
538 l_updated_keys := get_updated_ids();
539
540 FOR i IN 1..l_updated_keys.COUNT
541 LOOP
542 OPEN c_get_defaulting_info(l_updated_keys(i));
543 FETCH c_get_defaulting_info INTO l_prev_status_id, l_status_id, l_repair_line_id, l_wip_entity_id;
544 CLOSE c_get_defaulting_info;
545
546
547 IF l_prev_status_id <> l_status_id THEN
548 l_rule_input_rec.repair_line_id := l_repair_line_id;
549 l_rule_input_rec.wip_entity_id := l_wip_entity_id;
550 l_rule_input_rec.job_prev_status_id := l_prev_status_id;
551 l_rule_input_rec.job_curr_status_id := l_status_id;
552
553 CSD_RULES_ENGINE_PVT.SOO_DEFAULTING(
554 p_api_version_number => 1.0,
555 p_commit => fnd_api.g_false,
556 p_validation_level => p_validation_level,
557 p_event_type => 'JOB_STATUS',
558 p_rule_input_rec => l_rule_input_rec,
559 x_rule_id => l_rule_id,
560 x_return_status => x_return_status,
561 x_msg_count => x_msg_count,
562 x_msg_data => x_msg_data);
563
564 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
565 Debug('Trying to default SOO rule for job status event failed.',l_mod_name,4);
566 RAISE FND_API.G_EXC_ERROR;
567 END IF;
568 END IF;
569 END LOOP;
570
571 -- Standard check of p_commit.
572 IF FND_API.To_Boolean( p_commit ) THEN
573 COMMIT WORK;
574 END IF;
575
576 x_msg_count := fnd_msg_pub.count_msg;
577
578 EXCEPTION
579 WHEN FND_API.G_EXC_ERROR THEN
580 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
581 ROLLBACK TO REFRESH_JOB;
582 x_return_status := FND_API.G_RET_STS_ERROR ;
583 x_msg_count := fnd_msg_pub.count_msg;
584 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
585 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
586 IF ( l_error_level >= G_debug_level) THEN
587 fnd_message.set_name('CSD','CSD_SQL_ERROR');
588 fnd_message.set_token('SQLERRM',SQLERRM);
589 fnd_message.set_token('SQLCODE',SQLCODE);
590 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
591 END If;
592 ROLLBACK TO REFRESH_JOB;
593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
594 x_msg_count := fnd_msg_pub.count_msg;
595 WHEN OTHERS THEN
596 Debug('In OTHERS Exception',l_mod_name,4);
597 IF ( l_error_level >= G_debug_level) THEN
598 fnd_message.set_name('CSD','CSD_SQL_ERROR');
599 fnd_message.set_token('SQLERRM',SQLERRM);
600 fnd_message.set_token('SQLCODE',SQLCODE);
601 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
602 END If;
603 ROLLBACK TO REFRESH_JOB;
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
605 IF FND_MSG_PUB.Check_Msg_Level
606 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
607 THEN
608 FND_MSG_PUB.Add_Exc_Msg
609 (G_PKG_NAME ,
610 l_api_name );
611 END IF;
612 x_msg_count := fnd_msg_pub.count_msg;
613
614 END Refresh_Job;
615
616 PROCEDURE Refresh_SR (p_api_version IN NUMBER,
617 p_commit IN VARCHAR2,
618 p_init_msg_list IN VARCHAR2,
619 p_validation_level IN NUMBER,
620 x_return_status OUT NOCOPY VARCHAR2,
621 x_msg_count OUT NOCOPY NUMBER,
622 x_msg_data OUT NOCOPY VARCHAR2,
623 p_incident_id IN NUMBER := FND_API.G_MISS_NUM,
624 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
625 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
626 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
627 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
628 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
629 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
630 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
631 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
632 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
633 IS
634 -- Standard Variables
635 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH_SR';
636 l_api_version CONSTANT NUMBER := 1.0;
637
638 -- Variables in FND log
639 l_error_level number := FND_LOG.LEVEL_ERROR;
640 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh_sr';
641
642 l_user_id NUMBER;
643 l_login_id NUMBER;
644
645 l_updated_keys JTF_NUMBER_TABLE;
646
647 l_prev_status_id NUMBER;
648 l_status_id NUMBER;
649 l_repair_line_id NUMBER;
650 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
651 l_rule_id NUMBER;
652
653 CURSOR c_get_defaulting_info(c_orchestration_id NUMBER) IS
654 SELECT prev_status_id, status_id, repair_line_id
655 FROM CSD_SO_ORCHESTRATION
656 WHERE orchestration_id = c_orchestration_id;
657
658 BEGIN
659 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh_SR...');
660
661 -- Initialize API return status to success
662 x_return_status := FND_API.G_RET_STS_SUCCESS;
663
664 -- Log the api name in the log file
665 Debug('At the Beginning of Refresh_SR', l_mod_name, 1);
666
667 -- Standard call to check for call compatibility.
668 IF NOT FND_API.Compatible_API_Call (l_api_version,
669 p_api_version,
670 l_api_name ,
671 G_PKG_NAME)
672 THEN
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674 END IF;
675
676 -- Initialize message list if p_init_msg_list is set to TRUE.
677 IF FND_API.to_Boolean(p_init_msg_list) THEN
678 FND_MSG_PUB.initialize;
679 END IF;
680
681 -- Api body starts
682 l_user_id := NVL(fnd_global.USER_ID, -1);
683 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
684
685 g_updated_ids := JTF_NUMBER_TABLE();
686
687 MERGE INTO CSD_SO_ORCHESTRATION soo USING (
688 select
689 dra.repair_line_id,
690 dra.incident_id document_header_id,
691 null document_line_id,
692 inc.incident_number document_header_name,
693 null document_line_name,
694 'SR' document_type,
695 inc.incident_status_id status_id,
696 null status_code
697 from
698 csd_repairs dra,
699 ORG_ORGANIZATION_DEFINITIONS orgd,
700 cs_incidents_all_b inc
701 where
702 dra.incident_id = inc.incident_id and
703 orgd.organization_id(+) = inc.inv_organization_id and
704 inc.incident_id = decode(p_incident_id,
705 fnd_api.g_miss_num, inc.incident_id,
706 p_incident_id) and
707 dra.repair_line_id = decode(p_repair_line_id,
708 fnd_api.g_miss_num, dra.repair_line_id,
709 p_repair_line_id) and
710 nvl(orgd.operating_unit, fnd_api.g_miss_num) =
711 decode(p_operating_unit_id,
712 fnd_api.g_miss_num, nvl(orgd.operating_unit, fnd_api.g_miss_num),
713 p_operating_unit_id) and
714 nvl(inc.inv_organization_id, fnd_api.g_miss_num) =
715 decode(p_inventory_org_id,
716 fnd_api.g_miss_num, nvl(inc.inv_organization_id, fnd_api.g_miss_num),
717 p_inventory_org_id) and
718 nvl(dra.resource_id, fnd_api.g_miss_num) =
719 decode(p_repair_owner_id,
720 fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
721 p_repair_owner_id) and
722 dra.flow_status_id =
723 decode(p_ro_status_id,
724 fnd_api.g_miss_num, dra.flow_status_id,
725 p_ro_status_id) and
726 dra.repair_type_id =
727 decode(p_repair_type_id,
728 fnd_api.g_miss_num, dra.repair_type_id,
729 p_repair_type_id) and
730 nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
731 decode(p_ro_priority_code,
732 fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
733 p_ro_priority_code) and
734 dra.creation_date >=
735 decode(p_ro_age,
736 fnd_api.g_miss_num, dra.creation_date,
737 sysdate - p_ro_age) and
738 nvl(dra.promise_date, G_MAX_DATE) <=
739 decode(p_ro_due_date_age,
740 fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
741 sysdate + p_ro_due_date_age)) OLTP
742 ON (soo.repair_line_id = OLTP.repair_line_id and
743 soo.document_header_id = OLTP.document_header_id and
744 soo.document_type = 'SR')
745 WHEN MATCHED THEN
746 UPDATE SET
747 soo.prev_status_id = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_id end),
748 soo.status_id = OLTP.status_id,
749 soo.object_version_number = soo.object_version_number + 1,
750 soo.last_update_date = sysdate,
751 soo.last_updated_by = l_user_id,
752 soo.last_update_login = l_login_id
753 WHEN NOT MATCHED THEN
754 INSERT (ORCHESTRATION_ID,
755 REPAIR_LINE_ID,
756 OBJECT_VERSION_NUMBER,
757 CREATED_BY,
758 CREATION_DATE,
759 LAST_UPDATED_BY,
760 LAST_UPDATE_DATE,
761 LAST_UPDATE_LOGIN,
762 DOCUMENT_HEADER_ID,
763 DOCUMENT_LINE_ID,
764 DOCUMENT_HEADER_NAME,
765 DOCUMENT_LINE_NAME,
766 DOCUMENT_TYPE,
767 STATUS_ID,
768 STATUS_CODE,
769 PREV_STATUS_ID,
770 PREV_STATUS_CODE,
771 ATTRIBUTE_CATEGORY,
772 ATTRIBUTE1,
773 ATTRIBUTE2,
774 ATTRIBUTE3,
775 ATTRIBUTE4,
776 ATTRIBUTE5,
777 ATTRIBUTE6,
778 ATTRIBUTE7,
779 ATTRIBUTE8,
780 ATTRIBUTE9,
781 ATTRIBUTE10,
782 ATTRIBUTE11,
783 ATTRIBUTE12,
784 ATTRIBUTE13,
785 ATTRIBUTE14,
786 ATTRIBUTE15,
787 PARAMN1)
788 VALUES
789 (csd_so_orchestration_s1.nextval,
790 OLTP.repair_line_id,
791 1,
792 l_user_id,
793 sysdate,
794 l_user_id,
795 sysdate,
796 l_login_id,
797 OLTP.document_header_id,
798 null, -- document_line_id
799 OLTP.document_header_name,
800 null, -- document_line_name
801 'SR',
802 OLTP.status_id,
803 null, -- status_code
804 null, -- prev_status_id
805 null, -- prev_status_code
806 null, -- attribute_category
807 null, -- attribute1
808 null,
809 null,
810 null,
811 null,
812 null,
813 null,
814 null,
815 null,
816 null,
817 null,
818 null,
819 null,
820 null,
821 null,
822 null); -- paramn1
823
824 -- Save changes to CSD_SO_ORCHESTRATION table even
825 -- if SOO rules engine fails.
826 IF FND_API.To_Boolean( p_commit ) THEN
827 COMMIT WORK;
828 END IF;
829
830 SAVEPOINT REFRESH_SR;
831
832 l_updated_keys := get_updated_ids();
833
834 FOR i IN 1..l_updated_keys.COUNT
835 LOOP
836 OPEN c_get_defaulting_info(l_updated_keys(i));
837 FETCH c_get_defaulting_info INTO l_prev_status_id, l_status_id, l_repair_line_id;
838 CLOSE c_get_defaulting_info;
839
840
841 IF l_prev_status_id <> l_status_id THEN
842 l_rule_input_rec.repair_line_id := l_repair_line_id;
843 l_rule_input_rec.sr_prev_status_id := l_prev_status_id;
844 l_rule_input_rec.sr_curr_status_id := l_status_id;
845
846 CSD_RULES_ENGINE_PVT.SOO_DEFAULTING(
847 p_api_version_number => 1.0,
848 p_commit => fnd_api.g_false,
849 p_validation_level => p_validation_level,
850 p_event_type => 'SR_STATUS',
851 p_rule_input_rec => l_rule_input_rec,
852 x_rule_id => l_rule_id,
853 x_return_status => x_return_status,
854 x_msg_count => x_msg_count,
855 x_msg_data => x_msg_data);
856
857 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
858 Debug('Trying to default SOO rule for SR status event failed.',l_mod_name,4);
859 RAISE FND_API.G_EXC_ERROR;
860 END IF;
861 END IF;
862 END LOOP;
863
864 -- Standard check of p_commit.
865 IF FND_API.To_Boolean( p_commit ) THEN
866 COMMIT WORK;
867 END IF;
868
869 x_msg_count := fnd_msg_pub.count_msg;
870
871 EXCEPTION
872 WHEN FND_API.G_EXC_ERROR THEN
873 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
874 ROLLBACK TO REFRESH_SR;
875 x_return_status := FND_API.G_RET_STS_ERROR ;
876 x_msg_count := fnd_msg_pub.count_msg;
877 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
878 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
879 IF ( l_error_level >= G_debug_level) THEN
880 fnd_message.set_name('CSD','CSD_SQL_ERROR');
881 fnd_message.set_token('SQLERRM',SQLERRM);
882 fnd_message.set_token('SQLCODE',SQLCODE);
883 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
884 END If;
885 ROLLBACK TO REFRESH_SR;
886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
887 x_msg_count := fnd_msg_pub.count_msg;
888 WHEN OTHERS THEN
889 Debug('In OTHERS Exception',l_mod_name,4);
890 IF ( l_error_level >= G_debug_level) THEN
891 fnd_message.set_name('CSD','CSD_SQL_ERROR');
892 fnd_message.set_token('SQLERRM',SQLERRM);
893 fnd_message.set_token('SQLCODE',SQLCODE);
894 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
895 END If;
896 ROLLBACK TO REFRESH_SR;
897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
898 IF FND_MSG_PUB.Check_Msg_Level
899 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
900 THEN
901 FND_MSG_PUB.Add_Exc_Msg
902 (G_PKG_NAME ,
903 l_api_name );
904 END IF;
905 x_msg_count := fnd_msg_pub.count_msg;
906
907 END Refresh_SR;
908
909 PROCEDURE Refresh_SR_Task
910 (p_api_version IN NUMBER,
911 p_commit IN VARCHAR2,
912 p_init_msg_list IN VARCHAR2,
913 p_validation_level IN NUMBER,
914 x_return_status OUT NOCOPY VARCHAR2,
915 x_msg_count OUT NOCOPY NUMBER,
916 x_msg_data OUT NOCOPY VARCHAR2,
917 p_task_id IN NUMBER := FND_API.G_MISS_NUM,
918 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
919 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
920 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
921 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
922 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
923 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
924 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
925 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
926 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
927 IS
928 -- Standard Variables
929 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH_SR_TASK';
930 l_api_version CONSTANT NUMBER := 1.0;
931
932 -- Variables in FND log
933 l_error_level number := FND_LOG.LEVEL_ERROR;
934 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh_sr_task';
935
936 l_user_id NUMBER;
937 l_login_id NUMBER;
938
939 l_updated_keys JTF_NUMBER_TABLE;
940
941 l_prev_status_id NUMBER;
942 l_status_id NUMBER;
943 l_repair_line_id NUMBER;
944 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
945 l_rule_id NUMBER;
946
947 CURSOR c_get_defaulting_info(c_orchestration_id NUMBER) IS
948 SELECT prev_status_id, status_id, repair_line_id
949 FROM CSD_SO_ORCHESTRATION
950 WHERE orchestration_id = c_orchestration_id;
951
952 BEGIN
953 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh_SR_Task...');
954
955 -- Initialize API return status to success
956 x_return_status := FND_API.G_RET_STS_SUCCESS;
957
958 -- Log the api name in the log file
959 Debug('At the Beginning of Refresh_SR_Task', l_mod_name, 1);
960
961 -- Standard call to check for call compatibility.
962 IF NOT FND_API.Compatible_API_Call (l_api_version,
963 p_api_version,
964 l_api_name ,
965 G_PKG_NAME)
966 THEN
967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
968 END IF;
969
970 -- Initialize message list if p_init_msg_list is set to TRUE.
971 IF FND_API.to_Boolean(p_init_msg_list) THEN
972 FND_MSG_PUB.initialize;
973 END IF;
974
975 -- Api body starts
976 l_user_id := NVL(fnd_global.USER_ID, -1);
977 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
978
979 g_updated_ids := JTF_NUMBER_TABLE();
980
981 MERGE INTO CSD_SO_ORCHESTRATION soo USING (
982 select
983 dra.repair_line_id,
984 tsk.task_id document_header_id,
985 null document_line_id,
986 tsk.task_number document_header_name,
987 null document_line_name,
988 'SR_TASK' document_type,
989 tsk.task_status_id status_id,
990 null status_code
991 from cs_sr_tasks_v tsk,
992 cs_incidents_all_b inc,
993 ORG_ORGANIZATION_DEFINITIONS orgd,
994 csd_repairs dra
995 where
996 tsk.source_object_type_code = 'SR' and
997 dra.incident_id = inc.incident_id and
998 dra.incident_id = tsk.source_object_id and
999 orgd.organization_id(+) = inc.inv_organization_id and
1000 tsk.task_id = decode(p_task_id,
1001 fnd_api.g_miss_num, tsk.task_id,
1002 p_task_id) and
1003 dra.repair_line_id = decode(p_repair_line_id,
1004 fnd_api.g_miss_num, dra.repair_line_id,
1005 p_repair_line_id) and
1006 nvl(orgd.operating_unit, fnd_api.g_miss_num) =
1007 decode(p_operating_unit_id,
1008 fnd_api.g_miss_num, nvl(orgd.operating_unit, fnd_api.g_miss_num),
1009 p_operating_unit_id) and
1010 nvl(inc.inv_organization_id, fnd_api.g_miss_num) =
1011 decode(p_inventory_org_id,
1012 fnd_api.g_miss_num, nvl(inc.inv_organization_id, fnd_api.g_miss_num),
1013 p_inventory_org_id) and
1014 nvl(dra.resource_id, fnd_api.g_miss_num) =
1015 decode(p_repair_owner_id,
1016 fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
1017 p_repair_owner_id) and
1018 dra.flow_status_id =
1019 decode(p_ro_status_id,
1020 fnd_api.g_miss_num, dra.flow_status_id,
1021 p_ro_status_id) and
1022 dra.repair_type_id =
1023 decode(p_repair_type_id,
1024 fnd_api.g_miss_num, dra.repair_type_id,
1025 p_repair_type_id) and
1026 nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
1027 decode(p_ro_priority_code,
1028 fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
1029 p_ro_priority_code) and
1030 dra.creation_date >=
1031 decode(p_ro_age,
1032 fnd_api.g_miss_num, dra.creation_date,
1033 sysdate - p_ro_age) and
1034 nvl(dra.promise_date, G_MAX_DATE) <=
1035 decode(p_ro_due_date_age,
1036 fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
1037 sysdate + p_ro_due_date_age)) OLTP
1038 ON (soo.repair_line_id = OLTP.repair_line_id and
1039 soo.document_header_id = OLTP.document_header_id and
1040 -- nvl(soo.document_line_id, fnd_api.g_miss_num) =
1041 -- nvl(OLTP.document_line_id, fnd_api.g_miss_num) and
1042 soo.document_type = 'SR_TASK')
1043 WHEN MATCHED THEN
1044 UPDATE SET
1045 soo.prev_status_id = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_id end),
1046 soo.status_id = OLTP.status_id,
1047 soo.object_version_number = soo.object_version_number + 1,
1048 soo.last_update_date = sysdate,
1049 soo.last_updated_by = l_user_id,
1050 soo.last_update_login = l_login_id
1051 WHEN NOT MATCHED THEN
1052 INSERT (ORCHESTRATION_ID,
1053 REPAIR_LINE_ID,
1054 OBJECT_VERSION_NUMBER,
1055 CREATED_BY,
1056 CREATION_DATE,
1057 LAST_UPDATED_BY,
1058 LAST_UPDATE_DATE,
1059 LAST_UPDATE_LOGIN,
1060 DOCUMENT_HEADER_ID,
1061 DOCUMENT_LINE_ID,
1062 DOCUMENT_HEADER_NAME,
1063 DOCUMENT_LINE_NAME,
1064 DOCUMENT_TYPE,
1065 STATUS_ID,
1066 STATUS_CODE,
1067 PREV_STATUS_ID,
1068 PREV_STATUS_CODE,
1069 ATTRIBUTE_CATEGORY,
1070 ATTRIBUTE1,
1071 ATTRIBUTE2,
1072 ATTRIBUTE3,
1073 ATTRIBUTE4,
1074 ATTRIBUTE5,
1075 ATTRIBUTE6,
1076 ATTRIBUTE7,
1077 ATTRIBUTE8,
1078 ATTRIBUTE9,
1079 ATTRIBUTE10,
1080 ATTRIBUTE11,
1081 ATTRIBUTE12,
1082 ATTRIBUTE13,
1083 ATTRIBUTE14,
1084 ATTRIBUTE15,
1085 PARAMN1)
1086 VALUES
1087 (csd_so_orchestration_s1.nextval,
1088 OLTP.repair_line_id,
1089 1,
1090 l_user_id,
1091 sysdate,
1092 l_user_id,
1093 sysdate,
1094 l_login_id,
1095 OLTP.document_header_id,
1096 null, -- document_line_id
1097 OLTP.document_header_name,
1098 null, -- document_line_name
1099 'SR_TASK',
1100 OLTP.status_id,
1101 null, -- status_code
1102 null, -- prev_status_id
1103 null, -- prev_status_code
1104 null, -- attribute_category
1105 null, -- attribute1
1106 null,
1107 null,
1108 null,
1109 null,
1110 null,
1111 null,
1112 null,
1113 null,
1114 null,
1115 null,
1116 null,
1117 null,
1118 null,
1119 null,
1120 null); -- paramn1
1121
1122 -- Save changes to CSD_SO_ORCHESTRATION table even
1123 -- if SOO rules engine fails.
1124 IF FND_API.To_Boolean( p_commit ) THEN
1125 COMMIT WORK;
1126 END IF;
1127
1128 SAVEPOINT REFRESH_SR_TASK;
1129
1130 l_updated_keys := get_updated_ids();
1131
1132 FOR i IN 1..l_updated_keys.COUNT
1133 LOOP
1134 OPEN c_get_defaulting_info(l_updated_keys(i));
1135 FETCH c_get_defaulting_info INTO l_prev_status_id, l_status_id, l_repair_line_id;
1136 CLOSE c_get_defaulting_info;
1137
1138
1139 IF l_prev_status_id <> l_status_id THEN
1140 l_rule_input_rec.repair_line_id := l_repair_line_id;
1141 l_rule_input_rec.sr_task_prev_status_id := l_prev_status_id;
1142 l_rule_input_rec.sr_task_curr_status_id := l_status_id;
1143
1144 CSD_RULES_ENGINE_PVT.SOO_DEFAULTING(
1145 p_api_version_number => 1.0,
1146 p_commit => fnd_api.g_false,
1147 p_validation_level => p_validation_level,
1148 p_event_type => 'SR_TASK_STATUS',
1149 p_rule_input_rec => l_rule_input_rec,
1150 x_rule_id => l_rule_id,
1151 x_return_status => x_return_status,
1152 x_msg_count => x_msg_count,
1153 x_msg_data => x_msg_data);
1154
1155 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1156 Debug('Trying to default SOO rule for SR Task status event failed.',l_mod_name,4);
1157 RAISE FND_API.G_EXC_ERROR;
1158 END IF;
1159 END IF;
1160 END LOOP;
1161
1162 -- Standard check of p_commit.
1163 IF FND_API.To_Boolean( p_commit ) THEN
1164 COMMIT WORK;
1165 END IF;
1166
1167 x_msg_count := fnd_msg_pub.count_msg;
1168
1169 EXCEPTION
1170 WHEN FND_API.G_EXC_ERROR THEN
1171 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
1172 ROLLBACK TO REFRESH_SR_TASK;
1173 x_return_status := FND_API.G_RET_STS_ERROR ;
1174 x_msg_count := fnd_msg_pub.count_msg;
1175 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1176 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
1177 IF ( l_error_level >= G_debug_level) THEN
1178 fnd_message.set_name('CSD','CSD_SQL_ERROR');
1179 fnd_message.set_token('SQLERRM',SQLERRM);
1180 fnd_message.set_token('SQLCODE',SQLCODE);
1181 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
1182 END If;
1183 ROLLBACK TO REFRESH_SR_TASK;
1184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1185 x_msg_count := fnd_msg_pub.count_msg;
1186 WHEN OTHERS THEN
1187 Debug('In OTHERS Exception',l_mod_name,4);
1188 IF ( l_error_level >= G_debug_level) THEN
1189 fnd_message.set_name('CSD','CSD_SQL_ERROR');
1190 fnd_message.set_token('SQLERRM',SQLERRM);
1191 fnd_message.set_token('SQLCODE',SQLCODE);
1192 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
1193 END If;
1194 ROLLBACK TO REFRESH_SR_TASK;
1195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1196 IF FND_MSG_PUB.Check_Msg_Level
1197 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1198 THEN
1199 FND_MSG_PUB.Add_Exc_Msg
1200 (G_PKG_NAME ,
1201 l_api_name );
1202 END IF;
1203 x_msg_count := fnd_msg_pub.count_msg;
1204
1205 END Refresh_SR_Task;
1206
1207 PROCEDURE Refresh_Purchase_Req
1208 (p_api_version IN NUMBER,
1209 p_commit IN VARCHAR2,
1210 p_init_msg_list IN VARCHAR2,
1211 p_validation_level IN NUMBER,
1212 x_return_status OUT NOCOPY VARCHAR2,
1213 x_msg_count OUT NOCOPY NUMBER,
1214 x_msg_data OUT NOCOPY VARCHAR2,
1215 p_req_header_id IN NUMBER := FND_API.G_MISS_NUM,
1216 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
1217 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
1218 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
1219 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
1220 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
1221 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
1222 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1223 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
1224 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
1225 IS
1226 -- Standard Variables
1227 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH_PURCHASE_REQ';
1228 l_api_version CONSTANT NUMBER := 1.0;
1229
1230 -- Variables in FND log
1231 l_error_level number := FND_LOG.LEVEL_ERROR;
1232 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh_purchase_req';
1233
1234 l_user_id NUMBER;
1235 l_login_id NUMBER;
1236
1237 l_updated_keys JTF_NUMBER_TABLE;
1238
1239 l_prev_status_CODE VARCHAR2(80);
1240 l_status_CODE VARCHAR2(80);
1241 l_repair_line_id NUMBER;
1242 l_wip_entity_id NUMBER;
1243 l_pr_header_id NUMBER;
1244 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
1245 l_rule_id NUMBER;
1246
1247 CURSOR c_get_defaulting_info(c_orchestration_id NUMBER) IS
1248 SELECT prev_status_code, status_code, repair_line_id, document_header_id
1249 FROM CSD_SO_ORCHESTRATION
1250 WHERE orchestration_id = c_orchestration_id;
1251
1252 CURSOR c_get_wip_entity_id(c_requisition_header_id NUMBER) IS
1253 SELECT wip_entity_id
1254 FROM po_requisition_lines_all
1255 WHERE requisition_header_id = c_requisition_header_id;
1256
1257 BEGIN
1258 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh_Purchase_Req...');
1259
1260 -- Initialize API return status to success
1261 x_return_status := FND_API.G_RET_STS_SUCCESS;
1262
1263 -- Log the api name in the log file
1264 Debug('At the Beginning of Refresh_Purchase_Req', l_mod_name, 1);
1265
1266 -- Standard call to check for call compatibility.
1267 IF NOT FND_API.Compatible_API_Call (l_api_version,
1268 p_api_version,
1269 l_api_name ,
1270 G_PKG_NAME)
1271 THEN
1272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1273 END IF;
1274
1275 -- Initialize message list if p_init_msg_list is set to TRUE.
1276 IF FND_API.to_Boolean(p_init_msg_list) THEN
1277 FND_MSG_PUB.initialize;
1278 END IF;
1279
1280 -- Api body starts
1281 l_user_id := NVL(fnd_global.USER_ID, -1);
1282 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
1283
1284 g_updated_ids := JTF_NUMBER_TABLE();
1285
1286 MERGE INTO CSD_SO_ORCHESTRATION soo USING (
1287 select distinct
1288 xref.repair_line_id,
1289 porh.requisition_header_id document_header_id,
1290 null document_line_id,
1291 porh.segment1 document_header_name,
1292 null document_line_name,
1293 'PR' document_type,
1294 null status_id,
1295 porh.authorization_status status_code
1296 from
1297 csd_repairs dra,
1298 wip_entities we,
1299 po_requisition_headers_all porh,
1300 csd_repair_job_xref xref,
1301 po_requisition_lines_all porl
1302 where
1303 dra.repair_line_id = xref.repair_line_id and
1304 porh.type_lookup_code = 'PURCHASE' and
1305 we.wip_entity_id = xref.wip_entity_id and
1306 porl.wip_entity_id = xref.wip_entity_id and
1307 porh.requisition_header_id = porl.requisition_header_id and
1308 porh.requisition_header_id = decode(p_req_header_id,
1309 fnd_api.g_miss_num, porh.requisition_header_id,
1310 p_req_header_id) and
1311 dra.repair_line_id = decode(p_repair_line_id,
1312 fnd_api.g_miss_num, dra.repair_line_id,
1313 p_repair_line_id) and
1314 nvl(porh.org_id, fnd_api.g_miss_num) =
1315 decode(p_operating_unit_id,
1316 fnd_api.g_miss_num, nvl(porh.org_id, fnd_api.g_miss_num),
1317 p_operating_unit_id) and
1318 we.organization_id =
1319 decode(p_inventory_org_id,
1320 fnd_api.g_miss_num, we.organization_id,
1321 p_inventory_org_id) and
1322 nvl(dra.resource_id, fnd_api.g_miss_num) =
1323 decode(p_repair_owner_id,
1324 fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
1325 p_repair_owner_id) and
1326 dra.flow_status_id =
1327 decode(p_ro_status_id,
1328 fnd_api.g_miss_num, dra.flow_status_id,
1329 p_ro_status_id) and
1330 dra.repair_type_id =
1331 decode(p_repair_type_id,
1332 fnd_api.g_miss_num, dra.repair_type_id,
1333 p_repair_type_id) and
1334 nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
1335 decode(p_ro_priority_code,
1336 fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
1337 p_ro_priority_code) and
1338 dra.creation_date >=
1339 decode(p_ro_age,
1340 fnd_api.g_miss_num, dra.creation_date,
1341 sysdate - p_ro_age) and
1342 nvl(dra.promise_date, G_MAX_DATE) <=
1343 decode(p_ro_due_date_age,
1344 fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
1345 sysdate + p_ro_due_date_age)) OLTP
1346 ON (soo.repair_line_id = OLTP.repair_line_id and
1347 soo.document_header_id = OLTP.document_header_id and
1348 -- nvl(soo.document_line_id, fnd_api.g_miss_num) =
1349 -- nvl(OLTP.document_line_id, fnd_api.g_miss_num) and
1350 soo.document_type = 'PR')
1351 WHEN MATCHED THEN
1352 UPDATE SET
1353 soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
1354 soo.status_code = OLTP.status_code,
1355 soo.object_version_number = soo.object_version_number + 1,
1356 soo.last_update_date = sysdate,
1357 soo.last_updated_by = l_user_id,
1358 soo.last_update_login = l_login_id
1359 WHEN NOT MATCHED THEN
1360 INSERT (ORCHESTRATION_ID,
1361 REPAIR_LINE_ID,
1362 OBJECT_VERSION_NUMBER,
1363 CREATED_BY,
1364 CREATION_DATE,
1365 LAST_UPDATED_BY,
1366 LAST_UPDATE_DATE,
1367 LAST_UPDATE_LOGIN,
1368 DOCUMENT_HEADER_ID,
1369 DOCUMENT_LINE_ID,
1370 DOCUMENT_HEADER_NAME,
1371 DOCUMENT_LINE_NAME,
1372 DOCUMENT_TYPE,
1373 STATUS_ID,
1374 STATUS_CODE,
1375 PREV_STATUS_ID,
1376 PREV_STATUS_CODE,
1377 ATTRIBUTE_CATEGORY,
1378 ATTRIBUTE1,
1379 ATTRIBUTE2,
1380 ATTRIBUTE3,
1381 ATTRIBUTE4,
1382 ATTRIBUTE5,
1383 ATTRIBUTE6,
1384 ATTRIBUTE7,
1385 ATTRIBUTE8,
1386 ATTRIBUTE9,
1387 ATTRIBUTE10,
1388 ATTRIBUTE11,
1389 ATTRIBUTE12,
1390 ATTRIBUTE13,
1391 ATTRIBUTE14,
1392 ATTRIBUTE15,
1393 PARAMN1)
1394 VALUES
1395 (csd_so_orchestration_s1.nextval,
1396 OLTP.repair_line_id,
1397 1,
1398 l_user_id,
1399 sysdate,
1400 l_user_id,
1401 sysdate,
1402 l_login_id,
1403 OLTP.document_header_id,
1404 null, -- document_line_id
1405 OLTP.document_header_name,
1406 null, -- document_line_name
1407 'PR',
1408 null, -- status_od
1409 OLTP.status_code,
1410 null, -- prev_status_id
1411 null, -- prev_status_code
1412 null, -- attribute_category
1413 null, -- attribute1
1414 null,
1415 null,
1416 null,
1417 null,
1418 null,
1419 null,
1420 null,
1421 null,
1422 null,
1423 null,
1424 null,
1425 null,
1426 null,
1427 null,
1428 null); -- paramn1
1429
1430 -- Save changes to CSD_SO_ORCHESTRATION table even
1431 -- if SOO rules engine fails.
1432 IF FND_API.To_Boolean( p_commit ) THEN
1433 COMMIT WORK;
1434 END IF;
1435
1436 SAVEPOINT REFRESH_PURCHASE_REQ;
1437
1438 l_updated_keys := get_updated_ids();
1439
1440 FOR i IN 1..l_updated_keys.COUNT
1441 LOOP
1442 OPEN c_get_defaulting_info(l_updated_keys(i));
1443 FETCH c_get_defaulting_info INTO l_prev_status_code, l_status_code, l_repair_line_id, l_pr_header_id;
1444 CLOSE c_get_defaulting_info;
1445
1446 OPEN c_get_wip_entity_id(l_pr_header_id);
1447 FETCH c_get_wip_entity_id INTO l_wip_entity_id;
1448 CLOSE c_get_wip_entity_id;
1449
1450 IF l_prev_status_code <> l_status_code THEN
1451 l_rule_input_rec.repair_line_id := l_repair_line_id;
1452 l_rule_input_rec.wip_entity_id := l_wip_entity_id;
1453 l_rule_input_rec.pr_prev_status_code := l_prev_status_code;
1454 l_rule_input_rec.pr_curr_status_code := l_status_code;
1455
1456 CSD_RULES_ENGINE_PVT.SOO_DEFAULTING(
1457 p_api_version_number => 1.0,
1458 p_commit => fnd_api.g_false,
1459 p_validation_level => p_validation_level,
1460 p_event_type => 'PR_STATUS',
1461 p_rule_input_rec => l_rule_input_rec,
1462 x_rule_id => l_rule_id,
1463 x_return_status => x_return_status,
1464 x_msg_count => x_msg_count,
1465 x_msg_data => x_msg_data);
1466
1467 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1468 Debug('Trying to default SOO rule for Purchase Req status event failed.',l_mod_name,4);
1469 RAISE FND_API.G_EXC_ERROR;
1470 END IF;
1471 END IF;
1472 END LOOP;
1473
1474 -- Standard check of p_commit.
1475 IF FND_API.To_Boolean( p_commit ) THEN
1476 COMMIT WORK;
1477 END IF;
1478
1479 x_msg_count := fnd_msg_pub.count_msg;
1480
1481 EXCEPTION
1482 WHEN FND_API.G_EXC_ERROR THEN
1483 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
1484 ROLLBACK TO REFRESH_PURCHASE_REQ;
1485 x_return_status := FND_API.G_RET_STS_ERROR ;
1486 x_msg_count := fnd_msg_pub.count_msg;
1487 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1488 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
1489 IF ( l_error_level >= G_debug_level) THEN
1490 fnd_message.set_name('CSD','CSD_SQL_ERROR');
1491 fnd_message.set_token('SQLERRM',SQLERRM);
1492 fnd_message.set_token('SQLCODE',SQLCODE);
1493 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
1494 END If;
1495 ROLLBACK TO REFRESH_PURCHASE_REQ;
1496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1497 x_msg_count := fnd_msg_pub.count_msg;
1498 WHEN OTHERS THEN
1499 Debug('In OTHERS Exception',l_mod_name,4);
1500 IF ( l_error_level >= G_debug_level) THEN
1501 fnd_message.set_name('CSD','CSD_SQL_ERROR');
1502 fnd_message.set_token('SQLERRM',SQLERRM);
1503 fnd_message.set_token('SQLCODE',SQLCODE);
1504 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
1505 END If;
1506 ROLLBACK TO REFRESH_PURCHASE_REQ;
1507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1508 IF FND_MSG_PUB.Check_Msg_Level
1509 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1510 THEN
1511 FND_MSG_PUB.Add_Exc_Msg
1512 (G_PKG_NAME ,
1513 l_api_name );
1514 END IF;
1515 x_msg_count := fnd_msg_pub.count_msg;
1516
1517 END Refresh_Purchase_Req;
1518
1519 PROCEDURE Refresh_Internal_Req
1520 (p_api_version IN NUMBER,
1521 p_commit IN VARCHAR2,
1522 p_init_msg_list IN VARCHAR2,
1523 p_validation_level IN NUMBER,
1524 x_return_status OUT NOCOPY VARCHAR2,
1525 x_msg_count OUT NOCOPY NUMBER,
1526 x_msg_data OUT NOCOPY VARCHAR2,
1527 p_req_header_id IN NUMBER := FND_API.G_MISS_NUM,
1528 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
1529 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
1530 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
1531 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
1532 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
1533 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
1534 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1535 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
1536 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
1537 IS
1538 -- Standard Variables
1539 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH_INTERNAL_REQ';
1540 l_api_version CONSTANT NUMBER := 1.0;
1541
1542 -- Variables in FND log
1543 l_error_level number := FND_LOG.LEVEL_ERROR;
1544 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh_internal_req';
1545
1546 l_user_id NUMBER;
1547 l_login_id NUMBER;
1548
1549 l_updated_keys JTF_NUMBER_TABLE;
1550
1551 l_prev_status_CODE VARCHAR2(80);
1552 l_status_CODE VARCHAR2(80);
1553 l_repair_line_id NUMBER;
1554 l_wip_entity_id NUMBER;
1555 l_pr_header_id NUMBER;
1556 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
1557 l_rule_id NUMBER;
1558
1559 l_order_header_id NUMBER;
1560
1561 CURSOR c_get_defaulting_info(c_orchestration_id NUMBER) IS
1562 SELECT prev_status_code, status_code, repair_line_id, document_header_id
1563 FROM CSD_SO_ORCHESTRATION
1564 WHERE orchestration_id = c_orchestration_id;
1565
1566 CURSOR c_get_wip_entity_id(c_requisition_header_id NUMBER) IS
1567 SELECT wip_entity_id
1568 FROM po_requisition_lines_all
1569 WHERE requisition_header_id = c_requisition_header_id;
1570
1571 CURSOR c_get_order_header_id(c_requisition_header_id NUMBER,
1572 c_repair_line_id NUMBER) IS
1573 SELECT distinct
1574 order_header_id
1575 FROM csd_product_transactions
1576 WHERE req_header_id = c_requisition_header_id and
1577 repair_line_id = c_repair_line_id;
1578
1579 BEGIN
1580 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh_Internal_Req...');
1581
1582 -- Initialize API return status to success
1583 x_return_status := FND_API.G_RET_STS_SUCCESS;
1584
1585 -- Log the api name in the log file
1586 Debug('At the Beginning of Refresh_Internal_Req', l_mod_name, 1);
1587
1588 -- Standard call to check for call compatibility.
1589 IF NOT FND_API.Compatible_API_Call (l_api_version,
1590 p_api_version,
1591 l_api_name ,
1592 G_PKG_NAME)
1593 THEN
1594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1595 END IF;
1596
1597 -- Initialize message list if p_init_msg_list is set to TRUE.
1598 IF FND_API.to_Boolean(p_init_msg_list) THEN
1599 FND_MSG_PUB.initialize;
1600 END IF;
1601
1602 -- Api body starts
1603 l_user_id := NVL(fnd_global.USER_ID, -1);
1604 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
1605
1606 g_updated_ids := JTF_NUMBER_TABLE();
1607
1608 MERGE INTO CSD_SO_ORCHESTRATION soo USING (
1609 select distinct
1610 dra.repair_line_id,
1611 porh.requisition_header_id document_header_id,
1612 null document_line_id,
1613 porh.segment1 document_header_name,
1614 null document_line_name,
1615 'IR' document_type,
1616 null status_id,
1617 porh.authorization_status status_code
1618 from
1619 csd_repairs dra,
1620 po_requisition_headers_all porh,
1621 csd_product_transactions dpt
1622 where
1623 dra.repair_line_id = dpt.repair_line_id and
1624 dpt.action_type is null and
1625 dpt.req_header_id = porh.requisition_header_id and
1626 porh.requisition_header_id = decode(p_req_header_id,
1627 fnd_api.g_miss_num, porh.requisition_header_id,
1628 p_req_header_id) and
1629 dra.repair_line_id = decode(p_repair_line_id,
1630 fnd_api.g_miss_num, dra.repair_line_id,
1631 p_repair_line_id) and
1632 nvl(dpt.rcv_into_ou, fnd_api.g_miss_num) = -- operating unit
1633 decode(p_operating_unit_id,
1634 fnd_api.g_miss_num, nvl(dpt.rcv_into_ou, fnd_api.g_miss_num),
1635 p_operating_unit_id) and
1636 nvl(dpt.rcv_into_org, fnd_api.g_miss_num) = -- inventory org
1637 decode(p_inventory_org_id,
1638 fnd_api.g_miss_num, nvl(dpt.rcv_into_org, fnd_api.g_miss_num),
1639 p_inventory_org_id) and
1640 nvl(dra.resource_id, fnd_api.g_miss_num) =
1641 decode(p_repair_owner_id,
1642 fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
1643 p_repair_owner_id) and
1644 dra.flow_status_id =
1645 decode(p_ro_status_id,
1646 fnd_api.g_miss_num, dra.flow_status_id,
1647 p_ro_status_id) and
1648 dra.repair_type_id =
1649 decode(p_repair_type_id,
1650 fnd_api.g_miss_num, dra.repair_type_id,
1651 p_repair_type_id) and
1652 nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
1653 decode(p_ro_priority_code,
1654 fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
1655 p_ro_priority_code) and
1656 dra.creation_date >=
1657 decode(p_ro_age,
1658 fnd_api.g_miss_num, dra.creation_date,
1659 sysdate - p_ro_age) and
1660 nvl(dra.promise_date, G_MAX_DATE) <=
1661 decode(p_ro_due_date_age,
1662 fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
1663 sysdate + p_ro_due_date_age)) OLTP
1664 ON (soo.repair_line_id = OLTP.repair_line_id and
1665 soo.document_header_id = OLTP.document_header_id and
1666 -- nvl(soo.document_line_id, fnd_api.g_miss_num) =
1667 -- nvl(OLTP.document_line_id, fnd_api.g_miss_num) and
1668 soo.document_type = 'IR')
1669 WHEN MATCHED THEN
1670 UPDATE SET
1671 soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
1672 soo.status_code = OLTP.status_code,
1673 soo.object_version_number = soo.object_version_number + 1,
1674 soo.last_update_date = sysdate,
1675 soo.last_updated_by = l_user_id,
1676 soo.last_update_login = l_login_id
1677 WHEN NOT MATCHED THEN
1678 INSERT (ORCHESTRATION_ID,
1679 REPAIR_LINE_ID,
1680 OBJECT_VERSION_NUMBER,
1681 CREATED_BY,
1682 CREATION_DATE,
1683 LAST_UPDATED_BY,
1684 LAST_UPDATE_DATE,
1685 LAST_UPDATE_LOGIN,
1686 DOCUMENT_HEADER_ID,
1687 DOCUMENT_LINE_ID,
1688 DOCUMENT_HEADER_NAME,
1689 DOCUMENT_LINE_NAME,
1690 DOCUMENT_TYPE,
1691 STATUS_ID,
1692 STATUS_CODE,
1693 PREV_STATUS_ID,
1694 PREV_STATUS_CODE,
1695 ATTRIBUTE_CATEGORY,
1696 ATTRIBUTE1,
1697 ATTRIBUTE2,
1698 ATTRIBUTE3,
1699 ATTRIBUTE4,
1700 ATTRIBUTE5,
1701 ATTRIBUTE6,
1702 ATTRIBUTE7,
1703 ATTRIBUTE8,
1704 ATTRIBUTE9,
1705 ATTRIBUTE10,
1706 ATTRIBUTE11,
1707 ATTRIBUTE12,
1708 ATTRIBUTE13,
1709 ATTRIBUTE14,
1710 ATTRIBUTE15,
1711 PARAMN1)
1712 VALUES
1713 (csd_so_orchestration_s1.nextval,
1714 OLTP.repair_line_id,
1715 1,
1716 l_user_id,
1717 sysdate,
1718 l_user_id,
1719 sysdate,
1720 l_login_id,
1721 OLTP.document_header_id,
1722 null, -- document_line_id
1723 OLTP.document_header_name,
1724 null, -- document_line_name
1725 'IR',
1726 null, -- status_od
1727 OLTP.status_code,
1728 null, -- prev_status_id
1729 null, -- prev_status_code
1730 null, -- attribute_category
1731 null, -- attribute1
1732 null,
1733 null,
1734 null,
1735 null,
1736 null,
1737 null,
1738 null,
1739 null,
1740 null,
1741 null,
1742 null,
1743 null,
1744 null,
1745 null,
1746 null); -- paramn1
1747
1748 -- Save changes to CSD_SO_ORCHESTRATION table even
1749 -- if SOO rules engine fails.
1750 IF FND_API.To_Boolean( p_commit ) THEN
1751 COMMIT WORK;
1752 END IF;
1753
1754 SAVEPOINT REFRESH_INTERNAL_REQ;
1755
1756 l_updated_keys := get_updated_ids();
1757
1758 FOR i IN 1..l_updated_keys.COUNT
1759 LOOP
1760 OPEN c_get_defaulting_info(l_updated_keys(i));
1761 FETCH c_get_defaulting_info INTO l_prev_status_code, l_status_code, l_repair_line_id, l_pr_header_id;
1762 CLOSE c_get_defaulting_info;
1763
1764 OPEN c_get_wip_entity_id(l_pr_header_id);
1765 FETCH c_get_wip_entity_id INTO l_wip_entity_id;
1766 CLOSE c_get_wip_entity_id;
1767
1768 IF l_prev_status_code <> l_status_code THEN
1769 l_rule_input_rec.repair_line_id := l_repair_line_id;
1770 l_rule_input_rec.wip_entity_id := l_wip_entity_id;
1771 l_rule_input_rec.ir_prev_status_code := l_prev_status_code;
1772 l_rule_input_rec.ir_curr_status_code := l_status_code;
1773
1774 CSD_RULES_ENGINE_PVT.SOO_DEFAULTING(
1775 p_api_version_number => 1.0,
1776 p_commit => fnd_api.g_false,
1777 p_validation_level => p_validation_level,
1778 p_event_type => 'IR_STATUS',
1779 p_rule_input_rec => l_rule_input_rec,
1780 x_rule_id => l_rule_id,
1781 x_return_status => x_return_status,
1782 x_msg_count => x_msg_count,
1783 x_msg_data => x_msg_data);
1784
1785 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1786 Debug('Trying to default SOO rule for Internal Req status event failed.',l_mod_name,4);
1787 RAISE FND_API.G_EXC_ERROR;
1788 END IF;
1789
1790 OPEN c_get_order_header_id(l_pr_header_id, l_repair_line_id);
1791 LOOP
1792 FETCH c_get_order_header_id INTO l_order_header_id;
1793 EXIT WHEN c_get_order_header_id%NOTFOUND;
1794
1795 update_int_order_header_id(l_order_header_id);
1796 END LOOP;
1797 CLOSE c_get_order_header_id;
1798 END IF;
1799 END LOOP;
1800
1801 -- Standard check of p_commit.
1802 IF FND_API.To_Boolean( p_commit ) THEN
1803 COMMIT WORK;
1804 END IF;
1805
1806 x_msg_count := fnd_msg_pub.count_msg;
1807
1808 EXCEPTION
1809 WHEN FND_API.G_EXC_ERROR THEN
1810 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
1811 ROLLBACK TO REFRESH_INTERNAL_REQ;
1812 x_return_status := FND_API.G_RET_STS_ERROR ;
1813 x_msg_count := fnd_msg_pub.count_msg;
1814 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1815 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
1816 IF ( l_error_level >= G_debug_level) THEN
1817 fnd_message.set_name('CSD','CSD_SQL_ERROR');
1818 fnd_message.set_token('SQLERRM',SQLERRM);
1819 fnd_message.set_token('SQLCODE',SQLCODE);
1820 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
1821 END If;
1822 ROLLBACK TO REFRESH_INTERNAL_REQ;
1823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1824 x_msg_count := fnd_msg_pub.count_msg;
1825 WHEN OTHERS THEN
1826 Debug('In OTHERS Exception',l_mod_name,4);
1827 IF ( l_error_level >= G_debug_level) THEN
1828 fnd_message.set_name('CSD','CSD_SQL_ERROR');
1829 fnd_message.set_token('SQLERRM',SQLERRM);
1830 fnd_message.set_token('SQLCODE',SQLCODE);
1831 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
1832 END If;
1833 ROLLBACK TO REFRESH_INTERNAL_REQ;
1834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1835 IF FND_MSG_PUB.Check_Msg_Level
1836 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1837 THEN
1838 FND_MSG_PUB.Add_Exc_Msg
1839 (G_PKG_NAME ,
1840 l_api_name );
1841 END IF;
1842 x_msg_count := fnd_msg_pub.count_msg;
1843
1844 END Refresh_Internal_Req;
1845
1846 PROCEDURE Refresh_Int_Sales_Order
1847 (p_api_version IN NUMBER,
1848 p_commit IN VARCHAR2,
1849 p_init_msg_list IN VARCHAR2,
1850 p_validation_level IN NUMBER,
1851 x_return_status OUT NOCOPY VARCHAR2,
1852 x_msg_count OUT NOCOPY NUMBER,
1853 x_msg_data OUT NOCOPY VARCHAR2,
1854 p_order_line_id IN NUMBER := FND_API.G_MISS_NUM,
1855 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
1856 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
1857 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
1858 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
1859 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
1860 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
1861 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1862 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
1863 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
1864 IS
1865 -- Standard Variables
1866 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH_INT_SALES_ORDER';
1867 l_api_version CONSTANT NUMBER := 1.0;
1868
1869 -- Variables in FND log
1870 l_error_level number := FND_LOG.LEVEL_ERROR;
1871 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh_int_sales_order';
1872
1873 l_user_id NUMBER;
1874 l_login_id NUMBER;
1875
1876 l_updated_keys JTF_NUMBER_TABLE;
1877
1878 l_prev_status_CODE VARCHAR2(80);
1879 l_status_CODE VARCHAR2(80);
1880 l_repair_line_id NUMBER;
1881 l_order_header_id NUMBER;
1882 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
1883 l_rule_id NUMBER;
1884
1885 CURSOR c_get_defaulting_info(c_orchestration_id NUMBER) IS
1886 SELECT prev_status_code, status_code, repair_line_id, document_header_id
1887 FROM CSD_SO_ORCHESTRATION
1888 WHERE orchestration_id = c_orchestration_id;
1889
1890 BEGIN
1891 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh_Int_Sales_Order...');
1892
1893 -- Initialize API return status to success
1894 x_return_status := FND_API.G_RET_STS_SUCCESS;
1895
1896 -- Log the api name in the log file
1897 Debug('At the Beginning of Refresh_Int_Sales_Order', l_mod_name, 1);
1898
1899 -- Standard call to check for call compatibility.
1900 IF NOT FND_API.Compatible_API_Call (l_api_version,
1901 p_api_version,
1902 l_api_name ,
1903 G_PKG_NAME)
1904 THEN
1905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1906 END IF;
1907
1908 -- Initialize message list if p_init_msg_list is set to TRUE.
1909 IF FND_API.to_Boolean(p_init_msg_list) THEN
1910 FND_MSG_PUB.initialize;
1911 END IF;
1912
1913 -- Api body starts
1914 l_user_id := NVL(fnd_global.USER_ID, -1);
1915 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
1916
1917 g_updated_ids := JTF_NUMBER_TABLE();
1918
1919 MERGE INTO CSD_SO_ORCHESTRATION soo USING (
1920 select distinct
1921 dra.repair_line_id,
1922 oel.header_id document_header_id,
1923 oel.line_id document_line_id,
1924 oeh.order_number document_header_name,
1925 oel.line_number document_line_name,
1926 'INTSO' document_type,
1927 null status_id,
1928 lv.lookup_code status_code
1929 from oe_order_lines_all oel,
1930 oe_order_headers_all oeh,
1931 csd_product_transactions dpt,
1932 csd_repairs dra,
1933 fnd_lookup_values lv
1934 where oeh.header_id = oel.header_id
1935 and dpt.order_line_id = oel.line_id
1936 and dpt.repair_line_id = dra.repair_line_id
1937 and dpt.action_type is null
1938 and lv.lookup_type = 'LINE_FLOW_STATUS'
1939 and lv.meaning = OE_LINE_STATUS_PUB.get_line_status(p_line_id => oel.line_id,
1940 p_flow_status_code => oel.flow_status_code)
1941 and
1942 oel.line_id = decode(p_order_line_id,
1943 fnd_api.g_miss_num, oel.line_id,
1944 p_order_line_id) and
1945 dra.repair_line_id = decode(p_repair_line_id,
1946 fnd_api.g_miss_num, dra.repair_line_id,
1947 p_repair_line_id) and
1948 nvl(dpt.ship_from_ou, fnd_api.g_miss_num) = -- operating unit
1949 decode(p_operating_unit_id,
1950 fnd_api.g_miss_num, nvl(dpt.ship_from_ou, fnd_api.g_miss_num),
1951 p_operating_unit_id) and
1952 nvl(dpt.ship_from_org, fnd_api.g_miss_num) = -- inventory org
1953 decode(p_inventory_org_id,
1954 fnd_api.g_miss_num, nvl(dpt.ship_from_org, fnd_api.g_miss_num),
1955 p_inventory_org_id) and
1956 nvl(dra.resource_id, fnd_api.g_miss_num) =
1957 decode(p_repair_owner_id,
1958 fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
1959 p_repair_owner_id) and
1960 dra.flow_status_id =
1961 decode(p_ro_status_id,
1962 fnd_api.g_miss_num, dra.flow_status_id,
1963 p_ro_status_id) and
1964 dra.repair_type_id =
1965 decode(p_repair_type_id,
1966 fnd_api.g_miss_num, dra.repair_type_id,
1967 p_repair_type_id) and
1968 nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
1969 decode(p_ro_priority_code,
1970 fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
1971 p_ro_priority_code) and
1972 dra.creation_date >=
1973 decode(p_ro_age,
1974 fnd_api.g_miss_num, dra.creation_date,
1975 sysdate - p_ro_age) and
1976 nvl(dra.promise_date, G_MAX_DATE) <=
1977 decode(p_ro_due_date_age,
1978 fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
1979 sysdate + p_ro_due_date_age)) OLTP
1980 ON (soo.repair_line_id = OLTP.repair_line_id and
1981 soo.document_line_id = OLTP.document_line_id and
1982 soo.document_type = 'INTSO')
1983 WHEN MATCHED THEN
1984 UPDATE SET
1985 soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
1986 soo.status_code = OLTP.status_code,
1987 soo.object_version_number = soo.object_version_number + 1,
1988 soo.last_update_date = sysdate,
1989 soo.last_updated_by = l_user_id,
1990 soo.last_update_login = l_login_id
1991 WHEN NOT MATCHED THEN
1992 INSERT (ORCHESTRATION_ID,
1993 REPAIR_LINE_ID,
1994 OBJECT_VERSION_NUMBER,
1995 CREATED_BY,
1996 CREATION_DATE,
1997 LAST_UPDATED_BY,
1998 LAST_UPDATE_DATE,
1999 LAST_UPDATE_LOGIN,
2000 DOCUMENT_HEADER_ID,
2001 DOCUMENT_LINE_ID,
2002 DOCUMENT_HEADER_NAME,
2003 DOCUMENT_LINE_NAME,
2004 DOCUMENT_TYPE,
2005 STATUS_ID,
2006 STATUS_CODE,
2007 PREV_STATUS_ID,
2008 PREV_STATUS_CODE,
2009 ATTRIBUTE_CATEGORY,
2010 ATTRIBUTE1,
2011 ATTRIBUTE2,
2012 ATTRIBUTE3,
2013 ATTRIBUTE4,
2014 ATTRIBUTE5,
2015 ATTRIBUTE6,
2016 ATTRIBUTE7,
2017 ATTRIBUTE8,
2018 ATTRIBUTE9,
2019 ATTRIBUTE10,
2020 ATTRIBUTE11,
2021 ATTRIBUTE12,
2022 ATTRIBUTE13,
2023 ATTRIBUTE14,
2024 ATTRIBUTE15,
2025 PARAMN1)
2026 VALUES
2027 (csd_so_orchestration_s1.nextval,
2028 OLTP.repair_line_id,
2029 1,
2030 l_user_id,
2031 sysdate,
2032 l_user_id,
2033 sysdate,
2034 l_login_id,
2035 OLTP.document_header_id,
2036 OLTP.document_line_id,
2037 OLTP.document_header_name,
2038 OLTP.document_line_name,
2039 'INTSO',
2040 null, -- status_id
2041 OLTP.status_code,
2042 null, -- prev_status_id
2043 null, -- prev_status_code
2044 null, -- attribute_category
2045 null, -- attribute1
2046 null,
2047 null,
2048 null,
2049 null,
2050 null,
2051 null,
2052 null,
2053 null,
2054 null,
2055 null,
2056 null,
2057 null,
2058 null,
2059 null,
2060 null); -- paramn1
2061
2062 -- Save changes to CSD_SO_ORCHESTRATION table even
2063 -- if SOO rules engine fails.
2064 IF FND_API.To_Boolean( p_commit ) THEN
2065 COMMIT WORK;
2066 END IF;
2067
2068 SAVEPOINT REFRESH_ISO;
2069 l_updated_keys := get_updated_ids();
2070
2071 FOR i IN 1..l_updated_keys.COUNT
2072 LOOP
2073 OPEN c_get_defaulting_info(l_updated_keys(i));
2074 FETCH c_get_defaulting_info INTO l_prev_status_code,
2075 l_status_code,
2076 l_repair_line_id,
2077 l_order_header_id;
2078 CLOSE c_get_defaulting_info;
2079
2080 IF l_prev_status_code <> l_status_code THEN
2081 l_rule_input_rec.repair_line_id := l_repair_line_id;
2082 l_rule_input_rec.int_so_prev_status_code := l_prev_status_code;
2083 l_rule_input_rec.int_so_curr_status_code := l_status_code;
2084
2085 CSD_RULES_ENGINE_PVT.SOO_DEFAULTING(
2086 p_api_version_number => 1.0,
2087 p_commit => fnd_api.g_false,
2088 p_validation_level => p_validation_level,
2089 p_event_type => 'INTERNAL_SALES_STATUS',
2090 p_rule_input_rec => l_rule_input_rec,
2091 x_rule_id => l_rule_id,
2092 x_return_status => x_return_status,
2093 x_msg_count => x_msg_count,
2094 x_msg_data => x_msg_data);
2095
2096 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2097 Debug('Trying to default SOO rule for Internal Sales Order status event failed.',l_mod_name,4);
2098 RAISE FND_API.G_EXC_ERROR;
2099 END IF;
2100
2101 update_int_order_header_id(l_order_header_id);
2102 END IF;
2103 END LOOP;
2104
2105 -- Standard check of p_commit.
2106 IF FND_API.To_Boolean( p_commit ) THEN
2107 COMMIT WORK;
2108 END IF;
2109
2110 x_msg_count := fnd_msg_pub.count_msg;
2111 EXCEPTION
2112 WHEN FND_API.G_EXC_ERROR THEN
2113 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
2114 ROLLBACK TO REFRESH_ISO;
2115 x_return_status := FND_API.G_RET_STS_ERROR ;
2116 x_msg_count := fnd_msg_pub.count_msg;
2117 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2118 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
2119 IF ( l_error_level >= G_debug_level) THEN
2120 fnd_message.set_name('CSD','CSD_SQL_ERROR');
2121 fnd_message.set_token('SQLERRM',SQLERRM);
2122 fnd_message.set_token('SQLCODE',SQLCODE);
2123 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
2124 END If;
2125 ROLLBACK TO REFRESH_ISO;
2126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2127 x_msg_count := fnd_msg_pub.count_msg;
2128 WHEN OTHERS THEN
2129 Debug('In OTHERS Exception',l_mod_name,4);
2130 IF ( l_error_level >= G_debug_level) THEN
2131 fnd_message.set_name('CSD','CSD_SQL_ERROR');
2132 fnd_message.set_token('SQLERRM',SQLERRM);
2133 fnd_message.set_token('SQLCODE',SQLCODE);
2134 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
2135 END If;
2136 ROLLBACK TO REFRESH_ISO;
2137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2138 IF FND_MSG_PUB.Check_Msg_Level
2139 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2140 THEN
2141 FND_MSG_PUB.Add_Exc_Msg
2142 (G_PKG_NAME ,
2143 l_api_name );
2144 END IF;
2145 x_msg_count := fnd_msg_pub.count_msg;
2146
2147
2148 END Refresh_Int_Sales_Order;
2149
2150 PROCEDURE Refresh_RMA
2151 (p_api_version IN NUMBER,
2152 p_commit IN VARCHAR2,
2153 p_init_msg_list IN VARCHAR2,
2154 p_validation_level IN NUMBER,
2155 x_return_status OUT NOCOPY VARCHAR2,
2156 x_msg_count OUT NOCOPY NUMBER,
2157 x_msg_data OUT NOCOPY VARCHAR2,
2158 p_order_line_id IN NUMBER := FND_API.G_MISS_NUM,
2159 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
2160 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
2161 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
2162 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
2163 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
2164 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
2165 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
2166 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
2167 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
2168 IS
2169 -- Standard Variables
2170 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH_RMA';
2171 l_api_version CONSTANT NUMBER := 1.0;
2172
2173 -- Variables in FND log
2174 l_error_level number := FND_LOG.LEVEL_ERROR;
2175 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh_rma';
2176
2177 l_user_id NUMBER;
2178 l_login_id NUMBER;
2179
2180 l_updated_keys JTF_NUMBER_TABLE;
2181
2182 l_prev_status_CODE VARCHAR2(80);
2183 l_status_CODE VARCHAR2(80);
2184 l_repair_line_id NUMBER;
2185 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
2186 l_rule_id NUMBER;
2187
2188 CURSOR c_get_defaulting_info(c_orchestration_id NUMBER) IS
2189 SELECT prev_status_code, status_code, repair_line_id
2190 FROM CSD_SO_ORCHESTRATION
2191 WHERE orchestration_id = c_orchestration_id;
2192
2193 BEGIN
2194 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh_RMA...');
2195
2196 -- Initialize API return status to success
2197 x_return_status := FND_API.G_RET_STS_SUCCESS;
2198
2199 -- Log the api name in the log file
2200 Debug('At the Beginning of Refresh_RMA', l_mod_name, 1);
2201
2202 -- Standard call to check for call compatibility.
2203 IF NOT FND_API.Compatible_API_Call (l_api_version,
2204 p_api_version,
2205 l_api_name ,
2206 G_PKG_NAME)
2207 THEN
2208 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2209 END IF;
2210
2211 -- Initialize message list if p_init_msg_list is set to TRUE.
2212 IF FND_API.to_Boolean(p_init_msg_list) THEN
2213 FND_MSG_PUB.initialize;
2214 END IF;
2215
2216 -- Api body starts
2217 l_user_id := NVL(fnd_global.USER_ID, -1);
2218 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
2219
2220 g_updated_ids := JTF_NUMBER_TABLE();
2221
2222 MERGE INTO CSD_SO_ORCHESTRATION soo USING (
2223 select
2224 dra.repair_line_id,
2225 oel.header_id document_header_id,
2226 oel.line_id document_line_id,
2227 oeh.order_number document_header_name,
2228 oel.line_number document_line_name,
2229 'RMA' document_type,
2230 null status_id,
2231 lv.lookup_code status_code
2232 from oe_order_lines_all oel,
2233 oe_order_headers_all oeh,
2234 csd_product_transactions dpt,
2235 cs_estimate_details edt,
2236 csd_repairs dra,
2237 fnd_lookup_values lv
2238 where oeh.header_id = oel.header_id
2239 and dpt.estimate_detail_id = edt.estimate_detail_id
2240 and edt.order_line_id = oel.line_id
2241 and dpt.repair_line_id = dra.repair_line_id
2242 and dpt.action_type = 'RMA'
2243 and lv.lookup_type = 'LINE_FLOW_STATUS'
2244 and lv.meaning = OE_LINE_STATUS_PUB.get_line_status(p_line_id => oel.line_id,
2245 p_flow_status_code => oel.flow_status_code)
2246 and
2247 oel.line_id = decode(p_order_line_id,
2248 fnd_api.g_miss_num, oel.line_id,
2249 p_order_line_id) and
2250 dra.repair_line_id = decode(p_repair_line_id,
2251 fnd_api.g_miss_num, dra.repair_line_id,
2252 p_repair_line_id) and
2253 nvl(oel.org_id, fnd_api.g_miss_num) = -- operating unit
2254 decode(p_operating_unit_id,
2255 fnd_api.g_miss_num, nvl(oel.org_id, fnd_api.g_miss_num),
2256 p_operating_unit_id) and
2257 nvl(edt.transaction_inventory_org, fnd_api.g_miss_num) = -- inventory org
2258 decode(p_inventory_org_id,
2259 fnd_api.g_miss_num, nvl(edt.transaction_inventory_org, fnd_api.g_miss_num),
2260 p_inventory_org_id) and
2261 nvl(dra.resource_id, fnd_api.g_miss_num) =
2262 decode(p_repair_owner_id,
2263 fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
2264 p_repair_owner_id) and
2265 dra.flow_status_id =
2266 decode(p_ro_status_id,
2267 fnd_api.g_miss_num, dra.flow_status_id,
2268 p_ro_status_id) and
2269 dra.repair_type_id =
2270 decode(p_repair_type_id,
2271 fnd_api.g_miss_num, dra.repair_type_id,
2272 p_repair_type_id) and
2273 nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
2274 decode(p_ro_priority_code,
2275 fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
2276 p_ro_priority_code) and
2277 dra.creation_date >=
2278 decode(p_ro_age,
2279 fnd_api.g_miss_num, dra.creation_date,
2280 sysdate - p_ro_age) and
2281 nvl(dra.promise_date, G_MAX_DATE) <=
2282 decode(p_ro_due_date_age,
2283 fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
2284 sysdate + p_ro_due_date_age)) OLTP
2285 ON (soo.repair_line_id = OLTP.repair_line_id and
2286 soo.document_line_id = OLTP.document_line_id and
2287 soo.document_type = 'RMA')
2288 WHEN MATCHED THEN
2289 UPDATE SET
2290 soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
2291 soo.status_code = OLTP.status_code,
2292 soo.object_version_number = soo.object_version_number + 1,
2293 soo.last_update_date = sysdate,
2294 soo.last_updated_by = l_user_id,
2295 soo.last_update_login = l_login_id
2296 WHEN NOT MATCHED THEN
2297 INSERT (ORCHESTRATION_ID,
2298 REPAIR_LINE_ID,
2299 OBJECT_VERSION_NUMBER,
2300 CREATED_BY,
2301 CREATION_DATE,
2302 LAST_UPDATED_BY,
2303 LAST_UPDATE_DATE,
2304 LAST_UPDATE_LOGIN,
2305 DOCUMENT_HEADER_ID,
2306 DOCUMENT_LINE_ID,
2307 DOCUMENT_HEADER_NAME,
2308 DOCUMENT_LINE_NAME,
2309 DOCUMENT_TYPE,
2310 STATUS_ID,
2311 STATUS_CODE,
2312 PREV_STATUS_ID,
2313 PREV_STATUS_CODE,
2314 ATTRIBUTE_CATEGORY,
2315 ATTRIBUTE1,
2316 ATTRIBUTE2,
2317 ATTRIBUTE3,
2318 ATTRIBUTE4,
2319 ATTRIBUTE5,
2320 ATTRIBUTE6,
2321 ATTRIBUTE7,
2322 ATTRIBUTE8,
2323 ATTRIBUTE9,
2324 ATTRIBUTE10,
2325 ATTRIBUTE11,
2326 ATTRIBUTE12,
2327 ATTRIBUTE13,
2328 ATTRIBUTE14,
2329 ATTRIBUTE15,
2330 PARAMN1)
2331 VALUES
2332 (csd_so_orchestration_s1.nextval,
2333 OLTP.repair_line_id,
2334 1,
2335 l_user_id,
2336 sysdate,
2337 l_user_id,
2338 sysdate,
2339 l_login_id,
2340 OLTP.document_header_id,
2341 OLTP.document_line_id,
2342 OLTP.document_header_name,
2343 OLTP.document_line_name,
2344 'RMA',
2345 null, -- status_id
2346 OLTP.status_code,
2347 null, -- prev_status_id
2348 null, -- prev_status_code
2349 null, -- attribute_category
2350 null, -- attribute1
2351 null,
2352 null,
2353 null,
2354 null,
2355 null,
2356 null,
2357 null,
2358 null,
2359 null,
2360 null,
2361 null,
2362 null,
2363 null,
2364 null,
2365 null); -- paramn1
2366
2367 -- Save changes to CSD_SO_ORCHESTRATION table even
2368 -- if SOO rules engine fails.
2369 IF FND_API.To_Boolean( p_commit ) THEN
2370 COMMIT WORK;
2371 END IF;
2372
2373 SAVEPOINT REFRESH_RMA;
2374
2375 l_updated_keys := get_updated_ids();
2376
2377 FOR i IN 1..l_updated_keys.COUNT
2378 LOOP
2379 OPEN c_get_defaulting_info(l_updated_keys(i));
2380 FETCH c_get_defaulting_info INTO l_prev_status_code, l_status_code, l_repair_line_id;
2381 CLOSE c_get_defaulting_info;
2382
2383 IF l_prev_status_code <> l_status_code THEN
2384 l_rule_input_rec.repair_line_id := l_repair_line_id;
2385 l_rule_input_rec.rma_prev_status_code := l_prev_status_code;
2386 l_rule_input_rec.rma_curr_status_code := l_status_code;
2387
2388 CSD_RULES_ENGINE_PVT.SOO_DEFAULTING(
2389 p_api_version_number => 1.0,
2390 p_commit => fnd_api.g_false,
2391 p_validation_level => p_validation_level,
2392 p_event_type => 'RMA_STATUS',
2393 p_rule_input_rec => l_rule_input_rec,
2394 x_rule_id => l_rule_id,
2395 x_return_status => x_return_status,
2396 x_msg_count => x_msg_count,
2397 x_msg_data => x_msg_data);
2398
2399 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2400 Debug('Trying to default SOO rule for RMA status event failed.',l_mod_name,4);
2401 RAISE FND_API.G_EXC_ERROR;
2402 END IF;
2403
2404 update_rma_ro_line_id(l_repair_line_id);
2405 END IF;
2406 END LOOP;
2407
2408 -- Standard check of p_commit.
2409 IF FND_API.To_Boolean( p_commit ) THEN
2410 COMMIT WORK;
2411 END IF;
2412
2413 x_msg_count := fnd_msg_pub.count_msg;
2414 EXCEPTION
2415 WHEN FND_API.G_EXC_ERROR THEN
2416 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
2417 ROLLBACK TO REFRESH_RMA;
2418 x_return_status := FND_API.G_RET_STS_ERROR ;
2419 x_msg_count := fnd_msg_pub.count_msg;
2420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2421 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
2422 IF ( l_error_level >= G_debug_level) THEN
2423 fnd_message.set_name('CSD','CSD_SQL_ERROR');
2424 fnd_message.set_token('SQLERRM',SQLERRM);
2425 fnd_message.set_token('SQLCODE',SQLCODE);
2426 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
2427 END If;
2428 ROLLBACK TO REFRESH_RMA;
2429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2430 x_msg_count := fnd_msg_pub.count_msg;
2431 WHEN OTHERS THEN
2432 Debug('In OTHERS Exception',l_mod_name,4);
2433 IF ( l_error_level >= G_debug_level) THEN
2434 fnd_message.set_name('CSD','CSD_SQL_ERROR');
2435 fnd_message.set_token('SQLERRM',SQLERRM);
2436 fnd_message.set_token('SQLCODE',SQLCODE);
2437 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
2438 END If;
2439 ROLLBACK TO REFRESH_RMA;
2440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2441 IF FND_MSG_PUB.Check_Msg_Level
2442 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2443 THEN
2444 FND_MSG_PUB.Add_Exc_Msg
2445 (G_PKG_NAME ,
2446 l_api_name );
2447 END IF;
2448 x_msg_count := fnd_msg_pub.count_msg;
2449
2450 END Refresh_RMA;
2451
2452 PROCEDURE Refresh_RMA_Third_Party
2453 (p_api_version IN NUMBER,
2454 p_commit IN VARCHAR2,
2455 p_init_msg_list IN VARCHAR2,
2456 p_validation_level IN NUMBER,
2457 x_return_status OUT NOCOPY VARCHAR2,
2458 x_msg_count OUT NOCOPY NUMBER,
2459 x_msg_data OUT NOCOPY VARCHAR2,
2460 p_order_line_id IN NUMBER := FND_API.G_MISS_NUM,
2461 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
2462 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
2463 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
2464 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
2465 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
2466 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
2467 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
2468 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
2469 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
2470 IS
2471 -- Standard Variables
2472 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH_RMA_THIRD_PARTY';
2473 l_api_version CONSTANT NUMBER := 1.0;
2474
2475 -- Variables in FND log
2476 l_error_level number := FND_LOG.LEVEL_ERROR;
2477 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh_rma_third_party';
2478
2479 l_user_id NUMBER;
2480 l_login_id NUMBER;
2481
2482 l_updated_keys JTF_NUMBER_TABLE;
2483
2484 l_prev_status_CODE VARCHAR2(80);
2485 l_status_CODE VARCHAR2(80);
2486 l_repair_line_id NUMBER;
2487 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
2488 l_rule_id NUMBER;
2489
2490 CURSOR c_get_defaulting_info(c_orchestration_id NUMBER) IS
2491 SELECT prev_status_code, status_code, repair_line_id
2492 FROM CSD_SO_ORCHESTRATION
2493 WHERE orchestration_id = c_orchestration_id;
2494
2495 BEGIN
2496 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh_RMA_Third_Party...');
2497
2498 -- Initialize API return status to success
2499 x_return_status := FND_API.G_RET_STS_SUCCESS;
2500
2501 -- Log the api name in the log file
2502 Debug('At the Beginning of Refresh_RMA_Third_Party', l_mod_name, 1);
2503
2504 -- Standard call to check for call compatibility.
2505 IF NOT FND_API.Compatible_API_Call (l_api_version,
2506 p_api_version,
2507 l_api_name ,
2508 G_PKG_NAME)
2509 THEN
2510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2511 END IF;
2512
2513 -- Initialize message list if p_init_msg_list is set to TRUE.
2514 IF FND_API.to_Boolean(p_init_msg_list) THEN
2515 FND_MSG_PUB.initialize;
2516 END IF;
2517
2518 -- Api body starts
2519 l_user_id := NVL(fnd_global.USER_ID, -1);
2520 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
2521
2522 g_updated_ids := JTF_NUMBER_TABLE();
2523
2524 MERGE INTO CSD_SO_ORCHESTRATION soo USING (
2525 select
2526 dra.repair_line_id,
2527 oel.header_id document_header_id,
2528 oel.line_id document_line_id,
2529 oeh.order_number document_header_name,
2530 oel.line_number document_line_name,
2531 'RMA_TP' document_type,
2532 null status_id,
2533 lv.lookup_code status_code
2534 from oe_order_lines_all oel,
2535 oe_order_headers_all oeh,
2536 csd_product_transactions dpt,
2537 cs_estimate_details edt,
2538 csd_repairs dra,
2539 fnd_lookup_values lv
2540 where oeh.header_id = oel.header_id
2541 and dpt.estimate_detail_id = edt.estimate_detail_id
2542 and edt.order_line_id = oel.line_id
2543 and dpt.repair_line_id = dra.repair_line_id
2544 and dpt.action_type = 'RMA_THIRD_PTY'
2545 and lv.lookup_type = 'LINE_FLOW_STATUS'
2546 and lv.meaning = OE_LINE_STATUS_PUB.get_line_status(p_line_id => oel.line_id,
2547 p_flow_status_code => oel.flow_status_code)
2548 and
2549 oel.line_id = decode(p_order_line_id,
2550 fnd_api.g_miss_num, oel.line_id,
2551 p_order_line_id) and
2552 dra.repair_line_id = decode(p_repair_line_id,
2553 fnd_api.g_miss_num, dra.repair_line_id,
2554 p_repair_line_id) and
2555 nvl(oel.org_id, fnd_api.g_miss_num) = -- operating unit
2556 decode(p_operating_unit_id,
2557 fnd_api.g_miss_num, nvl(oel.org_id, fnd_api.g_miss_num),
2558 p_operating_unit_id) and
2559 nvl(edt.transaction_inventory_org, fnd_api.g_miss_num) = -- inventory org
2560 decode(p_inventory_org_id,
2561 fnd_api.g_miss_num, nvl(edt.transaction_inventory_org, fnd_api.g_miss_num),
2562 p_inventory_org_id) and
2563 nvl(dra.resource_id, fnd_api.g_miss_num) =
2564 decode(p_repair_owner_id,
2565 fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
2566 p_repair_owner_id) and
2567 dra.flow_status_id =
2568 decode(p_ro_status_id,
2569 fnd_api.g_miss_num, dra.flow_status_id,
2570 p_ro_status_id) and
2571 dra.repair_type_id =
2572 decode(p_repair_type_id,
2573 fnd_api.g_miss_num, dra.repair_type_id,
2574 p_repair_type_id) and
2575 nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
2576 decode(p_ro_priority_code,
2577 fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
2578 p_ro_priority_code) and
2579 dra.creation_date >=
2580 decode(p_ro_age,
2581 fnd_api.g_miss_num, dra.creation_date,
2582 sysdate - p_ro_age) and
2583 nvl(dra.promise_date, G_MAX_DATE) <=
2584 decode(p_ro_due_date_age,
2585 fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
2586 sysdate + p_ro_due_date_age)) OLTP
2587 ON (soo.repair_line_id = OLTP.repair_line_id and
2588 -- soo.document_header_id = OLTP.document_header_id and
2589 soo.document_line_id = OLTP.document_line_id and
2590 soo.document_type = 'RMA_TP')
2591 WHEN MATCHED THEN
2592 UPDATE SET
2593 soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
2594 soo.status_code = OLTP.status_code,
2595 soo.object_version_number = soo.object_version_number + 1,
2596 soo.last_update_date = sysdate,
2597 soo.last_updated_by = l_user_id,
2598 soo.last_update_login = l_login_id
2599 WHEN NOT MATCHED THEN
2600 INSERT (ORCHESTRATION_ID,
2601 REPAIR_LINE_ID,
2602 OBJECT_VERSION_NUMBER,
2603 CREATED_BY,
2604 CREATION_DATE,
2605 LAST_UPDATED_BY,
2606 LAST_UPDATE_DATE,
2607 LAST_UPDATE_LOGIN,
2608 DOCUMENT_HEADER_ID,
2609 DOCUMENT_LINE_ID,
2610 DOCUMENT_HEADER_NAME,
2611 DOCUMENT_LINE_NAME,
2612 DOCUMENT_TYPE,
2613 STATUS_ID,
2614 STATUS_CODE,
2615 PREV_STATUS_ID,
2616 PREV_STATUS_CODE,
2617 ATTRIBUTE_CATEGORY,
2618 ATTRIBUTE1,
2619 ATTRIBUTE2,
2620 ATTRIBUTE3,
2621 ATTRIBUTE4,
2622 ATTRIBUTE5,
2623 ATTRIBUTE6,
2624 ATTRIBUTE7,
2625 ATTRIBUTE8,
2626 ATTRIBUTE9,
2627 ATTRIBUTE10,
2628 ATTRIBUTE11,
2629 ATTRIBUTE12,
2630 ATTRIBUTE13,
2631 ATTRIBUTE14,
2632 ATTRIBUTE15,
2633 PARAMN1)
2634 VALUES
2635 (csd_so_orchestration_s1.nextval,
2636 OLTP.repair_line_id,
2637 1,
2638 l_user_id,
2639 sysdate,
2640 l_user_id,
2641 sysdate,
2642 l_login_id,
2643 OLTP.document_header_id,
2644 OLTP.document_line_id,
2645 OLTP.document_header_name,
2646 OLTP.document_line_name,
2647 'RMA_TP',
2648 null, -- status_id
2649 OLTP.status_code,
2650 null, -- prev_status_id
2651 null, -- prev_status_code
2652 null, -- attribute_category
2653 null, -- attribute1
2654 null,
2655 null,
2656 null,
2657 null,
2658 null,
2659 null,
2660 null,
2661 null,
2662 null,
2663 null,
2664 null,
2665 null,
2666 null,
2667 null,
2668 null); -- paramn1
2669
2670 -- Save changes to CSD_SO_ORCHESTRATION table even
2671 -- if SOO rules engine fails.
2672 IF FND_API.To_Boolean( p_commit ) THEN
2673 COMMIT WORK;
2674 END IF;
2675
2676 SAVEPOINT REFRESH_RMA_TP;
2677
2678 l_updated_keys := get_updated_ids();
2679
2680 FOR i IN 1..l_updated_keys.COUNT
2681 LOOP
2682 OPEN c_get_defaulting_info(l_updated_keys(i));
2683 FETCH c_get_defaulting_info INTO l_prev_status_code, l_status_code, l_repair_line_id;
2684 CLOSE c_get_defaulting_info;
2685
2686 IF l_prev_status_code <> l_status_code THEN
2687 l_rule_input_rec.repair_line_id := l_repair_line_id;
2688 l_rule_input_rec.rma_tp_prev_status_code := l_prev_status_code;
2689 l_rule_input_rec.rma_tp_curr_status_code := l_status_code;
2690
2691 CSD_RULES_ENGINE_PVT.SOO_DEFAULTING(
2692 p_api_version_number => 1.0,
2693 p_commit => fnd_api.g_false,
2694 p_validation_level => p_validation_level,
2695 p_event_type => 'RMA_TP_STATUS',
2696 p_rule_input_rec => l_rule_input_rec,
2697 x_rule_id => l_rule_id,
2698 x_return_status => x_return_status,
2699 x_msg_count => x_msg_count,
2700 x_msg_data => x_msg_data);
2701
2702 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2703 Debug('Trying to default SOO rule for RMA Third Party status event failed.',l_mod_name,4);
2704 RAISE FND_API.G_EXC_ERROR;
2705 END IF;
2706
2707 update_rma_ro_line_id(l_repair_line_id);
2708 END IF;
2709 END LOOP;
2710
2711 -- Standard check of p_commit.
2712 IF FND_API.To_Boolean( p_commit ) THEN
2713 COMMIT WORK;
2714 END IF;
2715
2716 x_msg_count := fnd_msg_pub.count_msg;
2717
2718 EXCEPTION
2719 WHEN FND_API.G_EXC_ERROR THEN
2720 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
2721 ROLLBACK TO REFRESH_RMA_TP;
2722 x_return_status := FND_API.G_RET_STS_ERROR ;
2723 x_msg_count := fnd_msg_pub.count_msg;
2724 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2725 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
2726 IF ( l_error_level >= G_debug_level) THEN
2727 fnd_message.set_name('CSD','CSD_SQL_ERROR');
2728 fnd_message.set_token('SQLERRM',SQLERRM);
2729 fnd_message.set_token('SQLCODE',SQLCODE);
2730 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
2731 END If;
2732 ROLLBACK TO REFRESH_RMA_TP;
2733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2734 x_msg_count := fnd_msg_pub.count_msg;
2735 WHEN OTHERS THEN
2736 Debug('In OTHERS Exception',l_mod_name,4);
2737 IF ( l_error_level >= G_debug_level) THEN
2738 fnd_message.set_name('CSD','CSD_SQL_ERROR');
2739 fnd_message.set_token('SQLERRM',SQLERRM);
2740 fnd_message.set_token('SQLCODE',SQLCODE);
2741 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
2742 END If;
2743 ROLLBACK TO REFRESH_RMA_TP;
2744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2745 IF FND_MSG_PUB.Check_Msg_Level
2746 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2747 THEN
2748 FND_MSG_PUB.Add_Exc_Msg
2749 (G_PKG_NAME ,
2750 l_api_name );
2751 END IF;
2752 x_msg_count := fnd_msg_pub.count_msg;
2753
2754 END Refresh_RMA_Third_Party;
2755
2756 PROCEDURE Refresh_Ship
2757 (p_api_version IN NUMBER,
2758 p_commit IN VARCHAR2,
2759 p_init_msg_list IN VARCHAR2,
2760 p_validation_level IN NUMBER,
2761 x_return_status OUT NOCOPY VARCHAR2,
2762 x_msg_count OUT NOCOPY NUMBER,
2763 x_msg_data OUT NOCOPY VARCHAR2,
2764 p_order_line_id IN NUMBER := FND_API.G_MISS_NUM,
2765 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
2766 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
2767 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
2768 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
2769 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
2770 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
2771 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
2772 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
2773 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
2774 IS
2775 -- Standard Variables
2776 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH_SHIP';
2777 l_api_version CONSTANT NUMBER := 1.0;
2778
2779 -- Variables in FND log
2780 l_error_level number := FND_LOG.LEVEL_ERROR;
2781 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh_ship';
2782
2783 l_user_id NUMBER;
2784 l_login_id NUMBER;
2785
2786 l_updated_keys JTF_NUMBER_TABLE;
2787
2788 l_prev_status_CODE VARCHAR2(80);
2789 l_status_CODE VARCHAR2(80);
2790 l_repair_line_id NUMBER;
2791 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
2792 l_rule_id NUMBER;
2793
2794 CURSOR c_get_defaulting_info(c_orchestration_id NUMBER) IS
2795 SELECT prev_status_code, status_code, repair_line_id
2796 FROM CSD_SO_ORCHESTRATION
2797 WHERE orchestration_id = c_orchestration_id;
2798
2799 BEGIN
2800 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh_Ship...');
2801
2802 -- Initialize API return status to success
2803 x_return_status := FND_API.G_RET_STS_SUCCESS;
2804
2805 -- Log the api name in the log file
2806 Debug('At the Beginning of Refresh_Ship', l_mod_name, 1);
2807
2808 -- Standard call to check for call compatibility.
2809 IF NOT FND_API.Compatible_API_Call (l_api_version,
2810 p_api_version,
2811 l_api_name ,
2812 G_PKG_NAME)
2813 THEN
2814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2815 END IF;
2816
2817 -- Initialize message list if p_init_msg_list is set to TRUE.
2818 IF FND_API.to_Boolean(p_init_msg_list) THEN
2819 FND_MSG_PUB.initialize;
2820 END IF;
2821
2822 -- Api body starts
2823 l_user_id := NVL(fnd_global.USER_ID, -1);
2824 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
2825
2826 g_updated_ids := JTF_NUMBER_TABLE();
2827
2828 MERGE INTO CSD_SO_ORCHESTRATION soo USING (
2829 select
2830 dra.repair_line_id,
2831 oel.header_id document_header_id,
2832 oel.line_id document_line_id,
2833 oeh.order_number document_header_name,
2834 oel.line_number document_line_name,
2835 'SHIP' document_type,
2836 null status_id,
2837 lv.lookup_code status_code
2838 from oe_order_lines_all oel,
2839 oe_order_headers_all oeh,
2840 csd_product_transactions dpt,
2841 cs_estimate_details edt,
2842 csd_repairs dra,
2843 fnd_lookup_values lv
2844 where oeh.header_id = oel.header_id
2845 and dpt.estimate_detail_id = edt.estimate_detail_id
2846 and edt.order_line_id = oel.line_id
2847 and dpt.repair_line_id = dra.repair_line_id
2848 and dpt.action_type = 'SHIP'
2849 and lv.lookup_type = 'LINE_FLOW_STATUS'
2850 and lv.meaning = OE_LINE_STATUS_PUB.get_line_status(p_line_id => oel.line_id,
2851 p_flow_status_code => oel.flow_status_code)
2852 and
2853 oel.line_id = decode(p_order_line_id,
2854 fnd_api.g_miss_num, oel.line_id,
2855 p_order_line_id) and
2856 dra.repair_line_id = decode(p_repair_line_id,
2857 fnd_api.g_miss_num, dra.repair_line_id,
2858 p_repair_line_id) and
2859 nvl(oel.org_id, fnd_api.g_miss_num) = -- operating unit
2860 decode(p_operating_unit_id,
2861 fnd_api.g_miss_num, nvl(oel.org_id, fnd_api.g_miss_num),
2862 p_operating_unit_id) and
2863 nvl(edt.transaction_inventory_org, fnd_api.g_miss_num) = -- inventory org
2864 decode(p_inventory_org_id,
2865 fnd_api.g_miss_num, nvl(edt.transaction_inventory_org, fnd_api.g_miss_num),
2866 p_inventory_org_id) and
2867 nvl(dra.resource_id, fnd_api.g_miss_num) =
2868 decode(p_repair_owner_id,
2869 fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
2870 p_repair_owner_id) and
2871 dra.flow_status_id =
2872 decode(p_ro_status_id,
2873 fnd_api.g_miss_num, dra.flow_status_id,
2874 p_ro_status_id) and
2875 dra.repair_type_id =
2876 decode(p_repair_type_id,
2877 fnd_api.g_miss_num, dra.repair_type_id,
2878 p_repair_type_id) and
2879 nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
2880 decode(p_ro_priority_code,
2881 fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
2882 p_ro_priority_code) and
2883 dra.creation_date >=
2884 decode(p_ro_age,
2885 fnd_api.g_miss_num, dra.creation_date,
2886 sysdate - p_ro_age) and
2887 nvl(dra.promise_date, G_MAX_DATE) <=
2888 decode(p_ro_due_date_age,
2889 fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
2890 sysdate + p_ro_due_date_age)) OLTP
2891 ON (soo.repair_line_id = OLTP.repair_line_id and
2892 -- soo.document_header_id = OLTP.document_header_id and
2893 soo.document_line_id = OLTP.document_line_id and
2894 soo.document_type = 'SHIP')
2895 WHEN MATCHED THEN
2896 UPDATE SET
2897 soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
2898 soo.status_code = OLTP.status_code,
2899 soo.object_version_number = soo.object_version_number + 1,
2900 soo.last_update_date = sysdate,
2901 soo.last_updated_by = l_user_id,
2902 soo.last_update_login = l_login_id
2903 WHEN NOT MATCHED THEN
2904 INSERT (ORCHESTRATION_ID,
2905 REPAIR_LINE_ID,
2906 OBJECT_VERSION_NUMBER,
2907 CREATED_BY,
2908 CREATION_DATE,
2909 LAST_UPDATED_BY,
2910 LAST_UPDATE_DATE,
2911 LAST_UPDATE_LOGIN,
2912 DOCUMENT_HEADER_ID,
2913 DOCUMENT_LINE_ID,
2914 DOCUMENT_HEADER_NAME,
2915 DOCUMENT_LINE_NAME,
2916 DOCUMENT_TYPE,
2917 STATUS_ID,
2918 STATUS_CODE,
2919 PREV_STATUS_ID,
2920 PREV_STATUS_CODE,
2921 ATTRIBUTE_CATEGORY,
2922 ATTRIBUTE1,
2923 ATTRIBUTE2,
2924 ATTRIBUTE3,
2925 ATTRIBUTE4,
2926 ATTRIBUTE5,
2927 ATTRIBUTE6,
2928 ATTRIBUTE7,
2929 ATTRIBUTE8,
2930 ATTRIBUTE9,
2931 ATTRIBUTE10,
2932 ATTRIBUTE11,
2933 ATTRIBUTE12,
2934 ATTRIBUTE13,
2935 ATTRIBUTE14,
2936 ATTRIBUTE15,
2937 PARAMN1)
2938 VALUES
2939 (csd_so_orchestration_s1.nextval,
2940 OLTP.repair_line_id,
2941 1,
2942 l_user_id,
2943 sysdate,
2944 l_user_id,
2945 sysdate,
2946 l_login_id,
2947 OLTP.document_header_id,
2948 OLTP.document_line_id,
2949 OLTP.document_header_name,
2950 OLTP.document_line_name,
2951 'SHIP',
2952 null, -- status_id
2953 OLTP.status_code,
2954 null, -- prev_status_id
2955 null, -- prev_status_code
2956 null, -- attribute_category
2957 null, -- attribute1
2958 null,
2959 null,
2960 null,
2961 null,
2962 null,
2963 null,
2964 null,
2965 null,
2966 null,
2967 null,
2968 null,
2969 null,
2970 null,
2971 null,
2972 null); -- paramn1
2973
2974 -- Save changes to CSD_SO_ORCHESTRATION table even
2975 -- if SOO rules engine fails.
2976 IF FND_API.To_Boolean( p_commit ) THEN
2977 COMMIT WORK;
2978 END IF;
2979
2980 SAVEPOINT REFRESH_SHIP;
2981
2982 l_updated_keys := get_updated_ids();
2983
2984 FOR i IN 1..l_updated_keys.COUNT
2985 LOOP
2986 OPEN c_get_defaulting_info(l_updated_keys(i));
2987 FETCH c_get_defaulting_info INTO l_prev_status_code, l_status_code, l_repair_line_id;
2988 CLOSE c_get_defaulting_info;
2989
2990 IF l_prev_status_code <> l_status_code THEN
2991 l_rule_input_rec.repair_line_id := l_repair_line_id;
2992 l_rule_input_rec.ship_prev_status_code := l_prev_status_code;
2993 l_rule_input_rec.ship_curr_status_code := l_status_code;
2994
2995 CSD_RULES_ENGINE_PVT.SOO_DEFAULTING(
2996 p_api_version_number => 1.0,
2997 p_commit => fnd_api.g_false,
2998 p_validation_level => p_validation_level,
2999 p_event_type => 'SHIP_STATUS',
3000 p_rule_input_rec => l_rule_input_rec,
3001 x_rule_id => l_rule_id,
3002 x_return_status => x_return_status,
3003 x_msg_count => x_msg_count,
3004 x_msg_data => x_msg_data);
3005
3006 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3007 Debug('Trying to default SOO rule for Ship status event failed.',l_mod_name,4);
3008 RAISE FND_API.G_EXC_ERROR;
3009 END IF;
3010
3011 update_ship_ro_line_id(l_repair_line_id);
3012 END IF;
3013 END LOOP;
3014
3015 -- Standard check of p_commit.
3016 IF FND_API.To_Boolean( p_commit ) THEN
3017 COMMIT WORK;
3018 END IF;
3019
3020 x_msg_count := fnd_msg_pub.count_msg;
3021
3022 EXCEPTION
3023 WHEN FND_API.G_EXC_ERROR THEN
3024 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
3025 ROLLBACK TO REFRESH_SHIP;
3026 x_return_status := FND_API.G_RET_STS_ERROR ;
3027 x_msg_count := fnd_msg_pub.count_msg;
3028 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3029 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
3030 IF ( l_error_level >= G_debug_level) THEN
3031 fnd_message.set_name('CSD','CSD_SQL_ERROR');
3032 fnd_message.set_token('SQLERRM',SQLERRM);
3033 fnd_message.set_token('SQLCODE',SQLCODE);
3034 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
3035 END If;
3036 ROLLBACK TO REFRESH_SHIP;
3037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3038 x_msg_count := fnd_msg_pub.count_msg;
3039 WHEN OTHERS THEN
3040 Debug('In OTHERS Exception',l_mod_name,4);
3041 IF ( l_error_level >= G_debug_level) THEN
3042 fnd_message.set_name('CSD','CSD_SQL_ERROR');
3043 fnd_message.set_token('SQLERRM',SQLERRM);
3044 fnd_message.set_token('SQLCODE',SQLCODE);
3045 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
3046 END If;
3047 ROLLBACK TO REFRESH_SHIP;
3048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3049 IF FND_MSG_PUB.Check_Msg_Level
3050 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3051 THEN
3052 FND_MSG_PUB.Add_Exc_Msg
3053 (G_PKG_NAME ,
3054 l_api_name );
3055 END IF;
3056 x_msg_count := fnd_msg_pub.count_msg;
3057
3058 END Refresh_Ship;
3059
3060 PROCEDURE Refresh_Ship_Third_Party
3061 (p_api_version IN NUMBER,
3062 p_commit IN VARCHAR2,
3063 p_init_msg_list IN VARCHAR2,
3064 p_validation_level IN NUMBER,
3065 x_return_status OUT NOCOPY VARCHAR2,
3066 x_msg_count OUT NOCOPY NUMBER,
3067 x_msg_data OUT NOCOPY VARCHAR2,
3068 p_order_line_id IN NUMBER := FND_API.G_MISS_NUM,
3069 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
3070 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
3071 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
3072 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
3073 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
3074 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
3075 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
3076 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
3077 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
3078 IS
3079 -- Standard Variables
3080 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH_SHIP_THIRD_PARTY';
3081 l_api_version CONSTANT NUMBER := 1.0;
3082
3083 -- Variables in FND log
3084 l_error_level number := FND_LOG.LEVEL_ERROR;
3085 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh_ship_third_party';
3086
3087 l_user_id NUMBER;
3088 l_login_id NUMBER;
3089
3090 l_updated_keys JTF_NUMBER_TABLE;
3091
3092 l_prev_status_CODE VARCHAR2(80);
3093 l_status_CODE VARCHAR2(80);
3094 l_repair_line_id NUMBER;
3095 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
3096 l_rule_id NUMBER;
3097
3098 CURSOR c_get_defaulting_info(c_orchestration_id NUMBER) IS
3099 SELECT prev_status_code, status_code, repair_line_id
3100 FROM CSD_SO_ORCHESTRATION
3101 WHERE orchestration_id = c_orchestration_id;
3102
3103 BEGIN
3104 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh_Ship_Third_Party...');
3105
3106 -- Initialize API return status to success
3107 x_return_status := FND_API.G_RET_STS_SUCCESS;
3108
3109 -- Log the api name in the log file
3110 Debug('At the Beginning of Refresh_Ship_Third_Party', l_mod_name, 1);
3111
3112 -- Standard call to check for call compatibility.
3113 IF NOT FND_API.Compatible_API_Call (l_api_version,
3114 p_api_version,
3115 l_api_name ,
3116 G_PKG_NAME)
3117 THEN
3118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3119 END IF;
3120
3121 -- Initialize message list if p_init_msg_list is set to TRUE.
3122 IF FND_API.to_Boolean(p_init_msg_list) THEN
3123 FND_MSG_PUB.initialize;
3124 END IF;
3125
3126 -- Api body starts
3127 l_user_id := NVL(fnd_global.USER_ID, -1);
3128 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
3129
3130 g_updated_ids := JTF_NUMBER_TABLE();
3131
3132 MERGE INTO CSD_SO_ORCHESTRATION soo USING (
3133 select
3134 dra.repair_line_id,
3135 oel.header_id document_header_id,
3136 oel.line_id document_line_id,
3137 oeh.order_number document_header_name,
3138 oel.line_number document_line_name,
3139 'SHIP_TP' document_type,
3140 null status_id,
3141 lv.lookup_code status_code
3142 from oe_order_lines_all oel,
3143 oe_order_headers_all oeh,
3144 csd_product_transactions dpt,
3145 cs_estimate_details edt,
3146 csd_repairs dra,
3147 fnd_lookup_values lv
3148 where oeh.header_id = oel.header_id
3149 and dpt.estimate_detail_id = edt.estimate_detail_id
3150 and edt.order_line_id = oel.line_id
3151 and dpt.repair_line_id = dra.repair_line_id
3152 and dpt.action_type = 'SHIP_THIRD_PTY'
3153 and lv.lookup_type = 'LINE_FLOW_STATUS'
3154 and lv.meaning = OE_LINE_STATUS_PUB.get_line_status(p_line_id => oel.line_id,
3155 p_flow_status_code => oel.flow_status_code)
3156 and
3157 oel.line_id = decode(p_order_line_id,
3158 fnd_api.g_miss_num, oel.line_id,
3159 p_order_line_id) and
3160 dra.repair_line_id = decode(p_repair_line_id,
3161 fnd_api.g_miss_num, dra.repair_line_id,
3162 p_repair_line_id) and
3163 nvl(oel.org_id, fnd_api.g_miss_num) = -- operating unit
3164 decode(p_operating_unit_id,
3165 fnd_api.g_miss_num, nvl(oel.org_id, fnd_api.g_miss_num),
3166 p_operating_unit_id) and
3167 nvl(edt.transaction_inventory_org, fnd_api.g_miss_num) = -- inventory org
3168 decode(p_inventory_org_id,
3169 fnd_api.g_miss_num, nvl(edt.transaction_inventory_org, fnd_api.g_miss_num),
3170 p_inventory_org_id) and
3171 nvl(dra.resource_id, fnd_api.g_miss_num) =
3172 decode(p_repair_owner_id,
3173 fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
3174 p_repair_owner_id) and
3175 dra.flow_status_id =
3176 decode(p_ro_status_id,
3177 fnd_api.g_miss_num, dra.flow_status_id,
3178 p_ro_status_id) and
3179 dra.repair_type_id =
3180 decode(p_repair_type_id,
3181 fnd_api.g_miss_num, dra.repair_type_id,
3182 p_repair_type_id) and
3183 nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
3184 decode(p_ro_priority_code,
3185 fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
3186 p_ro_priority_code) and
3187 dra.creation_date >=
3188 decode(p_ro_age,
3189 fnd_api.g_miss_num, dra.creation_date,
3190 sysdate - p_ro_age) and
3191 nvl(dra.promise_date, G_MAX_DATE) <=
3192 decode(p_ro_due_date_age,
3193 fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
3194 sysdate + p_ro_due_date_age)) OLTP
3195 ON (soo.repair_line_id = OLTP.repair_line_id and
3196 -- soo.document_header_id = OLTP.document_header_id and
3197 soo.document_line_id = OLTP.document_line_id and
3198 soo.document_type = 'SHIP_TP')
3199 WHEN MATCHED THEN
3200 UPDATE SET
3201 soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
3202 soo.status_code = OLTP.status_code,
3203 soo.object_version_number = soo.object_version_number + 1,
3204 soo.last_update_date = sysdate,
3205 soo.last_updated_by = l_user_id,
3206 soo.last_update_login = l_login_id
3207 WHEN NOT MATCHED THEN
3208 INSERT (ORCHESTRATION_ID,
3209 REPAIR_LINE_ID,
3210 OBJECT_VERSION_NUMBER,
3211 CREATED_BY,
3212 CREATION_DATE,
3213 LAST_UPDATED_BY,
3214 LAST_UPDATE_DATE,
3215 LAST_UPDATE_LOGIN,
3216 DOCUMENT_HEADER_ID,
3217 DOCUMENT_LINE_ID,
3218 DOCUMENT_HEADER_NAME,
3219 DOCUMENT_LINE_NAME,
3220 DOCUMENT_TYPE,
3221 STATUS_ID,
3222 STATUS_CODE,
3223 PREV_STATUS_ID,
3224 PREV_STATUS_CODE,
3225 ATTRIBUTE_CATEGORY,
3226 ATTRIBUTE1,
3227 ATTRIBUTE2,
3228 ATTRIBUTE3,
3229 ATTRIBUTE4,
3230 ATTRIBUTE5,
3231 ATTRIBUTE6,
3232 ATTRIBUTE7,
3233 ATTRIBUTE8,
3234 ATTRIBUTE9,
3235 ATTRIBUTE10,
3236 ATTRIBUTE11,
3237 ATTRIBUTE12,
3238 ATTRIBUTE13,
3239 ATTRIBUTE14,
3240 ATTRIBUTE15,
3241 PARAMN1)
3242 VALUES
3243 (csd_so_orchestration_s1.nextval,
3244 OLTP.repair_line_id,
3245 1,
3246 l_user_id,
3247 sysdate,
3248 l_user_id,
3249 sysdate,
3250 l_login_id,
3251 OLTP.document_header_id,
3252 OLTP.document_line_id,
3253 OLTP.document_header_name,
3254 OLTP.document_line_name,
3255 'SHIP_TP',
3256 null, -- status_id
3257 OLTP.status_code,
3258 null, -- prev_status_id
3259 null, -- prev_status_code
3260 null, -- attribute_category
3261 null, -- attribute1
3262 null,
3263 null,
3264 null,
3265 null,
3266 null,
3267 null,
3268 null,
3269 null,
3270 null,
3271 null,
3272 null,
3273 null,
3274 null,
3275 null,
3276 null); -- paramn1
3277
3278 -- Save changes to CSD_SO_ORCHESTRATION table even
3279 -- if SOO rules engine fails.
3280 IF FND_API.To_Boolean( p_commit ) THEN
3281 COMMIT WORK;
3282 END IF;
3283
3284 SAVEPOINT REFRESH_SHIP_TP;
3285
3286 l_updated_keys := get_updated_ids();
3287
3288 FOR i IN 1..l_updated_keys.COUNT
3289 LOOP
3290 OPEN c_get_defaulting_info(l_updated_keys(i));
3291 FETCH c_get_defaulting_info INTO l_prev_status_code, l_status_code, l_repair_line_id;
3292 CLOSE c_get_defaulting_info;
3293
3294 IF l_prev_status_code <> l_status_code THEN
3295 l_rule_input_rec.repair_line_id := l_repair_line_id;
3296 l_rule_input_rec.ship_tp_prev_status_code := l_prev_status_code;
3297 l_rule_input_rec.ship_tp_curr_status_code := l_status_code;
3298
3299 CSD_RULES_ENGINE_PVT.SOO_DEFAULTING(
3300 p_api_version_number => 1.0,
3301 p_commit => fnd_api.g_false,
3302 p_validation_level => p_validation_level,
3303 p_event_type => 'SHIP_TP_STATUS',
3304 p_rule_input_rec => l_rule_input_rec,
3305 x_rule_id => l_rule_id,
3306 x_return_status => x_return_status,
3307 x_msg_count => x_msg_count,
3308 x_msg_data => x_msg_data);
3309
3310 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3311 Debug('Trying to default SOO rule for Ship Third Party status event failed.',l_mod_name,4);
3312 RAISE FND_API.G_EXC_ERROR;
3313 END IF;
3314
3315 update_ship_ro_line_id(l_repair_line_id);
3316 END IF;
3317 END LOOP;
3318
3319 -- Standard check of p_commit.
3320 IF FND_API.To_Boolean( p_commit ) THEN
3321 COMMIT WORK;
3322 END IF;
3323
3324 x_msg_count := fnd_msg_pub.count_msg;
3325
3326 EXCEPTION
3327 WHEN FND_API.G_EXC_ERROR THEN
3328 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
3329 ROLLBACK TO REFRESH_SHIP_TP;
3330 x_return_status := FND_API.G_RET_STS_ERROR ;
3331 x_msg_count := fnd_msg_pub.count_msg;
3332 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3333 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
3334 IF ( l_error_level >= G_debug_level) THEN
3335 fnd_message.set_name('CSD','CSD_SQL_ERROR');
3336 fnd_message.set_token('SQLERRM',SQLERRM);
3337 fnd_message.set_token('SQLCODE',SQLCODE);
3338 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
3339 END If;
3340 ROLLBACK TO REFRESH_SHIP_TP;
3341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3342 x_msg_count := fnd_msg_pub.count_msg;
3343 WHEN OTHERS THEN
3344 Debug('In OTHERS Exception',l_mod_name,4);
3345 IF ( l_error_level >= G_debug_level) THEN
3346 fnd_message.set_name('CSD','CSD_SQL_ERROR');
3347 fnd_message.set_token('SQLERRM',SQLERRM);
3348 fnd_message.set_token('SQLCODE',SQLCODE);
3349 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
3350 END If;
3351 ROLLBACK TO REFRESH_SHIP_TP;
3352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3353 IF FND_MSG_PUB.Check_Msg_Level
3354 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3355 THEN
3356 FND_MSG_PUB.Add_Exc_Msg
3357 (G_PKG_NAME ,
3358 l_api_name );
3359 END IF;
3360 x_msg_count := fnd_msg_pub.count_msg;
3361
3362 END Refresh_Ship_Third_Party;
3363
3364 PROCEDURE Refresh_Delivery
3365 (p_api_version IN NUMBER,
3366 p_commit IN VARCHAR2,
3367 p_init_msg_list IN VARCHAR2,
3368 p_validation_level IN NUMBER,
3369 x_return_status OUT NOCOPY VARCHAR2,
3370 x_msg_count OUT NOCOPY NUMBER,
3371 x_msg_data OUT NOCOPY VARCHAR2,
3372 p_delivery_id IN NUMBER := FND_API.G_MISS_NUM,
3373 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
3374 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
3375 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
3376 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
3377 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
3378 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
3379 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
3380 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
3381 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
3382 IS
3383 -- Standard Variables
3384 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH_DELIVERY';
3385 l_api_version CONSTANT NUMBER := 1.0;
3386
3387 -- Variables in FND log
3388 l_error_level number := FND_LOG.LEVEL_ERROR;
3389 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh_delivery';
3390
3391 l_user_id NUMBER;
3392 l_login_id NUMBER;
3393
3394 l_updated_keys JTF_NUMBER_TABLE;
3395
3396 l_prev_status_CODE VARCHAR2(80);
3397 l_status_CODE VARCHAR2(80);
3398 l_repair_line_id NUMBER;
3399 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
3400 l_rule_id NUMBER;
3401
3402 CURSOR c_get_defaulting_info(c_orchestration_id NUMBER) IS
3403 SELECT prev_status_code, status_code, repair_line_id
3404 FROM CSD_SO_ORCHESTRATION
3405 WHERE orchestration_id = c_orchestration_id;
3406
3407 BEGIN
3408 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh_Delivery...');
3409
3410 -- Initialize API return status to success
3411 x_return_status := FND_API.G_RET_STS_SUCCESS;
3412
3413 -- Log the api name in the log file
3414 Debug('At the Beginning of Refresh_Delivery', l_mod_name, 1);
3415
3416 -- Standard call to check for call compatibility.
3417 IF NOT FND_API.Compatible_API_Call (l_api_version,
3418 p_api_version,
3419 l_api_name ,
3420 G_PKG_NAME)
3421 THEN
3422 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3423 END IF;
3424
3425 -- Initialize message list if p_init_msg_list is set to TRUE.
3426 IF FND_API.to_Boolean(p_init_msg_list) THEN
3427 FND_MSG_PUB.initialize;
3428 END IF;
3429
3430 -- Api body starts
3431 l_user_id := NVL(fnd_global.USER_ID, -1);
3432 l_login_id := NVL(fnd_global.LOGIN_ID, -1);
3433
3434 g_updated_ids := JTF_NUMBER_TABLE();
3435
3436 MERGE INTO CSD_SO_ORCHESTRATION soo USING (
3437 select distinct
3438 dra.repair_line_id,
3439 wnd.delivery_id document_header_id,
3440 null document_line_id,
3441 wnd.name document_header_name,
3442 null document_line_name,
3443 'DELIVERY' document_type,
3444 null status_id,
3445 wdd.released_status status_code
3446 from wsh_delivery_details wdd,
3447 wsh_delivery_assignments_v wda,
3448 wsh_new_deliveries wnd,
3449 oe_order_lines_all oel,
3450 csd_product_transactions dpt,
3451 cs_estimate_details edt,
3452 ORG_ORGANIZATION_DEFINITIONS orgd,
3453 csd_repairs dra
3454 where
3455 oel.line_id = wdd.source_line_id AND
3456 wdd.delivery_detail_id = wda.delivery_detail_id AND
3457 wdd.source_code = 'OE' AND
3458 wnd.delivery_id = wda.delivery_id and
3459 wnd.delivery_type = 'STANDARD'
3460 and dpt.estimate_detail_id = edt.estimate_detail_id
3461 and edt.order_line_id = oel.line_id
3462 and dpt.repair_line_id = dra.repair_line_id
3463 and orgd.organization_id(+) = wnd.organization_id
3464 and
3465 wnd.delivery_id = decode(p_delivery_id,
3466 fnd_api.g_miss_num, wnd.delivery_id,
3467 p_delivery_id) and
3468 dra.repair_line_id = decode(p_repair_line_id,
3469 fnd_api.g_miss_num, dra.repair_line_id,
3470 p_repair_line_id) and
3471 nvl(orgd.operating_unit, fnd_api.g_miss_num) =
3472 decode(p_operating_unit_id,
3473 fnd_api.g_miss_num, nvl(orgd.operating_unit, fnd_api.g_miss_num),
3474 p_operating_unit_id) and
3475 nvl(wnd.organization_id, fnd_api.g_miss_num) =
3476 decode(p_inventory_org_id,
3477 fnd_api.g_miss_num, nvl(wnd.organization_id, fnd_api.g_miss_num),
3478 p_inventory_org_id) and
3479 nvl(dra.resource_id, fnd_api.g_miss_num) =
3480 decode(p_repair_owner_id,
3481 fnd_api.g_miss_num, nvl(dra.resource_id, fnd_api.g_miss_num),
3482 p_repair_owner_id) and
3483 dra.flow_status_id =
3484 decode(p_ro_status_id,
3485 fnd_api.g_miss_num, dra.flow_status_id,
3486 p_ro_status_id) and
3487 dra.repair_type_id =
3488 decode(p_repair_type_id,
3489 fnd_api.g_miss_num, dra.repair_type_id,
3490 p_repair_type_id) and
3491 nvl(dra.ro_priority_code, fnd_api.g_miss_char) =
3492 decode(p_ro_priority_code,
3493 fnd_api.g_miss_char, nvl(dra.ro_priority_code, fnd_api.g_miss_char),
3494 p_ro_priority_code) and
3495 dra.creation_date >=
3496 decode(p_ro_age,
3497 fnd_api.g_miss_num, dra.creation_date,
3498 sysdate - p_ro_age) and
3499 nvl(dra.promise_date, G_MAX_DATE) <=
3500 decode(p_ro_due_date_age,
3501 fnd_api.g_miss_num, nvl(dra.promise_date, G_MAX_DATE),
3502 sysdate + p_ro_due_date_age)) OLTP
3503 ON (soo.repair_line_id = OLTP.repair_line_id and
3504 soo.document_header_id = OLTP.document_header_id and
3505 -- soo.document_line_id = OLTP.document_line_id and
3506 soo.document_type = 'DELIVERY')
3507 WHEN MATCHED THEN
3508 UPDATE SET
3509 soo.prev_status_code = (CASE update_count(soo.orchestration_id) when 'A' then soo.status_code end),
3510 soo.status_code = OLTP.status_code,
3511 soo.object_version_number = soo.object_version_number + 1,
3512 soo.last_update_date = sysdate,
3513 soo.last_updated_by = l_user_id,
3514 soo.last_update_login = l_login_id
3515 WHEN NOT MATCHED THEN
3516 INSERT (ORCHESTRATION_ID,
3517 REPAIR_LINE_ID,
3518 OBJECT_VERSION_NUMBER,
3519 CREATED_BY,
3520 CREATION_DATE,
3521 LAST_UPDATED_BY,
3522 LAST_UPDATE_DATE,
3523 LAST_UPDATE_LOGIN,
3524 DOCUMENT_HEADER_ID,
3525 DOCUMENT_LINE_ID,
3526 DOCUMENT_HEADER_NAME,
3527 DOCUMENT_LINE_NAME,
3528 DOCUMENT_TYPE,
3529 STATUS_ID,
3530 STATUS_CODE,
3531 PREV_STATUS_ID,
3532 PREV_STATUS_CODE,
3533 ATTRIBUTE_CATEGORY,
3534 ATTRIBUTE1,
3535 ATTRIBUTE2,
3536 ATTRIBUTE3,
3537 ATTRIBUTE4,
3538 ATTRIBUTE5,
3539 ATTRIBUTE6,
3540 ATTRIBUTE7,
3541 ATTRIBUTE8,
3542 ATTRIBUTE9,
3543 ATTRIBUTE10,
3544 ATTRIBUTE11,
3545 ATTRIBUTE12,
3546 ATTRIBUTE13,
3547 ATTRIBUTE14,
3548 ATTRIBUTE15,
3549 PARAMN1)
3550 VALUES
3551 (csd_so_orchestration_s1.nextval,
3552 OLTP.repair_line_id,
3553 1,
3554 l_user_id,
3555 sysdate,
3556 l_user_id,
3557 sysdate,
3558 l_login_id,
3559 OLTP.document_header_id,
3560 null,
3561 OLTP.document_header_name,
3562 null,
3563 'DELIVERY',
3564 null, -- status_id
3565 OLTP.status_code,
3566 null, -- prev_status_id
3567 null, -- prev_status_code
3568 null, -- attribute_category
3569 null, -- attribute1
3570 null,
3571 null,
3572 null,
3573 null,
3574 null,
3575 null,
3576 null,
3577 null,
3578 null,
3579 null,
3580 null,
3581 null,
3582 null,
3583 null,
3584 null); -- paramn1
3585
3586 -- Save changes to CSD_SO_ORCHESTRATION table even
3587 -- if SOO rules engine fails.
3588 IF FND_API.To_Boolean( p_commit ) THEN
3589 COMMIT WORK;
3590 END IF;
3591
3592 SAVEPOINT REFRESH_DELIVERY;
3593
3594 l_updated_keys := get_updated_ids();
3595
3596 FOR i IN 1..l_updated_keys.COUNT
3597 LOOP
3598 OPEN c_get_defaulting_info(l_updated_keys(i));
3599 FETCH c_get_defaulting_info INTO l_prev_status_code, l_status_code, l_repair_line_id;
3600 CLOSE c_get_defaulting_info;
3601
3602 IF l_prev_status_code <> l_status_code THEN
3603 l_rule_input_rec.repair_line_id := l_repair_line_id;
3604 l_rule_input_rec.delivery_prev_status_code := l_prev_status_code;
3605 l_rule_input_rec.delivery_curr_status_code := l_status_code;
3606
3607 CSD_RULES_ENGINE_PVT.SOO_DEFAULTING(
3608 p_api_version_number => 1.0,
3609 p_commit => fnd_api.g_false,
3610 p_validation_level => p_validation_level,
3611 p_event_type => 'DELIVERY_STATUS',
3612 p_rule_input_rec => l_rule_input_rec,
3613 x_rule_id => l_rule_id,
3614 x_return_status => x_return_status,
3615 x_msg_count => x_msg_count,
3616 x_msg_data => x_msg_data);
3617
3618 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3619 Debug('Trying to default SOO rule for Shipping: Delivery Line status event failed.',l_mod_name,4);
3620 RAISE FND_API.G_EXC_ERROR;
3621 END IF;
3622
3623 update_ship_ro_line_id(l_repair_line_id);
3624 END IF;
3625 END LOOP;
3626
3627 -- Standard check of p_commit.
3628 IF FND_API.To_Boolean( p_commit ) THEN
3629 COMMIT WORK;
3630 END IF;
3631
3632 x_msg_count := fnd_msg_pub.count_msg;
3633
3634 EXCEPTION
3635 WHEN FND_API.G_EXC_ERROR THEN
3636 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
3637 ROLLBACK TO REFRESH_DELIVERY;
3638 x_return_status := FND_API.G_RET_STS_ERROR ;
3639 x_msg_count := fnd_msg_pub.count_msg;
3640 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3641 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
3642 IF ( l_error_level >= G_debug_level) THEN
3643 fnd_message.set_name('CSD','CSD_SQL_ERROR');
3644 fnd_message.set_token('SQLERRM',SQLERRM);
3645 fnd_message.set_token('SQLCODE',SQLCODE);
3646 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
3647 END If;
3648 ROLLBACK TO REFRESH_DELIVERY;
3649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3650 x_msg_count := fnd_msg_pub.count_msg;
3651 WHEN OTHERS THEN
3652 Debug('In OTHERS Exception',l_mod_name,4);
3653 IF ( l_error_level >= G_debug_level) THEN
3654 fnd_message.set_name('CSD','CSD_SQL_ERROR');
3655 fnd_message.set_token('SQLERRM',SQLERRM);
3656 fnd_message.set_token('SQLCODE',SQLCODE);
3657 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
3658 END If;
3659 ROLLBACK TO REFRESH_DELIVERY;
3660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3661 IF FND_MSG_PUB.Check_Msg_Level
3662 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3663 THEN
3664 FND_MSG_PUB.Add_Exc_Msg
3665 (G_PKG_NAME ,
3666 l_api_name );
3667 END IF;
3668 x_msg_count := fnd_msg_pub.count_msg;
3669
3670 END Refresh_Delivery;
3671
3672 /*--------------------------------------------------*/
3673 /* procedure name: Refresh */
3674 /* description : procedure to load or update */
3675 /* CSD_SO_ORCHESTRATION_B and */
3676 /* CSD_SO_ORCHESTRATION_TL */
3677 /*--------------------------------------------------*/
3678 PROCEDURE Refresh(p_api_version IN NUMBER,
3679 p_commit IN VARCHAR2,
3680 p_init_msg_list IN VARCHAR2,
3681 p_validation_level IN NUMBER,
3682 x_return_status OUT NOCOPY VARCHAR2,
3683 x_msg_count OUT NOCOPY NUMBER,
3684 x_msg_data OUT NOCOPY VARCHAR2,
3685 p_so_orchestration_key_tbl IN so_orchestration_key_tbl_type,
3686 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
3687 p_document_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
3688 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
3689 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
3690 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
3691 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
3692 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
3693 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
3694 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
3695 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
3696 IS
3697 -- Standard Variables
3698 l_api_name CONSTANT VARCHAR2(30) := 'REFRESH';
3699 l_api_version CONSTANT NUMBER := 1.0;
3700
3701 -- Variables in FND log
3702 l_error_level number := FND_LOG.LEVEL_ERROR;
3703 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.refresh';
3704
3705 CURSOR c_so_orchestration_row(c_orchestration_id NUMBER) IS
3706 SELECT repair_line_id, document_type, document_header_id, document_line_id
3707 FROM csd_so_orchestration
3708 WHERE orchestration_id = c_orchestration_id;
3709
3710 l_row_repair_line_id NUMBER;
3711 l_row_document_type VARCHAR2(50);
3712 l_row_document_header_id NUMBER;
3713 l_row_document_line_id NUMBER;
3714
3715 l_repair_line_id NUMBER;
3716 l_document_type VARCHAR2(50);
3717 l_operating_unit_id NUMBER;
3718 l_inventory_org_id NUMBER;
3719 l_repair_owner_id NUMBER;
3720 l_ro_status_id NUMBER;
3721 l_repair_type_id NUMBER;
3722 l_ro_priority_code VARCHAR2(30);
3723 l_ro_age NUMBER;
3724 l_ro_due_date_age NUMBER;
3725
3726 l_rma_repair_line_id NUMBER;
3727 l_ship_repair_line_id NUMBER;
3728 l_int_order_header_id NUMBER;
3729
3730 l_return_status VARCHAR2(1);
3731 l_msg_count NUMBER;
3732 l_msg_data VARCHAR2(30000);
3733
3734 BEGIN
3735 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering Refresh...');
3736
3737 -- Initialize API return status to success
3738 x_return_status := FND_API.G_RET_STS_SUCCESS;
3739 l_return_status := FND_API.G_RET_STS_SUCCESS;
3740
3741 -- Log the api name in the log file
3742 Debug('At the Beginning of Refresh', l_mod_name, 1);
3743
3744 -- Standard call to check for call compatibility.
3745 IF NOT FND_API.Compatible_API_Call (l_api_version,
3746 p_api_version,
3747 l_api_name ,
3748 G_PKG_NAME)
3749 THEN
3750 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3751 END IF;
3752
3753 -- Initialize message list if p_init_msg_list is set to TRUE.
3754 IF FND_API.to_Boolean(p_init_msg_list) THEN
3755 FND_MSG_PUB.initialize;
3756 END IF;
3757
3758 -- Api body starts
3759
3760 l_repair_line_id := nvl(p_repair_line_id, fnd_api.g_miss_num);
3761 l_document_type := nvl(p_document_type, fnd_api.g_miss_char);
3762 l_operating_unit_id := nvl(p_operating_unit_id, fnd_api.g_miss_num);
3763 l_inventory_org_id := nvl(p_inventory_org_id, fnd_api.g_miss_num);
3764 l_repair_owner_id := nvl(p_repair_owner_id, fnd_api.g_miss_num);
3765 l_ro_status_id := nvl(p_ro_status_id, fnd_api.g_miss_num);
3766 l_repair_type_id := nvl(p_repair_type_id, fnd_api.g_miss_num);
3767 l_ro_priority_code := nvl(p_ro_priority_code, fnd_api.g_miss_char);
3768 l_ro_age := nvl(p_ro_age, fnd_api.g_miss_num);
3769 l_ro_due_date_age := nvl(p_ro_due_date_age, fnd_api.g_miss_num);
3770
3771 g_rma_ro_line_ids.delete();
3772 g_ship_ro_line_ids.delete();
3773 g_int_order_header_ids.delete();
3774
3775 IF p_so_orchestration_key_tbl.FIRST IS NOT null THEN
3776 Debug('Going through orchestration_id keys...', l_mod_name, 1);
3777
3778 FOR soo_ctr in p_so_orchestration_key_tbl.FIRST.. p_so_orchestration_key_tbl.LAST
3779 LOOP
3780 OPEN c_so_orchestration_row(p_so_orchestration_key_tbl(soo_ctr));
3781 FETCH c_so_orchestration_row INTO l_row_repair_line_id, l_row_document_type, l_row_document_header_id, l_row_document_line_id;
3782 CLOSE c_so_orchestration_row;
3783
3784
3785 IF l_row_document_type = 'JOB' THEN
3786 Refresh_Job
3787 (p_api_version => l_api_version,
3788 p_commit => p_commit,
3789 p_init_msg_list => FND_API.G_FALSE,
3790 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3791 x_return_status => x_return_status,
3792 x_msg_count => x_msg_count,
3793 x_msg_data => x_msg_data,
3794 p_wip_entity_id => l_row_document_header_id,
3795 p_repair_line_id => l_row_repair_line_id);
3796
3797 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3798 Debug('Refresh Job failed',l_mod_name,4);
3799 x_return_status := FND_API.G_RET_STS_ERROR;
3800 -- RAISE FND_API.G_EXC_ERROR;
3801 END IF;
3802
3803 ELSIF l_row_document_type = 'SR' THEN
3804 Refresh_SR
3805 (p_api_version => l_api_version,
3806 p_commit => p_commit,
3807 p_init_msg_list => FND_API.G_FALSE,
3808 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3809 x_return_status => x_return_status,
3810 x_msg_count => x_msg_count,
3811 x_msg_data => x_msg_data,
3812 p_incident_id => l_row_document_header_id,
3813 p_repair_line_id => l_row_repair_line_id);
3814
3815 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3816 Debug('Refresh SR failed',l_mod_name,4);
3817 x_return_status := FND_API.G_RET_STS_ERROR;
3818 -- RAISE FND_API.G_EXC_ERROR;
3819 END IF;
3820 ELSIF l_row_document_type = 'SR_TASK' THEN
3821 Refresh_SR_Task
3822 (p_api_version => l_api_version,
3823 p_commit => p_commit,
3824 p_init_msg_list => FND_API.G_FALSE,
3825 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3826 x_return_status => x_return_status,
3827 x_msg_count => x_msg_count,
3828 x_msg_data => x_msg_data,
3829 p_task_id => l_row_document_header_id,
3830 p_repair_line_id => l_row_repair_line_id);
3831
3832 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3833 Debug('Refresh SR Task failed',l_mod_name,4);
3834 x_return_status := FND_API.G_RET_STS_ERROR;
3835 -- RAISE FND_API.G_EXC_ERROR;
3836 END IF;
3837
3838 ELSIF l_row_document_type = 'PR' THEN
3839 Refresh_Purchase_Req
3840 (p_api_version => l_api_version,
3841 p_commit => p_commit,
3842 p_init_msg_list => FND_API.G_FALSE,
3843 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3844 x_return_status => x_return_status,
3845 x_msg_count => x_msg_count,
3846 x_msg_data => x_msg_data,
3847 p_req_header_id => l_row_document_header_id,
3848 p_repair_line_id => l_row_repair_line_id);
3849
3850 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3851 Debug('Refresh Purchase Req failed',l_mod_name,4);
3852 x_return_status := FND_API.G_RET_STS_ERROR;
3853 -- RAISE FND_API.G_EXC_ERROR;
3854 END IF;
3855
3856 ELSIF l_row_document_type = 'IR' THEN
3857 Refresh_Internal_Req
3858 (p_api_version => l_api_version,
3859 p_commit => p_commit,
3860 p_init_msg_list => FND_API.G_FALSE,
3861 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3862 x_return_status => x_return_status,
3863 x_msg_count => x_msg_count,
3864 x_msg_data => x_msg_data,
3865 p_req_header_id => l_row_document_header_id,
3866 p_repair_line_id => l_row_repair_line_id);
3867
3868 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3869 Debug('Refresh Internal Req failed',l_mod_name,4);
3870 x_return_status := FND_API.G_RET_STS_ERROR;
3871 -- RAISE FND_API.G_EXC_ERROR;
3872 END IF;
3873
3874 ELSIF l_row_document_type = 'RMA' THEN
3875 Refresh_RMA
3876 (p_api_version => l_api_version,
3877 p_commit => p_commit,
3878 p_init_msg_list => FND_API.G_FALSE,
3879 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3880 x_return_status => x_return_status,
3881 x_msg_count => x_msg_count,
3882 x_msg_data => x_msg_data,
3883 p_order_line_id => l_row_document_line_id,
3884 p_repair_line_id => l_row_repair_line_id);
3885
3886 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3887 Debug('Refresh RMA failed',l_mod_name,4);
3888 x_return_status := FND_API.G_RET_STS_ERROR;
3889 -- RAISE FND_API.G_EXC_ERROR;
3890 END IF;
3891
3892 ELSIF l_row_document_type = 'RMA_TP' THEN
3893 Refresh_RMA_Third_Party
3894 (p_api_version => l_api_version,
3895 p_commit => p_commit,
3896 p_init_msg_list => FND_API.G_FALSE,
3897 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3898 x_return_status => x_return_status,
3899 x_msg_count => x_msg_count,
3900 x_msg_data => x_msg_data,
3901 p_order_line_id => l_row_document_line_id,
3902 p_repair_line_id => l_row_repair_line_id);
3903
3904 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3905 Debug('Refresh RMA Third Party failed',l_mod_name,4);
3906 x_return_status := FND_API.G_RET_STS_ERROR;
3907 -- RAISE FND_API.G_EXC_ERROR;
3908 END IF;
3909
3910 ELSIF l_row_document_type = 'SHIP' THEN
3911 Refresh_Ship
3912 (p_api_version => l_api_version,
3913 p_commit => p_commit,
3914 p_init_msg_list => FND_API.G_FALSE,
3915 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3916 x_return_status => x_return_status,
3917 x_msg_count => x_msg_count,
3918 x_msg_data => x_msg_data,
3919 p_order_line_id => l_row_document_line_id,
3920 p_repair_line_id => l_row_repair_line_id);
3921
3922 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3923 Debug('Refresh Ship failed',l_mod_name,4);
3924 x_return_status := FND_API.G_RET_STS_ERROR;
3925 -- RAISE FND_API.G_EXC_ERROR;
3926 END IF;
3927
3928 ELSIF l_row_document_type = 'SHIP_TP' THEN
3929 Refresh_Ship_Third_Party
3930 (p_api_version => l_api_version,
3931 p_commit => p_commit,
3932 p_init_msg_list => FND_API.G_FALSE,
3933 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3934 x_return_status => x_return_status,
3935 x_msg_count => x_msg_count,
3936 x_msg_data => x_msg_data,
3937 p_order_line_id => l_row_document_line_id,
3938 p_repair_line_id => l_row_repair_line_id);
3939
3940 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3941 Debug('Refresh Ship Third Party failed',l_mod_name,4);
3942 x_return_status := FND_API.G_RET_STS_ERROR;
3943 -- RAISE FND_API.G_EXC_ERROR;
3944 END IF;
3945
3946 ELSIF l_row_document_type = 'DELIVERY' THEN
3947 Refresh_Delivery
3948 (p_api_version => l_api_version,
3949 p_commit => p_commit,
3950 p_init_msg_list => FND_API.G_FALSE,
3951 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3952 x_return_status => x_return_status,
3953 x_msg_count => x_msg_count,
3954 x_msg_data => x_msg_data,
3955 p_delivery_id => l_row_document_header_id,
3956 p_repair_line_id => l_row_repair_line_id);
3957
3958 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3959 Debug('Refresh Delivery failed',l_mod_name,4);
3960 x_return_status := FND_API.G_RET_STS_ERROR;
3961 -- RAISE FND_API.G_EXC_ERROR;
3962 END IF;
3963
3964 ELSIF l_row_document_type = 'INTSO' THEN
3965 Refresh_Int_Sales_Order
3966 (p_api_version => l_api_version,
3967 p_commit => p_commit,
3968 p_init_msg_list => FND_API.G_FALSE,
3969 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3970 x_return_status => x_return_status,
3971 x_msg_count => x_msg_count,
3972 x_msg_data => x_msg_data,
3973 p_order_line_id => l_row_document_line_id,
3974 p_repair_line_id => l_row_repair_line_id);
3975
3976 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3977 Debug('Refresh Internal Sales Order failed',l_mod_name,4);
3978 x_return_status := FND_API.G_RET_STS_ERROR;
3979 -- RAISE FND_API.G_EXC_ERROR;
3980 END IF;
3981 END IF; --IF l_row_document_type = ... THEN
3982 END LOOP;
3983 ELSE --csd_so_orchestration_key_tbl.first is null
3984 IF l_document_type = fnd_api.g_miss_char OR l_document_type = 'JOB' THEN
3985 Refresh_Job
3986 (p_api_version => l_api_version,
3987 p_commit => p_commit,
3988 p_init_msg_list => FND_API.G_FALSE,
3989 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3990 x_return_status => x_return_status,
3991 x_msg_count => x_msg_count,
3992 x_msg_data => x_msg_data,
3993 p_repair_line_id => l_repair_line_id,
3994 p_operating_unit_id => l_operating_unit_id,
3995 p_inventory_org_id => l_inventory_org_id,
3996 p_repair_owner_id => l_repair_owner_id,
3997 p_ro_status_id => l_ro_status_id,
3998 p_repair_type_id => l_repair_type_id,
3999 p_ro_priority_code => l_ro_priority_code,
4000 p_ro_age => l_ro_age,
4001 p_ro_due_date_age => l_ro_due_date_age);
4002
4003 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4004 Debug('Refresh Job failed',l_mod_name,4);
4005 l_return_status := FND_API.G_RET_STS_ERROR;
4006 -- RAISE FND_API.G_EXC_ERROR;
4007 END IF;
4008 END IF;
4009
4010 IF l_document_type = fnd_api.g_miss_char OR l_document_type = 'SR' THEN
4011 Refresh_SR
4012 (p_api_version => l_api_version,
4013 p_commit => p_commit,
4014 p_init_msg_list => FND_API.G_FALSE,
4015 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4016 x_return_status => x_return_status,
4017 x_msg_count => x_msg_count,
4018 x_msg_data => x_msg_data,
4019 p_repair_line_id => l_repair_line_id,
4020 p_operating_unit_id => l_operating_unit_id,
4021 p_inventory_org_id => l_inventory_org_id,
4022 p_repair_owner_id => l_repair_owner_id,
4023 p_ro_status_id => l_ro_status_id,
4024 p_repair_type_id => l_repair_type_id,
4025 p_ro_priority_code => l_ro_priority_code,
4026 p_ro_age => l_ro_age,
4027 p_ro_due_date_age => l_ro_due_date_age);
4028
4029 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4030 Debug('Refresh SR failed',l_mod_name,4);
4031 l_return_status := FND_API.G_RET_STS_ERROR;
4032 -- RAISE FND_API.G_EXC_ERROR;
4033 END IF;
4034 END IF;
4035
4036 IF l_document_type = fnd_api.g_miss_char OR l_document_type = 'SR_TASK' THEN
4037 Refresh_SR_Task
4038 (p_api_version => l_api_version,
4039 p_commit => p_commit,
4040 p_init_msg_list => FND_API.G_FALSE,
4041 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4042 x_return_status => x_return_status,
4043 x_msg_count => x_msg_count,
4044 x_msg_data => x_msg_data,
4045 p_repair_line_id => l_repair_line_id,
4046 p_operating_unit_id => l_operating_unit_id,
4047 p_inventory_org_id => l_inventory_org_id,
4048 p_repair_owner_id => l_repair_owner_id,
4049 p_ro_status_id => l_ro_status_id,
4050 p_repair_type_id => l_repair_type_id,
4051 p_ro_priority_code => l_ro_priority_code,
4052 p_ro_age => l_ro_age,
4053 p_ro_due_date_age => l_ro_due_date_age);
4054
4055 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4056 Debug('Refresh SR Task failed',l_mod_name,4);
4057 l_return_status := FND_API.G_RET_STS_ERROR;
4058 -- RAISE FND_API.G_EXC_ERROR;
4059 END IF;
4060 END IF;
4061
4062 IF l_document_type = fnd_api.g_miss_char OR l_document_type = 'PR' THEN
4063 Refresh_Purchase_Req
4064 (p_api_version => l_api_version,
4065 p_commit => p_commit,
4066 p_init_msg_list => FND_API.G_FALSE,
4067 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4068 x_return_status => x_return_status,
4069 x_msg_count => x_msg_count,
4070 x_msg_data => x_msg_data,
4071 p_repair_line_id => l_repair_line_id,
4072 p_operating_unit_id => l_operating_unit_id,
4073 p_inventory_org_id => l_inventory_org_id,
4074 p_repair_owner_id => l_repair_owner_id,
4075 p_ro_status_id => l_ro_status_id,
4076 p_repair_type_id => l_repair_type_id,
4077 p_ro_priority_code => l_ro_priority_code,
4078 p_ro_age => l_ro_age,
4079 p_ro_due_date_age => l_ro_due_date_age);
4080
4081 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4082 Debug('Refresh Purchase Req failed',l_mod_name,4);
4083 l_return_status := FND_API.G_RET_STS_ERROR;
4084 -- RAISE FND_API.G_EXC_ERROR;
4085 END IF;
4086 END IF;
4087
4088 IF l_document_type = fnd_api.g_miss_char OR l_document_type = 'IR' THEN
4089 Refresh_Internal_Req
4090 (p_api_version => l_api_version,
4091 p_commit => p_commit,
4092 p_init_msg_list => FND_API.G_FALSE,
4093 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4094 x_return_status => x_return_status,
4095 x_msg_count => x_msg_count,
4096 x_msg_data => x_msg_data,
4097 p_repair_line_id => l_repair_line_id,
4098 p_operating_unit_id => l_operating_unit_id,
4099 p_inventory_org_id => l_inventory_org_id,
4100 p_repair_owner_id => l_repair_owner_id,
4101 p_ro_status_id => l_ro_status_id,
4102 p_repair_type_id => l_repair_type_id,
4103 p_ro_priority_code => l_ro_priority_code,
4104 p_ro_age => l_ro_age,
4105 p_ro_due_date_age => l_ro_due_date_age);
4106
4107 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4108 Debug('Refresh Internal Req failed',l_mod_name,4);
4109 l_return_status := FND_API.G_RET_STS_ERROR;
4110 -- RAISE FND_API.G_EXC_ERROR;
4111 END IF;
4112 END IF;
4113
4114 IF l_document_type = fnd_api.g_miss_char OR l_document_type = 'RMA' THEN
4115 Refresh_RMA
4116 (p_api_version => l_api_version,
4117 p_commit => p_commit,
4118 p_init_msg_list => FND_API.G_FALSE,
4119 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4120 x_return_status => x_return_status,
4121 x_msg_count => x_msg_count,
4122 x_msg_data => x_msg_data,
4123 p_repair_line_id => l_repair_line_id,
4124 p_operating_unit_id => l_operating_unit_id,
4125 p_inventory_org_id => l_inventory_org_id,
4126 p_repair_owner_id => l_repair_owner_id,
4127 p_ro_status_id => l_ro_status_id,
4128 p_repair_type_id => l_repair_type_id,
4129 p_ro_priority_code => l_ro_priority_code,
4130 p_ro_age => l_ro_age,
4131 p_ro_due_date_age => l_ro_due_date_age);
4132
4133 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4134 Debug('Refresh RMA failed',l_mod_name,4);
4135 l_return_status := FND_API.G_RET_STS_ERROR;
4136 -- RAISE FND_API.G_EXC_ERROR;
4137 END IF;
4138 END IF;
4139
4140 IF l_document_type = fnd_api.g_miss_char OR l_document_type = 'RMA_TP' THEN
4141 Refresh_RMA_Third_Party
4142 (p_api_version => l_api_version,
4143 p_commit => p_commit,
4144 p_init_msg_list => FND_API.G_FALSE,
4145 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4146 x_return_status => x_return_status,
4147 x_msg_count => x_msg_count,
4148 x_msg_data => x_msg_data,
4149 p_repair_line_id => l_repair_line_id,
4150 p_operating_unit_id => l_operating_unit_id,
4151 p_inventory_org_id => l_inventory_org_id,
4152 p_repair_owner_id => l_repair_owner_id,
4153 p_ro_status_id => l_ro_status_id,
4154 p_repair_type_id => l_repair_type_id,
4155 p_ro_priority_code => l_ro_priority_code,
4156 p_ro_age => l_ro_age,
4157 p_ro_due_date_age => l_ro_due_date_age);
4158
4159 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4160 Debug('Refresh RMA Third Party failed',l_mod_name,4);
4161 l_return_status := FND_API.G_RET_STS_ERROR;
4162 -- RAISE FND_API.G_EXC_ERROR;
4163 END IF;
4164 END IF;
4165
4166 IF l_document_type = fnd_api.g_miss_char OR l_document_type = 'SHIP' THEN
4167 Refresh_Ship
4168 (p_api_version => l_api_version,
4169 p_commit => p_commit,
4170 p_init_msg_list => FND_API.G_FALSE,
4171 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4172 x_return_status => x_return_status,
4173 x_msg_count => x_msg_count,
4174 x_msg_data => x_msg_data,
4175 p_repair_line_id => l_repair_line_id,
4176 p_operating_unit_id => l_operating_unit_id,
4177 p_inventory_org_id => l_inventory_org_id,
4178 p_repair_owner_id => l_repair_owner_id,
4179 p_ro_status_id => l_ro_status_id,
4180 p_repair_type_id => l_repair_type_id,
4181 p_ro_priority_code => l_ro_priority_code,
4182 p_ro_age => l_ro_age,
4183 p_ro_due_date_age => l_ro_due_date_age);
4184
4185 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4186 Debug('Refresh Ship failed',l_mod_name,4);
4187 l_return_status := FND_API.G_RET_STS_ERROR;
4188 -- RAISE FND_API.G_EXC_ERROR;
4189 END IF;
4190 END IF;
4191
4192 IF l_document_type = fnd_api.g_miss_char OR l_document_type = 'SHIP_TP' THEN
4193 Refresh_Ship_Third_Party
4194 (p_api_version => l_api_version,
4195 p_commit => p_commit,
4196 p_init_msg_list => FND_API.G_FALSE,
4197 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4198 x_return_status => x_return_status,
4199 x_msg_count => x_msg_count,
4200 x_msg_data => x_msg_data,
4201 p_repair_line_id => l_repair_line_id,
4202 p_operating_unit_id => l_operating_unit_id,
4203 p_inventory_org_id => l_inventory_org_id,
4204 p_repair_owner_id => l_repair_owner_id,
4205 p_ro_status_id => l_ro_status_id,
4206 p_repair_type_id => l_repair_type_id,
4207 p_ro_priority_code => l_ro_priority_code,
4208 p_ro_age => l_ro_age,
4209 p_ro_due_date_age => l_ro_due_date_age);
4210
4211 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4212 Debug('Refresh Ship Third Party failed',l_mod_name,4);
4213 l_return_status := FND_API.G_RET_STS_ERROR;
4214 -- RAISE FND_API.G_EXC_ERROR;
4215 END IF;
4216 END IF;
4217
4218 IF l_document_type = fnd_api.g_miss_char OR l_document_type = 'DELIVERY' THEN
4219 Refresh_Delivery
4220 (p_api_version => l_api_version,
4221 p_commit => p_commit,
4222 p_init_msg_list => FND_API.G_FALSE,
4223 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4224 x_return_status => x_return_status,
4225 x_msg_count => x_msg_count,
4226 x_msg_data => x_msg_data,
4227 p_repair_line_id => l_repair_line_id,
4228 p_operating_unit_id => l_operating_unit_id,
4229 p_inventory_org_id => l_inventory_org_id,
4230 p_repair_owner_id => l_repair_owner_id,
4231 p_ro_status_id => l_ro_status_id,
4232 p_repair_type_id => l_repair_type_id,
4233 p_ro_priority_code => l_ro_priority_code,
4234 p_ro_age => l_ro_age,
4235 p_ro_due_date_age => l_ro_due_date_age);
4236
4237 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4238 Debug('Refresh Delivery failed',l_mod_name,4);
4239 l_return_status := FND_API.G_RET_STS_ERROR;
4240 -- RAISE FND_API.G_EXC_ERROR;
4241 END IF;
4242 END IF;
4243
4244 IF l_document_type = fnd_api.g_miss_char OR l_document_type = 'INTSO' THEN
4245 Refresh_Int_Sales_Order
4246 (p_api_version => l_api_version,
4247 p_commit => p_commit,
4248 p_init_msg_list => FND_API.G_FALSE,
4249 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4250 x_return_status => x_return_status,
4251 x_msg_count => x_msg_count,
4252 x_msg_data => x_msg_data,
4253 p_repair_line_id => l_repair_line_id,
4254 p_operating_unit_id => l_operating_unit_id,
4255 p_inventory_org_id => l_inventory_org_id,
4256 p_repair_owner_id => l_repair_owner_id,
4257 p_ro_status_id => l_ro_status_id,
4258 p_repair_type_id => l_repair_type_id,
4259 p_ro_priority_code => l_ro_priority_code,
4260 p_ro_age => l_ro_age,
4261 p_ro_due_date_age => l_ro_due_date_age);
4262
4263 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4264 Debug('Refresh Internal Sales Order failed',l_mod_name,4);
4265 l_return_status := FND_API.G_RET_STS_ERROR;
4266 -- RAISE FND_API.G_EXC_ERROR;
4267 END IF;
4268 END IF;
4269 END IF; -- IF csd_so_orchestration_key_tbl.first is not null THEN
4270
4271 l_rma_repair_line_id := g_rma_ro_line_ids.FIRST;
4272 WHILE l_rma_repair_line_id IS NOT NULL LOOP
4273 CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
4274 (p_api_version => l_api_version,
4275 p_commit => FND_API.G_FALSE,
4276 p_init_msg_list => FND_API.G_TRUE,
4277 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4278 x_return_status => x_return_status,
4279 x_msg_count => x_msg_count,
4280 x_msg_data => x_msg_data,
4281 p_internal_order_flag => 'N',
4282 p_order_header_id => NULL,
4283 p_repair_line_id => l_rma_repair_line_id);
4284
4285 x_msg_data := x_msg_data || '
4286 ' || l_msg_data;
4287
4288 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4289 Debug('RECEIPTS_UPDATE failed',l_mod_name,4);
4290 l_return_status := FND_API.G_RET_STS_ERROR;
4291 -- RAISE FND_API.G_EXC_ERROR;
4292 END IF;
4293
4294 l_rma_repair_line_id := g_rma_ro_line_ids.NEXT(l_rma_repair_line_id);
4295 END LOOP;
4296
4297 l_ship_repair_line_id := g_ship_ro_line_ids.FIRST;
4298 WHILE l_ship_repair_line_id IS NOT NULL LOOP
4299 CSD_UPDATE_PROGRAMS_PVT.SHIP_UPDATE
4300 (p_api_version => l_api_version,
4301 p_commit => FND_API.G_FALSE,
4302 p_init_msg_list => FND_API.G_TRUE,
4303 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4304 x_return_status => x_return_status,
4305 x_msg_count => x_msg_count,
4306 x_msg_data => l_msg_data,
4307 p_internal_order_flag => 'N',
4308 p_order_header_id => NULL,
4309 p_repair_line_id => l_ship_repair_line_id);
4310
4311 x_msg_data := x_msg_data || '
4312 ' || l_msg_data;
4313
4314 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4315 Debug('SHIP_UPDATE failed',l_mod_name,4);
4316 l_return_status := FND_API.G_RET_STS_ERROR;
4317 -- RAISE FND_API.G_EXC_ERROR;
4318 END IF;
4319
4320 l_ship_repair_line_id := g_ship_ro_line_ids.NEXT(l_ship_repair_line_id);
4321 END LOOP;
4322
4323 l_int_order_header_id := g_int_order_header_ids.FIRST;
4324 WHILE l_int_order_header_id IS NOT NULL LOOP
4325 CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
4326 (p_api_version => l_api_version,
4327 p_commit => FND_API.G_FALSE,
4328 p_init_msg_list => FND_API.G_TRUE,
4329 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4330 x_return_status => x_return_status,
4331 x_msg_count => x_msg_count,
4332 x_msg_data => l_msg_data,
4333 p_internal_order_flag => 'Y',
4334 p_order_header_id => l_int_order_header_id,
4335 p_repair_line_id => NULL);
4336
4337 x_msg_data := x_msg_data || '
4338 ' || l_msg_data;
4339
4340 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4341 Debug('RECEIPTS_UPDATE failed for internal sales orders and requisitions',l_mod_name,4);
4342 l_return_status := FND_API.G_RET_STS_ERROR;
4343 -- RAISE FND_API.G_EXC_ERROR;
4344 END IF;
4345
4346 l_int_order_header_id := g_int_order_header_ids.NEXT(l_int_order_header_id);
4347 END LOOP;
4348
4349 -- Something failed during a "Refresh All," so return an error status.
4350 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4351 x_return_status := l_return_status;
4352 END IF;
4353
4354 -- Standard check of p_commit.
4355 IF FND_API.To_Boolean( p_commit ) THEN
4356 COMMIT WORK;
4357 END IF;
4358
4359 x_msg_count := fnd_msg_pub.count_msg;
4360
4361 EXCEPTION
4362 WHEN FND_API.G_EXC_ERROR THEN
4363 Debug('In FND_API.G_EXC_ERROR Exception ',l_mod_name,4);
4364 x_return_status := FND_API.G_RET_STS_ERROR ;
4365 x_msg_count := fnd_msg_pub.count_msg;
4366 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4367 Debug('In FND_API.G_EXC_UNEXPECTED_ERROR Exception',l_mod_name,4);
4368 IF ( l_error_level >= G_debug_level) THEN
4369 fnd_message.set_name('CSD','CSD_SQL_ERROR');
4370 fnd_message.set_token('SQLERRM',SQLERRM);
4371 fnd_message.set_token('SQLCODE',SQLCODE);
4372 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
4373 END If;
4374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4375 x_msg_count := fnd_msg_pub.count_msg;
4376 WHEN OTHERS THEN
4377 Debug('In OTHERS Exception',l_mod_name,4);
4378 IF ( l_error_level >= G_debug_level) THEN
4379 fnd_message.set_name('CSD','CSD_SQL_ERROR');
4380 fnd_message.set_token('SQLERRM',SQLERRM);
4381 fnd_message.set_token('SQLCODE',SQLCODE);
4382 FND_LOG.MESSAGE(l_error_level,l_mod_name,FALSE);
4383 END If;
4384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4385 IF FND_MSG_PUB.Check_Msg_Level
4386 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4387 THEN
4388 FND_MSG_PUB.Add_Exc_Msg
4389 (G_PKG_NAME ,
4390 l_api_name );
4391 END IF;
4392 x_msg_count := fnd_msg_pub.count_msg;
4393
4394 End Refresh;
4395
4396
4397 /*--------------------------------------------------*/
4398 /* procedure name: Soo_Driver_Main */
4399 /* description : main procedure of */
4400 /* concurrent program */
4401 /*--------------------------------------------------*/
4402 PROCEDURE Soo_Driver_Main(errbuf OUT NOCOPY VARCHAR2,
4403 retcode OUT NOCOPY VARCHAR2,
4404 p_repair_line_id IN NUMBER := FND_API.G_MISS_NUM,
4405 p_document_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
4406 p_operating_unit_id IN NUMBER := FND_API.G_MISS_NUM,
4407 p_inventory_org_id IN NUMBER := FND_API.G_MISS_NUM,
4408 p_repair_owner_id IN NUMBER := FND_API.G_MISS_NUM,
4409 p_ro_status_id IN NUMBER := FND_API.G_MISS_NUM,
4410 p_repair_type_id IN NUMBER := FND_API.G_MISS_NUM,
4411 p_ro_priority_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
4412 p_ro_age IN NUMBER := FND_API.G_MISS_NUM,
4413 p_ro_due_date_age IN NUMBER := FND_API.G_MISS_NUM)
4414 IS
4415
4416 l_api_version CONSTANT NUMBER := 1.0;
4417 l_api_name CONSTANT VARCHAR2(30) := 'Soo_Driver_Main';
4418
4419 l_return_status VARCHAR2(1);
4420 l_msg_count NUMBER;
4421 l_msg_data VARCHAR2(30000);
4422 l_msg_text VARCHAR2(2000);
4423
4424 l_error_level number := FND_LOG.LEVEL_ERROR;
4425 l_mod_name varchar2(2000) := 'csd.plsql.csd_soo_pvt.Soo_Driver_Main';
4426
4427 l_so_orchestration_key_tbl so_orchestration_key_tbl_type;
4428 l_repair_line_id NUMBER;
4429 l_document_type VARCHAR2(50);
4430 l_operating_unit_id NUMBER;
4431 l_inventory_org_id NUMBER;
4432 l_repair_owner_id NUMBER;
4433 l_ro_status_id NUMBER;
4434 l_repair_type_id NUMBER;
4435 l_ro_priority_code VARCHAR2(30);
4436 l_ro_age NUMBER;
4437 l_ro_due_date_age NUMBER;
4438
4439 BEGIN
4440
4441 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Entering the concurrent program...');
4442
4443 retcode := C_OK;
4444 errbuf := '';
4445
4446 l_repair_line_id := nvl(p_repair_line_id, fnd_api.g_miss_num);
4447 l_document_type := nvl(p_document_type, fnd_api.g_miss_char);
4448 l_operating_unit_id := nvl(p_operating_unit_id, fnd_api.g_miss_num);
4449 l_inventory_org_id := nvl(p_inventory_org_id, fnd_api.g_miss_num);
4450 l_repair_owner_id := nvl(p_repair_owner_id, fnd_api.g_miss_num);
4451 l_ro_status_id := nvl(p_ro_status_id, fnd_api.g_miss_num);
4452 l_repair_type_id := nvl(p_repair_type_id, fnd_api.g_miss_num);
4453 l_ro_priority_code := nvl(p_ro_priority_code, fnd_api.g_miss_char);
4454 l_ro_age := nvl(p_ro_age, fnd_api.g_miss_num);
4455 l_ro_due_date_age := nvl(p_ro_due_date_age, fnd_api.g_miss_num);
4456
4457 Refresh(p_api_version => l_api_version,
4458 p_commit => FND_API.G_TRUE,
4459 p_init_msg_list => FND_API.G_TRUE,
4460 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4461 x_return_status => l_return_status,
4462 x_msg_count => l_msg_count,
4463 x_msg_data => l_msg_data,
4464 p_so_orchestration_key_tbl => l_so_orchestration_key_tbl,
4465 p_repair_line_id => l_repair_line_id,
4466 p_document_type => l_document_type,
4467 p_operating_unit_id => l_operating_unit_id,
4468 p_inventory_org_id => l_inventory_org_id,
4469 p_repair_owner_id => l_repair_owner_id,
4470 p_ro_status_id => l_ro_status_id,
4471 p_repair_type_id => l_repair_type_id,
4472 p_ro_priority_code => l_ro_priority_code,
4473 p_ro_age => l_ro_age,
4474 p_ro_due_date_age => l_ro_due_date_age);
4475
4476 IF l_msg_count > 1 then
4477 FOR i IN 1..l_msg_count LOOP
4478 l_msg_data := l_msg_data || '
4479 ' || FND_MSG_PUB.Get(i,FND_API.G_FALSE) ;
4480 END LOOP ;
4481 fnd_file.put_line(fnd_file.log, '');
4482 fnd_file.put_line(fnd_file.log, l_msg_data);
4483 END IF ;
4484
4485 If ((l_return_status = G_CSD_RET_STS_WARNING) AND
4486 (NVL(fnd_profile.value('CSD_LOGISTICS_PROGRAM_ERROR'), 'S') = 'I')) THEN
4487 retcode := C_OK; -- return success since we are ignoring warnings
4488 errbuf := l_msg_data; -- but show the messages in the log
4489 ELSIF ((l_return_status = G_CSD_RET_STS_WARNING) AND
4490 (NVL(fnd_profile.value('CSD_LOGISTICS_PROGRAM_ERROR'), 'S') = 'A')) THEN
4491 retcode := C_WARNING;
4492 errbuf := l_msg_data;
4493 ELSIF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4494 Debug(l_msg_data,l_mod_name,4);
4495 RAISE FND_API.G_EXC_ERROR;
4496 END IF;
4497
4498 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Leaving the concurrent program...');
4499
4500 Exception
4501 WHEN FND_API.G_EXC_ERROR THEN
4502 retcode := C_ERROR;
4503 errbuf := l_msg_data;
4504 WHEN Others then
4505 -- Handle others exception
4506 retcode := C_ERROR;
4507 errbuf := l_msg_data;
4508
4509 END Soo_Driver_Main;
4510
4511 END CSD_SOO_PVT;