[Home] [Help]
PACKAGE BODY: APPS.OZF_SETTLEMENT_DOCS_PKG
Source
1 PACKAGE BODY OZF_SETTLEMENT_DOCS_PKG as
2 /* $Header: ozftcsdb.pls 120.1 2005/07/08 06:59:59 appldev ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_SETTLEMENT_DOCS_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- MCHANG 23-OCT-2001 Remove security_group_id.
12 --
13 -- NOTE
14 --
15 -- End of Comments
16 -- ===============================================================
17
18
19 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_SETTLEMENT_DOCS_PKG';
20 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozftcsdb.pls';
21
22
23 ----------------------------------------------------------
24 ---- MEDIA ----
25 ----------------------------------------------------------
26
27 -- ========================================================
28 --
29 -- NAME
30 -- createInsertBody
31 --
32 -- PURPOSE
33 --
34 -- NOTES
35 --
36 -- HISTORY
37 --
38 -- ========================================================
39 PROCEDURE Insert_Row(
40 px_settlement_doc_id IN OUT NOCOPY NUMBER,
41 px_object_version_number 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 p_request_id NUMBER,
48 p_program_application_id NUMBER,
49 p_program_update_date DATE,
50 p_program_id NUMBER,
51 p_created_from VARCHAR2,
52 p_claim_id NUMBER,
53 p_claim_line_id NUMBER,
54 p_payment_method VARCHAR2,
55 p_settlement_id NUMBER,
56 p_settlement_type VARCHAR2,
57 p_settlement_type_id NUMBER,
58 p_settlement_number VARCHAR2,
59 p_settlement_date DATE,
60 p_settlement_amount NUMBER,
61 p_settlement_acctd_amount NUMBER,
62 p_status_code VARCHAR2,
63 p_attribute_category VARCHAR2,
64 p_attribute1 VARCHAR2,
65 p_attribute2 VARCHAR2,
66 p_attribute3 VARCHAR2,
67 p_attribute4 VARCHAR2,
68 p_attribute5 VARCHAR2,
69 p_attribute6 VARCHAR2,
70 p_attribute7 VARCHAR2,
71 p_attribute8 VARCHAR2,
72 p_attribute9 VARCHAR2,
73 p_attribute10 VARCHAR2,
74 p_attribute11 VARCHAR2,
75 p_attribute12 VARCHAR2,
76 p_attribute13 VARCHAR2,
77 p_attribute14 VARCHAR2,
78 p_attribute15 VARCHAR2,
79 px_org_id IN OUT NOCOPY NUMBER,
80 p_payment_reference_id NUMBER,
81 p_payment_reference_number VARCHAR2,
82 p_payment_status VARCHAR2,
83 p_group_claim_id NUMBER,
84 p_gl_date DATE,
85 p_wo_rec_trx_id NUMBER
86 )
87
88 IS
89 x_rowid VARCHAR2(30);
90
91
92 BEGIN
93
94 -- R12 Enhancements
95 /* IF (px_org_id IS NULL OR px_org_id = FND_API.G_MISS_NUM) THEN
96 SELECT NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99)
97 INTO px_org_id
98 FROM DUAL;
99 END IF; */
100
101
102 px_object_version_number := 1;
103
104
105 INSERT INTO OZF_SETTLEMENT_DOCS_ALL(
106 settlement_doc_id,
107 object_version_number,
108 last_update_date,
109 last_updated_by,
110 creation_date,
111 created_by,
112 last_update_login,
113 request_id,
114 program_application_id,
115 program_update_date,
116 program_id,
117 created_from,
118 claim_id,
119 claim_line_id,
120 payment_method,
121 settlement_id,
122 settlement_type,
123 settlement_type_id,
124 settlement_number,
125 settlement_date,
126 settlement_amount,
127 settlement_acctd_amount,
128 status_code,
129 attribute_category,
130 attribute1,
131 attribute2,
132 attribute3,
133 attribute4,
134 attribute5,
135 attribute6,
136 attribute7,
137 attribute8,
138 attribute9,
139 attribute10,
140 attribute11,
141 attribute12,
142 attribute13,
143 attribute14,
144 attribute15,
145 org_id,
146 payment_reference_id,
147 payment_reference_number,
148 payment_status,
149 group_claim_id,
150 gl_date,
151 wo_rec_trx_id
152 ) VALUES (
153 px_settlement_doc_id,
154 px_object_version_number,
155 p_last_update_date,
156 p_last_updated_by,
157 p_creation_date,
158 p_created_by,
159 p_last_update_login,
160 p_request_id,
161 p_program_application_id,
162 p_program_update_date,
163 p_program_id,
164 p_created_from,
165 p_claim_id,
166 p_claim_line_id,
167 p_payment_method,
168 p_settlement_id,
169 p_settlement_type,
170 p_settlement_type_id,
171 p_settlement_number,
172 p_settlement_date,
173 p_settlement_amount,
174 p_settlement_acctd_amount,
175 p_status_code,
176 p_attribute_category,
177 p_attribute1,
178 p_attribute2,
179 p_attribute3,
180 p_attribute4,
181 p_attribute5,
182 p_attribute6,
183 p_attribute7,
184 p_attribute8,
185 p_attribute9,
186 p_attribute10,
187 p_attribute11,
188 p_attribute12,
189 p_attribute13,
190 p_attribute14,
191 p_attribute15,
192 px_org_id,
193 p_payment_reference_id,
194 p_payment_reference_number,
195 p_payment_status,
196 p_group_claim_id,
197 p_gl_date,
198 p_wo_rec_trx_id
199 );
200 END Insert_Row;
201
202
203 ----------------------------------------------------------
204 ---- MEDIA ----
205 ----------------------------------------------------------
206
207 -- ========================================================
208 --
209 -- NAME
210 -- createUpdateBody
211 --
212 -- PURPOSE
213 --
214 -- NOTES
215 --
216 -- HISTORY
217 --
218 -- ========================================================
219 PROCEDURE Update_Row(
220 p_settlement_doc_id NUMBER,
221 p_object_version_number NUMBER,
222 p_last_update_date DATE,
223 p_last_updated_by NUMBER,
224 p_last_update_login NUMBER,
225 p_request_id NUMBER,
226 p_program_application_id NUMBER,
227 p_program_update_date DATE,
228 p_program_id NUMBER,
229 p_created_from VARCHAR2,
230 p_claim_id NUMBER,
231 p_claim_line_id NUMBER,
232 p_payment_method VARCHAR2,
233 p_settlement_id NUMBER,
234 p_settlement_type VARCHAR2,
235 p_settlement_type_id NUMBER,
236 p_settlement_number VARCHAR2,
237 p_settlement_date DATE,
238 p_settlement_amount NUMBER,
239 p_settlement_acctd_amount NUMBER,
240 p_status_code VARCHAR2,
241 p_attribute_category VARCHAR2,
242 p_attribute1 VARCHAR2,
243 p_attribute2 VARCHAR2,
244 p_attribute3 VARCHAR2,
245 p_attribute4 VARCHAR2,
246 p_attribute5 VARCHAR2,
247 p_attribute6 VARCHAR2,
248 p_attribute7 VARCHAR2,
249 p_attribute8 VARCHAR2,
250 p_attribute9 VARCHAR2,
251 p_attribute10 VARCHAR2,
252 p_attribute11 VARCHAR2,
253 p_attribute12 VARCHAR2,
254 p_attribute13 VARCHAR2,
255 p_attribute14 VARCHAR2,
256 p_attribute15 VARCHAR2,
257 p_org_id NUMBER,
258 p_payment_reference_id NUMBER,
259 p_payment_reference_number VARCHAR2,
260 p_payment_status VARCHAR2,
261 p_group_claim_id NUMBER,
262 p_gl_date DATE,
263 p_wo_rec_trx_id NUMBER
264 )
265
266 IS
267 BEGIN
268 Update OZF_SETTLEMENT_DOCS_ALL
269 SET
270 settlement_doc_id = p_settlement_doc_id,
271 object_version_number = p_object_version_number,
272 last_update_date = p_last_update_date,
273 last_updated_by = p_last_updated_by,
274 last_update_login = p_last_update_login,
275 request_id = p_request_id,
276 program_application_id = p_program_application_id,
277 program_update_date = p_program_update_date,
278 program_id = p_program_id,
279 created_from = p_created_from,
280 claim_id = p_claim_id,
281 claim_line_id = p_claim_line_id,
282 payment_method = p_payment_method,
283 settlement_id = p_settlement_id,
284 settlement_type = p_settlement_type,
285 settlement_type_id = p_settlement_type_id,
286 settlement_number = p_settlement_number,
287 settlement_date = p_settlement_date,
288 settlement_amount = p_settlement_amount,
289 settlement_acctd_amount = p_settlement_acctd_amount,
290 status_code = p_status_code,
291 attribute_category = p_attribute_category,
292 attribute1 = p_attribute1,
293 attribute2 = p_attribute2,
294 attribute3 = p_attribute3,
295 attribute4 = p_attribute4,
296 attribute5 = p_attribute5,
297 attribute6 = p_attribute6,
298 attribute7 = p_attribute7,
299 attribute8 = p_attribute8,
300 attribute9 = p_attribute9,
301 attribute10 = p_attribute10,
302 attribute11 = p_attribute11,
303 attribute12 = p_attribute12,
304 attribute13 = p_attribute13,
305 attribute14 = p_attribute14,
306 attribute15 = p_attribute15,
307 org_id = p_org_id,
308 payment_reference_id = p_payment_reference_id,
309 payment_reference_number = p_payment_reference_number,
310 payment_status = p_payment_status,
311 group_claim_id = p_group_claim_id,
312 gl_date = p_gl_date,
313 wo_rec_trx_id = p_wo_rec_trx_id
314 WHERE SETTLEMENT_DOC_ID = p_SETTLEMENT_DOC_ID;
315
316 IF (SQL%NOTFOUND) THEN
317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
318 END IF;
319 END Update_Row;
320
321
322 ----------------------------------------------------------
323 ---- MEDIA ----
324 ----------------------------------------------------------
325
326 -- ========================================================
327 --
328 -- NAME
329 -- createDeleteBody
330 --
331 -- PURPOSE
332 --
333 -- NOTES
334 --
335 -- HISTORY
336 --
337 -- ========================================================
338 PROCEDURE Delete_Row(
339 p_SETTLEMENT_DOC_ID NUMBER)
340 IS
341 BEGIN
342 DELETE FROM OZF_SETTLEMENT_DOCS_ALL
343 WHERE SETTLEMENT_DOC_ID = p_SETTLEMENT_DOC_ID;
344 If (SQL%NOTFOUND) then
345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346 End If;
347 END Delete_Row ;
348
349
350
351 ----------------------------------------------------------
352 ---- MEDIA ----
353 ----------------------------------------------------------
354
355 -- ========================================================
356 --
357 -- NAME
358 -- createLockBody
359 --
360 -- PURPOSE
361 --
362 -- NOTES
363 --
364 -- HISTORY
365 --
366 -- ========================================================
367 PROCEDURE Lock_Row(
368 p_settlement_doc_id NUMBER,
369 p_object_version_number NUMBER,
370 p_last_update_date DATE,
371 p_last_updated_by NUMBER,
372 p_creation_date DATE,
373 p_created_by NUMBER,
374 p_last_update_login NUMBER,
375 p_request_id NUMBER,
376 p_program_application_id NUMBER,
377 p_program_update_date DATE,
378 p_program_id NUMBER,
379 p_created_from VARCHAR2,
380 p_claim_id NUMBER,
381 p_claim_line_id NUMBER,
382 p_payment_method VARCHAR2,
383 p_settlement_id NUMBER,
384 p_settlement_type VARCHAR2,
385 p_settlement_type_id NUMBER,
386 p_settlement_number VARCHAR2,
387 p_settlement_date DATE,
388 p_settlement_amount NUMBER,
389 p_settlement_acctd_amount NUMBER,
390 p_status_code VARCHAR2,
391 p_attribute_category VARCHAR2,
392 p_attribute1 VARCHAR2,
393 p_attribute2 VARCHAR2,
394 p_attribute3 VARCHAR2,
395 p_attribute4 VARCHAR2,
396 p_attribute5 VARCHAR2,
397 p_attribute6 VARCHAR2,
398 p_attribute7 VARCHAR2,
399 p_attribute8 VARCHAR2,
400 p_attribute9 VARCHAR2,
401 p_attribute10 VARCHAR2,
402 p_attribute11 VARCHAR2,
403 p_attribute12 VARCHAR2,
404 p_attribute13 VARCHAR2,
405 p_attribute14 VARCHAR2,
406 p_attribute15 VARCHAR2,
407 p_org_id NUMBER
408 )
409
410 IS
411 CURSOR C IS
412 SELECT *
413 FROM OZF_SETTLEMENT_DOCS_ALL
414 WHERE SETTLEMENT_DOC_ID = p_SETTLEMENT_DOC_ID
415 FOR UPDATE of SETTLEMENT_DOC_ID NOWAIT;
416 Recinfo C%ROWTYPE;
417 BEGIN
418 OPEN c;
419 FETCH c INTO Recinfo;
420 If (c%NOTFOUND) then
421 CLOSE c;
422 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
423 APP_EXCEPTION.RAISE_EXCEPTION;
424 END IF;
425 CLOSE C;
426 IF (
427 ( Recinfo.settlement_doc_id = p_settlement_doc_id)
428 AND ( ( Recinfo.object_version_number = p_object_version_number)
429 OR ( ( Recinfo.object_version_number IS NULL )
430 AND ( p_object_version_number IS NULL )))
431 AND ( ( Recinfo.last_update_date = p_last_update_date)
432 OR ( ( Recinfo.last_update_date IS NULL )
433 AND ( p_last_update_date IS NULL )))
434 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
435 OR ( ( Recinfo.last_updated_by IS NULL )
436 AND ( p_last_updated_by IS NULL )))
440 AND ( ( Recinfo.created_by = p_created_by)
437 AND ( ( Recinfo.creation_date = p_creation_date)
438 OR ( ( Recinfo.creation_date IS NULL )
439 AND ( p_creation_date IS NULL )))
441 OR ( ( Recinfo.created_by IS NULL )
442 AND ( p_created_by IS NULL )))
443 AND ( ( Recinfo.last_update_login = p_last_update_login)
444 OR ( ( Recinfo.last_update_login IS NULL )
445 AND ( p_last_update_login IS NULL )))
446 AND ( ( Recinfo.request_id = p_request_id)
447 OR ( ( Recinfo.request_id IS NULL )
448 AND ( p_request_id IS NULL )))
449 AND ( ( Recinfo.program_application_id = p_program_application_id)
450 OR ( ( Recinfo.program_application_id IS NULL )
451 AND ( p_program_application_id IS NULL )))
452 AND ( ( Recinfo.program_update_date = p_program_update_date)
453 OR ( ( Recinfo.program_update_date IS NULL )
454 AND ( p_program_update_date IS NULL )))
455 AND ( ( Recinfo.program_id = p_program_id)
456 OR ( ( Recinfo.program_id IS NULL )
457 AND ( p_program_id IS NULL )))
458 AND ( ( Recinfo.created_from = p_created_from)
459 OR ( ( Recinfo.created_from IS NULL )
460 AND ( p_created_from IS NULL )))
461 AND ( ( Recinfo.claim_id = p_claim_id)
462 OR ( ( Recinfo.claim_id IS NULL )
463 AND ( p_claim_id IS NULL )))
464 AND ( ( Recinfo.claim_line_id = p_claim_line_id)
465 OR ( ( Recinfo.claim_line_id IS NULL )
466 AND ( p_claim_line_id IS NULL )))
467 AND ( ( Recinfo.payment_method = p_payment_method)
468 OR ( ( Recinfo.payment_method IS NULL )
469 AND ( p_payment_method IS NULL )))
470 AND ( ( Recinfo.settlement_id = p_settlement_id)
471 OR ( ( Recinfo.settlement_id IS NULL )
472 AND ( p_settlement_id IS NULL )))
473 AND ( ( Recinfo.settlement_type = p_settlement_type)
474 OR ( ( Recinfo.settlement_type IS NULL )
475 AND ( p_settlement_type IS NULL )))
476 AND ( ( Recinfo.settlement_type_id = p_settlement_type_id)
477 OR ( ( Recinfo.settlement_type_id IS NULL )
478 AND ( p_settlement_type_id IS NULL )))
479 AND ( ( Recinfo.settlement_number = p_settlement_number)
480 OR ( ( Recinfo.settlement_number IS NULL )
481 AND ( p_settlement_number IS NULL )))
482 AND ( ( Recinfo.settlement_date = p_settlement_date)
483 OR ( ( Recinfo.settlement_date IS NULL )
484 AND ( p_settlement_date IS NULL )))
485 AND ( ( Recinfo.settlement_amount = p_settlement_amount)
486 OR ( ( Recinfo.settlement_amount IS NULL )
487 AND ( p_settlement_amount IS NULL )))
488 AND ( ( Recinfo.settlement_acctd_amount = p_settlement_acctd_amount)
489 OR ( ( Recinfo.settlement_acctd_amount IS NULL )
490 AND ( p_settlement_amount IS NULL )))
491 AND ( ( Recinfo.status_code = p_status_code)
492 OR ( ( Recinfo.status_code IS NULL )
493 AND ( p_status_code IS NULL )))
494 AND ( ( Recinfo.attribute_category = p_attribute_category)
495 OR ( ( Recinfo.attribute_category IS NULL )
496 AND ( p_attribute_category IS NULL )))
497 AND ( ( Recinfo.attribute1 = p_attribute1)
498 OR ( ( Recinfo.attribute1 IS NULL )
499 AND ( p_attribute1 IS NULL )))
500 AND ( ( Recinfo.attribute2 = p_attribute2)
501 OR ( ( Recinfo.attribute2 IS NULL )
502 AND ( p_attribute2 IS NULL )))
503 AND ( ( Recinfo.attribute3 = p_attribute3)
504 OR ( ( Recinfo.attribute3 IS NULL )
505 AND ( p_attribute3 IS NULL )))
506 AND ( ( Recinfo.attribute4 = p_attribute4)
507 OR ( ( Recinfo.attribute4 IS NULL )
508 AND ( p_attribute4 IS NULL )))
509 AND ( ( Recinfo.attribute5 = p_attribute5)
510 OR ( ( Recinfo.attribute5 IS NULL )
511 AND ( p_attribute5 IS NULL )))
512 AND ( ( Recinfo.attribute6 = p_attribute6)
513 OR ( ( Recinfo.attribute6 IS NULL )
514 AND ( p_attribute6 IS NULL )))
515 AND ( ( Recinfo.attribute7 = p_attribute7)
516 OR ( ( Recinfo.attribute7 IS NULL )
517 AND ( p_attribute7 IS NULL )))
518 AND ( ( Recinfo.attribute8 = p_attribute8)
519 OR ( ( Recinfo.attribute8 IS NULL )
520 AND ( p_attribute8 IS NULL )))
521 AND ( ( Recinfo.attribute9 = p_attribute9)
522 OR ( ( Recinfo.attribute9 IS NULL )
523 AND ( p_attribute9 IS NULL )))
524 AND ( ( Recinfo.attribute10 = p_attribute10)
525 OR ( ( Recinfo.attribute10 IS NULL )
526 AND ( p_attribute10 IS NULL )))
527 AND ( ( Recinfo.attribute11 = p_attribute11)
528 OR ( ( Recinfo.attribute11 IS NULL )
529 AND ( p_attribute11 IS NULL )))
530 AND ( ( Recinfo.attribute12 = p_attribute12)
531 OR ( ( Recinfo.attribute12 IS NULL )
532 AND ( p_attribute12 IS NULL )))
533 AND ( ( Recinfo.attribute13 = p_attribute13)
534 OR ( ( Recinfo.attribute13 IS NULL )
535 AND ( p_attribute13 IS NULL )))
536 AND ( ( Recinfo.attribute14 = p_attribute14)
537 OR ( ( Recinfo.attribute14 IS NULL )
538 AND ( p_attribute14 IS NULL )))
539 AND ( ( Recinfo.attribute15 = p_attribute15)
540 OR ( ( Recinfo.attribute15 IS NULL )
541 AND ( p_attribute15 IS NULL )))
542 AND ( ( Recinfo.org_id = p_org_id)
543 OR ( ( Recinfo.org_id IS NULL )
544 AND ( p_org_id IS NULL )))
545 ) THEN
546 RETURN;
547 ELSE
548 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
549 APP_EXCEPTION.RAISE_EXCEPTION;
550 END IF;
551 END Lock_Row;
552
553 END OZF_SETTLEMENT_DOCS_PKG;