[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 2006/02/06 12:10 dedelgad noship $ */
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 BEGIN
229 d_position := 0;
230 IF (PO_LOG.d_proc) THEN
231 PO_LOG.proc_begin(d_module);
232 END IF;
233
234 IF (p_draft_info.distributions_changed = FND_API.G_FALSE) THEN
235 IF (PO_LOG.d_stmt) THEN
236 PO_LOG.stmt(d_module, d_position, 'no change-no need to apply');
237 END IF;
238
239 RETURN;
240 END IF;
241
242 d_position := 20;
243 -- Merge Changes
244 PO_DISTRIBUTIONS_DRAFT_PKG.merge_changes
245 ( p_draft_id => p_draft_info.draft_id
246 );
247
248 d_position := 30;
249 EXCEPTION
250 WHEN OTHERS THEN
251 PO_MESSAGE_S.add_exc_msg
252 ( p_pkg_name => d_pkg_name,
253 p_procedure_name => d_api_name || '.' || d_position
254 );
255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
256 END apply_changes;
257
258 -------------------------------------------------------
259 -------------- PRIVATE PROCEDURES ---------------------
260 -------------------------------------------------------
261
262 -----------------------------------------------------------------------
263 --Start of Comments
264 --Name: group_records_by_dml_type
265 --Pre-reqs: None
266 --Modifies:
267 --Locks:
268 -- None
269 --Function:
270 -- Get all the draft records and separate them into three categories:
271 -- records to be deleted, inserted, and updated. The lists are returned
272 -- as arrays of numbers
273 --Parameters:
274 --IN:
275 --p_draft_info
276 -- record structure to hold draft information
277 --IN OUT:
278 --OUT:
279 --x_delete_list
280 -- IDs to be deleted from transaction table
281 --x_insert_list
282 -- IDs to be inserted in transaction table
283 --x_update_list
284 -- IDs to be updated in transaction table
285 --Returns:
286 --Notes:
287 --Testing:
288 --End of Comments
289 ------------------------------------------------------------------------
290 PROCEDURE group_records_by_dml_type
291 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
292 , x_delete_list OUT NOCOPY PO_TBL_NUMBER
293 , x_insert_list OUT NOCOPY PO_TBL_NUMBER
294 , x_update_list OUT NOCOPY PO_TBL_NUMBER
295 ) IS
296 d_api_name CONSTANT VARCHAR2(30) := 'group_records_by_dml_type';
297 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
298 d_position NUMBER;
299
300 l_id_list PO_TBL_NUMBER;
301 l_del_flag_list PO_TBL_VARCHAR1;
302 l_txn_exists_list PO_TBL_VARCHAR1;
303
304 BEGIN
305 d_position := 0;
306 IF (PO_LOG.d_proc) THEN
307 PO_LOG.proc_begin(d_module);
308 END IF;
309
310 x_delete_list := PO_TBL_NUMBER();
311 x_insert_list := PO_TBL_NUMBER();
312 x_update_list := PO_TBL_NUMBER();
313
314 d_position := 10;
315 SELECT PDD.po_distribution_id,
316 NVL(PDD.delete_flag, 'N'),
317 DECODE(PD.po_distribution_id, NULL, 'N', 'Y')
318 BULK COLLECT
319 INTO l_id_list,
320 l_del_flag_list,
321 l_txn_exists_list
322 FROM po_distributions_draft_all PDD,
323 po_distributions_all PD
324 WHERE PDD.draft_id = p_draft_info.draft_id
325 AND NVL(PDD.change_accepted_flag, 'Y') = 'Y'
326 AND PDD.po_distribution_id = PD.po_distribution_id(+);
327
328 d_position := 20;
329 FOR i IN 1..l_id_list.COUNT LOOP
330 IF (l_del_flag_list(i) = 'Y') THEN
331 IF (l_txn_exists_list(i) = 'Y') THEN
332 x_delete_list.extend;
333 x_delete_list(x_delete_list.LAST) := l_id_list(i);
334 END IF;
335 ELSE
336 IF (l_txn_exists_list(i) = 'Y') THEN
337 x_update_list.extend;
338 x_update_list(x_update_list.LAST) := l_id_list(i);
339 ELSE
340 x_insert_list.extend;
341 x_insert_list(x_insert_list.LAST) := l_id_list(i);
342 END IF;
343 END IF;
344 END LOOP;
345
346 d_position := 30;
347 EXCEPTION
348 WHEN OTHERS THEN
349 PO_MESSAGE_S.add_exc_msg
350 ( p_pkg_name => d_pkg_name,
351 p_procedure_name => d_api_name || '.' || d_position
352 );
353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
354 END group_records_by_dml_type;
355
356 END PO_DISTRIBUTIONS_DRAFT_PVT;