1 PACKAGE BODY CS_STD as
2 /* $Header: csxcstdb.pls 120.3 2005/08/12 17:01:53 smisra noship $ */
3
4 -- Global variables
5 CurVal number; --used by the function GetNextValInPeriod.
6
7 /*******************************************************************************
8 ********************************************************************************
9 --
10 -- Private Functions/procedures
11 --
12 ********************************************************************************
13 *******************************************************************************/
14
15 procedure Get_Mfg_Org_Id(X_org_id OUT NOCOPY NUMBER) is
16 Org_id NUMBER;
17 BEGIN
18 X_org_id := Get_Item_Valdn_Orgzn_ID;
19 END Get_Mfg_Org_Id;
20 --
21 procedure Get_Order_Type_Id(X_order_type IN VARCHAR2,
22 X_order_type_id OUT NOCOPY NUMBER) is
23 BEGIN
24 null;
25 -- select transaction_type_id into X_order_type_id
26 -- from so_order_types
27 -- where name = X_order_type;
28 exception
29 when NO_DATA_FOUND then
30 Fnd_Message.Set_Name('CS','CS_ALL_INVALID_ORDER_TYPE');
31 Fnd_Message.Set_Token('ORDER_TYPE',X_Order_Type);
32 App_Exception.Raise_Exception;
33 END Get_Order_Type_Id;
34 --
35 procedure Get_Day_Uom(X_day_uom_code IN VARCHAR2,
36 X_day_uom OUT NOCOPY VARCHAR2) is
37 BEGIN
38 select unit_of_measure into X_day_uom
39 from mtl_units_of_measure
40 where uom_code = X_day_uom_code;
41 exception
42 when NO_DATA_FOUND then
43 Fnd_Message.Set_Name('CS','CS_ALL_INVALID_UOM_CODE');
44 Fnd_Message.Set_Token('UOM_CODE',X_Day_UOM_Code);
45 App_Exception.Raise_Exception;
46 END Get_Day_UOM;
47 --
48 procedure Get_Month_Uom(X_month_uom_code IN VARCHAR2,
49 X_month_uom OUT NOCOPY VARCHAR2) is
50 BEGIN
51 select unit_of_measure into X_month_uom
52 from mtl_units_of_measure
53 where uom_code = X_month_uom_code;
54 exception
55 when NO_DATA_FOUND then
56 Fnd_Message.Set_Name('CS','CS_ALL_INVALID_UOM_CODE');
57 Fnd_Message.Set_Token('UOM_CODE',X_Month_UOM_Code);
58 App_Exception.Raise_Exception;
59 END Get_Month_UOM;
60 --
61 procedure Get_Appl_Short_Name(X_flex_code IN VARCHAR2,
62 X_appl_short_name OUT NOCOPY VARCHAR2) is
63 BEGIN
64 X_appl_short_name := 'INV';
65 END Get_Appl_Short_Name;
66 --
67 /* Get the default order by selection and code. */
68 procedure Get_Default_Order_by(x_order_by_code IN VARCHAR2,
69 x_order_by_lookup_type IN VARCHAR2,
70 x_order_by OUT NOCOPY VARCHAR2 ) IS
71 BEGIN
72 SELECT Meaning
73 INTO X_order_by
74 FROM CS_LOOKUPS
75 WHERE lookup_type = x_order_by_lookup_type
76 AND lookup_code = x_order_by_code;
77 END Get_Default_Order_By;
78 --
79 --
80 --
81 --
82 /*******************************************************************************
83 ********************************************************************************
84 --
85 -- Public Functions/procedures
86 --
87 ********************************************************************************
88 *******************************************************************************/
89
90 procedure Get_Default_Values(
91 order_by_code IN VARCHAR2,
92 order_by_lookup_type IN VARCHAR2,
93 flex_code IN VARCHAR2 DEFAULT 'MSTK',
94 mfg_org_id IN OUT NOCOPY NUMBER,
95 appl_short_name IN OUT NOCOPY VARCHAR2,
96 order_by IN OUT NOCOPY VARCHAR2,
97 order_type IN VARCHAR2,
98 order_type_id IN OUT NOCOPY NUMBER,
99 day_uom_code IN VARCHAR2,
100 day_uom IN OUT NOCOPY VARCHAR2,
101 month_uom_code IN VARCHAR2,
102 month_uom IN OUT NOCOPY VARCHAR2
103 ) is
104 BEGIN
105 Get_Mfg_Org_Id(mfg_org_id);
106 Get_Appl_Short_Name(flex_code,appl_short_name);
107 Get_Default_Order_by(order_by_code,order_by_lookup_type,order_by);
108 --Get_Order_Type_Id(order_type, order_type_id);
109 Get_Day_Uom(day_uom_code, day_uom);
110 Get_Month_Uom(month_uom_code, month_uom);
111 END Get_Default_Values;
112 --
113 /* Overloaded Procedures Get_Default_Values */
114 procedure Get_Default_Values(
115 flex_code IN VARCHAR2 DEFAULT 'MSTK',
116 mfg_org_id IN OUT NOCOPY NUMBER,
117 appl_short_name IN OUT NOCOPY VARCHAR2
118 ) IS
119 BEGIN
120 Get_Mfg_Org_Id(mfg_org_id);
121 Get_Appl_Short_Name(flex_code,appl_short_name);
122 END Get_Default_Values;
123 --
124 procedure Get_Default_Values(flex_code IN VARCHAR2,
125 mfg_org_id IN OUT NOCOPY NUMBER,
126 mfg_org_name IN OUT NOCOPY VARCHAR2,
127 appl_short_name IN OUT NOCOPY VARCHAR2) is
128 begin
129 get_default_values(flex_code,mfg_org_id,appl_short_name);
130 --
131 select name
132 into mfg_org_name
133 from hr_organization_units
134 where organization_id = mfg_org_id;
135 end get_default_values;
136 --
137 procedure Get_Default_Values(
138 order_by_code IN VARCHAR2,
139 order_by_lookup_type IN VARCHAR2,
140 flex_code IN VARCHAR2 DEFAULT 'MSTK',
141 mfg_org_id IN OUT NOCOPY NUMBER,
142 appl_short_name IN OUT NOCOPY VARCHAR2,
143 order_by IN OUT NOCOPY VARCHAR2
144 ) IS
145 BEGIN
146 Get_Mfg_Org_Id(mfg_org_id);
147 Get_Appl_Short_Name(flex_code,appl_short_name);
148 Get_Default_Order_by(order_by_code,order_by_lookup_type,order_by);
149 END Get_Default_Values;
150 --
151 /*
152 function get_coterminated_end_dt
153 (
154 p_cot_day_mth varchar2, --in DD-MON fmt
155 p_start_dt date default sysdate
156 ) return date is
157 l_start_dt date;
158 l_end_dt date;
159 l_cot_day_mth varchar2(5); --in DD-MM fmt
160 begin
161 l_cot_day_mth := to_char(to_date(p_cot_day_mth,'DD-MON',
162 'NLS_DATE_LANGUAGE=American'),
163 'DD-MM');
164 l_start_dt := p_start_dt;
165 if l_start_dt is null then
166 l_start_dt := sysdate;
167 end if;
168 l_start_dt := trunc(l_start_dt);
169 --
170 l_end_dt := to_date(l_cot_day_mth||'-'||
171 to_char(l_start_dt,'RR'),'DD-MM-RR');
172 if l_end_dt < l_start_dt then
173 l_end_dt := add_months(l_end_dt,12);
174 end if;
175 --
176 return(l_end_dt);
177 end get_coterminated_end_dt;
178 */
179
180 --
181 --
182 --Get the phone# for the contact after determining whether the contact is
183 --a contact for a cust, or a contact for a cust for an addr.
184 -- -----------------------------------------------------------------------------
185 -- Modification History
186 -- Date Name Desc
187 -- -------- -------- -----------------------------------------------------------
188 -- 08/08/05 smisra bug 4532643
189 -- The function is being stubbed as it is not used
190 -- -----------------------------------------------------------------------------
191 function get_contact_phone
192 (
193 p_contact_id number
194 ) return varchar2 is
195 l_customer_id number;
196 l_address_id number;
197 l_phone_no varchar2(80);
198 --
199 l_sql_count number;
200 begin
201 /******
202 if p_contact_id is null then
203 return null;
204 end if;
205 --
206 l_sql_count := 1;
207 select customer_id,address_id
208 into l_customer_id,l_address_id
209 from ra_contacts
210 where contact_id = p_contact_id;
211 --
212 l_sql_count := 2;
213 if l_address_id is null then --contact for a cust.
214 select decode(area_code,null,null,'('||area_code||')')||
215 phone_number||
216 decode(extension,null,null,' x'||extension)
217 into l_phone_no
218 from ra_phones
219 where contact_id = p_contact_id
220 and customer_id = l_customer_id
221 and address_id is null
222 and primary_flag = 'Y'
223 and status = 'A';
224 else --contact for a cust for an address.
225 select decode(area_code,null,null,'('||area_code||')')||
226 phone_number||
227 decode(extension,null,null,' x'||extension)
228 into l_phone_no
229 from ra_phones
230 where contact_id = p_contact_id
231 and customer_id = l_customer_id
232 and address_id = l_address_id
233 and primary_flag = 'Y'
234 and status = 'A';
235 end if;
236 *************************/
237 --
238 return l_phone_no;
239 --
240 exception
241 when NO_DATA_FOUND then
242 if l_sql_count = 1 then
243 raise;
244 else
245 return null;
246 end if;
247 end get_contact_phone;
248 --
249 --
250 --Get the item's revision description.
251 --If p_error_flag is 'FALSE', then a NULL is returned if the revision is
252 --invalid. If p_error_flag is 'TRUE', the NO_DATA_FOUND exception is raised
253 --if the revision is invalid, and it is upto the calling code to handle it.
254 function get_item_rev_desc
255 (
256 p_org_id number,
257 p_inv_item_id number,
258 p_revision varchar2,
259 p_error_flag varchar2 default 'TRUE'
260 ) return varchar2 is
261 l_rev_desc mtl_item_revisions.description%type;
262 begin
263 if p_org_id is null or
264 p_inv_item_id is null or
265 p_revision is null then
266 return(null);
267 end if;
268 --
269 select description
270 into l_rev_desc
271 from mtl_item_revisions
272 where organization_id = p_org_id
273 and inventory_item_id = p_inv_item_id
274 and revision = p_revision;
275 --
276 return(l_rev_desc);
277 --
278 exception
279 when NO_DATA_FOUND then
280 if p_error_flag = 'TRUE' then
281 raise;
282 else
283 return(null);
284 end if;
285 end get_item_rev_desc;
286 --
287 --
288 --Get the site_use_id's location.
289 --If p_error_flag is 'FALSE', then a NULL is returned if the site_use is
290 --invalid. If p_error_flag is 'TRUE', the NO_DATA_FOUND exception is raised
291 --if the site_use is invalid, and it is upto the calling code to handle it.
292 -- -----------------------------------------------------------------------------
293 -- Modification History
294 -- Date Name Desc
295 -- -------- -------- -----------------------------------------------------------
296 -- 08/08/05 smisra bug 4532643
297 -- The function is being stubbed as it is not used
298 -- -----------------------------------------------------------------------------
299 function get_site_use_location
300 (
301 p_site_use_id number,
302 p_error_flag varchar2 default 'TRUE'
303 ) return varchar2 is
304 -- l_location ra_site_uses.location%type;
305 begin
306 /**************
307 if p_site_use_id is null then
308 raise NO_DATA_FOUND;
309 end if;
310 --
311 select location
312 into l_location
313 from ra_site_uses
314 where site_use_id = p_site_use_id;
315 --
316 return(l_location);
317 --
318 ********/
319 return null;
320 exception
321 when NO_DATA_FOUND then
322 if p_error_flag = 'TRUE' then
323 raise;
324 else
325 return(null);
326 end if;
327 end get_site_use_location;
328 --
329 --
330 --Get the customer name.
331 --If p_error_flag is 'FALSE', then a NULL is returned if the PK is
332 --invalid. If p_error_flag is 'TRUE', the NO_DATA_FOUND exception is raised
333 --if the PK is invalid, and it is upto the calling code to handle it.
334 -- 12/08/05 smisra replaced ra_customers with hz_parties
335 function get_cust_name
336 (
337 p_customer_id number,
338 p_error_flag varchar2 default 'TRUE'
339 ) return varchar2 is
340 l_cust_name hz_parties.party_name%type;
341 begin
342 if p_customer_id is null then
343 raise NO_DATA_FOUND;
344 end if;
345 --
346 select party_name
347 into l_cust_name
348 from hz_parties
349 where party_id = p_customer_id;
350 --
351 return(l_cust_name);
352 --
353 exception
354 when NO_DATA_FOUND then
355 if p_error_flag = 'TRUE' then
356 raise;
357 else
358 return(null);
359 end if;
360 end get_cust_name;
361
362
363 function warranty_exists(cp_id NUMBER)
364 return VARCHAR2 is
365 return_val VARCHAR2(1) ;
366 begin
367
368 select 'Y' INTO return_val
369 from cs_cp_services CSS
370 where css.customer_product_id = cp_id
371 and css.warranty_flag='Y' ;
372 return(return_val);
373
374
375 exception
376
377 WHEN NO_DATA_FOUND THEN
378 return_val := 'N' ;
379 return(return_val);
380
381 WHEN TOO_MANY_ROWS THEN
385
382 return_val := 'Y' ;
383 return(return_val);
384
386 end warranty_exists ;
387
388 --
389 --
390 --Note: Used by CSOEBAT and CSXSUDCP form as of 1/29/97.
391 function get_war_item_ids
392 (
393 p_organization_id number,
394 p_inventory_item_id number,
395 p_war_date date default sysdate
396 ) return varchar2 is
397 l_war_date date;
398 l_last_delim_pos number;
399 l_war_item_ids varchar2(2000);
400 l_com_bill_seq_id number;
401 --
402 cursor c_war_items(c_bill_seq_id number) is
403 select bic.component_item_id war_item_id
404 from bom_inventory_components bic
405 where bic.bill_sequence_id = c_bill_seq_id
406 --
407 --Fix to bug#479703. vharihar 4/16/97.
408 -- and trunc(l_war_date) between trunc(bic.effectivity_date) and
409 -- trunc(nvl(bic.disable_date,l_war_date))
410 and l_war_date >= bic.effectivity_date
411 and l_war_date < nvl(bic.disable_date,l_war_date+1)
412 --
413 and exists
414 (
415 select 'Component is a Warranty'
416 from mtl_system_items mtl
417 where mtl.organization_id = p_organization_id
418 and mtl.inventory_item_id = bic.component_item_id
419 and mtl.vendor_warranty_flag = 'Y'
420 )
421 order by bic.component_item_id;
422 --
423 begin
424 if p_organization_id is null or
425 p_inventory_item_id is null then
426 raise NO_DATA_FOUND;
427 end if;
428 --
429 l_war_date := nvl(p_war_date,sysdate);
430 --
431 select common_bill_sequence_id
432 into l_com_bill_seq_id
433 from bom_bill_of_materials
434 where organization_id = p_organization_id
435 and assembly_item_id = p_inventory_item_id
436 and alternate_bom_designator is null;
437 --
438 l_war_item_ids := null;
439 for c_war_items_rec in c_war_items(l_com_bill_seq_id) loop
440 l_war_item_ids := l_war_item_ids ||
441 to_char(c_war_items_rec.war_item_id)||',';
442 end loop;
443 --
444 if l_war_item_ids is null then
445 raise NO_DATA_FOUND;
446 end if;
447 --
448 --Strip off the last comma. Intentionally used instr() and
449 --substr() here, and not instrb() and substrb(), as these
450 --will suffice, and wont cause any harm in this case.
451 l_last_delim_pos := instr(l_war_item_ids,',',-1);
452 l_war_item_ids := substr(l_war_item_ids,1,l_last_delim_pos-1);
453 --
454 return(l_war_item_ids);
455 --
456 exception
457 when NO_DATA_FOUND then
458 return(null);
459 end get_war_item_ids;
460 --
461 --
462 --Get the system's name.
463 --If p_error_flag is 'FALSE', then a NULL is returned if the PK is
464 --invalid. If p_error_flag is 'TRUE', the NO_DATA_FOUND exception is raised
465 --if the PK is invalid, and it is upto the calling code to handle it.
466 function get_system_name
467 (
468 p_system_id number,
469 p_error_flag varchar2 default 'TRUE'
470 ) return varchar2 is
471 l_system_name cs_systems.name%type;
472 begin
473 if p_system_id is null then
474 raise NO_DATA_FOUND;
475 end if;
476 --
477 select name
478 into l_system_name
479 from cs_systems
480 where system_id = p_system_id;
481 --
482 return(l_system_name);
483 --
484 exception
485 when NO_DATA_FOUND then
486 if p_error_flag = 'TRUE' then
487 raise;
488 else
489 return(null);
490 end if;
491 end get_system_name;
492 --
493 --
494 --
495
496 -- -----------------------------------------------------------------------------
497 -- Modification History
498 -- Date Name Desc
499 -- -------- -------- -----------------------------------------------------------
500 -- 08/08/05 smisra bug 4532643
501 -- The procedure is being stubbed as it is not used
502 -- -----------------------------------------------------------------------------
503 procedure Get_Primary_Address(x_id NUMBER,
504 x_site_use_code VARCHAR2,
505 x_location OUT NOCOPY VARCHAR2,
506 x_site_use_id OUT NOCOPY NUMBER,
507 x_address1 OUT NOCOPY VARCHAR2,
508 x_address2 OUT NOCOPY VARCHAR2,
509 x_address3 OUT NOCOPY VARCHAR2,
510 error_flag OUT NOCOPY NUMBER) IS
511
512 begin
513 null;
514
515 /*******
516 select su1.location,
517 su1.site_use_id,
518 addr1.address1 || DECODE(ADDR1.ADDRESS1,'','',
519 DECODE(ADDR1.ADDRESS2,'','',', ')) ||
520 ADDR1.address2 ,
521 addr1.address3 || DECODE(addr1.address3,'','',
522 DECODE(addr1.address4,'','',', ')) ||
523 ADDR1.address4 ,
524 SUBSTR(ADDR1.CITY || DECODE(ADDR1.CITY,'','',
525 DECODE(ADDR1.STATE || ADDR1.COUNTRY || ADDR1.POSTAL_CODE,'','',
526 ', ')) || ADDR1.STATE || ' ' ||
527 ADDR1.POSTAL_CODE || ' ' || ADDR1.COUNTRY,1,220)
528
529 INTO x_location,
530 x_site_use_id,
531 x_address1,
532 x_address2,
533 x_address3
534
538 AND addr1.address_id = su1.address_id
535 FROM RA_SITE_USES su1,
536 RA_ADDRESSES ADDR1
537 WHERE addr1.customer_id = x_id
539 AND su1.primary_flag = 'Y'
540 AND su1.status = 'A'
541 AND su1.site_use_code = x_site_use_code ;
542
543
544 EXCEPTION
545 WHEN NO_DATA_FOUND THEN
546 error_flag := 0 ;
547
548 *******/
549
550 end Get_Primary_Address ;
551
552 --
553 -- CS_GET_SERVICED_STATUS
554 --
555 FUNCTION CS_Get_Serviced_Status
556 ( X_CP_ID IN NUMBER
557 ) RETURN VARCHAR2 IS
558 CURSOR serv_cur IS
559 SELECT trunc(nvl(serv.start_date_active, sysdate)) start_date_active,
560 trunc(nvl(serv.end_date_active, sysdate)) end_date_active
561 FROM cs_cp_services serv
562 WHERE serv.customer_product_id = X_CP_ID;
563
564 X_Sysdate DATE := TRUNC(sysdate);
565 X_Start_Date DATE;
566 X_End_Date DATE;
567 ret_val VARCHAR2(1) := 'N';
568 future BOOLEAN := FALSE;
569
570 BEGIN
571
572 OPEN serv_cur;
573
574 LOOP
575 FETCH serv_cur INTO X_Start_Date, X_End_Date;
576 EXIT WHEN serv_cur%NOTFOUND;
577
578 if (X_Sysdate between X_Start_Date and X_End_Date) then
579 ret_val := 'Y';
580 EXIT;
581 elsif (X_Sysdate < X_Start_Date) then
582 future := TRUE;
583 end if;
584 END LOOP;
585
586 if (ret_val <> 'Y') then
587 if (future = TRUE) then
588 ret_val := 'F';
589 end if;
590 end if;
591
592 CLOSE serv_cur;
593 return ret_val;
594
595 END CS_Get_Serviced_Status;
596
597 /********
598 procedure Output_Messages( p_return_status VARCHAR2,
599 p_msg_count NUMBER) IS
600 l_message VARCHAR2(2000);
601 BEGIN
602 IF (p_return_status = FND_API.G_RET_STS_SUCCESS) THEN
603 DBMS_OUTPUT.PUT_LINE('Result: Successful');
604 ELSIF (p_return_status = FND_API.G_RET_STS_ERROR) THEN
605 DBMS_OUTPUT.PUT_LINE('Result: Error');
606 ELSIF (p_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
607 DBMS_OUTPUT.PUT_LINE('Result: Unexpected Error');
608 ELSE
609 DBMS_OUTPUT.PUT_LINE('Result: Fatal Error - unrecognized return status code');
610 END IF;
611
612 IF (p_msg_count > 0) THEN
613
614 FOR counter IN REVERSE 1..p_msg_count LOOP
615
616 l_message := fnd_msg_pub.get(counter, FND_API.G_FALSE);
617 DBMS_OUTPUT.PUT_LINE(' MSG('||to_char(counter)||'): '||
618 l_message);
619
620 END LOOP;
621
622 fnd_msg_pub.delete_msg ;
623
624 END IF;
625
626
627 END Output_Messages;
628 ******/
629 -- -----------------------------------------------------------------------------
630 -- Modification History
631 -- Date Name Desc
632 -- -------- -------- -----------------------------------------------------------
633 -- 08/08/05 smisra bug 4532643
634 -- The procedure is being stubbed as it is not used
635 -- -----------------------------------------------------------------------------
636 procedure Get_Address_from_id(x_id NUMBER,
637 x_location OUT NOCOPY VARCHAR2,
638 x_address1 OUT NOCOPY VARCHAR2,
639 x_address2 OUT NOCOPY VARCHAR2,
640 x_address3 OUT NOCOPY VARCHAR2,
641 error_flag OUT NOCOPY NUMBER) IS
642
643 begin
644 null;
645 /*************************
646
647 select su1.location,
648 addr1.address1 || DECODE(ADDR1.ADDRESS1,'','',
649 DECODE(ADDR1.ADDRESS2,'','',', ')) ||
650 ADDR1.address2 ,
651 addr1.address3 || DECODE(addr1.address3,'','',
652 DECODE(addr1.address4,'','',', ')) ||
653 ADDR1.address4 ,
654 SUBSTR(ADDR1.CITY || DECODE(ADDR1.CITY,'','',
655 DECODE(ADDR1.STATE || ADDR1.COUNTRY || ADDR1.POSTAL_CODE,'','',
656 ', ')) || ADDR1.STATE || ' ' ||
657 ADDR1.POSTAL_CODE || ' ' || ADDR1.COUNTRY,1,220)
658
659 INTO x_location,
660 x_address1,
661 x_address2,
662 x_address3
663
664 FROM RA_SITE_USES su1,
665 RA_ADDRESSES ADDR1
666 WHERE su1.site_use_id = x_id
667 AND addr1.address_id = su1.address_id ;
668
669
670 EXCEPTION
671 WHEN NO_DATA_FOUND THEN
672 error_flag := 0 ;
673 *******/
674
675 END GET_ADDRESS_FROM_ID ;
676
677 -- This function returns the next entry in a periodic cycle.
678 -- As of 9/1/98, only 12 mth periods are supported. Thus, on subsequent
679 -- invocations, this functions currently returns 1, 2, ... 12, 1, 2, ...
680 -- p_reset = 1 will reset cycle.
681 function GetNextValInPeriod
682 (
683 p_reset NUMBER
684 ) return number is
685 Var1 number;
686 begin
687 if CurVal >= 12 then
688 CurVal := 1;
692 Var1 := CurVal;
689 return (1);
690 end if;
691 CurVal := CurVal + 1;
693 if p_reset = 1 then
694 CurVal := 0;
695 end if;
696 return (Var1);
697 end GetNextValInPeriod;
698 --
699 --
700 -- This function returns the item category of an item in the OE
701 -- category set.
702 function GetItemCategory
703 (
704 p_inv_item_id number,
705 p_inv_orgn_id number
706 ) return varchar2 is
707 l_cat varchar2(50);
708 begin
709 if p_inv_item_id is null or
710 p_inv_orgn_id is null then
711 return (null); /*****/
712 end if;
713 --
714 select mc.description
715 into l_cat
716 from
717 mtl_categories mc,
718 mtl_default_category_sets mdc,
719 mtl_item_categories mic
720 where mic.inventory_item_id = p_inv_item_id
721 and mic.organization_id = p_inv_orgn_id
722 and mdc.functional_area_id+0 = 7
723 and mic.category_set_id = mdc.category_set_id
724 and mc.category_id = mic.category_id;
725 --
726 return(l_cat); /*****/
727 --
728 exception
729 when NO_DATA_FOUND then
730 return(null); /*****/
731 end GetItemCategory;
732 --
733 --
734 --
735 --
736 --package initialization part
737
738 -- -----------------------------------------------------------------------------
739 -- Modification History
740 -- Date Name Desc
741 -- -------- -------- -----------------------------------------------------------
742 -- 08/08/05 smisra bug 4532643
743 -- The function is being stubbed as it is not used
744 -- -----------------------------------------------------------------------------
745 FUNCTION SITE_USE_ADDRESS(site_id IN NUMBER) RETURN VARCHAR2 IS
746 address VARCHAR2(2000);
747 temp_address1 VARCHAR2(240);
748 temp_address2 VARCHAR2(240);
749 temp_address3 VARCHAR2(240);
750 temp_address4 VARCHAR2(240);
751 temp_city VARCHAR2(60);
752 temp_state VARCHAR2(60);
753 temp_code VARCHAR2(60);
754 temp_country VARCHAR2(60);
755
756 BEGIN
757 return(null);
758 /*****
759 SELECT address1, address2, address3, address4, city, state, postal_code, country
760 INTO temp_address1, temp_address2, temp_address3, temp_address4, temp_city,
761 temp_state, temp_code, temp_country
762 FROM CS_RA_ADDR_LOC_RG_V
763 WHERE site_use_id = site_id;
764
765 address := temp_address1;
766
767 IF temp_address2 IS NOT NULL THEN
768 address := address||', '||temp_address2;
769 END IF;
770
771 IF temp_address3 IS NOT NULL THEN
772 address := address||', '||temp_address3;
773 END IF;
774
775 IF temp_address4 IS NOT NULL THEN
776 address := address||', '||temp_address4;
777 END IF;
778
779 IF temp_city IS NOT NULL THEN
780 address := address||', '||temp_city;
781 END IF;
782
783 IF temp_state IS NOT NULL THEN
784 address := address||', '||temp_state;
785 END IF;
786
787 IF temp_code IS NOT NULL THEN
788 address := address||' '||temp_code;
789 END IF;
790
791 IF temp_country IS NOT NULL THEN
792 address := address||' '||temp_country;
793 END IF;
794
795 RETURN address;
796 ****/
797
798 END Site_Use_Address;
799
800
801 -- This function returns the "inventory organization" ID (or whats also
802 -- called the "warehouse" ID that the Service suite of products should
803 -- use for validating items
804
805 function Get_Item_Valdn_Orgzn_ID return number is
806 Orgzn_ID varchar2(250);
807 begin
808 fnd_profile.get('CS_INV_VALIDATION_ORG',Orgzn_ID);
809 return(to_number(Orgzn_ID));
810 -- return(to_number(fnd_profile.value_wnps('ASO_PRODUCT_ORGANIZATION_ID')));
811 end;
812
813 /* The Following Functions will take address fields and Format them based on
814 HZ_FORMAT_PUB. In case of error, it returns a simple concatenation of fields.*/
815
816 FUNCTION format_address_concat( address_style IN VARCHAR2,
817 address1 IN VARCHAR2,
818 address2 IN VARCHAR2,
819 address3 IN VARCHAR2,
820 address4 IN VARCHAR2,
821 city IN VARCHAR2,
822 county IN VARCHAR2,
823 state IN VARCHAR2,
824 province IN VARCHAR2,
825 postal_code IN VARCHAR2,
826 territory_short_name IN VARCHAR2
827 )return VARCHAR2 IS
828 l_address varchar2(1000);
829 BEGIN
830 l_address := address1;
831
832 IF ( address2 IS NOT NULL ) THEN
833 l_address := l_address || ', ' || address2;
834 END IF;
835
836 IF ( address3 IS NOT NULL ) THEN
837 l_address := l_address || ', ' || address3;
838 END IF;
839
840 IF ( address4 IS NOT NULL ) THEN
841 l_address := l_address || ', ' || address4;
842 END IF;
843
844 IF ( city IS NOT NULL ) THEN
845 l_address := l_address || ', ' || city;
846 END IF;
847
848 IF ( county IS NOT NULL ) THEN
849 l_address := l_address || ', ' || county;
850 END IF;
851
852 IF ( state IS NOT NULL ) THEN
853 l_address := l_address || ', ' || state;
854 END IF;
855
856 IF ( province IS NOT NULL ) THEN
857 l_address := l_address || ', ' || province;
858 END IF;
859
860 IF ( postal_code IS NOT NULL ) THEN
861 l_address := l_address || ', ' || postal_code;
862 END IF;
863
864 IF ( territory_short_name IS NOT NULL ) THEN
865 l_address := l_address || ', ' || territory_short_name;
866 END IF;
867
868 RETURN( l_address );
869 END format_address_concat;
870
871
872 FUNCTION format_address( address_style IN VARCHAR2,
873 address1 IN VARCHAR2,
874 address2 IN VARCHAR2,
875 address3 IN VARCHAR2,
876 address4 IN VARCHAR2,
877 city IN VARCHAR2,
878 county IN VARCHAR2,
879 state IN VARCHAR2,
880 province IN VARCHAR2,
881 postal_code IN VARCHAR2,
882 territory_short_name IN VARCHAR2,
883 country_code IN VARCHAR2 default NULL,
884 customer_name IN VARCHAR2 default NULL,
885 first_name IN VARCHAR2 default NULL,
886 last_name IN VARCHAR2 default NULL,
887 mail_stop IN VARCHAR2 default NULL,
888 default_country_code IN VARCHAR2 default NULL,
889 default_country_desc IN VARCHAR2 default NULL,
890 print_home_country_flag IN VARCHAR2 default NULL,
891 print_default_attn_flag IN VARCHAR2 default NULL,
892 width IN NUMBER default NULL,
893 height_min IN NUMBER default NULL,
894 height_max IN NUMBER default NULL
898 l_formatted_lines_cnt NUMBER;
895 )return VARCHAR2 IS
896
897 l_formatted_address VARCHAR2(2000);
899 l_formatted_address_tbl hz_format_pub.string_tbl_type;
900 l_return_status VARCHAR2(1);
901 l_msg_count NUMBER;
902 l_msg_data VARCHAR2(2000);
903
904 BEGIN
905
906 hz_format_pub.format_address (
907 p_line_break => ', ',
908 p_from_territory_code => 'x', -- force country short name be displayed
909 p_address_line_1 => address1,
910 p_address_line_2 => address2,
911 p_address_line_3 => address3,
912 p_address_line_4 => address4,
913 p_city => city,
914 p_postal_code => postal_code,
915 p_state => state,
916 p_province => province,
917 p_county => county,
918 p_country => country_code,
919 -- output parameters
920 x_return_status => l_return_status,
921 x_msg_count => l_msg_count,
922 x_msg_data => l_msg_data,
923 x_formatted_address => l_formatted_address,
924 x_formatted_lines_cnt => l_formatted_lines_cnt,
925 x_formatted_address_tbl => l_formatted_address_tbl
926 );
927 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
928 return l_formatted_address;
929 ELSE
930 return(format_address_concat( address_style,
931 address1,
932 address2,
933 address3,
934 address4,
935 city,
936 county,
937 state,
938 province,
939 postal_code,
940 territory_short_name ) );
941 END IF;
942 END format_address;
943
944
945 END CS_STD;
[Home] [Help]