[Home] [Help]
PACKAGE BODY: APPS.OKE_DTS_INTEGRATION_PKG
Source
1 PACKAGE BODY OKE_DTS_INTEGRATION_PKG AS
2 /* $Header: OKEINTGB.pls 120.14 2010/04/09 05:26:05 aveeraba 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 l_customer_item_id number := null;
623
624 cursor term
625 ( C_deliverable_id number
626 , C_term_code varchar2
627 ) is
628 select kt1.term_value_pk1 term_value
629 from oke_k_deliverables_b d
630 , oke_k_terms kt1
631 , ( select cle_id , cle_id_ascendant , level_sequence from okc_ancestrys
632 union all
633 select id , id , 99999 from okc_k_lines_b ) a
634 where d.deliverable_id = C_deliverable_id
635 and kt1.term_code = C_term_code
636 and kt1.k_header_id = d.k_header_id
637 and a.cle_id = d.k_line_id
638 and ( ( kt1.k_line_id is null and a.cle_id = a.cle_id_ascendant )
639 or kt1.k_line_id = a.cle_id_ascendant )
640 order by decode(kt1.k_line_id , null , 0 , a.level_sequence) desc;
641
642 CURSOR csr_dts_ship(p_id number) IS
643 SELECT shipping_request_id, in_process_flag, initiate_shipment_date
644 FROM oke_k_deliverables_b
645 WHERE deliverable_id = p_id
646 FOR UPDATE OF shipping_request_id, in_process_flag, initiate_shipment_date NOWAIT;
647
648 CURSOR l_line_csr(p_id number) IS
649 SELECT B.K_HEADER_ID
650 , H.CUST_PO_NUMBER
651 , H.CONTRACT_NUMBER
652 , H.AUTHORING_ORG_ID
653 , B.DELIVERABLE_NUM
654 , B.PROJECT_ID
655 , B.TASK_ID
656 , B.QUANTITY
657 , B.ITEM_ID
658 , B.SHIP_TO_LOCATION_ID
659 , B.UOM_CODE
660 , B.EXPECTED_SHIPMENT_DATE
661 , B.PROMISED_SHIPMENT_DATE
662 , B.SHIP_FROM_LOCATION_ID
663 , B.INVENTORY_ORG_ID
664 , T.DESCRIPTION
665 , B.COUNTRY_OF_ORIGIN_CODE
666 , DECODE(B.INSPECTION_REQ_FLAG , 'Y' , 'R' , 'N') INSPECTION_REQ_FLAG
667 , B.UNIT_NUMBER
668 , B.CURRENCY_CODE
669 , B.WEIGHT
670 , B.WEIGHT_UOM_CODE
671 , B.VOLUME
672 , B.VOLUME_UOM_CODE
673 FROM OKC_K_HEADERS_B H
674 , OKE_K_DELIVERABLES_B B
675 , OKE_K_DELIVERABLES_TL T
676 WHERE B.DELIVERABLE_ID = p_id
677 AND B.DELIVERABLE_ID = T.DELIVERABLE_ID
678 AND T.LANGUAGE = USERENV('LANG')
679 AND H.ID = B.K_HEADER_ID;
680
681 CURSOR Item_C ( P_ID NUMBER ) IS
682 SELECT Decode(MTL_Transactions_Enabled_Flag, 'Y', 'Y', 'N')
683 FROM mtl_system_items
684 WHERE Inventory_Item_ID = P_ID;
685
686 CURSOR OU_C ( P_Organization_ID NUMBER ) IS
687 SELECT OPERATING_UNIT
688 FROM ORG_ORGANIZATION_DEFINITIONS
689 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
690
691 -- linfo l_line_csr%rowtype;
692 L_Flag VARCHAR2(1);
693 l_cust_item_flag varchar2(1) := 'Y';
694
695 CURSOR csr_cust_item (p_deliverable_id NUMBER )
696 IS
697 SELECT l.customer_item_id, d.item_id, d.INVENTORY_ORG_ID
698 FROM oke_k_lines l,
699 oke_k_deliverables_b d
700 WHERE d.deliverable_id = p_deliverable_id
701 AND d.k_line_id = l.k_line_id
702 AND l.inventory_item_id = d.item_id;
703
704 CURSOR csr_validate_cust_item
705 IS
706 SELECT 'X'
707 FROM mtl_parameters m,
708 MTL_CUSTOMER_ITEM_XREFS x,
709 mtl_customer_items m
710 where m.ORGANIZATION_ID = l_INV_ORG_ID
711 AND x. MASTER_ORGANIZATION_ID= m.MASTER_ORGANIZATION_ID
712 AND X.INVENTORY_ITEM_ID = l_item_id
713 AND x.CUSTOMER_ITEM_ID = l_customer_item_id
714 AND m.customer_item_id = x.CUSTOMER_ITEM_ID
715 AND m.customer_id = l_customer_id;
716
717 BEGIN
718 l_return_status := OKE_API.START_ACTIVITY(
719 l_api_name,
720 OKE_API.G_FALSE,
721 '_PVT',
722 x_return_status);
723
724 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
725
726 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
727
728 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
729
730 RAISE OKE_API.G_EXCEPTION_ERROR;
731
732 END IF;
733
734 OPEN l_line_csr(P_DELIVERABLE_ID);
735 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
736 , 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
737 , 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;
738 CLOSE l_line_csr;
739
740 -- get the right ship_to_location_id
741 select location_id into l_ship_rec.ship_to_location_id
742 from oke_cust_site_uses_v
743 where id1 = l_ship_to_location_id;
744
745 -- get the customer_id from contract header party
746 -- currently use ship_to_location derive party_id from oke_cust_site_uses_v
747
748 select cust_account_id into l_customer_id
749 from oke_cust_site_uses_v
750 where id1 = l_ship_to_location_id;
751
752 -- Populate shipping terms for report purpose
753
754 FOR Item_Info IN Term(P_Deliverable_ID, 'OB_FOB') LOOP
755 L_Fob_Term := Item_Info.Term_Value;
756 EXIT WHEN L_Fob_Term IS NOT NULL;
757 END LOOP;
758
759 FOR Item_Info IN Term(P_Deliverable_ID, 'OB_SHIPMENT_PRIORITY') LOOP
760 L_Ship_Priority := Item_Info.Term_Value;
761 EXIT WHEN L_Ship_Priority IS NOT NULL;
762 END LOOP;
763
764 FOR Item_Info IN Term(P_Deliverable_ID, 'OB_SHIPPING_METHOD') LOOP
765 L_Ship_Method := Item_Info.Term_Value;
766 EXIT WHEN L_Ship_Method IS NOT NULL;
767 END LOOP;
768
769 FOR Item_Info IN Term(P_Deliverable_ID, 'OB_FREIGHT_TERMS') LOOP
770 L_Freight_Term := Item_Info.Term_Value;
771 EXIT WHEN L_Freight_Term IS NOT NULL;
772 END LOOP;
773
774 -- Check item specific info
775 OPEN Item_C ( L_Item_ID );
776 FETCH Item_C INTO L_Flag;
777 CLOSE Item_C;
778
779 -- Retrieve Operating Unit info
780 OPEN OU_C(l_INV_ORG_ID);
781 FETCH OU_C INTO L_Org_ID;
782 CLOSE OU_C;
783
784 -- Retrieve Customer item info
785 OPEN csr_cust_item(P_Deliverable_ID);
786 FETCH csr_cust_item INTO l_customer_item_id,l_item_id, l_INV_ORG_ID;
787 CLOSE csr_cust_item;
788
789 IF ( l_customer_item_id IS NOT NULL ) THEN
790 OPEN csr_validate_cust_item;
791 FETCH csr_validate_cust_item INTO l_cust_item_flag;
792 CLOSE csr_validate_cust_item;
793 IF (l_cust_item_flag <> 'X' ) THEN
794 l_customer_item_id :=NULL;
795 END IF;
796 END IF;
797
798
799 -- set record
800 l_ship_rec.delivery_detail_id := null;
801 l_ship_rec.source_code := G_WSH_SOURCE_CODE;
802 l_ship_rec.source_header_id := L_HEADER_ID;
803 l_ship_rec.source_line_id := P_DELIVERABLE_ID;
804 l_ship_rec.customer_id := l_customer_id;
805 l_ship_rec.sold_to_contact_id := null;
806 l_ship_rec.inventory_item_id := l_ITEM_ID;
807 l_ship_rec.item_description := L_DESCRIPTION;
808 l_ship_rec.hazard_class_id := null;
809 l_ship_rec.country_of_origin := l_COUNTRY_OF_ORIGIN_CODE;
810 l_ship_rec.classification := null;
811 l_ship_rec.ship_from_location_id := l_SHIP_FROM_LOCATION_ID;
812 l_ship_rec.ship_to_site_use_id := l_ship_to_location_id;
813 l_ship_rec.ship_to_contact_id := null;
814 l_ship_rec.deliver_to_location_id := null;
815 l_ship_rec.deliver_to_contact_id := null;
816 l_ship_rec.intmed_ship_to_location_id := null;
817 l_ship_rec.intmed_ship_to_contact_id := null;
818 l_ship_rec.hold_code := null;
819 l_ship_rec.ship_tolerance_above := null;
820 l_ship_rec.ship_tolerance_below := null;
821 -- l_ship_rec.requested_quantity := l_QUANTITY;
822 l_ship_rec.shipped_quantity := null;
823 l_ship_rec.delivered_quantity := null;
824 -- l_ship_rec.requested_quantity_uom := l_UOM_CODE;
825 l_ship_rec.subinventory := null;
826 l_ship_rec.revision := null;
827 l_ship_rec.lot_number := null;
828 l_ship_rec.customer_requested_lot_flag:= null;
829 l_ship_rec.serial_number := null;
830 l_ship_rec.locator_id := null;
831 l_ship_rec.date_requested := l_promised_date;
832 l_ship_rec.date_scheduled := l_expected_date;
833 l_ship_rec.master_container_item_id := null;
834 l_ship_rec.detail_container_item_id := null;
835 l_ship_rec.load_seq_number := null;
836 l_ship_rec.ship_method_code := l_ship_method;
837 l_ship_rec.carrier_id := null;
838 l_ship_rec.freight_terms_code := l_freight_term;
839 l_ship_rec.shipment_priority_code := l_ship_priority;
840 l_ship_rec.fob_code := l_fob_term;
841 l_ship_rec.customer_item_id := l_customer_item_id;
842 l_ship_rec.dep_plan_required_flag := null;
843 l_ship_rec.customer_prod_seq := null;
844 l_ship_rec.customer_dock_code := null;
845 l_ship_rec.net_weight := l_weight;
846 l_ship_rec.weight_uom_code := l_weight_uom_code;
847 l_ship_rec.volume := l_volume;
848 l_ship_rec.volume_uom_code := l_volume_uom_code;
849 l_ship_rec.tp_attribute_category := null;
850 l_ship_rec.tp_attribute1 := null;
851 l_ship_rec.tp_attribute2 := null;
852 l_ship_rec.tp_attribute3 := null;
853 l_ship_rec.tp_attribute4 := null;
854 l_ship_rec.tp_attribute5 := null;
855 l_ship_rec.tp_attribute6 := null;
856 l_ship_rec.tp_attribute7 := null;
857 l_ship_rec.tp_attribute8 := null;
858 l_ship_rec.tp_attribute9 := null;
859 l_ship_rec.tp_attribute10 := null;
860 l_ship_rec.tp_attribute11 := null;
861 l_ship_rec.tp_attribute12 := null;
862 l_ship_rec.tp_attribute13 := null;
863 l_ship_rec.tp_attribute14 := null;
864 l_ship_rec.tp_attribute15 := null;
865 l_ship_rec.attribute_category := null;
866 l_ship_rec.attribute1 := null;
867 l_ship_rec.attribute2 := null;
868 l_ship_rec.attribute3 := null;
869 l_ship_rec.attribute4 := null;
870 l_ship_rec.attribute5 := null;
871 l_ship_rec.attribute6 := null;
872 l_ship_rec.attribute7 := null;
873 l_ship_rec.attribute8 := null;
874 l_ship_rec.attribute9 := null;
875 l_ship_rec.attribute10 := null;
876 l_ship_rec.attribute11 := null;
877 l_ship_rec.attribute12 := null;
878 l_ship_rec.attribute13 := null;
879 l_ship_rec.attribute14 := null;
880 l_ship_rec.attribute15 := null;
881 l_ship_rec.created_by := fnd_global.user_id;
882 l_ship_rec.creation_date := sysdate;
883 l_ship_rec.last_update_date := sysdate;
884 l_ship_rec.last_update_login := fnd_global.login_id;
885 l_ship_rec.last_updated_by := fnd_global.user_id;
886 l_ship_rec.program_application_id := null;
887 l_ship_rec.program_id := null;
888 l_ship_rec.program_update_date := null;
889 l_ship_rec.request_id := null;
890 l_ship_rec.mvt_stat_status := null;
891 l_ship_rec.released_flag := null;
892 l_ship_rec.organization_id := l_INV_ORG_ID;
893 l_ship_rec.transaction_temp_id := null;
894 l_ship_rec.ship_set_id := null;
895 l_ship_rec.arrival_set_id := null;
896 l_ship_rec.ship_model_complete_flag := null;
897 l_ship_rec.top_model_line_id := null;
898 l_ship_rec.source_header_number := l_CONTRACT_NUMBER;
899 l_ship_rec.source_header_type_id := null;
900 l_ship_rec.source_header_type_name := null;
901 l_ship_rec.cust_po_number := l_CUST_PO_NUMBER;
902 l_ship_rec.ato_line_id := null;
903 l_ship_rec.src_requested_quantity := l_QUANTITY;
904 l_ship_rec.src_requested_quantity_uom := l_UOM_CODE;
905 l_ship_rec.move_order_line_id := null;
906 l_ship_rec.cancelled_quantity := null;
907 l_ship_rec.quality_control_quantity := null;
908 l_ship_rec.cycle_count_quantity := null;
909 l_ship_rec.tracking_number := null;
910 l_ship_rec.movement_id := null;
911 l_ship_rec.shipping_instructions := null;
912 l_ship_rec.packing_instructions := null;
913 l_ship_rec.project_id := l_PROJECT_ID;
914 l_ship_rec.task_id := l_TASK_ID;
915 l_ship_rec.org_id := l_org_id;
916 l_ship_rec.oe_interfaced_flag := null;
917 l_ship_rec.split_from_detail_id := null;
918 l_ship_rec.inv_interfaced_flag := null;
919 l_ship_rec.source_line_number := l_DELIVERABLE_NUM;
920 l_ship_rec.inspection_flag := l_INSPECTION_REQ_FLAG;
921 l_ship_rec.container_flag := null;
922 l_ship_rec.container_type_code := null;
923 l_ship_rec.container_name := null;
924 l_ship_rec.fill_percent := null;
925 l_ship_rec.gross_weight := null;
926 l_ship_rec.master_serial_number := null;
927 l_ship_rec.maximum_load_weight := null;
928 l_ship_rec.maximum_volume := null;
929 l_ship_rec.minimum_fill_percent := null;
930 l_ship_rec.seal_code := null;
931 l_ship_rec.unit_number := l_unit_number;
932 l_ship_rec.currency_code := l_currency_code;
933 l_ship_rec.freight_class_cat_id := null;
934 l_ship_rec.commodity_code_cat_id := null;
935 l_ship_rec.preferred_grade := null;
936 l_ship_rec.src_requested_quantity2 := null;
937 l_ship_rec.src_requested_quantity_uom2 := null;
938 l_ship_rec.requested_quantity2 := null;
939 l_ship_rec.shipped_quantity2 := null;
940 l_ship_rec.delivered_quantity2 := null;
941 l_ship_rec.cancelled_quantity2 := null;
942 l_ship_rec.quality_control_quantity2 := null;
943 l_ship_rec.cycle_count_quantity2 := null;
944 l_ship_rec.requested_quantity_uom2 := null;
945 -- l_ship_rec.sublot_number := null;
946 l_ship_rec.lpn_id := null;
947
948 -- bug 2424468, populate pickable_flag
949 l_ship_rec.pickable_flag := l_flag;
950
951 -- bug 3597451
952 -- Try to lock record and update data
953
954 FOR rec_dts_ship IN csr_dts_ship(p_deliverable_id) LOOP
955 /*Bug 6011322 start */
956 IF rec_dts_ship.shipping_request_id IS NOT NULL THEN
957 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_SHIP_EXISTS');
958 FND_MESSAGE.set_token('SHIPPING_DETAIL',rec_dts_ship.shipping_request_id);
959 FND_MSG_PUB.Add;
960 RAISE OKE_API.G_EXCEPTION_ERROR;
961 END IF;
962 /* Bug 6011322 end */
963
964 -- call api
965 WSH_interface_pub.create_shipment_lines(l_ship_rec, l_id, l_return_status);
966
967 if l_return_status = oke_api.g_ret_sts_success then
968 -- update deliverable table
969 update oke_k_deliverables_b
970 set shipping_request_id = l_id,
971 in_process_flag = 'Y',
972 initiate_shipment_date = sysdate
973 where CURRENT OF csr_dts_ship;
974
975 x_return_status := l_return_status;
976
977 else
978
979 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
980
981 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
982
983 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
984
985 RAISE OKE_API.G_EXCEPTION_ERROR;
986
987 END IF;
988
989 end if;
990
991 END LOOP;
992 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
993 COMMIT;
994
995
996
997 EXCEPTION
998 WHEN OKE_API.G_EXCEPTION_ERROR THEN
999
1000 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1001 (
1002 l_api_name,
1003 G_PKG_NAME,
1004 'OKE_API.G_RET_STS_ERROR',
1005 x_msg_count,
1006 x_msg_data,
1007 '_PVT');
1008 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1009 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1010 (
1011 l_api_name,
1012 G_PKG_NAME,
1013 'OKE_API.G_RET_STS_UNEXP_ERROR',
1014 x_msg_count,
1015 x_msg_data,
1016 '_PVT');
1017
1018 WHEN OTHERS THEN
1019 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1020 (
1021 l_api_name,
1022 G_PKG_NAME,
1023 'OTHERS',
1024 x_msg_count,
1025 x_msg_data,
1026 '_PVT');
1027
1028 END;
1029
1030 PROCEDURE create_req_line
1031 ( p_requestor in varchar2
1032 , p_deliverable_id in number
1033 , p_charge_account in number
1034 , x_batch_id out nocopy number
1035 , x_return_status out nocopy varchar2
1036 ) IS
1037
1038 l_return_status VARCHAR2(1);
1039 l_api_version CONSTANT NUMBER :=1;
1040 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_REQ_LINE';
1041 l_mps_id NUMBER;
1042
1043 x_msg_count number;
1044 x_msg_data varchar2(2000);
1045 l_id number;
1046 l_requestor varchar2(150);
1047 l_found boolean;
1048 l_employee_id number;
1049 l_project_id NUMBER;
1050 l_task_id NUMBER;
1051 l_inv_org_id NUMBER;
1052 l_dest_type VARCHAR2(30);
1053 l_exp_type VARCHAR2(30);
1054 l_exp_org_id NUMBER;
1055 l_exp_item_date DATE;
1056 l_need_by_date DATE;
1057 l_error_code VARCHAR2(80);
1058 l_context VARCHAR2(1);
1059 l_mds_id Number;
1060 l_sched_date DATE;
1061 L_Item_Id NUMBER;
1062 L_Plan Varchar2(80);
1063 L_Currency_Code VARCHAR2(80);
1064 L_Exchange_Rate NUMBER;
1065 L_Unit_Price NUMBER;
1066 L_Currency_Price NUMBER;
1067 l_ship_from_org_id NUMBER;
1068 l_vendor_id NUMBER;
1069 l_vendor_site_id NUMBER;
1070 l_ship_from_location_id NUMBER;
1071 L_Buy_Or_Sell VARCHAR2(1);
1072 L_Func_Currency_Code VARCHAR2(30);
1073 L_Req_Line_Type_ID NUMBER;
1074 L_Category_ID NUMBER;
1075 l_quantity NUMBER;
1076 l_description VARCHAR2(240);
1077 l_item_description VARCHAR2(240);
1078 l_uom_code VARCHAR2(30);
1079
1080
1081
1082 cursor c is
1083 select employee_id
1084 from fnd_user
1085 where user_name = l_requestor;
1086
1087 cursor c1 is
1088 select project_id
1089 , task_id
1090 , destination_type_code
1091 , expenditure_type
1092 , expenditure_organization_id
1093 , expenditure_item_date
1094 , inventory_org_id
1095 , trunc(need_by_date)
1096 , mps_transaction_id
1097 , ndb_schedule_designator
1098 , expected_shipment_date
1099 , item_id
1100 , unit_price
1101 , exchange_rate
1102 , ship_from_org_id
1103 , currency_code
1104 , ship_from_location_id
1105 , requisition_line_type_id
1106 , po_category_id
1107 , quantity
1108 , description
1109 , uom_code
1110 from oke_k_deliverables_vl
1111 where deliverable_id = p_deliverable_id;
1112
1113 CURSOR csr_dts_req(p_id number) IS
1114 SELECT po_ref_1, in_process_flag
1115 FROM oke_k_deliverables_b
1116 WHERE deliverable_id = p_id
1117 FOR UPDATE OF po_ref_1, in_process_flag NOWAIT;
1118 /* Bug Number: 6011322 start */
1119 cursor req_c(p_id number, p_batch_id number) is
1120 select 'S'
1121 from po_requisitions_interface_all
1122 where oke_contract_deliverable_id = p_id
1123 and nvl(process_flag, 'S') = 'ERROR'
1124 and batch_id = p_batch_id;
1125 /* Bug Number: 6011322 end */
1126
1127 --
1128 -- Cursor to validate PA information for Inventory
1129 --
1130 cursor pi 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_PROJECT_NOT_SETUP'
1139 from dual
1140 where not exists (
1141 select 'Project valid for PJM'
1142 from pjm_project_parameters
1143 where organization_id = l_inv_org_id
1144 and project_id = l_project_id )
1145 /* union all
1146 select 'OKE_DTS_EXP_TASK_INVALID'
1147 from dual
1148 where not exists (
1149 select 'Task valid and chargeable'
1150 from pa_tasks_expend_v t
1151 where project_id = l_project_id
1152 and task_id = l_task_id
1153 and chargeable_flag = 'Y' ) */
1154 union all
1155 select 'OKE_TASK_REQUIRED'
1156 from dual
1157 where not exists (
1158 select 'Task Reference OK'
1159 from pjm_org_parameters
1160 where organization_id = l_inv_org_id
1161 and not ( project_control_level = 2
1162 and l_task_id is null )
1163 );
1164
1165 --
1166 -- Cursor to validate PA information for Expense
1167 --
1168 cursor pe is
1169 select 'OKE_DTS_EXP_PROJECT_INVALID'
1170 from dual
1171 where not exists (
1172 select 'Project is valid'
1173 from pa_projects_expend_v
1174 where project_id = l_project_id )
1175 union all
1176 select 'OKE_DTS_EXP_TASK_INVALID'
1177 from dual
1178 where not exists (
1179 select 'Task is valid and chargeable'
1180 from pa_tasks_expend_v
1181 where project_id = l_project_id
1182 and task_id = l_task_id
1183 and chargeable_flag = 'Y' )
1184 union all
1185 select 'OKE_DTS_EXP_DATE_INVALID'
1186 from dual
1187 where not exists (
1188 select 'Date is valid for task'
1189 from pa_tasks t
1190 , pa_projects_all p
1191 where t.project_id = l_project_id
1192 and t.task_id = l_task_id
1193 and p.project_id = t.project_id
1194 and l_exp_item_date
1195 between nvl(t.start_date , nvl(p.start_date , l_exp_item_date - 1))
1196 and nvl(t.completion_date , nvl(p.completion_date , l_exp_item_date + 1)) )
1197 union all
1198 select 'OKE_DTS_EXP_TYPE_INVALID'
1199 from dual
1200 where not exists (
1201 select 'Expenditure Type exists and is valid'
1202 from pa_expenditure_types_expend_v
1203 where expenditure_type = l_exp_type
1204 and system_linkage_function = 'VI' )
1205 union all
1206 select 'OKE_DTS_EXP_ORG_INVALID'
1207 from dual
1208 where not exists (
1209 select 'Expenditure Org exists and is valid'
1210 from pa_organizations_expend_v
1211 where organization_id = l_exp_org_id );
1212
1213 CURSOR Curr_C ( P_ID NUMBER ) IS
1214 SELECT gl.Currency_Code
1215 FROM gl_sets_of_books gl, org_organization_definitions org
1216 WHERE org.organization_id = P_ID
1217 AND gl.Set_Of_Books_ID = org.Set_Of_Books_ID;
1218
1219 CURSOR Header_C IS
1220 SELECT Buy_Or_Sell
1221 FROM okc_k_headers_b
1222 WHERE ID = (SELECT K_Header_ID FROM oke_k_deliverables_b WHERE Deliverable_ID = P_Deliverable_ID);
1223
1224 BEGIN
1225 l_return_status := OKE_API.START_ACTIVITY(
1226 l_api_name,
1227 OKE_API.G_FALSE,
1228 '_PVT',
1229 x_return_status);
1230
1231 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1232
1233 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1234
1235 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1236
1237 RAISE OKE_API.G_EXCEPTION_ERROR;
1238
1239 END IF;
1240
1241
1242 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1243 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'start centrol logging debug session for okeintgb.pls');
1244 EnD IF;
1245
1246 -- populate preparer_id, requestor_id based on wf requestor
1247 l_requestor := p_requestor;
1248
1249 OPEN c;
1250 fetch c into l_employee_id;
1251 l_found := c%found;
1252 close c;
1253
1254 if l_found then
1255 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1256 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'preparer populated');
1257 END IF;
1258 --
1259 -- verify if the project and task supplied for projects
1260 --
1261 open c1;
1262
1263 fetch c1 into l_project_id , l_task_id , l_dest_type
1264 , l_exp_type , l_exp_org_id , l_exp_item_date
1265 , l_inv_org_id , l_need_by_date
1266 , l_mds_id , l_plan , l_sched_date , l_item_id
1267 , l_unit_price, l_exchange_rate, l_ship_from_org_id
1268 , l_currency_code, l_ship_from_location_id, l_req_line_type_id
1269 , l_category_id, l_quantity, l_description, l_uom_code;
1270
1271 close c1;
1272 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1273 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'line type is : ' || l_req_line_type_id);
1274 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'category is :' || l_category_id);
1275 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'description is : ' || l_description);
1276
1277 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'before validate project');
1278 END IF;
1279
1280 if ( l_project_id is not null ) then
1281
1282 l_context := 'Y';
1283
1284 --
1285 -- Validate PA information
1286 --
1287 if ( l_dest_type = 'INVENTORY' ) then
1288
1289 l_error_code := NULL;
1290 open pi;
1291 loop
1292 fetch pi into l_error_code;
1293 exit when pi%notfound;
1294 FND_MESSAGE.Set_Name('OKE' , l_error_code);
1295 FND_MSG_PUB.Add;
1296 end loop;
1297 close pi;
1298 if ( l_error_code is not null ) then
1299 RAISE OKE_API.G_EXCEPTION_ERROR;
1300 end if;
1301
1302 l_exp_type := NULL;
1303 l_exp_org_id := NULL;
1304 l_exp_item_date := NULL;
1305
1306 else /* destination type is EXPENSE */
1307
1308 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1309 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'called validate expense type');
1310 END IF;
1311 l_error_code := NULL;
1312 open pe;
1313 loop
1314 fetch pe into l_error_code;
1315 exit when pe%notfound;
1316 FND_MESSAGE.Set_Name('OKE' , l_error_code);
1317 FND_MSG_PUB.Add;
1318 end loop;
1319 close pe;
1320 if ( l_error_code is not null ) then
1321 RAISE OKE_API.G_EXCEPTION_ERROR;
1322 end if;
1323
1324 end if;
1325
1326 else /* project_id is null */
1327
1328 l_context := 'N';
1329
1330 end if;
1331
1332
1333 -- Get converted price based on the functional currency and contract currency
1334
1335 OPEN Curr_C(L_Inv_Org_ID);
1336 FETCH Curr_C INTO L_Func_Currency_Code;
1337 CLOSE Curr_C;
1338
1339 IF L_Func_Currency_Code <> L_Currency_Code THEN
1340
1341 if l_unit_price > 0 and l_exchange_rate > 0 then
1342
1343 l_currency_price := L_Unit_Price;
1344 l_unit_price := l_unit_price * l_exchange_rate;
1345
1346 end if;
1347
1348 END IF;
1349
1350 -- Get vendor info if buy contract
1351
1352 OPEN Header_C;
1353 FETCH Header_C INTO L_Buy_Or_Sell;
1354 CLOSE Header_C;
1355
1356 IF L_Buy_Or_Sell = 'B' THEN
1357
1358 L_Vendor_ID := L_Ship_From_Org_ID;
1359 L_Vendor_Site_ID := L_Ship_From_Location_ID;
1360
1361 END IF;
1362
1363 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1364 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'call before amount based');
1365 END IF;
1366 -- Amount based requisition logics
1367
1368 if l_dest_type = 'EXPENSE' then
1369
1370 if l_item_id is null and l_req_line_type_id > 0 then
1371 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1372 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'called amount based');
1373 END IF;
1374
1375 l_quantity := l_unit_price * l_quantity;
1376 l_unit_price := 1;
1377 l_item_description := l_description;
1378 l_uom_code := null;
1379
1380 end if;
1381
1382 -- bug 7651409
1383 IF l_item_id is null and l_req_line_type_id IS NULL THEN
1384 l_item_description := l_description;
1385 END IF;
1386
1387 end if;
1388
1389 -- bug 3597451
1390 -- Try to lock record and update data
1391
1392 FOR rec_dts_req IN csr_dts_req(p_deliverable_id) LOOP
1393 /*Bug Bumber: 6011322 */
1394 if rec_dts_req.po_ref_1 > 0 then
1395
1396 l_return_status := OKE_API.G_RET_STS_ERROR;
1397 open req_c(p_deliverable_id, rec_dts_req.po_ref_1);
1398 fetch req_c into l_return_status;
1399 close req_c;
1400 IF l_return_status = OKE_API.G_RET_STS_ERROR THEN
1401 FND_MESSAGE.Set_Name('OKE', 'OKE_DTS_REQ_EXISTS');
1402 FND_MSG_PUB.Add;
1403 RAISE OKE_API.G_EXCEPTION_ERROR;
1404 END IF;
1405
1406 end if;
1407
1408 -- unique group id to be associate with all rows in the table
1409 select oke_interface_s.nextval
1410 into l_id
1411 from dual;
1412 /* Bug Number: 6011322 end */
1413
1414 insert into po_requisitions_interface_all(
1415 last_updated_by,
1416 last_update_login,
1417 last_update_date,
1418 creation_date,
1419 created_by,
1420 item_id,
1421 quantity,
1422 unit_price,
1423 need_by_date,
1424 interface_source_code,
1425 deliver_to_location_id,
1426 deliver_to_requestor_id,
1427 preparer_id,
1428 source_type_code,
1429 authorization_status,
1430 uom_code,
1431 batch_id,
1432 charge_account_id,
1433 group_code,
1434 destination_organization_id,
1435 autosource_flag,
1436 org_id,
1437 project_id,
1438 task_id,
1439 project_accounting_context,
1440 oke_contract_header_id,
1441 oke_contract_version_id,
1442 oke_contract_line_id,
1443 oke_contract_deliverable_id,
1444 end_item_unit_number,
1445 expenditure_organization_id,
1446 expenditure_type,
1447 expenditure_item_date,
1448 destination_type_code,
1449 currency_code,
1450 rate,
1451 rate_date,
1452 rate_type,
1453 currency_unit_price,
1454 suggested_vendor_id,
1455 suggested_vendor_site_id,
1456 line_type_id,
1457 category_id,
1458 item_description)
1459 select fnd_global.user_id,
1460 fnd_global.login_id,
1461 sysdate,
1462 sysdate,
1463 fnd_global.user_id,
1464 d.item_id,
1465 l_quantity,
1466 l_unit_price,
1467 l_need_by_date,
1468 'OKE', -- hard code for OKE
1469 d.ship_to_location_id,
1470 l_employee_id,
1471 l_employee_id,
1472 'VENDOR',
1473 decode(d.destination_type_code, 'INVENTORY', 'APPROVED','INCOMPLETE'),
1474 l_uom_code,
1475 l_id,
1476 decode(d.destination_type_code, 'INVENTORY',mp.material_account, p_charge_account),
1477 null, -- to be added later if required
1478 d.inventory_org_id,
1479 'N', -- hard coded
1480 ood.operating_unit,
1481 l_project_id,
1482 l_task_id,
1483 l_context,
1484 d.k_header_id,
1485 ver.major_version,
1486 d.k_line_id,
1487 d.deliverable_id,
1488 d.unit_number,
1489 l_exp_org_id,
1490 l_exp_type,
1491 l_exp_item_date,
1492 l_dest_type,
1493 nvl(d.currency_code,l_func_currency_code),
1494 d.exchange_rate,
1495 d.rate_date,
1496 d.rate_type,
1497 l_currency_price,
1498 l_vendor_id,
1499 l_vendor_site_id,
1500 l_req_line_type_id,
1501 l_category_id,
1502 l_item_description
1503 from oke_k_deliverables_b d
1504 , okc_k_headers_b h
1505 , mtl_parameters mp
1506 , org_organization_definitions ood
1507 , oke_k_vers_numbers_v ver
1508 where d.deliverable_id = p_deliverable_id
1509 and h.id = d.k_header_id
1510 and ver.chr_id = d.k_header_id
1511 and ood.organization_id = d.inventory_org_id
1512 and mp.organization_id = d.inventory_org_id;
1513
1514
1515
1516 if l_return_status = oke_api.g_ret_sts_success then
1517 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1518 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Req created successfully, update deliverable table');
1519 END IF;
1520
1521 -- update deliverable table
1522 update oke_k_deliverables_b
1523 set po_ref_1 = l_id
1524 , in_process_flag = 'Y'
1525 where CURRENT OF csr_dts_req;
1526
1527 --
1528 -- Work around for Planning not recognize OKE records when link PO to the MDS entries
1529 -- created from DTS
1530 --
1531
1532 IF ( l_plan IS NOT NULL
1533 AND nvl(l_sched_date , l_need_by_date) IS NOT NULL
1534 AND l_item_id IS NOT NULL
1535 AND l_inv_org_id IS NOT NULL ) THEN
1536 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1537 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Update MDS');
1538 END IF;
1539
1540 create_mds_entry(
1541 P_DELIVERABLE_ID => P_DELIVERABLE_ID,
1542 X_OUT_ID => L_MPS_ID,
1543 X_RETURN_STATUS => L_RETURN_STATUS);
1544
1545 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1546
1547 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1548
1549 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1550
1551 RAISE OKE_API.G_EXCEPTION_ERROR;
1552
1553 END IF;
1554
1555 END IF;
1556
1557 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1558 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'Create Req process completed normally');
1559 END IF;
1560
1561 x_batch_id := l_id;
1562 x_return_status := l_return_status;
1563
1564
1565
1566 else
1567
1568 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1569
1570 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1571
1572 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1573
1574 RAISE OKE_API.G_EXCEPTION_ERROR;
1575
1576 END IF;
1577
1578 end if;
1579
1580
1581 END LOOP;
1582 COMMIT;
1583
1584
1585 end if;
1586
1587 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1588
1589
1590 EXCEPTION
1591 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1592
1593 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1594 (
1595 l_api_name,
1596 G_PKG_NAME,
1597 'OKE_API.G_RET_STS_ERROR',
1598 x_msg_count,
1599 x_msg_data,
1600 '_PVT');
1601 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1602 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1603 (
1604 l_api_name,
1605 G_PKG_NAME,
1606 'OKE_API.G_RET_STS_UNEXP_ERROR',
1607 x_msg_count,
1608 x_msg_data,
1609 '_PVT');
1610
1611 WHEN OTHERS THEN
1612 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1613 (
1614 l_api_name,
1615 G_PKG_NAME,
1616 'OTHERS',
1617 x_msg_count,
1618 x_msg_data,
1619 '_PVT');
1620
1621 END;
1622
1623 PROCEDURE create_actions
1624 ( P_API_VERSION IN NUMBER
1625 , P_REQUESTOR IN VARCHAR2
1626 , P_INIT_MSG_LIST IN VARCHAR2
1627 , X_MSG_COUNT OUT NOCOPY NUMBER
1628 , X_MSG_DATA OUT NOCOPY VARCHAR2
1629 , P_ACTION IN VARCHAR2
1630 , P_DELIVERABLE_ID IN NUMBER
1631 , P_CHARGE_ACCOUNT IN NUMBER
1632 , X_RESULT OUT NOCOPY NUMBER
1633 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
1634 ) IS
1635
1636 l_api_name varchar2(30);
1637 l_return_status varchar2(1) ;
1638
1639 BEGIN
1640
1641 l_api_name := 'CREATE_ACTIONS';
1642 l_return_status := OKE_API.G_RET_STS_SUCCESS;
1643
1644 l_return_status := OKE_API.START_ACTIVITY(
1645 l_api_name,
1646 OKE_API.G_FALSE,
1647 '_PVT',
1648 x_return_status);
1649
1650 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1651
1652 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1653
1654 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1655
1656 RAISE OKE_API.G_EXCEPTION_ERROR;
1657
1658 END IF;
1659
1660
1661
1662 IF P_ACTION = 'PLAN' THEN
1663
1664 create_mds_entry(
1665 P_DELIVERABLE_ID => P_DELIVERABLE_ID,
1666 X_OUT_ID => X_RESULT,
1667 X_RETURN_STATUS => L_RETURN_STATUS);
1668
1669 ELSIF P_ACTION = 'SHIP' THEN
1670
1671 create_ship_line(
1672 P_DELIVERABLE_ID => P_DELIVERABLE_ID,
1673 X_DELVIERY_DETAIL_ID => X_RESULT,
1674 X_RETURN_STATUS => L_RETURN_STATUS);
1675
1676 ELSIF P_ACTION = 'REQ' THEN
1677
1678 create_req_line(
1679 p_requestor => P_REQUESTOR,
1680 p_deliverable_id => P_DELIVERABLE_ID,
1681 p_charge_account => P_CHARGE_ACCOUNT,
1682 x_batch_id => X_RESULT,
1683 x_return_status => L_RETURN_STATUS);
1684
1685 END IF;
1686
1687
1688 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1689
1690 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1691
1692 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1693
1694 RAISE OKE_API.G_EXCEPTION_ERROR;
1695
1696 END IF;
1697
1698 x_return_status := l_return_status;
1699
1700 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1701
1702 EXCEPTION
1703 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1704
1705 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1706 (
1707 l_api_name,
1708 G_PKG_NAME,
1709 'OKE_API.G_RET_STS_ERROR',
1710 x_msg_count,
1711 x_msg_data,
1712 '_PVT');
1713 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1714 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1715 (
1716 l_api_name,
1717 G_PKG_NAME,
1718 'OKE_API.G_RET_STS_UNEXP_ERROR',
1719 x_msg_count,
1720 x_msg_data,
1721 '_PVT');
1722
1723 WHEN OTHERS THEN
1724 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1725 (
1726 l_api_name,
1727 G_PKG_NAME,
1728 'OTHERS',
1729 x_msg_count,
1730 x_msg_data,
1731 '_PVT');
1732
1733 END;
1734
1735 --
1736 -- Public Procedures
1737 --
1738 PROCEDURE Set_WF_Attributes
1739 ( ItemType IN VARCHAR2
1740 , ItemKey IN VARCHAR2
1741 , ActID IN NUMBER
1742 , FuncMode IN VARCHAR2
1743 , ResultOut OUT NOCOPY VARCHAR2
1744 ) IS
1745
1746 l_k_number varchar2(120);
1747 l_doc_type varchar2(80);
1748 --bug 9354391 modified line_number length to 150 as per table
1749 l_line_number varchar2(150);
1750 l_deliverable_num varchar2(120);
1751 --bug 7390122 added parameter l_description to display deliverable description
1752 l_description varchar2(240);
1753 --bug 7390122 end
1754 l_destination_type varchar2(80);
1755 l_expenditure_type varchar2(80);
1756 l_expenditure_item_date date;
1757 l_expenditure_org varchar2(240);
1758 l_need_by_date date;
1759
1760 l_ship_from_location_id number;
1761 l_org_id number;
1762 l_ship_from_location varchar2(80);
1763
1764 CURSOR c_common (p_id number) IS
1765 SELECT H.k_number_disp k_number
1766 , T.k_type_name doc_type
1767 , L.line_number line_number
1768 , D.deliverable_num deliverable_num
1769 , D.description description
1770 , h.authoring_org_id org_id
1771 --bug 7390122 changed oke_k_deliverables_b to oke_k_deliverables_vl as it has description
1772 FROM oke_k_deliverables_vl D
1773 , okc_k_lines_b L
1774 , oke_k_headers_v H
1775 , oke_k_types_vl T
1776 WHERE D.deliverable_id = p_id
1777 AND L.id = D.k_line_id
1778 AND H.k_header_id = L.dnz_chr_id
1779 AND T.k_type_code = H.k_type_code;
1780 -- crec c_common%rowtype;
1781
1782 CURSOR c_req (p_id number) IS
1783 SELECT D.destination_type_code destination_type
1784 , D.expenditure_type
1785 , D.expenditure_item_date
1786 , O.name expenditure_org
1787 , D.need_by_date
1788 FROM oke_k_deliverables_b D
1789 , hr_all_organization_units_tl O
1790 WHERE D.deliverable_id = p_id
1791 AND O.organization_id (+) = D.expenditure_organization_id
1792 AND O.language (+) = userenv('LANG');
1793 -- rrec c_req%rowtype;
1794
1795 CURSOR c_ship (p_id number) IS
1796 SELECT D.ship_from_location_id
1797 , L.location_code ship_from_location
1798 FROM oke_k_deliverables_b D
1799 , hr_locations_all_tl L
1800 WHERE D.deliverable_id = p_id
1801 AND L.location_id = D.ship_from_location_id
1802 AND L.language = userenv('LANG');
1803 -- srec c_ship%rowtype;
1804
1805 l_deliverable_id NUMBER;
1806 l_action VARCHAR2(30);
1807
1808 BEGIN
1809
1810 IF ( FuncMode = 'RUN' ) THEN
1811
1812 l_deliverable_id := WF_ENGINE.GetItemAttrNumber
1813 ( itemtype => ItemType
1814 , ItemKey => ItemKey
1815 , AName => 'DELIVERABLE_ID' );
1816
1817 l_action := WF_ENGINE.GetItemAttrText
1818 ( itemtype => ItemType
1819 , ItemKey => ItemKey
1820 , AName => 'ACTION' );
1821
1822 --
1823 -- Initializing common attributes
1824 --
1825
1826
1827 OPEN c_common(l_deliverable_id);
1828 --bug 7390122 added l_description for fetching description
1829 FETCH c_common INTO l_k_number, l_doc_type, l_line_number, l_deliverable_num,l_description,l_org_id;
1830 CLOSE c_common;
1831
1832 WF_ENGINE.SetItemAttrText
1833 ( ItemType => ItemType
1834 , ItemKey => ItemKey
1835 , AName => 'DOC_TYPE'
1836 , AValue => l_doc_type );
1837
1838 WF_ENGINE.SetItemAttrText
1839 ( ItemType => ItemType
1840 , ItemKey => ItemKey
1841 , AName => 'K_NUMBER'
1842 , AValue => l_k_number );
1843
1844 WF_ENGINE.SetItemAttrText
1845 ( ItemType => ItemType
1846 , ItemKey => ItemKey
1847 , AName => 'DELIVERABLE_NUM'
1848 , AValue => l_deliverable_num );
1849
1850 --bug 7390122 added function for setting description
1851 WF_ENGINE.SetItemAttrText
1852 ( ItemType => ItemType
1853 , ItemKey => ItemKey
1854 , AName => 'DESCRIPTION'
1855 , AValue => l_description );
1856
1857 WF_ENGINE.SetItemAttrText
1858 ( ItemType => ItemType
1859 , ItemKey => ItemKey
1860 , AName => 'LINE_NUMBER'
1861 , AValue => l_line_number );
1862
1863 WF_ENGINE.SetItemAttrNUMBER
1864 ( ItemType => ItemType
1865 , ItemKey => ItemKey
1866 , AName => 'ORG_ID'
1867 , AValue => l_org_id );
1868
1869 IF ( l_action = 'REQ' ) THEN
1870 --
1871 -- Initializing Requisition specific attributes
1872 --
1873
1874
1875 OPEN c_req(l_deliverable_id);
1876 FETCH c_req INTO l_destination_type, l_expenditure_type, l_expenditure_item_date, l_expenditure_org, l_need_by_date;
1877 CLOSE c_req;
1878
1879 WF_ENGINE.SetItemAttrText
1880 ( ItemType => ItemType
1881 , ItemKey => ItemKey
1882 , AName => 'DESTINATION_TYPE'
1883 , AValue => l_destination_type );
1884
1885 WF_ENGINE.SetItemAttrText
1886 ( ItemType => ItemType
1887 , ItemKey => ItemKey
1888 , AName => 'EXPENDITURE_TYPE'
1889 , AValue => l_expenditure_type );
1890
1891 WF_ENGINE.SetItemAttrText
1892 ( ItemType => ItemType
1893 , ItemKey => ItemKey
1894 , AName => 'EXPENDITURE_ORG'
1895 , AValue => l_expenditure_org );
1896
1897 WF_ENGINE.SetItemAttrDate
1898 ( ItemType => ItemType
1899 , ItemKey => ItemKey
1900 , AName => 'EXPENDITURE_ITEM_DATE'
1901 , AValue => l_expenditure_item_date );
1902
1903 WF_ENGINE.SetItemAttrDate
1904 ( ItemType => ItemType
1905 , ItemKey => ItemKey
1906 , AName => 'SCHEDULE_DATE'
1907 , AValue => l_need_by_date );
1908
1909 ELSIF ( l_action = 'SHIP' ) THEN
1910 --
1911 -- Initializing Shipping specific attributes
1912 --
1913
1914
1915 OPEN c_ship(l_deliverable_id);
1916 FETCH c_ship INTO l_ship_from_location_id, l_ship_from_location;
1917 CLOSE c_ship;
1918
1919 WF_ENGINE.SetItemAttrText
1920 ( ItemType => ItemType
1921 , ItemKey => ItemKey
1922 , AName => 'SHIP_FROM_LOCATION'
1923 , AValue => l_ship_from_location );
1924
1925 END IF;
1926
1927 ResultOut := 'COMPLETE:';
1928 RETURN;
1929
1930 END IF;
1931
1932 IF ( FuncMode = 'CANCEL' ) THEN
1933 ResultOut := '';
1934 RETURN;
1935 END IF;
1936
1937 IF ( FuncMode = 'TIMEOUT' ) THEN
1938 ResultOut := '';
1939 RETURN;
1940 END IF;
1941
1942 EXCEPTION
1943 WHEN OTHERS THEN
1944 ResultOut := 'ERROR:';
1945 WF_ENGINE.SetItemAttrText
1946 ( ItemType => ItemType
1947 , ItemKey => ItemKey
1948 , AName => 'ERRORTEXT'
1949 , AValue => sqlerrm );
1950 WF_Core.Context
1951 ( 'OKE_DTS_INTEGRATION_PKG'
1952 , 'SET_WF_ATTRIBUTES'
1953 , ItemType
1954 , ItemKey
1955 , to_char(ActID)
1956 , FuncMode
1957 , ResultOut );
1958 RAISE;
1959
1960 END Set_WF_Attributes;
1961
1962
1963 PROCEDURE Create_Event
1964 ( ItemType IN VARCHAR2
1965 , ItemKey IN VARCHAR2
1966 , ActID IN NUMBER
1967 , FuncMode IN VARCHAR2
1968 , ResultOut OUT NOCOPY VARCHAR2
1969 ) IS
1970
1971 l_api_version NUMBER;
1972 l_init_msg_list VARCHAR2(240);
1973 l_action VARCHAR2(30);
1974 l_requestor VARCHAR2(30);
1975 l_msg_count NUMBER;
1976 l_msg_data VARCHAR2(240);
1977 l_deliverable_id NUMBER;
1978 l_result NUMBER;
1979 l_return_status VARCHAR2(240);
1980 l_error_text VARCHAR2(4000);
1981 l_charge_account NUMBER;
1982
1983 i NUMBER;
1984
1985 BEGIN
1986
1987 IF ( FuncMode = 'RUN' ) THEN
1988
1989 l_api_version := WF_ENGINE.GetItemAttrNumber
1990 ( itemtype => ItemType
1991 , ItemKey => ItemKey
1992 , AName => 'API_VERSION' );
1993
1994 l_init_msg_list := WF_ENGINE.GetItemAttrText
1995 ( itemtype => ItemType
1996 , ItemKey => ItemKey
1997 , AName => 'INIT_MSG_LIST' );
1998
1999 l_deliverable_id := WF_ENGINE.GetItemAttrNumber
2000 ( itemtype => ItemType
2001 , ItemKey => ItemKey
2002 , AName => 'DELIVERABLE_ID' );
2003
2004 l_action := WF_ENGINE.GetItemAttrText
2005 ( itemtype => ItemType
2006 , ItemKey => ItemKey
2007 , AName => 'ACTION' );
2008
2009 l_requestor := WF_ENGINE.GetItemAttrText
2010 ( itemtype => ItemType
2011 , ItemKey => ItemKey
2012 , AName => 'REQUESTOR' );
2013
2014 l_charge_account := WF_ENGINE.GetItemAttrNumber
2015 ( itemtype => ItemType
2016 , ItemKey => ItemKey
2017 , AName => 'CHARGE_ACCOUNT' );
2018 FND_MSG_PUB.initialize;
2019
2020
2021
2022 OKE_DTS_INTEGRATION_PKG.CREATE_ACTIONS
2023 ( l_api_version
2024 , l_requestor
2025 , l_init_msg_list
2026 , l_msg_count
2027 , l_msg_data
2028 , l_action
2029 , l_deliverable_id
2030 , l_charge_account
2031 , l_result
2032 , l_return_status);
2033
2034 WF_ENGINE.SetItemAttrNumber
2035 ( ItemType => ItemType
2036 , ItemKey => ItemKey
2037 , AName => 'MSG_COUNT'
2038 , AValue => l_msg_count );
2039
2040 WF_ENGINE.SetItemAttrText
2041 ( ItemType => ItemType
2042 , ItemKey => ItemKey
2043 , AName => 'MSG_DATA'
2044 , AValue => l_msg_data );
2045
2046 IF ( l_action = 'PLAN' ) THEN
2047 WF_ENGINE.SetItemAttrNumber
2048 ( ItemType => ItemType
2049 , ItemKey => ItemKey
2050 , AName => 'MPS_ID'
2051 , AValue => l_result );
2052 ELSIF ( l_action = 'SHIP' ) THEN
2053 WF_ENGINE.SetItemAttrNumber
2054 ( ItemType => ItemType
2055 , ItemKey => ItemKey
2056 , AName => 'DELIVERY_DETAIL_ID'
2057 , AValue => l_result );
2058 ELSIF ( l_action = 'REQ' ) THEN
2059 WF_ENGINE.SetItemAttrNumber
2060 ( ItemType => ItemType
2061 , ItemKey => ItemKey
2062 , AName => 'BATCH_ID'
2063 , AValue => l_result );
2064 END IF;
2065
2066 WF_ENGINE.SetItemAttrText
2067 ( ItemType => ItemType
2068 , ItemKey => ItemKey
2069 , AName => 'RETURN_STATUS'
2070 , AValue => l_return_status );
2071
2072 IF ( l_return_status <> OKE_API.G_RET_STS_SUCCESS ) THEN
2073
2074 if ( l_msg_count = 1 ) then
2075 l_error_text := FND_MSG_PUB.Get( p_msg_index => 1 , p_encoded => 'F' );
2076 elsif ( l_msg_count > 1 ) then
2077 for i in 1..l_msg_count loop
2078 if ( l_error_text is null ) then
2079 l_error_text := i || '. ' ||
2080 fnd_msg_pub.get( p_msg_index => i , p_encoded => 'F' );
2081 else
2082 l_error_text := l_error_text || fnd_global.newline || fnd_global.newline ||
2083 i || '. ' ||
2084 fnd_msg_pub.get( p_msg_index => i , p_encoded => 'F' );
2085 end if;
2086 end loop;
2087 end if;
2088
2089 WF_ENGINE.SetItemAttrText
2090 ( ItemType => ItemType
2091 , ItemKey => ItemKey
2092 , AName => 'ERRORTEXT'
2093 , AValue => l_error_text );
2094
2095 ResultOut := 'COMPLETE:E';
2096 ELSE
2097 ResultOut := 'COMPLETE:S';
2098 END IF;
2099
2100 RETURN;
2101
2102 END IF;
2103
2104 IF ( FuncMode = 'CANCEL' ) THEN
2105 ResultOut := '';
2106 RETURN;
2107 END IF;
2108
2109 IF ( FuncMode = 'TIMEOUT' ) THEN
2110 ResultOut := '';
2111 RETURN;
2112 END IF;
2113
2114 EXCEPTION
2115 WHEN OTHERS THEN
2116 ResultOut := 'ERROR:';
2117 WF_ENGINE.SetItemAttrText
2118 ( ItemType => ItemType
2119 , ItemKey => ItemKey
2120 , AName => 'ERRORTEXT'
2121 , AValue => sqlerrm );
2122 WF_Core.Context
2123 ( 'OKE_DTS_INTEGRATION_PKG'
2124 , 'CREATE_EVENT'
2125 , ItemType
2126 , ItemKey
2127 , to_char(ActID)
2128 , FuncMode
2129 , ResultOut );
2130 RAISE;
2131 END CREATE_EVENT;
2132
2133 PROCEDURE Get_Charge_Account
2134 ( ItemType IN VARCHAR2
2135 , ItemKey IN VARCHAR2
2136 , ActID IN NUMBER
2137 , FuncMode IN VARCHAR2
2138 , ResultOut OUT NOCOPY VARCHAR2
2139 ) IS
2140
2141 l_item_id NUMBER;
2142 l_org_id NUMBER;
2143 l_charge_account NUMBER;
2144
2145
2146 i NUMBER;
2147
2148 BEGIN
2149
2150 IF ( FuncMode = 'RUN' ) THEN
2151
2152 l_item_id := WF_ENGINE.GetItemAttrNumber
2153 ( itemtype => ItemType
2154 , ItemKey => ItemKey
2155 , AName => 'ITEM_ID' );
2156
2157 l_Org_id := WF_ENGINE.GetItemAttrNumber
2158 ( itemtype => ItemType
2159 , ItemKey => ItemKey
2160 , AName => 'ORGANIZATION_ID' );
2161
2162
2163
2164
2165 l_charge_account := OKE_DTS_INTEGRATION_PKG.CHARGE_ACCOUNT(L_Item_ID, L_Org_ID);
2166
2167 WF_ENGINE.SetItemAttrNumber
2168 ( ItemType => ItemType
2169 , ItemKey => ItemKey
2170 , AName => 'CHARGE_ACCOUNT'
2171 , AValue => l_charge_account);
2172
2173 ResultOut := 'COMPLETE:S';
2174
2175 RETURN;
2176
2177 END IF;
2178
2179
2180 IF ( FuncMode = 'CANCEL' ) THEN
2181 ResultOut := '';
2182 RETURN;
2183 END IF;
2184
2185 IF ( FuncMode = 'TIMEOUT' ) THEN
2186 ResultOut := '';
2187 RETURN;
2188 END IF;
2189
2190 EXCEPTION
2191 WHEN OTHERS THEN
2192 ResultOut := 'ERROR:';
2193 WF_ENGINE.SetItemAttrText
2194 ( ItemType => ItemType
2195 , ItemKey => ItemKey
2196 , AName => 'ERRORTEXT'
2197 , AValue => sqlerrm );
2198 WF_Core.Context
2199 ( 'OKE_DTS_INTEGRATION_PKG'
2200 , 'CREATE_EVENT'
2201 , ItemType
2202 , ItemKey
2203 , to_char(ActID)
2204 , FuncMode
2205 , ResultOut );
2206 RAISE;
2207 END Get_Charge_Account;
2208
2209 PROCEDURE Launch_Process
2210 ( P_ACTION IN VARCHAR2
2211 , P_API_VERSION IN NUMBER
2212 , P_COUNTRY_OF_ORIGIN_CODE IN VARCHAR2
2213 , P_CURRENCY_CODE IN VARCHAR2
2214 , P_DELIVERABLE_ID IN NUMBER
2215 , P_DELIVERABLE_NUM IN VARCHAR2
2216 , P_INIT_MSG_LIST IN VARCHAR2
2217 , P_INSPECTION_REQED IN VARCHAR2
2218 , P_ITEM_DESCRIPTION IN VARCHAR2
2219 , P_ITEM_ID IN NUMBER
2220 , P_ITEM_NUM IN VARCHAR2
2221 , P_K_HEADER_ID IN NUMBER
2222 , P_K_NUMBER IN VARCHAR2
2223 , P_LINE_NUMBER IN VARCHAR2
2224 , P_MPS_TRANSACTION_ID IN NUMBER
2225 , P_ORGANIZATION IN VARCHAR2
2226 , P_ORGANIZATION_ID IN NUMBER
2227 , P_PROJECT_ID IN NUMBER
2228 , P_PROJECT_NUM IN VARCHAR2
2229 , P_QUANTITY IN NUMBER
2230 , P_SCHEDULE_DATE IN DATE
2231 , P_SCHEDULE_DESIGNATOR IN VARCHAR2
2232 , P_SHIP_TO_LOCATION IN VARCHAR2
2233 , P_TASK_ID IN NUMBER
2234 , P_TASK_NUM IN VARCHAR2
2235 , P_UNIT_NUMBER IN VARCHAR2
2236 , P_UOM_CODE IN VARCHAR2
2237 , P_WORK_DATE IN DATE
2238 , P_REQUESTOR IN VARCHAR2 := NULL
2239 )IS
2240 L_WF_Item_Type VARCHAR2(8) ;
2241 L_WF_Process VARCHAR2(240) ;
2242 L_WF_Item_Key VARCHAR2(240) ;
2243 L_WF_User_Key VARCHAR2(240) ;
2244 L_REQUESTOR VARCHAR2(240) := NVL(P_REQUESTOR,FND_GLOBAL.User_Name);
2245 BEGIN
2246 L_WF_Item_Type :='OKEDTS';
2247
2248 IF P_ACTION = 'PLAN' THEN
2249 L_WF_Process := 'OKEDTSPLAN';
2250 ELSIF P_ACTION = 'SHIP' THEN
2251 L_WF_Process := 'OKEDTSSHIP';
2252 ELSIF P_ACTION = 'REQ' THEN
2253 L_WF_Process := 'OKEDTSREQ';
2254 END IF;
2255
2256 L_WF_Item_Key := P_Deliverable_ID || ':' ||
2257 to_char(sysdate , 'DDMONRRHH24MISS');
2258
2259 L_WF_User_Key := P_K_NUMBER || ':' ||
2260 P_LINE_NUMBER || ':' ||
2261 P_Deliverable_Num || ':' ||
2262 L_WF_Process || ':' ||
2263 P_Deliverable_ID || ':' ||
2264 to_char(sysdate , 'DDMONRRHH24MISS');
2265
2266
2267 WF_Engine.CreateProcess
2268 ( ItemType => L_WF_Item_Type
2269 , ItemKey => L_WF_Item_Key
2270 , Process => L_WF_Process);
2271
2272 WF_Engine.SetItemOwner
2273 ( ItemType => L_WF_Item_Type
2274 , ItemKey => L_WF_Item_Key
2275 , Owner => L_REQUESTOR);
2276
2277 WF_Engine.SetItemUserKey
2278 ( ItemType => L_WF_Item_Type
2279 , ItemKey => L_WF_Item_Key
2280 , UserKey => L_WF_User_Key);
2281
2282 --
2283 -- Setting various Workflow Item Attributes
2284 --
2285
2286 WF_ENGINE.SetItemAttrText
2287 ( ItemType => L_WF_Item_Type
2288 , ItemKey => L_WF_Item_Key
2289 , AName => 'ACTION'
2290 , AValue => P_ACTION );
2291
2292 WF_ENGINE.SetItemAttrNumber
2293 ( ItemType => L_WF_Item_Type
2294 , ItemKey => L_WF_Item_Key
2295 , AName => 'API_VERSION'
2296 , AValue => P_API_VERSION );
2297
2298 WF_ENGINE.SetItemAttrText
2299 ( ItemType => L_WF_Item_Type
2300 , ItemKey => L_WF_Item_Key
2301 , AName => 'COUNTRY_OF_ORIGIN_CODE'
2302 , AValue => P_COUNTRY_OF_ORIGIN_CODE );
2303
2304 WF_ENGINE.SetItemAttrText
2305 ( ItemType => L_WF_Item_Type
2306 , ItemKey => L_WF_Item_Key
2307 , AName => 'CURRENCY_CODE'
2308 , AValue => P_CURRENCY_CODE );
2309
2310 WF_ENGINE.SetItemAttrNumber
2311 ( ItemType => L_WF_Item_Type
2312 , ItemKey => L_WF_Item_Key
2313 , AName => 'DELIVERABLE_ID'
2314 , AValue => P_DELIVERABLE_ID );
2315
2316 WF_ENGINE.SetItemAttrText
2317 ( ItemType => L_WF_Item_Type
2318 , ItemKey => L_WF_Item_Key
2319 , AName => 'DELIVERABLE_NUM'
2320 , AValue => P_DELIVERABLE_NUM );
2321
2322 WF_ENGINE.SetItemAttrText
2323 ( ItemType => L_WF_Item_Type
2324 , ItemKey => L_WF_Item_Key
2325 , AName => 'INIT_MSG_LIST'
2326 , AValue => P_INIT_MSG_LIST );
2327
2328 WF_ENGINE.SetItemAttrText
2329 ( ItemType => L_WF_Item_Type
2330 , ItemKey => L_WF_Item_Key
2331 , AName => 'INSPECTION_REQED'
2332 , AValue => P_INSPECTION_REQED );
2333
2334 WF_ENGINE.SetItemAttrText
2335 ( ItemType => L_WF_Item_Type
2336 , ItemKey => L_WF_Item_Key
2337 , AName => 'ITEM_DESCRIPTION'
2338 , AValue => P_ITEM_DESCRIPTION );
2339
2340 WF_ENGINE.SetItemAttrNumber
2341 ( ItemType => L_WF_Item_Type
2342 , ItemKey => L_WF_Item_Key
2343 , AName => 'ITEM_ID'
2344 , AValue => P_ITEM_ID );
2345
2346 WF_ENGINE.SetItemAttrText
2347 ( ItemType => L_WF_Item_Type
2348 , ItemKey => L_WF_Item_Key
2349 , AName => 'ITEM_NUM'
2350 , AValue => P_ITEM_NUM );
2351
2352 WF_ENGINE.SetItemAttrNumber
2353 ( ItemType => L_WF_Item_Type
2354 , ItemKey => L_WF_Item_Key
2355 , AName => 'K_HEADER_ID'
2356 , AValue => P_K_HEADER_ID );
2357
2358 WF_ENGINE.SetItemAttrText
2359 ( ItemType => L_WF_Item_Type
2360 , ItemKey => L_WF_Item_Key
2361 , AName => 'K_NUMBER'
2362 , AValue => P_K_NUMBER );
2363
2364 WF_ENGINE.SetItemAttrText
2365 ( ItemType => L_WF_Item_Type
2366 , ItemKey => L_WF_Item_Key
2367 , AName => 'LINE_NUMBER'
2368 , AValue => P_LINE_NUMBER );
2369
2370 WF_ENGINE.SetItemAttrNumber
2371 ( ItemType => L_WF_Item_Type
2372 , ItemKey => L_WF_Item_Key
2373 , AName => 'MPS_TRANSACTION_ID'
2374 , AValue => P_MPS_TRANSACTION_ID );
2375
2376 WF_ENGINE.SetItemAttrText
2377 ( ItemType => L_WF_Item_Type
2378 , ItemKey => L_WF_Item_Key
2379 , AName => 'ORGANIZATION'
2380 , AValue => P_ORGANIZATION );
2381
2382 WF_ENGINE.SetItemAttrNumber
2383 ( ItemType => L_WF_Item_Type
2384 , ItemKey => L_WF_Item_Key
2385 , AName => 'ORGANIZATION_ID'
2386 , AValue => P_ORGANIZATION_ID );
2387
2388 WF_ENGINE.SetItemAttrNumber
2389 ( ItemType => L_WF_Item_Type
2390 , ItemKey => L_WF_Item_Key
2391 , AName => 'PROJECT_ID'
2392 , AValue => P_PROJECT_ID );
2393
2394 WF_ENGINE.SetItemAttrText
2395 ( ItemType => L_WF_Item_Type
2396 , ItemKey => L_WF_Item_Key
2397 , AName => 'PROJECT_NUM'
2398 , AValue => P_PROJECT_NUM );
2399
2400 WF_ENGINE.SetItemAttrNumber
2401 ( ItemType => L_WF_Item_Type
2402 , ItemKey => L_WF_Item_Key
2403 , AName => 'QUANTITY'
2404 , AValue => P_QUANTITY );
2405
2406 WF_ENGINE.SetItemAttrDate
2407 ( ItemType => L_WF_Item_Type
2408 , ItemKey => L_WF_Item_Key
2409 , AName => 'SCHEDULE_DATE'
2410 , AValue => P_SCHEDULE_DATE );
2411
2412 WF_ENGINE.SetItemAttrText
2413 ( ItemType => L_WF_Item_Type
2414 , ItemKey => L_WF_Item_Key
2415 , AName => 'SCHEDULE_DESIGNATOR'
2416 , AValue => P_SCHEDULE_DESIGNATOR );
2417
2418 WF_ENGINE.SetItemAttrText
2419 ( ItemType => L_WF_Item_Type
2420 , ItemKey => L_WF_Item_Key
2421 , AName => 'SHIP_TO_LOCATION'
2422 , AValue => P_SHIP_TO_LOCATION );
2423
2424 WF_ENGINE.SetItemAttrNumber
2425 ( ItemType => L_WF_Item_Type
2426 , ItemKey => L_WF_Item_Key
2427 , AName => 'TASK_ID'
2428 , AValue => P_TASK_ID );
2429
2430 WF_ENGINE.SetItemAttrText
2431 ( ItemType => L_WF_Item_Type
2432 , ItemKey => L_WF_Item_Key
2433 , AName => 'TASK_NUM'
2434 , AValue => P_TASK_NUM );
2435
2436 WF_ENGINE.SetItemAttrText
2437 ( ItemType => L_WF_Item_Type
2438 , ItemKey => L_WF_Item_Key
2439 , AName => 'UNIT_NUMBER'
2440 , AValue => P_UNIT_NUMBER );
2441
2442 WF_ENGINE.SetItemAttrText
2443 ( ItemType => L_WF_Item_Type
2444 , ItemKey => L_WF_Item_Key
2445 , AName => 'UOM_CODE'
2446 , AValue => P_UOM_CODE );
2447
2448 WF_ENGINE.SetItemAttrDate
2449 ( ItemType => L_WF_Item_Type
2450 , ItemKey => L_WF_Item_Key
2451 , AName => 'WORK_DATE'
2452 , AValue => P_WORK_DATE );
2453
2454 WF_ENGINE.SetItemAttrText
2455 ( ItemType => L_WF_Item_Type
2456 , ItemKey => L_WF_Item_Key
2457 , AName => 'REQUESTOR'
2458 , AValue => l_requestor );
2459
2460 WF_ENGINE.SetItemAttrNumber
2461 ( ItemType => L_WF_Item_Type
2462 , ItemKey => L_WF_Item_Key
2463 , AName => 'CHARGE_ACCOUNT'
2464 , AValue => NULL );
2465 --
2466 -- Start the Workflow Process
2467 --
2468 WF_ENGINE.StartProcess( ItemType => L_WF_Item_Type
2469 , ItemKey => L_WF_Item_Key );
2470
2471
2472 END Launch_Process;
2473
2474 FUNCTION Charge_Account ( P_Item_ID NUMBER, P_Org_ID NUMBER) RETURN NUMBER IS
2475
2476 CURSOR Item_C IS
2477 SELECT Expense_Account
2478 FROM mtl_system_items
2479 WHERE Inventory_Item_ID = P_Item_ID
2480 AND Organization_ID = P_Org_ID;
2481
2482 CURSOR Org_C IS
2483 SELECT Expense_Account
2484 FROM mtl_parameters
2485 WHERE ORGANIZATION_ID = P_Org_ID;
2486
2487 L_Account NUMBER;
2488
2489
2490 BEGIN
2491
2492
2493
2494 IF P_Item_ID > 0 THEN
2495
2496 OPEN Item_C;
2497 FETCH Item_C INTO L_Account;
2498 CLOSE Item_C;
2499
2500 IF L_Account IS NULL THEN
2501
2502
2503
2504 OPEN Org_C;
2505 FETCH Org_C INTO L_Account;
2506 CLOSE Org_C;
2507
2508 END IF;
2509
2510 ELSE
2511
2512 OPEN Org_C;
2513 FETCH Org_C INTO L_Account;
2514 CLOSE Org_C;
2515
2516 END IF;
2517
2518
2519
2520 IF L_Account > 0 THEN
2521
2522
2523 RETURN L_Account;
2524
2525 ELSE
2526
2527
2528 RETURN NULL;
2529
2530 END IF;
2531
2532
2533
2534 END Charge_Account;
2535
2536 FUNCTION Get_WSH_Allowed_Cancel_Qty (
2537 P_DELIVERABLE_ID IN NUMBER
2538 ) RETURN NUMBER IS
2539 l_cancel_qty NUMBER := -1;
2540 l_return_status VARCHAR2(1) := 'S';
2541 l_msg_count NUMBER := 0;
2542 l_msg_data VARCHAR2(2000);
2543 BEGIN
2544 WSH_INTEGRATION.Get_Cancel_Qty_Allowed
2545 ( p_source_code => 'OKE',
2546 p_source_line_id => P_DELIVERABLE_ID,
2547 x_cancel_qty_allowed => l_cancel_qty,
2548 x_return_status => l_return_status,
2549 x_msg_count => l_msg_count,
2550 x_msg_data => l_msg_data
2551 );
2552 IF l_return_status <> 'S' THEN
2553 l_cancel_qty := 0;
2554 END IF;
2555 RETURN l_cancel_qty;
2556
2557 END Get_WSH_Allowed_Cancel_Qty;
2558
2559 PROCEDURE Cancel_Shipping(
2560 P_DELIVERABLE_ID IN NUMBER,
2561 X_CANCELLED_QTY OUT NOCOPY NUMBER,
2562 X_RETURN_STATUS OUT NOCOPY VARCHAR2
2563 ) IS
2564 l_header_id NUMBER;
2565 l_shipping_request_id NUMBER;
2566 l_uom_code VARCHAR2(3);
2567 l_changed_attributes WSH_INTERFACE.ChangedAttributeTabType;
2568
2569 CURSOR dlvbl_c IS
2570 SELECT k_header_id, uom_code, shipping_request_id
2571 FROM oke_k_deliverables_b
2572 where deliverable_id = p_deliverable_id;
2573
2574 CURSOR cancel_qty_c IS
2575 SELECT Nvl(Sum(CANCELLED_QUANTITY),0)
2576 FROM wsh_delivery_details
2577 where source_code = 'OKE'
2578 AND SOURCE_line_ID = p_deliverable_id
2579 START WITH DELIVERY_DETAIL_ID=l_shipping_request_id
2580 CONNECT BY PRIOR DELIVERY_DETAIL_ID = SPLIT_FROM_DELIVERY_DETAIL_ID;
2581 BEGIN
2582 OPEN dlvbl_c;
2583 FETCH dlvbl_c INTO l_header_id, l_uom_code, l_shipping_request_id;
2584 CLOSE dlvbl_c;
2585 l_changed_attributes(1).source_header_id := l_header_id;
2586 l_changed_attributes(1).original_source_line_id := p_deliverable_id;
2587 l_changed_attributes(1).shipped_flag := 'N';
2588 l_changed_attributes(1).source_line_id := P_DELIVERABLE_ID;
2589 l_changed_attributes(1).order_quantity_uom := l_uom_code;
2590 l_changed_attributes(1).source_code := 'OKE';
2591 l_changed_attributes(1).action_flag := 'U';
2592 l_changed_attributes(1).ordered_quantity := 0;
2593 WSH_INTERFACE.Update_Shipping_Attributes (
2594 p_source_code => 'OKE',
2595 p_changed_attributes => l_changed_attributes,
2596 x_return_status => X_RETURN_STATUS
2597 );
2598 IF x_return_status = 'S' THEN
2599 OPEN cancel_qty_c;
2600 FETCH cancel_qty_c INTO x_cancelled_qty;
2601 CLOSE cancel_qty_c;
2602 --bug 8320909 start
2603 UPDATE oke_k_deliverables_b
2604 SET quantity=quantity-x_cancelled_qty
2605 WHERE deliverable_id = p_deliverable_id;
2606 --bug 8320909 end
2607 ELSE
2608 x_cancelled_qty := 0;
2609 END IF;
2610 END Cancel_Shipping;
2611
2612
2613
2614
2615 END;