1 PACKAGE BODY PO_Document_Control_PUB AS
2 /* $Header: POXPDCOB.pls 120.2.12020000.2 2013/02/11 00:38:01 vegajula ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
8
9
10 /**
11 * Public Procedure: control_document
12 * Requires: API message list has been initialized if p_init_msg_list is false.
13 * Modifies: All columns related to the control action, and who columns. The API
14 * message list.
15 * Effects: Performs the control action p_action on the specified document.
16 * Currently, only the 'CANCEL' action is supported. If the control action was
17 * successful, the document will be updated at the specified entity level.
18 * Derives any ID if the ID is NULL, but the matching number is passed in. If
19 * both the ID and number are passed in, the ID is used. Executes at shipment
20 * level if the final doc_id, line_id, and line_loc_id are not NULL. Executes
21 * at line level if only the final doc_id and line_id are not NULL. Executes
22 * at header level if only the final doc_id is not NULL. The document will be
23 * printed if it is a PO, PA, or RELEASE, and the p_print_flag is 'Y'. All
24 * changes will be committed upon success if p_commit is FND_API.G_TRUE.
25 * Appends to API message list on error, and leaves the document unchanged.
26 * Returns:
27 * x_return_status - FND_API.G_RET_STS_SUCCESS if control action succeeds
28 * FND_API.G_RET_STS_ERROR if control action fails
29 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
30 */
31 PROCEDURE control_document
32 (p_api_version IN NUMBER,
33 p_init_msg_list IN VARCHAR2,
34 p_commit IN VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2,
36 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
37 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
38 p_doc_id IN NUMBER,
39 p_doc_num IN PO_HEADERS.segment1%TYPE,
40 p_release_id IN NUMBER,
41 p_release_num IN NUMBER,
42 p_doc_line_id IN NUMBER,
43 p_doc_line_num IN NUMBER,
44 p_doc_line_loc_id IN NUMBER,
45 p_doc_shipment_num IN NUMBER,
46 p_action IN VARCHAR2,
47 p_action_date IN DATE,
48 p_cancel_reason IN PO_LINES.cancel_reason%TYPE,
49 p_cancel_reqs_flag IN VARCHAR2,
50 p_print_flag IN VARCHAR2,
51 p_note_to_vendor IN PO_HEADERS.note_to_vendor%TYPE,
52 p_use_gldate IN VARCHAR2, -- <ENCUMBRANCE FPJ>
53 p_org_id IN NUMBER, --<Bug#4581621>
54 p_launch_approvals_flag IN VARCHAR2 --<Bug#14605476>
55 )
56 IS
57
58 l_api_name CONSTANT VARCHAR2(30) := 'control_document';
59 l_api_version CONSTANT NUMBER := 1.0;
60 l_org_id PO_HEADERS_ALL.org_id%type := p_org_id;
61 BEGIN
62 -- Start standard API initialization
63 SAVEPOINT control_document_PUB;
64 IF FND_API.to_boolean(p_init_msg_list) THEN
65 FND_MSG_PUB.initialize;
66 END IF;
67 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
68 l_api_name, g_pkg_name)
69 THEN
70 RAISE FND_API.g_exc_unexpected_error;
71 END IF;
72 x_return_status := FND_API.g_ret_sts_success;
73 -- End standard API initialization
74
75 IF (g_fnd_debug = 'Y') THEN
76 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
77 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
78 '.invoked', 'Action: ' || NVL(p_action,'null') ||
79 ', Type: ' || NVL(p_doc_type,'null') ||
80 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
81 END IF;
82 END IF;
83
84 --<Bug#4581621 Start>
85 PO_MOAC_UTILS_PVT.validate_orgid_pub_api(x_org_id => l_org_id);
86 PO_MOAC_UTILS_PVT.set_policy_context('S',l_org_id);
87 --<Bug#4581621 End>
88
89 PO_Document_Control_GRP.control_document
90 (p_api_version => 1.0,
91 p_init_msg_list => FND_API.G_FALSE,
92 p_commit => FND_API.G_FALSE,
93 x_return_status => x_return_status,
94 p_doc_type => p_doc_type,
95 p_doc_subtype => p_doc_subtype,
96 p_doc_id => p_doc_id,
97 p_doc_num => p_doc_num,
98 p_release_id => p_release_id,
99 p_release_num => p_release_num,
100 p_doc_line_id => p_doc_line_id,
101 p_doc_line_num => p_doc_line_num,
102 p_doc_line_loc_id => p_doc_line_loc_id,
103 p_doc_shipment_num => p_doc_shipment_num,
104 p_source => NULL, -- p_source is currently unresolved
105 p_action => p_action,
106 p_action_date => p_action_date,
107 p_cancel_reason => p_cancel_reason,
108 p_cancel_reqs_flag => p_cancel_reqs_flag,
109 p_print_flag => p_print_flag,
110 p_note_to_vendor => p_note_to_vendor,
111 p_use_gldate => p_use_gldate, -- <ENCUMBRANCE FPJ>
112 p_launch_approvals_flag => p_launch_approvals_flag --<Bug#14605476>
113 );
114
118 RAISE FND_API.g_exc_unexpected_error;
115 IF (x_return_status = FND_API.g_ret_sts_error) THEN
116 RAISE FND_API.g_exc_error;
117 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
119 END IF;
120
121
122 -- Standard API check of p_commit
123 IF FND_API.to_boolean(p_commit) THEN
124 COMMIT WORK;
125 END IF;
126 EXCEPTION
127 WHEN FND_API.g_exc_error THEN
128 ROLLBACK TO control_document_PUB;
129 x_return_status := FND_API.g_ret_sts_error;
130 WHEN FND_API.g_exc_unexpected_error THEN
131 ROLLBACK TO control_document_PUB;
132 x_return_status := FND_API.g_ret_sts_unexp_error;
133 WHEN OTHERS THEN
134 ROLLBACK TO control_document_PUB;
135 x_return_status := FND_API.g_ret_sts_unexp_error;
136 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
137 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
138 IF (g_fnd_debug = 'Y') THEN
139 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
140 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
141 l_api_name || '.others_exception', 'Exception');
142 END IF;
143 END IF;
144 END IF;
145 END control_document;
146
147
148
149 /**
150 * Public Procedure: control_document
151 * Requires: API message list has been initialized if p_init_msg_list is false.
152 * Modifies: All columns related to the control action, and who columns. The API
153 * message list.
154 * Effects: Performs the control action p_action on the specified document.
155 * Currently, only the 'CANCEL' action is supported. If the control action was
156 * successful, the document will be updated at the specified entity level.
157 * Derives any ID if the ID is NULL, but the matching number is passed in. If
158 * both the ID and number are passed in, the ID is used. Executes at shipment
159 * level if the final doc_id, line_id, and line_loc_id are not NULL. Executes
160 * at line level if only the final doc_id and line_id are not NULL. Executes
161 * at header level if only the final doc_id is not NULL. The document will be
162 * printed if it is a PO, PA, or RELEASE, and the p_print_flag is 'Y'. All
163 * changes will be committed upon success if p_commit is FND_API.G_TRUE.
164 * Appends to API message list on error, and leaves the document unchanged.
165 * Returns:
166 * x_return_status - FND_API.G_RET_STS_SUCCESS if control action succeeds
167 * FND_API.G_RET_STS_ERROR if control action fails
168 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
169 */
170
171 PROCEDURE control_document(
172 p_api_version IN NUMBER,
173 p_init_msg_list IN VARCHAR2,
174 p_commit IN VARCHAR2,
175 x_return_status OUT NOCOPY VARCHAR2,
176 po_doc_tbl IN po_document_control_pub.PO_DTLS_REC_TBL,
177 p_action IN VARCHAR2,
178 p_action_date IN DATE,
179 p_cancel_reason IN PO_LINES.cancel_reason%TYPE,
180 p_cancel_reqs_flag IN VARCHAR2,
181 p_print_flag IN VARCHAR2,
182 p_revert_chg_flag IN VARCHAR2,
183 p_launch_approvals_flag IN VARCHAR2,
184 p_note_to_vendor IN PO_HEADERS.note_to_vendor%TYPE,
185 p_use_gldate IN VARCHAR2 DEFAULT NULL,
186 p_org_id IN NUMBER DEFAULT NULL
187 )
188 IS
189
190 l_api_name CONSTANT VARCHAR2(30) := 'control_document';
191 l_api_version CONSTANT NUMBER := 1.0;
192 l_org_id PO_HEADERS_ALL.org_id%type := p_org_id;
193
194
195 l_entity_dtl_rec_tbl po_document_action_pvt.entity_dtl_rec_type_tbl;
196 l_online_report_id NUMBER;
197 l_exc_msg VARCHAR2(2000);
198 l_return_code VARCHAR2(25);
199 l_communication_method_option VARCHAR2(30);
200 l_communication_method_value VARCHAR2(30);
201 l_old_auth_status_tbl PO_TBL_VARCHAR30;
202 l_doc_id_tbl po_tbl_number :=PO_TBL_NUMBER();
203 id_count NUMBER :=0;
204 l_doc_id NUMBER;
205 l_doc_line_id NUMBER;
206 l_doc_line_loc_id NUMBER;
207
208
209
210 BEGIN
211
212 -- Start standard API initialization
213 SAVEPOINT control_document_PUB;
214 IF FND_API.to_boolean(p_init_msg_list) THEN
215 FND_MSG_PUB.initialize;
216 END IF;
217
218 IF NOT FND_API.compatible_api_call(
219 l_api_version, p_api_version,
220 l_api_name, g_pkg_name)
221 THEN
222 RAISE FND_API.g_exc_unexpected_error;
223 END IF;
224
225 x_return_status := FND_API.g_ret_sts_success;
226 -- End standard API initialization
227
228 IF (g_fnd_debug = 'Y') THEN
229 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
230 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
231 '.invoked', 'Action: ' || NVL(p_action,'null') );
232 END IF;
233 END IF;
234
235 PO_MOAC_UTILS_PVT.validate_orgid_pub_api(x_org_id => l_org_id);
236 PO_MOAC_UTILS_PVT.set_policy_context('S',l_org_id);
237
238
239 -- Validate the action parameter
240 IF (p_action NOT IN ('CANCEL')) THEN
241 FND_MESSAGE.set_name('PO','PO_CONTROL_INVALID_ACTION');
242 FND_MESSAGE.set_token('ACTION',p_action);
243
244 IF (g_fnd_debug = 'Y') THEN
245 IF(FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
246 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name ||
250
247 '.invalid_action', FALSE);
248 END IF;
249 END IF;
251 FND_MSG_PUB.add;
252 RAISE FND_API.g_exc_error;
253 END IF;
254
255
256 l_entity_dtl_rec_tbl := po_document_action_pvt.entity_dtl_rec_type_tbl();
257 l_old_auth_status_tbl:= PO_TBL_VARCHAR30();
258
259 FOR i IN po_doc_tbl.FIRST..po_doc_tbl.LAST LOOP
260
261 -- Validates the document details and returns the document Id corresponding to the input values
262 -- Input to the routine can be document id or document numbers
263 -- For the p_doc_id/p_doc_num, it returns l_doc_id i.e. corresponding PO_HEADER_ID
264 -- For the p_release_id/p_release_num, it returns l_doc_id i.e. corresponding PO_RELEASE_ID
265 -- For the p_doc_line_id/p_doc_line_num, it returns l_doc_line_id i.e. corresponding PO_LINE_ID
266 -- For the p_doc_line_loc_id/p_doc_shipment_num, it returns l_doc_line_loc_id i.e. corresponding LINE_LOCATION_ID
267
268 PO_Document_Control_GRP.val_doc_params(
269 p_api_version => 1.0,
270 p_init_msg_list => FND_API.G_FALSE,
271 x_return_status => x_return_status,
272 p_doc_type => po_doc_tbl(i).p_doc_type,
273 p_doc_subtype => po_doc_tbl(i).p_doc_subtype,
274 p_doc_id => po_doc_tbl(i).p_doc_id,
275 p_doc_num => po_doc_tbl(i).p_doc_num,
276 p_doc_line_id => po_doc_tbl(i).p_doc_line_id,
277 p_doc_line_num => po_doc_tbl(i).p_doc_line_num,
278 p_release_id => po_doc_tbl(i).p_release_id,
279 p_release_num => po_doc_tbl(i).p_release_num,
280 p_doc_line_loc_id => po_doc_tbl(i).p_doc_line_loc_id,
281 p_doc_shipment_num => po_doc_tbl(i).p_doc_shipment_num,
282 x_doc_id => l_doc_id,
283 x_doc_line_id => l_doc_line_id,
284 x_doc_line_loc_id => l_doc_line_loc_id);
285
286
287 IF (x_return_status = FND_API.g_ret_sts_error) THEN
288 RAISE FND_API.g_exc_error;
289 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
290 RAISE FND_API.g_exc_unexpected_error;
291 END IF;
292
293 l_entity_dtl_rec_tbl.extend;
294 l_entity_dtl_rec_tbl(i).doc_id := l_doc_id;
295 l_entity_dtl_rec_tbl(i).document_type := po_doc_tbl(i).p_doc_type;
296 l_entity_dtl_rec_tbl(i).document_subtype := po_doc_tbl(i).p_doc_subtype;
297
298 IF l_doc_line_loc_id IS NOT NULL THEN
299 l_entity_dtl_rec_tbl(i).entity_level :=PO_Document_Cancel_PVT.c_entity_level_SHIPMENT;
300 l_entity_dtl_rec_tbl(i).entity_id := l_doc_line_loc_id;
301
302 ELSIF l_doc_line_id IS NOT NULL THEN
303 l_entity_dtl_rec_tbl(i).entity_level :=PO_Document_Cancel_PVT.c_entity_level_LINE;
304 l_entity_dtl_rec_tbl(i).entity_id := l_doc_line_id;
305
306 ELSE
307 l_entity_dtl_rec_tbl(i).entity_level :=PO_Document_Cancel_PVT.c_entity_level_HEADER;
308 l_entity_dtl_rec_tbl(i).entity_id := l_doc_id;
309 END IF;
310
311
312 l_entity_dtl_rec_tbl(i).entity_action_date := p_action_date;
313 l_entity_dtl_rec_tbl(i).process_entity_flag := 'Y';
314 l_entity_dtl_rec_tbl(i).recreate_demand_flag := 'N';
315
316
317 BEGIN
318
319 l_old_auth_status_tbl.extend;
320
321 IF (po_doc_tbl(i).p_doc_type = 'RELEASE') THEN
322
323 SELECT authorization_status
324 INTO l_old_auth_status_tbl(i)
325 FROM po_releases_all
326 WHERE po_release_id = l_doc_id;
327
328 ELSE
329
330 SELECT authorization_status
331 INTO l_old_auth_status_tbl(i)
332 FROM po_headers_all
333 WHERE po_header_id= l_doc_id;
334
335 END IF;
336
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN
339 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
340 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
341 'API control_document exception', 'Authorization Status not found for '||po_doc_tbl(i).p_doc_type);
342 END IF;
343 END;
344
345 END LOOP;
346
347 -- Cancel the entity
348 PO_DOCUMENT_ACTION_PVT.do_cancel(
349 p_entity_dtl_rec => l_entity_dtl_rec_tbl,
350 p_reason => p_cancel_reason,
351 p_action => PO_DOCUMENT_ACTION_PVT.g_doc_action_CANCEL,
352 p_action_date => p_action_date,
353 p_use_gl_date => p_use_gldate,
354 p_cancel_reqs_flag => p_cancel_reqs_flag,
355 p_note_to_vendor => p_note_to_vendor,
356 p_caller => PO_DOCUMENT_CANCEL_PVT.c_CANCEL_API,
357 x_online_report_id => l_online_report_id,
358 p_commit => p_commit,
359 x_return_status => x_return_status,
360 x_exception_msg => l_exc_msg,
361 x_return_code => l_return_code);
362
363
364 -- If the procedure does not complete successfully raise the
365 -- appropriate exception
366 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
367 RAISE FND_API.g_exc_error;
368 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
369 RAISE FND_API.g_exc_unexpected_error;
370 END IF;
371
372
373
374 IF p_print_flag ='Y' THEN
375 l_communication_method_option := 'PRINT';
376 l_communication_method_value :=NULL;
377 END IF;
378
382 AND l_entity_dtl_rec_tbl(i).process_entity_flag ='Y' THEN
379 FOR i IN 1..l_entity_dtl_rec_tbl.Count LOOP
380
381 IF NOT (l_doc_id_tbl.EXISTS(l_entity_dtl_rec_tbl(i).doc_id))
383
384 IF (p_launch_approvals_flag = 'Y'
385 AND l_old_auth_status_tbl(i) ='APPROVED') THEN
386
387 PO_Document_Control_PVT.do_approve_on_cancel(
388 p_doc_type => l_entity_dtl_rec_tbl(i).document_type,
389 p_doc_subtype => l_entity_dtl_rec_tbl(i).document_subtype,
390 p_doc_id => l_entity_dtl_rec_tbl(i).doc_id,
391 p_communication_method_option => l_communication_method_option,
392 p_communication_method_value => l_communication_method_value,
393 p_note_to_vendor => p_note_to_vendor,
394 p_source => PO_DOCUMENT_CANCEL_PVT.c_CANCEL_API,
395 x_exception_msg => l_exc_msg,
396 x_return_status => x_return_status);
397
398 IF (x_return_status = FND_API.g_ret_sts_error) THEN
399 RAISE FND_API.g_exc_error;
400 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
401 RAISE FND_API.g_exc_unexpected_error;
402 END IF;
403
404 END IF;
405
406 -- If the Cancel action is successful
407 -- Communicate the same to the supplier
408 PO_Document_Control_PVT.doc_communicate_oncancel(
409 p_doc_type => l_entity_dtl_rec_tbl(i).document_type,
410 p_doc_subtype => l_entity_dtl_rec_tbl(i).document_subtype,
411 p_doc_id => l_entity_dtl_rec_tbl(i).doc_id,
412 p_communication_method_option => l_communication_method_option,
413 p_communication_method_value => l_communication_method_value,
414 x_return_status => x_return_status );
415
416 -- If the procedure does not complete successfully raise the
417 -- appropriate exception
418 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
419 RAISE FND_API.g_exc_error;
420 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
421 RAISE FND_API.g_exc_unexpected_error;
422 END IF;
423
424 l_doc_id_tbl.extend;
425 id_count:=id_count+1;
426 l_doc_id_tbl(id_count):=l_entity_dtl_rec_tbl(i).doc_id;
427
428 END IF;
429
430 END LOOP;
431
432 --Add all the messages to the message list
433 IF l_return_code ='F' AND l_online_report_id IS NOT NULL THEN
434 PO_Document_Control_PVT.add_online_report_msgs(
435 p_api_version => 1.0,
436 p_init_msg_list => FND_API.G_FALSE,
437 x_return_status => x_return_status,
438 p_online_report_id => l_online_report_id);
439
440 RAISE FND_API.g_exc_error;
441 END IF;
442
443
444
445 -- Standard API check of p_commit
446 IF FND_API.to_boolean(p_commit) THEN
447 COMMIT WORK;
448 END IF;
449 EXCEPTION
450 WHEN FND_API.g_exc_error THEN
451 ROLLBACK TO control_document_PUB;
452 x_return_status := FND_API.g_ret_sts_error;
453 WHEN FND_API.g_exc_unexpected_error THEN
454 ROLLBACK TO control_document_PUB;
455 x_return_status := FND_API.g_ret_sts_unexp_error;
456 WHEN OTHERS THEN
457 ROLLBACK TO control_document_PUB;
458 x_return_status := FND_API.g_ret_sts_unexp_error;
459 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
460 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
461 IF (g_fnd_debug = 'Y') THEN
462 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
463 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
464 l_api_name || '.others_exception', 'Exception');
465 END IF;
466 END IF;
467 END IF;
468 END control_document;
469
470
471 END PO_Document_Control_PUB;
[Home] [Help]