[Home] [Help]
PACKAGE BODY: APPS.OKE_DELIVERABLE_ACTIONS_PKG
Source
1 PACKAGE BODY OKE_DELIVERABLE_ACTIONS_PKG AS
2 /* $Header: OKEVDACB.pls 120.2 2005/06/27 14:40:33 ausmani noship $ */
3
4 G_Pkg_Name CONSTANT VARCHAR2(30) := 'OKE_DTS_INTERGRATION';
5
6 FUNCTION Charge_Account ( P_Item_ID NUMBER, P_Org_ID NUMBER) RETURN NUMBER;
7 FUNCTION Uom_Conversion
8 ( P_Item_Id NUMBER
9 , P_From_Uom VARCHAR2
10 , P_To_Uom VARCHAR2
11 , P_Quantity NUMBER
12 ) RETURN NUMBER;
13
14
15 FUNCTION Uom_Conversion
16 ( P_Item_Id NUMBER
17 , P_From_Uom VARCHAR2
18 , P_To_Uom VARCHAR2
19 , P_Quantity NUMBER
20 ) RETURN NUMBER IS
21
22 L_Quantity NUMBER;
23
24 BEGIN
25
26 IF P_From_UOM = P_To_UOM THEN
27 L_Quantity := P_Quantity;
28 ELSIF P_From_Uom IS NULL OR P_To_Uom IS NULL THEN
29 L_Quantity := 0;
30 ELSIF P_From_Uom IS NULL AND P_To_Uom IS NULL THEN
31 L_Quantity := P_Quantity;
32 ELSE
33 L_Quantity := INV_CONVERT.Inv_Um_Convert
34 ( P_Item_ID
35 , 5 -- precision, can be changed later
36 , P_Quantity
37 , P_From_Uom
38 , P_To_Uom
39 , Null
40 , Null);
41 END IF;
42
43
44 IF L_Quantity = -9999 THEN
45 L_Quantity := 0;
46 END IF;
47
48 RETURN L_Quantity;
49
50 END UOM_Conversion;
51
52 FUNCTION Check_Item_Unique ( P_Action_ID NUMBER )
53 RETURN BOOLEAN IS
54
55 Dummy NUMBER;
56 L_Item_ID NUMBER;
57 L_Org_ID NUMBER;
58 L_Designator VARCHAR2(30);
59
60 BEGIN
61
62 SELECT 1
63 INTO Dummy
64 FROM dual
65 WHERE NOT EXISTS (
66 SELECT 1
67 FROM mrp_schedule_items mrp
68 , oke_deliverables_b oke
69 , oke_deliverable_actions oka
70 WHERE oka.action_id = p_action_id
71 AND oke.deliverable_id = oka.deliverable_id
72 AND mrp.inventory_item_id = oke.item_id
73 AND mrp.schedule_designator = oka.schedule_designator
74 AND mrp.organization_id = oka.ship_from_org_id);
75
76 RETURN ( TRUE );
77 RETURN NULL;
78
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN
81 RETURN ( FALSE );
82 END Check_Item_Unique;
83
84 PROCEDURE Delete_Row ( P_Action_ID NUMBER ) IS
85 L_ID NUMBER;
86
87 CURSOR c IS
88 SELECT reference2
89 FROM oke_deliverable_actions
90 WHERE action_id = p_action_id;
91
92 BEGIN
93 OPEN c;
94 FETCH c INTO l_id;
95 CLOSE c;
96
97 IF l_id IS NOT NULL THEN
98 DELETE FROM mrp_schedule_dates
99 WHERE mps_transaction_id = l_id;
100 END IF;
101
102 EXCEPTION
103 WHEN OTHERS THEN
104 RAISE;
105 END Delete_Row;
106
107 PROCEDURE Insert_Row ( P_Action_ID NUMBER
108 , X_Mps_Transaction_ID OUT NOCOPY NUMBER ) IS
109
110 L_ID NUMBER;
111 L_Workdate DATE;
112 L_Schedule_Level CONSTANT NUMBER := 2;
113 L_Supply_Demand_Type CONSTANT NUMBER := 1;
114 L_Schedule_Origination_Type CONSTANT NUMBER := 1;
115 L_Level NUMBER;
116 L_To_UOM VARCHAR2(3);
117 L_Primary_Qty NUMBER;
118
119 CURSOR c1 ( P_Transaction_ID NUMBER ) IS
120 SELECT rowid
121 FROM mrp_schedule_dates
122 WHERE mps_transaction_id = p_transaction_id
123 AND schedule_level = l_schedule_level
124 AND supply_demand_type = l_supply_demand_type;
125
126 c1info c1%rowtype;
127
128 CURSOR c2 ( P_Organization_ID NUMBER ) IS
129 SELECT Maximum_BOM_Level
130 FROM bom_parameters
131 WHERE organization_id = p_organization_id;
132
133 CURSOR C3 IS
134 SELECT b.item_id
135 , c.ship_from_org_id
136 , c.schedule_designator
137 , c.expected_date
138 , b.uom_code
139 , b.quantity
140 , b.project_id
141 , c.task_id
142 , b.unit_number
143 , c.deliverable_id
144 FROM oke_deliverables_b b
145 , oke_deliverable_actions c
146 WHERE c.action_id = p_action_id
147 AND b.deliverable_id = c.deliverable_id;
148
149 c3info c3%rowtype;
150
151 CURSOR c4 ( P_Item_ID NUMBER, P_Org_ID NUMBER ) IS
152 SELECT primary_uom_code
153 FROM mtl_system_items
154 WHERE inventory_item_id = p_item_id
155 AND organization_id = p_org_id;
156
157 BEGIN
158
159 -- Get ID
160 SELECT mrp_schedule_dates_s.nextval
161 INTO l_id
162 FROM dual;
163
164 IF Check_Item_Unique ( P_Action_ID ) THEN
165 INSERT INTO mrp_schedule_items (
166 inventory_item_id
167 , organization_id
168 , schedule_designator
169 , last_update_date
170 , last_updated_by
171 , creation_date
172 , created_by
173 , last_update_login
174 , mps_explosion_level )
175 SELECT b.item_id
176 , c.ship_from_org_id
177 , c.schedule_designator
178 , sysdate
179 , fnd_global.user_id
180 , sysdate
181 , fnd_global.user_id
182 , fnd_global.login_id
183 , d.maximum_bom_level
184 FROM oke_deliverables_b b
185 , oke_deliverable_actions c
186 , bom_parameters d
187 WHERE c.action_id = p_action_id
188 AND c.deliverable_id = b.deliverable_id
189 AND c.ship_from_org_id = d.organization_id;
190 END IF;
191
192
193 OPEN c3;
194 FETCH c3 INTO c3info;
195 CLOSE c3;
196
197 L_Workdate := MRP_CALENDAR.Prev_Work_Day ( c3info.ship_from_org_id
198 , 1
199 , c3info.expected_date );
200 OPEN c4 ( c3info.item_id, c3info.ship_from_org_id);
201 FETCH c4 INTO L_To_Uom;
202 CLOSE c4;
203
204 L_Primary_Qty := Uom_Conversion ( c3info.item_id
205 , c3info.uom_code
206 , l_to_uom
207 , c3info.quantity );
208
209 INSERT INTO MRP_SCHEDULE_DATES(
210 MPS_TRANSACTION_ID,
211 SCHEDULE_LEVEL,
212 SUPPLY_DEMAND_TYPE,
213 LAST_UPDATE_DATE,
214 LAST_UPDATED_BY,
215 CREATION_DATE,
216 CREATED_BY,
217 LAST_UPDATE_LOGIN,
218 INVENTORY_ITEM_ID,
219 ORGANIZATION_ID,
220 SCHEDULE_DESIGNATOR,
221 SCHEDULE_DATE,
222 SCHEDULE_WORKDATE,
223 SCHEDULE_QUANTITY,
224 ORIGINAL_SCHEDULE_QUANTITY,
225 SCHEDULE_ORIGINATION_TYPE,
226 PROJECT_ID,
227 TASK_ID,
228 END_ITEM_UNIT_NUMBER,
229 SOURCE_CODE,
230 SOURCE_LINE_ID)
231 SELECT
232 L_Id ,
233 lu.lookup_code ,
234 L_Supply_Demand_Type ,
235 Sysdate ,
236 Fnd_Global.User_Id ,
237 Sysdate ,
238 Fnd_Global.User_Id ,
239 Fnd_Global.Login_Id ,
240 c3info.Item_Id ,
241 c3info.Ship_From_Org_Id ,
242 c3info.Schedule_Designator ,
243 c3info.Expected_Date ,
244 l_workdate ,
245 L_Primary_Qty ,
246 L_Primary_Qty ,
247 l_schedule_origination_type,
248 c3info.Project_Id ,
249 c3info.Task_Id ,
250 c3info.Unit_Number ,
251 'OKE' ,
252 c3info.deliverable_id
253 FROM mfg_lookups lu
254 WHERE lookup_type = 'MRP_SCHEDULE_LEVEL'
255 AND lookup_code in ( 1 , 2)
256 AND NOT EXISTS (
257 SELECT NULL
258 FROM mrp_schedule_dates
259 WHERE mps_transaction_id = L_Id
260 AND schedule_level = lu.lookup_code );
261
262 OPEN c1 (l_id);
263 FETCH c1 INTO c1info;
264 IF (c1%NOTFOUND) THEN
265
266 CLOSE c1;
267 RAISE NO_DATA_FOUND;
268
269 END IF;
270
271 CLOSE c1;
272 x_mps_transaction_id := l_id;
273
274 END insert_row;
275
276 PROCEDURE update_row ( X_Mps_Transaction_Id IN OUT NOCOPY NUMBER
277 , P_Action_Id IN NUMBER
278 ) IS
279
280 CURSOR c IS
281 SELECT b.item_id
282 , c.ship_from_org_id
283 , c.schedule_designator
284 , c.expected_date
285 , b.quantity
286 , b.project_id
287 , c.task_id
288 , b.unit_number
289 , b.uom_code
290 , mrp_calendar.prev_work_day(c.ship_from_org_id
291 , 1
292 , c.expected_date) workdate
293 , d.primary_uom_code
294 FROM oke_deliverables_b b
295 , oke_deliverable_actions c
296 , mtl_system_items d
297 WHERE c.action_id = p_action_id
298 AND b.deliverable_id = c.deliverable_id
299 AND d.inventory_item_id = b.item_id
300 AND d.organization_id = c.ship_from_org_id;
301
302 cinfo c%rowtype;
303 L_Primary_Qty NUMBER;
304
305 BEGIN
306
307 OPEN c;
308 FETCH c INTO cinfo;
309 CLOSE c;
310
311 L_Primary_Qty := uom_conversion( cinfo.item_id
312 , cinfo.uom_code
313 , cinfo.primary_uom_code
314 , cinfo.quantity );
315
316 UPDATE MRP_SCHEDULE_DATES
317 SET
318 LAST_UPDATE_DATE = Sysdate,
319 LAST_UPDATED_BY = Fnd_Global.User_Id,
320 LAST_UPDATE_LOGIN = Fnd_Global.Login_Id,
321 INVENTORY_ITEM_ID = cinfo.Item_Id,
322 ORGANIZATION_ID = cinfo.Ship_From_Org_Id,
323 SCHEDULE_DESIGNATOR = cinfo.Schedule_Designator,
324 SCHEDULE_DATE = cinfo.Expected_Date,
325 SCHEDULE_WORKDATE = cinfo.Workdate,
326 SCHEDULE_QUANTITY = ( select greatest(L_Primary_Qty - nvl(sum(ref.relief_quantity) , 0) , 0)
327 from mrp_schedule_consumptions ref
328 where ref.transaction_id = x_mps_transaction_id ),
329 PROJECT_ID = cinfo.Project_Id,
330 TASK_ID = cinfo.Task_Id,
331 END_ITEM_UNIT_NUMBER = cinfo.Unit_Number
332 WHERE MPS_TRANSACTION_ID = X_MPS_TRANSACTION_ID
333 AND SCHEDULE_LEVEL = 2
334 AND SUPPLY_DEMAND_TYPE = 1;
335
336 IF ( sql%notfound ) THEN
337
338 insert_row( P_Action_ID => p_action_id
339 , X_MPS_Transaction_ID => x_mps_transaction_id );
340
341 END IF;
342
343 END update_row;
344
345 PROCEDURE Create_Demand ( P_Action_ID NUMBER
346 , P_Init_Msg_List VARCHAR2
347 , X_ID OUT NOCOPY NUMBER
348 , X_Return_Status OUT NOCOPY VARCHAR2
349 , X_Msg_Count OUT NOCOPY NUMBER
350 , X_Msg_Data OUT NOCOPY VARCHAR2 ) IS
351
352 L_Return_Status VARCHAR2(1) := OKE_API.G_Ret_Sts_Success;
353 L_API_Version CONSTANT NUMBER := 1;
354 L_API_Name CONSTANT VARCHAR2(30) := 'CREATE_DEMAND';
355 L_Quantity NUMBER;
356 L_ID NUMBER;
357
358 CURSOR c1 IS
359 SELECT reference2
360 FROM oke_deliverable_actions
361 WHERE action_id = p_action_id
362 FOR UPDATE NOWAIT;
363
364 CURSOR c2 IS
365 SELECT schedule_quantity
366 FROM mrp_schedule_dates
367 WHERE mps_transaction_id = l_id;
368
369 BEGIN
370
371 L_Return_Status := OKE_API.Start_Activity (
372 L_API_Name
373 , P_Init_Msg_List
374 , '_PKG'
375 , X_Return_Status );
376 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
377 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
378 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
379 RAISE OKE_API.G_EXCEPTION_ERROR;
380 END IF;
381
382 OPEN c1;
383 FETCH c1 INTO L_ID;
384 CLOSE c1;
385
386 IF L_ID IS NULL THEN
387 Insert_Row ( P_Action_ID => P_Action_ID
388 , X_Mps_Transaction_ID => L_ID );
389 ELSE
390 OPEN c2;
391 FETCH c2 INTO L_Quantity;
392 CLOSE c2;
393
394 IF L_Quantity <> 0 OR L_Quantity IS NULL THEN
395 Update_Row ( P_Action_ID => P_Action_ID
396 , X_Mps_Transaction_ID => L_ID );
397 END IF;
398
399 END IF;
400
401 IF L_ID IS NOT NULL THEN
402 X_ID := L_ID;
403
404 UPDATE oke_deliverable_actions
405 SET reference2 = L_ID
406 WHERE action_id = p_action_id
407 AND action_type = 'WSH';
408 END IF;
409
410 X_Return_Status := L_Return_Status;
411 -- Commit;
412 OKE_API.End_Activity ( X_Msg_Count, X_Msg_Data );
413
414 EXCEPTION
415 WHEN OKE_API.G_EXCEPTION_ERROR THEN
416
417 x_return_status := OKE_API.HANDLE_EXCEPTIONS
418 (
419 l_api_name,
420 G_PKG_NAME,
421 'OKE_API.G_RET_STS_ERROR',
422 x_msg_count,
423 x_msg_data,
424 '_PKG');
425 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
426 x_return_status := OKE_API.HANDLE_EXCEPTIONS
427 (
428 l_api_name,
429 G_PKG_NAME,
430 'OKE_API.G_RET_STS_UNEXP_ERROR',
431 x_msg_count,
432 x_msg_data,
433 '_PKG');
434
435 WHEN OTHERS THEN
436 x_return_status := OKE_API.HANDLE_EXCEPTIONS
437 (
438 l_api_name,
439 G_PKG_NAME,
440 'OTHERS',
441 x_msg_count,
442 x_msg_data,
443 '_PKG');
444
445
446 END Create_Demand;
447
448 PROCEDURE Create_Shipment ( P_Action_ID NUMBER
449 , P_Init_Msg_List VARCHAR2
450 , X_ID OUT NOCOPY NUMBER
451 , X_Return_Status OUT NOCOPY VARCHAR2
452 , X_Msg_Count OUT NOCOPY NUMBER
453 , X_Msg_Data OUT NOCOPY VARCHAR2 ) IS
454 L_Return_Status VARCHAR2(1) := OKE_API.G_Ret_Sts_Success;
455 L_API_Version CONSTANT NUMBER := 1;
456 L_API_Name CONSTANT VARCHAR2(30) := 'CREATE_SHIPMENT';
457 L_Quantity NUMBER;
458 L_ID NUMBER;
459 L_OU_ID NUMBER;
460 L_Ship_Rec wsh_delivery_details_pkg.delivery_details_rec_type;
461 L_Flag VARCHAR2(1);
462 L_Description VARCHAR2(250);
463 L_Header_Number VARCHAR2(150);
464 L_Action_Name VARCHAR2(240);
465 L_Action_Number VARCHAR2(150);
466 L_Deliverable_Name VARCHAR2(150);
467 L_Deliverable_Number VARCHAR2(150);
468 L_Project_ID NUMBER;
469
470 CURSOR c1 IS
471 SELECT b.source_header_id
472 , b.source_deliverable_id
473 , b.deliverable_id
474 , b.project_id
475 , b.item_id
476 , nvl(b.quantity, c.quantity) quantity
477 , nvl(b.uom_code, c.uom_code) uom_code
478 , b.inventory_org_id
479 , c.pa_action_id
480 , c.ship_from_org_id
481 , c.ship_to_org_id
482 , c.ship_to_location_id
483 , c.ship_from_location_id
484 , c.expected_date
485 , c.promised_date
486 , b.unit_number
487 , decode ( c.inspection_req_flag, 'Y', 'R', 'N') inspection_req_flag
488 , c.volume
489 , c.volume_uom_code
490 , c.weight
491 , c.weight_uom_code
492 , nvl(b.currency_code, c.currency_code) currency_code
493 , c.task_id
494 FROM oke_deliverables_b b
495 , oke_deliverable_actions c
496 WHERE c.action_id = p_action_id
497 AND b.deliverable_id = c.deliverable_id;
498
499 c1info c1%rowtype;
500
501 CURSOR c2 ( p_org_id NUMBER ) IS
502 SELECT operating_unit
503 FROM org_organization_definitions
504 WHERE organization_id = p_org_id;
505
506 CURSOR c3 ( p_location_id NUMBER ) IS
507 SELECT id1, cust_account_id
508 FROM oke_cust_site_uses_v
509 WHERE location_id = p_location_id
510 AND site_use_code = 'SHIP_TO';
511
512 c3info c3%rowtype;
513
514 CURSOR c4 ( p_org_id number,P_item_ID NUMBER ) IS
515 SELECT MTL_Transactions_Enabled_Flag
516 FROM mtl_system_items
517 where organization_id = p_org_id
518 and inventory_item_id = p_item_id;
519
520 BEGIN
521
522 L_Return_Status := OKE_API.Start_Activity (
523 L_API_Name
524 , P_Init_Msg_List
525 , '_PKG'
526 , X_Return_Status );
527 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
528 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
529 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
530 RAISE OKE_API.G_EXCEPTION_ERROR;
531 END IF;
532
533 OPEN c1;
534 FETCH c1 INTO c1info;
535 CLOSE c1;
536
537 OPEN c2(c1info.ship_from_org_id);
538 FETCH c2 INTO L_OU_ID;
539 CLOSE c2;
540
541 OPEN c3 ( c1info.ship_to_location_id );
542 FETCH c3 INTO c3info;
543 CLOSE c3;
544
545 OPEN c4 ( c1info.ship_from_org_id ,c1info.item_id);
546 FETCH c4 INTO L_Flag;
547 CLOSE c4;
548
549 -- Get deliverable description from PA
550 L_Description := substr(PA_DELIVERABLE_UTILS.Get_Dlv_Description ( p_action_ver_id => c1info.pa_action_id ), 1, 250);
551 PA_DELIVERABLE_UTILS.Get_Action_Project_Detail ( p_dlvr_action_ver_id => c1info.pa_action_id
552 , x_project_id => l_project_id
553 , x_project_name => l_header_number );
554 PA_DELIVERABLE_UTILS.Get_Action_Detail ( p_dlvr_action_ver_id => c1info.pa_action_id
555 , x_name => l_action_name
556 , x_number => l_action_number );
557 PA_DELIVERABLE_UTILS.Get_Dlvr_Detail ( p_dlvr_ver_id => c1info.source_deliverable_id
558 , x_name => l_deliverable_name
559 , x_number => l_deliverable_number );
560 --
561 -- Leave 25 for deliverable short name if over 150 after concat
562 --
563 IF Length ( L_Deliverable_Name || ' : ' || L_Action_Name ) > 150 THEN
564 IF Length ( L_Deliverable_Name ) > 25 THEN
565 L_Action_Name := SUBSTR ( L_Deliverable_Name, 1, 25 ) || ' : ' || SUBSTR( L_Action_Name, 1, 122 );
566 ELSE
567 L_Action_Name := L_Deliverable_Name || ' : ' || SUBSTR ( L_Action_Name, 1, 147 - Length ( L_Deliverable_Name ) );
568 END IF;
569
570 ELSE
571 L_Action_Name := L_Deliverable_Name || ' : ' || L_Action_Name ;
572 END IF;
573
574
575
576 l_ship_rec.source_code := 'OKE';
577 -- l_ship_rec.source_header_id := c1info.SOURCE_HEADER_ID; --Bug3863976
578 l_ship_rec.source_header_id := -99; -- Bug 3863976
579 l_ship_rec.source_line_id := p_action_id;
580 l_ship_rec.customer_id := c3info.cust_account_id;
581 l_ship_rec.inventory_item_id := c1info.item_id;
582 l_ship_rec.item_description := l_description;
583 l_ship_rec.ship_from_location_id := c1info.ship_from_location_id;
584 l_ship_rec.ship_to_location_id := c1info.ship_to_location_id;
585 l_ship_rec.ship_to_site_use_id := c3info.id1;
586 -- l_ship_rec.requested_quantity := nvl(c1info.quantity, 1);
587 -- l_ship_rec.requested_quantity_uom := nvl(c1info.uom_code, 'EA');
588 l_ship_rec.date_requested := c1info.promised_date;
589 l_ship_rec.date_scheduled := c1info.expected_date;
590 l_ship_rec.net_weight := c1info.weight;
591 l_ship_rec.weight_uom_code := c1info.weight_uom_code;
592 l_ship_rec.volume := c1info.volume;
593 l_ship_rec.volume_uom_code := c1info.volume_uom_code;
594 l_ship_rec.created_by := fnd_global.user_id;
595 l_ship_rec.creation_date := sysdate;
596 l_ship_rec.last_update_date := sysdate;
597 l_ship_rec.last_update_login := fnd_global.login_id;
598 l_ship_rec.last_updated_by := fnd_global.user_id;
599 l_ship_rec.organization_id := c1info.ship_from_org_id;
600 l_ship_rec.source_header_number := l_header_number;
601 l_ship_rec.src_requested_quantity := nvl(c1info.quantity, 1);
602 l_ship_rec.src_requested_quantity_uom := nvl(c1info.uom_code, 'EA');
603 l_ship_rec.project_id := c1info.source_header_id;
604 l_ship_rec.task_id := c1info.task_id;
605 l_ship_rec.org_id := l_ou_id;
606 l_ship_rec.source_line_number := l_action_name;
607 l_ship_rec.inspection_flag := c1info.inspection_req_flag;
608 l_ship_rec.unit_number := c1info.unit_number;
609 l_ship_rec.currency_code := c1info.currency_code;
610 l_ship_rec.pickable_flag := l_flag;
611
612 WSH_INTERFACE_PUB.Create_Shipment_Lines ( L_Ship_Rec
613 , L_ID
614 , L_Return_Status );
615
616
617 IF L_Return_Status = OKE_API.G_Ret_Sts_Success THEN
618 UPDATE oke_deliverable_actions
619 SET reference1 = l_id
620 , in_process_flag = 'Y'
621 , initiate_date = sysdate
622 WHERE action_id = p_action_id;
623
624 X_Return_Status := L_Return_Status;
625 -- Commit;
626 ELSE
627 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
628 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
629 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
630 RAISE OKE_API.G_EXCEPTION_ERROR;
631 END IF;
632 END IF;
633
634 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
635
636 EXCEPTION
637 WHEN OKE_API.G_EXCEPTION_ERROR THEN
638
639 x_return_status := OKE_API.HANDLE_EXCEPTIONS
640 (
641 l_api_name,
642 G_PKG_NAME,
643 'OKE_API.G_RET_STS_ERROR',
644 x_msg_count,
645 x_msg_data,
646 '_PKG');
647 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
648 x_return_status := OKE_API.HANDLE_EXCEPTIONS
649 (
650 l_api_name,
651 G_PKG_NAME,
652 'OKE_API.G_RET_STS_UNEXP_ERROR',
653 x_msg_count,
654 x_msg_data,
655 '_PKG');
656
657 WHEN OTHERS THEN
658 x_return_status := OKE_API.HANDLE_EXCEPTIONS
659 (
660 l_api_name,
661 G_PKG_NAME,
662 'OTHERS',
663 x_msg_count,
664 x_msg_data,
665 '_PKG');
666
667 END Create_Shipment;
668
669 PROCEDURE Create_Requisition ( P_Action_ID NUMBER
670 , P_Init_Msg_List VARCHAR2
671 , X_ID OUT NOCOPY NUMBER
672 , X_Return_Status OUT NOCOPY VARCHAR2
673 , X_Msg_Count OUT NOCOPY NUMBER
674 , X_Msg_Data OUT NOCOPY VARCHAR2 ) IS
675
676 L_Return_Status VARCHAR2(1) := OKE_API.G_Ret_Sts_Success;
677 L_API_Version CONSTANT NUMBER := 1;
678 L_API_Name CONSTANT VARCHAR2(30) := 'CREATE_REQUISITION';
679 L_Quantity NUMBER;
680 L_ID NUMBER;
681 L_Description VARCHAR2(150);
682 L_ID NUMBER;
683 L_Requestor NUMBER;
684 L_Employee NUMBER;
685 L_Context VARCHAR2(1);
686 L_Charge_Account NUMBER;
687
688 CURSOR c1 IS
689 SELECT employee_id
690 FROM fnd_user
691 WHERE user_id = l_requestor;
692
693 CURSOR c2 IS
694 SELECT b.source_header_id
695 , c.task_id
696 , c.destination_type_code
697 , c.expenditure_type
698 , c.expenditure_organization_id
699 , c.expenditure_item_date
700 , nvl(b.inventory_org_id,c.ship_to_org_id) inventory_org_id
701 , trunc ( c.expected_date ) expected_date
702 , c.reference1
703 , c.reference2
704 , c.schedule_designator
705 , b.item_id
706 , nvl(b.unit_price, c.unit_price) unit_price
707 , c.exchange_rate
708 , c.ship_from_org_id
709 , nvl(b.currency_code, c.currency_code) currency_code
710 , c.ship_from_location_id
711 , c.requisition_line_type_id
712 , c.po_category_id
713 , nvl(b.quantity, c.quantity) quantity
714 , nvl(b.uom_code, c.uom_code) uom_code
715 , c.pa_action_id
716 , c.ship_to_location_id
717 , c.deliverable_id
718 , c.action_id
719 , b.unit_number
720 , c.rate_date
721 , c.rate_type
722 FROM oke_deliverables_b b
723 , oke_deliverable_actions c
724 WHERE c.action_id = p_action_id
725 AND b.deliverable_id = c.deliverable_id;
726
727 c2info c2%rowtype;
728
729
730 BEGIN
731 L_Return_Status := OKE_API.Start_Activity (
732 L_API_Name
733 , P_Init_Msg_List
734 , '_PKG'
735 , X_Return_Status );
736 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
737 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
738 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
739 RAISE OKE_API.G_EXCEPTION_ERROR;
740 END IF;
741
742 OPEN c2;
743 FETCH c2 INTO c2info;
744 CLOSE c2;
745
746 L_Charge_Account := Charge_Account ( p_item_id => c2info.item_id
747 , p_org_id => c2info.inventory_org_id );
748
749 L_Requestor := FND_GLOBAL.User_ID;
750
751 OPEN c1;
752 FETCH c1 INTO L_Employee;
753 CLOSE c1;
754
755 L_Context := 'Y';
756 L_Description := substr(PA_DELIVERABLE_UTILS.Get_Dlv_Description ( p_action_ver_id => c2info.pa_action_id ), 1, 240);
757
758 IF c2info.reference1>0 THEN
759 DELETE FROM po_requisitions_interface_all
760 WHERE oke_contract_deliverable_id = c2info.action_id
761 AND batch_id = c2info.reference1;
762 END IF;
763
764 INSERT INTO po_requisitions_interface_all(
765 last_updated_by,
766 last_update_login,
767 last_update_date,
768 creation_date,
769 created_by,
770 item_id,
771 quantity,
772 unit_price,
773 need_by_date,
774 interface_source_code,
775 deliver_to_location_id,
776 deliver_to_requestor_id,
777 preparer_id,
778 source_type_code,
779 authorization_status,
780 uom_code,
781 batch_id,
782 charge_account_id,
783 group_code,
784 destination_organization_id,
785 autosource_flag,
786 org_id,
787 project_id,
788 task_id,
789 project_accounting_context,
790 oke_contract_header_id,
791 oke_contract_version_id,
792 oke_contract_line_id,
793 oke_contract_deliverable_id,
794 end_item_unit_number,
795 expenditure_organization_id,
796 expenditure_type,
797 expenditure_item_date,
798 destination_type_code,
799 currency_code,
800 rate,
801 rate_date,
802 rate_type,
803 currency_unit_price,
804 suggested_vendor_id,
805 suggested_vendor_site_id,
806 line_type_id,
807 category_id,
808 item_description)
809 select fnd_global.user_id,
810 fnd_global.login_id,
811 sysdate,
812 sysdate,
813 fnd_global.user_id,
814 c2info.item_id,
815 c2info.quantity,
816 c2info.unit_price * Nvl(c2info.exchange_rate,1), -- bug#4189882
817 c2info.expected_date,
818 'OKE', -- hard code for OKE
819 c2info.ship_to_location_id,
820 l_employee,
821 l_employee,
822 'VENDOR',
823 decode(c2info.destination_type_code, 'INVENTORY', 'APPROVED','INCOMPLETE'),
824 c2info.uom_code,
825 p_action_id,
826 decode(c2info.destination_type_code, 'INVENTORY',mp.material_account, l_charge_account),
827 null, -- to be added later if required
828 c2info.inventory_org_id,
829 'N', -- hard coded
830 ood.operating_unit,
831 c2info.source_header_id,
832 c2info.task_id,
833 l_context,
834 c2info.source_header_id,
835 null,
836 c2info.deliverable_id,
837 c2info.action_id,
838 c2info.unit_number,
839 c2info.expenditure_organization_id,
840 c2info.expenditure_type,
841 c2info.expenditure_item_date,
842 c2info.destination_type_code,
843 c2info.currency_code,
844 c2info.exchange_rate,
845 c2info.rate_date,
846 c2info.rate_type,
847 c2info.unit_price,
848 c2info.ship_from_org_id,
849 c2info.ship_from_location_id,
850 c2info.requisition_line_type_id,
851 c2info.po_category_id,
852 l_description
853 from mtl_parameters mp
854 , org_organization_definitions ood
855 where ood.organization_id = c2info.inventory_org_id
856 and mp.organization_id = c2info.inventory_org_id;
857
858 UPDATE oke_deliverable_actions
859 SET reference1 = p_action_id
860 , in_process_flag = 'Y'
861 WHERE action_id = p_action_id;
862 -- COMMIT;
863
864 X_ID := P_Action_ID;
865 X_Return_Status := L_Return_Status;
866 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
867
868 EXCEPTION
869 WHEN OKE_API.G_EXCEPTION_ERROR THEN
870
871 x_return_status := OKE_API.HANDLE_EXCEPTIONS
872 (
873 l_api_name,
874 G_PKG_NAME,
875 'OKE_API.G_RET_STS_ERROR',
876 x_msg_count,
877 x_msg_data,
878 '_PKG');
879 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
880 x_return_status := OKE_API.HANDLE_EXCEPTIONS
881 (
882 l_api_name,
883 G_PKG_NAME,
884 'OKE_API.G_RET_STS_UNEXP_ERROR',
885 x_msg_count,
886 x_msg_data,
887 '_PKG');
888
889 WHEN OTHERS THEN
890 x_return_status := OKE_API.HANDLE_EXCEPTIONS
891 (
892 l_api_name,
893 G_PKG_NAME,
894 'OTHERS',
895 x_msg_count,
896 x_msg_data,
897 '_PKG');
898
899 END Create_Requisition;
900
901 FUNCTION Charge_Account ( P_Item_ID NUMBER, P_Org_ID NUMBER) RETURN NUMBER IS
902
903 CURSOR Item_C IS
904 SELECT Expense_Account
905 FROM mtl_system_items
906 WHERE Inventory_Item_ID = P_Item_ID
907 AND Organization_ID = P_Org_ID;
908
909 CURSOR Org_C IS
910 SELECT Expense_Account
911 FROM mtl_parameters
912 WHERE ORGANIZATION_ID = P_Org_ID;
913
914 L_Account NUMBER;
915
916
917 BEGIN
918
919
920
921 IF P_Item_ID > 0 THEN
922
923 OPEN Item_C;
924 FETCH Item_C INTO L_Account;
925 CLOSE Item_C;
926
927 IF L_Account IS NULL THEN
928
929
930
931 OPEN Org_C;
932 FETCH Org_C INTO L_Account;
933 CLOSE Org_C;
934
935 END IF;
936
937 ELSE
938
939 OPEN Org_C;
940 FETCH Org_C INTO L_Account;
941 CLOSE Org_C;
942
943 END IF;
944
945
946
947 IF L_Account > 0 THEN
948
949
950 RETURN L_Account;
951
952 ELSE
953
954
955 RETURN NULL;
956
957 END IF;
958
959
960
961 END Charge_Account;
962
963 PROCEDURE Delete_Action ( P_Action_ID NUMBER ) IS
964
965 L_Action_ID NUMBER;
966 L_Ref_2 NUMBER;
967
968 CURSOR c IS
969 SELECT action_id
970 , reference2
971 FROM oke_deliverable_actions
972 WHERE pa_action_id = p_action_id;
973
974 BEGIN
975 OPEN c;
976 FETCH c INTO L_Action_ID, L_Ref_2;
977 CLOSE c;
978
979 IF L_Ref_2 > 0 THEN
980 Delete_Row ( L_Action_ID );
981 END IF;
982
983 DELETE FROM oke_deliverable_actions
984 WHERE action_id = l_action_id;
985
986 EXCEPTION
987 WHEN OTHERS THEN
988 RAISE;
989
990 END Delete_Action;
991
992 PROCEDURE Delete_Deliverable ( P_Deliverable_ID NUMBER ) IS
993
994 L_Deliverable_ID NUMBER;
995 L_Action_ID NUMBER;
996
997 CURSOR c IS
998 SELECT deliverable_id
999 FROM oke_deliverables_b
1000 WHERE source_deliverable_id = p_deliverable_id;
1001
1002 CURSOR c_act IS
1003 SELECT pa_action_id
1004 FROM oke_deliverable_actions
1005 WHERE deliverable_id = l_deliverable_id;
1006
1007 BEGIN
1008 OPEN c;
1009 FETCH c INTO L_Deliverable_ID;
1010 CLOSE c;
1011
1012 IF L_Deliverable_ID > 0 THEN
1013
1014 For c_rec IN c_act LOOP
1015 Delete_Action ( c_rec.pa_action_id );
1016 END LOOP;
1017
1018 DELETE FROM oke_deliverables_tl
1019 WHERE deliverable_id = l_deliverable_id;
1020
1021 DELETE FROM oke_deliverables_b
1022 WHERE deliverable_id = l_deliverable_id;
1023
1024 END IF;
1025
1026 EXCEPTION
1027 WHEN OTHERS THEN
1028 RAISE;
1029
1030 END Delete_Deliverable;
1031
1032 END;
1033