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