1 PACKAGE BODY PO_REQ_DOCUMENT_CHECKS_PVT AS
2 /* $Header: POXVRCKB.pls 120.1 2005/06/29 18:50:49 shsiung noship $*/
3
4 --CONSTANTS
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:
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.
27 -- A Line is updatable if it is 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;
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);
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
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 AND (h.closed_code is NULL or h.closed_code IN ('OPEN', 'CLOSED')))
250 AND (p_req_line_id(i) IS NULL
251 OR EXISTS (SELECT null from po_requisition_lines l
252 WHERE l.requisition_header_id = p_req_header_id(i)
253 and l.requisition_line_id = p_req_line_id(i)
254 AND (l.closed_code is NULL or l.closed_code IN ('OPEN', 'CLOSED'))))
255 ;
256
257 -- Allocate memory for reservable_flag Table to p_count size
258 l_progress := '020';
259 x_req_status_rec.reservable_flag := po_tbl_varchar1();
260 x_req_status_rec.reservable_flag.extend(p_count);
261
262 -- Set reservable_flag for each Entity using BULK_ROWCOUNT
263 l_progress := '030';
264 FOR i IN 1..p_count LOOP
265
266 IF SQL%BULK_ROWCOUNT(i) > 0 THEN
267 -- Reservable Header/Line found in the fake "update dual" stmt
268 x_req_status_rec.reservable_flag(i) := 'Y';
269 ELSE
270 x_req_status_rec.reservable_flag(i) := 'N';
271 END IF; --END of IF SQL%BULK_ROWCOUNT(i) > 0
272
273 END LOOP;
274
275 -- bug3606853 START
276 -- Remove everthing that has been inserted into PO_SESSION_GT by the above
277 -- dummy insert
278
279 DELETE FROM po_session_gt
280 WHERE key = l_procedure_id;
281
282 -- bug3592160 END
283
284 x_return_status := FND_API.G_RET_STS_SUCCESS;
285
286 EXCEPTION
287 WHEN FND_API.G_EXC_ERROR THEN
288 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
289 x_return_status := FND_API.G_RET_STS_ERROR;
290 WHEN OTHERS THEN
291 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
292 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
294
295 END check_reservable;
296
297 -------------------------------------------------------------------------------
298 --Start of Comments
299 --Name: check_unreservable
300 --Pre-reqs:
301 -- None.
302 --Modifies:
303 -- None.
304 --Locks:
305 -- None.
306 --Function:
307 -- Helper to req_status_check to Find if a Requisition Header/Line is unreservable based on status.
308 -- A Requisition Header/Line is unreservable if
309 -- Any Authorization Status, AND Closed Code is CLOSED or OPEN,
310 --Parameters:
311 --IN:
312 --p_count
313 -- Specifies the number of entities in table IN parameters like p_req_header_id
314 -- Other IN parameters are detailed in main procedure req_status_check
315 --OUT:
316 --x_return_status
317 -- Indicates API return status as 'S', 'E' or 'U'.
318 --x_req_status_rec
319 -- Table x_req_status_rec.updateable_flag will be 'Y' or 'N' for each input entity
320 --x_msg_count
321 -- The number of messages put into FND Message Stack by this API
322 --x_msg_data
323 -- First message put into FND Message Stack by this API
324 --Notes:
325 -- The implementation of unreservable_flag involves a fake "update dual" statement to
326 -- optimize performance.
327 --End of Comments
328 -------------------------------------------------------------------------------
329
330 PROCEDURE check_unreservable (
331 p_count IN NUMBER,
332 p_req_header_id IN PO_TBL_NUMBER,
333 p_req_line_id IN PO_TBL_NUMBER,
334 p_req_distribution_id IN PO_TBL_NUMBER,
335 p_lock_flag IN VARCHAR2 := 'N',
336 x_req_status_rec IN OUT NOCOPY PO_STATUS_REC_TYPE,
337 x_return_status OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY NUMBER,
339 x_msg_data OUT NOCOPY VARCHAR2
340 ) IS
341
342 l_api_name CONSTANT VARCHAR(30) := 'CHECK_UNRESERVABLE';
343 l_progress VARCHAR2(3) := '000';
344
345 l_procedure_id PO_SESSION_GT.key%TYPE; -- bug3606853
346
347 BEGIN
348
349 IF g_fnd_debug = 'Y' THEN
350 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
351 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
352 || l_progress, 'Entering Procedure '||l_api_name);
353 END IF;
354 END IF;
355
356 --To obtimize performance, Execute a fake "update dual" in BULK. The WHERE clause
357 -- of the fake update statement checks if the current entity is unreservable or not.
358 -- One dual row updated <==> where clause is true <==> current entity is unreservable.
359 -- Later, Examine BULK_ROWCOUNT in a loop to determine unreservable_flag
360 l_progress := '010';
361
362 -- bug3606853 START
363 -- The original approach was to do a fake UPDATE on DUAL table. However, this
364 -- is causing locking issue (and priviledge). Therefore, BULK INSERT is used
365 -- instead of BULK UPDATE
366
367 l_procedure_id := PO_CORE_S.get_session_gt_nextval;
368
369 FORALL i IN 1..p_count
370 -- SQL What: Checks if current PO Header/Line/Shipment is in unreservable status
371 INSERT INTO PO_SESSION_GT
372 ( key
373 )
374 SELECT l_procedure_id
375 FROM DUAL
376 WHERE
377 EXISTS (select null from po_requisition_headers h
378 WHERE h.requisition_header_id = p_req_header_id(i)
379 AND (h.closed_code is NULL or h.closed_code IN ('OPEN', 'CLOSED')))
380 AND (p_req_line_id(i) IS NULL
381 OR EXISTS (SELECT null from po_requisition_lines l
382 WHERE l.requisition_header_id = p_req_header_id(i)
383 AND l.requisition_line_id = p_req_line_id(i)
384 AND (l.closed_code is NULL or l.closed_code IN ('OPEN', 'CLOSED'))))
385 ;
386
387 -- Allocate memory for unreservable_flag Table to p_count size
388 l_progress := '020';
389 x_req_status_rec.unreservable_flag := po_tbl_varchar1();
390 x_req_status_rec.unreservable_flag.extend(p_count);
391
392 -- Set unreservable_flag for each Entity using BULK_ROWCOUNT
393 l_progress := '030';
394 FOR i IN 1..p_count LOOP
395
396 IF SQL%BULK_ROWCOUNT(i) > 0 THEN
397 -- unreservable Header/Line found in the fake "update dual" stmt
398 x_req_status_rec.unreservable_flag(i) := 'Y';
399 ELSE
400 x_req_status_rec.unreservable_flag(i) := 'N';
404
401 END IF; --END of IF SQL%BULK_ROWCOUNT(i) > 0
402
403 END LOOP;
405 -- bug3606853 START
406 -- Remove everthing that has been inserted into PO_SESSION_GT by the above
407 -- dummy insert
408
409 DELETE FROM po_session_gt
410 WHERE key = l_procedure_id;
411
412 -- bug3592160 END
413
414 x_return_status := FND_API.G_RET_STS_SUCCESS;
415
416 EXCEPTION
417 WHEN FND_API.G_EXC_ERROR THEN
418 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
419 x_return_status := FND_API.G_RET_STS_ERROR;
420 WHEN OTHERS THEN
421 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
422 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
424
425 END check_unreservable;
426
427 -------------------------------------------------------------------------------
428 --Start of Comments
429 --Name: get_status
430 --Pre-reqs:
431 -- None.
432 --Modifies:
433 -- None.
434 --Locks:
435 -- None.
436 --Function:
437 -- Helper to req_status_check to find status of a Purchase Order/Release/Line/Shipment
438 -- The following status fields of PO Header or Release are put into
439 -- the OUT parameter x_req_status_rec
440 -- AUTHORIZATION_STATUS, APPROVED_FLAG, CLOSED_CODE, CANCEL_FLAG, FROZEN_FLAG, HOLD_FLAG
441 -- When an optional Line specified, following Line level values are overwritten
442 -- CLOSED_CODE, CANCEL_FLAG, HOLD_FLAG
443 -- When an optional Shipment specified, following Shipment level values are overwritten
444 -- APPROVED_FLAG, CLOSED_CODE, CANCEL_FLAG
445 --Parameters:
446 --IN:
447 --p_count
448 -- Specifies the number of entities in table IN parameters like p_header_id, p_release_id
449 -- All the table IN parameters are assumed to be of the same size
450 -- Other IN parameters are detailed in main procedure req_status_check
451 --OUT:
452 --x_return_status
453 -- Indicates API return status as 'S', 'E' or 'U'.
454 --x_req_status_rec
455 -- The various status fields would have the PO/Rel Line/Shipment status values
456 --x_msg_count
457 -- The number of messages put into FND Message Stack by this API
458 --x_msg_data
459 -- First message put into FND Message Stack by this API
460 --End of Comments
461 -------------------------------------------------------------------------------
462
463 PROCEDURE get_status (
464 p_count IN NUMBER,
465 p_req_header_id IN PO_TBL_NUMBER,
466 p_req_line_id IN PO_TBL_NUMBER,
467 p_req_distribution_id IN PO_TBL_NUMBER,
468 x_req_status_rec IN OUT NOCOPY PO_STATUS_REC_TYPE,
469 x_return_status OUT NOCOPY VARCHAR2,
470 x_msg_count OUT NOCOPY NUMBER,
471 x_msg_data OUT NOCOPY VARCHAR2
472 ) IS
473
474 l_api_name CONSTANT VARCHAR(30) := 'GET_STATUS';
475 l_progress VARCHAR2(3) := '000';
476 l_sequence PO_TBL_NUMBER := PO_TBL_NUMBER();
477
478 BEGIN
479
480 IF g_fnd_debug = 'Y' THEN
481 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
482 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
483 || l_progress, 'Entering Procedure '||l_api_name);
484 END IF;
485 END IF;
486
487 --Use sequence(i) to simulate i inside FORALL as direct reference to i not allowed
488 --Initialize sequence array to contain 1,2,3, ..., p_count
489 l_progress := '010';
490 l_sequence.extend(p_count);
491 FOR i IN 1..p_count LOOP
492 l_sequence(i) := i;
493 END LOOP;
494
495 l_progress := '020';
496
497 delete from po_headers_gt;
498
499 -- For all the entities , get Requisition Header status fields into
500 -- global temprary table while storing sequence into po_headers_gt.PO_HEADER_ID column
501 l_progress := '030';
502 FORALL i IN 1..p_count
503 INSERT
504 INTO po_headers_gt
505 ( AGENT_ID, TYPE_LOOKUP_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
506 SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
507 authorization_status, closed_code,
508 cancel_flag, PO_HEADER_ID)
509 SELECT
510 -1, TYPE_LOOKUP_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
511 SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG,
512 NVL(authorization_status, 'INCOMPLETE'), nvl(closed_code, 'OPEN'),
513 NVL(cancel_flag, 'N'), l_sequence(i)
514 FROM po_requisition_headers h
515 WHERE h.requisition_header_id = p_req_header_id(i)
516 ;
517
518
519 --IF line ID present at an index, overwrite the status fields with Line Level status
520 l_progress := '040';
521 FORALL i IN 1..p_count
522 UPDATE po_headers_gt gt
523 SET (closed_code, cancel_flag)
524 =
525 (SELECT nvl(closed_code, 'OPEN'), NVL(cancel_flag, 'N')
526 FROM po_requisition_lines s
527 WHERE s.requisition_line_id = p_req_line_id(i))
528 WHERE p_req_line_id(i) is not null and gt.po_header_id = l_sequence(i)
529 ;
530
531 -- Fetch status fields from global temporary table into pl/sql table.
532 -- Order by sequence (stored in PO_HEADER_ID column) ensures
533 -- that input tables like p_header_id are in sync with
537 authorization_status, closed_code,
534 -- output status field tables like x_req_status_rec.authorization_status
535 l_progress := '060';
536 SELECT
538 cancel_flag
539 BULK COLLECT INTO
540 x_req_status_rec.authorization_status, x_req_status_rec.closed_code,
541 x_req_status_rec.cancel_flag
542 FROM po_headers_gt
543 ORDER BY PO_HEADER_ID;
544
545 x_return_status := FND_API.G_RET_STS_SUCCESS;
546
547 EXCEPTION
548 WHEN FND_API.G_EXC_ERROR THEN
549 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
550 x_return_status := FND_API.G_RET_STS_ERROR;
551 WHEN OTHERS THEN
552 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
553 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
555
556 END get_status;
557
558 -------------------------------------------------------------------------------
559 --Start of Comments
560 --Name: req_status_check
561 --Pre-reqs:
562 -- None.
563 --Modifies:
564 -- None.
565 --Locks:
566 -- None.
567 --Function:
568 -- Finds the status of a Requisition Header/Line. Refer to p_mode parameter
569 -- and PO_STATUS_REC_TYPE for various status information this procedure can find out.
570 -- A unique header has to be specified in p_req_header_id.
571 -- A line can optionally be specified to check status at that level also.
572 --Parameters:
573 --IN:
574 --p_api_version
575 -- Specifies API version.
576 --p_req_header_id
577 -- Specifies Requisition Header ID. This is a required field.
578 --p_req_line_id := NULL
579 -- Optionally Specifies Requisition Line ID to check status at line level
580 --p_req_distribution_id := NULL
581 -- This is not used currently, may be used in the future.
582 --p_mode
583 -- Indicates what status to check.
584 -- Can contain one or more of the following requests to check status
585 -- CHECK_UPDATEABLE to check if the current Req Header/Line is updatable
586 -- GET_STATUS to return various statuses of the current Req Header/Line
587 --OUT:
588 --x_return_status
589 -- Indicates API return status as 'S', 'E' or 'U'.
590 --x_req_status_rec
591 -- Contains the returned status elements
592 -- If p_mode contains CHECK_UPDATEABLE,
593 -- the updateable_flag would have 'Y' or 'N' for each entity in the Table
594 -- If p_mode contains GET_APPROVAL_STATUS,
595 -- various status fields for Header/Line like authorization_status, cancel_flag
596 --x_msg_count
597 -- The number of messages put into FND Message Stack by this API
598 --x_msg_data
599 -- First message put into FND Message Stack by this API
600 --Testing:
601 -- All the input table parameters should have the exact same length.
602 -- Call the API when 1. only Requisition Header Exist, and 2. Line also exists
603 --End of Comments
604 -------------------------------------------------------------------------------
608 p_req_header_id IN PO_TBL_NUMBER,
605
606 PROCEDURE req_status_check (
607 p_api_version IN NUMBER,
609 p_req_line_id IN PO_TBL_NUMBER,
610 p_req_distribution_id IN PO_TBL_NUMBER,
611 p_mode IN VARCHAR2,
612 p_lock_flag IN VARCHAR2 := 'N',
613 x_req_status_rec OUT NOCOPY PO_STATUS_REC_TYPE,
614 x_return_status OUT NOCOPY VARCHAR2,
615 x_msg_count OUT NOCOPY NUMBER,
616 x_msg_data OUT NOCOPY VARCHAR2
617 ) IS
618
619 l_api_name CONSTANT VARCHAR(30) := 'req_status_check';
620 l_api_version CONSTANT NUMBER := 1.0;
621 l_progress VARCHAR2(3) := '000';
622 l_count NUMBER;
623
624 BEGIN
625
626 IF g_fnd_debug = 'Y' THEN
627 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
628 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, c_log_head || '.'||l_api_name||'.'
629 || l_progress, 'Entering Procedure '||l_api_name);
630 END IF;
631 END IF;
632
633 -- Standard call to check for call compatibility
634 l_progress := '010';
635 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
637 END IF;
638
639 l_progress := '020'; -- Initialize Local/Output Variables
640 l_count := p_req_header_id.count;
641 x_req_status_rec := PO_STATUS_REC_TYPE(null, null, null, null, null, null, null, null, null);
642
643 l_progress := '030';
644
645 IF INSTR(p_mode, G_CHECK_UPDATEABLE) > 0 THEN
646
647 check_updatable (
648 p_count => l_count,
649 p_req_header_id => p_req_header_id,
650 p_req_line_id => p_req_line_id,
651 p_req_distribution_id => p_req_distribution_id,
652 p_lock_flag => p_lock_flag,
653 x_req_status_rec => x_req_status_rec,
654 x_return_status => x_return_status,
655 x_msg_count => x_msg_count,
656 x_msg_data => x_msg_data);
657
658 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
659 return;
660 END IF;
661
662 END IF; --END of IF INSTR(p_mode, G_CHECK_UPDATEABLE) > 0
663
664 l_progress := '033';
665
666 IF INSTR(p_mode, G_CHECK_RESERVABLE) > 0 THEN
667
668 check_reservable (
669 p_count => l_count,
670 p_req_header_id => p_req_header_id,
671 p_req_line_id => p_req_line_id,
672 p_req_distribution_id => p_req_distribution_id,
673 p_lock_flag => p_lock_flag,
674 x_req_status_rec => x_req_status_rec,
675 x_return_status => x_return_status,
676 x_msg_count => x_msg_count,
677 x_msg_data => x_msg_data);
678
679 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
680 return;
681 END IF;
682
683 END IF; --END of IF INSTR(p_mode, G_CHECK_RESERVABLE) > 0
684
685 l_progress := '036';
686
687 IF INSTR(p_mode, G_CHECK_UNRESERVABLE) > 0 THEN
688
689 check_unreservable (
690 p_count => l_count,
691 p_req_header_id => p_req_header_id,
692 p_req_line_id => p_req_line_id,
693 p_req_distribution_id => p_req_distribution_id,
694 p_lock_flag => p_lock_flag,
695 x_req_status_rec => x_req_status_rec,
696 x_return_status => x_return_status,
697 x_msg_count => x_msg_count,
698 x_msg_data => x_msg_data);
699
700 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
701 return;
702 END IF;
703
704 END IF; --END of IF INSTR(p_mode, G_CHECK_UNRESERVABLE) > 0
705
706 l_progress := '040';
707
708 IF INSTR(p_mode, G_GET_STATUS) > 0 THEN
709
710 get_status (
711 p_count => l_count,
712 p_req_header_id => p_req_header_id,
713 p_req_line_id => p_req_line_id,
714 p_req_distribution_id => p_req_distribution_id,
715 x_req_status_rec => x_req_status_rec,
716 x_return_status => x_return_status,
717 x_msg_count => x_msg_count,
718 x_msg_data => x_msg_data);
719
720 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
721 return;
722 END IF;
723
724 END IF; --}END of IF INSTR(p_mode, G_GET_STATUS) > 0
725
726 l_progress := '050';
727
728 IF x_return_status is null THEN -- no valid check status request specified
729 FND_MESSAGE.set_name('PO', 'PO_STATCHK_GENERAL_ERROR');
730 FND_MESSAGE.set_token('ERROR_TEXT', 'Invalid p_mode: ' || p_mode);
731 FND_MSG_PUB.Add;
732 RAISE FND_API.G_EXC_ERROR;
733 END IF;
734
735 EXCEPTION
736 WHEN FND_API.G_EXC_ERROR THEN
737 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
738 x_return_status := FND_API.G_RET_STS_ERROR;
739 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
740 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
742 WHEN OTHERS THEN
743 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name || '.' || l_progress);
744 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746
747 END req_status_check;
748
749
750 END PO_REQ_DOCUMENT_CHECKS_PVT;
[Home] [Help]