[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.12020000.3 2013/02/10 15:45:07 vegajula ship $ */
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 l_clm_document VARCHAR2(1);
232
233 BEGIN
234 d_position := 0;
235 IF (PO_LOG.d_proc) THEN
236 PO_LOG.proc_begin(d_module);
237 END IF;
238
239 IF (p_draft_info.line_locations_changed = FND_API.G_FALSE) THEN
240 IF (PO_LOG.d_stmt) THEN
241 PO_LOG.stmt(d_module, d_position, 'no change-no need to apply');
242 END IF;
243
244 RETURN;
245 END IF;
246
247 d_position := 10;
248 group_records_by_dml_type
249 ( p_draft_info => p_draft_info
250 , x_delete_list => l_delete_list
251 , x_insert_list => l_insert_list
252 , x_update_list => l_update_list
253 );
254
255 IF (l_delete_list.COUNT > 0) THEN
256
257 IF (PO_LOG.d_stmt) THEN
258 PO_LOG.stmt(d_module, d_position, 'Deleted Lines Found');
259 END IF;
260 -- Check for the documenr style. CLM or Commercial
261 BEGIN
262 SELECT 'Y'
263 INTO l_clm_document
264 FROM po_headers_all h,
265 po_doc_style_headers pdsh,
266 po_line_locations_all pll
267 WHERE h.style_id = pdsh.style_id
268 AND h.po_header_id = pll.po_header_id
269 AND pll.line_location_id = l_delete_list(1)
270 AND NVL(pdsh.clm_flag,'N') = 'Y'
271 AND ROWNUM = 1;
272
273 EXCEPTION WHEN No_Data_Found THEN
274 l_clm_document := 'N';
275 END;
276
277 d_position := 20;
278 IF (PO_LOG.d_stmt) THEN
279 PO_LOG.stmt(d_module, d_position, 'l_clm_document:'||l_clm_document);
280 END IF;
281
282 FOR i IN 1..l_delete_list.COUNT LOOP
283 -- Bug: 13948625, Call same old remove_req_from_po in case of Commercial PO line deleted from BWC.
284 -- Schedules cannot be deleted for BWC for CLM documents.
285 IF (l_clm_document <> 'Y') THEN
286
287 PO_REQ_LINES_SV.remove_req_from_po (l_delete_list(i), 'SHIPMENT');
288
289 END IF;
290
291 d_position := 30;
292 IF (PO_LOG.d_stmt) THEN
293 PO_LOG.stmt(d_module, d_position, 'Calling delete_attachments');
294 END IF;
295
296 --Bug 13938456 Conc Mod attachments
297 --Passing null for automatically_added_flag instead of Y
298 --as all attachments have to be deleted
299 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
300 ( 'PO_SHIPMENTS',
301 l_delete_list(i),
302 '','','','','', ''
303 );
304 END LOOP;
305 END IF;
306
307 d_position := 30;
308 -- Merge Changes
309 PO_LINE_LOCATIONS_DRAFT_PKG.merge_changes
310 ( p_draft_id => p_draft_info.draft_id
311 );
312
313 d_position := 40;
314 EXCEPTION
315 WHEN OTHERS THEN
316 PO_MESSAGE_S.add_exc_msg
317 ( p_pkg_name => d_pkg_name,
318 p_procedure_name => d_api_name || '.' || d_position
319 );
320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
321 END apply_changes;
322
323 -- bug4176111
324 -----------------------------------------------------------------------
325 --Start of Comments
326 --Name: maintain_retroactive_change
327 --Modifies:
328 --Locks:
329 -- None
330 --Function:
331 -- Updates retroactive date at line level for blanket and SPO if there's
332 -- price change
333 --Parameters:
334 --IN:
335 --p_draft_info
336 -- data structure storing draft information
337 --IN OUT:
338 --OUT:
339 --Returns:
340 --Notes:
341 --Testing:
342 --End of Comments
343 ------------------------------------------------------------------------
344 PROCEDURE maintain_retroactive_change
345 ( p_draft_info IN PO_DRAFTS_PVT.draft_info_rec_type
346 ) IS
347 d_api_name CONSTANT VARCHAR2(30) := 'maintain_retroactive_change';
348 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
349 d_position NUMBER;
350
351 l_key NUMBER;
352
353 l_po_line_id_tbl PO_TBL_NUMBER;
354 l_draft_id_tbl PO_TBL_NUMBER;
355 l_delete_flag_tbl PO_TBL_VARCHAR1;
356 l_record_exist_tbl PO_TBL_VARCHAR1;
357
358 BEGIN
359 d_position := 0;
360 IF (PO_LOG.d_proc) THEN
361 PO_LOG.proc_begin(d_module);
362 END IF;
363
364 IF (NOT (p_draft_info.doc_type = 'PA' AND
365 p_draft_info.doc_subtype = 'BLANKET') OR
366 p_draft_info.line_locations_changed = FND_API.G_FALSE) THEN
367
368 RETURN;
369 END IF;
370
371
372 d_position := 10;
373
374 l_key := PO_CORE_S.get_session_gt_nextval;
375
376 -- New price break or deleted price break trigger retroactive pricing
377 INSERT INTO po_session_gt
378 ( key,
379 num1
380 )
381 SELECT l_key,
382 POLLD.po_line_id
383 FROM po_line_locations_draft_all POLLD,
384 po_line_locations_all POLL
385 WHERE POLLD.draft_id = p_draft_info.draft_id
386 AND POLLD.line_location_id = POLL.line_location_id (+)
387 AND NVL(POLLD.change_accepted_flag, 'Y') = 'Y'
388 AND (NVL(POLLD.delete_flag, 'N') = 'Y' OR
389 POLL.line_location_id IS NULL);
390
391 d_position := 20;
392
393 -- If one of the pricing attributes get modified, it triggers
394 -- retroactive pricing event as well
395 INSERT INTO po_session_gt
396 ( key,
397 num1
398 )
399 SELECT l_key,
400 POLLD.po_line_id
401 FROM po_line_locations_draft_all POLLD,
402 po_line_locations_all POLL
403 WHERE POLLD.draft_id = p_draft_info.draft_id
404 AND POLLD.line_location_id = POLL.line_location_id
405 AND NVL(POLLD.change_accepted_flag, 'Y') = 'Y'
406 AND NVL(POLLD.delete_flag, 'N') = 'N'
407 AND (DECODE (POLLD.ship_to_organization_id,
408 POLL.ship_to_organization_id, 'Y', 'N') = 'N' OR
409 DECODE (POLLD.ship_to_location_id,
410 POLL.ship_to_location_id, 'Y', 'N') = 'N' OR
411 DECODE (POLLD.quantity,
412 POLL.quantity, 'Y', 'N') = 'N' OR
413 DECODE (POLLD.price_override,
414 POLL.price_override, 'Y', 'N') = 'N' OR
415 DECODE (POLLD.price_discount,
416 POLL.price_discount, 'Y', 'N') = 'N' OR
417 DECODE (POLLD.start_date,
418 POLL.start_date, 'Y', 'N') = 'N' OR
419 DECODE (POLLD.end_date,
420 POLL.end_date, 'Y', 'N') = 'N');
421
422 d_position := 30;
423
424 -- get all the lines that need to retroactively re-price
425 DELETE FROM po_session_gt
426 WHERE key = l_key
427 RETURNING num1, p_draft_info.draft_id, NULL
428 BULK COLLECT
429 INTO l_po_line_id_tbl, l_draft_id_tbl, l_delete_flag_tbl;
430
431 d_position := 40;
432
433 PO_LINES_DRAFT_PKG.sync_draft_from_txn
434 ( p_po_line_id_tbl => l_po_line_id_tbl,
435 p_draft_id_tbl => l_draft_id_tbl,
436 p_delete_flag_tbl => l_delete_flag_tbl,
437 x_record_already_exist_tbl => l_record_exist_tbl
438 );
439
440 d_position := 50;
441
442 FORALL i IN 1..l_po_line_id_tbl.COUNT
443 UPDATE po_lines_draft_all
444 SET retroactive_date = SYSDATE
445 WHERE draft_id = p_draft_info.draft_id
446 AND po_line_id = l_po_line_id_tbl(i);
447
448
449 IF (PO_LOG.d_proc) THEN
450 PO_LOG.proc_end(d_module);
451 END IF;
452
453 EXCEPTION
454 WHEN OTHERS THEN
455 PO_MESSAGE_S.add_exc_msg
456 ( p_pkg_name => d_pkg_name,
457 p_procedure_name => d_api_name || '.' || d_position
458 );
459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
460
461 END maintain_retroactive_change;
462
463 -------------------------------------------------------
464 -------------- PRIVATE PROCEDURES ---------------------
465 -------------------------------------------------------
466
467 -----------------------------------------------------------------------
468 --Start of Comments
469 --Name: group_records_by_dml_type
470 --Pre-reqs: None
471 --Modifies:
472 --Locks:
473 -- None
474 --Function:
475 -- Get all the draft records and separate them into three categories:
476 -- records to be deleted, inserted, and updated. The lists are returned
477 -- as arrays of numbers
478 --Parameters:
479 --IN:
480 --p_draft_info
481 -- record structure to hold draft information
482 --IN OUT:
483 --OUT:
484 --x_delete_list
485 -- IDs to be deleted from transaction table
486 --x_insert_list
487 -- IDs to be inserted in transaction table
488 --x_update_list
489 -- IDs to be updated in transaction table
490 --Returns:
491 --Notes:
492 --Testing:
493 --End of Comments
494 ------------------------------------------------------------------------
495 PROCEDURE group_records_by_dml_type
496 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
497 , x_delete_list OUT NOCOPY PO_TBL_NUMBER
498 , x_insert_list OUT NOCOPY PO_TBL_NUMBER
499 , x_update_list OUT NOCOPY PO_TBL_NUMBER
500 ) IS
501
502 d_api_name CONSTANT VARCHAR2(30) := 'group_records_by_dml_type';
503 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
504 d_position NUMBER;
505
506 l_id_list PO_TBL_NUMBER;
507 l_del_flag_list PO_TBL_VARCHAR1;
508 l_txn_exists_list PO_TBL_VARCHAR1;
509
510 BEGIN
511 d_position := 0;
512 IF (PO_LOG.d_proc) THEN
513 PO_LOG.proc_begin(d_module);
514 END IF;
515
516 x_delete_list := PO_TBL_NUMBER();
517 x_insert_list := PO_TBL_NUMBER();
518 x_update_list := PO_TBL_NUMBER();
519
520 d_position := 10;
521 SELECT PLLD.line_location_id,
522 NVL(PLLD.delete_flag, 'N'),
523 DECODE(PLL.po_line_id, NULL, 'N', 'Y')
524 BULK COLLECT
525 INTO l_id_list,
526 l_del_flag_list,
527 l_txn_exists_list
528 FROM po_line_locations_draft_all PLLD,
529 po_line_locations_all PLL
530 WHERE PLLD.draft_id = p_draft_info.draft_id
531 AND NVL(PLLD.change_accepted_flag, 'Y') = 'Y'
532 AND PLLD.line_location_id = PLL.line_location_id(+);
533
534 d_position := 20;
535 FOR i IN 1..l_id_list.COUNT LOOP
536 IF (l_del_flag_list(i) = 'Y') THEN
537 IF (l_txn_exists_list(i) = 'Y') THEN
538 x_delete_list.extend;
539 x_delete_list(x_delete_list.LAST) := l_id_list(i);
540 END IF;
541 ELSE
542 IF (l_txn_exists_list(i) = 'Y') THEN
543 x_update_list.extend;
544 x_update_list(x_update_list.LAST) := l_id_list(i);
545 ELSE
546 x_insert_list.extend;
547 x_insert_list(x_insert_list.LAST) := l_id_list(i);
548 END IF;
549 END IF;
550 END LOOP;
551
552 d_position := 30;
553 EXCEPTION
554 WHEN OTHERS THEN
555 PO_MESSAGE_S.add_exc_msg
556 ( p_pkg_name => d_pkg_name,
557 p_procedure_name => d_api_name || '.' || d_position
558 );
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560 END group_records_by_dml_type;
561
562 END PO_LINE_LOCATIONS_DRAFT_PVT;