1 PACKAGE BODY ARP_LOCKBOX_HOOK AS
2 /*$Header: ARRLBHKB.pls 120.7.12010000.5 2009/03/20 17:36:59 aghoraka ship $*/
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
20 ----------------------------------------------------------------------------*/
17 validation second time. However, if you are planning to call the second
18 validation, for customising lockbox, assign this variable as 'N'.
19
21 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
22
23 PROCEDURE proc_before_validation(out_errorbuf OUT NOCOPY VARCHAR2,
24 out_errorcode OUT NOCOPY VARCHAR2,
25 in_trans_req_id IN VARCHAR2,
26 out_insert_records OUT NOCOPY VARCHAR2) IS
27 BEGIN
28 IF PG_DEBUG in ('Y', 'C') THEN
29 arp_util.debug('arp_lockbox_hook.proc_before_validation()+');
30 END IF;
31 out_errorcode := 0;
32 out_errorbuf := NULL;
33 out_insert_records := 'Y';
34 IF PG_DEBUG in ('Y', 'C') THEN
35 arp_util.debug('arp_lockbox_hook.proc_before_validation()-');
36 END IF;
37 END proc_before_validation;
38 --
39 /*----------------------------------------------------------------------------
40 proc_after_validation
41
42 This procedure will be called after the validation is over from arlplb().
43 If this procedure returns 0,
44 arlplb.opc will understand that some processing had taken place in this
45 procedure and arlplb.opc will fire the validation (arlval) again.
46 If this procedure returns 2,
47 arlplb.opc will understand that some error had occured during the
48 processing in this procedure and will exit rolling back the information.
49 If this procedure returns 9,
50 arlplb.opc will not fire the validation second time and will go ahead
51 with arlprt(). This is the same path as it was taking in base Rel 10.7
52 If out_insert_records is returned as 'Y', the second validation will
53 insert the records into ar_interim_cash_receipt and receipt_line.
54 In non-custom mode, this parameter returns 'N', because we do not call
55 validation second time. However, if you are planning to call the second
56 validation and you have returned out_insert_records as 'N' in the
57 proc_before_validation, you should return 'Y' here. This parameter is
58 considered only if the out_errorcode was returned as 0.
59
60 ----------------------------------------------------------------------------*/
61 PROCEDURE proc_after_validation(out_errorbuf OUT NOCOPY VARCHAR2,
62 out_errorcode OUT NOCOPY VARCHAR2,
63 in_trans_req_id IN VARCHAR2,
64 out_insert_records OUT NOCOPY VARCHAR2) IS
65 --
66 BEGIN
67 IF PG_DEBUG in ('Y', 'C') THEN
68 arp_util.debug('arp_lockbox_hook.proc_after_validation()+');
69 END IF;
70 out_errorcode := 9;
71 out_errorbuf := NULL;
72 out_insert_records := 'N';
73 IF PG_DEBUG in ('Y', 'C') THEN
74 arp_util.debug('arp_lockbox_hook.proc_after_validation()-');
75 END IF;
76 END proc_after_validation;
77 --
78 /*----------------------------------------------------------------------------
79 proc_after_second_validation
80
81 This procedure will be called after the second validation and before printing
82 Lockbox execution report. It is called from arlplb().
83 If this procedure returns 0,
84 arlplb.opc will understand that this procedure returned success.
85 It will proceed with printing report then.
86 If this procedure returns anything other than 0,
87 arlplb.opc will understand that some error had occured during the
88 processing in this procedure and will exit rolling back the information.
89
90 ----------------------------------------------------------------------------*/
91 PROCEDURE proc_after_second_validation(out_errorbuf OUT NOCOPY VARCHAR2,
92 out_errorcode OUT NOCOPY VARCHAR2,
93 in_trans_req_id IN VARCHAR2) IS
94 BEGIN
95 IF PG_DEBUG in ('Y', 'C') THEN
96 arp_util.debug('arp_lockbox_hook.proc_after_second_validation()+');
97 END IF;
98 out_errorcode := 0;
99 out_errorbuf := NULL;
100 IF PG_DEBUG in ('Y', 'C') THEN
101 arp_util.debug('arp_lockbox_hook.proc_after_second_validation()-');
102 END IF;
103 END proc_after_second_validation;
104 --
105 /*----------------------------------------------------------------------------
106 Procedure
107
108 cursor_for_matching_rule
109
110 Oracle Receivables supplies the Packaged Procedure
111 arp_lockbox_Hook.cursor_for_matching_rule, which can be used to
112 add a matching rule to Lockbox functionality.
113 If for example you need to match matching numbers and date passed to
114 Lockbox with numbers and dates in your own custom tables
115 custom_table.custom_number and custom_table.custom_date, instead of
116 or in addition to standard matching options, you can use this feature.
117 Or you can use this feature to match with other numbers and dates in
118 the existing Receivables tables, as the need arises.
119
120 This procedure expects a row in the AR_LOOKUPS table with lookup_type
121 = 'ARLPLB_MATCHING_OPTION' and valid values for other columns required
122 for the customized option.
123 The master program arp_process_lockbox will fetch that row and if it
124 finds it to be one of the non-standard (NOT built in core AR), it will
125 pass the control to this procedure with the corresponding lookup_code
126 in your database.
127 The procedure should return a string that Dynamic SQL can use to open and
128 parse a cursor. You need to create this SQL string to replace the string
129 named 'p_cursor_string'. (see below an example).
130
131 Your string should have the following restrictions:
132 1. The only allowed bind variables are as follows:
133 a. b_current_matching_number
134 At execution time, this will get a value of a matching_number passed
135 in the overflow or payment record.
136 b. b_current_matching_date
137 At the execution time, this will get a value of a matching_date passed
138 in the overflow or payment record.
139 c. b_current_installment
140 At the execution time, this will get a value of installment num passed
141 in overflow or payment record.
142 d. b_customer_id
143 If the customer is identified using customer number or MICR number,
144 the program will enforce that the matching_number be of the same
145 customer (with an exception of a value 'Y' in b_pay_unrelated
146 _customers, see below).
147 e. b_pay_unrelated_customers
148 At the time of submitting the lockbox process, the user is prompted to
149 enter whether to allow payment through unrelated customers or not.
150 This variable will get a value 'Y' or 'N' based on that entry.
151 f. b_lockbox_matching_option
152 The value of this variable will match to the value of ar_lookups.lookup
153 _code. It is also stored in ar_customer_profiles.lockbox_matching
154 _option and in ar_lockboxes.lockbox_matching_option.
155 g. b_use_matching_date
156 This variable will be assigned a value 'NEVER', 'ALWAYS' or
157 'FOR_DUPLICATES', depending upon the setup in your lockbox
158 (in ar_lockboxes).
159
160 2. If you are customizing lockbox using this procedure, you have to make
161 sure that this procedure retrns a string that can create a valid cursor
162 and that the SQL returns one and only one row (neither zero nor more
163 than one).
164
165 3. The program expects three return values from proposed SQL necessarily
166 in the same order:
167 1. Customer_Id (NUMBER(15))
168 2. Invoice Number (VARCHAR2(20))
169 3. Invoice Date (DATE)
170
171 4. The program expects that the combination of Invoice Number and invoice
172 date is unique in ar_payment_schedules.
173
174 5. You need not use all the bind variables that are provided in your
175 proposed SQL.
176 For example your SQL string can be like this:
177
178 p_cursor_string := 'select ct.customer_id, ct.trx_number, ct.trx_date ' ||
179 'from custom_table ct ' ||
180 'where ct.matching_number = :b_current_matching_number ' ||
181 ' and ct.matching_date = :b_current_matching_date ';
182
183 6. The SQL must be such that, if it does not match with given matching
184 number and matching date (optional), it must return:
185 customer_id = -9999,
186 trx_number = null,
187 trx_date = null.
188
189 7. In case it matches to multiple customers, but the same trx numbers
190 it must return customer_id = -7777. trx_number and trx_date will
191 be ignored in this case.
192
193 8. The program calling this procedure does not expect it to return
194 any errors, as the definition of a cursor is a one-time procedure
195 and if done carefully should not error.
196
197
198 ----------------------------------------------------------------------------*/
199 PROCEDURE CURSOR_FOR_MATCHING_RULE(p_matching_option IN VARCHAR2,
200 p_cursor_string OUT NOCOPY VARCHAR2) IS
201 BEGIN
202 IF PG_DEBUG in ('Y', 'C') THEN
203 arp_util.debug('arp_lockbox_hook.cursor_for_matching_rule()+');
204 END IF;
205 p_cursor_string := 'select -9999, NULL, NULL from dual';
206 IF PG_DEBUG in ('Y', 'C') THEN
207 arp_util.debug('arp_lockbox_hook.cursor_for_matching_rule()+');
208 END IF;
209 RETURN;
210 END cursor_for_matching_rule;
211 --
212 /*----------------------------------------------------------------------------
213 Procedure
214
215 cursor_for_custom_llca
216
217 Oracle Receivables supplies the Packaged Procedure
218 arp_lockbox_Hook.cursor_for_custom_llca, which can be used to :-
219
220 1. Customise lockbox to add any matching rules of customers chioce.
221 2. Earlier custom matching rule functionality provided in the PROCEDURE
222 CURSOR_FOR_MATCHING_RULE, has a limitaion that each custom matching number
223 provided in tha lockbox data file should be resolved to only one invoice
224 number.Now this new custom hook is capable to handle, any mutiple invoice
225 numbers, to which the custom matching number is resolved to.
226 3. Customise lockbox to apply receipts at line level.Customers can provide the
227 invoice line details to which this receipt is to be applied, in this procedure.
228
229
230 Usage :-
231 --------
232 The pl/sql table p_unresolved_inv_array is populated with, one record for each
233 matching number in the datafile, and each record containing the values,
234 1. matching_number given in the lockbox datafile,
235 2. item_number of the item in which it was applied,
236 3. and the amount_applied on the matching_number.
237
238 Customers can write custom code which accepts any or all of the values
239 in (matching_number, item_number, amount_applied), from the pl/sql table
240 unresolved_invoice_array. And this code should fetch the invoice_number(s)
241 and the corresponding amount_applied on that invoice. And this data is to
242 be populated in the pl/sql table p_invoice_array, one row for each resolved
243 invoice number, with structure as below :-
244
245 TYPE invoice_record IS RECORD
246 (
247 matching_number varchar2(15), -- Unresolved matching_number
248 -- from the datafile.
249 item_number number, -- Item number from datafile.
250 invoice_number varchar2(15), -- Resolved invoice number.
251 amount_applied number -- Amount to be applied on this invoice.
252 amount_applied_from NUMBER, -- Amount applied in Receipt Currency
253 trans_to_receipt_rate NUMBER, -- Exchange rate
254 invoice_currency_code VARCHAR2(15), -- Invoice Currency Code
255 batch_name VARCHAR2(25), -- Batch Name, if multiple batches are present
256 record_type VARCHAR2(2) -- record type (No need to populate this value)
257 ) ;
258 TYPE invoice_array IS TABLE OF invoice_record INDEX BY BINARY_INTEGER ;
259
260 For doing a line level cash application, a pl/sql table p_line_array, also need
261 to be populated additionally, along with the resolved invoice details in the
262 following format, for each resolved invoice number, one row for each invoice line.
263
264 TYPE line_record IS RECORD
265 (
266 item_number number,
267 batch_name varchar2(30),
268 invoice_number varchar2(50),
269 apply_to varchar2(150),
270 amount_applied number,
271 allocated_receipt_amount number,
272 line_amount number,
273 tax_amount number,
274 freight number,
275 charges number
276 ) ;
277 TYPE line_array IS TABLE OF line_record INDEX BY BINARY_INTEGER ;
278
279 Guidelines to populate the line details pl/sql table p_line_array :-
280 --------------------------------------------------------------------
281 1. item_number = item number on the data file.
282 Item number of resolved/matching number inside the data file.
283 2. Batch_name = Batch name of the invoice.
284 Use this in case of batches present in the data file.
285 Otherwise don't populate.
286 3. invoice_number = Resolved invoice number.
287 4. apply _to = line_number of invoice line to apply.
288 (or)
289 'FREIGHT'/'CHARGES' to apply to
290 freight or charges of the invoice.
291 5. amount_applied = total amount applied to this line.
292 6. allocated_receipt_Amount = Amount Applied in receipt Currency
293 7. line_amount and tax_amount.
294 a. Should be populated only if apply_to = line_number.
295 b. Should be either both null or either both populated.
296 c. If both populated, then amount_applied = line_amount + tax_amount.
297 d. If both null we prorate amount_applied for line and tax of the line.
298 8. freight.
299 a. should be populated only if apply_to = 'FREIGHT'.
300 b. Can be null or populated.
301 c. If populated then amount_applied = freight.
302 d. If null, will be any way defaulted to amount_applied.
303 e. If invoice has line level freight then we prorate this 'FREIGHT'
304 applied amount between the freight amounts of all lines.
305 9. charges.
306 -- All the rules are same as freight column for this column too.
307
308 Points to note :-
309 -----------------
310 1. If custom code do not want to resolve any particular matching_numbers from the
311 data file, they can just be simply ignored and it will be processed, as standard
312 lockbox matching number. Anyway if some matching number is to ignored by the
313 custom code, it should already be a invoice number, else will be reported as
314 invalid number by,lockbox validation.
315
316 2. If line level details are to be given to for matching number which custom code
317 dont want to resolve, i.e already an invoice number then it should compulsarily
318 be treated as resolved number, where resolved invoice number after resolving it
319 will be same as it.
320
321 3. Custom code should return only invoice numbers after resolving a matching number
322 for all the matching numbers,if llca is required, i.e p_line_array has
323 atleast one row.
324
325 4. If llca is not needed, i.e if p_line_array is not populated at all then,
326 custom code can return purchase order or sales order or CBI numbers, but
327 all resolved numbers should be of same type of document.
328
329 5. For cross currency application, either of the trans_to_receipt_rate or amount_
330 applied_from must be provided, if not provided with the matching number.
331
332 6. If trans_to_receipt_rate/invoice_currency_code/amount_applied_from is/are
333 supplied with the matching number, then make sure that all the resolved
334 numbers for this matching number belong to the same currency code(=invoice_
335 currency_code with the matching number, if given).
336 ----------------------------------------------------------------------------*/
337 PROCEDURE CURSOR_FOR_CUSTOM_LLCA( p_unresolved_inv_array IN OUT NOCOPY arp_lockbox_hook_pvt.invoice_array,
338 p_invoice_array IN OUT NOCOPY arp_lockbox_hook_pvt.invoice_array,
339 p_line_array IN OUT NOCOPY arp_lockbox_hook_pvt.line_array ) IS
340
341 /*******************PSEUDO CODE************************************************
342 Declare cursor to fetch receipt applications.
343 Declare cusrsor to fetch line level application details.
344 ********************PSEUDO CODE***********************************************/
345
346 BEGIN
347 IF PG_DEBUG in ('Y', 'C') THEN
348 arp_util.debug('arp_lockbox_hook.cursor_for_custom_llca()+');
349 END IF;
350
351 /***********************PSEUDO CODE*****************************************
352 Loop till last record in p_unresolved_inv_array
353
354 Loop in cursor to fetch receipt applications.
355
356 Put one record in p_invoice_array for each application.
357
358 Loop in cursor to fetch line level application details.
359
360 Put one record in p_line_array for each line detail.
361
362 End Loop.
363
364 End Loop.
365
366 End Loop.
367 ************************PSEUDO CODE****************************************/
368 IF PG_DEBUG in ('Y', 'C') THEN
369 arp_util.debug('arp_lockbox_hook.cursor_for_custom_llca()-');
370 END IF;
371 RETURN;
372 END cursor_for_custom_llca;
373 --
374 END arp_lockbox_hook;
[Home] [Help]