[Home] [Help]
PACKAGE BODY: APPS.PO_COMPLEX_WORK_PVT
Source
1 PACKAGE BODY PO_COMPLEX_WORK_PVT AS
2 -- $Header: PO_COMPLEX_WORK_PVT.plb 120.3 2005/09/18 22:48:34 spangulu noship $
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
123 IF (l_payment_types_tbl(i) = g_payment_type_MILESTONE) THEN
124
125 x_milestone_allowed_flag := 'Y';
126
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.
273 -- FALSE: Any document with this style uses actuals progress payments.
274 --End of Comments
275 -------------------------------------------------------------------------------
276 FUNCTION is_financing_payment_style(p_style_id IN NUMBER) RETURN BOOLEAN
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
312 , x_status => l_status
309 , x_style_name => l_style_name
310 , x_style_description => l_style_desc
311 , x_style_type => l_style_type
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 l_style_id PO_HEADERS_ALL.style_id%TYPE;
377 l_is_complex_po BOOLEAN;
378
379 BEGIN
380
381 d_progress := 0;
382 IF (PO_LOG.d_proc) THEN
383 PO_LOG.proc_begin(d_module);
384 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
385 END IF;
386
387 d_progress := 10;
388
389 SELECT poh.style_id
390 INTO l_style_id
391 FROM po_headers_all poh
392 WHERE poh.po_header_id = p_po_header_id;
393
394 d_progress := 20;
395
396 IF (l_style_id IS NOT NULL) THEN
397
398 d_progress := 30;
399 l_is_complex_po := is_complex_work_style(p_style_id => l_style_id);
400
401 ELSE
402
403 d_progress := 40;
404 IF (PO_LOG.d_stmt) THEN
405 PO_LOG.stmt(d_module, d_progress, 'Style is NULL!');
406 END IF;
407
408 l_is_complex_po := FALSE;
409
410 END IF;
411
412 IF (PO_LOG.d_proc) THEN
413 PO_LOG.proc_return(d_module, l_is_complex_po);
414 PO_LOG.proc_end(d_module);
415 END IF;
416
417 RETURN l_is_complex_po;
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 IF (PO_LOG.d_exc) THEN
422 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
423 PO_LOG.proc_end(d_module);
424 END IF;
425 RAISE;
426 END is_complex_work_po;
427
428
429 ------------------------------------------------------------------------------
430 --Start of Comments
431 --Name: is_financing_po
432 --Pre-reqs:
433 -- None
434 --Modifies:
435 -- None.
436 --Locks:
437 -- None.
438 --Function:
439 -- This function will determine whether a PO uses financing (PREPAYMENT) or
440 -- actuals (STANDARD) payitems.
441 -- Note: This function will not first check if the PO is a complex work PO.
442 --Parameters:
443 --IN:
444 -- p_po_header_id
445 -- Header ID of the PO to check financing vs. actuals for.
446 --RETURNS:
447 -- TRUE: The PO uses financing pay items
448 -- FALSE: The PO uses actuals pay items
449 --End of Comments
450 -------------------------------------------------------------------------------
451 FUNCTION is_financing_po(p_po_header_id IN NUMBER) RETURN BOOLEAN
452 IS
453
454 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.is_financing_po';
455 d_progress NUMBER;
456
457 l_style_id PO_HEADERS_ALL.style_id%TYPE;
458 l_is_financing_po BOOLEAN;
459
460 BEGIN
461
462 d_progress := 0;
463 IF (PO_LOG.d_proc) THEN
464 PO_LOG.proc_begin(d_module);
465 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
466 END IF;
467
468 d_progress := 10;
469
470 SELECT poh.style_id
471 INTO l_style_id
472 FROM po_headers_all poh
473 WHERE poh.po_header_id = p_po_header_id;
474
475 d_progress := 20;
476
477 IF (l_style_id IS NOT NULL) THEN
478
479 d_progress := 30;
480 l_is_financing_po := is_financing_payment_style(p_style_id => l_style_id);
481
482 ELSE
483
484 d_progress := 40;
485 IF (PO_LOG.d_stmt) THEN
486 PO_LOG.stmt(d_module, d_progress, 'Style is NULL!');
487 END IF;
488
489 l_is_financing_po := FALSE;
490
491 END IF;
492
493 IF (PO_LOG.d_proc) THEN
497
494 PO_LOG.proc_return(d_module, l_is_financing_po);
495 PO_LOG.proc_end(d_module);
496 END IF;
498 RETURN l_is_financing_po;
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 IF (PO_LOG.d_exc) THEN
503 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
504 PO_LOG.proc_end(d_module);
505 END IF;
506 RAISE;
507 END is_financing_po;
508
509
510 ------------------------------------------------------------------------------
511 --Start of Comments
512 --Name: get_default_payitem_info
513 --Pre-reqs:
514 -- None
515 --Modifies:
516 -- None.
517 --Locks:
518 -- None.
519 --Function:
520 -- This procedure calculates the default payment_type, quantity, amount
521 -- and price from line information. It relies on the style at the
522 -- header level to determine the default payment type.
523 -- This procedure returns values that can be used to default the first pay item
524 -- created for a line.
525 --Parameters:
526 --IN:
527 -- p_po_header_id
528 -- Header ID for the PO to get default payment information for
529 -- Style id should already be populated in the headers table
530 -- p_po_line_id
531 -- Line ID for the PO line that the payitem belongs to
532 -- p_line_value_basis
533 -- Value Basis (order_type_lookup_code) of the line that the
534 -- payitem belongs to. This should be one of: 'FIXED PRICE', 'QUANTITY'
535 -- p_line_qty
536 -- Quantity at the line level
537 -- p_line_amt
538 -- Amount at the line level
539 -- p_price
540 -- Price at the line level
541 --OUT:
542 -- x_payment_type
543 -- Default payment type, as determined by the style settings
544 -- One of: g_payment_type_<>, where <> = MILESTONE, RATE, LUMPSUM
545 -- x_payitem_qty
546 -- Default quantity at the payitem level
547 -- x_payitem_amt
548 -- Default amount at the payitem level
549 -- x_payitem_price
550 -- Default price (price_override) at the payitem level
551 --End of Comments
552 -------------------------------------------------------------------------------
553 PROCEDURE get_default_payitem_info(
554 p_po_header_id IN NUMBER
555 , p_po_line_id IN NUMBER
556 , p_line_value_basis IN VARCHAR2
557 , p_line_matching_basis IN VARCHAR2
558 , p_line_qty IN NUMBER
559 , p_line_amt IN NUMBER
560 , p_line_price IN NUMBER
561 , x_payment_type OUT NOCOPY VARCHAR2
562 , x_payitem_qty OUT NOCOPY NUMBER
563 , x_payitem_amt OUT NOCOPY NUMBER
564 , x_payitem_price OUT NOCOPY NUMBER
565 )
566 IS
567
568 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.get_default_payitem_info';
569 d_progress NUMBER;
570
571 l_style_id PO_HEADERS.style_id%TYPE;
572 l_is_complex_flag VARCHAR2(1);
573 l_is_financing_flag VARCHAR2(1);
574 l_retainage_flag VARCHAR2(1);
575 l_advance_flag VARCHAR2(1);
576 l_milestone_flag VARCHAR2(1);
577 l_lumpsum_flag VARCHAR2(1);
578 l_rate_flag VARCHAR2(1);
579
580 BEGIN
581
582 d_progress := 0;
583 IF (PO_LOG.d_proc) THEN
584 PO_LOG.proc_begin(d_module);
585 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
586 PO_LOG.proc_begin(d_module, 'p_po_line_id', p_po_line_id);
587 PO_LOG.proc_begin(d_module, 'p_line_value_basis', p_line_value_basis);
588 PO_LOG.proc_begin(d_module, 'p_line_matching_basis', p_line_matching_basis);
589 PO_LOG.proc_begin(d_module, 'p_line_qty', p_line_qty);
590 PO_LOG.proc_begin(d_module, 'p_line_amt', p_line_amt);
591 PO_LOG.proc_begin(d_module, 'p_line_price', p_line_price);
592 END IF;
593
594 d_progress := 10;
595
596 SELECT poh.style_id
597 INTO l_style_id
598 FROM po_headers_all poh
599 WHERE poh.po_header_id = p_po_header_id;
600
601 d_progress := 20;
602
603 get_payment_style_settings(
604 p_style_id => l_style_id
605 , x_complex_work_flag => l_is_complex_flag
606 , x_financing_payments_flag => l_is_financing_flag
607 , x_retainage_allowed_flag => l_retainage_flag
608 , x_advance_allowed_flag => l_advance_flag
609 , x_milestone_allowed_flag => l_milestone_flag
610 , x_lumpsum_allowed_flag => l_lumpsum_flag
611 , x_rate_allowed_flag => l_rate_flag
612 );
613
614 d_progress := 30;
615
616 x_payment_type := NULL;
617 x_payitem_qty := NULL;
618 x_payitem_amt := NULL;
619 x_payitem_price := NULL;
620
621 d_progress := 40;
622
623 IF (l_is_complex_flag = 'Y') THEN
624
625 IF (p_line_value_basis = 'QUANTITY') THEN
626
627 x_payment_type := g_payment_type_MILESTONE;
628 x_payitem_price := p_line_price;
629 x_payitem_qty := p_line_qty;
630
631 ELSIF (p_line_value_basis = 'FIXED PRICE') THEN
632
633 IF (l_lumpsum_flag = 'Y') THEN
634
635 x_payment_type := g_payment_type_LUMPSUM;
636 x_payitem_amt := p_line_amt;
637
638 ELSIF (l_rate_flag = 'Y') THEN
639
640 x_payment_type := g_payment_type_RATE;
641 x_payitem_qty := 1;
642 x_payitem_price := p_line_amt;
643
644 ELSIF (l_milestone_flag = 'Y') THEN
645
646 x_payment_type := g_payment_type_MILESTONE;
647 x_payitem_amt := p_line_amt;
648
649 END IF; -- l_lumpsum_flag = 'Y'
650
651 END IF; -- p_line_value_basis = ...
652
656 PO_LOG.proc_end(d_module, 'x_payment_type', x_payment_type);
653 END IF; -- if l_is_complex_flag = 'Y'
654
655 IF (PO_LOG.d_proc) THEN
657 PO_LOG.proc_end(d_module, 'x_payitem_qty', x_payitem_qty);
658 PO_LOG.proc_end(d_module, 'x_payitem_amt', x_payitem_amt);
659 PO_LOG.proc_end(d_module, 'x_payitem_price', x_payitem_price);
660 PO_LOG.proc_end(d_module);
661 END IF;
662
663 EXCEPTION
664 WHEN OTHERS THEN
665 IF (PO_LOG.d_exc) THEN
666 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
667 PO_LOG.proc_end(d_module);
668 END IF;
669 RAISE;
670 END get_default_payitem_info;
671
672
673 ------------------------------------------------------------------------------
674 --Start of Comments
675 --Name: get_advanace_amount
676 --Pre-reqs:
677 -- None
678 --Modifies:
679 -- None.
680 --Locks:
681 -- None.
682 --Function:
683 -- This function will return the advance amount of a line, as stored
684 -- within its advance payitem.
685 --Parameters:
686 --IN:
687 -- p_po_line_id
688 -- Line id of the line to get advance amount for.
689 -- p_doc_revision_num
690 -- If checking for archived advance amount, pass in the document's revision
691 -- number. If checking main tables, pass NULL (default).
692 -- p_which_tables
693 -- Either 'MAIN' for current line or 'ARCHIVE' for line on archived doc.
694 --RETURNS:
695 -- Advance amount, or NULL if no advance payitem exists.
696 --End of Comments
697 -------------------------------------------------------------------------------
698 FUNCTION get_advance_amount(
699 p_po_line_id IN NUMBER
700 , p_doc_revision_num IN NUMBER DEFAULT NULL
701 , p_which_tables IN VARCHAR2 DEFAULT 'MAIN'
702 ) RETURN NUMBER
703 IS
704
705 d_module VARCHAR2(70) := 'po.plsql.PO_COMPLEX_WORK_PVT.get_advance_amount';
706 d_progress NUMBER;
707
708 l_advance_amount PO_LINE_LOCATIONS_ALL.amount%TYPE;
709
710 BEGIN
711
712 d_progress := 0;
713 IF (PO_LOG.d_proc) THEN
714 PO_LOG.proc_begin(d_module);
715 PO_LOG.proc_begin(d_module, 'p_po_line_id', p_po_line_id);
716 PO_LOG.proc_begin(d_module, 'p_doc_revision_num', p_doc_revision_num);
717 PO_LOG.proc_begin(d_module, 'p_which_tables', p_which_tables);
718 END IF;
719
720 IF (p_which_tables = 'MAIN') THEN
721
722 d_progress := 20;
723
724 SELECT poll.amount
725 INTO l_advance_amount
726 FROM po_line_locations_all poll
727 WHERE poll.po_line_id = p_po_line_id
728 AND poll.payment_type = 'ADVANCE';
729
730 ELSE
731
732 d_progress := 30;
733
734 SELECT polla.amount
735 INTO l_advance_amount
736 FROM po_line_locations_archive_all polla
737 WHERE polla.po_line_id = p_po_line_id
738 AND polla.revision_num =
739 (
740 SELECT MAX(polla2.revision_num)
741 FROM po_line_locations_archive_all polla2
742 WHERE polla2.line_location_id = polla.line_location_id
743 AND polla2.revision_num <= p_doc_revision_num
744 )
745 AND polla.payment_type = 'ADVANCE';
746
747 END IF;
748
749 IF (PO_LOG.d_proc) THEN
750 PO_LOG.proc_return(d_module, l_advance_amount);
751 PO_LOG.proc_end(d_module);
752 END IF;
753
754 RETURN l_advance_amount;
755
756 EXCEPTION
757 WHEN NO_DATA_FOUND THEN
758 IF (PO_LOG.d_proc) THEN
759 PO_LOG.stmt(d_module, d_progress, 'No advance found.');
760 PO_LOG.proc_return(d_module, 'NULL');
761 PO_LOG.proc_end(d_module);
762 END IF;
763 RETURN NULL;
764 WHEN OTHERS THEN
765 IF (PO_LOG.d_exc) THEN
766 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
767 PO_LOG.proc_end(d_module);
768 END IF;
769 RAISE;
770 END get_advance_amount;
771
772 END PO_COMPLEX_WORK_PVT;