[Home] [Help]
PACKAGE BODY: APPS.ARP_PROGRAM_GENERATE_BR
Source
1 PACKAGE BODY ARP_PROGRAM_GENERATE_BR AS
2 /* $Header: ARBRTESB.pls 120.20.12000000.3 2007/07/25 13:38:15 nemani ship $ */
3
4 --------- Private Procedures
5 PROCEDURE from_automatic_batch_window(
6 p_draft_mode IN VARCHAR2,
7 p_print_flag IN VARCHAR2,
8 p_batch_id IN RA_BATCHES.batch_id%TYPE,
9 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
10 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
11 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
12 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
13 P_trx_type_id IN RA_CUST_TRX_TYPES.cust_trx_type_id%TYPE,
14 p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
15 p_cust_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
16 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
17 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
18 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
19 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
20 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
21 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE);
22
23
24 PROCEDURE from_conc_request_window(
25 p_print_flag IN varchar2,
26 p_batch_source_id IN RA_BATCHES.batch_source_id%TYPE,
27 p_batch_date IN RA_BATCHES.batch_date%TYPE,
28 p_gl_date IN VARCHAR2,
29 p_issue_date IN VARCHAR2,
30 p_maturity_date IN RA_BATCHES.maturity_date%TYPE,
31 p_currency_code IN RA_BATCHES.currency_code%TYPE,
32 p_comments IN RA_BATCHES.comments%TYPE,
33 p_special_instructions IN RA_BATCHES.special_instructions%TYPE,
34 p_attribute_category IN RA_BATCHES.attribute_category%TYPE,
35 p_attribute1 IN VARCHAR2,
36 p_attribute2 IN VARCHAR2,
37 p_attribute3 IN VARCHAR2,
38 p_attribute4 IN VARCHAR2,
39 p_attribute5 IN VARCHAR2,
40 p_attribute6 IN VARCHAR2,
41 p_attribute7 IN VARCHAR2,
42 p_attribute8 IN VARCHAR2,
43 p_attribute9 IN VARCHAR2,
44 p_attribute10 IN VARCHAR2,
45 p_attribute11 IN VARCHAR2,
46 p_attribute12 IN VARCHAR2,
47 p_attribute13 IN VARCHAR2,
48 p_attribute14 IN VARCHAR2,
49 p_attribute15 IN VARCHAR2,
50 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
51 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
52 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
53 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
54 P_trx_type_id IN RA_CUST_TRX_TYPES.cust_trx_type_id%TYPE,
55 p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
56 p_cust_bank_branch_id IN CE_BANK_BRANCHES_V.branch_party_id%TYPE,
57 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
58 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
59 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
60 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
61 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
62 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE);
63
64 PROCEDURE arbr_cr_tmp_table;
65
66
67 PROCEDURE drop_tmp_table;
68
69
70 PROCEDURE create_batch_header (
71 p_batch_source_id IN RA_BATCHES.batch_source_id%TYPE,
72 p_batch_date IN RA_BATCHES.batch_date%TYPE,
73 p_gl_date IN VARCHAR2, -- currently not used
74 p_issue_date IN VARCHAR2, -- currently not used
75 p_maturity_date IN RA_BATCHES.maturity_date%TYPE,
76 p_currency_code IN RA_BATCHES.currency_code%TYPE,
77 p_comments IN RA_BATCHES.comments%TYPE,
78 p_special_instructions IN RA_BATCHES.special_instructions%TYPE,
79 p_attribute_category IN RA_BATCHES.attribute_category%TYPE,
80 p_attribute1 IN VARCHAR2,
81 p_attribute2 IN VARCHAR2,
82 p_attribute3 IN VARCHAR2,
83 p_attribute4 IN VARCHAR2,
84 p_attribute5 IN VARCHAR2,
85 p_attribute6 IN VARCHAR2,
86 p_attribute7 IN VARCHAR2,
87 p_attribute8 IN VARCHAR2,
88 p_attribute9 IN VARCHAR2,
89 p_attribute10 IN VARCHAR2,
90 p_attribute11 IN VARCHAR2,
91 p_attribute12 IN VARCHAR2,
92 p_attribute13 IN VARCHAR2,
93 p_attribute14 IN VARCHAR2,
94 p_attribute15 IN VARCHAR2,
95 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
96 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
97 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
98 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
99 P_trx_type_id IN RA_CUST_TRX_TYPES.cust_trx_type_id%TYPE,
100 p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
101 p_cust_bank_branch_id IN CE_BANK_BRANCHES_V.branch_party_id%TYPE,
102 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
103 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
104 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
105 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
106 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
107 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
108 p_batch_id OUT NOCOPY RA_BATCHES.batch_id%TYPE,
109 p_selection_criteria_id OUT NOCOPY RA_BATCHES.selection_criteria_id%TYPE);
110
111
112 PROCEDURE update_batch_status(
113 p_draft_mode IN VARCHAR2,
114 p_batch_id IN RA_BATCHES.batch_id%TYPE);
115
116
117 PROCEDURE select_trx_and_create_BR(
118 p_draft_mode IN VARCHAR2,
119 p_call IN NUMBER,
120 p_batch_id IN RA_BATCHES.batch_id%TYPE,
121 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
122 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
123 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
124 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
125 P_trx_type_id IN RA_CUST_TRX_TYPES.cust_trx_type_id%TYPE,
126 p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
127 p_cust_bank_branch_id IN CE_BANK_BRANCHES_V.branch_party_id%TYPE,
128 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
129 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
130 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
131 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
132 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
133 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE);
134
135 -- Bug 3922691 : added additional parameters so dynamic sql can be more fine-tuned
136 PROCEDURE construct_suffixe_select(
137 p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
138 p_suffixe_select_statement OUT NOCOPY varchar2,
139 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
140 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
141 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
142 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
143 p_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
144 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
145 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
146 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
147 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
148 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
149 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
150 p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE);
151
152 -- bug 3922691 : consolidate code used in 2 procedures
153 PROCEDURE construct_hz(
154 p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
155 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
156 p_suffix_hz OUT NOCOPY varchar2);
157
158 -- bug 3922691 : remove p_suffixe_select_statement, add p_lead_days
159 PROCEDURE select_DM_and_CM_IMM(
160 p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
161 p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
162 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
163 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
164 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
165 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
166 p_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
167 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
168 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
169 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
170 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
171 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
172 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
173 p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
174 p_batch_id IN RA_BATCHES.batch_id%TYPE,
175 p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
176 p_exchange_rate IN RA_CUSTOMER_TRX.exchange_rate%TYPE,
177 p_customer_bank_account_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE,
178 p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE);
179
180 -- bug 3922691 : remove p_suffixe_select_statement, add p_lead_days
181 PROCEDURE select_trx_NIMM(
182 p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
183 p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
184 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
185 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
186 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
187 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
188 p_trx_type_id IN RA_CUST_TRX_TYPES.cust_trx_type_id%TYPE,
189 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
190 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
191 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
192 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
193 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
194 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
195 p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
196 p_batch_id IN RA_BATCHES.batch_id%TYPE,
197 p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
198 p_exchange_rate IN RA_CUSTOMER_TRX.exchange_rate%TYPE,
199 p_payment_schedule_id IN AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE,
200 p_customer_trx_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
201 p_customer_bank_account_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE,
202 p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE);
203
204 PROCEDURE create_BR(
205 p_draft_mode IN VARCHAR2,
206 p_call IN NUMBER,
207 p_batch_id IN RA_BATCHES.batch_id%TYPE,
208 p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
209 p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
210 p_maturity_date_rule_code IN AR_RECEIPT_METHODS.maturity_date_rule_code%TYPE,
211 p_br_min_acctd_amount IN AR_RECEIPT_METHODS.br_min_acctd_amount%TYPE,
212 p_br_max_acctd_amount IN AR_RECEIPT_METHODS.br_max_acctd_amount%TYPE,
213 p_currency_code IN RA_BATCHES.currency_code%TYPE,
214 p_customer_bank_account_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE DEFAULT NULL,
215 p_le_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE,
216 p_bill_id OUT NOCOPY RA_CUSTOMER_TRX.customer_trx_id%TYPE,
217 p_request_id OUT NOCOPY NUMBER);
218
219
220 -- Bug 1420183 Added p_receipt_method_id
221 PROCEDURE AR_BR_INSERT_INTO_REPORT_TABLE(
222 p_request_id IN RA_CUSTOMER_TRX.request_id%TYPE,
223 p_batch_id IN RA_BATCHES.batch_id%TYPE,
224 p_br_customer_trx_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
225 p_bill_number IN RA_CUSTOMER_TRX.TRX_NUMBER%TYPE,
226 p_br_amount IN AR_RECEIPT_METHODS.br_min_acctd_amount%TYPE,
227 p_br_currency IN RA_BATCHES.currency_code%TYPE,
228 p_batch_status IN RA_BATCHES.status%TYPE,
229 p_maturity_date IN RA_BATCHES.maturity_date%TYPE,
230 p_drawee_id IN RA_CUSTOMER_TRX.drawee_id%TYPE,
231 p_drawee_contact_id IN RA_CUSTOMER_TRX.drawee_contact_id%TYPE,
232 p_drawee_site_use_id IN RA_CUSTOMER_TRX.drawee_site_use_id%TYPE,
233 p_drawee_bank_account_id IN RA_CUSTOMER_TRX.drawee_bank_account_id%TYPE,
234 p_transaction_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
235 p_amount_assigned IN RA_CUSTOMER_TRX.br_amount%TYPE,
236 p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE);
237
238 PROCEDURE run_report_pvt(
239 p_batch_id IN RA_BATCHES.batch_id%TYPE);
240
241
242 PROCEDURE print_BR_pvt(
243 p_object_id IN RA_BATCHES.batch_id%TYPE,
244 p_call IN NUMBER,
245 p_request_id OUT NOCOPY NUMBER);
246
247
248 --------- Global variables
249 G_PKG_NAME CONSTANT varchar2(30) := 'ARP_PROGRAM_GENERATE_BR';
250
251 TYPE cur_typ IS REF CURSOR;
252
253 /* Bug 3472744 Declarations for the new pl/sql table included
254 and other changes. */
255
256 l_error_mesg fnd_new_messages.message_text%TYPE;
257 g_num_br_failed NUMBER := 0 ;
258
259 g_ctr NUMBER := 0;
260
261 TYPE errorinvoicerectyp IS RECORD (
262 payment_schedule_id ar_payment_schedules.payment_schedule_id%TYPE ,
263 customer_trx_id ar_payment_schedules.customer_trx_id%TYPE ,
264 trx_number ar_payment_schedules.trx_number%TYPE);
265
266 TYPE errorinvoicetabtyp IS TABLE OF errorinvoicerectyp
267 INDEX by BINARY_INTEGER;
268
269 errorinv errorinvoicetabtyp;
270
271 /* Bug 3472744 End of changes */
272
273 g_tmp_table_nimm varchar2(50);
274 g_tmp_table_imm varchar2(50);
275 g_tmp_table_aimm varchar2(50); /* Bug 3930958 : define new temporary table */
276
277 g_num_br_created NUMBER :=0;
278 g_field varchar2(30);
279 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
280
281 -- SSA - R12
282 g_org_id AR_SYSTEM_PARAMETERS.org_id%TYPE;
283
284
285 PROCEDURE write_debug_and_log(p_message IN VARCHAR2) IS
286
287 BEGIN
288
289 IF FND_GLOBAL.CONC_REQUEST_ID is not null THEN
290
291 fnd_file.put_line(FND_FILE.LOG,p_message);
292
293 END IF;
294
295 IF PG_DEBUG in ('Y', 'C') THEN
296 arp_standard.debug(p_message);
297 END IF;
298
299 EXCEPTION
300 WHEN others THEN
301 NULL;
302
303 END write_debug_and_log;
304
305
306 -- Bug2290332: Added for better debugging of create_br procedure when running
307 -- from Transaction Form.
308 PROCEDURE program_debug(p_call IN NUMBER, string IN VARCHAR2) IS
309 BEGIN
310 IF p_call = 3 THEN
311 arp_util.debug(string);
312 ELSE
313 write_debug_and_log(string);
314 END IF;
315 END;
316 --------- Public Procedures
317
318 /*===========================================================================+
319 | PROCEDURE |
320 | auto_create_br_program |
321 | |
322 | DESCRIPTION |
323 | |
324 | |
325 | SCOPE - PUBLIC |
326 | |
327 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
328 | |
329 | ARGUMENTS : IN : |
330 | |
331 | p_call : |
332 | = 1 if submit from the Automatic Batch Window |
333 | = 2 if submit from the Submit Concurrent Request Window |
334 | p_draft_mode |
335 | = Y if mode draft is selected |
336 | = N if mode create is selected |
337 | |
338 | |
339 | |
340 | : OUT : NONE |
341 | |
342 | RETURNS : NONE |
343 | |
344 | NOTES |
345 | |
346 | MODIFICATION HISTORY - Created by Mireille Flahaut - 20/07/2000 |
347 | |
348 +===========================================================================*/
349 PROCEDURE auto_create_br_program(
350 errbuf OUT NOCOPY VARCHAR2,
351 retcode OUT NOCOPY VARCHAR2,
352 p_call IN NUMBER,
353 p_draft_mode IN VARCHAR2,
354 p_print_flag IN VARCHAR2,
355 p_batch_id IN RA_BATCHES.batch_id%TYPE DEFAULT NULL,
356 p_batch_source_id IN RA_BATCH_SOURCES.batch_source_id%TYPE,
357 p_batch_date IN VARCHAR2,
358 p_gl_date IN VARCHAR2 DEFAULT NULL,
359 p_issue_date IN VARCHAR2 DEFAULT NULL,
360 p_maturity_date IN VARCHAR2 DEFAULT NULL,
361 p_currency_code IN RA_BATCHES.currency_code%TYPE DEFAULT NULL,
362 p_comments IN RA_BATCHES.comments%TYPE DEFAULT NULL,
363 p_special_instructions IN RA_BATCHES.special_instructions%TYPE DEFAULT NULL,
364 p_attribute_category IN RA_BATCHES.attribute_category%TYPE DEFAULT NULL,
365 p_attribute1 IN VARCHAR2 DEFAULT NULL,
366 p_attribute2 IN VARCHAR2 DEFAULT NULL,
367 p_attribute3 IN VARCHAR2 DEFAULT NULL,
368 p_attribute4 IN VARCHAR2 DEFAULT NULL,
369 p_attribute5 IN VARCHAR2 DEFAULT NULL,
370 p_attribute6 IN VARCHAR2 DEFAULT NULL,
371 p_attribute7 IN VARCHAR2 DEFAULT NULL,
372 p_attribute8 IN VARCHAR2 DEFAULT NULL,
373 p_attribute9 IN VARCHAR2 DEFAULT NULL,
374 p_attribute10 IN VARCHAR2 DEFAULT NULL,
375 p_attribute11 IN VARCHAR2 DEFAULT NULL,
376 p_attribute12 IN VARCHAR2 DEFAULT NULL,
377 p_attribute13 IN VARCHAR2 DEFAULT NULL,
378 p_attribute14 IN VARCHAR2 DEFAULT NULL,
379 p_attribute15 IN VARCHAR2 DEFAULT NULL,
380 p_due_date_low IN VARCHAR2 DEFAULT NULL,
381 p_due_date_high IN VARCHAR2 DEFAULT NULL,
382 p_trx_date_low IN VARCHAR2 DEFAULT NULL,
383 p_trx_date_high IN VARCHAR2 DEFAULT NULL,
384 P_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE DEFAULT NULL,
385 p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE DEFAULT NULL,
386 p_cust_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE DEFAULT NULL,
387 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE DEFAULT NULL,
388 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE DEFAULT NULL,
389 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE DEFAULT NULL,
390 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE DEFAULT NULL,
391 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE DEFAULT NULL,
392 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE DEFAULT NULL) IS
393
394 -- Dates
395 l_batch_date DATE := NULL;
396 l_gl_date DATE := NULL;
397 l_issue_date DATE := NULL;
398 l_maturity_date DATE := NULL;
399 l_due_date_low DATE := NULL;
400 l_due_date_high DATE := NULL;
401 l_trx_date_low DATE := NULL;
402 l_trx_date_high DATE := NULL;
403
404 l_batch_id RA_BATCHES.batch_id%TYPE;
405
406 BEGIN
407
408 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.auto_create_br_program (+)');
409
410 --------------------------------------------------------------
411 -- Date Conversions
412 --------------------------------------------------------------
413 l_batch_date := TO_DATE(p_batch_date,'YYYY/MM/DD HH24:MI:SS');
414
415 IF (p_gl_date IS NOT NULL) THEN
416 l_gl_date := TO_DATE(p_gl_date,'YYYY/MM/DD HH24:MI:SS');
417 END IF;
418
419 IF (p_issue_date IS NOT NULL) THEN
420 l_issue_date := TO_DATE(p_issue_date,'YYYY/MM/DD HH24:MI:SS');
421 END IF;
422
423 IF (p_maturity_date IS NOT NULL) THEN
424 l_maturity_date := TO_DATE(p_maturity_date,'YYYY/MM/DD HH24:MI:SS');
425 END IF;
426
427 IF (p_due_date_low IS NOT NULL) AND (p_due_date_high IS NOT NULL) THEN
428 l_due_date_low := TO_DATE(p_due_date_low,'YYYY/MM/DD HH24:MI:SS');
429 l_due_date_high := TO_DATE(p_due_date_high,'YYYY/MM/DD HH24:MI:SS');
430 END IF;
431
432 IF (p_trx_date_low IS NOT NULL) AND (p_trx_date_high IS NOT NULL) THEN
433 l_trx_date_low := TO_DATE(p_trx_date_low,'YYYY/MM/DD HH24:MI:SS');
434 l_trx_date_high := TO_DATE(p_trx_date_high,'YYYY/MM/DD HH24:MI:SS');
435 END IF;
436
437 --------------------------------------------------------------
438 -- Process
439 --------------------------------------------------------------
440 IF (p_call = 1) THEN
441
442 ARP_PROGRAM_GENERATE_BR.from_automatic_batch_window(
443 p_draft_mode,
444 p_print_flag,
445 p_batch_id,
446 l_due_date_low,
447 l_due_date_high,
448 l_trx_date_low,
449 l_trx_date_high,
450 p_trx_type_id,
451 p_rcpt_meth_id,
452 p_cust_bank_branch_id,
453 p_trx_number_low,
454 p_trx_number_high,
455 p_cust_class,
456 p_cust_category,
457 p_customer_id,
458 p_site_use_id);
459
460 ELSIF (p_call = 2) THEN
461
462 ARP_PROGRAM_GENERATE_BR.from_conc_request_window(
463 p_print_flag,
464 p_batch_source_id,
465 l_batch_date,
466 l_gl_date,
467 l_issue_date,
468 l_maturity_date,
469 p_currency_code,
470 p_comments,
471 p_special_instructions,
472 p_attribute_category,
473 p_attribute1,
474 p_attribute2,
475 p_attribute3,
476 p_attribute4,
477 p_attribute5,
478 p_attribute6,
479 p_attribute7,
480 p_attribute8,
481 p_attribute9,
482 p_attribute10,
483 p_attribute11,
484 p_attribute12,
485 p_attribute13,
486 p_attribute14,
487 p_attribute15,
488 l_due_date_low,
489 l_due_date_high,
490 l_trx_date_low,
491 l_trx_date_high,
492 P_trx_type_id,
493 p_rcpt_meth_id,
494 p_cust_bank_branch_id,
495 p_trx_number_low,
496 p_trx_number_high,
497 p_cust_class,
498 p_cust_category,
499 p_customer_id,
500 p_site_use_id);
501
502
503 ELSE
504 g_field := 'p_call';
505 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
506 FND_MESSAGE.set_token('PROCEDURE','auto_create_br_program');
507 FND_MESSAGE.set_token('PARAMETER', g_field);
508 APP_EXCEPTION.raise_exception;
509 END IF;
510
511 --Temporary table Drop
512 ARP_PROGRAM_GENERATE_BR.drop_tmp_table;
513
514 /* Bug 3472744 Printing the errored BRs at the end of the log file. */
515 IF (g_num_br_failed > 0)
516 THEN
517 IF errorinv.EXISTS(g_ctr-1)
518 THEN
519 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
520 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
521
522 FND_MESSAGE.SET_NAME('AR','AR_BR_INVALID_TRX_WARNING');
523 l_error_mesg := FND_MESSAGE.GET;
524 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_mesg);
525
526 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
527 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
528 FND_FILE.PUT_LINE(FND_FILE.LOG,'*******************************************************************');
529 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
530 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
531
532 FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction Number'||rpad(' ',8)||'Payment
533 Schedule Id'||rpad(' ',7)||'Customer Trx Id'||rpad(' ',11));
534
535 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------'||rpad(' ',8)||'-------------------'||rpad(' ',7)||'---------------'||rpad(' ',11));
536
537 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
538
539 FOR l_ctr IN errorinv.FIRST .. errorinv.LAST LOOP
540 FND_FILE.PUT_LINE(FND_FILE.LOG,' ' || rpad(errorinv(l_ctr).trx_number,26)|| rpad(errorinv(l_ctr).payment_schedule_id,26) || rpad(errorinv(l_ctr).customer_trx_id,26));
541 END LOOP;
542 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
543
544 FND_FILE.PUT_LINE(FND_FILE.LOG,'*******************************************************************');
545 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
546 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
547
548 END IF;
549 END IF ;
550
551 --Temporary table Drop
552 ARP_PROGRAM_GENERATE_BR.drop_tmp_table;
553
554 COMMIT;
555
556 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.auto_create_br_program (-)');
557
558 EXCEPTION
559 WHEN OTHERS THEN
560 --Temporary table Drop
561 ARP_PROGRAM_GENERATE_BR.drop_tmp_table;
562 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.auto_create_br_program');
563 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
564 RAISE;
565
566 END auto_create_br_program;
567
568
569 /*===========================================================================+
570 | PROCEDURE |
571 | from_automatic_batch_window |
572 | |
573 | DESCRIPTION |
574 | |
575 | |
576 | SCOPE - PUBLIC |
577 | |
578 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
579 | |
580 | ARGUMENTS : IN : |
581 | : OUT : NONE |
582 | |
583 | RETURNS : NONE |
584 | |
585 | NOTES |
586 | |
587 | MODIFICATION HISTORY - Created by Mireille Flahaut - 20/07/2000 |
588 | |
589 +===========================================================================*/
590 PROCEDURE from_automatic_batch_window(
591 p_draft_mode IN VARCHAR2,
592 p_print_flag IN VARCHAR2,
593 p_batch_id IN RA_BATCHES.batch_id%TYPE,
594 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
595 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
596 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
597 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
598 P_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
599 p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
600 p_cust_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
601 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
602 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
603 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
604 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
605 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
606 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE) IS
607
608 l_request_id NUMBER;
609
610 BEGIN
611
612 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.from_automatic_batch_window (+)');
613
614 -- Temporary table Creation
615 ARP_PROGRAM_GENERATE_BR.arbr_cr_tmp_table;
616
617 -- Select the transactions using the users criteria and create the Bills Receivable
618 ARP_PROGRAM_GENERATE_BR.select_trx_and_create_BR(
619 p_draft_mode,
620 1, -- p_call
621 p_batch_id,
622 p_due_date_low,
623 p_due_date_high,
624 p_trx_date_low,
625 p_trx_date_high,
626 P_trx_type_id,
627 p_rcpt_meth_id,
628 p_cust_bank_branch_id,
629 p_trx_number_low,
630 p_trx_number_high,
631 p_cust_class,
632 p_cust_category,
633 p_customer_id,
634 p_site_use_id);
635 -- Update the batch status to 'CREATION_COMPLETED' if the batch run in Create mode.
636 -- Otherwise, the batch status is updated to 'DRAFT'.
637 ARP_PROGRAM_GENERATE_BR.update_batch_status(
638 p_draft_mode,
639 p_batch_id);
640
641 /* Bug 3472744 Added the check for number of brs created before
642 calling the Automatic Batches report and BR printing program. */
643
644 IF (g_num_br_created > 0)
645 THEN
646 ------ Run the report 'Automatic Transactions Batch'
647 run_report_pvt(p_batch_id);
648
649 ------ Action PRINT BR
650 IF (p_print_flag = 'Y') THEN
651 print_BR_pvt(p_batch_id,1,l_request_id);
652 END IF;
653 END IF;
654
655 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.from_automatic_batch_window (-)');
656
657 EXCEPTION
658 WHEN OTHERS THEN
659 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.from_automatic_batch_window');
660 RAISE;
661
662 END from_automatic_batch_window;
663
664
665 /*===========================================================================+
666 | PROCEDURE |
667 | from_conc_request_window |
668 | |
669 | DESCRIPTION |
670 | |
671 | |
672 | SCOPE - PUBLIC |
673 | |
674 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
675 | |
676 | ARGUMENTS : IN : |
677 | : OUT : NONE |
678 | |
679 | RETURNS : NONE |
680 | |
681 | NOTES |
682 | |
683 | MODIFICATION HISTORY - Created by Mireille Flahaut - 20/07/2000 |
684 | |
685 +===========================================================================*/
686 PROCEDURE from_conc_request_window(
687 p_print_flag IN varchar2,
688 p_batch_source_id IN RA_BATCHES.batch_source_id%TYPE,
689 p_batch_date IN RA_BATCHES.batch_date%TYPE,
690 p_gl_date IN VARCHAR2,
691 p_issue_date IN VARCHAR2,
692 p_maturity_date IN RA_BATCHES.maturity_date%TYPE,
693 p_currency_code IN RA_BATCHES.currency_code%TYPE,
694 p_comments IN RA_BATCHES.comments%TYPE,
695 p_special_instructions IN RA_BATCHES.special_instructions%TYPE,
696 p_attribute_category IN RA_BATCHES.attribute_category%TYPE,
697 p_attribute1 IN VARCHAR2,
698 p_attribute2 IN VARCHAR2,
699 p_attribute3 IN VARCHAR2,
700 p_attribute4 IN VARCHAR2,
701 p_attribute5 IN VARCHAR2,
702 p_attribute6 IN VARCHAR2,
703 p_attribute7 IN VARCHAR2,
704 p_attribute8 IN VARCHAR2,
705 p_attribute9 IN VARCHAR2,
706 p_attribute10 IN VARCHAR2,
707 p_attribute11 IN VARCHAR2,
708 p_attribute12 IN VARCHAR2,
709 p_attribute13 IN VARCHAR2,
710 p_attribute14 IN VARCHAR2,
711 p_attribute15 IN VARCHAR2,
712 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
713 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
714 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
715 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
716 P_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
717 p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
718 p_cust_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
719 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
720 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
721 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
722 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
723 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
724 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE) IS
725
726 l_batch_id RA_BATCHES.batch_id%TYPE;
727 l_selection_criteria_id RA_BATCHES.selection_criteria_id%TYPE;
728
729 l_request_id NUMBER;
730
731 BEGIN
732
733 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.from_conc_request_window (+)');
734
735 -- Temporary table Creation
736 ARP_PROGRAM_GENERATE_BR.arbr_cr_tmp_table;
737
738 -- Insert the batch header in RA_BATCHES and the criteria in AR_SELECTION_CRITERIA
739 ARP_PROGRAM_GENERATE_BR.create_batch_header(
740 p_batch_source_id,
741 p_batch_date,
742 p_gl_date,
743 p_issue_date,
744 p_maturity_date,
745 p_currency_code,
746 p_comments,
747 p_special_instructions,
748 p_attribute_category,
749 p_attribute1,
750 p_attribute2,
751 p_attribute3,
752 p_attribute4,
753 p_attribute5,
754 p_attribute6,
755 p_attribute7,
756 p_attribute8,
757 p_attribute9,
758 p_attribute10,
759 p_attribute11,
760 p_attribute12,
761 p_attribute13,
762 p_attribute14,
763 p_attribute15,
764 p_due_date_low,
765 p_due_date_high,
766 p_trx_date_low,
767 p_trx_date_high,
768 P_trx_type_id,
769 p_rcpt_meth_id,
770 p_cust_bank_branch_id,
771 p_trx_number_low,
772 p_trx_number_high,
773 p_cust_class,
774 p_cust_category,
775 p_customer_id,
776 p_site_use_id,
777 l_batch_id,
778 l_selection_criteria_id);
779
780
781
782 -- Select the transactions using the users criteria and create the Bills Receivable
783 ARP_PROGRAM_GENERATE_BR.select_trx_and_create_BR(
784 'N', -- p_draft_mode (the user do not have the option of creating a BR batch in Draft Mode, by SRS)
785 2, -- p_call
786 l_batch_id,
787 p_due_date_low,
788 p_due_date_high,
789 p_trx_date_low,
790 p_trx_date_high,
791 P_trx_type_id,
792 p_rcpt_meth_id,
793 p_cust_bank_branch_id,
794 p_trx_number_low,
795 p_trx_number_high,
796 p_cust_class,
797 p_cust_category,
798 p_customer_id,
799 p_site_use_id);
800
801
802 --- The batch status is updated to 'Creation Completed'
803 ARP_PROGRAM_GENERATE_BR.update_batch_status(
804 'N', -- p_draft_mode
805 l_batch_id);
806
807 /* Bug 3472744 Added the check for number of brs created before
808 calling the Automatic Batches report and BR printing program. */
809
810 IF (g_num_br_created > 0)
811 THEN
812 -- Run the report 'Automatic Transactions Batch'
813 run_report_pvt(l_batch_id);
814
815 ------ Action PRINT BR
816 IF (p_print_flag = 'Y') THEN
817 print_BR_pvt(l_batch_id,2,l_request_id);
818 END IF;
819 END IF;
820
821 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.from_conc_request_window (-)');
822
823 EXCEPTION
824 WHEN OTHERS THEN
825 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.from_conc_request_window');
826 RAISE;
827
828 END from_conc_request_window;
829
830
831 /*===========================================================================+
832 | PROCEDURE |
833 | create_batch_header |
834 | |
835 | DESCRIPTION |
836 | |
837 | |
838 | SCOPE - PUBLIC |
839 | |
840 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
841 | |
842 | ARGUMENTS : IN : |
843 | : OUT : NONE |
844 | |
845 | RETURNS : NONE |
846 | |
847 | NOTES |
848 | |
849 | MODIFICATION HISTORY - Created by Mireille Flahaut - 26/07/2000 |
850 | |
851 +===========================================================================*/
852 PROCEDURE create_batch_header (
853 p_batch_source_id IN RA_BATCHES.batch_source_id%TYPE,
854 p_batch_date IN RA_BATCHES.batch_date%TYPE,
855 p_gl_date IN VARCHAR2, -- currently not used
856 p_issue_date IN VARCHAR2, -- currently not used
857 p_maturity_date IN RA_BATCHES.maturity_date%TYPE,
858 p_currency_code IN RA_BATCHES.currency_code%TYPE,
859 p_comments IN RA_BATCHES.comments%TYPE,
860 p_special_instructions IN RA_BATCHES.special_instructions%TYPE,
861 p_attribute_category IN RA_BATCHES.attribute_category%TYPE,
862 p_attribute1 IN VARCHAR2,
863 p_attribute2 IN VARCHAR2,
864 p_attribute3 IN VARCHAR2,
865 p_attribute4 IN VARCHAR2,
866 p_attribute5 IN VARCHAR2,
867 p_attribute6 IN VARCHAR2,
868 p_attribute7 IN VARCHAR2,
869 p_attribute8 IN VARCHAR2,
870 p_attribute9 IN VARCHAR2,
871 p_attribute10 IN VARCHAR2,
872 p_attribute11 IN VARCHAR2,
873 p_attribute12 IN VARCHAR2,
874 p_attribute13 IN VARCHAR2,
875 p_attribute14 IN VARCHAR2,
876 p_attribute15 IN VARCHAR2,
877 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
878 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
879 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
880 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
881 P_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
882 p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
883 p_cust_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
884 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
885 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
886 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
887 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
888 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
889 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
890 p_batch_id OUT NOCOPY RA_BATCHES.batch_id%TYPE,
891 p_selection_criteria_id OUT NOCOPY
892 RA_BATCHES.selection_criteria_id%TYPE) IS
893 l_issue_date RA_BATCHES.issue_date%TYPE;
894 l_gl_date RA_BATCHES.gl_date%TYPE;
895 l_default_rule_used VARCHAR2(30);
896 l_error_message VARCHAR2(30);
897
898 l_batch_id RA_BATCHES.batch_id%TYPE;
899 l_selection_criteria_id RA_BATCHES.batch_id%TYPE;
900 l_name RA_BATCHES.name%TYPE;
901
902 BEGIN
903
904 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.create_batch_header (+)');
905
906 -- The Issue date will be inherited from the Batch date
907 l_issue_date := p_batch_date;
908
909 --------------------------------------------------------------
910 -- Validations
911 --------------------------------------------------------------
912 IF NVL(p_maturity_date,l_issue_date) < l_issue_date THEN
913 FND_MESSAGE.set_name('AR','AR_BR_MAT_BEFORE_ISSUE_DATE');
914 APP_EXCEPTION.raise_exception;
915 END IF;
916
917 -- The GL date will follow defaulting by first trying the issue date, then following open period rules
918 IF (arp_util.validate_and_default_gl_date(p_batch_date, -- gl_date
919 NULL, -- trx_date
920 NULL, -- validation_date1
921 NULL, -- validation-date2
922 NULL, -- validation-date3
923 l_issue_date, -- default_date1
924 NULL, -- default_date2
925 NULL, -- default_date3
926 NULL, -- p_allow_not_open_flag
927 NULL, -- p_invoicing_rule_id
928 arp_global.set_of_books_id, -- p_set_of_books_id
929 arp_global.program_application_id, -- p_application-id
930 l_gl_date,
931 l_default_rule_used,
932 l_error_message) = FALSE) THEN
933 FND_MESSAGE.set_name('AR', 'GENERIC_MESSAGE');
934 FND_MESSAGE.set_token('GENERIC_TEXT',l_error_message);
935 APP_EXCEPTION.raise_exception;
936 END IF;
937
938 -- Insert the batch header in RA_BATCHES and the criteria in AR_SELECTION_CRITERIA
939 arp_process_br_batches.insert_batch('FNDRSRUN', -- p_form_name
940 NULL, -- p_form_version
941 p_batch_source_id, -- p_batch_source_id
942 p_batch_date, -- p_batch_date
943 l_gl_date, -- p_gl_date
944 'BR', -- p_type
945 p_currency_code, -- p_currency_code
946 p_comments, -- p_comments
947 p_attribute_category, -- p_attribute_category
948 p_attribute1, -- p_attribute1
949 p_attribute2, -- p_attribute2
950 p_attribute3, -- p_attribute3
951 p_attribute4, -- p_attribute4
952 p_attribute5, -- p_attribute5
953 p_attribute6, -- p_attribute6
954 p_attribute7, -- p_attribute7
955 p_attribute8, -- p_attribute8
956 p_attribute9, -- p_attribute9
957 p_attribute10, -- p_attribute10
958 p_attribute11, -- p_attribute11
959 p_attribute12, -- p_attribute12
960 p_attribute13, -- p_attribute13
961 p_attribute14, -- p_attribute14
962 p_attribute15, -- p_attribute15
963 l_issue_date, -- p_issue_date
964 p_maturity_date, -- p_maturity_date
965 p_special_instructions, -- p_special_instructions
966 'CREATION_STARTED', -- p_batch_process_status
967 p_due_date_low, -- p_due_date_low
968 p_due_date_high, -- p_due_date_high
969 p_trx_date_low, -- p_trx_date_low
970 p_trx_date_high, -- p_trx_date_high
971 P_trx_type_id, -- p_cust_trx_type_id
972 p_rcpt_meth_id, -- p_receipt_method_id
973 p_cust_bank_branch_id, -- p_bank_branch_id
974 p_trx_number_low, -- p_trx_number_low
975 p_trx_number_high, -- p_trx_number_high
976 p_cust_class, -- p_customer_class_code
977 p_cust_category, -- p_customer_category_code
978 p_customer_id, -- p_customer_id
979 p_site_use_id, -- p_site_use_id
980 l_selection_criteria_id,
981 l_batch_id,
982 l_name);
983
984 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert the BR Creation Batch name:'||l_name);
985
986 p_selection_criteria_id := l_selection_criteria_id;
987 p_batch_id := l_batch_id;
988
989 COMMIT;
990
991 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.create_batch_header (-)');
992
993 EXCEPTION
994 WHEN OTHERS THEN
995 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.create_batch_header');
996 RAISE;
997
998 END create_batch_header;
999
1000
1001 /*===========================================================================+
1002 | PROCEDURE |
1003 | update_batch_status |
1004 | |
1005 | DESCRIPTION |
1006 | |
1007 | |
1008 | SCOPE - PUBLIC |
1009 | |
1010 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1011 | |
1012 | ARGUMENTS : IN : |
1013 | : OUT : NONE |
1014 | |
1015 | RETURNS : NONE |
1016 | |
1017 | NOTES |
1018 | |
1019 | MODIFICATION HISTORY - Created by Mireille Flahaut - 08/08/2000 |
1020 | |
1021 +===========================================================================*/
1022 PROCEDURE update_batch_status(
1023 p_draft_mode IN VARCHAR2,
1024 p_batch_id IN RA_BATCHES.batch_id%TYPE) IS
1025
1026 l_batch_rec RA_BATCHES%ROWTYPE;
1027 l_criteria_rec AR_SELECTION_CRITERIA%ROWTYPE;
1028
1029 l_selection_criteria_id RA_BATCHES.selection_criteria_id%TYPE;
1030 l_status RA_BATCHES.batch_process_status%TYPE;
1031
1032
1033 BEGIN
1034
1035 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.update_batch_status (+)');
1036
1037 -- fetch the batch
1038 arp_tbat_pkg.lock_fetch_p(l_batch_rec,p_batch_id);
1039
1040 -- fetch the criteria
1041 IF (l_batch_rec.selection_criteria_id IS NOT NULL) THEN
1042 Select *
1043 into l_criteria_rec
1044 from ar_selection_criteria
1045 where selection_criteria_id = l_batch_rec.selection_criteria_id;
1046 END IF;
1047
1048 -- Update the batch status to 'CREATION_COMPLETED' if the batch run in Create mode.
1049 -- Otherwise, the batch status is updated to 'DRAFT'.
1050
1051 IF (p_draft_mode = 'Y') THEN
1052 l_status := 'DRAFT';
1053 ELSE
1054 l_status := 'CREATION_COMPLETED';
1055 END IF;
1056
1057 arp_process_br_batches.update_batch ('FNDRSRUN', -- p_form_name
1058 NULL, -- p_form_version
1059 p_batch_id, -- p_batch_id
1060 l_batch_rec.name, -- p_name
1061 l_batch_rec.batch_source_id, -- p_batch_source_id
1062 l_batch_rec.batch_date, -- p_batch_date
1063 l_batch_rec.gl_date, -- p_gl_date
1064 l_batch_rec.type, -- p_type
1065 l_batch_rec.currency_code, -- p_currency_code
1066 l_batch_rec.comments, -- p_comments
1067 l_batch_rec.attribute_category, -- p_attribute_category
1068 l_batch_rec.attribute1, -- p_attribute1
1069 l_batch_rec.attribute2, -- p_attribute2
1070 l_batch_rec.attribute3, -- p_attribute3
1071 l_batch_rec.attribute4, -- p_attribute4
1072 l_batch_rec.attribute5, -- p_attribute5
1073 l_batch_rec.attribute6, -- p_attribute6
1074 l_batch_rec.attribute7, -- p_attribute7
1075 l_batch_rec.attribute8, -- p_attribute8
1076 l_batch_rec.attribute9, -- p_attribute9
1077 l_batch_rec.attribute10, -- p_attribute10
1078 l_batch_rec.attribute11, -- p_attribute11
1079 l_batch_rec.attribute12, -- p_attribute12
1080 l_batch_rec.attribute13, -- p_attribute13
1081 l_batch_rec.attribute14, -- p_attribute14
1082 l_batch_rec.attribute15, -- p_attribute15
1083 l_batch_rec.issue_date, -- p_issue_date
1084 l_batch_rec.maturity_date, -- p_maturity_date
1085 l_batch_rec.special_instructions, -- p_special_instructions
1086 l_status, -- p_batch_process_status
1087 arp_global.request_id, -- p_request_id
1088 l_criteria_rec.due_date_low, -- p_due_date_low,
1089 l_criteria_rec.due_date_high, -- p_due_date_high,
1090 l_criteria_rec.trx_date_low, -- p_trx_date_low,
1091 l_criteria_rec.trx_date_high, -- p_trx_date_high,
1092 l_criteria_rec.cust_trx_type_id, -- p_cust_trx_type_id
1093 l_criteria_rec.receipt_method_id, -- p_receipt_method_id
1094 l_criteria_rec.bank_branch_id, -- p_bank_branch_id
1095 l_criteria_rec.trx_number_low, -- p_trx_number_low
1096 l_criteria_rec.trx_number_high, -- p_trx_number_high
1097 l_criteria_rec.customer_class_code, -- p_customer_class_code
1098 l_criteria_rec.customer_category_code, -- p_customer_category_code
1099 l_criteria_rec.customer_id, -- p_customer_id
1100 l_criteria_rec.site_use_id, -- p_site_use_id
1101 l_selection_criteria_id);
1102
1103 COMMIT;
1104
1105 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.update_batch_status (-)');
1106
1107 EXCEPTION
1108 WHEN OTHERS THEN
1109 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.update_batch_status');
1110 RAISE;
1111
1112 END update_batch_status;
1113
1114
1115 /*===========================================================================+
1116 | PROCEDURE |
1117 | select_trx_and_create_BR |
1118 | |
1119 | DESCRIPTION |
1120 | |
1121 | |
1122 | SCOPE - PUBLIC |
1123 | |
1124 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1125 | |
1126 | ARGUMENTS : IN : |
1127 | : OUT : NONE |
1128 | |
1129 | RETURNS : NONE |
1130 | |
1131 | NOTES |
1132 | |
1133 | MODIFICATION HISTORY - Created by Mireille Flahaut - 26/07/2000 |
1134 | |
1135 | 06-Jun-01 VCRISOST Bug 1808976 : redefine c_receipt_methods to |
1136 | select customer_bank_account_id as well since|
1137 | this is now an implicit grouping rule |
1138 | 17-JAN-04 VCRISOST Bug 4109513 : major changes to looping |
1139 | mechanism to avoid multiple selects using |
1140 | null ps.customer_id |
1141 | 11-MAY-05 VCRISOST LE-R12 : in c_receipt_method, include |
1142 | trx.legal_entity_id, because it is an |
1143 | implicit grouping rule |
1144 | |
1145 +===========================================================================*/
1146 PROCEDURE select_trx_and_create_BR(
1147 p_draft_mode IN VARCHAR2,
1148 p_call IN NUMBER,
1149 p_batch_id IN RA_BATCHES.batch_id%TYPE,
1150 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1151 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1152 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
1153 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
1154 p_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
1155 p_rcpt_meth_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
1156 p_cust_bank_branch_id IN ce_bank_branches_v.branch_party_id%TYPE,
1157 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
1158 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
1159 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
1160 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
1161 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
1162 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE) IS
1163
1164 /*
1165 Bug 1808967 : added trx.customer_bank_account_id in select statement,
1166 by doing this I have made the bank account id an implicit grouping rule
1167
1168 Bug 4109513 : move bank account to cursor c_rm_bank, c_receipt_method is for
1169 distinct receipt methods only
1170 */
1171
1172 -- Cursor used to select the receipt method
1173 CURSOR c_receipt_method IS
1174 SELECT distinct pm.receipt_method_id, pm.receipt_creation_rule_code, NVL(pm.lead_days,0),
1175 pm.maturity_date_rule_code,
1176 DECODE(pm.br_min_acctd_amount,NULL,0.00000001,0,0.00000001,pm.br_min_acctd_amount),
1177 NVL(pm.br_max_acctd_amount,9999999999999999999999999999999999),
1178 trx.invoice_currency_code, trx.exchange_rate, trx.legal_entity_id
1179 FROM ra_batches batch,
1180 ar_receipt_classes class,
1181 ar_receipt_methods pm,
1182 ra_customer_trx trx,
1183 ra_cust_trx_types type,
1184 fnd_currencies_vl cur
1185 WHERE pm.receipt_method_id = NVL(p_rcpt_meth_id,pm.receipt_method_id)
1186 AND type.cust_trx_type_id = NVL(p_trx_type_id,type.cust_trx_type_id)
1187 AND trx.trx_number BETWEEN NVL(p_trx_number_low,trx.trx_number) AND NVL(p_trx_number_high,trx.trx_number)
1188 AND batch.batch_id = p_batch_id
1189 AND trx.trx_date <= NVL(batch.issue_date,batch.batch_date)
1190 AND class.creation_method_code = 'BR'
1191 AND class.receipt_class_id = pm.receipt_class_id
1192 AND trunc(NVL(batch.issue_date,sysdate))
1193 BETWEEN trunc(NVL(pm.start_date,NVL(batch.issue_date,sysdate)))
1194 AND trunc(NVL(pm.end_date,NVL(batch.issue_date,sysdate)))
1195 AND pm.receipt_method_id = trx.receipt_method_id
1196 AND trx.cust_trx_type_id = type.cust_trx_type_id
1197 AND type.type in ('INV','CM','DM','DEP','CB')
1198 AND trunc(NVL(batch.issue_date,sysdate))
1199 BETWEEN trunc(NVL(type.start_date,NVL(batch.issue_date,sysdate)))
1200 AND trunc(NVL(type.end_date,NVL(batch.issue_date,sysdate)))
1201 AND NVL(batch.currency_code,trx.invoice_currency_code) = trx.invoice_currency_code
1202 AND trx.invoice_currency_code = cur.currency_code
1203 AND cur.enabled_flag = 'Y'
1204 AND cur.currency_flag = 'Y'
1205 AND NVL(batch.exchange_rate,NVL(trx.exchange_rate,100)) = NVL(trx.exchange_rate,100)
1206 AND trunc(NVL(batch.issue_date,sysdate))
1207 BETWEEN trunc(NVL(cur.start_date_active,NVL(batch.issue_date,sysdate)))
1208 AND trunc(NVL(cur.end_date_active,NVL(batch.issue_date,sysdate)))
1209 AND pm.br_cust_trx_type_id IS NOT NULL
1210 ORDER BY pm.receipt_method_id;
1211
1212 /* bug 4109513 : get distinct bank accounts using receipt method */
1213 CURSOR c_rm_bank (rm_id IN NUMBER, cust_id IN NUMBER) IS
1214 -- SELECT distinct trx.customer_bank_account_id 5051673
1215 SELECT distinct instrument_id customer_bank_account_id
1216 FROM ra_batches batch,
1217 ar_receipt_classes class,
1218 ar_receipt_methods pm,
1219 ra_customer_trx trx,
1220 ra_cust_trx_types type,
1221 fnd_currencies_vl cur,
1222 iby_trxn_extensions_v extn
1223 WHERE pm.receipt_method_id = rm_id
1224 AND type.cust_trx_type_id = NVL(p_trx_type_id,type.cust_trx_type_id)
1225 AND trx.trx_number BETWEEN NVL(p_trx_number_low,trx.trx_number) AND NVL(p_trx_number_high,trx.trx_number)
1226 AND batch.batch_id = p_batch_id
1227 AND trx.trx_date <= NVL(batch.issue_date,batch.batch_date)
1228 AND class.creation_method_code = 'BR'
1229 AND class.receipt_class_id = pm.receipt_class_id
1230 AND trunc(NVL(batch.issue_date,sysdate))
1231 BETWEEN trunc(NVL(pm.start_date,NVL(batch.issue_date,sysdate)))
1232 AND trunc(NVL(pm.end_date,NVL(batch.issue_date,sysdate)))
1233 AND pm.receipt_method_id = trx.receipt_method_id
1234 AND trx.cust_trx_type_id = type.cust_trx_type_id
1235 AND type.type in ('INV','CM','DM','DEP','CB')
1236 AND trunc(NVL(batch.issue_date,sysdate))
1237 BETWEEN trunc(NVL(type.start_date,NVL(batch.issue_date,sysdate)))
1238 AND trunc(NVL(type.end_date,NVL(batch.issue_date,sysdate)))
1239 AND NVL(batch.currency_code,trx.invoice_currency_code) = trx.invoice_currency_code
1240 AND trx.invoice_currency_code = cur.currency_code
1241 AND cur.enabled_flag = 'Y'
1242 AND cur.currency_flag = 'Y'
1243 AND NVL(batch.exchange_rate,NVL(trx.exchange_rate,100)) = NVL(trx.exchange_rate,100)
1244 AND trunc(NVL(batch.issue_date,sysdate))
1245 BETWEEN trunc(NVL(cur.start_date_active,NVL(batch.issue_date,sysdate)))
1246 AND trunc(NVL(cur.end_date_active,NVL(batch.issue_date,sysdate)))
1247 AND pm.br_cust_trx_type_id IS NOT NULL
1248 AND trx.bill_to_customer_id = cust_id
1249 AND trx.payment_trxn_extension_id = extn.trxn_extension_id;
1250
1251 c_customer cur_typ;
1252
1253 /* Bug 3393994 Declared the variables used in the new exception handling part. */
1254
1255 c1 cur_typ ;
1256 c2 cur_typ;
1257 l_select varchar2(100) := NULL ;
1258
1259 /* Bug 3472744 */
1260
1261 l_trx_select_statement VARCHAR2(5000) :=NULL;
1262 l_suffixe_select_statement VARCHAR2(4000) :=NULL;
1263 l_suffix_hz VARCHAR2(5000) :=NULL;
1264
1265 -- 1st break criteria
1266 l_receipt_method_id AR_RECEIPT_METHODS.receipt_method_id%TYPE;
1267 l_receipt_creation_rule_code AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE;
1268 l_lead_days AR_RECEIPT_METHODS.lead_days%TYPE;
1269 l_maturity_date_rule_code AR_RECEIPT_METHODS.maturity_date_rule_code%TYPE;
1270 l_br_min_acctd_amount AR_RECEIPT_METHODS.br_min_acctd_amount%TYPE;
1271 l_br_max_acctd_amount AR_RECEIPT_METHODS.br_max_acctd_amount%TYPE;
1272
1273 l_invoice_currency_code RA_CUSTOMER_TRX.invoice_currency_code%TYPE;
1274 l_exchange_rate RA_CUSTOMER_TRX.exchange_rate%TYPE;
1275 l_le_id RA_CUSTOMER_TRX.legal_entity_id%TYPE;
1276
1277 -- Bug 1808976
1278 l_customer_bank_account_id RA_CUSTOMER_TRX.customer_bank_account_id%TYPE; -- this will FOREVER be -999
1279 l_customer_bank_account_id2 RA_CUSTOMER_TRX.customer_bank_account_id%TYPE; -- this changes per customer
1280
1281 c_grouping cur_typ;
1282
1283 -- 2nd break criteria
1284 l_customer_id HZ_CUST_ACCOUNTS.cust_account_id%TYPE;
1285 l_customer_id2 HZ_CUST_ACCOUNTS.cust_account_id%TYPE;
1286 l_due_date AR_PAYMENT_SCHEDULES.due_date%TYPE;
1287 l_site_use_id AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE;
1288 l_customer_trx_id AR_PAYMENT_SCHEDULES.customer_trx_id%TYPE;
1289 l_payment_schedule_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE;
1290
1291 l_bill_id RA_CUSTOMER_TRX.customer_trx_id%TYPE;
1292 l_request_id NUMBER;
1293 -- 3922691
1294 l_print BOOLEAN := TRUE;
1295 l_print1 BOOLEAN := TRUE;
1296
1297 BEGIN
1298
1299 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
1300 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.select_trx_and_create_BR (+)');
1301 FND_FILE.PUT_LINE(FND_FILE.LOG,'Dump of Parameters');
1302 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------------------------------------------');
1303 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_due_date_low = ' || p_due_date_low);
1304 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_due_date_high = ' || p_due_date_high);
1305 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_date_low = ' || p_trx_date_low);
1306 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_date_high = ' || p_trx_date_high);
1307 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_type_id = ' || p_trx_type_id);
1308 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_number_low = ' || p_trx_number_low);
1309 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_number_high = ' || p_trx_number_high);
1310 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_cust_class = ' || p_cust_class);
1311 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_cust_category = ' || p_cust_category);
1312 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_customer_id = ' || p_customer_id);
1313 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_site_use_id = ' || p_site_use_id);
1314 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_batch_id = ' || p_batch_id);
1315 END IF;
1316
1317 --------------------------------------------------------------------------
1318 ---- FIRST LOOP
1319 --------------------------------------------------------------------------
1320 OPEN c_receipt_method;
1321 LOOP
1322
1323 /* Bug 1808976 : added l_customer_bank_account_id
1324 Bug 4109513 : process l_customer_bank_account_id later, we just want c_receipt_method
1325 to return distinct receipt methods per Currency and exchange rate
1326 */
1327
1328 FETCH c_receipt_method into l_receipt_method_id, l_receipt_creation_rule_code, l_lead_days,
1329 l_maturity_date_rule_code, l_br_min_acctd_amount, l_br_max_acctd_amount,
1330 l_invoice_currency_code, l_exchange_rate, l_le_id;
1331
1332 /* Bug 4109513 : the value -999 signals to construct_suffixe_select, that I don't care about
1333 bank accounts yet, I just want to pick up all distinct customers using l_receipt_method_id
1334 */
1335
1336 l_customer_bank_account_id := -999;
1337 EXIT WHEN c_receipt_method%NOTFOUND;
1338
1339 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
1340 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------------------------------------------');
1341 FND_FILE.PUT_LINE(FND_FILE.LOG,'Receipt Method Details');
1342 FND_FILE.PUT_LINE(FND_FILE.LOG,'receipt_method_id :'||l_receipt_method_id);
1343 FND_FILE.PUT_LINE(FND_FILE.LOG,'receipt_creation_rule_code:'||l_receipt_creation_rule_code);
1344 FND_FILE.PUT_LINE(FND_FILE.LOG,'lead days :'||l_lead_days);
1345 FND_FILE.PUT_LINE(FND_FILE.LOG,'maturity_date_rule_code :'||l_maturity_date_rule_code);
1346 FND_FILE.PUT_LINE(FND_FILE.LOG,'br_min_acctd_amount :'||l_br_min_acctd_amount);
1347 FND_FILE.PUT_LINE(FND_FILE.LOG,'br_max_acctd_amount :'||l_br_max_acctd_amount);
1348 FND_FILE.PUT_LINE(FND_FILE.LOG,'invoice_currency_code :'||l_invoice_currency_code);
1349 FND_FILE.PUT_LINE(FND_FILE.LOG,'exchange_rate :'||l_exchange_rate);
1350 FND_FILE.PUT_LINE(FND_FILE.LOG,'legal_entity_id :'||l_le_id);
1351 FND_FILE.PUT_LINE(FND_FILE.LOG,'customer_bank_account_id :'||l_customer_bank_account_id);
1352 END IF;
1353
1354 -- Setup of the next cursor according to the handled receipt method
1355 -- Bug 3922691 : pass additional parameters to construct_suffixe_select,
1356 -- call new procedure construct_hz
1357
1358 ARP_PROGRAM_GENERATE_BR.construct_suffixe_select(
1359 l_lead_days,
1360 l_suffixe_select_statement,
1361 p_due_date_low,
1362 p_due_date_high,
1363 p_trx_date_low,
1364 p_trx_date_high,
1365 p_trx_type_id,
1366 p_trx_number_low,
1367 p_trx_number_high,
1368 p_cust_class,
1369 p_cust_category,
1370 p_customer_id,
1371 p_site_use_id,
1372 l_le_id);
1373
1374 ARP_PROGRAM_GENERATE_BR.construct_hz(
1375 l_receipt_creation_rule_code,
1376 p_customer_id,
1377 l_suffix_hz);
1378
1379 l_suffixe_select_statement := l_suffixe_select_statement || l_suffix_hz;
1380
1381 /* 5051673 Need to verfiy if this is really required
1382 IF (p_cust_bank_branch_id IS NOT NULL) THEN
1383 l_suffixe_select_statement := l_suffixe_select_statement ||
1384 'AND account.bank_branch_id = '||p_cust_bank_branch_id||' ';
1385
1386
1387 l_suffixe_select_statement := l_suffixe_select_statement ||
1388 'AND NVL(account.inactive_date,batch.issue_date) >= batch.issue_date ';
1389 END IF;
1390 */
1391
1392 l_trx_select_statement := NULL;
1393
1394 -- Bug 4109513 : at this point we just want to get all distinct customer_ids that use the
1395 -- receipt method id picked up by c_receipt_method
1396 l_trx_select_statement := 'SELECT DISTINCT ps.customer_id '||
1397 l_suffixe_select_statement||
1398 ' ORDER BY ps.customer_id ';
1399
1400 if l_print1 AND (p_call <> 3 OR PG_DEBUG in ('Y', 'C')) THEN
1401 l_print1 := FALSE;
1402 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------------------------------------------') ;
1403 FND_FILE.PUT_LINE(FND_FILE.LOG,'This select will get all distinct customer_ids');
1404 FND_FILE.PUT_LINE(FND_FILE.LOG,l_trx_select_statement);
1405 end if;
1406
1407 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
1408 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------------------------------------------') ;
1409 FND_FILE.PUT_LINE(FND_FILE.LOG,'Opening c_customer with the following parameters :');
1410 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_receipt_method_id : ' || to_char(l_receipt_method_id));
1411 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_invoice_currency_code : ' || l_invoice_currency_code);
1412 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_exchange_rate : ' || to_char(l_exchange_rate));
1413 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_customer_bank_account_id : ' || to_char(l_customer_bank_account_id));
1414 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_le_id : ' || to_char(l_le_id));
1415 fnd_file.put_line(fnd_file.log,'l_trx_st:'||l_trx_select_statement);
1416
1417 END IF;
1418
1419 OPEN c_customer FOR l_trx_select_statement
1420 using p_due_date_low,
1421 p_due_date_high,
1422 p_trx_date_low,
1423 p_trx_date_high,
1424 p_trx_type_id,
1425 p_trx_number_low,
1426 p_trx_number_high,
1427 p_cust_class,
1428 p_cust_category,
1429 p_customer_id,
1430 p_site_use_id,
1431 l_receipt_method_id,
1432 p_batch_id,
1433 l_invoice_currency_code,
1434 l_exchange_rate,
1435 l_customer_bank_account_id, -- this will always be -999
1436 l_customer_bank_account_id,
1437 l_le_id,
1438 p_customer_id;
1439
1440 LOOP
1441
1442 l_customer_id := NULL;
1443
1444 FETCH c_customer into l_customer_id;
1445
1446 EXIT WHEN c_customer%NOTFOUND;
1447
1448 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
1449 FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------------------------------------');
1450 FND_FILE.PUT_LINE(FND_FILE.LOG,'...Processing c_customer, customer_id :'||l_customer_id);
1451 END IF;
1452
1453 -- 4109513 : Now that we have customer_id, rebuild l_trx_select_statement
1454 -- to make it more selective on ps.customer_id
1455 ARP_PROGRAM_GENERATE_BR.construct_suffixe_select(
1456 l_lead_days,
1457 l_suffixe_select_statement,
1458 p_due_date_low,
1459 p_due_date_high,
1460 p_trx_date_low,
1461 p_trx_date_high,
1462 p_trx_type_id,
1463 p_trx_number_low,
1464 p_trx_number_high,
1465 p_cust_class,
1466 p_cust_category,
1467 l_customer_id,
1468 p_site_use_id,
1469 l_le_id);
1470
1471 ARP_PROGRAM_GENERATE_BR.construct_hz(
1472 l_receipt_creation_rule_code,
1473 l_customer_id,
1474 l_suffix_hz);
1475
1476 l_suffixe_select_statement := l_suffixe_select_statement || l_suffix_hz;
1477
1478 IF (p_cust_bank_branch_id IS NOT NULL) THEN
1479 l_suffixe_select_statement := l_suffixe_select_statement ||
1480 'AND account.bank_branch_id = '||p_cust_bank_branch_id||' ';
1481
1482 l_suffixe_select_statement := l_suffixe_select_statement ||
1483 'AND NVL(account.inactive_date,batch.issue_date) >= batch.issue_date ';
1484 END IF;
1485
1486 l_trx_select_statement := NULL;
1487
1488 IF l_receipt_creation_rule_code = 'PER_CUSTOMER' THEN
1489 l_trx_select_statement := 'SELECT DISTINCT ps.customer_id '||
1490 l_suffixe_select_statement||
1491 ' ORDER BY ps.customer_id ';
1492 ELSIF l_receipt_creation_rule_code = 'PER_CUSTOMER_DUE_DATE' THEN
1493 l_trx_select_statement := 'SELECT DISTINCT ps.customer_id, ps.due_date '||
1494 l_suffixe_select_statement||
1495 ' ORDER BY ps.customer_id, ps.due_date ';
1496 ELSIF l_receipt_creation_rule_code = 'PER_SITE' THEN
1497 l_trx_select_statement := 'SELECT DISTINCT ps.customer_site_use_id '||
1498 l_suffixe_select_statement||
1499 ' ORDER BY ps.customer_site_use_id ';
1500 ELSIF l_receipt_creation_rule_code = 'PER_SITE_DUE_DATE' THEN
1501 l_trx_select_statement := 'SELECT DISTINCT ps.customer_site_use_id, ps.due_date '||
1502 l_suffixe_select_statement||
1503 ' ORDER BY ps.customer_site_use_id, ps.due_date ';
1504 ELSIF l_receipt_creation_rule_code = 'PER_INVOICE' THEN
1505 l_trx_select_statement := 'SELECT DISTINCT ps.customer_trx_id '||
1506 l_suffixe_select_statement||
1507 ' ORDER BY ps.customer_trx_id ';
1508 ELSIF l_receipt_creation_rule_code = 'PER_PAYMENT_SCHEDULE' THEN
1509 l_trx_select_statement := 'SELECT DISTINCT ps.payment_schedule_id '||
1510 l_suffixe_select_statement||
1511 ' ORDER BY ps.payment_schedule_id ';
1512 ELSE
1513 FND_MESSAGE.set_name('AR','AR_BR_INVALID_GROUPING_RULE');
1514 FND_MESSAGE.set_token('GROUPING_RULE',l_receipt_creation_rule_code);
1515 APP_EXCEPTION.raise_exception;
1516 END IF;
1517
1518 -- bug 3888842
1519 if l_print AND (p_call <> 3 OR PG_DEBUG in ('Y', 'C')) THEN
1520 l_print := FALSE;
1521 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------------------------------------------') ;
1522 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_trx_select_statement = ' || l_trx_select_statement);
1523 end if;
1524
1525 -- process receipt method id Banks
1526 OPEN c_rm_bank(l_receipt_method_id,l_customer_id);
1527
1528 LOOP
1529
1530 FETCH c_rm_bank into l_customer_bank_account_id2;
1531
1532 EXIT WHEN c_rm_bank%NOTFOUND;
1533
1534 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
1535 FND_FILE.PUT_LINE(FND_FILE.LOG,'......Processing c_rm_bank, customer_id : ' ||
1536 to_char(l_customer_id) || ' ' ||
1537 ' bank_account : ' || to_char(l_customer_bank_account_id2));
1538 END IF;
1539
1540 -- bug 1808976 : added l_customer_bank_account_id
1541
1542 OPEN c_grouping FOR l_trx_select_statement
1543 using p_due_date_low,
1544 p_due_date_high,
1545 p_trx_date_low,
1546 p_trx_date_high,
1547 p_trx_type_id,
1548 p_trx_number_low,
1549 p_trx_number_high,
1550 p_cust_class,
1551 p_cust_category,
1552 l_customer_id,
1553 p_site_use_id,
1554 l_receipt_method_id,
1555 p_batch_id,
1556 l_invoice_currency_code,
1557 l_exchange_rate,
1558 l_customer_bank_account_id2,
1559 l_customer_bank_account_id2,
1560 l_le_id,
1561 l_customer_id;
1562
1563 LOOP
1564
1565 /* Bug 3393994 Enclosing the following inside a block so as
1566 to handle the exception and still continue with the
1567 next Transaction in the loop. */
1568
1569 BEGIN
1570
1571 l_customer_id2 := NULL;
1572 l_due_date := NULL;
1573 l_site_use_id := NULL;
1574 l_customer_trx_id := NULL;
1575 l_payment_schedule_id := NULL;
1576
1577 IF (l_receipt_creation_rule_code = 'PER_CUSTOMER') THEN
1578 FETCH c_grouping into l_customer_id2;
1579 ELSIF (l_receipt_creation_rule_code = 'PER_CUSTOMER_DUE_DATE') THEN
1580 FETCH c_grouping into l_customer_id2, l_due_date;
1581 ELSIF (l_receipt_creation_rule_code = 'PER_SITE') THEN
1582 FETCH c_grouping into l_site_use_id;
1583 ELSIF (l_receipt_creation_rule_code = 'PER_SITE_DUE_DATE') THEN
1584 FETCH c_grouping into l_site_use_id, l_due_date;
1585 ELSIF (l_receipt_creation_rule_code = 'PER_INVOICE') THEN
1586 FETCH c_grouping into l_customer_trx_id;
1587 ELSE
1588 FETCH c_grouping into l_payment_schedule_id;
1589 END IF;
1590
1591 EXIT WHEN c_grouping%NOTFOUND;
1592
1593 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
1594 IF (l_receipt_creation_rule_code = 'PER_CUSTOMER') THEN
1595 FND_FILE.PUT_LINE(FND_FILE.LOG,'.........Processing c_grouping, customer_id :'||l_customer_id2);
1596 ELSIF (l_receipt_creation_rule_code = 'PER_CUSTOMER_DUE_DATE') THEN
1597 FND_FILE.PUT_LINE(FND_FILE.LOG,'.........Processing c_grouping, customer_id :'||
1598 l_customer_id2||' due date :'||l_due_date);
1599 ELSIF (l_receipt_creation_rule_code = 'PER_SITE') THEN
1600 FND_FILE.PUT_LINE(FND_FILE.LOG,'.........Processing c_grouping, site_use_id :'||l_site_use_id);
1601 ELSIF (l_receipt_creation_rule_code = 'PER_SITE_DUE_DATE') THEN
1602 FND_FILE.PUT_LINE(FND_FILE.LOG,'.........Processing c_grouping, site_use_id :'||l_site_use_id||
1603 ' due date :'||l_due_date);
1604 ELSIF (l_receipt_creation_rule_code = 'PER_INVOICE') THEN
1605 FND_FILE.PUT_LINE(FND_FILE.LOG,'.........Processing c_grouping, customer_trx_id :'||l_customer_trx_id);
1606 ELSE
1607 FND_FILE.PUT_LINE(FND_FILE.LOG,'.........Processing c_grouping, payment_schedule_id :'||
1608 l_payment_schedule_id);
1609 END IF;
1610 END IF;
1611
1612 -- Selection of the Credit/Debit Note with a payment Term of Immediate (term_id = 5)
1613 ARP_PROGRAM_GENERATE_BR.select_DM_and_CM_IMM(
1614 l_lead_days,
1615 l_receipt_creation_rule_code,
1616 p_due_date_low,
1617 p_due_date_high,
1618 p_trx_date_low,
1619 p_trx_date_high,
1620 p_trx_type_id,
1621 p_trx_number_low,
1622 p_trx_number_high,
1623 p_cust_class,
1624 p_cust_category,
1625 NVL(l_customer_id2,p_customer_id),
1626 NVL(l_site_use_id,p_site_use_id),
1627 l_receipt_method_id,
1628 p_batch_id,
1629 l_invoice_currency_code,
1630 l_exchange_rate,
1631 l_customer_bank_account_id2,
1632 l_le_id);
1633
1634 -- Selection of the "others" transactions
1635 ARP_PROGRAM_GENERATE_BR.select_trx_NIMM(
1636 l_lead_days,
1637 l_receipt_creation_rule_code,
1638 NVL(l_due_date,p_due_date_low),
1639 NVL(l_due_date,p_due_date_high),
1640 p_trx_date_low,
1641 p_trx_date_high,
1642 p_trx_type_id,
1643 p_trx_number_low,
1644 p_trx_number_high,
1645 p_cust_class,
1646 p_cust_category,
1647 NVL(l_customer_id2,p_customer_id),
1648 NVL(l_site_use_id,p_site_use_id),
1649 l_receipt_method_id,
1650 p_batch_id,
1651 l_invoice_currency_code,
1652 l_exchange_rate,
1653 l_payment_schedule_id,
1654 l_customer_trx_id,
1655 l_customer_bank_account_id2,
1656 l_le_id);
1657
1658
1659 -- Creation of the Bills receivable
1660
1661 -- bug 1808976 : added l_customer_bank_account_id
1662 ARP_PROGRAM_GENERATE_BR.create_BR(
1663 p_draft_mode,
1664 p_call,
1665 p_batch_id,
1666 l_receipt_method_id,
1667 l_receipt_creation_rule_code,
1668 l_maturity_date_rule_code,
1669 l_br_min_acctd_amount,
1670 l_br_max_acctd_amount,
1671 l_invoice_currency_code,
1672 l_customer_bank_account_id2,
1673 l_le_id,
1674 l_bill_id,
1675 l_request_id);
1676
1677 /* Bug 3393994 Exception handling inside the loop so that
1678 program can continue through the next pass thru the loop
1679 after printing messages in the log file.*/
1680
1681 EXCEPTION
1682 WHEN OTHERS THEN
1683 FND_FILE.PUT_LINE(FND_FILE.LOG,'Creation error for this BR ');
1684 g_num_br_failed := g_num_br_failed + 1;
1685
1686 BEGIN
1687
1688 l_select := 'SELECT payment_schedule_id , customer_trx_id , trx_number FROM ' || g_tmp_table_nimm ;
1689
1690 OPEN c1 FOR l_select ;
1691 LOOP
1692 FETCH c1 INTO errorinv(g_ctr);
1693 EXIT WHEN c1%NOTFOUND;
1694 g_ctr := g_ctr + 1;
1695 END LOOP ;
1696 CLOSE c1;
1697
1698 l_select := 'SELECT payment_schedule_id , customer_trx_id , trx_number FROM ' || g_tmp_table_imm;
1699
1700 OPEN c2 FOR l_select ;
1701 LOOP
1702 FETCH c2 INTO errorinv(g_ctr);
1703 EXIT WHEN c2%NOTFOUND;
1704 g_ctr := g_ctr + 1;
1705 END LOOP ;
1706 CLOSE c2;
1707
1708 EXCEPTION
1709 WHEN OTHERS THEN
1710 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception : While Inserting into the table errorinv');
1711 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
1712 END ;
1713
1714 END ;
1715
1716 END LOOP;
1717 CLOSE c_grouping;
1718
1719 END LOOP;
1720 CLOSE c_rm_bank;
1721
1722 END LOOP;
1723 CLOSE c_customer;
1724
1725 END LOOP;
1726 CLOSE c_receipt_method;
1727
1728
1729 FND_FILE.PUT_LINE(FND_FILE.LOG,'The process has generated '||g_num_br_created||' Bills receivable');
1730 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.select_trx_and_create_BR (-)');
1731
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.select_trx_and_create_BR');
1735
1736 IF c_grouping%ISOPEN THEN
1737 CLOSE c_grouping;
1738 END IF;
1739
1740 IF c_rm_bank%ISOPEN THEN
1741 CLOSE c_rm_bank;
1742 END IF;
1743
1744 IF c_customer%ISOPEN THEN
1745 CLOSE c_customer;
1746 END IF;
1747
1748 IF c_receipt_method%ISOPEN THEN
1749 CLOSE c_receipt_method;
1750 END IF;
1751
1752 RAISE;
1753
1754 END select_trx_and_create_BR;
1755
1756 /*===========================================================================+
1757 | PROCEDURE |
1758 | construct_hz |
1759 | |
1760 | Code to define conditions re. customer_id and customer_site_use_id |
1761 | This code was lifted from select_trx_and_Create_br, note same logic also |
1762 | existed in auto_create_br_api |
1763 +===========================================================================*/
1764
1765 PROCEDURE construct_hz(
1766 p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
1767 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
1768 p_suffix_hz OUT NOCOPY varchar2) IS
1769
1770 l_trx_select_statement VARCHAR2(4000) := NULL;
1771
1772 BEGIN
1773
1774 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'construct_hz (+)');
1775
1776 /*
1777 Bug 1710187 :
1778
1779 IF the grouping rule IS :
1780 a) PER_CUSTOMER or PER_CUSTOMER_DUE_DATE, there should exist a DRAWEE site
1781 for this customer ID and it should be active and primary
1782 b) All other grouping rules, DO NOT require the site to be PRIMARY, but BILL TO
1783 site must be DRAWEE site as well
1784
1785 */
1786
1787 l_trx_select_statement := l_trx_select_statement ||
1788 ' AND exists
1789 (SELECT a.cust_account_id
1790 FROM hz_cust_acct_sites a,
1791 hz_cust_site_uses site,
1792 hz_cust_account_roles acct_role
1793 WHERE a.cust_acct_site_id = site.cust_acct_site_id ';
1794
1795 if p_customer_id is NOT NULL then
1796 l_trx_select_statement := l_trx_select_statement ||
1797 'AND a.cust_account_id = :p_customer_id ';
1798 else
1799 l_trx_select_statement := l_trx_select_statement ||
1800 'AND :p_customer_id IS NULL
1801 AND a.cust_account_id = ps.customer_id ';
1802 end if;
1803
1804 IF p_receipt_creation_rule_code IN ('PER_CUSTOMER','PER_CUSTOMER_DUE_DATE') THEN
1805
1806 l_trx_select_statement := l_trx_select_statement ||
1807 ' AND site.primary_flag = ''Y'' ';
1808 ELSE
1809 l_trx_select_statement := l_trx_select_statement ||
1810 ' AND site.cust_acct_site_id IN (select cust_acct_site_id
1811 from hz_cust_site_uses
1812 WHERE site_use_id = ps.customer_site_use_id) ';
1813 END IF;
1814
1815 l_trx_select_statement := l_trx_select_statement ||
1816 ' AND site.site_use_code = ''DRAWEE''
1817 AND site.status = ''A''
1818 AND site.contact_id = acct_role.cust_account_role_id(+)
1819 AND acct_role.status(+) = ''A'') ';
1820
1821
1822 p_suffix_hz := l_trx_select_statement;
1823
1824 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'construct_hz (-)');
1825
1826 EXCEPTION
1827 WHEN OTHERS THEN
1828 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.construct_hz');
1829 RAISE;
1830 END;
1831
1832
1833 /*===========================================================================+
1834 | PROCEDURE |
1835 | construct_suffixe_select |
1836 | |
1837 | DESCRIPTION |
1838 | |
1839 | SCOPE - PUBLIC |
1840 | |
1841 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
1842 | |
1843 | ARGUMENTS : IN : |
1844 | |
1845 | RETURNS : NONE |
1846 | |
1847 | NOTES |
1848 | |
1849 | MODIFICATION HISTORY - Created by Mireille Flahaut - 27/07/2000 |
1850 | |
1851 | 30-APR-01 V Crisostomo Bug 1744783 : transactions with multiple |
1852 | payment schedule records, only get the first |
1853 | installment converted to BR, rest are un- |
1854 | processed |
1855 | 06-JUN-01 V Crisostomo Bug 1808976 : include condition to restrict |
1856 | on customer_bank_account_id |
1857 | 23-SEP-04 V Crisostomo Bug 3922691, added params so dynamic sql |
1858 | can be more selective |
1859 | 17-JAN-04 V Crisostomo Bug 4109513 : modify logic re. |
1860 | :p_customer_bank_account_id, subsequent code |
1861 | that uses construct_suffixe_select to build |
1862 | statement will now pass bank account twice |
1863 | due to new decode statement |
1864 | 11-MAY-05 V Crisostomo LE-R12: add p_le_id |
1865 | 21-Jan-06 Surendra Rajan Removed the references to ap_bank_accounts |
1866 +===========================================================================*/
1867
1868 PROCEDURE construct_suffixe_select(
1869 p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
1870 p_suffixe_select_statement OUT NOCOPY varchar2,
1871 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1872 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
1873 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
1874 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
1875 p_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
1876 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
1877 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
1878 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
1879 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
1880 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
1881 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
1882 p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE
1883 ) IS
1884
1885 l_trx_select_statement VARCHAR2(4000) := NULL;
1886
1887 BEGIN
1888
1889 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.construct_suffixe_select (+)');
1890
1891 /* modified for tca uptake */
1892
1893 /* Bug 1744783 : for transactions with multiple payment schedule records,
1894 BR is only created for the first installment, replaced ps.customer_trx_id
1895 with ps.payment_schedule_id, also did a direct comparison between
1896 br_ref_payment_schedule_id = ps.payment_schedule, rather than comparing
1897 to is not null */
1898
1899 /* Bug 1808976 : add a condition to restrict records based on p_customer_bank_account_id */
1900
1901 /* Bug 3922691 :
1902 - replace trx.trx_date with ps.trx_date
1903 - replace (ps.due_date - pm.lead_days) <= SYSDATE
1904 with ps.due_date <= SYSDATE + pm.lead_days
1905 - use NOT EXISTS in check against ra_customer_trx_lines
1906 - remove join to hz_cust_acct_sites, this is done in construct_hz
1907 */
1908 /* Bug 4928711 - Removed the references to ap_bank_accounts */
1909
1910 l_trx_select_statement :=
1911 'FROM ra_batches batch,
1912 ar_receipt_methods pm,
1913 ar_payment_schedules ps,
1914 ra_customer_trx trx,
1915 ra_cust_trx_types type,
1916 fnd_currencies_vl cur,
1917 hz_cust_accounts cust,
1918 hz_parties party,
1919 iby_trxn_extensions_v extn
1920 WHERE trx.customer_trx_id = ps.customer_trx_id
1921 ';
1922
1923 -- Bug 3922691 : evaluate each parameter and only add the condition if param is not null
1924
1925 if p_due_date_low is not null and p_due_date_high is not null then
1926 l_trx_select_statement := l_trx_select_statement ||
1927 'AND ps.due_date BETWEEN :p_due_date_low AND :p_due_date_high
1928 ';
1929 elsif p_due_date_low is not null and p_due_date_high is null then
1930 l_trx_select_statement := l_trx_select_statement ||
1931 'AND ps.due_date = :p_due_date_low
1932 AND :p_due_date_high IS NULL
1933 ';
1934 else
1935 l_trx_select_statement := l_trx_select_statement ||
1936 'AND :p_due_date_low is NULL
1937 AND :p_due_date_high IS NULL
1938 ';
1939 end if;
1940
1941 if p_trx_date_low is not null and p_trx_date_high is not null then
1942 l_trx_select_statement := l_trx_select_statement ||
1943 'AND trx.trx_date BETWEEN :p_trx_date_low AND :p_trx_date_high
1944 ';
1945 elsif p_trx_date_low is not null and p_trx_date_high is null then
1946 l_trx_select_statement := l_trx_select_statement ||
1947 'AND trx.trx_date = :p_trx_date_low
1948 AND :p_trx_date_high is NULL
1949 ';
1950 else
1951 l_trx_select_statement := l_trx_select_statement ||
1952 'AND :p_trx_date_low is NULL
1953 AND :p_trx_date_high is NULL
1954 ';
1955 end if;
1956
1957 if p_trx_type_id is not null then
1958 l_trx_select_statement := l_trx_select_statement ||
1959 'AND trx.cust_trx_type_id = :p_trx_type_id
1960 ';
1961 else
1962 l_trx_select_statement := l_trx_select_statement ||
1963 'AND :p_trx_type_id is NULL
1964 ';
1965 end if;
1966
1967 if p_trx_number_low is not null and p_trx_number_high is not null then
1968 l_trx_select_statement := l_trx_select_statement ||
1969 'AND trx.trx_number BETWEEN :p_trx_number_low AND :p_trx_number_high
1970 ';
1971 elsif p_trx_number_low is not null and p_trx_number_high is null then
1972 l_trx_select_statement := l_trx_select_statement ||
1973 'AND trx.trx_number = :p_trx_number_low
1974 AND :p_trx_number_high is NULL
1975 ';
1976 else
1977 l_trx_select_statement := l_trx_select_statement ||
1978 'AND :p_trx_number_low is NULL
1979 AND :p_trx_number_high is NULL
1980 ';
1981 end if;
1982
1983 if p_cust_class is not null then
1984 l_trx_select_statement := l_trx_select_statement ||
1985 'AND NVL(cust.customer_class_code,1) = :p_cust_class
1986 ';
1987 else
1988 l_trx_select_statement := l_trx_select_statement ||
1989 'AND :p_cust_class is null
1990 ';
1991 end if;
1992
1993 if p_cust_category is not null then
1994 l_trx_select_statement := l_trx_select_statement ||
1995 'AND NVL(party.category_code,1) = :p_cust_category
1996 ';
1997 else
1998 l_trx_select_statement := l_trx_select_statement ||
1999 'AND :p_cust_category is NULL
2000 ';
2001 end if;
2002
2003 if p_customer_id is not null then
2004 l_trx_select_statement := l_trx_select_statement ||
2005 'AND ps.customer_id = :p_customer_id
2006 ';
2007 else
2008 l_trx_select_statement := l_trx_select_statement ||
2009 'AND :p_customer_id is NULL
2010 ';
2011 end if;
2012
2013 if p_site_use_id is not null then
2014 l_trx_select_statement := l_trx_select_statement ||
2015 'AND ps.customer_site_use_id = :p_site_use_id
2016 ';
2017 else
2018 l_trx_select_statement := l_trx_select_statement ||
2019 'AND :p_site_use_id is NULL
2020 ';
2021 end if;
2022
2023 l_trx_select_statement := l_trx_select_statement ||
2024 'AND pm.receipt_method_id = trx.receipt_method_id
2025 AND pm.receipt_method_id = :p_receipt_method_id
2026 AND batch.batch_id = :p_batch_id
2027 AND ps.trx_date <= NVL(batch.issue_date,batch.batch_date)
2028 AND trx.customer_trx_id = ps.customer_trx_id
2029 AND ps.reserved_type IS NULL
2030 AND ps.reserved_value IS NULL
2031 AND ps.amount_in_dispute IS NULL
2032 AND ps.customer_id = cust.cust_account_id
2033 AND cust.party_id = party.party_id
2034 AND trx.cust_trx_type_id = type.cust_trx_type_id
2035 AND ps.invoice_currency_code = NVL(:p_currency_code,ps.invoice_currency_code)
2036 AND ps.invoice_currency_code = cur.currency_code
2037 AND NVL(ps.exchange_rate,100) = NVL(:p_exchange_rate,100)
2038 and trx.payment_trxn_extension_id = extn.trxn_extension_id(+)
2039 --AND nvl(trx.customer_bank_account_id, -1) =
2040 -- decode(:p_customer_bank_account_id,-999, nvl(trx.customer_bank_account_id,-1), nvl(:p_customer_bank_account_id,-1))
2041 --Bug5051673
2042 and nvl(extn.instrument_id,-1) = decode(:p_customer_bank_account_id,-999, nvl(extn.instrument_id,-1),nvl(:p_customer_bank_account_id,-1))
2043 AND NOT EXISTS
2044 (SELECT br_ref_payment_schedule_id
2045 from
2046 ra_customer_trx_lines br_lines,
2047 ar_transaction_history th
2048 where br_lines.br_ref_payment_schedule_id = ps.payment_schedule_id
2049 and br_lines.customer_trx_id = th.customer_trx_id
2050 and th.current_record_flag = ''Y''
2051 and th.status <> ''CANCELLED'') /*Bug2290332*/
2052 AND ps.status =''OP''
2053 AND cur.enabled_flag =''Y''
2054 AND cur.currency_flag =''Y''
2055 AND trx.legal_entity_id = :p_le_id ';
2056
2057 -- The lead days indicate the number of days before the invoice due date that a transaction
2058 -- payment schedule can be exchanged for a bill receivable. IF its value is 999, the lead days
2059 -- isn't used to select the trx.
2060 IF p_lead_days <> 999 THEN
2061 l_trx_select_statement := l_trx_select_statement ||'
2062 AND ps.due_date <= SYSDATE + pm.lead_days';
2063 END IF;
2064
2065 p_suffixe_select_statement := l_trx_select_statement;
2066
2067 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.construct_suffixe_select (-)');
2068
2069 EXCEPTION
2070 WHEN OTHERS THEN
2071 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.construct_suffixe_select');
2072 RAISE;
2073
2074 END construct_suffixe_select;
2075
2076
2077 /*===========================================================================+
2078 | PROCEDURE |
2079 | select_DM_and_CM_IMM |
2080 | |
2081 | DESCRIPTION Selection of the Credit and debit notes with a payment term |
2082 | of 'Immediate' |
2083 | This is only run if grouping rule <> 'PER_INVOICE' |
2084 | |
2085 | SCOPE - PUBLIC |
2086 | |
2087 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
2088 | |
2089 | ARGUMENTS : IN : |
2090 | RETURNS : NONE |
2091 | |
2092 | NOTES |
2093 | |
2094 | MODIFICATION HISTORY - Created by Mireille Flahaut - 01/08/2000 |
2095 | |
2096 | 06-jun-01 VCRISOST Bug 1808976 : added parameter |
2097 | p_customer_bank_account_id |
2098 | 23-SEP-04 VCRISOST Bug 3922691 : need to rebuild select with |
2099 | current value of params, no need to pass |
2100 | p_suffixe_select_statement, instead pass |
2101 | p_lead_days |
2102 | Need to call construct* procedures |
2103 | 11-MAY-05 VCRISOST LE-R12: add p_le_id |
2104 | 25-MAY-05 VCRISOST SSA-R12: add p_org_id |
2105 +===========================================================================*/
2106 PROCEDURE select_DM_and_CM_IMM(
2107 p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
2108 p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
2109 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
2110 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
2111 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
2112 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
2113 p_trx_type_id IN ra_cust_trx_types.cust_trx_type_id%TYPE,
2114 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
2115 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
2116 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
2117 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
2118 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
2119 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
2120 p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
2121 p_batch_id IN RA_BATCHES.batch_id%TYPE,
2122 p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
2123 p_exchange_rate IN RA_CUSTOMER_TRX.exchange_rate%TYPE,
2124 p_customer_bank_account_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE,
2125 p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE) IS
2126
2127 l_trx_select_statement VARCHAR2(5000) :=NULL;
2128 l_suffixe_select_statement VARCHAR2(5000) :=NULL;
2129 l_suffix_hz VARCHAR2(5000) := NULL;
2130
2131 l_delete_statement VARCHAR2(2000);
2132 l_insert_statement VARCHAR2(2000);
2133
2134 -- bug 3930958
2135 l_aimm_statement VARCHAR2(100) := 'SELECT COUNT(*) FROM '|| g_tmp_table_aimm;
2136 l_aimm_ctr NUMBER;
2137
2138 c_trx cur_typ;
2139
2140 l_payment_schedule_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE;
2141 l_customer_trx_id AR_PAYMENT_SCHEDULES.customer_trx_id%TYPE;
2142 l_cust_trx_type_id AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE;
2143 l_customer_id AR_PAYMENT_SCHEDULES.customer_id%TYPE;
2144 l_customer_site_use_id AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE;
2145 l_trx_number AR_PAYMENT_SCHEDULES.trx_number%TYPE;
2146 l_due_date AR_PAYMENT_SCHEDULES.due_date%TYPE;
2147 l_amount_due_remaining AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
2148 l_org_id AR_PAYMENT_SCHEDULES.org_id%TYPE;
2149
2150 BEGIN
2151
2152 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.select_DM_and_CM_IMM (+)');
2153
2154 l_delete_statement := 'DELETE FROM '|| g_tmp_table_imm;
2155 execute immediate l_delete_statement;
2156
2157 IF p_receipt_creation_rule_code = 'PER_INVOICE' THEN
2158 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'grouping rule PER INVOICE -> no DM/CM Immediate');
2159 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.select_DM_and_CM_IMM (-)');
2160 RETURN;
2161 END IF;
2162
2163 -- If a Credit/Debit Note has been created with a payment term of immediate,
2164 -- it will be included in the first BR that can accomodate its amount irrespective of the due date
2165 -- component of the following BR payment method 's Grouping Rules
2166 -- ONE PER CUSTOMER AND DUE DATE - will effectively become ONE PER CUSTOMER
2167 -- ONE PER SITE AND DUE DATE - will effectively become ONE PER SITE
2168 -- ONE PER PAYMENT SCHEDULE - will effectively become ONE PER SITE
2169
2170 -- Bug 3922691, we cannot re-use p_suffixe_select_statement because the
2171 -- param values for p_due_date_low, p_due_date_high, p_customer_id,
2172 -- p_site_use_id may have changed, need to reconstruct with current values
2173
2174 ARP_PROGRAM_GENERATE_BR.construct_suffixe_select(
2175 p_lead_days,
2176 l_suffixe_select_statement,
2177 p_due_date_low,
2178 p_due_date_high,
2179 p_trx_date_low,
2180 p_trx_date_high,
2181 p_trx_type_id,
2182 p_trx_number_low,
2183 p_trx_number_high,
2184 p_cust_class,
2185 p_cust_category,
2186 p_customer_id,
2187 p_site_use_id,
2188 p_le_id );
2189
2190 ARP_PROGRAM_GENERATE_BR.construct_hz(
2191 p_receipt_creation_rule_code,
2192 p_customer_id,
2193 l_suffix_hz);
2194
2195
2196 l_suffixe_select_statement := l_suffixe_select_statement || l_suffix_hz;
2197
2198 -- Setup of the next cursor according to the handled receipt method
2199 -- Bug 3930958 : CM's term_id is always null, re-write condition
2200 l_trx_select_statement := l_suffixe_select_statement ||
2201 'AND ((type.type = ''CM'' and ps.term_id is null) OR (type.type = ''DM'' and ps.term_id = 5)) ';
2202
2203 -- SSA-R12 : add org_id
2204 l_trx_select_statement := 'SELECT ps.payment_schedule_id,ps.customer_trx_id,ps.cust_trx_type_id,
2205 ps.customer_id,ps.customer_site_use_id,ps.trx_number,ps.due_date,
2206 ps.amount_due_remaining, ps.org_id '||l_trx_select_statement;
2207
2208 /*
2209 FND_FILE.PUT_LINE(FND_FILE.LOG,'DEBUG TOOL 3IMM');
2210 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------------------------------------------');
2211 FND_FILE.PUT_LINE(FND_FILE.LOG,'select_trx_nimm: l_trx_select_statement = ' || l_trx_select_statement);
2212
2213 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_due_date_low = ' || p_due_date_low);
2214 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_due_date_high = ' || p_due_date_high);
2215 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_date_low = ' || p_trx_date_low);
2216 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_date_high = ' || p_trx_date_high);
2217 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_type_id = ' || p_trx_type_id);
2218 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_number_low = ' || p_trx_number_low);
2219 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_number_high = ' || p_trx_number_high);
2220 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_cust_class = ' || p_cust_class);
2221 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_cust_category = ' || p_cust_category);
2222 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_customer_id = ' || p_customer_id);
2223 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_site_use_id = ' || p_site_use_id);
2224 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_receipt_method_id = ' || p_receipt_method_id);
2225 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_batch_id = ' || p_batch_id);
2226 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_invoice_currency_code = ' || p_invoice_currency_code);
2227 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_exchange_rate = ' || p_exchange_rate);
2228 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_customer_bank_account_id = ' || p_customer_bank_account_id);
2229 */
2230
2231 OPEN c_trx FOR l_trx_select_statement
2232 using p_due_date_low,
2233 p_due_date_high,
2234 p_trx_date_low,
2235 p_trx_date_high,
2236 p_trx_type_id,
2237 p_trx_number_low,
2238 p_trx_number_high,
2239 p_cust_class,
2240 p_cust_category,
2241 p_customer_id,
2242 p_site_use_id,
2243 p_receipt_method_id,
2244 p_batch_id,
2245 p_invoice_currency_code,
2246 p_exchange_rate,
2247 p_customer_bank_account_id,
2248 p_customer_bank_account_id,
2249 p_le_id,
2250 p_customer_id;
2251
2252 -- SSA-R12 : add org_id
2253 l_insert_statement := 'INSERT INTO '||
2254 g_tmp_table_imm ||
2255 ' (payment_schedule_id, customer_trx_id, cust_trx_type_id, ' ||
2256 'customer_id, customer_site_use_id, trx_number, ' ||
2257 'due_date, amount_due_remaining, amount_assigned, exclude_flag, org_id) '||
2258 'VALUES (:payment_schedule_id, :customer_trx_id, :cust_trx_type_id, ' ||
2259 ':customer_id, :customer_site_use_id, :trx_number, ' ||
2260 ':due_date, :amount_due_remaining, NULL, NULL,:org_id) ';
2261
2262 -- Insert INTO the table g_tmp_table_imm of the CM/DM with payment term of Immediate
2263 LOOP
2264
2265 FETCH c_trx into l_payment_schedule_id,
2266 l_customer_trx_id,
2267 l_cust_trx_type_id,
2268 l_customer_id,
2269 l_customer_site_use_id,
2270 l_trx_number,
2271 l_due_date,
2272 l_amount_due_remaining,
2273 l_org_id;
2274
2275 EXIT WHEN c_trx%NOTFOUND;
2276
2277 execute immediate l_insert_statement
2278 USING l_payment_schedule_id,
2279 l_customer_trx_id,
2280 l_cust_trx_type_id,
2281 l_customer_id,
2282 l_customer_site_use_id,
2283 l_trx_number,
2284 l_due_date,
2285 l_amount_due_remaining,
2286 l_org_id;
2287 /*
2288 FND_FILE.PUT_LINE(FND_FILE.LOG,'select_DM_and_CM_IMM:'||l_payment_schedule_id||' '||
2289 l_customer_trx_id||' '||l_trx_number|| ' '||l_customer_site_use_id||' '||
2290 l_due_date||' '||l_amount_due_remaining);
2291 */
2292
2293 END LOOP;
2294 CLOSE c_trx;
2295
2296 -- Bug 3930958 : exclude immediate transactions that have already been previously assigned
2297
2298 execute immediate l_aimm_statement INTO l_aimm_ctr;
2299 IF l_aimm_ctr > 0 then
2300
2301 l_delete_statement := 'DELETE FROM '|| g_tmp_table_imm ||
2302 ' WHERE payment_schedule_id in
2303 (select payment_schedule_id
2304 from ' || g_tmp_table_aimm || ')';
2305
2306 execute immediate l_delete_statement;
2307
2308 END IF;
2309
2310 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.select_DM_and_CM_IMM (-)');
2311
2312 EXCEPTION
2313 WHEN OTHERS THEN
2314 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.select_DM_and_CM_IMM');
2315
2316 IF c_trx%ISOPEN THEN
2317 CLOSE c_trx;
2318 END IF;
2319
2320 RAISE;
2321
2322 END select_DM_and_CM_IMM;
2323
2324
2325 /*===========================================================================+
2326 | PROCEDURE |
2327 | select_trx_NIMM |
2328 | |
2329 | DESCRIPTION Selection of the other transactions (I mean INV, DEP and CB) |
2330 | and the CM/DM with a payment term of 'Non Immediate' |
2331 | |
2332 | SCOPE - PUBLIC |
2333 | |
2334 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
2335 | |
2336 | ARGUMENTS : IN : |
2337 | |
2338 | RETURNS : NONE |
2339 | |
2340 | NOTES |
2341 | |
2342 | MODIFICATION HISTORY - Created by Mireille Flahaut - 01/08/2000 |
2343 | |
2344 | 06-Jun-01 VCRISOST Bug 1808976 : added parameter |
2345 | p_customer_bank_account_id |
2346 | 23-SEP-04 VCRISOST Bug 3922691 : need to rebuild select with |
2347 | current value of params, no need to pass |
2348 | p_suffixe_select_statement, instead pass |
2349 | p_lead_days |
2350 | Need to call construct* procedures |
2351 | 11-MAY-05 VCRISOST LE-R12: add p_le_id |
2352 | 25-MAY-05 VCRISOST SSA-R12: add p_org_id |
2353 +===========================================================================*/
2354 PROCEDURE select_trx_NIMM(
2355 p_lead_days IN AR_RECEIPT_METHODS.lead_days%TYPE,
2356 p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
2357 p_due_date_low IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
2358 p_due_date_high IN AR_PAYMENT_SCHEDULES.due_date%TYPE,
2359 p_trx_date_low IN RA_CUSTOMER_TRX.trx_date%TYPE,
2360 p_trx_date_high IN RA_CUSTOMER_TRX.trx_date%TYPE,
2361 p_trx_type_id IN RA_CUST_TRX_TYPES.cust_trx_type_id%TYPE,
2362 p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
2363 p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
2364 p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
2365 p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
2366 p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
2367 p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
2368 p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
2369 p_batch_id IN RA_BATCHES.batch_id%TYPE,
2370 p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
2371 p_exchange_rate IN RA_CUSTOMER_TRX.exchange_rate%TYPE,
2372 p_payment_schedule_id IN AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE,
2373 p_customer_trx_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
2374 p_customer_bank_account_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE,
2375 p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE) IS
2376
2377 l_trx_select_statement VARCHAR2(5000) :=NULL;
2378 l_suffixe_select_statement VARCHAR2(5000) :=NULL;
2379 l_suffix_hz VARCHAR2(5000) := NULL;
2380
2381 l_delete_statement VARCHAR2(50);
2382 l_insert_statement VARCHAR2(2000);
2383
2384 c_trx cur_typ;
2385
2386 l_payment_schedule_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE;
2387 l_customer_trx_id AR_PAYMENT_SCHEDULES.customer_trx_id%TYPE;
2388 l_cust_trx_type_id AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE;
2389 l_customer_id AR_PAYMENT_SCHEDULES.customer_id%TYPE;
2390 l_customer_site_use_id AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE;
2391 l_trx_number AR_PAYMENT_SCHEDULES.trx_number%TYPE;
2392 l_due_date AR_PAYMENT_SCHEDULES.due_date%TYPE;
2393 l_amount_due_remaining AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
2394 l_org_id AR_PAYMENT_SCHEDULES.org_id%TYPE;
2395
2396 BEGIN
2397
2398 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.select_trx_NIMM (+)');
2399
2400 l_delete_statement := 'DELETE FROM '|| g_tmp_table_nimm;
2401 execute immediate l_delete_statement;
2402
2403
2404 -- Bug 3922691, we cannot re-use p_suffixe_select_statement because the
2405 -- param values for p_due_date_low, p_due_date_high, p_customer_id,
2406 -- p_site_use_id may have changed, need to reconstruct with current values
2407
2408 ARP_PROGRAM_GENERATE_BR.construct_suffixe_select(
2409 p_lead_days,
2410 l_suffixe_select_statement,
2411 p_due_date_low,
2412 p_due_date_high,
2413 p_trx_date_low,
2414 p_trx_date_high,
2415 p_trx_type_id,
2416 p_trx_number_low,
2417 p_trx_number_high,
2418 p_cust_class,
2419 p_cust_category,
2420 p_customer_id,
2421 p_site_use_id,
2422 p_le_id );
2423
2424 ARP_PROGRAM_GENERATE_BR.construct_hz(
2425 p_receipt_creation_rule_code,
2426 p_customer_id,
2427 l_suffix_hz);
2428
2429
2430 l_suffixe_select_statement := l_suffixe_select_statement || l_suffix_hz;
2431
2432 -- Setup of the next cursor according to the handled receipt method
2433 -- Bug 3930958 : since CM is always immediate, it would have been picked up in
2434 -- select_DM_and_CM_IMM, no need to pick it again here
2435 l_trx_select_statement := l_suffixe_select_statement ||'AND (type.type IN (''INV'',''DEP'',''CB'') '||
2436 'OR (type.type = ''DM'' AND ps.term_id <> 5)) ';
2437
2438 if p_payment_schedule_id is not null then
2439 l_trx_select_statement := l_trx_select_statement ||
2440 'AND ps.payment_schedule_id = :p_payment_schedule_id
2441 ';
2442 else
2443 l_trx_select_statement := l_trx_select_statement ||
2444 'AND :p_payment_schedule_id is null
2445 ';
2446 end if;
2447
2448 if p_customer_trx_id is not null then
2449 l_trx_select_statement := l_trx_select_statement ||
2450 'AND ps.customer_trx_id = :p_customer_trx_id
2451 ';
2452 else
2453 l_trx_select_statement := l_trx_select_statement ||
2454 'AND :p_customer_trx_id is null
2455 ';
2456 end if;
2457
2458 l_trx_select_statement := 'SELECT ps.payment_schedule_id,ps.customer_trx_id,ps.cust_trx_type_id,
2459 ps.customer_id,ps.customer_site_use_id,ps.trx_number,ps.due_date,
2460 ps.amount_due_remaining, ps.org_id '||l_trx_select_statement;
2461
2462 /*
2463 FND_FILE.PUT_LINE(FND_FILE.LOG,'DEBUG TOOL 3');
2464 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------------------------------------------');
2465 FND_FILE.PUT_LINE(FND_FILE.LOG,'select_trx_nimm: l_trx_select_statement = ' || l_trx_select_statement);
2466
2467 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_due_date_low = ' || p_due_date_low);
2468 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_due_date_high = ' || p_due_date_high);
2469 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_date_low = ' || p_trx_date_low);
2470 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_date_high = ' || p_trx_date_high);
2471 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_type_id = ' || p_trx_type_id);
2472 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_number_low = ' || p_trx_number_low);
2473 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_trx_number_high = ' || p_trx_number_high);
2474 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_cust_class = ' || p_cust_class);
2475 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_cust_category = ' || p_cust_category);
2476 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_customer_id = ' || p_customer_id);
2477 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_site_use_id = ' || p_site_use_id);
2478 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_receipt_method_id = ' || p_receipt_method_id);
2479 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_batch_id = ' || p_batch_id);
2480 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_invoice_currency_code = ' || p_invoice_currency_code);
2481 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_exchange_rate = ' || p_exchange_rate);
2482 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_customer_bank_account_id = ' || p_customer_bank_account_id);
2483 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_payment_schedule_id = ' || p_payment_schedule_id);
2484 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_customer_trx_id = ' || p_customer_trx_id);
2485 */
2486
2487 OPEN c_trx FOR l_trx_select_statement
2488 using p_due_date_low,
2489 p_due_date_high,
2490 p_trx_date_low,
2491 p_trx_date_high,
2492 p_trx_type_id,
2493 p_trx_number_low,
2494 p_trx_number_high,
2495 p_cust_class,
2496 p_cust_category,
2497 p_customer_id,
2498 p_site_use_id,
2499 p_receipt_method_id,
2500 p_batch_id,
2501 p_invoice_currency_code,
2502 p_exchange_rate,
2503 p_customer_bank_account_id,
2504 p_customer_bank_account_id,
2505 p_le_id,
2506 p_customer_id,
2507 p_payment_schedule_id,
2508 p_customer_trx_id;
2509
2510 l_insert_statement := 'INSERT INTO '|| g_tmp_table_nimm ||
2511 '(payment_schedule_id,customer_trx_id,cust_trx_type_id,customer_id,customer_site_use_id,trx_number,due_date,' ||
2512 ' amount_due_remaining,amount_assigned,exclude_flag, org_id) '||
2513 'VALUES (:payment_schedule_id,:customer_trx_id,:cust_trx_type_id,:customer_id,:customer_site_use_id,:trx_number,:due_date,' ||
2514 ' :amount_due_remaining,NULL,NULL,:org_id) ';
2515
2516
2517 -- Insert INTO the table g_tmp_table_nimm of the transactions (<> od CM and DM) and the CM and DM
2518 -- with payment term of Non Immediate
2519 LOOP
2520
2521 FETCH c_trx into l_payment_schedule_id,
2522 l_customer_trx_id,
2523 l_cust_trx_type_id,
2524 l_customer_id,
2525 l_customer_site_use_id,
2526 l_trx_number,
2527 l_due_date,
2528 l_amount_due_remaining,
2529 l_org_id;
2530
2531 EXIT WHEN c_trx%NOTFOUND;
2532
2533 execute immediate l_insert_statement
2534 USING l_payment_schedule_id,
2535 l_customer_trx_id,
2536 l_cust_trx_type_id,
2537 l_customer_id,
2538 l_customer_site_use_id,
2539 l_trx_number,
2540 l_due_date,
2541 l_amount_due_remaining,
2542 l_org_id;
2543 /*
2544 FND_FILE.PUT_LINE(FND_FILE.LOG,'select_trx_NIMM:'||l_payment_schedule_id||' '||l_customer_trx_id||' '||l_trx_number||
2545 ' '||l_customer_site_use_id||' '||l_due_date||' '||l_amount_due_remaining);
2546 */
2547
2548 END LOOP;
2549 CLOSE c_trx;
2550
2551 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.select_trx_NIMM (-)');
2552
2553 EXCEPTION
2554 WHEN OTHERS THEN
2555 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.select_trx_NIMM');
2556
2557 IF c_trx%ISOPEN THEN
2558 CLOSE c_trx;
2559 END IF;
2560
2561 RAISE;
2562
2563 END select_trx_NIMM;
2564
2565
2566
2567 /*===========================================================================+
2568 | PROCEDURE |
2569 | create_BR |
2570 | |
2571 | DESCRIPTION |
2572 | |
2573 | SCOPE - PUBLIC |
2574 | |
2575 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
2576 | |
2577 | ARGUMENTS : IN : |
2578 | p_call : 1 run from the Automatic batch window |
2579 | 2 run from SRS |
2580 | 3 run from the transaction workbench |
2581 | |
2582 | RETURNS : NONE |
2583 | |
2584 | NOTES |
2585 | |
2586 | MODIFICATION HISTORY - Created by Mireille Flahaut - 31/07/2000 |
2587 | |
2588 | 06-Jun-01 VCRISOST Bug 1808976 : added parameter |
2589 p_customer_bank_account_id |
2590 | |
2591 | 06-Mar-03 Sahana Bug2686697: BR batch Creation program was failing |
2592 | if the invoice had multiple terms and the payment |
2593 | method used rule 'One per Invoice'. Made changes in |
2594 | Create_BR to handle condition. |
2595 | |
2596 | 18-Apr-03 Sahana Bug2866665: Inherit Transaction No does not work for|
2597 | cases where the grouping rule is other then PER_INVOICE
2598 | or PER_PAYMENT_SCHEDULE.Re-wrote the logic which checks
2599 | for the one to one relationship. |
2600 | 09-NOV-04 VCRISOST Bug 4006714 : restructure logic to exclude trx when |
2601 | total of all trx to exchange does not fall with amt |
2602 | range of payment method |
2603 | 11-MAY-05 VCRISOST LE-R12:Pass p_le_id for create_br_header |
2604 | 25-MAY-05 VCRISOST SSA-R12: pass p_org_id |
2605 | 03-OCT-05 SGNAGARA PAYMENT UPTAKE: Added payment_trxn_extn_id. |
2606 +===========================================================================*/
2607 PROCEDURE create_BR(
2608 p_draft_mode IN VARCHAR2,
2609 p_call IN NUMBER,
2610 p_batch_id IN RA_BATCHES.batch_id%TYPE,
2611 p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
2612 p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
2613 p_maturity_date_rule_code IN AR_RECEIPT_METHODS.maturity_date_rule_code%TYPE,
2614 p_br_min_acctd_amount IN AR_RECEIPT_METHODS.br_min_acctd_amount%TYPE,
2615 p_br_max_acctd_amount IN AR_RECEIPT_METHODS.br_max_acctd_amount%TYPE,
2616 p_currency_code IN RA_BATCHES.currency_code%TYPE,
2617 p_customer_bank_account_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE DEFAULT NULL,
2618 p_le_id IN RA_CUSTOMER_TRX.customer_bank_account_id%TYPE,
2619 p_bill_id OUT NOCOPY RA_CUSTOMER_TRX.customer_trx_id%TYPE,
2620 p_request_id OUT NOCOPY NUMBER) IS
2621
2622
2623 c_trx cur_typ;
2624
2625 l_default_printing_option VARCHAR2(20);
2626
2627 l_return_status VARCHAR2(20);
2628 l_msg_count NUMBER;
2629 l_msg_data VARCHAR2(4000);
2630
2631 l_payment_schedule_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE;
2632
2633 l_customer_trx_id RA_CUSTOMER_TRX.customer_trx_id%TYPE;
2634 l_br_maturity_date RA_CUSTOMER_TRX.term_due_date%TYPE;
2635 l_cust_trx_type_id RA_CUSTOMER_TRX.cust_trx_type_id%TYPE;
2636 l_drawee_id RA_CUSTOMER_TRX.drawee_id%TYPE;
2637 l_drawee_site_use_id RA_CUSTOMER_TRX.drawee_site_use_id%TYPE;
2638 l_drawee_contact_id RA_CUSTOMER_TRX.drawee_contact_id%TYPE;
2639 l_drawee_bank_account_id RA_CUSTOMER_TRX.drawee_bank_account_id%TYPE;
2640 l_created_from RA_CUSTOMER_TRX.created_from%TYPE;
2641
2642 -- Bug 1808976
2643 l_site_id RA_CUSTOMER_TRX.bill_to_site_use_id%TYPE;
2644 l_bill_to_site_id RA_CUSTOMER_TRX.bill_to_site_use_id%TYPE;
2645
2646
2647 l_customer_trx_line_id RA_CUSTOMER_TRX_LINES.customer_trx_line_id%TYPE;
2648
2649 l_bill_id RA_CUSTOMER_TRX.customer_trx_id%TYPE;
2650 l_bill_number RA_CUSTOMER_TRX.trx_number%TYPE;
2651 l_bill_status AR_TRANSACTION_HISTORY.status%TYPE;
2652 l_request_id NUMBER;
2653
2654 l_batch_process_status RA_BATCHES.batch_process_status%TYPE;
2655 l_batch_source_id RA_BATCHES.batch_source_id%TYPE;
2656 l_gl_date RA_BATCHES.gl_date%TYPE;
2657 l_issue_date RA_BATCHES.issue_date%TYPE;
2658 l_maturity_date RA_BATCHES.maturity_date%TYPE;
2659 l_comments RA_BATCHES.comments%TYPE;
2660 l_special_instructions RA_BATCHES.special_instructions%TYPE;
2661 l_due_date_nimm RA_BATCHES.maturity_date%TYPE;
2662 l_due_date_imm RA_BATCHES.maturity_date%TYPE;
2663
2664 l_doc_sequence_id NUMBER;
2665 l_doc_sequence_value NUMBER;
2666 l_old_trx_number VARCHAR2(20);
2667
2668 l_table_name VARCHAR2(50);
2669 l_statement VARCHAR2(1000);
2670 l_update_statement VARCHAR2(1000);
2671 l_delete_statement VARCHAR2(1000);
2672
2673 l_trx_nimm_statement VARCHAR2(100) := 'SELECT COUNT(*) FROM '|| g_tmp_table_nimm ||
2674 ' WHERE amount_assigned IS NULL';
2675 l_nb_trx_nimm NUMBER;
2676
2677 l_sum_nimm_statement VARCHAR2(100) := 'SELECT SUM(amount_due_remaining) FROM '||
2678 g_tmp_table_nimm||' WHERE amount_assigned IS NULL';
2679 l_sum_imm_statement VARCHAR2(100) := 'SELECT SUM(amount_due_remaining) FROM '||
2680 g_tmp_table_imm ||' WHERE amount_assigned IS NULL';
2681 l_tot_nimm NUMBER;
2682 l_tot_imm NUMBER;
2683 l_br_amount NUMBER;
2684
2685 l_excluded_amount AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
2686 l_assigned_amount AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
2687
2688 l_tot_negative_count NUMBER;
2689 l_tot_positive_count NUMBER;
2690
2691 l_tot_rec_nimm NUMBER;
2692 l_excluded_rec_nimm NUMBER;
2693
2694 l_org_id NUMBER;
2695
2696 -- Bug 1710187 : define a variable to hold customer_site_use_id for transaction being exchanged for BR
2697 l_bill_to_id AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE;
2698
2699 -- Bug 1708420 : define variable to hold BR_INHERIT_INV_NUM_FLAG value
2700 l_br_inherit_inv_num_flag AR_RECEIPT_METHODS.BR_INHERIT_INV_NUM_FLAG%TYPE;
2701 l_trx_number RA_CUSTOMER_TRX.TRX_NUMBER%TYPE;
2702 l_count_trxid NUMBER;
2703 l_count_ps NUMBER;
2704 l_cust_trx_id RA_CUSTOMER_TRX.CUSTOMER_TRX_ID%TYPE;
2705
2706 -- Bug2290332: Check for Automatic Transaction Numbering
2707 CURSOR bs_details(p_batch_source_id IN number) is
2708 SELECT auto_trx_numbering_flag, name
2709 FROM ra_batch_sources
2710 WHERE batch_source_id = p_batch_source_id;
2711 rec_bs bs_details%ROWTYPE;
2712
2713 -- Bug 4006714 : define new variables
2714 l_cursor_nimm VARCHAR2(1000) := 'SELECT payment_schedule_id, due_date, ' ||
2715 'amount_due_remaining, nvl(exclude_flag,''N''), org_id FROM '|| g_tmp_table_nimm ||
2716 ' ORDER BY due_date DESC, amount_due_remaining DESC';
2717
2718 l_cursor_imm VARCHAR2(1000) := 'SELECT payment_schedule_id, due_date, ' ||
2719 'amount_due_remaining, nvl(exclude_flag,''N''), org_id FROM '|| g_tmp_table_imm ||
2720 ' ORDER BY due_date DESC, amount_due_remaining DESC';
2721 use_cursor_stmt VARCHAR2(1000);
2722 cursor_loop cur_typ;
2723 c_psid AR_PAYMENT_SCHEDULES.PAYMENT_SCHEDULE_ID%TYPE;
2724 c_due AR_PAYMENT_SCHEDULES.DUE_DATE%TYPE;
2725 c_adr AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING%TYPE;
2726 c_exc VARCHAR2(1);
2727 -- end 4006714
2728
2729 jnk1 NUMBER;
2730 jnk2 NUMBER;
2731
2732 --5051673
2733 l_ext_entity_tab IBY_FNDCPT_COMMON_PUB.Id_tbl_type;
2734 l_msg RA_INTERFACE_ERRORS.MESSAGE_TEXT%TYPE;
2735 l_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
2736 l_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
2737 l_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2738
2739 l_extension_id NUMBER default null;
2740
2741
2742 -- l_return_status VARCHAR2(100);
2743 -- l_msg_count NUMBER:=0;
2744 -- l_msg_data VARCHAR2(20000):= NULL;
2745
2746
2747 BEGIN
2748
2749 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
2750 program_debug(p_call,'ARP_PROGRAM_GENERATE_BR.create_BR (+)');
2751 END IF;
2752
2753 l_return_status := FND_API.G_RET_STS_SUCCESS;
2754
2755 SAVEPOINT create_BR_SVP;
2756
2757 -------------------------------------------------------------------------------
2758 -- FIRST LOOP
2759 -- are there some stored payment schedules not assigned to a BR ???
2760 -------------------------------------------------------------------------------
2761 LOOP /* process temporary table */
2762
2763 execute immediate l_trx_nimm_statement INTO l_nb_trx_nimm;
2764 EXIT WHEN l_nb_trx_nimm=0;
2765
2766 /* if the handled grouping rule is 'PER_INVOICE', one transaction must generate
2767 one BR; since only a transaction is stored in the temporary table in this case,
2768 it isn't necessary to try and exclude some payment schedules if the total amount
2769 isn't between the min and the max
2770 */
2771
2772 execute immediate l_sum_nimm_statement INTO l_tot_nimm;
2773 execute immediate l_sum_imm_statement INTO l_tot_imm;
2774 l_br_amount:= NVL(l_tot_nimm,0) + NVL(l_tot_imm,0);
2775
2776 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_br_amount = ' || to_char(l_br_amount));
2777 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'p_receipt_creation_rule_code = ' || p_receipt_creation_rule_code);
2778
2779 IF (p_receipt_creation_rule_code <> 'PER_INVOICE') THEN
2780
2781
2782 LOOP
2783 EXIT WHEN (l_br_amount BETWEEN p_br_min_acctd_amount AND p_br_max_acctd_amount);
2784
2785 /*---------------------------------------------------------------------------------------
2786 Bug 4006714 :
2787
2788 the total amount of transactions for exchange is not within BR amount range defined
2789 for BR creation payment method,
2790
2791 if total BR amount is less than minimum exclude negative amounts from NIMM then IMM
2792 if total BR amount is over the maximum than exclude positive amounts from NIMM then IMM
2793
2794 the order of excluding transactions is based on due_date DESC and
2795 amount_due_remaining DESC
2796 ---------------------------------------------------------------------------------------*/
2797
2798 -- process non-immediate first
2799 use_cursor_stmt := l_cursor_nimm;
2800 l_table_name := g_tmp_table_nimm;
2801
2802 LOOP -- first process non-immediate then immediate
2803
2804 EXIT WHEN l_table_name IS NULL;
2805 EXIT WHEN (l_br_amount BETWEEN p_br_min_acctd_amount AND p_br_max_acctd_amount);
2806
2807 OPEN cursor_loop FOR use_cursor_stmt;
2808
2809 LOOP -- to process all rows in l_table_name
2810
2811 EXIT WHEN cursor_loop%NOTFOUND;
2812 EXIT WHEN (l_br_amount BETWEEN p_br_min_acctd_amount AND p_br_max_acctd_amount);
2813
2814 FETCH cursor_loop into c_psid, c_due, c_adr, c_exc;
2815
2816 /* Bug 5917574 not to generate BR when total br amount is less than minimum amount
2817 of payment method thresh hold */
2818 /*
2819 IF l_br_amount < p_br_min_acctd_amount THEN
2820 -- total BR amount is less than MIN BR amount range, need to find
2821 -- negative amounts to exclude
2822 IF c_adr < 0 AND c_exc = 'N' THEN
2823
2824 l_update_statement := 'UPDATE '||l_table_name|| ' SET exclude_flag = ''Y'' WHERE '||
2825 ' payment_schedule_id = :c_psid';
2826 EXECUTE IMMEDIATE l_update_statement USING c_psid;
2827 l_br_amount := l_br_amount - c_adr;
2828
2829 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'excluding psid = ' || to_char(c_psid) ||
2830 -- ' amount = ' || to_char(c_adr) || ' l_br_amount now = ' || to_char(l_br_amount));
2831 END IF;
2832 */
2833 IF l_br_amount > p_br_max_acctd_amount THEN
2834 -- total BR amount is more than MAX BR amount range, need to find
2835 -- positive amounts to exclude
2836 IF c_adr > 0 AND c_exc = 'N' THEN
2837
2838 l_update_statement := 'UPDATE '||l_table_name|| ' SET exclude_flag = ''Y'' WHERE '||
2839 ' payment_schedule_id = :c_psid';
2840 EXECUTE IMMEDIATE l_update_statement USING c_psid;
2841 l_br_amount := l_br_amount - c_adr;
2842
2843 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'excluding psid = ' || to_char(c_psid) ||
2844 -- ' amount = ' || to_char(c_adr) || ' l_br_amount now = ' || to_char(l_br_amount));
2845
2846 END IF;
2847 END IF;
2848
2849 END LOOP;
2850
2851 -- set to process Immediate table
2852 IF (l_table_name = g_tmp_table_nimm) THEN
2853 l_table_name := g_tmp_table_imm;
2854 use_cursor_stmt := l_cursor_imm;
2855 ELSE
2856 l_table_name := NULL;
2857 END IF;
2858
2859 CLOSE cursor_loop;
2860 END LOOP;
2861
2862 -- no transaction exclusions are sufficient to bring BR into amount range
2863 IF (l_br_amount NOT BETWEEN p_br_min_acctd_amount AND p_br_max_acctd_amount) THEN
2864 EXIT;
2865 END IF;
2866
2867 END LOOP; /* validate amount is within range */
2868
2869 END IF; /* (p_receipt_creation_rule_code <> 'PER_INVOICE') */
2870
2871 /*------------------------------------------------------------
2872 BR cannot be created because total exceeds BR range amount
2873 ------------------------------------------------------------*/
2874 IF (l_br_amount NOT BETWEEN p_br_min_acctd_amount AND p_br_max_acctd_amount) THEN
2875 IF p_call = 3 THEN
2876 FND_MESSAGE.set_name('AR', 'AR_BR_AMOUNT_INCORRECT');
2877 APP_EXCEPTION.raise_exception;
2878 ELSE
2879 EXIT;
2880 END IF;
2881 END IF;
2882
2883 l_statement := 'SELECT COUNT(*) FROM '||g_tmp_table_nimm||' WHERE exclude_flag IS NOT NULL ';
2884 execute immediate l_statement into l_excluded_rec_nimm;
2885
2886
2887 l_statement := 'SELECT COUNT(*) FROM '||g_tmp_table_nimm;
2888 execute immediate l_statement into l_tot_rec_nimm;
2889
2890 /*-----------------------------------------------------------------------------------
2891 BR cannot be created because only transactions with IMMEDIATE term will be picked
2892 -----------------------------------------------------------------------------------*/
2893
2894 IF (l_excluded_rec_nimm > 0) AND (l_tot_rec_nimm = l_excluded_rec_nimm) THEN
2895 FND_MESSAGE.set_name('AR', 'AR_BR_ONLY_DR_CR');
2896 IF p_call = 3 THEN
2897 APP_EXCEPTION.raise_exception;
2898 ELSE
2899 program_debug(p_call,FND_MESSAGE.get);
2900 EXIT;
2901 END IF;
2902 END IF;
2903
2904 /*--------------------------
2905 Possible creation of BR
2906 --------------------------*/
2907
2908 l_statement := 'UPDATE '||g_tmp_table_nimm||
2909 ' SET amount_assigned=amount_due_remaining WHERE exclude_flag IS NULL ';
2910 execute immediate l_statement;
2911
2912 l_statement := 'UPDATE '||g_tmp_table_imm||
2913 ' SET amount_assigned=amount_due_remaining WHERE exclude_flag IS NULL';
2914 execute immediate l_statement;
2915
2916 IF (p_call <> 3) THEN
2917
2918 SELECT batch_source_id,
2919 batch_process_status,
2920 gl_date,
2921 issue_date,
2922 maturity_date,
2923 comments,
2924 special_instructions
2925 INTO l_batch_source_id,
2926 l_batch_process_status,
2927 l_gl_date,
2928 l_issue_date,
2929 l_maturity_date,
2930 l_comments,
2931 l_special_instructions
2932 FROM RA_BATCHES
2933 WHERE batch_id = p_batch_id;
2934
2935 ELSE
2936
2937 l_batch_source_id := fnd_profile.value('AR_BR_BATCH_SOURCE');
2938 l_batch_process_status := NULL;
2939 l_gl_date := SYSDATE;
2940 l_issue_date := SYSDATE;
2941 l_maturity_date := NULL;
2942 l_comments :=NULL;
2943 l_special_instructions := NULL;
2944
2945 END IF;
2946
2947 IF (l_batch_source_id IS NULL) THEN
2948 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
2949 program_debug(p_call, 'The Bill receivable can not be created because ' ||
2950 'the Batch_source_id is NULL ');
2951 END IF;
2952 IF p_call = 3 THEN
2953 FND_MESSAGE.set_name('AR','AR_NO_PROFILE_VALUE');
2954 FND_MESSAGE.set_token('PROFILE','AR: Bills Receivable Batch Source');
2955 APP_EXCEPTION.raise_exception;
2956 END IF;
2957 EXIT;
2958
2959 END IF;
2960
2961
2962 /* Start of Bug2290332 - Check if automatic numbering is enabled for batch source*/
2963 OPEN bs_details(l_batch_source_id);
2964 FETCH bs_details INTO rec_bs;
2965
2966 IF (rec_bs.auto_trx_numbering_flag = 'N') then
2967 fnd_message.set_name('AR','AR_BR_MANUAL_BATCH_SOURCE');
2968 fnd_message.set_token('BATCH_SOURCE_NAME',rec_bs.name);
2969 IF p_call = 3 THEN
2970 APP_EXCEPTION.raise_exception;
2971 ELSE
2972 program_debug(p_call,FND_MESSAGE.get);
2973 EXIT;
2974 END IF;
2975 END IF;
2976
2977 CLOSE bs_details;
2978 /* End of Bug2290332 - Check if automatic numbering is enabled for batch source*/
2979
2980
2981 -- set created_from
2982 IF (p_call = 1) THEN
2983 l_created_from := 'ARBRCBAT.fmx';
2984 ELSIF (p_call = 2) THEN
2985 l_created_from := 'FNDRSRUN.fmx';
2986 ELSIF (p_call = 3) THEN
2987 l_created_from := 'ARXTWMAI.fmx';
2988 ELSE
2989 g_field := 'p_call';
2990 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
2991 FND_MESSAGE.set_token('PROCEDURE','create_BR');
2992 FND_MESSAGE.set_token('PARAMETER', g_field);
2993 APP_EXCEPTION.raise_exception;
2994 END IF;
2995
2996 -- set BR Maturity date
2997 IF (l_maturity_date IS NULL) THEN
2998
2999 IF (p_maturity_date_rule_code = 'EARLIEST') THEN
3000
3001 l_statement := 'SELECT MIN(due_date) FROM '||g_tmp_table_nimm||' WHERE exclude_flag IS NULL';
3002 execute immediate l_statement INTO l_due_date_nimm;
3003
3004 -- Bug 3930958 : exclude CMs when picking trx to base maturity date from
3005 l_statement := 'SELECT MIN(due_date) FROM '||g_tmp_table_imm||' imm WHERE exclude_flag IS NULL
3006 and not exists (select ''x'' from ra_cust_trx_types t
3007 where t.cust_trx_type_id = imm.cust_trx_type_id
3008 and t.type = ''CM'') ';
3009 execute immediate l_statement INTO l_due_date_imm;
3010
3011 IF (l_due_date_imm IS NULL) THEN
3012 l_br_maturity_date := l_due_date_nimm;
3013 ELSIF (l_due_date_nimm < l_due_date_imm) THEN
3014 l_br_maturity_date := l_due_date_nimm;
3015 ELSE
3016 l_br_maturity_date := l_due_date_imm;
3017 END IF;
3018
3019 ELSIF (p_maturity_date_rule_code = 'LATEST') THEN
3020
3021 l_statement := 'SELECT MAX(due_date) FROM '||g_tmp_table_nimm||' WHERE exclude_flag IS NULL';
3022 execute immediate l_statement INTO l_due_date_nimm;
3023
3024 -- Bug 3930958 : exclude CMs when picking trx to base maturity date from
3025 l_statement := 'SELECT MAX(due_date) FROM '||g_tmp_table_imm||' imm WHERE exclude_flag IS NULL
3026 and not exists (select ''x'' from ra_cust_trx_types t
3027 where t.cust_trx_type_id = imm.cust_trx_type_id
3028 and t.type = ''CM'') ';
3029 execute immediate l_statement INTO l_due_date_imm;
3030
3031 IF (l_due_date_imm IS NULL) THEN
3032 l_br_maturity_date := l_due_date_nimm;
3033 ELSIF (l_due_date_nimm > l_due_date_imm) THEN
3034 l_br_maturity_date := l_due_date_nimm;
3035 ELSE
3036 l_br_maturity_date := l_due_date_imm;
3037 END IF;
3038
3039 ELSE
3040 FND_MESSAGE.set_name('AR','AR_BR_INVALID_MAT_DATE_RULE');
3041 FND_MESSAGE.set_token('MAT_DATE_RULE',p_maturity_date_rule_code);
3042 APP_EXCEPTION.raise_exception;
3043 END IF;
3044
3045 ELSE
3046 l_br_maturity_date := l_maturity_date;
3047 END IF;
3048
3049 /* -----------------------------------------------------------------------------------------
3050 4109513 : following sequence was originally in lower section of code,
3051 I have moved it up here so further selects are not run if BR won't be created anyway
3052
3053 A bill receivable must not be created when the maturity date is prior to the issue date
3054 -----------------------------------------------------------------------------------------*/
3055
3056 IF (TO_DATE(l_br_maturity_date,'DD/MM/RR') < TO_DATE(l_issue_date,'DD/MM/RR')) THEN
3057 FND_MESSAGE.set_name('AR','AR_BR_INCORRECT_MATURITY_DT');
3058 FND_MESSAGE.set_token('MATURITY_DT',l_br_maturity_date);
3059 FND_MESSAGE.set_token('ISSUE_DT',l_issue_date);
3060
3061 IF p_call = 3 THEN
3062 APP_EXCEPTION.raise_exception;
3063 ELSE
3064 program_debug(p_call,FND_MESSAGE.get);
3065 EXIT;
3066 END IF;
3067
3068 END IF;
3069
3070 -- BR Transaction type and BR inherit flag
3071 SELECT br_cust_trx_type_id,
3072 br_inherit_inv_num_flag
3073 INTO l_cust_trx_type_id,
3074 l_br_inherit_inv_num_flag
3075 FROM ar_receipt_methods
3076 WHERE receipt_method_id = p_receipt_method_id;
3077
3078 /* -------------------------------------------------------------------------
3079 Bug 1708420 : If receipt class was defined to "Inherit Transaction Number"
3080 then if there exists a one-to-one relationship between the AR transaction
3081 and the BR that is going to be generated, the BR should inherit the
3082 AR transaction's number
3083
3084 Bug2866665: Inherit Transaction No does not work for cases where the
3085 grouping rule is other then PER_INVOICE or PER_PAYMENT_SCHEDULE.Re-wrote
3086 the logic which checks for the one to one relationship.
3087
3088 -------------------------------------------------------------------------*/
3089
3090 -- Start of Bug2866665
3091 IF nvl(l_br_inherit_inv_num_flag,'N') = 'Y' THEN
3092
3093 l_statement := 'select count( distinct customer_trx_id ), max(customer_trx_id) from ' ||
3094 g_tmp_table_nimm;
3095 execute immediate l_statement INTO l_count_trxid, l_cust_trx_id;
3096
3097 IF l_count_trxid = 1 THEN
3098 l_statement := 'SELECT count(*), max(ps.trx_number) ' ||
3099 'from ar_payment_schedules ps ' ||
3100 'where ps.customer_trx_id = ' ||
3101 l_cust_trx_id ;
3102 execute immediate l_statement INTO l_count_ps, l_trx_number;
3103
3104 IF l_count_ps <> 1 AND
3105 p_receipt_creation_rule_code in ('PER_PAYMENT_SCHEDULE', 'PER_CUSTOMER_DUE_DATE',
3106 'PER_SITE_DUE_DATE') THEN
3107 l_trx_number := NULL;
3108 END IF;
3109 ELSE
3110 l_trx_number := NULL;
3111 END IF;
3112
3113 ELSE
3114
3115 l_trx_number := NULL; -- Inherit Trx No is N
3116
3117 END IF; -- nvl(l_br_inherit_inv_num_flag,'N') = 'Y'
3118
3119
3120 BEGIN
3121 IF l_trx_number is not null THEN
3122 ARP_TRX_VALIDATE.validate_trx_number(l_batch_source_id,l_trx_number,NULL);
3123 END IF;
3124 EXCEPTION
3125 WHEN OTHERS THEN
3126 l_trx_number := NULL;
3127 END;
3128
3129 -- End of Bug2866665
3130
3131 -- Drawee site information
3132 -- Whatever the handled grouping rule, only one customer is handled
3133
3134 l_statement := 'SELECT customer_id, customer_site_use_id, org_id from '||g_tmp_table_nimm||' WHERE ROWNUM < 2';
3135 execute immediate l_statement INTO l_drawee_id, l_bill_to_id, l_org_id;
3136
3137
3138 -- the BR drawee must be the primary and active DRAWEE site of the transaction customer.
3139 /* modified for tca uptake */
3140 /* Bug 1710187 : the grouping rule dictates whether or not we should check
3141 for site_uses.primary_flag = 'Y' */
3142
3143 /*
3144 DEVELOPER's NOTE (added by vcrisost 06/06/2001)
3145 -----------------------------------------------
3146 a limitation of TCA data model prevents recording a link between a bank account
3147 and a DRAWEE site. Currently the data model will always link Bank accounts to the
3148 BILL TO site only
3149
3150 the following 2 selects which try to find bank accounts for the DRAWEE site
3151 will always return a null l_drawee_bank_account_id
3152
3153 please see replacement code below tagged with Bug 1808976
3154
3155 if p_receipt_creation_rule_code in ('PER_CUSTOMER','PER_CUSTOMER_DUE_DATE') then
3156 SELECT site_uses.site_use_id,
3157 site_uses.contact_id,
3158 Null external_bank_account_id
3159 INTO l_drawee_site_use_id, l_drawee_contact_id, l_drawee_bank_account_id
3160 FROM hz_cust_accounts cust_acct,
3161 hz_cust_acct_sites acct_site,
3162 hz_cust_site_uses site_uses,
3163 hz_cust_account_roles acct_role
3164 WHERE cust_acct.cust_account_id = l_drawee_id
3165 AND cust_acct.cust_account_id = acct_site.cust_account_id
3166 AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
3167 AND site_uses.site_use_code = 'DRAWEE'
3168 AND site_uses.status ='A'
3169 AND site_uses.primary_flag = 'Y'
3170 AND site_uses.contact_id = acct_role.cust_account_role_id(+)
3171 AND acct_role.status(+) ='A';
3172 else
3173 -- Bug 1710187 : for other grouping rules, ensure that the BILL TO site of
3174 -- the transaction is ALSO a DRAWEE site
3175
3176 SELECT site_uses.site_use_id,
3177 site_uses.contact_id,
3178 Null external_bank_account_id
3179 INTO l_drawee_site_use_id, l_drawee_contact_id, l_drawee_bank_account_id
3180 FROM hz_cust_accounts cust_acct,
3181 hz_cust_acct_sites acct_site,
3182 hz_cust_site_uses site_uses,
3183 hz_cust_account_roles acct_role,
3184 hz_party_sites party_site
3185 WHERE cust_acct.cust_account_id = l_drawee_id
3186 AND cust_acct.cust_account_id = acct_site.cust_account_id
3187 AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
3188 AND site_uses.site_use_code = 'DRAWEE'
3189 AND site_uses.status = 'A'
3190 AND acct_site.party_site_id = party_site.party_site_id
3191 AND party_site.location_id =
3192 (select bloc.location_id
3193 FROM hz_cust_accounts bcust_acct,
3194 hz_cust_acct_sites bacct_site,
3195 hz_cust_site_uses bsite_uses,
3196 hz_party_sites bparty_site,
3197 hz_locations bloc
3198 WHERE bcust_acct.cust_account_id = l_drawee_id
3199 AND bcust_acct.cust_account_id = bacct_site.cust_account_id
3200 AND bacct_site.cust_acct_site_id = bsite_uses.cust_acct_site_id
3201 AND bsite_uses.site_use_code = 'BILL_TO'
3202 AND bsite_uses.site_use_id = l_bill_to_id
3203 AND bsite_uses.status = 'A'
3204 AND bacct_site.party_site_id = bparty_site.party_site_id
3205 AND bloc.location_id = bparty_site.location_id)
3206 AND site_uses.contact_id = acct_role.cust_account_role_id(+)
3207 AND acct_role.status(+) ='A';
3208
3209 end if;
3210
3211 */
3212
3213 /* bug 1808976 : replacement code for select statements commented out above
3214
3215 to define l_drawee_bank_account_id correctly, use the newly passed parameter
3216 p_customer_bank_account_id to check if the bank account of the AR transaction
3217 is also a bank account for the primary DRAWEE site
3218
3219 new logic is as follows :
3220
3221 Is the grouping rule : (a) one per customer or (b) one per customer per due date ?
3222 NO : use the AR transaction's bank account
3223 YES : is the AR transaction's bank account also defined as a bank account
3224 for the primary drawee site for this customer ?
3225 YES : use the AR transaction's bank account
3226 NO : use a NULL bank account
3227 */
3228
3229 if p_receipt_creation_rule_code in ('PER_CUSTOMER','PER_CUSTOMER_DUE_DATE') then
3230
3231 /* the AR transaction's bank account should also be defined as a bank account
3232 of the DRAWEE site, but since bank accounts cannot be linked to a DRAWEE site
3233 first make sure that this DRAWEE site is also a BILL TO site and then check
3234 that the bank account is defined for this BILL TO site
3235 */
3236
3237 -- get site/contact information pertaining to PRIMARY drawee site
3238
3239 SELECT site_uses.site_use_id, site_uses.contact_id, acct_site.cust_acct_site_id
3240 INTO l_drawee_site_use_id, l_drawee_contact_id, l_site_id
3241 FROM hz_cust_accounts cust_acct,
3242 hz_cust_acct_sites acct_site,
3243 hz_cust_site_uses site_uses,
3244 hz_cust_account_roles acct_role
3245 WHERE cust_acct.cust_account_id = l_drawee_id
3246 AND cust_acct.cust_account_id = acct_site.cust_account_id
3247 AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
3248 AND site_uses.site_use_code = 'DRAWEE'
3249 AND site_uses.status = 'A'
3250 AND site_uses.primary_flag = 'Y'
3251 AND site_uses.contact_id = acct_role.cust_account_role_id(+)
3252 AND acct_role.status(+) ='A';
3253
3254 -- get the BILL TO site id associated with l_site_id, because this is where
3255 -- the bank accounts are linked to
3256
3257 SELECT site_uses.site_use_id
3258 INTO l_bill_to_site_id
3259 FROM hz_cust_accounts cust_acct,
3260 hz_cust_acct_sites acct_site,
3261 hz_cust_site_uses site_uses
3262 WHERE cust_acct.cust_account_id = l_drawee_id
3263 AND cust_acct.cust_account_id = acct_site.cust_account_id
3264 AND acct_site.cust_acct_site_id = l_site_id
3265 AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
3266 AND site_uses.site_use_code = 'BILL_TO'
3267 AND site_uses.status = 'A';
3268
3269 -- if the following returns no rows it means that the AR's bank account
3270 -- IS NOT defined as a bank account for the PRIMARY drawee site and the
3271 -- BR should be created with no Drawee bank info
3272 /* 5051673 Need to verfiy this
3273 select instr_assignment_id
3274 into l_drawee_bank_account_id
3275 from IBY_FNDCPT_PAYER_ASSGN_INSTR_V instr
3276 where instr.acct_site_use_id = l_bill_to_site_id
3277 and instr.instr_assignment_id = p_customer_bank_account_id
3278 and nvl(instr.assignment_end_date,sysdate+1 ) > = sysdate;
3279 */
3280
3281
3282 /* Bug 4928711 - Removed the references of AP_BANK_ACCOUNT_USES
3283 BEGIN
3284 SELECT account.external_bank_account_id
3285 INTO l_drawee_bank_account_id
3286 FROM ap_bank_account_uses account
3287 WHERE account.customer_site_use_id = l_bill_to_site_id
3288 AND account.external_bank_account_id = p_customer_bank_account_id
3289 AND nvl(account.end_date, sysdate + 1) >= sysdate;
3290 EXCEPTION
3291 WHEN NO_DATA_FOUND THEN
3292 l_drawee_bank_account_id := NULL;
3293 END;
3294 */
3295
3296
3297
3298 /*
3299 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3300 program_debug(p_call,'l_drawee_site_use_id = ' || l_drawee_site_use_id);
3301 program_debug(p_call,'l_drawee_contact_id = ' || l_drawee_contact_id);
3302 program_debug(p_call,'l_site_id = ' || l_site_id);
3303 program_debug(p_call,'l_bill_to_site_id = ' || l_bill_to_site_id);
3304 program_debug(p_call,'l_drawee_bank_account_id = ' || l_drawee_bank_account_id);
3305 END IF;
3306 */
3307
3308 else
3309
3310 /* since all other grouping rules will look at the BILL TO site, and
3311 the AR transaction can only use the bank account if it was
3312 defined for this bill to, we can just automatically use the bank
3313 account from the AR transaction without much more validation */
3314
3315 SELECT site_uses.site_use_id,
3316 site_uses.contact_id,
3317 p_customer_bank_account_id
3318 INTO l_drawee_site_use_id, l_drawee_contact_id, l_drawee_bank_account_id
3319 FROM hz_cust_accounts cust_acct,
3320 hz_cust_acct_sites acct_site,
3321 hz_cust_site_uses site_uses,
3322 hz_cust_account_roles acct_role,
3323 hz_party_sites party_site
3324 WHERE cust_acct.cust_account_id = l_drawee_id
3325 AND cust_acct.cust_account_id = acct_site.cust_account_id
3326 AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
3327 AND site_uses.site_use_code = 'DRAWEE'
3328 AND site_uses.status = 'A'
3329 -- following conditions ensure that this DRAWEE site is also a BILL TO site
3330 AND acct_site.party_site_id = party_site.party_site_id
3331 AND party_site.location_id =
3332 (select bloc.location_id
3333 FROM hz_cust_accounts bcust_acct,
3334 hz_cust_acct_sites bacct_site,
3335 hz_cust_site_uses bsite_uses,
3336 hz_party_sites bparty_site,
3337 hz_locations bloc
3338 WHERE bcust_acct.cust_account_id = l_drawee_id
3339 AND bcust_acct.cust_account_id = bacct_site.cust_account_id
3340 AND bacct_site.cust_acct_site_id = bsite_uses.cust_acct_site_id
3341 AND bsite_uses.site_use_code = 'BILL_TO'
3342 AND bsite_uses.site_use_id = l_bill_to_id
3343 AND bsite_uses.status = 'A'
3344 AND bacct_site.party_site_id = bparty_site.party_site_id
3345 AND bloc.location_id = bparty_site.location_id)
3346 AND site_uses.contact_id = acct_role.cust_account_role_id(+)
3347 AND acct_role.status(+) ='A';
3348
3349 end if;
3350
3351 l_bill_id := NULL;
3352 l_bill_number := NULL;
3353 l_bill_status := NULL;
3354
3355 l_return_status := FND_API.G_RET_STS_SUCCESS;
3356
3357 /* -----------------------------------------------------------------------------------------
3358 4109513 : Transferred following sequence above, right after l_br_maturity_date is defined
3359
3360 A bill receivable must not be created when the maturity date is prior to the issue date
3361
3362 IF (TO_DATE(l_br_maturity_date,'DD/MM/RR') < TO_DATE(l_issue_date,'DD/MM/RR')) THEN
3363 FND_MESSAGE.set_name('AR','AR_BR_INCORRECT_MATURITY_DT');
3364 FND_MESSAGE.set_token('MATURITY_DT',l_br_maturity_date);
3365 FND_MESSAGE.set_token('ISSUE_DT',l_issue_date);
3366
3367 IF p_call = 3 THEN
3368 APP_EXCEPTION.raise_exception;
3369 ELSE
3370 program_debug(p_call,FND_MESSAGE.get);
3371 EXIT;
3372 END IF;
3373
3374 END IF;
3375 -----------------------------------------------------------------------------------------*/
3376
3377 /*-----------------------------------------------------------------------------------------------
3378 IF The Batch has been run in a draft mode, No BR are created but the report table is filled.
3379 Otherwise, the BR are created and the report table is filled.
3380 ------------------------------------------------------------------------------------------------*/
3381 IF p_draft_mode = 'N' THEN
3382
3383 /*----------------------------------------
3384 Create the Bill Receivable Header
3385 ----------------------------------------*/
3386 /* Bug 3472744 Placing an enclosing block to handle Exceptions. */
3387
3388 BEGIN /* create_br_header block */
3389
3390 SAVEPOINT create_BR_SVP2;
3391
3392 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3393 program_debug(p_call,'DEBUG TOOL 4');
3394 program_debug(p_call,'------------------------------------------------');
3395 program_debug(p_call,'l_br_maturity_date '||l_br_maturity_date);
3396 program_debug(p_call,'l_batch_source_id '||l_batch_source_id);
3397 program_debug(p_call,'l_cust_trx_type_id '||l_cust_trx_type_id);
3398 program_debug(p_call,'p_currency_code '||p_currency_code);
3399 program_debug(p_call,'l_br_amount '||l_br_amount);
3400 program_debug(p_call,'l_issue_date '||l_issue_date);
3401 program_debug(p_call,'l_gl_date '||l_gl_date);
3402 program_debug(p_call,'l_drawee_id '||l_drawee_id);
3403 program_debug(p_call,'l_drawee_site_use_id '||l_drawee_site_use_id);
3404 program_debug(p_call,'l_drawee_contact_id '||l_drawee_contact_id);
3405 program_debug(p_call,'p_printing_option '||NULL);
3406 program_debug(p_call,'l_comments '||l_comments);
3407 program_debug(p_call,'l_special_instructions '||l_special_instructions);
3408 program_debug(p_call,'l_drawee_bank_account_id '||l_drawee_bank_account_id);
3409 program_debug(p_call,'p_batch_id '||p_batch_id);
3410 program_debug(p_call,'l_created_from '||l_created_from);
3411 program_debug(p_call,'l_trx_number '||l_trx_number);
3412 program_debug(p_call,'l_bill_to_id '||l_bill_to_id);
3413 program_debug(p_call,'l_org_id '||l_org_id);
3414
3415 END IF;
3416
3417
3418 -- Bug 1708420 : pass l_trx_number rather than NULL
3419
3420 program_debug(p_call,'will call AR_BILLS_CREATION_PUB.Create_BR_Header');
3421 if p_call <> 3 then
3422 -- SSA-R12 : add l_org_id
3423 AR_BILLS_CREATION_PUB.Create_BR_Header (
3424 1.0, -- p_api_version
3425 NULL, -- p_init_msg_list
3426 NULL, -- p_commit,
3427 NULL, -- p_validation_level
3428 l_return_status,
3429 l_msg_count,
3430 l_msg_data,
3431 l_trx_number, -- p_trx_number
3432 l_br_maturity_date, -- p_term_due_date
3433 l_batch_source_id, -- p_batch_source_id
3434 l_cust_trx_type_id, -- p_cust_trx_type_id
3435 p_currency_code, -- p_invoice_currency_code
3436 l_br_amount, -- p_br_amount
3437 l_issue_date, -- p_trx_date
3438 l_gl_date, -- p_gl_date
3439 l_drawee_id, -- p_drawee_id
3440 l_drawee_site_use_id, -- p_drawee_site_use_id
3441 l_drawee_contact_id, -- p_drawee_contact_id
3442 NULL, -- p_printing_option
3443 l_comments, -- p_comments
3444 l_special_instructions, -- p_special_instructions
3445 null, -- p_drawee_bank_Account_id
3446 -- l_drawee_bank_account_id, -- p_drawee_bank_account_id
3447 NULL, -- p_remittance_bank_account_id
3448 NULL, -- p_override_remit_account_flag
3449 p_batch_id, -- p_batch_id
3450 NULL, -- p_doc_sequence_id
3451 NULL, -- p_doc_sequence_value
3452 l_created_from, -- p_created_from
3453 NULL, -- p_attribute_category
3454 NULL, -- p_attribute1
3455 NULL, -- p_attribute2
3456 NULL, -- p_attribute3
3457 NULL, -- p_attribute4
3458 NULL, -- p_attribute5
3459 NULL, -- p_attribute6
3460 NULL, -- p_attribute7
3461 NULL, -- p_attribute8
3462 NULL, -- p_attribute9
3463 NULL, -- p_attribute10
3464 NULL, -- p_attribute11
3465 NULL, -- p_attribute12
3466 NULL, -- p_attribute13
3467 NULL, -- p_attribute14
3468 NULL, -- p_attribute15
3469 p_le_id, -- p_legal_entity
3470 l_org_id, -- p_org_id
3471 NULL, -- p_payment_trxn_extn_id
3472 l_bill_id,
3473 l_bill_number,
3474 l_bill_status);
3475
3476 else
3477
3478 l_payer.Payment_Function := 'CUSTOMER_PAYMENT';
3479 l_payer.Party_Id := arp_trx_defaults_3.get_party_Id(l_drawee_id);
3480 l_payer.Cust_Account_Id := l_drawee_id;
3481 l_trxn_attribs.trxn_ref_number1 := 'BILLS_RECIEVABLE';
3482 l_ext_entity_tab(1) :=p_customer_bank_account_id;
3483
3484
3485 IBY_FNDCPT_TRXN_PUB.Copy_Transaction_Extension
3486 (
3487 p_api_version =>1.0,
3488 p_init_msg_list =>FND_API.G_TRUE,
3489 p_commit =>FND_API.G_FALSE,
3490 x_return_status =>l_return_status,
3491 x_msg_count =>l_msg_count,
3492 x_msg_data =>l_msg_data,
3493 p_payer =>l_payer,
3494 p_payer_equivalency =>IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_DOWNWARD,
3495 p_entities =>l_ext_entity_tab,
3496 p_trxn_attribs =>l_trxn_attribs,
3497 x_entity_id =>l_extension_id,
3498 x_response =>l_result
3499 );
3500
3501
3502 AR_BILLS_CREATION_PUB.Create_BR_Header (
3503 1.0, -- p_api_version
3504 NULL, -- p_init_msg_list
3505 NULL, -- p_commit,
3506 NULL, -- p_validation_level
3507 l_return_status,
3508 l_msg_count,
3509 l_msg_data,
3510 l_trx_number, -- p_trx_number
3511 l_br_maturity_date, -- p_term_due_date
3512 l_batch_source_id, -- p_batch_source_id
3513 l_cust_trx_type_id, -- p_cust_trx_type_id
3514 p_currency_code, -- p_invoice_currency_code
3515 l_br_amount, -- p_br_amount
3516 l_issue_date, -- p_trx_date
3517 l_gl_date, -- p_gl_date
3518 l_drawee_id, -- p_drawee_id
3519 l_drawee_site_use_id, -- p_drawee_site_use_id
3520 l_drawee_contact_id, -- p_drawee_contact_id
3521 NULL, -- p_printing_option
3522 l_comments, -- p_comments
3523 l_special_instructions, -- p_special_instructions
3524 null, -- p_drawee_bank_Account_id
3525 -- l_drawee_bank_account_id, -- p_drawee_bank_account_id
3526 NULL, -- p_remittance_bank_account_id
3527 NULL, -- p_override_remit_account_flag
3528 p_batch_id, -- p_batch_id
3529 NULL, -- p_doc_sequence_id
3530 NULL, -- p_doc_sequence_value
3531 l_created_from, -- p_created_from
3532 NULL, -- p_attribute_category
3533 NULL, -- p_attribute1
3534 NULL, -- p_attribute2
3535 NULL, -- p_attribute3
3536 NULL, -- p_attribute4
3537 NULL, -- p_attribute5
3538 NULL, -- p_attribute6
3539 NULL, -- p_attribute7
3540 NULL, -- p_attribute8
3541 NULL, -- p_attribute9
3542 NULL, -- p_attribute10
3543 NULL, -- p_attribute11
3544 NULL, -- p_attribute12
3545 NULL, -- p_attribute13
3546 NULL, -- p_attribute14
3547 NULL, -- p_attribute15
3548 p_le_id, -- p_legal_entity
3549 l_org_id, -- p_org_id
3550 l_extension_id, -- p_payment_trxn_extn_id
3551 l_bill_id,
3552 l_bill_number,
3553 l_bill_status);
3554 end if;
3555
3556
3557 select org_id into jnk1 from ra_customer_trx where customer_trx_id = l_bill_id;
3558 select org_id into jnk2 from ar_transaction_history where customer_trx_id = l_bill_id;
3559
3560 program_debug(p_call,'done with AR_BILLS_CREATION_PUB.Create_BR_Header org_id = ' || to_char(jnk1) || 'org_id = ' ||
3561 to_char(jnk2));
3562
3563 EXCEPTION /* Bug 3472744 Enclosed the following check in Exception block */
3564 WHEN OTHERS THEN
3565
3566 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3567 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3568 program_debug(p_call,'EXCEPTION AR_BILLS_CREATION_PUB.Create_BR_Header()- unexpected error');
3569 END IF;
3570 IF p_call = 3 THEN
3571 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CREATE_BR');
3572 END IF;
3573 APP_EXCEPTION.raise_exception;
3574 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3575 l_msg_data := FND_MESSAGE.Get;
3576 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3577 program_debug(p_call,'EXCEPTION AR_BILLS_CREATION_PUB.Create_BR_Header() ' || '- error :'||l_msg_data);
3578 END IF;
3579 ROLLBACK TO create_BR_SVP2;
3580 IF p_call = 3 THEN
3581 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CREATE_BR');
3582 END IF;
3583 APP_EXCEPTION.raise_exception; /* Bug3472744 Moved this from inside p_call=3 */
3584 -- EXIT; /* Bug 3472744 Commented the EXIT */
3585 END IF;
3586 END ; /* create_br_header block change6*/
3587
3588 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3589
3590 /*----------------------------------------------------
3591 Assign the payment schedules to the Bill receivable
3592 ----------------------------------------------------*/
3593
3594 -- 1st temporary table
3595 l_statement := 'SELECT payment_schedule_id, amount_assigned, org_id FROM '||
3596 g_tmp_table_nimm||' WHERE amount_assigned IS NOT NULL';
3597 OPEN c_trx FOR l_statement;
3598
3599 LOOP
3600
3601 /* Bug 3472744 Placing an enclosing block to handle Exceptions. */
3602
3603 BEGIN /* create_br_assignment_nimm block */
3604
3605 FETCH c_trx INTO l_payment_schedule_id, l_assigned_amount, l_org_id;
3606 EXIT WHEN c_trx%NOTFOUND;
3607
3608 program_debug(p_call,'nimm: l_org_id '||l_org_id);
3609
3610 program_debug(p_call,'will call AR_BILLS_CREATION_PUB.Create_BR_Assignment for NIMM');
3611 AR_BILLS_CREATION_PUB.create_br_assignment (
3612 1.0, -- p_api_version
3613 NULL, -- p_init_msg_list
3614 NULL, -- p_commit
3615 NULL, -- p_validation_level,
3616 l_return_status,
3617 l_msg_count,
3618 l_msg_data,
3619 l_bill_id, -- p_customer_trx_id
3620 l_payment_schedule_id, -- l_payment_schedule_id
3621 l_assigned_amount, -- p_assigned_amount
3622 NULL, -- p_attribute_category
3623 NULL, -- p_attribute1
3624 NULL, -- p_attribute2
3625 NULL, -- p_attribute3
3626 NULL, -- p_attribute4
3627 NULL, -- p_attribute5
3628 NULL, -- p_attribute6
3629 NULL, -- p_attribute7
3630 NULL, -- p_attribute8
3631 NULL, -- p_attribute9
3632 NULL, -- p_attribute10
3633 NULL, -- p_attribute11
3634 NULL, -- p_attribute12
3635 NULL, -- p_attribute13
3636 NULL, -- p_attribute14
3637 NULL, -- p_attribute15
3638 l_org_id, -- p_org_id
3639 l_customer_trx_line_id);
3640
3641 select org_id into jnk1 from ra_customer_trx_lines where customer_trx_line_id = l_customer_trx_line_id;
3642 program_debug(p_call,'done with AR_BILLS_CREATION_PUB.Create_BR_Assignment for NIMM, org_id = '
3643 || to_char(jnk1));
3644
3645 EXCEPTION /* Bug 3472744 Enclosed the following check in Exception block */
3646 WHEN OTHERS
3647 THEN
3648 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3649 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3650 program_debug(p_call,'EXCEPTION NIMM : AR_BILLS_CREATION_PUB.create_br_assignment() ' ||
3651 '- unexpected error ');
3652 END IF;
3653 IF p_call = 3 THEN
3654 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CREATE_BR');
3655 END IF;
3656 APP_EXCEPTION.raise_exception;
3657 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3658 l_msg_data := FND_MESSAGE.Get;
3659 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3660 program_debug(p_call,'EXCEPTION NIMM : AR_BILLS_CREATION_PUB.create_br_assignment() ' ||
3661 ' - error :'||l_msg_data);
3662 END IF;
3663 ROLLBACK TO create_BR_SVP2;
3664 IF p_call = 3 THEN
3665 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CREATE_BR');
3666 END IF;
3667 APP_EXCEPTION.raise_exception;
3668 END IF;
3669 END; /* create_br_assignment_nimm block change6*/
3670
3671 END LOOP;
3672 CLOSE c_trx;
3673
3674
3675 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3676
3677 -- 2nd temporary table
3678 l_statement := 'SELECT payment_schedule_id, amount_assigned, org_id FROM '||
3679 g_tmp_table_imm||' WHERE amount_assigned IS NOT NULL';
3680 OPEN c_trx FOR l_statement;
3681
3682 LOOP
3683
3684 /* Bug 3472744 Placing an enclosing block to handle Exceptions. */
3685
3686 BEGIN /* create_br_assignment_imm block */
3687
3688 FETCH c_trx INTO l_payment_schedule_id, l_assigned_amount, l_org_id;
3689 EXIT WHEN c_trx%NOTFOUND;
3690
3691 program_debug(p_call,'imm: l_org_id '||l_org_id);
3692 program_debug(p_call,'will call AR_BILLS_CREATION_PUB.Create_BR_Assignment for IMM');
3693
3694
3695 AR_BILLS_CREATION_PUB.create_br_assignment (
3696 1.0, -- p_api_version
3697 NULL, -- p_init_msg_list
3698 NULL, -- p_commit
3699 NULL, -- p_validation_level,
3700 l_return_status,
3701 l_msg_count,
3702 l_msg_data,
3703 l_bill_id, -- p_customer_trx_id
3704 l_payment_schedule_id, -- l_payment_schedule_id
3705 l_assigned_amount, -- p_assigned_amount
3706 NULL, -- p_attribute_category
3707 NULL, -- p_attribute1
3708 NULL, -- p_attribute2
3709 NULL, -- p_attribute3
3710 NULL, -- p_attribute4
3711 NULL, -- p_attribute5
3712 NULL, -- p_attribute6
3713 NULL, -- p_attribute7
3714 NULL, -- p_attribute8
3715 NULL, -- p_attribute9
3716 NULL, -- p_attribute10
3717 NULL, -- p_attribute11
3718 NULL, -- p_attribute12
3719 NULL, -- p_attribute13
3720 NULL, -- p_attribute14
3721 NULL, -- p_attribute15
3722 l_org_id, -- p_org_id
3723 l_customer_trx_line_id);
3724
3725 program_debug(p_call,'done with AR_BILLS_CREATION_PUB.Create_BR_Assignment for IMM');
3726
3727
3728 EXCEPTION /* Bug 3472744 Enclosed the following check in Exception block */
3729 WHEN OTHERS
3730 THEN
3731 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3732 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3733 program_debug(p_call,'EXCEPTION IMM: AR_BILLS_CREATION_PUB.create_br_assignment() ' ||
3734 '- unexpected error ');
3735 END IF;
3736 IF p_call = 3 THEN
3737 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CREATE_BR');
3738 END IF;
3739 APP_EXCEPTION.raise_exception;
3740 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3741 l_msg_data := FND_MESSAGE.Get;
3742 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3743 program_debug(p_call,'EXCEPTION IMM: AR_BILLS_CREATION_PUB.create_br_assignment() ' ||
3744 ' - error :'||l_msg_data);
3745 END IF;
3746 ROLLBACK TO create_BR_SVP2;
3747 IF p_call = 3 THEN
3748 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CREATE_BR');
3749 END IF;
3750 APP_EXCEPTION.raise_exception; /* Bug3472744 Moved this from inside p_call=3 */
3751 -- EXIT; /* Bug 3472744 Commented the exit */
3752 END IF;
3753 END ; /* create_br_assignment_imm block change6*/
3754
3755 END LOOP;
3756 CLOSE c_trx;
3757
3758
3759 -- END IF; /* Bug 3472744 Moved the end if to the position before Else */
3760
3761 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3762
3763 /*----------------------------
3764 Complete the Bill receivable
3765 -----------------------------*/
3766
3767 /* Bug 3472744 Placing an enclosing block to handle Exceptions. */
3768
3769 BEGIN /* complete_br block */
3770
3771 program_debug(p_call,'will call AR_BILLS_MAINTAIN_PUB.Complete_BR');
3772
3773 AR_BILLS_MAINTAIN_PUB.Complete_BR (
3774 1.0, -- p_api_version
3775 NULL, -- p_init_msg_list
3776 NULL, -- p_commit
3777 NULL, -- p_validation_level
3778 l_return_status,
3779 l_msg_count,
3780 l_msg_data,
3781 l_bill_id, -- p_customer_trx_id
3782 l_bill_number, -- p_trx_number
3783 l_doc_sequence_id,
3784 l_doc_sequence_value,
3785 l_old_trx_number,
3786 l_bill_status);
3787
3788
3789 program_debug(p_call,'done with AR_BILLS_MAINTAIN_PUB.Complete_BR');
3790
3791 EXCEPTION /* Bug 3472744 Enclosed the following check in Exception block */
3792 WHEN OTHERS
3793 THEN
3794 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3795 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3796 program_debug(p_call,
3797 'EXCEPTION AR_BILLS_CREATION_PUB.complete_BR() ' ||
3798 ' - unexpected error ');
3799 END IF;
3800 IF p_call = 3 THEN
3801 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CREATE_BR');
3802 END IF;
3803 APP_EXCEPTION.raise_exception;
3804 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3805 l_msg_data := FND_MESSAGE.Get;
3806 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3807 program_debug(p_call,
3808 '>>>> EXCEPTION AR_BILLS_CREATION_PUB.complete_BR() ' ||
3809 '- error :'||l_msg_data);
3810 END IF;
3811 ROLLBACK TO create_BR_SVP2;
3812 IF p_call = 3 THEN
3813 FND_MESSAGE.set_name('AR','AR_BR_CANNOT_CREATE_BR');
3814 END IF;
3815 APP_EXCEPTION.raise_exception;
3816 -- EXIT;
3817 END IF;
3818 END ; /* complete_br block change6 */
3819
3820 /* Bug 3472744 Moved SUCCESS status check to outside the block and
3821 added the condition p_call <> 3
3822
3823 Bug 3589636/3617582, restructure IF clause to ensure g_num_br_created
3824 is incremented when BR creation returns success
3825 */
3826
3827 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3828
3829 g_num_br_created := g_num_br_created + 1;
3830
3831 IF (p_call <> 3) THEN
3832 FND_FILE.PUT_LINE(FND_FILE.LOG,'the bill receivable '||l_bill_number||
3833 ' is created (amount='||l_br_amount||')');
3834
3835 ELSIF (p_call = 3) THEN
3836 /* Action PRINT BR if the program is run from the transaction workbench and
3837 the BR type printing option is set to 'Y' */
3838
3839 SELECT NVL(default_printing_option,'NOT')
3840 into l_default_printing_option
3841 FROM ra_cust_trx_types type,
3842 ra_customer_trx trx
3843 WHERE trx.customer_trx_id = l_bill_id
3844 AND trx.cust_trx_type_id = type.cust_trx_type_id;
3845
3846 l_request_id := NULL;
3847
3848 IF (l_default_printing_option = 'PRI') THEN
3849 print_BR_pvt(l_bill_id,3,l_request_id);
3850 END IF;
3851
3852 p_bill_id := l_bill_id;
3853 p_request_id := l_request_id;
3854
3855 END IF;
3856
3857 END IF;
3858
3859 END IF;
3860
3861 END IF;
3862
3863 END IF;
3864
3865
3866 ELSE
3867 g_num_br_created := g_num_br_created + 1;
3868 l_bill_number := g_num_br_created;
3869 END IF;
3870
3871
3872
3873 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) AND (p_call <> 3) THEN
3874
3875 /*------------------------------
3876 fill the report table
3877 ------------------------------*/
3878
3879 -- 1st temporary table
3880 l_statement := 'SELECT customer_trx_id, SUM(amount_assigned) FROM '||
3881 g_tmp_table_nimm||
3882 ' WHERE amount_assigned IS NOT NULL GROUP BY customer_trx_id ';
3883
3884 OPEN c_trx FOR l_statement;
3885
3886 LOOP
3887 FETCH c_trx INTO l_customer_trx_id, l_assigned_amount;
3888 EXIT WHEN c_trx%NOTFOUND;
3889
3890 -- Bug 1420183 Added p_receipt_method_id
3891 ARP_PROGRAM_GENERATE_BR.ar_br_insert_into_report_table(
3892 arp_global.request_id,
3893 p_batch_id,
3894 l_bill_id,
3895 NVL(l_bill_number,to_char(g_num_br_created)),
3896 l_br_amount,
3897 p_currency_code,
3898 l_batch_process_status,
3899 l_br_maturity_date,
3900 l_drawee_id,
3901 l_drawee_contact_id,
3902 l_drawee_site_use_id,
3903 l_drawee_bank_account_id,
3904 l_customer_trx_id,
3905 l_assigned_amount,
3906 p_receipt_method_id);
3907
3908 END LOOP;
3909
3910 CLOSE c_trx;
3911
3912 -- second temporary table
3913 l_statement := 'SELECT customer_trx_id, SUM(amount_assigned) FROM '||
3914 g_tmp_table_imm||
3915 ' WHERE amount_assigned IS NOT NULL GROUP BY customer_trx_id';
3916 OPEN c_trx FOR l_statement;
3917
3918 LOOP
3919
3920 FETCH c_trx INTO l_customer_trx_id, l_assigned_amount;
3921 EXIT WHEN c_trx%NOTFOUND;
3922
3923 -- Bug 1420183 Added p_receipt_method_id
3924 ARP_PROGRAM_GENERATE_BR.ar_br_insert_into_report_table(
3925 arp_global.request_id,
3926 p_batch_id,
3927 l_bill_id,
3928 NVL(l_bill_number,to_char(g_num_br_created)),
3929 l_br_amount,
3930 p_currency_code,
3931 l_batch_process_status,
3932 l_br_maturity_date,
3933 l_drawee_id,
3934 l_drawee_contact_id,
3935 l_drawee_site_use_id,
3936 l_drawee_bank_account_id,
3937 l_customer_trx_id,
3938 l_assigned_amount,
3939 p_receipt_method_id);
3940 END LOOP;
3941 CLOSE c_trx;
3942
3943 END IF;
3944
3945 -- the used payment schedules are deleted from the temporary tables
3946 l_delete_statement := 'DELETE FROM '||g_tmp_table_nimm||
3947 ' WHERE amount_assigned IS NOT NULL';
3948 execute immediate l_delete_statement;
3949
3950 -- bug 3930958 : insert assigned Immediate trx into AIMM table, this will prevent them from getting re-assigned
3951 -- into subsequent BRs created
3952 l_statement := 'INSERT INTO ' || g_tmp_table_aimm ||
3953 ' SELECT payment_schedule_id from ' || g_tmp_table_imm||
3954 ' WHERE amount_assigned IS NOT NULL';
3955 execute immediate l_statement;
3956
3957 l_delete_statement := 'DELETE FROM '||g_tmp_table_imm||
3958 ' WHERE amount_assigned IS NOT NULL';
3959 execute immediate l_delete_statement;
3960
3961 /* BUG 4006714 : Do not reset previously excluded transactions, doing so
3962 allows the code to exchange these transactions for a group rule that was
3963 already processed earlier, thus violating the grouping rule defined
3964
3965 -- the excluded payment schedule are released; thus, they could use to create another bill receivable
3966 l_update_statement := 'UPDATE '||g_tmp_table_nimm||
3967 ' SET exclude_flag = NULL WHERE exclude_flag IS NOT NULL';
3968 execute immediate l_update_statement;
3969
3970 l_update_statement := 'UPDATE '||g_tmp_table_imm||
3971 ' SET exclude_flag = NULL WHERE exclude_flag IS NOT NULL';
3972 execute immediate l_update_statement;
3973 */
3974
3975 END LOOP; /* process temporary table */
3976
3977 COMMIT;
3978
3979 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3980 program_debug(p_call,'ARP_PROGRAM_GENERATE_BR.create_BR (-)');
3981 END IF;
3982
3983
3984 EXCEPTION
3985 WHEN OTHERS THEN
3986 IF p_call <> 3 OR PG_DEBUG in ('Y', 'C') THEN
3987 program_debug(p_call,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.create_BR >>>> ROLLBACK');
3988 END IF;
3989 ROLLBACK TO create_BR_SVP;
3990
3991 IF c_trx%ISOPEN THEN
3992 CLOSE c_trx;
3993 END IF;
3994
3995 RAISE;
3996
3997 END create_BR;
3998
3999
4000 /*===========================================================================+
4001 | PROCEDURE |
4002 | AR_BR_INSERT_INTO_REPORT_TABLE |
4003 | |
4004 | DESCRIPTION |
4005 | |
4006 | |
4007 | SCOPE - PUBLIC |
4008 | |
4009 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
4010 | |
4011 | ARGUMENTS : IN : |
4012 | : OUT : NONE |
4013 | |
4014 | RETURNS : NONE |
4015 | |
4016 | NOTES |
4017 | |
4018 | MODIFICATION HISTORY - Created by Attila Rimai - 13/07/2000 |
4019 | |
4020 +===========================================================================*/
4021 PROCEDURE ar_br_insert_into_report_table(
4022 p_request_id IN RA_CUSTOMER_TRX.request_id%TYPE,
4023 p_batch_id IN RA_BATCHES.batch_id%TYPE,
4024 p_br_customer_trx_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
4025 p_bill_number IN RA_CUSTOMER_TRX.trx_number%TYPE,
4026 p_br_amount IN AR_RECEIPT_METHODS.br_min_acctd_amount%TYPE,
4027 p_br_currency IN RA_BATCHES.currency_code%TYPE,
4028 p_batch_status IN RA_BATCHES.status%TYPE,
4029 p_maturity_date IN RA_BATCHES.maturity_date%TYPE,
4030 p_drawee_id IN RA_CUSTOMER_TRX.drawee_id%TYPE,
4031 p_drawee_contact_id IN RA_CUSTOMER_TRX.drawee_contact_id%TYPE,
4032 p_drawee_site_use_id IN RA_CUSTOMER_TRX.drawee_site_use_id%TYPE,
4033 p_drawee_bank_account_id IN RA_CUSTOMER_TRX.drawee_bank_account_id%TYPE,
4034 p_transaction_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
4035 p_amount_assigned IN RA_CUSTOMER_TRX.br_amount%TYPE,
4036 p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE) IS
4037
4038 -- Bug 1420183
4039 -- Could pass creation rule code, min/max amounts into this procedure as parameters, but since
4040 -- we need to hit ar_receipt_methods to get lead days, we may as well get the other info there also
4041 CURSOR c_get_receipt_method(l_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE) IS
4042 SELECT name,
4043 receipt_creation_rule_code,
4044 br_min_acctd_amount,
4045 br_max_acctd_amount,
4046 lead_days
4047 FROM ar_receipt_methods
4048 WHERE receipt_method_id = l_receipt_method_id;
4049
4050 l_receipt_method_name AR_RECEIPT_METHODS.name%TYPE;
4051 l_receipt_creation_rule_code AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE;
4052 l_br_min_acctd_amount AR_RECEIPT_METHODS.br_min_acctd_amount%TYPE;
4053 l_br_max_acctd_amount AR_RECEIPT_METHODS.br_max_acctd_amount%TYPE;
4054 l_lead_days AR_RECEIPT_METHODS.lead_days%TYPE;
4055
4056 BEGIN
4057
4058
4059 OPEN c_get_receipt_method(p_receipt_method_id);
4060 FETCH c_get_receipt_method into l_receipt_method_name, l_receipt_creation_rule_code,
4061 l_br_min_acctd_amount, l_br_max_acctd_amount,
4062 l_lead_days;
4063
4064 IF (c_get_receipt_method%NOTFOUND) THEN
4065 null;
4066 END IF;
4067
4068 CLOSE c_get_receipt_method;
4069
4070 --
4071
4072 INSERT INTO AR_BR_TRX_BATCH_RPT ( creation_date,
4073 created_by,
4074 last_update_date,
4075 last_updated_by,
4076 last_update_login ,
4077 request_id,
4078 batch_id,
4079 br_customer_trx_id,
4080 bill_number,
4081 br_amount,
4082 br_currency,
4083 batch_status,
4084 maturity_date,
4085 drawee_id,
4086 drawee_contact_id,
4087 drawee_site_use_id,
4088 drawee_bank_account_id,
4089 transaction_id,
4090 amount_assigned,
4091 receipt_method_name,
4092 receipt_creation_rule_code,
4093 br_min_acctd_amount,
4094 br_max_acctd_amount,
4095 lead_days)
4096 VALUES
4097 (sysdate, /* creation_date */
4098 fnd_global.user_id, /* created_by */
4099 sysdate, /* last_update_date */
4100 fnd_global.user_id, /* last_updated_by */
4101 nvl(fnd_global.conc_login_id,fnd_global.login_id), /* last_update_login */
4102 p_request_id,
4103 p_batch_id,
4104 p_br_customer_trx_id,
4105 p_bill_number,
4106 p_br_amount,
4107 p_br_currency,
4108 p_batch_status,
4109 p_maturity_date,
4110 p_drawee_id,
4111 p_drawee_contact_id,
4112 p_drawee_site_use_id,
4113 p_drawee_bank_account_id,
4114 p_transaction_id,
4115 p_amount_assigned,
4116 l_receipt_method_name,
4117 l_receipt_creation_rule_code,
4118 l_br_min_acctd_amount,
4119 l_br_max_acctd_amount,
4120 l_lead_days);
4121
4122
4123 EXCEPTION WHEN OTHERS THEN
4124 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION OTHERS: ARP_PROGRAM_GENERATE_BR.AR_BR_INSERT_INTO_REPORT_TABLE ');
4125 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
4126 IF c_get_receipt_method%ISOPEN THEN
4127 CLOSE c_get_receipt_method;
4128 END IF;
4129
4130 END AR_BR_INSERT_INTO_REPORT_TABLE ;
4131
4132
4133 /*===========================================================================+
4134 | PROCEDURE |
4135 | arbr_cr_tmp_table |
4136 | |
4137 | DESCRIPTION |
4138 | |
4139 | |
4140 | SCOPE - PUBLIC |
4141 | |
4142 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
4143 | |
4144 | ARGUMENTS : IN : |
4145 | : OUT : NONE |
4146 | |
4147 | RETURNS : NONE |
4148 | |
4149 | NOTES |
4150 | |
4151 | MODIFICATION HISTORY - Created by Attila Rimai - 13/07/2000 |
4152 | 25-MAY-05 VCRISOST SSA - R12 : add org_id |
4153 | |
4154 +===========================================================================*/
4155 PROCEDURE arbr_cr_tmp_table IS
4156
4157 compteur NUMBER :=0;
4158 l_suffixe VARCHAR2(100) := NULL;
4159
4160 l_prefixe_nimm VARCHAR2(21) := 'AR_BR_TMP_NIMM';
4161 table_name_nimm VARCHAR2(50);
4162 nb_nimm NUMBER;
4163
4164 l_prefixe_imm VARCHAR2(21) := 'AR_BR_TMP_IMM';
4165 table_name_imm VARCHAR2(50);
4166 nb_imm NUMBER;
4167
4168 -- 3930958 : define another temp table containing IMM trx that have been assigned
4169 l_prefixe_aimm VARCHAR2(21) := 'AR_BR_TMP_AIMM';
4170 table_name_aimm VARCHAR2(50);
4171 nb_aimm NUMBER;
4172
4173 query_create VARCHAR2(20000);
4174
4175 /* Bug 3441913/ 3432134 */
4176 /* Developer Comments:
4177
4178 1. The way temporary tables are created in the APPS schema is not correct.
4179 All tables must be created in AR product schema using ad_ddl package and
4180 should be reworked at a future date.
4181
4182 The impact will be invasive, so I am not doing it in this bug.
4183 2. For now, the check for temporary tables will be done in the same schema as
4184 the schema from which the package is run (that's where the temp tables are
4185 getting created.
4186 */
4187
4188 l_user_schema VARCHAR2(30) := USER;
4189
4190 BEGIN
4191
4192 --FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.arbr_cr_tmp_table (+)');
4193
4194 LOOP
4195
4196
4197 compteur := compteur + 1;
4198 SELECT to_char(sysdate,'YYYYMMDDHH24MISS') INTO l_suffixe FROM dual;
4199
4200 table_name_nimm := l_prefixe_nimm||l_suffixe||to_char((compteur-1)*10);
4201 table_name_imm := l_prefixe_imm ||l_suffixe||to_char((compteur-1)*10);
4202 table_name_aimm := l_prefixe_aimm ||l_suffixe||to_char((compteur-1)*10);
4203
4204 /*
4205 FND_FILE.PUT_LINE(FND_FILE.LOG,'table_name_nimm = ' || table_name_nimm);
4206 FND_FILE.PUT_LINE(FND_FILE.LOG,'table_name_imm = ' || table_name_imm);
4207 FND_FILE.PUT_LINE(FND_FILE.LOG,'table_name_aimm = ' || table_name_aimm);
4208 */
4209
4210 /* Bug 3441913/3432134 - suffix dba_ views with owner */
4211 /* Added owner predicate which ensure that comply with GSCC Standard - File.Sql.47 */
4212
4213 BEGIN
4214
4215 SELECT COUNT(object_name)
4216 INTO nb_nimm
4217 FROM sys.dba_objects
4218 WHERE object_name = table_name_nimm
4219 AND owner = l_user_schema;
4220
4221 EXCEPTION
4222 WHEN NO_DATA_FOUND THEN
4223 nb_nimm := 0;
4224
4225 END;
4226
4227 BEGIN
4228
4229 SELECT COUNT(object_name)
4230 INTO nb_imm
4231 FROM sys.dba_objects
4232 WHERE object_name = table_name_imm
4233 AND owner = l_user_schema;
4234
4235 EXCEPTION
4236 WHEN NO_DATA_FOUND THEN
4237 nb_imm := 0;
4238
4239 END;
4240
4241 BEGIN
4242
4243 SELECT COUNT(object_name)
4244 INTO nb_aimm
4245 FROM sys.dba_objects
4246 WHERE object_name = table_name_aimm
4247 AND owner = l_user_schema;
4248
4249 EXCEPTION
4250 WHEN NO_DATA_FOUND THEN
4251 nb_aimm := 0;
4252
4253 END;
4254
4255 -- If the table names aren't stored in the table DBA_OJECTS, both temporary tables are created with these names
4256 IF (nb_nimm=0) AND (nb_imm = 0) AND (nb_aimm = 0) THEN
4257
4258 ----------------------------------------------------------------------------------------
4259 -- Creation of the temporary table for Transactions with a payment term of non immediate
4260 ----------------------------------------------------------------------------------------
4261 g_tmp_table_nimm := table_name_nimm;
4262
4263 query_create := 'CREATE TABLE '|| table_name_nimm ||
4264 ' (payment_schedule_id NUMBER(15),
4265 customer_trx_id NUMBER(15),
4266 cust_trx_type_id NUMBER(15),
4267 customer_id NUMBER(15),
4268 customer_site_use_id NUMBER(15),
4269 trx_number VARCHAR2(30),
4270 due_date DATE,
4271 amount_due_remaining NUMBER,
4272 amount_assigned NUMBER,
4273 exclude_flag VARCHAR2(1),
4274 org_id NUMBER(15))';
4275
4276 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Creation of the temporary table '||table_name_nimm);
4277 execute immediate query_create;
4278
4279 query_create := 'CREATE INDEX ' || 'ARBR_IND_NIMM' || l_suffixe || to_char((compteur-1)*10) || ' ON '
4280 || table_name_nimm || ' (customer_trx_id,amount_due_remaining)';
4281 execute immediate query_create;
4282
4283 -------------------------------------------------------------------------------------
4284 -- Creation of the temporary table for Transactions with a payment term of Immediate
4285 -------------------------------------------------------------------------------------
4286 g_tmp_table_imm := table_name_imm;
4287
4288 query_create := 'CREATE TABLE '|| table_name_imm ||
4289 ' (payment_schedule_id NUMBER(15),
4290 customer_trx_id NUMBER(15),
4291 cust_trx_type_id NUMBER(15),
4292 customer_id NUMBER(15),
4293 customer_site_use_id NUMBER(15),
4294 trx_number VARCHAR2(30),
4295 due_date DATE,
4296 amount_due_remaining NUMBER,
4297 amount_assigned NUMBER,
4298 exclude_flag VARCHAR2(1),
4299 org_id NUMBER(15))';
4300
4301 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Creation of the temporary table '||table_name_imm);
4302 execute immediate query_create;
4303
4304 query_create := 'CREATE INDEX ' || 'ARBR_IND_IMM' || l_suffixe || to_char((compteur-1)*10) || ' ON ' ||
4305 table_name_imm || ' (customer_trx_id,amount_due_remaining)';
4306
4307 execute immediate query_create;
4308
4309 ------------------------------------------------------------------------------------------------------------------------
4310 -- Creation of the temporary table for Transactions with a payment term of Immediate which have been assigned
4311 ------------------------------------------------------------------------------------------------------------------------
4312 g_tmp_table_aimm := table_name_aimm;
4313
4314 query_create := 'CREATE TABLE '|| table_name_aimm ||
4315 ' (payment_schedule_id NUMBER(15))';
4316
4317 FND_FILE.PUT_LINE(FND_FILE.LOG,'Creation of the temporary table '||table_name_aimm);
4318 execute immediate query_create;
4319
4320 query_create := 'CREATE INDEX ' || 'ARBR_IND_AIMM' || l_suffixe || to_char((compteur-1)*10) || ' ON ' ||
4321 table_name_aimm || ' (payment_schedule_id)';
4322
4323 execute immediate query_create;
4324
4325 EXIT;
4326
4327 END IF;
4328
4329 END LOOP;
4330
4331 --FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.arbr_cr_tmp_table (-)');
4332
4333 EXCEPTION
4334 WHEN OTHERS THEN
4335 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.arbr_cr_tmp_table');
4336 RAISE;
4337
4338 END arbr_cr_tmp_table;
4339
4340
4341 /*===========================================================================+
4342 | PROCEDURE |
4343 | drop_tmp_table |
4344 | |
4345 | DESCRIPTION |
4346 | |
4347 | |
4348 | SCOPE - PUBLIC |
4349 | |
4350 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
4351 | |
4352 | ARGUMENTS : IN : |
4353 | : OUT : NONE |
4354 | |
4355 | RETURNS : NONE |
4356 | |
4357 | NOTES |
4358 | |
4359 | MODIFICATION HISTORY - Created by Mireille Flahaut - 25/07/2000 |
4360 | |
4361 +===========================================================================*/
4362 PROCEDURE drop_tmp_table IS
4363
4364 nb_obj number;
4365 query_drop varchar2(50) := NULL;
4366
4367 /* Bug 3441913/3432134 */
4368 l_user_schema VARCHAR2(30) := USER;
4369
4370
4371 BEGIN
4372
4373 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.drop_tmp_table (+)');
4374
4375 ----------------------------------------------------------------------------------------
4376 -- Drop of the temporary table for Transactions with a payment term of non immediate
4377 ----------------------------------------------------------------------------------------
4378 /* Bug 3441913/3432134 - suffix dba_ views with owner */
4379
4380 /* Added owner predicate which ensure that comply with GSCC Standard - File.Sql.47 */
4381
4382 BEGIN
4383
4384 SELECT COUNT(object_name)
4385 INTO nb_obj
4386 FROM sys.dba_objects
4387 WHERE object_name = g_tmp_table_nimm
4388 AND owner = l_user_schema;
4389
4390 EXCEPTION
4391 WHEN NO_DATA_FOUND THEN
4392 nb_obj := 0;
4393
4394 END;
4395
4396
4397 IF (nb_obj > 0) THEN
4398 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Drop of the temporary table '||g_tmp_table_nimm);
4399 query_drop := 'DROP table '||g_tmp_table_nimm;
4400 execute immediate query_drop;
4401 END IF;
4402
4403 ----------------------------------------------------------------------------------------
4404 -- Drop of the temporary table for Transactions with a payment term of immediate
4405 ----------------------------------------------------------------------------------------
4406 /* Bug 3441913/3432134 - suffix dba_ views with owner */
4407
4408 /* Added owner predicate which ensure that comply with GSCC Standard - File.Sql.47 */
4409
4410 BEGIN
4411
4412 SELECT COUNT(object_name)
4413 INTO nb_obj
4414 FROM sys.dba_objects
4415 WHERE object_name = g_tmp_table_imm
4416 AND owner = l_user_schema;
4417
4418 EXCEPTION
4419 WHEN NO_DATA_FOUND THEN
4420 nb_obj := 0;
4421
4422 END;
4423
4424 IF (nb_obj > 0) THEN
4425 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Drop of the temporary table '||g_tmp_table_imm);
4426 query_drop := 'DROP table '||g_tmp_table_imm;
4427 execute immediate query_drop;
4428 END IF;
4429
4430 -----------------------------------------------------------------------------
4431 -- 3930958 : Drop of the temporary table for Assigned Immediate Transactions
4432 -----------------------------------------------------------------------------
4433 BEGIN
4434
4435 SELECT COUNT(object_name)
4436 INTO nb_obj
4437 FROM sys.dba_objects
4438 WHERE object_name = g_tmp_table_aimm
4439 AND owner = l_user_schema;
4440
4441 EXCEPTION
4442 WHEN NO_DATA_FOUND THEN
4443 nb_obj := 0;
4444
4445 END;
4446
4447 IF (nb_obj > 0) THEN
4448 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Drop of the temporary table '||g_tmp_table_aimm);
4449 query_drop := 'DROP table '||g_tmp_table_aimm;
4450 execute immediate query_drop;
4451 END IF;
4452
4453
4454 FND_FILE.PUT_LINE(FND_FILE.LOG,'ARP_PROGRAM_GENERATE_BR.drop_tmp_table (-)');
4455
4456 EXCEPTION
4457 WHEN OTHERS THEN
4458 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.drop_tmp_table');
4459 RAISE;
4460
4461 END drop_tmp_table;
4462
4463
4464 /*===========================================================================+
4465 | PROCEDURE |
4466 | run_report_pvt |
4467 | |
4468 | DESCRIPTION |
4469 | Procedure called during the process create bills receivable, |
4470 | to run the report 'Automatic Transaction Batch Report' |
4471 | |
4472 | SCOPE - PRIVATE |
4473 | |
4474 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
4475 | |
4476 | |
4477 | ARGUMENTS : IN : |
4478 | p_batch_id : remittance batch identifier |
4479 | |
4480 | : OUT : NONE |
4481 | |
4482 | RETURNS : NONE |
4483 | |
4484 | NOTES |
4485 | |
4486 | MODIFICATION HISTORY - Created by Mireille Flahaut - 25/07/2000 |
4487 | |
4488 +===========================================================================*/
4489 PROCEDURE run_report_pvt(
4490 p_batch_id IN RA_BATCHES.batch_id%TYPE) IS
4491
4492 l_request_id NUMBER;
4493 l_version VARCHAR2(30);
4494 l_meaning VARCHAR2(30);
4495
4496 BEGIN
4497
4498 --FND_FILE.PUT_LINE(FND_FILE.LOG,'run_report_pvt (+)');
4499
4500 SAVEPOINT run_report_SVP;
4501
4502 -- parameter p_batch_id mustn't be NULL
4503 IF (p_batch_id IS NULL) THEN
4504 g_field := 'p_batch_id';
4505 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
4506 FND_MESSAGE.set_token('PROCEDURE','run_report_pvt');
4507 FND_MESSAGE.set_token('PARAMETER', g_field);
4508 APP_EXCEPTION.raise_exception;
4509 END IF;
4510
4511 -- parameter l_version must be 'D' for detail
4512 SELECT LOOKUP_CODE, MEANING
4513 INTO l_version, l_meaning
4514 FROM AR_LOOKUPS
4515 WHERE LOOKUP_TYPE = 'ARBRATBR_REPORT_TYPE'
4516 AND LOOKUP_CODE = 'D';
4517
4518 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------- Automatic Transactions Batch Report Parameters ---------------');
4519
4520 FND_FILE.PUT_LINE(FND_FILE.LOG,'BATCH ID :'||p_batch_id);
4521 FND_FILE.PUT_LINE(FND_FILE.LOG,'VERSION :'||l_meaning);
4522 FND_FILE.PUT_LINE(FND_FILE.LOG,'API Request ID :'||arp_global.request_id);
4523
4524 -- SSA - R12 : set org id prior to calling submit_request
4525 FND_REQUEST.set_org_id(g_org_id);
4526 l_request_id := FND_REQUEST.submit_request('AR'
4527 ,'ARBRATBR'
4528 ,NULL
4529 ,NULL
4530 ,NULL
4531 ,p_batch_id
4532 ,l_version
4533 ,arp_global.request_id);
4534
4535 IF (l_request_id = 0) THEN
4536 FND_MESSAGE.set_name('AR','AR_BR_BATCH_SUBMIT_FAILED');
4537 FND_MESSAGE.set_token('PROCEDURE','ARP_PROGRAM_BR_REMIT.run_report_pvt');
4538 APP_EXCEPTION.raise_exception;
4539 ELSE
4540 FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitted Request : '||to_char(l_request_id));
4541 END IF;
4542
4543 commit;
4544
4545 FND_FILE.PUT_LINE(FND_FILE.LOG,'----------------------------------------------------------------------------');
4546
4547 --FND_FILE.PUT_LINE(FND_FILE.LOG,'run_report_pvt (-)');
4548
4549 EXCEPTION
4550 WHEN OTHERS THEN
4551 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.run_report_pvt - ROLLBACK');
4552 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
4553 ROLLBACK TO run_report_SVP;
4554
4555 END run_report_pvt;
4556
4557
4558 /*===========================================================================+
4559 | PROCEDURE |
4560 | print_BR_pvt |
4561 | |
4562 | DESCRIPTION |
4563 | Procedure called during the process create bills receivable, |
4564 | to handle the option Print BR |
4565 | |
4566 | SCOPE - PRIVATE |
4567 | |
4568 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
4569 | |
4570 | |
4571 | ARGUMENTS : IN : |
4572 | p_batch_id : remittance batch identifier |
4573 | |
4574 | : OUT : NONE |
4575 | |
4576 | RETURNS : NONE |
4577 | |
4578 | NOTES |
4579 | |
4580 | MODIFICATION HISTORY - Created by Mireille Flahaut - 25/07/2000 |
4581 | |
4582 +===========================================================================*/
4583 PROCEDURE print_BR_pvt(
4584 p_object_id IN RA_BATCHES.batch_id%TYPE,
4585 p_call IN NUMBER,
4586 p_request_id OUT NOCOPY NUMBER) IS
4587
4588 l_request_id NUMBER;
4589 l_format VARCHAR2(30);
4590
4591 BEGIN
4592
4593 --FND_FILE.PUT_LINE(FND_FILE.LOG,'print_BR_pvt (+)');
4594
4595 SAVEPOINT print_BR_SVP;
4596
4597 -- parameter p_object_id mustn't be NULL
4598 IF (p_object_id IS NULL) THEN
4599 g_field := 'p_object_id';
4600 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
4601 FND_MESSAGE.set_token('PROCEDURE','print_BR_pvt');
4602 FND_MESSAGE.set_token('PARAMETER', g_field);
4603 APP_EXCEPTION.raise_exception;
4604 END IF;
4605
4606
4607 IF p_call <> 3 THEN
4608 -- from the Batch window or SRS
4609 l_format := 'BR BATCH';
4610 ELSE
4611 -- from the transaction workbench
4612 l_format := 'IND';
4613 END IF;
4614
4615
4616 FND_FILE.PUT_LINE(FND_FILE.LOG,'--------------------------- ACTION Print Bills -----------------------------');
4617 FND_FILE.PUT_LINE(FND_FILE.LOG,'Program ARBRFMTW parameters');
4618 FND_FILE.PUT_LINE(FND_FILE.LOG,'BATCH OR TRX ID :'||p_object_id);
4619 FND_FILE.PUT_LINE(FND_FILE.LOG,'SOB ID :'||arp_global.set_of_books_id);
4620
4621 -- SSA - R12 : set org id prior to calling submit_request
4622 FND_REQUEST.set_org_id(g_org_id);
4623 l_request_id := FND_REQUEST.submit_request('AR'
4624 ,'ARBRFMTW'
4625 ,NULL
4626 ,NULL
4627 ,NULL
4628 ,l_format
4629 ,p_object_id
4630 ,NULL
4631 ,NULL
4632 ,arp_global.set_of_books_id);
4633
4634 IF (l_request_id = 0) THEN
4635 FND_MESSAGE.set_name('AR','AR_BR_BATCH_SUBMIT_FAILED');
4636 FND_MESSAGE.set_token('PROCEDURE','ARP_PROGRAM_BR_REMIT.print_BR_pvt');
4637 APP_EXCEPTION.raise_exception;
4638 ELSE
4639 FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitted Request : '||to_char(l_request_id));
4640 END IF;
4641
4642 p_request_id := l_request_id;
4643
4644 --commit;
4645
4646 FND_FILE.PUT_LINE(FND_FILE.LOG,'----------------------------------------------------------------------------');
4647
4648 --FND_FILE.PUT_LINE(FND_FILE.LOG,'print_BR_pvt (-)');
4649
4650 EXCEPTION
4651 WHEN OTHERS THEN
4652 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION : ARP_PROGRAM_GENERATE_BR.print_BR_pvt - ROLLBACK');
4653 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
4654 ROLLBACK TO print_BR_SVP;
4655
4656 END print_BR_pvt;
4657
4658
4659 /*===========================================================================+
4660 | PROCEDURE |
4661 | auto_create_br_API |
4662 | |
4663 | DESCRIPTION |
4664 | this procedure will create a BR given a customer_trx_id |
4665 | |
4666 | |
4667 | SCOPE - PUBLIC |
4668 | |
4669 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
4670 | |
4671 | ARGUMENTS : IN : |
4672 | |
4673 | : OUT : NONE |
4674 | |
4675 | RETURNS : NONE |
4676 | |
4677 | NOTES |
4678 | |
4679 | MODIFICATION HISTORY - Created by Mireille Flahaut - 10/08/2000 |
4680 | |
4681 | 06-JUN-01 VCRISOST Bug 1808976 : add customer_bank_account_id |
4682 | in c_receipt_method cursor |
4683 | 11-MAY-05 VCRISOST LE-R12: retrieve le_id |
4684 | |
4685 +===========================================================================*/
4686 PROCEDURE auto_create_br_API(
4687 p_api_version IN NUMBER,
4688 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
4689 p_commit IN VARCHAR2 := FND_API.G_TRUE,
4690 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4691 x_return_status OUT NOCOPY VARCHAR2,
4692 x_msg_count OUT NOCOPY NUMBER,
4693 x_msg_data OUT NOCOPY VARCHAR2,
4694 p_customer_trx_id IN RA_CUSTOMER_TRX.customer_trx_id%TYPE,
4695 p_bill_id OUT NOCOPY RA_CUSTOMER_TRX.customer_trx_id%TYPE,
4696 p_request_id OUT NOCOPY NUMBER,
4697 p_nb_bill OUT NOCOPY NUMBER) IS
4698
4699 l_api_name CONSTANT varchar2(30) := 'auto_create_br_API';
4700 l_api_version CONSTANT number := 1.0;
4701
4702 -- bug 1808976 : add customer_bank_account_id
4703
4704 CURSOR c_receipt_method IS
4705 SELECT pm.receipt_method_id,
4706 pm.receipt_creation_rule_code,
4707 NVL(pm.lead_days,0),
4708 pm.maturity_date_rule_code,
4709 DECODE(pm.br_min_acctd_amount,NULL,0.00000001,0,0.00000001,pm.br_min_acctd_amount),
4710 NVL(pm.br_max_acctd_amount,9999999999999999999999999999999999),
4711 trx.invoice_currency_code,
4712 -- trx.customer_bank_account_id, Bug 5051673
4713 trx.payment_trxn_extension_id,
4714 trx.legal_entity_id
4715 FROM ar_receipt_methods pm,
4716 ra_customer_trx trx
4717 WHERE trx.customer_trx_id = p_customer_trx_id
4718 AND trx.receipt_method_id = pm.receipt_method_id;
4719
4720 c_grouping cur_typ;
4721 c_trx cur_typ;
4722
4723 l_trx_select_statement VARCHAR2(3000) :=NULL;
4724 l_suffixe_select_statement VARCHAR2(2000) :=NULL;
4725 l_suffix_hz VARCHAR2(5000) :=NULL;
4726 l_insert_statement VARCHAR2(1000) :=NULL;
4727 l_delete_statement VARCHAR2(1000) :=NULL;
4728
4729 -- 1st break criteria
4730 l_receipt_method_id AR_RECEIPT_METHODS.receipt_method_id%TYPE;
4731 l_receipt_creation_rule_code AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE;
4732 l_lead_days AR_RECEIPT_METHODS.lead_days%TYPE;
4733 l_maturity_date_rule_code AR_RECEIPT_METHODS.maturity_date_rule_code%TYPE;
4734 l_br_min_acctd_amount AR_RECEIPT_METHODS.br_min_acctd_amount%TYPE;
4735 l_br_max_acctd_amount AR_RECEIPT_METHODS.br_max_acctd_amount%TYPE;
4736 l_invoice_currency_code RA_CUSTOMER_TRX.invoice_currency_code%TYPE;
4737 l_le_id RA_CUSTOMER_TRX.legal_entity_id%TYPE;
4738
4739 -- bug 1808976 :
4740 l_customer_bank_account_id RA_CUSTOMER_TRX.customer_bank_account_id%TYPE;
4741
4742 l_payment_schedule_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE;
4743 l_customer_trx_id AR_PAYMENT_SCHEDULES.customer_trx_id%TYPE;
4744 l_cust_trx_type_id AR_PAYMENT_SCHEDULES.cust_trx_type_id%TYPE;
4745 l_customer_id AR_PAYMENT_SCHEDULES.customer_id%TYPE;
4746 p_customer_id AR_PAYMENT_SCHEDULES.customer_id%TYPE := NULL;
4747 l_customer_site_use_id AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE;
4748 l_site_use_id AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE;
4749 l_trx_number AR_PAYMENT_SCHEDULES.trx_number%TYPE;
4750 l_due_date AR_PAYMENT_SCHEDULES.due_date%TYPE;
4751 l_amount_due_remaining AR_PAYMENT_SCHEDULES.amount_due_remaining%TYPE;
4752
4753 l_bill_id RA_CUSTOMER_TRX.customer_trx_id%TYPE;
4754 l_request_id NUMBER;
4755
4756 BEGIN
4757
4758 IF PG_DEBUG in ('Y', 'C') THEN
4759 arp_util.debug('ARP_PROGRAM_GENERATE_BR.auto_create_br_API (+)');
4760 END IF;
4761
4762 -- Standard call to check for call compatability
4763 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4765 END IF;
4766
4767 x_return_status := FND_API.G_RET_STS_SUCCESS;
4768
4769 -------------------------------------------------------------------------
4770 -- Validations
4771 -------------------------------------------------------------------------
4772 IF p_customer_trx_id IS NULL THEN
4773 g_field := 'p_customer_trx_id';
4774 FND_MESSAGE.set_name('AR','AR_PROCEDURE_VALID_ARGS_FAIL');
4775 FND_MESSAGE.set_token('PROCEDURE','auto_create_br_API');
4776 FND_MESSAGE.set_token('PARAMETER', g_field);
4777 app_exception.raise_exception;
4778 END IF;
4779
4780 /*Bug2290332: Reset Variable Storing No of BR Created */
4781 g_num_br_created := 0;
4782
4783
4784 -------------------------------------------------------------------------
4785 -- the transaction payment method is retrieved
4786 -------------------------------------------------------------------------
4787 OPEN c_receipt_method;
4788 FETCH c_receipt_method into l_receipt_method_id, l_receipt_creation_rule_code, l_lead_days,
4789 l_maturity_date_rule_code, l_br_min_acctd_amount, l_br_max_acctd_amount,
4790 l_invoice_currency_code, l_customer_bank_account_id, l_le_id;
4791
4792 IF (c_receipt_method%NOTFOUND) THEN
4793 FND_MESSAGE.set_name('AR','AR_BR_INVALID_TRANSACTION');
4794 FND_MESSAGE.set_token('TRX',p_customer_trx_id);
4795 APP_EXCEPTION.raise_exception;
4796 END IF;
4797
4798 CLOSE c_receipt_method;
4799
4800 -- Temporary table Creation
4801 ARP_PROGRAM_GENERATE_BR.arbr_cr_tmp_table;
4802
4803 -------------------------------------------------------------------------
4804 -- Setup the transaction select statement
4805 -------------------------------------------------------------------------
4806 /* Bug 1744783 : for transactions with multiple payment schedule records,
4807 BR is only created for the first installment, replaced ps.customer_trx_id
4808 with ps.payment_schedule_id, also did a direct comparison between
4809 br_ref_payment_schedule_id = ps.payment_schedule, rather than comparing
4810 to is not null
4811
4812 Bug 1849801 : the changes made for above bug have a typo, the NOT IN clause
4813 was using ps.payment_schedule rather than ps.payment_schedule_id,
4814 this was causing an ORA-904 error
4815
4816 Bug2290332: Modified the sub-query. The sub-query now allows transactions to
4817 be picked if the BR created earlier against the transaction was cancelled.
4818 Also added additional condition to check if the payment schedule is open and
4819 to check that the transaction has not been reserved.
4820 */
4821
4822 l_suffixe_select_statement := ' FROM ar_payment_schedules ps,'||
4823 ' ra_cust_trx_types type,'||
4824 ' ra_customer_trx trx,'||
4825 ' ar_receipt_methods pm '||
4826 'WHERE ps.cust_trx_type_id = type.cust_trx_type_id '||
4827 'AND (type.type IN (''INV'',''DEP'',''CB'') OR '||
4828 ' (type.type IN (''CM'',''DM'') AND ps.term_id <> 5)) '||
4829 'AND ps.customer_trx_id = NVL(:p_customer_trx_id,ps.customer_trx_id) '||
4830 'AND ps.payment_schedule_id NOT IN '||
4831 '(SELECT br_ref_payment_schedule_id '||
4832 ' from '||
4833 'ra_customer_trx_lines br_lines, '||
4834 'ar_transaction_history th '||
4835 'where br_lines.br_ref_payment_schedule_id = ps.payment_schedule_id '||
4836 'and br_lines.customer_trx_id = th.customer_trx_id '||
4837 'and th.current_record_flag = ''Y'' '||
4838 'and th.status <> ''CANCELLED'') '||
4839 'AND ps.reserved_type IS NULL '||
4840 'AND ps.reserved_value IS NULL '||
4841 'AND ps.status =''OP'' ' ||
4842 'AND ps.customer_trx_id = trx.customer_trx_id '||
4843 'AND trx.receipt_method_id = pm.receipt_method_id ';
4844
4845 -- bug 3922691
4846 ARP_PROGRAM_GENERATE_BR.construct_hz(l_receipt_creation_rule_code,
4847 p_customer_id,
4848 l_suffix_hz);
4849
4850 l_suffixe_select_statement := l_suffixe_select_statement || l_suffix_hz;
4851
4852 IF l_receipt_creation_rule_code = 'PER_CUSTOMER' THEN
4853 l_trx_select_statement := 'SELECT DISTINCT ps.customer_id '||
4854 l_suffixe_select_statement||
4855 ' ORDER BY ps.customer_id ';
4856 ELSIF l_receipt_creation_rule_code = 'PER_CUSTOMER_DUE_DATE' THEN
4857 l_trx_select_statement := 'SELECT DISTINCT ps.customer_id, ps.due_date '||
4858 l_suffixe_select_statement||
4859 ' ORDER BY ps.customer_id, ps.due_date ';
4860 ELSIF l_receipt_creation_rule_code = 'PER_SITE' THEN
4861 l_trx_select_statement := 'SELECT DISTINCT ps.customer_site_use_id '||
4862 l_suffixe_select_statement||
4863 ' ORDER BY ps.customer_site_use_id ';
4864 ELSIF l_receipt_creation_rule_code = 'PER_SITE_DUE_DATE' THEN
4865 l_trx_select_statement := 'SELECT DISTINCT ps.customer_site_use_id, ps.due_date '||
4866 l_suffixe_select_statement||
4867 ' ORDER BY ps.customer_site_use_id, ps.due_date ';
4868 ELSIF l_receipt_creation_rule_code = 'PER_INVOICE' THEN
4869 l_trx_select_statement := 'SELECT DISTINCT ps.customer_trx_id '||
4870 l_suffixe_select_statement||
4871 ' ORDER BY ps.customer_trx_id ';
4872 ELSIF l_receipt_creation_rule_code = 'PER_PAYMENT_SCHEDULE' THEN
4873 l_trx_select_statement := 'SELECT DISTINCT ps.payment_schedule_id '||
4874 l_suffixe_select_statement||
4875 ' ORDER BY ps.payment_schedule_id ';
4876 ELSE
4877 FND_MESSAGE.set_name('AR','AR_BR_INVALID_GROUPING_RULE');
4878 FND_MESSAGE.set_token('GROUPING_RULE',l_receipt_creation_rule_code);
4879 APP_EXCEPTION.raise_exception;
4880 END IF;
4881
4882 IF PG_DEBUG in ('Y', 'C') THEN
4883 arp_util.debug('l_trx_select_statement = ' || l_trx_select_statement);
4884 END IF;
4885
4886 --------------------------------------------------------------------------
4887 ---- FIRST LOOP
4888 --------------------------------------------------------------------------
4889 OPEN c_grouping FOR l_trx_select_statement
4890 using p_customer_trx_id, p_customer_id;
4891
4892 LOOP
4893
4894 l_customer_id := NULL;
4895 l_due_date := NULL;
4896 l_site_use_id := NULL;
4897 l_customer_trx_id := NULL;
4898 l_payment_schedule_id := NULL;
4899
4900 IF (l_receipt_creation_rule_code = 'PER_CUSTOMER') THEN
4901 FETCH c_grouping into l_customer_id;
4902 ELSIF (l_receipt_creation_rule_code = 'PER_CUSTOMER_DUE_DATE') THEN
4903 FETCH c_grouping into l_customer_id, l_due_date;
4904 ELSIF (l_receipt_creation_rule_code = 'PER_SITE') THEN
4905 FETCH c_grouping into l_site_use_id;
4906 ELSIF (l_receipt_creation_rule_code = 'PER_SITE_DUE_DATE') THEN
4907 FETCH c_grouping into l_site_use_id, l_due_date;
4908 ELSIF (l_receipt_creation_rule_code = 'PER_INVOICE') THEN
4909 FETCH c_grouping into l_customer_trx_id;
4910 ELSE
4911 FETCH c_grouping into l_payment_schedule_id;
4912 END IF;
4913
4914 EXIT WHEN c_grouping%NOTFOUND;
4915
4916
4917 l_trx_select_statement := 'SELECT ps.payment_schedule_id,ps.customer_trx_id,ps.cust_trx_type_id,'||
4918 'ps.customer_id,ps.customer_site_use_id,ps.trx_number,ps.due_date,ps.amount_due_remaining '||
4919 l_suffixe_select_statement;
4920
4921 l_trx_select_statement := l_trx_select_statement ||
4922 'AND ps.customer_id = NVL(:p_customer_id,ps.customer_id) '||
4923 'AND ps.due_date = NVL(:p_due_date,ps.due_date) '||
4924 'AND ps.customer_site_use_id = NVL(:p_customer_site_use_id,ps.customer_site_use_id) '||
4925 'AND ps.payment_schedule_id = NVL(:p_payment_schedule_id,ps.payment_schedule_id) ';
4926
4927
4928 ----------------------------------------------------------------------------------------------
4929 ---- SECOND LOOP - the bill receivable is created according to the transaction receipt method
4930 ----------------------------------------------------------------------------------------------
4931 l_delete_statement := 'DELETE FROM '|| g_tmp_table_nimm;
4932 execute immediate l_delete_statement;
4933
4934 OPEN c_trx FOR l_trx_select_statement
4935 using p_customer_trx_id, p_customer_id,
4936 l_customer_id, l_due_date, l_site_use_id, l_payment_schedule_id;
4937
4938 l_insert_statement := 'INSERT INTO '|| g_tmp_table_nimm ||
4939 '(payment_schedule_id,customer_trx_id,cust_trx_type_id,customer_id,'||
4940 'customer_site_use_id,trx_number,due_date,amount_due_remaining,amount_assigned,exclude_flag) '||
4941 'VALUES (:payment_schedule_id,:customer_trx_id,:cust_trx_type_id,:customer_id,'||
4942 ':customer_site_use_id,:trx_number,:due_date,:amount_due_remaining,NULL,NULL) ';
4943
4944
4945 LOOP
4946
4947 FETCH c_trx into l_payment_schedule_id,
4948 l_customer_trx_id,
4949 l_cust_trx_type_id,
4950 l_customer_id,
4951 l_customer_site_use_id,
4952 l_trx_number,
4953 l_due_date,
4954 l_amount_due_remaining;
4955
4956 EXIT WHEN c_trx%NOTFOUND;
4957
4958
4959 execute immediate l_insert_statement
4960 USING l_payment_schedule_id,
4961 l_customer_trx_id,
4962 l_cust_trx_type_id,
4963 l_customer_id,
4964 l_customer_site_use_id,
4965 l_trx_number,
4966 l_due_date,
4967 l_amount_due_remaining;
4968
4969 ---- SECOND LOOP END
4970 END LOOP;
4971 CLOSE c_trx;
4972
4973
4974 -- create the bills receivable
4975
4976 -- bug 1808976 : pass customer_bank_account_id to create_br
4977 ARP_PROGRAM_GENERATE_BR.create_BR(
4978 'N', -- p_draft_mode
4979 3, -- p_call
4980 NULL, -- p_batch_id
4981 l_receipt_method_id,
4982 l_receipt_creation_rule_code,
4983 l_maturity_date_rule_code,
4984 l_br_min_acctd_amount,
4985 l_br_max_acctd_amount,
4986 l_invoice_currency_code,
4987 l_customer_bank_account_id,
4988 l_le_id,
4989 l_bill_id,
4990 l_request_id);
4991
4992
4993 ---- FIRST LOOP END
4994 END LOOP;
4995 CLOSE c_grouping;
4996
4997 IF (p_commit = FND_API.G_TRUE) THEN
4998 COMMIT;
4999 END IF;
5000
5001
5002 p_nb_bill := g_num_br_created;
5003
5004 IF g_num_br_created > 1 THEN
5005 p_bill_id := NULL;
5006 p_request_id := NULL;
5007 ELSE
5008 p_bill_id := l_bill_id;
5009 p_request_id := l_request_id;
5010 END IF;
5011
5012 /*Bug2290332*/
5013 IF g_num_br_created = 0 THEN
5014 FND_MESSAGE.set_name('AR','AR_BR_NOT_VALID_CONDITION');
5015 APP_EXCEPTION.raise_exception;
5016 END IF;
5017
5018 --Temporary table Drop
5019 ARP_PROGRAM_GENERATE_BR.drop_tmp_table;
5020
5021 IF PG_DEBUG in ('Y', 'C') THEN
5022 arp_util.debug('ARP_PROGRAM_GENERATE_BR.auto_create_br_API (-)');
5023 END IF;
5024
5025
5026
5027 EXCEPTION
5028 WHEN FND_API.G_EXC_ERROR THEN
5029 IF PG_DEBUG in ('Y', 'C') THEN
5030 arp_util.debug('EXCEPTION FND_API.G_EXC_ERROR:ARP_PROGRAM_GENERATE_BR.auto_create_br_API ');
5031 END IF;
5032 x_return_status := FND_API.G_RET_STS_ERROR;
5033
5034 IF c_grouping%ISOPEN THEN
5035 CLOSE c_grouping;
5036 END IF;
5037
5038 IF c_receipt_method%ISOPEN THEN
5039 CLOSE c_receipt_method;
5040 END IF;
5041
5042 IF c_trx%ISOPEN THEN
5043 CLOSE c_trx;
5044 END IF;
5045
5046 ARP_PROGRAM_GENERATE_BR.drop_tmp_table;
5047 raise;
5048
5049 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5050 IF PG_DEBUG in ('Y', 'C') THEN
5051 arp_util.debug('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR: ARP_PROGRAM_GENERATE_BR.auto_create_br_API');
5052 END IF;
5053 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5054
5055 IF c_grouping%ISOPEN THEN
5056 CLOSE c_grouping;
5057 END IF;
5058
5059 IF c_receipt_method%ISOPEN THEN
5060 CLOSE c_receipt_method;
5061 END IF;
5062
5063 IF c_trx%ISOPEN THEN
5064 CLOSE c_trx;
5065 END IF;
5066
5067 ARP_PROGRAM_GENERATE_BR.drop_tmp_table;
5068 raise;
5069
5070 WHEN OTHERS THEN
5071 IF PG_DEBUG in ('Y', 'C') THEN
5072 arp_util.debug('EXCEPTION OTHERS: ARP_PROGRAM_GENERATE_BR.auto_create_br_API');
5073 END IF;
5074
5075 IF c_grouping%ISOPEN THEN
5076 CLOSE c_grouping;
5077 END IF;
5078
5079 IF c_receipt_method%ISOPEN THEN
5080 CLOSE c_receipt_method;
5081 END IF;
5082
5083 IF c_trx%ISOPEN THEN
5084 CLOSE c_trx;
5085 END IF;
5086
5087 ARP_PROGRAM_GENERATE_BR.drop_tmp_table;
5088 IF (SQLCODE = -20001) THEN
5089 x_return_status := FND_API.G_RET_STS_ERROR;
5090 END IF;
5091
5092 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5093 raise;
5094
5095 END auto_create_br_API;
5096
5097
5098
5099 /*===========================================================================+
5100 | FUNCTION |
5101 | revision |
5102 | |
5103 | DESCRIPTION |
5104 | This function returns the revision number of this package. |
5105 | |
5106 | SCOPE - PUBLIC |
5107 | |
5108 | RETURNS : Revision number of this package |
5109 | |
5110 | MODIFICATION HISTORY |
5111 | 10 JAN 2001 John HALL Created |
5112 +===========================================================================*/
5113 FUNCTION revision RETURN VARCHAR2 IS
5114 BEGIN
5115 RETURN '$Revision: 120.20.12000000.3 $';
5116 END revision;
5117 --
5118
5119 BEGIN
5120
5121 select org_id
5122 into g_org_id
5123 from ar_system_parameters;
5124
5125
5126 END ARP_PROGRAM_GENERATE_BR;