[Home] [Help]
PACKAGE BODY: APPS.AS_LLOG_SUMMARY_PKG
Source
1 Package body AS_LLOG_SUMMARY_PKG AS
2 /* $Header: asxopslb.pls 115.9 2004/06/11 06:29:52 pvarshne ship $ */
3
4
5
6 PROCEDURE write_log(p_debug_source NUMBER, p_fpt number, p_mssg varchar2) IS
7 BEGIN
8 /*
9 --IF G_Debug AND p_debug_source = G_DEBUG_TRIGGER THEN
10 -- Write debug message to message stack
11 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, p_mssg);
12 --END IF;
13 */
14 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
15 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
16 fnd_message.Set_Token('TEXT', p_mssg );
17 fnd_msg_pub.Add;
18 END IF;
19
20
21 IF p_debug_source = G_DEBUG_CONCURRENT THEN
22 -- p_fpt (1,2)?(log : output)
23 FND_FILE.put(p_fpt, p_mssg);
24 FND_FILE.NEW_LINE(p_fpt, 1);
25 -- If p_fpt == 2 and debug flag then also write to log file
26 IF p_fpt = 2 And G_Debug THEN
27 FND_FILE.put(1, p_mssg);
28 FND_FILE.NEW_LINE(1, 1);
29 END IF;
30 END IF;
31
32 EXCEPTION
33 WHEN OTHERS THEN
34 NULL;
35 END Write_Log;
36
37 -- Why doesn't use dbms_session.set_sql_trace(TRUE) ?
38 PROCEDURE trace (p_mode in boolean) is
39 ddl_curs integer;
40 v_Dummy integer;
41 BEGIN
42 null;
43 EXCEPTION WHEN OTHERS THEN
44 NULL;
45 END trace;
46
47
48
49 PROCEDURE Precalculate_Log_data IS
50
51 BEGIN
52
53
54 -- clean out duplicate logs on the same LEAD_ID and LAST_UPDATE_DATE
55 delete from as_leads_log log
56 where log.log_id <>
57 ( select max(log1.log_id)
58 from as_leads_log log1
59 where log1.lead_id = log.lead_id
60 and log1.last_update_date = log.last_update_date);
61
62
63 -- initial the derivative columns and set log_start_date
64 update as_leads_log
65 set object_version_number = nvl(object_version_number,0) + 1, log_start_date = last_update_date,
66 log_end_date = null,
67 log_active_days = null,
68 endday_log_flag = 'N',
69 current_log = 0;
70
71
72 -- set log_end_date
73 update as_leads_log log
74 set object_version_number = nvl(object_version_number,0) + 1, log.log_end_date =
75 ( select min(log1.last_update_date)
76 from as_leads_log log1
77 where log1.lead_id = log.lead_id
78 and log1.last_update_date > log.last_update_date);
79
80 update as_leads_log log
81 set object_version_number = nvl(object_version_number,0) + 1, log.log_end_date = log.log_start_date
82 where log.log_end_date is null;
83
84 -- set log_active_days
85 update as_leads_log
86 set object_version_number = nvl(object_version_number,0) + 1, log_active_days = trunc(log_end_date) - trunc(log_start_date)
87 where log_active_days is null;
88
89 -- set endday_log_flag
90 update as_leads_log log
91 set object_version_number = nvl(object_version_number,0) + 1, log.endday_log_flag = 'Y'
92 where log.last_update_date =
93 ( select max(log1.last_update_date)
94 from as_leads_log log1
95 where log1.lead_id = log.lead_id
96 and trunc(log1.last_update_date) = trunc(log.last_update_date) );
97
98 -- set current_log
99 update as_leads_log log
100 set object_version_number = nvl(object_version_number,0) + 1, log.current_log = 1
101 where log.last_update_date =
102 ( select max(log1.last_update_date)
103 from as_leads_log log1
104 where log1.lead_id = log.lead_id );
105
106 EXCEPTION WHEN OTHERS THEN
107 Write_Log(G_DEBUG_CONCURRENT, 1, 'Error in Precalculate_Log_data');
108 Write_Log(G_DEBUG_CONCURRENT, 1, sqlerrm);
109 ROLLBACK;
110 END Precalculate_Log_data;
111
112
113
114 Procedure Refresh_Status_Summary(
115 ERRBUF OUT Varchar2,
116 RETCODE OUT Varchar2,
117 p_debug_mode IN Varchar2,
118 p_trace_mode IN Varchar2)
119 IS
120
121
122 CURSOR last_refresh_date IS
123 select PROGRAM_UPDATE_DATE
124 from as_last_run_dates
125 where PROGRAM_NAME = 'ASXRSSM';
126
127 CURSOR from_date IS
128 select min(LAST_UPDATE_DATE)
129 from as_leads_log;
130
131 l_this_refresh_date DATE;
132 l_last_refresh_date DATE;
133
134
135 BEGIN
136
137 IF p_debug_mode = 'Y' THEN G_Debug := TRUE; ELSE G_Debug := FALSE; END IF;
138 IF p_trace_mode = 'Y' THEN trace(TRUE); ELSE trace(FALSE); END IF;
139 Write_Log(G_DEBUG_CONCURRENT, 1, 'Process began: ' || to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
140 RETCODE := 0;
141
142
143 OPEN last_refresh_date;
144 FETCH last_refresh_date INTO l_last_refresh_date;
145 CLOSE last_refresh_date;
146
147 IF l_last_refresh_date IS NULL THEN
148 Write_Log(G_DEBUG_CONCURRENT, 1, 'This is the first time to run the program');
149 OPEN from_date;
150 FETCH from_date INTO l_last_refresh_date;
151 CLOSE from_date;
152 IF l_last_refresh_date IS NULL THEN
153 l_last_refresh_date := sysdate - 10000;
154 END IF;
155 -- Precalculate_Log_data;
156 ELSE
157 Write_Log(G_DEBUG_CONCURRENT, 1, 'This program was last run on '|| to_char(l_last_refresh_date));
158 END IF;
159
160 l_this_refresh_date := sysdate;
161
162 DELETE FROM AS_LLOG_STATUS_SUMMARY
163 WHERE lead_id in
164 ( select lead_id
165 from as_leads_log
166 where last_update_date >= l_last_refresh_date );
167
168 INSERT INTO AS_LLOG_STATUS_SUMMARY
169 ( CREATION_DATE,
170 CREATED_BY,
171 LAST_UPDATE_DATE,
172 LAST_UPDATED_BY,
173 LAST_UPDATE_LOGIN,
174 REQUEST_ID,
175 PROGRAM_ID,
176 PROGRAM_APPLICATION_ID,
177 PROGRAM_UPDATE_DATE,
178 LEAD_ID,
179 DECISION_DATE,
180 STATUS_CODE,
181 STATUS_START_DATE,
182 STATUS_END_DATE,
183 STATUS_DAYS,
184 CURRENT_STATUS
185 )
186 SELECT SYSDATE CREATION_DATE,
187 FND_GLOBAL.USER_ID CREATED_BY,
188 SYSDATE LAST_UPDATE_DATE,
189 FND_GLOBAL.USER_ID LAST_UPDATED_BY,
190 FND_GLOBAL.CONC_LOGIN_ID LAST_UPDATE_LOGIN,
191 FND_GLOBAL.Conc_Request_Id REQUEST_ID,
192 FND_GLOBAL.Conc_Program_Id PROGRAM_ID,
193 FND_GLOBAL.Prog_Appl_Id PROGRAM_APPLICATION_ID,
194 SYSDATE PROGRAM_UPDATE_DATE,
195 log.LEAD_ID LEAD_ID,
196 ld.DECISION_DATE DECISION_DATE,
197 log.STATUS_CODE STATUS_CODE,
198 min(log.LOG_START_DATE) STATUS_START_DATE,
199 max(log.LOG_END_DATE) STATUS_END_DATE,
200 sum(log.LOG_ACTIVE_DAYS) STATUS_DAYS,
201 sum(log.CURRENT_LOG) CURRENT_STATUS
202 FROM AS_LEADS_LOG log,
203 AS_LEADS_ALL ld
204 WHERE log.ENDDAY_LOG_FLAG = 'Y'
205 AND ld.LEAD_ID = log.LEAD_ID
206 AND log.LEAD_ID IN
207 ( select lead_id
208 from as_leads_log
209 where last_update_date >= l_last_refresh_date )
210 GROUP BY log.LEAD_ID, ld.DECISION_DATE, log.STATUS_CODE;
211
212 -- Update as_last_run_dates
213 UPDATE AS_LAST_RUN_DATES
214 SET PROGRAM_UPDATE_DATE = l_this_refresh_date,
215 LAST_UPDATE_DATE = sysdate,
216 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
217 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
218 REQUEST_ID = FND_GLOBAL.Conc_Request_Id,
219 PROGRAM_APPLICATION_ID = FND_GLOBAL.Prog_Appl_Id,
220 PROGRAM_ID = FND_GLOBAL.Conc_Program_Id
221 WHERE PROGRAM_NAME = 'ASXRSSM';
222 IF (SQL%NOTFOUND) THEN
223 INSERT INTO AS_LAST_RUN_DATES
224 ( LAST_UPDATE_DATE,
225 LAST_UPDATED_BY,
226 CREATION_DATE,
227 CREATED_BY,
228 LAST_UPDATE_LOGIN,
229 REQUEST_ID,
230 PROGRAM_APPLICATION_ID,
231 PROGRAM_ID,
232 PROGRAM_UPDATE_DATE,
233 PROGRAM_NAME
234 )
235 VALUES
236 ( sysdate,
237 FND_GLOBAL.USER_ID,
238 sysdate,
239 FND_GLOBAL.USER_ID,
240 FND_GLOBAL.CONC_LOGIN_ID,
241 FND_GLOBAL.Conc_Request_Id,
242 FND_GLOBAL.Prog_Appl_Id,
243 FND_GLOBAL.Conc_Program_Id,
244 l_this_refresh_date,
245 'ASXRSSM'
246 );
247 END IF;
248
249 COMMIT;
250
251 Write_Log(G_DEBUG_CONCURRENT, 1, 'Process end: ' || to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
252
253 EXCEPTION
254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
255 ERRBUF := ERRBUF||'Error in Refresh_Status_Summary: '||to_char(sqlcode);
256 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
257 Write_Log(G_DEBUG_CONCURRENT, 1, 'Error in Refresh_Status_Summary');
258 Write_Log(G_DEBUG_CONCURRENT, 1, sqlerrm);
259 ROLLBACK;
260 WHEN OTHERS THEN
261 ERRBUF := ERRBUF||'Error in efresh_Status_Summary: '||to_char(sqlcode);
262 RETCODE := '2';
263 Write_Log(G_DEBUG_CONCURRENT, 1, 'Error in Refresh_Status_Summary');
264 Write_Log(G_DEBUG_CONCURRENT, 1, sqlerrm);
265 ROLLBACK;
266 END Refresh_Status_Summary;
267
268 End AS_LLOG_SUMMARY_PKG;