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