[Home] [Help]
PACKAGE BODY: APPS.PO_LINE_LOCATIONS_DRAFT_PVT
Source
1 PACKAGE BODY PO_LINE_LOCATIONS_DRAFT_PVT AS
2 /* $Header: PO_LINE_LOCATIONS_DRAFT_PVT.plb 120.3 2006/09/14 01:30:39 bao noship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_LINE_LOCATIONS_DRAFT_PVT');
6
7 -------------------------------------------------------
8 ----------- PRIVATE PROCEDURES PROTOTYPE --------------
9 -------------------------------------------------------
10
11 PROCEDURE group_records_by_dml_type
12 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
13 , x_delete_list OUT NOCOPY PO_TBL_NUMBER
14 , x_insert_list OUT NOCOPY PO_TBL_NUMBER
15 , x_update_list OUT NOCOPY PO_TBL_NUMBER
16 );
17
18
19 -------------------------------------------------------
20 -------------- PUBLIC PROCEDURES ----------------------
21 -------------------------------------------------------
22
23 -----------------------------------------------------------------------
24 --Start of Comments
25 --Name: draft_changes_exist
26 --Pre-reqs: None
27 --Modifies:
28 --Locks:
29 -- None
30 --Function:
31 -- Checks whether there is any draft changes in the draft table
32 -- given the draft_id or draft_id + line_location_id
33 -- If only draft_id is provided, this program returns FND_API.G_TRUE for
34 -- any draft changes in this table for the draft
35 -- If the whole primary key is provided (draft_id + line location id), then
36 -- it return true if there is draft for this particular record in
37 -- the draft table
38 --Parameters:
39 --IN:
40 --p_draft_id_tbl
41 -- draft unique identifier
42 --p_line_location_id_tbl
43 -- po line location unique identifier
44 --IN OUT:
45 --OUT:
46 --Returns:
47 -- Array of flags indicating whether draft changes exist for the corresponding
48 -- entry in the input parameter. For each entry in the returning array:
49 -- FND_API.G_TRUE if there are draft changes
50 -- FND_API.G_FALSE if there aren't draft changes
51 --Notes:
52 --Testing:
53 --End of Comments
54 ------------------------------------------------------------------------
55 FUNCTION draft_changes_exist
56 ( p_draft_id_tbl IN PO_TBL_NUMBER,
57 p_line_location_id_tbl IN PO_TBL_NUMBER
58 ) RETURN PO_TBL_VARCHAR1
59 IS
60 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
61 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
62 d_position NUMBER;
63
64 l_key NUMBER;
65 l_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
66 l_dft_exists_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
67 l_dft_exists_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
68
69 BEGIN
70 d_position := 0;
71 IF (PO_LOG.d_proc) THEN
72 PO_LOG.proc_begin(d_module);
73 END IF;
74
75 l_index_tbl.extend(p_draft_id_tbl.COUNT);
76 l_dft_exists_tbl.extend(p_draft_id_tbl.COUNT);
77
78 FOR i IN 1..l_index_tbl.COUNT LOOP
79 l_index_tbl(i) := i;
80 l_dft_exists_tbl(i) := FND_API.G_FALSE;
81 END LOOP;
82
83 d_position := 10;
84
85 l_key := PO_CORE_S.get_session_gt_nextval;
86
87 d_position := 20;
88
89 FORALL i IN 1..p_draft_id_tbl.COUNT
90 INSERT INTO po_session_gt
91 ( key,
92 num1
93 )
94 SELECT l_key,
95 l_index_tbl(i)
96 FROM DUAL
97 WHERE EXISTS (SELECT 1
98 FROM po_line_locations_draft_all PLLD
99 WHERE PLLD.draft_id = p_draft_id_tbl(i)
100 AND PLLD.line_location_id = NVL(p_line_location_id_tbl(i),
101 PLLD.line_location_id)
102 AND NVL(PLLD.change_accepted_flag, 'Y') = 'Y');
103
104
105 d_position := 30;
106
107 -- All the num1 returned from this DELETE statement are indexes for
108 -- records that contain draft changes
109 DELETE FROM po_session_gt
110 WHERE key = l_key
111 RETURNING num1
112 BULK COLLECT INTO l_dft_exists_index_tbl;
113
114 d_position := 40;
115
116 FOR i IN 1..l_dft_exists_index_tbl.COUNT LOOP
117 l_dft_exists_tbl(l_dft_exists_index_tbl(i)) := FND_API.G_TRUE;
118 END LOOP;
119
120 IF (PO_LOG.d_stmt) THEN
121 PO_LOG.stmt(d_module, d_position, '# of records that have dft changes',
122 l_dft_exists_index_tbl.COUNT);
123 END IF;
124
125 RETURN l_dft_exists_tbl;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 PO_MESSAGE_S.add_exc_msg
130 ( p_pkg_name => d_pkg_name,
131 p_procedure_name => d_api_name || '.' || d_position
132 );
133 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134 END draft_changes_exist;
135
136
137 -----------------------------------------------------------------------
138 --Start of Comments
139 --Name: draft_changes_exist
140 --Pre-reqs: None
141 --Modifies:
142 --Locks:
143 -- None
144 --Function:
145 -- Same functionality as the bulk version of draft_changes_exist
146 --Parameters:
147 --IN:
148 --p_draft_id
149 -- draft unique identifier
150 --p_line_location_id
151 -- line location unique identifier
152 --IN OUT:
153 --OUT:
154 --Returns:
155 -- FND_API.G_TRUE if there are draft changes
156 -- FND_API.G_FALSE if there aren't draft changes
157 --Notes:
158 --Testing:
159 --End of Comments
160 ------------------------------------------------------------------------
161 FUNCTION draft_changes_exist
162 ( p_draft_id IN NUMBER,
163 p_line_location_id IN NUMBER
164 ) RETURN VARCHAR2
165 IS
166 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
167 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
168 d_position NUMBER;
169
170 l_exists_tbl PO_TBL_VARCHAR1;
171 BEGIN
172 d_position := 0;
173 IF (PO_LOG.d_proc) THEN
174 PO_LOG.proc_begin(d_module);
175 END IF;
176
177 l_exists_tbl :=
178 draft_changes_exist
179 ( p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
180 p_line_location_id_tbl => PO_TBL_NUMBER(p_line_location_id)
181 );
182
183 IF (PO_LOG.d_stmt) THEN
184 PO_LOG.stmt(d_module, d_position, 'exists', l_exists_tbl(1));
185 END IF;
186
187 RETURN l_exists_tbl(1);
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 PO_MESSAGE_S.add_exc_msg
192 ( p_pkg_name => d_pkg_name,
193 p_procedure_name => d_api_name || '.' || d_position
194 );
195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
196 END draft_changes_exist;
197
198 -----------------------------------------------------------------------
199 --Start of Comments
200 --Name: apply_changes
201 --Pre-reqs: None
202 --Modifies:
203 --Locks:
204 -- None
205 --Function:
206 -- Process line location draft records and merge them to transaction table. It
207 -- also performs all additional work related specifically to the merge
208 -- action
209 --Parameters:
210 --IN:
211 --p_draft_info
212 -- data structure storing draft information
213 --IN OUT:
214 --OUT:
215 --Returns:
216 --Notes:
217 --Testing:
218 --End of Comments
219 ------------------------------------------------------------------------
220 PROCEDURE apply_changes
221 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
222 ) IS
223 d_api_name CONSTANT VARCHAR2(30) := 'apply_changes';
224 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
225 d_position NUMBER;
226
227 l_delete_list PO_TBL_NUMBER;
228 l_insert_list PO_TBL_NUMBER;
229 l_update_list PO_TBL_NUMBER;
230
231 BEGIN
232 d_position := 0;
233 IF (PO_LOG.d_proc) THEN
234 PO_LOG.proc_begin(d_module);
235 END IF;
236
237 IF (p_draft_info.line_locations_changed = FND_API.G_FALSE) THEN
238 IF (PO_LOG.d_stmt) THEN
239 PO_LOG.stmt(d_module, d_position, 'no change-no need to apply');
240 END IF;
241
242 RETURN;
243 END IF;
244
245 d_position := 10;
246 group_records_by_dml_type
247 ( p_draft_info => p_draft_info
248 , x_delete_list => l_delete_list
249 , x_insert_list => l_insert_list
250 , x_update_list => l_update_list
251 );
252
253 IF (l_delete_list.COUNT > 0) THEN
254 d_position := 20; FOR i IN 1..l_delete_list.COUNT LOOP
255
256 PO_REQ_LINES_SV.remove_req_from_po (l_delete_list(i), 'SHIPMENT');
257
258 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
259 ( 'PO_SHIPMENTS',
260 l_delete_list(i),
261 '','','','','', 'Y'
262 );
263 END LOOP;
264 END IF;
265
266 d_position := 30;
267 -- Merge Changes
268 PO_LINE_LOCATIONS_DRAFT_PKG.merge_changes
269 ( p_draft_id => p_draft_info.draft_id
270 );
271
272 d_position := 40;
273 EXCEPTION
274 WHEN OTHERS THEN
275 PO_MESSAGE_S.add_exc_msg
276 ( p_pkg_name => d_pkg_name,
277 p_procedure_name => d_api_name || '.' || d_position
278 );
279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
280 END apply_changes;
281
282 -- bug4176111
283 -----------------------------------------------------------------------
284 --Start of Comments
285 --Name: maintain_retroactive_change
286 --Modifies:
287 --Locks:
288 -- None
289 --Function:
290 -- Updates retroactive date at line level for blanket and SPO if there's
291 -- price change
292 --Parameters:
293 --IN:
294 --p_draft_info
295 -- data structure storing draft information
296 --IN OUT:
297 --OUT:
298 --Returns:
299 --Notes:
300 --Testing:
301 --End of Comments
302 ------------------------------------------------------------------------
303 PROCEDURE maintain_retroactive_change
304 ( p_draft_info IN PO_DRAFTS_PVT.draft_info_rec_type
305 ) IS
306 d_api_name CONSTANT VARCHAR2(30) := 'maintain_retroactive_change';
307 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
308 d_position NUMBER;
309
310 l_key NUMBER;
311
312 l_po_line_id_tbl PO_TBL_NUMBER;
313 l_draft_id_tbl PO_TBL_NUMBER;
314 l_delete_flag_tbl PO_TBL_VARCHAR1;
315 l_record_exist_tbl PO_TBL_VARCHAR1;
316
317 BEGIN
318 d_position := 0;
319 IF (PO_LOG.d_proc) THEN
320 PO_LOG.proc_begin(d_module);
321 END IF;
322
323 IF (NOT (p_draft_info.doc_type = 'PA' AND
324 p_draft_info.doc_subtype = 'BLANKET') OR
325 p_draft_info.line_locations_changed = FND_API.G_FALSE) THEN
326
327 RETURN;
328 END IF;
329
330
331 d_position := 10;
332
333 l_key := PO_CORE_S.get_session_gt_nextval;
334
335 -- New price break or deleted price break trigger retroactive pricing
336 INSERT INTO po_session_gt
337 ( key,
338 num1
339 )
340 SELECT l_key,
341 POLLD.po_line_id
342 FROM po_line_locations_draft_all POLLD,
343 po_line_locations_all POLL
344 WHERE POLLD.draft_id = p_draft_info.draft_id
345 AND POLLD.line_location_id = POLL.line_location_id (+)
346 AND NVL(POLLD.change_accepted_flag, 'Y') = 'Y'
347 AND (NVL(POLLD.delete_flag, 'N') = 'Y' OR
348 POLL.line_location_id IS NULL);
349
350 d_position := 20;
351
352 -- If one of the pricing attributes get modified, it triggers
353 -- retroactive pricing event as well
354 INSERT INTO po_session_gt
355 ( key,
356 num1
357 )
358 SELECT l_key,
359 POLLD.po_line_id
360 FROM po_line_locations_draft_all POLLD,
361 po_line_locations_all POLL
362 WHERE POLLD.draft_id = p_draft_info.draft_id
363 AND POLLD.line_location_id = POLL.line_location_id
364 AND NVL(POLLD.change_accepted_flag, 'Y') = 'Y'
365 AND NVL(POLLD.delete_flag, 'N') = 'N'
366 AND (DECODE (POLLD.ship_to_organization_id,
367 POLL.ship_to_organization_id, 'Y', 'N') = 'N' OR
368 DECODE (POLLD.ship_to_location_id,
369 POLL.ship_to_location_id, 'Y', 'N') = 'N' OR
370 DECODE (POLLD.quantity,
371 POLL.quantity, 'Y', 'N') = 'N' OR
372 DECODE (POLLD.price_override,
373 POLL.price_override, 'Y', 'N') = 'N' OR
374 DECODE (POLLD.price_discount,
375 POLL.price_discount, 'Y', 'N') = 'N' OR
376 DECODE (POLLD.start_date,
377 POLL.start_date, 'Y', 'N') = 'N' OR
378 DECODE (POLLD.end_date,
379 POLL.end_date, 'Y', 'N') = 'N');
380
381 d_position := 30;
382
383 -- get all the lines that need to retroactively re-price
384 DELETE FROM po_session_gt
385 WHERE key = l_key
386 RETURNING num1, p_draft_info.draft_id, NULL
387 BULK COLLECT
388 INTO l_po_line_id_tbl, l_draft_id_tbl, l_delete_flag_tbl;
389
390 d_position := 40;
391
392 PO_LINES_DRAFT_PKG.sync_draft_from_txn
393 ( p_po_line_id_tbl => l_po_line_id_tbl,
394 p_draft_id_tbl => l_draft_id_tbl,
395 p_delete_flag_tbl => l_delete_flag_tbl,
396 x_record_already_exist_tbl => l_record_exist_tbl
397 );
398
399 d_position := 50;
400
401 FORALL i IN 1..l_po_line_id_tbl.COUNT
402 UPDATE po_lines_draft_all
403 SET retroactive_date = SYSDATE
404 WHERE draft_id = p_draft_info.draft_id
405 AND po_line_id = l_po_line_id_tbl(i);
406
407
408 IF (PO_LOG.d_proc) THEN
409 PO_LOG.proc_end(d_module);
410 END IF;
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 PO_MESSAGE_S.add_exc_msg
415 ( p_pkg_name => d_pkg_name,
416 p_procedure_name => d_api_name || '.' || d_position
417 );
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419
420 END maintain_retroactive_change;
421
422 -------------------------------------------------------
423 -------------- PRIVATE PROCEDURES ---------------------
424 -------------------------------------------------------
425
426 -----------------------------------------------------------------------
427 --Start of Comments
428 --Name: group_records_by_dml_type
429 --Pre-reqs: None
430 --Modifies:
431 --Locks:
432 -- None
433 --Function:
434 -- Get all the draft records and separate them into three categories:
435 -- records to be deleted, inserted, and updated. The lists are returned
436 -- as arrays of numbers
437 --Parameters:
438 --IN:
439 --p_draft_info
440 -- record structure to hold draft information
441 --IN OUT:
442 --OUT:
443 --x_delete_list
444 -- IDs to be deleted from transaction table
445 --x_insert_list
446 -- IDs to be inserted in transaction table
447 --x_update_list
448 -- IDs to be updated in transaction table
449 --Returns:
450 --Notes:
451 --Testing:
452 --End of Comments
453 ------------------------------------------------------------------------
454 PROCEDURE group_records_by_dml_type
455 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
456 , x_delete_list OUT NOCOPY PO_TBL_NUMBER
457 , x_insert_list OUT NOCOPY PO_TBL_NUMBER
458 , x_update_list OUT NOCOPY PO_TBL_NUMBER
459 ) IS
460
461 d_api_name CONSTANT VARCHAR2(30) := 'group_records_by_dml_type';
462 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
463 d_position NUMBER;
464
465 l_id_list PO_TBL_NUMBER;
466 l_del_flag_list PO_TBL_VARCHAR1;
467 l_txn_exists_list PO_TBL_VARCHAR1;
468
469 BEGIN
470 d_position := 0;
471 IF (PO_LOG.d_proc) THEN
472 PO_LOG.proc_begin(d_module);
473 END IF;
474
475 x_delete_list := PO_TBL_NUMBER();
476 x_insert_list := PO_TBL_NUMBER();
477 x_update_list := PO_TBL_NUMBER();
478
479 d_position := 10;
480 SELECT PLLD.line_location_id,
481 NVL(PLLD.delete_flag, 'N'),
482 DECODE(PLL.po_line_id, NULL, 'N', 'Y')
483 BULK COLLECT
484 INTO l_id_list,
485 l_del_flag_list,
486 l_txn_exists_list
487 FROM po_line_locations_draft_all PLLD,
488 po_line_locations_all PLL
489 WHERE PLLD.draft_id = p_draft_info.draft_id
490 AND NVL(PLLD.change_accepted_flag, 'Y') = 'Y'
491 AND PLLD.line_location_id = PLL.line_location_id(+);
492
493 d_position := 20;
494 FOR i IN 1..l_id_list.COUNT LOOP
495 IF (l_del_flag_list(i) = 'Y') THEN
496 IF (l_txn_exists_list(i) = 'Y') THEN
497 x_delete_list.extend;
498 x_delete_list(x_delete_list.LAST) := l_id_list(i);
499 END IF;
500 ELSE
501 IF (l_txn_exists_list(i) = 'Y') THEN
502 x_update_list.extend;
503 x_update_list(x_update_list.LAST) := l_id_list(i);
504 ELSE
505 x_insert_list.extend;
506 x_insert_list(x_insert_list.LAST) := l_id_list(i);
507 END IF;
508 END IF;
509 END LOOP;
510
511 d_position := 30;
512 EXCEPTION
513 WHEN OTHERS THEN
514 PO_MESSAGE_S.add_exc_msg
515 ( p_pkg_name => d_pkg_name,
516 p_procedure_name => d_api_name || '.' || d_position
517 );
518 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
519 END group_records_by_dml_type;
520
521 END PO_LINE_LOCATIONS_DRAFT_PVT;