[Home] [Help]
PACKAGE BODY: APPS.PO_LINES_DRAFT_PVT
Source
1 PACKAGE BODY PO_LINES_DRAFT_PVT AS
2 /* $Header: PO_LINES_DRAFT_PVT.plb 120.3 2006/09/14 01:29:22 bao noship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_LINES_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 + po_line_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 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_po_line_id_tbl
43 -- po line 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_po_line_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_lines_draft_all PLD
99 WHERE PLD.draft_id = p_draft_id_tbl(i)
100 AND PLD.po_line_id = NVL(p_po_line_id_tbl(i),
101 PLD.po_line_id)
102 AND NVL(PLD.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_po_line_id
151 -- po line 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_po_line_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_po_line_id_tbl => PO_TBL_NUMBER(p_po_line_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 -----------------------------------------------------------------------
200 --Start of Comments
201 --Name: apply_changes
202 --Pre-reqs: None
203 --Modifies:
204 --Locks:
205 -- None
206 --Function:
207 -- Process line draft records and merge them to transaction table. It
208 -- also performs all additional work related specifically to the merge
209 -- action
210 --Parameters:
211 --IN:
212 --p_draft_info
213 -- data structure storing draft information
214 --IN OUT:
215 --OUT:
216 --Returns:
217 --Notes:
218 --Testing:
219 --End of Comments
220 ------------------------------------------------------------------------
221 PROCEDURE apply_changes
222 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
223 ) IS
224 d_api_name CONSTANT VARCHAR2(30) := 'apply_changes';
225 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
226 d_position NUMBER;
227
228 l_delete_list PO_TBL_NUMBER;
229 l_insert_list PO_TBL_NUMBER;
230 l_update_list PO_TBL_NUMBER;
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.lines_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 d_position := 20;
254 IF (l_delete_list.COUNT > 0) THEN
255
256 d_position := 30;
257 FOR i IN 1..l_delete_list.COUNT LOOP
258 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
259 ( 'PO_LINES',
260 l_delete_list(i),
261 '','','','','', 'Y'
262 );
263 END LOOP;
264 END IF;
265
266 d_position := 40;
267 -- Merge Changes
268 PO_LINES_DRAFT_PKG.merge_changes
269 ( p_draft_id => p_draft_info.draft_id
270 );
271
272 EXCEPTION
273 WHEN OTHERS THEN
274 PO_MESSAGE_S.add_exc_msg
275 ( p_pkg_name => d_pkg_name,
276 p_procedure_name => d_api_name || '.' || d_position
277 );
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279
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_po_line_id_tbl PO_TBL_NUMBER;
311 l_price_break_lookup_code_tbl PO_TBL_VARCHAR30;
312
313 l_update_retro_date_flag_tbl PO_TBL_VARCHAR1;
314
315 BEGIN
316 d_position := 0;
317 IF (PO_LOG.d_proc) THEN
318 PO_LOG.proc_begin(d_module);
319 END IF;
320
321 IF ((NOT (p_draft_info.doc_type = 'PO' AND
322 p_draft_info.doc_subtype = 'STANDARD') AND
323 NOT (p_draft_info.doc_type = 'PA' AND
324 p_draft_info.doc_subtype = 'BLANKET')) OR
325 p_draft_info.lines_changed = FND_API.G_FALSE) THEN
326
327 RETURN;
328 END IF;
329
330 SELECT POLD.po_line_id,
331 NVL(POLD.price_break_lookup_code, 'NON CUMULATIVE'),
332 'N'
333 BULK COLLECT
334 INTO l_po_line_id_tbl,
335 l_price_break_lookup_code_tbl,
336 l_update_retro_date_flag_tbl
337 FROM po_lines_all POL,
338 po_lines_draft_all POLD
339 WHERE POLD.draft_id = p_draft_info.draft_id
340 AND POLD.po_line_id = POL.po_line_Id
341 AND NVL(POLD.delete_flag, 'N') = 'N'
342 AND NVL(POLD.change_accepted_flag, 'Y') = 'Y'
343 AND DECODE(POLD.unit_price, POL.unit_price, 'Y', 'N') = 'N' ;
344
345 FOR i IN 1..l_po_line_id_tbl.COUNT LOOP
346 IF (p_draft_info.doc_subtype = 'STANDARD') THEN
347 l_update_retro_date_flag_tbl(i) := 'Y';
348 ELSIF (p_draft_info.doc_subtype = 'BLANKET' AND
349 l_price_break_lookup_code_tbl(i) = 'NON CUMULATIVE') THEN
350 l_update_retro_date_flag_tbl(i) := 'Y';
351 END IF;
352 END LOOP;
353
354 FORALL i IN 1..l_po_line_id_tbl.COUNT
355 UPDATE po_lines_draft_all
356 SET retroactive_date = SYSDATE
357 WHERE draft_id = p_draft_info.draft_id
358 AND po_line_id = l_po_line_id_tbl(i)
359 AND l_update_retro_date_flag_tbl(i) = 'Y';
360
361
362 IF (PO_LOG.d_proc) THEN
363 PO_LOG.proc_end(d_module);
364 END IF;
365
366 EXCEPTION
367 WHEN OTHERS THEN
368 PO_MESSAGE_S.add_exc_msg
369 ( p_pkg_name => d_pkg_name,
370 p_procedure_name => d_api_name || '.' || d_position
371 );
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373
374 END maintain_retroactive_change;
375
376 -------------------------------------------------------
377 -------------- PRIVATE PROCEDURES ---------------------
378 -------------------------------------------------------
379
380 -----------------------------------------------------------------------
381 --Start of Comments
382 --Name: group_records_by_dml_type
383 --Pre-reqs: None
384 --Modifies:
385 --Locks:
386 -- None
387 --Function:
388 -- Get all the draft records and separate them into three categories:
389 -- records to be deleted, inserted, and updated. The lists are returned
390 -- as arrays of numbers
391 --Parameters:
392 --IN:
393 --p_draft_info
394 -- record structure to hold draft information
395 --IN OUT:
396 --OUT:
397 --x_delete_list
398 -- IDs to be deleted from transaction table
399 --x_insert_list
400 -- IDs to be inserted in transaction table
401 --x_update_list
402 -- IDs to be updated in transaction table
403 --Returns:
404 --Notes:
405 --Testing:
406 --End of Comments
407 ------------------------------------------------------------------------
408 PROCEDURE group_records_by_dml_type
409 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
410 , x_delete_list OUT NOCOPY PO_TBL_NUMBER
411 , x_insert_list OUT NOCOPY PO_TBL_NUMBER
412 , x_update_list OUT NOCOPY PO_TBL_NUMBER
413 ) IS
414 d_api_name CONSTANT VARCHAR2(30) := 'group_records_by_dml_type';
415 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
416 d_position NUMBER;
417
418 l_id_list PO_TBL_NUMBER;
419 l_del_flag_list PO_TBL_VARCHAR1;
420 l_txn_exists_list PO_TBL_VARCHAR1;
421
422 BEGIN
423 d_position := 0;
424 IF (PO_LOG.d_proc) THEN
425 PO_LOG.proc_begin(d_module);
426 END IF;
427
428 x_delete_list := PO_TBL_NUMBER();
429 x_insert_list := PO_TBL_NUMBER();
430 x_update_list := PO_TBL_NUMBER();
431
432 d_position := 10;
433 SELECT PLD.po_line_id,
434 NVL(PLD.delete_flag, 'N'),
435 DECODE(PL.po_line_id, NULL, 'N', 'Y')
436 BULK COLLECT
437 INTO l_id_list,
438 l_del_flag_list,
439 l_txn_exists_list
440 FROM po_lines_draft_all PLD,
441 po_lines_all PL
442 WHERE PLD.draft_id = p_draft_info.draft_id
443 AND NVL(PLD.change_accepted_flag, 'Y') = 'Y'
444 AND PLD.po_line_id = PL.po_line_id(+);
445
446 d_position := 20;
447 FOR i IN 1..l_id_list.COUNT LOOP
448 IF (l_del_flag_list(i) = 'Y') THEN
449 IF (l_txn_exists_list(i) = 'Y') THEN
450 x_delete_list.extend;
451 x_delete_list(x_delete_list.LAST) := l_id_list(i);
452 END IF;
453 ELSE
454 IF (l_txn_exists_list(i) = 'Y') THEN
455 x_update_list.extend;
456 x_update_list(x_update_list.LAST) := l_id_list(i);
457 ELSE
458 x_insert_list.extend;
459 x_insert_list(x_insert_list.LAST) := l_id_list(i);
460 END IF;
461 END IF;
462 END LOOP;
463
464 d_position := 30;
465 EXCEPTION
466 WHEN OTHERS THEN
467 PO_MESSAGE_S.add_exc_msg
468 ( p_pkg_name => d_pkg_name,
469 p_procedure_name => d_api_name || '.' || d_position
470 );
471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472 END group_records_by_dml_type;
473
474
475 END PO_LINES_DRAFT_PVT;