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