[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_USG_ASSIGNMENTS_PKG
Source
1 PACKAGE BODY HZ_PARTY_USG_ASSIGNMENTS_PKG AS
2 /*$Header: ARHPUATB.pls 120.2 2006/02/28 21:59:56 jhuang noship $ */
3
4 D_FUTURE_DATE CONSTANT DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
5
6 PROCEDURE insert_row (
7 x_party_id IN NUMBER,
8 x_party_usage_code IN VARCHAR2,
9 x_effective_start_date IN DATE,
10 x_effective_end_date IN DATE,
11 x_status_flag IN VARCHAR2,
12 x_comments IN VARCHAR2,
13 x_owner_table_name IN VARCHAR2,
14 x_owner_table_id IN NUMBER,
15 x_attribute_category IN VARCHAR2,
16 x_attribute1 IN VARCHAR2,
17 x_attribute2 IN VARCHAR2,
18 x_attribute3 IN VARCHAR2,
19 x_attribute4 IN VARCHAR2,
20 x_attribute5 IN VARCHAR2,
21 x_attribute6 IN VARCHAR2,
22 x_attribute7 IN VARCHAR2,
23 x_attribute8 IN VARCHAR2,
24 x_attribute9 IN VARCHAR2,
25 x_attribute10 IN VARCHAR2,
26 x_attribute11 IN VARCHAR2,
27 x_attribute12 IN VARCHAR2,
28 x_attribute13 IN VARCHAR2,
29 x_attribute14 IN VARCHAR2,
30 x_attribute15 IN VARCHAR2,
31 x_attribute16 IN VARCHAR2,
32 x_attribute17 IN VARCHAR2,
33 x_attribute18 IN VARCHAR2,
34 x_attribute19 IN VARCHAR2,
35 x_attribute20 IN VARCHAR2,
36 x_object_version_number IN NUMBER,
37 x_created_by_module IN VARCHAR2,
38 x_application_id IN NUMBER,
39 x_party_usg_assignment_id OUT NOCOPY NUMBER
40 ) IS
41
42 BEGIN
43
44 INSERT INTO HZ_PARTY_USG_ASSIGNMENTS (
45 party_usg_assignment_id,
46 party_id,
47 party_usage_code,
48 effective_start_date,
49 effective_end_date,
50 status_flag,
51 comments,
52 owner_table_name,
53 owner_table_id,
54 attribute_category,
55 attribute1,
56 attribute2,
57 attribute3,
58 attribute4,
59 attribute5,
60 attribute6,
61 attribute7,
62 attribute8,
63 attribute9,
64 attribute10,
65 attribute11,
66 attribute12,
67 attribute13,
68 attribute14,
69 attribute15,
70 attribute16,
71 attribute17,
72 attribute18,
73 attribute19,
74 attribute20,
75 object_version_number,
76 created_by_module,
77 application_id,
78 created_by,
79 creation_date,
80 last_update_login,
81 last_update_date,
82 last_updated_by,
83 request_id,
84 program_application_id,
85 program_id,
86 program_login_id
87 )
88 VALUES (
89 hz_party_usg_assignments_s.nextval,
90 x_party_id,
91 x_party_usage_code,
92 DECODE(x_effective_start_date,
93 NULL, trunc(sysdate),
94 FND_API.G_MISS_DATE, trunc(sysdate),
95 trunc(x_effective_start_date)),
96 DECODE(x_effective_end_date,
97 NULL, D_FUTURE_DATE,
98 FND_API.G_MISS_DATE, D_FUTURE_DATE,
99 trunc(x_effective_end_date)),
100 DECODE(x_status_flag,
101 NULL, 'A',
102 FND_API.G_MISS_CHAR, 'A',
103 x_status_flag),
104 DECODE(x_comments,
105 FND_API.G_MISS_CHAR, NULL,
106 x_comments),
107 DECODE(x_owner_table_name,
108 FND_API.G_MISS_CHAR, NULL,
109 x_owner_table_name),
110 DECODE(x_owner_table_id,
111 FND_API.G_MISS_NUM, NULL,
112 x_owner_table_id),
113 DECODE(x_attribute_category,
114 FND_API.G_MISS_CHAR, NULL,
115 x_attribute_category),
116 DECODE(x_attribute1,
117 FND_API.G_MISS_CHAR, NULL,
118 x_attribute1),
119 DECODE(x_attribute2,
120 FND_API.G_MISS_CHAR, NULL,
121 x_attribute2),
122 DECODE(x_attribute3,
123 FND_API.G_MISS_CHAR, NULL,
124 x_attribute3),
125 DECODE(x_attribute4,
126 FND_API.G_MISS_CHAR, NULL,
127 x_attribute4),
128 DECODE(x_attribute5,
129 FND_API.G_MISS_CHAR, NULL,
130 x_attribute5),
131 DECODE(x_attribute6,
132 FND_API.G_MISS_CHAR, NULL,
133 x_attribute6),
134 DECODE(x_attribute7,
135 FND_API.G_MISS_CHAR, NULL,
136 x_attribute7),
137 DECODE(x_attribute8,
138 FND_API.G_MISS_CHAR, NULL,
139 x_attribute8),
140 DECODE(x_attribute9,
141 FND_API.G_MISS_CHAR, NULL,
142 x_attribute9),
143 DECODE(x_attribute10,
144 FND_API.G_MISS_CHAR, NULL,
145 x_attribute10),
146 DECODE(x_attribute11,
147 FND_API.G_MISS_CHAR, NULL,
148 x_attribute11),
149 DECODE(x_attribute12,
150 FND_API.G_MISS_CHAR, NULL,
151 x_attribute12),
152 DECODE(x_attribute13,
153 FND_API.G_MISS_CHAR, NULL,
154 x_attribute13),
155 DECODE(x_attribute14,
156 FND_API.G_MISS_CHAR, NULL,
157 x_attribute14),
158 DECODE(x_attribute15,
159 FND_API.G_MISS_CHAR, NULL,
160 x_attribute15),
161 DECODE(x_attribute16,
162 FND_API.G_MISS_CHAR, NULL,
163 x_attribute16),
164 DECODE(x_attribute17,
165 FND_API.G_MISS_CHAR, NULL,
166 x_attribute17),
167 DECODE(x_attribute18,
168 FND_API.G_MISS_CHAR, NULL,
169 x_attribute18),
170 DECODE(x_attribute19,
171 FND_API.G_MISS_CHAR, NULL,
172 x_attribute19),
173 DECODE(x_attribute20,
174 FND_API.G_MISS_CHAR, NULL,
175 x_attribute20),
176 x_object_version_number,
177 x_created_by_module,
178 hz_utility_v2pub.application_id,
179 hz_utility_v2pub.created_by,
180 SYSDATE,
181 hz_utility_v2pub.last_update_login,
182 SYSDATE,
183 hz_utility_v2pub.last_updated_by,
184 hz_utility_v2pub.request_id,
185 hz_utility_v2pub.program_application_id,
186 hz_utility_v2pub.program_id,
187 fnd_global.conc_login_id
188 )
189 RETURNING
190 party_usg_assignment_id
191 INTO
192 x_party_usg_assignment_id;
193
194 END insert_row;
195
196
197 PROCEDURE update_row (
198 x_party_usg_assignment_id IN NUMBER,
199 x_party_id IN NUMBER,
200 x_party_usage_code IN VARCHAR2,
201 x_effective_start_date IN DATE,
202 x_effective_end_date IN DATE,
203 x_status_flag IN VARCHAR2,
204 x_comments IN VARCHAR2,
205 x_owner_table_name IN VARCHAR2,
206 x_owner_table_id IN NUMBER,
207 x_attribute_category IN VARCHAR2,
208 x_attribute1 IN VARCHAR2,
209 x_attribute2 IN VARCHAR2,
210 x_attribute3 IN VARCHAR2,
211 x_attribute4 IN VARCHAR2,
212 x_attribute5 IN VARCHAR2,
213 x_attribute6 IN VARCHAR2,
214 x_attribute7 IN VARCHAR2,
215 x_attribute8 IN VARCHAR2,
216 x_attribute9 IN VARCHAR2,
217 x_attribute10 IN VARCHAR2,
218 x_attribute11 IN VARCHAR2,
219 x_attribute12 IN VARCHAR2,
220 x_attribute13 IN VARCHAR2,
221 x_attribute14 IN VARCHAR2,
222 x_attribute15 IN VARCHAR2,
223 x_attribute16 IN VARCHAR2,
224 x_attribute17 IN VARCHAR2,
225 x_attribute18 IN VARCHAR2,
226 x_attribute19 IN VARCHAR2,
227 x_attribute20 IN VARCHAR2,
228 x_object_version_number IN NUMBER
229 ) IS
230
231 BEGIN
232
233 UPDATE HZ_PARTY_USG_ASSIGNMENTS
234 SET
235 party_id =
236 DECODE(x_party_id,
237 NULL, party_id,
238 x_party_id),
239 party_usage_code =
240 DECODE(x_party_usage_code,
241 NULL, party_usage_code,
242 x_party_usage_code),
243 effective_start_date =
244 DECODE(x_effective_start_date,
245 NULL, effective_start_date,
246 trunc(x_effective_start_date)),
247 effective_end_date =
248 DECODE(x_effective_end_date,
249 NULL, effective_end_date,
250 FND_API.G_MISS_DATE, D_FUTURE_DATE,
251 trunc(x_effective_end_date)),
252 status_flag =
253 DECODE(x_status_flag,
254 NULL, status_flag,
255 x_status_flag),
256 comments =
257 DECODE(x_comments,
258 NULL, comments,
259 FND_API.G_MISS_CHAR, NULL,
260 x_comments),
261 owner_table_name =
262 DECODE(x_owner_table_name,
263 NULL, owner_table_name,
264 FND_API.G_MISS_CHAR, NULL,
265 x_owner_table_name),
266 owner_table_id =
267 DECODE(x_owner_table_id,
268 NULL, owner_table_id,
269 FND_API.G_MISS_CHAR, NULL,
270 x_owner_table_id),
271 attribute_category =
272 DECODE(x_attribute_category,
273 NULL, attribute_category,
274 FND_API.G_MISS_CHAR, NULL,
275 x_attribute_category),
276 attribute1 =
277 DECODE(x_attribute1,
278 NULL, attribute1,
279 FND_API.G_MISS_CHAR, NULL,
280 x_attribute1),
281 attribute2 =
282 DECODE(x_attribute2,
283 NULL, attribute2,
284 FND_API.G_MISS_CHAR, NULL,
285 x_attribute2),
286 attribute3 =
287 DECODE(x_attribute3,
288 NULL, attribute3,
289 FND_API.G_MISS_CHAR, NULL,
290 x_attribute3),
291 attribute4 =
292 DECODE(x_attribute4,
293 NULL, attribute4,
294 FND_API.G_MISS_CHAR, NULL,
295 x_attribute4),
296 attribute5 =
297 DECODE(x_attribute5,
298 NULL, attribute5,
299 FND_API.G_MISS_CHAR, NULL,
300 x_attribute5),
301 attribute6 =
302 DECODE(x_attribute6,
303 NULL, attribute6,
304 FND_API.G_MISS_CHAR, NULL,
305 x_attribute6),
306 attribute7 =
307 DECODE(x_attribute7,
308 NULL, attribute7,
309 FND_API.G_MISS_CHAR, NULL,
310 x_attribute7),
311 attribute8 =
312 DECODE(x_attribute8,
313 NULL, attribute8,
314 FND_API.G_MISS_CHAR, NULL,
315 x_attribute8),
316 attribute9 =
317 DECODE(x_attribute9,
318 NULL, attribute9,
319 FND_API.G_MISS_CHAR, NULL,
320 x_attribute9),
321 attribute10 =
322 DECODE(x_attribute10,
323 NULL, attribute10,
324 FND_API.G_MISS_CHAR, NULL,
325 x_attribute10),
326 attribute11 =
327 DECODE(x_attribute11,
328 NULL, attribute11,
329 FND_API.G_MISS_CHAR, NULL,
330 x_attribute11),
331 attribute12 =
332 DECODE(x_attribute12,
333 NULL, attribute12,
334 FND_API.G_MISS_CHAR, NULL,
335 x_attribute12),
336 attribute13 =
337 DECODE(x_attribute13,
338 NULL, attribute13,
339 FND_API.G_MISS_CHAR, NULL,
340 x_attribute13),
341 attribute14 =
342 DECODE(x_attribute14,
343 NULL, attribute14,
344 FND_API.G_MISS_CHAR, NULL,
345 x_attribute14),
346 attribute15 =
347 DECODE(x_attribute15,
348 NULL, attribute15,
349 FND_API.G_MISS_CHAR, NULL,
350 x_attribute15),
351 attribute16 =
352 DECODE(x_attribute16,
353 NULL, attribute16,
354 FND_API.G_MISS_CHAR, NULL,
355 x_attribute16),
356 attribute17 =
357 DECODE(x_attribute17,
358 NULL, attribute17,
359 FND_API.G_MISS_CHAR, NULL,
360 x_attribute17),
361 attribute18 =
362 DECODE(x_attribute18,
363 NULL, attribute18,
364 FND_API.G_MISS_CHAR, NULL,
365 x_attribute18),
366 attribute19 =
367 DECODE(x_attribute19,
368 NULL, attribute19,
369 FND_API.G_MISS_CHAR, NULL,
370 x_attribute19),
371 attribute20 =
372 DECODE(x_attribute20,
373 NULL, attribute20,
374 FND_API.G_MISS_CHAR, NULL,
375 x_attribute20),
376 object_version_number =
377 DECODE(x_object_version_number,
378 NULL, object_version_number,
379 x_object_version_number),
380 last_update_login = hz_utility_v2pub.last_update_login,
381 last_update_date = SYSDATE,
382 last_updated_by = hz_utility_v2pub.last_updated_by,
383 request_id = hz_utility_v2pub.request_id,
384 program_application_id = hz_utility_v2pub.program_application_id,
385 program_id = hz_utility_v2pub.program_id,
386 program_login_id = fnd_global.conc_login_id,
387 application_id =
388 NVL(application_id, hz_utility_v2pub.application_id)
389 WHERE party_usg_assignment_id = x_party_usg_assignment_id;
390
391 IF ( SQL%NOTFOUND ) THEN
392 RAISE NO_DATA_FOUND;
393 END IF;
394
395 END update_row;
396
397
398 END HZ_PARTY_USG_ASSIGNMENTS_PKG;