[Home] [Help]
PACKAGE BODY: APPS.CE_PURGE
Source
1 PACKAGE BODY ce_purge AS
2 /* $Header: cepurestrb.pls 120.5 2011/03/13 05:55:39 rtumati noship $ */
3
4 l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
5
6
7 PROCEDURE put_starttag(tag_name IN VARCHAR2) IS
8 BEGIN
9
10 fnd_file.put_line(fnd_file.output, '<'||tag_name||'>');
11 --fnd_file.new_line(fnd_file.output,1);
12
13 EXCEPTION
14
15 WHEN OTHERS then
16 APP_EXCEPTION.RAISE_EXCEPTION;
17
18 END;
19
20 PROCEDURE put_endtag(tag_name IN VARCHAR2) IS
21 BEGIN
22
23 fnd_file.put_line(fnd_file.output, '</'||tag_name||'>');
24 --fnd_file.new_line(fnd_file.output,1);
25
26 EXCEPTION
27
28 WHEN OTHERS then
29 APP_EXCEPTION.RAISE_EXCEPTION;
30
31 END;
32
33 PROCEDURE put_element(tag_name IN VARCHAR2,
34 value IN VARCHAR2) IS
35 BEGIN
36
37 fnd_file.put(fnd_file.output, '<'||tag_name||'>');
38 fnd_file.put(fnd_file.output, '<![CDATA[');
39 fnd_file.put(fnd_file.output, value);
40 fnd_file.put(fnd_file.output, ']]>');
41 fnd_file.put_line(fnd_file.output, '</'||tag_name||'>');
42
43 EXCEPTION
44
45 WHEN OTHERS then
46 APP_EXCEPTION.RAISE_EXCEPTION;
47
48 END;
49
50 /* ---------------------------------------------------------------------
51 | PUBLIC PROCEDURE |
52 | set_parameters |
53 | |
54 | DESCRIPTION |
55 | Copies the parameters passed to Report |
56 | |
57 | CALLED BY |
58 | CEPURGE.rdf from BeforeReport Trigger |
59 | REQUIRES |
60 | |
61 | HISTORY |
62 | 29-Dec-2010 Created Rtumati |
63 --------------------------------------------------------------------- */
64 PROCEDURE set_parameters(
65 p_BANK_ACCOUNT_ID ce_bank_accounts.BANK_ACCOUNT_ID%TYPE,
66 p_BANK_BRANCH_ID ce_bank_accounts.BANK_branch_ID%TYPE,
67 p_STATEMENT_DATE_FROM ce_statement_headers.statement_date%TYPE,
68 p_STATEMENT_DATE_TO ce_statement_headers.statement_date%TYPE,
69 p_Bank_Name ce_bank_branches_v.bank_name%TYPE,
70 p_Bank_Branch_Name ce_bank_branches_v.bank_branch_name%TYPE,
71 p_Bank_Account_Name ce_bank_accounts.Bank_Account_Name%TYPE,
72 p_Currency_Code ce_bank_accounts.Currency_Code%TYPE,
73 p_Bank_Account_Num ce_bank_accounts.Bank_Account_Num%TYPE,
74 p_Archive_Purge_Option VARCHAR2,
75 p_Delete_Or_Append_Action VARCHAR2,
76 p_Objects VARCHAR2,
77 p_Statement_type VARCHAR2,
78 p_hdr_int_status VARCHAR2,
79 p_Only_Unrec VARCHAR2) IS
80 BEGIN
81 G_BANK_ACCOUNT_ID := p_BANK_ACCOUNT_ID;
82 G_BANK_BRANCH_ID := p_BANK_BRANCH_ID;
83 G_STATEMENT_DATE_FROM := p_STATEMENT_DATE_FROM;
84 G_STATEMENT_DATE_TO := p_STATEMENT_DATE_TO;
85 G_Bank_Name := p_Bank_Name;
86 G_Bank_Branch_Name := p_Bank_Branch_Name;
87 G_BANK_ACCOUNT_Name := p_Bank_Account_Name;
88 G_Currency_Code := p_Currency_Code;
89 G_BANK_ACCOUNT_num := p_Bank_Account_Num;
90 G_Archive_Purge_Option := p_Archive_Purge_Option;
91 G_Delete_Or_Append_Action := p_Delete_Or_Append_Action;
92 G_Objects := p_Objects;
93 G_Statement_type := p_Statement_type;
94 G_hdr_int_status := p_hdr_int_status;
95 G_Only_Unrec := p_Only_Unrec;
96 END set_parameters;
97
98 /* ---------------------------------------------------------------------
99 | PUBLIC PROCEDURE |
100 | get_arch_purge_counts |
101 | |
102 | DESCRIPTION |
103 | Returns the Achive and purge counts to report |
104 | |
105 | CALLED BY |
106 | CEPURGE.rdf from BeforeReport Trigger |
107 | REQUIRES |
108 | |
109 | HISTORY |
110 | 29-Dec-2010 Created Rtumati |
111 --------------------------------------------------------------------- */
112 PROCEDURE get_arch_purge_counts(
113 P_Purge_Inf_Headers OUT NOCOPY NUMBER,
114 P_Purge_Inf_Lines OUT NOCOPY NUMBER,
115 P_Purge_Stat_Lines OUT NOCOPY NUMBER,
116 P_Purge_Stat_Headers OUT NOCOPY NUMBER,
117 P_Purge_Stat_Rec OUT NOCOPY NUMBER,
118 P_Purge_Stat_Errors OUT NOCOPY NUMBER,
119 P_Purge_Inf_Headers2 OUT NOCOPY NUMBER,
120 P_Purge_Inf_Lines2 OUT NOCOPY NUMBER,
121 P_Purge_Stat_Lines2 OUT NOCOPY NUMBER,
122 P_Purge_Stat_Headers2 OUT NOCOPY NUMBER,
123 P_Archive_Inf_Headers OUT NOCOPY NUMBER,
124 P_Archive_Inf_Lines OUT NOCOPY NUMBER,
125 P_Archive_Stat_Headers OUT NOCOPY NUMBER,
126 P_Archive_Stat_Lines OUT NOCOPY NUMBER,
127 P_Archive_Stat_Errors OUT NOCOPY NUMBER,
128 P_Archive_Stat_Rec OUT NOCOPY NUMBER,
129 P_Archive_Inf_Lines2 OUT NOCOPY NUMBER,
130 P_Archive_Inf_Headers2 OUT NOCOPY NUMBER,
131 P_Archive_Stat_Lines2 OUT NOCOPY NUMBER,
132 P_Archive_Stat_Headers2 OUT NOCOPY NUMBER ) IS
133
134 BEGIN
135 P_Purge_Inf_Headers := G_Purge_Inf_Headers;
136 P_Purge_Inf_Lines := G_Purge_Inf_Lines;
137 P_Purge_Stat_Lines := G_Purge_Stat_Lines;
138 P_Purge_Stat_Headers := G_Purge_Stat_Headers;
139 P_Purge_Stat_Rec := G_Purge_Stat_Rec;
140 P_Purge_Stat_Errors := G_Purge_Stat_Errors;
141 P_Purge_Inf_Headers2 := G_Purge_Inf_Headers2;
142 P_Purge_Inf_Lines2 := G_Purge_Inf_Lines2;
143 P_Purge_Stat_Lines2 := G_Purge_Stat_Lines2;
144 P_Purge_Stat_Headers2 := G_Purge_Stat_Headers2;
145 P_Archive_Inf_Headers := G_Archive_Inf_Headers;
146 P_Archive_Inf_Lines := G_Archive_Inf_Lines;
147 P_Archive_Stat_Headers := G_Archive_Stat_Headers;
148 P_Archive_Stat_Lines := G_Archive_Stat_Lines;
149 P_Archive_Stat_Errors := G_Archive_Stat_Errors;
150 P_Archive_Stat_Rec := G_Archive_Stat_Rec;
151 P_Archive_Inf_Lines2 := G_Archive_Inf_Lines2;
152 P_Archive_Inf_Headers2 := G_Archive_Inf_Headers2;
153 P_Archive_Stat_Lines2 := G_Archive_Stat_Lines2;
154 P_Archive_Stat_Headers2 := G_Archive_Stat_Headers2;
155 END get_arch_purge_counts;
156
157 /* ---------------------------------------------------------------------
158 | PRIVATE PROCEDURE |
159 | set_arch_purge_counts |
160 | |
161 | DESCRIPTION |
162 | Restore the Bank Statements from Archive tables |
163 | |
164 | CALLED BY |
165 | restore_stmts |
166 | REQUIRES |
167 | |
168 | HISTORY |
169 | 17-Jan-2011 Created Rtumati |
170 --------------------------------------------------------------------- */
171 PROCEDURE set_arch_purge_counts
172 IS
173 BEGIN
174 G_Purge_Inf_Headers := 0;
175 G_Purge_Inf_Lines := 0;
176 G_Purge_Stat_Lines := 0;
177 G_Purge_Stat_Headers := 0;
178 G_Purge_Stat_Rec := 0;
179 G_Purge_Stat_Errors := 0;
180 G_Purge_Inf_Headers2 := 0;
181 G_Purge_Inf_Lines2 := 0;
182 G_Purge_Stat_Lines2 := 0;
183 G_Purge_Stat_Headers2 := 0;
184 G_Archive_Inf_Headers := 0;
185 G_Archive_Inf_Lines := 0;
186 G_Archive_Stat_Headers := 0;
187 G_Archive_Stat_Lines := 0;
188 G_Archive_Stat_Errors := 0;
189 G_Archive_Stat_Rec := 0;
190 G_Archive_Inf_Lines2 := 0;
191 G_Archive_Inf_Headers2 := 0;
192 G_Archive_Stat_Lines2 := 0;
193 G_Archive_Stat_Headers2 := 0;
194
195 END set_arch_purge_counts;
196
197 /* ---------------------------------------------------------------------
198 | PUBLIC PROCEDURE |
199 | delete_duplicate_data |
200 | |
201 | DESCRIPTION |
202 | Removes the duplicate entries from Archive Tables |
203 | |
204 | |
205 | CALLED BY |
206 | purge_process |
207 | REQUIRES |
208 | |
209 | HISTORY |
210 | 11-Jan-2011 Created Rtumati |
211 --------------------------------------------------------------------- */
212
213 PROCEDURE delete_duplicate_data IS
214 l_count NUMBER:=0;
215 BEGIN
216 IF (G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')) THEN
217 IF (G_OBJECTS IN ('BOTH','STATEMENT')) THEN
218
219 DELETE FROM CE_ARCH_RECONCILIATIONS car
220 WHERE car.statement_line_id IN
221 (SELECT cl.statement_line_id FROM ce_arch_lines cl WHERE
222 cl.statement_header_id IN (SELECT cah.statement_header_id
223 FROM ce_arch_headers cah ,
224 ce_statement_headers csh
225 WHERE cah.bank_account_id = csh.bank_account_id
226 AND csh.statement_number = cah.statement_number
227 AND csh.org_id = cah.org_id
228 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
229 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
230 AND csh.bank_account_id IN (SELECT bank_account_id
231 FROM ce_bank_accts_gt_v ba
232 WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
233 AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)));
234
235 IF( G_Archive_Purge_Option = 'Restore' ) THEN
236 G_Purge_Stat_Rec := SQL%ROWCOUNT;
237 END IF;
238
239 DELETE FROM CE_ARCH_RECON_ERRORS cre
240 WHERE cre.statement_line_id IN (SELECT cah.statement_header_id
241 FROM ce_arch_headers cah ,
242 ce_statement_headers csh
243 WHERE cah.bank_account_id = csh.bank_account_id
244 AND csh.statement_number = cah.statement_number
245 AND csh.org_id = cah.org_id
246 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
247 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
248 AND csh.bank_account_id IN (SELECT bank_account_id
249 FROM ce_bank_accts_gt_v ba
250 WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
251 AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));
252
253 IF( G_Archive_Purge_Option = 'Restore' ) THEN
254 G_Purge_Stat_Errors := SQL%ROWCOUNT;
255 END IF;
256
257 DELETE FROM CE_ARCH_LINES
258 WHERE statement_header_id IN (SELECT cah.statement_header_id
259 FROM ce_arch_headers cah ,
260 ce_statement_headers csh
261 WHERE cah.bank_account_id = csh.bank_account_id
262 AND csh.statement_number = cah.statement_number
263 AND csh.org_id = cah.org_id
264 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
265 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
266 AND csh.bank_account_id IN (SELECT bank_account_id
267 FROM ce_bank_accts_gt_v ba
268 WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
269 AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));
270
271 IF( G_Archive_Purge_Option = 'Restore' ) THEN
272 G_Purge_Stat_Lines := SQL%ROWCOUNT;
273 END IF;
274
275 DELETE FROM CE_ARCH_HEADERS
276 WHERE statement_header_id IN (SELECT cah.statement_header_id
277 FROM ce_arch_headers cah ,
278 ce_statement_headers csh
279 WHERE cah.bank_account_id = csh.bank_account_id
280 AND csh.statement_number = cah.statement_number
281 AND csh.org_id = cah.org_id
282 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
283 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
284 AND csh.bank_account_id IN (SELECT bank_account_id
285 FROM ce_bank_accts_gt_v ba
286 WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id))
287 AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id));
288
289 IF( G_Archive_Purge_Option = 'Restore' ) THEN
290 G_Purge_Stat_Headers := SQL%ROWCOUNT;
291 END IF;
292
293 END IF;
294
295 IF (G_OBJECTS IN ('BOTH','INTERFACE')) THEN
296
297 DELETE FROM CE_ARCH_INTERFACE_LINES cal
298 WHERE cal.statement_number ||'-'|| cal.bank_account_num IN
299 ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
300 FROM ce_statement_headers_int csh
301 WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
302 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
303 WHERE bb.branch_party_id = ba.bank_branch_id
304 AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
305 AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
306 AND csh.bank_account_num IN (SELECT bank_account_num
307 FROM ce_bank_accts_gt_v
308 WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
309 AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
310 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
311 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
312 AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
313 AND nvl(csh.intra_day_flag, 'N') = 'N');
314
315 IF( G_Archive_Purge_Option = 'Restore' ) THEN
316 G_Purge_Inf_Lines := SQL%ROWCOUNT;
317 END IF;
318
319 DELETE FROM CE_ARCH_INTERFACE_HEADERS cah
320 WHERE cah.statement_number ||'-'|| cah.bank_account_num IN
321 ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
322 FROM ce_statement_headers_int csh
323 WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
324 (SELECT bb.bank_branch_name
325 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
326 WHERE bb.branch_party_id = ba.bank_branch_id
327 AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
328 AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
329 AND csh.bank_account_num IN
330 (SELECT bank_account_num
331 FROM ce_bank_accts_gt_v
332 WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
333 AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
334 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
335 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
336 AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
337 AND nvl(csh.intra_day_flag, 'N') = 'N');
338
339 IF( G_Archive_Purge_Option = 'Restore' ) THEN
340 G_Purge_Inf_Headers := SQL%ROWCOUNT;
341 END IF;
342
343 END IF;
344 END IF;
345
346 IF (G_STATEMENT_TYPE IN ('BOTH','INTRADAY')) THEN
347 IF (G_OBJECTS IN ('BOTH','STATEMENT')) THEN
348
349 delete FROM CE_ARCH_INTRA_LINES cal
350 WHERE cal.statement_header_id IN (
351 SELECT cah.statement_header_id
352 FROM ce_intra_stmt_headers csh , CE_ARCH_INTRA_HEADERS cah
353 WHERE cah.statement_number = csh.statement_number
354 AND cah.org_id = csh.org_id
355 AND cah.bank_account_id = csh.bank_account_id
356 AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)
357 AND csh.bank_account_id IN
358 (SELECT bank_account_id
359 FROM ce_bank_accts_gt_v ba
360 WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
361 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
362 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));
363
364 IF( G_Archive_Purge_Option = 'Restore' ) THEN
365 G_Purge_Stat_Lines2 := SQL%ROWCOUNT;
366 END IF;
367
368 DELETE from CE_ARCH_INTRA_HEADERS cah2
369 WHERE cah2.statement_header_id IN (
370 SELECT cah.statement_header_id
371 FROM ce_intra_stmt_headers csh , CE_ARCH_INTRA_HEADERS cah
372 WHERE cah.statement_number = csh.statement_number
373 AND cah.org_id = csh.org_id
374 AND cah.bank_account_id = csh.bank_account_id
375 AND csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,csh.bank_account_id)
376 AND csh.bank_account_id IN
377 (SELECT bank_account_id
378 FROM ce_bank_accts_gt_v ba
379 WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
380 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
381 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date)));
382
383 IF( G_Archive_Purge_Option = 'Restore' ) THEN
384 G_Purge_Stat_Headers2 := SQL%ROWCOUNT;
385 END IF;
386
387
388 END IF;
389
390 IF (G_OBJECTS IN ('BOTH','INTERFACE')) THEN
391
392 DELETE FROM CE_ARCH_INTERFACE_LINES cal
393 WHERE cal.statement_number ||'-'|| cal.bank_account_num IN
394 ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
395 FROM ce_statement_headers_int csh
396 WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (
397 SELECT bb.bank_branch_name
398 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
399 WHERE bb.branch_party_id = ba.bank_branch_id
400 AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
401 AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
402 AND csh.bank_account_num IN
403 (SELECT bank_account_num
404 FROM ce_bank_accts_gt_v
405 WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
406 AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
407 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
408 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
409 AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
410 AND csh.intra_day_flag = 'Y');
411
412 IF( G_Archive_Purge_Option = 'Restore' ) THEN
413 G_Purge_Inf_Lines2 := SQL%ROWCOUNT;
414 END IF;
415
416
417 DELETE FROM CE_ARCH_INTERFACE_HEADERS cah
418 WHERE cah.statement_number ||'-'|| cah.bank_account_num IN
419 (SELECT csh.statement_number ||'-'|| csh.bank_account_num
420 FROM ce_statement_headers_int csh
421 WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
422 (SELECT bb.bank_branch_name
423 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
424 WHERE bb.branch_party_id = ba.bank_branch_id
425 AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
426 AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
427 AND csh.bank_account_num IN
428 (SELECT bank_account_num
429 FROM ce_bank_accts_gt_v
430 WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
431 AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
432 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
433 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
434 AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
435 AND csh.intra_day_flag = 'Y');
436
437 IF( G_Archive_Purge_Option = 'Restore' ) THEN
438 G_Purge_Inf_Headers2 := SQL%ROWCOUNT;
439 END IF;
440
441 END IF;
442 END IF;
443
444
445 END delete_duplicate_data;
446
447 /* ---------------------------------------------------------------------
448 | PUBLIC PROCEDURE |
449 | delete_archive_tables |
450 | |
451 | DESCRIPTION |
452 | Empty Archive Tables |
453 | |
454 | |
455 | CALLED BY |
456 | purge_process |
457 | REQUIRES |
458 | |
459 | HISTORY |
460 | 29-Dec-2010 Created Rtumati |
461 --------------------------------------------------------------------- */
462
463 PROCEDURE delete_archive_tables IS
464
465 BEGIN
466 IF (G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')) THEN
467 IF (G_OBJECTS IN ('BOTH','STATEMENT')) THEN
468
469 DELETE FROM CE_ARCH_RECONCILIATIONS_all
470 where org_id in (select org_id from ce_security_profiles_gt) or
471 legal_entity_id in (select org_id from ce_security_profiles_gt) or
472 REFERENCE_TYPE in ('JE_LINE', 'ROI_LINE','STATEMENT');
473
474 DELETE FROM CE_ARCH_RECON_ERRORS;
475
476 DELETE FROM CE_ARCH_LINES;
477
478 DELETE FROM CE_ARCH_HEADERS;
479
480 END IF;
481
482 IF (G_OBJECTS IN ('BOTH','INTERFACE')) THEN
483
484 DELETE FROM CE_ARCH_INTERFACE_LINES
485 WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN
486 (SELECT BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
487 FROM CE_ARCH_INTERFACE_HEADERS
488 WHERE NVL(INTRA_DAY_FLAG, 'N') = 'N');
489
490 DELETE FROM CE_ARCH_INTERFACE_HEADERS
491 WHERE NVL(INTRA_DAY_FLAG, 'N') = 'N';
492
493 END IF;
494 END IF;
495
496 IF (G_STATEMENT_TYPE IN ('BOTH','INTRADAY')) THEN
497 IF (G_OBJECTS IN ('BOTH','STATEMENT')) THEN
498
499 DELETE FROM CE_ARCH_INTRA_HEADERS;
500
501 DELETE FROM CE_ARCH_INTRA_LINES;
502
503 END IF;
504
505 IF (G_OBJECTS IN ('BOTH','INTERFACE')) THEN
506
507 DELETE FROM CE_ARCH_INTERFACE_LINES
508 WHERE BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER IN
509 (SELECT BANK_ACCOUNT_NUM || '-' || STATEMENT_NUMBER
510 FROM CE_ARCH_INTERFACE_HEADERS
511 WHERE NVL(INTRA_DAY_FLAG, 'N') = 'Y');
512
513 DELETE FROM CE_ARCH_INTERFACE_HEADERS
514 WHERE NVL(INTRA_DAY_FLAG, 'N') = 'Y';
515
516 END IF;
517 END IF;
518
519 END delete_archive_tables;
520
521
522 /* ---------------------------------------------------------------------
523 | PUBLIC PROCEDURE |
524 | arch_ce_statements |
525 | |
526 | DESCRIPTION |
527 | Archives the Bank statements with in the user specified range |
528 | |
529 | CALLED BY |
530 | Purge_Process |
531 | REQUIRES |
532 | |
533 | HISTORY |
534 | 29-Dec-2010 Created Rtumati |
535 --------------------------------------------------------------------- */
536
537 PROCEDURE arch_ce_statements
538 IS
539 BEGIN
540 fnd_file.put_line(fnd_file.log, 'CE_PURGE.G_BANK_ACCOUNT_ID: '||CE_PURGE.G_BANK_ACCOUNT_ID);
541 fnd_file.put_line(fnd_file.log, 'CE_PURGE.G_BANK_BRANCH_ID: '||CE_PURGE.G_BANK_BRANCH_ID);
542
543
544 IF (CE_PURGE.G_BANK_ACCOUNT_ID IS NOT NULL) THEN
545 fnd_file.put_line(fnd_file.log, 'IF (CE_PURGE.G_BANK_ACCOUNT_ID IS NOT NULL ');
546
547
548 -- bug 3676745 in order to archive/purge stmts, the user
549 -- must have access to all the reconciled trx's
550 -- org (ce_statement_reconcils_all.org_id and ce_statement_reconcils_all.legal_entity_id)
551 -- This is to ensure that there are no orphan trx data in the system
552
553
554 INSERT INTO ce_arch_headers (
555 STATEMENT_COMPLETE_FLAG,
556 --ORG_ID,
557 DOC_SEQUENCE_ID,
558 DOC_SEQUENCE_VALUE,
559 STATEMENT_HEADER_ID,
560 BANK_ACCOUNT_ID,
561 STATEMENT_NUMBER,
562 STATEMENT_DATE,
563 AUTO_LOADED_FLAG,
564 GL_DATE,
565 CHECK_DIGITS,
566 CONTROL_BEGIN_BALANCE,
567 CONTROL_TOTAL_DR,
568 CONTROL_TOTAL_CR,
569 CONTROL_END_BALANCE,
570 CASHFLOW_BALANCE,
571 INT_CALC_BALANCE,
572 ONE_DAY_FLOAT,
573 TWO_DAY_FLOAT,
574 CONTROL_DR_LINE_COUNT,
575 CONTROL_CR_LINE_COUNT,
576 CURRENCY_CODE,
577 ATTRIBUTE_CATEGORY,
578 ATTRIBUTE1,
579 ATTRIBUTE2,
580 ATTRIBUTE3,
581 ATTRIBUTE4,
582 ATTRIBUTE5,
583 ATTRIBUTE6,
584 ATTRIBUTE7,
585 ATTRIBUTE8,
586 ATTRIBUTE9,
587 ATTRIBUTE10,
588 ATTRIBUTE11,
589 ATTRIBUTE12,
590 ATTRIBUTE13,
591 ATTRIBUTE14,
592 ATTRIBUTE15,
593 LAST_UPDATE_LOGIN,
594 CREATED_BY,
595 CREATION_DATE,
596 LAST_UPDATED_BY,
597 LAST_UPDATE_DATE)
598 SELECT
599 STATEMENT_COMPLETE_FLAG,
600 --ORG_ID,
601 DOC_SEQUENCE_ID,
602 DOC_SEQUENCE_VALUE,
603 STATEMENT_HEADER_ID,
604 BANK_ACCOUNT_ID,
605 STATEMENT_NUMBER,
606 STATEMENT_DATE,
607 AUTO_LOADED_FLAG,
608 GL_DATE,
609 CHECK_DIGITS,
610 CONTROL_BEGIN_BALANCE,
611 CONTROL_TOTAL_DR,
612 CONTROL_TOTAL_CR,
613 CONTROL_END_BALANCE,
614 CASHFLOW_BALANCE,
615 INT_CALC_BALANCE,
616 ONE_DAY_FLOAT,
617 TWO_DAY_FLOAT,
618 CONTROL_DR_LINE_COUNT,
619 CONTROL_CR_LINE_COUNT,
620 CURRENCY_CODE,
621 ATTRIBUTE_CATEGORY,
622 ATTRIBUTE1,
623 ATTRIBUTE2,
624 ATTRIBUTE3,
625 ATTRIBUTE4,
626 ATTRIBUTE5,
627 ATTRIBUTE6,
628 ATTRIBUTE7,
629 ATTRIBUTE8,
630 ATTRIBUTE9,
631 ATTRIBUTE10,
632 ATTRIBUTE11,
633 ATTRIBUTE12,
634 ATTRIBUTE13,
635 ATTRIBUTE14,
636 ATTRIBUTE15,
637 LAST_UPDATE_LOGIN,
638 CREATED_BY,
639 CREATION_DATE,
640 LAST_UPDATED_BY,
641 LAST_UPDATE_DATE
642 FROM ce_statement_headers csh
643 WHERE csh.bank_account_id = CE_PURGE.G_BANK_ACCOUNT_ID
644 AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
645 AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
646 AND not exists
647 (select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
648 from ce_statement_headers sh, ce_statement_lines sl , ce_statement_reconcils_all sr
649 where sh.statement_header_id = sl.statement_header_id
650 and sl.statement_line_id= sr.statement_line_id
651 and sh.statement_header_id= csh.statement_header_id
652 and sh.bank_account_id = CE_PURGE.G_BANK_ACCOUNT_ID
653 AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
654 AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
655 and sr.CURRENT_RECORD_FLAG = 'Y'
656 and sr.STATUS_FLAG = 'M'
657 and (sr.org_id is not null or sr.legal_entity_id is not null)
658 and not exists
659 (select 1 from ce_security_profiles_gt lbg
660 where lbg.organization_id = sr.org_id or
661 sr.legal_entity_id = lbg.organization_id ) )
662 AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /*10400780 start*/
663 (SELECT 1
664 FROM ce_statement_lines csl, ce_statement_reconcils_all csr
665 WHERE csl.statement_header_id = csh.statement_header_id
666 and csl.statement_line_id= csr.statement_line_id
667 AND csr.CURRENT_RECORD_FLAG = 'Y'
668 and csr.STATUS_FLAG = 'M'
669 )
670 )
671 ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/
672
673
674 G_Archive_Stat_Headers := SQL%ROWCOUNT;
675
676 ELSIF (CE_PURGE.G_BANK_BRANCH_ID IS NOT NULL) THEN
677
678 fnd_file.put_line(fnd_file.log, 'IF (CE_PURGE.G_BANK_BRANCH_ID IS NOT NULL ');
679
680 INSERT INTO ce_arch_headers (
681 STATEMENT_COMPLETE_FLAG,
682 --ORG_ID,
683 DOC_SEQUENCE_ID,
684 DOC_SEQUENCE_VALUE,
685 STATEMENT_HEADER_ID,
686 BANK_ACCOUNT_ID,
687 STATEMENT_NUMBER,
688 STATEMENT_DATE,
689 AUTO_LOADED_FLAG,
690 GL_DATE,
691 CHECK_DIGITS,
692 CONTROL_BEGIN_BALANCE,
693 CONTROL_TOTAL_DR,
694 CONTROL_TOTAL_CR,
695 CONTROL_END_BALANCE,
696 CASHFLOW_BALANCE,
697 INT_CALC_BALANCE,
698 ONE_DAY_FLOAT,
699 TWO_DAY_FLOAT,
700 CONTROL_DR_LINE_COUNT,
701 CONTROL_CR_LINE_COUNT,
702 CURRENCY_CODE,
703 ATTRIBUTE_CATEGORY,
704 ATTRIBUTE1,
705 ATTRIBUTE2,
706 ATTRIBUTE3,
707 ATTRIBUTE4,
708 ATTRIBUTE5,
709 ATTRIBUTE6,
710 ATTRIBUTE7,
711 ATTRIBUTE8,
712 ATTRIBUTE9,
713 ATTRIBUTE10,
714 ATTRIBUTE11,
715 ATTRIBUTE12,
716 ATTRIBUTE13,
717 ATTRIBUTE14,
718 ATTRIBUTE15,
719 LAST_UPDATE_LOGIN,
720 CREATED_BY,
721 CREATION_DATE,
722 LAST_UPDATED_BY,
723 LAST_UPDATE_DATE)
724 SELECT
725 STATEMENT_COMPLETE_FLAG,
726 --ORG_ID,
727 DOC_SEQUENCE_ID,
728 DOC_SEQUENCE_VALUE,
729 STATEMENT_HEADER_ID,
730 BANK_ACCOUNT_ID,
731 STATEMENT_NUMBER,
732 STATEMENT_DATE,
733 AUTO_LOADED_FLAG,
734 GL_DATE,
735 CHECK_DIGITS,
736 CONTROL_BEGIN_BALANCE,
737 CONTROL_TOTAL_DR,
738 CONTROL_TOTAL_CR,
739 CONTROL_END_BALANCE,
740 CASHFLOW_BALANCE,
741 INT_CALC_BALANCE,
742 ONE_DAY_FLOAT,
743 TWO_DAY_FLOAT,
744 CONTROL_DR_LINE_COUNT,
745 CONTROL_CR_LINE_COUNT,
746 CURRENCY_CODE,
747 ATTRIBUTE_CATEGORY,
748 ATTRIBUTE1,
749 ATTRIBUTE2,
750 ATTRIBUTE3,
751 ATTRIBUTE4,
752 ATTRIBUTE5,
753 ATTRIBUTE6,
754 ATTRIBUTE7,
755 ATTRIBUTE8,
756 ATTRIBUTE9,
757 ATTRIBUTE10,
758 ATTRIBUTE11,
759 ATTRIBUTE12,
760 ATTRIBUTE13,
761 ATTRIBUTE14,
762 ATTRIBUTE15,
763 LAST_UPDATE_LOGIN,
764 CREATED_BY,
765 CREATION_DATE,
766 LAST_UPDATED_BY,
767 LAST_UPDATE_DATE
768 FROM ce_statement_headers csh
769 WHERE csh.bank_account_id IN (SELECT aba.bank_account_id
770 FROM CE_BANK_ACCTS_GT_V aba
771 WHERE aba.bank_branch_id = CE_PURGE.G_BANK_BRANCH_ID)
772 AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
773 AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
774 AND not exists
775 (select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
776 from ce_statement_headers sh, ce_statement_lines sl , ce_statement_reconcils_all sr
777 where sh.statement_header_id = sl.statement_header_id
778 and sl.statement_line_id= sr.statement_line_id
779 and sh.statement_header_id = csh.statement_header_id
780 and sh.bank_account_id IN (SELECT aba.bank_account_id
781 FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
782 WHERE aba.bank_branch_id = CE_PURGE.G_BANK_BRANCH_ID)
783 AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
784 AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
785 and sr.CURRENT_RECORD_FLAG = 'Y'
786 and sr.STATUS_FLAG = 'M'
787 and (sr.org_id is not null or sr.legal_entity_id is not null)
788 and not exists
789 (select 1 from ce_security_profiles_gt lbg
790 where lbg.organization_id = sr.org_id or
791 sr.legal_entity_id = lbg.organization_id )
792 ) AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /*10400780 start*/
793 (SELECT 1
794 FROM ce_statement_lines csl, ce_statement_reconcils_all csr
795 WHERE csl.statement_header_id = csh.statement_header_id
796 and csl.statement_line_id= csr.statement_line_id
797 AND csr.CURRENT_RECORD_FLAG = 'Y'
798 and csr.STATUS_FLAG = 'M'
799 )
800 )
801 ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/
802
803 G_Archive_Stat_Headers := SQL%ROWCOUNT;
804 ELSE
805 INSERT INTO ce_arch_headers (
806 STATEMENT_COMPLETE_FLAG,
807 --ORG_ID,
808 DOC_SEQUENCE_ID,
809 DOC_SEQUENCE_VALUE,
810 STATEMENT_HEADER_ID,
811 BANK_ACCOUNT_ID,
812 STATEMENT_NUMBER,
813 STATEMENT_DATE,
814 AUTO_LOADED_FLAG,
815 GL_DATE,
816 CHECK_DIGITS,
817 CONTROL_BEGIN_BALANCE,
818 CONTROL_TOTAL_DR,
819 CONTROL_TOTAL_CR,
820 CONTROL_END_BALANCE,
821 CASHFLOW_BALANCE,
822 INT_CALC_BALANCE,
823 ONE_DAY_FLOAT,
824 TWO_DAY_FLOAT,
825 CONTROL_DR_LINE_COUNT,
826 CONTROL_CR_LINE_COUNT,
827 CURRENCY_CODE,
828 ATTRIBUTE_CATEGORY,
829 ATTRIBUTE1,
830 ATTRIBUTE2,
831 ATTRIBUTE3,
832 ATTRIBUTE4,
833 ATTRIBUTE5,
834 ATTRIBUTE6,
835 ATTRIBUTE7,
836 ATTRIBUTE8,
837 ATTRIBUTE9,
838 ATTRIBUTE10,
839 ATTRIBUTE11,
840 ATTRIBUTE12,
841 ATTRIBUTE13,
842 ATTRIBUTE14,
843 ATTRIBUTE15,
844 LAST_UPDATE_LOGIN,
845 CREATED_BY,
846 CREATION_DATE,
847 LAST_UPDATED_BY,
848 LAST_UPDATE_DATE)
849 SELECT
850 STATEMENT_COMPLETE_FLAG,
851 --ORG_ID,
852 DOC_SEQUENCE_ID,
853 DOC_SEQUENCE_VALUE,
854 STATEMENT_HEADER_ID,
855 BANK_ACCOUNT_ID,
856 STATEMENT_NUMBER,
857 STATEMENT_DATE,
858 AUTO_LOADED_FLAG,
859 GL_DATE,
860 CHECK_DIGITS,
861 CONTROL_BEGIN_BALANCE,
862 CONTROL_TOTAL_DR,
863 CONTROL_TOTAL_CR,
864 CONTROL_END_BALANCE,
865 CASHFLOW_BALANCE,
866 INT_CALC_BALANCE,
867 ONE_DAY_FLOAT,
868 TWO_DAY_FLOAT,
869 CONTROL_DR_LINE_COUNT,
870 CONTROL_CR_LINE_COUNT,
871 CURRENCY_CODE,
872 ATTRIBUTE_CATEGORY,
873 ATTRIBUTE1,
874 ATTRIBUTE2,
875 ATTRIBUTE3,
876 ATTRIBUTE4,
877 ATTRIBUTE5,
878 ATTRIBUTE6,
879 ATTRIBUTE7,
880 ATTRIBUTE8,
881 ATTRIBUTE9,
882 ATTRIBUTE10,
883 ATTRIBUTE11,
884 ATTRIBUTE12,
885 ATTRIBUTE13,
886 ATTRIBUTE14,
887 ATTRIBUTE15,
888 LAST_UPDATE_LOGIN,
889 CREATED_BY,
890 CREATION_DATE,
891 LAST_UPDATED_BY,
892 LAST_UPDATE_DATE
893 FROM ce_statement_headers csh
894 WHERE csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
895 AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
896 AND not exists
897 (select 1
898 from ce_statement_headers sh, ce_statement_lines sl , ce_statement_reconcils_all sr
899 where sh.statement_header_id = sl.statement_header_id
900 and sl.statement_line_id= sr.statement_line_id
901 and sh.statement_header_id= csh.statement_header_id
902 and sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
903 AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
904 and sr.CURRENT_RECORD_FLAG = 'Y'
905 and sr.STATUS_FLAG = 'M'
906 and (sr.org_id is not null or sr.legal_entity_id is not null)
907 and not exists
908 (select 1 from ce_security_profiles_gt lbg
909 where lbg.organization_id = sr.org_id or sr.legal_entity_id = lbg.organization_id )
910 ) AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /*10400780 start*/
911 (SELECT 1
912 FROM ce_statement_lines csl, ce_statement_reconcils_all csr
913 WHERE csl.statement_header_id = csh.statement_header_id
914 and csl.statement_line_id= csr.statement_line_id
915 AND csr.CURRENT_RECORD_FLAG = 'Y'
916 and csr.STATUS_FLAG = 'M'
917 )
918 )
919 ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/
920
921 G_Archive_Stat_Headers := SQL%ROWCOUNT;
922 END IF;
923
924 INSERT INTO ce_arch_lines
925 (STATEMENT_LINE_ID,
926 STATEMENT_HEADER_ID,
927 LINE_NUMBER,
928 TRX_DATE,
929 TRX_TYPE,
930 AMOUNT,
931 CHARGES_AMOUNT,
932 STATUS,
933 TRX_CODE_ID,
934 EFFECTIVE_DATE,
935 BANK_TRX_NUMBER,
936 TRX_TEXT,
937 CUSTOMER_TEXT,
938 INVOICE_TEXT,
939 CURRENCY_CODE,
940 EXCHANGE_RATE_TYPE,
941 EXCHANGE_RATE,
942 EXCHANGE_RATE_DATE,
943 ORIGINAL_AMOUNT,
944 ATTRIBUTE_CATEGORY,
945 ATTRIBUTE1,
946 ATTRIBUTE2,
947 ATTRIBUTE3,
948 ATTRIBUTE4,
949 ATTRIBUTE5,
950 ATTRIBUTE6,
951 ATTRIBUTE7,
952 ATTRIBUTE8,
953 ATTRIBUTE9,
954 ATTRIBUTE10,
955 ATTRIBUTE11,
956 ATTRIBUTE12,
957 ATTRIBUTE13,
958 ATTRIBUTE14,
959 ATTRIBUTE15,
960 LAST_UPDATE_LOGIN,
961 CREATED_BY,
962 CREATION_DATE,
963 LAST_UPDATED_BY,
964 LAST_UPDATE_DATE,
965 RECONCILE_TO_STATEMENT_FLAG)
966 SELECT
967 STATEMENT_LINE_ID,
968 STATEMENT_HEADER_ID,
969 LINE_NUMBER,
970 TRX_DATE,
971 TRX_TYPE,
972 AMOUNT,
973 CHARGES_AMOUNT,
974 STATUS,
975 TRX_CODE_ID,
976 EFFECTIVE_DATE,
977 BANK_TRX_NUMBER,
978 TRX_TEXT,
979 CUSTOMER_TEXT,
980 INVOICE_TEXT,
981 CURRENCY_CODE,
982 EXCHANGE_RATE_TYPE,
983 EXCHANGE_RATE,
984 EXCHANGE_RATE_DATE,
985 ORIGINAL_AMOUNT,
986 ATTRIBUTE_CATEGORY,
987 ATTRIBUTE1,
988 ATTRIBUTE2,
989 ATTRIBUTE3,
990 ATTRIBUTE4,
991 ATTRIBUTE5,
992 ATTRIBUTE6,
993 ATTRIBUTE7,
994 ATTRIBUTE8,
995 ATTRIBUTE9,
996 ATTRIBUTE10,
997 ATTRIBUTE11,
998 ATTRIBUTE12,
999 ATTRIBUTE13,
1000 ATTRIBUTE14,
1001 ATTRIBUTE15,
1002 LAST_UPDATE_LOGIN,
1003 CREATED_BY,
1004 CREATION_DATE,
1005 LAST_UPDATED_BY,
1006 LAST_UPDATE_DATE,
1007 RECONCILE_TO_STATEMENT_FLAG
1008 FROM ce_statement_lines
1009 WHERE statement_header_id IN
1010 ( SELECT statement_header_id
1011 FROM ce_arch_headers );
1012 G_Archive_Stat_Lines := SQL%ROWCOUNT;
1013
1014 INSERT INTO ce_arch_reconciliations_all
1015 (STATEMENT_LINE_ID,
1016 REFERENCE_TYPE,
1017 REFERENCE_ID,
1018 JE_HEADER_ID,
1019 ORG_ID,
1020 LEGAL_ENTITY_ID,
1021 REFERENCE_STATUS,
1022 STATUS_FLAG,
1023 ACTION_FLAG,
1024 CURRENT_RECORD_FLAG,
1025 AUTO_RECONCILED_FLAG,
1026 CREATED_BY,
1027 CREATION_DATE,
1028 LAST_UPDATED_BY,
1029 LAST_UPDATE_DATE,
1030 REQUEST_ID,
1031 PROGRAM_APPLICATION_ID,
1032 PROGRAM_ID,
1033 PROGRAM_UPDATE_DATE,
1034 AMOUNT)
1035 SELECT
1036 STATEMENT_LINE_ID,
1037 REFERENCE_TYPE,
1038 REFERENCE_ID,
1039 JE_HEADER_ID,
1040 ORG_ID,
1041 LEGAL_ENTITY_ID,
1042 REFERENCE_STATUS,
1043 STATUS_FLAG,
1044 ACTION_FLAG,
1045 CURRENT_RECORD_FLAG,
1046 AUTO_RECONCILED_FLAG,
1047 CREATED_BY,
1048 CREATION_DATE,
1049 LAST_UPDATED_BY,
1050 LAST_UPDATE_DATE,
1051 REQUEST_ID,
1052 PROGRAM_APPLICATION_ID,
1053 PROGRAM_ID,
1054 PROGRAM_UPDATE_DATE,
1055 AMOUNT
1056 FROM ce_statement_recon_gt_v --ce_statement_reconciliations
1057 WHERE statement_line_id IN
1058 ( SELECT statement_line_id
1059 FROM ce_arch_lines );
1060 G_Archive_Stat_Rec := SQL%ROWCOUNT;
1061
1062 INSERT INTO ce_arch_recon_errors
1063 (STATEMENT_LINE_ID,
1064 MESSAGE_NAME,
1065 CREATION_DATE,
1066 CREATED_BY,
1067 APPLICATION_SHORT_NAME,
1068 STATEMENT_HEADER_ID)
1069 SELECT
1070 STATEMENT_LINE_ID,
1071 MESSAGE_NAME,
1072 CREATION_DATE,
1073 CREATED_BY,
1074 APPLICATION_SHORT_NAME,
1075 STATEMENT_HEADER_ID
1076 FROM ce_reconciliation_errors
1077 WHERE statement_line_id IN
1078 ( SELECT statement_line_id
1079 FROM ce_arch_lines );
1080 G_Archive_Stat_Errors := SQL%ROWCOUNT;
1081
1082 END arch_ce_statements;
1083
1084 /* ---------------------------------------------------------------------
1085 | PUBLIC PROCEDURE |
1086 | arch_ce_interface |
1087 | |
1088 | DESCRIPTION |
1089 | Archives the interface data with in the user specified range |
1090 | |
1091 | CALLED BY |
1092 | Purge_Process |
1093 | REQUIRES |
1094 | |
1095 | HISTORY |
1096 | 29-Dec-2010 Created Rtumati |
1097 --------------------------------------------------------------------- */
1098
1099
1100 PROCEDURE arch_ce_interface(p_intra_day_flag IN VARCHAR2)
1101 IS
1102 BEGIN
1103
1104 INSERT INTO ce_arch_interface_headers
1105 (STATEMENT_NUMBER,
1106 BANK_ACCOUNT_NUM,
1107 STATEMENT_DATE,
1108 BANK_NAME,
1109 BANK_BRANCH_NAME,
1110 CHECK_DIGITS,
1111 CONTROL_BEGIN_BALANCE,
1112 CONTROL_TOTAL_DR,
1113 CONTROL_TOTAL_CR,
1114 CONTROL_END_BALANCE,
1115 CASHFLOW_BALANCE,
1116 INT_CALC_BALANCE,
1117 ONE_DAY_FLOAT,
1118 TWO_DAY_FLOAT,
1119 CONTROL_DR_LINE_COUNT,
1120 CONTROL_CR_LINE_COUNT,
1121 CONTROL_LINE_COUNT,
1122 RECORD_STATUS_FLAG,
1123 CURRENCY_CODE,
1124 ATTRIBUTE_CATEGORY,
1125 ATTRIBUTE1,
1126 ATTRIBUTE2,
1127 ATTRIBUTE3,
1128 ATTRIBUTE4,
1129 ATTRIBUTE5,
1130 ATTRIBUTE6,
1131 ATTRIBUTE7,
1132 ATTRIBUTE8,
1133 ATTRIBUTE9,
1134 ATTRIBUTE10,
1135 ATTRIBUTE11,
1136 ATTRIBUTE12,
1137 ATTRIBUTE13,
1138 ATTRIBUTE14,
1139 ATTRIBUTE15,
1140 CREATED_BY,
1141 CREATION_DATE,
1142 LAST_UPDATED_BY,
1143 LAST_UPDATE_DATE,
1144 --ORG_ID
1145 INTRA_DAY_FLAG)
1146 SELECT
1147 STATEMENT_NUMBER,
1148 BANK_ACCOUNT_NUM,
1149 STATEMENT_DATE,
1150 BANK_NAME,
1151 BANK_BRANCH_NAME,
1152 CHECK_DIGITS,
1153 CONTROL_BEGIN_BALANCE,
1154 CONTROL_TOTAL_DR,
1155 CONTROL_TOTAL_CR,
1156 CONTROL_END_BALANCE,
1157 CASHFLOW_BALANCE,
1158 INT_CALC_BALANCE,
1159 ONE_DAY_FLOAT,
1160 TWO_DAY_FLOAT,
1161 CONTROL_DR_LINE_COUNT,
1162 CONTROL_CR_LINE_COUNT,
1163 CONTROL_LINE_COUNT,
1164 RECORD_STATUS_FLAG,
1165 CURRENCY_CODE,
1166 ATTRIBUTE_CATEGORY,
1167 ATTRIBUTE1,
1168 ATTRIBUTE2,
1169 ATTRIBUTE3,
1170 ATTRIBUTE4,
1171 ATTRIBUTE5,
1172 ATTRIBUTE6,
1173 ATTRIBUTE7,
1174 ATTRIBUTE8,
1175 ATTRIBUTE9,
1176 ATTRIBUTE10,
1177 ATTRIBUTE11,
1178 ATTRIBUTE12,
1179 ATTRIBUTE13,
1180 ATTRIBUTE14,
1181 ATTRIBUTE15,
1182 CREATED_BY,
1183 CREATION_DATE,
1184 LAST_UPDATED_BY,
1185 LAST_UPDATE_DATE,
1186 INTRA_DAY_FLAG
1187 --ORG_ID
1188 FROM ce_statement_headers_int csh
1189 WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1190 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1191 WHERE bb.branch_party_id = ba.bank_branch_id
1192 AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1193 AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1194 AND csh.bank_account_num IN (SELECT bank_account_num
1195 FROM ce_bank_accts_gt_v
1196 WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1197 AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1198 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1199 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1200 AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
1201 AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag ;
1202
1203 IF( p_intra_day_flag = 'N' ) THEN
1204 G_Archive_Inf_Headers := SQL%ROWCOUNT;
1205 ELSE
1206 G_Archive_Inf_Headers2 := SQL%ROWCOUNT;
1207 END IF;
1208
1209
1210 INSERT INTO ce_arch_interface_lines
1211 (EXCHANGE_RATE_DATE,
1212 EXCHANGE_RATE,
1213 BANK_TRX_NUMBER,
1214 CUSTOMER_TEXT,
1215 CREATED_BY,
1216 CREATION_DATE,
1217 LAST_UPDATED_BY,
1218 LAST_UPDATE_DATE,
1219 ATTRIBUTE_CATEGORY,
1220 ATTRIBUTE1,
1221 ATTRIBUTE2,
1222 ATTRIBUTE3,
1223 ATTRIBUTE4,
1224 ATTRIBUTE5,
1225 ATTRIBUTE6,
1226 ATTRIBUTE7,
1227 ATTRIBUTE8,
1228 ATTRIBUTE9,
1229 ATTRIBUTE10,
1230 ATTRIBUTE11,
1231 ATTRIBUTE12,
1232 ATTRIBUTE13,
1233 ATTRIBUTE14,
1234 ATTRIBUTE15,
1235 ORIGINAL_AMOUNT,
1236 BANK_ACCOUNT_NUM,
1237 STATEMENT_NUMBER,
1238 LINE_NUMBER,
1239 TRX_DATE,
1240 TRX_CODE,
1241 EFFECTIVE_DATE,
1242 TRX_TEXT,
1243 INVOICE_TEXT,
1244 AMOUNT,
1245 CHARGES_AMOUNT,
1246 CURRENCY_CODE,
1247 USER_EXCHANGE_RATE_TYPE)
1248 SELECT
1249 EXCHANGE_RATE_DATE,
1250 EXCHANGE_RATE,
1251 BANK_TRX_NUMBER,
1252 CUSTOMER_TEXT,
1253 CREATED_BY,
1254 CREATION_DATE,
1255 LAST_UPDATED_BY,
1256 LAST_UPDATE_DATE,
1257 ATTRIBUTE_CATEGORY,
1258 ATTRIBUTE1,
1259 ATTRIBUTE2,
1260 ATTRIBUTE3,
1261 ATTRIBUTE4,
1262 ATTRIBUTE5,
1263 ATTRIBUTE6,
1264 ATTRIBUTE7,
1265 ATTRIBUTE8,
1266 ATTRIBUTE9,
1267 ATTRIBUTE10,
1268 ATTRIBUTE11,
1269 ATTRIBUTE12,
1270 ATTRIBUTE13,
1271 ATTRIBUTE14,
1272 ATTRIBUTE15,
1273 ORIGINAL_AMOUNT,
1274 BANK_ACCOUNT_NUM,
1275 STATEMENT_NUMBER,
1276 LINE_NUMBER,
1277 TRX_DATE,
1278 TRX_CODE,
1279 EFFECTIVE_DATE,
1280 TRX_TEXT,
1281 INVOICE_TEXT,
1282 AMOUNT,
1283 CHARGES_AMOUNT,
1284 CURRENCY_CODE,
1285 USER_EXCHANGE_RATE_TYPE
1286 FROM ce_statement_lines_interface csl
1287 WHERE csl.statement_number ||'-'|| csl.bank_account_num IN (
1288 SELECT csh.statement_number ||'-'|| csh.bank_account_num
1289 FROM ce_statement_headers_int csh
1290 WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1291 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1292 WHERE bb.branch_party_id = ba.bank_branch_id
1293 AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1294 AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1295 AND csh.bank_account_num IN (SELECT cv.bank_account_num
1296 FROM ce_bank_accts_gt_v cv
1297 WHERE cv.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,cv.bank_account_id)
1298 AND cv.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, cv.bank_branch_id))
1299 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1300 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1301 AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
1302 AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag);
1303
1304 IF( p_intra_day_flag = 'N' ) THEN
1305 G_Archive_Inf_Lines := SQL%ROWCOUNT;
1306 ELSE
1307 G_Archive_Inf_Lines2 := SQL%ROWCOUNT;
1308 END IF;
1309
1310
1311 END arch_ce_interface;
1312
1313 /* ---------------------------------------------------------------------
1314 | PUBLIC PROCEDURE |
1315 | arch_ce_intra_statements |
1316 | |
1317 | DESCRIPTION |
1318 | Archives the Intra day statements with in the user specified range |
1319 | |
1320 | CALLED BY |
1321 | Purge_Process |
1322 | REQUIRES |
1323 | |
1324 | HISTORY |
1325 | 29-Dec-2010 Created Rtumati |
1326 --------------------------------------------------------------------- */
1327
1328 PROCEDURE arch_ce_intra_statements
1329 is
1330 BEGIN
1331
1332 IF (G_BANK_ACCOUNT_ID IS NOT NULL) THEN
1333 INSERT INTO ce_arch_intra_headers (
1334 STATEMENT_COMPLETE_FLAG,
1335 --ORG_ID,
1336 DOC_SEQUENCE_ID,
1337 DOC_SEQUENCE_VALUE,
1338 STATEMENT_HEADER_ID,
1339 BANK_ACCOUNT_ID,
1340 STATEMENT_NUMBER,
1341 STATEMENT_DATE,
1342 AUTO_LOADED_FLAG,
1343 GL_DATE,
1344 CHECK_DIGITS,
1345 CONTROL_BEGIN_BALANCE,
1346 CONTROL_TOTAL_DR,
1347 CONTROL_TOTAL_CR,
1348 CONTROL_END_BALANCE,
1349 CASHFLOW_BALANCE,
1350 INT_CALC_BALANCE,
1351 ONE_DAY_FLOAT,
1352 TWO_DAY_FLOAT,
1353 CONTROL_DR_LINE_COUNT,
1354 CONTROL_CR_LINE_COUNT,
1355 CURRENCY_CODE,
1356 ATTRIBUTE_CATEGORY,
1357 ATTRIBUTE1,
1358 ATTRIBUTE2,
1359 ATTRIBUTE3,
1360 ATTRIBUTE4,
1361 ATTRIBUTE5,
1362 ATTRIBUTE6,
1363 ATTRIBUTE7,
1364 ATTRIBUTE8,
1365 ATTRIBUTE9,
1366 ATTRIBUTE10,
1367 ATTRIBUTE11,
1368 ATTRIBUTE12,
1369 ATTRIBUTE13,
1370 ATTRIBUTE14,
1371 ATTRIBUTE15,
1372 LAST_UPDATE_LOGIN,
1373 CREATED_BY,
1374 CREATION_DATE,
1375 LAST_UPDATED_BY,
1376 LAST_UPDATE_DATE)
1377 SELECT
1378 STATEMENT_COMPLETE_FLAG,
1379 --ORG_ID,
1380 DOC_SEQUENCE_ID,
1381 DOC_SEQUENCE_VALUE,
1382 STATEMENT_HEADER_ID,
1383 BANK_ACCOUNT_ID,
1384 STATEMENT_NUMBER,
1385 STATEMENT_DATE,
1386 AUTO_LOADED_FLAG,
1387 GL_DATE,
1388 CHECK_DIGITS,
1389 CONTROL_BEGIN_BALANCE,
1390 CONTROL_TOTAL_DR,
1391 CONTROL_TOTAL_CR,
1392 CONTROL_END_BALANCE,
1393 CASHFLOW_BALANCE,
1394 INT_CALC_BALANCE,
1395 ONE_DAY_FLOAT,
1396 TWO_DAY_FLOAT,
1397 CONTROL_DR_LINE_COUNT,
1398 CONTROL_CR_LINE_COUNT,
1399 CURRENCY_CODE,
1400 ATTRIBUTE_CATEGORY,
1401 ATTRIBUTE1,
1402 ATTRIBUTE2,
1403 ATTRIBUTE3,
1404 ATTRIBUTE4,
1405 ATTRIBUTE5,
1406 ATTRIBUTE6,
1407 ATTRIBUTE7,
1408 ATTRIBUTE8,
1409 ATTRIBUTE9,
1410 ATTRIBUTE10,
1411 ATTRIBUTE11,
1412 ATTRIBUTE12,
1413 ATTRIBUTE13,
1414 ATTRIBUTE14,
1415 ATTRIBUTE15,
1416 LAST_UPDATE_LOGIN,
1417 CREATED_BY,
1418 CREATION_DATE,
1419 LAST_UPDATED_BY,
1420 LAST_UPDATE_DATE
1421 FROM ce_intra_stmt_headers csh
1422 WHERE csh.bank_account_id = G_BANK_ACCOUNT_ID
1423 AND trunc(csh.statement_date) >= NVL(G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1424 AND trunc(csh.statement_date) <= Nvl(G_STATEMENT_DATE_TO, trunc(csh.statement_date));
1425 G_Archive_Stat_Headers2 := SQL%ROWCOUNT;
1426
1427 ELSIF (G_BANK_BRANCH_ID IS NOT NULL) THEN
1428
1429 INSERT INTO ce_arch_intra_headers (
1430 STATEMENT_COMPLETE_FLAG,
1431 --ORG_ID,
1432 DOC_SEQUENCE_ID,
1433 DOC_SEQUENCE_VALUE,
1434 STATEMENT_HEADER_ID,
1435 BANK_ACCOUNT_ID,
1436 STATEMENT_NUMBER,
1437 STATEMENT_DATE,
1438 AUTO_LOADED_FLAG,
1439 GL_DATE,
1440 CHECK_DIGITS,
1441 CONTROL_BEGIN_BALANCE,
1442 CONTROL_TOTAL_DR,
1443 CONTROL_TOTAL_CR,
1444 CONTROL_END_BALANCE,
1445 CASHFLOW_BALANCE,
1446 INT_CALC_BALANCE,
1447 ONE_DAY_FLOAT,
1448 TWO_DAY_FLOAT,
1449 CONTROL_DR_LINE_COUNT,
1450 CONTROL_CR_LINE_COUNT,
1451 CURRENCY_CODE,
1452 ATTRIBUTE_CATEGORY,
1453 ATTRIBUTE1,
1454 ATTRIBUTE2,
1455 ATTRIBUTE3,
1456 ATTRIBUTE4,
1457 ATTRIBUTE5,
1458 ATTRIBUTE6,
1459 ATTRIBUTE7,
1460 ATTRIBUTE8,
1461 ATTRIBUTE9,
1462 ATTRIBUTE10,
1463 ATTRIBUTE11,
1464 ATTRIBUTE12,
1465 ATTRIBUTE13,
1466 ATTRIBUTE14,
1467 ATTRIBUTE15,
1468 LAST_UPDATE_LOGIN,
1469 CREATED_BY,
1470 CREATION_DATE,
1471 LAST_UPDATED_BY,
1472 LAST_UPDATE_DATE)
1473 SELECT
1474 STATEMENT_COMPLETE_FLAG,
1475 --ORG_ID,
1476 DOC_SEQUENCE_ID,
1477 DOC_SEQUENCE_VALUE,
1478 STATEMENT_HEADER_ID,
1479 BANK_ACCOUNT_ID,
1480 STATEMENT_NUMBER,
1481 STATEMENT_DATE,
1482 AUTO_LOADED_FLAG,
1483 GL_DATE,
1484 CHECK_DIGITS,
1485 CONTROL_BEGIN_BALANCE,
1486 CONTROL_TOTAL_DR,
1487 CONTROL_TOTAL_CR,
1488 CONTROL_END_BALANCE,
1489 CASHFLOW_BALANCE,
1490 INT_CALC_BALANCE,
1491 ONE_DAY_FLOAT,
1492 TWO_DAY_FLOAT,
1493 CONTROL_DR_LINE_COUNT,
1494 CONTROL_CR_LINE_COUNT,
1495 CURRENCY_CODE,
1496 ATTRIBUTE_CATEGORY,
1497 ATTRIBUTE1,
1498 ATTRIBUTE2,
1499 ATTRIBUTE3,
1500 ATTRIBUTE4,
1501 ATTRIBUTE5,
1502 ATTRIBUTE6,
1503 ATTRIBUTE7,
1504 ATTRIBUTE8,
1505 ATTRIBUTE9,
1506 ATTRIBUTE10,
1507 ATTRIBUTE11,
1508 ATTRIBUTE12,
1509 ATTRIBUTE13,
1510 ATTRIBUTE14,
1511 ATTRIBUTE15,
1512 LAST_UPDATE_LOGIN,
1513 CREATED_BY,
1514 CREATION_DATE,
1515 LAST_UPDATED_BY,
1516 LAST_UPDATE_DATE
1517 FROM ce_intra_stmt_headers csh
1518 WHERE csh.bank_account_id IN (SELECT aba.bank_account_id
1519 FROM ce_BANK_ACCOUNTS aba
1520 WHERE aba.bank_branch_id = G_BANK_BRANCH_ID)
1521 AND trunc(csh.statement_date) >= NVL(G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1522 AND trunc(csh.statement_date) <= NVL(G_STATEMENT_DATE_TO, trunc(csh.statement_date));
1523 G_Archive_Stat_Headers2 := SQL%ROWCOUNT;
1524
1525 ELSE
1526
1527 INSERT INTO ce_arch_intra_headers (
1528 STATEMENT_COMPLETE_FLAG,
1529 --ORG_ID,
1530 DOC_SEQUENCE_ID,
1531 DOC_SEQUENCE_VALUE,
1532 STATEMENT_HEADER_ID,
1533 BANK_ACCOUNT_ID,
1534 STATEMENT_NUMBER,
1535 STATEMENT_DATE,
1536 AUTO_LOADED_FLAG,
1537 GL_DATE,
1538 CHECK_DIGITS,
1539 CONTROL_BEGIN_BALANCE,
1540 CONTROL_TOTAL_DR,
1541 CONTROL_TOTAL_CR,
1542 CONTROL_END_BALANCE,
1543 CASHFLOW_BALANCE,
1544 INT_CALC_BALANCE,
1545 ONE_DAY_FLOAT,
1546 TWO_DAY_FLOAT,
1547 CONTROL_DR_LINE_COUNT,
1548 CONTROL_CR_LINE_COUNT,
1549 CURRENCY_CODE,
1550 ATTRIBUTE_CATEGORY,
1551 ATTRIBUTE1,
1552 ATTRIBUTE2,
1553 ATTRIBUTE3,
1554 ATTRIBUTE4,
1555 ATTRIBUTE5,
1556 ATTRIBUTE6,
1557 ATTRIBUTE7,
1558 ATTRIBUTE8,
1559 ATTRIBUTE9,
1560 ATTRIBUTE10,
1561 ATTRIBUTE11,
1562 ATTRIBUTE12,
1563 ATTRIBUTE13,
1564 ATTRIBUTE14,
1565 ATTRIBUTE15,
1566 LAST_UPDATE_LOGIN,
1567 CREATED_BY,
1568 CREATION_DATE,
1569 LAST_UPDATED_BY,
1570 LAST_UPDATE_DATE)
1571 SELECT
1572 STATEMENT_COMPLETE_FLAG,
1573 --ORG_ID,
1574 DOC_SEQUENCE_ID,
1575 DOC_SEQUENCE_VALUE,
1576 STATEMENT_HEADER_ID,
1577 BANK_ACCOUNT_ID,
1578 STATEMENT_NUMBER,
1579 STATEMENT_DATE,
1580 AUTO_LOADED_FLAG,
1581 GL_DATE,
1582 CHECK_DIGITS,
1583 CONTROL_BEGIN_BALANCE,
1584 CONTROL_TOTAL_DR,
1585 CONTROL_TOTAL_CR,
1586 CONTROL_END_BALANCE,
1587 CASHFLOW_BALANCE,
1588 INT_CALC_BALANCE,
1589 ONE_DAY_FLOAT,
1590 TWO_DAY_FLOAT,
1591 CONTROL_DR_LINE_COUNT,
1592 CONTROL_CR_LINE_COUNT,
1593 CURRENCY_CODE,
1594 ATTRIBUTE_CATEGORY,
1595 ATTRIBUTE1,
1596 ATTRIBUTE2,
1597 ATTRIBUTE3,
1598 ATTRIBUTE4,
1599 ATTRIBUTE5,
1600 ATTRIBUTE6,
1601 ATTRIBUTE7,
1602 ATTRIBUTE8,
1603 ATTRIBUTE9,
1604 ATTRIBUTE10,
1605 ATTRIBUTE11,
1606 ATTRIBUTE12,
1607 ATTRIBUTE13,
1608 ATTRIBUTE14,
1609 ATTRIBUTE15,
1610 LAST_UPDATE_LOGIN,
1611 CREATED_BY,
1612 CREATION_DATE,
1613 LAST_UPDATED_BY,
1614 LAST_UPDATE_DATE
1615 FROM ce_intra_stmt_headers csh
1616 WHERE trunc(csh.statement_date) >= NVL(G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1617 AND trunc(csh.statement_date) <= NVL(G_STATEMENT_DATE_TO, trunc(csh.statement_date));
1618
1619 G_Archive_Stat_Headers2 := SQL%ROWCOUNT;
1620
1621 END IF;
1622
1623 INSERT INTO ce_arch_intra_lines
1624 (STATEMENT_LINE_ID,
1625 STATEMENT_HEADER_ID,
1626 LINE_NUMBER,
1627 TRX_DATE,
1628 TRX_TYPE,
1629 AMOUNT,
1630 CHARGES_AMOUNT,
1631 STATUS,
1632 TRX_CODE_ID,
1633 EFFECTIVE_DATE,
1634 BANK_TRX_NUMBER,
1635 TRX_TEXT,
1636 CUSTOMER_TEXT,
1637 INVOICE_TEXT,
1638 CURRENCY_CODE,
1639 EXCHANGE_RATE_TYPE,
1640 EXCHANGE_RATE,
1641 EXCHANGE_RATE_DATE,
1642 ORIGINAL_AMOUNT,
1643 ATTRIBUTE_CATEGORY,
1644 ATTRIBUTE1,
1645 ATTRIBUTE2,
1646 ATTRIBUTE3,
1647 ATTRIBUTE4,
1648 ATTRIBUTE5,
1649 ATTRIBUTE6,
1650 ATTRIBUTE7,
1651 ATTRIBUTE8,
1652 ATTRIBUTE9,
1653 ATTRIBUTE10,
1654 ATTRIBUTE11,
1655 ATTRIBUTE12,
1656 ATTRIBUTE13,
1657 ATTRIBUTE14,
1658 ATTRIBUTE15,
1659 LAST_UPDATE_LOGIN,
1660 CREATED_BY,
1661 CREATION_DATE,
1662 LAST_UPDATED_BY,
1663 LAST_UPDATE_DATE,
1664 RECONCILE_TO_STATEMENT_FLAG)
1665 SELECT
1666 STATEMENT_LINE_ID,
1667 STATEMENT_HEADER_ID,
1668 LINE_NUMBER,
1669 TRX_DATE,
1670 TRX_TYPE,
1671 AMOUNT,
1672 CHARGES_AMOUNT,
1673 STATUS,
1674 TRX_CODE_ID,
1675 EFFECTIVE_DATE,
1676 BANK_TRX_NUMBER,
1677 TRX_TEXT,
1678 CUSTOMER_TEXT,
1679 INVOICE_TEXT,
1680 CURRENCY_CODE,
1681 EXCHANGE_RATE_TYPE,
1682 EXCHANGE_RATE,
1683 EXCHANGE_RATE_DATE,
1684 ORIGINAL_AMOUNT,
1685 ATTRIBUTE_CATEGORY,
1686 ATTRIBUTE1,
1687 ATTRIBUTE2,
1688 ATTRIBUTE3,
1689 ATTRIBUTE4,
1690 ATTRIBUTE5,
1691 ATTRIBUTE6,
1692 ATTRIBUTE7,
1693 ATTRIBUTE8,
1694 ATTRIBUTE9,
1695 ATTRIBUTE10,
1696 ATTRIBUTE11,
1697 ATTRIBUTE12,
1698 ATTRIBUTE13,
1699 ATTRIBUTE14,
1700 ATTRIBUTE15,
1701 LAST_UPDATE_LOGIN,
1702 CREATED_BY,
1703 CREATION_DATE,
1704 LAST_UPDATED_BY,
1705 LAST_UPDATE_DATE,
1706 RECONCILE_TO_STATEMENT_FLAG
1707 FROM ce_intra_stmt_lines
1708 WHERE statement_header_id IN
1709 ( SELECT statement_header_id
1710 FROM ce_arch_intra_headers );
1711
1712 G_Archive_Stat_Lines2 := SQL%ROWCOUNT;
1713
1714 END arch_ce_intra_statements;
1715
1716
1717 /* ---------------------------------------------------------------------
1718 | PUBLIC PROCEDURE |
1719 | purge_process |
1720 | |
1721 | DESCRIPTION |
1722 | Completes the Archive and purge process |
1723 | |
1724 | CALLED BY |
1725 | CEPURGE.rdf from BeforeReport Trigger |
1726 | REQUIRES |
1727 | |
1728 | HISTORY |
1729 | 29-Dec-2010 Created Rtumati |
1730 --------------------------------------------------------------------- */
1731 PROCEDURE purge_process is
1732
1733 l_message FND_NEW_MESSAGES.message_text%TYPE;
1734 l_bank_branch_id NUMBER;
1735 error_found BOOLEAN DEFAULT FALSE;
1736 X_header_id NUMBER DEFAULT 0;
1737 X_line_id NUMBER DEFAULT 0;
1738 purge_lines NUMBER DEFAULT 0;
1739 purge_headers NUMBER DEFAULT 0;
1740 purge_recons NUMBER DEFAULT 0;
1741 purge_recon_errors NUMBER DEFAULT 0;
1742 l_def_org_id number;
1743 l_sp_id number;
1744 -- bug 3676745 in order to archive/purge stmts, the user
1745 -- must have access to all the reconciled trx's
1746 -- org (ce_statement_reconcils_all.org_id and ce_statement_reconcils_all.legal_entity_id)
1747 -- This is to ensure that there are no orphan trx data in the system
1748 -- 1) Main query - should query from security view (ce_bank_accts_gt_v)
1749 -- 2) Sub-query (not exists) should select from ce_bank_accounts table,
1750 -- This query is to select all stmts that the users do not have access to.
1751 -- If stmts exists in this subquery then the stmt should not be deleted.
1752 -- This is to ensure that there are no orphan trx data in the system
1753
1754 -- Cursor to purge the statement Headers
1755 CURSOR C_PURGE_HEADERS IS
1756 SELECT csh.statement_header_id
1757 FROM ce_statement_headers csh
1758 WHERE csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1759 AND csh.bank_account_id IN
1760 (SELECT bank_account_id
1761 FROM ce_bank_accts_gt_v ba
1762 WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
1763 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1764 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1765 AND not exists
1766 (select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
1767 from ce_statement_headers sh, ce_statement_lines sl , ce_statement_reconcils_all sr
1768 where sh.statement_header_id = sl.statement_header_id
1769 and sl.statement_line_id= sr.statement_line_id
1770 -- AND sl.status = Decode(G_Only_Unrec,'Y','RECONCILED',sl.status)
1771 and sh.statement_header_id= csh.statement_header_id
1772 and sh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1773 AND sh.bank_account_id IN
1774 (SELECT bank_account_id
1775 FROM ce_bank_accounts ba --ce_bank_accts_gt_v ba
1776 WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
1777 AND trunc(sh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(sh.statement_date))
1778 AND trunc(sh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(sh.statement_date))
1779 and sr.CURRENT_RECORD_FLAG = 'Y'
1780 and sr.STATUS_FLAG = 'M'
1781 and (sr.org_id is not null or sr.legal_entity_id is not null)
1782 AND not exists
1783 (select 1 from ce_security_profiles_gt lbg
1784 where lbg.organization_id = sr.org_id or sr.legal_entity_id = lbg.organization_id )
1785 )
1786 AND (( CE_PURGE.G_Only_Unrec = 'Y' AND ( NOT EXISTS /* 10400780 start*/
1787 (SELECT 1
1788 FROM ce_statement_lines csl, ce_statement_reconcils_all csr
1789 WHERE csl.statement_header_id = csh.statement_header_id
1790 and csl.statement_line_id= csr.statement_line_id
1791 AND csr.CURRENT_RECORD_FLAG = 'Y'
1792 and csr.STATUS_FLAG = 'M'
1793 )
1794 )
1795 ) OR CE_PURGE.G_Only_Unrec = 'N'); /*10400780 end*/
1796
1797
1798 -- Cursor to purge the statement Lines
1799 CURSOR C_PURGE_LINES IS
1800 SELECT csl.statement_line_id
1801 FROM ce_statement_lines csl
1802 WHERE csl.statement_header_id = X_header_id;
1803
1804 -- Cursor to purge the Intra day statement Headers
1805 CURSOR C_PURGE_INTRA_HEADERS IS
1806 SELECT csh.statement_header_id
1807 FROM ce_intra_stmt_headers csh
1808 WHERE csh.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1809 AND csh.bank_account_id IN
1810 (SELECT bank_account_id
1811 FROM ce_bank_accts_gt_v ba
1812 WHERE ba.bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID,ba.bank_branch_id) )
1813 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1814 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date));
1815
1816 -- Cursor to purge the Intra day statement lines
1817 CURSOR C_PURGE_INTRA_LINES IS
1818 SELECT csl.statement_line_id
1819 FROM ce_intra_stmt_lines csl
1820 WHERE csl.statement_header_id = X_header_id;
1821
1822 BEGIN
1823
1824 --
1825 -- First we check that the archive tables are empty.
1826 -- They need to be emply, if the user has selected the delete option
1827
1828 --
1829 IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH' AND NVL(CE_PURGE.G_Delete_Or_Append_Action, 'APPEND') = 'DELETE') THEN
1830 delete_archive_tables;
1831 ELSIF (CE_PURGE.G_Archive_Purge_Option = 'BOTH' AND NVL(CE_PURGE.G_Delete_Or_Append_Action, 'APPEND') = 'APPEND') THEN
1832 delete_duplicate_data;
1833 END IF;
1834
1835 -- Perform the archive/purge based on the user parameters
1836 --
1837
1838 IF (CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')) THEN
1839 IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN
1840 IF( CE_PURGE.G_Archive_Purge_Option IN ('BOTH')) THEN
1841 -- Archives the Ce statement Headers lines along with reconcile history and reconcile errors
1842 arch_ce_statements;
1843 END IF;
1844 --
1845 -- bug# 924584
1846 --
1847 OPEN C_PURGE_HEADERS;
1848 FETCH C_PURGE_HEADERS INTO X_header_id;
1849 LOOP
1850 EXIT WHEN c_purge_headers%NOTFOUND;
1851
1852 DELETE FROM ce_statement_headers
1853 WHERE statement_header_id = X_header_id;
1854 purge_headers := purge_headers + SQL%ROWCOUNT;
1855
1856 OPEN C_PURGE_LINES;
1857 FETCH C_PURGE_LINES INTO X_line_id;
1858 LOOP
1859 EXIT WHEN c_purge_lines%NOTFOUND;
1860
1861 DELETE FROM ce_statement_lines
1862 WHERE statement_header_id = X_header_id;
1863 purge_lines := purge_lines + SQL%ROWCOUNT;
1864
1865 --DELETE FROM ce_statement_reconciliations
1866 DELETE FROM ce_statement_reconcils_all
1867 WHERE statement_line_id = X_line_id
1868 and (org_id in (select organization_id from ce_security_profiles_gt) or
1869 legal_entity_id in (select organization_id from ce_security_profiles_gt) or
1870 REFERENCE_TYPE in ('JE_LINE', 'ROI_LINE','STATEMENT')
1871 );
1872
1873 purge_recons := purge_recons + SQL%ROWCOUNT;
1874
1875 DELETE FROM ce_reconciliation_errors
1876 WHERE statement_line_id = X_line_id;
1877 purge_recon_errors := purge_recon_errors + SQL%ROWCOUNT;
1878
1879 FETCH C_PURGE_LINES INTO X_line_id;
1880 END LOOP; -- End of Lines Cursor
1881 CLOSE C_PURGE_LINES;
1882
1883 FETCH C_PURGE_HEADERS INTO X_header_id;
1884 END LOOP; -- End of Headers Cursor
1885 CLOSE C_PURGE_HEADERS;
1886
1887 CE_PURGE.G_Purge_Stat_Lines := purge_lines;
1888 CE_PURGE.G_Purge_Stat_Headers := purge_headers;
1889 CE_PURGE.G_Purge_Stat_Rec := purge_recons;
1890 CE_PURGE.G_Purge_Stat_Errors := purge_recon_errors;
1891
1892 END IF; /* IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */
1893
1894 IF (G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN
1895
1896 IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH')THEN
1897 arch_ce_interface('N');
1898 END IF;
1899
1900 DELETE FROM ce_statement_lines_interface csl
1901 WHERE csl.statement_number ||'-'|| csl.bank_account_num IN
1902 ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
1903 FROM ce_statement_headers_int csh
1904 WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
1905 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1906 WHERE bb.branch_party_id = ba.bank_branch_id
1907 AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1908 AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1909 AND csh.bank_account_num IN (SELECT bank_account_num
1910 FROM ce_bank_accts_gt_v
1911 WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1912 AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1913 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1914 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1915 AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
1916 AND nvl(csh.intra_day_flag, 'N') = 'N');
1917
1918 CE_PURGE.G_Purge_Inf_Lines := SQL%ROWCOUNT;
1919
1920 DELETE FROM ce_statement_headers_int csh
1921 WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
1922 (SELECT bb.bank_branch_name
1923 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
1924 WHERE bb.branch_party_id = ba.bank_branch_id
1925 AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
1926 AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
1927 AND csh.bank_account_num IN
1928 (SELECT bank_account_num
1929 FROM ce_bank_accts_gt_v
1930 WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
1931 AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
1932 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
1933 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
1934 AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
1935 AND nvl(csh.intra_day_flag, 'N') = 'N';
1936
1937 CE_PURGE.G_Purge_Inf_Headers := SQL%ROWCOUNT;
1938
1939 END IF; /* IF (G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN */
1940
1941 END IF; -- CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','PREVIOUS')
1942
1943
1944 -------------------------------------------------
1945
1946 IF (CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','INTRADAY')) THEN
1947 IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN
1948
1949 IF( CE_PURGE.G_Archive_Purge_Option IN ('BOTH')) THEN
1950 arch_ce_intra_statements;
1951 END IF;
1952 --
1953 -- bug# 924584
1954 --
1955 OPEN C_PURGE_INTRA_HEADERS;
1956 FETCH C_PURGE_INTRA_HEADERS INTO X_header_id;
1957 purge_headers := 0;
1958 purge_lines := 0;
1959 LOOP
1960 EXIT WHEN c_purge_intra_headers%NOTFOUND;
1961 DELETE FROM ce_intra_stmt_headers
1962 WHERE statement_header_id = X_header_id;
1963 purge_headers := purge_headers + SQL%ROWCOUNT;
1964
1965 OPEN C_PURGE_INTRA_LINES;
1966 FETCH C_PURGE_INTRA_LINES INTO X_line_id;
1967 LOOP
1968 EXIT WHEN c_purge_intra_lines%NOTFOUND;
1969
1970 DELETE FROM ce_intra_stmt_lines
1971 WHERE statement_header_id = X_header_id;
1972 purge_lines := purge_lines + SQL%ROWCOUNT;
1973
1974 FETCH C_PURGE_INTRA_LINES INTO X_line_id;
1975 END LOOP;
1976 CLOSE C_PURGE_INTRA_LINES;
1977
1978 FETCH C_PURGE_INTRA_HEADERS INTO X_header_id;
1979 END LOOP;
1980 CLOSE C_PURGE_INTRA_HEADERS;
1981
1982 CE_PURGE.G_Purge_Stat_Lines2 := purge_lines;
1983 CE_PURGE.G_Purge_Stat_Headers2 := purge_headers;
1984
1985
1986 END IF; /*IF( CE_PURGE.G_OBJECTS IN ('BOTH', 'STATEMENT') ) THEN */
1987
1988
1989
1990 IF (CE_PURGE.G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN
1991 IF (CE_PURGE.G_Archive_Purge_Option = 'BOTH')THEN
1992 arch_ce_interface('Y');
1993 END IF;
1994
1995 DELETE FROM ce_statement_lines_interface csl
1996 WHERE csl.statement_number ||'-'|| csl.bank_account_num IN
1997 ( SELECT csh.statement_number ||'-'|| csh.bank_account_num
1998 FROM ce_statement_headers_int csh
1999 WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
2000 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2001 WHERE bb.branch_party_id = ba.bank_branch_id
2002 AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2003 AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2004 AND csh.bank_account_num IN
2005 (SELECT bank_account_num
2006 FROM ce_bank_accts_gt_v
2007 WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2008 AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2009 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2010 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2011 AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
2012 AND csh.intra_day_flag = 'Y');
2013
2014 CE_PURGE.G_Purge_Inf_Lines2 := SQL%ROWCOUNT;
2015
2016 DELETE FROM ce_statement_headers_int csh
2017 WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN
2018 (SELECT bb.bank_branch_name
2019 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2020 WHERE bb.branch_party_id = ba.bank_branch_id
2021 AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2022 AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2023 AND csh.bank_account_num IN
2024 (SELECT bank_account_num
2025 FROM ce_bank_accts_gt_v
2026 WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2027 AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2028 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2029 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2030 AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
2031 AND csh.intra_day_flag = 'Y';
2032
2033 CE_PURGE.G_Purge_Inf_Headers2 := SQL%ROWCOUNT;
2034
2035 END IF; --IF (CE_PURGE.G_OBJECTS IN ('BOTH', 'INTERFACE') ) THEN
2036
2037 END IF; -- CE_PURGE.G_STATEMENT_TYPE IN ('BOTH','INTRADAY')
2038
2039 END purge_process;
2040
2041 /* ---------------------------------------------------------------------
2042 | PRIVATE PROCEDURE |
2043 | restore_statements |
2044 | |
2045 | DESCRIPTION |
2046 | Restore the Bank Statements from Archive tables |
2047 | |
2048 | CALLED BY |
2049 | restore_stmts |
2050 | REQUIRES |
2051 | |
2052 | HISTORY |
2053 | 17-Jan-2011 Created Rtumati |
2054 --------------------------------------------------------------------- */
2055 PROCEDURE restore_statements
2056 IS
2057
2058 CURSOR C_restore_stmts is
2059 SELECT
2060 STATEMENT_COMPLETE_FLAG,
2061 --ORG_ID,
2062 DOC_SEQUENCE_ID,
2063 DOC_SEQUENCE_VALUE,
2064 STATEMENT_HEADER_ID,
2065 BANK_ACCOUNT_ID,
2066 STATEMENT_NUMBER,
2067 STATEMENT_DATE,
2068 AUTO_LOADED_FLAG,
2069 GL_DATE,
2070 CHECK_DIGITS,
2071 CONTROL_BEGIN_BALANCE,
2072 CONTROL_TOTAL_DR,
2073 CONTROL_TOTAL_CR,
2074 CONTROL_END_BALANCE,
2075 CASHFLOW_BALANCE,
2076 INT_CALC_BALANCE,
2077 ONE_DAY_FLOAT,
2078 TWO_DAY_FLOAT,
2079 CONTROL_DR_LINE_COUNT,
2080 CONTROL_CR_LINE_COUNT,
2081 CURRENCY_CODE,
2082 ATTRIBUTE_CATEGORY,
2083 ATTRIBUTE1,
2084 ATTRIBUTE2,
2085 ATTRIBUTE3,
2086 ATTRIBUTE4,
2087 ATTRIBUTE5,
2088 ATTRIBUTE6,
2089 ATTRIBUTE7,
2090 ATTRIBUTE8,
2091 ATTRIBUTE9,
2092 ATTRIBUTE10,
2093 ATTRIBUTE11,
2094 ATTRIBUTE12,
2095 ATTRIBUTE13,
2096 ATTRIBUTE14,
2097 ATTRIBUTE15,
2098 LAST_UPDATE_LOGIN,
2099 CREATED_BY,
2100 CREATION_DATE,
2101 LAST_UPDATED_BY,
2102 LAST_UPDATE_DATE
2103 FROM ce_arch_headers csh
2104 WHERE ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
2105 OR
2106 (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2107 AND (csh.bank_account_id in (SELECT aba.bank_account_id
2108 FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
2109 WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
2110 AND csh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, csh.statement_date)
2111 AND csh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, csh.statement_date)
2112 AND not exists
2113 (select 1 -- find all stmts that are not in ce_security_profiles_gt (cannot delete these stmts)
2114 from ce_arch_headers sh, ce_arch_lines sl , ce_arch_reconciliations_all sr
2115 where sh.statement_header_id = sl.statement_header_id
2116 and sl.statement_line_id= sr.statement_line_id
2117 and sh.statement_header_id= csh.statement_header_id
2118 and ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
2119 OR
2120 (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2121 AND (sh.bank_account_id in (SELECT aba.bank_account_id
2122 FROM ce_bank_accounts aba --CE_BANK_ACCTS_GT_V aba
2123 WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_branch_ID,aba.bank_branch_id)))))
2124 AND sh.statement_date >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, sh.statement_date)
2125 AND sh.statement_date <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, sh.statement_date)
2126 and sr.CURRENT_RECORD_FLAG = 'Y'
2127 and sr.STATUS_FLAG = 'M'
2128 and (sr.org_id is not null or sr.legal_entity_id is not null)
2129 and not exists
2130 (select 1 from ce_security_profiles_gt lbg
2131 where lbg.organization_id = sr.org_id or
2132 sr.legal_entity_id = lbg.organization_id ) )
2133 FOR update;
2134
2135 l_bank_account_name ce_bank_accounts.Bank_Account_Name%TYPE;
2136 l_bank_account_num CE_BANK_ACCOUNTS.BANK_ACCOUNT_NUM%TYPE;
2137 l_currency_code CE_BANK_ACCOUNTS.currency_code%TYPE;
2138 l_No_data_flag VARCHAR2(1):='Y';
2139
2140 BEGIN
2141
2142 cep_standard.debug('>>CE_PURGE.restore_statements ');
2143
2144 put_starttag('Statement');
2145
2146 FOR r_stmt IN C_restore_stmts LOOP
2147 cep_standard.debug('>>CE_PURGE.restore_statements C_restore_stmts: '||G_Archive_Stat_Headers);
2148 G_Archive_Stat_Lines:=0;
2149 G_Archive_Stat_Rec:=0;
2150 G_Archive_Stat_Errors:=0;
2151
2152 BEGIN
2153
2154 l_No_data_flag :='N';
2155
2156 SELECT Bank_Account_Name,BANK_ACCOUNT_NUM, currency_code
2157 INTO l_bank_account_name, l_bank_account_num , l_currency_code
2158 FROM ce_bank_accounts
2159 WHERE bank_account_id = r_stmt.bank_account_id;
2160
2161 INSERT INTO ce_statement_headers (
2162 STATEMENT_COMPLETE_FLAG,
2163 --ORG_ID,
2164 DOC_SEQUENCE_ID,
2165 DOC_SEQUENCE_VALUE,
2166 STATEMENT_HEADER_ID,
2167 BANK_ACCOUNT_ID,
2168 STATEMENT_NUMBER,
2169 STATEMENT_DATE,
2170 AUTO_LOADED_FLAG,
2171 GL_DATE,
2172 CHECK_DIGITS,
2173 CONTROL_BEGIN_BALANCE,
2174 CONTROL_TOTAL_DR,
2175 CONTROL_TOTAL_CR,
2176 CONTROL_END_BALANCE,
2177 CASHFLOW_BALANCE,
2178 INT_CALC_BALANCE,
2179 ONE_DAY_FLOAT,
2180 TWO_DAY_FLOAT,
2181 CONTROL_DR_LINE_COUNT,
2182 CONTROL_CR_LINE_COUNT,
2183 CURRENCY_CODE,
2184 ATTRIBUTE_CATEGORY,
2185 ATTRIBUTE1,
2186 ATTRIBUTE2,
2187 ATTRIBUTE3,
2188 ATTRIBUTE4,
2189 ATTRIBUTE5,
2190 ATTRIBUTE6,
2191 ATTRIBUTE7,
2192 ATTRIBUTE8,
2193 ATTRIBUTE9,
2194 ATTRIBUTE10,
2195 ATTRIBUTE11,
2196 ATTRIBUTE12,
2197 ATTRIBUTE13,
2198 ATTRIBUTE14,
2199 ATTRIBUTE15,
2200 LAST_UPDATE_LOGIN,
2201 CREATED_BY,
2202 CREATION_DATE,
2203 LAST_UPDATED_BY,
2204 LAST_UPDATE_DATE)
2205 VALUES(r_stmt.STATEMENT_COMPLETE_FLAG,
2206 --r_stmt.ORG_ID,
2207 r_stmt.DOC_SEQUENCE_ID,
2208 r_stmt.DOC_SEQUENCE_VALUE,
2209 r_stmt.STATEMENT_HEADER_ID,
2210 r_stmt.BANK_ACCOUNT_ID,
2211 r_stmt.STATEMENT_NUMBER,
2212 r_stmt.STATEMENT_DATE,
2213 r_stmt.AUTO_LOADED_FLAG,
2214 r_stmt.GL_DATE,
2215 r_stmt.CHECK_DIGITS,
2216 r_stmt.CONTROL_BEGIN_BALANCE,
2217 r_stmt.CONTROL_TOTAL_DR,
2218 r_stmt.CONTROL_TOTAL_CR,
2219 r_stmt.CONTROL_END_BALANCE,
2220 r_stmt.CASHFLOW_BALANCE,
2221 r_stmt.INT_CALC_BALANCE,
2222 r_stmt.ONE_DAY_FLOAT,
2223 r_stmt.TWO_DAY_FLOAT,
2224 r_stmt.CONTROL_DR_LINE_COUNT,
2225 r_stmt.CONTROL_CR_LINE_COUNT,
2226 r_stmt.CURRENCY_CODE,
2227 r_stmt.ATTRIBUTE_CATEGORY,
2228 r_stmt.ATTRIBUTE1,
2229 r_stmt.ATTRIBUTE2,
2230 r_stmt.ATTRIBUTE3,
2231 r_stmt.ATTRIBUTE4,
2232 r_stmt.ATTRIBUTE5,
2233 r_stmt.ATTRIBUTE6,
2234 r_stmt.ATTRIBUTE7,
2235 r_stmt.ATTRIBUTE8,
2236 r_stmt.ATTRIBUTE9,
2237 r_stmt.ATTRIBUTE10,
2238 r_stmt.ATTRIBUTE11,
2239 r_stmt.ATTRIBUTE12,
2240 r_stmt.ATTRIBUTE13,
2241 r_stmt.ATTRIBUTE14,
2242 r_stmt.ATTRIBUTE15,
2243 r_stmt.LAST_UPDATE_LOGIN,
2244 r_stmt.CREATED_BY,
2245 r_stmt.CREATION_DATE,
2246 r_stmt.LAST_UPDATED_BY,
2247 r_stmt.LAST_UPDATE_DATE);
2248
2249 G_Archive_Stat_Headers := G_Archive_Stat_Headers + SQL%ROWCOUNT;
2250
2251 cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Headers: '||G_Archive_Stat_Headers);
2252
2253 INSERT INTO ce_statement_lines
2254 (STATEMENT_LINE_ID,
2255 STATEMENT_HEADER_ID,
2256 LINE_NUMBER,
2257 TRX_DATE,
2258 TRX_TYPE,
2259 AMOUNT,
2260 CHARGES_AMOUNT,
2261 STATUS,
2262 TRX_CODE_ID,
2263 EFFECTIVE_DATE,
2264 BANK_TRX_NUMBER,
2265 TRX_TEXT,
2266 CUSTOMER_TEXT,
2267 INVOICE_TEXT,
2268 CURRENCY_CODE,
2269 EXCHANGE_RATE_TYPE,
2270 EXCHANGE_RATE,
2271 EXCHANGE_RATE_DATE,
2272 ORIGINAL_AMOUNT,
2273 ATTRIBUTE_CATEGORY,
2274 ATTRIBUTE1,
2275 ATTRIBUTE2,
2276 ATTRIBUTE3,
2277 ATTRIBUTE4,
2278 ATTRIBUTE5,
2279 ATTRIBUTE6,
2280 ATTRIBUTE7,
2281 ATTRIBUTE8,
2282 ATTRIBUTE9,
2283 ATTRIBUTE10,
2284 ATTRIBUTE11,
2285 ATTRIBUTE12,
2286 ATTRIBUTE13,
2287 ATTRIBUTE14,
2288 ATTRIBUTE15,
2289 LAST_UPDATE_LOGIN,
2290 CREATED_BY,
2291 CREATION_DATE,
2292 LAST_UPDATED_BY,
2293 LAST_UPDATE_DATE,
2294 RECONCILE_TO_STATEMENT_FLAG)
2295 SELECT
2296 STATEMENT_LINE_ID,
2297 STATEMENT_HEADER_ID,
2298 LINE_NUMBER,
2299 TRX_DATE,
2300 TRX_TYPE,
2301 AMOUNT,
2302 CHARGES_AMOUNT,
2303 STATUS,
2304 TRX_CODE_ID,
2305 EFFECTIVE_DATE,
2306 BANK_TRX_NUMBER,
2307 TRX_TEXT,
2308 CUSTOMER_TEXT,
2309 INVOICE_TEXT,
2310 CURRENCY_CODE,
2311 EXCHANGE_RATE_TYPE,
2312 EXCHANGE_RATE,
2313 EXCHANGE_RATE_DATE,
2314 ORIGINAL_AMOUNT,
2315 ATTRIBUTE_CATEGORY,
2316 ATTRIBUTE1,
2317 ATTRIBUTE2,
2318 ATTRIBUTE3,
2319 ATTRIBUTE4,
2320 ATTRIBUTE5,
2321 ATTRIBUTE6,
2322 ATTRIBUTE7,
2323 ATTRIBUTE8,
2324 ATTRIBUTE9,
2325 ATTRIBUTE10,
2326 ATTRIBUTE11,
2327 ATTRIBUTE12,
2328 ATTRIBUTE13,
2329 ATTRIBUTE14,
2330 ATTRIBUTE15,
2331 LAST_UPDATE_LOGIN,
2332 CREATED_BY,
2333 CREATION_DATE,
2334 LAST_UPDATED_BY,
2335 LAST_UPDATE_DATE,
2336 RECONCILE_TO_STATEMENT_FLAG
2337 FROM ce_arch_lines
2338 WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID;
2339
2340
2341 G_Archive_Stat_Lines := SQL%ROWCOUNT;
2342
2343 cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Lines: '||G_Archive_Stat_Lines);
2344
2345 INSERT INTO ce_statement_reconcils_all
2346 (STATEMENT_LINE_ID,
2347 REFERENCE_TYPE,
2348 REFERENCE_ID,
2349 JE_HEADER_ID,
2350 ORG_ID,
2351 LEGAL_ENTITY_ID,
2352 REFERENCE_STATUS,
2353 STATUS_FLAG,
2354 ACTION_FLAG,
2355 CURRENT_RECORD_FLAG,
2356 AUTO_RECONCILED_FLAG,
2357 CREATED_BY,
2358 CREATION_DATE,
2359 LAST_UPDATED_BY,
2360 LAST_UPDATE_DATE,
2361 REQUEST_ID,
2362 PROGRAM_APPLICATION_ID,
2363 PROGRAM_ID,
2364 PROGRAM_UPDATE_DATE,
2365 AMOUNT)
2366 SELECT
2367 STATEMENT_LINE_ID,
2368 REFERENCE_TYPE,
2369 REFERENCE_ID,
2370 JE_HEADER_ID,
2371 ORG_ID,
2372 LEGAL_ENTITY_ID,
2373 REFERENCE_STATUS,
2374 STATUS_FLAG,
2375 ACTION_FLAG,
2376 CURRENT_RECORD_FLAG,
2377 AUTO_RECONCILED_FLAG,
2378 CREATED_BY,
2379 CREATION_DATE,
2380 LAST_UPDATED_BY,
2381 LAST_UPDATE_DATE,
2382 REQUEST_ID,
2383 PROGRAM_APPLICATION_ID,
2384 PROGRAM_ID,
2385 PROGRAM_UPDATE_DATE,
2386 AMOUNT
2387 FROM ce_arch_reconciliations_all --ce_statement_reconciliations
2388 WHERE statement_line_id IN
2389 ( SELECT statement_line_id
2390 FROM ce_arch_lines
2391 WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID );
2392
2393 G_Archive_Stat_Rec := SQL%ROWCOUNT;
2394
2395 cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Rec: '||G_Archive_Stat_Rec);
2396
2397 INSERT INTO ce_reconciliation_errors
2398 (STATEMENT_LINE_ID,
2399 MESSAGE_NAME,
2400 CREATION_DATE,
2401 CREATED_BY,
2402 APPLICATION_SHORT_NAME,
2403 STATEMENT_HEADER_ID)
2404 SELECT
2405 STATEMENT_LINE_ID,
2406 MESSAGE_NAME,
2407 CREATION_DATE,
2408 CREATED_BY,
2409 APPLICATION_SHORT_NAME,
2410 STATEMENT_HEADER_ID
2411 FROM ce_arch_recon_errors
2412 WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID;
2413
2414 G_Archive_Stat_Errors := SQL%ROWCOUNT;
2415
2416 cep_standard.debug('>>CE_PURGE.restore_statements G_Archive_Stat_Errors: '||G_Archive_Stat_Errors);
2417
2418 put_starttag('StmtRestored') ;
2419 put_element('BANK_ACCOUNT_NUM',l_bank_account_num);
2420 put_element('BANK_ACCOUNT_NAME',l_bank_account_name);
2421 put_element('CURRENCY',l_currency_code);
2422 put_element('STATEMENT_NUMBER',r_stmt.STATEMENT_NUMBER);
2423 put_element('STATEMENT_DATE',r_stmt.STATEMENT_DATE);
2424 put_element('LINE_COUNT', Nvl(G_Archive_Stat_Lines,0));
2425 put_element('REC_COUNT', Nvl(G_Archive_Stat_Rec,0));
2426 put_element('ERROR_COUNT', Nvl(G_Archive_Stat_Errors,0));
2427 put_endtag('StmtRestored') ;
2428
2429 /*Delete Restored Statements*/
2430
2431 DELETE FROM ce_arch_reconciliations_all --ce_statement_reconciliations
2432 WHERE statement_line_id IN
2433 ( SELECT statement_line_id
2434 FROM ce_arch_lines
2435 WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID );
2436
2437 DELETE FROM ce_arch_recon_errors
2438 WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID;
2439
2440 DELETE FROM ce_arch_lines
2441 WHERE statement_header_id = r_stmt.STATEMENT_HEADER_ID;
2442
2443 DELETE FROM ce_arch_headers
2444 WHERE CURRENT OF C_restore_stmts;
2445
2446 EXCEPTION
2447 WHEN DUP_VAL_ON_INDEX THEN
2448 put_starttag('StmtErrors');
2449 put_element('BANK_ACCOUNT_NUM',l_bank_account_num);
2450 put_element('BANK_ACCOUNT_NAME',l_bank_account_name);
2451 put_element('CURRENCY',l_currency_code);
2452 put_element('STATEMENT_NUMBER',r_stmt.STATEMENT_NUMBER);
2453 put_element('STATEMENT_DATE',r_stmt.STATEMENT_DATE);
2454 put_element('ERROR_MSG',fnd_message.get_string('CE','CE_PREV_IMPORT'));
2455 put_endtag('StmtErrors');
2456 cep_standard.debug('Restore Statements >>DUP_VAL_ON_INDEX: '||r_stmt.BANK_ACCOUNT_ID||', '||r_stmt.STATEMENT_NUMBER);
2457 G_Archive_Stat_Headers := G_Archive_Stat_Headers + 1;
2458 END;
2459 END LOOP;
2460
2461 IF( l_No_data_flag = 'Y') THEN
2462 put_element('NO_DATA_STMT_PREV','Y');
2463 end IF;
2464
2465 put_endtag('Statement');
2466
2467 cep_standard.debug('G_Archive_Stat_Headers : '||G_Archive_Stat_Headers);
2468
2469 END restore_statements;
2470
2471 /* ---------------------------------------------------------------------
2472 | PUBLIC PROCEDURE |
2473 | restore_intraday_statements |
2474 | |
2475 | DESCRIPTION |
2476 | Restore the Intra Day Statements from Archive tables |
2477 | |
2478 | CALLED BY |
2479 | restore_stmts |
2480 | REQUIRES |
2481 | |
2482 | HISTORY |
2483 | 17-Jan-2011 Created Rtumati |
2484 --------------------------------------------------------------------- */
2485 PROCEDURE restore_intraday_statements
2486 IS
2487
2488 CURSOR C_Restore_Intra_Stmts IS
2489 SELECT
2490 STATEMENT_COMPLETE_FLAG,
2491 --ORG_ID,
2492 DOC_SEQUENCE_ID,
2493 DOC_SEQUENCE_VALUE,
2494 STATEMENT_HEADER_ID,
2495 BANK_ACCOUNT_ID,
2496 STATEMENT_NUMBER,
2497 STATEMENT_DATE,
2498 AUTO_LOADED_FLAG,
2499 GL_DATE,
2500 CHECK_DIGITS,
2501 CONTROL_BEGIN_BALANCE,
2502 CONTROL_TOTAL_DR,
2503 CONTROL_TOTAL_CR,
2504 CONTROL_END_BALANCE,
2505 CASHFLOW_BALANCE,
2506 INT_CALC_BALANCE,
2507 ONE_DAY_FLOAT,
2508 TWO_DAY_FLOAT,
2509 CONTROL_DR_LINE_COUNT,
2510 CONTROL_CR_LINE_COUNT,
2511 CURRENCY_CODE,
2512 ATTRIBUTE_CATEGORY,
2513 ATTRIBUTE1,
2514 ATTRIBUTE2,
2515 ATTRIBUTE3,
2516 ATTRIBUTE4,
2517 ATTRIBUTE5,
2518 ATTRIBUTE6,
2519 ATTRIBUTE7,
2520 ATTRIBUTE8,
2521 ATTRIBUTE9,
2522 ATTRIBUTE10,
2523 ATTRIBUTE11,
2524 ATTRIBUTE12,
2525 ATTRIBUTE13,
2526 ATTRIBUTE14,
2527 ATTRIBUTE15,
2528 LAST_UPDATE_LOGIN,
2529 CREATED_BY,
2530 CREATION_DATE,
2531 LAST_UPDATED_BY,
2532 LAST_UPDATE_DATE
2533 FROM ce_arch_intra_headers csh
2534 WHERE ( csh.bank_account_id = Nvl(CE_PURGE.G_BANK_ACCOUNT_ID,-1)
2535 OR
2536 (CE_PURGE.G_BANK_ACCOUNT_ID IS NULL
2537 AND csh.bank_account_id IN (SELECT aba.bank_account_id
2538 FROM ce_BANK_ACCOUNTS aba
2539 WHERE aba.bank_branch_id = Nvl(CE_PURGE.G_BANK_BRANCH_ID,aba.bank_branch_id))))
2540 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2541 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2542 FOR update;
2543
2544 l_bank_account_name ce_bank_accounts.Bank_Account_Name%TYPE;
2545 l_bank_account_num CE_BANK_ACCOUNTS.BANK_ACCOUNT_NUM%TYPE;
2546 l_currency_code CE_BANK_ACCOUNTS.currency_code%TYPE;
2547 l_No_data_flag VARCHAR2(1):='Y';
2548 BEGIN
2549
2550 IF l_DEBUG in ('Y', 'C') THEN
2551 cep_standard.debug('>> restore_intraday_statements');
2552 END IF;
2553
2554
2555 Put_starttag('IntraStatements');
2556
2557 FOR r_intraStmt IN C_Restore_Intra_Stmts
2558 LOOP
2559
2560 BEGIN
2561 l_No_data_flag := 'N';
2562
2563 SELECT Bank_Account_Name,BANK_ACCOUNT_NUM, currency_code
2564 INTO l_bank_account_name, l_bank_account_num , l_currency_code
2565 FROM ce_bank_accounts
2566 WHERE bank_account_id = r_intraStmt.bank_account_id;
2567
2568 INSERT INTO ce_intra_stmt_headers (
2569 STATEMENT_COMPLETE_FLAG,
2570 --ORG_ID,
2571 DOC_SEQUENCE_ID,
2572 DOC_SEQUENCE_VALUE,
2573 STATEMENT_HEADER_ID,
2574 BANK_ACCOUNT_ID,
2575 STATEMENT_NUMBER,
2576 STATEMENT_DATE,
2577 AUTO_LOADED_FLAG,
2578 GL_DATE,
2579 CHECK_DIGITS,
2580 CONTROL_BEGIN_BALANCE,
2581 CONTROL_TOTAL_DR,
2582 CONTROL_TOTAL_CR,
2583 CONTROL_END_BALANCE,
2584 CASHFLOW_BALANCE,
2585 INT_CALC_BALANCE,
2586 ONE_DAY_FLOAT,
2587 TWO_DAY_FLOAT,
2588 CONTROL_DR_LINE_COUNT,
2589 CONTROL_CR_LINE_COUNT,
2590 CURRENCY_CODE,
2591 ATTRIBUTE_CATEGORY,
2592 ATTRIBUTE1,
2593 ATTRIBUTE2,
2594 ATTRIBUTE3,
2595 ATTRIBUTE4,
2596 ATTRIBUTE5,
2597 ATTRIBUTE6,
2598 ATTRIBUTE7,
2599 ATTRIBUTE8,
2600 ATTRIBUTE9,
2601 ATTRIBUTE10,
2602 ATTRIBUTE11,
2603 ATTRIBUTE12,
2604 ATTRIBUTE13,
2605 ATTRIBUTE14,
2606 ATTRIBUTE15,
2607 LAST_UPDATE_LOGIN,
2608 CREATED_BY,
2609 CREATION_DATE,
2610 LAST_UPDATED_BY,
2611 LAST_UPDATE_DATE)
2612 VALUES
2613 (r_intraStmt.STATEMENT_COMPLETE_FLAG,
2614 r_intraStmt.DOC_SEQUENCE_ID,
2615 r_intraStmt.DOC_SEQUENCE_VALUE,
2616 r_intraStmt.STATEMENT_HEADER_ID,
2617 r_intraStmt.BANK_ACCOUNT_ID,
2618 r_intraStmt.STATEMENT_NUMBER,
2619 r_intraStmt.STATEMENT_DATE,
2620 r_intraStmt.AUTO_LOADED_FLAG,
2621 r_intraStmt.GL_DATE,
2622 r_intraStmt.CHECK_DIGITS,
2623 r_intraStmt.CONTROL_BEGIN_BALANCE,
2624 r_intraStmt.CONTROL_TOTAL_DR,
2625 r_intraStmt.CONTROL_TOTAL_CR,
2626 r_intraStmt.CONTROL_END_BALANCE,
2627 r_intraStmt.CASHFLOW_BALANCE,
2628 r_intraStmt.INT_CALC_BALANCE,
2629 r_intraStmt.ONE_DAY_FLOAT,
2630 r_intraStmt.TWO_DAY_FLOAT,
2631 r_intraStmt.CONTROL_DR_LINE_COUNT,
2632 r_intraStmt.CONTROL_CR_LINE_COUNT,
2633 r_intraStmt.CURRENCY_CODE,
2634 r_intraStmt.ATTRIBUTE_CATEGORY,
2635 r_intraStmt.ATTRIBUTE1,
2636 r_intraStmt.ATTRIBUTE2,
2637 r_intraStmt.ATTRIBUTE3,
2638 r_intraStmt.ATTRIBUTE4,
2639 r_intraStmt.ATTRIBUTE5,
2640 r_intraStmt.ATTRIBUTE6,
2641 r_intraStmt.ATTRIBUTE7,
2642 r_intraStmt.ATTRIBUTE8,
2643 r_intraStmt.ATTRIBUTE9,
2644 r_intraStmt.ATTRIBUTE10,
2645 r_intraStmt.ATTRIBUTE11,
2646 r_intraStmt.ATTRIBUTE12,
2647 r_intraStmt.ATTRIBUTE13,
2648 r_intraStmt.ATTRIBUTE14,
2649 r_intraStmt.ATTRIBUTE15,
2650 r_intraStmt.LAST_UPDATE_LOGIN,
2651 r_intraStmt.CREATED_BY,
2652 r_intraStmt.CREATION_DATE,
2653 r_intraStmt.LAST_UPDATED_BY,
2654 r_intraStmt.LAST_UPDATE_DATE);
2655
2656 G_Archive_Stat_Headers2 :=G_Archive_Stat_Headers2+ SQL%ROWCOUNT;
2657
2658
2659 IF l_DEBUG in ('Y', 'C') THEN
2660 cep_standard.debug('>> G_Archive_Stat_Headers2: '||G_Archive_Stat_Headers2);
2661 END IF;
2662
2663 INSERT INTO ce_intra_stmt_lines
2664 (STATEMENT_LINE_ID,
2665 STATEMENT_HEADER_ID,
2666 LINE_NUMBER,
2667 TRX_DATE,
2668 TRX_TYPE,
2669 AMOUNT,
2670 CHARGES_AMOUNT,
2671 STATUS,
2672 TRX_CODE_ID,
2673 EFFECTIVE_DATE,
2674 BANK_TRX_NUMBER,
2675 TRX_TEXT,
2676 CUSTOMER_TEXT,
2677 INVOICE_TEXT,
2678 CURRENCY_CODE,
2679 EXCHANGE_RATE_TYPE,
2680 EXCHANGE_RATE,
2681 EXCHANGE_RATE_DATE,
2682 ORIGINAL_AMOUNT,
2683 ATTRIBUTE_CATEGORY,
2684 ATTRIBUTE1,
2685 ATTRIBUTE2,
2686 ATTRIBUTE3,
2687 ATTRIBUTE4,
2688 ATTRIBUTE5,
2689 ATTRIBUTE6,
2690 ATTRIBUTE7,
2691 ATTRIBUTE8,
2692 ATTRIBUTE9,
2693 ATTRIBUTE10,
2694 ATTRIBUTE11,
2695 ATTRIBUTE12,
2696 ATTRIBUTE13,
2697 ATTRIBUTE14,
2698 ATTRIBUTE15,
2699 LAST_UPDATE_LOGIN,
2700 CREATED_BY,
2701 CREATION_DATE,
2702 LAST_UPDATED_BY,
2703 LAST_UPDATE_DATE,
2704 RECONCILE_TO_STATEMENT_FLAG)
2705 SELECT
2706 STATEMENT_LINE_ID,
2707 STATEMENT_HEADER_ID,
2708 LINE_NUMBER,
2709 TRX_DATE,
2710 TRX_TYPE,
2711 AMOUNT,
2712 CHARGES_AMOUNT,
2713 STATUS,
2714 TRX_CODE_ID,
2715 EFFECTIVE_DATE,
2716 BANK_TRX_NUMBER,
2717 TRX_TEXT,
2718 CUSTOMER_TEXT,
2719 INVOICE_TEXT,
2720 CURRENCY_CODE,
2721 EXCHANGE_RATE_TYPE,
2722 EXCHANGE_RATE,
2723 EXCHANGE_RATE_DATE,
2724 ORIGINAL_AMOUNT,
2725 ATTRIBUTE_CATEGORY,
2726 ATTRIBUTE1,
2727 ATTRIBUTE2,
2728 ATTRIBUTE3,
2729 ATTRIBUTE4,
2730 ATTRIBUTE5,
2731 ATTRIBUTE6,
2732 ATTRIBUTE7,
2733 ATTRIBUTE8,
2734 ATTRIBUTE9,
2735 ATTRIBUTE10,
2736 ATTRIBUTE11,
2737 ATTRIBUTE12,
2738 ATTRIBUTE13,
2739 ATTRIBUTE14,
2740 ATTRIBUTE15,
2741 LAST_UPDATE_LOGIN,
2742 CREATED_BY,
2743 CREATION_DATE,
2744 LAST_UPDATED_BY,
2745 LAST_UPDATE_DATE,
2746 RECONCILE_TO_STATEMENT_FLAG
2747 FROM ce_arch_intra_lines
2748 WHERE statement_header_id = r_intraStmt.statement_header_id;
2749
2750 G_Archive_Stat_Lines2 := SQL%ROWCOUNT;
2751
2752
2753
2754 put_starttag('IntraStmtRestored');
2755 put_Element('BANK_ACCOUNT_NUM',l_BANK_ACCOUNT_NUM);
2756 put_Element('BANK_ACCOUNT_NAME',l_BANK_ACCOUNT_NAME);
2757 put_element('CURRENCY',l_CURRENCY_CODE);
2758 put_Element('STATEMENT_NUMBER',r_intraStmt.STATEMENT_NUMBER);
2759 put_Element('STATEMENT_DATE',r_intraStmt.STATEMENT_DATE);
2760 put_Element('INTRA_STMT_LINES',Nvl(G_Archive_Stat_Lines2,0));
2761 put_endtag('IntraStmtRestored');
2762
2763 /* Delete Restored Lines */
2764 DELETE FROM ce_arch_intra_lines
2765 WHERE statement_header_id = r_intraStmt.statement_header_id;
2766
2767 DELETE FROM ce_arch_intra_headers
2768 WHERE CURRENT OF C_Restore_Intra_Stmts;
2769
2770 EXCEPTION
2771 WHEN Dup_Val_On_Index THEN
2772 put_starttag('IntraStmtErrors');
2773 put_Element('BANK_ACCOUNT_NUM',l_BANK_ACCOUNT_NUM);
2774 put_Element('BANK_ACCOUNT_NAME',l_BANK_ACCOUNT_NAME);
2775 put_element('CURRENCY',l_CURRENCY_CODE);
2776 put_Element('STATEMENT_NUMBER',r_intraStmt.STATEMENT_NUMBER);
2777 put_Element('STATEMENT_DATE',r_intraStmt.STATEMENT_DATE);
2778 put_element('ERROR_MSG',fnd_message.get_string('CE','CE_PREV_IMPORT'));
2779 put_endtag('IntraStmtErrors');
2780 END;
2781
2782 END LOOP;
2783
2784 IF( l_No_data_flag = 'Y') THEN
2785 put_element('NO_DATA_STMT_INTRA','Y');
2786 end IF;
2787
2788 put_endtag('IntraStatements');
2789
2790 IF l_DEBUG in ('Y', 'C') THEN
2791 cep_standard.debug('>> G_Archive_Stat_Lines2: '||G_Archive_Stat_Lines2);
2792 cep_standard.debug('<<restore_intraday_statements');
2793 END IF;
2794
2795
2796 END restore_intraday_statements;
2797
2798 /* ---------------------------------------------------------------------
2799 | PUBLIC PROCEDURE |
2800 | restore_interface |
2801 | |
2802 | DESCRIPTION |
2803 | Restore the Interface Statements from Archive tables |
2804 | |
2805 | CALLED BY |
2806 | restore_stmts |
2807 | REQUIRES |
2808 | |
2809 | HISTORY |
2810 | 29-Dec-2010 Created Rtumati |
2811 --------------------------------------------------------------------- */
2812 PROCEDURE restore_interface(p_intra_day_flag varchar2)
2813 IS
2814
2815 CURSOR C_Restore_interface(p_intra_day_flag varchar2)
2816 IS
2817 SELECT
2818 STATEMENT_NUMBER,
2819 BANK_ACCOUNT_NUM,
2820 STATEMENT_DATE,
2821 BANK_NAME,
2822 BANK_BRANCH_NAME,
2823 CHECK_DIGITS,
2824 CONTROL_BEGIN_BALANCE,
2825 CONTROL_TOTAL_DR,
2826 CONTROL_TOTAL_CR,
2827 CONTROL_END_BALANCE,
2828 CASHFLOW_BALANCE,
2829 INT_CALC_BALANCE,
2830 ONE_DAY_FLOAT,
2831 TWO_DAY_FLOAT,
2832 CONTROL_DR_LINE_COUNT,
2833 CONTROL_CR_LINE_COUNT,
2834 CONTROL_LINE_COUNT,
2835 RECORD_STATUS_FLAG,
2836 CURRENCY_CODE,
2837 ATTRIBUTE_CATEGORY,
2838 ATTRIBUTE1,
2839 ATTRIBUTE2,
2840 ATTRIBUTE3,
2841 ATTRIBUTE4,
2842 ATTRIBUTE5,
2843 ATTRIBUTE6,
2844 ATTRIBUTE7,
2845 ATTRIBUTE8,
2846 ATTRIBUTE9,
2847 ATTRIBUTE10,
2848 ATTRIBUTE11,
2849 ATTRIBUTE12,
2850 ATTRIBUTE13,
2851 ATTRIBUTE14,
2852 ATTRIBUTE15,
2853 CREATED_BY,
2854 CREATION_DATE,
2855 LAST_UPDATED_BY,
2856 LAST_UPDATE_DATE,
2857 intra_day_flag,
2858 ORG_ID
2859 FROM ce_arch_interface_headers csh
2860 WHERE NVL(csh.bank_branch_name,CE_PURGE.G_BANK_BRANCH_NAME) IN (SELECT bb.bank_branch_name
2861 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
2862 WHERE bb.branch_party_id = ba.bank_branch_id
2863 AND ba.bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,ba.bank_account_id)
2864 AND bb.branch_party_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bb.branch_party_id))
2865 AND csh.bank_account_num IN (SELECT bank_account_num
2866 FROM ce_bank_accts_gt_v
2867 WHERE bank_account_id = NVL(CE_PURGE.G_BANK_ACCOUNT_ID,bank_account_id)
2868 AND bank_branch_id = NVL(CE_PURGE.G_BANK_BRANCH_ID, bank_branch_id))
2869 AND trunc(csh.statement_date) >= NVL(CE_PURGE.G_STATEMENT_DATE_FROM, trunc(csh.statement_date))
2870 AND trunc(csh.statement_date) <= NVL(CE_PURGE.G_STATEMENT_DATE_TO, trunc(csh.statement_date))
2871 AND csh.record_status_flag in (decode (CE_PURGE.G_HDR_INT_STATUS,'C','C','E','E','N','N','T','T','A',record_status_flag,'T'))
2872 AND nvl(csh.intra_day_flag, 'N') = p_intra_day_flag
2873 FOR update;
2874
2875 l_bank_account_name VARCHAR2(500);
2876 l_No_data_flag VARCHAR2(1):='Y';
2877 BEGIN
2878
2879 IF l_DEBUG in ('Y', 'C') THEN
2880 cep_standard.debug('>>CE_PURGE.restore_interface '||p_intra_day_flag);
2881 END IF;
2882
2883 IF ( p_intra_day_flag = 'Y' ) THEN
2884 put_starttag('INTERFACE_INTRADAY');
2885 G_Archive_Inf_Headers2:=0;
2886 ELSE
2887 put_starttag('INTERFACE_PREVIOUS');
2888 G_Archive_Inf_Headers:=0;
2889 END IF;
2890
2891 FOR r_interface IN C_Restore_interface(p_intra_day_flag)
2892 LOOP
2893
2894 BEGIN
2895
2896 l_No_data_flag := 'N';
2897
2898 IF ( p_intra_day_flag = 'Y' ) THEN
2899 G_Archive_Inf_Lines:=0;
2900 ELSE
2901 G_Archive_Inf_Lines2:=0;
2902 END IF;
2903
2904 BEGIN
2905 IF(G_bank_account_name IS NOT NULL) THEN
2906 l_bank_account_name :=G_bank_account_name;
2907 ELSE
2908
2909 SELECT Bank_Account_Name
2910 INTO l_bank_account_name
2911 FROM ce_bank_accounts ce
2912 WHERE ce.bank_account_num = r_interface.bank_account_num
2913 AND ce.bank_branch_id=(SELECT Branch_party_id
2914 FROM ce_bank_Branches_v
2915 WHERE bank_branch_name = Nvl(r_interface.BANK_BRANCH_NAME,G_BANK_BRANCH_NAME))
2916 AND ce.currency_code=r_interface.CURRENCY_CODE ;
2917 END IF;
2918
2919 EXCEPTION
2920 WHEN TOO_MANY_ROWS THEN
2921
2922 FND_MESSAGE.set_name('CE', 'CE_MULTIPLE_ACCNTS');
2923 l_bank_account_name:=fnd_message.get;
2924
2925 WHEN NO_DATA_FOUND THEN
2926 FND_MESSAGE.set_name('CE', 'CE_INVALID_BANK');
2927 l_bank_account_name:= fnd_message.get;
2928 END;
2929
2930
2931 INSERT INTO ce_statement_headers_int
2932 (STATEMENT_NUMBER,
2933 BANK_ACCOUNT_NUM,
2934 STATEMENT_DATE,
2935 BANK_NAME,
2936 BANK_BRANCH_NAME,
2937 CHECK_DIGITS,
2938 CONTROL_BEGIN_BALANCE,
2939 CONTROL_TOTAL_DR,
2940 CONTROL_TOTAL_CR,
2941 CONTROL_END_BALANCE,
2942 CASHFLOW_BALANCE,
2943 INT_CALC_BALANCE,
2944 ONE_DAY_FLOAT,
2945 TWO_DAY_FLOAT,
2946 CONTROL_DR_LINE_COUNT,
2947 CONTROL_CR_LINE_COUNT,
2948 CONTROL_LINE_COUNT,
2949 RECORD_STATUS_FLAG,
2950 CURRENCY_CODE,
2951 ATTRIBUTE_CATEGORY,
2952 ATTRIBUTE1,
2953 ATTRIBUTE2,
2954 ATTRIBUTE3,
2955 ATTRIBUTE4,
2956 ATTRIBUTE5,
2957 ATTRIBUTE6,
2958 ATTRIBUTE7,
2959 ATTRIBUTE8,
2960 ATTRIBUTE9,
2961 ATTRIBUTE10,
2962 ATTRIBUTE11,
2963 ATTRIBUTE12,
2964 ATTRIBUTE13,
2965 ATTRIBUTE14,
2966 ATTRIBUTE15,
2967 CREATED_BY,
2968 CREATION_DATE,
2969 LAST_UPDATED_BY,
2970 LAST_UPDATE_DATE,
2971 intra_day_flag)
2972 VALUES ( r_interface.STATEMENT_NUMBER,
2973 r_interface.BANK_ACCOUNT_NUM,
2974 r_interface.STATEMENT_DATE,
2975 r_interface.BANK_NAME,
2976 r_interface.BANK_BRANCH_NAME,
2977 r_interface.CHECK_DIGITS,
2978 r_interface.CONTROL_BEGIN_BALANCE,
2979 r_interface.CONTROL_TOTAL_DR,
2980 r_interface.CONTROL_TOTAL_CR,
2981 r_interface.CONTROL_END_BALANCE,
2982 r_interface.CASHFLOW_BALANCE,
2983 r_interface.INT_CALC_BALANCE,
2984 r_interface.ONE_DAY_FLOAT,
2985 r_interface.TWO_DAY_FLOAT,
2986 r_interface.CONTROL_DR_LINE_COUNT,
2987 r_interface.CONTROL_CR_LINE_COUNT,
2988 r_interface.CONTROL_LINE_COUNT,
2989 r_interface.RECORD_STATUS_FLAG,
2990 r_interface.CURRENCY_CODE,
2991 r_interface.ATTRIBUTE_CATEGORY,
2992 r_interface.ATTRIBUTE1,
2993 r_interface.ATTRIBUTE2,
2994 r_interface.ATTRIBUTE3,
2995 r_interface.ATTRIBUTE4,
2996 r_interface.ATTRIBUTE5,
2997 r_interface.ATTRIBUTE6,
2998 r_interface.ATTRIBUTE7,
2999 r_interface.ATTRIBUTE8,
3000 r_interface.ATTRIBUTE9,
3001 r_interface.ATTRIBUTE10,
3002 r_interface.ATTRIBUTE11,
3003 r_interface.ATTRIBUTE12,
3004 r_interface.ATTRIBUTE13,
3005 r_interface.ATTRIBUTE14,
3006 r_interface.ATTRIBUTE15,
3007 r_interface.CREATED_BY,
3008 r_interface.CREATION_DATE,
3009 r_interface.LAST_UPDATED_BY,
3010 r_interface.LAST_UPDATE_DATE,
3011 r_interface.intra_day_flag);
3012
3013
3014 IF( p_intra_day_flag = 'N' ) THEN
3015 G_Archive_Inf_Headers := G_Archive_Inf_Headers+SQL%ROWCOUNT;
3016 ELSE
3017 G_Archive_Inf_Headers2 := G_Archive_Inf_Headers2 + SQL%ROWCOUNT;
3018 END IF;
3019
3020
3021 INSERT INTO ce_statement_lines_interface
3022 (EXCHANGE_RATE_DATE,
3023 EXCHANGE_RATE,
3024 BANK_TRX_NUMBER,
3025 CUSTOMER_TEXT,
3026 CREATED_BY,
3027 CREATION_DATE,
3028 LAST_UPDATED_BY,
3029 LAST_UPDATE_DATE,
3030 ATTRIBUTE_CATEGORY,
3031 ATTRIBUTE1,
3032 ATTRIBUTE2,
3033 ATTRIBUTE3,
3034 ATTRIBUTE4,
3035 ATTRIBUTE5,
3036 ATTRIBUTE6,
3037 ATTRIBUTE7,
3038 ATTRIBUTE8,
3039 ATTRIBUTE9,
3040 ATTRIBUTE10,
3041 ATTRIBUTE11,
3042 ATTRIBUTE12,
3043 ATTRIBUTE13,
3044 ATTRIBUTE14,
3045 ATTRIBUTE15,
3046 ORIGINAL_AMOUNT,
3047 BANK_ACCOUNT_NUM,
3048 STATEMENT_NUMBER,
3049 LINE_NUMBER,
3050 TRX_DATE,
3051 TRX_CODE,
3052 EFFECTIVE_DATE,
3053 TRX_TEXT,
3054 INVOICE_TEXT,
3055 AMOUNT,
3056 CHARGES_AMOUNT,
3057 CURRENCY_CODE,
3058 USER_EXCHANGE_RATE_TYPE)
3059 SELECT
3060 EXCHANGE_RATE_DATE,
3061 EXCHANGE_RATE,
3062 BANK_TRX_NUMBER,
3063 CUSTOMER_TEXT,
3064 CREATED_BY,
3065 CREATION_DATE,
3066 LAST_UPDATED_BY,
3067 LAST_UPDATE_DATE,
3068 ATTRIBUTE_CATEGORY,
3069 ATTRIBUTE1,
3070 ATTRIBUTE2,
3071 ATTRIBUTE3,
3072 ATTRIBUTE4,
3073 ATTRIBUTE5,
3074 ATTRIBUTE6,
3075 ATTRIBUTE7,
3076 ATTRIBUTE8,
3077 ATTRIBUTE9,
3078 ATTRIBUTE10,
3079 ATTRIBUTE11,
3080 ATTRIBUTE12,
3081 ATTRIBUTE13,
3082 ATTRIBUTE14,
3083 ATTRIBUTE15,
3084 ORIGINAL_AMOUNT,
3085 BANK_ACCOUNT_NUM,
3086 STATEMENT_NUMBER,
3087 LINE_NUMBER,
3088 TRX_DATE,
3089 TRX_CODE,
3090 EFFECTIVE_DATE,
3091 TRX_TEXT,
3092 INVOICE_TEXT,
3093 AMOUNT,
3094 CHARGES_AMOUNT,
3095 CURRENCY_CODE,
3096 USER_EXCHANGE_RATE_TYPE
3097 FROM ce_arch_interface_lines csl
3098 WHERE csl.statement_number ||'-'|| csl.bank_account_num =
3099 r_interface.statement_number ||'-'|| r_interface.bank_account_num;
3100
3101 IF( p_intra_day_flag = 'N' ) THEN
3102 G_Archive_Inf_Lines := SQL%ROWCOUNT;
3103 put_starttag('PrevInfRestored');
3104 put_element('BANK_ACCOUNT_NUM',r_interface.bank_account_num);
3105 put_element('BANK_ACCOUNT_NAME',l_bank_account_name);
3106 put_element('CURRENCY',r_interface.CURRENCY_CODE);
3107 put_element('STATEMENT_NUMBER',r_interface.statement_number);
3108 put_element('STATEMENT_DATE',r_interface.statement_DATE);
3109 put_element('PREV_INF_LINES',Nvl(G_Archive_Inf_Lines,0));
3110 put_endtag('PrevInfRestored');
3111 IF l_DEBUG in ('Y', 'C') THEN
3112 cep_standard.debug('>> G_Archive_Inf_Lines: '||G_Archive_Inf_Lines);
3113 END IF;
3114 ELSE
3115 G_Archive_Inf_Lines2 := SQL%ROWCOUNT;
3116 put_starttag('IntraInfRestored');
3117 put_element('BANK_ACCOUNT_NUM',r_interface.bank_account_num);
3118 put_element('BANK_ACCOUNT_NAME',l_bank_account_name);
3119 put_element('CURRENCY',r_interface.CURRENCY_CODE);
3120 put_element('STATEMENT_NUMBER',r_interface.statement_number);
3121 put_element('STATEMENT_DATE',r_interface.statement_DATE);
3122 put_element('INTRA_INF_LINES',Nvl(G_Archive_Inf_Lines2,0));
3123 put_endtag('IntraInfRestored');
3124 IF l_DEBUG in ('Y', 'C') THEN
3125 cep_standard.debug('>> G_Archive_Inf_Lines2: '||G_Archive_Inf_Lines2);
3126 END IF;
3127 END IF;
3128
3129 DELETE FROM ce_arch_interface_headers
3130 WHERE CURRENT OF C_Restore_interface;
3131
3132 DELETE FROM ce_arch_interface_lines csl
3133 WHERE csl.statement_number ||'-'|| csl.bank_account_num =
3134 r_interface.statement_number ||'-'|| r_interface.bank_account_num;
3135
3136
3137 EXCEPTION
3138 WHEN Dup_Val_On_Index THEN
3139 IF l_DEBUG in ('Y', 'C') THEN
3140 cep_standard.debug('<<CE_PURGE.restore_interface Dup_Val_On_Index EXCEPTION');
3141 END IF;
3142
3143 IF ( p_intra_day_flag = 'Y' ) THEN
3144 put_starttag('IntraInfErrors');
3145 put_element('BANK_ACCOUNT_NUM',r_interface.bank_account_num);
3146 put_element('BANK_ACCOUNT_NAME',l_bank_account_name);
3147 put_element('CURRENCY',r_interface.CURRENCY_CODE);
3148 put_element('STATEMENT_NUMBER',r_interface.statement_number);
3149 put_element('STATEMENT_DATE',r_interface.statement_DATE);
3150 put_element('ERROR_MSG',fnd_message.get_string('CE','CE_PREV_IMPORT'));
3151 put_endtag('IntraInfErrors');
3152 ELSE
3153 put_starttag('PrevInfErrors');
3154 put_element('BANK_ACCOUNT_NUM',r_interface.bank_account_num);
3155 put_element('BANK_ACCOUNT_NAME',l_bank_account_name);
3156 put_element('CURRENCY',r_interface.CURRENCY_CODE);
3157 put_element('STATEMENT_NUMBER',r_interface.statement_number);
3158 put_element('STATEMENT_DATE',r_interface.statement_DATE);
3159 put_element('ERROR_MSG',fnd_message.get_string('CE','CE_PREV_IMPORT'));
3160 put_endtag('PrevInfErrors');
3161
3162 END IF;
3163 END;
3164 END LOOP;
3165
3166 IF ( p_intra_day_flag = 'Y' ) THEN
3167 IF( l_No_data_flag = 'Y') THEN
3168 put_element('NO_DATA_INF_INTRA','Y');
3169 end IF;
3170 put_endtag('INTERFACE_INTRADAY');
3171 ELSE
3172 IF( l_No_data_flag = 'Y') THEN
3173 put_element('NO_DATA_INF_PREV','Y');
3174 end IF;
3175 put_endtag('INTERFACE_PREVIOUS');
3176 END IF;
3177
3178 IF l_DEBUG in ('Y', 'C') THEN
3179 cep_standard.debug('<<CE_PURGE.restore_interface');
3180 END IF;
3181 END restore_interface;
3182
3183 /* ---------------------------------------------------------------------
3184 | PUBLIC PROCEDURE |
3185 | restore_stmts |
3186 | |
3187 | DESCRIPTION |
3188 | Completes the Restore process |
3189 | |
3190 | CALLED BY |
3191 | CP Restore Bank Statements |
3192 | REQUIRES |
3193 | |
3194 | HISTORY |
3195 | 17-Jan-2011 Created Rtumati |
3196 --------------------------------------------------------------------- */
3197
3198
3199 PROCEDURE restore_process(errbuf OUT NOCOPY VARCHAR2,
3200 retcode OUT NOCOPY NUMBER,
3201 p_Objects IN VARCHAR2,
3202 p_Statement_type IN VARCHAR2,
3203 p_hdr_int_status IN VARCHAR2,
3204 p_BANK_BRANCH_ID IN ce_bank_accounts.BANK_branch_ID%TYPE,
3205 p_BANK_ACCOUNT_ID IN ce_bank_accounts.BANK_ACCOUNT_ID%TYPE,
3206 p_STATEMENT_DATE_FROM IN VARCHAR2,-- ce_statement_headers.statement_date%TYPE,
3207 p_STATEMENT_DATE_TO IN VARCHAR2, --ce_statement_headers.statement_date%TYPE,
3208 X_sql_trace IN VARCHAR2 ,
3209 X_debug_path IN VARCHAR2
3210 ) IS
3211 error_found BOOLEAN:= FALSE;
3212 l_dummy varchar2(200);
3213 l_bank_branch_id NUMBER;
3214 l_encoding VARCHAR2(20);
3215
3216 BEGIN
3217
3218 IF l_DEBUG in ('Y', 'C') THEN
3219 cep_standard.debug('>>CE_PURGE.restore_stmts');
3220 END IF;
3221
3222 set_arch_purge_counts;
3223 CEP_STANDARD.init_security;
3224
3225 --
3226 -- If the statement_date parameter is NULL, we default to sysdate
3227 --
3228 /* Init AOL - only necessary if AOL user exits are called */
3229 /* If this is deleted, also delete SRWEXIT in After Report Trigger! */
3230
3231 G_BANK_ACCOUNT_ID := p_BANK_ACCOUNT_ID;
3232 G_BANK_BRANCH_ID := p_BANK_BRANCH_ID;
3233 G_STATEMENT_DATE_FROM := to_date(p_STATEMENT_DATE_FROM,'YYYY/MM/DD HH24:MI:SS');
3234 G_STATEMENT_DATE_TO := to_date(p_STATEMENT_DATE_TO,'YYYY/MM/DD HH24:MI:SS');
3235 G_Objects := p_Objects;
3236 G_Statement_type := p_Statement_type;
3237 G_hdr_int_status := p_hdr_int_status;
3238 G_Archive_Purge_Option := 'Restore';
3239
3240 SELECT meaning INTO G_status
3241 FROM ce_lookups
3242 WHERE lookup_type = 'HEADER_INTERFACE_STATUS'
3243 AND LOOKUP_CODE = G_hdr_int_status;
3244
3245
3246 IF (G_STATEMENT_DATE_TO IS NULL) THEN
3247 G_STATEMENT_DATE_TO := sysdate;
3248 END IF;
3249
3250 IF (G_Statement_type IS NULL) THEN
3251 G_Statement_type := 'BOTH';
3252 END IF;
3253
3254 IF l_DEBUG in ('Y', 'C') THEN
3255 cep_standard.debug('>>CE_PURGE.restore_stmts G_Statement_type: '||G_Statement_type);
3256 cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_ACCOUNT_ID: '||G_BANK_ACCOUNT_ID);
3257 cep_standard.debug('>>CE_PURGE.restore_stmts G_BANK_BRANCH_ID: '||G_BANK_BRANCH_ID||' p_BANK_BRANCH_ID: '||p_BANK_BRANCH_ID);
3258 cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_FROM: '||G_STATEMENT_DATE_FROM);
3259 cep_standard.debug('>>CE_PURGE.restore_stmts G_STATEMENT_DATE_TO: '||G_STATEMENT_DATE_TO);
3260 cep_standard.debug('>>CE_PURGE.restore_stmts G_hdr_int_status: '||G_hdr_int_status);
3261 END IF;
3262
3263 IF (G_BANK_ACCOUNT_ID IS NOT NULL) THEN
3264 SELECT bb.bank_name,
3265 bb.bank_branch_name,
3266 ba.bank_account_name,
3267 ba.bank_account_num,
3268 ba.currency_code
3269 INTO G_Bank_Name,
3270 G_Bank_Branch_Name,
3271 G_Bank_Account_Name,
3272 G_Bank_Account_Num,
3273 G_Currency_Code
3274 FROM ce_bank_branches_v bb, ce_bank_accts_gt_v ba
3275 WHERE ba.bank_account_id = G_BANK_ACCOUNT_ID
3276 AND bb.branch_party_id = ba.bank_branch_id;
3277
3278
3279 ELSIF (G_BANK_BRANCH_ID IS NOT NULL) THEN
3280 SELECT bb.bank_name,
3281 bb.bank_branch_name
3282 INTO G_Bank_Name,
3283 G_Bank_Branch_Name
3284 FROM ce_bank_branches_v bb
3285 WHERE bb.branch_party_id = G_BANK_BRANCH_ID;
3286
3287 END IF;
3288 --
3289 -- Fetch the SOB info
3290 --
3291 BEGIN
3292 SELECT l.meaning
3293 INTO l_dummy
3294 FROM gl_sets_of_books gl,
3295 ce_system_parameters cb,
3296 ce_lookups l
3297 WHERE gl.set_of_books_id = cb.set_of_books_id AND
3298 l.lookup_type = 'LITERAL' AND
3299 l.lookup_code = 'ALL' and rownum = 1;
3300
3301 EXCEPTION
3302 WHEN NO_DATA_FOUND THEN
3303 FND_MESSAGE.set_name('CE','CE_PURGE_NO_SOB');
3304 error_found := TRUE;
3305 null;
3306 END;
3307
3308 IF (G_BANK_ACCOUNT_ID IS NOT NULL AND G_BANK_BRANCH_ID IS NOT NULL) THEN
3309 SELECT BANK_BRANCH_ID
3310 INTO l_bank_branch_id
3311 FROM CE_BANK_ACCTS_GT_V
3312 WHERE BANK_ACCOUNT_ID = G_BANK_ACCOUNT_ID;
3313
3314 IF (l_bank_branch_id <> G_BANK_BRANCH_ID) THEN
3315 FND_MESSAGE.set_name('CE','CE_PURGE_BRANCH_ACCOUNT');
3316 error_found := TRUE;
3317 END IF;
3318 END IF;
3319
3320 IF( NOT error_found ) THEN
3321
3322 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
3323 put_starttag('?xml version="1.0" encoding="'||l_encoding||'"?');
3324 put_starttag('RESTORE_REPORT');
3325 put_element('DateFrom',To_char(G_STATEMENT_DATE_FROM,'DD-Mon-YYYY'));
3326 put_element('DateTo',To_char(G_STATEMENT_DATE_TO,'DD-Mon-YYYY'));
3327 put_element('BankName',G_Bank_Name);
3328 put_element('BranchName' ,G_Bank_Branch_Name);
3329 put_element('BankAccountName',G_BANK_ACCOUNT_Name);
3330 put_element('CurrencyCode',G_Currency_Code);
3331 put_element('BankAccountNum',G_BANK_ACCOUNT_Num);
3332 put_element('Objects',InitCap(G_Objects));
3333 put_element('StatementType',InitCap(G_Statement_type));
3334 put_element('Status',G_status);
3335
3336
3337 IF( G_Objects IN ( 'BOTH','STATEMENT') ) THEN
3338 IF ( G_Statement_type IN ('BOTH','PREVIOUS') )THEN
3339 restore_statements;
3340 END IF;
3341 IF ( G_Statement_type IN ('BOTH','INTRADAY')) THEN
3342 restore_intraday_statements;
3343 END IF;
3344
3345 END IF; -- end of IF( G_Objects IN ( 'BOTH','STATEMENT') ) THEN
3346
3347 IF (G_Objects IN ( 'BOTH','INTERFACE') ) THEN
3348 IF ( G_Statement_type IN ('BOTH','PREVIOUS') ) THEN
3349 restore_interface('N');
3350 END IF;
3351 IF ( G_Statement_type IN ('BOTH','INTRADAY')) THEN
3352 restore_interface('Y');
3353 END IF;
3354
3355 END IF; -- end of IF (G_Objects IN ( 'BOTH','INTERFACE') ) THEN
3356
3357 put_endtag('RESTORE_REPORT');
3358 ELSE
3359 put_endtag('RESTORE_REPORT');
3360 ROLLBACK;
3361 END IF;
3362
3363 IF l_DEBUG in ('Y', 'C') THEN
3364 cep_standard.debug('<<CE_PURGE.restore_stmts');
3365 END IF;
3366
3367 EXCEPTION WHEN OTHERS THEN
3368 IF l_DEBUG in ('Y', 'C') THEN
3369 cep_standard.debug('<<CE_PURGE.restore_stmts Exception Occured');
3370 END IF;
3371
3372 put_endtag('RESTORE_REPORT');
3373 END restore_Process;
3374
3375 END CE_PURGE;