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