[Home] [Help]
PACKAGE BODY: APPS.PO_HEADERS_DRAFT_PVT
Source
1 PACKAGE BODY PO_HEADERS_DRAFT_PVT AS
2 /* $Header: PO_HEADERS_DRAFT_PVT.plb 120.4 2006/02/06 11:24 dedelgad noship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_HEADERS_DRAFT_PVT');
6
7 -----------------------------------------------------------------------
8 --Start of Comments
9 --Name: draft_changes_exist
10 --Pre-reqs: None
11 --Modifies:
12 --Locks:
13 -- None
14 --Function:
15 -- Checks whether there is any draft changes in the draft table
16 -- given the draft_id or draft_id + po_header_id
17 -- If only draft_id is provided, this program returns FND_API.G_TRUE for
18 -- any draft changes in this table for the draft
19 -- If the whole primary key is provided (draft_id + header id), then
20 -- it return true if there is draft for this particular record in
21 -- the draft table
22 --Parameters:
23 --IN:
24 --p_draft_id_tbl
25 -- draft unique identifier
26 --p_po_header_id_tbl
27 -- po header unique identifier
28 --IN OUT:
29 --OUT:
30 --Returns:
31 -- Array of flags indicating whether draft changes exist for the corresponding
32 -- entry in the input parameter. For each entry in the returning array:
33 -- FND_API.G_TRUE if there are draft changes
34 -- FND_API.G_FALSE if there aren't draft changes
35 --Notes:
36 --Testing:
37 --End of Comments
38 ------------------------------------------------------------------------
39 FUNCTION draft_changes_exist
40 ( p_draft_id_tbl IN PO_TBL_NUMBER,
41 p_po_header_id_tbl IN PO_TBL_NUMBER
42 ) RETURN PO_TBL_VARCHAR1
43 IS
44 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
45 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
46 d_position NUMBER;
47
48 l_key NUMBER;
49 l_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
50 l_dft_exists_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
51 l_dft_exists_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
52
53 BEGIN
54 d_position := 0;
55 IF (PO_LOG.d_proc) THEN
56 PO_LOG.proc_begin(d_module);
57 END IF;
58
59 l_index_tbl.extend(p_draft_id_tbl.COUNT);
60 l_dft_exists_tbl.extend(p_draft_id_tbl.COUNT);
61
62 FOR i IN 1..l_index_tbl.COUNT LOOP
63 l_index_tbl(i) := i;
64 l_dft_exists_tbl(i) := FND_API.G_FALSE;
65 END LOOP;
66
67 d_position := 10;
68
69 l_key := PO_CORE_S.get_session_gt_nextval;
70
71 d_position := 20;
72
73 FORALL i IN 1..p_draft_id_tbl.COUNT
74 INSERT INTO po_session_gt
75 ( key,
76 num1
77 )
78 SELECT l_key,
79 l_index_tbl(i)
80 FROM DUAL
81 WHERE EXISTS (SELECT 1
82 FROM po_headers_draft_all PHD
83 WHERE PHD.draft_id = p_draft_id_tbl(i)
84 AND PHD.po_header_id = NVL(p_po_header_id_tbl(i),
85 PHD.po_header_id)
86 AND NVL(PHD.change_accepted_flag, 'Y') = 'Y');
87
88
89 d_position := 30;
90
91 -- All the num1 returned from this DELETE statement are indexes for
92 -- records that contain draft changes
93 DELETE FROM po_session_gt
94 WHERE key = l_key
95 RETURNING num1
96 BULK COLLECT INTO l_dft_exists_index_tbl;
97
98 d_position := 40;
99
100 FOR i IN 1..l_dft_exists_index_tbl.COUNT LOOP
101 l_dft_exists_tbl(l_dft_exists_index_tbl(i)) := FND_API.G_TRUE;
102 END LOOP;
103
104 IF (PO_LOG.d_stmt) THEN
105 PO_LOG.stmt(d_module, d_position, '# of records that have dft changes',
106 l_dft_exists_index_tbl.COUNT);
107 END IF;
108
109 RETURN l_dft_exists_tbl;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 PO_MESSAGE_S.add_exc_msg
114 ( p_pkg_name => d_pkg_name,
115 p_procedure_name => d_api_name || '.' || d_position
116 );
117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
118 END draft_changes_exist;
119
120
121
122 -----------------------------------------------------------------------
123 --Start of Comments
124 --Name: draft_changes_exist
125 --Pre-reqs: None
126 --Modifies:
127 --Locks:
128 -- None
129 --Function:
130 -- Same functionality as the bulk version of draft_changes_exist
131 --Parameters:
132 --IN:
133 --p_draft_id
134 -- draft unique identifier
135 --p_po_header_id
136 -- po header unique identifier
137 --IN OUT:
138 --OUT:
139 --Returns:
140 -- FND_API.G_TRUE if there are draft changes
141 -- FND_API.G_FALSE if there aren't draft changes
142 --Notes:
143 --Testing:
144 --End of Comments
145 ------------------------------------------------------------------------
146 FUNCTION draft_changes_exist
147 ( p_draft_id IN NUMBER,
148 p_po_header_id IN NUMBER
149 ) RETURN VARCHAR2
150 IS
151 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
152 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
153 d_position NUMBER;
154
155 l_exists_tbl PO_TBL_VARCHAR1;
156 BEGIN
157 d_position := 0;
158 IF (PO_LOG.d_proc) THEN
159 PO_LOG.proc_begin(d_module);
160 END IF;
161
162 l_exists_tbl :=
163 draft_changes_exist
164 ( p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
165 p_po_header_id_tbl => PO_TBL_NUMBER(p_po_header_id)
166 );
167
168 IF (PO_LOG.d_stmt) THEN
169 PO_LOG.stmt(d_module, d_position, 'exists', l_exists_tbl(1));
170 END IF;
171
172 RETURN l_exists_tbl(1);
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 PO_MESSAGE_S.add_exc_msg
177 ( p_pkg_name => d_pkg_name,
178 p_procedure_name => d_api_name || '.' || d_position
179 );
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END draft_changes_exist;
182
183
184 -----------------------------------------------------------------------
185 --Start of Comments
186 --Name: apply_changes
187 --Pre-reqs: None
188 --Modifies:
189 --Locks:
190 -- None
191 --Function:
192 -- Process header draft records and merge them to transaction table. It
193 -- also performs all additional work related specifically to the merge
194 -- action
195 --Parameters:
196 --IN:
197 --p_draft_info
198 -- data structure storing draft information
199 --IN OUT:
200 --OUT:
201 --Returns:
202 --Notes:
203 --Testing:
204 --End of Comments
205 ------------------------------------------------------------------------
206 PROCEDURE apply_changes
207 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
208 ) IS
209
210 d_api_name CONSTANT VARCHAR2(30) := 'apply_changes';
211 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
212 d_position NUMBER;
213
214 l_delete_flag PO_HEADERS_DRAFT_ALL.delete_flag%TYPE;
215 l_record_exists_in_txn VARCHAR2(1);
216 l_dml_operation VARCHAR2(10);
217 l_vendor_changed VARCHAR2(1);
218 l_vendor_site_changed VARCHAR2(1);
219 l_conterms_exist_flag PO_HEADERS_ALL.conterms_exist_flag%TYPE;
220 l_new_vendor_id PO_HEADERS_ALL.vendor_id%TYPE;
221 l_new_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE;
222 l_contract_document_type VARCHAR2(20);
223
224 l_return_status VARCHAR2(1);
225 l_msg_data VARCHAR2(2000);
226 l_msg_count NUMBER;
227 BEGIN
228 d_position := 0;
229 IF (PO_LOG.d_proc) THEN
230 PO_LOG.proc_begin(d_module);
231 END IF;
232
233 IF (p_draft_info.headers_changed = FND_API.G_FALSE) THEN
234 IF (PO_LOG.d_stmt) THEN
235 PO_LOG.stmt(d_module, d_position, 'no change-no need to apply');
236 END IF;
237
238 RETURN;
239 END IF;
240
241 d_position := 10;
242 SELECT NVL(PHD.delete_flag, 'N'),
243 DECODE (PH.po_header_id, NULL, 'N', 'Y')
244 INTO l_delete_flag, l_record_exists_in_txn
245 FROM po_headers_draft_all PHD,
246 po_headers_all PH
247 WHERE PHD.draft_id = p_draft_info.draft_id
248 AND PHD.po_header_id = p_draft_info.po_header_Id
249 AND NVL(PHD.change_accepted_flag, 'Y') = 'Y'
250 AND PHD.po_header_id = PH.po_header_id(+);
251
252 l_dml_operation := NULL;
253
254 IF (l_delete_flag = 'Y') THEN
255 IF (l_record_exists_in_txn = 'Y') THEN
256 l_dml_operation := 'DELETE';
257 END IF;
258 ELSE
259 IF (l_record_exists_in_txn = 'Y') THEN
260 l_dml_operation := 'UPDATE';
261 ELSE
262 l_dml_operation := 'INSERT';
263 END IF;
264 END IF;
265
266 d_position := 20;
267 IF (l_dml_operation = 'UPDATE') THEN
268 SELECT DECODE (PHD.vendor_id, PH.vendor_id, 'Y', 'N'),
269 DECODE (PHD.vendor_site_id, PH.vendor_site_id, 'Y', 'N'),
270 NVL(PH.conterms_exist_flag, 'N'),
271 PHD.vendor_id,
272 PHD.vendor_site_id
273 INTO l_vendor_changed,
274 l_vendor_site_changed,
275 l_conterms_exist_flag,
276 l_new_vendor_id,
277 l_new_vendor_site_id
278 FROM po_headers_draft_all PHD,
279 po_headers_all PH
280 WHERE PHD.draft_id = p_draft_info.draft_id
281 AND PHD.po_header_id = p_draft_info.po_header_id
282 AND PHD.po_header_id = PH.po_header_id;
283 END IF;
284
285 IF (l_dml_operation = 'DELETE') THEN
286 -- No need to delete children because OA will handle it
287 NULL;
288 END IF;
289
290 d_position := 30;
291 -- transfer changes from draft to txn table
292 PO_HEADERS_DRAFT_PKG.merge_changes
293 ( p_draft_id => p_draft_info.draft_id
294 );
295
296 d_position := 40;
297 IF (l_dml_operation = 'DELETE') THEN
298
299 d_position := 50;
300
301 --TODO: delete action history (Phase 2)
302
303 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments
304 ( 'PO_HEADERS',
305 p_draft_info.po_header_id,
306 '', '', '', '', 'Y');
307
308 SELECT NVL(PH.conterms_exist_flag, 'N')
309 INTO l_conterms_exist_flag
310 FROM po_headers_all PH
311 WHERE po_header_id = p_draft_info.po_header_id;
312
313 l_contract_document_type :=
314 PO_CONTERMS_UTL_GRP.get_po_contract_doctype
315 ( p_sub_doc_type => p_draft_info.doc_subtype
316 );
317
318 -- call contract api to delete contract terms
319 OKC_TERMS_UTIL_GRP.delete_doc
320 ( p_api_version => 1.0
321 , p_init_msg_list => FND_API.G_TRUE
322 , p_commit => FND_API.G_FALSE
323 , p_doc_id => p_draft_info.po_header_id
324 , p_doc_type => l_contract_document_type
325 , p_validate_commit => FND_API.G_FALSE
326 , x_return_status => l_return_status
327 , x_msg_data => l_msg_data
328 , x_msg_count => l_msg_count
329 );
330
331 d_position := 60;
332 IF (l_return_status <> 'S') THEN
333 -- display error
334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
335 END IF;
336 ELSIF (l_dml_operation = 'UPDATE') THEN
337
338 d_position := 70;
339 IF ( (l_vendor_changed = 'Y' OR l_vendor_site_changed = 'Y')
340 AND l_conterms_exist_flag = 'Y') THEN
341
342 l_contract_document_type :=
343 PO_CONTERMS_UTL_GRP.get_po_contract_doctype
344 ( p_sub_doc_type => p_draft_info.doc_subtype
345 );
346
347 OKC_MANAGE_DELIVERABLES_GRP.updateExtPartyOnDeliverables
348 ( p_api_version => 1.0
349 , p_bus_doc_id => p_draft_info.po_header_id
350 , p_bus_doc_type => l_contract_document_type
351 , p_external_party_id => l_new_vendor_id
352 , p_external_party_site_id => l_new_vendor_site_id
353 , x_msg_data => l_msg_data
354 , x_msg_count => l_msg_count
355 , x_return_status => l_return_status
356 );
357
358 d_position := 80;
359 IF (l_return_status <> 'S') THEN
360 -- display error
361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
362 END IF;
363
364 END IF;
365
366 END IF;
367
368 d_position := 90;
369
370 d_position := 110;
371 EXCEPTION
372 WHEN OTHERS THEN
373 PO_MESSAGE_S.add_exc_msg
374 ( p_pkg_name => d_pkg_name,
375 p_procedure_name => d_api_name || '.' || d_position
376 );
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 END apply_changes;
379
380 END PO_HEADERS_DRAFT_PVT;