[Home] [Help]
PACKAGE BODY: APPS.PO_DISTRIBUTIONS_DRAFT_PVT
Source
1 PACKAGE BODY PO_DISTRIBUTIONS_DRAFT_PVT AS
2 /* $Header: PO_DISTRIBUTIONS_DRAFT_PVT.plb 120.3.12020000.2 2013/02/11 01:13:35 vegajula ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_DISTRIBUTIONS_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_distribution_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 + distribution 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_distribution_id_tbl
43 -- po distribution 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_distribution_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_distributions_draft_all PDD
99 WHERE PDD.draft_id = p_draft_id_tbl(i)
100 AND PDD.po_distribution_id =
101 NVL(p_po_distribution_id_tbl(i),
102 PDD.po_distribution_id)
103 AND NVL(PDD.change_accepted_flag, 'Y') = 'Y');
104
105
106 d_position := 30;
107
108 -- All the num1 returned from this DELETE statement are indexes for
109 -- records that contain draft changes
110 DELETE FROM po_session_gt
111 WHERE key = l_key
112 RETURNING num1
113 BULK COLLECT INTO l_dft_exists_index_tbl;
114
115 d_position := 40;
116
117 FOR i IN 1..l_dft_exists_index_tbl.COUNT LOOP
118 l_dft_exists_tbl(l_dft_exists_index_tbl(i)) := FND_API.G_TRUE;
119 END LOOP;
120
121 IF (PO_LOG.d_stmt) THEN
122 PO_LOG.stmt(d_module, d_position, '# of records that have dft changes',
123 l_dft_exists_index_tbl.COUNT);
124 END IF;
125
126 RETURN l_dft_exists_tbl;
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 PO_MESSAGE_S.add_exc_msg
131 ( p_pkg_name => d_pkg_name,
132 p_procedure_name => d_api_name || '.' || d_position
133 );
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135 END draft_changes_exist;
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_distribution_id
151 -- distribution 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_distribution_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_distribution_id_tbl => PO_TBL_NUMBER(p_po_distribution_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 distribution 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
232 l_clm_document VARCHAR2(1);
233
234 BEGIN
235 d_position := 0;
236 IF (PO_LOG.d_proc) THEN
237 PO_LOG.proc_begin(d_module);
238 END IF;
239
240 IF (p_draft_info.distributions_changed = FND_API.G_FALSE) THEN
241 IF (PO_LOG.d_stmt) THEN
242 PO_LOG.stmt(d_module, d_position, 'no change-no need to apply');
243 END IF;
244
245 RETURN;
246 END IF;
247
248 d_position := 10;
249 group_records_by_dml_type
250 ( p_draft_info => p_draft_info
251 , x_delete_list => l_delete_list
252 , x_insert_list => l_insert_list
253 , x_update_list => l_update_list
254 );
255
256 -- Bug: 13948625
257 -- If any deleted Mod lines found, send the linked requisitions back to pool
258 -- if entire Clin-Slin structure eligible.
259 IF (l_delete_list. Count > 0) THEN
260
261 BEGIN
262 SELECT 'Y'
263 INTO l_clm_document
264 FROM po_headers_all h,
265 po_doc_style_headers pdsh,
266 po_distributions_all pd
267 WHERE h.style_id = pdsh.style_id
268 AND h.po_header_id = pd.po_header_id
269 AND pd.po_distribution_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 -- Bug: 13948625
278 -- Call update_req_for_linked_po_count, where the linked_po_count is updated for unlinked requisitions
279 -- and those reqs CLINs(Autocreated/SoftLinked) and PSlins(Softlinked) inserted into GT table for
280 -- further processing
281 -- This should be called only for CLM Standard document.
282 IF (l_clm_document = 'Y') THEN
283 PO_REQ_LINES_SV.update_req_for_linked_po_count (l_delete_list, 'DISTRIBUTION');
284 END IF;
285
286 END IF;
287
288 d_position := 20;
289 -- Merge Changes
290 PO_DISTRIBUTIONS_DRAFT_PKG.merge_changes
291 ( p_draft_id => p_draft_info.draft_id
292 );
293
294 d_position := 30;
295 EXCEPTION
296 WHEN OTHERS THEN
297 PO_MESSAGE_S.add_exc_msg
298 ( p_pkg_name => d_pkg_name,
299 p_procedure_name => d_api_name || '.' || d_position
300 );
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302 END apply_changes;
303
304 -------------------------------------------------------
305 -------------- PRIVATE PROCEDURES ---------------------
306 -------------------------------------------------------
307
308 -----------------------------------------------------------------------
309 --Start of Comments
310 --Name: group_records_by_dml_type
311 --Pre-reqs: None
312 --Modifies:
313 --Locks:
314 -- None
315 --Function:
316 -- Get all the draft records and separate them into three categories:
317 -- records to be deleted, inserted, and updated. The lists are returned
318 -- as arrays of numbers
319 --Parameters:
320 --IN:
321 --p_draft_info
322 -- record structure to hold draft information
323 --IN OUT:
324 --OUT:
325 --x_delete_list
326 -- IDs to be deleted from transaction table
327 --x_insert_list
328 -- IDs to be inserted in transaction table
329 --x_update_list
330 -- IDs to be updated in transaction table
331 --Returns:
332 --Notes:
333 --Testing:
334 --End of Comments
335 ------------------------------------------------------------------------
336 PROCEDURE group_records_by_dml_type
337 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
338 , x_delete_list OUT NOCOPY PO_TBL_NUMBER
339 , x_insert_list OUT NOCOPY PO_TBL_NUMBER
340 , x_update_list OUT NOCOPY PO_TBL_NUMBER
341 ) IS
342 d_api_name CONSTANT VARCHAR2(30) := 'group_records_by_dml_type';
343 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
344 d_position NUMBER;
345
346 l_id_list PO_TBL_NUMBER;
347 l_del_flag_list PO_TBL_VARCHAR1;
348 l_txn_exists_list PO_TBL_VARCHAR1;
349
350 BEGIN
351 d_position := 0;
352 IF (PO_LOG.d_proc) THEN
353 PO_LOG.proc_begin(d_module);
354 END IF;
355
356 x_delete_list := PO_TBL_NUMBER();
357 x_insert_list := PO_TBL_NUMBER();
358 x_update_list := PO_TBL_NUMBER();
359
360 d_position := 10;
361 SELECT PDD.po_distribution_id,
362 NVL(PDD.delete_flag, 'N'),
363 DECODE(PD.po_distribution_id, NULL, 'N', 'Y')
364 BULK COLLECT
365 INTO l_id_list,
366 l_del_flag_list,
367 l_txn_exists_list
368 FROM po_distributions_draft_all PDD,
369 po_distributions_all PD
370 WHERE PDD.draft_id = p_draft_info.draft_id
371 AND NVL(PDD.change_accepted_flag, 'Y') = 'Y'
372 AND PDD.po_distribution_id = PD.po_distribution_id(+);
373
374 d_position := 20;
375 FOR i IN 1..l_id_list.COUNT LOOP
376 IF (l_del_flag_list(i) = 'Y') THEN
377 IF (l_txn_exists_list(i) = 'Y') THEN
378 x_delete_list.extend;
379 x_delete_list(x_delete_list.LAST) := l_id_list(i);
380 END IF;
381 ELSE
382 IF (l_txn_exists_list(i) = 'Y') THEN
383 x_update_list.extend;
384 x_update_list(x_update_list.LAST) := l_id_list(i);
385 ELSE
386 x_insert_list.extend;
387 x_insert_list(x_insert_list.LAST) := l_id_list(i);
388 END IF;
389 END IF;
390 END LOOP;
391
392 d_position := 30;
393 EXCEPTION
394 WHEN OTHERS THEN
395 PO_MESSAGE_S.add_exc_msg
396 ( p_pkg_name => d_pkg_name,
397 p_procedure_name => d_api_name || '.' || d_position
398 );
399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 END group_records_by_dml_type;
401
402 END PO_DISTRIBUTIONS_DRAFT_PVT;