1 PACKAGE BODY PO_Document_Control_GRP AS
2 /* $Header: POXGDCOB.pls 120.5.12020000.2 2013/02/10 20:07:39 vegajula ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_fnd_debug CONSTANT 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 * Public Procedure: control_document
11 * Requires: API message list has been initialized if p_init_msg_list is false.
12 * Modifies: All columns related to the control action, and who columns. The API
13 * message list.
14 * Effects: Performs the control action p_action on the specified document.
15 * Currently, only the 'CANCEL' action is supported. If the control action was
16 * successful, the document will be updated at the specified entity level.
17 * Derives any ID if the ID is NULL, but the matching number is passed in. If
18 * both the ID and number are passed in, the ID is used. Executes at shipment
19 * level if the final doc_id, line_id, and line_loc_id are not NULL. Executes
20 * at line level if only the final doc_id and line_id are not NULL. Executes
21 * at header level if only the final doc_id is not NULL. The document will be
22 * printed if it is a PO, PA, or RELEASE, and the p_print_flag is 'Y'. All
23 * changes will be committed upon success if p_commit is FND_API.G_TRUE.
24 * Appends to API message list on error, and leaves the document unchanged.
25 * Returns:
26 * x_return_status - FND_API.G_RET_STS_SUCCESS if control action succeeds
27 * FND_API.G_RET_STS_ERROR if control action fails
28 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
29 */
30 PROCEDURE control_document
31 (p_api_version IN NUMBER,
32 p_init_msg_list IN VARCHAR2,
33 p_commit IN VARCHAR2,
34 x_return_status OUT NOCOPY VARCHAR2,
35 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
36 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
37 p_doc_id IN NUMBER,
38 p_doc_num IN PO_HEADERS.segment1%TYPE,
39 p_release_id IN NUMBER,
40 p_release_num IN NUMBER,
41 p_doc_line_id IN NUMBER,
42 p_doc_line_num IN NUMBER,
43 p_doc_line_loc_id IN NUMBER,
44 p_doc_shipment_num IN NUMBER,
45 p_source IN VARCHAR2,
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_launch_approvals_flag IN VARCHAR2, -- Bug#8224603
54 p_caller IN VARCHAR2 --Bug9321845(6603493)
55 )
56 IS
57
58 l_api_name CONSTANT VARCHAR2(30) := 'control_document';
59 l_api_version CONSTANT NUMBER := 1.0;
60 l_doc_id NUMBER;
61 l_doc_line_id NUMBER;
62 l_doc_line_loc_id NUMBER;
63
64 -- Bug 8831247
65 x_online_report_id number;
66
67 --16035142 start
68 x_default_method VARCHAR2(30);
69 x_preparer_id NUMBER(12);
70 x_print_flag varchar2(1) := 'N';
71 x_fax_flag varchar2(1) := 'N';
72 x_email_flag varchar2(1) := 'N';
73 x_eMail_address po_vendor_sites_all.email_address%TYPE := null;
74 x_fax_number varchar2(100) := null;
75 x_po_api_return_status varchar2 (3) := null;
76 x_msg_count number := NULL;
77 x_msg_data varchar2(2000):= NULL;
78 x_document_num po_headers.segment1%type := null;
79 x_communication_method_value varchar2(100) := null;
80 --16035142 end
81
82 BEGIN
83 -- Start standard API initialization
84 SAVEPOINT control_document_GRP;
85 IF FND_API.to_boolean(p_init_msg_list) THEN
86 FND_MSG_PUB.initialize;
87 END IF;
88 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
89 l_api_name, g_pkg_name)
90 THEN
91 RAISE FND_API.g_exc_unexpected_error;
92 END IF;
93 x_return_status := FND_API.g_ret_sts_success;
94 -- End standard API initialization
95
96 IF (g_fnd_debug = 'Y') THEN
97 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
98 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
99 '.invoked, Original', 'Source: ' || NVL(p_source,'null') ||
100 ', Action: ' || NVL(p_action,'null') ||
101 ', Type: ' || NVL(p_doc_type,'null') ||
102 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
103 END IF;
104 END IF;
105
106 -- Validate the action parameter
107 IF (p_action NOT IN ('CANCEL')) THEN
108 FND_MESSAGE.set_name('PO','PO_CONTROL_INVALID_ACTION');
109 FND_MESSAGE.set_token('ACTION',p_action);
110 IF (g_fnd_debug = 'Y') THEN
111 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
112 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name ||
113 '.invalid_action', FALSE);
114 END IF;
115 END IF;
116 FND_MSG_PUB.add;
117 RAISE FND_API.g_exc_error;
118 END IF;
119
120 val_doc_params(p_api_version => 1.0,
121 p_init_msg_list => FND_API.G_FALSE,
122 x_return_status => x_return_status,
123 p_doc_type => p_doc_type,
124 p_doc_subtype => p_doc_subtype,
125 p_doc_id => p_doc_id,
126 p_doc_num => p_doc_num,
127 p_doc_line_id => p_doc_line_id,
128 p_doc_line_num => p_doc_line_num,
129 p_release_id => p_release_id,
130 p_release_num => p_release_num,
131 p_doc_line_loc_id => p_doc_line_loc_id,
132 p_doc_shipment_num => p_doc_shipment_num,
133 x_doc_id => l_doc_id,
134 x_doc_line_id => l_doc_line_id,
135 x_doc_line_loc_id => l_doc_line_loc_id);
136 IF (x_return_status = FND_API.g_ret_sts_error) THEN
137 RAISE FND_API.g_exc_error;
138 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
139 RAISE FND_API.g_exc_unexpected_error;
140 END IF;
141
142 --16035142 start
143 /* Get supplier's default transmission settings */
144
145 IF p_launch_approvals_flag = 'Y' THEN
146 PO_VENDOR_SITES_GRP.Get_Transmission_Defaults(
147 p_api_version => 1.0,
148 p_init_msg_list => FND_API.G_FALSE,
149 p_document_id => l_doc_id,
150 p_document_type => p_doc_type,
151 p_document_subtype => p_doc_subtype,
152 p_preparer_id => x_preparer_id,
153 x_default_method => x_default_method,
154 x_email_address => x_email_address,
155 x_fax_number => x_fax_number,
156 x_document_num => x_document_num,
157 x_print_flag => x_print_flag,
158 x_fax_flag => x_fax_flag,
159 x_email_flag => x_email_flag,
160 x_return_status => x_po_api_return_status,
161 x_msg_count => x_msg_count,
162 x_msg_data => x_msg_data);
163
164 if x_email_flag = 'Y' then
165 x_communication_method_value := x_email_address;
166 elsif x_fax_flag = 'Y' then
167 x_communication_method_value := x_fax_number;
168 else
169 x_communication_method_value := null;
170 end if;
171
172 IF (g_fnd_debug = 'Y') THEN
173 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
174 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
175 'PO_VENDOR_SITES_GRP.Get_Transmission_Defaults.invoked, Original', 'x_email_flag: ' || NVL(x_email_flag,'null') ||
176 ', x_fax_flag: ' || NVL(x_fax_flag,'null') ||
177 ', x_communication_method_value: ' || NVL(x_communication_method_value,'null') ||
178 ', x_default_method: ' || NVL(TO_CHAR(x_default_method),'null'));
179 END IF;
180 END IF;
181 END IF;
182
183 --16035142 end
184
185 /* Bug 8831247 , As part of this bug we introduced a new out parameter
186 p_online_report_id, Modifying below call accordingly */
187
188 PO_Document_Control_PVT.control_document
189 (p_api_version => 1.0,
190 p_init_msg_list => FND_API.G_FALSE,
191 p_commit => FND_API.G_FALSE,
192 x_return_status => x_return_status,
193 p_doc_type => p_doc_type,
194 p_doc_subtype => p_doc_subtype,
195 p_doc_id => l_doc_id,
196 p_doc_line_id => l_doc_line_id,
197 p_doc_line_loc_id => l_doc_line_loc_id,
198 p_source => p_source,
199 p_action => p_action,
200 p_action_date => p_action_date,
201 p_cancel_reason => p_cancel_reason,
202 p_cancel_reqs_flag => p_cancel_reqs_flag,
203 p_print_flag => x_print_flag, --16035142
204 p_note_to_vendor => p_note_to_vendor,
205 p_use_gldate => p_use_gldate, -- <ENCUMBRANCE FPJ>
206 p_launch_approvals_flag => p_launch_approvals_flag, --Bug8224603
207 p_communication_method_option => x_default_method, --16035142
208 p_communication_method_value => x_communication_method_value, --16035142
209 p_online_report_id => x_online_report_id,
210 p_caller => p_caller --Bug9321845(6603493)
211 );
212
213 IF (x_return_status = FND_API.g_ret_sts_error) THEN
214 RAISE FND_API.g_exc_error;
215 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
216 RAISE FND_API.g_exc_unexpected_error;
217 END IF;
218
219
220 -- Standard API check of p_commit
221 IF FND_API.to_boolean(p_commit) THEN
222 COMMIT WORK;
223 END IF;
224 EXCEPTION
225 WHEN FND_API.g_exc_error THEN
226 ROLLBACK TO control_document_GRP;
227 x_return_status := FND_API.g_ret_sts_error;
228 WHEN FND_API.g_exc_unexpected_error THEN
229 ROLLBACK TO control_document_GRP;
230 x_return_status := FND_API.g_ret_sts_unexp_error;
231 WHEN OTHERS THEN
232 ROLLBACK TO control_document_GRP;
233 x_return_status := FND_API.g_ret_sts_unexp_error;
234 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
235 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
236 IF (g_fnd_debug = 'Y') THEN
237 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
238 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
239 l_api_name || '.others_exception', 'Exception');
240 END IF;
241 END IF;
242 END IF;
243 END control_document;
244
245 /*Bug9321845(6603493): overloaded procedure control_document*/
246
247 PROCEDURE control_document
248 (p_api_version IN NUMBER,
249 p_init_msg_list IN VARCHAR2,
250 p_commit IN VARCHAR2,
251 x_return_status OUT NOCOPY VARCHAR2,
252 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
253 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
254 p_doc_id IN NUMBER,
255 p_doc_num IN PO_HEADERS.segment1%TYPE,
256 p_release_id IN NUMBER,
257 p_release_num IN NUMBER,
258 p_doc_line_id IN RecTabpo_line_ids,
259 p_doc_line_num IN NUMBER,
260 p_doc_line_loc_id IN NUMBER,
261 p_doc_shipment_num IN NUMBER,
262 p_source IN VARCHAR2,
263 p_action IN VARCHAR2,
264 p_action_date IN DATE,
265 p_cancel_reason IN PO_LINES.cancel_reason%TYPE,
266 p_cancel_reqs_flag IN VARCHAR2,
267 p_print_flag IN VARCHAR2,
268 p_note_to_vendor IN PO_HEADERS.note_to_vendor%TYPE,
269 p_use_gldate IN VARCHAR2,
270 p_launch_approvals_flag IN VARCHAR2,
271 p_caller IN VARCHAR2)
272 -- <ENCUMBRANCE FPJ>)
273 IS
274
275 l_entity_dtl_rec_tbl po_document_action_pvt.entity_dtl_rec_type_tbl;
276 l_online_report_id NUMBER;
277 l_exc_msg VARCHAR2(2000);
278 l_return_code VARCHAR2(25);
279 l_communication_method_option VARCHAR2(30);
280 l_communication_method_value VARCHAR2(30);
281 l_old_auth_status VARCHAR2(30);
282 l_api_name CONSTANT VARCHAR2(30) := 'control_document';
283 l_api_version CONSTANT NUMBER := 1.0;
284 l_doc_id NUMBER;
285 l_doc_line_id NUMBER;
286 l_doc_line_loc_id NUMBER;
287
288 --16035142 start
289 x_default_method VARCHAR2(30);
290 x_preparer_id NUMBER(12);
291 x_print_flag varchar2(1) := 'N';
292 x_fax_flag varchar2(1) := 'N';
293 x_email_flag varchar2(1) := 'N';
294 x_eMail_address po_vendor_sites_all.email_address%TYPE := null;
295 x_fax_number varchar2(100) := null;
296 x_po_api_return_status varchar2 (3) := null;
297 x_msg_count number := NULL;
298 x_msg_data varchar2(2000):= NULL;
299 x_document_num po_headers.segment1%type := null;
300 --16035142 end
301
302
303 BEGIN
304
305 -- Start standard API initialization
306 SAVEPOINT control_document_GRP;
307 IF FND_API.to_boolean(p_init_msg_list) THEN
308 FND_MSG_PUB.initialize;
309 END IF;
310 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
311 l_api_name, g_pkg_name)
312 THEN
313 RAISE FND_API.g_exc_unexpected_error;
314 END IF;
315 x_return_status := FND_API.g_ret_sts_success;
316 -- End standard API initialization
317
318 IF (g_fnd_debug = 'Y') THEN
319 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
320 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
321 '.invoked, Overloaded', 'Source: ' || NVL(p_source,'null') ||
322 ', Action: ' || NVL(p_action,'null') ||
323 ', Type: ' || NVL(p_doc_type,'null') ||
324 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
325 END IF;
326 END IF;
327
328
329
330 -- Validate the action parameter
331 IF (p_action NOT IN ('CANCEL')) THEN
332 FND_MESSAGE.set_name('PO','PO_CONTROL_INVALID_ACTION');
333 FND_MESSAGE.set_token('ACTION',p_action);
334 IF (g_fnd_debug = 'Y') THEN
335 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
336 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name ||
337 '.invalid_action', FALSE);
338 END IF;
339 END IF;
340 FND_MSG_PUB.add;
341 RAISE FND_API.g_exc_error;
342 END IF;
343
344 l_entity_dtl_rec_tbl := po_document_action_pvt.entity_dtl_rec_type_tbl();
345
346 FOR i IN p_doc_line_id.FIRST..p_doc_line_id.LAST LOOP
347
348 -- Validates the input parameters to be valid and match the
349 -- document specified
350 -- Input to the routine can be document id or document numbers
351 -- For the p_doc_id/p_doc_num, it returns l_doc_id i.e. corresponding PO_HEADER_ID
352 -- For the p_release_id/p_release_num, it returns l_doc_id i.e. corresponding PO_RELEASE_ID
353 -- For the p_doc_line_id/p_doc_line_num, it returns l_doc_line_id i.e. corresponding PO_LINE_ID
354 -- For the p_doc_line_loc_id/p_doc_shipment_num, it returns l_doc_line_loc_id i.e. corresponding LINE_LOCATION_ID
355
356 val_doc_params(
357 p_api_version => 1.0,
358 p_init_msg_list => FND_API.G_FALSE,
359 x_return_status => x_return_status,
360 p_doc_type => p_doc_type,
361 p_doc_subtype => p_doc_subtype,
362 p_doc_id => p_doc_id,
363 p_doc_num => p_doc_num,
364 p_doc_line_id => p_doc_line_id(i).po_line_id,
365 p_doc_line_num => p_doc_line_num,
366 p_release_id => p_release_id,
367 p_release_num => p_release_num,
368 p_doc_line_loc_id => p_doc_line_id(i).po_line_location_id,
369 p_doc_shipment_num => p_doc_shipment_num,
370 x_doc_id => l_doc_id,
371 x_doc_line_id => l_doc_line_id,
372 x_doc_line_loc_id => l_doc_line_loc_id);
373
374
375 IF (x_return_status = FND_API.g_ret_sts_error) THEN
376 RAISE FND_API.g_exc_error;
377 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
378 RAISE FND_API.g_exc_unexpected_error;
379 END IF;
380
381 -- Constructing the table of records
382 l_entity_dtl_rec_tbl.extend;
383 l_entity_dtl_rec_tbl(i).doc_id := l_doc_id;
384 l_entity_dtl_rec_tbl(i).document_type := p_doc_type;
385 l_entity_dtl_rec_tbl(i).document_subtype := p_doc_subtype;
386
387 IF l_doc_line_loc_id IS NOT NULL THEN
388 l_entity_dtl_rec_tbl(i).entity_level :=PO_Document_Cancel_PVT.c_entity_level_SHIPMENT;
389 l_entity_dtl_rec_tbl(i).entity_id := l_doc_line_loc_id;
390
391 ELSIF l_doc_line_id IS NOT NULL THEN
392 l_entity_dtl_rec_tbl(i).entity_level :=PO_Document_Cancel_PVT.c_entity_level_LINE;
393 l_entity_dtl_rec_tbl(i).entity_id := l_doc_line_id;
394
395 ELSE
396 l_entity_dtl_rec_tbl(i).entity_level :=PO_Document_Cancel_PVT.c_entity_level_HEADER;
397 l_entity_dtl_rec_tbl(i).entity_id := l_doc_id;
398 END IF;
399
400
401 l_entity_dtl_rec_tbl(i).entity_action_date := p_action_date;
402 l_entity_dtl_rec_tbl(i).process_entity_flag := 'Y';
403 l_entity_dtl_rec_tbl(i).recreate_demand_flag := 'N';
404
405
406 END LOOP;
407
408 BEGIN
409
410 IF (p_doc_type = 'RELEASE') THEN
411
412 SELECT authorization_status
413 INTO l_old_auth_status
414 FROM po_releases_all
415 WHERE po_release_id = l_doc_id;
416
417 ELSE
418
419 SELECT authorization_status
420 INTO l_old_auth_status
421 FROM po_headers_all
422 WHERE po_header_id= l_doc_id;
423
424 END IF;
425 --Bug 8792423 added exception
426 EXCEPTION
427 WHEN NO_DATA_FOUND THEN
428 l_old_auth_status:=null;
429 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
430 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
431 'API control_document exception', 'Authorization Status not found for '||p_doc_type);
432 END IF;
433 END;
434
435 -- Call do_cancel for cancelling all the entities
436
437 PO_DOCUMENT_ACTION_PVT.do_cancel(
438 p_entity_dtl_rec => l_entity_dtl_rec_tbl,
439 p_reason => p_cancel_reason,
440 p_action => PO_DOCUMENT_ACTION_PVT.g_doc_action_CANCEL,
441 p_action_date => p_action_date,
442 p_use_gl_date => p_use_gldate,
443 p_cancel_reqs_flag => p_cancel_reqs_flag,
444 p_note_to_vendor => p_note_to_vendor,
445 p_caller => PO_DOCUMENT_CANCEL_PVT.c_CANCEL_API,
446 x_online_report_id => l_online_report_id,
447 p_commit => p_commit,
448 x_return_status => x_return_status,
449 x_exception_msg => l_exc_msg,
450 x_return_code => l_return_code);
451
452
453 -- If the procedure does not complete successfully raise the
454 -- appropriate exception
455 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
456 RAISE FND_API.g_exc_error;
457 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
458 RAISE FND_API.g_exc_unexpected_error;
459 END IF;
460
461
462 --Add all the messages to the message list
463 IF l_return_code ='F' AND l_online_report_id IS NOT NULL THEN
464 PO_Document_Control_PVT.add_online_report_msgs(
465 p_api_version => 1.0,
466 p_init_msg_list => FND_API.G_FALSE,
467 x_return_status => x_return_status,
468 p_online_report_id => l_online_report_id);
469
470 RAISE FND_API.g_exc_error;
471 END IF;
472
473 IF p_print_flag ='Y' THEN
474 l_communication_method_option := 'PRINT';
475 l_communication_method_value :=NULL;
476 END IF;
477
478 --16035142 start
479 /* Get supplier's default transmission settings */
480 PO_VENDOR_SITES_GRP.Get_Transmission_Defaults(
481 p_api_version => 1.0,
482 p_init_msg_list => FND_API.G_FALSE,
483 p_document_id => l_doc_id,
484 p_document_type => p_doc_type,
485 p_document_subtype => p_doc_subtype,
486 p_preparer_id => x_preparer_id,
487 x_default_method => x_default_method,
488 x_email_address => x_email_address,
489 x_fax_number => x_fax_number,
490 x_document_num => x_document_num,
491 x_print_flag => x_print_flag,
492 x_fax_flag => x_fax_flag,
493 x_email_flag => x_email_flag,
494 x_return_status => x_po_api_return_status,
495 x_msg_count => x_msg_count,
496 x_msg_data => x_msg_data);
497
498 if x_email_flag = 'Y' then
499 l_communication_method_value := x_email_address;
500 elsif x_fax_flag = 'Y' then
501 l_communication_method_value := x_fax_number;
502 else
503 l_communication_method_value := null;
504 end if;
505
506 IF (g_fnd_debug = 'Y') THEN
507 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
508 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
509 'PO_VENDOR_SITES_GRP.Get_Transmission_Defaults.invoked, Original', 'x_email_flag: ' || NVL(x_email_flag,'null') ||
510 ', x_fax_flag: ' || NVL(x_fax_flag,'null') ||
511 ', l_communication_method_value: ' || NVL(l_communication_method_value,'null') ||
512 ', x_default_method: ' || NVL(TO_CHAR(x_default_method),'null'));
513 END IF;
514 END IF;
515
516 --16035142 end
517
518
519 -- Approve the document if p_launch_approvals_flag='Y'
520 IF (p_launch_approvals_flag = 'Y'
521 AND l_old_auth_status ='APPROVED') THEN
522
523 PO_Document_Control_PVT.do_approve_on_cancel(
524 p_doc_type => p_doc_type,
525 p_doc_subtype => p_doc_subtype,
526 p_doc_id => p_doc_id,
527 p_communication_method_option => x_default_method, --16035142
528 p_communication_method_value => l_communication_method_value,
529 p_note_to_vendor => p_note_to_vendor,
530 p_source => p_caller,
531 x_exception_msg => l_exc_msg,
532 x_return_status => x_return_status
533 );
534
535 IF (x_return_status = FND_API.g_ret_sts_error) THEN
536 RAISE FND_API.g_exc_error;
537 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
538 RAISE FND_API.g_exc_unexpected_error;
539 END IF;
540
541 END IF;
542
543 -- If the Cancel action is successful
544 -- Communicate the same to the supplier
545 PO_Document_Control_PVT.doc_communicate_oncancel(
546 p_doc_type => p_doc_type,
547 p_doc_subtype => p_doc_subtype,
548 p_doc_id => p_doc_id,
549 p_communication_method_option => x_default_method, --16035142
550 p_communication_method_value => l_communication_method_value,
551 x_return_status => x_return_status);
552
553 -- If the procedure does not complete successfully raise the
554 -- appropriate exception
555 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
556 RAISE FND_API.g_exc_error;
557 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
558 RAISE FND_API.g_exc_unexpected_error;
559 END IF;
560
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
565 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix , 'There is a problem '||'SQLCODE '|| SQLCODE
566 || 'SQLERRM '||SQLERRM);
567 END IF;
568
569 END control_document;
570
571 /*Bug9321845(6603493) end */
572 /**
573 * Private Procedure: validate_control_action
574 * Requires: API message list has been initialized.
575 * Modifies: API message list.
576 * Effects: Validates that p_action is an allowable control action to be
577 * executed by the caller on the document entity level specified. Derives any
578 * ID if the ID is NULL, but the matching number is passed in. If both ID and
579 * number are passed in, the ID is used. Validates at shipment level if the
580 * final line_loc_id is not NULL. Else, validates at line level if the final
581 * line_id is not NULL. Else, validates at header level if the final doc_id is
582 * not NULL. Control actions supported for p_action are: 'CANCEL'.
583 * Requisitions are currently not supported. Appends to API message list on
584 * error.
585 * Returns:
586 * x_return_status - FND_API.G_RET_STS_SUCCESS if validation succeeds
587 * FND_API.G_RET_STS_ERROR if validation fails
588 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
589 */
590 PROCEDURE validate_control_action
591 (p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
592 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
593 p_doc_id IN NUMBER,
594 p_doc_num IN PO_HEADERS.segment1%TYPE,
595 p_release_id IN NUMBER,
596 p_release_num IN NUMBER,
597 p_doc_line_id IN NUMBER,
598 p_doc_line_num IN NUMBER,
599 p_doc_line_loc_id IN NUMBER,
600 p_doc_shipment_num IN NUMBER,
601 p_action IN VARCHAR2,
602 p_agent_id IN PO_HEADERS.agent_id%TYPE,
603 x_return_status OUT NOCOPY VARCHAR2)
604 IS
605
606 l_api_name CONSTANT VARCHAR2(30) := 'validate_control_action';
607 l_control_level NUMBER;
608 l_doc_id NUMBER;
609 l_doc_line_id NUMBER;
610 l_doc_line_loc_id NUMBER;
611
612 BEGIN
613 x_return_status := FND_API.g_ret_sts_success;
614
615 IF (g_fnd_debug = 'Y') THEN
616 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
617 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
618 '.invoked', 'Action: ' || NVL(p_action,'null') ||
619 ', Type: ' || NVL(p_doc_type,'null') ||
620 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
621 END IF;
622 END IF;
623
624 -- Validate the action parameter
625 IF (p_action NOT IN ('CANCEL')) THEN
626 FND_MESSAGE.set_name('PO','PO_CONTROL_INVALID_ACTION');
627 FND_MESSAGE.set_token('ACTION',p_action);
628 IF (g_fnd_debug = 'Y') THEN
629 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
630 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name ||
631 '.invalid_action', FALSE);
632 END IF;
633 END IF;
634 FND_MSG_PUB.add;
635 RAISE FND_API.g_exc_error;
636 END IF;
637
638 val_doc_params(p_api_version => 1.0,
639 p_init_msg_list => FND_API.G_FALSE,
640 x_return_status => x_return_status,
641 p_doc_type => p_doc_type,
642 p_doc_subtype => p_doc_subtype,
643 p_doc_id => p_doc_id,
644 p_doc_num => p_doc_num,
645 p_release_id => p_release_id,
646 p_release_num => p_release_num,
647 p_doc_line_id => p_doc_line_id,
648 p_doc_line_num => p_doc_line_num,
649 p_doc_line_loc_id => p_doc_line_loc_id,
650 p_doc_shipment_num => p_doc_shipment_num,
651 x_doc_id => l_doc_id,
652 x_doc_line_id => l_doc_line_id,
653 x_doc_line_loc_id => l_doc_line_loc_id);
654 IF (x_return_status = FND_API.g_ret_sts_error) THEN
655 RAISE FND_API.g_exc_error;
656 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
657 RAISE FND_API.g_exc_unexpected_error;
658 END IF;
659
660 -- Call private level validation. If the agent ID is not NULL, then user
661 -- authority and document access levels will be validated as well.
662 PO_Document_Control_PVT.val_control_action
663 (p_api_version => 1.0,
664 p_init_msg_list => FND_API.G_FALSE,
665 x_return_status => x_return_status,
666 p_doc_type => p_doc_type,
667 p_doc_subtype => p_doc_subtype,
668 p_doc_id => l_doc_id,
669 p_doc_line_id => l_doc_line_id,
670 p_doc_line_loc_id => l_doc_line_loc_id,
671 p_action => p_action,
672 p_agent_id => p_agent_id,
673 x_control_level => l_control_level);
674
675 IF (x_return_status = FND_API.g_ret_sts_error) THEN
676 RAISE FND_API.g_exc_error;
677 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
678 RAISE FND_API.g_exc_unexpected_error;
679 END IF;
680
681 EXCEPTION
682 WHEN FND_API.g_exc_error THEN
683 x_return_status := FND_API.g_ret_sts_error;
684 WHEN FND_API.g_exc_unexpected_error THEN
685 x_return_status := FND_API.g_ret_sts_unexp_error;
686 WHEN OTHERS THEN
687 x_return_status := FND_API.g_ret_sts_unexp_error;
688 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
689 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
690 IF (g_fnd_debug = 'Y') THEN
691 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
692 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
693 l_api_name || '.others_exception', 'Exception');
694 END IF;
695 END IF;
696 END IF;
697 END validate_control_action;
698
699
700 /**
701 * Public Procedure: val_control_action
702 * Requires: API message list has been initialized if p_init_msg_list is false.
703 * Modifies: API message list.
704 * Effects: Validates that p_action is an allowable control action to be
705 * executed by the caller on the document entity level specified. Derives any
706 * ID if the ID is NULL, but the matching number is passed in. If both ID and
707 * number are passed in, the ID is used. Validates at shipment level if the
708 * final line_loc_id is not NULL. Else, validates at line level if the final
709 * line_id is not NULL. Else, validates at header level if the final doc_id is
710 * not NULL. Control actions supported for p_action are: 'CANCEL'.
711 * Requisitions are currently not supported. Appends to API message list on
712 * error.
713 * Returns:
714 * x_return_status - FND_API.G_RET_STS_SUCCESS if validation succeeds
715 * FND_API.G_RET_STS_ERROR if validation fails
716 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
717 */
718 PROCEDURE val_control_action
719 (p_api_version IN NUMBER,
720 p_init_msg_list IN VARCHAR2,
721 x_return_status OUT NOCOPY VARCHAR2,
722 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
723 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
724 p_doc_id IN NUMBER,
725 p_doc_num IN PO_HEADERS.segment1%TYPE,
726 p_release_id IN NUMBER,
727 p_release_num IN NUMBER,
728 p_doc_line_id IN NUMBER,
729 p_doc_line_num IN NUMBER,
730 p_doc_line_loc_id IN NUMBER,
731 p_doc_shipment_num IN NUMBER,
732 p_action IN VARCHAR2)
733 IS
734
735 l_api_name CONSTANT VARCHAR2(30) := 'val_control_action';
736 l_api_version CONSTANT NUMBER := 1.0;
737
738 -- Apps context should be initialized, so we can get the employee id directly
739 l_agent_id PO_HEADERS.agent_id%TYPE := FND_GLOBAL.employee_id;
740
741 BEGIN
742 -- Start standard API initialization
743 IF FND_API.to_boolean(p_init_msg_list) THEN
744 FND_MSG_PUB.initialize;
745 END IF;
746 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
747 l_api_name, g_pkg_name)
748 THEN
749 RAISE FND_API.g_exc_unexpected_error;
750 END IF;
751 x_return_status := FND_API.g_ret_sts_success;
752 -- End standard API initialization
753
754 -- Ensure that we are not using a NULL agent ID.
755 IF (l_agent_id IS NULL) THEN
756 l_agent_id := -1;
757 END IF;
758
759 IF (g_fnd_debug = 'Y') THEN
760 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
761 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
762 '.invoked', 'Action: ' || NVL(p_action,'null') ||
763 ', Type: ' || NVL(p_doc_type,'null') ||
764 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null') ||
765 ', agent ID: ' || NVL(TO_CHAR(l_agent_id),'null'));
766 END IF;
767 END IF;
768
769 -- Call private procedure validation with agent ID. This ensures that the
770 -- user's authority and document access level are validated.
771 validate_control_action(p_doc_type => p_doc_type,
772 p_doc_subtype => p_doc_subtype,
773 p_doc_id => p_doc_id,
774 p_doc_num => p_doc_num,
775 p_release_id => p_release_id,
776 p_release_num => p_release_num,
777 p_doc_line_id => p_doc_line_id,
778 p_doc_line_num => p_doc_line_num,
779 p_doc_line_loc_id => p_doc_line_loc_id,
780 p_doc_shipment_num => p_doc_shipment_num,
781 p_action => p_action,
782 p_agent_id => l_agent_id,
783 x_return_status => x_return_status);
784 IF (x_return_status = FND_API.g_ret_sts_error) THEN
785 RAISE FND_API.g_exc_error;
786 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
787 RAISE FND_API.g_exc_unexpected_error;
788 END IF;
789
790 EXCEPTION
791 WHEN FND_API.g_exc_error THEN
792 x_return_status := FND_API.g_ret_sts_error;
793 WHEN FND_API.g_exc_unexpected_error THEN
794 x_return_status := FND_API.g_ret_sts_unexp_error;
795 WHEN OTHERS THEN
796 x_return_status := FND_API.g_ret_sts_unexp_error;
797 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
798 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
799 IF (g_fnd_debug = 'Y') THEN
800 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
801 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
802 l_api_name || '.others_exception', 'Exception');
803 END IF;
804 END IF;
805 END IF;
806 END val_control_action;
807
808
809 /**
810 * Public Procedure: check_control_action
811 * Requires: API message list has been initialized if p_init_msg_list is false.
812 * Modifies: API message list.
813 * Effects: Checks that p_action is an allowable control action to be executed
814 * on the document at the entity level specified, regardless of the API
815 * caller's authority or access level. Derives any ID if the ID is NULL, but
816 * the matching number is passed in. If both ID and number are passed in, the
817 * ID is used. Checks at shipment level if the final line_loc_id is not NULL.
818 * Else, checks at line level if the final line_id is not NULL. Else, checks
819 * at header level if the final doc_id is not NULL. Control actions supported
820 * for p_action are: 'CANCEL'. Requisitions are currently not supported.
821 * Appends to API message list on error.
822 * Returns:
823 * x_return_status - FND_API.G_RET_STS_SUCCESS if check succeeds
824 * FND_API.G_RET_STS_ERROR if check fails
825 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
826 */
827 PROCEDURE check_control_action
828 (p_api_version IN NUMBER,
829 p_init_msg_list IN VARCHAR2,
830 x_return_status OUT NOCOPY VARCHAR2,
831 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
832 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
833 p_doc_id IN NUMBER,
834 p_doc_num IN PO_HEADERS.segment1%TYPE,
835 p_release_id IN NUMBER,
836 p_release_num IN NUMBER,
837 p_doc_line_id IN NUMBER,
838 p_doc_line_num IN NUMBER,
839 p_doc_line_loc_id IN NUMBER,
840 p_doc_shipment_num IN NUMBER,
841 p_action IN VARCHAR2)
842 IS
843
844 l_api_name CONSTANT VARCHAR2(30) := 'check_control_action';
845 l_api_version CONSTANT NUMBER := 1.0;
846
847 BEGIN
848 -- Start standard API initialization
849 IF FND_API.to_boolean(p_init_msg_list) THEN
850 FND_MSG_PUB.initialize;
851 END IF;
852 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
853 l_api_name, g_pkg_name)
854 THEN
855 RAISE FND_API.g_exc_unexpected_error;
856 END IF;
857 x_return_status := FND_API.g_ret_sts_success;
858 -- End standard API initialization
859
860 IF (g_fnd_debug = 'Y') THEN
861 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
862 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
863 '.invoked', 'Action: ' || NVL(p_action,'null') ||
864 ', Type: ' || NVL(p_doc_type,'null') ||
865 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
866 END IF;
867 END IF;
868
869 -- Call private procedure validation with a NULL agent ID. This will
870 -- ensure that no user authority or document access levels are checked.
871 validate_control_action(p_doc_type => p_doc_type,
872 p_doc_subtype => p_doc_subtype,
873 p_doc_id => p_doc_id,
874 p_doc_num => p_doc_num,
875 p_release_id => p_release_id,
876 p_release_num => p_release_num,
877 p_doc_line_id => p_doc_line_id,
878 p_doc_line_num => p_doc_line_num,
879 p_doc_line_loc_id => p_doc_line_loc_id,
880 p_doc_shipment_num => p_doc_shipment_num,
881 p_action => p_action,
882 p_agent_id => NULL,
883 x_return_status => x_return_status);
884 IF (x_return_status = FND_API.g_ret_sts_error) THEN
885 RAISE FND_API.g_exc_error;
886 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
887 RAISE FND_API.g_exc_unexpected_error;
888 END IF;
889
890 EXCEPTION
891 WHEN FND_API.g_exc_error THEN
892 x_return_status := FND_API.g_ret_sts_error;
893 WHEN FND_API.g_exc_unexpected_error THEN
894 x_return_status := FND_API.g_ret_sts_unexp_error;
895 WHEN OTHERS THEN
896 x_return_status := FND_API.g_ret_sts_unexp_error;
897 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
898 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
899 IF (g_fnd_debug = 'Y') THEN
900 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
901 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
902 l_api_name || '.others_exception', 'Exception');
903 END IF;
904 END IF;
905 END IF;
906 END check_control_action;
907
908
909 /**
910 * Private Procedure: val_po_params
911 * Requires: API message list has been initialized. Document is PO or PA.
912 * Modifies: API message list
913 * Effects: Derives any ID if the ID is NULL and the matching number is passed
914 * in. If both the ID and number are passed in, the ID is used for validation.
915 * The final IDs must match the document specified. Appends to API message
916 * list on error.
917 * Returns:
918 * x_return_status - FND_API.G_RET_STS_SUCCESS if derived all IDs correctly
919 * FND_API.G_RET_STS_ERROR if error occurred
920 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
921 */
922 PROCEDURE val_po_params
923 (p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
924 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
925 p_doc_id IN NUMBER,
926 p_doc_num IN PO_HEADERS.segment1%TYPE,
927 p_doc_line_id IN NUMBER,
928 p_doc_line_num IN NUMBER,
929 p_doc_line_loc_id IN NUMBER,
930 p_doc_shipment_num IN NUMBER,
931 x_doc_id OUT NOCOPY NUMBER,
932 x_doc_line_id OUT NOCOPY NUMBER,
933 x_doc_line_loc_id OUT NOCOPY NUMBER,
934 x_return_status OUT NOCOPY VARCHAR2)
935 IS
936
937 l_api_name CONSTANT VARCHAR2(30) := 'val_po_params';
938 l_exists VARCHAR2(10);
939 l_control_level NUMBER;
940
941 BEGIN
942 x_return_status := FND_API.g_ret_sts_success;
943
944 IF (g_fnd_debug = 'Y') THEN
945 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
946 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
947 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
948 ', Subtype: ' || NVL(p_doc_subtype,'null') ||
949 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null') ||
950 ', Num: ' || NVL(p_doc_num,'null'));
951 END IF;
952 END IF;
953
954 -- Must have a document ID or document num
955 IF (p_doc_id IS NULL) AND (p_doc_num IS NULL) THEN
956 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
957 IF (g_fnd_debug = 'Y') THEN
958 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
959 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name
960 || '.invalid_doc_ids', FALSE);
961 END IF;
962 END IF;
963 FND_MSG_PUB.add;
964 RAISE FND_API.g_exc_error;
965 END IF; --<if p_doc_id ...>
966
967 -- initialize outputs
968 x_doc_id := p_doc_id;
969 x_doc_line_id := p_doc_line_id;
970 x_doc_line_loc_id := p_doc_line_loc_id;
971
972 -- Find level of control action
973 IF (p_doc_line_loc_id IS NOT NULL) OR (p_doc_shipment_num IS NOT NULL)
974 THEN
975 l_control_level := PO_Document_Control_PVT.g_shipment_level;
976 ELSIF (p_doc_line_id IS NOT NULL) OR (p_doc_line_num IS NOT NULL) THEN
977 l_control_level := PO_Document_Control_PVT.g_line_level;
978 ELSE
979 l_control_level := PO_Document_Control_PVT.g_header_level;
980 END IF; --<if p_doc_line_loc_id ...>
981
982 -- Derive header
983 IF (p_doc_id IS NULL) THEN
984 SELECT poh.po_header_id
985 INTO x_doc_id
986 FROM po_headers poh
987 WHERE poh.segment1 = p_doc_num AND
988 poh.type_lookup_code = p_doc_subtype;
989 ELSE
990 SELECT poh.po_header_id
991 INTO x_doc_id
992 FROM po_headers poh
993 WHERE poh.po_header_id = p_doc_id;
994 END IF;
995
996 IF (g_fnd_debug = 'Y') THEN
997 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
998 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_module_prefix || l_api_name ||
999 '.validated_header', 'ID: ' || NVL(TO_CHAR(x_doc_id),'null'));
1000 END IF;
1001 END IF;
1002
1003 IF (l_control_level <> PO_Document_Control_PVT.g_header_level) THEN
1004
1005 IF (p_doc_line_id IS NULL) THEN
1006 SELECT pol.po_line_id
1007 INTO x_doc_line_id
1008 FROM po_lines pol
1009 WHERE pol.po_header_id = x_doc_id AND
1010 pol.line_num = p_doc_line_num;
1011 ELSE
1012 SELECT 'Exists'
1013 INTO l_exists
1014 FROM po_lines pol
1015 WHERE pol.po_line_id = x_doc_line_id AND
1016 pol.po_header_id = x_doc_id;
1017 END IF; --<if p_doc_line_id ...>
1018
1019 IF (g_fnd_debug = 'Y') THEN
1020 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1021 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_module_prefix ||
1022 l_api_name || '.validated_line', 'ID: ' ||
1023 NVL(TO_CHAR(x_doc_line_id),'null'));
1024 END IF;
1025 END IF;
1026
1027 -- Derive shipment if at shipment level and doc is PO
1028 IF (l_control_level = PO_Document_Control_PVT.g_shipment_level) THEN
1029
1030 IF (p_doc_type = 'PO') THEN
1031
1032 IF (p_doc_line_loc_id IS NULL) THEN
1033 SELECT poll.line_location_id
1034 INTO x_doc_line_loc_id
1035 FROM po_line_locations poll
1036 WHERE poll.shipment_num = p_doc_shipment_num AND
1037 poll.po_line_id = x_doc_line_id AND
1038 poll.po_header_id = x_doc_id;
1039 ELSE
1040 SELECT 'Exists'
1041 INTO l_exists
1042 FROM po_line_locations poll
1043 WHERE poll.line_location_id = x_doc_line_loc_id AND
1044 poll.po_line_id = x_doc_line_id AND
1045 poll.po_header_id = x_doc_id;
1046 END IF; --<if p_doc_line_loc_id ...>
1047
1048 IF (g_fnd_debug = 'Y') THEN
1049 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1050 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_module_prefix ||
1051 l_api_name || '.validated_shipment', 'ID: ' ||
1052 NVL(TO_CHAR(x_doc_line_loc_id),'null'));
1053 END IF;
1054 END IF;
1055
1056 ELSE
1057 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
1058 IF (g_fnd_debug = 'Y') THEN
1059 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1060 FND_LOG.message(FND_LOG.level_error, g_module_prefix ||
1061 l_api_name || '.pa_has_ship_ids', FALSE);
1062 END IF;
1063 END IF;
1064 FND_MSG_PUB.add;
1065 RAISE FND_API.g_exc_error;
1066 END IF; --<if p_doc_type PO>
1067
1068 END IF; --<if l_control_level = g_shipment_level>
1069
1070 END IF; --<if l_control_level <> g_header_level>
1071
1072 EXCEPTION
1073 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
1074 x_return_status := FND_API.g_ret_sts_error;
1075 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
1076 IF (g_fnd_debug = 'Y') THEN
1077 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1078 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name ||
1079 '.invalid_doc_ids', FALSE);
1080 END IF;
1081 END IF;
1082 FND_MSG_PUB.add;
1083 WHEN FND_API.g_exc_error THEN
1084 x_return_status := FND_API.g_ret_sts_error;
1085 WHEN OTHERS THEN
1086 x_return_status := FND_API.g_ret_sts_unexp_error;
1087 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1088 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1089 IF (g_fnd_debug = 'Y') THEN
1090 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1091 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1092 l_api_name || '.others_exception', 'Exception');
1093 END IF;
1094 END IF;
1095 END IF;
1096 END val_po_params;
1097
1098
1099 /**
1100 * Private Procedure: val_rel_params
1101 * Requires: API message list has been initialized. Document is a Release.
1102 * Modifies: API message list
1103 * Effects: Derives any ID if the ID is NULL and the matching number is passed
1104 * in. If both the ID and number are passed in, the ID is used for validation.
1105 * The final IDs must match the document specified. Appends to API message
1106 * list on error.
1107 * Returns:
1108 * x_return_status - FND_API.G_RET_STS_SUCCESS if derived all IDs correctly
1109 * FND_API.G_RET_STS_ERROR if error occurred
1110 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1111 */
1112 PROCEDURE val_rel_params
1113 (p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
1114 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
1115 p_doc_id IN NUMBER,
1116 p_doc_num IN PO_HEADERS.segment1%TYPE,
1117 p_release_id IN NUMBER,
1118 p_release_num IN NUMBER,
1119 p_doc_line_loc_id IN NUMBER,
1120 p_doc_shipment_num IN NUMBER,
1121 x_doc_id OUT NOCOPY NUMBER,
1122 x_doc_line_loc_id OUT NOCOPY NUMBER,
1123 x_return_status OUT NOCOPY VARCHAR2)
1124 IS
1125
1126 l_api_name CONSTANT VARCHAR2(30) := 'val_rel_params';
1127 l_exists VARCHAR2(10);
1128 l_control_level NUMBER;
1129 l_release_po_header_id NUMBER;
1130 l_release_po_subtype PO_DOCUMENT_TYPES.document_subtype%TYPE;
1131
1132 BEGIN
1133 x_return_status := FND_API.g_ret_sts_success;
1134
1135 IF (g_fnd_debug = 'Y') THEN
1136 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1137 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1138 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
1139 ', Subtype: ' || NVL(p_doc_subtype,'null') ||
1140 ', ID: ' || NVL(TO_CHAR(p_release_id),'null') ||
1141 ', Num: ' || NVL(TO_CHAR(p_release_num),'null'));
1142 END IF;
1143 END IF;
1144
1145 -- Must have a document ID or document num if release_id is null
1146 IF (p_release_id IS NULL) AND (p_doc_id IS NULL) AND (p_doc_num IS NULL)
1147 THEN
1148 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
1149 IF (g_fnd_debug = 'Y') THEN
1150 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1151 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name
1152 || '.invalid_doc_ids', FALSE);
1153 END IF;
1154 END IF;
1155 FND_MSG_PUB.add;
1156 RAISE FND_API.g_exc_error;
1157 END IF; --<if p_doc_id ...>
1158
1159 -- initialize output
1160 x_doc_id := p_release_id;
1161 x_doc_line_loc_id := p_doc_line_loc_id;
1162
1163 -- Find level of control action
1164 IF (p_doc_line_loc_id IS NOT NULL) OR (p_doc_shipment_num IS NOT NULL)
1165 THEN
1166 l_control_level := PO_Document_Control_PVT.g_rel_shipment_level;
1167 ELSE
1168 l_control_level := PO_Document_Control_PVT.g_rel_header_level;
1169 END IF; --<if p_doc_line_loc_id ...>
1170
1171 -- Derive release header
1172 IF (p_release_id IS NULL) THEN
1173
1174 IF (p_doc_id IS NULL) THEN
1175 IF (p_doc_subtype = 'BLANKET') THEN
1176 l_release_po_subtype := 'BLANKET';
1177 ELSE
1178 l_release_po_subtype := 'PLANNED';
1179 END IF;
1180
1181 -- SQL What: Query to find po_header_id and po_release_id
1182 -- SQL Why: Need to derive the missing unique po_release_id
1183 -- SQL Join: po_header_id
1184 SELECT poh.po_header_id, por.po_release_id
1185 INTO l_release_po_header_id, x_doc_id
1186 FROM po_headers poh,
1187 po_releases por
1188 WHERE poh.segment1 = p_doc_num AND
1189 poh.type_lookup_code = l_release_po_subtype AND
1190 por.po_header_id = poh.po_header_id AND
1191 por.release_num = p_release_num;
1192 ELSE
1193 -- SQL What: Query to find po_header_id and po_release_id
1194 -- SQL Why: Need to derive the missing unique po_release_id
1195 -- SQL Join: po_header_id
1196 SELECT poh.po_header_id, por.po_release_id
1197 INTO l_release_po_header_id, x_doc_id
1198 FROM po_headers poh,
1199 po_releases por
1200 WHERE poh.po_header_id = p_doc_id AND
1201 por.po_header_id = poh.po_header_id AND
1202 por.release_num = p_release_num;
1203 END IF; --<if p_doc_id is null>
1204
1205 ELSE
1206 SELECT por.po_header_id
1207 INTO l_release_po_header_id
1208 FROM po_releases por
1209 WHERE por.po_release_id = x_doc_id;
1210 END IF; --<if p_release_id is null>
1211
1212 IF (g_fnd_debug = 'Y') THEN
1213 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1214 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_module_prefix || l_api_name ||
1215 '.validated_rel_header', 'ID: ' ||
1216 NVL(TO_CHAR(x_doc_id),'null'));
1217 END IF;
1218 END IF;
1219
1220 -- Derive release shipment if at shipment level
1221 IF (l_control_level = PO_Document_Control_PVT.g_rel_shipment_level) THEN
1222
1223 IF (p_doc_line_loc_id IS NULL) THEN
1224 -- SQL What: Query to find the line_location_id
1225 -- SQL Why: Need to derive the missing unique line_location_id
1226 -- SQL Join: po_line_id
1227 SELECT poll.line_location_id
1228 INTO x_doc_line_loc_id
1229 FROM po_line_locations poll,
1230 po_lines pol
1231 WHERE poll.po_release_id = x_doc_id AND
1232 poll.po_header_id = l_release_po_header_id AND
1233 poll.po_line_id = pol.po_line_id AND
1234 poll.shipment_num = p_doc_shipment_num AND
1235 pol.po_header_id = l_release_po_header_id;
1236 ELSE
1237 SELECT 'Exists'
1238 INTO l_exists
1239 FROM po_line_locations poll
1240 WHERE poll.line_location_id = x_doc_line_loc_id AND
1241 poll.po_release_id = x_doc_id;
1242 END IF; --<if p_doc_line_loc_id ...>
1243
1244 IF (g_fnd_debug = 'Y') THEN
1245 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1246 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_module_prefix ||
1247 l_api_name || '.validated_rel_shipment', 'ID: ' ||
1248 NVL(TO_CHAR(x_doc_line_loc_id),'null'));
1249 END IF;
1250 END IF;
1251
1252 END IF; --<if l_control_level = g_rel_shipment_level>
1253
1254 EXCEPTION
1255 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
1256 x_return_status := FND_API.g_ret_sts_error;
1257 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
1258 IF (g_fnd_debug = 'Y') THEN
1259 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1260 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name ||
1261 '.invalid_doc_ids', FALSE);
1262 END IF;
1263 END IF;
1264 FND_MSG_PUB.add;
1265 WHEN FND_API.g_exc_error THEN
1266 x_return_status := FND_API.g_ret_sts_error;
1267 WHEN OTHERS THEN
1268 x_return_status := FND_API.g_ret_sts_unexp_error;
1269 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1270 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1271 IF (g_fnd_debug = 'Y') THEN
1272 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1273 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1274 l_api_name || '.others_exception', 'Exception');
1275 END IF;
1276 END IF;
1277 END IF;
1278 END val_rel_params;
1279
1280
1281 /**
1282 * Public Procedure: val_doc_params
1283 * Requires: API message list has been initialized if p_init_msg_list is false.
1284 * Modifies: API message list
1285 * Effects: Validates that the input parameters are all valid and match the
1286 * document specified. Derives any ID if the ID is NULL, but the matching
1287 * number is passed in. If both the ID and number are passed in, the ID is
1288 * used. Appends to API message list on error.
1289 * Returns:
1290 * x_return_status - FND_API.G_RET_STS_SUCCESS if validation succeeds
1291 * FND_API.G_RET_STS_ERROR if validation fails
1292 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1293 * x_doc_id - The valid document ID
1294 * x_doc_line_id - The valid line ID, if at line level
1295 * x_doc_line_loc_id - The valid line location ID, if at shipment level
1296 */
1297 PROCEDURE val_doc_params
1298 (p_api_version IN NUMBER,
1299 p_init_msg_list IN VARCHAR2,
1300 x_return_status OUT NOCOPY VARCHAR2,
1301 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
1302 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
1303 p_doc_id IN NUMBER,
1304 p_doc_num IN PO_HEADERS.segment1%TYPE,
1305 p_release_id IN NUMBER,
1306 p_release_num IN NUMBER,
1307 p_doc_line_id IN NUMBER,
1308 p_doc_line_num IN NUMBER,
1309 p_doc_line_loc_id IN NUMBER,
1310 p_doc_shipment_num IN NUMBER,
1311 x_doc_id OUT NOCOPY NUMBER,
1312 x_doc_line_id OUT NOCOPY NUMBER,
1313 x_doc_line_loc_id OUT NOCOPY NUMBER)
1314 IS
1315
1316 l_api_name CONSTANT VARCHAR2(30) := 'val_doc_params';
1317 l_api_version CONSTANT NUMBER := 1.0;
1318 l_exists VARCHAR2(10);
1319 l_control_level NUMBER;
1320 l_release_po_header_id NUMBER;
1321 l_release_po_subtype PO_DOCUMENT_TYPES.document_subtype%TYPE;
1322
1323 BEGIN
1324 -- Start standard API initialization
1325 IF FND_API.to_boolean(p_init_msg_list) THEN
1326 FND_MSG_PUB.initialize;
1327 END IF;
1328 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
1329 l_api_name, g_pkg_name)
1330 THEN
1331 RAISE FND_API.g_exc_unexpected_error;
1332 END IF;
1333 x_return_status := FND_API.g_ret_sts_success;
1334 -- End standard API initialization
1335
1336 IF (g_fnd_debug = 'Y') THEN
1337 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1338 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1339 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
1340 ', Subtype: ' || NVL(p_doc_subtype,'null') ||
1341 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
1342 END IF;
1343 END IF;
1344
1345 -- Must have correct, matching doc types and subtypes
1346 IF ((p_doc_type IS NULL) OR (p_doc_subtype IS NULL)) OR
1347 (p_doc_type NOT IN ('PO','PA','RELEASE')) OR
1348 ((p_doc_type = 'PO') AND (p_doc_subtype NOT IN('STANDARD','PLANNED'))) OR
1349 ((p_doc_type = 'PA') AND (p_doc_subtype NOT IN('BLANKET','CONTRACT'))) OR
1350 ((p_doc_type = 'RELEASE') AND
1351 (p_doc_subtype NOT IN ('BLANKET','SCHEDULED')))
1352 THEN
1353 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_TYPE_SUBTYPE');
1354 FND_MESSAGE.set_token('TYPE',p_doc_type);
1355 FND_MESSAGE.set_token('SUBTYPE',p_doc_subtype);
1356 IF (g_fnd_debug = 'Y') THEN
1357 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1358 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name ||
1359 '.invalid_doc_type', FALSE);
1360 END IF;
1361 END IF;
1362 FND_MSG_PUB.add;
1363 RAISE FND_API.g_exc_error;
1364 END IF;
1365
1366 IF (p_doc_type IN ('PO','PA')) THEN
1367
1368 IF (p_release_id IS NOT NULL) OR (p_release_num IS NOT NULL) THEN
1369 -- Should not pass in release info for POs and PAs
1370 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
1371 IF (g_fnd_debug = 'Y') THEN
1372 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1373 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name ||
1374 '.po_pa_has_release_info', FALSE);
1375 END IF;
1376 END IF;
1377 FND_MSG_PUB.add;
1378 RAISE FND_API.g_exc_error;
1379 END IF; --<if p_doc_line_id ...>
1380
1381 val_po_params(p_doc_type => p_doc_type,
1382 p_doc_subtype => p_doc_subtype,
1383 p_doc_id => p_doc_id,
1384 p_doc_num => p_doc_num,
1385 p_doc_line_id => p_doc_line_id,
1386 p_doc_line_num => p_doc_line_num,
1387 p_doc_line_loc_id => p_doc_line_loc_id,
1388 p_doc_shipment_num => p_doc_shipment_num,
1389 x_doc_id => x_doc_id,
1390 x_doc_line_id => x_doc_line_id,
1391 x_doc_line_loc_id => x_doc_line_loc_id,
1392 x_return_status => x_return_status);
1393
1394 ELSE -- else is a release
1395
1396 IF (p_doc_line_id IS NOT NULL) OR (p_doc_line_num IS NOT NULL) THEN
1397 -- Releases don't have lines
1398 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
1399 IF (g_fnd_debug = 'Y') THEN
1400 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1401 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name ||
1402 '.release_has_line_ids', FALSE);
1403 END IF;
1404 END IF;
1405 FND_MSG_PUB.add;
1406 RAISE FND_API.g_exc_error;
1407 END IF; --<if p_doc_line_id ...>
1408
1409 val_rel_params(p_doc_type => p_doc_type,
1410 p_doc_subtype => p_doc_subtype,
1411 p_doc_id => p_doc_id,
1412 p_doc_num => p_doc_num,
1413 p_release_id => p_release_id,
1414 p_release_num => p_release_num,
1415 p_doc_line_loc_id => p_doc_line_loc_id,
1416 p_doc_shipment_num => p_doc_shipment_num,
1417 x_doc_id => x_doc_id,
1418 x_doc_line_loc_id => x_doc_line_loc_id,
1419 x_return_status => x_return_status);
1420
1421 x_doc_line_id := NULL;
1422
1423 END IF; --<if p_doc_type PO or PA>
1424
1425 EXCEPTION
1426 WHEN FND_API.g_exc_error THEN
1427 x_return_status := FND_API.g_ret_sts_error;
1428 WHEN FND_API.g_exc_unexpected_error THEN
1429 x_return_status := FND_API.g_ret_sts_unexp_error;
1430 WHEN OTHERS THEN
1431 x_return_status := FND_API.g_ret_sts_unexp_error;
1432 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1433 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1434 IF (g_fnd_debug = 'Y') THEN
1435 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1436 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1437 l_api_name || '.others_exception', 'Exception');
1438 END IF;
1439 END IF;
1440 END IF;
1441 END val_doc_params;
1442
1443
1444 END PO_Document_Control_GRP;
[Home] [Help]