[Home] [Help]
PACKAGE BODY: APPS.AMS_ACT_CONTACT_POINTS_PKG
Source
1 PACKAGE BODY AMS_ACT_CONTACT_POINTS_PKG as
2 /* $Header: amstconb.pls 120.0 2005/05/31 17:56:32 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_ACT_CONTACT_POINTS_PKG
7 -- Purpose
8 --
9 -- History
10 -- 20-may-2005 musman Added contact_point_value_id column for webadi collaboration script usage
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_ACT_CONTACT_POINTS_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstconb.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 PROCEDURE Insert_Row(
38 px_contact_point_id IN OUT NOCOPY NUMBER,
39 p_last_update_date DATE,
40 p_last_updated_by NUMBER,
41 p_creation_date DATE,
42 p_created_by NUMBER,
43 p_last_update_login NUMBER,
44 px_object_version_number IN OUT NOCOPY NUMBER,
45 p_arc_contact_used_by VARCHAR2,
46 p_act_contact_used_by_id NUMBER,
47 p_contact_point_type VARCHAR2,
48 p_contact_point_value VARCHAR2,
49 p_city VARCHAR2,
50 p_country NUMBER,
51 p_zipcode VARCHAR2,
52 p_attribute_category VARCHAR2,
53 p_attribute1 VARCHAR2,
54 p_attribute2 VARCHAR2,
55 p_attribute3 VARCHAR2,
56 p_attribute4 VARCHAR2,
57 p_attribute5 VARCHAR2,
58 p_attribute6 VARCHAR2,
59 p_attribute7 VARCHAR2,
60 p_attribute8 VARCHAR2,
61 p_attribute9 VARCHAR2,
62 p_attribute10 VARCHAR2,
63 p_attribute11 VARCHAR2,
64 p_attribute12 VARCHAR2,
65 p_attribute13 VARCHAR2,
66 p_attribute14 VARCHAR2,
67 p_attribute15 VARCHAR2
68 ,p_contact_point_value_id NUMBER
69 )
70
71 IS
72 x_rowid VARCHAR2(30);
73
74
75 BEGIN
76
77
78 px_object_version_number := 1;
79
80
81 INSERT INTO AMS_ACT_CONTACT_POINTS(
82 contact_point_id,
83 last_update_date,
84 last_updated_by,
85 creation_date,
86 created_by,
87 last_update_login,
88 object_version_number,
89 arc_contact_used_by,
90 act_contact_used_by_id,
91 contact_point_type,
92 contact_point_value,
93 city,
94 country,
95 zipcode,
96 attribute_category,
97 attribute1,
98 attribute2,
99 attribute3,
100 attribute4,
101 attribute5,
102 attribute6,
103 attribute7,
104 attribute8,
105 attribute9,
106 attribute10,
107 attribute11,
108 attribute12,
109 attribute13,
110 attribute14,
111 attribute15,
112 contact_point_value_id
113 ) VALUES (
114 DECODE( px_contact_point_id, FND_API.g_miss_num, NULL, px_contact_point_id),
115 DECODE( p_last_update_date, FND_API.g_miss_date, NULL, p_last_update_date),
116 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
117 DECODE( p_creation_date, FND_API.g_miss_date, NULL, p_creation_date),
118 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
119 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
120 px_object_version_number, -- object version_number is always 1 when created
121 DECODE( p_arc_contact_used_by, FND_API.g_miss_char, NULL, p_arc_contact_used_by),
122 DECODE( p_act_contact_used_by_id, FND_API.g_miss_num, NULL, p_act_contact_used_by_id),
123 DECODE( p_contact_point_type, FND_API.g_miss_char, NULL, p_contact_point_type),
124 DECODE( p_contact_point_value, FND_API.g_miss_char, NULL, p_contact_point_value),
125 DECODE( p_city, FND_API.g_miss_char, NULL, p_city),
126 DECODE( p_country, FND_API.g_miss_num, NULL, p_country),
127 DECODE( p_zipcode, FND_API.g_miss_char, NULL, p_zipcode),
128 DECODE( p_attribute_category, FND_API.g_miss_char, NULL, p_attribute_category),
129 DECODE( p_attribute1, FND_API.g_miss_char, NULL, p_attribute1),
130 DECODE( p_attribute2, FND_API.g_miss_char, NULL, p_attribute2),
131 DECODE( p_attribute3, FND_API.g_miss_char, NULL, p_attribute3),
132 DECODE( p_attribute4, FND_API.g_miss_char, NULL, p_attribute4),
133 DECODE( p_attribute5, FND_API.g_miss_char, NULL, p_attribute5),
134 DECODE( p_attribute6, FND_API.g_miss_char, NULL, p_attribute6),
135 DECODE( p_attribute7, FND_API.g_miss_char, NULL, p_attribute7),
136 DECODE( p_attribute8, FND_API.g_miss_char, NULL, p_attribute8),
137 DECODE( p_attribute9, FND_API.g_miss_char, NULL, p_attribute9),
138 DECODE( p_attribute10, FND_API.g_miss_char, NULL, p_attribute10),
139 DECODE( p_attribute11, FND_API.g_miss_char, NULL, p_attribute11),
140 DECODE( p_attribute12, FND_API.g_miss_char, NULL, p_attribute12),
141 DECODE( p_attribute13, FND_API.g_miss_char, NULL, p_attribute13),
142 DECODE( p_attribute14, FND_API.g_miss_char, NULL, p_attribute14),
143 DECODE( p_attribute15, FND_API.g_miss_char, NULL, p_attribute15),
144 DECODE( p_contact_point_value_id, FND_API.g_miss_num, NULL, p_contact_point_value_id));
145 END Insert_Row;
146
147
148 ----------------------------------------------------------
149 ---- MEDIA ----
150 ----------------------------------------------------------
151
152 -- ========================================================
153 --
154 -- NAME
155 -- createUpdateBody
156 --
157 -- PURPOSE
158 --
159 -- NOTES
160 --
161 -- HISTORY
162 --
163 -- ========================================================
164 PROCEDURE Update_Row(
165 p_contact_point_id NUMBER,
166 p_last_update_date DATE,
167 p_last_updated_by NUMBER,
168 p_last_update_login NUMBER,
169 p_object_version_number NUMBER,
170 p_arc_contact_used_by VARCHAR2,
171 p_act_contact_used_by_id NUMBER,
172 p_contact_point_type VARCHAR2,
173 p_contact_point_value VARCHAR2,
174 p_city VARCHAR2,
175 p_country NUMBER,
176 p_zipcode VARCHAR2,
177 p_attribute_category VARCHAR2,
178 p_attribute1 VARCHAR2,
179 p_attribute2 VARCHAR2,
180 p_attribute3 VARCHAR2,
181 p_attribute4 VARCHAR2,
182 p_attribute5 VARCHAR2,
183 p_attribute6 VARCHAR2,
184 p_attribute7 VARCHAR2,
185 p_attribute8 VARCHAR2,
186 p_attribute9 VARCHAR2,
187 p_attribute10 VARCHAR2,
188 p_attribute11 VARCHAR2,
189 p_attribute12 VARCHAR2,
190 p_attribute13 VARCHAR2,
191 p_attribute14 VARCHAR2,
192 p_attribute15 VARCHAR2,
193 p_contact_point_value_id NUMBER)
194
195 IS
196 BEGIN
197
198
199 Update AMS_ACT_CONTACT_POINTS
200 SET
201 contact_point_id = DECODE( p_contact_point_id, FND_API.g_miss_num, contact_point_id, p_contact_point_id),
202 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
203 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
204 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
205 object_version_number = object_version_number + 1, -- always increment the object version Number by 1
206 arc_contact_used_by = DECODE( p_arc_contact_used_by, FND_API.g_miss_char, arc_contact_used_by, p_arc_contact_used_by),
207 act_contact_used_by_id = DECODE( p_act_contact_used_by_id, FND_API.g_miss_num, act_contact_used_by_id, p_act_contact_used_by_id),
208 contact_point_type = DECODE( p_contact_point_type, FND_API.g_miss_char, contact_point_type, p_contact_point_type),
209 contact_point_value = DECODE( p_contact_point_value, FND_API.g_miss_char, contact_point_value, p_contact_point_value),
210 city = DECODE( p_city, FND_API.g_miss_char, city, p_city),
211 country = DECODE( p_country, FND_API.g_miss_num, country, p_country),
212 zipcode = DECODE( p_zipcode, FND_API.g_miss_char, zipcode, p_zipcode),
213 attribute_category = DECODE( p_attribute_category, FND_API.g_miss_char, attribute_category, p_attribute_category),
214 attribute1 = DECODE( p_attribute1, FND_API.g_miss_char, attribute1, p_attribute1),
215 attribute2 = DECODE( p_attribute2, FND_API.g_miss_char, attribute2, p_attribute2),
216 attribute3 = DECODE( p_attribute3, FND_API.g_miss_char, attribute3, p_attribute3),
217 attribute4 = DECODE( p_attribute4, FND_API.g_miss_char, attribute4, p_attribute4),
218 attribute5 = DECODE( p_attribute5, FND_API.g_miss_char, attribute5, p_attribute5),
219 attribute6 = DECODE( p_attribute6, FND_API.g_miss_char, attribute6, p_attribute6),
220 attribute7 = DECODE( p_attribute7, FND_API.g_miss_char, attribute7, p_attribute7),
221 attribute8 = DECODE( p_attribute8, FND_API.g_miss_char, attribute8, p_attribute8),
222 attribute9 = DECODE( p_attribute9, FND_API.g_miss_char, attribute9, p_attribute9),
223 attribute10 = DECODE( p_attribute10, FND_API.g_miss_char, attribute10, p_attribute10),
224 attribute11 = DECODE( p_attribute11, FND_API.g_miss_char, attribute11, p_attribute11),
225 attribute12 = DECODE( p_attribute12, FND_API.g_miss_char, attribute12, p_attribute12),
226 attribute13 = DECODE( p_attribute13, FND_API.g_miss_char, attribute13, p_attribute13),
227 attribute14 = DECODE( p_attribute14, FND_API.g_miss_char, attribute14, p_attribute14),
228 attribute15 = DECODE( p_attribute15, FND_API.g_miss_char, attribute15, p_attribute15),
229 contact_point_value_id = DECODE( p_contact_point_value_id, FND_API.g_miss_num, contact_point_value_id, p_contact_point_value_id)
230 WHERE CONTACT_POINT_ID = p_CONTACT_POINT_ID
231 AND object_version_number = p_object_version_number;
232
233 IF (SQL%NOTFOUND) THEN
234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
235 END IF;
236 END Update_Row;
237
238
239 ----------------------------------------------------------
240 ---- MEDIA ----
241 ----------------------------------------------------------
242
243 -- ========================================================
244 --
245 -- NAME
246 -- createDeleteBody
247 --
248 -- PURPOSE
249 --
250 -- NOTES
251 --
252 -- HISTORY
253 --
254 -- ========================================================
255 PROCEDURE Delete_Row(
256 p_CONTACT_POINT_ID NUMBER)
257 IS
258 BEGIN
259 DELETE FROM AMS_ACT_CONTACT_POINTS
260 WHERE CONTACT_POINT_ID = p_CONTACT_POINT_ID;
261 If (SQL%NOTFOUND) then
262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263 End If;
264 END Delete_Row ;
265
266
267
268 ----------------------------------------------------------
269 ---- MEDIA ----
270 ----------------------------------------------------------
271
272 -- ========================================================
273 --
274 -- NAME
275 -- createLockBody
276 --
277 -- PURPOSE
278 --
279 -- NOTES
280 --
281 -- HISTORY
282 --
283 -- ========================================================
284 PROCEDURE Lock_Row( p_CONTACT_POINT_ID NUMBER)
285
286 IS
287 CURSOR C IS
288 SELECT *
289 FROM AMS_ACT_CONTACT_POINTS
290 WHERE CONTACT_POINT_ID = p_CONTACT_POINT_ID
291 FOR UPDATE of CONTACT_POINT_ID NOWAIT;
292 Recinfo C%ROWTYPE;
293 BEGIN
294 OPEN c;
295 FETCH c INTO Recinfo;
296 If (c%NOTFOUND) then
297 CLOSE c;
298 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
299 APP_EXCEPTION.RAISE_EXCEPTION;
300 END IF;
301 CLOSE C;
302 END Lock_Row;
303
304 END AMS_ACT_CONTACT_POINTS_PKG;