[Home] [Help]
PACKAGE BODY: APPS.XDP_CRON_UTIL
Source
1 PACKAGE BODY XDP_CRON_UTIL AS
2 /* $Header: XDPCRONB.pls 120.1 2005/06/08 23:51:10 appldev $ */
3
4 Procedure SubmitAdapterAdminJob (
5 p_request in number,
6 p_RunDate in date,
7 p_RunFreq in number default null,
8 p_JobNumber OUT NOCOPY number )
9 IS
10 l_tmpdate varchar2(4000);
11 BEGIN
12 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN -- Fix: 4256771, dbhagat, 28 Apr 05
13 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE, 'XDP_CRON_UTIL.SUBMITADAPTERADMINJOB',
14 'BEGIN:p_request: '||p_request);
15 END IF;
16
17 if p_RunFreq is not null then
18 l_tmpdate := 'sysdate + (' || TO_CHAR(p_RunFreq) || '/(24*60))';
19 else
20 l_tmpdate := null;
21 end if;
22
23 DBMS_JOB.SUBMIT(job => p_JobNumber,
24 what => pv_jobAdapterAdmin || '('||to_char(p_request)||');',
25 next_date => p_RunDate,
26 interval =>l_tmpdate);
27
28 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN -- Fix: 4256771, dbhagat, 28 Apr 05
29 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, 'XDP_CRON_UTIL.SUBMITADAPTERADMINJOB',
30 'END:Job: '||p_JobNumber);
31 END IF;
32
33 END SubmitAdapterAdminJob;
34
35
36 Procedure UpdateDBJob (
37 p_jobID in number,
38 p_request in number,
39 p_ReqDate in date,
40 p_Freq in number )
41 is
42 l_tmpdate varchar2(4000);
43 begin
44 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN -- Fix: 4256771, dbhagat, 28 Apr 05
45 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE, 'XDP_CRON_UTIL.UPDATEDBJOB',
46 'BEGIN:p_request: '||p_request);
47 END IF;
48
49 if p_Freq is not null then
50 l_tmpdate := 'sysdate + (' || TO_CHAR(p_Freq) || '/(24*60))';
51 else
52 l_tmpdate := null;
53 end if;
54
55 DBMS_JOB.CHANGE(job => p_jobID,
56 what => pv_jobAdapterAdmin || '('||to_char(p_request)||');',
57 next_date => p_ReqDate,
58 interval =>l_tmpdate);
59
60 end UpdateDBJob;
61
62 Procedure Execute_Adapter_Admin(p_request in number)
63 IS
64 l_dummydate date;
65 l_requestCode varchar2(30);
66 l_Freq number;
67 l_job_no number;
68 l_ChannelName varchar2(40);
69
70 l_Status varchar2(30) := XDP_ADAPTER.pv_adminStatusCompleted;
71
72 l_ErrorMsg VARCHAR2 (4000);
73 l_RetCode NUMBER := 0;
74
75 l_offset NUMBER;
76 l_offset1 NUMBER;
77 l_LockTimeout NUMBER;
78
79 l_User varchar2(40);
80
81 BEGIN
82 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN -- Fix: 4256771, dbhagat, 28 Apr 05
83 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE, 'XDP_CRON_UTIL.EXECUTE_ADAPTER_ADMIN',
84 'BEGIN:p_request: '||p_request);
85 END IF;
86
87 BEGIN
88 XDP_ADAPTER_CORE_DB.FetchAdapterAdminReqInfo(
89 p_RequestID => p_request,
90 p_RequestType => l_requestCode,
91 p_RequestDate => l_dummydate,
92 p_RequestedBy => l_User,
93 p_Freq => l_Freq,
94 p_DBJobID => l_job_no,
95 p_ChannelName => l_ChannelName);
96
97 -- dbms_output.put_line('Request Type: ' || l_requestCode);
98 EXCEPTION
99 WHEN others then
100 -- What can we do? - nothing
101 -- WHEN NO_DATA_FOUND then
102 -- This is really weird, request is getting executed and
103 -- request is not there? Maybe, request got deleted and job is still
104 -- getting executed. Donot do anything, return
105 -- With the deletion of request, job for future will anyway be deleted
106
107 IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN -- Fix: 4256771, dbhagat, 28 Apr 05
108 FND_LOG.STRING (FND_LOG.LEVEL_ERROR,
109 'XDP_CRON_UTIL.EXECUTE_ADAPTER_ADMIN',
110 'After FetchAdapterAdminReqInfo, SQLCODE: '||SQLCODE);
111 END IF;
112 return;
113 END;
114
115 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN -- Fix: 4256771, dbhagat, 28 Apr 05
116 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION, 'XDP_CRON_UTIL.EXECUTE_ADAPTER_ADMIN',
117 'Request Type: '||l_requestCode||
118 ', Job no.: '||l_job_no||
119 ', Channel name: '||l_ChannelName);
120 END IF;
121
122 if l_requestCode = XDP_ADAPTER.pv_opSuspend then
123 -- dbms_output.put_line('suspending.. ');
124 XDP_ADAPTER.Suspend_Adapter(l_ChannelName, l_RetCode, l_ErrorMsg);
125
126 elsif l_requestCode = XDP_ADAPTER.pv_opConnect then
127 -- dbms_output.put_line('Connecting.. ');
128 XDP_ADAPTER.Connect_Adapter(l_ChannelName, l_RetCode, l_ErrorMsg);
129
130 elsif l_requestCode = XDP_ADAPTER.pv_opDisconnect then
131 -- dbms_output.put_line('Disconnecting.. ');
132 XDP_ADAPTER.Disconnect_Adapter(l_ChannelName, l_RetCode, l_ErrorMsg);
133
134 elsif l_requestCode = XDP_ADAPTER.pv_opResume then
135 -- dbms_output.put_line('resuming.. ');
136 XDP_ADAPTER.Resume_Adapter(l_ChannelName, l_RetCode, l_ErrorMsg);
137 elsif (l_requestCode = XDP_ADAPTER.pv_opStop) then
138
139 l_LockTimeout := XDP_ADAPTER_CORE_DB.GetLockTimeOut;
140
141 if XDP_ADAPTER_CORE_DB.ObtainAdapterLock_Verify(
142 p_ChannelName => l_ChannelName,
143 p_Timeout => l_LockTimeout) = 'N' then
144
145 l_RetCode := XDP_ADAPTER.pv_retAdapterCannotLock;
146
147 -- Could get the lock after 1 HOUR, continue
148 -- Following operation will also not get lock
149 -- and the logic will flow.
150 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN -- Fix: 4256771, dbhagat, 28 Apr 05
151 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
152 'XDP_CRON_UTIL.EXECUTE_ADAPTER_ADMIN',
153 'Could get lock after waiting for :'||l_LockTimeout||' secs');
154 END IF;
155 end if;
156
157 -- Special case, we donot want this to be cyclic
158 if (l_RetCode <> XDP_ADAPTER.pv_retAdapterCannotLock) then
159 -- dbms_output.put_line('Stopping.. ');
160 XDP_ADAPTER.Stop_Adapter(l_ChannelName, l_RetCode, l_ErrorMsg);
161 -- Release the lock
162 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock(
163 p_ChannelName => l_ChannelName);
164 END IF;
165
166 elsif l_requestCode = XDP_ADAPTER.pv_opStartup then
167 -- dbms_output.put_line('Starting.. ');
168 XDP_ADAPTER.Start_Adapter(l_ChannelName, l_RetCode, l_ErrorMsg);
169
170 elsif l_requestCode = XDP_ADAPTER.pv_opVerify then
171 -- dbms_output.put_line('Verifying.. ');
172 XDP_ADAPTER.Verify_Adapter(l_ChannelName, l_RetCode, l_ErrorMsg);
173
174 -- elsif l_requestCode = XDP_ADAPTER.pv_opFtpFile then
175 else
176 -- dbms_output.put_line('Generic control operation.. ');
177 -- TODO What about parameters?
178 XDP_ADAPTER.Generic_Operation (
179 l_ChannelName, l_requestCode, '',
180 l_RetCode, l_ErrorMsg);
181 end if;
182
183 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN -- Fix: 4256771, dbhagat, 28 Apr 05
184 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, 'XDP_CRON_UTIL.EXECUTE_ADAPTER_ADMIN',
185 'After adapter operation, Return code: '||l_RetCode);
186 END IF;
187
188 l_Status := XDP_ADAPTER.pv_adminStatusCompleted;
189
190 if l_RetCode = XDP_ADAPTER.pv_retAdapterInvalidState or
191 l_RetCode = XDP_ADAPTER.pv_retAdapterCannotLock then
192 -- SKIPPED
193 l_Status := XDP_ADAPTER.pv_adminStatusSkipped;
194 elsif l_RetCode <> 0 then
195 -- ERRORED cases are:
196 -- pv_retAdapterCtrlNotRunning
197 -- pv_retAdapterAbnormalExit, will not happen for pv_opStart
198 -- when other errors
199 l_Status := XDP_ADAPTER.pv_adminStatusErrored;
200 end if;
201
202 if l_Freq is null then
203
204 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN -- Fix: 4256771, dbhagat, 28 Apr 05
205 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT,
206 'XDP_CRON_UTIL.EXECUTE_ADAPTER_ADMIN',
207 'Frequency is null, removing job');
208 END IF;
209 -- dbms_output.put_line('No Freq found... updating req');
210
211 XDP_ADAPTER.Delete_Admin_Request (
212 p_request, l_RetCode, l_ErrorMsg);
213 -- Donot care for l_RetCode
214 end if;
215
216 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN -- Fix: 4256771, dbhagat, 28 Apr 05
217 FND_LOG.STRING (FND_LOG.LEVEL_STATEMENT, 'XDP_CRON_UTIL.EXECUTE_ADAPTER_ADMIN',
218 'Request audited with status: '||l_Status);
219 END IF;
220
221 XDP_ADAPTER_CORE_DB.Audit_Adapter_Admin_Request (
222 p_RequestID => p_request,
223 p_RequestType => l_requestCode,
224 p_RequestDate => l_dummydate,
225 p_RequestedBy => l_User,
226 p_Freq => l_Freq,
227 p_RequestStatus => l_Status,
228 p_RequestMessage => l_ErrorMsg,
229 p_ChannelName => l_ChannelName);
230
231 commit;
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 -- Can come here only because of some SQL error in XDP_ADAPTER_CORE_DB.Audit_Adapter_Admin_Request
236 -- Still we need to commit;
237 IF( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN -- Fix: 4256771, dbhagat, 28 Apr 05
238 FND_LOG.STRING (FND_LOG.LEVEL_ERROR, 'XDP_CRON_UTIL.EXECUTE_ADAPTER_ADMIN',
239 'Unhandled error, SQLCODE: '||SQLCODE);
240 END IF;
241 l_Status := XDP_ADAPTER_CORE_DB.ReleaseAdapterLock(p_ChannelName => l_ChannelName);
242 commit;
243
244 END Execute_Adapter_Admin;
245
246 end XDP_CRON_UTIL;