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