DBA Data[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;