1 PACKAGE BODY AR_IDEP_UTILS AS
2 /* $Header: ARDEPUTB.pls 120.2 2005/08/12 13:01:05 rsinthre noship $ */
3
4
5 /*========================================================================
6 | PUBLIC function get_course_description
7 |
8 | DESCRIPTION
9 | function which returns the description of a course which is an item in
10 | an invoice against a deposit.
11 |
12 | PSEUDO CODE/LOGIC
13 |
14 | PARAMETERS
15 | p_line_id the line number of the invoice
16 | p_uom the unit of measure for the line item
17 |
18 | RETURNS
19 | Description of the line item
20 | Output string will contain activity name, student name, event start date,
21 | event end date and event title saperated by comma (",") for UOM=ENR.
22 | The string will not contain student name if student name is NULL for UOM=ENR.
23 |
24 | Output string will contain activity name, max attendee, event start date,
25 | event end date, event title saperated by comma (",") for UOM=EVT.
26 | The string will not contain student name for UOM=EVT.
27 |
28 | Output string x_description will not contain contact name for ENR and EVT.
29 |
30 | KNOWN ISSUES
31 |
32 | NOTES
33 |
34 | MODIFICATION HISTORY
35 | Date Author Description of Changes
36 | 20-Jun-2001 Krishnakumar Menon Created
37 *=======================================================================*/
38
39 FUNCTION get_course_description(pn_line_id IN Number,
40 pv_uom IN Varchar2) RETURN VARCHAR2 IS
41
42 l_description VARCHAR2(360);
43 l_course_end_date DATE;
44 l_return_status VARCHAR2(240);
45 BEGIN
46
47 OTA_UTILITY.GET_DESCRIPTION (p_line_id => pn_line_id,
48 p_uom => pv_uom,
49 x_description => l_description,
50 x_course_end_date => l_course_end_date,
51 x_return_status => l_return_status);
52 RETURN l_description;
53 END;
54
55 /*========================================================================
56 | PUBLIC function get_reserved_commitment_amt
57 |
58 | DESCRIPTION
59 | function which returns the reserved amount for a given commitment/deposit.
60 |
61 | PSEUDO CODE/LOGIC
62 |
63 | PARAMETERS
64 | p_customer_trx_id The deposit identifier
65 |
66 | RETURNS
67 | The reserved amount
68 |
69 | KNOWN ISSUES
70 |
71 | NOTES
72 |
73 | MODIFICATION HISTORY
74 | Date Author Description of Changes
75 | 13-Dec-2001 krmenon Created
76 | 02-May-2002 krmenon Replaces SQL with call to OM api
77 *=======================================================================*/
78 FUNCTION get_reserved_commitment_amt (p_customer_trx_id in NUMBER) RETURN NUMBER IS
79 l_reserved_amount NUMBER := 0;
80 BEGIN
81
82 l_reserved_amount := OE_PAYMENTS_UTIL.get_uninvoiced_commitment_bal(p_customer_trx_id);
83
84 RETURN nvl(l_reserved_amount,0);
85 END;
86
87
88 /*========================================================================
89 | PUBLIC function get_applied_commitment_amt
90 |
91 | DESCRIPTION
92 | function which returns the applied amount for a given commitment/deposit.
93 |
94 | PSEUDO CODE/LOGIC
95 |
96 | PARAMETERS
97 | p_customer_trx_id The deposit identifier
98 |
99 | RETURNS
100 | The applied amount
101 |
102 | KNOWN ISSUES
103 |
104 | NOTES
105 |
106 | MODIFICATION HISTORY
107 | Date Author Description of Changes
108 | 02-May-2002 krmenon Created
109 *=======================================================================*/
110 FUNCTION get_applied_commitment_amt (p_customer_trx_id in NUMBER) RETURN NUMBER IS
111
112 l_commitment_class ra_cust_trx_types.type%type;
113 l_currency_code ra_customer_trx_all.invoice_currency_code%type;
114 l_applied_amount NUMBER := 0;
115 l_invoiced_amount NUMBER := 0;
116 l_credit_memo_amount NUMBER := 0;
117
118 BEGIN
119
120 --
121 -- Determine the type of transaction
122 --
123 SELECT type.type,
124 trx.invoice_currency_code
125 INTO l_commitment_class,
126 l_currency_code
127 FROM ra_customer_trx_all trx,
128 ra_cust_trx_types_all type
129 WHERE trx.customer_trx_id = p_customer_trx_id
130 AND trx.cust_trx_type_id = type.cust_trx_type_id
131 AND trx.org_id = type.org_id
132 AND type.type IN ('DEP','GUAR');
133
134
135
136 /*-------------------------------------------+
137 | If the commitment type is for a DEPOSIT, |
138 | then add in commitment adjustments |
139 +-------------------------------------------*/
140
141 IF ( l_commitment_class = 'DEP' ) THEN
142
143 --
144 -- Get the Adjustments
145 --
146 SELECT ( NVL(SUM( ADJ.AMOUNT),0) * -1)
147 INTO l_invoiced_amount
148 FROM ra_customer_trx_all trx,
149 ra_cust_trx_types_all type,
150 ar_adjustments_all adj
151 WHERE trx.cust_trx_type_id = type.cust_trx_type_id
152 AND trx.org_id = type.org_id
153 AND trx.initial_customer_trx_id = p_customer_trx_id
154 AND trx.complete_flag = 'Y'
155 AND adj.adjustment_type = 'C'
156 AND type.type IN ('INV', 'CM')
157 AND adj.org_id = trx.org_id
158 AND adj.customer_trx_id = DECODE(type.type,
159 'INV', trx.customer_trx_id,
160 'CM', trx.previous_customer_trx_id)
161 AND NVL( adj.subsequent_trx_id, -111) = DECODE(type.type,
162 'INV', -111,
166 -- Get the Credit Memos against the Commitment
163 'CM', trx.customer_trx_id) ;
164
165 --
167 --
168 SELECT NVL(SUM(-1 * line.extended_amount),0)
169 INTO l_credit_memo_amount
170 FROM ra_customer_trx_all trx,
171 ra_customer_trx_lines_all line
172 WHERE trx.customer_trx_id = line.customer_trx_id
173 AND trx.org_id = line.org_id
174 AND trx.previous_customer_trx_id = p_customer_trx_id
175 AND trx.complete_flag = 'Y';
176
177
178 ELSE -- Guarantee case
179
180 SELECT ( NVL( SUM(amount_line_items_original), 0)
181 -
182 NVL( SUM(amount_due_remaining), 0))
183 INTO l_invoiced_amount
184 FROM ar_payment_schedules_all
185 WHERE customer_trx_id = p_customer_trx_id;
186
187
188 /*------------------------------------------------------------+
189 | We do not want to adjust the commitment balance by the |
190 | amount of any manual adjustments against the commitment. |
191 | The following statement backs out these manual |
192 | adjustments from the commitment balance. |
193 +------------------------------------------------------------*/
194
195 SELECT NVL( SUM( amount ), 0)
196 INTO l_credit_memo_amount
197 FROM ar_adjustments_all
198 WHERE customer_trx_id = p_customer_trx_id
199 AND adjustment_type <> 'C';
200
201 END IF; -- end Guarantee case
202
203
204 l_applied_amount := l_invoiced_amount + l_credit_memo_amount;
205
206 RETURN nvl(l_applied_amount,0);
207
208 END;
209
210
211 END AR_IDEP_UTILS;
[Home] [Help]