1 PACKAGE BODY MSC_X_UTIL AS
2 /* $Header: MSCXUTLB.pls 115.45 2004/07/07 22:39:55 pshah ship $ */
3
4 STATUS_ERROR CONSTANT NUMBER := 1;
5 STATUS_SUCCESS CONSTANT NUMBER := 0;
6
7 -- function get_party_name takes in party_id and returns the party
8 -- name from HZ_PARTIES
9 FUNCTION GET_PARTY_NAME (p_party_id IN NUMBER) RETURN VARCHAR2
10 IS
11 l_party_name VARCHAR2(30);
12
13 BEGIN
14
15 SELECT party_name
16 INTO l_party_name
17 FROM hz_parties
18 WHERE party_id = p_party_id;
19
20 RETURN l_party_name;
21
22 EXCEPTION
23 WHEN NO_DATA_FOUND THEN
24 RETURN NULL;
25
26 END GET_PARTY_NAME;
27
28 -- function get_category_code takes in inventory_item_id of an item,
29 -- customer and supplier info and returns the category name of the
30 -- item defined in the OEM's org.
31 FUNCTION GET_CATEGORY_CODE(p_inventory_item_id IN NUMBER,
32 p_publisher_id IN NUMBER,
33 p_publisher_site_id IN NUMBER,
34 p_customer_id IN NUMBER,
35 p_customer_site_id IN NUMBER,
36 p_supplier_id IN NUMBER,
37 p_supplier_site_id IN NUMBER)
38 RETURN VARCHAR2
39 IS
40 l_category_code varchar2(250);
41 l_org_id NUMBER;
42 BEGIN
43
44 -- get the OEM's org
45 if(p_publisher_id = 1) then
46 l_org_id := p_publisher_site_id;
47 elsif(p_customer_id = 1) then
48 l_org_id := p_customer_site_id;
49 elsif (p_supplier_id = 1) then
50 l_org_id := p_supplier_site_id;
51 else
52 l_org_id := null;
53 end if;
54
55 l_category_code := null;
56
57 SELECT category_name
58 INTO l_category_code
59 FROM msc_item_categories mic,
60 msc_trading_partners tp,
61 msc_trading_partner_maps map
62 WHERE map.company_key = l_org_id
63 and map.map_type = 2
64 and map.tp_key = tp.partner_id
65 and tp.sr_tp_id = mic.organization_id
66 and mic.sr_instance_id = tp.sr_instance_id
67 and mic.inventory_item_id = p_inventory_item_id
68 and mic.category_set_id = FND_PROFILE.VALUE('MSCX_CP_HZ_CATEGORY_SET');
69
70 return l_category_code;
71
72 EXCEPTION
73 WHEN NO_DATA_FOUND THEN
74 BEGIN
75 return NULL;
76
77 EXCEPTION
78 WHEN NO_DATA_FOUND THEN
79
80 RETURN NULL;
81 END;
82 END GET_CATEGORY_CODE;
83
84 -- function get_buyer_code takes in inventory_item_id, customer and supplier information
85 -- from CP to return the buyer_code from msc_system_items.
86 FUNCTION GET_BUYER_CODE (p_inventory_item_id IN NUMBER,
87 p_publisher_id IN NUMBER,
88 p_publisher_site_id IN NUMBER,
89 p_customer_id IN NUMBER,
90 p_customer_site_id IN NUMBER,
91 p_supplier_id IN NUMBER,
92 p_supplier_site_id IN NUMBER) RETURN VARCHAR2
93 IS
94 l_buyer_code VARCHAR2(240);
95 l_org_id NUMBER;
96 BEGIN
97
98 -- get the OEM's org
99 if(p_publisher_id = 1) then
100 l_org_id := p_publisher_site_id;
101 elsif(p_customer_id = 1) then
102 l_org_id := p_customer_site_id;
103 elsif (p_supplier_id = 1) then
104 l_org_id := p_supplier_site_id;
105 else
106 l_org_id := null;
107 end if;
108
109 SELECT buyer_name into l_buyer_code
110 FROM msc_system_items msi,
111 msc_trading_partners tp,
112 msc_trading_partner_maps map
113 WHERE map.company_key = l_org_id
114 and map.map_type = 2
115 and map.tp_key = tp.partner_id
116 and tp.sr_tp_id = msi.organization_id
117 and msi.sr_instance_id = tp.sr_instance_id
118 and msi.inventory_item_id = p_inventory_item_id
119 and msi.plan_id = -1;
120
121 RETURN l_buyer_code;
122
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 RETURN NULL;
126
127 END GET_BUYER_CODE;
128
129 -- function get_xref_party_name takes in party_id s of a trading partner
130 -- and a cross referenced trading partner and returns the xref name of the
131 -- cross referenced trading partner
132 FUNCTION GET_XREF_PARTY_NAME (p_party_id IN NUMBER, p_xref_party_id IN NUMBER)
133 RETURN VARCHAR2 IS
134
135 l_xref_party_name VARCHAR2(80);
136
137 BEGIN
138
139 /* select max(tpx.xref_ext_value)
140 into l_xref_party_name
141 from ect_xref_dtl tpx
142 where tpx.xref_int_value = p_xref_party_id
143 and tpx.party_id = p_party_id
144 and tpx.direction = 'IN'
145 and tpx.xref_category_id = 9 ;
146
147 RETURN l_xref_party_name;
148
149
150 EXCEPTION
151 WHEN NO_DATA_FOUND THEN
152 RETURN NULL; */
153
154 return NULL;
155
156 END GET_XREF_PARTY_NAME;
157
158 ---FUNCTION CREATE_EXCH_PARTITIONS creates partitions in MSC_ITEM_EXCEPTION
159 -- and MSC_EXCEPTION_DETAILS in exchange 6.2
160
161 PROCEDURE CREATE_EXCH_PARTITIONS(p_status OUT NOCOPY NUMBER) IS
162
163 share_partition VARCHAR2(10) := 'X';
164 partition1_exists VARCHAR2(1) := 'N';
165 partition2_exists VARCHAR2(1) := 'N';
166 errbuf VARCHAR2(1000);
167 retcode NUMBER;
168
169 BEGIN
170
171 /* This function was used in Supply Chain Exchange and no
172 ** longer required in Oracle Collaborative Planning
173 */
174
175
176 null;
177
178 END CREATE_EXCH_PARTITIONS;
179
180 /*----------------------------------------------------+
181 | This procedure takes into 2 uom codes in the same |
182 | uom class or across classes and returns a conv |
183 | rate between them. |
184 | If a conversion is not found then it sets |
185 | the output variable conv found to FALSE |
186 | and returns a conv factor of 1. |
187 | Do not use this procedure to validate UOM's. Use |
188 | validate uom code. |
189 +-----------------------------------------------------*/
190
191 PROCEDURE GET_UOM_CONVERSION_RATES(p_uom_code IN VARCHAR2,
192 p_dest_uom_code IN VARCHAR2,
193 p_inventory_item_id IN NUMBER DEFAULT 0,
194 p_conv_found OUT NOCOPY BOOLEAN,
195 p_conv_rate OUT NOCOPY NUMBER) IS
196 l_uom_class VARCHAR2(10);
197 l_dest_uom_class VARCHAR2(10);
198 BEGIN
199
200
201 /*-------------------------------------------------------------+
202 | Rownum = 1 is used to account for the corner case APS bug |
203 | when the same uom code points to different unit of measures |
204 | in multiple instances. This can be removed when APS makes |
205 | the fix to allow only 1 uom code in addition to unit of |
206 | measure in MSC_UNITS_OF_MEASURE. |
207 +--------------------------------------------------------------*/
208
209 /*-----------------------------------------------------+
210 | Inventory Item Id = non zero is required only if |
211 | we are doing conversions across uom classes |
212 +------------------------------------------------------*/
213
214 BEGIN
215 select uom_class
216 into l_uom_class
217 from msc_units_of_measure
218 where uom_code = p_uom_code
219 and rownum = 1;
220 EXCEPTION WHEN no_data_found then
221 p_conv_found := FALSE;
222 p_conv_rate := 1.0;
223 return;
224 END;
225
226 BEGIN
227 select uom_class
228 into l_dest_uom_class
229 from msc_units_of_measure
230 where uom_code = p_dest_uom_code
231 and rownum = 1;
232 EXCEPTION WHEN no_data_found then
233 p_conv_found := FALSE;
234 p_conv_rate := 1.0;
235 return;
236 END;
237
238
239 if(l_uom_class = l_dest_uom_class) then
240 BEGIN
241 select muc1.conversion_rate/muc2.conversion_rate
242 INTO
243 p_conv_rate
244 FROM
245 msc_uom_conversions muc1,
246 msc_uom_conversions muc2
247 where muc1.inventory_item_id = 0
248 and muc2.inventory_item_id = 0
249 and muc1.uom_class = muc2.uom_class
250 and muc1.uom_class = l_uom_class
251 and muc1.uom_code = p_uom_code
252 and muc2.uom_code = p_dest_uom_code
253 and rownum = 1;
254 EXCEPTION when NO_DATA_FOUND then
255 p_conv_found := FALSE;
256 p_conv_rate := 1.0;
257 return;
258 END;
259
260 else
261
262 BEGIN
263 select muc.conversion_rate
264 INTO
265 p_conv_rate
266 FROM
267 msc_uom_conversions_view muc
268 where muc.inventory_item_id = p_inventory_item_id
269 and muc.primary_uom_code = p_uom_code
270 and muc.uom_code = p_dest_uom_code
271 and rownum = 1;
272 EXCEPTION when NO_DATA_FOUND then
273
274 BEGIN
275 select muc.conversion_rate
276 INTO
277 p_conv_rate
278 FROM
279 msc_uom_conversions_view muc
280 where muc.inventory_item_id = p_inventory_item_id
281 and muc.primary_uom_code = p_dest_uom_code
282 and muc.uom_code = p_uom_code
283 and rownum = 1;
284 EXCEPTION when NO_DATA_FOUND then
285 p_conv_found := FALSE;
286 p_conv_rate := 1.0;
287 return;
288 END;
289 END;
290
291 end if;
292
293 p_conv_found := TRUE;
294 return;
295
296 END;
297
298 FUNCTION GET_DEFAULT_RES_DATE(p_type IN NUMBER,
299 p_ship_date IN DATE,
300 p_rcpt_date IN DATE,
301 p_supplier_id IN NUMBER,
302 p_customer_id IN NUMBER,
303 p_order_type IN NUMBER) return DATE
304 IS
305
306 l_result_date DATE;
307 BEGIN
308
309 /*------------------------------------------------------+
310 | The behaviour of sales forecast is different from |
311 | other order types. Hence it is handled first. |
312 +-------------------------------------------------------*/
313
314 if(p_order_type = 1) then /* Sales Forecast */
315 if(p_ship_date is null) then
316 l_result_date := p_rcpt_date;
317 elsif (p_rcpt_date is null) then
318 l_result_date := p_ship_date;
319 end if;
320
321 return l_result_date;
322
323
324 end if;
325
326
327 if(p_type = 2) then
328
329 if(p_order_type in (13, 15, 16, 17, 2, 20)) then
330 /* Outbound docs to Supp's */
331 l_result_date := p_rcpt_date;
332 elsif (p_order_type in (14,15, 21, 3, 6)) then
333 /* Inbound docs from Supp's */
334 if(p_ship_date is null) then
335 l_result_date := p_rcpt_date;
336 elsif (p_rcpt_date is null) then
337 l_result_date := p_ship_date;
338 end if;
339
340 end if;
341
342 elsif(p_type = 1) then
343
344 if(p_order_type in (13, 15, 16, 17, 2, 20)) then
345 /* Inbound docs from customers */
346 l_result_date := p_rcpt_date;
347 elsif (p_order_type in (14,15, 21, 3, 6)) then
348 /* Outbound docs to customers */
349 if(p_ship_date is null) then
350 l_result_date := p_rcpt_date;
351 elsif (p_rcpt_date is null) then
352 l_result_date := p_ship_date;
353 end if;
354
355 end if;
356
357 end if;
358
359 return l_result_date;
360
361 END;
362
363 FUNCTION UPDATE_SHIP_RCPT_DATES (
364 p_customer_id IN NUMBER,
365 p_customer_site_id IN NUMBER,
366 p_supplier_id IN NUMBER,
367 p_supplier_site_id IN NUMBER,
368 p_order_type IN NUMBER,
369 p_item_id IN NUMBER,
370 p_ship_date IN DATE,
371 p_rcpt_date IN DATE) RETURN DATE IS
372
373 l_org_id NUMBER NULL;
374 l_supplier_id NUMBER NULL;
375 l_supplier_site_id NUMBER NULL;
376 l_customer_id NUMBER NULL;
377 l_customer_site_id NUMBER NULL;
378 l_tp_org_partner_id NUMBER NULL;
379 l_sr_tp_id NUMBER NULL;
380 l_sr_instance_id NUMBER NULL;
381 l_company_id NUMBER NULL;
382 l_tp_customer_id NUMBER NULL;
383 l_tp_customer_site_id NUMBER NULL;
384 l_tp_supplier_id NUMBER NULL;
385 l_tp_supplier_site_id NUMBER NULL;
386 l_location_id NUMBER NULL;
387 l_lead_time NUMBER NULL;
388 l_result_date DATE NULL;
389 l_session_id NUMBER NULL;
390 l_org_location_id NUMBER NULL;
391 l_regions_return_status VARCHAR2(1);
392
393 l_return_status VARCHAR2(1);
394 l_ship_method varchar2(30);
395
396 BEGIN
397
398
399 if((p_customer_id <> 1) and (p_supplier_id <> 1)) then
400
401 if(p_ship_date is null) then
402 l_result_date := p_rcpt_date;
403 elsif (p_rcpt_date is null) then
404 l_result_date := p_ship_date;
405 end if;
406
407 return l_result_date;
408
409 end if;
410
411
412
413
414 /*----------------------------------------------------------+
415 | Before the ATP routines are called to return intransit |
416 | times, the correct variables need to be passed |
417 +-----------------------------------------------------------*/
418
419
420 if(p_customer_id = 1) then /* Buyer is the OEM */
421
422 if (p_supplier_id is not null) then
423
424 l_org_id := p_customer_site_id;
425 l_supplier_id := p_supplier_id;
426 l_supplier_site_id := p_supplier_site_id;
427 l_customer_id := null;
428 l_customer_site_id := null;
429 l_company_id := p_customer_id; /* Just used for mapping to
430 tp schema */
431
432 end if;
433
434 end if;
435
436 if(p_supplier_id = 1) /* Seller is the OEM */ then
437
438 if ((p_customer_id is not null) and
439 (p_order_type in (14, 15, 21, 3, 6, 1))) then
440
441 l_org_id := p_supplier_site_id;
442 l_customer_id := p_customer_id;
443 l_customer_site_id := p_customer_site_id;
444 l_supplier_id := NULL;
445 l_supplier_site_id := NULL;
446 l_company_id := p_supplier_id; /* Just used for mapping to
447 * TP schema */
448
449
450 elsif((p_customer_id is not null) AND
451 (p_order_type in (13, 15, 16, 17, 2, 20, 1))) then
452
453 l_org_id := p_supplier_site_id;
454 l_customer_id := p_customer_id;
455 l_customer_site_id := p_customer_site_id;
456 l_supplier_id := NULL;
457 l_supplier_site_id := NULL;
458 l_company_id := p_customer_id; /* Just used for mapping to
459 TP schema */
460
461 end if;
462
463 end if;
464
465
466
467 /*--------------------------------------------------------+
468 | For material bound to customers, the lead time is based |
469 | on the location to location lead time. For |
470 | material from suppliers, the lead time is based on |
471 | lead time on the asl for the item/supplier |
472 +---------------------------------------------------------*/
473
474 if(l_customer_id is not null)
475 then
476
477
478 /*-----------------------------------------------------------+
479 | Map the id's to the TP schema, these will be passed to the |
480 | ATP functions |
481 +------------------------------------------------------------*/
482
483 /*-----------------------------------------------------------+
484 | First get the partner id corresponding to the org |
485 +------------------------------------------------------------*/
486
487 BEGIN
488 SELECT tp_key
489 INTO l_tp_org_partner_id
490 FROM msc_trading_partner_maps map
491 WHERE map.map_type = 2
492 and map.company_key = l_org_id;
493 EXCEPTION WHEN OTHERS THEN
494 l_result_date := get_default_res_date(1,
495 p_ship_date,
496 p_rcpt_date,
497 p_supplier_id,
498 p_customer_id,
499 p_order_type);
500 return l_result_date;
501 END;
502
503
504 /*--------------------------------------------------------------+
505 | Get the sr_tp_id and sr_instance_id corresponding to the orgs |
506 +---------------------------------------------------------------*/
507
508 BEGIN
509 SELECT sr_tp_id,
510 sr_instance_id
511 INTO l_sr_tp_id,
512 l_sr_instance_id
513 FROM msc_trading_partners
514 WHERE partner_id = l_tp_org_partner_id;
515 EXCEPTION WHEN OTHERS THEN
516 l_result_date := get_default_res_date(1,
517 p_ship_date,
518 p_rcpt_date,
519 p_supplier_id,
520 p_customer_id,
521 p_order_type);
522 return l_result_date;
523 END;
524
525 /*-----------------------------------------------+
526 | Get the sr_tp_id for the customer from the |
527 | corresponding instance in the lid table. |
528 +------------------------------------------------*/
529
530
531 BEGIN
532 SELECT tpl.sr_tp_id INTO
533 l_tp_customer_id
534 FROM
535 msc_tp_id_lid tpl,
536 msc_trading_partner_maps map,
537 msc_company_relationships rels
538 WHERE tpl.tp_id = map.tp_key
539 and tpl.partner_type = 2
540 and tpl.sr_company_id = -1
541 and tpl.sr_instance_id = l_sr_instance_id
542 and map.map_type = 1
543 and map.company_key = rels.relationship_id
544 and rels.object_id = l_customer_id
545 and rels.subject_id = l_company_id
546 and rels.relationship_type = 1;
547 EXCEPTION WHEN OTHERS THEN
548 l_result_date := get_default_res_date(1,
549 p_ship_date,
550 p_rcpt_date,
551 p_supplier_id,
552 p_customer_id,
553 p_order_type);
554 return l_result_date;
555 END;
556
557
558
559 /*-------------------------------------------------------+
560 | Map the customer site and get its source id from the |
561 | lid table for the corresponding instance. |
562 | If multiple sites are found use the SHIP_TO site. |
563 +--------------------------------------------------------*/
564
565 BEGIN
566 SELECT tps.sr_tp_site_id into
567 l_tp_customer_site_id
568 FROM
569 msc_tp_site_id_lid tps,
570 msc_trading_partner_maps map
571 WHERE tps.sr_company_id = -1
572 and tps.tp_site_id = map.tp_key
573 and tps.sr_instance_id = l_sr_instance_id
574 and tps.partner_type = 2
575 and map.map_type = 3
576 and map.company_key = l_customer_site_id;
577 EXCEPTION WHEN TOO_MANY_ROWS THEN
578 BEGIN
579 SELECT tps.sr_tp_site_id into
580 l_tp_customer_site_id
581 FROM
582 msc_tp_site_id_lid tps,
583 msc_trading_partner_sites tp_sites,
584 msc_trading_partner_maps map
585 WHERE tps.sr_company_id = -1
586 and tps.tp_site_id = tp_sites.partner_site_id
587 and tps.sr_instance_id = l_sr_instance_id
588 and tps.partner_type = 2
589 and tp_sites.partner_site_id = map.tp_key
590 and tp_sites.tp_site_code = 'SHIP_TO'
591 and map.map_type = 3
592 and map.company_key = l_customer_site_id;
593 EXCEPTION WHEN OTHERS THEN
594 l_result_date := get_default_res_date(1,
595 p_ship_date,
596 p_rcpt_date,
597 p_supplier_id,
598 p_customer_id,
599 p_order_type);
600 return l_result_date;
601 END;
602 WHEN OTHERS THEN
603 l_result_date := get_default_res_date(1,
604 p_ship_date,
605 p_rcpt_date,
606 p_supplier_id,
607 p_customer_id,
608 p_order_type);
609 return l_result_date;
610 END;
611
612
613 /*-----------------------------------------------------+
614 | The ATP API's to get lead time will use region level |
615 | leadtime if location level lead time is not found |
616 +------------------------------------------------------*/
617
618
619 --===============
620 -- Get session id
621 --===============
622 select mrp_atp_schedule_temp_s.nextval
623 into l_session_id
624 from dual;
625
626 BEGIN
627
628 MSC_SATP_FUNC.GET_REGIONS(l_tp_customer_site_id,
629 724, -- Calling Module is 'MSC'
630 l_sr_instance_id,
631 l_session_id,
632 null,
633 l_regions_return_status);
634 EXCEPTION WHEN OTHERS THEN
635 l_result_date := get_default_res_date(1,
636 p_ship_date,
637 p_rcpt_date,
638 p_supplier_id,
639 p_customer_id,
640 p_order_type);
641 return l_result_date;
642
643 END;
644
645
646 /*-----------------------------------------------+
647 | Get the default ship to/deliver from location |
648 | for the org. |
649 +------------------------------------------------*/
650
651 BEGIN
652 l_org_location_id :=
653
654 msc_atp_func.get_location_id(
655 l_sr_instance_id,
656 l_sr_tp_id,
657 null,
658 null,
659 null,
660 null);
661 EXCEPTION WHEN OTHERS then
662 l_result_date := get_default_res_date(1,
663 p_ship_date,
664 p_rcpt_date,
665 p_supplier_id,
666 p_customer_id,
667 p_order_type);
668 return l_result_date;
669 END;
670
671
672 /*-----------------------------------------------+
673 | Get the default ship to/deliver from location |
674 | for the customer/supplier |
675 +------------------------------------------------*/
676
677 BEGIN
678 l_location_id := msc_atp_func.get_location_id(
679 l_sr_instance_id,
680 null,
681 l_tp_customer_id,
682 l_tp_customer_site_id,
683 l_tp_supplier_id,
684 l_tp_supplier_site_id);
685
686 EXCEPTION WHEN OTHERS then
687 l_result_date := get_default_res_date(1,
688 p_ship_date,
689 p_rcpt_date,
690 p_supplier_id,
691 p_customer_id,
692 p_order_type);
693 return l_result_date;
694 END;
695
696
697 BEGIN
698 l_lead_time := MSC_SCATP_PUB.get_default_intransit_time (
699 l_org_location_id,
700 l_sr_instance_id,
701 l_location_id,
702 l_sr_instance_id,
703 l_session_id,
704 l_tp_customer_site_id);
705 EXCEPTION WHEN OTHERS then
706 l_result_date := get_default_res_date(1,
707 p_ship_date,
708 p_rcpt_date,
709 p_supplier_id,
710 p_customer_id,
711 p_order_type);
712 return l_result_date;
713 END;
714
715
716 if(p_ship_date is null) then
717 l_result_date := p_rcpt_date - nvl(l_lead_time, 0);
718 elsif (p_rcpt_date is null) then
719 l_result_date := p_ship_date + nvl(l_lead_time, 0);
720 end if;
721
722 return l_result_date;
723
724 elsif (l_supplier_id is not null) then
725 /*-----------------------------------------------------------+
726 | Map the id's to the TP schema, these will be passed to the |
727 | ATP functions |
728 +------------------------------------------------------------*/
729
730 /*-----------------------------------------------------------+
731 | First get the partner id corresponding to the org |
732 +------------------------------------------------------------*/
733
734 BEGIN
735 SELECT tp_key
736 INTO l_tp_org_partner_id
737 FROM msc_trading_partner_maps map
738 WHERE map.map_type = 2
739 and map.company_key = l_org_id;
740 EXCEPTION WHEN OTHERS THEN
741 l_result_date := get_default_res_date(1,
742 p_ship_date,
743 p_rcpt_date,
744 p_supplier_id,
745 p_customer_id,
746 p_order_type);
747 return l_result_date;
748 END;
749
750
751 /*--------------------------------------------------------------+
752 | Get the sr_tp_id and sr_instance_id corresponding to the orgs |
753 +---------------------------------------------------------------*/
754
755 BEGIN
756 SELECT sr_tp_id,
757 sr_instance_id
758 INTO l_sr_tp_id,
759 l_sr_instance_id
760 FROM msc_trading_partners
761 WHERE partner_id = l_tp_org_partner_id;
762 EXCEPTION WHEN OTHERS THEN
763 l_result_date := get_default_res_date(1,
764 p_ship_date,
765 p_rcpt_date,
766 p_supplier_id,
767 p_customer_id,
768 p_order_type);
769 return l_result_date;
770 END;
771
772 /*------------------------------------------------+
773 | Get the id of the supplier and supplier site in |
774 | the TP schema. |
775 +-------------------------------------------------*/
776
777 BEGIN
778 SELECT map.tp_key INTO
779 l_tp_supplier_id
780 FROM
781 msc_trading_partner_maps map,
782 msc_company_relationships rels
783 WHERE map.map_type = 1
784 and map.company_key = rels.relationship_id
785 and rels.object_id = l_supplier_id
786 and rels.subject_id = l_company_id
787 and rels.relationship_type = 2;
788 EXCEPTION WHEN OTHERS THEN
789 l_result_date := get_default_res_date(2,
790 p_ship_date,
791 p_rcpt_date,
792 p_supplier_id,
793 p_customer_id,
794 p_order_type);
795 return l_result_date;
796 END;
797
798
799 BEGIN
800 SELECT map.tp_key INTO
801 l_tp_supplier_site_id
802 FROM
803 msc_trading_partner_maps map
804 WHERE map.map_type = 3
805 and map.company_key = l_supplier_site_id;
806 EXCEPTION WHEN OTHERS THEN
807 l_result_date := get_default_res_date(2,
808 p_ship_date,
809 p_rcpt_date,
810 p_supplier_id,
811 p_customer_id,
812 p_order_type);
813 return l_result_date;
814 END;
815
816 /*-----------------------------------------------------+
817 | The ATP API's to get lead time will use region level |
818 | leadtime if location level lead time is not found |
819 +------------------------------------------------------*/
820
821 --===============
822 -- Get session id
823 --===============
824 BEGIN
825 select mrp_atp_schedule_temp_s.nextval
826 into l_session_id
827 from dual;
828
829 MSC_ATP_PROC.ATP_Intransit_LT(
830 2 , --- Destination
831 l_session_id, -- session_id
832 null, -- from_org_id
833 null, -- from_loc_id
834 l_tp_supplier_site_id, -- from_vendor_site_id
835 l_sr_instance_id, -- p_to_instance_id
836 --null, -- p_from_instance_id
837 l_sr_tp_id, -- p_to_org_id
838 null, -- p_to_loc_id
839 null, -- p_to_customer_site_id
840 l_sr_instance_id, -- p_to_instance_id
841 l_ship_method, -- p_ship_method
842 l_lead_time, -- x_intransit_lead_time
843 l_return_status -- x_return_status
844 );
845
846
847 IF (l_return_status = FND_API.G_RET_STS_ERROR) then
848 l_result_date := get_default_res_date(2,
849 p_ship_date,
850 p_rcpt_date,
851 p_supplier_id,
852 p_customer_id,
853 p_order_type);
854 return l_result_date;
855 END IF;
856
857 EXCEPTION
858 WHEN OTHERS THEN
859 l_result_date := get_default_res_date(2,
860 p_ship_date,
861 p_rcpt_date,
862 p_supplier_id,
863 p_customer_id,
864 p_order_type);
865 return l_result_date;
866 END;
867
868 if(p_order_type in (1, 3, 6, 21, 14, 15))
869 then
870 if(p_ship_date is null) then
871 l_result_date := p_rcpt_date - nvl(l_lead_time, 0);
872 elsif (p_rcpt_date is null) then
873 l_result_date := p_ship_date + nvl(l_lead_time, 0);
874 end if;
875 else
876 l_result_date := p_rcpt_date - nvl(l_lead_time,0);
877 end if;
878 return l_result_date;
879
880 end if;
881
882
883 END UPDATE_SHIP_RCPT_DATES;
884
885 FUNCTION GET_CUSTOMER_TRANSIT_TIME(p_publisher_id IN NUMBER,
886 p_publisher_site_id IN NUMBER,
887 p_customer_id IN NUMBER,
888 p_customer_site_id IN NUMBER) RETURN NUMBER IS
889 rcpt_date DATE;
890 ship_date DATE := sysdate;
891 jul_rcpt_date NUMBER;
892 jul_ship_date NUMBER;
893 lead_time NUMBER := 0;
894
895 BEGIN
896
897
898 select to_number(to_char(sysdate, 'j'))
899 INTO jul_ship_date
900 FROM dual;
901
902
903 /* This function returns the lead time between an OEM org and a
904 ** customer/customer site.
905 ** It is built on top of update_ship_rcpt_dates fn. It passes
906 ** in a ship date and gets back a recipt date. The difference
907 ** between the 2 is returned as lead time.
908 */
909
910
911 BEGIN
912 rcpt_date := UPDATE_SHIP_RCPT_DATES (
913 p_customer_id,
914 p_customer_site_id,
915 p_publisher_id,
916 p_publisher_site_id,
917 14, /* Hard Coded to Sales Order */
918 NULL,
919 ship_date,
920 NULL);
921 EXCEPTION WHEN OTHERS THEN
922 return lead_time;
923 END;
924
925
926 select to_number(to_char(rcpt_date, 'j'))
927 INTO jul_rcpt_date FROM dual;
928
929 lead_time := GREATEST(jul_rcpt_date - jul_ship_date, 0);
930
931 return lead_time;
932
933 END;
934
935 --------------------------------------------------------------------------
936 -- Function GET_LOOKUP_MEANING
937 ----------------------------------------------------------------------
938 FUNCTION GET_LOOKUP_MEANING(p_lookup_type in varchar2,
939 p_order_type_code in Number) RETURN Varchar2 IS
940 l_order_type_desc varchar2(240);
941 BEGIN
942 --Get the order type desc. Takes care of order type renaming.
943 BEGIN
944 select meaning
945 into l_order_type_desc
946 from mfg_lookups
947 where lookup_type = p_lookup_type
948 and lookup_code = p_order_type_code;
949
950 return l_order_type_desc;
951 EXCEPTION
952 WHEN OTHERS THEN
953 l_order_type_desc := null;
954 return l_order_type_desc;
955 END;
956
957 END GET_LOOKUP_MEANING;
958
959 PROCEDURE SCE_TO_APS(
960 p_map_type IN NUMBER,
961 p_sce_company_id IN NUMBER,
962 p_sce_company_site_id IN NUMBER,
963 p_relationship_type IN NUMBER,
964 aps_partner_id OUT NOCOPY NUMBER,
965 aps_partner_site_id OUT NOCOPY NUMBER,
966 aps_sr_instance_id OUT NOCOPY NUMBER
967 )
968 IS
969
970 BEGIN
971
972 --dbms_output.put_line(' p_map_type '|| p_map_type );
973 --dbms_output.put_line(' p_sce_company_id '|| p_sce_company_id );
974 --dbms_output.put_line(' p_sce_company_site_id '|| p_sce_company_site_id );
975 if (p_map_type = G_ORGANIZATION_MAPPING) then
976
977 SELECT tp.sr_tp_id,tp.sr_instance_id
978 INTO aps_partner_id, aps_sr_instance_id
979 FROM msc_trading_partner_maps map,
980 msc_trading_partners tp
981 WHERE map.map_type = G_ORGANIZATION_MAPPING
982 AND map.company_key = p_sce_company_site_id
983 AND map.tp_key = tp.partner_id;
984
985 elsif (p_map_type = G_COMPANY_MAPPING) then
986
987 --dbms_output.put_line(' Inside SCE TO APS ' );
988 SELECT map.tp_key
989 INTO aps_partner_id
990 FROM msc_trading_partner_maps map,
991 msc_company_relationships cr
992 WHERE map.map_type = G_COMPANY_MAPPING
993 AND cr.object_id = p_sce_company_id
994 AND map.company_key = cr.relationship_id
995 AND cr.relationship_type = p_relationship_type
996 AND cr.subject_id = OEM_COMPANY_ID;
997
998 --dbms_output.put_line(' aps_partner_id : ' || aps_partner_id);
999 if (p_sce_company_site_id is not null) then
1000
1001 begin
1002 SELECT map.tp_key
1003 INTO aps_partner_site_id
1004 FROM msc_trading_partner_maps map
1005 WHERE map.map_type = G_COMPANY_SITE_MAPPING
1006 AND map.company_key = p_sce_company_site_id;
1007 exception
1008 when too_many_rows then
1009 BEGIN
1010 SELECT tp_sites.partner_site_id
1011 INTO aps_partner_site_id
1012 FROM msc_trading_partner_sites tp_sites,
1013 msc_trading_partner_maps map
1014 WHERE tp_sites.partner_site_id = map.tp_key
1015 and tp_sites.tp_site_code = 'SHIP_TO'
1016 and tp_sites.partner_type = 2
1017 and map.map_type = G_COMPANY_SITE_MAPPING
1018 and map.company_key = p_sce_company_site_id;
1019
1020 EXCEPTION
1021 WHEN OTHERS THEN
1022 RAISE;
1023 END;
1024
1025 when others then
1026 raise;
1027 end;
1028 end if;
1029
1030 end if;
1031
1032 --dbms_output.put_line(' aps_partner_id = ' || aps_partner_id);
1033 --dbms_output.put_line(' aps_partner_site_id : ' || aps_partner_site_id);
1034 --dbms_output.put_line(' aps_sr_instance_id = '||aps_sr_instance_id);
1035 EXCEPTION
1036
1037 WHEN OTHERS THEN
1038 RAISE;
1039
1040
1041 END SCE_TO_APS;
1042
1043 PROCEDURE GET_CALENDAR_CODE(
1044 p_supplier_id in number,
1045 p_supplier_site_id in number,
1046 p_customer_id in number,
1047 p_customer_site_id in number,
1048 p_calendar_code out nocopy varchar2,
1049 p_sr_instance_id out nocopy number,
1050 p_tp_ids in number default 1, --1 means CP,2 means APS
1051 p_tp_instance_id in number default 99999,
1052 p_oem_ident in number default 3)
1053 IS
1054
1055 lv_calendar_code varchar2(30);
1056
1057 aps_org_partner_id number;
1058 aps_org_site_partner_id number;
1059 aps_sr_instance_id number;
1060
1061 aps_cust_partner_id number;
1062 aps_cust_partner_site_id number;
1063
1064 aps_supp_partner_id number;
1065 aps_supp_partner_site_id number;
1066
1067 BEGIN
1068
1069 if (p_tp_ids = 1) then /* TP ids are in CP schema */
1070 if (p_supplier_id <> 1) then
1071 /* Get the APS Ids for the Supplier company id */
1072 SCE_TO_APS(G_COMPANY_MAPPING ,
1073 p_supplier_id,
1074 p_supplier_site_id,
1075 2, --- supplier relationship
1076 aps_supp_partner_id,
1077 aps_supp_partner_site_id,
1078 aps_sr_instance_id
1079 );
1080 end if;
1081
1082 if (p_customer_id <> 1) then
1083 /* Get the APS Ids for the Customer company id */
1084 SCE_TO_APS(G_COMPANY_MAPPING,
1085 p_customer_id,
1086 p_customer_site_id,
1087 1, --- customer relationship
1088 aps_cust_partner_id,
1089 aps_cust_partner_site_id,
1090 aps_sr_instance_id
1091 );
1092 end if;
1093 else
1094 /* This code added for VMI to use the Calendar hierarchy */
1095 /* TP ids are in APS schema */
1096 aps_supp_partner_id := p_supplier_id;
1097 aps_supp_partner_site_id := p_supplier_site_id;
1098 aps_cust_partner_id := p_customer_id;
1099 aps_cust_partner_site_id := p_customer_site_id;
1100 aps_sr_instance_id := p_tp_instance_id;
1101
1102 ---1-Supplier is OEM , 2 means Customer is OEM,3 means IDs are in terms of CP
1103 if (p_oem_ident = 1) then
1104 aps_org_partner_id := p_supplier_site_id;
1105 elsif (p_oem_ident = 2) then
1106 aps_org_partner_id := p_customer_site_id;
1107 end if;
1108
1109 end if;
1110
1111 if (p_supplier_id = 1 and p_tp_ids=1) OR
1112 (p_oem_ident=1 and p_tp_ids = 2) then --- OEM is the supplier
1113 if (p_tp_ids = 1) then /* TP ids are in CP schema */
1114 /* Get the APS sr_instance_id and partner_id for the OEM */
1115 SCE_TO_APS(G_ORGANIZATION_MAPPING ,
1116 p_supplier_id,
1117 p_supplier_site_id,
1118 null, --- relationship
1119 aps_org_partner_id,
1120 aps_org_site_partner_id,
1121 aps_sr_instance_id
1122 );
1123 end if;
1124
1125 begin
1126 /* Get the Customer Receiving calendar */
1127 select nvl(ca1.CALENDAR_CODE, ca.CALENDAR_CODE)
1128 into lv_calendar_code
1129 from msc_calendar_assignments ca,
1130 msc_calendar_assignments ca1
1131 where ca.sr_instance_id = aps_sr_instance_id
1132 and ca.CALENDAR_TYPE = 'RECEIVING'
1133 and ca.partner_type = 2
1134 and ca.partner_id = aps_cust_partner_id
1135 and ca.ORGANIZATION_ID is null
1136 and ca.ASSOCIATION_TYPE = G_CUSTOMER
1137 and ca1.sr_instance_id(+) = ca.sr_instance_id
1138 and ca1.CALENDAR_TYPE(+) = ca.CALENDAR_TYPE
1139 and ca1.partner_type(+) = ca.partner_type
1140 and ca1.ORGANIZATION_ID is null
1141 and ca1.ASSOCIATION_TYPE(+) = G_CUSTOMER_SITE
1142 and ca1.partner_id(+) = ca.partner_id
1143 and ca1.partner_site_id(+) = aps_cust_partner_site_id;
1144
1145 exception
1146 when no_data_found then
1147
1148 begin
1149 /* If there is no customer calendar, get the OEM Org Shipping or Mfg calendar */
1150 select nvl(ca.CALENDAR_CODE, tp.calendar_code)
1151 into lv_calendar_code
1152 from msc_trading_partners tp,
1153 msc_calendar_assignments ca
1154 where tp.sr_instance_id = aps_sr_instance_id
1155 and tp.sr_tp_id = aps_org_partner_id
1156 and tp.partner_type = 3
1157 and ca.sr_instance_id(+) = tp.sr_instance_id
1158 and ca.ORGANIZATION_ID(+) = tp.sr_tp_id
1159 and ca.partner_type(+) = tp.partner_type
1160 and ca.CALENDAR_TYPE(+) = 'SHIPPING'
1161 and ca.ASSOCIATION_TYPE(+) = G_ORGANIZATION;
1162
1163 exception
1164 when others then
1165 raise;
1166 end;
1167
1168 when others then
1169 raise;
1170 end;
1171
1172 elsif (p_customer_id = 1 and p_tp_ids=1) OR
1173 (p_oem_ident=2 and p_tp_ids = 2) then --- OEM is the customer
1174
1175 if (p_tp_ids = 1) then /* TP ids are in CP schema */
1176 /* Get the APS sr_instance_id and partner_id for the OEM */
1177 SCE_TO_APS(G_ORGANIZATION_MAPPING ,
1178 p_customer_id,
1179 p_customer_site_id,
1180 null, --- relationship type
1181 aps_org_partner_id,
1182 aps_org_site_partner_id,
1183 aps_sr_instance_id
1184 );
1185 --dbms_output.put_line(' sr_tp _id = ' || aps_org_partner_id);
1186 --dbms_output.put_line(' Org sr_instance_id = ' || aps_sr_instance_id);
1187 end if;
1188
1189
1190 /* Get the Org Receiving Calendar, if not available then get the OEM Orgs' Mfg calendar */
1191 select nvl(ca.CALENDAR_CODE, tp.calendar_code)
1192 into lv_calendar_code
1193 from msc_calendar_assignments ca,
1194 msc_trading_partners tp
1195 where tp.sr_instance_id = aps_sr_instance_id
1196 and tp.sr_tp_id = aps_org_partner_id
1197 and tp.partner_type = 3
1198 and ca.sr_instance_id(+) = tp.sr_instance_id
1199 and ca.ORGANIZATION_ID(+) = tp.sr_tp_id
1200 and ca.partner_type(+) = tp.partner_type
1201 and ca.CALENDAR_TYPE(+) = 'RECEIVING'
1202 and ca.ASSOCIATION_TYPE(+) = G_ORGANIZATION;
1203
1204 elsif (p_supplier_id is null and nvl(p_customer_id,-1) <> 1) then
1205 /* This is added as per UI reqmnt in the case where
1206 they dont call with supplier(OEM) info
1207 This will be called by UI for OEM to Customer */
1208
1209 begin
1210 /* Get the Customer Receiving calendar */
1211 select nvl(ca1.CALENDAR_CODE, ca.CALENDAR_CODE),
1212 nvl(ca1.sr_instance_id,ca.sr_instance_id)
1213 into lv_calendar_code,
1214 aps_sr_instance_id
1215 from msc_calendar_assignments ca,
1216 msc_calendar_assignments ca1
1217 where ca.CALENDAR_TYPE = 'RECEIVING'
1218 and ca.partner_type = 2
1219 and ca.partner_id = aps_cust_partner_id
1220 and ca.ORGANIZATION_ID is null
1221 and ca.ASSOCIATION_TYPE = G_CUSTOMER
1222 and ca1.sr_instance_id(+) = ca.sr_instance_id
1223 and ca1.CALENDAR_TYPE(+) = ca.CALENDAR_TYPE
1224 and ca1.partner_type(+) = ca.partner_type
1225 and ca1.ORGANIZATION_ID is null
1226 and ca1.ASSOCIATION_TYPE(+) = G_CUSTOMER_SITE
1227 and ca1.partner_id(+) = ca.partner_id
1228 and ca1.partner_site_id(+) = aps_cust_partner_site_id;
1229
1230 exception
1231 when others then
1232 raise;
1233 end;
1234
1235 else -- OEM is neither supplier/customer
1236
1237 begin
1238 /* Get the Customer or Customer Site Receiving calendar */
1239 select nvl(ca1.CALENDAR_CODE, ca.CALENDAR_CODE)
1240 into lv_calendar_code
1241 from msc_calendar_assignments ca,
1242 msc_calendar_assignments ca1
1243 where ca.sr_instance_id = aps_sr_instance_id
1244 and ca.CALENDAR_TYPE = 'RECEIVING'
1245 and ca.partner_type = 2
1246 and ca.partner_id = aps_cust_partner_id
1247 and ca.ORGANIZATION_ID is null
1248 and ca.ASSOCIATION_TYPE = G_CUSTOMER
1249 and ca1.sr_instance_id(+) = ca.sr_instance_id
1250 and ca1.CALENDAR_TYPE(+) = ca.CALENDAR_TYPE
1251 and ca1.partner_type(+) = ca.partner_type
1252 and ca1.ORGANIZATION_ID is null
1253 and ca1.ASSOCIATION_TYPE(+) = G_CUSTOMER_SITE
1254 and ca1.partner_id(+) = ca.partner_id
1255 and ca1.partner_site_id(+) = aps_cust_partner_site_id;
1256
1257 exception
1258 when no_data_found then
1259
1260 /* If there is no customer calendar, get the OEM Org Shipping or Mfg calendar */
1261 select nvl(ca.CALENDAR_CODE, tp.calendar_code)
1262 into lv_calendar_code
1263 from msc_trading_partners tp,
1264 msc_calendar_assignments ca
1265 where tp.sr_instance_id = aps_sr_instance_id
1266 and tp.sr_tp_id = aps_org_partner_id
1267 and tp.partner_type = 3
1268 and ca.sr_instance_id(+) = tp.sr_instance_id
1269 and ca.ORGANIZATION_ID(+) = tp.sr_tp_id
1270 and ca.partner_type(+) = tp.partner_type
1271 and ca.CALENDAR_TYPE(+) = 'SHIPPING'
1272 and ca.ASSOCIATION_TYPE(+) = G_ORGANIZATION;
1273
1274 when others then
1275 raise;
1276
1277 end;
1278
1279 end if;
1280
1281 p_calendar_code := lv_calendar_code;
1282 p_sr_instance_id := aps_sr_instance_id;
1283
1284 EXCEPTION
1285 WHEN OTHERS THEN
1286 p_calendar_code := nvl(fnd_profile.value('MSC_X_DEFAULT_CALENDAR'),'-1');
1287 begin
1288 select sr_instance_id
1289 into p_sr_instance_id
1290 from msc_calendar_dates
1291 where calendar_code = p_calendar_code
1292 and rownum = 1;
1293 exception
1294 when others then
1295 p_sr_instance_id := -1;
1296 end;
1297 END GET_CALENDAR_CODE;
1298
1299 FUNCTION GET_SHIPPING_CONTROL_ID(l_customer_id IN NUMBER,
1300 l_customer_site_id IN NUMBER,
1301 l_supplier_id IN NUMBER,
1302 l_supplier_site_id IN NUMBER)
1303 RETURN NUMBER IS
1304
1305 CURSOR C_SHIPPING_CONTROL(a_site_id NUMBER,
1306 a_partner_type NUMBER) IS
1307 SELECT decode(upper(mtps.shipping_control), 'BUYER', 2,1)
1308 from msc_trading_partner_maps mtpm,
1309 msc_trading_partner_sites mtps
1310 where mtpm.company_key = a_site_id
1311 and mtpm.map_type = G_COMPANY_SITE
1312 and mtpm.tp_key = mtps.partner_site_id
1313 and mtps.partner_type = a_partner_type;
1314
1315 CURSOR C_SHIPPING_CONTROL_SHIP_TO(a_site_id NUMBER,
1316 a_partner_type NUMBER) IS
1317 SELECT decode(upper(mtps.shipping_control), 'BUYER', 2,1)
1318 from msc_trading_partner_maps mtpm,
1319 msc_trading_partner_sites mtps
1320 where mtpm.company_key = a_site_id
1321 and mtpm.map_type = G_COMPANY_SITE
1322 and mtpm.tp_key = mtps.partner_site_id
1323 and mtps.partner_type = a_partner_type
1324 AND mtps.tp_site_code = 'SHIP_TO'
1325 ;
1326
1327 l_oem_type NUMBER;
1328 a_site_id NUMBER;
1329 a_partner_type NUMBER;
1330 l_shipping_control NUMBER;
1331
1332 BEGIN
1333
1334 /* -------------------------------------------------------+
1335 | Check if OEM is involved in transaction. If OEM is |
1336 | involved then determine if OEM is Customer or Supplier |
1337 +--------------------------------------------------------*/
1338
1339 IF (l_customer_id = OEM_COMPANY_ID) THEN
1340 l_oem_type := G_CUSTOMER;
1341 RETURN 1;
1342 ELSIF (l_supplier_id = OEM_COMPANY_ID) THEN
1343 l_oem_type := G_SUPPLIER;
1344 ELSE
1345 RETURN 1;
1346 END IF;
1347
1348 /*----------------------------------------------------------------+
1349 | If OEM is Customer the get shipping control from Supplier Sites|
1350 | or get shipping control from Customer sites. |
1351 +----------------------------------------------------------------*/
1352
1353 IF (l_oem_type = G_CUSTOMER) THEN
1354 a_site_id := l_supplier_site_id;
1355 a_partner_type := G_SUPPLIER;
1356 ELSE
1357 a_site_id := l_customer_site_id;
1358 a_partner_type := G_CUSTOMER;
1359 END IF;
1360
1361 BEGIN
1362 OPEN C_SHIPPING_CONTROL(a_site_id, a_partner_type);
1363
1364 IF (C_SHIPPING_CONTROL%ROWCOUNT = 1) THEN
1365 FETCH C_SHIPPING_CONTROL INTO l_shipping_control;
1366 ELSE
1367 OPEN C_SHIPPING_CONTROL_SHIP_TO(a_site_id, a_partner_type);
1368 FETCH C_SHIPPING_CONTROL_SHIP_TO INTO l_shipping_control;
1369 CLOSE C_SHIPPING_CONTROL_SHIP_TO;
1370 END IF;
1371 CLOSE C_SHIPPING_CONTROL;
1372
1373 RETURN l_shipping_control;
1374
1375 EXCEPTION WHEN OTHERS THEN
1376
1377 RETURN 1;
1378
1379 END;
1380
1381 EXCEPTION
1382 WHEN OTHERS THEN
1383 RETURN 1;
1384 END GET_SHIPPING_CONTROL_ID;
1385
1386 FUNCTION GET_SHIPPING_CONTROL(p_customer_name IN VARCHAR2,
1387 p_customer_site_name IN VARCHAR2,
1388 p_supplier_name IN VARCHAR2,
1389 p_supplier_site_name IN VARCHAR2)
1390 RETURN NUMBER IS
1391
1392 l_shipping_control NUMBER;
1393 l_customer_id NUMBER;
1394 l_customer_site_id NUMBER;
1395 l_supplier_id NUMBER;
1396 l_supplier_site_id NUMBER;
1397
1398 BEGIN
1399
1400 /*--------------------------------------------------------+
1401 | Get the Ids for Customer and Supplier and their sites |
1402 +--------------------------------------------------------*/
1403
1404 select mcs.company_id, mcs.company_site_id
1405 INTO l_customer_id, l_customer_site_id
1406 from msc_companies mc,
1407 msc_company_sites mcs
1408 where mc.company_id = mcs.company_id
1409 and upper(mc.company_name) = upper(p_customer_name)
1410 and upper(mcs.company_site_name) = upper(p_customer_site_name);
1411
1412 select mcs.company_id, mcs.company_site_id
1413 INTO l_supplier_id, l_supplier_site_id
1414 from msc_companies mc,
1415 msc_company_sites mcs
1416 where mc.company_id = mcs.company_id
1417 and upper(mc.company_name) = upper(p_supplier_name)
1418 and upper(mcs.company_site_name) = upper(p_supplier_site_name);
1419
1420 l_shipping_control := GET_SHIPPING_CONTROL_ID(
1421 l_customer_id,
1422 l_customer_site_id,
1423 l_supplier_id,
1424 l_supplier_site_id);
1425
1426 return l_shipping_control;
1427
1428 EXCEPTION
1429 WHEN OTHERS THEN
1430 RETURN 1;
1431 END GET_SHIPPING_CONTROL;
1432
1433
1434 -- function to return the buyer code for VMI
1435
1436 FUNCTION GET_BUYER_CODE (p_inventory_item_id IN NUMBER,
1437 p_organization_id IN NUMBER,
1438 p_sr_instance_id IN NUMBER
1439 ) RETURN VARCHAR2
1440 IS
1441 l_buyer_code VARCHAR2(240);
1442 BEGIN
1443
1444 SELECT distinct buyer_name into l_buyer_code
1445 FROM msc_system_items msi
1446 WHERE
1447 msi.organization_id=p_organization_id
1448 and msi.sr_instance_id = p_sr_instance_id
1449 and msi.inventory_item_id = p_inventory_item_id
1450 and msi.plan_id = -1;
1451
1452 RETURN l_buyer_code;
1453
1454 EXCEPTION
1455 WHEN NO_DATA_FOUND THEN
1456 RETURN NULL;
1457
1458 END GET_BUYER_CODE;
1459
1460
1461 END MSC_X_UTIL;
[Home] [Help]