[Home] [Help]
PACKAGE BODY: APPS.CSF_DEBRIEF_PVT
Source
1 PACKAGE BODY CSF_DEBRIEF_PVT as
2 /* $Header: csfvdbfb.pls 120.67.12020000.4 2013/04/09 09:41:05 shadas ship $ */
3
4 -- Start of Comments
5 -- Package name : CSF_DEBRIEF_PVT
6 -- Purpose :
7 -- History :
8 -- NOTE :
9 -- End of Comments
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSF_DEBRIEF_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csfvdbfb.pls';
13
14 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
15 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
16 G_UPDATE NUMBER := 1;
17 G_CREATE NUMBER := 2;
18
19 procedure log(p_procedure in varchar2,p_message in varchar2) as
20 begin
21 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
22 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
23 'csf.plsql.csf_debrief_pvt.'||p_procedure,
24 p_message);
25 end if;
26 end;
27
28 Procedure validate_start_end(p_labor_start_time date,
29 p_labor_end_time date,
30 p_debrief_header_id NUMBER,
31 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
32 X_Return_Status OUT NOCOPY VARCHAR2,
33 X_Msg_Count OUT NOCOPY NUMBER,
34 X_Msg_Data OUT NOCOPY VARCHAR2) IS
35
36 l_service_request_date date;
37 l_date_val varchar2(30) := nvl(fnd_profile.value('CSF_DEBRIEF_SERVICE_DATE'),'B');
38
39 Cursor get_service_request_date IS
40 select nvl(incident_occurred_date,incident_date)
41 from jtf_task_assignments jta , cs_incidents_all cia, jtf_tasks_b jtb,
42 csf_debrief_headers cdh
43 where jta.task_assignment_id = cdh.task_assignment_id
44 and jtb.task_id = jta.task_id
45 and cia.incident_id = jtb.source_object_id
46 and jtb.source_object_type_code = 'SR'
47 and cdh.debrief_header_id = p_debrief_header_id;
48
49 BEGIN
50 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
51 open get_service_request_date;
52 fetch get_service_request_date INTO l_service_request_date;
53 close get_service_request_date;
54
55 If (p_labor_start_time IS NOT NULL
56 and p_labor_end_time IS NOT NULL
57 and p_labor_start_time <> FND_API.g_miss_date
58 and p_labor_end_time <> FND_API.g_miss_date
59 AND p_labor_start_time > p_labor_end_time) THEN
60 X_Return_Status := FND_API.G_RET_STS_ERROR;
61 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
62 FND_MESSAGE.Set_Name('CSF','CSF_LABOR_START_DATE_ERR');
63 FND_MSG_PUB.ADD;
64 END IF;
65 END IF;
66 If p_labor_start_time IS NOT NULL
67 and l_date_val in ('N','P')
68 and p_labor_start_time <> FND_API.g_miss_date
69 and trunc(fnd_timezones_pvt.adjust_datetime(p_labor_start_time,
70 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')),
71 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID'))))
72 > trunc(fnd_timezones_pvt.adjust_datetime(sysdate,
73 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')),
74 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')))) THEN
75 X_Return_Status := FND_API.G_RET_STS_ERROR;
76 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
77 FND_MESSAGE.Set_Name('CSF','CSFW_SERVICE_DATE');
78 fnd_message.set_token('P_SR_DATE',to_char(fnd_timezones_pvt.adjust_datetime(l_service_request_date,
79 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')),
80 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID'))),'DD-Mon-RRRR HH24:MI'));
81 fnd_message.set_token('P_SYSTEM_DATE',to_char(trunc(fnd_timezones_pvt.adjust_datetime(sysdate,
82 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')),
83 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')))),'DD-Mon-RRRR')||' 23:59');
84 FND_MSG_PUB.ADD;
85 END IF;
86 END IF;
87 If p_labor_end_time IS NOT NULL
88 and l_date_val in ('N','P')
89 and p_labor_end_time <> FND_API.g_miss_date
90 and trunc(fnd_timezones_pvt.adjust_datetime(p_labor_end_time,
91 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')),
92 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID'))))
93 > trunc(fnd_timezones_pvt.adjust_datetime(sysdate,
94 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')),
95 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')))) THEN
96 X_Return_Status := FND_API.G_RET_STS_ERROR;
97 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
98 FND_MESSAGE.Set_Name('CSF','CSFW_SERVICE_DATE_END');
99 fnd_message.set_token('P_SR_DATE',to_char(fnd_timezones_pvt.adjust_datetime(l_service_request_date,
100 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')),
101 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID'))),'DD-Mon-RRRR HH24:MI'));
102 fnd_message.set_token('P_SYSTEM_DATE',to_char(trunc(fnd_timezones_pvt.adjust_datetime(sysdate,
103 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')),
104 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')))),'DD-Mon-RRRR')||' 23:59');
105 FND_MSG_PUB.ADD;
106 END IF;
107 END IF;
108
109 If (p_labor_start_time IS NOT NULL
110 and l_date_val in ('N','Y')
111 and p_labor_start_time <> FND_API.g_miss_date
112 and p_labor_start_time < l_service_request_date) THEN
113 X_Return_Status := FND_API.G_RET_STS_ERROR;
114 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
115 FND_MESSAGE.Set_Name('CSF','CSFW_SERVICE_DATE');
116 fnd_message.set_token('P_SR_DATE',to_char(fnd_timezones_pvt.adjust_datetime(l_service_request_date,
117 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')),
118 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID'))),'DD-Mon-RRRR HH24:MI'));
119 fnd_message.set_token('P_SYSTEM_DATE',to_char(trunc(fnd_timezones_pvt.adjust_datetime(sysdate,
120 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')),
121 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')))),'DD-Mon-RRRR')||' 23:59');
122 FND_MSG_PUB.ADD;
123 END IF;
124 END IF;
125 If (p_labor_end_time IS NOT NULL
126 and l_date_val in ('N','Y')
127 and p_labor_end_time <> FND_API.g_miss_date
128 and p_labor_end_time < l_service_request_date) THEN
129 X_Return_Status := FND_API.G_RET_STS_ERROR;
130 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
131 FND_MESSAGE.Set_Name('CSF','CSFW_SERVICE_DATE_END');
132 fnd_message.set_token('P_SR_DATE',to_char(fnd_timezones_pvt.adjust_datetime(l_service_request_date,
133 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')),
134 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID'))),'DD-Mon-RRRR HH24:MI'));
135 fnd_message.set_token('P_SYSTEM_DATE',to_char(trunc(fnd_timezones_pvt.adjust_datetime(sysdate,
136 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')),
137 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')))),'DD-Mon-RRRR')||' 23:59');
138 FND_MSG_PUB.ADD;
139 END IF;
140 END IF;
141 END;
142
143 PROCEDURE Create_debrief(
144 P_Api_Version_Number IN NUMBER,
145 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
146 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
147 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
148 P_DEBRIEF_Rec IN DEBRIEF_Rec_Type := G_MISS_DEBRIEF_REC,
149 P_DEBRIEF_LINE_tbl IN DEBRIEF_LINE_tbl_type
150 := G_MISS_DEBRIEF_LINE_tbl,
151 X_DEBRIEF_HEADER_ID OUT NOCOPY NUMBER,
152 X_Return_Status OUT NOCOPY VARCHAR2,
153 X_Msg_Count OUT NOCOPY NUMBER,
154 X_Msg_Data OUT NOCOPY VARCHAR2
155 )
156 IS
157 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
158 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
159 l_row_id varchar2(100) := null;
160 l_api_name CONSTANT VARCHAR2(30) := 'Create_Debrief';
161 l_api_version_number CONSTANT NUMBER := 1.0;
162 l_return_status VARCHAR2(1);
163 l_count NUMBER := p_Debrief_line_tbl.count;
164 l_DEBRIEF_HEADER_ID NUMBER;
165 l_service_date date;
166 l_debrief_number varchar2(50);
167 l_interaction_rec JTF_IH_PUB.INTERACTION_REC_TYPE;
168 l_activity_rec JTF_IH_PUB.ACTIVITY_REC_TYPE;
169 l_resource_id number;
170 l_task_id number;
171 l_party_id number;
172 l_task_assignment_id number ;
173 l_cust_account_id number;
174 l_msg_count number;
175 l_msg_data varchar2(2000);
176 l_interaction_id number;
177 l_activity_id number;
178 x number;
179 l_msg_index_out number;
180 l_source_object_type_code varchar2(30);
181 errbuf Varchar2(1000);
182 retcode Number;
183 l_Unit_Of_Measure_For_Hr varchar2(3);
184 l_oject_version_number NUMBER;
185 l_actual_travel_duration NUMBER;
186 l_resource_type_code VARCHAR2(30);
187 l_task_object_version NUMBER := null;
188 l_task_status_id NUMBER := null;
189
190 cursor c_source_object_type_code (p_task_assignment_id number) is
191 select jtv.source_object_type_code
192 from jtf_tasks_vl jtv,
193 jtf_task_assignments_v jta
194 where jta.task_assignment_id = p_task_assignment_id
195 and jta.task_id = jtv.task_id;
196
197 cursor c_task_assgin_object_version is
198 select jta.object_version_number, jta.resource_type_code, jta.resource_id
199 from jtf_task_assignments jta
200 where jta.task_assignment_id = p_debrief_rec.TASK_ASSIGNMENT_ID;
201
202 BEGIN
203
204
205 -- Standard Start of API savepoint
206
207 SAVEPOINT CREATE_DEBRIEF_HEADER_PVT;
208 -- Standard call to check for call compatibility.
209
210 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
211 p_api_version_number,
212 l_api_name,
213 G_PKG_NAME)
214 THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END IF;
217
218
219 -- Initialize message list if p_init_msg_list is set to TRUE.
220 IF FND_API.to_Boolean( p_init_msg_list )
221 THEN
222 FND_MSG_PUB.initialize;
223 END IF;
224
225
226 -- Debug Message
227 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
228 THEN
229 FND_MESSAGE.Set_Name('CSF', l_api_name);
230 FND_MESSAGE.Set_Token ('INFO', G_PKG_NAME, FALSE);
231 FND_MSG_PUB.Add;
232
233 END IF;
234
235 -- Initialize API return status to SUCCESS
236 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
237
238 --
239 -- API body
240 --
241 -- ******************************************************************
242 -- Validate Environment
243 -- ******************************************************************
244
245 -- Virendra Singh 03/24/2000 check whether Header record is missing
246 if IS_DEBRIEF_HEADER_REC_MISSING(P_DEBRIEF_REC) then
247
248 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
249 THEN
250 fnd_message.set_name('CSF', 'CSF_DEBRIEF_MISSING_HEADER');
251 fnd_msg_pub.add;
252 x_return_status := fnd_api.g_ret_sts_unexp_error;
253 RAISE fnd_api.g_exc_unexpected_error;
254 END IF;
255 end if;
256 -- Invoke validation procedures
257
258 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
259
260 Validate_DEBRIEF_DATE(
261 p_init_msg_list => FND_API.G_FALSE,
262 p_validation_mode => G_CREATE,
263 p_DEBRIEF_DATE => P_DEBRIEF_Rec.DEBRIEF_DATE,
264 x_return_status => x_return_status,
265 x_msg_count => x_msg_count,
266 x_msg_data => x_msg_data);
267 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
268 x_return_status := fnd_api.g_ret_sts_unexp_error;
269 RAISE fnd_api.g_exc_unexpected_error;
270 END IF;
271
272 Validate_Task_Assignment_Id(
273 p_init_msg_list => FND_API.G_FALSE,
274 p_validation_mode => G_CREATE,
275 p_Task_Assignment_Id => P_DEBRIEF_Rec.Task_Assignment_Id,
276 x_return_status => x_return_status,
277 x_msg_count => x_msg_count,
278 x_msg_data => x_msg_data);
279 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
280 x_return_status := fnd_api.g_ret_sts_unexp_error;
281 RAISE fnd_api.g_exc_unexpected_error;
282 END IF;
283 END IF;
284 -- Debug Message
285 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
286 THEN
287 FND_MESSAGE.Set_Name('CSF', l_api_name);
288 FND_MESSAGE.Set_Token ('INFO', G_PKG_NAME, FALSE);
289 FND_MSG_PUB.Add;
290 END IF;
291
292 -- Invoke table handler(debrief_Insert_Row)
293
294
295 l_debrief_header_id := p_debrief_rec.debrief_header_id;
296 l_debrief_number := p_debrief_rec.debrief_number;
297
298 if (l_debrief_header_id<>FND_API.G_MISS_NUM) and (l_debrief_header_id is not NULL) then
299 begin
300 select 1 into x
301 from CSF_DEBRIEF_HEADERS
302 where DEBRIEF_HEADER_ID = l_DEBRIEF_HEADER_ID ;
303
304 fnd_message.set_name('CSF', 'CSF_DEBRIEF_INVALID_HEADER');
305 fnd_msg_pub.add;
306 x_return_status := fnd_api.g_ret_sts_unexp_error;
307 RAISE fnd_api.g_exc_unexpected_error;
308 exception
309 when no_data_found then
310 null ;
311 when too_many_rows then
312 fnd_message.set_name('CSF', 'CSF_DEBRIEF_INVALID_HEADER');
313 fnd_msg_pub.add;
314 x_return_status := fnd_api.g_ret_sts_unexp_error;
315 RAISE fnd_api.g_exc_unexpected_error;
316 end ;
317
318
319 if (l_debrief_number <>FND_API.G_MISS_CHAR) and (l_debrief_number is not null) then
320 null;
321 else
322 SELECT CSF_DEBRIEF_HEADERS_S2.nextval
323 INTO l_debrief_number
324 FROM dual;
325 end if;
326 ELSE
327 SELECT CSF_DEBRIEF_HEADERS_S1.nextval
328 INTO l_debrief_header_id
329 FROM dual;
330 if (l_debrief_number <>FND_API.G_MISS_CHAR) and (l_debrief_number is not null) then
331 null;
332 else
333 SELECT CSF_DEBRIEF_HEADERS_S2.nextval
334 INTO l_debrief_number
335 FROM dual;
336 end if;
337 END IF;
338 X_DEBRIEF_HEADER_ID:=l_debrief_header_id;
339 validate_travel_times(p_debrief_rec.TRAVEL_START_TIME,
340 p_debrief_rec.TRAVEL_END_TIME,
341 p_debrief_rec.task_assignment_id,
342 fnd_api.g_false,
343 X_Return_Status,
344 X_Msg_Count,
345 X_Msg_Data);
346 if X_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
347 return;
348 END IF;
349
350 open c_task_assgin_object_version ;
351 fetch c_task_assgin_object_version INTO l_oject_version_number,
352 l_resource_type_code,
353 l_resource_id;
354 CLOSE c_task_assgin_object_version ;
355
356 l_unit_of_measure_for_hr := fnd_profile.value('CSF_UOM_HOURS');
357 IF (p_debrief_rec.TRAVEL_START_TIME IS NOT NULL AND p_debrief_rec.TRAVEL_START_TIME <> FND_API.G_MISS_DATE)
358 AND (p_debrief_rec.TRAVEL_END_TIME IS NOT NULL AND p_debrief_rec.TRAVEL_END_TIME <> FND_API.G_MISS_DATE)
359 THEN
360 l_actual_travel_duration := round ((p_debrief_rec.TRAVEL_END_TIME - p_debrief_rec.TRAVEL_START_TIME) * 24,4);
361 END IF;
362 if (p_debrief_rec.TRAVEL_DISTANCE_IN_KM IS NOT NULL AND p_debrief_rec.TRAVEL_DISTANCE_IN_KM<> FND_API.G_MISS_NUM) THEN
363 csf_task_assignments_pub.update_task_assignment(
364 p_api_version => 1.0,
365 x_return_status => x_return_status,
366 x_msg_count => x_msg_count,
367 x_msg_data => x_msg_Data,
368 p_task_assignment_id => p_debrief_rec.task_assignment_id,
369 p_object_version_number => l_oject_version_number,
370 p_actual_travel_distance => p_debrief_rec.TRAVEL_DISTANCE_IN_KM ,
371 p_actual_travel_duration => l_actual_travel_duration,
372 p_actual_travel_duration_uom => l_unit_of_measure_for_hr,
373 p_resource_type_code => l_resource_type_code,
374 p_resource_id => l_resource_id,
375 x_task_object_version_number => l_task_object_version,
376 x_task_status_id => l_task_status_id);
377 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
378 x_return_status := fnd_api.g_ret_sts_unexp_error;
379 RAISE fnd_api.g_exc_unexpected_error;
380 END IF;
381 END IF;
382 csf_debrief_headers_pkg.Insert_Row(
383 PX_DEBRIEF_HEADER_ID => l_debrief_header_id,
384 P_DEBRIEF_NUMBER => l_debrief_number,
385 P_DEBRIEF_DATE => p_debrief_rec.debrief_date,
386 P_DEBRIEF_STATUS_ID => p_debrief_rec.DEBRIEF_STATUS_ID,
387 P_TASK_ASSIGNMENT_ID => p_debrief_rec.TASK_ASSIGNMENT_ID,
388 P_CREATED_BY => nvl(p_debrief_rec.created_by,G_USER_ID),
389 P_CREATION_DATE => nvl(p_debrief_rec.creation_date,SYSDATE),
390 P_LAST_UPDATED_BY => nvl(p_debrief_rec.last_updated_by,G_USER_ID),
391 P_LAST_UPDATE_DATE => nvl(p_debrief_rec.last_update_date,SYSDATE),
392 P_LAST_UPDATE_LOGIN => nvl(p_debrief_rec.last_update_login,G_LOGIN_ID),
393 P_ATTRIBUTE1 => p_debrief_rec.ATTRIBUTE1 ,
394 P_ATTRIBUTE2 => p_debrief_rec.ATTRIBUTE2 ,
395 P_ATTRIBUTE3 => p_debrief_rec.ATTRIBUTE3 ,
396 P_ATTRIBUTE4 => p_debrief_rec.ATTRIBUTE4 ,
397 P_ATTRIBUTE5 => p_debrief_rec.ATTRIBUTE5 ,
398 P_ATTRIBUTE6 => p_debrief_rec.ATTRIBUTE6 ,
399 P_ATTRIBUTE7 => p_debrief_rec.ATTRIBUTE7 ,
400 P_ATTRIBUTE8 => p_debrief_rec.ATTRIBUTE8 ,
401 P_ATTRIBUTE9 => p_debrief_rec.ATTRIBUTE9 ,
402 P_ATTRIBUTE10 => p_debrief_rec.ATTRIBUTE10 ,
403 P_ATTRIBUTE11 => p_debrief_rec.ATTRIBUTE11 ,
404 P_ATTRIBUTE12 => p_debrief_rec.ATTRIBUTE12 ,
405 P_ATTRIBUTE13 => p_debrief_rec.ATTRIBUTE13 ,
406 P_ATTRIBUTE14 => p_debrief_rec.ATTRIBUTE14,
407 P_ATTRIBUTE15 => p_debrief_rec.ATTRIBUTE15,
408 P_ATTRIBUTE_CATEGORY => p_debrief_rec.ATTRIBUTE_CATEGORY,
409 p_object_version_number => p_debrief_rec.object_version_number,
410 p_TRAVEL_START_TIME =>p_debrief_rec.TRAVEL_START_TIME,
411 p_TRAVEL_END_TIME =>p_debrief_rec.TRAVEL_END_TIME,
412 p_TRAVEL_DISTANCE_IN_KM =>p_debrief_rec.TRAVEL_DISTANCE_IN_KM
413 );
414
415 --Virendra Singh 03/28/2000 commented out
416 -- create Interaction 04/06/2000
417 /*CSF_DEBRIEF_PVT.Create_Interaction (P_Api_Version_Number =>1.0,
418 P_Init_Msg_List =>FND_API.G_FALSE,
419 P_Commit =>FND_API.G_FALSE,
420 P_TASK_ASSIGNMENT_ID =>P_DEBRIEF_REC.TASK_ASSIGNMENT_ID,
421 P_DEBRIEF_HEADER_ID =>l_debrief_header_id,
422 P_MEDIA_ID =>29386,
423 P_ACTION_ID =>22,
424 X_RETURN_STATUS =>l_return_status,
425 X_Msg_Count =>X_MSG_COUNT,
426 X_Msg_Data =>X_MSG_DATA);
427
428 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
429 RAISE FND_API.G_EXC_ERROR;
430 END IF;
431 */
432 open c_source_object_type_code (p_debrief_rec.TASK_ASSIGNMENT_ID);
433 fetch c_source_object_type_code into l_source_object_type_code;
434 close c_source_object_type_code;
435
436 IF P_DEBRIEF_LINE_TBL.count > 0 then
437 CSF_DEBRIEF_PVT.Create_debrief_lines(
438 P_Api_Version_Number => 1.0 ,
439 P_Init_Msg_List => FND_API.G_FALSE,
440 P_Commit => FND_API.G_FALSE,
441 P_Upd_tskassgnstatus => NULL,
442 P_Task_Assignment_status => NULL,
443 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
444 P_DEBRIEF_line_tbl => p_DEBRIEF_line_tbl,
445 P_DEBRIEF_HEADER_ID => l_DEBRIEF_HEADER_ID,
446 P_SOURCE_OBJECT_TYPE_CODE => l_source_object_type_code,
447 X_Return_Status => l_Return_Status,
448 X_Msg_Count => X_Msg_Count,
449 X_Msg_Data => X_Msg_Data
450 );
451
452 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
453 RAISE FND_API.G_EXC_ERROR;
454 END IF;
455 END IF;
456
457
458 -- Standard check for p_commit
459 IF FND_API.to_Boolean( p_commit )
460 THEN
461 COMMIT WORK;
462 END IF;
463
464
465 -- Standard call to get message count and if count is 1, get message info.
466 FND_MSG_PUB.Count_And_Get
467 ( p_count => x_msg_count,
468 p_data => x_msg_data
469 );
470
471
472 EXCEPTION
473 WHEN FND_API.G_EXC_ERROR THEN
474 ROLLBACK TO CREATE_DEBRIEF_HEADER_PVT;
475 x_return_status := FND_API.G_RET_STS_ERROR;
476 FND_MSG_PUB.Count_And_Get (
477 P_COUNT => X_MSG_COUNT
478 ,P_DATA => X_MSG_DATA);
479
480 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
481 ROLLBACK TO CREATE_DEBRIEF_HEADER_PVT;
482
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
484 FND_MSG_PUB.Count_And_Get (
485 P_COUNT => X_MSG_COUNT
486 ,P_DATA => X_MSG_DATA);
487
488 WHEN OTHERS THEN
489 ROLLBACK TO CREATE_DEBRIEF_HEADER_PVT;
490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
491 IF FND_MSG_PUB.Check_Msg_Level
492 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
493 THEN
494 FND_MSG_PUB.Add_Exc_Msg (
495 G_PKG_NAME
496 ,L_API_NAME );
497 END IF;
498
499 FND_MSG_PUB.Count_And_Get (
500 P_COUNT => X_MSG_COUNT
501 ,P_DATA => X_MSG_DATA);
502
503 End Create_debrief;
504
505
506 -----------------------------------------------------------------------------------------------------------
507 PROCEDURE Update_debrief(
508 P_Api_Version_Number IN NUMBER,
509 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
510 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
511 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
512 P_DEBRIEF_Rec IN DEBRIEF_Rec_Type,
513 X_Return_Status OUT NOCOPY VARCHAR2,
514 X_Msg_Count OUT NOCOPY NUMBER,
515 X_Msg_Data OUT NOCOPY VARCHAR2
516 )
517 IS
518
519
520 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
521 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
522 l_api_name CONSTANT VARCHAR2(30) := 'Update_debrief';
523 l_api_version_number CONSTANT NUMBER := 1.0;
524
525 -- Local Variables
526
527 l_ref_DEBRIEF_rec CSF_DEBRIEF_PVT.DEBRIEF_Rec_Type;
528 l_tar_DEBRIEF_rec CSF_DEBRIEF_PVT.DEBRIEF_Rec_Type := p_debrief_rec;
529 l_rowid ROWID;
530 x_row_id varchar2(100) := null;
531 l_task_assignment_id number;
532 l_assignment_status_id number;
533 l_actual_start_date date;
534 l_actual_end_date date;
535 l_debrief_header_id number;
536 l_counter number;
537 l_Unit_Of_Measure_For_Hr varchar2(3);
538 l_oject_version_number NUMBER;
539 l_actual_travel_duration NUMBER;
540 l_resource_type_code varchar2(30) := null;
541 l_resource_id number := null;
542 l_task_object_version number := null;
543 l_task_status_id number := null;
544
545 cursor c_task_assgin_object_version is
546 select jta.object_version_number,
547 jta.resource_type_code,
548 jta.resource_id
549 from jtf_task_assignments jta
550 where jta.task_assignment_id = p_debrief_rec.TASK_ASSIGNMENT_ID;
551
552 BEGIN
553
554 -- Standard Start of API savepoint
555 SAVEPOINT UPDATE_DEBRIEF_PVT;
556 -- Standard call to check for call compatibility.
557
558 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
559 p_api_version_number,
560 l_api_name,
561 G_PKG_NAME)
562 THEN
563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
564 END IF;
565
566
567 -- Initialize message list if p_init_msg_list is set to TRUE.
568 IF FND_API.to_Boolean( p_init_msg_list )
569 THEN
570 FND_MSG_PUB.initialize;
571 END IF;
572
573 -- Initialize API return status to SUCCESS
574 x_return_status := FND_API.G_RET_STS_SUCCESS;
575
576 l_debrief_header_id:=l_tar_DEBRIEF_rec.Debrief_header_id;
577 -- Virendra Singh 03/27/2000 check whether Debrief_Header_ID is not null or missging
578 if (l_debrief_header_id =FND_API.G_MISS_NUM) or (l_debrief_header_id is NULL ) then
579 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
580 THEN
581 fnd_message.set_name('CSF', 'CSF_DEBRIEF_MISSING_HEADER');
582 fnd_msg_pub.add;
583 x_return_status := fnd_api.g_ret_sts_unexp_error;
584 RAISE fnd_api.g_exc_unexpected_error;
585 END IF;
586 end if;
587 -- Invoke validation procedures
588
589
590 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
591
592 IF (l_tar_debrief_rec.DEBRIEF_DATE <> FND_API.G_MISS_DATE) then
593 Validate_DEBRIEF_DATE(
594 p_init_msg_list => FND_API.G_FALSE,
595 p_validation_mode => G_CREATE,
596 p_DEBRIEF_DATE => P_DEBRIEF_Rec.DEBRIEF_DATE,
597 x_return_status => x_return_status,
598 x_msg_count => x_msg_count,
599 x_msg_data => x_msg_data);
600 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
601 x_return_status := fnd_api.g_ret_sts_unexp_error;
602 RAISE fnd_api.g_exc_unexpected_error;
603 END IF;
604 END If;
605
606 IF (l_tar_DEBRIEF_rec.task_assignment_id <>FND_API.G_MISS_NUM) then
607 Validate_Task_Assignment_Id(
608 p_init_msg_list => FND_API.G_FALSE,
609 p_validation_mode => G_CREATE,
610 p_Task_Assignment_Id => P_DEBRIEF_Rec.Task_Assignment_Id,
611 x_return_status => x_return_status,
612 x_msg_count => x_msg_count,
613 x_msg_data => x_msg_data);
614 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
615 x_return_status := fnd_api.g_ret_sts_unexp_error;
616 RAISE fnd_api.g_exc_unexpected_error;
617 END IF;
618 END IF;
619 END IF;
620
621
622 validate_travel_times(p_debrief_rec.TRAVEL_START_TIME,
623 p_debrief_rec.TRAVEL_END_TIME,
624 p_debrief_rec.task_assignment_id,
625 fnd_api.g_false,
626 X_Return_Status,
627 X_Msg_Count,
628 X_Msg_Data);
629 if X_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
630 return;
631 END IF;
632
633 open c_task_assgin_object_version ;
634 fetch c_task_assgin_object_version INTO l_oject_version_number,
635 l_resource_type_code,
636 l_resource_id;
637 CLOSE c_task_assgin_object_version ;
638
639 l_actual_travel_duration := FND_API.G_MISS_NUM;
640 IF (p_debrief_rec.TRAVEL_START_TIME IS NOT NULL AND p_debrief_rec.TRAVEL_START_TIME <> FND_API.G_MISS_DATE)
641 AND (p_debrief_rec.TRAVEL_END_TIME IS NOT NULL AND p_debrief_rec.TRAVEL_END_TIME <> FND_API.G_MISS_DATE)
642 THEN
643 l_actual_travel_duration := round ((p_debrief_rec.TRAVEL_END_TIME - p_debrief_rec.TRAVEL_START_TIME) * 24,4);
644 l_unit_of_measure_for_hr := fnd_profile.value('CSF_UOM_HOURS');
645 END IF;
646
647
648 if (p_debrief_rec.TRAVEL_DISTANCE_IN_KM IS NOT NULL AND p_debrief_rec.TRAVEL_DISTANCE_IN_KM<> FND_API.G_MISS_NUM)
649 OR ( l_actual_travel_duration IS NOT NULL AND l_actual_travel_duration <> FND_API.G_MISS_NUM ) THEN
650 csf_task_assignments_pub.update_task_assignment(
651 p_api_version => 1.0,
652 x_return_status => x_return_status,
653 x_msg_count => x_msg_count,
654 x_msg_data => x_msg_Data,
655 p_task_assignment_id => p_debrief_rec.task_assignment_id,
656 p_object_version_number => l_oject_version_number,
657 p_actual_travel_distance => p_debrief_rec.TRAVEL_DISTANCE_IN_KM ,
658 p_actual_travel_duration => l_actual_travel_duration,
659 p_actual_travel_duration_uom => l_unit_of_measure_for_hr,
660 p_resource_type_code => l_resource_type_code,
661 p_resource_id => l_resource_id,
662 x_task_object_version_number => l_task_object_version,
663 x_task_status_id => l_task_status_id);
664 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
665 x_return_status := fnd_api.g_ret_sts_unexp_error;
666 RAISE fnd_api.g_exc_unexpected_error;
667 END IF;
668 END IF;
669
670
671 CSF_DEBRIEF_HEADERS_PKG.Update_Row(
672 P_DEBRIEF_HEADER_ID => p_debrief_rec.DEBRIEF_HEADER_ID,
673 P_DEBRIEF_NUMBER => p_debrief_rec.DEBRIEF_NUMBER ,
674 P_DEBRIEF_DATE => p_debrief_rec.DEBRIEF_DATE,
675 P_DEBRIEF_STATUS_ID => p_debrief_rec.DEBRIEF_STATUS_ID ,
676 P_TASK_ASSIGNMENT_ID => p_debrief_rec.TASK_ASSIGNMENT_ID,
677 P_CREATED_BY => p_debrief_rec.CREATED_BY,
678 P_CREATION_DATE => p_debrief_rec.CREATION_DATE,
679 P_LAST_UPDATED_BY => nvl(p_debrief_rec.last_updated_by,g_user_id),
680 P_LAST_UPDATE_DATE => nvl(p_debrief_Rec.last_update_date,sysdate),
681 P_LAST_UPDATE_LOGIN => nvl(p_debrief_rec.last_update_login,g_login_id),
682 P_ATTRIBUTE1 => p_debrief_rec.ATTRIBUTE1 ,
683 P_ATTRIBUTE2 => p_debrief_rec.ATTRIBUTE2 ,
684 P_ATTRIBUTE3 => p_debrief_rec.ATTRIBUTE3 ,
685 P_ATTRIBUTE4 => p_debrief_rec.ATTRIBUTE4 ,
686 P_ATTRIBUTE5 => p_debrief_rec.ATTRIBUTE5 ,
687 P_ATTRIBUTE6 => p_debrief_rec.ATTRIBUTE6 ,
688 P_ATTRIBUTE7 => p_debrief_rec.ATTRIBUTE7 ,
689 P_ATTRIBUTE8 => p_debrief_rec.ATTRIBUTE8 ,
690 P_ATTRIBUTE9 => p_debrief_rec.ATTRIBUTE9 ,
691 P_ATTRIBUTE10 => p_debrief_rec.ATTRIBUTE10 ,
692 P_ATTRIBUTE11 => p_debrief_rec.ATTRIBUTE11 ,
693 P_ATTRIBUTE12 => p_debrief_rec.ATTRIBUTE12 ,
694 P_ATTRIBUTE13 => p_debrief_rec.ATTRIBUTE13 ,
695 P_ATTRIBUTE14 => p_debrief_rec.ATTRIBUTE14,
696 P_ATTRIBUTE15 => p_debrief_rec.ATTRIBUTE15,
697 P_ATTRIBUTE_CATEGORY => p_debrief_rec.ATTRIBUTE_CATEGORY,
698 p_object_version_number => p_debrief_rec.object_version_number,
699 p_TRAVEL_START_TIME =>p_debrief_rec.TRAVEL_START_TIME,
700 p_TRAVEL_END_TIME =>p_debrief_rec.TRAVEL_END_TIME,
701 p_TRAVEL_DISTANCE_IN_KM =>p_debrief_rec.TRAVEL_DISTANCE_IN_KM);
702
703 --
704 -- End of API body.
705 --
706
707 -- Standard check for p_commit
708 IF FND_API.to_Boolean( p_commit )
709 THEN
710 COMMIT WORK;
711 END IF;
712
713 -- Standard call to get message count and if count is 1, get message info.
714 FND_MSG_PUB.Count_And_Get
715 ( p_count => x_msg_count,
716 p_data => x_msg_data
717 );
718
719 EXCEPTION
720 WHEN FND_API.G_EXC_ERROR THEN
721 ROLLBACK TO UPDATE_DEBRIEF_PVT;
722 x_return_status := FND_API.G_RET_STS_ERROR;
723 FND_MSG_PUB.Count_And_Get (
724 P_COUNT => X_MSG_COUNT
725 ,P_DATA => X_MSG_DATA);
726
727 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
728 ROLLBACK TO UPDATE_DEBRIEF_PVT;
729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
730 FND_MSG_PUB.Count_And_Get (
731 P_COUNT => X_MSG_COUNT
732 ,P_DATA => X_MSG_DATA);
733
734 WHEN OTHERS THEN
735 ROLLBACK TO UPDATE_DEBRIEF_PVT;
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737 IF FND_MSG_PUB.Check_Msg_Level
738 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
739 THEN
740 FND_MSG_PUB.Add_Exc_Msg (
741 G_PKG_NAME
742 ,L_API_NAME );
743 END IF;
744
745 FND_MSG_PUB.Count_And_Get (
746 P_COUNT => X_MSG_COUNT
747 ,P_DATA => X_MSG_DATA);
748
749 End Update_debrief;
750 -- *******************
751
752 -- ********************* Worked till here
753
754 -- Lead Lines Starts from here
755
756 PROCEDURE Validate_Task_Assignment_ID (
757 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
758 P_Validation_mode IN VARCHAR2,
759 P_TASK_Assignment_ID IN NUMBER,
760 X_Return_Status OUT NOCOPY VARCHAR2,
761 X_Msg_Count OUT NOCOPY NUMBER,
762 X_Msg_Data OUT NOCOPY VARCHAR2
763 )
764 IS
765 l_api_name varchar2(30) := 'Create Debrief' ;
766 cursor c is
767 select 1
768 from JTF_TASK_ASSIGNMENTS
769 where TASK_ASSIGNMENT_ID=P_TASK_ASSIGNMENT_ID;
770
771 l_dummy number;
772 BEGIN
773 -- Initialize message list if p_init_msg_list is set to TRUE.
774 IF FND_API.to_Boolean( p_init_msg_list )
775 THEN
776 FND_MSG_PUB.initialize;
777 END IF;
778 -- Initialize API return status to SUCCESS
779 x_return_status := FND_API.G_RET_STS_SUCCESS;
780
781 IF (p_task_assignment_id is NULL OR p_task_assignment_id = FND_API.G_MISS_NUM) THEN
782 x_return_status := FND_API.G_RET_STS_ERROR;
783 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
784 THEN
785 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_INVALID_TA_ID');
786 FND_MSG_PUB.ADD;
787 END IF;
788 ELSE
789 open c;
790 fetch c into l_dummy;
791 if c%notfound then
792 close c;
793 x_return_status := FND_API.G_RET_STS_ERROR;
794 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
795 THEN
796 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_INVALID_TA_ID');
797 FND_MSG_PUB.ADD;
798 END IF;
799 else
800 close c;
801 end if;
802 END IF;
803
804 -- Standard call to get message count and if count is 1, get message info.
805 FND_MSG_PUB.Count_And_Get
806 ( p_count => x_msg_count,
807 p_data => x_msg_data
808 );
809
810 END Validate_Task_assignment_ID;
811
812
813 PROCEDURE Validate_Debrief_Date (
814 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
815 P_Validation_mode IN VARCHAR2,
816 P_Debrief_Date IN DATE,
817 X_Return_Status OUT NOCOPY VARCHAR2,
818 X_Msg_Count OUT NOCOPY NUMBER,
819 X_Msg_Data OUT NOCOPY VARCHAR2
820 )
821 IS
822 l_api_name varchar2(30) := 'Create Debrief' ;
823 BEGIN
824 -- Initialize message list if p_init_msg_list is set to TRUE.
825 IF FND_API.to_Boolean( p_init_msg_list )
826 THEN
827 FND_MSG_PUB.initialize;
828 END IF;
829
830 -- Initialize API return status to SUCCESS
831 x_return_status := FND_API.G_RET_STS_SUCCESS;
832 IF (p_debrief_date is NULL OR p_debrief_date = FND_API.G_MISS_DATE) THEN
833 x_return_status := FND_API.G_RET_STS_ERROR;
834 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
835 THEN
836 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_DEBRIEF_DATE');
837 FND_MSG_PUB.ADD;
838 END IF;
839 END IF;
840
841 -- Standard call to get message count and if count is 1, get message info.
842 FND_MSG_PUB.Count_And_Get
843 ( p_count => x_msg_count,
844 p_data => x_msg_data
845 );
846 END Validate_Debrief_Date;
847
848 Function debrief_type(p_debrief_line_rec debrief_line_rec_type)
849 return varchar2 is
850
851 l_debrief_type varchar2(3);
852 l_valid_org number := fnd_profile.value('CS_INV_VALIDATION_ORG');
853
854 cursor c_debrief_type is
855 select billing_category
856 from cs_billing_type_categories cbtc,
857 mtl_system_items_b msib
858 where inventory_item_id = p_debrief_line_rec.inventory_item_id
859 and organization_id = l_valid_org -- changed for bug 4259770
860 -- nvl(p_debrief_line_rec.issuing_inventory_org_id, p_debrief_line_rec.receiving_inventory_org_id)
861 and material_billable_flag = billing_type;
862
863 begin
864
865 open c_debrief_type;
866 fetch c_debrief_type into l_debrief_type;
867 close c_debrief_type;
868
869 if nvl(p_debrief_line_rec.inventory_item_id,fnd_api.g_miss_num) = fnd_api.g_miss_num then
870 l_debrief_type := 'L';
871 end if;
872
873 return l_debrief_type;
874
875 end;
876
877 Function IS_DEBRIEF_HEADER_REC_MISSING(P_DEBRIEF_REC DEBRIEF_REC_TYPE) Return BOOLEAN is
878 BEGIN
879 if P_DEBRIEF_REC.DEBRIEF_HEADER_ID <> FND_API.G_MISS_NUM then
880 Return FALSE;
881 elsif P_DEBRIEF_REC.DEBRIEF_NUMBER <>FND_API.G_MISS_CHAR then
882 Return FALSE;
883 elsif P_DEBRIEF_REC.DEBRIEF_DATE <>FND_API.G_MISS_DATE then
884 Return FALSE;
885 elsif P_DEBRIEF_REC.DEBRIEF_STATUS_ID <>FND_API.G_MISS_NUM then
886 Return FALSE;
887 elsif P_DEBRIEF_REC.TASK_ASSIGNMENT_ID <>FND_API.G_MISS_NUM then
888 Return FALSE;
889 elsif P_DEBRIEF_REC.CREATED_BY <>FND_API.G_MISS_NUM then
890 Return FALSE;
891 elsif P_DEBRIEF_REC.CREATION_DATE <>FND_API.G_MISS_DATE then
892 Return FALSE;
893 elsif P_DEBRIEF_REC.LAST_UPDATED_BY <>FND_API.G_MISS_NUM then
894 Return FALSE;
895 elsif P_DEBRIEF_REC.LAST_UPDATE_DATE <>FND_API.G_MISS_DATE then
896 Return FALSE;
897 elsif P_DEBRIEF_REC.LAST_UPDATE_LOGIN <>FND_API.G_MISS_NUM then
898 Return FALSE;
899 elsif P_DEBRIEF_REC.ATTRIBUTE1 <>FND_API.G_MISS_CHAR then
900 Return FALSE;
901 elsif P_DEBRIEF_REC.ATTRIBUTE2 <>FND_API.G_MISS_CHAR then
902 Return FALSE;
903 elsif P_DEBRIEF_REC.ATTRIBUTE3 <>FND_API.G_MISS_CHAR then
904 Return FALSE;
905 elsif P_DEBRIEF_REC.ATTRIBUTE4 <>FND_API.G_MISS_CHAR then
906 Return FALSE;
907 elsif P_DEBRIEF_REC.ATTRIBUTE5 <>FND_API.G_MISS_CHAR then
908 Return FALSE;
909 elsif P_DEBRIEF_REC.ATTRIBUTE6 <>FND_API.G_MISS_CHAR then
910 Return FALSE;
911 elsif P_DEBRIEF_REC.ATTRIBUTE7 <>FND_API.G_MISS_CHAR then
912 Return FALSE;
913 elsif P_DEBRIEF_REC.ATTRIBUTE8 <>FND_API.G_MISS_CHAR then
914 Return FALSE;
915 elsif P_DEBRIEF_REC.ATTRIBUTE9 <>FND_API.G_MISS_CHAR then
916 Return FALSE;
917 elsif P_DEBRIEF_REC.ATTRIBUTE10 <>FND_API.G_MISS_CHAR then
918 Return FALSE;
919 elsif P_DEBRIEF_REC.ATTRIBUTE11 <>FND_API.G_MISS_CHAR then
920 Return FALSE;
921 elsif P_DEBRIEF_REC.ATTRIBUTE12 <>FND_API.G_MISS_CHAR then
922 Return FALSE;
923 elsif P_DEBRIEF_REC.ATTRIBUTE13 <>FND_API.G_MISS_CHAR then
924 Return FALSE;
925 elsif P_DEBRIEF_REC.ATTRIBUTE14 <>FND_API.G_MISS_CHAR then
926 Return FALSE;
927 elsif P_DEBRIEF_REC.ATTRIBUTE15 <>FND_API.G_MISS_CHAR then
928 Return FALSE;
929 elsif P_DEBRIEF_REC.ATTRIBUTE_CATEGORY <>FND_API.G_MISS_CHAR then
930 Return FALSE;
931 else
932 return TRUE;
933 end if;
934 End Is_DEBRIEF_HEADER_REC_MISSING;
935
936 PROCEDURE Create_debrief_lines(
937 P_Api_Version_Number IN NUMBER,
938 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
939 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
940 P_Upd_tskassgnstatus IN VARCHAR2 ,
941 P_Task_Assignment_status IN VARCHAR2 ,
942 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
943 P_DEBRIEF_LINE_tbl IN DEBRIEF_LINE_tbl_type,
944 -- DEFAULT G_MISS_DEBRIEF_LINE_tbl,
945 P_DEBRIEF_HEADER_ID IN NUMBER ,
946 P_SOURCE_OBJECT_TYPE_CODE IN VARCHAR2,
947 X_Return_Status OUT NOCOPY VARCHAR2,
948 X_Msg_Count OUT NOCOPY NUMBER,
949 X_Msg_Data OUT NOCOPY VARCHAR2
950 )
951 IS
952 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
953 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
954 l_api_name CONSTANT VARCHAR2(30) := 'Create_debrief_lines';
955 l_api_version_number CONSTANT NUMBER := 1.0;
956 x_row_id varchar2(100) := 100;
957 l_request_id number;
958 l_call_cp varchar2(1);
959 l_count NUMBER := p_debrief_line_tbl.count;
960 p_DEBRIEF_LINE_rec debrief_line_rec_type;
961 X_DEBRIEF_LINE_ID Number;
962 z_debrief_line_number number;
963 z_start_date date;
964 z_end_date date;
965 z_debrief_header_id number;
966 z_task_assignment_id number;
967 z_resource_id number;
968 z_location mdsys.sdo_geometry;
969 z_debrief_line_id number;
970 z_object_version_number NUMBER;
971 l_object_version_number number;
972 l_task_id number;
973 l_task_assignment_id number;
974 l_assignment_status_id number;
975 closed_assignment_count number;
976 l_debrief_line_id number;
977 x number;
978 l_business_process_id number;
979 l_billing_type cs_txn_billing_types.billing_type%type;
980 l_task_object_version_number number;
981 l_task_status_id number;
982 l_task_status_name varchar2(200);
983 xx_labor_start_date date;
984 xx_labor_end_date date;
985 l_cp_status_id number;
986 l_cp_status varchar2(30);
987 l_return_reason_code varchar2(30) ;
988 l_hr_uom varchar2(100) := fnd_profile.value('CSF_UOM_HOURS');
989 l_quantity number;
990 l_line_order_category_code varchar2(30);
991 l_transaction_type_id number;
992 l_debrief_type varchar2(1);
993 l_resource_id number;
994 l_resource_type varchar2(240);
995 l_task_number number;
996 l_debrief_number number;
997
998 cursor c_status(p_debrief_header_id number) is
999 select greatest(nvl(rejected_flag,'N'),
1000 nvl(completed_flag,'N'),
1001 nvl(closed_flag,'N'),
1002 nvl(cancelled_flag,'N'))
1003 from jtf_task_statuses_b jtsb,
1004 jtf_task_assignments jta,
1005 csf_debrief_headers cdh
1006 where cdh.debrief_header_id = p_debrief_header_id
1007 and jta.task_assignment_id = cdh.task_assignment_id
1008 AND jtsb.task_status_id = jta.assignment_status_id;
1009
1010 cursor c_cp_status(p_transaction_type_id number) is
1011 select ctst.src_status_id
1012 from csi_txn_sub_types ctst ,
1013 cs_transaction_types_vl cttv
1014 where ctst.cs_transaction_type_id = cttv.transaction_type_id
1015 AND cttv.transaction_type_id = p_transaction_type_id;
1016
1017 cursor c_cp_status_notnull (p_cp_status varchar2) is
1018 select instance_status_id
1019 from csi_instance_statuses
1020 where name = p_cp_status;
1021
1022 cursor labor_uom(p_inventory_item_id number) is
1023 select primary_uom_code
1024 from mtl_system_items_b
1025 where inventory_item_id = p_inventory_item_id;
1026
1027 cursor c_txn_billing_type_id (p_txn_billing_type_id number) is
1028 select ctbt.billing_type,
1029 cttv.line_order_category_code,
1030 cttv.transaction_type_id
1031 from cs_transaction_types_vl cttv,
1032 cs_txn_billing_types ctbt
1033 where ctbt.txn_billing_type_id = p_txn_billing_type_id
1034 and ctbt.transaction_type_id = cttv.transaction_type_id;
1035
1036 cursor c_transaction_type_id (p_transaction_type_id number,
1037 p_inventory_item_id number) is
1038 select ctbt.billing_type,
1039 cttv.line_order_category_code
1040 from cs_transaction_types_vl cttv,
1041 cs_txn_billing_types ctbt,
1042 mtl_system_items_b_kfv msibk
1043 where cttv.transaction_type_id = p_transaction_type_id
1044 and cttv.transaction_type_id = ctbt.transaction_type_id
1045 and ctbt.billing_type = msibk.material_billable_flag
1046 and msibk.inventory_item_id = p_inventory_item_id;
1047
1048
1049 BEGIN
1050 --dbms_output.put_line('DBF PVT : BEGIN...');
1051 z_debrief_line_number := p_debrief_line_tbl(1).debrief_line_number ;
1052
1053 -- Standard Start of API savepoint
1054
1055 SAVEPOINT CREATE_DEBRIEF_LINE_PVT;
1056
1057 -- Standard call to check for call compatibility.
1058 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1059 p_api_version_number,
1060 l_api_name,
1061 G_PKG_NAME)
1062 THEN
1063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1064 END IF;
1065
1066
1067 -- Initialize message list if p_init_msg_list is set to TRUE.
1068
1069 IF FND_API.to_Boolean( p_init_msg_list )
1070 THEN
1071 FND_MSG_PUB.initialize;
1072 END IF;
1073
1074 -- Initialize API return status to SUCCESS
1075 x_return_status := FND_API.G_RET_STS_SUCCESS;
1076 --dbms_output.put_line('LOOP Starts...');
1077 FOR l_curr_row in 1..l_count
1078 LOOP
1079 BEGIN
1080 z_debrief_line_number := p_debrief_line_tbl(l_curr_row).debrief_line_number ;
1081 z_debrief_line_id := p_debrief_line_tbl(l_curr_row).debrief_line_id ;
1082 z_debrief_header_id := p_debrief_line_tbl(l_curr_row).debrief_header_id ;
1083 z_start_date := p_debrief_line_tbl(l_curr_row).labor_start_date;
1084 z_end_date := p_debrief_line_tbl(l_curr_row).labor_end_date;
1085
1086 p_debrief_line_rec.DEBRIEF_LINE_ID := p_debrief_line_tbl(l_curr_row).DEBRIEF_LINE_ID ;
1087 p_debrief_line_rec.DEBRIEF_HEADER_ID := p_debrief_line_tbl(l_curr_row).DEBRIEF_HEADER_ID;
1088 p_debrief_line_rec.DEBRIEF_LINE_NUMBER := p_debrief_line_tbl(l_curr_row).DEBRIEF_LINE_NUMBER;
1089 log('test', 'org value=' || p_debrief_line_tbl(l_curr_row).SERVICE_DATE);
1090 p_debrief_line_rec.SERVICE_DATE := p_debrief_line_tbl(l_curr_row).SERVICE_DATE ;
1091 log('test', 'p_debrief_line_rec.SERVICE_DATE=' || p_debrief_line_rec.SERVICE_DATE);
1092 p_debrief_line_rec.BUSINESS_PROCESS_ID := p_debrief_line_tbl(l_curr_row).BUSINESS_PROCESS_ID ;
1093 p_debrief_line_rec.TXN_BILLING_TYPE_ID := p_debrief_line_tbl(l_curr_row).TXN_BILLING_TYPE_ID ;
1094 p_debrief_line_rec.INVENTORY_ITEM_ID := p_debrief_line_tbl(l_curr_row).INVENTORY_ITEM_ID ;
1095 p_debrief_line_rec.INSTANCE_ID := p_debrief_line_tbl(l_curr_row).INSTANCE_ID ;
1096 p_debrief_line_rec.ISSUING_INVENTORY_ORG_ID := p_debrief_line_tbl(l_curr_row).ISSUING_INVENTORY_ORG_ID ;
1097 p_debrief_line_rec.RECEIVING_INVENTORY_ORG_ID := p_debrief_line_tbl(l_curr_row).RECEIVING_INVENTORY_ORG_ID ;
1098 p_debrief_line_rec.ISSUING_SUB_INVENTORY_CODE := p_debrief_line_tbl(l_curr_row).ISSUING_SUB_INVENTORY_CODE ;
1099 p_debrief_line_rec.RECEIVING_SUB_INVENTORY_CODE := p_debrief_line_tbl(l_curr_row).RECEIVING_SUB_INVENTORY_CODE ;
1100 p_debrief_line_rec.ISSUING_LOCATOR_ID := p_debrief_line_tbl(l_curr_row).ISSUING_LOCATOR_ID ;
1101 p_debrief_line_rec.RECEIVING_LOCATOR_ID := p_debrief_line_tbl(l_curr_row).RECEIVING_LOCATOR_ID ;
1102 p_debrief_line_rec.PARENT_PRODUCT_ID := p_debrief_line_tbl(l_curr_row).PARENT_PRODUCT_ID ;
1103 p_debrief_line_rec.REMOVED_PRODUCT_ID := p_debrief_line_tbl(l_curr_row).REMOVED_PRODUCT_ID ;
1104 p_debrief_line_rec.STATUS_OF_RECEIVED_PART := p_debrief_line_tbl(l_curr_row).STATUS_OF_RECEIVED_PART ;
1105 p_debrief_line_rec.ITEM_SERIAL_NUMBER := p_debrief_line_tbl(l_curr_row).ITEM_SERIAL_NUMBER ;
1106 p_debrief_line_rec.ITEM_REVISION := p_debrief_line_tbl(l_curr_row).ITEM_REVISION ;
1107 p_debrief_line_rec.ITEM_LOTNUMBER := p_debrief_line_tbl(l_curr_row).ITEM_LOTNUMBER ;
1108 p_debrief_line_rec.UOM_CODE := p_debrief_line_tbl(l_curr_row).UOM_CODE ;
1109 p_debrief_line_rec.QUANTITY := p_debrief_line_tbl(l_curr_row).QUANTITY ;
1110 p_debrief_line_rec.RMA_HEADER_ID := p_debrief_line_tbl(l_curr_row).RMA_HEADER_ID ;
1111 p_debrief_line_rec.DISPOSITION_CODE := p_debrief_line_tbl(l_curr_row).DISPOSITION_CODE ;
1112 p_debrief_line_rec.MATERIAL_REASON_CODE := p_debrief_line_tbl(l_curr_row).MATERIAL_REASON_CODE ;
1113 p_debrief_line_rec.LABOR_REASON_CODE := p_debrief_line_tbl(l_curr_row).LABOR_REASON_CODE ;
1114 p_debrief_line_rec.EXPENSE_REASON_CODE := p_debrief_line_tbl(l_curr_row).EXPENSE_REASON_CODE ;
1115 p_debrief_line_rec.LABOR_START_DATE := p_debrief_line_tbl(l_curr_row).LABOR_START_DATE ;
1116 p_debrief_line_rec.LABOR_END_DATE := p_debrief_line_tbl(l_curr_row).LABOR_END_DATE ;
1117 p_debrief_line_rec.STARTING_MILEAGE := p_debrief_line_tbl(l_curr_row).STARTING_MILEAGE ;
1118 p_debrief_line_rec.ENDING_MILEAGE := p_debrief_line_tbl(l_curr_row).ENDING_MILEAGE ;
1119 p_debrief_line_rec.EXPENSE_AMOUNT := p_debrief_line_tbl(l_curr_row).EXPENSE_AMOUNT ;
1120 p_debrief_line_rec.CURRENCY_CODE := p_debrief_line_tbl(l_curr_row).CURRENCY_CODE;
1121 p_debrief_line_rec.DEBRIEF_LINE_STATUS_ID := p_debrief_line_tbl(l_curr_row).DEBRIEF_LINE_STATUS_ID ;
1122 p_debrief_line_rec.CHANNEL_CODE := p_debrief_line_tbl(l_curr_row).CHANNEL_CODE ;
1123 p_debrief_line_rec.CHARGE_UPLOAD_STATUS := p_debrief_line_tbl(l_curr_row).CHARGE_UPLOAD_STATUS ;
1124 p_debrief_line_rec.CHARGE_UPLOAD_MSG_CODE := p_debrief_line_tbl(l_curr_row).CHARGE_UPLOAD_MSG_CODE ;
1125 p_debrief_line_rec.CHARGE_UPLOAD_MESSAGE := p_debrief_line_tbl(l_curr_row).CHARGE_UPLOAD_MESSAGE ;
1126 p_debrief_line_rec.IB_UPDATE_STATUS := p_debrief_line_tbl(l_curr_row).IB_UPDATE_STATUS ;
1127 p_debrief_line_rec.IB_UPDATE_MSG_CODE := p_debrief_line_tbl(l_curr_row).IB_UPDATE_MSG_CODE ;
1128 p_debrief_line_rec.IB_UPDATE_MESSAGE := p_debrief_line_tbl(l_curr_row).IB_UPDATE_MESSAGE ;
1129 p_debrief_line_rec.SPARE_UPDATE_STATUS := p_debrief_line_tbl(l_curr_row).SPARE_UPDATE_STATUS ;
1130 p_debrief_line_rec.SPARE_UPDATE_MSG_CODE := p_debrief_line_tbl(l_curr_row).SPARE_UPDATE_MSG_CODE ;
1131 p_debrief_line_rec.SPARE_UPDATE_MESSAGE := p_debrief_line_tbl(l_curr_row).SPARE_UPDATE_MESSAGE ;
1132 p_debrief_line_rec.LAST_UPDATE_DATE := nvl(p_debrief_line_tbl(l_curr_row).last_update_date,SYSDATE);
1133 p_debrief_line_rec.LAST_UPDATED_BY := nvl(p_debrief_line_tbl(l_curr_row).last_updated_by,G_USER_ID);
1134 p_debrief_line_rec.CREATION_DATE := nvl(p_debrief_line_tbl(l_curr_row).creation_date,SYSDATE);
1135 p_debrief_line_rec.CREATED_BY := nvl(p_debrief_line_tbl(l_curr_row).created_by,G_USER_ID);
1136 p_debrief_line_rec.LAST_UPDATE_LOGIN := nvl(p_debrief_line_tbl(l_curr_row).last_update_login,G_LOGIN_ID);
1137 p_debrief_line_rec.ATTRIBUTE_CATEGORY := p_debrief_line_tbl(l_curr_row).ATTRIBUTE_CATEGORY;
1138 p_debrief_line_rec.ATTRIBUTE1 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE1;
1139 p_debrief_line_rec.ATTRIBUTE2 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE2;
1140 p_debrief_line_rec.ATTRIBUTE3 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE3;
1141 p_debrief_line_rec.ATTRIBUTE4 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE4;
1142 p_debrief_line_rec.ATTRIBUTE5 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE5;
1143 p_debrief_line_rec.ATTRIBUTE6 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE6;
1144 p_debrief_line_rec.ATTRIBUTE7 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE7;
1145 p_debrief_line_rec.ATTRIBUTE8 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE8;
1146 p_debrief_line_rec.ATTRIBUTE9 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE9;
1147 p_debrief_line_rec.ATTRIBUTE10 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE10;
1148 p_debrief_line_rec.ATTRIBUTE11 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE11;
1149 p_debrief_line_rec.ATTRIBUTE12 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE12;
1150 p_debrief_line_rec.ATTRIBUTE13 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE13;
1151 p_debrief_line_rec.ATTRIBUTE14 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE14;
1152 p_debrief_line_rec.ATTRIBUTE15 := p_debrief_line_tbl(l_curr_row).ATTRIBUTE15;
1153 p_debrief_line_rec.TRANSACTION_TYPE_ID := P_debrief_line_tbl(l_curr_row).TRANSACTION_TYPE_ID;
1154 p_debrief_line_rec.RETURN_DATE := p_debrief_line_tbl(l_curr_row).RETURN_DATE ;
1155 p_debrief_line_rec.debrief_header_id := p_debrief_header_id;
1156
1157 --dbms_output.put_line('p_debrief_line_tbl(l_curr_row).usage_type :: '||p_debrief_line_tbl(l_curr_row).usage_type);
1158 p_debrief_line_rec.usage_type := p_debrief_line_tbl(l_curr_row).usage_type;
1159 p_debrief_line_rec.dest_organization_id := p_debrief_line_tbl(l_curr_row).dest_organization_id;
1160 p_debrief_line_rec.dest_subinventory_name := p_debrief_line_tbl(l_curr_row).dest_subinventory_name;
1161 p_debrief_line_rec.carrier_code := p_debrief_line_tbl(l_curr_row).carrier_code;
1162 p_debrief_line_rec.shipping_method := p_debrief_line_tbl(l_curr_row).shipping_method;
1163 p_debrief_line_rec.shipment_number := p_debrief_line_tbl(l_curr_row).shipment_number;
1164 p_debrief_line_rec.waybill := p_debrief_line_tbl(l_curr_row).waybill;
1165
1166 p_debrief_line_rec.expenditure_org_id := p_debrief_line_tbl(l_curr_row).expenditure_org_id;
1167 p_debrief_line_rec.project_id := p_debrief_line_tbl(l_curr_row).project_id;
1168 p_debrief_line_rec.project_task_id := p_debrief_line_tbl(l_curr_row).project_task_id;
1169 --dbms_output.put_line('Assigned new values in DBF PVT.');
1170
1171 l_debrief_type := debrief_type(p_debrief_line_rec);
1172
1173 if p_debrief_line_rec.issuing_inventory_org_id = fnd_api.g_miss_num then
1174 p_debrief_line_rec.issuing_inventory_org_id := null;
1175 end if;
1176 if p_debrief_line_rec.receiving_inventory_org_id = fnd_api.g_miss_num then
1177 p_debrief_line_rec.receiving_inventory_org_id := null;
1178 end if;
1179
1180 IF (p_DEBRIEF_LINE_REC.CHANNEL_CODE <> 'CSF_PORTAL') THEN
1181 --if transaction_type_id is passed, we will save that
1182 --if transaction_type_id is not passed => we will find it from txn_billing_type_id
1183
1184 --in both cases we have to find billing_type + line_order_category_code in order to know if we should default return reason or not.
1185 --dbms_output.put_line('<> CSF_PORTAL CODE !!');
1186 IF (p_debrief_line_tbl(l_curr_row).TRANSACTION_TYPE_ID is null)
1187 or (p_debrief_line_tbl(l_curr_row).TRANSACTION_TYPE_ID = fnd_api.g_miss_num)
1188 Then
1189 open c_txn_billing_type_id (p_debrief_line_rec.txn_billing_type_id);
1190 fetch c_txn_billing_type_id into l_billing_type, l_line_order_category_code, l_transaction_type_id;
1191 close c_txn_billing_type_id;
1192 p_debrief_line_rec.transaction_type_id := l_transaction_type_id;
1193 Else
1194 open c_transaction_type_id (p_debrief_line_rec.transaction_type_id, p_debrief_line_rec.inventory_item_id);
1195 fetch c_transaction_type_id into l_billing_type, l_line_order_category_code;
1196 close c_transaction_type_id;
1197 END IF;
1198 --dbms_output.put_line('1');
1199
1200 IF (p_debrief_line_tbl(l_curr_row).RETURN_REASON_CODE is null)
1201 or (p_debrief_line_tbl(l_curr_row).RETURN_REASON_CODE = fnd_api.g_miss_char) Then
1202 --------------------------------------------------------------
1203 if nvl(l_billing_type, fnd_api.g_miss_char) <>'M'
1204 Then l_return_reason_code := fnd_api.g_miss_char;
1205 else if l_line_order_category_code = 'RETURN'
1206 Then l_return_reason_code := fnd_profile.value('CSF_RETURN_REASON');
1207 Else l_return_reason_code := fnd_api.g_miss_char;
1208 End If;
1209 End If;
1210 --------------------------------------------------------------
1211 P_debrief_line_rec.RETURN_REASON_CODE := l_return_reason_code;
1212 else
1213 P_debrief_line_rec.RETURN_REASON_CODE := p_debrief_line_tbl(l_curr_row).RETURN_REASON_CODE;
1214 End If;
1215
1216 --dbms_output.put_line('2');
1217
1218
1219 l_cp_status := p_debrief_line_rec.STATUS_OF_RECEIVED_PART;
1220 if (l_cp_status is null or l_cp_status=FND_API.G_MISS_CHAR) then
1221 open c_cp_status(p_debrief_line_tbl(l_curr_row).TRANSACTION_TYPE_ID);
1222 fetch c_cp_status into l_cp_status_id;
1223 close c_cp_status;
1224 else
1225 open c_cp_status_notnull (l_cp_status);
1226 fetch c_cp_status_notnull into l_cp_status_id;
1227 close c_cp_status_notnull;
1228 END IF;
1229 --dbms_output.put_line('3');
1230 if l_debrief_type = 'E' and
1231 nvl(p_debrief_line_rec.expense_amount,fnd_api.g_miss_num) <> fnd_api.g_miss_num then
1232 p_debrief_line_rec.UOM_CODE := null;
1233 P_DEBRIEF_LINE_Rec.quantity := null;
1234 END IF;
1235 --dbms_output.put_line('4');
1236 if l_debrief_type = 'L' then
1237 -- Fixed bug 1286592
1238 if nvl(p_DEBRIEF_LINE_rec.labor_start_date,FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE then
1239 xx_labor_start_date := NULL;
1240 else
1241 xx_labor_start_date := p_DEBRIEF_LINE_rec.labor_start_date;
1242 -- If this is a labor line, ignore what is passed, get primary uom
1243 open labor_uom(p_debrief_line_rec.inventory_item_id);
1244 fetch labor_uom into p_debrief_line_rec.UOM_CODE;
1245 close labor_uom;
1246 end if;
1247 if nvl(p_DEBRIEF_LINE_rec.labor_end_date,FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE then
1248 xx_labor_end_date := NULL;
1249 else
1250 xx_labor_end_date := p_DEBRIEF_LINE_rec.labor_end_date;
1251 end if;
1252
1253 --we should calculate quantity based on start time and end time
1254
1255 if nvl(p_debrief_line_rec.uom_code,fnd_api.g_miss_char) = fnd_api.g_miss_char then
1256
1257 p_debrief_line_rec.uom_code := fnd_profile.value('CSF_LABOR_DEBRIEF_DEFAULT_UOM');
1258 end if;
1259 if nvl(p_DEBRIEF_LINE_rec.quantity,fnd_api.g_miss_num) = fnd_api.g_miss_num then
1260
1261 l_quantity := (p_debrief_line_rec.LABOR_END_DATE - p_debrief_line_rec.LABOR_START_DATE);
1262 if p_debrief_line_rec.UOM_CODE <> l_hr_uom and l_quantity is not null then
1263
1264 l_quantity := inv_convert.inv_um_convert(
1265 p_debrief_line_rec.INVENTORY_ITEM_ID,
1266 20,
1267 l_quantity*24,
1268 l_hr_uom,
1269 p_debrief_line_rec.UOM_CODE ,
1270 null,
1271 null);
1272 else
1273
1274 l_quantity := l_quantity * 24;
1275 end if;
1276 l_quantity := nvl(l_quantity,0);
1277 p_DEBRIEF_LINE_rec.QUANTITY := round(l_quantity,4);
1278 end if;
1279
1280 if nvl(p_DEBRIEF_LINE_rec.labor_start_date,fnd_api.g_miss_date) = fnd_api.g_miss_date then
1281
1282 if p_debrief_line_rec.UOM_CODE <> l_hr_uom then
1283
1284 l_quantity := inv_convert.inv_um_convert(
1285 p_debrief_line_rec.INVENTORY_ITEM_ID,
1286 20,
1287 p_DEBRIEF_LINE_rec.quantity,
1288 p_debrief_line_rec.UOM_CODE ,
1289 l_hr_uom,
1290 null,
1291 null);
1292 else
1293
1294 l_quantity := p_DEBRIEF_LINE_rec.quantity;
1295 end if;
1296 if nvl(p_DEBRIEF_LINE_rec.labor_end_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date then
1297
1298 xx_labor_start_date := p_DEBRIEF_LINE_rec.labor_end_date - l_quantity/24;
1299 end if;
1300 end if;
1301
1302 if nvl(p_DEBRIEF_LINE_rec.labor_end_date,fnd_api.g_miss_date) = fnd_api.g_miss_date or
1303 (nvl(p_debrief_line_rec.labor_start_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date and
1304 nvl(p_debrief_line_rec.quantity,fnd_api.g_miss_num) <> fnd_api.g_miss_num) then
1305
1306 if p_debrief_line_rec.UOM_CODE <> l_hr_uom then
1307
1308 l_quantity := inv_convert.inv_um_convert(
1309 p_debrief_line_rec.INVENTORY_ITEM_ID,
1310 20,
1311 p_DEBRIEF_LINE_rec.quantity,
1312 p_debrief_line_rec.UOM_CODE ,
1313 l_hr_uom,
1314 null,
1315 null);
1316 else
1317
1318 l_quantity := p_DEBRIEF_LINE_rec.quantity;
1319 end if;
1320
1321 if nvl(p_DEBRIEF_LINE_rec.labor_start_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date then
1322
1323 xx_labor_end_date := p_DEBRIEF_LINE_rec.labor_start_date + l_quantity/24;
1324 end if;
1325 end if;
1326 if nvl(p_debrief_line_rec.channel_code,'a') not in ('CSF_LAPTOP','CSF_PALM') then
1327 validate_start_end(xx_labor_start_date,
1328 xx_labor_end_date,
1329 p_debrief_line_rec.debrief_header_id,
1330 fnd_api.g_false,
1331 X_Return_Status,
1332 X_Msg_Count,
1333 X_Msg_Data);
1334 if x_return_status <> fnd_api.g_ret_sts_success then
1335 x_return_status := fnd_api.g_ret_sts_unexp_error;
1336 raise fnd_api.g_exc_unexpected_error;
1337 end if;
1338 end if;
1339 END IF;
1340 --dbms_output.put_line('5');
1341
1342 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL) THEN
1343
1344 if l_debrief_type = 'M' then
1345 validate_subinventory_code(
1346 p_init_msg_list => fnd_api.g_false,
1347 p_organization_id => nvl(p_debrief_line_rec.issuing_inventory_org_id,p_debrief_line_rec.receiving_inventory_org_id),
1348 p_subinventory_code => nvl(p_debrief_line_rec.issuing_sub_inventory_code,p_debrief_line_rec.receiving_sub_inventory_code),
1349 x_return_status => x_return_status,
1350 x_msg_count => x_msg_count,
1351 x_msg_data => x_msg_data);
1352
1353 if x_return_status <> fnd_api.g_ret_sts_success then
1354 x_return_status := fnd_api.g_ret_sts_unexp_error;
1355 raise fnd_api.g_exc_unexpected_error;
1356 end if;
1357 END IF;
1358 --dbms_output.put_line('6');
1359 log('test', 'before calling Validate_Service_Date p_debrief_line_rec.Service_Date=' || p_debrief_line_rec.Service_Date);
1360
1361 /* for testing only.. remove this comment after done
1362
1363 Validate_Service_Date (
1364 P_Init_Msg_List =>FND_API.G_FALSE,
1365 P_Service_Date =>p_debrief_line_rec.Service_Date,
1366 X_Return_Status =>x_return_status,
1367 X_Msg_Count =>x_msg_count,
1368 X_Msg_Data =>x_msg_data);
1369 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1370 x_return_status := fnd_api.g_ret_sts_unexp_error;
1371 RAISE fnd_api.g_exc_unexpected_error;
1372 END IF;
1373
1374 */
1375
1376 --dbms_output.put_line('7');
1377 --dbms_output.put_line('l_debrief_type :: '|| l_debrief_type);
1378 if nvl(p_debrief_line_rec.inventory_item_id,fnd_api.g_miss_num) =
1379 fnd_api.g_miss_num and l_debrief_type = 'L' then
1380 if nvl(p_debrief_line_rec.labor_start_date,fnd_api.g_miss_date) = fnd_api.g_miss_date and
1381 nvl(p_debrief_line_rec.labor_end_date,fnd_api.g_miss_date) = fnd_api.g_miss_date then
1382 x_return_status := FND_API.G_RET_STS_ERROR;
1383 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1384 FND_MESSAGE.Set_Name('CSF', 'CSF_DEBRIEF_LABOR_DATES_ITEM');
1385 FND_MSG_PUB.ADD;
1386 END IF;
1387 RAISE fnd_api.g_exc_error;
1388 end if;
1389 else
1390 Validate_Inventory_Item_ID (
1391 P_Init_Msg_List => FND_API.G_FALSE,
1392 p_organization_id => nvl(p_debrief_line_rec.issuing_inventory_org_id,p_debrief_line_rec.receiving_inventory_org_id),
1393 P_Inventory_Item_ID =>p_debrief_line_rec.Inventory_Item_ID,
1394 X_Return_Status =>x_return_status,
1395 X_Msg_Count =>x_msg_count,
1396 X_Msg_Data =>x_msg_data);
1397 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1398 x_return_status := fnd_api.g_ret_sts_unexp_error;
1399 RAISE fnd_api.g_exc_unexpected_error;
1400 END IF;
1401 --dbms_output.put_line('8');
1402 Validate_Instance_ID (
1403 P_Init_Msg_List => FND_API.G_FALSE,
1404 P_Instance_ID =>p_debrief_line_rec.Instance_ID,
1405 X_Return_Status =>x_return_status,
1406 X_Msg_Count =>x_msg_count,
1407 X_Msg_Data =>x_msg_data);
1408 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1409 x_return_status := fnd_api.g_ret_sts_unexp_error;
1410 RAISE fnd_api.g_exc_unexpected_error;
1411 END IF;
1412 END IF;
1413 --dbms_output.put_line('9');
1414 Validate_Debrief_Header_ID (
1415 P_Init_Msg_List => FND_API.G_FALSE,
1416 P_Debrief_Header_ID =>p_debrief_line_rec.Debrief_Header_ID,
1417 X_Return_Status =>x_return_status,
1418 X_Msg_Count =>x_msg_count,
1419 X_Msg_Data =>x_msg_data);
1420 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1421 x_return_status := fnd_api.g_ret_sts_unexp_error;
1422 RAISE fnd_api.g_exc_unexpected_error;
1423 END IF;
1424 --dbms_output.put_line('10');
1425 --dbms_output.put_line('Value: '||p_debrief_line_rec.usage_type);
1426 IF p_debrief_line_rec.usage_type <> 'DOA' AND p_debrief_line_rec.usage_type <> 'UNUSED'
1427 THEN
1428 Validate_BUSINESS_PROCESS_ID (
1429 P_Init_Msg_List => FND_API.G_FALSE,
1430 P_BUSINESS_PROCESS_ID =>p_debrief_line_rec.BUSINESS_PROCESS_ID,
1431 X_Return_Status =>x_return_status,
1432 X_Msg_Count =>x_msg_count,
1433 X_Msg_Data =>x_msg_data);
1434 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1435 x_return_status := fnd_api.g_ret_sts_unexp_error;
1436 RAISE fnd_api.g_exc_unexpected_error;
1437 END IF;
1438 --dbms_output.put_line('11');
1439 Validate_TRANSACTION_TYPE_ID (
1440 P_Init_Msg_List => FND_API.G_FALSE,
1441 P_TRANSACTION_TYPE_ID =>p_debrief_line_rec.transaction_type_id,
1442 X_Return_Status =>x_return_status,
1443 X_Msg_Count =>x_msg_count,
1444 X_Msg_Data =>x_msg_data);
1445 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1446 x_return_status := fnd_api.g_ret_sts_unexp_error;
1447 RAISE fnd_api.g_exc_unexpected_error;
1448 END IF;
1449
1450 END IF;
1451 END IF;
1452 --dbms_output.put_line('12');
1453 l_debrief_line_id := p_debrief_line_rec.debrief_line_id;
1454 if (l_debrief_line_id<>FND_API.G_MISS_NUM) and (l_debrief_line_id is not NULL) then
1455 begin
1456 select 1 into x
1457 from CSF_DEBRIEF_LINES
1458 where DEBRIEF_LINE_ID = l_DEBRIEF_LINE_ID ;
1459
1460 fnd_message.set_name('CSF', 'CSF_DEBRIEF_INVALID_LINE_ID');
1461 fnd_msg_pub.add;
1462 x_return_status := fnd_api.g_ret_sts_unexp_error;
1463 RAISE fnd_api.g_exc_unexpected_error;
1464 exception
1465 when no_data_found then
1466 null ;
1467 when too_many_rows then
1468 fnd_message.set_name('CSF', 'CSF_DEBRIEF_INVALID_LINE_ID');
1469 fnd_msg_pub.add;
1470 x_return_status := fnd_api.g_ret_sts_unexp_error;
1471 RAISE fnd_api.g_exc_unexpected_error;
1472 end ;
1473 ELSE
1474
1475 SELECT CSF_DEBRIEF_LINES_S.nextval
1476 INTO l_debrief_line_id
1477 FROM dual;
1478 END IF;
1479
1480 --dbms_output.put_line('Calling INSERT_ROW...');
1481 -- Invoke table handler(debrief_Line_Insert_Row)
1482 csf_debrief_lines_pkg.INSERT_ROW(
1483 px_DEBRIEF_LINE_ID => l_debrief_line_id,
1484 p_DEBRIEF_HEADER_ID => p_DEBRIEF_LINE_rec.DEBRIEF_HEADER_ID,
1485 p_DEBRIEF_LINE_NUMBER => p_DEBRIEF_LINE_rec.DEBRIEF_LINE_NUMBER,
1486 p_SERVICE_DATE => p_DEBRIEF_LINE_rec.SERVICE_DATE ,
1487 p_BUSINESS_PROCESS_ID => p_DEBRIEF_LINE_rec.BUSINESS_PROCESS_ID ,
1488 p_TXN_BILLING_TYPE_ID => p_DEBRIEF_LINE_rec.TXN_BILLING_TYPE_ID ,
1489 p_INVENTORY_ITEM_ID => p_DEBRIEF_LINE_rec.INVENTORY_ITEM_ID ,
1490 p_INSTANCE_ID => p_DEBRIEF_LINE_rec.INSTANCE_ID ,
1491 p_ISSUING_INVENTORY_ORG_ID => p_DEBRIEF_LINE_rec.ISSUING_INVENTORY_ORG_ID,
1492 p_RECEIVING_INVENTORY_ORG_ID => p_DEBRIEF_LINE_rec.RECEIVING_INVENTORY_ORG_ID,
1493 p_ISSUING_SUB_INVENTORY_CODE => p_DEBRIEF_LINE_rec.ISSUING_SUB_INVENTORY_CODE,
1494 p_RECEIVING_SUB_INVENTORY_CODE => p_DEBRIEF_LINE_rec.RECEIVING_SUB_INVENTORY_CODE,
1495 p_ISSUING_LOCATOR_ID => p_DEBRIEF_LINE_rec.ISSUING_LOCATOR_ID ,
1496 p_RECEIVING_LOCATOR_ID => p_DEBRIEF_LINE_rec.RECEIVING_LOCATOR_ID ,
1497 p_PARENT_PRODUCT_ID => p_DEBRIEF_LINE_rec.PARENT_PRODUCT_ID ,
1498 p_REMOVED_PRODUCT_ID => p_DEBRIEF_LINE_rec.REMOVED_PRODUCT_ID ,
1499 p_STATUS_OF_RECEIVED_PART => l_cp_status_id ,
1500 p_ITEM_SERIAL_NUMBER => p_DEBRIEF_LINE_rec.ITEM_SERIAL_NUMBER ,
1501 p_ITEM_REVISION => p_DEBRIEF_LINE_rec.ITEM_REVISION ,
1502 p_ITEM_LOTNUMBER => p_DEBRIEF_LINE_rec.ITEM_LOTNUMBER ,
1503 p_UOM_CODE => p_DEBRIEF_LINE_rec.UOM_CODE ,
1504 p_QUANTITY => p_DEBRIEF_LINE_rec.QUANTITY ,
1505 p_RMA_HEADER_ID => p_DEBRIEF_LINE_rec.RMA_HEADER_ID ,
1506 p_DISPOSITION_CODE => p_DEBRIEF_LINE_rec.DISPOSITION_CODE ,
1507 p_MATERIAL_REASON_CODE => p_DEBRIEF_LINE_rec.MATERIAL_REASON_CODE ,
1508 p_LABOR_REASON_CODE => p_DEBRIEF_LINE_rec.LABOR_REASON_CODE ,
1509 p_EXPENSE_REASON_CODE => p_DEBRIEF_LINE_rec.EXPENSE_REASON_CODE ,
1510 p_LABOR_START_DATE => trunc(xx_labor_start_date,'MI'),
1511 p_LABOR_END_DATE => trunc(xx_labor_end_date,'MI'),
1512 p_STARTING_MILEAGE => p_DEBRIEF_LINE_rec.STARTING_MILEAGE ,
1513 p_ENDING_MILEAGE => p_DEBRIEF_LINE_rec.ENDING_MILEAGE ,
1514 p_EXPENSE_AMOUNT => p_DEBRIEF_LINE_rec.EXPENSE_AMOUNT ,
1515 p_CURRENCY_CODE => p_DEBRIEF_LINE_rec.CURRENCY_CODE ,
1516 p_DEBRIEF_LINE_STATUS_ID => p_DEBRIEF_LINE_rec.DEBRIEF_LINE_STATUS_ID ,
1517 p_RETURN_REASON_CODE => P_debrief_line_rec.RETURN_REASON_CODE ,
1518 p_CHANNEL_CODE => p_DEBRIEF_LINE_rec.CHANNEL_CODE,
1519 p_CHARGE_UPLOAD_STATUS => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_STATUS ,
1520 p_CHARGE_UPLOAD_MSG_CODE => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_MSG_CODE ,
1521 p_CHARGE_UPLOAD_MESSAGE => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_MESSAGE ,
1522 p_IB_UPDATE_STATUS => p_DEBRIEF_LINE_rec.IB_UPDATE_STATUS ,
1523 p_IB_UPDATE_MSG_CODE => p_DEBRIEF_LINE_rec.IB_UPDATE_MSG_CODE ,
1524 p_IB_UPDATE_MESSAGE => p_DEBRIEF_LINE_rec.IB_UPDATE_MESSAGE ,
1525 p_SPARE_UPDATE_STATUS => p_DEBRIEF_LINE_rec.SPARE_UPDATE_STATUS ,
1526 p_SPARE_UPDATE_MSG_CODE => p_DEBRIEF_LINE_rec.SPARE_UPDATE_MSG_CODE ,
1527 p_SPARE_UPDATE_MESSAGE => p_DEBRIEF_LINE_rec.SPARE_UPDATE_MESSAGE ,
1528 p_CREATED_BY => nvl(p_debrief_line_rec.created_by,G_USER_ID),
1529 p_CREATION_DATE => nvl(p_debrief_line_rec.creation_date,SYSDATE),
1530 p_LAST_UPDATED_BY => nvl(p_debrief_line_rec.last_updated_by,G_USER_ID),
1531 p_LAST_UPDATE_DATE => nvl(p_debrief_line_rec.last_update_date,SYSDATE),
1532 p_LAST_UPDATE_LOGIN => nvl(p_debrief_line_rec.last_update_login,G_LOGIN_ID),
1533 p_ATTRIBUTE1 => p_DEBRIEF_LINE_rec.ATTRIBUTE1,
1534 p_ATTRIBUTE2 => p_DEBRIEF_LINE_rec.ATTRIBUTE2,
1535 p_ATTRIBUTE3 => p_DEBRIEF_LINE_rec.ATTRIBUTE3,
1536 p_ATTRIBUTE4 => p_DEBRIEF_LINE_rec.ATTRIBUTE4,
1537 p_ATTRIBUTE5 => p_DEBRIEF_LINE_rec.ATTRIBUTE5,
1538 p_ATTRIBUTE6 => p_DEBRIEF_LINE_rec.ATTRIBUTE6,
1539 p_ATTRIBUTE7 => p_DEBRIEF_LINE_rec.ATTRIBUTE7,
1540 p_ATTRIBUTE8 => p_DEBRIEF_LINE_rec.ATTRIBUTE8,
1541 p_ATTRIBUTE9 => p_DEBRIEF_LINE_rec.ATTRIBUTE9,
1542 p_ATTRIBUTE10 => p_DEBRIEF_LINE_rec.ATTRIBUTE10,
1543 p_ATTRIBUTE11 => p_DEBRIEF_LINE_rec.ATTRIBUTE11,
1544 p_ATTRIBUTE12 => p_DEBRIEF_LINE_rec.ATTRIBUTE12,
1545 p_ATTRIBUTE13 => p_DEBRIEF_LINE_rec.ATTRIBUTE13,
1546 p_ATTRIBUTE14 => p_DEBRIEF_LINE_rec.ATTRIBUTE14,
1547 p_ATTRIBUTE15 => p_DEBRIEF_LINE_rec.ATTRIBUTE15,
1548 p_ATTRIBUTE_CATEGORY => p_DEBRIEF_LINE_rec.ATTRIBUTE_CATEGORY,
1549 p_TRANSACTION_TYPE_ID => P_DEBRIEF_LINE_REC.TRANSACTION_TYPE_ID,
1550 p_RETURN_DATE => P_DEBRIEF_LINE_REC.RETURN_DATE,
1551
1552 P_USAGE_TYPE => P_DEBRIEF_LINE_REC.USAGE_TYPE,
1553 P_DEST_ORGANIZATION_ID => P_DEBRIEF_LINE_REC.DEST_ORGANIZATION_ID,
1554 P_DEST_SUBINVENTORY_NAME => P_DEBRIEF_LINE_REC.DEST_SUBINVENTORY_NAME,
1555 P_CARRIER_CODE => P_DEBRIEF_LINE_REC.CARRIER_CODE,
1556 P_SHIPPING_METHOD => P_DEBRIEF_LINE_REC.SHIPPING_METHOD,
1557 P_SHIPMENT_NUMBER => P_DEBRIEF_LINE_REC.SHIPMENT_NUMBER,
1558 P_WAYBILL => P_DEBRIEF_LINE_REC.WAYBILL,
1559
1560 p_expenditure_org_id => p_debrief_line_rec.expenditure_org_id,
1561 p_project_id => p_debrief_line_rec.project_id,
1562 p_project_task_id => p_debrief_line_rec.project_task_id
1563 );
1564
1565 -- Calling the Update Resource Location API to Update Resource's Locatio
1566 --dbms_output.put_line('l_Debrief_type : '||l_Debrief_type);
1567 IF (l_Debrief_type = 'L') THEN
1568 BEGIN
1569 SELECT jta.resource_type_code,
1570 jta.resource_id,
1571 jta.task_id
1572 INTO l_resource_type,
1573 l_resource_id,
1574 l_task_id
1575 FROM jtf_task_Assignments jta, csf_Debrief_headers cdh
1576 WHERE jta.task_Assignment_id = cdh.task_assignment_id
1577 AND cdh.debrief_header_id = p_debrief_line_rec.DEBRIEF_HEADER_ID;
1578 EXCEPTION
1579 when no_data_found then
1580 null;
1581 END;
1582 VALIDATE_LABOR_TIMES (
1583 P_Init_Msg_List => null,
1584 P_api_version_number => 1.0,
1585 p_resource_type_code => l_Resource_type,
1586 p_resource_id => l_resource_id,
1587 p_debrief_line_id => p_debrief_line_rec.debrief_line_id,
1588 p_labor_start_date => p_debrief_line_rec.labor_start_date,
1589 p_labor_end_date => p_Debrief_line_rec.labor_end_date,
1590 p_task_id => l_task_id,
1591 x_return_status => x_return_status,
1592 x_msg_count => x_msg_count,
1593 x_msg_data => x_msg_data,
1594 x_debrief_number => l_debrief_number,
1595 x_task_number => l_task_number
1596 );
1597 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1598 FND_MESSAGE.Set_Name('CSF', 'CSF_OVERLAP_LABOR_LINE');
1599 FND_MESSAGE.SET_TOKEN ('TASK_NUMBER', l_task_number);
1600 FND_MSG_PUB.ADD;
1601 RAISE fnd_api.g_exc_error;
1602 END IF;
1603 END IF;
1604 --Update actual times for task and task assignment
1605 if l_debrief_type = 'L' then
1606 update_task_actuals(p_debrief_line_rec.debrief_header_id, -- modified for bug 3748610
1607 x_return_status,
1608 x_msg_count,
1609 x_msg_data);
1610 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1611 RAISE FND_API.G_EXC_ERROR;
1612 END IF;
1613 end if;
1614 if l_debrief_type='L' then
1615 Begin
1616 select jta.resource_id,
1617 loc.geometry
1618 into z_resource_id,
1619 z_location
1620 from csf_debrief_lines cdl,
1621 csf_debrief_headers csf,
1622 jtf_task_assignments jta,
1623 jtf_tasks_b jtb,
1624 hz_party_sites p,
1625 hz_locations loc
1626 where csf.debrief_header_id = p_debrief_line_rec.debrief_header_id
1627 and debrief_line_id = l_debrief_line_id
1628 and csf.debrief_header_id = cdl.debrief_header_id
1629 and jta.task_assignment_id = csf.task_assignment_id
1630 and jta.task_id = jtb.task_id
1631 and jtb.address_id = p.party_site_id
1632 and p.location_id = loc.location_id;
1633 select max(actual_start_date)
1634 into z_start_date
1635 from jtf_task_assignments
1636 where resource_id = z_resource_id;
1637
1638 IF p_debrief_line_rec.labor_start_date > z_start_date then
1639 select object_version_number
1640 into z_object_version_number
1641 from jtf_rs_resource_extns
1642 where resource_id = z_resource_id;
1643
1644 /* jtf_rs_resource_pub.update_resource
1645 (P_API_VERSION => 1,
1646 P_INIT_MSG_LIST => fnd_api.g_false,
1647 P_COMMIT => fnd_api.g_false,
1648 P_RESOURCE_ID => z_resource_id,
1649 P_RESOURCE_NUMBER => null,
1650 P_LOCATION => z_location,
1651 P_object_version_num => z_object_version_number,
1652 X_RETURN_STATUS => x_return_status,
1653 X_MSG_COUNT => x_msg_count,
1654 X_MSG_DATA => x_msg_data);
1655 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1656 Raise FND_API.G_EXC_ERROR;
1657 END IF;
1658 */
1659 END IF;
1660 Exception
1661 When NO_DATA_FOUND then
1662 NULL;
1663 End;
1664 End if;
1665 -- Calling the Update Task Assignment API Location API to Update Resource's Location
1666
1667 if P_Upd_tskassgnstatus = 'Y' then
1668
1669 BEGIN
1670 Validate_Task_Assignment_Satus(
1671 P_Api_Version_Number =>1.0,
1672 P_Init_Msg_List => FND_API.G_FALSE,
1673 P_Commit => FND_API.G_FALSE,
1674 P_Task_Assignment_status =>P_Task_Assignment_status,
1675 X_TA_STATUS_ID =>l_assignment_status_id,
1676 X_Return_Status =>X_Return_Status,
1677 X_Msg_Count =>X_MSG_COUNT,
1678 X_Msg_Data =>X_MSG_DATA);
1679 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1680 RAISE FND_API.G_EXC_ERROR;
1681 END IF;
1682
1683
1684 select jta.task_assignment_id,
1685 jta.object_version_number,
1686 jta.task_id
1687 into l_task_assignment_id,
1688 l_object_version_number,
1689 l_task_id
1690 from jtf_task_assignments jta,
1691 csf_debrief_headers csf
1692 where csf.task_assignment_id = jta.task_assignment_id
1693 and csf.debrief_header_id = p_debrief_line_rec.debrief_header_id;
1694
1695
1696 CSF_TASK_ASSIGNMENTS_PUB.UPDATE_ASSIGNMENT_STATUS(
1697 P_API_VERSION =>1.0 ,
1698 P_INIT_MSG_LIST =>FND_API.G_FALSE,
1699 P_COMMIT =>FND_API.G_FALSE,
1700 X_RETURN_STATUS =>x_return_status,
1701 X_MSG_COUNT =>x_msg_count,
1702 X_MSG_DATA =>x_msg_data,
1703 P_TASK_ASSIGNMENT_ID =>l_task_assignment_id,
1704 P_ASSIGNMENT_STATUS_ID =>l_assignment_status_id,
1705 P_OBJECT_VERSION_NUMBER =>l_object_version_number,
1706 P_UPDATE_TASK =>'T',
1707 X_TASK_OBJECT_VERSION_NUMBER =>l_task_object_version_number,
1708 X_TASK_STATUS_ID =>l_task_status_id);
1709 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1710 RAISE FND_API.G_EXC_ERROR;
1711 END IF;
1712
1713 EXCEPTION
1714 WHEN OTHERS THEN
1715 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1716 FND_MESSAGE.Set_Name('CSF', 'CSF_DEBRIEF_UPDATE_TASK_ASSIGNMENT');
1717 FND_MSG_PUB.ADD;
1718 END IF;
1719 RAISE FND_API.G_EXC_ERROR;
1720 END;
1721 end if;
1722 END IF;
1723 END;
1724 --dbms_output.put_line('END LOOP');
1725 END LOOP;
1726
1727 --dbms_output.put_line('OUT OF LOOP');
1728
1729 IF (p_DEBRIEF_LINE_REC.CHANNEL_CODE <> 'CSF_PORTAL') THEN
1730 -- Call concurrent program if assignment status is completed,closed,rejected,canceled
1731 open c_status(p_debrief_line_rec.debrief_header_id);
1732 fetch c_status into l_call_cp;
1733 close c_status;
1734 if nvl(l_call_cp,'N') = 'Y' then
1735 l_request_id := fnd_request.submit_request(
1736 'CSF',
1737 'CSFUPDATE',
1738 'CSF:Update Debrief Lines',
1739 null,
1740 FALSE,
1741 1.0,
1742 p_debrief_line_rec.debrief_header_id);
1743 end if;
1744 -- Standard check for p_commit
1745 IF FND_API.to_Boolean( p_commit )
1746 THEN
1747 COMMIT WORK;
1748 END IF;
1749
1750 END IF;
1751
1752 -- Standard call to get message count and if count is 1, get message info.
1753 FND_MSG_PUB.Count_And_Get
1754 ( p_count => x_msg_count,
1755 p_data => x_msg_data
1756 );
1757 --dbms_output.put_line('Start of EXCEPTION Block');
1758 EXCEPTION
1759 WHEN FND_API.G_EXC_ERROR THEN
1760 ROLLBACK TO CREATE_DEBRIEF_LINE_PVT;
1761 x_return_status := FND_API.G_RET_STS_ERROR;
1762 FND_MSG_PUB.Count_And_Get (
1763 P_COUNT => X_MSG_COUNT
1764 ,P_DATA => X_MSG_DATA);
1765
1766 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1767 ROLLBACK TO CREATE_DEBRIEF_LINE_PVT;
1768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1769 FND_MSG_PUB.Count_And_Get (
1770 P_COUNT => X_MSG_COUNT
1771 ,P_DATA => X_MSG_DATA);
1772 WHEN OTHERS THEN
1773 ROLLBACK TO CREATE_DEBRIEF_LINE_PVT;
1774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1775 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1776 THEN
1777 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME ,L_API_NAME );
1778 END IF;
1779
1780 FND_MSG_PUB.Count_And_Get (
1781 P_COUNT => X_MSG_COUNT
1782 ,P_DATA => X_MSG_DATA);
1783 End Create_debrief_lines;
1784
1785 -- **********
1786
1787 PROCEDURE Validate_Task_Assignment_Satus(
1788 P_Api_Version_Number IN NUMBER,
1789 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1790 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1791 P_Task_Assignment_status IN VARCHAR2 ,
1792 X_TA_STATUS_ID OUT NOCOPY NUMBER,
1793 X_Return_Status OUT NOCOPY VARCHAR2,
1794 X_Msg_Count OUT NOCOPY NUMBER,
1795 X_Msg_Data OUT NOCOPY VARCHAR2
1796 )
1797 IS
1798
1799 Cursor validate_ta_status is
1800 select task_status_id
1801 from jtf_task_statuses_vl
1802 where name=P_Task_Assignment_status;
1803
1804 BEGIN
1805 -- Initialize message list if p_init_msg_list is set to TRUE.
1806 IF FND_API.to_Boolean( p_init_msg_list )
1807 THEN
1808 FND_MSG_PUB.initialize;
1809 END IF;
1810
1811 -- Initialize API return status to SUCCESS
1812 x_return_status := FND_API.G_RET_STS_SUCCESS;
1813 IF (P_Task_Assignment_status is NULL OR P_Task_Assignment_status = FND_API.G_MISS_CHAR) THEN
1814 x_return_status := FND_API.G_RET_STS_ERROR;
1815 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1816 FND_MESSAGE.Set_Name('CSF', 'CSF_DEBRIEF_TA_STATUS');
1817 FND_MSG_PUB.ADD;
1818 END IF;
1819 ELSE
1820 open validate_ta_status;
1821 fetch validate_ta_status into X_TA_STATUS_ID;
1822 if validate_ta_status%notfound then
1823 close validate_ta_status ;
1824 x_return_status := FND_API.G_RET_STS_ERROR;
1825 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1826 FND_MESSAGE.Set_Name('CSF', 'CSF_DEBRIEF_TAvalvav_STATUS');
1827 FND_MSG_PUB.ADD;
1828 END IF;
1829 end if;
1830 close validate_ta_status ;
1831 END IF;
1832
1833 -- Standard call to get message count and if count is 1, get message info.
1834 FND_MSG_PUB.Count_And_Get
1835 ( p_count => x_msg_count,
1836 p_data => x_msg_data
1837 );
1838 END Validate_Task_Assignment_Satus;
1839 -- **********
1840
1841 PROCEDURE Update_debrief_line(
1842 P_Api_Version_Number IN NUMBER,
1843 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1844 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1845 P_Upd_tskassgnstatus IN VARCHAR2 ,
1846 P_Task_Assignment_status IN VARCHAR2 ,
1847 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1848 P_DEBRIEF_LINE_Rec IN OUT NOCOPY DEBRIEF_LINE_Rec_type,
1849 X_Return_Status OUT NOCOPY VARCHAR2,
1850 X_Msg_Count OUT NOCOPY NUMBER,
1851 X_Msg_Data OUT NOCOPY VARCHAR2
1852 )
1853 IS
1854
1855 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
1856 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
1857 l_hr_uom varchar2(100) := fnd_profile.value('CSF_UOM_HOURS');
1858 l_quantity number;
1859 l_cp_status_id number;
1860 l_cp_status varchar2(30);
1861 l_request_id number;
1862 l_call_cp varchar2(1);
1863
1864 cursor c_status(p_debrief_header_id number) is
1865 select greatest(nvl(rejected_flag,'N'),
1866 nvl(completed_flag,'N'),
1867 nvl(closed_flag,'N'),
1868 nvl(cancelled_flag,'N'))
1869 from jtf_task_statuses_b jtsb,
1870 jtf_task_assignments jta,
1871 csf_debrief_headers cdh
1872 where cdh.debrief_header_id = p_debrief_header_id
1873 and jta.task_assignment_id = cdh.task_assignment_id
1874 and jtsb.task_status_id = jta.assignment_status_id;
1875
1876 -- Virendra Singh 03/21/2000
1877 -- cursor to get CHARGE_UPLOAD_STATUS
1878 cursor GET_CHARGE_UPLOAD_STATUS (P_DEBRIEF_LINE_ID NUMBER) is
1879 select CHARGE_UPLOAD_STATUS,
1880 IB_UPDATE_STATUS,
1881 SPARE_UPDATE_STATUS,
1882 labor_start_date
1883 from CSF_DEBRIEF_LINES
1884 where DEBRIEF_LINE_ID=P_DEBRIEF_LINE_ID;
1885
1886
1887 cursor c_cp_status(p_transaction_type_id number) is
1888 select ctst.src_status_id
1889 from csi_txn_sub_types ctst ,
1890 cs_transaction_types_vl cttv
1891 where ctst.cs_transaction_type_id = cttv.transaction_type_id
1892 and cttv.transaction_type_id = p_transaction_type_id;
1893
1894 cursor c_cp_status_notnull (p_cp_status varchar2) is
1895 select instance_status_id
1896 from csi_instance_statuses
1897 where name = p_cp_status;
1898
1899
1900 l_api_name CONSTANT VARCHAR2(30) := 'Update_debrief_line';
1901 l_api_version_number CONSTANT NUMBER := 1.0;
1902 -- Local Variables
1903 l_debrief_line_rec CSF_DEBRIEF_PVT.DEBRIEF_LINE_REC_TYPE;
1904 z_debrief_line_number number;
1905 z_start_date date;
1906 z_end_date date;
1907 z_debrief_header_id number;
1908 z_task_assignment_id number;
1909 z_resource_id number;
1910 z_location mdsys.sdo_geometry;
1911 z_debrief_line_id number;
1912 z_object_version_number NUMBER;
1913 l_object_version_number number;
1914 l_task_id number;
1915 l_task_assignment_id number;
1916 l_assignment_status_id number;
1917 closed_assignment_count number;
1918 l_charge_upload_status varchar2(30);
1919 L_DEBRIEF_LINE_ID number;
1920 l_business_process_id number;
1921 l_billing_type cs_txn_billing_types.billing_type%type;
1922 l_task_object_version_number number;
1923 l_task_status_id number;
1924 l_task_status_name varchar2(200);
1925 l_data varchar2(500);
1926 l_msg_index_out number;
1927 l_ib_upload_status varchar2(30);
1928 l_spare_upload_status varchar2(30);
1929 l_return_reason_code varchar2(30) ;
1930 l_uom_code varchar2(30);
1931 l_labor_start_date date;
1932 l_debrief_type varchar2(1);
1933 l_issuing_inventory_org_id number:= null;
1934 l_receiving_inventory_org_id number:= null;
1935
1936 cursor labor_uom(p_inventory_item_id number) is
1937 select primary_uom_code
1938 from mtl_system_items_b
1939 where inventory_item_id = p_inventory_item_id;
1940
1941 cursor c_uom_code is
1942 select uom_code
1943 from csf_debrief_lines cdl
1944 where debrief_line_id = P_DEBRIEF_LINE_Rec.debrief_line_id;
1945
1946 BEGIN
1947 l_debrief_type := debrief_type(p_debrief_line_rec);
1948
1949 l_issuing_inventory_org_id := p_debrief_line_rec.issuing_inventory_org_id;
1950 l_receiving_inventory_org_id := p_debrief_line_rec.receiving_inventory_org_id;
1951 if p_debrief_line_rec.issuing_inventory_org_id = fnd_api.g_miss_num then
1952 l_issuing_inventory_org_id := null;
1953 end if;
1954 if p_debrief_line_rec.receiving_inventory_org_id = fnd_api.g_miss_num then
1955 l_receiving_inventory_org_id := null;
1956 end if;
1957
1958 -- Standard Start of API savepoint
1959 SAVEPOINT UPDATE_DEBRIEF_LINE_PVT;
1960
1961 -- Standard call to check for call compatibility.
1962 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1963 p_api_version_number,
1964 l_api_name,
1965 G_PKG_NAME)
1966 THEN
1967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1968 END IF;
1969
1970 IF FND_API.to_Boolean( p_init_msg_list )
1971 THEN
1972 FND_MSG_PUB.initialize;
1973 END IF;
1974
1975 -- Initialize API return status to SUCCESS
1976 x_return_status := FND_API.G_RET_STS_SUCCESS;
1977
1978 -- Virendra Singh 03/21/2000
1979 -- fetch charge upload status and check if its already upload
1980 -- then don't update debrief lines
1981 l_debrief_line_id:=p_debrief_line_rec.debrief_line_id;
1982 open GET_CHARGE_UPLOAD_STATUS(l_DEBRIEF_LINE_ID);
1983 fetch GET_CHARGE_UPLOAD_STATUS into l_charge_upload_status, l_ib_upload_status, l_spare_upload_status, l_labor_start_date;
1984 if GET_CHARGE_UPLOAD_STATUS%notfound then
1985 close GET_CHARGE_UPLOAD_STATUS;
1986 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1987 THEN
1988 FND_MESSAGE.Set_Name('CSF', 'CSF_DEBRIEF_INVALID_LINE_ID');
1989 FND_MSG_PUB.Add;
1990
1991 END IF;
1992 raise FND_API.G_EXC_ERROR;
1993 end if;
1994 close GET_CHARGE_UPLOAD_STATUS;
1995 l_cp_status := p_debrief_line_rec.STATUS_OF_RECEIVED_PART;
1996 if (l_cp_status is null or l_cp_status=FND_API.G_MISS_CHAR) then
1997 open c_cp_status(p_debrief_line_rec.TXN_BILLING_TYPE_ID);
1998 fetch c_cp_status into l_cp_status_id;
1999 close c_cp_status;
2000 else
2001 open c_cp_status_notnull (l_cp_status);
2002 fetch c_cp_status_notnull into l_cp_status_id;
2003 close c_cp_status_notnull;
2004 end if;
2005 IF l_charge_upload_status='SUCCEEDED' or
2006 l_ib_upload_status ='SUCCEEDED' or
2007 l_spare_upload_status ='SUCCEEDED' then
2008 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2009 THEN
2010 FND_MESSAGE.Set_Name('CSF', 'CSF_DEBRIEF_CHARGE_UPLOADED');
2011 FND_MSG_PUB.Add;
2012 x_return_status := 'E';
2013 END IF;
2014 ELSE
2015 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
2016 THEN
2017 IF (p_debrief_line_rec.Service_Date<> FND_API.G_MISS_DATE) then
2018 Validate_Service_Date (
2019 P_Init_Msg_List =>FND_API.G_FALSE,
2020 P_Service_Date =>p_debrief_line_rec.Service_Date,
2021 X_Return_Status =>x_return_status,
2022 X_Msg_Count =>x_msg_count,
2023 X_Msg_Data =>x_msg_data);
2024 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2025 x_return_status := fnd_api.g_ret_sts_unexp_error;
2026 RAISE fnd_api.g_exc_unexpected_error;
2027 END IF;
2028 END IF;
2029 IF nvl(P_DEBRIEF_LINE_REC.Inventory_Item_ID,fnd_api.g_miss_num)= FND_API.G_MISS_NUM and l_debrief_type = 'L' then
2030 if nvl(p_debrief_line_rec.labor_start_date,fnd_api.g_miss_date) = fnd_api.g_miss_date and
2031 nvl(p_debrief_line_rec.labor_end_date,fnd_api.g_miss_date) = fnd_api.g_miss_date then
2032 x_return_status := FND_API.G_RET_STS_ERROR;
2033 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2034 FND_MESSAGE.Set_Name('CSF', 'CSF_DEBRIEF_LABOR_DATES_ITEM');
2035 FND_MSG_PUB.ADD;
2036 END IF;
2037 RAISE fnd_api.g_exc_error;
2038 end if;
2039 else
2040 Validate_Inventory_Item_ID (
2041 P_Init_Msg_List => FND_API.G_FALSE,
2042 p_organization_id => nvl(l_issuing_inventory_org_id,l_receiving_inventory_org_id),
2043 P_Inventory_Item_ID =>p_debrief_line_rec.Inventory_Item_ID,
2044 X_Return_Status =>x_return_status,
2045 X_Msg_Count =>x_msg_count,
2046 X_Msg_Data =>x_msg_data);
2047 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2048 x_return_status := fnd_api.g_ret_sts_unexp_error;
2049 RAISE fnd_api.g_exc_unexpected_error;
2050 END IF;
2051 END IF;
2052 IF (P_DEBRIEF_LINE_REC.DEBRIEF_HEADER_ID <>FND_API.G_MISS_NUM) THEN
2053 Validate_Debrief_Header_ID (
2054 P_Init_Msg_List => FND_API.G_FALSE,
2055 P_Debrief_Header_ID =>p_debrief_line_rec.Debrief_Header_ID,
2056 X_Return_Status =>x_return_status,
2057 X_Msg_Count =>x_msg_count,
2058 X_Msg_Data =>x_msg_data);
2059 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2060 x_return_status := fnd_api.g_ret_sts_unexp_error;
2061 RAISE fnd_api.g_exc_unexpected_error;
2062 END IF;
2063 END IF;
2064 IF p_debrief_line_rec.usage_type <> 'DOA' AND p_debrief_line_rec.usage_type <> 'UNUSED' then
2065 IF (P_DEBRIEF_LINE_REC.BUSINESS_PROCESS_ID <> FND_API.G_MISS_NUM) THEN
2066 Validate_BUSINESS_PROCESS_ID (
2067 P_Init_Msg_List => FND_API.G_FALSE,
2068 P_BUSINESS_PROCESS_ID =>p_debrief_line_rec.BUSINESS_PROCESS_ID,
2069 X_Return_Status =>x_return_status,
2070 X_Msg_Count =>x_msg_count,
2071 X_Msg_Data =>x_msg_data);
2072 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2073 x_return_status := fnd_api.g_ret_sts_unexp_error;
2074 RAISE fnd_api.g_exc_unexpected_error;
2075 END IF;
2076 END IF;
2077 IF (P_DEBRIEF_LINE_REC.TXN_BILLING_TYPE_ID <> FND_API.G_MISS_NUM) THEN
2078 Validate_TRANSACTION_TYPE_ID (
2079 P_Init_Msg_List => FND_API.G_FALSE,
2080 P_TRANSACTION_TYPE_ID =>p_debrief_line_rec.transaction_type_id,
2081 X_Return_Status =>x_return_status,
2082 X_Msg_Count =>x_msg_count,
2083 X_Msg_Data =>x_msg_data);
2084 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2085 x_return_status := fnd_api.g_ret_sts_unexp_error;
2086 RAISE fnd_api.g_exc_unexpected_error;
2087 END IF;
2088 END IF;
2089 end if;
2090 END IF;
2091 -- Fixed bug 1286592
2092 -- For labor lines ignore the UOM passed from
2093 l_uom_code := p_debrief_line_rec.uom_code;
2094 if l_labor_start_date is not null then
2095 open labor_uom(p_debrief_line_rec.inventory_item_id);
2096 fetch labor_uom into l_uom_code;
2097 close labor_uom;
2098 end if;
2099 if l_debrief_type = 'E' and nvl(p_debrief_line_rec.expense_amount,fnd_api.g_miss_num) <> fnd_api.g_miss_num then
2100 p_debrief_line_rec.UOM_CODE := null;
2101 P_DEBRIEF_LINE_Rec.quantity := null;
2102 end if;
2103 if l_debrief_type = 'L' then
2104 --we should calculate quantity based on start time and end time
2105 --we should calculate quantity based on start time and end time
2106 if nvl(p_debrief_line_rec.uom_code,fnd_api.g_miss_char) = fnd_api.g_miss_char then
2107 open c_uom_code;
2108 fetch c_uom_code into l_uom_code;
2109 close c_uom_code;
2110 -- p_debrief_line_rec.uom_code := fnd_profile.value('CSF_LABOR_DEBRIEF_DEFAULT_UOM');
2111 end if;
2112 if nvl(p_DEBRIEF_LINE_rec.quantity,fnd_api.g_miss_num) = fnd_api.g_miss_num then
2113 l_quantity := (p_debrief_line_rec.LABOR_END_DATE - p_debrief_line_rec.LABOR_START_DATE);
2114 if l_uom_code <> l_hr_uom and l_quantity is not null then
2115 l_quantity := inv_convert.inv_um_convert(
2116 p_debrief_line_rec.INVENTORY_ITEM_ID,
2117 20,
2118 l_quantity*24,
2119 l_hr_uom,
2120 l_uom_code ,
2121 null,
2122 null);
2123 else
2124 l_quantity := l_quantity * 24;
2125 end if;
2126 l_quantity := round(nvl(l_quantity,0),4);
2127 p_DEBRIEF_LINE_rec.QUANTITY := round(l_quantity,4);
2128 end if;
2129
2130 if nvl(p_DEBRIEF_LINE_rec.labor_start_date,fnd_api.g_miss_date) = fnd_api.g_miss_date then
2131 if l_uom_code <> l_hr_uom then
2132 l_quantity := inv_convert.inv_um_convert(
2133 p_debrief_line_rec.INVENTORY_ITEM_ID,
2134 20,
2135 p_DEBRIEF_LINE_rec.quantity,
2136 l_uom_code ,
2137 l_hr_uom,
2138 null,
2139 null);
2140 else
2141 l_quantity := p_DEBRIEF_LINE_rec.quantity;
2142 end if;
2143 if nvl(p_DEBRIEF_LINE_rec.labor_end_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date then
2144 p_debrief_line_rec.labor_start_date := p_DEBRIEF_LINE_rec.labor_end_date - l_quantity/24;
2145 end if;
2146 end if;
2147
2148 if nvl(p_DEBRIEF_LINE_rec.labor_end_date,fnd_api.g_miss_date) = fnd_api.g_miss_date or
2149 (nvl(p_debrief_line_rec.labor_start_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date and
2150 nvl(P_DEBRIEF_LINE_Rec.quantity,fnd_api.g_miss_num) <> fnd_api.g_miss_num ) then
2151 if l_uom_code <> l_hr_uom then
2152 l_quantity := inv_convert.inv_um_convert(
2153 p_debrief_line_rec.INVENTORY_ITEM_ID,
2154 20,
2155 p_DEBRIEF_LINE_rec.quantity,
2156 l_uom_code ,
2157 l_hr_uom,
2158 null,
2159 null);
2160 else
2161 l_quantity := p_DEBRIEF_LINE_rec.quantity;
2162 end if;
2163 if nvl(p_DEBRIEF_LINE_rec.labor_start_date,fnd_api.g_miss_date) <> fnd_api.g_miss_date then
2164 p_debrief_line_rec.labor_end_date := p_DEBRIEF_LINE_rec.labor_start_date + l_quantity/24;
2165 end if;
2166 end if;
2167
2168 if nvl(p_debrief_line_rec.channel_code,'a') not in ('CSF_LAPTOP','CSF_PALM') then
2169 validate_start_end(p_debrief_line_rec.labor_start_date,
2170 p_debrief_line_rec.labor_end_date,
2171 p_debrief_line_rec.debrief_header_id,
2172 fnd_api.g_false,
2173 X_Return_Status,
2174 X_Msg_Count,
2175 X_Msg_Data);
2176 if x_return_status <> fnd_api.g_ret_sts_success then
2177 x_return_status := fnd_api.g_ret_sts_unexp_error;
2178 raise fnd_api.g_exc_unexpected_error;
2179 end if;
2180 end if;
2181 if nvl(l_uom_code,fnd_api.g_miss_char) = fnd_api.g_miss_char then
2182 p_debrief_line_rec.uom_code := fnd_profile.value('CSF_LABOR_DEBRIEF_DEFAULT_UOM');
2183 end if;
2184 end if;
2185
2186 ----
2187 csf_debrief_lines_pkg.update_ROW(
2188 p_DEBRIEF_LINE_ID => p_DEBRIEF_LINE_rec.debrief_line_id,
2189 p_DEBRIEF_HEADER_ID => p_DEBRIEF_LINE_rec.DEBRIEF_HEADER_ID,
2190 p_DEBRIEF_LINE_NUMBER => p_DEBRIEF_LINE_rec.DEBRIEF_LINE_NUMBER,
2191 p_SERVICE_DATE => p_DEBRIEF_LINE_rec.SERVICE_DATE ,
2192 p_BUSINESS_PROCESS_ID => p_DEBRIEF_LINE_rec.BUSINESS_PROCESS_ID ,
2193 p_TXN_BILLING_TYPE_ID => p_DEBRIEF_LINE_rec.TXN_BILLING_TYPE_ID ,
2194 p_INVENTORY_ITEM_ID => p_DEBRIEF_LINE_rec.INVENTORY_ITEM_ID ,
2195 p_INSTANCE_ID => p_DEBRIEF_LINE_rec.INSTANCE_ID ,
2196 p_ISSUING_INVENTORY_ORG_ID => p_DEBRIEF_LINE_rec.ISSUING_INVENTORY_ORG_ID,
2197 p_RECEIVING_INVENTORY_ORG_ID => p_DEBRIEF_LINE_rec.RECEIVING_INVENTORY_ORG_ID,
2198 p_ISSUING_SUB_INVENTORY_CODE => p_DEBRIEF_LINE_rec.ISSUING_SUB_INVENTORY_CODE,
2199 p_RECEIVING_SUB_INVENTORY_CODE => p_DEBRIEF_LINE_rec.RECEIVING_SUB_INVENTORY_CODE,
2200 p_ISSUING_LOCATOR_ID => p_DEBRIEF_LINE_rec.ISSUING_LOCATOR_ID ,
2201 p_RECEIVING_LOCATOR_ID => p_DEBRIEF_LINE_rec.RECEIVING_LOCATOR_ID ,
2202 p_PARENT_PRODUCT_ID => p_DEBRIEF_LINE_rec.PARENT_PRODUCT_ID ,
2203 p_REMOVED_PRODUCT_ID => p_DEBRIEF_LINE_rec.REMOVED_PRODUCT_ID ,
2204 p_STATUS_OF_RECEIVED_PART => l_cp_status_id,
2205 p_ITEM_SERIAL_NUMBER => p_DEBRIEF_LINE_rec.ITEM_SERIAL_NUMBER ,
2206 p_ITEM_REVISION => p_DEBRIEF_LINE_rec.ITEM_REVISION ,
2207 p_ITEM_LOTNUMBER => p_DEBRIEF_LINE_rec.ITEM_LOTNUMBER ,
2208 p_UOM_CODE => p_debrief_line_rec.uom_code ,
2209 p_QUANTITY => P_DEBRIEF_LINE_Rec.quantity ,
2210 p_RMA_HEADER_ID => p_DEBRIEF_LINE_rec.RMA_HEADER_ID ,
2211 p_DISPOSITION_CODE => p_DEBRIEF_LINE_rec.DISPOSITION_CODE ,
2212 p_MATERIAL_REASON_CODE => p_DEBRIEF_LINE_rec.MATERIAL_REASON_CODE ,
2213 p_LABOR_REASON_CODE => p_DEBRIEF_LINE_rec.LABOR_REASON_CODE ,
2214 p_EXPENSE_REASON_CODE => p_DEBRIEF_LINE_rec.EXPENSE_REASON_CODE ,
2215 p_LABOR_START_DATE => trunc(p_DEBRIEF_LINE_rec.labor_start_date,'MI'),
2216 p_LABOR_END_DATE => trunc(p_DEBRIEF_LINE_rec.labor_end_date,'MI'),
2217 p_STARTING_MILEAGE => p_DEBRIEF_LINE_rec.STARTING_MILEAGE ,
2218 p_ENDING_MILEAGE => p_DEBRIEF_LINE_rec.ENDING_MILEAGE ,
2219 p_EXPENSE_AMOUNT => p_DEBRIEF_LINE_rec.EXPENSE_AMOUNT ,
2220 p_CURRENCY_CODE => p_DEBRIEF_LINE_rec.CURRENCY_CODE ,
2221 p_DEBRIEF_LINE_STATUS_ID => p_DEBRIEF_LINE_rec.DEBRIEF_LINE_STATUS_ID ,
2222 p_return_reason_code => p_debrief_line_rec.return_reason_code,
2223 p_CHANNEL_CODE =>p_DEBRIEF_LINE_rec.CHANNEL_CODE,
2224 p_CHARGE_UPLOAD_STATUS => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_STATUS ,
2225 p_CHARGE_UPLOAD_MSG_CODE => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_MSG_CODE ,
2226 p_CHARGE_UPLOAD_MESSAGE => p_DEBRIEF_LINE_rec.CHARGE_UPLOAD_MESSAGE ,
2227 p_IB_UPDATE_STATUS => p_DEBRIEF_LINE_rec.IB_UPDATE_STATUS ,
2228 p_IB_UPDATE_MSG_CODE => p_DEBRIEF_LINE_rec.IB_UPDATE_MSG_CODE ,
2229 p_IB_UPDATE_MESSAGE => p_DEBRIEF_LINE_rec.IB_UPDATE_MESSAGE ,
2230 p_SPARE_UPDATE_STATUS => p_DEBRIEF_LINE_rec.SPARE_UPDATE_STATUS ,
2231 p_SPARE_UPDATE_MSG_CODE => p_DEBRIEF_LINE_rec.SPARE_UPDATE_MSG_CODE ,
2232 p_SPARE_UPDATE_MESSAGE => p_DEBRIEF_LINE_rec.SPARE_UPDATE_MESSAGE ,
2233 p_CREATED_BY => p_debrief_line_rec.created_by,
2234 p_CREATION_DATE => p_debrief_line_rec.creation_date,
2235 p_LAST_UPDATED_BY => nvl(p_debrief_line_rec.last_updated_by,g_user_id),
2236 p_LAST_UPDATE_DATE => nvl(p_debrief_line_rec.last_update_date,sysdate),
2237 p_LAST_UPDATE_LOGIN => nvl(p_debrief_line_rec.last_update_login,g_login_id),
2238 p_ATTRIBUTE1 => p_DEBRIEF_LINE_rec.ATTRIBUTE1,
2239 p_ATTRIBUTE2 => p_DEBRIEF_LINE_rec.ATTRIBUTE2,
2240 p_ATTRIBUTE3 => p_DEBRIEF_LINE_rec.ATTRIBUTE3,
2241 p_ATTRIBUTE4 => p_DEBRIEF_LINE_rec.ATTRIBUTE4,
2242 p_ATTRIBUTE5 => p_DEBRIEF_LINE_rec.ATTRIBUTE5,
2243 p_ATTRIBUTE6 => p_DEBRIEF_LINE_rec.ATTRIBUTE6,
2244 p_ATTRIBUTE7 => p_DEBRIEF_LINE_rec.ATTRIBUTE7,
2245 p_ATTRIBUTE8 => p_DEBRIEF_LINE_rec.ATTRIBUTE8,
2246 p_ATTRIBUTE9 => p_DEBRIEF_LINE_rec.ATTRIBUTE9,
2247 p_ATTRIBUTE10 => p_DEBRIEF_LINE_rec.ATTRIBUTE10,
2248 p_ATTRIBUTE11 => p_DEBRIEF_LINE_rec.ATTRIBUTE11,
2249 p_ATTRIBUTE12 => p_DEBRIEF_LINE_rec.ATTRIBUTE12,
2250 p_ATTRIBUTE13 => p_DEBRIEF_LINE_rec.ATTRIBUTE13,
2251 p_ATTRIBUTE14 => p_DEBRIEF_LINE_rec.ATTRIBUTE14,
2252 p_ATTRIBUTE15 => p_DEBRIEF_LINE_rec.ATTRIBUTE15,
2253 p_ATTRIBUTE_CATEGORY => p_DEBRIEF_LINE_rec.ATTRIBUTE_CATEGORY,
2254 P_TRANSACTION_TYPE_ID => P_DEBRIEF_LINE_REC.TRANSACTION_TYPE_ID,
2255 p_RETURN_DATE => p_DEBRIEF_LINE_rec.RETURN_DATE,
2256
2257 p_usage_type => p_DEBRIEF_LINE_rec.usage_type,
2258 p_dest_organization_id => p_DEBRIEF_LINE_rec.dest_organization_id,
2259 p_dest_subinventory_name => p_DEBRIEF_LINE_rec.dest_subinventory_name,
2260 p_carrier_code => p_DEBRIEF_LINE_rec.carrier_code,
2261 p_shipping_method => p_DEBRIEF_LINE_rec.shipping_method,
2262 p_shipment_number => p_DEBRIEF_LINE_rec.shipment_number,
2263 p_waybill => p_DEBRIEF_LINE_rec.waybill,
2264
2265 p_expenditure_org_id => p_debrief_line_rec.expenditure_org_id,
2266 p_project_id => p_debrief_line_rec.project_id,
2267 p_project_task_id => p_debrief_line_rec.project_task_id
2268 );
2269
2270 --Update actual times for task and task assignment
2271 if l_debrief_type = 'L' then
2272 update_task_actuals(p_debrief_line_rec.debrief_header_id, -- modified for bug 3748610
2273 x_return_status,
2274 x_msg_count,
2275 x_msg_data);
2276 end if;
2277
2278 -- Calling the Update Resource Location API to Update Resource's Locatio
2279 if l_debrief_type='L' then
2280 Begin
2281 select jta.resource_id,
2282 loc.geometry
2283 into z_resource_id,
2284 z_location
2285 from csf_debrief_lines cdl,
2286 csf_debrief_headers csf,
2287 jtf_task_assignments jta,
2288 jtf_tasks_b jtb,
2289 hz_party_sites p,
2290 hz_locations loc
2291 where csf.debrief_header_id = l_debrief_line_rec.debrief_header_id
2292 and debrief_line_id = l_debrief_line_id
2293 and csf.debrief_header_id = cdl.debrief_header_id
2294 and jta.task_assignment_id = csf.task_assignment_id
2295 and jta.task_id = jtb.task_id
2296 and jtb.address_id = p.party_site_id
2297 and p.location_id = loc.location_id;
2298
2299 select max(actual_start_date)
2300 into z_start_date
2301 from jtf_task_assignments
2302 where resource_id = z_resource_id;
2303
2304 IF p_debrief_line_rec.labor_start_date > z_start_date then
2305 select object_version_number
2306 into z_object_version_number
2307 from jtf_rs_resource_extns
2308 where resource_id = z_resource_id;
2309
2310
2311 /* jtf_rs_resource_pub.update_resource
2312 (P_API_VERSION => 1,
2313 P_INIT_MSG_LIST => fnd_api.g_false,
2314 P_COMMIT => fnd_api.g_false,
2315 P_RESOURCE_ID => z_resource_id,
2316 P_RESOURCE_NUMBER => null,
2317 P_LOCATION => z_location,
2318 P_object_version_num => z_object_version_number,
2319 X_RETURN_STATUS => x_return_status,
2320 X_MSG_COUNT => x_msg_count,
2321 X_MSG_DATA => x_msg_data);
2322 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2323 Raise FND_API.G_EXC_ERROR;
2324 END IF;
2325 */
2326 END IF;
2327
2328 Exception
2329 When NO_DATA_FOUND then
2330 NULL;
2331 End;
2332 End if;
2333 -- Calling the Update Task Assignment API Location API to Update Resource's Location
2334
2335 if P_Upd_tskassgnstatus = 'Y' then
2336
2337 BEGIN
2338 Validate_Task_Assignment_Satus(
2339 P_Api_Version_Number =>1.0,
2340 P_Init_Msg_List => FND_API.G_FALSE,
2341 P_Commit => FND_API.G_FALSE,
2342 P_Task_Assignment_status =>P_Task_Assignment_status,
2343 X_TA_STATUS_ID =>l_assignment_status_id,
2344 X_Return_Status =>X_Return_Status,
2345 X_Msg_Count =>X_MSG_COUNT,
2346 X_Msg_Data =>X_MSG_DATA);
2347 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2348 RAISE FND_API.G_EXC_ERROR;
2349 END IF;
2350
2351 select jta.task_assignment_id,
2352 jta.object_version_number,
2353 jta.task_id
2354 into l_task_assignment_id,
2355 l_object_version_number,
2356 l_task_id
2357 from jtf_task_assignments jta,
2358 csf_debrief_headers csf
2359 where csf.task_assignment_id = jta.task_assignment_id
2360 and csf.debrief_header_id = p_debrief_line_rec.debrief_header_id;
2361
2362
2363 CSF_TASK_ASSIGNMENTS_PUB.UPDATE_ASSIGNMENT_STATUS(
2364 P_API_VERSION =>1.0 ,
2365 P_INIT_MSG_LIST =>FND_API.G_FALSE,
2366 P_COMMIT =>FND_API.G_FALSE,
2367 X_RETURN_STATUS =>x_return_status,
2368 X_MSG_COUNT =>x_msg_count,
2369 X_MSG_DATA =>x_msg_data,
2370 P_TASK_ASSIGNMENT_ID =>l_task_assignment_id,
2371 P_ASSIGNMENT_STATUS_ID =>l_assignment_status_id,
2372 P_OBJECT_VERSION_NUMBER =>l_object_version_number,
2373 P_UPDATE_TASK =>'T',
2374 X_TASK_OBJECT_VERSION_NUMBER =>l_task_object_version_number,
2375 X_TASK_STATUS_ID =>l_task_status_id);
2376 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2377 RAISE FND_API.G_EXC_ERROR;
2378 END IF;
2379
2380 EXCEPTION
2381 WHEN OTHERS THEN
2382 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2383 FND_MESSAGE.Set_Name('CSF', 'CSF_DEBRIEF_UPDATE_TASK_ASSIGNMENT');
2384 FND_MSG_PUB.ADD;
2385 END IF;
2386 RAISE FND_API.G_EXC_ERROR;
2387 END;
2388 end if;
2389
2390 -- Call concurrent program if assignment status is completed,closed,rejected,canceled
2391 open c_status(p_debrief_line_rec.debrief_header_id);
2392 fetch c_status into l_call_cp;
2393 close c_status;
2394 if nvl(l_call_cp,'N') = 'Y' then
2395 l_request_id := fnd_request.submit_request(
2396 'CSF',
2397 'CSFUPDATE',
2398 'CSF:Update Debrief Lines',
2399 null,
2400 FALSE,
2401 1.0,
2402 p_debrief_line_rec.debrief_header_id);
2403 end if;
2404
2405 -- Standard check for p_commit
2406 IF FND_API.to_Boolean( p_commit )
2407 THEN
2408 COMMIT WORK;
2409 END IF;
2410
2411 -- Debug Message
2412 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
2413 THEN
2414 FND_MESSAGE.Set_Name('CSF', l_api_name);
2415 FND_MESSAGE.Set_Token ('INFO', G_PKG_NAME, FALSE);
2416 FND_MSG_PUB.Add;
2417 END IF;
2418
2419 -- Standard call to get message count and if count is 1, get message info.
2420 FND_MSG_PUB.Count_And_Get
2421 ( p_count => x_msg_count,
2422 p_data => x_msg_data
2423 );
2424 end if; -- if charge_upload_status='INTERFACED'
2425 EXCEPTION
2426 WHEN FND_API.G_EXC_ERROR THEN
2427 ROLLBACK TO UPDATE_DEBRIEF_LINE_PVT;
2428 x_return_status := FND_API.G_RET_STS_ERROR;
2429 FND_MSG_PUB.Count_And_Get (
2430 P_COUNT => X_MSG_COUNT
2431 ,P_DATA => X_MSG_DATA);
2432
2433 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2434 ROLLBACK TO UPDATE_DEBRIEF_LINE_PVT;
2435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2436 FND_MSG_PUB.Count_And_Get (
2437 P_COUNT => X_MSG_COUNT
2438 ,P_DATA => X_MSG_DATA);
2439
2440 WHEN OTHERS THEN
2441 ROLLBACK TO UPDATE_DEBRIEF_LINE_PVT;
2442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2443 IF FND_MSG_PUB.Check_Msg_Level
2444 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2445 THEN
2446 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME ,L_API_NAME );
2447 END IF;
2448
2449 FND_MSG_PUB.Count_And_Get (
2450 P_COUNT => X_MSG_COUNT
2451 ,P_DATA => X_MSG_DATA);
2452
2453 End Update_debrief_line;
2454
2455
2456 PROCEDURE Validate_Service_Date (
2457 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2458 P_Service_Date IN DATE,
2459 X_Return_Status OUT NOCOPY VARCHAR2,
2460 X_Msg_Count OUT NOCOPY NUMBER,
2461 X_Msg_Data OUT NOCOPY VARCHAR2
2462 )
2463 IS
2464 BEGIN
2465 log('Validate_Service_Date' ,'P_Service_Date=' || P_Service_Date);
2466 -- Initialize message list if p_init_msg_list is set to TRUE.
2467 IF FND_API.to_Boolean( p_init_msg_list )
2468 THEN
2469 FND_MSG_PUB.initialize;
2470 END IF;
2471
2472 -- Initialize API return status to SUCCESS
2473 x_return_status := FND_API.G_RET_STS_SUCCESS;
2474 IF (p_service_date is NULL OR p_service_date = FND_API.G_MISS_DATE) THEN
2475 x_return_status := FND_API.G_RET_STS_ERROR;
2476 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2477 FND_MESSAGE.Set_Name('CSF', 'CSF_DEBRIEF_SERVICE_DATE');
2478 FND_MSG_PUB.ADD;
2479 END IF;
2480 END IF;
2481
2482 -- Standard call to get message count and if count is 1, get message info.
2483 FND_MSG_PUB.Count_And_Get
2484 ( p_count => x_msg_count,
2485 p_data => x_msg_data
2486 );
2487
2488 END Validate_Service_Date;
2489
2490 PROCEDURE validate_subinventory_code (
2491 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2492 p_organization_id IN number,
2493 p_subinventory_code in varchar2,
2494 X_Return_Status OUT NOCOPY VARCHAR2,
2495 X_Msg_Count OUT NOCOPY NUMBER,
2496 X_Msg_Data OUT NOCOPY VARCHAR2
2497 )
2498 IS
2499 l_organization_code varchar2(30) := null;
2500 l_subinventory_code varchar2(30) := null;
2501
2502 cursor c_subinventory_code is
2503 select mp.organization_code,
2504 msi.secondary_inventory_name
2505 from mtl_secondary_inventories msi,
2506 mtl_parameters mp
2507 where mp.organization_id = p_organization_id
2508 and msi.organization_id = mp.organization_id
2509 and secondary_inventory_name = p_subinventory_code;
2510
2511 BEGIN
2512 -- Initialize message list if p_init_msg_list is set to TRUE.
2513 IF FND_API.to_Boolean( p_init_msg_list )
2514 THEN
2515 FND_MSG_PUB.initialize;
2516 END IF;
2517
2518 -- Initialize API return status to SUCCESS
2519 x_return_status := FND_API.G_RET_STS_SUCCESS;
2520 open c_subinventory_code;
2521 fetch c_subinventory_code into l_organization_code,l_subinventory_code;
2522 close c_subinventory_code;
2523 IF l_subinventory_code is NULL THEN
2524 x_return_status := FND_API.G_RET_STS_ERROR;
2525 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2526 FND_MESSAGE.Set_Name('CSP', 'CSP_INVALID_ORG_SUBINV');
2527 FND_MESSAGE.set_token('ORG',l_organization_code,TRUE);
2528 FND_MESSAGE.set_token('SUBINV',l_subinventory_code,TRUE);
2529 FND_MSG_PUB.ADD;
2530 END IF;
2531 END IF;
2532
2533 -- Standard call to get message count and if count is 1, get message info.
2534 FND_MSG_PUB.Count_And_Get
2535 ( p_count => x_msg_count,
2536 p_data => x_msg_data);
2537
2538 END validate_subinventory_code;
2539
2540 PROCEDURE Validate_Inventory_Item_ID (
2541 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2542 p_organization_id IN NUMBER,
2543 P_Inventory_Item_ID IN NUMBER,
2544 X_Return_Status OUT NOCOPY VARCHAR2,
2545 X_Msg_Count OUT NOCOPY NUMBER,
2546 X_Msg_Data OUT NOCOPY VARCHAR2) IS
2547
2548 l_inventory_item_id number;
2549 cursor c_inventory_item is
2550 select msib.inventory_item_id
2551 from mtl_system_items_b msib,cs_billing_type_categories cbtc
2552 where msib.organization_id = nvl(p_organization_id,fnd_profile.value('CS_INV_VALIDATION_ORG'))
2553 and msib.inventory_item_id = p_inventory_item_id
2554 and msib.material_billable_flag = cbtc.billing_type;
2555
2556 BEGIN
2557 -- Initialize message list if p_init_msg_list is set to TRUE.
2558 IF FND_API.to_Boolean( p_init_msg_list )
2559 THEN
2560 FND_MSG_PUB.initialize;
2561 END IF;
2562
2563 -- Initialize API return status to SUCCESS
2564 x_return_status := FND_API.G_RET_STS_SUCCESS;
2565 l_inventory_item_id := null;
2566 open c_inventory_item;
2567 fetch c_inventory_item into l_inventory_item_id;
2568 close c_inventory_item;
2569 IF l_inventory_item_id is null THEN
2570 x_return_status := FND_API.G_RET_STS_ERROR;
2571 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2572 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_INVENTORY_ITEM_ID');
2573 FND_MSG_PUB.ADD;
2574 END IF;
2575 END IF;
2576 -- Standard call to get message count and if count is 1, get message info.
2577 FND_MSG_PUB.Count_And_Get
2578 ( p_count => x_msg_count,
2579 p_data => x_msg_data
2580 );
2581
2582 END Validate_Inventory_Item_ID;
2583
2584 PROCEDURE Validate_Instance_ID (
2585 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2586 P_Instance_ID IN NUMBER,
2587 X_Return_Status OUT NOCOPY VARCHAR2,
2588 X_Msg_Count OUT NOCOPY NUMBER,
2589 X_Msg_Data OUT NOCOPY VARCHAR2
2590 )
2591 IS
2592 l_dummy number;
2593
2594 cursor get_INSTANCE_ID is
2595 select 1
2596 from csi_item_instances
2597 where INSTANCE_ID=P_INSTANCE_ID;
2598
2599 Begin
2600
2601 -- Initialize message list if p_init_msg_list is set to TRUE.
2602 IF FND_API.to_Boolean( p_init_msg_list )
2603 THEN
2604 FND_MSG_PUB.initialize;
2605 END IF;
2606
2607 -- Initialize API return status to SUCCESS
2608 x_return_status := FND_API.G_RET_STS_SUCCESS;
2609 IF (p_instance_id is NOT NULL and p_instance_id <> FND_API.G_MISS_NUM ) THEN
2610 open get_instance_id;
2611 fetch get_instance_id into l_dummy;
2612 if get_instance_id%notfound then
2613 close get_instance_id;
2614 x_return_status := FND_API.G_RET_STS_ERROR;
2615 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2616 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_INSTANCE_ID');
2617 FND_MSG_PUB.ADD;
2618 END IF;
2619 end if;
2620 close get_instance_id;
2621 END IF;
2622
2623 -- Standard call to get message count and if count is 1, get message info.
2624 FND_MSG_PUB.Count_And_Get
2625 ( p_count => x_msg_count,
2626 p_data => x_msg_data
2627 );
2628 END Validate_Instance_ID;
2629
2630 PROCEDURE Validate_Debrief_Header_ID (
2631 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2632 P_Debrief_Header_ID IN NUMBER,
2633 X_Return_Status OUT NOCOPY VARCHAR2,
2634 X_Msg_Count OUT NOCOPY NUMBER,
2635 X_Msg_Data OUT NOCOPY VARCHAR2
2636 )
2637 IS
2638 l_dummy number;
2639 cursor validate_header_id is
2640 select 1
2641 from csf_debrief_headers
2642 where debrief_header_id=p_debrief_header_id;
2643 BEGIN
2644
2645 -- Initialize message list if p_init_msg_list is set to TRUE.
2646 IF FND_API.to_Boolean( p_init_msg_list )
2647 THEN
2648 FND_MSG_PUB.initialize;
2649 END IF;
2650
2651 -- Initialize API return status to SUCCESS
2652 x_return_status := FND_API.G_RET_STS_SUCCESS;
2653 IF (P_DEBRIEF_HEADER_ID is NULL OR P_DEBRIEF_HEADER_ID = FND_API.G_MISS_NUM) THEN
2654 x_return_status := FND_API.G_RET_STS_ERROR;
2655 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2656 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_HEADER_ID');
2657 FND_MSG_PUB.ADD;
2658 END IF;
2659 ELSE
2660 open validate_header_id;
2661 fetch validate_header_id into l_dummy;
2662 if validate_header_id%notfound then
2663 -- close validate_header_id;
2664 x_return_status := FND_API.G_RET_STS_ERROR;
2665 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2666 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_HEADER_ID');
2667 FND_MSG_PUB.ADD;
2668 END IF;
2669 end if;
2670 close validate_header_id ;
2671 END IF;
2672 -- Standard call to get message count and if count is 1, get message info.
2673 FND_MSG_PUB.Count_And_Get
2674 ( p_count => x_msg_count,
2675 p_data => x_msg_data
2676 );
2677
2678 END Validate_DEBRIEF_HEADER_ID;
2679
2680
2681
2682 PROCEDURE Validate_DEBRIEF_LINE_NUMBER (
2683 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2684 P_Validation_mode IN VARCHAR2,
2685 P_DEBRIEF_LINE_NUMBER IN NUMBER,
2686 X_Return_Status OUT NOCOPY VARCHAR2,
2687 X_Msg_Count OUT NOCOPY NUMBER,
2688 X_Msg_Data OUT NOCOPY VARCHAR2
2689 )
2690 IS
2691 l_api_name varchar2(30) := 'Create Debrief Line' ;
2692 BEGIN
2693 -- Initialize message list if p_init_msg_list is set to TRUE.
2694 IF FND_API.to_Boolean( p_init_msg_list )
2695 THEN
2696 FND_MSG_PUB.initialize;
2697 END IF;
2698
2699 -- Initialize API return status to SUCCESS
2700 x_return_status := FND_API.G_RET_STS_SUCCESS;
2701 IF(p_validation_mode = G_CREATE) THEN
2702 IF (p_debrief_line_number is NULL OR p_debrief_line_number = FND_API.G_MISS_NUM ) THEN
2703 x_return_status := FND_API.G_RET_STS_ERROR;
2704 ELSIF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2705 THEN
2706 FND_MESSAGE.Set_Name('CSF', l_api_name);
2707 FND_MESSAGE.Set_Token('COLUMN', 'DEBRIEF_LINE_NUMBER', FALSE);
2708 FND_MSG_PUB.ADD;
2709 END IF;
2710 END IF;
2711
2712 -- Standard call to get message count and if count is 1, get message info.
2713 FND_MSG_PUB.Count_And_Get
2714 ( p_count => x_msg_count,
2715 p_data => x_msg_data
2716 );
2717
2718 END Validate_DEBRIEF_LINE_NUMBER;
2719
2720 PROCEDURE Validate_BUSINESS_PROCESS_ID (
2721 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2722 P_BUSINESS_PROCESS_ID IN NUMBER,
2723 X_Return_Status OUT NOCOPY VARCHAR2,
2724 X_Msg_Count OUT NOCOPY NUMBER,
2725 X_Msg_Data OUT NOCOPY VARCHAR2
2726 )
2727 IS
2728 l_dummy number;
2729 cursor get_BUSINESS_PROCESS_ID is
2730 select 1
2731 from cs_business_processes
2732 where BUSINESS_PROCESS_ID=P_BUSINESS_PROCESS_ID;
2733 BEGIN
2734 -- Initialize message list if p_init_msg_list is set to TRUE.
2735 IF FND_API.to_Boolean( p_init_msg_list )
2736 THEN
2737 FND_MSG_PUB.initialize;
2738 END IF;
2739
2740 -- Initialize API return status to SUCCESS
2741 x_return_status := FND_API.G_RET_STS_SUCCESS;
2742 IF (p_business_process_id is NULL OR p_business_process_id = FND_API.G_MISS_NUM ) THEN
2743 x_return_status := FND_API.G_RET_STS_ERROR;
2744 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2745 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_BUS_PROCESS_ID');
2746 FND_MSG_PUB.ADD;
2747 END IF;
2748 ELSE
2749 open get_business_process_id;
2750 fetch get_business_process_id into l_dummy;
2751 if get_business_process_id%notfound then
2752 close get_business_process_id;
2753 x_return_status := FND_API.G_RET_STS_ERROR;
2754 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2755 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_BUS_PROCESS_ID');
2756 FND_MSG_PUB.ADD;
2757 END IF;
2758 end if;
2759 close get_business_process_id;
2760 END IF;
2761
2762 -- Standard call to get message count and if count is 1, get message info.
2763 FND_MSG_PUB.Count_And_Get
2764 ( p_count => x_msg_count,
2765 p_data => x_msg_data
2766 );
2767 END Validate_BUSINESS_PROCESS_ID;
2768 PROCEDURE Validate_TRANSACTION_TYPE_ID (
2769 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2770 P_TRANSACTION_TYPE_ID IN NUMBER,
2771 X_Return_Status OUT NOCOPY VARCHAR2,
2772 X_Msg_Count OUT NOCOPY NUMBER,
2773 X_Msg_Data OUT NOCOPY VARCHAR2
2774 )
2775 IS
2776 l_dummy number;
2777 cursor get_transaction_type_id is
2778 select 1
2779 from cs_transaction_types vl
2780 where transaction_type_id=P_Transaction_type_id;
2781 BEGIN
2782 -- Initialize message list if p_init_msg_list is set to TRUE.
2783 IF FND_API.to_Boolean( p_init_msg_list )
2784 THEN
2785 FND_MSG_PUB.initialize;
2786 END IF;
2787
2788 -- Initialize API return status to SUCCESS
2789 x_return_status := FND_API.G_RET_STS_SUCCESS;
2790 IF (p_transaction_type_id is NULL OR p_transaction_type_id = FND_API.G_MISS_NUM ) THEN
2791 x_return_status := FND_API.G_RET_STS_ERROR;
2792 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2793 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_TXN_BILLING_TYP_ID');
2794 FND_MSG_PUB.ADD;
2795 END IF;
2796 ELSE
2797 open get_transaction_type_id;
2798 fetch get_transaction_type_id into l_dummy;
2799 if get_transaction_type_id%notfound then
2800 close get_transaction_type_id;
2801 x_return_status := FND_API.G_RET_STS_ERROR;
2802 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2803 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_TXN_BILLING_TYP_ID');
2804 FND_MSG_PUB.ADD;
2805 END IF;
2806 end if;
2807 close get_transaction_type_id;
2808 END IF;
2809
2810 -- Standard call to get message count and if count is 1, get message info.
2811 FND_MSG_PUB.Count_And_Get
2812 ( p_count => x_msg_count,
2813 p_data => x_msg_data
2814 );
2815 END Validate_TRANSACTION_TYPE_ID;
2816
2817
2818 Procedure CREATE_INTERACTION(P_Api_Version_Number IN NUMBER,
2819 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2820 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2821 P_TASK_ASSIGNMENT_ID IN NUMBER,
2822 P_DEBRIEF_HEADER_ID IN NUMBER,
2823 P_MEDIA_ID IN NUMBER,
2824 P_ACTION_ID IN NUMBER,
2825 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2826 X_Msg_Count OUT NOCOPY NUMBER,
2827 X_Msg_Data OUT NOCOPY VARCHAR2) is
2828
2829 l_api_version_number number :=1.0;
2830 l_api_name varchar2(50):='CREATE_INTERACTION';
2831 l_interaction_rec JTF_IH_PUB.Interaction_Rec_Type;
2832 l_activity_rec JTF_IH_PUB.Activity_Rec_Type;
2833 l_task_id number;
2834 l_party_id number;
2835 l_cust_account_id number;
2836 l_resource_id number;
2837 l_msg_count number;
2838 l_msg_data varchar2(200);
2839 l_return_status varchar2(20);
2840 l_interaction_id number;
2841 l_activity_id number;
2842 l_msg_index_out number;
2843 Begin
2844 -- Standard Start of API savepoint
2845 SAVEPOINT CREATE_INTERACTION;
2846 -- Standard call to check for call compatibility.
2847
2848 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2849 p_api_version_number,
2850 l_api_name,
2851 G_PKG_NAME)
2852 THEN
2853 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2854 END IF;
2855
2856
2857 -- Initialize message list if p_init_msg_list is set to TRUE.
2858 IF FND_API.to_Boolean( p_init_msg_list )
2859 THEN
2860 FND_MSG_PUB.initialize;
2861 END IF;
2862
2863 -- Initialize API return status to SUCCESS
2864 x_return_status := FND_API.G_RET_STS_SUCCESS;
2865
2866 select jtb.task_id,
2867 jtb.customer_id,
2868 jtb.cust_account_id,
2869 jta.resource_id
2870 into l_task_id,
2871 l_party_id,
2872 l_cust_account_id,
2873 l_resource_id
2874 from jtf_task_assignments jta,
2875 jtf_tasks_b jtb
2876 where jta.task_id = jtb.task_id
2877 and jta.task_assignment_id = P_task_assignment_id;
2878
2879 -- Virendra Singh 03/21/2000
2880 -- Initialize interaction record
2881
2882 l_interaction_rec.handler_id := 747;
2883 l_interaction_rec.outcome_id := 7;
2884 l_interaction_rec.resource_id := l_resource_id;
2885 l_interaction_rec.party_id := l_party_id;
2886 -- open interaction
2887 jtf_ih_pub.Open_Interaction(
2888 p_api_version => 1.0,
2889 p_init_msg_list => FND_API.G_TRUE,
2890 p_commit => FND_API.G_FALSE,
2891 p_user_id => G_USER_ID,
2892 x_return_status =>l_return_status,
2893 x_msg_count =>l_msg_count,
2894 x_msg_data =>l_msg_data,
2895 p_interaction_rec =>l_interaction_rec,
2896 x_interaction_id =>l_interaction_id );
2897 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2898 RAISE FND_API.G_EXC_ERROR;
2899 END IF;
2900
2901 FND_MSG_PUB.GET(P_MSG_INDEX=>1,
2902 P_ENCODED=>'F',
2903 p_data=>l_msg_data,
2904 p_MSG_index_out=>l_msg_index_out);
2905
2906 -- Initialise activity record
2907 l_activity_rec.cust_account_id :=l_cust_account_id;
2908 l_activity_rec.task_id :=l_task_id;
2909 l_activity_rec.doc_id :=P_debrief_header_id;
2910 l_activity_rec.doc_ref :='DEBRIEF_HEADER';
2911 l_activity_rec.media_id :=P_MEDIA_ID;
2912 l_activity_rec.action_item_id :='FSR';
2913 l_activity_rec.interaction_id :=l_interaction_id;
2914 l_activity_rec.outcome_id :=7 ;
2915 l_activity_rec.action_id :=P_ACTION_ID;
2916 l_return_status:=NULL;
2917 -- add activity
2918 jtf_ih_pub.add_activity(
2919 p_api_version => 1.0,
2920 p_init_msg_list => FND_API.G_FALSE,
2921 p_commit => FND_API.G_FALSE,
2922 p_user_id =>G_USER_ID,
2923 x_return_status =>l_return_status,
2924 x_msg_count =>l_msg_count,
2925 x_msg_data =>l_msg_data,
2926 p_activity_rec =>l_activity_rec,
2927 x_activity_id =>l_activity_id);
2928 if l_return_status<>FND_API.G_RET_STS_SUCCESS then
2929 raise FND_API.G_EXC_ERROR;
2930 end if;
2931 -- close interaction
2932 l_return_status:=NULL;
2933 jtf_ih_pub.Close_Interaction(
2934 p_api_version => 1.0,
2935 p_init_msg_list => FND_API.G_FALSE,
2936 p_commit => FND_API.G_FALSE,
2937 p_user_id => G_USER_ID,
2938 x_return_status =>l_return_status,
2939 x_msg_count =>l_msg_count,
2940 x_msg_data =>l_msg_data,
2941 p_interaction_rec =>l_interaction_rec );
2942
2943 if l_return_status<>FND_API.G_RET_STS_SUCCESS then
2944 raise FND_API.G_EXC_ERROR;
2945 end if;
2946 EXCEPTION
2947 WHEN FND_API.G_EXC_ERROR THEN
2948 ROLLBACK TO CREATE_INTERACTION;
2949 x_return_status := FND_API.G_RET_STS_ERROR;
2950 FND_MSG_PUB.Count_And_Get (
2951 P_COUNT => X_MSG_COUNT
2952 ,P_DATA => X_MSG_DATA);
2953
2954 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2955 ROLLBACK TO CREATE_INTERACTION;
2956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2957 FND_MSG_PUB.Count_And_Get (
2958 P_COUNT => X_MSG_COUNT
2959 ,P_DATA => X_MSG_DATA);
2960
2961 WHEN OTHERS THEN
2962 ROLLBACK TO CREATE_INTERACTION;
2963 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2964 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2965 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,L_API_NAME);
2966 END IF;
2967 FND_MSG_PUB.Count_And_Get (
2968 P_COUNT => X_MSG_COUNT
2969 ,P_DATA => X_MSG_DATA);
2970
2971 END;
2972
2973 PROCEDURE UPDATE_TASK_ACTUAL_DATES (
2974 p_task_id IN NUMBER,
2975 p_actual_start_date IN DATE,
2976 p_actual_end_date IN DATE,
2977 x_return_status OUT NOCOPY VARCHAR2,
2978 x_msg_count OUT NOCOPY NUMBER,
2979 x_msg_data OUT NOCOPY VARCHAR2
2980 ) IS
2981 l_version number;
2982 cursor c_version is
2983 SELECT OBJECT_VERSION_NUMBER
2984 FROM JTF_TASKS_VL
2985 WHERE TASK_ID = p_task_id;
2986 BEGIN
2987 OPEN C_VERSION;
2988 FETCH C_VERSION INTO l_version;
2989 CLOSE C_VERSION;
2990
2991
2992 csf_tasks_pub.update_task (
2993 p_api_version => 1.0,
2994 p_object_version_number => l_version,
2995 p_task_id => p_task_id,
2996 p_actual_start_date => p_actual_start_date,
2997 p_actual_end_date => p_actual_end_date,
2998 x_return_status => x_return_status,
2999 x_msg_count => x_msg_count,
3000 x_msg_data => x_msg_data
3001 ) ;
3002
3003
3004
3005 END;
3006
3007 procedure update_actual_times (
3008 p_task_assignment_id in number,
3009 x_return_status out nocopy varchar2,
3010 x_msg_count out nocopy number,
3011 x_msg_data out nocopy varchar2) IS
3012
3013 cursor c_actual_times is
3014 select max(cdh.debrief_header_id),
3015 min(cdl.labor_start_date),
3016 max(cdl.labor_end_date)
3017 from csf_debrief_headers cdh,
3018 csf_debrief_lines cdl
3019 where cdh.task_assignment_id = p_task_assignment_id
3020 and cdl.debrief_header_id = cdh.debrief_header_id
3021 and cdl.labor_start_date is not null
3022 and cdl.labor_end_date is not null;
3023
3024 cursor c_Debrief_header_for_cleanup is
3025 select cdh.debrief_header_id
3026 from csf_debrief_headers cdh,jtf_task_assignments jta
3027 where cdh.task_assignment_id = jta.task_assignment_id
3028 and jta.task_assignment_id =p_task_assignment_id
3029 and (jta.actual_start_date is not null
3030 or jta.actual_end_date is not null
3031 or jta.actual_effort is not null);
3032
3033 l_debrief_header_id number;
3034 l_labor_start_date date;
3035 l_labor_end_date date;
3036
3037 begin
3038 x_return_status := FND_API.G_RET_STS_SUCCESS;
3039 open c_actual_times;
3040 fetch c_actual_times into l_debrief_header_id,
3041 l_labor_start_date,
3042 l_labor_end_date;
3043 close c_actual_times;
3044
3045 if l_debrief_header_id is null then
3046 open c_Debrief_header_for_cleanup;
3047 fetch c_Debrief_header_for_cleanup into l_debrief_header_id;
3048
3049 close c_Debrief_header_for_cleanup;
3050 end if;
3051 if l_debrief_header_id is not null then
3052 update_task_actuals(l_debrief_header_id,
3053 x_return_status,
3054 x_msg_count,
3055 x_msg_data);
3056 end if;
3057 end;
3058
3059 procedure update_actual_times (
3060 p_debrief_header_id in number,
3061 p_start_date in date,
3062 p_end_date in date,
3063 x_return_status out nocopy varchar2,
3064 x_msg_count out nocopy number,
3065 x_msg_data out nocopy varchar2) IS
3066
3067 l_task_id number := null;
3068 l_task_assignment_id number := null;
3069 l_start_date date := null;
3070 l_end_date date := null;
3071 l_object_version number := null;
3072 l_resource_type_code varchar2(30) := null;
3073 l_resource_id number := null;
3074 l_task_object_version number := null;
3075 l_task_status_id number := null;
3076
3077 cursor c_task_assignment is
3078 select task_id,
3079 jta.task_assignment_id,
3080 least(nvl(actual_start_date,p_start_date),
3081 nvl(p_start_date,fnd_api.g_miss_date)),
3082 greatest(nvl(actual_end_date,p_end_date),
3083 nvl(p_end_date,fnd_api.g_miss_date)),
3084 jta.object_version_number,
3085 jta.resource_type_code,
3086 jta.resource_id
3087 from jtf_task_assignments jta,
3088 csf_debrief_headers cdh
3089 where debrief_header_id = p_debrief_header_id
3090 and jta.task_assignment_id = cdh.task_assignment_id
3091 and (nvl(actual_start_date,p_start_date) >=
3092 nvl(p_start_date,actual_start_date)
3093 or nvl(actual_end_date,p_end_date) <=
3094 nvl(p_end_date,actual_end_date));
3095
3096 cursor c_task is
3097 select least(nvl(actual_start_date,p_start_date),
3098 nvl(p_start_date,fnd_api.g_miss_date)),
3099 greatest(nvl(actual_end_date,p_end_date),
3100 nvl(p_end_date,fnd_api.g_miss_date)),
3101 object_version_number
3102 from jtf_tasks_b
3103 where task_id = l_task_id
3104 and (nvl(actual_start_date,p_start_date) >=
3105 nvl(p_start_date,actual_start_date)
3106 or nvl(actual_end_date,p_end_date) <=
3107 nvl(p_end_date,actual_end_date));
3108
3109 BEGIN
3110 -- Initialize API return status to SUCCESS
3111 x_return_status := FND_API.G_RET_STS_SUCCESS;
3112
3113 open c_task_assignment;
3114 fetch c_task_assignment into l_task_id,
3115 l_task_assignment_id,
3116 l_start_date,
3117 l_end_date,
3118 l_object_version,
3119 l_resource_type_code,
3120 l_resource_id;
3121 close c_task_assignment;
3122
3123 if l_object_version is not null then
3124 csf_task_assignments_pub.update_task_assignment (
3125 p_api_version => 1.0,
3126 x_return_status => x_return_status,
3127 x_msg_count => x_msg_count,
3128 x_msg_data => x_msg_data,
3129 p_task_assignment_id => l_task_assignment_id,
3130 p_object_version_number => l_object_version,
3131 p_actual_start_date => l_start_date,
3132 p_actual_end_date => l_end_date,
3133 p_resource_type_code => l_resource_type_code,
3134 p_resource_id => l_resource_id,
3135 x_task_object_version_number => l_task_object_version,
3136 x_task_status_id => l_task_status_id);
3137
3138 l_object_version := null;
3139
3140 open c_task;
3141 fetch c_task into l_start_date,l_end_date,l_object_version;
3142 close c_task;
3143
3144 if l_object_version is not null then
3145 csf_tasks_pub.update_task (
3146 p_api_version => 1.0,
3147 p_object_version_number => l_object_version,
3148 p_task_id => l_task_id,
3149 p_actual_start_date => l_start_date,
3150 p_actual_end_date => l_end_date,
3151 x_return_status => x_return_status,
3152 x_msg_count => x_msg_count,
3153 x_msg_data => x_msg_data) ;
3154 end if;
3155 end if;
3156
3157 END;
3158 PROCEDURE VALIDATE_COUNTERS (
3159 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3160 p_task_id in number,
3161 p_incident_id in number,
3162 x_return_status out nocopy varchar2,
3163 x_msg_count out nocopy number,
3164 x_msg_data out nocopy varchar2
3165 ) is
3166 l_value_timestamp date;
3167 l_task_creation_date date;
3168 l_customer_product_id number;
3169
3170 cursor c_customer_product is
3171 select customer_product_id
3172 from cs_incidents_all_b
3173 where incident_id = p_incident_id;
3174
3175 cursor c_counter is
3176 select max(ccv.value_timestamp),
3177 max(jtb.creation_date)
3178 from csi_counter_associations cca,
3179 cs_counter_values ccv,
3180 jtf_tasks_b jtb
3181 where cca.source_object_id = l_customer_product_id
3182 and cca.source_object_code = 'CP'
3183 and ccv.counter_id(+)= cca.counter_id
3184 and jtb.task_id = p_task_id;
3185
3186 begin
3187
3188 open c_customer_product;
3189 fetch c_customer_product into l_customer_product_id;
3190 close c_customer_product;
3191
3192 open c_counter;
3193 fetch c_counter into l_value_timestamp, l_task_creation_date;
3194 close c_counter;
3195
3196 if l_value_timestamp >= l_task_creation_date Then
3197 x_return_status := 'S' ;
3198 else
3199 x_return_status := 'E';
3200 end if;
3201 end;
3202
3203
3204 PROCEDURE VALIDATE_LABOR_TIMES (
3205 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
3206 P_api_version_number In number,
3207 p_resource_type_code in Varchar2,
3208 p_resource_id in Number,
3209 p_debrief_line_id in Number,
3210 p_labor_start_date in Date,
3211 p_labor_end_date in Date,
3212 p_task_id in Number,
3213 x_return_status out nocopy varchar2,
3214 x_msg_count out nocopy number,
3215 x_msg_data out nocopy varchar2,
3216 x_debrief_number out nocopy number,
3217 x_task_number out nocopy varchar2
3218 ) is
3219
3220
3221 l_labor_start_date date;
3222 l_labor_end_date date;
3223 l_profile varchar2(10) := fnd_profile.value('CSF_DEBRIEF_OVERLAPPING_LABOR');
3224
3225 cursor c_val is
3226 Select cdh.debrief_number,
3227 cdl.labor_start_date,
3228 cdl.labor_end_date,
3229 jtb.task_number
3230 from csf_debrief_lines cdl,
3231 csf_debrief_headers cdh,
3232 jtf_task_assignments jta ,
3233 jtf_tasks_b jtb
3234 where cdh.debrief_header_id = cdl.debrief_header_id
3235 and jta.task_assignment_id = cdh.task_assignment_id
3236 and cdl.labor_start_date is not null
3237 and ((p_debrief_line_id is not null
3238 and cdl.debrief_line_id <> p_debrief_line_id)
3239 or (p_debrief_line_id is null))
3240 and jta.resource_type_code = p_resource_type_code
3241 and jta.resource_id = p_resource_id
3242 and jtb.task_id = jta.task_id
3243 and jtb.task_id <> p_task_id
3244 and (p_labor_start_date >= cdl.labor_start_date and p_labor_start_date < cdl.labor_end_date
3245 or p_labor_end_date > cdl.labor_start_date and p_labor_end_date < cdl.labor_end_date
3246 or cdl.labor_start_date > p_labor_start_date and cdl.labor_start_date < p_labor_end_date
3247 or cdl.labor_end_date > p_labor_start_date and cdl.labor_end_date < p_labor_end_date);
3248
3249
3250 begin
3251
3252 if l_profile ='N' Then --not allowed, needs to be checked
3253
3254 open c_val;
3255 fetch c_val into x_debrief_number,
3256 l_labor_start_date,
3257 l_labor_end_date,
3258 x_task_number;
3259
3260 if c_val%notfound Then x_return_status := 'S' ;
3261 x_msg_data := 'Overlapping is not allowed and there are no labor lines overlapping';
3262 else x_return_status :='E';
3263 x_msg_data := 'Overlapping is not allowed and there are labor lines overlapping';
3264 end if;
3265 close c_val;
3266 ELSE x_return_status:='S';
3267 x_msg_data := 'Overlapping is allowed. There might be overlapping lines';
3268
3269 end if;
3270
3271
3272 end;
3273
3274 PROCEDURE TASK_ASSIGNMENT_PRE_UPDATE(x_return_status out nocopy varchar2) is
3275 l_task_assignment_id number := null;
3276 l_old_resource_type varchar2(100) := null;
3277 l_old_resource_id number := null;
3278 l_new_resource_type varchar2(100) := null;
3279 l_new_resource_id number := null;
3280 l_debrief_header_id number := null;
3281 begin
3282 x_return_status := FND_API.G_RET_STS_SUCCESS;
3283 l_task_assignment_id :=
3284 JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_assignment_id;
3285 l_new_resource_id :=
3286 JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.resource_id;
3287 l_new_resource_type :=
3288 JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.resource_type_code;
3289
3290 select cdh.debrief_header_id,resource_type_code,resource_id
3291 into l_debrief_header_id,l_old_resource_type,l_old_resource_id
3292 from csf_debrief_headers cdh,jtf_task_assignments jta,csf_debrief_lines cdl
3293 where jta.task_assignment_id = l_task_assignment_id
3294 and cdl.debrief_header_id = cdh.debrief_header_id
3295 and cdh.task_assignment_id = jta.task_assignment_id
3296 and rownum = 1;
3297
3298 if l_debrief_header_id is not null then
3299 if l_old_resource_type <> l_new_resource_type or
3300 l_old_resource_id <> l_new_resource_id then
3301 x_return_status := FND_API.G_RET_STS_ERROR;
3302 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_EXISTS');
3303 FND_MSG_PUB.ADD;
3304 end if;
3305 end if;
3306
3307 exception
3308 when others then
3309 null;
3310 end;
3311
3312 PROCEDURE TASK_ASSIGNMENT_PRE_DELETE(x_return_status out nocopy varchar2) is
3313 l_task_assignment_id number := null;
3314 l_debrief_header_id number := null;
3315 begin
3316 x_return_status := FND_API.G_RET_STS_SUCCESS;
3317 l_task_assignment_id := JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_assignment_id;
3318 select debrief_header_id
3319 into l_debrief_header_id
3320 from csf_debrief_headers
3321 where task_assignment_id = l_task_assignment_id;
3322
3323 if l_debrief_header_id is not null then
3324 x_return_status := FND_API.G_RET_STS_ERROR;
3325 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_EXISTS');
3326 FND_MSG_PUB.ADD;
3327 end if;
3328
3329 exception
3330 when others then
3331 null;
3332 end;
3333
3334 PROCEDURE TASK_ASSIGNMENT_POST_UPDATE(x_return_status out nocopy varchar2) is
3335 l_msg_data varchar(2000);
3336 l_msg_count number;
3337 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
3338 l_travel_start_date date;
3339 l_travel_end_date date;
3340 l_travel_exists varchar2(1);
3341 l_working_start_date date;
3342 l_working_end_date date;
3343 l_working_exists varchar2(1);
3344 l_debrief_header_id number;
3345 l_record_counter number := 1;
3346 l_task_assignment_id number;
3347 l_transaction_type_id number;
3348 l_business_process_id number;
3349 l_resource_type_code varchar2(60);
3350 l_resource_id number;
3351 l_assignment_status_id number;
3352 l_working_flag varchar2(1);
3353 l_travel_flag varchar2(1);
3354 l_task_id number;
3355 l_task_type_rule varchar2(60);
3356 l_any_working_task varchar2(1) := 'N';
3357
3358 l_debrief_header csf_debrief_pub.debrief_rec_type;
3359 l_debrief_line csf_debrief_pub.debrief_line_tbl_type;
3360
3361 cursor c_header_id(p_task_assignment_id number) is
3362 select debrief_header_id
3363 from csf_debrief_headers
3364 where task_assignment_id = p_task_assignment_id;
3365
3366 cursor c_travel_exists(p_task_assignment_id number) is
3367 select 'Y'
3368 from csf_debrief_headers cdh,
3369 csf_debrief_lines cdl,
3370 cs_transaction_types ctt,
3371 cs_txn_billing_types ctbt
3372 where cdh.task_assignment_id = p_task_assignment_id
3373 and cdl.debrief_header_id = cdh.debrief_header_id
3374 and ctt.transaction_type_id = cdl.transaction_type_id
3375 and ctbt.transaction_type_id = ctt.transaction_type_id
3376 and ctbt.billing_type = 'L'
3377 and ctt.travel_flag = 'Y';
3378
3379 cursor c_working_exists(p_task_assignment_id number) is
3380 select 'Y'
3381 from csf_debrief_headers cdh,
3382 csf_debrief_lines cdl,
3383 cs_transaction_types ctt,
3384 cs_txn_billing_types ctbt
3385 where cdh.task_assignment_id = p_task_assignment_id
3386 and cdl.debrief_header_id = cdh.debrief_header_id
3387 and ctt.transaction_type_id = cdl.transaction_type_id
3388 and ctbt.transaction_type_id = ctt.transaction_type_id
3389 and ctbt.billing_type = 'L'
3390 and nvl(ctt.travel_flag,'N') = 'N';
3391
3392 cursor c_audit_travel(p_task_assignment_id number) is
3393 select jtsbold.travel_flag old_travel_flag,
3394 jtsbnew.travel_flag new_travel_flag,
3395 trunc(jtaab.creation_date,'MI') creation_date
3396 from jtf_task_assignments_audit_b jtaab,
3397 jtf_task_statuses_b jtsbnew,
3398 jtf_task_statuses_b jtsbold
3399 where jtsbold.task_status_id = jtaab.old_assignment_status_id
3400 and jtsbnew.task_status_id = jtaab.new_assignment_status_id
3401 and jtaab.old_assignment_status_id <> jtaab.new_assignment_status_id
3402 and jtaab.assignment_id = p_task_assignment_id
3403 order by jtaab.creation_date desc;
3404
3405 cursor c_audit_working(p_task_assignment_id number) is
3406 select jtsbold.working_flag old_working_flag,
3407 jtsbnew.working_flag new_working_flag,
3408 trunc(jtaab.creation_date,'MI') creation_date
3409 from jtf_task_assignments_audit_b jtaab,
3410 jtf_task_statuses_b jtsbnew,
3411 jtf_task_statuses_b jtsbold
3412 where jtsbold.task_status_id = jtaab.old_assignment_status_id
3413 and jtsbnew.task_status_id = jtaab.new_assignment_status_id
3414 and jtaab.old_assignment_status_id <> jtaab.new_assignment_status_id
3415 and jtaab.assignment_id = p_task_assignment_id
3416 order by jtaab.creation_date desc;
3417
3418 cursor c_working_flag is
3419 select working_flag,travel_flag
3420 from jtf_task_statuses_b
3421 where task_status_id = l_assignment_status_id;
3422
3423 cursor c_task_type_rule is
3424 select jttb.rule
3425 from jtf_task_types_b jttb,
3426 jtf_tasks_b jtb
3427 where jtb.task_id = l_task_id
3428 and jttb.task_type_id = jtb.task_type_id;
3429
3430 cursor c_any_working_task is
3431 select 'Y'
3432 from jtf_task_assignments jta,
3433 jtf_task_statuses_b jtsb
3434 where assignee_role = 'ASSIGNEE'
3435 and resource_type_code = l_resource_type_code
3436 and resource_id = l_resource_id
3437 and jtsb.task_status_id = jta.assignment_status_id
3438 and (working_flag = 'Y' or travel_flag = 'Y');
3439
3440 cursor c_assignee_role(p_task_assignment_id number) is
3441 select assignee_role
3442 from jtf_task_assignments
3443 where task_assignment_id = p_task_assignment_id;
3444
3445 l_assignee_role varchar2(100);
3446
3447 begin
3448 log('task_assignment_post_update','task_assignment_id: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id);
3449 log('task_assignment_post_update','hook assignee role: '||jtf_task_assignments_pub.p_task_assignments_user_hooks.assignee_role);
3450 open
3451 c_assignee_role(jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id);
3452 fetch c_assignee_role into l_assignee_role;
3453 close c_assignee_role;
3454 log('task_assignment_post_update','l_assignee_role: '||l_assignee_role);
3455 if nvl(l_assignee_role,'OWNER') = 'ASSIGNEE' then
3456 l_task_id :=
3457 JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_id;
3458 log('task_assignment_post_update','l_task_id: '||l_task_id);
3459 open c_task_type_rule;
3460 fetch c_task_type_rule into l_task_type_rule;
3461 log('task_assignment_post_update','l_task_type_rule: '||l_task_type_rule);
3462 close c_task_type_rule;
3463 if l_task_type_rule = 'DISPATCH' then
3464 l_task_assignment_id :=
3465 JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.task_assignment_id;
3466 log('task_assignment_post_update','l_task_assignment_id: '||l_task_assignment_id);
3467 l_resource_type_code :=
3468 JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.resource_type_code;
3469 log('task_assignment_post_update','l_resource_type_code: '||l_resource_type_code);
3470 l_resource_id :=
3471 JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.resource_id;
3472 log('task_assignment_post_update','l_resource_id: '||l_resource_id);
3473 l_assignment_status_id :=
3474 JTF_TASK_ASSIGNMENTS_PUB.p_task_assignments_user_hooks.assignment_status_id;
3475 log('task_assignment_post_update','l_assignment_status_id: '||l_assignment_status_id);
3476
3477 open c_working_flag;
3478 fetch c_working_flag into l_working_flag,l_travel_flag;
3479 log('task_assignment_post_update','l_travel_flag: '||l_travel_flag);
3480 log('task_assignment_post_update','l_working_flag: '||l_working_flag);
3481 close c_working_flag;
3482 if nvl(l_working_flag,'N') = 'Y' or nvl(l_travel_flag,'N') = 'Y' then
3483 log('task_assignment_post_update','update csp_planning_parameters');
3484 update csp_planning_parameters cpp
3485 set hz_location_id =
3486 (select hps.location_id
3487 from hz_party_sites hps,
3488 jtf_tasks_b jtb,
3489 jtf_task_assignments jta
3490 where hps.party_site_id = jtb.address_id
3491 and jta.task_id = jtb.task_id
3492 and jta.task_assignment_id = l_task_assignment_id)
3493 where cpp.stocking_site_type = 'TECHNICIAN'
3494 and (cpp.organization_id,cpp.secondary_inventory) in
3495 (select csi.organization_id,
3496 csi.secondary_inventory_name
3497 from csp_sec_inventories csi
3498 where csi.owner_resource_type = l_resource_type_code
3499 and csi.owner_resource_id = l_resource_id);
3500 else
3501 open c_any_working_task;
3502 fetch c_any_working_task into l_any_working_task;
3503 log('task_assignment_post_update','l_any_working_task: '||l_any_working_task);
3504 close c_any_working_task;
3505 if l_any_working_task = 'N' then
3506 log('task_assignment_post_update','update csp_planning_parameters in else');
3507 update csp_planning_parameters cpp
3508 set hz_location_id =
3509 (select min(hps.location_id)
3510 from hz_party_sites hps, csp_rs_cust_relations crcr,
3511 hz_cust_acct_sites_all hcasa,hz_cust_site_uses_all hcsua
3512 where crcr.customer_id = hcasa.cust_account_id
3513 and hcasa.party_site_id = hps.party_site_id
3514 and crcr.resource_type = l_resource_type_code
3515 and crcr.resource_id = l_resource_id
3516 and hcsua.site_use_code = 'SHIP_TO'
3517 and hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
3518 and hcsua.primary_flag = 'Y'
3519 and hcsua.status = 'A')
3520 where cpp.stocking_site_type = 'TECHNICIAN'
3521 and (cpp.organization_id,cpp.secondary_inventory) in
3522 (select csi.organization_id,
3523 csi.secondary_inventory_name
3524 from csp_sec_inventories csi
3525 where csi.owner_resource_type = l_resource_type_code
3526 and csi.owner_resource_id = l_resource_id);
3527 end if;
3528 end if;
3529
3530 log('task_assignment_post_update','profile CSF_DEFAULT_LABOR_DEBRIEF_DATETIME'||
3531 fnd_profile.value('CSF_DEFAULT_LABOR_DEBRIEF_DATETIME'));
3532 if fnd_profile.value('CSF_DEFAULT_LABOR_DEBRIEF_DATETIME') = 'STATUS'
3533 and csf_debrief_pub.g_auto_debrief then
3534 open c_travel_exists(l_task_assignment_id);
3535 fetch c_travel_exists into l_travel_exists;
3536 log('task_assignment_post_update','l_travel_exists: '||l_travel_exists);
3537 close c_travel_exists;
3538 l_travel_exists := nvl(l_travel_exists,'N');
3539 log('task_assignment_post_update','l_travel_exists: '||l_travel_exists);
3540 if l_travel_exists = 'N' then
3541 log('task_assignment_post_update','l_task_assignment_id: '||l_task_assignment_id);
3542 for cr in c_audit_travel(l_task_assignment_id) loop
3543 log('task_assignment_post_update','In c_audit_travel loop');
3544 log('task_assignment_post_update','cr.old_travel_flag: '||cr.old_travel_flag);
3545 if cr.old_travel_flag = 'Y' then
3546 l_travel_end_date := cr.creation_date;
3547 log('task_assignment_post_update','l_travel_end_date: '||
3548 to_char(l_travel_end_date,'dd-mon-yyyy hh24:mi:ss'));
3549 elsif cr.new_travel_flag = 'Y' then
3550 l_travel_start_date := cr.creation_date;
3551 log('task_assignment_post_update','l_travel_start_date: '||
3552 to_char(l_travel_start_date,'dd-mon-yyyy hh24:mi:ss'));
3553 exit;
3554 end if;
3555 end loop;
3556 log('task_assignment_post_update','end c_audit_travel loop');
3557 end if;
3558 open c_working_exists(l_task_assignment_id);
3559 fetch c_working_exists into l_working_exists;
3560 log('task_assignment_post_update','l_working_exists: '||l_working_exists);
3561 close c_working_exists;
3562 l_working_exists := nvl(l_working_exists,'N');
3563 log('task_assignment_post_update','l_working_exists: '||l_working_exists);
3564 if l_working_exists = 'N' then
3565 for cr in c_audit_working(l_task_assignment_id) loop
3566 log('task_assignment_post_update','In c_audit_working loop');
3567 log('task_assignment_post_update','cr.old_working_flag: '||cr.old_working_flag);
3568 if cr.old_working_flag = 'Y' then
3569 l_working_end_date := cr.creation_date;
3570 log('task_assignment_post_update','l_working_end_date: '||
3571 to_char(l_working_end_date,'dd-mon-yyyy hh24:mi:ss'));
3572 elsif cr.new_working_flag = 'Y' then
3573 l_working_start_date := cr.creation_date;
3574 log('task_assignment_post_update','l_working_start_date: '||
3575 to_char(l_working_start_date,'dd-mon-yyyy hh24:mi:ss'));
3576 exit;
3577 end if;
3578 end loop;
3579 log('task_assignment_post_update','end c_audit_working loop');
3580 end if;
3581 open c_header_id(l_task_assignment_id);
3582 fetch c_header_id into l_debrief_header_id;
3583 log('task_assignment_post_update','l_debrief_header_id: '||l_debrief_header_id);
3584 close c_header_id;
3585
3586 if l_travel_start_date is not null and l_travel_end_date is not null then
3587 log('task_assignment_post_update','l_record_counter: '||l_record_counter);
3588 l_business_process_id :=
3589 fnd_profile.value('CSF:DEFAULT DEBRIEF BUSINESS PROCESS');
3590 log('task_assignment_post_update','l_business_process_id: '||l_business_process_id);
3591 l_transaction_type_id :=
3592 fnd_profile.value('CSF_DEBRIEF_TRAVEL_SAC');
3593 log('task_assignment_post_update','l_transaction_type_id: '||l_transaction_type_id);
3594 l_debrief_line(l_record_counter).debrief_header_id := l_debrief_header_id;
3595 l_debrief_line(l_record_counter).service_date := l_travel_end_date;
3596 l_debrief_line(l_record_counter).labor_start_date := l_travel_start_date;
3597 l_debrief_line(l_record_counter).labor_end_date := l_travel_end_date;
3598 l_debrief_line(l_record_counter).created_by := fnd_global.user_id;
3599 l_debrief_line(l_record_counter).creation_date := sysdate;
3600 l_debrief_line(l_record_counter).last_updated_by := fnd_global.user_id;
3601 l_debrief_line(l_record_counter).last_update_date := sysdate;
3602 l_debrief_line(l_record_counter).last_update_login := fnd_global.user_id;
3603 l_debrief_line(l_record_counter).business_process_id :=
3604 l_business_process_id;
3605 l_debrief_line(l_record_counter).transaction_type_id :=
3606 l_transaction_type_id;
3607 l_record_counter := l_record_counter + 1;
3608 end if;
3609 if l_working_start_date is not null and l_working_end_date is not null then
3610 l_business_process_id :=
3611 fnd_profile.value('CSF:DEFAULT DEBRIEF BUSINESS PROCESS');
3612 log('task_assignment_post_update','l_business_process_id: '||l_business_process_id);
3613 l_transaction_type_id :=
3614 fnd_profile.value('CSF_DEBRIEF_LABOR_SAC');
3615 log('task_assignment_post_update','l_transaction_type_id: '||l_transaction_type_id);
3616 l_debrief_line(l_record_counter).debrief_header_id := l_debrief_header_id;
3617 l_debrief_line(l_record_counter).service_date := l_working_end_date;
3618 l_debrief_line(l_record_counter).labor_start_date := l_working_start_date;
3619 l_debrief_line(l_record_counter).labor_end_date := l_working_end_date;
3620 l_debrief_line(l_record_counter).created_by := fnd_global.user_id;
3621 l_debrief_line(l_record_counter).creation_date := sysdate;
3622 l_debrief_line(l_record_counter).last_updated_by := fnd_global.user_id;
3623 l_debrief_line(l_record_counter).last_update_date := sysdate;
3624 l_debrief_line(l_record_counter).last_update_login := fnd_global.user_id;
3625 l_debrief_line(l_record_counter).business_process_id :=
3626 l_business_process_id;
3627 l_debrief_line(l_record_counter).transaction_type_id :=
3628 l_transaction_type_id;
3629 l_record_counter := l_record_counter + 1;
3630 end if;
3631 log('task_assignment_post_update','l_record_counter: '||l_record_counter);
3632 if l_record_counter > 1 then
3633 log('task_assignment_post_update','l_debrief_header_id: '||l_debrief_header_id);
3634 if l_debrief_header_id is null then
3635 l_debrief_header.task_assignment_id := l_task_assignment_id;
3636 l_debrief_header.debrief_date := sysdate;
3637 log('task_assignment_post_update','calling csf_debrief_pub.create_debrief');
3638 csf_debrief_pub.create_debrief_with_dff_vals(
3639 p_api_version_number => 1.0,
3640 p_debrief_rec => l_debrief_header,
3641 p_debrief_line_tbl => l_debrief_line,
3642 x_debrief_header_id => l_debrief_header_id,
3643 x_return_status => l_return_status,
3644 x_msg_count => l_msg_count,
3645 x_msg_data => l_msg_data);
3646 log('task_assignment_post_update','l_return_status: '||l_return_status);
3647 else
3648 log('task_assignment_post_update','Calling csf_debrief_pub.create_debrief_lines');
3649 csf_debrief_pub.create_debrief_lines(
3650 p_api_version_number => 1.0,
3651 p_debrief_line_tbl => l_debrief_line,
3652 p_debrief_header_id => l_debrief_header_id,
3653 p_source_object_type_code => null,
3654 x_return_status => l_return_status,
3655 x_msg_count => l_msg_count,
3656 x_msg_data => l_msg_data);
3657 log('task_assignment_post_update','l_return_status: '||l_return_status);
3658 end if;
3659 end if;
3660 end if;
3661 end if;
3662 end if;
3663 x_return_status := l_return_status;
3664 log('task_assignment_post_update','x_return_status: '||x_return_status);
3665 log('task_assignment_post_update','End of task_assignment_post_update');
3666 end;
3667
3668 function labor_auto_create(
3669 p_task_assignment_id in number)
3670 return varchar2 is
3671
3672 cursor c_labor_auto_create is
3673 select decode(fnd_profile.value('CSF_DEFAULT_LABOR_DEBRIEF_DATETIME'),
3674 'STATUS',jtsb.working_flag,'N')
3675 from jtf_task_assignments_audit_b jtaab,
3676 jtf_task_statuses_b jtsb
3677 where jtsb.task_status_id = jtaab.new_assignment_status_id
3678 and jtaab.assignment_id = p_task_assignment_id
3679 and jtsb.working_flag = 'Y';
3680
3681 l_working_flag varchar2(1) := null;
3682
3683 begin
3684 open c_labor_auto_create;
3685 fetch c_labor_auto_create into l_working_flag;
3686 close c_labor_auto_create;
3687 return nvl(l_working_flag,'N');
3688 end;
3689
3690 PROCEDURE CLOSE_DEBRIEF (
3691 p_task_assignment_id in number,
3692 x_return_status out nocopy varchar2,
3693 x_msg_count out nocopy number,
3694 x_msg_data out nocopy varchar2) IS
3695
3696 l_processed_flag csf_debrief_headers.processed_flag%type;
3697
3698 cursor get_debrief_status is
3699 select nvl(cdh.processed_flag,'PENDING')
3700 from csf_debrief_headers cdh,
3701 csf_debrief_lines cdl
3702 where cdh.task_assignment_id = p_task_assignment_id
3703 and cdh.debrief_header_id = cdl.debrief_header_id;
3704
3705 BEGIN
3706 FND_MSG_PUB.initialize;
3707 -- Initialize API return status to SUCCESS
3708 x_return_status := FND_API.G_RET_STS_SUCCESS;
3709 open get_debrief_status;
3710 fetch get_debrief_status into l_processed_flag;
3711 close get_debrief_status;
3712 if nvl(l_processed_flag,'COMPLETED') = 'COMPLETED' THEN
3713 x_return_status := FND_API.G_RET_STS_SUCCESS;
3714 else
3715 x_return_status := FND_API.G_RET_STS_ERROR;
3716 FND_MESSAGE.Set_Name('CSF','CSF_DEBRIEF_PENDING');
3717 FND_MSG_PUB.ADD;
3718 end if;
3719 -- Standard call to get message count and if count is 1, get message info.
3720 FND_MSG_PUB.Count_And_Get
3721 ( p_count => x_msg_count,
3722 p_data => x_msg_data);
3723 END CLOSE_DEBRIEF;
3724
3725 procedure update_task_actuals (
3726 p_debrief_header_id in number,
3727 x_return_status out nocopy varchar2,
3728 x_msg_count out nocopy number,
3729 x_msg_data out nocopy varchar2) IS
3730
3731 l_task_id number := null;
3732 l_parent_task_id number := null;
3733 l_task_assignment_id number := null;
3734 l_start_date date := null;
3735 l_end_date date := null;
3736 l_start_date_ret date := null;
3737 l_end_date_ret date := null;
3738 l_object_version number := null;
3739 l_debrief_line_id number := null;
3740 l_organization_id number := cs_std.get_item_valdn_orgzn_id;
3741 l_task_object_version number := null;
3742 l_task_status_id number := null;
3743 l_resource_type_code varchar2(30) := null;
3744 l_resource_id number := null;
3745
3746 cursor c_task_assgin_object_version is
3747 select task_id, jta.task_assignment_id, jta.object_version_number,
3748 jta.resource_type_code, jta.resource_id
3749 from jtf_task_assignments jta,
3750 csf_debrief_headers cdh
3751 where jta.task_assignment_id = cdh.task_assignment_id
3752 and cdh.debrief_header_id = p_debrief_header_id;
3753
3754 cursor c_task_object_version is
3755 select parent_task_id,object_version_number
3756 from jtf_tasks_b
3757 where task_id = l_task_id ;
3758
3759 cursor c_parent_task_object_version is
3760 select object_version_number
3761 from jtf_tasks_b
3762 where task_id = l_parent_task_id ;
3763
3764 cursor c_task_assignment is
3765 select min(labor_start_date),
3766 max(labor_end_date),
3767 min(debrief_line_id)
3768 from csf_debrief_lines cdl,
3769 cs_transaction_types ctt
3770 where cdl.debrief_header_id = p_debrief_header_id
3771 and cdl.transaction_type_id = ctt.transaction_type_id
3772 and ctt.line_order_category_code = 'ORDER';
3773
3774 cursor c_task_assignment_return (p_start_date_order IN date, p_end_date_order IN date) is
3775 select min(labor_start_date),
3776 max(labor_end_date),
3777 min(debrief_line_id)
3778 from csf_debrief_lines cdl,
3779 cs_transaction_types ctt
3780 where cdl.debrief_header_id = p_debrief_header_id
3781 and cdl.transaction_type_id = ctt.transaction_type_id
3782 and ctt.line_order_category_code = 'RETURN'
3783 and cdl.labor_start_date between p_start_date_order and p_end_date_order
3784 and cdl.labor_end_date between p_start_date_order and p_end_date_order;
3785
3786 cursor c_task is
3787 select min(labor_start_date),
3788 max(labor_end_date),
3789 min(debrief_line_id)
3790 from csf_debrief_lines cdl,
3791 csf_debrief_headers cdh,
3792 jtf_task_assignments jta,
3793 cs_transaction_types ctt
3794 where jta.task_id = l_task_id
3795 and jta.task_assignment_id = cdh.task_assignment_id
3796 and cdh.debrief_header_id = cdl.debrief_header_id
3797 and cdl.transaction_type_id = ctt.transaction_type_id
3798 and ctt.line_order_category_code = 'ORDER';
3799
3800 cursor c_task_return (p_start_date_order IN date, p_end_date_order IN date) is
3801 select min(labor_start_date),
3802 max(labor_end_date),
3803 min(debrief_line_id)
3804 from csf_debrief_lines cdl,
3805 csf_debrief_headers cdh,
3806 jtf_task_assignments jta,
3807 cs_transaction_types ctt
3808 where jta.task_id = l_task_id
3809 and jta.task_assignment_id = cdh.task_assignment_id
3810 and cdh.debrief_header_id = cdl.debrief_header_id
3811 and cdl.transaction_type_id = ctt.transaction_type_id
3812 and ctt.line_order_category_code = 'RETURN'
3813 and cdl.labor_start_date between p_start_date_order and p_end_date_order
3814 and cdl.labor_end_date between p_start_date_order and p_end_date_order;
3815
3816 cursor c_parent_task is
3817 select min(labor_start_date),
3818 max(labor_end_date)
3819 from csf_debrief_lines cdl,
3820 csf_debrief_headers cdh,
3821 jtf_task_assignments jta,
3822 jtf_tasks_b jtb,
3823 cs_transaction_types ctt
3824 where jtb.parent_task_id = l_parent_task_id
3825 and jta.task_id = jtb.task_id
3826 and jta.task_assignment_id = cdh.task_assignment_id
3827 and cdh.debrief_header_id = cdl.debrief_header_id
3828 and cdl.transaction_type_id = ctt.transaction_type_id
3829 and ctt.line_order_category_code = 'ORDER';
3830
3831 cursor c_parent_task_return (p_start_date_order IN date, p_end_date_order IN date) is
3832 select min(labor_start_date),
3833 max(labor_end_date)
3834 from csf_debrief_lines cdl,
3835 csf_debrief_headers cdh,
3836 jtf_task_assignments jta,
3837 jtf_tasks_b jtb,
3838 cs_transaction_types ctt
3839 where jtb.parent_task_id = l_parent_task_id
3840 and jta.task_id = jtb.task_id
3841 and jta.task_assignment_id = cdh.task_assignment_id
3842 and cdh.debrief_header_id = cdl.debrief_header_id
3843 and cdl.transaction_type_id = ctt.transaction_type_id
3844 and ctt.line_order_category_code = 'RETURN'
3845 and cdl.labor_start_date between p_start_date_order and p_end_date_order
3846 and cdl.labor_end_date between p_start_date_order and p_end_date_order;
3847
3848
3849 ---- added for bug 3629886 starts ---------------------------------------------------
3850
3851 -- bug # 6960521
3852 -- substract RETURN type line's duration from total
3853 cursor c_task_assignment_dur is
3854 select uom_code, quantity
3855 from jtf_task_assignments jta,
3856 csf_debrief_headers cdh,
3857 csf_debrief_lines cdl,
3858 cs_transaction_types ctt
3859 where cdh.debrief_header_id = cdl.debrief_header_id
3860 and jta.task_assignment_id = cdh.task_assignment_id
3861 and cdh.debrief_header_id = p_debrief_header_id
3862 and cdl.transaction_type_id = ctt.transaction_type_id
3863 and ctt.line_order_category_code = 'ORDER'
3864 and (cdl.inventory_item_id in
3865 (select msib.inventory_item_id
3866 from mtl_system_items_b msib
3867 where msib.organization_id = l_organization_id
3868 and msib.inventory_item_id = cdl.inventory_item_id
3869 and msib.material_billable_flag = 'L')
3870 or
3871 cdl.inventory_item_id is null)
3872 union all
3873 select uom_code, (quantity * -1)
3874 from jtf_task_assignments jta,
3875 csf_debrief_headers cdh,
3876 csf_debrief_lines cdl,
3877 cs_transaction_types ctt
3878 where cdh.debrief_header_id = cdl.debrief_header_id
3879 and jta.task_assignment_id = cdh.task_assignment_id
3880 and cdh.debrief_header_id = p_debrief_header_id
3881 and cdl.transaction_type_id = ctt.transaction_type_id
3882 and ctt.line_order_category_code = 'RETURN'
3883 and (cdl.inventory_item_id in
3884 (select msib.inventory_item_id
3885 from mtl_system_items_b msib
3886 where msib.organization_id = l_organization_id
3887 and msib.inventory_item_id = cdl.inventory_item_id
3888 and msib.material_billable_flag = 'L')
3889 or
3890 cdl.inventory_item_id is null);
3891
3892 cursor c_task_dur is
3893 select uom_code, quantity
3894 from csf_debrief_lines cdl,
3895 csf_debrief_headers cdh,
3896 jtf_task_assignments jta,
3897 jtf_tasks_b jtb,
3898 cs_transaction_types ctt
3899 where cdh.debrief_header_id = cdl.debrief_header_id
3900 and jta.task_assignment_id = cdh.task_assignment_id
3901 and cdl.transaction_type_id = ctt.transaction_type_id
3902 and ctt.line_order_category_code = 'ORDER'
3903 and jtb.task_id = jta.task_id
3904 and jtb.task_id = l_task_id
3905 and (cdl.inventory_item_id in
3906 (select msib.inventory_item_id
3907 from mtl_system_items_b msib
3908 where msib.organization_id = l_organization_id
3909 and msib.inventory_item_id = cdl.inventory_item_id
3910 and msib.material_billable_flag = 'L')
3911 or
3912 cdl.inventory_item_id is null)
3913 union all
3914 select uom_code, (quantity * -1)
3915 from csf_debrief_lines cdl,
3916 csf_debrief_headers cdh,
3917 jtf_task_assignments jta,
3918 jtf_tasks_b jtb,
3919 cs_transaction_types ctt
3920 where cdh.debrief_header_id = cdl.debrief_header_id
3921 and jta.task_assignment_id = cdh.task_assignment_id
3922 and cdl.transaction_type_id = ctt.transaction_type_id
3923 and ctt.line_order_category_code = 'RETURN'
3924 and jtb.task_id = jta.task_id
3925 and jtb.task_id = l_task_id
3926 and (cdl.inventory_item_id in
3927 (select msib.inventory_item_id
3928 from mtl_system_items_b msib
3929 where msib.organization_id = l_organization_id
3930 and msib.inventory_item_id = cdl.inventory_item_id
3931 and msib.material_billable_flag = 'L')
3932 or
3933 cdl.inventory_item_id is null);
3934
3935 cursor c_parent_task_dur is
3936 select uom_code, quantity
3937 from csf_debrief_lines cdl,
3938 csf_debrief_headers cdh,
3939 jtf_task_assignments jta,
3940 jtf_tasks_b jtb,
3941 cs_transaction_types ctt
3942 where cdh.debrief_header_id = cdl.debrief_header_id
3943 and jta.task_assignment_id = cdh.task_assignment_id
3944 and cdl.transaction_type_id = ctt.transaction_type_id
3945 and ctt.line_order_category_code = 'ORDER'
3946 and jtb.task_id = jta.task_id
3947 and jtb.parent_task_id = l_parent_task_id
3948 and (cdl.inventory_item_id in
3949 (select msib.inventory_item_id
3950 from mtl_system_items_b msib
3951 where msib.organization_id = l_organization_id
3952 and msib.inventory_item_id = cdl.inventory_item_id
3953 and msib.material_billable_flag = 'L')
3954 or
3955 cdl.inventory_item_id is null)
3956 union all
3957 select uom_code, (quantity * -1)
3958 from csf_debrief_lines cdl,
3959 csf_debrief_headers cdh,
3960 jtf_task_assignments jta,
3961 jtf_tasks_b jtb,
3962 cs_transaction_types ctt
3963 where cdh.debrief_header_id = cdl.debrief_header_id
3964 and jta.task_assignment_id = cdh.task_assignment_id
3965 and cdl.transaction_type_id = ctt.transaction_type_id
3966 and ctt.line_order_category_code = 'RETURN'
3967 and jtb.task_id = jta.task_id
3968 and jtb.parent_task_id = l_parent_task_id
3969 and (cdl.inventory_item_id in
3970 (select msib.inventory_item_id
3971 from mtl_system_items_b msib
3972 where msib.organization_id = l_organization_id
3973 and msib.inventory_item_id = cdl.inventory_item_id
3974 and msib.material_billable_flag = 'L')
3975 or
3976 cdl.inventory_item_id is null);
3977
3978
3979 l_uom_code varchar2(3);
3980 l_duration number := 0;
3981 l_duration_sum number := 0;
3982
3983
3984 cursor c_hours_uom is
3985 select fnd_profile.value('CSF_UOM_HOURS')
3986 from dual;
3987
3988 l_uom varchar2(3);
3989 l_t_uom varchar2(3);
3990
3991 ---- added for bug 3629886 ends ------------------------------------------------------
3992
3993 BEGIN
3994 -- Initialize API return status to SUCCESS
3995 x_return_status := FND_API.G_RET_STS_SUCCESS;
3996
3997 open c_task_assgin_object_version;
3998 fetch c_task_assgin_object_version into l_task_id, l_task_assignment_id, l_object_version,
3999 l_resource_type_code, l_resource_id ;
4000 close c_task_assgin_object_version;
4001
4002 open c_task_assignment;
4003 fetch c_task_assignment into l_start_date,l_end_date,l_debrief_line_id;
4004
4005 if l_start_date is null then
4006 l_start_date := null;
4007 l_end_date := null;
4008 else
4009 l_start_date := nvl(l_start_date,fnd_api.g_miss_date);
4010 l_end_date := nvl(l_end_date,fnd_api.g_miss_date);
4011
4012 -- bug # 6960521
4013 open c_task_assignment_return(l_start_date, l_end_date);
4014 fetch c_task_assignment_return into l_start_date_ret, l_end_date_ret, l_debrief_line_id;
4015 if(l_start_date is not null) then
4016
4017 if (l_start_date_ret = l_start_date) and (l_end_date_ret <= l_end_date) then
4018 l_start_date := l_end_date_ret;
4019 end if;
4020
4021 if (l_end_date_ret = l_end_date) and (l_start_date_ret >= l_start_date) then
4022 l_end_date := l_start_date_ret;
4023 end if;
4024
4025 end if;
4026 close c_task_assignment_return;
4027
4028 end if;
4029 close c_task_assignment;
4030
4031 ---- added for bug 3629886 starts -------------------------------------------------------
4032
4033 open c_hours_uom;
4034 fetch c_hours_uom into l_uom;
4035 close c_hours_uom;
4036
4037 l_duration_sum := 0;
4038 l_duration := 0;
4039 l_t_uom := l_uom;
4040
4041 open c_task_assignment_dur;
4042 loop
4043 fetch c_task_assignment_dur into l_uom_code, l_duration;
4044
4045 if l_uom_code <> l_uom then
4046 l_duration := inv_convert.inv_um_convert
4047 ( item_id => 0
4048 , precision => null
4049 , from_quantity => l_duration
4050 , from_unit => l_uom_code
4051 , to_unit => l_uom
4052 , from_name => null
4053 , to_name => null );
4054 end if;
4055
4056 exit when c_task_assignment_dur%notfound;
4057 l_duration_sum := l_duration_sum + l_duration ;
4058
4059 end loop;
4060 close c_task_assignment_dur;
4061 l_duration_sum := round(l_duration_sum,4);
4062 if nvl(l_duration_sum,0) = 0 then
4063 l_duration_sum := null;
4064 l_t_uom := null;
4065 end if;
4066
4067 ---- added for bug 3629886 ends -----------------------------------------------------------
4068
4069 if l_object_version is not null then
4070
4071 csf_task_assignments_pub.update_task_assignment (
4072 p_api_version => 1.0,
4073 x_return_status => x_return_status,
4074 x_msg_count => x_msg_count,
4075 x_msg_data => x_msg_data,
4076 p_task_assignment_id => l_task_assignment_id,
4077 p_object_version_number => l_object_version,
4078 p_actual_start_date => l_start_date,
4079 p_actual_end_date => l_end_date,
4080 p_actual_effort => l_duration_sum, -- added for bug 3629886
4081 p_actual_effort_uom => l_t_uom, -- added for bug 3629886
4082 p_resource_type_code => l_resource_type_code,
4083 p_resource_id => l_resource_id,
4084 x_task_object_version_number => l_task_object_version,
4085 x_task_status_id => l_task_status_id);
4086
4087 l_object_version := null;
4088
4089 open c_task_object_version;
4090 fetch c_task_object_version into l_parent_task_id,l_object_version;
4091 close c_task_object_version;
4092
4093 open c_task;
4094 fetch c_task into l_start_date,l_end_date,l_debrief_line_id;
4095 if l_start_date is null then
4096 l_start_date := null;
4097 l_end_date := null;
4098 else
4099 l_start_date := nvl(l_start_date,fnd_api.g_miss_date);
4100 l_end_date := nvl(l_end_date,fnd_api.g_miss_date);
4101
4102 -- bug # 6960521
4103 open c_task_return(l_start_date, l_end_date);
4104 fetch c_task_return into l_start_date_ret, l_end_date_ret, l_debrief_line_id;
4105 if(l_start_date is not null) then
4106
4107 if (l_start_date_ret = l_start_date) and (l_end_date_ret <= l_end_date) then
4108 l_start_date := l_end_date_ret;
4109 end if;
4110
4111 if (l_end_date_ret = l_end_date) and (l_start_date_ret >= l_start_date) then
4112 l_end_date := l_start_date_ret;
4113 end if;
4114
4115 end if;
4116 close c_task_return;
4117
4118 end if;
4119 close c_task;
4120
4121 ---- added for bug 3629886 starts ------------------------------------------------------
4122
4123 l_duration_sum := 0;
4124 l_duration := 0;
4125 l_t_uom := l_uom;
4126
4127 open c_task_dur;
4128 loop
4129 fetch c_task_dur into l_uom_code, l_duration;
4130
4131 if l_uom_code <> l_uom then
4132 l_duration := inv_convert.inv_um_convert
4133 ( item_id => 0
4134 , precision => null
4135 , from_quantity => l_duration
4136 , from_unit => l_uom_code
4137 , to_unit => l_uom
4138 , from_name => null
4139 , to_name => null );
4140 end if;
4141
4142 exit when c_task_dur%notfound;
4143 l_duration_sum := l_duration_sum + l_duration ;
4144
4145 end loop;
4146 close c_task_dur;
4147 l_duration_sum := round(l_duration_sum,4);
4148 if nvl(l_duration_sum,0) = 0 then
4149 l_duration_sum := null;
4150 l_t_uom := null;
4151 end if;
4152
4153 ---- added for bug 3629886 ends ----------------------------------------------------------
4154
4155 if l_object_version is not null then
4156 csf_tasks_pub.update_task (
4157 p_api_version => 1.0,
4158 p_object_version_number => l_object_version,
4159 p_task_id => l_task_id,
4160 p_actual_start_date => l_start_date,
4161 p_actual_end_date => l_end_date,
4162 p_actual_effort => l_duration_sum, -- added for bug 3629886
4163 p_actual_effort_uom => l_t_uom, -- added for bug 3629886
4164 x_return_status => x_return_status,
4165 x_msg_count => x_msg_count,
4166 x_msg_data => x_msg_data) ;
4167
4168 end if;
4169
4170
4171 if nvl(l_parent_task_id,-1) <> -1 then
4172 l_object_version := null;
4173
4174 open c_parent_task_object_version;
4175 fetch c_parent_task_object_version into l_object_version;
4176 close c_parent_task_object_version;
4177
4178 open c_parent_task;
4179 fetch c_parent_task into l_start_date,l_end_date;
4180 close c_parent_task;
4181
4182 -- bug # 6960521
4183 open c_parent_task_return(l_start_date, l_end_date);
4184 fetch c_parent_task_return into l_start_date_ret, l_end_date_ret;
4185 if (l_start_date_ret = l_start_date) and (l_end_date_ret <= l_end_date) then
4186 l_start_date := l_end_date_ret;
4187 end if;
4188
4189 if (l_end_date_ret = l_end_date) and (l_start_date_ret >= l_start_date) then
4190 l_end_date := l_start_date_ret;
4191 end if;
4192 close c_parent_task_return;
4193
4194
4195 l_duration_sum := 0;
4196 l_duration := 0;
4197 l_t_uom := l_uom;
4198
4199 open c_parent_task_dur;
4200 loop
4201 fetch c_parent_task_dur into l_uom_code, l_duration;
4202
4203 if l_uom_code <> l_uom then
4204 l_duration := inv_convert.inv_um_convert
4205 ( item_id => 0
4206 , precision => null
4207 , from_quantity => l_duration
4208 , from_unit => l_uom_code
4209 , to_unit => l_uom
4210 , from_name => null
4211 , to_name => null );
4212 end if;
4213
4214 exit when c_parent_task_dur%notfound;
4215 l_duration_sum := l_duration_sum + l_duration ;
4216
4217 end loop;
4218 close c_parent_task_dur;
4219
4220 if nvl(l_duration_sum,0) = 0 then
4221 l_duration_sum := null;
4222 l_t_uom := null;
4223 end if;
4224
4225
4226 if l_object_version is not null then
4227 csf_tasks_pub.update_task (
4228 p_api_version => 1.0,
4229 p_object_version_number => l_object_version,
4230 p_task_id => l_parent_task_id,
4231 p_actual_start_date => l_start_date,
4232 p_actual_end_date => l_end_date,
4233 p_actual_effort => l_duration_sum, -- added for bug 3629886
4234 p_actual_effort_uom => l_t_uom, -- added for bug 3629886
4235 x_return_status => x_return_status,
4236 x_msg_count => x_msg_count,
4237 x_msg_data => x_msg_data) ;
4238 end if;
4239 end if;
4240 end if;
4241
4242 END;
4243
4244
4245 Procedure validate_travel_times(p_actual_travel_start_time date,
4246 p_actual_travel_end_time date,
4247 p_task_assignment_id NUMBER,
4248 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
4249 X_Return_Status OUT NOCOPY VARCHAR2,
4250 X_Msg_Count OUT NOCOPY NUMBER,
4251 X_Msg_Data OUT NOCOPY VARCHAR2) IS
4252
4253 l_service_request_date date;
4254
4255 Cursor get_service_request_date IS
4256 select nvl(incident_occurred_date,incident_date)
4257 from jtf_task_assignments jta , cs_incidents_all cia, jtf_tasks_b jtb
4258 where jta.task_assignment_id = p_task_assignment_id
4259 and jtb.task_id = jta.task_id
4260 and cia.incident_id = jtb.source_object_id
4261 and jtb.source_object_type_code = 'SR';
4262
4263 BEGIN
4264 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
4265 open get_service_request_date;
4266 fetch get_service_request_date INTO l_service_request_date;
4267 close get_service_request_date;
4268
4269 If (p_actual_travel_start_time IS NULL
4270 or p_actual_travel_start_time = FND_API.g_miss_date)
4271 and (p_actual_travel_end_time <> FND_API.g_miss_date
4272 and p_actual_travel_end_time IS NOT NULL) THEN
4273 X_Return_Status := FND_API.G_RET_STS_ERROR;
4274 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4275 FND_MESSAGE.Set_Name('CSF','CSF_TRAVEL_START_TIME');
4276 FND_MSG_PUB.ADD;
4277 END IF;
4278 END IF;
4279
4280 If (p_actual_travel_start_time IS NOT NULL
4281 and p_actual_travel_end_time IS NOT NULL
4282 and p_actual_travel_start_time <> FND_API.g_miss_date
4283 and p_actual_travel_end_time <> FND_API.g_miss_date
4284 AND p_actual_travel_start_time > p_actual_travel_end_time) THEN
4285 X_Return_Status := FND_API.G_RET_STS_ERROR;
4286 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4287 FND_MESSAGE.Set_Name('CSF','CSF_TRAVEL_START_AFTER_END');
4288 FND_MSG_PUB.ADD;
4289 END IF;
4290 END IF;
4291
4292 If p_actual_travel_start_time IS NOT NULL
4293 and p_actual_travel_start_time <> FND_API.g_miss_date
4294 and trunc(fnd_timezones_pvt.adjust_datetime(p_actual_travel_start_time,
4295 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')),
4296 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID'))))
4297 > trunc(fnd_timezones_pvt.adjust_datetime(sysdate,
4298 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')),
4299 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')))) THEN
4300 X_Return_Status := FND_API.G_RET_STS_ERROR;
4301 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4302 FND_MESSAGE.Set_Name('CSF','CSF_TRAVEL_START_CURR_DATE');
4303 FND_MSG_PUB.ADD;
4304 END IF;
4305 END IF;
4306 If p_actual_travel_end_time IS NOT NULL
4307 and p_actual_travel_end_time <> FND_API.g_miss_date
4308 and trunc(fnd_timezones_pvt.adjust_datetime(p_actual_travel_end_time,
4309 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')),
4310 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID'))))
4311 > trunc(fnd_timezones_pvt.adjust_datetime(sysdate,
4312 fnd_timezones.get_code(fnd_profile.value('SERVER_TIMEZONE_ID')),
4313 fnd_timezones.get_code(fnd_profile.value('CLIENT_TIMEZONE_ID')))) THEN
4314 X_Return_Status := FND_API.G_RET_STS_ERROR;
4315 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4316 FND_MESSAGE.Set_Name('CSF','CSF_TRAVEL_END_CURR_DATE');
4317 FND_MSG_PUB.ADD;
4318 END IF;
4319 END IF;
4320
4321 If (p_actual_travel_start_time IS NOT NULL
4322 and p_actual_travel_start_time <> FND_API.g_miss_date
4323 and p_actual_travel_start_time < l_service_request_date) THEN
4324 X_Return_Status := FND_API.G_RET_STS_ERROR;
4325 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4326 FND_MESSAGE.Set_Name('CSF','CSF_TRAVEL_START_SR_DATE');
4327 FND_MSG_PUB.ADD;
4328 END IF;
4329 END IF;
4330 If (p_actual_travel_end_time IS NOT NULL
4331 and p_actual_travel_end_time <> FND_API.g_miss_date
4332 and p_actual_travel_end_time < l_service_request_date) THEN
4333 X_Return_Status := FND_API.G_RET_STS_ERROR;
4334 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
4335 FND_MESSAGE.Set_Name('CSF','CSF_TRAVEL_END_SR_DATE');
4336 FND_MSG_PUB.ADD;
4337 END IF;
4338 END IF;
4339 END;
4340
4341 function serial_number_control_code(p_organization_id number,
4342 p_inventory_item_id number,
4343 p_transaction_type_id number)
4344 return number is
4345 l_serial_control number := -1;
4346 l_return_status varchar2(2000);
4347 l_null number := null;
4348 l_serial_tagging number := 0;
4349 l_schema_name varchar2(100) := 'APPS';
4350
4351 cursor c_serial_tagging is
4352 select 1
4353 from all_procedures
4354 where object_name = 'INV_SERIAL_NUMBER_PUB'
4355 and procedure_name = 'IS_SERIAL_CONTROLLED'
4356 and owner = l_schema_name;
4357
4358 cursor c_get_schema is
4359 select oracle_username
4360 from fnd_oracle_userid
4361 where read_only_flag = 'U';
4362
4363 cursor c_serial_control is
4364 select serial_number_control_code
4365 from mtl_system_items_b
4366 where organization_id = p_organization_id
4367 and inventory_item_id = p_inventory_item_id;
4368
4369 begin
4370 open c_serial_control;
4371 fetch c_serial_control into l_serial_control;
4372 close c_serial_control;
4373 if l_serial_control = 1 then
4374 open c_get_schema;
4375 fetch c_get_schema into l_schema_name;
4376 close c_get_schema;
4377
4378 open c_serial_tagging;
4379 fetch c_serial_tagging into l_serial_tagging;
4380 close c_serial_tagging;
4381 if l_serial_tagging = 1 then
4382 execute immediate
4383 'begin
4384 inv_serial_number_pub.is_serial_controlled(
4385 :1,:2,:3,:4,:5,:6,:7,:8,:9,:10);
4386 end;'
4387 using in p_inventory_item_id, in p_organization_id, in l_null,
4388 in p_transaction_type_id, in l_null, in l_null, in l_null,
4389 in l_null, out l_serial_control, out l_return_status;
4390 if l_serial_control = 2 then
4391 l_serial_control := 6;
4392 end if;
4393 end if;
4394 end if;
4395 return l_serial_control;
4396 end;
4397
4398 END CSF_DEBRIEF_PVT;
4399