1 PACKAGE BODY IEC_DEFAULT_SUBSET_PVT AS
2 /* $Header: IECADSBB.pls 115.8 2004/05/18 19:38:06 minwang noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'IEC_DEFAULT_SUBSET_PVT';
5
6 G_DEFAULT_SUBSET_NAME CONSTANT VARCHAR2(30) := 'IEC_DEFAULT_SUBSET_NAME';
7
8
9 -----------------------------++++++-------------------------------
10 --
11 -- Start of comments
12 --
13 -- API name : CREATE_DEFAULT_SUBSETS
14 -- Type : Public
15 -- Pre-reqs : None
16 -- Function : if a default subset has not previously been
17 -- created on the list the create one.
18
19 -- Parameters :
20 --
21 -- Version : Initial version 1.0
22 --
23 -- End of comments
24 --
25 -----------------------------++++++-------------------------------
26 /* Called by the Status Plugin. */
27 PROCEDURE CREATE_DEFAULT_SUBSETS( P_LIST_ID IN NUMBER
28 , X_RETURN_STATUS OUT NOCOPY VARCHAR2)
29 IS
30 l_user_id NUMBER;
31 l_login_id NUMBER;
32 l_log_status VARCHAR2(1);
33 l_method_name CONSTANT VARCHAR2(30) := 'CREATE_DEFAULT_SUBSETS';
34 l_default_subset_name VARCHAR2(255);
35 l_subset_name VARCHAR2(255);
36 l_index NUMBER;
37
38 BEGIN
39 l_user_id := NVL(FND_GLOBAL.USER_ID, -1);
40 l_login_id := NVL(FND_GLOBAL.CONC_LOGIN_ID, -1);
41
42 SAVEPOINT CREATE_DEFAULT_SUBSET_START;
43 X_RETURN_STATUS := 'S';
44
45 ----------------------------------------------------------------
46 -- We use the lookup to get the default subset name.
47 ----------------------------------------------------------------
48 BEGIN
49 SELECT MEANING
50 INTO l_default_subset_name
51 FROM IEC_LOOKUPS
52 WHERE LOOKUP_TYPE = G_DEFAULT_SUBSET_NAME
53 AND LOOKUP_CODE = G_DEFAULT_SUBSET_NAME;
54
55 EXCEPTION
56 ----------------------------------------------------------------
57 -- We probably should log the fact that we weren't able to get
58 -- the default subset names for the lookups table. Currently
59 -- we just set the name to a default. FUTURE.
60 ----------------------------------------------------------------
61 WHEN NO_DATA_FOUND THEN
62 l_default_subset_name := 'DEFAULT SUBSET';
63
64 ----------------------------------------------------------------
65 -- We need to create a log message indicating that an internal
66 -- unexpected PLSQL error occurred, give the sub_method, method
67 -- package, SQLCODE, and SQLERRM FUTURE. For now we are just
68 -- re-raising the original exception.
69 ----------------------------------------------------------------
70 WHEN OTHERS THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END;
73
74 BEGIN
75
76 SELECT SUBSET_NAME
77 INTO L_SUBSET_NAME
78 FROM IEC_G_LIST_SUBSETS
79 WHERE LIST_HEADER_ID = P_LIST_ID
80 AND DEFAULT_SUBSET_FLAG = 'Y';
81
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 BEGIN
85
86 INSERT INTO IEC_G_LIST_SUBSETS
87 ( LIST_SUBSET_ID
88 , LIST_HEADER_ID
89 , SUBSET_NAME
90 , QUANTUM
91 , PRIORITY
92 , STATUS_CODE
93 , DEFAULT_SUBSET_FLAG
94 , QUOTA
95 , QUOTA_RESET
96 , RELEASE_STRATEGY
97 , CREATED_BY
98 , CREATION_DATE
99 , LAST_UPDATED_BY
100 , LAST_UPDATE_DATE
101 , OBJECT_VERSION_NUMBER
102 )
103 VALUES
104 ( IEC_G_LIST_SUBSETS_S.NEXTVAL
105 , P_LIST_ID
106 , l_default_subset_name
107 , 10
108 , 1
109 , 'ACTIVE'
110 , 'Y'
111 , 0
112 , 0
113 , 'QUA'
114 , l_user_id
115 , SYSDATE
116 , l_login_id
117 , SYSDATE
118 , 1
119 );
120
121 EXCEPTION
122 WHEN DUP_VAL_ON_INDEX THEN
123 NULL;
124 WHEN OTHERS THEN
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 END; -- END INSERT BLOCK
127
128 WHEN OTHERS THEN
129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
130 END; -- END SELECT BLOCK
131
132 COMMIT;
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 ROLLBACK TO CREATE_DEFAULT_SUBSET_START;
137 X_RETURN_STATUS := 'U';
138 END CREATE_DEFAULT_SUBSETS;
139
140
141
142 -- PL/SQL Block
143 END IEC_DEFAULT_SUBSET_PVT;