[Home] [Help]
PACKAGE BODY: APPS.POS_AP_INVOICE_PAYMENTS_PKG
Source
1 PACKAGE BODY POS_AP_INVOICE_PAYMENTS_PKG AS
2 /* $Header: POSAPPAB.pls 115.0 2001/06/18 18:42:23 pkm ship $ */
3
4 -----------------------------------------------------------------------
5 -- Declare record type used to hold records returned from generic cursor
6 --
7 TYPE PAYMENT_RECORD IS
8 RECORD (check_number ap_checks.check_number%TYPE,
9 check_id ap_checks.check_id%TYPE,
10 check_date ap_checks.check_date%TYPE,
11 invoice_num ap_invoices.invoice_num%TYPE,
12 invoice_id ap_invoices.invoice_id%TYPE,
13 gl_date ap_invoice_payments.accounting_date%TYPE,
14 type ap_invoice_payments.invoice_payment_type%TYPE,
15 type_trans ap_lookup_codes.displayed_field%TYPE,
16 method ap_checks.payment_method_lookup_code%TYPE,
17 method_trans ap_lookup_codes.displayed_field%TYPE);
18
19 -----------------------------------------------------------------------
20 -- Declare generic cursor to get check number, check id, invoice number
21 -- invoice id, payment type, payment method lookup code, and translated
22 -- payment method
23 --
24 --togeorge 11/15/2000
25 --changed org specific views to _all tables
26 CURSOR payment_cursor (l_invoice_id NUMBER, l_payment_num NUMBER)
27 RETURN payment_record IS
28 SELECT ac.check_number,
29 ac.check_id,
30 ac.check_date,
31 ai.invoice_num,
32 ai.invoice_id,
33 aip.accounting_date,
34 aip.invoice_payment_type,
35 alc1.displayed_field,
36 ac.payment_method_lookup_code,
37 alc2.displayed_field
38 FROM ap_invoice_payments_all aip, --ap_invoice_payments
39 ap_checks_all ac, --ap_checks
40 ap_invoices_all ai, --ap_invoices
41 ap_lookup_codes alc1,
42 ap_lookup_codes alc2
43 WHERE aip.invoice_id = l_invoice_id
44 AND aip.payment_num = l_payment_num
45 AND aip.check_id = ac.check_id
46 AND aip.other_invoice_id = ai.invoice_id (+)
47 AND alc1.lookup_type = 'NLS TRANSLATION'
48 AND alc1.lookup_code = 'PREPAY'
49 AND alc2.lookup_type = 'PAYMENT METHOD'
50 AND alc2.lookup_code = ac.payment_method_lookup_code;
51
52 -----------------------------------------------------------------------
53 -- Function concat_document_num_type concatenates the document number
54 -- to the document type
55 --
56 FUNCTION concat_document_num_type(l_payment_record IN PAYMENT_RECORD)
57 RETURN VARCHAR2
58 IS
59 l_paid_by VARCHAR2(80);
60 BEGIN
61
62 IF (l_payment_record.type = 'PREPAY') THEN
63 -------------------------------------------------------
64 -- Get prepayment number concatenated to prepayment type
65 --
66 l_paid_by := l_payment_record.invoice_num ||' - '||
67 l_payment_record.type_trans;
68 ELSE
69 -------------------------------------------------------
70 -- Get payment number concatenated to payment method type
71 --
72 l_paid_by := l_payment_record.check_number ||' - '||
73 l_payment_record.method_trans;
74 END IF;
75
76 RETURN(l_paid_by);
77
78 END concat_document_num_type;
79
80
81 -----------------------------------------------------------------------
82 -- Function get_paid_by_list returns a list of document numbers
83 -- concatenated to the document type used to pay this payment schedule
84 -- or NULL if unpaid.
85 --
86 FUNCTION get_paid_by_list(l_invoice_id IN NUMBER, l_payment_num IN NUMBER)
87 RETURN VARCHAR2
88 IS
89 l_paid_by VARCHAR2(80);
90 l_paid_by_list VARCHAR2(2000) := NULL;
91 l_payment_record PAYMENT_RECORD;
92
93 BEGIN
94
95 OPEN payment_cursor(l_invoice_id, l_payment_num);
96
97 LOOP
98 FETCH payment_cursor INTO l_payment_record;
99 EXIT WHEN payment_cursor%NOTFOUND;
100
101 l_paid_by := POS_AP_INVOICE_PAYMENTS_PKG.CONCAT_DOCUMENT_NUM_TYPE(
102 l_payment_record);
103
104 IF (l_paid_by_list IS NOT NULL) THEN
105 l_paid_by_list := l_paid_by_list || ', ';
106 END IF;
107
108 l_paid_by_list := l_paid_by_list || l_paid_by;
109
110 END LOOP;
111
112 CLOSE payment_cursor;
113
114 RETURN(l_paid_by_list);
115
116 END get_paid_by_list;
117
118 END POS_AP_INVOICE_PAYMENTS_PKG;