1 PACKAGE BODY PO_COMPLEX_WORK_PVT AS
2 -- $Header: PO_COMPLEX_WORK_PVT.plb 120.5 2010/06/24 13:04:33 vinnaray ship $
3
4 ------------------------------------------------------------------------------
5 --Start of Comments
6 --Name: get_payment_style_settings
7 --Pre-reqs:
8 -- None
9 --Modifies:
10 -- None.
11 --Locks:
12 -- None.
13 --Function:
14 -- This procedure returns all important flags related to complex
15 -- work procurement that can be derived from a style.
16 --Parameters:
17 --IN:
18 -- p_style_id
19 -- ID of the style to get the complex work flags for.
20 --OUT:
21 -- x_complex_work_flag
22 -- 'Y': Any document with this style uses progress payments.
23 -- 'N': Any document with this style cannot use progress payments.
24 -- x_financing_payments_flag
25 -- 'Y': All user entered payitems for a document with this style
26 -- are financing (prepayment) pay items.
27 -- 'N': All user entered payitems for a document with this style
28 -- are actual (standard) pay items.
29 -- x_retainage_allowed_flag
30 -- 'Y': Retainage terms can be specified as part of the document.
31 -- 'N': Retainage terms cannot be specified in the document.
32 -- x_advance_allowed_flag
33 -- 'Y': An advance amount can be specified at the line level.
34 -- 'N': Advance amounts cannot be specified.
35 -- x_milestone_allowed_flag
36 -- 'Y': Complex work POs with this style can contain pay items of type MILESTONE.
37 -- 'N': Complex work POs with this style cannot contain pay items of type MILESTONE.
38 -- x_lumpsum_allowed_flag
39 -- 'Y': Complex work POs with this style can contain pay items of type LUMPSUM.
40 -- 'N': Complex work POs with this style cannot contain pay items of type LUMPSUM.
41 -- x_rate_allowed_flag
42 -- 'Y': Complex work POs with this style can contain pay items of type RATE.
43 -- 'N': Complex work POs with this style cannot contain pay items of type RATE.
44 --End of Comments
45 -------------------------------------------------------------------------------
46 PROCEDURE get_payment_style_settings(
47 p_style_id IN NUMBER
48 , x_complex_work_flag OUT NOCOPY VARCHAR2
49 , x_financing_payments_flag OUT NOCOPY VARCHAR2
50 , x_retainage_allowed_flag OUT NOCOPY VARCHAR2
51 , x_advance_allowed_flag OUT NOCOPY VARCHAR2
52 , x_milestone_allowed_flag OUT NOCOPY VARCHAR2
53 , x_lumpsum_allowed_flag OUT NOCOPY VARCHAR2
54 , x_rate_allowed_flag OUT NOCOPY VARCHAR2
55 )
56 IS
57
58 d_progress NUMBER;
59 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.get_payment_style_settings';
60
61 l_style_name PO_DOC_STYLE_HEADERS.style_name%TYPE;
62 l_style_desc PO_DOC_STYLE_HEADERS.style_description%TYPE;
63 l_style_type PO_DOC_STYLE_HEADERS.style_type%TYPE;
64 l_status PO_DOC_STYLE_HEADERS.status%TYPE;
65 l_price_breaks_flag PO_DOC_STYLE_HEADERS.price_breaks_flag%TYPE;
66 l_price_diffs_flag PO_DOC_STYLE_HEADERS.price_differentials_flag%TYPE;
67 l_line_type_allowed PO_DOC_STYLE_HEADERS.line_type_allowed%TYPE;
68
69 TYPE t_payment_type_tbl IS TABLE OF PO_STYLE_ENABLED_PAY_ITEMS.pay_item_type%TYPE;
70 l_payment_types_tbl t_payment_type_tbl;
71
72 BEGIN
73
74 d_progress := 0;
75 IF (PO_LOG.d_proc) THEN
76 PO_LOG.proc_begin(d_module);
77 PO_LOG.proc_begin(d_module, 'p_style_id', p_style_id);
78 END IF;
79
80 d_progress := 10;
81
82 PO_DOC_STYLE_GRP.get_document_style_settings(
83 p_api_version => 1.0
84 , p_style_id => p_style_id
85 , x_style_name => l_style_name
86 , x_style_description => l_style_desc
87 , x_style_type => l_style_type
88 , x_status => l_status
89 , x_advances_flag => x_advance_allowed_flag
90 , x_retainage_flag => x_retainage_allowed_flag
91 , x_price_breaks_flag => l_price_breaks_flag
92 , x_price_differentials_flag => l_price_diffs_flag
93 , x_progress_payment_flag => x_complex_work_flag
94 , x_contract_financing_flag => x_financing_payments_flag
95 , x_line_type_allowed => l_line_type_allowed
96 );
97
98 d_progress := 20;
99
100 x_advance_allowed_flag := NVL(x_advance_allowed_flag, 'N');
101 x_retainage_allowed_flag := NVL(x_retainage_allowed_flag, 'N');
102 x_complex_work_flag := NVL(x_complex_work_flag, 'N');
103 x_financing_payments_flag := NVL(x_financing_payments_flag, 'N');
104
105 d_progress := 30;
106
107 SELECT psepi.pay_item_type
108 BULK COLLECT INTO l_payment_types_tbl
109 FROM po_style_enabled_pay_items psepi
110 WHERE psepi.style_id = p_style_id;
111
112 d_progress := 40;
113
114 x_milestone_allowed_flag := 'N';
115 x_lumpsum_allowed_flag := 'N';
116 x_rate_allowed_flag := 'N';
117
118 d_progress := 50;
119
120 FOR i IN 1..l_payment_types_tbl.COUNT
121 LOOP
122
126
123 IF (l_payment_types_tbl(i) = g_payment_type_MILESTONE) THEN
124
125 x_milestone_allowed_flag := 'Y';
127 ELSIF (l_payment_types_tbl(i) = g_payment_type_LUMPSUM) THEN
128
129 x_lumpsum_allowed_flag := 'Y';
130
131 ELSIF (l_payment_types_tbl(i) = g_payment_type_RATE) THEN
132
133 x_rate_allowed_flag := 'Y';
134
135 END IF;
136
137 END LOOP;
138
139 IF (PO_LOG.d_proc) THEN
140 PO_LOG.proc_end(d_module, 'x_complex_work_flag', x_complex_work_flag);
141 PO_LOG.proc_end(d_module, 'x_financing_payments_flag', x_financing_payments_flag);
142 PO_LOG.proc_end(d_module, 'x_retainage_allowed_flag', x_retainage_allowed_flag);
143 PO_LOG.proc_end(d_module, 'x_advance_allowed_flag', x_advance_allowed_flag);
144 PO_LOG.proc_end(d_module, 'x_milestone_allowed_flag', x_milestone_allowed_flag);
145 PO_LOG.proc_end(d_module, 'x_lumpsum_allowed_flag', x_lumpsum_allowed_flag);
146 PO_LOG.proc_end(d_module, 'x_rate_allowed_flag', x_rate_allowed_flag);
147 PO_LOG.proc_end(d_module);
148 END IF;
149
150 EXCEPTION
151 WHEN OTHERS THEN
152 IF (PO_LOG.d_exc) THEN
153 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
154 PO_LOG.proc_end(d_module);
155 END IF;
156 RAISE;
157 END get_payment_style_settings;
158
159
160 ------------------------------------------------------------------------------
161 --Start of Comments
162 --Name: is_complex_work_style
163 --Pre-reqs:
164 -- None
165 --Modifies:
166 -- None.
167 --Locks:
168 -- None.
169 --Function:
170 -- This function will determine whether a style indicates complex work.
171 --Parameters:
172 --IN:
173 -- p_style_id
174 -- ID of the style to get the complex work flags for.
175 --RETURNS:
176 -- TRUE: Any document with this style uses progress payments
177 -- FALSE: Any document with this style cannot use progress payments.
178 --End of Comments
179 -------------------------------------------------------------------------------
180 FUNCTION is_complex_work_style(p_style_id IN NUMBER) RETURN BOOLEAN
181 IS
182
183 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.is_complex_work_style';
184 d_progress NUMBER;
185
186 l_style_name PO_DOC_STYLE_HEADERS.style_name%TYPE;
187 l_style_desc PO_DOC_STYLE_HEADERS.style_description%TYPE;
188 l_style_type PO_DOC_STYLE_HEADERS.style_type%TYPE;
189 l_status PO_DOC_STYLE_HEADERS.status%TYPE;
190 l_advances_flag PO_DOC_STYLE_HEADERS.advances_flag%TYPE;
191 l_retainage_flag PO_DOC_STYLE_HEADERS.retainage_flag%TYPE;
192 l_price_breaks_flag PO_DOC_STYLE_HEADERS.price_breaks_flag%TYPE;
193 l_price_diffs_flag PO_DOC_STYLE_HEADERS.price_differentials_flag%TYPE;
194 l_complex_work_flag PO_DOC_STYLE_HEADERS.progress_payment_flag%TYPE;
195 l_financing_flag PO_DOC_STYLE_HEADERS.contract_financing_flag%TYPE;
196 l_line_type_allowed PO_DOC_STYLE_HEADERS.line_type_allowed%TYPE;
197
198 l_is_complex_style BOOLEAN;
199
200 BEGIN
201
202 d_progress := 0;
203 IF (PO_LOG.d_proc) THEN
204 PO_LOG.proc_begin(d_module);
205 PO_LOG.proc_begin(d_module, 'p_style_id', p_style_id);
206 END IF;
207
208 d_progress := 10;
209
210 PO_DOC_STYLE_GRP.get_document_style_settings(
211 p_api_version => 1.0
212 , p_style_id => p_style_id
213 , x_style_name => l_style_name
214 , x_style_description => l_style_desc
215 , x_style_type => l_style_type
216 , x_status => l_status
217 , x_advances_flag => l_advances_flag
218 , x_retainage_flag => l_retainage_flag
219 , x_price_breaks_flag => l_price_breaks_flag
220 , x_price_differentials_flag => l_price_diffs_flag
221 , x_progress_payment_flag => l_complex_work_flag
222 , x_contract_financing_flag => l_financing_flag
223 , x_line_type_allowed => l_line_type_allowed
224 );
225
226 d_progress := 20;
227
228 IF (NVL(l_complex_work_flag, 'N') = 'Y')
229 THEN
230 l_is_complex_style := TRUE;
231 ELSE
232 l_is_complex_style := FALSE;
233 END IF;
234
235 d_progress := 30;
236
237 IF (PO_LOG.d_proc) THEN
238 PO_LOG.proc_return(d_module, l_is_complex_style);
239 PO_LOG.proc_end(d_module);
240 END IF;
241
242 RETURN l_is_complex_style;
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 IF (PO_LOG.d_exc) THEN
247 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
248 PO_LOG.proc_end(d_module);
249 END IF;
250 RAISE;
251 END is_complex_work_style;
252
253
254 ------------------------------------------------------------------------------
255 --Start of Comments
256 --Name: is_financing_payment_style
257 --Pre-reqs:
258 -- None
259 --Modifies:
260 -- None.
261 --Locks:
262 -- None.
263 --Function:
264 -- This function will determine whether a style indicates that payitems for
265 -- a document are of type financing (PREPAYMENT) as opposed to actuals (STANDARD).
266 -- Note: This function will not first check if a style is complex work enabled.
267 --Parameters:
268 --IN:
269 -- p_style_id
270 -- ID of the style to get the complex work flags for.
271 --RETURNS:
272 -- TRUE: Any document with this style uses financing progress payments.
276 FUNCTION is_financing_payment_style(p_style_id IN NUMBER) RETURN BOOLEAN
273 -- FALSE: Any document with this style uses actuals progress payments.
274 --End of Comments
275 -------------------------------------------------------------------------------
277 IS
278
279 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.is_financing_payment_style';
280 d_progress NUMBER;
281
282 l_style_name PO_DOC_STYLE_HEADERS.style_name%TYPE;
283 l_style_desc PO_DOC_STYLE_HEADERS.style_description%TYPE;
284 l_style_type PO_DOC_STYLE_HEADERS.style_type%TYPE;
285 l_status PO_DOC_STYLE_HEADERS.status%TYPE;
286 l_advances_flag PO_DOC_STYLE_HEADERS.advances_flag%TYPE;
287 l_retainage_flag PO_DOC_STYLE_HEADERS.retainage_flag%TYPE;
288 l_price_breaks_flag PO_DOC_STYLE_HEADERS.price_breaks_flag%TYPE;
289 l_price_diffs_flag PO_DOC_STYLE_HEADERS.price_differentials_flag%TYPE;
290 l_complex_work_flag PO_DOC_STYLE_HEADERS.progress_payment_flag%TYPE;
291 l_financing_flag PO_DOC_STYLE_HEADERS.contract_financing_flag%TYPE;
292 l_line_type_allowed PO_DOC_STYLE_HEADERS.line_type_allowed%TYPE;
293
294 l_is_financing_style BOOLEAN;
295
296 BEGIN
297
298 d_progress := 0;
299 IF (PO_LOG.d_proc) THEN
300 PO_LOG.proc_begin(d_module);
301 PO_LOG.proc_begin(d_module, 'p_style_id', p_style_id);
302 END IF;
303
304 d_progress := 10;
305
306 PO_DOC_STYLE_GRP.get_document_style_settings(
307 p_api_version => 1.0
308 , p_style_id => p_style_id
309 , x_style_name => l_style_name
310 , x_style_description => l_style_desc
311 , x_style_type => l_style_type
312 , x_status => l_status
313 , x_advances_flag => l_advances_flag
314 , x_retainage_flag => l_retainage_flag
315 , x_price_breaks_flag => l_price_breaks_flag
316 , x_price_differentials_flag => l_price_diffs_flag
317 , x_progress_payment_flag => l_complex_work_flag
318 , x_contract_financing_flag => l_financing_flag
319 , x_line_type_allowed => l_line_type_allowed
320 );
321
322 d_progress := 20;
323
324 IF (NVL(l_financing_flag, 'N') = 'Y')
325 THEN
326 l_is_financing_style := TRUE;
327 ELSE
328 l_is_financing_style := FALSE;
329 END IF;
330
331 d_progress := 30;
332
333 IF (PO_LOG.d_proc) THEN
334 PO_LOG.proc_return(d_module, l_is_financing_style);
335 PO_LOG.proc_end(d_module);
336 END IF;
337
338 RETURN l_is_financing_style;
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 IF (PO_LOG.d_exc) THEN
343 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
344 PO_LOG.proc_end(d_module);
345 END IF;
346 RAISE;
347 END is_financing_payment_style;
348
349
350 ------------------------------------------------------------------------------
351 --Start of Comments
352 --Name: is_complex_work_po
353 --Pre-reqs:
354 -- None
355 --Modifies:
356 -- None.
357 --Locks:
358 -- None.
359 --Function:
360 -- This function will determine whether a PO is a complex work PO.
361 --Parameters:
362 --IN:
363 -- p_po_header_id
364 -- Header ID of the PO to check whether or not it's a complex work PO
365 --RETURNS:
366 -- TRUE: The PO is a complex work PO
367 -- FALSE: The PO is not a complex work PO
368 --End of Comments
369 -------------------------------------------------------------------------------
370 FUNCTION is_complex_work_po(p_po_header_id IN NUMBER) RETURN BOOLEAN
371 IS
372
373 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.is_complex_work_po';
374 d_progress NUMBER;
375
376 BEGIN
377
378 RETURN is_complex_work_po(p_po_header_id, -1);
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 IF (PO_LOG.d_exc) THEN
383 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
384 PO_LOG.proc_end(d_module);
385 END IF;
386 RAISE;
387 END is_complex_work_po;
388
389
390 ------------------------------------------------------------------------------
391 --Start of Comments
392 --Name: is_complex_work_po
393 --Pre-reqs:
394 -- None
395 --Modifies:
396 -- None.
397 --Locks:
398 -- None.
399 --Function:
400 -- This function will determine whether a PO is a complex work PO.
401 --Parameters:
402 --IN:
403 -- p_po_header_id
404 -- Header ID of the PO to check whether or not it's a complex work PO
405 -- p_draft_id
406 -- Draft_id if called from autocreate
407 --RETURNS:
408 -- TRUE: The PO is a complex work PO
409 -- FALSE: The PO is not a complex work PO
410 --End of Comments
411 -------------------------------------------------------------------------------
412 FUNCTION is_complex_work_po(p_po_header_id IN NUMBER,
413 p_draft_id IN NUMBER) RETURN BOOLEAN
414 IS
415
416 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.is_complex_work_po';
417 d_progress NUMBER;
418
419 l_style_id PO_HEADERS_ALL.style_id%TYPE;
420 l_is_complex_po BOOLEAN;
421
422 BEGIN
423
424 d_progress := 0;
425 IF (PO_LOG.d_proc) THEN
426 PO_LOG.proc_begin(d_module);
427 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
428 END IF;
429
430 d_progress := 10;
431
435 WHERE poh.po_header_id = p_po_header_id
432 SELECT poh.style_id
433 INTO l_style_id
434 FROM po_headers_merge_v poh
436 AND poh.draft_id = p_draft_id;
437
438 d_progress := 20;
439
440 IF (l_style_id IS NOT NULL) THEN
441
442 d_progress := 30;
443 l_is_complex_po := is_complex_work_style(p_style_id => l_style_id);
444
445 ELSE
446
447 d_progress := 40;
448 IF (PO_LOG.d_stmt) THEN
449 PO_LOG.stmt(d_module, d_progress, 'Style is NULL!');
450 END IF;
451
452 l_is_complex_po := FALSE;
453
454 END IF;
455
456 IF (PO_LOG.d_proc) THEN
457 PO_LOG.proc_return(d_module, l_is_complex_po);
458 PO_LOG.proc_end(d_module);
459 END IF;
460
461 RETURN l_is_complex_po;
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 IF (PO_LOG.d_exc) THEN
466 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
467 PO_LOG.proc_end(d_module);
468 END IF;
469 RAISE;
470 END is_complex_work_po;
471
472
473 ------------------------------------------------------------------------------
474 --Start of Comments
475 --Name: is_financing_po
476 --Pre-reqs:
477 -- None
478 --Modifies:
479 -- None.
480 --Locks:
481 -- None.
482 --Function:
483 -- This function will determine whether a PO uses financing (PREPAYMENT) or
484 -- actuals (STANDARD) payitems.
485 -- Note: This function will not first check if the PO is a complex work PO.
486 --Parameters:
487 --IN:
488 -- p_po_header_id
489 -- Header ID of the PO to check financing vs. actuals for.
490 --RETURNS:
491 -- TRUE: The PO uses financing pay items
492 -- FALSE: The PO uses actuals pay items
493 --End of Comments
494 -------------------------------------------------------------------------------
495 FUNCTION is_financing_po(p_po_header_id IN NUMBER) RETURN BOOLEAN
496 IS
497
498 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.is_financing_po';
499 d_progress NUMBER;
500
501 BEGIN
502
503 RETURN is_financing_po(p_po_header_id, -1);
504
505 EXCEPTION
506 WHEN OTHERS THEN
507 IF (PO_LOG.d_exc) THEN
508 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
509 PO_LOG.proc_end(d_module);
510 END IF;
511 RAISE;
512 END is_financing_po;
513
514 ------------------------------------------------------------------------------
515 --Start of Comments
516 --Name: is_financing_po
517 --Pre-reqs:
518 -- None
519 --Modifies:
520 -- None.
521 --Locks:
522 -- None.
523 --Function:
524 -- This function will determine whether a PO uses financing (PREPAYMENT) or
525 -- actuals (STANDARD) payitems.
526 -- Note: This function will not first check if the PO is a complex work PO.
527 --Parameters:
528 --IN:
529 -- p_po_header_id
530 -- Header ID of the PO to check financing vs. actuals for.
531 -- p_draft_id
532 -- Draft_id if called from autocreate
533 --RETURNS:
534 -- TRUE: The PO uses financing pay items
535 -- FALSE: The PO uses actuals pay items
536 --End of Comments
537 -------------------------------------------------------------------------------
538 FUNCTION is_financing_po(p_po_header_id IN NUMBER,
539 p_draft_id IN NUMBER) RETURN BOOLEAN
540 IS
541
542 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.is_financing_po';
543 d_progress NUMBER;
544
545 l_style_id PO_HEADERS_ALL.style_id%TYPE;
546 l_is_financing_po BOOLEAN;
547
548 BEGIN
549
550 d_progress := 0;
551 IF (PO_LOG.d_proc) THEN
552 PO_LOG.proc_begin(d_module);
553 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
554 END IF;
555
556 d_progress := 10;
557
558 SELECT poh.style_id
559 INTO l_style_id
560 FROM po_headers_merge_v poh
561 WHERE poh.po_header_id = p_po_header_id
562 AND poh.draft_id = p_draft_id;
563
564 d_progress := 20;
565
566 IF (l_style_id IS NOT NULL) THEN
567
568 d_progress := 30;
569 l_is_financing_po := is_financing_payment_style(p_style_id => l_style_id);
570
571 ELSE
572
573 d_progress := 40;
574 IF (PO_LOG.d_stmt) THEN
575 PO_LOG.stmt(d_module, d_progress, 'Style is NULL!');
576 END IF;
577
578 l_is_financing_po := FALSE;
579
580 END IF;
581
582 IF (PO_LOG.d_proc) THEN
583 PO_LOG.proc_return(d_module, l_is_financing_po);
584 PO_LOG.proc_end(d_module);
585 END IF;
586
587 RETURN l_is_financing_po;
588
589 EXCEPTION
590 WHEN OTHERS THEN
591 IF (PO_LOG.d_exc) THEN
592 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
593 PO_LOG.proc_end(d_module);
594 END IF;
595 RAISE;
596 END is_financing_po;
597
598
599 ------------------------------------------------------------------------------
600 --Start of Comments
601 --Name: get_default_payitem_info
602 --Pre-reqs:
603 -- None
604 --Modifies:
605 -- None.
606 --Locks:
607 -- None.
608 --Function:
609 -- This procedure calculates the default payment_type, quantity, amount
610 -- and price from line information. It relies on the style at the
614 --Parameters:
611 -- header level to determine the default payment type.
612 -- This procedure returns values that can be used to default the first pay item
613 -- created for a line.
615 --IN:
616 -- p_po_header_id
617 -- Header ID for the PO to get default payment information for
618 -- Style id should already be populated in the headers table
619 -- p_po_line_id
620 -- Line ID for the PO line that the payitem belongs to
621 -- p_line_value_basis
622 -- Value Basis (order_type_lookup_code) of the line that the
623 -- payitem belongs to. This should be one of: 'FIXED PRICE', 'QUANTITY'
624 -- p_line_qty
625 -- Quantity at the line level
626 -- p_line_amt
627 -- Amount at the line level
628 -- p_price
629 -- Price at the line level
630 --OUT:
631 -- x_payment_type
632 -- Default payment type, as determined by the style settings
633 -- One of: g_payment_type_<>, where <> = MILESTONE, RATE, LUMPSUM
634 -- x_payitem_qty
635 -- Default quantity at the payitem level
636 -- x_payitem_amt
637 -- Default amount at the payitem level
638 -- x_payitem_price
639 -- Default price (price_override) at the payitem level
640 --End of Comments
641 -------------------------------------------------------------------------------
642 PROCEDURE get_default_payitem_info(
643 p_po_header_id IN NUMBER
644 , p_po_line_id IN NUMBER
645 , p_line_value_basis IN VARCHAR2
646 , p_line_matching_basis IN VARCHAR2
647 , p_line_qty IN NUMBER
648 , p_line_amt IN NUMBER
649 , p_line_price IN NUMBER
650 , x_payment_type OUT NOCOPY VARCHAR2
651 , x_payitem_qty OUT NOCOPY NUMBER
652 , x_payitem_amt OUT NOCOPY NUMBER
653 , x_payitem_price OUT NOCOPY NUMBER
654 )
655 IS
656
657 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.get_default_payitem_info';
658 d_progress NUMBER;
659
660 BEGIN
661
662 get_default_payitem_info(
663 p_po_header_id
664 , -1
665 , p_po_line_id
666 , p_line_value_basis
667 , p_line_matching_basis
668 , p_line_qty
669 , p_line_amt
670 , p_line_price
671 , x_payment_type
672 , x_payitem_qty
673 , x_payitem_amt
674 , x_payitem_price
675 );
676
677 EXCEPTION
678 WHEN OTHERS THEN
679 IF (PO_LOG.d_exc) THEN
680 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
681 PO_LOG.proc_end(d_module);
682 END IF;
683 RAISE;
684 END get_default_payitem_info;
685
686 ------------------------------------------------------------------------------
687 --Start of Comments
688 --Name: get_default_payitem_info
689 --Pre-reqs:
690 -- None
691 --Modifies:
692 -- None.
693 --Locks:
694 -- None.
695 --Function:
696 -- This procedure calculates the default payment_type, quantity, amount
697 -- and price from line information. It relies on the style at the
698 -- header level to determine the default payment type.
699 -- This procedure returns values that can be used to default the first pay item
700 -- created for a line.
701 --Parameters:
702 --IN:
703 -- p_po_header_id
704 -- Header ID for the PO to get default payment information for
705 -- Style id should already be populated in the headers table
706 -- p_draft_id
707 -- Draft_id if called from autocreate
708 -- p_po_line_id
709 -- Line ID for the PO line that the payitem belongs to
710 -- p_line_value_basis
711 -- Value Basis (order_type_lookup_code) of the line that the
712 -- payitem belongs to. This should be one of: 'FIXED PRICE', 'QUANTITY'
713 -- p_line_qty
714 -- Quantity at the line level
715 -- p_line_amt
716 -- Amount at the line level
717 -- p_price
718 -- Price at the line level
719 --OUT:
720 -- x_payment_type
721 -- Default payment type, as determined by the style settings
722 -- One of: g_payment_type_<>, where <> = MILESTONE, RATE, LUMPSUM
723 -- x_payitem_qty
724 -- Default quantity at the payitem level
725 -- x_payitem_amt
726 -- Default amount at the payitem level
727 -- x_payitem_price
728 -- Default price (price_override) at the payitem level
729 --End of Comments
730 -------------------------------------------------------------------------------
731 PROCEDURE get_default_payitem_info(
732 p_po_header_id IN NUMBER
733 , p_draft_id IN NUMBER
734 , p_po_line_id IN NUMBER
735 , p_line_value_basis IN VARCHAR2
736 , p_line_matching_basis IN VARCHAR2
737 , p_line_qty IN NUMBER
738 , p_line_amt IN NUMBER
739 , p_line_price IN NUMBER
740 , x_payment_type OUT NOCOPY VARCHAR2
741 , x_payitem_qty OUT NOCOPY NUMBER
742 , x_payitem_amt OUT NOCOPY NUMBER
743 , x_payitem_price OUT NOCOPY NUMBER
744 )
745 IS
746
747 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.get_default_payitem_info';
748 d_progress NUMBER;
749
750 l_style_id PO_HEADERS.style_id%TYPE;
751 l_is_complex_flag VARCHAR2(1);
752 l_is_financing_flag VARCHAR2(1);
753 l_retainage_flag VARCHAR2(1);
757 l_rate_flag VARCHAR2(1);
754 l_advance_flag VARCHAR2(1);
755 l_milestone_flag VARCHAR2(1);
756 l_lumpsum_flag VARCHAR2(1);
758
759 BEGIN
760
761 d_progress := 0;
762 IF (PO_LOG.d_proc) THEN
763 PO_LOG.proc_begin(d_module);
764 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
765 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
766 PO_LOG.proc_begin(d_module, 'p_po_line_id', p_po_line_id);
767 PO_LOG.proc_begin(d_module, 'p_line_value_basis', p_line_value_basis);
768 PO_LOG.proc_begin(d_module, 'p_line_matching_basis', p_line_matching_basis);
769 PO_LOG.proc_begin(d_module, 'p_line_qty', p_line_qty);
770 PO_LOG.proc_begin(d_module, 'p_line_amt', p_line_amt);
771 PO_LOG.proc_begin(d_module, 'p_line_price', p_line_price);
772 END IF;
773
774 d_progress := 10;
775
776 SELECT poh.style_id
777 INTO l_style_id
778 FROM po_headers_merge_v poh
779 WHERE poh.po_header_id = p_po_header_id
780 AND poh.draft_id = p_draft_id;
781
782 d_progress := 20;
783
784 get_payment_style_settings(
785 p_style_id => l_style_id
786 , x_complex_work_flag => l_is_complex_flag
787 , x_financing_payments_flag => l_is_financing_flag
788 , x_retainage_allowed_flag => l_retainage_flag
789 , x_advance_allowed_flag => l_advance_flag
790 , x_milestone_allowed_flag => l_milestone_flag
791 , x_lumpsum_allowed_flag => l_lumpsum_flag
792 , x_rate_allowed_flag => l_rate_flag
793 );
794
795 d_progress := 30;
796
797 x_payment_type := NULL;
798 x_payitem_qty := NULL;
799 x_payitem_amt := NULL;
800 x_payitem_price := NULL;
801
802 d_progress := 40;
803
804 IF (l_is_complex_flag = 'Y') THEN
805
806 IF (p_line_value_basis = 'QUANTITY') THEN
807
808 x_payment_type := g_payment_type_MILESTONE;
809 x_payitem_price := p_line_price;
810 x_payitem_qty := p_line_qty;
811
812 ELSIF (p_line_value_basis = 'FIXED PRICE') THEN
813
814 IF (l_lumpsum_flag = 'Y') THEN
815
816 x_payment_type := g_payment_type_LUMPSUM;
817 x_payitem_amt := p_line_amt;
818
819 ELSIF (l_rate_flag = 'Y') THEN
820
821 x_payment_type := g_payment_type_RATE;
822 x_payitem_qty := 1;
823 x_payitem_price := p_line_amt;
824
825 ELSIF (l_milestone_flag = 'Y') THEN
826
827 x_payment_type := g_payment_type_MILESTONE;
828 x_payitem_amt := p_line_amt;
829
830 END IF; -- l_lumpsum_flag = 'Y'
831
832 END IF; -- p_line_value_basis = ...
833
834 END IF; -- if l_is_complex_flag = 'Y'
835
836 IF (PO_LOG.d_proc) THEN
837 PO_LOG.proc_end(d_module, 'x_payment_type', x_payment_type);
838 PO_LOG.proc_end(d_module, 'x_payitem_qty', x_payitem_qty);
839 PO_LOG.proc_end(d_module, 'x_payitem_amt', x_payitem_amt);
840 PO_LOG.proc_end(d_module, 'x_payitem_price', x_payitem_price);
841 PO_LOG.proc_end(d_module);
842 END IF;
843
844 EXCEPTION
845 WHEN OTHERS THEN
846 IF (PO_LOG.d_exc) THEN
847 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
848 PO_LOG.proc_end(d_module);
849 END IF;
850 RAISE;
851 END get_default_payitem_info;
852
853
854 ------------------------------------------------------------------------------
855 --Start of Comments
856 --Name: get_advanace_amount
857 --Pre-reqs:
858 -- None
859 --Modifies:
860 -- None.
861 --Locks:
862 -- None.
863 --Function:
864 -- This function will return the advance amount of a line, as stored
865 -- within its advance payitem.
866 --Parameters:
867 --IN:
868 -- p_po_line_id
869 -- Line id of the line to get advance amount for.
870 -- p_doc_revision_num
871 -- If checking for archived advance amount, pass in the document's revision
872 -- number. If checking main tables, pass NULL (default).
873 -- p_which_tables
874 -- Either 'MAIN' for current line or 'ARCHIVE' for line on archived doc.
875 --RETURNS:
876 -- Advance amount, or NULL if no advance payitem exists.
877 --End of Comments
878 -------------------------------------------------------------------------------
879 FUNCTION get_advance_amount(
880 p_po_line_id IN NUMBER
881 , p_doc_revision_num IN NUMBER DEFAULT NULL
882 , p_which_tables IN VARCHAR2 DEFAULT 'MAIN'
883 ) RETURN NUMBER
884 IS
885
886 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.get_advance_amount';
887 d_progress NUMBER;
888
889 l_advance_amount PO_LINE_LOCATIONS_ALL.amount%TYPE;
890
891 BEGIN
892
893 d_progress := 0;
894 IF (PO_LOG.d_proc) THEN
895 PO_LOG.proc_begin(d_module);
896 PO_LOG.proc_begin(d_module, 'p_po_line_id', p_po_line_id);
897 PO_LOG.proc_begin(d_module, 'p_doc_revision_num', p_doc_revision_num);
898 PO_LOG.proc_begin(d_module, 'p_which_tables', p_which_tables);
899 END IF;
900
901 IF (p_which_tables = 'MAIN') THEN
902
903 d_progress := 20;
904
905 SELECT poll.amount
906 INTO l_advance_amount
907 FROM po_line_locations_all poll
908 WHERE poll.po_line_id = p_po_line_id
909 AND poll.payment_type = 'ADVANCE';
910
911 ELSE
912
913 d_progress := 30;
914
915 SELECT polla.amount
916 INTO l_advance_amount
917 FROM po_line_locations_archive_all polla
918 WHERE polla.po_line_id = p_po_line_id
919 AND polla.revision_num =
920 (
921 SELECT MAX(polla2.revision_num)
922 FROM po_line_locations_archive_all polla2
923 WHERE polla2.line_location_id = polla.line_location_id
924 AND polla2.revision_num <= p_doc_revision_num
925 )
926 AND polla.payment_type = 'ADVANCE';
927
928 END IF;
929
930 IF (PO_LOG.d_proc) THEN
931 PO_LOG.proc_return(d_module, l_advance_amount);
932 PO_LOG.proc_end(d_module);
933 END IF;
934
935 RETURN l_advance_amount;
936
937 EXCEPTION
938 WHEN NO_DATA_FOUND THEN
939 IF (PO_LOG.d_proc) THEN
940 PO_LOG.stmt(d_module, d_progress, 'No advance found.');
941 PO_LOG.proc_return(d_module, 'NULL');
942 PO_LOG.proc_end(d_module);
943 END IF;
944 RETURN NULL;
945 WHEN OTHERS THEN
946 IF (PO_LOG.d_exc) THEN
947 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
948 PO_LOG.proc_end(d_module);
949 END IF;
950 RAISE;
951 END get_advance_amount;
952
953 END PO_COMPLEX_WORK_PVT;
[Home] [Help]