[Home] [Help]
PACKAGE BODY: APPS.AR_ADD_FETCH_SELECT
Source
4
1 package body AR_ADD_FETCH_SELECT AS
2 /* $Header: ARXRWAFB.pls 120.13 2010/11/18 06:57:31 vpusulur ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5 -- Cursors for On Account, Open Transactions and Credit Memo Applications
6 c_on_acct INTEGER;
7 c_open_trx INTEGER;
8 c_cm_apps INTEGER;
9
10 TYPE rectyp is RECORD (
11 row_id ROWID,
12 cash_receipt_id NUMBER,
13 customer_trx_id NUMBER,
14 cm_customer_trx_id NUMBER,
15 last_update_date DATE,
16 last_updated_by NUMBER,
17 creation_date DATE,
18 created_by NUMBER,
19 last_update_login NUMBER,
20 program_application_id NUMBER,
21 program_id NUMBER,
22 program_update_date DATE,
23 request_id NUMBER,
24 receipt_number VARCHAR2(100),
25 applied_flag VARCHAR2(100),
26 customer_id NUMBER,
27 customer_name VARCHAR2(100),
28 customer_number VARCHAR2(100),
29 trx_number VARCHAR2(100),
30 installment NUMBER,
31 amount_applied NUMBER,
32 amount_applied_from NUMBER,
33 trans_to_receipt_rate NUMBER,
34 discount NUMBER,
35 discounts_earned NUMBER,
36 discounts_unearned NUMBER,
37 discount_taken_earned NUMBER,
38 discount_taken_unearned NUMBER,
39 amount_due_remaining NUMBER,
40 due_date DATE,
41 status VARCHAR2(100),
42 term_id NUMBER,
43 trx_class_name VARCHAR2(100),
44 trx_class_code VARCHAR2(100),
45 trx_type_name VARCHAR2(100),
46 cust_trx_type_id NUMBER,
47 trx_date DATE,
48 location_name VARCHAR2(100),
49 bill_to_site_use_id NUMBER,
50 days_late NUMBER,
51 line_number NUMBER,
52 customer_trx_line_id NUMBER,
53 apply_date DATE,
54 gl_date DATE,
55 gl_posted_date DATE,
56 reversal_gl_date DATE,
57 exchange_rate NUMBER,
58 invoice_currency_code VARCHAR2(15),
59 amount_due_original NUMBER,
60 amount_in_dispute NUMBER,
61 amount_line_items_original NUMBER,
62 acctd_amount_due_remaining NUMBER,
63 acctd_amount_applied_to NUMBER,
64 acctd_amount_applied_from NUMBER,
65 exchange_gain_loss NUMBER,
66 discount_remaining NUMBER,
67 calc_discount_on_lines_flag VARCHAR2(100),
68 partial_discount_flag VARCHAR2(100),
69 allow_overapplication_flag VARCHAR2(100),
70 natural_application_only_flag VARCHAR2(100),
71 creation_sign VARCHAR2(100),
72 applied_payment_schedule_id NUMBER,
73 ussgl_transaction_code VARCHAR2(100),
74 ussgl_transaction_code_context VARCHAR2(100),
75 purchase_order VARCHAR2(50),
76 trx_doc_sequence_id NUMBER,
77 trx_doc_sequence_value VARCHAR2(100),
78 trx_batch_source_name VARCHAR2(100),
79 amount_adjusted NUMBER,
80 amount_adjusted_pending NUMBER,
81 amount_line_items_remaining NUMBER,
82 freight_original NUMBER,
83 freight_remaining NUMBER,
84 receivables_charges_remaining NUMBER,
85 tax_original NUMBER,
86 tax_remaining NUMBER,
87 selected_for_receipt_batch_id NUMBER,
88 receivable_application_id NUMBER,
89 attribute_category VARCHAR2(50),
90 attribute1 VARCHAR2(150),
91 attribute2 VARCHAR2(150),
92 attribute3 VARCHAR2(150),
93 attribute4 VARCHAR2(150),
94 attribute5 VARCHAR2(150),
95 attribute6 VARCHAR2(150),
96 attribute7 VARCHAR2(150),
97 attribute8 VARCHAR2(150),
98 attribute9 VARCHAR2(150),
99 attribute10 VARCHAR2(150),
100 attribute11 VARCHAR2(150),
101 attribute12 VARCHAR2(150),
105 trx_billing_number VARCHAR2(30),
102 attribute13 VARCHAR2(150),
103 attribute14 VARCHAR2(150),
104 attribute15 VARCHAR2(150),
106 global_attribute_category VARCHAR2(50),
107 global_attribute1 VARCHAR2(150),
108 global_attribute2 VARCHAR2(150),
109 global_attribute3 VARCHAR2(150),
110 global_attribute4 VARCHAR2(150),
111 global_attribute5 VARCHAR2(150),
112 global_attribute6 VARCHAR2(150),
113 global_attribute7 VARCHAR2(150),
114 global_attribute8 VARCHAR2(150),
115 global_attribute9 VARCHAR2(150),
116 global_attribute10 VARCHAR2(150),
117 global_attribute11 VARCHAR2(150),
118 global_attribute12 VARCHAR2(150),
119 global_attribute13 VARCHAR2(150),
120 global_attribute14 VARCHAR2(150),
121 global_attribute15 VARCHAR2(150),
122 global_attribute16 VARCHAR2(150),
123 global_attribute17 VARCHAR2(150),
124 global_attribute18 VARCHAR2(150),
125 global_attribute19 VARCHAR2(150),
126 global_attribute20 VARCHAR2(150),
127 transaction_category VARCHAR2(150),
128 trx_gl_date DATE,
129 comments VARCHAR2(240), -- bug 2662270
130 receivables_trx_id NUMBER, --
131 rec_activity_name VARCHAR2(50), --
132 application_ref_id NUMBER, -- CM refunds
133 application_ref_num VARCHAR2(150), --
134 application_ref_type VARCHAR2(30), --
135 application_ref_type_meaning VARCHAR2(80) --
136 );
137
138 open_trx_row rectyp;
139 on_acct_row rectyp;
140 cm_apps_row rectyp;
141
142 l_app_gl_date_default VARCHAR2(30):= fnd_profile.value('AR_APPLICATION_GL_DATE_DEFAULT');
143
144
145 /*===========================================================================+
146 | FUNCTION
147 | CHANGE_ORDER_BY
148 |
149 | DESCRIPTION
150 |
151 | SCOPE - PUBLIC
152 |
153 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
154 |
155 | ARGUMENTS : IN:
156 |
157 | OUT:
158 |
159 | RETURNS : NONE
160 |
161 | NOTES
162 |
163 | MODIFICATION HISTORY
164 | 25-AUG-97 Joan Zaman Created.
165 |
166 +===========================================================================*/
167 FUNCTION CHANGE_ORDER_BY (p_order_by VARCHAR2 ) RETURN VARCHAR2 IS
168
169 l_order_by VARCHAR2(2000);
170
171 BEGIN
172
173 l_order_by := p_order_by;
174
175 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('cash_receipt_id'), '2');
176 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('customer_trx_id'), '3');
177 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('cm_customer_trx_id'), '4');
178 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('last_update_date'), '5');
179 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('last_updated_by'), '6');
180 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('creation_date'), '7');
181 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('created_by'), '8');
182 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('last_update_login'), '9');
183 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('program_application_id'), '10');
184 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('program_id'), '11');
185 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('program_update_date'), '12');
186 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('request_id'), '13');
187 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('receipt_number'), '14');
188 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('applied_flag'), '15');
189 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('customer_id'), '16');
190 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('customer_name'), '17');
191 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('customer_number'), '18');
192 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('trx_number'), '19');
193 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('installment'), '20');
194 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('amount_applied'), '21');
195 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('amount_applied_from'), '22');
196 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('trans_to_receipt_rate'), '23');
197 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('discount'), '24');
198 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('discounts_earned'), '25');
199 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('discounts_unearned'), '26');
200 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('discount_taken_earned'), '27');
201 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('discount_taken_unearned'), '28');
202 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('amount_due_remaining'), '29');
203 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('due_date'), '30');
204 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('status'), '31');
205 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('term_id'), '32');
206 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('trx_class_name'), '33');
207 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('trx_class_code'), '34');
208 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('trx_type_name'), '35');
209 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('cust_trx_type_id'), '36');
210 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('trx_date'), '37');
211 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('location_name'), '38');
212 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('bill_to_site_use_id'), '39');
213 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('days_late'), '40');
214 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('line_number'), '41');
215 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('customer_trx_line_id'), '42');
219 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('reversal_gl_date'), '46');
216 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('apply_date'), '43');
217 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('gl_date'), '44');
218 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('gl_posted_date'), '45');
220 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('exchange_rate'), '47');
221 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('invoice_currency_code'), '48');
222 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('amount_due_original'), '49');
223 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('amount_in_dispute'), '50');
224 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('amount_line_items_original'), '51');
225 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('acctd_amount_due_remaining'), '52');
226 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('acctd_amount_applied_to'), '53');
227 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('acctd_amount_applied_from'), '54');
228 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('exchange_gain_loss'), '55');
229
230 /* Bug2680500 Deleted
231 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('amount_exch_rate_diff_base'), '56');
232 */
233
234 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('discount_remaining'), '56');
235 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('calc_discount_on_lines_flag'), '57');
236 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('partial_discount_flag'), '58');
237 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('allow_overapplication_flag'), '59');
238 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('natural_application_only_flag'), '60');
239 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('creation_sign'), '61');
240 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('applied_payment_schedule_id'), '62');
241 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('ussgl_transaction_code'), '63');
242 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('ussgl_transaction_code_context'), '64');
243 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('purchase_order'), '65');
244 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('trx_doc_sequence_id'), '66');
245 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('trx_doc_sequence_value'), '67');
246 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('trx_batch_source_name'), '68');
247 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('amount_adjusted'), '69');
248 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('amount_adjusted_pending'), '70');
249 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('amount_line_items_remaining'), '71');
250 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('freight_original'), '72');
251 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('freight_remaining'), '73');
252 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('receivables_charges_remaining'), '74');
253 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('tax_original'), '75');
254 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('tax_remaining'), '76');
255 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('selected_for_receipt_batch_id'), '77');
256 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('receivable_application_id'), '78');
257 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute_category'), '79');
258 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute1'), '80');
259 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute2'), '81');
260 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute3'), '82');
261 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute4'), '83');
262 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute5'), '84');
263 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute6'), '85');
264 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute7'), '86');
265 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute8'), '87');
266 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute9'), '88');
267 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute10'), '89');
268 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute11'), '90');
269 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute12'), '91');
270 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute13'), '92');
271 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute14'), '93');
272 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('attribute15'), '94');
273 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('trx_billing_number'), '95');
274 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute_category'), '96');
275 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute1'), '97');
276 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute2'), '98');
277 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute3'), '99');
278 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute4'), '100');
279 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute5'), '101');
280 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute6'), '102');
281 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute7'), '103');
282 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute8'), '104');
283 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute9'), '105');
284 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute10'), '106');
285 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute11'), '107');
286 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute12'), '108');
287 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute13'), '109');
288 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute14'), '110');
289 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute15'), '111');
290 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute16'), '112');
291 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute17'), '113');
292 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute18'), '114');
293 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute19'), '115');
294 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('global_attribute20'), '116');
295 -- bug3098721
296 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('transaction_category'), '117');
297 L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('trx_gl_date'), '118');
301 END;
298
299 return (L_ORDER_BY);
300
302
303 /*===========================================================================+
304 | PROCEDURE
305 | ON_SELECT
306 |
307 | DESCRIPTION
308 |
309 | This procedure opens and executes the appropriate cursor.
310 |
311 | SCOPE - PUBLIC
312 |
313 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
314 |
315 | ARGUMENTS : IN:
316 |
317 | OUT:
318 |
319 | RETURNS : NONE
320 |
321 | NOTES
322 |
323 | MODIFICATION HISTORY
324 | 25-AUG-97 Joan Zaman Created.
325 | 08-JAN-98 Karen Murphy See list of changes at the package level.
326 | 14-MAY-98 Guat Eng Tan Bug #655455. Added p_trx_bill_number_find
327 | parameter to on_select procedure. Added
328 | code to include the billing number restriction
329 | when selecting for open invoices.
330 | 24-Aug-01 H Yoshihara Bug 1930411: add p_related_cust_flag
331 | argument to ON_SELECT procedure.
332 |
333 +===========================================================================*/
334
335
336 PROCEDURE on_select (
337 p_select_type VARCHAR2
338 ,p_apply_date DATE
339 ,p_receipt_gl_date DATE
340 ,p_customer_id NUMBER
341 ,p_bill_to_site_use_id NUMBER
342 ,p_receipt_currency VARCHAR2
343 ,p_cm_customer_trx_id NUMBER
344 ,p_trx_type_name_find VARCHAR2
345 ,p_due_date_find VARCHAR2
346 ,p_trx_date_find VARCHAR2
347 ,p_amt_due_rem_find VARCHAR2
348 ,p_trx_number_find VARCHAR2
349 ,p_include_disputed VARCHAR2
350 ,p_include_cross_curr VARCHAR2
351 ,p_inv_class VARCHAR2
352 ,p_chb_class VARCHAR2
353 ,p_cm_class VARCHAR2
354 ,p_dm_class VARCHAR2
355 ,p_dep_class VARCHAR2
356 ,p_status VARCHAR2
357 ,p_order_by VARCHAR2
358 ,p_trx_bill_number_find VARCHAR2
359 ,p_purchase_order_find VARCHAR2 default NULL
360 ,p_transaction_category_find VARCHAR2 default NULL
361 ,p_br_class VARCHAR2 default NULL /* 01-JUN-2000 J Rautiainen BR Implementation */
362 ,p_related_cust_flag VARCHAR2 /* bug1930411 */
363 ) IS
364
365 open_trx_lng long;
366 on_acct_lng long;
367 cm_apps_lng long;
368
369 ignore number;
370
371 l_trx_number_find VARCHAR2(100);
372 l_trx_bill_number_find VARCHAR2(100);
373 l_trx_type_name_find VARCHAR2(100);
374 l_due_date_find VARCHAR2(200);
375 l_trx_date_find VARCHAR2(200);
376 l_amt_due_rem_find VARCHAR2(100);
377 l_purchase_order_find VARCHAR2(200);
378 l_transaction_category_find VARCHAR2(200);
379
380 l_cm_cust_trx_where VARCHAR2(100); -- Added for bug1821585
381
382 l_order_by VARCHAR2(2000);
383
384 BEGIN
385
386 IF PG_DEBUG in ('Y', 'C') THEN
387 arp_standard.debug( 'on_select()+' );
388 END IF;
389
390 --
391 -- Open Transactions:
392 -- Returns all of the unapplied transations for a specified
393 -- customer and selection criteria.
394 --
395 -- The select statement has been optimized to query with customer_id
396 -- (class index has been disabled on purpose).
397 -- CBO optimizations are done for best performance.
398 --
399 -- Bug 2113440 :
400 -- 1) modify Hints for Indexes : replace ra_batch_sources_u1 with ra_batch_sources_u2
401 -- replace ra_terms_u1 with ra_terms_b_u1
402 -- 2) use ra_terms_b instead of ra_terms
403
404 open_trx_lng := '
405 SELECT
406 /*+
407 FIRST_ROWS
408 INDEX(ps_inv AR_PAYMENT_SCHEDULES_N6)
409 INDEX(cust hz_cust_accounts_u1)
410 INDEX(bs ra_batch_sources_u2)
411 INDEX(ctt ra_cust_trx_types_u1)
412 INDEX(rcr hz_cust_acct_relate_n1)
413 INDEX(su hz_cust_site_uses_u1)
414 INDEX(t ra_terms_b_u1)
415 USE_NL(ps_inv)
416 USE_NL(ct)
417 USE_NL(ctt)
418 USE_NL(cust)
419 USE_NL(su)
420 USE_NL(bs)
421 USE_NL(t)
422 USE_NL(ci)
423 USE_NL(l_class)
424 PUSH_SUBQ ORDERED */
425 ps_inv.rowid row_id
426 , -1 cash_receipt_id
427 , ps_inv.customer_trx_id customer_trx_id
428 , NULL cm_customer_trx_id
429 , ps_inv.last_update_date last_update_date
430 , ps_inv.last_updated_by last_updated_by
431 , ps_inv.creation_date creation_date
432 , ps_inv.created_by created_by
433 , ps_inv.last_update_login last_update_login
434 , ps_inv.program_application_id program_application_id
435 , ps_inv.program_id program_id
436 , ps_inv.program_update_date program_update_date
437 , ps_inv.request_id request_id
438 , NULL receipt_number
439 , ''n'' applied_flag
440 , ps_inv.customer_id customer_id
444 , ps_inv.terms_sequence_number installment
441 , substrb(party.party_name,1, 50) customer_name
442 , cust.account_number customer_number
443 , ps_inv.trx_number trx_number
445 , NULL amount_applied
446 , NULL amount_applied_from
447 , NULL trans_to_receipt_rate
448 , NULL discount
449 , NULL discounts_earned
450 , NULL discounts_unearned
451 , ps_inv.discount_taken_earned discount_taken_earned
452 , ps_inv.discount_taken_unearned discount_taken_unearned
453 , ps_inv.amount_due_remaining amount_due_remaining
454 , ps_inv.due_date due_date
455 , ps_inv.status status
456 , ps_inv.term_id term_id
457 , l_class.meaning trx_class_name
458 , ps_inv.class trx_class_code
459 , ctt.name trx_type_name
460 , ctt.cust_trx_type_id cust_trx_type_id
461 , ct.trx_date trx_date
462 , su.location location_name
463 , ps_inv.customer_site_use_id bill_to_site_use_id
464 , NULL days_late
465 , NULL line_number
466 , NULL customer_trx_line_id
467 , :p_apply_date apply_date
468 , arp_view_constants.get_default_gl_date(
469 greatest(ps_inv.gl_date,
470 :p_receipt_gl_date,
471 DECODE(NVL(:l_app_gl_date_default, ''INV_REC_DT''),
472 ''INV_REC_SYS_DT'', sysdate,
473 ''INV_REC_DT'', ps_inv.gl_date,
474 ps_inv.gl_date))) gl_date
475 , NULL gl_posted_date
476 , NULL reversal_gl_date
477 , ps_inv.exchange_rate exchange_rate
478 , ps_inv.invoice_currency_code invoice_currency_code
479 , ps_inv.amount_due_original amount_due_original
480 , ps_inv.amount_in_dispute amount_in_dispute
481 , ps_inv.amount_line_items_original amount_line_items_original
482 , ps_inv.acctd_amount_due_remaining acctd_amount_due_remaining
483 , NULL acctd_amount_applied_to
484 , NULL acctd_amount_applied_from
485 , NULL exchange_gain_loss
486 , ps_inv.discount_remaining discount_remaining
487 , t.calc_discount_on_lines_flag calc_discount_on_lines_flag
488 , t.partial_discount_flag partial_discount_flag
489 , ctt.allow_overapplication_flag allow_overapplication_flag
490 , ctt.natural_application_only_flag natural_application_only_flag
491 , ctt.creation_sign creation_sign
492 , ps_inv.payment_schedule_id applied_payment_schedule_id
493 , ct.default_ussgl_transaction_code ussgl_transaction_code
494 , ct.default_ussgl_trx_code_context ussgl_transaction_code_context
495 , ct.purchase_order purchase_order
496 , ct.doc_sequence_id trx_doc_sequence_id
497 , ct.doc_sequence_value trx_doc_sequence_value
498 , bs.name trx_batch_source_name
499 , ps_inv.amount_adjusted amount_adjusted
500 , ps_inv.amount_adjusted_pending amount_adjusted_pending
501 , ps_inv.amount_line_items_remaining amount_line_items_remaining
502 , ps_inv.freight_original freight_original
503 , ps_inv.freight_remaining freight_remaining
504 , ps_inv.receivables_charges_remaining receivables_charges_remaining
505 , ps_inv.tax_original tax_original
506 , ps_inv.tax_remaining tax_remaining
507 , ps_inv.selected_for_receipt_batch_id selected_for_receipt_batch_id
508 , NULL receivable_application_id
509 , NULL attribute_category
510 , NULL attribute1
511 , NULL attribute2
512 , NULL attribute3
513 , NULL attribute4
514 , NULL attribute5
515 , NULL attribute6
516 , NULL attribute7
517 , NULL attribute8
518 , NULL attribute9
519 , NULL attribute10
520 , NULL attribute11
521 , NULL attribute12
522 , NULL attribute13
523 , NULL attribute14
524 , NULL attribute15
525 , ci.cons_billing_number trx_billing_number
526 , NULL global_attribute_CATEGORY
527 , NULL global_attribute1
528 , NULL global_attribute2
529 , NULL global_attribute3
530 , NULL global_attribute4
531 , NULL global_attribute5
532 , NULL global_attribute6
533 , NULL global_attribute7
534 , NULL global_attribute8
535 , NULL global_attribute9
536 , NULL global_attribute10
537 , NULL global_attribute11
538 , NULL global_attribute12
542 , NULL global_attribute16
539 , NULL global_attribute13
540 , NULL global_attribute14
541 , NULL global_attribute15
543 , NULL global_attribute17
544 , NULL global_attribute18
545 , NULL global_attribute19
546 , NULL global_attribute20
547 , ctt.attribute10 transaction_category -- ARTA
548 , ps_inv.gl_date trx_gl_date
549 FROM
550 ar_payment_schedules ps_inv
551 , ra_customer_trx ct
552 , ra_cust_trx_types ctt
553 , hz_cust_accounts cust
554 , hz_parties party
555 , hz_cust_site_uses su
556 , ra_batch_sources bs
557 , ra_terms_b t
558 , ar_cons_inv ci
559 , ar_lookups l_class
560 WHERE ps_inv.selected_for_receipt_batch_id IS NULL
561 /* 08-JUL-2000 J Rautiainen BR Implementation */
562 AND ps_inv.reserved_type IS NULL
563 AND ps_inv.reserved_value IS NULL
564 AND ps_inv.class||'''' NOT IN(''GUAR'',''PMT'')
565 AND ps_inv.class = l_class.lookup_code
566 AND l_class.lookup_type = ''INV/CM''
567 AND ps_inv.customer_trx_id = ct.customer_trx_id
568 AND ps_inv.customer_site_use_id = nvl(:p_bill_to_site_use_id, ps_inv.customer_site_use_id)
569 AND ps_inv.status = ''OP''
570 AND bs.batch_source_id = ct.batch_source_id
571 AND ps_inv.cust_trx_type_id = ctt.cust_trx_type_id
572 AND ps_inv.customer_id = cust.cust_account_id
573 AND cust.party_id = party.party_id
574 AND ps_inv.customer_site_use_id= su.site_use_id
575 AND ci.cons_inv_id(+) = ps_inv.cons_inv_id
576 -- Term id for credit memos is null
577 AND ps_inv.term_id = t.term_id(+)
578 AND ps_inv.customer_id IN
579 (SELECT rcr.RELATED_CUST_ACCOUNT_ID
580 FROM hz_cust_acct_relate RCR
581 WHERE RCR.CUST_ACCOUNT_ID = :p_customer_id
582 AND RCR.STATUS=''A''
583 AND RCR.BILL_TO_FLAG=''Y''
584 -- bug1930411 add flag whether or not related customers are selected
585 AND :p_related_cust_flag = ''Y''
586 UNION
587 SELECT :p_customer_id
588 FROM SYS.DUAL)
589 AND ps_inv.invoice_currency_code =
590 decode(:p_include_cross_curr,''Y'',
591 ps_inv.invoice_currency_code
592 ,:p_receipt_currency)
593 AND ps_inv.status = NVL(:p_status,ps_inv.status)
594 /* 01-JUN-2000 J Rautiainen BR Implementation
595 * Added BR class */
596 AND ps_inv.class NOT IN (nvl(:p_inv_class,''XX''),nvl(:p_chb_class,''XX''),
597 nvl(:p_dep_class,''XX''),nvl(:p_cm_class,''XX''),
598 nvl(:p_dm_class,''XX''), nvl(:p_br_class,''XX''))
599 AND NVL(ps_inv.amount_in_dispute,0) = DECODE ( :p_include_disputed
600 , ''N'' , 0
601 , NVL(ps_inv.amount_in_dispute,0) )
602 ';
603
604 --
605 -- On Account Row:
606 -- Returns single "On Account" row from payment schedules. This is
607 -- returned as the last row in Mass Apply and Preview.
608 --
609 on_acct_lng := '
610 -- This select statement will get one row (On-Account)
611 SELECT
612 ps_inv.rowid row_id
613 , -1 cash_receipt_id
614 , ps_inv.customer_trx_id customer_trx_id
615 , ps_inv.last_update_date last_update_date
616 , ps_inv.last_updated_by last_updated_by
617 , ps_inv.creation_date creation_date
618 , ps_inv.created_by created_by
619 , ps_inv.last_update_login last_update_login
620 , ps_inv.program_application_id program_application_id
621 , ps_inv.program_id program_id
622 , ps_inv.program_update_date program_update_date
623 , ps_inv.request_id request_id
624 , ''n'' applied_flag
625 , -1 customer_id
626 , ps_inv.trx_number trx_number
627 , 0 discounts_earned
628 , 0 discounts_unearned
629 , 0 discount_taken_earned
630 , 0 discount_taken_unearned
631 , ps_inv.amount_due_remaining amount_due_remaining
632 , ps_inv.status status
633 , ps_inv.term_id term_id
634 , -1 bill_to_site_use_id
635 , arp_view_constants.get_apply_date apply_date
636 , arp_view_constants.get_default_gl_date(
637 greatest(ps_inv.gl_date,
638 :p_receipt_gl_date,
639 DECODE(NVL(:l_app_gl_date_default, ''INV_REC_DT''),
640 ''INV_REC_SYS_DT'', sysdate,
641 ''INV_REC_DT'', ps_inv.gl_date,
642 ps_inv.gl_date))) gl_date
643 , :p_receipt_currency invoice_currency_code
644 , ps_inv.amount_due_original amount_due_original
645 , ps_inv.amount_in_dispute amount_in_dispute
646 , ps_inv.amount_line_items_original amount_line_items_original
647 , ps_inv.discount_remaining discount_remaining
651 WHERE
648 , ps_inv.payment_schedule_id applied_payment_schedule_id
649 FROM
650 ar_payment_schedules ps_inv
652 ps_inv.payment_schedule_id = -1
653 ';
654
655 --
656 -- On Account Credit Memo Applications:
657 -- Returns all of the existing applications for an On Account Credit Memo.
658 -- These are viewed through the Transaction Workbench.
659 --
660 /* Start of bug fix 1821585.
661 Dynamic query building:
662 If the user passes the customer_trx_id,then append the where clause with
663 this filter.Else, don't append.
664 If the cust_trx_id is provided, the index on it will be used by query,
665 thus improving the performance.
666 */
667
668 IF p_cm_customer_trx_id IS NOT NULL THEN
669 l_cm_cust_trx_where := 'AND ps_cm.customer_trx_id = :p_cm_customer_trx_id
670 ';
671 ELSE
672 l_cm_cust_trx_where := NULL;
673 END IF;
674
675 --Fixed bug 767029 by adding hints USE_NL
676
677 cm_apps_lng := '
678 SELECT
679 /*+
680 INDEX(app AR_RECEIVABLE_APPLICATIONS_N8 AR_RECEIVABLE_APPLICATIONS_N2)
681 INDEX(ps_inv ar_payment_schedules_u1 ar_payment_schedules_n2 ar_payment_schedules_n6)
682 INDEX(ps_cm ar_payment_schedules_n2)
683 INDEX(cust hz_cust_accounts_u1)
684 INDEX(ct ra_customer_trx_u1)
685 INDEX(bs ra_batch_sources_u2)
686 INDEX(ctt ra_cust_trx_types_u1)
687 INDEX(su hz_cust_site_uses_u1)
688 INDEX(t ra_terms_u1)
689 USE_NL(ps_cm)
690 USE_NL(app)
691 USE_NL(ps_inv)
692 USE_NL(cust)
693 USE_NL(ct)
694 USE_NL(ctl)
695 USE_NL(bs)
696 USE_NL(su)
697 USE_NL(ci)
698 USE_NL(l_class)
699 ORDERED PUSH_SUBQ */
700 app.rowid row_id
701 , -2 cash_receipt_id
702 , ps_inv.customer_trx_id customer_trx_id
703 , ps_cm.customer_trx_id cm_customer_trx_id
704 , app.last_update_date last_update_date
705 , app.last_updated_by last_updated_by
706 , app.creation_date creation_date
707 , app.created_by created_by
708 , app.last_update_login last_update_login
709 , app.program_application_id program_application_id
710 , app.program_id program_id
711 , app.program_update_date program_update_date
712 , app.request_id request_id
713 , ps_cm.trx_number receipt_number
714 , ''Y'' applied_flag
715 , ps_inv.customer_id customer_id
716 , substrb(party.party_name,1,50) customer_name
717 , cust.account_number customer_number
718 , ps_inv.trx_number trx_number
719 , DECODE(SIGN(ps_inv.payment_schedule_id),-1,NULL,ps_inv.terms_sequence_number) installment
720 , app.amount_applied amount_applied
721 , app.amount_applied amount_applied_from
722 , NULL trans_to_receipt_rate
723 , NULL discount
724 , NULL discounts_earned
725 , NULL discounts_unearned
726 , ps_inv.discount_taken_earned discount_taken_earned
727 , ps_inv.discount_taken_unearned discount_taken_unearned
728 , TO_NUMBER(DECODE(SIGN(ps_inv.payment_schedule_id),-1,NULL,ps_inv.amount_due_remaining)) amount_due_remaining
729 , DECODE(SIGN(ps_inv.payment_schedule_id),-1,TO_DATE(NULL), ps_inv.due_date) due_date
730 , ps_inv.status status
731 , ps_inv.term_id term_id
732 -- , l_class.meaning trx_class_name
733 , DECODE(SIGN(ps_inv.payment_schedule_id),-1,NULL, arpt_sql_func_util.get_lookup_meaning(''INV/CM'', ps_inv.CLASS)) trx_class_name
734 , ps_inv.class trx_class_code
735 , ctt.name trx_type_name
736 , ctt.cust_trx_type_id cust_trx_type_id
737 , ct.trx_date trx_date
738 , su.location location_name
739 , ct.bill_to_site_use_id bill_to_site_use_id
740 , DECODE(SIGN(ps_inv.payment_schedule_id),-1,NULL,to_number(app.apply_date-TRUNC(ps_inv.due_date))) days_late
741 , ctl.line_number line_number
742 , ctl.customer_trx_line_id customer_trx_line_id
743 , app.apply_date apply_date
744 , app.gl_date gl_date
745 , app.gl_posted_date gl_posted_date
746 , app.reversal_gl_date reversal_gl_date
747 , ps_inv.exchange_rate exchange_rate
748 , DECODE(SIGN(ps_inv.payment_schedule_id),-1,ps_cm.invoice_currency_code, ps_inv.invoice_currency_code) invoice_currency_code
749 , ps_inv.amount_due_original amount_due_original
750 , ps_inv.amount_in_dispute amount_in_dispute
751 , ps_inv.amount_line_items_original amount_line_items_original
752 , TO_NUMBER(DECODE(SIGN(ps_inv.payment_schedule_id),-1,NULL, ps_inv.acctd_amount_due_remaining)) acctd_amount_due_remaining
753 , app.acctd_amount_applied_to acctd_amount_applied_to
754 , app.acctd_amount_applied_from acctd_amount_applied_from
755 , app.acctd_amount_applied_from
756 - NVL(app.acctd_amount_applied_to,app.acctd_amount_applied_from)
757 exchange_gain_loss
758 , ps_inv.discount_remaining discount_remaining
759 , t.calc_discount_on_lines_flag calc_discount_on_lines_flag
763 , ctt.creation_sign creation_sign
760 , t.partial_discount_flag partial_discount_flag
761 , ctt.allow_overapplication_flag allow_overapplication_flag
762 , ctt.natural_application_only_flag natural_application_only_flag
764 , ps_inv.payment_schedule_id applied_payment_schedule_id
765 , app.ussgl_transaction_code ussgl_transaction_code
766 , app.ussgl_transaction_code_context ussgl_transaction_code_context
767 , ct.purchase_order purchase_order
768 , ct.doc_sequence_id trx_doc_sequence_id
769 , ct.doc_sequence_value trx_doc_sequence_value
770 , bs.name trx_batch_source_name
771 , ps_inv.amount_adjusted amount_adjusted
772 , ps_inv.amount_adjusted_pending amount_adjusted_pending
773 , ps_inv.amount_line_items_remaining amount_line_items_remaining
774 , ps_inv.freight_original freight_original
775 , ps_inv.freight_remaining freight_remaining
776 , ps_inv.receivables_charges_remaining receivables_charges_remaining
777 , ps_inv.tax_original tax_original
778 , ps_inv.tax_remaining tax_remaining
779 , ps_inv.selected_for_receipt_batch_id selected_for_receipt_batch_id
780 , app.receivable_application_id receivable_application_id
781 , app.attribute_category attribute_category
782 , app.attribute1 attribute1
783 , app.attribute2 attribute2
784 , app.attribute3 attribute3
785 , app.attribute4 attribute4
786 , app.attribute5 attribute5
787 , app.attribute6 attribute6
788 , app.attribute7 attribute7
789 , app.attribute8 attribute8
790 , app.attribute9 attribute9
791 , app.attribute10 attribute10
792 , app.attribute11 attribute11
793 , app.attribute12 attribute12
794 , app.attribute13 attribute13
795 , app.attribute14 attribute14
796 , app.attribute15 attribute15
797 , ci.cons_billing_number trx_billing_number
798 , app.global_attribute_category global_attribute_CATEGORY
799 , app.global_attribute1 global_attribute1
800 , app.global_attribute2 global_attribute2
801 , app.global_attribute3 global_attribute3
802 , app.global_attribute4 global_attribute4
803 , app.global_attribute5 global_attribute5
804 , app.global_attribute6 global_attribute6
805 , app.global_attribute7 global_attribute7
806 , app.global_attribute8 global_attribute8
807 , app.global_attribute9 global_attribute9
808 , app.global_attribute10 global_attribute10
809 , app.global_attribute11 global_attribute11
810 , app.global_attribute12 global_attribute12
811 , app.global_attribute13 global_attribute13
812 , app.global_attribute14 global_attribute14
813 , app.global_attribute15 global_attribute15
814 , app.global_attribute16 global_attribute16
815 , app.global_attribute17 global_attribute17
816 , app.global_attribute18 global_attribute18
817 , app.global_attribute19 global_attribute19
818 , app.global_attribute20 global_attribute20
819 , ctt.attribute10 transaction_category -- ARTA
820 , ps_cm.gl_date trx_gl_date
821 , app.comments comments -- bug 2662270
822 , app.receivables_trx_id receivables_trx_id -- cm refunds
823 , rt.name rec_activity_name
824 , app.application_ref_id application_ref_id
825 , app.application_ref_num application_ref_num
826 , app.application_ref_type application_ref_type
827 , arpt_sql_func_util.get_lookup_meaning(''APPLICATION_REF_TYPE'', app.application_ref_type) application_ref_type_meaning
828 FROM
829 ar_payment_schedules ps_cm
830 , ar_receivable_applications app
831 , ar_payment_schedules ps_inv
832 , hz_cust_accounts cust
833 , hz_parties party
834 , ra_customer_trx ct
835 , ra_customer_trx_lines ctl
836 , ra_batch_sources bs
837 , ar_receivables_trx rt
838 , ra_cust_trx_types ctt
839 , hz_cust_site_uses su
840 , ar_cons_inv ci
841 -- , ar_lookups l_class
842 , ra_terms t
843 WHERE
844 app.applied_payment_schedule_id = ps_inv.payment_schedule_id
845 AND app.display = ''Y''
846 -- This means we only get CM applications. We use index :)
847 AND app.customer_trx_id > -1
848 AND app.customer_trx_id = ps_cm.customer_trx_id
849 AND t.term_id(+) = ps_inv.term_id
850 AND ct.customer_trx_id(+) = ps_inv.customer_trx_id
851 AND bs.batch_source_id (+) = ct.batch_source_id
852 AND ctt.cust_trx_type_id(+) = ps_inv.cust_trx_type_id
853 AND cust.cust_account_id(+) = ps_inv.customer_id
854 AND cust.party_id = party.party_id(+)
855 AND su.site_use_id(+) = ct.bill_to_site_use_id
856 AND ctl.customer_trx_line_id(+) = app.applied_customer_trx_line_id
857 -- AND ps_inv.class||'''' = l_class.lookup_code
858 -- AND l_class.lookup_type = ''INV/CM''
859 AND ci.cons_inv_id(+) = ps_inv.cons_inv_id
860 AND rt.receivables_trx_id(+) = app.receivables_trx_id
861 ' || l_cm_cust_trx_where ;
862
863 l_order_by := p_order_by;
864
865 IF l_order_by IS NOT NULL THEN
866 l_order_by := ' ORDER BY ' || l_order_by ;
867 l_order_by := CHANGE_ORDER_BY(l_order_by);
868 END IF;
869
870 ------------------------------------------------------------
871 --
872 -- Mass Applications
873 --
874 -- We are working with the Open Transactions and On Account
875 -- cursors. Open, parse and execute both cursors which
876 -- will be used during fetching.
877 --
878 ------------------------------------------------------------
879 IF p_select_type = 'MASSAPPLY' THEN
880
881 --
882 -- Before opening the cursor query, we need to incoroprate
883 -- the query conditions (that were included in the Mass Apply
884 -- window) in the cursor select.
885 --
886 -- The query conditions were passed to the procedure in the
887 -- following variables.
888 --
889
890 l_trx_number_find := p_trx_number_find;
891 l_trx_bill_number_find := p_trx_bill_number_find;
892 l_trx_type_name_find := p_trx_type_name_find;
893 l_due_date_find := p_due_date_find;
894 l_trx_date_find := p_trx_date_find;
895 l_amt_due_rem_find := p_amt_due_rem_find;
896 l_purchase_order_find := p_purchase_order_find;
897 l_transaction_category_find := p_transaction_category_find;
898
899 IF PG_DEBUG in ('Y', 'C') THEN
900 arp_standard.debug( 'p_trx_number_find =>'||p_trx_number_find );
901 arp_standard.debug( 'p_trx_type_name_find =>'||p_trx_type_name_find );
902 arp_standard.debug( 'p_due_date_find =>'||p_due_date_find );
903 arp_standard.debug( 'p_trx_date_find =>'||p_trx_date_find );
904 arp_standard.debug( 'p_amt_due_rem_find =>'||p_amt_due_rem_find);
905 END IF;
906
907
908 IF l_trx_number_find IS NOT NULL THEN
909 IF INSTR(l_trx_number_find,'#') = 0 THEN
910 -- 11/09/2000 mramanat Bugfix 1477745. Find Criteria does not work properly
911 -- in the Mass Apply Window for Trx Number.
912 l_trx_number_find := ' # LIKE '||''''||l_trx_number_find||'''';
913 END IF;
914
915 l_trx_number_find := ' AND ' || l_trx_number_find;
916 l_trx_number_find := REPLACE(l_trx_number_find,'#','ps_inv.trx_number');
917 END IF;
918
919
920 -- gtan temp
921 IF PG_DEBUG in ('Y', 'C') THEN
922 arp_standard.debug( 'about to enter l_trx_bill_number_find ');
923 END IF;
924
925 IF l_trx_bill_number_find IS NOT NULL THEN
926 IF INSTR(l_trx_bill_number_find,'#') = 0 THEN
927 l_trx_bill_number_find := ' # BETWEEN '||''''||l_trx_bill_number_find||''''||' AND '||'''' ||l_trx_bill_number_find||'''';
928 END IF;
929
930 l_trx_bill_number_find := ' AND ' || l_trx_bill_number_find;
931 l_trx_bill_number_find := REPLACE(l_trx_bill_number_find,'#','ci.cons_billing_number');
932 END IF;
933 IF PG_DEBUG in ('Y', 'C') THEN
934 arp_standard.debug( l_trx_bill_number_find);
935 END IF;
936
937 -- gtan temp
938 IF PG_DEBUG in ('Y', 'C') THEN
939 arp_standard.debug( 'about to leave l_trx_bill_number_find ');
940 END IF;
941
942 IF l_trx_type_name_find IS NOT NULL THEN
943 IF INSTR(l_trx_type_name_find,'#') = 0 THEN
944 l_trx_type_name_find := ' # BETWEEN '||''''||l_trx_type_name_find||''''||' AND '||''''||l_trx_type_name_find||'''';
945 END IF;
946
947 l_trx_type_name_find := ' AND ' ||l_trx_type_name_find;
948
949 IF PG_DEBUG in ('Y', 'C') THEN
950 arp_standard.debug( l_trx_type_name_find );
951 END IF;
952 l_trx_type_name_find := REPLACE(l_trx_type_name_find,'#','ctt.name');
953 IF PG_DEBUG in ('Y', 'C') THEN
954 arp_standard.debug( l_trx_type_name_find );
955 END IF;
956 END IF;
957
958 IF l_due_date_find IS NOT NULL THEN
959 l_due_date_find := ' AND ' ||l_due_date_find;
960 l_due_date_find := REPLACE(l_due_date_find,'#','ps_inv.due_date');
961 END IF;
962
963 IF l_trx_date_find IS NOT NULL THEN
964 l_trx_date_find := ' AND ' ||l_trx_date_find;
965 l_trx_date_find := REPLACE(l_trx_date_find,'#','ct.trx_date');
966 END IF;
967
968 IF l_amt_due_rem_find IS NOT NULL THEN
969 IF INSTR(l_amt_due_rem_find,'#') = 0 THEN
970 l_amt_due_rem_find := ' # BETWEEN '||''''||l_amt_due_rem_find||''''||' AND '||''''||l_amt_due_rem_find||'''';
971 END IF;
972
973 l_amt_due_rem_find := ' AND ' ||l_amt_due_rem_find;
974 IF PG_DEBUG in ('Y', 'C') THEN
975 arp_standard.debug( l_amt_due_rem_find);
976 END IF;
977 l_amt_due_rem_find := REPLACE(l_amt_due_rem_find,'#','ps_inv.amount_due_remaining');
978 IF PG_DEBUG in ('Y', 'C') THEN
979 arp_standard.debug( l_amt_due_rem_find);
980 END IF;
981 END IF;
982
983 -- ARTA Changes
984 -- Bugfix 1572879. Find Criteria does not work properly in the Mass Apply Window for PO Number.
985 IF l_purchase_order_find IS NOT NULL THEN
986 IF INSTR(l_purchase_order_find,'#') = 0 THEN
987 l_purchase_order_find:= ' # LIKE '||''''||l_purchase_order_find||'''';
988 END IF;
989 l_purchase_order_find := 'AND '||l_purchase_order_find;
990 l_purchase_order_find := REPLACE(l_purchase_order_find,'#','ct.purchase_order');
991 END IF;
992
993 IF l_transaction_category_find IS NOT NULL THEN
994 l_transaction_category_find := 'AND '||l_transaction_category_find;
995 l_transaction_category_find := REPLACE(l_transaction_category_find,'#',
996 'ctt.attribute10');
997 END IF;
998
999 -- gtan temp
1000 IF PG_DEBUG in ('Y', 'C') THEN
1001 arp_standard.debug( 'about to leave l_amt_due_rem_find ');
1002 END IF;
1003 -- ARTA Changes
1004 open_trx_lng := open_trx_lng || l_trx_type_name_find
1005 || l_due_date_find
1006 || l_trx_date_find
1007 || l_amt_due_rem_find
1008 || l_trx_number_find
1009 || l_trx_bill_number_find
1010 || l_purchase_order_find
1011 || l_transaction_category_find
1012 || l_order_by;
1013
1014 -- gtan temp
1015 IF PG_DEBUG in ('Y', 'C') THEN
1016 arp_standard.debug( 'after open_trx_lng ');
1017 END IF;
1018
1019 --
1020 -- Open, Parse and Execute the Open Transactions cursor.
1021 --
1022
1023 c_open_trx := dbms_sql.open_cursor;
1024
1025 -- gtan temp
1026 IF PG_DEBUG in ('Y', 'C') THEN
1027 arp_standard.debug( 'after c_open_trx ');
1028 END IF;
1029
1030 dbms_sql.parse(c_open_trx , open_trx_lng , dbms_sql.v7 );
1031 -- gtan temp
1032 IF PG_DEBUG in ('Y', 'C') THEN
1033 arp_standard.debug( 'after parse c_open_trx ');
1034 END IF;
1035
1036 dbms_sql.bind_variable(c_open_trx ,':p_apply_date' , p_apply_date );
1037 dbms_sql.bind_variable(c_open_trx ,':p_receipt_gl_date' , p_receipt_gl_date );
1038 dbms_sql.bind_variable(c_open_trx ,':l_app_gl_date_default' , l_app_gl_date_default );
1039 dbms_sql.bind_variable(c_open_trx ,':p_customer_id' , p_customer_id );
1040 dbms_sql.bind_variable(c_open_trx ,':p_bill_to_site_use_id' , p_bill_to_site_use_id );
1041 dbms_sql.bind_variable(c_open_trx ,':p_receipt_currency' , p_receipt_currency );
1042 dbms_sql.bind_variable(c_open_trx ,':p_inv_class', p_inv_class);
1043 dbms_sql.bind_variable(c_open_trx ,':p_chb_class', p_chb_class);
1044 dbms_sql.bind_variable(c_open_trx ,':p_dep_class', p_dep_class);
1045 dbms_sql.bind_variable(c_open_trx ,':p_cm_class', p_cm_class);
1046 dbms_sql.bind_variable(c_open_trx ,':p_dm_class', p_dm_class);
1047 /* 01-JUN-2000 J Rautiainen BR Implementation */
1048 dbms_sql.bind_variable(c_open_trx ,':p_br_class', p_br_class);
1049 dbms_sql.bind_variable(c_open_trx ,':p_include_disputed', p_include_disputed);
1050 dbms_sql.bind_variable(c_open_trx ,':p_include_cross_curr', p_include_cross_curr);
1051 dbms_sql.bind_variable(c_open_trx ,':p_status', p_status);
1052 -- bug1930411
1053 dbms_sql.bind_variable(c_open_trx ,':p_related_cust_flag', p_related_cust_flag);
1054
1055 dbms_sql.define_column_rowid(c_open_trx,1,open_trx_row.row_id);
1056 dbms_sql.define_column(c_open_trx,2,open_trx_row.cash_receipt_id);
1057 dbms_sql.define_column(c_open_trx,3,open_trx_row.customer_trx_id);
1058 dbms_sql.define_column(c_open_trx,4,open_trx_row.cm_customer_trx_id);
1059 dbms_sql.define_column(c_open_trx,5,open_trx_row.last_update_date);
1060 dbms_sql.define_column(c_open_trx,6,open_trx_row.last_updated_by);
1061 dbms_sql.define_column(c_open_trx,7,open_trx_row.creation_date);
1062 dbms_sql.define_column(c_open_trx,8,open_trx_row.created_by);
1063 dbms_sql.define_column(c_open_trx,9,open_trx_row.last_update_login);
1064 dbms_sql.define_column(c_open_trx,10,open_trx_row.program_application_id);
1065 dbms_sql.define_column(c_open_trx,11,open_trx_row.program_id);
1066 dbms_sql.define_column(c_open_trx,12,open_trx_row.program_update_date);
1067 dbms_sql.define_column(c_open_trx,13,open_trx_row.request_id);
1068 dbms_sql.define_column(c_open_trx,14,open_trx_row.receipt_number,100);
1069 dbms_sql.define_column(c_open_trx,15,open_trx_row.applied_flag,100);
1070 dbms_sql.define_column(c_open_trx,16,open_trx_row.customer_id);
1071 dbms_sql.define_column(c_open_trx,17,open_trx_row.customer_name,100);
1072 dbms_sql.define_column(c_open_trx,18,open_trx_row.customer_number,100);
1073 dbms_sql.define_column(c_open_trx,19,open_trx_row.trx_number,100);
1074 dbms_sql.define_column(c_open_trx,20,open_trx_row.installment);
1075 dbms_sql.define_column(c_open_trx,21,open_trx_row.amount_applied);
1076 dbms_sql.define_column(c_open_trx,22,open_trx_row.amount_applied_from);
1077 dbms_sql.define_column(c_open_trx,23,open_trx_row.trans_to_receipt_rate);
1078 dbms_sql.define_column(c_open_trx,24,open_trx_row.discount);
1079 dbms_sql.define_column(c_open_trx,25,open_trx_row.discounts_earned);
1080 dbms_sql.define_column(c_open_trx,26,open_trx_row.discounts_unearned);
1081 dbms_sql.define_column(c_open_trx,27,open_trx_row.discount_taken_earned);
1082 dbms_sql.define_column(c_open_trx,28,open_trx_row.discount_taken_unearned);
1083 dbms_sql.define_column(c_open_trx,29,open_trx_row.amount_due_remaining);
1084 dbms_sql.define_column(c_open_trx,30,open_trx_row.due_date);
1085 dbms_sql.define_column(c_open_trx,31,open_trx_row.status,100);
1086 dbms_sql.define_column(c_open_trx,32,open_trx_row.term_id);
1087 dbms_sql.define_column(c_open_trx,33,open_trx_row.trx_class_name,100);
1088 dbms_sql.define_column(c_open_trx,34,open_trx_row.trx_class_code,100);
1089 dbms_sql.define_column(c_open_trx,35,open_trx_row.trx_type_name,100);
1090 dbms_sql.define_column(c_open_trx,36,open_trx_row.cust_trx_type_id);
1091 dbms_sql.define_column(c_open_trx,37,open_trx_row.trx_date);
1092 dbms_sql.define_column(c_open_trx,38,open_trx_row.location_name,100);
1093 dbms_sql.define_column(c_open_trx,39,open_trx_row.bill_to_site_use_id);
1094 dbms_sql.define_column(c_open_trx,40,open_trx_row.days_late);
1095 dbms_sql.define_column(c_open_trx,41,open_trx_row.line_number);
1096 dbms_sql.define_column(c_open_trx,42,open_trx_row.customer_trx_line_id);
1097 dbms_sql.define_column(c_open_trx,43,open_trx_row.apply_date);
1098 dbms_sql.define_column(c_open_trx,44,open_trx_row.gl_date);
1099 dbms_sql.define_column(c_open_trx,45,open_trx_row.gl_posted_date);
1100 dbms_sql.define_column(c_open_trx,46,open_trx_row.reversal_gl_date);
1101 dbms_sql.define_column(c_open_trx,47,open_trx_row.exchange_rate);
1102 dbms_sql.define_column(c_open_trx,48,open_trx_row.invoice_currency_code,15);
1103 dbms_sql.define_column(c_open_trx,49,open_trx_row.amount_due_original);
1104 dbms_sql.define_column(c_open_trx,50,open_trx_row.amount_in_dispute);
1105 dbms_sql.define_column(c_open_trx,51,open_trx_row.amount_line_items_original);
1106 dbms_sql.define_column(c_open_trx,52,open_trx_row.acctd_amount_due_remaining);
1107 dbms_sql.define_column(c_open_trx,53,open_trx_row.acctd_amount_applied_to);
1111 dbms_sql.define_column(c_open_trx,57,open_trx_row.calc_discount_on_lines_flag,100);
1108 dbms_sql.define_column(c_open_trx,54,open_trx_row.acctd_amount_applied_from);
1109 dbms_sql.define_column(c_open_trx,55,open_trx_row.exchange_gain_loss);
1110 dbms_sql.define_column(c_open_trx,56,open_trx_row.discount_remaining);
1112 dbms_sql.define_column(c_open_trx,58,open_trx_row.partial_discount_flag,100);
1113 dbms_sql.define_column(c_open_trx,59,open_trx_row.allow_overapplication_flag,100);
1114 dbms_sql.define_column(c_open_trx,60,open_trx_row.natural_application_only_flag,100);
1115 dbms_sql.define_column(c_open_trx,61,open_trx_row.creation_sign,100);
1116 dbms_sql.define_column(c_open_trx,62,open_trx_row.applied_payment_schedule_id);
1117 dbms_sql.define_column(c_open_trx,63,open_trx_row.ussgl_transaction_code,100);
1118 dbms_sql.define_column(c_open_trx,64,open_trx_row.ussgl_transaction_code_context,100);
1119 dbms_sql.define_column(c_open_trx,65,open_trx_row.purchase_order,50);
1120 dbms_sql.define_column(c_open_trx,66,open_trx_row.trx_doc_sequence_id);
1121 dbms_sql.define_column(c_open_trx,67,open_trx_row.trx_doc_sequence_value,100);
1122 dbms_sql.define_column(c_open_trx,68,open_trx_row.trx_batch_source_name,100);
1123 dbms_sql.define_column(c_open_trx,69,open_trx_row.amount_adjusted);
1124 dbms_sql.define_column(c_open_trx,70,open_trx_row.amount_adjusted_pending);
1125 dbms_sql.define_column(c_open_trx,71,open_trx_row.amount_line_items_remaining);
1126 dbms_sql.define_column(c_open_trx,72,open_trx_row.freight_original);
1127 dbms_sql.define_column(c_open_trx,73,open_trx_row.freight_remaining);
1128 dbms_sql.define_column(c_open_trx,74,open_trx_row.receivables_charges_remaining);
1129 dbms_sql.define_column(c_open_trx,75,open_trx_row.tax_original);
1130 dbms_sql.define_column(c_open_trx,76,open_trx_row.tax_remaining);
1131 dbms_sql.define_column(c_open_trx,77,open_trx_row.selected_for_receipt_batch_id);
1132 dbms_sql.define_column(c_open_trx,78,open_trx_row.receivable_application_id);
1133 dbms_sql.define_column(c_open_trx,79,open_trx_row.attribute_category,50);
1134 dbms_sql.define_column(c_open_trx,80,open_trx_row.attribute1,150);
1135 dbms_sql.define_column(c_open_trx,81,open_trx_row.attribute2,150);
1136 dbms_sql.define_column(c_open_trx,82,open_trx_row.attribute3,150);
1137 dbms_sql.define_column(c_open_trx,83,open_trx_row.attribute4,150);
1138 dbms_sql.define_column(c_open_trx,84,open_trx_row.attribute5,150);
1139 dbms_sql.define_column(c_open_trx,85,open_trx_row.attribute6,150);
1140 dbms_sql.define_column(c_open_trx,86,open_trx_row.attribute7,150);
1141 dbms_sql.define_column(c_open_trx,87,open_trx_row.attribute8,150);
1142 dbms_sql.define_column(c_open_trx,88,open_trx_row.attribute9,150);
1143 dbms_sql.define_column(c_open_trx,89,open_trx_row.attribute10,150);
1144 dbms_sql.define_column(c_open_trx,90,open_trx_row.attribute11,150);
1145 dbms_sql.define_column(c_open_trx,91,open_trx_row.attribute12,150);
1146 dbms_sql.define_column(c_open_trx,92,open_trx_row.attribute13,150);
1147 dbms_sql.define_column(c_open_trx,93,open_trx_row.attribute14,150);
1148 dbms_sql.define_column(c_open_trx,94,open_trx_row.attribute15,150);
1149 dbms_sql.define_column(c_open_trx,95,open_trx_row.trx_billing_number,30);
1150 dbms_sql.define_column(c_open_trx,96,open_trx_row.global_attribute_category,50);
1151 dbms_sql.define_column(c_open_trx,97,open_trx_row.global_attribute1,150);
1152 dbms_sql.define_column(c_open_trx,98,open_trx_row.global_attribute2,150);
1153 dbms_sql.define_column(c_open_trx,99,open_trx_row.global_attribute3,150);
1154 dbms_sql.define_column(c_open_trx,100,open_trx_row.global_attribute4,150);
1155 dbms_sql.define_column(c_open_trx,101,open_trx_row.global_attribute5,150);
1156 dbms_sql.define_column(c_open_trx,102,open_trx_row.global_attribute6,150);
1157 dbms_sql.define_column(c_open_trx,103,open_trx_row.global_attribute7,150);
1158 dbms_sql.define_column(c_open_trx,104,open_trx_row.global_attribute8,150);
1159 dbms_sql.define_column(c_open_trx,105,open_trx_row.global_attribute9,150);
1160 dbms_sql.define_column(c_open_trx,106,open_trx_row.global_attribute10,150);
1161 dbms_sql.define_column(c_open_trx,107,open_trx_row.global_attribute11,150);
1162 dbms_sql.define_column(c_open_trx,108,open_trx_row.global_attribute12,150);
1163 dbms_sql.define_column(c_open_trx,109,open_trx_row.global_attribute13,150);
1164 dbms_sql.define_column(c_open_trx,110,open_trx_row.global_attribute14,150);
1165 dbms_sql.define_column(c_open_trx,111,open_trx_row.global_attribute15,150);
1166 dbms_sql.define_column(c_open_trx,112,open_trx_row.global_attribute16,150);
1167 dbms_sql.define_column(c_open_trx,113,open_trx_row.global_attribute17,150);
1168 dbms_sql.define_column(c_open_trx,114,open_trx_row.global_attribute18,150);
1169 dbms_sql.define_column(c_open_trx,115,open_trx_row.global_attribute19,150);
1170 dbms_sql.define_column(c_open_trx,116,open_trx_row.global_attribute20,150);
1171 dbms_sql.define_column(c_open_trx,117,open_trx_row.transaction_category,150); -- AR TA change
1172 dbms_sql.define_column(c_open_trx,118,open_trx_row.trx_gl_date);
1173
1174 ignore := dbms_sql.execute(c_open_trx);
1175
1176 IF PG_DEBUG in ('Y', 'C') THEN
1177 arp_standard.debug( 'Open, Parsed and Executed c_open_trx' );
1178 END IF;
1179
1180 --
1181 -- Open, Parse and Execute the On Account cursor.
1182 --
1183 on_acct_lng := on_acct_lng;
1184
1185 c_on_acct := dbms_sql.open_cursor;
1186
1187 dbms_sql.parse(c_on_acct , on_acct_lng , dbms_sql.v7 );
1188
1189 dbms_sql.bind_variable(c_on_acct ,':p_receipt_gl_date' , p_receipt_gl_date );
1193 dbms_sql.bind_variable(c_on_acct ,':l_app_gl_date_default' , l_app_gl_date_default );
1190 dbms_sql.bind_variable(c_on_acct ,':p_receipt_currency' , p_receipt_currency );
1191
1192
1194
1195 dbms_sql.define_column_rowid(c_on_acct,1,on_acct_row.row_id);
1196 dbms_sql.define_column(c_on_acct,2,on_acct_row.cash_receipt_id);
1197 dbms_sql.define_column(c_on_acct,3,on_acct_row.customer_trx_id);
1198 dbms_sql.define_column(c_on_acct,4,on_acct_row.last_update_date);
1199 dbms_sql.define_column(c_on_acct,5,on_acct_row.last_updated_by);
1200 dbms_sql.define_column(c_on_acct,6,on_acct_row.creation_date);
1201 dbms_sql.define_column(c_on_acct,7,on_acct_row.created_by);
1202 dbms_sql.define_column(c_on_acct,8,on_acct_row.last_update_login);
1203 dbms_sql.define_column(c_on_acct,9,on_acct_row.program_application_id);
1204 dbms_sql.define_column(c_on_acct,10,on_acct_row.program_id);
1205 dbms_sql.define_column(c_on_acct,11,on_acct_row.program_update_date);
1206 dbms_sql.define_column(c_on_acct,12,on_acct_row.request_id);
1207 dbms_sql.define_column(c_on_acct,13,on_acct_row.applied_flag,100);
1208 dbms_sql.define_column(c_on_acct,14,on_acct_row.customer_id);
1209 dbms_sql.define_column(c_on_acct,15,on_acct_row.trx_number,100);
1210 dbms_sql.define_column(c_on_acct,16,on_acct_row.discounts_earned);
1211 dbms_sql.define_column(c_on_acct,17,on_acct_row.discounts_unearned);
1212 dbms_sql.define_column(c_on_acct,18,on_acct_row.discount_taken_earned);
1213 dbms_sql.define_column(c_on_acct,19,on_acct_row.discount_taken_unearned);
1214 dbms_sql.define_column(c_on_acct,20,on_acct_row.amount_due_remaining);
1215 dbms_sql.define_column(c_on_acct,21,on_acct_row.status,100);
1216 dbms_sql.define_column(c_on_acct,22,on_acct_row.term_id);
1217 dbms_sql.define_column(c_on_acct,23,on_acct_row.bill_to_site_use_id);
1218 dbms_sql.define_column(c_on_acct,24,on_acct_row.apply_date);
1219 dbms_sql.define_column(c_on_acct,25,on_acct_row.gl_date);
1220 dbms_sql.define_column(c_on_acct,26,on_acct_row.invoice_currency_code,15);
1221 dbms_sql.define_column(c_on_acct,27,on_acct_row.amount_due_original);
1222 dbms_sql.define_column(c_on_acct,28,on_acct_row.amount_in_dispute);
1223 dbms_sql.define_column(c_on_acct,29,on_acct_row.amount_line_items_original);
1224 dbms_sql.define_column(c_on_acct,30,on_acct_row.discount_remaining);
1225 dbms_sql.define_column(c_on_acct,31,on_acct_row.applied_payment_schedule_id);
1226
1227 ignore := dbms_sql.execute(c_on_acct);
1228
1229 IF PG_DEBUG in ('Y', 'C') THEN
1230 arp_standard.debug( 'Open, Parsed and Executed c_on_acct' );
1231 END IF;
1232
1233 -----------------------------------------------------
1234 --
1235 -- Credit Memo Applications
1236 --
1237 -- We are working with the Credit Memo Applications
1238 -- cursor. Open, parse and execute the cursor which
1239 -- will be used during fetching.
1240 --
1241 -----------------------------------------------------
1242 ELSIF p_select_type = 'CM' THEN
1243
1244 cm_apps_lng := cm_apps_lng || l_order_by;
1245
1246 --
1247 -- Open, Parse and Execute the Credit Memo Applications cursor.
1248 --
1249 c_cm_apps := dbms_sql.open_cursor;
1250
1251 dbms_sql.parse(c_cm_apps , cm_apps_lng , dbms_sql.v7 );
1252 IF p_cm_customer_trx_id IS NOT NULL THEN
1253 dbms_sql.bind_variable(c_cm_apps , ':p_cm_customer_trx_id' , p_cm_customer_trx_id);
1254 END IF;
1255 dbms_sql.define_column_rowid(c_cm_apps,1,cm_apps_row.row_id);
1256 dbms_sql.define_column(c_cm_apps,2,cm_apps_row.cash_receipt_id);
1257 dbms_sql.define_column(c_cm_apps,3,cm_apps_row.customer_trx_id);
1258 dbms_sql.define_column(c_cm_apps,4,cm_apps_row.cm_customer_trx_id);
1259 dbms_sql.define_column(c_cm_apps,5,cm_apps_row.last_update_date);
1260 dbms_sql.define_column(c_cm_apps,6,cm_apps_row.last_updated_by);
1261 dbms_sql.define_column(c_cm_apps,7,cm_apps_row.creation_date);
1262 dbms_sql.define_column(c_cm_apps,8,cm_apps_row.created_by);
1263 dbms_sql.define_column(c_cm_apps,9,cm_apps_row.last_update_login);
1264 dbms_sql.define_column(c_cm_apps,10,cm_apps_row.program_application_id);
1265 dbms_sql.define_column(c_cm_apps,11,cm_apps_row.program_id);
1266 dbms_sql.define_column(c_cm_apps,12,cm_apps_row.program_update_date);
1267 dbms_sql.define_column(c_cm_apps,13,cm_apps_row.request_id);
1268 dbms_sql.define_column(c_cm_apps,14,cm_apps_row.receipt_number,100);
1269 dbms_sql.define_column(c_cm_apps,15,cm_apps_row.applied_flag,100);
1270 dbms_sql.define_column(c_cm_apps,16,cm_apps_row.customer_id);
1271 dbms_sql.define_column(c_cm_apps,17,cm_apps_row.customer_name,100);
1272 dbms_sql.define_column(c_cm_apps,18,cm_apps_row.customer_number,100);
1273 dbms_sql.define_column(c_cm_apps,19,cm_apps_row.trx_number,100);
1274 dbms_sql.define_column(c_cm_apps,20,cm_apps_row.installment);
1275 dbms_sql.define_column(c_cm_apps,21,cm_apps_row.amount_applied);
1276 dbms_sql.define_column(c_cm_apps,22,cm_apps_row.amount_applied_from);
1277 dbms_sql.define_column(c_cm_apps,23,cm_apps_row.trans_to_receipt_rate);
1278 dbms_sql.define_column(c_cm_apps,24,cm_apps_row.discount);
1279 dbms_sql.define_column(c_cm_apps,25,cm_apps_row.discounts_earned);
1280 dbms_sql.define_column(c_cm_apps,26,cm_apps_row.discounts_unearned);
1281 dbms_sql.define_column(c_cm_apps,27,cm_apps_row.discount_taken_earned);
1282 dbms_sql.define_column(c_cm_apps,28,cm_apps_row.discount_taken_unearned);
1283 dbms_sql.define_column(c_cm_apps,29,cm_apps_row.amount_due_remaining);
1284 dbms_sql.define_column(c_cm_apps,30,cm_apps_row.due_date);
1285 dbms_sql.define_column(c_cm_apps,31,cm_apps_row.status,100);
1286 dbms_sql.define_column(c_cm_apps,32,cm_apps_row.term_id);
1287 dbms_sql.define_column(c_cm_apps,33,cm_apps_row.trx_class_name,100);
1288 dbms_sql.define_column(c_cm_apps,34,cm_apps_row.trx_class_code,100);
1289 dbms_sql.define_column(c_cm_apps,35,cm_apps_row.trx_type_name,100);
1293 dbms_sql.define_column(c_cm_apps,39,cm_apps_row.bill_to_site_use_id);
1290 dbms_sql.define_column(c_cm_apps,36,cm_apps_row.cust_trx_type_id);
1291 dbms_sql.define_column(c_cm_apps,37,cm_apps_row.trx_date);
1292 dbms_sql.define_column(c_cm_apps,38,cm_apps_row.location_name,100);
1294 dbms_sql.define_column(c_cm_apps,40,cm_apps_row.days_late);
1295 dbms_sql.define_column(c_cm_apps,41,cm_apps_row.line_number);
1296 dbms_sql.define_column(c_cm_apps,42,cm_apps_row.customer_trx_line_id);
1297 dbms_sql.define_column(c_cm_apps,43,cm_apps_row.apply_date);
1298 dbms_sql.define_column(c_cm_apps,44,cm_apps_row.gl_date);
1299 dbms_sql.define_column(c_cm_apps,45,cm_apps_row.gl_posted_date);
1300 dbms_sql.define_column(c_cm_apps,46,cm_apps_row.reversal_gl_date);
1301 dbms_sql.define_column(c_cm_apps,47,cm_apps_row.exchange_rate);
1302 dbms_sql.define_column(c_cm_apps,48,cm_apps_row.invoice_currency_code,15);
1303 dbms_sql.define_column(c_cm_apps,49,cm_apps_row.amount_due_original);
1304 dbms_sql.define_column(c_cm_apps,50,cm_apps_row.amount_in_dispute);
1305 dbms_sql.define_column(c_cm_apps,51,cm_apps_row.amount_line_items_original);
1306 dbms_sql.define_column(c_cm_apps,52,cm_apps_row.acctd_amount_due_remaining);
1307 dbms_sql.define_column(c_cm_apps,53,cm_apps_row.acctd_amount_applied_to);
1308 dbms_sql.define_column(c_cm_apps,54,cm_apps_row.acctd_amount_applied_from);
1309 dbms_sql.define_column(c_cm_apps,55,cm_apps_row.exchange_gain_loss);
1310 dbms_sql.define_column(c_cm_apps,56,cm_apps_row.discount_remaining);
1311 dbms_sql.define_column(c_cm_apps,57,cm_apps_row.calc_discount_on_lines_flag,100);
1312 dbms_sql.define_column(c_cm_apps,58,cm_apps_row.partial_discount_flag,100);
1313 dbms_sql.define_column(c_cm_apps,59,cm_apps_row.allow_overapplication_flag,100);
1314 dbms_sql.define_column(c_cm_apps,60,cm_apps_row.natural_application_only_flag,100);
1315 dbms_sql.define_column(c_cm_apps,61,cm_apps_row.creation_sign,100);
1316 dbms_sql.define_column(c_cm_apps,62,cm_apps_row.applied_payment_schedule_id);
1317 dbms_sql.define_column(c_cm_apps,63,cm_apps_row.ussgl_transaction_code,100);
1318 dbms_sql.define_column(c_cm_apps,64,cm_apps_row.ussgl_transaction_code_context,100);
1319 dbms_sql.define_column(c_cm_apps,65,cm_apps_row.purchase_order,50);
1320 dbms_sql.define_column(c_cm_apps,66,cm_apps_row.trx_doc_sequence_id);
1321 dbms_sql.define_column(c_cm_apps,67,cm_apps_row.trx_doc_sequence_value,100);
1322 dbms_sql.define_column(c_cm_apps,68,cm_apps_row.trx_batch_source_name,100);
1323 dbms_sql.define_column(c_cm_apps,69,cm_apps_row.amount_adjusted);
1324 dbms_sql.define_column(c_cm_apps,70,cm_apps_row.amount_adjusted_pending);
1325 dbms_sql.define_column(c_cm_apps,71,cm_apps_row.amount_line_items_remaining);
1326 dbms_sql.define_column(c_cm_apps,72,cm_apps_row.freight_original);
1327 dbms_sql.define_column(c_cm_apps,73,cm_apps_row.freight_remaining);
1328 dbms_sql.define_column(c_cm_apps,74,cm_apps_row.receivables_charges_remaining);
1329 dbms_sql.define_column(c_cm_apps,75,cm_apps_row.tax_original);
1330 dbms_sql.define_column(c_cm_apps,76,cm_apps_row.tax_remaining);
1331 dbms_sql.define_column(c_cm_apps,77,cm_apps_row.selected_for_receipt_batch_id);
1332 dbms_sql.define_column(c_cm_apps,78,cm_apps_row.receivable_application_id);
1333 dbms_sql.define_column(c_cm_apps,79,cm_apps_row.attribute_category,50);
1334 dbms_sql.define_column(c_cm_apps,80,cm_apps_row.attribute1,150);
1335 dbms_sql.define_column(c_cm_apps,81,cm_apps_row.attribute2,150);
1336 dbms_sql.define_column(c_cm_apps,82,cm_apps_row.attribute3,150);
1337 dbms_sql.define_column(c_cm_apps,83,cm_apps_row.attribute4,150);
1338 dbms_sql.define_column(c_cm_apps,84,cm_apps_row.attribute5,150);
1339 dbms_sql.define_column(c_cm_apps,85,cm_apps_row.attribute6,150);
1340 dbms_sql.define_column(c_cm_apps,86,cm_apps_row.attribute7,150);
1341 dbms_sql.define_column(c_cm_apps,87,cm_apps_row.attribute8,150);
1342 dbms_sql.define_column(c_cm_apps,88,cm_apps_row.attribute9,150);
1343 dbms_sql.define_column(c_cm_apps,89,cm_apps_row.attribute10,150);
1344 dbms_sql.define_column(c_cm_apps,90,cm_apps_row.attribute11,150);
1345 dbms_sql.define_column(c_cm_apps,91,cm_apps_row.attribute12,150);
1346 dbms_sql.define_column(c_cm_apps,92,cm_apps_row.attribute13,150);
1347 dbms_sql.define_column(c_cm_apps,93,cm_apps_row.attribute14,150);
1348 dbms_sql.define_column(c_cm_apps,94,cm_apps_row.attribute15,150);
1349 dbms_sql.define_column(c_cm_apps,95,cm_apps_row.trx_billing_number,30);
1350 dbms_sql.define_column(c_cm_apps,96,cm_apps_row.global_attribute_category,50);
1351 dbms_sql.define_column(c_cm_apps,97,cm_apps_row.global_attribute1,150);
1352 dbms_sql.define_column(c_cm_apps,98,cm_apps_row.global_attribute2,150);
1353 dbms_sql.define_column(c_cm_apps,99,cm_apps_row.global_attribute3,150);
1354 dbms_sql.define_column(c_cm_apps,100,cm_apps_row.global_attribute4,150);
1355 dbms_sql.define_column(c_cm_apps,101,cm_apps_row.global_attribute5,150);
1356 dbms_sql.define_column(c_cm_apps,102,cm_apps_row.global_attribute6,150);
1357 dbms_sql.define_column(c_cm_apps,103,cm_apps_row.global_attribute7,150);
1358 dbms_sql.define_column(c_cm_apps,104,cm_apps_row.global_attribute8,150);
1359 dbms_sql.define_column(c_cm_apps,105,cm_apps_row.global_attribute9,150);
1360 dbms_sql.define_column(c_cm_apps,106,cm_apps_row.global_attribute10,150);
1361 dbms_sql.define_column(c_cm_apps,107,cm_apps_row.global_attribute11,150);
1362 dbms_sql.define_column(c_cm_apps,108,cm_apps_row.global_attribute12,150);
1363 dbms_sql.define_column(c_cm_apps,109,cm_apps_row.global_attribute13,150);
1364 dbms_sql.define_column(c_cm_apps,110,cm_apps_row.global_attribute14,150);
1365 dbms_sql.define_column(c_cm_apps,111,cm_apps_row.global_attribute15,150);
1369 dbms_sql.define_column(c_cm_apps,115,cm_apps_row.global_attribute19,150);
1366 dbms_sql.define_column(c_cm_apps,112,cm_apps_row.global_attribute16,150);
1367 dbms_sql.define_column(c_cm_apps,113,cm_apps_row.global_attribute17,150);
1368 dbms_sql.define_column(c_cm_apps,114,cm_apps_row.global_attribute18,150);
1370 dbms_sql.define_column(c_cm_apps,116,cm_apps_row.global_attribute20,150);
1371 dbms_sql.define_column(c_cm_apps,117,cm_apps_row.transaction_category,150); -- AR TA change
1372 dbms_sql.define_column(c_cm_apps,118,cm_apps_row.trx_gl_date);
1373 dbms_sql.define_column(c_cm_apps,119,cm_apps_row.comments,240); -- bug 2662270
1374 /* columns 120 - 125 added for CM refunds */
1375 dbms_sql.define_column(c_cm_apps,120,cm_apps_row.receivables_trx_id);
1376 dbms_sql.define_column(c_cm_apps,121,cm_apps_row.rec_activity_name,50);
1377 dbms_sql.define_column(c_cm_apps,122,cm_apps_row.application_ref_id);
1378 dbms_sql.define_column(c_cm_apps,123,cm_apps_row.application_ref_num,150);
1379 dbms_sql.define_column(c_cm_apps,124,cm_apps_row.application_ref_type,30);
1380 dbms_sql.define_column(c_cm_apps,125,cm_apps_row.application_ref_type_meaning,80);
1381 ignore := dbms_sql.execute(c_cm_apps);
1382
1383 IF PG_DEBUG in ('Y', 'C') THEN
1384 arp_standard.debug( 'Open, Parsed and Executed c_cm_apps' );
1385 END IF;
1386
1387 END IF;
1388
1389 IF PG_DEBUG in ('Y', 'C') THEN
1390 arp_standard.debug( 'on_select()-' );
1391 END IF;
1392
1393 EXCEPTION
1394 WHEN OTHERS THEN
1395 IF PG_DEBUG in ('Y', 'C') THEN
1396 arp_standard.debug( SQLERRM);
1397 arp_standard.debug('EXCEPTION: ar_add_fetch_select.on_select');
1398 END IF;
1399 RAISE;
1400
1401 END on_select;
1402
1403 /*===========================================================================+
1404 | FUNCTION
1405 | ON_FETCH
1406 |
1407 | DESCRIPTION
1408 | This function selects a single row from the cursor
1409 | and returns the record values to the form.
1410 |
1411 | SCOPE - PUBLIC
1412 |
1413 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
1414 |
1415 | ARGUMENTS : IN:
1416 |
1417 | OUT:
1418 |
1419 | RETURNS : TRUE if a record has been selected
1420 | FALSE if a record has not been selected
1421 | NOTES
1422 |
1423 | MODIFICATION HISTORY
1424 | 25-AUG-97 Joan Zaman Created.
1425 | 08-JAN-98 Karen Murphy See list of changes at the package level.
1426 |
1427 +===========================================================================*/
1428
1429 function on_fetch (
1430 p_select_type IN VARCHAR2,
1431 row_id OUT NOCOPY VARCHAR2,
1432 cash_receipt_id OUT NOCOPY NUMBER,
1433 customer_trx_id OUT NOCOPY NUMBER,
1434 cm_customer_trx_id OUT NOCOPY NUMBER,
1435 last_update_date OUT NOCOPY DATE,
1436 last_updated_by OUT NOCOPY NUMBER,
1437 creation_date OUT NOCOPY DATE,
1438 created_by OUT NOCOPY NUMBER,
1439 last_update_login OUT NOCOPY NUMBER,
1440 program_application_id OUT NOCOPY NUMBER,
1441 program_id OUT NOCOPY NUMBER,
1445 applied_flag OUT NOCOPY VARCHAR2,
1442 program_update_date OUT NOCOPY DATE,
1443 request_id OUT NOCOPY NUMBER,
1444 receipt_number OUT NOCOPY VARCHAR2,
1446 customer_id OUT NOCOPY NUMBER,
1447 customer_name OUT NOCOPY VARCHAR2,
1448 customer_number OUT NOCOPY VARCHAR2,
1449 trx_number OUT NOCOPY VARCHAR2,
1450 installment OUT NOCOPY NUMBER,
1451 amount_applied OUT NOCOPY NUMBER,
1452 amount_applied_from OUT NOCOPY NUMBER,
1453 trans_to_receipt_rate OUT NOCOPY NUMBER,
1454 discount OUT NOCOPY NUMBER,
1455 discounts_earned OUT NOCOPY NUMBER,
1456 discounts_unearned OUT NOCOPY NUMBER,
1457 discount_taken_earned OUT NOCOPY NUMBER,
1458 discount_taken_unearned OUT NOCOPY NUMBER,
1459 amount_due_remaining OUT NOCOPY NUMBER,
1460 due_date OUT NOCOPY DATE,
1461 status OUT NOCOPY VARCHAR2,
1462 term_id OUT NOCOPY NUMBER,
1463 trx_class_name OUT NOCOPY VARCHAR2,
1464 trx_class_code OUT NOCOPY VARCHAR2,
1465 trx_type_name OUT NOCOPY VARCHAR2,
1466 cust_trx_type_id OUT NOCOPY NUMBER,
1467 trx_date OUT NOCOPY DATE,
1468 location_name OUT NOCOPY VARCHAR2,
1469 bill_to_site_use_id OUT NOCOPY NUMBER,
1470 days_late OUT NOCOPY NUMBER,
1471 line_number OUT NOCOPY NUMBER,
1472 customer_trx_line_id OUT NOCOPY NUMBER,
1473 apply_date OUT NOCOPY DATE,
1474 gl_date OUT NOCOPY DATE,
1475 gl_posted_date OUT NOCOPY DATE,
1476 reversal_gl_date OUT NOCOPY DATE,
1477 exchange_rate OUT NOCOPY NUMBER,
1478 invoice_currency_code OUT NOCOPY VARCHAR2,
1479 amount_due_original OUT NOCOPY NUMBER,
1480 amount_in_dispute OUT NOCOPY NUMBER,
1481 amount_line_items_original OUT NOCOPY NUMBER,
1482 acctd_amount_due_remaining OUT NOCOPY NUMBER,
1483 acctd_amount_applied_to OUT NOCOPY NUMBER,
1484 acctd_amount_applied_from OUT NOCOPY NUMBER,
1485 exchange_gain_loss OUT NOCOPY NUMBER,
1486 discount_remaining OUT NOCOPY NUMBER,
1487 calc_discount_on_lines_flag OUT NOCOPY VARCHAR2,
1488 partial_discount_flag OUT NOCOPY VARCHAR2,
1489 allow_overapplication_flag OUT NOCOPY VARCHAR2,
1490 natural_application_only_flag OUT NOCOPY VARCHAR2,
1491 creation_sign OUT NOCOPY VARCHAR2,
1492 applied_payment_schedule_id OUT NOCOPY NUMBER,
1493 ussgl_transaction_code OUT NOCOPY VARCHAR2,
1494 ussgl_transaction_code_context OUT NOCOPY VARCHAR2,
1495 purchase_order OUT NOCOPY VARCHAR2,
1496 trx_doc_sequence_id OUT NOCOPY NUMBER,
1497 trx_doc_sequence_value OUT NOCOPY VARCHAR2,
1498 trx_batch_source_name OUT NOCOPY VARCHAR2,
1499 amount_adjusted OUT NOCOPY NUMBER,
1500 amount_adjusted_pending OUT NOCOPY NUMBER,
1501 amount_line_items_remaining OUT NOCOPY NUMBER,
1502 freight_original OUT NOCOPY NUMBER,
1503 freight_remaining OUT NOCOPY NUMBER,
1504 receivables_charges_remaining OUT NOCOPY NUMBER,
1505 tax_original OUT NOCOPY NUMBER,
1506 tax_remaining OUT NOCOPY NUMBER,
1507 selected_for_receipt_batch_id OUT NOCOPY NUMBER,
1508 receivable_application_id OUT NOCOPY NUMBER,
1509 attribute_category OUT NOCOPY VARCHAR2,
1510 attribute1 OUT NOCOPY VARCHAR2,
1511 attribute2 OUT NOCOPY VARCHAR2,
1512 attribute3 OUT NOCOPY VARCHAR2,
1513 attribute4 OUT NOCOPY VARCHAR2,
1514 attribute5 OUT NOCOPY VARCHAR2,
1515 attribute6 OUT NOCOPY VARCHAR2,
1516 attribute7 OUT NOCOPY VARCHAR2,
1517 attribute8 OUT NOCOPY VARCHAR2,
1518 attribute9 OUT NOCOPY VARCHAR2,
1519 attribute10 OUT NOCOPY VARCHAR2,
1520 attribute11 OUT NOCOPY VARCHAR2,
1521 attribute12 OUT NOCOPY VARCHAR2,
1522 attribute13 OUT NOCOPY VARCHAR2,
1523 attribute14 OUT NOCOPY VARCHAR2,
1524 attribute15 OUT NOCOPY VARCHAR2,
1525 trx_billing_number OUT NOCOPY VARCHAR2,
1526 global_attribute_category OUT NOCOPY VARCHAR2,
1527 global_attribute1 OUT NOCOPY VARCHAR2,
1528 global_attribute2 OUT NOCOPY VARCHAR2,
1529 global_attribute3 OUT NOCOPY VARCHAR2,
1530 global_attribute4 OUT NOCOPY VARCHAR2,
1531 global_attribute5 OUT NOCOPY VARCHAR2,
1532 global_attribute6 OUT NOCOPY VARCHAR2,
1533 global_attribute7 OUT NOCOPY VARCHAR2,
1534 global_attribute8 OUT NOCOPY VARCHAR2,
1535 global_attribute9 OUT NOCOPY VARCHAR2,
1536 global_attribute10 OUT NOCOPY VARCHAR2,
1537 global_attribute11 OUT NOCOPY VARCHAR2,
1538 global_attribute12 OUT NOCOPY VARCHAR2,
1539 global_attribute13 OUT NOCOPY VARCHAR2,
1540 global_attribute14 OUT NOCOPY VARCHAR2,
1541 global_attribute15 OUT NOCOPY VARCHAR2,
1542 global_attribute16 OUT NOCOPY VARCHAR2,
1543 global_attribute17 OUT NOCOPY VARCHAR2,
1544 global_attribute18 OUT NOCOPY VARCHAR2,
1545 global_attribute19 OUT NOCOPY VARCHAR2,
1546 global_attribute20 OUT NOCOPY VARCHAR2,
1550 comments OUT NOCOPY VARCHAR2, --- bug 2662270
1547 -- purchase_order OUT NOCOPY VARCHAR2,
1548 transaction_category OUT NOCOPY VARCHAR2,
1549 trx_gl_date OUT NOCOPY DATE,
1551 receivables_trx_id OUT NOCOPY NUMBER,
1552 rec_activity_name OUT NOCOPY VARCHAR,
1553 application_ref_id OUT NOCOPY NUMBER,
1554 application_ref_num OUT NOCOPY VARCHAR2,
1555 application_ref_type OUT NOCOPY VARCHAR2,
1556 application_ref_type_meaning OUT NOCOPY VARCHAR2
1557 ) return BOOLEAN IS
1558
1559 ignore number;
1560
1561 BEGIN
1562
1563 IF PG_DEBUG in ('Y', 'C') THEN
1564 arp_standard.debug( 'on_fetch()+' );
1565 END IF;
1566
1567 ------------------------------------------------------------
1568 --
1569 -- Mass Applications
1570 --
1571 -- We are working with the Open Transactions and On Account
1572 -- cursors. We want to loop through the Open Transactions
1573 -- cursor, once all of the records have been fetched,
1574 -- close the cursor and fetch the single On Account row.
1575 --
1576 ------------------------------------------------------------
1577 IF dbms_sql.is_open(c_open_trx) THEN
1578
1579 IF PG_DEBUG in ('Y', 'C') THEN
1580 arp_standard.debug( 'Open Trx Cursor is OPEN' );
1581 END IF;
1582 IF dbms_sql.fetch_rows(c_open_trx) > 0 THEN
1583
1584 IF PG_DEBUG in ('Y', 'C') THEN
1585 arp_standard.debug( 'And we have fetched a record from Open Trx' );
1586 END IF;
1587 dbms_sql.column_value_rowid(c_open_trx,1,open_trx_row.row_id);
1588 dbms_sql.column_value(c_open_trx,2,open_trx_row.cash_receipt_id);
1589 dbms_sql.column_value(c_open_trx,3,open_trx_row.customer_trx_id);
1590 dbms_sql.column_value(c_open_trx,4,open_trx_row.cm_customer_trx_id);
1591 dbms_sql.column_value(c_open_trx,5,open_trx_row.last_update_date);
1592 dbms_sql.column_value(c_open_trx,6,open_trx_row.last_updated_by);
1593 dbms_sql.column_value(c_open_trx,7,open_trx_row.creation_date);
1594 dbms_sql.column_value(c_open_trx,8,open_trx_row.created_by);
1595 dbms_sql.column_value(c_open_trx,9,open_trx_row.last_update_login);
1596 dbms_sql.column_value(c_open_trx,10,open_trx_row.program_application_id);
1597 dbms_sql.column_value(c_open_trx,11,open_trx_row.program_id);
1598 dbms_sql.column_value(c_open_trx,12,open_trx_row.program_update_date);
1599 dbms_sql.column_value(c_open_trx,13,open_trx_row.request_id);
1600 dbms_sql.column_value(c_open_trx,14,open_trx_row.receipt_number);
1601 dbms_sql.column_value(c_open_trx,15,open_trx_row.applied_flag);
1602 dbms_sql.column_value(c_open_trx,16,open_trx_row.customer_id);
1603 dbms_sql.column_value(c_open_trx,17,open_trx_row.customer_name);
1604 dbms_sql.column_value(c_open_trx,18,open_trx_row.customer_number);
1605 dbms_sql.column_value(c_open_trx,19,open_trx_row.trx_number);
1606 dbms_sql.column_value(c_open_trx,20,open_trx_row.installment);
1607 dbms_sql.column_value(c_open_trx,21,open_trx_row.amount_applied);
1608 dbms_sql.column_value(c_open_trx,22,open_trx_row.amount_applied_from);
1609 dbms_sql.column_value(c_open_trx,23,open_trx_row.trans_to_receipt_rate);
1610 dbms_sql.column_value(c_open_trx,24,open_trx_row.discount);
1611 dbms_sql.column_value(c_open_trx,25,open_trx_row.discounts_earned);
1612 dbms_sql.column_value(c_open_trx,26,open_trx_row.discounts_unearned);
1613 dbms_sql.column_value(c_open_trx,27,open_trx_row.discount_taken_earned);
1614 dbms_sql.column_value(c_open_trx,28,open_trx_row.discount_taken_unearned);
1615 dbms_sql.column_value(c_open_trx,29,open_trx_row.amount_due_remaining);
1616 dbms_sql.column_value(c_open_trx,30,open_trx_row.due_date);
1617 dbms_sql.column_value(c_open_trx,31,open_trx_row.status);
1618 dbms_sql.column_value(c_open_trx,32,open_trx_row.term_id);
1619 dbms_sql.column_value(c_open_trx,33,open_trx_row.trx_class_name);
1620 dbms_sql.column_value(c_open_trx,34,open_trx_row.trx_class_code);
1621 dbms_sql.column_value(c_open_trx,35,open_trx_row.trx_type_name);
1622 dbms_sql.column_value(c_open_trx,36,open_trx_row.cust_trx_type_id);
1623 dbms_sql.column_value(c_open_trx,37,open_trx_row.trx_date);
1624 dbms_sql.column_value(c_open_trx,38,open_trx_row.location_name);
1625 dbms_sql.column_value(c_open_trx,39,open_trx_row.bill_to_site_use_id);
1626 dbms_sql.column_value(c_open_trx,40,open_trx_row.days_late);
1632 dbms_sql.column_value(c_open_trx,46,open_trx_row.reversal_gl_date);
1627 dbms_sql.column_value(c_open_trx,41,open_trx_row.line_number);
1628 dbms_sql.column_value(c_open_trx,42,open_trx_row.customer_trx_line_id);
1629 dbms_sql.column_value(c_open_trx,43,open_trx_row.apply_date);
1630 dbms_sql.column_value(c_open_trx,44,open_trx_row.gl_date);
1631 dbms_sql.column_value(c_open_trx,45,open_trx_row.gl_posted_date);
1633 dbms_sql.column_value(c_open_trx,47,open_trx_row.exchange_rate);
1634 dbms_sql.column_value(c_open_trx,48,open_trx_row.invoice_currency_code);
1635 dbms_sql.column_value(c_open_trx,49,open_trx_row.amount_due_original);
1636 dbms_sql.column_value(c_open_trx,50,open_trx_row.amount_in_dispute);
1637 dbms_sql.column_value(c_open_trx,51,open_trx_row.amount_line_items_original);
1638 dbms_sql.column_value(c_open_trx,52,open_trx_row.acctd_amount_due_remaining);
1639 dbms_sql.column_value(c_open_trx,53,open_trx_row.acctd_amount_applied_to);
1640 dbms_sql.column_value(c_open_trx,54,open_trx_row.acctd_amount_applied_from);
1641 dbms_sql.column_value(c_open_trx,55,open_trx_row.exchange_gain_loss);
1642 dbms_sql.column_value(c_open_trx,56,open_trx_row.discount_remaining);
1643 dbms_sql.column_value(c_open_trx,57,open_trx_row.calc_discount_on_lines_flag);
1644 dbms_sql.column_value(c_open_trx,58,open_trx_row.partial_discount_flag);
1645 dbms_sql.column_value(c_open_trx,59,open_trx_row.allow_overapplication_flag);
1646 dbms_sql.column_value(c_open_trx,60,open_trx_row.natural_application_only_flag);
1647 dbms_sql.column_value(c_open_trx,61,open_trx_row.creation_sign);
1648 dbms_sql.column_value(c_open_trx,62,open_trx_row.applied_payment_schedule_id);
1649 dbms_sql.column_value(c_open_trx,63,open_trx_row.ussgl_transaction_code);
1650 dbms_sql.column_value(c_open_trx,64,open_trx_row.ussgl_transaction_code_context);
1651 dbms_sql.column_value(c_open_trx,65,open_trx_row.purchase_order);
1652 dbms_sql.column_value(c_open_trx,66,open_trx_row.trx_doc_sequence_id);
1653 dbms_sql.column_value(c_open_trx,67,open_trx_row.trx_doc_sequence_value);
1654 dbms_sql.column_value(c_open_trx,68,open_trx_row.trx_batch_source_name);
1655 dbms_sql.column_value(c_open_trx,69,open_trx_row.amount_adjusted);
1656 dbms_sql.column_value(c_open_trx,70,open_trx_row.amount_adjusted_pending);
1657 dbms_sql.column_value(c_open_trx,71,open_trx_row.amount_line_items_remaining);
1658 dbms_sql.column_value(c_open_trx,72,open_trx_row.freight_original);
1659 dbms_sql.column_value(c_open_trx,73,open_trx_row.freight_remaining);
1660 dbms_sql.column_value(c_open_trx,74,open_trx_row.receivables_charges_remaining);
1661 dbms_sql.column_value(c_open_trx,75,open_trx_row.tax_original);
1662 dbms_sql.column_value(c_open_trx,76,open_trx_row.tax_remaining);
1663 dbms_sql.column_value(c_open_trx,77,open_trx_row.selected_for_receipt_batch_id);
1664 dbms_sql.column_value(c_open_trx,78,open_trx_row.receivable_application_id);
1665 dbms_sql.column_value(c_open_trx,79,open_trx_row.attribute_category);
1666 dbms_sql.column_value(c_open_trx,80,open_trx_row.attribute1);
1667 dbms_sql.column_value(c_open_trx,81,open_trx_row.attribute2);
1668 dbms_sql.column_value(c_open_trx,82,open_trx_row.attribute3);
1669 dbms_sql.column_value(c_open_trx,83,open_trx_row.attribute4);
1670 dbms_sql.column_value(c_open_trx,84,open_trx_row.attribute5);
1671 dbms_sql.column_value(c_open_trx,85,open_trx_row.attribute6);
1672 dbms_sql.column_value(c_open_trx,86,open_trx_row.attribute7);
1673 dbms_sql.column_value(c_open_trx,87,open_trx_row.attribute8);
1674 dbms_sql.column_value(c_open_trx,88,open_trx_row.attribute9);
1675 dbms_sql.column_value(c_open_trx,89,open_trx_row.attribute10);
1676 dbms_sql.column_value(c_open_trx,90,open_trx_row.attribute11);
1677 dbms_sql.column_value(c_open_trx,91,open_trx_row.attribute12);
1678 dbms_sql.column_value(c_open_trx,92,open_trx_row.attribute13);
1679 dbms_sql.column_value(c_open_trx,93,open_trx_row.attribute14);
1680 dbms_sql.column_value(c_open_trx,94,open_trx_row.attribute15);
1681 dbms_sql.column_value(c_open_trx,95,open_trx_row.trx_billing_number);
1682 dbms_sql.column_value(c_open_trx,96,open_trx_row.global_attribute_category);
1683 dbms_sql.column_value(c_open_trx,97,open_trx_row.global_attribute1);
1684 dbms_sql.column_value(c_open_trx,98,open_trx_row.global_attribute2);
1685 dbms_sql.column_value(c_open_trx,99,open_trx_row.global_attribute3);
1686 dbms_sql.column_value(c_open_trx,100,open_trx_row.global_attribute4);
1687 dbms_sql.column_value(c_open_trx,101,open_trx_row.global_attribute5);
1688 dbms_sql.column_value(c_open_trx,102,open_trx_row.global_attribute6);
1689 dbms_sql.column_value(c_open_trx,103,open_trx_row.global_attribute7);
1690 dbms_sql.column_value(c_open_trx,104,open_trx_row.global_attribute8);
1691 dbms_sql.column_value(c_open_trx,105,open_trx_row.global_attribute9);
1692 dbms_sql.column_value(c_open_trx,106,open_trx_row.global_attribute10);
1693 dbms_sql.column_value(c_open_trx,107,open_trx_row.global_attribute11);
1694 dbms_sql.column_value(c_open_trx,108,open_trx_row.global_attribute12);
1695 dbms_sql.column_value(c_open_trx,109,open_trx_row.global_attribute13);
1696 dbms_sql.column_value(c_open_trx,110,open_trx_row.global_attribute14);
1697 dbms_sql.column_value(c_open_trx,111,open_trx_row.global_attribute15);
1698 dbms_sql.column_value(c_open_trx,112,open_trx_row.global_attribute16);
1699 dbms_sql.column_value(c_open_trx,113,open_trx_row.global_attribute17);
1700 dbms_sql.column_value(c_open_trx,114,open_trx_row.global_attribute18);
1701 dbms_sql.column_value(c_open_trx,115,open_trx_row.global_attribute19);
1702 dbms_sql.column_value(c_open_trx,116,open_trx_row.global_attribute20);
1703 dbms_sql.column_value(c_open_trx,117,open_trx_row.transaction_category); -- ARTA Changes
1704 dbms_sql.column_value(c_open_trx,118,open_trx_row.trx_gl_date);
1705
1706
1707 row_id :=open_trx_row.row_id;
1708 cash_receipt_id :=open_trx_row.cash_receipt_id;
1712 last_updated_by :=open_trx_row.last_updated_by;
1709 customer_trx_id :=open_trx_row.customer_trx_id;
1710 cm_customer_trx_id :=open_trx_row.cm_customer_trx_id;
1711 last_update_date :=open_trx_row.last_update_date;
1713 creation_date :=open_trx_row.creation_date;
1714 created_by :=open_trx_row.created_by;
1715 last_update_login :=open_trx_row.last_update_login;
1716 program_application_id :=open_trx_row.program_application_id;
1717 program_id :=open_trx_row.program_id;
1718 program_update_date :=open_trx_row.program_update_date;
1719 request_id :=open_trx_row.request_id;
1720 receipt_number :=open_trx_row.receipt_number;
1721 applied_flag :=open_trx_row.applied_flag;
1725 trx_number :=open_trx_row.trx_number;
1722 customer_id :=open_trx_row.customer_id;
1723 customer_name :=open_trx_row.customer_name;
1724 customer_number :=open_trx_row.customer_number;
1726 installment :=open_trx_row.installment;
1727 amount_applied :=open_trx_row.amount_applied;
1728 amount_applied_from :=open_trx_row.amount_applied_from;
1729 trans_to_receipt_rate :=open_trx_row.trans_to_receipt_rate;
1730 discount :=open_trx_row.discount;
1731 discounts_earned :=open_trx_row.discounts_earned;
1732 discounts_unearned :=open_trx_row.discounts_unearned;
1733 discount_taken_earned :=open_trx_row.discount_taken_earned;
1734 discount_taken_unearned :=open_trx_row.discount_taken_unearned;
1735 amount_due_remaining :=open_trx_row.amount_due_remaining;
1736 due_date :=open_trx_row.due_date;
1737 status :=open_trx_row.status;
1738 term_id :=open_trx_row.term_id;
1739 trx_class_name :=open_trx_row.trx_class_name;
1740 trx_class_code :=open_trx_row.trx_class_code;
1741 trx_type_name :=open_trx_row.trx_type_name;
1742 cust_trx_type_id :=open_trx_row.cust_trx_type_id;
1743 trx_date :=open_trx_row.trx_date;
1744 location_name :=open_trx_row.location_name;
1745 bill_to_site_use_id :=open_trx_row.bill_to_site_use_id;
1746 days_late :=open_trx_row.days_late;
1747 line_number :=open_trx_row.line_number;
1748 customer_trx_line_id :=open_trx_row.customer_trx_line_id;
1749 apply_date :=open_trx_row.apply_date;
1750 gl_date :=open_trx_row.gl_date;
1751 gl_posted_date :=open_trx_row.gl_posted_date;
1752 reversal_gl_date :=open_trx_row.reversal_gl_date;
1753 exchange_rate :=open_trx_row.exchange_rate;
1754 invoice_currency_code :=open_trx_row.invoice_currency_code;
1755 amount_due_original :=open_trx_row.amount_due_original;
1756 amount_in_dispute :=open_trx_row.amount_in_dispute;
1757 amount_line_items_original:=open_trx_row.amount_line_items_original;
1758 acctd_amount_due_remaining:=open_trx_row.acctd_amount_due_remaining;
1759 acctd_amount_applied_to :=open_trx_row.acctd_amount_applied_to;
1760 acctd_amount_applied_from :=open_trx_row.acctd_amount_applied_from;
1761 exchange_gain_loss :=open_trx_row.exchange_gain_loss;
1762 discount_remaining :=open_trx_row.discount_remaining;
1763 calc_discount_on_lines_flag:=open_trx_row.calc_discount_on_lines_flag;
1764 partial_discount_flag :=open_trx_row.partial_discount_flag;
1765 allow_overapplication_flag:=open_trx_row.allow_overapplication_flag;
1766 natural_application_only_flag:=open_trx_row.natural_application_only_flag;
1767 creation_sign :=open_trx_row.creation_sign;
1768 applied_payment_schedule_id:=open_trx_row.applied_payment_schedule_id;
1769 ussgl_transaction_code :=open_trx_row.ussgl_transaction_code;
1770 ussgl_transaction_code_context:=open_trx_row.ussgl_transaction_code_context;
1771 purchase_order :=open_trx_row.purchase_order;
1772 trx_doc_sequence_id :=open_trx_row.trx_doc_sequence_id;
1773 trx_doc_sequence_value :=open_trx_row.trx_doc_sequence_value;
1774 trx_batch_source_name :=open_trx_row.trx_batch_source_name;
1775 amount_adjusted :=open_trx_row.amount_adjusted;
1776 amount_adjusted_pending :=open_trx_row.amount_adjusted_pending;
1777 amount_line_items_remaining:=open_trx_row.amount_line_items_remaining;
1778 freight_original :=open_trx_row.freight_original;
1779 freight_remaining :=open_trx_row.freight_remaining;
1780 receivables_charges_remaining:=open_trx_row.receivables_charges_remaining;
1781 tax_original :=open_trx_row.tax_original;
1782 tax_remaining :=open_trx_row.tax_remaining;
1783 selected_for_receipt_batch_id:=open_trx_row.selected_for_receipt_batch_id;
1784 receivable_application_id :=open_trx_row.receivable_application_id;
1788 attribute3 :=open_trx_row.attribute3;
1785 attribute_category :=open_trx_row.attribute_category;
1786 attribute1 :=open_trx_row.attribute1;
1787 attribute2 :=open_trx_row.attribute2;
1789 attribute4 :=open_trx_row.attribute4;
1790 attribute5 :=open_trx_row.attribute5;
1791 attribute6 :=open_trx_row.attribute6;
1792 attribute7 :=open_trx_row.attribute7;
1793 attribute8 :=open_trx_row.attribute8;
1794 attribute9 :=open_trx_row.attribute9;
1795 attribute10 :=open_trx_row.attribute10;
1796 attribute11 :=open_trx_row.attribute11;
1797 attribute12 :=open_trx_row.attribute12;
1798 attribute13 :=open_trx_row.attribute13;
1799 attribute14 :=open_trx_row.attribute14;
1800 attribute15 :=open_trx_row.attribute15;
1801 trx_billing_number :=open_trx_row.trx_billing_number;
1802 global_attribute_category :=open_trx_row.global_attribute_category;
1803 global_attribute1 :=open_trx_row.global_attribute1;
1804 global_attribute2 :=open_trx_row.global_attribute2;
1805 global_attribute3 :=open_trx_row.global_attribute3;
1806 global_attribute4 :=open_trx_row.global_attribute4;
1807 global_attribute5 :=open_trx_row.global_attribute5;
1808 global_attribute6 :=open_trx_row.global_attribute6;
1809 global_attribute7 :=open_trx_row.global_attribute7;
1810 global_attribute8 :=open_trx_row.global_attribute8;
1811 global_attribute9 :=open_trx_row.global_attribute9;
1812 global_attribute10 :=open_trx_row.global_attribute10;
1813 global_attribute11 :=open_trx_row.global_attribute11;
1814 global_attribute12 :=open_trx_row.global_attribute12;
1815 global_attribute13 :=open_trx_row.global_attribute13;
1816 global_attribute14 :=open_trx_row.global_attribute14;
1817 global_attribute15 :=open_trx_row.global_attribute15;
1818 global_attribute16 :=open_trx_row.global_attribute16;
1819 global_attribute17 :=open_trx_row.global_attribute17;
1820 global_attribute18 :=open_trx_row.global_attribute18;
1821 global_attribute19 :=open_trx_row.global_attribute19;
1822 global_attribute20 :=open_trx_row.global_attribute20;
1823 -- ARTA Changes
1824 transaction_category :=open_trx_row.transaction_category;
1825 trx_gl_date :=open_trx_row.trx_gl_date;
1826
1827 return(TRUE);
1828
1829 ELSE
1830
1831 IF PG_DEBUG in ('Y', 'C') THEN
1832 arp_standard.debug( 'Open Trx has no more records so we will close it' );
1833 END IF;
1834 dbms_sql.close_cursor(c_open_trx);
1835
1836 IF PG_DEBUG in ('Y', 'C') THEN
1837 arp_standard.debug( 'But On Acct is open' );
1838 END IF;
1839 ignore := dbms_sql.fetch_rows(c_on_acct);
1840
1841 IF PG_DEBUG in ('Y', 'C') THEN
1842 arp_standard.debug( 'On Acct cursor has a record' );
1843 END IF;
1844 dbms_sql.column_value_rowid(c_on_acct,1,on_acct_row.row_id);
1845 dbms_sql.column_value(c_on_acct,2,on_acct_row.cash_receipt_id);
1846 dbms_sql.column_value(c_on_acct,3,on_acct_row.customer_trx_id);
1847 dbms_sql.column_value(c_on_acct,4,on_acct_row.last_update_date);
1848 dbms_sql.column_value(c_on_acct,5,on_acct_row.last_updated_by);
1849 dbms_sql.column_value(c_on_acct,6,on_acct_row.creation_date);
1850 dbms_sql.column_value(c_on_acct,7,on_acct_row.created_by);
1851 dbms_sql.column_value(c_on_acct,8,on_acct_row.last_update_login);
1852 dbms_sql.column_value(c_on_acct,9,on_acct_row.program_application_id);
1853 dbms_sql.column_value(c_on_acct,10,on_acct_row.program_id);
1854 dbms_sql.column_value(c_on_acct,11,on_acct_row.program_update_date);
1855 dbms_sql.column_value(c_on_acct,12,on_acct_row.request_id);
1856 dbms_sql.column_value(c_on_acct,13,on_acct_row.applied_flag);
1857 dbms_sql.column_value(c_on_acct,14,on_acct_row.customer_id);
1858 dbms_sql.column_value(c_on_acct,15,on_acct_row.trx_number);
1859 dbms_sql.column_value(c_on_acct,16,on_acct_row.discounts_earned);
1860 dbms_sql.column_value(c_on_acct,17,on_acct_row.discounts_unearned);
1861 dbms_sql.column_value(c_on_acct,18,on_acct_row.discount_taken_earned);
1862 dbms_sql.column_value(c_on_acct,19,on_acct_row.discount_taken_unearned);
1863 dbms_sql.column_value(c_on_acct,20,on_acct_row.amount_due_remaining);
1864 dbms_sql.column_value(c_on_acct,21,on_acct_row.status);
1865 dbms_sql.column_value(c_on_acct,22,on_acct_row.term_id);
1866 dbms_sql.column_value(c_on_acct,23,on_acct_row.bill_to_site_use_id);
1867 dbms_sql.column_value(c_on_acct,24,on_acct_row.apply_date);
1868 dbms_sql.column_value(c_on_acct,25,on_acct_row.gl_date);
1869 dbms_sql.column_value(c_on_acct,26,on_acct_row.invoice_currency_code);
1870 dbms_sql.column_value(c_on_acct,27,on_acct_row.amount_due_original);
1871 dbms_sql.column_value(c_on_acct,28,on_acct_row.amount_in_dispute);
1872 dbms_sql.column_value(c_on_acct,29,on_acct_row.amount_line_items_original);
1873 dbms_sql.column_value(c_on_acct,30,on_acct_row.discount_remaining);
1874 dbms_sql.column_value(c_on_acct,31,on_acct_row.applied_payment_schedule_id);
1875
1876 row_id := on_acct_row.row_id;
1877 cash_receipt_id := on_acct_row.cash_receipt_id;
1878 customer_trx_id := on_acct_row.customer_trx_id;
1879 last_update_date := on_acct_row.last_update_date;
1880 last_updated_by := on_acct_row.last_updated_by;
1881 creation_date := on_acct_row.creation_date;
1882 created_by := on_acct_row.created_by;
1886 program_update_date := on_acct_row.program_update_date;
1883 last_update_login := on_acct_row.last_update_login;
1884 program_application_id := on_acct_row.program_application_id;
1885 program_id := on_acct_row.program_id;
1887 request_id := on_acct_row.request_id;
1888 applied_flag := on_acct_row.applied_flag;
1889 customer_id := on_acct_row.customer_id;
1890 trx_number := on_acct_row.trx_number;
1891 discounts_earned := on_acct_row.discounts_earned;
1892 discounts_unearned := on_acct_row.discounts_unearned;
1893 discount_taken_earned := on_acct_row.discount_taken_earned;
1894 discount_taken_unearned := on_acct_row.discount_taken_unearned;
1895 amount_due_remaining := on_acct_row.amount_due_remaining;
1896 status := on_acct_row.status;
1897 term_id := on_acct_row.term_id;
1898 bill_to_site_use_id := on_acct_row.bill_to_site_use_id;
1899 apply_date := on_acct_row.apply_date;
1900 gl_date := on_acct_row.gl_date;
1901 invoice_currency_code := on_acct_row.invoice_currency_code;
1902 amount_due_original := on_acct_row.amount_due_original;
1903 amount_in_dispute := on_acct_row.amount_in_dispute;
1904 amount_line_items_original:= on_acct_row.amount_line_items_original;
1905 discount_remaining := on_acct_row.discount_remaining;
1906 applied_payment_schedule_id:= on_acct_row.applied_payment_schedule_id;
1907 -- ARTA Changes
1908 purchase_order := NULL;
1909 transaction_category := NULL;
1910
1911 IF PG_DEBUG in ('Y', 'C') THEN
1912 arp_standard.debug( 'Close On Acct Cursor' );
1913 END IF;
1914 dbms_sql.close_cursor(c_on_acct);
1915 return(TRUE);
1916
1917 END IF;
1918
1919 -----------------------------------------------------
1920 --
1921 -- Credit Memo Applications
1922 --
1923 -- We are working with the Credit Memo Applications
1924 -- cursor. Loop through the cursor until all
1925 -- records have been returned.
1926 --
1927 -----------------------------------------------------
1928 ELSIF dbms_sql.is_open(c_cm_apps) THEN
1929
1930 IF PG_DEBUG in ('Y', 'C') THEN
1931 arp_standard.debug( 'CM Apps cursor is OPEN' );
1932 END IF;
1933 IF dbms_sql.fetch_rows(c_cm_apps) > 0 THEN
1934
1935 IF PG_DEBUG in ('Y', 'C') THEN
1936 arp_standard.debug( 'And we have fetched a record from CM Apps' );
1937 END IF;
1938 dbms_sql.column_value_rowid(c_cm_apps,1,cm_apps_row.row_id);
1939 dbms_sql.column_value(c_cm_apps,2,cm_apps_row.cash_receipt_id);
1940 dbms_sql.column_value(c_cm_apps,3,cm_apps_row.customer_trx_id);
1941 dbms_sql.column_value(c_cm_apps,4,cm_apps_row.cm_customer_trx_id);
1942 dbms_sql.column_value(c_cm_apps,5,cm_apps_row.last_update_date);
1943 dbms_sql.column_value(c_cm_apps,6,cm_apps_row.last_updated_by);
1944 dbms_sql.column_value(c_cm_apps,7,cm_apps_row.creation_date);
1945 dbms_sql.column_value(c_cm_apps,8,cm_apps_row.created_by);
1946 dbms_sql.column_value(c_cm_apps,9,cm_apps_row.last_update_login);
1947 dbms_sql.column_value(c_cm_apps,10,cm_apps_row.program_application_id);
1948 dbms_sql.column_value(c_cm_apps,11,cm_apps_row.program_id);
1949 dbms_sql.column_value(c_cm_apps,12,cm_apps_row.program_update_date);
1950 dbms_sql.column_value(c_cm_apps,13,cm_apps_row.request_id);
1951 dbms_sql.column_value(c_cm_apps,14,cm_apps_row.receipt_number);
1952 dbms_sql.column_value(c_cm_apps,15,cm_apps_row.applied_flag);
1953 dbms_sql.column_value(c_cm_apps,16,cm_apps_row.customer_id);
1954 dbms_sql.column_value(c_cm_apps,17,cm_apps_row.customer_name);
1955 dbms_sql.column_value(c_cm_apps,18,cm_apps_row.customer_number);
1956 dbms_sql.column_value(c_cm_apps,19,cm_apps_row.trx_number);
1957 dbms_sql.column_value(c_cm_apps,20,cm_apps_row.installment);
1958 dbms_sql.column_value(c_cm_apps,21,cm_apps_row.amount_applied);
1959 dbms_sql.column_value(c_cm_apps,22,cm_apps_row.amount_applied_from);
1960 dbms_sql.column_value(c_cm_apps,23,cm_apps_row.trans_to_receipt_rate);
1961 dbms_sql.column_value(c_cm_apps,24,cm_apps_row.discount);
1962 dbms_sql.column_value(c_cm_apps,25,cm_apps_row.discounts_earned);
1963 dbms_sql.column_value(c_cm_apps,26,cm_apps_row.discounts_unearned);
1964 dbms_sql.column_value(c_cm_apps,27,cm_apps_row.discount_taken_earned);
1965 dbms_sql.column_value(c_cm_apps,28,cm_apps_row.discount_taken_unearned);
1966 dbms_sql.column_value(c_cm_apps,29,cm_apps_row.amount_due_remaining);
1967 dbms_sql.column_value(c_cm_apps,30,cm_apps_row.due_date);
1968 dbms_sql.column_value(c_cm_apps,31,cm_apps_row.status);
1969 dbms_sql.column_value(c_cm_apps,32,cm_apps_row.term_id);
1970 dbms_sql.column_value(c_cm_apps,33,cm_apps_row.trx_class_name);
1971 dbms_sql.column_value(c_cm_apps,34,cm_apps_row.trx_class_code);
1972 dbms_sql.column_value(c_cm_apps,35,cm_apps_row.trx_type_name);
1973 dbms_sql.column_value(c_cm_apps,36,cm_apps_row.cust_trx_type_id);
1974 dbms_sql.column_value(c_cm_apps,37,cm_apps_row.trx_date);
1975 dbms_sql.column_value(c_cm_apps,38,cm_apps_row.location_name);
1976 dbms_sql.column_value(c_cm_apps,39,cm_apps_row.bill_to_site_use_id);
1977 dbms_sql.column_value(c_cm_apps,40,cm_apps_row.days_late);
1978 dbms_sql.column_value(c_cm_apps,41,cm_apps_row.line_number);
1979 dbms_sql.column_value(c_cm_apps,42,cm_apps_row.customer_trx_line_id);
1980 dbms_sql.column_value(c_cm_apps,43,cm_apps_row.apply_date);
1981 dbms_sql.column_value(c_cm_apps,44,cm_apps_row.gl_date);
1982 dbms_sql.column_value(c_cm_apps,45,cm_apps_row.gl_posted_date);
1986 dbms_sql.column_value(c_cm_apps,49,cm_apps_row.amount_due_original);
1983 dbms_sql.column_value(c_cm_apps,46,cm_apps_row.reversal_gl_date);
1984 dbms_sql.column_value(c_cm_apps,47,cm_apps_row.exchange_rate);
1985 dbms_sql.column_value(c_cm_apps,48,cm_apps_row.invoice_currency_code);
1987 dbms_sql.column_value(c_cm_apps,50,cm_apps_row.amount_in_dispute);
1988 dbms_sql.column_value(c_cm_apps,51,cm_apps_row.amount_line_items_original);
1989 dbms_sql.column_value(c_cm_apps,52,cm_apps_row.acctd_amount_due_remaining);
1990 dbms_sql.column_value(c_cm_apps,53,cm_apps_row.acctd_amount_applied_to);
1991 dbms_sql.column_value(c_cm_apps,54,cm_apps_row.acctd_amount_applied_from);
1992 dbms_sql.column_value(c_cm_apps,55,cm_apps_row.exchange_gain_loss);
1993 dbms_sql.column_value(c_cm_apps,56,cm_apps_row.discount_remaining);
1994 dbms_sql.column_value(c_cm_apps,57,cm_apps_row.calc_discount_on_lines_flag);
1995 dbms_sql.column_value(c_cm_apps,58,cm_apps_row.partial_discount_flag);
1996 dbms_sql.column_value(c_cm_apps,59,cm_apps_row.allow_overapplication_flag);
1997 dbms_sql.column_value(c_cm_apps,60,cm_apps_row.natural_application_only_flag);
1998 dbms_sql.column_value(c_cm_apps,61,cm_apps_row.creation_sign);
1999 dbms_sql.column_value(c_cm_apps,62,cm_apps_row.applied_payment_schedule_id);
2000 dbms_sql.column_value(c_cm_apps,63,cm_apps_row.ussgl_transaction_code);
2001 dbms_sql.column_value(c_cm_apps,64,cm_apps_row.ussgl_transaction_code_context);
2002 dbms_sql.column_value(c_cm_apps,65,cm_apps_row.purchase_order);
2003 dbms_sql.column_value(c_cm_apps,66,cm_apps_row.trx_doc_sequence_id);
2004 dbms_sql.column_value(c_cm_apps,67,cm_apps_row.trx_doc_sequence_value);
2005 dbms_sql.column_value(c_cm_apps,68,cm_apps_row.trx_batch_source_name);
2006 dbms_sql.column_value(c_cm_apps,69,cm_apps_row.amount_adjusted);
2007 dbms_sql.column_value(c_cm_apps,70,cm_apps_row.amount_adjusted_pending);
2008 dbms_sql.column_value(c_cm_apps,71,cm_apps_row.amount_line_items_remaining);
2009 dbms_sql.column_value(c_cm_apps,72,cm_apps_row.freight_original);
2010 dbms_sql.column_value(c_cm_apps,73,cm_apps_row.freight_remaining);
2011 dbms_sql.column_value(c_cm_apps,74,cm_apps_row.receivables_charges_remaining);
2012 dbms_sql.column_value(c_cm_apps,75,cm_apps_row.tax_original);
2013 dbms_sql.column_value(c_cm_apps,76,cm_apps_row.tax_remaining);
2014 dbms_sql.column_value(c_cm_apps,77,cm_apps_row.selected_for_receipt_batch_id);
2015 dbms_sql.column_value(c_cm_apps,78,cm_apps_row.receivable_application_id);
2016 dbms_sql.column_value(c_cm_apps,79,cm_apps_row.attribute_category);
2017 dbms_sql.column_value(c_cm_apps,80,cm_apps_row.attribute1);
2018 dbms_sql.column_value(c_cm_apps,81,cm_apps_row.attribute2);
2019 dbms_sql.column_value(c_cm_apps,82,cm_apps_row.attribute3);
2020 dbms_sql.column_value(c_cm_apps,83,cm_apps_row.attribute4);
2021 dbms_sql.column_value(c_cm_apps,84,cm_apps_row.attribute5);
2022 dbms_sql.column_value(c_cm_apps,85,cm_apps_row.attribute6);
2023 dbms_sql.column_value(c_cm_apps,86,cm_apps_row.attribute7);
2024 dbms_sql.column_value(c_cm_apps,87,cm_apps_row.attribute8);
2025 dbms_sql.column_value(c_cm_apps,88,cm_apps_row.attribute9);
2026 dbms_sql.column_value(c_cm_apps,89,cm_apps_row.attribute10);
2027 dbms_sql.column_value(c_cm_apps,90,cm_apps_row.attribute11);
2028 dbms_sql.column_value(c_cm_apps,91,cm_apps_row.attribute12);
2029 dbms_sql.column_value(c_cm_apps,92,cm_apps_row.attribute13);
2030 dbms_sql.column_value(c_cm_apps,93,cm_apps_row.attribute14);
2031 dbms_sql.column_value(c_cm_apps,94,cm_apps_row.attribute15);
2032 dbms_sql.column_value(c_cm_apps,95,cm_apps_row.trx_billing_number);
2033 dbms_sql.column_value(c_cm_apps,96,cm_apps_row.global_attribute_category);
2034 dbms_sql.column_value(c_cm_apps,97,cm_apps_row.global_attribute1);
2035 dbms_sql.column_value(c_cm_apps,98,cm_apps_row.global_attribute2);
2036 dbms_sql.column_value(c_cm_apps,99,cm_apps_row.global_attribute3);
2037 dbms_sql.column_value(c_cm_apps,100,cm_apps_row.global_attribute4);
2038 dbms_sql.column_value(c_cm_apps,101,cm_apps_row.global_attribute5);
2039 dbms_sql.column_value(c_cm_apps,102,cm_apps_row.global_attribute6);
2040 dbms_sql.column_value(c_cm_apps,103,cm_apps_row.global_attribute7);
2041 dbms_sql.column_value(c_cm_apps,104,cm_apps_row.global_attribute8);
2042 dbms_sql.column_value(c_cm_apps,105,cm_apps_row.global_attribute9);
2043 dbms_sql.column_value(c_cm_apps,106,cm_apps_row.global_attribute10);
2044 dbms_sql.column_value(c_cm_apps,107,cm_apps_row.global_attribute11);
2045 dbms_sql.column_value(c_cm_apps,108,cm_apps_row.global_attribute12);
2046 dbms_sql.column_value(c_cm_apps,109,cm_apps_row.global_attribute13);
2047 dbms_sql.column_value(c_cm_apps,110,cm_apps_row.global_attribute14);
2048 dbms_sql.column_value(c_cm_apps,111,cm_apps_row.global_attribute15);
2049 dbms_sql.column_value(c_cm_apps,112,cm_apps_row.global_attribute16);
2050 dbms_sql.column_value(c_cm_apps,113,cm_apps_row.global_attribute17);
2051 dbms_sql.column_value(c_cm_apps,114,cm_apps_row.global_attribute18);
2052 dbms_sql.column_value(c_cm_apps,115,cm_apps_row.global_attribute19);
2053 dbms_sql.column_value(c_cm_apps,116,cm_apps_row.global_attribute20);
2054 dbms_sql.column_value(c_cm_apps,117,cm_apps_row.transaction_category); -- ARTA Changes
2055 dbms_sql.column_value(c_cm_apps,118,cm_apps_row.trx_gl_date);
2056 dbms_sql.column_value(c_cm_apps,119,cm_apps_row.comments); -- bug 2662270
2057 /* cols 120-125 added for CM refunds */
2058 dbms_sql.column_value(c_cm_apps,120,cm_apps_row.receivables_trx_id);
2059 dbms_sql.column_value(c_cm_apps,121,cm_apps_row.rec_activity_name);
2060 dbms_sql.column_value(c_cm_apps,122,cm_apps_row.application_ref_id);
2061 dbms_sql.column_value(c_cm_apps,123,cm_apps_row.application_ref_num);
2062 dbms_sql.column_value(c_cm_apps,124,cm_apps_row.application_ref_type);
2063 dbms_sql.column_value(c_cm_apps,125,cm_apps_row.application_ref_type_meaning);
2064
2068 cm_customer_trx_id :=cm_apps_row.cm_customer_trx_id;
2065 row_id :=cm_apps_row.row_id;
2066 cash_receipt_id :=cm_apps_row.cash_receipt_id;
2067 customer_trx_id :=cm_apps_row.customer_trx_id;
2069 last_update_date :=cm_apps_row.last_update_date;
2070 last_updated_by :=cm_apps_row.last_updated_by;
2071 creation_date :=cm_apps_row.creation_date;
2072 created_by :=cm_apps_row.created_by;
2073 last_update_login :=cm_apps_row.last_update_login;
2074 program_application_id :=cm_apps_row.program_application_id;
2075 program_id :=cm_apps_row.program_id;
2076 program_update_date :=cm_apps_row.program_update_date;
2077 request_id :=cm_apps_row.request_id;
2078 receipt_number :=cm_apps_row.receipt_number;
2079 applied_flag :=cm_apps_row.applied_flag;
2080 customer_id :=cm_apps_row.customer_id;
2081 customer_name :=cm_apps_row.customer_name;
2082 customer_number :=cm_apps_row.customer_number;
2083 trx_number :=cm_apps_row.trx_number;
2084 installment :=cm_apps_row.installment;
2085 amount_applied :=cm_apps_row.amount_applied;
2086 amount_applied_from :=cm_apps_row.amount_applied_from;
2087 trans_to_receipt_rate :=cm_apps_row.trans_to_receipt_rate;
2088 discount :=cm_apps_row.discount;
2089 discounts_earned :=cm_apps_row.discounts_earned;
2090 discounts_unearned :=cm_apps_row.discounts_unearned;
2091 discount_taken_earned :=cm_apps_row.discount_taken_earned;
2092 discount_taken_unearned :=cm_apps_row.discount_taken_unearned;
2093 amount_due_remaining :=cm_apps_row.amount_due_remaining;
2094 due_date :=cm_apps_row.due_date;
2095 status :=cm_apps_row.status;
2096 term_id :=cm_apps_row.term_id;
2097 trx_class_name :=cm_apps_row.trx_class_name;
2098 trx_class_code :=cm_apps_row.trx_class_code;
2099 trx_type_name :=cm_apps_row.trx_type_name;
2100 cust_trx_type_id :=cm_apps_row.cust_trx_type_id;
2101 trx_date :=cm_apps_row.trx_date;
2102 location_name :=cm_apps_row.location_name;
2103 bill_to_site_use_id :=cm_apps_row.bill_to_site_use_id;
2104 days_late :=cm_apps_row.days_late;
2105 line_number :=cm_apps_row.line_number;
2106 customer_trx_line_id :=cm_apps_row.customer_trx_line_id;
2107 apply_date :=cm_apps_row.apply_date;
2108 gl_date :=cm_apps_row.gl_date;
2109 gl_posted_date :=cm_apps_row.gl_posted_date;
2110 reversal_gl_date :=cm_apps_row.reversal_gl_date;
2111 exchange_rate :=cm_apps_row.exchange_rate;
2112 invoice_currency_code :=cm_apps_row.invoice_currency_code;
2113 amount_due_original :=cm_apps_row.amount_due_original;
2114 amount_in_dispute :=cm_apps_row.amount_in_dispute;
2115 amount_line_items_original:=cm_apps_row.amount_line_items_original;
2116 acctd_amount_due_remaining:=cm_apps_row.acctd_amount_due_remaining;
2117 acctd_amount_applied_to :=cm_apps_row.acctd_amount_applied_to;
2118 acctd_amount_applied_from :=cm_apps_row.acctd_amount_applied_from;
2119 exchange_gain_loss :=cm_apps_row.exchange_gain_loss;
2120 discount_remaining :=cm_apps_row.discount_remaining;
2121 calc_discount_on_lines_flag:=cm_apps_row.calc_discount_on_lines_flag;
2122 partial_discount_flag :=cm_apps_row.partial_discount_flag;
2123 allow_overapplication_flag:=cm_apps_row.allow_overapplication_flag;
2124 natural_application_only_flag:=cm_apps_row.natural_application_only_flag;
2125 creation_sign :=cm_apps_row.creation_sign;
2126 applied_payment_schedule_id:=cm_apps_row.applied_payment_schedule_id;
2127 ussgl_transaction_code :=cm_apps_row.ussgl_transaction_code;
2128 ussgl_transaction_code_context:=cm_apps_row.ussgl_transaction_code_context;
2129 purchase_order :=cm_apps_row.purchase_order;
2130 trx_doc_sequence_id :=cm_apps_row.trx_doc_sequence_id;
2131 trx_doc_sequence_value :=cm_apps_row.trx_doc_sequence_value;
2132 trx_batch_source_name :=cm_apps_row.trx_batch_source_name;
2133 amount_adjusted :=cm_apps_row.amount_adjusted;
2134 amount_adjusted_pending :=cm_apps_row.amount_adjusted_pending;
2135 amount_line_items_remaining:=cm_apps_row.amount_line_items_remaining;
2136 freight_original :=cm_apps_row.freight_original;
2137 freight_remaining :=cm_apps_row.freight_remaining;
2138 receivables_charges_remaining:=cm_apps_row.receivables_charges_remaining;
2139 tax_original :=cm_apps_row.tax_original;
2140 tax_remaining :=cm_apps_row.tax_remaining;
2141 selected_for_receipt_batch_id:=cm_apps_row.selected_for_receipt_batch_id;
2142 receivable_application_id :=cm_apps_row.receivable_application_id;
2143 attribute_category :=cm_apps_row.attribute_category;
2144 attribute1 :=cm_apps_row.attribute1;
2145 attribute2 :=cm_apps_row.attribute2;
2146 attribute3 :=cm_apps_row.attribute3;
2147 attribute4 :=cm_apps_row.attribute4;
2148 attribute5 :=cm_apps_row.attribute5;
2149 attribute6 :=cm_apps_row.attribute6;
2150 attribute7 :=cm_apps_row.attribute7;
2151 attribute8 :=cm_apps_row.attribute8;
2152 attribute9 :=cm_apps_row.attribute9;
2153 attribute10 :=cm_apps_row.attribute10;
2157 attribute14 :=cm_apps_row.attribute14;
2154 attribute11 :=cm_apps_row.attribute11;
2155 attribute12 :=cm_apps_row.attribute12;
2156 attribute13 :=cm_apps_row.attribute13;
2158 attribute15 :=cm_apps_row.attribute15;
2159 trx_billing_number :=cm_apps_row.trx_billing_number;
2160 global_attribute_category :=cm_apps_row.global_attribute_category;
2161 global_attribute1 :=cm_apps_row.global_attribute1;
2162 global_attribute2 :=cm_apps_row.global_attribute2;
2163 global_attribute3 :=cm_apps_row.global_attribute3;
2164 global_attribute4 :=cm_apps_row.global_attribute4;
2165 global_attribute5 :=cm_apps_row.global_attribute5;
2166 global_attribute6 :=cm_apps_row.global_attribute6;
2167 global_attribute7 :=cm_apps_row.global_attribute7;
2168 global_attribute8 :=cm_apps_row.global_attribute8;
2169 global_attribute9 :=cm_apps_row.global_attribute9;
2170 global_attribute10 :=cm_apps_row.global_attribute10;
2171 global_attribute11 :=cm_apps_row.global_attribute11;
2172 global_attribute12 :=cm_apps_row.global_attribute12;
2173 global_attribute13 :=cm_apps_row.global_attribute13;
2174 global_attribute14 :=cm_apps_row.global_attribute14;
2175 global_attribute15 :=cm_apps_row.global_attribute15;
2176 global_attribute16 :=cm_apps_row.global_attribute16;
2177 global_attribute17 :=cm_apps_row.global_attribute17;
2178 global_attribute18 :=cm_apps_row.global_attribute18;
2179 global_attribute19 :=cm_apps_row.global_attribute19;
2180 global_attribute20 :=cm_apps_row.global_attribute20;
2181 -- ARTA Changes
2182 transaction_category := NULL;
2183 trx_gl_date :=cm_apps_row.trx_gl_date;
2184 comments :=cm_apps_row.comments; -- bug 2662270
2185 receivables_trx_id :=cm_apps_row.receivables_trx_id; --
2186 rec_activity_name :=cm_apps_row.rec_activity_name; --
2187 application_ref_id :=cm_apps_row.application_ref_id; -- CM refunds
2188 application_ref_num :=cm_apps_row.application_ref_num; --
2189 application_ref_type :=cm_apps_row.application_ref_type; --
2190 application_ref_type_meaning :=cm_apps_row.application_ref_type_meaning; --
2191
2192 return(TRUE);
2193
2194 ELSE
2195
2196 IF PG_DEBUG in ('Y', 'C') THEN
2197 arp_standard.debug( 'CM Apps has no more records so we will close it' );
2198 END IF;
2199 dbms_sql.close_cursor(c_cm_apps);
2200 return(FALSE);
2201
2202 END IF;
2203
2204 ELSE
2205
2206 IF PG_DEBUG in ('Y', 'C') THEN
2207 arp_standard.debug( 'Nothing to do!' );
2208 END IF;
2209 return(FALSE);
2210
2211 END IF;
2212
2213 IF PG_DEBUG in ('Y', 'C') THEN
2214 arp_standard.debug( 'on_fetch()-' );
2215 END IF;
2216
2217 EXCEPTION
2218 WHEN OTHERS THEN
2219 IF PG_DEBUG in ('Y', 'C') THEN
2220 arp_standard.debug('EXCEPTION: ar_add_fetch_select.on_fetch');
2221 END IF;
2222 RAISE;
2223
2224 END on_fetch;
2225
2226 END ar_add_fetch_select;