[Home] [Help]
PACKAGE BODY: APPS.OKE_DEFAULTING_PKG
Source
1 PACKAGE BODY OKE_DEFAULTING_PKG AS
2 /* $Header: OKEVLTDB.pls 120.8 2007/12/18 11:45:35 serukull ship $ */
3
4 G_MSG_MAX_LIMIT CONSTANT NUMBER := 1950;
5
6 FUNCTION isNewMessageWithinLimit(
7 p_existing_message IN VARCHAR2
8 ,p_new_token_value IN VARCHAR2) RETURN BOOLEAN IS
9
10 l_api_name CONSTANT VARCHAR2(30) :='isNewMessageWithinLimit';
11
12 BEGIN
13
14 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
15 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'oke.plsql.'||g_pkg_name||'.'||l_api_name,'100: ENTERED ');
16 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'oke.plsql.'||g_pkg_name||'.'||l_api_name
17 ,'101: p_existing_message length = '||length(p_existing_message));
18 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'oke.plsql.'||g_pkg_name||'.'||l_api_name
19 ,'102: p_new_token_value = '||p_new_token_value);
20 END IF;
21
22 IF p_existing_message is null THEN
23 return TRUE;
24 END IF;
25
26 IF length (p_existing_message||','||p_new_token_value) > G_MSG_MAX_LIMIT THEN
27 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
28 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'oke.plsql.'||g_pkg_name||'.'||l_api_name
29 ,'103: Returning FALSE ');
30 END IF;
31 return FALSE;
32 ELSE
33 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
34 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'oke.plsql.'||g_pkg_name||'.'||l_api_name
35 ,'104: Returning TRUE ');
36 END IF;
37 return TRUE;
38 END IF;
39
40 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
41 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,'oke.plsql.'||g_pkg_name||'.'||l_api_name
42 ,'105: Returning TRUE outside conditions');
43 END IF;
44
45 return TRUE;
46
47 END isNewMessageWithinLimit;
48
49
50 PROCEDURE Default_Deliverables (
51 P_Api_Version IN NUMBER
52 , P_Init_Msg_List IN VARCHAR2
53 , P_Update_Yn IN VARCHAR2
54 , P_Header_ID IN NUMBER
55 , P_Line_ID IN NUMBER
56 , X_Return_Status OUT NOCOPY VARCHAR2
57 , X_Msg_Count OUT NOCOPY NUMBER
58 , X_Msg_Data OUT NOCOPY VARCHAR2
59 , X_Counter OUT NOCOPY NUMBER ) IS
60
61 l_api_version CONSTANT NUMBER := 1;
62 l_del_rec oke_deliverable_pvt.del_rec_type;
63 l_del_tbl oke_deliverable_pvt.del_tbl_type;
64 l_api_name CONSTANT VARCHAR2(30) := 'DEFAULT_DELIVERABLES';
65
66 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
67 l_buy_or_sell VARCHAR2(1);
68 l_direction VARCHAR2(3);
69
70 L_Inventory_Org_ID NUMBER;
71 L_Counter NUMBER;
72 L_Total_Counter NUMBER;
73
74
75 CURSOR C IS
76 SELECT DECODE(Buy_Or_Sell, 'B', 'IN', 'OUT') Direction
77 , Inv_Organization_ID
78 FROM okc_k_headers_b
79 WHERE ID = P_Header_ID;
80
81
82 BEGIN
83
84
85
86 -- call START_ACTIVITY to create savepoint, check compatibility
87 -- and initialize message list
88
89 l_return_status := OKE_API.START_ACTIVITY(
90 p_api_name => l_api_name,
91 p_init_msg_list => p_init_msg_list,
92 p_api_type => g_api_type,
93 x_return_status => l_return_status);
94
95
96 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
97 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
98 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
99 raise OKE_API.G_EXCEPTION_ERROR;
100 End If;
101
102 --
103 -- Fetch buy_or_sell to determine defaulted direction for deliverables
104 --
105 OPEN C;
106 FETCH C INTO L_Direction, L_Inventory_Org_ID;
107 CLOSE C;
108
109
110
111
112 IF NVL(P_Update_Yn, 'N') = 'N' THEN /* New deliverable default */
113
114
115
116 IF P_Line_ID > 0 THEN /* Line Default */
117
118
119 Create_New_L (P_Initiate_Msg_List => G_False
120 , X_Return_Status => L_Return_Status
121 , X_Msg_Count => X_Msg_Count
122 , X_Msg_Data => X_Msg_Data
123 , P_Header_ID => P_Header_ID
124 , P_Line_ID => P_Line_ID
125 , P_Direction => L_Direction
126 , P_Inventory_Org_ID => L_Inventory_Org_ID
127 , X_Counter => X_Counter);
128
129
130
131 If (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
132 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
133 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
134 raise OKE_API.G_EXCEPTION_ERROR;
135 End If;
136
137
138
139 ELSE /* Header batch defaulting */
140
141 Create_New ( P_Init_Msg_List => G_False
142 , X_Return_Status => L_Return_Status
143 , X_Msg_Count => X_Msg_Count
144 , X_Msg_Data => X_Msg_Data
145 , P_Header_ID => P_Header_ID
146 , P_Direction => L_Direction
147 , P_Inventory_Org_ID => L_Inventory_Org_ID
148 , X_Counter => X_Counter);
149
150 If (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
151 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
152 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
153 raise OKE_API.G_EXCEPTION_ERROR;
154 End If;
155
156 END IF;
157
158 ELSE /* Mixed defaulting and re-defaulting */
159
160
161 IF P_Line_ID > 0 THEN /* Line re-defaulting */
162
163 Update_Line( P_Init_Msg_List => G_False
164 , X_Return_Status => L_Return_Status
165 , X_Msg_Count => X_Msg_Count
166 , X_Msg_Data => X_Msg_Data
167 , P_Header_ID => P_Header_ID
168 , P_Line_ID => P_Line_ID
169 , P_Direction => L_Direction
170 , P_Inventory_Org_ID => L_Inventory_Org_ID
171 , X_Counter => X_Counter);
172
173 If (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
174 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
175 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
176 raise OKE_API.G_EXCEPTION_ERROR;
177 End If;
178
179 ELSE /* Header batch re-defaulting */
180
181 -- Two step process for both new defaults and re-defaults
182
183 Update_Batch ( P_Init_Msg_List => G_False
184 , X_Return_Status => L_Return_Status
185 , X_Msg_Count => X_Msg_Count
186 , X_Msg_Data => X_Msg_Data
187 , P_Header_ID => P_Header_ID
188 , P_Direction => L_Direction
189 , P_Inventory_Org_ID => L_Inventory_Org_ID
190 , X_Counter => L_Counter);
191
192 If (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
193 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
194 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
195 raise OKE_API.G_EXCEPTION_ERROR;
196 End If;
197
198
199
200 L_Total_Counter := L_Counter;
201
202 Create_New ( P_Init_Msg_List => G_False
203 , X_Return_Status => L_Return_Status
204 , X_Msg_Count => X_Msg_Count
205 , X_Msg_Data => X_Msg_Data
206 , P_Header_ID => P_Header_ID
207 , P_Direction => L_Direction
208 , P_Inventory_Org_ID => L_Inventory_Org_ID
209 , X_Counter => L_Counter);
210
211 If (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
212 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
213 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
214 raise OKE_API.G_EXCEPTION_ERROR;
215 End If;
216
217 L_Total_Counter := L_Total_Counter + L_Counter;
218
219 X_Counter := L_Total_Counter;
220
221 END IF;
222
223 END IF;
224
225 X_Return_Status := L_Return_Status;
226
227 -- end activity
228 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
229 x_msg_data => x_msg_data);
230 EXCEPTION
231 when OKE_API.G_EXCEPTION_ERROR then
232 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
233 p_api_name => l_api_name,
234 p_pkg_name => g_pkg_name,
235 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
236 x_msg_count => x_msg_count,
237 x_msg_data => x_msg_data,
238 p_api_type => g_api_type);
239
240 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
241 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
242 p_api_name => l_api_name,
243 p_pkg_name => g_pkg_name,
244 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
245 x_msg_count => x_msg_count,
246 x_msg_data => x_msg_data,
247 p_api_type => g_api_type);
248
249 when OTHERS then
250 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
251 p_api_name => l_api_name,
252 p_pkg_name => g_pkg_name,
253 p_exc_name => 'OTHERS',
254 x_msg_count => x_msg_count,
255 x_msg_data => x_msg_data,
256 p_api_type => g_api_type);
257
258 END Default_Deliverables;
259
260 PROCEDURE Get_Org (
261 P_Header_ID IN NUMBER
262 , P_Line_ID IN NUMBER
263 , X_Ship_To_ID OUT NOCOPY NUMBER
264 , X_Ship_From_ID OUT NOCOPY NUMBER )IS
265
266 cursor party_csr1(p_id Number,p_code varchar2) is
267 select object1_id1, object1_id2, jtot_object1_code
268 from okc_k_party_roles_b
269 where dnz_chr_id = p_header_id and cle_id = p_id
270 and rle_code = p_code;
271
272 cursor party_csr2(p_id Number,p_code varchar2) is
273 select object1_id1, object1_id2, jtot_object1_code
274 from okc_k_party_roles_b
275 where dnz_chr_id = p_header_id and chr_id = p_id
276 and rle_code = p_code;
277
278 cursor line_party(p_code Varchar2) is
279 select Max(a.level_sequence) from okc_ancestrys a
280 where a.cle_id = p_line_id
281 and exists(select 'x' from okc_k_party_roles_b b where dnz_chr_id = p_header_id and b.cle_id = a.cle_id_ascendant and b.rle_code = p_code and object1_id1 is not null);
282
283 cursor header_party(p_code Varchar2) is
284 select count(*) from okc_k_party_roles_b
285 where dnz_chr_id = p_header_id and chr_id = p_header_id
286 and rle_code = p_code
287 and object1_id1 is not null;
288
289 cursor c is
290 select buy_or_sell from okc_k_headers_b
291 where id = p_header_id;
292
293 cursor top_line is
294 select 'x' from okc_ancestrys
295 where cle_id = p_line_id;
296
297 Cursor Inv_C(P_Id Number) Is
298 Select 'x'
299 From HR_ALL_ORGANIZATION_UNITS hr, MTL_PARAMETERS mp
300 Where hr.Organization_Id = P_Id
301 And mp.Organization_Id = hr.Organization_Id;
302
303
304 l_ship_to_id number;
305 l_ship_from_id number;
306 l_id NUMBER;
307 l_id1 varchar2(40);
308 l_id2 varchar2(200);
309 l_object_code varchar2(30);
310 l_level Number;
311 l_value Varchar2(1);
312 l_found Boolean := TRUE;
313
314 c1info party_csr1%rowtype;
315 c2info party_csr2%rowtype;
316
317 l_row_count number;
318 l_buy_or_sell varchar2(1);
319
320 BEGIN
321
322 select buy_or_sell into l_buy_or_sell
323 from okc_k_headers_b
324 where id = p_header_id;
325
326 IF p_line_id is not null then
327
328 SELECT COUNT(*) INTO l_row_count
329 FROM OKC_K_PARTY_ROLES_B
330 WHERE dnz_chr_id = p_header_id and cle_id = p_line_id
331 and rle_code = 'SHIP_FROM'
332 and object1_id1 is not null;
333
334 if l_row_count = 1 then
335
336 l_id := p_line_id;
337
338 open party_csr1(l_id,'SHIP_FROM');
339 fetch party_csr1 into c1info;
340 close party_csr1;
341
342 l_object_code := c1info.jtot_object1_code;
343 if l_buy_or_sell = 'B' then
344 if l_object_code = 'OKE_VENDSITE' then
345 l_id1 := c1info.object1_id1;
346 end if;
347 else
348 if l_object_code = 'OKX_INVENTORY' then
349 -- only inventory_org will be defaulted down to DTS
350
351 Open Inv_C(c1info.object1_Id1);
352 Fetch Inv_C Into L_Value;
353 Close Inv_C;
354
355 if l_value = 'x' then
356 l_id1 := c1info.object1_id1;
357 end if;
358
359 end if;
360 end if;
361
362 elsif l_row_count = 0 then
363
364 -- if the line is top line, go directly to header, else search parent line
365
366 open top_line;
367 fetch top_line into l_value;
368 l_found := top_line%found;
369 close top_line;
370
371 if l_found then
372
373 open line_party('SHIP_FROM');
374 fetch line_party into l_level;
375 l_found := line_party%found;
376 close line_party;
377
378 end if;
379
380
381
382 if l_level is not null then
383
384 -- check parent line default
385
386 select cle_id_ascendant into l_id
387 from okc_ancestrys
388 where cle_id = p_line_id
389 and level_sequence = l_level;
390
391 select count(*) into l_row_count
392 from okc_k_party_roles_b
393 where dnz_chr_id = p_header_id and cle_id = l_id
394 and rle_code = 'SHIP_FROM';
395
396 if l_row_count = 1 then
397 open party_csr1(l_id, 'SHIP_FROM');
398 fetch party_csr1 into c1info;
399 close party_csr1;
400 l_object_code := c1info.jtot_object1_code;
401
402
403 if l_buy_or_sell = 'B' then
404 if l_object_code = 'OKE_VENDSITE' then
405 l_id1 := c1info.object1_id1;
406 end if;
407 else
408 if l_object_code = 'OKX_INVENTORY' then
409 Open Inv_C(c1info.object1_id1);
410 Fetch Inv_C Into L_Value;
411 Close Inv_C;
412
413 if l_value = 'x' then
414 l_id1 := c1info.object1_id1;
415 end if;
416
417 end if;
418 end if;
419 end if;
420
421 else
422
423 -- check header party for default
424
425 open header_party('SHIP_FROM');
426 fetch header_party into l_level;
427 l_found := header_party%found;
428 close header_party;
429
430 if l_level > 0 then
431
432 if l_level = 1 then
433 open party_csr2(p_header_id, 'SHIP_FROM');
434 fetch party_csr2 into c2info;
435 close party_csr2;
436 l_object_code := c2info.jtot_object1_code;
437
438
439
440 if l_buy_or_sell = 'B' then
441 if l_object_code = 'OKE_VENDSITE' then
442 l_id1 := c2info.object1_id1;
443 end if;
444 else
445 if l_object_code = 'OKX_INVENTORY' then
446 -- only inventory_org will be defaulted down to DTS
447 Open Inv_C(c2info.object1_id1);
448 Fetch Inv_C Into L_Value;
449 Close Inv_C;
450
451 if l_value = 'x' then
452 l_id1 := c2info.object1_id1;
453 end if;
454
455 end if;
456 end if;
457 end if;
458
459 end if;
460 end if;
461 end if;
462 end if;
463
464
465 if l_id1 is not null then
466 l_ship_from_id := to_number(l_id1);
467 l_id1 := null;
468 end if;
469
470
471 select count(*) into l_row_count
472 from okc_k_party_roles_b
473 where dnz_chr_id = p_header_id and cle_id = p_line_id
474 and rle_code = 'SHIP_TO';
475
476 if l_row_count = 1 then
477
478 l_id := p_line_id;
479
480 open party_csr1(l_id,'SHIP_TO');
481 fetch party_csr1 into c1info;
482 close party_csr1;
483
484 l_object_code := c1info.jtot_object1_code;
485 if l_buy_or_sell = 'S' then
486 if l_object_code = 'OKE_SHIPTO' then
487
488 l_id1 := c1info.object1_id1;
489 end if;
490 else
491 if l_object_code = 'OKX_INVENTORY' then
492
493 -- only inventory_org will be defaulted down to DTS
494
495 Open Inv_C(c1info.object1_id1);
496 Fetch Inv_C Into L_Value;
497 Close Inv_C;
498
499 if l_value = 'x' then
500 l_id1 := c1info.object1_id1;
501 end if;
502
503 end if;
504 end if;
505
506 elsif l_row_count = 0 then
507
508
509 open line_party('SHIP_TO');
510 fetch line_party into l_level;
511 l_found := line_party%found;
512 close line_party;
513
514 if l_level is not null then
515
516
517 -- check parent line default
518
519 select cle_id_ascendant into l_id
520 from okc_ancestrys
521 where cle_id = p_line_id
522 and level_sequence = l_level;
523
524 select count(*) into l_row_count
525 from okc_k_party_roles_b
526 where dnz_chr_id = p_header_id and cle_id = l_id
527 and rle_code = 'SHIP_TO';
528
529 if l_row_count = 1 then
530 open party_csr1(l_id, 'SHIP_TO');
531 fetch party_csr1 into c1info;
532 close party_csr1;
533 l_object_code := c1info.jtot_object1_code;
534
535 if l_buy_or_sell = 'S' then
536 if l_object_code = 'OKE_SHIPTO' then
537
538 l_id1 := c1info.object1_id1;
539 end if;
540 else
541 if l_object_code = 'OKX_INVENTORY' then
542 -- only inventory_org will be defaulted down to DTS
543 Open Inv_C(c1info.object1_id1);
544 Fetch Inv_C Into L_Value;
545 Close Inv_C;
546
547 if l_value = 'x' then
548 l_id1 := c1info.object1_id1;
549 end if;
550
551 end if;
552 end if;
553 end if;
554
555 else
556
557
558 -- check header party for default
559
560 open header_party('SHIP_TO');
561 fetch header_party into l_level;
562 l_found := header_party%found;
563 close header_party;
564
565 if l_found then
566
567 if l_level = 1 then
568 open party_csr2(p_header_id, 'SHIP_TO');
569 fetch party_csr2 into c2info;
570 close party_csr2;
571
572 l_object_code := c2info.jtot_object1_code;
573 if l_buy_or_sell = 'S' then
574 if l_object_code = 'OKE_SHIPTO' then
575 l_id1 := c2info.object1_id1;
576 end if;
577 else
578 if l_object_code = 'OKX_INVENTORY' then
579 -- only inventory_org will be defaulted down to DTS
580 Open Inv_C(c2info.object1_id1);
581 Fetch Inv_C Into L_Value;
582 Close Inv_C;
583
584 if l_value = 'x' then
585 l_id1 := c2info.object1_id1;
586 end if;
587
588 end if;
589 end if;
590
591 end if;
592
593 end if;
594
595 end if;
596
597 end if;
598
599 if l_id1 is not null then
600
601 l_ship_to_id := to_number(l_id1);
602 l_id1 := null;
603 end if;
604
605 x_ship_to_id := l_ship_to_id;
606 x_ship_from_id := l_ship_from_id;
607
608 END Get_Org;
609
610
611 PROCEDURE Verify_Defaults (
612 P_Line_ID IN NUMBER
613 , X_Msg_1 OUT NOCOPY VARCHAR2
614 , X_Msg_2 OUT NOCOPY VARCHAR2
615 , X_Msg_3 OUT NOCOPY VARCHAR2
616 , X_Return_Status OUT NOCOPY VARCHAR2
617 , P_Calling_Level IN VARCHAR2) IS
618
619 L_Count NUMBER;
620 L_MDS VARCHAR2(1);
621 L_REQ VARCHAR2(1);
622 L_WSH VARCHAR2(1);
623 L_BIL VARCHAR2(1);
624 L_Msg VARCHAR2(2000);
625 L_Msg2 VARCHAR2(2000);
626 L_Msg3 VARCHAR2(2000);
627 L_Return_Status VARCHAR2(1);
628 L_Completed VARCHAR2(1);
629
630 CURSOR Exist_C IS
631 SELECT Count(*)
632 FROM oke_k_deliverables_b
633 WHERE K_Line_ID = P_Line_ID
634 AND NVL(Defaulted_Flag, 'N') = 'Y';
635
636 CURSOR Qualify_C IS
637 SELECT NVL(Create_Demand, 'N')
638 , NVL(Ready_To_Procure, 'N')
639 , NVL(Available_For_Ship_Flag, 'N')
640 , NVL(Ready_To_Bill, 'N')
641 , NVL(Completed_Flag, 'N')
642 FROM oke_k_deliverables_b
643 WHERE K_Line_ID = P_Line_ID
644 AND NVL(Defaulted_Flag, 'N') = 'Y';
645
646 BEGIN
647
648 --
649 -- Check if deliverable(s) exist for the deliverable, create new or update existing
650 -- Return status with value 'N' for new deliverable, 'U' for update deliverable, 'S' for split deliverable
651 --
652
653 OPEN Exist_C;
654 FETCH Exist_C INTO L_Count;
655 CLOSE Exist_C;
656
657 IF L_Count = 0 THEN /* No existing deliverable, create new */
658
659
660 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_CREATE_NEW');
661 X_Msg_1 := FND_MESSAGE.Get;
662
663 L_Return_Status := 'N';
664
665
666 ELSIF L_Count = 1 THEN /* Defaulted previously but not splited */
667
668 -- IF Actions initiated for the deliverable, prompt the user for the updating of other application data
669 -- once the deliverable is overiden by the changes.MDS entries will be updated as well.
670
671
672 OPEN Qualify_C;
673 FETCH Qualify_C INTO L_Mds, L_Req, L_Wsh, L_Bil, L_Completed;
674 CLOSE Qualify_C;
675
676 IF L_Req = 'Y' THEN
677
678 IF P_Calling_Level = 'L' THEN
679 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_REQ');
680 ELSE
681 FND_MESSAGE.Set_Name('OKE', 'OKE_H_DTS_DATA_REQ');
682 END IF;
683
684 L_Msg := FND_MESSAGE.Get;
685 L_Msg3 := 'REQ';
686
687 ELSIF L_Wsh = 'Y' THEN
688
689 IF P_Calling_Level = 'L' THEN
690 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_WSH');
691 ELSE
692 FND_MESSAGE.Set_Name('OKE', 'OKE_H_DTS_DATA_WSH');
693 END IF;
694
695 L_Msg := FND_MESSAGE.Get;
696 L_Msg3 := 'WSH';
697
698 ELSIF L_Bil = 'Y' THEN
699
700 IF P_Calling_Level = 'L' THEN
701 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_BILL');
702 ELSE
703 FND_MESSAGE.Set_Name('OKE', 'OKE_H_DTS_DATA_BILL');
704 END IF;
705
706 L_Msg := FND_MESSAGE.Get;
707 L_Msg3 := 'BIL';
708
709 ELSIF L_Completed = 'Y' THEN
710
711 IF P_Calling_Level = 'L' THEN
712 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_COMPLETED');
713 ELSE
714 FND_MESSAGE.Set_Name('OKE', 'OKE_H_DTS_DATA_COMPLETED');
715 END IF;
716
717 L_Msg := FND_MESSAGE.Get;
718 L_Msg3 := 'COM';
719
720 ELSIF L_Mds = 'Y' THEN
721
722 FND_MESSAGE.Set_Name('OKE', 'OKE_MISS_DATA_MDS');
723 L_Msg2 := FND_MESSAGE.Get;
724
725 END IF;
726
727 X_Msg_1 := L_Msg;
728 X_Msg_2 := L_Msg2;
729 X_Msg_3 := L_Msg3;
730 L_Return_Status := 'U';
731
732 ELSE /* Splited deliverable, not qualify for auto-update */
733
734
735 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_SPLIT');
736 L_Msg := FND_MESSAGE.Get;
737 X_Msg_1 := L_Msg;
738 X_Msg_3 := 'SPL';
739 L_Return_Status := 'S';
740
741 END IF;
742
743 X_Return_Status := L_Return_Status;
744
745
746 END Verify_Defaults;
747
748 PROCEDURE Convert_Value(P_Header_ID NUMBER
749 , P_Line_ID NUMBER
750 , P_Direction VARCHAR2
751 , X_Ship_To_Org_ID OUT NOCOPY NUMBER
752 , X_Ship_To_ID OUT NOCOPY NUMBER
753 , X_Ship_From_Org_ID OUT NOCOPY NUMBER
754 , X_Ship_From_ID OUT NOCOPY NUMBER
755 , X_Inv_Org_ID OUT NOCOPY NUMBER) IS
756
757 L_Ship_To_ID NUMBER;
758 L_Ship_From_ID NUMBER;
759 L_ID NUMBER;
760 L_Buy_Or_Sell VARCHAR2(1);
761 L_Status VARCHAR2(1);
762
763 CURSOR C(P_ID NUMBER) IS
764 SELECT Cust_Account_ID, status
765 FROM oke_cust_site_uses_v
766 WHERE ID1 = P_ID;
767
768 CURSOR Buy_Or_Sell ( P_ID NUMBER ) IS
769 SELECT Buy_Or_Sell
770 FROM OKC_K_HEADERS_B
771 WHERE ID = P_ID;
772
773 CURSOR Ven_C ( P_ID NUMBER ) IS
774 SELECT Vendor_ID, status
775 FROM oke_vendor_sites_v
776 WHERE ID1 = P_ID;
777
778 FUNCTION Get_Inv_Loc_Id( P_ID NUMBER ) RETURN NUMBER
779 IS
780 L_ID NUMBER;
781 CURSOR Def_Loc_C IS
782 SELECT location_id
783 FROM hr_organization_units
784 WHERE ORGANIZATION_ID = p_ID;
785 CURSOR Inv_Loc_C IS
786 SELECT ID1
787 FROM okx_locations_v
788 WHERE Organization_ID = P_ID AND status='A'
789 ORDER BY ID1;
790 BEGIN
791 OPEN Def_Loc_C;
792 FETCH Def_Loc_C INTO L_ID;
793 CLOSE Def_Loc_C;
794 IF l_ID IS NULL THEN
795 OPEN Inv_Loc_C;
796 FETCH Inv_Loc_C INTO L_ID;
797 CLOSE Inv_Loc_C;
798 END IF;
799 RETURN L_ID;
800 END Get_Inv_Loc_Id;
801
802 BEGIN
803
804
805 -- Get defaultable ship_to, ship_from from authoring, if org exists, location
806 -- will be derived from org, if multiple, first one fits
807 -- org can be derived from locations as well
808
809
810 Get_Org(P_Header_ID, P_Line_ID, L_Ship_To_ID, L_Ship_From_ID);
811
812 -- Party roles defined in Authoring follows the rules:
813 -- Except for customer_account and customer sites, rest are org only
814 -- Locations derived from the org will be the first location order by id
815 -- Will be taken off if new defaulting changes in Authoring
816 -- Add to accomemdate changes in Authoring, for buy contract, ship from
817 -- is vendor site
818
819 IF L_Ship_To_ID > 0 THEN
820
821 IF P_Direction = 'OUT' THEN
822
823 -- If direction is Out, since RMA is not supported at this moment, so
824 -- Ship From will always be Inventory Org, Ship To will always be the customer
825
826 L_ID := NULL;
827 OPEN C( L_Ship_To_ID );
828 FETCH C INTO L_ID, L_Status;
829 CLOSE C;
830
831 X_Ship_To_Org_ID := L_ID;
832 IF L_Status <> 'A' THEN
833 X_Ship_To_ID := NULL;
834 ELSE
835 X_Ship_To_ID := L_Ship_To_ID;
836 END IF;
837
838 ELSIF P_Direction = 'IN' THEN
839
840 -- If direction is In, the Ship To will always be the inventory Org
841
842 X_Inv_Org_ID := L_Ship_To_ID;
843 X_Ship_To_Org_ID := L_Ship_To_ID;
844 X_Ship_To_ID := Get_Inv_Loc_Id( L_Ship_To_ID );
845
846 END IF;
847
848 END IF;
849
850 IF L_Ship_From_ID > 0 THEN
851
852 IF P_Direction = 'OUT' THEN
853
854 X_Inv_Org_ID := L_Ship_From_ID;
855 X_Ship_From_Org_ID := L_Ship_From_ID;
856 X_Ship_From_ID := Get_Inv_Loc_Id( L_Ship_From_ID );
857
858 ELSIF P_Direction = 'IN' THEN
859
860 OPEN Buy_Or_Sell ( P_Header_ID );
861 FETCH Buy_Or_Sell INTO L_Buy_Or_Sell;
862 CLOSE Buy_Or_Sell;
863
864 IF L_Buy_Or_Sell = 'B' THEN
865
866 L_ID := NULL;
867 OPEN Ven_C( L_Ship_From_ID );
868 FETCH Ven_C INTO L_ID, L_Status;
869 CLOSE Ven_C;
870
871 X_Ship_From_Org_ID := L_ID;
872 IF L_Status <> 'A' THEN
873 X_Ship_From_ID := NULL;
874 ELSE
875 X_Ship_From_ID := L_Ship_From_ID;
876 END IF;
877
878 END IF;
879
880 END IF;
881
882 END IF;
883
884
885 END Convert_Value;
886
887 /* bug 3820544 new procedure to determine of org needs to be defaulted
888 if the item does not exist in the org, we cannot default the org
889 RETURNS 'Y' if can default, 'N' if otherwise */
890
891 Function Check_Org_Items( L_Inventory_item_id NUMBER,
892 L_Inventory_org NUMBER
893 ) RETURN VARCHAR2 IS
894
895 CURSOR check_item IS
896 SELECT 'x'
897 FROM MTL_SYSTEM_ITEMS
898 WHERE inventory_item_id = L_inventory_item_id
899 AND organization_id = L_inventory_org;
900
901 L_check VARCHAR2(1) := '?';
902
903 BEGIN
904 OPEN check_item;
905 FETCH check_item INTO l_check;
906 CLOSE check_item;
907
908 IF L_check = 'x' THEN
909 RETURN 'Y';
910 END IF;
911 RETURN 'N';
912 END Check_Org_items;
913
914
915
916 PROCEDURE Create_New_L (P_Initiate_Msg_List IN VARCHAR2
917 , X_Return_Status OUT NOCOPY VARCHAR2
918 , X_Msg_Count OUT NOCOPY NUMBER
919 , X_Msg_Data OUT NOCOPY VARCHAR2
920 , P_Header_ID IN NUMBER
921 , P_Line_ID IN NUMBER
922 , P_Direction IN VARCHAR2
923 , P_Inventory_Org_ID IN NUMBER
924 , X_Counter OUT NOCOPY NUMBER) IS
925
926 L_Api_Name CONSTANT VARCHAR2(30) := 'Create_New_L';
927 L_Api_Version CONSTANT NUMBER := 1;
928 L_Return_Status VARCHAR2(1) := OKE_API.G_Ret_Sts_Success;
929 L_Del_Rec OKE_DELIVERABLE_PVT.Del_Rec_Type;
930 X_Del_Rec OKE_DELIVERABLE_PVT.Del_Rec_Type;
931 L_Ship_From_Org_ID NUMBER;
932 L_Ship_From_Location_ID NUMBER;
933 L_Ship_To_Org_ID NUMBER;
934 L_Ship_To_Location_ID NUMBER;
935 L_Inv_Org_ID NUMBER;
936
937
938 cursor new_l_c(p_id NUMBER) is
939 select l.k_line_id,
940 l.line_number,
941 l.project_id,
942 l.inventory_item_id,
943 SUBSTR(l.line_description, 1, 240) line_description,
944 l.delivery_date,
945 l.status_code,
946 l.start_date,
947 l.end_date,
948 k.priority_code,
949 h.currency_code,
950 DECODE(h.buy_or_sell, 'B', 'IN', 'OUT') Direction,
951 l.unit_price,
952 l.uom_code,
953 l.line_quantity,
954 k.country_of_origin_code,
955 l.subcontracted_flag,
956 l.billable_flag,
957 l.drop_shipped_flag,
958 -- l.completed_flag,
959 l.shippable_flag,
960 l.cfe_flag,
961 l.inspection_req_flag,
962 l.interim_rpt_req_flag,
963 l.customer_approval_req_flag,
964 l.as_of_date,
965 l.date_of_first_submission,
966 l.frequency,
967 l.data_item_subtitle,
968 l.copies_required,
969 l.cdrl_category,
970 l.data_item_name,
971 l.export_flag
972 from oke_k_lines_v l, okc_k_headers_b h, oke_k_headers k
973 where h.id = l.header_id
974 and l.k_line_id = p_id
975 and h.id = k.k_header_id;
976
977 New_L_Rec New_L_C%ROWTYPE;
978
979 BEGIN
980
981 l_return_status := OKE_API.START_ACTIVITY(
982 p_api_name => l_api_name,
983 p_init_msg_list => p_initiate_msg_list,
984 p_api_type => g_api_type,
985 x_return_status => l_return_status);
986
987
988 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
989 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
990 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
991 raise OKE_API.G_EXCEPTION_ERROR;
992 End If;
993
994 OPEN New_L_C(P_Line_ID);
995 FETCH New_L_C INTO New_L_Rec;
996 CLOSE New_L_C;
997
998
999
1000 Convert_Value(P_Header_ID
1001 , P_Line_ID
1002 , P_Direction
1003 , L_Ship_To_Org_ID
1004 , L_Ship_To_Location_ID
1005 , L_Ship_From_Org_ID
1006 , L_Ship_From_Location_ID
1007 , L_Inv_Org_ID);
1008
1009 IF new_l_rec.inventory_item_id is not null THEN -- need to check inventory
1010 IF P_Direction = 'IN' THEN -- need to check ship to location
1011 IF Check_Org_Items(new_l_rec.inventory_item_id,l_ship_to_org_id) = 'N' THEN
1012 L_ship_to_org_id := NULL;
1013 L_ship_to_location_id := NULL;
1014 END IF;
1015 ELSIF P_Direction = 'OUT' THEN -- need to check ship from location
1016 IF Check_Org_Items(new_l_rec.inventory_item_id,l_ship_from_org_id) = 'N' THEN
1017 L_ship_from_org_id := NULL;
1018 L_ship_from_location_id := NULL;
1019 END IF;
1020 END IF;
1021 END IF;
1022
1023 IF L_Inv_Org_ID IS NULL THEN
1024
1025 L_Inv_Org_ID := P_Inventory_Org_ID;
1026
1027 END IF;
1028
1029 L_DEL_REC.k_line_id := P_Line_ID;
1030 L_DEL_REC.defaulted_flag := 'Y';
1031 L_DEL_REC.direction := P_Direction;
1032 L_DEL_REC.k_header_id := P_Header_ID;
1033 L_DEL_REC.Inventory_Org_ID := L_Inv_Org_ID;
1034 L_DEL_REC.Ship_To_Org_ID := L_Ship_To_Org_ID;
1035 L_DEL_REC.Ship_To_Location_ID := L_Ship_To_Location_ID;
1036 L_DEL_REC.Ship_From_Org_ID := L_Ship_From_Org_ID;
1037 L_DEL_REC.Ship_From_Location_ID := L_Ship_From_Location_ID;
1038 L_DEL_REC.deliverable_num := NULL; /* Use numbering package to generate new number */
1039 L_DEL_REC.project_id := NEW_L_REC.project_id;
1040 L_DEL_REC.item_id := NEW_L_REC.inventory_item_id;
1041 L_DEL_REC.description := NEW_L_REC.line_description;
1042 L_DEL_REC.delivery_date := NEW_L_REC.delivery_date;
1043 L_DEL_REC.status_code := NEW_L_REC.status_code;
1044 L_DEL_REC.start_date := NEW_L_REC.start_date;
1045 L_DEL_REC.end_date := NEW_L_REC.end_date;
1046 L_DEL_REC.priority_code := NEW_L_REC.priority_code;
1047 L_DEL_REC.currency_code := NEW_L_REC.currency_code;
1048 L_DEL_REC.unit_price := NEW_L_REC.unit_price;
1049 L_DEL_REC.uom_code := NEW_L_REC.uom_code;
1050 L_DEL_REC.quantity := NEW_L_REC.line_quantity;
1051 L_DEL_REC.country_of_origin_code := NEW_L_REC.country_of_origin_code;
1052 L_DEL_REC.subcontracted_flag := NEW_L_REC.subcontracted_flag;
1053 L_DEL_REC.billable_flag := NEW_L_REC.billable_flag;
1054 L_DEL_REC.drop_shipped_flag := NEW_L_REC.drop_shipped_flag;
1055 -- L_DEL_REC.completed_flag := NEW_L_REC.completed_flag;
1056 L_DEL_REC.shippable_flag := NEW_L_REC.shippable_flag;
1057 L_DEL_REC.cfe_req_flag := NEW_L_REC.cfe_flag;
1058 L_DEL_REC.inspection_req_flag := NEW_L_REC.inspection_req_flag;
1059 L_DEL_REC.interim_rpt_req_flag := NEW_L_REC.interim_rpt_req_flag;
1060 L_DEL_REC.customer_approval_req_flag := NEW_L_REC.customer_approval_req_flag;
1061 L_DEL_REC.as_of_date := NEW_L_REC.as_of_date;
1062 L_DEL_REC.date_of_first_submission := NEW_L_REC.date_of_first_submission;
1063 L_DEL_REC.frequency := NEW_L_REC.frequency;
1064 L_DEL_REC.data_item_subtitle := NEW_L_REC.data_item_subtitle;
1065 L_DEL_REC.total_num_of_copies := NEW_L_REC.copies_required;
1066 L_DEL_REC.cdrl_category := NEW_L_REC.cdrl_category;
1067 L_DEL_REC.data_item_name := NEW_L_REC.data_item_name;
1068 L_DEL_REC.export_flag := NEW_L_REC.export_flag;
1069
1070 -- default destination type code if for inbound deliverable
1071
1072 IF P_Direction = 'IN' THEN
1073
1074 IF L_DEL_REC.Item_ID IS NOT NULL THEN
1075
1076 L_DEL_REC.Destination_Type_Code := 'INVENTORY';
1077
1078 END IF;
1079
1080 END IF;
1081
1082 -- Create new deliverable
1083
1084 OKE_CONTRACT_PUB.create_deliverable(
1085 p_api_version => l_api_version,
1086 p_init_msg_list => p_initiate_msg_list,
1087 x_return_status => l_return_status,
1088 x_msg_count => x_msg_count,
1089 x_msg_data => x_msg_data,
1090 p_del_rec => l_del_rec,
1091 x_del_rec => x_del_rec);
1092
1093
1094 If (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1095 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1096 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1097 raise OKE_API.G_EXCEPTION_ERROR;
1098 End If;
1099
1100 X_Counter := 1;
1101 X_Return_Status := L_Return_Status;
1102
1103 -- end activity
1104 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1105 x_msg_data => x_msg_data);
1106
1107 EXCEPTION
1108 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1109
1110 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1111 (
1112 l_api_name,
1113 G_PKG_NAME,
1114 'OKE_API.G_RET_STS_ERROR',
1115 x_msg_count,
1116 x_msg_data,
1117 g_api_type
1118 );
1119 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1120
1121 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1122 (
1123 l_api_name,
1124 G_PKG_NAME,
1125 'OKE_API.G_RET_STS_UNEXP_ERROR',
1126 x_msg_count,
1127 x_msg_data,
1128 g_api_type
1129 );
1130 WHEN OTHERS THEN
1131
1132 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1133 (
1134 l_api_name,
1135 G_PKG_NAME,
1136 'OTHERS',
1137 x_msg_count,
1138 x_msg_data,
1139 g_api_type
1140 );
1141
1142 END Create_New_L;
1143
1144 PROCEDURE Create_New ( P_Init_Msg_List VARCHAR2
1145 , X_Return_Status OUT NOCOPY VARCHAR2
1146 , X_Msg_Count OUT NOCOPY NUMBER
1147 , X_Msg_Data OUT NOCOPY VARCHAR2
1148 , P_Header_ID IN NUMBER
1149 , P_Direction IN VARCHAR2
1150 , P_Inventory_Org_ID IN NUMBER
1151 , X_Counter OUT NOCOPY NUMBER) IS
1152
1153 L_Api_Name CONSTANT VARCHAR2(30) := 'Create_New_L';
1154 L_Api_Verson CONSTANT NUMBER := 1;
1155 L_Return_Status VARCHAR2(1) := OKE_API.G_Ret_Sts_Success;
1156 X_Del_Rec OKE_DELIVERABLE_PVT.Del_Rec_Type;
1157 L_Del_Rec OKE_DELIVERABLE_PVT.Del_Rec_Type;
1158 L_Del_Tbl OKE_DELIVERABLE_PVT.Del_Tbl_Type;
1159 X_Del_Tbl OKE_DELIVERABLE_PVT.Del_Tbl_Type;
1160 L_Counter NUMBER;
1161
1162 cursor new_c(p_id NUMBER) is
1163 select l.k_line_id,
1164 l.line_number,
1165 l.project_id,
1166 l.inventory_item_id,
1167 substr(l.line_description, 1, 240) line_description,
1168 l.delivery_date,
1169 l.status_code,
1170 l.start_date,
1171 l.end_date,
1172 k.priority_code,
1173 h.currency_code,
1174 DECODE(h.buy_or_sell, 'B', 'IN', 'OUT') Direction,
1175 l.unit_price,
1176 l.uom_code,
1177 l.line_quantity,
1178 k.country_of_origin_code,
1179 l.subcontracted_flag,
1180 l.billable_flag,
1181 l.drop_shipped_flag,
1182 l.completed_flag,
1183 l.shippable_flag,
1184 l.cfe_flag,
1185 l.inspection_req_flag,
1186 l.interim_rpt_req_flag,
1187 l.customer_approval_req_flag,
1188 l.as_of_date,
1189 l.date_of_first_submission,
1190 l.frequency,
1191 l.data_item_subtitle,
1192 l.copies_required,
1193 l.cdrl_category,
1194 l.data_item_name,
1195 l.export_flag
1196 from oke_k_lines_v l, okc_k_headers_b h, oke_k_headers k
1197 where h.id = p_id
1198 and l.header_id = p_id
1199 and h.id = k.k_header_id
1200 and not exists (select 'x' from oke_k_deliverables_b where k_line_id = l.k_line_id and nvl(defaulted_flag, 'N') = 'Y')
1201 and not exists (select 'x' from okc_k_lines_b s where s.cle_id = l.k_line_id)
1202 and exists (select 'x' from okc_assents a
1203 where a.opn_code = 'CREATE_DELV'
1204 and a.sts_code = l.status_code
1205 and a.scs_code = 'PROJECT'
1206 and a.allowed_yn = 'Y');
1207
1208
1209 BEGIN
1210
1211 l_return_status := OKE_API.START_ACTIVITY(
1212 p_api_name => l_api_name,
1213 p_init_msg_list => p_init_msg_list,
1214 p_api_type => g_api_type,
1215 x_return_status => l_return_status);
1216
1217 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1218 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1219 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1220 raise OKE_API.G_EXCEPTION_ERROR;
1221 End If;
1222
1223 L_Counter := 0;
1224
1225 FOR New_C_Rec IN New_C(P_Header_ID) LOOP
1226
1227
1228 Create_New_L (P_Initiate_Msg_List => G_False
1229 , X_Return_Status => L_Return_Status
1230 , X_Msg_Count => X_Msg_Count
1231 , X_Msg_Data => X_Msg_Data
1232 , P_Header_ID => P_Header_ID
1233 , P_Line_ID => NEW_C_REC.K_Line_ID
1234 , P_Direction => P_Direction
1235 , P_Inventory_Org_ID => P_Inventory_Org_ID
1236 , X_Counter => X_Counter);
1237
1238 IF L_Return_Status <> OKE_API.G_Ret_Sts_Success THEN
1239 IF L_Return_Status <> OKE_API.G_Ret_Sts_Unexp_Error THEN
1240 L_Return_Status := X_Return_Status;
1241 END IF;
1242 END IF;
1243
1244 L_Counter := L_Counter + 1;
1245
1246 END LOOP;
1247
1248 X_Return_Status := L_Return_Status;
1249 X_Counter := L_Counter;
1250
1251 -- end activity
1252 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1253 x_msg_data => x_msg_data);
1254
1255
1256 EXCEPTION
1257 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1258
1259 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1260 (
1261 l_api_name,
1262 G_PKG_NAME,
1263 'OKE_API.G_RET_STS_ERROR',
1264 x_msg_count,
1265 x_msg_data,
1266 g_api_type
1267 );
1268 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1269
1270 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1271 (
1272 l_api_name,
1273 G_PKG_NAME,
1274 'OKE_API.G_RET_STS_UNEXP_ERROR',
1275 x_msg_count,
1276 x_msg_data,
1277 g_api_type
1278 );
1279 WHEN OTHERS THEN
1280
1281 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1282 (
1283 l_api_name,
1284 G_PKG_NAME,
1285 'OTHERS',
1286 x_msg_count,
1287 x_msg_data,
1288 g_api_type
1289 );
1290
1291 END Create_New;
1292
1293 PROCEDURE Update_Line( P_Init_Msg_List VARCHAR2
1294 , X_Return_Status OUT NOCOPY VARCHAR2
1295 , X_Msg_Count OUT NOCOPY NUMBER
1296 , X_Msg_Data OUT NOCOPY VARCHAR2
1297 , P_Header_ID IN NUMBER
1298 , P_Line_ID IN NUMBER
1299 , P_Direction IN VARCHAR2
1300 , P_Inventory_Org_ID IN NUMBER
1301 , X_Counter OUT NOCOPY NUMBER) IS
1302
1303 L_Api_Name CONSTANT VARCHAR2(30) := 'Update_Line';
1304 L_Api_Version CONSTANT NUMBER := 1;
1305 L_Return_Status VARCHAR2(1) := OKE_API.G_Ret_Sts_Success;
1306 L_Del_Rec OKE_DELIVERABLE_PVT.Del_Rec_Type;
1307 L_Del_Tbl OKE_DELIVERABLE_PVT.Del_Tbl_Type;
1308 X_Del_Rec OKE_DELIVERABLE_PVT.Del_Rec_Type;
1309 X_Del_Tbl OKE_DELIVERABLE_PVT.Del_Tbl_Type;
1310 L_Counter NUMBER;
1311 L_Direction VARCHAR2(30);
1312 L_Ship_From_Org_ID NUMBER;
1313 L_Ship_From_Location_ID NUMBER;
1314 L_Ship_To_Org_ID NUMBER;
1315 L_Ship_To_Location_ID NUMBER;
1316 L_Inv_Org_ID NUMBER;
1317 L_MDS_ID NUMBER;
1318 L_Out_ID NUMBER;
1319
1320
1321 CURSOR Update_L_C ( P_ID NUMBER ) IS
1322 SELECT l.line_number,
1323 l.project_id,
1324 l.inventory_item_id,
1325 SUBSTR(l.line_description, 1, 240) line_description,
1326 l.delivery_date,
1327 l.status_code,
1328 l.start_date,
1329 l.end_date,
1330 k.priority_code,
1331 h.currency_code,
1332 DECODE(h.buy_or_sell, 'B', 'IN', 'OUT') Direction,
1333 l.unit_price,
1334 l.uom_code,
1335 l.line_quantity,
1336 k.country_of_origin_code,
1337 l.subcontracted_flag,
1338 l.billable_flag,
1339 l.drop_shipped_flag,
1340 -- l.completed_flag,
1341 l.shippable_flag,
1342 l.cfe_flag,
1343 l.inspection_req_flag,
1344 l.interim_rpt_req_flag,
1345 l.customer_approval_req_flag,
1346 l.as_of_date,
1347 l.date_of_first_submission,
1348 l.frequency,
1349 l.data_item_subtitle,
1350 l.copies_required,
1351 l.cdrl_category,
1352 l.data_item_name,
1353 l.export_flag
1354 from oke_k_lines_v l, okc_k_headers_b h, oke_k_headers k
1355 where l.k_line_id = p_id
1356 and h.id = l.header_id
1357 and k.k_header_id = l.header_id;
1358
1359 CURSOR C IS
1360 SELECT Deliverable_ID, Mps_Transaction_ID
1361 FROM oke_k_deliverables_b
1362 WHERE K_Line_ID = P_Line_ID
1363 AND NVL(Defaulted_Flag, 'N') = 'Y';
1364
1365 Update_L_Rec Update_L_C%ROWTYPE;
1366
1367 BEGIN
1368
1369 l_return_status := OKE_API.START_ACTIVITY(
1370 p_api_name => l_api_name,
1371 p_init_msg_list => p_init_msg_list,
1372 p_api_type => g_api_type,
1373 x_return_status => l_return_status);
1374
1375
1376 OPEN Update_L_C(P_Line_ID);
1377 FETCH Update_L_C INTO Update_L_Rec;
1378 CLOSE Update_L_C;
1379
1380 Convert_Value(P_Header_ID
1381 , P_Line_ID
1382 , P_Direction
1383 , L_Ship_To_Org_ID
1384 , L_Ship_To_Location_ID
1385 , L_Ship_From_Org_ID
1386 , L_Ship_From_Location_ID
1387 , L_Inv_Org_ID);
1388
1389 IF Update_L_Rec.inventory_item_id is not null THEN -- need to check inventory
1390 IF P_Direction = 'IN' THEN -- need to check ship to location
1391 IF Check_Org_Items(update_l_rec.inventory_item_id,l_ship_to_org_id) = 'N' THEN
1392 L_ship_to_org_id := NULL;
1393 L_ship_to_location_id := NULL;
1394 END IF;
1395 ELSIF P_Direction = 'OUT' THEN -- need to check ship from location
1396 IF Check_Org_Items(update_l_rec.inventory_item_id,l_ship_from_org_id) = 'N' THEN
1397 L_ship_from_org_id := NULL;
1398 L_ship_from_location_id := NULL;
1399 END IF;
1400 END IF;
1401 END IF;
1402
1403 IF L_Inv_Org_ID IS NULL THEN
1404
1405 L_Inv_Org_ID := P_Inventory_Org_ID;
1406
1407 END IF;
1408
1409 -- Get deliverable_ID, assume that splited/newly created deliverale
1410 -- have been filtered out by batch process/line update
1411
1412 OPEN C;
1413 FETCH C INTO L_DEL_REC.deliverable_ID, L_MDS_ID;
1414 CLOSE C;
1415
1416 L_DEL_REC.defaulted_flag := 'Y';
1417 L_DEL_REC.direction := P_Direction;
1418 L_DEL_REC.k_header_id := P_Header_ID;
1419 L_DEL_REC.Inventory_Org_ID := L_Inv_Org_ID;
1420 L_DEL_REC.Ship_To_Org_ID := L_Ship_To_Org_ID;
1421 L_DEL_REC.Ship_To_Location_ID := L_Ship_To_location_ID;
1422 L_DEL_REC.Ship_From_Org_ID := L_Ship_From_Org_ID;
1423 L_DEL_REC.Ship_From_Location_ID := L_Ship_From_location_ID;
1424 L_DEL_REC.project_id := UPDATE_L_REC.project_id;
1425 L_DEL_REC.item_id := UPDATE_L_REC.inventory_item_id;
1426 L_DEL_REC.description := UPDATE_L_REC.line_description;
1427 L_DEL_REC.delivery_date := UPDATE_L_REC.delivery_date;
1428 L_DEL_REC.status_code := UPDATE_L_REC.status_code;
1429 L_DEL_REC.start_date := UPDATE_L_REC.start_date;
1430 L_DEL_REC.end_date := UPDATE_L_REC.end_date;
1431 L_DEL_REC.priority_code := UPDATE_L_REC.priority_code;
1432 L_DEL_REC.currency_code := UPDATE_L_REC.currency_code;
1433 L_DEL_REC.unit_price := UPDATE_L_REC.unit_price;
1434 L_DEL_REC.uom_code := UPDATE_L_REC.uom_code;
1435 L_DEL_REC.quantity := UPDATE_L_REC.line_quantity;
1436 L_DEL_REC.country_of_origin_code := UPDATE_L_REC.country_of_origin_code;
1437 L_DEL_REC.subcontracted_flag := UPDATE_L_REC.subcontracted_flag;
1438 L_DEL_REC.billable_flag := UPDATE_L_REC.billable_flag;
1439 L_DEL_REC.drop_shipped_flag := UPDATE_L_REC.drop_shipped_flag;
1440 -- L_DEL_REC.completed_flag := UPDATE_L_REC.completed_flag;
1441 L_DEL_REC.shippable_flag := UPDATE_L_REC.shippable_flag;
1442 L_DEL_REC.cfe_req_flag := UPDATE_L_REC.cfe_flag;
1443 L_DEL_REC.inspection_req_flag := UPDATE_L_REC.inspection_req_flag;
1444 L_DEL_REC.interim_rpt_req_flag := UPDATE_L_REC.interim_rpt_req_flag;
1445 L_DEL_REC.customer_approval_req_flag := UPDATE_L_REC.customer_approval_req_flag;
1446 L_DEL_REC.as_of_date := UPDATE_L_REC.as_of_date;
1447 L_DEL_REC.date_of_first_submission := UPDATE_L_REC.date_of_first_submission;
1448 L_DEL_REC.frequency := UPDATE_L_REC.frequency;
1449 L_DEL_REC.data_item_subtitle := UPDATE_L_REC.data_item_subtitle;
1450 L_DEL_REC.total_num_of_copies := UPDATE_L_REC.copies_required;
1451 L_DEL_REC.cdrl_category := UPDATE_L_REC.cdrl_category;
1452 L_DEL_REC.data_item_name := UPDATE_L_REC.data_item_name;
1453 L_DEL_REC.export_flag := UPDATE_L_REC.export_flag;
1454
1455
1456
1457 -- Update deliverable
1458
1459 OKE_CONTRACT_PUB.update_deliverable(
1460 p_api_version => l_api_version,
1461 p_init_msg_list => p_init_msg_list,
1462 x_return_status => l_return_status,
1463 x_msg_count => x_msg_count,
1464 x_msg_data => x_msg_data,
1465 p_del_rec => l_del_rec,
1466 x_del_rec => x_del_rec);
1467
1468
1469 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1470 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1471 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1472 raise OKE_API.G_EXCEPTION_ERROR;
1473 End If;
1474
1475 -- If planning is initiated, update MDS as well
1476
1477 /*
1478
1479 IF L_MDS_ID > 0 THEN
1480
1481 OKE_DTS_INTEGRATION_PKG.Create_MDS_Entry (
1482 P_Deliverable_ID => L_DEL_REC.Deliverable_ID
1483 , X_Out_ID => L_Out_ID
1484 , X_Return_Status => L_Return_Status);
1485
1486 END IF;
1487
1488 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1489 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1490 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1491 raise OKE_API.G_EXCEPTION_ERROR;
1492 End If; */
1493
1494 X_Return_Status := L_Return_Status;
1495 X_Counter := 1;
1496
1497 -- end activity
1498 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1499 x_msg_data => x_msg_data);
1500 EXCEPTION
1501 when OKE_API.G_EXCEPTION_ERROR then
1502 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1503 p_api_name => l_api_name,
1504 p_pkg_name => g_pkg_name,
1505 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1506 x_msg_count => x_msg_count,
1507 x_msg_data => x_msg_data,
1508 p_api_type => g_api_type);
1509
1510 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1511 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1512 p_api_name => l_api_name,
1513 p_pkg_name => g_pkg_name,
1514 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1515 x_msg_count => x_msg_count,
1516 x_msg_data => x_msg_data,
1517 p_api_type => g_api_type);
1518
1519 when OTHERS then
1520 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1521 p_api_name => l_api_name,
1522 p_pkg_name => g_pkg_name,
1523 p_exc_name => 'OTHERS',
1524 x_msg_count => x_msg_count,
1525 x_msg_data => x_msg_data,
1526 p_api_type => g_api_type);
1527
1528 END Update_Line;
1529
1530 PROCEDURE Update_Batch ( P_Init_Msg_List VARCHAR2
1531 , X_Return_Status OUT NOCOPY VARCHAR2
1532 , X_Msg_Count OUT NOCOPY NUMBER
1533 , X_Msg_Data OUT NOCOPY VARCHAR2
1534 , P_Header_ID IN NUMBER
1535 , P_Direction IN VARCHAR2
1536 , P_Inventory_Org_ID IN NUMBER
1537 , X_Counter OUT NOCOPY NUMBER) IS
1538
1539 L_Api_Name CONSTANT VARCHAR2(30) := 'Update_Batch';
1540 L_Api_Verson CONSTANT NUMBER := 1;
1541 L_Return_Status VARCHAR2(1) := OKE_API.G_Ret_Sts_Success;
1542 L_Del_Rec OKE_DELIVERABLE_PVT.Del_Rec_Type;
1543 L_Del_Tbl OKE_DELIVERABLE_PVT.Del_Tbl_Type;
1544 X_Del_Rec OKE_DELIVERABLE_PVT.Del_Rec_Type;
1545 X_Del_Tbl OKE_DELIVERABLE_PVT.Del_Tbl_Type;
1546 L_Counter NUMBER;
1547 L_Deliverable_ID NUMBER;
1548 L_Msg_Data VARCHAR2(2000);
1549 L_Msg_Count NUMBER;
1550 L_Count NUMBER;
1551 L_Msg1 VARCHAR2(2000);
1552 L_Msg2 VARCHAR2(2000);
1553 L_Msg3 VARCHAR2(2000);
1554 L_Mps_F VARCHAR2(2000);
1555 L_Mps_S VARCHAR2(2000);
1556
1557 -- Message related variables
1558
1559 L_Token1 VARCHAR2(150);
1560 L_Token1_Value LONG;
1561 L_Token2 VARCHAR2(150);
1562 L_Token2_Value LONG;
1563 L_Token3 VARCHAR2(150);
1564 L_Token3_Value LONG;
1565 L_Token4 VARCHAR2(150);
1566 L_Token4_Value LONG;
1567 L_Token5 VARCHAR2(150);
1568 L_Token5_Value LONG;
1569 L_Token6 VARCHAR2(150);
1570 L_Token6_Value LONG;
1571 L_Token7 VARCHAR2(150);
1572 L_Token7_Value LONG;
1573 L_Used_Token NUMBER;
1574 L_Next_Token VARCHAR2(150);
1575 L_Final_Msg LONG;
1576 L_Token_Value VARCHAR2(2000);
1577
1578 CURSOR Update_C ( P_ID NUMBER ) IS
1579 SELECT l.k_line_id, b.deliverable_ID, l.line_number
1580 from oke_k_lines_v l, oke_k_deliverables_b b
1581 where b.k_header_id = p_id
1582 and b.k_line_id = l.k_line_id
1583 and nvl(l.scheduled_delv_default, 'N') = 'Y'
1584 and nvl(defaulted_flag, 'N') = 'Y'
1585 GROUP BY l.k_line_id, b.deliverable_ID, l.line_number
1586 HAVING count(*)=1;
1587
1588 BEGIN
1589
1590 l_return_status := OKE_API.START_ACTIVITY(
1591 p_api_name => l_api_name,
1592 p_init_msg_list => p_init_msg_list,
1593 p_api_type => g_api_type,
1594 x_return_status => l_return_status);
1595
1596 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1597 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1598 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1599 raise OKE_API.G_EXCEPTION_ERROR;
1600 End If;
1601
1602 L_Counter := 0;
1603
1604 FOR Update_Rec IN Update_C(P_Header_ID) LOOP
1605
1606 Verify_Defaults (
1607 P_Line_ID => UPDATE_REC.K_Line_ID
1608 , X_Msg_1 => L_Msg1
1609 , X_Msg_2 => L_Msg2
1610 , X_Msg_3 => L_Msg3
1611 , X_Return_Status => L_Return_Status
1612 , P_Calling_Level => 'H');
1613
1614 IF L_Msg1 IS NULL AND L_Return_Status = 'U' THEN
1615
1616 Update_Line(
1617 P_Init_Msg_List => G_False
1618 , X_Return_Status => L_Return_Status
1619 , X_Msg_Count => X_Msg_Count
1620 , X_Msg_Data => X_Msg_Data
1621 , P_Header_ID => P_Header_ID
1622 , P_Line_ID => Update_Rec.K_Line_ID
1623 , P_Direction => P_Direction
1624 , P_Inventory_Org_ID => P_Inventory_Org_ID
1625 , X_Counter => X_Counter);
1626
1627 IF L_Return_Status <> OKE_API.G_Ret_Sts_Success THEN
1628 IF L_Return_Status <> OKE_API.G_Ret_Sts_Unexp_Error THEN
1629 L_Return_Status := X_Return_Status;
1630 END IF;
1631 ELSE
1632 UPDATE oke_k_lines
1633 SET Scheduled_Delv_Default = 'N'
1634 WHERE K_Line_ID = Update_Rec.K_Line_ID;
1635 END IF;
1636
1637 -- Check mds records
1638
1639 IF Check_Mps_Valid ( Update_Rec.K_Line_ID, L_Mps_S, L_Mps_F ) THEN
1640
1641 OKE_DTS_ACTION_PKG.Initiate_Actions( P_Action => 'PLAN'
1642 , P_Action_Level => 3
1643 , P_Header_ID => P_Header_ID
1644 , P_Line_ID => Update_Rec.K_Line_ID
1645 , P_Deliverable_ID => Update_Rec.Deliverable_ID
1646 , X_Return_Status => L_Return_Status
1647 , X_Msg_Data => L_Msg_Data
1648 , X_Msg_Count => L_Msg_Count );
1649
1650 ELSE
1651
1652 UPDATE oke_k_deliverables_b
1653 SET Create_Demand = 'N'
1654 WHERE Deliverable_ID = Update_Rec.Deliverable_ID;
1655
1656 END IF;
1657
1658 IF L_Mps_S IS NOT NULL THEN
1659
1660 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_MDS_SUCCESS');
1661 L_Token6 := FND_MESSAGE.Get;
1662 IF L_Token6_Value IS NULL THEN
1663 L_Token6_Value := UPDATE_REC.Line_Number;
1664 ELSE
1665 IF isNewMessageWithinLimit (
1666 p_existing_message => L_Token6||L_Token6_Value
1667 ,p_new_token_value => UPDATE_REC.Line_Number) THEN
1668 L_Token6_Value := L_Token6_Value || ', ' || UPDATE_REC.Line_Number;
1669 END IF;
1670 END IF;
1671
1672 END IF;
1673
1674 IF L_Mps_F IS NOT NULL THEN
1675
1676 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_MDS_FAILURE');
1677 L_Token7 := FND_MESSAGE.Get;
1678 IF L_Token7_Value IS NULL THEN
1679 L_Token7_Value := UPDATE_REC.Line_Number;
1680 ELSE
1681 IF isNewMessageWithinLimit (
1682 p_existing_message => L_Token7||L_Token7_Value
1683 ,p_new_token_value => UPDATE_REC.Line_Number) THEN
1684 L_Token7_Value := L_Token7_Value || ', ' || UPDATE_REC.Line_Number;
1685 END IF;
1686
1687 END IF;
1688
1689 END IF;
1690
1691
1692 ELSE -- Previous if condition after verify defaults
1693
1694 IF l_msg3 = 'SPL' THEN
1695 L_Token1 := l_msg1;
1696 IF L_Token1_Value IS NULL THEN
1697 L_Token1_Value := UPDATE_REC.Line_Number;
1698 ELSE
1699 IF isNewMessageWithinLimit (
1700 p_existing_message => L_Token1||L_Token1_Value
1701 ,p_new_token_value => UPDATE_REC.Line_Number) THEN
1702 L_Token1_Value := L_Token1_Value || ', ' || UPDATE_REC.Line_Number;
1703 END IF;
1704 END IF;
1705
1706 ELSIF l_msg3 = 'WSH' THEN
1707 L_Token2 := l_msg1;
1708 IF L_Token2_Value IS NULL THEN
1709 L_Token2_Value := UPDATE_REC.Line_Number;
1710 ELSE
1711 IF isNewMessageWithinLimit (
1712 p_existing_message => L_Token2||L_Token2_Value
1713 ,p_new_token_value => UPDATE_REC.Line_Number) THEN
1714 L_Token2_Value := L_Token2_Value || ', ' || UPDATE_REC.Line_Number ;
1715 END IF;
1716 END IF;
1717
1718 ELSIF l_msg3 = 'REQ' THEN
1719
1720
1721 L_Token3 := l_msg1;
1722 IF L_Token3_Value IS NULL THEN
1723 L_Token3_Value := UPDATE_REC.Line_Number;
1724 ELSE
1725 IF isNewMessageWithinLimit (
1726 p_existing_message => L_Token3||L_Token3_Value
1727 ,p_new_token_value => UPDATE_REC.Line_Number) THEN
1728 L_Token3_Value := L_Token3_Value || ', ' || UPDATE_REC.Line_Number ;
1729 END IF;
1730
1731 END IF;
1732
1733 ELSIF l_msg3 = 'BIL' THEN
1734 L_Token4 := l_msg1;
1735 IF L_Token4_Value IS NULL THEN
1736 L_Token4_Value := UPDATE_REC.Line_Number;
1737 ELSE
1738 IF isNewMessageWithinLimit (
1739 p_existing_message => L_Token4||L_Token4_Value
1740 ,p_new_token_value => UPDATE_REC.Line_Number) THEN
1741 L_Token4_Value := L_Token4_Value || ', ' || UPDATE_REC.Line_Number ;
1742 END IF;
1743
1744 END IF;
1745
1746 ELSIF l_msg3 = 'COM' THEN
1747 L_Token5 := l_msg1;
1748 IF L_Token5_Value IS NULL THEN
1749 L_Token5_Value := UPDATE_REC.Line_Number;
1750 ELSE
1751 IF isNewMessageWithinLimit (
1752 p_existing_message => L_Token5||L_Token5_Value
1753 ,p_new_token_value => UPDATE_REC.Line_Number) THEN
1754 L_Token5_Value := L_Token5_Value || ', ' || UPDATE_REC.Line_Number ;
1755 END IF;
1756
1757 END IF;
1758
1759 END IF;
1760
1761 END IF;
1762
1763 L_Counter := L_Counter + 1;
1764
1765 END LOOP;
1766
1767 -- Sort messages and put on the message stack
1768
1769 IF L_Token1 IS NOT NULL OR L_Token2 IS NOT NULL OR L_Token3 IS NOT NULL OR L_Token4 IS NOT NULL
1770 OR L_Token5 IS NOT NULL OR L_Token6 IS NOT NULL OR L_Token7 IS NOT NULL THEN
1771
1772 IF L_Token1 IS NOT NULL THEN
1773
1774 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_SPLIT');
1775 FND_MESSAGE.Set_Token('TOKEN_VALUE_1', L_Token1_Value);
1776 FND_MSG_PUB.Add;
1777 END IF;
1778 IF L_Token2 IS NOT NULL THEN
1779 FND_MESSAGE.Set_Name('OKE', 'OKE_H_DTS_DATA_WSH');
1780 FND_MESSAGE.Set_Token('TOKEN_VALUE_1', L_Token2_Value);
1781 FND_MSG_PUB.Add;
1782 END IF;
1783 IF L_Token3 IS NOT NULL THEN
1784 FND_MESSAGE.Set_Name('OKE', 'OKE_H_DTS_DATA_REQ');
1785 FND_MESSAGE.Set_Token('TOKEN_VALUE_1', L_Token3_Value);
1786 FND_MSG_PUB.Add;
1787 END IF;
1788 IF L_Token4 IS NOT NULL THEN
1789 FND_MESSAGE.Set_Name('OKE', 'OKE_H_DTS_DATA_BILL');
1790 FND_MESSAGE.Set_Token('TOKEN_VALUE_1', L_Token4_Value);
1791 FND_MSG_PUB.Add;
1792 END IF;
1793
1794 IF L_Token5 IS NOT NULL THEN
1795 FND_MESSAGE.Set_Name('OKE', 'OKE_H_DTS_DATA_COMPLETED');
1796 FND_MESSAGE.Set_Token('TOKEN_VALUE_1', L_Token5_Value);
1797 FND_MSG_PUB.Add;
1798 END IF;
1799 IF L_Token6 IS NOT NULL THEN
1800 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_MDS_SUCCESS');
1801 FND_MESSAGE.Set_Token('TOKEN_VALUE_1', L_Token6_Value);
1802 FND_MSG_PUB.Add;
1803 END IF;
1804 IF L_Token7 IS NOT NULL THEN
1805 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_MDS_FAILURE');
1806 FND_MESSAGE.Set_Token('TOKEN_VALUE_1', L_Token7_Value);
1807 FND_MSG_PUB.Add;
1808 END IF;
1809 END IF;
1810 X_Return_Status := L_Return_Status;
1811 X_Counter := L_Counter;
1812
1813 -- end activity
1814 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1815 x_msg_data => x_msg_data);
1816
1817 EXCEPTION
1818 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1819
1820 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1821 (
1822 l_api_name,
1823 G_PKG_NAME,
1824 'OKE_API.G_RET_STS_ERROR',
1825 x_msg_count,
1826 x_msg_data,
1827 g_api_type
1828 );
1829 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1830
1831 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1832 (
1833 l_api_name,
1834 G_PKG_NAME,
1835 'OKE_API.G_RET_STS_UNEXP_ERROR',
1836 x_msg_count,
1837 x_msg_data,
1838 g_api_type
1839 );
1840 WHEN OTHERS THEN
1841
1842 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1843 (
1844 l_api_name,
1845 G_PKG_NAME,
1846 'OTHERS',
1847 x_msg_count,
1848 x_msg_data,
1849 g_api_type
1850 );
1851
1852 END Update_Batch;
1853
1854 FUNCTION Check_Mps_Valid ( P_Line_ID NUMBER, X_Mps_S OUT NOCOPY VARCHAR2, X_Mps_F OUT NOCOPY VARCHAR2 )RETURN BOOLEAN IS
1855
1856 L_ID NUMBER;
1857 L_Mds_ID NUMBER;
1858 L_Msg VARCHAR2(2000);
1859 L_Value VARCHAR2(1000);
1860 L_Direction VARCHAR2(20);
1861 L_Header_ID NUMBER;
1862 L_Inventory_Org_ID NUMBER;
1863 L_Ship_From_Org_ID NUMBER;
1864 L_Ship_To_Org_ID NUMBER;
1865 L_Ship_From_ID NUMBER;
1866 L_Ship_To_ID NUMBER;
1867 L_Item_ID NUMBER;
1868 L_Demand VARCHAR2(1);
1869 L_Quantity NUMBER;
1870 L_Line_Number VARCHAR2(150);
1871 L_Total_Msg LONG;
1872 L_Next_Token VARCHAR2(150);
1873
1874 CURSOR C IS
1875 SELECT Mps_Transaction_ID, Deliverable_ID, Create_Demand
1876 FROM OKE_K_DELIVERABLES_B
1877 WHERE K_Line_ID = P_Line_ID
1878 AND NVL(Defaulted_Flag, 'N') = 'Y'
1879 AND NVL(Create_Demand, 'N') = 'Y';
1880
1881 CURSOR Verify_C IS
1882 SELECT DECODE(H.Buy_Or_Sell, 'B', 'IN', 'OUT')
1883 , L.Header_ID
1884 , L.Inventory_Item_ID
1885 , L.Line_Quantity
1886 , L.Line_Number
1887 FROM okc_k_headers_b H, oke_k_lines_v L
1888 WHERE H.ID = L.Header_ID
1889 AND L.K_Line_ID = P_Line_ID;
1890
1891 CURSOR Item_C IS
1892 SELECT 'X'
1893 FROM oke_system_items_v
1894 WHERE ID1 = L_Item_ID
1895 AND ID2 = L_Inventory_Org_ID;
1896
1897
1898 BEGIN
1899
1900 OPEN C;
1901 FETCH C INTO L_Mds_ID, L_ID, L_Demand;
1902 CLOSE C;
1903
1904 IF L_Demand = 'Y' THEN
1905
1906 OPEN Verify_C;
1907 FETCH Verify_C INTO L_Direction, L_Header_ID, L_Item_ID, L_Quantity, L_Line_Number;
1908 CLOSE Verify_C;
1909
1910
1911 OKE_DEFAULTING_PKG.Convert_Value(L_Header_ID
1912 , P_Line_ID
1913 , L_Direction
1914 , L_Ship_To_Org_ID
1915 , L_Ship_To_ID
1916 , L_Ship_From_Org_ID
1917 , L_Ship_From_ID
1918 , L_Inventory_Org_ID);
1919
1920
1921 IF L_Item_ID IS NULL THEN
1922
1923 fnd_message.set_name('OKE', 'OKE_DTS_DATA_ITEM');
1924 l_msg := fnd_message.get;
1925
1926 ELSIF L_Ship_From_ID IS NULL THEN
1927
1928 fnd_message.set_name('OKE', 'OKE_DTS_DATA_FROM_LOCATION');
1929 l_msg := fnd_message.get;
1930
1931 ELSIF L_Ship_To_ID IS NULL THEN
1932
1933 fnd_message.set_name('OKE', 'OKE_DTS_DATA_TO_LOCATION');
1934 l_msg := fnd_message.get;
1935
1936 ELSIF L_Quantity IS NULL THEN
1937
1938 fnd_message.set_name('OKE', 'OKE_DTS_DATA_QTY');
1939 l_msg := fnd_message.get;
1940
1941 ELSIF L_Item_ID IS NOT NULL THEN
1942
1943 IF L_Inventory_Org_ID > 0 THEN
1944
1945 OPEN Item_C;
1946 FETCH Item_C INTO L_Value;
1947 CLOSE Item_C;
1948
1949 IF L_Value <> 'X' OR L_Value IS NULL THEN
1950
1951 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_DATA_INVALID_ITEM_ORG');
1952 L_Msg := FND_MESSAGE.Get;
1953
1954 END IF;
1955
1956 END IF;
1957
1958 END IF;
1959
1960 IF L_Msg IS NOT NULL THEN
1961
1962 -- OKE_API.Set_Message('OKE', 'OKE_KAUWB_MDS_NOT_VALID', 'TOKEN1', L_Line_Number, 'TOKEN2', L_Msg);
1963
1964 -- FND_MESSAGE.Set_Name('OKE','OKE_KAUWB_MDS_NOT_VALID');
1965 -- FND_MESSAGE.Set_Token('TOKEN1', L_Line_Number);
1966 -- FND_MESSAGE.Set_Token('TOKEN2', L_Msg);
1967 X_MPS_F := L_Msg;
1968
1969 RETURN FALSE;
1970
1971 ELSE
1972
1973 -- OKE_API.Set_Message('OKE', 'OKE_KAUWB_MDS_UPDATED', 'TOKEN1', L_Line_Number);
1974
1975 -- FND_MESSAGE.Set_Name('OKE', 'OKE_KAUWB_MDS_UPDATED');
1976 -- X_Mps_S := FND_MESSAGE.Get;
1977 X_Mps_S := 'S';
1978
1979 RETURN TRUE;
1980
1981 END IF;
1982
1983 ELSE
1984
1985
1986 RETURN TRUE;
1987
1988 END IF;
1989
1990 EXCEPTION
1991
1992 WHEN OTHERS THEN
1993
1994 RETURN FALSE;
1995
1996 END Check_MPS_Valid;
1997
1998
1999 END;
2000