[Home] [Help]
PACKAGE BODY: APPS.CSC_CUSTOMERS_PKG
Source
1 package body csc_customers_pkg as
2 /*$Header: csctcccb.pls 115.7 2004/04/27 10:26:23 vshastry ship $*/
3
4 procedure process_audit_table (
5 x_party_id NUMBER,
6 x_cust_account_id NUMBER,
7 x_last_update_date DATE,
8 x_last_updated_by NUMBER,
9 x_last_update_login NUMBER,
10 x_creation_date DATE,
11 x_created_by NUMBER,
12 x_sys_det_critical_flag VARCHAR2,
13 x_override_flag VARCHAR2,
14 x_overridden_critical_flag VARCHAR2,
15 x_override_reason_code VARCHAR2,
16 p_party_status VARCHAR2 DEFAULT NULL,
17 p_request_id NUMBER DEFAULT NULL,
18 p_program_application_id NUMBER DEFAULT NULL,
19 p_program_id NUMBER DEFAULT NULL,
20 p_program_update_date DATE DEFAULT NULL);
21
22 procedure insert_row(
23 x_rowid IN OUT NOCOPY VARCHAR2,
24 x_party_id NUMBER,
25 x_cust_account_id NUMBER,
26 x_last_update_date DATE,
27 x_last_updated_by NUMBER,
28 x_last_update_login NUMBER,
29 x_creation_date DATE,
30 x_created_by NUMBER,
31 x_sys_det_critical_flag VARCHAR2,
32 x_override_flag VARCHAR2,
33 x_overridden_critical_flag VARCHAR2,
34 x_override_reason_code VARCHAR2,
35 x_attribute1 VARCHAR2 DEFAULT NULL,
36 x_attribute2 VARCHAR2 DEFAULT NULL,
37 x_attribute3 VARCHAR2 DEFAULT NULL,
38 x_attribute4 VARCHAR2 DEFAULT NULL,
39 x_attribute5 VARCHAR2 DEFAULT NULL,
40 x_attribute6 VARCHAR2 DEFAULT NULL,
41 x_attribute7 VARCHAR2 DEFAULT NULL,
42 x_attribute8 VARCHAR2 DEFAULT NULL,
43 x_attribute9 VARCHAR2 DEFAULT NULL,
44 x_attribute10 VARCHAR2 DEFAULT NULL,
45 x_attribute11 VARCHAR2 DEFAULT NULL,
46 x_attribute12 VARCHAR2 DEFAULT NULL,
47 x_attribute13 VARCHAR2 DEFAULT NULL,
48 x_attribute14 VARCHAR2 DEFAULT NULL,
49 x_attribute15 VARCHAR2 DEFAULT NULL,
50 p_party_status VARCHAR2 DEFAULT NULL,
51 p_request_id NUMBER DEFAULT NULL,
52 p_program_application_id NUMBER DEFAULT NULL,
53 p_program_id NUMBER DEFAULT NULL,
54 p_program_update_date DATE DEFAULT NULL)
55 IS
56 CURSOR C is select rowid from csc_customers
57 where party_id = x_party_id;
58 /*
59
60 CURSOR C1 is select pc.value from cs_prof_check_results pc
61 where pc.check_id (+) =
62 fnd_profile.value_wnps ('CS_CRITICAL_CUSTOMER_CHECK')
63 and pc.customer_id = x_customer_id;
64 */
65
66 BEGIN
67 INSERT INTO csc_customers
68 (
69 party_id,
70 cust_account_id,
71 last_update_date,
72 last_updated_by,
73 last_update_login,
74 creation_date,
75 created_by,
76 override_flag,
77 overridden_critical_flag,
78 override_reason_code,
79 party_status,
80 request_id,
81 program_application_id,
82 program_id,
83 program_update_date,
84 attribute1,
85 attribute2,
86 attribute3,
87 attribute4,
88 attribute5,
89 attribute6,
90 attribute7,
91 attribute8,
92 attribute9,
93 attribute10,
94 attribute11,
95 attribute12,
96 attribute13,
97 attribute14,
98 attribute15
99 )
100 VALUES
101 (
102 DECODE(x_party_id,FND_API.G_MISS_NUM,NULL,x_party_id),
103 DECODE(x_cust_account_id,FND_API.G_MISS_NUM,NULL,x_cust_account_id),
104 DECODE(x_last_update_date,FND_API.G_MISS_DATE,NULL,x_last_update_date),
105 DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by),
106 DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login),
107 DECODE(x_creation_date,FND_API.G_MISS_DATE,NULL,x_creation_date),
108 DECODE(x_created_by,FND_API.G_MISS_NUM,NULL,x_created_by),
109 DECODE(x_override_flag,FND_API.G_MISS_CHAR,NULL,x_override_flag),
110 DECODE(x_overridden_critical_flag,FND_API.G_MISS_CHAR,NULL,x_overridden_critical_flag),
111 DECODE(x_override_reason_code,FND_API.G_MISS_CHAR,NULL,x_override_reason_code),
112 DECODE(p_party_status,FND_API.G_MISS_CHAR,NULL,p_party_status),
113 DECODE(p_request_id,FND_API.G_MISS_NUM,NULL,p_request_id),
114 DECODE(p_program_application_id,FND_API.G_MISS_NUM,NULL,p_program_application_id),
115 DECODE(p_program_id,FND_API.G_MISS_NUM,NULL,p_program_id),
116 DECODE(p_program_update_date,FND_API.G_MISS_DATE,NULL,p_program_update_date),
117 x_attribute1,
118 x_attribute2,
119 x_attribute3,
120 x_attribute4,
121 x_attribute5,
122 x_attribute6,
123 x_attribute7,
124 x_attribute8,
125 x_attribute9,
126 x_attribute10,
127 x_attribute11,
128 x_attribute12,
129 x_attribute13,
130 x_attribute14,
131 x_attribute15
132 );
133 OPEN C;
134 FETCH C into x_rowid;
135 if (C%NOTFOUND) then
136 CLOSE C;
137 RAISE NO_DATA_FOUND;
138 end if;
139 CLOSE C;
140
141 process_audit_table (
142 x_party_id,
143 x_cust_account_id,
144 x_last_update_date,
145 x_last_updated_by,
146 x_last_update_login,
147 x_creation_date,
148 x_created_by,
149 x_sys_det_critical_flag,
150 x_override_flag,
151 x_overridden_critical_flag,
152 x_override_reason_code,
153 p_party_status,
154 p_request_id,
155 p_program_application_id,
156 p_program_id,
157 p_program_update_date );
158
159 END insert_row;
160
161 procedure process_audit_table(
162 x_party_id NUMBER,
163 x_cust_account_id NUMBER,
164 x_last_update_date DATE,
165 x_last_updated_by NUMBER,
166 x_last_update_login NUMBER,
167 x_creation_date DATE,
168 x_created_by NUMBER,
169 x_sys_det_critical_flag VARCHAR2,
170 x_override_flag VARCHAR2,
171 x_overridden_critical_flag VARCHAR2,
172 x_override_reason_code VARCHAR2,
173 p_party_status VARCHAR2 DEFAULT NULL,
174 p_request_id NUMBER DEFAULT NULL,
175 p_program_application_id NUMBER DEFAULT NULL,
176 p_program_id NUMBER DEFAULT NULL,
177 p_program_update_date DATE DEFAULT NULL)
178 IS
179 x_cust_hist_id number;
180 cursor C2 is select csc_customers_audit_hist_s.nextval from sys.dual;
181
182 /*
183 x_sys_det_critical_flag varchar2(1);
184 cursor C1 is select pc.value
185 from cs_prof_check_results pc
186 where pc.check_id (+) =
187 fnd_profile.value_wnps ('CS_CRITICAL_CUSTOMER_CHECK')
188 and pc.customer_id = x_customer_id;
189 */
190
191 begin
192
193 if x_cust_hist_id is null then
194 open C2;
195 fetch C2 into x_cust_hist_id;
196 close C2;
197 end if;
198
199 /*
200 if x_sys_det_critical_flag is null then
201 open C1;
202 fetch C1 into x_sys_det_critical_flag;
203 close C1;
204 end if;
205 */
206
207 -- Bug 1352203 - while inserting, the changed by field should be
208 -- populated with last_updated_by instead of created_by
209 insert into csc_customers_audit_hist (
210 cust_hist_id,
211 party_id,
212 cust_account_id,
213 last_update_date,
214 last_updated_by,
215 last_update_login,
216 creation_date,
217 created_by,
218 changed_date,
219 changed_by,
220 sys_det_critical_flag,
221 override_flag,
222 overridden_critical_flag,
223 override_reason_code,
224 party_status,
225 request_id,
226 program_application_id,
227 program_id,
228 program_update_date )
229 values (
230 x_cust_hist_id,
231 x_party_id,
232 x_cust_account_id,
233 sysdate,
234 x_last_updated_by,
235 x_last_update_login,
236 sysdate,
237 x_created_by,
238 sysdate,
239 x_last_updated_by,
240 x_sys_det_critical_flag,
241 x_override_flag,
242 x_overridden_critical_flag,
243 x_override_reason_code,
244 p_party_status,
245 p_request_id,
246 p_program_application_id,
247 p_program_id,
248 p_program_update_date );
249 /*
250 open C2;
251 fetch C2 into x_rowid;
252 if (C2%NOTFOUND) then
253 close C2;
254 RAISE NO_DATA_FOUND;
255 end if;
256 close C2;
257 */
258 end process_audit_table;
259
260
261 Procedure lock_row(
262 x_rowid VARCHAR2,
263 x_party_id NUMBER,
264 x_cust_account_id NUMBER,
265 x_last_update_date DATE,
266 x_last_updated_by NUMBER,
267 x_last_update_login NUMBER,
268 x_creation_date DATE,
269 x_created_by NUMBER,
270 x_override_flag VARCHAR2,
271 x_overridden_critical_flag VARCHAR2,
272 x_override_reason_code VARCHAR2
273 ) IS
274 CURSOR C is
275 select *
276 from csc_customers
277 where rowid = x_rowid
278 for update of party_id NOWAIT;
279 Recinfo C%ROWTYPE;
280 BEGIN
281 OPEN C;
282 FETCH C INTO recinfo;
283 if (C%NOTFOUND) then
284 CLOSE C;
285 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
286 APP_EXCEPTION.Raise_Exception;
287 end if;
288 CLOSE C;
289 if(
290 (Recinfo.party_id = x_party_id)
291 AND ( (Recinfo.last_update_date = x_last_update_date)
292 OR ( (Recinfo.last_update_date is null)
293 AND (x_last_update_date IS NULL)))
294 AND ( (Recinfo.last_updated_by = x_last_updated_by)
295 OR ( (Recinfo.last_updated_by is null)
296 AND (x_last_updated_by IS NULL)))
297 AND ( (Recinfo.last_update_login = x_last_update_login)
298 OR ( (Recinfo.last_update_login is null)
299 AND (x_last_update_login IS NULL)))
300 AND ( (Recinfo.creation_date = x_creation_date)
301 OR ( (Recinfo.creation_date is null)
302 AND (x_creation_date IS NULL)))
303 AND ( (Recinfo.created_by = x_created_by)
304 OR ( (Recinfo.created_by is null)
305 AND (x_created_by IS NULL)))
306 AND ( (Recinfo.override_flag = x_override_flag)
307 OR ( (Recinfo.override_flag is null)
308 AND (x_override_flag IS NULL)))
309 AND ( (Recinfo.overridden_critical_flag = x_overridden_critical_flag)
310 OR ( (Recinfo.overridden_critical_flag is null)
311 AND (x_overridden_critical_flag IS NULL)))
312 AND ( (Recinfo.override_reason_code = x_override_reason_code)
313 OR ( (Recinfo.override_reason_code is null)
314 AND (x_override_reason_code IS NULL)))
315 ) then
316 return;
317 else
318 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
319 APP_EXCEPTION.Raise_Exception;
320 end if;
321 end lock_row;
322
323 procedure update_row(
324 x_rowid VARCHAR2,
325 x_party_id NUMBER,
326 x_cust_account_id NUMBER,
327 x_last_update_date DATE,
328 x_last_updated_by NUMBER,
329 x_last_update_login NUMBER,
330 x_creation_date DATE,
331 x_created_by NUMBER,
332 x_sys_det_critical_flag VARCHAR2,
333 x_override_flag VARCHAR2,
334 x_overridden_critical_flag VARCHAR2,
335 x_override_reason_code VARCHAR2,
336 x_attribute1 VARCHAR2 DEFAULT NULL,
337 x_attribute2 VARCHAR2 DEFAULT NULL,
338 x_attribute3 VARCHAR2 DEFAULT NULL,
339 x_attribute4 VARCHAR2 DEFAULT NULL,
340 x_attribute5 VARCHAR2 DEFAULT NULL,
341 x_attribute6 VARCHAR2 DEFAULT NULL,
342 x_attribute7 VARCHAR2 DEFAULT NULL,
343 x_attribute8 VARCHAR2 DEFAULT NULL,
344 x_attribute9 VARCHAR2 DEFAULT NULL,
345 x_attribute10 VARCHAR2 DEFAULT NULL,
346 x_attribute11 VARCHAR2 DEFAULT NULL,
347 x_attribute12 VARCHAR2 DEFAULT NULL,
348 x_attribute13 VARCHAR2 DEFAULT NULL,
349 x_attribute14 VARCHAR2 DEFAULT NULL,
350 x_attribute15 VARCHAR2 DEFAULT NULL,
351 p_party_status VARCHAR2 DEFAULT NULL,
352 p_request_id NUMBER DEFAULT NULL,
353 p_program_application_id NUMBER DEFAULT NULL,
354 p_program_id NUMBER DEFAULT NULL,
355 p_program_update_date DATE DEFAULT NULL)
356 IS
357 begin
358 update csc_customers
359 set
360 party_id = DECODE(x_party_id,FND_API.G_MISS_NUM,NULL,NVL(x_party_id,party_id)),
361 cust_account_id = DECODE(x_cust_account_id,FND_API.G_MISS_NUM,NULL,NVL(x_cust_account_id,cust_account_id)),
362 last_update_date = DECODE(x_last_update_date,FND_API.G_MISS_DATE,NULL,NVL(x_last_update_date,last_update_date)),
363 last_updated_by = DECODE(x_last_updated_by,FND_API.G_MISS_CHAR,NULL,NVL(x_last_updated_by,last_updated_by)),
364 last_update_login = DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,NVL(x_last_update_login,last_update_login)),
365 --creation_date = x_creation_date,
366 --created_by = x_created_by,
367 override_flag = DECODE(x_override_flag,FND_API.G_MISS_CHAR,NULL,NVL(x_override_flag,override_flag)),
368 overridden_critical_flag = DECODE(x_overridden_critical_flag,FND_API.G_MISS_CHAR,NULL,NVL(x_overridden_critical_flag,overridden_critical_flag)),
369 override_reason_code = DECODE(x_override_reason_code,FND_API.G_MISS_CHAR,NULL,NVL(x_override_reason_code,override_reason_code)),
370 party_status = DECODE(p_party_status,FND_API.G_MISS_CHAR,NULL,NVL(p_party_status,party_status)),
371 request_id = DECODE(p_request_id,FND_API.G_MISS_NUM,NULL,NVL(p_request_id,request_id)),
372 program_application_id = DECODE(p_program_application_id,FND_API.G_MISS_NUM,NULL,NVL(p_program_application_id,program_application_id)),
373 program_id = DECODE(p_program_id,FND_API.G_MISS_NUM,NULL,NVL(p_program_id,program_id)),
374 program_update_date = DECODE(p_program_update_date,FND_API.G_MISS_DATE,NULL,NVL(p_program_update_date,program_update_date)),
375 attribute1 = x_attribute1,
376 attribute2 = x_attribute2,
377 attribute3 = x_attribute3,
378 attribute4 = x_attribute4,
379 attribute5 = x_attribute5,
380 attribute6 = x_attribute6,
381 attribute7 = x_attribute7,
382 attribute8 = x_attribute8,
383 attribute9 = x_attribute9,
384 attribute10 = x_attribute10,
385 attribute11 = x_attribute11,
386 attribute12 = x_attribute12,
387 attribute13 = x_attribute13,
388 attribute14 = x_attribute14,
389 attribute15 = x_attribute15
390 where rowid = x_rowid;
391
392 if (SQL%NOTFOUND) then
393 raise NO_DATA_FOUND;
394 end if;
395
396 process_audit_table (
397 x_party_id,
398 x_cust_account_id,
399 x_last_update_date,
400 x_last_updated_by,
401 x_last_update_login,
402 x_creation_date,
403 x_created_by,
404 --x_changed_date,
405 --x_changed_by,
406 x_sys_det_critical_flag,
407 x_override_flag,
408 x_overridden_critical_flag,
409 x_override_reason_code,
410 p_party_status,
411 p_request_id,
412 p_program_application_id,
413 p_program_id,
414 p_program_update_date );
415 end update_row;
416 /*
417 procedure delete_row(x_rowid varchar2) is
418 begin
419 delete from cs_contacts
420 where rowid = x_rowid;
421 if (SQL%NOTFOUND) then
422 raise NO_DATA_FOUND;
423 end if;
424 end delete_row;
425 */
426
427 end csc_customers_pkg;