[Home] [Help]
PACKAGE BODY: APPS.OKE_ACTION_VALIDATIONS_PKG
Source
1 PACKAGE BODY OKE_ACTION_VALIDATIONS_PKG AS
2 /* $Header: OKEVDATB.pls 120.2 2011/05/03 12:51:17 serukull ship $ */
3
4 G_Yes CONSTANT VARCHAR2(1) := 'Y';
5 G_No CONSTANT VARCHAR2(1) := 'N';
6
7 L_req_list VARCHAR2(2000);
8 PROCEDURE Add_Attr_To_Required_List( name VARCHAR2 ) IS
9 l_msg VARCHAR2(2000) := NULL;
10 BEGIN
11 IF name LIKE 'OKE%' THEN
12 FND_MESSAGE.Set_Name ( 'OKE', name );
13 l_msg := FND_MESSAGE.Get;
14 ELSE
15 l_msg := name;
16 END IF;
17 IF L_req_list IS NOT NULL THEN
18 FND_MESSAGE.Set_Name ( 'OKE', 'OKE_LIST' ); -- 'START, END' for left2right and reverse for right2left
19 FND_MESSAGE.Set_Token ( 'START', L_req_list );
20 FND_MESSAGE.Set_Token ( 'END', L_msg );
21 L_req_list := FND_MESSAGE.Get;
22 ELSE
23 L_req_list := l_msg ;
24 END IF;
25 END Add_Attr_To_Required_List;
26
27
28 FUNCTION Check_Pjm ( P_Action_ID NUMBER
29 , P_Organization_ID NUMBER
30 , P_Project_ID NUMBER )
31 RETURN BOOLEAN IS
32
33 L_Value VARCHAR2(25);
34 L_ID NUMBER;
35 L_Prj_ID NUMBER;
36 L_Result BOOLEAN;
37 L_Msg VARCHAR2(2000);
38
39 CURSOR Org_C IS
40 SELECT 'X' FROM pjm_org_parameters
41 WHERE Organization_ID = L_ID
42 AND PROJECT_REFERENCE_ENABLED = 'Y';
43
44 CURSOR Prj_C IS
45 SELECT 'X' FROM pjm_project_parameters
46 WHERE Organization_ID = L_ID
47 AND Project_ID = L_Prj_ID;
48
49
50 BEGIN
51
52 -- Validate inventory org against pjm
53 L_ID := P_Organization_ID;
54 OPEN Org_C;
55 FETCH Org_C INTO L_Value;
56 CLOSE Org_C;
57
58 IF L_Value IS NULL THEN
59
60 L_Result := TRUE;
61 ELSE
62
63 L_Value := NULL;
64 -- Validate project with pjm
65 IF P_Project_ID IS NOT NULL THEN
66 L_Prj_ID := P_Project_ID;
67
68 OPEN Prj_C;
69 FETCH Prj_C INTO L_Value;
70 CLOSE Prj_C;
71
72 IF L_Value IS NULL THEN
73 --FND_MESSAGE.Set_Name('OKE', 'OKE_PROJECT_NOT_SETUP');
74 --L_Msg := FND_MESSAGE.Get;
75 -- l_msg := 'Project is not set up in project manufacturing';
76 -- Add_Msg ( P_Action_ID, L_Msg );
77 L_Result := FALSE;
78 ELSE
79 L_Result := TRUE;
80 END IF;
81 END IF;
82 END IF;
83
84
85 RETURN L_Result;
86
87 END Check_Pjm;
88
89 PROCEDURE Add_Msg ( P_Action_ID NUMBER
90 , P_Msg VARCHAR2 ) IS
91
92 L_ID NUMBER;
93 L_Name VARCHAR2(120);
94 L_Number VARCHAR2(120);
95
96 CURSOR c IS
97 SELECT pa_action_id
98 FROM oke_deliverable_actions
99 WHERE action_id = p_action_id;
100
101 BEGIN
102 OPEN c;
103 FETCH c INTO L_ID;
104 CLOSE c;
105
106 PA_DELIVERABLE_UTILS.Get_Action_Detail ( p_dlvr_action_ver_id => L_ID
107 , x_name => L_Name
108 , x_number => L_Number );
109 FND_MESSAGE.Set_Name ( 'PA', 'PA_ACTION_NAME_ERR');
110 FND_MESSAGE.Set_Token ( 'ACTION_NAME', L_Name );
111 FND_MESSAGE.Set_Token ( 'MESSAGE', P_Msg );
112 FND_MSG_PUB.Add;
113
114 END Add_Msg;
115
116
117 FUNCTION Validate_Mds ( P_Action_ID NUMBER
118 , P_Deliverable_ID NUMBER
119 , P_Task_ID NUMBER
120 , P_Ship_From_Org_ID NUMBER
121 , P_Ship_From_Location_ID NUMBER
122 , P_Ship_To_Org_ID NUMBER
123 , P_Ship_To_Location_ID NUMBER
124 , P_Schedule_Designator VARCHAR2
125 , P_Expected_Date DATE
126 , P_Quantity NUMBER
127 , P_Uom_Code VARCHAR2 ) RETURN VARCHAR2 IS
128
129 l_ret VARCHAR2(1) := G_YES;
130 L_Item_ID NUMBER;
131 L_Org_ID NUMBER;
132 L_Qty NUMBER;
133 L_Project_ID NUMBER;
134 l_dummy VARCHAR2(1);
135 L_Uom_Code VARCHAR2(30);
136 L_Msg VARCHAR2(2000);
137
138 CURSOR c1 IS
139 SELECT b.item_id
140 , b.inventory_org_id
141 , b.quantity
142 , b.uom_code
143 , b.project_id
144 FROM oke_deliverables_b b
145 WHERE b.deliverable_id = p_deliverable_id;
146
147 CURSOR c2 IS
148 SELECT '!'
149 FROM oke_system_items_v
150 WHERE id1 = l_item_id
151 AND organization_id = l_org_id
152 AND NVL(shippable_item_flag, 'N') = 'Y';
153
154 CURSOR c3 IS
155 SELECT '!'
156 FROM oke_deliverable_actions b
157 WHERE b.Action_ID = p_Action_ID
158 AND (b.deliverable_id = p_deliverable_id or b.deliverable_id is null and p_deliverable_id is null)
159 AND (b.Task_ID = p_Task_ID or b.Task_ID is null and p_Task_ID is null)
160 AND (b.Ship_From_Org_ID = p_Ship_From_Org_ID or b.Ship_From_Org_ID is null and p_Ship_From_Org_ID is null)
161 AND (b.Ship_From_Location_ID = p_Ship_From_Location_ID or b.Ship_From_Location_ID is null and p_Ship_From_Location_ID is null)
162 AND (b.Ship_To_Org_ID = p_Ship_To_Org_ID or b.Ship_To_Org_ID is null and p_Ship_To_Org_ID is null)
163 AND (b.Ship_To_Location_ID = p_Ship_To_Location_ID or b.Ship_To_Location_ID is null and p_Ship_To_Location_ID is null)
164 AND (b.Expected_Date = p_Expected_Date or b.Expected_Date is null and p_Expected_Date is null)
165 AND (b.Schedule_Designator = p_Schedule_Designator or b.Schedule_Designator is null and p_Schedule_Designator is null)
166 AND (b.Quantity = p_Quantity or b.Quantity is null and p_Quantity is null)
167 AND (b.Uom_Code = p_Uom_Code or b.Uom_Code is null and p_Uom_Code is null)
168 ;
169
170 BEGIN
171
172 FND_MSG_PUB.Initialize;
173 L_req_list := NULL;
174
175 l_dummy := NULL;
176 OPEN c3;
177 FETCH c3 INTO l_dummy;
178 CLOSE c3;
179
180 IF l_dummy IS NULL THEN
181 FND_MESSAGE.Set_Name ( 'OKE', 'OKE_SAVE_BEFORE_PROCEED' );
182 FND_MSG_PUB.Add;
183 RETURN G_NO;
184 END IF;
185
186 OPEN c1;
187 FETCH c1 INTO L_Item_ID, L_Org_ID, L_Qty, L_Uom_Code, L_Project_ID;
188 CLOSE c1;
189
190 IF P_Task_ID IS NULL THEN
191 Add_Attr_To_Required_List('OKE_DTS_REQ_TASK');
192 END IF;
193
194 IF L_Item_ID IS NULL THEN
195 Add_Attr_To_Required_List('OKE_DTS_REQ_ITEM');
196 END IF;
197
198 IF NVL ( L_Org_ID, P_Ship_From_Org_ID ) IS NULL THEN
199 Add_Attr_To_Required_List('OKE_DTS_REQ_ORG');
200 END IF;
201
202 IF L_Qty IS NULL THEN
203 Add_Attr_To_Required_List('OKE_DTS_REQ_QTY');
204 END IF;
205
206 IF L_Uom_Code IS NULL THEN
207 Add_Attr_To_Required_List('OKE_DTS_REQ_UOM');
208 END IF;
209
210 IF NOT Check_Pjm ( P_Action_ID, L_Org_ID, L_Project_ID ) THEN
211 -- l_msg := 'PJM setup is required';
212 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_NO_PJM_SETUP');
213 L_Msg := FND_MESSAGE.Get;
214 Add_Msg ( P_Action_ID, L_Msg );
215 l_ret := G_NO;
216 END IF;
217
218 IF P_Expected_Date IS NULL THEN
219 Add_Attr_To_Required_List('OKE_DTS_REQ_DATE');
220 ELSIF P_Expected_Date < Trunc( SYSDATE ) THEN
221 -- l_msg := 'The date is past due';
222 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_EXP_DATE_PAST' );
223 L_Msg := FND_MESSAGE.Get;
224 Add_Msg ( P_Action_ID, L_Msg );
225 l_ret := G_NO;
226 END IF;
227
228 IF P_Schedule_Designator IS NULL THEN
229 Add_Attr_To_Required_List('OKE_DTS_DATA_PLAN');
230 END IF;
231
232 IF L_req_list IS NOT NULL THEN
233 -- l_msg := 'The following data are required: ' || L_req_list;
234 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_MISS_REQ_DATA');
235 FND_MESSAGE.Set_Token ( 'DATA', L_req_list );
236 L_Msg := FND_MESSAGE.Get;
237 Add_Msg ( P_Action_ID, L_Msg );
238 l_ret := G_NO;
239 END IF;
240
241 RETURN l_ret;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 Add_Msg ( P_Action_ID, sqlerrm );
246 RETURN G_NO;
247
248 END Validate_MDS;
249
250 FUNCTION Validate_WSH ( P_Action_ID NUMBER
251 , P_Deliverable_ID NUMBER
252 , P_Task_ID NUMBER
253 , P_Ship_From_Org_ID NUMBER
254 , P_Ship_From_Location_ID NUMBER
255 , P_Ship_To_Org_ID NUMBER
256 , P_Ship_To_Location_ID NUMBER
257 , P_Expected_Date DATE
258 , P_Volume NUMBER
259 , P_Volume_Uom VARCHAR2
260 , P_Weight NUMBER
261 , P_Weight_Uom VARCHAR2
262 , P_Quantity NUMBER
263 , P_Uom_Code VARCHAR2 )
264 RETURN VARCHAR2 IS
265
266 L_Item_ID NUMBER;
267 L_Org_ID NUMBER;
268 L_Qty NUMBER;
269 l_Dummy VARCHAR2(1);
270 l_ret VARCHAR2(1) := G_YES;
271 L_Uom_Code VARCHAR2(30);
272 L_Msg VARCHAR2(2000);
273 L_Desc VARCHAR2(2000);
274 Item_Based BOOLEAN := TRUE;
275 L_ID NUMBER;
276 L_Project_ID NUMBER;
277
278 CURSOR c1 IS
279 SELECT b.item_id
280 , b.inventory_org_id
281 , b.source_deliverable_id
282 , b.project_id, 'x'
283 FROM oke_deliverables_b b
284 WHERE b.deliverable_id = p_deliverable_id
285 AND b.source_code = 'PA';
286
287 CURSOR c2 IS
288 SELECT 'x'
289 FROM oke_system_items_v
290 WHERE id1 = l_item_id
291 AND organization_id = l_org_id
292 AND NVL(shippable_item_flag, 'N') = 'Y';
293
294 BEGIN
295
296 FND_MSG_PUB.Initialize;
297 L_req_list := NULL;
298
299 l_dummy := NULL;
300 OPEN c1;
301 FETCH c1 INTO L_Item_ID, L_Org_ID, L_ID, L_Project_ID, l_dummy;
302 CLOSE c1;
303
304 IF l_dummy IS NULL THEN
305 FND_MESSAGE.Set_Name ( 'OKE', 'OKE_SAVE_BEFORE_PROCEED' );
306 FND_MSG_PUB.Add;
307 RETURN G_NO;
308 END IF;
309
310 IF P_Quantity IS NULL THEN
311 Add_Attr_To_Required_List('OKE_DTS_REQ_QTY');
312 END IF;
313
314 IF P_Uom_Code IS NULL THEN
315 Add_Attr_To_Required_List('OKE_DTS_REQ_UOM');
316 END IF;
317
318 IF P_Task_ID IS NULL THEN
319 Add_Attr_To_Required_List('OKE_DTS_REQ_TASK');
320 END IF;
321
322 IF PA_DELIVERABLE_UTILS.Is_Dlvr_Item_Based ( p_deliverable_id => L_ID ) = 'Y' THEN
323
324 IF L_Item_ID IS NULL THEN
325 Add_Attr_To_Required_List('OKE_DTS_REQ_ITEM');
326 END IF;
327
328 IF NVL ( L_Org_ID, P_Ship_From_Org_ID ) IS NULL THEN
329 Add_Attr_To_Required_List('OKE_DTS_REQ_ORG');
330 ELSE
331 l_dummy := NULL;
332 OPEN c2;
333 FETCH c2 INTO l_dummy;
334 CLOSE c2;
335
336 IF l_Dummy IS NULL THEN
337 -- l_msg := 'Item is not shippable';
338 FND_MESSAGE.Set_Name ( 'OKE', 'OKE_DTS_ITEM_NOSHIP');
339 L_Msg := FND_MESSAGE.Get;
340 Add_Msg ( P_Action_ID, L_Msg );
341 l_ret := G_NO;
342 END IF;
343 END IF;
344
345 ELSE
346 IF P_Volume IS NULL OR P_Volume_UOM IS NULL OR P_Weight IS NULL OR P_Weight_UOM IS NULL THEN
347 Add_Attr_To_Required_List('OKE_DTS_REQ_VOLUME');
348 END IF;
349
350 L_Desc := PA_DELIVERABLE_UTILS.Get_Deliverable_Description ( P_Deliverable_ID => L_ID );
351 IF L_Desc IS NULL THEN
352 Add_Attr_To_Required_List('OKE_DTS_REQ_DESC');
353 END IF;
354 END IF;
355
356
357 IF P_Ship_To_Location_ID IS NULL THEN
358 Add_Attr_To_Required_List('OKE_DTS_REQ_SHIPTO');
359 END IF;
360
361 IF P_Ship_From_Location_ID IS NULL THEN
362 Add_Attr_To_Required_List('OKE_DTS_REQ_SHIPFROM');
363 END IF;
364
365 IF P_Expected_Date IS NULL THEN
366 Add_Attr_To_Required_List('OKE_DTS_REQ_DATE');
367 ELSIF P_Expected_Date < Trunc( SYSDATE ) THEN
368 -- l_msg := 'The date is past due';
369 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_EXP_DATE_PAST_WSH' );
370 L_Msg := FND_MESSAGE.Get;
371 Add_Msg ( P_Action_ID, L_Msg );
372 l_ret := G_NO;
373 END IF;
374
375 IF NOT Check_Pjm ( P_Action_ID, L_Org_ID, L_Project_ID ) THEN
376 -- l_msg := 'PJM setup is required';
377 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_NO_PJM_SETUP');
378 L_Msg := FND_MESSAGE.Get;
379 Add_Msg ( P_Action_ID, L_Msg );
380 l_ret := G_NO;
381 END IF;
382
383 IF L_req_list IS NOT NULL THEN
384 -- l_msg := 'The following data are required: ' || L_req_list;
385 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_MISS_REQ_DATA');
386 FND_MESSAGE.Set_Token ( 'DATA', L_req_list );
387 L_Msg := FND_MESSAGE.Get;
388 Add_Msg ( P_Action_ID, L_Msg );
389 l_ret := G_NO;
390 END IF;
391
392 RETURN l_ret;
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 Add_Msg ( P_Action_ID, sqlerrm );
397 RETURN G_NO;
398
399 END Validate_Wsh;
400
401 FUNCTION Validate_Req ( P_Action_ID NUMBER
402 , P_Deliverable_ID NUMBER
403 , P_Task_ID NUMBER
404 , P_Ship_From_Org_ID NUMBER
405 , P_Ship_From_Location_ID NUMBER
406 , P_Ship_To_Org_ID NUMBER
407 , P_Ship_To_Location_ID NUMBER
408 , P_Expected_Date DATE
409 , P_Destination_Type_Code VARCHAR2
410 , P_Requisition_Line_Type_ID NUMBER
411 , P_Category_ID NUMBER
412 , P_Currency_Code VARCHAR2
413 , P_Quantity NUMBER
414 , P_UOM_Code VARCHAR2
415 , P_Unit_Price NUMBER
416 , P_Rate_Type VARCHAR2
417 , P_Rate_Date DATE
418 , P_Exchange_Rate NUMBER
419 , P_Expenditure_Type_Code VARCHAR2
420 , P_Expenditure_Organization_Id NUMBER
421 , P_Expenditure_Item_Date DATE )
422 RETURN VARCHAR2 IS
423
424 L_Item_ID NUMBER;
425 L_Org_ID NUMBER;
426 L_Qty NUMBER;
427 l_Dummy VARCHAR2(1);
428 l_ret VARCHAR2(1) := G_YES;
429 L_Uom_Code VARCHAR2(30);
430 L_Msg VARCHAR2(2000);
431 L_Desc VARCHAR2(2000);
432 Item_Based BOOLEAN := TRUE;
433 L_ID NUMBER;
434 L_Error_Code VARCHAR2(2000);
435 L_Currency_Code VARCHAR2(30);
436 L_Project_ID NUMBER;
437 L_Task_ID NUMBER;
438
439 -- PATC variables
440 l_msg_application VARCHAR2(5);
441 l_msg_type VARCHAR2(1);
442 l_msg_token1 VARCHAR2(200);
443 l_msg_token2 VARCHAR2(200);
444 l_msg_token3 VARCHAR2(200);
445 l_msg_count NUMBER;
446 l_billable_flag VARCHAR2(1);
447
448 CURSOR c1 IS
449 SELECT b.item_id ,
450 nvl(b.inventory_org_id,act.ship_to_org_id) org_id ,
451 b.project_id ,
452 b.source_deliverable_id, 'x'
453 FROM oke_deliverables_b b , oke_deliverable_actions act
454 WHERE b.deliverable_id = P_Deliverable_ID
455 and b.deliverable_id = act.Deliverable_ID
456 and act.action_id = p_action_id
457 AND b.source_code = 'PA' ;
458
459 CURSOR c2 IS
460 SELECT 'x'
461 FROM oke_system_items_v
462 WHERE id1 = l_item_id
463 AND organization_id = l_org_id
464 AND NVL(purchasing_enabled_flag, 'N') = 'Y';
465
466 cursor pi is
467 select 'OKE_DTS_EXP_PROJECT_INVALID'
468 from dual
469 where not exists (
470 select 'Project is valid'
471 from pa_projects_expend_v
472 where project_id = l_project_id )
473 union all
474 select 'OKE_PROJECT_NOT_SETUP'
475 from dual
476 where not exists (
477 select 'Project valid for PJM'
478 from pjm_project_parameters
479 where organization_id = l_org_id
480 and project_id = l_project_id )
481 /* union all
482 select 'OKE_DTS_EXP_TASK_INVALID'
483 from dual
484 where not exists (
485 select 'Task valid and chargeable'
486 from pa_tasks_expend_v t
487 where project_id = l_project_id
488 and task_id = l_task_id
489 and chargeable_flag = 'Y' ) */
490 union all
491 select 'OKE_TASK_REQUIRED'
492 from dual
493 where not exists (
494 select 'Task Reference OK'
495 from pjm_org_parameters
496 where organization_id = l_org_id
497 and not ( project_control_level = 2
498 and l_task_id is null )
499 );
500
501 cursor pe is
502 select 'OKE_DTS_EXP_PROJECT_INVALID'
503 from dual
504 where not exists (
505 select 'Project is valid'
506 from pa_projects_expend_v
507 where project_id = l_project_id )
508 union all
509 select 'OKE_DTS_EXP_TASK_INVALID'
510 from dual
511 where not exists (
512 select 'Task is valid and chargeable'
513 from pa_tasks_expend_v
514 where project_id = l_project_id
515 and task_id = l_task_id
516 and chargeable_flag = 'Y' )
517 union all
518 select 'OKE_DTS_EXP_DATE_INVALID'
519 from dual
520 where not exists (
521 select 'Date is valid for task'
522 from pa_tasks t
523 , pa_projects_all p
524 where t.project_id = l_project_id
525 and t.task_id = l_task_id
526 and p.project_id = t.project_id
527 and p_expenditure_item_date
528 between nvl(t.start_date , nvl(p.start_date , p_expenditure_item_date - 1))
529 and nvl(t.completion_date , nvl(p.completion_date , p_expenditure_item_date + 1)) )
530 union all
531 select 'OKE_DTS_EXP_TYPE_INVALID'
532 from dual
533 where not exists (
534 select 'Expenditure Type exists and is valid'
535 from pa_expenditure_types_expend_v
536 where expenditure_type = p_expenditure_type_code
537 and system_linkage_function = 'VI' )
538 union all
539 select 'OKE_DTS_EXP_ORG_INVALID'
540 from dual
541 where not exists (
542 select 'Expenditure Org exists and is valid'
543 from pa_organizations_expend_v
544 where organization_id = p_expenditure_organization_id );
545
546 CURSOR c3 ( P_ID NUMBER ) IS
547 SELECT 'x'
548 FROM gl_sets_of_books gl, org_organization_definitions org
549 WHERE org.organization_id = P_ID
550 AND gl.Set_Of_Books_ID = org.Set_Of_Books_ID
551 AND gl.currency_code = p_currency_code;
552
553 BEGIN
554
555 FND_MSG_PUB.Initialize;
556 L_req_list := NULL;
557
558 l_dummy := NULL;
559 OPEN c1;
560 FETCH c1 INTO L_Item_ID, L_Org_ID, L_Project_ID, L_ID, l_dummy;
561 CLOSE c1;
562
563 IF l_dummy IS NULL THEN
564 FND_MESSAGE.Set_Name ( 'OKE', 'OKE_SAVE_BEFORE_PROCEED' );
565 FND_MSG_PUB.Add;
566 RETURN G_NO;
567 END IF;
568
569 IF P_Destination_Type_Code IS NULL THEN
570 Add_Attr_To_Required_List('OKE_DTS_DEST_TYPE');
571 END IF;
572
573 IF P_Task_ID IS NULL THEN
574 Add_Attr_To_Required_List('OKE_DTS_REQ_TASK');
575 END IF;
576
577 IF P_Uom_Code IS NULL THEN
578 Add_Attr_To_Required_List('OKE_DTS_REQ_UOM');
579 END IF;
580
581 Item_Based := PA_DELIVERABLE_UTILS.Is_Dlvr_Item_Based ( p_deliverable_id => L_ID ) = 'Y';
582 IF Item_Based THEN
583
584 IF L_Item_ID IS NULL THEN
585 Add_Attr_To_Required_List('OKE_DTS_REQ_ITEM');
586 ELSE
587 l_dummy := NULL;
588 OPEN c2;
589 FETCH c2 INTO l_dummy;
590 CLOSE c2;
591 IF l_dummy IS NULL THEN
592 -- l_msg := 'Item not purchasable';
593 FND_MESSAGE.Set_Name ( 'OKE', 'OKE_DTS_ITEM_NOPO');
594 L_Msg := FND_MESSAGE.Get;
595 Add_Msg ( P_Action_ID, L_Msg );
596 l_ret := G_NO;
597 END IF;
598
599 END IF;
600
601 IF NVL ( L_Org_ID, P_Ship_From_Org_ID ) IS NULL THEN
602 Add_Attr_To_Required_List('OKE_DTS_REQ_ORG');
603 END IF;
604
605 ELSE -- if Non Item Based
606
607 IF P_Requisition_Line_Type_ID IS NULL THEN
608 Add_Attr_To_Required_List('OKE_DTS_REQ_LINE_TYPE');
609 END IF;
610
611 IF P_Category_ID IS NULL THEN
612 Add_Attr_To_Required_List('OKE_DTS_REQ_CATEGORY');
613 END IF;
614
615 L_Desc := PA_DELIVERABLE_UTILS.Get_Deliverable_Description ( p_deliverable_id => L_ID );
616 IF L_Desc IS NULL THEN
617 Add_Attr_To_Required_List('OKE_DTS_REQ_DESC');
618 END IF;
619
620 END IF;
621
622 IF P_Quantity IS NULL THEN
623 Add_Attr_To_Required_List('OKE_DTS_REQ_QTY');
624 END IF;
625
626 IF P_Ship_To_Location_ID IS NULL THEN
627 Add_Attr_To_Required_List('OKE_DTS_REQ_RECLOC');
628 END IF;
629
630 IF P_Expected_Date IS NULL THEN
631 Add_Attr_To_Required_List('OKE_DTS_REQ_DATE');
632 ELSIF P_Expected_Date < Trunc( SYSDATE ) THEN
633 -- l_msg := 'The date is past due';
634 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_EXP_DATE_PAST_REQ' );
635 L_Msg := FND_MESSAGE.Get;
636 Add_Msg ( P_Action_ID, L_Msg );
637 l_ret := G_NO;
638 END IF;
639
640 IF P_Destination_Type_Code = 'EXPENSE' AND L_Item_ID > 0 THEN
641 IF P_Expenditure_Organization_ID IS NULL THEN
642 Add_Attr_To_Required_List('OKE_DTS_REQ_EXP_ORG');
643 END IF;
644 IF P_Expenditure_Item_Date IS NULL THEN
645 Add_Attr_To_Required_List('OKE_DTS_REQ_EXP_DATE');
646 END IF;
647 IF P_Expenditure_Type_Code IS NULL THEN
648 Add_Attr_To_Required_List('OKE_DTS_REQ_EXP_TYPE');
649 END IF;
650 END IF;
651
652 l_dummy := null;
653 OPEN c3 ( L_Org_ID );
654 FETCH c3 INTO l_dummy;
655 CLOSE c3;
656
657 IF l_dummy IS NULL THEN
658 IF P_Exchange_Rate IS NULL THEN
659 Add_Attr_To_Required_List('OKE_DTS_REQ_RATE');
660 END IF;
661 END IF;
662
663 IF L_req_list IS NOT NULL THEN
664 -- l_msg := 'The following data are required: ' || L_req_list;
665 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_MISS_REQ_DATA');
666 FND_MESSAGE.Set_Token ( 'DATA', L_req_list );
667 L_Msg := FND_MESSAGE.Get;
668 Add_Msg ( P_Action_ID, L_Msg );
669 l_ret := G_NO;
670 END IF;
671
672 L_Task_ID := P_Task_ID;
673 IF ( P_Destination_Type_Code = 'INVENTORY' ) then
674
675 l_error_code := NULL;
676 open pi;
677 loop
678 fetch pi into l_error_code;
679 exit when pi%notfound;
680 FND_MESSAGE.Set_Name('OKE' , l_error_code);
681 FND_MSG_PUB.Add;
682 end loop;
683 close pi;
684 if ( l_error_code is not null ) then
685 l_ret := G_NO;
686 end if;
687
688 IF NOT Check_Pjm ( P_Action_ID, L_Org_ID, L_Project_ID ) THEN
689 -- l_msg := 'PJM setup is required';
690 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_NO_PJM_SETUP');
691 L_Msg := FND_MESSAGE.Get;
692 Add_Msg ( P_Action_ID, L_Msg );
693 l_ret := G_NO;
694 END IF;
695
696 ELSE -- Destination_Type_Code = 'EXPENSE'
697
698 l_error_code := NULL;
699 open pe;
700 loop
701 fetch pe into l_error_code;
702 exit when pe%notfound;
703 FND_MESSAGE.Set_Name('OKE' , l_error_code);
704 L_Msg := FND_MESSAGE.Get;
705 Add_Msg ( P_Action_ID, L_Msg );
706 end loop;
707 close pe;
708 if ( l_error_code is not null ) then
709 l_ret := G_NO;
710 end if;
711
712 END IF;
713
714 IF l_ret = G_YES AND P_Destination_Type_Code = 'EXPENSE' THEN
715 PATC.get_status(
716 X_project_id => L_Project_ID
717 , X_task_id => P_Task_ID
718 , X_ei_date => p_expenditure_item_date
719 , X_expenditure_type => P_Expenditure_Type_Code
720 , X_non_labor_resource => NULL --X_non_labor_resource --?
721 , X_person_id => NULL --X_person_id --?
722 , X_quantity => P_Quantity
723 , X_denom_currency_code => P_Currency_Code
724 -- , X_acct_currency_code => X_acct_currency_code --?
725 , X_denom_raw_cost => P_Unit_Price
726 -- , X_acct_raw_cost => X_acct_raw_cost --?
727 , X_acct_rate_type => P_Rate_Type
728 , X_acct_rate_date => P_RATE_DATE
729 , X_acct_exchange_rate =>P_EXCHANGE_RATE
730 -- , X_transfer_ei => X_transfer_ei
731 , X_incurred_by_org_id => P_Expenditure_Organization_ID
732 , X_msg_application => l_msg_application
733 , X_msg_type => l_msg_type
734 , X_msg_token1 => l_msg_token1
735 , X_msg_token2 => l_msg_token2
736 , X_msg_token3 => l_msg_token3
737 , X_msg_count => l_msg_count
738 , X_status => l_msg
739 , X_billable_flag => l_billable_flag
740 );
741
742 IF l_msg IS NOT NULL THEN
743 FND_MESSAGE.SET_NAME(l_Msg_Application, l_msg);
744 IF l_Msg_Token1 IS NOT NULL THEN
745 FND_MESSAGE.SET_TOKEN('patc_msg_token1', l_Msg_Token1);
746 END IF;
747 IF l_Msg_Token2 IS NOT NULL THEN
748 FND_MESSAGE.SET_TOKEN('patc_msg_token2', l_Msg_Token2);
749 END IF;
750 IF l_Msg_Token3 IS NOT NULL THEN
751 FND_MESSAGE.SET_TOKEN('patc_msg_token3', l_Msg_Token3);
752 END IF;
753 FND_MSG_PUB.Add;
754 l_ret := G_NO;
755 END IF;
756 END IF;
757
758 RETURN l_ret;
759
760 EXCEPTION
761 WHEN OTHERS THEN
762 Add_Msg ( P_Action_ID, sqlerrm );
763 RETURN G_NO;
764
765 END Validate_Req;
766
767 FUNCTION Exchange_Rate ( P_Orig_Code VARCHAR2
768 , P_Target_Code VARCHAR2
769 , P_Rate_Type VARCHAR2
770 , P_Date DATE ) RETURN NUMBER IS
771 ConvAmt number;
772 Numerator number;
773 Denom number;
774 Rate number;
775 Func_Code varchar2(30);
776 Unit_Price number := 0;
777
778 BEGIN
779 IF P_Orig_Code <> P_Target_Code THEN
780 GL_CURRENCY_API.Convert_Amount
781 ( X_From_Currency => P_Orig_Code
782 , X_To_Currency => P_Target_Code
783 , X_Conversion_Date => P_Date
784 , X_Conversion_Type => P_Rate_Type
785 , X_Amount => Unit_Price
786 , X_Converted_Amount => ConvAmt
787 , X_Denominator => Denom
788 , X_Numerator => Numerator
789 , X_Rate => Rate
790 );
791 RETURN Rate;
792 ELSE
793 RETURN NULL;
794 END IF;
795
796 EXCEPTION
797 WHEN OTHERS THEN
798 RETURN NULL;
799 END Exchange_Rate;
800
801 FUNCTION Functional_Currency ( P_Org_ID NUMBER ) RETURN VARCHAR2 IS
802
803 L_Currency_Code VARCHAR2(30);
804 CURSOR c IS
805 SELECT gl.Currency_Code
806 FROM gl_sets_of_books gl, org_organization_definitions org
807 WHERE org.ORGANIZATION_ID = P_Org_ID
808 AND gl.Set_Of_Books_ID = org.Set_Of_Books_ID;
809
810 BEGIN
811
812 OPEN c;
813 FETCH c INTO L_Currency_Code;
814 CLOSE c;
815
816 RETURN L_Currency_Code;
817
818 EXCEPTION
819 WHEN OTHERS THEN
820 RETURN NULL;
821 END Functional_Currency;
822
823 FUNCTION Get_Location_Description ( P_ID NUMBER ) RETURN VARCHAR2 IS
824 l_return_val VARCHAR2(240);
825
826 CURSOR c_hr IS
827 SELECT nvl(description,'')
828 FROM hr_locations_all
829 WHERE location_id=p_id;
830
831 CURSOR c_hz IS
832 SELECT substr(address1,1,240)
833 FROM hz_locations
834 WHERE location_id=p_id;
835
836 CURSOR c_both IS
837 SELECT description
838 FROM hr_locations
839 WHERE location_id=p_id;
840
841 BEGIN
842
843 OPEN c_hr;
844 FETCH c_hr INTO l_return_val;
845 IF c_hr%NOTFOUND THEN
846 OPEN c_hz;
847 FETCH c_hz INTO l_return_val;
848 IF c_hz%NOTFOUND THEN
849 l_return_val:='ERROR-NO SUCH LOCATION_ID';
850 END IF;
851 CLOSE c_hz;
852 END IF;
853 CLOSE c_hr;
854 RETURN l_return_val;
855
856 END;
857
858 END;
859