[Home] [Help]
PACKAGE BODY: APPS.MSC_WS_OTM_BPEL
Source
1 PACKAGE BODY MSC_WS_OTM_BPEL AS
2 /* $Header: MSCWOTMB.pls 120.16.12010000.6 2008/09/11 21:15:50 bnaghi ship $ */
3
4
5 g_UserId NUMBER:=0;
6 --========= PRIVATE FUNCTIONS ===========================
7 function GetLeadTime(itemId IN NUMBER,
8 orgId IN NUMBER,
9 planId IN NUMBER,
10 srInstanceId IN NUMBER,
11 newArrivalDate IN DATE,
12 adjustedArrivalDate OUT nocopy DATE) RETURN boolean;
13
14
15 function getLastRefreshNumber(orderNumber IN VARCHAR2,
16 lineNumber IN VARCHAR2,
17 releaseNumber IN VARCHAR2) RETURN NUMBER;
18
19 function GetProfilePlanId return NUMBER;
20
21 --========= IMPLEMENTATION ===========================
22
23 function GetLeadTime(itemId IN NUMBER,
24 orgId IN NUMBER,
25 planId IN NUMBER,
26 srInstanceId IN NUMBER,
27 newArrivalDate IN DATE,
28 adjustedArrivalDate OUT nocopy DATE) RETURN boolean is
29 v_leadTime NUMBER :=0;
30 v_offset NUMBER :=0;
31 d1 DATE;
32 d2 DATE;
33 d3 DATE;
34 d4 DATE;
35 calendarCode varchar2(100);
36 seq NUMBER :=0;
37 begin
38
39
40 /*dbms_output.put_line('itemId, ' ||itemId );
41 dbms_output.put_line(' orgid' || orgid );
42 dbms_output.put_line(' planId' || planId );
43 dbms_output.put_line('srInstanceId' || srInstanceId);*/
44
45 select POSTPROCESSING_LEAD_TIME into v_leadTime
46 from msc_system_items
47 where inventory_item_id = itemId
48 and organization_id = orgid -- You need both organziation id and inventory_item_id to get an unique item
49 and msc_system_items.plan_id = planId
50 and msc_system_items.sr_instance_id = srInstanceId;
51
52 if v_leadTime is null then
53 v_leadTime :=0;
54 end if;
55
56 --dbms_output.put_line('v_leadTime' || v_leadTime);
57
58 select calendar_code into calendarCode
59 from msc_trading_partners
60 where partner_type = 3
61 and sr_instance_id = srInstanceId
62 and sr_tp_id = orgId;
63
64 --dbms_output.put_line('Cal Code' || calendarCode);
65 --dbms_output.put_line('newArrivalDate' || newarrivalDate);
66
67 select n.calendar_date into d4
68 from msc_calendar_dates original,
69 msc_calendar_dates n
70 where original.calendar_code = calendarCode
71 and original.exception_set_id = -1
72 and original.sr_instance_id = srInstanceId
73 and original.calendar_date = newarrivalDate
74 and n.calendar_code = original.calendar_code
75 and n.exception_set_id = original.exception_set_id
76 and n.sr_instance_id = original.sr_instance_id
77 and n.seq_num = original.seq_num + v_leadTime;
78
79 -- SET D4 !!!!!!!!!!!!!
80 adjustedArrivalDate := d4;
81 return true;
82
83 EXCEPTION
84 WHEN no_data_found THEN
85 adjustedArrivalDate := newarrivalDate + v_leadTime;
86 return true;
87 when others then
88 --dbms_output.put_line('Error in calendar');
89 return false;
90
91 end GetLeadTime;
92
93
94
95 procedure UpdateKeyDateInCP ( status OUT NOCOPY VARCHAR2) is
96 cursor getLineIds is
97 SELECT
98 po_line_location_id , UPDATED_ARRIVAL_DATE
99 FROM
100 MSC_transportation_updates
101 WHERE order_type = 1;
102
103 v_line_location_id NUMBER :=0;
104 v_arrival_Date DATE;
105
106 begin
107 AppsInit;
108
109 OPEN getLineIds;
110 LOOP
111 FETCH getLineIds into v_line_location_id, v_arrival_Date;
112 EXIT WHEN getLineIds%NOTFOUND;
113 Update_CP(v_line_location_id,v_arrival_Date, status );
114
115 END LOOP;
116 CLOSE getLineIds;
117
118 end UpdateKeyDateInCP;
119
120
121 procedure UpdateCP_1 ( tranzId IN NUMBER,
122 status OUT NOCOPY VARCHAR2) is
123 v_line_location_id NUMBER :=0;
124 v_arrival_Date DATE;
125
126 begin
127 if tranzId is null then
128 status := 'NO_RECORD_TO_UPDATE';
129 return;
130 end if;
131 AppsInit;
132
133 SELECT po_line_location_id , UPDATED_ARRIVAL_DATE
134 INTO v_line_location_id, v_arrival_Date
135 FROM MSC_transportation_updates
136 WHERE order_type = 1
137 AND trans_Update_id = tranzId;
138
139 Update_CP(v_line_location_id,v_arrival_Date, status );
140
141 end UpdateCP_1;
142
143 procedure Update_CP ( lineLocationId IN NUMBER, arrivalDate IN DATE, status OUT NOCOPY VARCHAR2) is
144 v_cnt NUMBER :=0;
145 v_last_refresh_number NUMBER;
146 v_old_key_date date;
147 v_order_Number varchar2(100);
148 orderNumber varchar2(240);
149 v_line_Number NUMBER;
150 lineNumber varchar2(100);
151 releaseNumber VARCHAR2(200);
152 srInstanceId NUMBER:=0;
153 v_order_Type NUMBER := 0;
154 req_id NUMBER :=0;
155 profile_exceptions NUMBER :=0;
156 userId NUMBER :=0;
157 location NUMBER :=1;
158 cursor lookUpSupplies( C_lineLocationId NUMBER, C_srInstanceId NUMBER) is
159 SELECT order_number, purch_line_num, order_type
160 --INTO v_order_Number, v_line_Number, v_order_type
161 FROM msc_supplies
162 WHERE msc_supplies.po_line_location_id = c_lineLocationId
163 and plan_id = -1
164 and msc_supplies.sr_instance_id = c_srInstanceId
165 and msc_supplies.order_type in (1, 11);
166
167
168 begin
169 srInstanceId := fnd_profile.value('MSC_EBS_INSTANCE_FOR_OTM');
170 userId := fnd_global.User_id();
171
172
173 OPEN lookUpSupplies(lineLocationId, srInstanceId);
174 LOOP
175 FETCH lookUpSupplies INTO v_order_Number, v_line_Number, v_order_type ;
176 EXIT WHEN lookUpSupplies%NOTFOUND;
177
178
179 if (v_order_type = 1) then
180 select decode(instr(v_order_Number,'('), 0, v_order_Number, substr(v_order_Number, 1, instr(v_order_Number,'(') - 1))
181 into orderNumber
182 from dual;
183
184 select decode(v_order_type, 1, nvl(substr(v_order_number,instr(v_order_number,'(')+1,instr(v_order_number,'(',1,2)-2
185 - instr(v_order_number,'(')),' ') , decode(instr(v_order_number,'('), 0, to_char(null),
186 substr(v_order_number, instr(v_order_number,'('))))
187 into releaseNumber
188 from dual;
189
190 else
191
192 orderNumber :=v_order_Number;
193
194 end if;
195
196
197
198
199 lineNumber := v_line_Number; -- number to chars conversion
200
201 select count(*) into v_cnt
202 from msc_sup_dem_entries
203 where msc_sup_dem_entries.order_number = to_char(orderNumber)
204 and msc_sup_dem_entries.line_number = to_number(lineNumber)
205 -- and msc_sup_dem_entries.release_number = releaseNumber
206 and msc_sup_dem_entries.publisher_order_type in (13, 15);
207
208
209 if v_cnt = 0 then
210 status := 'Order not in CP';
211 return;
212 end if;
213
214 v_last_refresh_number:= getLastRefreshNumber(orderNumber, lineNumber, releaseNumber);
215
216 if ( v_last_refresh_number = 0) then
217 status := 'UNKNOWN ERROR';
218 return;
219 end if;
220
221 v_old_key_date := getKeyDate(orderNumber, lineNumber, releaseNumber, v_last_refresh_number);
222
223 if ( v_old_key_date <> arrivalDate ) then
224 update msc_sup_dem_entries
225 set key_date = arrivalDate,
226 receipt_date = arrivalDate,
227 LAST_UPDATE_DATE = SYSDATE,
228 LAST_UPDATED_BY = userId
229 where msc_sup_dem_entries.order_number = orderNumber
230 and msc_sup_dem_entries.line_number = lineNumber
231 -- and msc_sup_dem_entries.release_number = releaseNumber
232 and msc_sup_dem_entries.last_refresh_number = v_last_refresh_number
233 and msc_sup_dem_entries.publisher_order_type in (13, 15)
234 and msc_sup_dem_entries.plan_id = -1;
235
236 -- dbms_output.put_line('arrivalDate= ' || arrivalDate || ' orderNumber=' || orderNumber||
237 -- ' lineNumber=' || lineNumber || ' releaseNumber= ' || releaseNumber ||
238 -- ' v_last_refresh_number=' || v_last_refresh_number);
239 end if;
240
241 ---- TO BE DONE
242 -- READ PROFILE FOR IF TO GENERATE EXCEPTIONS OR NOT
243
244
245 profile_exceptions := fnd_profile.value('MSC_WS_OTM_GEN_EXC_CP');
246 if ( profile_exceptions = 1) then
247 -- GENERATE EXCEPTIONS
248 req_id := fnd_request.submit_request('MSC','MSCXNETG','Exception Manager',NULL, false,
249 'Y', /*p_early_order*/
250 'N', /* p_changed_order */
251 'N',/* p_forecast_accuracy*/
252 'N',/* p_forecast_mismatch*/
253 'Y',/* p_late_order*/
254 'N',/* p_material_excess*/
255 'N', /* p_material_shortage*/
256 'N',/* p_performance*/
257 'N',/* p_potential_late_order*/
258 'Y',/* p_response_required*/
259 'N'); /* p_custom_exception*/
260
261
262 IF(req_id = 0) THEN
263 status := 'ERROR_GENERATING_EXCEPTIONS_CP' ;
264 return;
265 END IF ;
266 end if;
267 END LOOP;
268 CLOSE lookUpSupplies;
269 /* SELECT distinct order_number, purch_line_num, order_type
270 INTO v_order_Number, v_line_Number, v_order_type
271 FROM msc_supplies
272 WHERE msc_supplies.po_line_location_id = lineLocationId
273 and plan_id = -1
274 and msc_supplies.sr_instance_id = srInstanceId
275 -- and msc_supplies.order_type in (1, 11);
276 and msc_supplies.order_type=11;*/
277
278
279
280 status := 'SUCCESS';
281 EXCEPTION
282 when no_data_found then
283 status := 'No CP data found';
284 return;
285 when others then
286 status := 'ERROR in CP';
287
288 end Update_CP;
289
290 function getLastRefreshNumber(orderNumber IN VARCHAR2,
291 lineNumber IN VARCHAR2,
292 releaseNumber IN VARCHAR2) RETURN NUMBER is
293 v_last_refresh_number NUMBER :=0;
294 --cursor get_last(orN VARCHAR2, lN VARCHAR2, rN VARCHAR2) is
295
296 cursor get_last(orN VARCHAR2, lN VARCHAR2) is
297 SELECT
298 last_refresh_number
299 FROM
300 msc_sup_dem_entries
301 WHERE
302 msc_sup_dem_entries.order_number = orN
303 and msc_sup_dem_entries.line_number = lN
304 -- and msc_sup_dem_entries.release_number = rN
305 ORDER BY last_refresh_number DESC;
306
307 begin
308 -- don't loop , bring just the first element
309 -- OPEN get_last(orderNumber, lineNumber, releaseNumber);
310 OPEN get_last(orderNumber, lineNumber);
311
312 FETCH get_last into v_last_refresh_number;
313
314 CLOSE get_last;
315 return v_last_refresh_number;
316 end getLastRefreshNumber;
317
318 function getKeyDate(orderNumber IN VARCHAR2,
319 lineNumber IN VARCHAR2,
320 releaseNumber IN VARCHAR2,
321 lastRefreshNumber IN NUMBER) RETURN DATE is
322 v_old_key_date DATE;
323
324 begin
325 SELECT
326 key_date into v_old_key_date
327 FROM
328 msc_sup_dem_entries
329 WHERE
330 msc_sup_dem_entries.order_number = orderNumber
331 and msc_sup_dem_entries.line_number = lineNumber
332 --and msc_sup_dem_entries.release_number = releaseNumber
333 and msc_sup_dem_entries.last_refresh_number = lastRefreshNumber;
334
335 return v_old_key_date;
336 end getKeyDate;
337
338
339 procedure UpdatePDS( status OUT nocopy VARCHAR2) is
340
341 cursor c_getLineIds is
342 SELECT
343 order_type, TRANS_UPDATE_ID
344 FROM
345 MSC_TRANSPORTATION_UPDATES;
346
347 v_order_type NUMBER :=0;
348 v_id NUMBER :=0;
349 plan_id NUMBER :=0;
350
351 begin
352 AppsInit;
353
354
355 --plan_id := fnd_profile.value('MSC_PROD_PLAN_ID_FOR_OTM_UPDATES');
356 plan_id := GetProfilePlanId();
357
358 if ( plan_Id = -3 ) then -- planId is NONE
359 status := 'No Plan to Update.';
360 return;
361 end if;
362
363 begin
364 OPEN c_getLineIds;
365 LOOP
366 FETCH c_getLineIds into v_order_type, v_id;
367 EXIT WHEN c_getLineIds%NOTFOUND;
368 UpdatePDS_Order(v_id, v_order_type, status );
369 END LOOP;
370 CLOSE c_getLineIds;
371 end;
372
373 status := 'SUCCESS';
374
375 EXCEPTION when others then
376 status := 'EXCEPTION_IN_PDS';
377 END UpdatePDS;
378
379 procedure UpdatePDS_1( tranzId IN NUMBER,
380 bpelOrderType IN NUMBER,
381 status OUT nocopy VARCHAR2) is
382 plan_id NUMBER :=0;
383 begin
384
385 if tranzId is null then
386 status := 'NO_RECORD_TO_UPDATE';
387 return;
388 end if;
389
390 AppsInit;
391
392 --plan_id := fnd_profile.value('MSC_PROD_PLAN_ID_FOR_OTM_UPDATES');
393 plan_id := GetProfilePlanId();
394
395 if ( plan_Id = -3 ) then -- planId is NONE
396 status := 'No Plan to Update.';
397 return;
398 end if;
399
400 UpdatePDS_Order(tranzId, bpelOrderType, status );
401
402 EXCEPTION when others then
403 status := 'EXCEPTION_IN_PDS';
404
405 END UpdatePDS_1;
406
407
408
409 PROCEDURE UpdatePDS_Order( transId IN NUMBER ,
410 order_type IN NUMBER,
411 status OUT nocopy varchar2) IS
412 cursor getProductionPlans is
413 SELECT plans.plan_id
414 FROM msc_plans plans, msc_designators desig
415 WHERE plans.curr_plan_type in (1,2,3,5)
416 AND plans.organization_id = desig.organization_id
417 AND plans.sr_instance_id = desig.sr_instance_id
418 AND plans.compile_designator = desig.designator
419 AND NVL(desig.disable_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
420 AND plans.organization_selection <> 1
421 and desig.PRODUCTION = 1
422 AND NVL(plans.copy_plan_id,-1) = -1
423 AND NVL(desig.copy_designator_id, -1) = -1;
424
425 v_plan_id NUMBER:=0; -- in case planId = -2, we'll use local var
426 planId NUMBER :=0;
427
428 begin
429
430 --planId := fnd_profile.value('MSC_PROD_PLAN_ID_FOR_OTM_UPDATES');
431 planId := GetProfilePlanId();
432
433 if ( planId <> -2) then -- not ALL, but just single value
434 if (order_type = 1) then
435 UpdatePDS_PO(planId, transId, status);
436 else
437 UpdatePDS_SO(planId, transId, status);
438 end if;
439 end if;
440
441 if (planId = -2) then -- ALL PLANS
442 OPEN getProductionPlans;
443 LOOP
444 FETCH getProductionPlans into v_plan_id;
445 EXIT WHEN getProductionPlans%NOTFOUND;
446 if (order_type = 1) then
447 UpdatePDS_PO(v_plan_id, transId, status);
448 else
449 UpdatePDS_SO(v_plan_id, transId, status);
450 end if;
451 END LOOP;
452 CLOSE getProductionPlans;
453 end if;
454
455 -- no EXCEPTION handling here; let it go up to UpdatePds
456 end UpdatePDS_Order;
457
458 PROCEDURE UpdatePDS_PO( planId IN NUMBER,
459 transId IN NUMBER,
460 status OUT nocopy varchar2) IS
461 isPoShipment NUMBER :=0;
462 begin
463 UpdateNewColumnAndFirmDate_PO(planId, transId, isPoShipment, status);
464 if ( status = 'SUCCESS') then
465 GenerateException(planId, transId, isPoShipment, status);
466 end if;
467 end UpdatePDS_PO;
468
469 PROCEDURE UpdatePDS_SO( planId IN NUMBER,
470 transId IN NUMBER,
471 status OUT nocopy varchar2) IS
472 begin
473 UpdateNewColumnAndFirmDate_SO(planId, transId, status);
474 if ( status = 'SUCCESS') then
475 GenerateException_SO(planId, transId, status);
476 end if;
477 end UpdatePDS_SO;
478
479
480 PROCEDURE GenerateException( planId IN NUMBER,
481 transId IN NUMBER,
482 isPoShipment IN NUMBER,
483 status out nocopy varchar2) IS
484 newArrivalDate DATE;
485 srInstanceId NUMBER :=0;
486 v_org_id NUMBER :=0;
487 v_inv_item_id NUMBER:=0;
488 v_supplier_id NUMBER:=0;
489 v_q NUMBER :=0;
490 v_supplier_site_id NUMBER:=0;
491 v_source_sr_inst_id NUMBER :=0;
492 v_sr_org_id NUMBER :=0;
493 v_order_number varchar2(240);
494 userId NUMBER;
495 v_old_dock_date DATE;
496 excType NUMBER :=0;
497 countItemExc NUMBER :=0;
498 supp_Transaction_id NUMBER :=0;
499 count_exc_this_order NUMBER :=0;
500
501 -- IF I NEED TO PUT TRANSACTION_ID, I NEED TO GENERATE ONE EXCEPTION FOR EACH ROW !! IF NOT, JUST ONE EXC PER LINE ITEM
502 begin
503
504 if ( isPoShipment = 1) then
505 SELECT distinct s.transaction_id, tu.UPDATED_ARRIVAL_DATE, tu.EBS_SR_INSTANCE_ID, s.ORGANIZATION_ID, s.INVENTORY_ITEM_ID, s.supplier_id, s.supplier_site_id,
506 s.new_dock_date, s.order_number,
507 s.NEW_ORDER_QUANTITY, s.SOURCE_SR_INSTANCE_ID, s.SOURCE_ORGANIZATION_ID
508 INTO supp_Transaction_id, newArrivalDate, srInstanceId, v_org_id, v_inv_item_id, v_supplier_id, v_supplier_site_id, v_old_dock_date, v_order_number, v_q,
509 v_source_sr_inst_id, v_sr_org_id
510 FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
511 WHERE s.ORDER_TYPE = 11
512 AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
513 AND s.SUPPLIER_ID is not null
514 AND s.PLAN_ID = planId
515 AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
516 AND tu.TRANS_UPDATE_ID = transId;
517
518
519 else
520 SELECT distinct s.transaction_id, tu.UPDATED_ARRIVAL_DATE, tu.EBS_SR_INSTANCE_ID,s.ORGANIZATION_ID, s.INVENTORY_ITEM_ID, s.supplier_id,
521 s.supplier_site_id, s.new_dock_date, s.order_number,
522 s.NEW_ORDER_QUANTITY, s.SOURCE_SR_INSTANCE_ID,s.SOURCE_ORGANIZATION_ID
523 INTO supp_Transaction_id, newArrivalDate, srInstanceId, v_org_id, v_inv_item_id, v_supplier_id, v_supplier_site_id, v_old_dock_date, v_order_number, v_q,
524 v_source_sr_inst_id, v_sr_org_id
525 FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
526 WHERE s.ORDER_TYPE = 1
527 AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
528 AND s.PO_LINE_ID = tu.PO_LINE_ID
529 AND s.PLAN_ID =planId
530 AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
531 AND tu.TRANS_UPDATE_ID = transId;
532
533 end if;
534
535 --userId := fnd_global.USER_ID();
536 userId := g_UserId;
537
538 if v_old_dock_date < newArrivalDate then
539 excType := 119; -- late replenishment
540 else
541 excType := 118; -- early replenishment
542 end if;
543
544 select count(1)
545 into count_exc_this_order
546 from msc_exception_details
547 where exception_type = excType
548 and plan_id = planId
549 and organization_id =v_org_id
550 and inventory_item_id =v_inv_item_id
551 and resource_id =-1 and department_id = -1
552 and sr_Instance_id = srInstanceId
553 and supplier_id = v_supplier_id
554 and order_number =v_order_number;
555
556 if ( count_exc_this_order > 0) then
557 update msc_exception_details
558 set date2=newArrivalDate
559 where exception_type = excType
560 and plan_id = planId
561 and organization_id =v_org_id
562 and inventory_item_id =v_inv_item_id
563 and resource_id =-1 and department_id = -1
564 and sr_Instance_id = srInstanceId
565 and supplier_id = v_supplier_id
566 and order_number =v_order_number;
567
568 status := 'Exception for this order already inserted. Updated new Arrival Date';
569 return;
570 end if;
571
572
573
574 select count(1)
575 into countItemExc
576 from msc_item_exceptions
577 where plan_id = planId
578 and organization_id = v_org_id
579 and sr_Instance_id = srInstanceId
580 and inventory_item_id = v_inv_item_id
581 and exception_type = excType;
582
583
584
585 if ( countItemExc = 0) then
586 INSERT INTO msc_item_exceptions(plan_id, organization_id, sr_Instance_id, inventory_item_id, exception_type,
587 exception_group,
588 LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE ,CREATED_BY,
589 supplier_id, supplier_site_id, exception_count)
590 VALUES(planId, v_org_id, srInstanceId,v_inv_item_id, excType,
591 21,
592 SYSDATE, userId, SYSDATE, userId,
593 v_supplier_id, v_supplier_site_id, 1);
594
595 else
596 select exception_count
597 into countItemExc
598 from msc_item_exceptions
599 where plan_id = planId
600 and organization_id = v_org_id
601 and sr_Instance_id = srInstanceId
602 and inventory_item_id = v_inv_item_id
603 and exception_type = excType;
604
605 countItemExc := countItemExc +1;
606 --dbms_output.put_line('countExc=' || countItemExc);
607
608 update msc_item_exceptions
609 set exception_count = countItemExc,
610 LAST_UPDATE_DATE = SYSDATE,
611 LAST_UPDATED_BY = userId
612 where plan_id = planId
613 and organization_id = v_org_id
614 and sr_Instance_id = srInstanceId
615 and inventory_item_id = v_inv_item_id
616 and exception_type = excType;
617
618 end if;
619
620 INSERT into msc_exception_details
621 (
622 exception_detail_id, exception_type, plan_id, organization_id, inventory_item_id, resource_id, -- -1
623 department_id, sr_Instance_id, LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE ,CREATED_BY,
624 supplier_id, supplier_site_id, order_number, date2, date1, quantity, number1, number2,
625 transaction_id
626 )
627
628 VALUES (MSC_EXCEPTION_DETAILS_S.nextval, excType, planId, v_org_id, v_inv_item_id, -1,
629 -1, srInstanceId, SYSDATE, userId, SYSDATE, userId,
630 v_supplier_id, v_supplier_site_id, v_order_number, newArrivalDate, v_old_dock_date,v_q, v_sr_org_id, v_source_sr_inst_id,
631 supp_Transaction_id);
632
633
634 status := 'SUCCESS';
635
636 EXCEPTION
637 when no_data_found then
638 status := 'No exception generated';
639 return;
640 when others then
641 status := 'ERROR in Gen Exceptions';
642 return;
643
644 end GenerateException;
645
646 PROCEDURE GenerateException_SO( planId IN NUMBER,
647 transId IN NUMBER,
648 status out nocopy varchar2) IS
649 cursor GetSupplierDataForIR_shipment( srIId IN NUMBER) is
650 SELECT s2.transaction_id, s2.supplier_id, s2.supplier_site_id, s2.new_dock_date, s2.order_number, s2.INVENTORY_ITEM_ID,
651 s2.SOURCE_ORGANIZATION_ID , s2.ORGANIZATION_ID, s2.NEW_ORDER_QUANTITY, s2.SR_INSTANCE_ID
652 FROM MSC_SUPPLIES s2, MSC_SALES_ORDERS sO, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
653 WHERE s2.ORDER_TYPE = 11 -- IR Shipment
654 AND s2.PLAN_ID =planId
655 AND s2.SR_INSTANCE_ID = srIId
656 AND SO.SR_INSTANCE_ID = srIId
657 AND dd.SR_INSTANCE_ID = srIId
658 AND tu.EBS_SR_INSTANCE_ID = srIId
659 AND s2.REQ_LINE_ID = SO.ORIGINAL_SYSTEM_LINE_REFERENCE
660 AND sO.DEMAND_SOURCE_LINE = dd.SOURCE_LINE_ID
661 AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
662 AND tu.Trans_update_id = transId;
663
664 newArrivalDate DATE;
665 SrInstanceId NUMBER :=0;
666 v_org_id NUMBER :=0;
667 v_sr_org_id NUMBER :=0;
668 v_inv_item_id NUMBER:=0;
669 v_supplier_id NUMBER:=0;
670 v_q NUMBER :=0;
671 v_source_sr_Inst_id NUMBEr :=0;
672 v_supplier_site_id NUMBER:=0;
673 v_demand_id NUMBER :=0;
674 v_order_number varchar2(240);
675 userId NUMBER;
676 v_old_dock_date DATE;
677 excType NUMBER :=0;
678 countItemExc NUMBER :=0;
679 supp_Transaction_id NUMBER :=0;
680 ISOID1 NUMBER :=0;
681 count_exc_this_order NUMBER :=0;
682
683 -- IF I NEED TO PUT TRANSACTION_ID, I NEED TO GENERATE ONE EXCEPTION FOR EACH ROW !! IF NOT, JUST ONE EXC PER LINE ITEM
684 begin
685
686 select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID into newArrivalDate, SrInstanceId
687 from msc_transportation_updates
688 where TRANS_UPDATE_ID = transId;
689
690 SELECT distinct d.DEMAND_ID
691 INTO ISOID1
692 FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
693 WHERE d.SALES_ORDER_LINE_ID = dd.SOURCE_LINE_ID
694 AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
695 AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
696 AND d.PLAN_ID = planId
697 AND d.ORIGINATION_TYPE = 30
698 AND tu.Trans_update_id = transId;
699
700 SELECT distinct s.transaction_id, s.supplier_id, s.supplier_site_id, s.new_dock_date, s.order_number, d.INVENTORY_ITEM_ID,
701 d.SOURCE_ORGANIZATION_ID , d.ORGANIZATION_ID, s.NEW_ORDER_QUANTITY, s.SR_INSTANCE_ID--IR
702 INTO supp_Transaction_id, v_supplier_id, v_supplier_site_id, v_old_dock_date, v_order_number, v_inv_item_id,
703 v_sr_org_id, v_org_id, v_q, v_source_sr_Inst_id
704 FROM MSC_SUPPLIES s, MSC_DEMANDS d
705 WHERE s.ORDER_TYPE = 2 -- IR
706 AND s.TRANSACTION_ID = d.DISPOSITION_ID
707 AND s.PLAN_ID =planId
708 AND s.SR_INSTANCE_ID = SrInstanceId
709 AND d.DEMAND_ID = ISOID1;
710
711 /* if ( v_inv_item_id =0 ) then
712 OPEN GetSupplierDataForIR_shipment(SrInstanceId);
713 LOOP
714 FETCH GetSupplierDataForIR_shipment into supp_Transaction_id, v_supplier_id, v_supplier_site_id, v_old_dock_date, v_order_number, v_inv_item_id,
715 v_sr_org_id, v_org_id, v_q, v_source_sr_Inst_id;
716 EXIT WHEN GetSupplierDataForIR_shipment%NOTFOUND;
717 END LOOP;
718 CLOSE GetSupplierDataForIR_shipment;
719 end if;*/
720
721 --dbms_output.put_line('passed 2');
722
723 --userId := fnd_global.USER_ID();
724 userId := g_UserId;
725
726 if v_old_dock_date < newArrivalDate then
727 excType := 119; -- late replenishment
728 else
729 excType := 118; -- early replenishment
730 end if;
731
732
733 select count(1)
734 into count_exc_this_order
735 from msc_exception_details
736 where exception_type = excType
737 and plan_id = planId
738 and organization_id =v_org_id
739 and inventory_item_id =v_inv_item_id
740 and resource_id =-1 and department_id = -1
741 and sr_Instance_id = srInstanceId
742 and supplier_id = v_supplier_id
743 and order_number =v_order_number;
744
745 if ( count_exc_this_order > 0) then
746 update msc_exception_details
747 set date2=newArrivalDate
748 where exception_type = excType
749 and plan_id = planId
750 and organization_id =v_org_id
751 and inventory_item_id =v_inv_item_id
752 and resource_id =-1 and department_id = -1
753 and sr_Instance_id = srInstanceId
754 and supplier_id = v_supplier_id
755 and order_number =v_order_number;
756
757 status := 'Exception for this order already inserted. Updated new Arrival Date';
758 return;
759 end if;
760
761 select count(1)
762 into countItemExc
763 from msc_item_exceptions
764 where plan_id = planId
765 and organization_id = v_org_id
766 and sr_Instance_id = srInstanceId
767 and inventory_item_id = v_inv_item_id
768 and exception_type = excType;
769
770 --dbms_output.put_line('countItemExec=' || countItemExc);
771
772
773 if ( countItemExc = 0) then
774 INSERT INTO msc_item_exceptions(plan_id, organization_id, sr_Instance_id, inventory_item_id, exception_type,
775 exception_group,
776 LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE ,CREATED_BY,
777 supplier_id, supplier_site_id, exception_count)
778 VALUES(planId, v_org_id, srInstanceId,v_inv_item_id, excType,
779 21,
780 SYSDATE, userId, SYSDATE, userId,
781 v_supplier_id, v_supplier_site_id, 1);
782
783 else
784 /*dbms_output.put_line('plan_id=' || planId);
785 dbms_output.put_line('v_org_id=' || v_org_id);
786 dbms_output.put_line('srInstanceId=' || srInstanceId);
787 dbms_output.put_line('v_inv_item_id=' || v_inv_item_id);
788 dbms_output.put_line('excType=' || excType);*/
789
790 select exception_count
791 into countItemExc
792 from msc_item_exceptions
793 where plan_id = planId
794 and organization_id = v_org_id
795 and sr_Instance_id = srInstanceId
796 and inventory_item_id = v_inv_item_id
797 and exception_type = excType;
798
799 countItemExc := countItemExc +1;
800 --dbms_output.put_line('countExc=' || countItemExc);
801
802 update msc_item_exceptions
803 set exception_count = countItemExc,
804 LAST_UPDATE_DATE = SYSDATE,
805 LAST_UPDATED_BY = userId
806 where plan_id = planId
807 and organization_id = v_org_id
808 and sr_Instance_id = srInstanceId
809 and inventory_item_id = v_inv_item_id
810 and exception_type = excType;
811
812
813 end if;
814
815 --dbms_output.put_line(supp_Transaction_id);
816 INSERT into msc_exception_details
817 (
818 exception_detail_id, exception_type, plan_id, organization_id, inventory_item_id, resource_id, -- -1
819 department_id, sr_Instance_id, LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE ,CREATED_BY,
820 supplier_id, supplier_site_id, order_number, date2, date1, quantity, number1, number2,
821 transaction_id
822 )
823
824 VALUES (MSC_EXCEPTION_DETAILS_S.nextval, excType, planId, v_org_id, v_inv_item_id, -1,
825 -1, srInstanceId, SYSDATE, userId, SYSDATE, userId,
826 v_supplier_id, v_supplier_site_id, v_order_number, newArrivalDate, v_old_dock_date, v_q,
827 v_sr_org_id, v_source_sr_Inst_id, supp_Transaction_id);
828
829
830 status := 'SUCCESS';
831
832 EXCEPTION
833 when others then
834 status := 'ERROR in Gen Exceptions';
835
836
837 end GenerateException_SO;
838
839 PROCEDURE UpdateNewColumnAndFirmDate_PO( planId IN NUMBER,
840 transId IN NUMBER,
841 isPoShipment out nocopy NUMBER,
842 status out nocopy varchar2) IS
843
844 cursor GetPOIds is
845 SELECT s.TRANSACTION_ID
846 FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
847 WHERE s.ORDER_TYPE = 1
848 AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
849 AND s.PO_LINE_ID = tu.PO_LINE_ID
850 AND s.PLAN_ID =planId
851 AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
852 AND tu.TRANS_UPDATE_ID = transId;
853
854 cursor GetPOShipmentIds is
855 SELECT s.TRANSACTION_ID
856 FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
857 WHERE s.ORDER_TYPE = 11
858 AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
859 AND s.SUPPLIER_ID is not null
860 AND s.PLAN_ID = planId
861 AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
862 AND tu.TRANS_UPDATE_ID = transId;
863
864
865 PO_Ids MscNumberArr := MscNumberArr();
866 --PO_Shipment_ids MscNumberArr := MscNumberArr();
867 invItemId NUMBER :=0;
868 orgId NUMBER :=0;
869 newArrivalDate DATE;
870 v_new_firm_Date DATE;
871 SrInstanceId NUMBER :=0;
872 v_temp NUMBER :=0;
873 i NUMBER :=0;
874 userId NUMBER :=0;
875 begin
876
877 select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID into newArrivalDate, SrInstanceId
878 from msc_transportation_updates
879 where TRANS_UPDATE_ID = transId;
880
881 --- Get PO_Ids
882 i:=1;
883 OPEN GetPOIds;
884 LOOP
885 FETCH GetPOIds into v_temp;
886 EXIT WHEN GetPOIds%NOTFOUND;
887 PO_Ids.extend;
888 PO_Ids(i) := v_temp;
889 i := i+1;
890 END LOOP;
891 CLOSE GetPOIds;
892
893 if ( i = 1) then -- PO shipment, not PO
894 isPoShipment := 1;
895 select distinct s.INVENTORY_ITEM_ID, s.ORGANIZATION_ID into invItemId, orgId
896 FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
897 WHERE s.ORDER_TYPE = 11
898 AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
899 AND s.PLAN_ID =planId
900 AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
901 AND tu.TRANS_UPDATE_ID = transId;
902
903 else
904 isPoShipment := 0;
905 select distinct s.INVENTORY_ITEM_ID, s.ORGANIZATION_ID into invItemId, orgId
906 FROM MSC_SUPPLIES s, MSC_TRANSPORTATION_UPDATES tu
907 WHERE s.ORDER_TYPE = 1
908 AND s.PO_LINE_LOCATION_ID = tu.PO_LINE_LOCATION_ID
909 AND s.PO_LINE_ID = tu.PO_LINE_ID
910 AND s.PLAN_ID =planId
911 AND s.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
912 AND tu.TRANS_UPDATE_ID = transId;
913
914 end if;
915
916 --dbms_output.put_line( ' passed 3');
917
918 --- Get PO_Shipment_ids ( add them in same array, so that looping done easier
919
920 OPEN GetPOShipmentIds;
921 LOOP
922 FETCH GetPOShipmentIds into v_temp;
923 EXIT WHEN GetPOShipmentIds%NOTFOUND;
924 PO_Ids.extend;
925 PO_Ids(i) := v_temp;
926 i := i+1;
927 END LOOP;
928 CLOSE GetPOShipmentIds;
929
930 if ( i =1 ) then -- no orders found, no PO, no PO shipment
931 status := 'NO_ORDERS_FOUND_TO_UPDATE';
932 return;
933 end if;
934
935 if ( GetLeadTime(invItemId, orgId, planId, SrInstanceId, newArrivalDate, v_new_firm_Date) = false) then
936 v_new_firm_Date := newArrivalDate;
937 end if;
938
939 --userId := fnd_global.User_id();
940 userId := g_UserId;
941 --dbms_output.put_line('got leadTime' || v_new_firm_date);
942
943 UPDATE msc_transportation_updates
944 SET UPDATED_DUE_DATE = v_new_firm_Date,
945 LAST_UPDATE_DATE = SYSDATE,
946 LAST_UPDATED_BY = userId
947 WHERE TRANS_UPDATE_ID = transId;
948
949 i:=0;
950 FOR i IN 1 .. PO_Ids.COUNT
951 LOOP
952 --dbms_output.put_line(' tranz_id = ' || PO_Ids(i));
953 UPDATE MSC_SUPPLIES
954 Set FIRM_DATE = v_new_firm_Date,
955 APPLIED = 2,
956 STATUS = 0,
957 FIRM_PLANNED_TYPE = 1,
958 OTM_ARRIVAL_DATE = newArrivalDate,
959 FIRM_QUANTITY = NEW_ORDER_QUANTITY,
960 LAST_UPDATE_DATE = SYSDATE,
961 LAST_UPDATED_BY = userId
962 WHERE TRANSACTION_ID = PO_Ids(i)
963 AND SR_INSTANCE_ID = SrInstanceId
964 AND PLAN_ID = planId;
965
966 END LOOP ;
967
968 status := 'SUCCESS';
969
970 EXCEPTION
971 when no_data_found then
972 status := ' NO_ORDERS_FOUND_TO_UPDATE_IN_PDS';
973
974 when others then
975 status := 'ERROR_PDS_PO';
976
977 end UpdateNewColumnAndFirmDate_PO;
978
979
980 PROCEDURE UpdateNewColumnAndFirmDate_SO( planId IN NUMBER,
981 transId IN NUMBER , status out nocopy varchar2) IS
982 /*cursor GetIR_Shipments( srIId IN NUMBER ) is
983 SELECT s2.TRANSACTION_ID
984 FROM MSC_SUPPLIES s2, MSC_SALES_ORDERS sO, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
985 WHERE s2.ORDER_TYPE = 11 -- IR Shipment
986 AND s2.PLAN_ID =planId
987 AND s2.SR_INSTANCE_ID = srIId
988 AND SO.SR_INSTANCE_ID = srIId
989 AND dd.SR_INSTANCE_ID = srIId
990 AND tu.EBS_SR_INSTANCE_ID = srIId
991 AND s2.REQ_LINE_ID = SO.ORIGINAL_SYSTEM_LINE_REFERENCE
992 AND sO.DEMAND_SOURCE_LINE = dd.SOURCE_LINE_ID
993 AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
994 AND tu.Trans_update_id = transId;*/
995
996 /*ISO_Ids MscNumberArr := MscNumberArr();
997 IR_Ids MscNumberArr := MscNumberArr();
998 IR_Shipment_ids MscNumberArr := MscNumberArr();*/
999
1000 ISOID1 NUMBER :=0;
1001 IRID1 NUMBER :=0;
1002
1003 invItemId NUMBER :=0;
1004 orgId NUMBER :=0;
1005 newArrivalDate DATE;
1006 v_new_firm_Date DATE;
1007 SrInstanceId NUMBER :=0;
1008 i NUMBER :=0;
1009 v_temp NUMBER :=0;
1010 userId NUMBER :=0;
1011 begin
1012
1013 --dbms_output.put_line(fnd_profile.value('MSC_EBS_INSTANCE_FOR_OTM'));
1014
1015 select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID into newArrivalDate, SrInstanceId
1016 from msc_transportation_updates
1017 where TRANS_UPDATE_ID = transId;
1018
1019 -- is this only one result ????? or more ???
1020 select distinct d.INVENTORY_ITEM_ID, d.SOURCE_ORGANIZATION_ID into invItemId, orgId
1021 FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
1022 WHERE d.SALES_ORDER_LINE_ID = to_char(dd.SOURCE_LINE_ID)
1023 AND d.SR_INSTANCE_ID = dd.SR_INSTANCE_ID
1024 AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
1025 AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
1026 AND d.PLAN_ID = planId
1027 AND d.ORIGINATION_TYPE = 30
1028 AND tu.TRANS_UPDATE_ID = transId;
1029
1030 SELECT distinct d.DEMAND_ID
1031 into ISOID1
1032 FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
1033 WHERE d.SALES_ORDER_LINE_ID = dd.SOURCE_LINE_ID
1034 AND d.SR_INSTANCE_ID = dd.SR_INSTANCE_ID
1035 AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
1036 AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
1037 AND d.PLAN_ID = planId
1038 AND d.ORIGINATION_TYPE = 30
1039 AND tu.Trans_update_id = transId;
1040
1041 --userId := fnd_global.User_id();
1042 userId := g_UserId;
1043
1044
1045 --Update all ISOs
1046
1047 UPDATE MSC_DEMANDS
1048 Set OTM_ARRIVAL_DATE = newArrivalDate,
1049 LAST_UPDATE_DATE = SYSDATE,
1050 LAST_UPDATED_BY = userId
1051 WHERE PLAN_ID = planId
1052 AND SR_INSTANCE_ID = SrInstanceId
1053 AND DEMAND_ID = ISOID1;
1054
1055 --Select all IRs ---------------------
1056
1057 --dbms_output.put_line(ISOID1);
1058 SELECT distinct s.TRANSACTION_ID --IR_Ids
1059 INTO IRID1
1060 FROM MSC_SUPPLIES s, MSC_DEMANDS d
1061 WHERE s.ORDER_TYPE = 2 -- IR
1062 AND s.TRANSACTION_ID = d.DISPOSITION_ID
1063 AND s.PLAN_ID =planId
1064 AND d.PLAN_ID =planId
1065 AND s.SR_INSTANCE_ID = srInstanceId
1066 AND d.DEMAND_ID = ISOID1;
1067
1068 -- select all IR shipments -- put all IR_Shipment ids in same array as IR_IDs, easier to loop
1069 /* OPEN GetIR_Shipments(SrInstanceId);
1070 LOOP
1071 FETCH GetIR_Shipments into v_temp;
1072 EXIT WHEN GetIR_Shipments%NOTFOUND;
1073 IR_Ids.extend;
1074 IR_Ids(i) := v_temp;
1075 i := i+1;
1076 END LOOP;
1077 CLOSE GetIR_Shipments;*/
1078
1079
1080 if ( ISOID1=0 and IRID1 = 0) then
1081 status := 'NO_ISO_IR_IRSHIPMENTS_FOUND';
1082 return;
1083 end if;
1084
1085
1086 if ( GetLeadTime(invItemId, orgId, planId, SrInstanceId, newArrivalDate, v_new_firm_Date) = false) then
1087 v_new_firm_Date := newArrivalDate;
1088 end if;
1089
1090 UPDATE msc_transportation_updates
1091 SET UPDATED_DUE_DATE = v_new_firm_Date,
1092 LAST_UPDATE_DATE = SYSDATE,
1093 LAST_UPDATED_BY = userId
1094 WHERE TRANS_UPDATE_ID = transId;
1095
1096 -- IR for now, IR shipment later
1097 --update both IRs and IR shipments
1098 UPDATE MSC_SUPPLIES
1099 Set FIRM_DATE = v_new_firm_Date,
1100 APPLIED = 2,
1101 STATUS = 0,
1102 FIRM_PLANNED_TYPE = 1,
1103 OTM_ARRIVAL_DATE = newArrivalDate,
1104 FIRM_QUANTITY = NEW_ORDER_QUANTITY,
1105 LAST_UPDATE_DATE = SYSDATE,
1106 LAST_UPDATED_BY = userId
1107 WHERE
1108 PLAN_ID =planId
1109 AND SR_INSTANCE_ID = SrInstanceId
1110 AND TRANSACTION_ID = IRID1;
1111
1112
1113 status := 'SUCCESS';
1114
1115 EXCEPTION
1116 when no_data_found then
1117 status := 'NO_ISO_IR_FOUND';
1118 return;
1119 when others then
1120 status := 'ERROR_in_PDS_SO';
1121 return;
1122 end UpdateNewColumnAndFirmDate_SO;
1123
1124 PROCEDURE UpdateNewColumnAndFirmDate_SO_( planId IN NUMBER,
1125 transId IN NUMBER , status out nocopy varchar2) IS
1126 cursor GetISOs is
1127 SELECT d.DEMAND_ID
1128 FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
1129 WHERE d.SALES_ORDER_LINE_ID = dd.SOURCE_LINE_ID
1130 AND d.SR_INSTANCE_ID = dd.SR_INSTANCE_ID
1131 AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
1132 AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
1133 AND d.PLAN_ID = planId
1134 AND d.ORIGINATION_TYPE = 30
1135 AND tu.Trans_update_id = transId;
1136
1137 cursor GetIR_IDs (srIId IN NUMBER) is
1138 SELECT s.TRANSACTION_ID --IR_Ids
1139 FROM MSC_SUPPLIES s, MSC_DEMANDS d
1140 WHERE s.ORDER_TYPE = 2 -- IR
1141 AND s.TRANSACTION_ID = d.DISPOSITION_ID
1142 AND s.PLAN_ID =planId
1143 AND s.SR_INSTANCE_ID = srIId
1144 AND d.DEMAND_ID IN ( SELECT d.DEMAND_ID
1145 FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
1146 WHERE d.SALES_ORDER_LINE_ID = dd.SOURCE_LINE_ID
1147 AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
1148 AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
1149 AND d.PLAN_ID = planId
1150 AND d.ORIGINATION_TYPE = 30
1151 AND tu.Trans_update_id = transId);
1152
1153
1154 cursor GetIR_Shipments( srIId IN NUMBER ) is
1155 SELECT s2.TRANSACTION_ID
1156 FROM MSC_SUPPLIES s2, MSC_SALES_ORDERS sO, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
1157 WHERE s2.ORDER_TYPE = 11 -- IR Shipment
1158 AND s2.PLAN_ID =planId
1159 AND s2.SR_INSTANCE_ID = srIId
1160 AND SO.SR_INSTANCE_ID = srIId
1161 AND dd.SR_INSTANCE_ID = srIId
1162 AND tu.EBS_SR_INSTANCE_ID = srIId
1163 AND s2.REQ_LINE_ID = SO.ORIGINAL_SYSTEM_LINE_REFERENCE
1164 AND sO.DEMAND_SOURCE_LINE = dd.SOURCE_LINE_ID
1165 AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID -- maybe use wsh_delive from MTU
1166 AND tu.Trans_update_id = transId;
1167
1168
1169
1170
1171 ISO_Ids MscNumberArr := MscNumberArr();
1172 IR_Ids MscNumberArr := MscNumberArr();
1173 IR_Shipment_ids MscNumberArr := MscNumberArr();
1174 invItemId NUMBER :=0;
1175 orgId NUMBER :=0;
1176 newArrivalDate DATE;
1177 v_new_firm_Date DATE;
1178 SrInstanceId NUMBER :=0;
1179 i NUMBER :=0;
1180 v_temp NUMBER :=0;
1181 userId NUMBER :=0;
1182 begin
1183
1184 --dbms_output.put_line(fnd_profile.value('MSC_EBS_INSTANCE_FOR_OTM'));
1185
1186
1187
1188 select UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID into newArrivalDate, SrInstanceId
1189 from msc_transportation_updates
1190 where TRANS_UPDATE_ID = transId;
1191
1192 --dbms_output.put_line(planId);
1193
1194 -- is this only one result ????? or more ???
1195 select d.INVENTORY_ITEM_ID, d.SOURCE_ORGANIZATION_ID into invItemId, orgId
1196 FROM MSC_DEMANDS d, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
1197 WHERE d.SALES_ORDER_LINE_ID = dd.SOURCE_LINE_ID
1198 AND d.SR_INSTANCE_ID = dd.SR_INSTANCE_ID
1199 AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
1200 AND dd.SR_INSTANCE_ID = tu.EBS_SR_INSTANCE_ID
1201 AND d.PLAN_ID = planId
1202 AND d.ORIGINATION_TYPE = 30
1203 AND tu.TRANS_UPDATE_ID = transId;
1204
1205
1206 --select all ISO
1207 i:=1;
1208 OPEN GetISOs;
1209 LOOP
1210 FETCH GetISOs into v_temp;
1211 EXIT WHEN GetISOs%NOTFOUND;
1212 ISO_Ids.extend;
1213 ISO_Ids(i) := v_temp;
1214 i := i+1;
1215 END LOOP;
1216 CLOSE GetISOs;
1217
1218 --userId := fnd_global.User_id();
1219 userId := g_UserId;
1220
1221
1222 --Update all ISOs
1223 i:=1;
1224 FOR i IN 1 .. ISO_Ids.COUNT
1225 LOOP
1226 UPDATE MSC_DEMANDS
1227 Set OTM_ARRIVAL_DATE = newArrivalDate,
1228 LAST_UPDATE_DATE = SYSDATE,
1229 LAST_UPDATED_BY = userId
1230 WHERE PLAN_ID = planId
1231 AND SR_INSTANCE_ID = SrInstanceId
1232 AND DEMAND_ID = ISO_Ids(i);
1233
1234 END LOOP;
1235
1236
1237 --Select all IRs ---------------------
1238
1239 i:=1;
1240 OPEN GetIR_IDs(SrInstanceId);
1241 LOOP
1242 FETCH GetIR_IDs into v_temp;
1243 EXIT WHEN GetIR_IDs%NOTFOUND;
1244 IR_Ids.extend;
1245 IR_Ids(i) := v_temp;
1246 i := i+1;
1247 END LOOP;
1248 CLOSE GetIR_IDs;
1249
1250
1251 -- select all IR shipments -- put all IR_Shipment ids in same array as IR_IDs, easier to loop
1252 /* OPEN GetIR_Shipments(SrInstanceId);
1253 LOOP
1254 FETCH GetIR_Shipments into v_temp;
1255 EXIT WHEN GetIR_Shipments%NOTFOUND;
1256 IR_Ids.extend;
1257 IR_Ids(i) := v_temp;
1258 i := i+1;
1259 END LOOP;
1260 CLOSE GetIR_Shipments;*/
1261
1262
1263 if ( i = 1) then
1264 status := 'NO_ISO_IR_IRSHIPMENTS_FOUND';
1265 return;
1266 end if;
1267
1268
1269 if ( GetLeadTime(invItemId, orgId, planId, SrInstanceId, newArrivalDate, v_new_firm_Date) = false) then
1270 v_new_firm_Date := newArrivalDate;
1271 end if;
1272
1273 UPDATE msc_transportation_updates
1274 SET UPDATED_DUE_DATE = v_new_firm_Date,
1275 LAST_UPDATE_DATE = SYSDATE,
1276 LAST_UPDATED_BY = userId
1277 WHERE TRANS_UPDATE_ID = transId;
1278
1279 FOR i IN 1 .. IR_Ids.COUNT
1280 LOOP
1281 --update both IRs and IR shipments
1282 UPDATE MSC_SUPPLIES
1283 Set FIRM_DATE = v_new_firm_Date,
1284 APPLIED = 2,
1285 STATUS = 0,
1286 FIRM_PLANNED_TYPE = 1,
1287 OTM_ARRIVAL_DATE = newArrivalDate,
1288 FIRM_QUANTITY = NEW_ORDER_QUANTITY,
1289 LAST_UPDATE_DATE = SYSDATE,
1290 LAST_UPDATED_BY = userId
1291 WHERE
1292 PLAN_ID =planId
1293 AND SR_INSTANCE_ID = SrInstanceId
1294 AND TRANSACTION_ID = IR_Ids(i); --in (IR_Ids, IR_Shipment_ids);
1295 END LOOP;
1296
1297 status := 'SUCCESS';
1298
1299 EXCEPTION
1300 when no_data_found then
1301 status := 'NO_ISO_IR_FOUND';
1302 return;
1303 when others then
1304 status := 'ERROR_in_PDS_SO';
1305 return;
1306 end UpdateNewColumnAndFirmDate_SO_;
1307
1308
1309 procedure GetPlanner_1( srInstanceId IN NUMBER,
1310 inventoryItemId IN NUMBER,
1311 orgId IN NUMBER,
1312 planner OUT nocopy varchar2,
1313 status OUT nocopy varchar2) is
1314 v_plannerCode varchar2(100);
1315 begin
1316
1317 --dbms_output.put_line('srInstanceId=' || srInstanceId);
1318
1319 select PLANNER_CODE into v_plannerCode
1320 from MSC_SYSTEM_ITEMS
1321 where INVENTORY_ITEM_ID = inventoryItemId
1322 and plan_id = -1
1323 and ORGANIZATION_ID = orgId;
1324
1325 select USER_NAME into planner
1326 from MSC_PLANNERS
1327 where PLANNER_CODE = v_plannerCode
1328 and ORGANIZATION_ID = orgId
1329 and SR_INSTANCE_ID = srInstanceId;
1330
1331 status:='SUCCESS';
1332
1333 EXCEPTION
1334 when NO_DATA_FOUND then
1335 planner := '0';
1336 status :='NO_PLANNER';
1337 return;
1338 when others then
1339 planner := '0';
1340 status :='ERROR_GETTING_PLANNER';
1341 return;
1342 end GetPlanner_1;
1343
1344
1345
1346
1347 procedure AddLineId ( poIdString IN varchar2,
1348 pnewArrivalDate IN varchar2,
1349 ReleaseGid IN varchar2,
1350 ReleaseLineGid IN varchar2,
1351 tranzId out nocopy NUMBER,
1352 status out nocopy varchar2) is
1353 poId NUMBER :=0;
1354 locationLineId NUMBER :=0;
1355 indexI NUMBER :=0;
1356 temp varchar2(100);
1357 lg NUMBER :=0;
1358 userId NUMBER :=0;
1359 d1 DATE;
1360 key NUMBER :=0;
1361 srInstanceId NUMBER :=0;
1362 nCount NUMBER :=0;
1363 begin
1364
1365 -- this procedure adds PO s of type order_type =1 only !
1366 SAVEPOINT sv_addLineId;
1367 AppsInit;
1368 --userId := fnd_global.User_id();
1369 userId := g_UserId;
1370
1371 indexI := INSTR(poIdString,'SCHED');
1372 poId := to_NUMBER( substr(poIdString, 6, indexI-7) );
1373 --dbms_output.put_line(poId);
1374
1375 lg := LENGTH(poIdString);
1376 locationLineId := to_number( substr(poIdString, indexI + 6, lg - indexI -6 +1));
1377 --dbms_output.put_line(locationLineId);
1378
1379 d1 := to_date(pnewArrivalDate, 'YYYY/MM/DD HH24:MI:SS');
1380 --dbms_output.put_line('d1 = ' || d1);
1381 srInstanceId := fnd_profile.value('MSC_EBS_INSTANCE_FOR_OTM');
1382
1383 select count(1) into nCount
1384 from MSC_TRANSPORTATION_UPDATES
1385 where OTM_RELEASE_LINE_GID = ReleaseLineGid;
1386
1387 if ( nCount =0 ) then
1388
1389 select MSC_TRANSPORTATION_UPDATES_s.nextval into key from dual;
1390
1391 insert into MSC_TRANSPORTATION_UPDATES (TRANS_UPDATE_ID, ORDER_TYPE, PO_LINE_LOCATION_ID, PO_LINE_ID, UPDATED_ARRIVAL_DATE, EBS_SR_INSTANCE_ID,
1392 OTM_RELEASE_GID, OTM_RELEASE_LINE_GID,
1393 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
1394 VALUES (key, 1, locationLineId,poId, d1, srInstanceId,
1395 ReleaseGid, ReleaseLineGid, SYSDATE, userId, SYSDATE, userId);
1396
1397 status:= 'SUCCESS';
1398 tranzId := key;
1399 else
1400 update MSC_TRANSPORTATION_UPDATES
1401 set UPDATED_ARRIVAL_DATE = d1,
1402 LAST_UPDATE_DATE = SYSDATE,
1403 LAST_UPDATED_BY = userId
1404 where OTM_RELEASE_LINE_GID = ReleaseLineGid;
1405
1406 select trans_update_id into tranzId
1407 from msc_transportation_updates
1408 where OTM_RELEASE_LINE_GID = ReleaseLineGid;
1409
1410 status:= 'SUCCESS';
1411 end if;
1412
1413
1414 EXCEPTION when others then
1415 ROLLBACK to sv_addLineId;
1416 status := 'ERROR';
1417
1418 end AddLineId;
1419
1420 procedure AddLineSO ( pnewArrivalDate IN varchar2,
1421 ReleaseGid IN varchar2,
1422 ReleaseLineGid IN varchar2,
1423 isInternalSO IN varchar2,
1424 tranzId out nocopy NUMBER,
1425 status out nocopy varchar2) is
1426 key NUMBER :=0;
1427 d1 DATE;
1428 userId NUMBER :=0;
1429 isOrg varchar2(3);
1430 nCount NUMBER :=0;
1431 srInstanceId NUMBER :=0;
1432 begin
1433
1434 AppsInit;
1435 --userId := fnd_global.User_id();
1436 userId := g_UserId;
1437
1438 -- if not internal sales order, then just exit
1439 isOrg := substr(isInternalSO, 1, 3);
1440 if ( isOrg <> 'ORG') then
1441 status := 'NOT_INTERNAL_SO';
1442 return;
1443 end if;
1444
1445 d1 := to_date(pnewArrivalDate, 'YYYY/MM/DD HH24:MI:SS');
1446
1447
1448 select count(1) into nCount
1449 from MSC_TRANSPORTATION_UPDATES
1450 where OTM_RELEASE_LINE_GID = ReleaseLineGid;
1451
1452 --dbms_output.put_line('count = '|| nCount);
1453 --dbms_output.put_line('ReleaseLineGid = '|| '<' || ReleaseLineGid || '>');
1454
1455 srInstanceId := fnd_profile.value('MSC_EBS_INSTANCE_FOR_OTM');
1456
1457 if ( nCount =0 ) then
1458 select MSC_TRANSPORTATION_UPDATES_s.nextval into key from dual;
1459
1460 insert into MSC_TRANSPORTATION_UPDATES (TRANS_UPDATE_ID, ORDER_TYPE, UPDATED_ARRIVAL_DATE,EBS_SR_INSTANCE_ID,
1461 OTM_RELEASE_GID,OTM_RELEASE_LINE_GID,WSH_DELIVERY_DETAIL_ID,
1462 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
1463 VALUES (key, 2, d1, srInstanceId, ReleaseGid, ReleaseLineGid, ReleaseLineGid, SYSDATE, userId, SYSDATE, userId);
1464
1465 status:= 'SUCCESS';
1466 tranzId := key;
1467 else
1468 update MSC_TRANSPORTATION_UPDATES
1469 set UPDATED_ARRIVAL_DATE = d1,
1470 LAST_UPDATE_DATE = SYSDATE,
1471 LAST_UPDATED_BY = userId
1472 where OTM_RELEASE_LINE_GID = ReleaseLineGid;
1473
1474 select trans_update_id into tranzId
1475 from msc_transportation_updates
1476 where OTM_RELEASE_LINE_GID = ReleaseLineGid;
1477
1478 status:= 'SUCCESS';
1479
1480 end if;
1481
1482 EXCEPTION when others then
1483 status := 'ERROR';
1484
1485 end AddLineSO;
1486
1487 --========================= NOTIFICATION =====================================
1488
1489
1490
1491 procedure SendNotification_1 ( tranzId IN NUMBER,
1492 status out nocopy varchar2) is
1493 userId NUMBER :=0;
1494 respId NUMBER :=0;
1495 planner varchar2(40);
1496 tokenValues MsgTokenValuePairList;
1497 v_arrival_Date DATE;
1498 v_po_line_id NUMBER :=0;
1499 v_line_location_id NUMBER :=0;
1500 v_tranzId NUMBER :=0;
1501 v_srInstanceId NUMBER :=0;
1502 v_orderNumber varchar2(100) :='';
1503 v_Http varchar2(200);
1504 otmReleaseGid varchar2(100);
1505 v_order_type NUMBER :=0;
1506 v_itemId NUMBER :=0;
1507 v_orgId NUMBER :=0;
1508
1509 begin
1510
1511 if tranzId is null then
1512 status := 'NO_NOTIFICATION_TO_BE_SEND';
1513 return;
1514 end if;
1515
1516 if fnd_profile.value('MSC_GEN_NOTIFICATION_FOR_OTM') = 2 then
1517 status := 'NOTIFICATION_NOT_ALLOWED_BY PROFILE_OPTION';
1518 return;
1519 end if;
1520
1521 AppsInit;
1522 userId := fnd_global.USER_ID();
1523 respId := fnd_global.RESP_ID();
1524
1525 if ( userId =0 or respId =0) then
1526 status := 'ERROR_USER_RESP_PROFILES_NOT_SET';
1527 end if;
1528
1529 tokenValues := MsgTokenValuePairList();
1530
1531 select order_type , EBS_SR_INSTANCE_ID, OTM_RELEASE_GID, updated_Arrival_Date
1532 into v_order_type, v_srInstanceId, otmReleaseGid, v_arrival_Date
1533 from msc_transportation_updates
1534 where trans_update_id = tranzId;
1535
1536 v_Http := GetPunchoutURI( 0, otmReleaseGid);
1537 --dbms_output.put_line(v_Http);
1538
1539 -- order number needed, that is different for PO and SO.
1540 if (v_order_type = 1) then
1541 SELECT po_line_location_id
1542 INTO v_line_location_id
1543 FROM MSC_TRANSPORTATION_UPDATES
1544 WHERE trans_update_id = tranzId;
1545
1546 GetDataForNotification( v_line_location_id, v_srInstanceId, v_orderNumber, v_itemId, v_orgId);
1547 --dbms_output.put_line(v_itemId || ' ' || v_orderNumber );
1548 else
1549 SELECT s2.order_number, s2.INVENTORY_ITEM_ID, s2.ORGANIZATION_ID
1550 INTO v_orderNumber, v_itemId, v_orgId
1551 FROM MSC_SUPPLIES s2, MSC_SALES_ORDERS sO, MSC_DELIVERY_DETAILS dd, MSC_TRANSPORTATION_UPDATES tu
1552 WHERE s2.PLAN_ID =-1
1553 AND s2.SR_INSTANCE_ID = v_srInstanceId
1554 AND SO.SR_INSTANCE_ID = v_srInstanceId
1555 AND dd.SR_INSTANCE_ID = v_srInstanceId
1556 AND tu.EBS_SR_INSTANCE_ID = v_srInstanceId
1557 AND s2.TRANSACTION_ID = SO.SUPPLY_ID
1558 AND sO.DEMAND_SOURCE_LINE = dd.SOURCE_LINE_ID
1559 AND dd.DELIVERY_DETAIL_ID = tu.OTM_RELEASE_LINE_GID
1560 AND tu.Trans_update_id = tranzId
1561 AND s2.order_type = 2;
1562
1563 --dbms_output.put_line(v_itemId || ' ' || v_orderNumber );
1564 end if;
1565
1566 GetPlanner_1( v_srInstanceId, v_itemId, v_orgId, planner, status);
1567
1568 if ( planner <> '0') then
1569 tokenValues.extend;
1570 tokenValues(1) := MsgTokenValuePair('PO_ORDERNUMBER', v_orderNumber);
1571 tokenValues.extend;
1572 tokenValues(2) := MsgTokenValuePair('PO_ARRIVALDATE', v_arrival_Date);
1573 tokenValues.extend;
1574 tokenValues(3) := MsgTokenValuePair('PO_URI', v_Http);
1575
1576 status := MSC_WS_NOTIFICATION_BPEL.SendFYINotification ( userId, respID, planner, 'EN', 'W_OTM_UP', 'W_OTM_PROC', tokenValues);
1577
1578 end if;
1579
1580
1581 EXCEPTION when no_data_found then
1582 status := 'NO_DATA_FOUND_FOR_NOTIFICATION';
1583 when others then
1584 status := 'ERROR_IN_NOTIFICATION ' || fnd_message.get();
1585 end SendNotification_1;
1586
1587
1588 procedure GetDataForNotification(lineLocationId IN NUMBER,
1589 srInstanceId IN NUMBER,
1590 orderNumber OUT nocopy VARCHAR2,
1591 inventoryItemId out nocopy NUMBER,
1592 orgId out nocopy NUMBER) is
1593 cursor GetOrderNumber is
1594 select distinct order_number, inventory_item_id, ORGANIZATION_ID
1595 from msc_supplies
1596 WHERE PLAN_ID= -1
1597 AND SR_INSTANCE_ID=srInstanceId
1598 AND ORDER_TYPE= 1
1599 AND PO_line_LOCATION_ID = lineLocationId;
1600
1601 begin
1602
1603 open GetOrderNumber;
1604 loop
1605 FETCH GetOrderNumber into orderNumber, inventoryItemId, orgId;
1606 EXIT WHEN GetOrderNumber%NOTFOUND;
1607 end loop;
1608 close GetOrderNumber;
1609
1610 return;
1611
1612 end GetDataForNotification;
1613
1614
1615 procedure AppsInit is
1616 userId NUMBER :=0;
1617 respId NUMBER :=0;
1618 appId NUMBER :=0;
1619 begin
1620 userId := fnd_profile.value('MSC_WS_OTM_USERID');
1621 respId := fnd_profile.value('MSC_WS_OTM_RESPID');
1622 if ( respId = 0) then
1623 respId := fnd_profile.value('MSC: OTM RESPONSIBILITY');
1624 end if;
1625 SELECT application_id INTO appId FROM fnd_responsibility WHERE responsibility_id = respId;
1626 fnd_global.apps_initialize(userId, respId, appId);
1627
1628 g_UserId := userId;
1629
1630 --dbms_output.put_line(userId || ' ' || respId || ' ' || appId);
1631 end AppsInit;
1632
1633 function GetProfilePlanId return NUMBER is
1634 planId NUMBER :=0;
1635 begin
1636 planId := fnd_profile.value('MSC_PROD_PLAN_ID_FOR_OTM_UPDATES');
1637 return planId;
1638
1639 EXCEPTION when others then
1640 return 0;
1641
1642 end GetProfilePlanId;
1643
1644 function GetPunchoutURI(srInstanceId IN NUMBER,
1645 otmReleaseGid IN varchar2) return varchar2 is
1646 v_Http varchar2(200);
1647
1648 lg NUMBER:=0;
1649 strTemp varchar2(200);
1650 t1 varchar2(10);
1651 t2 varchar2(100);
1652
1653 begin
1654 -- srInstanceId is not used right now. Will be used when multi-Instance allowed.
1655
1656 -- http://otm-it1-55-oas.us.oracle.com/GC3/OrderReleaseCustManagement?management_action=view=ORDER_RELEASE_VIEW=GUEST.20011214-0001-001
1657 --server from OTM: Servlet URI
1658 --pk = DOMAIN.OTM_RELEASE_GID
1659 -- where DOMAIN taken from OTM: Domain Name
1660
1661 strTemp := fnd_profile.value('MSC_OTM_PUNCHOUT_URI');
1662 lg := LENGTH(strTemp);
1663
1664 t1 := substr(strTemp, lg , lg) ;
1665
1666 if ( t1 = '/') then
1667 t2 := substr(strTemp, 1 , lg-1) ;
1668 else
1669 t2 := strTemp;
1670 end if;
1671
1672 v_Http := t2
1673 ||'/GC3/OrderReleaseCustManagement' || '?management_action=view'||'&'||'manager_layout_gid=ORDER_RELEASE_VIEW'||'&'||'pk='
1674 || fnd_profile.value('WSH_OTM_DOMAIN_NAME')
1675 || '.'
1676 || otmReleaseGid;
1677
1678 return v_Http;
1679
1680 EXCEPTION when others then
1681 return '';
1682
1683 end GetPunchoutURI;
1684
1685
1686 procedure PurgeTransportationUpdates is
1687 cursor c_getLine is
1688 SELECT
1689 TRANS_UPDATE_ID, updated_Due_Date
1690 FROM
1691 MSC_TRANSPORTATION_UPDATES;
1692
1693 v_trans_id NUMBER :=0;
1694 v_due_Date DATE;
1695 v_adj_date DATE;
1696
1697 begin
1698 AppsInit;
1699
1700 OPEN c_getLine;
1701 LOOP
1702 FETCH c_getLine into v_trans_id, v_due_Date;
1703 EXIT WHEN c_getLine%NOTFOUND;
1704 v_adj_date := v_due_Date + 90;
1705 --dbms_output.put_line(v_adj_date);
1706 if ( v_adj_Date < SYSDATE ) then
1707 delete from msc_transportation_updates where trans_update_id = v_trans_id;
1708 end if;
1709
1710 END LOOP;
1711 CLOSE c_getLine;
1712
1713 end PurgeTransportationUpdates;
1714
1715
1716 END MSC_WS_OTM_BPEL;