[Home] [Help]
PACKAGE BODY: APPS.CSFW_DEBRIEF_PUB
Source
1 Package Body csfw_debrief_pub AS
2 /*$Header: csfwdbfb.pls 120.10.12000000.3 2007/07/05 09:09:56 htank ship $*/
3
4 PROCEDURE Create_Debrief_header
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(
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_debrief_line_id OUT NOCOPY NUMBER,
109 p_error_id OUT NOCOPY NUMBER,
110 p_error OUT NOCOPY VARCHAR2
111
112 )
113 IS
114
115 l_return_status varchar2(2000);
116 l_msg_count number;
117 l_msg_data varchar2(2000);
118 l_header_id number;
119 l_dbf_line_id number;
120 l_user number;
121 l_data varchar2(2000);
122 l_msg_index_out number;
123 P_DEBRIEF_TBL CSF_DEBRIEF_PUB.DEBRIEF_LINE_Tbl_Type ;
124 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
125 l_organization_id number;
126 l_UOM_code varchar2(100);
127 l_order_header_id number;
128 l_order_type_id number;
129 l_line_type_id number;
130 l_object_version_number NUMBER;
131 l_estimate_detail_id NUMBER;
132 l_line_number NUMBER;
133 l_dt_format varchar2(10);
134 l_sr_date DATE;
135 l_charge_Entry varchar2(1);
136
137
138 l_resource_type VARCHAR2(30);
139 l_resource_id NUMBER;
140 l_validate_lab_time_msg varchar2(255);
141
142
143 cursor find_resource is
144 select a.resource_id resource_id, b.resource_type resource_type
145 from CSP_RS_RESOURCES_V b, jtf_rs_resource_extns a
146 where a.resource_id =b.resource_id
147 and a.user_id = FND_GLOBAL.USER_ID;
148
149 r_find_resource find_resource%ROWTYPE;
150
151
152 cursor c_dbf_line_id is select CSF_DEBRIEF_LINES_S.nextval from dual;
153
154 cursor c_uom_code(v_org_id number, v_item_id number)
155 is
156 select primary_uom_code from mtl_system_items_vl
157 where organization_id = v_org_id
158 and inventory_item_id = v_item_id;
159
160 cursor c_creation_date (v_incident_id number)
161 is
162 select creation_date from cs_incidents_all_b where incident_id = v_incident_id;
163
164
165 BEGIN
166 l_order_header_id := 0;
167 p_error_id := 0; --Assume success
168 l_header_id := p_debrief_header_id;
169
170 l_user := FND_GLOBAL.user_id ;
171
172 open c_dbf_line_id;
173 fetch c_dbf_line_id into l_dbf_line_id;
174 close c_dbf_line_id;
175
176 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_organization_id);
177
178
179 -------------------------------------------------------------------------------------------------
180 -------
181 ------- check for the Service date, which should not be less than SR date and more than Sysdate
182 -------
183 -------------------------------------------------------------------------------------------------
184 -- first get the date format
185 FND_PROFILE.GET ( 'CSFWDATEFORMAT' , l_dt_format);
186 -- Now get the SR Date in date format
187 open c_creation_date(p_incident_id);
188 fetch c_creation_date into l_sr_date;
189 close c_creation_date;
190
191 -- now check if it is more than sysdate
192 -- BUG 2225745
193 -- if l_sr_date is p_service_date, then make l_sr_date = l_sr_date - 1
194 -- Now that we are dealing with dates all over we do not need this
195
196 -- IF trunc(l_sr_date) = trunc(p_service_date)
197 -- THEN
198 -- l_sr_date := l_sr_date - 1;
199 -- END IF; --BUG 2451683
200
201 /* bug# 2844824
202 -- remove the checks
203 --if start Date is not between the creation date and sysdate
204 IF p_labor_start_date between l_sr_date and sysdate
205 THEN
206 null;
207 ELSE
208 --convert the Server to Client time zone
209 FND_MESSAGE.Set_Name('CSF', 'CSFW_SERVICE_DATE');
210 FND_MESSAGE.Set_Token('P_SR_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(l_sr_date), l_dt_format||' HH24:MI'));
211 FND_MESSAGE.Set_Token('P_SYSTEM_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(sysdate), l_dt_format||' HH24:MI'));
212
213 p_error := -21;
214 p_error := FND_MESSAGE.Get;
215
216 RETURN ;
217 END IF;
218
219 -- SAme For End date
220 IF p_labor_end_date between l_sr_date and sysdate
221 THEN
222 null;
223 ELSE
224 --convert the Server to Client time zone
225 FND_MESSAGE.Set_Name('CSF', 'CSFW_SERVICE_DATE_END');
226 FND_MESSAGE.Set_Token('P_SR_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(l_sr_date), l_dt_format||' HH24:MI'));
227 FND_MESSAGE.Set_Token('P_SYSTEM_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(sysdate), l_dt_format||' HH24:MI'));
228
229 p_error := -21;
230 p_error := FND_MESSAGE.Get;
231
232 RETURN ;
233 END IF;
234 */
235
236 IF (p_labor_start_date >= p_labor_end_date) THEN
237 FND_MESSAGE.Set_Name('CSF', 'CSFW_START_END_DATE_SAME');
238 p_error := -30;
239 p_error := FND_MESSAGE.Get;
240 RETURN;
241 END IF;
242
243 -- CALL THE VALIDATE FUNCTION
244 OPEN find_resource;
245 fetch find_resource INTO r_find_resource;
246 close find_resource;
247 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);
248 IF l_validate_lab_time_msg <> 'S' THEN
249 p_error := -22;
250 p_error := l_validate_lab_time_msg;
251 RETURN;
252 END IF;
253
254
255 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := l_dbf_line_id;
256 P_DEBRIEF_LINE_Rec.DEBRIEF_HEADER_ID := l_header_id;--from header record
257 P_DEBRIEF_LINE_Rec.SERVICE_DATE := p_service_date;
258 P_DEBRIEF_LINE_Rec.TXN_BILLING_TYPE_ID := p_txn_billing_type_id;
259
260 IF p_inventory_item_id <> 0 THEN
261 open c_uom_code (l_organization_id, p_inventory_item_id);
262 fetch c_uom_code into l_UOM_code;
263 close c_uom_code;
264
265 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := p_inventory_item_id;
266 P_DEBRIEF_LINE_Rec.UOM_CODE := l_UOM_code;
267 END IF;
268
269 P_DEBRIEF_LINE_Rec.BUSINESS_PROCESS_ID := p_business_process_id;
270 P_DEBRIEF_LINE_Rec.LABOR_START_DATE := p_labor_start_date;
271 P_DEBRIEF_LINE_Rec.LABOR_END_DATE := p_labor_end_date;
272 P_DEBRIEF_LINE_Rec.channel_code := 'WIRELESS_USER';
273 P_DEBRIEF_LINE_Rec.issuing_inventory_org_id := l_organization_id;
274 P_DEBRIEF_LINE_Rec.TRANSACTION_TYPE_ID := p_txnTypeId;
275 P_DEBRIEF_LINE_Rec.LABOR_REASON_CODE := p_justificationCode;
276
277 -- Bug 4543409
278 P_DEBRIEF_LINE_Rec.UOM_CODE := p_uom;
279 IF p_quantity IS NOT NULL THEN
280 P_DEBRIEF_LINE_Rec.QUANTITY := p_quantity;
281 END IF;
282
283 P_DEBRIEF_TBL (1) := P_DEBRIEF_LINE_Rec;
284 CSF_DEBRIEF_PUB.Create_debrief_lines(
285 P_Api_Version_Number => 1.0,
286 P_Init_Msg_List => FND_API.G_FALSE,
287 P_Commit => FND_API.G_TRUE,
288 P_Upd_tskassgnstatus => NULL,
289 P_Task_Assignment_status => NULL,
290 P_DEBRIEF_LINE_Tbl => P_DEBRIEF_TBL ,
291 P_DEBRIEF_HEADER_ID => l_header_id,
292 P_SOURCE_OBJECT_TYPE_CODE => 'CSFW' ,
293 X_Return_Status => l_return_status ,
294 X_Msg_Count => l_msg_count ,
295 X_Msg_Data => l_msg_data
296 );
297
298 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
299 FOR l_counter IN 1 .. l_msg_count
300 LOOP
301 fnd_msg_pub.get
302 ( p_msg_index => l_counter
303 , p_encoded => FND_API.G_FALSE
304 , p_data => l_data
305 , p_msg_index_out => l_msg_index_out
306 );
307 END LOOP;
308 p_error_id := 1;
309 p_error := l_data;
310 ELSE
311 p_debrief_line_id := l_dbf_line_id; -- added to return dbf_line_id
312 END IF;
316
313 if p_error = FND_API.G_RET_STS_SUCCESS then
314 p_error := '';
315 end if;
317 EXCEPTION
318 WHEN OTHERS
319 THEN
320 p_error_id := -1;
321 p_error := SQLERRM;
322
323 END Create_Labor_Line;
324
325
326
327
328 PROCEDURE Create_Expense_Line
329 ( p_debrief_header_id IN NUMBER,
330 p_txn_billing_type_id IN NUMBER,
331 p_inventory_item_id IN NUMBER,
332 p_business_process_id IN NUMBER,
333 p_charge_Entry IN VARCHAR2,
334 p_incident_id IN NUMBER,
335 p_expense_amount IN NUMBER,
336 p_currency_code IN VARCHAR2,
337 p_txnTypeId IN NUMBER,
338 p_justificationCode IN VARCHAR2,
339 p_quantity IN NUMBER,
340 p_uom_code IN VARCHAR2,
341 p_debrief_line_id OUT NOCOPY NUMBER,
342 p_error_id OUT NOCOPY NUMBER,
343 p_error OUT NOCOPY VARCHAR2
344
345 ) IS
346
347 l_return_status varchar2(2000);
348 l_msg_count number;
349 l_msg_data varchar2(2000);
350 l_header_id number;
351 l_dbf_line_id number;
352 l_user number;
353 l_data varchar2(2000);
354 l_msg_index_out number;
355 P_DEBRIEF_TBL CSF_DEBRIEF_PUB.DEBRIEF_LINE_Tbl_Type ;
356 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
357 l_organization_id number;
358 l_UOM_code varchar2(100);
359 l_order_header_id number;
360 l_order_type_id number;
361 l_line_type_id number;
362 l_object_version_number NUMBER;
363 l_estimate_detail_id NUMBER;
364 l_line_number NUMBER;
365 l_charge_Entry VARCHAR2(1);
366
367 cursor c_dbf_lineId is select CSF_DEBRIEF_LINES_S.nextval from dual;
368 cursor c_uom_code (v_org_id number, v_item_id number)
369 is
370 select primary_uom_code from mtl_system_items_vl
371 where organization_id = v_org_id
372 and inventory_item_id = v_item_id;
373
374
375 BEGIN
376 l_order_header_id := 0;
377 p_error_id := 0; --Assume success
378 l_header_id := p_debrief_header_id;
379
380 open c_dbf_lineId;
381 fetch c_dbf_lineId into l_dbf_line_id;
382 close c_dbf_lineId;
383
384 l_user := FND_GLOBAL.user_id ;
385
386 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_organization_id);
387
388 open c_uom_code (l_organization_id, p_inventory_item_id);
389 fetch c_uom_code into l_UOM_code;
390 close c_uom_code;
391
392
393
394 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := l_dbf_line_id;
395 P_DEBRIEF_LINE_Rec.DEBRIEF_HEADER_ID := l_header_id;--from header record
396 P_DEBRIEF_LINE_Rec.SERVICE_DATE := sysdate;
397 P_DEBRIEF_LINE_Rec.TXN_BILLING_TYPE_ID := p_txn_billing_type_id;
398 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := p_inventory_item_id;
399 P_DEBRIEF_LINE_Rec.BUSINESS_PROCESS_ID := p_business_process_id;
400
401 IF p_expense_amount IS NOT NULL THEN
402 P_DEBRIEF_LINE_Rec.UOM_CODE := l_UOM_code;
403 P_DEBRIEF_LINE_Rec.EXPENSE_AMOUNT := p_expense_amount;
404 P_DEBRIEF_LINE_Rec.CURRENCY_CODE := p_currency_code;
405 ELSE
406 P_DEBRIEF_LINE_Rec.QUANTITY := p_quantity;
407 P_DEBRIEF_LINE_Rec.UOM_CODE := p_uom_code;
408 END IF;
409
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.EXPENSE_REASON_CODE := p_justificationCode;
414
415 P_DEBRIEF_TBL (1) := P_DEBRIEF_LINE_Rec;
416 CSF_DEBRIEF_PUB.Create_debrief_lines(
417 P_Api_Version_Number => 1.0,
418 P_Init_Msg_List => FND_API.G_FALSE,
419 P_Commit => FND_API.G_TRUE,
420 P_Upd_tskassgnstatus => NULL,
421 P_Task_Assignment_status => NULL,
422 P_DEBRIEF_LINE_Tbl => P_DEBRIEF_TBL ,
423 P_DEBRIEF_HEADER_ID => l_header_id,
424 P_SOURCE_OBJECT_TYPE_CODE => 'CSFW' ,
425 X_Return_Status => l_return_status ,
426 X_Msg_Count => l_msg_count ,
427 X_Msg_Data => l_msg_data
428 );
429
430 IF l_return_status = FND_API.G_RET_STS_SUCCESS
431 THEN
432 p_debrief_line_id := l_dbf_line_id; -- Added to return newly created Line_ID
433 null;
434 ELSE
435 FOR l_counter IN 1 .. l_msg_count
436 LOOP
437 fnd_msg_pub.get
438 ( p_msg_index => l_counter
439 , p_encoded => FND_API.G_FALSE
440 , p_data => l_data
441 , p_msg_index_out => l_msg_index_out
442 );
443 END LOOP;
444 p_error_id := 1;
445 p_error := l_data;
446
447
448 END IF;
449 if p_error = FND_API.G_RET_STS_SUCCESS
450 then
451 p_error := '';
452 end if;
453
454 EXCEPTION
455 WHEN OTHERS
456 THEN
457 p_error_id := -1;
458 p_error := SQLERRM;
459
460 END Create_Expense_Line;
461
462
463
464
465
466
467 PROCEDURE Update_debrief_Expense_line(
471 p_txn_billing_type_id IN NUMBER,
468 p_debrief_line_id IN NUMBER,
469 p_expense_amount IN NUMBER,
470 p_currency_code IN VARCHAR2,
472 p_inventory_item_id IN NUMBER,
473 p_business_process_id IN NUMBER,
474 p_charge_Entry IN VARCHAR2,
475 p_incident_id IN NUMBER,
476 p_txnTypeId IN NUMBER,
477 p_justificationCode IN VARCHAR2,
478 p_quantity IN NUMBER,
479 p_uom_code IN VARCHAR2,
480 p_error_id OUT NOCOPY NUMBER,
481 p_error OUT NOCOPY VARCHAR2
482 )IS
483
484 l_return_status varchar2(2000);
485 l_msg_count number;
486 l_msg_data varchar2(2000);
487 l_header_id number;
488 l_dbf_line_id number;
489 l_user number;
490 l_data varchar2(2000);
491 l_msg_index_out number;
492 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
493 l_organization_id number;
494 l_UOM_code varchar2(100);
495 l_order_header_id number;
496 l_order_type_id number;
497 l_line_type_id number;
498 l_object_version_number NUMBER;
499 l_estimate_detail_id NUMBER;
500 l_line_number NUMBER;
501 l_charge_Entry VARCHAR2(1);
502
503 cursor c_DEBRIEF_HEADER_ID(v_debrief_line_id number ) is
504 select DEBRIEF_HEADER_ID from CSF_DEBRIEF_LINES where DEBRIEF_LINE_ID =
505 v_debrief_line_id ;
506
507 cursor c_uom_code(v_org_id number, v_item_id number) is
508 select primary_uom_code from mtl_system_items_vl
509 where organization_id = v_org_id
510 and inventory_item_id = v_item_id;
511
512 BEGIN
513 p_error_id := 0; --Assume success
514
515 open c_DEBRIEF_HEADER_ID(p_debrief_line_id);
516 fetch c_DEBRIEF_HEADER_ID into l_header_id;
517 close c_DEBRIEF_HEADER_ID;
518
519 l_user := FND_GLOBAL.user_id ;
520
521 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_organization_id);
522
523 open c_uom_code (l_organization_id, p_inventory_item_id);
524 fetch c_uom_code into l_UOM_code;
525 close c_uom_code;
526
527 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := p_debrief_line_id;
528 P_DEBRIEF_LINE_Rec.DEBRIEF_HEADER_ID := l_header_id;--from header record
529 P_DEBRIEF_LINE_Rec.TXN_BILLING_TYPE_ID := p_txn_billing_type_id;
530 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := p_inventory_item_id;
531 P_DEBRIEF_LINE_Rec.BUSINESS_PROCESS_ID := p_business_process_id;
532
533 IF p_expense_amount IS NOT NULL THEN
534 P_DEBRIEF_LINE_Rec.UOM_CODE := l_UOM_code;
535 P_DEBRIEF_LINE_Rec.EXPENSE_AMOUNT := p_expense_amount;
536 P_DEBRIEF_LINE_Rec.CURRENCY_CODE := p_currency_code;
537 P_DEBRIEF_LINE_Rec.QUANTITY := null;
538 ELSE
539 P_DEBRIEF_LINE_Rec.QUANTITY := p_quantity;
540 P_DEBRIEF_LINE_Rec.UOM_CODE := p_uom_code;
541 P_DEBRIEF_LINE_Rec.EXPENSE_AMOUNT := null;
542 P_DEBRIEF_LINE_Rec.CURRENCY_CODE := null;
543 END IF;
544
545 P_DEBRIEF_LINE_Rec.channel_code := 'WIRELESS_USER';
546 P_DEBRIEF_LINE_Rec.issuing_inventory_org_id := l_organization_id;
547 P_DEBRIEF_LINE_Rec.TRANSACTION_TYPE_ID := p_txnTypeId;
548 P_DEBRIEF_LINE_Rec.EXPENSE_REASON_CODE := p_justificationCode;
549
550
551 CSF_DEBRIEF_PUB.Update_debrief_line(
552 P_Api_Version_Number => 1.0,
553 P_Init_Msg_List => FND_API.G_FALSE,
554 P_Commit => FND_API.G_FALSE,
555 P_Upd_tskassgnstatus => NULL,
556 P_Task_Assignment_status => NULL,
557 P_DEBRIEF_LINE_Rec => P_DEBRIEF_LINE_Rec,
558 X_Return_Status => l_return_status ,
559 X_Msg_Count => l_msg_count ,
560 X_Msg_Data => l_msg_data
561 );
562
563
564 IF l_return_status = FND_API.G_RET_STS_SUCCESS
565 THEN
566 NULL;
567 ELSE
568 FOR l_counter IN 1 .. l_msg_count
569 LOOP
570 fnd_msg_pub.get
571 ( p_msg_index => l_counter
572 , p_encoded => FND_API.G_FALSE
573 , p_data => l_data
574 , p_msg_index_out => l_msg_index_out
575 );
576 END LOOP;
577 p_error_id := 1;
578 p_error := l_data;
579
580
581 END IF;
582
583 if p_error = FND_API.G_RET_STS_SUCCESS
584 then
585 p_error := '';
586 end if;
587
588 EXCEPTION
589 WHEN OTHERS
590 THEN
591 p_error_id := -1;
592 p_error := SQLERRM;
593
594 END Update_debrief_Expense_line;
595
596
597 -- Bug Number : 4543409, added quantity and uom
598 PROCEDURE Update_debrief_Labor_line(
599 p_debrief_line_id IN NUMBER,
600 p_labor_start_date IN DATE,
601 p_labor_end_date IN DATE,
602 p_service_date IN DATE,
603 p_txn_billing_type_id IN NUMBER,
604 p_inventory_item_id IN NUMBER,
605 p_business_process_id IN NUMBER,
606 p_charge_Entry IN VARCHAR2,
610 p_uom IN VARCHAR2,
607 p_incident_id IN NUMBER,
608 p_txnTypeId IN NUMBER,
609 p_quantity IN NUMBER,
611 p_justificationCode IN VARCHAR2,
612 p_error_id OUT NOCOPY NUMBER,
613 p_error OUT NOCOPY VARCHAR2
614 )IS
615
616 l_resource_type VARCHAR2(30);
617 l_resource_id NUMBER;
618
619 l_return_status varchar2(2000);
620 l_msg_count number;
621 l_msg_data varchar2(2000);
622 l_header_id number;
623 l_dbf_line_id number;
624 l_user number;
625 l_data varchar2(2000);
626 l_msg_index_out number;
627 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
628 l_organization_id number;
629 l_UOM_code varchar2(100);
630 l_order_header_id number;
631 l_order_type_id number;
632 l_line_type_id number;
633
634 l_object_version_number NUMBER;
635 l_estimate_detail_id NUMBER;
636 l_line_number NUMBER;
637
638 l_dt_format varchar2(10);
639 l_sr_date DATE;
640 l_charge_Entry varchar2(1);
641
642 cursor c_DEBRIEF_HEADER_ID(v_debrief_line_id number ) is
643 select DEBRIEF_HEADER_ID from CSF_DEBRIEF_LINES where DEBRIEF_LINE_ID =
644 v_debrief_line_id;
645
646 cursor c_uom_code(v_org_id number, v_item_id number) is
647 select primary_uom_code from mtl_system_items_vl
648 where organization_id = v_org_id
649 and inventory_item_id = v_item_id;
650
651 cursor c_creation_date(v_incident_id number) is
652 select creation_date from cs_incidents_all_b where incident_id = v_incident_id;
653 l_validate_lab_time_msg varchar2(255);
654
655
656 cursor find_resource is
657 select a.resource_id resource_id, b.resource_type resource_type
658 from CSP_RS_RESOURCES_V b, jtf_rs_resource_extns a
659 where a.resource_id =b.resource_id
660 and a.user_id = FND_GLOBAL.USER_ID;
661
662 r_find_resource find_resource%ROWTYPE;
663
664 BEGIN
665 p_error_id := 0; --Assume success
666
667 open c_DEBRIEF_HEADER_ID(p_debrief_line_id);
668 fetch c_DEBRIEF_HEADER_ID into l_header_id;
669 close c_DEBRIEF_HEADER_ID;
670
671
672 --select FND_GLOBAL.user_id into l_user from dual;
673 l_user :=FND_GLOBAL.user_id ;
674
675 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_organization_id);
676
677
678
679 -------------------------------------------------------------------------------------------------
680 -------
681 ------- check for the Service date, which should not be less than SR date and more than Sysdate
682 -------
683 -------------------------------------------------------------------------------------------------
684 -- first get the date format
685 FND_PROFILE.GET ( 'CSFWDATEFORMAT' , l_dt_format);
686 -- Now get the SR Date in date format
687 open c_creation_date(p_incident_id);
688 fetch c_creation_date into l_sr_date;
689 close c_creation_date;
690
691
692 -- now check if it is more than sysdate
693 -- BUG 2225745
694 -- if l_sr_date is p_service_date, then make l_sr_date = l_sr_date - 1
695 --WE do not need this as these are all date ...
696 -- IF trunc(l_sr_date) = trunc(p_service_date)
697 -- THEN
698 -- l_sr_date := l_sr_date - 1;
699 -- END IF;
700
701 /*
702
703 IF p_service_date between l_sr_date and sysdate
704 THEN
705 null;
706 ELSE
707 FND_MESSAGE.Set_Name('CSF', 'CSFW_SERVICE_DATE');
708 FND_MESSAGE.Set_Token('P_SR_DATE', to_char(l_sr_date, l_dt_format));
709 FND_MESSAGE.Set_Token('P_SYSTEM_DATE', to_char(sysdate, l_dt_format));
710 p_error := -21;
711 p_error := FND_MESSAGE.Get;
712 return;
713
714 END IF;
715 */
716 IF (p_labor_start_date >= p_labor_end_date) THEN
717 FND_MESSAGE.Set_Name('CSF', 'CSFW_START_END_DATE_SAME');
718 p_error := -30;
719 p_error := FND_MESSAGE.Get;
720 RETURN;
721 END IF;
722
723 -- CALL THE VALIDATE FUNCTION
724 OPEN find_resource;
725 fetch find_resource INTO r_find_resource;
726 close find_resource;
727 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);
728 IF l_validate_lab_time_msg <> 'S' THEN
729 p_error := -22;
730 p_error := l_validate_lab_time_msg;
731 RETURN;
732 END IF;
733
734
735 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := p_debrief_line_id;
736 P_DEBRIEF_LINE_Rec.DEBRIEF_HEADER_ID := l_header_id;--from header record
737 P_DEBRIEF_LINE_Rec.SERVICE_DATE := p_service_date;
738 P_DEBRIEF_LINE_Rec.TXN_BILLING_TYPE_ID := p_txn_billing_type_id;
739 IF p_inventory_item_id <> 0 THEN
740 open c_uom_code(l_organization_id, p_inventory_item_id);
741 fetch c_uom_code into l_UOM_code;
742 close c_uom_code;
743
744 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := p_inventory_item_id;
745 P_DEBRIEF_LINE_Rec.UOM_CODE := l_UOM_code;
746 ELSE
747 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := NULL; -- Bug Number : 3491830
748 END IF;
749
753 P_DEBRIEF_LINE_Rec.channel_code := 'WIRELESS_USER';
750 P_DEBRIEF_LINE_Rec.BUSINESS_PROCESS_ID := p_business_process_id;
751 P_DEBRIEF_LINE_Rec.LABOR_START_DATE := p_labor_start_date;
752 P_DEBRIEF_LINE_Rec.LABOR_END_DATE := p_labor_end_date;
754 P_DEBRIEF_LINE_Rec.issuing_inventory_org_id := l_organization_id;
755 P_DEBRIEF_LINE_Rec.TRANSACTION_TYPE_ID := p_txnTypeId;
756
757 P_DEBRIEF_LINE_Rec.LABOR_REASON_CODE := p_justificationCode;
758
759 -- Bug 4543409
760 P_DEBRIEF_LINE_Rec.UOM_CODE := p_uom;
761 IF p_quantity IS NOT NULL THEN
762 P_DEBRIEF_LINE_Rec.QUANTITY := p_quantity;
763 END IF;
764
765 CSF_DEBRIEF_PUB.Update_debrief_line(
766 P_Api_Version_Number => 1.0,
767 P_Init_Msg_List => FND_API.G_FALSE,
768 P_Commit => FND_API.G_FALSE,
769 P_Upd_tskassgnstatus => NULL,
770 P_Task_Assignment_status => NULL,
771 P_DEBRIEF_LINE_Rec => P_DEBRIEF_LINE_Rec,
772 X_Return_Status => l_return_status ,
773 X_Msg_Count => l_msg_count ,
774 X_Msg_Data => l_msg_data
775 );
776
777
778 IF l_return_status = FND_API.G_RET_STS_SUCCESS
779 THEN
780 NULL;
781 ELSE
782 FOR l_counter IN 1 .. l_msg_count
783 LOOP
784 fnd_msg_pub.get
785 ( p_msg_index => l_counter
786 , p_encoded => FND_API.G_FALSE
787 , p_data => l_data
788 , p_msg_index_out => l_msg_index_out
789 );
790 END LOOP;
791 p_error_id := 1;
792 p_error := l_data;
793
794 END IF;
795
796 if p_error = FND_API.G_RET_STS_SUCCESS
797 then
798 p_error := '';
799 end if;
800
801 EXCEPTION
802 WHEN OTHERS
803 THEN
804 p_error_id := -1;
805 p_error := SQLERRM;
806
807 END Update_debrief_Labor_line;
808
809 PROCEDURE SAVE_DEBRIEF_MATERIAL_LINE (
810 p_taskid IN VARCHAR2,
811 p_taskassignmentid IN VARCHAR2,
812 p_incidentid IN VARCHAR2,
813 p_partyid IN VARCHAR2,
814 p_dbfNr IN VARCHAR2,
815 p_billingTypeId IN VARCHAR2,
816 p_txnTypeId IN VARCHAR2,
817 p_orderCategoryCode IN VARCHAR2,
818 p_txnTypeName IN VARCHAR2,
819 p_itemId IN VARCHAR2,
820 p_revisionFlag IN VARCHAR2,
821 p_businessProcessId IN VARCHAR2,
822 p_subTypeId IN VARCHAR2,
823 p_updateIBFlag IN VARCHAR2,
824 p_srcChangeOwner IN VARCHAR2,
825 p_srcChangeOwnerToCode IN VARCHAR2,
826 p_srcReferenceReqd IN VARCHAR2,
827 p_srcReturnReqd IN VARCHAR2,
828 p_parentReferenceReqd IN VARCHAR2,
829 p_srcStatusId IN VARCHAR2,
830 p_srcStatusName IN VARCHAR2,
831 p_csiTxnTypeId IN VARCHAR2,
832 p_subInv IN VARCHAR2,
833 p_orgId IN VARCHAR2,
834 p_serviceDate IN VARCHAR2,
835 p_qty IN VARCHAR2,
836 p_chgFlag IN VARCHAR2,
837 p_ibFlag IN VARCHAR2,
838 p_invFlag IN VARCHAR2,
839 p_reasonCd IN VARCHAR2,
840 p_instanceId IN VARCHAR2,
841 p_parentProductId IN VARCHAR2,
842 p_partStatusCd IN VARCHAR2,
843 p_recoveredPartId IN VARCHAR2,
844 p_retReasonCd IN VARCHAR2,
845 p_serialNr IN VARCHAR2,
846 p_lotNr IN VARCHAR2,
847 p_revisionNr IN VARCHAR2,
848 p_locatorId IN VARCHAR2,
849 p_UOM IN VARCHAR2,
850 p_updateFlag IN NUMBER,
851 p_dbfLineId IN NUMBER,
852 p_ret_dbfLine_id OUT NOCOPY NUMBER,
853 p_error_id OUT NOCOPY NUMBER,
854 p_error OUT NOCOPY VARCHAR2,
855 p_return_date IN VARCHAR2
856
857 )IS
858
859 --l_return_status varchar2(2000);
860 l_msg_count number;
861 l_msg_data varchar2(2000);
862 l_header_id number;
863 l_dbf_line_id number;
864 l_user number;
865 l_data varchar2(2000);
866 l_msg_index_out number;
867 P_DEBRIEF_TBL CSF_DEBRIEF_PUB.DEBRIEF_LINE_Tbl_Type ;
868 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
869 l_organization_id number;
870 l_UOM_code varchar2(100);
871 l_order_header_id number;
872 l_order_type_id number;
873 l_line_type_id number;
874 l_Charges_Rec CS_Charge_Details_PUB.Charges_Rec_Type;
875 l_object_version_number NUMBER;
876 l_estimate_detail_id NUMBER;
877 l_line_number NUMBER;
878 l_dt_format varchar2(10);
879 l_sr_date DATE;
880 l_interface_status varchar2(20);
881 l_interface_status_meaning varchar2(20);
882
883 /* FOR INVENTORY */
884 l_transaction_type_id number ;
885 l_lot_number varchar2(30) ;
886 l_revision varchar2(3) ;
887 l_serial_number varchar2(30) ;
888 l_transfer_to_subinventory varchar2(10):= NULL; --optional
889 l_transfer_to_locator number := NULL; --optional
890 l_transfer_to_organization number := NULL; --optional
891 l_api_version number := 1.1;
892 l_account_id number ;
893 mesg Varchar2(2000);
894 l_locator_id number;
895 lx_transaction_header_id number;
899 l_in_out_flag varchar2(4);
896 lx_transaction_id number;
897
898 /* for IB */
900 l_transaction_type_id_csi number ;
901 l_txn_sub_type_id number ;
902
903 l_instance_id number ;
904 l_parent_instance_id number ;
905 l_new_instance_id number := null;
906 l_new_instance_number Varchar2(40);
907
908 l_inventory_item_id number ;
909 l_inv_organization_id number ;
910 l_inv_subinventory_name varchar2(60);
911 l_inv_master_organization_id number ;
912 l_quantity number ;
913 l_mfg_serial_number_flag varchar2(3) ;
914 l_service_date date ;
915 l_shipped_date date;
916 l_currency_code varchar2(10);
917
918 l_party_id number ;
919 l_party_account_id number ;
920 l_customer_id number ;
921 l_party_site_id number;
922
923 l_debrief_line_id number ;
924 l_debrief_header_id number ;
925 l_incident_id number ;
926
927
928 l_return_status varchar2(1);
929 l_mesg varchar2(2000);
930 l_counter number;
931 l_install_site_use_id number;
932 l_ship_site_use_id number;
933 l_parent_cpid number;
934
935 /* declaration for all the fields to check for null */
936 l_taskid Number;
937 l_taskassignmentid Number;
938 l_incidentid Number;
939 l_partyid Number;
940 l_dbfNr varchar2(30);
941 l_billingTypeId Number;
942 l_txnTypeId Number;
943 l_orderCategoryCode varchar2(30);
944 l_txnTypeName varchar2(30);
945 l_itemId Number;
946 l_revisionFlag varchar2(30);
947 l_businessProcessId Number;
948 l_subTypeId Number;
949 l_updateIBFlag varchar2(30);
950 l_srcChangeOwner varchar2(30);
951 l_srcChangeOwnerToCode varchar2(30);
952 l_srcReferenceReqd varchar2(30);
953 l_srcReturnReqd varchar2(30);
954 l_parentReferenceReqd varchar2(30);
955 l_srcStatusId Number;
956 l_srcStatusName varchar2(30);
957 l_csiTxnTypeId Number;
958 l_subInv varchar2(30);
959 l_orgId Number;
960 l_serviceDate varchar2(30);
961 l_qty Number;
962 l_chgFlag varchar2(30);
963 l_ibFlag varchar2(30);
964 l_invFlag varchar2(30);
965 l_reasonCd varchar2(30);
966 l_instanceId Number;
967 l_parentProductId Number;
968 l_partStatusCd varchar2(30);
969 l_recoveredPartId Number;
970 l_retReasonCd varchar2(30);
971 l_serialNr varchar2(30);
972 l_lotNr varchar2(30);
973 l_revisionNr varchar2(30);
974 l_locatorId Number;
975 l_UOM varchar2(30);
976 lp_servicedate DATE;
977
978 l_client_tz_id number;
979 l_server_tz_id number;
980 l_server_time date;
981
982 l_part_status_name varchar2(30);
983
984
985 Cursor c_site (p_incident_id number) Is
986 select install_site_use_id,
987 ship_to_site_use_id
988 from cs_incidents_all
989 where incident_id = p_incident_id;
990 cursor c_party_site_id (p_install_site_id number) Is
991 select party_site_id
992 from hz_party_site_uses
993 where party_site_use_id = p_install_site_id;
994 Cursor c_instance_number(p_instance_id Number) is
995 select instance_number
996 from csi_item_instances
997 where instance_id = p_instance_id;
998
999 Cursor c_internal_party_id Is
1000 select internal_party_id
1001 from csi_install_parameters;
1002
1003
1004 cursor c_line_type_id_order (p_order_type_id number,p_incident_id Number) is
1005 select default_outbound_line_type_id
1006 from oe_transaction_types_all
1007 where transaction_type_id = p_order_type_id
1008 and transaction_type_code = 'ORDER';
1009
1010 cursor c_line_type_id_return (p_order_type_id number,p_incident_id number) is
1011 select default_inbound_line_type_id
1012 from oe_transaction_types_all
1013 where transaction_type_id = p_order_type_id
1014 and transaction_type_code = 'ORDER';
1015
1016
1017
1018 Cursor c_status_meaning(p_code Varchar2) Is
1019 select meaning
1020 from fnd_lookups
1021 where lookup_type = 'CSF_INTERFACE_STATUS'
1022 and lookup_code = p_code;
1023
1024
1025 /* commenting this cursor since it's used no where
1026 cursor c_party(b_dbfId number) is
1027 select customer_id,customer_account_id from csf_debrief_tasks_v where debrief_header_id = b_dbfId;
1028 r_party c_party%ROWTYPE;
1029 */
1030
1031 cursor c_DEBRIEF_HEADER_ID(v_dbf_nr varchar2) is
1032 select DEBRIEF_HEADER_ID from csf_debrief_headers where debrief_number = v_dbf_nr;
1033
1034
1035 cursor c_dbf_lines is select CSF_DEBRIEF_LINES_S.nextval from dual;
1036
1037 cursor c_status_name(v_partStatusCd varchar2) is
1038 select name
1039 from csi_instance_statuses
1040 where INSTANCE_STATUS_ID = v_partStatusCd;
1041
1042 cursor c_creation_date(v_incident_id number) is
1043 select creation_date from cs_incidents_all_b where incident_id = v_incident_id;
1047 BEGIN
1044
1045
1046
1048
1049 --dbms_output.put_line('BEGINING....');
1050 l_client_tz_id := to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID'));
1051 l_server_tz_id := to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
1052
1053 -- first lets get the nulls out
1054 if p_taskid = '$$#@' then
1055 l_taskid := null;
1056 else
1057 l_taskid := to_number(p_taskid);
1058 end if;
1059 if p_itemId= '$$#@' then
1060 l_itemId := null;
1061 else
1062 l_itemId := to_number(p_itemId);
1063 end if;
1064
1065 if p_taskassignmentid = '$$#@' then
1066 l_taskassignmentid := null;
1067 else
1068 l_taskassignmentid := to_number(p_taskassignmentid);
1069 end if;
1070
1071 if p_incidentid = '$$#@' then
1072 l_incidentid := null;
1073 else
1074 l_incidentid := to_number(p_incidentid);
1075 end if;
1076
1077 if p_partyid = '$$#@' then
1078 l_partyid := null;
1079 else
1080 l_partyid := to_number(p_partyid);
1081 end if;
1082
1083 if p_dbfNr = '$$#@' then
1084 l_dbfNr := null;
1085 else
1086 l_dbfNr := p_dbfNr;
1087 end if;
1088
1089 if p_billingTypeId = '$$#@' then
1090 l_billingTypeId := null;
1091 else
1092 l_billingTypeId := to_number(p_billingTypeId);
1093 end if;
1094 if p_txnTypeId = '$$#@' then
1095 l_txnTypeId := null;
1096 else
1097 l_txnTypeId := to_number(p_txnTypeId);
1098 end if;
1099 if p_businessProcessId = '$$#@' then
1100 l_businessProcessId := null;
1101 else
1102 l_businessProcessId := to_number(p_businessProcessId);
1103 end if;
1104 if p_subTypeId = '$$#@' then
1105 l_subTypeId := null;
1106 else
1107 l_subTypeId := to_number(p_subTypeId);
1108 end if;
1109 if p_srcStatusId = '$$#@' then
1110 l_srcStatusId := null;
1111 else
1112 l_srcStatusId := to_number(p_srcStatusId);
1113 end if;
1114 if p_locatorId = '$$#@' then
1115 l_locatorId := null;
1116 else
1117 l_locatorId := to_number(p_locatorId);
1118 end if;
1119 if p_csiTxnTypeId = '$$#@' then
1120 l_csiTxnTypeId := null;
1121 else
1122 l_csiTxnTypeId := to_number(p_csiTxnTypeId);
1123 end if;
1124 if p_orgId = '$$#@' then
1125 l_orgId := null;
1126 else
1127 l_orgId := to_number(p_orgId);
1128 end if;
1129 if p_qty = '$$#@' then
1130 l_qty := null;
1131 else
1132 l_qty := to_number(p_qty);
1133 end if;
1134 if p_instanceId = '$$#@' then
1135 l_instanceId := null;
1136 else
1137 l_instanceId := to_number(p_instanceId );
1138 end if;
1139 if p_parentProductId = '$$#@' then
1140 l_parentProductId := null;
1141 else
1142 l_parentProductId := to_number(p_parentProductId );
1143 end if;
1144 if p_recoveredPartId = '$$#@' then
1145 l_recoveredPartId := null;
1146 else
1147 l_recoveredPartId := to_number(p_recoveredPartId);
1148 end if;
1149 if p_revisionFlag = '$$#@' then
1150 l_revisionFlag := null;
1151 else
1152 l_revisionFlag := p_revisionFlag;
1153 end if;
1154 if p_srcStatusName = '$$#@' then
1155 l_srcStatusName := null;
1156 else
1157 l_srcStatusName := p_srcStatusName;
1158 end if;
1159 if p_partStatusCd = '$$#@' then
1160 l_partStatusCd := null;
1161 else
1162 l_partStatusCd := p_partStatusCd;
1163 end if;
1164 if p_chgFlag = '$$#@' then
1165 l_chgFlag := null;
1166 else
1167 l_chgFlag := p_chgFlag ;
1168 end if;
1169 if p_ibFlag = '$$#@' then
1170 l_ibFlag := null;
1171 else
1172 l_ibFlag := p_ibFlag ;
1173 end if;
1174 if p_invFlag = '$$#@' then
1175 l_invFlag := null;
1176 else
1177 l_invFlag := p_invFlag ;
1178 end if;
1179 if p_reasonCd = '$$#@' then
1180 l_reasonCd := null;
1181 else
1182 l_reasonCd := p_reasonCd;
1183 end if;
1184 if p_subInv = '$$#@' then
1185 l_subInv := null;
1186 else
1187 l_subInv := p_subInv;
1188 end if;
1189
1190 if p_orderCategoryCode = '$$#@' then
1191 l_orderCategoryCode := null;
1192 else
1193 l_orderCategoryCode := p_orderCategoryCode;
1194 end if;
1195 if p_serviceDate = '$$#@' then
1196 l_serviceDate := null;
1197 else
1198 l_serviceDate := p_serviceDate;
1199 end if;
1200 if p_txnTypeName = '$$#@' then
1201 l_txnTypeName := null;
1202 else
1203 l_txnTypeName := p_txnTypeName;
1204 end if;
1205 if p_updateIBFlag = '$$#@' then
1206 l_updateIBFlag := null;
1207 else
1208 l_updateIBFlag := p_updateIBFlag;
1209 end if;
1210
1211 if p_retReasonCd = '$$#@' then
1212 l_retReasonCd := null;
1213 else
1214 l_retReasonCd := p_retReasonCd;
1215 end if;
1216 if p_serialNr = '$$#@' then
1217 l_serialNr := null;
1218 else
1219 l_serialNr := p_serialNr ;
1220 end if;
1221 if p_lotNr = '$$#@' then
1222 l_lotNr := null;
1223 else
1224 l_lotNr := p_lotNr ;
1225 end if;
1226 if p_revisionNr = '$$#@' then
1227 l_revisionNr := null;
1228 else
1229 l_revisionNr := p_revisionNr;
1230 end if;
1231 if p_UOM = '$$#@' then
1232 l_UOM := null;
1233 else
1234 l_UOM := p_UOM;
1235 end if;
1236 if p_srcChangeOwner = '$$#@' then
1237 l_srcChangeOwner := null;
1241 if p_srcChangeOwnerToCode = '$$#@' then
1238 else
1239 l_srcChangeOwner := p_srcChangeOwner;
1240 end if;
1242 l_srcChangeOwnerToCode := null;
1243 else
1244 l_srcChangeOwnerToCode := p_srcChangeOwnerToCode ;
1245 end if;
1246 if p_srcReferenceReqd = '$$#@' then
1247 l_srcReferenceReqd := null;
1248 else
1249 l_srcReferenceReqd := p_srcReferenceReqd ;
1250 end if;
1251 if p_srcReturnReqd = '$$#@' then
1252 l_srcReturnReqd := null;
1253 else
1254 l_srcReturnReqd := p_srcReturnReqd;
1255 end if;
1256 if p_parentReferenceReqd = '$$#@' then
1257 l_parentReferenceReqd := null;
1258 else
1259 l_parentReferenceReqd := p_parentReferenceReqd;
1260 end if;
1261
1262
1263
1264 --dbms_output.put_line('So far so good....');
1265 l_order_header_id := 0;
1266
1267
1268 open c_DEBRIEF_HEADER_ID(l_dbfNr);
1269 fetch c_DEBRIEF_HEADER_ID into l_header_id;
1270 close c_DEBRIEF_HEADER_ID;
1271
1272 open c_dbf_lines;
1273 fetch c_dbf_lines into l_dbf_line_id;
1274 close c_dbf_lines;
1275
1276 if (l_partStatusCd is not null or rtrim(l_partStatusCd) <> '') then
1277 open c_status_name(l_partStatusCd);
1278 fetch c_status_name into l_part_status_name;
1279 close c_status_name;
1280 end if;
1281 --This part was done as the debrief was looking for part status naem in stead of the id
1282
1283 if (l_part_status_name is not null or rtrim(l_part_status_name) <> '') then
1284 l_partStatusCd := l_part_status_name ;
1285 end if;
1286
1287
1288
1289 -------------------------------------------------------------------------------------------------
1290 -------
1291 ------- check for the Service date, which should not be less than SR date and more than Sysdate
1292 -------
1293 -------------------------------------------------------------------------------------------------
1294 -- first get the date format
1295 FND_PROFILE.GET ( 'CSFWDATEFORMAT' , l_dt_format);
1296 -- Now get the SR Date in date format
1297 open c_creation_date(l_incidentid);
1298 fetch c_creation_date into l_sr_date;
1299 close c_creation_date;
1300
1301 -- bug # 5351199
1302 -- Save service line with 23:59 time
1303 -- bug # 5519603
1304 if(to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(sysdate), l_dt_format) = l_serviceDate)
1305 then
1306 lp_servicedate := to_date(l_serviceDate || ' ' || to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(sysdate), 'HH24:MI'), l_dt_format || ' HH24:MI');
1307 else
1308 lp_servicedate := to_date(l_serviceDate || ' 23:59', l_dt_format || ' HH24:MI');
1309 end if;
1310 -- add the return date
1311 if p_return_date <> '$$#@' then
1312 P_DEBRIEF_LINE_Rec.RETURN_DATE := to_date(p_return_date, l_dt_format);
1313 end if;
1314
1315
1316 -- now check if it is more than sysdate
1317 -- BUG 2225745
1318 -- if l_sr_date is p_service_date, then make l_sr_date = l_sr_date - 1
1319
1320 IF trunc(l_sr_date) = trunc(lp_servicedate)
1321 THEN
1322 l_sr_date := l_sr_date - 1;
1323 END IF;
1324
1325 /* check in JSP only
1326 IF lp_servicedate not between l_sr_date and sysdate
1327 THEN
1328 FND_MESSAGE.Set_Name('CSF', 'CSFW_SERVICE_DATE');
1329 FND_MESSAGE.Set_Token('P_SR_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(l_sr_date), l_dt_format||' HH24:MI'));
1330 FND_MESSAGE.Set_Token('P_SYSTEM_DATE', to_char(CSFW_TIMEZONE_PUB.GET_CLIENT_TIME(sysdate), l_dt_format||' HH24:MI'));
1331
1332
1333 p_error := -21;
1334 p_error := FND_MESSAGE.Get;
1335 RETURN ;
1336 END IF;
1337
1338 */
1339 --dbms_output.put_line('Start to fill the record.....');
1340
1341 if(p_updateFlag = 1) then
1342 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := p_dbfLineId;
1343 else
1344 P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := l_dbf_line_id;
1345 end if;
1346
1347
1348 -- lp_servicedate is in Client Time Zone. Lets Convert it to Service Time Zone
1349 IF (fnd_timezones.timezones_enabled = 'Y') THEN
1350 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);
1351 ELSE
1352 l_server_time := lp_servicedate;
1353 END IF;
1354
1355
1356
1357
1358 P_DEBRIEF_LINE_Rec.DEBRIEF_HEADER_ID := l_header_id ;
1359 P_DEBRIEF_LINE_Rec.SERVICE_DATE := l_server_time;
1360 P_DEBRIEF_LINE_Rec.BUSINESS_PROCESS_ID := l_businessProcessId;
1361 P_DEBRIEF_LINE_Rec.TXN_BILLING_TYPE_ID := l_billingTypeId;
1362 P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID := l_itemId;
1363
1364 P_DEBRIEF_LINE_Rec.INSTANCE_ID := l_instanceId;
1365 P_DEBRIEF_LINE_Rec.PARENT_PRODUCT_ID := l_parentProductId;
1366 P_DEBRIEF_LINE_Rec.REMOVED_PRODUCT_ID := l_recoveredPartId;
1367
1368 P_DEBRIEF_LINE_Rec.STATUS_OF_RECEIVED_PART := l_partStatusCd;
1369 P_DEBRIEF_LINE_Rec.ITEM_SERIAL_NUMBER := l_serialNr;
1370 P_DEBRIEF_LINE_Rec.ITEM_REVISION := l_revisionNr;
1371 P_DEBRIEF_LINE_Rec.ITEM_LOTNUMBER := l_lotNr;
1375 P_DEBRIEF_LINE_Rec.CHANNEL_CODE := 'WIRELESS_USER';
1372 P_DEBRIEF_LINE_Rec.UOM_CODE := l_UOM;
1373 P_DEBRIEF_LINE_Rec.QUANTITY := l_qty;
1374 P_DEBRIEF_LINE_Rec.MATERIAL_REASON_CODE := l_reasonCd;
1376 P_DEBRIEF_LINE_Rec.RETURN_REASON_CODE := l_retReasonCd;
1377
1378 IF l_orderCategoryCode = 'ORDER'
1379 THEN
1380 P_DEBRIEF_LINE_Rec.ISSUING_INVENTORY_ORG_ID := l_orgId;
1381 P_DEBRIEF_LINE_Rec.ISSUING_SUB_INVENTORY_CODE := l_subInv;
1382 P_DEBRIEF_LINE_Rec.ISSUING_LOCATOR_ID := l_locatorId;
1383 ELSE
1384 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
1385 p_debrief_line_rec.issuing_sub_inventory_code := NULL;-- this is also a hack
1386 P_DEBRIEF_LINE_Rec.RECEIVING_INVENTORY_ORG_ID := l_orgId;
1387 P_DEBRIEF_LINE_Rec.RECEIVING_SUB_INVENTORY_CODE := l_subInv;
1388 P_DEBRIEF_LINE_Rec.RECEIVING_LOCATOR_ID := l_locatorId;
1389 END IF;
1390
1391 -- P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_NUMBER
1392 -- P_DEBRIEF_LINE_Rec.RMA_HEADER_ID
1393 -- P_DEBRIEF_LINE_Rec.DISPOSITION_CODE
1394 -- P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_STATUS_ID
1395 -- P_DEBRIEF_LINE_Rec.CHARGE_UPLOAD_STATUS
1396 -- P_DEBRIEF_LINE_Rec.CHARGE_UPLOAD_MSG_CODE
1397 -- P_DEBRIEF_LINE_Rec.CHARGE_UPLOAD_MESSAGE
1398 -- P_DEBRIEF_LINE_Rec.IB_UPDATE_STATUS
1399 -- P_DEBRIEF_LINE_Rec.IB_UPDATE_MSG_CODE
1400 -- P_DEBRIEF_LINE_Rec.IB_UPDATE_MESSAGE
1401 -- P_DEBRIEF_LINE_Rec.SPARE_UPDATE_STATUS
1402 -- P_DEBRIEF_LINE_Rec.SPARE_UPDATE_MSG_CODE
1403 -- P_DEBRIEF_LINE_Rec.SPARE_UPDATE_MESSAGE
1404 --Setting the Transaction Id
1405 P_DEBRIEF_LINE_Rec.TRANSACTION_TYPE_ID := l_txnTypeId;
1406
1407
1408 --dbms_output.put_line('putting into table....');
1409 --dbms_output.put_line('P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID....'||P_DEBRIEF_LINE_Rec.INVENTORY_ITEM_ID);
1410
1411
1412 P_DEBRIEF_TBL (1) := P_DEBRIEF_LINE_Rec;
1413 --dbms_output.put_line('table filled. Call API....');
1414
1415
1416 if (p_updateFlag = 1) then
1417
1418 CSF_DEBRIEF_PUB.Update_debrief_line(
1419 P_Api_Version_Number => 1.0,
1420 P_Init_Msg_List => FND_API.G_FALSE,
1421 P_Commit => FND_API.G_TRUE,
1422 P_Upd_tskassgnstatus => NULL,
1423 P_Task_Assignment_status => NULL,
1424 P_DEBRIEF_LINE_Rec => P_DEBRIEF_LINE_Rec,
1425 X_Return_Status => l_return_status ,
1426 X_Msg_Count => l_msg_count ,
1427 X_Msg_Data => l_msg_data
1428 );
1429 else
1430 CSF_DEBRIEF_PUB.Create_debrief_lines(
1431 P_Api_Version_Number => 1.0,
1432 P_Init_Msg_List => FND_API.G_FALSE,
1433 P_Commit => FND_API.G_TRUE,
1434 P_Upd_tskassgnstatus => NULL,
1435 P_Task_Assignment_status => NULL,
1436 P_DEBRIEF_LINE_Tbl => P_DEBRIEF_TBL ,
1437 P_DEBRIEF_HEADER_ID => l_header_id,
1438 P_SOURCE_OBJECT_TYPE_CODE => 'CSFW' ,
1439 X_Return_Status => l_return_status ,
1440 X_Msg_Count => l_msg_count ,
1441 X_Msg_Data => l_msg_data
1442 );
1443
1444 end if;
1445
1446
1447 IF l_return_status = FND_API.G_RET_STS_SUCCESS
1448 THEN
1449 p_error_id := 0;
1450 p_error := 'S';
1451 p_ret_dbfLine_id := P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID; -- for DFF
1452 ELSE
1453 FOR l_counter IN 1 .. l_msg_count
1454 LOOP
1455 fnd_msg_pub.get
1456 ( p_msg_index => l_counter
1457 , p_encoded => FND_API.G_FALSE
1458 , p_data => l_data
1459 , p_msg_index_out => l_msg_index_out
1460 );
1461 --dbms_output.put_line('l_data '|| l_data);
1462 END LOOP;
1463 p_error_id := 1;
1464 p_error := l_data;
1465
1466
1467 END IF;
1468 if p_error = FND_API.G_RET_STS_SUCCESS
1469 then
1470 p_error := '';
1471 end if;
1472 EXCEPTION
1473 WHEN OTHERS
1474 THEN
1475 p_error_id := -1;
1476 p_error := SQLERRM;
1477
1478 END SAVE_DEBRIEF_MATERIAL_LINE ;
1479
1480
1481 /*
1482 PROCEDURE UPDATE_CHARGES(
1483 p_dbfLineId in number,
1484 p_incidentId in number,
1485 p_error out NOCOPY varchar2,
1486 p_error_id out NOCOPY number
1487 )IS
1488 l_return_status varchar2(10);
1489 l_object_version_number NUMBER;
1490 l_estimate_detail_id number;
1491 l_msg_count number;
1492 l_msg_data varchar2(2000);
1493 l_header_id number;
1494 l_busProcessId number;
1495 l_user number;
1496 l_data varchar2(2000);
1497 l_msg_index_out number;
1498 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
1499 l_organization_id number;
1500
1501 l_UOM_code varchar2(100);
1502 l_order_header_id number;
1503 l_order_type_id number;
1504 l_line_type_id number;
1505 l_Charges_Rec CS_Charge_Details_PUB.Charges_Rec_Type;
1506 l_line_number NUMBER;
1507 l_dt_format varchar2(10);
1508 l_sr_date DATE;
1509 l_interface_status varchar2(20);
1513
1510 l_interface_status_meaning varchar2(20);
1511 l_orderCategoryCode varchar2(50);
1512
1514 cursor c_line_type_id_order (p_order_type_id number,p_incident_id Number) is
1515 select default_outbound_line_type_id
1516 from oe_transaction_types_all
1517 where transaction_type_id = p_order_type_id
1518 and transaction_type_code = 'ORDER';
1519
1520 cursor c_line_type_id_return (p_order_type_id number,p_incident_id number) is
1521 select default_inbound_line_type_id
1522 from oe_transaction_types_all
1523 where transaction_type_id = p_order_type_id
1524 and transaction_type_code = 'ORDER';
1525
1526 Cursor c_status_meaning(p_code Varchar2) Is
1527 select meaning
1528 from fnd_lookups
1529 where lookup_type = 'CSF_INTERFACE_STATUS'
1530 and lookup_code = p_code;
1531
1532
1533 CURSOR c_dbfLineRec
1534 ( b_dbfLineId csf_debrief_lines.DEBRIEF_LINE_ID%TYPE
1535 )
1536 IS
1537 SELECT BUSINESS_PROCESS_ID
1538 , inventory_item_id
1539 , UOM_CODE
1540 , QUANTITY
1541 , TXN_BILLING_TYPE_ID
1542 , REMOVED_PRODUCT_ID
1543 , RETURN_REASON_CODE
1544 FROM CSF_DEBRIEF_MAT_LINES_V
1545 WHERE DEBRIEF_LINE_ID = b_dbfLineId;
1546
1547 r_dbfLineRec c_dbfLineRec%ROWTYPE;
1548
1549
1550 BEGIN
1551
1552 l_order_header_id := 0;
1553
1554
1555 OPEN c_dbfLineRec
1556 ( b_dbfLineId => p_dbfLineId
1557 );
1558 FETCH c_dbfLineRec
1559 INTO r_dbfLineRec;
1560 CLOSE c_dbfLineRec;
1561
1562
1563
1564
1565 select line_order_category_code into l_orderCategoryCode
1566 from cs_transaction_types_b
1567 where TRANSACTION_TYPE_ID = (select TRANSACTION_TYPE_ID from CSF_DEBRIEF_MAT_LINES_V
1568 where DEBRIEF_LINE_ID = p_dbfLineId);
1569
1570
1571 select order_type_id into l_order_type_id from cs_business_processes where business_process_id = r_dbfLineRec.BUSINESS_PROCESS_ID;
1572
1573 if (l_orderCategoryCode = 'ORDER') then
1574 open c_line_type_id_order (l_order_type_id,p_incidentId);
1575 fetch c_line_type_id_order into l_line_type_id;
1576 close c_line_type_id_order;
1577 else
1578 open c_line_type_id_return (l_order_type_id,p_incidentId);
1579 fetch c_line_type_id_return into l_line_type_id;
1580 close c_line_type_id_return;
1581 end if;
1582
1583
1584
1585
1586 l_Charges_Rec.original_source_id := p_incidentId;
1587 l_Charges_Rec.original_source_code := 'SR' ;
1588 l_Charges_Rec.incident_id := p_incidentId ;
1589 l_Charges_Rec.business_process_id := r_dbfLineRec.BUSINESS_PROCESS_ID;
1590 l_Charges_Rec.order_header_id := l_order_header_id ;
1591 l_Charges_Rec.line_category_code := l_orderCategoryCode;
1592 --l_Charges_Rec.line_type_id := l_line_type_id ;
1593 l_Charges_Rec.source_code := 'SD';
1594 l_Charges_Rec.source_id := p_dbfLineId;
1595
1596 l_Charges_Rec.inventory_item_id_in := r_dbfLineRec.inventory_item_id;
1597 l_charges_rec.unit_of_measure_code := r_dbfLineRec.UOM_CODE;
1598 l_charges_rec.quantity_required := r_dbfLineRec.QUANTITY;
1599 l_charges_rec.txn_billing_type_id := r_dbfLineRec.TXN_BILLING_TYPE_ID;
1600 l_charges_rec.customer_product_id := r_dbfLineRec.REMOVED_PRODUCT_ID;
1601 l_charges_rec.installed_cp_return_by_date := sysdate;
1602 l_charges_rec.after_warranty_cost := null;
1603 l_charges_rec.currency_code := null;
1604 l_charges_rec.return_reason_code := r_dbfLineRec.RETURN_REASON_CODE;
1605
1606 CS_Charge_Details_PUB.Create_Charge_Details(
1607 p_api_version => 1.0,
1608 x_return_status => l_return_status,
1609 x_msg_count => l_msg_count,
1610 x_object_version_number => l_object_version_number,
1611 x_msg_data => l_msg_data,
1612 x_estimate_detail_id => l_estimate_detail_id,
1613 x_line_number => l_line_number,
1614 p_Charges_Rec => l_Charges_Rec
1615 );
1616
1617 IF l_return_status = FND_API.G_RET_STS_SUCCESS
1618 THEN
1619 l_interface_status := 'SUCCEEDED';
1620 ELSE
1621 l_interface_status := 'FAILED';
1622 END IF;
1623
1624 open c_status_meaning(l_interface_status);
1625 fetch c_status_meaning INTO l_interface_status_meaning;
1626 close c_status_meaning;
1627
1628 CSF_DEBRIEF_LINES_PKG.Update_Row(
1629 p_DEBRIEF_LINE_ID => p_dbfLineId,
1630 p_DEBRIEF_HEADER_ID => FND_API.G_MISS_NUM,
1631 p_DEBRIEF_LINE_NUMBER => FND_API.G_MISS_NUM,
1632 p_SERVICE_DATE => FND_API.G_MISS_DATE,
1633 p_BUSINESS_PROCESS_ID => FND_API.G_MISS_NUM,
1634 p_TXN_BILLING_TYPE_ID => FND_API.G_MISS_NUM,
1635 p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
1636 P_INSTANCE_ID => FND_API.G_MISS_NUM,
1637 p_ISSUING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
1638 p_RECEIVING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
1639 p_ISSUING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
1643 p_PARENT_PRODUCT_ID => FND_API.G_MISS_NUM,
1640 p_RECEIVING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
1641 p_ISSUING_LOCATOR_ID => FND_API.G_MISS_NUM,
1642 p_RECEIVING_LOCATOR_ID => FND_API.G_MISS_NUM,
1644 p_REMOVED_PRODUCT_ID => FND_API.G_MISS_NUM,
1645 p_STATUS_OF_RECEIVED_PART => FND_API.G_MISS_CHAR,
1646 p_ITEM_SERIAL_NUMBER => FND_API.G_MISS_CHAR,
1647 p_ITEM_REVISION => FND_API.G_MISS_CHAR,
1648 p_ITEM_LOTNUMBER => FND_API.G_MISS_CHAR,
1649 p_UOM_CODE => FND_API.G_MISS_CHAR,
1650 p_QUANTITY => FND_API.G_MISS_NUM,
1651 p_RMA_HEADER_ID => FND_API.G_MISS_NUM,
1652 p_DISPOSITION_CODE => FND_API.G_MISS_CHAR,
1653 p_MATERIAL_REASON_CODE => FND_API.G_MISS_CHAR,
1654 p_LABOR_REASON_CODE => FND_API.G_MISS_CHAR,
1655 p_EXPENSE_REASON_CODE => FND_API.G_MISS_CHAR,
1656 p_LABOR_START_DATE => FND_API.G_MISS_DATE,
1657 p_LABOR_END_DATE => FND_API.G_MISS_DATE,
1658 p_STARTING_MILEAGE => FND_API.G_MISS_NUM,
1659 p_ENDING_MILEAGE => FND_API.G_MISS_NUM,
1660 p_EXPENSE_AMOUNT => FND_API.G_MISS_NUM,
1661 p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
1662 p_DEBRIEF_LINE_STATUS_ID => FND_API.G_MISS_NUM,
1663 P_RETURN_REASON_CODE => FND_API.G_MISS_CHAR,
1664 p_CHANNEL_CODE => 'WIRELESS_USER',
1665 p_CHARGE_UPLOAD_STATUS => l_interface_status,
1666 p_CHARGE_UPLOAD_MSG_CODE => FND_API.G_MISS_CHAR,
1667 p_CHARGE_UPLOAD_MESSAGE => l_interface_status_meaning,
1668 p_IB_UPDATE_STATUS => FND_API.G_MISS_CHAR,
1669 p_IB_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
1670 p_IB_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
1671 p_SPARE_UPDATE_STATUS => FND_API.G_MISS_CHAR,
1672 p_SPARE_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
1673 p_SPARE_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
1674 p_CREATED_BY => FND_API.G_MISS_NUM,
1675 p_CREATION_DATE => FND_API.G_MISS_DATE,
1676 p_LAST_UPDATED_BY => FND_API.G_MISS_NUM,
1677 p_LAST_UPDATE_DATE => FND_API.G_MISS_DATE,
1678 p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
1679 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
1680 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
1681 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
1682 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
1683 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
1684 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
1685 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
1686 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
1687 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
1688 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
1689 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
1690 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
1691 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
1692 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
1693 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
1694 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR);
1695
1696 commit work;
1697
1698 p_error := 'S';
1699 p_error_id := 0;
1700
1701
1702 if p_error = FND_API.G_RET_STS_SUCCESS
1703 then
1704 p_error := '';
1705 end if;
1706
1707 EXCEPTION
1708 WHEN OTHERS
1709 THEN
1710 p_error_id := -1;
1711 p_error := SQLERRM;
1712
1713
1714 END UPDATE_CHARGES;
1715
1716
1717 PROCEDURE UPDATE_IB
1718 (
1719 p_dbfLineId in number,
1720 p_incidentId in number,
1721 p_error_id out NOCOPY number,
1722 p_error out NOCOPY varchar2
1723
1724 ) IS
1725 l_in_out_flag varchar2(4);
1726 l_transaction_type_id_csi number ;
1727 l_txn_sub_type_id number ;
1728
1729 l_instance_id number ;
1730 l_parent_instance_id number ;
1731 l_new_instance_id number := null;
1732 l_new_instance_number Varchar2(40);
1733
1734 l_inventory_item_id number ;
1735 l_inv_organization_id number ;
1736 l_inv_subinventory_name varchar2(60);
1737 l_inv_master_organization_id number ;
1738 l_quantity number ;
1739 l_mfg_serial_number_flag varchar2(3) ;
1740 l_service_date date ;
1741 l_shipped_date date;
1742 l_currency_code varchar2(10);
1743
1744 l_party_id number ;
1745 l_party_account_id number ;
1746 l_customer_id number ;
1747 l_party_site_id number;
1748
1749 l_debrief_line_id number ;
1750 l_debrief_header_id number ;
1751 l_incident_id number ;
1752
1753
1757 l_install_site_use_id number;
1754 l_return_status varchar2(1);
1755 l_mesg varchar2(2000);
1756 l_counter number;
1758 l_ship_site_use_id number;
1759 l_parent_cpid number;
1760 l_serial_number varchar2(30) ;
1761 l_lot_number varchar2(30) ;
1762 l_UOM varchar2(30);
1763 l_msg_count number;
1764 l_msg_index_out number;
1765 l_interface_status varchar2(20);
1766 l_interface_status_meaning varchar2(20);
1767 l_data varchar2(1000);
1768 l_msg_data varchar2(1000);
1769 P_DEBRIEF_LINE_Rec CSF_DEBRIEF_PUB.DEBRIEF_LINE_Rec_Type;
1770
1771
1772 Cursor c_internal_party_id Is
1773 select internal_party_id
1774 from csi_install_parameters;
1775
1776 Cursor c_status_meaning(p_code Varchar2) Is
1777 select meaning
1778 from fnd_lookups
1779 where lookup_type = 'CSF_INTERFACE_STATUS'
1780 and lookup_code = p_code;
1781
1782 Cursor c_site (p_incident_id number) Is
1783 select install_site_use_id,
1784 ship_to_site_use_id
1785 from cs_incidents_all
1786 where incident_id = p_incident_id;
1787 cursor c_party_site_id (p_install_site_id number) Is
1788 select party_site_id
1789 from hz_party_site_uses
1790 where party_site_use_id = p_install_site_id;
1791 Cursor c_instance_number(p_instance_id Number) is
1792 select instance_number
1793 from csi_item_instances
1794 where instance_id = p_instance_id;
1795
1796
1797
1798
1799 CURSOR c_dbfLineRec
1800 ( b_dbfLineId csf_debrief_lines.DEBRIEF_LINE_ID%TYPE
1801 )
1802 IS
1803 SELECT service_date
1804 , DEBRIEF_HEADER_ID
1805 , inventory_item_id
1806 , PARENT_PRODUCT_ID
1807 , ITEM_SERIAL_NUMBER
1808 , INVENTORY_ORG_ID
1809 , SUB_INVENTORY_CODE
1810 , INSTANCE_ID
1811 , QUANTITY
1812 , UOM_CODE
1813
1814 FROM CSF_DEBRIEF_MAT_LINES_V
1815 WHERE DEBRIEF_LINE_ID = b_dbfLineId;
1816
1817 r_dbfLineRec c_dbfLineRec%ROWTYPE;
1818
1819
1820
1821 cursor c_ib_rec (b_dbfLineId csf_debrief_lines.DEBRIEF_LINE_ID%TYPE) is
1822 select
1823 ctst.sub_type_id sub_type_id ,
1824 ctst.transaction_type_id transaction_type_id_csi ,
1825 cttv.line_order_category_code line_order_category_code
1826 from
1827 cs_transaction_types_vl cttv,
1828 cs_txn_billing_types ctbt,
1829 cs_bus_process_txns cbpt,
1830 cs_business_processes cbp,
1831 csi_txn_sub_types ctst,
1832 csi_txn_types ctt,
1833 csi_instance_statuses cis
1834 where
1835 cttv.transaction_type_id = ctbt.transaction_type_id and
1836 ctbt.transaction_type_id = cbpt.transaction_type_id
1837 and ctbt.transaction_type_id = (select transaction_type_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
1838 and trunc(sysdate) between nvl(ctbt.start_date_active,to_date(sysdate)) and nvl(ctbt.end_date_active,to_date(sysdate))
1839 and trunc(sysdate) between nvl(cbpt.start_date_active, to_date(sysdate)) and nvl(cbpt.end_date_active, to_date(sysdate))
1840 and cbpt.business_process_id = cbp.business_process_id
1841 and cbpt.business_process_id = (select business_process_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
1842 and cbp.field_service_flag = 'Y'
1843 and ctbt.billing_type = 'M'
1844 and ctst.cs_transaction_type_id = cttv.transaction_type_id
1845 and ctt.source_application_id=513
1846 and ctt.transaction_type_id = ctst.transaction_type_id
1847 and ctst.src_status_id = cis.instance_status_id(+)
1848 and (nvl(ctst.update_ib_flag, 'N') = 'N'
1849 or ( ctst.update_ib_flag = 'Y'
1850 and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
1851 and nvl(cis.terminated_flag, 'N') <> 'Y'
1852 and ctst.src_change_owner = 'Y'
1853 and nvl(ctst.src_return_reqd, 'N') = 'N'
1854 and (
1855 (ctst.src_change_owner_to_code = 'I'
1856 and nvl(ctst.parent_reference_reqd, 'N') = 'N'
1857 and cttv.line_order_category_code='RETURN')
1858 or
1859 (ctst.src_change_owner_to_code = 'E'
1860 and ctst.src_reference_reqd = 'Y'
1861 and cttv.line_order_category_code='ORDER')
1862 )
1863 )
1864 ) ;
1865
1866 r_ib_rec c_ib_rec%ROWTYPE;
1867
1868 cursor c_party(b_dbfId number) is
1869 select customer_id,customer_account_id from csf_debrief_tasks_v where debrief_header_id = b_dbfId;
1870 r_party c_party%ROWTYPE;
1871
1872
1873 BEGIN
1874
1875
1876 OPEN c_ib_rec
1877 ( b_dbfLineId => p_dbfLineId
1878 );
1879 FETCH c_ib_rec
1880 INTO r_ib_rec;
1881 CLOSE c_ib_rec;
1882
1883 OPEN c_dbfLineRec
1884 ( b_dbfLineId => p_dbfLineId
1885 );
1886 FETCH c_dbfLineRec
1887 INTO r_dbfLineRec;
1888 CLOSE c_dbfLineRec;
1889
1890
1894
1891 l_currency_code :='USD';-- this method is never used now
1892
1893
1895 l_service_Date := r_dbfLineRec.service_date;
1896 l_shipped_date := r_dbfLineRec.service_date;
1897 l_debrief_line_id := p_dbfLineId;
1898 l_debrief_header_id := r_dbfLineRec.DEBRIEF_HEADER_ID;
1899 l_incident_id := p_incidentId;
1900 l_parent_cpid := r_dbfLineRec.PARENT_PRODUCT_ID;
1901 l_serial_number := r_dbfLineRec.ITEM_SERIAL_NUMBER;
1902 l_transaction_type_id_csi := r_ib_rec.transaction_type_id_csi;
1903 l_txn_sub_type_id := r_ib_rec.sub_type_id;
1904
1905 l_instance_id := r_dbfLineRec.INSTANCE_ID;
1906 l_parent_instance_id := r_dbfLineRec.PARENT_PRODUCT_ID;
1907 l_inventory_item_id := r_dbfLineRec.inventory_item_id;
1908 l_inv_organization_id := r_dbfLineRec.INVENTORY_ORG_ID ;
1909 l_inv_subinventory_name := r_dbfLineRec.SUB_INVENTORY_CODE ;
1910 l_inv_master_organization_id := r_dbfLineRec.INVENTORY_ORG_ID ;
1911 l_quantity := r_dbfLineRec.QUANTITY;
1912 l_uom := r_dbfLineRec.UOM_CODE;
1913 l_mfg_serial_number_flag := 'N' ;
1914
1915 OPEN c_party
1916 ( b_dbfId => r_dbfLineRec.DEBRIEF_HEADER_ID
1917 );
1918 FETCH c_party
1919 INTO r_party;
1920 CLOSE c_party;
1921
1922 l_party_id := r_party.customer_id;
1923 l_party_account_id := r_party.customer_account_id;
1924
1925
1926 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_inv_organization_id);
1927
1928 if (r_ib_rec.line_order_category_code = 'RETURN' ) then
1929 l_in_out_flag:='IN';
1930 open c_internal_party_id;
1931 fetch c_internal_party_id into l_party_id;
1932 close c_internal_party_id;
1933 else
1934 l_in_out_flag:='OUT';
1935 open c_site(l_incident_id);
1936 fetch c_site into l_install_site_use_id, l_ship_site_use_id;
1937 close c_site;
1938 l_install_site_use_id := nvl(l_install_site_use_id, l_ship_site_use_id);
1939 open c_party_site_id (l_install_site_use_id);
1940 fetch c_party_site_id into l_party_site_id;
1941 close c_party_site_id;
1942 end if;
1943
1944 csf_ib.update_install_base(
1945 p_api_version => 1.0,
1946 p_init_msg_list => null,
1947 p_commit => null,
1948 p_validation_level => null,
1949 x_return_status => l_return_status,
1950 x_msg_count => l_msg_count,
1951 x_msg_data => l_msg_data,
1952 x_new_instance_id => l_new_instance_id,
1953 p_in_out_flag => l_in_out_flag,
1954 p_transaction_type_id => l_transaction_type_id_csi,
1955 p_txn_sub_type_id => l_txn_sub_type_id,
1956 p_instance_id => l_instance_id,
1957 p_inventory_item_id => l_inventory_item_id,
1958 p_inv_organization_id => l_inv_organization_id,
1959 p_inv_subinventory_name => l_inv_subinventory_name,
1960 p_quantity => l_quantity,
1961 p_inv_master_organization_id => l_inv_master_organization_id,
1962 p_mfg_serial_number_flag => l_mfg_serial_number_flag,
1963 p_serial_number => l_serial_number,
1964 p_lot_number => l_lot_number,
1965 p_unit_of_measure => l_uom,
1966 p_party_id => l_party_id,
1967 p_party_account_id => l_party_account_id,
1968 p_party_site_id => l_party_site_id,
1969 p_parent_instance_id => l_parent_instance_id) ;
1970
1971
1972 if l_RETURN_STATUS = 'S' then -- success
1973 l_interface_status := 'SUCCEEDED';
1974 else
1975 l_interface_status := 'FAILED';
1976 FOR l_counter IN 1 .. l_msg_count
1977 LOOP
1978 fnd_msg_pub.get
1979 ( p_msg_index => l_counter
1980 , p_encoded => FND_API.G_FALSE
1981 , p_data => l_data
1982 , p_msg_index_out => l_msg_index_out
1983 );
1984 --dbms_output.put_line( 'Message: '||l_data );
1985 END LOOP ;
1986
1987
1988
1989 end if;
1990
1991 open c_status_meaning(l_interface_status);
1992 fetch c_status_meaning INTO l_interface_status_meaning;
1993 close c_status_meaning;
1994
1995 CSF_DEBRIEF_LINES_PKG.Update_Row(
1996 p_DEBRIEF_LINE_ID => p_dbfLineId,
1997 p_DEBRIEF_HEADER_ID => FND_API.G_MISS_NUM,
1998 p_DEBRIEF_LINE_NUMBER => FND_API.G_MISS_NUM,
1999 p_SERVICE_DATE => FND_API.G_MISS_DATE,
2000 p_BUSINESS_PROCESS_ID => FND_API.G_MISS_NUM,
2001 p_TXN_BILLING_TYPE_ID => FND_API.G_MISS_NUM,
2002 p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
2003 P_INSTANCE_ID => FND_API.G_MISS_NUM,
2004 p_ISSUING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
2005 p_RECEIVING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
2006 p_ISSUING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
2007 p_RECEIVING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
2008 p_ISSUING_LOCATOR_ID => FND_API.G_MISS_NUM,
2009 p_RECEIVING_LOCATOR_ID => FND_API.G_MISS_NUM,
2010 p_PARENT_PRODUCT_ID => FND_API.G_MISS_NUM,
2011 p_REMOVED_PRODUCT_ID => FND_API.G_MISS_NUM,
2012 p_STATUS_OF_RECEIVED_PART => FND_API.G_MISS_CHAR,
2016 p_UOM_CODE => FND_API.G_MISS_CHAR,
2013 p_ITEM_SERIAL_NUMBER => FND_API.G_MISS_CHAR,
2014 p_ITEM_REVISION => FND_API.G_MISS_CHAR,
2015 p_ITEM_LOTNUMBER => FND_API.G_MISS_CHAR,
2017 p_QUANTITY => FND_API.G_MISS_NUM,
2018 p_RMA_HEADER_ID => FND_API.G_MISS_NUM,
2019 p_DISPOSITION_CODE => FND_API.G_MISS_CHAR,
2020 p_MATERIAL_REASON_CODE => FND_API.G_MISS_CHAR,
2021 p_LABOR_REASON_CODE => FND_API.G_MISS_CHAR,
2022 p_EXPENSE_REASON_CODE => FND_API.G_MISS_CHAR,
2023 p_LABOR_START_DATE => FND_API.G_MISS_DATE,
2024 p_LABOR_END_DATE => FND_API.G_MISS_DATE,
2025 p_STARTING_MILEAGE => FND_API.G_MISS_NUM,
2026 p_ENDING_MILEAGE => FND_API.G_MISS_NUM,
2027 p_EXPENSE_AMOUNT => FND_API.G_MISS_NUM,
2028 p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
2029 p_DEBRIEF_LINE_STATUS_ID => FND_API.G_MISS_NUM,
2030 P_RETURN_REASON_CODE => FND_API.G_MISS_CHAR,
2031 p_CHANNEL_CODE => 'WIRELESS_USER',
2032 p_CHARGE_UPLOAD_STATUS => FND_API.G_MISS_CHAR,
2033 p_CHARGE_UPLOAD_MSG_CODE => FND_API.G_MISS_CHAR,
2034 p_CHARGE_UPLOAD_MESSAGE => FND_API.G_MISS_CHAR,
2035 p_IB_UPDATE_STATUS => l_interface_status,
2036 p_IB_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
2037 p_IB_UPDATE_MESSAGE => l_interface_status_meaning,
2038 p_SPARE_UPDATE_STATUS => FND_API.G_MISS_CHAR,
2039 p_SPARE_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
2040 p_SPARE_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
2041 p_CREATED_BY => FND_API.G_MISS_NUM,
2042 p_CREATION_DATE => FND_API.G_MISS_DATE,
2043 p_LAST_UPDATED_BY => FND_API.G_MISS_NUM,
2044 p_LAST_UPDATE_DATE => FND_API.G_MISS_DATE,
2045 p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
2046 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
2047 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
2048 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
2049 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
2050 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
2051 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
2052 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
2053 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
2054 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
2055 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
2056 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
2057 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
2058 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
2059 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
2060 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
2061 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR);
2062
2063 commit work;
2064
2065 p_error := 'S';
2066 p_error_id := 0;
2067
2068
2069 if p_error = FND_API.G_RET_STS_SUCCESS
2070 then
2071 p_error := '';
2072 end if;
2073
2074 EXCEPTION
2075 WHEN OTHERS
2076 THEN
2077 p_error_id := -1;
2078 p_error := SQLERRM;
2079
2080
2081 END UPDATE_IB;
2082
2083 PROCEDURE UPDATE_SPARES(
2084 p_dbfLineId in number,
2085 p_dbfNr in varchar2,
2086 p_error_id out NOCOPY number,
2087 p_error out NOCOPY varchar2
2088 )IS
2089
2090 l_transaction_type_id number ;
2091 l_lot_number varchar2(30) ;
2092 l_revision varchar2(3) ;
2093 l_serial_number varchar2(30) ;
2094 l_transfer_to_subinventory varchar2(10):= NULL; --optional
2095 l_transfer_to_locator number := NULL; --optional
2096 l_transfer_to_organization number := NULL; --optional
2097 l_api_version number := 1.1;
2098 l_account_id number ;
2099 l_msg_Count number ;
2100 mesg Varchar2(2000);
2101 l_msg_data Varchar2(2000);
2102 l_locator_id number;
2103 lx_transaction_header_id number;
2104 lx_transaction_id number;
2105 l_interface_status varchar2(20);
2106 l_interface_status_meaning varchar2(20);
2107 l_return_status varchar2(10);
2108 l_data varchar2(2000);
2109 l_msg_index_out number;
2110
2111 Cursor c_status_meaning(p_code Varchar2) Is
2112 select meaning
2113 from fnd_lookups
2114 where lookup_type = 'CSF_INTERFACE_STATUS'
2115 and lookup_code = p_code;
2116
2117
2118 CURSOR c_dbfLineRec
2119 ( b_dbfLineId csf_debrief_lines.DEBRIEF_LINE_ID%TYPE
2120 )
2121 IS
2122 SELECT DEBRIEF_HEADER_ID
2123 , inventory_item_id
2124 , PARENT_PRODUCT_ID
2125 , ITEM_SERIAL_NUMBER
2126 , INVENTORY_ORG_ID
2127 , SUB_INVENTORY_CODE
2128 , INSTANCE_ID
2129 , QUANTITY
2130 , UOM_CODE
2131 , LOCATOR
2132 , ITEM_REVISION
2136
2133 , ITEM_LOTNUMBER
2134 FROM CSF_DEBRIEF_MAT_LINES_V
2135 WHERE DEBRIEF_LINE_ID = b_dbfLineId;
2137 r_dbfLineRec c_dbfLineRec%ROWTYPE;
2138
2139
2140
2141 cursor c_ib_rec (b_dbfLineId csf_debrief_lines.DEBRIEF_LINE_ID%TYPE) is
2142 select
2143 ctst.sub_type_id sub_type_id ,
2144 ctst.transaction_type_id transaction_type_id_csi ,
2145 cttv.line_order_category_code line_order_category_code
2146 from
2147 cs_transaction_types_vl cttv,
2148 cs_txn_billing_types ctbt,
2149 cs_bus_process_txns cbpt,
2150 cs_business_processes cbp,
2151 csi_txn_sub_types ctst,
2152 csi_txn_types ctt,
2153 csi_instance_statuses cis
2154 where
2155 cttv.transaction_type_id = ctbt.transaction_type_id and
2156 ctbt.transaction_type_id = cbpt.transaction_type_id
2157 and ctbt.transaction_type_id = (select transaction_type_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
2158 and trunc(sysdate) between nvl(ctbt.start_date_active,to_date(sysdate)) and nvl(ctbt.end_date_active,to_date(sysdate))
2159 and trunc(sysdate) between nvl(cbpt.start_date_active, to_date(sysdate)) and nvl(cbpt.end_date_active, to_date(sysdate))
2160 and cbpt.business_process_id = cbp.business_process_id
2161 and cbpt.business_process_id = (select business_process_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
2162 and cbp.field_service_flag = 'Y'
2163 and ctbt.billing_type = 'M'
2164 and ctst.cs_transaction_type_id = cttv.transaction_type_id
2165 and ctt.source_application_id=513
2166 and ctt.transaction_type_id = ctst.transaction_type_id
2167 and ctst.src_status_id = cis.instance_status_id(+)
2168 and (nvl(ctst.update_ib_flag, 'N') = 'N'
2169 or ( ctst.update_ib_flag = 'Y'
2170 and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
2171 and nvl(cis.terminated_flag, 'N') <> 'Y'
2172 and ctst.src_change_owner = 'Y'
2173 and nvl(ctst.src_return_reqd, 'N') = 'N'
2174 and (
2175 (ctst.src_change_owner_to_code = 'I'
2176 and nvl(ctst.parent_reference_reqd, 'N') = 'N'
2177 and cttv.line_order_category_code='RETURN')
2178 or
2179 (ctst.src_change_owner_to_code = 'E'
2180 and ctst.src_reference_reqd = 'Y'
2181 and cttv.line_order_category_code='ORDER')
2182 )
2183 )
2184 ) ;
2185
2186 r_ib_rec c_ib_rec%ROWTYPE;
2187
2188 BEGIN
2189
2190 OPEN c_ib_rec
2191 ( b_dbfLineId => p_dbfLineId
2192 );
2193 FETCH c_ib_rec
2194 INTO r_ib_rec;
2195 CLOSE c_ib_rec;
2196
2197 OPEN c_dbfLineRec
2198 ( b_dbfLineId => p_dbfLineId
2199 );
2200 FETCH c_dbfLineRec
2201 INTO r_dbfLineRec;
2202 CLOSE c_dbfLineRec;
2203
2204 p_error_id := 0;
2205 p_error := 'S';
2206
2207
2208
2209
2210 if (r_ib_rec.line_order_category_code = 'ORDER' ) then
2211 l_transaction_type_id := 93; --ISSUEING
2212 else
2213 l_transaction_type_id := 94; --RECEIVING
2214 end if;
2215
2216
2217 CSP_TRANSACTIONS_PUB.TRANSACT_MATERIAL(
2218 p_api_version => l_api_version,
2219 x_return_status => l_RETURN_STATUS,
2220 x_msg_count => l_MSG_COUNT,
2221 x_msg_data => l_MSG_DATA,
2222 p_init_msg_list => FND_API.G_TRUE,
2223 p_commit => FND_API.G_FALSE,
2224 p_inventory_item_id => r_dbfLineRec.inventory_item_id,
2225 p_organization_id => r_dbfLineRec.INVENTORY_ORG_ID,
2226 p_subinventory_code => r_dbfLineRec.SUB_INVENTORY_CODE,
2227 p_locator_id => r_dbfLineRec.LOCATOR,
2228 p_serial_number => r_dbfLineRec.ITEM_SERIAL_NUMBER,
2229 p_quantity => r_dbfLineRec.QUANTITY,
2230 p_uom => r_dbfLineRec.UOM_CODE,
2231 p_revision => r_dbfLineRec.ITEM_REVISION ,
2232 p_lot_number => r_dbfLineRec.ITEM_LOTNUMBER,
2233 p_transfer_to_subinventory => l_transfer_to_subinventory,
2234 p_transfer_to_locator => l_transfer_to_organization,
2235 p_transfer_to_organization => l_transfer_to_organization,
2236 p_source_id => NULL,
2237 p_source_line_id => NULL,
2238 p_transaction_type_id => l_transaction_type_id,
2239 p_account_id => l_account_id,
2240 px_transaction_header_id => lx_transaction_header_id,
2241 px_transaction_id => lx_transaction_id,
2242 p_transaction_source_id => r_dbfLineRec.DEBRIEF_HEADER_ID,
2243 p_trx_source_line_id => p_dbfLineId,
2244 p_transaction_source_name => p_dbfNr );
2245
2246 if l_RETURN_STATUS = 'S' then -- success
2247 l_interface_status := 'SUCCEEDED';
2248 else
2249 l_interface_status := 'FAILED';
2250 FOR l_counter IN 1 .. l_msg_count
2251 LOOP
2252 fnd_msg_pub.get
2253 ( p_msg_index => l_counter
2254 , p_encoded => FND_API.G_FALSE
2255 , p_data => l_data
2259 END LOOP ;
2256 , p_msg_index_out => l_msg_index_out
2257 );
2258 --dbms_output.put_line( 'Message: '||l_data );
2260
2261 end if;
2262
2263 open c_status_meaning(l_interface_status);
2264 fetch c_status_meaning INTO l_interface_status_meaning;
2265 close c_status_meaning;
2266
2267 CSF_DEBRIEF_LINES_PKG.Update_Row(
2268 p_DEBRIEF_LINE_ID => p_dbfLineId,
2269 p_DEBRIEF_HEADER_ID => FND_API.G_MISS_NUM,
2270 p_DEBRIEF_LINE_NUMBER => FND_API.G_MISS_NUM,
2271 p_SERVICE_DATE => FND_API.G_MISS_DATE,
2272 p_BUSINESS_PROCESS_ID => FND_API.G_MISS_NUM,
2273 p_TXN_BILLING_TYPE_ID => FND_API.G_MISS_NUM,
2274 p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
2275 P_INSTANCE_ID => FND_API.G_MISS_NUM,
2276 p_ISSUING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
2277 p_RECEIVING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
2278 p_ISSUING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
2279 p_RECEIVING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
2280 p_ISSUING_LOCATOR_ID => FND_API.G_MISS_NUM,
2281 p_RECEIVING_LOCATOR_ID => FND_API.G_MISS_NUM,
2282 p_PARENT_PRODUCT_ID => FND_API.G_MISS_NUM,
2283 p_REMOVED_PRODUCT_ID => FND_API.G_MISS_NUM,
2284 p_STATUS_OF_RECEIVED_PART => FND_API.G_MISS_CHAR,
2285 p_ITEM_SERIAL_NUMBER => FND_API.G_MISS_CHAR,
2286 p_ITEM_REVISION => FND_API.G_MISS_CHAR,
2287 p_ITEM_LOTNUMBER => FND_API.G_MISS_CHAR,
2288 p_UOM_CODE => FND_API.G_MISS_CHAR,
2289 p_QUANTITY => FND_API.G_MISS_NUM,
2290 p_RMA_HEADER_ID => FND_API.G_MISS_NUM,
2291 p_DISPOSITION_CODE => FND_API.G_MISS_CHAR,
2292 p_MATERIAL_REASON_CODE => FND_API.G_MISS_CHAR,
2293 p_LABOR_REASON_CODE => FND_API.G_MISS_CHAR,
2294 p_EXPENSE_REASON_CODE => FND_API.G_MISS_CHAR,
2295 p_LABOR_START_DATE => FND_API.G_MISS_DATE,
2296 p_LABOR_END_DATE => FND_API.G_MISS_DATE,
2297 p_STARTING_MILEAGE => FND_API.G_MISS_NUM,
2298 p_ENDING_MILEAGE => FND_API.G_MISS_NUM,
2299 p_EXPENSE_AMOUNT => FND_API.G_MISS_NUM,
2300 p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
2301 p_DEBRIEF_LINE_STATUS_ID => FND_API.G_MISS_NUM,
2302 P_RETURN_REASON_CODE => FND_API.G_MISS_CHAR,
2303 p_CHANNEL_CODE => 'WIRELESS_USER',
2304 p_CHARGE_UPLOAD_STATUS => FND_API.G_MISS_CHAR,
2305 p_CHARGE_UPLOAD_MSG_CODE => FND_API.G_MISS_CHAR,
2306 p_CHARGE_UPLOAD_MESSAGE => FND_API.G_MISS_CHAR,
2307 p_IB_UPDATE_STATUS => FND_API.G_MISS_CHAR,
2308 p_IB_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
2309 p_IB_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
2310 p_SPARE_UPDATE_STATUS => l_interface_status,
2311 p_SPARE_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
2312 p_SPARE_UPDATE_MESSAGE => l_interface_status_meaning,
2313 p_CREATED_BY => FND_API.G_MISS_NUM,
2314 p_CREATION_DATE => FND_API.G_MISS_DATE,
2315 p_LAST_UPDATED_BY => FND_API.G_MISS_NUM,
2316 p_LAST_UPDATE_DATE => FND_API.G_MISS_DATE,
2317 p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
2318 p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
2319 p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
2320 p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
2321 p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
2322 p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
2323 p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
2324 p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
2325 p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
2326 p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
2327 p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
2328 p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
2329 p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
2330 p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
2331 p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
2332 p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
2333 p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR);
2334
2335 commit work;
2336
2337
2338
2339 if p_error = FND_API.G_RET_STS_SUCCESS
2340 then
2341 p_error := '';
2342 end if;
2343
2344 EXCEPTION
2345 WHEN OTHERS
2346 THEN
2347 p_error_id := -1;
2348 p_error := SQLERRM;
2349
2350 END UPDATE_SPARES;
2351 */
2352
2353 FUNCTION validate_labor_time(
2354 p_resource_type_code in Varchar2,
2355 p_resource_id in Number,
2356 p_debrief_line_id in Number,
2357 p_labor_start_date in Date,
2358 p_labor_end_date in Date
2359 )
2360 return varchar IS
2361
2362 l_return_status varchar2(1);
2363 l_msg_count number;
2364 l_msg_data varchar2(255);
2368
2365 l_debrief_number number;
2366 l_task_number varchar2(30);
2367 l_return_value VARCHAR2(255);
2369
2370 BEGIN
2371
2372 l_return_value := 'S';
2373
2374 CSF_DEBRIEF_PVT.VALIDATE_LABOR_TIMES (
2375 P_Init_Msg_List => FND_API.G_FALSE,
2376 P_api_version_number => 1.0,
2377 p_resource_type_code => p_resource_type_code,
2378 p_resource_id => p_resource_id,
2379 p_debrief_line_id => p_debrief_line_id,
2380 p_labor_start_date => p_labor_start_date,
2381 p_labor_end_date => p_labor_end_date,
2382 x_return_status => l_return_status,
2383 x_msg_count => l_msg_count,
2384 x_msg_data => l_msg_data,
2385 x_debrief_number => l_debrief_number,
2386 x_task_number => l_task_number
2387 );
2388
2389 IF l_return_status <> 'S' THEN
2390 l_return_value := l_msg_data;
2391 ELSE
2392 l_return_value := 'S';
2393 END IF;
2394
2395 RETURN l_return_value;
2396
2397 END validate_labor_time;
2398
2399
2400 /* Updates info for travel debrief */
2401 PROCEDURE Create_Travel_Debrief
2402 ( p_task_assignment_id IN NUMBER
2403 , p_debrief_header_id IN NUMBER
2404 , p_start_date IN DATE
2405 , p_end_date IN DATE
2406 , p_distance IN NUMBER
2407 , p_error_id OUT NOCOPY NUMBER
2408 , p_error OUT NOCOPY VARCHAR2
2409 )
2410 IS
2411 P_DEBRIEF_Rec CSF_DEBRIEF_PUB.DEBRIEF_Rec_Type;
2412
2413 l_return_status varchar2(2000);
2414 l_msg_count number;
2415 l_msg_data varchar2(2000);
2416 l_user number;
2417 l_data varchar2(2000);
2418 l_msg_index_out number;
2419
2420 -- cursors
2421
2422 cursor c_dbf_rec (v_hrd_id number, v_asgn_id number) is
2423 select
2424 DEBRIEF_HEADER_ID, DEBRIEF_NUMBER,
2425 DEBRIEF_DATE, DEBRIEF_STATUS_ID,
2426 TASK_ASSIGNMENT_ID, CREATED_BY,
2427 CREATION_DATE, LAST_UPDATED_BY,
2428 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
2429 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
2430 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
2431 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
2432 ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
2433 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
2434 ATTRIBUTE_CATEGORY, object_version_number,
2435 TRAVEL_START_TIME, TRAVEL_END_TIME,
2436 TRAVEL_DISTANCE_IN_KM
2437 from CSF_DEBRIEF_HEADERS
2438 where DEBRIEF_HEADER_ID = v_hrd_id
2439 and TASK_ASSIGNMENT_ID = v_asgn_id;
2440
2441 Begin
2442 p_error_id := 0; --Assume success
2443
2444 l_user := FND_GLOBAL.user_id;
2445
2446 /* getting present values of the debrief header */
2447 open c_dbf_rec(p_debrief_header_id, p_task_assignment_id);
2448 fetch c_dbf_rec into P_DEBRIEF_Rec;
2449 close c_dbf_rec;
2450
2451 /* changing the values to be updated */
2452 P_DEBRIEF_Rec.DEBRIEF_HEADER_ID := p_debrief_header_id;
2453 P_DEBRIEF_Rec.TRAVEL_START_TIME := p_start_date;
2454 P_DEBRIEF_Rec.TRAVEL_END_TIME := p_end_date;
2455 P_DEBRIEF_Rec.TRAVEL_DISTANCE_IN_KM := p_distance;
2456 P_DEBRIEF_Rec.TASK_ASSIGNMENT_ID := p_task_assignment_id;
2457
2458 CSF_DEBRIEF_PUB.Update_DEBRIEF(
2459 P_Api_Version_Number => 1.0,
2460 P_Init_Msg_List => FND_API.G_FALSE,
2461 P_Commit => FND_API.G_TRUE,
2462 P_DEBRIEF_Rec => P_DEBRIEF_Rec,
2463 X_Return_Status => l_return_status,
2464 X_Msg_Count => l_msg_count,
2465 X_Msg_Data => l_msg_data
2466 );
2467
2468
2469 IF l_return_status = FND_API.G_RET_STS_SUCCESS
2470 THEN
2471 /* API-call was successfull */
2472 p_error_id := 0;
2473 p_error := FND_API.G_RET_STS_SUCCESS;
2474 ELSE
2475 FOR l_counter IN 1 .. l_msg_count
2476 LOOP
2477 fnd_msg_pub.get
2478 ( p_msg_index => l_counter
2479 , p_encoded => FND_API.G_FALSE
2480 , p_data => l_data
2481 , p_msg_index_out => l_msg_index_out
2482 );
2483 END LOOP ;
2484 p_error_id := 1;
2485 p_error := l_data;
2486 END IF;
2487
2488 if p_error = FND_API.G_RET_STS_SUCCESS
2489 then
2490 p_error := '';
2491 end if;
2492
2493 EXCEPTION
2494 WHEN OTHERS
2495 THEN
2496 p_error_id := -1;
2497 p_error := SQLERRM;
2498
2499 END Create_Travel_Debrief;
2500
2501 -- To Update Debrief Header DFF values
2502 PROCEDURE Update_Debrief_Header
2503 ( p_DEBRIEF_ID IN NUMBER,
2504 p_DEBRIEF_NUMBER IN VARCHAR2,
2505 p_DEBRIEF_DATE IN DATE,
2506 p_DEBRIEF_STATUS_ID IN NUMBER,
2507 p_TASK_ASSIGNMENT_ID IN NUMBER,
2508 p_CREATED_BY IN NUMBER,
2509 p_CREATION_DATE IN DATE,
2510 p_LAST_UPDATED_BY IN NUMBER,
2511 p_LAST_UPDATE_DATE IN DATE,
2512 p_LAST_UPDATE_LOGIN IN NUMBER,
2513 p_ATTRIBUTE1 IN VARCHAR2,
2514 p_ATTRIBUTE2 IN VARCHAR2,
2518 p_ATTRIBUTE6 IN VARCHAR2,
2515 p_ATTRIBUTE3 IN VARCHAR2,
2516 p_ATTRIBUTE4 IN VARCHAR2,
2517 p_ATTRIBUTE5 IN VARCHAR2,
2519 p_ATTRIBUTE7 IN VARCHAR2,
2520 p_ATTRIBUTE8 IN VARCHAR2,
2521 p_ATTRIBUTE9 IN VARCHAR2,
2522 p_ATTRIBUTE10 IN VARCHAR2,
2523 p_ATTRIBUTE11 IN VARCHAR2,
2524 p_ATTRIBUTE12 IN VARCHAR2,
2525 p_ATTRIBUTE13 IN VARCHAR2,
2526 p_ATTRIBUTE14 IN VARCHAR2,
2527 p_ATTRIBUTE15 IN VARCHAR2,
2528 p_ATTRIBUTE_CATEGORY IN VARCHAR2,
2529 p_return_status OUT NOCOPY VARCHAR2,
2530 p_error_count OUT NOCOPY NUMBER,
2531 p_error OUT NOCOPY VARCHAR2
2532 )
2533 IS
2534 P_DEBRIEF_Rec CSF_DEBRIEF_PUB.DEBRIEF_Rec_Type;
2535 l_error_msg VARCHAR2(2000);
2536 l_msg_index_out NUMBER;
2537 BEGIN
2538
2539 -- set debrief_id
2540 P_DEBRIEF_Rec.DEBRIEF_HEADER_ID := p_DEBRIEF_ID;
2541
2542 -- set other parameters
2543 IF p_ATTRIBUTE1 IS NULL OR p_ATTRIBUTE1 <> '#%*%#'
2544 THEN
2545 P_DEBRIEF_Rec.ATTRIBUTE1 := p_ATTRIBUTE1;
2546 END IF;
2547
2548 IF p_ATTRIBUTE2 IS NULL OR p_ATTRIBUTE2 <> '#%*%#'
2549 THEN
2550 P_DEBRIEF_Rec.ATTRIBUTE2 := p_ATTRIBUTE2;
2551 END IF;
2552
2553 IF p_ATTRIBUTE3 IS NULL OR p_ATTRIBUTE3 <> '#%*%#'
2554 THEN
2555 P_DEBRIEF_Rec.ATTRIBUTE3 := p_ATTRIBUTE3;
2556 END IF;
2557
2558 IF p_ATTRIBUTE4 IS NULL OR p_ATTRIBUTE4 <> '#%*%#'
2559 THEN
2560 P_DEBRIEF_Rec.ATTRIBUTE4 := p_ATTRIBUTE4;
2561 END IF;
2562
2563 IF p_ATTRIBUTE5 IS NULL OR p_ATTRIBUTE5 <> '#%*%#'
2564 THEN
2565 P_DEBRIEF_Rec.ATTRIBUTE5 := p_ATTRIBUTE5;
2566 END IF;
2567
2568 IF p_ATTRIBUTE6 IS NULL OR p_ATTRIBUTE6 <> '#%*%#'
2569 THEN
2570 P_DEBRIEF_Rec.ATTRIBUTE6 := p_ATTRIBUTE6;
2571 END IF;
2572
2573 IF p_ATTRIBUTE7 IS NULL OR p_ATTRIBUTE7 <> '#%*%#'
2574 THEN
2575 P_DEBRIEF_Rec.ATTRIBUTE7 := p_ATTRIBUTE7;
2576 END IF;
2577
2578 IF p_ATTRIBUTE8 IS NULL OR p_ATTRIBUTE8 <> '#%*%#'
2579 THEN
2580 P_DEBRIEF_Rec.ATTRIBUTE8 := p_ATTRIBUTE8;
2581 END IF;
2582
2583 IF p_ATTRIBUTE9 IS NULL OR p_ATTRIBUTE9 <> '#%*%#'
2584 THEN
2585 P_DEBRIEF_Rec.ATTRIBUTE9 := p_ATTRIBUTE9;
2586 END IF;
2587
2588 IF p_ATTRIBUTE10 IS NULL OR p_ATTRIBUTE10 <> '#%*%#'
2589 THEN
2590 P_DEBRIEF_Rec.ATTRIBUTE10 := p_ATTRIBUTE10;
2591 END IF;
2592
2593 IF p_ATTRIBUTE11 IS NULL OR p_ATTRIBUTE11 <> '#%*%#'
2594 THEN
2595 P_DEBRIEF_Rec.ATTRIBUTE11 := p_ATTRIBUTE11;
2596 END IF;
2597
2598 IF p_ATTRIBUTE12 IS NULL OR p_ATTRIBUTE12 <> '#%*%#'
2599 THEN
2600 P_DEBRIEF_Rec.ATTRIBUTE12 := p_ATTRIBUTE12;
2601 END IF;
2602
2603 IF p_ATTRIBUTE13 IS NULL OR p_ATTRIBUTE13 <> '#%*%#'
2604 THEN
2605 P_DEBRIEF_Rec.ATTRIBUTE13 := p_ATTRIBUTE13;
2606 END IF;
2607
2608 IF p_ATTRIBUTE14 IS NULL OR p_ATTRIBUTE14 <> '#%*%#'
2609 THEN
2610 P_DEBRIEF_Rec.ATTRIBUTE14 := p_ATTRIBUTE14;
2611 END IF;
2612
2613 IF p_ATTRIBUTE15 IS NULL OR p_ATTRIBUTE15 <> '#%*%#'
2614 THEN
2615 P_DEBRIEF_Rec.ATTRIBUTE15 := p_ATTRIBUTE15;
2616 END IF;
2617
2618 IF p_ATTRIBUTE_CATEGORY IS NULL OR p_ATTRIBUTE_CATEGORY <> '#%*%#'
2619 THEN
2620 P_DEBRIEF_Rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
2621 END IF;
2622
2623 CSF_DEBRIEF_PUB.Update_debrief (
2624 P_Api_Version_Number => 1.0,
2625 P_Init_Msg_List => FND_API.G_FALSE,
2626 P_Commit => FND_API.G_FALSE,
2627 P_DEBRIEF_Rec => P_DEBRIEF_Rec,
2628 X_Return_Status => p_return_status,
2629 X_Msg_Count => p_error_count,
2630 X_Msg_Data => l_error_msg
2631 );
2632
2633 IF p_return_status = FND_API.G_RET_STS_SUCCESS
2634 THEN
2635 commit;
2636 ELSE
2637 FOR l_counter IN 1 .. p_error_count
2638 LOOP
2639 FND_MSG_PUB.Get (
2640 p_msg_index => l_counter,
2641 p_encoded => FND_API.G_FALSE,
2642 p_data => l_error_msg,
2643 p_msg_index_out => l_msg_index_out
2644 );
2645 END LOOP ;
2646 p_error := l_error_msg;
2647 END IF;
2648
2649
2650 EXCEPTION
2651 WHEN OTHERS
2652 THEN
2653 p_error := SQLERRM;
2654
2655 END Update_Debrief_Header;
2656
2657 END csfw_debrief_pub;
2658