[Home] [Help]
PACKAGE BODY: APPS.OKE_DELIVERABLE_PVT
Source
1 PACKAGE BODY OKE_DELIVERABLE_PVT AS
2 /* $Header: OKEVDELB.pls 120.2 2005/11/23 14:37:29 ausmani noship $ */
3
4 FUNCTION validate_attributes( p_del_rec IN del_rec_type)
5 RETURN VARCHAR2;
6
7 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKE_NO_PARENT_RECORD';
8 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKE_CONTRACTS_UNEXPECTED_ERROR';
9 g_module CONSTANT VARCHAR2(250) := 'oke.plsql.oke_deliverable_pvt.';
10 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
11 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
12 G_VIEW CONSTANT VARCHAR2(200) := 'OKE_K_DELIVERABLES_VL';
13 G_EXCEPTION_HALT_VALIDATION exception;
14 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
15
16 -- validation code goes here
17
18
19 PROCEDURE Validate_Header_ID (X_Return_Status OUT NOCOPY VARCHAR2,
20 P_Del_Rec IN Del_Rec_Type) IS
21
22 L_Value VARCHAR2(1) := 'N';
23
24 CURSOR C (P_ID NUMBER) IS
25 SELECT 'X'
26 FROM okc_k_headers_b
27 WHERE ID = P_ID;
28
29 Begin
30
31 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
32
33 IF P_DEL_REC.K_Header_ID IS NOT NULL THEN
34
35
36
37 OPEN C ( P_DEL_REC.K_Header_ID);
38 FETCH C INTO L_Value;
39 CLOSE C;
40
41 IF L_Value <> 'X' THEN
42
43 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
44 p_msg_name => g_invalid_value,
45 p_token1 => g_col_name_token,
46 p_token1_value => 'K_Header_ID');
47 -- notify caller of an error
48 X_Return_Status := OKE_API.G_RET_STS_ERROR;
49
50 -- halt validation
51 RAISE G_EXCEPTION_HALT_VALIDATION;
52
53 END IF;
54
55 ELSE
56
57 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
58 p_msg_name => g_required_value,
59 p_token1 => g_col_name_token,
60 p_token1_value => 'K_Header_ID');
61 -- notify caller of an error
62 X_Return_Status := OKE_API.G_RET_STS_ERROR;
63
64 -- halt validation
65 RAISE G_EXCEPTION_HALT_VALIDATION;
66
67 End If;
68
69
70 EXCEPTION
71 WHEN G_EXCEPTION_HALT_VALIDATION THEN
72
73 NULL; -- Even failed, continue validate other attributes
74
75 WHEN OTHERS THEN
76
77 -- store SQL error message on message stack
78 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
79 p_msg_name => g_unexpected_error,
80 p_token1 => g_sqlcode_token,
81 p_token1_value=> sqlcode,
82 p_token2 => g_sqlerrm_token,
83 p_token2_value=> sqlerrm);
84 -- notify caller of an error as UNEXPETED error
85 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
86
87 End Validate_Header_ID;
88
89 PROCEDURE Validate_Line_ID (X_Return_Status OUT NOCOPY VARCHAR2,
90 P_Del_Rec IN Del_Rec_Type) IS
91
92 L_Value VARCHAR2(1) := 'N';
93
94 CURSOR C (P_ID NUMBER) IS
95 SELECT 'X'
96 FROM okc_k_lines_b
97 WHERE ID = P_ID
98 AND NOT EXISTS(SELECT 'X' FROM okc_ancestrys
99 WHERE Cle_ID_Ascendant = P_ID);
100
101 Begin
102
103 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
104
105 IF P_DEL_REC.K_Line_ID IS NOT NULL THEN
106
107 -- Only the lowest level line should carrry deliverables
108
109 OPEN C ( P_DEL_REC.K_Line_ID);
110 FETCH C INTO L_Value;
111 CLOSE C;
112
113
114
115 IF L_Value <> 'X' THEN
116
117 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
118 p_msg_name => g_invalid_value,
119 p_token1 => g_col_name_token,
120 p_token1_value => 'K_Line_ID');
121 -- notify caller of an error
122 X_Return_Status := OKE_API.G_RET_STS_ERROR;
123
124 -- halt validation
125 RAISE G_EXCEPTION_HALT_VALIDATION;
126
127 END IF;
128
129 ELSE
130
131 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
132 p_msg_name => g_required_value,
133 p_token1 => g_col_name_token,
134 p_token1_value => 'K_Line_ID');
135 -- notify caller of an error
136 X_Return_Status := OKE_API.G_RET_STS_ERROR;
137
138 -- halt validation
139 RAISE G_EXCEPTION_HALT_VALIDATION;
140
141 End If;
142
143
144 EXCEPTION
145 WHEN G_EXCEPTION_HALT_VALIDATION THEN
146
147 NULL; -- Even failed, continue validate other attributes
148
149 WHEN OTHERS THEN
150
151 -- store SQL error message on message stack
152 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
153 p_msg_name => g_unexpected_error,
154 p_token1 => g_sqlcode_token,
155 p_token1_value=> sqlcode,
156 p_token2 => g_sqlerrm_token,
157 p_token2_value=> sqlerrm);
158 -- notify caller of an error as UNEXPETED error
159 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
160
161 End Validate_Line_ID;
162
163
164
165 PROCEDURE Validate_Deliverable_Number(X_Return_Status OUT NOCOPY VARCHAR2,
166 P_Del_Rec IN Del_Rec_Type) IS
167 L_Value VARCHAR2(1) := 'N';
168
169 CURSOR C (P_ID NUMBER, P_Num VARCHAR2 ) IS
170 SELECT 'X'
171 FROM oke_k_deliverables_b
172 WHERE Deliverable_Num = P_Num
173 AND K_Line_ID = P_ID;
174
175 Begin
176
177 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
178
179 IF P_DEL_REC.Deliverable_Num IS NOT NULL AND P_DEL_REC.Deliverable_ID IS NULL THEN
180
181 OPEN C ( P_DEL_REC.K_Line_ID, P_DEL_REC.Deliverable_Num );
182 FETCH C INTO L_Value;
183 CLOSE C;
184
185
186
187 IF L_Value = 'X' THEN
188
189 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
190 p_msg_name => g_invalid_value,
191 p_token1 => g_col_name_token,
192 p_token1_value => 'Deliverable_Num');
193 -- notify caller of an error
194 X_Return_Status := OKE_API.G_RET_STS_ERROR;
195
196 -- halt validation
197 RAISE G_EXCEPTION_HALT_VALIDATION;
198
199 END IF;
200 END IF;
201
202
203 EXCEPTION
204 WHEN G_EXCEPTION_HALT_VALIDATION THEN
205
206 NULL; -- Even failed, continue validate other attributes
207
208 WHEN OTHERS THEN
209
210 -- store SQL error message on message stack
211 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
212 p_msg_name => g_unexpected_error,
213 p_token1 => g_sqlcode_token,
214 p_token1_value=> sqlcode,
215 p_token2 => g_sqlerrm_token,
216 p_token2_value=> sqlerrm);
217 -- notify caller of an error as UNEXPETED error
218 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
219
220 End Validate_Deliverable_Number;
221
222 PROCEDURE Validate_Project_ID (X_Return_Status OUT NOCOPY VARCHAR2,
223 P_Del_Rec IN Del_Rec_Type) IS
224
225 L_Line_Id Number;
226 L_Sequence Number := 1;
227 L_Max_Sequence Number;
228 L_Project_Id NUMBER;
229 L_Task_Id NUMBER;
230 L_Parent_Id NUMBER;
231 L_Value VARCHAR2(1) := 'N';
232
233 CURSOR Header_C ( P_Header_Id Number ) IS
234 SELECT Project_ID
235 FROM oke_k_headers
236 WHERE K_Header_Id = P_Header_Id;
237
238 CURSOR Top_C IS
239 SELECT PROJECT_ID, TASK_ID, PARENT_LINE_ID
240 FROM oke_k_lines
241 WHERE K_Line_Id = L_Line_Id;
242
243 CURSOR Sub_C (P_Line_Id NUMBER, P_Sequence NUMBER) IS
244 SELECT PROJECT_ID, TASK_ID
245 FROM OKE_K_LINES
246 WHERE K_LINE_ID = (SELECT CLE_ID_ASCENDANT FROM OKC_ANCESTRYS WHERE CLE_ID = P_LINE_ID AND LEVEL_SEQUENCE = P_SEQUENCE)
247 AND PROJECT_ID IS NOT NULL;
248
249 CURSOR C (P_ID NUMBER, P_ID1 NUMBER, P_ID2 NUMBER) IS
250 SELECT 'X'
251 FROM DUAL
252 WHERE P_ID IN (
253 SELECT P.Project_ID
254 FROM pa_projects_all p
255 WHERE p.Project_ID IN (SELECT To_Number(sub_project_id)
256 FROM pa_fin_structures_links_v
257 START WITH parent_project_id = P_ID1 AND (parent_task_id IN (SELECT Task_ID FROM pa_tasks WHERE Top_Task_ID = P_ID2) or P_ID2 IS NULL)
258 CONNECT BY parent_project_id = PRIOR sub_project_id)
259 UNION
260 SELECT Project_ID
261 FROM pa_projects_all
262 WHERE Project_ID = P_ID1);
263
264 CURSOR Seq_C (P_ID NUMBER ) IS
265 SELECT MAX(LEVEL_SEQUENCE)
266 FROM OKC_ANCESTRYS
267 WHERE CLE_ID = P_ID;
268
269 Begin
270
271 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
272
273 IF P_DEL_REC.Project_ID IS NOT NULL THEN
274
275 L_Line_Id := P_DEL_REC.K_Line_ID;
276
277 OPEN Top_C;
278 FETCH Top_C INTO L_Project_Id, L_Task_Id, L_Parent_Id;
279 CLOSE Top_C;
280
281 IF L_Project_Id IS NULL THEN
282 IF L_Parent_Id IS NULL THEN
283
284 OPEN Header_C (P_DEL_REC.K_Header_ID);
285 FETCH Header_C INTO L_Project_Id;
286 CLOSE Header_C;
287
288 ELSE
289
290 OPEN Seq_C ( L_Line_ID );
291 FETCH Seq_C INTO L_Max_Sequence;
292 CLOSE Seq_C;
293
294 FOR L_Sequence IN 1 ..L_Max_Sequence LOOP
295 OPEN Sub_C( L_Line_Id, L_Sequence);
296 Fetch Sub_C INTO L_Project_Id, L_Task_Id;
297 CLOSE Sub_C;
298
299 EXIT WHEN L_Project_Id IS NOT NULL;
300
301 END LOOP;
302
303
304
305 IF L_Project_Id IS NULL THEN
306 OPEN Header_C ( P_DEL_REC.K_Header_ID );
307 FETCH Header_C INTO L_Project_Id;
308 CLOSE Header_C;
309 END IF;
310
311
312 END IF;
313
314 END IF;
315
316 OPEN C ( P_DEL_REC.Project_ID, L_Project_ID, L_Task_ID);
317 FETCH C INTO L_Value;
318 CLOSE C;
319
320
321
322 IF L_Value <> 'X' THEN
323
324 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
325 p_msg_name => g_invalid_value,
326 p_token1 => g_col_name_token,
327 p_token1_value => 'Project_ID');
328 -- notify caller of an error
329 X_Return_Status := OKE_API.G_RET_STS_ERROR;
330
331 -- halt validation
332 RAISE G_EXCEPTION_HALT_VALIDATION;
333
334 END IF;
335
336 END IF;
337
338
339 EXCEPTION
340 WHEN G_EXCEPTION_HALT_VALIDATION THEN
341
342 NULL; -- Even failed, continue validate other attributes
343
344 WHEN OTHERS THEN
345
346 -- store SQL error message on message stack
347 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
348 p_msg_name => g_unexpected_error,
349 p_token1 => g_sqlcode_token,
350 p_token1_value=> sqlcode,
351 p_token2 => g_sqlerrm_token,
352 p_token2_value=> sqlerrm);
353 -- notify caller of an error as UNEXPETED error
354 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
355
356 End Validate_Project_ID;
357
358 PROCEDURE Validate_Task_ID (X_Return_Status OUT NOCOPY VARCHAR2,
359 P_Del_Rec IN Del_Rec_Type) IS
360 L_Value VARCHAR2(1) := 'N';
361
362 CURSOR C (P_ID1 NUMBER, P_ID2 NUMBER) IS
363 SELECT 'X'
364 FROM pa_tasks
365 WHERE Task_ID = P_ID1
366 AND Project_ID = P_ID2;
367
368 Begin
369
370 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
371
372 IF P_DEL_REC.Task_ID IS NOT NULL THEN
373
374 IF P_DEL_REC.Project_ID IS NULL THEN
375
376 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
377 p_msg_name => g_required_value,
378 p_token1 => g_col_name_token,
379 p_token1_value => 'Project_ID');
380 -- notify caller of an error
381 X_Return_Status := OKE_API.G_RET_STS_ERROR;
382
383 -- halt validation
384 RAISE G_EXCEPTION_HALT_VALIDATION;
385
386 ELSE
387
388 OPEN C ( P_DEL_REC.Task_ID, P_DEL_REC.Project_ID );
389 FETCH C INTO L_Value;
390 CLOSE C;
391
392 IF L_Value <> 'X' THEN
393
394 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
395 p_msg_name => g_invalid_value,
396 p_token1 => g_col_name_token,
397 p_token1_value => 'Task_ID');
398 -- notify caller of an error
399 X_Return_Status := OKE_API.G_RET_STS_ERROR;
400
401 -- halt validation
402 RAISE G_EXCEPTION_HALT_VALIDATION;
403
404 END IF;
405
406 END IF;
407
408 END IF;
409
410 EXCEPTION
411 WHEN G_EXCEPTION_HALT_VALIDATION THEN
412
413 NULL; -- Even failed, continue validate other attributes
414
415 WHEN OTHERS THEN
416
417 -- store SQL error message on message stack
418 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
419 p_msg_name => g_unexpected_error,
420 p_token1 => g_sqlcode_token,
421 p_token1_value=> sqlcode,
422 p_token2 => g_sqlerrm_token,
423 p_token2_value=> sqlerrm);
424 -- notify caller of an error as UNEXPETED error
425 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
426
427 End Validate_Task_ID;
428
429 PROCEDURE Validate_Inventory_Org_ID (X_Return_Status OUT NOCOPY VARCHAR2,
430 P_Del_Rec IN Del_Rec_Type) IS
431 L_Value VARCHAR2(1) := 'N';
432
433 CURSOR C (P_ID NUMBER ) IS
434 SELECT 'X'
435 FROM okx_organization_defs_v
436 WHERE ID1 = P_ID;
437
438 Begin
439
440 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
441
442 IF P_DEL_REC.Inventory_Org_ID IS NOT NULL THEN
443
444 OPEN C ( P_DEL_REC.Inventory_Org_ID );
445 FETCH C INTO L_Value;
446 CLOSE C;
447
448 IF L_Value <> 'X' THEN
449
450 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
451 p_msg_name => g_invalid_value,
452 p_token1 => g_col_name_token,
453 p_token1_value => 'Inventory_Org_ID');
454 -- notify caller of an error
455 X_Return_Status := OKE_API.G_RET_STS_ERROR;
456
457 -- halt validation
458 RAISE G_EXCEPTION_HALT_VALIDATION;
459
460 END IF;
461
462 ELSE
463
464 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
465 p_msg_name => g_required_value,
466 p_token1 => g_col_name_token,
467 p_token1_value => 'Inventory_Org_ID');
468 -- notify caller of an error
469 X_Return_Status := OKE_API.G_RET_STS_ERROR;
470
471 -- halt validation
472 RAISE G_EXCEPTION_HALT_VALIDATION;
473
474
475 END IF;
476
477 EXCEPTION
478 WHEN G_EXCEPTION_HALT_VALIDATION THEN
479
480 NULL; -- Even failed, continue validate other attributes
481
482 WHEN OTHERS THEN
483
484 -- store SQL error message on message stack
485 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
486 p_msg_name => g_unexpected_error,
487 p_token1 => g_sqlcode_token,
488 p_token1_value=> sqlcode,
489 p_token2 => g_sqlerrm_token,
490 p_token2_value=> sqlerrm);
491 -- notify caller of an error as UNEXPETED error
492 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
493
494 End Validate_Inventory_Org_ID;
495
496 PROCEDURE Validate_Item_ID (X_Return_Status OUT NOCOPY VARCHAR2,
497 P_Del_Rec IN Del_Rec_Type) IS
498 L_Value VARCHAR2(1) := 'N';
499 L_ID NUMBER;
500
501 CURSOR C (P_ID1 NUMBER, P_ID2 NUMBER) IS
502 SELECT 'X'
503 FROM oke_system_items_v
504 WHERE ID1 = P_ID1
505 AND ID2 = P_ID2;
506
507 Begin
508
509 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
510
511 IF P_DEL_REC.Item_ID IS NOT NULL THEN
512
513 IF P_DEL_REC.Direction = 'OUT' THEN
514
515 IF P_DEL_REC.Ship_From_Org_ID IS NOT NULL THEN
516
517 L_ID := P_DEL_REC.Ship_From_Org_ID;
518
519 ELSE
520
521 L_ID := P_DEL_REC.Inventory_Org_ID;
522
523 END IF;
524
525 ELSE
526
527 IF P_DEL_REC.Ship_To_Org_ID IS NOT NULL THEN
528
529 L_ID := P_DEL_REC.Ship_To_Org_ID;
530
531 ELSE
532
533 L_ID := P_DEL_REC.Inventory_Org_ID;
534
535 END IF;
536
537 END IF;
538
539 IF P_DEL_REC.Inventory_Org_ID IS NULL THEN
540
541 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
542 p_msg_name => g_required_value,
543 p_token1 => g_col_name_token,
544 p_token1_value => 'Inventory_Org_ID');
545 -- notify caller of an error
546 X_Return_Status := OKE_API.G_RET_STS_ERROR;
547
548 -- halt validation
549 RAISE G_EXCEPTION_HALT_VALIDATION;
550
551 ELSE
552
553 OPEN C ( P_DEL_REC.Item_ID, L_ID );
554 FETCH C INTO L_Value;
555 CLOSE C;
556
557 IF L_Value <> 'X' THEN
558
559 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
560 p_msg_name => g_invalid_value,
561 p_token1 => g_col_name_token,
562 p_token1_value => 'Item_ID');
563 -- notify caller of an error
564 X_Return_Status := OKE_API.G_RET_STS_ERROR;
565
566 -- halt validation
567 RAISE G_EXCEPTION_HALT_VALIDATION;
568
569 END IF;
570
571 END IF;
572
573 END IF;
574
575 EXCEPTION
576 WHEN G_EXCEPTION_HALT_VALIDATION THEN
577
578 NULL; -- Even failed, continue validate other attributes
579
580 WHEN OTHERS THEN
581
582 -- store SQL error message on message stack
583 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
584 p_msg_name => g_unexpected_error,
585 p_token1 => g_sqlcode_token,
586 p_token1_value=> sqlcode,
587 p_token2 => g_sqlerrm_token,
588 p_token2_value=> sqlerrm);
589 -- notify caller of an error as UNEXPETED error
590 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
591
592 End Validate_Item_ID;
593
594 PROCEDURE Validate_Delivery_Date (X_Return_Status OUT NOCOPY VARCHAR2,
595 P_Del_Rec IN Del_Rec_Type) IS
596 L_Value VARCHAR2(1) := 'N';
597
598 CURSOR C IS
599 SELECT 'X'
600 FROM okc_k_lines_b
601 WHERE P_DEL_REC.Delivery_Date >= NVL(P_DEL_REC.Start_Date, P_DEL_REC.Delivery_Date)
602 AND P_DEL_REC.Delivery_Date <= NVL(P_DEL_REC.End_Date, P_DEL_REC.Delivery_Date)
603 AND P_DEL_REC.Delivery_Date >= NVL(Start_Date, P_DEL_REC.Delivery_Date)
604 AND P_DEL_REC.Delivery_Date <= NVL(End_Date, P_DEL_REC.Delivery_Date)
605 AND ID = P_DEL_REC.K_Line_ID;
606
607 Begin
608
609 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
610
611 IF P_DEL_REC.Delivery_Date IS NOT NULL THEN
612
613 OPEN C ;
614 FETCH C INTO L_Value;
615 CLOSE C;
616
617 IF L_Value <> 'X' THEN
618
619 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
620 p_msg_name => g_invalid_value,
621 p_token1 => g_col_name_token,
622 p_token1_value => 'Delivery_Date');
623 -- notify caller of an error
624 X_Return_Status := OKE_API.G_RET_STS_ERROR;
625
626 -- halt validation
627 RAISE G_EXCEPTION_HALT_VALIDATION;
628
629 END IF;
630
631 END IF;
632
633 EXCEPTION
634 WHEN G_EXCEPTION_HALT_VALIDATION THEN
635
636 NULL; -- Even failed, continue validate other attributes
637
638 WHEN OTHERS THEN
639
640 -- store SQL error message on message stack
641 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
642 p_msg_name => g_unexpected_error,
643 p_token1 => g_sqlcode_token,
644 p_token1_value=> sqlcode,
645 p_token2 => g_sqlerrm_token,
646 p_token2_value=> sqlerrm);
647 -- notify caller of an error as UNEXPETED error
648 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
649
650 End Validate_Delivery_Date;
651
652 PROCEDURE Validate_Direction (X_Return_Status OUT NOCOPY VARCHAR2,
653 P_Del_Rec IN Del_Rec_Type) IS
654 Begin
655
656 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
657
658 IF P_DEL_REC.Direction IS NOT NULL THEN
659
660 IF P_DEL_REC.Direction NOT IN ('IN', 'OUT') THEN
661
662 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
663 p_msg_name => g_invalid_value,
664 p_token1 => g_col_name_token,
665 p_token1_value => 'Direction');
666 -- notify caller of an error
667 X_Return_Status := OKE_API.G_RET_STS_ERROR;
668
669 -- halt validation
670 RAISE G_EXCEPTION_HALT_VALIDATION;
671
672
673 END IF;
674
675 ELSE
676
677 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
678 p_msg_name => g_required_value,
679 p_token1 => g_col_name_token,
680 p_token1_value => 'Direction');
681 -- notify caller of an error
682 X_Return_Status := OKE_API.G_RET_STS_ERROR;
683
684 -- halt validation
685 RAISE G_EXCEPTION_HALT_VALIDATION;
686
687 END IF;
688
689 EXCEPTION
690 WHEN G_EXCEPTION_HALT_VALIDATION THEN
691
692 NULL; -- Even failed, continue validate other attributes
693
694 WHEN OTHERS THEN
695
696 -- store SQL error message on message stack
697 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
698 p_msg_name => g_unexpected_error,
699 p_token1 => g_sqlcode_token,
700 p_token1_value=> sqlcode,
701 p_token2 => g_sqlerrm_token,
702 p_token2_value=> sqlerrm);
703 -- notify caller of an error as UNEXPETED error
704 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
705
706 End Validate_Direction;
707
708 PROCEDURE Validate_Ship_To_Org_ID (X_Return_Status OUT NOCOPY VARCHAR2,
709 P_Del_Rec IN Del_Rec_Type) IS
710
711 L_Value VARCHAR2(1) := 'N';
712 L_Intent VARCHAR2(1);
713
714 CURSOR C1 ( P_ID NUMBER ) IS
715 SELECT 'X'
716 FROM okx_organization_defs_v
717 WHERE ID1 = P_ID;
718
719 CURSOR C2 ( P_ID NUMBER ) IS
720 SELECT 'X'
721 FROM oke_customer_accounts_v
722 WHERE ID1 = P_ID;
723
724 Begin
725
726 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
727
728 IF P_DEL_REC.Ship_To_Org_ID > 0 THEN
729
730 IF P_DEL_REC.Direction = 'IN' THEN
731
732 OPEN C1 ( P_DEL_REC.Ship_To_Org_ID );
733 FETCH C1 INTO L_Value;
734 CLOSE C1;
735
736 ELSIF P_DEL_REC.Direction = 'OUT' THEN
737
738 OPEN C2 ( P_DEL_REC.Ship_To_Org_ID );
739 FETCH C2 INTO L_Value;
740 CLOSE C2;
741
742
743 END IF;
744
745 IF L_Value <> 'X' THEN
746
747 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
748 p_msg_name => g_invalid_value,
749 p_token1 => g_col_name_token,
750 p_token1_value => 'Ship_To_Org_ID');
751 -- notify caller of an error
752 X_Return_Status := OKE_API.G_RET_STS_ERROR;
753
754 -- halt validation
755 RAISE G_EXCEPTION_HALT_VALIDATION;
756
757
758 END IF;
759
760 END IF;
761
762 EXCEPTION
763 WHEN G_EXCEPTION_HALT_VALIDATION THEN
764
765 NULL; -- Even failed, continue validate other attributes
766
767 WHEN OTHERS THEN
768
769 -- store SQL error message on message stack
770 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
771 p_msg_name => g_unexpected_error,
772 p_token1 => g_sqlcode_token,
773 p_token1_value=> sqlcode,
774 p_token2 => g_sqlerrm_token,
775 p_token2_value=> sqlerrm);
776 -- notify caller of an error as UNEXPETED error
777 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
778
779 End Validate_Ship_To_Org_ID;
780
781 PROCEDURE Validate_Ship_From_Org_ID (X_Return_Status OUT NOCOPY VARCHAR2,
782 P_Del_Rec IN Del_Rec_Type) IS
783
784 L_Value VARCHAR2(1) := 'N';
785 L_Intent VARCHAR2(1);
786
787 CURSOR C ( P_ID NUMBER ) IS
788 SELECT Buy_Or_Sell
789 FROM okc_k_headers_b
790 WHERE ID = P_ID;
791
792 CURSOR C1 ( P_ID NUMBER ) IS
793 SELECT 'X'
794 FROM okx_vendors_v
795 WHERE ID1 = P_ID;
796
797 CURSOR C2 ( P_ID NUMBER ) IS
798 SELECT 'X'
799 FROM oke_customer_accounts_v
800 WHERE ID1 = P_ID;
801
802 CURSOR C3 ( P_ID NUMBER ) IS
803 SELECT 'X'
804 FROM okx_organization_defs_v
805 WHERE ID1 = P_ID;
806
807
808 Begin
809
810 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
811
812 IF P_DEL_REC.Ship_From_Org_ID > 0 THEN
813
814 IF P_DEL_REC.Direction = 'IN' THEN
815
816 OPEN C ( P_DEL_REC.K_Header_ID );
817 FETCH C INTO L_Intent;
818 CLOSE C;
819
820 IF L_Intent = 'B' THEN
821
822 OPEN C1 ( P_DEL_REC.Ship_From_Org_ID );
823 FETCH C1 INTO L_Value;
824 CLOSE C1;
825
826 ELSIF L_Intent = 'S' THEN
827
828 OPEN C2 ( P_DEL_REC.Ship_From_Org_ID );
829 FETCH C2 INTO L_Value;
830 CLOSE C2;
831
832 END IF;
833
834 ELSIF P_DEL_REC.Direction = 'OUT' THEN
835
836 OPEN C3 ( P_DEL_REC.Ship_From_Org_ID );
837 FETCH C3 INTO L_Value;
838 CLOSE C3;
839
840 END IF;
841
842
843 IF L_Value <> 'X' THEN
844
845 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
846 p_msg_name => g_invalid_value,
847 p_token1 => g_col_name_token,
848 p_token1_value => 'Ship_From_Org_ID');
849 -- notify caller of an error
850 X_Return_Status := OKE_API.G_RET_STS_ERROR;
851
852 -- halt validation
853 RAISE G_EXCEPTION_HALT_VALIDATION;
854
855
856 END IF;
857
858 END IF;
859
860 EXCEPTION
861 WHEN G_EXCEPTION_HALT_VALIDATION THEN
862
863 NULL; -- Even failed, continue validate other attributes
864
865 WHEN OTHERS THEN
866
867 -- store SQL error message on message stack
868 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
869 p_msg_name => g_unexpected_error,
870 p_token1 => g_sqlcode_token,
871 p_token1_value=> sqlcode,
872 p_token2 => g_sqlerrm_token,
873 p_token2_value=> sqlerrm);
874 -- notify caller of an error as UNEXPETED error
875 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
876
877 End Validate_Ship_From_Org_ID;
878
879 PROCEDURE Validate_Ship_To_Location_ID (X_Return_Status OUT NOCOPY VARCHAR2,
880 P_Del_Rec IN Del_Rec_Type) IS
881 L_Value VARCHAR2(1) := 'N';
882
883 CURSOR C1 ( P_ID1 NUMBER, P_ID2 NUMBER ) IS
884 SELECT 'X'
885 FROM oke_cust_site_uses_v
886 WHERE Cust_Account_ID = P_ID2
887 AND ID1 = P_ID1;
888
889 CURSOR C2 ( P_ID1 NUMBER, P_ID2 NUMBER ) IS
890 SELECT 'X'
891 FROM okx_locations_v
892 WHERE ID1 = P_ID1
893 AND Organization_ID = P_ID2;
894
895
896
897 Begin
898
899 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
900
901 IF P_DEL_REC.Ship_To_Location_ID > 0 THEN
902
903 IF P_DEL_REC.Ship_To_Org_ID IS NULL THEN
904
905 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
906 p_msg_name => g_required_value,
907 p_token1 => g_col_name_token,
908 p_token1_value => 'Ship_to_org_ID if ship_to_location_ID is present');
909 -- notify caller of an error
910 X_Return_Status := OKE_API.G_RET_STS_ERROR;
911
912 -- halt validation
913 RAISE G_EXCEPTION_HALT_VALIDATION;
914
915 ELSE
916
917 IF NVL(P_DEL_REC.Direction, 'OUT') = 'OUT' THEN
918
919 OPEN C1 ( P_DEL_REC.Ship_To_Location_ID, P_DEL_REC.Ship_To_Org_ID );
920 FETCH C1 INTO L_Value;
921 CLOSE C1;
922
923 ELSE
924
925 OPEN C2 ( P_DEL_REC.Ship_To_Location_ID, P_DEL_REC.Ship_To_Org_ID );
926 FETCH C2 INTO L_Value;
927 CLOSE C2;
928
929 END IF;
930
931 IF L_Value <> 'X' THEN
932
933 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
934 p_msg_name => g_invalid_value,
935 p_token1 => g_col_name_token,
936 p_token1_value => 'Ship_To_Location_ID');
937 -- notify caller of an error
938 X_Return_Status := OKE_API.G_RET_STS_ERROR;
939
940 -- halt validation
941 RAISE G_EXCEPTION_HALT_VALIDATION;
942
943 END IF;
944
945 END IF;
946
947 END IF;
948
949 EXCEPTION
950 WHEN G_EXCEPTION_HALT_VALIDATION THEN
951
952 NULL; -- Even failed, continue validate other attributes
953
954 WHEN OTHERS THEN
955
956 -- store SQL error message on message stack
957 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
958 p_msg_name => g_unexpected_error,
959 p_token1 => g_sqlcode_token,
960 p_token1_value=> sqlcode,
961 p_token2 => g_sqlerrm_token,
962 p_token2_value=> sqlerrm);
963 -- notify caller of an error as UNEXPETED error
964 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
965
966 End Validate_Ship_To_location_ID;
967
968 PROCEDURE Validate_Ship_From_Location_ID (X_Return_Status OUT NOCOPY VARCHAR2,
969 P_Del_Rec IN Del_Rec_Type) IS
970 L_Value VARCHAR2(1) := 'N';
971 L_Intent VARCHAR2(1);
972
973 CURSOR C1 ( P_ID1 NUMBER, P_ID2 NUMBER ) IS
974 SELECT 'X'
975 FROM okx_vendor_sites_v
976 WHERE Vendor_ID = P_ID2
977 AND ID1 = P_ID1;
978
979 CURSOR C2 ( P_ID1 NUMBER, P_ID2 NUMBER ) IS
980 SELECT 'X'
981 FROM okx_locations_v
982 WHERE ID1 = P_ID1
983 AND Organization_ID = P_ID2;
984
985 CURSOR C3 ( P_ID1 NUMBER, P_ID2 NUMBER ) IS
986 SELECT 'X'
987 FROM oke_cust_site_uses_v
988 WHERE ID1 = P_ID1
989 AND Cust_Account_ID = P_ID2;
990
991 CURSOR Header_C ( P_ID NUMBER ) IS
992 SELECT Buy_Or_Sell
993 FROM okc_k_headers_b
994 WHERE ID = P_ID;
995
996
997
998 Begin
999
1000 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1001
1002 IF P_DEL_REC.Ship_From_Location_ID > 0 THEN
1003
1004 IF P_DEL_REC.Ship_From_Org_ID IS NULL THEN
1005
1006 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1007 p_msg_name => g_required_value,
1008 p_token1 => g_col_name_token,
1009 p_token1_value => 'Ship_From_org_ID if ship_from_location_ID is present');
1010 -- notify caller of an error
1011 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1012
1013 -- halt validation
1014 RAISE G_EXCEPTION_HALT_VALIDATION;
1015
1016 ELSE
1017
1018 IF NVL(P_DEL_REC.Direction, 'OUT') = 'OUT' THEN
1019
1020 OPEN C2 ( P_DEL_REC.Ship_From_Location_ID, P_DEL_REC.Ship_From_Org_ID );
1021 FETCH C2 INTO L_Value;
1022 CLOSE C2;
1023
1024 ELSE
1025
1026 OPEN Header_C ( P_DEL_REC.K_Header_ID );
1027 FETCH Header_C INTO L_Intent;
1028 CLOSE Header_C;
1029
1030 IF L_Intent = 'B' THEN
1031
1032 OPEN C1 ( P_DEL_REC.Ship_From_Location_ID, P_DEL_REC.Ship_From_Org_ID );
1033 FETCH C1 INTO L_Value;
1034 CLOSE C1;
1035
1036 ELSE
1037
1038 OPEN C3 ( P_DEL_REC.Ship_From_Location_ID, P_DEL_REC.Ship_From_Org_ID );
1039 FETCH C3 INTO L_Value;
1040 CLOSE C3;
1041
1042 END IF;
1043
1044 END IF;
1045
1046 IF L_Value <> 'X' THEN
1047
1048 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1049 p_msg_name => g_invalid_value,
1050 p_token1 => g_col_name_token,
1051 p_token1_value => 'Ship_From_Location_ID');
1052 -- notify caller of an error
1053 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1054
1055 -- halt validation
1056 RAISE G_EXCEPTION_HALT_VALIDATION;
1057
1058 END IF;
1059
1060 END IF;
1061
1062 END IF;
1063
1064 EXCEPTION
1065 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1066
1067 NULL; -- Even failed, continue validate other attributes
1068
1069 WHEN OTHERS THEN
1070
1071 -- store SQL error message on message stack
1072 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1073 p_msg_name => g_unexpected_error,
1074 p_token1 => g_sqlcode_token,
1075 p_token1_value=> sqlcode,
1076 p_token2 => g_sqlerrm_token,
1077 p_token2_value=> sqlerrm);
1078 -- notify caller of an error as UNEXPETED error
1079 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1080
1081 End Validate_Ship_From_location_ID;
1082
1083
1084 PROCEDURE Validate_In_Process_Flag (X_Return_Status OUT NOCOPY VARCHAR2,
1085 P_Del_Rec IN Del_Rec_Type) IS
1086 L_Value VARCHAR2(1) := 'N';
1087
1088
1089 Begin
1090
1091 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1092
1093 IF P_DEL_REC.In_Process_Flag = 'Y' THEN
1094
1095 IF P_DEL_REC.Po_Ref_1 IS NULL AND P_DEL_REC.Mps_Transaction_ID IS NULL AND P_DEL_REC.Shipping_Request_ID IS NULL THEN
1096
1097
1098 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1099 p_msg_name => g_invalid_value,
1100 p_token1 => g_col_name_token,
1101 p_token1_value => 'In_Process_Flag');
1102 -- notify caller of an error
1103 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1104
1105 -- halt validation
1106 RAISE G_EXCEPTION_HALT_VALIDATION;
1107
1108 END IF;
1109
1110 END IF;
1111
1112 EXCEPTION
1113 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1114
1115 NULL; -- Even failed, continue validate other attributes
1116
1117 WHEN OTHERS THEN
1118
1119 -- store SQL error message on message stack
1120 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1121 p_msg_name => g_unexpected_error,
1122 p_token1 => g_sqlcode_token,
1123 p_token1_value=> sqlcode,
1124 p_token2 => g_sqlerrm_token,
1125 p_token2_value=> sqlerrm);
1126 -- notify caller of an error as UNEXPETED error
1127 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1128
1129 End Validate_In_Process_Flag;
1130
1131
1132 PROCEDURE Validate_Start_Date (X_Return_Status OUT NOCOPY VARCHAR2,
1133 P_Del_Rec IN Del_Rec_Type) IS
1134 L_Value VARCHAR2(1) := 'N';
1135 L_Start_Date DATE;
1136 L_End_Date DATE;
1137
1138 CURSOR C ( P_ID NUMBER ) IS
1139 SELECT Start_Date, End_Date
1140 FROM okc_k_lines_b
1141 WHERE ID = P_ID;
1142
1143
1144 Begin
1145
1146 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1147
1148
1149 IF P_DEL_REC.Start_Date IS NOT NULL THEN
1150
1151 OPEN C (P_DEL_REC.K_Line_ID);
1152 FETCH C INTO L_Start_Date, L_End_Date;
1153 CLOSE C;
1154
1155 IF P_DEL_REC.Start_Date < NVL(L_Start_Date, P_DEL_REC.Start_Date + 1) OR P_DEL_REC.Start_Date > NVL( L_End_Date, P_DEL_REC.Start_Date) OR P_DEL_REC.Start_Date > P_DEL_REC.End_Date THEN
1156
1157 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1158 p_msg_name => g_invalid_value,
1159 p_token1 => g_col_name_token,
1160 p_token1_value => 'Start_Date');
1161 -- notify caller of an error
1162 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1163
1164 -- halt validation
1165 RAISE G_EXCEPTION_HALT_VALIDATION;
1166
1167 END IF;
1168
1169 END IF;
1170
1171 EXCEPTION
1172 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1173
1174 NULL; -- Even failed, continue validate other attributes
1175
1176 WHEN OTHERS THEN
1177
1178 -- store SQL error message on message stack
1179 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1180 p_msg_name => g_unexpected_error,
1181 p_token1 => g_sqlcode_token,
1182 p_token1_value=> sqlcode,
1183 p_token2 => g_sqlerrm_token,
1184 p_token2_value=> sqlerrm);
1185 -- notify caller of an error as UNEXPETED error
1186 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1187
1188 End Validate_Start_Date;
1189
1190
1191 PROCEDURE Validate_End_Date (X_Return_Status OUT NOCOPY VARCHAR2,
1192 P_Del_Rec IN Del_Rec_Type) IS
1193 L_Value VARCHAR2(1) := 'N';
1194 L_Start_Date DATE;
1195 L_End_Date DATE;
1196
1197 CURSOR C ( P_ID NUMBER ) IS
1198 SELECT Start_Date, End_Date
1199 FROM okc_k_lines_b
1200 WHERE ID = P_ID;
1201
1202
1203 Begin
1204
1205 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1206
1207 IF P_DEL_REC.End_Date IS NOT NULL THEN
1208
1209 OPEN C ( P_DEL_REC.K_Line_ID );
1210 FETCH C INTO L_Start_Date, L_End_Date;
1211 CLOSE C;
1212
1213 IF P_DEL_REC.End_Date < NVL(L_Start_Date, P_DEL_REC.End_Date + 1) OR P_DEL_REC.End_Date > NVL( L_End_Date, P_DEL_REC.End_Date) OR P_DEL_REC.End_Date < P_DEL_REC.Start_Date THEN
1214
1215 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1216 p_msg_name => g_invalid_value,
1217 p_token1 => g_col_name_token,
1218 p_token1_value => 'End_Date');
1219 -- notify caller of an error
1220 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1221
1222 -- halt validation
1223 RAISE G_EXCEPTION_HALT_VALIDATION;
1224
1225 END IF;
1226
1227 END IF;
1228
1229 EXCEPTION
1230 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1231
1232 NULL; -- Even failed, continue validate other attributes
1233
1234 WHEN OTHERS THEN
1235
1236 -- store SQL error message on message stack
1237 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1238 p_msg_name => g_unexpected_error,
1239 p_token1 => g_sqlcode_token,
1240 p_token1_value=> sqlcode,
1241 p_token2 => g_sqlerrm_token,
1242 p_token2_value=> sqlerrm);
1243 -- notify caller of an error as UNEXPETED error
1244 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1245
1246 End Validate_End_Date;
1247
1248 PROCEDURE Validate_Need_By_Date (X_Return_Status OUT NOCOPY VARCHAR2,
1249 P_Del_Rec IN Del_Rec_Type) IS
1250 L_Value VARCHAR2(1) := 'N';
1251 L_Start_Date DATE;
1252 L_End_Date DATE;
1253
1254 CURSOR C ( P_ID NUMBER ) IS
1255 SELECT Start_Date, End_Date
1256 FROM okc_k_lines_b
1257 WHERE ID = P_ID;
1258
1259
1260 Begin
1261
1262 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1263
1264 IF P_DEL_REC.Need_By_Date IS NOT NULL THEN
1265
1266 OPEN C ( P_DEL_REC.K_Line_ID );
1267 FETCH C INTO L_Start_Date, L_End_Date;
1268 CLOSE C;
1269
1270 IF P_DEL_REC.Need_By_Date < NVL(L_Start_Date, P_DEL_REC.Need_By_Date + 1)
1271 OR P_DEL_REC.Need_By_Date > NVL( L_End_Date, P_DEL_REC.Need_By_Date)
1272 OR P_DEL_REC.Need_By_Date < NVL(P_DEL_REC.Start_Date, P_DEL_REC.Need_By_Date + 1)
1273 OR P_DEL_REC.Need_By_Date > NVL(P_DEL_REC.End_Date, P_DEL_REC.Need_By_Date) THEN
1274
1275 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1276 p_msg_name => g_invalid_value,
1277 p_token1 => g_col_name_token,
1278 p_token1_value => 'Need_By_Date');
1279 -- notify caller of an error
1280 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1281
1282 -- halt validation
1283 RAISE G_EXCEPTION_HALT_VALIDATION;
1284
1285 END IF;
1286
1287 END IF;
1288
1289 EXCEPTION
1290 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1291
1292 NULL; -- Even failed, continue validate other attributes
1293
1294 WHEN OTHERS THEN
1295
1296 -- store SQL error message on message stack
1297 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1298 p_msg_name => g_unexpected_error,
1299 p_token1 => g_sqlcode_token,
1300 p_token1_value=> sqlcode,
1301 p_token2 => g_sqlerrm_token,
1302 p_token2_value=> sqlerrm);
1303 -- notify caller of an error as UNEXPETED error
1304 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1305
1306 End Validate_Need_By_Date;
1307
1308 PROCEDURE Validate_Currency_Code (X_Return_Status OUT NOCOPY VARCHAR2,
1309 P_Del_Rec IN Del_Rec_Type) IS
1310 L_Value VARCHAR2(1) := 'N';
1311
1312 CURSOR C ( P_CODE VARCHAR2 ) IS
1313 SELECT 'X'
1314 FROM fnd_currencies_vl
1315 WHERE Enabled_Flag = 'Y'
1316 AND Sysdate BETWEEN NVL(Start_Date_Active, Sysdate)
1317 AND NVL(End_Date_Active, Sysdate)
1318 AND Currency_Code = P_CODE;
1319
1320
1321 Begin
1322
1323 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1324
1325 IF P_DEL_REC.Currency_Code IS NOT NULL THEN
1326
1327 OPEN C ( P_DEL_REC.Currency_Code );
1328 FETCH C INTO L_Value;
1329 CLOSE C;
1330
1331 IF L_Value <> 'X' THEN
1332
1333 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1334 p_msg_name => g_invalid_value,
1335 p_token1 => g_col_name_token,
1336 p_token1_value => 'Currency_Code');
1337 -- notify caller of an error
1338 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1339
1340 -- halt validation
1341 RAISE G_EXCEPTION_HALT_VALIDATION;
1342
1343 END IF;
1344
1345 END IF;
1346
1347 EXCEPTION
1348 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1349
1350 NULL; -- Even failed, continue validate other attributes
1351
1352 WHEN OTHERS THEN
1353
1354 -- store SQL error message on message stack
1355 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1356 p_msg_name => g_unexpected_error,
1357 p_token1 => g_sqlcode_token,
1358 p_token1_value=> sqlcode,
1359 p_token2 => g_sqlerrm_token,
1360 p_token2_value=> sqlerrm);
1361 -- notify caller of an error as UNEXPETED error
1362 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1363
1364 End Validate_Currency_Code;
1365
1366 PROCEDURE Validate_UOM_Code (X_Return_Status OUT NOCOPY VARCHAR2,
1367 P_Del_Rec IN Del_Rec_Type) IS
1368 L_Value VARCHAR2(1) := 'N';
1369
1370 CURSOR C1 ( P_CODE VARCHAR2, P_ID NUMBER ) IS
1371 SELECT 'X'
1372 FROM mtl_item_uoms_view
1373 WHERE UOM_Code = P_Code
1374 AND Inventory_Item_ID = P_ID;
1375
1376 CURSOR C2 ( P_CODE VARCHAR2 ) IS
1377 SELECT 'X'
1378 FROM mtl_units_of_measure
1379 WHERE Sysdate < NVL(Disable_Date, Sysdate + 1)
1380 AND UOM_Code = P_Code;
1381
1382 Begin
1383
1384 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1385
1386 IF P_DEL_REC.UOM_Code IS NOT NULL THEN
1387
1388 IF P_DEL_REC.Item_ID IS NOT NULL THEN
1389
1390
1391 OPEN C1 ( P_DEL_REC.UOM_Code, P_DEL_REC.Item_ID );
1392 FETCH C1 INTO L_Value;
1393 CLOSE C1;
1394
1395 ELSE
1396
1397 OPEN C2 ( P_DEL_REC.UOM_Code );
1398 FETCH C2 INTO L_Value;
1399 CLOSE C2;
1400
1401 END IF;
1402
1403 IF L_Value <> 'X' THEN
1404
1405 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1406 p_msg_name => g_invalid_value,
1407 p_token1 => g_col_name_token,
1408 p_token1_value => 'UOM_Code');
1409 -- notify caller of an error
1410 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1411
1412 -- halt validation
1413 RAISE G_EXCEPTION_HALT_VALIDATION;
1414
1415 END IF;
1416
1417 END IF;
1418
1419 EXCEPTION
1420 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1421
1422 NULL; -- Even failed, continue validate other attributes
1423
1424 WHEN OTHERS THEN
1425
1426 -- store SQL error message on message stack
1427 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1428 p_msg_name => g_unexpected_error,
1429 p_token1 => g_sqlcode_token,
1430 p_token1_value=> sqlcode,
1431 p_token2 => g_sqlerrm_token,
1432 p_token2_value=> sqlerrm);
1433 -- notify caller of an error as UNEXPETED error
1434 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1435
1436 End Validate_UOM_Code;
1437
1438 PROCEDURE Validate_Shipping_Request_ID (X_Return_Status OUT NOCOPY VARCHAR2,
1439 P_Del_Rec IN Del_Rec_Type) IS
1440 L_Value VARCHAR2(1) := 'N';
1441
1442 CURSOR C ( P_ID NUMBER ) IS
1443 SELECT 'X'
1444 FROM wsh_delivery_details
1445 WHERE Delivery_Detail_ID = P_ID;
1446
1447 Begin
1448
1449 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1450
1451 IF P_DEL_REC.Shipping_Request_ID IS NOT NULL THEN
1452
1453
1454 OPEN C ( P_DEL_REC.Shipping_Request_ID );
1455 FETCH C INTO L_Value;
1456 CLOSE C;
1457
1458
1459
1460 IF L_Value <> 'X' THEN
1461
1462 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1463 p_msg_name => g_invalid_value,
1464 p_token1 => g_col_name_token,
1465 p_token1_value => 'Shipping_Request_ID');
1466 -- notify caller of an error
1467 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1468
1469 -- halt validation
1470 RAISE G_EXCEPTION_HALT_VALIDATION;
1471
1472 END IF;
1473
1474 END IF;
1475
1476 EXCEPTION
1477 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1478
1479 NULL; -- Even failed, continue validate other attributes
1480
1481 WHEN OTHERS THEN
1482
1483 -- store SQL error message on message stack
1484 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1485 p_msg_name => g_unexpected_error,
1486 p_token1 => g_sqlcode_token,
1487 p_token1_value=> sqlcode,
1488 p_token2 => g_sqlerrm_token,
1489 p_token2_value=> sqlerrm);
1490 -- notify caller of an error as UNEXPETED error
1491 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1492
1493 End Validate_Shipping_Request_ID;
1494
1495
1496 PROCEDURE Validate_Mps_Transaction_ID (X_Return_Status OUT NOCOPY VARCHAR2,
1497 P_Del_Rec IN Del_Rec_Type) IS
1498 L_Value VARCHAR2(1) := 'N';
1499
1500 CURSOR C ( P_ID NUMBER ) IS
1501 SELECT 'X'
1502 FROM mrp_schedule_dates
1503 WHERE Mps_Transaction_ID = P_ID;
1504
1505 Begin
1506
1507 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1508
1509 IF P_DEL_REC.Shipping_Request_ID IS NOT NULL THEN
1510
1511
1512 OPEN C ( P_DEL_REC.Mps_Transaction_ID );
1513 FETCH C INTO L_Value;
1514 CLOSE C;
1515
1516
1517
1518 IF L_Value <> 'X' THEN
1519
1520 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1521 p_msg_name => g_invalid_value,
1522 p_token1 => g_col_name_token,
1523 p_token1_value => 'Mps_Transaction_ID');
1524 -- notify caller of an error
1525 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1526
1527 -- halt validation
1528 RAISE G_EXCEPTION_HALT_VALIDATION;
1529
1530 END IF;
1531
1532 END IF;
1533
1534 EXCEPTION
1535 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1536
1537 NULL; -- Even failed, continue validate other attributes
1538
1539 WHEN OTHERS THEN
1540
1541 -- store SQL error message on message stack
1542 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1543 p_msg_name => g_unexpected_error,
1544 p_token1 => g_sqlcode_token,
1545 p_token1_value=> sqlcode,
1546 p_token2 => g_sqlerrm_token,
1547 p_token2_value=> sqlerrm);
1548 -- notify caller of an error as UNEXPETED error
1549 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1550
1551 End Validate_Mps_Transaction_ID;
1552
1553
1554 PROCEDURE Validate_Unit_Number (X_Return_Status OUT NOCOPY VARCHAR2,
1555 P_Del_Rec IN Del_Rec_Type) IS
1556 L_Value VARCHAR2(1) := 'N';
1557
1558 CURSOR C ( P_Number VARCHAR2 ) IS
1559 SELECT 'X'
1560 FROM pjm_unit_numbers_lov_v
1561 WHERE Unit_Number = P_Number;
1562
1563
1564
1565 Begin
1566
1567 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1568
1569 IF P_DEL_REC.Unit_Number IS NOT NULL THEN
1570
1571 OPEN C ( P_DEL_REC.Unit_Number );
1572 FETCH C INTO L_Value;
1573 CLOSE C;
1574
1575
1576
1577 IF L_Value <> 'X' THEN
1578
1579 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1580 p_msg_name => g_invalid_value,
1581 p_token1 => g_col_name_token,
1582 p_token1_value => 'Unit_Number');
1583 -- notify caller of an error
1584 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1585
1586 -- halt validation
1587 RAISE G_EXCEPTION_HALT_VALIDATION;
1588
1589 END IF;
1590
1591 END IF;
1592
1593 EXCEPTION
1594 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1595
1596 NULL; -- Even failed, continue validate other attributes
1597
1598 WHEN OTHERS THEN
1599
1600 -- store SQL error message on message stack
1601 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1602 p_msg_name => g_unexpected_error,
1603 p_token1 => g_sqlcode_token,
1604 p_token1_value=> sqlcode,
1605 p_token2 => g_sqlerrm_token,
1606 p_token2_value=> sqlerrm);
1607 -- notify caller of an error as UNEXPETED error
1608 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1609
1610 End Validate_Unit_Number;
1611
1612
1613
1614 PROCEDURE Validate_Plan_Name (X_Return_Status OUT NOCOPY VARCHAR2,
1615 P_Del_Rec IN Del_Rec_Type) IS
1616 L_Value VARCHAR2(1) := 'N';
1617 L_ID NUMBER;
1618
1619 CURSOR C ( P_Designator VARCHAR2, P_ID NUMBER ) IS
1620 SELECT 'X'
1621 FROM mrp_designators_view
1622 WHERE Designator_Type = 1
1623 AND NVL(Disable_Date, TRUNC(Sysdate) + 1) > TRUNC(Sysdate)
1624 AND Organization_ID = P_ID
1625 AND Designator = P_Designator;
1626
1627 Begin
1628
1629 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1630
1631 IF P_DEL_REC.Ndb_Schedule_Designator IS NOT NULL THEN
1632
1633 IF P_DEL_REC.Direction = 'OUT' THEN
1634
1635 IF P_DEL_REC.Ship_From_Org_ID IS NOT NULL THEN
1636
1637 L_ID := P_DEL_REC.Ship_From_Org_ID;
1638
1639 ELSE
1640
1641 L_ID := P_DEL_REC.Inventory_Org_ID;
1642
1643 END IF;
1644
1645 ELSE
1646
1647 IF P_DEL_REC.Ship_From_Org_ID IS NOT NULL THEN
1648
1649 L_ID := P_DEL_REC.Ship_From_Org_ID;
1650
1651 ELSE
1652
1653 L_ID := P_DEL_REC.Inventory_Org_ID;
1654
1655 END IF;
1656
1657 END IF;
1658
1659 OPEN C ( P_DEL_REC.Ndb_Schedule_Designator, L_ID );
1660 FETCH C INTO L_Value;
1661 CLOSE C;
1662
1663
1664
1665 IF L_Value <> 'X' THEN
1666
1667 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1668 p_msg_name => g_invalid_value,
1669 p_token1 => g_col_name_token,
1670 p_token1_value => 'Ndb_Schedule_Designator');
1671 -- notify caller of an error
1672 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1673
1674 -- halt validation
1675 RAISE G_EXCEPTION_HALT_VALIDATION;
1676
1677 END IF;
1678
1679 END IF;
1680
1681 EXCEPTION
1682 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1683
1684 NULL; -- Even failed, continue validate other attributes
1685
1686 WHEN OTHERS THEN
1687
1688 -- store SQL error message on message stack
1689 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1690 p_msg_name => g_unexpected_error,
1691 p_token1 => g_sqlcode_token,
1692 p_token1_value=> sqlcode,
1693 p_token2 => g_sqlerrm_token,
1694 p_token2_value=> sqlerrm);
1695 -- notify caller of an error as UNEXPETED error
1696 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1697
1698 End Validate_Plan_Name;
1699
1700 PROCEDURE Validate_Volume_UOM_Code (X_Return_Status OUT NOCOPY VARCHAR2,
1701 P_Del_Rec IN Del_Rec_Type) IS
1702 L_Value VARCHAR2(1) := 'N';
1703 L_ID NUMBER;
1704
1705 CURSOR C ( P_Code VARCHAR2, P_ID NUMBER ) IS
1706 SELECT 'X'
1707 FROM mtl_units_of_measure uom, wsh_shipping_parameters wsp
1708 WHERE uom.uom_class = wsp.volume_uom_class
1709 AND wsp.organization_ID = P_ID
1710 AND Sysdate < NVL(Disable_Date, Sysdate + 1)
1711 AND uom.Uom_Code = P_Code;
1712
1713 Begin
1714
1715 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1716
1717 IF P_DEL_REC.Volume_UOM_Code IS NOT NULL THEN
1718
1719 IF P_DEL_REC.Direction = 'OUT' THEN
1720
1721 IF P_DEL_REC.Ship_From_Org_ID IS NOT NULL THEN
1722
1723 L_ID := P_DEL_REC.Ship_From_Org_ID;
1724
1725 ELSE
1726
1727 L_ID := P_DEL_REC.Inventory_Org_ID;
1728
1729 END IF;
1730
1731 ELSE
1732
1733 IF P_DEL_REC.Ship_To_Org_ID IS NOT NULL THEN
1734
1735 L_ID := P_DEL_REC.Ship_To_Org_ID;
1736
1737 ELSE
1738
1739 L_ID := P_DEL_REC.Inventory_Org_ID;
1740
1741 END IF;
1742
1743 END IF;
1744
1745 OPEN C ( P_DEL_REC.Volume_UOM_Code, L_ID );
1746 FETCH C INTO L_Value;
1747 CLOSE C;
1748
1749
1750
1751 IF L_Value <> 'X' THEN
1752
1753 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1754 p_msg_name => g_invalid_value,
1755 p_token1 => g_col_name_token,
1756 p_token1_value => 'Volume_Uom_Code');
1757 -- notify caller of an error
1758 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1759
1760 -- halt validation
1761 RAISE G_EXCEPTION_HALT_VALIDATION;
1762
1763 END IF;
1764
1765 END IF;
1766
1767 EXCEPTION
1768 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1769
1770 NULL; -- Even failed, continue validate other attributes
1771
1772 WHEN OTHERS THEN
1773
1774 -- store SQL error message on message stack
1775 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1776 p_msg_name => g_unexpected_error,
1777 p_token1 => g_sqlcode_token,
1778 p_token1_value=> sqlcode,
1779 p_token2 => g_sqlerrm_token,
1780 p_token2_value=> sqlerrm);
1781 -- notify caller of an error as UNEXPETED error
1782 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1783
1784 End Validate_Volume_UOM_Code;
1785
1786 PROCEDURE Validate_Weight_UOM_Code (X_Return_Status OUT NOCOPY VARCHAR2,
1787 P_Del_Rec IN Del_Rec_Type) IS
1788 L_Value VARCHAR2(1) := 'N';
1789 L_ID NUMBER;
1790
1791 CURSOR C ( P_Code VARCHAR2, P_ID NUMBER ) IS
1792 SELECT 'X'
1793 FROM mtl_units_of_measure uom, wsh_shipping_parameters wsp
1794 WHERE uom.uom_class = wsp.weight_uom_class
1795 AND wsp.organization_ID = P_ID
1796 AND Sysdate < NVL(Disable_Date, Sysdate + 1)
1797 AND uom.Uom_Code = P_Code;
1798
1799 Begin
1800
1801 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1802
1803 IF P_DEL_REC.Weight_UOM_Code IS NOT NULL THEN
1804
1805 IF P_DEL_REC.Direction = 'OUT' THEN
1806
1807 IF P_DEL_REC.Ship_From_Org_ID IS NOT NULL THEN
1808
1809 L_ID := P_DEL_REC.Ship_From_Org_ID;
1810
1811 ELSE
1812
1813 L_ID := P_DEL_REC.Inventory_Org_ID;
1814
1815 END IF;
1816
1817 ELSE
1818
1819 IF P_DEL_REC.Ship_To_Org_ID IS NOT NULL THEN
1820
1821 L_ID := P_DEL_REC.Ship_To_Org_ID;
1822
1823 ELSE
1824
1825 L_ID := P_DEL_REC.Inventory_Org_ID;
1826
1827 END IF;
1828
1829 END IF;
1830
1831 OPEN C ( P_DEL_REC.Volume_UOM_Code, L_ID );
1832 FETCH C INTO L_Value;
1833 CLOSE C;
1834
1835
1836
1837 IF L_Value <> 'X' THEN
1838
1839 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1840 p_msg_name => g_invalid_value,
1841 p_token1 => g_col_name_token,
1842 p_token1_value => 'Weight_Uom_Code');
1843 -- notify caller of an error
1844 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1845
1846 -- halt validation
1847 RAISE G_EXCEPTION_HALT_VALIDATION;
1848
1849 END IF;
1850
1851 END IF;
1852
1853 EXCEPTION
1854 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1855
1856 NULL; -- Even failed, continue validate other attributes
1857
1858 WHEN OTHERS THEN
1859
1860 -- store SQL error message on message stack
1861 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1862 p_msg_name => g_unexpected_error,
1863 p_token1 => g_sqlcode_token,
1864 p_token1_value=> sqlcode,
1865 p_token2 => g_sqlerrm_token,
1866 p_token2_value=> sqlerrm);
1867 -- notify caller of an error as UNEXPETED error
1868 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1869
1870 End Validate_Weight_UOM_Code;
1871
1872 PROCEDURE Validate_Exp_Organization_ID (X_Return_Status OUT NOCOPY VARCHAR2,
1873 P_Del_Rec IN Del_Rec_Type) IS
1874 L_Value VARCHAR2(1) := 'N';
1875
1876 CURSOR C ( P_ID NUMBER ) IS
1877 SELECT 'X'
1878 FROM pa_organizations_all_expend_v
1879 WHERE Active_Flag = 'Y'
1880 AND TRUNC(Sysdate) BETWEEN Date_From AND NVL(Date_To, TRUNC(Sysdate))
1881 AND Organization_ID = P_ID;
1882
1883 Begin
1884
1885 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1886
1887 IF P_DEL_REC.Expenditure_Organization_ID IS NOT NULL THEN
1888
1889 OPEN C ( P_DEL_REC.Expenditure_Organization_ID );
1890 FETCH C INTO L_Value;
1891 CLOSE C;
1892
1893
1894
1895 IF L_Value <> 'X' THEN
1896
1897 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1898 p_msg_name => g_invalid_value,
1899 p_token1 => g_col_name_token,
1900 p_token1_value => 'Expenditure_Organization_ID');
1901 -- notify caller of an error
1902 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1903
1904 -- halt validation
1905 RAISE G_EXCEPTION_HALT_VALIDATION;
1906
1907 END IF;
1908
1909 END IF;
1910
1911 EXCEPTION
1912 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1913
1914 NULL; -- Even failed, continue validate other attributes
1915
1916 WHEN OTHERS THEN
1917
1918 -- store SQL error message on message stack
1919 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1920 p_msg_name => g_unexpected_error,
1921 p_token1 => g_sqlcode_token,
1922 p_token1_value=> sqlcode,
1923 p_token2 => g_sqlerrm_token,
1924 p_token2_value=> sqlerrm);
1925 -- notify caller of an error as UNEXPETED error
1926 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1927
1928 End Validate_Exp_Organization_ID;
1929
1930 PROCEDURE Validate_Destination_Type_Code (X_Return_Status OUT NOCOPY VARCHAR2,
1931 P_Del_Rec IN Del_Rec_Type) IS
1932 L_Value VARCHAR2(1) := 'N';
1933
1934 CURSOR C ( P_Code VARCHAR2 ) IS
1935 SELECT 'X'
1936 FROM po_lookup_codes
1937 WHERE Lookup_Type = 'DESTINATION TYPE'
1938 AND Lookup_Code <> 'SHIP FLOOR'
1939 AND Lookup_Code = P_Code;
1940
1941 Begin
1942
1943 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
1944
1945 IF P_DEL_REC.Destination_Type_Code IS NOT NULL THEN
1946
1947 OPEN C ( P_DEL_REC.Destination_Type_Code );
1948 FETCH C INTO L_Value;
1949 CLOSE C;
1950
1951
1952
1953 IF L_Value <> 'X' THEN
1954
1955 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1956 p_msg_name => g_invalid_value,
1957 p_token1 => g_col_name_token,
1958 p_token1_value => 'Destination_Type_Code');
1959 -- notify caller of an error
1960 X_Return_Status := OKE_API.G_RET_STS_ERROR;
1961
1962 -- halt validation
1963 RAISE G_EXCEPTION_HALT_VALIDATION;
1964
1965 END IF;
1966
1967 END IF;
1968
1969 EXCEPTION
1970 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1971
1972 NULL; -- Even failed, continue validate other attributes
1973
1974 WHEN OTHERS THEN
1975
1976 -- store SQL error message on message stack
1977 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1978 p_msg_name => g_unexpected_error,
1979 p_token1 => g_sqlcode_token,
1980 p_token1_value=> sqlcode,
1981 p_token2 => g_sqlerrm_token,
1982 p_token2_value=> sqlerrm);
1983 -- notify caller of an error as UNEXPETED error
1984 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
1985
1986 End Validate_Destination_Type_Code;
1987
1988 PROCEDURE Validate_Exp_Type (X_Return_Status OUT NOCOPY VARCHAR2,
1989 P_Del_Rec IN Del_Rec_Type) IS
1990 L_Value VARCHAR2(1) := 'N';
1991
1992 CURSOR C ( P_ID NUMBER , P_Type VARCHAR2 ) IS
1993 SELECT 'X'
1994 FROM pa_expenditure_types_expend_v
1995 WHERE System_Linkage_Function = 'VI'
1996 AND ( Project_ID = P_ID OR Project_ID IS NULL )
1997 AND Expenditure_Type = P_Type;
1998
1999 Begin
2000
2001 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
2002
2003 IF P_DEL_REC.Expenditure_Type IS NOT NULL THEN
2004
2005 OPEN C ( P_DEL_REC.Project_ID, P_DEL_REC.Expenditure_Type );
2006 FETCH C INTO L_Value;
2007 CLOSE C;
2008
2009
2010 IF L_Value <> 'X' THEN
2011
2012 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2013 p_msg_name => g_invalid_value,
2014 p_token1 => g_col_name_token,
2015 p_token1_value => 'Expenditure_Type');
2016 -- notify caller of an error
2017 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2018
2019 -- halt validation
2020 RAISE G_EXCEPTION_HALT_VALIDATION;
2021
2022 END IF;
2023
2024 END IF;
2025
2026 EXCEPTION
2027 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2028
2029 NULL; -- Even failed, continue validate other attributes
2030
2031 WHEN OTHERS THEN
2032
2033 -- store SQL error message on message stack
2034 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2035 p_msg_name => g_unexpected_error,
2036 p_token1 => g_sqlcode_token,
2037 p_token1_value=> sqlcode,
2038 p_token2 => g_sqlerrm_token,
2039 p_token2_value=> sqlerrm);
2040 -- notify caller of an error as UNEXPETED error
2041 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
2042
2043 End Validate_Exp_Type;
2044
2045 PROCEDURE Validate_Rate_Type (X_Return_Status OUT NOCOPY VARCHAR2,
2046 P_Del_Rec IN Del_Rec_Type) IS
2047 L_Value VARCHAR2(1) := 'N';
2048
2049 CURSOR C ( P_Type VARCHAR2 ) IS
2050 SELECT 'X'
2051 FROM gl_daily_conversion_types
2052 WHERE Conversion_Type = P_Type;
2053
2054 Begin
2055
2056 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
2057
2058 IF P_DEL_REC.Rate_Type IS NOT NULL THEN
2059
2060 OPEN C ( P_DEL_REC.Rate_Type );
2061 FETCH C INTO L_Value;
2062 CLOSE C;
2063
2064
2065 IF L_Value <> 'X' THEN
2066
2067 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2068 p_msg_name => g_invalid_value,
2069 p_token1 => g_col_name_token,
2070 p_token1_value => 'Rate_Type');
2071 -- notify caller of an error
2072 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2073
2074 -- halt validation
2075 RAISE G_EXCEPTION_HALT_VALIDATION;
2076
2077 END IF;
2078
2079 END IF;
2080
2081 EXCEPTION
2082 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2083
2084 NULL; -- Even failed, continue validate other attributes
2085
2086 WHEN OTHERS THEN
2087
2088 -- store SQL error message on message stack
2089 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2090 p_msg_name => g_unexpected_error,
2091 p_token1 => g_sqlcode_token,
2092 p_token1_value=> sqlcode,
2093 p_token2 => g_sqlerrm_token,
2094 p_token2_value=> sqlerrm);
2095 -- notify caller of an error as UNEXPETED error
2096 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
2097
2098 End Validate_Rate_Type;
2099
2100 PROCEDURE Validate_Flag_Values (X_Return_Status OUT NOCOPY VARCHAR2,
2101 P_Del_Rec IN Del_Rec_Type) IS
2102 Begin
2103
2104 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
2105
2106 IF P_DEL_REC.Defaulted_Flag IS NOT NULL AND P_DEL_REC.Defaulted_Flag NOT IN ('Y', 'N') THEN
2107
2108 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2109 p_msg_name => g_invalid_value,
2110 p_token1 => g_col_name_token,
2111 p_token1_value => 'Defaulted_Flag');
2112 -- notify caller of an error
2113 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2114
2115 -- halt validation
2116 RAISE G_EXCEPTION_HALT_VALIDATION;
2117
2118 END IF;
2119
2120 IF P_DEL_REC.In_Process_Flag IS NOT NULL AND P_DEL_REC.In_Process_Flag NOT IN ('Y', 'N') THEN
2121
2122 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2123 p_msg_name => g_invalid_value,
2124 p_token1 => g_col_name_token,
2125 p_token1_value => 'In_Process_Flag');
2126 -- notify caller of an error
2127 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2128
2129 -- halt validation
2130 RAISE G_EXCEPTION_HALT_VALIDATION;
2131
2132 END IF;
2133
2134 IF P_DEL_REC.Subcontracted_Flag IS NOT NULL AND P_DEL_REC.Subcontracted_Flag NOT IN ('Y', 'N') THEN
2135
2136 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2137 p_msg_name => g_invalid_value,
2138 p_token1 => g_col_name_token,
2139 p_token1_value => 'Subcontracted_Flag');
2140 -- notify caller of an error
2141 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2142
2143 -- halt validation
2144 RAISE G_EXCEPTION_HALT_VALIDATION;
2145
2146 END IF;
2147
2148 IF P_DEL_REC.Dependency_Flag IS NOT NULL AND P_DEL_REC.Dependency_Flag NOT IN ('Y', 'N') THEN
2149
2150 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2151 p_msg_name => g_invalid_value,
2152 p_token1 => g_col_name_token,
2153 p_token1_value => 'Dependency_Flag');
2154 -- notify caller of an error
2155 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2156
2157 -- halt validation
2158 RAISE G_EXCEPTION_HALT_VALIDATION;
2159
2160 END IF;
2161
2162 IF P_DEL_REC.Billable_Flag IS NOT NULL AND P_DEL_REC.Billable_Flag NOT IN ('Y', 'N') THEN
2163
2164 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2165 p_msg_name => g_invalid_value,
2166 p_token1 => g_col_name_token,
2167 p_token1_value => 'Billable_Flag');
2168 -- notify caller of an error
2169 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2170
2171 -- halt validation
2172 RAISE G_EXCEPTION_HALT_VALIDATION;
2173
2174 END IF;
2175
2176 IF P_DEL_REC.Drop_Shipped_Flag IS NOT NULL AND P_DEL_REC.Drop_Shipped_Flag NOT IN ('Y', 'N') THEN
2177
2178 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2179 p_msg_name => g_invalid_value,
2180 p_token1 => g_col_name_token,
2181 p_token1_value => 'Drop_Shipped_Flag');
2182 -- notify caller of an error
2183 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2184
2185 -- halt validation
2186 RAISE G_EXCEPTION_HALT_VALIDATION;
2187
2188 END IF;
2189
2190 IF P_DEL_REC.Completed_Flag IS NOT NULL AND P_DEL_REC.Completed_Flag NOT IN ('Y', 'N') THEN
2191
2192 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2193 p_msg_name => g_invalid_value,
2194 p_token1 => g_col_name_token,
2195 p_token1_value => 'Completed_Flag');
2196 -- notify caller of an error
2197 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2198
2199 -- halt validation
2200 RAISE G_EXCEPTION_HALT_VALIDATION;
2201
2202 END IF;
2203
2204 IF P_DEL_REC.Available_For_Ship_Flag IS NOT NULL AND P_DEL_REC.Available_For_Ship_Flag NOT IN ('Y', 'N') THEN
2205
2206 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2207 p_msg_name => g_invalid_value,
2208 p_token1 => g_col_name_token,
2209 p_token1_value => 'Available_For_Ship_Flag');
2210 -- notify caller of an error
2211 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2212
2213 -- halt validation
2214 RAISE G_EXCEPTION_HALT_VALIDATION;
2215
2216 END IF;
2217
2218 IF P_DEL_REC.Create_Demand IS NOT NULL AND P_DEL_REC.Create_Demand NOT IN ('Y', 'N') THEN
2219
2220 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2221 p_msg_name => g_invalid_value,
2222 p_token1 => g_col_name_token,
2223 p_token1_value => 'Create_Demand');
2224 -- notify caller of an error
2225 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2226
2227 -- halt validation
2228 RAISE G_EXCEPTION_HALT_VALIDATION;
2229
2230 END IF;
2231
2232 IF P_DEL_REC.Ready_To_Procure IS NOT NULL AND P_DEL_REC.Ready_To_Procure NOT IN ('Y', 'N') THEN
2233
2234 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2235 p_msg_name => g_invalid_value,
2236 p_token1 => g_col_name_token,
2237 p_token1_value => 'Ready_To_Procure');
2238 -- notify caller of an error
2239 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2240
2241 -- halt validation
2242 RAISE G_EXCEPTION_HALT_VALIDATION;
2243
2244 END IF;
2245
2246 IF P_DEL_REC.Ready_To_Bill IS NOT NULL AND P_DEL_REC.Ready_To_Bill NOT IN ('Y', 'N') THEN
2247
2248 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2249 p_msg_name => g_invalid_value,
2250 p_token1 => g_col_name_token,
2251 p_token1_value => 'Ready_To_Bill');
2252 -- notify caller of an error
2253 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2254
2255 -- halt validation
2256 RAISE G_EXCEPTION_HALT_VALIDATION;
2257
2258 END IF;
2259
2260 IF P_DEL_REC.Shippable_Flag IS NOT NULL AND P_DEL_REC.Shippable_Flag NOT IN ('Y', 'N') THEN
2261
2262 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2263 p_msg_name => g_invalid_value,
2264 p_token1 => g_col_name_token,
2265 p_token1_value => 'Shippable_Flag');
2266 -- notify caller of an error
2267 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2268
2269 -- halt validation
2270 RAISE G_EXCEPTION_HALT_VALIDATION;
2271
2272 END IF;
2273
2274 IF P_DEL_REC.Cfe_Req_Flag IS NOT NULL AND P_DEL_REC.Cfe_Req_Flag NOT IN ('Y', 'N') THEN
2275
2276 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2277 p_msg_name => g_invalid_value,
2278 p_token1 => g_col_name_token,
2279 p_token1_value => 'Cfe_Req_Flag');
2280 -- notify caller of an error
2281 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2282
2283 -- halt validation
2284 RAISE G_EXCEPTION_HALT_VALIDATION;
2285
2286 END IF;
2287
2288 IF P_DEL_REC.Inspection_Req_Flag IS NOT NULL AND P_DEL_REC.Inspection_Req_Flag NOT IN ('Y', 'N') THEN
2289
2290 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2291 p_msg_name => g_invalid_value,
2292 p_token1 => g_col_name_token,
2293 p_token1_value => 'Inspection_Req_Flag');
2294 -- notify caller of an error
2295 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2296
2297 -- halt validation
2298 RAISE G_EXCEPTION_HALT_VALIDATION;
2299
2300 END IF;
2301
2302 IF P_DEL_REC.Interim_Rpt_Req_flag IS NOT NULL AND P_DEL_REC.Interim_Rpt_Req_Flag NOT IN ('Y', 'N') THEN
2303
2304 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2305 p_msg_name => g_invalid_value,
2306 p_token1 => g_col_name_token,
2307 p_token1_value => 'Interim_Rpt_Req_Flag');
2308 -- notify caller of an error
2309 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2310
2311 -- halt validation
2312 RAISE G_EXCEPTION_HALT_VALIDATION;
2313
2314 END IF;
2315
2316 IF P_DEL_REC.Customer_Approval_Req_Flag IS NOT NULL AND P_DEL_REC.Customer_Approval_Req_Flag NOT IN ('Y', 'N') THEN
2317
2318 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2319 p_msg_name => g_invalid_value,
2320 p_token1 => g_col_name_token,
2321 p_token1_value => 'Customer_Approval_Req_Flag');
2322 -- notify caller of an error
2323 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2324
2325 -- halt validation
2326 RAISE G_EXCEPTION_HALT_VALIDATION;
2327
2328 END IF;
2329
2330 IF P_DEL_REC.Export_Flag IS NOT NULL AND P_DEL_REC.Export_Flag NOT IN ('Y', 'N') THEN
2331
2332 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2333 p_msg_name => g_invalid_value,
2334 p_token1 => g_col_name_token,
2335 p_token1_value => 'Export_Flag');
2336 -- notify caller of an error
2337 X_Return_Status := OKE_API.G_RET_STS_ERROR;
2338
2339 -- halt validation
2340 RAISE G_EXCEPTION_HALT_VALIDATION;
2341
2342 END IF;
2343
2344 EXCEPTION
2345 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2346
2347 NULL; -- Even failed, continue validate other attributes
2348
2349 WHEN OTHERS THEN
2350
2351 -- store SQL error message on message stack
2352 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
2353 p_msg_name => g_unexpected_error,
2354 p_token1 => g_sqlcode_token,
2355 p_token1_value=> sqlcode,
2356 p_token2 => g_sqlerrm_token,
2357 p_token2_value=> sqlerrm);
2358 -- notify caller of an error as UNEXPETED error
2359 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
2360
2361 End Validate_Flag_Values;
2362
2363 FUNCTION check_dependency( p_deliverable_id IN Number) RETURN BOOLEAN IS
2364
2365 CURSOR c IS
2366 select 'x' from oke_dependencies
2367 where dependent_id = p_deliverable_id;
2368
2369 l_result Varchar2(1);
2370 l_found Boolean := FALSE;
2371
2372 BEGIN
2373
2374 open c;
2375 fetch c into l_result;
2376 close c;
2377
2378 if l_result = 'x' then
2379 l_found := TRUE;
2380 else
2381 l_found := FALSE;
2382 end if;
2383
2384 return l_found;
2385
2386 END check_dependency;
2387
2388
2389
2390 FUNCTION get_rec (
2391 p_del_rec IN del_rec_type,
2392 x_no_data_found OUT NOCOPY BOOLEAN
2393 ) RETURN del_rec_type IS
2394
2395 CURSOR del_pk_csr (p_id IN NUMBER) IS
2396 select b.deliverable_id,
2397 b.deliverable_num,
2398 b.project_id,
2399 b.task_id,
2400 b.item_id,
2401 b.k_header_id,
2402 b.k_line_id,
2403 b.delivery_date,
2404 b.status_code,
2405 b.parent_deliverable_id,
2406 b.ship_to_org_id,
2407 b.ship_to_location_id,
2408 b.ship_from_org_id,
2409 b.ship_from_location_id,
2410 b.inventory_org_id,
2411 b.direction,
2412 b.defaulted_flag,
2413 b.in_process_flag,
2414 b.wf_item_key,
2415 b.sub_ref_id,
2416 b.start_date,
2417 b.end_date,
2418 b.priority_code,
2419 b.currency_code,
2420 b.unit_price,
2421 b.uom_code,
2422 b.quantity,
2423 b.country_of_origin_code,
2424 b.subcontracted_flag,
2425 b.dependency_flag,
2426 b.billable_flag,
2427 b.billing_event_id,
2428 b.drop_shipped_flag,
2429 b.completed_flag,
2430 b.available_for_ship_flag,
2431 b.create_demand,
2432 b.ready_to_bill,
2433 b.need_by_date,
2434 b.ready_to_procure,
2435 b.mps_transaction_id,
2436 b.po_ref_1,
2437 b.po_ref_2,
2438 b.po_ref_3,
2439 b.shipping_request_id,
2440 b.unit_number,
2441 b.ndb_schedule_designator,
2442 b.shippable_flag,
2443 b.cfe_req_flag,
2444 b.inspection_req_flag,
2445 b.interim_rpt_req_flag,
2446 b.lot_applies_flag,
2447 b.customer_approval_req_flag,
2448 b.expected_shipment_date,
2449 b.initiate_shipment_date,
2450 b.promised_shipment_date,
2451 b.as_of_date,
2452 b.date_of_first_submission,
2453 b.frequency,
2454 b.acq_doc_number,
2455 b.submission_flag,
2456 b.data_item_subtitle,
2457 b.total_num_of_copies,
2458 b.cdrl_category,
2459 b.data_item_name,
2460 b.export_flag,
2461 b.export_license_num,
2462 b.export_license_res,
2463 b.created_by,
2464 b.creation_date,
2465 b.last_updated_by,
2466 b.last_update_login,
2467 b.last_update_date,
2468 b.attribute_category,
2469 b.attribute1,
2470 b.attribute2,
2471 b.attribute3,
2472 b.attribute4,
2473 b.attribute5,
2474 b.attribute6,
2475 b.attribute7,
2476 b.attribute8,
2477 b.attribute9,
2478 b.attribute10,
2479 b.attribute11,
2480 b.attribute12,
2481 b.attribute13,
2482 b.attribute14,
2483 b.attribute15,
2484 t.description,
2485 t.comments,
2486 t.sfwt_flag,
2487 b.weight,
2488 b.weight_uom_code,
2489 b.volume,
2490 b.volume_uom_code,
2491 b.expenditure_organization_id,
2492 b.expenditure_type,
2493 b.expenditure_item_date,
2494 b.destination_type_code,
2495 b.rate_type,
2496 b.rate_date,
2497 b.exchange_rate,
2498 b.requisition_line_type_id,
2499 b.po_category_id
2500 from oke_k_deliverables_b b, oke_k_deliverables_tl t
2501 where b.deliverable_id = p_id
2502 and t.deliverable_id = p_id
2503 and t.language = userenv('LANG');
2504
2505 l_del_pk del_pk_csr%ROWTYPE;
2506 l_del_rec del_rec_type;
2507
2508 BEGIN
2509 x_no_data_found := TRUE;
2510
2511 -- get current database value
2512
2513 OPEN del_pk_csr(p_del_rec.deliverable_id);
2514 FETCH del_pk_csr INTO l_del_rec.deliverable_id,
2515 l_del_rec.deliverable_num,
2516 l_del_rec.project_id,
2517 l_del_rec.task_id,
2518 l_del_rec.item_id,
2519 l_del_rec.k_header_id,
2520 l_del_rec.k_line_id,
2521 l_del_rec.delivery_date,
2522 l_del_rec.status_code,
2523 l_del_rec.parent_deliverable_id,
2524 l_del_rec.ship_to_org_id,
2525 l_del_rec.ship_to_location_id,
2526 l_del_rec.ship_from_org_id,
2527 l_del_rec.ship_from_location_id,
2528 l_del_rec.inventory_org_id,
2529 l_del_rec.direction,
2530 l_del_rec.defaulted_flag,
2531 l_del_rec.in_process_flag,
2532 l_del_rec.wf_item_key,
2533 l_del_rec.sub_ref_id,
2534 l_del_rec.start_date,
2535 l_del_rec.end_date,
2536 l_del_rec.priority_code,
2537 l_del_rec.currency_code,
2538 l_del_rec.unit_price,
2539 l_del_rec.uom_code,
2540 l_del_rec.quantity,
2541 l_del_rec.country_of_origin_code,
2542 l_del_rec.subcontracted_flag,
2543 l_del_rec.dependency_flag,
2544 l_del_rec.billable_flag,
2545 l_del_rec.billing_event_id,
2546 l_del_rec.drop_shipped_flag,
2547 l_del_rec.completed_flag,
2548 l_del_rec.available_for_ship_flag,
2549 l_del_rec.create_demand,
2550 l_del_rec.ready_to_bill,
2551 l_del_rec.need_by_date,
2552 l_del_rec.ready_to_procure,
2553 l_del_rec.mps_transaction_id,
2554 l_del_rec.po_ref_1,
2555 l_del_rec.po_ref_2,
2556 l_del_rec.po_ref_3,
2557 l_del_rec.shipping_request_id,
2558 l_del_rec.unit_number,
2559 l_del_rec.ndb_schedule_designator,
2560 l_del_rec.shippable_flag,
2561 l_del_rec.cfe_req_flag,
2562 l_del_rec.inspection_req_flag,
2563 l_del_rec.interim_rpt_req_flag,
2564 l_del_rec.lot_applies_flag,
2565 l_del_rec.customer_approval_req_flag,
2566 l_del_rec.expected_shipment_date,
2567 l_del_rec.initiate_shipment_date,
2568 l_del_rec.promised_shipment_date,
2569 l_del_rec.as_of_date,
2570 l_del_rec.date_of_first_submission,
2571 l_del_rec.frequency,
2572 l_del_rec.acq_doc_number,
2573 l_del_rec.submission_flag,
2574 l_del_rec.data_item_subtitle,
2575 l_del_rec.total_num_of_copies,
2576 l_del_rec.cdrl_category,
2577 l_del_rec.data_item_name,
2578 l_del_rec.export_flag,
2579 l_del_rec.export_license_num,
2580 l_del_rec.export_license_res,
2581 l_del_rec.created_by,
2582 l_del_rec.creation_date,
2583 l_del_rec.last_updated_by,
2584 l_del_rec.last_update_login,
2585 l_del_rec.last_update_date,
2586 l_del_rec.attribute_category,
2587 l_del_rec.attribute1,
2588 l_del_rec.attribute2,
2589 l_del_rec.attribute3,
2590 l_del_rec.attribute4,
2591 l_del_rec.attribute5,
2592 l_del_rec.attribute6,
2593 l_del_rec.attribute7,
2594 l_del_rec.attribute8,
2595 l_del_rec.attribute9,
2596 l_del_rec.attribute10,
2597 l_del_rec.attribute11,
2598 l_del_rec.attribute12,
2599 l_del_rec.attribute13,
2600 l_del_rec.attribute14,
2601 l_del_rec.attribute15,
2602 l_del_rec.description,
2603 l_del_rec.comments,
2604 l_del_rec.sfwt_flag,
2605 l_del_rec.weight,
2606 l_del_rec.weight_uom_code,
2607 l_del_rec.volume,
2608 l_del_rec.volume_uom_code,
2609 l_del_rec.expenditure_organization_id,
2610 l_del_rec.expenditure_type,
2611 l_del_rec.expenditure_item_date,
2612 l_del_rec.destination_type_code,
2613 l_del_rec.rate_type,
2614 l_del_rec.rate_date,
2615 l_del_rec.exchange_rate,
2616 l_del_rec.requisition_line_type_id,
2617 l_del_rec.po_category_id;
2618
2619 x_no_data_found := del_pk_csr%NOTFOUND;
2620
2621 CLOSE del_pk_csr;
2622
2623 RETURN(l_del_rec);
2624
2625 END get_rec;
2626
2627 FUNCTION get_rec (
2628 p_del_rec IN del_rec_type)RETURN del_rec_type IS
2629 l_row_notfound BOOLEAN := TRUE;
2630
2631 BEGIN
2632 RETURN(get_rec(p_del_rec, l_row_notfound));
2633 END get_rec;
2634
2635 FUNCTION null_out_defaults(
2636 p_del_rec IN del_rec_type) RETURN del_rec_type IS
2637
2638 l_del_rec del_rec_type := p_del_rec;
2639
2640 BEGIN
2641
2642
2643
2644 IF l_del_rec.DELIVERABLE_ID = OKE_API.G_MISS_NUM THEN
2645 l_del_rec.DELIVERABLE_ID := NULL;
2646 END IF;
2647
2648 IF l_del_rec.DELIVERABLE_NUM = OKE_API.G_MISS_CHAR THEN
2649 l_del_rec.DELIVERABLE_NUM := NULL;
2650 END IF;
2651
2652 IF l_del_rec.PROJECT_ID = OKE_API.G_MISS_NUM THEN
2653 l_del_rec.PROJECT_ID := NULL;
2654 END IF;
2655
2656 IF l_del_rec.TASK_ID = OKE_API.G_MISS_NUM THEN
2657 l_del_rec.TASK_ID := NULL;
2658 END IF;
2659
2660 IF l_del_rec.ITEM_ID = OKE_API.G_MISS_NUM THEN
2661 l_del_rec.ITEM_ID := NULL;
2662 END IF;
2663
2664 IF l_del_rec.K_HEADER_ID = OKE_API.G_MISS_NUM THEN
2665 l_del_rec.K_HEADER_ID := NULL;
2666 END IF;
2667
2668 IF l_del_rec.K_LINE_ID = OKE_API.G_MISS_NUM THEN
2669 l_del_rec.K_LINE_ID := NULL;
2670 END IF;
2671
2672 IF l_del_rec.DELIVERY_DATE = OKE_API.G_MISS_DATE THEN
2673 l_del_rec.DELIVERY_DATE := NULL;
2674 END IF;
2675
2676 IF l_del_rec.STATUS_CODE = OKE_API.G_MISS_CHAR THEN
2677 l_del_rec.STATUS_CODE := NULL;
2678 END IF;
2679
2680 IF l_del_rec.PARENT_DELIVERABLE_ID = OKE_API.G_MISS_NUM THEN
2681 l_del_rec.PARENT_DELIVERABLE_ID := NULL;
2682 END IF;
2683
2684 IF l_del_rec.SHIP_TO_ORG_ID = OKE_API.G_MISS_NUM THEN
2685 l_del_rec.SHIP_TO_ORG_ID := NULL;
2686 END IF;
2687
2688 IF l_del_rec.SHIP_TO_LOCATION_ID = OKE_API.G_MISS_NUM THEN
2689 l_del_rec.SHIP_TO_LOCATION_ID := NULL;
2690 END IF;
2691
2692 IF l_del_rec.SHIP_FROM_ORG_ID = OKE_API.G_MISS_NUM THEN
2693 l_del_rec.SHIP_FROM_ORG_ID := NULL;
2694 END IF;
2695
2696 IF l_del_rec.SHIP_FROM_LOCATION_ID = OKE_API.G_MISS_NUM THEN
2697 l_del_rec.SHIP_FROM_LOCATION_ID := NULL;
2698 END IF;
2699
2700 IF l_del_rec.INVENTORY_ORG_ID = OKE_API.G_MISS_NUM THEN
2701 l_del_rec.INVENTORY_ORG_ID := NULL;
2702 END IF;
2703
2704 IF l_del_rec.DIRECTION = OKE_API.G_MISS_CHAR THEN
2705 l_del_rec.DIRECTION := NULL;
2706 END IF;
2707
2708 IF l_del_rec.DEFAULTED_FLAG = OKE_API.G_MISS_CHAR THEN
2709 l_del_rec.DEFAULTED_FLAG := NULL;
2710 END IF;
2711
2712 IF l_del_rec.IN_PROCESS_FLAG = OKE_API.G_MISS_CHAR THEN
2713 l_del_rec.IN_PROCESS_FLAG := NULL;
2714 END IF;
2715
2716 IF l_del_rec.WF_ITEM_KEY = OKE_API.G_MISS_CHAR THEN
2717 l_del_rec.WF_ITEM_KEY := NULL;
2718 END IF;
2719
2720 IF l_del_rec.SUB_REF_ID = OKE_API.G_MISS_NUM THEN
2721 l_del_rec.SUB_REF_ID := NULL;
2722 END IF;
2723
2724 IF l_del_rec.START_DATE = OKE_API.G_MISS_DATE THEN
2725 l_del_rec.START_DATE := NULL;
2726 END IF;
2727
2728 IF l_del_rec.END_DATE = OKE_API.G_MISS_DATE THEN
2729 l_del_rec.END_DATE := NULL;
2730 END IF;
2731
2732 IF l_del_rec.PRIORITY_CODE = OKE_API.G_MISS_CHAR THEN
2733 l_del_rec.PRIORITY_CODE := NULL;
2734 END IF;
2735
2736 IF l_del_rec.CURRENCY_CODE = OKE_API.G_MISS_CHAR THEN
2737 l_del_rec.CURRENCY_CODE := NULL;
2738 END IF;
2739
2740 IF l_del_rec.UNIT_PRICE = OKE_API.G_MISS_NUM THEN
2741 l_del_rec.UNIT_PRICE := NULL;
2742 END IF;
2743
2744 IF l_del_rec.UOM_CODE = OKE_API.G_MISS_CHAR THEN
2745 l_del_rec.UOM_CODE := NULL;
2746 END IF;
2747
2748 IF l_del_rec.QUANTITY = OKE_API.G_MISS_NUM THEN
2749 l_del_rec.QUANTITY := NULL;
2750 END IF;
2751
2752 IF l_del_rec.COUNTRY_OF_ORIGIN_CODE = OKE_API.G_MISS_CHAR THEN
2753 l_del_rec.COUNTRY_OF_ORIGIN_CODE := NULL;
2754 END IF;
2755
2756 IF l_del_rec.SUBCONTRACTED_FLAG = OKE_API.G_MISS_CHAR THEN
2757 l_del_rec.SUBCONTRACTED_FLAG := NULL;
2758 END IF;
2759
2760 IF l_del_rec.DEPENDENCY_FLAG = OKE_API.G_MISS_CHAR THEN
2761 l_del_rec.DEPENDENCY_FLAG := NULL;
2762 END IF;
2763
2764
2765
2766 IF l_del_rec.BILLABLE_FLAG = OKE_API.G_MISS_CHAR THEN
2767 l_del_rec.BILLABLE_FLAG := NULL;
2768 END IF;
2769
2770 IF l_del_rec.BILLING_EVENT_ID = OKE_API.G_MISS_NUM THEN
2771 l_del_rec.BILLING_EVENT_ID := NULL;
2772 END IF;
2773
2774 IF l_del_rec.DROP_SHIPPED_FLAG = OKE_API.G_MISS_CHAR THEN
2775 l_del_rec.DROP_SHIPPED_FLAG := NULL;
2776 END IF;
2777
2778 IF l_del_rec.COMPLETED_FLAG = OKE_API.G_MISS_CHAR THEN
2779 l_del_rec.COMPLETED_FLAG := NULL;
2780 END IF;
2781
2782 IF l_del_rec.AVAILABLE_FOR_SHIP_FLAG = OKE_API.G_MISS_CHAR THEN
2783 l_del_rec.AVAILABLE_FOR_SHIP_FLAG := NULL;
2784 END IF;
2785
2786 IF l_del_rec.CREATE_DEMAND = OKE_API.G_MISS_CHAR THEN
2787 l_del_rec.CREATE_DEMAND := NULL;
2788 END IF;
2789
2790 IF l_del_rec.READY_TO_BILL = OKE_API.G_MISS_CHAR THEN
2791 l_del_rec.READY_TO_BILL := NULL;
2792 END IF;
2793
2794 IF l_del_rec.NEED_BY_DATE = OKE_API.G_MISS_DATE THEN
2795 l_del_rec.NEED_BY_DATE := NULL;
2796 END IF;
2797
2798 IF l_del_rec.READY_TO_PROCURE = OKE_API.G_MISS_CHAR THEN
2799 l_del_rec.READY_TO_PROCURE := NULL;
2800 END IF;
2801
2802 IF l_del_rec.MPS_TRANSACTION_ID = OKE_API.G_MISS_NUM THEN
2803 l_del_rec.MPS_TRANSACTION_ID := NULL;
2804 END IF;
2805
2806 IF l_del_rec.PO_REF_1 = OKE_API.G_MISS_NUM THEN
2807 l_del_rec.PO_REF_1 := NULL;
2808 END IF;
2809
2810 IF l_del_rec.PO_REF_2 = OKE_API.G_MISS_NUM THEN
2811 l_del_rec.PO_REF_2 := NULL;
2812 END IF;
2813
2814 IF l_del_rec.PO_REF_3 = OKE_API.G_MISS_NUM THEN
2815 l_del_rec.PO_REF_3 := NULL;
2816 END IF;
2817
2818 IF l_del_rec.SHIPPING_REQUEST_ID = OKE_API.G_MISS_NUM THEN
2819 l_del_rec.SHIPPING_REQUEST_ID := NULL;
2820 END IF;
2821
2822 IF l_del_rec.UNIT_NUMBER = OKE_API.G_MISS_CHAR THEN
2823 l_del_rec.UNIT_NUMBER := NULL;
2824 END IF;
2825
2826 IF l_del_rec.NDB_SCHEDULE_DESIGNATOR = OKE_API.G_MISS_CHAR THEN
2827 l_del_rec.NDB_SCHEDULE_DESIGNATOR := NULL;
2828 END IF;
2829
2830 IF l_del_rec.SHIPPABLE_FLAG = OKE_API.G_MISS_CHAR THEN
2831 l_del_rec.SHIPPABLE_FLAG := NULL;
2832 END IF;
2833
2834 IF l_del_rec.CFE_REQ_FLAG = OKE_API.G_MISS_CHAR THEN
2835 l_del_rec.CFE_REQ_FLAG := NULL;
2836 END IF;
2837
2838 IF l_del_rec.INSPECTION_REQ_FLAG = OKE_API.G_MISS_CHAR THEN
2839 l_del_rec.INSPECTION_REQ_FLAG := NULL;
2840 END IF;
2841
2842 IF l_del_rec.INTERIM_RPT_REQ_FLAG = OKE_API.G_MISS_CHAR THEN
2843 l_del_rec.INTERIM_RPT_REQ_FLAG := NULL;
2844 END IF;
2845
2846 IF l_del_rec.LOT_APPLIES_FLAG = OKE_API.G_MISS_CHAR THEN
2847 l_del_rec.LOT_APPLIES_FLAG := NULL;
2848 END IF;
2849
2850 IF l_del_rec.CUSTOMER_APPROVAL_REQ_FLAG = OKE_API.G_MISS_CHAR THEN
2851 l_del_rec.CUSTOMER_APPROVAL_REQ_FLAG := NULL;
2852 END IF;
2853
2854 IF l_del_rec.EXPECTED_SHIPMENT_DATE = OKE_API.G_MISS_DATE THEN
2855 l_del_rec.EXPECTED_SHIPMENT_DATE := NULL;
2856 END IF;
2857
2858 IF l_del_rec.INITIATE_SHIPMENT_DATE = OKE_API.G_MISS_DATE THEN
2859 l_del_rec.INITIATE_SHIPMENT_DATE := NULL;
2860 END IF;
2861
2862 IF l_del_rec.PROMISED_SHIPMENT_DATE = OKE_API.G_MISS_DATE THEN
2863 l_del_rec.PROMISED_SHIPMENT_DATE := NULL;
2864 END IF;
2865
2866 IF l_del_rec.AS_OF_DATE = OKE_API.G_MISS_DATE THEN
2867 l_del_rec.AS_OF_DATE := NULL;
2868 END IF;
2869
2870 IF l_del_rec.DATE_OF_FIRST_SUBMISSION = OKE_API.G_MISS_DATE THEN
2871 l_del_rec.DATE_OF_FIRST_SUBMISSION := NULL;
2872 END IF;
2873
2874 IF l_del_rec.FREQUENCY = OKE_API.G_MISS_CHAR THEN
2875 l_del_rec.FREQUENCY := NULL;
2876 END IF;
2877
2878 IF l_del_rec.ACQ_DOC_NUMBER = OKE_API.G_MISS_CHAR THEN
2879 l_del_rec.ACQ_DOC_NUMBER := NULL;
2880 END IF;
2881
2882 IF l_del_rec.SUBMISSION_FLAG = OKE_API.G_MISS_CHAR THEN
2883 l_del_rec.SUBMISSION_FLAG := NULL;
2884 END IF;
2885
2886 IF l_del_rec.DATA_ITEM_NAME = OKE_API.G_MISS_CHAR THEN
2887 l_del_rec.DATA_ITEM_NAME := NULL;
2888 END IF;
2889
2890 IF l_del_rec.DATA_ITEM_SUBTITLE = OKE_API.G_MISS_CHAR THEN
2891 l_del_rec.DATA_ITEM_SUBTITLE := NULL;
2892 END IF;
2893
2894 IF l_del_rec.TOTAL_NUM_OF_COPIES = OKE_API.G_MISS_NUM THEN
2895 l_del_rec.TOTAL_NUM_OF_COPIES := NULL;
2896 END IF;
2897
2898 IF l_del_rec.CDRL_CATEGORY = OKE_API.G_MISS_CHAR THEN
2899 l_del_rec.CDRL_CATEGORY := NULL;
2900 END IF;
2901
2902 IF l_del_rec.EXPORT_LICENSE_NUM = OKE_API.G_MISS_CHAR THEN
2903 l_del_rec.EXPORT_LICENSE_NUM := NULL;
2904 END IF;
2905
2906 IF l_del_rec.EXPORT_LICENSE_RES = OKE_API.G_MISS_CHAR THEN
2907 l_del_rec.EXPORT_LICENSE_RES := NULL;
2908 END IF;
2909
2910 IF l_del_rec.EXPORT_FLAG = OKE_API.G_MISS_CHAR THEN
2911 l_del_rec.EXPORT_FLAG := NULL;
2912 END IF;
2913
2914 IF l_del_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
2915 l_del_rec.CREATED_BY := NULL;
2916 END IF;
2917
2918 IF l_del_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
2919 l_del_rec.CREATION_DATE := NULL;
2920 END IF;
2921
2922 IF l_del_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
2923 l_del_rec.LAST_UPDATED_BY := NULL;
2924 END IF;
2925
2926 IF l_del_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
2927 l_del_rec.LAST_UPDATE_LOGIN := NULL;
2928 END IF;
2929
2930 IF l_del_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
2931 l_del_rec.LAST_UPDATE_DATE := NULL;
2932 END IF;
2933
2934 IF l_del_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
2935 l_del_rec.ATTRIBUTE_CATEGORY := NULL;
2936 END IF;
2937
2938 IF l_del_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
2939 l_del_rec.ATTRIBUTE1 := NULL;
2940 END IF;
2941
2942 IF l_del_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
2943 l_del_rec.ATTRIBUTE2 := NULL;
2944 END IF;
2945
2946 IF l_del_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
2947 l_del_rec.ATTRIBUTE3 := NULL;
2948 END IF;
2949
2950 IF l_del_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
2951 l_del_rec.ATTRIBUTE4 := NULL;
2952 END IF;
2953
2954 IF l_del_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
2955 l_del_rec.ATTRIBUTE5 := NULL;
2956 END IF;
2957
2958 IF l_del_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
2959 l_del_rec.ATTRIBUTE6 := NULL;
2960 END IF;
2961
2962 IF l_del_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
2963 l_del_rec.ATTRIBUTE7 := NULL;
2964 END IF;
2965
2966 IF l_del_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
2967 l_del_rec.ATTRIBUTE8 := NULL;
2968 END IF;
2969
2970 IF l_del_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
2971 l_del_rec.ATTRIBUTE9 := NULL;
2972 END IF;
2973
2974 IF l_del_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
2975 l_del_rec.ATTRIBUTE10 := NULL;
2976 END IF;
2977
2978 IF l_del_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
2979 l_del_rec.ATTRIBUTE11 := NULL;
2980 END IF;
2981
2982 IF l_del_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
2983 l_del_rec.ATTRIBUTE12 := NULL;
2984 END IF;
2985
2986 IF l_del_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
2987 l_del_rec.ATTRIBUTE13 := NULL;
2988 END IF;
2989
2990 IF l_del_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
2991 l_del_rec.ATTRIBUTE14 := NULL;
2992 END IF;
2993
2994 IF l_del_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
2995 l_del_rec.ATTRIBUTE15 := NULL;
2996 END IF;
2997
2998
2999
3000 IF l_del_rec.comments = OKE_API.G_MISS_CHAR THEN
3001
3002 l_del_rec.comments := NULL;
3003 END IF;
3004
3005 IF l_del_rec.weight = OKE_API.G_MISS_NUM THEN
3006 l_del_rec.weight := NULL;
3007 END IF;
3008
3009 IF l_del_rec.weight_uom_code = OKE_API.G_MISS_CHAR THEN
3010 l_del_rec.weight_uom_code := NULL;
3011 END IF;
3012
3013 IF l_del_rec.volume = OKE_API.G_MISS_NUM THEN
3014 l_del_rec.volume := NULL;
3015 END IF;
3016
3017 IF l_del_rec.volume_uom_code = OKE_API.G_MISS_CHAR THEN
3018 l_del_rec.volume_uom_code := NULL;
3019 END IF;
3020
3021 IF l_del_rec.expenditure_organization_id = OKE_API.G_MISS_NUM THEN
3022 l_del_rec.expenditure_organization_id := NULL;
3023 END IF;
3024
3025 IF l_del_rec.expenditure_type = OKE_API.G_MISS_CHAR THEN
3026 l_del_rec.expenditure_type := NULL;
3027 END IF;
3028
3029 IF l_del_rec.expenditure_item_date = OKE_API.G_MISS_DATE THEN
3030 l_del_rec.expenditure_item_date := NULL;
3031 END IF;
3032
3033 IF l_del_rec.destination_type_code = OKE_API.G_MISS_CHAR THEN
3034 l_del_rec.destination_type_code := NULL;
3035 END IF;
3036
3037 IF l_del_rec.rate_type = OKE_API.G_MISS_CHAR THEN
3038 l_del_rec.rate_type := NULL;
3039 END IF;
3040
3041 IF l_del_rec.rate_date = OKE_API.G_MISS_DATE THEN
3042 l_del_rec.rate_date := NULL;
3043 END IF;
3044
3045 IF l_del_rec.exchange_rate = OKE_API.G_MISS_NUM THEN
3046 l_del_rec.exchange_rate := NULL;
3047 END IF;
3048
3049 IF l_del_rec.description = OKE_API.G_MISS_CHAR THEN
3050 l_del_rec.description := NULL;
3051 END IF;
3052
3053 IF l_del_rec.requisition_line_type_id = OKE_API.G_MISS_NUM THEN
3054 l_del_rec.requisition_line_type_id := NULL;
3055 END IF;
3056
3057 IF l_del_rec.po_category_id = OKE_API.G_MISS_NUM THEN
3058 l_del_rec.po_category_id := NULL;
3059 END IF;
3060
3061 RETURN(l_del_rec);
3062
3063 END null_out_defaults;
3064
3065 -- validate attributes
3066
3067 FUNCTION validate_attributes(
3068 p_del_rec IN del_rec_type ) RETURN VARCHAR2 IS
3069
3070 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3071 x_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3072
3073 BEGIN
3074 /* call individual validation procedure */
3075
3076
3077 Validate_Header_ID(
3078 x_return_status => l_return_status,
3079 p_del_rec => p_del_rec);
3080
3081
3082
3083 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3084 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3085 x_return_status := l_return_status;
3086 End If;
3087 End If;
3088
3089
3090 Validate_Line_ID(
3091 x_return_status => l_return_status,
3092 p_del_rec => p_del_rec);
3093
3094 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3095 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3096 x_return_status := l_return_status;
3097 End If;
3098 End If;
3099
3100 Validate_Deliverable_Number(
3101 x_return_status => l_return_status,
3102 p_del_rec => p_del_rec);
3103
3104 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3105 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3106 x_return_status := l_return_status;
3107 End If;
3108 End If;
3109
3110
3111
3112 Validate_Project_ID(
3113 x_return_status => l_return_status,
3114 p_del_rec => p_del_rec);
3115
3116 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3117 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3118 x_return_status := l_return_status;
3119 End If;
3120 End If;
3121
3122 Validate_Task_ID(
3123 x_return_status => l_return_status,
3124 p_del_rec => p_del_rec);
3125
3126 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3127 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3128 x_return_status := l_return_status;
3129 End If;
3130 End If;
3131
3132 Validate_Inventory_Org_ID(
3133 x_return_status => l_return_status,
3134 p_del_rec => p_del_rec);
3135
3136 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3137 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3138 x_return_status := l_return_status;
3139 End If;
3140 End If;
3141
3142 Validate_Item_ID(
3143 x_return_status => l_return_status,
3144 p_del_rec => p_del_rec);
3145
3146 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3147 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3148 x_return_status := l_return_status;
3149 End If;
3150 End If;
3151
3152 Validate_Delivery_Date(
3153 x_return_status => l_return_status,
3154 p_del_rec => p_del_rec);
3155
3156
3157 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3158 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3159 x_return_status := l_return_status;
3160 End If;
3161 End If;
3162
3163 Validate_Direction(
3164 x_return_status => l_return_status,
3165 p_del_rec => p_del_rec);
3166
3167 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3168 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3169 x_return_status := l_return_status;
3170 End If;
3171 End If;
3172
3173 Validate_Ship_To_Org_ID(
3174 x_return_status => l_return_status,
3175 p_del_rec => p_del_rec);
3176
3177 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3178 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3179 x_return_status := l_return_status;
3180 End If;
3181 End If;
3182
3183 Validate_Ship_From_Org_ID(
3184 x_return_status => l_return_status,
3185 p_del_rec => p_del_rec);
3186
3187 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3188 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3189 x_return_status := l_return_status;
3190 End If;
3191 End If;
3192
3193 Validate_Ship_To_Location_ID(
3194 x_return_status => l_return_status,
3195 p_del_rec => p_del_rec);
3196
3197 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3198 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3199 x_return_status := l_return_status;
3200 End If;
3201 End If;
3202
3203 Validate_Ship_From_Location_ID(
3204 x_return_status => l_return_status,
3205 p_del_rec => p_del_rec);
3206
3207 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3208 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3209 x_return_status := l_return_status;
3210 End If;
3211 End If;
3212
3213 Validate_In_Process_Flag(
3214 x_return_status => l_return_status,
3215 p_del_rec => p_del_rec);
3216
3217 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3218 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3219 x_return_status := l_return_status;
3220 End If;
3221 End If;
3222
3223 Validate_Start_Date(
3224 x_return_status => l_return_status,
3225 p_del_rec => p_del_rec);
3226
3227 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3228 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3229 x_return_status := l_return_status;
3230 End If;
3231 End If;
3232
3233 Validate_End_Date(
3234 x_return_status => l_return_status,
3235 p_del_rec => p_del_rec);
3236
3237 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3238 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3239 x_return_status := l_return_status;
3240 End If;
3241 End If;
3242
3243 Validate_Need_By_Date(
3244 x_return_status => l_return_status,
3245 p_del_rec => p_del_rec);
3246
3247 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3248 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3249 x_return_status := l_return_status;
3250 End If;
3251 End If;
3252
3253 Validate_Currency_Code(
3254 x_return_status => l_return_status,
3255 p_del_rec => p_del_rec);
3256
3257 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3258 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3259 x_return_status := l_return_status;
3260 End If;
3261 End If;
3262
3263 Validate_UOM_Code(
3264 x_return_status => l_return_status,
3265 p_del_rec => p_del_rec);
3266
3267 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3268 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3269 x_return_status := l_return_status;
3270 End If;
3271 End If;
3272
3273 Validate_Shipping_Request_ID(
3274 x_return_status => l_return_status,
3275 p_del_rec => p_del_rec);
3276
3277 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3278 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3279 x_return_status := l_return_status;
3280 End If;
3281 End If;
3282
3283 Validate_Mps_Transaction_ID(
3284 x_return_status => l_return_status,
3285 p_del_rec => p_del_rec);
3286
3287 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3288 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3289 x_return_status := l_return_status;
3290 End If;
3291 End If;
3292
3293 Validate_Unit_Number(
3294 x_return_status => l_return_status,
3295 p_del_rec => p_del_rec);
3296
3297 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3298 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3299 x_return_status := l_return_status;
3300 End If;
3301 End If;
3302
3303 Validate_Plan_Name(
3304 x_return_status => l_return_status,
3305 p_del_rec => p_del_rec);
3306
3307 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3308 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3309 x_return_status := l_return_status;
3310 End If;
3311 End If;
3312
3313 Validate_Volume_UOM_Code(
3314 x_return_status => l_return_status,
3315 p_del_rec => p_del_rec);
3316
3317 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3318 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3319 x_return_status := l_return_status;
3320 End If;
3321 End If;
3322
3323 Validate_Weight_UOM_Code(
3324 x_return_status => l_return_status,
3325 p_del_rec => p_del_rec);
3326
3327 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3328 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3329 x_return_status := l_return_status;
3330 End If;
3331 End If;
3332
3333 Validate_Exp_Organization_ID(
3334 x_return_status => l_return_status,
3335 p_del_rec => p_del_rec);
3336
3337 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3338 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3339 x_return_status := l_return_status;
3340 End If;
3341 End If;
3342
3343 Validate_Destination_Type_Code(
3344 x_return_status => l_return_status,
3345 p_del_rec => p_del_rec);
3346
3347 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3348 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3349 x_return_status := l_return_status;
3350 End If;
3351 End If;
3352
3353 Validate_Exp_Type(
3354 x_return_status => l_return_status,
3355 p_del_rec => p_del_rec);
3356
3357 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3358 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3359 x_return_status := l_return_status;
3360 End If;
3361 End If;
3362
3363 Validate_Rate_Type(
3364 x_return_status => l_return_status,
3365 p_del_rec => p_del_rec);
3366
3367 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3368 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3369 x_return_status := l_return_status;
3370 End If;
3371 End If;
3372
3373 Validate_Flag_Values(
3374 x_return_status => l_return_status,
3375 p_del_rec => p_del_rec);
3376
3377 If l_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3378 If x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3379 x_return_status := l_return_status;
3380 End If;
3381 End If;
3382
3383 return (x_return_status);
3384
3385 exception
3386 when OTHERS then
3387 -- store SQL error message on message stack
3388 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
3389 p_msg_name => g_unexpected_error,
3390 p_token1 => g_sqlcode_token,
3391 p_token1_value => sqlcode,
3392 p_token2 => g_sqlerrm_token,
3393 p_token2_value => sqlerrm);
3394
3395 -- notify caller of an UNEXPETED error
3396 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
3397
3398 -- return status to caller
3399 RETURN(x_return_status);
3400
3401 END Validate_Attributes;
3402
3403 -- validate record
3404
3405 FUNCTION validate_record (
3406 p_del_rec IN del_rec_type
3407 ) RETURN VARCHAR2 IS
3408 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3409 BEGIN
3410
3411 RETURN(l_return_status);
3412
3413 END validate_record;
3414
3415 -- validate row
3416
3417 PROCEDURE validate_row(
3418 p_api_version IN NUMBER,
3419 p_init_msg_list IN VARCHAR2 ,
3420 x_return_status OUT NOCOPY VARCHAR2,
3421 x_msg_count OUT NOCOPY NUMBER,
3422 x_msg_data OUT NOCOPY VARCHAR2,
3423 p_del_rec IN del_rec_type) IS
3424
3425 l_api_version CONSTANT NUMBER := 1;
3426 l_api_name CONSTANT VARCHAR2(30) := 'B_validate_row';
3427 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3428 l_del_rec del_rec_type := p_del_rec;
3429
3430 BEGIN
3431 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
3432 G_PKG_NAME,
3433 p_init_msg_list,
3434 l_api_version,
3435 p_api_version,
3436 '_PVT',
3437 x_return_status);
3438 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3439 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3440 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3441
3442 RAISE OKE_API.G_EXCEPTION_ERROR;
3443 END IF;
3444 --- Validate all non-missing attributes (Item Level Validation)
3445 l_return_status := Validate_Attributes(l_del_rec);
3446 --- If any errors happen abort API
3447 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3448 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3449 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3450 RAISE OKE_API.G_EXCEPTION_ERROR;
3451 END IF;
3452 l_return_status := Validate_Record(l_del_rec);
3453
3454 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3455 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3456 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3457 RAISE OKE_API.G_EXCEPTION_ERROR;
3458 END IF;
3459 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
3460 EXCEPTION
3461 WHEN OKE_API.G_EXCEPTION_ERROR THEN
3462 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3463 (
3464 l_api_name,
3465 G_PKG_NAME,
3466 'OKE_API.G_RET_STS_ERROR',
3467 x_msg_count,
3468 x_msg_data,
3469 '_PVT'
3470 );
3471 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3472 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
3473 (
3474 l_api_name,
3475 G_PKG_NAME,
3476 'OKE_API.G_RET_STS_UNEXP_ERROR',
3477 x_msg_count,
3478 x_msg_data,
3479 '_PVT'
3480 );
3481 WHEN OTHERS THEN
3482 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
3483 (
3484 l_api_name,
3485 G_PKG_NAME,
3486 'OTHERS',
3487 x_msg_count,
3488 x_msg_data,
3489 '_PVT'
3490 );
3491 END validate_row;
3492
3493 PROCEDURE validate_row(
3494 p_api_version IN NUMBER,
3495 p_init_msg_list IN VARCHAR2 ,
3496 x_return_status OUT NOCOPY VARCHAR2,
3497 x_msg_count OUT NOCOPY NUMBER,
3498 x_msg_data OUT NOCOPY VARCHAR2,
3499 p_del_tbl IN del_tbl_type) IS
3500
3501 l_api_version CONSTANT NUMBER := 1;
3502 l_api_name CONSTANT VARCHAR2(30) := 'TBL_validate_row';
3503 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3504 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3505 i NUMBER := 0;
3506 BEGIN
3507 OKE_API.init_msg_list(p_init_msg_list);
3508 -- Make sure PL/SQL table has records in it before passing
3509 IF (p_del_tbl.COUNT > 0) THEN
3510 i := p_del_tbl.FIRST;
3511 LOOP
3512 validate_row (
3513 p_api_version => p_api_version,
3514 p_init_msg_list => G_FALSE,
3515 x_return_status => x_return_status,
3516 x_msg_count => x_msg_count,
3517 x_msg_data => x_msg_data,
3518 p_del_rec => p_del_tbl(i));
3519
3520 -- store the highest degree of error
3521 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3522 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3523 l_overall_status := x_return_status;
3524 End If;
3525 End If;
3526
3527 EXIT WHEN (i = p_del_tbl.LAST);
3528 i := p_del_tbl.NEXT(i);
3529 END LOOP;
3530 -- return overall status
3531 x_return_status := l_overall_status;
3532 END IF;
3533
3534 EXCEPTION
3535 WHEN OKE_API.G_EXCEPTION_ERROR THEN
3536 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3537 (
3538 l_api_name,
3539 G_PKG_NAME,
3540 'OKE_API.G_RET_STS_ERROR',
3541 x_msg_count,
3542 x_msg_data,
3543 '_PVT'
3544 );
3545 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3546 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
3547 (
3548 l_api_name,
3549 G_PKG_NAME,
3550 'OKE_API.G_RET_STS_UNEXP_ERROR',
3551 x_msg_count,
3552 x_msg_data,
3553 '_PVT'
3554 );
3555 WHEN OTHERS THEN
3556 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
3557 (
3558 l_api_name,
3559 G_PKG_NAME,
3560 'OTHERS',
3561 x_msg_count,
3562 x_msg_data,
3563 '_PVT'
3564 );
3565 END validate_row;
3566
3567 -- insert data into oke_k_deliverables_b/tl
3568
3569 PROCEDURE insert_row(
3570 p_api_version IN NUMBER,
3571 p_init_msg_list IN VARCHAR2 ,
3572 x_return_status OUT NOCOPY VARCHAR2,
3573 x_msg_count OUT NOCOPY NUMBER,
3574 x_msg_data OUT NOCOPY VARCHAR2,
3575 p_del_rec IN del_rec_type,
3576 x_del_rec OUT NOCOPY del_rec_type) IS
3577
3578 l_api_version CONSTANT NUMBER := 1;
3579 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
3580 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3581 l_del_rec del_rec_type;
3582 l_def_del_rec del_rec_type;
3583 lx_del_rec del_rec_type;
3584
3585 -- FUNCTION fill_who_columns --
3586 -------------------------------
3587 FUNCTION fill_who_columns (
3588 p_del_rec IN del_rec_type
3589 ) RETURN del_rec_type IS
3590
3591 l_del_rec del_rec_type := p_del_rec;
3592
3593 BEGIN
3594
3595 l_del_rec.CREATION_DATE := SYSDATE;
3596 l_del_rec.CREATED_BY := FND_GLOBAL.USER_ID;
3597 l_del_rec.LAST_UPDATE_DATE := SYSDATE;
3598 l_del_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3599 l_del_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3600 RETURN(l_del_rec);
3601
3602 END fill_who_columns;
3603
3604 -- Set_Attributes for:OKE_K_DELIVERABLES_B
3605
3606 FUNCTION Set_Attributes (
3607 p_del_rec IN del_rec_type,
3608 x_del_rec OUT NOCOPY del_rec_type
3609 ) RETURN VARCHAR2 IS
3610 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3611 cursor l_csr is
3612 select oke_k_deliverables_s.nextval from dual;
3613
3614 BEGIN
3615
3616 x_del_rec := p_del_rec;
3617
3618 -- get id
3619 open l_csr;
3620 fetch l_csr into x_del_rec.deliverable_id;
3621 close l_csr;
3622
3623 x_del_rec.BILLABLE_FLAG := UPPER(x_del_rec.BILLABLE_FLAG);
3624 x_del_rec.SHIPPABLE_FLAG := UPPER(x_del_rec.SHIPPABLE_FLAG);
3625 x_del_rec.SUBCONTRACTED_FLAG := UPPER(x_del_rec.SUBCONTRACTED_FLAG);
3626
3627 x_del_rec.COMPLETED_FLAG := UPPER(x_del_rec.COMPLETED_FLAG);
3628
3629 x_del_rec.DROP_SHIPPED_FLAG := UPPER(x_del_rec.DROP_SHIPPED_FLAG);
3630
3631 x_del_rec.CUSTOMER_APPROVAL_REQ_FLAG := UPPER(x_del_rec.CUSTOMER_APPROVAL_REQ_FLAG);
3632
3633 x_del_rec.INSPECTION_REQ_FLAG := UPPER(x_del_rec.INSPECTION_REQ_FLAG);
3634
3635 x_del_rec.INTERIM_RPT_REQ_FLAG := UPPER(x_del_rec.INTERIM_RPT_REQ_FLAG);
3636
3637 x_del_rec.EXPORT_FLAG := UPPER(x_del_rec.EXPORT_FLAG);
3638
3639 x_del_rec.CFE_REQ_FLAG := UPPER(x_del_rec.CFE_REQ_FLAG);
3640
3641 x_del_rec.DEFAULTED_FLAG := UPPER(x_del_rec.DEFAULTED_FLAG);
3642
3643 x_del_rec.IN_PROCESS_FLAG := UPPER(x_del_rec.IN_PROCESS_FLAG);
3644
3645 RETURN(l_return_status);
3646
3647 END Set_Attributes;
3648
3649 BEGIN
3650
3651
3652 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
3653 G_PKG_NAME,
3654 p_init_msg_list,
3655 l_api_version,
3656 p_api_version,
3657 '_PVT',
3658 x_return_status);
3659
3660 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3661 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3662 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3663 RAISE OKE_API.G_EXCEPTION_ERROR;
3664 END IF;
3665
3666
3667 l_del_rec := null_out_defaults(p_del_rec);
3668
3669
3670
3671 --- Setting item attributes
3672 l_return_status := Set_Attributes(
3673 l_del_rec, -- IN
3674 l_def_del_rec); -- OUT
3675
3676 --- If any errors happen abort API
3677 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3678 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3679 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3680 RAISE OKE_API.G_EXCEPTION_ERROR;
3681 END IF;
3682
3683
3684
3685 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3686 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3687 END IF;
3688
3689 l_def_del_rec := fill_who_columns(l_def_del_rec);
3690
3691
3692
3693 /* --- Validate all non-missing attributes (Item Level Validation)
3694 l_return_status := Validate_Attributes(l_def_del_rec);
3695
3696 --- If any errors happen abort API
3697 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3698 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3699
3700 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3701 RAISE OKE_API.G_EXCEPTION_ERROR;
3702 END IF;
3703
3704
3705
3706 l_return_status := Validate_Record(l_def_del_rec);
3707
3708 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
3709 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3710 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
3711 RAISE OKE_API.G_EXCEPTION_ERROR;
3712 END IF; */
3713
3714 -- get deliverable number
3715
3716 if l_def_del_rec.deliverable_num is null then
3717
3718 l_def_del_rec.deliverable_num := OKE_NUMBER_SEQUENCES_PKG.Next_Deliverable_Number(
3719 l_def_del_rec.k_header_id
3720 , l_def_del_rec.k_line_id);
3721
3722 end if;
3723
3724
3725 INSERT INTO OKE_K_DELIVERABLES_B(
3726 deliverable_id,
3727 deliverable_num,
3728 project_id,
3729 task_id,
3730 item_id,
3731 k_header_id,
3732 k_line_id,
3733 delivery_date,
3734 status_code,
3735 status_date,
3736 parent_deliverable_id,
3737 ship_to_org_id,
3738 ship_to_location_id,
3739 ship_from_org_id,
3740 ship_from_location_id,
3741 inventory_org_id,
3742 direction,
3743 defaulted_flag,
3744 in_process_flag,
3745 wf_item_key,
3746 sub_ref_id,
3747 start_date,
3748 end_date,
3749 priority_code,
3750 currency_code,
3751 unit_price,
3752 uom_code,
3753 quantity,
3754 country_of_origin_code,
3755 subcontracted_flag,
3756 dependency_flag,
3757 billable_flag,
3758 billing_event_id,
3759 drop_shipped_flag,
3760 completed_flag,
3761 available_for_ship_flag,
3762 create_demand,
3763 ready_to_bill,
3764 need_by_date,
3765 ready_to_procure,
3766 mps_transaction_id,
3767 po_ref_1,
3768 po_ref_2,
3769 po_ref_3,
3770 shipping_request_id,
3771 unit_number,
3772 ndb_schedule_designator,
3773 shippable_flag,
3774 cfe_req_flag,
3775 inspection_req_flag,
3776 interim_rpt_req_flag,
3777 lot_applies_flag,
3778 customer_approval_req_flag,
3779 expected_shipment_date,
3780 initiate_shipment_date,
3781 promised_shipment_date,
3782 as_of_date,
3783 date_of_first_submission,
3784 frequency,
3785 acq_doc_number,
3786 submission_flag,
3787 data_item_subtitle,
3788 total_num_of_copies,
3789 cdrl_category,
3790 data_item_name,
3791 export_flag,
3792 export_license_num,
3793 export_license_res,
3794 created_by,
3795 creation_date,
3796 last_updated_by,
3797 last_update_login,
3798 last_update_date,
3799 attribute_category,
3800 attribute1,
3801 attribute2,
3802 attribute3,
3803 attribute4,
3804 attribute5,
3805 attribute6,
3806 attribute7,
3807 attribute8,
3808 attribute9,
3809 attribute10,
3810 attribute11,
3811 attribute12,
3812 attribute13,
3813 attribute14,
3814 attribute15,
3815 weight,
3816 weight_uom_code,
3817 volume,
3818 volume_uom_code,
3819 expenditure_organization_id,
3820 expenditure_type,
3821 expenditure_item_date,
3822 destination_type_code,
3823 rate_type,
3824 rate_date,
3825 exchange_rate,
3826 requisition_line_type_id,
3827 po_category_id)
3828 VALUES(
3829 l_def_del_rec.deliverable_id,
3830 l_def_del_rec.deliverable_num,
3831 l_def_del_rec.project_id,
3832 l_def_del_rec.task_id,
3833 l_def_del_rec.item_id,
3834 l_def_del_rec.k_header_id,
3835 l_def_del_rec.k_line_id,
3836 l_def_del_rec.delivery_date,
3837 l_def_del_rec.status_code,
3838 sysdate,
3839 l_def_del_rec.parent_deliverable_id,
3840 l_def_del_rec.ship_to_org_id,
3841 l_def_del_rec.ship_to_location_id,
3842 l_def_del_rec.ship_from_org_id,
3843 l_def_del_rec.ship_from_location_id,
3844 l_def_del_rec.inventory_org_id,
3845 l_def_del_rec.direction,
3846 l_def_del_rec.defaulted_flag,
3847 l_def_del_rec.in_process_flag,
3848 l_def_del_rec.wf_item_key,
3849 l_def_del_rec.sub_ref_id,
3850 l_def_del_rec.start_date,
3851 l_def_del_rec.end_date,
3852 l_def_del_rec.priority_code,
3853 l_def_del_rec.currency_code,
3854 l_def_del_rec.unit_price,
3855 l_def_del_rec.uom_code,
3856 l_def_del_rec.quantity,
3857 l_def_del_rec.country_of_origin_code,
3858 l_def_del_rec.subcontracted_flag,
3859 l_def_del_rec.dependency_flag,
3860 l_def_del_rec.billable_flag,
3861 l_def_del_rec.billing_event_id,
3862 l_def_del_rec.drop_shipped_flag,
3863 l_def_del_rec.completed_flag,
3864 l_def_del_rec.available_for_ship_flag,
3865 l_def_del_rec.create_demand,
3866 l_def_del_rec.ready_to_bill,
3867 l_def_del_rec.need_by_date,
3868 l_def_del_rec.ready_to_procure,
3869 l_def_del_rec.mps_transaction_id,
3870 l_def_del_rec.po_ref_1,
3871 l_def_del_rec.po_ref_2,
3872 l_def_del_rec.po_ref_3,
3873 l_def_del_rec.shipping_request_id,
3874 l_def_del_rec.unit_number,
3875 l_def_del_rec.ndb_schedule_designator,
3876 l_def_del_rec.shippable_flag,
3877 l_def_del_rec.cfe_req_flag,
3878 l_def_del_rec.inspection_req_flag,
3879 l_def_del_rec.interim_rpt_req_flag,
3880 l_def_del_rec.lot_applies_flag,
3881 l_def_del_rec.customer_approval_req_flag,
3882 l_def_del_rec.expected_shipment_date,
3883 l_def_del_rec.initiate_shipment_date,
3884 l_def_del_rec.promised_shipment_date,
3885 l_def_del_rec.as_of_date,
3886 l_def_del_rec.date_of_first_submission,
3887 l_def_del_rec.frequency,
3888 l_def_del_rec.acq_doc_number,
3889 l_def_del_rec.submission_flag,
3890 l_def_del_rec.data_item_subtitle,
3891 l_def_del_rec.total_num_of_copies,
3892 l_def_del_rec.cdrl_category,
3893 l_def_del_rec.data_item_name,
3894 l_def_del_rec.export_flag,
3895 l_def_del_rec.export_license_num,
3896 l_def_del_rec.export_license_res,
3897 l_def_del_rec.created_by,
3898 l_def_del_rec.creation_date,
3899 l_def_del_rec.last_updated_by,
3900 l_def_del_rec.last_update_login,
3901 l_def_del_rec.last_update_date,
3902 l_def_del_rec.attribute_category,
3903 l_def_del_rec.attribute1,
3904 l_def_del_rec.attribute2,
3905 l_def_del_rec.attribute3,
3906 l_def_del_rec.attribute4,
3907 l_def_del_rec.attribute5,
3908 l_def_del_rec.attribute6,
3909 l_def_del_rec.attribute7,
3910 l_def_del_rec.attribute8,
3911 l_def_del_rec.attribute9,
3912 l_def_del_rec.attribute10,
3913 l_def_del_rec.attribute11,
3914 l_def_del_rec.attribute12,
3915 l_def_del_rec.attribute13,
3916 l_def_del_rec.attribute14,
3917 l_def_del_rec.attribute15,
3918 l_def_del_rec.weight,
3919 l_def_del_rec.weight_uom_code,
3920 l_def_del_rec.volume,
3921 l_def_del_rec.volume_uom_code,
3922 l_def_del_rec.expenditure_organization_id,
3923 l_def_del_rec.expenditure_type,
3924 l_def_del_rec.expenditure_item_date,
3925 l_def_del_rec.destination_type_code,
3926 l_def_del_rec.rate_type,
3927 l_def_del_rec.rate_date,
3928 l_def_del_rec.exchange_rate,
3929 l_def_del_rec.requisition_line_type_id,
3930 l_def_del_rec.po_category_id);
3931
3932 -- insert into TL table
3933
3934 insert into OKE_K_DELIVERABLES_TL(
3935 deliverable_id,
3936 language,
3937 creation_date,
3938 created_by,
3939 last_updated_by,
3940 last_update_login,
3941 last_update_date,
3942 k_header_id,
3943 k_line_id,
3944 source_lang,
3945 sfwt_flag,
3946 description,
3947 comments)
3948 select
3949 l_def_del_rec.deliverable_id,
3950 l.language_code,
3951 l_def_del_rec.creation_date,
3952 l_def_del_rec.created_by,
3953 l_def_del_rec.last_updated_by,
3954 l_def_del_rec.last_update_login,
3955 l_def_del_rec.last_update_date,
3956 l_def_del_rec.k_header_id,
3957 l_def_del_rec.k_line_id,
3958 okc_util.get_userenv_lang,
3959 'NO',
3960 l_def_del_rec.description,
3961 l_def_del_rec.comments
3962 from FND_LANGUAGES L
3963 where L.INSTALLED_FLAG in ('I', 'B')
3964 and not exists
3965 (select NULL
3966 from OKE_K_DELIVERABLES_TL T
3967 where T.DELIVERABLE_ID = l_def_del_rec.deliverable_id
3968 and T.LANGUAGE = L.LANGUAGE_CODE);
3969
3970
3971
3972 -- Set OUT values
3973 x_del_rec := l_def_del_rec;
3974 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
3975
3976
3977 EXCEPTION
3978 WHEN OKE_API.G_EXCEPTION_ERROR THEN
3979 x_return_status := OKE_API.HANDLE_EXCEPTIONS
3980 (
3981 l_api_name,
3982 G_PKG_NAME,
3983 'OKE_API.G_RET_STS_ERROR',
3984 x_msg_count,
3985 x_msg_data,
3986 '_PVT'
3987 );
3988 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3989 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
3990 (
3991 l_api_name,
3992 G_PKG_NAME,
3993 'OKE_API.G_RET_STS_UNEXP_ERROR',
3994 x_msg_count,
3995 x_msg_data,
3996 '_PVT'
3997 );
3998 WHEN OTHERS THEN
3999 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
4000 (
4001 l_api_name,
4002 G_PKG_NAME,
4003 'OTHERS',
4004 x_msg_count,
4005 x_msg_data,
4006 '_PVT'
4007 );
4008 END insert_row;
4009
4010 PROCEDURE insert_row(
4011 p_api_version IN NUMBER,
4012 p_init_msg_list IN VARCHAR2 ,
4013 x_return_status OUT NOCOPY VARCHAR2,
4014 x_msg_count OUT NOCOPY NUMBER,
4015 x_msg_data OUT NOCOPY VARCHAR2,
4016 p_del_tbl IN del_tbl_type,
4017 x_del_tbl OUT NOCOPY del_tbl_type) IS
4018
4019 l_api_version CONSTANT NUMBER := 1;
4020 l_api_name CONSTANT VARCHAR2(30) := 'TBL_insert_row';
4021 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4022 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4023 i NUMBER := 0;
4024 BEGIN
4025
4026 OKE_API.init_msg_list(p_init_msg_list);
4027 -- Make sure PL/SQL table has records in it before passing
4028 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4029 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'called pvt insert_row');
4030 END IF;
4031 IF (p_del_tbl.COUNT > 0) THEN
4032 i := p_del_tbl.FIRST;
4033 LOOP
4034 insert_row (
4035 p_api_version => p_api_version,
4036 p_init_msg_list => G_FALSE,
4037 x_return_status => x_return_status,
4038 x_msg_count => x_msg_count,
4039 x_msg_data => x_msg_data,
4040
4041 p_del_rec => p_del_tbl(i),
4042 x_del_rec => x_del_tbl(i));
4043
4044 -- store the highest degree of error
4045 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
4046 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
4047 l_overall_status := x_return_status;
4048 End If;
4049 End If;
4050
4051 EXIT WHEN (i = p_del_tbl.LAST);
4052
4053 i := p_del_tbl.NEXT(i);
4054 END LOOP;
4055 -- return overall status
4056 x_return_status := l_overall_status;
4057 END IF;
4058
4059 EXCEPTION
4060 WHEN OKE_API.G_EXCEPTION_ERROR THEN
4061 x_return_status := OKE_API.HANDLE_EXCEPTIONS
4062 (
4063 l_api_name,
4064 G_PKG_NAME,
4065 'OKE_API.G_RET_STS_ERROR',
4066 x_msg_count,
4067 x_msg_data,
4068 '_PVT'
4069 );
4070 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4071 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
4072 (
4073 l_api_name,
4074 G_PKG_NAME,
4075 'OKE_API.G_RET_STS_UNEXP_ERROR',
4076 x_msg_count,
4077 x_msg_data,
4078 '_PVT'
4079 );
4080 WHEN OTHERS THEN
4081 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
4082 (
4083 l_api_name,
4084 G_PKG_NAME,
4085 'OTHERS',
4086 x_msg_count,
4087 x_msg_data,
4088 '_PVT'
4089 );
4090 END insert_row;
4091
4092 -- update oke_k_lines
4093
4094 PROCEDURE update_row(
4095 p_api_version IN NUMBER,
4096 p_init_msg_list IN VARCHAR2 ,
4097 x_return_status OUT NOCOPY VARCHAR2,
4098 x_msg_count OUT NOCOPY NUMBER,
4099 x_msg_data OUT NOCOPY VARCHAR2,
4100 p_del_rec IN del_rec_type,
4101 x_del_rec OUT NOCOPY del_rec_type) IS
4102
4103 l_api_version CONSTANT NUMBER := 1.0;
4104 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
4105 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4106 l_del_rec del_rec_type := p_del_rec;
4107 l_def_del_rec del_rec_type;
4108 lx_del_rec del_rec_type;
4109
4110 -------------------------------
4111 -- FUNCTION fill_who_columns --
4112 -------------------------------
4113 FUNCTION fill_who_columns (
4114 p_del_rec IN del_rec_type
4115 ) RETURN del_rec_type IS
4116
4117 l_del_rec del_rec_type := p_del_rec;
4118
4119 BEGIN
4120 l_del_rec.LAST_UPDATE_DATE := SYSDATE;
4121 l_del_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
4122 l_del_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
4123 RETURN(l_del_rec);
4124 END fill_who_columns;
4125
4126 ----------------------------------
4127 -- FUNCTION populate_new_record --
4128 ----------------------------------
4129 FUNCTION populate_new_record (
4130 p_del_rec IN del_rec_type,
4131 x_del_rec OUT NOCOPY del_rec_type
4132 ) RETURN VARCHAR2 IS
4133
4134 l_del_rec del_rec_type;
4135 l_row_notfound BOOLEAN := TRUE;
4136 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4137
4138 BEGIN
4139
4140
4141 x_del_rec := p_del_rec;
4142
4143
4144 -- Get current database values
4145 l_del_rec := get_rec(p_del_rec, l_row_notfound);
4146
4147 IF (l_row_notfound) THEN
4148 l_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
4149 END IF;
4150
4151
4152 IF x_del_rec.DELIVERABLE_NUM = OKE_API.G_MISS_CHAR THEN
4153 x_del_rec.DELIVERABLE_NUM := l_del_rec.DELIVERABLE_NUM;
4154 END IF;
4155
4156 IF x_del_rec.PROJECT_ID = OKE_API.G_MISS_NUM THEN
4157 x_del_rec.PROJECT_ID := l_del_rec.PROJECT_ID;
4158 END IF;
4159
4160 IF x_del_rec.TASK_ID = OKE_API.G_MISS_NUM THEN
4161 x_del_rec.TASK_ID := l_del_rec.TASK_ID;
4162 END IF;
4163
4164 IF x_del_rec.ITEM_ID = OKE_API.G_MISS_NUM THEN
4165 x_del_rec.ITEM_ID := l_del_rec.ITEM_ID;
4166 END IF;
4167
4168 IF x_del_rec.K_HEADER_ID = OKE_API.G_MISS_NUM THEN
4169 x_del_rec.K_HEADER_ID := l_del_rec.K_HEADER_ID;
4170 END IF;
4171
4172 IF x_del_rec.K_LINE_ID = OKE_API.G_MISS_NUM THEN
4173 x_del_rec.K_LINE_ID := l_del_rec.K_LINE_ID;
4174 END IF;
4175
4176 IF x_del_rec.DELIVERY_DATE = OKE_API.G_MISS_DATE THEN
4177 x_del_rec.DELIVERY_DATE := l_del_rec.DELIVERY_DATE;
4178 END IF;
4179
4180 IF x_del_rec.STATUS_CODE = OKE_API.G_MISS_CHAR THEN
4181 x_del_rec.STATUS_CODE := l_del_rec.STATUS_CODE;
4182 END IF;
4183
4184 IF x_del_rec.PARENT_DELIVERABLE_ID = OKE_API.G_MISS_NUM THEN
4185 x_del_rec.PARENT_DELIVERABLE_ID := l_del_rec.PARENT_DELIVERABLE_ID;
4186 END IF;
4187
4188 IF x_del_rec.SHIP_TO_ORG_ID = OKE_API.G_MISS_NUM THEN
4189 x_del_rec.SHIP_TO_ORG_ID := l_del_rec.SHIP_TO_ORG_ID;
4190 END IF;
4191
4192 IF x_del_rec.SHIP_TO_LOCATION_ID = OKE_API.G_MISS_NUM THEN
4193 x_del_rec.SHIP_TO_LOCATION_ID := l_del_rec.SHIP_TO_LOCATION_ID;
4194 END IF;
4195
4196 IF x_del_rec.SHIP_FROM_ORG_ID = OKE_API.G_MISS_NUM THEN
4197 x_del_rec.SHIP_FROM_ORG_ID := l_del_rec.SHIP_FROM_ORG_ID;
4198 END IF;
4199
4200 IF x_del_rec.SHIP_FROM_LOCATION_ID = OKE_API.G_MISS_NUM THEN
4201 x_del_rec.SHIP_FROM_LOCATION_ID := l_del_rec.SHIP_FROM_LOCATION_ID;
4202 END IF;
4203
4204 IF x_del_rec.INVENTORY_ORG_ID = OKE_API.G_MISS_NUM THEN
4205 x_del_rec.INVENTORY_ORG_ID := l_del_rec.INVENTORY_ORG_ID;
4206 END IF;
4207
4208 IF x_del_rec.DIRECTION = OKE_API.G_MISS_CHAR THEN
4209 x_del_rec.DIRECTION := l_del_rec.DIRECTION;
4210 END IF;
4211
4212 IF x_del_rec.DEFAULTED_FLAG = OKE_API.G_MISS_CHAR THEN
4213 x_del_rec.DEFAULTED_FLAG := l_del_rec.DEFAULTED_FLAG;
4214 END IF;
4215
4216 IF x_del_rec.IN_PROCESS_FLAG = OKE_API.G_MISS_CHAR THEN
4217 x_del_rec.IN_PROCESS_FLAG := l_del_rec.IN_PROCESS_FLAG;
4218 END IF;
4219
4220 IF x_del_rec.WF_ITEM_KEY = OKE_API.G_MISS_CHAR THEN
4221 x_del_rec.WF_ITEM_KEY := l_del_rec.WF_ITEM_KEY;
4222 END IF;
4223
4224 IF x_del_rec.SUB_REF_ID = OKE_API.G_MISS_NUM THEN
4225 x_del_rec.SUB_REF_ID := l_del_rec.SUB_REF_ID;
4226 END IF;
4227
4228 IF x_del_rec.START_DATE = OKE_API.G_MISS_DATE THEN
4229 x_del_rec.START_DATE := l_del_rec.START_DATE;
4230 END IF;
4231
4232 IF x_del_rec.END_DATE = OKE_API.G_MISS_DATE THEN
4233 x_del_rec.END_DATE := l_del_rec.END_DATE;
4234 END IF;
4235
4236 IF x_del_rec.PRIORITY_CODE = OKE_API.G_MISS_CHAR THEN
4237 x_del_rec.PRIORITY_CODE := l_del_rec.PRIORITY_CODE;
4238 END IF;
4239
4240 IF x_del_rec.CURRENCY_CODE = OKE_API.G_MISS_CHAR THEN
4241 x_del_rec.CURRENCY_CODE := l_del_rec.CURRENCY_CODE;
4242 END IF;
4243
4244 IF x_del_rec.UNIT_PRICE = OKE_API.G_MISS_NUM THEN
4245 x_del_rec.UNIT_PRICE := l_del_rec.UNIT_PRICE;
4246 END IF;
4247
4248 IF x_del_rec.UOM_CODE = OKE_API.G_MISS_CHAR THEN
4249 x_del_rec.UOM_CODE := l_del_rec.UOM_CODE;
4250 END IF;
4251
4252 IF x_del_rec.QUANTITY = OKE_API.G_MISS_NUM THEN
4253 x_del_rec.QUANTITY := l_del_rec.QUANTITY;
4254 END IF;
4255
4256 IF x_del_rec.COUNTRY_OF_ORIGIN_CODE = OKE_API.G_MISS_CHAR THEN
4257 x_del_rec.COUNTRY_OF_ORIGIN_CODE := l_del_rec.COUNTRY_OF_ORIGIN_CODE;
4258 END IF;
4259
4260 IF x_del_rec.SUBCONTRACTED_FLAG = OKE_API.G_MISS_CHAR THEN
4261 x_del_rec.SUBCONTRACTED_FLAG := l_del_rec.SUBCONTRACTED_FLAG;
4262 END IF;
4263
4264 IF x_del_rec.DEPENDENCY_FLAG = OKE_API.G_MISS_CHAR THEN
4265 x_del_rec.DEPENDENCY_FLAG := l_del_rec.DEPENDENCY_FLAG;
4266 END IF;
4267
4268
4269
4270 IF x_del_rec.BILLABLE_FLAG = OKE_API.G_MISS_CHAR THEN
4271 x_del_rec.BILLABLE_FLAG := l_del_rec.BILLABLE_FLAG;
4272 END IF;
4273
4274 IF x_del_rec.BILLING_EVENT_ID = OKE_API.G_MISS_NUM THEN
4275 x_del_rec.BILLING_EVENT_ID := l_del_rec.BILLING_EVENT_ID;
4276 END IF;
4277
4278 IF x_del_rec.DROP_SHIPPED_FLAG = OKE_API.G_MISS_CHAR THEN
4279 x_del_rec.DROP_SHIPPED_FLAG := l_del_rec.DROP_SHIPPED_FLAG;
4280 END IF;
4281
4282 IF x_del_rec.COMPLETED_FLAG = OKE_API.G_MISS_CHAR THEN
4283 x_del_rec.COMPLETED_FLAG := l_del_rec.COMPLETED_FLAG;
4284 END IF;
4285
4286 IF x_del_rec.AVAILABLE_FOR_SHIP_FLAG = OKE_API.G_MISS_CHAR THEN
4287 x_del_rec.AVAILABLE_FOR_SHIP_FLAG := l_del_rec.AVAILABLE_FOR_SHIP_FLAG;
4288 END IF;
4289
4290 IF x_del_rec.CREATE_DEMAND = OKE_API.G_MISS_CHAR THEN
4291 x_del_rec.CREATE_DEMAND := l_del_rec.CREATE_DEMAND;
4292 END IF;
4293
4294 IF x_del_rec.READY_TO_BILL = OKE_API.G_MISS_CHAR THEN
4295 x_del_rec.READY_TO_BILL := l_del_rec.READY_TO_BILL;
4296 END IF;
4297
4298 IF x_del_rec.NEED_BY_DATE = OKE_API.G_MISS_DATE THEN
4299 x_del_rec.NEED_BY_DATE := l_del_rec.NEED_BY_DATE;
4300 END IF;
4301
4302 IF x_del_rec.READY_TO_PROCURE = OKE_API.G_MISS_CHAR THEN
4303 x_del_rec.READY_TO_PROCURE := l_del_rec.READY_TO_PROCURE;
4304 END IF;
4305
4306 IF x_del_rec.MPS_TRANSACTION_ID = OKE_API.G_MISS_NUM THEN
4307 x_del_rec.MPS_TRANSACTION_ID := l_del_rec.MPS_TRANSACTION_ID;
4308 END IF;
4309
4310 IF x_del_rec.PO_REF_1 = OKE_API.G_MISS_NUM THEN
4311 x_del_rec.PO_REF_1 := l_del_rec.PO_REF_1;
4312 END IF;
4313
4314 IF x_del_rec.PO_REF_2 = OKE_API.G_MISS_NUM THEN
4315 x_del_rec.PO_REF_2 := l_del_rec.PO_REF_2;
4316 END IF;
4317
4318 IF x_del_rec.PO_REF_3 = OKE_API.G_MISS_NUM THEN
4319 x_del_rec.PO_REF_3 := l_del_rec.PO_REF_3;
4320 END IF;
4321
4322 IF x_del_rec.SHIPPING_REQUEST_ID = OKE_API.G_MISS_NUM THEN
4323 x_del_rec.SHIPPING_REQUEST_ID := l_del_rec.SHIPPING_REQUEST_ID;
4324 END IF;
4325
4326 IF x_del_rec.UNIT_NUMBER = OKE_API.G_MISS_CHAR THEN
4327 x_del_rec.UNIT_NUMBER := l_del_rec.UNIT_NUMBER;
4328 END IF;
4329
4330 IF x_del_rec.NDB_SCHEDULE_DESIGNATOR = OKE_API.G_MISS_CHAR THEN
4331 x_del_rec.NDB_SCHEDULE_DESIGNATOR := l_del_rec.NDB_SCHEDULE_DESIGNATOR;
4332 END IF;
4333
4334 IF x_del_rec.SHIPPABLE_FLAG = OKE_API.G_MISS_CHAR THEN
4335 x_del_rec.SHIPPABLE_FLAG := l_del_rec.SHIPPABLE_FLAG;
4336 END IF;
4337
4338 IF x_del_rec.CFE_REQ_FLAG = OKE_API.G_MISS_CHAR THEN
4339 x_del_rec.CFE_REQ_FLAG := l_del_rec.CFE_REQ_FLAG;
4340 END IF;
4341
4342 IF x_del_rec.INSPECTION_REQ_FLAG = OKE_API.G_MISS_CHAR THEN
4343 x_del_rec.INSPECTION_REQ_FLAG := l_del_rec.INSPECTION_REQ_FLAG;
4344 END IF;
4345
4346 IF x_del_rec.INTERIM_RPT_REQ_FLAG = OKE_API.G_MISS_CHAR THEN
4347 x_del_rec.INTERIM_RPT_REQ_FLAG := l_del_rec.INTERIM_RPT_REQ_FLAG;
4348 END IF;
4349
4350 IF x_del_rec.LOT_APPLIES_FLAG = OKE_API.G_MISS_CHAR THEN
4351 x_del_rec.LOT_APPLIES_FLAG := l_del_rec.LOT_APPLIES_FLAG;
4352 END IF;
4353
4354 IF x_del_rec.CUSTOMER_APPROVAL_REQ_FLAG = OKE_API.G_MISS_CHAR THEN
4355 x_del_rec.CUSTOMER_APPROVAL_REQ_FLAG := l_del_rec.CUSTOMER_APPROVAL_REQ_FLAG;
4356 END IF;
4357
4358 IF x_del_rec.EXPECTED_SHIPMENT_DATE = OKE_API.G_MISS_DATE THEN
4359 x_del_rec.EXPECTED_SHIPMENT_DATE := l_del_rec.EXPECTED_SHIPMENT_DATE;
4360 END IF;
4361
4362 IF x_del_rec.INITIATE_SHIPMENT_DATE = OKE_API.G_MISS_DATE THEN
4363 x_del_rec.INITIATE_SHIPMENT_DATE := l_del_rec.INITIATE_SHIPMENT_DATE;
4364 END IF;
4365
4366 IF x_del_rec.PROMISED_SHIPMENT_DATE = OKE_API.G_MISS_DATE THEN
4367 x_del_rec.PROMISED_SHIPMENT_DATE := l_del_rec.PROMISED_SHIPMENT_DATE;
4368 END IF;
4369
4370 IF x_del_rec.AS_OF_DATE = OKE_API.G_MISS_DATE THEN
4371 x_del_rec.AS_OF_DATE := l_del_rec.AS_OF_DATE;
4372 END IF;
4373
4374 IF x_del_rec.DATE_OF_FIRST_SUBMISSION = OKE_API.G_MISS_DATE THEN
4375 x_del_rec.DATE_OF_FIRST_SUBMISSION := l_del_rec.DATE_OF_FIRST_SUBMISSION;
4376 END IF;
4377
4378 IF x_del_rec.FREQUENCY = OKE_API.G_MISS_CHAR THEN
4379 x_del_rec.FREQUENCY := l_del_rec.FREQUENCY;
4380 END IF;
4381
4382 IF x_del_rec.ACQ_DOC_NUMBER = OKE_API.G_MISS_CHAR THEN
4383 x_del_rec.ACQ_DOC_NUMBER := l_del_rec.ACQ_DOC_NUMBER;
4384 END IF;
4385
4386 IF x_del_rec.SUBMISSION_FLAG = OKE_API.G_MISS_CHAR THEN
4387 x_del_rec.SUBMISSION_FLAG := l_del_rec.SUBMISSION_FLAG;
4388 END IF;
4389
4390 IF x_del_rec.DATA_ITEM_NAME = OKE_API.G_MISS_CHAR THEN
4391 x_del_rec.DATA_ITEM_NAME := l_del_rec.DATA_ITEM_NAME;
4392 END IF;
4393
4394 IF x_del_rec.DATA_ITEM_SUBTITLE = OKE_API.G_MISS_CHAR THEN
4395 x_del_rec.DATA_ITEM_SUBTITLE := l_del_rec.DATA_ITEM_SUBTITLE;
4396 END IF;
4397
4398 IF x_del_rec.TOTAL_NUM_OF_COPIES = OKE_API.G_MISS_NUM THEN
4399 x_del_rec.TOTAL_NUM_OF_COPIES := l_del_rec.TOTAL_NUM_OF_COPIES;
4400 END IF;
4401
4402 IF x_del_rec.CDRL_CATEGORY = OKE_API.G_MISS_CHAR THEN
4403 x_del_rec.CDRL_CATEGORY := l_del_rec.CDRL_CATEGORY;
4404 END IF;
4405
4406 IF x_del_rec.EXPORT_LICENSE_NUM = OKE_API.G_MISS_CHAR THEN
4407 x_del_rec.EXPORT_LICENSE_NUM := l_del_rec.EXPORT_LICENSE_NUM;
4408 END IF;
4409
4410 IF x_del_rec.EXPORT_LICENSE_RES = OKE_API.G_MISS_CHAR THEN
4411 x_del_rec.EXPORT_LICENSE_RES := l_del_rec.EXPORT_LICENSE_RES;
4412 END IF;
4413
4414 IF x_del_rec.EXPORT_FLAG = OKE_API.G_MISS_CHAR THEN
4415 x_del_rec.EXPORT_FLAG := l_del_rec.EXPORT_FLAG;
4416 END IF;
4417
4418 IF x_del_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
4419 x_del_rec.CREATED_BY := l_del_rec.CREATED_BY;
4420 END IF;
4421
4422 IF x_del_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
4423 x_del_rec.CREATION_DATE := l_del_rec.CREATION_DATE;
4424 END IF;
4425
4426 IF x_del_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
4427 x_del_rec.LAST_UPDATED_BY := l_del_rec.LAST_UPDATED_BY;
4428 END IF;
4429
4430 IF x_del_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
4431 x_del_rec.LAST_UPDATE_LOGIN := l_del_rec.LAST_UPDATE_LOGIN;
4432 END IF;
4433
4434 IF x_del_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
4435 x_del_rec.LAST_UPDATE_DATE := l_del_rec.LAST_UPDATE_DATE;
4436 END IF;
4437
4438 IF x_del_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
4439 x_del_rec.ATTRIBUTE_CATEGORY := l_del_rec.ATTRIBUTE_CATEGORY;
4440 END IF;
4441
4442 IF x_del_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
4443 x_del_rec.ATTRIBUTE1 := l_del_rec.ATTRIBUTE1;
4444 END IF;
4445
4446 IF x_del_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
4447 x_del_rec.ATTRIBUTE2 := l_del_rec.ATTRIBUTE2;
4448 END IF;
4449
4450 IF x_del_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
4451 x_del_rec.ATTRIBUTE3 := l_del_rec.ATTRIBUTE3;
4452 END IF;
4453
4454 IF x_del_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
4455 x_del_rec.ATTRIBUTE4 := l_del_rec.ATTRIBUTE4;
4456 END IF;
4457
4458 IF x_del_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
4459 x_del_rec.ATTRIBUTE5 := l_del_rec.ATTRIBUTE5;
4460 END IF;
4461
4462 IF x_del_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
4463 x_del_rec.ATTRIBUTE6 := l_del_rec.ATTRIBUTE6;
4464 END IF;
4465
4466 IF x_del_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
4467 x_del_rec.ATTRIBUTE7 := l_del_rec.ATTRIBUTE7;
4468 END IF;
4469
4470 IF x_del_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
4471 x_del_rec.ATTRIBUTE8 := l_del_rec.ATTRIBUTE8;
4472 END IF;
4473
4474 IF x_del_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
4475 x_del_rec.ATTRIBUTE9 := l_del_rec.ATTRIBUTE9;
4476 END IF;
4477
4478 IF x_del_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
4479 x_del_rec.ATTRIBUTE10 := l_del_rec.ATTRIBUTE10;
4480 END IF;
4481
4482 IF x_del_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
4483 x_del_rec.ATTRIBUTE11 := l_del_rec.ATTRIBUTE11;
4484 END IF;
4485
4486 IF x_del_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
4487 x_del_rec.ATTRIBUTE12 := l_del_rec.ATTRIBUTE12;
4488 END IF;
4489
4490 IF x_del_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
4491 x_del_rec.ATTRIBUTE13 := l_del_rec.ATTRIBUTE13;
4492 END IF;
4493
4494 IF x_del_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
4495 x_del_rec.ATTRIBUTE14 := l_del_rec.ATTRIBUTE14;
4496 END IF;
4497
4498 IF x_del_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
4499 x_del_rec.ATTRIBUTE15 := l_del_rec.ATTRIBUTE15;
4500 END IF;
4501
4502 IF x_del_rec.WEIGHT = OKE_API.G_MISS_NUM THEN
4503 x_del_rec.WEIGHT := l_del_rec.WEIGHT;
4504 END IF;
4505
4506 IF x_del_rec.WEIGHT_UOM_CODE = OKE_API.G_MISS_CHAR THEN
4507 x_del_rec.WEIGHT_UOM_CODE := l_del_rec.WEIGHT_UOM_CODE;
4508 END IF;
4509
4510 IF x_del_rec.VOLUME = OKE_API.G_MISS_NUM THEN
4511 x_del_rec.VOLUME := l_del_rec.VOLUME;
4512 END IF;
4513
4514 IF x_del_rec.VOLUME_UOM_CODE = OKE_API.G_MISS_CHAR THEN
4515 x_del_rec.VOLUME_UOM_CODE := l_del_rec.VOLUME_UOM_CODE;
4516 END IF;
4517
4518 IF x_del_rec.EXPENDITURE_ORGANIZATION_ID = OKE_API.G_MISS_NUM THEN
4519 x_del_rec.EXPENDITURE_ORGANIZATION_ID := l_del_rec.EXPENDITURE_ORGANIZATION_ID;
4520 END IF;
4521
4522 IF x_del_rec.EXPENDITURE_TYPE = OKE_API.G_MISS_CHAR THEN
4523 x_del_rec.EXPENDITURE_TYPE := l_del_rec.EXPENDITURE_TYPE;
4524 END IF;
4525
4526 IF x_del_rec.DESTINATION_TYPE_CODE = OKE_API.G_MISS_CHAR THEN
4527 x_del_rec.DESTINATION_TYPE_CODE := l_del_rec.DESTINATION_TYPE_CODE;
4528 END IF;
4529
4530 IF x_del_rec.EXPENDITURE_ITEM_DATE = OKE_API.G_MISS_DATE THEN
4531 x_del_rec.EXPENDITURE_ITEM_DATE := l_del_rec.EXPENDITURE_ITEM_DATE;
4532 END IF;
4533
4534 IF x_del_rec.RATE_DATE = OKE_API.G_MISS_DATE THEN
4535 x_del_rec.RATE_DATE := l_del_rec.RATE_DATE;
4536 END IF;
4537
4538 IF x_del_rec.RATE_TYPE = OKE_API.G_MISS_CHAR THEN
4539 x_del_rec.RATE_TYPE := l_del_rec.RATE_TYPE;
4540 END IF;
4541
4542 IF x_del_rec.EXCHANGE_RATE = OKE_API.G_MISS_NUM THEN
4543 x_del_rec.EXCHANGE_RATE := l_del_rec.EXCHANGE_RATE;
4544 END IF;
4545
4546 IF x_del_rec.DESCRIPTION = OKE_API.G_MISS_CHAR THEN
4547 x_del_rec.DESCRIPTION := l_del_rec.DESCRIPTION;
4548 END IF;
4549
4550 IF x_del_rec.COMMENTS = OKE_API.G_MISS_CHAR THEN
4551 x_del_rec.COMMENTS := l_del_rec.COMMENTS;
4552 END IF;
4553
4554 IF x_del_rec.REQUISITION_LINE_TYPE_ID = OKE_API.G_MISS_NUM THEN
4555 x_del_rec.REQUISITION_LINE_TYPE_ID := l_del_rec.REQUISITION_LINE_TYPE_ID;
4556 END IF;
4557
4558 IF x_del_rec.PO_CATEGORY_ID = OKE_API.G_MISS_NUM THEN
4559 x_del_rec.PO_CATEGORY_ID := l_del_rec.PO_CATEGORY_ID;
4560 END IF;
4561
4562 RETURN(l_return_status);
4563
4564
4565
4566 END populate_new_record;
4567
4568 -- set attributes for oke_k_lines
4569
4570 FUNCTION set_attributes(
4571 p_del_rec IN del_rec_type,
4572 x_del_rec OUT NOCOPY del_rec_type
4573 ) RETURN VARCHAR2 IS
4574 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4575 BEGIN
4576
4577 x_del_rec := p_del_rec;
4578 x_del_rec.BILLABLE_FLAG := UPPER(x_del_rec.BILLABLE_FLAG);
4579 x_del_rec.SHIPPABLE_FLAG := UPPER(x_del_rec.SHIPPABLE_FLAG);
4580 x_del_rec.SUBCONTRACTED_FLAG := UPPER(x_del_rec.SUBCONTRACTED_FLAG);
4581 x_del_rec.COMPLETED_FLAG := UPPER(x_del_rec.COMPLETED_FLAG);
4582
4583 x_del_rec.DROP_SHIPPED_FLAG := UPPER(x_del_rec.DROP_SHIPPED_FLAG);
4584
4585 x_del_rec.CUSTOMER_APPROVAL_REQ_FLAG := UPPER(x_del_rec.CUSTOMER_APPROVAL_REQ_FLAG);
4586
4587 x_del_rec.INSPECTION_REQ_FLAG := UPPER(x_del_rec.INSPECTION_REQ_FLAG);
4588
4589 x_del_rec.INTERIM_RPT_REQ_FLAG := UPPER(x_del_rec.INTERIM_RPT_REQ_FLAG);
4590
4591 x_del_rec.EXPORT_FLAG := UPPER(x_del_rec.EXPORT_FLAG);
4592
4593 x_del_rec.CFE_REQ_FLAG := UPPER(x_del_rec.CFE_REQ_FLAG);
4594
4595 x_del_rec.DEFAULTED_FLAG := UPPER(x_del_rec.DEFAULTED_FLAG);
4596
4597 x_del_rec.IN_PROCESS_FLAG := UPPER(x_del_rec.IN_PROCESS_FLAG);
4598
4599 RETURN(l_return_status);
4600
4601 END Set_Attributes;
4602
4603 BEGIN
4604
4605
4606
4607 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
4608 G_PKG_NAME,
4609 p_init_msg_list,
4610 l_api_version,
4611 p_api_version,
4612 '_PVT',
4613 x_return_status);
4614
4615 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4616 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4617 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
4618 RAISE OKE_API.G_EXCEPTION_ERROR;
4619 END IF;
4620
4621
4622
4623 l_return_status := Set_Attributes(
4624 p_del_rec, -- IN
4625 l_del_rec); -- OUT
4626
4627 --- If any errors happen abort API
4628 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4629 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4630 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
4631 RAISE OKE_API.G_EXCEPTION_ERROR;
4632 END IF;
4633
4634
4635
4636 l_return_status := populate_new_record(l_del_rec, l_def_del_rec);
4637
4638 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4639 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4640 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
4641 RAISE OKE_API.G_EXCEPTION_ERROR;
4642 END IF;
4643
4644
4645
4646 l_def_del_rec := fill_who_columns(l_def_del_rec);
4647
4648
4649
4650 -- validate attributes when update is not necessory, since the control logic is at the
4651 -- client side
4652
4653 /* --- Validate all non-missing attributes (Item Level Validation)
4654 l_return_status := Validate_Attributes(l_def_del_rec);
4655
4656
4657
4658 --- If any errors happen abort API
4659 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4660 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4661 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
4662 RAISE OKE_API.G_EXCEPTION_ERROR;
4663 END IF;
4664
4665
4666
4667 l_return_status := Validate_Record(l_def_del_rec);
4668 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4669 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4670 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
4671 RAISE OKE_API.G_EXCEPTION_ERROR;
4672 END IF; */
4673
4674 UPDATE oke_k_deliverables_b
4675 SET
4676 deliverable_num = l_def_del_rec.deliverable_num,
4677 project_id = l_def_del_rec.project_id,
4678 task_id = l_def_del_rec.task_id,
4679 item_id = l_def_del_rec.item_id,
4680 k_header_id = l_def_del_rec.k_header_id,
4681 k_line_id = l_def_del_rec.k_line_id,
4682 delivery_date = l_def_del_rec.delivery_date,
4683 status_code = l_def_del_rec.status_code,
4684 parent_deliverable_id = l_def_del_rec.parent_deliverable_id,
4685 ship_to_org_id = l_def_del_rec.ship_to_org_id,
4686 ship_to_location_id = l_def_del_rec.ship_to_location_id,
4687 ship_from_org_id = l_def_del_rec.ship_from_org_id,
4688 ship_from_location_id = l_def_del_rec.ship_from_location_id,
4689 inventory_org_id = l_def_del_rec.inventory_org_id,
4690 direction = l_def_del_rec.direction,
4691 defaulted_flag = l_def_del_rec.defaulted_flag,
4692 in_process_flag = l_def_del_rec.in_process_flag,
4693 wf_item_key = l_def_del_rec.wf_item_key,
4694 sub_ref_id = l_def_del_rec.sub_ref_id,
4695 start_date = l_def_del_rec.start_date,
4696 end_date = l_def_del_rec.end_date,
4697 priority_code = l_def_del_rec.priority_code,
4698 currency_code = l_def_del_rec.currency_code,
4699 unit_price = l_def_del_rec.unit_price,
4700 uom_code = l_def_del_rec.uom_code,
4701 quantity = l_def_del_rec.quantity,
4702 country_of_origin_code = l_def_del_rec.country_of_origin_code,
4703 subcontracted_flag = l_def_del_rec.subcontracted_flag,
4704 dependency_flag = l_def_del_rec.dependency_flag,
4705 billable_flag = l_def_del_rec.billable_flag,
4706 billing_event_id = l_def_del_rec.billing_event_id,
4707 drop_shipped_flag = l_def_del_rec.drop_shipped_flag,
4708 completed_flag = l_def_del_rec.completed_flag,
4709 available_for_ship_flag = l_def_del_rec.available_for_ship_flag,
4710 create_demand = l_def_del_rec.create_demand,
4711 ready_to_bill = l_def_del_rec.ready_to_bill,
4712 need_by_date = l_def_del_rec.need_by_date,
4713 ready_to_procure = l_def_del_rec.ready_to_procure,
4714 mps_transaction_id = l_def_del_rec.mps_transaction_id,
4715 po_ref_1 = l_def_del_rec.po_ref_1,
4716 po_ref_2 = l_def_del_rec.po_ref_2,
4717 po_ref_3 = l_def_del_rec.po_ref_3,
4718 shipping_request_id = l_def_del_rec.shipping_request_id,
4719 unit_number = l_def_del_rec.unit_number,
4720 ndb_schedule_designator = l_def_del_rec.ndb_schedule_designator,
4721 shippable_flag = l_def_del_rec.shippable_flag,
4722 cfe_req_flag = l_def_del_rec.cfe_req_flag,
4723 inspection_req_flag = l_def_del_rec.inspection_req_flag,
4724 interim_rpt_req_flag = l_def_del_rec.interim_rpt_req_flag,
4725 lot_applies_flag = l_def_del_rec.lot_applies_flag,
4726 customer_approval_req_flag = l_def_del_rec.customer_approval_req_flag,
4727 expected_shipment_date = l_def_del_rec.expected_shipment_date,
4728 initiate_shipment_date = l_def_del_rec.initiate_shipment_date,
4729 promised_shipment_date = l_def_del_rec.promised_shipment_date,
4730 as_of_date = l_def_del_rec.as_of_date,
4731 date_of_first_submission = l_def_del_rec.date_of_first_submission,
4732 frequency = l_def_del_rec.frequency,
4733 acq_doc_number = l_def_del_rec.acq_doc_number,
4734 submission_flag = l_def_del_rec.submission_flag,
4735 data_item_subtitle = l_def_del_rec.data_item_subtitle,
4736 total_num_of_copies = l_def_del_rec.total_num_of_copies,
4737 cdrl_category = l_def_del_rec.cdrl_category,
4738 data_item_name = l_def_del_rec.data_item_name,
4739 export_flag = l_def_del_rec.export_flag,
4740 export_license_num = l_def_del_rec.export_license_num,
4741 export_license_res = l_def_del_rec.export_license_res,
4742 created_by = l_def_del_rec.created_by,
4743 creation_date = l_def_del_rec.creation_date,
4744 last_updated_by = l_def_del_rec.last_updated_by,
4745 last_update_login = l_def_del_rec.last_update_login,
4746 last_update_date = l_def_del_rec.last_update_date,
4747 attribute_category = l_def_del_rec.attribute_category,
4748 attribute1 = l_def_del_rec.attribute1,
4749 attribute2 = l_def_del_rec.attribute2,
4750 attribute3 = l_def_del_rec.attribute3,
4751 attribute4 = l_def_del_rec.attribute4,
4752 attribute5 = l_def_del_rec.attribute5,
4753 attribute6 = l_def_del_rec.attribute6,
4754 attribute7 = l_def_del_rec.attribute7,
4755 attribute8 = l_def_del_rec.attribute8,
4756 attribute9 = l_def_del_rec.attribute9,
4757 attribute10 = l_def_del_rec.attribute10,
4758 attribute11 = l_def_del_rec.attribute11,
4759 attribute12 = l_def_del_rec.attribute12,
4760 attribute13 = l_def_del_rec.attribute13,
4761 attribute14 = l_def_del_rec.attribute14,
4762 attribute15 = l_def_del_rec.attribute15,
4763 weight = l_def_del_rec.weight,
4764 weight_uom_code = l_def_del_rec.weight_uom_code,
4765 volume = l_def_del_rec.volume,
4766 volume_uom_code = l_def_del_rec.volume_uom_code,
4767 expenditure_organization_id = l_def_del_rec.expenditure_organization_id,
4768 expenditure_type = l_def_del_rec.expenditure_type,
4769 expenditure_item_date = l_def_del_rec.expenditure_item_date,
4770 destination_type_code = l_def_del_rec.destination_type_code,
4771 rate_type = l_def_del_rec.rate_type,
4772 rate_date = l_def_del_rec.rate_date,
4773 exchange_rate = l_def_del_rec.exchange_rate,
4774 requisition_line_type_id = l_def_del_rec.requisition_line_type_id,
4775 po_category_id = l_def_del_rec.po_category_id
4776 where deliverable_id = l_def_del_rec.deliverable_id;
4777
4778 -- update the TL table
4779
4780 update oke_k_deliverables_tl
4781 set
4782 description = l_def_del_rec.description,
4783 comments = l_def_del_rec.comments,
4784 sfwt_flag = l_def_del_rec.sfwt_flag,
4785 source_lang = userenv('LANG')
4786 where deliverable_id = l_def_del_rec.deliverable_id
4787 and userenv('LANG') in (language , source_lang);
4788
4789 x_del_rec := l_def_del_rec;
4790
4791 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
4792
4793 EXCEPTION
4794 WHEN OKE_API.G_EXCEPTION_ERROR THEN
4795 x_return_status := OKE_API.HANDLE_EXCEPTIONS
4796 (
4797 l_api_name,
4798 G_PKG_NAME,
4799 'OKE_API.G_RET_STS_ERROR',
4800 x_msg_count,
4801 x_msg_data,
4802 '_PVT'
4803 );
4804 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4805 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
4806 (
4807 l_api_name,
4808 G_PKG_NAME,
4809 'OKE_API.G_RET_STS_UNEXP_ERROR',
4810 x_msg_count,
4811 x_msg_data,
4812 '_PVT'
4813 );
4814 WHEN OTHERS THEN
4815 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
4816 (
4817 l_api_name,
4818 G_PKG_NAME,
4819 'OTHERS',
4820 x_msg_count,
4821 x_msg_data,
4822 '_PVT'
4823 );
4824 END update_row;
4825
4826 PROCEDURE update_row(
4827 p_api_version IN NUMBER,
4828 p_init_msg_list IN VARCHAR2 ,
4829 x_return_status OUT NOCOPY VARCHAR2,
4830 x_msg_count OUT NOCOPY NUMBER,
4831 x_msg_data OUT NOCOPY VARCHAR2,
4832 p_del_tbl IN del_tbl_type,
4833 x_del_tbl OUT NOCOPY del_tbl_type) IS
4834
4835 l_api_version CONSTANT NUMBER := 1.0;
4836 l_api_name CONSTANT VARCHAR2(30) := 'TBL_update_row';
4837
4838 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4839 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4840 i NUMBER := 0;
4841 BEGIN
4842 OKE_API.init_msg_list(p_init_msg_list);
4843 -- Make sure PL/SQL table has records in it before passing
4844 IF (p_del_tbl.COUNT > 0) THEN
4845 i := p_del_tbl.FIRST;
4846 LOOP
4847 update_row (
4848 p_api_version => p_api_version,
4849 p_init_msg_list => G_FALSE,
4850 x_return_status => x_return_status,
4851 x_msg_count => x_msg_count,
4852 x_msg_data => x_msg_data,
4853 p_del_rec => p_del_tbl(i),
4854 x_del_rec => x_del_tbl(i));
4855
4856 -- store the highest degree of error
4857 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
4858 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
4859 l_overall_status := x_return_status;
4860 End If;
4861 End If;
4862
4863 EXIT WHEN (i = p_del_tbl.LAST);
4864 i := p_del_tbl.NEXT(i);
4865 END LOOP;
4866 -- return overall status
4867 x_return_status := l_overall_status;
4868 END IF;
4869
4870 EXCEPTION
4871 WHEN OKE_API.G_EXCEPTION_ERROR THEN
4872 x_return_status := OKE_API.HANDLE_EXCEPTIONS
4873 (
4874 l_api_name,
4875 G_PKG_NAME,
4876 'OKE_API.G_RET_STS_ERROR',
4877 x_msg_count,
4878 x_msg_data,
4879 '_PVT'
4880 );
4881 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4882 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
4883 (
4884 l_api_name,
4885 G_PKG_NAME,
4886 'OKE_API.G_RET_STS_UNEXP_ERROR',
4887 x_msg_count,
4888 x_msg_data,
4889 '_PVT'
4890 );
4891 WHEN OTHERS THEN
4892 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
4893 (
4894 l_api_name,
4895 G_PKG_NAME,
4896 'OTHERS',
4897 x_msg_count,
4898 x_msg_data,
4899 '_PVT'
4900 );
4901 END update_row;
4902
4903 PROCEDURE delete_row(
4904 p_api_version IN NUMBER,
4905 p_init_msg_list IN VARCHAR2 ,
4906 x_return_status OUT NOCOPY VARCHAR2,
4907 x_msg_count OUT NOCOPY NUMBER,
4908 x_msg_data OUT NOCOPY VARCHAR2,
4909 p_del_rec IN del_rec_type) IS
4910
4911 l_api_version CONSTANT NUMBER := 1;
4912 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
4913 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4914 l_del_rec del_rec_type := p_del_rec;
4915
4916 BEGIN
4917
4918 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
4919 p_init_msg_list,
4920 '_PVT',
4921 x_return_status);
4922
4923 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
4924 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4925 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
4926 RAISE OKE_API.G_EXCEPTION_ERROR;
4927 END IF;
4928
4929 DELETE FROM oke_k_deliverables_b
4930 WHERE deliverable_id = l_del_rec.deliverable_id;
4931
4932 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
4933
4934 EXCEPTION
4935 WHEN OKE_API.G_EXCEPTION_ERROR THEN
4936 x_return_status := OKE_API.HANDLE_EXCEPTIONS
4937 (
4938 l_api_name,
4939 G_PKG_NAME,
4940 'OKE_API.G_RET_STS_ERROR',
4941 x_msg_count,
4942 x_msg_data,
4943 '_PVT'
4944 );
4945 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4946 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
4947 (
4948 l_api_name,
4949 G_PKG_NAME,
4950 'OKE_API.G_RET_STS_UNEXP_ERROR',
4951 x_msg_count,
4952 x_msg_data,
4953 '_PVT'
4954 );
4955 WHEN OTHERS THEN
4956 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
4957 (
4958 l_api_name,
4959 G_PKG_NAME,
4960 'OTHERS',
4961 x_msg_count,
4962 x_msg_data,
4963 '_PVT'
4964 );
4965 END delete_row;
4966
4967 PROCEDURE delete_row(
4968 p_api_version IN NUMBER,
4969 p_init_msg_list IN VARCHAR2 ,
4970 x_return_status OUT NOCOPY VARCHAR2,
4971 x_msg_count OUT NOCOPY NUMBER,
4972 x_msg_data OUT NOCOPY VARCHAR2,
4973 p_del_tbl IN del_tbl_type) IS
4974
4975 l_api_version CONSTANT NUMBER := 1;
4976 l_api_name CONSTANT VARCHAR2(30) := 'TBL_delete_row';
4977 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4978 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4979 i NUMBER := 0;
4980 BEGIN
4981 OKE_API.init_msg_list(p_init_msg_list);
4982
4983 -- Make sure PL/SQL table has records in it before passing
4984 IF (p_del_tbl.COUNT > 0) THEN
4985 i := p_del_tbl.FIRST;
4986 LOOP
4987 delete_row (
4988 p_api_version => p_api_version,
4989 p_init_msg_list => G_FALSE,
4990 x_return_status => x_return_status,
4991 x_msg_count => x_msg_count,
4992 x_msg_data => x_msg_data,
4993 p_del_rec => p_del_tbl(i));
4994
4995 -- store the highest degree of error
4996 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
4997 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
4998 l_overall_status := x_return_status;
4999 End If;
5000 End If;
5001
5002 EXIT WHEN (i = p_del_tbl.LAST);
5003 i := p_del_tbl.NEXT(i);
5004 END LOOP;
5005
5006 -- return overall status
5007 x_return_status := l_overall_status;
5008 END IF;
5009
5010 EXCEPTION
5011 WHEN OKE_API.G_EXCEPTION_ERROR THEN
5012 x_return_status := OKE_API.HANDLE_EXCEPTIONS
5013 (
5014 l_api_name,
5015 G_PKG_NAME,
5016 'OKE_API.G_RET_STS_ERROR',
5017 x_msg_count,
5018 x_msg_data,
5019 '_PVT'
5020 );
5021 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5022 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
5023 (
5024 l_api_name,
5025 G_PKG_NAME,
5026 'OKE_API.G_RET_STS_UNEXP_ERROR',
5027 x_msg_count,
5028 x_msg_data,
5029 '_PVT'
5030 );
5031 WHEN OTHERS THEN
5032 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
5033 (
5034 l_api_name,
5035 G_PKG_NAME,
5036 'OTHERS',
5037 x_msg_count,
5038 x_msg_data,
5039 '_PVT'
5040 );
5041 END delete_row;
5042
5043 PROCEDURE lock_row(
5044 p_api_version IN NUMBER,
5045 p_init_msg_list IN VARCHAR2 ,
5046 x_return_status OUT NOCOPY VARCHAR2,
5047 x_msg_count OUT NOCOPY NUMBER,
5048 x_msg_data OUT NOCOPY VARCHAR2,
5049 p_del_rec IN del_rec_type) IS
5050
5051 E_Resource_Busy EXCEPTION;
5052 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
5053 CURSOR lock_csr (p_del_rec IN del_rec_type) IS
5054 SELECT deliverable_num
5055 FROM oke_k_deliverables_b
5056 WHERE deliverable_id = p_del_rec.deliverable_id
5057
5058 FOR UPDATE OF deliverable_id NOWAIT;
5059
5060 CURSOR lchk_csr (p_del_rec IN del_rec_type) IS
5061 SELECT deliverable_num
5062 FROM oke_k_deliverables_b
5063 WHERE deliverable_id = p_del_rec.deliverable_id;
5064 l_api_version CONSTANT NUMBER := 1;
5065 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
5066 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
5067 l_deliverable_num OKE_K_DELIVERABLES_B.DELIVERABLE_NUM%TYPE;
5068 lc_deliverable_num OKE_K_DELIVERABLES_B.DELIVERABLE_NUM%TYPE;
5069 l_row_notfound BOOLEAN := FALSE;
5070 lc_row_notfound BOOLEAN := FALSE;
5071 BEGIN
5072 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
5073 p_init_msg_list,
5074 '_PVT',
5075 x_return_status);
5076 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
5077 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
5078 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
5079 RAISE OKE_API.G_EXCEPTION_ERROR;
5080 END IF;
5081 BEGIN
5082 OPEN lock_csr(p_del_rec);
5083 FETCH lock_csr INTO l_deliverable_num;
5084 l_row_notfound := lock_csr%NOTFOUND;
5085 CLOSE lock_csr;
5086 EXCEPTION
5087 WHEN E_Resource_Busy THEN
5088 IF (lock_csr%ISOPEN) THEN
5089 CLOSE lock_csr;
5090 END IF;
5091 OKE_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
5092 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
5093 END;
5094
5095 IF ( l_row_notfound ) THEN
5096 OPEN lchk_csr(p_del_rec);
5097 FETCH lchk_csr INTO lc_deliverable_num;
5098 lc_row_notfound := lchk_csr%NOTFOUND;
5099 CLOSE lchk_csr;
5100 END IF;
5101 IF (lc_row_notfound) THEN
5102 OKE_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
5103 RAISE OKE_API.G_EXCEPTION_ERROR;
5104 ELSIF lc_deliverable_num > p_del_rec.deliverable_num THEN
5105 OKE_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
5106 RAISE OKE_API.G_EXCEPTION_ERROR;
5107 ELSIF lc_deliverable_num <> p_del_rec.deliverable_num THEN
5108 OKE_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
5109 RAISE OKE_API.G_EXCEPTION_ERROR;
5110 ELSIF lc_deliverable_num = -1 THEN
5111 OKE_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
5112 RAISE OKE_API.G_EXCEPTION_ERROR;
5113 END IF;
5114 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
5115 EXCEPTION
5116 WHEN OKE_API.G_EXCEPTION_ERROR THEN
5117 x_return_status := OKE_API.HANDLE_EXCEPTIONS
5118 (
5119 l_api_name,
5120 G_PKG_NAME,
5121 'OKE_API.G_RET_STS_ERROR',
5122 x_msg_count,
5123 x_msg_data,
5124 '_PVT'
5125 );
5126 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5127 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
5128 (
5129 l_api_name,
5130 G_PKG_NAME,
5131 'OKE_API.G_RET_STS_UNEXP_ERROR',
5132 x_msg_count,
5133 x_msg_data,
5134 '_PVT'
5135 );
5136 WHEN OTHERS THEN
5137 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
5138 (
5139 l_api_name,
5140 G_PKG_NAME,
5141 'OTHERS',
5142 x_msg_count,
5143 x_msg_data,
5144 '_PVT'
5145 );
5146 END lock_row;
5147
5148 PROCEDURE lock_row(
5149 p_api_version IN NUMBER,
5150 p_init_msg_list IN VARCHAR2 ,
5151 x_return_status OUT NOCOPY VARCHAR2,
5152 x_msg_count OUT NOCOPY NUMBER,
5153 x_msg_data OUT NOCOPY VARCHAR2,
5154 p_del_tbl IN del_tbl_type) IS
5155
5156 l_api_version CONSTANT NUMBER := 1;
5157 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
5158 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
5159 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
5160 i NUMBER := 0;
5161 BEGIN
5162 OKE_API.init_msg_list(p_init_msg_list);
5163 -- Make sure PL/SQL table has records in it before passing
5164 IF (p_del_tbl.COUNT > 0) THEN
5165 i := p_del_tbl.FIRST;
5166 LOOP
5167 lock_row (
5168 p_api_version => p_api_version,
5169 p_init_msg_list => G_FALSE,
5170 x_return_status => x_return_status,
5171 x_msg_count => x_msg_count,
5172 x_msg_data => x_msg_data,
5173 p_del_rec => p_del_tbl(i));
5174
5175 -- store the highest degree of error
5176 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
5177 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
5178 l_overall_status := x_return_status;
5179 End If;
5180 End If;
5181
5182 EXIT WHEN (i = p_del_tbl.LAST);
5183 i := p_del_tbl.NEXT(i);
5184 END LOOP;
5185 -- return overall status
5186 x_return_status := l_overall_status;
5187 END IF;
5188 EXCEPTION
5189 WHEN OKE_API.G_EXCEPTION_ERROR THEN
5190 x_return_status := OKE_API.HANDLE_EXCEPTIONS
5191 (
5192 l_api_name,
5193 G_PKG_NAME,
5194 'OKE_API.G_RET_STS_ERROR',
5195 x_msg_count,
5196 x_msg_data,
5197 '_PVT'
5198 );
5199 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5200 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
5201 (
5202 l_api_name,
5203 G_PKG_NAME,
5204 'OKE_API.G_RET_STS_UNEXP_ERROR',
5205 x_msg_count,
5206 x_msg_data,
5207 '_PVT'
5208 );
5209 WHEN OTHERS THEN
5210 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
5211 (
5212 l_api_name,
5213 G_PKG_NAME,
5214 'OTHERS',
5215 x_msg_count,
5216 x_msg_data,
5217 '_PVT'
5218 );
5219 END lock_row;
5220
5221 PROCEDURE add_language
5222 is
5223 begin
5224 delete from OKE_K_DELIVERABLES_TL T
5225 where not exists
5226 (select NULL
5227 from OKE_K_DELIVERABLES_B B
5228 where B.DELIVERABLE_ID = T.DELIVERABLE_ID
5229 );
5230
5231 update OKE_K_DELIVERABLES_TL T set (
5232 DESCRIPTION,
5233 COMMENTS
5234 ) = (select
5235 B.DESCRIPTION,
5236 B.COMMENTS
5237 from OKE_K_DELIVERABLES_TL B
5238 where B.DELIVERABLE_ID = T.DELIVERABLE_ID
5239 and B.LANGUAGE = T.SOURCE_LANG)
5240 where (
5241 T.DELIVERABLE_ID,
5242 T.LANGUAGE
5243 ) in (select
5244 SUBT.DELIVERABLE_ID,
5245 SUBT.LANGUAGE
5246 from OKE_K_DELIVERABLES_TL SUBB, OKE_K_DELIVERABLES_TL SUBT
5247 where SUBB.DELIVERABLE_ID = SUBT.DELIVERABLE_ID
5248 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
5249 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
5250 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
5251 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
5252 or SUBB.COMMENTS <> SUBT.COMMENTS
5253 or (SUBB.COMMENTS is null and SUBT.COMMENTS is not null)
5254 or (SUBB.COMMENTS is not null and SUBT.COMMENTS is null)
5255 ));
5256
5257 insert into OKE_K_DELIVERABLES_TL (
5258 DELIVERABLE_ID,
5259 CREATION_DATE,
5260 CREATED_BY,
5261 LAST_UPDATE_DATE,
5262 LAST_UPDATED_BY,
5263 LAST_UPDATE_LOGIN,
5264 K_HEADER_ID,
5265 K_LINE_ID,
5266 SFWT_FLAG,
5267 DESCRIPTION,
5268 COMMENTS,
5269 LANGUAGE,
5270 SOURCE_LANG
5271 ) select
5272 B.DELIVERABLE_ID,
5273 B.CREATION_DATE,
5274 B.CREATED_BY,
5275 B.LAST_UPDATE_DATE,
5276 B.LAST_UPDATED_BY,
5277 B.LAST_UPDATE_LOGIN,
5278 B.K_HEADER_ID,
5279 B.K_LINE_ID,
5280 B.SFWT_FLAG,
5281 B.DESCRIPTION,
5282 B.COMMENTS,
5283 L.LANGUAGE_CODE,
5284 B.SOURCE_LANG
5285 from OKE_K_DELIVERABLES_TL B, FND_LANGUAGES L
5286 where L.INSTALLED_FLAG in ('I', 'B')
5287 and B.LANGUAGE = userenv('LANG')
5288 and not exists
5289 (select NULL
5290 from OKE_K_DELIVERABLES_TL T
5291 where T.DELIVERABLE_ID = B.DELIVERABLE_ID
5292 and T.LANGUAGE = L.LANGUAGE_CODE);
5293
5294 --
5295 -- History table
5296 --
5297 delete from OKE_K_DELIVERABLES_TLH T
5298 where not exists
5299 (select NULL
5300 from OKE_K_DELIVERABLES_BH B
5301 where B.DELIVERABLE_ID = T.DELIVERABLE_ID
5302 and T.MAJOR_VERSION = B.MAJOR_VERSION
5303 );
5304
5305 update OKE_K_DELIVERABLES_TLH T set (
5306 DESCRIPTION,
5307 COMMENTS
5308 ) = (select
5309 B.DESCRIPTION,
5310 B.COMMENTS
5311 from OKE_K_DELIVERABLES_TLH B
5312 where B.DELIVERABLE_ID = T.DELIVERABLE_ID
5313 and B.LANGUAGE = T.SOURCE_LANG
5314 and T.MAJOR_VERSION = B.MAJOR_VERSION)
5315 where (
5316 T.DELIVERABLE_ID,
5317 T.MAJOR_VERSION,
5318 T.LANGUAGE
5319 ) in (select
5320 SUBT.DELIVERABLE_ID,
5321 SUBT.MAJOR_VERSION,
5322 SUBT.LANGUAGE
5323 from OKE_K_DELIVERABLES_TLH SUBB, OKE_K_DELIVERABLES_TLH SUBT
5324 where SUBB.DELIVERABLE_ID = SUBT.DELIVERABLE_ID
5325 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
5326 and SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
5327 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
5328 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
5329 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
5330 or SUBB.COMMENTS <> SUBT.COMMENTS
5331 or (SUBB.COMMENTS is null and SUBT.COMMENTS is not null)
5332 or (SUBB.COMMENTS is not null and SUBT.COMMENTS is null)
5333 ));
5334
5335 insert into OKE_K_DELIVERABLES_TLH (
5336 DELIVERABLE_ID,
5337 MAJOR_VERSION,
5338 CREATION_DATE,
5339 CREATED_BY,
5340 LAST_UPDATE_DATE,
5341 LAST_UPDATED_BY,
5342 LAST_UPDATE_LOGIN,
5343 K_HEADER_ID,
5344 K_LINE_ID,
5345 SFWT_FLAG,
5346 DESCRIPTION,
5347 COMMENTS,
5348 LANGUAGE,
5349 SOURCE_LANG
5350 ) select
5351 B.DELIVERABLE_ID,
5352 B.MAJOR_VERSION,
5353 B.CREATION_DATE,
5354 B.CREATED_BY,
5355 B.LAST_UPDATE_DATE,
5356 B.LAST_UPDATED_BY,
5357 B.LAST_UPDATE_LOGIN,
5358 B.K_HEADER_ID,
5359 B.K_LINE_ID,
5360 B.SFWT_FLAG,
5361 B.DESCRIPTION,
5362 B.COMMENTS,
5363 L.LANGUAGE_CODE,
5364 B.SOURCE_LANG
5365 from OKE_K_DELIVERABLES_TLH B, FND_LANGUAGES L
5366 where L.INSTALLED_FLAG in ('I', 'B')
5367 and B.LANGUAGE = userenv('LANG')
5368 and not exists
5369 (select NULL
5370 from OKE_K_DELIVERABLES_TLH T
5371 where T.DELIVERABLE_ID = B.DELIVERABLE_ID
5372 and T.LANGUAGE = L.LANGUAGE_CODE
5373 and T.MAJOR_VERSION = B.MAJOR_VERSION);
5374
5375 END add_language;
5376
5377 END OKE_DELIVERABLE_PVT;