[Home] [Help]
PACKAGE BODY: APPS.CSFW_DEBRIEF_PUB
Source
4 PROCEDURE Create_Debrief_header
1 Package Body csfw_debrief_pub AS
2 /*$Header: csfwdbfb.pls 120.16.12020000.3 2013/04/09 09:39:51 shadas ship $*/
3
5 ( p_task_assignment_id IN NUMBER
6 , p_error_id OUT NOCOPY NUMBER
7 , p_error OUT NOCOPY VARCHAR2
8 , p_debrief_header_id OUT NOCOPY NUMBER
9 )
10 IS
11 P_DEBRIEF_Rec CSF_DEBRIEF_PUB.DEBRIEF_Rec_Type;
12 x_header_id number;
13 l_return_status varchar2(2000);
14 l_msg_count number;
15 l_msg_data varchar2(2000);
16 l_header_id number;
17 l_debrief_number number;
18 l_user number;
19 l_data varchar2(2000);
20 l_msg_index_out number;
21
22 cursor c_header_id is select CSF_DEBRIEF_HEADERS_S1.nextval from dual;
23 cursor c_header_number is select CSF_DEBRIEF_HEADERS_S2.nextval from dual;
24
25 Begin
26 p_error_id := 0; --Assume success
27
28 open c_header_id;
29 fetch c_header_id into l_header_id;
30 close c_header_id;
31
32 open c_header_number;
33 fetch c_header_number into l_debrief_number;
34 close c_header_number;
35
36 l_user := FND_GLOBAL.user_id;
37
38 P_DEBRIEF_Rec.DEBRIEF_HEADER_ID := l_header_id ;
39 P_DEBRIEF_Rec.DEBRIEF_NUMBER := to_char(l_debrief_number);
40 P_DEBRIEF_Rec.DEBRIEF_DATE := sysdate;
41 P_DEBRIEF_Rec.TASK_ASSIGNMENT_ID := p_task_assignment_id;
42
43 CSF_DEBRIEF_PUB.Create_Debrief_With_Dff_Vals(
44 P_Api_Version_Number => 1.0,
45 P_Init_Msg_List => FND_API.G_FALSE,
46 P_Commit => FND_API.G_FALSE,
47 P_DEBRIEF_Rec => P_DEBRIEF_Rec,
48 P_DEBRIEF_LINE_tbl => CSF_DEBRIEF_PUB.G_MISS_DEBRIEF_LINE_Tbl ,
49 X_DEBRIEF_HEADER_ID => x_header_id,
50 X_Return_Status => l_return_status,
51 X_Msg_Count => l_msg_count,
52 X_Msg_Data => l_msg_data
53 );
54
55 IF l_return_status = FND_API.G_RET_STS_SUCCESS
56 THEN
57 /* API-call was successfull */
58 p_error_id := 0;
59 p_error := FND_API.G_RET_STS_SUCCESS;
60 p_debrief_header_id := x_header_id;
61 ELSE
62
63 FOR l_counter IN 1 .. l_msg_count
64 LOOP
65 fnd_msg_pub.get
66 ( p_msg_index => l_counter
67 , p_encoded => FND_API.G_FALSE
68 , p_data => l_data
69 , p_msg_index_out => l_msg_index_out
70 );
71 --dbms_output.put_line( 'Message: '||l_data );
72 END LOOP ;
73 p_error_id := 1;
74 p_error := l_data;
75 p_debrief_header_id := 0;
76 END IF;
77
78 if p_error = FND_API.G_RET_STS_SUCCESS
79 then
80 p_error := '';
81 end if;
82
83 EXCEPTION
84 WHEN OTHERS
85 THEN
86 p_error_id := -1;
87 p_error := SQLERRM;
88 p_debrief_header_id := 0;
89
90 END Create_Debrief_header;
91
92
93 -- Bug Number : 4543409, added quantity and uom
94 PROCEDURE Create_Labor_Line
95 ( p_debrief_header_id IN NUMBER,
96 p_labor_start_date IN DATE,
97 p_labor_end_date IN DATE,
98 p_service_date IN DATE,
99 p_txn_billing_type_id IN NUMBER,
100 p_inventory_item_id IN NUMBER,
101 p_business_process_id IN NUMBER,
102 p_charge_Entry IN VARCHAR2,
103 p_incident_id IN NUMBER,
104 p_txnTypeId IN NUMBER,
105 p_quantity IN NUMBER,
106 p_uom IN VARCHAR2,
107 p_justificationCode IN VARCHAR2,
108 p_return_reason_code IN VARCHAR2,
109 p_debrief_line_id OUT NOCOPY NUMBER,
110 p_error_id OUT NOCOPY NUMBER,
111 p_error OUT NOCOPY VARCHAR2,
112 p_attribute1 IN VARCHAR2 DEFAULT '$$#@',
113 p_attribute2 IN VARCHAR2 DEFAULT '$$#@',
114 p_attribute3 IN VARCHAR2 DEFAULT '$$#@',
115 p_attribute4 IN VARCHAR2 DEFAULT '$$#@',
116 p_attribute5 IN VARCHAR2 DEFAULT '$$#@',
117 p_attribute6 IN VARCHAR2 DEFAULT '$$#@',
118 p_attribute7 IN VARCHAR2 DEFAULT '$$#@',
119 p_attribute8 IN VARCHAR2 DEFAULT '$$#@',
120 p_attribute9 IN VARCHAR2 DEFAULT '$$#@',
121 p_attribute10 IN VARCHAR2 DEFAULT '$$#@',
122 p_attribute11 IN VARCHAR2 DEFAULT '$$#@',
123 p_attribute12 IN VARCHAR2 DEFAULT '$$#@',
124 p_attribute13 IN VARCHAR2 DEFAULT '$$#@',
125 p_attribute14 IN VARCHAR2 DEFAULT '$$#@',
129 p_expenditure_org_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
126 p_attribute15 IN VARCHAR2 DEFAULT '$$#@',
127 p_attribute_category IN VARCHAR2 DEFAULT '$$#@',
128
130 p_project_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
131 p_project_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
132 )
133 IS
134
135 l_return_status varchar2(2000);
136 l_msg_count number;
137 l_msg_data varchar2(2000);
138 l_header_id number;
139 l_dbf_line_id number;
140 l_user number;
141 l_data varchar2(2000);
142 l_msg_index_out number;
143 P_DEBRIEF_TBL CSF_DEBRIEF_PUB.DEBRIEF_LINE_Tbl_Type ;
144 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
145 l_organization_id number;
146 l_UOM_code varchar2(100);
147 l_order_header_id number;
148 l_order_type_id number;
149 l_line_type_id number;
150 l_object_version_number NUMBER;
151 l_estimate_detail_id NUMBER;
152 l_line_number NUMBER;
153 l_dt_format varchar2(20);
154 l_sr_date DATE;
155 l_charge_Entry varchar2(1);
156
157
158 l_resource_type VARCHAR2(30);
159 l_resource_id NUMBER;
160 l_validate_lab_time_msg varchar2(255);
161 l_attribute1 VARCHAR2(150);
162 l_attribute2 VARCHAR2(150);
163 l_attribute3 VARCHAR2(150);
164 l_attribute4 VARCHAR2(150);
165 l_attribute5 VARCHAR2(150);
166 l_attribute6 VARCHAR2(150);
167 l_attribute7 VARCHAR2(150);
168 l_attribute8 VARCHAR2(150);
169 l_attribute9 VARCHAR2(150);
170 l_attribute10 VARCHAR2(150);
171 l_attribute11 VARCHAR2(150);
172 l_attribute12 VARCHAR2(150);
173 l_attribute13 VARCHAR2(150);
174 l_attribute14 VARCHAR2(150);
175 l_attribute15 VARCHAR2(150);
176 l_attribute_category VARCHAR2(150);
177
178 l_expenditure_org_id number;
179 l_project_id number;
180 l_project_task_id number;
181
182 cursor find_resource is
183 select a.resource_id resource_id, b.resource_type resource_type
184 from CSP_RS_RESOURCES_V b, jtf_rs_resource_extns a
185 where a.resource_id =b.resource_id
186 and a.user_id = FND_GLOBAL.USER_ID;
187
188 r_find_resource find_resource%ROWTYPE;
189
190
191 cursor c_dbf_line_id is select CSF_DEBRIEF_LINES_S.nextval from dual;
192
193 cursor c_uom_code(v_org_id number, v_item_id number)
194 is
195 select primary_uom_code from mtl_system_items_vl
196 where organization_id = v_org_id
197 and inventory_item_id = v_item_id;
198
199 cursor c_creation_date (v_incident_id number)
200 is
201 select creation_date from cs_incidents_all_b where incident_id = v_incident_id;
202
203
204 BEGIN
205 if p_attribute1 = '$$#@' then
206 l_attribute1 := null;
207 else
208 l_attribute1 := p_attribute1;
209 end if;
210 if p_attribute2 = '$$#@' then
211 l_attribute2 := null;
212 else
213 l_attribute2 := p_attribute2;
214 end if;
215 if p_attribute3 = '$$#@' then
216 l_attribute3 := null;
217 else
218 l_attribute3 := p_attribute3;
219 end if;
220 if p_attribute4 = '$$#@' then
221 l_attribute4 := null;
222 else
223 l_attribute4 := p_attribute4;
224 end if;
225 if p_attribute5 = '$$#@' then
226 l_attribute5 := null;
227 else
228 l_attribute5 := p_attribute5;
229 end if;
230 if p_attribute6 = '$$#@' then
231 l_attribute6 := null;
232 else
233 l_attribute6 := p_attribute6;
234 end if;
235 if p_attribute7 = '$$#@' then
236 l_attribute7 := null;
237 else
238 l_attribute7 := p_attribute7;
239 end if;
240 if p_attribute8 = '$$#@' then
241 l_attribute8 := null;
242 else
243 l_attribute8 := p_attribute8;
244 end if;
245 if p_attribute9 = '$$#@' then
246 l_attribute9 := null;
247 else
248 l_attribute9 := p_attribute9;
249 end if;
250 if p_attribute10 = '$$#@' then
251 l_attribute10 := null;
252 else
253 l_attribute10 := p_attribute10;
254 end if;
255 if p_attribute11 = '$$#@' then
256 l_attribute11 := null;
257 else
258 l_attribute11 := p_attribute11;
259 end if;
260 if p_attribute12 = '$$#@' then
261 l_attribute12 := null;
262 else
263 l_attribute12 := p_attribute12;
264 end if;
265 if p_attribute13 = '$$#@' then
266 l_attribute13 := null;
267 else
268 l_attribute13 := p_attribute13;
269 end if;
270 if p_attribute14 = '$$#@' then
271 l_attribute14 := null;
272 else
273 l_attribute14 := p_attribute14;
274 end if;
275 if p_attribute15 = '$$#@' then
276 l_attribute15 := null;
277 else
278 l_attribute15 := p_attribute15;
279 end if;
280 if p_attribute_category = '$$#@' then
281 l_attribute_category := null;
282 else
283 l_attribute_category := p_attribute_category;
284 end if;
285
286 if p_expenditure_org_id <= 0 then
287 l_expenditure_org_id := null;
288 else
289 l_expenditure_org_id := p_expenditure_org_id;
290 end if;
291 if p_project_id <= 0 then
292 l_project_id := null;
293 else
294 l_project_id := p_project_id;
295 end if;
296 if p_project_task_id <= 0 then
297 l_project_task_id := null;
298 else
299 l_project_task_id := p_project_task_id;
300 end if;
301
302 l_order_header_id := 0;
303 p_error_id := 0; --Assume success
304 l_header_id := p_debrief_header_id;
305
306 l_user := FND_GLOBAL.user_id ;
307
308 open c_dbf_line_id;
312 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_organization_id);
309 fetch c_dbf_line_id into l_dbf_line_id;
310 close c_dbf_line_id;
311
313
314
315 -------------------------------------------------------------------------------------------------
316 -------
317 ------- check for the Service date, which should not be less than SR date and more than Sysdate
318 -------
319 -------------------------------------------------------------------------------------------------
320 -- first get the date format
321 -- Bug 2862796. Using ICX: Date Format Mask in place of CSFW: Date Format.
322 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK', l_dt_format);
323
324 -- Now get the SR Date in date format
325 open c_creation_date(p_incident_id);
326 fetch c_creation_date into l_sr_date;
327 close c_creation_date;
328
329 -- now check if it is more than sysdate
330 -- BUG 2225745
331 -- if l_sr_date is p_service_date, then make l_sr_date = l_sr_date - 1
332 -- Now that we are dealing with dates all over we do not need this
333
334 -- IF trunc(l_sr_date) = trunc(p_service_date)
335 -- THEN
336 -- l_sr_date := l_sr_date - 1;
337 -- END IF; --BUG 2451683
338
339 /* bug# 2844824
340 -- remove the checks
341 --if start Date is not between the creation date and sysdate
342 IF p_labor_start_date between l_sr_date and sysdate
343 THEN
344 null;
345 ELSE
346 --convert the Server to Client time zone
347 FND_MESSAGE.Set_Name('CSF', 'CSFW_SERVICE_DATE');
348 FND_MESSAGE.Set_Token('P_SR_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(l_sr_date), l_dt_format||' HH24:MI'));
349 FND_MESSAGE.Set_Token('P_SYSTEM_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(sysdate), l_dt_format||' HH24:MI'));
350
351 p_error := -21;
352 p_error := FND_MESSAGE.Get;
353
354 RETURN ;
355 END IF;
356
357 -- SAme For End date
358 IF p_labor_end_date between l_sr_date and sysdate
359 THEN
360 null;
361 ELSE
362 --convert the Server to Client time zone
363 FND_MESSAGE.Set_Name('CSF', 'CSFW_SERVICE_DATE_END');
364 FND_MESSAGE.Set_Token('P_SR_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(l_sr_date), l_dt_format||' HH24:MI'));
365 FND_MESSAGE.Set_Token('P_SYSTEM_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(sysdate), l_dt_format||' HH24:MI'));
366
367 p_error := -21;
368 p_error := FND_MESSAGE.Get;
369
370 RETURN ;
371 END IF;
372 */
373
374 IF (p_labor_start_date >= p_labor_end_date) THEN
375 FND_MESSAGE.Set_Name('CSF', 'CSFW_START_END_DATE_SAME');
376 p_error := -30;
377 p_error := FND_MESSAGE.Get;
378 RETURN;
379 END IF;
380
381 -- CALL THE VALIDATE FUNCTION
382 OPEN find_resource;
383 fetch find_resource INTO r_find_resource;
384 close find_resource;
385 l_validate_lab_time_msg := validate_labor_time(r_find_resource.resource_type, r_find_resource.resource_id, l_dbf_line_id,p_labor_start_date, p_labor_end_date);
386 IF l_validate_lab_time_msg <> 'S' THEN
387 p_error := -22;
388 p_error := l_validate_lab_time_msg;
389 RETURN;
390 END IF;
391
392
393 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := l_dbf_line_id;
394 P_DEBRIEF_LINE_Rec.DEBRIEF_HEADER_ID := l_header_id;--from header record
395 P_DEBRIEF_LINE_Rec.SERVICE_DATE := p_service_date;
396 P_DEBRIEF_LINE_Rec.TXN_BILLING_TYPE_ID := p_txn_billing_type_id;
397
398 IF p_inventory_item_id <> 0 THEN
399 open c_uom_code (l_organization_id, p_inventory_item_id);
400 fetch c_uom_code into l_UOM_code;
401 close c_uom_code;
402
403 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := p_inventory_item_id;
404 P_DEBRIEF_LINE_Rec.UOM_CODE := l_UOM_code;
405 END IF;
406
407 P_DEBRIEF_LINE_Rec.BUSINESS_PROCESS_ID := p_business_process_id;
408 P_DEBRIEF_LINE_Rec.LABOR_START_DATE := p_labor_start_date;
409 P_DEBRIEF_LINE_Rec.LABOR_END_DATE := p_labor_end_date;
410 P_DEBRIEF_LINE_Rec.channel_code := 'WIRELESS_USER';
411 P_DEBRIEF_LINE_Rec.issuing_inventory_org_id := l_organization_id;
412 P_DEBRIEF_LINE_Rec.TRANSACTION_TYPE_ID := p_txnTypeId;
413 P_DEBRIEF_LINE_Rec.LABOR_REASON_CODE := p_justificationCode;
414 P_DEBRIEF_LINE_Rec.RETURN_REASON_CODE := p_return_reason_code;
415
416 P_DEBRIEF_LINE_Rec.ATTRIBUTE1 := l_attribute1;
417 P_DEBRIEF_LINE_Rec.ATTRIBUTE2 := l_attribute2;
418 P_DEBRIEF_LINE_Rec.ATTRIBUTE3 := l_attribute3;
419 P_DEBRIEF_LINE_Rec.ATTRIBUTE4 := l_attribute4;
420 P_DEBRIEF_LINE_Rec.ATTRIBUTE5 := l_attribute5;
421 P_DEBRIEF_LINE_Rec.ATTRIBUTE6 := l_attribute6;
422 P_DEBRIEF_LINE_Rec.ATTRIBUTE7 := l_attribute7;
423 P_DEBRIEF_LINE_Rec.ATTRIBUTE8 := l_attribute8;
424 P_DEBRIEF_LINE_Rec.ATTRIBUTE9 := l_attribute9;
425 P_DEBRIEF_LINE_Rec.ATTRIBUTE10 := l_attribute10;
426 P_DEBRIEF_LINE_Rec.ATTRIBUTE11 := l_attribute11;
427 P_DEBRIEF_LINE_Rec.ATTRIBUTE12 := l_attribute12;
428 P_DEBRIEF_LINE_Rec.ATTRIBUTE13 := l_attribute13;
429 P_DEBRIEF_LINE_Rec.ATTRIBUTE14 := l_attribute14;
430 P_DEBRIEF_LINE_Rec.ATTRIBUTE15 := l_attribute15;
431 P_DEBRIEF_LINE_Rec.ATTRIBUTE_CATEGORY := l_attribute_category;
432
433
434 P_DEBRIEF_LINE_Rec.EXPENDITURE_ORG_ID := l_expenditure_org_id;
435 P_DEBRIEF_LINE_Rec.PROJECT_ID := l_project_id;
439 P_DEBRIEF_LINE_Rec.UOM_CODE := p_uom;
436 P_DEBRIEF_LINE_Rec.PROJECT_TASK_ID := l_project_task_id;
437
438 -- Bug 4543409
440 IF p_quantity IS NOT NULL THEN
441 P_DEBRIEF_LINE_Rec.QUANTITY := p_quantity;
442 END IF;
443
444 P_DEBRIEF_TBL (1) := P_DEBRIEF_LINE_Rec;
445 CSF_DEBRIEF_PUB.Create_debrief_lines(
446 P_Api_Version_Number => 1.0,
447 P_Init_Msg_List => FND_API.G_FALSE,
448 P_Commit => FND_API.G_TRUE,
449 P_Upd_tskassgnstatus => NULL,
450 P_Task_Assignment_status => NULL,
451 P_DEBRIEF_LINE_Tbl => P_DEBRIEF_TBL ,
452 P_DEBRIEF_HEADER_ID => l_header_id,
453 P_SOURCE_OBJECT_TYPE_CODE => 'CSFW' ,
454 X_Return_Status => l_return_status ,
455 X_Msg_Count => l_msg_count ,
456 X_Msg_Data => l_msg_data
457 );
458
459 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
460 FOR l_counter IN 1 .. l_msg_count
461 LOOP
462 fnd_msg_pub.get
463 ( p_msg_index => l_counter
464 , p_encoded => FND_API.G_FALSE
465 , p_data => l_data
466 , p_msg_index_out => l_msg_index_out
467 );
468 END LOOP;
469 p_error_id := 1;
470 p_error := l_data;
471 ELSE
472 p_debrief_line_id := l_dbf_line_id; -- added to return dbf_line_id
473 END IF;
474 if p_error = FND_API.G_RET_STS_SUCCESS then
475 p_error := '';
476 end if;
477
478 EXCEPTION
479 WHEN OTHERS
480 THEN
481 p_error_id := -1;
482 p_error := SQLERRM;
483
484 END Create_Labor_Line;
485
486 PROCEDURE Create_Expense_Line
487 ( p_debrief_header_id IN NUMBER,
488 p_txn_billing_type_id IN NUMBER,
489 p_inventory_item_id IN NUMBER,
490 p_business_process_id IN NUMBER,
491 p_charge_Entry IN VARCHAR2,
492 p_incident_id IN NUMBER,
493 p_expense_amount IN NUMBER,
494 p_currency_code IN VARCHAR2,
495 p_txnTypeId IN NUMBER,
496 p_justificationCode IN VARCHAR2,
497 p_return_reason_code IN VARCHAR2,
498 p_quantity IN NUMBER,
499 p_uom_code IN VARCHAR2,
500 p_debrief_line_id OUT NOCOPY NUMBER,
501 p_error_id OUT NOCOPY NUMBER,
502 p_error OUT NOCOPY VARCHAR2,
503 p_attribute1 IN VARCHAR2 DEFAULT '$$#@',
504 p_attribute2 IN VARCHAR2 DEFAULT '$$#@',
505 p_attribute3 IN VARCHAR2 DEFAULT '$$#@',
506 p_attribute4 IN VARCHAR2 DEFAULT '$$#@',
507 p_attribute5 IN VARCHAR2 DEFAULT '$$#@',
508 p_attribute6 IN VARCHAR2 DEFAULT '$$#@',
509 p_attribute7 IN VARCHAR2 DEFAULT '$$#@',
510 p_attribute8 IN VARCHAR2 DEFAULT '$$#@',
511 p_attribute9 IN VARCHAR2 DEFAULT '$$#@',
512 p_attribute10 IN VARCHAR2 DEFAULT '$$#@',
513 p_attribute11 IN VARCHAR2 DEFAULT '$$#@',
514 p_attribute12 IN VARCHAR2 DEFAULT '$$#@',
515 p_attribute13 IN VARCHAR2 DEFAULT '$$#@',
516 p_attribute14 IN VARCHAR2 DEFAULT '$$#@',
517 p_attribute15 IN VARCHAR2 DEFAULT '$$#@',
518 p_attribute_category IN VARCHAR2 DEFAULT '$$#@',
519
520 p_expenditure_org_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
521 p_project_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
522 p_project_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
523 ) IS
524
525 l_return_status varchar2(2000);
526 l_msg_count number;
527 l_msg_data varchar2(2000);
528 l_header_id number;
529 l_dbf_line_id number;
530 l_user number;
531 l_data varchar2(2000);
532 l_msg_index_out number;
533 P_DEBRIEF_TBL CSF_DEBRIEF_PUB.DEBRIEF_LINE_Tbl_Type ;
534 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
535 l_organization_id number;
536 l_UOM_code varchar2(100);
537 l_order_header_id number;
538 l_order_type_id number;
539 l_line_type_id number;
540 l_object_version_number NUMBER;
541 l_estimate_detail_id NUMBER;
542 l_line_number NUMBER;
543 l_charge_Entry VARCHAR2(1);
544 l_attribute1 VARCHAR2(150);
545 l_attribute2 VARCHAR2(150);
546 l_attribute3 VARCHAR2(150);
547 l_attribute4 VARCHAR2(150);
548 l_attribute5 VARCHAR2(150);
549 l_attribute6 VARCHAR2(150);
550 l_attribute7 VARCHAR2(150);
551 l_attribute8 VARCHAR2(150);
552 l_attribute9 VARCHAR2(150);
553 l_attribute10 VARCHAR2(150);
554 l_attribute11 VARCHAR2(150);
555 l_attribute12 VARCHAR2(150);
556 l_attribute13 VARCHAR2(150);
557 l_attribute14 VARCHAR2(150);
558 l_attribute15 VARCHAR2(150);
559 l_attribute_category VARCHAR2(150);
560
561 l_expenditure_org_id number;
562 l_project_id number;
563 l_project_task_id number;
564
565 cursor c_dbf_lineId is select CSF_DEBRIEF_LINES_S.nextval from dual;
566 cursor c_uom_code (v_org_id number, v_item_id number)
567 is
568 select primary_uom_code from mtl_system_items_vl
569 where organization_id = v_org_id
570 and inventory_item_id = v_item_id;
571
572
573 BEGIN
574 if p_attribute1 = '$$#@' then
575 l_attribute1 := null;
576 else
577 l_attribute1 := p_attribute1;
578 end if;
579 if p_attribute2 = '$$#@' then
580 l_attribute2 := null;
581 else
582 l_attribute2 := p_attribute2;
583 end if;
587 l_attribute3 := p_attribute3;
584 if p_attribute3 = '$$#@' then
585 l_attribute3 := null;
586 else
588 end if;
589 if p_attribute4 = '$$#@' then
590 l_attribute4 := null;
591 else
592 l_attribute4 := p_attribute4;
593 end if;
594 if p_attribute5 = '$$#@' then
595 l_attribute5 := null;
596 else
597 l_attribute5 := p_attribute5;
598 end if;
599 if p_attribute6 = '$$#@' then
600 l_attribute6 := null;
601 else
602 l_attribute6 := p_attribute6;
603 end if;
604 if p_attribute7 = '$$#@' then
605 l_attribute7 := null;
606 else
607 l_attribute7 := p_attribute7;
608 end if;
609 if p_attribute8 = '$$#@' then
610 l_attribute8 := null;
611 else
612 l_attribute8 := p_attribute8;
613 end if;
614 if p_attribute9 = '$$#@' then
615 l_attribute9 := null;
616 else
617 l_attribute9 := p_attribute9;
618 end if;
619 if p_attribute10 = '$$#@' then
620 l_attribute10 := null;
621 else
622 l_attribute10 := p_attribute10;
623 end if;
624 if p_attribute11 = '$$#@' then
625 l_attribute11 := null;
626 else
627 l_attribute11 := p_attribute11;
628 end if;
629 if p_attribute12 = '$$#@' then
630 l_attribute12 := null;
631 else
632 l_attribute12 := p_attribute12;
633 end if;
634 if p_attribute13 = '$$#@' then
635 l_attribute13 := null;
636 else
637 l_attribute13 := p_attribute13;
638 end if;
639 if p_attribute14 = '$$#@' then
640 l_attribute14 := null;
641 else
642 l_attribute14 := p_attribute14;
643 end if;
644 if p_attribute15 = '$$#@' then
645 l_attribute15 := null;
646 else
647 l_attribute15 := p_attribute15;
648 end if;
649 if p_attribute_category = '$$#@' then
650 l_attribute_category := null;
651 else
652 l_attribute_category := p_attribute_category;
653 end if;
654
655 if p_expenditure_org_id <= 0 then
656 l_expenditure_org_id := null;
657 else
658 l_expenditure_org_id := p_expenditure_org_id;
659 end if;
660 if p_project_id <= 0 then
661 l_project_id := null;
662 else
663 l_project_id := p_project_id;
664 end if;
665 if p_project_task_id <= 0 then
666 l_project_task_id := null;
667 else
668 l_project_task_id := p_project_task_id;
669 end if;
670
671 l_order_header_id := 0;
672 p_error_id := 0; --Assume success
673 l_header_id := p_debrief_header_id;
674
675 open c_dbf_lineId;
676 fetch c_dbf_lineId into l_dbf_line_id;
677 close c_dbf_lineId;
678
679 l_user := FND_GLOBAL.user_id ;
680
681 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_organization_id);
682
683 open c_uom_code (l_organization_id, p_inventory_item_id);
684 fetch c_uom_code into l_UOM_code;
685 close c_uom_code;
686
687
688
689 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := l_dbf_line_id;
690 P_DEBRIEF_LINE_Rec.DEBRIEF_HEADER_ID := l_header_id;--from header record
691 P_DEBRIEF_LINE_Rec.SERVICE_DATE := sysdate;
692 P_DEBRIEF_LINE_Rec.TXN_BILLING_TYPE_ID := p_txn_billing_type_id;
693 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := p_inventory_item_id;
694 P_DEBRIEF_LINE_Rec.BUSINESS_PROCESS_ID := p_business_process_id;
695
696 IF p_expense_amount IS NOT NULL THEN
697 P_DEBRIEF_LINE_Rec.UOM_CODE := l_UOM_code;
698 P_DEBRIEF_LINE_Rec.EXPENSE_AMOUNT := p_expense_amount;
699 P_DEBRIEF_LINE_Rec.CURRENCY_CODE := p_currency_code;
700 ELSE
701 P_DEBRIEF_LINE_Rec.QUANTITY := p_quantity;
702 P_DEBRIEF_LINE_Rec.UOM_CODE := p_uom_code;
703 END IF;
704
705 P_DEBRIEF_LINE_Rec.channel_code := 'WIRELESS_USER';
706 P_DEBRIEF_LINE_Rec.issuing_inventory_org_id := l_organization_id;
707 P_DEBRIEF_LINE_Rec.TRANSACTION_TYPE_ID := p_txnTypeId;
708 P_DEBRIEF_LINE_Rec.EXPENSE_REASON_CODE := p_justificationCode;
709 P_DEBRIEF_LINE_Rec.RETURN_REASON_CODE := p_return_reason_code;
710 P_DEBRIEF_LINE_Rec.ATTRIBUTE1 := l_attribute1;
711 P_DEBRIEF_LINE_Rec.ATTRIBUTE2 := l_attribute2;
712 P_DEBRIEF_LINE_Rec.ATTRIBUTE3 := l_attribute3;
713 P_DEBRIEF_LINE_Rec.ATTRIBUTE4 := l_attribute4;
714 P_DEBRIEF_LINE_Rec.ATTRIBUTE5 := l_attribute5;
715 P_DEBRIEF_LINE_Rec.ATTRIBUTE6 := l_attribute6;
716 P_DEBRIEF_LINE_Rec.ATTRIBUTE7 := l_attribute7;
717 P_DEBRIEF_LINE_Rec.ATTRIBUTE8 := l_attribute8;
718 P_DEBRIEF_LINE_Rec.ATTRIBUTE9 := l_attribute9;
719 P_DEBRIEF_LINE_Rec.ATTRIBUTE10 := l_attribute10;
720 P_DEBRIEF_LINE_Rec.ATTRIBUTE11 := l_attribute11;
721 P_DEBRIEF_LINE_Rec.ATTRIBUTE12 := l_attribute12;
722 P_DEBRIEF_LINE_Rec.ATTRIBUTE13 := l_attribute13;
723 P_DEBRIEF_LINE_Rec.ATTRIBUTE14 := l_attribute14;
724 P_DEBRIEF_LINE_Rec.ATTRIBUTE15 := l_attribute15;
725 P_DEBRIEF_LINE_Rec.ATTRIBUTE_CATEGORY := l_attribute_category;
726
727 P_DEBRIEF_LINE_Rec.EXPENDITURE_ORG_ID := l_expenditure_org_id;
728 P_DEBRIEF_LINE_Rec.PROJECT_ID := l_project_id;
729 P_DEBRIEF_LINE_Rec.PROJECT_TASK_ID := l_project_task_id;
730
731 P_DEBRIEF_TBL (1) := P_DEBRIEF_LINE_Rec;
732 CSF_DEBRIEF_PUB.Create_debrief_lines(
733 P_Api_Version_Number => 1.0,
734 P_Init_Msg_List => FND_API.G_FALSE,
735 P_Commit => FND_API.G_TRUE,
739 P_DEBRIEF_HEADER_ID => l_header_id,
736 P_Upd_tskassgnstatus => NULL,
737 P_Task_Assignment_status => NULL,
738 P_DEBRIEF_LINE_Tbl => P_DEBRIEF_TBL ,
740 P_SOURCE_OBJECT_TYPE_CODE => 'CSFW' ,
741 X_Return_Status => l_return_status ,
742 X_Msg_Count => l_msg_count ,
743 X_Msg_Data => l_msg_data
744 );
745
746 IF l_return_status = FND_API.G_RET_STS_SUCCESS
747 THEN
748 p_debrief_line_id := l_dbf_line_id; -- Added to return newly created Line_ID
749 null;
750 ELSE
751 FOR l_counter IN 1 .. l_msg_count
752 LOOP
753 fnd_msg_pub.get
754 ( p_msg_index => l_counter
755 , p_encoded => FND_API.G_FALSE
756 , p_data => l_data
757 , p_msg_index_out => l_msg_index_out
758 );
759 END LOOP;
760 p_error_id := 1;
761 p_error := l_data;
762
763
764 END IF;
765 if p_error = FND_API.G_RET_STS_SUCCESS
766 then
767 p_error := '';
768 end if;
769
770 EXCEPTION
771 WHEN OTHERS
772 THEN
773 p_error_id := -1;
774 p_error := SQLERRM;
775
776 END Create_Expense_Line;
777
778 PROCEDURE Update_debrief_Expense_line(
779 p_debrief_line_id IN NUMBER,
780 p_expense_amount IN NUMBER,
781 p_currency_code IN VARCHAR2,
782 p_txn_billing_type_id IN NUMBER,
783 p_inventory_item_id IN NUMBER,
784 p_business_process_id IN NUMBER,
785 p_charge_Entry IN VARCHAR2,
786 p_incident_id IN NUMBER,
787 p_txnTypeId IN NUMBER,
788 p_justificationCode IN VARCHAR2,
789 p_return_reason_code IN VARCHAR2,
790 p_quantity IN NUMBER,
791 p_uom_code IN VARCHAR2,
792 p_error_id OUT NOCOPY NUMBER,
793 p_error OUT NOCOPY VARCHAR2,
794 p_attribute1 IN VARCHAR2 DEFAULT '$$#@',
795 p_attribute2 IN VARCHAR2 DEFAULT '$$#@',
796 p_attribute3 IN VARCHAR2 DEFAULT '$$#@',
797 p_attribute4 IN VARCHAR2 DEFAULT '$$#@',
798 p_attribute5 IN VARCHAR2 DEFAULT '$$#@',
799 p_attribute6 IN VARCHAR2 DEFAULT '$$#@',
800 p_attribute7 IN VARCHAR2 DEFAULT '$$#@',
801 p_attribute8 IN VARCHAR2 DEFAULT '$$#@',
802 p_attribute9 IN VARCHAR2 DEFAULT '$$#@',
803 p_attribute10 IN VARCHAR2 DEFAULT '$$#@',
804 p_attribute11 IN VARCHAR2 DEFAULT '$$#@',
805 p_attribute12 IN VARCHAR2 DEFAULT '$$#@',
806 p_attribute13 IN VARCHAR2 DEFAULT '$$#@',
807 p_attribute14 IN VARCHAR2 DEFAULT '$$#@',
808 p_attribute15 IN VARCHAR2 DEFAULT '$$#@',
809 p_attribute_category IN VARCHAR2 DEFAULT '$$#@',
810 p_expenditure_org_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
811 p_project_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
812 p_project_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
813 )IS
814
815 l_return_status varchar2(2000);
816 l_msg_count number;
817 l_msg_data varchar2(2000);
818 l_header_id number;
819 l_dbf_line_id number;
820 l_user number;
821 l_data varchar2(2000);
822 l_msg_index_out number;
823 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
824 l_organization_id number;
825 l_UOM_code varchar2(100);
826 l_order_header_id number;
827 l_order_type_id number;
828 l_line_type_id number;
829 l_object_version_number NUMBER;
830 l_estimate_detail_id NUMBER;
831 l_line_number NUMBER;
832 l_charge_Entry VARCHAR2(1);
833 l_attribute1 VARCHAR2(150);
834 l_attribute2 VARCHAR2(150);
835 l_attribute3 VARCHAR2(150);
836 l_attribute4 VARCHAR2(150);
837 l_attribute5 VARCHAR2(150);
838 l_attribute6 VARCHAR2(150);
839 l_attribute7 VARCHAR2(150);
840 l_attribute8 VARCHAR2(150);
841 l_attribute9 VARCHAR2(150);
842 l_attribute10 VARCHAR2(150);
843 l_attribute11 VARCHAR2(150);
844 l_attribute12 VARCHAR2(150);
845 l_attribute13 VARCHAR2(150);
846 l_attribute14 VARCHAR2(150);
847 l_attribute15 VARCHAR2(150);
848 l_attribute_category VARCHAR2(150);
849
850 l_expenditure_org_id number;
851 l_project_id number;
852 l_project_task_id number;
853
854 cursor c_DEBRIEF_HEADER_ID(v_debrief_line_id number ) is
855 select DEBRIEF_HEADER_ID from CSF_DEBRIEF_LINES where DEBRIEF_LINE_ID =
856 v_debrief_line_id ;
857
858 cursor c_uom_code(v_org_id number, v_item_id number) is
859 select primary_uom_code from mtl_system_items_vl
860 where organization_id = v_org_id
861 and inventory_item_id = v_item_id;
862
863 BEGIN
864
865 if p_attribute1 = '$$#@' then
866 l_attribute1 := null;
867 else
868 l_attribute1 := p_attribute1;
869 end if;
870 if p_attribute2 = '$$#@' then
871 l_attribute2 := null;
872 else
873 l_attribute2 := p_attribute2;
874 end if;
875 if p_attribute3 = '$$#@' then
876 l_attribute3 := null;
877 else
878 l_attribute3 := p_attribute3;
879 end if;
880 if p_attribute4 = '$$#@' then
881 l_attribute4 := null;
882 else
883 l_attribute4 := p_attribute4;
884 end if;
885 if p_attribute5 = '$$#@' then
886 l_attribute5 := null;
887 else
888 l_attribute5 := p_attribute5;
889 end if;
890 if p_attribute6 = '$$#@' then
891 l_attribute6 := null;
892 else
896 l_attribute7 := null;
893 l_attribute6 := p_attribute6;
894 end if;
895 if p_attribute7 = '$$#@' then
897 else
898 l_attribute7 := p_attribute7;
899 end if;
900 if p_attribute8 = '$$#@' then
901 l_attribute8 := null;
902 else
903 l_attribute8 := p_attribute8;
904 end if;
905 if p_attribute9 = '$$#@' then
906 l_attribute9 := null;
907 else
908 l_attribute9 := p_attribute9;
909 end if;
910 if p_attribute10 = '$$#@' then
911 l_attribute10 := null;
912 else
913 l_attribute10 := p_attribute10;
914 end if;
915 if p_attribute11 = '$$#@' then
916 l_attribute11 := null;
917 else
918 l_attribute11 := p_attribute11;
919 end if;
920 if p_attribute12 = '$$#@' then
921 l_attribute12 := null;
922 else
923 l_attribute12 := p_attribute12;
924 end if;
925 if p_attribute13 = '$$#@' then
926 l_attribute13 := null;
927 else
928 l_attribute13 := p_attribute13;
929 end if;
930 if p_attribute14 = '$$#@' then
931 l_attribute14 := null;
932 else
933 l_attribute14 := p_attribute14;
934 end if;
935 if p_attribute15 = '$$#@' then
936 l_attribute15 := null;
937 else
938 l_attribute15 := p_attribute15;
939 end if;
940 if p_attribute_category = '$$#@' then
941 l_attribute_category := null;
942 else
943 l_attribute_category := p_attribute_category;
944 end if;
945
946 if p_expenditure_org_id <= 0 then
947 l_expenditure_org_id := null;
948 else
949 l_expenditure_org_id := p_expenditure_org_id;
950 end if;
951 if p_project_id <= 0 then
952 l_project_id := null;
953 else
954 l_project_id := p_project_id;
955 end if;
956 if p_project_task_id <= 0 then
957 l_project_task_id := null;
958 else
959 l_project_task_id := p_project_task_id;
960 end if;
961
962 p_error_id := 0; --Assume success
963
964 open c_DEBRIEF_HEADER_ID(p_debrief_line_id);
965 fetch c_DEBRIEF_HEADER_ID into l_header_id;
966 close c_DEBRIEF_HEADER_ID;
967
968 l_user := FND_GLOBAL.user_id ;
969
970 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_organization_id);
971
972 open c_uom_code (l_organization_id, p_inventory_item_id);
973 fetch c_uom_code into l_UOM_code;
974 close c_uom_code;
975
976 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := p_debrief_line_id;
977 P_DEBRIEF_LINE_Rec.DEBRIEF_HEADER_ID := l_header_id;--from header record
978 P_DEBRIEF_LINE_Rec.TXN_BILLING_TYPE_ID := p_txn_billing_type_id;
979 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := p_inventory_item_id;
980 P_DEBRIEF_LINE_Rec.BUSINESS_PROCESS_ID := p_business_process_id;
981
982 IF p_expense_amount IS NOT NULL THEN
983 P_DEBRIEF_LINE_Rec.UOM_CODE := l_UOM_code;
984 P_DEBRIEF_LINE_Rec.EXPENSE_AMOUNT := p_expense_amount;
985 P_DEBRIEF_LINE_Rec.CURRENCY_CODE := p_currency_code;
986 P_DEBRIEF_LINE_Rec.QUANTITY := null;
987 ELSE
988 P_DEBRIEF_LINE_Rec.QUANTITY := p_quantity;
989 P_DEBRIEF_LINE_Rec.UOM_CODE := p_uom_code;
990 P_DEBRIEF_LINE_Rec.EXPENSE_AMOUNT := null;
991 P_DEBRIEF_LINE_Rec.CURRENCY_CODE := null;
992 END IF;
993
994 P_DEBRIEF_LINE_Rec.channel_code := 'WIRELESS_USER';
995 P_DEBRIEF_LINE_Rec.issuing_inventory_org_id := l_organization_id;
996 P_DEBRIEF_LINE_Rec.TRANSACTION_TYPE_ID := p_txnTypeId;
997 P_DEBRIEF_LINE_Rec.EXPENSE_REASON_CODE := p_justificationCode;
998 P_DEBRIEF_LINE_Rec.RETURN_REASON_CODE := p_return_reason_code;
999 P_DEBRIEF_LINE_Rec.ATTRIBUTE1 := l_attribute1;
1000 P_DEBRIEF_LINE_Rec.ATTRIBUTE2 := l_attribute2;
1001 P_DEBRIEF_LINE_Rec.ATTRIBUTE3 := l_attribute3;
1002 P_DEBRIEF_LINE_Rec.ATTRIBUTE4 := l_attribute4;
1003 P_DEBRIEF_LINE_Rec.ATTRIBUTE5 := l_attribute5;
1004 P_DEBRIEF_LINE_Rec.ATTRIBUTE6 := l_attribute6;
1005 P_DEBRIEF_LINE_Rec.ATTRIBUTE7 := l_attribute7;
1006 P_DEBRIEF_LINE_Rec.ATTRIBUTE8 := l_attribute8;
1007 P_DEBRIEF_LINE_Rec.ATTRIBUTE9 := l_attribute9;
1008 P_DEBRIEF_LINE_Rec.ATTRIBUTE10 := l_attribute10;
1009 P_DEBRIEF_LINE_Rec.ATTRIBUTE11 := l_attribute11;
1010 P_DEBRIEF_LINE_Rec.ATTRIBUTE12 := l_attribute12;
1011 P_DEBRIEF_LINE_Rec.ATTRIBUTE13 := l_attribute13;
1012 P_DEBRIEF_LINE_Rec.ATTRIBUTE14 := l_attribute14;
1013 P_DEBRIEF_LINE_Rec.ATTRIBUTE15 := l_attribute15;
1014 P_DEBRIEF_LINE_Rec.ATTRIBUTE_CATEGORY := l_attribute_category;
1015
1016 P_DEBRIEF_LINE_Rec.EXPENDITURE_ORG_ID := l_expenditure_org_id;
1017 P_DEBRIEF_LINE_Rec.PROJECT_ID := l_project_id;
1018 P_DEBRIEF_LINE_Rec.PROJECT_TASK_ID := l_project_task_id;
1019
1020 CSF_DEBRIEF_PUB.Update_debrief_line(
1021 P_Api_Version_Number => 1.0,
1022 P_Init_Msg_List => FND_API.G_FALSE,
1023 P_Commit => FND_API.G_FALSE,
1024 P_Upd_tskassgnstatus => NULL,
1025 P_Task_Assignment_status => NULL,
1026 P_DEBRIEF_LINE_Rec => P_DEBRIEF_LINE_Rec,
1027 X_Return_Status => l_return_status ,
1028 X_Msg_Count => l_msg_count ,
1029 X_Msg_Data => l_msg_data
1030 );
1031
1032
1033 IF l_return_status = FND_API.G_RET_STS_SUCCESS
1034 THEN
1035 NULL;
1036 ELSE
1037 FOR l_counter IN 1 .. l_msg_count
1038 LOOP
1039 fnd_msg_pub.get
1040 ( p_msg_index => l_counter
1044 );
1041 , p_encoded => FND_API.G_FALSE
1042 , p_data => l_data
1043 , p_msg_index_out => l_msg_index_out
1045 END LOOP;
1046 p_error_id := 1;
1047 p_error := l_data;
1048
1049
1050 END IF;
1051
1052 if p_error = FND_API.G_RET_STS_SUCCESS
1053 then
1054 p_error := '';
1055 end if;
1056
1057 EXCEPTION
1058 WHEN OTHERS
1059 THEN
1060 p_error_id := -1;
1061 p_error := SQLERRM;
1062
1063 END Update_debrief_Expense_line;
1064
1065 -- Bug Number : 4543409, added quantity and uom
1066 PROCEDURE Update_debrief_Labor_line(
1067 p_debrief_line_id IN NUMBER,
1068 p_labor_start_date IN DATE,
1069 p_labor_end_date IN DATE,
1070 p_service_date IN DATE,
1071 p_txn_billing_type_id IN NUMBER,
1072 p_inventory_item_id IN NUMBER,
1073 p_business_process_id IN NUMBER,
1074 p_charge_Entry IN VARCHAR2,
1075 p_incident_id IN NUMBER,
1076 p_txnTypeId IN NUMBER,
1077 p_quantity IN NUMBER,
1078 p_uom IN VARCHAR2,
1079 p_justificationCode IN VARCHAR2,
1080 p_return_reason_code IN VARCHAR2,
1081 p_error_id OUT NOCOPY NUMBER,
1082 p_error OUT NOCOPY VARCHAR2,
1083 p_attribute1 IN VARCHAR2 DEFAULT '$$#@',
1084 p_attribute2 IN VARCHAR2 DEFAULT '$$#@',
1085 p_attribute3 IN VARCHAR2 DEFAULT '$$#@',
1086 p_attribute4 IN VARCHAR2 DEFAULT '$$#@',
1087 p_attribute5 IN VARCHAR2 DEFAULT '$$#@',
1088 p_attribute6 IN VARCHAR2 DEFAULT '$$#@',
1089 p_attribute7 IN VARCHAR2 DEFAULT '$$#@',
1090 p_attribute8 IN VARCHAR2 DEFAULT '$$#@',
1091 p_attribute9 IN VARCHAR2 DEFAULT '$$#@',
1092 p_attribute10 IN VARCHAR2 DEFAULT '$$#@',
1093 p_attribute11 IN VARCHAR2 DEFAULT '$$#@',
1094 p_attribute12 IN VARCHAR2 DEFAULT '$$#@',
1095 p_attribute13 IN VARCHAR2 DEFAULT '$$#@',
1096 p_attribute14 IN VARCHAR2 DEFAULT '$$#@',
1097 p_attribute15 IN VARCHAR2 DEFAULT '$$#@',
1098 p_attribute_category IN VARCHAR2 DEFAULT '$$#@',
1099
1100 p_expenditure_org_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1101 p_project_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1102 p_project_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
1103 )IS
1104
1105 l_resource_type VARCHAR2(30);
1106 l_resource_id NUMBER;
1107
1108 l_return_status varchar2(2000);
1109 l_msg_count number;
1110 l_msg_data varchar2(2000);
1111 l_header_id number;
1112 l_dbf_line_id number;
1113 l_user number;
1114 l_data varchar2(2000);
1115 l_msg_index_out number;
1116 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
1117 l_organization_id number;
1118 l_UOM_code varchar2(100);
1119 l_order_header_id number;
1120 l_order_type_id number;
1121 l_line_type_id number;
1122
1123 l_object_version_number NUMBER;
1124 l_estimate_detail_id NUMBER;
1125 l_line_number NUMBER;
1126
1127 l_dt_format varchar2(20);
1128 l_sr_date DATE;
1129 l_charge_Entry varchar2(1);
1130 l_attribute1 VARCHAR2(150);
1131 l_attribute2 VARCHAR2(150);
1132 l_attribute3 VARCHAR2(150);
1133 l_attribute4 VARCHAR2(150);
1134 l_attribute5 VARCHAR2(150);
1135 l_attribute6 VARCHAR2(150);
1136 l_attribute7 VARCHAR2(150);
1137 l_attribute8 VARCHAR2(150);
1138 l_attribute9 VARCHAR2(150);
1139 l_attribute10 VARCHAR2(150);
1140 l_attribute11 VARCHAR2(150);
1141 l_attribute12 VARCHAR2(150);
1142 l_attribute13 VARCHAR2(150);
1143 l_attribute14 VARCHAR2(150);
1144 l_attribute15 VARCHAR2(150);
1145 l_attribute_category VARCHAR2(150);
1146
1147 l_expenditure_org_id number;
1148 l_project_id number;
1149 l_project_task_id number;
1150
1151 cursor c_DEBRIEF_HEADER_ID(v_debrief_line_id number ) is
1152 select DEBRIEF_HEADER_ID from CSF_DEBRIEF_LINES where DEBRIEF_LINE_ID =
1153 v_debrief_line_id;
1154
1155 cursor c_uom_code(v_org_id number, v_item_id number) is
1156 select primary_uom_code from mtl_system_items_vl
1157 where organization_id = v_org_id
1158 and inventory_item_id = v_item_id;
1159
1160 cursor c_creation_date(v_incident_id number) is
1161 select creation_date from cs_incidents_all_b where incident_id = v_incident_id;
1162 l_validate_lab_time_msg varchar2(255);
1163
1164
1165 cursor find_resource is
1166 select a.resource_id resource_id, b.resource_type resource_type
1167 from CSP_RS_RESOURCES_V b, jtf_rs_resource_extns a
1168 where a.resource_id =b.resource_id
1169 and a.user_id = FND_GLOBAL.USER_ID;
1170
1171 r_find_resource find_resource%ROWTYPE;
1172
1173 BEGIN
1174
1175 if p_attribute1 = '$$#@' then
1176 l_attribute1 := null;
1177 else
1178 l_attribute1 := p_attribute1;
1179 end if;
1180 if p_attribute2 = '$$#@' then
1181 l_attribute2 := null;
1182 else
1183 l_attribute2 := p_attribute2;
1184 end if;
1185 if p_attribute3 = '$$#@' then
1186 l_attribute3 := null;
1187 else
1188 l_attribute3 := p_attribute3;
1189 end if;
1190 if p_attribute4 = '$$#@' then
1191 l_attribute4 := null;
1192 else
1193 l_attribute4 := p_attribute4;
1194 end if;
1195 if p_attribute5 = '$$#@' then
1196 l_attribute5 := null;
1197 else
1201 l_attribute6 := null;
1198 l_attribute5 := p_attribute5;
1199 end if;
1200 if p_attribute6 = '$$#@' then
1202 else
1203 l_attribute6 := p_attribute6;
1204 end if;
1205 if p_attribute7 = '$$#@' then
1206 l_attribute7 := null;
1207 else
1208 l_attribute7 := p_attribute7;
1209 end if;
1210 if p_attribute8 = '$$#@' then
1211 l_attribute8 := null;
1212 else
1213 l_attribute8 := p_attribute8;
1214 end if;
1215 if p_attribute9 = '$$#@' then
1216 l_attribute9 := null;
1217 else
1218 l_attribute9 := p_attribute9;
1219 end if;
1220 if p_attribute10 = '$$#@' then
1221 l_attribute10 := null;
1222 else
1223 l_attribute10 := p_attribute10;
1224 end if;
1225 if p_attribute11 = '$$#@' then
1226 l_attribute11 := null;
1227 else
1228 l_attribute11 := p_attribute11;
1229 end if;
1230 if p_attribute12 = '$$#@' then
1231 l_attribute12 := null;
1232 else
1233 l_attribute12 := p_attribute12;
1234 end if;
1235 if p_attribute13 = '$$#@' then
1236 l_attribute13 := null;
1237 else
1238 l_attribute13 := p_attribute13;
1239 end if;
1240 if p_attribute14 = '$$#@' then
1241 l_attribute14 := null;
1242 else
1243 l_attribute14 := p_attribute14;
1244 end if;
1245 if p_attribute15 = '$$#@' then
1246 l_attribute15 := null;
1247 else
1248 l_attribute15 := p_attribute15;
1249 end if;
1250 if p_attribute_category = '$$#@' then
1251 l_attribute_category := null;
1252 else
1253 l_attribute_category := p_attribute_category;
1254 end if;
1255
1256 if p_expenditure_org_id <= 0 then
1257 l_expenditure_org_id := null;
1258 else
1259 l_expenditure_org_id := p_expenditure_org_id;
1260 end if;
1261 if p_project_id <= 0 then
1262 l_project_id := null;
1263 else
1264 l_project_id := p_project_id;
1265 end if;
1266 if p_project_task_id <= 0 then
1267 l_project_task_id := null;
1268 else
1269 l_project_task_id := p_project_task_id;
1270 end if;
1271
1272 p_error_id := 0; --Assume success
1273
1274 open c_DEBRIEF_HEADER_ID(p_debrief_line_id);
1275 fetch c_DEBRIEF_HEADER_ID into l_header_id;
1276 close c_DEBRIEF_HEADER_ID;
1277
1278
1279 --select FND_GLOBAL.user_id into l_user from dual;
1280 l_user :=FND_GLOBAL.user_id ;
1281
1282 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_organization_id);
1283
1284
1285
1286 -------------------------------------------------------------------------------------------------
1287 -------
1288 ------- check for the Service date, which should not be less than SR date and more than Sysdate
1289 -------
1290 -------------------------------------------------------------------------------------------------
1291 -- first get the date format
1292 -- Bug 2862796. Using ICX: Date Format Mask in place of CSFW: Date Format.
1293 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK', l_dt_format);
1294 -- Now get the SR Date in date format
1295 open c_creation_date(p_incident_id);
1296 fetch c_creation_date into l_sr_date;
1297 close c_creation_date;
1298
1299
1300 -- now check if it is more than sysdate
1301 -- BUG 2225745
1302 -- if l_sr_date is p_service_date, then make l_sr_date = l_sr_date - 1
1303 --WE do not need this as these are all date ...
1304 -- IF trunc(l_sr_date) = trunc(p_service_date)
1305 -- THEN
1306 -- l_sr_date := l_sr_date - 1;
1307 -- END IF;
1308
1309 /*
1310
1311 IF p_service_date between l_sr_date and sysdate
1312 THEN
1313 null;
1314 ELSE
1315 FND_MESSAGE.Set_Name('CSF', 'CSFW_SERVICE_DATE');
1316 FND_MESSAGE.Set_Token('P_SR_DATE', to_char(l_sr_date, l_dt_format));
1317 FND_MESSAGE.Set_Token('P_SYSTEM_DATE', to_char(sysdate, l_dt_format));
1318 p_error := -21;
1319 p_error := FND_MESSAGE.Get;
1320 return;
1321
1322 END IF;
1323 */
1324 IF (p_labor_start_date >= p_labor_end_date) THEN
1325 FND_MESSAGE.Set_Name('CSF', 'CSFW_START_END_DATE_SAME');
1326 p_error := -30;
1327 p_error := FND_MESSAGE.Get;
1328 RETURN;
1329 END IF;
1330
1331 -- CALL THE VALIDATE FUNCTION
1332 OPEN find_resource;
1333 fetch find_resource INTO r_find_resource;
1334 close find_resource;
1335 l_validate_lab_time_msg := validate_labor_time(r_find_resource.resource_type, r_find_resource.resource_id, l_dbf_line_id,p_labor_start_date, p_labor_end_date);
1336 IF l_validate_lab_time_msg <> 'S' THEN
1337 p_error := -22;
1338 p_error := l_validate_lab_time_msg;
1339 RETURN;
1340 END IF;
1341
1342
1343 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := p_debrief_line_id;
1344 P_DEBRIEF_LINE_Rec.DEBRIEF_HEADER_ID := l_header_id;--from header record
1345 P_DEBRIEF_LINE_Rec.SERVICE_DATE := p_service_date;
1346 P_DEBRIEF_LINE_Rec.TXN_BILLING_TYPE_ID := p_txn_billing_type_id;
1347 IF p_inventory_item_id <> 0 THEN
1348 open c_uom_code(l_organization_id, p_inventory_item_id);
1349 fetch c_uom_code into l_UOM_code;
1350 close c_uom_code;
1351
1352 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := p_inventory_item_id;
1353 P_DEBRIEF_LINE_Rec.UOM_CODE := l_UOM_code;
1354 ELSE
1355 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := NULL; -- Bug Number : 3491830
1356 END IF;
1357
1358 P_DEBRIEF_LINE_Rec.BUSINESS_PROCESS_ID := p_business_process_id;
1359 P_DEBRIEF_LINE_Rec.LABOR_START_DATE := p_labor_start_date;
1360 P_DEBRIEF_LINE_Rec.LABOR_END_DATE := p_labor_end_date;
1361 P_DEBRIEF_LINE_Rec.channel_code := 'WIRELESS_USER';
1365 P_DEBRIEF_LINE_Rec.LABOR_REASON_CODE := p_justificationCode;
1362 P_DEBRIEF_LINE_Rec.issuing_inventory_org_id := l_organization_id;
1363 P_DEBRIEF_LINE_Rec.TRANSACTION_TYPE_ID := p_txnTypeId;
1364
1366 P_DEBRIEF_LINE_Rec.RETURN_REASON_CODE := p_return_reason_code;
1367 P_DEBRIEF_LINE_Rec.ATTRIBUTE1 := l_attribute1;
1368 P_DEBRIEF_LINE_Rec.ATTRIBUTE2 := l_attribute2;
1369 P_DEBRIEF_LINE_Rec.ATTRIBUTE3 := l_attribute3;
1370 P_DEBRIEF_LINE_Rec.ATTRIBUTE4 := l_attribute4;
1371 P_DEBRIEF_LINE_Rec.ATTRIBUTE5 := l_attribute5;
1372 P_DEBRIEF_LINE_Rec.ATTRIBUTE6 := l_attribute6;
1373 P_DEBRIEF_LINE_Rec.ATTRIBUTE7 := l_attribute7;
1374 P_DEBRIEF_LINE_Rec.ATTRIBUTE8 := l_attribute8;
1375 P_DEBRIEF_LINE_Rec.ATTRIBUTE9 := l_attribute9;
1376 P_DEBRIEF_LINE_Rec.ATTRIBUTE10 := l_attribute10;
1377 P_DEBRIEF_LINE_Rec.ATTRIBUTE11 := l_attribute11;
1378 P_DEBRIEF_LINE_Rec.ATTRIBUTE12 := l_attribute12;
1379 P_DEBRIEF_LINE_Rec.ATTRIBUTE13 := l_attribute13;
1380 P_DEBRIEF_LINE_Rec.ATTRIBUTE14 := l_attribute14;
1381 P_DEBRIEF_LINE_Rec.ATTRIBUTE15 := l_attribute15;
1382 P_DEBRIEF_LINE_Rec.ATTRIBUTE_CATEGORY := l_attribute_category;
1383
1384 -- Bug 4543409
1385 P_DEBRIEF_LINE_Rec.UOM_CODE := p_uom;
1386 IF p_quantity IS NOT NULL THEN
1387 P_DEBRIEF_LINE_Rec.QUANTITY := p_quantity;
1388 END IF;
1389
1390 P_DEBRIEF_LINE_Rec.EXPENDITURE_ORG_ID := l_expenditure_org_id;
1391 P_DEBRIEF_LINE_Rec.PROJECT_ID := l_project_id;
1392 P_DEBRIEF_LINE_Rec.PROJECT_TASK_ID := l_project_task_id;
1393
1394 CSF_DEBRIEF_PUB.Update_debrief_line(
1395 P_Api_Version_Number => 1.0,
1396 P_Init_Msg_List => FND_API.G_FALSE,
1397 P_Commit => FND_API.G_FALSE,
1398 P_Upd_tskassgnstatus => NULL,
1399 P_Task_Assignment_status => NULL,
1400 P_DEBRIEF_LINE_Rec => P_DEBRIEF_LINE_Rec,
1401 X_Return_Status => l_return_status ,
1402 X_Msg_Count => l_msg_count ,
1403 X_Msg_Data => l_msg_data
1404 );
1405
1406
1407 IF l_return_status = FND_API.G_RET_STS_SUCCESS
1408 THEN
1409 NULL;
1410 ELSE
1411 FOR l_counter IN 1 .. l_msg_count
1412 LOOP
1413 fnd_msg_pub.get
1414 ( p_msg_index => l_counter
1415 , p_encoded => FND_API.G_FALSE
1416 , p_data => l_data
1417 , p_msg_index_out => l_msg_index_out
1418 );
1419 END LOOP;
1420 p_error_id := 1;
1421 p_error := l_data;
1422
1423 END IF;
1424
1425 if p_error = FND_API.G_RET_STS_SUCCESS
1426 then
1427 p_error := '';
1428 end if;
1429
1430 EXCEPTION
1431 WHEN OTHERS
1432 THEN
1433 p_error_id := -1;
1434 p_error := SQLERRM;
1435
1436 END Update_debrief_Labor_line;
1437
1438 PROCEDURE SAVE_DEBRIEF_MATERIAL_LINE (
1439 p_taskid IN VARCHAR2,
1440 p_taskassignmentid IN VARCHAR2,
1441 p_incidentid IN VARCHAR2,
1442 p_partyid IN VARCHAR2,
1443 p_dbfNr IN VARCHAR2,
1444 p_billingTypeId IN VARCHAR2,
1445 p_txnTypeId IN VARCHAR2,
1446 p_orderCategoryCode IN VARCHAR2,
1447 p_txnTypeName IN VARCHAR2,
1448 p_itemId IN VARCHAR2,
1449 p_revisionFlag IN VARCHAR2,
1450 p_businessProcessId IN VARCHAR2,
1451 p_subTypeId IN VARCHAR2,
1452 p_updateIBFlag IN VARCHAR2,
1453 p_srcChangeOwner IN VARCHAR2,
1454 p_srcChangeOwnerToCode IN VARCHAR2,
1455 p_srcReferenceReqd IN VARCHAR2,
1456 p_srcReturnReqd IN VARCHAR2,
1457 p_parentReferenceReqd IN VARCHAR2,
1458 p_srcStatusId IN VARCHAR2,
1459 p_srcStatusName IN VARCHAR2,
1460 p_csiTxnTypeId IN VARCHAR2,
1461 p_subInv IN VARCHAR2,
1462 p_orgId IN VARCHAR2,
1463 p_serviceDate IN VARCHAR2,
1464 p_qty IN VARCHAR2,
1465 p_chgFlag IN VARCHAR2,
1466 p_ibFlag IN VARCHAR2,
1467 p_invFlag IN VARCHAR2,
1468 p_reasonCd IN VARCHAR2,
1469 p_instanceId IN VARCHAR2,
1470 p_parentProductId IN VARCHAR2,
1471 p_partStatusCd IN VARCHAR2,
1472 p_recoveredPartId IN VARCHAR2,
1473 p_retReasonCd IN VARCHAR2,
1474 p_serialNr IN VARCHAR2,
1475 p_lotNr IN VARCHAR2,
1476 p_revisionNr IN VARCHAR2,
1477 p_locatorId IN VARCHAR2,
1478 p_UOM IN VARCHAR2,
1479 p_updateFlag IN NUMBER,
1480 p_dbfLineId IN NUMBER,
1481 p_ret_dbfLine_id OUT NOCOPY NUMBER,
1482 p_error_id OUT NOCOPY NUMBER,
1483 p_error OUT NOCOPY VARCHAR2,
1484 p_return_date IN VARCHAR2,
1485
1486 p_usage_type varchar2,
1487 p_dest_organization_id number,
1488 p_dest_subinventory_name varchar2,
1489 p_carrier_code varchar2,
1490 p_shipping_method varchar2,
1491 p_shipment_number varchar2,
1492 p_waybill varchar2,
1493 p_attribute1 IN VARCHAR2 DEFAULT '$$#@',
1494 p_attribute2 IN VARCHAR2 DEFAULT '$$#@',
1495 p_attribute3 IN VARCHAR2 DEFAULT '$$#@',
1496 p_attribute4 IN VARCHAR2 DEFAULT '$$#@',
1497 p_attribute5 IN VARCHAR2 DEFAULT '$$#@',
1498 p_attribute6 IN VARCHAR2 DEFAULT '$$#@',
1499 p_attribute7 IN VARCHAR2 DEFAULT '$$#@',
1500 p_attribute8 IN VARCHAR2 DEFAULT '$$#@',
1504 p_attribute12 IN VARCHAR2 DEFAULT '$$#@',
1501 p_attribute9 IN VARCHAR2 DEFAULT '$$#@',
1502 p_attribute10 IN VARCHAR2 DEFAULT '$$#@',
1503 p_attribute11 IN VARCHAR2 DEFAULT '$$#@',
1505 p_attribute13 IN VARCHAR2 DEFAULT '$$#@',
1506 p_attribute14 IN VARCHAR2 DEFAULT '$$#@',
1507 p_attribute15 IN VARCHAR2 DEFAULT '$$#@',
1508 p_attribute_category IN VARCHAR2 DEFAULT '$$#@',
1509
1510 p_expenditure_org_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1511 p_project_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1512 p_project_task_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
1513
1514 )IS
1515
1516 --l_return_status varchar2(2000);
1517 l_msg_count number;
1518 l_msg_data varchar2(2000);
1519 l_header_id number;
1520 l_dbf_line_id number;
1521 l_user number;
1522 l_data varchar2(2000);
1523 l_msg_index_out number;
1524 P_DEBRIEF_TBL CSF_DEBRIEF_PUB.DEBRIEF_LINE_Tbl_Type ;
1525 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
1526 l_organization_id number;
1527 l_UOM_code varchar2(100);
1528 l_order_header_id number;
1529 l_order_type_id number;
1530 l_line_type_id number;
1531 l_Charges_Rec CS_Charge_Details_PUB.Charges_Rec_Type;
1532 l_object_version_number NUMBER;
1533 l_estimate_detail_id NUMBER;
1534 l_line_number NUMBER;
1535 l_dt_format varchar2(20);
1536 l_sr_date DATE;
1537 l_interface_status varchar2(20);
1538 l_interface_status_meaning varchar2(20);
1539
1540 /* FOR INVENTORY */
1541 l_transaction_type_id number ;
1542 l_lot_number varchar2(30) ;
1543 l_revision varchar2(3) ;
1544 l_serial_number varchar2(30) ;
1545 l_transfer_to_subinventory varchar2(10):= NULL; --optional
1546 l_transfer_to_locator number := NULL; --optional
1547 l_transfer_to_organization number := NULL; --optional
1548 l_api_version number := 1.1;
1549 l_account_id number ;
1550 mesg Varchar2(2000);
1551 l_locator_id number;
1552 lx_transaction_header_id number;
1553 lx_transaction_id number;
1554
1555 /* for IB */
1556 l_in_out_flag varchar2(4);
1557 l_transaction_type_id_csi number ;
1558 l_txn_sub_type_id number ;
1559
1560 l_instance_id number ;
1561 l_parent_instance_id number ;
1562 l_new_instance_id number := null;
1563 l_new_instance_number Varchar2(40);
1564
1565 l_inventory_item_id number ;
1566 l_inv_organization_id number ;
1567 l_inv_subinventory_name varchar2(60);
1568 l_inv_master_organization_id number ;
1569 l_quantity number ;
1570 l_mfg_serial_number_flag varchar2(3) ;
1571 l_service_date date ;
1572 l_shipped_date date;
1573 l_currency_code varchar2(10);
1574
1575 l_party_id number ;
1576 l_party_account_id number ;
1577 l_customer_id number ;
1578 l_party_site_id number;
1579
1580 l_debrief_line_id number ;
1581 l_debrief_header_id number ;
1582 l_incident_id number ;
1583
1584
1585 l_return_status varchar2(1);
1586 l_mesg varchar2(2000);
1587 l_counter number;
1588 l_install_site_use_id number;
1589 l_ship_site_use_id number;
1590 l_parent_cpid number;
1591
1592 /* declaration for all the fields to check for null */
1593 l_taskid Number;
1594 l_taskassignmentid Number;
1595 l_incidentid Number;
1596 l_partyid Number;
1597 l_dbfNr varchar2(30);
1598 l_billingTypeId Number;
1599 l_txnTypeId Number;
1600 l_orderCategoryCode varchar2(30);
1601 l_txnTypeName varchar2(30);
1602 l_itemId Number;
1603 l_revisionFlag varchar2(30);
1604 l_businessProcessId Number;
1605 l_subTypeId Number;
1606 l_updateIBFlag varchar2(30);
1607 l_srcChangeOwner varchar2(30);
1608 l_srcChangeOwnerToCode varchar2(30);
1609 l_srcReferenceReqd varchar2(30);
1610 l_srcReturnReqd varchar2(30);
1611 l_parentReferenceReqd varchar2(30);
1612 l_srcStatusId Number;
1613 l_srcStatusName varchar2(30);
1614 l_csiTxnTypeId Number;
1615 l_subInv varchar2(30);
1616 l_orgId Number;
1617 l_serviceDate varchar2(30);
1618 l_qty Number;
1619 l_chgFlag varchar2(30);
1620 l_ibFlag varchar2(30);
1621 l_invFlag varchar2(30);
1622 l_reasonCd varchar2(30);
1623 l_instanceId Number;
1624 l_parentProductId Number;
1625 l_partStatusCd varchar2(30);
1626 l_recoveredPartId Number;
1627 l_retReasonCd varchar2(30);
1628 l_serialNr varchar2(30);
1629 l_lotNr varchar2(30);
1630 l_revisionNr varchar2(30);
1631 l_locatorId Number;
1632 l_UOM varchar2(30);
1633 lp_servicedate DATE;
1634
1635 l_client_tz_id number;
1636 l_server_tz_id number;
1637 l_server_time date;
1638
1639 l_part_status_name varchar2(30);
1640
1641 l_usage_type varchar2(10);
1642 l_dest_organization_id number;
1643 l_dest_subinventory_name varchar2(10);
1644 l_carrier_code varchar2(25);
1645 l_shipping_method varchar2(60);
1646 l_shipment_number varchar2(60);
1647 l_waybill varchar2(60);
1648 l_attribute1 VARCHAR2(150);
1649 l_attribute2 VARCHAR2(150);
1653 l_attribute6 VARCHAR2(150);
1650 l_attribute3 VARCHAR2(150);
1651 l_attribute4 VARCHAR2(150);
1652 l_attribute5 VARCHAR2(150);
1654 l_attribute7 VARCHAR2(150);
1655 l_attribute8 VARCHAR2(150);
1656 l_attribute9 VARCHAR2(150);
1657 l_attribute10 VARCHAR2(150);
1658 l_attribute11 VARCHAR2(150);
1659 l_attribute12 VARCHAR2(150);
1660 l_attribute13 VARCHAR2(150);
1661 l_attribute14 VARCHAR2(150);
1662 l_attribute15 VARCHAR2(150);
1663 l_attribute_category VARCHAR2(150);
1664
1665 l_expenditure_org_id number;
1666 l_project_id number;
1667 l_project_task_id number;
1668
1669 Cursor c_site (p_incident_id number) Is
1670 select install_site_use_id,
1671 ship_to_site_use_id
1672 from cs_incidents_all
1673 where incident_id = p_incident_id;
1674 cursor c_party_site_id (p_install_site_id number) Is
1675 select party_site_id
1676 from hz_party_site_uses
1677 where party_site_use_id = p_install_site_id;
1678 Cursor c_instance_number(p_instance_id Number) is
1679 select instance_number
1680 from csi_item_instances
1681 where instance_id = p_instance_id;
1682
1683 Cursor c_internal_party_id Is
1684 select internal_party_id
1685 from csi_install_parameters;
1686
1687
1688 cursor c_line_type_id_order (p_order_type_id number,p_incident_id Number) is
1689 select default_outbound_line_type_id
1690 from oe_transaction_types_all
1691 where transaction_type_id = p_order_type_id
1692 and transaction_type_code = 'ORDER';
1693
1694 cursor c_line_type_id_return (p_order_type_id number,p_incident_id number) is
1695 select default_inbound_line_type_id
1696 from oe_transaction_types_all
1697 where transaction_type_id = p_order_type_id
1698 and transaction_type_code = 'ORDER';
1699
1700
1701
1702 Cursor c_status_meaning(p_code Varchar2) Is
1703 select meaning
1704 from fnd_lookups
1705 where lookup_type = 'CSF_INTERFACE_STATUS'
1706 and lookup_code = p_code;
1707
1708
1709 /* commenting this cursor since it's used no where
1710 cursor c_party(b_dbfId number) is
1711 select customer_id,customer_account_id from csf_debrief_tasks_v where debrief_header_id = b_dbfId;
1712 r_party c_party%ROWTYPE;
1713 */
1714
1715 cursor c_DEBRIEF_HEADER_ID(v_dbf_nr varchar2) is
1716 select DEBRIEF_HEADER_ID from csf_debrief_headers where debrief_number = v_dbf_nr;
1717
1718
1719 cursor c_dbf_lines is select CSF_DEBRIEF_LINES_S.nextval from dual;
1720
1721 cursor c_status_name(v_partStatusCd varchar2) is
1722 select name
1723 from csi_instance_statuses
1724 where INSTANCE_STATUS_ID = v_partStatusCd;
1725
1726 cursor c_creation_date(v_incident_id number) is
1727 select creation_date from cs_incidents_all_b where incident_id = v_incident_id;
1728
1729
1730
1731 BEGIN
1732
1733 --dbms_output.put_line('BEGINING....');
1734 l_client_tz_id := to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID'));
1735 l_server_tz_id := to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
1736
1737 -- first lets get the nulls out
1738 if p_taskid = '$$#@' then
1739 l_taskid := null;
1740 else
1741 l_taskid := to_number(p_taskid);
1742 end if;
1743 if p_itemId= '$$#@' then
1744 l_itemId := null;
1745 else
1746 l_itemId := to_number(p_itemId);
1747 end if;
1748
1749 if p_taskassignmentid = '$$#@' then
1750 l_taskassignmentid := null;
1751 else
1752 l_taskassignmentid := to_number(p_taskassignmentid);
1753 end if;
1754
1755 if p_incidentid = '$$#@' then
1756 l_incidentid := null;
1757 else
1758 l_incidentid := to_number(p_incidentid);
1759 end if;
1760
1761 if p_partyid = '$$#@' then
1762 l_partyid := null;
1763 else
1764 l_partyid := to_number(p_partyid);
1765 end if;
1766
1767 if p_dbfNr = '$$#@' then
1768 l_dbfNr := null;
1769 else
1770 l_dbfNr := p_dbfNr;
1771 end if;
1772
1773 if p_billingTypeId = '$$#@' then
1774 l_billingTypeId := null;
1775 else
1776 l_billingTypeId := to_number(p_billingTypeId);
1777 end if;
1778 if p_txnTypeId = '$$#@' then
1779 l_txnTypeId := null;
1780 else
1781 l_txnTypeId := to_number(p_txnTypeId);
1782 end if;
1783 if p_businessProcessId = '$$#@' then
1784 l_businessProcessId := null;
1785 else
1786 l_businessProcessId := to_number(p_businessProcessId);
1787 end if;
1788 if p_subTypeId = '$$#@' then
1789 l_subTypeId := null;
1790 else
1791 l_subTypeId := to_number(p_subTypeId);
1792 end if;
1793 if p_srcStatusId = '$$#@' then
1794 l_srcStatusId := null;
1795 else
1796 l_srcStatusId := to_number(p_srcStatusId);
1797 end if;
1798 if p_locatorId = '$$#@' then
1799 l_locatorId := null;
1800 else
1801 l_locatorId := to_number(p_locatorId);
1802 end if;
1803 if p_csiTxnTypeId = '$$#@' then
1804 l_csiTxnTypeId := null;
1805 else
1806 l_csiTxnTypeId := to_number(p_csiTxnTypeId);
1807 end if;
1808 if p_orgId = '$$#@' then
1809 l_orgId := null;
1810 else
1811 l_orgId := to_number(p_orgId);
1812 end if;
1813 if p_qty = '$$#@' then
1814 l_qty := null;
1818 if p_instanceId = '$$#@' then
1815 else
1816 l_qty := to_number(p_qty);
1817 end if;
1819 l_instanceId := null;
1820 else
1821 l_instanceId := to_number(p_instanceId );
1822 end if;
1823 if p_parentProductId = '$$#@' then
1824 l_parentProductId := null;
1825 else
1826 l_parentProductId := to_number(p_parentProductId );
1827 end if;
1828 if p_recoveredPartId = '$$#@' then
1829 l_recoveredPartId := null;
1830 else
1831 l_recoveredPartId := to_number(p_recoveredPartId);
1832 end if;
1833 if p_revisionFlag = '$$#@' then
1834 l_revisionFlag := null;
1835 else
1836 l_revisionFlag := p_revisionFlag;
1837 end if;
1838 if p_srcStatusName = '$$#@' then
1839 l_srcStatusName := null;
1840 else
1841 l_srcStatusName := p_srcStatusName;
1842 end if;
1843 if p_partStatusCd = '$$#@' then
1844 l_partStatusCd := null;
1845 else
1846 l_partStatusCd := p_partStatusCd;
1847 end if;
1848 if p_chgFlag = '$$#@' then
1849 l_chgFlag := null;
1850 else
1851 l_chgFlag := p_chgFlag ;
1852 end if;
1853 if p_ibFlag = '$$#@' then
1854 l_ibFlag := null;
1855 else
1856 l_ibFlag := p_ibFlag ;
1857 end if;
1858 if p_invFlag = '$$#@' then
1859 l_invFlag := null;
1860 else
1861 l_invFlag := p_invFlag ;
1862 end if;
1863 if p_reasonCd = '$$#@' then
1864 l_reasonCd := null;
1865 else
1866 l_reasonCd := p_reasonCd;
1867 end if;
1868 if p_subInv = '$$#@' then
1869 l_subInv := null;
1870 else
1871 l_subInv := p_subInv;
1872 end if;
1873
1874 if p_orderCategoryCode = '$$#@' then
1875 l_orderCategoryCode := null;
1876 else
1877 l_orderCategoryCode := p_orderCategoryCode;
1878 end if;
1879 if p_serviceDate = '$$#@' then
1880 l_serviceDate := null;
1881 else
1882 l_serviceDate := p_serviceDate;
1883 end if;
1884 if p_txnTypeName = '$$#@' then
1885 l_txnTypeName := null;
1886 else
1887 l_txnTypeName := p_txnTypeName;
1888 end if;
1889 if p_updateIBFlag = '$$#@' then
1890 l_updateIBFlag := null;
1891 else
1892 l_updateIBFlag := p_updateIBFlag;
1893 end if;
1894
1895 if p_retReasonCd = '$$#@' then
1896 l_retReasonCd := null;
1897 else
1898 l_retReasonCd := p_retReasonCd;
1899 end if;
1900 if p_serialNr = '$$#@' then
1901 l_serialNr := null;
1902 else
1903 l_serialNr := p_serialNr ;
1904 end if;
1905 if p_lotNr = '$$#@' then
1906 l_lotNr := null;
1907 else
1908 l_lotNr := p_lotNr ;
1909 end if;
1910 if p_revisionNr = '$$#@' then
1911 l_revisionNr := null;
1912 else
1913 l_revisionNr := p_revisionNr;
1914 end if;
1915 if p_UOM = '$$#@' then
1916 l_UOM := null;
1917 else
1918 l_UOM := p_UOM;
1919 end if;
1920 if p_srcChangeOwner = '$$#@' then
1921 l_srcChangeOwner := null;
1922 else
1923 l_srcChangeOwner := p_srcChangeOwner;
1924 end if;
1925 if p_srcChangeOwnerToCode = '$$#@' then
1926 l_srcChangeOwnerToCode := null;
1927 else
1928 l_srcChangeOwnerToCode := p_srcChangeOwnerToCode ;
1929 end if;
1930 if p_srcReferenceReqd = '$$#@' then
1931 l_srcReferenceReqd := null;
1932 else
1933 l_srcReferenceReqd := p_srcReferenceReqd ;
1934 end if;
1935 if p_srcReturnReqd = '$$#@' then
1936 l_srcReturnReqd := null;
1937 else
1938 l_srcReturnReqd := p_srcReturnReqd;
1939 end if;
1940 if p_parentReferenceReqd = '$$#@' then
1941 l_parentReferenceReqd := null;
1942 else
1943 l_parentReferenceReqd := p_parentReferenceReqd;
1944 end if;
1945
1946 if p_usage_type = '$$#@' then
1947 l_usage_type := null;
1948 else
1949 l_usage_type := p_usage_type;
1950 end if;
1951 if p_dest_organization_id = 0 then
1952 l_dest_organization_id := null;
1953 else
1954 l_dest_organization_id := p_dest_organization_id;
1955 end if;
1956 if p_dest_subinventory_name = '$$#@' then
1957 l_dest_subinventory_name := null;
1958 else
1959 l_dest_subinventory_name := p_dest_subinventory_name;
1960 end if;
1961 if p_carrier_code = '$$#@' then
1962 l_carrier_code := null;
1963 else
1964 l_carrier_code := p_carrier_code;
1965 end if;
1966 if p_shipping_method = '$$#@' then
1967 l_shipping_method := null;
1968 else
1969 l_shipping_method := p_shipping_method;
1970 end if;
1971 if p_shipment_number = '$$#@' then
1972 l_shipment_number := null;
1973 else
1974 l_shipment_number := p_shipment_number;
1975 end if;
1976 if p_waybill = '$$#@' then
1977 l_waybill := null;
1978 else
1979 l_waybill := p_waybill;
1980 end if;
1981 if p_attribute1 = '$$#@' then
1982 l_attribute1 := null;
1983 else
1984 l_attribute1 := p_attribute1;
1985 end if;
1986 if p_attribute2 = '$$#@' then
1987 l_attribute2 := null;
1988 else
1989 l_attribute2 := p_attribute2;
1990 end if;
1991 if p_attribute3 = '$$#@' then
1992 l_attribute3 := null;
1993 else
1994 l_attribute3 := p_attribute3;
1995 end if;
1996 if p_attribute4 = '$$#@' then
1997 l_attribute4 := null;
1998 else
1999 l_attribute4 := p_attribute4;
2000 end if;
2001 if p_attribute5 = '$$#@' then
2002 l_attribute5 := null;
2003 else
2004 l_attribute5 := p_attribute5;
2005 end if;
2006 if p_attribute6 = '$$#@' then
2007 l_attribute6 := null;
2008 else
2009 l_attribute6 := p_attribute6;
2010 end if;
2011 if p_attribute7 = '$$#@' then
2012 l_attribute7 := null;
2016 if p_attribute8 = '$$#@' then
2013 else
2014 l_attribute7 := p_attribute7;
2015 end if;
2017 l_attribute8 := null;
2018 else
2019 l_attribute8 := p_attribute8;
2020 end if;
2021 if p_attribute9 = '$$#@' then
2022 l_attribute9 := null;
2023 else
2024 l_attribute9 := p_attribute9;
2025 end if;
2026 if p_attribute10 = '$$#@' then
2027 l_attribute10 := null;
2028 else
2029 l_attribute10 := p_attribute10;
2030 end if;
2031 if p_attribute11 = '$$#@' then
2032 l_attribute11 := null;
2033 else
2034 l_attribute11 := p_attribute11;
2035 end if;
2036 if p_attribute12 = '$$#@' then
2037 l_attribute12 := null;
2038 else
2039 l_attribute12 := p_attribute12;
2040 end if;
2041 if p_attribute13 = '$$#@' then
2042 l_attribute13 := null;
2043 else
2044 l_attribute13 := p_attribute13;
2045 end if;
2046 if p_attribute14 = '$$#@' then
2047 l_attribute14 := null;
2048 else
2049 l_attribute14 := p_attribute14;
2050 end if;
2051 if p_attribute15 = '$$#@' then
2052 l_attribute15 := null;
2053 else
2054 l_attribute15 := p_attribute15;
2055 end if;
2056 if p_attribute_category = '$$#@' then
2057 l_attribute_category := null;
2058 else
2059 l_attribute_category := p_attribute_category;
2060 end if;
2061
2062 if p_expenditure_org_id <= 0 then
2063 l_expenditure_org_id := null;
2064 else
2065 l_expenditure_org_id := p_expenditure_org_id;
2066 end if;
2067 if p_project_id <= 0 then
2068 l_project_id := null;
2069 else
2070 l_project_id := p_project_id;
2071 end if;
2072 if p_project_task_id <= 0 then
2073 l_project_task_id := null;
2074 else
2075 l_project_task_id := p_project_task_id;
2076 end if;
2077
2078 --dbms_output.put_line('So far so good....');
2079 l_order_header_id := 0;
2080
2081
2082 open c_DEBRIEF_HEADER_ID(l_dbfNr);
2083 fetch c_DEBRIEF_HEADER_ID into l_header_id;
2084 close c_DEBRIEF_HEADER_ID;
2085
2086 open c_dbf_lines;
2087 fetch c_dbf_lines into l_dbf_line_id;
2088 close c_dbf_lines;
2089
2090 if (l_partStatusCd is not null or rtrim(l_partStatusCd) <> '') then
2091 open c_status_name(l_partStatusCd);
2092 fetch c_status_name into l_part_status_name;
2093 close c_status_name;
2094 end if;
2095 --This part was done as the debrief was looking for part status naem in stead of the id
2096
2097 if (l_part_status_name is not null or rtrim(l_part_status_name) <> '') then
2098 l_partStatusCd := l_part_status_name ;
2099 end if;
2100
2101
2102
2103 -------------------------------------------------------------------------------------------------
2104 -------
2105 ------- check for the Service date, which should not be less than SR date and more than Sysdate
2106 -------
2107 -------------------------------------------------------------------------------------------------
2108 -- first get the date format
2109 -- Bug 2862796. Using ICX: Date Format Mask in place of CSFW: Date Format.
2110 FND_PROFILE.GET('ICX_DATE_FORMAT_MASK', l_dt_format);
2111
2112 -- Now get the SR Date in date format
2113 open c_creation_date(l_incidentid);
2114 fetch c_creation_date into l_sr_date;
2115 close c_creation_date;
2116
2117 -- bug # 5351199
2118 -- Save service line with 23:59 time
2119 -- bug # 5519603
2120 if(to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(sysdate), l_dt_format) = l_serviceDate)
2121 then
2122 lp_servicedate := to_date(l_serviceDate || ' ' || to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(sysdate), 'HH24:MI'), l_dt_format || ' HH24:MI');
2123 else
2124 lp_servicedate := to_date(l_serviceDate || ' 23:59', l_dt_format || ' HH24:MI');
2125 end if;
2126 -- add the return date
2127 if p_return_date <> '$$#@' then
2128 P_DEBRIEF_LINE_Rec.RETURN_DATE := to_date(p_return_date, l_dt_format);
2129 end if;
2130
2131
2132 -- now check if it is more than sysdate
2133 -- BUG 2225745
2134 -- if l_sr_date is p_service_date, then make l_sr_date = l_sr_date - 1
2135
2136 IF trunc(l_sr_date) = trunc(lp_servicedate)
2137 THEN
2138 l_sr_date := l_sr_date - 1;
2139 END IF;
2140
2141 /* check in JSP only
2142 IF lp_servicedate not between l_sr_date and sysdate
2143 THEN
2144 FND_MESSAGE.Set_Name('CSF', 'CSFW_SERVICE_DATE');
2145 FND_MESSAGE.Set_Token('P_SR_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(l_sr_date), l_dt_format||' HH24:MI'));
2146 FND_MESSAGE.Set_Token('P_SYSTEM_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(sysdate), l_dt_format||' HH24:MI'));
2147
2148
2149 p_error := -21;
2150 p_error := FND_MESSAGE.Get;
2151 RETURN ;
2152 END IF;
2153
2154 */
2155 --dbms_output.put_line('Start to fill the record.....');
2156
2157 if(p_updateFlag = 1) then
2158 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := p_dbfLineId;
2159 else
2160 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := l_dbf_line_id;
2161 end if;
2162
2163
2164 -- lp_servicedate is in Client Time Zone. Lets Convert it to Service Time Zone
2165 IF (fnd_timezones.timezones_enabled = 'Y') THEN
2166 HZ_TIMEZONE_PUB.GET_TIME(1.0, 'F',l_client_tz_id ,l_server_tz_id , lp_servicedate, l_server_time, l_return_status, l_msg_count, l_msg_data);
2167 ELSE
2168 l_server_time := lp_servicedate;
2169 END IF;
2170
2171
2172
2173
2174 P_DEBRIEF_LINE_Rec.DEBRIEF_HEADER_ID := l_header_id ;
2175 P_DEBRIEF_LINE_Rec.SERVICE_DATE := l_server_time;
2176 P_DEBRIEF_LINE_Rec.BUSINESS_PROCESS_ID := l_businessProcessId;
2180 P_DEBRIEF_LINE_Rec.INSTANCE_ID := l_instanceId;
2177 P_DEBRIEF_LINE_Rec.TXN_BILLING_TYPE_ID := l_billingTypeId;
2178 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := l_itemId;
2179
2181 P_DEBRIEF_LINE_Rec.PARENT_PRODUCT_ID := l_parentProductId;
2182 P_DEBRIEF_LINE_Rec.REMOVED_PRODUCT_ID := l_recoveredPartId;
2183
2184 P_DEBRIEF_LINE_Rec.STATUS_OF_RECEIVED_PART := l_partStatusCd;
2185 P_DEBRIEF_LINE_Rec.ITEM_SERIAL_NUMBER := l_serialNr;
2186 P_DEBRIEF_LINE_Rec.ITEM_REVISION := l_revisionNr;
2187 P_DEBRIEF_LINE_Rec.ITEM_LOTNUMBER := l_lotNr;
2188 P_DEBRIEF_LINE_Rec.UOM_CODE := l_UOM;
2189 P_DEBRIEF_LINE_Rec.QUANTITY := l_qty;
2190 P_DEBRIEF_LINE_Rec.MATERIAL_REASON_CODE := l_reasonCd;
2191 P_DEBRIEF_LINE_Rec.CHANNEL_CODE := 'WIRELESS_USER';
2192 P_DEBRIEF_LINE_Rec.RETURN_REASON_CODE := l_retReasonCd;
2193
2194 IF l_usage_type = 'USED' THEN
2195 IF l_orderCategoryCode = 'ORDER' THEN
2196 P_DEBRIEF_LINE_Rec.ISSUING_INVENTORY_ORG_ID := l_orgId;
2197 P_DEBRIEF_LINE_Rec.ISSUING_SUB_INVENTORY_CODE := l_subInv;
2198 P_DEBRIEF_LINE_Rec.ISSUING_LOCATOR_ID := l_locatorId;
2199 ELSE
2200 P_DEBRIEF_LINE_Rec.ISSUING_INVENTORY_ORG_ID := NULL;-- This is a hack for BUG 2431433 should be removed when the bug is fixed at the core debrief level
2201 p_debrief_line_rec.issuing_sub_inventory_code := NULL;-- this is also a hack
2202 P_DEBRIEF_LINE_Rec.RECEIVING_INVENTORY_ORG_ID := l_orgId;
2203 P_DEBRIEF_LINE_Rec.RECEIVING_SUB_INVENTORY_CODE := l_subInv;
2204 P_DEBRIEF_LINE_Rec.RECEIVING_LOCATOR_ID := l_locatorId;
2205 END IF;
2206 ELSE
2207 P_DEBRIEF_LINE_Rec.ISSUING_INVENTORY_ORG_ID := l_orgId;
2208 P_DEBRIEF_LINE_Rec.ISSUING_SUB_INVENTORY_CODE := l_subInv;
2209 P_DEBRIEF_LINE_Rec.ISSUING_LOCATOR_ID := l_locatorId;
2210 END IF;
2211
2212 -- P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_NUMBER
2213 -- P_DEBRIEF_LINE_Rec.RMA_HEADER_ID
2214 -- P_DEBRIEF_LINE_Rec.DISPOSITION_CODE
2215 -- P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_STATUS_ID
2216 -- P_DEBRIEF_LINE_Rec.CHARGE_UPLOAD_STATUS
2217 -- P_DEBRIEF_LINE_Rec.CHARGE_UPLOAD_MSG_CODE
2218 -- P_DEBRIEF_LINE_Rec.CHARGE_UPLOAD_MESSAGE
2219 -- P_DEBRIEF_LINE_Rec.IB_UPDATE_STATUS
2220 -- P_DEBRIEF_LINE_Rec.IB_UPDATE_MSG_CODE
2221 -- P_DEBRIEF_LINE_Rec.IB_UPDATE_MESSAGE
2222 -- P_DEBRIEF_LINE_Rec.SPARE_UPDATE_STATUS
2223 -- P_DEBRIEF_LINE_Rec.SPARE_UPDATE_MSG_CODE
2224 -- P_DEBRIEF_LINE_Rec.SPARE_UPDATE_MESSAGE
2225 --Setting the Transaction Id
2226 P_DEBRIEF_LINE_REC.TRANSACTION_TYPE_ID := L_TXNTYPEID;
2227
2228 --dbms_output.put_line('Setting new values... l_usage_type: '||l_usage_type);
2229 P_DEBRIEF_LINE_REC.USAGE_TYPE := l_usage_type;
2230 P_DEBRIEF_LINE_REC.DEST_ORGANIZATION_ID := l_dest_organization_id;
2231 P_DEBRIEF_LINE_REC.DEST_SUBINVENTORY_NAME := l_dest_subinventory_name;
2232 P_DEBRIEF_LINE_REC.CARRIER_CODE := l_carrier_code;
2233 P_DEBRIEF_LINE_REC.SHIPPING_METHOD := l_shipping_method;
2234 P_DEBRIEF_LINE_REC.SHIPMENT_NUMBER := l_shipment_number;
2235 P_DEBRIEF_LINE_REC.WAYBILL := l_waybill;
2236 P_DEBRIEF_LINE_REC.ATTRIBUTE1 := l_attribute1;
2237 P_DEBRIEF_LINE_REC.ATTRIBUTE2 := l_attribute2;
2238 P_DEBRIEF_LINE_REC.ATTRIBUTE3 := l_attribute3;
2239 P_DEBRIEF_LINE_REC.ATTRIBUTE4 := l_attribute4;
2240 P_DEBRIEF_LINE_REC.ATTRIBUTE5 := l_attribute5;
2241 P_DEBRIEF_LINE_REC.ATTRIBUTE6 := l_attribute6;
2242 P_DEBRIEF_LINE_REC.ATTRIBUTE7 := l_attribute7;
2243 P_DEBRIEF_LINE_REC.ATTRIBUTE8 := l_attribute8;
2244 P_DEBRIEF_LINE_REC.ATTRIBUTE9 := l_attribute9;
2245 P_DEBRIEF_LINE_REC.ATTRIBUTE10 := l_attribute10;
2246 P_DEBRIEF_LINE_REC.ATTRIBUTE11 := l_attribute11;
2247 P_DEBRIEF_LINE_REC.ATTRIBUTE12 := l_attribute12;
2248 P_DEBRIEF_LINE_REC.ATTRIBUTE13 := l_attribute13;
2249 P_DEBRIEF_LINE_REC.ATTRIBUTE14 := l_attribute14;
2250 P_DEBRIEF_LINE_REC.ATTRIBUTE15 := l_attribute15;
2251 P_DEBRIEF_LINE_REC.ATTRIBUTE_CATEGORY := l_attribute_category;
2252
2253
2254 --dbms_output.put_line('putting into table....');
2255 --dbms_output.put_line('P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID....'||P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID);
2256
2257 P_DEBRIEF_LINE_Rec.EXPENDITURE_ORG_ID := l_expenditure_org_id;
2258 P_DEBRIEF_LINE_Rec.PROJECT_ID := l_project_id;
2259 P_DEBRIEF_LINE_Rec.PROJECT_TASK_ID := l_project_task_id;
2260
2261 P_DEBRIEF_TBL (1) := P_DEBRIEF_LINE_Rec;
2262 --dbms_output.put_line('table filled. Call API....');
2263
2264 --dbms_output.put_line('Calling DEBRIEF PUB apis...');
2265 if (p_updateFlag = 1) then
2266
2267 CSF_DEBRIEF_PUB.Update_debrief_line(
2268 P_Api_Version_Number => 1.0,
2269 P_Init_Msg_List => FND_API.G_FALSE,
2270 P_Commit => FND_API.G_TRUE,
2271 P_Upd_tskassgnstatus => NULL,
2272 P_Task_Assignment_status => NULL,
2273 P_DEBRIEF_LINE_Rec => P_DEBRIEF_LINE_Rec,
2274 X_Return_Status => l_return_status ,
2275 X_Msg_Count => l_msg_count ,
2276 X_Msg_Data => l_msg_data
2277 );
2278 else
2279 CSF_DEBRIEF_PUB.Create_debrief_lines(
2280 P_Api_Version_Number => 1.0,
2281 P_Init_Msg_List => FND_API.G_FALSE,
2282 P_Commit => FND_API.G_TRUE,
2283 P_Upd_tskassgnstatus => NULL,
2284 P_Task_Assignment_status => NULL,
2285 P_DEBRIEF_LINE_Tbl => P_DEBRIEF_TBL ,
2286 P_DEBRIEF_HEADER_ID => l_header_id,
2287 P_SOURCE_OBJECT_TYPE_CODE => 'CSFW' ,
2288 X_Return_Status => l_return_status ,
2289 X_Msg_Count => l_msg_count ,
2293 end if;
2290 X_Msg_Data => l_msg_data
2291 );
2292
2294
2295
2296 IF l_return_status = FND_API.G_RET_STS_SUCCESS
2297 THEN
2298 p_error_id := 0;
2299 p_error := 'S';
2300 p_ret_dbfLine_id := P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID; -- for DFF
2301 ELSE
2302 FOR l_counter IN 1 .. l_msg_count
2303 LOOP
2304 fnd_msg_pub.get
2305 ( p_msg_index => l_counter
2306 , p_encoded => FND_API.G_FALSE
2307 , p_data => l_data
2308 , p_msg_index_out => l_msg_index_out
2309 );
2310 --dbms_output.put_line('l_data '|| l_data);
2311 END LOOP;
2312 p_error_id := 1;
2313 p_error := l_data;
2314
2315
2316 END IF;
2317 if p_error = FND_API.G_RET_STS_SUCCESS
2318 then
2319 p_error := '';
2320 end if;
2321 EXCEPTION
2322 WHEN OTHERS
2323 THEN
2324 p_error_id := -1;
2325 p_error := SQLERRM;
2326
2327 END SAVE_DEBRIEF_MATERIAL_LINE ;
2328
2329
2330 /*
2331 PROCEDURE UPDATE_CHARGES(
2332 p_dbfLineId in number,
2333 p_incidentId in number,
2334 p_error out NOCOPY varchar2,
2335 p_error_id out NOCOPY number
2336 )IS
2337 l_return_status varchar2(10);
2338 l_object_version_number NUMBER;
2339 l_estimate_detail_id number;
2340 l_msg_count number;
2341 l_msg_data varchar2(2000);
2342 l_header_id number;
2343 l_busProcessId number;
2344 l_user number;
2345 l_data varchar2(2000);
2346 l_msg_index_out number;
2347 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
2348 l_organization_id number;
2349
2350 l_UOM_code varchar2(100);
2351 l_order_header_id number;
2352 l_order_type_id number;
2353 l_line_type_id number;
2354 l_Charges_Rec CS_Charge_Details_PUB.Charges_Rec_Type;
2355 l_line_number NUMBER;
2356 l_dt_format varchar2(20);
2357 l_sr_date DATE;
2358 l_interface_status varchar2(20);
2359 l_interface_status_meaning varchar2(20);
2360 l_orderCategoryCode varchar2(50);
2361
2362
2363 cursor c_line_type_id_order (p_order_type_id number,p_incident_id Number) is
2364 select default_outbound_line_type_id
2365 from oe_transaction_types_all
2366 where transaction_type_id = p_order_type_id
2367 and transaction_type_code = 'ORDER';
2368
2369 cursor c_line_type_id_return (p_order_type_id number,p_incident_id number) is
2370 select default_inbound_line_type_id
2371 from oe_transaction_types_all
2372 where transaction_type_id = p_order_type_id
2373 and transaction_type_code = 'ORDER';
2374
2375 Cursor c_status_meaning(p_code Varchar2) Is
2376 select meaning
2377 from fnd_lookups
2378 where lookup_type = 'CSF_INTERFACE_STATUS'
2379 and lookup_code = p_code;
2380
2381
2382 CURSOR c_dbfLineRec
2383 ( b_dbfLineId csf_debrief_lines.DEBRIEF_LINE_ID%TYPE
2384 )
2385 IS
2386 SELECT BUSINESS_PROCESS_ID
2387 , inventory_item_id
2388 , UOM_CODE
2389 , QUANTITY
2390 , TXN_BILLING_TYPE_ID
2391 , REMOVED_PRODUCT_ID
2392 , RETURN_REASON_CODE
2393 FROM CSF_DEBRIEF_MAT_LINES_V
2394 WHERE DEBRIEF_LINE_ID = b_dbfLineId;
2395
2396 r_dbfLineRec c_dbfLineRec%ROWTYPE;
2397
2398
2399 BEGIN
2400
2401 l_order_header_id := 0;
2402
2403
2404 OPEN c_dbfLineRec
2405 ( b_dbfLineId => p_dbfLineId
2406 );
2407 FETCH c_dbfLineRec
2408 INTO r_dbfLineRec;
2409 CLOSE c_dbfLineRec;
2410
2411
2412
2413
2414 select line_order_category_code into l_orderCategoryCode
2415 from cs_transaction_types_b
2416 where TRANSACTION_TYPE_ID = (select TRANSACTION_TYPE_ID from CSF_DEBRIEF_MAT_LINES_V
2417 where DEBRIEF_LINE_ID = p_dbfLineId);
2418
2419
2420 select order_type_id into l_order_type_id from cs_business_processes where business_process_id = r_dbfLineRec.BUSINESS_PROCESS_ID;
2421
2422 if (l_orderCategoryCode = 'ORDER') then
2423 open c_line_type_id_order (l_order_type_id,p_incidentId);
2424 fetch c_line_type_id_order into l_line_type_id;
2425 close c_line_type_id_order;
2426 else
2427 open c_line_type_id_return (l_order_type_id,p_incidentId);
2428 fetch c_line_type_id_return into l_line_type_id;
2429 close c_line_type_id_return;
2430 end if;
2431
2432
2433
2434
2435 l_Charges_Rec.original_source_id := p_incidentId;
2436 l_Charges_Rec.original_source_code := 'SR' ;
2437 l_Charges_Rec.incident_id := p_incidentId ;
2438 l_Charges_Rec.business_process_id := r_dbfLineRec.BUSINESS_PROCESS_ID;
2439 l_Charges_Rec.order_header_id := l_order_header_id ;
2440 l_Charges_Rec.line_category_code := l_orderCategoryCode;
2441 --l_Charges_Rec.line_type_id := l_line_type_id ;
2442 l_Charges_Rec.source_code := 'SD';
2443 l_Charges_Rec.source_id := p_dbfLineId;
2444
2445 l_Charges_Rec.inventory_item_id_in := r_dbfLineRec.inventory_item_id;
2446 l_charges_rec.unit_of_measure_code := r_dbfLineRec.UOM_CODE;
2447 l_charges_rec.quantity_required := r_dbfLineRec.QUANTITY;
2448 l_charges_rec.txn_billing_type_id := r_dbfLineRec.TXN_BILLING_TYPE_ID;
2449 l_charges_rec.customer_product_id := r_dbfLineRec.REMOVED_PRODUCT_ID;
2450 l_charges_rec.installed_cp_return_by_date := sysdate;
2451 l_charges_rec.after_warranty_cost := null;
2452 l_charges_rec.currency_code := null;
2456 p_api_version => 1.0,
2453 l_charges_rec.return_reason_code := r_dbfLineRec.RETURN_REASON_CODE;
2454
2455 CS_Charge_Details_PUB.Create_Charge_Details(
2457 x_return_status => l_return_status,
2458 x_msg_count => l_msg_count,
2459 x_object_version_number => l_object_version_number,
2460 x_msg_data => l_msg_data,
2461 x_estimate_detail_id => l_estimate_detail_id,
2462 x_line_number => l_line_number,
2463 p_Charges_Rec => l_Charges_Rec
2464 );
2465
2466 IF l_return_status = FND_API.G_RET_STS_SUCCESS
2467 THEN
2468 l_interface_status := 'SUCCEEDED';
2469 ELSE
2470 l_interface_status := 'FAILED';
2471 END IF;
2472
2473 open c_status_meaning(l_interface_status);
2474 fetch c_status_meaning INTO l_interface_status_meaning;
2475 close c_status_meaning;
2476
2477 CSF_DEBRIEF_LINES_PKG.Update_Row(
2478 p_DEBRIEF_LINE_ID => p_dbfLineId,
2479 p_DEBRIEF_HEADER_ID => FND_API.G_MISS_NUM,
2480 p_DEBRIEF_LINE_NUMBER => FND_API.G_MISS_NUM,
2481 p_SERVICE_DATE => FND_API.G_MISS_DATE,
2482 p_BUSINESS_PROCESS_ID => FND_API.G_MISS_NUM,
2483 p_TXN_BILLING_TYPE_ID => FND_API.G_MISS_NUM,
2484 p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
2485 P_INSTANCE_ID => FND_API.G_MISS_NUM,
2486 p_ISSUING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
2487 p_RECEIVING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
2488 p_ISSUING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
2489 p_RECEIVING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
2490 p_ISSUING_LOCATOR_ID => FND_API.G_MISS_NUM,
2491 p_RECEIVING_LOCATOR_ID => FND_API.G_MISS_NUM,
2492 p_PARENT_PRODUCT_ID => FND_API.G_MISS_NUM,
2493 p_REMOVED_PRODUCT_ID => FND_API.G_MISS_NUM,
2494 p_STATUS_OF_RECEIVED_PART => FND_API.G_MISS_CHAR,
2495 p_ITEM_SERIAL_NUMBER => FND_API.G_MISS_CHAR,
2496 p_ITEM_REVISION => FND_API.G_MISS_CHAR,
2497 p_ITEM_LOTNUMBER => FND_API.G_MISS_CHAR,
2498 p_UOM_CODE => FND_API.G_MISS_CHAR,
2499 p_QUANTITY => FND_API.G_MISS_NUM,
2500 p_RMA_HEADER_ID => FND_API.G_MISS_NUM,
2501 p_DISPOSITION_CODE => FND_API.G_MISS_CHAR,
2502 p_MATERIAL_REASON_CODE => FND_API.G_MISS_CHAR,
2503 p_LABOR_REASON_CODE => FND_API.G_MISS_CHAR,
2504 p_EXPENSE_REASON_CODE => FND_API.G_MISS_CHAR,
2505 p_LABOR_START_DATE => FND_API.G_MISS_DATE,
2506 p_LABOR_END_DATE => FND_API.G_MISS_DATE,
2507 p_STARTING_MILEAGE => FND_API.G_MISS_NUM,
2508 p_ENDING_MILEAGE => FND_API.G_MISS_NUM,
2509 p_EXPENSE_AMOUNT => FND_API.G_MISS_NUM,
2510 p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
2511 p_DEBRIEF_LINE_STATUS_ID => FND_API.G_MISS_NUM,
2512 P_RETURN_REASON_CODE => FND_API.G_MISS_CHAR,
2513 p_CHANNEL_CODE => 'WIRELESS_USER',
2514 p_CHARGE_UPLOAD_STATUS => l_interface_status,
2515 p_CHARGE_UPLOAD_MSG_CODE => FND_API.G_MISS_CHAR,
2516 p_CHARGE_UPLOAD_MESSAGE => l_interface_status_meaning,
2517 p_IB_UPDATE_STATUS => FND_API.G_MISS_CHAR,
2518 p_IB_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
2519 p_IB_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
2520 p_SPARE_UPDATE_STATUS => FND_API.G_MISS_CHAR,
2521 p_SPARE_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
2522 p_SPARE_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
2523 p_CREATED_BY => FND_API.G_MISS_NUM,
2524 p_CREATION_DATE => FND_API.G_MISS_DATE,
2525 p_LAST_UPDATED_BY => FND_API.G_MISS_NUM,
2526 p_LAST_UPDATE_DATE => FND_API.G_MISS_DATE,
2527 p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
2528 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
2529 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
2530 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
2531 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
2532 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
2533 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
2534 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
2535 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
2536 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
2537 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
2538 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
2539 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
2540 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
2541 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
2542 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
2543 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR);
2544
2545 commit work;
2546
2547 p_error := 'S';
2548 p_error_id := 0;
2549
2550
2551 if p_error = FND_API.G_RET_STS_SUCCESS
2552 then
2553 p_error := '';
2554 end if;
2555
2556 EXCEPTION
2557 WHEN OTHERS
2558 THEN
2559 p_error_id := -1;
2560 p_error := SQLERRM;
2561
2562
2563 END UPDATE_CHARGES;
2564
2565
2566 PROCEDURE UPDATE_IB
2567 (
2568 p_dbfLineId in number,
2569 p_incidentId in number,
2570 p_error_id out NOCOPY number,
2571 p_error out NOCOPY varchar2
2572
2573 ) IS
2574 l_in_out_flag varchar2(4);
2578 l_instance_id number ;
2575 l_transaction_type_id_csi number ;
2576 l_txn_sub_type_id number ;
2577
2579 l_parent_instance_id number ;
2580 l_new_instance_id number := null;
2581 l_new_instance_number Varchar2(40);
2582
2583 l_inventory_item_id number ;
2584 l_inv_organization_id number ;
2585 l_inv_subinventory_name varchar2(60);
2586 l_inv_master_organization_id number ;
2587 l_quantity number ;
2588 l_mfg_serial_number_flag varchar2(3) ;
2589 l_service_date date ;
2590 l_shipped_date date;
2591 l_currency_code varchar2(10);
2592
2593 l_party_id number ;
2594 l_party_account_id number ;
2595 l_customer_id number ;
2596 l_party_site_id number;
2597
2598 l_debrief_line_id number ;
2599 l_debrief_header_id number ;
2600 l_incident_id number ;
2601
2602
2603 l_return_status varchar2(1);
2604 l_mesg varchar2(2000);
2605 l_counter number;
2606 l_install_site_use_id number;
2607 l_ship_site_use_id number;
2608 l_parent_cpid number;
2609 l_serial_number varchar2(30) ;
2610 l_lot_number varchar2(30) ;
2611 l_UOM varchar2(30);
2612 l_msg_count number;
2613 l_msg_index_out number;
2614 l_interface_status varchar2(20);
2615 l_interface_status_meaning varchar2(20);
2616 l_data varchar2(1000);
2617 l_msg_data varchar2(1000);
2618 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
2619
2620
2621 Cursor c_internal_party_id Is
2622 select internal_party_id
2623 from csi_install_parameters;
2624
2625 Cursor c_status_meaning(p_code Varchar2) Is
2626 select meaning
2627 from fnd_lookups
2628 where lookup_type = 'CSF_INTERFACE_STATUS'
2629 and lookup_code = p_code;
2630
2631 Cursor c_site (p_incident_id number) Is
2632 select install_site_use_id,
2633 ship_to_site_use_id
2634 from cs_incidents_all
2635 where incident_id = p_incident_id;
2636 cursor c_party_site_id (p_install_site_id number) Is
2637 select party_site_id
2638 from hz_party_site_uses
2639 where party_site_use_id = p_install_site_id;
2640 Cursor c_instance_number(p_instance_id Number) is
2641 select instance_number
2642 from csi_item_instances
2643 where instance_id = p_instance_id;
2644
2645
2646
2647
2648 CURSOR c_dbfLineRec
2649 ( b_dbfLineId csf_debrief_lines.DEBRIEF_LINE_ID%TYPE
2650 )
2651 IS
2652 SELECT service_date
2653 , DEBRIEF_HEADER_ID
2654 , inventory_item_id
2655 , PARENT_PRODUCT_ID
2656 , ITEM_SERIAL_NUMBER
2657 , INVENTORY_ORG_ID
2658 , SUB_INVENTORY_CODE
2659 , INSTANCE_ID
2660 , QUANTITY
2661 , UOM_CODE
2662
2663 FROM CSF_DEBRIEF_MAT_LINES_V
2664 WHERE DEBRIEF_LINE_ID = b_dbfLineId;
2665
2666 r_dbfLineRec c_dbfLineRec%ROWTYPE;
2667
2668
2669
2670 cursor c_ib_rec (b_dbfLineId csf_debrief_lines.DEBRIEF_LINE_ID%TYPE) is
2671 select
2672 ctst.sub_type_id sub_type_id ,
2673 ctst.transaction_type_id transaction_type_id_csi ,
2674 cttv.line_order_category_code line_order_category_code
2675 from
2676 cs_transaction_types_vl cttv,
2677 cs_txn_billing_types ctbt,
2678 cs_bus_process_txns cbpt,
2679 cs_business_processes cbp,
2680 csi_txn_sub_types ctst,
2681 csi_txn_types ctt,
2682 csi_instance_statuses cis
2683 where
2684 cttv.transaction_type_id = ctbt.transaction_type_id and
2685 ctbt.transaction_type_id = cbpt.transaction_type_id
2686 and ctbt.transaction_type_id = (select transaction_type_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
2687 and trunc(sysdate) between nvl(ctbt.start_date_active,to_date(sysdate)) and nvl(ctbt.end_date_active,to_date(sysdate))
2688 and trunc(sysdate) between nvl(cbpt.start_date_active, to_date(sysdate)) and nvl(cbpt.end_date_active, to_date(sysdate))
2689 and cbpt.business_process_id = cbp.business_process_id
2690 and cbpt.business_process_id = (select business_process_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
2691 and cbp.field_service_flag = 'Y'
2692 and ctbt.billing_type = 'M'
2693 and ctst.cs_transaction_type_id = cttv.transaction_type_id
2694 and ctt.source_application_id=513
2695 and ctt.transaction_type_id = ctst.transaction_type_id
2696 and ctst.src_status_id = cis.instance_status_id(+)
2697 and (nvl(ctst.update_ib_flag, 'N') = 'N'
2698 or ( ctst.update_ib_flag = 'Y'
2699 and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
2700 and nvl(cis.terminated_flag, 'N') <> 'Y'
2701 and ctst.src_change_owner = 'Y'
2702 and nvl(ctst.src_return_reqd, 'N') = 'N'
2703 and (
2704 (ctst.src_change_owner_to_code = 'I'
2705 and nvl(ctst.parent_reference_reqd, 'N') = 'N'
2706 and cttv.line_order_category_code='RETURN')
2707 or
2708 (ctst.src_change_owner_to_code = 'E'
2709 and ctst.src_reference_reqd = 'Y'
2710 and cttv.line_order_category_code='ORDER')
2711 )
2712 )
2713 ) ;
2714
2718 select customer_id,customer_account_id from csf_debrief_tasks_v where debrief_header_id = b_dbfId;
2715 r_ib_rec c_ib_rec%ROWTYPE;
2716
2717 cursor c_party(b_dbfId number) is
2719 r_party c_party%ROWTYPE;
2720
2721
2722 BEGIN
2723
2724
2725 OPEN c_ib_rec
2726 ( b_dbfLineId => p_dbfLineId
2727 );
2728 FETCH c_ib_rec
2729 INTO r_ib_rec;
2730 CLOSE c_ib_rec;
2731
2732 OPEN c_dbfLineRec
2733 ( b_dbfLineId => p_dbfLineId
2734 );
2735 FETCH c_dbfLineRec
2736 INTO r_dbfLineRec;
2737 CLOSE c_dbfLineRec;
2738
2739
2740 l_currency_code :='USD';-- this method is never used now
2741
2742
2743
2744 l_service_Date := r_dbfLineRec.service_date;
2745 l_shipped_date := r_dbfLineRec.service_date;
2746 l_debrief_line_id := p_dbfLineId;
2747 l_debrief_header_id := r_dbfLineRec.DEBRIEF_HEADER_ID;
2748 l_incident_id := p_incidentId;
2749 l_parent_cpid := r_dbfLineRec.PARENT_PRODUCT_ID;
2750 l_serial_number := r_dbfLineRec.ITEM_SERIAL_NUMBER;
2751 l_transaction_type_id_csi := r_ib_rec.transaction_type_id_csi;
2752 l_txn_sub_type_id := r_ib_rec.sub_type_id;
2753
2754 l_instance_id := r_dbfLineRec.INSTANCE_ID;
2755 l_parent_instance_id := r_dbfLineRec.PARENT_PRODUCT_ID;
2756 l_inventory_item_id := r_dbfLineRec.inventory_item_id;
2757 l_inv_organization_id := r_dbfLineRec.INVENTORY_ORG_ID ;
2758 l_inv_subinventory_name := r_dbfLineRec.SUB_INVENTORY_CODE ;
2759 l_inv_master_organization_id := r_dbfLineRec.INVENTORY_ORG_ID ;
2760 l_quantity := r_dbfLineRec.QUANTITY;
2761 l_uom := r_dbfLineRec.UOM_CODE;
2762 l_mfg_serial_number_flag := 'N' ;
2763
2764 OPEN c_party
2765 ( b_dbfId => r_dbfLineRec.DEBRIEF_HEADER_ID
2766 );
2767 FETCH c_party
2768 INTO r_party;
2769 CLOSE c_party;
2770
2771 l_party_id := r_party.customer_id;
2772 l_party_account_id := r_party.customer_account_id;
2773
2774
2775 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_inv_organization_id);
2776
2777 if (r_ib_rec.line_order_category_code = 'RETURN' ) then
2778 l_in_out_flag:='IN';
2779 open c_internal_party_id;
2780 fetch c_internal_party_id into l_party_id;
2781 close c_internal_party_id;
2782 else
2783 l_in_out_flag:='OUT';
2784 open c_site(l_incident_id);
2785 fetch c_site into l_install_site_use_id, l_ship_site_use_id;
2786 close c_site;
2787 l_install_site_use_id := nvl(l_install_site_use_id, l_ship_site_use_id);
2788 open c_party_site_id (l_install_site_use_id);
2789 fetch c_party_site_id into l_party_site_id;
2790 close c_party_site_id;
2791 end if;
2792
2793 csf_ib.update_install_base(
2794 p_api_version => 1.0,
2795 p_init_msg_list => null,
2796 p_commit => null,
2797 p_validation_level => null,
2798 x_return_status => l_return_status,
2799 x_msg_count => l_msg_count,
2800 x_msg_data => l_msg_data,
2801 x_new_instance_id => l_new_instance_id,
2802 p_in_out_flag => l_in_out_flag,
2803 p_transaction_type_id => l_transaction_type_id_csi,
2804 p_txn_sub_type_id => l_txn_sub_type_id,
2805 p_instance_id => l_instance_id,
2806 p_inventory_item_id => l_inventory_item_id,
2807 p_inv_organization_id => l_inv_organization_id,
2808 p_inv_subinventory_name => l_inv_subinventory_name,
2809 p_quantity => l_quantity,
2810 p_inv_master_organization_id => l_inv_master_organization_id,
2811 p_mfg_serial_number_flag => l_mfg_serial_number_flag,
2812 p_serial_number => l_serial_number,
2813 p_lot_number => l_lot_number,
2814 p_unit_of_measure => l_uom,
2815 p_party_id => l_party_id,
2816 p_party_account_id => l_party_account_id,
2817 p_party_site_id => l_party_site_id,
2818 p_parent_instance_id => l_parent_instance_id) ;
2819
2820
2821 if l_RETURN_STATUS = 'S' then -- success
2822 l_interface_status := 'SUCCEEDED';
2823 else
2824 l_interface_status := 'FAILED';
2825 FOR l_counter IN 1 .. l_msg_count
2826 LOOP
2827 fnd_msg_pub.get
2828 ( p_msg_index => l_counter
2829 , p_encoded => FND_API.G_FALSE
2830 , p_data => l_data
2831 , p_msg_index_out => l_msg_index_out
2832 );
2833 --dbms_output.put_line( 'Message: '||l_data );
2834 END LOOP ;
2835
2836
2837
2838 end if;
2839
2840 open c_status_meaning(l_interface_status);
2841 fetch c_status_meaning INTO l_interface_status_meaning;
2842 close c_status_meaning;
2843
2844 CSF_DEBRIEF_LINES_PKG.Update_Row(
2845 p_DEBRIEF_LINE_ID => p_dbfLineId,
2846 p_DEBRIEF_HEADER_ID => FND_API.G_MISS_NUM,
2847 p_DEBRIEF_LINE_NUMBER => FND_API.G_MISS_NUM,
2848 p_SERVICE_DATE => FND_API.G_MISS_DATE,
2849 p_BUSINESS_PROCESS_ID => FND_API.G_MISS_NUM,
2850 p_TXN_BILLING_TYPE_ID => FND_API.G_MISS_NUM,
2851 p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
2852 P_INSTANCE_ID => FND_API.G_MISS_NUM,
2853 p_ISSUING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
2854 p_RECEIVING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
2855 p_ISSUING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
2856 p_RECEIVING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
2857 p_ISSUING_LOCATOR_ID => FND_API.G_MISS_NUM,
2858 p_RECEIVING_LOCATOR_ID => FND_API.G_MISS_NUM,
2859 p_PARENT_PRODUCT_ID => FND_API.G_MISS_NUM,
2863 p_ITEM_REVISION => FND_API.G_MISS_CHAR,
2860 p_REMOVED_PRODUCT_ID => FND_API.G_MISS_NUM,
2861 p_STATUS_OF_RECEIVED_PART => FND_API.G_MISS_CHAR,
2862 p_ITEM_SERIAL_NUMBER => FND_API.G_MISS_CHAR,
2864 p_ITEM_LOTNUMBER => FND_API.G_MISS_CHAR,
2865 p_UOM_CODE => FND_API.G_MISS_CHAR,
2866 p_QUANTITY => FND_API.G_MISS_NUM,
2867 p_RMA_HEADER_ID => FND_API.G_MISS_NUM,
2868 p_DISPOSITION_CODE => FND_API.G_MISS_CHAR,
2869 p_MATERIAL_REASON_CODE => FND_API.G_MISS_CHAR,
2870 p_LABOR_REASON_CODE => FND_API.G_MISS_CHAR,
2871 p_EXPENSE_REASON_CODE => FND_API.G_MISS_CHAR,
2872 p_LABOR_START_DATE => FND_API.G_MISS_DATE,
2873 p_LABOR_END_DATE => FND_API.G_MISS_DATE,
2874 p_STARTING_MILEAGE => FND_API.G_MISS_NUM,
2875 p_ENDING_MILEAGE => FND_API.G_MISS_NUM,
2876 p_EXPENSE_AMOUNT => FND_API.G_MISS_NUM,
2877 p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
2878 p_DEBRIEF_LINE_STATUS_ID => FND_API.G_MISS_NUM,
2879 P_RETURN_REASON_CODE => FND_API.G_MISS_CHAR,
2880 p_CHANNEL_CODE => 'WIRELESS_USER',
2881 p_CHARGE_UPLOAD_STATUS => FND_API.G_MISS_CHAR,
2882 p_CHARGE_UPLOAD_MSG_CODE => FND_API.G_MISS_CHAR,
2883 p_CHARGE_UPLOAD_MESSAGE => FND_API.G_MISS_CHAR,
2884 p_IB_UPDATE_STATUS => l_interface_status,
2885 p_IB_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
2886 p_IB_UPDATE_MESSAGE => l_interface_status_meaning,
2887 p_SPARE_UPDATE_STATUS => FND_API.G_MISS_CHAR,
2888 p_SPARE_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
2889 p_SPARE_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
2890 p_CREATED_BY => FND_API.G_MISS_NUM,
2891 p_CREATION_DATE => FND_API.G_MISS_DATE,
2892 p_LAST_UPDATED_BY => FND_API.G_MISS_NUM,
2893 p_LAST_UPDATE_DATE => FND_API.G_MISS_DATE,
2894 p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
2895 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
2896 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
2897 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
2898 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
2899 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
2900 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
2901 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
2902 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
2903 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
2904 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
2905 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
2906 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
2907 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
2908 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
2909 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
2910 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR);
2911
2912 commit work;
2913
2914 p_error := 'S';
2915 p_error_id := 0;
2916
2917
2918 if p_error = FND_API.G_RET_STS_SUCCESS
2919 then
2920 p_error := '';
2921 end if;
2922
2923 EXCEPTION
2924 WHEN OTHERS
2925 THEN
2926 p_error_id := -1;
2927 p_error := SQLERRM;
2928
2929
2930 END UPDATE_IB;
2931
2932 PROCEDURE UPDATE_SPARES(
2933 p_dbfLineId in number,
2934 p_dbfNr in varchar2,
2935 p_error_id out NOCOPY number,
2936 p_error out NOCOPY varchar2
2937 )IS
2938
2939 l_transaction_type_id number ;
2940 l_lot_number varchar2(30) ;
2941 l_revision varchar2(3) ;
2942 l_serial_number varchar2(30) ;
2943 l_transfer_to_subinventory varchar2(10):= NULL; --optional
2944 l_transfer_to_locator number := NULL; --optional
2945 l_transfer_to_organization number := NULL; --optional
2946 l_api_version number := 1.1;
2947 l_account_id number ;
2948 l_msg_Count number ;
2949 mesg Varchar2(2000);
2950 l_msg_data Varchar2(2000);
2951 l_locator_id number;
2952 lx_transaction_header_id number;
2953 lx_transaction_id number;
2954 l_interface_status varchar2(20);
2955 l_interface_status_meaning varchar2(20);
2956 l_return_status varchar2(10);
2957 l_data varchar2(2000);
2958 l_msg_index_out number;
2959
2960 Cursor c_status_meaning(p_code Varchar2) Is
2961 select meaning
2962 from fnd_lookups
2963 where lookup_type = 'CSF_INTERFACE_STATUS'
2964 and lookup_code = p_code;
2965
2966
2967 CURSOR c_dbfLineRec
2968 ( b_dbfLineId csf_debrief_lines.DEBRIEF_LINE_ID%TYPE
2969 )
2970 IS
2971 SELECT DEBRIEF_HEADER_ID
2972 , inventory_item_id
2973 , PARENT_PRODUCT_ID
2974 , ITEM_SERIAL_NUMBER
2975 , INVENTORY_ORG_ID
2976 , SUB_INVENTORY_CODE
2977 , INSTANCE_ID
2978 , QUANTITY
2979 , UOM_CODE
2980 , LOCATOR
2981 , ITEM_REVISION
2982 , ITEM_LOTNUMBER
2983 FROM CSF_DEBRIEF_MAT_LINES_V
2984 WHERE DEBRIEF_LINE_ID = b_dbfLineId;
2985
2986 r_dbfLineRec c_dbfLineRec%ROWTYPE;
2987
2988
2989
2990 cursor c_ib_rec (b_dbfLineId csf_debrief_lines.DEBRIEF_LINE_ID%TYPE) is
2991 select
2995 from
2992 ctst.sub_type_id sub_type_id ,
2993 ctst.transaction_type_id transaction_type_id_csi ,
2994 cttv.line_order_category_code line_order_category_code
2996 cs_transaction_types_vl cttv,
2997 cs_txn_billing_types ctbt,
2998 cs_bus_process_txns cbpt,
2999 cs_business_processes cbp,
3000 csi_txn_sub_types ctst,
3001 csi_txn_types ctt,
3002 csi_instance_statuses cis
3003 where
3004 cttv.transaction_type_id = ctbt.transaction_type_id and
3005 ctbt.transaction_type_id = cbpt.transaction_type_id
3006 and ctbt.transaction_type_id = (select transaction_type_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
3007 and trunc(sysdate) between nvl(ctbt.start_date_active,to_date(sysdate)) and nvl(ctbt.end_date_active,to_date(sysdate))
3008 and trunc(sysdate) between nvl(cbpt.start_date_active, to_date(sysdate)) and nvl(cbpt.end_date_active, to_date(sysdate))
3009 and cbpt.business_process_id = cbp.business_process_id
3010 and cbpt.business_process_id = (select business_process_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
3011 and cbp.field_service_flag = 'Y'
3012 and ctbt.billing_type = 'M'
3013 and ctst.cs_transaction_type_id = cttv.transaction_type_id
3014 and ctt.source_application_id=513
3015 and ctt.transaction_type_id = ctst.transaction_type_id
3016 and ctst.src_status_id = cis.instance_status_id(+)
3017 and (nvl(ctst.update_ib_flag, 'N') = 'N'
3018 or ( ctst.update_ib_flag = 'Y'
3019 and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
3020 and nvl(cis.terminated_flag, 'N') <> 'Y'
3021 and ctst.src_change_owner = 'Y'
3022 and nvl(ctst.src_return_reqd, 'N') = 'N'
3023 and (
3024 (ctst.src_change_owner_to_code = 'I'
3025 and nvl(ctst.parent_reference_reqd, 'N') = 'N'
3026 and cttv.line_order_category_code='RETURN')
3027 or
3028 (ctst.src_change_owner_to_code = 'E'
3029 and ctst.src_reference_reqd = 'Y'
3030 and cttv.line_order_category_code='ORDER')
3031 )
3032 )
3033 ) ;
3034
3035 r_ib_rec c_ib_rec%ROWTYPE;
3036
3037 BEGIN
3038
3039 OPEN c_ib_rec
3040 ( b_dbfLineId => p_dbfLineId
3041 );
3042 FETCH c_ib_rec
3043 INTO r_ib_rec;
3044 CLOSE c_ib_rec;
3045
3046 OPEN c_dbfLineRec
3047 ( b_dbfLineId => p_dbfLineId
3048 );
3049 FETCH c_dbfLineRec
3050 INTO r_dbfLineRec;
3051 CLOSE c_dbfLineRec;
3052
3053 p_error_id := 0;
3054 p_error := 'S';
3055
3056
3057
3058
3059 if (r_ib_rec.line_order_category_code = 'ORDER' ) then
3060 l_transaction_type_id := 93; --ISSUEING
3061 else
3062 l_transaction_type_id := 94; --RECEIVING
3063 end if;
3064
3065
3066 CSP_TRANSACTIONS_PUB.TRANSACT_MATERIAL(
3067 p_api_version => l_api_version,
3068 x_return_status => l_RETURN_STATUS,
3069 x_msg_count => l_MSG_COUNT,
3070 x_msg_data => l_MSG_DATA,
3071 p_init_msg_list => FND_API.G_TRUE,
3072 p_commit => FND_API.G_FALSE,
3073 p_inventory_item_id => r_dbfLineRec.inventory_item_id,
3074 p_organization_id => r_dbfLineRec.INVENTORY_ORG_ID,
3075 p_subinventory_code => r_dbfLineRec.SUB_INVENTORY_CODE,
3076 p_locator_id => r_dbfLineRec.LOCATOR,
3077 p_serial_number => r_dbfLineRec.ITEM_SERIAL_NUMBER,
3078 p_quantity => r_dbfLineRec.QUANTITY,
3079 p_uom => r_dbfLineRec.UOM_CODE,
3080 p_revision => r_dbfLineRec.ITEM_REVISION ,
3081 p_lot_number => r_dbfLineRec.ITEM_LOTNUMBER,
3082 p_transfer_to_subinventory => l_transfer_to_subinventory,
3083 p_transfer_to_locator => l_transfer_to_organization,
3084 p_transfer_to_organization => l_transfer_to_organization,
3085 p_source_id => NULL,
3086 p_source_line_id => NULL,
3087 p_transaction_type_id => l_transaction_type_id,
3088 p_account_id => l_account_id,
3089 px_transaction_header_id => lx_transaction_header_id,
3090 px_transaction_id => lx_transaction_id,
3091 p_transaction_source_id => r_dbfLineRec.DEBRIEF_HEADER_ID,
3092 p_trx_source_line_id => p_dbfLineId,
3093 p_transaction_source_name => p_dbfNr );
3094
3095 if l_RETURN_STATUS = 'S' then -- success
3096 l_interface_status := 'SUCCEEDED';
3097 else
3098 l_interface_status := 'FAILED';
3099 FOR l_counter IN 1 .. l_msg_count
3100 LOOP
3101 fnd_msg_pub.get
3102 ( p_msg_index => l_counter
3103 , p_encoded => FND_API.G_FALSE
3104 , p_data => l_data
3105 , p_msg_index_out => l_msg_index_out
3106 );
3107 --dbms_output.put_line( 'Message: '||l_data );
3108 END LOOP ;
3109
3110 end if;
3111
3112 open c_status_meaning(l_interface_status);
3113 fetch c_status_meaning INTO l_interface_status_meaning;
3114 close c_status_meaning;
3115
3116 CSF_DEBRIEF_LINES_PKG.Update_Row(
3117 p_DEBRIEF_LINE_ID => p_dbfLineId,
3118 p_DEBRIEF_HEADER_ID => FND_API.G_MISS_NUM,
3119 p_DEBRIEF_LINE_NUMBER => FND_API.G_MISS_NUM,
3120 p_SERVICE_DATE => FND_API.G_MISS_DATE,
3121 p_BUSINESS_PROCESS_ID => FND_API.G_MISS_NUM,
3122 p_TXN_BILLING_TYPE_ID => FND_API.G_MISS_NUM,
3126 p_RECEIVING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
3123 p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
3124 P_INSTANCE_ID => FND_API.G_MISS_NUM,
3125 p_ISSUING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
3127 p_ISSUING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
3128 p_RECEIVING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
3129 p_ISSUING_LOCATOR_ID => FND_API.G_MISS_NUM,
3130 p_RECEIVING_LOCATOR_ID => FND_API.G_MISS_NUM,
3131 p_PARENT_PRODUCT_ID => FND_API.G_MISS_NUM,
3132 p_REMOVED_PRODUCT_ID => FND_API.G_MISS_NUM,
3133 p_STATUS_OF_RECEIVED_PART => FND_API.G_MISS_CHAR,
3134 p_ITEM_SERIAL_NUMBER => FND_API.G_MISS_CHAR,
3135 p_ITEM_REVISION => FND_API.G_MISS_CHAR,
3136 p_ITEM_LOTNUMBER => FND_API.G_MISS_CHAR,
3137 p_UOM_CODE => FND_API.G_MISS_CHAR,
3138 p_QUANTITY => FND_API.G_MISS_NUM,
3139 p_RMA_HEADER_ID => FND_API.G_MISS_NUM,
3140 p_DISPOSITION_CODE => FND_API.G_MISS_CHAR,
3141 p_MATERIAL_REASON_CODE => FND_API.G_MISS_CHAR,
3142 p_LABOR_REASON_CODE => FND_API.G_MISS_CHAR,
3143 p_EXPENSE_REASON_CODE => FND_API.G_MISS_CHAR,
3144 p_LABOR_START_DATE => FND_API.G_MISS_DATE,
3145 p_LABOR_END_DATE => FND_API.G_MISS_DATE,
3146 p_STARTING_MILEAGE => FND_API.G_MISS_NUM,
3147 p_ENDING_MILEAGE => FND_API.G_MISS_NUM,
3148 p_EXPENSE_AMOUNT => FND_API.G_MISS_NUM,
3149 p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
3150 p_DEBRIEF_LINE_STATUS_ID => FND_API.G_MISS_NUM,
3151 P_RETURN_REASON_CODE => FND_API.G_MISS_CHAR,
3152 p_CHANNEL_CODE => 'WIRELESS_USER',
3153 p_CHARGE_UPLOAD_STATUS => FND_API.G_MISS_CHAR,
3154 p_CHARGE_UPLOAD_MSG_CODE => FND_API.G_MISS_CHAR,
3155 p_CHARGE_UPLOAD_MESSAGE => FND_API.G_MISS_CHAR,
3156 p_IB_UPDATE_STATUS => FND_API.G_MISS_CHAR,
3157 p_IB_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
3158 p_IB_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
3159 p_SPARE_UPDATE_STATUS => l_interface_status,
3160 p_SPARE_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
3161 p_SPARE_UPDATE_MESSAGE => l_interface_status_meaning,
3162 p_CREATED_BY => FND_API.G_MISS_NUM,
3163 p_CREATION_DATE => FND_API.G_MISS_DATE,
3164 p_LAST_UPDATED_BY => FND_API.G_MISS_NUM,
3165 p_LAST_UPDATE_DATE => FND_API.G_MISS_DATE,
3166 p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
3167 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
3168 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
3169 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
3170 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
3171 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
3172 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
3173 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
3174 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
3175 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
3176 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
3177 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
3178 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
3179 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
3180 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
3181 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
3182 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR);
3183
3184 commit work;
3185
3186
3187
3188 if p_error = FND_API.G_RET_STS_SUCCESS
3189 then
3190 p_error := '';
3191 end if;
3192
3193 EXCEPTION
3194 WHEN OTHERS
3195 THEN
3196 p_error_id := -1;
3197 p_error := SQLERRM;
3198
3199 END UPDATE_SPARES;
3200 */
3201
3202 FUNCTION validate_labor_time(
3203 p_resource_type_code in Varchar2,
3204 p_resource_id in Number,
3205 p_debrief_line_id in Number,
3206 p_labor_start_date in Date,
3207 p_labor_end_date in Date
3208 )
3209 return varchar IS
3210
3211 l_return_status varchar2(1);
3212 l_msg_count number;
3213 l_msg_data varchar2(255);
3214 l_debrief_number number;
3215 l_task_number varchar2(30);
3216 l_return_value VARCHAR2(255);
3217
3218
3219 BEGIN
3220
3221 l_return_value := 'S';
3222
3223 CSF_DEBRIEF_PVT.VALIDATE_LABOR_TIMES (
3224 P_Init_Msg_List => FND_API.G_FALSE,
3225 P_api_version_number => 1.0,
3226 p_resource_type_code => p_resource_type_code,
3227 p_resource_id => p_resource_id,
3228 p_debrief_line_id => p_debrief_line_id,
3229 p_labor_start_date => p_labor_start_date,
3230 p_labor_end_date => p_labor_end_date,
3231 x_return_status => l_return_status,
3232 x_msg_count => l_msg_count,
3233 x_msg_data => l_msg_data,
3234 x_debrief_number => l_debrief_number,
3235 x_task_number => l_task_number
3236 );
3237
3238 IF l_return_status <> 'S' THEN
3239 l_return_value := l_msg_data;
3240 ELSE
3241 l_return_value := 'S';
3242 END IF;
3243
3244 RETURN l_return_value;
3245
3246 END validate_labor_time;
3247
3248
3252 , p_debrief_header_id IN NUMBER
3249 /* Updates info for travel debrief */
3250 PROCEDURE Create_Travel_Debrief
3251 ( p_task_assignment_id IN NUMBER
3253 , p_start_date IN DATE
3254 , p_end_date IN DATE
3255 , p_distance IN NUMBER
3256 , p_error_id OUT NOCOPY NUMBER
3257 , p_error OUT NOCOPY VARCHAR2
3258 )
3259 IS
3260 P_DEBRIEF_Rec CSF_DEBRIEF_PUB.DEBRIEF_Rec_Type;
3261
3262 l_return_status varchar2(2000);
3263 l_msg_count number;
3264 l_msg_data varchar2(2000);
3265 l_user number;
3266 l_data varchar2(2000);
3267 l_msg_index_out number;
3268
3269 -- cursors
3270
3271 cursor c_dbf_rec (v_hrd_id number, v_asgn_id number) is
3272 select
3273 DEBRIEF_HEADER_ID, DEBRIEF_NUMBER,
3274 DEBRIEF_DATE, DEBRIEF_STATUS_ID,
3275 TASK_ASSIGNMENT_ID, CREATED_BY,
3276 CREATION_DATE, LAST_UPDATED_BY,
3277 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
3278 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
3279 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
3280 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
3281 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
3282 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
3283 ATTRIBUTE_CATEGORY, object_version_number,
3284 TRAVEL_START_TIME, TRAVEL_END_TIME,
3285 TRAVEL_DISTANCE_IN_KM
3286 from CSF_DEBRIEF_HEADERS
3287 where DEBRIEF_HEADER_ID = v_hrd_id
3288 and TASK_ASSIGNMENT_ID = v_asgn_id;
3289
3290 Begin
3291 p_error_id := 0; --Assume success
3292
3293 l_user := FND_GLOBAL.user_id;
3294
3295 /* getting present values of the debrief header */
3296 open c_dbf_rec(p_debrief_header_id, p_task_assignment_id);
3297 fetch c_dbf_rec into P_DEBRIEF_Rec;
3298 close c_dbf_rec;
3299
3300 /* changing the values to be updated */
3301 P_DEBRIEF_Rec.DEBRIEF_HEADER_ID := p_debrief_header_id;
3302 P_DEBRIEF_Rec.TRAVEL_START_TIME := p_start_date;
3303 P_DEBRIEF_Rec.TRAVEL_END_TIME := p_end_date;
3304 P_DEBRIEF_Rec.TRAVEL_DISTANCE_IN_KM := p_distance;
3305 P_DEBRIEF_Rec.TASK_ASSIGNMENT_ID := p_task_assignment_id;
3306
3307 CSF_DEBRIEF_PUB.Update_DEBRIEF(
3308 P_Api_Version_Number => 1.0,
3309 P_Init_Msg_List => FND_API.G_FALSE,
3310 P_Commit => FND_API.G_TRUE,
3311 P_DEBRIEF_Rec => P_DEBRIEF_Rec,
3312 X_Return_Status => l_return_status,
3313 X_Msg_Count => l_msg_count,
3314 X_Msg_Data => l_msg_data
3315 );
3316
3317
3318 IF l_return_status = FND_API.G_RET_STS_SUCCESS
3319 THEN
3320 /* API-call was successfull */
3321 p_error_id := 0;
3322 p_error := FND_API.G_RET_STS_SUCCESS;
3323 ELSE
3324 FOR l_counter IN 1 .. l_msg_count
3325 LOOP
3326 fnd_msg_pub.get
3327 ( p_msg_index => l_counter
3328 , p_encoded => FND_API.G_FALSE
3329 , p_data => l_data
3330 , p_msg_index_out => l_msg_index_out
3331 );
3332 END LOOP ;
3333 p_error_id := 1;
3334 p_error := l_data;
3335 END IF;
3336
3337 if p_error = FND_API.G_RET_STS_SUCCESS
3338 then
3339 p_error := '';
3340 end if;
3341
3342 EXCEPTION
3343 WHEN OTHERS
3344 THEN
3345 p_error_id := -1;
3346 p_error := SQLERRM;
3347
3348 END Create_Travel_Debrief;
3349
3350 -- To Update Debrief Header DFF values
3351 PROCEDURE Update_Debrief_Header
3352 ( p_DEBRIEF_ID IN NUMBER,
3353 p_DEBRIEF_NUMBER IN VARCHAR2,
3354 p_DEBRIEF_DATE IN DATE,
3355 p_DEBRIEF_STATUS_ID IN NUMBER,
3356 p_TASK_ASSIGNMENT_ID IN NUMBER,
3357 p_CREATED_BY IN NUMBER,
3358 p_CREATION_DATE IN DATE,
3359 p_LAST_UPDATED_BY IN NUMBER,
3360 p_LAST_UPDATE_DATE IN DATE,
3361 p_LAST_UPDATE_LOGIN IN NUMBER,
3362 p_ATTRIBUTE1 IN VARCHAR2,
3363 p_ATTRIBUTE2 IN VARCHAR2,
3364 p_ATTRIBUTE3 IN VARCHAR2,
3365 p_ATTRIBUTE4 IN VARCHAR2,
3366 p_ATTRIBUTE5 IN VARCHAR2,
3367 p_ATTRIBUTE6 IN VARCHAR2,
3368 p_ATTRIBUTE7 IN VARCHAR2,
3369 p_ATTRIBUTE8 IN VARCHAR2,
3370 p_ATTRIBUTE9 IN VARCHAR2,
3371 p_ATTRIBUTE10 IN VARCHAR2,
3372 p_ATTRIBUTE11 IN VARCHAR2,
3373 p_ATTRIBUTE12 IN VARCHAR2,
3374 p_ATTRIBUTE13 IN VARCHAR2,
3375 p_ATTRIBUTE14 IN VARCHAR2,
3376 p_ATTRIBUTE15 IN VARCHAR2,
3377 p_ATTRIBUTE_CATEGORY IN VARCHAR2,
3378 p_return_status OUT NOCOPY VARCHAR2,
3379 p_error_count OUT NOCOPY NUMBER,
3380 p_error OUT NOCOPY VARCHAR2
3381 )
3382 IS
3383 P_DEBRIEF_Rec CSF_DEBRIEF_PUB.DEBRIEF_Rec_Type;
3384 l_error_msg VARCHAR2(2000);
3385 l_msg_index_out NUMBER;
3386 BEGIN
3387
3388 -- set debrief_id
3389 P_DEBRIEF_Rec.DEBRIEF_HEADER_ID := p_DEBRIEF_ID;
3390
3391 -- set other parameters
3392 IF p_ATTRIBUTE1 IS NULL OR p_ATTRIBUTE1 <> '#%*%#'
3393 THEN
3394 P_DEBRIEF_Rec.ATTRIBUTE1 := p_ATTRIBUTE1;
3395 END IF;
3396
3397 IF p_ATTRIBUTE2 IS NULL OR p_ATTRIBUTE2 <> '#%*%#'
3398 THEN
3399 P_DEBRIEF_Rec.ATTRIBUTE2 := p_ATTRIBUTE2;
3400 END IF;
3401
3402 IF p_ATTRIBUTE3 IS NULL OR p_ATTRIBUTE3 <> '#%*%#'
3403 THEN
3404 P_DEBRIEF_Rec.ATTRIBUTE3 := p_ATTRIBUTE3;
3405 END IF;
3406
3407 IF p_ATTRIBUTE4 IS NULL OR p_ATTRIBUTE4 <> '#%*%#'
3408 THEN
3412 IF p_ATTRIBUTE5 IS NULL OR p_ATTRIBUTE5 <> '#%*%#'
3409 P_DEBRIEF_Rec.ATTRIBUTE4 := p_ATTRIBUTE4;
3410 END IF;
3411
3413 THEN
3414 P_DEBRIEF_Rec.ATTRIBUTE5 := p_ATTRIBUTE5;
3415 END IF;
3416
3417 IF p_ATTRIBUTE6 IS NULL OR p_ATTRIBUTE6 <> '#%*%#'
3418 THEN
3419 P_DEBRIEF_Rec.ATTRIBUTE6 := p_ATTRIBUTE6;
3420 END IF;
3421
3422 IF p_ATTRIBUTE7 IS NULL OR p_ATTRIBUTE7 <> '#%*%#'
3423 THEN
3424 P_DEBRIEF_Rec.ATTRIBUTE7 := p_ATTRIBUTE7;
3425 END IF;
3426
3427 IF p_ATTRIBUTE8 IS NULL OR p_ATTRIBUTE8 <> '#%*%#'
3428 THEN
3429 P_DEBRIEF_Rec.ATTRIBUTE8 := p_ATTRIBUTE8;
3430 END IF;
3431
3432 IF p_ATTRIBUTE9 IS NULL OR p_ATTRIBUTE9 <> '#%*%#'
3433 THEN
3434 P_DEBRIEF_Rec.ATTRIBUTE9 := p_ATTRIBUTE9;
3435 END IF;
3436
3437 IF p_ATTRIBUTE10 IS NULL OR p_ATTRIBUTE10 <> '#%*%#'
3438 THEN
3439 P_DEBRIEF_Rec.ATTRIBUTE10 := p_ATTRIBUTE10;
3440 END IF;
3441
3442 IF p_ATTRIBUTE11 IS NULL OR p_ATTRIBUTE11 <> '#%*%#'
3443 THEN
3444 P_DEBRIEF_Rec.ATTRIBUTE11 := p_ATTRIBUTE11;
3445 END IF;
3446
3447 IF p_ATTRIBUTE12 IS NULL OR p_ATTRIBUTE12 <> '#%*%#'
3448 THEN
3449 P_DEBRIEF_Rec.ATTRIBUTE12 := p_ATTRIBUTE12;
3450 END IF;
3451
3452 IF p_ATTRIBUTE13 IS NULL OR p_ATTRIBUTE13 <> '#%*%#'
3453 THEN
3454 P_DEBRIEF_Rec.ATTRIBUTE13 := p_ATTRIBUTE13;
3455 END IF;
3456
3457 IF p_ATTRIBUTE14 IS NULL OR p_ATTRIBUTE14 <> '#%*%#'
3458 THEN
3459 P_DEBRIEF_Rec.ATTRIBUTE14 := p_ATTRIBUTE14;
3460 END IF;
3461
3462 IF p_ATTRIBUTE15 IS NULL OR p_ATTRIBUTE15 <> '#%*%#'
3463 THEN
3464 P_DEBRIEF_Rec.ATTRIBUTE15 := p_ATTRIBUTE15;
3465 END IF;
3466
3467 IF p_ATTRIBUTE_CATEGORY IS NULL OR p_ATTRIBUTE_CATEGORY <> '#%*%#'
3468 THEN
3469 P_DEBRIEF_Rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
3470 END IF;
3471
3472 --DBMS_OUTPUT.PUT_LINE('Calling CSF_DEBRIEF_PUB.Update_debrief...');
3473 CSF_DEBRIEF_PUB.Update_debrief (
3474 P_Api_Version_Number => 1.0,
3475 P_Init_Msg_List => FND_API.G_FALSE,
3476 P_Commit => FND_API.G_FALSE,
3477 P_DEBRIEF_Rec => P_DEBRIEF_Rec,
3478 X_Return_Status => p_return_status,
3479 X_Msg_Count => p_error_count,
3480 X_Msg_Data => l_error_msg
3481 );
3482 --DBMS_OUTPUT.PUT_LINE('Done CSF_DEBRIEF_PUB.');
3483 --DBMS_OUTPUT.PUT_LINE('l_error_msg: '||l_error_msg);
3484 --DBMS_OUTPUT.PUT_LINE('p_return_status: '||p_return_status);
3485
3486 IF p_return_status = FND_API.G_RET_STS_SUCCESS
3487 THEN
3488 commit;
3489 ELSE
3490 FOR l_counter IN 1 .. p_error_count
3491 LOOP
3492 FND_MSG_PUB.Get (
3493 p_msg_index => l_counter,
3494 p_encoded => FND_API.G_FALSE,
3495 p_data => l_error_msg,
3496 p_msg_index_out => l_msg_index_out
3497 );
3498 END LOOP ;
3499 p_error := l_error_msg;
3500 END IF;
3501
3502
3503 EXCEPTION
3504 WHEN OTHERS
3505 THEN
3506 p_error := SQLERRM;
3507
3508 END Update_Debrief_Header;
3509
3510 END csfw_debrief_pub;
3511