1 PACKAGE BODY OE_ACCOUNTING AS
2 /* $Header: OEXACCTB.pls 115.2 1999/11/15 15:22:36 pkm ship $ */
3
4 -----------------------------------------------------------------
5 --
6 -- Receivables Functions
7 --
8 ------------------------------------------------------------------
9 --
10 ------------------------------------------------------------------
11 --
12 -- Function Name: Get_Uninvoiced_Commitment_Bal
13 -- Parameter: p_customer_trx_id.
14 -- Return : Number.
15 --
16 -- The purpose of this function is to calculate the uninvoiced
17 -- commitment balance for a given commitment_id, in Order Entry.
18 -- This function is called by Account Receivables.
19 -- This function is provided by OE for interoperability purpose
20 -- between old OE and new OE.
21 --
22 -- total uninvoiced commitment balance =
23 -- total of order lines associated with one particular
24 -- commitment that are not interfaced to AR yet.
25 --
26 -- pseudo code:
27 --
28 -- if new OE then
29 --
30 -- select sum of (ordered_quantity)* unit_selling_price
31 -- from oe_order_lines
32 -- where commitement_id = p_customer_trx_id
33 -- and line is not RETURN line
34 -- and invoice_interface_status_code <> 'YES' -- never been AR interfaced
35 --
36 -- else old OE
37 --
38 -- select sum of ((ordered_quantity - cancelled_quantity -
39 -- invoiced_quantity) * selling_price)
40 -- from so_lines
41 -- where commitment_id = p_customer_trx_id
42 -- and line_type is regular or detail
43 --
44 -- end if;
45 --
46 --------------------------------------------------------------------
47
48 FUNCTION Get_Uninvoiced_Commitment_Bal
49 ( p_customer_trx_id IN NUMBER
50 )
51 RETURN NUMBER IS
52
53 l_uninv_commitment_bal NUMBER := 0;
54
55 BEGIN
56
57 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
58
59 SELECT
60 NVL(SUM( NVL(ordered_quantity,0) * NVL(unit_selling_price,0)),0)
61 INTO l_uninv_commitment_bal
62 FROM oe_order_lines
63 WHERE commitment_id = p_customer_trx_id
64 AND NVL(line_category_code,'STANDARD') <> 'RETURN'
65 AND NVL(invoice_interface_status_code,'NO') <> 'YES';
66
67 ELSE
68
69 SELECT
70 NVL( SUM( ( NVL( ordered_quantity, 0 ) -
71 NVL( cancelled_quantity, 0 ) -
72 NVL( invoiced_quantity, 0 )
73 ) *
74 NVL( selling_price, 0 )
75 ), 0 )
76 INTO l_uninv_commitment_bal
77 FROM so_lines
78 WHERE commitment_id = p_customer_trx_id
79 AND line_type_code IN ( 'REGULAR', 'DETAIL');
80
81
82 END IF;
83
84 RETURN (l_uninv_commitment_bal);
85
86 END Get_Uninvoiced_Commitment_Bal;
87
88
89 END OE_ACCOUNTING;