4 --CONSTANTS
1 PACKAGE BODY PO_REQ_DOCUMENT_CHECKS_PVT AS
2 /* $Header: POXVRCKB.pls 120.2 2010/02/28 13:55:16 ssreekum ship $*/
3
5
6 G_PKG_NAME CONSTANT varchar2(30) := 'PO_REQ_DOCUMENT_CHECKS_PVT';
7
8 c_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| G_PKG_NAME || '.';
9
10 -- Read the profile option that enables/disables the debug log
11 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
12
13
14 -------------------------------------------------------------------------------
15 --Start of Comments
16 --Name: check_updatable
17 --Pre-reqs:
18 -- None.
19 --Modifies:
20 -- None.
21 --Locks:
22 -- None.
23 --Function:
27 -- A Line is updatable if it is not canceled, not finally closed.
24 -- Helper to req_status_check to Find if a Requisition Header/Line is updatable based on status.
25 -- A Requisition Header is updatable if
26 -- it is not Pre Approved, not In Process, not canceled, not finally closed.
28 --Parameters:
29 --IN:
30 --p_count
31 -- Specifies the number of entities in table IN parameters like p_req_header_id
32 -- Other IN parameters are detailed in main procedure req_status_check
33 --OUT:
34 --x_return_status
35 -- Indicates API return status as 'S', 'E' or 'U'.
36 --x_req_status_rec
37 -- Table x_req_status_rec.updateable_flag will be 'Y' or 'N' for each input entity
38 --x_msg_count
39 -- The number of messages put into FND Message Stack by this API
40 --x_msg_data
41 -- First message put into FND Message Stack by this API
42 --Notes:
43 -- The implementation of updatable_flag involves a fake "update dual" statement to
44 -- optimize performance.
45 --End of Comments
46 -------------------------------------------------------------------------------
47
48 PROCEDURE check_updatable (
49 p_count IN NUMBER,
50 p_req_header_id IN PO_TBL_NUMBER,
51 p_req_line_id IN PO_TBL_NUMBER,
52 p_req_distribution_id IN PO_TBL_NUMBER,
53 p_lock_flag IN VARCHAR2 := 'N',
54 x_req_status_rec IN OUT NOCOPY PO_STATUS_REC_TYPE,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2
58 ) IS
59
60 l_api_name CONSTANT VARCHAR(30) := 'CHECK_UPDATABLE';
61 l_progress VARCHAR2(3) := '000';
62
63 l_procedure_id PO_SESSION_GT.key%TYPE; -- bug3606853
64
65 BEGIN
66
67 IF g_fnd_debug = 'Y' THEN
68 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
69 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
70 || l_progress, 'Entering Procedure '||l_api_name);
71 END IF;
72 END IF;
73
74 --To obtimize performance, Execute a fake "update dual" in BULK. The WHERE clause
75 -- of the fake update statement checks if the current entity is updatable or not.
76 -- One dual row updated <==> where clause is true <==> current entity is updatable.
77 -- Later, Examine BULK_ROWCOUNT in a loop to determine updatable_flag
78 l_progress := '010';
79
80 -- bug3606853 START
81 -- The original approach was to do a fake UPDATE on DUAL table. However, this
82 -- is causing locking issue (and priviledge). Therefore, BULK INSERT is used
83 -- instead of BULK UPDATE
84
85 l_procedure_id := PO_CORE_S.get_session_gt_nextval;
86
87 FORALL i IN 1..p_count
88 -- SQL What: Checks if current PO Header/Line/Shipment is in updateable status
89 INSERT INTO PO_SESSION_GT
90 ( key
91 )
92 SELECT l_procedure_id
93 FROM DUAL
94 WHERE
95 EXISTS (select null from po_requisition_headers h
96 WHERE h.requisition_header_id = p_req_header_id(i)
97 AND (h.authorization_status is NULL
98 OR h.authorization_status NOT IN ('PRE-APPROVED', 'IN PROCESS'))
99 AND (h.cancel_flag is null or h.cancel_flag <> 'Y')
100 AND (h.closed_code is NULL or h.closed_code NOT IN ('FINALLY CLOSED')))
101 AND (p_req_line_id(i) IS NULL
102 OR EXISTS (SELECT null from po_requisition_lines l
103 WHERE l.requisition_header_id = p_req_header_id(i)
104 and l.requisition_line_id = p_req_line_id(i)
105 AND (l.cancel_flag is null or l.cancel_flag <> 'Y')
106 AND (l.closed_code is NULL or l.closed_code NOT IN ('FINALLY CLOSED'))
107 AND nvl(l.modified_by_agent_flag, 'N') = 'N'))
108 ;
109
110 -- bug3606853 END
111
112 -- Allocate memory for updatable_flag Table to p_count size
113 l_progress := '020';
114 x_req_status_rec.updatable_flag := po_tbl_varchar1();
115 x_req_status_rec.updatable_flag.extend(p_count);
116
117 -- Set Updatable_flag for each Entity using BULK_ROWCOUNT
118 l_progress := '030';
119 FOR i IN 1..p_count LOOP
120
121 IF SQL%BULK_ROWCOUNT(i) > 0 THEN
122 -- Updateable Header/Line found in the fake "update dual" stmt
123 x_req_status_rec.updatable_flag(i) := 'Y';
124
125 -- This document is updatable, lock the document if p_lock_flag=Y
126 l_progress := '040';
127 IF p_lock_flag = 'Y' THEN
128 PO_REQ_DOCUMENT_LOCK_GRP.LOCK_requisition (
129 p_api_version => 1.0,
130 P_req_header_id => p_req_header_id(i),
131 x_return_status => x_return_status);
132
133 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
134 return;
135 END IF;
136 END IF; --END of IF p_lock_flag = 'Y'
137 ELSE
138 x_req_status_rec.updatable_flag(i) := 'N';
139 END IF; --END of IF SQL%BULK_ROWCOUNT(i) > 0
140
141 END LOOP;
142
143 -- bug3606853 START
144 -- Remove everthing that has been inserted into PO_SESSION_GT by the above
145 -- dummy insert
146
147 DELETE FROM po_session_gt
148 WHERE key = l_procedure_id;
149
150 -- bug3592160 END
151
152
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154
155 EXCEPTION
156 WHEN FND_API.G_EXC_ERROR THEN
157 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
158 x_return_status := FND_API.G_RET_STS_ERROR;
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
159 WHEN OTHERS THEN
160 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
161 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
163
164 END check_updatable;
165
166 -------------------------------------------------------------------------------
167 --Start of Comments
168 --Name: check_reservable
169 --Pre-reqs:
170 -- None.
171 --Modifies:
172 -- None.
173 --Locks:
174 -- None.
175 --Function:
176 -- Helper to req_status_check to Find if a Requisition Header/Line is reservable based on status.
177 -- A Requisition Header/Line is reservable if
178 -- Authorization Status not APPROVED, AND Closed Code is CLOSED or OPEN,
179 --Parameters:
180 --IN:
181 --p_count
182 -- Specifies the number of entities in table IN parameters like p_req_header_id
183 -- Other IN parameters are detailed in main procedure req_status_check
184 --OUT:
185 --x_return_status
186 -- Indicates API return status as 'S', 'E' or 'U'.
187 --x_req_status_rec
188 -- Table x_req_status_rec.updateable_flag will be 'Y' or 'N' for each input entity
189 --x_msg_count
190 -- The number of messages put into FND Message Stack by this API
191 --x_msg_data
192 -- First message put into FND Message Stack by this API
193 --Notes:
194 -- The implementation of reservable_flag involves a fake "update dual" statement to
195 -- optimize performance.
196 --End of Comments
197 -------------------------------------------------------------------------------
198
199 PROCEDURE check_reservable (
200 p_count IN NUMBER,
201 p_req_header_id IN PO_TBL_NUMBER,
202 p_req_line_id IN PO_TBL_NUMBER,
203 p_req_distribution_id IN PO_TBL_NUMBER,
204 p_lock_flag IN VARCHAR2 := 'N',
205 x_req_status_rec IN OUT NOCOPY PO_STATUS_REC_TYPE,
206 x_return_status OUT NOCOPY VARCHAR2,
207 x_msg_count OUT NOCOPY NUMBER,
208 x_msg_data OUT NOCOPY VARCHAR2
209 ) IS
210
211 l_api_name CONSTANT VARCHAR(30) := 'CHECK_RESERVABLE';
212 l_progress VARCHAR2(3) := '000';
213
214 l_procedure_id PO_SESSION_GT.key%TYPE; -- bug3606853
215 BEGIN
216
217 IF g_fnd_debug = 'Y' THEN
218 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
219 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
220 || l_progress, 'Entering Procedure '||l_api_name);
221 END IF;
222 END IF;
223
224 --To obtimize performance, Execute a fake "update dual" in BULK. The WHERE clause
225 -- of the fake update statement checks if the current entity is reservable or not.
226 -- One dual row updated <==> where clause is true <==> current entity is reservable.
227 -- Later, Examine BULK_ROWCOUNT in a loop to determine reservable_flag
228 l_progress := '010';
229
230 -- bug3606853 START
231 -- The original approach was to do a fake UPDATE on DUAL table. However, this
232 -- is causing locking issue (and priviledge). Therefore, BULK INSERT is used
233 -- instead of BULK UPDATE
234
235 l_procedure_id := PO_CORE_S.get_session_gt_nextval;
236
237 FORALL i IN 1..p_count
238 -- SQL What: Checks if current PO Header/Line/Shipment is in reservable status
239 INSERT INTO PO_SESSION_GT
240 ( key
241 )
242 SELECT l_procedure_id
243 FROM DUAL
244 WHERE
245 EXISTS (select null from po_requisition_headers h
246 WHERE h.requisition_header_id = p_req_header_id(i)
247 AND (h.authorization_status is NULL
248 OR h.authorization_status NOT IN ('APPROVED')
249 /* <<CLM Partial Funding Code Changes>> */
250 -- Skip this for PR Amendments
251 OR nvl(h.federal_flag,'N') = 'Y'
252 /* <<CLM Partial Funding Code Changes>> */
253 )
254 AND (h.closed_code is NULL or h.closed_code IN ('OPEN', 'CLOSED')))
255 AND (p_req_line_id(i) IS NULL
256 OR EXISTS (SELECT null from po_requisition_lines l
257 WHERE l.requisition_header_id = p_req_header_id(i)
258 and l.requisition_line_id = p_req_line_id(i)
259 AND (l.closed_code is NULL or l.closed_code IN ('OPEN', 'CLOSED'))))
260 ;
261
262 -- Allocate memory for reservable_flag Table to p_count size
263 l_progress := '020';
264 x_req_status_rec.reservable_flag := po_tbl_varchar1();
265 x_req_status_rec.reservable_flag.extend(p_count);
266
267 -- Set reservable_flag for each Entity using BULK_ROWCOUNT
268 l_progress := '030';
269 FOR i IN 1..p_count LOOP
270
271 IF SQL%BULK_ROWCOUNT(i) > 0 THEN
272 -- Reservable Header/Line found in the fake "update dual" stmt
273 x_req_status_rec.reservable_flag(i) := 'Y';
274 ELSE
275 x_req_status_rec.reservable_flag(i) := 'N';
276 END IF; --END of IF SQL%BULK_ROWCOUNT(i) > 0
277
278 END LOOP;
279
280 -- bug3606853 START
281 -- Remove everthing that has been inserted into PO_SESSION_GT by the above
282 -- dummy insert
283
284 DELETE FROM po_session_gt
285 WHERE key = l_procedure_id;
286
287 -- bug3592160 END
288
289 x_return_status := FND_API.G_RET_STS_SUCCESS;
290
291 EXCEPTION
292 WHEN FND_API.G_EXC_ERROR THEN
293 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
294 x_return_status := FND_API.G_RET_STS_ERROR;
295 WHEN OTHERS THEN
296 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
297 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
301
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299
300 END check_reservable;
302 -------------------------------------------------------------------------------
303 --Start of Comments
304 --Name: check_unreservable
305 --Pre-reqs:
306 -- None.
307 --Modifies:
308 -- None.
309 --Locks:
310 -- None.
311 --Function:
312 -- Helper to req_status_check to Find if a Requisition Header/Line is unreservable based on status.
313 -- A Requisition Header/Line is unreservable if
314 -- Any Authorization Status, AND Closed Code is CLOSED or OPEN,
315 --Parameters:
316 --IN:
317 --p_count
318 -- Specifies the number of entities in table IN parameters like p_req_header_id
319 -- Other IN parameters are detailed in main procedure req_status_check
320 --OUT:
321 --x_return_status
322 -- Indicates API return status as 'S', 'E' or 'U'.
323 --x_req_status_rec
324 -- Table x_req_status_rec.updateable_flag will be 'Y' or 'N' for each input entity
325 --x_msg_count
326 -- The number of messages put into FND Message Stack by this API
327 --x_msg_data
328 -- First message put into FND Message Stack by this API
329 --Notes:
330 -- The implementation of unreservable_flag involves a fake "update dual" statement to
331 -- optimize performance.
332 --End of Comments
333 -------------------------------------------------------------------------------
334
335 PROCEDURE check_unreservable (
336 p_count IN NUMBER,
337 p_req_header_id IN PO_TBL_NUMBER,
338 p_req_line_id IN PO_TBL_NUMBER,
339 p_req_distribution_id IN PO_TBL_NUMBER,
340 p_lock_flag IN VARCHAR2 := 'N',
341 x_req_status_rec IN OUT NOCOPY PO_STATUS_REC_TYPE,
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_msg_count OUT NOCOPY NUMBER,
344 x_msg_data OUT NOCOPY VARCHAR2
345 ) IS
346
347 l_api_name CONSTANT VARCHAR(30) := 'CHECK_UNRESERVABLE';
348 l_progress VARCHAR2(3) := '000';
349
350 l_procedure_id PO_SESSION_GT.key%TYPE; -- bug3606853
351
352 BEGIN
353
354 IF g_fnd_debug = 'Y' THEN
355 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
356 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
357 || l_progress, 'Entering Procedure '||l_api_name);
358 END IF;
359 END IF;
360
361 --To obtimize performance, Execute a fake "update dual" in BULK. The WHERE clause
362 -- of the fake update statement checks if the current entity is unreservable or not.
363 -- One dual row updated <==> where clause is true <==> current entity is unreservable.
364 -- Later, Examine BULK_ROWCOUNT in a loop to determine unreservable_flag
365 l_progress := '010';
366
367 -- bug3606853 START
368 -- The original approach was to do a fake UPDATE on DUAL table. However, this
369 -- is causing locking issue (and priviledge). Therefore, BULK INSERT is used
370 -- instead of BULK UPDATE
371
372 l_procedure_id := PO_CORE_S.get_session_gt_nextval;
373
374 FORALL i IN 1..p_count
375 -- SQL What: Checks if current PO Header/Line/Shipment is in unreservable status
376 INSERT INTO PO_SESSION_GT
377 ( key
378 )
379 SELECT l_procedure_id
380 FROM DUAL
381 WHERE
382 EXISTS (select null from po_requisition_headers h
383 WHERE h.requisition_header_id = p_req_header_id(i)
384 AND (h.closed_code is NULL or h.closed_code IN ('OPEN', 'CLOSED')))
385 AND (p_req_line_id(i) IS NULL
386 OR EXISTS (SELECT null from po_requisition_lines l
387 WHERE l.requisition_header_id = p_req_header_id(i)
388 AND l.requisition_line_id = p_req_line_id(i)
389 AND (l.closed_code is NULL or l.closed_code IN ('OPEN', 'CLOSED'))))
390 ;
391
392 -- Allocate memory for unreservable_flag Table to p_count size
393 l_progress := '020';
394 x_req_status_rec.unreservable_flag := po_tbl_varchar1();
395 x_req_status_rec.unreservable_flag.extend(p_count);
396
397 -- Set unreservable_flag for each Entity using BULK_ROWCOUNT
398 l_progress := '030';
399 FOR i IN 1..p_count LOOP
400
401 IF SQL%BULK_ROWCOUNT(i) > 0 THEN
402 -- unreservable Header/Line found in the fake "update dual" stmt
403 x_req_status_rec.unreservable_flag(i) := 'Y';
404 ELSE
405 x_req_status_rec.unreservable_flag(i) := 'N';
406 END IF; --END of IF SQL%BULK_ROWCOUNT(i) > 0
407
408 END LOOP;
409
410 -- bug3606853 START
411 -- Remove everthing that has been inserted into PO_SESSION_GT by the above
412 -- dummy insert
413
414 DELETE FROM po_session_gt
415 WHERE key = l_procedure_id;
416
417 -- bug3592160 END
418
419 x_return_status := FND_API.G_RET_STS_SUCCESS;
420
421 EXCEPTION
422 WHEN FND_API.G_EXC_ERROR THEN
423 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
424 x_return_status := FND_API.G_RET_STS_ERROR;
425 WHEN OTHERS THEN
426 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
427 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
429
430 END check_unreservable;
431
432 -------------------------------------------------------------------------------
433 --Start of Comments
434 --Name: get_status
435 --Pre-reqs:
436 -- None.
437 --Modifies:
438 -- None.
439 --Locks:
440 -- None.
441 --Function:
445 -- AUTHORIZATION_STATUS, APPROVED_FLAG, CLOSED_CODE, CANCEL_FLAG, FROZEN_FLAG, HOLD_FLAG
442 -- Helper to req_status_check to find status of a Purchase Order/Release/Line/Shipment
443 -- The following status fields of PO Header or Release are put into
444 -- the OUT parameter x_req_status_rec
446 -- When an optional Line specified, following Line level values are overwritten
447 -- CLOSED_CODE, CANCEL_FLAG, HOLD_FLAG
448 -- When an optional Shipment specified, following Shipment level values are overwritten
449 -- APPROVED_FLAG, CLOSED_CODE, CANCEL_FLAG
450 --Parameters:
451 --IN:
452 --p_count
453 -- Specifies the number of entities in table IN parameters like p_header_id, p_release_id
454 -- All the table IN parameters are assumed to be of the same size
455 -- Other IN parameters are detailed in main procedure req_status_check
456 --OUT:
457 --x_return_status
458 -- Indicates API return status as 'S', 'E' or 'U'.
459 --x_req_status_rec
460 -- The various status fields would have the PO/Rel Line/Shipment status values
461 --x_msg_count
462 -- The number of messages put into FND Message Stack by this API
463 --x_msg_data
464 -- First message put into FND Message Stack by this API
465 --End of Comments
466 -------------------------------------------------------------------------------
467
468 PROCEDURE get_status (
469 p_count IN NUMBER,
470 p_req_header_id IN PO_TBL_NUMBER,
471 p_req_line_id IN PO_TBL_NUMBER,
472 p_req_distribution_id IN PO_TBL_NUMBER,
473 x_req_status_rec IN OUT NOCOPY PO_STATUS_REC_TYPE,
474 x_return_status OUT NOCOPY VARCHAR2,
475 x_msg_count OUT NOCOPY NUMBER,
476 x_msg_data OUT NOCOPY VARCHAR2
477 ) IS
478
479 l_api_name CONSTANT VARCHAR(30) := 'GET_STATUS';
480 l_progress VARCHAR2(3) := '000';
481 l_sequence PO_TBL_NUMBER := PO_TBL_NUMBER();
482
483 BEGIN
484
485 IF g_fnd_debug = 'Y' THEN
486 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
487 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
488 || l_progress, 'Entering Procedure '||l_api_name);
489 END IF;
490 END IF;
491
492 --Use sequence(i) to simulate i inside FORALL as direct reference to i not allowed
493 --Initialize sequence array to contain 1,2,3, ..., p_count
494 l_progress := '010';
495 l_sequence.extend(p_count);
496 FOR i IN 1..p_count LOOP
497 l_sequence(i) := i;
498 END LOOP;
499
500 l_progress := '020';
501
502 delete from po_headers_gt;
503
504 -- For all the entities , get Requisition Header status fields into
505 -- global temprary table while storing sequence into po_headers_gt.PO_HEADER_ID column
506 l_progress := '030';
507 FORALL i IN 1..p_count
508 INSERT
509 INTO po_headers_gt
510 ( AGENT_ID, TYPE_LOOKUP_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
511 SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
512 authorization_status, closed_code,
513 cancel_flag, PO_HEADER_ID)
514 SELECT
515 -1, TYPE_LOOKUP_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
516 SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
517 NVL(authorization_status, 'INCOMPLETE'), nvl(closed_code, 'OPEN'),
518 NVL(cancel_flag, 'N'), l_sequence(i)
519 FROM po_requisition_headers h
520 WHERE h.requisition_header_id = p_req_header_id(i)
521 ;
522
523
524 --IF line ID present at an index, overwrite the status fields with Line Level status
525 l_progress := '040';
526 FORALL i IN 1..p_count
527 UPDATE po_headers_gt gt
528 SET (closed_code, cancel_flag)
529 =
530 (SELECT nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N')
531 FROM po_requisition_lines s
532 WHERE s.requisition_line_id = p_req_line_id(i))
533 WHERE p_req_line_id(i) is not null and gt.po_header_id = l_sequence(i)
534 ;
535
536 -- Fetch status fields from global temporary table into pl/sql table.
537 -- Order by sequence (stored in PO_HEADER_ID column) ensures
538 -- that input tables like p_header_id are in sync with
539 -- output status field tables like x_req_status_rec.authorization_status
540 l_progress := '060';
541 SELECT
542 authorization_status, closed_code,
543 cancel_flag
544 BULK COLLECT INTO
545 x_req_status_rec.authorization_status, x_req_status_rec.closed_code,
546 x_req_status_rec.cancel_flag
547 FROM po_headers_gt
548 ORDER BY PO_HEADER_ID;
549
550 x_return_status := FND_API.G_RET_STS_SUCCESS;
551
552 EXCEPTION
553 WHEN FND_API.G_EXC_ERROR THEN
554 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
555 x_return_status := FND_API.G_RET_STS_ERROR;
556 WHEN OTHERS THEN
557 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
558 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
560
561 END get_status;
562
563 -------------------------------------------------------------------------------
564 --Start of Comments
565 --Name: req_status_check
566 --Pre-reqs:
567 -- None.
568 --Modifies:
569 -- None.
570 --Locks:
571 -- None.
572 --Function:
573 -- Finds the status of a Requisition Header/Line. Refer to p_mode parameter
574 -- and PO_STATUS_REC_TYPE for various status information this procedure can find out.
575 -- A unique header has to be specified in p_req_header_id.
576 -- A line can optionally be specified to check status at that level also.
577 --Parameters:
581 --p_req_header_id
578 --IN:
579 --p_api_version
580 -- Specifies API version.
582 -- Specifies Requisition Header ID. This is a required field.
583 --p_req_line_id := NULL
584 -- Optionally Specifies Requisition Line ID to check status at line level
585 --p_req_distribution_id := NULL
586 -- This is not used currently, may be used in the future.
587 --p_mode
588 -- Indicates what status to check.
589 -- Can contain one or more of the following requests to check status
590 -- CHECK_UPDATEABLE to check if the current Req Header/Line is updatable
591 -- GET_STATUS to return various statuses of the current Req Header/Line
592 --OUT:
593 --x_return_status
594 -- Indicates API return status as 'S', 'E' or 'U'.
595 --x_req_status_rec
596 -- Contains the returned status elements
597 -- If p_mode contains CHECK_UPDATEABLE,
598 -- the updateable_flag would have 'Y' or 'N' for each entity in the Table
599 -- If p_mode contains GET_APPROVAL_STATUS,
600 -- various status fields for Header/Line like authorization_status, cancel_flag
601 --x_msg_count
602 -- The number of messages put into FND Message Stack by this API
603 --x_msg_data
604 -- First message put into FND Message Stack by this API
605 --Testing:
606 -- All the input table parameters should have the exact same length.
607 -- Call the API when 1. only Requisition Header Exist, and 2. Line also exists
608 --End of Comments
609 -------------------------------------------------------------------------------
610
611 PROCEDURE req_status_check (
612 p_api_version IN NUMBER,
613 p_req_header_id IN PO_TBL_NUMBER,
614 p_req_line_id IN PO_TBL_NUMBER,
615 p_req_distribution_id IN PO_TBL_NUMBER,
616 p_mode IN VARCHAR2,
617 p_lock_flag IN VARCHAR2 := 'N',
618 x_req_status_rec OUT NOCOPY PO_STATUS_REC_TYPE,
619 x_return_status OUT NOCOPY VARCHAR2,
620 x_msg_count OUT NOCOPY NUMBER,
621 x_msg_data OUT NOCOPY VARCHAR2
622 ) IS
623
624 l_api_name CONSTANT VARCHAR(30) := 'req_status_check';
625 l_api_version CONSTANT NUMBER := 1.0;
626 l_progress VARCHAR2(3) := '000';
627 l_count NUMBER;
628
629 BEGIN
630
631 IF g_fnd_debug = 'Y' THEN
632 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
633 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
634 || l_progress, 'Entering Procedure '||l_api_name);
635 END IF;
636 END IF;
637
638 -- Standard call to check for call compatibility
639 l_progress := '010';
640 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
642 END IF;
643
644 l_progress := '020'; -- Initialize Local/Output Variables
645 l_count := p_req_header_id.count;
646 x_req_status_rec := PO_STATUS_REC_TYPE(null, null, null, null, null, null, null, null, null);
647
648 l_progress := '030';
649
650 IF INSTR(p_mode, G_CHECK_UPDATEABLE) > 0 THEN
651
652 check_updatable (
653 p_count => l_count,
654 p_req_header_id => p_req_header_id,
655 p_req_line_id => p_req_line_id,
656 p_req_distribution_id => p_req_distribution_id,
657 p_lock_flag => p_lock_flag,
658 x_req_status_rec => x_req_status_rec,
659 x_return_status => x_return_status,
660 x_msg_count => x_msg_count,
661 x_msg_data => x_msg_data);
662
663 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
664 return;
665 END IF;
666
667 END IF; --END of IF INSTR(p_mode, G_CHECK_UPDATEABLE) > 0
668
669 l_progress := '033';
670
671 IF INSTR(p_mode, G_CHECK_RESERVABLE) > 0 THEN
672
673 check_reservable (
674 p_count => l_count,
675 p_req_header_id => p_req_header_id,
676 p_req_line_id => p_req_line_id,
677 p_req_distribution_id => p_req_distribution_id,
678 p_lock_flag => p_lock_flag,
679 x_req_status_rec => x_req_status_rec,
680 x_return_status => x_return_status,
681 x_msg_count => x_msg_count,
682 x_msg_data => x_msg_data);
683
684 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
685 return;
686 END IF;
687
688 END IF; --END of IF INSTR(p_mode, G_CHECK_RESERVABLE) > 0
689
690 l_progress := '036';
691
692 IF INSTR(p_mode, G_CHECK_UNRESERVABLE) > 0 THEN
693
694 check_unreservable (
695 p_count => l_count,
696 p_req_header_id => p_req_header_id,
697 p_req_line_id => p_req_line_id,
698 p_req_distribution_id => p_req_distribution_id,
699 p_lock_flag => p_lock_flag,
700 x_req_status_rec => x_req_status_rec,
701 x_return_status => x_return_status,
702 x_msg_count => x_msg_count,
703 x_msg_data => x_msg_data);
704
705 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
706 return;
707 END IF;
708
709 END IF; --END of IF INSTR(p_mode, G_CHECK_UNRESERVABLE) > 0
710
711 l_progress := '040';
712
713 IF INSTR(p_mode, G_GET_STATUS) > 0 THEN
714
715 get_status (
716 p_count => l_count,
717 p_req_header_id => p_req_header_id,
718 p_req_line_id => p_req_line_id,
719 p_req_distribution_id => p_req_distribution_id,
720 x_req_status_rec => x_req_status_rec,
721 x_return_status => x_return_status,
725 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
722 x_msg_count => x_msg_count,
723 x_msg_data => x_msg_data);
724
726 return;
727 END IF;
728
729 END IF; --}END of IF INSTR(p_mode, G_GET_STATUS) > 0
730
731 l_progress := '050';
732
733 IF x_return_status is null THEN -- no valid check status request specified
734 FND_MESSAGE.set_name('PO', 'PO_STATCHK_GENERAL_ERROR');
735 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid p_mode: ' || p_mode);
736 FND_MSG_PUB.Add;
737 RAISE FND_API.G_EXC_ERROR;
738 END IF;
739
740 EXCEPTION
741 WHEN FND_API.G_EXC_ERROR THEN
742 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
743 x_return_status := FND_API.G_RET_STS_ERROR;
744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
747 WHEN OTHERS THEN
748 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
749 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
751
752 END req_status_check;
753
754
755 END PO_REQ_DOCUMENT_CHECKS_PVT;
[Home] [Help]