[Home] [Help]
PACKAGE BODY: APPS.FV_SF1081_TRANSACTION
Source
1 PACKAGE BODY FV_SF1081_TRANSACTION AS
2 --$Header: FVX1081B.pls 120.17.12010000.5 2009/10/09 17:09:34 snama ship $
3 -- l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
4 g_module_name VARCHAR2(100) ;
5
6
7 -- ------------------------------------
8 -- Stored Input Parameters
9 -- ------------------------------------
10 parm_order_by VARCHAR2(30);
11 parm_batch NUMBER(15);
12 parm_transaction_class VARCHAR2(30);
13 parm_transaction_type NUMBER(15);
14 parm_trans_num_low VARCHAR2(20);
15 parm_trans_num_high VARCHAR2(20);
16 parm_print_date_low DATE;
17 parm_print_date_high DATE;
18 parm_cust_profile_class_id NUMBER(15);
19 parm_customer_class VARCHAR2(30);
20 parm_customer VARCHAR2(50);
21 parm_alc VARCHAR2(50);
22 parm_open_invoices_only VARCHAR2(3);
23 parm_print_choice VARCHAR2(30);
24 parm_details_of_charges VARCHAR2(50);
25
26 -- ------------------------------------
27 -- Stored Global Variables
28 -- ------------------------------------
29 g_error_code NUMBER;
30 g_error_message VARCHAR2(80);
31 v_segment VARCHAR2(25);
32 v_set_of_books_id NUMBER;
33 v_org_id NUMBER;
34 v_bal_seg_name VARCHAR2(25);
35 v_default_alc VARCHAR2(30);
36 v_warning VARCHAR2(1);
37 v_exception VARCHAR2(1);
38 v_trx_found_2 varchar2(1) ;
39 v_trx_found_1 VARCHAR2(1) ;
40 v_trx_found_3 VARCHAR2(1) ;
41 v_warning_num NUMBER;
42 v_alc_code VARCHAR2(30);
43
44 PROCEDURE a100_clear_report_temp_table;
45
46 PROCEDURE a200_load_report_tables;
47
48 PROCEDURE get_bal_seg_name;
49
50 abort_error EXCEPTION;
51 report_failure EXCEPTION;
52
53 -- ---------- End of Package Level Declaritives -----------------------------
54
55 PROCEDURE a000_load_table
56 (error_code OUT NOCOPY NUMBER,
57 error_message OUT NOCOPY VARCHAR2,
58 order_by IN VARCHAR2,
59 batch IN NUMBER,
60 transaction_class IN VARCHAR2,
61 transaction_type IN NUMBER,
62 trans_num_low IN VARCHAR2,
63 trans_num_high IN VARCHAR2,
64 print_date_low IN VARCHAR2,
65 print_date_high IN VARCHAR2,
66 cust_profile_class_id IN NUMBER,
67 customer_class IN VARCHAR2,
68 customer IN VARCHAR2,
69 alc IN VARCHAR2,
70 prepared_by IN VARCHAR2,
71 approved_by IN VARCHAR2,
72 telephone_number_1 IN VARCHAR2,
73 telephone_number_2 IN VARCHAR2,
74 open_invoices_only IN VARCHAR2,
75 print_choice IN VARCHAR2,
76 details_of_charges IN VARCHAR2)
77 IS
78 l_module_name VARCHAR2(200) ;
79 l_request_id number;
80
81 CURSOR c_warning_1 IS
82 select distinct trx_number
83 from fv_sf1081_temp
84 where alc_code = '1';
85
86 CURSOR c_warning_2 IS
87 select distinct trx_number
88 from fv_sf1081_temp
89 where alc_code = '2';
90
91 CURSOR c_warning_3 IS
92 select distinct customer_name
93 from fv_sf1081_temp
94 where alc_code = '3';
95
96 BEGIN
97
98
99 l_module_name := g_module_name || 'a000_load_table';
100
101
102 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
103 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Parameters: ');
104 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'order_by: '||order_by);
105 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'batch: '||batch);
106 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'transaction_class: '||transaction_class);
107 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'transaction_type: '||transaction_type);
108 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'trans_num_low: '||trans_num_low);
109 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'trans_num_high: '||trans_num_high);
110 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'print_date_low: '||print_date_low);
111 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'print_date_high: '||print_date_high);
112 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cust_profile_class_id: '||cust_profile_class_id);
113 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'customer_class: '||customer_class);
114 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'customer: '||customer);
115 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'alc: '||alc);
116 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'open_invoices_only: '||open_invoices_only);
117 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'print_choice: '||print_choice);
118 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'details_of_charges: '||details_of_charges);
119 END IF;
120
121
122
123
124 -- ------------------------------------------
125 -- Store Input Parameters in Global Variables
126 -- ------------------------------------------
127 parm_order_by := order_by;
128 parm_batch := batch;
129 parm_cust_profile_class_id := cust_profile_class_id;
130 parm_transaction_class := transaction_class;
131 parm_transaction_type := transaction_type;
132 parm_trans_num_low := trans_num_low;
133 parm_trans_num_high := trans_num_high;
134 parm_print_date_low := FND_DATE.CANONICAL_TO_DATE(print_date_low);
135 parm_print_date_high := FND_DATE.CANONICAL_TO_DATE(print_date_high);
136 parm_customer_class := customer_class;
137 parm_customer := customer;
138 parm_alc := alc;
139 parm_open_invoices_only := open_invoices_only;
140 parm_details_of_charges := details_of_charges;
141 parm_print_choice := print_choice;
142
143 error_code := 0;
144 error_message := '?';
145 g_error_code := 0;
146 g_error_message := '?';
147
148 a100_clear_report_temp_table;
149
150 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
151 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'g_error_code after clear temp table = ' || g_error_code);
152 END IF;
153
154
155 IF g_error_code = 0 THEN
156 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
157 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CALLING A200_LOAD_REPORT_TABLES');
158 END IF;
159 a200_load_report_tables;
160 -- FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Process Complete');
161 END IF;
162
163 IF g_error_code <> 0 THEN
164 RAISE abort_error;
165 END IF;
166
167 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
168 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'After a200_load_report_tables');
169 END IF;
170
171 -- print error messages to log file if there are any warnings.
172 IF v_warning = 'Y' THEN
173 g_error_code := '1'; --process should end in warning
174 fv_utility.log_mesg('The receipt method has not been defined for the following transactions: ');
175
176 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
177 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE PAYMENT METHOD HAS NOT BEEN DEFINED IN THE '||
178 'Paying Customer tab for the following Transactions: ');
179 END IF;
180
181 FOR c_warning1_rec IN c_warning_1 LOOP
182 v_trx_found_1 := 'Y';
183
184 fv_utility.log_mesg(C_WARNING1_REC.TRX_NUMBER);
185
186 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
187 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,C_WARNING1_REC.TRX_NUMBER);
188 END IF;
189 END LOOP;
190
191 IF v_trx_found_1 = 'N' THEN
192 fv_utility.log_mesg('No Transactions have this exception.');
193 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
194 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO TRANSACTIONS HAVE THIS EXCEPTION.');
195 END IF;
196 END IF;
197
198 fv_utility.log_mesg('The Agency Location Code has not been defined for ');
199 fv_utility.log_mesg('the Primary bank account of the payment method for the following transactions: ');
200 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
201 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE AGENCY LOCATION CODE HAS NOT BEEN DEFINED FOR '||
202 'the Primary bank account of the payment method for the following '||
203 'Transactions: ');
204 END IF;
205
206 FOR c_warning2_rec IN c_warning_2 LOOP
207 v_trx_found_2 := 'Y';
208
209 fv_utility.log_mesg(C_WARNING2_REC.TRX_NUMBER);
210
211 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
212 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,C_WARNING2_REC.TRX_NUMBER);
213 END IF;
214 END LOOP;
215
216 IF v_trx_found_2 = 'N' THEN
217
218 fv_utility.log_mesg('No transactions have this exception.');
219
220 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
221 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO TRANSACTIONS HAVE THIS EXCEPTION.');
222 END IF;
223 END IF;
224 fv_utility.log_mesg('The customer agency location code has not been defined for the ');
225 fv_utility.log_mesg('primary bank account for the following customers: ');
226
227 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
228 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'THE CUSTOMER AGENCY LOCATION CODE HAS NOT BEEN '||
229 'defined for the Primary bank account for the following Customers: ');
230 END IF;
231
232 FOR c_warning3_rec IN c_warning_3 LOOP
233 v_trx_found_3 := 'Y';
234
235 fv_utility.log_mesg(C_WARNING3_REC.CUSTOMER_NAME);
236
237 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
238 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,C_WARNING3_REC.CUSTOMER_NAME);
239 END IF;
240 END LOOP;
241
242 IF v_trx_found_3 = 'N' THEN
243
244 fv_utility.log_mesg('No customers have this exception.');
245
246 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
247 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO CUSTOMERS HAVE THIS EXCEPTION.');
248 END IF;
249 END IF;
250
251 delete from fv_sf1081_temp where alc_code in ('1','2','3');
252 END IF;
253
254 error_code := g_error_code;
255 error_message := 'Normal End of FVSF1081 Package';
256 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
257 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Process Complete');
258 END IF;
259
260 fnd_request.set_org_id(mo_global.get_current_org_id);
261 --- Kick Off the SL1081 report
262 l_request_id :=fnd_request.submit_request(
263 application => 'FV',
264 program => 'FVSF1081',
265 description => '',
266 start_time => '',
267 sub_request => FALSE,
268 argument1 => order_by,
269 argument2 => prepared_by,
270 argument3 => approved_by,
271 argument4 => telephone_number_1,
272 argument5 => telephone_number_2,
273 argument6 => details_of_charges);
274 IF (l_request_id = 0) THEN
275 RAISE report_failure;
276 ELSE
277 COMMIT;
278 END IF;
279
280 EXCEPTION
281
282 WHEN abort_error THEN
283 error_code := 2;
284 error_message := g_error_message;
285 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.abort_error',g_error_message);
286
287
288 WHEN report_failure THEN
289 g_error_message := 'Submission of FVSF1081 Report failed';
290 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.report_failure',g_error_message);
291
292
293 WHEN OTHERS THEN
294 error_code := 2;
295 error_message := sqlerrm;
296 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_message);
297 RAISE_APPLICATION_ERROR(-20222,'FVSF1081 Exception-'||error_message);
298
299 END a000_load_table;
300
301 -- -------------------------------------------------------
302 -- This procedure derives the balancing segment name.
303 -- -------------------------------------------------------
304
305 PROCEDURE get_bal_seg_name
306 IS
307 l_module_name VARCHAR2(200) ;
308 v_boolean boolean;
309 sob number;
310 flex_num number;
311 flex_code varchar2(60) ;
312 apps_id number := 101;
313 seg_number number;
314 bl_seg_name varchar2(60);
315 seg_app_name varchar2(60);
316 seg_prompt varchar2(60);
317 seg_value_set_name varchar2(60);
318
319 BEGIN
320
321
322 l_module_name := g_module_name || 'get_bal_seg_name';
323 flex_code := 'GL#';
324
325 -- sob := TO_NUMBER(fnd_profile.value('GL_SET_OF_BKS_ID'));
326
327 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
328 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SOB ID: '||to_char(v_set_of_books_id));
329 END IF;
330
331 SELECT chart_of_accounts_id INTO flex_num
332 FROM gl_sets_of_books
333 WHERE set_of_books_id = v_set_of_books_id;
334
335 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
336 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'FLEX NUM: '||to_char(flex_num));
337 END IF;
338
339 v_boolean := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(apps_id,flex_code,flex_num,
340 'GL_BALANCING',seg_number);
341
342 IF (v_boolean) THEN
343 v_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO
344 (apps_id,flex_code,flex_num,seg_number,bl_seg_name,
345 seg_app_name,seg_prompt,seg_value_set_name);
346 END IF;
347
348 v_segment := ' glc.'||bl_seg_name;
349 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
350 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,'segment name: '||v_segment);
351 END IF;
352 v_bal_seg_name := UPPER(bl_seg_name);
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 g_error_code := SQLCODE;
357 g_error_message := SQLERRM;
358 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_message);
359 RAISE;
360 END get_bal_seg_name ;
361
362 -- -------------------------------------------------------
363 -- This procedure deletes records from temp table.
364 -- -------------------------------------------------------
365 PROCEDURE a100_clear_report_temp_table
366
367 IS
368 l_module_name VARCHAR2(200) ;
369
370 BEGIN
371
372 l_module_name := g_module_name || 'a100_clear_report_temp_table';
373
374 -- FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'0_clear_reporxemp_table');
375 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
376 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Start a100_clear_report_temp_table');
377 END IF;
378
379 DELETE
380 FROM fv_sf1081_temp;
381
382 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
383 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Deleting from FV_SF1081_TEMP');
384 END IF;
385
386 EXCEPTION
387 WHEN NO_DATA_FOUND THEN
388 NULL;
389 WHEN OTHERS THEN
390 g_error_code := SQLCODE;
391 g_error_message := 'a100_clear_report_header_table: /'||SQLERRM;
392 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_message);
393 COMMIT;
394 END a100_clear_report_temp_table ;
395
396 -- --------------------------------------------------------
397 -- This procedure loads the temp table by reading
398 -- records from the cursor.
399 -- --------------------------------------------------------
400
401 PROCEDURE a200_load_report_tables
402 IS
403 l_module_name VARCHAR2(200) ;
404 --
405 l_ledger_id number;
406 l_ledger_name varchar2(200);
407 --
408 vc_trx_number ra_customer_trx.trx_number%TYPE;
409 vc_receipt_method_id ra_customer_trx.receipt_method_id%TYPE;
410 vc_customer_name hz_parties.party_name%TYPE;
411 vc_customer_id hz_cust_accounts.cust_Account_id%TYPE;
412 vc_cust_alc ce_bank_accounts.agency_location_code%TYPE;
413 vc_address_id hz_cust_acct_sites.cust_acct_site_id%type;
414 vc_address1 hz_locations.address1%TYPE;
415 vc_address2 hz_locations.address2%TYPE;
416 vc_address3 hz_locations.address3%TYPE;
417 vc_city hz_locations.city%TYPE;
418 vc_state hz_locations.state%TYPE;
419 vc_postal_code hz_locations.postal_code%TYPE;
420 vc_customer_trx_id ra_customer_trx.customer_trx_id%TYPE;
421 vc_amount ra_cust_trx_line_gl_dist.amount%TYPE;
422 vc_bill_to_customer_id ra_customer_trx.bill_to_customer_id%TYPE;
423 vc_trx_date ra_customer_trx.trx_date%TYPE;
424 vc_code_combination_id number;
425
426 v_treasury_symbol fv_treasury_symbols.treasury_symbol%TYPE;
427 err number;
428
429 -- Bug 4960824
430 CURSOR ts_report_noalc_cursor
431 IS
432 SELECT
433 RCT.TRX_NUMBER,
434 RCT.RECEIPT_METHOD_ID,
435 HZP.PARTY_NAME,
436 HZCA.CUST_ACCOUNT_ID,
437 HZCAS.CUST_ACCT_SITE_ID,
438 HZL.ADDRESS1,
439 HZL.ADDRESS2,
440 HZL.ADDRESS3,
441 HZL.CITY,
442 HZL.STATE,
443 HZL.POSTAL_CODE,
444 RCT.CUSTOMER_TRX_ID,
445 SUM(RLD.AMOUNT) AMOUNT,
446 RCT.BILL_TO_CUSTOMER_ID,
447 RCT.TRX_DATE,
448 RLD.CODE_COMBINATION_ID
449 FROM
450 RA_CUSTOMER_TRX RCT,
451 RA_CUSTOMER_TRX_LINES RTL,
452 RA_CUST_TRX_LINE_GL_DIST RLD,
453 HZ_CUST_SITE_USES HZCSU,
454 HZ_LOCATIONS HZL,
455 HZ_CUST_ACCT_SITES HZCAS,
456 HZ_CUST_ACCOUNTS HZCA,
457 HZ_PARTY_SITES HZPS,
458 HZ_PARTIES HZP
459 WHERE
460 RCT.CUSTOMER_TRX_ID = RTL.CUSTOMER_TRX_ID
461 AND RTL.CUSTOMER_TRX_LINE_ID = RLD.CUSTOMER_TRX_LINE_ID
462 AND RCT.COMPLETE_FLAG = 'Y'
463 AND RCT.PRINTING_OPTION = 'PRI'
464 AND RCT.SET_OF_BOOKS_ID = v_set_of_books_id
465 AND RCT.TRX_NUMBER BETWEEN NVL( parm_trans_num_low ,'0')
466 AND NVL( parm_trans_num_high ,'zzzzzzzzzzzzzzzzzzzz')
467 AND RCT.TRX_DATE BETWEEN NVL( parm_print_date_low , TO_DATE('1990/1/1', 'yyyy/mm/dd'))
468 AND NVL( parm_print_date_high , TRUNC(SYSDATE))
469 AND NVL(RCT.STATUS_TRX,'-1') LIKE DECODE( parm_open_invoices_only ,'Y','OP','N','%')
470 AND HZPS.PARTY_ID = HZCA.PARTY_ID
471 AND HZP.PARTY_ID = HZPS.PARTY_ID
472 AND RCT.BILL_TO_SITE_USE_ID = HZCSU.SITE_USE_ID
473 AND HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID
474 AND HZCAS.PARTY_SITE_ID = HZPS.PARTY_SITE_ID
475 AND HZPS.LOCATION_ID = HZL.LOCATION_ID
476 AND HZCAS.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID
477 AND hzca.cust_account_id IN
478 ((SELECT cust_account_id
479 FROM hz_cust_accounts hzca
480 WHERE NVL(customer_class_code,'XXX') LIKE
481 DECODE(parm_customer_class,null,
482 NVL(customer_class_code,'XXX'),parm_customer_class))
483 INTERSECT
484 (SELECT cust_account_id
485 FROM hz_cust_accounts hzca
486 WHERE cust_account_id LIKE NVL(parm_customer,'%')))
487 AND RCT.BILL_TO_CUSTOMER_ID IN
488 (SELECT HCP.CUST_ACCOUNT_ID
489 FROM HZ_CUSTOMER_PROFILES HCP
490 WHERE HCP.CUST_ACCOUNT_ID = RCT.BILL_TO_CUSTOMER_ID
491 AND HCP.PROFILE_CLASS_ID = NVL(parm_cust_profile_class_id , HCP.PROFILE_CLASS_ID))
492 AND RCT.CUST_TRX_TYPE_ID IN
493 (SELECT CUST_TRX_TYPE_ID
494 FROM RA_CUST_TRX_TYPES RCTT
495 WHERE RCTT.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
496 AND ( RCTT.TYPE LIKE NVL(parm_transaction_class ,'%')
497 OR RCTT.CUST_TRX_TYPE_ID = NVL(parm_transaction_type, RCTT.CUST_TRX_TYPE_ID)))
498 AND ((RCT.BATCH_ID IN
499 (SELECT BATCH_ID
500 FROM RA_BATCHES RB
501 WHERE RB.BATCH_ID = RCT.BATCH_ID
502 AND RB.BATCH_ID = NVL( parm_batch ,RB.BATCH_ID)))
503 OR
504 (parm_batch is null and NVL(BATCH_ID,'99') LIKE DECODE( parm_print_choice ,'SEL','99','NEW','99')))
505 AND NVL(RCT.PRINTING_COUNT,'99') LIKE DECODE(parm_print_choice ,'NEW','99','%')
506 AND NVL(TO_CHAR(RCT.PRINTING_ORIGINAL_DATE,'DD-MM-YYYY'),'01-01-1999')
507 LIKE DECODE(parm_print_choice ,'NEW','01-01-1999','%')
508 AND NVL(TO_CHAR(RCT.PRINTING_LAST_PRINTED,'DD-MM-YYYY'),'01-01-1999')
509 LIKE DECODE(parm_print_choice ,'NEW','01-01-1999','%')
510 GROUP BY
511 RCT.TRX_NUMBER,
512 RCT.RECEIPT_METHOD_ID,
513 HZP.PARTY_NAME,
514 HZCA.CUST_ACCOUNT_ID,
515 HZCAS.CUST_ACCT_SITE_ID,
516 HZL.ADDRESS1,
517 HZL.ADDRESS2,
518 HZL.ADDRESS3,
519 HZL.CITY,
520 HZL.STATE,
521 HZL.POSTAL_CODE,
522 RCT.CUSTOMER_TRX_ID,
523 RCT.BILL_TO_CUSTOMER_ID,
524 RCT.TRX_DATE,
525 RLD.CODE_COMBINATION_ID
526 ORDER BY parm_order_by;
527
528
529 CURSOR ts_report_alc_cursor
530 IS
531 SELECT rct.trx_number,
532 rct.receipt_method_id,
533 hzp.party_name,
534 hzca.cust_account_id,
535 cba.agency_location_code,
536 hzl.address1,
537 hzl.address2,
538 hzl.address3,
539 hzl.city,
540 hzl.state,
541 hzl.postal_code,
542 rct.customer_trx_id,
543 sum(rld.amount) Amount,
544 rct.bill_to_customer_id,
545 rct.trx_date,
546 rld.code_combination_id
547 FROM
548 hz_locations hzl,
549 hz_cust_acct_sites hzcas,
550 hz_party_sites hzps ,
551 HZ_CUST_SITE_USEs hzcsu,
552 hz_cust_accounts hzca,
553 hz_parties hzp,
554 ra_customer_trx rct,
555 ra_customer_trx_lines rtl,
556 ra_cust_trx_line_gl_dist rld,
557 ce_bank_accounts cba,
558 ce_bank_acct_uses_all cbau
559 WHERE
560 hzp.party_id = hzca.party_id
561 AND hzca.cust_account_id = hzcas.cust_account_id
562 AND hzcas.party_site_id = hzps.party_site_id
563 AND hzps.location_id = hzl.location_id
564 AND hzcsu.Cust_Acct_site_ID = hzcas.CUST_ACCT_SITE_ID
565 AND hzps.party_id = hzp.party_id
566
567 AND rct.bill_to_site_use_id = hzcsu.site_use_id
568 AND rct.remit_bank_acct_use_id = cbau.bank_acct_use_id
569
570 AND cba.bank_account_id = cbau.bank_account_id
571 AND cba.account_owner_party_id = cbau.org_party_id
572 AND cba.account_classification = 'EXTERNAL'
573 AND cbau.org_id = v_org_id
574 AND cba.account_owner_org_id = cbau.org_id
575 AND cbau.primary_flag = 'Y'
576 AND cba.account_owner_party_id = hzp.party_id
577 AND cba.agency_location_code = parm_alc
578 AND rct.complete_flag = 'Y'
579 AND rct.printing_option = 'PRI'
580 AND rtl.customer_trx_line_id = rld.customer_trx_line_id
581 AND rct.customer_trx_id = rtl.customer_trx_id
582 AND rct.set_of_books_id = v_set_of_books_id
583 AND rct.bill_to_customer_id IN
584 (SELECT DISTINCT cust_account_id
585 FROM hz_customer_profiles
586 WHERE profile_class_id =
587 DECODE(parm_cust_profile_class_id,null,profile_class_id,
588 parm_cust_profile_class_id))
589 AND rct.cust_trx_type_id IN
590 ((SELECT cust_trx_type_id
591 FROM ra_cust_trx_types
592 WHERE type LIKE NVL(parm_transaction_class,'%'))
593 INTERSECT
594 (SELECT cust_trx_type_id
595 FROM ra_cust_trx_types
596 WHERE cust_trx_type_id = DECODE(parm_transaction_type,null,
597 cust_trx_type_id,parm_transaction_type)))
598 AND (rct.trx_number BETWEEN NVL(parm_trans_num_low,'0')
599 AND NVL(parm_trans_num_high,'zzzzzzzzzzzzzzzzzzzz'))
600 AND rct.trx_date BETWEEN DECODE(parm_print_date_low,null,TO_DATE('1990/1/1', 'yyyy/mm/dd'), parm_print_date_low)
601 AND DECODE(parm_print_date_high,null,trunc(sysdate),parm_print_date_high )
602 AND hzca.cust_account_id IN
603 ((SELECT cust_account_id
604 FROM hz_cust_accounts hzca
605 WHERE NVL(customer_class_code,'XXX') LIKE DECODE(parm_customer_class,null,
606 NVL(customer_class_code,'XXX'),parm_customer_class))
607 INTERSECT
608 (SELECT cust_account_id
609 FROM hz_cust_accounts hzca
610 WHERE cust_account_id LIKE NVL(parm_customer,'%')))
611 AND rct.customer_trx_id IN
612 (SELECT customer_trx_id
613 FROM ra_customer_trx
614 WHERE NVL(status_trx,'-1') LIKE decode(parm_open_invoices_only,'Y','OP','N','%'))
615 AND ((rct.customer_trx_id IN
616 (SELECT customer_trx_id
617 FROM ra_customer_trx
618 WHERE batch_id IN
619 (SELECT batch_id
620 FROM ra_batches
621 WHERE batch_id = DECODE(parm_batch,null,BATCH_ID,parm_batch))))
622 OR (parm_batch is null and (NVL(BATCH_ID,'99') LIKE DECODE(parm_print_choice,'SEL','99','NEW','99'))))
623 AND rct.customer_trx_id IN
624 (SELECT rct.customer_trx_id
625 FROM ra_customer_trx rct
626 WHERE (NVL(rct.printing_count,'99') LIKE
627 DECODE(parm_print_choice,'NEW','99','%'))
628 AND (NVL(TO_CHAR(rct.printing_original_date,'DD-MM-YYYY'),'01-01-1999') LIKE
629 DECODE(parm_print_choice,'NEW','01-01-1999','%'))
630 AND (NVL(TO_CHAR(rct.printing_last_printed,'DD-MM-YYYY'),'01-01-1999') LIKE
631 DECODE(parm_print_choice,'NEW','01-01-1999','%')))
632 GROUP BY rct.trx_number,cba.agency_location_code,rct.receipt_method_id,
633 hzp.party_name, hzca.cust_account_id,
634 hzl.address1,hzl.address2,hzl.address3,hzl.city,hzl.state,hzl.postal_code,
635 rct.customer_trx_id,rct.bill_to_customer_id, rct.trx_date, rld.code_combination_id
636 ORDER BY parm_order_by;
637
638 BEGIN
639
640 l_module_name := g_module_name ||'a200_load_report_tables';
641
642 v_org_id := mo_global.get_current_org_id;
643 mo_utils.get_ledger_info(v_org_id,l_ledger_id,l_ledger_name);
644 v_set_of_books_id := l_ledger_id;
645
646 get_bal_seg_name;
647
648 BEGIN
649 -- get default alc for this org if there is one. cmb
650 select alc_code
651 into v_default_alc
652 from fv_operating_units_all
653 where default_alc = 'Y'
654 and nvl(org_id,-99) = nvl(v_org_id,-99);
655
656 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
657 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Default ALC Defined = '||v_default_alc);
658 END IF;
659
660 EXCEPTION
661 when others then
662 v_default_alc := 'N'; -- no default alc was found
663 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
664 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_default_alc = N');
665 END IF;
666 END;
667
668 -- This for loop inserts values into FV_SF1081_TEMP table.
669 v_warning := 'N'; -- set warning condition to No cmb
670
671 IF parm_alc is not null THEN
672 open ts_report_alc_cursor;
673 ELSE
674 open ts_report_noalc_cursor;
675 END IF;
676
677 LOOP
678
679 v_exception := 'N';
680
681 IF parm_alc is not null THEN
682 FETCH ts_report_alc_cursor INTO vc_trx_number, vc_receipt_method_id,
683 vc_customer_name,vc_customer_id, vc_cust_alc, vc_address1,
684 vc_address2, vc_address3, vc_city, vc_state, vc_postal_code,
685 vc_customer_trx_id, vc_amount,
686 vc_bill_to_customer_id, vc_trx_date, vc_code_combination_id;
687
688 EXIT when ts_report_alc_cursor%NOTFOUND;
689
690 ELSE
691 FETCH ts_report_noalc_cursor INTO vc_trx_number, vc_receipt_method_id,
692 vc_customer_name, vc_customer_id,vc_address_id,vc_address1, vc_address2, vc_address3,
693 vc_city, vc_state, vc_postal_code, vc_customer_trx_id, vc_amount,
694 vc_bill_to_customer_id, vc_trx_date, vc_code_combination_id;
695
696 EXIT when ts_report_noalc_cursor%NOTFOUND;
697 END IF;
698
699 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
700 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRX_NUMBER = '||VC_TRX_NUMBER);
701 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'RECEIPT_METHOD_ID = '||VC_RECEIPT_METHOD_ID);
702 END IF;
703
704 IF (parm_alc is null) THEN
705 --determine customer alc for the customer on the invoice.
706
707 BEGIN
708
709 SELECT eb.agency_location_code
710 INTO vc_cust_alc
711 FROM hz_cust_acct_sites_all hzcas,
712 hz_cust_site_uses_all hzcsu,
713 iby_external_payers_all payer,
714 iby_pmt_instr_uses_all iby_ins,
715 iby_ext_bank_accounts_v eb
716 WHERE hzcas.cust_account_id = vc_customer_id
717 AND hzcas.cust_acct_site_id = vc_address_id
718 AND hzcsu.cust_acct_site_id=hzcas.cust_acct_site_id
719 AND hzcsu.site_use_code = 'BILL_TO'
720 AND hzcsu.site_use_id = payer.acct_site_use_id
721 AND payer.ext_payer_id= iby_ins.ext_pmt_party_id
722 AND iby_ins.instrument_type = 'BANKACCOUNT'
723 AND iby_ins.instrument_id = eb.ext_bank_account_id
724 --Bug8654464
725 --AND iby_ins.start_date < vc_trx_date
726 AND Decode(iby_ins.start_date,NULL,(vc_trx_date-1),iby_ins.start_date) < vc_trx_date
727 AND (Decode(iby_ins.end_date,NULL,Sysdate,iby_ins.end_date))> vc_trx_date
728 and iby_ins.payment_function = 'CUSTOMER_PAYMENT' ;
729
730 EXCEPTION
731 when no_data_found then
732 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
733 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO CUSTOMER ALC FOUND');
734 END IF;
735 v_warning := 'Y';
736 v_warning_num := '3';
737 v_exception := 'Y';
738 END;
739
740 IF vc_cust_alc is null THEN
741 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
742 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO CUSTOMER ALC DEFINED.');
743 END IF;
744 v_warning := 'Y';
745 v_warning_num := '3';
746 v_exception := 'Y';
747 END If;
748
749 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
750 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'VC_CUST_ALC = '||VC_CUST_ALC);
751 END IF;
752
753
754 END IF;
755
756 IF v_exception = 'N' THEN
757 --no warning condition found so continue.
758 IF (vc_receipt_method_id is null) and (v_default_alc = 'N')
759 THEN
760 -- this is a warning condition since no default alc has been assigned.
761 -- Set this warning num to 1 which is The payment method has not been
762 -- defined in the Paying Customer tab.
763
764 v_warning := 'Y';
765 v_warning_num := '1';
766
767 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
768 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO RM_ID AND NO DEFAULT');
769 END IF;
770
771 ELSIF (vc_receipt_method_id is not null) THEN
772 -- must find the primary bank account and pull the alc code from there.
773 -- Set this warning num to 2 if there is no alc code defined.
774
775 BEGIN
776 select cba.agency_location_code
777 into v_alc_code
778 from ar_receipt_method_accounts arma,
779 ce_bank_accounts cba,
780 ce_bank_acct_uses_all cbau
781 where arma.primary_flag = 'Y'
782 and arma.receipt_method_id = vc_receipt_method_id
783 and cbau.bank_acct_use_id = arma.remit_bank_acct_use_id
784 and cba.bank_account_id = cbau.bank_account_id
785 and cbau.org_id = v_org_id;
786 --Bug8654464
787 --and cba.account_owner_party_id = cbau.org_party_id
788
789 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
790 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_ALC_CODE = '||V_ALC_CODE);
791 END IF;
792
793 IF v_alc_code is null THEN
794 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
795 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO ALC FOUND ON BANK ACCOUNT');
796 END IF;
797 v_warning := 'Y';
798 v_warning_num := 2;
799 END IF;
800
801 EXCEPTION
802 when no_data_found then
803 IF v_default_alc <> 'N' THEN
804 v_alc_code := v_default_alc;
805 ELSE
806 v_warning := 'Y';
807 v_warning_num := '2';
808 END IF;
809 END;
810
811 ELSIF (vc_receipt_method_id is null)
812 and (v_default_alc <> 'N') THEN
813 -- default alc has been found so use that alc code
814 v_alc_code := v_default_alc;
815 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
816 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'USING DEFAULT ALC ');
817 END IF;
818
819 END IF;
820 END IF; -- v_warning = 'N' branch
821
822 BEGIN
823
824 SELECT fts.treasury_symbol
825 INTO v_treasury_symbol
826 FROM fv_fund_parameters ffp,
827 fv_treasury_symbols fts,
828 gl_code_combinations glc
829 WHERE decode(v_bal_seg_name,'SEGMENT1', glc.segment1,
830 'SEGMENT2', glc.segment2,
831 'SEGMENT3', glc.segment3,
832 'SEGMENT4', glc.segment4,
833 'SEGMENT5', glc.segment5,
834 'SEGMENT6', glc.segment6,
835 'SEGMENT7', glc.segment7,
836 'SEGMENT8', glc.segment8,
837 'SEGMENT9', glc.segment9,
838 'SEGMENT10', glc.segment10,
839 'SEGMENT11', glc.segment11,
840 'SEGMENT12', glc.segment12,
841 'SEGMENT13', glc.segment13,
842 'SEGMENT14', glc.segment14,
843 'SEGMENT15', glc.segment15,
844 'SEGMENT16', glc.segment16,
845 'SEGMENT17', glc.segment17,
846 'SEGMENT18', glc.segment18,
847 'SEGMENT19', glc.segment19,
848 'SEGMENT20', glc.segment20,
849 'SEGMENT21', glc.segment21,
850 'SEGMENT22', glc.segment22,
851 'SEGMENT23', glc.segment23,
852 'SEGMENT24', glc.segment24,
853 'SEGMENT25', glc.segment25,
854 'SEGMENT26', glc.segment26,
855 'SEGMENT27', glc.segment27,
856 'SEGMENT28', glc.segment28,
857 'SEGMENT29', glc.segment29,
858 'SEGMENT30', glc.segment30) = ffp.fund_value
859 AND glc.code_combination_id = vc_code_combination_id
860 AND ffp.treasury_symbol_id = fts.treasury_symbol_id
861 AND ffp.set_of_books_id = v_set_of_books_id;
862
863 EXCEPTION
864 WHEN NO_DATA_FOUND THEN NULL;
865 when others then
866 -- fnd_file.put_line(fnd_file.log,'hiuhkjsdhfkjsdhfkjsdfhjksdf'SQLERRM);
867 fnd_file.put_line(fnd_file.log,SQLERRM);
868 END;
869
870
871 BEGIN
872 INSERT INTO fv_sf1081_temp
873 (customer_trx_id,
874 trx_number,
875 customer_name,
876 cust_address1,
877 cust_address2,
878 cust_address3,
879 cust_city,
880 cust_state,
881 cust_postal_code,
882 tax_reference,
883 treasury_symbol,
884 amount,
885 bill_to_customer_id,
886 trx_date,
887 alc_code)
888 VALUES
889 (vc_customer_trx_id,
890 vc_trx_number,
891 vc_customer_name,
892 vc_address1,
893 vc_address2,
894 vc_address3,
895 vc_city,
896 vc_state,
897 vc_postal_code,
898 vc_cust_alc,
899 v_treasury_symbol,
900 vc_amount,
901 vc_bill_to_customer_id,
902 vc_trx_date,
903 decode(v_warning_num,'1','1','2','2','3','3',v_alc_code));
904
905 EXCEPTION
906 WHEN OTHERS THEN
907 g_error_code := SQLCODE;
908 g_error_message := 'INSERT_info: /'||SQLERRM;
909 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1', g_error_message);
910 END;
911
912 v_warning_num := '0';
913 END LOOP;
914
915 IF (parm_alc is not null) THEN
916 CLOSE ts_report_alc_cursor;
917 ELSE
918 CLOSE ts_report_noalc_cursor;
919 END IF;
920
921 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
922 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AFTER LOOP');
923 END IF;
924
925 COMMIT;
926
927 EXCEPTION
928
929 WHEN OTHERS THEN
930 g_error_code := 2;
931 g_error_message := SQLERRM;
932 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_message);
933 if ts_report_alc_cursor%ISOPEN THEN
934 close ts_report_alc_cursor;
935 end if;
936 if ts_report_noalc_cursor%ISOPEN THEN
937 close ts_report_noalc_cursor;
938 end if;
939 rollback;
940 RAISE;
941
942
943 END a200_load_report_tables;
944 -------------------------------------------------------
945 ------------------------------------------------------
946 BEGIN
947
948 g_module_name := 'fv.plsql.FV_SF1081_TRANSACTION.';
949 v_trx_found_2 := 'N' ;
950 v_trx_found_1 := 'N' ;
951 v_trx_found_3 := 'N' ;
952
953 END FV_SF1081_TRANSACTION ;