1 PACKAGE BODY IBY_AR_UTILS AS
2 /* $Header: ibyarutb.pls 120.7.12010000.3 2008/11/13 14:15:17 lmallick ship $*/
3
4
5 --
6 -- NOTE
7 -- For performance reasons this function returns a concatenation
8 -- of DocumentReceivable and DocumentReceivableCount
9 --
10 FUNCTION get_document_receivable
11 (
12 p_tangibleid IN iby_trxn_summaries_all.tangibleid%TYPE,
13 p_trxntypeid IN iby_trxn_summaries_all.trxntypeid%TYPE,
14 p_card_data_level IN iby_trxn_core.card_data_level%TYPE,
15 p_instrument_type IN iby_trxn_summaries_all.instrtype%TYPE
16 )
17 RETURN XMLType
18 IS
19
20 BEGIN
21
22 -- Stub out the code
23 RETURN NULL;
24
25 END get_document_receivable;
26
27 -- Overloaded form of the earlier API. This is transaction
28 -- extension driven. The earlier function has been obsoleted.
29 --
30 -- NOTE
31 -- For performance reasons this function returns a concatenation
32 -- of DocumentReceivable and DocumentReceivableCount
33 --
34 FUNCTION get_document_receivable
35 (
36 p_extension_id IN iby_trxn_summaries_all.initiator_extension_id%TYPE,
37 p_trxntypeid IN iby_trxn_summaries_all.trxntypeid%TYPE,
38 p_card_data_level IN iby_trxn_core.card_data_level%TYPE,
39 p_instrument_type IN iby_trxn_summaries_all.instrtype%TYPE
40 )
41 RETURN XMLType
42 IS
43 l_doc_rec XMLType;
44
45 CURSOR l_doc_rec_csr
46 (c_extension_id iby_trxn_summaries_all.initiator_extension_id%TYPE) IS
47 SELECT
48 XMLConcat(XMLAgg(document_headers),
49 XMLElement("DocumentReceivableCount",count(trxn_extension_id)))
50 FROM iby_xml_fndcpt_doc_headers_v
51 WHERE trxn_extension_id = c_extension_id;
52 BEGIN
53
54 -- only supported for capture trxn's
55 IF (NOT p_trxntypeid IN (8,9,100)) THEN
56 RETURN null;
57 END IF;
58
59 IF (l_doc_rec_csr%ISOPEN) THEN
60 CLOSE l_doc_rec_csr;
61 END IF;
62
63 OPEN l_doc_rec_csr(p_extension_id);
64 FETCH l_doc_rec_csr INTO l_doc_rec;
65 IF (l_doc_rec_csr%NOTFOUND) THEN
66 l_doc_rec := NULL;
67 END IF;
68 CLOSE l_doc_rec_csr;
69 RETURN l_doc_rec;
70
71 END get_document_receivable;
72
73 PROCEDURE call_get_payment_info(
74 p_payment_server_order_num IN
75 ar_cash_receipts.payment_server_order_num%TYPE,
76 x_customer_trx_id OUT NOCOPY
77 ar_receivable_applications.customer_trx_id%TYPE,
78 x_return_status OUT NOCOPY VARCHAR2,
79 x_msg_count OUT NOCOPY NUMBER,
80 x_msg_data OUT NOCOPY VARCHAR2
81 ) IS
82
83 l_receipt_header ar_cash_receipts%ROWTYPE;
84 l_app_type ar_receivable_applications.status%TYPE :='APP';
85 l_app_tbl_type AR_PUBLIC_UTILS.application_tbl_type;
86 l_app_rec_type ar_receivable_applications%ROWTYPE;
87
88 BEGIN
89 x_customer_trx_id := 0;
90 x_return_status := FND_API.G_RET_STS_SUCCESS;
91
92 IF p_payment_server_order_num IS NOT NULL THEN
93
94 AR_PUBLIC_UTILS.get_payment_info(
95 p_payment_server_order_num,
96 l_app_type,
97 l_receipt_header,
98 l_app_tbl_type,
99 x_return_status,
100 x_msg_count,
101 x_msg_data);
102
103 IF l_app_tbl_type.COUNT = 1 THEN
104 x_customer_trx_id := l_app_tbl_type(1).applied_customer_trx_id;
105 END IF;
106
107 END IF;
108
109 EXCEPTION
110 WHEN others THEN
111 x_msg_count := 1;
112 x_return_status := FND_API.G_RET_STS_ERROR;
113 x_msg_data := 'IBY_AR_UTILS.CALL_GET_PAYMENT_INFO ERROR: ' || x_msg_data;
114 END;
115
116 FUNCTION call_get_payment_info
117 (p_payment_server_order_num IN
118 ar_cash_receipts.payment_server_order_num%TYPE)
119 RETURN ar_receivable_applications.customer_trx_id%TYPE
120 IS
121 l_trx_id ar_receivable_applications.customer_trx_id%TYPE;
122 l_ret_status VARCHAR2(2000);
123 l_msg_count NUMBER;
124 l_msg_data VARCHAR2(5000);
125 BEGIN
126
127 call_get_payment_info
128 (p_payment_server_order_num,l_trx_id,l_ret_status,l_msg_count,l_msg_data);
129 RETURN l_trx_id;
130
131 END call_get_payment_info;
132
133 FUNCTION get_order_freight_amount(p_customer_trx_id IN
134 ar_invoice_header_v.customer_trx_id%TYPE)
135 RETURN NUMBER
136 IS
137 l_freight_amount NUMBER := 0;
138
139 CURSOR c_header_freight
140 (ci_cust_trx_id ar_invoice_header_v.customer_trx_id%TYPE)
141 IS
142 SELECT extended_amount
143 FROM ar_invoice_lines_v
144 WHERE (link_to_cust_trx_line_id IS NULL)
145 AND (line_type = 'FREIGHT')
146 AND (customer_trx_id = ci_cust_trx_id);
147
148 CURSOR c_freight_total
149 (ci_cust_trx_id ar_invoice_header_v.customer_trx_id%TYPE)
150 IS
151 SELECT SUM(extended_amount)
152 FROM ar_invoice_lines_v
153 WHERE
154 (line_type = 'FREIGHT')
155 AND (customer_trx_id = ci_cust_trx_id);
156
157 BEGIN
158
159 IF (c_header_freight%ISOPEN) THEN
160 CLOSE c_header_freight;
161 END IF;
162 IF (c_freight_total%ISOPEN) THEN
163 CLOSE c_freight_total;
164 END IF;
165
166 OPEN c_header_freight(p_customer_trx_id);
167 FETCH c_header_freight INTO l_freight_amount;
168 IF (c_header_freight%NOTFOUND) THEN
169 l_freight_amount:=0;
170 END IF;
171 CLOSE c_header_freight;
172
173 IF (l_freight_amount>0) THEN
174 RETURN l_freight_amount;
175 END IF;
176 --
177 -- if no header-level amount exists, then take
178 -- the sum of all line-level freight charges
179 --
180 OPEN c_freight_total(p_customer_trx_id);
181 FETCH c_freight_total INTO l_freight_amount;
182 IF (c_freight_total%NOTFOUND) THEN
183 l_freight_amount := 0;
184 END IF;
185 CLOSE c_freight_total;
186
187 RETURN NVL(l_freight_amount,0);
188 END get_order_freight_amount;
189
190 FUNCTION get_order_tax_amount
191 (p_customer_trx_id IN ar_invoice_header_v.customer_trx_id%TYPE,
192 p_tax_type IN VARCHAR2)
193 RETURN NUMBER
194 IS
195 l_tax_total NUMBER := 0;
196
197 CURSOR c_line_items
198 (ci_cust_trx_id ar_invoice_header_v.customer_trx_id%TYPE)
199 IS
200 SELECT customer_trx_line_id
201 FROM ar_invoice_lines_v
202 WHERE
203 ( line_type = 'LINE')
204 AND (customer_trx_id = ci_cust_trx_id);
205
206 BEGIN
207
208 FOR order_line IN c_line_items(p_customer_trx_id) LOOP
209 l_tax_total := get_line_tax_amount(p_customer_trx_id,
210 order_line.customer_trx_line_id,
211 p_tax_type);
212 END LOOP;
213
214 RETURN l_tax_total;
215 END get_order_tax_amount;
216
217 FUNCTION get_order_amount
218 (p_customer_trx_id IN ar_invoice_header_v.customer_trx_id%TYPE)
219 RETURN NUMBER
220 IS
221 l_order_total NUMBER := 0;
222
223 CURSOR c_line_items
224 (ci_cust_trx_id ar_invoice_header_v.customer_trx_id%TYPE)
225 IS
226 SELECT extended_amount
227 FROM ar_invoice_lines_v
228 WHERE
229 ( line_type = 'LINE')
230 AND (customer_trx_id = ci_cust_trx_id);
231 BEGIN
232
233 FOR order_line IN c_line_items(p_customer_trx_id) LOOP
234 l_order_total := l_order_total + NVL(order_line.extended_amount,0);
235 END LOOP;
236
237 RETURN l_order_total;
238 END get_order_amount;
239
240 FUNCTION get_line_tax_amount
241 (p_customer_trx_id IN ar_invoice_header_v.customer_trx_id%TYPE,
242 p_customer_trx_line_id IN ar_invoice_lines_v.customer_trx_line_id%TYPE,
243 p_tax_type IN VARCHAR2)
244 RETURN NUMBER
245 IS
246
247 l_sales_tax_total NUMBER := 0;
248 l_vat_tax_total NUMBER := 0;
249
250 CURSOR c_tax_items
251 (ci_cust_trx_id ar_invoice_lines_v.customer_trx_id%TYPE,
252 ci_cust_trx_line_id ar_invoice_lines_v.link_to_cust_trx_line_id%TYPE)
253 IS
254 SELECT extended_amount, location_rate_id, location_segment_id
255 FROM ar_invoice_lines_v
256 WHERE
257 (line_type='TAX')
258 AND (customer_trx_id = ci_cust_trx_id)
259 AND (link_to_cust_trx_line_id = ci_cust_trx_line_id);
260
261 BEGIN
262
263 FOR tax_line IN c_tax_items(p_customer_trx_id,p_customer_trx_line_id) LOOP
264 IF (NOT tax_line.location_rate_id IS NULL)
265 OR (NOT tax_line.location_segment_id IS NULL)
266 THEN
267 l_sales_tax_total := l_sales_tax_total + NVL(tax_line.extended_amount,0);
268 ELSE
269 l_vat_tax_total := l_vat_tax_total + NVL(tax_line.extended_amount,0);
270 END IF;
271 END LOOP;
272
273 IF (p_tax_type = G_TAX_TYPE_SALES) THEN
274 RETURN l_sales_tax_total;
275 ELSIF (p_tax_type = G_TAX_TYPE_VAT) THEN
276 RETURN l_vat_tax_total;
277 ELSE
278 RETURN 0;
279 END IF;
280
281 END get_line_tax_amount;
282
283 FUNCTION get_line_tax_rate
284 (p_customer_trx_id IN ar_invoice_header_v.customer_trx_id%TYPE,
285 p_customer_trx_line_id IN ar_invoice_lines_v.customer_trx_line_id%TYPE,
286 p_tax_type IN VARCHAR2)
287 RETURN NUMBER
288 IS
289 l_sales_rate_total NUMBER := 0;
290 l_vat_rate_total NUMBER := 0;
291
292 l_sales_count NUMBER := 0;
293 l_vat_count NUMBER := 0;
294
295 CURSOR c_tax_items
296 (ci_cust_trx_id ar_invoice_lines_v.customer_trx_id%TYPE,
297 ci_cust_trx_line_id ar_invoice_lines_v.link_to_cust_trx_line_id%TYPE)
298 IS
299 SELECT location_rate_id, location_segment_id, tax_rate
300 FROM ar_invoice_lines_v
301 WHERE
302 (line_type='TAX')
303 AND (customer_trx_id = ci_cust_trx_id)
304 AND (link_to_cust_trx_line_id = ci_cust_trx_line_id);
305
306 BEGIN
307
308 FOR tax_line IN c_tax_items(p_customer_trx_id,p_customer_trx_line_id) LOOP
309 IF (NOT tax_line.location_rate_id IS NULL)
310 OR (NOT tax_line.location_segment_id IS NULL)
311 THEN
312 l_sales_rate_total := l_sales_rate_total + NVL(tax_line.tax_rate,0);
313 l_sales_count := l_sales_count + 1;
314 ELSE
315 l_vat_rate_total := l_vat_rate_total + NVL(tax_line.tax_rate,0);
316 l_vat_count := l_vat_count + 1;
317 END IF;
318 END LOOP;
319
320 IF (p_tax_type = G_TAX_TYPE_SALES) THEN
321 RETURN (l_sales_rate_total/GREATEST(l_sales_count,1));
322 ELSIF (p_tax_type = G_TAX_TYPE_VAT) THEN
323 RETURN (l_vat_rate_total/GREATEST(l_vat_count,1));
324 ELSE
325 RETURN 0;
326 END IF;
327
328 END get_line_tax_rate;
329
330 -- Return: The Authorization Flag for the given Transaction Extension Id
331 PROCEDURE get_authorization_status
332 (p_trxn_extension_id IN iby_fndcpt_tx_operations.trxn_extension_id%TYPE,
333 x_auth_flag OUT NOCOPY VARCHAR2)
334 IS
335 BEGIN
336 SELECT decode(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG
337 into x_auth_flag
338 FROM iby_trxn_summaries_all summ
339 , iby_fndcpt_tx_operations op
340 WHERE summ.transactionid = op.transactionid
341 AND reqtype = 'ORAPMTREQ'
342 AND status IN(0, 100)
343 AND trxntypeid IN(2, 3, 20)
344 AND op.trxn_extension_id = p_trxn_extension_id
345 AND summ.trxnmid = (SELECT MAX(trxnmid)
346 FROM iby_trxn_summaries_all summ1
347 , iby_fndcpt_tx_operations op1
348 WHERE summ1.transactionid = op1.transactionid
349 AND summ1.reqtype = 'ORAPMTREQ'
350 AND summ1.status IN(0, 100)
351 AND summ1.trxntypeid IN(2, 3, 20)
352 AND op1.trxn_extension_id = op.trxn_extension_id);
353 EXCEPTION
354 WHEN others THEN
355 x_auth_flag:='N';
356 END;
357
358 END IBY_AR_UTILS;
[Home] [Help]