1 PACKAGE BODY OKL_PURGE_STRM_INTF_PVT AS
2 /* $Header: OKLRPSIB.pls 120.2.12010000.3 2009/06/03 04:21:12 racheruv ship $ */
3 PROCEDURE PURGE_INTERFACE_TABLES(
4 x_errbuf OUT NOCOPY VARCHAR2
5 ,x_retcode OUT NOCOPY NUMBER
6 ,p_end_date IN VARCHAR2) IS
7
8 l_end_date DATE := null;
9 l_sif_id NUMBER;
10 l_sir_id NUMBER;
11 l_api_name CONSTANT VARCHAR2(40) := 'PURGE_INTERFACE_TABLES';
12 l_api_version CONSTANT NUMBER := 1.0;
13 lp_api_version CONSTANT NUMBER := 1.0;
14 l_init_msg_list VARCHAR2(1) := 'F';
15 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
16 lx_return_status VARCHAR2(1);
17 lx_msg_count NUMBER;
18 lx_msg_data VARCHAR2(2000);
19
20 lp_sifv_rec sifv_rec_type;
21 lp_sitv_rec sitv_rec_type;
22 lp_sfev_rec sfev_rec_type;
23 lp_silv_rec silv_rec_type;
24 lp_sxpv_rec sxpv_rec_type;
25 lp_siyv_rec siyv_rec_type;
26 lp_sirv_rec sirv_rec_type;
27 lp_srsv_rec srsv_rec_type;
28 lp_srlv_rec srlv_rec_type;
29 lp_srmv_rec srmv_rec_type;
30
31 lx_sifv_rec sifv_rec_type;
32 lx_sitv_rec sitv_rec_type;
33 lx_sfev_rec sfev_rec_type;
34 lx_silv_rec silv_rec_type;
35 lx_sxpv_rec sxpv_rec_type;
36 lx_siyv_rec siyv_rec_type;
37 lx_sirv_rec sirv_rec_type;
38 lx_srsv_rec srsv_rec_type;
39 lx_srlv_rec srlv_rec_type;
40 lx_srmv_rec srmv_rec_type;
41
42 l_error_msg_rec error_message_type;
43 BEGIN
44 --EXECUTE IMMEDIATE ('ALTER SESSION FORCE PARALLEL QUERY'); -- commented out for test patch 5560534
45 -- EXECUTE IMMEDIATE ('ALTER SESSION ENABLE PARALLEL DML'); -- commented out for test patch 5560534
46
47 x_retcode := 0;
48 FND_FILE.Put_Line(FND_FILE.LOG,'Lease and Finance Management: Streams Interface Tables purge concurrent program');
49 --l_end_date := TO_DATE(p_end_date,'YYYY/MM/DD HH:MI:SS');
50 l_end_date := fnd_date.canonical_to_date(p_end_date);
51 FND_FILE.Put_Line(FND_FILE.LOG, 'Deleting records with creation date less then '||l_end_date);
52
53 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from OKL_SIF_FEES');
54
55 DELETE FROM OKL_SIF_FEES
56 WHERE sif_id IN (SELECT id FROM okl_stream_interfaces
57 WHERE creation_date < l_end_date
58 AND sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
59 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED'));
60 commit;
61 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from OKL_SIF_LINES');
62
63 DELETE FROM OKL_SIF_LINES
64 WHERE sif_id IN (SELECT id FROM okl_stream_interfaces
65 WHERE creation_date < l_end_date
66 AND sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
67 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED'));
68 commit;
69
70 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from OKL_SIF_STREAM_TYPES');
71
72
73 DELETE FROM OKL_SIF_STREAM_TYPES
74 WHERE sif_id IN (SELECT id FROM okl_stream_interfaces
75 WHERE creation_date < l_end_date
76 AND sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
77 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED'));
78 commit;
79
80 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from OKL_SIF_YIELDS');
81
82 DELETE FROM OKL_SIF_YIELDS
83 WHERE sif_id IN (SELECT id FROM okl_stream_interfaces
84 WHERE creation_date < l_end_date
85 AND sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
86 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED'));
87 commit;
88
89 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from OKL_SIF_PRICING_PARAMS');
90
91 DELETE FROM OKL_SIF_PRICING_PARAMS
92 WHERE sif_id IN (SELECT id FROM okl_stream_interfaces
93 WHERE creation_date < l_end_date
94 AND sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
95 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED'));
96 commit;
97
98 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from OKL_SIF_TRX_PARMS');
99
100 DELETE FROM OKL_SIF_TRX_PARMS
101 WHERE sif_id IN (SELECT id FROM okl_stream_interfaces
102 WHERE creation_date < l_end_date
103 AND sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
104 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED'));
105 commit;
106
107 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from OKL_SIF_RET_LEVELS');
108
109
110 DELETE FROM OKL_SIF_RET_LEVELS
111 WHERE sir_id IN (SELECT rets.id from OKL_SIF_RETS rets, okl_stream_interfaces str
112 WHERE rets.transaction_number = str.transaction_number
113 and str.creation_date < l_end_date
114 AND str.sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
115 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED'));
116 commit;
117
118 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from OKL_SIF_RET_ERRORS');
119
120 DELETE FROM OKL_SIF_RET_ERRORS
121 WHERE sir_id IN (SELECT rets.id from OKL_SIF_RETS rets, okl_stream_interfaces str
122 WHERE rets.transaction_number = str.transaction_number
123 and str.creation_date < l_end_date
124 AND str.sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
125 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED'));
126 commit;
127
128 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from OKL_SIF_RET_STRMS');
129
130
131 DELETE FROM OKL_SIF_RET_STRMS
132 WHERE sir_id IN (SELECT rets.id from OKL_SIF_RETS rets, okl_stream_interfaces str
133 WHERE rets.transaction_number = str.transaction_number
134 and str.creation_date < l_end_date
135 AND str.sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
136 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED'));
137 commit;
138
139 --Added by bkatraga
140 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from okl_stream_trx_data');
141
142 DELETE FROM okl_stream_trx_data
143 WHERE transaction_number IN (SELECT transaction_number FROM okl_stream_interfaces
144 WHERE creation_date < l_end_date
145 AND sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
146 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED'));
147
148 commit;
149 --end bkatraga
150
151 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from OKL_SIF_RETS');
152
153 DELETE FROM OKL_SIF_RETS
154 WHERE transaction_number IN (SELECT transaction_number FROM okl_stream_interfaces
155 WHERE creation_date < l_end_date
156 AND sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
157 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED'));
158 commit;
159
160 FND_FILE.Put_Line(FND_FILE.LOG,'Deleting records from OKL_STREAM_INTERFACES');
161
162 DELETE FROM OKL_STREAM_INTERFACES
163 WHERE creation_date < l_end_date
164 AND sis_code NOT IN ('DATA_ENTERED', 'HDR_INSERTED',
165 'PROCESSING_REQUEST', 'RET_DATA_RECEIVED');
166
167 commit;
168
169 FND_FILE.Put_Line(FND_FILE.LOG, '');
170 FND_FILE.Put_Line(FND_FILE.LOG,'End of Message Purge Concurrent Program');
171 x_retcode := 0;
172 x_errbuf := 'Successful';
173 --commit;
174 EXCEPTION
175 WHEN OTHERS THEN
176 ROLLBACK;
177 x_errbuf := SQLERRM;
178 x_retcode := 2;
179 END PURGE_INTERFACE_TABLES;
180 END OKL_PURGE_STRM_INTF_PVT;
[Home] [Help]