[Home] [Help]
PACKAGE BODY: APPS.PO_NOTIFICATION_CTRL_DRAFT_PKG
Source
1 PACKAGE BODY PO_NOTIFICATION_CTRL_DRAFT_PKG AS
2 /* $Header: PO_NOTIFICATION_CTRL_DRAFT_PKG.plb 120.5 2006/09/28 23:04:57 bao noship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_NOTIFICATION_CTRL_DRAFT_PKG');
6
7 -----------------------------------------------------------------------
8 --Start of Comments
9 --Name: delete_rows
10 --Pre-reqs: None
11 --Modifies:
12 --Locks:
13 -- None
14 --Function:
15 -- Deletes drafts for notification controls based on the information given
16 -- If only draft_id is provided, then all notification ctrls for the draft
17 -- will be deleted
18 -- If notification_id is also provided, then the one record that has such
19 -- primary key will be deleted
20 --Parameters:
21 --IN:
22 --p_draft_id
23 -- draft unique identifier
24 --p_notification_id
25 -- notification control unique identifier
26 --IN OUT:
27 --OUT:
28 --Returns:
29 --Notes:
30 --Testing:
31 --End of Comments
32 ------------------------------------------------------------------------
33 PROCEDURE delete_rows
34 ( p_draft_id IN NUMBER,
35 p_notification_id IN NUMBER
36 ) IS
37 d_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
38 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
39 d_position NUMBER;
40
41 BEGIN
42
43 d_position := 0;
44 IF (PO_LOG.d_proc) THEN
45 PO_LOG.proc_begin(d_module);
46 END IF;
47
48 DELETE FROM po_notification_ctrl_draft
49 WHERE draft_id = p_draft_id
50 AND notification_id = NVL(p_notification_id, notification_id);
51
52 d_position := 10;
53 EXCEPTION
54 WHEN OTHERS THEN
55 PO_MESSAGE_S.add_exc_msg
56 ( p_pkg_name => d_pkg_name,
57 p_procedure_name => d_api_name || '.' || d_position
58 );
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END delete_rows;
61
62
63 -----------------------------------------------------------------------
64 --Start of Comments
65 --Name: sync_draft_from_txn
66 --Pre-reqs: None
67 --Modifies:
68 --Locks:
69 -- None
70 --Function:
71 -- Copy data from transaction table to draft table, if the corresponding
72 -- record in draft table does not exist. It also sets the delete flag of
73 -- the draft record according to the parameter.
74 --Parameters:
75 --IN:
76 --p_notification_id_tbl
77 -- table of po notification controls unique identifier
78 --p_draft_id_tbl
79 -- table of draft ids this sync up will be done for
80 --p_delete_flag_tbl
81 -- table fo flags to indicate whether the draft record should be maked as
82 -- "to be deleted"
83 --IN OUT:
84 --OUT:
85 --x_record_already_exist_tbl
86 -- Returns whether the record was already in draft table or not
87 --Returns:
88 --Notes:
89 --Testing:
90 --End of Comments
91 ------------------------------------------------------------------------
92 PROCEDURE sync_draft_from_txn
93 ( p_notification_id_tbl IN PO_TBL_NUMBER,
94 p_draft_id_tbl IN PO_TBL_NUMBER,
95 p_delete_flag_tbl IN PO_TBL_VARCHAR1,
96 x_record_already_exist_tbl OUT NOCOPY PO_TBL_VARCHAR1
97 ) IS
98
99 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
100 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
101 d_position NUMBER;
102
103 l_distinct_id_list DBMS_SQL.NUMBER_TABLE;
104 l_duplicate_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
105
106 BEGIN
107 d_position := 0;
108 IF (PO_LOG.d_proc) THEN
109 PO_LOG.proc_begin(d_module);
110 END IF;
111
112 x_record_already_exist_tbl :=
113 PO_NOTIFICATION_CTRL_DRAFT_PVT.draft_changes_exist
114 ( p_draft_id_tbl => p_draft_id_tbl,
115 p_notification_id_tbl => p_notification_id_tbl
116 );
117
118 -- bug5471513 START
119 -- If there're duplicate entries in the id table,
120 -- we do not want to insert multiple entries
121 -- Created an associative array to store what id has appeared.
122 l_duplicate_flag_tbl.EXTEND(p_notification_id_tbl.COUNT);
123
124 FOR i IN 1..p_notification_id_tbl.COUNT LOOP
125 IF (x_record_already_exist_tbl(i) = FND_API.G_FALSE) THEN
126
127 IF (l_distinct_id_list.EXISTS(p_notification_id_tbl(i))) THEN
128
129 l_duplicate_flag_tbl(i) := FND_API.G_TRUE;
130 ELSE
131 l_duplicate_flag_tbl(i) := FND_API.G_FALSE;
132
133 l_distinct_id_list(p_notification_id_tbl(i)) := 1;
134 END IF;
135
136 ELSE
137
138 l_duplicate_flag_tbl(i) := NULL;
139
140 END IF;
141 END LOOP;
142 -- bug5471513 END
143
144 d_position := 10;
145 IF (PO_LOG.d_stmt) THEN
146 PO_LOG.stmt(d_module, d_position, 'transfer records from txn to dft');
147 END IF;
148
149 FORALL i IN 1..p_notification_id_tbl.COUNT
150 INSERT INTO po_notification_ctrl_draft
151 (
152 draft_id,
153 delete_flag,
154 change_accepted_flag,
155 notification_id,
156 po_header_id,
157 start_date_active,
158 end_date_active,
159 notification_amount,
160 notification_condition_code,
161 notification_qty_percentage,
162 last_update_date,
163 last_update_login,
164 last_updated_by,
165 program_id,
166 program_application_id,
167 program_update_date,
168 request_id,
169 created_by,
170 creation_date,
171 attribute_category,
172 attribute1,
173 attribute10,
174 attribute11,
175 attribute12,
176 attribute13,
177 attribute14,
178 attribute15,
179 attribute2,
180 attribute3,
181 attribute4,
182 attribute5,
183 attribute6,
184 attribute7,
185 attribute8,
186 attribute9
187 )
188 SELECT
189 p_draft_id_tbl(i),
190 p_delete_flag_tbl(i),
191 NULL,
192 notification_id,
193 po_header_id,
194 start_date_active,
195 end_date_active,
196 notification_amount,
197 notification_condition_code,
198 notification_qty_percentage,
199 last_update_date,
200 last_update_login,
201 last_updated_by,
202 program_id,
203 program_application_id,
204 program_update_date,
205 request_id,
206 created_by,
207 creation_date,
208 attribute_category,
209 attribute1,
210 attribute10,
211 attribute11,
212 attribute12,
213 attribute13,
214 attribute14,
215 attribute15,
216 attribute2,
217 attribute3,
218 attribute4,
219 attribute5,
220 attribute6,
221 attribute7,
222 attribute8,
223 attribute9
224 FROM po_notification_controls
225 WHERE notification_id = p_notification_id_tbl(i)
226 AND x_record_already_exist_tbl(i) = FND_API.G_FALSE
227 AND l_duplicate_flag_tbl(i) = FND_API.G_FALSE;
228
229 d_position := 20;
230 IF (PO_LOG.d_stmt) THEN
231 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
232 END IF;
233
234 FORALL i IN 1..p_notification_id_tbl.COUNT
235 UPDATE po_notification_ctrl_draft
236 SET delete_flag = p_delete_flag_tbl(i)
237 WHERE notification_id = p_notification_id_tbl(i)
238 AND draft_id = p_draft_id_tbl(i)
239 AND NVL(delete_flag, 'N') <> 'Y' -- bug5570989
240 AND x_record_already_exist_tbl(i) = FND_API.G_TRUE;
241
242 d_position := 30;
243
244 IF (PO_LOG.d_stmt) THEN
245 PO_LOG.stmt(d_module, d_position, 'update draft records that are already' ||
246 ' in draft table. Count = ' || SQL%ROWCOUNT);
247 END IF;
248
249 d_position := 40;
250
251 IF (PO_LOG.d_proc) THEN
252 PO_LOG.proc_end(d_module);
253 END IF;
254
255 EXCEPTION
256 WHEN OTHERS THEN
257 PO_MESSAGE_S.add_exc_msg
258 ( p_pkg_name => d_pkg_name,
259 p_procedure_name => d_api_name || '.' || d_position
260 );
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262 END sync_draft_from_txn;
263
264 -----------------------------------------------------------------------
265 --Start of Comments
266 --Name: sync_draft_from_txn
267 --Pre-reqs: None
268 --Modifies:
269 --Locks:
270 -- None
271 --Function:
272 -- Same functionality as the bulk version of this procedure
273 --Parameters:
274 --IN:
275 --p_notification_id
276 -- notification control unique identifier
277 --p_draft_id
278 -- the draft this sync up will be done for
279 --p_delete_flag
280 -- flag to indicate whether the draft record should be maked as "to be
281 -- deleted"
282 --IN OUT:
283 --OUT:
284 --x_record_already_exist
285 -- Returns whether the record was already in draft table or not
286 --Returns:
287 --Notes:
288 --Testing:
289 --End of Comments
290 ------------------------------------------------------------------------
291 PROCEDURE sync_draft_from_txn
292 ( p_notification_id IN NUMBER,
293 p_draft_id IN NUMBER,
294 p_delete_flag IN VARCHAR2,
295 x_record_already_exist OUT NOCOPY VARCHAR2
296 ) IS
297
298 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
299 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
300 d_position NUMBER;
301
302 l_record_already_exist_tbl PO_TBL_VARCHAR1;
303
304 BEGIN
305 d_position := 0;
306 IF (PO_LOG.d_proc) THEN
307 PO_LOG.proc_begin(d_module);
308 PO_LOG.proc_begin(d_module, 'p_notification_id', p_notification_id);
309 END IF;
310
311 sync_draft_from_txn
312 ( p_notification_id_tbl => PO_TBL_NUMBER(p_notification_id),
313 p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
314 p_delete_flag_tbl => PO_TBL_VARCHAR1(p_delete_flag),
315 x_record_already_exist_tbl => l_record_already_exist_tbl
316 );
317
318 x_record_already_exist := l_record_already_exist_tbl(1);
319
320 d_position := 10;
321 IF (PO_LOG.d_proc) THEN
322 PO_LOG.proc_end(d_module);
323 PO_LOG.proc_end(d_module, 'x_record_already_exist', x_record_already_exist);
324 END IF;
325
326 EXCEPTION
327 WHEN OTHERS THEN
328 PO_MESSAGE_S.add_exc_msg
329 ( p_pkg_name => d_pkg_name,
330 p_procedure_name => d_api_name || '.' || d_position
331 );
332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
333 END sync_draft_from_txn;
334
335
336 -----------------------------------------------------------------------
337 --Start of Comments
338 --Name: merge_changes
339 --Pre-reqs: None
340 --Modifies:
341 --Locks:
342 -- None
343 --Function:
344 -- Merge the records in draft table to transaction table
345 -- Either insert, update or delete will be performed on top of transaction
346 -- table, depending on the delete_flag on the draft record and whether the
347 -- record already exists in transaction table
348 --
349 --Parameters:
350 --IN:
351 --p_draft_id
352 -- draft unique identifier
353 --IN OUT:
354 --OUT:
355 --Returns:
356 --Notes:
357 --Testing:
358 --End of Comments
359 ------------------------------------------------------------------------
360 PROCEDURE merge_changes
361 ( p_draft_id IN NUMBER
362 ) IS
363
364 d_api_name CONSTANT VARCHAR2(30) := 'merge_changes';
365 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
366 d_position NUMBER;
367
368 BEGIN
369
370 d_position := 0;
371 IF (PO_LOG.d_proc) THEN
372 PO_LOG.proc_begin(d_module);
373 END IF;
374
375 -- Since putting DELETE within MERGE statement is causing database
376 -- to thrown internal error, for now we just separate the DELETE statement.
377 -- Once this is fixed we'll move the delete statement back to the merge
378 -- statement
379
380 -- bug5187544
381 -- Delete only records that have not been rejected
382
383 DELETE FROM po_notification_controls PNC
384 WHERE PNC.notification_id IN
385 ( SELECT PNCD.notification_id
386 FROM po_notification_ctrl_draft PNCD
387 WHERE PNCD.draft_id = p_draft_id
388 AND PNCD.delete_flag = 'Y'
389 AND NVL(PNCD.change_accepted_flag, 'Y') = 'Y' );
390
391 d_position := 10;
392
393 -- Merge PO notification control changes
394 -- For update case, the following columns will be skipped:
395 --PNC.notification_id
396 --PNC.program_id
397 --PNC.program_application_id
398 --PNC.program_update_date
399 --PNC.request_id
400 --PNC.created_by
401 --PNC.creation_date
402 MERGE INTO po_notification_controls PNC
403 USING (
404 SELECT
405 PNCD.draft_id,
406 PNCD.delete_flag,
407 PNCD.change_accepted_flag,
408 PNCD.notification_id,
409 PNCD.po_header_id,
410 PNCD.start_date_active,
411 PNCD.end_date_active,
412 PNCD.notification_amount,
413 PNCD.notification_condition_code,
414 PNCD.notification_qty_percentage,
415 PNCD.last_update_date,
416 PNCD.last_update_login,
417 PNCD.last_updated_by,
418 PNCD.program_id,
419 PNCD.program_application_id,
420 PNCD.program_update_date,
421 PNCD.request_id,
422 PNCD.created_by,
423 PNCD.creation_date,
424 PNCD.attribute_category,
425 PNCD.attribute1,
426 PNCD.attribute10,
427 PNCD.attribute11,
428 PNCD.attribute12,
429 PNCD.attribute13,
430 PNCD.attribute14,
431 PNCD.attribute15,
432 PNCD.attribute2,
433 PNCD.attribute3,
434 PNCD.attribute4,
435 PNCD.attribute5,
436 PNCD.attribute6,
437 PNCD.attribute7,
438 PNCD.attribute8,
439 PNCD.attribute9
440 FROM po_notification_ctrl_draft PNCD
441 WHERE PNCD.draft_id = p_draft_id
442 AND NVL(PNCD.change_accepted_flag, 'Y') = 'Y'
443 ) PNCDV
444 ON (PNC.notification_id = PNCDV.notification_id)
445 WHEN MATCHED THEN
446 UPDATE
447 SET
448 PNC.po_header_id = PNCDV.po_header_id,
449 PNC.start_date_active = PNCDV.start_date_active,
450 PNC.end_date_active = PNCDV.end_date_active,
451 PNC.notification_amount = PNCDV.notification_amount,
452 PNC.notification_condition_code = PNCDV.notification_condition_code,
453 PNC.notification_qty_percentage = PNCDV.notification_qty_percentage,
454 PNC.last_update_date = PNCDV.last_update_date,
455 PNC.last_update_login = PNCDV.last_update_login,
456 PNC.last_updated_by = PNCDV.last_updated_by,
457 PNC.attribute_category = PNCDV.attribute_category,
458 PNC.attribute1 = PNCDV.attribute1,
459 PNC.attribute10 = PNCDV.attribute10,
460 PNC.attribute11 = PNCDV.attribute11,
461 PNC.attribute12 = PNCDV.attribute12,
462 PNC.attribute13 = PNCDV.attribute13,
463 PNC.attribute14 = PNCDV.attribute14,
464 PNC.attribute15 = PNCDV.attribute15,
465 PNC.attribute2 = PNCDV.attribute2,
466 PNC.attribute3 = PNCDV.attribute3,
467 PNC.attribute4 = PNCDV.attribute4,
468 PNC.attribute5 = PNCDV.attribute5,
469 PNC.attribute6 = PNCDV.attribute6,
470 PNC.attribute7 = PNCDV.attribute7,
471 PNC.attribute8 = PNCDV.attribute8,
472 PNC.attribute9 = PNCDV.attribute9
473 -- DELETE WHERE PNCDV.delete_flag = 'Y'
474 WHEN NOT MATCHED THEN
475 INSERT
476 (
477 PNC.notification_id,
478 PNC.po_header_id,
479 PNC.start_date_active,
480 PNC.end_date_active,
481 PNC.notification_amount,
482 PNC.notification_condition_code,
483 PNC.notification_qty_percentage,
484 PNC.last_update_date,
485 PNC.last_update_login,
486 PNC.last_updated_by,
487 PNC.program_id,
488 PNC.program_application_id,
489 PNC.program_update_date,
490 PNC.request_id,
491 PNC.created_by,
492 PNC.creation_date,
493 PNC.attribute_category,
494 PNC.attribute1,
495 PNC.attribute10,
496 PNC.attribute11,
497 PNC.attribute12,
498 PNC.attribute13,
499 PNC.attribute14,
500 PNC.attribute15,
501 PNC.attribute2,
502 PNC.attribute3,
503 PNC.attribute4,
504 PNC.attribute5,
505 PNC.attribute6,
506 PNC.attribute7,
507 PNC.attribute8,
508 PNC.attribute9
509 )
510 VALUES
511 (
512 PNCDV.notification_id,
513 PNCDV.po_header_id,
514 PNCDV.start_date_active,
515 PNCDV.end_date_active,
516 PNCDV.notification_amount,
517 PNCDV.notification_condition_code,
518 PNCDV.notification_qty_percentage,
519 PNCDV.last_update_date,
520 PNCDV.last_update_login,
521 PNCDV.last_updated_by,
522 PNCDV.program_id,
523 PNCDV.program_application_id,
524 PNCDV.program_update_date,
525 PNCDV.request_id,
526 PNCDV.created_by,
527 PNCDV.creation_date,
528 PNCDV.attribute_category,
529 PNCDV.attribute1,
530 PNCDV.attribute10,
531 PNCDV.attribute11,
532 PNCDV.attribute12,
533 PNCDV.attribute13,
534 PNCDV.attribute14,
535 PNCDV.attribute15,
536 PNCDV.attribute2,
537 PNCDV.attribute3,
538 PNCDV.attribute4,
539 PNCDV.attribute5,
540 PNCDV.attribute6,
541 PNCDV.attribute7,
542 PNCDV.attribute8,
543 PNCDV.attribute9
544 ) WHERE NVL(PNCDV.delete_flag, 'N') <> 'Y';
545
546 d_position := 10;
547 EXCEPTION
548 WHEN OTHERS THEN
549 PO_MESSAGE_S.add_exc_msg
550 ( p_pkg_name => d_pkg_name,
551 p_procedure_name => d_api_name || '.' || d_position
552 );
553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
554 END merge_changes;
555
556 -----------------------------------------------------------------------
557 --Start of Comments
558 --Name: lock_draft_record
559 --Function:
560 -- Obtain database lock for the record in draft table
561 --Parameters:
562 --IN:
563 --p_notification_id
564 -- id for po notification control record
565 --p_draft_id
566 -- draft unique identifier
567 --RETURN:
568 --End of Comments
569 ------------------------------------------------------------------------
570 PROCEDURE lock_draft_record
571 ( p_notification_id IN NUMBER,
572 p_draft_id IN NUMBER
573 ) IS
574
575 d_api_name CONSTANT VARCHAR2(30) := 'lock_draft_record';
576 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
577 d_position NUMBER;
578
579 l_dummy NUMBER;
580
581 BEGIN
582 d_position := 0;
583 IF (PO_LOG.d_proc) THEN
584 PO_LOG.proc_begin(d_module);
585 END IF;
586
587 SELECT 1
588 INTO l_dummy
589 FROM po_notification_ctrl_draft
590 WHERE notification_id = p_notification_id
591 AND draft_id = p_draft_id
592 FOR UPDATE NOWAIT;
593
594 IF (PO_LOG.d_proc) THEN
595 PO_LOG.proc_end(d_module);
596 END IF;
597
598 EXCEPTION
599 WHEN NO_DATA_FOUND THEN
600 NULL;
601 END lock_draft_record;
602
603 -----------------------------------------------------------------------
604 --Start of Comments
605 --Name: lock_transaction_record
606 --Function:
607 -- Obtain database lock for the record in transaction table
608 --Parameters:
609 --IN:
610 --p_notification_id
611 -- id for po notification control record
612 --RETURN:
613 --End of Comments
614 ------------------------------------------------------------------------
615 PROCEDURE lock_transaction_record
616 ( p_notification_id IN NUMBER
617 ) IS
618
619 d_api_name CONSTANT VARCHAR2(30) := 'lock_transaction_record';
620 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
621 d_position NUMBER;
622
623 l_dummy NUMBER;
624
625 BEGIN
626 d_position := 0;
627 IF (PO_LOG.d_proc) THEN
628 PO_LOG.proc_begin(d_module);
629 END IF;
630
631 SELECT 1
632 INTO l_dummy
633 FROM po_notification_controls
634 WHERE notification_id = p_notification_id
635 FOR UPDATE NOWAIT;
636
637 IF (PO_LOG.d_proc) THEN
638 PO_LOG.proc_end(d_module);
639 END IF;
640
641 EXCEPTION
642 WHEN NO_DATA_FOUND THEN
643 NULL;
644 END lock_transaction_record;
645
646 END PO_NOTIFICATION_CTRL_DRAFT_PKG;