PACKAGE BODY: APPS.ICX_CAT_STORE_ASSIGNMENT_PKG

Source

1 PACKAGE BODY ICX_CAT_STORE_ASSIGNMENT_PKG AS
2 /* $Header: ICXSTAGB.pls 120.3 2006/03/20 20:49:35 msheikh noship $*/
3 
4 FUNCTION GET_ASSIGNED_STORE_ID(p_contentType IN VARCHAR2,
5                                p_contentId IN NUMBER)
6 RETURN NUMBER IS
7   xErrLoc         INTEGER := 0;
8   xCount          INTEGER := 0;
9   xStoreId        ICX_CAT_SHOP_STORES_B.STORE_ID%TYPE;
10 
11   CURSOR store_assignments(p_contentType IN VARCHAR2,
12                            p_contentId IN NUMBER) IS
13     SELECT
14       DISTINCT stores.store_id
15     FROM
16       icx_cat_store_contents contents,
17       icx_Cat_shop_stores_b stores
18     WHERE
19       contents.content_type = p_contentType AND
20       contents.content_id = p_contentId  AND
21       contents.store_id = stores.store_id;
22 BEGIN
23 
24   xErrLoc := 100;
25 
26   FOR stores IN store_assignments(p_contentType, p_contentId) LOOP
27     xErrLoc := xErrLoc + 100;
28     xCount := xCount + 1;
29     xStoreId := stores.store_id;
30   END LOOP;
31 
32   IF (xCount = 0) THEN
33     RETURN  0;
34   ELSIF (xCount = 1) THEN
35     RETURN  xStoreId;
36   ELSE
37     RETURN -1;
38   END IF;
39 
40 
41 END GET_ASSIGNED_STORE_ID;
42 
43 
44 FUNCTION GET_STORE_ASSIGNMENT(p_contentType IN VARCHAR2,
45                               p_contentId IN NUMBER)
46 RETURN VARCHAR2 IS
47   xErrLoc         INTEGER := 0;
48   xCount          INTEGER := 0;
49   xStoreName      ICX_CAT_SHOP_STORES_TL.NAME%TYPE;
50   xMultipleMsg    FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
51 
52   CURSOR store_assignments(p_contentType IN VARCHAR2,
53                            p_contentId IN NUMBER) IS
54     SELECT
55       DISTINCT name
56     FROM
57       icx_cat_store_contents contents,
58       icx_Cat_shop_stores_vl stores
59     WHERE
60       contents.content_type = p_contentType AND
61       contents.content_id = p_contentId  AND
62       contents.store_id = stores.store_id;
63 BEGIN
64 
65   xErrLoc := 100;
66 
67   FOR stores IN store_assignments(p_contentType, p_contentId) LOOP
68     xErrLoc := xErrLoc + 100;
69     xCount := xCount + 1;
70     xStoreName := stores.name;
71   END LOOP;
72 
73   IF (xCount = 0) THEN
74     RETURN  NULL;
75   ELSIF (xCount = 1) THEN
76     RETURN  xStoreName;
77   ELSE
78     SELECT message_text
79     INTO   xMultipleMsg
80     FROM   fnd_new_messages
81     WHERE  message_name = 'ICX_POR_MULTIPLE'
82        AND language_code = USERENV('LANG');
83 
84     RETURN xMultipleMsg;
85   END IF;
86 
87 
88 END GET_STORE_ASSIGNMENT;
89 
90 END ICX_CAT_STORE_ASSIGNMENT_PKG;