[Home] [Help]
PACKAGE BODY: APPS.AMS_DM_TARGET_SOURCES_PKG
Source
1 PACKAGE BODY AMS_Dm_Target_Sources_PKG as
2 /* $Header: amstdtsb.pls 115.6 2003/12/24 22:51:00 choang noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Dm_Target_Sources_PKG
7 -- Purpose
8 --
9 -- History
10 -- 17-Dec-2003 choang bug 3316903: fixed load_row logic
11 -- 24-Dec-2003 choang bug 3338413: fixed load_row logic for call to insert_row
12 --
13 -- NOTE
14 --
15 -- This Api is generated with Latest version of
16 -- Rosetta, where g_miss indicates NULL and
17 -- NULL indicates missing value. Rosetta Version 1.55
18 -- End of Comments
19 -- ===============================================================
20
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Dm_Target_Sources_PKG';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstdtsb.pls';
24
25
26
27
28 -- ========================================================
29 --
30 -- NAME
31 -- Insert_Row
32 --
33 -- PURPOSE
34 --
35 -- NOTES
36 --
37 -- HISTORY
38 --
39 -- ========================================================
40 PROCEDURE Insert_Row(
41 px_target_source_id IN OUT NOCOPY NUMBER,
42 p_last_update_date DATE,
43 p_last_updated_by NUMBER,
44 p_creation_date DATE,
45 p_created_by NUMBER,
46 p_last_update_login NUMBER,
47 px_object_version_number IN OUT NOCOPY NUMBER,
48 p_target_id NUMBER,
49 p_data_source_id NUMBER)
50
51 IS
52 x_rowid VARCHAR2(30);
53
54
55 BEGIN
56
57
58 px_object_version_number := nvl(px_object_version_number, 1);
59
60
61 INSERT INTO ams_dm_target_sources(
62 target_source_id,
63 last_update_date,
64 last_updated_by,
65 creation_date,
66 created_by,
67 last_update_login,
68 object_version_number,
69 target_id,
70 data_source_id
71 ) VALUES (
72 DECODE( px_target_source_id, FND_API.G_MISS_NUM, NULL, px_target_source_id),
73 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
74 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
75 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
76 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
77 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
78 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
79 DECODE( p_target_id, FND_API.G_MISS_NUM, NULL, p_target_id),
80 DECODE( p_data_source_id, FND_API.G_MISS_NUM, NULL, p_data_source_id));
81
82 END Insert_Row;
83
84
85
86
87 -- ========================================================
88 --
89 -- NAME
90 -- Update_Row
91 --
92 -- PURPOSE
93 --
94 -- NOTES
95 --
96 -- HISTORY
97 -- 15-Dec-2003 choang changed obj version num to update without increment
98 --
99 -- ========================================================
100 PROCEDURE Update_Row(
101 p_target_source_id NUMBER,
102 p_last_update_date DATE,
103 p_last_updated_by NUMBER,
104 p_last_update_login NUMBER,
105 p_object_version_number IN NUMBER,
106 p_target_id NUMBER,
107 p_data_source_id NUMBER)
108
109 IS
110 BEGIN
111 Update ams_dm_target_sources
112 SET
113 target_source_id = DECODE( p_target_source_id, null, target_source_id, FND_API.G_MISS_NUM, null, p_target_source_id),
114 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
115 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
116 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
117 object_version_number = p_object_version_number,
118 target_id = DECODE( p_target_id, null, target_id, FND_API.G_MISS_NUM, null, p_target_id),
119 data_source_id = DECODE( p_data_source_id, null, data_source_id, FND_API.G_MISS_NUM, null, p_data_source_id)
120 WHERE target_source_id = p_target_source_id;
121
122
123 IF (SQL%NOTFOUND) THEN
124 RAISE NO_DATA_FOUND;
125 END IF;
126
127
128 END Update_Row;
129
130
131
132
133 -- ========================================================
134 --
135 -- NAME
136 -- Delete_Row
137 --
138 -- PURPOSE
139 --
140 -- NOTES
141 --
142 -- HISTORY
143 --
144 -- ========================================================
145 PROCEDURE Delete_Row(
146 p_target_source_id NUMBER,
147 p_object_version_number NUMBER)
148 IS
149 BEGIN
150 DELETE FROM ams_dm_target_sources
151 WHERE target_source_id = p_target_source_id
152 AND object_version_number = p_object_version_number;
153 If (SQL%NOTFOUND) then
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 End If;
156 END Delete_Row ;
157
158
159
160
161
162 -- ========================================================
163 --
164 -- NAME
165 -- Lock_Row
166 --
167 -- PURPOSE
168 --
169 -- NOTES
170 --
171 -- HISTORY
172 --
173 -- ========================================================
174 PROCEDURE Lock_Row(
175 p_target_source_id NUMBER,
176 p_object_version_number NUMBER)
177 IS
178 CURSOR C IS
179 SELECT *
180 FROM ams_dm_target_sources
181 WHERE target_source_id = p_target_source_id
182 AND object_version_number = p_object_version_number
183 FOR UPDATE OF target_source_id NOWAIT;
184 Recinfo C%ROWTYPE;
185 BEGIN
186
187 OPEN c;
188 FETCH c INTO Recinfo;
189 IF (c%NOTFOUND) THEN
190 CLOSE c;
191 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
192 RAISE FND_API.g_exc_error;
193 END IF;
194 CLOSE c;
195 END Lock_Row;
196
197
198 PROCEDURE load_row(
199 x_target_source_id IN NUMBER,
200 x_target_id IN NUMBER,
201 x_data_source_id IN NUMBER,
202 x_owner IN VARCHAR2,
203 x_custom_mode IN VARCHAR2
204 )
205 IS
206 l_user_id number := 0;
207 l_db_luby_id number;
208 l_obj_verno number;
209 l_target_source_id number := x_target_source_id;
210
211 cursor c_chk_target_source_exists is
212 select last_updated_by, nvl(object_version_number, 1)
213 from ams_dm_target_sources
214 where target_source_id = x_target_source_id;
215
216 cursor c_get_target_source_id is
217 select ams_dm_target_sources_s.nextval
218 from dual;
219
220 BEGIN
221
222 -- set the last_updated_by to be used while updating the data in customer data.
223 if X_OWNER = 'SEED' then
224 l_user_id := 1;
225 elsif X_OWNER = 'ORACLE' THEN
226 l_user_id := 2;
227 elsif X_OWNER = 'SYSADMIN' THEN
228 l_user_id := 0;
229 end if ;
230
231 -- choang - 17-Dec-2003 - Fixed bug 3316903: added close to "found"
232 -- condition and modified if/else logic
233 open c_chk_target_source_exists;
234 fetch c_chk_target_source_exists into l_db_luby_id, l_obj_verno;
235 if c_chk_target_source_exists%notfound THEN
236 if x_target_source_id is null then
237 open c_get_target_source_id;
238 fetch c_get_target_source_id into l_target_source_id;
239 close c_get_target_source_id;
240 end if;
241
242 l_obj_verno := 1;
243
244 -- choang - 24-Dec-2003 - fixed 3338413: changed parameter for
245 -- target_source_id
246 AMS_Dm_Target_Sources_PKG.Insert_Row(
247 px_target_source_id => l_target_source_id,
248 p_last_update_date => SYSDATE,
249 p_last_updated_by => l_user_id,
250 p_creation_date => SYSDATE,
251 p_created_by => l_user_id,
252 p_last_update_login => 0,
253 px_object_version_number => l_obj_verno,
254 p_target_id => x_target_id,
255 p_data_source_id => x_data_source_id
256 );
257
258 else
259 l_target_source_id := x_target_source_id;
260
261 if ( l_db_luby_id IN (1, 2, 0) OR NVL(x_custom_mode,'PRESERVE') = 'FORCE') THEN
262 AMS_Dm_Target_Sources_PKG.Update_Row(
263 p_target_source_id => l_target_source_id,
264 p_last_update_date => SYSDATE,
265 p_last_updated_by => l_user_id,
266 p_last_update_login => 0,
267 p_object_version_number => l_obj_verno,
268 p_target_id => x_target_id,
269 p_data_source_id => x_data_source_id
270 );
271 end if;
272 end if;
273 close c_chk_target_source_exists;
274 END load_row;
275
276 END AMS_Dm_Target_Sources_PKG;