[Home] [Help]
PACKAGE BODY: APPS.PO_PRICE_DIFF_DRAFT_PKG
Source
1 PACKAGE BODY PO_PRICE_DIFF_DRAFT_PKG AS
2 /* $Header: PO_PRICE_DIFF_DRAFT_PKG.plb 120.5 2006/09/28 23:05:20 bao noship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_PRICE_DIFF_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 price differentials based on the information given
16 -- If only draft_id is provided, then all price diffs for the draft will be
17 -- deleted
18 -- If price_differential_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_price_differential_id
25 -- price differential 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_price_differential_id IN NUMBER
36 ) IS
37
38 d_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
39 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
40 d_position NUMBER;
41
42 BEGIN
43
44 d_position := 0;
45 IF (PO_LOG.d_proc) THEN
46 PO_LOG.proc_begin(d_module);
47 END IF;
48
49 DELETE FROM po_price_diff_draft
50 WHERE draft_id = p_draft_id
51 AND price_differential_id = NVL(p_price_differential_id,
52 price_differential_id);
53
54 d_position := 10;
55 EXCEPTION
56 WHEN OTHERS THEN
57 PO_MESSAGE_S.add_exc_msg
58 ( p_pkg_name => d_pkg_name,
59 p_procedure_name => d_api_name || '.' || d_position
60 );
61 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
62 END delete_rows;
63
64 -----------------------------------------------------------------------
65 --Start of Comments
66 --Name: sync_draft_from_txn
67 --Pre-reqs: None
68 --Modifies:
69 --Locks:
70 -- None
71 --Function:
72 -- Copy data from transaction table to draft table, if the corresponding
73 -- record in draft table does not exist. It also sets the delete flag of
74 -- the draft record according to the parameter.
75 --Parameters:
76 --IN:
77 --p_price_differential_id_tbl
78 -- table of po price differentials unique identifier
79 --p_draft_id_tbl
80 -- table of draft ids this sync up will be done for
81 --p_delete_flag_tbl
82 -- table fo flags to indicate whether the draft record should be maked as
83 -- "to be deleted"
84 --IN OUT:
85 --OUT:
86 --x_record_already_exist_tbl
87 -- Returns whether the record was already in draft table or not
88 --Returns:
89 --Notes:
90 --Testing:
91 --End of Comments
92 ------------------------------------------------------------------------
93 PROCEDURE sync_draft_from_txn
94 ( p_price_differential_id_tbl IN PO_TBL_NUMBER,
95 p_draft_id_tbl IN PO_TBL_NUMBER,
96 p_delete_flag_tbl IN PO_TBL_VARCHAR1,
97 x_record_already_exist_tbl OUT NOCOPY PO_TBL_VARCHAR1
98 ) IS
99
100 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
101 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
102 d_position NUMBER;
103
104 l_distinct_id_list DBMS_SQL.NUMBER_TABLE;
105 l_duplicate_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
106
107
108 BEGIN
109 d_position := 0;
110 IF (PO_LOG.d_proc) THEN
111 PO_LOG.proc_begin(d_module);
112 END IF;
113
114 x_record_already_exist_tbl :=
115 PO_PRICE_DIFF_DRAFT_PVT.draft_changes_exist
116 ( p_draft_id_tbl => p_draft_id_tbl,
117 p_price_differential_id_tbl => p_price_differential_id_tbl
118 );
119
120 -- bug5471513 START
121 -- If there're duplicate entries in the id table,
122 -- we do not want to insert multiple entries
123 -- Created an associative array to store what id has appeared.
124 l_duplicate_flag_tbl.EXTEND(p_price_differential_id_tbl.COUNT);
125
126 FOR i IN 1..p_price_differential_id_tbl.COUNT LOOP
127 IF (x_record_already_exist_tbl(i) = FND_API.G_FALSE) THEN
128
129 IF (l_distinct_id_list.EXISTS(p_price_differential_id_tbl(i))) THEN
130
131 l_duplicate_flag_tbl(i) := FND_API.G_TRUE;
132 ELSE
133 l_duplicate_flag_tbl(i) := FND_API.G_FALSE;
134
135 l_distinct_id_list(p_price_differential_id_tbl(i)) := 1;
136 END IF;
137
138 ELSE
139
140 l_duplicate_flag_tbl(i) := NULL;
141
142 END IF;
143 END LOOP;
144 -- bug5471513 END
145
146 d_position := 10;
147 IF (PO_LOG.d_stmt) THEN
148 PO_LOG.stmt(d_module, d_position, 'transfer records from txn to dft');
149 END IF;
150
151 FORALL i IN 1..p_price_differential_id_tbl.COUNT
152 INSERT INTO po_price_diff_draft
153 (
154 draft_id,
155 delete_flag,
156 change_accepted_flag,
157 price_differential_id,
158 price_differential_num,
159 entity_id,
160 entity_type,
161 price_type,
162 enabled_flag,
163 min_multiplier,
164 max_multiplier,
165 multiplier,
166 creation_date,
167 created_by,
168 last_update_date,
169 last_updated_by,
170 last_update_login
171 )
172 SELECT
173 p_draft_id_tbl(i),
174 p_delete_flag_tbl(i),
175 NULL,
176 price_differential_id,
177 price_differential_num,
178 entity_id,
179 entity_type,
180 price_type,
181 enabled_flag,
182 min_multiplier,
183 max_multiplier,
184 multiplier,
185 creation_date,
186 created_by,
187 last_update_date,
188 last_updated_by,
189 last_update_login
190 FROM po_price_differentials
191 WHERE price_differential_id = p_price_differential_id_tbl(i)
192 AND x_record_already_exist_tbl(i) = FND_API.G_FALSE
193 AND l_duplicate_flag_tbl(i) = FND_API.G_FALSE;
194
195 d_position := 20;
196 IF (PO_LOG.d_stmt) THEN
197 PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
198 END IF;
199
200 FORALL i IN 1..p_price_differential_id_tbl.COUNT
201 UPDATE po_price_diff_draft
202 SET delete_flag = p_delete_flag_tbl(i)
203 WHERE price_differential_id = p_price_differential_id_tbl(i)
204 AND draft_id = p_draft_id_tbl(i)
205 AND NVL(delete_flag, 'N') <> 'Y' -- bug5570989
206 AND x_record_already_exist_tbl(i) = FND_API.G_TRUE;
207
208 d_position := 30;
209
210 IF (PO_LOG.d_stmt) THEN
211 PO_LOG.stmt(d_module, d_position, 'update draft records that are already' ||
212 ' in draft table. Count = ' || SQL%ROWCOUNT);
213 END IF;
214
215 d_position := 40;
216
217 IF (PO_LOG.d_proc) THEN
218 PO_LOG.proc_end(d_module);
219 END IF;
220
221 EXCEPTION
222 WHEN OTHERS THEN
223 PO_MESSAGE_S.add_exc_msg
224 ( p_pkg_name => d_pkg_name,
225 p_procedure_name => d_api_name || '.' || d_position
226 );
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
228 END sync_draft_from_txn;
229
230 -----------------------------------------------------------------------
231 --Start of Comments
232 --Name: sync_draft_from_txn
233 --Pre-reqs: None
234 --Modifies:
235 --Locks:
236 -- None
237 --Function:
238 -- Same functionality as the bulk version of this procedure
239 --Parameters:
240 --IN:
241 --p_price_differential_id
242 -- price differential unique identifier
243 --p_draft_id
244 -- the draft this sync up will be done for
245 --p_delete_flag
246 -- flag to indicate whether the draft record should be maked as "to be
247 -- deleted"
248 --IN OUT:
249 --OUT:
250 --x_record_already_exist
251 -- Returns whether the record was already in draft table or not
252 --Returns:
253 --Notes:
254 --Testing:
255 --End of Comments
256 ------------------------------------------------------------------------
257 PROCEDURE sync_draft_from_txn
258 ( p_price_differential_id IN NUMBER,
259 p_draft_id IN NUMBER,
260 p_delete_flag IN VARCHAR2,
261 x_record_already_exist OUT NOCOPY VARCHAR2
262 ) IS
263
264 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
265 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
266 d_position NUMBER;
267
268 l_record_already_exist_tbl PO_TBL_VARCHAR1;
269
270 BEGIN
271 d_position := 0;
272 IF (PO_LOG.d_proc) THEN
273 PO_LOG.proc_begin(d_module);
274 PO_LOG.proc_begin(d_module, 'p_price_differential_id',
275 p_price_differential_id);
276 END IF;
277
278 sync_draft_from_txn
279 ( p_price_differential_id_tbl => PO_TBL_NUMBER(p_price_differential_id),
280 p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
281 p_delete_flag_tbl => PO_TBL_VARCHAR1(p_delete_flag),
282 x_record_already_exist_tbl => l_record_already_exist_tbl
283 );
284
285 x_record_already_exist := l_record_already_exist_tbl(1);
286
287 d_position := 10;
288 IF (PO_LOG.d_proc) THEN
289 PO_LOG.proc_end(d_module);
290 PO_LOG.proc_end(d_module, 'x_record_already_exist', x_record_already_exist);
291 END IF;
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 PO_MESSAGE_S.add_exc_msg
296 ( p_pkg_name => d_pkg_name,
297 p_procedure_name => d_api_name || '.' || d_position
298 );
299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
300 END sync_draft_from_txn;
301
302
303 -----------------------------------------------------------------------
304 --Start of Comments
305 --Name: merge_changes
306 --Pre-reqs: None
307 --Modifies:
308 --Locks:
309 -- None
310 --Function:
311 -- Merge the records in draft table to transaction table
312 -- Either insert, update or delete will be performed on top of transaction
313 -- table, depending on the delete_flag on the draft record and whether the
314 -- record already exists in transaction table
315 --
316 --Parameters:
317 --IN:
318 --p_draft_id
319 -- draft unique identifier
320 --IN OUT:
321 --OUT:
322 --Returns:
323 --Notes:
324 --Testing:
325 --End of Comments
326 ------------------------------------------------------------------------
327 PROCEDURE merge_changes
328 ( p_draft_id IN NUMBER
329 ) IS
330
331 d_api_name CONSTANT VARCHAR2(30) := 'merge_changes';
332 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
333 d_position NUMBER;
334
335 BEGIN
336
337 d_position := 0;
338 IF (PO_LOG.d_proc) THEN
339 PO_LOG.proc_begin(d_module);
340 END IF;
341
342 -- Since putting DELETE within MERGE statement is causing database
343 -- to thrown internal error, for now we just separate the DELETE statement.
344 -- Once this is fixed we'll move the delete statement back to the merge
345 -- statement
346
347 -- bug5187544
348 -- Delete only records that have not been rejected
349
350 DELETE FROM po_price_differentials PPD
351 WHERE PPD.price_differential_id IN
352 ( SELECT PPDD.price_differential_id
353 FROM po_price_diff_draft PPDD
354 WHERE PPDD.draft_id = p_draft_id
355 AND PPDD.delete_flag = 'Y'
356 AND NVL(PPDD.change_accepted_flag, 'Y') = 'Y');
357
358 d_position := 10;
359
360 -- Merge Price Differential changes
361 -- For update case, the following columns will be skipped:
362 --PPD.price_differential_id
363 --PPD.entity_id
364 --PPD.entity_type
365 --PPD.creation_date
366 --PPD.created_by
367 MERGE INTO po_price_differentials PPD
368 USING (
369 SELECT
370 PPDD.draft_id,
371 PPDD.delete_flag,
372 PPDD.change_accepted_flag,
373 PPDD.price_differential_id,
374 PPDD.price_differential_num,
375 PPDD.entity_id,
376 PPDD.entity_type,
377 PPDD.price_type,
378 PPDD.enabled_flag,
379 PPDD.min_multiplier,
380 PPDD.max_multiplier,
381 PPDD.multiplier,
382 PPDD.creation_date,
383 PPDD.created_by,
384 PPDD.last_update_date,
385 PPDD.last_updated_by,
386 PPDD.last_update_login
387 FROM po_price_diff_draft PPDD
388 WHERE PPDD.draft_id = p_draft_id
389 AND NVL(PPDD.change_accepted_flag, 'Y') = 'Y'
390 ) PPDDV
391 ON (PPD.price_differential_id = PPDDV.price_differential_id)
392 WHEN MATCHED THEN
393 UPDATE
394 SET
395 PPD.price_differential_num = PPDDV.price_differential_num,
396 PPD.price_type = PPDDV.price_type,
397 PPD.enabled_flag = PPDDV.enabled_flag,
398 PPD.min_multiplier = PPDDV.min_multiplier,
399 PPD.max_multiplier = PPDDV.max_multiplier,
400 PPD.multiplier = PPDDV.multiplier,
401 PPD.last_update_date = PPDDV.last_update_date,
402 PPD.last_updated_by = PPDDV.last_updated_by,
403 PPD.last_update_login = PPDDV.last_update_login
404 -- DELETE WHERE PPDDV.delete_flag = 'Y'
405 WHEN NOT MATCHED THEN
406 INSERT
407 (
408 PPD.price_differential_id,
409 PPD.price_differential_num,
410 PPD.entity_id,
411 PPD.entity_type,
412 PPD.price_type,
413 PPD.enabled_flag,
414 PPD.min_multiplier,
415 PPD.max_multiplier,
416 PPD.multiplier,
417 PPD.creation_date,
418 PPD.created_by,
419 PPD.last_update_date,
420 PPD.last_updated_by,
421 PPD.last_update_login
422 )
423 VALUES
424 (
425 PPDDV.price_differential_id,
426 PPDDV.price_differential_num,
427 PPDDV.entity_id,
428 PPDDV.entity_type,
429 PPDDV.price_type,
430 PPDDV.enabled_flag,
431 PPDDV.min_multiplier,
432 PPDDV.max_multiplier,
433 PPDDV.multiplier,
434 PPDDV.creation_date,
435 PPDDV.created_by,
436 PPDDV.last_update_date,
437 PPDDV.last_updated_by,
438 PPDDV.last_update_login
439 ) WHERE NVL(PPDDV.delete_flag, 'N') <> 'Y';
440
441 d_position := 10;
442 EXCEPTION
443 WHEN OTHERS THEN
444 PO_MESSAGE_S.add_exc_msg
445 ( p_pkg_name => d_pkg_name,
446 p_procedure_name => d_api_name || '.' || d_position
447 );
448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
449 END merge_changes;
450
451 -----------------------------------------------------------------------
452 --Start of Comments
453 --Name: lock_draft_record
454 --Function:
455 -- Obtain database lock for the record in draft table
456 --Parameters:
457 --IN:
458 --p_price_differential_id
459 -- id for po price differential record
460 --p_draft_id
461 -- draft unique identifier
462 --RETURN:
463 --End of Comments
464 ------------------------------------------------------------------------
465 PROCEDURE lock_draft_record
466 ( p_price_differential_id IN NUMBER,
467 p_draft_id IN NUMBER
468 ) IS
469
470 d_api_name CONSTANT VARCHAR2(30) := 'lock_draft_record';
471 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
472 d_position NUMBER;
473
474 l_dummy NUMBER;
475
476 BEGIN
477 d_position := 0;
478 IF (PO_LOG.d_proc) THEN
479 PO_LOG.proc_begin(d_module);
480 END IF;
481
482 SELECT 1
483 INTO l_dummy
484 FROM po_price_diff_draft
485 WHERE price_differential_id = p_price_differential_id
486 AND draft_id = p_draft_id
487 FOR UPDATE NOWAIT;
488
489 IF (PO_LOG.d_proc) THEN
490 PO_LOG.proc_end(d_module);
491 END IF;
492
493 EXCEPTION
494 WHEN NO_DATA_FOUND THEN
495 NULL;
496 END lock_draft_record;
497
498 -----------------------------------------------------------------------
499 --Start of Comments
500 --Name: lock_transaction_record
501 --Function:
502 -- Obtain database lock for the record in transaction table
503 --Parameters:
504 --IN:
505 --p_price_differential_id
506 -- id for price differential record
507 --RETURN:
508 --End of Comments
509 ------------------------------------------------------------------------
510 PROCEDURE lock_transaction_record
511 ( p_price_differential_id IN NUMBER
512 ) IS
513
514 d_api_name CONSTANT VARCHAR2(30) := 'lock_transaction_record';
515 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
516 d_position NUMBER;
517
518 l_dummy NUMBER;
519
520 BEGIN
521 d_position := 0;
522 IF (PO_LOG.d_proc) THEN
523 PO_LOG.proc_begin(d_module);
524 END IF;
525
526 SELECT 1
527 INTO l_dummy
528 FROM po_price_differentials
529 WHERE price_differential_id = p_price_differential_id
530 FOR UPDATE NOWAIT;
531
532 IF (PO_LOG.d_proc) THEN
533 PO_LOG.proc_end(d_module);
534 END IF;
535
536 EXCEPTION
537 WHEN NO_DATA_FOUND THEN
538 NULL;
539 END lock_transaction_record;
540
541 END PO_PRICE_DIFF_DRAFT_PKG;