[Home] [Help]
PACKAGE BODY: APPS.FV_SF1080_TRANSACTION
Source
1 PACKAGE BODY fv_sf1080_transaction AS
2 --$Header: FVX1080B.pls 120.15 2006/01/18 19:01:52 ksriniva 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_chart_of_accounts_id NUMBER;
11 parm_set_of_books_id NUMBER;
12 parm_order_by VARCHAR2(30);
13 parm_batch NUMBER(15);
14 parm_transaction_class VARCHAR2(30);
15 parm_transaction_type NUMBER(15);
16 parm_trans_num_low VARCHAR2(20);
17 parm_trans_num_high VARCHAR2(20);
18 parm_print_date_low DATE;
19 parm_print_date_high DATE;
20 parm_cust_profile_class_id NUMBER(15);
21 parm_customer_class VARCHAR2(30);
22 parm_customer VARCHAR2(360);
23 parm_open_invoices_only VARCHAR2(3);
24 parm_office_charged VARCHAR2(50);
25 parm_print_choice VARCHAR2(30);
26
27 --
28 -- ------------------------------------
29 -- Stored Global Variables
30 -- ------------------------------------
31 --
32 g_error_code NUMBER;
33 g_error_message VARCHAR2(80);
34 --
35 --
36 -- ---------- Transactions Report Header Cursor Vaiables -----------
37 remit_address1 hz_locations.address1%TYPE;
38 remit_address2 hz_locations.address2%TYPE;
39 remit_address3 hz_locations.address3%TYPE;
40 remit_address4 hz_locations.address4%TYPE;
41 remit_city hz_locations.city%TYPE;
42 remit_state hz_locations.state%TYPE;
43 remit_postal_code hz_locations.postal_code%TYPE;
44 line_count number;
45 line_counter number;
46 line_count_flag number;
47 segment varchar2(800);
48 --
49
50 -- ---------- Define Report Transaction Header Cursor -------------
51 ---
52 PROCEDURE a100_clear_report_temp_table;
53 --
54 PROCEDURE a200_load_report_tables;
55 --
56 --
57 abort_error EXCEPTION;
58 report_failure EXCEPTION;
59 --
60 -- ---------- End of Package Level Declaritives -----------------------------
61 --
62 PROCEDURE a000_load_table
63 (error_code OUT NOCOPY NUMBER,
64 error_message OUT NOCOPY VARCHAR2,
65 order_by IN VARCHAR2,
66 batch IN NUMBER,
67 transaction_class IN VARCHAR2,
68 transaction_type IN NUMBER,
69 trans_num_low IN VARCHAR2,
70 trans_num_high IN VARCHAR2,
71 print_date_low IN VARCHAR2,
72 print_date_high IN VARCHAR2,
73 cust_profile_class_id IN NUMBER,
74 customer_class IN VARCHAR2,
75 customer IN VARCHAR2,
76 open_invoices_only IN VARCHAR2,
77 office_charged IN VARCHAR2,
78 print_choice IN VARCHAR2)
79 --
80 IS
81 --
82 l_module_name VARCHAR2(200) ;
83 l_status VARCHAR2(2) ;
84 l_currency VARCHAR2(50) ;
85 l_request_id NUMBER;
86 -- number to handle the return value of the submit request
87
88 BEGIN
89 --
90 -- ------------------------------------
91 -- Store Input Parameters in Global Variables
92 -- ------------------------------------
93
94 l_module_name := g_module_name || 'a000_load_table';
95 parm_order_by := order_by;
96 parm_batch := batch;
97 parm_transaction_class := transaction_class;
98 parm_transaction_type := transaction_type;
99 parm_trans_num_low := trans_num_low;
100 parm_trans_num_high := trans_num_high;
101 parm_print_date_low := FND_DATE.CANONICAL_TO_DATE(print_date_low);
102 parm_print_date_high := FND_DATE.CANONICAL_TO_DATE(print_date_high);
103 parm_cust_profile_class_id := cust_profile_class_id;
104 parm_customer_class := customer_class;
105 parm_customer := customer;
106 parm_open_invoices_only := open_invoices_only;
107 parm_office_charged := office_charged;
108 parm_print_choice := print_choice;
109
110
111 -- Derive SOB,COA
112
113 FV_utility.get_ledger_info(mo_global.get_current_org_id,parm_set_of_books_id,
114 parm_chart_of_accounts_id,
115 l_currency,l_status);
116 If l_status = 0 then
117
118 error_code := 0;
119 error_message := '?';
120 g_error_code := 0;
121 g_error_message := '?';
122
123 -- Delete All Entries from Report Temp Table
124 a100_clear_report_temp_table;
125
126 IF g_error_code = 0 THEN
127
128 -- ----------------------------------------
129 -- Load Report Lines
130 -- ----------------------------------------
131
132 a200_load_report_tables;
133
134 fnd_request.set_org_id(mo_global.get_current_org_id);
135 --- Kick Off the SL1080 report
136 l_request_id :=fnd_request.submit_request(
137 application => 'FV',
138 program => 'FVSF1080_NEW',
139 description=>'',
140 start_time=>'',
141 sub_request => FALSE,
142 argument1 => parm_set_of_books_id,
143 argument2 => order_by);
144 IF (l_request_id = 0) THEN
145 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_request_id is'||l_request_id);
146 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'argument1 is'||parm_set_of_books_id);
147 RAISE report_failure;
148 ELSE
149 COMMIT;
150 END IF;
151 END IF;
152 Else
153 g_error_code := -1;
154 error_message := 'Cannot get current org_id or Ledger info';
155 End if;
156
157 IF g_error_code <> 0 THEN
158 RAISE abort_error;
159 END IF;
160
161 error_code := g_error_code;
162 error_message := 'Normal End of FVSF1080 Package';
163 -- ------------------------------------
164 -- Exceptions
165 -- ------------------------------------
166 EXCEPTION
167 --
168 WHEN abort_error THEN
169 error_code := g_error_code;
170 error_message := g_error_message;
171 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.abort_error',g_error_message);
172
173 --- in case the report submission is not successful ---
174 WHEN report_failure THEN
175 g_error_message := 'Submission of FVSF1080 Report failed';
176 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.report_failure',g_error_message);
177
178 WHEN OTHERS THEN
179 g_error_code := SQLCODE;
180 g_error_message := SQLERRM;
181 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_message);
182 RAISE_APPLICATION_ERROR(-20222,
183 'FVSF1080 Exception-'||g_error_message);
184 --
185 END a000_load_table;
186 -- ------------------------------------------------------------------
187 -- This procedure deletes records from temporary tables.
188 -- --------------------------------------------------------
189 PROCEDURE a100_clear_report_temp_table
190 --
191 IS
192 --
193 l_module_name VARCHAR2(200) ;
194 BEGIN
195 --
196 ---FV_UTILITY.DEBUG_MESG('Start a100_clear_report_temp_table');
197 --
198 l_module_name := g_module_name || 'a100_clear_report_temp_table';
199
200 BEGIN
201
202 DELETE
203 FROM FV_SF1080_HEADER_TEMP;
204 ---FV_UTILITY.DEBUG_MESG('Deleting from FV_SF1080_HEADER_TEMP');
205 EXCEPTION
206 WHEN NO_DATA_FOUND THEN
207 NULL;
208 WHEN OTHERS THEN
209 g_error_code := SQLCODE;
210 g_error_message := 'a100_clear_report_header_table: /'||SQLERRM;
211 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',g_error_message);
212 END;
213
214 BEGIN
215 DELETE
216 FROM FV_SF1080_DETAIL_TEMP;
217 ---FV_UTILITY.DEBUG_MESG('Deleting from FV_SF1080_DETAIL_TEMP');
218 EXCEPTION
219 WHEN NO_DATA_FOUND THEN
220 NULL;
221 WHEN OTHERS THEN
222 g_error_code := SQLCODE;
223 g_error_message := 'a100_clear_report_detail_table: /'||SQLERRM;
224 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_message);
225 END;
226 --
227 COMMIT;
228 --
229 --
230 END a100_clear_report_temp_table;
231 --
232 -- --------------------------------------------------------
233 -- This procedure loads the three temporary tables by reading
234 -- records from the cursor.
235 -- --------------------------------------------------------
236 --
237 PROCEDURE a200_load_report_tables
238 --
239 AS
240 l_module_name VARCHAR2(200);
241 CURSOR ts_report_header_cursor
242 IS
243 SELECT
244 rct.customer_trx_id customer_trx_id,
245 rct.trx_number trx_number,
246 rct.bill_to_customer_id bill_to_customer_id,
247 rct.remit_to_address_id remit_to_address_id,
248 hzp.party_name customer_name,
249 hzl.address1 cust_address1,
250 hzl.address2 cust_address2,
251 hzl.address3 cust_address3,
252 hzl.address4 cust_address4,
253 hzl.city cust_city,
254 hzl.state cust_state,
255 hzl.postal_code cust_postal_code
256 FROM RA_CUSTOMER_TRX rct,
257 hz_locations hzl, hz_cust_acct_sites hzcas,
258 hz_party_sites hzps , HZ_CUST_SITE_USEs hzcsu,hz_cust_accounts hzca, hz_parties hzp
259 WHERE rct.set_of_books_id = parm_set_of_books_id
260 AND rct.bill_to_customer_id = hzca.cust_account_id
261 AND hzca.cust_Account_id = hzcas.cust_account_id
262 AND hzp.party_id = hzca.party_id
263 AND hzca.cust_account_id = hzcas.cust_account_id
264 AND hzca.party_id = hzp.party_id
265 And hzcas.party_site_id = hzps.party_site_id
266 AND hzps.location_id = hzl.location_id
267 AND hzcsu.Cust_Acct_site_ID = hzcas.CUST_ACCT_SITE_ID
268 AND rct.complete_flag = 'Y'
269 AND rct.printing_option = 'PRI'
270 AND hzcsu.site_use_id = rct.bill_to_site_use_id
271 AND rct.bill_to_customer_id IN
272 (SELECT cust_account_id
273 FROM hz_customer_profiles
274 WHERE profile_class_id = decode(parm_cust_profile_class_id,null,
275 profile_class_id,parm_cust_profile_class_id))
276 AND rct.cust_trx_type_id IN
277 ((SELECT CUST_TRX_TYPE_ID
278 FROM RA_CUST_TRX_TYPES
279 WHERE TYPE LIKE NVL(parm_transaction_class,'%'))
280 intersect
281 (SELECT CUST_TRX_TYPE_ID
282 FROM RA_CUST_TRX_TYPES
283 WHERE CUST_TRX_TYPE_ID = decode(parm_transaction_type,null,
284 CUST_TRX_TYPE_ID,parm_transaction_type)))
285 AND (rct.trx_number between nvl(parm_trans_num_low,'0')
286 and nvl(parm_trans_num_high,'ZZZZZZZZZZZZZZZZZZZZ'))
287 AND rct.trx_date between DECODE(parm_print_date_low,null,TO_DATE('1990/1/1','yyyy/mm/dd'),
288 parm_print_date_low)
289 AND DECODE(parm_print_date_high,null,trunc(sysdate),parm_print_date_high )
290 AND hzca.cust_account_id IN
291 ((SELECT cust_account_id
292 FROM hz_cust_accounts hzca
293 WHERE NVL(customer_class_code,'XXX') LIKE DECODE(parm_customer_class,null,
294 NVL(customer_class_code,'XXX'),parm_customer_class))
295 INTERSECT
296 (SELECT cust_account_id
297 FROM hz_cust_accounts hzca
298 WHERE cust_account_id like nvl(parm_customer,'%')))
299 and rct.customer_trx_id in
300 (SELECT CUSTOMER_TRX_ID
301 FROM RA_CUSTOMER_TRX
302 WHERE nvl(status_trx,'-1') LIKE decode(parm_open_invoices_only,'Y','OP','N','%'))
303 AND rct.customer_trx_id IN
304 (SELECT CUSTOMER_TRX_ID
305 FROM RA_CUSTOMER_TRX
306 WHERE batch_id IN
307 (SELECT BATCH_ID
308 FROM RA_BATCHES
309 WHERE BATCH_ID = decode(parm_batch,null,BATCH_ID,parm_batch))
310 OR (nvl(BATCH_ID,'99') LIKE decode(parm_print_choice,'SEL','99','NEW','99')))
311 AND rct.customer_trx_id IN
312 (SELECT rct.CUSTOMER_TRX_ID
313 FROM RA_CUSTOMER_TRX rct
314 WHERE (nvl(rct.printing_count,'99') like
315 decode(parm_print_choice,'NEW','99','%'))
316 AND (nvl(to_char(rct.printing_original_date,'DD-MM-YYYY'),'01-01-1999') like
317 decode(parm_print_choice,'NEW','01-01-1999','%'))
318 AND (nvl(to_char(rct.printing_last_printed,'DD-MM-YYYY'),'01-01-1999') like
319 decode(parm_print_choice,'NEW','01-01-1999','%')))
320 ORDER BY decode(parm_order_by,'TRX_NUMBER',rct.trx_number,
321 'POSTAL_CODE',hzl.postal_code,
322 'CUSTOMER_NAME',hzp.party_name,rct.trx_number);
323 --
324 -- ----------------------------------------
325 BEGIN
326
327 l_module_name := g_module_name || 'a200_load_report_tables';
328
329 --
330 ---FV_UTILITY.DEBUG_MESG('Start a200_load_report_tables');
331 ---FV_UTILITY.DEBUG_MESG('parm_chart_of_accounts_id = ' || parm_chart_of_accounts_id);
332 ---FV_UTILITY.DEBUG_MESG('parm_set_of_books_id = ' || parm_set_of_books_id);
333 ---FV_UTILITY.DEBUG_MESG('parm_order_by = ' || parm_order_by);
334 ---FV_UTILITY.DEBUG_MESG('parm_batch = ' || parm_batch);
335 ---FV_UTILITY.DEBUG_MESG('parm_transaction_class = ' || parm_transaction_class);
336 ---FV_UTILITY.DEBUG_MESG('parm_transaction_type = ' || parm_transaction_type);
337 ---FV_UTILITY.DEBUG_MESG('parm_trans_num_low = ' || parm_trans_num_low);
338 ---FV_UTILITY.DEBUG_MESG('parm_trans_num_high = ' || parm_trans_num_high);
339 ---FV_UTILITY.DEBUG_MESG('parm_print_date_low = ' || parm_print_date_low);
340 ---FV_UTILITY.DEBUG_MESG('parm_print_date_high = ' || parm_print_date_high);
341 ---FV_UTILITY.DEBUG_MESG('parm_customer_class = ' || parm_customer_class);
342 ---FV_UTILITY.DEBUG_MESG('parm_customer = ' || parm_customer);
343 ---FV_UTILITY.DEBUG_MESG('parm_open_invoices_only = ' || parm_open_invoices_only);
344 ---FV_UTILITY.DEBUG_MESG('parm_office_charged = ' || parm_office_charged);
345 ---FV_UTILITY.DEBUG_MESG('parm_print_choice = ' || parm_print_choice);
346
347 --
348 -- This for loop selects the remit address and then inserts into FV_SF1080_
349 -- HEADER_TEMP table based on cursor records.
350 ---
351 FOR ts_report_header_entry IN ts_report_header_cursor LOOP
352 --
353 ---FV_UTILITY.DEBUG_MESG('Inside the header cursor');
354 ---FV_UTILITY.DEBUG_MESG('customer_trx_id = '||TS_REPORT_HEADER_ENTRY.customer_trx_id);
355
356 BEGIN
357 SELECT ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,CITY,STATE,POSTAL_CODE
358 INTO REMIT_ADDRESS1,REMIT_ADDRESS2,REMIT_ADDRESS3,REMIT_ADDRESS4,
359 REMIT_CITY,REMIT_STATE,REMIT_POSTAL_CODE
360 FROM hz_locations hzl, hz_cust_acct_sites hzcas, hz_party_sites hzps
361 WHERE hzcas.cust_Acct_site_id =
362 TS_REPORT_HEADER_ENTRY.REMIT_TO_ADDRESS_ID
363 AND hzcas.party_site_id = hzps.party_site_id
364 AND hzps.location_id = hzl.location_id;
365 ---FV_UTILITY.DEBUG_MESG('remit_address1 = '||REMIT_ADDRESS1);
366 ---FV_UTILITY.DEBUG_MESG('remit_address2 = '||REMIT_ADDRESS2);
367 ---FV_UTILITY.DEBUG_MESG('remit_address3 = '||REMIT_ADDRESS3);
368 ---FV_UTILITY.DEBUG_MESG('remit_address4 = '||REMIT_ADDRESS4);
369 EXCEPTION
370 WHEN NO_DATA_FOUND THEN
371 REMIT_ADDRESS1 := ' ';
372 REMIT_ADDRESS2 := NULL;
373 REMIT_ADDRESS3 := NULL;
374 REMIT_ADDRESS4 := NULL;
375 REMIT_CITY := NULL;
376 REMIT_STATE := NULL;
377 REMIT_POSTAL_CODE := NULL;
378 WHEN OTHERS THEN
379 g_error_code := SQLCODE;
380 g_error_message := 'remit_address_info: /'||SQLERRM;
381 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error3',g_error_message);
382 END;
383
384 Begin
385 INSERT INTO FV_SF1080_HEADER_TEMP
386 ( CUSTOMER_TRX_ID,
387 TRX_NUMBER,
388 CUSTOMER_NAME,
389 CUST_ADDRESS1,
390 REMIT_TO_ADDRESS_ADDRESS1,
391 CUST_ADDRESS2,
392 CUST_ADDRESS3,
393 CUST_ADDRESS4,
394 CUST_CITY,
395 CUST_STATE,
396 CUST_POSTAL_CODE,
397 REMIT_TO_ADDRESS_ADDRESS2,
398 REMIT_TO_ADDRESS_ADDRESS3,
399 REMIT_TO_ADDRESS_ADDRESS4,
400 REMIT_TO_ADDRESS_CITY,
401 REMIT_TO_ADDRESS_STATE,
402 REMIT_TO_ADDRESS_POSTAL_CODE,
403 ACCT_OFC_CHRG)
404 VALUES
405 (TS_REPORT_HEADER_ENTRY.CUSTOMER_TRX_ID,
406 TS_REPORT_HEADER_ENTRY.TRX_NUMBER,
407 TS_REPORT_HEADER_ENTRY.CUSTOMER_NAME,
408 TS_REPORT_HEADER_ENTRY.CUST_ADDRESS1,
409 REMIT_ADDRESS1,
410 TS_REPORT_HEADER_ENTRY.CUST_ADDRESS2,
411 TS_REPORT_HEADER_ENTRY.CUST_ADDRESS3,
412 TS_REPORT_HEADER_ENTRY.CUST_ADDRESS4,
413 TS_REPORT_HEADER_ENTRY.CUST_CITY,
414 TS_REPORT_HEADER_ENTRY.CUST_STATE,
415 TS_REPORT_HEADER_ENTRY.CUST_POSTAL_CODE,
416 REMIT_ADDRESS2,
417 REMIT_ADDRESS3,
418 REMIT_ADDRESS4,
419 REMIT_CITY,
420 REMIT_STATE,
421 REMIT_POSTAL_CODE,
422 PARM_OFFICE_CHARGED);
423 EXCEPTION
424 WHEN OTHERS THEN
425 g_error_code := SQLCODE;
426 g_error_message := 'INSERT_HEADER_info: /'||SQLERRM;
427 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error2',g_error_message);
428 END;
429
430 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
431 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CUSTOMER_TRX_ID = ' || TS_REPORT_HEADER_ENTRY.CUSTOMER_TRX_ID);
432 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRX_NUMBER = ' ||TS_REPORT_HEADER_ENTRY.TRX_NUMBER);
433 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CUSTOMER_NAME = ' || TS_REPORT_HEADER_ENTRY.CUSTOMER_NAME);
434 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CUST_POSTAL_CODE = ' || TS_REPORT_HEADER_ENTRY.CUST_POSTAL_CODE);
435 END IF;
436 BEGIN
437 LINE_COUNT := 0;
438 line_count_flag := 0;
439 SELECT COUNT(*)
440 INTO LINE_COUNT
441 FROM RA_CUSTOMER_TRX_LINES
442 WHERE CUSTOMER_TRX_ID = TS_REPORT_HEADER_ENTRY.CUSTOMER_TRX_ID;
443 ---FV_UTILITY.DEBUG_MESG('line_count = ' ||line_count);
444 EXCEPTION
445 WHEN NO_DATA_FOUND THEN
446 line_count_flag := 1;
447 WHEN OTHERS THEN
448 g_error_code := SQLCODE;
449 g_error_message := 'line_count_info: /'||SQLERRM;
450 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.error1',g_error_message);
451 END;
452
453 BEGIN
454 segment := NULL;
455
456 -- Commented out this piece of code because the accounting classification
457 -- section of the report is no longer needed
458 -- Uncommenetd the below code for the Bug 2138767
459
460 SELECT decode(glc.segment1,null,null,glc.segment1)||
461 decode(glc.segment2,null,null,'.'||glc.segment2)||
462 decode(glc.segment3,null,null,'.'||glc.segment3)||
463 decode(glc.segment4,null,null,'.'||glc.segment4)||
464 decode(glc.segment5,null,null,'.'||glc.segment5)||
465 decode(glc.segment6,null,null,'.'||glc.segment6)||
466 decode(glc.segment7,null,null,'.'||glc.segment7)||
467 decode(glc.segment8,null,null,'.'||glc.segment8)||
468 decode(glc.segment9,null,null,'.'||glc.segment9)||
469 decode(glc.segment10,null,null,'.'||glc.segment10)||
470 decode(glc.segment11,null,null,'.'||glc.segment11)||
471 decode(glc.segment12,null,null,'.'||glc.segment12)||
472 decode(glc.segment13,null,null,'.'||glc.segment13)||
473 decode(glc.segment14,null,null,'.'||glc.segment14)||
474 decode(glc.segment15,null,null,'.'||glc.segment15)||
475 decode(glc.segment16,null,null,'.'||glc.segment16)||
476 decode(glc.segment17,null,null,'.'||glc.segment17)||
477 decode(glc.segment18,null,null,'.'||glc.segment18)||
478 decode(glc.segment19,null,null,'.'||glc.segment19)||
479 decode(glc.segment20,null,null,'.'||glc.segment20)||
480 decode(glc.segment21,null,null,'.'||glc.segment21)||
481 decode(glc.segment22,null,null,'.'||glc.segment22)||
482 decode(glc.segment23,null,null,'.'||glc.segment23)||
483 decode(glc.segment24,null,null,'.'||glc.segment24)||
484 decode(glc.segment25,null,null,'.'||glc.segment25)||
485 decode(glc.segment26,null,null,'.'||glc.segment26)||
486 decode(glc.segment27,null,null,'.'||glc.segment27)||
487 decode(glc.segment28,null,null,'.'||glc.segment28)||
488 decode(glc.segment29,null,null,'.'||glc.segment29)||
489 decode(glc.segment30,null,null,'.'||glc.segment30)
490 INTO segment
491 FROM RA_CUST_TRX_LINE_GL_DIST rld,
492 GL_CODE_COMBINATIONS glc
493 WHERE rld.customer_trx_id =
494 TS_REPORT_HEADER_ENTRY.CUSTOMER_TRX_ID
495 AND rld.account_class = 'REC'
496 AND rld.code_combination_id = glc.code_combination_id;
497
498 END;
499
500
501 BEGIN
502 LINE_COUNTER := 1;
503 ---FV_UTILITY.DEBUG_MESG('line_count= '||line_count);
504 ---FV_UTILITY.DEBUG_MESG('line_counter = '||line_counter);
505 ---FV_UTILITY.DEBUG_MESG('line_count_flag = '||line_count_flag);
506 IF LINE_COUNT > 1 AND line_count_flag = 0 THEN
507 ---FV_UTILITY.DEBUG_MESG('Inside the multi line if statement');
508 BEGIN
509 FOR LINE_COUNTER IN 1..LINE_COUNT LOOP
510 INSERT INTO FV_SF1080_DETAIL_TEMP
511 (
512 CUSTOMER_TRX_ID,
513 LINE_NUMBER,
514 EXTENDED_AMOUNT,
515 SALES_ORDER,
516 DESCRIPTION,
517 QUANTITY_INVOICED,
518 UNIT_SELLING_PRICE,
519 UOM_CODE,
520 ACCT_OFC_RECV_FND
521 )
522 SELECT rtl.CUSTOMER_TRX_ID,
523 rtl.LINE_NUMBER,
524 rtl.EXTENDED_AMOUNT,
525 rtl.SALES_ORDER,
526 SUBSTR(rtl.DESCRIPTION,1,60),
527 rtl.QUANTITY_INVOICED,
528 rtl.UNIT_SELLING_PRICE,
529 rtl.UOM_CODE,
530 segment
531 FROM RA_CUSTOMER_TRX_LINES rtl
532 WHERE rtl.customer_trx_id =
533 TS_REPORT_HEADER_ENTRY.CUSTOMER_TRX_ID
534 AND rtl.line_number = line_counter;
535
536 END LOOP; --- for loop ended
537
538 END;
539 ELSIF LINE_COUNT = 1 AND line_count_flag = 0 THEN
540 ---FV_UTILITY.DEBUG_MESG('Inside the single line if statement');
541 INSERT INTO FV_SF1080_DETAIL_TEMP
542 (
543 CUSTOMER_TRX_ID,
544 LINE_NUMBER,
545 EXTENDED_AMOUNT,
546 SALES_ORDER,
547 DESCRIPTION,
548 QUANTITY_INVOICED,
549 UNIT_SELLING_PRICE,
550 UOM_CODE,
551 ACCT_OFC_RECV_FND
552 )
553 SELECT rtl.CUSTOMER_TRX_ID,
554 rtl.LINE_NUMBER,
555 rtl.EXTENDED_AMOUNT,
556 rtl.SALES_ORDER,
557 SUBSTR(rtl.DESCRIPTION,1,60),
558 rtl.QUANTITY_INVOICED,
559 rtl.UNIT_SELLING_PRICE,
560 rtl.UOM_CODE,
561 segment
562 FROM RA_CUSTOMER_TRX_LINES rtl
563 WHERE rtl.customer_trx_id =
564 TS_REPORT_HEADER_ENTRY.CUSTOMER_TRX_ID;
565 END IF;
566 END;
567 END LOOP;
568 ---FV_UTILITY.DEBUG_MESG('End of multiple line invoice loop.');
569 commit;
570 --
571 -- ------------------------------------
572 -- Exceptions
573 -- ------------------------------------
574 EXCEPTION
575 --
576 WHEN OTHERS THEN
577 g_error_code := SQLCODE;
578 g_error_message := SQLERRM;
579 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_message);
580 IF ts_report_header_cursor%ISOPEN THEN
581 close ts_report_header_cursor;
582 END IF;
583 --
584 END a200_load_report_tables;
585 -------------------------------------------------------
586 ------------------------------------------------------
587 BEGIN
588
589 g_module_name := 'fv.plsql.fv_sf1080_transaction.';
590
591
592 End fv_sf1080_transaction;