[Home] [Help]
PACKAGE BODY: APPS.ARRX_BRS
Source
1 PACKAGE BODY arrx_brs AS
2 /* $Header: ARRXBRB.pls 120.5 2006/04/24 12:08:49 ggadhams ship $ */
3
4 PROCEDURE arrxbrs_report(p_request_id IN NUMBER
5 ,p_user_id IN NUMBER
6 ,p_reporting_level IN VARCHAR2
7 ,p_reporting_entity_id IN NUMBER
8 ,p_status_as_of_date IN DATE
9 ,p_first_status IN VARCHAR2
10 ,p_second_status IN VARCHAR2
11 ,p_third_status IN VARCHAR2
12 ,p_excluded_status IN VARCHAR2
13 ,p_transaction_type IN VARCHAR2
14 ,p_maturity_date_from IN DATE
15 ,p_maturity_date_to IN DATE
16 ,p_drawee_name IN VARCHAR2
17 ,p_drawee_number_from IN VARCHAR2
18 ,p_drawee_number_to IN VARCHAR2
19 ,p_remittance_batch_name IN VARCHAR2
20 ,p_remittance_bank_account IN VARCHAR2
21 ,p_drawee_bank_name IN VARCHAR2
22 ,p_original_amount_from IN NUMBER
23 ,p_original_amount_to IN NUMBER
24 ,p_transaction_issue_date_from IN DATE
25 ,p_transaction_issue_date_to IN DATE
26 ,p_on_hold IN VARCHAR2
27 ,retcode OUT NOCOPY NUMBER
28 ,errbuf OUT NOCOPY VARCHAR2) AS
29
30 -- Declare local variables
31 l_login_id NUMBER;
32 l_creation_gl_date DATE;
33 l_original_maturity_date DATE;
34 l_unpaid_date DATE;
35 l_acceptance_date DATE;
36 l_remit_date DATE;
37 l_as_of_date VARCHAR2(4000);
38 l_remit_batch VARCHAR2(4000);
39 l_open_amount VARCHAR2(4000);
40 l_assigned_amount VARCHAR2(4000);
41 l_receipt_reversal VARCHAR2(4000);
42 l_status_where VARCHAR2(200);
43 l_excluded_status_where VARCHAR2(100);
44 l_transaction_type_where VARCHAR2(100);
45 l_maturity_date_where VARCHAR2(200);
46 l_drawee_name_where VARCHAR2(300);
47 l_drawee_number_where VARCHAR2(200);
48 l_drawee_bank_name_where VARCHAR2(100);
49 l_issue_date_where VARCHAR2(200);
50 l_on_hold_where VARCHAR2(100);
51 l_org_where_trx VARCHAR2(2000);
52 l_org_where_trl VARCHAR2(2000);
53 l_org_where_rabs VARCHAR2(2000);
54 l_org_where_ctt VARCHAR2(2000);
55 l_org_where_raa VARCHAR2(2000);
56 l_org_where_rasu VARCHAR2(2000);
57 l_org_where_aba VARCHAR2(2000);
58 l_org_where_raba VARCHAR2(2000);
59 l_org_where_rah VARCHAR2(2000);
60 l_org_where_rah1 VARCHAR2(2000);
61 l_org_where_ps VARCHAR2(2000);
62 l_org_where_rab VARCHAR2(2000);
63 l_org_where_arb VARCHAR2(2000);
64 l_org_where_app VARCHAR2(2000);
65 l_org_where_cr VARCHAR2(2000);
66 l_books_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
67 l_currency_code GL_SETS_OF_BOOKS.currency_code%TYPE;
68 l_sob_name GL_SETS_OF_BOOKS.name%TYPE;
69 l_populate VARCHAR2(1);
70 l_populate_amt VARCHAR2(1) := 'Y';
71 l_new_ADR AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
72 l_new_acctd_ADR AR_PAYMENT_SCHEDULES.acctd_amount_due_remaining%TYPE;
73 l_new_original_funct_amt AR_PAYMENT_SCHEDULES.amount_due_original%TYPE;
74
75 -- Declare variables for Dynamic Cursors
76 v_CursorID_main INTEGER;
77 v_Dummy_main INTEGER;
78 v_CursorID_date INTEGER;
79 v_Dummy_date INTEGER;
80 v_CursorID_rbatch INTEGER;
81 v_Dummy_rbatch INTEGER;
82 v_CursorID_amt INTEGER;
83 v_Dummy_amt INTEGER;
84 v_CursorID_rev INTEGER;
85 v_Dummy_rev INTEGER;
86 v_CursorID_asg INTEGER;
87 v_Dummy_asg INTEGER;
88
89 -- Declare the variables which will hold the results of the SELECT statements
90 v_status AR_LOOKUPS.meaning%TYPE;
91 v_status_date AR_TRANSACTION_HISTORY.trx_date%TYPE;
92 v_customer_trx_id RA_CUSTOMER_TRX.customer_trx_id%TYPE;
93 v_transaction_number RA_CUSTOMER_TRX.trx_number%TYPE;
94 v_document_number RA_CUSTOMER_TRX.doc_sequence_value%TYPE;
95 v_document_sequence_name FND_DOCUMENT_SEQUENCES.name%TYPE;
96 v_currency_code RA_CUSTOMER_TRX.invoice_currency_code%TYPE;
97 v_magnetic_format_code RA_CUST_TRX_TYPES.magnetic_format_code%TYPE;
98 v_original_entered_amount AR_PAYMENT_SCHEDULES.amount_due_original%TYPE;
99 v_open_entered_amount AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
100 v_open_functional_amount AR_PAYMENT_SCHEDULES.acctd_amount_due_remaining%TYPE;
101 v_assigned_entered_amount RA_CUSTOMER_TRX_LINES.extended_amount%TYPE;
102 v_assigned_functional_amount RA_CUSTOMER_TRX_LINES.extended_acctd_amount%TYPE;
103 v_drawee_name hz_parties.party_name%TYPE;
104 v_drawee_number hz_cust_accounts.account_number%TYPE;
105 v_drawee_taxpayer_id hz_parties.jgzz_fiscal_code%TYPE;
106 v_drawee_vat_reg_number HZ_CUST_SITE_USES.tax_reference%TYPE;
107 v_drawee_city HZ_LOCATIONS.city%TYPE;
108 v_drawee_state HZ_LOCATIONS.state%TYPE;
109 v_drawee_country HZ_LOCATIONS.country%TYPE;
110 v_drawee_postal_code HZ_LOCATIONS.postal_code%TYPE;
111 v_drawee_class AR_LOOKUPS.meaning%TYPE;
112 v_drawee_category AR_LOOKUPS.meaning%TYPE;
113 v_drawee_location HZ_CUST_SITE_USES.location%TYPE;
114 v_issue_date RA_CUSTOMER_TRX.trx_date%TYPE;
115 v_status_gl_date AR_TRANSACTION_HISTORY.gl_date%TYPE;
116 v_maturity_date RA_CUSTOMER_TRX.term_due_date%TYPE;
117 v_issued_by_drawee RA_CUST_TRX_TYPES.drawee_issued_flag%TYPE;
118 v_signed_by_drawee RA_CUST_TRX_TYPES.signed_flag%TYPE;
119 v_transaction_type RA_CUST_TRX_TYPES.name%TYPE;
120 v_transaction_batch_source RA_BATCH_SOURCES.name%TYPE;
121 v_remit_bank_name ce_bank_branches_v.bank_name%TYPE;
122 v_remit_bank_number ce_bank_branches_v.bank_number%TYPE;
123 v_remit_branch_name ce_bank_branches_v.bank_branch_name%TYPE;
124 v_remit_branch_number ce_bank_branches_v.branch_number%TYPE;
125 v_remit_bank_acc_name ce_bank_accounts.bank_account_name%TYPE;
126 v_remit_bank_acc_number ce_bank_accounts.bank_account_num%TYPE;
127 v_remit_bank_acc_id ce_bank_accounts.bank_account_id%TYPE;
128 v_remit_branch_city ce_bank_branches_v.city%TYPE;
129 v_remit_branch_state ce_bank_branches_v.state%TYPE;
130 v_remit_branch_country ce_bank_branches_v.country%TYPE;
131 v_remit_branch_postal_code ce_bank_branches_v.zip%TYPE;
132 v_remit_branch_address1 ce_bank_branches_v.address_line1%TYPE;
133 v_remit_branch_address2 ce_bank_branches_v.address_line2%TYPE;
134 v_remit_branch_address3 ce_bank_branches_v.address_line3%TYPE;
135 v_remit_bank_allow_override RA_CUSTOMER_TRX.override_remit_account_flag%TYPE;
136 v_remit_bank_acc_check_digits ce_bank_accounts.check_digits%TYPE;
137 v_remit_bank_acc_curr ce_bank_accounts.currency_code%TYPE;
138 v_drawee_bank_name ce_bank_branches_v.bank_name%TYPE;
139 v_drawee_bank_number ce_bank_branches_v.bank_number%TYPE;
140 v_drawee_branch_name ce_bank_branches_v.bank_branch_name%TYPE;
141 v_drawee_branch_number ce_bank_branches_v.branch_number%TYPE;
142 v_drawee_bank_acc_name AP_BANK_ACCOUNTS.bank_account_name%TYPE;
143 v_drawee_bank_acc_number AP_BANK_ACCOUNTS.bank_account_num%TYPE;
144 v_drawee_branch_city ce_bank_branches_v.city%TYPE;
145 v_drawee_branch_state ce_bank_branches_v.state%TYPE;
146 v_drawee_branch_country ce_bank_branches_v.country%TYPE;
147 v_drawee_branch_postal_code ce_bank_branches_v.zip%TYPE;
148 v_drawee_branch_address1 ce_bank_branches_v.address_line1%TYPE;
149 v_drawee_branch_address2 ce_bank_branches_v.address_line2%TYPE;
150 v_drawee_branch_address3 ce_bank_branches_v.address_line3%TYPE;
151 v_drawee_bank_acc_check_digits AP_BANK_ACCOUNTS.check_digits%TYPE;
152 v_drawee_bank_acc_curr AP_BANK_ACCOUNTS.currency_code%TYPE;
153 v_comments RA_CUSTOMER_TRX.comments%TYPE;
154 v_days_late NUMBER;
155 v_remittance_batch_name AR_BATCHES.name%TYPE;
156 v_remittance_method AR_BATCHES.remit_method_code%TYPE;
157 v_with_recourse AR_BATCHES.with_recourse_flag%TYPE;
158 v_last_printed_date RA_CUSTOMER_TRX.printing_last_printed%TYPE;
159 v_unpaid_receipt_rev_reason AR_LOOKUPS.meaning%TYPE;
160 v_risk_elimination_days AR_RECEIPT_METHOD_ACCOUNTS.risk_elimination_days%TYPE;
161 v_remittance_date AR_TRANSACTION_HISTORY.trx_date%TYPE;
162 v_remittance_payment_method AR_RECEIPT_METHODS.name%TYPE;
163 v_creation_batch_name RA_BATCHES.name%TYPE;
164 v_drawee_address1 HZ_LOCATIONS.address1%TYPE;
165 v_drawee_address2 HZ_LOCATIONS.address2%TYPE;
166 v_drawee_address3 HZ_LOCATIONS.address3%TYPE;
167 v_drawee_contact VARCHAR2(100);
168 v_special_instructions RA_CUSTOMER_TRX.special_instructions%TYPE;
169 v_status_code AR_TRANSACTION_HISTORY.status%TYPE;
170 v_amount_applied AR_RECEIVABLE_APPLICATIONS.amount_applied%TYPE;
171 v_functional_amount_applied AR_RECEIVABLE_APPLICATIONS.amount_applied%TYPE;
172 v_ps_exchange_rate AR_PAYMENT_SCHEDULES.exchange_rate%TYPE;
173
174
175
176 BEGIN
177
178 -- Initialise status parameters
179 retcode := 2;
180 errbuf := 'Inner Package Failure';
181
182 -- Initialize MO Reporting
183 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
184
185 -- Initialize the org parameters for the ALL tables
186 l_org_where_trx := XLA_MO_REPORTING_API.Get_Predicate('trx', null);
187 l_org_where_trl := XLA_MO_REPORTING_API.Get_Predicate('trl', null);
188 l_org_where_rabs := XLA_MO_REPORTING_API.Get_Predicate('rabs', null);
189 l_org_where_ctt := XLA_MO_REPORTING_API.Get_Predicate('ctt', null);
190 l_org_where_raa := XLA_MO_REPORTING_API.Get_Predicate('raa', null);
191 l_org_where_rasu := XLA_MO_REPORTING_API.Get_Predicate('rasu', null);
192 l_org_where_aba := XLA_MO_REPORTING_API.Get_Predicate('aba', null);
193 l_org_where_raba := XLA_MO_REPORTING_API.Get_Predicate('raba', null);
194 l_org_where_rah := XLA_MO_REPORTING_API.Get_Predicate('rah', null);
195 l_org_where_rah1 := XLA_MO_REPORTING_API.Get_Predicate('rah1', null);
196 l_org_where_ps := XLA_MO_REPORTING_API.Get_Predicate('ps', null);
197 l_org_where_rab := XLA_MO_REPORTING_API.Get_Predicate('rab', null);
198 l_org_where_arb := XLA_MO_REPORTING_API.Get_Predicate('arb', null);
199 l_org_where_app := XLA_MO_REPORTING_API.Get_Predicate('app', null);
200 l_org_where_cr := XLA_MO_REPORTING_API.Get_Predicate('cr', null);
201
202
203 -- Get the Login info
204 fnd_profile.get('LOGIN_ID', l_login_id);
205
206 -- Get functional currency
207
208 /* bug 2018415 replace fnd_profile call
209 fnd_profile.get(name => 'GL_SET_OF_BKS_ID',
210 val => l_books_id);
211 */
212
213 -- l_books_id := arp_global.sysparam.set_of_books_id;
214 --Bug 5041260 Setting the Set Of Books based on the reporting level
215 if p_reporting_level = 3000 then
216
217 select set_of_books_id
218 into l_books_id
219 from ar_system_parameters_all
220 where org_id = p_reporting_entity_id;
221
222 elsif p_reporting_level = 1000 then
223 l_books_id := p_reporting_entity_id;
224 end if ;
225
226
227 SELECT currency_code,
228 name
229 INTO l_currency_code,
230 l_sob_name
231 FROM gl_sets_of_books
232 WHERE set_of_books_id = l_books_id;
233
234 -- As of Date cursor
235 l_as_of_date := 'SELECT arl.meaning,
236 rah.trx_date,
237 rah.gl_date,
238 rah.status
239 FROM ar_transaction_history_all rah,
240 ar_lookups arl
241 WHERE arl.lookup_code = rah.status '||
242 l_org_where_rah ||
243 'AND arl.lookup_type = ''TRANSACTION_HISTORY_STATUS'''||
244 'AND rah.transaction_history_id = (SELECT MAX(rah1.transaction_history_id)
245 FROM ar_transaction_history_all rah1
246 WHERE rah1.trx_date <= to_char(:b_status_date) '||
247 l_org_where_rah1 ||
248 'AND rah1.customer_trx_id = :b_trx_id)';
249
250 -- Remittance Batch / Bank Acc Details cursor
251 l_remit_batch := 'SELECT arb.name
252 ,arb.remit_method_code
253 ,arb.with_recourse_flag
254 ,rm.name
255 ,rabb.bank_name remit_bank_name
256 ,rabb.bank_number remit_bank_number
257 ,rabb.bank_branch_name remit_branch_name
258 ,rabb.branch_number remit_branch_number
259 ,cba.bank_account_name remit_bank_acc_name
260 ,cba.bank_account_num remit_bank_acc_number
261 ,cba.bank_account_id remit_bank_acc_id
262 ,rabb.city remit_branch_city
263 ,rabb.state remit_branch_state
264 ,rabb.country remit_branch_country
265 ,rabb.zip remit_branch_postal_code
266 ,rabb.address_line1 remit_branch_address1
267 ,rabb.address_line2 remit_branch_address2
268 ,rabb.address_line3 remit_branch_address3
269 ,cba.check_digits
270 ,cba.currency_code remit_bank_acc_curr
271 ,rma.risk_elimination_days
272 FROM ar_transaction_history_all rah
273 ,ar_batches_all arb
274 ,ar_receipt_methods rm
275 ,ar_receipt_method_accounts rma
276 ,ce_bank_accounts cba
277 ,ce_bank_acct_uses raba
278 ,ce_bank_branches_v rabb
279 WHERE rah.batch_id = arb.batch_id
280 AND arb.receipt_method_id = rm.receipt_method_id(+)
281 AND rm.receipt_method_id = rma.receipt_method_id(+)
282 AND arb.remit_bank_acct_use_id = raba.bank_acct_use_id(+)
283 AND raba.bank_account_id = cba.bank_account_id (+)
284 AND cba.bank_branch_id = rabb.branch_party_id(+) '||
285 l_org_where_rah ||
286 l_org_where_arb ||
287 l_org_where_raba ||
288 'AND rah.transaction_history_id = (SELECT max(rah1.transaction_history_id)
289 FROM ar_transaction_history_all rah1
290 WHERE rah1.trx_date <= to_char(:b_status_date) '||
291 l_org_where_rah1 ||
292 'AND rah1.batch_id IS NOT NULL
293 AND rah1.customer_trx_id = :b_trx_id)';
294
295 -- Open Amounts Cursor
296 l_open_amount := 'SELECT nvl(SUM(app.amount_applied),0)
297 FROM ra_customer_trx_all trx,
298 ar_receivable_applications_all app
299 WHERE trx.customer_trx_id = app.applied_customer_trx_id
300 AND app.applied_customer_trx_id = :b_trx_id '||
301 l_org_where_trx ||
302 l_org_where_app ||
303 'AND app.status = ''APP'''||
304 'AND trunc(app.apply_date) > :b_status_as_of_date';
305
306 -- Unpaid Receipt Reversal Reason Cursor
307 l_receipt_reversal := 'SELECT distinct DECODE(cr.reversal_reason_code, NULL, NULL
308 , initcap(arl.meaning))
309 FROM ar_cash_receipts_all cr,
310 ar_receivable_applications_all app,
311 ar_lookups arl
312 WHERE cr.cash_receipt_id = app.cash_receipt_id '||
313 l_org_where_app ||
314 l_org_where_cr ||
315 'AND cr.reversal_reason_code = arl.lookup_code (+)
316 AND arl.lookup_type (+) = ''CKAJST_REASON'''||
317 'AND app.applied_customer_trx_id = :b_trx_id';
318
319 -- Assigned Amounts Cursor
320 l_assigned_amount := 'SELECT nvl(sum(trl.extended_amount),0),
321 nvl(sum(trl.extended_acctd_amount),0)
322 FROM ra_customer_trx_lines_all trl
323 WHERE trl.customer_trx_id = :b_trx_id '||
324 l_org_where_trl;
325
326
327 /*------------------------------------+
328 | Where Clauses |
329 +------------------------------------*/
330 -- Where clause for statuses
331 IF p_first_status IS NOT NULL THEN
332 IF p_second_status IS NOT NULL THEN
333 IF p_third_status IS NOT NULL THEN
334 l_status_where := 'and rah.status in (:b_first_status, :b_second_status, :b_third_status)';
335 ELSE
336 l_status_where := 'AND rah.status in (:b_first_status, :b_second_status)';
337 END IF;
338 ELSE
339 l_status_where := 'AND rah.status = :b_first_status ';
340 END IF;
341 END IF;
342
343 -- Where clause for excluded status
344 IF p_excluded_status IS NOT NULL THEN
345 l_excluded_status_where := 'AND rah.status <> :b_excluded_status ';
346 END IF;
347
348 -- Where clause for Transaction Type
349 IF p_transaction_type IS NOT NULL THEN
350 l_transaction_type_where := 'AND ctt.name = :b_transaction_type ';
351 END IF;
352
353 -- Where clause for Maturity Dates
354 IF p_maturity_date_from IS NOT NULL THEN
355 IF p_maturity_date_to IS NOT NULL THEN
356 l_maturity_date_where := 'AND trunc(trx.term_due_date) between :b_maturity_date_from AND :b_maturity_date_to ';
357 ELSE
358 l_maturity_date_where := 'AND trunc(trx.term_due_date) >= :b_maturity_date_from ';
359 END IF;
360 ELSIF p_maturity_date_to IS NOT NULL THEN
361 l_maturity_date_where := 'AND trunc(trx.term_due_date) <= :b_maturity_date_to ';
362 END IF;
363
364 -- Where Clause for Drawee Name
365 IF p_drawee_name IS NOT NULL THEN
366 l_drawee_name_where := 'AND party.party_name = :b_drawee_name ';
367 END IF;
368
369 -- Where clause for Drawee Number
370 IF p_drawee_number_from IS NOT NULL THEN
371 IF p_drawee_number_to IS NOT NULL THEN
372 l_drawee_number_where := 'AND cust_acct.account_number between :b_drawee_number_from and :b_drawee_number_to ';
373 ELSE
374 l_drawee_number_where := 'AND cust_acct.account_number >= :b_drawee_number_from ';
375 END IF;
376 ELSIF p_drawee_number_to IS NOT NULL THEN
377 l_drawee_number_where := 'AND cust_acct.account_number <= :b_drawee_number_to ';
378 END IF;
379
380 -- Where clause for Drawee Bank
381 IF p_drawee_bank_name IS NOT NULL THEN
382 l_drawee_bank_name_where := 'AND abb.bank_name = :b_drawee_bank_name ';
383 END IF;
384
385 -- Where clause for Transaction Issue Dates
386 IF p_transaction_issue_date_from IS NOT NULL THEN
387 IF p_transaction_issue_date_to IS NOT NULL THEN
388 l_issue_date_where := 'AND trunc(trx.trx_date) between :b_issue_date_from and :b_issue_date_to ';
389 ELSE
390 l_issue_date_where := 'AND trunc(trx.trx_date) >= :b_issue_date_from ';
391 END IF;
392 ELSIF p_transaction_issue_date_to IS NOT NULL THEN
393 l_issue_date_where := 'AND trunc(trx.trx_date) <= :b_issue_date_to ';
394 END IF;
395
396 -- Where clause for BR On Hold Flag
397 IF p_on_hold IS NOT NULL THEN
398 l_on_hold_where := 'AND trx.br_on_hold_flag = :b_on_hold ';
399 END IF;
400
401
402 /*------------------------------------------------------------------+
403 | Parse the main cursor |
404 +------------------------------------------------------------------*/
405 -- Open the cursor for dynamic processing.
406 v_CursorID_main := DBMS_SQL.OPEN_CURSOR;
407
408 -- Parse the main query.
409 DBMS_SQL.PARSE(v_CursorID_main,
410 'SELECT trx.customer_trx_id
411 ,trx.trx_number transaction_number
412 ,trx.doc_sequence_value document_number
413 ,fds.name document_sequence_name
414 ,trx.invoice_currency_code currency_code
415 ,ctt.magnetic_format_code
416 ,nvl(ps.amount_due_original,0) original_entered_amount
417 ,nvl(ps.amount_due_remaining,0) open_entered_amount
418 ,nvl(ps.acctd_amount_due_remaining,0) open_functional_amount
419 ,substrb(party.party_name,1,50) drawee_name
420 ,cust_acct.account_number drawee_number
421 ,party.jgzz_fiscal_code drawee_taxpayer_id
422 ,rasu.tax_reference drawee_vat_reg_number
423 ,loc.city drawee_city
424 ,loc.state drawee_state
425 ,loc.country drawee_country
426 ,loc.postal_code drawee_postal_code
427 ,arl_class.meaning drawee_class
428 ,arl_category.meaning drawee_category
429 ,rasu.location drawee_location
430 ,trx.trx_date issue_date
431 ,trx.term_due_date maturity_date
432 ,ctt.drawee_issued_flag issued_by_drawee
433 ,ctt.signed_flag signed_by_drawee
434 ,ctt.name transaction_type
435 ,rabb.bank_name remit_bank_name
436 ,rabb.bank_number remit_bank_number
437 ,rabb.bank_branch_name remit_branch_name
438 ,rabb.branch_number remit_branch_number
439 ,cba.bank_account_name remit_bank_acc_name
440 ,cba.bank_account_num remit_bank_acc_number
441 ,cba.bank_account_id remit_bank_acc_id
442 ,rabb.city remit_branch_city
443 ,rabb.state remit_branch_state
444 ,rabb.country remit_branch_country
445 ,rabb.zip remit_branch_postal_code
446 ,rabb.address_line1 remit_branch_address1
447 ,rabb.address_line2 remit_branch_address2
448 ,rabb.address_line3 remit_branch_address3
449 ,trx.override_remit_account_flag remit_bank_allow_override
450 ,cba.check_digits remit_bank_acc_check_digits
451 ,cba.currency_code remit_bank_acc_curr
452 ,abb.bank_name drawee_bank_name
453 ,abb.bank_number drawee_bank_number
454 ,abb.bank_branch_name drawee_branch_name
455 ,abb.branch_number drawee_branch_number
456 ,aba.bank_account_name drawee_bank_acc_name
457 ,aba.bank_account_num drawee_bank_acc_number
458 ,abb.city drawee_branch_city
459 ,abb.state drawee_branch_state
460 ,abb.country drawee_branch_country
461 ,abb.zip drawee_branch_postal_code
462 ,abb.address_line1 drawee_branch_address1
463 ,abb.address_line2 drawee_branch_address2
464 ,abb.address_line3 drawee_branch_address3
465 ,aba.check_digits drawee_bank_acc_check_digits
466 ,aba.currency_code drawee_bank_acc_curr
467 ,trx.comments
468 ,decode(ps.amount_due_remaining, 0 , to_number(null)
469 , trunc(sysdate) - ps.due_date) days_late
470 ,trx.printing_last_printed last_printed_date
471 ,loc.address1 drawee_address1
472 ,loc.address2 drawee_address2
473 ,loc.address3 drawee_address3
474 ,substrb(party.person_first_name,1,40) ||'' ''||substrb(party.person_last_name,1,50) drawee_contact
475 ,trx.special_instructions
476 ,rah.status status_code
477 ,rab.name creation_batch_name
478 ,rabs.name transaction_batch_source
479 ,nvl(ps.exchange_rate,1)
480 FROM ra_cust_trx_types_all ctt
481 ,ra_customer_trx_all trx
482 ,hz_cust_acct_sites raa
483 ,hz_party_sites party_site
484 ,hz_locations loc
485 ,hz_cust_account_roles acct_role
486 ,hz_parties rel_party
487 ,hz_relationships rel
488 ,hz_cust_site_uses_all rasu
489 ,ap_bank_accounts_all aba
490 ,ce_bank_branches_v abb
491 ,hz_cust_accounts cust_acct
492 ,hz_parties party
493 ,ce_bank_branches_v rabb
494 ,ce_bank_accounts cba
495 ,ce_bank_acct_uses raba
496 ,ar_lookups arl
497 ,ar_lookups arl_class
498 ,ar_lookups arl_category
499 ,ar_transaction_history_all rah
500 ,fnd_document_sequences fds
501 ,ar_payment_schedules_all ps
502 ,ra_batches_all rab
503 ,ra_batch_sources_all rabs
504 WHERE trx.cust_trx_type_id = ctt.cust_trx_type_id
505 AND trx.batch_source_id = rabs.batch_source_id
506 AND trx.drawee_site_use_id = rasu.site_use_id (+)
507 AND rasu.cust_acct_site_id = raa.cust_acct_site_id (+)
508 AND raa.party_site_id = party_site.party_site_id (+)
509 AND loc.location_id(+) = party_site.location_id
510 AND trx.drawee_bank_account_id = aba.bank_account_id (+)
511 AND aba.bank_branch_id = abb.branch_party_id (+)
512 AND trx.drawee_id = cust_acct.cust_account_id
513 AND cust_acct.party_id = party.party_id
514 AND trx.remit_bank_acct_use_id = raba.bank_acct_use_id (+)
515 AND raba.bank_account_id = cba.bank_account_id (+)
516 AND cba.bank_branch_id = rabb.branch_party_id (+)
517 AND trx.drawee_contact_id = acct_role.cust_account_role_id (+)
518 AND acct_role.party_id = rel.party_id (+)
519 AND rel.subject_id = rel_party.party_id(+)
520 AND rel.subject_table_name(+) = ''HZ_PARTIES'''||
521 'AND rel.object_table_name(+) = ''HZ_PARTIES'''||
522 'AND rel.directional_flag(+) = ''F'''||
523 'AND rah.customer_trx_id = trx.customer_trx_id
524 AND rah.current_record_flag = ''Y'''||
525 'AND arl.lookup_code = rah.status
526 AND arl.lookup_type = ''TRANSACTION_HISTORY_STATUS'''||
527 'AND trx.doc_sequence_id = fds.doc_sequence_id(+)
528 AND cust_acct.customer_class_code = arl_class.lookup_code(+)
529 AND arl_class.lookup_type(+) = ''CUSTOMER_CLASS'''||
530 'AND party.category_code = arl_category.lookup_code(+)
531 AND arl_category.lookup_type(+) = ''CUSTOMER_CATEGORY'''||
532 l_org_where_trx ||
533 l_org_where_rabs ||
534 l_org_where_ctt ||
535 l_org_where_raa ||
536 l_org_where_raa ||
537 l_org_where_rasu ||
538 l_org_where_aba ||
539 l_org_where_raba ||
540 l_org_where_rah ||
541 l_org_where_ps ||
542 l_org_where_rab||
543 l_status_where ||
544 l_excluded_status_where ||
545 l_transaction_type_where ||
546 l_maturity_date_where ||
547 l_drawee_name_where ||
548 l_drawee_number_where ||
549 l_drawee_bank_name_where ||
550 l_issue_date_where ||
551 l_on_hold_where ||
552 'AND trx.customer_trx_id = ps.customer_trx_id(+)
553 AND trx.batch_id = rab.batch_id(+)
554 AND rah.status <> ''INCOMPLETE''',
555 DBMS_SQL.native);
556
557
558
559 /*------------------------------------------------------------------+
560 | Bind variables for main cursor |
561 +------------------------------------------------------------------*/
562
563 -- If the MO Reporting Get Predicate function returns a bind variable then
564 -- we need to bind it.
565 IF l_org_where_trx like '%:p_reporting_entity_id%' THEN
566 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':p_reporting_entity_id', p_reporting_entity_id);
567 END IF;
568
569 IF p_first_status IS NOT NULL THEN
570 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_first_status', p_first_status);
571 IF p_second_status IS NOT NULL THEN
572 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_second_status', p_second_status);
573 IF p_third_status IS NOT NULL THEN
574 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_third_status', p_third_status);
575 END IF;
576 END IF;
577 END IF;
578
579 IF p_excluded_status IS NOT NULL THEN
580 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_excluded_status', p_excluded_status);
581 END IF;
582
583 IF p_transaction_type IS NOT NULL THEN
584 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_transaction_type', p_transaction_type);
585 END IF;
586
587 IF p_maturity_date_from IS NOT NULL THEN
588 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_maturity_date_from', p_maturity_date_from);
589 END IF;
590
591 IF p_maturity_date_to IS NOT NULL THEN
592 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_maturity_date_to', p_maturity_date_to);
593 END IF;
594
595 IF p_drawee_name IS NOT NULL THEN
596 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_drawee_name', p_drawee_name);
597 END IF;
598
599 IF p_drawee_number_from IS NOT NULL THEN
600 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_drawee_number_from', p_drawee_number_from);
601 END IF;
602
603 IF p_drawee_number_to IS NOT NULL THEN
604 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_drawee_number_to', p_drawee_number_to);
605 END IF;
606
607 IF p_drawee_bank_name IS NOT NULL THEN
608 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_drawee_bank_name', p_drawee_bank_name);
609 END IF;
610
611 IF p_transaction_issue_date_from IS NOT NULL THEN
612 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_issue_date_from', p_transaction_issue_date_from);
613 END IF;
614
615 IF p_transaction_issue_date_to IS NOT NULL THEN
616 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_issue_date_to', p_transaction_issue_date_to);
617 END IF;
618
619 IF p_on_hold IS NOT NULL THEN
620 DBMS_SQL.BIND_VARIABLE(v_CursorID_main, ':b_on_hold', p_on_hold);
621 END IF;
622
623
624 /*------------------------------------------------------------------+
625 | Define the output variables |
626 +------------------------------------------------------------------*/
627
628 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 1, v_customer_trx_id);
629 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 2, v_transaction_number, 20);
630 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 3, v_document_number);
631 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 4, v_document_sequence_name, 30);
632 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 5, v_currency_code, 15);
633 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 6, v_magnetic_format_code, 30);
634 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 7, v_original_entered_amount);
635 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 8, v_open_entered_amount);
636 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 9, v_open_functional_amount);
637 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 10, v_drawee_name, 255);
638 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 11, v_drawee_number, 30);
639 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 12, v_drawee_taxpayer_id, 20);
640 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 13, v_drawee_vat_reg_number, 50);
641 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 14, v_drawee_city, 60);
642 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 15, v_drawee_state, 60);
643 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 16, v_drawee_country, 60);
644 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 17, v_drawee_postal_code, 60);
645 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 18, v_drawee_class, 80);
646 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 19, v_drawee_category, 80);
647 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 20, v_drawee_location, 40);
648 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 21, v_issue_date);
649 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 22, v_maturity_date);
650 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 23, v_issued_by_drawee, 1);
651 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 24, v_signed_by_drawee, 1);
652 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 25, v_transaction_type, 20);
653 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 26, v_remit_bank_name, 60);
654 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 27, v_remit_bank_number, 30);
655 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 28, v_remit_branch_name, 60);
656 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 29, v_remit_branch_number, 25);
657 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 30, v_remit_bank_acc_name, 80);
658 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 31, v_remit_bank_acc_number, 30);
659 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 32, v_remit_bank_acc_id);
660 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 33, v_remit_branch_city, 25);
661 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 34, v_remit_branch_state, 25);
662 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 35, v_remit_branch_country, 25);
663 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 36, v_remit_branch_postal_code, 20);
664 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 37, v_remit_branch_address1, 35);
665 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 38, v_remit_branch_address2, 35);
666 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 39, v_remit_branch_address3, 35);
667 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 40, v_remit_bank_allow_override, 1);
668 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 41, v_remit_bank_acc_check_digits, 30);
669 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 42, v_remit_bank_acc_curr, 15);
670 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 43, v_drawee_bank_name, 60);
671 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 44, v_drawee_bank_number, 30);
672 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 45, v_drawee_branch_name, 60);
673 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 46, v_drawee_branch_number, 25);
674 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 47, v_drawee_bank_acc_name, 80);
675 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 48, v_drawee_bank_acc_number, 30);
676 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 49, v_drawee_branch_city, 25);
677 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 50, v_drawee_branch_state, 25);
678 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 51, v_drawee_branch_country, 25);
679 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 52, v_drawee_branch_postal_code, 20);
680 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 53, v_drawee_branch_address1, 35);
681 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 54, v_drawee_branch_address2, 35);
682 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 55, v_drawee_branch_address3, 35);
683 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 56, v_drawee_bank_acc_check_digits, 30);
684 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 57, v_drawee_bank_acc_curr, 15);
685 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 58, v_comments, 240);
686 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 59, v_days_late);
687 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 60, v_last_printed_date);
688 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 61, v_drawee_address1, 240);
689 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 62, v_drawee_address2, 240);
690 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 63, v_drawee_address3, 240);
691 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 64, v_drawee_contact, 100);
692 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 65, v_special_instructions, 240);
693 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 66, v_status_code, 30);
694 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 67, v_creation_batch_name, 50);
695 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 68, v_transaction_batch_source, 50);
696 DBMS_SQL.DEFINE_COLUMN(v_CursorID_main, 69, v_ps_exchange_rate);
697
698
699 -- Execute the statement. We're not concerned about the return
700 -- value, but we do need to declare a variable for it.
701 v_Dummy_main := DBMS_SQL.EXECUTE(v_CursorID_main);
702
703 -- This is the fetch loop.
704 LOOP
705
706 -- Fetch the rows into the buffer, and also check for the exit
707 -- condition from the loop.
708 IF DBMS_SQL.FETCH_ROWS(v_CursorID_main) = 0 THEN
709 EXIT;
710 END IF;
711
712 -- Retrieve the rows from the buffer into PL/SQL variables.
713 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 1, v_customer_trx_id);
714 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 2, v_transaction_number);
715 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 3, v_document_number);
716 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 4, v_document_sequence_name);
717 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 5, v_currency_code);
718 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 6, v_magnetic_format_code);
719 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 7, v_original_entered_amount);
720 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 8, v_open_entered_amount);
721 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 9, v_open_functional_amount);
722 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 10, v_drawee_name);
723 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 11, v_drawee_number);
724 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 12, v_drawee_taxpayer_id);
725 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 13, v_drawee_vat_reg_number);
726 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 14, v_drawee_city);
727 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 15, v_drawee_state);
728 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 16, v_drawee_country);
729 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 17, v_drawee_postal_code);
730 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 18, v_drawee_class);
731 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 19, v_drawee_category);
732 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 20, v_drawee_location);
733 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 21, v_issue_date);
734 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 22, v_maturity_date);
735 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 23, v_issued_by_drawee);
736 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 24, v_signed_by_drawee);
737 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 25, v_transaction_type);
738 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 26, v_remit_bank_name);
739 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 27, v_remit_bank_number);
740 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 28, v_remit_branch_name);
741 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 29, v_remit_branch_number);
742 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 30, v_remit_bank_acc_name);
743 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 31, v_remit_bank_acc_number);
744 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 32, v_remit_bank_acc_id);
745 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 33, v_remit_branch_city);
746 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 34, v_remit_branch_state);
747 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 35, v_remit_branch_country);
748 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 36, v_remit_branch_postal_code);
749 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 37, v_remit_branch_address1);
750 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 38, v_remit_branch_address2);
751 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 39, v_remit_branch_address3);
752 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 40, v_remit_bank_allow_override);
753 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 41, v_remit_bank_acc_check_digits);
754 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 42, v_remit_bank_acc_curr);
755 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 43, v_drawee_bank_name);
756 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 44, v_drawee_bank_number);
757 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 45, v_drawee_branch_name);
758 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 46, v_drawee_branch_number);
759 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 47, v_drawee_bank_acc_name);
760 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 48, v_drawee_bank_acc_number);
761 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 49, v_drawee_branch_city);
762 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 50, v_drawee_branch_state);
763 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 51, v_drawee_branch_country);
764 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 52, v_drawee_branch_postal_code);
765 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 53, v_drawee_branch_address1);
766 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 54, v_drawee_branch_address2);
767 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 55, v_drawee_branch_address3);
768 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 56, v_drawee_bank_acc_check_digits);
769 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 57, v_drawee_bank_acc_curr);
770 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 58, v_comments);
771 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 59, v_days_late);
772 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 60, v_last_printed_date);
773 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 61, v_drawee_address1);
774 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 62, v_drawee_address2);
775 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 63, v_drawee_address3);
776 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 64, v_drawee_contact);
777 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 65, v_special_instructions);
778 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 66, v_status_code);
779 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 67, v_creation_batch_name);
780 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 68, v_transaction_batch_source);
781 DBMS_SQL.COLUMN_VALUE(v_CursorID_main, 69, v_ps_exchange_rate);
782
783 -- Calculate correct Orignal Entered Funcional Amount
784 --Bug 5041260 replaced the call arp_util.calc_acctd_amount with
785 -- arp_util.calc_accounted_amount.
786 arp_util.calc_accounted_amount(l_currency_code,
787 NULL,
788 NULL,
789 v_ps_exchange_rate,
790 '-',
791 v_open_entered_amount,
792 v_open_functional_amount,
793 v_original_entered_amount,
794 l_new_ADR,
795 l_new_acctd_ADR,
796 l_new_original_funct_amt);
797
798 -- Restrict by original amount parameters if populated
799 IF p_original_amount_from IS NOT NULL THEN
800 IF p_original_amount_to IS NOT NULL THEN
801 IF l_new_original_funct_amt BETWEEN p_original_amount_from and p_original_amount_to THEN
802 l_populate_amt := 'Y';
803 ELSE
804 l_populate_amt := 'N';
805 END IF;
806 ELSE
807 IF l_new_original_funct_amt >= p_original_amount_from THEN
808 l_populate_amt := 'Y';
809 ELSE
810 l_populate_amt := 'N';
811 END IF;
812 END IF;
813 ELSIF p_original_amount_to IS NOT NULL THEN
814 IF l_new_original_funct_amt <= p_original_amount_to THEN
815 l_populate_amt := 'Y';
816 ELSE
817 l_populate_amt := 'N';
818 END IF;
819 END IF;
820
821 IF l_populate_amt = 'Y' THEN
822
823 /*------------------------------------------------------------------+
824 | Remittance Batch Information |
825 +------------------------------------------------------------------*/
826
827 -- Remittance Batch ID will be taken from the Pending Remittance status
828 -- since this is the first stage where it will be populated.
829 -- Moved to after as of date processing so that remittance batch
830 -- information is also date specific.
831 -- Remittance Bank Info is also selected and will update previous info.
832
833 BEGIN
834
835 -- Initialize parameters
836 v_remittance_batch_name :=null;
837 v_remittance_method :=null;
838 v_with_recourse :=null;
839 v_remittance_payment_method :=null;
840 v_risk_elimination_days := null;
841 l_populate := 'Y';
842
843 -- Open the As of Date cursor for dynamic processing.
844 v_CursorID_rbatch := DBMS_SQL.OPEN_CURSOR;
845
846 -- Parse the As of Date Cursor
847 DBMS_SQL.PARSE(v_CursorID_rbatch, l_remit_batch, DBMS_SQL.native);
848
849 -- Bind variables for cursor.
850 DBMS_SQL.BIND_VARIABLE(v_CursorID_rbatch, ':b_status_date', p_status_as_of_date);
851 DBMS_SQL.BIND_VARIABLE(v_CursorID_rbatch, ':b_trx_id', v_customer_trx_id);
852
853 -- If the MO Reporting Get Predicate function returns a bind variable then
854 -- we need to bind it.
855 IF l_org_where_trx like '%:p_reporting_entity_id%' THEN
856 DBMS_SQL.BIND_VARIABLE(v_CursorID_rbatch, ':p_reporting_entity_id', p_reporting_entity_id);
857 END IF;
858
859 -- Define the output variables.
860 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 1, v_remittance_batch_name, 20);
861 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 2, v_remittance_method, 30);
862 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 3, v_with_recourse, 1);
863 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 4, v_remittance_payment_method, 30);
864 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 5, v_remit_bank_name, 60);
865 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 6, v_remit_bank_number, 30);
866 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 7, v_remit_branch_name, 60);
867 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 8, v_remit_branch_number, 25);
868 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 9, v_remit_bank_acc_name, 80);
869 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 10, v_remit_bank_acc_number, 30);
870 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 11, v_remit_bank_acc_id);
871 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 12, v_remit_branch_city, 25);
872 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 13, v_remit_branch_state, 25);
873 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 14, v_remit_branch_country, 25);
874 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 15, v_remit_branch_postal_code, 20);
875 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 16, v_remit_branch_address1, 35);
876 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 17, v_remit_branch_address2, 35);
877 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 18, v_remit_branch_address3, 35);
878 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 19, v_remit_bank_acc_check_digits, 30);
879 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 20, v_remit_bank_acc_curr, 15);
880 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rbatch, 21, v_risk_elimination_days);
881
882 -- Execute the statement.
883 v_Dummy_rbatch := DBMS_SQL.EXECUTE(v_CursorID_rbatch);
884
885 IF DBMS_SQL.FETCH_ROWS(v_CursorID_rbatch) > 0 THEN
886 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 1, v_remittance_batch_name);
887 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 2, v_remittance_method);
888 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 3, v_with_recourse);
889 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 4, v_remittance_payment_method);
890 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 5, v_remit_bank_name);
891 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 6, v_remit_bank_number);
892 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 7, v_remit_branch_name);
893 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 8, v_remit_branch_number);
894 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 9, v_remit_bank_acc_name);
895 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 10, v_remit_bank_acc_number);
896 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 11, v_remit_bank_acc_id);
897 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 12, v_remit_branch_city);
898 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 13, v_remit_branch_state);
899 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 14, v_remit_branch_country);
900 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 15, v_remit_branch_postal_code);
901 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 16, v_remit_branch_address1);
902 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 17, v_remit_branch_address2);
903 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 18, v_remit_branch_address3);
904 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 19, v_remit_bank_acc_check_digits);
905 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 20, v_remit_bank_acc_curr);
906 DBMS_SQL.COLUMN_VALUE(v_CursorID_rbatch, 21, v_risk_elimination_days);
907
908
909 END IF;
910
911 IF DBMS_SQL.IS_OPEN (v_CursorID_rbatch) THEN
912 DBMS_SQL.CLOSE_CURSOR(v_CursorID_rbatch);
913 END IF;
914
915 -- Remitance Batch Name Restriction
916 IF p_remittance_batch_name IS NOT NULL THEN
917 IF p_remittance_batch_name <> v_remittance_batch_name
918 OR v_remittance_batch_name IS NULL THEN
919 l_populate := 'N';
920 END IF;
921 END IF;
922
923 -- Remittance Bank Acc Restriction
924 IF p_remittance_bank_account IS NOT NULL THEN
925 IF p_remittance_bank_account <> v_remit_bank_acc_name
926 OR v_remit_bank_acc_name IS NULL THEN
927 l_populate := 'N';
928 END IF;
929 END IF;
930
931 EXCEPTION
932 WHEN OTHERS THEN
933 -- Close both cursors, then raise the error again.
934 DBMS_SQL.CLOSE_CURSOR(v_CursorID_rbatch);
935 DBMS_SQL.CLOSE_CURSOR(v_CursorID_main);
936 RAISE;
937
938 END;
939
940 IF l_populate = 'Y' THEN
941
942 /*------------------------------------------------------------------+
943 | Determine the statuses from the as of date |
944 +------------------------------------------------------------------*/
945
946 BEGIN
947
948 -- Initialize parameters
949 v_status := null;
950 v_status_date := null;
951 v_status_gl_date := null;
952
953 -- Open the As of Date cursor for dynamic processing.
954 v_CursorID_date := DBMS_SQL.OPEN_CURSOR;
955
956 -- Parse the As of Date Cursor
957 DBMS_SQL.PARSE(v_CursorID_date, l_as_of_date, DBMS_SQL.native);
958
959 -- Bind variables for cursor
960 DBMS_SQL.BIND_VARIABLE(v_CursorID_date, ':b_status_date', p_status_as_of_date);
961 DBMS_SQL.BIND_VARIABLE(v_CursorID_date, ':b_trx_id', v_customer_trx_id);
962 -- If the MO Reporting Get Predicate function returns a bind variable then
963 -- we need to bind it.
964 IF l_org_where_trx like '%:p_reporting_entity_id%' THEN
965 DBMS_SQL.BIND_VARIABLE(v_CursorID_date, ':p_reporting_entity_id', p_reporting_entity_id);
966 END IF;
967
968 -- Define the output variables.
969 DBMS_SQL.DEFINE_COLUMN(v_CursorID_date, 1, v_status, 30);
970 DBMS_SQL.DEFINE_COLUMN(v_CursorID_date, 2, v_status_date);
971 DBMS_SQL.DEFINE_COLUMN(v_CursorID_date, 3, v_status_gl_date);
972 DBMS_SQL.DEFINE_COLUMN(v_CursorID_date, 4, v_status_code, 30);
973
974 -- Execute the statement.
975 v_Dummy_date := DBMS_SQL.EXECUTE(v_CursorID_date);
976
977 IF DBMS_SQL.FETCH_ROWS(v_CursorID_date) > 0 THEN
978 DBMS_SQL.COLUMN_VALUE(v_CursorID_date, 1, v_status);
979 DBMS_SQL.COLUMN_VALUE(v_CursorID_date, 2, v_status_date);
980 DBMS_SQL.COLUMN_VALUE(v_CursorID_date, 3, v_status_gl_date);
981 DBMS_SQL.COLUMN_VALUE(v_CursorID_date, 4, v_status_code);
982 END IF;
983
984 IF DBMS_SQL.IS_OPEN (v_CursorID_date) THEN
985 DBMS_SQL.CLOSE_CURSOR(v_CursorID_date);
986 END IF;
987
988 EXCEPTION
989 WHEN OTHERS THEN
990 -- Close both cursors, then raise the error again.
991 DBMS_SQL.CLOSE_CURSOR(v_CursorID_date);
992 DBMS_SQL.CLOSE_CURSOR(v_CursorID_main);
993 RAISE;
994
995 END;
996
997 -- If the record does not have a valid status for the data we do not want
998 -- to do any further processing of the record.
999 IF v_status IS NOT NULL THEN
1000
1001 /*------------------------------------------------------------------+
1002 | Assigned Amounts |
1003 +------------------------------------------------------------------*/
1004
1005 BEGIN
1006
1007 -- Initialize variables
1008 v_assigned_entered_amount := 0;
1009 v_assigned_functional_amount := 0;
1010
1011 -- Open the Assigned Amounts cursor for dynamic processing.
1012 v_CursorID_asg := DBMS_SQL.OPEN_CURSOR;
1013
1014 -- Parse the Assigned Amounts Cursor
1015 DBMS_SQL.PARSE(v_CursorID_asg, l_assigned_amount, DBMS_SQL.native);
1016
1017 -- Bind variables for cursor
1018 DBMS_SQL.BIND_VARIABLE(v_CursorID_asg, ':b_trx_id', v_customer_trx_id);
1019 -- If the MO Reporting Get Predicate function returns a bind variable then
1020 -- we need to bind it.
1021
1022 IF l_org_where_trx like '%:p_reporting_entity_id%' THEN
1023 DBMS_SQL.BIND_VARIABLE(v_CursorID_asg, ':p_reporting_entity_id', p_reporting_entity_id);
1024 END IF;
1025
1026 -- Define the output variables.
1027 DBMS_SQL.DEFINE_COLUMN(v_CursorID_asg, 1, v_assigned_entered_amount);
1028 DBMS_SQL.DEFINE_COLUMN(v_CursorID_asg, 2, v_assigned_functional_amount);
1029
1030 -- Execute the statement.
1031 v_Dummy_asg := DBMS_SQL.EXECUTE(v_CursorID_asg);
1032
1033 IF DBMS_SQL.FETCH_ROWS(v_CursorID_asg) > 0 THEN
1034 DBMS_SQL.COLUMN_VALUE(v_CursorID_asg, 1, v_assigned_entered_amount);
1035 DBMS_SQL.COLUMN_VALUE(v_CursorID_asg, 2, v_assigned_functional_amount);
1036 END IF;
1037
1038 IF DBMS_SQL.IS_OPEN (v_CursorID_asg) THEN
1039 DBMS_SQL.CLOSE_CURSOR(v_CursorID_asg);
1040 END IF;
1041
1042 EXCEPTION
1043 WHEN OTHERS THEN
1044 -- Close both cursors, then raise the error again.
1045 DBMS_SQL.CLOSE_CURSOR(v_CursorID_asg);
1046 DBMS_SQL.CLOSE_CURSOR(v_CursorID_main);
1047 RAISE;
1048
1049 END;
1050
1051 /*------------------------------------------------------------------+
1052 | Calculate the Open Amounts from the as of date |
1053 +------------------------------------------------------------------*/
1054
1055 BEGIN
1056
1057 -- Initialize variables
1058 v_amount_applied := null;
1059 v_functional_amount_applied := null;
1060
1061 -- Open the Open Amt cursor for dynamic processing.
1062 v_CursorID_amt := DBMS_SQL.OPEN_CURSOR;
1063
1064 -- Parse the Open Amt Cursor
1065 DBMS_SQL.PARSE(v_CursorID_amt, l_open_amount, DBMS_SQL.native);
1066
1067 -- Bind variables for cursor
1068 DBMS_SQL.BIND_VARIABLE(v_CursorID_amt, ':b_status_as_of_date', p_status_as_of_date);
1069 DBMS_SQL.BIND_VARIABLE(v_CursorID_amt, ':b_trx_id', v_customer_trx_id);
1070 -- If the MO Reporting Get Predicate function returns a bind variable then
1071 -- we need to bind it.
1072 IF l_org_where_trx like '%:p_reporting_entity_id%' THEN
1073 DBMS_SQL.BIND_VARIABLE(v_CursorID_amt, ':p_reporting_entity_id', p_reporting_entity_id);
1074 END IF;
1075
1076 -- Define the output variables.
1077 DBMS_SQL.DEFINE_COLUMN(v_CursorID_amt, 1, v_amount_applied);
1078
1079 -- Execute the statement.
1080 v_Dummy_amt := DBMS_SQL.EXECUTE(v_CursorID_amt);
1081
1082 IF DBMS_SQL.FETCH_ROWS(v_CursorID_amt) > 0 THEN
1083 DBMS_SQL.COLUMN_VALUE(v_CursorID_amt, 1, v_amount_applied);
1084 END IF;
1085
1086 IF DBMS_SQL.IS_OPEN (v_CursorID_amt) THEN
1087 DBMS_SQL.CLOSE_CURSOR(v_CursorID_amt);
1088 END IF;
1089
1090 IF v_amount_applied IS NOT NULL THEN
1091 v_open_entered_amount := v_amount_applied + v_open_entered_amount;
1092
1093 -- Ensure we calculate Functional Acctd Amount correctly
1094 --Bug 5041260 replaced the call arp_util.calc_acctd_amount with
1095 -- arp_util.calc_accounted_amount.
1096
1097 arp_util.calc_accounted_amount(l_currency_code,
1098 NULL,
1099 NULL,
1100 v_ps_exchange_rate,
1101 '-',
1102 v_open_entered_amount,
1103 v_open_functional_amount,
1104 v_amount_applied,
1105 l_new_ADR,
1106 l_new_acctd_ADR,
1107 v_functional_amount_applied);
1108
1109 v_open_functional_amount := v_functional_amount_applied + v_open_functional_amount;
1110
1111 END IF;
1112
1113 EXCEPTION
1114 WHEN OTHERS THEN
1115 -- Close both cursors, then raise the error again.
1116 DBMS_SQL.CLOSE_CURSOR(v_CursorID_amt);
1117 DBMS_SQL.CLOSE_CURSOR(v_CursorID_main);
1118 RAISE;
1119
1120 END;
1121
1122 /*------------------------------------------------------------------+
1123 | Unpaid Receipt Reversal Reason |
1124 +------------------------------------------------------------------*/
1125
1126 IF v_status_code = 'UNPAID' THEN
1127
1128 BEGIN
1129
1130 -- Initialize variables
1131 v_unpaid_receipt_rev_reason := null;
1132
1133 -- Open the Unpaide Receipt Reversal Reason cursor for dynamic processing.
1134 v_CursorID_rev := DBMS_SQL.OPEN_CURSOR;
1135
1136 -- Parse the Open Amt Cursor
1137 DBMS_SQL.PARSE(v_CursorID_rev, l_receipt_reversal, DBMS_SQL.native);
1138
1139 -- Bind variables for cursor
1140 DBMS_SQL.BIND_VARIABLE(v_CursorID_rev, ':b_trx_id', v_customer_trx_id);
1141 -- If the MO Reporting Get Predicate function returns a bind variable then
1142 -- we need to bind it.
1143 IF l_org_where_trx like '%:p_reporting_entity_id%' THEN
1144 DBMS_SQL.BIND_VARIABLE(v_CursorID_rev, ':p_reporting_entity_id', p_reporting_entity_id);
1145 END IF;
1146
1147 -- Define the output variables.
1148 DBMS_SQL.DEFINE_COLUMN(v_CursorID_rev, 1, v_unpaid_receipt_rev_reason, 80);
1149
1150 -- Execute the statement.
1151 v_Dummy_amt := DBMS_SQL.EXECUTE(v_CursorID_rev);
1152
1153 IF DBMS_SQL.FETCH_ROWS(v_CursorID_rev) > 0 THEN
1154 DBMS_SQL.COLUMN_VALUE(v_CursorID_rev, 1, v_unpaid_receipt_rev_reason);
1155 END IF;
1156
1157 IF DBMS_SQL.IS_OPEN (v_CursorID_rev) THEN
1158 DBMS_SQL.CLOSE_CURSOR(v_CursorID_rev);
1159 END IF;
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 -- Close both cursors, then raise the error again.
1164 DBMS_SQL.CLOSE_CURSOR(v_CursorID_rev);
1165 DBMS_SQL.CLOSE_CURSOR(v_CursorID_main);
1166 RAISE;
1167
1168 END;
1169
1170 END IF;
1171
1172 /*------------------------------------------------------------------+
1173 | Populate Dates |
1174 +------------------------------------------------------------------*/
1175
1176 SELECT MIN(trx_date)
1177 INTO l_creation_gl_date
1178 FROM ar_transaction_history_all
1179 WHERE event = 'COMPLETED'
1180 AND customer_trx_id = v_customer_trx_id;
1181
1182 SELECT MIN(maturity_date)
1183 INTO l_original_maturity_date
1184 FROM ar_transaction_history_all
1185 WHERE customer_trx_id = v_customer_trx_id
1186 AND status IN ('PENDING_REMITTANCE', 'PENDING_ACCEPTANCE')
1187 AND event = 'COMPLETED';
1188
1189 SELECT MAX(trx_date)
1190 INTO l_unpaid_date
1191 FROM ar_transaction_history_all
1192 WHERE status = 'UNPAID'
1193 AND customer_trx_id = v_customer_trx_id;
1194
1195 SELECT MAX(trx_date)
1196 INTO l_acceptance_date
1197 FROM ar_transaction_history_all
1198 WHERE event = 'ACCEPTED'
1199 AND customer_trx_id = v_customer_trx_id;
1200
1201 SELECT MAX(trx_date)
1202 INTO l_remit_date
1203 FROM ar_transaction_history_all
1204 WHERE batch_id IS NOT NULL
1205 AND customer_trx_id = v_customer_trx_id;
1206
1207
1208 /*------------------------------------------------------------------+
1209 | Insert Data into Interface Table |
1210 +------------------------------------------------------------------*/
1211
1212 INSERT INTO ar_br_status_rep_itf
1213 (creation_date
1214 ,created_by
1215 ,last_update_login
1216 ,last_update_date
1217 ,last_updated_by
1218 ,request_id
1219 ,status
1220 ,status_date
1221 ,transaction_number
1222 ,document_number
1223 ,document_sequence_name
1224 ,currency
1225 ,magnetic_format_code
1226 ,entered_amount
1227 ,functional_amount
1228 ,balance_due
1229 ,functional_balance_due
1230 ,drawee_name
1231 ,drawee_number
1232 ,jgzz_fiscal_code
1233 ,drawee_vat_reg_number
1234 ,drawee_city
1235 ,drawee_state
1236 ,drawee_country
1237 ,drawee_postal_code
1238 ,drawee_class
1239 ,drawee_category
1240 ,drawee_location
1241 ,issue_date
1242 ,creation_gl_date
1243 ,status_gl_date
1244 ,maturity_date
1245 ,original_maturity_date
1246 ,issued_by_drawee
1247 ,signed_by_drawee
1248 ,transaction_type
1249 ,transaction_batch_source
1250 ,remit_bank_name
1251 ,remit_bank_number
1252 ,remit_branch_name
1253 ,remit_branch_number
1254 ,remit_bank_acc_name
1255 ,remit_bank_acc_number
1256 ,remit_branch_city
1257 ,remit_branch_state
1258 ,remit_branch_country
1259 ,remit_branch_postal_code
1260 ,remit_branch_address1
1261 ,remit_branch_address2
1262 ,remit_branch_address3
1263 ,remit_bank_allow_override
1264 ,remit_bank_acc_check_digits
1265 ,remit_bank_acc_curr
1266 ,drawee_bank_name
1267 ,drawee_bank_number
1268 ,drawee_branch_name
1269 ,drawee_branch_number
1270 ,drawee_bank_acc_name
1271 ,drawee_bank_acc_number
1272 ,drawee_branch_city
1273 ,drawee_branch_state
1274 ,drawee_branch_country
1275 ,drawee_branch_postal_code
1276 ,drawee_branch_address1
1277 ,drawee_branch_address2
1278 ,drawee_branch_address3
1279 ,drawee_bank_acc_check_digits
1280 ,drawee_bank_acc_curr
1281 ,unpaid_date
1282 ,acceptance_date
1283 ,comments
1284 ,days_late
1285 ,last_printed_date
1286 ,remittance_date
1287 ,drawee_address1
1288 ,drawee_address2
1289 ,drawee_address3
1290 ,drawee_contact
1291 ,special_instructions
1292 ,remittance_batch_name
1293 ,remittance_method
1294 ,with_recourse
1295 ,remittance_payment_method
1296 ,risk_elimination_days
1297 ,creation_batch_name
1298 ,assigned_entered_amount
1299 ,assigned_functional_amount
1300 ,unpaid_receipt_reversal_reason
1301 ,functional_currency_code
1302 ,organization_name
1303 )
1304 VALUES
1305 (sysdate
1306 ,p_user_id
1307 ,l_login_id
1308 ,sysdate
1309 ,p_user_id
1310 ,p_request_id
1311 ,v_status
1312 ,v_status_date
1313 ,v_transaction_number
1314 ,v_document_number
1315 ,v_document_sequence_name
1316 ,v_currency_code
1317 ,v_magnetic_format_code
1318 ,v_original_entered_amount
1319 ,l_new_original_funct_amt
1320 ,v_open_entered_amount
1321 ,v_open_functional_amount
1322 ,v_drawee_name
1323 ,v_drawee_number
1324 ,v_drawee_taxpayer_id
1325 ,v_drawee_vat_reg_number
1326 ,v_drawee_city
1327 ,v_drawee_state
1328 ,v_drawee_country
1329 ,v_drawee_postal_code
1330 ,v_drawee_class
1331 ,v_drawee_category
1332 ,v_drawee_location
1333 ,v_issue_date
1334 ,l_creation_gl_date
1335 ,v_status_gl_date
1336 ,v_maturity_date
1337 ,l_original_maturity_date
1338 ,v_issued_by_drawee
1339 ,v_signed_by_drawee
1340 ,v_transaction_type
1341 ,v_transaction_batch_source
1342 ,v_remit_bank_name
1343 ,v_remit_bank_number
1344 ,v_remit_branch_name
1345 ,v_remit_branch_number
1346 ,v_remit_bank_acc_name
1347 ,v_remit_bank_acc_number
1348 ,v_remit_branch_city
1349 ,v_remit_branch_state
1350 ,v_remit_branch_country
1351 ,v_remit_branch_postal_code
1352 ,v_remit_branch_address1
1353 ,v_remit_branch_address2
1354 ,v_remit_branch_address3
1355 ,v_remit_bank_allow_override
1356 ,v_remit_bank_acc_check_digits
1357 ,v_remit_bank_acc_curr
1358 ,v_drawee_bank_name
1359 ,v_drawee_bank_number
1360 ,v_drawee_branch_name
1361 ,v_drawee_branch_number
1362 ,v_drawee_bank_acc_name
1363 ,v_drawee_bank_acc_number
1364 ,v_drawee_branch_city
1365 ,v_drawee_branch_state
1366 ,v_drawee_branch_country
1367 ,v_drawee_branch_postal_code
1368 ,v_drawee_branch_address1
1369 ,v_drawee_branch_address2
1370 ,v_drawee_branch_address3
1371 ,v_drawee_bank_acc_check_digits
1372 ,v_drawee_bank_acc_curr
1373 ,l_unpaid_date
1374 ,l_acceptance_date
1375 ,v_comments
1376 ,v_days_late
1377 ,v_last_printed_date
1378 ,l_remit_date
1379 ,v_drawee_address1
1380 ,v_drawee_address2
1381 ,v_drawee_address3
1382 ,v_drawee_contact
1383 ,v_special_instructions
1384 ,v_remittance_batch_name
1385 ,v_remittance_method
1386 ,v_with_recourse
1387 ,v_remittance_payment_method
1388 ,v_risk_elimination_days
1389 ,v_creation_batch_name
1390 ,v_assigned_entered_amount
1391 ,v_assigned_functional_amount
1392 ,v_unpaid_receipt_rev_reason
1393 ,l_currency_code
1394 ,l_sob_name
1395 );
1396
1397 -- v_status
1398 END IF;
1399
1400 -- l_populate
1401 END IF;
1402
1403 -- l_populate_amt
1404 END IF;
1405
1406 END LOOP;
1407
1408 -- Close the cursor.
1409 DBMS_SQL.CLOSE_CURSOR(v_CursorID_main);
1410
1411 -- Update status variables to successful completion
1412 retcode := 0;
1413 errbuf := '';
1414
1415 -- Commit our work.
1416 COMMIT;
1417
1418 EXCEPTION
1419 WHEN OTHERS THEN
1420 -- Close the cursor, then raise the error again.
1421 DBMS_SQL.CLOSE_CURSOR(v_CursorID_main);
1422 RAISE;
1423 END arrxbrs_report;
1424
1425 END arrx_brs;