[Home] [Help]
PACKAGE BODY: APPS.AMS_CUSTOM_SETUP_PURPOSE_PKG
Source
1 PACKAGE BODY AMS_Custom_Setup_Purpose_PKG as
2 /* $Header: amslcspb.pls 115.3 2004/04/08 22:51:45 asaha noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Custom_Setup_Purpose_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Custom_Setup_Purpose_PKG';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amslcspb.pls';
15
16 -- ========================================================
17 --
18 -- NAME
19 -- Insert_Row
20 --
21 -- PURPOSE
22 --
23 -- NOTES
24 --
25 -- HISTORY
26 --
27 -- ========================================================
28 PROCEDURE Insert_Row(
29 px_setup_purpose_id IN OUT NOCOPY NUMBER,
30 p_custom_setup_id NUMBER,
31 p_last_update_date DATE,
32 p_last_updated_by NUMBER,
33 p_creation_date DATE,
34 p_created_by NUMBER,
35 p_last_update_login NUMBER,
36 p_activity_purpose_code VARCHAR2,
37 p_enabled_flag VARCHAR2,
38 p_def_list_template_id NUMBER,
39 px_object_version_number IN OUT NOCOPY NUMBER)
40
41 IS
42 x_rowid VARCHAR2(30);
43
44 BEGIN
45 px_object_version_number := nvl(px_object_version_number, 1);
46
47 INSERT INTO ams_custom_setup_purpose(
48 setup_purpose_id,
49 custom_setup_id,
50 last_update_date,
51 last_updated_by,
52 creation_date,
53 created_by,
54 last_update_login,
55 activity_purpose_code,
56 enabled_flag,
57 def_list_template_id,
58 object_version_number
59 ) VALUES (
60 DECODE( px_setup_purpose_id, FND_API.G_MISS_NUM, NULL, px_setup_purpose_id),
61 DECODE( p_custom_setup_id, FND_API.G_MISS_NUM, NULL, p_custom_setup_id),
62 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
63 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
64 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
65 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
66 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
67 DECODE( p_activity_purpose_code, FND_API.g_miss_char, NULL, p_activity_purpose_code),
68 DECODE( p_enabled_flag, FND_API.g_miss_char, NULL, p_enabled_flag),
69 DECODE( p_def_list_template_id, FND_API.G_MISS_NUM, NULL, p_def_list_template_id),
70 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number));
71
72 END Insert_Row;
73
74 -- ========================================================
75 --
76 -- NAME
77 -- Update_Row
78 --
79 -- PURPOSE
80 --
81 -- NOTES
82 --
83 -- HISTORY
84 --
85 -- ========================================================
86 PROCEDURE Update_Row(
87 p_setup_purpose_id NUMBER,
88 p_custom_setup_id NUMBER,
89 p_last_update_date DATE,
90 p_last_updated_by NUMBER,
91 p_last_update_login NUMBER,
92 p_activity_purpose_code VARCHAR2,
93 p_enabled_flag VARCHAR2,
94 p_def_list_template_id NUMBER,
95 p_object_version_number IN NUMBER)
96
97 IS
98 BEGIN
99 Update ams_custom_setup_purpose
100 SET
101 setup_purpose_id = DECODE( p_setup_purpose_id, null, setup_purpose_id, FND_API.G_MISS_NUM, null, p_setup_purpose_id),
102 custom_setup_id = DECODE( p_custom_setup_id, null, custom_setup_id, FND_API.G_MISS_NUM, null, p_custom_setup_id),
103 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
104 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
105 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
106 activity_purpose_code = DECODE( p_activity_purpose_code, null, activity_purpose_code, FND_API.g_miss_char, null, p_activity_purpose_code),
107 enabled_flag = DECODE( p_enabled_flag, null, enabled_flag, FND_API.g_miss_char, null, p_enabled_flag),
108 def_list_template_id = DECODE( p_def_list_template_id, null, def_list_template_id, FND_API.G_MISS_NUM, null, p_def_list_template_id),
109 object_version_number = nvl(p_object_version_number,0) + 1
110 WHERE setup_purpose_id = p_setup_purpose_id
111 AND object_version_number = p_object_version_number;
112
113
114 IF (SQL%NOTFOUND) THEN
115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116 END IF;
117
118
119 END Update_Row;
120
121 -- ========================================================
122 --
123 -- NAME
124 -- Delete_Row
125 --
126 -- PURPOSE
127 --
128 -- NOTES
129 --
130 -- HISTORY
131 --
132 -- ========================================================
133 PROCEDURE Delete_Row(
134 p_setup_purpose_id NUMBER,
135 p_object_version_number NUMBER)
136 IS
137 BEGIN
138 DELETE FROM ams_custom_setup_purpose
139 WHERE setup_purpose_id = p_setup_purpose_id
140 AND object_version_number = p_object_version_number;
141 If (SQL%NOTFOUND) then
142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
143 End If;
144 END Delete_Row ;
145
146 -- ========================================================
147 --
148 -- NAME
149 -- Lock_Row
150 --
151 -- PURPOSE
152 --
153 -- NOTES
154 --
155 -- HISTORY
156 --
157 -- ========================================================
158 PROCEDURE Lock_Row(
159 p_setup_purpose_id NUMBER,
160 p_object_version_number NUMBER)
161 IS
162 CURSOR C IS
163 SELECT *
164 FROM ams_custom_setup_purpose
165 WHERE setup_purpose_id = p_setup_purpose_id
166 AND object_version_number = p_object_version_number
167 FOR UPDATE OF setup_purpose_id NOWAIT;
168 Recinfo C%ROWTYPE;
169 BEGIN
170
171 OPEN c;
172 FETCH c INTO Recinfo;
173 IF (c%NOTFOUND) THEN
174 CLOSE c;
175 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
176 RAISE FND_API.g_exc_error;
177 END IF;
178 CLOSE c;
179 END Lock_Row;
180 -- ========================================================
181 /* This procedure is used to load the data from flat file to customer's database.
182 If there is no row existing for the data from flat file then create the data.
183 else
184 1) modify the whole data when data in db is not modified by customer which
185 can be found by comparing last updated by value to be
186 SEED/DATAMERGE(1), or
187 INITIAL SETUP/ORACLE (2), or
188 SYSTEM ADMINISTRATOR (0).or
189 2) modify the whole data when custom_mode is 'FORCE'
190 3) if the data in db is modified by customer, which can be found by
191 by comparing last updated by value to be not of 0,1,2, then
192 in that case modify only the user unexposed data with last updated by as
193 3 to distinguish that data is updated by patch.
194 */
195 PROCEDURE Load_Row(
196 p_setup_purpose_id NUMBER,
197 p_custom_setup_id NUMBER,
198 p_activity_purpose_code VARCHAR2,
199 p_enabled_flag VARCHAR2,
200 p_def_list_template_id NUMBER,
201 p_owner VARCHAR2,
202 p_custom_mode VARCHAR2,
203 X_LAST_UPDATE_DATE in DATE)
204
205 IS
206 l_user_id number := 1;
207 -- user id to be used in case of exceptions to update the customer
208 -- modified unexposed data.
209 l_excp_user_id number := 3 ;
210
211 l_obj_verno number;
212 l_dummy_number number;
213 l_row_id varchar2(100);
214 l_setup_purpose_id number;
215 l_db_luby_id NUMBER;
216
217 cursor c_db_data_details is
218 select last_updated_by, nvl(object_version_number,1)
219 from ams_custom_setup_purpose
220 where setup_purpose_id = p_setup_purpose_id;
221
222 cursor c_chk_csp_exists is
223 select 1
224 from ams_custom_setup_purpose
225 where setup_purpose_id = p_setup_purpose_id;
226
227 cursor c_get_cspid is
228 select ams_custom_setup_purpose_s.nextval
229 from dual;
230
231 BEGIN
232
233 -- set the last_updated_by to be used while updating the data in customer data.
234
235 if p_OWNER = 'SEED' then
236 l_user_id := 1;
237 elsif p_OWNER = 'ORACLE' THEN
238 l_user_id := 2;
239 elsif p_OWNER = 'SYSADMIN' THEN
240 l_user_id := 0;
241 end if ;
242
243 open c_chk_csp_exists;
244 fetch c_chk_csp_exists into l_dummy_number;
245 if c_chk_csp_exists%notfound
246 then
247 -- data does not exist in customer, and hence create the data.
248 close c_chk_csp_exists;
249 if p_setup_purpose_id is null
250 then
251 open c_get_cspid;
252 fetch c_get_cspid into l_setup_purpose_id;
253 close c_get_cspid;
254 else
255 l_setup_purpose_id := p_setup_purpose_id;
256 end if;
257
258 l_obj_verno := 1;
259
260 Insert_Row(px_setup_purpose_id => l_setup_purpose_id,
261 p_custom_setup_id => p_custom_setup_id,
262 p_last_update_date => X_LAST_UPDATE_DATE,
263 p_last_updated_by => l_user_id,
264 p_creation_date => X_LAST_UPDATE_DATE,
265 p_created_by => l_user_id,
266 p_last_update_login => 0,
267 p_activity_purpose_code => p_activity_purpose_code,
268 p_enabled_flag => p_enabled_flag,
269 p_def_list_template_id => p_def_list_template_id,
270 px_object_version_number => l_obj_verno);
271
272 else
273 -- Update the data as per above rules.
274 close c_chk_csp_exists;
275 open c_db_data_details;
276 fetch c_db_data_details into l_db_luby_id, l_obj_verno;
277 close c_db_data_details;
278 if ( l_db_luby_id IN (1, 2, 0)
279 OR NVL(p_custom_mode,'PRESERVE') = 'FORCE') THEN
280
281 Update_Row(
282 p_setup_purpose_id => p_setup_purpose_id,
283 p_custom_setup_id => p_custom_setup_id,
284 p_last_update_date => X_LAST_UPDATE_DATE,
285 p_last_updated_by => l_user_id,
286 p_last_update_login => 0,
287 p_activity_purpose_code => p_activity_purpose_code,
288 p_enabled_flag => p_enabled_flag,
289 p_def_list_template_id => p_def_list_template_id,
290 p_object_version_number => l_obj_verno);
291
292 end if;
293 end if;
294 end LOAD_ROW;
295
296
297 END AMS_Custom_Setup_Purpose_PKG;