[Home] [Help]
PACKAGE BODY: APPS.PO_PRICE_HELPER
Source
1 PACKAGE BODY PO_PRICE_HELPER AS
2 -- $Header: PO_PRICE_HELPER.plb 120.2 2005/12/02 15:40:40 vinokris noship $
3
4 ---------------------------------------------------------------------------
5 -- Modules for debugging.
6 ---------------------------------------------------------------------------
7
8 -- The module base for this package.
9 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
10 PO_LOG.get_package_base('PO_PRICE_HELPER');
11
12 -- The module base for the subprogram.
13 D_no_dists_reserved CONSTANT VARCHAR2(100) :=
14 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'no_dists_reserved');
15
16 -- The module base for the subprogram.
17 D_accruals_allow_update CONSTANT VARCHAR2(100) :=
18 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'accruals_allow_update');
19
20 -- The module base for the subprogram.
21 D_no_timecards_exist CONSTANT VARCHAR2(100) :=
22 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'no_timecards_exist');
23
24 -- The module base for the subprogram.
25 D_no_pending_receipts CONSTANT VARCHAR2(100) :=
26 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'no_pending_receipts');
27
28 -- The module base for the subprogram.
29 D_retro_account_allows_update CONSTANT VARCHAR2(100) :=
30 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'retro_account_allows_update');
31
32 -- The module base for the subprogram.
33 D_warn_amt_based_notif_ctrls CONSTANT VARCHAR2(100) :=
34 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'warn_amt_based_notif_ctrls');
35
36 -- The module base for the subprogram.
37 D_attempt_line_price_update CONSTANT VARCHAR2(100) :=
38 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'attempt_line_price_update');
39
40 -- The module base for the subprogram.
41 D_check_system_allows_update CONSTANT VARCHAR2(100) :=
42 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'check_system_allows_update');
43
44 ---------------------------------------------------------------------------
45 -- Constants.
46 ---------------------------------------------------------------------------
47
48 c_result_type_rank_WARNING CONSTANT NUMBER :=
49 PO_VALIDATIONS.result_type_rank(PO_VALIDATIONS.c_result_type_warning);
50
51 c_ENTITY_TYPE_LINE CONSTANT VARCHAR2(30) := PO_VALIDATIONS.c_ENTITY_TYPE_LINE;
52
53 c_UNIT_PRICE CONSTANT VARCHAR2(30) := 'UNIT_PRICE';
54
55 -- PO_HEADERS_ALL.type_lookup_code
56 c_STANDARD CONSTANT VARCHAR2(10) := 'STANDARD';
57
58 -- PO_LINES_ALL.order_type_lookup_code
59 c_RATE CONSTANT VARCHAR2(5) := 'RATE';
60
61 -- RCV_TRANSACTIONS_INTERFACE.transaction_status_code
62 c_PENDING CONSTANT VARCHAR2(10) := 'PENDING';
63
64 -- Retroactive update mode
65 c_ALL_RELEASES CONSTANT VARCHAR2(30) := 'ALL_RELEASES';
66
67 -- PO_HEADERS_ALL.authorization_status
68 c_INCOMPLETE CONSTANT VARCHAR2(30) := 'INCOMPLETE';
69
70 -- PO_LINES_ALL.price_break_lookup_code
71 c_NON_CUMULATIVE CONSTANT VARCHAR2(30) := 'NON CUMULATIVE';
72
73 -------------------------------------------------------------------------------
74 --Start of Comments
75 --Pre-reqs: None.
76 --Modifies: PO_VALIDATION_RESULTS, sequences.
77 --Locks: None.
78 --Function:
79 -- Determines if a line's price is allowed to be updated.
80 --Parameters:
81 --IN:
82 --p_po_line_id
83 -- Identifies the line that should be checked.
84 --p_draft_id
85 -- Further identifies the line.
86 --OUT:
87 --x_system_allows_update
88 -- Indicates whether or not a price change is allowed.
89 -- 'Y' - price change is allowed.
90 -- 'N' - price change is not allowed.
91 -- VARCHAR2(1)
92 --End of Comments
93 -------------------------------------------------------------------------------
94 PROCEDURE check_system_allows_update(
95 p_po_line_id IN NUMBER
96 , p_price_break_lookup_code IN VARCHAR2
97 , x_system_allows_update OUT NOCOPY VARCHAR2
98 )
99 IS
100 d_mod CONSTANT VARCHAR2(100) := D_check_system_allows_update;
101 d_position NUMBER := 0;
102
103 l_line_id_tbl PO_TBL_NUMBER;
104 l_price_break_lookup_code_tbl PO_TBL_VARCHAR30;
105 l_result_set_id NUMBER;
106 l_result_type VARCHAR2(30);
107 l_results PO_VALIDATION_RESULTS_TYPE;
108
109 BEGIN
110
111 IF PO_LOG.d_proc THEN
112 PO_LOG.proc_begin(d_mod,'p_po_line_id',p_po_line_id);
113 PO_LOG.proc_begin(d_mod,'p_price_break_lookup_code',p_price_break_lookup_code);
114 END IF;
115
116 d_position := 1;
117
118 l_line_id_tbl := PO_TBL_NUMBER(p_po_line_id);
119 l_price_break_lookup_code_tbl := PO_TBL_VARCHAR30(p_price_break_lookup_code);
120
121 d_position := 100;
122
123 IF PO_LOG.d_stmt THEN
124 PO_LOG.stmt(d_mod,d_position,'l_line_id_tbl',l_line_id_tbl);
125 END IF;
126
127 PO_VALIDATIONS.validate_unit_price_change(
128 p_line_id_tbl => l_line_id_tbl
129 , p_price_break_lookup_code_tbl => l_price_break_lookup_code_tbl
130 , p_stopping_result_type => PO_VALIDATIONS.c_result_type_FAILURE
131 , x_result_type => l_result_type
132 , x_result_set_id => l_result_set_id
133 , x_results => l_results
134 );
135
136 d_position := 200;
137 IF PO_LOG.d_stmt THEN
138 PO_LOG.stmt(d_mod,d_position,'l_result_set_id',l_result_set_id);
139 PO_LOG.stmt(d_mod,d_position,'l_result_type',l_result_type);
140 END IF;
141
142 IF (PO_VALIDATIONS.result_type_rank(l_result_type) >=
143 c_result_type_rank_WARNING)
144 THEN
145 x_system_allows_update := 'Y';
146 ELSE
147 x_system_allows_update := 'N';
148 END IF;
149
150 d_position := 300;
151
152 IF PO_LOG.d_proc THEN
153 PO_LOG.proc_end(d_mod,'x_system_allows_update',x_system_allows_update);
154 END IF;
155
156 EXCEPTION
157 WHEN OTHERS THEN
158 IF PO_LOG.d_exc THEN
159 PO_LOG.exc(d_mod,d_position,NULL);
160 END IF;
161 RAISE;
162
163 END check_system_allows_update;
164
165
166 -------------------------------------------------------------------------------
167 --Start of Comments
168 --Pre-reqs: None.
169 --Modifies: PO_VALIDATION_RESULTS, sequences.
170 --Locks: None.
171 --Function:
172 -- Verifies that the line's price is allowed to be updated
173 -- and calls the Pricing API to get a new price.
174 --Parameters:
175 -- See the parameter descriptions for PO_SOURCING_SV2.get_break_price.
176 --OUT:
177 --x_system_allows_update
178 -- Indicates whether or not a price change is allowed.
179 -- 'Y' - price change is allowed.
180 -- 'N' - price change is not allowed.
181 -- VARCHAR2(1)
182 --End of Comments
183 -------------------------------------------------------------------------------
184 PROCEDURE attempt_line_price_update(
185 p_order_quantity IN NUMBER
186 , p_ship_to_org IN NUMBER
187 , p_ship_to_loc IN NUMBER
188 , p_po_line_id IN NUMBER
189 , p_need_by_date IN DATE
190 , p_line_location_id IN NUMBER
191 , p_contract_id IN NUMBER
192 , p_org_id IN NUMBER
193 , p_supplier_id IN NUMBER
194 , p_supplier_site_id IN NUMBER
195 , p_creation_date IN DATE
196 , p_order_header_id IN NUMBER
197 , p_order_line_id IN NUMBER
198 , p_line_type_id IN NUMBER
199 , p_item_revision IN VARCHAR2
200 , p_item_id IN NUMBER
201 , p_category_id IN NUMBER
202 , p_supplier_item_num IN VARCHAR2
203 , p_uom IN VARCHAR2
204 , p_in_price IN NUMBER
205 , p_currency_code IN VARCHAR2
206 , p_price_break_lookup_code IN VARCHAR2
207 , x_base_unit_price OUT NOCOPY NUMBER
208 , x_price_break_id OUT NOCOPY NUMBER
209 , x_price OUT NOCOPY NUMBER
210 , x_return_status OUT NOCOPY VARCHAR2
211 , x_from_advanced_pricing OUT NOCOPY VARCHAR2
212 , x_system_allows_update OUT NOCOPY VARCHAR2
213 )
214 IS
215 d_mod CONSTANT VARCHAR2(100) := D_attempt_line_price_update;
216 d_position NUMBER := 0;
217 BEGIN
218
219 IF PO_LOG.d_proc THEN
220 PO_LOG.proc_begin(d_mod,'p_order_quantity',p_order_quantity);
221 PO_LOG.proc_begin(d_mod,'p_ship_to_org',p_ship_to_org);
222 PO_LOG.proc_begin(d_mod,'p_ship_to_loc',p_ship_to_loc);
223 PO_LOG.proc_begin(d_mod,'p_po_line_id',p_po_line_id);
224 PO_LOG.proc_begin(d_mod,'p_need_by_date',p_need_by_date);
225 PO_LOG.proc_begin(d_mod,'p_line_location_id',p_line_location_id);
226 PO_LOG.proc_begin(d_mod,'p_contract_id',p_contract_id);
227 PO_LOG.proc_begin(d_mod,'p_org_id',p_org_id);
228 PO_LOG.proc_begin(d_mod,'p_supplier_id',p_supplier_id);
229 PO_LOG.proc_begin(d_mod,'p_supplier_site_id',p_supplier_site_id);
230 PO_LOG.proc_begin(d_mod,'p_creation_date',p_creation_date);
231 PO_LOG.proc_begin(d_mod,'p_order_header_id',p_order_header_id);
232 PO_LOG.proc_begin(d_mod,'p_order_line_id',p_order_line_id);
233 PO_LOG.proc_begin(d_mod,'p_line_type_id',p_line_type_id);
234 PO_LOG.proc_begin(d_mod,'p_item_revision',p_item_revision);
235 PO_LOG.proc_begin(d_mod,'p_item_id',p_item_id);
236 PO_LOG.proc_begin(d_mod,'p_category_id',p_category_id);
237 PO_LOG.proc_begin(d_mod,'p_supplier_item_num',p_supplier_item_num);
238 PO_LOG.proc_begin(d_mod,'p_uom',p_uom);
239 PO_LOG.proc_begin(d_mod,'p_in_price',p_in_price);
240 PO_LOG.proc_begin(d_mod,'p_currency_code',p_currency_code);
241 END IF;
242
243 d_position := 1;
244
245 check_system_allows_update(
246 p_po_line_id => p_order_line_id
247 , p_price_break_lookup_code => p_price_break_lookup_code
248 , x_system_allows_update => x_system_allows_update
249 );
250
251 d_position := 90;
252 IF PO_LOG.d_stmt THEN
253 PO_LOG.stmt(d_mod,d_position,'x_system_allows_update',x_system_allows_update);
254 END IF;
255
256 IF (x_system_allows_update = 'Y') THEN
257 d_position := 100;
258
259 PO_SOURCING2_SV.get_break_price(
260 p_api_version => 1.0
261 , p_order_quantity => p_order_quantity
262 , p_ship_to_org => p_ship_to_org
263 , p_ship_to_loc => p_ship_to_loc
264 , p_po_line_id => p_po_line_id
265 , p_cum_flag => FALSE
266 , p_need_by_date => TRUNC(p_need_by_date)
267 , p_line_location_id => p_line_location_id
268 , p_contract_id => p_contract_id
269 , p_org_id => p_org_id
270 , p_supplier_id => p_supplier_id
271 , p_supplier_site_id => p_supplier_site_id
272 , p_creation_date => p_creation_date
273 , p_order_header_id => p_order_header_id
274 , p_order_line_id => p_order_line_id
275 , p_line_type_id => p_line_type_id
276 , p_item_revision => p_item_revision
277 , p_item_id => p_item_id
278 , p_category_id => p_category_id
279 , p_supplier_item_num => p_supplier_item_num
280 , p_uom => p_uom
281 , p_in_price => p_in_price
282 , p_currency_code => p_currency_code
283 , x_base_unit_price => x_base_unit_price
284 , x_price_break_id => x_price_break_id
285 , x_price => x_price
286 , x_return_status => x_return_status
287 , x_from_advanced_pricing => x_from_advanced_pricing
288 );
289
290 END IF;
291
292 IF PO_LOG.d_proc THEN
293 PO_LOG.proc_end(d_mod,'x_base_unit_price',x_base_unit_price);
294 PO_LOG.proc_end(d_mod,'x_price_break_id',x_price_break_id);
295 PO_LOG.proc_end(d_mod,'x_price',x_price);
296 PO_LOG.proc_end(d_mod,'x_return_status',x_return_status);
297 PO_LOG.proc_end(d_mod,'x_from_advanced_pricing',x_from_advanced_pricing);
298 PO_LOG.proc_end(d_mod,'x_system_allows_update',x_system_allows_update);
299 END IF;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 IF PO_LOG.d_exc THEN
304 PO_LOG.exc(d_mod,d_position,NULL);
305 END IF;
306 RAISE;
307
308 END attempt_line_price_update;
309
310
311 -------------------------------------------------------------------------------
312 --Start of Comments
313 --Pre-reqs: None.
314 --Modifies: PO_VALIDATION_RESULTS, sequences.
315 --Locks: None.
316 --Function:
317 -- Inserts a row into PO_VALIDATION_RESULTS for any of the specified
318 -- lines that have a reserved distribution.
319 --Parameters:
320 --IN:
321 --p_line_id_tbl
322 -- Identifies the lines that should be checked.
323 --p_draft_id_tbl
324 -- Used to insert messages into PO_VALIDATION_RESULTS.
325 --IN OUT:
326 --x_result_set_id
327 -- The identifier into PO_VALIDATION_RESULTS for the results produced.
328 -- If this is NULL, it will be retrieved from the sequence.
329 --OUT:
330 --x_result_type
331 -- Indicates whether or not any error results were produced.
332 -- c_result_type_FAILURE - results were produced.
333 -- c_result_type_SUCCESS - no errors.
334 -- VARCHAR2(30)
335 --End of Comments
336 -------------------------------------------------------------------------------
337 PROCEDURE no_dists_reserved(
338 p_line_id_tbl IN PO_TBL_NUMBER
339 , x_result_set_id IN OUT NOCOPY NUMBER
340 , x_result_type OUT NOCOPY VARCHAR2
341 )
342 IS
343 d_mod CONSTANT VARCHAR2(100) := D_no_dists_reserved;
344 d_position NUMBER := 0;
345 BEGIN
346
347 IF PO_LOG.d_proc THEN
348 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
349 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
350 END IF;
351
352 d_position := 1;
353
354 IF (x_result_set_id IS NULL) THEN
355 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
356 END IF;
357
358 d_position := 100;
359
360 FORALL i IN 1 .. p_line_id_tbl.COUNT
361 INSERT INTO PO_VALIDATION_RESULTS_GT
362 ( result_set_id
363 , entity_type
364 , entity_id
365 , column_name
366 , message_name
367 )
368 SELECT
369 x_result_set_id
370 , c_ENTITY_TYPE_LINE
371 , p_line_id_tbl(i)
372 , c_UNIT_PRICE
373 , NULL -- TODO: Get message from PM.
374 FROM
375 PO_LINES_ALL LINE
376 , PO_HEADERS_ALL HEADER
377 WHERE
378 LINE.po_line_id = p_line_id_tbl(i)
379 AND HEADER.po_header_id = LINE.po_header_id
380 AND HEADER.type_lookup_code = c_STANDARD
381 AND LINE.order_type_lookup_code <> c_RATE
382 AND EXISTS
383 ( SELECT NULL
384 FROM PO_DISTRIBUTIONS_ALL DIST
385 WHERE
386 DIST.po_line_id = LINE.po_line_id
387 AND DIST.encumbered_flag = 'Y'
388 )
389 ;
390
391 d_position := 200;
392
393 IF (SQL%ROWCOUNT > 0) THEN
394 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
395 ELSE
396 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
397 END IF;
398
399 IF PO_LOG.d_proc THEN
400 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
401 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
402 END IF;
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 IF PO_LOG.d_exc THEN
407 PO_LOG.exc(d_mod,d_position,NULL);
408 END IF;
409 RAISE;
410
411 END no_dists_reserved;
412
413
414 -------------------------------------------------------------------------------
415 --Start of Comments
416 --Pre-reqs: None.
417 --Modifies: PO_VALIDATION_RESULTS, sequences.
418 --Locks: None.
419 --Function:
420 -- Inserts a row into PO_VALIDATION_RESULTS for each of the lines specified
421 -- whose accrued status should prevent an update to the price.
422 -- An example of an error is that some quantity has been billed and
423 -- retroactive price updates are not allowed.
424 --Parameters:
425 --IN:
426 --p_line_id_tbl
427 -- Identifies the lines that should be checked.
428 --p_draft_id_tbl
429 -- Used to insert messages into PO_VALIDATION_RESULTS.
430 --IN OUT:
431 --x_result_set_id
432 -- The identifier into PO_VALIDATION_RESULTS for the results produced.
433 -- If this is NULL, it will be retrieved from the sequence.
434 --OUT:
435 --x_result_type
436 -- Indicates whether or not any error results were produced.
437 -- c_result_type_FAILURE - results were produced.
438 -- c_result_type_SUCCESS - no errors.
439 -- VARCHAR2(30)
440 --End of Comments
441 -------------------------------------------------------------------------------
442 PROCEDURE accruals_allow_update(
443 p_line_id_tbl IN PO_TBL_NUMBER
444 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
445 , x_result_type OUT NOCOPY VARCHAR2
446 )
447 IS
448 d_mod CONSTANT VARCHAR2(100) := D_accruals_allow_update;
449 d_position NUMBER := 0;
450 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
451
452 l_results_count NUMBER;
453 l_data_key NUMBER;
454 l_line_id_tbl PO_TBL_NUMBER;
455 l_expense_accrual_code_tbl PO_TBL_VARCHAR4000;
456 l_header_id_tbl PO_TBL_NUMBER;
457 l_type_lookup_code_tbl PO_TBL_VARCHAR4000;
458
459 l_quantity_received NUMBER;
460 l_quantity_billed NUMBER;
461 l_encumbered_flag VARCHAR2(1);
462 l_prevent_price_update_flag VARCHAR2(1);
463 l_online_req_flag VARCHAR2(1);
464 l_quantity_released NUMBER;
465 l_amount_released NUMBER;
466
467 BEGIN
468
469 IF PO_LOG.d_proc THEN
470 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
471 END IF;
472
473 d_position := 1;
474
475 IF (x_results IS NULL) THEN
476 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
477 END IF;
478
479 l_results_count := x_results.result_type.COUNT;
480
481
482 d_position := 10;
483
484
485 l_data_key := PO_CORE_S.get_session_gt_nextval();
486
487 FORALL i IN 1 .. p_line_id_tbl.COUNT
488 INSERT INTO PO_SESSION_GT
489 ( key
490 , num1
491 )
492 VALUES
493 ( l_data_key
494 , p_line_id_tbl(i)
495 )
496 ;
497
498 d_position := 20;
499
500 SELECT
501 LINE.po_line_id
502 , HEADER.po_header_id
503 , HEADER.type_lookup_code
504 , PARAMS.expense_accrual_code
505 BULK COLLECT INTO
506 l_line_id_tbl
507 , l_header_id_tbl
508 , l_type_lookup_code_tbl
509 , l_expense_accrual_code_tbl
510 FROM
511 PO_SESSION_GT SES
512 , PO_LINES_ALL LINE
513 , PO_HEADERS_ALL HEADER
514 , PO_SYSTEM_PARAMETERS_ALL PARAMS
515 WHERE
516 SES.key = l_data_key
517 AND LINE.po_line_id = SES.num1
518 AND HEADER.po_header_id = LINE.po_header_id
519 AND HEADER.type_lookup_code = c_STANDARD
520 AND PARAMS.org_id = HEADER.org_id
521 ;
522
523 d_position := 100;
524
525 IF PO_LOG.d_stmt THEN
526 PO_LOG.stmt_session_gt(d_mod,d_position,l_data_key);
527 PO_LOG.stmt(d_mod,d_position,'l_line_id_tbl',l_line_id_tbl);
528 PO_LOG.stmt(d_mod,d_position,'l_header_id_tbl',l_header_id_tbl);
529 PO_LOG.stmt(d_mod,d_position,'l_type_lookup_code_tbl',l_type_lookup_code_tbl);
530 PO_LOG.stmt(d_mod,d_position,'l_expense_accrual_code_tbl',l_expense_accrual_code_tbl);
531 END IF;
532
533 FOR i IN 1 .. l_line_id_tbl.COUNT LOOP
534
535 -- The prevent_price_udpate_flag should start as N,
536 -- and an error will only be reported if it becomed Y.
537 l_prevent_price_update_flag := 'N';
538
539 d_position := 200;
540
541 PO_LINES_SV4.get_ship_quantity_info(
542 x_po_line_id => l_line_id_tbl(i)
543 , x_expense_accrual_code => l_expense_accrual_code_tbl(i)
544 , x_po_header_id => l_header_id_tbl(i)
545 , x_type_lookup_code => l_type_lookup_code_tbl(i)
546 , x_quantity_received => l_quantity_received
547 , x_quantity_billed => l_quantity_billed
548 , x_encumbered_flag => l_encumbered_flag
549 , x_prevent_price_update_flag => l_prevent_price_update_flag
550 , x_online_req_flag => l_online_req_flag
551 , x_quantity_released => l_quantity_released
552 , x_amount_released => l_amount_released
553 );
554
555 d_position := 300;
556 IF d_stmt THEN
557 PO_LOG.stmt(d_mod,d_position,'l_prevent_price_update_flag',l_prevent_price_update_flag);
558 END IF;
559
560 IF (l_prevent_price_update_flag = 'Y') THEN
561 d_position := 400;
562
563 x_results.add_result(
564 p_entity_type => c_ENTITY_TYPE_LINE
565 , p_entity_id => l_line_id_tbl(i)
566 , p_column_name => c_UNIT_PRICE
567 , p_message_name => NULL -- TODO: need message from PM.
568 );
569
570 d_position := 450;
571
572 END IF;
573
574 END LOOP;
575
576 IF (l_results_count < x_results.result_type.COUNT) THEN
577 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
578 ELSE
579 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
580 END IF;
581
582 IF PO_LOG.d_proc THEN
583 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
584 END IF;
585
586 EXCEPTION
587 WHEN OTHERS THEN
588 IF PO_LOG.d_exc THEN
589 PO_LOG.exc(d_mod,d_position,NULL);
590 END IF;
591 RAISE;
592
593 END accruals_allow_update;
594
595
596 -------------------------------------------------------------------------------
597 --Start of Comments
598 --Pre-reqs: None.
599 --Modifies: PO_VALIDATION_RESULTS, sequences.
600 --Locks: None.
601 --Function:
602 -- Inserts a row into PO_VALIDATION_RESULTS for any of the specified
603 -- lines for which there are submitted or approved timecards.
604 --Parameters:
605 --IN:
606 --p_line_id_tbl
607 -- Identifies the lines that should be checked.
608 --p_draft_id_tbl
609 -- Used to insert messages into PO_VALIDATION_RESULTS.
610 --IN OUT:
611 --x_result_set_id
612 -- The identifier into PO_VALIDATION_RESULTS for the results produced.
613 -- If this is NULL, it will be retrieved from the sequence.
614 --OUT:
615 --x_result_type
616 -- Indicates whether or not any error results were produced.
617 -- c_result_type_FAILURE - results were produced.
618 -- c_result_type_SUCCESS - no errors.
619 -- VARCHAR2(30)
620 --End of Comments
621 -------------------------------------------------------------------------------
622 PROCEDURE no_timecards_exist(
623 p_line_id_tbl IN PO_TBL_NUMBER
624 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
625 , x_result_type OUT NOCOPY VARCHAR2
626 )
627 IS
628 BEGIN
629
630 PO_VALIDATION_HELPER.no_timecards_exist(
631 p_calling_module => D_no_timecards_exist
632 , p_line_id_tbl => p_line_id_tbl
633 , p_start_date_tbl => NULL
634 , p_expiration_date_tbl => NULL
635 , p_column_name => c_UNIT_PRICE
636 , p_message_name => PO_MESSAGE_S.PO_CHNG_OTL_NO_PRICE_CHANGE
637 , x_results => x_results
638 , x_result_type => x_result_type
639 );
640
641 END no_timecards_exist;
642
643
644 -------------------------------------------------------------------------------
645 --Start of Comments
646 --Pre-reqs: None.
647 --Modifies: PO_VALIDATION_RESULTS, sequences.
648 --Locks: None.
649 --Function:
650 -- Inserts a row into PO_VALIDATION_RESULTS for any of the specified
651 -- lines for which there are pending receipts.
652 --Parameters:
653 --IN:
654 --p_line_id_tbl
655 -- Identifies the lines that should be checked.
656 --p_draft_id_tbl
657 -- Used to insert messages into PO_VALIDATION_RESULTS.
658 --IN OUT:
659 --x_result_set_id
660 -- The identifier into PO_VALIDATION_RESULTS for the results produced.
661 -- If this is NULL, it will be retrieved from the sequence.
662 --OUT:
663 --x_result_type
664 -- Indicates whether or not any error results were produced.
665 -- c_result_type_FAILURE - results were produced.
666 -- c_result_type_SUCCESS - no errors.
667 -- VARCHAR2(30)
668 --End of Comments
669 -------------------------------------------------------------------------------
670 PROCEDURE no_pending_receipts(
671 p_line_id_tbl IN PO_TBL_NUMBER
672 , x_result_set_id IN OUT NOCOPY NUMBER
673 , x_result_type OUT NOCOPY VARCHAR2
674 )
675 IS
676 d_mod CONSTANT VARCHAR2(100) := D_no_pending_receipts;
677 d_position NUMBER := 0;
678 BEGIN
679
680 IF PO_LOG.d_proc THEN
681 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
682 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
683 END IF;
684
685 d_position := 1;
686
687 IF (x_result_set_id IS NULL) THEN
688 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
689 END IF;
690
691 d_position := 100;
692
693 FORALL i IN 1 .. p_line_id_tbl.COUNT
694 INSERT INTO PO_VALIDATION_RESULTS_GT
695 ( result_set_id
696 , entity_type
697 , entity_id
698 , column_name
699 , message_name
700 )
701 SELECT
702 x_result_set_id
703 , c_ENTITY_TYPE_LINE
704 , p_line_id_tbl(i)
705 , c_UNIT_PRICE
706 , PO_MESSAGE_S.PO_RCV_TRANSACTION_PENDING
707 FROM DUAL
708 WHERE EXISTS
709 ( SELECT null
710 FROM
711 RCV_TRANSACTIONS_INTERFACE RTI
712 , PO_LINE_LOCATIONS_ALL POLL
713 WHERE
714 RTI.po_line_location_id = POLL.line_location_id
715 AND POLL.po_line_id = p_line_id_tbl(i)
716 AND RTI.transaction_status_code = c_PENDING
717 )
718 ;
719
720 d_position := 200;
721
722 IF (SQL%ROWCOUNT > 0) THEN
723 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
724 ELSE
725 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
726 END IF;
727
728 IF PO_LOG.d_proc THEN
729 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
730 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
731 END IF;
732
733 EXCEPTION
734 WHEN OTHERS THEN
735 IF PO_LOG.d_exc THEN
736 PO_LOG.exc(d_mod,d_position,NULL);
737 END IF;
738 RAISE;
739
740 END no_pending_receipts;
741
742
743 -------------------------------------------------------------------------------
744 --Start of Comments
745 --Pre-reqs: None.
746 --Modifies: PO_VALIDATION_RESULTS, sequences.
747 --Locks: None.
748 --Function:
749 -- Inserts a row into PO_VALIDATION_RESULTS for any of the specified
750 -- lines for which the retroactive account setup is not valid.
751 --Parameters:
752 --IN:
753 --p_line_id_tbl
754 -- Identifies the lines that should be checked.
755 --p_draft_id_tbl
756 -- Used to insert messages into PO_VALIDATION_RESULTS.
757 --IN OUT:
758 --x_result_set_id
759 -- The identifier into PO_VALIDATION_RESULTS for the results produced.
760 -- If this is NULL, it will be retrieved from the sequence.
761 --OUT:
762 --x_result_type
763 -- Indicates whether or not any error results were produced.
764 -- c_result_type_FAILURE - results were produced.
765 -- c_result_type_SUCCESS - no errors.
766 -- VARCHAR2(30)
767 --End of Comments
768 -------------------------------------------------------------------------------
769 PROCEDURE retro_account_allows_update(
770 p_line_id_tbl IN PO_TBL_NUMBER
771 , p_price_break_lookup_code_tbl IN PO_TBL_VARCHAR30
772 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
773 , x_result_type OUT NOCOPY VARCHAR2
774 )
775 IS
776 d_mod CONSTANT VARCHAR2(100) := D_retro_account_allows_update;
777 d_position NUMBER := 0;
778 d_stmt CONSTANT BOOLEAN := PO_LOG.d_stmt;
779
780 l_results_count NUMBER;
781 l_data_key NUMBER;
782
783 l_line_id_tbl PO_TBL_NUMBER;
784 l_price_break_lookup_code_tbl PO_TBL_VARCHAR4000;
785
786 l_line_id NUMBER;
787 l_price_break_lookup_code VARCHAR2(4000);
788
789 l_retroactive_update_mode VARCHAR2(30);
790 l_account_valid VARCHAR2(1);
791
792 BEGIN
793
794 IF PO_LOG.d_proc THEN
795 PO_LOG.proc_begin(d_mod,'p_line_id_tbl',p_line_id_tbl);
796 PO_LOG.proc_begin(d_mod,'p_price_break_lookup_code_tbl',p_price_break_lookup_code_tbl);
797 END IF;
798
799 d_position := 1;
800
801 IF (x_results IS NULL) THEN
802 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
803 END IF;
804
805 l_results_count := x_results.result_type.COUNT;
806
807 d_position := 50;
808
809 l_retroactive_update_mode := PO_RETROACTIVE_PRICING_PVT.get_retro_mode();
810
811 d_position := 90;
812 IF d_stmt THEN
813 PO_LOG.stmt(d_mod,d_position,'l_retroactive_update_mode',l_retroactive_update_mode);
814 END IF;
815
816 IF (l_retroactive_update_mode = c_ALL_RELEASES) THEN
817 d_position := 100;
818
819 l_data_key := PO_CORE_S.get_session_gt_nextval();
820
821 FORALL i IN 1 .. p_line_id_tbl.COUNT
822 INSERT INTO PO_SESSION_GT
823 ( key
824 , num1
825 , char1
826 )
827 VALUES
828 ( l_data_key
829 , p_line_id_tbl(i)
830 , p_price_break_lookup_code_tbl(i)
831 )
832 ;
833
834 d_position := 110;
835
836 SELECT
837 SES.num1
838 , SES.char1
839 BULK COLLECT INTO
840 l_line_id_tbl
841 , l_price_break_lookup_code_tbl
842 FROM
843 PO_SESSION_GT SES
844 , PO_LINES_ALL LINE
845 , PO_HEADERS_ALL HEADER
846 WHERE
847 SES.key = l_data_key
848 AND LINE.po_line_id = SES.num1
849 AND HEADER.po_header_id = LINE.po_header_id
850 AND HEADER.type_lookup_code = c_STANDARD
851 AND HEADER.authorization_status <> c_INCOMPLETE
852 AND (SES.char1 IS NULL OR SES.char1 = c_NON_CUMULATIVE)
853 ;
854
855 d_position := 120;
856
857 IF PO_LOG.d_stmt THEN
858 PO_LOG.stmt_session_gt(d_mod,d_position,l_data_key);
859 PO_LOG.stmt(d_mod,d_position,'l_line_id_tbl',l_line_id_tbl);
860 PO_LOG.stmt(d_mod,d_position,'l_price_break_lookup_code',l_price_break_lookup_code);
861 END IF;
862
863 FOR i IN 1 .. l_line_id_tbl.COUNT LOOP
864 d_position := 200;
865
866 l_line_id := l_line_id_tbl(i);
867 l_price_break_lookup_code := l_price_break_lookup_code_tbl(i);
868
869 IF d_stmt THEN
870 PO_LOG.stmt(d_mod,d_position,'iteration '||i||' l_line_id',l_line_id);
871 END IF;
872
873 l_account_valid :=
874 PO_RETROACTIVE_PRICING_PVT.is_adjustment_account_valid(
875 p_std_po_price_change => 'Y'
876 , p_po_line_id => l_line_id
877 , p_po_line_loc_id => NULL
878 );
879
880 d_position := 250;
881
882 IF d_stmt THEN
883 PO_LOG.stmt(d_mod,d_position,'l_account_valid',l_account_valid);
884 END IF;
885
886 IF (l_account_valid = 'N') THEN
887 d_position := 300;
888
889 x_results.add_result(
890 p_entity_type => c_ENTITY_TYPE_LINE
891 , p_entity_id => l_line_id
892 , p_message_name => PO_MESSAGE_S.PO_CHNG_OTL_NO_PRICE_CHANGE
893 , p_column_name => c_UNIT_PRICE
894 );
895
896 d_position := 350;
897
898 END IF;
899
900 END LOOP;
901
902 END IF;
903
904 IF (l_results_count < x_results.result_type.COUNT) THEN
905 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
906 ELSE
907 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
908 END IF;
909
910 IF PO_LOG.d_proc THEN
911 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
912 END IF;
913
914 EXCEPTION
915 WHEN OTHERS THEN
916 IF PO_LOG.d_exc THEN
917 PO_LOG.exc(d_mod,d_position,NULL);
918 END IF;
919 RAISE;
920
921 END retro_account_allows_update;
922
923
924 -------------------------------------------------------------------------------
925 --Start of Comments
926 --Pre-reqs: None.
927 --Modifies: PO_VALIDATION_RESULTS, sequences.
928 --Locks: None.
929 --Function:
930 -- Inserts a row into PO_VALIDATION_RESULTS for any of the specified
931 -- lines for which amount based notification controls exist.
932 --Parameters:
933 --IN:
934 --p_line_id_tbl
935 -- Identifies the lines that should be checked.
936 --p_draft_id_tbl
937 -- Used to insert messages into PO_VALIDATION_RESULTS.
938 --IN OUT:
939 --x_result_set_id
940 -- The identifier into PO_VALIDATION_RESULTS for the results produced.
941 -- If this is NULL, it will be retrieved from the sequence.
942 --OUT:
943 --x_result_type
944 -- Indicates whether or not any error results were produced.
945 -- c_result_type_WARNING - results were produced.
946 -- c_result_type_SUCCESS - no errors.
947 -- VARCHAR2(30)
948 --End of Comments
949 -------------------------------------------------------------------------------
950 PROCEDURE warn_amt_based_notif_ctrls(
951 p_line_id_tbl IN PO_TBL_NUMBER
952 , x_result_set_id IN OUT NOCOPY NUMBER
953 , x_result_type OUT NOCOPY VARCHAR2
954 )
955 IS
956 BEGIN
957
958 PO_VALIDATION_HELPER.amount_notif_ctrl_warning(
959 p_calling_module => D_warn_amt_based_notif_ctrls
960 , p_line_id_tbl => p_line_id_tbl
961 , p_quantity_tbl => NULL
962 , p_column_name => c_UNIT_PRICE
963 , p_message_name => PO_MESSAGE_S.PO_PO_NFC_PRICE_CHANGE
964 , x_result_set_id => x_result_set_id
965 , x_result_type => x_result_type
966 );
967
968 END warn_amt_based_notif_ctrls;
969
970
971 END PO_PRICE_HELPER;