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