[Home] [Help]
PACKAGE BODY: APPS.IBY_AR_UTILS
Source
1 PACKAGE BODY IBY_AR_UTILS AS
2 /* $Header: ibyarutb.pls 120.19.12020000.5 2012/11/20 11:37:51 dhati 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
28 -- Overloaded form of the earlier API. This is transaction
29 -- extension driven. The earlier function has been obsoleted.
30 --
31 -- NOTE
32 -- For performance reasons this function returns a concatenation
33 -- of DocumentReceivable and DocumentReceivableCount
34 --
35 -- Bug # 8301765
36 -- Added input parameter p_source_view : Name of the invoking view name.
37 FUNCTION get_document_receivable
38 (
39 p_extension_id IN iby_trxn_summaries_all.initiator_extension_id%TYPE,
40 p_trxntypeid IN iby_trxn_summaries_all.trxntypeid%TYPE,
41 p_card_data_level IN iby_trxn_core.card_data_level%TYPE,
42 p_instrument_type IN iby_trxn_summaries_all.instrtype%TYPE,
43 p_source_view IN VARCHAR2
44 )
45 RETURN XMLType
46 IS
47 l_dbg_mod VARCHAR2(100) := 'IBY_AR_UTILS' || '.get_document_receivable(2)';
48 l_doc_rec XMLType;
49
50 /*
51 Bug Number: 9397208
52 For Performance reasons, iby_xml_fndcpt_doc_headers_v view has been replaced
53 with global temporary tables iby_ar_doc_header_gt and iby_ar_doc_lines_gt.
54 Data will be inserted into GTs before extract view call
55 */
56 CURSOR l_doc_rec_csr
57 (c_extension_id iby_trxn_summaries_all.initiator_extension_id%TYPE,
58 c_card_data_level iby_trxn_core.card_data_level%TYPE) IS
59 /*
60 SELECT
61 XMLConcat(XMLAgg(document_headers),
62 XMLElement("DocumentReceivableCount",count(trxn_extension_id)))
63 FROM iby_xml_fndcpt_doc_headers_v
64 WHERE trxn_extension_id = c_extension_id;
65 */
66 -- Bug# 12790543
67 -- Added XMLAgg to pick up all the inovoices which satisfy the criteria
68 -- Without XMLAgg only the first invoice gets picked up b'coz the cursor
69 -- uses a simple fetch
70 SELECT
71 XMLAgg(
72 XMLConcat(XMLElement("DocumentReceivable",
73 XMLElement("DocumentID", NVL(headergt.calling_app_doc_ref_number, headergt.doc_unique_ref)),
74 XMLElement("DocumentStatus",
75 XMLElement("Code", NULL),
76 XMLElement("Meaning", NULL)),
77 XMLElement("DocumentDate", to_char(headergt.document_date, 'YYYY-MM-DD"T"HH24:MI:SS')),
78 XMLElement("DocumentCreationDate", to_char(headergt.creation_date, 'YYYY-MM-DD"T"HH24:MI:SS')),
79 XMLForest(NULL AS "PaymentDueDate"),
80 XMLElement("DocumentType",
81 XMLElement("Code", headergt.document_type),
82 XMLElement("Meaning", NULL)),
83 XMLElement("DocumentDescription", headergt.document_description),
84 XMLElement("BRSignedFlag", headergt.br_signed_flag),
85 XMLElement("BRDraweeIssuedFlag", headergt.br_drawee_issued_flag),
86 XMLElement("TotalDocumentAmount",
87 XMLElement("Value", headergt.document_amount),
88 XMLElement("Currency",
89 XMLElement("Code", headergt.document_currency_code))),
90 XMLElement("PaymentAmount",
91 XMLElement("Value", headergt.settlement_amount),
92 XMLElement("Currency",
93 XMLElement("Code", headergt.document_currency_code))),
94 XMLElement("Charge",
95 XMLElement("Amount",
96 XMLElement("Value", nvl(headergt.freight_amount, 0)),
97 XMLElement("Currency",
98 XMLElement("Code", headergt.document_currency_code))),
99 XMLForest('FREIGHT' AS "ChargeType")),
100 XMLElement("Discount",
101 XMLElement("Amount",
102 XMLElement("Value", headergt.document_discount_earned),
103 XMLElement("Currency",
104 XMLElement("Code", headergt.document_currency_code))),
105 XMLForest(NULL AS "DiscountType")),
106 XMLElement("Tax",
107 XMLElement("Amount",
108 XMLElement("Value", nvl(headergt.local_tax_amount, 0) + nvl(headergt.national_tax_amount, 0)),
109 XMLElement("Currency",
110 XMLElement("Code", headergt.document_currency_code))),
111 XMLElement("RatePercent", NULL),
112 XMLForest('SALESTAX' AS "TaxType"),
113 XMLForest(NULL AS "TaxJurisdiction")),
114 XMLElement("Tax",
115 XMLElement("Amount",
116 XMLElement("Value", headergt.vat_tax_amount),
117 XMLElement("Currency",
118 XMLElement("Code", headergt.document_currency_code))),
119 XMLElement("RatePercent", NULL),
120 XMLForest('VAT' AS "TaxType"),
121 XMLForest(NULL AS "TaxJurisdiction")),
122 XMLElement("ShipmentOrigin",
123 XMLElement("AddressLine1", headergt.address1),
124 XMLForest(headergt.address2 AS "AddressLine2"),
125 XMLForest(headergt.address3 AS "AddressLine3"),
126 XMLElement("City", headergt.city),
127 XMLForest(headergt.county AS "County"),
128 XMLElement("State", headergt.state),
129 XMLElement("Country", headergt.country),
130 XMLElement("PostalCode", headergt.postal_code)),
131 XMLElement("ShipmentDestination",
132 XMLElement("AddressLine1", headergt.to_address1),
133 XMLForest(headergt.to_address2 AS "AddressLine2"),
134 XMLForest(headergt.to_address3 AS "AddressLine3"),
135 XMLElement("City", headergt.to_city),
136 XMLForest(headergt.to_county AS "County"),
137 XMLElement("State", headergt.to_state),
138 XMLElement("Country", headergt.to_country),
139 XMLElement("PostalCode", headergt.to_postal_code)),
140 CASE
141 WHEN(c_card_data_level = '3') THEN
142 (SELECT xmlagg(
143 xmlelement("DocumentLine",
144 xmlelement("LineID", linegt.line_number),
145 xmlelement("LineNumber", linegt.line_number),
146 xmlforest(linegt.po_number AS "PONumber"),
147 xmlelement("LineType",
148 xmlelement("Code", linegt.line_type),
149 xmlelement("Meaning", NULL)),
150 xmlelement("LineDescription", linegt.description),
151 xmlelement("LineAmount",
152 xmlelement("Value", linegt.extended_amount),
153 xmlelement("Currency",
154 xmlelement("Code", linegt.invoice_currency_code))),
155 xmlelement("UnitRate", linegt.unit_price),
156 xmlelement("Quantity", linegt.quantity),
157 xmlelement("UnitOfMeasure", linegt.unit_of_measure),
158 xmlforest(linegt.inventory_item_id AS "ProductCode"),
159 xmlforest(NULL AS "CommodityCode"),
160 xmlelement("Discount",
161 xmlelement("Amount",
162 xmlelement("Value", linegt.discount_amount),
163 xmlelement("Currency",
164 xmlelement("Code", linegt.invoice_currency_code))),
165 xmlforest(NULL AS "DiscountType")),
166 xmlelement("Tax",
167 xmlelement("Amount",
168 xmlelement("Value", linegt.sales_tax_amount),
169 xmlelement("Currency",
170 xmlelement("Code", linegt.invoice_currency_code))),
171 xmlelement("RatePercent", linegt.tax_rate),
172 xmlforest('SALESTAX' AS "TaxType"),
173 xmlforest(NULL AS "TaxJurisdiction")),
174 xmlelement("Tax",
175 xmlelement("Amount",
176 xmlelement("Value", linegt.vat_tax_amount),
177 xmlelement("Currency",
178 xmlelement("Code", linegt.invoice_currency_code))),
179 xmlelement("RatePercent", linegt.tax_rate),
180 xmlforest('VAT' AS "TaxType"),
181 xmlforest(NULL AS "TaxJurisdiction"))))
182 FROM iby_ar_doc_lines_gt linegt
183 WHERE linegt.doc_unique_ref = headergt.doc_unique_ref)
184 ELSE
185 NULL
186 END
187 )))
188 FROM iby_ar_doc_header_gt headergt
189 WHERE headergt.initiator_extension_id = c_extension_id;
190
191 BEGIN
192
193 -- [lmallick] - bug# 9496405
194 -- Supported for capture and Return trxn's
195 iby_debug_pub.add('p_trxntypeid: ' || p_trxntypeid,
196 IBY_DEBUG_PUB.G_LEVEL_INFO,l_dbg_mod);
197 IF (NOT p_trxntypeid IN (8,9,100,5,11)) THEN
198 RETURN null;
199 END IF;
200
201 -- Bug # 8301765 : Performance issue
202 -- IBY_AR_UTILS.get_document_receivable() will be invoked from two views (ibyxmlv.odf)
203 -- IBY_XML_FNDCPT_ORDER_1_0_V and IBY_XML_FNDCPT_ORDER_PN_1_0_V
204 -- If either Pcard level is 0 or invoking view is IBY_XML_FNDCPT_ORDER_1_0_V then return NULL
205 IF((p_source_view = 'IBY_XML_FNDCPT_ORDER_1_0_V') AND ( p_instrument_type <> 'BANKACCOUNT' )
206 AND (NVL(p_card_data_level,'0') = '0')) THEN
207 RETURN NULL;
208 END IF;
209
210 IF (l_doc_rec_csr%ISOPEN) THEN
211 CLOSE l_doc_rec_csr;
212 END IF;
213
214 OPEN l_doc_rec_csr(p_extension_id,p_card_data_level);
215 FETCH l_doc_rec_csr INTO l_doc_rec;
216 IF (l_doc_rec_csr%NOTFOUND) THEN
217 l_doc_rec := NULL;
218 END IF;
219 CLOSE l_doc_rec_csr;
220 RETURN l_doc_rec;
221
222 END get_document_receivable;
223
224
225 -- Overloaded form of the earlier API.This restricts the Invoice details
226 -- based on the instrument type. The earlier function has been kept
227 -- for backward compatibility.
228 -- NOTE
229 -- For performance reasons this function returns a concatenation
230 -- of DocumentReceivable and DocumentReceivableCount
231 --
232 -- Bug # 8713025
233 -- Added input parameter p_process_profile : process profile code of the transaction.
234
235 FUNCTION get_document_receivable
236 (
237 p_extension_id IN iby_trxn_summaries_all.initiator_extension_id%TYPE,
238 p_trxntypeid IN iby_trxn_summaries_all.trxntypeid%TYPE,
239 p_card_data_level IN iby_trxn_core.card_data_level%TYPE,
240 p_instrument_type IN iby_trxn_summaries_all.instrtype%TYPE,
241 p_process_profile IN iby_trxn_summaries_all.process_profile_code%TYPE,
242 p_source_view IN VARCHAR2
243 )
244 RETURN XMLType
245 IS
246 l_dbg_mod VARCHAR2(100) := 'IBY_AR_UTILS' || '.get_document_receivable(3)';
247 l_doc_rec XMLType;
248 l_exclude_flag VARCHAR2(1);
249
250 /*
251 Bug Number: 9397208
252 For Performance reasons, iby_xml_fndcpt_doc_headers_v view has been replaced
253 with global temporary tables iby_ar_doc_header_gt and iby_ar_doc_lines_gt.
254 Data will be inserted into GTs before extract view call
255 */
256 CURSOR l_doc_rec_csr
257 (c_extension_id iby_trxn_summaries_all.initiator_extension_id%TYPE,
258 c_card_data_level iby_trxn_core.card_data_level%TYPE) IS
259 /*
260 SELECT
261 XMLConcat(XMLAgg(document_headers),
262 XMLElement("DocumentReceivableCount",count(trxn_extension_id)))
263 FROM iby_xml_fndcpt_doc_headers_v
264 WHERE trxn_extension_id = c_extension_id;
265 */
266 -- Bug# 12790543
267 -- Added XMLAgg to pick up all the inovoices which satisfy the criteria
268 -- Without XMLAgg only the first invoice gets picked up b'coz the cursor
269 -- uses a simple fetch
270 SELECT
271 XMLAgg(
272 XMLConcat(XMLElement("DocumentReceivable",
273 XMLElement("DocumentID", NVL(headergt.calling_app_doc_ref_number, headergt.doc_unique_ref)),
274 XMLElement("DocumentStatus",
275 XMLElement("Code", NULL),
276 XMLElement("Meaning", NULL)),
277 XMLElement("DocumentDate", to_char(headergt.document_date, 'YYYY-MM-DD"T"HH24:MI:SS')),
278 XMLElement("DocumentCreationDate", to_char(headergt.creation_date, 'YYYY-MM-DD"T"HH24:MI:SS')),
279 XMLForest(NULL AS "PaymentDueDate"),
280 XMLElement("DocumentType",
281 XMLElement("Code", headergt.document_type),
282 XMLElement("Meaning", NULL)),
283 XMLElement("DocumentDescription", headergt.document_description),
284 XMLElement("BRSignedFlag", headergt.br_signed_flag),
285 XMLElement("BRDraweeIssuedFlag", headergt.br_drawee_issued_flag),
286 XMLElement("TotalDocumentAmount",
287 XMLElement("Value", headergt.document_amount),
288 XMLElement("Currency",
289 XMLElement("Code", headergt.document_currency_code))),
290 XMLElement("PaymentAmount",
291 XMLElement("Value", headergt.settlement_amount),
292 XMLElement("Currency",
293 XMLElement("Code", headergt.document_currency_code))),
294 XMLElement("Charge",
295 XMLElement("Amount",
296 XMLElement("Value", nvl(headergt.freight_amount, 0)),
297 XMLElement("Currency",
298 XMLElement("Code", headergt.document_currency_code))),
299 XMLForest('FREIGHT' AS "ChargeType")),
300 XMLElement("Discount",
301 XMLElement("Amount",
302 XMLElement("Value", headergt.document_discount_earned),
303 XMLElement("Currency",
304 XMLElement("Code", headergt.document_currency_code))),
305 XMLForest(NULL AS "DiscountType")),
306 XMLElement("Tax",
307 XMLElement("Amount",
308 XMLElement("Value", nvl(headergt.local_tax_amount, 0) + nvl(headergt.national_tax_amount, 0)),
309 XMLElement("Currency",
310 XMLElement("Code", headergt.document_currency_code))),
311 XMLElement("RatePercent", NULL),
312 XMLForest('SALESTAX' AS "TaxType"),
313 XMLForest(NULL AS "TaxJurisdiction")),
314 XMLElement("Tax",
315 XMLElement("Amount",
316 XMLElement("Value", headergt.vat_tax_amount),
317 XMLElement("Currency",
318 XMLElement("Code", headergt.document_currency_code))),
319 XMLElement("RatePercent", NULL),
320 XMLForest('VAT' AS "TaxType"),
321 XMLForest(NULL AS "TaxJurisdiction")),
322 XMLElement("ShipmentOrigin",
323 XMLElement("AddressLine1", headergt.address1),
324 XMLForest(headergt.address2 AS "AddressLine2"),
325 XMLForest(headergt.address3 AS "AddressLine3"),
326 XMLElement("City", headergt.city),
327 XMLForest(headergt.county AS "County"),
328 XMLElement("State", headergt.state),
329 XMLElement("Country", headergt.country),
330 XMLElement("PostalCode", headergt.postal_code)),
331 XMLElement("ShipmentDestination",
332 XMLElement("AddressLine1", headergt.to_address1),
333 XMLForest(headergt.to_address2 AS "AddressLine2"),
334 XMLForest(headergt.to_address3 AS "AddressLine3"),
335 XMLElement("City", headergt.to_city),
336 XMLForest(headergt.to_county AS "County"),
337 XMLElement("State", headergt.to_state),
338 XMLElement("Country", headergt.to_country),
339 XMLElement("PostalCode", headergt.to_postal_code)),
340 CASE
341 WHEN(c_card_data_level = '3') THEN
342 (SELECT xmlagg(
343 xmlelement("DocumentLine",
344 xmlelement("LineID", linegt.line_number),
345 xmlelement("LineNumber", linegt.line_number),
346 xmlforest(linegt.po_number AS "PONumber"),
347 xmlelement("LineType",
348 xmlelement("Code", linegt.line_type),
349 xmlelement("Meaning", NULL)),
350 xmlelement("LineDescription", linegt.description),
351 xmlelement("LineAmount",
352 xmlelement("Value", linegt.extended_amount),
353 xmlelement("Currency",
354 xmlelement("Code", linegt.invoice_currency_code))),
355 xmlelement("UnitRate", linegt.unit_price),
356 xmlelement("Quantity", linegt.quantity),
357 xmlelement("UnitOfMeasure", linegt.unit_of_measure),
358 xmlforest(linegt.inventory_item_id AS "ProductCode"),
359 xmlforest(NULL AS "CommodityCode"),
360 xmlelement("Discount",
361 xmlelement("Amount",
362 xmlelement("Value", linegt.discount_amount),
363 xmlelement("Currency",
364 xmlelement("Code", linegt.invoice_currency_code))),
365 xmlforest(NULL AS "DiscountType")),
366 xmlelement("Tax",
367 xmlelement("Amount",
368 xmlelement("Value", linegt.sales_tax_amount),
369 xmlelement("Currency",
370 xmlelement("Code", linegt.invoice_currency_code))),
371 xmlelement("RatePercent", linegt.tax_rate),
372 xmlforest('SALESTAX' AS "TaxType"),
373 xmlforest(NULL AS "TaxJurisdiction")),
374 xmlelement("Tax",
375 xmlelement("Amount",
376 xmlelement("Value", linegt.vat_tax_amount),
377 xmlelement("Currency",
378 xmlelement("Code", linegt.invoice_currency_code))),
379 xmlelement("RatePercent", linegt.tax_rate),
380 xmlforest('VAT' AS "TaxType"),
381 xmlforest(NULL AS "TaxJurisdiction"))))
382 FROM iby_ar_doc_lines_gt linegt
383 WHERE linegt.doc_unique_ref = headergt.doc_unique_ref)
384 ELSE
385 NULL
386 END
387 )))
388 FROM iby_ar_doc_header_gt headergt
389 WHERE headergt.initiator_extension_id = c_extension_id;
390
391 BEGIN
392 iby_debug_pub.add('p_trxntypeid: ' || p_trxntypeid,
393 IBY_DEBUG_PUB.G_LEVEL_INFO,l_dbg_mod);
394 -- [lmallick] - bug# 9496405
395 -- Supported for capture and Return trxn's
396 IF (NOT p_trxntypeid IN (8,9,100,5,11)) THEN
397 RETURN null;
398 END IF;
399
400 IF ( (p_source_view = 'IBY_XML_FNDCPT_ORDER_1_0_V') AND NVL(p_card_data_level,'0') = '0') THEN
401
402 IF ( upper(p_instrument_type) = 'CREDITCARD')
403 THEN
404 SELECT sp.exclude_trxn_det_extraction
405 INTO l_exclude_flag
406 FROM IBY_FNDCPT_USER_CC_PF_B up
407 ,IBY_FNDCPT_SYS_CC_PF_B sp
408 WHERE up.user_cc_profile_code = p_process_profile
409 AND up.sys_cc_profile_code = sp.sys_cc_profile_code;
410
411 ELSIF ( upper(p_instrument_type) = 'BANKACCOUNT')
412 THEN
413 SELECT sp.exclude_trxn_det_extraction
414 INTO l_exclude_flag
415 FROM IBY_FNDCPT_USER_EFT_PF_B up
416 ,IBY_FNDCPT_SYS_EFT_PF_B sp
417 WHERE up.user_eft_profile_code = p_process_profile
418 AND up.sys_eft_profile_code = sp.sys_eft_profile_code;
419
420 ELSE
421 SELECT sp.exclude_trxn_det_extraction
422 INTO l_exclude_flag
423 FROM IBY_FNDCPT_USER_DC_PF_B up
424 ,IBY_FNDCPT_SYS_DC_PF_B sp
425 WHERE up.user_dc_profile_code = p_process_profile
426 AND up.sys_dc_profile_code = sp.sys_dc_profile_code;
427
428 END IF;
429
430 IF ( NVL(l_exclude_flag,'N') = 'Y') THEN
431 RETURN null;
432 END IF;
433
434 END IF;
435
436 IF (l_doc_rec_csr%ISOPEN) THEN
437 CLOSE l_doc_rec_csr;
438 END IF;
439
440 OPEN l_doc_rec_csr(p_extension_id,p_card_data_level);
441 FETCH l_doc_rec_csr INTO l_doc_rec;
442 IF (l_doc_rec_csr%NOTFOUND) THEN
443 l_doc_rec := NULL;
444 END IF;
445 CLOSE l_doc_rec_csr;
446 RETURN l_doc_rec;
447
448 END get_document_receivable;
449
450 PROCEDURE call_get_payment_info(
451 p_payment_server_order_num IN
452 ar_cash_receipts.payment_server_order_num%TYPE,
453 x_customer_trx_id OUT NOCOPY
454 ar_receivable_applications.customer_trx_id%TYPE,
455 x_return_status OUT NOCOPY VARCHAR2,
456 x_msg_count OUT NOCOPY NUMBER,
457 x_msg_data OUT NOCOPY VARCHAR2
458 ) IS
459
460 l_receipt_header ar_cash_receipts%ROWTYPE;
461 l_app_type ar_receivable_applications.status%TYPE :='APP';
462 l_app_tbl_type AR_PUBLIC_UTILS.application_tbl_type;
463 l_app_rec_type ar_receivable_applications%ROWTYPE;
464
465 BEGIN
466 x_customer_trx_id := 0;
467 x_return_status := FND_API.G_RET_STS_SUCCESS;
468
469 IF p_payment_server_order_num IS NOT NULL THEN
470
471 AR_PUBLIC_UTILS.get_payment_info(
472 p_payment_server_order_num,
473 l_app_type,
474 l_receipt_header,
475 l_app_tbl_type,
476 x_return_status,
477 x_msg_count,
478 x_msg_data);
479
480 IF l_app_tbl_type.COUNT = 1 THEN
481 x_customer_trx_id := l_app_tbl_type(1).applied_customer_trx_id;
482 END IF;
483
484 END IF;
485
486 EXCEPTION
487 WHEN others THEN
488 x_msg_count := 1;
489 x_return_status := FND_API.G_RET_STS_ERROR;
490 x_msg_data := 'IBY_AR_UTILS.CALL_GET_PAYMENT_INFO ERROR: ' || x_msg_data;
491 END;
492
493 FUNCTION call_get_payment_info
494 (p_payment_server_order_num IN
495 ar_cash_receipts.payment_server_order_num%TYPE)
496 RETURN ar_receivable_applications.customer_trx_id%TYPE
497 IS
498 l_trx_id ar_receivable_applications.customer_trx_id%TYPE;
499 l_ret_status VARCHAR2(2000);
500 l_msg_count NUMBER;
501 l_msg_data VARCHAR2(5000);
502 BEGIN
503
504 call_get_payment_info
505 (p_payment_server_order_num,l_trx_id,l_ret_status,l_msg_count,l_msg_data);
506 RETURN l_trx_id;
507
508 END call_get_payment_info;
509
510 FUNCTION get_order_freight_amount(p_customer_trx_id IN
511 ar_invoice_header_v.customer_trx_id%TYPE)
512 RETURN NUMBER
513 IS
514 l_freight_amount NUMBER := 0;
515
516 CURSOR c_header_freight
517 (ci_cust_trx_id ar_invoice_header_v.customer_trx_id%TYPE)
518 IS
519 SELECT extended_amount
520 FROM ar_invoice_lines_v
521 WHERE (link_to_cust_trx_line_id IS NULL)
522 AND (line_type = 'FREIGHT')
523 AND (customer_trx_id = ci_cust_trx_id);
524
525 CURSOR c_freight_total
526 (ci_cust_trx_id ar_invoice_header_v.customer_trx_id%TYPE)
527 IS
528 SELECT SUM(extended_amount)
529 FROM ar_invoice_lines_v
530 WHERE
531 (line_type = 'FREIGHT')
532 AND (customer_trx_id = ci_cust_trx_id);
533
534 BEGIN
535
536 IF (c_header_freight%ISOPEN) THEN
537 CLOSE c_header_freight;
538 END IF;
539 IF (c_freight_total%ISOPEN) THEN
540 CLOSE c_freight_total;
541 END IF;
542
543 OPEN c_header_freight(p_customer_trx_id);
544 FETCH c_header_freight INTO l_freight_amount;
545 IF (c_header_freight%NOTFOUND) THEN
546 l_freight_amount:=0;
547 END IF;
548 CLOSE c_header_freight;
549
550 IF (l_freight_amount>0) THEN
551 RETURN l_freight_amount;
552 END IF;
553 --
554 -- if no header-level amount exists, then take
555 -- the sum of all line-level freight charges
556 --
557 OPEN c_freight_total(p_customer_trx_id);
558 FETCH c_freight_total INTO l_freight_amount;
559 IF (c_freight_total%NOTFOUND) THEN
560 l_freight_amount := 0;
561 END IF;
562 CLOSE c_freight_total;
563
564 RETURN NVL(l_freight_amount,0);
565 END get_order_freight_amount;
566
567 FUNCTION get_order_tax_amount
568 (p_customer_trx_id IN ar_invoice_header_v.customer_trx_id%TYPE,
569 p_tax_type IN VARCHAR2)
570 RETURN NUMBER
571 IS
572 l_tax_total NUMBER := 0;
573
574 CURSOR c_line_items
575 (ci_cust_trx_id ar_invoice_header_v.customer_trx_id%TYPE)
576 IS
577 SELECT customer_trx_line_id
578 FROM ar_invoice_lines_v
579 WHERE
580 ( line_type = 'LINE')
581 AND (customer_trx_id = ci_cust_trx_id);
582
583 BEGIN
584
585 FOR order_line IN c_line_items(p_customer_trx_id) LOOP
586 l_tax_total := get_line_tax_amount(p_customer_trx_id,
587 order_line.customer_trx_line_id,
588 p_tax_type);
589 END LOOP;
590
591 RETURN l_tax_total;
592 END get_order_tax_amount;
593
594 FUNCTION get_order_amount
595 (p_customer_trx_id IN ar_invoice_header_v.customer_trx_id%TYPE)
596 RETURN NUMBER
597 IS
598 l_order_total NUMBER := 0;
599
600 CURSOR c_line_items
601 (ci_cust_trx_id ar_invoice_header_v.customer_trx_id%TYPE)
602 IS
603 SELECT extended_amount
604 FROM ar_invoice_lines_v
605 WHERE
606 ( line_type = 'LINE')
607 AND (customer_trx_id = ci_cust_trx_id);
608 BEGIN
609
610 FOR order_line IN c_line_items(p_customer_trx_id) LOOP
611 l_order_total := l_order_total + NVL(order_line.extended_amount,0);
612 END LOOP;
613
614 RETURN l_order_total;
615 END get_order_amount;
616
617 FUNCTION get_line_tax_amount
618 (p_customer_trx_id IN ar_invoice_header_v.customer_trx_id%TYPE,
619 p_customer_trx_line_id IN ar_invoice_lines_v.customer_trx_line_id%TYPE,
620 p_tax_type IN VARCHAR2)
621 RETURN NUMBER
622 IS
623
624 l_sales_tax_total NUMBER := 0;
625 l_vat_tax_total NUMBER := 0;
626
627 CURSOR c_tax_items
628 (ci_cust_trx_id ar_invoice_lines_v.customer_trx_id%TYPE,
629 ci_cust_trx_line_id ar_invoice_lines_v.link_to_cust_trx_line_id%TYPE)
630 IS
631 SELECT extended_amount, location_rate_id, location_segment_id
632 FROM ar_invoice_lines_v
633 WHERE
634 (line_type='TAX')
635 AND (customer_trx_id = ci_cust_trx_id)
636 AND (link_to_cust_trx_line_id = ci_cust_trx_line_id);
637
638 BEGIN
639
640 FOR tax_line IN c_tax_items(p_customer_trx_id,p_customer_trx_line_id) LOOP
641 IF (NOT tax_line.location_rate_id IS NULL)
642 OR (NOT tax_line.location_segment_id IS NULL)
643 THEN
644 l_sales_tax_total := l_sales_tax_total + NVL(tax_line.extended_amount,0);
645 ELSE
646 l_vat_tax_total := l_vat_tax_total + NVL(tax_line.extended_amount,0);
647 END IF;
648 END LOOP;
649
650 IF (p_tax_type = G_TAX_TYPE_SALES) THEN
651 RETURN l_sales_tax_total;
652 ELSIF (p_tax_type = G_TAX_TYPE_VAT) THEN
653 RETURN l_vat_tax_total;
654 ELSE
655 RETURN 0;
656 END IF;
657
658 END get_line_tax_amount;
659
660 FUNCTION get_line_tax_rate
661 (p_customer_trx_id IN ar_invoice_header_v.customer_trx_id%TYPE,
662 p_customer_trx_line_id IN ar_invoice_lines_v.customer_trx_line_id%TYPE,
663 p_tax_type IN VARCHAR2)
664 RETURN NUMBER
665 IS
666 l_sales_rate_total NUMBER := 0;
667 l_vat_rate_total NUMBER := 0;
668
669 l_sales_count NUMBER := 0;
670 l_vat_count NUMBER := 0;
671
672 CURSOR c_tax_items
673 (ci_cust_trx_id ar_invoice_lines_v.customer_trx_id%TYPE,
674 ci_cust_trx_line_id ar_invoice_lines_v.link_to_cust_trx_line_id%TYPE)
675 IS
676 SELECT location_rate_id, location_segment_id, tax_rate
677 FROM ar_invoice_lines_v
678 WHERE
679 (line_type='TAX')
680 AND (customer_trx_id = ci_cust_trx_id)
681 AND (link_to_cust_trx_line_id = ci_cust_trx_line_id);
682
683 BEGIN
684
685 FOR tax_line IN c_tax_items(p_customer_trx_id,p_customer_trx_line_id) LOOP
686 IF (NOT tax_line.location_rate_id IS NULL)
687 OR (NOT tax_line.location_segment_id IS NULL)
688 THEN
689 l_sales_rate_total := l_sales_rate_total + NVL(tax_line.tax_rate,0);
690 l_sales_count := l_sales_count + 1;
691 ELSE
692 l_vat_rate_total := l_vat_rate_total + NVL(tax_line.tax_rate,0);
693 l_vat_count := l_vat_count + 1;
694 END IF;
695 END LOOP;
696
697 IF (p_tax_type = G_TAX_TYPE_SALES) THEN
698 RETURN (l_sales_rate_total/GREATEST(l_sales_count,1));
699 ELSIF (p_tax_type = G_TAX_TYPE_VAT) THEN
700 RETURN (l_vat_rate_total/GREATEST(l_vat_count,1));
701 ELSE
702 RETURN 0;
703 END IF;
704
705 END get_line_tax_rate;
706
707 FUNCTION get_trxn_ref_number2
708 (p_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
709 RETURN VARCHAR2
710 IS
711 l_trxn_ref_number2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
712 l_dbg_mod VARCHAR2(100) := 'IBY_AR_UTILS' || '.get_trxn_ref_number2';
713
714 CURSOR c_trxn_ref_number2
715 (ci_trxn_ext_id iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
716 IS
717 SELECT tx.trxn_ref_number2
718 FROM iby_fndcpt_tx_extensions tx
719 WHERE
720 tx.trxn_extension_id = ci_trxn_ext_id
721 AND tx.trxn_ref_number1 = 'RECEIPT';
722 BEGIN
723 IF(c_trxn_ref_number2%ISOPEN) THEN
724 CLOSE c_trxn_ref_number2;
725 END IF;
726
727 OPEN c_trxn_ref_number2(p_trxn_extension_id);
728 FETCH c_trxn_ref_number2 INTO l_trxn_ref_number2;
729 IF(c_trxn_ref_number2%NOTFOUND) THEN
730 l_trxn_ref_number2 := NULL;
731 END IF;
732 CLOSE c_trxn_ref_number2;
733
734 RETURN l_trxn_ref_number2;
735
736 EXCEPTION
737 WHEN others THEN
738 iby_debug_pub.add('SQLCODE 1:: ' || SQLCODE,
739 IBY_DEBUG_PUB.G_LEVEL_INFO,l_dbg_mod);
740 iby_debug_pub.add('SQLERRM :: ' || sqlerrm,
741 IBY_DEBUG_PUB.G_LEVEL_INFO,l_dbg_mod);
742 RETURN NULL;
743
744 END get_trxn_ref_number2;
745
746 -- Return: The Authorization Flag for the given Transaction Extension Id
747 PROCEDURE get_authorization_status
748 (p_trxn_extension_id IN iby_fndcpt_tx_operations.trxn_extension_id%TYPE,
749 x_auth_flag OUT NOCOPY VARCHAR2)
750 IS
751 l_dbg_mod VARCHAR2(100) := 'IBY_AR_UTILS' || '.get_authorization_status';
752
753 CURSOR c_auth_status(c_extension_id iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
754 IS
755 SELECT decode(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG
756 FROM iby_trxn_summaries_all summ
757 , iby_fndcpt_tx_operations op
758 WHERE summ.transactionid = op.transactionid
759 AND reqtype = 'ORAPMTREQ'
760 AND status IN(0, 100)
761 AND op.trxn_extension_id = c_extension_id
762 AND ((trxntypeid IN(2, 3))
763 OR
764 (trxntypeid = 20
765 AND summ.trxnmid = (SELECT MAX(trxnmid)
766 FROM iby_trxn_summaries_all summ1,
767 iby_fndcpt_tx_operations op1
768 WHERE summ1.transactionid = op1.transactionid
769 AND summ1.reqtype = 'ORAPMTREQ'
770 AND summ1.status IN(0, 100)
771 AND summ1.trxntypeid IN(2, 3, 20)
772 AND op1.trxn_extension_id = op.trxn_extension_id
773 )
774 )
775 ) ORDER BY op.transactionid ASC;
776
777 BEGIN
778 iby_debug_pub.add('Enter: '||p_trxn_extension_id, IBY_DEBUG_PUB.G_LEVEL_INFO,l_dbg_mod);
779 IF (c_auth_status%ISOPEN) THEN
780 CLOSE c_auth_status;
781 END IF;
782
783 OPEN c_auth_status(p_trxn_extension_id);
784 FETCH c_auth_status INTO x_auth_flag;
785 IF (c_auth_status%NOTFOUND) THEN
786 iby_debug_pub.add('0 records fetched', IBY_DEBUG_PUB.G_LEVEL_INFO,l_dbg_mod);
787 x_auth_flag := 'N';
788 END IF;
789 CLOSE c_auth_status;
790 iby_debug_pub.add('x_auth_flag= '||x_auth_flag, IBY_DEBUG_PUB.G_LEVEL_INFO,l_dbg_mod);
791 EXCEPTION
792 WHEN others THEN
793 iby_debug_pub.add('Exception thrown!. Error: ' || SQLERRM,
794 IBY_DEBUG_PUB.G_LEVEL_EXCEPTION,l_dbg_mod);
795
796 x_auth_flag:='N';
797 END;
798
799 END IBY_AR_UTILS;