1 PACKAGE BODY okl_lockbox_hook AS
2 /*$Header: OKLRLBHB.pls 120.4 2006/07/07 10:07:24 pagarg noship $*/
3 --
4 /*----------------------------------------------------------------------------
5 proc_before_validation
6
7 This procedure will be called before the validation is called from arlplb().
8 If this procedure returns 0,
9 arlplb.opc will understand that some processing had taken place in this
10 procedure and it returned success. It will proceed with validation then.
11 If this procedure returns 2,
12 arlplb.opc will understand that some error had occured during the
13 processing in this procedure and will exit rolling back the information.
14 If out_insert_records is returned as 'Y', the first validation will
15 insert the records into ar_interim_cash_receipt and receipt_line.
16 In non-custom mode, this parameter returns 'Y', because we do not call
17 validation second time. However, if you are planning to call the second
18 validation, for customising lockbox, assign this variable as 'N'.
19
20 ----------------------------------------------------------------------------*/
21 PROCEDURE proc_before_validation(out_errorbuf OUT NOCOPY VARCHAR2,
22 out_errorcode OUT NOCOPY VARCHAR2,
23 in_trans_req_id IN VARCHAR2,
24 out_insert_records OUT NOCOPY VARCHAR2) IS
25 BEGIN
26 arp_util.debug('arp_lockbox_hook.proc_before_validation()+');
27 IF nvl(arp_global.sysparam.ta_installed_flag,'N') = 'Y' THEN
28 --lockbox_cover.proc_before_valid(in_trans_req_id,out_errorcode,out_errorbuf);
29 out_insert_records := 'N';
30 ELSE
31 out_errorcode := 0;
32 out_errorbuf := NULL;
33 out_insert_records := 'N';
34 END IF;
35 arp_util.debug('arp_lockbox_hook.proc_before_validation()-');
36 END proc_before_validation;
37 --
38 /*----------------------------------------------------------------------------
39 proc_after_validation
40
41 This procedure will be called after the validation is over from arlplb().
42 If this procedure returns 0,
43 arlplb.opc will understand that some processing had taken place in this
44 procedure and arlplb.opc will fire the validation (arlval) again.
45 If this procedure returns 2,
46 arlplb.opc will understand that some error had occured during the
47 processing in this procedure and will exit rolling back the information.
48 If this procedure returns 9,
49 arlplb.opc will not fire the validation second time and will go ahead
50 with arlprt(). This is the same path as it was taking in base Rel 10.7
51 If out_insert_records is returned as 'Y', the second validation will
52 insert the records into ar_interim_cash_receipt and receipt_line.
53 In non-custom mode, this parameter returns 'N', because we do not call
54 validation second time. However, if you are planning to call the second
55 validation and you have returned out_insert_records as 'N' in the
56 proc_before_validation, you should return 'Y' here. This parameter is
57 considered only if the out_errorcode was returned as 0.
58
59 ----------------------------------------------------------------------------*/
60 PROCEDURE proc_after_validation(out_errorbuf OUT NOCOPY VARCHAR2,
61 out_errorcode OUT NOCOPY VARCHAR2,
62 in_trans_req_id IN VARCHAR2,
63 out_insert_records OUT NOCOPY VARCHAR2) IS
64
65 p_api_version NUMBER := 1;
66 p_init_msg_list VARCHAR2(1);
67 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
68 x_msg_count NUMBER;
69 x_msg_data VARCHAR(2000);
70
71 lp_trans_req_id AR_PAYMENTS_INTERFACE.TRANSMISSION_REQUEST_ID%TYPE;
72
73 BEGIN
74
75 lp_trans_req_id := in_trans_req_id;
76
77 OKL_LCKBX_CSH_APP_PUB.handle_auto_pay ( p_api_version
78 ,p_init_msg_list
79 ,x_return_status
80 ,x_msg_count
81 ,x_msg_data
82 ,lp_trans_req_id
83 );
84 arp_util.debug('arp_lockbox_hook.proc_before_validation()+');
85 IF nvl(arp_global.sysparam.ta_installed_flag,'N') = 'Y' THEN
86 --lockbox_cover.proc_after_valid(in_trans_req_id,out_errorcode,out_errorbuf);
87 out_insert_records := 'Y';
88 ELSE
89 out_errorcode := 9;
90 out_errorbuf := NULL;
91 out_insert_records := 'Y';
92 END IF;
93 arp_util.debug('arp_lockbox_hook.proc_before_validation()-');
94 END proc_after_validation;
95 --
96 /*----------------------------------------------------------------------------
97 proc_after_second_validation
98
99 This procedure will be called after the second validation and before printing
100 Lockbox execution report. It is called from arlplb().
101 If this procedure returns 0,
102 arlplb.opc will understand that this procedure returned success.
103 It will proceed with printing report then.
104 If this procedure returns anything other than 0,
105 arlplb.opc will understand that some error had occured during the
106 processing in this procedure and will exit rolling back the information.
107
108 ----------------------------------------------------------------------------*/
109 PROCEDURE proc_after_second_validation(out_errorbuf OUT NOCOPY VARCHAR2,
110 out_errorcode OUT NOCOPY VARCHAR2,
111 in_trans_req_id IN VARCHAR2) IS
112 BEGIN
113 arp_util.debug('arp_lockbox_hook.proc_after_second_validation()+');
114 IF nvl(arp_global.sysparam.ta_installed_flag,'N') = 'Y' THEN
115 --lockbox_cover.proc_after_second_valid(in_trans_req_id,out_errorcode,out_errorbuf);
116 NULL;
117 ELSE
118 out_errorcode := 0;
119 out_errorbuf := NULL;
120 END IF;
121 arp_util.debug('arp_lockbox_hook.proc_after_second_validation()-');
122 END proc_after_second_validation;
123 --
124 /*----------------------------------------------------------------------------
125 Procedure
126
127 cursor_for_matching_rule
128
129 Oracle Receivables supplies the Packaged Procedure
130 arp_lockbox_Hook.cursor_for_matching_rule, which can be used to
131 add a matching rule to Lockbox functionality.
132 If for example you need to match matching numbers and date passed to
133 Lockbox with numbers and dates in your own custom tables
134 custom_table.custom_number and custom_table.custom_date, instead of
135 or in addition to standard matching options, you can use this feature.
136 Or you can use this feature to match with other numbers and dates in
137 the existing Receivables tables, as the need arises.
138
139 This procedure expects a row in the AR_LOOKUPS table with lookup_type
140 = 'ARLPLB_MATCHING_OPTION' and valid values for other columns required
141 for the customized option.
142 The master program arp_process_lockbox will fetch that row and if it
143 finds it to be one of the non-standard (NOT built in core AR), it will
144 pass the control to this procedure with the corresponding lookup_code
145 in your database.
146 The procedure should return a string that Dynamic SQL can use to open and
147 parse a cursor. You need to create this SQL string to replace the string
148 named 'p_cursor_string'. (see below an example).
149
150 Your string should have the following restrictions:
151 1. The only allowed bind variables are as follows:
152 a. b_current_matching_number
153 At execution time, this will get a value of a matching_number passed
154 in the overflow or payment record.
155 b. b_current_matching_date
156 At the execution time, this will get a value of a matching_date passed
157 in the overflow or payment record.
158 c. b_current_installment
159 At the execution time, this will get a value of installment num passed
160 in overflow or payment record.
161 d. b_customer_id
162 If the customer is identified using customer number or MICR number,
163 the program will enforce that the matching_number be of the same
164 customer (with an exception of a value 'Y' in b_pay_unrelated
165 _customers, see below).
166 e. b_pay_unrelated_customers
167 At the time of submitting the lockbox process, the user is prompted to
168 enter whether to allow payment through unrelated customers or not.
169 This variable will get a value 'Y' or 'N' based on that entry.
170 f. b_lockbox_matching_option
171 The value of this variable will match to the value of ar_lookups.lookup
172 _code. It is also stored in ar_customer_profiles.lockbox_matching
173 _option and in ar_lockboxes.lockbox_matching_option.
174 g. b_use_matching_date
175 This variable will be assigned a value 'NEVER', 'ALWAYS' or
176 'FOR_DUPLICATES', depending upon the setup in your lockbox
177 (in ar_lockboxes).
178
179 2. If you are customizing lockbox using this procedure, you have to make
180 sure that this procedure retrns a string that can create a valid cursor
181 and that the SQL returns one and only one row (neither zero nor more
182 than one).
183
184 3. The program expects three return values from proposed SQL necessarily
185 in the same order:
186 1. Customer_Id (NUMBER(15))
187 2. Invoice Number (VARCHAR2(20))
188 3. Invoice Date (DATE)
189
190 4. The program expects that the combination of Invoice Number and invoice
191 date is unique in ar_payment_schedules.
192
193 5. You need not use all the bind variables that are provided in your
194 proposed SQL.
195 For example your SQL string can be like this:
196
197 p_cursor_string := 'select ct.customer_id, ct.trx_number, ct.trx_date ' ||
198 'from custom_table ct ' ||
199 'where ct.matching_number = :b_current_matching_number ' ||
200 ' and ct.matching_date = :b_current_matching_date ';
201
202 6. The SQL must be such that, if it does not match with given matching
203 number and matching date (optional), it must return:
204 customer_id = -9999,
205 trx_number = null,
206 trx_date = null.
207
208 7. In case it matches to multiple customers, but the same trx numbers
209 it must return customer_id = -7777. trx_number and trx_date will
210 be ignored in this case.
211
212 8. The program calling this procedure does not expect it to return
213 any errors, as the definition of a cursor is a one-time procedure
214 and if done carefully should not error.
215
216
217 ----------------------------------------------------------------------------*/
218 PROCEDURE CURSOR_FOR_MATCHING_RULE(p_matching_option IN VARCHAR2,
219 p_cursor_string OUT NOCOPY VARCHAR2) IS
220 BEGIN
221 arp_util.debug('arp_lockbox_hook.cursor_for_matching_rule()+');
222 p_cursor_string := 'select -9999, NULL, NULL from dual';
223 arp_util.debug('arp_lockbox_hook.cursor_for_matching_rule()+');
224 RETURN;
225 END cursor_for_matching_rule;
226 --
227 END okl_lockbox_hook;
[Home] [Help]