1 PACKAGE BODY PO_DOCUMENT_CHECKS_GRP AS
2 /* $Header: POXGDCKB.pls 120.10.12010000.2 2008/08/02 10:35:20 rramasam ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_DOCUMENT_CHECKS_GRP';
5
6 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| g_pkg_name || '.';
7
8 -- Read the profile option that enables/disables the debug log
9 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
10
11 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
12 g_debug_unexp BOOLEAN := PO_DEBUG.is_debug_unexp_on;
13
14 /**
15 * Public Procedure: po_submission_check
16 * Requires:
17 * IN PARAMETERS:
18 * p_api_version: Version number of API that caller expects. It
19 * should match the l_api_version defined in the
20 * procedure
21 * p_action_requested: In FPJ, the Action requested should be in
22 * g_action_(DOC_SUBMISSION_CHECK, UNRESERVE)
23 * p_document_type: The type of the document to perform
24 * the submission check on.
25 * p_document_subtype: The subtype of the document.
26 * Valid Document types and Document subtypes are
27 * Document Type Document Subtype
28 * REQUISITION --->
29 * RELEASE ---> SCHEDULED/BLANKET
30 * PO ---> PLANNED/STANDARD
31 * PA ---> CONTRACT/BLANKET
32 --
33 -- <FPJ ENCUMBRANCE>
34 -- The following 2 parameters replace the p_document_id parameter.
35 --p_document_level
36 -- The type of id that is being passed. Use g_doc_level_<>
37 -- HEADER
38 -- The following are only supported for the UNRESERVE action:
39 -- LINE
40 -- SHIPMENT
41 -- DISTRIBUTION
42 --p_document_level_id
43 -- Id of the doc level type on which to perform the check.
44 --
45 --
46 --p_org_id
47 -- If not NULL, this org context will be set.
48 --
49 --
50 * p_requested_changes: This object contains all the requested changes to
51 * the document. It contains 5 objects. These objects
52 * are: 1.Header_Changes 2.Release_Changes 3.Line_
53 * Changes 4.Shipment_Changes 5.Distribution_Changes.
54 * In FPI, following change requests are allowed:
55 * 1. HEADER_CHANGES: None
56 * 2. RELEASE_CHANGES: None
57 * 3. LINE_CHANGES: unit_price, vendor_product_num
58 * 4. SHIPMENT_CHANGES: quantity, promised_date,
59 * price_override
60 * 5. DISTRIBUTION_CHANGES: quantity_ordered
61 * p_check_asl: Determines whether or not to perform the checks:
62 * PO_SUB_ITEM_NOT_APPROVED / PO_SUB_ITEM_ASL_DEBARRED
63 * (a) TRUE : Perform check
64 * (b) FALSE : Do not perform check
65 *
66 * Modifies: Inserts error msgs in online_report_text table, uses global_temp
67 * tables for processing
68 * Effects: This procedure runs the document submission checks on passed in
69 * document.
70 * Returns:
71 * x_return_status: FND_API.G_RET_STS_SUCCESS if API succeeds
72 * FND_API.G_RET_STS_ERROR if API fails
73 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
74 * x_sub_check_status: FND_API.G_RET_STS_SUCCESS if document passes all
75 * submission checks, even if warnings are found
76 * FND_API.G_RET_STS_ERROR if document fails atleast one
77 * submission check (returns at least one error)
78 * x_has_warnings: FND_API.G_TRUE if submission check returns warnings
79 * FND_API.G_FALSE if no warnings are found
80 * x_msg_data: Contains error msg in case x_return_status returned
81 * FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_UNEXP_ERROR
82 * x_online_report_id: This id can be used to get all submission check errors
83 * for given document from online_report_text table
84 * x_doc_check_error_record: If x_sub_check_status returned G_RET_STS_ERROR
85 * then this object of tables will contain information about
86 * all submission check errors for given document including
87 * message_name and text_line.
88 */
89 -- SUB_CHK_1
90 PROCEDURE po_submission_check(
91 p_api_version IN NUMBER
92 , p_action_requested IN VARCHAR2
93 , p_document_type IN VARCHAR2
94 , p_document_subtype IN VARCHAR2
95 , p_document_level IN VARCHAR2
96 , p_document_level_id IN NUMBER
97 , p_org_id IN NUMBER
98 , p_requested_changes IN PO_CHANGES_REC_TYPE
99 , p_check_asl IN BOOLEAN
100 , p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
101 , p_origin_doc_id IN NUMBER := NULL -- Bug#5462677
102 , x_return_status OUT NOCOPY VARCHAR2
103 , x_sub_check_status OUT NOCOPY VARCHAR2
104 , x_has_warnings OUT NOCOPY VARCHAR2 -- bug3574165
105 , x_msg_data OUT NOCOPY VARCHAR2
106 , x_online_report_id OUT NOCOPY NUMBER
107 , x_doc_check_error_record OUT NOCOPY doc_check_Return_Type
108 )
109 IS
110
111 l_api_name CONSTANT varchar2(30) := 'PO_SUBMISSION_CHECK';
112 l_api_version CONSTANT NUMBER := 1.1;
113 l_progress VARCHAR2(3);
114
115 l_document_id NUMBER;
116 l_document_id_tbl po_tbl_number;
117
118 l_doc_id NUMBER;
119
120 l_consigned_flag PO_HEADERS_ALL.CONSIGNED_CONSUMPTION_FLAG%TYPE;
121
122 BEGIN
123
124 -- Initialize variables
125 l_consigned_flag := 'N';
126
127 --Do validations on passed in parameters
128
129 l_progress := '000';
130
131 IF g_fnd_debug = 'Y' THEN
132 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
133 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
134 || l_progress,'Doing Validation on passed in data');
135 END IF;
136 END IF;
137
138 -- Standard call to check for call compatibility
139 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
140 THEN
141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
142 END IF;
143
144 PO_CORE_S.get_document_ids(
145 p_doc_type => p_document_type
146 , p_doc_level => p_document_level
147 , p_doc_level_id_tbl => po_tbl_number( p_document_level_id )
148 , x_doc_id_tbl => l_document_id_tbl
149 );
150
151 l_document_id := l_document_id_tbl(1);
152
153 l_progress := '001';
154 --Check p_action_requested
155 -- <Doc Manager Rewrite 11.5.11>: Support FINAL_CLOSE submission checks
156 IF p_action_requested NOT IN
157 (g_action_DOC_SUBMISSION_CHECK, g_action_UNRESERVE, g_action_FINAL_CLOSE_CHECK)
158 THEN
159 FND_MESSAGE.set_name('PO', 'PO_SUB_GENERAL_ERROR');
160 FND_MESSAGE.set_token('ERROR_TEXT','Invalid Action Requested');
161 FND_MSG_PUB.Add;
162 RAISE FND_API.G_EXC_ERROR;
163 END IF;
164
165 l_progress := '002';
166 --check p_document_type
167 IF p_document_type NOT IN ('REQUISITION', 'RELEASE', 'PO', 'PA') THEN
168 FND_MESSAGE.set_name('PO', 'PO_SUB_GENERAL_ERROR');
169 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid Document TYpe');
170 FND_MSG_PUB.Add;
171 RAISE FND_API.G_EXC_ERROR;
172 END IF;
173
174 l_progress := '003';
175
176 --check that action requested matches
177 IF p_action_requested = g_action_UNRESERVE THEN
178 IF p_document_type NOT IN ('PO', 'RELEASE') THEN
179 FND_MESSAGE.set_name('PO', 'PO_SUB_GENERAL_ERROR');
180 FND_MESSAGE.set_token('ERROR_TEXT', 'UNRESERVE Action Requested for invalid document type');
181 FND_MSG_PUB.Add;
182 RAISE FND_API.G_EXC_ERROR;
183 END IF;
184 -- <Doc Manager Rewrite 11.5.11 Start>
185 ELSIF (p_action_requested = g_action_FINAL_CLOSE_CHECK) THEN
186 IF p_document_type NOT IN ('PO', 'PA', 'RELEASE') THEN
187 FND_MESSAGE.set_name('PO', 'PO_SUB_GENERAL_ERROR');
188 FND_MESSAGE.set_token('ERROR_TEXT', 'FINAL_CLOSE Action Requested for invalid document type');
189 FND_MSG_PUB.Add;
190 RAISE FND_API.G_EXC_ERROR;
191 END IF;
192 -- <Doc Manager Rewrite 11.5.11 End>
193 ELSE
194 IF p_document_level <> g_document_level_HEADER THEN
195 FND_MESSAGE.set_name('PO', 'PO_SUB_GENERAL_ERROR');
196 FND_MESSAGE.set_token('ERROR_TEXT', 'Non-Header level only supported for UNRESERVE action');
197 FND_MSG_PUB.Add;
198 RAISE FND_API.G_EXC_ERROR;
199 END IF;
200 END IF;
201
202 l_progress := '004';
203 --check that document_subtype matches
204 IF p_document_type = 'PO' THEN
205 IF p_document_subtype NOT IN ('STANDARD', 'PLANNED') THEN
206 FND_MESSAGE.set_name('PO', 'PO_SUB_GENERAL_ERROR');
207 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid document type for document type PO');
208 FND_MSG_PUB.Add;
209 RAISE FND_API.G_EXC_ERROR;
210 END IF;
211 ELSIF p_document_type = 'PA' THEN
212 IF p_document_subtype NOT IN ('BLANKET', 'CONTRACT') THEN
213 FND_MESSAGE.set_name('PO', 'PO_SUB_GENERAL_ERROR');
214 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid document type for document type PA');
215 FND_MSG_PUB.Add;
216 RAISE FND_API.G_EXC_ERROR;
217 END IF;
218 END IF;
219
220 l_progress := '005';
221 --Set the org context
222 IF p_org_id IS NOT NULL THEN
223 PO_MOAC_UTILS_PVT.Set_Org_Context(p_org_id); -- <R12 MOAC>
224 END IF;
225
226 l_progress := '006';
227 --check that document_id passed exists and that org context is set.
228 IF p_document_type IN ('PO', 'PA') THEN
229 BEGIN
230 SELECT po_header_id,
231 NVL(consigned_consumption_flag, 'N')
232 INTO l_doc_id,
233 l_consigned_flag
234 FROM PO_HEADERS
235 WHERE po_header_id= l_document_id;
236 EXCEPTION
237 WHEN NO_DATA_FOUND THEN
238 FND_MESSAGE.set_name('PO', 'PO_SUB_GENERAL_ERROR');
239 FND_MESSAGE.set_token('ERROR_TEXT', 'Either Org Context is not set OR Doc_id does not exist');
240 FND_MSG_PUB.Add;
241 WHEN OTHERS THEN
242 RAISE;
243 END;
244 ELSIF p_document_type = 'REQUISITION' THEN
245 BEGIN
246 SELECT requisition_header_id
247 INTO l_doc_id
248 FROM PO_REQUISITION_HEADERS
249 WHERE requisition_header_id= l_document_id;
250 EXCEPTION
251 WHEN NO_DATA_FOUND THEN
252 FND_MESSAGE.set_name('PO', 'PO_SUB_GENERAL_ERROR');
253 FND_MESSAGE.set_token('ERROR_TEXT', 'Either Org Context is not set OR Doc_id does not exist');
254 FND_MSG_PUB.Add;
255 WHEN OTHERS THEN
256 RAISE;
257 END;
258 ELSE --Its a release
259 BEGIN
260 SELECT po_release_id,
261 NVL(consigned_consumption_flag, 'N')
262 INTO l_doc_id,
263 l_consigned_flag
264 FROM PO_RELEASES
265 WHERE po_release_id= l_document_id;
266 EXCEPTION
267 WHEN NO_DATA_FOUND THEN
268 FND_MESSAGE.set_name('PO', 'PO_SUB_GENERAL_ERROR');
269 FND_MESSAGE.set_token('ERROR_TEXT', 'Either Org Context is not set OR Doc_id does not exist');
270 FND_MSG_PUB.Add;
271 WHEN OTHERS THEN
272 RAISE;
273 END;
274 END IF;
275
276 l_progress := '007';
277
278 IF g_fnd_debug = 'Y' THEN
279 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
280 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
281 || l_progress,'Calling PVT package');
282 END IF;
283 END IF;
284
285 -- Bug 3318625
286 -- skip the submission checks for a consumption advice as they are not
287 -- done when creating a consumption advice.This may chnage if we decide
288 -- to add them when creating
289 -- <Doc Manager Rewrite 11.5.11>: Do check even if consigned for final close.
290 IF (l_consigned_flag = 'Y') AND (p_action_requested <> g_action_FINAL_CLOSE_CHECK)
291 THEN
292
293 x_sub_check_status := FND_API.G_RET_STS_SUCCESS;
294 x_return_status := FND_API.G_RET_STS_SUCCESS;
295
296 Return;
297 END IF;
298
299 --Call the private po_submission_check
300
301 PO_DOCUMENT_CHECKS_PVT.po_submission_check(
302 p_api_version => 2.0
303 , p_action_requested => p_action_requested
304 , p_document_type => p_document_type
305 , p_document_subtype => p_document_subtype
306 , p_document_level => p_document_level
307 , p_document_level_id => p_document_level_id
308 , p_requested_changes => p_requested_changes
309 , p_check_asl => p_check_asl
310 , p_req_chg_initiator => p_req_chg_initiator -- bug4957243
311 , p_origin_doc_id => p_origin_doc_id -- Bug#5462677
312 , x_return_status => x_return_status
313 , x_sub_check_status => x_sub_check_status
314 , x_has_warnings => x_has_warnings -- bug3574165
315 , x_msg_data => x_msg_data
316 , x_online_report_id => x_online_report_id
317 , x_doc_check_error_record => x_doc_check_error_record
318 );
319
320 l_progress := '008';
321 IF g_fnd_debug = 'Y' THEN
322 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
323 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
324 || l_progress,'Back in GRP package. Returning to the caller');
325 END IF;
326 END IF;
327
328 EXCEPTION
329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
330 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
331 p_encoded => 'F');
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
333 x_sub_check_status := FND_API.G_RET_STS_ERROR;
334 WHEN FND_API.G_EXC_ERROR THEN
335 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
336 p_encoded => 'F');
337 x_return_status := FND_API.G_RET_STS_ERROR;
338 x_sub_check_status := FND_API.G_RET_STS_ERROR;
339 WHEN OTHERS THEN
340 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
341 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
342 END IF;
343
344 IF (g_fnd_debug = 'Y') THEN
345 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
346 FND_LOG.string(FND_LOG.level_unexpected, g_log_head ||
347 l_api_name || '.others_exception', 'EXCEPTION: Location is '
348 || l_progress || ' SQL CODE is '||sqlcode);
349 END IF;
350 END IF;
351
352 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
353 p_encoded => 'F');
354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
355 x_sub_check_status := FND_API.G_RET_STS_ERROR;
356 END po_submission_check;
357
358 -- bug3574165 START
359 -- Overloaded procedure. This procedure does not take l_has_warnings
360 -- as parameter.
361
362 -- SUB_CHK_2
363 PROCEDURE po_submission_check(
364 p_api_version IN NUMBER
365 , p_action_requested IN VARCHAR2
366 , p_document_type IN VARCHAR2
367 , p_document_subtype IN VARCHAR2
368 , p_document_level IN VARCHAR2
369 , p_document_level_id IN NUMBER
370 , p_org_id IN NUMBER
371 , p_requested_changes IN PO_CHANGES_REC_TYPE
372 , p_check_asl IN BOOLEAN
373 , p_origin_doc_id IN NUMBER := NULL -- Bug#5462677
374 , p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
375 , x_return_status OUT NOCOPY VARCHAR2
376 , x_sub_check_status OUT NOCOPY VARCHAR2
377 , x_msg_data OUT NOCOPY VARCHAR2
378 , x_online_report_id OUT NOCOPY NUMBER
379 , x_doc_check_error_record OUT NOCOPY doc_check_Return_Type
380 )
381 IS
382
383 l_has_warnings VARCHAR2(1);
384
385 BEGIN
386
387 -- Call SUB_CHK_1
388 po_submission_check(
389 p_api_version => p_api_version
390 , p_action_requested => p_action_requested
391 , p_document_type => p_document_type
392 , p_document_subtype => p_document_subtype
393 , p_document_level => p_document_level -- Bug 3601682
394 , p_document_level_id => p_document_level_id
395 , p_org_id => p_org_id
396 , p_requested_changes => p_requested_changes
397 , p_check_asl => p_check_asl
398 , p_origin_doc_id => p_origin_doc_id -- Bug#5462677
399 , p_req_chg_initiator => p_req_chg_initiator -- bug4957243
400 , x_return_status => x_return_status
401 , x_sub_check_status => x_sub_check_status
402 , x_has_warnings => l_has_warnings -- bug3574165
403 , x_msg_data => x_msg_data
404 , x_online_report_id => x_online_report_id
405 , x_doc_check_error_record => x_doc_check_error_record
406 );
407
408 END po_submission_check;
409
410
411
412 ------------------------------------------------------------------------------
413 --<FPJ ENCUMBRANCE>
414 -- The UNRESERVE action may be taken at any document level.
415 -- This required the replacement of p_document_id with
416 -- p_document_level, p_document_level_id.
417 -- All users of the previous signature were HEADER-level calls.
418 ------------------------------------------------------------------------------
419
420 -- SUB_CHK_3
421 PROCEDURE po_submission_check(
422 p_api_version IN NUMBER
423 , p_action_requested IN VARCHAR2
424 , p_document_type IN VARCHAR2
425 , p_document_subtype IN VARCHAR2
426 , p_document_id IN NUMBER
427 , p_org_id IN NUMBER
428 , p_requested_changes IN PO_CHANGES_REC_TYPE
429 , p_check_asl IN BOOLEAN
430 , p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
431 , x_return_status OUT NOCOPY VARCHAR2
432 , x_sub_check_status OUT NOCOPY VARCHAR2
433 , x_msg_data OUT NOCOPY VARCHAR2
434 , x_online_report_id OUT NOCOPY NUMBER
435 , x_doc_check_error_record OUT NOCOPY doc_check_Return_Type
436 )
437 IS
438
439 BEGIN
440
441 -- Call SUB_CHK_2
442 po_submission_check(
443 p_api_version => p_api_version
444 , p_action_requested => p_action_requested
445 , p_document_type => p_document_type
446 , p_document_subtype => p_document_subtype
447 , p_document_level => g_document_level_HEADER
448 , p_document_level_id => p_document_id
449 , p_org_id => p_org_id
450 , p_requested_changes => p_requested_changes
451 , p_check_asl => p_check_asl
452 , p_req_chg_initiator => p_req_chg_initiator -- bug4957243
453 , x_return_status => x_return_status
454 , x_sub_check_status => x_sub_check_status
455 , x_msg_data => x_msg_data
456 , x_online_report_id => x_online_report_id
457 , x_doc_check_error_record => x_doc_check_error_record
458 );
459
460 END po_submission_check;
461
462
463
464
465 /**
466 * This is overloaded procedure without follwoing parameters
467 * IN: p_requested_changes,
468 * p_org_id
469 * OUT : x_doc_check_error_record
470 * This procedure is called from .pld and .lpc in PO Source code
471 * as these calls do not need above IN OUT parameters
472 */
473
474 -- SUB_CHK_4
475 PROCEDURE po_submission_check(p_api_version IN NUMBER,
476 p_action_requested IN VARCHAR2,
477 p_document_type IN VARCHAR2,
478 p_document_subtype IN VARCHAR2,
479 p_document_id IN NUMBER,
480 x_return_status OUT NOCOPY VARCHAR2,
481 x_sub_check_status OUT NOCOPY VARCHAR2,
482 x_msg_data OUT NOCOPY VARCHAR2,
483 x_online_report_id OUT NOCOPY NUMBER) IS
484
485 -- <PO_CHANGE_API FPJ> Renamed the type to PO_CHANGES_REC_TYPE:
486 l_requested_changes PO_CHANGES_REC_TYPE := NULL;
487 l_doc_check_error_record doc_check_Return_Type := NULL;
488 l_org_id NUMBER :=NULL;
489
490 l_api_name CONSTANT varchar2(30) := 'PO_SUBMISSION_CHECK';
491 l_progress VARCHAR2(3);
492
493 BEGIN
494
495 l_progress := '000';
496 IF g_fnd_debug = 'Y' THEN
497 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
498 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
499 || l_progress,'Called OVERLOADED procedure. Filled missing parameters');
500 END IF;
501 END IF;
502
503 -- Call SUB_CHK_2
504 PO_DOCUMENT_CHECKS_GRP.po_submission_check(p_api_version => p_api_version,
505 p_action_requested => p_action_requested,
506 p_document_type => p_document_type,
507 p_document_subtype => p_document_subtype,
508 p_document_level => g_document_level_HEADER,
509 p_document_level_id => p_document_id,
510 p_org_id => l_org_id,
511 p_requested_changes => l_requested_changes,
512 p_check_asl => TRUE, -- <2757450>
513 x_return_status => x_return_status,
514 x_sub_check_status => x_sub_check_status,
515 x_msg_data => x_msg_data,
516 x_online_report_id => x_online_report_id,
517 x_doc_check_error_record => l_doc_check_error_record);
518
519 l_progress := '001';
520 EXCEPTION
521 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
522 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
523 p_encoded => 'F');
524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
525 x_sub_check_status := FND_API.G_RET_STS_ERROR;
526 WHEN FND_API.G_EXC_ERROR THEN
527 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
528 p_encoded => 'F');
529 x_return_status := FND_API.G_RET_STS_ERROR;
530 x_sub_check_status := FND_API.G_RET_STS_ERROR;
531 WHEN OTHERS THEN
532 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
533 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
534 END IF;
535
536 IF (g_fnd_debug = 'Y') THEN
537 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
538 FND_LOG.string(FND_LOG.level_unexpected, g_log_head ||
539 l_api_name || '.others_exception', 'EXCEPTION: Location is '
540 || l_progress || ' SQL CODE is '||sqlcode);
541 END IF;
542 END IF;
543
544 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
545 p_encoded => 'F');
546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
547 x_sub_check_status := FND_API.G_RET_STS_ERROR;
548 END po_submission_check;
549
550
551 -- bug3574165 START
552 -- Overloaded procedure to include x_has_warnings parameter
553 -- This parameter is used to indicate whether there are warnings coming
554 -- out from po submission check
555
556 -- SUB_CHK_5
557 PROCEDURE po_submission_check
558 (
559 p_api_version IN NUMBER,
560 p_action_requested IN VARCHAR2,
561 p_document_type IN VARCHAR2,
562 p_document_subtype IN VARCHAR2,
563 p_document_id IN NUMBER,
564 p_check_asl IN BOOLEAN,
565 x_return_status OUT NOCOPY VARCHAR2,
566 x_sub_check_status OUT NOCOPY VARCHAR2,
567 x_has_warnings OUT NOCOPY VARCHAR2,
568 x_msg_data OUT NOCOPY VARCHAR2,
569 x_online_report_id OUT NOCOPY NUMBER
570 )
571 IS
572 -- <PO_CHANGE_API FPJ> Renamed the type to PO_CHANGES_REC_TYPE:
573 l_requested_changes PO_CHANGES_REC_TYPE := NULL;
574 l_doc_check_error_record doc_check_Return_Type := NULL;
575 l_org_id NUMBER := NULL;
576
577 l_api_name CONSTANT varchar2(30) := 'PO_SUBMISSION_CHECK';
578 l_progress VARCHAR2(3);
579
580 BEGIN
581
582 l_progress := '000';
583
584 IF ( g_fnd_debug = 'Y' )
585 THEN
586 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
587 FND_LOG.string( FND_LOG.LEVEL_STATEMENT,
588 g_log_head || '.'||l_api_name||'.'|| l_progress,
589 'Called OVERLOADED procedure. Filled missing parameters');
590 END IF;
591 END IF;
592
593 -- Call SUB_CHK_2
594 PO_DOCUMENT_CHECKS_GRP.po_submission_check
595 (
596 p_api_version => p_api_version,
597 p_action_requested => p_action_requested,
598 p_document_type => p_document_type,
599 p_document_subtype => p_document_subtype,
600 p_document_level => g_document_level_HEADER,
601 p_document_level_id => p_document_id,
602 p_org_id => l_org_id,
603 p_requested_changes => l_requested_changes,
604 p_check_asl => p_check_asl,
605 x_return_status => x_return_status,
606 x_sub_check_status => x_sub_check_status,
607 x_has_warnings => x_has_warnings,
608 x_msg_data => x_msg_data,
609 x_online_report_id => x_online_report_id,
610 x_doc_check_error_record => l_doc_check_error_record
611 );
612
613 l_progress := '001';
614
615 EXCEPTION
616 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
617 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
618 p_encoded => 'F');
619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620 x_sub_check_status := FND_API.G_RET_STS_ERROR;
621
622 WHEN FND_API.G_EXC_ERROR THEN
623 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
624 p_encoded => 'F');
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 x_sub_check_status := FND_API.G_RET_STS_ERROR;
627
628 WHEN OTHERS THEN
629 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
630 THEN
631 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
632 END IF;
633
634 IF ( g_fnd_debug = 'Y' )
635 THEN
636 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
637 FND_LOG.string(FND_LOG.level_unexpected,
638 g_log_head || l_api_name || '.others_exception',
639 'EXCEPTION: Location is '|| l_progress || ' SQL CODE is '|| sqlcode);
640 END IF;
641 END IF;
642
643 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
644 p_encoded => 'F');
645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
646 x_sub_check_status := FND_API.G_RET_STS_ERROR;
647
648 END po_submission_check;
649 -- bug3574165 END
650
651
652 -- <2757450 START>: Overloaded procedure to include 'p_check_asl' parameter.
653 -- This parameter is used to indicate whether or not to perform the
654 -- PO_SUB_ITEM_NOT_APPROVED and PO_SUB_ITEM_ASL_DEBARRED checks.
655 --
656
657 -- SUB_CHK_6
658 PROCEDURE po_submission_check
659 (
660 p_api_version IN NUMBER,
661 p_action_requested IN VARCHAR2,
662 p_document_type IN VARCHAR2,
663 p_document_subtype IN VARCHAR2,
664 p_document_id IN NUMBER,
665 p_check_asl IN BOOLEAN,
666 x_return_status OUT NOCOPY VARCHAR2,
667 x_sub_check_status OUT NOCOPY VARCHAR2,
668 x_msg_data OUT NOCOPY VARCHAR2,
669 x_online_report_id OUT NOCOPY NUMBER
670 )
671 IS
672
673 -- bug3574165
674 -- Removed all other parameters and added l_has_warnings, which serves
675 -- as a dummy variable in this procedure
676 l_has_warnings VARCHAR2(1);
677
678 BEGIN
679
680 -- bug3574165
681 -- We are calling another po_submission_check that has x_has_warnings
682 -- as OUT parameter. Setting OUT parameters will be handled within that
683 -- procedure
684
685 -- Call SUB_CHK_5
686 PO_DOCUMENT_CHECKS_GRP.po_submission_check
687 (
688 p_api_version => p_api_version,
689 p_action_requested => p_action_requested,
690 p_document_type => p_document_type,
691 p_document_subtype => p_document_subtype,
692 p_document_id => p_document_id,
693 p_check_asl => p_check_asl,
694 x_return_status => x_return_status,
695 x_sub_check_status => x_sub_check_status,
696 x_has_warnings => l_has_warnings,
697 x_msg_data => x_msg_data,
698 x_online_report_id => x_online_report_id
699 );
700
701 END po_submission_check;
702
703 --
704 -- <2757450 END>
705
706 -- <FPJ Refactor Security API START>
707
708 /**
709 * Public Procedure: PO_Security_Check
710 * Requires:
711 * IN PARAMETERS:
712 * p_api_version: Version number of API that caller expects. It
713 * should match the l_api_version defined in the
714 * procedure
715 * p_query_table: Table you want to check
716 * p_owner_id_column: Owner id column of the table
717 * p_employee_id: User id to access the document
718 * p_minimum_access_level: Minimum access level to the document
719 * p_document_type: The type of the document to perform
720 * the security check on.
721 * OR
722 * PO_PA -- check both PO and PA (bug 5054685)
723 * p_document_subtype: The subtype of the document.
724 * Valid Document types and Document subtypes
725 * Document Type Document Subtype
726 * RFQ ---> STANDARD
727 * QUOTATION ---> STANDARD
728 * REQUISITION ---> PURCHASE/INTERNAL
729 * RELEASE ---> SCHEDULED/BLANKET
730 * PO ---> PLANNED/STANDARD
731 * PA ---> CONTRACT/BLANKET
732 * PO_PA ---> "ALL" (PLANNED/STANDARD/CONTRACT/BLANKET) (bug 5054685)
733 * p_type_clause: The document type clause to be used in
734 * constructing where clause
735 *
736 * Modifies: None
737 * Effects: This procedure builds dynamic WHERE clause fragments based on
738 * document security parameters.
739 * Returns:
740 * x_return_status: FND_API.G_RET_STS_SUCCESS if API succeeds
741 * FND_API.G_RET_STS_ERROR if API fails
742 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
743 * x_msg_data: Contains error msg in case x_return_status returned
744 * FND_API.G_RET_STS_ERROR or
745 * FND_API.G_RET_STS_UNEXP_ERROR
746 * x_where_clause: The constructed where clause
747 */
748 PROCEDURE PO_SECURITY_CHECK (p_api_version IN NUMBER,
749 p_query_table IN VARCHAR2,
750 p_owner_id_column IN VARCHAR2,
751 p_employee_id IN VARCHAR2,
752 p_org_id IN NUMBER,
753 p_minimum_access_level IN VARCHAR2,
754 p_document_type IN VARCHAR2,
755 p_document_subtype IN VARCHAR2,
756 p_type_clause IN VARCHAR2,
757 x_return_status OUT NOCOPY VARCHAR2,
758 x_msg_data OUT NOCOPY VARCHAR2,
759 x_where_clause OUT NOCOPY VARCHAR2)
760 IS
761
762 l_api_name CONSTANT varchar2(30) := 'PO_SECURITY_CHECK';
763 l_api_version CONSTANT NUMBER := 1.0;
764 l_progress VARCHAR2(3);
765
766 BEGIN
767
768 l_progress := '000';
769 -- Standard call to check for call compatibility
770 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
771 THEN
772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
773 END IF;
774
775 l_progress := '010';
776
777 IF g_fnd_debug = 'Y' THEN
778 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
779 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
780 || l_progress,'Doing Validation on passed in data');
781 END IF;
782 END IF;
783
784 -- Check the required fields
785 IF ((p_query_table is NULL) OR (p_owner_id_column is NULL) OR
786 (p_employee_id IS NULL) OR
787 (p_minimum_access_level IS NULL) OR (p_document_type is NULL) OR
788 (p_document_subtype is NULL) OR (p_type_clause is NULL)) THEN
789 FND_MESSAGE.set_name('PO', 'PO_SEC_GENERAL_ERROR');
790 FND_MESSAGE.set_token('ERROR_TEXT', 'Mandatory parameters are NULL');
791 FND_MSG_PUB.Add;
792 RAISE FND_API.G_EXC_ERROR;
793 END IF; /*(p_query_table is NULL) OR (p_owner_id_column is NULL)*/
794
795 l_progress := '020';
796 --Check p_minimum_access_level
797 IF p_minimum_access_level NOT IN ('VIEW_ONLY', 'MODIFY', 'FULL') THEN
798 FND_MESSAGE.set_name('PO', 'PO_SEC_GENERAL_ERROR');
799 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid Minimum Access Level');
800 FND_MSG_PUB.Add;
801 RAISE FND_API.G_EXC_ERROR;
802 END IF; /*p_minimum_access_level NOT IN ('VIEW_ONLY', 'MODIFY', 'ALL')*/
803
804 l_progress := '030';
805 --check p_document_type
806 -- Bug 5054685: Added 'PO_PA' as additional check
807 IF p_document_type NOT IN ('RFQ', 'QUOTATION', 'REQUISITION',
808 'RELEASE', 'PO', 'PA', 'PO_PA') THEN
809 FND_MESSAGE.set_name('PO', 'PO_SEC_GENERAL_ERROR');
810 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid Document Type');
811 FND_MSG_PUB.Add;
812 RAISE FND_API.G_EXC_ERROR;
813 END IF; /*p_document_type NOT IN ('RFQ', 'QUOTATION', 'REQUISITION',*/
814
815 l_progress := '040';
816 --check that document_subtype matches
817 IF p_document_type = 'REQUISITION' THEN
818 IF p_document_subtype NOT IN ('PURCHASE', 'INTERNAL') THEN
819 FND_MESSAGE.set_name('PO', 'PO_SEC_GENERAL_ERROR');
820 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid Document Subtype');
821 FND_MSG_PUB.Add;
822 RAISE FND_API.G_EXC_ERROR;
823 END IF;
824 ELSIF p_document_type = 'RELEASE' THEN
825 IF p_document_subtype NOT IN ('SCHEDULED', 'BLANKET') THEN
826 FND_MESSAGE.set_name('PO', 'PO_SEC_GENERAL_ERROR');
827 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid Document Subtype');
828 FND_MSG_PUB.Add;
829 RAISE FND_API.G_EXC_ERROR;
830 END IF;
831 ELSIF p_document_type = 'PO' THEN
832 IF p_document_subtype NOT IN ('STANDARD', 'PLANNED') THEN
833 FND_MESSAGE.set_name('PO', 'PO_SEC_GENERAL_ERROR');
834 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid Document Subtype');
835 FND_MSG_PUB.Add;
836 RAISE FND_API.G_EXC_ERROR;
837 END IF;
838 ELSIF p_document_type = 'PA' THEN
839 IF p_document_subtype NOT IN ('BLANKET', 'CONTRACT') THEN
840 FND_MESSAGE.set_name('PO', 'PO_SEC_GENERAL_ERROR');
841 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid Document Subtype');
842 FND_MSG_PUB.Add;
843 RAISE FND_API.G_EXC_ERROR;
844 END IF;
845 -- Bug 5054685 Start: Performance issue. Collapsed multiple security
846 -- related where-clauses into one.
847 ELSIF p_document_type = 'PO_PA' THEN
848 IF p_document_subtype NOT IN ('ALL') THEN
849 FND_MESSAGE.set_name('PO', 'PO_SEC_GENERAL_ERROR');
850 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid Document Subtype');
851 FND_MSG_PUB.Add;
852 RAISE FND_API.G_EXC_ERROR;
853 END IF;
854 -- Bug 5054685 End
855 END IF; /*p_document_type = 'REQUISITION'*/
856
857 l_progress := '050';
858 --Set the org context
859 IF p_org_id IS NOT NULL THEN
860 PO_MOAC_UTILS_PVT.Set_Org_Context(p_org_id); -- <R12 MOAC>
861 END IF; /* p_org_id IS NOT NULL*/
862
863 l_progress := '060';
864 --Call the private PO_Security_Check
865 PO_DOCUMENT_CHECKS_PVT.PO_SECURITY_CHECK(
866 p_api_version => p_api_version,
867 p_query_table => p_query_table,
868 p_owner_id_column => p_owner_id_column,
869 p_employee_id => p_employee_id,
870 p_minimum_access_level => p_minimum_access_level,
871 p_document_type => p_document_type,
872 p_document_subtype => p_document_subtype,
873 p_type_clause => p_type_clause,
874 x_return_status => x_return_status,
875 x_msg_data => x_msg_data,
876 x_where_clause => x_where_clause);
877
878 l_progress := '100';
879 EXCEPTION
880 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
881 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
882 p_encoded => 'F');
883 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
884 x_where_clause := NULL;
885 WHEN FND_API.G_EXC_ERROR THEN
886 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
887 p_encoded => 'F');
888 x_return_status := FND_API.G_RET_STS_ERROR;
889 x_where_clause := NULL;
890 WHEN OTHERS THEN
891 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
892 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name);
893 END IF;
894
895 IF (g_fnd_debug = 'Y') THEN
896 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
897 FND_LOG.string(FND_LOG.level_unexpected, g_log_head ||
898 l_api_name || '.others_exception', 'EXCEPTION: Location is '
899 || l_progress || ' SQL CODE is '||sqlcode);
900 END IF;
901 END IF;
902
903 x_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
904 p_encoded => 'F');
905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
906 x_where_clause := NULL;
907 END PO_SECURITY_CHECK;
908
909 -- <FPJ Refactor Security API END>
910
911 -- The following new procedures for status check added in DropShip FPJ project
912
913 -------------------------------------------------------------------------------
914 --Start of Comments
915 --Name: validate_status_check_inputs
916 --Pre-reqs:
917 -- None.
918 --Modifies:
919 -- None.
920 --Locks:
921 -- None.
922 --Function:
923 -- The following Validations done, called by po_status_check Group procedure.
924 -- 1. All the ID input tables p_header_id,p_release_id etc. should be of same size.
925 -- 2. Each entity specifies a PO Header or Release through one of the following.
926 -- 2a:Header Id is not null
927 -- 2b:Release Id is not null
928 -- 2c:Document Number and Document Sub Type are not null
929 -- 2d:Vendor Order Num is not null
930 -- Note that the Line/Shipment are optional but a Header/Release is required.
931 --Notes:
932 -- Detailed comments maintained in PVT Package Body PO_DOCUMENT_CHECKS_PVT.po_status_check
933 --End of Comments
934 -------------------------------------------------------------------------------
935 PROCEDURE validate_status_check_inputs (
936 p_api_version IN NUMBER,
937 p_header_id IN OUT NOCOPY PO_TBL_NUMBER,
938 p_release_id IN OUT NOCOPY PO_TBL_NUMBER,
939 p_document_type IN OUT NOCOPY PO_TBL_VARCHAR30,
940 p_document_subtype IN OUT NOCOPY PO_TBL_VARCHAR30,
941 p_document_num IN OUT NOCOPY PO_TBL_VARCHAR30,
942 p_vendor_order_num IN OUT NOCOPY PO_TBL_VARCHAR30,
943 p_line_id IN OUT NOCOPY PO_TBL_NUMBER,
944 p_line_location_id IN OUT NOCOPY PO_TBL_NUMBER,
945 p_distribution_id IN OUT NOCOPY PO_TBL_NUMBER,
946 p_mode IN VARCHAR2,
947 p_lock_flag IN VARCHAR2 := 'N',
948 x_po_status_rec OUT NOCOPY PO_STATUS_REC_TYPE,
949 x_return_status OUT NOCOPY VARCHAR2
950 ) IS
951
952 l_api_name CONSTANT VARCHAR(30) := 'VALIDATE_STATUS_CHECK_INPUTS';
953 l_progress VARCHAR2(3) := '000';
954 l_count NUMBER;
955 l_dummy_table_number po_tbl_number := po_tbl_number();
956 l_dummy_table_varchar30 po_tbl_varchar30 := po_tbl_varchar30();
957
958 BEGIN
959
960 --Initialize l_count to length of first non-null Required Input Table
961 l_progress := '005';
962 IF p_header_id IS NOT NULL THEN
963 l_count := p_header_id.COUNT;
964 ELSIF p_release_id IS NOT NULL THEN
965 l_count := p_release_id.COUNT;
966 ELSIF p_document_num IS NOT NULL THEN
967 l_count := p_document_num.COUNT;
968 ELSIF p_document_subtype IS NOT NULL THEN
969 l_count := p_document_subtype.COUNT;
970 ELSIF p_vendor_order_num IS NOT NULL THEN
971 l_count := p_vendor_order_num.COUNT;
972 ELSE -- The required input table ID parameters are all null !
973 FND_MESSAGE.set_name('PO', 'PO_STATCHK_ERR_NULL_INPARAM');
974 FND_MSG_PUB.Add;
975 RAISE FND_API.G_EXC_ERROR;
976 END IF;
977
978 --Initialize any null Tables to a dummy table of null values with length of p_header_id.COUNT
979 l_progress := '007';
980 l_dummy_table_number.extend(l_count);
981 l_dummy_table_varchar30.extend(l_count);
982 IF p_header_id IS NULL THEN
983 p_header_id := l_dummy_table_number;
984 END IF;
985 IF p_release_id IS NULL THEN
986 p_release_id := l_dummy_table_number;
987 END IF;
988 IF p_document_type IS NULL THEN
989 p_document_type := l_dummy_table_varchar30;
990 END IF;
991 IF p_document_subtype IS NULL THEN
992 p_document_subtype := l_dummy_table_varchar30;
993 END IF;
994 IF p_document_num IS NULL THEN
995 p_document_num := l_dummy_table_varchar30;
996 END IF;
997 IF p_vendor_order_num IS NULL THEN
998 p_vendor_order_num := l_dummy_table_varchar30;
999 END IF;
1000 IF p_line_id IS NULL THEN
1001 p_line_id := l_dummy_table_number;
1002 END IF;
1003 IF p_line_location_id IS NULL THEN
1004 p_line_location_id := l_dummy_table_number;
1005 END IF;
1006 IF p_distribution_id IS NULL THEN
1007 p_distribution_id := l_dummy_table_number;
1008 END IF;
1009
1010 --Validate that Input ID Tables are all of the same size
1011 l_progress := '010';
1012 IF l_count <> p_release_id.count
1013 OR l_count <> p_document_type.count
1014 OR l_count <> p_document_subtype.count
1015 OR l_count <> p_document_num.count
1016 OR l_count <> p_vendor_order_num.count
1017 OR l_count <> p_line_id.count
1018 OR l_count <> p_line_location_id.count
1019 OR l_count <> p_distribution_id.count THEN
1020
1021 FND_MESSAGE.set_name('PO', 'PO_STATCHK_GENERAL_ERROR');
1022 FND_MESSAGE.set_token('ERROR_TEXT', 'The input table ID parameters are not of same size !');
1023 FND_MSG_PUB.Add;
1024 RAISE FND_API.G_EXC_ERROR;
1025 END IF;
1026
1027 --Validate that a Header or Release is specified through any of the possible combinations
1028 -- Line/Shipment/Distribution are optional, but Header/Release should be specified.
1029 l_progress := '020';
1030 FOR i IN 1..l_count LOOP
1031
1032 --For each index, Input IDs should refer to a valid entity
1033 --bug 4931241 - p_vendor_order_num and p_document_num/p_document_subtype don't
1034 --uniquely identify a document. Hence we have to rely on p_header_id or p_release_id
1035 IF (p_header_id(i) IS NULL) AND (p_release_id(i) IS NULL) THEN
1036 -- Means that no Header/Release is specified
1037 FND_MESSAGE.set_name('PO', 'PO_STATCHK_GENERAL_ERROR');
1038 FND_MESSAGE.set_token('ERROR_TEXT', 'There is no Header/Release specified at index ' || i);
1039 FND_MSG_PUB.Add;
1040 RAISE FND_API.G_EXC_ERROR;
1041 END IF;
1042
1043 END LOOP;
1044
1045 l_progress := '030';
1046
1047 x_return_status := FND_API.G_RET_STS_SUCCESS;
1048
1049 EXCEPTION
1050 WHEN FND_API.G_EXC_ERROR THEN
1051 x_return_status := FND_API.G_RET_STS_ERROR;
1052 WHEN OTHERS THEN
1053 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1054 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
1055
1056 END validate_status_check_inputs;
1057
1058 -------------------------------------------------------------------------------
1059 --Start of Comments
1060 --Name: po_status_check
1061 --Pre-reqs:
1062 -- None.
1063 --Modifies:
1064 -- None.
1065 --Locks:
1066 -- None.
1067 --Function:
1068 -- Group procedure to find the status of a Purchase Order or a Release
1069 -- This validates inputs and calls the private procedure po_status_check
1070 --Notes:
1071 -- For details on validations, refer to Group Procedure validate_status_check_inputs
1072 -- Detailed comments maintained in PVT Package Body PO_DOCUMENT_CHECKS_PVT.po_status_check
1073 --End of Comments
1074 -------------------------------------------------------------------------------
1075 PROCEDURE po_status_check (
1076 p_api_version IN NUMBER,
1077 p_header_id IN PO_TBL_NUMBER,
1078 p_release_id IN PO_TBL_NUMBER,
1079 p_document_type IN PO_TBL_VARCHAR30,
1080 p_document_subtype IN PO_TBL_VARCHAR30,
1081 p_document_num IN PO_TBL_VARCHAR30,
1082 p_vendor_order_num IN PO_TBL_VARCHAR30,
1083 p_line_id IN PO_TBL_NUMBER,
1084 p_line_location_id IN PO_TBL_NUMBER,
1085 p_distribution_id IN PO_TBL_NUMBER,
1086 p_mode IN VARCHAR2,
1087 p_lock_flag IN VARCHAR2 := 'N',
1088 p_calling_module IN VARCHAR2 := NULL, -- PDOI Rewrite R12
1089 p_role IN VARCHAR2 := NULL, -- PDOI Rewrite R12
1090 p_skip_cat_upload_chk IN VARCHAR2 := NULL, -- PDOI Rewrite R12
1091 x_po_status_rec OUT NOCOPY PO_STATUS_REC_TYPE,
1092 x_return_status OUT NOCOPY VARCHAR2
1093 ) IS
1094
1095 l_header_id PO_TBL_NUMBER := p_header_id;
1096 l_release_id PO_TBL_NUMBER := p_release_id;
1097 l_document_type PO_TBL_VARCHAR30 := p_document_type;
1098 l_document_subtype PO_TBL_VARCHAR30 := p_document_subtype;
1099 l_document_num PO_TBL_VARCHAR30 := p_document_num;
1100 l_vendor_order_num PO_TBL_VARCHAR30 := p_vendor_order_num;
1101 l_line_id PO_TBL_NUMBER := p_line_id;
1102 l_line_location_id PO_TBL_NUMBER := p_line_location_id;
1103 l_distribution_id PO_TBL_NUMBER := p_distribution_id;
1104
1105 l_api_name CONSTANT VARCHAR(30) := 'PO_STATUS_CHECK';
1106 l_api_version CONSTANT NUMBER := 1.0;
1107 l_progress VARCHAR2(3) := '000';
1108
1109 BEGIN
1110
1111 --Standard call to check for call compatibility
1112 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1114 END IF;
1115
1116 --Validate Input Parameters and do any defaulting
1117 l_progress := '010';
1118 validate_status_check_inputs(
1119 p_api_version => p_api_version,
1120 p_header_id => l_header_id,
1121 p_release_id => l_release_id,
1122 p_document_type => l_document_type,
1123 p_document_subtype => l_document_subtype,
1124 p_document_num => l_document_num,
1125 p_vendor_order_num => l_vendor_order_num,
1126 p_line_id => l_line_id,
1127 p_line_location_id => l_line_location_id,
1128 p_distribution_id => l_distribution_id,
1129 p_mode => p_mode,
1130 x_po_status_rec => x_po_status_rec,
1131 x_return_status => x_return_status);
1132
1133 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1134 return;
1135 END IF;
1136
1137 --Call the private procedure to actually do po status check
1138 l_progress := '020';
1139 PO_DOCUMENT_CHECKS_PVT.po_status_check(
1140 p_api_version => p_api_version,
1141 p_header_id => l_header_id,
1142 p_release_id => l_release_id,
1143 p_document_type => l_document_type,
1144 p_document_subtype => l_document_subtype,
1145 p_document_num => l_document_num,
1146 p_vendor_order_num => l_vendor_order_num,
1147 p_line_id => l_line_id,
1148 p_line_location_id => l_line_location_id,
1149 p_distribution_id => l_distribution_id,
1150 p_mode => p_mode,
1151 p_lock_flag => p_lock_flag,
1152 p_calling_module => p_calling_module, -- PDOI Rewrite R12
1153 p_role => p_role, -- PDOI Rewrite R12
1154 p_skip_cat_upload_chk => p_skip_cat_upload_chk, -- PDOI Rewrite R12
1155 x_po_status_rec => x_po_status_rec,
1156 x_return_status => x_return_status);
1157
1158
1159 l_progress := '030';
1160
1161 EXCEPTION
1162 WHEN FND_API.G_EXC_ERROR THEN
1163 x_return_status := FND_API.G_RET_STS_ERROR;
1164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1166 WHEN OTHERS THEN
1167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1168 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
1169
1170 END po_status_check;
1171
1172 -------------------------------------------------------------------------------
1173 --Start of Comments
1174 --Name: po_status_check
1175 --Pre-reqs:
1176 -- None.
1177 --Modifies:
1178 -- None.
1179 --Locks:
1180 -- None.
1181 --Function:
1182 -- Finds the status of a Purchase Order or a Release
1183 -- This is a convenience procedure for a single entity and takes in scalar input IDs
1184 -- This in turn calls the group procedure po_status_check that takes Table input IDs
1185 --Notes:
1186 -- Detailed comments maintained in PVT Package Body PO_DOCUMENT_CHECKS_PVT.po_status_check
1187 --End of Comments
1188 -------------------------------------------------------------------------------
1189 PROCEDURE po_status_check (
1190 p_api_version IN NUMBER,
1191 p_header_id IN NUMBER := NULL,
1192 p_release_id IN NUMBER := NULL,
1193 p_document_type IN VARCHAR2 := NULL,
1194 p_document_subtype IN VARCHAR2 := NULL,
1195 p_document_num IN VARCHAR2 := NULL,
1196 p_vendor_order_num IN VARCHAR2 := NULL,
1197 p_line_id IN NUMBER := NULL,
1198 p_line_location_id IN NUMBER := NULL,
1199 p_distribution_id IN NUMBER := NULL,
1200 p_mode IN VARCHAR2,
1201 p_lock_flag IN VARCHAR2 := 'N',
1202 p_calling_module IN VARCHAR2 := NULL, -- PDOI Rewrite R12
1203 p_role IN VARCHAR2 := NULL, -- PDOI Rewrite R12
1204 p_skip_cat_upload_chk IN VARCHAR2 := NULL, -- PDOI Rewrite R12
1205 x_po_status_rec OUT NOCOPY PO_STATUS_REC_TYPE,
1206 x_return_status OUT NOCOPY VARCHAR2
1207 ) IS
1208
1209 l_api_name CONSTANT VARCHAR(30) := 'PO_STATUS_CHECK';
1210 l_api_version CONSTANT NUMBER := 1.0;
1211 l_progress VARCHAR2(3) := '000';
1212
1213 BEGIN
1214
1215 --Standard call to check for call compatibility
1216 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1218 END IF;
1219
1220 --Call the overloaded procedure that takes in Table IDs after
1221 -- creating size=1 Tables of IDs 1 with value of the scalar input IDs
1222 l_progress := '010';
1223 PO_DOCUMENT_CHECKS_GRP.po_status_check(
1224 p_api_version => p_api_version,
1225 p_header_id => PO_TBL_NUMBER(p_header_id),
1226 p_release_id => PO_TBL_NUMBER(p_release_id),
1227 p_document_type => PO_TBL_VARCHAR30(p_document_type),
1228 p_document_subtype => PO_TBL_VARCHAR30(p_document_subtype),
1229 p_document_num => PO_TBL_VARCHAR30(p_document_num),
1230 p_vendor_order_num => PO_TBL_VARCHAR30(p_vendor_order_num),
1231 p_line_id => PO_TBL_NUMBER(p_line_id),
1232 p_line_location_id => PO_TBL_NUMBER(p_line_location_id),
1233 p_distribution_id => PO_TBL_NUMBER(p_distribution_id),
1234 p_mode => p_mode,
1235 p_lock_flag => p_lock_flag,
1236 p_calling_module => p_calling_module, -- PDOI Rewrite R12
1237 p_role => p_role, -- PDOI Rewrite R12
1238 p_skip_cat_upload_chk => p_skip_cat_upload_chk, -- PDOI Rewrite R12
1239 x_po_status_rec => x_po_status_rec,
1240 x_return_status => x_return_status);
1241
1242 l_progress := '020';
1243
1244 EXCEPTION
1245 WHEN FND_API.G_EXC_ERROR THEN
1246 x_return_status := FND_API.G_RET_STS_ERROR;
1247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1249 WHEN OTHERS THEN
1250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1251 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
1252
1253 END po_status_check;
1254
1255 -- Bug 3312906 START
1256 -------------------------------------------------------------------------------
1257 --Start of Comments
1258 --Name: check_std_po_price_updateable
1259 --Function:
1260 -- Checks whether price updates are allowed on this Standard PO line.
1261 --Pre-reqs:
1262 -- N/A
1263 --Modifies:
1264 -- standard API message list
1265 --Locks:
1266 -- None.
1267 --Parameters:
1268 --IN:
1269 --p_api_version
1270 -- API version expected by the caller
1271 --p_po_line_id
1272 -- ID of a Standard PO line
1273 --p_from_price_break
1274 -- PO_CORE_S.G_PARAMETER_YES means that the price update is coming from a
1275 -- price break;
1276 -- PO_CORE_S.G_PARAMETER_NO means that the price update is coming from the user.
1277 --p_add_reasons_to_msg_list
1278 -- (Only applies if x_price_updateable = PO_CORE_S.G_PARAMETER_NO.)
1279 -- If PO_CORE_S.G_PARAMETER_YES, the API will add the reasons why price updates
1280 -- are not allowed to the standard API message list; otherwise, the API will not
1281 -- add the reasons to the message list.
1282 --OUT:
1283 --x_return_status
1284 -- FND_API.G_RET_STS_SUCCESS if the API completed successfully.
1285 -- FND_API.G_RET_STS_ERROR if there was an error.
1286 -- FND_API.G_RET_STS_UNEXP_ERROR if there was an unexpected error.
1287 --x_price_updateable
1288 -- PO_CORE_S.G_PARAMETER_YES if price updates are allowed on this shipment,
1289 -- PO_CORE_S.G_PARAMETER_NO otherwise
1290 --x_retroactive_price_change
1291 -- PO_CORE_S.G_PARAMETER_YES if a price update on this PO line would be
1292 -- considered a retroactive price change, PO_CORE_S.G_PARAMETER_NO otherwise.
1293 --End of Comments
1294 -------------------------------------------------------------------------------
1295 PROCEDURE check_std_po_price_updateable (
1296 p_api_version IN NUMBER,
1297 x_return_status OUT NOCOPY VARCHAR2,
1298 p_po_line_id IN PO_LINES_ALL.po_line_id%TYPE,
1299 p_from_price_break IN VARCHAR2,
1300 p_add_reasons_to_msg_list IN VARCHAR2,
1301 x_price_updateable OUT NOCOPY VARCHAR2,
1302 x_retroactive_price_change OUT NOCOPY VARCHAR2
1303 ) IS
1304 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_STD_PO_PRICE_UPDATEABLE';
1305 l_log_head CONSTANT VARCHAR2(100):= g_log_head || l_api_name;
1306 l_api_version CONSTANT NUMBER := 1.0;
1307 l_progress VARCHAR2(3) := '000';
1308
1309 l_has_ga_ref PO_HEADERS_ALL.global_agreement_flag%TYPE;
1310 l_allow_price_override PO_LINES_ALL.allow_price_override_flag%TYPE;
1311 l_accrue_invoice_count NUMBER;
1312 l_pending_rcv_transactions NUMBER;
1313 l_archive_mode_std_po PO_DOCUMENT_TYPES.archive_external_revision_code%TYPE;
1314 l_current_org_id NUMBER;
1315
1316 l_po_header_id NUMBER; -- <Complex Work R12>
1317 l_is_complex_work_po BOOLEAN; -- <Complex Work R12>
1318 l_is_financing_po BOOLEAN; -- <Complex Work R12>
1319
1320
1321 BEGIN
1322 IF g_debug_stmt THEN
1323 PO_DEBUG.debug_begin(l_log_head);
1324 END IF;
1325
1326 -- Standard API initialization:
1327 IF NOT FND_API.compatible_api_call (
1328 p_current_version_number => l_api_version,
1329 p_caller_version_number => p_api_version,
1330 p_api_name => l_api_name,
1331 p_pkg_name => g_pkg_name ) THEN
1332 RAISE FND_API.g_exc_unexpected_error;
1333 END IF;
1334 x_return_status := FND_API.G_RET_STS_SUCCESS;
1335
1336 -- Default: price updates allowed, no retroactive price change.
1337 x_price_updateable := PO_CORE_S.G_PARAMETER_YES;
1338 x_retroactive_price_change := PO_CORE_S.G_PARAMETER_NO;
1339
1340 l_progress := '010';
1341
1342 -- <Complex Work R12 START>
1343 SELECT pol.po_header_id
1344 INTO l_po_header_id
1345 FROM po_lines_all pol
1346 WHERE pol.po_line_id = p_po_line_id;
1347
1348 l_is_complex_work_po := PO_COMPLEX_WORK_PVT.is_complex_work_po(
1349 p_po_header_id => l_po_header_id);
1350
1351 IF (NOT l_is_complex_work_po) THEN
1352 -- <Complex Work R12 END>
1353
1354 -- SQL What: Retrieve the GA flag and the Allow Price Override flag
1355 -- from the referenced document.
1356 -- SQL Why: Used in the checks below.
1357 SELECT NVL(REFH.global_agreement_flag, 'N'),
1358 NVL(REFL.allow_price_override_flag, 'N')
1359 INTO l_has_ga_ref,
1360 l_allow_price_override
1361 FROM po_lines_all POL,
1362 po_headers_all REFH,
1363 po_lines_all REFL
1364 WHERE POL.po_line_id = p_po_line_id
1365 AND REFH.po_header_id (+) = POL.from_header_id -- JOIN
1366 AND REFL.po_line_id (+) = POL.from_line_id; -- JOIN
1367
1368 -- Bug 3565522 : get the archive mode
1369 l_archive_mode_std_po := PO_DOCUMENT_ARCHIVE_PVT.get_archive_mode(
1370 p_doc_type => 'PO',
1371 p_doc_subtype => 'STANDARD');
1372
1373 l_current_org_id := PO_GA_PVT.get_current_org;
1374
1375 ----------------------------------------------------------------------------
1376 -- Check: Do not allow price updates if a receipt has been created against
1377 -- one of the line's shipments and it is accrued upon receipt, or if an
1378 -- invoice has been created against one of the line's shipments.
1379 -- * Exception: Allow such updates if the retroactive pricing mode is set
1380 -- to "All Releases" and the adjustment account is valid
1381 -- Bug 3565522 : Allow retroactive price changes for all releases only
1382 -- when the archive is set to approve
1383 ----------------------------------------------------------------------------
1384 l_progress := '020';
1385 IF (PO_RETROACTIVE_PRICING_PVT.get_retro_mode() = 'ALL_RELEASES') AND
1386 (l_archive_mode_std_po = 'APPROVE' ) AND
1387 not (PO_CORE_S.is_encumbrance_on(p_doc_type => 'PO',
1388 p_org_id => l_current_org_id)) AND
1389 -- Bug 3231062
1390 (PO_RETROACTIVE_PRICING_PVT.Is_Retro_Project_Allowed(
1391 p_std_po_price_change => 'Y',
1392 p_po_line_id => p_po_line_id,
1393 p_po_line_loc_id => null ) = 'Y')
1394 THEN
1395 -- Allow price updates. Remember this as a retroactive price change.
1396 x_retroactive_price_change := PO_CORE_S.G_PARAMETER_YES;
1397
1398 -- Bug 3339149
1399 IF (PO_RETROACTIVE_PRICING_PVT.Is_Adjustment_Account_Valid(
1400 p_std_po_price_change => 'Y',
1401 p_po_line_id => p_po_line_id,
1402 p_po_line_loc_id => null ) = 'N')
1403 THEN
1404
1405 x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
1406 x_retroactive_price_change := PO_CORE_S.G_PARAMETER_NO;
1407
1408 IF (p_add_reasons_to_msg_list = PO_CORE_S.G_PARAMETER_YES) THEN
1409 FND_MESSAGE.set_name('PO','PO_RETRO_PRICING_NOT_ALLOWED');
1410 FND_MSG_PUB.add;
1411 END IF;
1412 END IF;
1413
1414 ELSE
1415 -- SQL What: Returns the number of shipments of this line that are
1416 -- either received and accrued, or invoiced.
1417 SELECT count(*)
1418 INTO l_accrue_invoice_count
1419 FROM po_line_locations_all
1420 WHERE (po_line_id = p_po_line_id)
1421 AND ((NVL(quantity_received,0) > 0 AND accrue_on_receipt_flag = 'Y')
1422 OR (NVL(quantity_billed,0) > 0));
1423
1424 IF (l_accrue_invoice_count > 0) THEN
1425 x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
1426 IF (p_add_reasons_to_msg_list = PO_CORE_S.G_PARAMETER_YES) THEN
1427 FND_MESSAGE.set_name('PO','PO_CHNG_PRICE_RESTRICTED');
1428 FND_MSG_PUB.add;
1429 END IF;
1430 END IF;
1431 END IF; -- if retro mode is all_releases
1432
1433 ----------------------------------------------------------------------------
1434 -- Check: For a standard PO referencing a GA, do not allow line price
1435 -- changes (except those from price breaks) if the GA line has
1436 -- Allow Price Override set to No.
1437 ----------------------------------------------------------------------------
1438 l_progress := '030';
1439 IF (l_has_ga_ref = 'Y')
1440 AND (NVL(p_from_price_break, PO_CORE_S.G_PARAMETER_NO)
1441 = PO_CORE_S.G_PARAMETER_NO)
1442 AND (l_allow_price_override = 'N') THEN
1443 x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
1444 IF (p_add_reasons_to_msg_list = PO_CORE_S.G_PARAMETER_YES) THEN
1445 FND_MESSAGE.set_name('PO','PO_CHNG_GA_NO_PRICE_OVERRIDE');
1446 FND_MSG_PUB.add;
1447 END IF;
1448 END IF; -- l_has_ga_ref
1449
1450 -- <Complex Work R12 START>
1451 ELSE -- IF (NOT l_is_complex_work_po)
1452 l_is_financing_po := PO_COMPLEX_WORK_PVT.is_financing_po(
1453 p_po_header_id => l_po_header_id);
1454
1455 IF (l_is_financing_po) THEN
1456 -- SQL What: See if the delivery shipment has been executed against
1457 SELECT COUNT(*)
1458 INTO l_accrue_invoice_count
1459 FROM po_line_locations_all pll
1460 WHERE po_line_id = p_po_line_id
1461 AND pll.payment_type = 'DELIVERY'
1462 AND ( ( NVL(quantity_received,0) > 0
1463 AND accrue_on_receipt_flag = 'Y'
1464 )
1465 OR NVL(quantity_billed,0) > 0
1466 );
1467
1468 IF (l_accrue_invoice_count > 0) THEN
1469 x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
1470 IF (p_add_reasons_to_msg_list = PO_CORE_S.G_PARAMETER_YES) THEN
1471 FND_MESSAGE.set_name('PO','PO_CHNG_PRICE_RESTRICTED');
1472 FND_MSG_PUB.add;
1473 END IF;
1474 END IF;
1475 END IF; -- IF (l_is_financing_po) THEN
1476 END IF; -- IF (NOT l_is_complex_work_po)
1477 -- <Complex Work R12 END>
1478
1479 ----------------------------------------------------------------------------
1480 -- Check: Do not allow price changes if there are pending receiving
1481 -- transactions for any shipments of this line.
1482 ----------------------------------------------------------------------------
1483 l_progress := '040';
1484
1485 -- SQL What: Returns 1 if there are any pending receiving transactions
1486 -- for the shipments of this line, 0 otherwise.
1487 -- SQL Why: To prevent price changes if there are pending transactions.
1488 SELECT count(*)
1489 INTO l_pending_rcv_transactions
1490 FROM dual
1491 WHERE EXISTS
1492 ( SELECT 1
1493 FROM rcv_transactions_interface RTI, po_line_locations_all PLL
1494 WHERE PLL.po_line_id = p_po_line_id
1495 AND RTI.po_line_location_id = PLL.line_location_id -- JOIN
1496 AND RTI.transaction_status_code = 'PENDING' );
1497
1498 IF (l_pending_rcv_transactions > 0) THEN
1499 x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
1500 IF (p_add_reasons_to_msg_list = PO_CORE_S.G_PARAMETER_YES) THEN
1501 FND_MESSAGE.set_name('PO','PO_RCV_TRANSACTION_PENDING');
1502 FND_MSG_PUB.add;
1503 END IF;
1504 END IF;
1505
1506 IF g_debug_stmt THEN
1507 PO_DEBUG.debug_end(l_log_head);
1508 END IF;
1509 EXCEPTION
1510 WHEN FND_API.G_EXC_ERROR THEN
1511 x_return_status := FND_API.G_RET_STS_ERROR;
1512 IF g_debug_unexp THEN
1513 PO_DEBUG.debug_exc(l_log_head, l_progress);
1514 END IF;
1515 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1517 IF g_debug_unexp THEN
1518 PO_DEBUG.debug_exc(l_log_head, l_progress);
1519 END IF;
1520 WHEN OTHERS THEN
1521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1522 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||'.'||l_progress);
1523 IF g_debug_unexp THEN
1524 PO_DEBUG.debug_exc(l_log_head, l_progress);
1525 END IF;
1526 END check_std_po_price_updateable;
1527
1528 -------------------------------------------------------------------------------
1529 --Start of Comments
1530 --Name: check_rel_price_updateable
1531 --Function:
1532 -- Checks whether price updates are allowed on this release shipment.
1533 --Pre-reqs:
1534 -- N/A
1535 --Modifies:
1536 -- standard API message list
1537 --Locks:
1538 -- None.
1539 --Parameters:
1540 --IN:
1541 --p_api_version
1542 -- API version expected by the caller
1543 --p_line_location_id
1544 -- ID of a release shipment
1545 --p_from_price_break
1546 -- PO_CORE_S.G_PARAMETER_YES means that the price update is coming from a
1547 -- price break;
1548 -- PO_CORE_S.G_PARAMETER_NO means that the price update is coming from the user.
1549 --p_add_reasons_to_msg_list
1550 -- (Only applies if x_price_updateable = PO_CORE_S.G_PARAMETER_NO.)
1551 -- If PO_CORE_S.G_PARAMETER_YES, the API will add the reasons why price updates
1552 -- are not allowed to the standard API message list; otherwise, the API will not
1553 -- add the reasons to the message list.
1554 --OUT:
1555 --x_return_status
1556 -- FND_API.G_RET_STS_SUCCESS if the API completed successfully.
1557 -- FND_API.G_RET_STS_ERROR if there was an error.
1558 -- FND_API.G_RET_STS_UNEXP_ERROR if there was an unexpected error.
1559 --x_price_updateable
1560 -- PO_CORE_S.G_PARAMETER_YES if price updates are allowed on this shipment,
1561 -- PO_CORE_S.G_PARAMETER_NO otherwise
1562 --x_retroactive_price_change
1563 -- PO_CORE_S.G_PARAMETER_YES if a price update on this release shipment would
1564 -- be considered a retroactive price change, PO_CORE_S.G_PARAMETER_NO otherwise.
1565 --End of Comments
1566 -------------------------------------------------------------------------------
1567 PROCEDURE check_rel_price_updateable (
1568 p_api_version IN NUMBER,
1569 x_return_status OUT NOCOPY VARCHAR2,
1570 p_line_location_id IN PO_LINE_LOCATIONS_ALL.line_location_id%TYPE,
1571 p_from_price_break IN VARCHAR2,
1572 p_add_reasons_to_msg_list IN VARCHAR2,
1573 x_price_updateable OUT NOCOPY VARCHAR2,
1574 x_retroactive_price_change OUT NOCOPY VARCHAR2
1575 )
1576 IS
1577 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_REL_PRICE_UPDATEABLE';
1578 l_log_head CONSTANT VARCHAR2(100):= g_log_head || l_api_name;
1579 l_api_version CONSTANT NUMBER := 1.0;
1580 l_progress VARCHAR2(3) := '000';
1581
1582 l_allow_price_override PO_LINES_ALL.allow_price_override_flag%TYPE;
1583 l_qty_received PO_LINE_LOCATIONS.quantity_received%TYPE;
1584 l_accrue_flag PO_LINE_LOCATIONS.accrue_on_receipt_flag%TYPE;
1585 l_qty_billed PO_LINE_LOCATIONS.quantity_billed%TYPE;
1586 l_pending_rcv_transactions NUMBER;
1587 -- Bug 3565522
1588 l_archive_mode_rel PO_DOCUMENT_TYPES.archive_external_revision_code%TYPE;
1589 l_current_org_id NUMBER;
1590
1591 BEGIN
1592 IF g_debug_stmt THEN
1593 PO_DEBUG.debug_begin(l_log_head);
1594 END IF;
1595
1596 -- Standard API initialization:
1597 IF NOT FND_API.compatible_api_call (
1598 p_current_version_number => l_api_version,
1599 p_caller_version_number => p_api_version,
1600 p_api_name => l_api_name,
1601 p_pkg_name => g_pkg_name ) THEN
1602 RAISE FND_API.g_exc_unexpected_error;
1603 END IF;
1604 x_return_status := FND_API.G_RET_STS_SUCCESS;
1605
1606 -- Default: price updates allowed, no retroactive price change.
1607 x_price_updateable := PO_CORE_S.G_PARAMETER_YES;
1608 x_retroactive_price_change := PO_CORE_S.G_PARAMETER_NO;
1609
1610 l_progress := '010';
1611
1612 -- SQL What: Retrieve the GA flag and the Allow Price Override flag
1613 -- from the referenced document.
1614 -- SQL Why: Used in the checks below.
1615 SELECT NVL(PLL.quantity_received,0),
1616 NVL(PLL.accrue_on_receipt_flag,'N'),
1617 NVL(PLL.quantity_billed,0),
1618 NVL(POL.allow_price_override_flag, 'N')
1619 INTO l_qty_received,
1620 l_accrue_flag,
1621 l_qty_billed,
1622 l_allow_price_override
1623 FROM po_line_locations_all PLL,
1624 po_lines_all POL
1625 WHERE PLL.line_location_id = p_line_location_id
1626 AND PLL.po_line_id = POL.po_line_id; -- JOIN
1627
1628 -- Bug 3565522 : get the archive mode
1629 l_archive_mode_rel := PO_DOCUMENT_ARCHIVE_PVT.get_archive_mode(
1630 p_doc_type => 'RELEASE',
1631 p_doc_subtype => 'BLANKET');
1632 l_current_org_id := PO_GA_PVT.get_current_org;
1633
1634 ----------------------------------------------------------------------------
1635 -- Check: Do not allow price updates if a receipt has been created against
1636 -- the shipments and it is accrued upon receipt, or if an invoice has been
1637 -- created against one of the line's shipments.
1638 -- * Exception: Allow such updates if the retroactive pricing mode is set to
1639 -- "All Releases". In this case the adjustment account should be valid
1640 -- Bug 3565522 : Allow retroactive price changes for all releases only
1641 -- when the archive is set to approve
1642 ----------------------------------------------------------------------------
1643 l_progress := '020';
1644 IF (PO_RETROACTIVE_PRICING_PVT.get_retro_mode() = 'ALL_RELEASES') AND
1645 (l_archive_mode_rel = 'APPROVE') AND
1646 not (PO_CORE_S.is_encumbrance_on(p_doc_type => 'RELEASE',
1647 p_org_id => l_current_org_id)) AND
1648 -- Bug 3231062
1649 (PO_RETROACTIVE_PRICING_PVT.Is_Retro_Project_Allowed(
1650 p_std_po_price_change => 'N',
1651 p_po_line_id => null,
1652 p_po_line_loc_id => p_line_location_id ) = 'Y')
1653 THEN
1654 -- Allow price updates. Remember this as a retroactive price change.
1655 x_retroactive_price_change := PO_CORE_S.G_PARAMETER_YES;
1656
1657 -- Bug 3339149
1658 IF (PO_RETROACTIVE_PRICING_PVT.Is_Adjustment_Account_Valid(
1659 p_std_po_price_change => 'N',
1660 p_po_line_id => null,
1661 p_po_line_loc_id => p_line_location_id ) = 'N')
1662 THEN
1663
1664 x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
1665 x_retroactive_price_change := PO_CORE_S.G_PARAMETER_NO;
1666
1667 IF (p_add_reasons_to_msg_list = PO_CORE_S.G_PARAMETER_YES) THEN
1668 FND_MESSAGE.set_name('PO','PO_RETRO_PRICING_NOT_ALLOWED');
1669 FND_MSG_PUB.add;
1670 END IF;
1671 END IF;
1672
1673 ELSE
1674 IF ((l_qty_received > 0) AND (l_accrue_flag = 'Y'))
1675 OR (l_qty_billed > 0) THEN
1676 x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
1677 IF (p_add_reasons_to_msg_list = PO_CORE_S.G_PARAMETER_YES) THEN
1678 FND_MESSAGE.set_name('PO','PO_CHNG_PRICE_RESTRICTED');
1679 FND_MSG_PUB.add;
1680 END IF;
1681 END IF;
1682 END IF; -- g_retropricing_mode
1683
1684 ----------------------------------------------------------------------------
1685 -- Check: Do not allow shipment price changes (except those from price breaks)
1686 -- if the blanket line has Allow Price Override set to No.
1687 ----------------------------------------------------------------------------
1688 l_progress := '030';
1689 IF (NVL(p_from_price_break, PO_CORE_S.G_PARAMETER_NO)
1690 = PO_CORE_S.G_PARAMETER_NO)
1691 AND (l_allow_price_override = 'N') THEN
1692 x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
1693 IF (p_add_reasons_to_msg_list = PO_CORE_S.G_PARAMETER_YES) THEN
1694 FND_MESSAGE.set_name('PO','PO_CHNG_NO_PRICE_OVERRIDE');
1695 FND_MSG_PUB.add;
1696 END IF;
1697 END IF;
1698
1699 ----------------------------------------------------------------------------
1700 -- Check: Do not allow price changes if there are pending receiving
1701 -- transactions for this shipment.
1702 ----------------------------------------------------------------------------
1703 l_progress := '040';
1704
1705 -- SQL What: Returns 1 if there are any pending receiving transactions
1706 -- for the shipments of this line, 0 otherwise.
1707 -- SQL Why: To prevent price changes if there are pending transactions.
1708 SELECT count(*)
1709 INTO l_pending_rcv_transactions
1710 FROM dual
1711 WHERE EXISTS
1712 ( SELECT 1
1713 FROM rcv_transactions_interface RTI
1714 WHERE RTI.po_line_location_id = p_line_location_id
1715 AND RTI.transaction_status_code = 'PENDING' );
1716
1717 IF (l_pending_rcv_transactions > 0) THEN
1718 x_price_updateable := PO_CORE_S.G_PARAMETER_NO;
1719 IF (p_add_reasons_to_msg_list = PO_CORE_S.G_PARAMETER_YES) THEN
1720 FND_MESSAGE.set_name('PO','PO_RCV_TRANSACTION_PENDING');
1721 FND_MSG_PUB.add;
1722 END IF;
1723 END IF;
1724
1725 IF g_debug_stmt THEN
1726 PO_DEBUG.debug_end(l_log_head);
1727 END IF;
1728 EXCEPTION
1729 WHEN FND_API.G_EXC_ERROR THEN
1730 x_return_status := FND_API.G_RET_STS_ERROR;
1731 IF g_debug_unexp THEN
1732 PO_DEBUG.debug_exc(l_log_head, l_progress);
1733 END IF;
1734 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1736 IF g_debug_unexp THEN
1737 PO_DEBUG.debug_exc(l_log_head, l_progress);
1738 END IF;
1739 WHEN OTHERS THEN
1740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1741 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||'.'||l_progress);
1742 IF g_debug_unexp THEN
1743 PO_DEBUG.debug_exc(l_log_head, l_progress);
1744 END IF;
1745 END check_rel_price_updateable;
1746 -- Bug 3312906 END
1747
1748 -- <Complex Work R12 START>
1749 -- Checks if a pay item price is updateable
1750 PROCEDURE check_payitem_price_updateable (
1751 p_api_version IN NUMBER
1752 , p_line_location_id IN NUMBER
1753 , p_add_reasons_to_msg_list IN VARCHAR2
1754 , x_return_status OUT NOCOPY VARCHAR2
1755 , x_price_updateable OUT NOCOPY VARCHAR2
1756 ) IS
1757
1758 d_module VARCHAR2(70) :=
1759 'po.plsql.PO_DOCUMENT_CHECKS_GRP.check_payitem_price_updateable';
1760 d_progress NUMBER;
1761 l_is_price_updateable BOOLEAN;
1762
1763 BEGIN
1764 d_progress := 0;
1765 IF (PO_LOG.d_proc) THEN
1766 PO_LOG.proc_begin(d_module, 'p_api_version', p_api_version);
1767 PO_LOG.proc_begin(d_module, 'p_line_location_id', p_line_location_id);
1768 PO_LOG.proc_begin(d_module, 'p_add_reasons_to_msg_list', p_add_reasons_to_msg_list);
1769 END IF;
1770
1771 d_progress := 10;
1772
1773 x_return_status := FND_API.g_ret_sts_success;
1774
1775 l_is_price_updateable := PO_DOCUMENT_CHECKS_PVT.is_pay_item_price_updateable(
1776 p_line_location_id => p_line_location_id
1777 , p_add_reasons_to_msg_list => p_add_reasons_to_msg_list);
1778
1779 d_progress := 20;
1780
1781 IF (l_is_price_updateable) THEN
1782 x_price_updateable := PO_CORE_S.g_parameter_yes;
1783 ELSE
1784 x_price_updateable := PO_CORE_S.g_parameter_no;
1785 END IF;
1786
1787 d_progress := 30;
1788
1789 IF (PO_LOG.d_proc) THEN
1790 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1791 PO_LOG.proc_end(d_module, 'x_price_updateable', x_price_updateable);
1792 PO_LOG.proc_end(d_module);
1793 END IF;
1794
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 IF (PO_LOG.d_exc) THEN
1798 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1799 PO_LOG.proc_end(d_module);
1800 END IF;
1801 x_return_status := FND_API.g_ret_sts_unexp_error;
1802 x_price_updateable := PO_CORE_S.g_parameter_no;
1803 END;
1804 -- <Complex Work R12 END>
1805
1806 -- Bug 5560980 START
1807 -------------------------------------------------------------------------------
1808 --Start of Comments
1809 --Name: merge_online_report_id
1810 --Function:
1811 -- Private procedure to combine the two online reports to one new report and
1812 -- return the single id for the new combined report
1813 --End of Comments
1814 -------------------------------------------------------------------------------
1815 PROCEDURE merge_online_report_id
1816 (
1817 p_report_id1 IN NUMBER,
1818 p_report_id2 IN NUMBER,
1819 x_new_report_id OUT NOCOPY NUMBER -- id for new combined online report
1820 )
1821 IS
1822 d_module VARCHAR2(70) :=
1823 'po.plsql.PO_DOCUMENT_CHECKS_GRP.merge_online_report_id';
1824 d_progress NUMBER;
1825 l_id1_max_seq NUMBER; -- max sequence number value for the first online report
1826 l_id2_seq_list PO_TBL_NUMBER; -- list for all sequence numbers of report 2
1827
1828 BEGIN
1829 d_progress := 0;
1830 IF (PO_LOG.d_proc) THEN
1831 PO_LOG.proc_begin(d_module, 'p_report_id1', p_report_id1);
1832 PO_LOG.proc_begin(d_module, 'p_report_id2', p_report_id2);
1833 END IF;
1834
1835 d_progress := 10;
1836
1837 -- SQL What: Get the id from the given nline report sequence
1838 -- SQL Why: Used for the new combined online report below.
1839 SELECT po_online_report_text_s.nextval
1840 INTO x_new_report_id
1841 FROM sys.dual;
1842
1843 IF (PO_LOG.d_stmt) THEN
1844 PO_LOG.stmt(d_module,d_progress,'After got the new id for combined online report');
1845 END IF;
1846
1847 d_progress := 20;
1848
1849 -- SQL What: Copy all data rows from the online report 1 to the new combined
1850 -- online report.
1851 -- SQL Why: Construct the 1st part of the new combined online report.
1852 INSERT INTO PO_ONLINE_REPORT_TEXT
1853 (
1854 online_report_id
1855 ,sequence
1856 ,last_updated_by
1857 ,last_update_date
1858 ,created_by
1859 ,creation_date
1860 ,last_update_login
1861 ,text_line
1862 ,line_num
1863 ,shipment_num
1864 ,distribution_num
1865 ,transaction_level
1866 ,quantity
1867 ,transaction_id
1868 ,transaction_date
1869 ,transaction_type
1870 ,transaction_uom
1871 ,transaction_location
1872 ,request_id
1873 ,program_application_id
1874 ,program_id
1875 ,program_update_date
1876 ,message_type
1877 ,show_in_psa_flag
1878 ,segment1
1879 ,distribution_type
1880 )
1881 SELECT
1882 x_new_report_id
1883 ,sequence
1884 ,last_updated_by
1885 ,last_update_date
1886 ,created_by
1887 ,creation_date
1888 ,last_update_login
1889 ,text_line
1890 ,line_num
1891 ,shipment_num
1892 ,distribution_num
1893 ,transaction_level
1894 ,quantity
1895 ,transaction_id
1896 ,transaction_date
1897 ,transaction_type
1898 ,transaction_uom
1899 ,transaction_location
1900 ,request_id
1901 ,program_application_id
1902 ,program_id
1903 ,program_update_date
1904 ,message_type
1905 ,show_in_psa_flag
1906 ,segment1
1907 ,distribution_type
1908 FROM PO_ONLINE_REPORT_TEXT
1909 WHERE online_report_id = p_report_id1
1910 ORDER BY sequence ASC;
1911
1912 IF (PO_LOG.d_stmt) THEN
1913 PO_LOG.stmt(d_module,d_progress,'After inserted data of report 1 to the combined report, #rows='||SQL%ROWCOUNT);
1914 END IF;
1915
1916 d_progress := 30;
1917
1918 -- SQL What: Retrieve the max sequence number value from all rows of online report 1.
1919 -- SQL Why: To set the start value for the sequence increment used to add rows from report 2.
1920 SELECT MAX(sequence)
1921 INTO l_id1_max_seq
1922 FROM PO_ONLINE_REPORT_TEXT
1923 WHERE online_report_id = p_report_id1;
1924
1925 IF (PO_LOG.d_stmt) THEN
1926 PO_LOG.stmt(d_module,d_progress,'After got the max sequence for online report 1 rows as: '||l_id1_max_seq);
1927 END IF;
1928
1929 d_progress := 40;
1930
1931 -- SQL What: Retrieve list for all sequence numbers of report 2
1932 -- SQL Why: Used to count how many rows from report 2 need to be added to combined report.
1933 SELECT sequence
1934 BULK COLLECT INTO l_id2_seq_list
1935 FROM PO_ONLINE_REPORT_TEXT
1936 WHERE online_report_id = p_report_id2
1937 ORDER BY sequence ASC;
1938
1939 IF (PO_LOG.d_stmt) THEN
1940 PO_LOG.stmt(d_module,d_progress,'After got the sequence list for online report 2');
1941 END IF;
1942
1943 d_progress := 50;
1944
1945 -- SQL What: For each row of reprot 2, change the sequence number to the series incremented
1946 -- from the max sequence number from report1. Then add the row into
1947 -- the combined report.
1948 -- SQL Why: Construct the 2nd part of the new combined online report.
1949 FOR i IN 1 .. l_id2_seq_list.COUNT
1950 LOOP
1951 INSERT INTO PO_ONLINE_REPORT_TEXT
1952 (
1953 online_report_id
1954 ,sequence
1955 ,last_updated_by
1956 ,last_update_date
1957 ,created_by
1958 ,creation_date
1959 ,last_update_login
1960 ,text_line
1961 ,line_num
1962 ,shipment_num
1963 ,distribution_num
1964 ,transaction_level
1965 ,quantity
1966 ,transaction_id
1967 ,transaction_date
1968 ,transaction_type
1969 ,transaction_uom
1970 ,transaction_location
1971 ,request_id
1972 ,program_application_id
1973 ,program_id
1974 ,program_update_date
1975 ,message_type
1976 ,show_in_psa_flag
1977 ,segment1
1978 ,distribution_type
1979 )
1980 SELECT
1981 x_new_report_id
1982 ,l_id1_max_seq + i -- increment
1983 ,last_updated_by
1984 ,last_update_date
1985 ,created_by
1986 ,creation_date
1987 ,last_update_login
1988 ,text_line
1989 ,line_num
1990 ,shipment_num
1991 ,distribution_num
1992 ,transaction_level
1993 ,quantity
1994 ,transaction_id
1995 ,transaction_date
1996 ,transaction_type
1997 ,transaction_uom
1998 ,transaction_location
1999 ,request_id
2000 ,program_application_id
2001 ,program_id
2002 ,program_update_date
2003 ,message_type
2004 ,show_in_psa_flag
2005 ,segment1
2006 ,distribution_type
2007 FROM PO_ONLINE_REPORT_TEXT
2008 WHERE online_report_id = p_report_id2 and sequence = l_id2_seq_list(i);
2009 END LOOP;
2010
2011 IF (PO_LOG.d_stmt) THEN
2012 PO_LOG.stmt(d_module,d_progress,'After inserted data of report 2 to the combined report');
2013 END IF;
2014
2015 d_progress := 100;
2016
2017 IF (PO_LOG.d_proc) THEN
2018 PO_LOG.proc_end(d_module, 'x_new_report_id', x_new_report_id);
2019 END IF;
2020
2021 EXCEPTION
2022 WHEN OTHERS THEN
2023 IF (PO_LOG.d_exc) THEN
2024 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2025 PO_LOG.proc_end(d_module);
2026 END IF;
2027 END merge_online_report_id;
2028
2029 -------------------------------------------------------------------------------
2030 --Start of Comments
2031 --Name: po_combined_submission_check
2032 --Function:
2033 -- Call both Copy_Doc submission check and regular po submission check,
2034 -- then combine the two online reports to one and return the single report id
2035 /**
2036 * Public Procedure:
2037 * Requires:
2038 * IN PARAMETERS:
2039 * p_api_version: Version number of API that caller expects. It
2040 * should match the l_api_version defined in the
2041 * procedure
2042 * p_action_requested: In FPJ, the Action requested should be in
2043 * g_action_(DOC_SUBMISSION_CHECK, UNRESERVE)
2044 * p_document_type: The type of the document to perform
2045 * the submission check on.
2046 * p_document_subtype: The subtype of the document.
2047 * Valid Document types and Document subtypes are
2048 * Document Type Document Subtype
2049 * REQUISITION --->
2050 * RELEASE ---> SCHEDULED/BLANKET
2051 * PO ---> PLANNED/STANDARD
2052 * PA ---> CONTRACT/BLANKET
2053 --
2054 -- <FPJ ENCUMBRANCE>
2055 -- The following 2 parameters replace the p_document_id parameter.
2056 --p_document_level
2057 -- The type of id that is being passed. Use g_doc_level_<>
2058 -- HEADER
2059 -- The following are only supported for the UNRESERVE action:
2060 -- LINE
2061 -- SHIPMENT
2062 -- DISTRIBUTION
2063 --p_document_level_id
2064 -- Id of the doc level type on which to perform the check.
2065 --
2066 --
2067 --p_org_id
2068 -- If not NULL, this org context will be set.
2069 --
2070 --
2071 * p_requested_changes: This object contains all the requested changes to
2072 * the document. It contains 5 objects. These objects
2073 * are: 1.Header_Changes 2.Release_Changes 3.Line_
2074 * Changes 4.Shipment_Changes 5.Distribution_Changes.
2075 * In FPI, following change requests are allowed:
2076 * 1. HEADER_CHANGES: None
2077 * 2. RELEASE_CHANGES: None
2078 * 3. LINE_CHANGES: unit_price, vendor_product_num
2079 * 4. SHIPMENT_CHANGES: quantity, promised_date,
2080 * price_override
2081 * 5. DISTRIBUTION_CHANGES: quantity_ordered
2082 * p_check_asl: Determines whether or not to perform the checks:
2083 * PO_SUB_ITEM_NOT_APPROVED / PO_SUB_ITEM_ASL_DEBARRED
2084 * (a) TRUE : Perform check
2085 * (b) FALSE : Do not perform check
2086 *
2087 * Modifies: Inserts error msgs in online_report_text table, uses global_temp
2088 * tables for processing
2089 * Effects: This procedure runs the document submission checks on passed in
2090 * document.
2091 * Returns:
2092 * x_return_status: FND_API.G_RET_STS_SUCCESS if API succeeds
2093 * FND_API.G_RET_STS_ERROR if API fails
2094 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2095 * x_sub_check_status: FND_API.G_RET_STS_SUCCESS if document passes all
2096 * submission checks, even if warnings are found
2097 * FND_API.G_RET_STS_ERROR if document fails atleast one
2098 * submission check (returns at least one error)
2099 * x_has_warnings: FND_API.G_TRUE if submission check returns warnings
2100 * FND_API.G_FALSE if no warnings are found
2101 * x_msg_data: Contains error msg in case x_return_status returned
2102 * FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_UNEXP_ERROR
2103 * x_online_report_id: This id can be used to get all submission check (including
2104 * copydoc check and normal po submission check) errors
2105 * for given document from online_report_text table.
2106 * x_doc_check_error_record: If x_sub_check_status returned G_RET_STS_ERROR
2107 * then this object of tables will contain information about
2108 * all submission check errors for given document including
2109 * message_name and text_line.
2110 */
2111 --End of Comments
2112 -------------------------------------------------------------------------------
2113
2114 PROCEDURE po_combined_submission_check(
2115 p_api_version IN NUMBER
2116 , p_action_requested IN VARCHAR2
2117 , p_document_type IN VARCHAR2
2118 , p_document_subtype IN VARCHAR2
2119 , p_document_level IN VARCHAR2
2120 , p_document_level_id IN NUMBER
2121 , p_org_id IN NUMBER
2122 , p_requested_changes IN PO_CHANGES_REC_TYPE
2123 , p_check_asl IN BOOLEAN
2124 , p_req_chg_initiator IN VARCHAR2 := NULL -- bug4957243
2125 , p_origin_doc_id IN NUMBER := NULL -- Bug#5462677
2126 -- parameters for combination
2127 , p_from_header_id IN NUMBER
2128 , p_from_type_lookup_code IN VARCHAR2
2129 , p_po_header_id IN NUMBER
2130 , p_online_report_id IN NUMBER
2131 , p_sob_id IN NUMBER
2132 , x_return_status OUT NOCOPY VARCHAR2
2133 , x_sub_check_status OUT NOCOPY VARCHAR2
2134 , x_has_warnings OUT NOCOPY VARCHAR2 -- bug3574165
2135 , x_msg_data OUT NOCOPY VARCHAR2
2136 , x_online_report_id OUT NOCOPY NUMBER
2137 , x_doc_check_error_record OUT NOCOPY doc_check_Return_Type
2138 )
2139 IS
2140
2141 d_module VARCHAR2(70) :=
2142 'po.plsql.PO_DOCUMENT_CHECKS_GRP.po_combined_submission_check';
2143 d_progress NUMBER;
2144 l_report_id1_rownum NUMBER; --the number of data rows for report 1
2145 l_report_id2_rownum NUMBER; --the number of data rows for report 2
2146 l_report_id2 NUMBER; --id for report 2
2147 l_inv_org_id NUMBER; --bug 6713929
2148
2149 BEGIN
2150 d_progress := 0;
2151 IF (PO_LOG.d_proc) THEN
2152 PO_LOG.proc_begin(d_module, 'p_api_version', p_api_version);
2153 PO_LOG.proc_begin(d_module, 'p_action_requested', p_action_requested);
2154 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2155 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
2156 PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
2157 PO_LOG.proc_begin(d_module, 'p_document_level_id', p_document_level_id);
2158 PO_LOG.proc_begin(d_module, 'p_org_id', p_org_id);
2159 PO_LOG.proc_begin(d_module, 'p_check_asl', p_check_asl);
2160 PO_LOG.proc_begin(d_module, 'p_from_header_id', p_from_header_id);
2161 PO_LOG.proc_begin(d_module, 'p_from_type_lookup_code', p_from_type_lookup_code);
2162 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
2163 PO_LOG.proc_begin(d_module, 'p_online_report_id', p_online_report_id);
2164 PO_LOG.proc_begin(d_module, 'p_sob_id', p_sob_id);
2165 END IF;
2166
2167 d_progress := 10;
2168
2169 -- bug 6713929 execute a query to fetch inventory_org_id set for this Operating Unit
2170 BEGIN
2171 SELECT FSP.inventory_organization_id
2172 INTO l_inv_org_id
2173 FROM po_system_parameters_all PSP,
2174 financials_system_params_all FSP,
2175 gl_sets_of_books GLS,
2176 fnd_id_flex_structures COAFS
2177 WHERE FSP.org_id= PSP.org_id
2178 AND FSP.set_of_books_id = GLS.set_of_books_id
2179 AND COAFS.id_flex_num = GLS.chart_of_accounts_id
2180 AND COAFS.application_id = 101 /** SQLGL **/
2181 AND COAFS.id_flex_code = 'GL#'
2182 AND fsp.org_id = p_org_id;
2183 END;
2184
2185
2186 --If there is a source document and the source document type is one of
2187 --'QUOTATION', 'STANDARD', 'PLANNED', 'BLANKET', 'CONTRACT';
2188 --Then we need to do the copydoc submission check first and generate online report 1.
2189 IF (p_from_header_id IS NOT NULL AND
2190 p_from_type_lookup_code in ('QUOTATION', 'STANDARD', 'PLANNED', 'BLANKET', 'CONTRACT')) THEN --<BUG 3520619>
2191 PO_COPYDOC_SUB.SUBMISSION_CHECK_COPYDOC(
2192 x_po_header_id => p_po_header_id
2193 ,x_online_report_id => p_online_report_id
2194 ,x_sob_id => p_sob_id
2195 ,x_inv_org_id => l_inv_org_id --bug 6713929
2196 );
2197 END IF;
2198
2199 IF (PO_LOG.d_stmt) THEN
2200 PO_LOG.stmt(d_module,d_progress,'After called SUBMISSION_CHECK_COPYDOC()');
2201 END IF;
2202
2203 d_progress := 20;
2204
2205 -- Do the normal PO submission check and generate online report 2
2206 po_submission_check(
2207 p_api_version => p_api_version
2208 , p_action_requested => p_action_requested
2209 , p_document_type => p_document_type
2210 , p_document_subtype => p_document_subtype
2211 , p_document_level => p_document_level
2212 , p_document_level_id => p_document_level_id
2213 , p_org_id => p_org_id
2214 , p_requested_changes => p_requested_changes
2215 , p_check_asl => p_check_asl
2216 , p_req_chg_initiator => p_req_chg_initiator
2217 , p_origin_doc_id => p_origin_doc_id
2218 , x_return_status => x_return_status
2219 , x_sub_check_status => x_sub_check_status
2220 , x_has_warnings => x_has_warnings
2221 , x_msg_data => x_msg_data
2222 , x_online_report_id => l_report_id2
2223 , x_doc_check_error_record => x_doc_check_error_record
2224 );
2225
2226 IF (PO_LOG.d_stmt) THEN
2227 PO_LOG.stmt(d_module,d_progress,'After called po_submission_check()');
2228 END IF;
2229
2230 d_progress := 30;
2231
2232 -- SQL What: Get the number of data rows generated for report 1
2233 -- SQL Why: Used for the check below.
2234 SELECT COUNT(*)
2235 INTO l_report_id1_rownum
2236 FROM PO_ONLINE_REPORT_TEXT
2237 WHERE online_report_id = p_online_report_id;
2238
2239 IF (PO_LOG.d_stmt) THEN
2240 PO_LOG.stmt(d_module,d_progress,'After counted the first report rows and get the count as: '|| TO_CHAR(l_report_id1_rownum));
2241 END IF;
2242
2243 d_progress := 40;
2244
2245 -- SQL What: Get the number of data rows generated for report 2
2246 -- SQL Why: Used for the check below.
2247 SELECT COUNT(*)
2248 INTO l_report_id2_rownum
2249 FROM PO_ONLINE_REPORT_TEXT
2250 WHERE online_report_id = l_report_id2;
2251
2252 IF (PO_LOG.d_stmt) THEN
2253 PO_LOG.stmt(d_module,d_progress,'After counted the second report rows and get the count as: '|| TO_CHAR(l_report_id2_rownum));
2254 END IF;
2255
2256 d_progress := 50;
2257
2258 -- If there is data rows generated for both report 1 (copy doc check report in this case) and report 2 (normal po submission check),
2259 -- Then merge all data of report 1 and report 2 and generate a new report with a new report id
2260 IF (NVL(l_report_id1_rownum, 0) > 0) THEN
2261
2262 --Change the x_return_status to success if there is any error/warnings created in the first report(copydoc submission check)
2263 x_return_status := FND_API.G_RET_STS_SUCCESS;
2264 --Also change the x_sub_check_status to FND_API.G_RET_STS_ERROR if there is any error/warnings created in the first report(copydoc submission check)
2265 x_sub_check_status := FND_API.G_RET_STS_ERROR;
2266
2267 IF ((NVL(l_report_id2_rownum, 0) > 0)) THEN
2268 merge_online_report_id
2269 (
2270 p_report_id1 => p_online_report_id
2271 , p_report_id2 => l_report_id2
2272 , x_new_report_id => x_online_report_id
2273 );
2274
2275 IF (PO_LOG.d_stmt) THEN
2276 PO_LOG.stmt(d_module,d_progress,'After called merge_online_report_id(), created the new report id');
2277 END IF;
2278 ELSE -- If only report 1 has msg rows, then return the id for report 1 directly.
2279 x_online_report_id := p_online_report_id;
2280
2281 IF (PO_LOG.d_stmt) THEN
2282 PO_LOG.stmt(d_module,d_progress,'Not called merge_online_report_id(), use the first report id as:' || TO_CHAR(p_online_report_id));
2283 END IF;
2284 END IF;
2285
2286 d_progress := 60;
2287
2288 -- If there is no data rows generated for report 1, return the id for report 2 directly.
2289 ELSE
2290 x_online_report_id := l_report_id2;
2291
2292 IF (PO_LOG.d_stmt) THEN
2293 PO_LOG.stmt(d_module,d_progress,'Not called merge_online_report_id(), use the second report id as:' || TO_CHAR(l_report_id2));
2294 END IF;
2295 END IF;
2296
2297 IF (PO_LOG.d_proc) THEN
2298 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2299 PO_LOG.proc_end(d_module, 'x_sub_check_status', x_sub_check_status);
2300 PO_LOG.proc_end(d_module, 'x_has_warnings', x_has_warnings);
2301 PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
2302 PO_LOG.proc_end(d_module, 'x_online_report_id', x_online_report_id);
2303
2304 END IF;
2305
2306 EXCEPTION
2307 WHEN OTHERS THEN
2308 IF (PO_LOG.d_exc) THEN
2309 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2310 PO_LOG.proc_end(d_module);
2311 END IF;
2312 END po_combined_submission_check;
2313
2314 -- Bug 5560980 END
2315
2316
2317 END PO_DOCUMENT_CHECKS_GRP;
[Home] [Help]