[Home] [Help]
PACKAGE BODY: APPS.OKE_DELIVERABLE_ACTIONS_PKG
Source
1 PACKAGE BODY OKE_DELIVERABLE_ACTIONS_PKG AS
2 /* $Header: OKEVDACB.pls 120.4 2012/03/07 11:12:47 skuchima ship $ */
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 /*added for bug13038760*/
730 CURSOR c_allow_desc_upd ( p_org_id number,P_item_ID NUMBER ) IS
731 SELECT allow_item_desc_update_flag
732 FROM mtl_system_items
733 where organization_id = p_org_id
734 and inventory_item_id = p_item_id;
735
736 l_allow_item_desc_upd_flag VARCHAR2(1);
737
738 BEGIN
739 L_Return_Status := OKE_API.Start_Activity (
740 L_API_Name
741 , P_Init_Msg_List
742 , '_PKG'
743 , X_Return_Status );
744 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
745 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
746 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
747 RAISE OKE_API.G_EXCEPTION_ERROR;
748 END IF;
749
750 OPEN c2;
751 FETCH c2 INTO c2info;
752 CLOSE c2;
753
754 L_Charge_Account := Charge_Account ( p_item_id => c2info.item_id
755 , p_org_id => c2info.inventory_org_id );
756
757 L_Requestor := FND_GLOBAL.User_ID;
758
759 OPEN c1;
760 FETCH c1 INTO L_Employee;
761 CLOSE c1;
762
763 L_Context := 'Y';
764 /*modified for bug13038760*/
765 OPEN c_allow_desc_upd(c2info.inventory_org_id,c2info.item_id);
766 FETCH c_allow_desc_upd INTO l_allow_item_desc_upd_flag;
767 CLOSE c_allow_desc_upd;
768
769 IF NVL(l_allow_item_desc_upd_flag,'Y') = 'Y' THEN
770 L_Description := substr(PA_DELIVERABLE_UTILS.Get_Dlv_Description ( p_action_ver_id => c2info.pa_action_id ), 1, 240);
771 END IF;
772
773 IF c2info.reference1>0 THEN
774 DELETE FROM po_requisitions_interface_all
775 WHERE oke_contract_deliverable_id = c2info.action_id
776 AND batch_id = c2info.reference1;
777 END IF;
778
779 INSERT INTO po_requisitions_interface_all(
780 last_updated_by,
781 last_update_login,
782 last_update_date,
783 creation_date,
784 created_by,
785 item_id,
786 quantity,
787 unit_price,
788 need_by_date,
789 interface_source_code,
790 deliver_to_location_id,
791 deliver_to_requestor_id,
792 preparer_id,
793 source_type_code,
794 authorization_status,
795 uom_code,
796 batch_id,
797 charge_account_id,
798 group_code,
799 destination_organization_id,
800 autosource_flag,
801 org_id,
802 project_id,
803 task_id,
804 project_accounting_context,
805 oke_contract_header_id,
806 oke_contract_version_id,
807 oke_contract_line_id,
808 oke_contract_deliverable_id,
809 end_item_unit_number,
810 expenditure_organization_id,
811 expenditure_type,
812 expenditure_item_date,
813 destination_type_code,
814 currency_code,
815 rate,
816 rate_date,
817 rate_type,
818 currency_unit_price,
819 suggested_vendor_id,
820 suggested_vendor_site_id,
821 line_type_id,
822 category_id,
823 item_description)
824 select fnd_global.user_id,
825 fnd_global.login_id,
826 sysdate,
827 sysdate,
828 fnd_global.user_id,
829 c2info.item_id,
830 c2info.quantity,
831 c2info.unit_price * Nvl(c2info.exchange_rate,1), -- bug#4189882
832 c2info.expected_date,
833 'OKE', -- hard code for OKE
834 c2info.ship_to_location_id,
835 l_employee,
836 l_employee,
837 'VENDOR',
838 decode(c2info.destination_type_code, 'INVENTORY', 'APPROVED','INCOMPLETE'),
839 c2info.uom_code,
840 p_action_id,
841 decode(c2info.destination_type_code, 'INVENTORY',mp.material_account, l_charge_account),
842 null, -- to be added later if required
843 c2info.inventory_org_id,
844 'N', -- hard coded
845 ood.operating_unit,
846 c2info.source_header_id,
847 c2info.task_id,
848 l_context,
849 c2info.source_header_id,
850 null,
851 c2info.deliverable_id,
852 c2info.action_id,
853 c2info.unit_number,
854 c2info.expenditure_organization_id,
855 c2info.expenditure_type,
856 c2info.expenditure_item_date,
857 c2info.destination_type_code,
858 c2info.currency_code,
859 c2info.exchange_rate,
860 c2info.rate_date,
861 c2info.rate_type,
862 c2info.unit_price,
863 c2info.ship_from_org_id,
864 c2info.ship_from_location_id,
865 c2info.requisition_line_type_id,
866 c2info.po_category_id,
867 l_description
868 from mtl_parameters mp
869 , org_organization_definitions ood
870 where ood.organization_id = c2info.inventory_org_id
871 and mp.organization_id = c2info.inventory_org_id;
872
873 UPDATE oke_deliverable_actions
874 SET reference1 = p_action_id
875 , in_process_flag = 'Y'
876 WHERE action_id = p_action_id;
877 -- COMMIT;
878
879 X_ID := P_Action_ID;
880 X_Return_Status := L_Return_Status;
881 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
882
883 EXCEPTION
884 WHEN OKE_API.G_EXCEPTION_ERROR THEN
885
886 x_return_status := OKE_API.HANDLE_EXCEPTIONS
887 (
888 l_api_name,
889 G_PKG_NAME,
890 'OKE_API.G_RET_STS_ERROR',
891 x_msg_count,
892 x_msg_data,
893 '_PKG');
894 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
895 x_return_status := OKE_API.HANDLE_EXCEPTIONS
896 (
897 l_api_name,
898 G_PKG_NAME,
899 'OKE_API.G_RET_STS_UNEXP_ERROR',
900 x_msg_count,
901 x_msg_data,
902 '_PKG');
903
904 WHEN OTHERS THEN
905 x_return_status := OKE_API.HANDLE_EXCEPTIONS
906 (
907 l_api_name,
908 G_PKG_NAME,
909 'OTHERS',
910 x_msg_count,
911 x_msg_data,
912 '_PKG');
913
914 END Create_Requisition;
915
916 FUNCTION Charge_Account ( P_Item_ID NUMBER, P_Org_ID NUMBER) RETURN NUMBER IS
917
918 CURSOR Item_C IS
919 SELECT Expense_Account
920 FROM mtl_system_items
921 WHERE Inventory_Item_ID = P_Item_ID
922 AND Organization_ID = P_Org_ID;
923
924 CURSOR Org_C IS
925 SELECT Expense_Account
926 FROM mtl_parameters
927 WHERE ORGANIZATION_ID = P_Org_ID;
928
929 L_Account NUMBER;
930
931
932 BEGIN
933
934
935
936 IF P_Item_ID > 0 THEN
937
938 OPEN Item_C;
939 FETCH Item_C INTO L_Account;
940 CLOSE Item_C;
941
942 IF L_Account IS NULL THEN
943
944
945
946 OPEN Org_C;
947 FETCH Org_C INTO L_Account;
948 CLOSE Org_C;
949
950 END IF;
951
952 ELSE
953
954 OPEN Org_C;
955 FETCH Org_C INTO L_Account;
956 CLOSE Org_C;
957
958 END IF;
959
960
961
962 IF L_Account > 0 THEN
963
964
965 RETURN L_Account;
966
967 ELSE
968
969
970 RETURN NULL;
971
972 END IF;
973
974
975
976 END Charge_Account;
977
978 PROCEDURE Delete_Action ( P_Action_ID NUMBER ) IS
979
980 L_Action_ID NUMBER;
981 L_Ref_2 NUMBER;
982
983 CURSOR c IS
984 SELECT action_id
985 , reference2
986 FROM oke_deliverable_actions
987 WHERE pa_action_id = p_action_id;
988
989 BEGIN
990 OPEN c;
991 FETCH c INTO L_Action_ID, L_Ref_2;
992 CLOSE c;
993
994 IF L_Ref_2 > 0 THEN
995 Delete_Row ( L_Action_ID );
996 END IF;
997
998 DELETE FROM oke_deliverable_actions
999 WHERE action_id = l_action_id;
1000
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 RAISE;
1004
1005 END Delete_Action;
1006
1007 PROCEDURE Delete_Deliverable ( P_Deliverable_ID NUMBER ) IS
1008
1009 L_Deliverable_ID NUMBER;
1010 L_Action_ID NUMBER;
1011
1012 CURSOR c IS
1013 SELECT deliverable_id
1014 FROM oke_deliverables_b
1015 WHERE source_deliverable_id = p_deliverable_id;
1016
1017 CURSOR c_act IS
1018 SELECT pa_action_id
1019 FROM oke_deliverable_actions
1020 WHERE deliverable_id = l_deliverable_id;
1021
1022 BEGIN
1023 OPEN c;
1024 FETCH c INTO L_Deliverable_ID;
1025 CLOSE c;
1026
1027 IF L_Deliverable_ID > 0 THEN
1028
1029 For c_rec IN c_act LOOP
1030 Delete_Action ( c_rec.pa_action_id );
1031 END LOOP;
1032
1033 DELETE FROM oke_deliverables_tl
1034 WHERE deliverable_id = l_deliverable_id;
1035
1036 DELETE FROM oke_deliverables_b
1037 WHERE deliverable_id = l_deliverable_id;
1038
1039 END IF;
1040
1041 EXCEPTION
1042 WHEN OTHERS THEN
1043 RAISE;
1044
1045 END Delete_Deliverable;
1046
1047 END;
1048