[Home] [Help]
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
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;
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
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');
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')
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
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
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
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
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
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
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
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;