[Home] [Help]
PACKAGE BODY: APPS.PO_DOCUMENT_ACTION_CHECK
Source
1 PACKAGE BODY PO_DOCUMENT_ACTION_CHECK AS
2 -- $Header: POXDACKB.pls 120.8.12010000.1 2008/07/24 14:12:09 appldev ship $
3
4 -- Private package constants
5
6 g_pkg_name CONSTANT varchar2(30) := 'PO_DOCUMENT_ACTION_CHECK';
7 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| g_pkg_name || '.';
8
9 g_chktype_DOC_TOTAL_LIMIT CONSTANT VARCHAR2(20) := 'DOC TOTAL LIMIT';
10 g_chktype_ACCOUNT_LIMIT CONSTANT VARCHAR2(20) := 'ACCOUNT LIMIT';
11 g_chktype_ITEM_LIMIT CONSTANT VARCHAR2(20) := 'ITEM LIMIT';
12 g_chktype_CATEGORY_LIMIT CONSTANT VARCHAR2(20) := 'CATEGORY LIMIT';
13 g_chktype_LOCATION_LIMIT CONSTANT VARCHAR2(20) := 'LOCATION LIMIT';
14
15 g_chktype_ACCOUNT_EXISTS CONSTANT VARCHAR2(20) := 'ACCOUNT EXISTS';
16
17 -- Private package types
18 TYPE AUTH_CHECK_TYPES_REC IS RECORD
19 (
20 check_accounts BOOLEAN,
21 check_items BOOLEAN,
22 check_item_categories BOOLEAN,
23 check_locations BOOLEAN,
24 check_doc_totals BOOLEAN
25 );
26
27 TYPE AUTH_CHECK_IDS_REC IS RECORD
28 (
29 object_id NUMBER,
30 position_id NUMBER,
31 job_id NUMBER,
32 ctl_function_id NUMBER,
33 fsp_org_id NUMBER,
34 coa_id NUMBER,
35 item_cat_struct_id NUMBER
36 );
37
38 -- Forward declare private methods
39
40 PROCEDURE authority_checks_setup(
41 p_action_to_verify IN VARCHAR2
42 , p_document_id IN NUMBER
43 , p_document_type IN VARCHAR2
44 , p_document_subtype IN VARCHAR2
45 , p_employee_id IN NUMBER
46 , x_auth_checks_to_do OUT NOCOPY AUTH_CHECK_TYPES_REC
47 , x_auth_check_ids OUT NOCOPY AUTH_CHECK_IDS_REC
48 , x_using_positions OUT NOCOPY BOOLEAN
49 , x_return_status OUT NOCOPY VARCHAR2
50 , x_functional_error OUT NOCOPY VARCHAR2
51 );
52
53
54 PROCEDURE check_doc_total_limit(
55 p_document_id IN NUMBER
56 , p_document_type IN VARCHAR2
57 , p_document_subtype IN VARCHAR2
58 , p_session_gt_key IN NUMBER
59 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
60 , x_return_status OUT NOCOPY VARCHAR2
61 , x_authorized_yn OUT NOCOPY VARCHAR2
62 );
63
64 PROCEDURE check_account_limit(
65 p_document_id IN NUMBER
66 , p_document_type IN VARCHAR2
67 , p_document_subtype IN VARCHAR2
68 , p_session_gt_key IN NUMBER
69 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
70 , x_return_status OUT NOCOPY VARCHAR2
71 , x_authorized_yn OUT NOCOPY VARCHAR2
72 );
73
74 PROCEDURE check_account_exists(
75 p_document_id IN NUMBER
76 , p_document_type IN VARCHAR2
77 , p_document_subtype IN VARCHAR2
78 , p_session_gt_key IN NUMBER
79 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
80 , x_return_status OUT NOCOPY VARCHAR2
81 , x_authorized_yn OUT NOCOPY VARCHAR2
82 );
83
84 PROCEDURE check_item_limit(
85 p_document_id IN NUMBER
86 , p_document_type IN VARCHAR2
87 , p_document_subtype IN VARCHAR2
88 , p_session_gt_key IN NUMBER
89 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
90 , x_return_status OUT NOCOPY VARCHAR2
91 , x_authorized_yn OUT NOCOPY VARCHAR2
92 );
93
94 PROCEDURE check_category_limit(
95 p_document_id IN NUMBER
96 , p_document_type IN VARCHAR2
97 , p_document_subtype IN VARCHAR2
98 , p_session_gt_key IN NUMBER
99 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
100 , x_return_status OUT NOCOPY VARCHAR2
101 , x_authorized_yn OUT NOCOPY VARCHAR2
102 );
103
104 PROCEDURE check_location_limit(
105 p_document_id IN NUMBER
106 , p_document_type IN VARCHAR2
107 , p_document_subtype IN VARCHAR2
108 , p_session_gt_key IN NUMBER
109 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
110 , x_return_status OUT NOCOPY VARCHAR2
111 , x_authorized_yn OUT NOCOPY VARCHAR2
112 );
113
114 PROCEDURE get_range_limit_sql(
115 p_document_id IN NUMBER
116 , p_document_type IN VARCHAR2
117 , p_document_subtype IN VARCHAR2
118 , p_check_type IN VARCHAR2
119 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
120 , x_return_status OUT NOCOPY VARCHAR2
121 , x_range_check_sql OUT NOCOPY VARCHAR2
122 );
123
124 PROCEDURE get_range_exists_sql(
125 p_document_id IN NUMBER
126 , p_document_type IN VARCHAR2
127 , p_document_subtype IN VARCHAR2
128 , p_check_type IN VARCHAR2
129 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
130 , x_return_status OUT NOCOPY VARCHAR2
131 , x_range_check_sql OUT NOCOPY VARCHAR2
132 );
133
134 PROCEDURE get_flex_where_sql(
135 p_auth_check_ids IN AUTH_CHECK_IDS_REC
136 , p_check_type IN VARCHAR2
137 , x_return_status OUT NOCOPY VARCHAR2
138 , x_flex_sql OUT NOCOPY VARCHAR2
139 );
140
141 PROCEDURE populate_session_gt(
142 p_document_id IN NUMBER
143 , p_document_type IN VARCHAR2
144 , p_document_subtype IN VARCHAR2
145 , x_session_gt_key OUT NOCOPY NUMBER
146 , x_return_status OUT NOCOPY VARCHAR2
147 );
148
149 PROCEDURE decode_result(
150 p_document_type IN VARCHAR2
151 , p_result_val IN NUMBER
152 , x_authorized_yn OUT NOCOPY VARCHAR2
153 );
154
155 -- Public methods
156
157
158 PROCEDURE approve_status_check(
159 p_action_ctl_rec IN OUT NOCOPY PO_DOCUMENT_ACTION_PVT.doc_action_call_rec_type
160 )
161 IS
162
163 d_progress NUMBER;
164 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.approve_status_check';
165
166 l_allowed_states PO_DOCUMENT_ACTION_UTIL.doc_state_rec_type;
167 l_doc_state_ok BOOLEAN;
168 l_ret_sts VARCHAR2(1);
169 d_msg VARCHAR2(200);
170
171 BEGIN
172
173 d_progress := 0;
174 IF (PO_LOG.d_proc) THEN
175 PO_LOG.proc_begin(d_module);
176 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_id', p_action_ctl_rec.document_id);
177 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_type', p_action_ctl_rec.document_type);
178 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_subtype', p_action_ctl_rec.document_subtype);
179 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.action', p_action_ctl_rec.action);
180 END IF;
181
182 BEGIN
183
184 d_progress := 10;
185
186 l_allowed_states.auth_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS;
187 l_allowed_states.auth_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED;
188 l_allowed_states.auth_states(3) := PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE;
189 l_allowed_states.auth_states(4) := PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED;
190 l_allowed_states.auth_states(5) := PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED;
191 l_allowed_states.auth_states(6) := PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL;
192 l_allowed_states.hold_flag := 'N';
193 l_allowed_states.frozen_flag := 'N';
194 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED;
195 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
196 l_allowed_states.fully_reserved_flag := NULL;
197
198 d_progress := 20;
199
200 l_doc_state_ok := PO_DOCUMENT_ACTION_UTIL.check_doc_state(
201 p_document_id => p_action_ctl_rec.document_id
202 , p_document_type => p_action_ctl_rec.document_type
203 , p_allowed_states => l_allowed_states
204 , x_return_status => l_ret_sts
205 );
206
207 IF (l_ret_sts <> 'S')
208 THEN
209
210 d_progress := 30;
211 d_msg := 'check_doc_state not successful';
212 RAISE PO_CORE_S.g_early_return_exc;
213
214 END IF;
215
216 d_progress := 40;
217
218 IF (l_doc_state_ok) THEN
219
220 d_progress := 50;
221 p_action_ctl_rec.return_code := NULL;
222
223 ELSE
224
225 d_progress := 60;
226 p_action_ctl_rec.return_code := 'STATE_FAILED';
227
228 END IF; -- if l_doc_state_ok
229
230 l_ret_sts := 'S';
231
232 EXCEPTION
233 WHEN PO_CORE_S.g_early_return_exc THEN
234 l_ret_sts := 'U';
235 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
236 IF (PO_LOG.d_exc) THEN
237 PO_LOG.exc(d_module, d_progress, d_msg);
238 END IF;
239 END;
240
241 p_action_ctl_rec.return_status := l_ret_sts;
242
243 IF (PO_LOG.d_proc) THEN
244 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
245 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_code', p_action_ctl_rec.return_code);
246 PO_LOG.proc_end(d_module);
247 END IF;
248
249 RETURN;
250
251 EXCEPTION
252 WHEN OTHERS THEN
253 p_action_ctl_rec.return_status := 'U';
254
255 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
256 IF (PO_LOG.d_exc) THEN
257 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
258 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
259 PO_LOG.proc_end(d_module);
260 END IF;
261
262 RETURN;
263
264 END approve_status_check;
265
266 PROCEDURE reject_status_check(
267 p_action_ctl_rec IN OUT NOCOPY PO_DOCUMENT_ACTION_PVT.doc_action_call_rec_type
268 )
269 IS
270
271 d_progress NUMBER;
272 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.reject_status_check';
273
274 l_allowed_states PO_DOCUMENT_ACTION_UTIL.doc_state_rec_type;
275 l_doc_state_ok BOOLEAN;
276 l_ret_sts VARCHAR2(1);
277 d_msg VARCHAR2(200);
278
279 BEGIN
280
281 d_progress := 0;
282 IF (PO_LOG.d_proc) THEN
283 PO_LOG.proc_begin(d_module);
284 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_id', p_action_ctl_rec.document_id);
285 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_type', p_action_ctl_rec.document_type);
286 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_subtype', p_action_ctl_rec.document_subtype);
287 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.action', p_action_ctl_rec.action);
288 END IF;
289
290 BEGIN
291
292 d_progress := 10;
293
294 l_allowed_states.auth_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS;
295 l_allowed_states.auth_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED;
296 l_allowed_states.hold_flag := 'N';
297 l_allowed_states.frozen_flag := 'N';
298 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED;
299 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
300 l_allowed_states.fully_reserved_flag := NULL;
301
302 d_progress := 20;
303
304 l_doc_state_ok := PO_DOCUMENT_ACTION_UTIL.check_doc_state(
305 p_document_id => p_action_ctl_rec.document_id
306 , p_document_type => p_action_ctl_rec.document_type
307 , p_allowed_states => l_allowed_states
308 , x_return_status => l_ret_sts
309 );
310
311 IF (l_ret_sts <> 'S')
312 THEN
313
314 d_progress := 30;
315 d_msg := 'check_doc_state not successful';
316 RAISE PO_CORE_S.g_early_return_exc;
317
318 END IF;
319
320 d_progress := 40;
321
322 IF (l_doc_state_ok) THEN
323
324 d_progress := 50;
325 p_action_ctl_rec.return_code := NULL;
326
327 ELSE
328
329 d_progress := 60;
330 p_action_ctl_rec.return_code := 'STATE_FAILED';
331
332 END IF; -- if l_doc_state_ok
333
334 l_ret_sts := 'S';
335
336 EXCEPTION
337 WHEN PO_CORE_S.g_early_return_exc THEN
338 l_ret_sts := 'U';
339 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
340 IF (PO_LOG.d_exc) THEN
341 PO_LOG.exc(d_module, d_progress, d_msg);
342 END IF;
343 END;
344
345 p_action_ctl_rec.return_status := l_ret_sts;
346
347 IF (PO_LOG.d_proc) THEN
348 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
349 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_code', p_action_ctl_rec.return_code);
350 PO_LOG.proc_end(d_module);
351 END IF;
352
353 RETURN;
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 p_action_ctl_rec.return_status := 'U';
358
359 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
360 IF (PO_LOG.d_exc) THEN
361 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
362 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
363 PO_LOG.proc_end(d_module);
364 END IF;
365
366 RETURN;
367 END reject_status_check;
368
369 PROCEDURE authority_check(
370 p_action_ctl_rec IN OUT NOCOPY PO_DOCUMENT_ACTION_PVT.doc_action_call_rec_type
371 )
372 IS
373
374 d_progress NUMBER;
375 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.authority_check';
376
377 l_authority_verified VARCHAR2(1);
378 l_action_to_verify VARCHAR2(30) := PO_DOCUMENT_ACTION_PVT.g_doc_action_APPROVE;
379 l_auth_checks_to_do AUTH_CHECK_TYPES_REC;
380 l_auth_check_ids AUTH_CHECK_IDS_REC;
381 l_using_positions BOOLEAN;
382
383 l_session_gt_key NUMBER;
384
385 l_ret_sts VARCHAR2(1);
386 d_msg VARCHAR2(200);
387
388 l_authorized_yn VARCHAR2(1);
389
390 BEGIN
391
392 d_progress := 0;
393 IF (PO_LOG.d_proc) THEN
394 PO_LOG.proc_begin(d_module);
395 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_id', p_action_ctl_rec.document_id);
396 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_type', p_action_ctl_rec.document_type);
397 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_subtype', p_action_ctl_rec.document_subtype);
398 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.action', p_action_ctl_rec.action);
399 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.employee_id', p_action_ctl_rec.employee_id);
400 END IF;
401
402 BEGIN
403
404 d_progress := 10;
405
406 authority_checks_setup(
407 p_action_to_verify => l_action_to_verify
408 , p_document_id => p_action_ctl_rec.document_id
409 , p_document_type => p_action_ctl_rec.document_type
410 , p_document_subtype => p_action_ctl_rec.document_subtype
411 , p_employee_id => p_action_ctl_rec.employee_id
412 , x_auth_checks_to_do => l_auth_checks_to_do
413 , x_auth_check_ids => l_auth_check_ids
414 , x_using_positions => l_using_positions
415 , x_return_status => l_ret_sts
416 , x_functional_error => p_action_ctl_rec.functional_error
417 );
418
419 IF (l_ret_sts = 'U')
420 THEN
421 d_progress := 20;
422 d_msg := 'unexpected error in authority_checks_setup';
423 RAISE PO_CORE_S.g_early_return_exc;
424 END IF;
425
426 IF (l_ret_sts = 'E')
427 THEN
428 d_progress := 30;
429 d_msg := 'functional error in authority_checks_setup';
430 RAISE PO_CORE_S.g_early_return_exc;
431 END IF;
432
433 d_progress := 40;
434
435 populate_session_gt(
436 p_document_id => p_action_ctl_rec.document_id
437 , p_document_type => p_action_ctl_rec.document_type
438 , p_document_subtype => p_action_ctl_rec.document_subtype
439 , x_session_gt_key => l_session_gt_key
440 , x_return_status => l_ret_sts
441 );
442
443 IF (l_ret_sts <> 'S')
444 THEN
445 d_progress := 50;
446 l_ret_sts := 'U';
447 d_msg := 'populate_session_gt not successful';
448 RAISE PO_CORE_S.g_early_return_exc;
449 END IF;
450
451 d_progress := 60;
452
453 IF (l_auth_checks_to_do.check_doc_totals) THEN
454
455 d_progress := 70;
456
457 check_doc_total_limit(
458 p_document_id => p_action_ctl_rec.document_id
459 , p_document_type => p_action_ctl_rec.document_type
460 , p_document_subtype => p_action_ctl_rec.document_subtype
461 , p_session_gt_key => l_session_gt_key
462 , p_auth_check_ids => l_auth_check_ids
463 , x_return_status => l_ret_sts
464 , x_authorized_yn => l_authorized_yn
465 );
466
467 IF (l_ret_sts <> 'S')
468 THEN
469 d_progress := 80;
470 d_msg := 'check_doc_total_limit not successful';
471 RAISE PO_CORE_S.g_early_return_exc;
472 END IF;
473
474 IF (l_authorized_yn <> 'Y')
475 THEN
476 d_progress := 90;
477 d_msg := 'functional error in check_doc_total_limit';
478 p_action_ctl_rec.functional_error := fnd_message.get_string('PO', 'PO_AUT_DOC_TOT_FAIL');
479 RAISE PO_CORE_S.g_early_return_exc;
480 END IF;
481
482 d_progress := 100;
483 IF (PO_LOG.d_stmt) THEN
484 PO_LOG.stmt(d_module, d_progress, 'Passed document total check.');
485 END IF;
486
487 END IF; -- IF check_doc_totals
488
489 IF (l_auth_checks_to_do.check_accounts) THEN
490
491 d_progress := 110;
492
493 check_account_exists(
494 p_document_id => p_action_ctl_rec.document_id
495 , p_document_type => p_action_ctl_rec.document_type
496 , p_document_subtype => p_action_ctl_rec.document_subtype
497 , p_session_gt_key => l_session_gt_key
498 , p_auth_check_ids => l_auth_check_ids
499 , x_return_status => l_ret_sts
500 , x_authorized_yn => l_authorized_yn
501 );
502
503 IF (l_ret_sts <> 'S')
504 THEN
505 d_progress := 120;
506 d_msg := 'check_account_exists not successful';
507 RAISE PO_CORE_S.g_early_return_exc;
508 END IF;
509
510 IF (l_authorized_yn <> 'Y')
511 THEN
512 d_progress := 130;
513 d_msg := 'functional error in check_account_exists';
514 p_action_ctl_rec.functional_error := fnd_message.get_string('PO', 'PO_AUT_ACCOUNT_NOT_EXISTS');
515 RAISE PO_CORE_S.g_early_return_exc;
516 END IF;
517
518 d_progress := 140;
519 IF (PO_LOG.d_stmt) THEN
520 PO_LOG.stmt(d_module, d_progress, 'Passed account exists check.');
521 END IF;
522
523 check_account_limit(
524 p_document_id => p_action_ctl_rec.document_id
525 , p_document_type => p_action_ctl_rec.document_type
526 , p_document_subtype => p_action_ctl_rec.document_subtype
527 , p_session_gt_key => l_session_gt_key
528 , p_auth_check_ids => l_auth_check_ids
529 , x_return_status => l_ret_sts
530 , x_authorized_yn => l_authorized_yn
531 );
532
533 IF (l_ret_sts <> 'S')
534 THEN
535 d_progress := 150;
536 d_msg := 'check_account_limit not successful';
537 RAISE PO_CORE_S.g_early_return_exc;
538 END IF;
539
540 IF (l_authorized_yn <> 'Y')
541 THEN
542 d_progress := 160;
543 d_msg := 'functional error in check_account_limit';
544 p_action_ctl_rec.functional_error := fnd_message.get_string('PO', 'PO_AUT_ACCOUNT_LIMIT_FAIL');
545 RAISE PO_CORE_S.g_early_return_exc;
546 END IF;
547
548 d_progress := 170;
549 IF (PO_LOG.d_stmt) THEN
550 PO_LOG.stmt(d_module, d_progress, 'Passed account limit check.');
551 END IF;
552
553 END IF; -- IF check_accounts
554
555 IF (l_auth_checks_to_do.check_locations) THEN
556
557 d_progress := 180;
558
559 check_location_limit(
560 p_document_id => p_action_ctl_rec.document_id
561 , p_document_type => p_action_ctl_rec.document_type
562 , p_document_subtype => p_action_ctl_rec.document_subtype
563 , p_session_gt_key => l_session_gt_key
564 , p_auth_check_ids => l_auth_check_ids
565 , x_return_status => l_ret_sts
566 , x_authorized_yn => l_authorized_yn
567 );
568
569 IF (l_ret_sts <> 'S')
570 THEN
571 d_progress := 190;
572 d_msg := 'check_location_limit not successful';
573 RAISE PO_CORE_S.g_early_return_exc;
574 END IF;
575
576 IF (l_authorized_yn <> 'Y')
577 THEN
578 d_progress := 200;
579 d_msg := 'functional error in check_location_limit';
580 p_action_ctl_rec.functional_error := fnd_message.get_string('PO', 'PO_AUT_LOC_LIMIT_FAIL');
581 RAISE PO_CORE_S.g_early_return_exc;
582 END IF;
583
584 d_progress := 210;
585 IF (PO_LOG.d_stmt) THEN
586 PO_LOG.stmt(d_module, d_progress, 'Passed location limit check.');
587 END IF;
588
589 END IF; -- IF check_locations
590
591 IF (l_auth_checks_to_do.check_items) THEN
592
593 d_progress := 220;
594
595 check_item_limit(
596 p_document_id => p_action_ctl_rec.document_id
597 , p_document_type => p_action_ctl_rec.document_type
598 , p_document_subtype => p_action_ctl_rec.document_subtype
599 , p_session_gt_key => l_session_gt_key
600 , p_auth_check_ids => l_auth_check_ids
601 , x_return_status => l_ret_sts
602 , x_authorized_yn => l_authorized_yn
603 );
604
605 IF (l_ret_sts <> 'S')
606 THEN
607 d_progress := 230;
608 d_msg := 'check_item_limit not successful';
609 RAISE PO_CORE_S.g_early_return_exc;
610 END IF;
611
612 IF (l_authorized_yn <> 'Y')
613 THEN
614 d_progress := 240;
615 d_msg := 'functional error in check_item_limit';
616 p_action_ctl_rec.functional_error := fnd_message.get_string('PO', 'PO_AUT_ITEM_LIMIT_FAIL');
617 RAISE PO_CORE_S.g_early_return_exc;
618 END IF;
619
620 d_progress := 250;
621 IF (PO_LOG.d_stmt) THEN
622 PO_LOG.stmt(d_module, d_progress, 'Passed item limit check.');
623 END IF;
624
625 END IF; -- IF check_items
626
627 IF (l_auth_checks_to_do.check_item_categories) THEN
628
629 d_progress := 260;
630
631 check_category_limit(
632 p_document_id => p_action_ctl_rec.document_id
633 , p_document_type => p_action_ctl_rec.document_type
634 , p_document_subtype => p_action_ctl_rec.document_subtype
635 , p_session_gt_key => l_session_gt_key
636 , p_auth_check_ids => l_auth_check_ids
637 , x_return_status => l_ret_sts
638 , x_authorized_yn => l_authorized_yn
639 );
640
641 IF (l_ret_sts <> 'S')
642 THEN
643 d_progress := 270;
644 d_msg := 'check_category_limit not successful';
645 RAISE PO_CORE_S.g_early_return_exc;
646 END IF;
647
648 IF (l_authorized_yn <> 'Y')
649 THEN
650 d_progress := 280;
651 d_msg := 'functional error in check_category_limit';
652 p_action_ctl_rec.functional_error := fnd_message.get_string('PO', 'PO_AUT_CATEGORY_LIMIT_FAIL');
653 RAISE PO_CORE_S.g_early_return_exc;
654 END IF;
655
656 d_progress := 290;
657 IF (PO_LOG.d_stmt) THEN
658 PO_LOG.stmt(d_module, d_progress, 'Passed category limit check.');
659 END IF;
660
661 END IF; -- IF check_locations
662
663 d_progress := 300;
664 l_authority_verified := 'Y';
665
666 EXCEPTION
667 WHEN PO_CORE_S.g_early_return_exc THEN
668 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
669 IF ((l_ret_sts = 'U') and (PO_LOG.d_exc)) THEN
670 PO_LOG.exc(d_module, d_progress, d_msg);
671 END IF;
672 IF (l_ret_sts = 'E' OR (NVL(l_authorized_yn, 'N') <> 'Y')) THEN
673 PO_LOG.stmt(d_module, d_progress, 'Error: ' || d_msg);
674 l_authority_verified := 'N';
675 END IF;
676 END;
677
678 IF (NVL(l_authority_verified,'X') = 'N')
679 THEN
680 p_action_ctl_rec.return_code := 'AUTHORIZATION_FAILED';
681 ELSE
682 p_action_ctl_rec.return_code := NULL;
683 END IF;
684
685 p_action_ctl_rec.return_status := l_ret_sts;
686 IF (p_action_ctl_rec.return_status = 'E') THEN
687 p_action_ctl_rec.return_status := 'S';
688 END IF;
689
690 IF (PO_LOG.d_proc) THEN
691 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
692 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_code', p_action_ctl_rec.return_code);
693 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.functional_error', p_action_ctl_rec.functional_error);
694 PO_LOG.proc_end(d_module);
695 END IF;
696
697 RETURN;
698
699 EXCEPTION
700 WHEN OTHERS THEN
701 p_action_ctl_rec.return_status := 'U';
702
703 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
704 IF (PO_LOG.d_exc) THEN
705 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
706 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
707 PO_LOG.proc_end(d_module);
708 END IF;
709
710 RETURN;
711
712 END authority_check;
713
714
715 PROCEDURE authority_checks_setup(
716 p_action_to_verify IN VARCHAR2
717 , p_document_id IN NUMBER
718 , p_document_type IN VARCHAR2
719 , p_document_subtype IN VARCHAR2
720 , p_employee_id IN NUMBER
721 , x_auth_checks_to_do OUT NOCOPY AUTH_CHECK_TYPES_REC
722 , x_auth_check_ids OUT NOCOPY AUTH_CHECK_IDS_REC
723 , x_using_positions OUT NOCOPY BOOLEAN
724 , x_return_status OUT NOCOPY VARCHAR2
725 , x_functional_error OUT NOCOPY VARCHAR2
726 )
727 IS
728
729 d_progress NUMBER;
730 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.authority_checks_setup';
731
732 l_ret_sts VARCHAR2(1);
733 d_msg VARCHAR2(200);
734
735 l_emp_flag BOOLEAN;
736 l_emp_id PER_EMPLOYEES_CURRENT_X.employee_id%TYPE;
737 l_emp_name PER_EMPLOYEES_CURRENT_X.full_name%TYPE;
738 l_emp_loc_id PER_EMPLOYEES_CURRENT_X.location_id%TYPE;
739 l_emp_loc_code HR_LOCATIONS.location_code%TYPE;
740 l_emp_is_buyer BOOLEAN;
741
742 l_using_pos_str FINANCIALS_SYSTEM_PARAMETERS.use_positions_flag%TYPE;
743
744 BEGIN
745
746 d_progress := 0;
747 IF (PO_LOG.d_proc) THEN
748 PO_LOG.proc_begin(d_module);
749 PO_LOG.proc_begin(d_module, 'p_action_to_verify', p_action_to_verify);
750 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
751 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
752 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
753 PO_LOG.proc_begin(d_module, 'p_employee_id', p_employee_id);
754 END IF;
755
756 d_progress := 10;
757
758 BEGIN
759
760 SELECT structure_id
761 INTO x_auth_check_ids.item_cat_struct_id
762 FROM mtl_default_sets_view mfsv
763 WHERE mfsv.functional_area_id = 2;
764
765 d_progress := 15;
766 IF (PO_LOG.d_stmt) THEN
767 PO_LOG.stmt(d_module, d_progress, 'x_auth_check_ids.item_cat_struct_id', x_auth_check_ids.item_cat_struct_id);
768 END IF;
769
770 IF (p_employee_id IS NULL)
771 THEN
772
773 d_progress := 20;
774
775 PO_DOCUMENT_ACTION_UTIL.get_employee_info(
776 p_user_id => FND_GLOBAL.USER_ID
777 , x_return_status => l_ret_sts
778 , x_employee_flag => l_emp_flag
779 , x_employee_id => l_emp_id
780 , x_employee_name => l_emp_name
781 , x_location_id => l_emp_loc_id
782 , x_location_code => l_emp_loc_code
783 , x_is_buyer_flag => l_emp_is_buyer
784 );
785
786 IF (l_ret_sts <> 'S')
787 THEN
788 d_progress := 20;
789 l_ret_sts := 'U';
790 d_msg := 'get_employee_id not successful';
791 RAISE PO_CORE_S.g_early_return_exc;
792 END IF;
793
794 IF (NOT l_emp_flag)
795 THEN
796 d_progress := 30;
797 l_ret_sts := 'E';
798 d_msg := 'No employee flag returned';
799 x_functional_error := fnd_message.get_string('PO', 'PO_ALL_NO_EMP_ID_FOR_USER_ID');
800 RAISE PO_CORE_S.g_early_return_exc;
801 END IF;
802
803 ELSE
804
805 d_progress := 40;
806
807 PO_EMPLOYEES_SV.get_employee_name(
808 x_emp_id => p_employee_id
809 , x_emp_name => l_emp_name
810 );
811
812 l_emp_id := p_employee_id;
813
814 END IF; -- p_employee_id IS NULL
815
816 IF (PO_LOG.d_stmt) THEN
817 PO_LOG.stmt(d_module, d_progress, 'l_emp_id', l_emp_id);
818 PO_LOG.stmt(d_module, d_progress, 'l_emp_name', l_emp_name);
819 END IF;
820
821 d_progress := 50;
822
823 SELECT glsob.chart_of_accounts_id
824 , NVL(fsp.use_positions_flag, 'N')
825 , fsp.inventory_organization_id
826 INTO x_auth_check_ids.coa_id
827 , l_using_pos_str
828 , x_auth_check_ids.fsp_org_id
829 FROM financials_system_parameters fsp,
830 gl_sets_of_books glsob
831 WHERE fsp.set_of_books_id = glsob.set_of_books_id;
832
833 d_progress := 60;
834
835 IF (l_using_pos_str = 'Y')
836 THEN
837 x_using_positions := TRUE;
838 ELSE
839 x_using_positions := FALSE;
840 END IF;
841
842 IF (PO_LOG.d_stmt) THEN
843 PO_LOG.stmt(d_module, d_progress, 'l_using_pos_str', l_using_pos_str);
844 PO_LOG.stmt(d_module, d_progress, 'x_using_positions', x_using_positions);
845 PO_LOG.stmt(d_module, d_progress, 'x_auth_check_ids.fsp_org_id', x_auth_check_ids.fsp_org_id);
846 PO_LOG.stmt(d_module, d_progress, 'x_auth_check_ids.coa_id', x_auth_check_ids.coa_id);
847 END IF;
848
849 IF (x_using_positions)
850 THEN
851
852 d_progress := 70;
853
854 SELECT nvl(paf.position_id, 0)
855 INTO x_auth_check_ids.position_id
856 FROM PER_ALL_ASSIGNMENTS_F paf -- <BUG 6615913>
857 WHERE paf.person_id = l_emp_id
858 AND paf.assignment_type IN ('E','C') --R12 CWK enhancement
859 AND paf.primary_flag = 'Y'
860 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date;
861
862 d_progress := 80;
863 IF (x_auth_check_ids.position_id = 0)
864 THEN
865 d_progress := 90;
866 l_ret_sts := 'E';
867 d_msg := 'position_id is 0';
868 x_functional_error := fnd_message.get_string('PO', 'PO_ALL_NO_POSITION_ID');
869 RAISE PO_CORE_S.g_early_return_exc;
870 END IF;
871
872 x_auth_check_ids.job_id := NULL;
873
874 ELSE
875
876 d_progress := 90;
877
878 SELECT nvl(paf.job_id, 0)
879 INTO x_auth_check_ids.job_id
880 FROM PER_ALL_ASSIGNMENTS_F paf -- <BUG 6615913>
881 WHERE paf.person_id = l_emp_id
882 AND paf.assignment_type IN ('E','C') --R12 CWK enhancement
883 AND paf.primary_flag = 'Y'
884 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date;
885
886 d_progress := 100;
887
888 IF (x_auth_check_ids.job_id = 0)
889 THEN
890 d_progress := 110;
891 l_ret_sts := 'E';
892 d_msg := 'job_id is 0';
893 x_functional_error := fnd_message.get_string('PO', 'PO_ALL_NO_JOB_ID');
894 RAISE PO_CORE_S.g_early_return_exc;
895 END IF;
896
897 x_auth_check_ids.position_id := NULL;
898
899 END IF; -- if x_using_positions
900
901 IF (PO_LOG.d_stmt) THEN
902 PO_LOG.stmt(d_module, d_progress, 'x_auth_check_ids.position_id', x_auth_check_ids.position_id);
903 PO_LOG.stmt(d_module, d_progress, 'x_auth_check_ids.job_id', x_auth_check_ids.job_id);
904 END IF;
905
906 d_progress := 120;
907
908 BEGIN
909
910 SELECT pocf.control_function_id
911 INTO x_auth_check_ids.ctl_function_id
912 FROM po_control_functions pocf
913 WHERE pocf.document_type_code = p_document_type
914 AND pocf.document_subtype = p_document_subtype
915 AND pocf.action_type_code = p_action_to_verify
916 AND pocf.enabled_flag = 'Y';
917
918 EXCEPTION
919 WHEN no_data_found THEN
920 d_progress := 125;
921 l_ret_sts := 'E';
922 d_msg := 'no control function id available';
923 x_functional_error := fnd_message.get_string('PO', 'PO_ALL_NO_CONTROL_FUCNTION_ID');
924 RAISE PO_CORE_S.g_early_return_exc;
925 END;
926
927 d_progress := 130;
928 IF (PO_LOG.d_stmt) THEN
929 PO_LOG.stmt(d_module, d_progress, 'x_auth_check_ids.ctl_function_id', x_auth_check_ids.ctl_function_id);
930 END IF;
931
932 x_auth_check_ids.object_id := p_document_id;
933
934 d_progress := 140;
935
936 IF (p_document_type IN ('REQUISITION', 'PO', 'RELEASE'))
937 THEN
938
939 -- all checks
940 d_progress := 150;
941 x_auth_checks_to_do.check_accounts := TRUE;
942 x_auth_checks_to_do.check_items := TRUE;
943 x_auth_checks_to_do.check_item_categories := TRUE;
944 x_auth_checks_to_do.check_locations := TRUE;
945 x_auth_checks_to_do.check_doc_totals := TRUE;
946
947 ELSIF ((p_document_type = 'PA') and (p_document_subtype = 'BLANKET'))
948 THEN
949
950 -- item only checks, plus doc total check
951 d_progress := 160;
952 x_auth_checks_to_do.check_accounts := FALSE;
953 x_auth_checks_to_do.check_items := TRUE;
954 x_auth_checks_to_do.check_item_categories := TRUE;
955 x_auth_checks_to_do.check_locations := FALSE;
956 x_auth_checks_to_do.check_doc_totals := TRUE;
957
958 ELSIF ((p_document_type = 'PA') and (p_document_subtype = 'CONTRACT'))
959 THEN
960
961 -- no checks other than doc total check
962 d_progress := 170;
963 x_auth_checks_to_do.check_accounts := FALSE;
964 x_auth_checks_to_do.check_items := FALSE;
965 x_auth_checks_to_do.check_item_categories := FALSE;
966 x_auth_checks_to_do.check_locations := FALSE;
967 x_auth_checks_to_do.check_doc_totals := TRUE;
968
969 ELSE
970
971 d_progress := 180;
972 l_ret_sts := 'U';
973 d_msg := 'bad document type or subtype';
974 RAISE PO_CORE_S.g_early_return_exc;
975
976 END IF; -- p_document_type IN ...
977
978 IF (PO_LOG.d_stmt) THEN
979 PO_LOG.stmt(d_module, d_progress, 'x_auth_checks_to_do.check_accounts', x_auth_checks_to_do.check_accounts);
980 PO_LOG.stmt(d_module, d_progress, 'x_auth_checks_to_do.check_items', x_auth_checks_to_do.check_items);
981 PO_LOG.stmt(d_module, d_progress, 'x_auth_checks_to_do.check_item_categories', x_auth_checks_to_do.check_item_categories);
982 PO_LOG.stmt(d_module, d_progress, 'x_auth_checks_to_do.check_locations', x_auth_checks_to_do.check_locations);
983 PO_LOG.stmt(d_module, d_progress, 'x_auth_checks_to_do.check_doc_totals', x_auth_checks_to_do.check_doc_totals);
984 END IF;
985
986 l_ret_sts := 'S';
987
988 EXCEPTION
989 WHEN PO_CORE_S.g_early_return_exc THEN
990 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
991 IF ((l_ret_sts = 'U') and (PO_LOG.d_exc)) THEN
992 PO_LOG.exc(d_module, d_progress, d_msg);
993 END IF;
994 IF (l_ret_sts = 'E') THEN
995 PO_LOG.stmt(d_module, d_progress, 'Error: ' || d_msg);
996 END IF;
997 END;
998
999 x_return_status := l_ret_sts;
1000
1001 IF (PO_LOG.d_proc) THEN
1002 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1003 PO_LOG.proc_end(d_module, 'x_auth_check_ids.object_id', x_auth_check_ids.object_id);
1004 PO_LOG.proc_end(d_module);
1005 END IF;
1006
1007 RETURN;
1008
1009 EXCEPTION
1010 WHEN others THEN
1011 x_return_status := 'U';
1012 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1013 IF (PO_LOG.d_exc) THEN
1014 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1015 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1016 PO_LOG.proc_end(d_module);
1017 END IF;
1018
1019 RETURN;
1020
1021 END authority_checks_setup;
1022
1023 PROCEDURE check_doc_total_limit(
1024 p_document_id IN NUMBER
1025 , p_document_type IN VARCHAR2
1026 , p_document_subtype IN VARCHAR2
1027 , p_session_gt_key IN NUMBER
1028 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
1029 , x_return_status OUT NOCOPY VARCHAR2
1030 , x_authorized_yn OUT NOCOPY VARCHAR2
1031 )
1032 IS
1033
1034 d_progress NUMBER;
1035 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.check_doc_total_limit';
1036
1037 l_result NUMBER;
1038 l_amt_limit_nvl NUMBER;
1039
1040 BEGIN
1041
1042 d_progress := 0;
1043 IF (PO_LOG.d_proc) THEN
1044 PO_LOG.proc_begin(d_module);
1045 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1046 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1047 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1048 PO_LOG.proc_begin(d_module, 'p_session_gt_key', p_session_gt_key);
1049 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.object_id', p_auth_check_ids.object_id);
1050 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.position_id', p_auth_check_ids.position_id);
1051 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.job_id', p_auth_check_ids.job_id);
1052 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.ctl_function_id', p_auth_check_ids.ctl_function_id);
1053 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.fsp_org_id', p_auth_check_ids.fsp_org_id);
1054 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.coa_id', p_auth_check_ids.coa_id);
1055 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.item_cat_struct_id', p_auth_check_ids.item_cat_struct_id);
1056 END IF;
1057
1058 IF (p_document_type = 'PA')
1059 THEN
1060 l_amt_limit_nvl := 0;
1061 ELSE
1062 l_amt_limit_nvl := -1;
1063 END IF;
1064
1065 d_progress := 10;
1066
1067 IF (PO_LOG.d_stmt) THEN
1068 PO_LOG.stmt(d_module, d_progress, 'l_amt_limit_nvl', l_amt_limit_nvl);
1069 END IF;
1070
1071
1072
1073 SELECT sign(min(nvl(POCR.amount_limit, l_amt_limit_nvl) - sum(pgt.num1)))
1074 INTO l_result
1075 FROM po_control_rules pocr
1076 , po_control_groups pocg
1077 , po_position_controls popc
1078 , po_session_gt pgt
1079 WHERE pgt.key = p_session_gt_key
1080 AND pgt.num1 IS NOT NULL
1081 AND pgt.num2 IS NULL -- Bug 4610058
1082 -- <Bug 4605781 Start>
1083 AND ((p_auth_check_ids.position_id IS NULL) OR
1084 (popc.position_id = p_auth_check_ids.position_id))
1085 AND ((p_auth_check_ids.job_id IS NULL) OR
1086 (popc.job_id = p_auth_check_ids.job_id))
1087 -- <Bug 4605781 End>
1088 AND sysdate BETWEEN NVL(popc.start_date, sysdate - 1) AND NVL(popc.end_date, sysdate + 1)
1089 AND popc.control_function_id = p_auth_check_ids.ctl_function_id
1090 AND pocg.enabled_flag = 'Y'
1091 AND pocg.control_group_id = popc.control_group_id
1092 AND pocr.control_group_id = pocg.control_group_id
1093 AND pocr.object_code = 'DOCUMENT_TOTAL'
1094 AND NVL(pocr.inactive_date, sysdate + 1) > sysdate
1095 GROUP BY pocr.control_rule_id, pocr.amount_limit;
1096
1097 d_progress := 20;
1098
1099 -- bug4772633
1100 -- For PA, if document total control rule is not defined, the above sql
1101 -- returns NULL. In that case, it means that approval is not allowed
1102 IF (p_document_type = 'PA') THEN
1103 l_result := NVL(l_result, -2);
1104 END IF;
1105
1106 IF (PO_LOG.d_stmt) THEN
1107 PO_LOG.stmt(d_module, d_progress, 'l_result', l_result);
1108 END IF;
1109
1110 decode_result(
1111 p_document_type => p_document_type
1112 , p_result_val => l_result
1113 , x_authorized_yn => x_authorized_yn);
1114
1115 d_progress := 30;
1116 x_return_status := 'S';
1117
1118 IF (PO_LOG.d_proc) THEN
1119 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1120 PO_LOG.proc_end(d_module, 'x_authorized_yn', x_authorized_yn);
1121 PO_LOG.proc_end(d_module);
1122 END IF;
1123
1124 RETURN;
1125
1126 EXCEPTION
1127 WHEN others THEN
1128 x_return_status := 'U';
1129 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1130 IF (PO_LOG.d_exc) THEN
1131 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1132 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1133 PO_LOG.proc_end(d_module);
1134 END IF;
1135
1136 RETURN;
1137
1138 END check_doc_total_limit;
1139
1140
1141 PROCEDURE check_location_limit(
1142 p_document_id IN NUMBER
1143 , p_document_type IN VARCHAR2
1144 , p_document_subtype IN VARCHAR2
1145 , p_session_gt_key IN NUMBER
1146 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
1147 , x_return_status OUT NOCOPY VARCHAR2
1148 , x_authorized_yn OUT NOCOPY VARCHAR2
1149 )
1150 IS
1151
1152 d_progress NUMBER;
1153 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.check_location_limit';
1154
1155 l_result NUMBER;
1156
1157 BEGIN
1158
1159 d_progress := 0;
1160 IF (PO_LOG.d_proc) THEN
1161 PO_LOG.proc_begin(d_module);
1162 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1163 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1164 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1165 PO_LOG.proc_begin(d_module, 'p_session_gt_key', p_session_gt_key);
1166 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.object_id', p_auth_check_ids.object_id);
1167 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.position_id', p_auth_check_ids.position_id);
1168 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.job_id', p_auth_check_ids.job_id);
1169 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.ctl_function_id', p_auth_check_ids.ctl_function_id);
1170 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.fsp_org_id', p_auth_check_ids.fsp_org_id);
1171 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.coa_id', p_auth_check_ids.coa_id);
1172 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.item_cat_struct_id', p_auth_check_ids.item_cat_struct_id);
1173 END IF;
1174
1175 SELECT sign(min(nvl(POCR.amount_limit, -1) - sum(pgt.num1)))
1176 INTO l_result
1177 FROM po_control_rules pocr
1178 , po_control_groups pocg
1179 , po_position_controls popc
1180 , po_session_gt pgt
1181 WHERE pgt.key = p_session_gt_key
1182 AND pgt.num1 IS NOT NULL
1183 AND pgt.num2 IS NULL -- Bug 4610058
1184 AND pgt.char1 = 'N'
1185 AND pgt.char2 <> 'FINALLY CLOSED'
1186 -- <Bug 4605781 Start>
1187 AND ((p_auth_check_ids.position_id IS NULL) OR
1188 (popc.position_id = p_auth_check_ids.position_id))
1189 AND ((p_auth_check_ids.job_id IS NULL) OR
1190 (popc.job_id = p_auth_check_ids.job_id))
1191 -- <Bug 4605781 End>
1192 AND sysdate BETWEEN NVL(popc.start_date, sysdate - 1) AND NVL(popc.end_date, sysdate + 1)
1193 AND popc.control_function_id = p_auth_check_ids.ctl_function_id
1194 AND pocg.enabled_flag = 'Y'
1195 AND pocg.control_group_id = popc.control_group_id
1196 AND pocr.control_group_id = pocg.control_group_id
1197 AND pocr.object_code = 'LOCATION' --Bug#4901549
1198 AND NVL(pocr.inactive_date, sysdate + 1) > sysdate
1199 AND pocr.location_id = pgt.num4
1200 GROUP BY pocr.control_rule_id, pocr.amount_limit;
1201
1202 d_progress := 10;
1203
1204 IF (PO_LOG.d_stmt) THEN
1205 PO_LOG.stmt(d_module, d_progress, 'l_result', l_result);
1206 END IF;
1207
1208 decode_result(
1209 p_document_type => p_document_type
1210 , p_result_val => l_result
1211 , x_authorized_yn => x_authorized_yn);
1212
1213 d_progress := 20;
1214
1215 x_return_status := 'S';
1216
1217 IF (PO_LOG.d_proc) THEN
1218 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1219 PO_LOG.proc_end(d_module, 'x_authorized_yn', x_authorized_yn);
1220 PO_LOG.proc_end(d_module);
1221 END IF;
1222
1223 RETURN;
1224
1225 EXCEPTION
1226 WHEN others THEN
1227 x_return_status := 'U';
1228 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1229 IF (PO_LOG.d_exc) THEN
1230 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1231 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1232 PO_LOG.proc_end(d_module);
1233 END IF;
1234
1235 RETURN;
1236
1237 END check_location_limit;
1238
1239
1240 PROCEDURE check_account_limit(
1241 p_document_id IN NUMBER
1242 , p_document_type IN VARCHAR2
1243 , p_document_subtype IN VARCHAR2
1244 , p_session_gt_key IN NUMBER
1245 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
1246 , x_return_status OUT NOCOPY VARCHAR2
1247 , x_authorized_yn OUT NOCOPY VARCHAR2
1248 )
1249 IS
1250
1251 d_progress NUMBER;
1252 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.check_account_limit';
1253
1254 l_sql VARCHAR2(8000);
1255 l_result NUMBER;
1256 l_ret_sts VARCHAR2(1);
1257 d_msg VARCHAR2(200);
1258
1259 BEGIN
1260
1261 d_progress := 0;
1262 IF (PO_LOG.d_proc) THEN
1263 PO_LOG.proc_begin(d_module);
1264 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1265 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1266 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1267 PO_LOG.proc_begin(d_module, 'p_session_gt_key', p_session_gt_key);
1268 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.object_id', p_auth_check_ids.object_id);
1269 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.position_id', p_auth_check_ids.position_id);
1270 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.job_id', p_auth_check_ids.job_id);
1271 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.ctl_function_id', p_auth_check_ids.ctl_function_id);
1272 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.fsp_org_id', p_auth_check_ids.fsp_org_id);
1273 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.coa_id', p_auth_check_ids.coa_id);
1274 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.item_cat_struct_id', p_auth_check_ids.item_cat_struct_id);
1275 END IF;
1276
1277 BEGIN
1278
1279 d_progress := 10;
1280
1281 get_range_limit_sql(
1282 p_document_id => p_document_id
1283 , p_document_type => p_document_type
1284 , p_document_subtype => p_document_subtype
1285 , p_check_type => g_chktype_ACCOUNT_LIMIT
1286 , p_auth_check_ids => p_auth_check_ids
1287 , x_return_status => l_ret_sts
1288 , x_range_check_sql => l_sql
1289 );
1290
1291 d_progress := 20;
1292
1293 IF (l_ret_sts <> 'S')
1294 THEN
1295 d_msg := 'get_range_limit_sql not successful';
1296 l_ret_sts := 'U';
1297 RAISE PO_CORE_S.g_early_return_exc;
1298 END IF;
1299
1300 IF (PO_LOG.d_stmt) THEN
1301 PO_LOG.stmt(d_module, d_progress, 'l_sql', l_sql);
1302 END IF;
1303
1304 d_progress := 30;
1305
1306 EXECUTE IMMEDIATE l_sql
1307 INTO l_result
1308 USING p_session_gt_key, p_auth_check_ids.position_id
1309 , p_auth_check_ids.position_id, p_auth_check_ids.job_id
1310 , p_auth_check_ids.job_id, p_auth_check_ids.ctl_function_id
1311 , 'ACCOUNT_RANGE';
1312
1313 IF (PO_LOG.d_stmt) THEN
1314 PO_LOG.stmt(d_module, d_progress, 'l_result', l_result);
1315 END IF;
1316
1317 decode_result(
1318 p_document_type => p_document_type
1319 , p_result_val => l_result
1320 , x_authorized_yn => x_authorized_yn);
1321
1322 l_ret_sts := 'S';
1323
1324 EXCEPTION
1325 WHEN PO_CORE_S.g_early_return_exc THEN
1326 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
1327 IF (PO_LOG.d_exc) THEN
1328 PO_LOG.exc(d_module, d_progress, d_msg);
1329 END IF;
1330 END;
1331
1332 x_return_status := l_ret_sts;
1333
1334 IF (PO_LOG.d_proc) THEN
1335 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1336 PO_LOG.proc_end(d_module, 'x_authorized_yn', x_authorized_yn);
1337 PO_LOG.proc_end(d_module);
1338 END IF;
1339
1340 RETURN;
1341
1342 EXCEPTION
1343 WHEN others THEN
1344 x_return_status := 'U';
1345 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1346 IF (PO_LOG.d_exc) THEN
1347 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1348 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1349 PO_LOG.proc_end(d_module);
1350 END IF;
1351
1352 RETURN;
1353 END check_account_limit;
1354
1355 PROCEDURE check_account_exists(
1356 p_document_id IN NUMBER
1357 , p_document_type IN VARCHAR2
1358 , p_document_subtype IN VARCHAR2
1359 , p_session_gt_key IN NUMBER
1360 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
1361 , x_return_status OUT NOCOPY VARCHAR2
1362 , x_authorized_yn OUT NOCOPY VARCHAR2
1363 )
1364 IS
1365
1366 d_progress NUMBER;
1367 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.check_account_exists';
1368
1369 l_sql VARCHAR2(8000);
1370 l_result NUMBER;
1371 l_ret_sts VARCHAR2(1);
1372 d_msg VARCHAR2(200);
1373
1374 BEGIN
1375
1376 d_progress := 0;
1377 IF (PO_LOG.d_proc) THEN
1378 PO_LOG.proc_begin(d_module);
1379 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1380 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1381 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1382 PO_LOG.proc_begin(d_module, 'p_session_gt_key', p_session_gt_key);
1383 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.object_id', p_auth_check_ids.object_id);
1384 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.position_id', p_auth_check_ids.position_id);
1385 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.job_id', p_auth_check_ids.job_id);
1386 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.ctl_function_id', p_auth_check_ids.ctl_function_id);
1387 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.fsp_org_id', p_auth_check_ids.fsp_org_id);
1388 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.coa_id', p_auth_check_ids.coa_id);
1389 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.item_cat_struct_id', p_auth_check_ids.item_cat_struct_id);
1390 END IF;
1391
1392 BEGIN
1393
1394 d_progress := 10;
1395
1396 get_range_exists_sql(
1397 p_document_id => p_document_id
1398 , p_document_type => p_document_type
1399 , p_document_subtype => p_document_subtype
1400 , p_check_type => g_chktype_ACCOUNT_EXISTS
1401 , p_auth_check_ids => p_auth_check_ids
1402 , x_return_status => l_ret_sts
1403 , x_range_check_sql => l_sql
1404 );
1405
1406 d_progress := 20;
1407
1408 IF (l_ret_sts <> 'S')
1409 THEN
1410 d_msg := 'get_range_exists_sql not successful';
1411 l_ret_sts := 'U';
1412 RAISE PO_CORE_S.g_early_return_exc;
1413 END IF;
1414
1415 IF (PO_LOG.d_stmt) THEN
1416 PO_LOG.stmt(d_module, d_progress, 'l_sql', l_sql);
1417 END IF;
1418
1419 d_progress := 30;
1420
1421 EXECUTE IMMEDIATE l_sql
1422 INTO l_result
1423 USING p_session_gt_key, p_auth_check_ids.position_id
1424 , p_auth_check_ids.position_id, p_auth_check_ids.job_id
1425 , p_auth_check_ids.job_id, p_auth_check_ids.ctl_function_id
1426 , 'ACCOUNT_RANGE';
1427
1428 IF (PO_LOG.d_stmt) THEN
1429 PO_LOG.stmt(d_module, d_progress, 'l_result', l_result);
1430 END IF;
1431
1432 decode_result(
1433 p_document_type => p_document_type
1434 , p_result_val => l_result
1435 , x_authorized_yn => x_authorized_yn);
1436
1437 l_ret_sts := 'S';
1438
1439 EXCEPTION
1440 WHEN PO_CORE_S.g_early_return_exc THEN
1441 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
1442 IF (PO_LOG.d_exc) THEN
1443 PO_LOG.exc(d_module, d_progress, d_msg);
1444 END IF;
1445 END;
1446
1447 x_return_status := l_ret_sts;
1448
1449 IF (PO_LOG.d_proc) THEN
1450 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1451 PO_LOG.proc_end(d_module, 'x_authorized_yn', x_authorized_yn);
1452 PO_LOG.proc_end(d_module);
1453 END IF;
1454
1455 RETURN;
1456
1457 EXCEPTION
1458 WHEN others THEN
1459 x_return_status := 'U';
1460 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1461 IF (PO_LOG.d_exc) THEN
1462 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1463 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1464 PO_LOG.proc_end(d_module);
1465 END IF;
1466
1467 RETURN;
1468
1469 END check_account_exists;
1470
1471 PROCEDURE check_item_limit(
1472 p_document_id IN NUMBER
1473 , p_document_type IN VARCHAR2
1474 , p_document_subtype IN VARCHAR2
1475 , p_session_gt_key IN NUMBER
1476 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
1477 , x_return_status OUT NOCOPY VARCHAR2
1478 , x_authorized_yn OUT NOCOPY VARCHAR2
1479 )
1480 IS
1481
1482 l_sql VARCHAR2(8000);
1483 l_result NUMBER;
1484 l_ret_sts VARCHAR2(1);
1485
1486 d_progress NUMBER;
1487 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.check_item_limit';
1488
1489 d_msg VARCHAR2(200);
1490
1491 BEGIN
1492
1493 d_progress := 0;
1494 IF (PO_LOG.d_proc) THEN
1495 PO_LOG.proc_begin(d_module);
1496 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1497 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1498 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1499 PO_LOG.proc_begin(d_module, 'p_session_gt_key', p_session_gt_key);
1500 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.object_id', p_auth_check_ids.object_id);
1501 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.position_id', p_auth_check_ids.position_id);
1502 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.job_id', p_auth_check_ids.job_id);
1503 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.ctl_function_id', p_auth_check_ids.ctl_function_id);
1504 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.fsp_org_id', p_auth_check_ids.fsp_org_id);
1505 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.coa_id', p_auth_check_ids.coa_id);
1506 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.item_cat_struct_id', p_auth_check_ids.item_cat_struct_id);
1507 END IF;
1508
1509 BEGIN
1510
1511 d_progress := 10;
1512
1513 get_range_limit_sql(
1514 p_document_id => p_document_id
1515 , p_document_type => p_document_type
1516 , p_document_subtype => p_document_subtype
1517 , p_check_type => g_chktype_ITEM_LIMIT
1518 , p_auth_check_ids => p_auth_check_ids
1519 , x_return_status => l_ret_sts
1520 , x_range_check_sql => l_sql
1521 );
1522
1523 d_progress := 20;
1524
1525 IF (l_ret_sts <> 'S')
1526 THEN
1527 d_msg := 'get_range_limit_sql not successful';
1528 l_ret_sts := 'U';
1529 RAISE PO_CORE_S.g_early_return_exc;
1530 END IF;
1531
1532 IF (PO_LOG.d_stmt) THEN
1533 PO_LOG.stmt(d_module, d_progress, 'l_sql', l_sql);
1534 END IF;
1535
1536 d_progress := 30;
1537
1538 EXECUTE IMMEDIATE l_sql
1539 INTO l_result
1540 USING p_session_gt_key, p_auth_check_ids.position_id
1541 , p_auth_check_ids.position_id, p_auth_check_ids.job_id
1542 , p_auth_check_ids.job_id, p_auth_check_ids.ctl_function_id
1543 , 'ITEM_RANGE';
1544
1545 IF (PO_LOG.d_stmt) THEN
1546 PO_LOG.stmt(d_module, d_progress, 'l_result', l_result);
1547 END IF;
1548
1549 decode_result(
1550 p_document_type => p_document_type
1551 , p_result_val => l_result
1552 , x_authorized_yn => x_authorized_yn);
1553
1554 l_ret_sts := 'S';
1555
1556 EXCEPTION
1557 WHEN PO_CORE_S.g_early_return_exc THEN
1558 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
1559 IF (PO_LOG.d_exc) THEN
1560 PO_LOG.exc(d_module, d_progress, d_msg);
1561 END IF;
1562 END;
1563
1564 x_return_status := l_ret_sts;
1565
1566 IF (PO_LOG.d_proc) THEN
1567 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1568 PO_LOG.proc_end(d_module, 'x_authorized_yn', x_authorized_yn);
1569 PO_LOG.proc_end(d_module);
1570 END IF;
1571
1572 RETURN;
1573
1574 EXCEPTION
1575 WHEN others THEN
1576 x_return_status := 'U';
1577 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1578 IF (PO_LOG.d_exc) THEN
1579 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1580 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1581 PO_LOG.proc_end(d_module);
1582 END IF;
1583
1584 RETURN;
1585
1586 END check_item_limit;
1587
1588 PROCEDURE check_category_limit(
1589 p_document_id IN NUMBER
1590 , p_document_type IN VARCHAR2
1591 , p_document_subtype IN VARCHAR2
1592 , p_session_gt_key IN NUMBER
1593 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
1594 , x_return_status OUT NOCOPY VARCHAR2
1595 , x_authorized_yn OUT NOCOPY VARCHAR2
1596 )
1597 IS
1598
1599 l_sql VARCHAR2(8000);
1600 l_result NUMBER;
1601 l_ret_sts VARCHAR2(1);
1602
1603 d_progress NUMBER;
1604 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.check_category_limit';
1605
1606 d_msg VARCHAR2(200);
1607
1608 BEGIN
1609
1610 d_progress := 0;
1611 IF (PO_LOG.d_proc) THEN
1612 PO_LOG.proc_begin(d_module);
1613 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1614 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1615 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1616 PO_LOG.proc_begin(d_module, 'p_session_gt_key', p_session_gt_key);
1617 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.object_id', p_auth_check_ids.object_id);
1618 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.position_id', p_auth_check_ids.position_id);
1619 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.job_id', p_auth_check_ids.job_id);
1620 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.ctl_function_id', p_auth_check_ids.ctl_function_id);
1621 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.fsp_org_id', p_auth_check_ids.fsp_org_id);
1622 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.coa_id', p_auth_check_ids.coa_id);
1623 PO_LOG.proc_begin(d_module, 'p_auth_check_ids.item_cat_struct_id', p_auth_check_ids.item_cat_struct_id);
1624 END IF;
1625
1626 BEGIN
1627
1628 d_progress := 10;
1629
1630 get_range_limit_sql(
1631 p_document_id => p_document_id
1632 , p_document_type => p_document_type
1633 , p_document_subtype => p_document_subtype
1634 , p_check_type => g_chktype_CATEGORY_LIMIT
1635 , p_auth_check_ids => p_auth_check_ids
1636 , x_return_status => l_ret_sts
1637 , x_range_check_sql => l_sql
1638 );
1639
1640 d_progress := 20;
1641
1642 IF (l_ret_sts <> 'S')
1643 THEN
1644 d_msg := 'get_range_limit_sql not successful';
1645 l_ret_sts := 'U';
1646 RAISE PO_CORE_S.g_early_return_exc;
1647 END IF;
1648
1649 IF (PO_LOG.d_stmt) THEN
1650 PO_LOG.stmt(d_module, d_progress, 'l_sql', l_sql);
1651 END IF;
1652
1653 d_progress := 30;
1654
1655 EXECUTE IMMEDIATE l_sql
1656 INTO l_result
1657 USING p_session_gt_key, p_auth_check_ids.position_id
1658 , p_auth_check_ids.position_id, p_auth_check_ids.job_id
1659 , p_auth_check_ids.job_id, p_auth_check_ids.ctl_function_id
1660 , 'ITEM_CATEGORY_RANGE';
1661
1662 IF (PO_LOG.d_stmt) THEN
1663 PO_LOG.stmt(d_module, d_progress, 'l_result', l_result);
1664 END IF;
1665
1666 decode_result(
1667 p_document_type => p_document_type
1668 , p_result_val => l_result
1669 , x_authorized_yn => x_authorized_yn);
1670
1671 l_ret_sts := 'S';
1672
1673 EXCEPTION
1674 WHEN PO_CORE_S.g_early_return_exc THEN
1675 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
1676 IF (PO_LOG.d_exc) THEN
1677 PO_LOG.exc(d_module, d_progress, d_msg);
1678 END IF;
1679 END;
1680
1681 x_return_status := l_ret_sts;
1682
1683 IF (PO_LOG.d_proc) THEN
1684 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1685 PO_LOG.proc_end(d_module, 'x_authorized_yn', x_authorized_yn);
1686 PO_LOG.proc_end(d_module);
1687 END IF;
1688
1689 RETURN;
1690
1691 EXCEPTION
1692 WHEN others THEN
1693 x_return_status := 'U';
1694 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1695 IF (PO_LOG.d_exc) THEN
1696 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1697 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1698 PO_LOG.proc_end(d_module);
1699 END IF;
1700
1701 RETURN;
1702
1703 END check_category_limit;
1704
1705 PROCEDURE get_range_limit_sql(
1706 p_document_id IN NUMBER
1707 , p_document_type IN VARCHAR2
1708 , p_document_subtype IN VARCHAR2
1709 , p_check_type IN VARCHAR2
1710 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
1711 , x_return_status OUT NOCOPY VARCHAR2
1712 , x_range_check_sql OUT NOCOPY VARCHAR2
1713 )
1714 IS
1715
1716 -- x_range_check_sql buffer size should be at least 8000 characters.
1717
1718 d_progress NUMBER;
1719 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.get_range_limit_sql';
1720
1721 l_flex_segment_where VARCHAR2(4000);
1722
1723 l_sum_col VARCHAR2(80);
1724 l_flex_table VARCHAR2(80);
1725 l_flex_join VARCHAR2(80);
1726
1727 l_ret_sts VARCHAR2(1);
1728 d_msg VARCHAR2(200);
1729
1730 BEGIN
1731
1732 d_progress := 0;
1733 IF (PO_LOG.d_proc) THEN
1734 PO_LOG.proc_begin(d_module);
1735 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1736 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1737 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1738 PO_LOG.proc_begin(d_module, 'p_check_type', p_check_type);
1739 END IF;
1740
1741 BEGIN
1742
1743 IF (p_check_type = g_chktype_ACCOUNT_LIMIT)
1744 THEN
1745
1746 d_progress := 10;
1747
1748 l_sum_col := 'pgt.num1';
1749 l_flex_table := ' , gl_code_combinations glcc ';
1750 l_flex_join := ' AND glcc.code_combination_id = pgt.num3 ';
1751
1752 ELSIF (p_check_type = g_chktype_ITEM_LIMIT)
1753 THEN
1754
1755 d_progress := 20;
1756
1757 IF (p_document_type <> 'PA')
1758 THEN
1759 l_sum_col := 'pgt.num1';
1760 ELSE
1761 l_sum_col := 'pgt.num2';
1762 END IF;
1763
1764 l_flex_table := ' , mtl_system_items mtsi ';
1765 l_flex_join := ' AND mtsi.inventory_item_id = pgt.num5 AND mtsi.organization_id = ' || p_auth_check_ids.fsp_org_id || ' ';
1766
1767 ELSIF (p_check_type = g_chktype_CATEGORY_LIMIT)
1768 THEN
1769
1770 d_progress := 30;
1771
1772 IF (p_document_type <> 'PA')
1773 THEN
1774 l_sum_col := 'pgt.num1';
1775 ELSE
1776 l_sum_col := 'pgt.num2';
1777 END IF;
1778
1779 l_flex_table := ' , mtl_categories mtcat ';
1780 l_flex_join := ' AND mtcat.category_id = pgt.num6 ';
1781
1782 ELSE
1783
1784 d_progress := 40;
1785 l_ret_sts := 'U';
1786 d_msg := 'check type not supported';
1787 RAISE PO_CORE_S.g_early_return_exc;
1788
1789 END IF;
1790
1791 IF (PO_LOG.d_stmt) THEN
1792 PO_LOG.stmt(d_module, d_progress, 'l_sum_col', l_sum_col);
1793 PO_LOG.stmt(d_module, d_progress, 'l_flex_table', l_flex_table);
1794 PO_LOG.stmt(d_module, d_progress, 'l_flex_join', l_flex_join);
1795 END IF;
1796
1797 d_progress := 50;
1798
1799 get_flex_where_sql(
1800 p_auth_check_ids => p_auth_check_ids
1801 , p_check_type => p_check_type
1802 , x_return_status => l_ret_sts
1803 , x_flex_sql => l_flex_segment_where
1804 );
1805
1806 IF (l_ret_sts <> 'S') THEN
1807
1808 d_progress := 60;
1809 l_ret_sts := 'U';
1810 d_msg := 'get_flex_where_sql not successful';
1811 RAISE PO_CORE_S.g_early_return_exc;
1812
1813 END IF;
1814
1815 d_progress := 70;
1816
1817 IF (PO_LOG.d_stmt) THEN
1818 PO_LOG.stmt(d_module, d_progress, 'l_flex_segment_where', l_flex_segment_where);
1819 END IF;
1820
1821 -- Bind variables created in sql string:
1822 -- 1 - key into po_session_gt table
1823 -- 2 - p_auth_check_ids.position_id
1824 -- 3 - p_auth_check_ids.job_id
1825 -- 4 - p_auth_check_ids.ctl_function_id
1826 -- 5 - object code in po_control_rules, e.g. 'ACCOUNT_RANGE'
1827
1828 x_range_check_sql := 'SELECT sign(min(nvl(POCR.amount_limit, -1) '
1829 || ' - sum(' || l_sum_col || ')))'
1830 || ' FROM po_session_gt pgt, po_control_rules pocr'
1831 || ' , po_control_groups pocg, po_position_controls popc'
1832 || l_flex_table
1833 || ' WHERE pgt.key = :1 AND ' || l_sum_col || ' IS NOT NULL'
1834 || ' AND pgt.char1 = ''N'' '
1835 || ' AND pgt.char2 <> ''FINALLY CLOSED'' '
1836 || l_flex_join
1837 -- <Bug 4605781 Start>
1838 || ' AND ((:2 IS NULL) OR (popc.position_id = :3))'
1839 || ' AND ((:4 IS NULL) OR (popc.job_id = :5))'
1840 -- <Bug 4605781 End>
1841 || ' AND sysdate BETWEEN NVL(popc.start_date, sysdate - 1) AND NVL(popc.end_date, sysdate + 1)'
1842 || ' AND popc.control_function_id = :6'
1843 || ' AND pocg.enabled_flag = ''Y'' '
1844 || ' AND pocg.control_group_id = popc.control_group_id'
1845 || ' AND pocr.control_group_id = pocg.control_group_id'
1846 || ' AND pocr.object_code = :7 '
1847 || ' AND NVL(pocr.inactive_date, sysdate + 1) > sysdate '
1848 || l_flex_segment_where
1849 || ' GROUP BY pocr.control_rule_id, pocr.amount_limit';
1850
1851 d_progress := 80;
1852 l_ret_sts := 'S';
1853
1854 EXCEPTION
1855 WHEN PO_CORE_S.g_early_return_exc THEN
1856 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
1857 IF (PO_LOG.d_exc) THEN
1858 PO_LOG.exc(d_module, d_progress, d_msg);
1859 END IF;
1860 END;
1861
1862 x_return_status := l_ret_sts;
1863
1864 IF (PO_LOG.d_proc) THEN
1865 PO_LOG.proc_end(d_module, 'x_range_check_sql', x_range_check_sql);
1866 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1867 PO_LOG.proc_end(d_module);
1868 END IF;
1869
1870 RETURN;
1871
1872 EXCEPTION
1873 WHEN others THEN
1874 x_return_status := 'U';
1875 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1876 IF (PO_LOG.d_exc) THEN
1877 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1878 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1879 PO_LOG.proc_end(d_module);
1880 END IF;
1881
1882 RETURN;
1883
1884 END get_range_limit_sql;
1885
1886 PROCEDURE get_range_exists_sql(
1887 p_document_id IN NUMBER
1888 , p_document_type IN VARCHAR2
1889 , p_document_subtype IN VARCHAR2
1890 , p_check_type IN VARCHAR2
1891 , p_auth_check_ids IN AUTH_CHECK_IDS_REC
1892 , x_return_status OUT NOCOPY VARCHAR2
1893 , x_range_check_sql OUT NOCOPY VARCHAR2
1894 )
1895 IS
1896
1897 -- x_range_check_sql buffer size should be at least 8000 characters.
1898
1899 l_flex_segment_where VARCHAR2(4000);
1900 l_flex_table VARCHAR2(80);
1901 l_flex_join VARCHAR2(80);
1902
1903 l_ret_sts VARCHAR2(1);
1904
1905 d_msg VARCHAR2(200);
1906 d_progress NUMBER;
1907 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.get_range_exists_sql';
1908
1909 BEGIN
1910
1911 d_progress := 0;
1912 IF (PO_LOG.d_proc) THEN
1913 PO_LOG.proc_begin(d_module);
1914 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1915 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1916 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1917 PO_LOG.proc_begin(d_module, 'p_check_type', p_check_type);
1918 END IF;
1919
1920 BEGIN
1921
1922 IF (p_check_type = g_chktype_ACCOUNT_EXISTS)
1923 THEN
1924
1925 d_progress := 10;
1926
1927 l_flex_table := ' , gl_code_combinations glcc ';
1928 l_flex_join := ' AND glcc.code_combination_id = pgt.num3 ';
1929
1930 ELSE
1931
1932 d_progress := 20;
1933 l_ret_sts := 'U';
1934 d_msg := 'check type not supported';
1935 RAISE PO_CORE_S.g_early_return_exc;
1936
1937 END IF;
1938
1939 IF (PO_LOG.d_stmt) THEN
1940 PO_LOG.stmt(d_module, d_progress, 'l_flex_table', l_flex_table);
1941 PO_LOG.stmt(d_module, d_progress, 'l_flex_join', l_flex_join);
1942 END IF;
1943
1944 d_progress := 30;
1945
1946 get_flex_where_sql(
1947 p_auth_check_ids => p_auth_check_ids
1948 , p_check_type => p_check_type
1949 , x_return_status => l_ret_sts
1950 , x_flex_sql => l_flex_segment_where
1951 );
1952
1953 IF (l_ret_sts <> 'S') THEN
1954
1955 d_progress := 40;
1956 l_ret_sts := 'U';
1957 d_msg := 'get_flex_where_sql not successful';
1958 RAISE PO_CORE_S.g_early_return_exc;
1959
1960 END IF;
1961
1962 d_progress := 50;
1963
1964 IF (PO_LOG.d_stmt) THEN
1965 PO_LOG.stmt(d_module, d_progress, 'l_flex_segment_where', l_flex_segment_where);
1966 END IF;
1967
1968 -- Bind variables created in sql string:
1969 -- 1 - key into po_session_gt table
1970 -- 2 - p_auth_check_ids.position_id
1971 -- 3 - p_auth_check_ids.job_id
1972 -- 4 - p_auth_check_ids.ctl_function_id
1973 -- 5 - object code in po_control_rules, e.g. 'ACCOUNT_RANGE'
1974
1975 x_range_check_sql := 'SELECT nvl(min(-1),0) '
1976 || ' FROM po_session_gt pgt '
1977 || l_flex_table
1978 || ' WHERE pgt.key = :1 '
1979 || l_flex_join
1980 || ' AND NOT EXISTS ( '
1981 || ' SELECT ''account is in range'' '
1982 || ' FROM po_control_rules pocr, po_control_groups pocg'
1983 || ' , po_position_controls popc '
1984 -- <Bug 4605781 Start>
1985 || ' WHERE ((:2 IS NULL) OR (popc.position_id = :3))'
1986 || ' AND ((:4 IS NULL) OR (popc.job_id = :5))'
1987 -- <Bug 4605781 End>
1988 || ' AND sysdate BETWEEN NVL(popc.start_date, sysdate - 1) AND NVL(popc.end_date, sysdate + 1)'
1989 || ' AND popc.control_function_id = :6'
1990 || ' AND pocg.enabled_flag = ''Y'' '
1991 || ' AND pocg.control_group_id = popc.control_group_id'
1992 || ' AND pocr.control_group_id = pocg.control_group_id'
1993 || ' AND pocr.object_code = :7 '
1994 || ' AND NVL(pocr.inactive_date, sysdate + 1) > sysdate '
1995 || l_flex_segment_where
1996 || ' ) ';
1997
1998
1999 d_progress := 60;
2000 l_ret_sts := 'S';
2001
2002 EXCEPTION
2003 WHEN PO_CORE_S.g_early_return_exc THEN
2004 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
2005 IF (PO_LOG.d_exc) THEN
2006 PO_LOG.exc(d_module, d_progress, d_msg);
2007 END IF;
2008 END;
2009
2010 x_return_status := l_ret_sts;
2011
2012 IF (PO_LOG.d_proc) THEN
2013 PO_LOG.proc_end(d_module, 'x_range_check_sql', x_range_check_sql);
2014 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2015 PO_LOG.proc_end(d_module);
2016 END IF;
2017
2018 RETURN;
2019
2020 EXCEPTION
2021 WHEN others THEN
2022 x_return_status := 'U';
2023 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2024 IF (PO_LOG.d_exc) THEN
2025 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2026 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2027 PO_LOG.proc_end(d_module);
2028 END IF;
2029
2030 RETURN;
2031
2032 END get_range_exists_sql;
2033
2034 PROCEDURE get_flex_where_sql(
2035 p_auth_check_ids IN AUTH_CHECK_IDS_REC
2036 , p_check_type IN VARCHAR2
2037 , x_return_status OUT NOCOPY VARCHAR2
2038 , x_flex_sql OUT NOCOPY VARCHAR2
2039 )
2040 IS
2041
2042 l_flexfield_rec FND_FLEX_KEY_API.flexfield_type;
2043 l_structure_rec FND_FLEX_KEY_API.structure_type;
2044 l_segment_rec FND_FLEX_KEY_API.segment_type;
2045 l_segment_tbl FND_FLEX_KEY_API.segment_list;
2046
2047 l_appl_short_name VARCHAR2(8);
2048 l_flex_code VARCHAR2(8);
2049 l_structure_code NUMBER;
2050 l_table_alias VARCHAR2(8);
2051
2052 l_segment_number NUMBER;
2053 l_idx NUMBER;
2054 l_segment VARCHAR2(160);
2055
2056 d_msg VARCHAR2(200);
2057 d_progress NUMBER;
2058 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.get_flex_where_sql';
2059
2060 BEGIN
2061
2062 d_progress := 0;
2063 IF (PO_LOG.d_proc) THEN
2064 PO_LOG.proc_begin(d_module);
2065 PO_LOG.proc_begin(d_module, 'p_check_type', p_check_type);
2066 END IF;
2067
2068 x_flex_sql := ' ';
2069
2070 IF ((p_check_type = g_chktype_ACCOUNT_LIMIT)
2071 OR (p_check_type = g_chktype_ACCOUNT_EXISTS))
2072 THEN
2073
2074 d_progress := 10;
2075 l_appl_short_name := 'SQLGL';
2076 l_flex_code := 'GL#';
2077 l_structure_code := p_auth_check_ids.coa_id;
2078 l_table_alias := 'glcc';
2079
2080 ELSIF (p_check_type = g_chktype_ITEM_LIMIT)
2081 THEN
2082
2083 d_progress := 20;
2084 l_appl_short_name := 'INV';
2085 l_flex_code := 'MSTK';
2086 l_structure_code := 101;
2087 l_table_alias := 'mtsi';
2088
2089 ELSIF (p_check_type = g_chktype_CATEGORY_LIMIT)
2090 THEN
2091
2092 d_progress := 30;
2093 l_appl_short_name := 'INV';
2094 l_flex_code := 'MCAT';
2095 l_structure_code := p_auth_check_ids.item_cat_struct_id;
2096 l_table_alias := 'mtcat';
2097
2098 ELSE
2099
2100 d_progress := 40;
2101 d_msg := 'check type not supported';
2102 RAISE PO_CORE_S.g_early_return_exc;
2103
2104 END IF; -- p_check_type = ...
2105
2106 IF (PO_LOG.d_stmt) THEN
2107 PO_LOG.stmt(d_module, d_progress, 'l_appl_short_name', l_appl_short_name);
2108 PO_LOG.stmt(d_module, d_progress, 'l_flex_code', l_flex_code);
2109 PO_LOG.stmt(d_module, d_progress, 'l_structure_code', l_structure_code);
2110 PO_LOG.stmt(d_module, d_progress, 'l_table_alias', l_table_alias);
2111 END IF;
2112
2113 d_progress := 50;
2114
2115 -- Call FND_FLEX_KEY_API to get flexfield information
2116 FND_FLEX_KEY_API.set_session_mode('customer_data');
2117
2118 d_progress := 60;
2119
2120 -- Retrieve flexfield sgements
2121 l_flexfield_rec := FND_FLEX_KEY_API.find_flexfield(l_appl_short_name,l_flex_code);
2122 d_progress := 65;
2123 l_structure_rec := FND_FLEX_KEY_API.find_structure(l_flexfield_rec, l_structure_code);
2124
2125 d_progress := 70;
2126
2127 FND_FLEX_KEY_API.get_segments(
2128 flexfield => l_flexfield_rec
2129 , structure => l_structure_rec
2130 , nsegments => l_segment_number
2131 , segments => l_segment_tbl
2132 );
2133
2134 d_progress := 80;
2135
2136 -- Construct the where condition for the flexfield values to be
2137 -- within range of the control rule's values.
2138 FOR l_idx IN 1..l_segment_number
2139 LOOP
2140
2141 d_progress := 90;
2142
2143 l_segment_rec := FND_FLEX_KEY_API.find_segment(l_flexfield_rec,l_structure_rec,l_segment_tbl(l_idx));
2144
2145 l_segment := 'NVL(' || l_table_alias || '.' || l_segment_rec.column_name || ', ''0'')';
2146
2147 x_flex_sql := x_flex_sql || ' AND '|| l_segment || ' BETWEEN '
2148 || ' NVL(POCR.' || l_segment_rec.column_name || '_low, ' || l_segment
2149 || ' ) AND NVL(POCR.' || l_segment_rec.column_name ||'_high, ' || l_segment || ') ';
2150
2151 END LOOP; -- FOR l_idx IN 1..l_segment_number
2152
2153 x_return_status := 'S';
2154
2155 IF (PO_LOG.d_proc) THEN
2156 PO_LOG.proc_end(d_module, 'x_flex_sql', x_flex_sql);
2157 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2158 PO_LOG.proc_end(d_module);
2159 END IF;
2160
2161 RETURN;
2162
2163 EXCEPTION
2164 WHEN others THEN
2165 x_return_status := 'U';
2166 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2167 IF (PO_LOG.d_exc) THEN
2168 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2169 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2170 PO_LOG.proc_end(d_module);
2171 END IF;
2172
2173 RETURN;
2174
2175 END get_flex_where_sql;
2176
2177
2178
2179 PROCEDURE populate_session_gt(
2180 p_document_id IN NUMBER
2181 , p_document_type IN VARCHAR2
2182 , p_document_subtype IN VARCHAR2
2183 , x_session_gt_key OUT NOCOPY NUMBER
2184 , x_return_status OUT NOCOPY VARCHAR2
2185 )
2186 IS
2187
2188 d_progress NUMBER;
2189 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.populate_session_gt';
2190
2191 BEGIN
2192
2193 d_progress := 0;
2194 IF (PO_LOG.d_proc) THEN
2195 PO_LOG.proc_begin(d_module);
2196 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
2197 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2198 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
2199 END IF;
2200
2201 /*
2202 * PO_SESSION_GT:
2203 * key = key into table
2204 * num1 = unit total for most checks
2205 * num2 = unit total for item/cat limit checks for PAs only
2206 * num3 = code combination id (for account limit check)
2207 * num4 = location id (for location check)
2208 * num5 = item_id (for item check)
2209 * num6 = category_id (for category check)
2210 * char1 = cancel_flag
2211 * char2 = closed_code
2212 */
2213
2214 SELECT PO_SESSION_GT_S.nextval INTO x_session_gt_key FROM dual;
2215
2216 d_progress := 10;
2217 IF (PO_LOG.d_stmt) THEN
2218 PO_LOG.stmt(d_module, d_progress, 'x_session_gt_key', x_session_gt_key);
2219 END IF;
2220
2221 IF (p_document_type = 'PO')
2222 THEN
2223
2224 d_progress := 20;
2225
2226 INSERT INTO PO_SESSION_GT(
2227 key
2228 , num1
2229 , num2
2230 , num3
2231 , num4
2232 , num5
2233 , num6
2234 , char1
2235 , char2
2236 )
2237 SELECT
2238 x_session_gt_key
2239 , (DECODE(pod.amount_ordered,
2240 NULL,(pod.quantity_ordered - NVL(pod.quantity_cancelled,0)) * poll.price_override,
2241 pod.amount_ordered - NVL(pod.amount_cancelled,0))
2242 + po_tax_sv.get_tax('PO',pod.po_distribution_id))
2243 * nvl(pod.rate,1)
2244 , NULL
2245 , pod.code_combination_id
2246 , poll.ship_to_location_id + 0
2247 , pol.item_id
2248 , pol.category_id
2249 , DECODE(nvl(pol.cancel_flag, 'N'), 'N', NVL(poll.cancel_flag, 'N'), pol.cancel_flag)
2250 , DECODE(nvl(pol.closed_code, 'OPEN'), 'OPEN', NVL(poll.closed_code, 'OPEN'), pol.closed_code)
2251 FROM po_headers poh
2252 , po_lines pol
2253 , po_line_locations poll
2254 , po_distributions pod
2255 WHERE poh.po_header_id = p_document_id
2256 AND pol.po_header_id = poh.po_header_id
2257 AND poll.po_line_id = pol.po_line_id
2258 AND poll.shipment_type <> 'PREPAYMENT' -- <Complex Work R12>
2259 AND pod.line_location_id = poll.line_location_id
2260 AND ((poh.type_lookup_code <> 'PLANNED') OR
2261 ((poh.type_lookup_code = 'PLANNED') AND (poll.shipment_type = 'PLANNED')))
2262 ;
2263
2264
2265 ELSIF (p_document_type = 'REQUISITION')
2266 THEN
2267
2268 d_progress := 30;
2269
2270 INSERT INTO PO_SESSION_GT(
2271 key
2272 , num1
2273 , num2
2274 , num3
2275 , num4
2276 , num5
2277 , num6
2278 , char1
2279 , char2
2280 )
2281 SELECT
2282 x_session_gt_key
2283 , po_calculatereqtotal_pvt.get_req_distribution_total(
2284 porl.requisition_header_id,porl.requisition_line_id,pord.distribution_id)
2285 , NULL
2286 , pord.code_combination_id
2287 , porl.deliver_to_location_id
2288 , porl.item_id
2289 , porl.category_id
2290 , 'N'
2291 , 'OPEN' -- Bug 4610058
2292 FROM po_req_distributions pord
2293 , po_requisition_lines porl
2294 WHERE porl.requisition_header_id = p_document_id
2295 AND porl.requisition_line_id = pord.requisition_line_id
2296 AND NVL(porl.cancel_flag, 'N') = 'N'
2297 AND NVL(porl.modified_by_agent_flag, 'N') = 'N';
2298
2299 ELSIF (p_document_type = 'RELEASE')
2300 THEN
2301
2302 d_progress := 40;
2303
2304 INSERT INTO PO_SESSION_GT(
2305 key
2306 , num1
2307 , num2
2308 , num3
2309 , num4
2310 , num5
2311 , num6
2312 , char1
2313 , char2
2314 )
2315 SELECT
2316 x_session_gt_key
2317 , (DECODE(pod.amount_ordered,
2318 NULL, (pod.quantity_ordered - NVL(pod.quantity_cancelled,0)) * poll.price_override,
2319 pod.amount_ordered - NVL(pod.amount_cancelled,0))
2320 + po_tax_sv.get_tax('RELEASE',pod.po_distribution_id))
2321 * NVL(pod.rate,1)
2322 , NULL
2323 , pod.code_combination_id
2324 , poll.ship_to_location_id
2325 , pol.item_id
2326 , pol.category_id
2327 , DECODE(nvl(pol.cancel_flag, 'N'), 'N', NVL(poll.cancel_flag, 'N'), pol.cancel_flag)
2328 , DECODE(nvl(pol.closed_code, 'OPEN'), 'OPEN', NVL(poll.closed_code, 'OPEN'), pol.closed_code)
2329 FROM po_distributions pod
2330 , po_line_locations poll
2331 , po_lines pol
2332 WHERE poll.po_release_id = p_document_id
2333 AND poll.po_line_id = pol.po_line_id
2334 AND pod.line_location_id = poll.line_location_id;
2335
2336 ELSIF (p_document_type = 'PA')
2337 THEN
2338
2339 d_progress := 50;
2340
2341 INSERT INTO PO_SESSION_GT(
2342 key
2343 , num1
2344 , num2
2345 , num3
2346 , num4
2347 , num5
2348 , num6
2349 , char1
2350 , char2
2351 )
2352 SELECT
2353 x_session_gt_key
2354 , nvl(poh.blanket_total_amount,0) * nvl(poh.rate,1)
2355 , NULL
2356 , NULL
2357 , NULL
2358 , NULL
2359 , NULL
2360 , 'N'
2361 , 'OPEN' -- Bug 4610058
2362 FROM po_headers poh
2363 WHERE poh.po_header_id = p_document_id;
2364
2365 d_progress := 60;
2366
2367 INSERT INTO PO_SESSION_GT(
2368 key
2369 , num1
2370 , num2
2371 , num3
2372 , num4
2373 , num5
2374 , num6
2375 , char1
2376 , char2
2377 )
2378 SELECT
2379 x_session_gt_key
2380 , NULL
2381 -- Bug 4610058 Start : Should not sum up lines here; that will be done
2382 -- in the range_limit dynamic sql.
2383 , GREATEST(NVL(pol.committed_amount,0),
2384 NVL(pol.quantity_committed, 0) * NVL (pol.unit_price, 0) * NVL (poh.rate, 1))
2385 -- Bug 4510058 End
2386 , NULL
2387 , NULL
2388 , pol.item_id -- Bug 4610058
2389 , pol.category_id -- Bug 4610058
2390 , NVL(pol.cancel_flag, 'N') -- Bug 4610058
2391 , 'OPEN' -- Bug 4610058
2392 FROM po_headers poh
2393 , po_lines pol
2394 WHERE poh.po_header_id = p_document_id
2395 AND pol.po_header_id = poh.po_header_id
2396 AND NVL(pol.cancel_flag, 'N') = 'N';
2397
2398
2399 ELSE
2400
2401 d_progress := 70;
2402 RAISE PO_CORE_S.g_early_return_exc;
2403
2404 END IF; -- p_document_type
2405
2406 x_return_status := 'S';
2407
2408 IF (PO_LOG.d_proc) THEN
2409 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2410 PO_LOG.proc_end(d_module);
2411 END IF;
2412
2413 RETURN;
2414
2415 EXCEPTION
2416 WHEN OTHERS THEN
2417 x_return_status := 'U';
2418
2419 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2420 IF (PO_LOG.d_exc) THEN
2421 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2422 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2423 PO_LOG.proc_end(d_module);
2424 END IF;
2425
2426 RETURN;
2427
2428 END populate_session_gt;
2429
2430 PROCEDURE decode_result(
2431 p_document_type IN VARCHAR2
2432 , p_result_val IN NUMBER
2433 , x_authorized_yn OUT NOCOPY VARCHAR2
2434 )
2435 IS
2436
2437 l_result NUMBER;
2438 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CHECK.decode_result';
2439 BEGIN
2440
2441 IF (PO_LOG.d_proc) THEN
2442 PO_LOG.proc_begin(d_module);
2443 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2444 PO_LOG.proc_begin(d_module, 'p_result_val', p_result_val);
2445 END IF;
2446
2447 -- Bug 4610058: No need for special case for 'PA', which was a result
2448 -- of incorrect comments in old Pro*C code. If no rows are returned for 'PA'
2449 -- check should still pass.
2450
2451 l_result := NVL(p_result_val, 0);
2452
2453
2454 IF (l_result < 0)
2455 THEN
2456 x_authorized_yn := 'N';
2457 ELSE
2458 x_authorized_yn := 'Y';
2459 END IF;
2460
2461 IF (PO_LOG.d_proc) THEN
2462 PO_LOG.proc_end(d_module, 'x_authorized_yn', x_authorized_yn);
2463 PO_LOG.proc_end(d_module);
2464 END IF;
2465
2466 RETURN;
2467
2468 END decode_result;
2469
2470
2471 END PO_DOCUMENT_ACTION_CHECK;