1 PACKAGE BODY IGI_ITR_TIMEOUT_PKG as
2 -- $Header: igiitrxb.pls 120.9.12000000.1 2007/09/12 10:33:24 mbremkum ship $
3 --
4
5 --**************************************************************************
6 -- Private procedure: Display log messages
7 -- ****************************************************************************
8 PROCEDURE WriteToLogFile (pp_mesg in varchar2) IS
9 l_debug boolean := TRUE;
10 BEGIN
11 if l_debug then
12 fnd_file.put_line( fnd_file.log , pp_mesg );
13 else
14 null;
15 end if;
16 END WriteToLogFile;
17
18 --
19 -- ********************************************************************
20 -- Procedure find_services
21 -- *********************************************************************
22 --
23
24 /* Find services for the required set of books/ data access set,
25 which need to be auto-approved
26 ** due to no response within the auto approval exceed days limit
27 ** Set their statuses to approved
28 Modified this package to find services of all ledgers in R12 Data Access Set
29 and auto approve them on 14-06-2007.
30 */
31
32
33 PROCEDURE find_services(errbuf OUT NOCOPY VARCHAR2,
34 retcode OUT NOCOPY VARCHAR2,
35 p_set_of_books_id IN NUMBER,
36 p_access_set_id IN NUMBER)
37
38 IS
39
40 /* Added this cursor for R12uptake Bug#6028574.
41 This cursor lists all the ledgers which have a write access */
42 CURSOR c_get_ledgers(p_access_set_id NUMBER)
43 IS
44 select distinct asl.ledger_id ledger_id from
45 gl_access_set_ledgers_v asl, gl_access_sets_v asv
46 where asl.access_set_id = asv.access_set_id
47 and asl.access_set_id = p_access_set_id
48 and asl.access_privilege_code in ('B','F')
49 and asl.object_type_code = 'L'
50 and asv.security_segment_code <> 'M'
51 order by ledger_id;
52 p_ledger_id NUMBER;
53
54 BEGIN
55
56 /* If Ledger Name is not passed for Concurrent Program "Automatic Approval
57 of Service Lines", then call "find_ledger_services" procedure for each
58 ledger in a Data Access Set otherwise call "find_ledger_services" procedure
59 for the specified ledger.
60 **/
61
62 IF p_set_of_books_id is null THEN
63 OPEN c_get_ledgers(p_access_set_id);
64 LOOP
65 FETCH c_get_ledgers INTO p_ledger_id;
66 EXIT WHEN c_get_ledgers%NOTFOUND;
67 find_ledger_services(errbuf, retcode, p_ledger_id);
68 END LOOP;
69 CLOSE c_get_ledgers;
70 ELSE
71 find_ledger_services(errbuf, retcode, p_set_of_books_id);
72 END IF;
73 /* Commit the changes which have been made
74 */
75 commit;
76 END find_services;
77
78 --
79 -- ********************************************************************
80 -- Procedure find_ledger_services
81 -- *********************************************************************
82 --
83 /* The code in procedure "find_services" has been moved to "find_ledger_service s", which will be called for each ledger in a Data Access Set */
84 PROCEDURE find_ledger_services( errbuf OUT NOCOPY VARCHAR2,
85 retcode OUT NOCOPY VARCHAR2,
86 p_set_of_books_id IN NUMBER)
87 IS
88 CURSOR c_is_workflow_enabled(p_set_of_books_id NUMBER)
89 IS
90 SELECT nvl(use_workflow_flag,'N')
91 FROM igi_itr_charge_setup
92 WHERE set_of_books_id = p_set_of_books_id;
93
94 l_workflow_enabled VARCHAR2(1);
95
96 /* This cursor retrieves the number of days after which
97 ** auto-approval takes place. If no value has been set up
98 ** the default value of 7 days should be used
99 **/
100
101 CURSOR c_get_timeout_days(p_set_of_books_id NUMBER)
102 IS
103 SELECT nvl(auto_approve_exceed_days,7)
104 FROM igi_itr_charge_setup
105 WHERE set_of_books_id = p_set_of_books_id;
106
107 l_timeout_days NUMBER;
108
109
110 /* This cursor retrieves the services which have been awaiting approval
111 ** for longer than the specified time limit. The status flag must be
112 ** equal to 'V' - Awaiting Reciever Approval
113 */
114
115 CURSOR c_get_waiting_services(p_set_of_books_id NUMBER,
116 p_timeout_days NUMBER,
117 p_header_id igi_itr_charge_headers.it_header_id%type )--shsaxena
118 IS
119 SELECT it_service_line_id
120 FROM igi_itr_charge_lines lines
121 WHERE lines.set_of_books_id = p_set_of_books_id
122 AND sysdate > (lines.submit_date + p_timeout_days)
123 AND lines.status_flag = 'V'
124 AND lines.it_header_id=p_header_id;
125
126 /*shsaxena for bug no 2782312*/
127 CURSOR c_header_id (p_set_of_books_id NUMBER,
128 p_timeout_days NUMBER)
129 IS
130 SELECT Distinct it_header_id from igi_itr_charge_lines lines
131 WHERE lines.set_of_books_id = p_set_of_books_id
132 AND sysdate > (lines.submit_date + p_timeout_days)
133 AND lines.status_flag = 'V';
134 /*shsaxena for bug no 2782312*/
135
136
137 l_it_service_line_id NUMBER;
138 l_it_header_id igi_itr_charge_lines.it_header_id%type; --shsaxena for bug 2782312
139 l_sequence_num NUMBER;
140 l_rec_fnd_user_id NUMBER;
141 l_user_id NUMBER := fnd_global.user_id;
142 l_conc_login_id NUMBER := fnd_global.conc_login_id;
143 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
144 l_state_level number := FND_LOG.LEVEL_STATEMENT;
145 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
146 l_event_level number := FND_LOG.LEVEL_EVENT;
147 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
148 l_error_level number := FND_LOG.LEVEL_ERROR;
149 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
150
151
152
153 BEGIN
154
155 /* Check if workflow is enabled for the set of books
156 ** If it is, do nothing and exit
157 **/
158
159
160 OPEN c_is_workflow_enabled(p_set_of_books_id);
161 FETCH c_is_workflow_enabled INTO l_workflow_enabled;
162 IF c_is_workflow_enabled%NOTFOUND THEN
163 l_workflow_enabled := 'N';
164 END IF;
165 CLOSE c_is_workflow_enabled;
166
167 IF l_workflow_enabled = 'Y' THEN
168 IF (l_state_level >= l_debug_level ) THEN
169 FND_LOG.STRING( l_state_level,'igi.plsql.igiitrxb.IGI_ITR_TIMEOUT_PKG.find_services','Workflow is enabled for the set of books ');
170 END IF;
171
172 return;
173 END IF;
174
175
176 /* Fetch the number of days after which auto-approval should occur.
177 ** This is set up at the ITR set options level, per set of books.
178 */
179
180 OPEN c_get_timeout_days(p_set_of_books_id);
181 FETCH c_get_timeout_days INTO l_timeout_days;
182 IF c_get_timeout_days%NOTFOUND THEN
183 l_timeout_days := 7;
184 END IF;
185 CLOSE c_get_timeout_days;
186
187 IF (l_state_level >= l_debug_level ) THEN
188 FND_LOG.STRING( l_state_level,'igi.plsql.igiitrxb.IGI_ITR_TIMEOUT_PKG.find_services','Service lines will be auto approved if waiting for '||l_timeout_days );
189 END IF;
190
191 /*shsaxena for bug no 2782312*/
192 OPEN c_header_id (p_set_of_books_id ,l_timeout_days);
193 LOOP
194 FETCH c_header_id INTO l_it_header_id;
195 IF c_header_id%NOTFOUND THEN
196 IF (l_state_level >= l_debug_level ) THEN
197 FND_LOG.STRING( l_state_level,'igi.plsql.igiitrxb.IGI_ITR_TIMEOUT_PKG.find_services','There are no more service lines awaiting approval for set of books '||p_set_of_books_id );
198 END IF;
199 EXIT;
200 END IF;
201
202 OPEN c_get_waiting_services(p_set_of_books_id
203 ,l_timeout_days,
204 l_it_header_id);
205 LOOP
206 FETCH c_get_waiting_services INTO l_it_service_line_id;
207 EXIT WHEN c_get_waiting_services%NOTFOUND;
208
209
210 UPDATE igi_itr_charge_lines
211 SET status_flag = 'A'
212 ,last_updated_by = l_user_id
213 ,last_update_login = l_conc_login_id
214 ,last_update_date = sysdate
215 WHERE it_service_line_id = l_it_service_line_id;
216
217 IF (l_state_level >= l_debug_level ) THEN
218 FND_LOG.STRING( l_state_level,'igi.plsql.igiitrxb.IGI_ITR_TIMEOUT_PKG.find_services','Service line id '||l_it_service_line_id||' has been auto approved');
219 END IF;
220 /* Now need to update the action history table with information
221 ** indicating that the service line has been auto-approved.
222 ** So start by fetching all the information needed for insertion
223 ** into the action history table
224 */
225
226 SELECT max(sequence_num) + 1
227 INTO l_sequence_num
228 FROM igi_itr_action_history
229 WHERE it_service_line_id = l_it_service_line_id;
230
231 /* Find the fnd user id of the receiver for the service line
232 */
233
234 SELECT auth.authoriser_id
235 INTO l_rec_fnd_user_id
236 FROM igi_itr_charge_ranges auth
237 ,igi_itr_charge_lines itrl
238 WHERE itrl.it_service_line_id = l_it_service_line_id
239 AND itrl.charge_range_id = auth.charge_range_id;
240
241
242 /* Call common package to insert record into action
243 ** history table
244 */
245
246 igi_itr_action_history_ss_pkg.insert_row(
247 X_Service_Line_Id => l_it_service_line_id
248 ,X_Sequence_Num => l_sequence_num
249 ,X_Action_Code => 'U'
250 ,X_Action_Date => sysdate
251 ,X_Employee_Id => l_rec_fnd_user_id
252 ,X_Use_Workflow_Flag => 'N'
253 ,X_Note => null
254 ,X_Created_By => l_user_id
255 ,X_Creation_Date => sysdate
256 ,X_Last_Update_Login => l_conc_login_id
257 ,X_Last_Update_Date => sysdate
258 ,X_Last_Updated_By => l_user_id
259 );
260
261 IF (l_state_level >= l_debug_level ) THEN
262 FND_LOG.STRING( l_state_level,'igi.plsql.igiitrxb.IGI_ITR_TIMEOUT_PKG.find_services','Action History table has been updated for service line id '||l_it_service_line_id );
263 END IF;
264
265
266 END LOOP;
267 CLOSE c_get_waiting_services;
268 IGIGITCH.update_header_status(l_it_header_id);
269
270 END LOOP;
271 CLOSE c_header_id;
272 /*shsaxena for bug no 2782312*/
273
274 EXCEPTION
275 WHEN OTHERS
276 THEN
277 IF ( l_unexp_level >= l_debug_level) THEN
278 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
279 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
280 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
281 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrwb.IGI_ITR_TIMEOUT_TEST_PKG.find_services',TRUE);
282 END IF;
283 raise_application_error
284 (-20001,'IGI_ITR_TIMEOUT_TEST_PKG.find_services'||SQLERRM);
285
286 END find_ledger_services;
287
288
289 END IGI_ITR_TIMEOUT_PKG;
[Home] [Help]