[Home] [Help]
PACKAGE BODY: APPS.XTR_TRANS_INTERFACE
Source
1 PACKAGE BODY XTR_TRANS_INTERFACE AS
2 /* $Header: xtrtrinb.pls 120.3 2005/06/29 07:58:26 rjose ship $ */
3
4 --
5 CURSOR FIND_USER (fnd_user_id in number) is
6 select dealer_code
7 from xtr_dealer_codes_v
8 where user_id = fnd_user_id;
9
10 --
11 CURSOR IMP_SOURCE is
12 select distinct e.SOURCE
13 from XTR_EXT_IMPORT_INTERFACE_V e
14 where e.SELECT_FOR_TRANSFER = 'Y'
15 and e.TRANSFER_BY = x_user;
16 --
17 CURSOR SOURCE_DET is
18 select s.SOURCE,s.TRANSFER_TRAILER_YN,s.REVERSE_ON_TRANSFER_YN,
19 s.VERIFY_TRAILER_TOTALS,s.CURRENCY,s.ACCOUNT_NUMBER,
20 s.COMPANY_CODE,s.IMPORT_INCLUDES_DECIMAL
21 from XTR_SOURCE_OF_IMPORTS_V s
22 where s.SOURCE = l_imp_source;
23 --
24 CURSOR TSFR_DATES is
25 select distinct CREATION_DATE
26 from XTR_EXT_IMPORT_INTERFACE_V
27 where SELECT_FOR_TRANSFER = 'Y'
28 and TRANSFER_BY = x_user
29 and SOURCE = l_source
30 and CURRENCY = l_ccy;
31 --
32 CURSOR VERIFY_DR is
33 select sum(b.AMOUNT / l_divisor),count(b.TRANSACTION_CODE)
34 from XTR_EXT_IMPORT_INTERFACE_V b
35 where b.SOURCE = l_source
36 and b.CURRENCY = l_ccy
37 and b.CREATION_DATE = l_cre_date
38 and to_number(b.TRANSACTION_CODE) <= 49
39 and b.RECORD_TYPE IN ('1','01');
40 --
41 CURSOR VERIFY_CR is
42 select sum(b.AMOUNT / l_divisor),count(b.TRANSACTION_CODE)
43 from XTR_EXT_IMPORT_INTERFACE_V b
44 where b.SOURCE = l_source
45 and b.CURRENCY = l_ccy
46 and b.CREATION_DATE = l_cre_date
47 and to_number(b.TRANSACTION_CODE) >= 50
48 and b.RECORD_TYPE IN ('1','01');
49 --
50 CURSOR TRAILER_TOT is
51 select nvl(a.AMOUNT,0) / l_divisor,nvl(a.DEBIT_AMOUNT,0) / l_divisor,
52 nvl(a.CREDIT_AMOUNT,0) / l_divisor,
53 nvl(a.NUMBER_OF_TRANSACTIONS,0)
54 from XTR_EXT_IMPORT_INTERFACE_V a
55 where a.SOURCE = l_source
56 and a.CURRENCY = l_ccy
57 and a.CREATION_DATE = l_cre_date
58 and a.RECORD_TYPE IN ('2','02');
59 --
60 CURSOR GET_ACCT is
61 select a.ACCOUNT_NUMBER
62 from XTR_EXT_IMPORT_INTERFACE_V a
63 where a.SOURCE = l_source
64 and a.CURRENCY = l_ccy
65 and a.CREATION_DATE = l_cre_date
66 and a.RECORD_TYPE IN ('0','00','2','02')
67 order by a.RECORD_TYPE asc;
68 --
69 CURSOR CHK_ACCT is
70 select 1
71 from XTR_BANK_ACCOUNTS_V
72 where PARTY_CODE = l_company
73 and CURRENCY = l_ccy;
74 --
75 CURSOR TSFR is
76 select *
77 from XTR_EXT_IMPORT_INTERFACE_V
78 where SELECT_FOR_TRANSFER = 'Y'
79 and TRANSFER_BY = x_user
80 and SOURCE = l_source
81 and CREATION_DATE = l_cre_date
82 and CURRENCY = l_ccy
83 order by SOURCE,RECORD_TYPE
84 for update of CREATION_DATE;
85 --
86 CURSOR IMP_NUM is
87 select XTR_DEAL_DATE_AMOUNTS_S.NEXTVAL
88 from DUAL;
89
90 /* ---------------------------------------------------------------------
91 | PRIVATE PROCEDURE |
92 | transfer_from_interface |
93 | |
94 | DESCRIPTION |
95 | Procedure to Transfer Records from the External Interface Table |
96 | the interface tables or reconcile a previously imported state- |
97 | ment. |
98 | |
99 | REQUIRES |
100 | |
101 | RETURNS |
102 | errbuf |
103 | retcode |
104 | |
105 | HISTORY |
106 --------------------------------------------------------------------- */
107
108
109
110 PROCEDURE TRANSFER_FROM_INTERFACE (errbuf OUT NOCOPY VARCHAR2,
111 retcode OUT NOCOPY NUMBER,
112 p_source VARCHAR2,
113 p_creation_date VARCHAR2,
114 p_currency VARCHAR2) IS
115 row_det TSFR%rowtype;
116 dummy NUMBER;
117 error_msg VARCHAR2(255);
118 --
119 BEGIN
120
121 --
122 -- set parameters
123 --
124 G_source := p_source;
125 G_creation_date := to_date(p_creation_date, 'YYYY/MM/DD HH24:MI:SS');
126 G_currency := p_currency;
127
128 --
129 -- Find the dealer code
130 --
131 fnd_user_id := FND_GLOBAL.USER_ID;
132 open FIND_USER(fnd_user_id);
133 fetch FIND_USER into x_user;
134 close FIND_USER;
135
136 --
137 -- Set value of 'Y' to column 'SELECT_FOR_TRANSFER'
138 -- In Table XTR_EXT_IMPORT_INTERFACE_V
139 --
140 UPDATE XTR_EXT_IMPORT_INTERFACE
141 set SELECT_FOR_TRANSFER = 'Y',
142 TRANSFER_BY = X_user
143 where CREATION_DATE = NVL(G_creation_date, creation_date)
144 and SOURCE = NVL(G_source, source)
145 and CURRENCY = NVL(G_currency, currency);
146
147
148 --
149 -- Transfer
150 --
151
152 l_error := 0;
153 l_count := 0;
154 open IMP_SOURCE;
155 LOOP
156 fetch IMP_SOURCE INTO l_imp_source;
157 EXIT WHEN IMP_SOURCE%NOTFOUND;
158 open SOURCE_DET;
159 LOOP
160 fetch SOURCE_DET INTO l_source,l_tsfr_trailer,l_rev_trailer,
161 l_verify_total,l_ccy,l_acct,l_company,l_div;
162 EXIT WHEN SOURCE_DET%NOTFOUND;
163 if l_div = 'Y' then
164 l_divisor := 1;
165 else
166 l_divisor := 100;
167 end if;
168 open TSFR_DATES;
169 LOOP
170 fetch TSFR_DATES INTO l_cre_date;
171 EXIT WHEN TSFR_DATES%NOTFOUND;
172 open GET_ACCT;
173 fetch GET_ACCT into l_batch_acct;
174 WHILE l_batch_acct is NULL and GET_ACCT%FOUND LOOP
175 fetch GET_ACCT into l_batch_acct;
176 END LOOP;
177 close GET_ACCT;
178 if l_batch_acct is NOT NULL then
179 open CHK_ACCT;
180 fetch CHK_ACCT INTO dummy;
181 if CHK_ACCT%NOTFOUND then
182 close CHK_ACCT;
183 FND_MESSAGE.SET_NAME('XTR','XTR_997');
184 error_msg := FND_MESSAGE.GET;
185 insert into XTR_IMPORT_TRANSFER_ERRORS_V
186 (SOURCE,CREATION_DATE,CURRENCY,NET_AMOUNT,NET_DEBIT_AMOUNT,
187 NET_CREDIT_AMOUNT,NET_TRANS_NOS,TRANSFER_ON,TRANSFER_BY,COMMENTS)
188 values
189 (l_source,l_cre_date,l_ccy,NULL,NULL,NULL,NULL, sysdate, x_user,error_msg);
190 l_error := nvl(l_error,0) + 1;
191
192 ---add
193 update XTR_EXT_IMPORT_INTERFACE_V
194 set SELECT_FOR_TRANSFER = NULL
195 where CREATION_DATE = l_cre_date
196 and SOURCE = l_source
197 and CURRENCY = l_ccy
198 and TRANSFER_BY = x_user;
199 ---
200 goto NEXT_TRANSFER;
201 end if;
202 close CHK_ACCT;
203 else
204 -- No Batch A/c has been specified
205 FND_MESSAGE.SET_NAME('XTR','XTR_998');
206 error_msg := FND_MESSAGE.GET;
207 insert into XTR_IMPORT_TRANSFER_ERRORS_V
208 (SOURCE,CREATION_DATE,CURRENCY,NET_AMOUNT,NET_DEBIT_AMOUNT,
209 NET_CREDIT_AMOUNT,NET_TRANS_NOS,TRANSFER_ON,TRANSFER_BY,COMMENTS)
210 values
211 (l_source,l_cre_date,l_ccy,NULL,NULL,NULL,NULL, sysdate, x_user,error_msg);
212 l_error := nvl(l_error,0) + 1;
213
214 ---add
215 update XTR_EXT_IMPORT_INTERFACE_V
216 set SELECT_FOR_TRANSFER = NULL
217 where CREATION_DATE = l_cre_date
218 and SOURCE = l_source
219 and CURRENCY = l_ccy
220 and TRANSFER_BY = x_user;
221 ---
222 goto NEXT_TRANSFER;
223 end if;
224
225 if nvl(l_verify_total,'N') = 'Y' then
226 open TRAILER_TOT;
227 fetch TRAILER_TOT INTO l_total,l_db_total,l_cr_total,l_num_trans;
228 if TRAILER_TOT%FOUND then
229 -- Control Total exists
230 open VERIFY_DR;
231 fetch VERIFY_DR INTO l_net_debit,l_dr_trans;
232 close VERIFY_DR;
233 open VERIFY_CR;
234 fetch VERIFY_CR INTO l_net_credit,l_cr_trans;
235 close VERIFY_CR;
236 l_net_amount := nvl(l_net_debit,0) + nvl(l_net_credit,0);
237 l_net_trans := nvl(l_dr_trans,0) + nvl(l_cr_trans,0);
238 if nvl(l_total,0) <> 0 then
239 if nvl(l_net_amount,0) <> nvl(l_total,0) then
240 l_error := nvl(l_error,0) + 1;
241 FND_MESSAGE.SET_NAME('XTR','XTR_999');
242 error_msg := FND_MESSAGE.GET;
243 insert into XTR_IMPORT_TRANSFER_ERRORS_V
244 (SOURCE,CREATION_DATE,CURRENCY,NET_AMOUNT,NET_DEBIT_AMOUNT,
245 NET_CREDIT_AMOUNT,NET_TRANS_NOS,TRANSFER_ON,TRANSFER_BY,COMMENTS)
246 values
247 (l_source,l_cre_date,l_ccy,nvl(l_net_amount,0) - nvl(l_total,0),
248 NULL,NULL,NULL, sysdate, x_user,error_msg);
249
250 ---add
251 update XTR_EXT_IMPORT_INTERFACE_V
252 set SELECT_FOR_TRANSFER = NULL
253 where CREATION_DATE = l_cre_date
254 and SOURCE = l_source
255 and CURRENCY = l_ccy
256 and TRANSFER_BY = x_user;
257 ---
258 end if;
259 end if;
260 if nvl(l_net_debit,0) <> nvl(l_db_total,0) or
261 nvl(l_net_credit,0) <> nvl(l_cr_total,0) or
262 nvl(l_net_trans,0) <> nvl(l_num_trans,0) then
263 -- Contains Errors
264 FND_MESSAGE.SET_NAME('XTR','XTR_1000');
265 error_msg := FND_MESSAGE.GET;
266 insert into XTR_IMPORT_TRANSFER_ERRORS_V
267 (SOURCE,CREATION_DATE,CURRENCY,NET_AMOUNT,NET_DEBIT_AMOUNT,
268 NET_CREDIT_AMOUNT,NET_TRANS_NOS,TRANSFER_ON,TRANSFER_BY,COMMENTS)
269 values
270 (l_source,l_cre_date,l_ccy,NULL,
271 nvl(l_net_debit,0) - nvl(l_db_total,0),
272 nvl(l_net_credit,0) - nvl(l_cr_total,0),
273 nvl(l_net_trans,0) - nvl(l_num_trans,0),
274 sysdate, x_user,error_msg);
275 l_error := nvl(l_error,0) + 1;
276
277 ---add
278 update XTR_EXT_IMPORT_INTERFACE_V
279 set SELECT_FOR_TRANSFER = NULL
280 where CREATION_DATE = l_cre_date
281 and SOURCE = l_source
282 and CURRENCY = l_ccy
283 and TRANSFER_BY = x_user;
284 ---
285
286 close TRAILER_TOT;
287 goto NEXT_TRANSFER;
288 else
289 -- No errors in verification therefore do the transfer
290 close TRAILER_TOT;
291 goto DO_TRANSFER;
292 end if;
293
294 else
295 -- Verification Reqd but control record (Trailer does not exist).
296 l_error := nvl(l_error,0) + 1;
297 FND_MESSAGE.SET_NAME('XTR','XTR_1001');
298 error_msg := FND_MESSAGE.GET;
299 insert into XTR_IMPORT_TRANSFER_ERRORS_V
300 (SOURCE,CREATION_DATE,CURRENCY,NET_AMOUNT,NET_DEBIT_AMOUNT,
301 NET_CREDIT_AMOUNT,NET_TRANS_NOS,TRANSFER_ON,TRANSFER_BY,COMMENTS)
302 values
303 (l_source,l_cre_date,l_ccy,NULL,NULL,NULL,NULL, sysdate,
304 x_user,error_msg);
305 close TRAILER_TOT;
306 ---add
307 update XTR_EXT_IMPORT_INTERFACE_V
308 set SELECT_FOR_TRANSFER = NULL
309 where CREATION_DATE = l_cre_date
310 and SOURCE = l_source
311 and CURRENCY = l_ccy
312 and TRANSFER_BY = x_user;
313 ---
314 goto NEXT_TRANSFER;
315 end if;
316 close TRAILER_TOT;
317 end if;
318 <<DO_TRANSFER>>
319 -- Fetch Unique Import Reference Number for this Import Transfer
320 open IMP_NUM;
321 fetch IMP_NUM INTO l_import_nos;
322 close IMP_NUM;
323 -- Transfer Records from Import to Reconciliation Table
324 open TSFR;
325 LOOP
326 fetch TSFR INTO row_det;
327 EXIT WHEN TSFR%NOTFOUND;
328 if row_det.RECORD_TYPE IN ('0','00') then
329 -- Do nothing (Header Record)
330 NULL;
331 elsif row_det.RECORD_TYPE IN ('1','01') then
332 -- Transfer individual Rows
333 if to_number(row_det.TRANSACTION_CODE) < 49 then
334 row_det.DEBIT_AMOUNT := row_det.AMOUNT / l_divisor;
335 row_det.CREDIT_AMOUNT := NULL;
336 else
337 row_det.CREDIT_AMOUNT := row_det.AMOUNT / l_divisor;
338 row_det.DEBIT_AMOUNT := NULL;
339 end if;
340 l_count := l_count + 1;
341 insert into XTR_PAY_REC_RECONCILIATION_V
342 (IMPORT_REFERENCE,VALUE_DATE,PARTY_NAME,PARTICULARS,
343 SERIAL_REFERENCE,RECORD_TYPE,DEBIT_AMOUNT,CREDIT_AMOUNT,
344 COMMENTS)
345 values
346 (l_import_nos,row_det.VALUE_DATE,row_det.PARTY_NAME,
347 row_det.PARTICULARS,row_det.SERIAL_REFERENCE,
348 row_det.RECORD_TYPE,row_det.DEBIT_AMOUNT,
349 row_det.CREDIT_AMOUNT,row_det.COMMENTS);
350 elsif row_det.RECORD_TYPE IN ('2','02') then
351 -- Insert Trailer Record for later reference
352 insert into XTR_IMPORT_TRAILER_DETAILS_V
353 (SOURCE,IMPORT_REFERENCE,ACCOUNT_NUMBER,CURRENCY,CREATION_DATE)
354 values
355 (l_source,l_import_nos,nvl(l_batch_acct,l_acct),l_ccy,l_cre_date);
356 -- Transfer Trailer Record if Required
357 -- (As specified in the Source Setup).
358 if row_det.RECORD_TYPE in('2','02') and upper(nvl(l_tsfr_trailer,'N')) =
359 'Y' then
360 -- Reverse Trailer Record if Required
361 -- (As specified in the Source Setup).
362 if upper(nvl(l_rev_trailer,'N')) = 'Y' then
363 l_debit := row_det.DEBIT_AMOUNT / l_divisor;
364 row_det.DEBIT_AMOUNT := row_det.CREDIT_AMOUNT / l_divisor;
365 row_det.CREDIT_AMOUNT := l_debit;
366 end if;
367 if row_det.DEBIT_AMOUNT=0 then
368 row_det.DEBIT_AMOUNT :=NULL;
369 end if;
370 if row_det.CREDIT_AMOUNT=0 then
371 row_det.CREDIT_AMOUNT :=NULL;
372 end if;
373 -- Insert Trailer Record for into Reconciliation table
374 l_count := l_count + 1;
375 insert into XTR_PAY_REC_RECONCILIATION_V
376 (IMPORT_REFERENCE,VALUE_DATE,PARTY_NAME,PARTICULARS,
377 SERIAL_REFERENCE,RECORD_TYPE,DEBIT_AMOUNT,CREDIT_AMOUNT,
378 COMMENTS)
379 values
380 (l_import_nos,row_det.VALUE_DATE,row_det.PARTY_NAME,
381 row_det.PARTICULARS,row_det.SERIAL_REFERENCE,
382 row_det.RECORD_TYPE,row_det.DEBIT_AMOUNT,
383 row_det.CREDIT_AMOUNT,row_det.COMMENTS);
384 end if;
385 end if;
386 /* Do Not Delete for Test purposes*/
387 -- Delete Successful transfer from Interface Table
388 delete from XTR_EXT_IMPORT_INTERFACE_V
389 where SELECT_FOR_TRANSFER = 'Y'
390 and CREATION_DATE = row_det.CREATION_DATE
391 and SOURCE = row_det.SOURCE
392 and TRANSFER_BY = row_det.TRANSFER_BY;
393 END LOOP;
394 close TSFR;
395 <<NEXT_TRANSFER>>
396 NULL;
397 END LOOP;
398 close TSFR_DATES;
399 END LOOP;
400 close SOURCE_DET;
401 END LOOP;
402 close IMP_SOURCE;
403
404 /*
405 if nvl(l_error,0) = 0 then
406 ---DISP_WARN(982);-- Processing Complete without errors
407 --alert_message(3,'Processing Complete without errors.');
408 DISP_WARN('XTR_1547');
409 else
410 bell;
411 --alert_message(3,'APP-983 Processing Complete WITH '||to_char(l_error)||
412 -- ' ERROR(s).');
413 DISP_WARN('XTR_1548');
414 end if;
415 */
416
417 EXCEPTION
418 WHEN OTHERS THEN
419 IF xtr_debug_pkg.pg_sqlplus_enable_flag = 1 THEN
420 xtr_debug_pkg.debug('EXCEPTION: XTR_TRANS_INTERFACE.transfer_from_interface');
421 END IF;
422 RAISE;
423 END TRANSFER_FROM_INTERFACE;
424
425 END XTR_TRANS_INTERFACE;