[Home] [Help]
PACKAGE BODY: APPS.OKE_DTS_INTEGRATION_PKG
Source
1 PACKAGE BODY OKE_DTS_INTEGRATION_PKG AS
2 /* $Header: OKEINTGB.pls 120.8.12010000.2 2008/09/22 12:42:50 serukull ship $ */
3
4 g_module CONSTANT VARCHAR2(250) := 'oke.plsql.oke_dts.integaration_pkg.';
5 FUNCTION Uom_Conversion
6 ( P_Item_Id NUMBER
7 , P_From_Uom VARCHAR2
8 , P_To_Uom VARCHAR2
9 , P_Quantity NUMBER
10 ) RETURN NUMBER IS
11
12 L_Quantity NUMBER;
13
14 BEGIN
15
16
17
18 IF P_From_UOM = P_To_UOM THEN
19 L_Quantity := P_Quantity;
20 ELSIF P_From_Uom IS NULL OR P_To_Uom IS NULL THEN
21 L_Quantity := 0;
22 ELSIF P_From_Uom IS NULL AND P_To_Uom IS NULL THEN
23 L_Quantity := P_Quantity;
24 ELSE
25 L_Quantity := INV_CONVERT.Inv_Um_Convert
26 ( P_Item_ID
27 , 5 -- precision, can be changed later
28 , P_Quantity
29 , P_From_Uom
30 , P_To_Uom
31 , Null
32 , Null);
33 END IF;
34
35
36 IF L_Quantity = -9999 THEN
37 L_Quantity := 0;
38 END IF;
39
40 RETURN L_Quantity;
41
42 END;
43
44 PROCEDURE Create_Mrp_Item(p_item_id NUMBER, p_inventory_org_id NUMBER, p_plan VARCHAR2)
45 IS
46 L_Level NUMBER;
47 l_rowid VARCHAR2(100);
48 dummy VARCHAR2(1);
49 CURSOR Level_C(P_ID NUMBER) IS
50 SELECT Maximum_BOM_Level
51 FROM bom_parameters
52 WHERE Organization_ID = P_ID;
53
54 CURSOR v IS
55 SELECT '!' INTO Dummy
56 FROM mrp_schedule_items
57 WHERE Inventory_Item_Id = p_Item_Id
58 AND Schedule_designator = p_Plan
59 AND Organization_Id = p_Inventory_Org_Id;
60 BEGIN
61
62 OPEN v;
63 FETCH v INTO dummy;
64 CLOSE v;
65
66 IF dummy IS NULL THEN
67 OPEN Level_C ( p_Inventory_Org_ID );
68 FETCH Level_C INTO L_Level;
69 CLOSE Level_C;
70 IF L_Level IS NULL THEN
71 L_Level := 5;
72 END IF;
73
74 MRP_SCHEDULE_ITEMS_PKG.Insert_Row(
75 X_Rowid => l_rowid,
76 X_Inventory_Item_Id => p_Item_Id,
77 X_Organization_Id => p_inventory_org_id,
78 X_Schedule_Designator => p_plan,
79 X_Last_Update_Date => sysdate,
80 X_Last_Updated_By => fnd_global.user_id,
81 X_Creation_Date => sysdate,
82 X_Created_By => fnd_global.user_id,
83 X_Last_Update_Login => fnd_global.login_id,
84 X_MPS_Explosion_Level => L_Level
85 );
86 END IF;
87
88 END Create_Mrp_Item;
89
90 PROCEDURE INSERT_ROW (
91 p_Item_ID NUMBER,
92 p_Inv_Org_ID NUMBER,
93 p_Designator VARCHAR2,
94 p_Demand_Date DATE,
95 p_workdate DATE,
96 p_Primary_Qty NUMBER,
97 p_Project_ID NUMBER,
98 p_Task_ID NUMBER,
99 p_Unit_Number VARCHAR2,
100 p_deliverable_id NUMBER,
101 x_mps_transaction_id OUT NOCOPY NUMBER
102 ) IS
103
104 l_id NUMBER;
105 l_schedule_level CONSTANT NUMBER := 2;
106 l_supply_demand_type CONSTANT NUMBER := 1;
107 l_schedule_origination_type CONSTANT NUMBER := 1;
108
109 CURSOR l_csr(p_id NUMBER) IS
110 SELECT MPS_TRANSACTION_ID FROM MRP_SCHEDULE_DATES
111 WHERE MPS_TRANSACTION_ID = l_id
112 AND SCHEDULE_LEVEL = L_Schedule_Level
113 AND SUPPLY_DEMAND_TYPE = L_Supply_Demand_Type;
114
115 CURSOR l_id_csr IS
116 SELECT mrp_schedule_dates_s.NEXTVAL FROM dual;
117
118 BEGIN
119
120 --
121 -- Get id
122 --
123 OPEN l_id_csr;
124 FETCH l_id_csr INTO l_id;
125 CLOSE l_id_csr;
126
127 -- Insert twice to create both original and current record
128 INSERT INTO MRP_SCHEDULE_DATES(
129 MPS_TRANSACTION_ID,
130 SCHEDULE_LEVEL,
131 SUPPLY_DEMAND_TYPE,
132 LAST_UPDATE_DATE,
133 LAST_UPDATED_BY,
134 CREATION_DATE,
135 CREATED_BY,
136 LAST_UPDATE_LOGIN,
137 INVENTORY_ITEM_ID,
138 ORGANIZATION_ID,
139 SCHEDULE_DESIGNATOR,
140 SCHEDULE_DATE,
141 SCHEDULE_WORKDATE,
142 SCHEDULE_QUANTITY,
143 ORIGINAL_SCHEDULE_QUANTITY,
144 SCHEDULE_ORIGINATION_TYPE,
145 PROJECT_ID,
146 TASK_ID,
147 END_ITEM_UNIT_NUMBER,
148 SOURCE_CODE,
149 SOURCE_LINE_ID)
150 SELECT
151 L_Id ,
152 lu.lookup_code ,
153 L_Supply_Demand_Type ,
154 Sysdate ,
155 Fnd_Global.User_Id ,
156 Sysdate ,
157 Fnd_Global.User_Id ,
158 Fnd_Global.Login_Id ,
159 p_Item_Id ,
160 p_Inv_Org_ID ,
161 p_Designator ,
162 p_Demand_Date ,
163 p_workdate ,
164 p_Primary_Qty ,
165 p_Primary_Qty ,
166 l_schedule_origination_type,
167 p_Project_Id ,
168 p_Task_Id ,
169 p_Unit_Number ,
170 'OKE' ,
171 P_Deliverable_ID
172 FROM mfg_lookups lu
173 WHERE lookup_type = 'MRP_SCHEDULE_LEVEL'
174 AND lookup_code IN ( 1 , 2)
175 AND NOT EXISTS (
176 SELECT NULL
177 FROM mrp_schedule_dates
178 WHERE mps_transaction_id = L_Id
179 AND schedule_level = lu.lookup_code );
180
181 OPEN l_csr(l_id);
182 FETCH l_csr INTO l_id;
183 IF (l_csr%NOTFOUND) THEN
184
185 CLOSE l_csr;
186 RAISE NO_DATA_FOUND;
187
188 END IF;
189
190 CLOSE l_csr;
191
192 x_mps_transaction_id := l_id;
193
194 END insert_row;
195
196 PROCEDURE update_row (
197 p_Item_ID NUMBER,
198 p_Inv_Org_ID NUMBER,
199 p_Designator VARCHAR2,
200 p_Demand_Date DATE,
201 p_workdate DATE,
202 p_Primary_Qty NUMBER,
203 p_Project_ID NUMBER,
204 p_Task_ID NUMBER,
205 p_Unit_Number VARCHAR2,
206 p_deliverable_id NUMBER,
207 p_row_id ROWID
208 ) IS
209
210 BEGIN
211
212 UPDATE MRP_SCHEDULE_DATES d
213 SET
214 LAST_UPDATE_DATE = Sysdate,
215 LAST_UPDATED_BY = Fnd_Global.User_Id,
216 LAST_UPDATE_LOGIN = Fnd_Global.Login_Id,
217 INVENTORY_ITEM_ID = p_Item_Id,
218 ORGANIZATION_ID = p_Inv_Org_Id,
219 SCHEDULE_DESIGNATOR = p_Designator,
220 SCHEDULE_DATE = p_Demand_Date,
221 SCHEDULE_WORKDATE = p_workdate,
222 ORIGINAL_SCHEDULE_QUANTITY = p_Primary_Qty,
223 SCHEDULE_QUANTITY = ( SELECT greatest(p_Primary_Qty - nvl(sum(R.relief_quantity) , 0) , 0)
224 FROM mrp_schedule_consumptions R
225 WHERE R.transaction_id = d.mps_transaction_id ),
226 PROJECT_ID = p_Project_Id,
227 TASK_ID = p_Task_Id,
228 END_ITEM_UNIT_NUMBER = p_Unit_Number
229 WHERE ROWID = p_row_id;
230
231 END update_row;
232 /*
233 PROCEDURE lock_row (P_MPS_TRANSACTION_ID IN NUMBER, P_Deliverable_Id In Number) IS
234
235
236 CURSOR l_csr IS
237 SELECT
238 MPS_TRANSACTION_ID,
239 SCHEDULE_LEVEL,
240 SUPPLY_DEMAND_TYPE,
241 LAST_UPDATE_DATE,
242 LAST_UPDATED_BY,
243 CREATION_DATE,
244 CREATED_BY,
245 LAST_UPDATE_LOGIN,
246 INVENTORY_ITEM_ID,
247 ORGANIZATION_ID,
248 SCHEDULE_DESIGNATOR,
249 SCHEDULE_DATE,
250 SCHEDULE_WORKDATE,
251 RATE_END_DATE,
252 SCHEDULE_QUANTITY,
253 ORIGINAL_SCHEDULE_QUANTITY,
254 REPETITIVE_DAILY_RATE,
255 SCHEDULE_ORIGINATION_TYPE,
256 SOURCE_FORECAST_DESIGNATOR,
257 REFERENCE_SCHEDULE_ID,
258 SCHEDULE_COMMENTS,
259 SOURCE_ORGANIZATION_ID,
260 SOURCE_SCHEDULE_DESIGNATOR,
261 SOURCE_SALES_ORDER_ID,
262 SOURCE_CODE,
263 SOURCE_LINE_ID,
264 RESERVATION_ID,
265 FORECAST_ID,
266 REQUEST_ID,
267 PROGRAM_APPLICATION_ID,
268 PROGRAM_ID,
269 PROGRAM_UPDATE_DATE,
270 DDF_CONTEXT,
271 ATTRIBUTE_CATEGORY,
272 ATTRIBUTE1,
273 ATTRIBUTE2,
274 ATTRIBUTE3,
275 ATTRIBUTE4,
276 ATTRIBUTE5,
277 ATTRIBUTE6,
278 ATTRIBUTE7,
279 ATTRIBUTE8,
280 ATTRIBUTE9,
281 ATTRIBUTE10,
282 ATTRIBUTE11,
283 ATTRIBUTE12,
284 ATTRIBUTE13,
285 ATTRIBUTE14,
286 ATTRIBUTE15,
287 PROJECT_ID,
288 TASK_ID,
289 LINE_ID,
290 END_ITEM_UNIT_NUMBER
291 FROM MRP_SCHEDULE_DATES
292 WHERE MPS_TRANSACTION_ID = P_MPS_TRANSACTION_ID
293 AND SCHEDULE_LEVEL = 2
294 AND SUPPLY_DEMAND_TYPE = 1
295 FOR UPDATE OF MPS_TRANSACTION_ID NOWAIT;
296
297 Cursor C Is
298 Select Mps_Transaction_Id
299 , Item_Id
300 , Inventory_Org_Id
301 , Ndb_Schedule_Designator
302 , Expected_Shipment_Date
303 , Quantity
304 , Project_Id
305 , Task_Id
306 , Unit_Number
307 From oke_k_deliverables_b
308 Where Deliverable_Id = P_Deliverable_Id;
309
310 recinfo l_csr%ROWTYPE;
311 cinfo C%ROWTYPE;
312 l_schedule_level constant number := 2;
313 l_supply_demand_type constant number := 1;
314 l_schedule_origination_type constant number := 1;
315
316 BEGIN
317
318
319 Open C;
320 Fetch C Into Cinfo;
321 Close C;
322
323 OPEN l_csr;
324 FETCH l_csr INTO recinfo;
325
326 IF(l_csr%NOTFOUND) THEN
327 CLOSE l_csr;
328 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
329 APP_EXCEPTION.RAISE_EXCEPTION;
330 END IF;
331
332 CLOSE l_csr;
333
334 IF(
335 (recinfo.mps_transaction_id = cinfo.mps_transaction_id)
336 and (recinfo.schedule_level = l_schedule_level)
337 and (recinfo.supply_demand_type = l_supply_demand_type)
338 and (recinfo.inventory_item_id = cinfo.item_id)
339 and (recinfo.organization_id = cinfo.inventory_org_id)
340 and (recinfo.schedule_designator = cinfo.ndb_schedule_designator)
341 and (recinfo.schedule_date = cinfo.expected_shipment_date)
342
343 and (recinfo.schedule_origination_type = l_schedule_origination_type)
344 and ((recinfo.project_id = cinfo.project_id)
345 or ((recinfo.project_id is null)
346 and (cinfo.project_id is null)))
347 and ((recinfo.task_id = cinfo.task_id)
348 or ((recinfo.task_id is null)
349 and (cinfo.task_id is null)))
350 and ((recinfo.end_item_unit_number = cinfo.unit_number)
351 or ((recinfo.end_item_unit_number is null)
352 and (cinfo.unit_number is null)))) THEN
353
354 NULL;
355
356 ELSE
357 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_CHANGED');
358 APP_EXCEPTION.RAISE_EXCEPTION;
359
360 END IF;
361
362 RETURN;
363
364 END lock_row;
365 */
366 PROCEDURE create_mds_entry(
367 P_DELIVERABLE_ID IN NUMBER,
368 X_OUT_ID OUT NOCOPY NUMBER,
369 X_RETURN_STATUS OUT NOCOPY VARCHAR2)
370 IS
371 l_return_status VARCHAR2(1) := oke_api.g_ret_sts_success;
372 l_api_version CONSTANT NUMBER :=1;
373 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MDS_ENTRY';
374 x_msg_count NUMBER;
375 x_msg_data VARCHAR2(2000);
376
377 l_current_id NUMBER;
378 l_func VARCHAR2(1);
379 l_workdate Date;
380 L_Primary_Qty NUMBER;
381 L_To_Uom VARCHAR2(3);
382 l_cr_item BOOLEAN := TRUE;
383
384 CURSOR mds_c IS
385 Select rowid
386 , item_id
387 , inventory_org_id
388 , ndb_schedule_designator
389 , nvl(expected_shipment_date , need_by_date) demand_date
390 , quantity
391 , project_id
392 , task_id
393 , unit_number
394 , uom_code
395 , mps_transaction_id
396 From oke_k_deliverables_b
397 Where deliverable_id = p_deliverable_id
398 FOR UPDATE NOWAIT;
399 L_DRow_ID ROWID;
400 L_Item_ID NUMBER;
401 L_Inv_Org_ID NUMBER;
402 L_Designator VARCHAR2(80);
403 L_Demand_Date DATE;
404 L_Quantity NUMBER;
405 L_Project_ID NUMBER;
406 L_Task_ID NUMBER;
407 L_Unit_Number VARCHAR2(80);
408 L_UOM_Code VARCHAR2(80);
409 l_mps_transaction_id NUMBER;
410
411 CURSOR Uom_C ( P_Item_ID NUMBER , P_Org_ID NUMBER ) IS
412 SELECT Primary_Uom_Code
413 FROM mtl_system_items
414 WHERE inventory_item_id = P_Item_ID
415 AND organization_id = P_Org_ID;
416
417 CURSOR sdates_c IS
418 SELECT rowid,
419 INVENTORY_ITEM_ID,
420 ORGANIZATION_ID,
421 SCHEDULE_DESIGNATOR
422 FROM mrp_schedule_dates
423 WHERE MPS_TRANSACTION_ID = l_MPS_TRANSACTION_ID
424 AND SCHEDULE_LEVEL = 2
425 AND SUPPLY_DEMAND_TYPE = 1
426 FOR UPDATE NOWAIT;
427 L_SRow_ID ROWID;
428 L_Item_ID_Old NUMBER;
429 L_Inv_Org_ID_Old NUMBER;
430 L_Designator_Old VARCHAR2(80);
431
432 BEGIN
433 l_return_status := OKE_API.START_ACTIVITY(
434 l_api_name,
435 OKE_API.G_FALSE,
436 '_PVT',
437 x_return_status);
438
439 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
440 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
441 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
442 RAISE OKE_API.G_EXCEPTION_ERROR;
443 END IF;
444
445 Open Mds_C;
446 Fetch Mds_C Into L_DRow_ID, L_Item_ID, L_Inv_Org_ID, L_Designator,
447 L_Demand_Date, L_Quantity, L_Project_ID, L_Task_ID,
448 L_Unit_Number, L_Uom_Code, l_mps_transaction_id;
449 Close Mds_C;
450
451 IF l_mps_transaction_id IS NOT NULL THEN
452 OPEN sdates_c;
453 FETCH sdates_c INTO L_SRow_ID, L_Item_ID_Old, L_Inv_Org_ID_Old, L_Designator_Old;
454 CLOSE sdates_c;
455 END IF;
456
457 --
458 -- Get workdate
459 --
460 l_workdate := mrp_calendar.prev_work_day(l_Inv_Org_Id , 1 , l_Demand_Date);
461
462 --
463 -- Get Primary Uom
464 --
465 OPEN Uom_C(l_Item_ID , l_Inv_Org_Id);
466 FETCH Uom_C INTO L_To_Uom;
467 CLOSE Uom_C;
468
469 L_Primary_Qty := Uom_Conversion( l_Item_ID , l_Uom_Code , L_To_Uom , l_Quantity);
470
471 IF L_SRow_ID IS NOT NULL THEN -- update if MDS record exists
472 update_row(
473 p_Item_ID => l_Item_ID,
474 p_Inv_Org_ID => l_Inv_Org_ID,
475 p_Designator => l_Designator,
476 p_Demand_Date => l_Demand_Date,
477 p_workdate => l_workdate,
478 p_Primary_Qty => l_Primary_Qty ,
479 p_Project_ID => l_Project_ID,
480 p_Task_ID => l_Task_ID,
481 p_Unit_Number => l_Unit_Number,
482 P_Deliverable_Id => p_deliverable_id,
483 P_ROW_ID => L_SRow_ID
484 );
485
486 -- delete empty mrp_schedule_items, if item, org, or plan changed
487 IF L_Item_ID_Old = L_Item_ID
488 AND L_Inv_Org_ID_Old = L_Inv_Org_ID
489 AND L_Designator_Old = L_Designator
490 THEN
491 l_cr_item := FALSE;
492 -- Do not remove extra records from mrp_schedule_items
493 -- ELSE
494 -- DELETE mrp_schedule_items
495 -- WHERE Inventory_Item_Id = L_Item_ID_Old
496 -- AND Schedule_designator = L_Designator_Old
497 -- AND Organization_Id = L_Inv_Org_ID_Old
498 -- AND NOT EXISTS(
499 -- SELECT NULL FROM mrp_schedule_dates
500 -- WHERE Inventory_Item_Id = L_Item_ID_Old
501 -- AND Schedule_designator = L_Designator_Old
502 -- AND Organization_Id = L_Inv_Org_ID_Old
503 -- );
504 END IF;
505
506 UPDATE oke_k_deliverables_b
507 SET po_ref_2 = 1
508 WHERE ROWID = L_DRow_ID;
509
510 ELSE -- if record wasn't updated - insert it
511
512 INSERT_ROW(
513 p_Item_ID => l_Item_ID,
514 p_Inv_Org_ID => l_Inv_Org_ID,
515 p_Designator => l_Designator,
516 p_Demand_Date => l_Demand_Date,
517 p_workdate => l_workdate,
518 p_Primary_Qty => l_Primary_Qty ,
519 p_Project_ID => l_Project_ID,
520 p_Task_ID => l_Task_ID,
521 p_Unit_Number => l_Unit_Number,
522 P_Deliverable_Id => P_Deliverable_Id,
523 X_Mps_Transaction_Id => L_mps_transaction_id
524 );
525 IF l_mps_transaction_id IS NOT NULL THEN
526 x_out_id := l_mps_transaction_id;
527 -- update mps_transaction_id in deliverable table
528 UPDATE oke_k_deliverables_b
529 SET mps_transaction_id = l_mps_transaction_id
530 WHERE ROWID = L_DRow_ID;
531 END IF;
532 END IF;
533
534 -- Create Mrp Schedule Item if not Exists
535 IF l_cr_item THEN
536 Create_Mrp_Item( l_Item_Id, l_Inv_Org_Id, l_Designator );
537 END IF;
538
539 x_return_status := l_return_status;
540 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
541
542 EXCEPTION
543 WHEN OKE_API.G_EXCEPTION_ERROR THEN
544
545 x_return_status := OKE_API.HANDLE_EXCEPTIONS
546 (
547 l_api_name,
548 G_PKG_NAME,
549 'OKE_API.G_RET_STS_ERROR',
550 x_msg_count,
551 x_msg_data,
552 '_PVT');
553 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
554 x_return_status := OKE_API.HANDLE_EXCEPTIONS
555 (
556 l_api_name,
557 G_PKG_NAME,
558 'OKE_API.G_RET_STS_UNEXP_ERROR',
559 x_msg_count,
560 x_msg_data,
561 '_PVT');
562
563 WHEN OTHERS THEN
564 x_return_status := OKE_API.HANDLE_EXCEPTIONS
565 (
566 l_api_name,
567 G_PKG_NAME,
568 'OTHERS',
569 x_msg_count,
570 x_msg_data,
571 '_PVT');
572
573
574 END create_mds_entry;
575
576 PROCEDURE create_ship_line(
577 P_DELIVERABLE_ID IN NUMBER,
578 X_DELVIERY_DETAIL_ID OUT NOCOPY NUMBER,
579 X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
580
581 l_ship_rec wsh_delivery_details_pkg.delivery_details_rec_type;
582 l_return_status varchar2(1);
583 l_api_version CONSTANT NUMBER :=1;
584 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SHIP_LINE';
585
586 x_msg_count number;
587 x_msg_data varchar2(2000);
588 l_id number;
589 l_customer_id Number;
590 l_fob_term VARCHAR2(30);
591 l_ship_method VARCHAR2(30);
592 l_ship_priority VARCHAR2(30);
593 l_freight_term VARCHAR2(30);
594 l_org_id NUMBER;
595
596 -- dumb addition for bug 3405926
597 l_header_id number;
598 l_cust_po_number varchar2(150);
599 l_contract_number varchar2(150);
600 l_authoring_org_id number;
601 l_deliverable_num varchar2(150);
602 l_project_id number;
603 l_task_id number;
604 l_quantity number;
605 l_item_id number;
606 l_ship_to_location_id number;
607 l_uom_code varchar2(80);
608 l_expected_date date;
609 l_promised_date date;
610 l_ship_from_location_id number;
611 l_inv_org_id number;
612 l_description varchar2(250);
613 l_country_of_origin_code varchar2(80);
614 l_inspection_req_flag varchar2(1);
615 l_unit_number varchar2(80);
616 l_currency_code varchar2(80);
617 l_weight number;
618 l_weight_uom_code varchar2(80);
619 l_volume number;
620 l_volume_uom_code varchar2(80);
621
622 cursor term
623 ( C_deliverable_id number
624 , C_term_code varchar2
625 ) is
626 select kt1.term_value_pk1 term_value
627 from oke_k_deliverables_b d
628 , oke_k_terms kt1
629 , ( select cle_id , cle_id_ascendant , level_sequence from okc_ancestrys
630 union all
631 select id , id , 99999 from okc_k_lines_b ) a
632 where d.deliverable_id = C_deliverable_id
633 and kt1.term_code = C_term_code
634 and kt1.k_header_id = d.k_header_id
635 and a.cle_id = d.k_line_id
636 and ( ( kt1.k_line_id is null and a.cle_id = a.cle_id_ascendant )
637 or kt1.k_line_id = a.cle_id_ascendant )
638 order by decode(kt1.k_line_id , null , 0 , a.level_sequence) desc;
639
640 CURSOR csr_dts_ship(p_id number) IS
641 SELECT shipping_request_id, in_process_flag, initiate_shipment_date
642 FROM oke_k_deliverables_b
643 WHERE deliverable_id = p_id
644 FOR UPDATE OF shipping_request_id, in_process_flag, initiate_shipment_date NOWAIT;
645
646 CURSOR l_line_csr(p_id number) IS
647 SELECT B.K_HEADER_ID
648 , H.CUST_PO_NUMBER
649 , H.CONTRACT_NUMBER
650 , H.AUTHORING_ORG_ID
651 , B.DELIVERABLE_NUM
652 , B.PROJECT_ID
653 , B.TASK_ID
654 , B.QUANTITY
655 , B.ITEM_ID
656 , B.SHIP_TO_LOCATION_ID
657 , B.UOM_CODE
658 , B.EXPECTED_SHIPMENT_DATE
659 , B.PROMISED_SHIPMENT_DATE
660 , B.SHIP_FROM_LOCATION_ID
661 , B.INVENTORY_ORG_ID
662 , T.DESCRIPTION
663 , B.COUNTRY_OF_ORIGIN_CODE
664 , DECODE(B.INSPECTION_REQ_FLAG , 'Y' , 'R' , 'N') INSPECTION_REQ_FLAG
665 , B.UNIT_NUMBER
666 , B.CURRENCY_CODE
667 , B.WEIGHT
668 , B.WEIGHT_UOM_CODE
669 , B.VOLUME
670 , B.VOLUME_UOM_CODE
671 FROM OKC_K_HEADERS_B H
672 , OKE_K_DELIVERABLES_B B
673 , OKE_K_DELIVERABLES_TL T
674 WHERE B.DELIVERABLE_ID = p_id
675 AND B.DELIVERABLE_ID = T.DELIVERABLE_ID
676 AND T.LANGUAGE = USERENV('LANG')
677 AND H.ID = B.K_HEADER_ID;
678
679 CURSOR Item_C ( P_ID NUMBER ) IS
680 SELECT Decode(MTL_Transactions_Enabled_Flag, 'Y', 'Y', 'N')
681 FROM mtl_system_items
682 WHERE Inventory_Item_ID = P_ID;
683
684 CURSOR OU_C ( P_Organization_ID NUMBER ) IS
685 SELECT OPERATING_UNIT
686 FROM ORG_ORGANIZATION_DEFINITIONS
687 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
688
689 -- linfo l_line_csr%rowtype;
690 L_Flag VARCHAR2(1);
691
692 BEGIN
693 l_return_status := OKE_API.START_ACTIVITY(
694 l_api_name,
695 OKE_API.G_FALSE,
696 '_PVT',
697 x_return_status);
698
699 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
700
701 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
702
703 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
704
705 RAISE OKE_API.G_EXCEPTION_ERROR;
706
707 END IF;
708
709 OPEN l_line_csr(P_DELIVERABLE_ID);
710 FETCH l_line_csr INTO l_header_id, l_cust_po_number, l_contract_number, l_authoring_org_id, l_deliverable_num, l_project_id, l_task_id, l_quantity
711 , l_item_id, l_ship_to_location_id, l_uom_code, l_expected_date, l_promised_date, l_ship_from_location_id, l_inv_org_id, l_description
712 , l_country_of_origin_code, l_inspection_req_flag, l_unit_number, l_currency_code, l_weight, l_weight_uom_code, l_volume, l_volume_uom_code;
713 CLOSE l_line_csr;
714
715 -- get the right ship_to_location_id
716 select location_id into l_ship_rec.ship_to_location_id
717 from oke_cust_site_uses_v
718 where id1 = l_ship_to_location_id;
719
720 -- get the customer_id from contract header party
721 -- currently use ship_to_location derive party_id from oke_cust_site_uses_v
722
723 select cust_account_id into l_customer_id
724 from oke_cust_site_uses_v
725 where id1 = l_ship_to_location_id;
726
727 -- Populate shipping terms for report purpose
728
729 FOR Item_Info IN Term(P_Deliverable_ID, 'OB_FOB') LOOP
730 L_Fob_Term := Item_Info.Term_Value;
731 EXIT WHEN L_Fob_Term IS NOT NULL;
732 END LOOP;
733
734 FOR Item_Info IN Term(P_Deliverable_ID, 'OB_SHIPMENT_PRIORITY') LOOP
735 L_Ship_Priority := Item_Info.Term_Value;
736 EXIT WHEN L_Ship_Priority IS NOT NULL;
737 END LOOP;
738
739 FOR Item_Info IN Term(P_Deliverable_ID, 'OB_SHIPPING_METHOD') LOOP
740 L_Ship_Method := Item_Info.Term_Value;
741 EXIT WHEN L_Ship_Method IS NOT NULL;
742 END LOOP;
743
744 FOR Item_Info IN Term(P_Deliverable_ID, 'OB_FREIGHT_TERMS') LOOP
745 L_Freight_Term := Item_Info.Term_Value;
746 EXIT WHEN L_Freight_Term IS NOT NULL;
747 END LOOP;
748
749 -- Check item specific info
750 OPEN Item_C ( L_Item_ID );
751 FETCH Item_C INTO L_Flag;
752 CLOSE Item_C;
753
754 -- Retrieve Operating Unit info
755 OPEN OU_C(l_INV_ORG_ID);
756 FETCH OU_C INTO L_Org_ID;
757 CLOSE OU_C;
758
759
760
761 -- set record
762 l_ship_rec.delivery_detail_id := null;
763 l_ship_rec.source_code := G_WSH_SOURCE_CODE;
764 l_ship_rec.source_header_id := L_HEADER_ID;
765 l_ship_rec.source_line_id := P_DELIVERABLE_ID;
766 l_ship_rec.customer_id := l_customer_id;
767 l_ship_rec.sold_to_contact_id := null;
768 l_ship_rec.inventory_item_id := l_ITEM_ID;
769 l_ship_rec.item_description := L_DESCRIPTION;
770 l_ship_rec.hazard_class_id := null;
771 l_ship_rec.country_of_origin := l_COUNTRY_OF_ORIGIN_CODE;
772 l_ship_rec.classification := null;
773 l_ship_rec.ship_from_location_id := l_SHIP_FROM_LOCATION_ID;
774 l_ship_rec.ship_to_site_use_id := l_ship_to_location_id;
775 l_ship_rec.ship_to_contact_id := null;
776 l_ship_rec.deliver_to_location_id := null;
777 l_ship_rec.deliver_to_contact_id := null;
778 l_ship_rec.intmed_ship_to_location_id := null;
779 l_ship_rec.intmed_ship_to_contact_id := null;
780 l_ship_rec.hold_code := null;
781 l_ship_rec.ship_tolerance_above := null;
782 l_ship_rec.ship_tolerance_below := null;
783 -- l_ship_rec.requested_quantity := l_QUANTITY;
784 l_ship_rec.shipped_quantity := null;
785 l_ship_rec.delivered_quantity := null;
786 -- l_ship_rec.requested_quantity_uom := l_UOM_CODE;
787 l_ship_rec.subinventory := null;
788 l_ship_rec.revision := null;
789 l_ship_rec.lot_number := null;
790 l_ship_rec.customer_requested_lot_flag:= null;
791 l_ship_rec.serial_number := null;
792 l_ship_rec.locator_id := null;
793 l_ship_rec.date_requested := l_promised_date;
794 l_ship_rec.date_scheduled := l_expected_date;
795 l_ship_rec.master_container_item_id := null;
796 l_ship_rec.detail_container_item_id := null;
797 l_ship_rec.load_seq_number := null;
798 l_ship_rec.ship_method_code := l_ship_method;
799 l_ship_rec.carrier_id := null;
800 l_ship_rec.freight_terms_code := l_freight_term;
801 l_ship_rec.shipment_priority_code := l_ship_priority;
802 l_ship_rec.fob_code := l_fob_term;
803 l_ship_rec.customer_item_id := null;
804 l_ship_rec.dep_plan_required_flag := null;
805 l_ship_rec.customer_prod_seq := null;
806 l_ship_rec.customer_dock_code := null;
807 l_ship_rec.net_weight := l_weight;
808 l_ship_rec.weight_uom_code := l_weight_uom_code;
809 l_ship_rec.volume := l_volume;
810 l_ship_rec.volume_uom_code := l_volume_uom_code;
811 l_ship_rec.tp_attribute_category := null;
812 l_ship_rec.tp_attribute1 := null;
813 l_ship_rec.tp_attribute2 := null;
814 l_ship_rec.tp_attribute3 := null;
815 l_ship_rec.tp_attribute4 := null;
816 l_ship_rec.tp_attribute5 := null;
817 l_ship_rec.tp_attribute6 := null;
818 l_ship_rec.tp_attribute7 := null;
819 l_ship_rec.tp_attribute8 := null;
820 l_ship_rec.tp_attribute9 := null;
821 l_ship_rec.tp_attribute10 := null;
822 l_ship_rec.tp_attribute11 := null;
823 l_ship_rec.tp_attribute12 := null;
824 l_ship_rec.tp_attribute13 := null;
825 l_ship_rec.tp_attribute14 := null;
826 l_ship_rec.tp_attribute15 := null;
827 l_ship_rec.attribute_category := null;
828 l_ship_rec.attribute1 := null;
829 l_ship_rec.attribute2 := null;
830 l_ship_rec.attribute3 := null;
831 l_ship_rec.attribute4 := null;
832 l_ship_rec.attribute5 := null;
833 l_ship_rec.attribute6 := null;
834 l_ship_rec.attribute7 := null;
835 l_ship_rec.attribute8 := null;
836 l_ship_rec.attribute9 := null;
837 l_ship_rec.attribute10 := null;
838 l_ship_rec.attribute11 := null;
839 l_ship_rec.attribute12 := null;
840 l_ship_rec.attribute13 := null;
841 l_ship_rec.attribute14 := null;
842 l_ship_rec.attribute15 := null;
843 l_ship_rec.created_by := fnd_global.user_id;
844 l_ship_rec.creation_date := sysdate;
845 l_ship_rec.last_update_date := sysdate;
846 l_ship_rec.last_update_login := fnd_global.login_id;
847 l_ship_rec.last_updated_by := fnd_global.user_id;
848 l_ship_rec.program_application_id := null;
849 l_ship_rec.program_id := null;
850 l_ship_rec.program_update_date := null;
851 l_ship_rec.request_id := null;
852 l_ship_rec.mvt_stat_status := null;
853 l_ship_rec.released_flag := null;
854 l_ship_rec.organization_id := l_INV_ORG_ID;
855 l_ship_rec.transaction_temp_id := null;
856 l_ship_rec.ship_set_id := null;
857 l_ship_rec.arrival_set_id := null;
858 l_ship_rec.ship_model_complete_flag := null;
859 l_ship_rec.top_model_line_id := null;
860 l_ship_rec.source_header_number := l_CONTRACT_NUMBER;
861 l_ship_rec.source_header_type_id := null;
862 l_ship_rec.source_header_type_name := null;
863 l_ship_rec.cust_po_number := l_CUST_PO_NUMBER;
864 l_ship_rec.ato_line_id := null;
865 l_ship_rec.src_requested_quantity := l_QUANTITY;
866 l_ship_rec.src_requested_quantity_uom := l_UOM_CODE;
867 l_ship_rec.move_order_line_id := null;
868 l_ship_rec.cancelled_quantity := null;
869 l_ship_rec.quality_control_quantity := null;
870 l_ship_rec.cycle_count_quantity := null;
871 l_ship_rec.tracking_number := null;
872 l_ship_rec.movement_id := null;
873 l_ship_rec.shipping_instructions := null;
874 l_ship_rec.packing_instructions := null;
875 l_ship_rec.project_id := l_PROJECT_ID;
876 l_ship_rec.task_id := l_TASK_ID;
877 l_ship_rec.org_id := l_org_id;
878 l_ship_rec.oe_interfaced_flag := null;
879 l_ship_rec.split_from_detail_id := null;
880 l_ship_rec.inv_interfaced_flag := null;
881 l_ship_rec.source_line_number := l_DELIVERABLE_NUM;
882 l_ship_rec.inspection_flag := l_INSPECTION_REQ_FLAG;
883 l_ship_rec.container_flag := null;
884 l_ship_rec.container_type_code := null;
885 l_ship_rec.container_name := null;
886 l_ship_rec.fill_percent := null;
887 l_ship_rec.gross_weight := null;
888 l_ship_rec.master_serial_number := null;
889 l_ship_rec.maximum_load_weight := null;
890 l_ship_rec.maximum_volume := null;
891 l_ship_rec.minimum_fill_percent := null;
892 l_ship_rec.seal_code := null;
893 l_ship_rec.unit_number := l_unit_number;
894 l_ship_rec.currency_code := l_currency_code;
895 l_ship_rec.freight_class_cat_id := null;
896 l_ship_rec.commodity_code_cat_id := null;
897 l_ship_rec.preferred_grade := null;
898 l_ship_rec.src_requested_quantity2 := null;
899 l_ship_rec.src_requested_quantity_uom2 := null;
900 l_ship_rec.requested_quantity2 := null;
901 l_ship_rec.shipped_quantity2 := null;
902 l_ship_rec.delivered_quantity2 := null;
903 l_ship_rec.cancelled_quantity2 := null;
904 l_ship_rec.quality_control_quantity2 := null;
905 l_ship_rec.cycle_count_quantity2 := null;
906 l_ship_rec.requested_quantity_uom2 := null;
907 -- l_ship_rec.sublot_number := null;
908 l_ship_rec.lpn_id := null;
909
910 -- bug 2424468, populate pickable_flag
911 l_ship_rec.pickable_flag := l_flag;
912
913 -- bug 3597451
914 -- Try to lock record and update data
915
916 FOR rec_dts_ship IN csr_dts_ship(p_deliverable_id) LOOP
917 /*Bug 6011322 start */
918 IF rec_dts_ship.shipping_request_id IS NOT NULL THEN
919 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_SHIP_EXISTS');
920 FND_MESSAGE.set_token('SHIPPING_DETAIL',rec_dts_ship.shipping_request_id);
921 FND_MSG_PUB.Add;
922 RAISE OKE_API.G_EXCEPTION_ERROR;
923 END IF;
924 /* Bug 6011322 end */
925
926 -- call api
927 WSH_interface_pub.create_shipment_lines(l_ship_rec, l_id, l_return_status);
928
929 if l_return_status = oke_api.g_ret_sts_success then
930 -- update deliverable table
931 update oke_k_deliverables_b
932 set shipping_request_id = l_id,
933 in_process_flag = 'Y',
934 initiate_shipment_date = sysdate
935 where CURRENT OF csr_dts_ship;
936
937 x_return_status := l_return_status;
938
939 else
940
941 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
942
943 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
944
945 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
946
947 RAISE OKE_API.G_EXCEPTION_ERROR;
948
949 END IF;
950
951 end if;
952
953 END LOOP;
954 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
955 COMMIT;
956
957
958
959 EXCEPTION
960 WHEN OKE_API.G_EXCEPTION_ERROR THEN
961
962 x_return_status := OKE_API.HANDLE_EXCEPTIONS
963 (
964 l_api_name,
965 G_PKG_NAME,
966 'OKE_API.G_RET_STS_ERROR',
967 x_msg_count,
968 x_msg_data,
969 '_PVT');
970 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
971 x_return_status := OKE_API.HANDLE_EXCEPTIONS
972 (
973 l_api_name,
974 G_PKG_NAME,
975 'OKE_API.G_RET_STS_UNEXP_ERROR',
976 x_msg_count,
977 x_msg_data,
978 '_PVT');
979
980 WHEN OTHERS THEN
981 x_return_status := OKE_API.HANDLE_EXCEPTIONS
982 (
983 l_api_name,
984 G_PKG_NAME,
985 'OTHERS',
986 x_msg_count,
987 x_msg_data,
988 '_PVT');
989
990 END;
991
992 PROCEDURE create_req_line
993 ( p_requestor in varchar2
994 , p_deliverable_id in number
995 , p_charge_account in number
996 , x_batch_id out nocopy number
997 , x_return_status out nocopy varchar2
998 ) IS
999
1000 l_return_status VARCHAR2(1);
1001 l_api_version CONSTANT NUMBER :=1;
1002 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_REQ_LINE';
1003 l_mps_id NUMBER;
1004
1005 x_msg_count number;
1006 x_msg_data varchar2(2000);
1007 l_id number;
1008 l_requestor varchar2(150);
1009 l_found boolean;
1010 l_employee_id number;
1011 l_project_id NUMBER;
1012 l_task_id NUMBER;
1013 l_inv_org_id NUMBER;
1014 l_dest_type VARCHAR2(30);
1015 l_exp_type VARCHAR2(30);
1016 l_exp_org_id NUMBER;
1017 l_exp_item_date DATE;
1018 l_need_by_date DATE;
1019 l_error_code VARCHAR2(80);
1020 l_context VARCHAR2(1);
1021 l_mds_id Number;
1022 l_sched_date DATE;
1023 L_Item_Id NUMBER;
1024 L_Plan Varchar2(80);
1025 L_Currency_Code VARCHAR2(80);
1026 L_Exchange_Rate NUMBER;
1027 L_Unit_Price NUMBER;
1028 L_Currency_Price NUMBER;
1029 l_ship_from_org_id NUMBER;
1030 l_vendor_id NUMBER;
1031 l_vendor_site_id NUMBER;
1032 l_ship_from_location_id NUMBER;
1033 L_Buy_Or_Sell VARCHAR2(1);
1034 L_Func_Currency_Code VARCHAR2(30);
1035 L_Req_Line_Type_ID NUMBER;
1036 L_Category_ID NUMBER;
1037 l_quantity NUMBER;
1038 l_description VARCHAR2(240);
1039 l_item_description VARCHAR2(240);
1040 l_uom_code VARCHAR2(30);
1041
1042
1043
1044 cursor c is
1045 select employee_id
1046 from fnd_user
1047 where user_name = l_requestor;
1048
1049 cursor c1 is
1050 select project_id
1051 , task_id
1052 , destination_type_code
1053 , expenditure_type
1054 , expenditure_organization_id
1055 , expenditure_item_date
1056 , inventory_org_id
1057 , trunc(need_by_date)
1058 , mps_transaction_id
1059 , ndb_schedule_designator
1060 , expected_shipment_date
1061 , item_id
1062 , unit_price
1063 , exchange_rate
1064 , ship_from_org_id
1065 , currency_code
1066 , ship_from_location_id
1067 , requisition_line_type_id
1068 , po_category_id
1069 , quantity
1070 , description
1071 , uom_code
1072 from oke_k_deliverables_vl
1073 where deliverable_id = p_deliverable_id;
1074
1075 CURSOR csr_dts_req(p_id number) IS
1076 SELECT po_ref_1, in_process_flag
1077 FROM oke_k_deliverables_b
1078 WHERE deliverable_id = p_id
1079 FOR UPDATE OF po_ref_1, in_process_flag NOWAIT;
1080 /* Bug Number: 6011322 start */
1081 cursor req_c(p_id number, p_batch_id number) is
1082 select 'S'
1083 from po_requisitions_interface_all
1084 where oke_contract_deliverable_id = p_id
1085 and nvl(process_flag, 'S') = 'ERROR'
1086 and batch_id = p_batch_id;
1087 /* Bug Number: 6011322 end */
1088
1089 --
1090 -- Cursor to validate PA information for Inventory
1091 --
1092 cursor pi is
1093 select 'OKE_DTS_EXP_PROJECT_INVALID'
1094 from dual
1095 where not exists (
1096 select 'Project is valid'
1097 from pa_projects_expend_v
1098 where project_id = l_project_id )
1099 union all
1100 select 'OKE_PROJECT_NOT_SETUP'
1101 from dual
1102 where not exists (
1103 select 'Project valid for PJM'
1104 from pjm_project_parameters
1105 where organization_id = l_inv_org_id
1106 and project_id = l_project_id )
1107 union all
1108 select 'OKE_DTS_EXP_TASK_INVALID'
1109 from dual
1110 where not exists (
1111 select 'Task valid and chargeable'
1112 from pa_tasks_expend_v t
1113 where project_id = l_project_id
1114 and task_id = l_task_id
1115 and chargeable_flag = 'Y' )
1116 union all
1117 select 'OKE_TASK_REQUIRED'
1118 from dual
1119 where not exists (
1120 select 'Task Reference OK'
1121 from pjm_org_parameters
1122 where organization_id = l_inv_org_id
1123 and not ( project_control_level = 2
1124 and l_task_id is null )
1125 );
1126
1127 --
1128 -- Cursor to validate PA information for Expense
1129 --
1130 cursor pe is
1131 select 'OKE_DTS_EXP_PROJECT_INVALID'
1132 from dual
1133 where not exists (
1134 select 'Project is valid'
1135 from pa_projects_expend_v
1136 where project_id = l_project_id )
1137 union all
1138 select 'OKE_DTS_EXP_TASK_INVALID'
1139 from dual
1140 where not exists (
1141 select 'Task is valid and chargeable'
1142 from pa_tasks_expend_v
1143 where project_id = l_project_id
1144 and task_id = l_task_id
1145 and chargeable_flag = 'Y' )
1146 union all
1147 select 'OKE_DTS_EXP_DATE_INVALID'
1148 from dual
1149 where not exists (
1150 select 'Date is valid for task'
1151 from pa_tasks t
1152 , pa_projects_all p
1153 where t.project_id = l_project_id
1154 and t.task_id = l_task_id
1155 and p.project_id = t.project_id
1156 and l_exp_item_date
1157 between nvl(t.start_date , nvl(p.start_date , l_exp_item_date - 1))
1158 and nvl(t.completion_date , nvl(p.completion_date , l_exp_item_date + 1)) )
1159 union all
1160 select 'OKE_DTS_EXP_TYPE_INVALID'
1161 from dual
1162 where not exists (
1163 select 'Expenditure Type exists and is valid'
1164 from pa_expenditure_types_expend_v
1165 where expenditure_type = l_exp_type
1166 and system_linkage_function = 'VI' )
1167 union all
1168 select 'OKE_DTS_EXP_ORG_INVALID'
1169 from dual
1170 where not exists (
1171 select 'Expenditure Org exists and is valid'
1172 from pa_organizations_expend_v
1173 where organization_id = l_exp_org_id );
1174
1175 CURSOR Curr_C ( P_ID NUMBER ) IS
1176 SELECT gl.Currency_Code
1177 FROM gl_sets_of_books gl, org_organization_definitions org
1178 WHERE org.organization_id = P_ID
1179 AND gl.Set_Of_Books_ID = org.Set_Of_Books_ID;
1180
1181 CURSOR Header_C IS
1182 SELECT Buy_Or_Sell
1183 FROM okc_k_headers_b
1184 WHERE ID = (SELECT K_Header_ID FROM oke_k_deliverables_b WHERE Deliverable_ID = P_Deliverable_ID);
1185
1186 BEGIN
1187 l_return_status := OKE_API.START_ACTIVITY(
1188 l_api_name,
1189 OKE_API.G_FALSE,
1190 '_PVT',
1191 x_return_status);
1192
1193 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1194
1195 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1196
1197 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1198
1199 RAISE OKE_API.G_EXCEPTION_ERROR;
1200
1201 END IF;
1202
1203
1204 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1205 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start centrol logging debug session for okeintgb.pls');
1206 EnD IF;
1207
1208 -- populate preparer_id, requestor_id based on wf requestor
1209 l_requestor := p_requestor;
1210
1211 OPEN c;
1212 fetch c into l_employee_id;
1213 l_found := c%found;
1214 close c;
1215
1216 if l_found then
1217 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1218 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'preparer populated');
1219 END IF;
1220 --
1221 -- verify if the project and task supplied for projects
1222 --
1223 open c1;
1224
1225 fetch c1 into l_project_id , l_task_id , l_dest_type
1226 , l_exp_type , l_exp_org_id , l_exp_item_date
1227 , l_inv_org_id , l_need_by_date
1228 , l_mds_id , l_plan , l_sched_date , l_item_id
1229 , l_unit_price, l_exchange_rate, l_ship_from_org_id
1230 , l_currency_code, l_ship_from_location_id, l_req_line_type_id
1231 , l_category_id, l_quantity, l_description, l_uom_code;
1232
1233 close c1;
1234 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1235 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'line type is : ' || l_req_line_type_id);
1236 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'category is :' || l_category_id);
1237 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'description is : ' || l_description);
1238
1239 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'before validate project');
1240 END IF;
1241
1242 if ( l_project_id is not null ) then
1243
1244 l_context := 'Y';
1245
1246 --
1247 -- Validate PA information
1248 --
1249 if ( l_dest_type = 'INVENTORY' ) then
1250
1251 l_error_code := NULL;
1252 open pi;
1253 loop
1254 fetch pi into l_error_code;
1255 exit when pi%notfound;
1256 FND_MESSAGE.Set_Name('OKE' , l_error_code);
1257 FND_MSG_PUB.Add;
1258 end loop;
1259 close pi;
1260 if ( l_error_code is not null ) then
1261 RAISE OKE_API.G_EXCEPTION_ERROR;
1262 end if;
1263
1264 l_exp_type := NULL;
1265 l_exp_org_id := NULL;
1266 l_exp_item_date := NULL;
1267
1268 else /* destination type is EXPENSE */
1269
1270 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1271 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'called validate expense type');
1272 END IF;
1273 l_error_code := NULL;
1274 open pe;
1275 loop
1276 fetch pe into l_error_code;
1277 exit when pe%notfound;
1278 FND_MESSAGE.Set_Name('OKE' , l_error_code);
1279 FND_MSG_PUB.Add;
1280 end loop;
1281 close pe;
1282 if ( l_error_code is not null ) then
1283 RAISE OKE_API.G_EXCEPTION_ERROR;
1284 end if;
1285
1286 end if;
1287
1288 else /* project_id is null */
1289
1290 l_context := 'N';
1291
1292 end if;
1293
1294
1295 -- Get converted price based on the functional currency and contract currency
1296
1297 OPEN Curr_C(L_Inv_Org_ID);
1298 FETCH Curr_C INTO L_Func_Currency_Code;
1299 CLOSE Curr_C;
1300
1301 IF L_Func_Currency_Code <> L_Currency_Code THEN
1302
1303 if l_unit_price > 0 and l_exchange_rate > 0 then
1304
1305 l_currency_price := L_Unit_Price;
1306 l_unit_price := l_unit_price * l_exchange_rate;
1307
1308 end if;
1309
1310 END IF;
1311
1312 -- Get vendor info if buy contract
1313
1314 OPEN Header_C;
1315 FETCH Header_C INTO L_Buy_Or_Sell;
1316 CLOSE Header_C;
1317
1318 IF L_Buy_Or_Sell = 'B' THEN
1319
1320 L_Vendor_ID := L_Ship_From_Org_ID;
1321 L_Vendor_Site_ID := L_Ship_From_Location_ID;
1322
1323 END IF;
1324
1325 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1326 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'call before amount based');
1327 END IF;
1328 -- Amount based requisition logics
1329
1330 if l_dest_type = 'EXPENSE' then
1331
1332 if l_item_id is null and l_req_line_type_id > 0 then
1333 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1334 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'called amount based');
1335 END IF;
1336
1337 l_quantity := l_unit_price * l_quantity;
1338 l_unit_price := 1;
1339 l_item_description := l_description;
1340 l_uom_code := null;
1341
1342 end if;
1343
1344 end if;
1345
1346 -- bug 3597451
1347 -- Try to lock record and update data
1348
1349 FOR rec_dts_req IN csr_dts_req(p_deliverable_id) LOOP
1350 /*Bug Bumber: 6011322 */
1351 if rec_dts_req.po_ref_1 > 0 then
1352
1353 l_return_status := OKE_API.G_RET_STS_ERROR;
1354 open req_c(p_deliverable_id, rec_dts_req.po_ref_1);
1355 fetch req_c into l_return_status;
1356 close req_c;
1357 IF l_return_status = OKE_API.G_RET_STS_ERROR THEN
1358 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_REQ_EXISTS');
1359 FND_MSG_PUB.Add;
1360 RAISE OKE_API.G_EXCEPTION_ERROR;
1361 END IF;
1362
1363 end if;
1364
1365 -- unique group id to be associate with all rows in the table
1366 select oke_interface_s.nextval
1367 into l_id
1368 from dual;
1369 /* Bug Number: 6011322 end */
1370
1371 insert into po_requisitions_interface_all(
1372 last_updated_by,
1373 last_update_login,
1374 last_update_date,
1375 creation_date,
1376 created_by,
1377 item_id,
1378 quantity,
1379 unit_price,
1380 need_by_date,
1381 interface_source_code,
1382 deliver_to_location_id,
1383 deliver_to_requestor_id,
1384 preparer_id,
1385 source_type_code,
1386 authorization_status,
1387 uom_code,
1388 batch_id,
1389 charge_account_id,
1390 group_code,
1391 destination_organization_id,
1392 autosource_flag,
1393 org_id,
1394 project_id,
1395 task_id,
1396 project_accounting_context,
1397 oke_contract_header_id,
1398 oke_contract_version_id,
1399 oke_contract_line_id,
1400 oke_contract_deliverable_id,
1401 end_item_unit_number,
1402 expenditure_organization_id,
1403 expenditure_type,
1404 expenditure_item_date,
1405 destination_type_code,
1406 currency_code,
1407 rate,
1408 rate_date,
1409 rate_type,
1410 currency_unit_price,
1411 suggested_vendor_id,
1412 suggested_vendor_site_id,
1413 line_type_id,
1414 category_id,
1415 item_description)
1416 select fnd_global.user_id,
1417 fnd_global.login_id,
1418 sysdate,
1419 sysdate,
1420 fnd_global.user_id,
1421 d.item_id,
1422 l_quantity,
1423 l_unit_price,
1424 l_need_by_date,
1425 'OKE', -- hard code for OKE
1426 d.ship_to_location_id,
1427 l_employee_id,
1428 l_employee_id,
1429 'VENDOR',
1430 decode(d.destination_type_code, 'INVENTORY', 'APPROVED','INCOMPLETE'),
1431 l_uom_code,
1432 l_id,
1433 decode(d.destination_type_code, 'INVENTORY',mp.material_account, p_charge_account),
1434 null, -- to be added later if required
1435 d.inventory_org_id,
1436 'N', -- hard coded
1437 ood.operating_unit,
1438 l_project_id,
1439 l_task_id,
1440 l_context,
1441 d.k_header_id,
1442 ver.major_version,
1443 d.k_line_id,
1444 d.deliverable_id,
1445 d.unit_number,
1446 l_exp_org_id,
1447 l_exp_type,
1448 l_exp_item_date,
1449 l_dest_type,
1450 nvl(d.currency_code,l_func_currency_code),
1451 d.exchange_rate,
1452 d.rate_date,
1453 d.rate_type,
1454 l_currency_price,
1455 l_vendor_id,
1456 l_vendor_site_id,
1457 l_req_line_type_id,
1458 l_category_id,
1459 l_item_description
1460 from oke_k_deliverables_b d
1461 , okc_k_headers_b h
1462 , mtl_parameters mp
1463 , org_organization_definitions ood
1464 , oke_k_vers_numbers_v ver
1465 where d.deliverable_id = p_deliverable_id
1466 and h.id = d.k_header_id
1467 and ver.chr_id = d.k_header_id
1468 and ood.organization_id = d.inventory_org_id
1469 and mp.organization_id = d.inventory_org_id;
1470
1471
1472
1473 if l_return_status = oke_api.g_ret_sts_success then
1474 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1475 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Req created successfully, update deliverable table');
1476 END IF;
1477
1478 -- update deliverable table
1479 update oke_k_deliverables_b
1480 set po_ref_1 = l_id
1481 , in_process_flag = 'Y'
1482 where CURRENT OF csr_dts_req;
1483
1484 --
1485 -- Work around for Planning not recognize OKE records when link PO to the MDS entries
1486 -- created from DTS
1487 --
1488
1489 IF ( l_plan IS NOT NULL
1490 AND nvl(l_sched_date , l_need_by_date) IS NOT NULL
1491 AND l_item_id IS NOT NULL
1492 AND l_inv_org_id IS NOT NULL ) THEN
1493 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1494 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Update MDS');
1495 END IF;
1496
1497 create_mds_entry(
1498 P_DELIVERABLE_ID => P_DELIVERABLE_ID,
1499 X_OUT_ID => L_MPS_ID,
1500 X_RETURN_STATUS => L_RETURN_STATUS);
1501
1502 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1503
1504 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1505
1506 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1507
1508 RAISE OKE_API.G_EXCEPTION_ERROR;
1509
1510 END IF;
1511
1512 END IF;
1513
1514 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1515 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Create Req process completed normally');
1516 END IF;
1517
1518 x_batch_id := l_id;
1519 x_return_status := l_return_status;
1520
1521
1522
1523 else
1524
1525 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1526
1527 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1528
1529 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1530
1531 RAISE OKE_API.G_EXCEPTION_ERROR;
1532
1533 END IF;
1534
1535 end if;
1536
1537
1538 END LOOP;
1539 COMMIT;
1540
1541
1542 end if;
1543
1544 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1545
1546
1547 EXCEPTION
1548 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1549
1550 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1551 (
1552 l_api_name,
1553 G_PKG_NAME,
1554 'OKE_API.G_RET_STS_ERROR',
1555 x_msg_count,
1556 x_msg_data,
1557 '_PVT');
1558 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1559 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1560 (
1561 l_api_name,
1562 G_PKG_NAME,
1563 'OKE_API.G_RET_STS_UNEXP_ERROR',
1564 x_msg_count,
1565 x_msg_data,
1566 '_PVT');
1567
1568 WHEN OTHERS THEN
1569 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1570 (
1571 l_api_name,
1572 G_PKG_NAME,
1573 'OTHERS',
1574 x_msg_count,
1575 x_msg_data,
1576 '_PVT');
1577
1578 END;
1579
1580 PROCEDURE create_actions
1581 ( P_API_VERSION IN NUMBER
1582 , P_REQUESTOR IN VARCHAR2
1583 , P_INIT_MSG_LIST IN VARCHAR2
1584 , X_MSG_COUNT OUT NOCOPY NUMBER
1585 , X_MSG_DATA OUT NOCOPY VARCHAR2
1586 , P_ACTION IN VARCHAR2
1587 , P_DELIVERABLE_ID IN NUMBER
1588 , P_CHARGE_ACCOUNT IN NUMBER
1589 , X_RESULT OUT NOCOPY NUMBER
1590 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
1591 ) IS
1592
1593 l_api_name varchar2(30);
1594 l_return_status varchar2(1) ;
1595
1596 BEGIN
1597
1598 l_api_name := 'CREATE_ACTIONS';
1599 l_return_status := OKE_API.G_RET_STS_SUCCESS;
1600
1601 l_return_status := OKE_API.START_ACTIVITY(
1602 l_api_name,
1603 OKE_API.G_FALSE,
1604 '_PVT',
1605 x_return_status);
1606
1607 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1608
1609 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1610
1611 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1612
1613 RAISE OKE_API.G_EXCEPTION_ERROR;
1614
1615 END IF;
1616
1617
1618
1619 IF P_ACTION = 'PLAN' THEN
1620
1621 create_mds_entry(
1622 P_DELIVERABLE_ID => P_DELIVERABLE_ID,
1623 X_OUT_ID => X_RESULT,
1624 X_RETURN_STATUS => L_RETURN_STATUS);
1625
1626 ELSIF P_ACTION = 'SHIP' THEN
1627
1628 create_ship_line(
1629 P_DELIVERABLE_ID => P_DELIVERABLE_ID,
1630 X_DELVIERY_DETAIL_ID => X_RESULT,
1631 X_RETURN_STATUS => L_RETURN_STATUS);
1632
1633 ELSIF P_ACTION = 'REQ' THEN
1634
1635 create_req_line(
1636 p_requestor => P_REQUESTOR,
1637 p_deliverable_id => P_DELIVERABLE_ID,
1638 p_charge_account => P_CHARGE_ACCOUNT,
1639 x_batch_id => X_RESULT,
1640 x_return_status => L_RETURN_STATUS);
1641
1642 END IF;
1643
1644
1645 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1646
1647 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1648
1649 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1650
1651 RAISE OKE_API.G_EXCEPTION_ERROR;
1652
1653 END IF;
1654
1655 x_return_status := l_return_status;
1656
1657 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1658
1659 EXCEPTION
1660 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1661
1662 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1663 (
1664 l_api_name,
1665 G_PKG_NAME,
1666 'OKE_API.G_RET_STS_ERROR',
1667 x_msg_count,
1668 x_msg_data,
1669 '_PVT');
1670 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1671 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1672 (
1673 l_api_name,
1674 G_PKG_NAME,
1675 'OKE_API.G_RET_STS_UNEXP_ERROR',
1676 x_msg_count,
1677 x_msg_data,
1678 '_PVT');
1679
1680 WHEN OTHERS THEN
1681 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1682 (
1683 l_api_name,
1684 G_PKG_NAME,
1685 'OTHERS',
1686 x_msg_count,
1687 x_msg_data,
1688 '_PVT');
1689
1690 END;
1691
1692 --
1693 -- Public Procedures
1694 --
1695 PROCEDURE Set_WF_Attributes
1696 ( ItemType IN VARCHAR2
1697 , ItemKey IN VARCHAR2
1698 , ActID IN NUMBER
1699 , FuncMode IN VARCHAR2
1700 , ResultOut OUT NOCOPY VARCHAR2
1701 ) IS
1702
1703 l_k_number varchar2(120);
1704 l_doc_type varchar2(80);
1705 l_line_number varchar2(120);
1706 l_deliverable_num varchar2(120);
1707 --bug 7390122 added parameter l_description to display deliverable description
1708 l_description varchar2(240);
1709 --bug 7390122 end
1710 l_destination_type varchar2(80);
1711 l_expenditure_type varchar2(80);
1712 l_expenditure_item_date date;
1713 l_expenditure_org varchar2(240);
1714 l_need_by_date date;
1715
1716 l_ship_from_location_id number;
1717 l_org_id number;
1718 l_ship_from_location varchar2(80);
1719
1720 CURSOR c_common (p_id number) IS
1721 SELECT H.k_number_disp k_number
1722 , T.k_type_name doc_type
1723 , L.line_number line_number
1724 , D.deliverable_num deliverable_num
1725 , D.description description
1726 , h.authoring_org_id org_id
1727 --bug 7390122 changed oke_k_deliverables_b to oke_k_deliverables_vl as it has description
1728 FROM oke_k_deliverables_vl D
1729 , okc_k_lines_b L
1730 , oke_k_headers_v H
1731 , oke_k_types_vl T
1732 WHERE D.deliverable_id = p_id
1733 AND L.id = D.k_line_id
1734 AND H.k_header_id = L.dnz_chr_id
1735 AND T.k_type_code = H.k_type_code;
1736 -- crec c_common%rowtype;
1737
1738 CURSOR c_req (p_id number) IS
1739 SELECT D.destination_type_code destination_type
1740 , D.expenditure_type
1741 , D.expenditure_item_date
1742 , O.name expenditure_org
1743 , D.need_by_date
1744 FROM oke_k_deliverables_b D
1745 , hr_all_organization_units_tl O
1746 WHERE D.deliverable_id = p_id
1747 AND O.organization_id (+) = D.expenditure_organization_id
1748 AND O.language (+) = userenv('LANG');
1749 -- rrec c_req%rowtype;
1750
1751 CURSOR c_ship (p_id number) IS
1752 SELECT D.ship_from_location_id
1753 , L.location_code ship_from_location
1754 FROM oke_k_deliverables_b D
1755 , hr_locations_all_tl L
1756 WHERE D.deliverable_id = p_id
1757 AND L.location_id = D.ship_from_location_id
1758 AND L.language = userenv('LANG');
1759 -- srec c_ship%rowtype;
1760
1761 l_deliverable_id NUMBER;
1762 l_action VARCHAR2(30);
1763
1764 BEGIN
1765
1766 IF ( FuncMode = 'RUN' ) THEN
1767
1768 l_deliverable_id := WF_ENGINE.GetItemAttrNumber
1769 ( itemtype => ItemType
1770 , ItemKey => ItemKey
1771 , AName => 'DELIVERABLE_ID' );
1772
1773 l_action := WF_ENGINE.GetItemAttrText
1774 ( itemtype => ItemType
1775 , ItemKey => ItemKey
1776 , AName => 'ACTION' );
1777
1778 --
1779 -- Initializing common attributes
1780 --
1781
1782
1783 OPEN c_common(l_deliverable_id);
1784 --bug 7390122 added l_description for fetching description
1785 FETCH c_common INTO l_k_number, l_doc_type, l_line_number, l_deliverable_num,l_description,l_org_id;
1786 CLOSE c_common;
1787
1788 WF_ENGINE.SetItemAttrText
1789 ( ItemType => ItemType
1790 , ItemKey => ItemKey
1791 , AName => 'DOC_TYPE'
1792 , AValue => l_doc_type );
1793
1794 WF_ENGINE.SetItemAttrText
1795 ( ItemType => ItemType
1796 , ItemKey => ItemKey
1797 , AName => 'K_NUMBER'
1798 , AValue => l_k_number );
1799
1800 WF_ENGINE.SetItemAttrText
1801 ( ItemType => ItemType
1802 , ItemKey => ItemKey
1803 , AName => 'DELIVERABLE_NUM'
1804 , AValue => l_deliverable_num );
1805
1806 --bug 7390122 added function for setting description
1807 WF_ENGINE.SetItemAttrText
1808 ( ItemType => ItemType
1809 , ItemKey => ItemKey
1810 , AName => 'DESCRIPTION'
1811 , AValue => l_description );
1812
1813 WF_ENGINE.SetItemAttrText
1814 ( ItemType => ItemType
1815 , ItemKey => ItemKey
1816 , AName => 'LINE_NUMBER'
1817 , AValue => l_line_number );
1818
1819 WF_ENGINE.SetItemAttrNUMBER
1820 ( ItemType => ItemType
1821 , ItemKey => ItemKey
1822 , AName => 'ORG_ID'
1823 , AValue => l_org_id );
1824
1825 IF ( l_action = 'REQ' ) THEN
1826 --
1827 -- Initializing Requisition specific attributes
1828 --
1829
1830
1831 OPEN c_req(l_deliverable_id);
1832 FETCH c_req INTO l_destination_type, l_expenditure_type, l_expenditure_item_date, l_expenditure_org, l_need_by_date;
1833 CLOSE c_req;
1834
1835 WF_ENGINE.SetItemAttrText
1836 ( ItemType => ItemType
1837 , ItemKey => ItemKey
1838 , AName => 'DESTINATION_TYPE'
1839 , AValue => l_destination_type );
1840
1841 WF_ENGINE.SetItemAttrText
1842 ( ItemType => ItemType
1843 , ItemKey => ItemKey
1844 , AName => 'EXPENDITURE_TYPE'
1845 , AValue => l_expenditure_type );
1846
1847 WF_ENGINE.SetItemAttrText
1848 ( ItemType => ItemType
1849 , ItemKey => ItemKey
1850 , AName => 'EXPENDITURE_ORG'
1851 , AValue => l_expenditure_org );
1852
1853 WF_ENGINE.SetItemAttrDate
1854 ( ItemType => ItemType
1855 , ItemKey => ItemKey
1856 , AName => 'EXPENDITURE_ITEM_DATE'
1857 , AValue => l_expenditure_item_date );
1858
1859 WF_ENGINE.SetItemAttrDate
1860 ( ItemType => ItemType
1861 , ItemKey => ItemKey
1862 , AName => 'SCHEDULE_DATE'
1863 , AValue => l_need_by_date );
1864
1865 ELSIF ( l_action = 'SHIP' ) THEN
1866 --
1867 -- Initializing Shipping specific attributes
1868 --
1869
1870
1871 OPEN c_ship(l_deliverable_id);
1872 FETCH c_ship INTO l_ship_from_location_id, l_ship_from_location;
1873 CLOSE c_ship;
1874
1875 WF_ENGINE.SetItemAttrText
1876 ( ItemType => ItemType
1877 , ItemKey => ItemKey
1878 , AName => 'SHIP_FROM_LOCATION'
1879 , AValue => l_ship_from_location );
1880
1881 END IF;
1882
1883 ResultOut := 'COMPLETE:';
1884 RETURN;
1885
1886 END IF;
1887
1888 IF ( FuncMode = 'CANCEL' ) THEN
1889 ResultOut := '';
1890 RETURN;
1891 END IF;
1892
1893 IF ( FuncMode = 'TIMEOUT' ) THEN
1894 ResultOut := '';
1895 RETURN;
1896 END IF;
1897
1898 EXCEPTION
1899 WHEN OTHERS THEN
1900 ResultOut := 'ERROR:';
1901 WF_ENGINE.SetItemAttrText
1902 ( ItemType => ItemType
1903 , ItemKey => ItemKey
1904 , AName => 'ERRORTEXT'
1905 , AValue => sqlerrm );
1906 WF_Core.Context
1907 ( 'OKE_DTS_INTEGRATION_PKG'
1908 , 'SET_WF_ATTRIBUTES'
1909 , ItemType
1910 , ItemKey
1911 , to_char(ActID)
1912 , FuncMode
1913 , ResultOut );
1914 RAISE;
1915
1916 END Set_WF_Attributes;
1917
1918
1919 PROCEDURE Create_Event
1920 ( ItemType IN VARCHAR2
1921 , ItemKey IN VARCHAR2
1922 , ActID IN NUMBER
1923 , FuncMode IN VARCHAR2
1924 , ResultOut OUT NOCOPY VARCHAR2
1925 ) IS
1926
1927 l_api_version NUMBER;
1928 l_init_msg_list VARCHAR2(240);
1929 l_action VARCHAR2(30);
1930 l_requestor VARCHAR2(30);
1931 l_msg_count NUMBER;
1932 l_msg_data VARCHAR2(240);
1933 l_deliverable_id NUMBER;
1934 l_result NUMBER;
1935 l_return_status VARCHAR2(240);
1936 l_error_text VARCHAR2(4000);
1937 l_charge_account NUMBER;
1938
1939 i NUMBER;
1940
1941 BEGIN
1942
1943 IF ( FuncMode = 'RUN' ) THEN
1944
1945 l_api_version := WF_ENGINE.GetItemAttrNumber
1946 ( itemtype => ItemType
1947 , ItemKey => ItemKey
1948 , AName => 'API_VERSION' );
1949
1950 l_init_msg_list := WF_ENGINE.GetItemAttrText
1951 ( itemtype => ItemType
1952 , ItemKey => ItemKey
1953 , AName => 'INIT_MSG_LIST' );
1954
1955 l_deliverable_id := WF_ENGINE.GetItemAttrNumber
1956 ( itemtype => ItemType
1957 , ItemKey => ItemKey
1958 , AName => 'DELIVERABLE_ID' );
1959
1960 l_action := WF_ENGINE.GetItemAttrText
1961 ( itemtype => ItemType
1962 , ItemKey => ItemKey
1963 , AName => 'ACTION' );
1964
1965 l_requestor := WF_ENGINE.GetItemAttrText
1966 ( itemtype => ItemType
1967 , ItemKey => ItemKey
1968 , AName => 'REQUESTOR' );
1969
1970 l_charge_account := WF_ENGINE.GetItemAttrNumber
1971 ( itemtype => ItemType
1972 , ItemKey => ItemKey
1973 , AName => 'CHARGE_ACCOUNT' );
1974 FND_MSG_PUB.initialize;
1975
1976
1977
1978 OKE_DTS_INTEGRATION_PKG.CREATE_ACTIONS
1979 ( l_api_version
1980 , l_requestor
1981 , l_init_msg_list
1982 , l_msg_count
1983 , l_msg_data
1984 , l_action
1985 , l_deliverable_id
1986 , l_charge_account
1987 , l_result
1988 , l_return_status);
1989
1990 WF_ENGINE.SetItemAttrNumber
1991 ( ItemType => ItemType
1992 , ItemKey => ItemKey
1993 , AName => 'MSG_COUNT'
1994 , AValue => l_msg_count );
1995
1996 WF_ENGINE.SetItemAttrText
1997 ( ItemType => ItemType
1998 , ItemKey => ItemKey
1999 , AName => 'MSG_DATA'
2000 , AValue => l_msg_data );
2001
2002 IF ( l_action = 'PLAN' ) THEN
2003 WF_ENGINE.SetItemAttrNumber
2004 ( ItemType => ItemType
2005 , ItemKey => ItemKey
2006 , AName => 'MPS_ID'
2007 , AValue => l_result );
2008 ELSIF ( l_action = 'SHIP' ) THEN
2009 WF_ENGINE.SetItemAttrNumber
2010 ( ItemType => ItemType
2011 , ItemKey => ItemKey
2012 , AName => 'DELIVERY_DETAIL_ID'
2013 , AValue => l_result );
2014 ELSIF ( l_action = 'REQ' ) THEN
2015 WF_ENGINE.SetItemAttrNumber
2016 ( ItemType => ItemType
2017 , ItemKey => ItemKey
2018 , AName => 'BATCH_ID'
2019 , AValue => l_result );
2020 END IF;
2021
2022 WF_ENGINE.SetItemAttrText
2023 ( ItemType => ItemType
2024 , ItemKey => ItemKey
2025 , AName => 'RETURN_STATUS'
2026 , AValue => l_return_status );
2027
2028 IF ( l_return_status <> OKE_API.G_RET_STS_SUCCESS ) THEN
2029
2030 if ( l_msg_count = 1 ) then
2031 l_error_text := FND_MSG_PUB.Get( p_msg_index => 1 , p_encoded => 'F' );
2032 elsif ( l_msg_count > 1 ) then
2033 for i in 1..l_msg_count loop
2034 if ( l_error_text is null ) then
2035 l_error_text := i || '. ' ||
2036 fnd_msg_pub.get( p_msg_index => i , p_encoded => 'F' );
2037 else
2038 l_error_text := l_error_text || fnd_global.newline || fnd_global.newline ||
2039 i || '. ' ||
2040 fnd_msg_pub.get( p_msg_index => i , p_encoded => 'F' );
2041 end if;
2042 end loop;
2043 end if;
2044
2045 WF_ENGINE.SetItemAttrText
2046 ( ItemType => ItemType
2047 , ItemKey => ItemKey
2048 , AName => 'ERRORTEXT'
2049 , AValue => l_error_text );
2050
2051 ResultOut := 'COMPLETE:E';
2052 ELSE
2053 ResultOut := 'COMPLETE:S';
2054 END IF;
2055
2056 RETURN;
2057
2058 END IF;
2059
2060 IF ( FuncMode = 'CANCEL' ) THEN
2061 ResultOut := '';
2062 RETURN;
2063 END IF;
2064
2065 IF ( FuncMode = 'TIMEOUT' ) THEN
2066 ResultOut := '';
2067 RETURN;
2068 END IF;
2069
2070 EXCEPTION
2071 WHEN OTHERS THEN
2072 ResultOut := 'ERROR:';
2073 WF_ENGINE.SetItemAttrText
2074 ( ItemType => ItemType
2075 , ItemKey => ItemKey
2076 , AName => 'ERRORTEXT'
2077 , AValue => sqlerrm );
2078 WF_Core.Context
2079 ( 'OKE_DTS_INTEGRATION_PKG'
2080 , 'CREATE_EVENT'
2081 , ItemType
2082 , ItemKey
2083 , to_char(ActID)
2084 , FuncMode
2085 , ResultOut );
2086 RAISE;
2087 END CREATE_EVENT;
2088
2089 PROCEDURE Get_Charge_Account
2090 ( ItemType IN VARCHAR2
2091 , ItemKey IN VARCHAR2
2092 , ActID IN NUMBER
2093 , FuncMode IN VARCHAR2
2094 , ResultOut OUT NOCOPY VARCHAR2
2095 ) IS
2096
2097 l_item_id NUMBER;
2098 l_org_id NUMBER;
2099 l_charge_account NUMBER;
2100
2101
2102 i NUMBER;
2103
2104 BEGIN
2105
2106 IF ( FuncMode = 'RUN' ) THEN
2107
2108 l_item_id := WF_ENGINE.GetItemAttrNumber
2109 ( itemtype => ItemType
2110 , ItemKey => ItemKey
2111 , AName => 'ITEM_ID' );
2112
2113 l_Org_id := WF_ENGINE.GetItemAttrNumber
2114 ( itemtype => ItemType
2115 , ItemKey => ItemKey
2116 , AName => 'ORGANIZATION_ID' );
2117
2118
2119
2120
2121 l_charge_account := OKE_DTS_INTEGRATION_PKG.CHARGE_ACCOUNT(L_Item_ID, L_Org_ID);
2122
2123 WF_ENGINE.SetItemAttrNumber
2124 ( ItemType => ItemType
2125 , ItemKey => ItemKey
2126 , AName => 'CHARGE_ACCOUNT'
2127 , AValue => l_charge_account);
2128
2129 ResultOut := 'COMPLETE:S';
2130
2131 RETURN;
2132
2133 END IF;
2134
2135
2136 IF ( FuncMode = 'CANCEL' ) THEN
2137 ResultOut := '';
2138 RETURN;
2139 END IF;
2140
2141 IF ( FuncMode = 'TIMEOUT' ) THEN
2142 ResultOut := '';
2143 RETURN;
2144 END IF;
2145
2146 EXCEPTION
2147 WHEN OTHERS THEN
2148 ResultOut := 'ERROR:';
2149 WF_ENGINE.SetItemAttrText
2150 ( ItemType => ItemType
2151 , ItemKey => ItemKey
2152 , AName => 'ERRORTEXT'
2153 , AValue => sqlerrm );
2154 WF_Core.Context
2155 ( 'OKE_DTS_INTEGRATION_PKG'
2156 , 'CREATE_EVENT'
2157 , ItemType
2158 , ItemKey
2159 , to_char(ActID)
2160 , FuncMode
2161 , ResultOut );
2162 RAISE;
2163 END Get_Charge_Account;
2164
2165 PROCEDURE Launch_Process
2166 ( P_ACTION IN VARCHAR2
2167 , P_API_VERSION IN NUMBER
2168 , P_COUNTRY_OF_ORIGIN_CODE IN VARCHAR2
2169 , P_CURRENCY_CODE IN VARCHAR2
2170 , P_DELIVERABLE_ID IN NUMBER
2171 , P_DELIVERABLE_NUM IN VARCHAR2
2172 , P_INIT_MSG_LIST IN VARCHAR2
2173 , P_INSPECTION_REQED IN VARCHAR2
2174 , P_ITEM_DESCRIPTION IN VARCHAR2
2175 , P_ITEM_ID IN NUMBER
2176 , P_ITEM_NUM IN VARCHAR2
2177 , P_K_HEADER_ID IN NUMBER
2178 , P_K_NUMBER IN VARCHAR2
2179 , P_LINE_NUMBER IN VARCHAR2
2180 , P_MPS_TRANSACTION_ID IN NUMBER
2181 , P_ORGANIZATION IN VARCHAR2
2182 , P_ORGANIZATION_ID IN NUMBER
2183 , P_PROJECT_ID IN NUMBER
2184 , P_PROJECT_NUM IN VARCHAR2
2185 , P_QUANTITY IN NUMBER
2186 , P_SCHEDULE_DATE IN DATE
2187 , P_SCHEDULE_DESIGNATOR IN VARCHAR2
2188 , P_SHIP_TO_LOCATION IN VARCHAR2
2189 , P_TASK_ID IN NUMBER
2190 , P_TASK_NUM IN VARCHAR2
2191 , P_UNIT_NUMBER IN VARCHAR2
2192 , P_UOM_CODE IN VARCHAR2
2193 , P_WORK_DATE IN DATE
2194 , P_REQUESTOR IN VARCHAR2 := NULL
2195 )IS
2196 L_WF_Item_Type VARCHAR2(8) ;
2197 L_WF_Process VARCHAR2(240) ;
2198 L_WF_Item_Key VARCHAR2(240) ;
2199 L_WF_User_Key VARCHAR2(240) ;
2200 L_REQUESTOR VARCHAR2(240) := NVL(P_REQUESTOR,FND_GLOBAL.User_Name);
2201 BEGIN
2202 L_WF_Item_Type :='OKEDTS';
2203
2204 IF P_ACTION = 'PLAN' THEN
2205 L_WF_Process := 'OKEDTSPLAN';
2206 ELSIF P_ACTION = 'SHIP' THEN
2207 L_WF_Process := 'OKEDTSSHIP';
2208 ELSIF P_ACTION = 'REQ' THEN
2209 L_WF_Process := 'OKEDTSREQ';
2210 END IF;
2211
2212 L_WF_Item_Key := P_Deliverable_ID || ':' ||
2213 to_char(sysdate , 'DDMONRRHH24MISS');
2214
2215 L_WF_User_Key := P_K_NUMBER || ':' ||
2216 P_LINE_NUMBER || ':' ||
2217 P_Deliverable_Num || ':' ||
2218 L_WF_Process || ':' ||
2219 P_Deliverable_ID || ':' ||
2220 to_char(sysdate , 'DDMONRRHH24MISS');
2221
2222
2223 WF_Engine.CreateProcess
2224 ( ItemType => L_WF_Item_Type
2225 , ItemKey => L_WF_Item_Key
2226 , Process => L_WF_Process);
2227
2228 WF_Engine.SetItemOwner
2229 ( ItemType => L_WF_Item_Type
2230 , ItemKey => L_WF_Item_Key
2231 , Owner => L_REQUESTOR);
2232
2233 WF_Engine.SetItemUserKey
2234 ( ItemType => L_WF_Item_Type
2235 , ItemKey => L_WF_Item_Key
2236 , UserKey => L_WF_User_Key);
2237
2238 --
2239 -- Setting various Workflow Item Attributes
2240 --
2241
2242 WF_ENGINE.SetItemAttrText
2243 ( ItemType => L_WF_Item_Type
2244 , ItemKey => L_WF_Item_Key
2245 , AName => 'ACTION'
2246 , AValue => P_ACTION );
2247
2248 WF_ENGINE.SetItemAttrNumber
2249 ( ItemType => L_WF_Item_Type
2250 , ItemKey => L_WF_Item_Key
2251 , AName => 'API_VERSION'
2252 , AValue => P_API_VERSION );
2253
2254 WF_ENGINE.SetItemAttrText
2255 ( ItemType => L_WF_Item_Type
2256 , ItemKey => L_WF_Item_Key
2257 , AName => 'COUNTRY_OF_ORIGIN_CODE'
2258 , AValue => P_COUNTRY_OF_ORIGIN_CODE );
2259
2260 WF_ENGINE.SetItemAttrText
2261 ( ItemType => L_WF_Item_Type
2262 , ItemKey => L_WF_Item_Key
2263 , AName => 'CURRENCY_CODE'
2264 , AValue => P_CURRENCY_CODE );
2265
2266 WF_ENGINE.SetItemAttrNumber
2267 ( ItemType => L_WF_Item_Type
2268 , ItemKey => L_WF_Item_Key
2269 , AName => 'DELIVERABLE_ID'
2270 , AValue => P_DELIVERABLE_ID );
2271
2272 WF_ENGINE.SetItemAttrText
2273 ( ItemType => L_WF_Item_Type
2274 , ItemKey => L_WF_Item_Key
2275 , AName => 'DELIVERABLE_NUM'
2276 , AValue => P_DELIVERABLE_NUM );
2277
2278 WF_ENGINE.SetItemAttrText
2279 ( ItemType => L_WF_Item_Type
2280 , ItemKey => L_WF_Item_Key
2281 , AName => 'INIT_MSG_LIST'
2282 , AValue => P_INIT_MSG_LIST );
2283
2284 WF_ENGINE.SetItemAttrText
2285 ( ItemType => L_WF_Item_Type
2286 , ItemKey => L_WF_Item_Key
2287 , AName => 'INSPECTION_REQED'
2288 , AValue => P_INSPECTION_REQED );
2289
2290 WF_ENGINE.SetItemAttrText
2291 ( ItemType => L_WF_Item_Type
2292 , ItemKey => L_WF_Item_Key
2293 , AName => 'ITEM_DESCRIPTION'
2294 , AValue => P_ITEM_DESCRIPTION );
2295
2296 WF_ENGINE.SetItemAttrNumber
2297 ( ItemType => L_WF_Item_Type
2298 , ItemKey => L_WF_Item_Key
2299 , AName => 'ITEM_ID'
2300 , AValue => P_ITEM_ID );
2301
2302 WF_ENGINE.SetItemAttrText
2303 ( ItemType => L_WF_Item_Type
2304 , ItemKey => L_WF_Item_Key
2305 , AName => 'ITEM_NUM'
2306 , AValue => P_ITEM_NUM );
2307
2308 WF_ENGINE.SetItemAttrNumber
2309 ( ItemType => L_WF_Item_Type
2310 , ItemKey => L_WF_Item_Key
2311 , AName => 'K_HEADER_ID'
2312 , AValue => P_K_HEADER_ID );
2313
2314 WF_ENGINE.SetItemAttrText
2315 ( ItemType => L_WF_Item_Type
2316 , ItemKey => L_WF_Item_Key
2317 , AName => 'K_NUMBER'
2318 , AValue => P_K_NUMBER );
2319
2320 WF_ENGINE.SetItemAttrText
2321 ( ItemType => L_WF_Item_Type
2322 , ItemKey => L_WF_Item_Key
2323 , AName => 'LINE_NUMBER'
2324 , AValue => P_LINE_NUMBER );
2325
2326 WF_ENGINE.SetItemAttrNumber
2327 ( ItemType => L_WF_Item_Type
2328 , ItemKey => L_WF_Item_Key
2329 , AName => 'MPS_TRANSACTION_ID'
2330 , AValue => P_MPS_TRANSACTION_ID );
2331
2332 WF_ENGINE.SetItemAttrText
2333 ( ItemType => L_WF_Item_Type
2334 , ItemKey => L_WF_Item_Key
2335 , AName => 'ORGANIZATION'
2336 , AValue => P_ORGANIZATION );
2337
2338 WF_ENGINE.SetItemAttrNumber
2339 ( ItemType => L_WF_Item_Type
2340 , ItemKey => L_WF_Item_Key
2341 , AName => 'ORGANIZATION_ID'
2342 , AValue => P_ORGANIZATION_ID );
2343
2344 WF_ENGINE.SetItemAttrNumber
2345 ( ItemType => L_WF_Item_Type
2346 , ItemKey => L_WF_Item_Key
2347 , AName => 'PROJECT_ID'
2348 , AValue => P_PROJECT_ID );
2349
2350 WF_ENGINE.SetItemAttrText
2351 ( ItemType => L_WF_Item_Type
2352 , ItemKey => L_WF_Item_Key
2353 , AName => 'PROJECT_NUM'
2354 , AValue => P_PROJECT_NUM );
2355
2356 WF_ENGINE.SetItemAttrNumber
2357 ( ItemType => L_WF_Item_Type
2358 , ItemKey => L_WF_Item_Key
2359 , AName => 'QUANTITY'
2360 , AValue => P_QUANTITY );
2361
2362 WF_ENGINE.SetItemAttrDate
2363 ( ItemType => L_WF_Item_Type
2364 , ItemKey => L_WF_Item_Key
2365 , AName => 'SCHEDULE_DATE'
2366 , AValue => P_SCHEDULE_DATE );
2367
2368 WF_ENGINE.SetItemAttrText
2369 ( ItemType => L_WF_Item_Type
2370 , ItemKey => L_WF_Item_Key
2371 , AName => 'SCHEDULE_DESIGNATOR'
2372 , AValue => P_SCHEDULE_DESIGNATOR );
2373
2374 WF_ENGINE.SetItemAttrText
2375 ( ItemType => L_WF_Item_Type
2376 , ItemKey => L_WF_Item_Key
2377 , AName => 'SHIP_TO_LOCATION'
2378 , AValue => P_SHIP_TO_LOCATION );
2379
2380 WF_ENGINE.SetItemAttrNumber
2381 ( ItemType => L_WF_Item_Type
2382 , ItemKey => L_WF_Item_Key
2383 , AName => 'TASK_ID'
2384 , AValue => P_TASK_ID );
2385
2386 WF_ENGINE.SetItemAttrText
2387 ( ItemType => L_WF_Item_Type
2388 , ItemKey => L_WF_Item_Key
2389 , AName => 'TASK_NUM'
2390 , AValue => P_TASK_NUM );
2391
2392 WF_ENGINE.SetItemAttrText
2393 ( ItemType => L_WF_Item_Type
2394 , ItemKey => L_WF_Item_Key
2395 , AName => 'UNIT_NUMBER'
2396 , AValue => P_UNIT_NUMBER );
2397
2398 WF_ENGINE.SetItemAttrText
2399 ( ItemType => L_WF_Item_Type
2400 , ItemKey => L_WF_Item_Key
2401 , AName => 'UOM_CODE'
2402 , AValue => P_UOM_CODE );
2403
2404 WF_ENGINE.SetItemAttrDate
2405 ( ItemType => L_WF_Item_Type
2406 , ItemKey => L_WF_Item_Key
2407 , AName => 'WORK_DATE'
2408 , AValue => P_WORK_DATE );
2409
2410 WF_ENGINE.SetItemAttrText
2411 ( ItemType => L_WF_Item_Type
2412 , ItemKey => L_WF_Item_Key
2413 , AName => 'REQUESTOR'
2414 , AValue => l_requestor );
2415
2416 WF_ENGINE.SetItemAttrNumber
2417 ( ItemType => L_WF_Item_Type
2418 , ItemKey => L_WF_Item_Key
2419 , AName => 'CHARGE_ACCOUNT'
2420 , AValue => NULL );
2421 --
2422 -- Start the Workflow Process
2423 --
2424 WF_ENGINE.StartProcess( ItemType => L_WF_Item_Type
2425 , ItemKey => L_WF_Item_Key );
2426
2427
2428 END Launch_Process;
2429
2430 FUNCTION Charge_Account ( P_Item_ID NUMBER, P_Org_ID NUMBER) RETURN NUMBER IS
2431
2432 CURSOR Item_C IS
2433 SELECT Expense_Account
2434 FROM mtl_system_items
2435 WHERE Inventory_Item_ID = P_Item_ID
2436 AND Organization_ID = P_Org_ID;
2437
2438 CURSOR Org_C IS
2439 SELECT Expense_Account
2440 FROM mtl_parameters
2441 WHERE ORGANIZATION_ID = P_Org_ID;
2442
2443 L_Account NUMBER;
2444
2445
2446 BEGIN
2447
2448
2449
2450 IF P_Item_ID > 0 THEN
2451
2452 OPEN Item_C;
2453 FETCH Item_C INTO L_Account;
2454 CLOSE Item_C;
2455
2456 IF L_Account IS NULL THEN
2457
2458
2459
2460 OPEN Org_C;
2461 FETCH Org_C INTO L_Account;
2462 CLOSE Org_C;
2463
2464 END IF;
2465
2466 ELSE
2467
2468 OPEN Org_C;
2469 FETCH Org_C INTO L_Account;
2470 CLOSE Org_C;
2471
2472 END IF;
2473
2474
2475
2476 IF L_Account > 0 THEN
2477
2478
2479 RETURN L_Account;
2480
2481 ELSE
2482
2483
2484 RETURN NULL;
2485
2486 END IF;
2487
2488
2489
2490 END Charge_Account;
2491
2492 FUNCTION Get_WSH_Allowed_Cancel_Qty (
2493 P_DELIVERABLE_ID IN NUMBER
2494 ) RETURN NUMBER IS
2495 l_cancel_qty NUMBER := -1;
2496 l_return_status VARCHAR2(1) := 'S';
2497 l_msg_count NUMBER := 0;
2498 l_msg_data VARCHAR2(2000);
2499 BEGIN
2500 WSH_INTEGRATION.Get_Cancel_Qty_Allowed
2501 ( p_source_code => 'OKE',
2502 p_source_line_id => P_DELIVERABLE_ID,
2503 x_cancel_qty_allowed => l_cancel_qty,
2504 x_return_status => l_return_status,
2505 x_msg_count => l_msg_count,
2506 x_msg_data => l_msg_data
2507 );
2508 IF l_return_status <> 'S' THEN
2509 l_cancel_qty := 0;
2510 END IF;
2511 RETURN l_cancel_qty;
2512
2513 END Get_WSH_Allowed_Cancel_Qty;
2514
2515 PROCEDURE Cancel_Shipping(
2516 P_DELIVERABLE_ID IN NUMBER,
2517 X_CANCELLED_QTY OUT NOCOPY NUMBER,
2518 X_RETURN_STATUS OUT NOCOPY VARCHAR2
2519 ) IS
2520 l_header_id NUMBER;
2521 l_shipping_request_id NUMBER;
2522 l_uom_code VARCHAR2(3);
2523 l_changed_attributes WSH_INTERFACE.ChangedAttributeTabType;
2524
2525 CURSOR dlvbl_c IS
2526 SELECT k_header_id, uom_code, shipping_request_id
2527 FROM oke_k_deliverables_b
2528 where deliverable_id = p_deliverable_id;
2529
2530 CURSOR cancel_qty_c IS
2531 SELECT Nvl(Sum(CANCELLED_QUANTITY),0)
2532 FROM wsh_delivery_details
2533 where source_code = 'OKE'
2534 AND SOURCE_line_ID = p_deliverable_id
2535 START WITH DELIVERY_DETAIL_ID=l_shipping_request_id
2536 CONNECT BY PRIOR DELIVERY_DETAIL_ID = SPLIT_FROM_DELIVERY_DETAIL_ID;
2537 BEGIN
2538 OPEN dlvbl_c;
2539 FETCH dlvbl_c INTO l_header_id, l_uom_code, l_shipping_request_id;
2540 CLOSE dlvbl_c;
2541 l_changed_attributes(1).source_header_id := l_header_id;
2542 l_changed_attributes(1).original_source_line_id := p_deliverable_id;
2543 l_changed_attributes(1).shipped_flag := 'N';
2544 l_changed_attributes(1).source_line_id := P_DELIVERABLE_ID;
2545 l_changed_attributes(1).order_quantity_uom := l_uom_code;
2546 l_changed_attributes(1).source_code := 'OKE';
2547 l_changed_attributes(1).action_flag := 'U';
2548 l_changed_attributes(1).ordered_quantity := 0;
2549 WSH_INTERFACE.Update_Shipping_Attributes (
2550 p_source_code => 'OKE',
2551 p_changed_attributes => l_changed_attributes,
2552 x_return_status => X_RETURN_STATUS
2553 );
2554 IF x_return_status = 'S' THEN
2555 OPEN cancel_qty_c;
2556 FETCH cancel_qty_c INTO x_cancelled_qty;
2557 CLOSE cancel_qty_c;
2558 ELSE
2559 x_cancelled_qty := 0;
2560 END IF;
2561 END Cancel_Shipping;
2562
2563
2564
2565
2566 END;