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