[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIRS_PVT
Source
1 Package BODY Csd_Repairs_Pvt AS
2 /* $Header: csdvdrab.pls 120.38.12020000.4 2013/03/30 00:14:11 yvchen ship $ */
3 --
4 -- Package name : CSD_REPAIRS_PVT
5 -- Purpose : This package contains the private APIs for creating,
6 -- updating, deleting repair orders. Access is
7 -- restricted to Oracle Depot Rapair Internal Development.
8 -- History :
9 -- Version Date Name Description
10 -- 115.0 11/17/99 pkdas Created.
11 -- 115.1 12/20/99 pkdas
12 -- 115.2 01/04/00 pkdas
13 -- 115.3 01/18/00 pkdas
14 -- 115.4 02/14/00 pkdas Added p_REPAIR_LINE_ID as IN parameter in the Create_Repairs
15 -- procedure.
16 -- Added p_REPAIR_NUMBER as OUT parameter in the Create_Repairs
17 -- procedure.
18 -- Added validation logic.
19 -- 115.5 02/29/00 pkdas Changed the procedure name
20 -- Create_Repairs -> Create_Repair_Order
21 -- Update_Repairs -> Update_Repair_Order
22 -- 115.6 04/26/00 pkdas Modified some validation logic.
23 -- 115.7 05/10/00 pkdas Removed defaulting received quantity.
24 -- 115.8 06/22/00 pkdas In the Validate_Customer_Product_ID procedure
25 -- added a check to see whether ORG_ID is null
26 -- or not.
27 --
28 -- 115.12 07/25/01 jkuruvil Commented out sr.org_id for Bug#1847161,1903177
29 --
30 -- 115.13 11/30/01 travi Added AUTO_PROCESS_RMA, OBJECT_VERSION_NUMBER and REPAIR_MODE Col.
31 -- Added Logic to implement the Object_Version_Number
32 -- 115.14 01/14/02 travi Added Item_REVISION Col.
33 -- 115.17 05/02/02 askumar Added Validate_RO_GROUP_ID for 11.5.7.1
34 -- development
35 -- 115.27 23/01/03 saupadhy Commmented proc Validate_Quantity_in_WIP
36 -- NOTE :
37 --
38 -- 09/01/04 saupadhy made changes to Validate_repair procedure to not to
39 -- validate ib_ref_number when repair type is refurbished.
40 -- 115.45 05/19/05 vparvath Adding update_ro_Status API for R12 development.
41 -- 08/15/06 rfieldma Adding new error messages for update repair type and update repair status.
42 -- procedures update_repair_type, update_flow_status
43 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_REPAIRS_PVT';
44 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvdrab.pls';
45 g_debug NUMBER := Csd_Gen_Utility_Pvt.g_debug_level;
46 --
47
48 G_USER_ID NUMBER := Fnd_Global.USER_ID;
49 G_LOGIN_ID NUMBER := Fnd_Global.CONC_LOGIN_ID;
50 G_REQUEST_ID NUMBER := Fnd_Global.CONC_REQUEST_ID;
51 G_PROGRAM_ID NUMBER := Fnd_Global.CONC_PROGRAM_ID;
52 G_PROG_APPL_ID NUMBER := Fnd_Global.PROG_APPL_ID;
53 --
54 procedure debug(l_msg varchar2) is
55 --pragma autonomous_transaction;
56 begin
57 --dbms_output.put_line(msg);
58 --insert into apps.vijay_debug(log_msg,timestamp) values(l_msg, sysdate);
59 --commit;
60 null;
61 end;
62
63 PROCEDURE Create_Repair_Order(
64 P_Api_Version_Number IN NUMBER,
65 P_Init_Msg_List IN VARCHAR2 := Fnd_Api.G_FALSE,
66 P_Commit IN VARCHAR2 := Fnd_Api.G_FALSE,
67 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
68 p_REPAIR_LINE_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
69 P_REPLN_Rec IN Csd_Repairs_Pub.REPLN_Rec_Type,
70 X_REPAIR_LINE_ID OUT NOCOPY NUMBER,
71 X_REPAIR_NUMBER OUT NOCOPY VARCHAR2,
72 X_Return_Status OUT NOCOPY VARCHAR2,
73 X_Msg_Count OUT NOCOPY NUMBER,
74 X_Msg_Data OUT NOCOPY VARCHAR2
75 )
76 IS
77 --
78 l_api_name CONSTANT VARCHAR2(30) := 'Create_Repair_Order';
79 l_api_version_number CONSTANT NUMBER := 1.0;
80 l_rowid ROWID;
81 l_repair_line_id NUMBER;
82 l_repair_number VARCHAR2(30);
83 -- Added following variables to fix bug 3435292 saupadhy
84 l_Approval_required_flag VARCHAR2(1) ;
85 l_Auto_Process_RMA VARCHAR2(1);
86 l_Refurbish_Non_IO_Order VARCHAR2(1);
87
88 l_flow_status_id NUMBER := NULL;
89 l_flow_status_code VARCHAR2(30) := NULL;
90 l_flow_status VARCHAR2(80) := NULL;
91 l_status VARCHAR2(30) := NULL;
92 l_date_closed DATE;
93 l_resolve_by_date DATE; --rfieldma: 5355051
94 l_business_process_id NUMBER; -- rfieldma: 5355051
95 l_severity_id NUMBER; -- rfieldma: 5355051
96 l_REPLN_Rec Csd_Repairs_Pub.REPLN_Rec_Type; -- swai bug 7657379
97
98 -- bug#7043215, subhat
99 -- new DFF value rec.
100 x_dff_value_rec CSD_REPAIRS_UTIL.DEF_Rec_Type;
101
102 -- subhat, resolve by date calc changes.
103 l_resolve_by_dt_calc_at VARCHAR2(30);
104
105 --
106 l_parent_repair_number VARCHAR2(30); -- bug#13698799 auto create ro, parent ro
107 l_repair_history_id NUMBER;
108 l_inventory_item_name VARCHAR2(40);
109 l_repair_type_name VARCHAR2(80);
110 --
111 CURSOR C1 IS
112 SELECT CSD_REPAIRS_S1.NEXTVAL
113 FROM sys.dual;
114 --
115 CURSOR C2 IS
116 SELECT CSD_REPAIRS_S2.NEXTVAL
117 FROM sys.dual;
118 --
119 CURSOR get_draft_status_details IS
120 SELECT FS_B.flow_status_id,
121 FS_B.flow_status_code,
122 FS_LKUP.meaning flow_status,
123 FS_B.status_code
124 FROM CSD_FLOW_STATUSES_B FS_B,
125 FND_LOOKUPS FS_LKUP
126 WHERE FS_B.flow_status_code = 'D' AND
127 FS_LKUP.lookup_type = 'CSD_REPAIR_FLOW_STATUS' AND
128 FS_LKUP.lookup_code = FS_B.flow_status_code AND
129 FS_LKUP.enabled_flag = 'Y' AND
130 TRUNC(SYSDATE) BETWEEN
131 TRUNC(NVL(FS_LKUP.start_date_active, SYSDATE)) AND
132 TRUNC(NVL(FS_LKUP.end_date_active, SYSDATE));
133 --
134 --rfieldma: 5355051
135 CURSOR c_get_bus_proc_id(p_repair_type_id NUMBER) IS
136 SELECT business_process_id, res_dt_calc_point
137 FROM csd_repair_types_b b
138 WHERE repair_type_id = p_repair_type_id;
139
140 --rfieldma: 5355051
141 CURSOR c_get_severity_id (p_incident_id NUMBER) IS
142 SELECT incident_severity_id
143 FROM csd_incidents_v a
144 WHERE incident_id = p_incident_id;
145
146 -- bug#13698799 auto create ro, parent ro
147 CURSOR c_get_repair_number (p_repair_line_id NUMBER) IS
148 Select repair_number
149 from csd_repairs
150 where repair_line_id = p_repair_line_id;
151
152 Cursor c_get_item_name (p_inventory_item_id number,
153 p_organization_id number) is
154 select mtl.concatenated_segments
155 from mtl_system_items_kfv mtl
156 where mtl.inventory_item_id = p_inventory_item_id
157 and mtl.organization_id = p_organization_id;
158
159 CURSOR c_get_repair_type_name (p_repair_type_id NUMBER) IS
160 Select Name
161 from csd_repair_types_vl
162 where repair_type_id = P_Repair_Type_Id;
163 -- end bug#13698799
164
165 BEGIN
166
167 -- Standard Start of API savepoint
168 SAVEPOINT CREATE_REPAIR_ORDER_PVT;
169 -- Standard call to check for call compatibility.
170 IF NOT Fnd_Api.Compatible_API_Call
171 (l_api_version_number,
172 p_api_version_number,
173 l_api_name,
174 G_PKG_NAME)
175 THEN
176 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
177 END IF;
178
179 -- swai: bug 7657379
180 -- move msg initialization further up, since call to DEFAULT_RO_ATTRS_FROM_RULE
181 -- may add additional messages to the stack.
182 -- Initialize message list if p_init_msg_list is set to TRUE.
183 IF Fnd_Api.to_Boolean(p_init_msg_list)
184 THEN
185 Fnd_Msg_Pub.initialize;
186 END IF;
187 -- Initialize API return status to SUCCESS
188 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
189
190 -- swai: bug 7657379
191 -- Default fields in repln_rec from defauling rules before
192 -- checking required params.
193 l_REPLN_Rec := p_REPLN_Rec;
194 Csd_Repairs_Util.DEFAULT_RO_ATTRS_FROM_RULE
195 ( p_api_version => 1.0,
196 p_commit => fnd_api.g_false,
197 p_init_msg_list => fnd_api.g_false,
198 px_repln_rec => l_REPLN_Rec,
199 x_return_status => x_return_status,
200 x_msg_count => x_msg_count,
201 x_msg_data => x_msg_data);
202 -- end swai: bug 7657379
203 -- Note: From this point on, P_REPLN_REC has been replaced with l_REPLN_Rec
204
205 --
206 -- Check for required parameters
207 Csd_Repairs_Util.check_reqd_param
208 (p_param_value => l_REPLN_Rec.incident_id,
209 p_param_name => 'P_REPLN_REC.INCIDENT_ID',
210 p_api_name => l_api_name
211 );
212 Csd_Repairs_Util.check_reqd_param
213 (p_param_value => l_REPLN_Rec.inventory_item_id,
214 p_param_name => 'P_REPLN_REC.INVENTORY_ITEM_ID',
215 p_api_name => l_api_name
216 );
217
218 Csd_Repairs_Util.check_reqd_param
219 (p_param_value => l_REPLN_Rec.unit_of_measure,
220 p_param_name => 'P_REPLN_REC.UNIT_OF_MEASURE',
221 p_api_name => l_api_name
222 );
223 Csd_Repairs_Util.check_reqd_param
224 (p_param_value => l_REPLN_Rec.quantity,
225 p_param_name => 'P_REPLN_REC.QUANTITY',
226 p_api_name => l_api_name
227 );
228
229 /* R12 Flex Flow change, vkjain.
230 -- Status is no longer mandatory.
231 -- One can either pass flow_status_id
232 -- or just status.
233 CSD_REPAIRS_UTIL.check_reqd_param
234 (p_param_value => l_REPLN_Rec.status,
235 p_param_name => 'P_REPLN_REC.STATUS',
236 p_api_name => l_api_name
237 );
238 */
239
240 Csd_Repairs_Util.check_reqd_param
241 (p_param_value => l_REPLN_Rec.currency_code,
242 p_param_name => 'P_REPLN_REC.CURRENCY_CODE',
243 p_api_name => l_api_name
244 );
245
246 -- Need to check if the status_reason_code is to be validated.
247
248 Csd_Repairs_Util.check_reqd_param
249 (p_param_value => l_REPLN_Rec.approval_required_flag,
250 p_param_name => 'P_REPLN_REC.APPROVAL_REQUIRED_FLAG',
251 p_api_name => l_api_name
252 );
253 Csd_Repairs_Util.check_reqd_param
254 (p_param_value => l_REPLN_Rec.repair_type_id,
255 p_param_name => 'P_REPLN_REC.REPAIR_TYPE_ID',
256 p_api_name => l_api_name
257 );
258
259
260 -- API body
261
262 -- Validate Environment
263
264 IF G_USER_ID IS NULL
265 THEN
266 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
267 THEN
268 Fnd_Message.Set_Name('CSD', 'CSD_CANNOT_GET_PROFILE_VALUE');
269 Fnd_Message.Set_Token('PROFILE', 'USER_ID', FALSE);
270 Fnd_Msg_Pub.ADD;
271 END IF;
272 RAISE Fnd_Api.G_EXC_ERROR;
273 END IF;
274
275
276 -- Validate Repair Order Group id
277
278
279 -- Generate REPAIR_NUMBER
280
281 IF l_REPLN_Rec.repair_number = Fnd_Api.G_MISS_CHAR
282 OR l_REPLN_Rec.repair_number IS NULL THEN
283 OPEN C2;
284 FETCH C2 INTO l_REPAIR_NUMBER;
285 CLOSE C2;
286 ELSE
287 l_repair_number := l_REPLN_Rec.repair_number;
288 END IF;
289
290
291 --
292 -- Invoke validation procedures
293 -- added a new out parameter which is of type CSD_REPAIRS_UTIL.DEF_Rec_Type
294 -- this collection holds the validated/derived DFF values (only in create mode)
295 -- subhat, FP bug#7242791
296 --
297 Validate_Repairs
298 (
299 P_Api_Version_Number => 1.0,
300 p_validation_mode => Jtf_Plsql_Api.G_CREATE,
301 p_repair_line_id => p_repair_line_id,
302 P_REPLN_Rec => l_REPLN_Rec,
303 x_return_status => x_return_status,
304 x_msg_count => x_msg_count,
305 x_msg_data => x_msg_data,
306 --bug#7043215 subhat
307 x_dff_rec => x_dff_value_rec
308 );
309
310 -- Check return status from the above procedure call
311 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
312 ROLLBACK TO CREATE_REPAIR_ORDER_PVT;
313 RETURN;
314 END IF;
315
316 -- Added for R12 Flex Flow, vkjain.
317
318 -- The status will always be derived from the RT,
319 -- with one exception when we want to create an RO
320 -- in 'draft' status.
321 IF (l_REPLN_Rec.flow_status_code = 'D' OR
322 l_REPLN_Rec.status = 'D') THEN
323 -- Get the corresponding information for the 'Draft' status.
324 OPEN get_draft_status_details;
325 FETCH get_draft_status_details INTO
326 l_flow_status_id,
327 l_flow_status_code,
328 l_flow_status,
329 l_status;
330
331 CLOSE get_draft_status_details;
332
333 IF l_flow_status_id IS NULL THEN
334 -- Repair Order creation failed.
335 -- Unable to get the draft status details.
336 Fnd_Message.SET_NAME('CSD','CSD_RO_NO_DRAFT_STATUS_DTLS');
337 Fnd_Msg_Pub.ADD;
338 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
339 END IF;
340
341 ELSE
342 -- Get the start flow status from repair type
343 Csd_Repair_Types_Pvt.get_start_flow_status(x_return_status => x_return_status,
344 x_msg_count => x_msg_count,
345 x_msg_data => x_msg_data,
346 p_repair_type_id => l_REPLN_Rec.repair_type_id,
347 x_start_flow_status_id => l_flow_status_id,
348 x_start_flow_status_code => l_flow_status_code,
349 x_start_flow_status_meaning => l_flow_status,
350 x_status_code => l_status
351 );
352 IF x_return_status <> 'S' THEN
353 -- Unexpected error. Raise an exception.
354 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
355 END IF;
356 END IF;
357
358 -- For R12 Flex Flow, date closed is set to a value
359 -- if the Ro is created in Closed State. vkjain.
360 IF l_status = 'C' THEN
361 l_date_closed := SYSDATE;
362 ELSE
363 l_date_closed := l_REPLN_Rec.DATE_CLOSED;
364 END IF;
365
366
367 -- Generate REPAIR_LINE_ID
368
369 IF p_repair_line_id = Fnd_Api.G_MISS_NUM
370 OR p_repair_line_id IS NULL THEN
371 OPEN C1;
372 FETCH C1 INTO l_REPAIR_LINE_ID;
373 CLOSE C1;
374 ELSE
375 l_repair_line_id := p_repair_line_id;
376 END IF;
377
378
379 -- Check if Repair Type is Refurbish non IO
380 -- This is to fix bug 3435292 saupadhy
381 BEGIN
382 SELECT 'Y'
383 INTO l_Refurbish_Non_IO_Order
384 FROM Csd_Repair_types_b
385 WHERE Repair_type_id = l_REPLN_Rec.Repair_type_Id
386 AND repair_type_Ref = 'RF'
387 AND NVL(internal_order_flag,'N') = 'N' ;
388 l_Approval_required_flag := 'N' ;
389 l_Auto_Process_RMA := 'N';
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 l_Approval_required_flag := l_REPLN_Rec.Approval_required_flag ;
393 l_Auto_Process_RMA := l_REPLN_Rec.Auto_Process_RMA;
394 l_Refurbish_Non_IO_Order := 'N' ;
395 END;
396
397 -- rfieldma: 5355051
398 -- Default Resolve By date if there is a contract and
399 -- user want it to be derived
400 -- if resolve_by_date = null => don't default
401 -- if resolve_by_date = fnd_api.G_Miss_date => default
402 -- else, (resolve_by_date is passed in) => use the passed in value
403
404 l_resolve_by_date := l_REPLN_Rec.resolve_by_date;
405
406 IF (l_resolve_by_date = FND_API.G_MISS_DATE) THEN
407 -- only default if there is a default contract
408 IF ( l_REPLN_Rec.contract_line_id IS NOT NULL
409 AND l_REPLN_Rec.contract_line_id <> Fnd_Api.G_MISS_NUM) THEN
410 -- get business process id
411 -- subhat. Huawei changes (resolve by date).
412 OPEN c_get_bus_proc_id(l_REPLN_Rec.repair_type_id);
413 FETCH c_get_bus_proc_id
414 INTO l_business_process_id, l_resolve_by_dt_calc_at;
415 CLOSE c_get_bus_proc_id;
416
417 -- subhat, Huawei changes (resolve by date)
418 IF NVL(l_resolve_by_dt_calc_at,'RO_CREATION') = 'RO_CREATION'
419 THEN
420 -- get severity id
421 OPEN c_get_severity_id(l_REPLN_Rec.incident_id);
422 FETCH c_get_severity_id
423 INTO l_severity_id;
424 CLOSE c_get_severity_id;
425
426 -- get resolve by date
427 csd_repairs_util.get_contract_resolve_by_date(
428 p_contract_line_id => l_REPLN_Rec.contract_line_id,
429 p_bus_proc_id => l_business_process_id,
430 p_severity_id => l_severity_id,
431 p_request_date => sysdate,
432 x_return_status => x_return_status,
433 x_msg_count => x_msg_count,
434 x_msg_data => x_msg_data,
435 x_resolve_by_date => l_resolve_by_date);
436
437 IF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
438 RAISE Fnd_Api.G_EXC_ERROR ;
439 ELSIF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
440 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
441 END IF; -- end exception handling
442 ELSE
443 l_resolve_by_date := NULL;
444 END IF; -- end subhat changes.
445 END IF; -- end contract line id is not null and not g_miss_num
446 END IF; -- end resolve by date = g_miss_date
447
448 --
449 -- Invoke table handler(CSD_REPAIRS_PKG.Insert_Row)
450 --
451 Csd_Repairs_Pkg.Insert_Row(
452 px_REPAIR_LINE_ID => l_REPAIR_LINE_ID,
453 p_REQUEST_ID => G_REQUEST_ID,
454 p_PROGRAM_ID => G_PROGRAM_ID,
455 p_PROGRAM_APPLICATION_ID => G_PROG_APPL_ID,
456 p_PROGRAM_UPDATE_DATE => SYSDATE,
457 p_CREATED_BY => G_USER_ID,
458 p_CREATION_DATE => SYSDATE,
459 p_LAST_UPDATED_BY => G_USER_ID,
460 p_LAST_UPDATE_DATE => SYSDATE,
461 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
462 p_REPAIR_NUMBER => l_REPAIR_NUMBER,
463 p_INCIDENT_ID => l_REPLN_Rec.INCIDENT_ID,
464 p_INVENTORY_ITEM_ID => l_REPLN_Rec.INVENTORY_ITEM_ID,
465 p_CUSTOMER_PRODUCT_ID => l_REPLN_Rec.CUSTOMER_PRODUCT_ID,
466 p_UNIT_OF_MEASURE => l_REPLN_Rec.UNIT_OF_MEASURE,
467 p_REPAIR_TYPE_ID => l_REPLN_Rec.REPAIR_TYPE_ID,
468 p_RESOURCE_GROUP => l_REPLN_Rec.RESOURCE_GROUP,
469 p_RESOURCE_ID => l_REPLN_Rec.RESOURCE_ID,
470 p_INSTANCE_ID => l_REPLN_Rec.INSTANCE_ID,
471 p_PROJECT_ID => l_REPLN_Rec.PROJECT_ID,
472 p_TASK_ID => l_REPLN_Rec.TASK_ID,
473 p_UNIT_NUMBER => l_REPLN_Rec.UNIT_NUMBER, -- rfieldma, project integration
474 p_CONTRACT_LINE_ID => l_REPLN_Rec.CONTRACT_LINE_ID,
475 p_QUANTITY => l_REPLN_Rec.QUANTITY,
476 -- p_STATUS => l_REPLN_Rec.STATUS,
477 p_STATUS => l_status, -- Modifed for R12 Flex Flow
478 p_APPROVAL_REQUIRED_FLAG => l_Approval_Required_Flag,
479 p_DATE_CLOSED => l_date_closed,
480 p_QUANTITY_IN_WIP => l_REPLN_Rec.QUANTITY_IN_WIP,
481 p_APPROVAL_STATUS => l_REPLN_Rec.APPROVAL_STATUS,
482 p_QUANTITY_RCVD => l_REPLN_Rec.QUANTITY_RCVD,
483 p_QUANTITY_SHIPPED => l_REPLN_Rec.QUANTITY_SHIPPED,
484 p_CURRENCY_CODE => l_REPLN_Rec.CURRENCY_CODE,
485 p_DEFAULT_PO_NUM => l_REPLN_Rec.DEFAULT_PO_NUM,
486 p_SERIAL_NUMBER => l_REPLN_Rec.SERIAL_NUMBER,
487 p_PROMISE_DATE => l_REPLN_Rec.PROMISE_DATE,
488 -- subhat, bug#7242791
489 p_ATTRIBUTE_CATEGORY => x_dff_value_rec.ATTRIBUTE_CATEGORY, --p_REPLN_rec.ATTRIBUTE_CATEGORY,
490 p_ATTRIBUTE1 => x_dff_value_rec.ATTRIBUTE1, --p_REPLN_rec.ATTRIBUTE1,
491 p_ATTRIBUTE2 => x_dff_value_rec.ATTRIBUTE2,--p_REPLN_rec.ATTRIBUTE2,
492 p_ATTRIBUTE3 => x_dff_value_rec.ATTRIBUTE3,--p_REPLN_rec.ATTRIBUTE3,
493 p_ATTRIBUTE4 => x_dff_value_rec.ATTRIBUTE4,--p_REPLN_rec.ATTRIBUTE4,
494 p_ATTRIBUTE5 => x_dff_value_rec.ATTRIBUTE5,--p_REPLN_rec.ATTRIBUTE5,
495 p_ATTRIBUTE6 => x_dff_value_rec.ATTRIBUTE6,--p_REPLN_rec.ATTRIBUTE6,
496 p_ATTRIBUTE7 => x_dff_value_rec.ATTRIBUTE7,--p_REPLN_rec.ATTRIBUTE7,
497 p_ATTRIBUTE8 => x_dff_value_rec.ATTRIBUTE8,--p_REPLN_rec.ATTRIBUTE8,
498 p_ATTRIBUTE9 => x_dff_value_rec.ATTRIBUTE9,--p_REPLN_rec.ATTRIBUTE9,
499 p_ATTRIBUTE10 => x_dff_value_rec.ATTRIBUTE10,--p_REPLN_rec.ATTRIBUTE10,
500 p_ATTRIBUTE11 => x_dff_value_rec.ATTRIBUTE11,--p_REPLN_rec.ATTRIBUTE11,
501 p_ATTRIBUTE12 => x_dff_value_rec.ATTRIBUTE12,--p_REPLN_rec.ATTRIBUTE12,
502 p_ATTRIBUTE13 => x_dff_value_rec.ATTRIBUTE13,--p_REPLN_rec.ATTRIBUTE13,
503 p_ATTRIBUTE14 => x_dff_value_rec.ATTRIBUTE14,--p_REPLN_rec.ATTRIBUTE14,
504 p_ATTRIBUTE15 => x_dff_value_rec.ATTRIBUTE15,--p_REPLN_rec.ATTRIBUTE15,
505 -- end bug#7242791, subhat.
506 --bug#7497907, 12.1 FP, subhat
507 p_ATTRIBUTE16 => x_dff_value_rec.ATTRIBUTE16,
508 p_ATTRIBUTE17 => x_dff_value_rec.ATTRIBUTE17,
509 p_ATTRIBUTE18 => x_dff_value_rec.ATTRIBUTE18,
510 p_ATTRIBUTE19 => x_dff_value_rec.ATTRIBUTE19,
511 p_ATTRIBUTE20 => x_dff_value_rec.ATTRIBUTE20,
512 p_ATTRIBUTE21 => x_dff_value_rec.ATTRIBUTE21,
513 p_ATTRIBUTE22 => x_dff_value_rec.ATTRIBUTE22,
514 p_ATTRIBUTE23 => x_dff_value_rec.ATTRIBUTE23,
515 p_ATTRIBUTE24 => x_dff_value_rec.ATTRIBUTE24,
516 p_ATTRIBUTE25 => x_dff_value_rec.ATTRIBUTE25,
517 p_ATTRIBUTE26 => x_dff_value_rec.ATTRIBUTE26,
518 p_ATTRIBUTE27 => x_dff_value_rec.ATTRIBUTE27,
519 p_ATTRIBUTE28 => x_dff_value_rec.ATTRIBUTE28,
520 p_ATTRIBUTE29 => x_dff_value_rec.ATTRIBUTE29,
521 p_ATTRIBUTE30 => x_dff_value_rec.ATTRIBUTE30,
522 p_ORDER_LINE_ID => l_REPLN_Rec.ORDER_LINE_ID,
523 p_ORIGINAL_SOURCE_REFERENCE => l_REPLN_Rec.ORIGINAL_SOURCE_REFERENCE,
524 p_STATUS_REASON_CODE => l_REPLN_Rec.STATUS_REASON_CODE,
525 p_OBJECT_VERSION_NUMBER => 1, -- travi l_REPLN_Rec.OBJECT_VERSION_NUMBER,
526 p_AUTO_PROCESS_RMA => l_Auto_Process_RMA,
527 p_REPAIR_MODE => l_REPLN_Rec.REPAIR_MODE,
528 p_ITEM_REVISION => l_REPLN_Rec.ITEM_REVISION,
529 p_REPAIR_GROUP_ID => l_REPLN_Rec.REPAIR_GROUP_ID,
530 p_RO_TXN_STATUS => l_REPLN_Rec.RO_TXN_STATUS,
531 p_ORIGINAL_SOURCE_HEADER_ID => l_REPLN_Rec.ORIGINAL_SOURCE_HEADER_ID,
532 p_ORIGINAL_SOURCE_LINE_ID => l_REPLN_Rec.ORIGINAL_SOURCE_LINE_ID,
533 p_PRICE_LIST_HEADER_ID => l_REPLN_Rec.PRICE_LIST_HEADER_ID,
534 p_Supercession_Inv_Item_Id => l_REPLN_Rec.Supercession_Inv_Item_Id,
535 p_flow_status_Id => l_flow_status_Id,
536 p_Inventory_Org_Id => l_REPLN_Rec.Inventory_Org_Id,
537 p_PROBLEM_DESCRIPTION => l_REPLN_Rec.PROBLEM_DESCRIPTION, -- swai: bug 4666344
538 p_RO_PRIORITY_CODE => l_REPLN_Rec.RO_PRIORITY_CODE, -- swai: R12
539 p_RESOLVE_BY_DATE => l_resolve_by_date, -- rfieldma: 5355051
540 p_BULLETIN_CHECK_DATE => l_REPLN_Rec.BULLETIN_CHECK_DATE,
541 p_ESCALATION_CODE => l_REPLN_Rec.ESCALATION_CODE,
542 p_RO_WARRANTY_STATUS_CODE => l_REPLN_Rec.RO_WARRANTY_STATUS_CODE,
543 p_REPAIR_YIELD_QUANTITY => l_REPLN_Rec.REPAIR_YIELD_QUANTITY, --bug#6692459
544 --12.2 returns portal project, subhat
545 p_return_attribute_category => p_repln_rec.return_attribute_category,
546 p_return_attribute1 => p_repln_rec.return_attribute1,
547 p_return_attribute2 => p_repln_rec.return_attribute2,
548 p_return_attribute3 => p_repln_rec.return_attribute3,
549 p_return_attribute4 => p_repln_rec.return_attribute4,
550 p_return_attribute5 => p_repln_rec.return_attribute5,
551 p_return_attribute6 => p_repln_rec.return_attribute6,
552 p_return_attribute7 => p_repln_rec.return_attribute7,
553 p_return_attribute8 => p_repln_rec.return_attribute8,
554 p_return_attribute9 => p_repln_rec.return_attribute9,
555 p_return_attribute10 => p_repln_rec.return_attribute10,
556 p_return_attribute11 => p_repln_rec.return_attribute11,
557 p_return_attribute12 => p_repln_rec.return_attribute12,
558 p_return_attribute13 => p_repln_rec.return_attribute13,
559 p_return_attribute14 => p_repln_rec.return_attribute14,
560 p_return_attribute15 => p_repln_rec.return_attribute15,
561 p_return_attribute16 => p_repln_rec.return_attribute16,
562 p_return_attribute17 => p_repln_rec.return_attribute17,
563 p_return_attribute18 => p_repln_rec.return_attribute18,
564 p_return_attribute19 => p_repln_rec.return_attribute19,
565 p_return_attribute20 => p_repln_rec.return_attribute20,
566 p_return_attribute21 => p_repln_rec.return_attribute21,
567 p_return_attribute22 => p_repln_rec.return_attribute22,
568 p_return_attribute23 => p_repln_rec.return_attribute23,
569 p_return_attribute24 => p_repln_rec.return_attribute24,
570 p_return_attribute25 => p_repln_rec.return_attribute25,
571 p_return_attribute26 => p_repln_rec.return_attribute26,
572 p_return_attribute27 => p_repln_rec.return_attribute27,
573 p_return_attribute28 => p_repln_rec.return_attribute28,
574 p_return_attribute29 => p_repln_rec.return_attribute29,
575 p_return_attribute30 => p_repln_rec.return_attribute30,
576 p_waybill_no => p_repln_rec.waybill_no,
577 p_return_type_id => p_repln_rec.return_type_id,
578 p_parent_repair_line_id => p_repln_rec.parent_repair_line_id -- bug#13698799 auto create ro, parent ro
579 );
580
581 x_REPAIR_LINE_ID := l_REPAIR_LINE_ID;
582 x_REPAIR_NUMBER := l_REPAIR_NUMBER;
583
584 -- START bug#13698799 auto create ro, parent ro
585 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
586 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, 'CSD.PLSQL.csd_repairs_pvt.create_repair_order',
587 'Calling Repair History to log activity');
588 END IF;
589
590 IF l_REPLN_Rec.parent_repair_line_id is not null and l_REPLN_Rec.parent_repair_line_id <> FND_API.G_MISS_NUM THEN
591 OPEN c_get_repair_number (l_REPLN_Rec.parent_repair_line_id);
592 FETCH c_get_repair_number INTO l_parent_repair_number;
593 CLOSE c_get_repair_number;
594 ELSE
595 l_parent_repair_number := NULL;
596 END IF;
597
598 OPEN c_get_item_name(l_REPLN_Rec.inventory_item_id, l_REPLN_Rec.inventory_org_id);
599 FETCH c_get_item_name into l_inventory_item_name;
600 CLOSE c_get_item_name;
601
602 OPEN c_get_repair_type_name(l_REPLN_Rec.repair_type_id);
603 FETCH c_get_repair_type_name into l_repair_type_name;
604 CLOSE c_get_repair_type_name;
605
606 -- log an activity on the newly created repair order
607 Csd_To_Form_Repair_History.Validate_And_Write
608 (p_Api_Version_Number => 1.0,
609 p_init_msg_list => Fnd_Api.G_FALSE,
610 p_commit => Fnd_Api.G_FALSE,
611 p_validation_level => NULL,
612 p_action_code => 0,
613 px_REPAIR_HISTORY_ID => l_repair_history_id,
614 p_OBJECT_VERSION_NUMBER => NULL,
615 p_REQUEST_ID => NULL,
616 p_PROGRAM_ID => NULL,
617 p_PROGRAM_APPLICATION_ID => NULL,
618 p_PROGRAM_UPDATE_DATE => NULL,
619 p_CREATED_BY => Fnd_Global.USER_ID,
620 p_CREATION_DATE => SYSDATE,
621 p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
622 p_LAST_UPDATE_DATE => SYSDATE,
623 p_repair_line_id => l_repair_line_id,
624 p_EVENT_CODE => 'ROC',
625 p_EVENT_DATE => SYSDATE,
626 p_QUANTITY => l_REPLN_Rec.quantity,
627 p_PARAMC1 => l_parent_repair_number,
628 p_PARAMC2 => l_flow_status_code,
629 p_PARAMC3 => l_inventory_item_name,
630 p_PARAMC4 => l_REPLN_Rec.SERIAL_NUMBER,
631 p_PARAMC5 => l_repair_type_name,
632 p_LAST_UPDATE_LOGIN => Fnd_Global.CONC_LOGIN_ID,
633 X_Return_Status => x_return_status,
634 X_Msg_Count => x_msg_count,
635 X_Msg_Data => x_msg_data
636 );
637
638 IF x_return_status <> 'S' THEN
639 -- Unable to update repair status. Adding repair activity
640 -- process has failed.
641 Fnd_Message.Set_Name('CSD', 'CSD_CREATE_RO_ACTY_FAIL');
642 Fnd_Msg_Pub.ADD;
643 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
644 END IF;
645
646 IF l_REPLN_Rec.parent_repair_line_id is not NULL and l_REPLN_Rec.parent_repair_line_id <> FND_API.G_MISS_NUM THEN
647 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
648 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, 'CSD.PLSQL.csd_repairs_pvt.create_repair_order',
649 'Calling Repair History to log activity for parent RO');
650 END IF;
651
652 -- log an activity on the newly created repair order
653 Csd_To_Form_Repair_History.Validate_And_Write
654 (p_Api_Version_Number => 1.0,
655 p_init_msg_list => Fnd_Api.G_FALSE,
656 p_commit => Fnd_Api.G_FALSE,
657 p_validation_level => NULL,
658 p_action_code => 0,
659 px_REPAIR_HISTORY_ID => l_repair_history_id,
660 p_OBJECT_VERSION_NUMBER => NULL,
661 p_REQUEST_ID => NULL,
662 p_PROGRAM_ID => NULL,
663 p_PROGRAM_APPLICATION_ID => NULL,
664 p_PROGRAM_UPDATE_DATE => NULL,
665 p_CREATED_BY => Fnd_Global.USER_ID,
666 p_CREATION_DATE => SYSDATE,
667 p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
668 p_LAST_UPDATE_DATE => SYSDATE,
669 p_repair_line_id => l_REPLN_Rec.parent_repair_line_id,
670 p_EVENT_CODE => 'CROC',
671 p_EVENT_DATE => SYSDATE,
672 p_QUANTITY => l_REPLN_Rec.quantity,
673 p_PARAMC1 => l_repair_number,
674 p_PARAMC2 => l_flow_status_code,
675 p_PARAMC3 => l_inventory_item_name,
676 p_PARAMC4 => l_REPLN_Rec.SERIAL_NUMBER,
677 p_PARAMC5 => l_repair_type_name,
678 p_LAST_UPDATE_LOGIN => Fnd_Global.CONC_LOGIN_ID,
679 X_Return_Status => x_return_status,
680 X_Msg_Count => x_msg_count,
681 X_Msg_Data => x_msg_data
682 );
683
684 If x_return_status <> 'S' THEN
685 -- Unable to update repair status. Adding repair activity
686 -- process has failed.
687 Fnd_Message.Set_Name('CSD', 'CSD_CREATE_CHILD_RO_ACTY_FAIL');
688 Fnd_Msg_Pub.ADD;
689 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
690 END IF;
691 END IF;
692 -- END bug#13698799 auto create ro, parent ro
693
694 --
695 -- End of API body
696 --
697 -- Standard check for p_commit
698 IF Fnd_Api.to_Boolean( p_commit )
699 THEN
700 COMMIT WORK;
701 END IF;
702 -- Standard call to get message count and if count is 1, get message info.
703 Fnd_Msg_Pub.Count_And_Get
704 (p_count => x_msg_count,
705 p_data => x_msg_data
706 );
707
708 EXCEPTION
709 WHEN Fnd_Api.G_EXC_ERROR THEN
710 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
711 ( P_API_NAME => L_API_NAME
712 ,P_PKG_NAME => G_PKG_NAME
713 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
714 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
715 ,X_MSG_COUNT => X_MSG_COUNT
716 ,X_MSG_DATA => X_MSG_DATA
717 ,X_RETURN_STATUS => X_RETURN_STATUS);
718
719 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
720 ROLLBACK TO CREATE_REPAIR_ORDER_PVT;
721 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
722 (P_API_NAME => L_API_NAME
723 ,P_PKG_NAME => G_PKG_NAME
724 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
725 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
726 ,X_MSG_COUNT => X_MSG_COUNT
727 ,X_MSG_DATA => X_MSG_DATA
728 ,X_RETURN_STATUS => X_RETURN_STATUS);
729
730 WHEN OTHERS THEN
731 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
732 (P_API_NAME => L_API_NAME
733 ,P_PKG_NAME => G_PKG_NAME
734 ,P_EXCEPTION_LEVEL => Jtf_Plsql_Api.G_EXC_OTHERS
735 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
736 ,X_MSG_COUNT => X_MSG_COUNT
737 ,X_MSG_DATA => X_MSG_DATA
738 ,X_RETURN_STATUS => X_RETURN_STATUS);
739
740 END Create_Repair_Order;
741
742 PROCEDURE Update_Repair_Order(
743 P_Api_Version_Number IN NUMBER,
744 P_Init_Msg_List IN VARCHAR2 := Fnd_Api.G_FALSE,
745 P_Commit IN VARCHAR2 := Fnd_Api.G_FALSE,
746 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
747 p_REPAIR_LINE_ID IN NUMBER,
748 P_REPLN_Rec IN OUT NOCOPY Csd_Repairs_Pub.REPLN_Rec_Type,
749 X_Return_Status OUT NOCOPY VARCHAR2,
750 X_Msg_Count OUT NOCOPY NUMBER,
751 X_Msg_Data OUT NOCOPY VARCHAR2
752 )
753 IS
754
755 CURSOR C_Get_repairs(p_REPAIR_LINE_ID NUMBER) IS
756 SELECT ROWID,
757 REPAIR_LINE_ID,
758 REPAIR_NUMBER,
759 INCIDENT_ID,
760 INVENTORY_ITEM_ID,
761 CUSTOMER_PRODUCT_ID,
762 UNIT_OF_MEASURE,
763 REPAIR_TYPE_ID,
764 OWNING_ORGANIZATION_ID,
765 RESOURCE_ID,
766 PROJECT_ID,
767 TASK_ID,
768 UNIT_NUMBER, -- rfieldma, prj integration
769 CONTRACT_LINE_ID,
770 AUTO_PROCESS_RMA,
771 REPAIR_MODE,
772 OBJECT_VERSION_NUMBER,
773 ITEM_REVISION,
774 INSTANCE_ID,
775 STATUS,
776 STATUS_REASON_CODE,
777 DATE_CLOSED,
778 APPROVAL_REQUIRED_FLAG,
779 APPROVAL_STATUS,
780 SERIAL_NUMBER,
781 PROMISE_DATE,
782 ATTRIBUTE_CATEGORY,
783 ATTRIBUTE1,
784 ATTRIBUTE2,
785 ATTRIBUTE3,
786 ATTRIBUTE4,
787 ATTRIBUTE5,
788 ATTRIBUTE6,
789 ATTRIBUTE7,
790 ATTRIBUTE8,
791 ATTRIBUTE9,
792 ATTRIBUTE10,
793 ATTRIBUTE11,
794 ATTRIBUTE12,
795 ATTRIBUTE13,
796 ATTRIBUTE14,
797 ATTRIBUTE15,
798 QUANTITY,
799 QUANTITY_IN_WIP,
800 QUANTITY_RCVD,
801 QUANTITY_SHIPPED
802 -- bug#7497907, 12.1 FP, subhat
803 ,ATTRIBUTE16
804 ,ATTRIBUTE17
805 ,ATTRIBUTE18
806 ,ATTRIBUTE19
807 ,ATTRIBUTE20
808 ,ATTRIBUTE21
809 ,ATTRIBUTE22
810 ,ATTRIBUTE23
811 ,ATTRIBUTE24
812 ,ATTRIBUTE25
813 ,ATTRIBUTE26
814 ,ATTRIBUTE27
815 ,ATTRIBUTE28
816 ,ATTRIBUTE29
817 ,ATTRIBUTE30,
818 RETURN_ATTRIBUTE_CATEGORY, --12.2 returns portal project, subhat
819 RETURN_ATTRIBUTE1,
820 RETURN_ATTRIBUTE2,
821 RETURN_ATTRIBUTE3,
822 RETURN_ATTRIBUTE4,
823 RETURN_ATTRIBUTE5,
824 RETURN_ATTRIBUTE6,
825 RETURN_ATTRIBUTE7,
826 RETURN_ATTRIBUTE8,
827 RETURN_ATTRIBUTE9,
828 RETURN_ATTRIBUTE10,
829 RETURN_ATTRIBUTE11,
830 RETURN_ATTRIBUTE12,
831 RETURN_ATTRIBUTE13,
832 RETURN_ATTRIBUTE14,
833 RETURN_ATTRIBUTE15,
834 RETURN_ATTRIBUTE16,
835 RETURN_ATTRIBUTE17,
836 RETURN_ATTRIBUTE18,
837 RETURN_ATTRIBUTE19,
838 RETURN_ATTRIBUTE20,
839 RETURN_ATTRIBUTE21,
840 RETURN_ATTRIBUTE22,
841 RETURN_ATTRIBUTE23,
842 RETURN_ATTRIBUTE24,
843 RETURN_ATTRIBUTE25,
844 RETURN_ATTRIBUTE26,
845 RETURN_ATTRIBUTE27,
846 RETURN_ATTRIBUTE28,
847 RETURN_ATTRIBUTE29,
848 RETURN_ATTRIBUTE30,
849 waybill_no,
850 return_type_id,
851 PARENT_REPAIR_LINE_ID -- bug#13698799 auto create ro, parent ro
852 FROM CSD_REPAIRS
853 WHERE REPAIR_LINE_ID = p_REPAIR_LINE_ID
854 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
855
856 l_api_name CONSTANT VARCHAR2(30) := 'Update_Repair_Order';
857 l_api_version_number CONSTANT NUMBER := 1.0;
858 l_OLD_REPLN_rec Csd_Repairs_Pub.REPLN_Rec_Type;
859 l_NEW_REPLN_rec Csd_Repairs_Pub.REPLN_Rec_Type := P_REPLN_Rec;
860 l_rowid ROWID;
861 l_repair_line_id NUMBER;
862 l_OBJECT_VERSION_NUMBER NUMBER;
863
864 -- bug#7242791, 12.1 FP, subhat
865 -- new out parameter for validate_repairs API.
866 x_dff_value_rec csd_repairs_util.def_rec_type;
867
868 BEGIN
869
870 -- Standard Start of API savepoint
871 SAVEPOINT UPDATE_REPAIR_ORDER_PVT;
872 -- Standard call to check for call compatibility.
873 IF NOT Fnd_Api.Compatible_API_Call
874 (l_api_version_number,
875 p_api_version_number,
876 l_api_name,
877 G_PKG_NAME)
878 THEN
879 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
880 END IF;
881 -- Check for required parameters
882 Csd_Repairs_Util.check_reqd_param
883 (p_param_value => p_repair_line_id,
884 p_param_name => 'P_REPAIR_LINE_ID',
885 p_api_name => l_api_name
886 );
887
888 -- Initialize message list if p_init_msg_list is set to TRUE.
889 IF Fnd_Api.to_Boolean(p_init_msg_list)
890 THEN
891 Fnd_Msg_Pub.initialize;
892 END IF;
893 -- Initialize API return status to SUCCESS
894 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
895
896 -- Api body
897
898 -- Validate Environment
899
900 IF G_USER_ID IS NULL
901 THEN
902 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
903 THEN
904 Fnd_Message.Set_Name('CSD', 'CSD_CANNOT_GET_PROFILE_VALUE');
905 Fnd_Message.Set_Token('PROFILE', 'USER_ID', FALSE);
906 Fnd_Msg_Pub.ADD;
907 END IF;
908 RAISE Fnd_Api.G_EXC_ERROR;
909 END IF;
910
911 OPEN C_Get_repairs(p_REPAIR_LINE_ID);
912 FETCH C_Get_repairs INTO
913 l_rowid,
914 l_REPAIR_LINE_ID,
915 l_OLD_REPLN_rec.REPAIR_NUMBER,
916 l_OLD_REPLN_rec.INCIDENT_ID,
917 l_OLD_REPLN_rec.INVENTORY_ITEM_ID,
918 l_OLD_REPLN_rec.CUSTOMER_PRODUCT_ID,
919 l_OLD_REPLN_rec.UNIT_OF_MEASURE,
920 l_OLD_REPLN_rec.REPAIR_TYPE_ID,
921 l_OLD_REPLN_rec.RESOURCE_GROUP,
922 l_OLD_REPLN_rec.RESOURCE_ID,
923 l_OLD_REPLN_rec.PROJECT_ID,
924 l_OLD_REPLN_rec.TASK_ID,
925 l_OLD_REPLN_rec.UNIT_NUMBER, -- rfieldma, project integration
926 l_OLD_REPLN_rec.CONTRACT_LINE_ID,
927 l_OLD_REPLN_rec.AUTO_PROCESS_RMA,
928 l_OLD_REPLN_rec.REPAIR_MODE,
929 l_OLD_REPLN_rec.OBJECT_VERSION_NUMBER,
930 l_OLD_REPLN_rec.ITEM_REVISION,
931 l_OLD_REPLN_rec.INSTANCE_ID,
932 l_OLD_REPLN_rec.STATUS,
933 l_OLD_REPLN_rec.STATUS_REASON_CODE,
934 l_OLD_REPLN_rec.DATE_CLOSED,
935 l_OLD_REPLN_rec.APPROVAL_REQUIRED_FLAG,
936 l_OLD_REPLN_rec.APPROVAL_STATUS,
937 l_OLD_REPLN_rec.SERIAL_NUMBER,
938 l_OLD_REPLN_rec.PROMISE_DATE,
939 l_OLD_REPLN_rec.ATTRIBUTE_CATEGORY,
940 l_OLD_REPLN_rec.ATTRIBUTE1,
941 l_OLD_REPLN_rec.ATTRIBUTE2,
942 l_OLD_REPLN_rec.ATTRIBUTE3,
943 l_OLD_REPLN_rec.ATTRIBUTE4,
944 l_OLD_REPLN_rec.ATTRIBUTE5,
945 l_OLD_REPLN_rec.ATTRIBUTE6,
946 l_OLD_REPLN_rec.ATTRIBUTE7,
947 l_OLD_REPLN_rec.ATTRIBUTE8,
948 l_OLD_REPLN_rec.ATTRIBUTE9,
949 l_OLD_REPLN_rec.ATTRIBUTE10,
950 l_OLD_REPLN_rec.ATTRIBUTE11,
951 l_OLD_REPLN_rec.ATTRIBUTE12,
952 l_OLD_REPLN_rec.ATTRIBUTE13,
953 l_OLD_REPLN_rec.ATTRIBUTE14,
954 l_OLD_REPLN_rec.ATTRIBUTE15,
955 l_OLD_REPLN_rec.QUANTITY,
956 l_OLD_REPLN_rec.QUANTITY_IN_WIP,
957 l_OLD_REPLN_rec.QUANTITY_RCVD,
958 l_OLD_REPLN_rec.QUANTITY_SHIPPED,
959 -- bug#7497907, 12.1 FP, subhat
960 l_OLD_REPLN_rec.ATTRIBUTE16,
961 l_OLD_REPLN_rec.ATTRIBUTE17,
962 l_OLD_REPLN_rec.ATTRIBUTE18,
963 l_OLD_REPLN_rec.ATTRIBUTE19,
964 l_OLD_REPLN_rec.ATTRIBUTE20,
965 l_OLD_REPLN_rec.ATTRIBUTE21,
966 l_OLD_REPLN_rec.ATTRIBUTE22,
967 l_OLD_REPLN_rec.ATTRIBUTE23,
968 l_OLD_REPLN_rec.ATTRIBUTE24,
969 l_OLD_REPLN_rec.ATTRIBUTE25,
970 l_OLD_REPLN_rec.ATTRIBUTE26,
971 l_OLD_REPLN_rec.ATTRIBUTE27,
972 l_OLD_REPLN_rec.ATTRIBUTE28,
973 l_OLD_REPLN_rec.ATTRIBUTE29,
974 l_OLD_REPLN_rec.ATTRIBUTE30,
975 l_OLD_REPLN_rec.RETURN_ATTRIBUTE_CATEGORY, --12.2 returns portal project, subhat
976 l_OLD_REPLN_rec.RETURN_ATTRIBUTE1,
977 l_OLD_REPLN_rec.RETURN_ATTRIBUTE2,
978 l_OLD_REPLN_rec.RETURN_ATTRIBUTE3,
979 l_OLD_REPLN_rec.RETURN_ATTRIBUTE4,
980 l_OLD_REPLN_rec.RETURN_ATTRIBUTE5,
981 l_OLD_REPLN_rec.RETURN_ATTRIBUTE6,
982 l_OLD_REPLN_rec.RETURN_ATTRIBUTE7,
983 l_OLD_REPLN_rec.RETURN_ATTRIBUTE8,
984 l_OLD_REPLN_rec.RETURN_ATTRIBUTE9,
985 l_OLD_REPLN_rec.RETURN_ATTRIBUTE10,
986 l_OLD_REPLN_rec.RETURN_ATTRIBUTE11,
987 l_OLD_REPLN_rec.RETURN_ATTRIBUTE12,
988 l_OLD_REPLN_rec.RETURN_ATTRIBUTE13,
989 l_OLD_REPLN_rec.RETURN_ATTRIBUTE14,
990 l_OLD_REPLN_rec.RETURN_ATTRIBUTE15,
991 l_OLD_REPLN_rec.RETURN_ATTRIBUTE16,
992 l_OLD_REPLN_rec.RETURN_ATTRIBUTE17,
993 l_OLD_REPLN_rec.RETURN_ATTRIBUTE18,
994 l_OLD_REPLN_rec.RETURN_ATTRIBUTE19,
995 l_OLD_REPLN_rec.RETURN_ATTRIBUTE20,
996 l_OLD_REPLN_rec.RETURN_ATTRIBUTE21,
997 l_OLD_REPLN_rec.RETURN_ATTRIBUTE22,
998 l_OLD_REPLN_rec.RETURN_ATTRIBUTE23,
999 l_OLD_REPLN_rec.RETURN_ATTRIBUTE24,
1000 l_OLD_REPLN_rec.RETURN_ATTRIBUTE25,
1001 l_OLD_REPLN_rec.RETURN_ATTRIBUTE26,
1002 l_OLD_REPLN_rec.RETURN_ATTRIBUTE27,
1003 l_OLD_REPLN_rec.RETURN_ATTRIBUTE28,
1004 l_OLD_REPLN_rec.RETURN_ATTRIBUTE29,
1005 l_OLD_REPLN_rec.RETURN_ATTRIBUTE30,
1006 l_OLD_REPLN_rec.waybill_no,
1007 l_OLD_REPLN_rec.return_type_id,
1008 l_OLD_REPLN_rec.parent_repair_line_id -- bug#13698799 auto create ro, parent ro
1009 ;
1010
1011 IF (C_Get_repairs%NOTFOUND) THEN
1012 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1013 Fnd_Message.Set_Name('CSD', 'CSD_API_MISSING_UPDATE_TARGET');
1014 Fnd_Message.Set_Token ('INFO', 'Repairs', FALSE);
1015 Fnd_Msg_Pub.ADD;
1016 END IF;
1017 CLOSE C_Get_repairs;
1018 RAISE Fnd_Api.G_EXC_ERROR;
1019 END IF;
1020 CLOSE C_Get_repairs;
1021
1022 -- bug#12884352
1023 -- should use csd_repairs_pub.update_repair_type to update repair type, not this function
1024 IF P_REPLN_rec.repair_type_id is not NULL and P_REPLN_rec.repair_type_id <> Fnd_Api.G_MISS_NUM and l_OLD_REPLN_rec.repair_type_id <> P_REPLN_Rec.repair_type_id THEN
1025 IF Fnd_Msg_Pub.Check_Msg_Level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1026 Fnd_Message.Set_Name('CSD', 'CSD_UPDATE_REPAIR_TYPE');
1027 Fnd_Msg_Pub.ADD;
1028 END IF;
1029 RAISE Fnd_Api.G_EXC_ERROR;
1030 END IF;
1031
1032 /*
1033 if l_OLD_REPLN_Rec.status = 'C' then
1034 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1035 FND_MESSAGE.Set_Name('CSD', 'CSD_RO_CLOSED');
1036 FND_MESSAGE.Set_Token ('REP_NUM', l_OLD_REPLN_Rec.repair_number);
1037 FND_MSG_PUB.Add;
1038 END IF;
1039 raise FND_API.G_EXC_ERROR;
1040 end if;
1041 */
1042
1043 -- Invoke validation procedures
1044 -- bug#7242791, 12.1 FP, subhat
1045 -- new out DFF rec is added. Currently this rec will not be used in
1046 -- update API.
1047
1048 Validate_Repairs
1049 (
1050 P_Api_Version_Number => 1.0,
1051 p_validation_mode => Jtf_Plsql_Api.G_UPDATE,
1052 p_repair_line_id => p_repair_line_id,
1053 P_REPLN_Rec => P_REPLN_Rec,
1054 P_OLD_REPLN_Rec => l_OLD_REPLN_Rec,
1055 x_return_status => x_return_status,
1056 x_msg_count => x_msg_count,
1057 x_msg_data => x_msg_data,
1058 x_dff_rec => x_dff_value_rec
1059 );
1060 --
1061 -- Check return status from the above procedure call
1062 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
1063 ROLLBACK TO UPDATE_REPAIR_ORDER_PVT;
1064 RETURN;
1065 END IF;
1066
1067 l_OBJECT_VERSION_NUMBER := p_REPLN_rec.OBJECT_VERSION_NUMBER + 1;
1068
1069 --
1070 -- Invoke table handler(CSD_REPAIRS_PKG.Update_Row)
1071 --
1072 --
1073 Csd_Repairs_Pkg.Update_Row(
1074 p_REPAIR_LINE_ID => p_REPAIR_LINE_ID,
1075 p_REQUEST_ID => G_REQUEST_ID,
1076 p_PROGRAM_ID => G_PROGRAM_ID,
1077 p_PROGRAM_APPLICATION_ID => G_PROG_APPL_ID,
1078 p_PROGRAM_UPDATE_DATE => SYSDATE,
1079 p_CREATED_BY => G_USER_ID,
1080 p_CREATION_DATE => Fnd_Api.G_MISS_DATE, -- swai ADS bug 3063922, changed from sysdate
1081 p_LAST_UPDATED_BY => G_USER_ID,
1082 p_LAST_UPDATE_DATE => SYSDATE,
1083 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
1084 p_REPAIR_NUMBER => p_REPLN_rec.REPAIR_NUMBER,
1085 p_INCIDENT_ID => p_REPLN_rec.INCIDENT_ID,
1086 p_INVENTORY_ITEM_ID => p_REPLN_rec.INVENTORY_ITEM_ID,
1087 p_CUSTOMER_PRODUCT_ID => p_REPLN_rec.CUSTOMER_PRODUCT_ID,
1088 p_UNIT_OF_MEASURE => p_REPLN_rec.UNIT_OF_MEASURE,
1089 p_REPAIR_TYPE_ID => p_REPLN_rec.REPAIR_TYPE_ID,
1090 p_RESOURCE_GROUP => p_REPLN_rec.RESOURCE_GROUP,
1091 p_RESOURCE_ID => p_REPLN_rec.RESOURCE_ID,
1092 p_INSTANCE_ID => p_REPLN_rec.INSTANCE_ID,
1093 p_PROJECT_ID => p_REPLN_rec.PROJECT_ID,
1094 p_TASK_ID => p_REPLN_rec.TASK_ID,
1095 p_UNIT_NUMBER => p_REPLN_rec.UNIT_NUMBER, -- rfieldma, project integration
1096 p_CONTRACT_LINE_ID => p_REPLN_rec.CONTRACT_LINE_ID,
1097 p_QUANTITY => p_REPLN_rec.QUANTITY,
1098 p_STATUS => p_REPLN_rec.STATUS,
1099 p_APPROVAL_REQUIRED_FLAG => p_REPLN_rec.APPROVAL_REQUIRED_FLAG,
1100 p_DATE_CLOSED => p_REPLN_rec.DATE_CLOSED,
1101 p_QUANTITY_IN_WIP => p_REPLN_rec.QUANTITY_IN_WIP,
1102 p_APPROVAL_STATUS => p_REPLN_rec.APPROVAL_STATUS,
1103 p_QUANTITY_RCVD => p_REPLN_rec.QUANTITY_RCVD,
1104 p_QUANTITY_SHIPPED => p_REPLN_rec.QUANTITY_SHIPPED,
1105 p_CURRENCY_CODE => p_REPLN_rec.CURRENCY_CODE,
1106 p_DEFAULT_PO_NUM => p_REPLN_rec.DEFAULT_PO_NUM,
1107 p_SERIAL_NUMBER => p_REPLN_rec.SERIAL_NUMBER,
1108 p_PROMISE_DATE => p_REPLN_rec.PROMISE_DATE,
1109 p_ATTRIBUTE_CATEGORY => p_REPLN_rec.ATTRIBUTE_CATEGORY,
1110 p_ATTRIBUTE1 => p_REPLN_rec.ATTRIBUTE1,
1111 p_ATTRIBUTE2 => p_REPLN_rec.ATTRIBUTE2,
1112 p_ATTRIBUTE3 => p_REPLN_rec.ATTRIBUTE3,
1113 p_ATTRIBUTE4 => p_REPLN_rec.ATTRIBUTE4,
1114 p_ATTRIBUTE5 => p_REPLN_rec.ATTRIBUTE5,
1115 p_ATTRIBUTE6 => p_REPLN_rec.ATTRIBUTE6,
1116 p_ATTRIBUTE7 => p_REPLN_rec.ATTRIBUTE7,
1117 p_ATTRIBUTE8 => p_REPLN_rec.ATTRIBUTE8,
1118 p_ATTRIBUTE9 => p_REPLN_rec.ATTRIBUTE9,
1119 p_ATTRIBUTE10 => p_REPLN_rec.ATTRIBUTE10,
1120 p_ATTRIBUTE11 => p_REPLN_rec.ATTRIBUTE11,
1121 p_ATTRIBUTE12 => p_REPLN_rec.ATTRIBUTE12,
1122 p_ATTRIBUTE13 => p_REPLN_rec.ATTRIBUTE13,
1123 p_ATTRIBUTE14 => p_REPLN_rec.ATTRIBUTE14,
1124 p_ATTRIBUTE15 => p_REPLN_rec.ATTRIBUTE15,
1125 -- bug#7497907, 12.1 FP, subhat
1126 p_ATTRIBUTE16 => p_REPLN_rec.ATTRIBUTE16,
1127 p_ATTRIBUTE17 => p_REPLN_rec.ATTRIBUTE17,
1128 p_ATTRIBUTE18 => p_REPLN_rec.ATTRIBUTE18,
1129 p_ATTRIBUTE19 => p_REPLN_rec.ATTRIBUTE19,
1130 p_ATTRIBUTE20 => p_REPLN_rec.ATTRIBUTE20,
1131 p_ATTRIBUTE21 => p_REPLN_rec.ATTRIBUTE21,
1132 p_ATTRIBUTE22 => p_REPLN_rec.ATTRIBUTE22,
1133 p_ATTRIBUTE23 => p_REPLN_rec.ATTRIBUTE23,
1134 p_ATTRIBUTE24 => p_REPLN_rec.ATTRIBUTE24,
1135 p_ATTRIBUTE25 => p_REPLN_rec.ATTRIBUTE25,
1136 p_ATTRIBUTE26 => p_REPLN_rec.ATTRIBUTE26,
1137 p_ATTRIBUTE27 => p_REPLN_rec.ATTRIBUTE27,
1138 p_ATTRIBUTE28 => p_REPLN_rec.ATTRIBUTE28,
1139 p_ATTRIBUTE29 => p_REPLN_rec.ATTRIBUTE29,
1140 p_ATTRIBUTE30 => p_REPLN_rec.ATTRIBUTE30,
1141 p_ORDER_LINE_ID => p_REPLN_rec.ORDER_LINE_ID,
1142 p_ORIGINAL_SOURCE_REFERENCE => p_REPLN_rec.ORIGINAL_SOURCE_REFERENCE,
1143 p_STATUS_REASON_CODE => p_REPLN_rec.STATUS_REASON_CODE,
1144 p_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER,
1145 p_AUTO_PROCESS_RMA => p_REPLN_rec.AUTO_PROCESS_RMA,
1146 p_REPAIR_MODE => p_REPLN_rec.REPAIR_MODE,
1147 p_ITEM_REVISION => p_REPLN_rec.ITEM_REVISION,
1148 p_REPAIR_GROUP_ID => p_REPLN_rec.REPAIR_GROUP_ID,
1149 p_RO_TXN_STATUS => p_REPLN_rec.RO_TXN_STATUS,
1150 p_ORIGINAL_SOURCE_HEADER_ID => p_REPLN_rec.ORIGINAL_SOURCE_HEADER_ID,
1151 p_ORIGINAL_SOURCE_LINE_ID => p_REPLN_rec.ORIGINAL_SOURCE_LINE_ID,
1152 p_PRICE_LIST_HEADER_ID => p_REPLN_rec.PRICE_LIST_HEADER_ID,
1153 p_PROBLEM_DESCRIPTION => p_REPLN_rec.PROBLEM_DESCRIPTION, -- swai: bug 4666344
1154 p_RO_PRIORITY_CODE => p_Repln_Rec.RO_PRIORITY_CODE, -- swai: R12
1155 p_RESOLVE_BY_DATE => p_Repln_rec.RESOLVE_BY_DATE, -- rfieldma: 5355051
1156 p_BULLETIN_CHECK_DATE => p_Repln_rec.BULLETIN_CHECK_DATE,
1157 p_ESCALATION_CODE => p_Repln_rec.ESCALATION_CODE,
1158 p_RO_WARRANTY_STATUS_CODE => p_Repln_rec.RO_WARRANTY_STATUS_CODE,
1159 -- 12.2 development.
1160 p_return_attribute_category => p_repln_rec.return_attribute_category, --12.2 returns portal project, subhat
1161 p_return_attribute1 => p_repln_rec.return_attribute1,
1162 p_return_attribute2 => p_repln_rec.return_attribute2,
1163 p_return_attribute3 => p_repln_rec.return_attribute3,
1164 p_return_attribute4 => p_repln_rec.return_attribute4,
1165 p_return_attribute5 => p_repln_rec.return_attribute5,
1166 p_return_attribute6 => p_repln_rec.return_attribute6,
1167 p_return_attribute7 => p_repln_rec.return_attribute7,
1168 p_return_attribute8 => p_repln_rec.return_attribute8,
1169 p_return_attribute9 => p_repln_rec.return_attribute9,
1170 p_return_attribute10 => p_repln_rec.return_attribute10,
1171 p_return_attribute11 => p_repln_rec.return_attribute11,
1172 p_return_attribute12 => p_repln_rec.return_attribute12,
1173 p_return_attribute13 => p_repln_rec.return_attribute13,
1174 p_return_attribute14 => p_repln_rec.return_attribute14,
1175 p_return_attribute15 => p_repln_rec.return_attribute15,
1176 p_return_attribute16 => p_repln_rec.return_attribute16,
1177 p_return_attribute17 => p_repln_rec.return_attribute17,
1178 p_return_attribute18 => p_repln_rec.return_attribute18,
1179 p_return_attribute19 => p_repln_rec.return_attribute19,
1180 p_return_attribute20 => p_repln_rec.return_attribute20,
1181 p_return_attribute21 => p_repln_rec.return_attribute21,
1182 p_return_attribute22 => p_repln_rec.return_attribute22,
1183 p_return_attribute23 => p_repln_rec.return_attribute23,
1184 p_return_attribute24 => p_repln_rec.return_attribute24,
1185 p_return_attribute25 => p_repln_rec.return_attribute25,
1186 p_return_attribute26 => p_repln_rec.return_attribute26,
1187 p_return_attribute27 => p_repln_rec.return_attribute27,
1188 p_return_attribute28 => p_repln_rec.return_attribute28,
1189 p_return_attribute29 => p_repln_rec.return_attribute29,
1190 p_return_attribute30 => p_repln_rec.return_attribute30,
1191 p_waybill_no => p_repln_rec.waybill_no,
1192 p_return_type_id => p_repln_rec.return_type_id,
1193 p_parent_repair_line_id => p_repln_rec.parent_repair_line_id -- bug#13698799 auto create ro, parent ro
1194 );
1195
1196
1197 p_REPLN_rec.object_version_number := l_object_version_number;
1198
1199 --
1200 -- End of API body.
1201 --
1202 -- Standard check for p_commit
1203 IF Fnd_Api.to_Boolean(p_commit) THEN
1204 COMMIT WORK;
1205 END IF;
1206 -- Standard call to get message count and if count is 1, get message info.
1207 Fnd_Msg_Pub.Count_And_Get
1208 (p_count => x_msg_count,
1209 p_data => x_msg_data
1210 );
1211
1212 EXCEPTION
1213 WHEN Fnd_Api.G_EXC_ERROR THEN
1214 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
1215 (P_API_NAME => L_API_NAME
1216 ,P_PKG_NAME => G_PKG_NAME
1217 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
1218 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
1219 ,X_MSG_COUNT => X_MSG_COUNT
1220 ,X_MSG_DATA => X_MSG_DATA
1221 ,X_RETURN_STATUS => X_RETURN_STATUS);
1222
1223 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1224 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
1225 (P_API_NAME => L_API_NAME
1226 ,P_PKG_NAME => G_PKG_NAME
1227 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
1228 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
1229 ,X_MSG_COUNT => X_MSG_COUNT
1230 ,X_MSG_DATA => X_MSG_DATA
1231 ,X_RETURN_STATUS => X_RETURN_STATUS);
1232
1233 WHEN OTHERS THEN
1234 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
1235 (P_API_NAME => L_API_NAME
1236 ,P_PKG_NAME => G_PKG_NAME
1237 ,P_EXCEPTION_LEVEL => Jtf_Plsql_Api.G_EXC_OTHERS
1238 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
1239 ,X_MSG_COUNT => X_MSG_COUNT
1240 ,X_MSG_DATA => X_MSG_DATA
1241 ,X_RETURN_STATUS => X_RETURN_STATUS);
1242
1243 END Update_Repair_Order;
1244
1245 PROCEDURE Delete_Repair_Order(
1246 P_Api_Version_Number IN NUMBER,
1247 P_Init_Msg_List IN VARCHAR2 := Fnd_Api.G_FALSE,
1248 P_Commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1249 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1250 p_REPAIR_LINE_ID IN NUMBER,
1251 X_Return_Status OUT NOCOPY VARCHAR2,
1252 X_Msg_Count OUT NOCOPY NUMBER,
1253 X_Msg_Data OUT NOCOPY VARCHAR2
1254 )
1255 IS
1256
1257 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Repair_Order';
1258 l_api_version_number CONSTANT NUMBER := 1.0;
1259
1260 BEGIN
1261 --
1262 -- Standard Start of API savepoint
1263 SAVEPOINT DELETE_REPAIR_ORDER_PVT;
1264 -- Standard call to check for call compatibility.
1265 IF NOT Fnd_Api.Compatible_API_Call
1266 (l_api_version_number,
1267 p_api_version_number,
1268 l_api_name,
1269 G_PKG_NAME)
1270 THEN
1271 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1272 END IF;
1273 -- Initialize message list if p_init_msg_list is set to TRUE.
1274 IF Fnd_Api.to_Boolean(p_init_msg_list) THEN
1275 Fnd_Msg_Pub.initialize;
1276 END IF;
1277 -- Initialize API return status to SUCCESS
1278 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1279 --
1280 -- Api body
1281 --
1282 -- Invoke table handler(CSD_REPAIRS_PKG.Delete_Row)
1283 Csd_Repairs_Pkg.Delete_Row(
1284 p_REPAIR_LINE_ID => p_REPAIR_LINE_ID);
1285
1286 -- End of API body
1287
1288 -- Standard check for p_commit
1289 IF Fnd_Api.to_Boolean(p_commit) THEN
1290 COMMIT WORK;
1291 END IF;
1292 -- Standard call to get message count and if count is 1, get message info.
1293 Fnd_Msg_Pub.Count_And_Get
1294 (p_count => x_msg_count,
1295 p_data => x_msg_data
1296 );
1297
1298 EXCEPTION
1299 WHEN Fnd_Api.G_EXC_ERROR THEN
1300 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
1301 (P_API_NAME => L_API_NAME
1302 ,P_PKG_NAME => G_PKG_NAME
1303 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
1304 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
1305 ,X_MSG_COUNT => X_MSG_COUNT
1306 ,X_MSG_DATA => X_MSG_DATA
1307 ,X_RETURN_STATUS => X_RETURN_STATUS);
1308
1309 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1310 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
1311 (P_API_NAME => L_API_NAME
1312 ,P_PKG_NAME => G_PKG_NAME
1313 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
1314 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
1315 ,X_MSG_COUNT => X_MSG_COUNT
1316 ,X_MSG_DATA => X_MSG_DATA
1317 ,X_RETURN_STATUS => X_RETURN_STATUS);
1318
1319 WHEN OTHERS THEN
1320 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
1321 (P_API_NAME => L_API_NAME
1322 ,P_PKG_NAME => G_PKG_NAME
1323 ,P_EXCEPTION_LEVEL => Jtf_Plsql_Api.G_EXC_OTHERS
1324 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
1325 ,X_MSG_COUNT => X_MSG_COUNT
1326 ,X_MSG_DATA => X_MSG_DATA
1327 ,X_RETURN_STATUS => X_RETURN_STATUS);
1328
1329 END Delete_Repair_Order;
1330
1331 --
1332 -- Item-level validation procedures
1333 --
1334 PROCEDURE Validate_REPAIR_LINE_ID
1335 (
1336 P_REPAIR_LINE_ID IN NUMBER,
1337 p_validation_mode IN VARCHAR2,
1338 x_return_status OUT NOCOPY VARCHAR2
1339 )
1340 IS
1341
1342 CURSOR c1 IS
1343 SELECT 'X'
1344 FROM CSD_REPAIRS dra
1345 WHERE dra.repair_line_id = p_repair_line_id;
1346
1347 l_dummy VARCHAR2(1);
1348 l_valid VARCHAR2(1) := 'Y';
1349
1350 BEGIN
1351
1352 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1353
1354 IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
1355 RETURN;
1356 END IF;
1357
1358 IF p_repair_line_id = Fnd_Api.G_MISS_NUM
1359 OR p_repair_line_id IS NULL THEN
1360 RETURN;
1361 END IF;
1362
1363 OPEN c1;
1364 FETCH c1 INTO l_dummy;
1365 IF c1%FOUND THEN
1366 l_valid := 'N';
1367 END IF;
1368 CLOSE c1;
1369
1370 IF l_valid = 'N' THEN
1371 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1372 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1373 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_REPLN_ID');
1374 Fnd_Msg_Pub.ADD;
1375 END IF;
1376 END IF;
1377
1378 END Validate_REPAIR_LINE_ID;
1379
1380
1381 PROCEDURE Validate_REPAIR_NUMBER
1382 (
1383 P_REPAIR_NUMBER IN VARCHAR2,
1384 P_OLD_REPAIR_NUMBER IN VARCHAR2,
1385 p_validation_mode IN VARCHAR2,
1386 x_return_status OUT NOCOPY VARCHAR2
1387 )
1388 IS
1389
1390 CURSOR c1 IS
1391 SELECT 'X'
1392 FROM CSD_REPAIRS dra
1393 WHERE dra.repair_number = p_repair_number;
1394
1395 l_dummy VARCHAR2(1);
1396 l_valid VARCHAR2(1) := 'Y';
1397
1398 BEGIN
1399
1400 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1401
1402 IF p_validation_mode = Jtf_Plsql_Api.G_CREATE
1403 AND (p_repair_number = Fnd_Api.G_MISS_CHAR
1404 OR p_repair_number IS NULL) THEN
1405 RETURN;
1406 END IF;
1407
1408 IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
1409 IF p_repair_number IS NULL THEN
1410 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1411 Fnd_Message.SET_NAME('CSD', 'CSD_API_NULL_PARAM');
1412 Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
1413 Fnd_Message.SET_TOKEN('NULL_PARAM', 'REPAIR_NUMBER');
1414 Fnd_Msg_Pub.ADD;
1415 RETURN;
1416 ELSIF p_repair_number = p_old_repair_number
1417 OR p_repair_number = Fnd_Api.G_MISS_CHAR THEN
1418 RETURN;
1419 END IF;
1420 END IF;
1421
1422 OPEN c1;
1423 FETCH c1 INTO l_dummy;
1424 IF c1%FOUND THEN
1425 l_valid := 'N';
1426 END IF;
1427 CLOSE c1;
1428
1429 IF l_valid = 'N' THEN
1430 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1431 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1432 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_REPLN_NUMBER');
1433 Fnd_Msg_Pub.ADD;
1434 END IF;
1435 END IF;
1436
1437 END Validate_REPAIR_NUMBER;
1438
1439
1440 PROCEDURE Validate_INCIDENT_ID
1441 (
1442 P_INCIDENT_ID IN NUMBER,
1443 p_OLD_INCIDENT_ID IN NUMBER,
1444 p_validation_mode IN VARCHAR2,
1445 x_return_status OUT NOCOPY VARCHAR2
1446 )
1447 IS
1448
1449 CURSOR c1 IS
1450 SELECT 'X'
1451 FROM cs_incidents_all_b sr
1452 WHERE sr.incident_id = p_incident_id; -- need to add more condition
1453
1454 l_dummy VARCHAR2(1);
1455 l_valid VARCHAR2(1) := 'Y';
1456
1457 BEGIN
1458
1459 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1460
1461 IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
1462 IF p_incident_id IS NULL THEN
1463 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1464 Fnd_Message.SET_NAME('CSD', 'CSD_API_NULL_PARAM');
1465 Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
1466 Fnd_Message.SET_TOKEN('NULL_PARAM', 'INCIDENT_ID');
1467 Fnd_Msg_Pub.ADD;
1468 RETURN;
1469 ELSIF p_incident_id = p_old_incident_id
1470 OR p_incident_id = Fnd_Api.G_MISS_NUM THEN
1471 RETURN;
1472 END IF;
1473 END IF;
1474
1475 OPEN c1;
1476 FETCH c1 INTO l_dummy;
1477 IF c1%NOTFOUND THEN
1478 l_valid := 'N';
1479 END IF;
1480 CLOSE c1;
1481
1482 IF l_valid = 'N' THEN
1483 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1484 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1485 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_INCIDENT_ID');
1486 Fnd_Msg_Pub.ADD;
1487 END IF;
1488 END IF;
1489
1490 END Validate_INCIDENT_ID;
1491
1492
1493 PROCEDURE Validate_INVENTORY_ITEM_ID
1494 (
1495 p_INVENTORY_ITEM_ID IN NUMBER,
1496 p_OLD_INVENTORY_ITEM_ID IN NUMBER,
1497 p_validation_mode IN VARCHAR2,
1498 x_return_status OUT NOCOPY VARCHAR2
1499 )
1500 IS
1501
1502 CURSOR c1 IS
1503 SELECT 'X'
1504 FROM mtl_system_items_b mtl
1505 WHERE mtl.inventory_item_id = p_inventory_item_id
1506 AND mtl.organization_id = Cs_Std.get_item_valdn_orgzn_id;
1507 -- swai: forward port bug 2870951
1508 -- AND mtl.serviceable_product_flag = 'Y';
1509
1510 l_dummy VARCHAR2(1);
1511 l_valid VARCHAR2(1) := 'Y';
1512
1513 BEGIN
1514
1515 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1516
1517 IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
1518 IF p_inventory_item_id IS NULL THEN
1519 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1520 Fnd_Message.SET_NAME('CSD', 'CSD_API_NULL_PARAM');
1521 Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
1522 Fnd_Message.SET_TOKEN('NULL_PARAM', 'INVENTORY_ITEM_ID');
1523 Fnd_Msg_Pub.ADD;
1524 RETURN;
1525 ELSIF p_inventory_item_id = p_old_inventory_item_id
1526 OR p_inventory_item_id = Fnd_Api.G_MISS_NUM THEN
1527 RETURN;
1528 END IF;
1529 END IF;
1530
1531 OPEN c1;
1532 FETCH c1 INTO l_dummy;
1533 IF c1%NOTFOUND THEN
1534 l_valid := 'N';
1535 END IF;
1536 CLOSE c1;
1537
1538 IF l_valid = 'N' THEN
1539 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1540 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1541 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_INVENTORY_ITEM');
1542 Fnd_Msg_Pub.ADD;
1543 END IF;
1544 END IF;
1545
1546 END Validate_INVENTORY_ITEM_ID;
1547
1548
1549 PROCEDURE Validate_CUSTOMER_PRODUCT_ID
1550 (
1551 P_CUSTOMER_PRODUCT_ID IN NUMBER,
1552 P_OLD_CUSTOMER_PRODUCT_ID IN NUMBER,
1553 P_INCIDENT_ID IN NUMBER,
1554 P_INVENTORY_ITEM_ID IN NUMBER,
1555 P_SERIAL_NUMBER IN VARCHAR2,
1556 p_validation_mode IN VARCHAR2,
1557 x_return_status OUT NOCOPY VARCHAR2
1558 )
1559 IS
1560 /* Bug fix 9225852: Commented out old definition of cursor c1 and replaced with new one
1561 CURSOR c1 IS
1562 SELECT 'X'
1563 FROM csi_item_instances cii,
1564 cs_incidents_all_b sr
1565 WHERE sr.incident_id = p_incident_id
1566 AND cii.instance_id = p_customer_product_id
1567 AND cii.inventory_item_id = p_inventory_item_id
1568 AND sr.customer_id = cii.owner_party_id
1569 AND (p_serial_number IS NULL OR (p_serial_number IS NOT NULL AND
1570 cii.serial_number = p_serial_number));
1571 l_dummy VARCHAR(1);
1572 l_valid VARCHAR2(1) := 'Y';
1573 */
1574 CURSOR c1 IS
1575 SELECT 'X'
1576 FROM cs_incidents_all_b sr,
1577 csi_i_parties cip
1578 WHERE sr.incident_id = p_incident_id
1579 AND cip.instance_id = p_customer_product_id
1580 AND sr.customer_id = cip.party_id
1581 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(cip.active_start_date,sysdate))
1582 AND TRUNC(NVL(cip.active_end_date,sysdate));
1583
1584 l_dummy VARCHAR(1);
1585 l_valid VARCHAR2(1) := 'Y';
1586
1587 BEGIN
1588
1589 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1590
1591 IF (p_validation_mode = Jtf_Plsql_Api.G_CREATE)
1592 AND (p_customer_product_id = Fnd_Api.G_MISS_NUM
1593 OR p_customer_product_id IS NULL) THEN
1594 RETURN;
1595 END IF;
1596
1597 IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
1598 AND (p_customer_product_id = p_old_customer_product_id
1599 OR p_customer_product_id = Fnd_Api.G_MISS_NUM
1600 OR p_customer_product_id IS NULL) THEN
1601 RETURN;
1602 END IF;
1603
1604 OPEN c1;
1605 FETCH c1 INTO l_dummy;
1606 IF c1%NOTFOUND THEN
1607 l_valid := 'N';
1608 END IF;
1609 CLOSE c1;
1610
1611 IF l_valid = 'N' THEN
1612 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1613 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1614 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_CUST_PROD');
1615 Fnd_Msg_Pub.ADD;
1616 END IF;
1617 END IF;
1618
1619 END Validate_CUSTOMER_PRODUCT_ID;
1620
1621 -- Private procedure to validate customer product_id for refurbished non-IRO
1622 -- saupadhy 11.5.10 01/29/2004
1623
1624 PROCEDURE Validate_INTERNAL_CUST_PROD_ID
1625 (
1626 P_CUSTOMER_PRODUCT_ID IN NUMBER,
1627 P_OLD_CUSTOMER_PRODUCT_ID IN NUMBER,
1628 P_INCIDENT_ID IN NUMBER,
1629 P_INVENTORY_ITEM_ID IN NUMBER,
1630 P_SERIAL_NUMBER IN VARCHAR2,
1631 p_validation_mode IN VARCHAR2,
1632 x_return_status OUT NOCOPY VARCHAR2
1633 )
1634 IS
1635
1636 l_Internal_Party_Id NUMBER ;
1637
1638 CURSOR c1 IS
1639 SELECT 'X'
1640 FROM csi_item_instances cii,
1641 cs_incidents_all_b sr
1642 WHERE sr.incident_id = p_incident_id
1643 AND cii.instance_id = p_customer_product_id
1644 AND cii.inventory_item_id = p_inventory_item_id
1645 AND cii.owner_party_id IN (sr.customer_id , l_Internal_Party_Id )
1646 AND (p_serial_number IS NULL OR (p_serial_number IS NOT NULL AND
1647 cii.serial_number = p_serial_number));
1648 l_dummy VARCHAR(1);
1649 l_valid VARCHAR2(1) := 'Y';
1650
1651 BEGIN
1652
1653 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1654
1655 IF (p_validation_mode = Jtf_Plsql_Api.G_CREATE)
1656 AND (p_customer_product_id = Fnd_Api.G_MISS_NUM
1657 OR p_customer_product_id IS NULL) THEN
1658 RETURN;
1659 END IF;
1660
1661 IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
1662 AND (p_customer_product_id = p_old_customer_product_id
1663 OR p_customer_product_id = Fnd_Api.G_MISS_NUM
1664 OR p_customer_product_id IS NULL) THEN
1665 RETURN;
1666 END IF;
1667
1668 SELECT Internal_Party_id
1669 INTO l_Internal_party_id
1670 FROM csi_install_parameters
1671 WHERE ROWNUM = 1;
1672
1673 OPEN c1;
1674 FETCH c1 INTO l_dummy;
1675 IF c1%NOTFOUND THEN
1676 l_valid := 'N';
1677 END IF;
1678 CLOSE c1;
1679
1680 IF l_valid = 'N' THEN
1681 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1682 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1683 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_CUST_PROD');
1684 Fnd_Msg_Pub.ADD;
1685 END IF;
1686 END IF;
1687
1688 END Validate_Internal_CUST_PROD_ID;
1689
1690
1691 PROCEDURE Validate_UNIT_OF_MEASURE
1692 (
1693 P_UNIT_OF_MEASURE IN VARCHAR2,
1694 P_INVENTORY_ITEM_ID IN NUMBER,
1695 p_validation_mode IN VARCHAR2,
1696 x_return_status OUT NOCOPY VARCHAR2
1697 )
1698 IS
1699 --
1700 CURSOR c1 IS
1701 SELECT 'X'
1702 FROM aso_i_item_uoms_v uom
1703 WHERE uom.uom_code = p_unit_of_measure
1704 AND uom.inventory_item_id = p_inventory_item_id
1705 AND uom.organization_id = Cs_Std.get_item_valdn_orgzn_id;
1706
1707 l_dummy VARCHAR2(1);
1708 l_valid VARCHAR2(1) := 'Y';
1709
1710 BEGIN
1711
1712 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1713
1714 IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
1715 IF p_unit_of_measure IS NULL THEN
1716 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1717 Fnd_Message.SET_NAME('CSD', 'CSD_API_NULL_PARAM');
1718 Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
1719 Fnd_Message.SET_TOKEN('NULL_PARAM', 'UNIT_OF_MEASURE');
1720 Fnd_Msg_Pub.ADD;
1721 RETURN;
1722 ELSIF p_unit_of_measure = p_unit_of_measure
1723 OR p_unit_of_measure = Fnd_Api.G_MISS_CHAR THEN
1724 RETURN;
1725 END IF;
1726 END IF;
1727
1728 OPEN c1;
1729 FETCH c1 INTO l_dummy;
1730 IF c1%NOTFOUND THEN
1731 l_valid := 'N';
1732 END IF;
1733 CLOSE c1;
1734
1735 IF l_valid = 'N' THEN
1736 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1737 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1738 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_UOM');
1739 Fnd_Msg_Pub.ADD;
1740 END IF;
1741 END IF;
1742
1743 END Validate_UNIT_OF_MEASURE;
1744
1745
1746 PROCEDURE Validate_REPAIR_TYPE_ID
1747 (
1748 P_REPAIR_TYPE_ID IN NUMBER,
1749 P_OLD_REPAIR_TYPE_ID IN NUMBER,
1750 p_validation_mode IN VARCHAR2,
1751 x_return_status OUT NOCOPY VARCHAR2
1752 )
1753 IS
1754
1755 CURSOR c1 IS
1756 SELECT 'X'
1757 FROM CSD_REPAIR_TYPES_B TYPE
1758 WHERE TYPE.repair_type_id = p_repair_type_id
1759 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(TYPE.start_date_active, SYSDATE))
1760 AND TRUNC(NVL(TYPE.end_date_active, SYSDATE));
1761
1762 l_dummy VARCHAR2(1);
1763 l_valid VARCHAR2(1) := 'Y';
1764
1765 BEGIN
1766
1767 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1768
1769 IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
1770 IF p_repair_type_id IS NULL THEN
1771 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1772 Fnd_Message.SET_NAME('CSD', 'CSD_API_NULL_PARAM');
1773 Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
1774 Fnd_Message.SET_TOKEN('NULL_PARAM', 'REPAIR_TYPE_ID');
1775 Fnd_Msg_Pub.ADD;
1776 RETURN;
1777 ELSIF p_repair_type_id = p_old_repair_type_id
1778 OR p_repair_type_id = Fnd_Api.G_MISS_NUM THEN
1779 RETURN;
1780 END IF;
1781 END IF;
1782
1783 OPEN c1;
1784 FETCH c1 INTO l_dummy;
1785 IF c1%NOTFOUND THEN
1786 l_valid := 'N';
1787 END IF;
1788 CLOSE c1;
1789
1790 IF l_valid = 'N' THEN
1791 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1792 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1793 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_REPAIR_TYPE');
1794 Fnd_Msg_Pub.ADD;
1795 END IF;
1796 END IF;
1797
1798 END Validate_REPAIR_TYPE_ID;
1799
1800
1801 PROCEDURE Validate_RESOURCE_ID
1802 (
1803 P_RESOURCE_GROUP IN NUMBER,
1804 P_RESOURCE_ID IN NUMBER,
1805 P_OLD_RESOURCE_ID IN NUMBER,
1806 p_validation_mode IN VARCHAR2,
1807 x_return_status OUT NOCOPY VARCHAR2
1808 )
1809 IS
1810
1811 -- swai: bug 7565999 - change cursor to take resorce group and resource id
1812 -- as params instead of relying on procedure parameters.
1813 CURSOR c1 (l_resource_group NUMBER, l_resource_id NUMBER) IS
1814 SELECT 'X'
1815 FROM jtf_rs_resource_extns rs
1816 WHERE l_resource_group IS NULL
1817 AND rs.resource_id = l_resource_id
1818 AND rs.category = 'EMPLOYEE'
1819 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(rs.start_date_active, SYSDATE))
1820 AND TRUNC(NVL(rs.end_date_active, SYSDATE))
1821 UNION
1822
1823 SELECT 'X'
1824 FROM jtf_rs_group_members rm, jtf_rs_resource_extns rs,jtf_rs_groups_b rg
1825 WHERE l_resource_group IS NOT NULL
1826 AND rm.resource_id = l_resource_id
1827 AND rm.group_id = l_resource_group
1828 AND rm.delete_flag <> 'Y'
1829 AND rs.resource_id = rm.resource_id
1830 AND rs.category = 'EMPLOYEE'
1831 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(rs.start_date_active, SYSDATE))
1832 AND TRUNC(NVL(rs.end_date_active, SYSDATE))
1833 AND rg.group_id = rm.group_id
1834 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(rg.start_date_active, SYSDATE))
1835 AND TRUNC(NVL(rg.end_date_active, SYSDATE));
1836
1837 l_dummy VARCHAR2(1);
1838 l_valid VARCHAR2(1) := 'Y';
1839 l_resource_group NUMBER; -- swai: bug 7565999
1840
1841 BEGIN
1842
1843 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1844
1845 -- swai: bug 7565999
1846 if(p_resource_group = Fnd_Api.G_MISS_NUM) THEN
1847 l_resource_group := NULL;
1848 else
1849 l_resource_group := p_resource_group;
1850 end if;
1851 -- end swai: bug 7565999
1852
1853 IF (p_validation_mode = Jtf_Plsql_Api.G_CREATE)
1854 AND (p_resource_id = Fnd_Api.G_MISS_NUM
1855 OR p_resource_id IS NULL) THEN
1856 RETURN;
1857 END IF;
1858
1859 IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
1860 AND (p_resource_id = p_old_resource_id
1861 OR p_resource_id = Fnd_Api.G_MISS_NUM
1862 OR p_resource_id IS NULL) THEN
1863 RETURN;
1864 END IF;
1865
1866 OPEN c1 (l_resource_group, p_resource_id); -- swai: bug 7565999
1867 FETCH c1 INTO l_dummy;
1868 IF c1%NOTFOUND THEN
1869 l_valid := 'N';
1870 END IF;
1871 CLOSE c1;
1872
1873 IF l_valid = 'N' THEN
1874 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1875 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1876 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_RESOURCE');
1877 Fnd_Msg_Pub.ADD;
1878 END IF;
1879 END IF;
1880
1881 END Validate_RESOURCE_ID;
1882 /************************************
1883 Desc: Validates the resource group against the JTF tables.
1884
1885 **************************************/
1886 PROCEDURE Validate_RESOURCE_GROUP
1887 (
1888 P_RESOURCE_GROUP IN NUMBER,
1889 P_OLD_RESOURCE_GROUP IN NUMBER,
1890 p_validation_mode IN VARCHAR2,
1891 x_return_status OUT NOCOPY VARCHAR2
1892 )
1893 IS
1894
1895 CURSOR c1 IS
1896 SELECT 'X'
1897 FROM jtf_rs_group_usages rs, jtf_Rs_groups_b rg
1898 WHERE rs.group_id = p_resource_group
1899 AND rs.usage = 'REPAIR_ORGANIZATION'
1900 AND rs.group_id = rg.group_id
1901 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(rg.start_date_active, SYSDATE))
1902 AND TRUNC(NVL(rg.end_date_active, SYSDATE));
1903
1904 l_dummy VARCHAR2(1);
1905 l_valid VARCHAR2(1) := 'Y';
1906
1907 BEGIN
1908
1909 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1910
1911 IF (p_validation_mode = Jtf_Plsql_Api.G_CREATE)
1912 AND (p_resource_group = Fnd_Api.G_MISS_NUM
1913 OR p_resource_group IS NULL) THEN
1914 RETURN;
1915 END IF;
1916
1917 IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
1918 AND (p_resource_group = p_old_resource_group
1919 OR p_resource_group = Fnd_Api.G_MISS_NUM
1920 OR p_resource_group IS NULL) THEN
1921 RETURN;
1922 END IF;
1923
1924 OPEN c1;
1925 FETCH c1 INTO l_dummy;
1926 IF c1%NOTFOUND THEN
1927 l_valid := 'N';
1928 END IF;
1929 CLOSE c1;
1930
1931 IF l_valid = 'N' THEN
1932 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1933 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1934 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_RS_GROUP');
1935 Fnd_Msg_Pub.ADD;
1936 END IF;
1937 END IF;
1938
1939 END Validate_RESOURCE_GROUP;
1940
1941
1942 PROCEDURE Validate_PROJECT_ID
1943 (
1944 P_PROJECT_ID IN NUMBER,
1945 x_return_status OUT NOCOPY VARCHAR2
1946 )
1947 IS
1948
1949 BEGIN
1950
1951 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1952
1953 RETURN;
1954
1955 END Validate_PROJECT_ID;
1956
1957
1958 PROCEDURE Validate_TASK_ID
1959 (
1960 P_TASK_ID IN NUMBER,
1961 x_return_status OUT NOCOPY VARCHAR2
1962 )
1963 IS
1964 --
1965 BEGIN
1966 --
1967 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1968 --
1969 RETURN;
1970 --
1971 END Validate_TASK_ID;
1972
1973
1974 PROCEDURE Validate_INSTANCE_ID
1975 (
1976 P_INSTANCE_ID IN NUMBER,
1977 x_return_status OUT NOCOPY VARCHAR2
1978 )
1979 IS
1980
1981 BEGIN
1982
1983 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1984
1985 RETURN;
1986
1987 END Validate_INSTANCE_ID;
1988
1989
1990 PROCEDURE Validate_STATUS
1991 (
1992 P_STATUS IN VARCHAR2,
1993 P_OLD_STATUS IN VARCHAR2,
1994 p_validation_mode IN VARCHAR2,
1995 x_return_status OUT NOCOPY VARCHAR2
1996 )
1997 IS
1998
1999 CURSOR c1 IS
2000 SELECT 'X'
2001 FROM fnd_lookups fnd
2002 WHERE fnd.lookup_code = p_status
2003 AND fnd.lookup_type = 'CSD_REPAIR_STATUS'
2004 AND fnd.enabled_flag = 'Y'
2005 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(fnd.start_date_active, SYSDATE))
2006 AND TRUNC(NVL(fnd.end_date_active, SYSDATE));
2007
2008 l_dummy VARCHAR2(1);
2009 l_valid VARCHAR2(1) := 'Y';
2010
2011 BEGIN
2012
2013 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2014
2015 IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
2016 IF p_status IS NULL THEN
2017 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2018 Fnd_Message.SET_NAME('CSD', 'CSD_API_NULL_PARAM');
2019 Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
2020 Fnd_Message.SET_TOKEN('NULL_PARAM', 'STATUS');
2021 Fnd_Msg_Pub.ADD;
2022 RETURN;
2023 ELSIF p_status = p_old_status
2024 OR p_status = Fnd_Api.G_MISS_CHAR THEN
2025 RETURN;
2026 END IF;
2027 END IF;
2028
2029 OPEN c1;
2030 FETCH c1 INTO l_dummy;
2031 IF c1%NOTFOUND THEN
2032 l_valid := 'N';
2033 END IF;
2034 CLOSE c1;
2035
2036 IF l_valid = 'N' THEN
2037 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2038 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2039 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_STATUS');
2040 Fnd_Msg_Pub.ADD;
2041 END IF;
2042 END IF;
2043
2044 END Validate_STATUS;
2045
2046 PROCEDURE Validate_APPROVAL_REQD_FLAG
2047 (
2048 P_APPROVAL_REQUIRED_FLAG IN VARCHAR2,
2049 P_OLD_APPROVAL_REQUIRED_FLAG IN VARCHAR2,
2050 p_validation_mode IN VARCHAR2,
2051 x_return_status OUT NOCOPY VARCHAR2
2052 )
2053 IS
2054
2055 CURSOR c1 IS
2056 SELECT 'X'
2057 FROM fnd_lookups fnd
2058 WHERE fnd.lookup_code = p_approval_required_flag
2059 AND fnd.lookup_type = 'YES_NO'
2060 AND fnd.enabled_flag = 'Y'
2061 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(fnd.start_date_active, SYSDATE))
2062 AND TRUNC(NVL(fnd.end_date_active, SYSDATE));
2063
2064 l_dummy VARCHAR2(1);
2065 l_valid VARCHAR2(1) := 'Y';
2066
2067 BEGIN
2068
2069 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2070
2071 IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
2072 IF p_approval_required_flag IS NULL THEN
2073 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2074 Fnd_Message.SET_NAME('CSD', 'CSD_API_NULL_PARAM');
2075 Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
2076 Fnd_Message.SET_TOKEN('NULL_PARAM', 'APPROVAL_REQUIRED_FLAG');
2077 Fnd_Msg_Pub.ADD;
2078 RETURN;
2079 ELSIF p_approval_required_flag = p_old_approval_required_flag
2080 OR p_approval_required_flag = Fnd_Api.G_MISS_CHAR THEN
2081 RETURN;
2082 END IF;
2083 END IF;
2084
2085 OPEN c1;
2086 FETCH c1 INTO l_dummy;
2087 IF c1%NOTFOUND THEN
2088 l_valid := 'N';
2089 END IF;
2090 CLOSE c1;
2091
2092 IF l_valid = 'N' THEN
2093 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2094 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2095 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_APRVL_REQD_FLG');
2096 Fnd_Msg_Pub.ADD;
2097 END IF;
2098 END IF;
2099
2100 END Validate_APPROVAL_REQD_FLAG;
2101
2102
2103 PROCEDURE Validate_APPROVAL_STATUS
2104 (
2105 P_APPROVAL_STATUS IN VARCHAR2,
2106 P_OLD_APPROVAL_STATUS IN VARCHAR2,
2107 p_validation_mode IN VARCHAR2,
2108 x_return_status OUT NOCOPY VARCHAR2
2109 )
2110 IS
2111
2112 -- Fix for Bug 3824988, sragunat, 11/16/04, Bind Variable fix,
2113 -- Introduced the following constants to use in Cursor cur_module_lookup
2114 -- query
2115 lc_appr_sts_lkp_typ CONSTANT VARCHAR2(19) := 'CSD_APPROVAL_STATUS' ;
2116 lc_enabled CONSTANT VARCHAR2(1) := 'Y';
2117
2118
2119 CURSOR c1 IS
2120 SELECT 'X'
2121 FROM fnd_lookups fnd
2122 WHERE fnd.lookup_code = p_approval_status
2123 AND fnd.lookup_type = lc_appr_sts_lkp_typ
2124 AND fnd.enabled_flag = lc_enabled
2125 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(fnd.start_date_active, SYSDATE))
2126 AND TRUNC(NVL(fnd.end_date_active, SYSDATE));
2127
2128 l_dummy VARCHAR2(1);
2129 l_valid VARCHAR2(1) := 'Y';
2130
2131 BEGIN
2132
2133 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2134
2135 IF (p_validation_mode = Jtf_Plsql_Api.G_CREATE)
2136 AND (p_approval_status = Fnd_Api.G_MISS_CHAR
2137 OR p_approval_status IS NULL) THEN
2138 RETURN;
2139 END IF;
2140
2141 IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
2142 AND (p_approval_status = p_old_approval_status
2143 OR p_approval_status = Fnd_Api.G_MISS_CHAR
2144 OR p_approval_status IS NULL) THEN
2145 RETURN;
2146 END IF;
2147
2148 OPEN c1;
2149 FETCH c1 INTO l_dummy;
2150 IF c1%NOTFOUND THEN
2151 l_valid := 'N';
2152 END IF;
2153 CLOSE c1;
2154
2155 IF l_valid = 'N' THEN
2156 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2157 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2158 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_APRVL_STATUS');
2159 Fnd_Msg_Pub.ADD;
2160 END IF;
2161 END IF;
2162
2163 END Validate_APPROVAL_STATUS;
2164
2165
2166 PROCEDURE Validate_SERIAL_NUMBER
2167 (
2168 P_SERIAL_NUMBER IN VARCHAR2,
2169 x_return_status OUT NOCOPY VARCHAR2
2170 )
2171 IS
2172
2173 BEGIN
2174
2175 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2176
2177 RETURN;
2178
2179 END Validate_SERIAL_NUMBER;
2180
2181
2182 PROCEDURE Validate_PROMISE_DATE
2183 (
2184 P_PROMISE_DATE IN DATE,
2185 P_OLD_PROMISE_DATE IN DATE,
2186 p_validation_mode IN VARCHAR2,
2187 x_return_status OUT NOCOPY VARCHAR2
2188 )
2189 IS
2190
2191 l_valid VARCHAR2(1) := 'Y';
2192
2193 BEGIN
2194
2195 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2196
2197 IF (p_validation_mode = Jtf_Plsql_Api.G_CREATE)
2198 AND (p_promise_date = Fnd_Api.G_MISS_DATE
2199 OR p_promise_date IS NULL) THEN
2200 RETURN;
2201 END IF;
2202
2203 IF (p_validation_mode = Jtf_Plsql_Api.G_UPDATE)
2204 AND (p_promise_date = p_old_promise_date
2205 OR p_promise_date = Fnd_Api.G_MISS_DATE
2206 OR p_promise_date IS NULL) THEN
2207 RETURN;
2208 END IF;
2209
2210 --bug#13517822, promise date can be < sysdate
2211 -- IF p_promise_date < SYSDATE THEN
2212 -- l_valid := 'N';
2213 -- END IF;
2214
2215 IF l_valid = 'N' THEN
2216 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2217 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2218 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_PROMISE_DATE');
2219 Fnd_Msg_Pub.ADD;
2220 END IF;
2221 END IF;
2222
2223 END Validate_PROMISE_DATE;
2224
2225
2226 PROCEDURE Validate_QUANTITY
2227 (
2228 P_QUANTITY IN NUMBER,
2229 P_OLD_QUANTITY IN NUMBER,
2230 p_validation_mode IN VARCHAR2,
2231 x_return_status OUT NOCOPY VARCHAR2
2232 )
2233 IS
2234
2235 l_valid VARCHAR2(1) := 'Y';
2236
2237 BEGIN
2238
2239 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2240
2241 IF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
2242 IF p_quantity IS NULL THEN
2243 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2244 Fnd_Message.SET_NAME('CSD', 'CSD_API_NULL_PARAM');
2245 Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
2246 Fnd_Message.SET_TOKEN('NULL_PARAM', 'QUANTITY');
2247 Fnd_Msg_Pub.ADD;
2248 RETURN;
2249 ELSIF p_quantity = p_old_quantity
2250 OR p_quantity = Fnd_Api.G_MISS_NUM THEN
2251 RETURN;
2252 END IF;
2253 END IF;
2254
2255 IF p_quantity < 0 THEN
2256 l_valid := 'N';
2257 END IF;
2258
2259 IF l_valid = 'N' THEN
2260 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2261 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2262 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_QUANTITY');
2263 Fnd_Msg_Pub.ADD;
2264 END IF;
2265 END IF;
2266
2267 END Validate_QUANTITY;
2268
2269
2270 PROCEDURE Validate_QUANTITY_IN_WIP
2271 (
2272 P_QUANTITY_IN_WIP IN NUMBER,
2273 P_QUANTITY IN NUMBER,
2274 P_OLD_QUANTITY IN NUMBER,
2275 p_validation_mode IN VARCHAR2,
2276 x_return_status OUT NOCOPY VARCHAR2
2277 )
2278 IS
2279
2280 l_valid VARCHAR2(1) := 'Y';
2281
2282 BEGIN
2283
2284 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2285 /*****************************************saupadhy
2286 Following procedure will always return success , after JDSU enhancements,
2287 user can submit quantities more then quantity received. i.e Quantity_in_WIP can
2288 can be greater then Quantity_Rcvd ****************************
2289 if p_quantity_in_wip = FND_API.G_MISS_NUM
2290 or p_quantity_in_wip is null then
2291 return;
2292 end if;
2293
2294 if p_validation_mode = JTF_PLSQL_API.G_CREATE then
2295 if p_quantity < p_quantity_in_wip then
2296 l_valid := 'N';
2297 end if;
2298 elsif p_validation_mode = JTF_PLSQL_API.G_UPDATE then
2299 if p_quantity = FND_API.G_MISS_NUM
2300 or p_quantity is null then
2301 if p_old_quantity < p_quantity_in_wip then
2302 l_valid := 'N';
2303 end if;
2304 else
2305 if p_quantity < p_quantity_in_wip then
2306 l_valid := 'N';
2307 end if;
2308 end if;
2309 end if;
2310
2311 if l_valid = 'N' then
2312 x_return_status := FND_API.G_RET_STS_ERROR;
2313 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2314 FND_MESSAGE.Set_Name('CSD', 'CSD_INVALID_QTY_IN_WIP');
2315 FND_MSG_PUB.ADD;
2316 END IF;
2317 end if;
2318 Commented by saupadhy after JDSU enhancements
2319 **************************************************/
2320
2321 END Validate_QUANTITY_IN_WIP;
2322
2323
2324 PROCEDURE Validate_QUANTITY_RCVD
2325 (
2326 P_QUANTITY_RCVD IN NUMBER,
2327 P_QUANTITY IN NUMBER,
2328 x_return_status OUT NOCOPY VARCHAR2
2329 )
2330 IS
2331
2332 BEGIN
2333
2334 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2335
2336 RETURN;
2337
2338 END Validate_QUANTITY_RCVD;
2339
2340
2341 PROCEDURE Validate_QUANTITY_SHIPPED
2342 (
2343 P_QUANTITY_SHIPPED IN NUMBER,
2344 P_QUANTITY IN NUMBER,
2345 x_return_status OUT NOCOPY VARCHAR2
2346 )
2347 IS
2348
2349 BEGIN
2350
2351 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2352
2353 RETURN;
2354
2355 END Validate_QUANTITY_SHIPPED;
2356
2357 PROCEDURE Validate_OBJECT_VERSION_NUMBER
2358 (
2359 p_OBJECT_VERSION_NUMBER IN NUMBER,
2360 p_OLD_OBJECT_VERSION_NUMBER IN NUMBER,
2361 x_return_status OUT NOCOPY VARCHAR2
2362 )
2363 IS
2364 l_valid VARCHAR2(1) := 'Y';
2365
2366
2367 BEGIN
2368
2369 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2370
2371 -- Check if the current obj ver num from form and the obj ver num in db are different
2372 IF (p_OBJECT_VERSION_NUMBER <> p_OLD_OBJECT_VERSION_NUMBER) THEN
2373 l_valid := 'N';
2374 END IF;
2375
2376 IF l_valid = 'N' THEN
2377 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2378 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2379
2380 -- travi this mesg need to be changed
2381 -- travi FND_MESSAGE.Set_Name('CSD', 'CSD_INVALID_OBJ_VER_NUM');
2382 Fnd_Msg_Pub.ADD;
2383
2384 END IF;
2385 END IF;
2386
2387
2388 RETURN;
2389
2390 END Validate_OBJECT_VERSION_NUMBER;
2391
2392 -- New Code for 11.5.7.1
2393 -- Purpose : Validate Repiar Group id
2394 PROCEDURE Validate_RO_GROUP_ID
2395 (
2396 p_ro_group_id IN NUMBER,
2397 p_old_ro_group_id IN NUMBER,
2398 p_validation_mode IN VARCHAR2,
2399 x_return_status OUT NOCOPY VARCHAR2
2400 )
2401 IS
2402
2403 CURSOR c1 IS
2404 SELECT 'X'
2405 FROM csd_repair_order_groups crog
2406 WHERE crog.repair_group_id = p_ro_group_id;
2407
2408 l_dummy VARCHAR2(1);
2409 l_valid VARCHAR2(1) := 'Y';
2410
2411 BEGIN
2412
2413 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2414
2415 -- travi fix
2416 IF p_validation_mode = Jtf_Plsql_Api.G_CREATE THEN
2417 IF (p_ro_group_id IS NULL) THEN
2418 IF (g_debug > 0 ) THEN
2419 Csd_Gen_Utility_Pvt.ADD('Create_Repair_Order group_id is null : Validate_RO_GROUP_ID');
2420 END IF;
2421
2422 IF (g_debug > 0 ) THEN
2423 Csd_Gen_Utility_Pvt.ADD('Create_Repair_Order group_id p_validation_mode : '||p_validation_mode);
2424 END IF;
2425
2426 RETURN;
2427 END IF;
2428 ELSIF p_validation_mode = Jtf_Plsql_Api.G_UPDATE THEN
2429 IF p_ro_group_id IS NULL THEN
2430 IF (g_debug > 0 ) THEN
2431 Csd_Gen_Utility_Pvt.ADD('Update_Repair_Order group_id is null : Validate_RO_GROUP_ID');
2432 END IF;
2433
2434 IF (g_debug > 0 ) THEN
2435 Csd_Gen_Utility_Pvt.ADD('Update_Repair_Order group_id p_validation_mode : '||p_validation_mode);
2436 END IF;
2437
2438 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2439 Fnd_Message.SET_NAME('CSD', 'CSD_API_NULL_PARAM');
2440 Fnd_Message.SET_TOKEN('API_NAME', 'Update_Repair_Order');
2441 Fnd_Message.SET_TOKEN('NULL_PARAM', 'RO_GROUP_ID');
2442 Fnd_Msg_Pub.ADD;
2443 RETURN;
2444 ELSIF p_ro_group_id = p_old_ro_group_id
2445 OR p_ro_group_id = Fnd_Api.G_MISS_NUM THEN
2446 IF (g_debug > 0 ) THEN
2447 Csd_Gen_Utility_Pvt.ADD('Update_Repair_Order group_id is not null or g_miss_num : Validate_RO_GROUP_ID');
2448 END IF;
2449
2450 IF (g_debug > 0 ) THEN
2451 Csd_Gen_Utility_Pvt.ADD('Update_Repair_Order group_id p_validation_mode : '||p_validation_mode);
2452 END IF;
2453
2454 RETURN;
2455 END IF;
2456 END IF;
2457
2458 /* old code
2459 if p_validation_mode = JTF_PLSQL_API.G_UPDATE then
2460 if p_ro_group_id is null then
2461 x_return_status := FND_API.G_RET_STS_ERROR;
2462 FND_MESSAGE.SET_NAME('CSD', 'CSD_API_NULL_PARAM');
2463 FND_MESSAGE.SET_TOKEN('API_NAME', 'Update_Repair_Order');
2464 FND_MESSAGE.SET_TOKEN('NULL_PARAM', 'RO_GROUP_ID');
2465 FND_MSG_PUB.Add;
2466 return;
2467 elsif p_ro_group_id = p_old_ro_group_id
2468 or p_ro_group_id = FND_API.G_MISS_NUM then
2469 return;
2470 end if;
2471 end if;
2472 */
2473
2474 IF (p_ro_group_id IS NOT NULL) OR
2475 (p_ro_group_id = Fnd_Api.G_MISS_NUM) THEN
2476 IF (g_debug > 0 ) THEN
2477 Csd_Gen_Utility_Pvt.ADD('Create / update Repair_Order group_id is not null or g_miss_num : Validate_RO_GROUP_ID');
2478 END IF;
2479
2480 IF (g_debug > 0 ) THEN
2481 Csd_Gen_Utility_Pvt.ADD('Create / update group_id p_validation_mode : '||p_validation_mode);
2482 END IF;
2483
2484 OPEN c1;
2485 FETCH c1 INTO l_dummy;
2486 IF c1%NOTFOUND THEN
2487 l_valid := 'N';
2488 END IF;
2489 CLOSE c1;
2490
2491 IF l_valid = 'N' THEN
2492 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2493 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2494 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_RO_GROUP_ID');
2495 Fnd_Msg_Pub.ADD;
2496 END IF;
2497 END IF;
2498 END IF;
2499
2500 END Validate_RO_GROUP_ID;
2501
2502
2503 PROCEDURE Validate_SOURCE
2504 (
2505 p_ORIGINAL_SOURCE_HEADER_ID IN NUMBER,
2506 p_ORIGINAL_SOURCE_LINE_ID IN NUMBER,
2507 p_ORIGINAL_SOURCE_REFERENCE IN VARCHAR2,
2508 p_validation_mode IN VARCHAR2,
2509 x_return_status OUT NOCOPY VARCHAR2
2510 ) IS
2511
2512 CURSOR c1 IS
2513 SELECT 'X'
2514 FROM oe_order_lines_all
2515 WHERE line_id = p_ORIGINAL_SOURCE_LINE_ID
2516 AND header_id = p_ORIGINAL_SOURCE_HEADER_ID;
2517
2518 l_dummy VARCHAR2(1);
2519 l_valid VARCHAR2(1) := 'Y';
2520
2521 BEGIN
2522
2523 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2524
2525 IF (p_validation_mode = Jtf_Plsql_Api.G_CREATE)
2526 AND (p_ORIGINAL_SOURCE_REFERENCE = Fnd_Api.G_MISS_CHAR
2527 OR p_ORIGINAL_SOURCE_REFERENCE IS NULL) THEN
2528 RETURN;
2529 END IF;
2530
2531 OPEN c1;
2532 FETCH c1 INTO l_dummy;
2533 IF c1%NOTFOUND THEN
2534 l_valid := 'N';
2535 END IF;
2536 CLOSE c1;
2537
2538 IF l_valid = 'N' THEN
2539 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2540 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2541 Fnd_Message.Set_Name('CSD', 'CSD_INVALID_SOURCE');
2542 Fnd_Message.SET_TOKEN('ORDER_LINE_ID', p_ORIGINAL_SOURCE_LINE_ID);
2543 Fnd_Message.SET_TOKEN('ORDER_HEADER_ID', p_ORIGINAL_SOURCE_HEADER_ID);
2544 Fnd_Msg_Pub.ADD;
2545 END IF;
2546 END IF;
2547
2548 END Validate_SOURCE;
2549
2550 --
2551 -- bug#7242791,12. FP, subhat
2552 -- added a new out parameter.ds
2553 -- @param: x_dff_rec OUT NOCOPY CSD_REPAIRS_UTIL.DEF_Rec_Type
2554 --
2555 PROCEDURE Validate_Repairs
2556 (
2557 P_Api_Version_Number IN NUMBER,
2558 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2559 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2560 P_Validation_mode IN VARCHAR2,
2561 p_repair_line_id IN NUMBER := Fnd_Api.G_MISS_NUM,
2562 P_REPLN_Rec IN Csd_Repairs_Pub.REPLN_Rec_Type,
2563 P_OLD_REPLN_Rec IN Csd_Repairs_Pub.REPLN_Rec_Type := Csd_Repairs_Pub.G_MISS_REPLN_Rec,
2564 x_return_status OUT NOCOPY VARCHAR2,
2565 x_msg_count OUT NOCOPY NUMBER,
2566 x_msg_data OUT NOCOPY VARCHAR2,
2567 -- bug#7242791, subhat
2568 x_dff_rec OUT NOCOPY CSD_REPAIRS_UTIL.DEF_Rec_Type
2569
2570 )
2571 IS
2572
2573 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Repairs';
2574 l_api_version_number CONSTANT NUMBER := 1.0;
2575 --l_DEF_Rec Csd_Repairs_Util.DEF_Rec_Type;
2576 l_valid_def_rec BOOLEAN;
2577 l_attribute_category VARCHAR2(30);
2578 l_attribute1 VARCHAR2(150);
2579 l_attribute2 VARCHAR2(150);
2580 l_attribute3 VARCHAR2(150);
2581 l_attribute4 VARCHAR2(150);
2582 l_attribute5 VARCHAR2(150);
2583 l_attribute6 VARCHAR2(150);
2584 l_attribute7 VARCHAR2(150);
2585 l_attribute8 VARCHAR2(150);
2586 l_attribute9 VARCHAR2(150);
2587 l_attribute10 VARCHAR2(150);
2588 l_attribute11 VARCHAR2(150);
2589 l_attribute12 VARCHAR2(150);
2590 l_attribute13 VARCHAR2(150);
2591 l_attribute14 VARCHAR2(150);
2592 l_attribute15 VARCHAR2(150);
2593 -- subhat, 15 new DFF columns(bug#7497907).
2594 l_attribute16 VARCHAR2(150);
2595 l_attribute17 VARCHAR2(150);
2596 l_attribute18 VARCHAR2(150);
2597 l_attribute19 VARCHAR2(150);
2598 l_attribute20 VARCHAR2(150);
2599 l_attribute21 VARCHAR2(150);
2600 l_attribute22 VARCHAR2(150);
2601 l_attribute23 VARCHAR2(150);
2602 l_attribute24 VARCHAR2(150);
2603 l_attribute25 VARCHAR2(150);
2604 l_attribute26 VARCHAR2(150);
2605 l_attribute27 VARCHAR2(150);
2606 l_attribute28 VARCHAR2(150);
2607 l_attribute29 VARCHAR2(150);
2608 l_attribute30 VARCHAR2(150);
2609
2610 l_ib_flag VARCHAR2(1):= '';
2611 l_refurbished_repair_type_flag VARCHAR2(1);
2612
2613 BEGIN
2614
2615 -- Standard Start of API savepoint
2616 SAVEPOINT VALIDATE_REPAIRS_PVT;
2617 -- Standard call to check for call compatibility.
2618 IF NOT Fnd_Api.Compatible_API_Call
2619 (l_api_version_number,
2620 p_api_version_number,
2621 l_api_name,
2622 G_PKG_NAME)
2623 THEN
2624 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2625 END IF;
2626 -- Initialize message list if p_init_msg_list is set to TRUE.
2627 IF Fnd_Api.to_Boolean(p_init_msg_list)
2628 THEN
2629 Fnd_Msg_Pub.initialize;
2630 END IF;
2631 -- Initialize API return status to SUCCESS
2632 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2633 --
2634 -- API Body
2635 --
2636
2637 -- New Code for 11.5.7.1
2638 -- Purpose : Validate Repiar Group id
2639 Validate_RO_GROUP_ID
2640 (
2641 p_ro_group_id => P_REPLN_Rec.repair_group_id,
2642 p_old_ro_group_id => P_OLD_REPLN_Rec.repair_group_id,
2643 p_validation_mode => p_validation_mode,
2644 x_return_status => x_return_status
2645 );
2646 -- Check return status from the above procedure call
2647
2648
2649 Validate_REPAIR_LINE_ID
2650 (
2651 p_REPAIR_LINE_ID => P_REPAIR_LINE_ID,
2652 p_validation_mode => p_validation_mode,
2653 x_return_status => x_return_status
2654 );
2655 -- Check return status from the above procedure call
2656 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2657 RAISE Fnd_Api.G_EXC_ERROR;
2658 END IF;
2659
2660 Validate_REPAIR_NUMBER
2661 (
2662 p_REPAIR_NUMBER => P_REPLN_Rec.REPAIR_NUMBER,
2663 p_OLD_REPAIR_NUMBER => P_OLD_REPLN_Rec.REPAIR_NUMBER,
2664 p_validation_mode => p_validation_mode,
2665 x_return_status => x_return_status
2666 );
2667 -- Check return status from the above procedure call
2668 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2669 RAISE Fnd_Api.G_EXC_ERROR;
2670 END IF;
2671
2672 Validate_INCIDENT_ID
2673 (
2674 p_INCIDENT_ID => P_REPLN_Rec.INCIDENT_ID,
2675 p_OLD_INCIDENT_ID => P_OLD_REPLN_Rec.INCIDENT_ID,
2676 p_validation_mode => p_validation_mode,
2677 x_return_status => x_return_status
2678 );
2679 -- Check return status from the above procedure call
2680 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2681 RAISE Fnd_Api.G_EXC_ERROR;
2682 END IF;
2683
2684 Validate_INVENTORY_ITEM_ID
2685 (
2686 p_INVENTORY_ITEM_ID => P_REPLN_Rec.INVENTORY_ITEM_ID,
2687 p_OLD_INVENTORY_ITEM_ID => P_OLD_REPLN_Rec.INVENTORY_ITEM_ID,
2688 p_validation_mode => p_validation_mode,
2689 x_return_status => x_return_status
2690 );
2691 -- Check return status from the above procedure call
2692 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2693 RAISE Fnd_Api.G_EXC_ERROR;
2694 END IF;
2695
2696 Validate_UNIT_OF_MEASURE
2697 (
2698 p_UNIT_OF_MEASURE => P_REPLN_Rec.UNIT_OF_MEASURE,
2699 p_INVENTORY_ITEM_ID => P_REPLN_Rec.INVENTORY_ITEM_ID,
2700 p_validation_mode => p_validation_mode,
2701 x_return_status => x_return_status
2702 );
2703 -- Check return status from the above procedure call
2704 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2705 RAISE Fnd_Api.G_EXC_ERROR;
2706 END IF;
2707
2708 Validate_REPAIR_TYPE_ID
2709 (
2710 p_REPAIR_TYPE_ID => P_REPLN_Rec.REPAIR_TYPE_ID,
2711 p_OLD_REPAIR_TYPE_ID => P_OLD_REPLN_Rec.REPAIR_TYPE_ID,
2712 p_validation_mode => p_validation_mode,
2713 x_return_status => x_return_status
2714 );
2715
2716 -- Check if repair Type is Refurbished saupadhy 11.5.10
2717 -- If so set flag l_Refurbihed_repair_Type_Flag with value 'Y'
2718 BEGIN
2719 SELECT 'Y' INTO l_Refurbished_repair_Type_Flag
2720 FROM Csd_REpair_types_b
2721 WHERE Repair_type_Id = p_Repln_Rec.Repair_Type_Id
2722 AND NVL(internal_order_Flag,'N') = 'N'
2723 AND repair_type_ref = 'RF' ;
2724 EXCEPTION
2725 WHEN NO_DATA_FOUND THEN
2726 l_Refurbished_repair_Type_Flag := 'N' ;
2727 END;
2728
2729 -- Check return status from the above procedure call
2730 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2731 RAISE Fnd_Api.G_EXC_ERROR;
2732 END IF;
2733
2734 Validate_APPROVAL_STATUS
2735 (
2736 p_APPROVAL_STATUS => P_REPLN_Rec.APPROVAL_STATUS,
2737 p_OLD_APPROVAL_STATUS => P_OLD_REPLN_Rec.APPROVAL_STATUS,
2738 p_validation_mode => p_validation_mode,
2739 x_return_status => x_return_status
2740 );
2741 -- Check return status from the above procedure call
2742 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2743 RAISE Fnd_Api.G_EXC_ERROR;
2744 END IF;
2745
2746 /* Commented for R12 Flex Flow
2747 -- No validation for Status required.
2748 Validate_STATUS
2749 (
2750 p_STATUS => P_REPLN_Rec.STATUS,
2751 p_OLD_STATUS => P_OLD_REPLN_Rec.STATUS,
2752 p_validation_mode => p_validation_mode,
2753 x_return_status => x_return_status
2754 );
2755 -- Check return status from the above procedure call
2756 IF not (x_return_status = FND_API.G_RET_STS_SUCCESS) then
2757 RAISE FND_API.G_EXC_ERROR;
2758 END IF;
2759 */
2760
2761 Validate_QUANTITY
2762 (
2763 p_QUANTITY => P_REPLN_Rec.QUANTITY,
2764 p_OLD_QUANTITY => P_OLD_REPLN_Rec.QUANTITY,
2765 p_validation_mode => p_validation_mode,
2766 x_return_status => x_return_status
2767 );
2768 -- Check return status from the above procedure call
2769 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2770 RAISE Fnd_Api.G_EXC_ERROR;
2771 END IF;
2772
2773 -- travi new code to validate the object version number
2774 Validate_OBJECT_VERSION_NUMBER
2775 (
2776 p_OBJECT_VERSION_NUMBER => P_REPLN_Rec.OBJECT_VERSION_NUMBER,
2777 p_OLD_OBJECT_VERSION_NUMBER => P_OLD_REPLN_Rec.OBJECT_VERSION_NUMBER,
2778 x_return_status => x_return_status
2779 );
2780
2781 -- Check return status from the above procedure call
2782 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2783 RAISE Fnd_Api.G_EXC_ERROR;
2784 END IF;
2785
2786 IF (p_validation_level >= Fnd_Api.G_VALID_LEVEL_FULL) THEN
2787
2788
2789 -- Do following validation only for non refurbished repair orders
2790 -- Validation for customer_product_id should not be done for
2791 -- refurbished repair orders saupadhy 11.5.10
2792 IF l_Refurbished_repair_Type_Flag = 'N' THEN
2793 Validate_CUSTOMER_PRODUCT_ID
2794 (
2795 p_CUSTOMER_PRODUCT_ID => P_REPLN_Rec.CUSTOMER_PRODUCT_ID,
2796 p_OLD_CUSTOMER_PRODUCT_ID => P_OLD_REPLN_Rec.CUSTOMER_PRODUCT_ID,
2797 p_INCIDENT_ID => P_REPLN_Rec.INCIDENT_ID,
2798 p_INVENTORY_ITEM_ID => P_REPLN_Rec.INVENTORY_ITEM_ID,
2799 p_SERIAL_NUMBER => P_REPLN_Rec.SERIAL_NUMBER,
2800 p_validation_mode => p_validation_mode,
2801 x_return_status => x_return_status);
2802 ELSE
2803 Validate_Internal_CUST_PROD_ID
2804 (
2805 p_CUSTOMER_PRODUCT_ID => P_REPLN_Rec.CUSTOMER_PRODUCT_ID,
2806 p_OLD_CUSTOMER_PRODUCT_ID => P_OLD_REPLN_Rec.CUSTOMER_PRODUCT_ID,
2807 p_INCIDENT_ID => P_REPLN_Rec.INCIDENT_ID,
2808 p_INVENTORY_ITEM_ID => P_REPLN_Rec.INVENTORY_ITEM_ID,
2809 p_SERIAL_NUMBER => P_REPLN_Rec.SERIAL_NUMBER,
2810 p_validation_mode => p_validation_mode,
2811 x_return_status => x_return_status);
2812
2813 END IF;
2814
2815 -- Check return status from the above procedure call
2816 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2817 RAISE Fnd_Api.G_EXC_ERROR;
2818 END IF;
2819
2820 Validate_SOURCE
2821 (
2822 p_ORIGINAL_SOURCE_HEADER_ID => P_REPLN_Rec.ORIGINAL_SOURCE_HEADER_ID,
2823 p_ORIGINAL_SOURCE_LINE_ID => P_REPLN_Rec.ORIGINAL_SOURCE_LINE_ID,
2824 p_ORIGINAL_SOURCE_REFERENCE => P_REPLN_Rec.ORIGINAL_SOURCE_REFERENCE,
2825 p_validation_mode => Jtf_Plsql_Api.G_CREATE,
2826 x_return_status => x_return_status);
2827
2828 -- Check return status from the above procedure call
2829 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2830 RAISE Fnd_Api.G_EXC_ERROR;
2831 END IF;
2832
2833 --Vijay 10/28/2004 Begin
2834
2835 Validate_RESOURCE_GROUP
2836 (
2837 p_RESOURCE_GROUP => P_REPLN_Rec.RESOURCE_GROUP,
2838 p_OLD_RESOURCE_GROUP => P_OLD_REPLN_Rec.RESOURCE_GROUP,
2839 p_validation_mode => p_validation_mode,
2840 x_return_status => x_return_status
2841 );
2842 -- Check return status from the above procedure call
2843 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2844 RAISE Fnd_Api.G_EXC_ERROR;
2845 END IF;
2846 --Vijay 10/28/2004 End
2847
2848 Validate_RESOURCE_ID
2849 (
2850 p_RESOURCE_GROUP => P_REPLN_Rec.RESOURCE_GROUP,
2851 p_RESOURCE_ID => P_REPLN_Rec.RESOURCE_ID,
2852 p_OLD_RESOURCE_ID => P_OLD_REPLN_Rec.RESOURCE_ID,
2853 p_validation_mode => p_validation_mode,
2854 x_return_status => x_return_status
2855 );
2856 -- Check return status from the above procedure call
2857 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2858 RAISE Fnd_Api.G_EXC_ERROR;
2859 END IF;
2860
2861 Validate_APPROVAL_REQD_FLAG
2862 (
2863 p_APPROVAL_REQUIRED_FLAG => P_REPLN_Rec.APPROVAL_REQUIRED_FLAG,
2864 p_OLD_APPROVAL_REQUIRED_FLAG => P_OLD_REPLN_Rec.APPROVAL_REQUIRED_FLAG,
2865 p_validation_mode => p_validation_mode,
2866 x_return_status => x_return_status
2867 );
2868 -- Check return status from the above procedure call
2869 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2870 RAISE Fnd_Api.G_EXC_ERROR;
2871 END IF;
2872
2873
2874 Validate_PROMISE_DATE
2875 (
2876 p_PROMISE_DATE => P_REPLN_Rec.PROMISE_DATE,
2877 p_OLD_PROMISE_DATE => P_OLD_REPLN_Rec.PROMISE_DATE,
2878 p_validation_mode => p_validation_mode,
2879 x_return_status => x_return_status
2880 );
2881 -- Check return status from the above procedure call
2882 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2883 RAISE Fnd_Api.G_EXC_ERROR;
2884 END IF;
2885
2886 Validate_QUANTITY_IN_WIP
2887 (
2888 p_QUANTITY_IN_WIP => P_REPLN_Rec.QUANTITY_IN_WIP,
2889 p_QUANTITY => P_REPLN_Rec.QUANTITY,
2890 p_OLD_QUANTITY => P_OLD_REPLN_Rec.QUANTITY,
2891 p_validation_mode => p_validation_mode,
2892 x_return_status => x_return_status
2893 );
2894
2895 -- Check return status from the above procedure call
2896 IF NOT (x_return_status = Fnd_Api.G_RET_STS_SUCCESS) THEN
2897 RAISE Fnd_Api.G_EXC_ERROR;
2898 END IF;
2899
2900 -- bugfix 3390579: p_repln_rec does not contain all the values. To validate
2901 -- DFF, we need proper values to be populated. Here, we will populate the
2902 -- appropriate values from p_old_repln_rec record structure so that
2903 -- validation happens correctly.
2904
2905 IF (p_REPLN_Rec.attribute_category IS NULL) OR (p_REPLN_Rec.attribute_category = Fnd_Api.G_MISS_CHAR) THEN
2906 IF NVL(p_old_repln_rec.attribute_category,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2907 l_attribute_category := p_old_repln_rec.attribute_category;
2908 END IF;
2909 ELSE
2910 l_attribute_category := p_REPLN_Rec.attribute_category;
2911 END IF;
2912
2913 IF (p_REPLN_Rec.attribute1 IS NULL) OR (p_REPLN_Rec.attribute1 = Fnd_Api.G_MISS_CHAR) THEN
2914 IF NVL(p_old_repln_rec.attribute1,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2915 l_attribute1 := p_old_repln_rec.attribute1;
2916 END IF;
2917 ELSE
2918 l_attribute1 := p_REPLN_Rec.attribute1;
2919 END IF;
2920
2921 IF (p_REPLN_Rec.attribute2 IS NULL) OR (p_REPLN_Rec.attribute2 = Fnd_Api.G_MISS_CHAR) THEN
2922 IF NVL(p_old_repln_rec.attribute2,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2923 l_attribute2 := p_old_repln_rec.attribute2;
2924 END IF;
2925 ELSE
2926 l_attribute2 := p_REPLN_Rec.attribute2;
2927 END IF;
2928
2929 IF (p_REPLN_Rec.attribute3 IS NULL) OR (p_REPLN_Rec.attribute3 = Fnd_Api.G_MISS_CHAR) THEN
2930 IF NVL(p_old_repln_rec.attribute3,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2931 l_attribute3 := p_old_repln_rec.attribute3;
2932 END IF;
2933 ELSE
2934 l_attribute3 := p_REPLN_Rec.attribute3;
2935 END IF;
2936
2937 IF (p_REPLN_Rec.attribute4 IS NULL) OR (p_REPLN_Rec.attribute4 = Fnd_Api.G_MISS_CHAR) THEN
2938 IF NVL(p_old_repln_rec.attribute4,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2939 l_attribute4 := p_old_repln_rec.attribute4;
2940 END IF;
2941 ELSE
2942 l_attribute4 := p_REPLN_Rec.attribute4;
2943 END IF;
2944
2945 IF (p_REPLN_Rec.attribute5 IS NULL) OR (p_REPLN_Rec.attribute5 = Fnd_Api.G_MISS_CHAR) THEN
2946 IF NVL(p_old_repln_rec.attribute5,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2947 l_attribute5 := p_old_repln_rec.attribute5;
2948 END IF;
2949 ELSE
2950 l_attribute5 := p_REPLN_Rec.attribute5;
2951 END IF;
2952
2953 IF (p_REPLN_Rec.attribute6 IS NULL) OR (p_REPLN_Rec.attribute6 = Fnd_Api.G_MISS_CHAR) THEN
2954 IF NVL(p_old_repln_rec.attribute6,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2955 l_attribute6 := p_old_repln_rec.attribute6;
2956 END IF;
2957 ELSE
2958 l_attribute6 := p_REPLN_Rec.attribute6;
2959 END IF;
2960
2961 IF (p_REPLN_Rec.attribute7 IS NULL) OR (p_REPLN_Rec.attribute7 = Fnd_Api.G_MISS_CHAR) THEN
2962 IF NVL(p_old_repln_rec.attribute7,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2963 l_attribute7 := p_old_repln_rec.attribute7;
2964 END IF;
2965 ELSE
2966 l_attribute7 := p_REPLN_Rec.attribute7;
2967 END IF;
2968
2969 IF (p_REPLN_Rec.attribute8 IS NULL) OR (p_REPLN_Rec.attribute8 = Fnd_Api.G_MISS_CHAR) THEN
2970 IF NVL(p_old_repln_rec.attribute8,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2971 l_attribute8 := p_old_repln_rec.attribute8;
2972 END IF;
2973 ELSE
2974 l_attribute8 := p_REPLN_Rec.attribute8;
2975 END IF;
2976
2977 IF (p_REPLN_Rec.attribute9 IS NULL) OR (p_REPLN_Rec.attribute9 = Fnd_Api.G_MISS_CHAR) THEN
2978 IF NVL(p_old_repln_rec.attribute9,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2979 l_attribute9 := p_old_repln_rec.attribute9;
2980 END IF;
2981 ELSE
2982 l_attribute9 := p_REPLN_Rec.attribute9;
2983 END IF;
2984
2985 IF (p_REPLN_Rec.attribute10 IS NULL) OR (p_REPLN_Rec.attribute10 = Fnd_Api.G_MISS_CHAR) THEN
2986 IF NVL(p_old_repln_rec.attribute10,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2987 l_attribute10 := p_old_repln_rec.attribute10;
2988 END IF;
2989 ELSE
2990 l_attribute10 := p_REPLN_Rec.attribute10;
2991 END IF;
2992
2993 IF (p_REPLN_Rec.attribute11 IS NULL) OR (p_REPLN_Rec.attribute11 = Fnd_Api.G_MISS_CHAR) THEN
2994 IF NVL(p_old_repln_rec.attribute11,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
2995 l_attribute11 := p_old_repln_rec.attribute11;
2996 END IF;
2997 ELSE
2998 l_attribute11 := p_REPLN_Rec.attribute11;
2999 END IF;
3000
3001 IF (p_REPLN_Rec.attribute12 IS NULL) OR (p_REPLN_Rec.attribute12 = Fnd_Api.G_MISS_CHAR) THEN
3002 IF NVL(p_old_repln_rec.attribute12,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
3003 l_attribute12 := p_old_repln_rec.attribute12;
3004 END IF;
3005 ELSE
3006 l_attribute12 := p_REPLN_Rec.attribute12;
3007 END IF;
3008
3009 IF (p_REPLN_Rec.attribute13 IS NULL) OR (p_REPLN_Rec.attribute13 = Fnd_Api.G_MISS_CHAR) THEN
3010 IF NVL(p_old_repln_rec.attribute13,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
3011 l_attribute13 := p_old_repln_rec.attribute13;
3012 END IF;
3013 ELSE
3014 l_attribute13 := p_REPLN_Rec.attribute13;
3015 END IF;
3016
3017 IF (p_REPLN_Rec.attribute14 IS NULL) OR (p_REPLN_Rec.attribute14 = Fnd_Api.G_MISS_CHAR) THEN
3018 IF NVL(p_old_repln_rec.attribute14,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
3019 l_attribute14 := p_old_repln_rec.attribute14;
3020 END IF;
3021 ELSE
3022 l_attribute14 := p_REPLN_Rec.attribute14;
3023 END IF;
3024
3025 IF (p_REPLN_Rec.attribute15 IS NULL) OR (p_REPLN_Rec.attribute15 = Fnd_Api.G_MISS_CHAR) THEN
3026 IF NVL(p_old_repln_rec.attribute15,Fnd_Api.G_MISS_CHAR) <> Fnd_Api.G_MISS_CHAR THEN
3027 l_attribute15 := p_old_repln_rec.attribute15;
3028 END IF;
3029 ELSE
3030 l_attribute15 := p_REPLN_Rec.attribute15;
3031 END IF;
3032
3033 --subhat, 15 new DFF columns(bug#7497907), 12.1 FP.
3034 IF (p_REPLN_Rec.attribute16 is null) or (p_REPLN_Rec.attribute16 = FND_API.G_MISS_CHAR) then
3035 IF nvl(p_old_repln_rec.attribute16,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3036 l_attribute16 := p_old_repln_rec.attribute16;
3037 END IF;
3038 ELSE
3039 l_attribute16 := p_REPLN_Rec.attribute16;
3040 END IF;
3041
3042 IF (p_REPLN_Rec.attribute17 is null) or (p_REPLN_Rec.attribute17 = FND_API.G_MISS_CHAR) then
3043 IF nvl(p_old_repln_rec.attribute17,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3044 l_attribute17 := p_old_repln_rec.attribute17;
3045 END IF;
3046 ELSE
3047 l_attribute17 := p_REPLN_Rec.attribute17;
3048 END IF;
3049
3050 IF (p_REPLN_Rec.attribute18 is null) or (p_REPLN_Rec.attribute18 = FND_API.G_MISS_CHAR) then
3051 IF nvl(p_old_repln_rec.attribute18,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3052 l_attribute18 := p_old_repln_rec.attribute18;
3053 END IF;
3054 ELSE
3055 l_attribute18 := p_REPLN_Rec.attribute18;
3056 END IF;
3057
3058 IF (p_REPLN_Rec.attribute19 is null) or (p_REPLN_Rec.attribute19 = FND_API.G_MISS_CHAR) then
3059 IF nvl(p_old_repln_rec.attribute19,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3060 l_attribute19 := p_old_repln_rec.attribute19;
3061 END IF;
3062 ELSE
3063 l_attribute19 := p_REPLN_Rec.attribute19;
3064 END IF;
3065
3066 IF (p_REPLN_Rec.attribute20 is null) or (p_REPLN_Rec.attribute20 = FND_API.G_MISS_CHAR) then
3067 IF nvl(p_old_repln_rec.attribute20,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3068 l_attribute20 := p_old_repln_rec.attribute20;
3069 END IF;
3070 ELSE
3071 l_attribute20 := p_REPLN_Rec.attribute20;
3072 END IF;
3073
3074 IF (p_REPLN_Rec.attribute21 is null) or (p_REPLN_Rec.attribute21 = FND_API.G_MISS_CHAR) then
3075 IF nvl(p_old_repln_rec.attribute21,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3076 l_attribute21 := p_old_repln_rec.attribute21;
3077 END IF;
3078 ELSE
3079 l_attribute21 := p_REPLN_Rec.attribute21;
3080 END IF;
3081
3082 IF (p_REPLN_Rec.attribute22 is null) or (p_REPLN_Rec.attribute22 = FND_API.G_MISS_CHAR) then
3083 IF nvl(p_old_repln_rec.attribute22,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3084 l_attribute22 := p_old_repln_rec.attribute22;
3085 END IF;
3086 ELSE
3087 l_attribute22 := p_REPLN_Rec.attribute22;
3088 END IF;
3089
3090 IF (p_REPLN_Rec.attribute23 is null) or (p_REPLN_Rec.attribute23 = FND_API.G_MISS_CHAR) then
3091 IF nvl(p_old_repln_rec.attribute23,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3092 l_attribute23 := p_old_repln_rec.attribute23;
3093 END IF;
3094 ELSE
3095 l_attribute23 := p_REPLN_Rec.attribute23;
3096 END IF;
3097
3098 IF (p_REPLN_Rec.attribute24 is null) or (p_REPLN_Rec.attribute24 = FND_API.G_MISS_CHAR) then
3099 IF nvl(p_old_repln_rec.attribute24,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3100 l_attribute24 := p_old_repln_rec.attribute24;
3101 END IF;
3102 ELSE
3103 l_attribute24 := p_REPLN_Rec.attribute24;
3104 END IF;
3105
3106 IF (p_REPLN_Rec.attribute25 is null) or (p_REPLN_Rec.attribute25 = FND_API.G_MISS_CHAR) then
3107 IF nvl(p_old_repln_rec.attribute25,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3108 l_attribute25 := p_old_repln_rec.attribute25;
3109 END IF;
3110 ELSE
3111 l_attribute25 := p_REPLN_Rec.attribute25;
3112 END IF;
3113
3114 IF (p_REPLN_Rec.attribute26 is null) or (p_REPLN_Rec.attribute26 = FND_API.G_MISS_CHAR) then
3115 IF nvl(p_old_repln_rec.attribute26,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3116 l_attribute26 := p_old_repln_rec.attribute26;
3117 END IF;
3118 ELSE
3119 l_attribute26 := p_REPLN_Rec.attribute26;
3120 END IF;
3121
3122 IF (p_REPLN_Rec.attribute27 is null) or (p_REPLN_Rec.attribute27 = FND_API.G_MISS_CHAR) then
3123 IF nvl(p_old_repln_rec.attribute27,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3124 l_attribute27 := p_old_repln_rec.attribute27;
3125 END IF;
3126 ELSE
3127 l_attribute27 := p_REPLN_Rec.attribute27;
3128 END IF;
3129
3130 IF (p_REPLN_Rec.attribute28 is null) or (p_REPLN_Rec.attribute28 = FND_API.G_MISS_CHAR) then
3131 IF nvl(p_old_repln_rec.attribute28,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3132 l_attribute28 := p_old_repln_rec.attribute28;
3133 END IF;
3134 ELSE
3135 l_attribute28 := p_REPLN_Rec.attribute28;
3136 END IF;
3137
3138 IF (p_REPLN_Rec.attribute29 is null) or (p_REPLN_Rec.attribute29 = FND_API.G_MISS_CHAR) then
3139 IF nvl(p_old_repln_rec.attribute29,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3140 l_attribute29 := p_old_repln_rec.attribute29;
3141 END IF;
3142 ELSE
3143 l_attribute29 := p_REPLN_Rec.attribute29;
3144 END IF;
3145
3146 IF (p_REPLN_Rec.attribute30 is null) or (p_REPLN_Rec.attribute30 = FND_API.G_MISS_CHAR) then
3147 IF nvl(p_old_repln_rec.attribute30,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
3148 l_attribute30 := p_old_repln_rec.attribute30;
3149 END IF;
3150 ELSE
3151 l_attribute30 := p_REPLN_Rec.attribute30;
3152 END IF;
3153 Csd_Repairs_Util.Convert_to_DEF_Rec_Type
3154 (p_attribute_category => l_attribute_category,
3155 p_attribute1 => l_attribute1,
3156 p_attribute2 => l_attribute2,
3157 p_attribute3 => l_attribute3,
3158 p_attribute4 => l_attribute4,
3159 p_attribute5 => l_attribute5,
3160 p_attribute6 => l_attribute6,
3161 p_attribute7 => l_attribute7,
3162 p_attribute8 => l_attribute8,
3163 p_attribute9 => l_attribute9,
3164 p_attribute10 => l_attribute10,
3165 p_attribute11 => l_attribute11,
3166 p_attribute12 => l_attribute12,
3167 p_attribute13 => l_attribute13,
3168 p_attribute14 => l_attribute14,
3169 p_attribute15 => l_attribute15,
3170 p_attribute16 => l_attribute16, --DFF changes, subhat(bug#7497907)
3171 p_attribute17 => l_attribute17,
3172 p_attribute18 => l_attribute18,
3173 p_attribute19 => l_attribute19,
3174 p_attribute20 => l_attribute20,
3175 p_attribute21 => l_attribute21,
3176 p_attribute22 => l_attribute22,
3177 p_attribute23 => l_attribute23,
3178 p_attribute24 => l_attribute24,
3179 p_attribute25 => l_attribute25,
3180 p_attribute26 => l_attribute26,
3181 p_attribute27 => l_attribute27,
3182 p_attribute28 => l_attribute28,
3183 p_attribute29 => l_attribute29,
3184 p_attribute30 => l_attribute30,
3185 x_DEF_Rec => x_DFF_Rec
3186 );
3187
3188 -- bug#7242791, subhat (This FP also contains the fix provided in bug#7438725)
3189 -- the defaulting in the FND Flex API should happen only if the mode is
3190 -- create and profile CSD: Enable Flexfield Defaulting for Repair Orders Flexfield
3191 -- is set to Yes.
3192 IF p_validation_mode = Jtf_Plsql_Api.G_CREATE THEN
3193 if nvl(fnd_profile.value('CSD_RO_DFF_DEF'),'N') = 'Y' then
3194 l_valid_def_rec := Csd_Repairs_Util.Is_DescFlex_Valid
3195 (p_api_name => 'Create_Repair_Order',
3196 p_desc_flex_name => 'CSD_REPAIRS',
3197 p_attr_values => x_dff_rec,
3198 p_validate_only => FND_API.G_FALSE);
3199 else
3200 l_valid_def_rec := CSD_REPAIRS_UTIL.Is_DescFlex_Valid
3201 (p_api_name => 'Create_Repair_Order',
3202 p_desc_flex_name => 'CSD_REPAIRS',
3203 p_attr_values => x_dff_rec,
3204 p_validate_only => FND_API.G_TRUE
3205 );
3206 end if;
3207 ELSE
3208 l_valid_def_rec := CSD_REPAIRS_UTIL.Is_DescFlex_Valid
3209 (p_api_name => 'Update_Repair_Order',
3210 p_desc_flex_name => 'CSD_REPAIRS',
3211 p_attr_values => x_dff_rec,
3212 p_validate_only => FND_API.G_TRUE
3213 );
3214 END IF;
3215 -- Check validation status from the above procedure call
3216 IF NOT (l_valid_def_rec) THEN
3217 RAISE Fnd_Api.G_EXC_ERROR;
3218 END IF;
3219
3220 END IF; -- End of Validation Level IF Block
3221 --
3222 EXCEPTION
3223 WHEN Fnd_Api.G_EXC_ERROR THEN
3224 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
3225 ( P_API_NAME => L_API_NAME
3226 ,P_PKG_NAME => G_PKG_NAME
3227 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
3228 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
3229 ,X_MSG_COUNT => X_MSG_COUNT
3230 ,X_MSG_DATA => X_MSG_DATA
3231 ,X_RETURN_STATUS => X_RETURN_STATUS);
3232
3233 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3234 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
3235 (P_API_NAME => L_API_NAME
3236 ,P_PKG_NAME => G_PKG_NAME
3237 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
3238 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
3239 ,X_MSG_COUNT => X_MSG_COUNT
3240 ,X_MSG_DATA => X_MSG_DATA
3241 ,X_RETURN_STATUS => X_RETURN_STATUS);
3242
3243 WHEN OTHERS THEN
3244 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
3245 (P_API_NAME => L_API_NAME
3246 ,P_PKG_NAME => G_PKG_NAME
3247 ,P_EXCEPTION_LEVEL => Jtf_Plsql_Api.G_EXC_OTHERS
3248 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
3249 ,X_MSG_COUNT => X_MSG_COUNT
3250 ,X_MSG_DATA => X_MSG_DATA
3251 ,X_RETURN_STATUS => X_RETURN_STATUS);
3252
3253 END Validate_Repairs;
3254
3255 /*-------------------------------------------------------------------*/
3256 /* procedure name: Copy_Attachments */
3257 /* description : Thi procedure copies all the attachements from */
3258 /* the original repair order to the new repair */
3259 /* order. */
3260 /* */
3261 /* */
3262 /* p_api_version Standard IN param */
3263 /* p_commit Standard IN param */
3264 /* p_init_msg_list Standard IN param */
3265 /* p_validation_level Standard IN param */
3266 /* p_original_ro_id Original Repair Line Id */
3267 /* p_new_ro_id New Repair Line Id */
3268 /* x_return_status Standard OUT param */
3269 /* x_msg_count Standard OUT param */
3270 /* x_msg_data Standard OUT param */
3271 /* */
3272 /*-------------------------------------------------------------------*/
3273 PROCEDURE Copy_Attachments
3274 ( p_api_version IN NUMBER,
3275 p_commit IN VARCHAR2,
3276 p_init_msg_list IN VARCHAR2,
3277 p_validation_level IN NUMBER,
3278 x_return_status OUT NOCOPY VARCHAR2,
3279 x_msg_count OUT NOCOPY NUMBER,
3280 x_msg_data OUT NOCOPY VARCHAR2,
3281 p_original_ro_id IN NUMBER,
3282 p_new_ro_id IN NUMBER)
3283 IS
3284 CURSOR doclist IS
3285 SELECT fad.seq_num,
3286 fad.document_id,
3287 fad.attached_document_id,
3288 fad.attribute_category,
3289 fad.attribute1,
3290 fad.attribute2,
3291 fad.attribute3,
3292 fad.attribute4,
3293 fad.attribute5,
3294 fad.attribute6,
3295 fad.attribute7,
3296 fad.attribute8,
3297 fad.attribute9,
3298 fad.attribute10,
3299 fad.attribute11,
3300 fad.attribute12,
3301 fad.attribute13,
3302 fad.attribute14,
3303 fad.attribute15,
3304 fad.column1,
3305 fad.automatically_added_flag,
3306 fd.datatype_id,
3307 fd.category_id,
3308 fd.security_type,
3309 fd.security_id,
3310 fd.publish_flag,
3311 fd.image_type,
3312 fd.storage_type,
3313 fd.usage_type,
3314 fd.start_date_active,
3315 fd.end_date_active,
3316 fd.request_id,
3317 fd.program_application_id,
3318 fd.program_id,
3319 fdtl.LANGUAGE,
3320 fdtl.description,
3321 fdtl.file_name,
3322 fdtl.media_id,
3323 fdtl.doc_attribute_category dattr_cat,
3324 fdtl.doc_attribute1 dattr1,
3325 fdtl.doc_attribute2 dattr2,
3326 fdtl.doc_attribute3 dattr3,
3327 fdtl.doc_attribute4 dattr4,
3328 fdtl.doc_attribute5 dattr5,
3329 fdtl.doc_attribute6 dattr6,
3330 fdtl.doc_attribute7 dattr7,
3331 fdtl.doc_attribute8 dattr8,
3332 fdtl.doc_attribute9 dattr9,
3333 fdtl.doc_attribute10 dattr10,
3334 fdtl.doc_attribute11 dattr11,
3335 fdtl.doc_attribute12 dattr12,
3336 fdtl.doc_attribute13 dattr13,
3337 fdtl.doc_attribute14 dattr14,
3338 fdtl.doc_attribute15 dattr15
3339 FROM fnd_attached_documents fad,
3340 fnd_documents fd,
3341 fnd_documents_tl fdtl
3342 WHERE fad.document_id = fd.document_id
3343 AND fd.document_id = fdtl.document_id
3344 AND fdtl.LANGUAGE = USERENV('LANG')
3345 AND fad.pk1_value = TO_CHAR(p_original_ro_id);
3346
3347 CURSOR shorttext (mid NUMBER) IS
3348 SELECT short_text
3349 FROM fnd_documents_short_text
3350 WHERE media_id = mid;
3351
3352 CURSOR longtext (mid NUMBER) IS
3353 SELECT long_text
3354 FROM fnd_documents_long_text
3355 WHERE media_id = mid;
3356
3357 CURSOR fnd_lobs_cur (mid NUMBER) IS
3358 SELECT file_id,
3359 file_name,
3360 file_content_type,
3361 upload_date,
3362 expiration_date,
3363 program_name,
3364 program_tag,
3365 file_data,
3366 LANGUAGE,
3367 oracle_charset,
3368 file_format
3369 FROM fnd_lobs
3370 WHERE file_id = mid;
3371
3372 l_media_id_tmp NUMBER;
3373 l_document_id_tmp NUMBER;
3374 l_row_id_tmp VARCHAR2(30);
3375 l_short_text_tmp VARCHAR2(2000);
3376 l_long_text_tmp LONG;
3377 l_fnd_lobs_rec fnd_lobs_cur%ROWTYPE;
3378 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Attachments';
3379 l_api_version CONSTANT NUMBER := 1.0;
3380 l_debug_level NUMBER := Fnd_Log.G_CURRENT_RUNTIME_LEVEL;
3381 l_procedure_level NUMBER := Fnd_Log.LEVEL_PROCEDURE;
3382 l_statement_level NUMBER := Fnd_Log.LEVEL_STATEMENT;
3383 l_event_level NUMBER := Fnd_Log.LEVEL_EVENT;
3384
3385 BEGIN
3386
3387 -- Standard Start of API savepoint
3388 SAVEPOINT copy_Attachments;
3389
3390 IF(Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level) THEN
3391 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
3392 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3393 'Entered Copy_Attachments API');
3394 END IF;
3395
3396 -- Standard call to check for call compatibility.
3397 IF NOT Fnd_Api.Compatible_API_Call (l_api_version,
3398 p_api_version,
3399 l_api_name,
3400 G_PKG_NAME)
3401 THEN
3402 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3403 END IF;
3404
3405 -- Initialize message list if p_init_msg_list is set to TRUE.
3406 IF Fnd_Api.to_Boolean(p_init_msg_list) THEN
3407 Fnd_Msg_Pub.initialize;
3408 END IF;
3409
3410 -- Initialize API return status to success
3411 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3412
3413 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3414 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3415 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3416 'Validate Original Repair line id = '||p_original_ro_id);
3417 END IF;
3418
3419 IF NOT (Csd_Process_Util.Validate_rep_line_id
3420 ( p_repair_line_id => p_original_ro_id)) THEN
3421 RAISE Fnd_Api.G_EXC_ERROR;
3422 END IF;
3423
3424 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3425 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3426 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3427 'Validation of Original Repair line id completed successfully');
3428 END IF;
3429
3430 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3431 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3432 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3433 'Validate New Repair line id = '||p_new_ro_id);
3434 END IF;
3435
3436 IF NOT (Csd_Process_Util.Validate_rep_line_id
3437 ( p_repair_line_id => p_new_ro_id)) THEN
3438 RAISE Fnd_Api.G_EXC_ERROR;
3439 END IF;
3440
3441 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3442 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3443 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3444 'Validation of New Repair line id completed successfully');
3445 END IF;
3446
3447 -- Begin API Body
3448 -- Use cursor loop to get all attachments associated with
3449 -- the from_entity (Original Repair Order)
3450
3451 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3452 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3453 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3454 'Open main cursor');
3455 END IF;
3456
3457 IF(Fnd_Log.Level_Event >= Fnd_Log.G_Current_Runtime_Level) THEN
3458 Fnd_Log.STRING(Fnd_Log.Level_Event,
3459 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3460 'Started Copying of Attachments ');
3461 END IF;
3462
3463
3464 FOR docrec IN doclist LOOP
3465
3466 IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3467 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3468 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3469 'Main cursor - Datatype id = '||docrec.datatype_id);
3470
3471 END IF;
3472
3473 -- Data type codes
3474 -- 1 = Short text
3475 -- 2 = Long text
3476 -- 6 = File
3477 -- 5 = Web page
3478 IF (docrec.usage_type = 'O'
3479 AND docrec.datatype_id IN (1,2,6,5) ) THEN
3480 -- Create Documents records
3481
3482 IF ( Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level ) THEN
3483 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
3484 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3485 'Calling the FND_DOCUMENTS_PKG.Insert_Row');
3486 END IF;
3487
3488 Fnd_Documents_Pkg.Insert_Row(l_row_id_tmp,
3489 l_document_id_tmp,
3490 SYSDATE,
3491 Fnd_Global.user_id,
3492 SYSDATE,
3493 Fnd_Global.user_id,
3494 Fnd_Global.user_id,
3495 docrec.datatype_id,
3496 docrec.category_id,
3497 docrec.security_type,
3498 docrec.security_id,
3499 docrec.publish_flag,
3500 docrec.image_type,
3501 docrec.storage_type,
3502 docrec.usage_type,
3503 docrec.start_date_active,
3504 docrec.end_date_active,
3505 docrec.request_id,
3506 docrec.program_application_id,
3507 docrec.program_id,
3508 SYSDATE,
3509 docrec.LANGUAGE,
3510 docrec.description,
3511 docrec.file_name,
3512 l_media_id_tmp,
3513 docrec.dattr_cat, docrec.dattr1,
3514 docrec.dattr2, docrec.dattr3,
3515 docrec.dattr4, docrec.dattr5,
3516 docrec.dattr6, docrec.dattr7,
3517 docrec.dattr8, docrec.dattr9,
3518 docrec.dattr10, docrec.dattr11,
3519 docrec.dattr12, docrec.dattr13,
3520 docrec.dattr14, docrec.dattr15);
3521
3522
3523 docrec.document_id := l_document_id_tmp;
3524
3525 -- Insert data into media tables depending on
3526 -- the data type.
3527 -- 1.If datatype id = 1 (short text) then insert
3528 -- into fnd_documents_short_text.
3529 -- 2.If datatype id = 2 (Long text) then insert
3530 -- into fnd_documents_long_text.
3531 -- 3.If datatype id = 6 ( File ) then insert
3532 -- into fnd_lobs_cur.
3533
3534 -- Duplicate short or long text
3535 IF (docrec.datatype_id = 1) THEN
3536 -- Handle short Text
3537 -- get original data
3538 OPEN shorttext(docrec.media_id);
3539 FETCH shorttext INTO l_short_text_tmp;
3540 CLOSE shorttext;
3541
3542 IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3543 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3544 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3545 'Inserting into fnd_documents_short_text for
3546 document id = '||docrec.document_id);
3547 END IF;
3548
3549 INSERT INTO fnd_documents_short_text (
3550 media_id,
3551 short_text)
3552 VALUES (
3553 l_media_id_tmp,
3554 l_short_text_tmp);
3555
3556 l_media_id_tmp := '';
3557
3558 ELSIF (docrec.datatype_id = 2) THEN
3559 -- Handle long text
3560 -- get original data
3561 OPEN longtext(docrec.media_id);
3562 FETCH longtext INTO l_long_text_tmp;
3563 CLOSE longtext;
3564
3565 IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3566 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3567 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3568 'Inserting into fnd_documents_long_text for
3569 document id = '||docrec.document_id);
3570 END IF;
3571
3572 INSERT INTO fnd_documents_long_text (
3573 media_id,
3574 long_text)
3575 VALUES (
3576 l_media_id_tmp,
3577 l_long_text_tmp);
3578
3579 l_media_id_tmp := '';
3580
3581 ELSIF (docrec.datatype_id=6) THEN
3582
3583 OPEN fnd_lobs_cur(docrec.media_id);
3584 FETCH fnd_lobs_cur
3585 INTO l_fnd_lobs_rec.file_id,
3586 l_fnd_lobs_rec.file_name,
3587 l_fnd_lobs_rec.file_content_type,
3588 l_fnd_lobs_rec.upload_date,
3589 l_fnd_lobs_rec.expiration_date,
3590 l_fnd_lobs_rec.program_name,
3591 l_fnd_lobs_rec.program_tag,
3592 l_fnd_lobs_rec.file_data,
3593 l_fnd_lobs_rec.LANGUAGE,
3594 l_fnd_lobs_rec.oracle_charset,
3595 l_fnd_lobs_rec.file_format;
3596 CLOSE fnd_lobs_cur;
3597
3598 IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3599 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3600 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3601 'Inserting into fnd_lobs for
3602 document id = '||docrec.document_id);
3603 END IF;
3604
3605 INSERT INTO fnd_lobs (
3606 file_id,
3607 file_name,
3608 file_content_type,
3609 upload_date,
3610 expiration_date,
3611 program_name,
3612 program_tag,
3613 file_data,
3614 LANGUAGE,
3615 oracle_charset,
3616 file_format)
3617 VALUES (
3618 l_media_id_tmp,
3619 l_fnd_lobs_rec.file_name,
3620 l_fnd_lobs_rec.file_content_type,
3621 l_fnd_lobs_rec.upload_date,
3622 l_fnd_lobs_rec.expiration_date,
3623 l_fnd_lobs_rec.program_name,
3624 l_fnd_lobs_rec.program_tag,
3625 l_fnd_lobs_rec.file_data,
3626 l_fnd_lobs_rec.LANGUAGE,
3627 l_fnd_lobs_rec.oracle_charset,
3628 l_fnd_lobs_rec.file_format);
3629
3630 l_media_id_tmp := '';
3631
3632 END IF; -- end of duplicating text
3633
3634 END IF; -- end if usage_type = 'O' and datatype in (1,2,6,5)
3635
3636
3637 IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3638 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3639 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3640 'Inserting into fnd_attached_documents for
3641 document id = '||docrec.document_id);
3642 END IF;
3643
3644 -- Create attachment record
3645 INSERT INTO fnd_attached_documents
3646 (attached_document_id,
3647 document_id,
3648 creation_date,
3649 created_by,
3650 last_update_date,
3651 last_updated_by,
3652 last_update_login,
3653 seq_num,
3654 entity_name,
3655 pk1_value,
3656 pk2_value,
3657 pk3_value,
3658 pk4_value,
3659 pk5_value,
3660 automatically_added_flag,
3661 program_application_id,
3662 program_id,
3663 program_update_date,
3664 request_id,
3665 attribute_category,
3666 attribute1, attribute2,
3667 attribute3, attribute4,
3668 attribute5, attribute6,
3669 attribute7, attribute8,
3670 attribute9, attribute10,
3671 attribute11, attribute12,
3672 attribute13, attribute14,
3673 attribute15,
3674 column1)
3675 (SELECT
3676 fnd_attached_documents_s.NEXTVAL,
3677 docrec.document_id,
3678 SYSDATE,
3679 Fnd_Global.user_id,
3680 SYSDATE,
3681 Fnd_Global.user_id,
3682 Fnd_Global.user_id,
3683 docrec.seq_num,
3684 entity_name,
3685 TO_CHAR(p_new_ro_id),
3686 pk2_value,
3687 pk3_value,
3688 pk4_value,
3689 pk5_value,
3690 'Y',
3691 program_application_id,
3692 program_id,
3693 SYSDATE,
3694 request_id,
3695 docrec.attribute_category,
3696 docrec.attribute1, docrec.attribute2,
3697 docrec.attribute3, docrec.attribute4,
3698 docrec.attribute5, docrec.attribute6,
3699 docrec.attribute7, docrec.attribute8,
3700 docrec.attribute9, docrec.attribute10,
3701 docrec.attribute11, docrec.attribute12,
3702 docrec.attribute13, docrec.attribute14,
3703 docrec.attribute15,
3704 docrec.column1
3705 FROM fnd_attached_documents
3706 WHERE attached_document_id = docrec.attached_document_id);
3707
3708 END LOOP; -- end of working through all attachments
3709
3710 IF (shorttext%ISOPEN) THEN
3711 CLOSE shorttext;
3712 END IF;
3713
3714 IF (longtext%ISOPEN) THEN
3715 CLOSE longtext;
3716 END IF;
3717
3718 IF (fnd_lobs_cur%ISOPEN) THEN
3719 CLOSE fnd_lobs_cur;
3720 END IF;
3721
3722 -- Api body ends here
3723
3724 -- Standard check of p_commit.
3725 IF Fnd_Api.To_Boolean( p_commit ) THEN
3726 COMMIT;
3727 END IF;
3728
3729 -- Standard call to get message count and if count is 1, get message info
3730 Fnd_Msg_Pub.Count_And_Get
3731 (p_count => x_msg_count,
3732 p_data => x_msg_data);
3733
3734 IF ( Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3735 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3736 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3737 'Copy Attachments completed successfully ');
3738 END IF;
3739
3740 EXCEPTION
3741 WHEN Fnd_Api.G_EXC_ERROR THEN
3742 ROLLBACK TO Copy_Attachments;
3743 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
3744 Fnd_Msg_Pub.Count_And_Get
3745 (p_count => x_msg_count,
3746 p_data => x_msg_data
3747 );
3748 IF ( Fnd_Log.LEVEL_ERROR >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
3749 Fnd_Log.STRING(Fnd_Log.LEVEL_ERROR,
3750 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3751 'EXC_ERROR ['||x_msg_data||']');
3752 END IF;
3753
3754 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3755 ROLLBACK TO Copy_Attachments;
3756 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
3757 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count,
3758 p_data => x_msg_data );
3759
3760 IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
3761 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
3762 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3763 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
3764 END IF;
3765
3766 WHEN OTHERS THEN
3767 ROLLBACK TO Copy_Attachments;
3768 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
3769 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
3770 THEN
3771 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME ,
3772 l_api_name );
3773 END IF;
3774 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count,
3775 p_data => x_msg_data );
3776
3777 IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
3778 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
3779 'CSD.PLSQL.csd_repairs_pvt.copy_attachments',
3780 'SQL Message ['||SQLERRM||']');
3781 END IF;
3782
3783 END Copy_Attachments;
3784
3785
3786 /*------------------------------------------------------------------*/
3787 /* procedure name: Delete_Attachments */
3788 /* description : This procedure deletes all the attachements */
3789 /* linked with the repair line id. */
3790 /* */
3791 /* p_api_version Standard IN param */
3792 /* p_commit Standard IN param */
3793 /* p_init_msg_list Standard IN param */
3794 /* p_validation_level Standard IN param */
3795 /* p_repair_line_id Repair Line Id */
3796 /* x_return_status Standard OUT param */
3797 /* x_msg_count Standard OUT param */
3798 /* x_msg_data Standard OUT param */
3799 /* */
3800 /*------------------------------------------------------------------*/
3801 PROCEDURE Delete_Attachments
3802 (p_api_version IN NUMBER,
3803 p_commit IN VARCHAR2,
3804 p_init_msg_list IN VARCHAR2,
3805 p_validation_level IN NUMBER,
3806 x_return_status OUT NOCOPY VARCHAR2,
3807 x_msg_count OUT NOCOPY NUMBER,
3808 x_msg_data OUT NOCOPY VARCHAR2,
3809 p_repair_line_id IN NUMBER)
3810 IS
3811
3812 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attachments';
3813 l_api_version CONSTANT NUMBER := 1.0;
3814 l_debug_level NUMBER := Fnd_Log.G_CURRENT_RUNTIME_LEVEL;
3815 l_procedure_level NUMBER := Fnd_Log.LEVEL_PROCEDURE;
3816 l_statement_level NUMBER := Fnd_Log.LEVEL_STATEMENT;
3817 l_event_level NUMBER := Fnd_Log.LEVEL_EVENT;
3818
3819 BEGIN
3820
3821 -- Standard Start of API savepoint
3822 SAVEPOINT Delete_Attachments;
3823
3824 IF(Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level) THEN
3825 Fnd_Log.STRING(Fnd_Log.Level_Procedure,
3826 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3827 'Entered Delete_Attachments API');
3828 END IF;
3829
3830 -- Standard call to check for call compatibility.
3831 IF NOT Fnd_Api.Compatible_API_Call (l_api_version,
3832 p_api_version,
3833 l_api_name,
3834 G_PKG_NAME)
3835 THEN
3836 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3837 END IF;
3838
3839 -- Initialize message list if p_init_msg_list is set to TRUE.
3840 IF Fnd_Api.to_Boolean(p_init_msg_list) THEN
3841 Fnd_Msg_Pub.initialize;
3842 END IF;
3843
3844 -- Initialize API return status to success
3845 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3846
3847 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3848 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3849 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3850 'Validate Repair line id = '||p_repair_line_id);
3851 END IF;
3852
3853 IF NOT (Csd_Process_Util.Validate_rep_line_id
3854 ( p_repair_line_id => p_repair_line_id)) THEN
3855 RAISE Fnd_Api.G_EXC_ERROR;
3856 END IF;
3857
3858 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3859 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3860 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3861 'Validation of Repair line id completed successfully');
3862 END IF;
3863
3864 --
3865 -- Begin API Body
3866 --
3867
3868 -- Delete from FND_DOCUMENTS_SHORT_TEXT table
3869 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3870 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3871 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3872 'Deleting from fnd_documents_short_text');
3873 END IF;
3874
3875 IF(Fnd_Log.Level_Event >= Fnd_Log.G_Current_Runtime_Level) THEN
3876 Fnd_Log.STRING(Fnd_Log.Level_Event,
3877 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3878 'Started Deleting of Attachments');
3879 END IF;
3880
3881
3882 DELETE FROM fnd_documents_short_text
3883 WHERE media_id IN
3884 (SELECT fdtl.media_id
3885 FROM fnd_documents_tl fdtl,
3886 fnd_documents fd,
3887 fnd_attached_documents fad
3888 WHERE fdtl.document_id = fd.document_id
3889 AND fd.document_id = fad.document_id
3890 AND fd.usage_type = 'O'
3891 AND fd.datatype_id = 1
3892 AND fad.pk1_value = TO_CHAR(p_repair_line_id));
3893
3894 -- Delete from FND_DOCUMENTS_LONG_TEXT table
3895 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3896 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3897 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3898 'Deleting from fnd_documents_long_text');
3899 END IF;
3900
3901 DELETE FROM fnd_documents_long_text
3902 WHERE media_id IN
3903 (SELECT fdtl.media_id
3904 FROM fnd_documents_tl fdtl,
3905 fnd_documents fd,
3906 fnd_attached_documents fad
3907 WHERE fdtl.document_id = fd.document_id
3908 AND fd.document_id = fad.document_id
3909 AND fd.usage_type = 'O'
3910 AND fd.datatype_id = 2
3911 AND fad.pk1_value = TO_CHAR(p_repair_line_id));
3912
3913 -- Delete from FND_DOCUMENTS_LONG_RAW table
3914 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3915 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3916 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3917 'Deleting from fnd_documents_long_raw');
3918 END IF;
3919
3920 DELETE FROM fnd_documents_long_raw
3921 WHERE media_id IN
3922 (SELECT fdtl.media_id
3923 FROM fnd_documents_tl fdtl,
3924 fnd_documents fd,
3925 fnd_attached_documents fad
3926 WHERE fdtl.document_id = fd.document_id
3927 AND fd.document_id = fad.document_id
3928 AND fd.usage_type = 'O'
3929 AND fd.datatype_id IN (3,4)
3930 AND fad.pk1_value = TO_CHAR(p_repair_line_id));
3931
3932
3933 -- Delete from FND_LOBS table
3934 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3935 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3936 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3937 'Deleting from fnd_lobs');
3938 END IF;
3939
3940 DELETE FROM fnd_lobs
3941 WHERE file_id IN
3942 (SELECT fdtl.media_id
3943 FROM fnd_documents_tl fdtl,
3944 fnd_documents fd,
3945 fnd_attached_documents fad
3946 WHERE fdtl.document_id = fd.document_id
3947 AND fd.document_id = fad.document_id
3948 AND fd.usage_type = 'O'
3949 AND fd.datatype_id = 6
3950 AND fad.pk1_value = TO_CHAR(p_repair_line_id));
3951
3952 -- Delete from FND_DOCUMENTS_TL table
3953 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3954 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3955 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3956 'Deleting from fnd_documents_tl');
3957 END IF;
3958
3959 DELETE FROM fnd_documents_tl
3960 WHERE document_id IN
3961 (SELECT fad.document_id
3962 FROM fnd_attached_documents fad, fnd_documents fd
3963 WHERE fad.document_id = fd.document_id
3964 AND fd.usage_type = 'O'
3965 AND fad.pk1_value = TO_CHAR(p_repair_line_id));
3966
3967 -- Delete from FND_DOCUMENTS table
3968 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3969 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3970 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3971 'Deleting from fnd_documents');
3972 END IF;
3973
3974 DELETE FROM fnd_documents
3975 WHERE usage_type = 'O'
3976 AND document_id IN
3977 (SELECT document_id
3978 FROM fnd_attached_documents fad
3979 WHERE fad.pk1_value = TO_CHAR(p_repair_line_id));
3980
3981 -- delete from FND_ATTACHED_DOCUMENTS table
3982 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
3983 Fnd_Log.STRING(Fnd_Log.Level_Statement,
3984 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
3985 'Deleting from fnd_attached_documents');
3986 END IF;
3987
3988 DELETE FROM fnd_attached_documents fad
3989 WHERE fad.pk1_value = TO_CHAR(p_repair_line_id);
3990
3991 -- Api body ends here
3992
3993 -- Standard check of p_commit.
3994 IF Fnd_Api.To_Boolean( p_commit ) THEN
3995 COMMIT;
3996 END IF;
3997
3998 -- Standard call to get message count and if count is 1, get message info
3999 Fnd_Msg_Pub.Count_And_Get
4000 (p_count => x_msg_count,
4001 p_data => x_msg_data);
4002
4003 IF(Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level) THEN
4004 Fnd_Log.STRING(Fnd_Log.Level_Statement,
4005 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
4006 'Delete_Attachments completed successfully');
4007 END IF;
4008
4009 EXCEPTION
4010 WHEN Fnd_Api.G_EXC_ERROR THEN
4011 ROLLBACK TO Delete_Attachments;
4012 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
4013 Fnd_Msg_Pub.Count_And_Get
4014 (p_count => x_msg_count,
4015 p_data => x_msg_data
4016 );
4017 IF ( Fnd_Log.LEVEL_ERROR >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
4018 Fnd_Log.STRING(Fnd_Log.LEVEL_ERROR,
4019 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
4020 'EXC_ERROR ['||x_msg_data||']');
4021 END IF;
4022
4023 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
4024 ROLLBACK TO Delete_Attachments;
4025 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
4026 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count,
4027 p_data => x_msg_data );
4028
4029 IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
4030 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
4031 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
4032 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
4033 END IF;
4034
4035 WHEN OTHERS THEN
4036 ROLLBACK TO Delete_Attachments;
4037 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
4038 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
4039 THEN
4040 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME ,
4041 l_api_name );
4042 END IF;
4043 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count,
4044 p_data => x_msg_data );
4045
4046 IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
4047 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
4048 'CSD.PLSQL.csd_repairs_pvt.delete_attachments',
4049 'SQL Message ['||SQLERRM||']');
4050 END IF;
4051
4052
4053 END Delete_Attachments;
4054
4055
4056 -- R12 development changes begin...
4057 -- *******************************************************
4058 -- API Name: update_ro_status
4059 -- Type : Private
4060 -- Pre-Req : None
4061 -- Parameters:
4062 -- IN
4063 -- p_api_version IN NUMBER,
4064 -- p_commit IN VARCHAR2,
4065 -- p_init_msg_list IN VARCHAR2,
4066 -- p_validation_level IN NUMBER,
4067 -- p_repair_status_rec IN CSD_REPAIRS_PUB.REPAIR_STATUS_REC,
4068 -- p_status_control_rec IN CSD_REPAIRS_PUB.STATUS_UPD_CONTROL_REC,
4069 -- OUT
4070 -- x_return_status
4071 -- x_msg_count
4072 -- x_msg_data
4073 -- x_object_version_number OUT NUMBER
4074 --
4075 -- Version : Current version 1.0
4076 -- Initial Version 1.0
4077 --
4078 -- Description : This API updates the repair status to a given value.
4079 -- It checks for the open tasks/wipjobs based on the input
4080 -- flag p_check_task_wip in the status control record.
4081 --
4082 --
4083 -- ***********************************************************
4084 PROCEDURE UPDATE_RO_STATUS
4085 (
4086 p_api_version IN NUMBER,
4087 p_commit IN VARCHAR2,
4088 p_init_msg_list IN VARCHAR2,
4089 p_validation_level IN NUMBER,
4090 x_return_status OUT NOCOPY VARCHAR2,
4091 x_msg_count OUT NOCOPY NUMBER,
4092 x_msg_data OUT NOCOPY VARCHAR2,
4093 p_repair_status_Rec IN Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE,
4094 p_status_control_rec IN Csd_Repairs_Pub.STATUS_UPD_CONTROL_REC_TYPE,
4095 x_object_version_number OUT NOCOPY NUMBER
4096 ) IS
4097 l_api_version_number CONSTANT NUMBER := 1.0;
4098 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_RO_STATUS';
4099 C_CLOSED_STATE CONSTANT VARCHAR2 (1) := 'C';
4100 l_return_status VARCHAR2 (1) ;
4101 l_msg_count NUMBER;
4102 l_msg_data VARCHAR2 (2000);
4103 l_incident_id NUMBER;
4104 l_repair_type_id NUMBER;
4105 l_object_version_number NUMBER;
4106 l_repair_status_Rec Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE;
4107
4108 --Define cursors
4109
4110 --Get the flow status id from csd_repairs
4111 CURSOR ro_details_cur(p_repair_line_id NUMBER) IS
4112 SELECT FLOW_STATUS_ID, REPAIR_TYPE_ID
4113 FROM CSD_REPAIRS
4114 WHERE REPAIR_LINE_ID = p_repair_line_id;
4115
4116 -- Get state, state is the status_code in the scehma. status is flow_status
4117 CURSOR flow_stat_cur(p_repair_status_id VARCHAR2) IS
4118 SELECT STATUS_CODE
4119 FROM CSD_FLOW_STATUSES_B
4120 WHERE FLOW_STATUS_ID = p_repair_status_id;
4121
4122
4123 BEGIN
4124 --------------------Standard stuff -------------------------
4125 IF (Fnd_Log.level_procedure >= Fnd_Log.g_current_runtime_level) THEN
4126 Fnd_Log.STRING (Fnd_Log.level_procedure,
4127 'csd.plsql.csd_repairs_pvt.update_ro_status.begin',
4128 'Entering update_ro_status private api');
4129 END IF;
4130 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
4131 Fnd_Msg_Pub.initialize;
4132 END IF;
4133 IF NOT Fnd_Api.compatible_api_call(l_api_version_number, p_api_version, l_api_name, g_pkg_name) THEN
4134 RAISE Fnd_Api.g_exc_unexpected_error;
4135 END IF;
4136
4137 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4138 SAVEPOINT update_ro_status_pvt;
4139
4140 l_repair_status_Rec := p_repair_status_rec;
4141
4142 ------------Convert Values to Id----------------------------------------------
4143 IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
4144 Fnd_Log.STRING (Fnd_Log.level_event,
4145 'csd.plsql.csd_repairs_pvt.update_ro_status',
4146 '-----step 1: Value to Id conversion');
4147 END IF;
4148 Csd_Repairs_Util.Convert_Status_Val_To_Id(p_repair_status_rec,
4149 l_repair_status_rec,
4150 l_return_status);
4151
4152 ------------Default/Validate Input parameters----------------------------------------------
4153 IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
4154 Fnd_Log.STRING (Fnd_Log.level_event,
4155 'csd.plsql.csd_repairs_pvt.update_ro_status',
4156 '-----step 2: Validate input ');
4157 END IF;
4158
4159 IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
4160 Fnd_Log.STRING (Fnd_Log.level_event,
4161 'csd.plsql.csd_repairs_pvt.update_ro_status',
4162 '-----step 3: get required repair order values for update private api,ro['
4163 ||l_repair_status_rec.repair_line_id||'],status['
4164 ||l_repair_status_rec.repair_status||']');
4165
4166 END IF;
4167
4168 OPEN ro_details_cur(l_repair_status_rec.repair_line_id);
4169 FETCH ro_Details_cur INTO l_repair_status_rec.from_status_id, l_repair_type_id;
4170 IF(ro_details_cur%NOTFOUND) THEN
4171 CLOSE ro_details_cur;
4172 Fnd_Message.SET_NAME('CSD','CSD_INVALID_REPAIR_ORDER');
4173 Fnd_Msg_Pub.ADD;
4174 RAISE Fnd_Api.G_EXC_ERROR;
4175 END IF;
4176 CLOSE ro_details_cur;
4177
4178 OPEN flow_stat_cur(l_repair_status_Rec.repair_status_id);
4179 FETCH flow_stat_cur INTO l_repair_status_rec.repair_state;
4180 IF(flow_stat_cur%NOTFOUND) THEN
4181 CLOSE flow_stat_cur;
4182 Fnd_Message.SET_NAME('CSD','CSD_INVALID_FLOW_STATUS');
4183 Fnd_Msg_Pub.ADD;
4184 RAISE Fnd_Api.G_EXC_ERROR;
4185 END IF;
4186 CLOSE flow_stat_cur;
4187
4188 IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
4189 Fnd_Log.STRING (Fnd_Log.level_event,
4190 'csd.plsql.csd_repairs_pvt.update_ro_status',
4191 '-----step 4: Checking for open jobs/tasks,ro['
4192 ||l_repair_status_rec.repair_line_id||'],state['
4193 ||l_repair_status_rec.repair_state||']');
4194 END IF;
4195 ------------Validate Input parameters: record level validation------------------------------
4196 IF(p_status_control_Rec.check_task_wip = 'Y') THEN
4197
4198 Csd_Repairs_Util.CHECK_TASK_N_WIPJOB (
4199 p_repair_line_id => l_repair_status_rec.repair_line_id,
4200 p_repair_status => l_repair_status_rec.repair_state,
4201 x_return_status => l_return_status,
4202 x_msg_count => l_msg_count,
4203 x_msg_data => l_msg_data );
4204
4205 IF l_return_status = Fnd_Api.G_RET_STS_ERROR THEN
4206 RAISE Fnd_Api.G_EXC_ERROR;
4207 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
4208 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4209 END IF;
4210
4211
4212 END IF;
4213
4214
4215 ------------Call other private api to perform the status update------------------------------
4216 /*----------------------Vinay says the api update_flow_Status has logic to close ro status.*/
4217 IF (Fnd_Log.level_event >= Fnd_Log.g_current_runtime_level) THEN
4218 Fnd_Log.STRING (Fnd_Log.level_event,
4219 'csd.plsql.csd_repairs_pvt.update_ro_status',
4220 '-----step 5a: Calling another private api to validate and update status');
4221 END IF;
4222 Csd_Repairs_Pvt.UPDATE_FLOW_STATUS (
4223 p_api_version => 1.0,
4224 p_commit => Fnd_api.g_false,
4225 p_init_msg_list => Fnd_Api.g_false,
4226 p_validation_level => Fnd_Api.g_valid_level_full,
4227 x_return_status => l_return_status,
4228 x_msg_count => l_msg_count,
4229 x_msg_data => l_msg_data,
4230 p_repair_line_id => l_repair_status_rec.repair_line_id,
4231 p_repair_type_id => l_repair_type_id,
4232 p_from_flow_status_id => l_repair_status_rec.from_status_id,
4233 p_to_flow_status_id => l_repair_status_rec.repair_status_id,
4234 p_reason_code => l_repair_status_rec.reason_code,
4235 p_comments => l_repair_status_rec.comments,
4236 p_check_access_flag => 'Y',
4237 p_object_version_number => l_repair_status_rec.object_version_number,
4238 x_object_version_number => x_object_version_number) ;
4239
4240 IF(l_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
4241 RAISE Fnd_Api.G_EXC_ERROR;
4242 ELSIF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
4243 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4244 END IF;
4245
4246
4247 -- Api body ends here
4248
4249 -- Standard check of p_commit.
4250 IF Fnd_Api.to_boolean (p_commit) THEN
4251 COMMIT WORK;
4252 END IF;
4253
4254 -- Standard call to get message count and IF count is get message info.
4255 Fnd_Msg_Pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4256
4257 IF (Fnd_Log.level_procedure >= Fnd_Log.g_current_runtime_level) THEN
4258 Fnd_Log.STRING (Fnd_Log.level_procedure,
4259 'csd.plsql.csd_repairs_pvt.update_ro_status',
4260 'Leaving update_ro_Status private api');
4261 END IF;
4262
4263 EXCEPTION
4264 WHEN Fnd_Api.g_exc_error
4265 THEN
4266 x_return_status := Fnd_Api.G_RET_STS_ERROR;
4267 ROLLBACK TO update_ro_status_pvt;
4268 Fnd_Msg_Pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4269 IF (Fnd_Log.level_error >= Fnd_Log.g_current_runtime_level) THEN
4270 Fnd_Log.STRING (Fnd_Log.level_error,
4271 'csd.plsql.csd_repairs_pvt.update_ro_status',
4272 'EXC_ERROR[' || x_msg_data || ']');
4273 END IF;
4274 WHEN Fnd_Api.g_exc_unexpected_error
4275 THEN
4276 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4277 ROLLBACK TO update_ro_status_pvt;
4278 Fnd_Msg_Pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4279
4280 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level) THEN
4281 Fnd_Log.STRING (Fnd_Log.level_exception,
4282 'csd.plsql.csd_repairs_pvt.update_ro_status',
4283 'EXC_UNEXP_ERROR[' || x_msg_data || ']');
4284 END IF;
4285 WHEN OTHERS
4286 THEN
4287 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4288 ROLLBACK TO update_ro_status_pvt;
4289
4290 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error) THEN
4291 Fnd_Msg_Pub.add_exc_msg (g_pkg_name, l_api_name);
4292 END IF;
4293
4294 Fnd_Msg_Pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4295
4296 IF (Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level) THEN
4297 Fnd_Log.STRING (Fnd_Log.level_exception,
4298 'csd.plsql.csd_repairs_pvt.update_ro_status',
4299 'SQL MEssage[' || SQLERRM || ']');
4300 END IF;
4301 END UPDATE_RO_STATUS;
4302
4303 PROCEDURE Update_Flow_Status (
4304 p_api_version IN NUMBER,
4305 p_commit IN VARCHAR2,
4306 p_init_msg_list IN VARCHAR2,
4307 p_validation_level IN NUMBER,
4308 x_return_status OUT NOCOPY VARCHAR2,
4309 x_msg_count OUT NOCOPY NUMBER,
4310 x_msg_data OUT NOCOPY VARCHAR2,
4311 p_repair_line_id IN NUMBER,
4312 p_repair_type_id IN NUMBER,
4313 p_from_flow_status_id IN NUMBER,
4314 p_to_flow_status_id IN NUMBER,
4315 p_reason_code IN VARCHAR2,
4316 p_comments IN VARCHAR2,
4317 p_check_access_flag IN VARCHAR2,
4318 p_object_version_number IN NUMBER,
4319 x_object_version_number OUT NOCOPY NUMBER
4320 ) IS
4321
4322 -- CONSTANTS --
4323 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.update_flow_status';
4324 lc_api_name CONSTANT VARCHAR2(30) := 'Update_Flow_Status';
4325 lc_api_version CONSTANT NUMBER := 1.0;
4326 lc_update_fs_event_code CONSTANT VARCHAR2(5) := 'SC';
4327 lc_status_code_closed CONSTANT VARCHAR2(1) := 'C';
4328
4329 -- VARIABLES --
4330 l_flwsts_tran_id NUMBER := NULL;
4331 l_wf_item_type VARCHAR2(8) := NULL;
4332 l_wf_process_name VARCHAR2(30) := NULL;
4333 l_reason_required_flag VARCHAR2(1) := NULL;
4334 l_capture_activity_flag VARCHAR2(1) := NULL;
4335 l_allow_all_resp_flag VARCHAR2(1) := NULL;
4336 l_to_status_code VARCHAR2(30) := NULL;
4337 l_to_flow_status_code VARCHAR2(30) := NULL;
4338 l_from_flow_status_code VARCHAR2(30) := NULL;
4339 l_wf_item_key VARCHAR2(240) := NULL;
4340
4341 l_flwsts_wf_rec Flwsts_Wf_Rec_Type;
4342 l_ro_status_bevent_rec RO_STATUS_BEVENT_REC_TYPE;
4343 l_repair_milestone_rec CSD_REPAIR_MILESTONES_PVT.REPAIR_MILESTONE_REC_TYPE;
4344
4345 x_repair_history_id NUMBER;
4346 x_repair_milestone_id NUMBER;
4347 l_reason_meaning VARCHAR2(80) := NULL;
4348
4349 -- CURSOR --
4350 -- Gets all flow status transition details.
4351 CURSOR c_get_trans_details IS
4352 SELECT FS_TRANS.flwsts_tran_id,
4353 FS_TRANS.wf_item_type,
4354 FS_TRANS.wf_process_name,
4355 FS_TRANS.reason_required_flag,
4356 FS_TRANS.capture_activity_flag,
4357 FS_TRANS.allow_all_resp_flag,
4358 TO_FS_B.status_code,
4359 TO_FS_B.flow_status_code to_flow_status_code,
4360 FROM_FS_B.flow_status_code from_flow_status_code
4361 FROM CSD_FLWSTS_TRANS_B FS_TRANS,
4362 CSD_FLOW_STATUSES_B TO_FS_B,
4363 CSD_FLOW_STATUSES_B FROM_FS_B
4364 WHERE FS_TRANS.from_flow_status_id = p_from_flow_status_id AND
4365 FS_TRANS.to_flow_status_id = p_to_flow_status_id AND
4366 FS_TRANS.repair_type_id = p_repair_type_id AND
4367 TO_FS_B.flow_status_id = FS_TRANS.to_flow_status_id AND
4368 FROM_FS_B.flow_status_id = FS_TRANS.from_flow_status_id;
4369
4370 -- Query to validate the reason code
4371 CURSOR c_check_status_reason IS
4372 SELECT RSN_LKUP.meaning reason
4373 FROM FND_LOOKUPS RSN_LKUP
4374 WHERE
4375 RSN_LKUP.lookup_type = 'CSD_REASON' AND
4376 RSN_LKUP.lookup_code = p_reason_code AND
4377 RSN_LKUP.enabled_flag = 'Y' AND
4378 TRUNC(SYSDATE) BETWEEN
4379 TRUNC(NVL(RSN_LKUP.start_date_active, SYSDATE)) AND
4380 TRUNC(NVL(RSN_LKUP.end_date_active, SYSDATE));
4381
4382 -- Query to get all milestones codes
4383 CURSOR c_get_flwsts_miles (p_flwsts_tran_id NUMBER) IS
4384 SELECT milestone_code
4385 FROM CSD_FLWSTS_TRAN_MILES
4386 WHERE flwsts_tran_id = p_flwsts_tran_id;
4387
4388 -- swai: bug 6937272( FP of 6882484)
4389 -- Query to get current object version number of repair order
4390 CURSOR c_get_object_version_number (p_repair_line_id NUMBER) IS
4391 SELECT object_version_number
4392 FROM CSD_REPAIRS
4393 WHERE repair_line_id = p_repair_line_id;
4394
4395 BEGIN
4396
4397 -- Standard Start of API savepoint
4398 SAVEPOINT Update_Flow_Status;
4399
4400 -- Standard call to check for call compatibility.
4401 IF NOT Fnd_Api.Compatible_API_Call (lc_api_version,
4402 p_api_version,
4403 lc_api_name ,
4404 G_PKG_NAME )
4405 THEN
4406 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4407 END IF;
4408
4409 -- Initialize message list if p_init_msg_list is set to TRUE.
4410 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
4411 Fnd_Msg_Pub.initialize;
4412 END IF;
4413
4414 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4415 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.BEGIN',
4416 'Entered Update_Flow_Status');
4417 END IF;
4418
4419 -- Initialize API return status to success
4420 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
4421
4422 -- Api body starts
4423
4424 -- STEP 1: Validate Mandatory Parameters.
4425
4426 -- Check the required parameters
4427 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4428 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4429 'Checking required parameters');
4430 END IF;
4431
4432 -- Check the required parameters
4433 Csd_Process_Util.Check_Reqd_Param
4434 ( p_param_value => p_repair_line_id,
4435 p_param_name => 'P_REPAIR_LINE_ID',
4436 p_api_name => lc_api_name);
4437
4438 Csd_Process_Util.Check_Reqd_Param
4439 ( p_param_value => p_from_flow_status_id,
4440 p_param_name => 'P_FROM_FLOW_STATUS_ID',
4441 p_api_name => lc_api_name);
4442
4443 -- 5362259, p_flow_status_id will be checked later
4444 /* Csd_Process_Util.Check_Reqd_Param
4445 * ( p_param_value => p_to_flow_status_id,
4446 * p_param_name => 'P_TO_FLOW_STATUS_ID',
4447 * p_api_name => lc_api_name);
4448 */
4449
4450 Csd_Process_Util.Check_Reqd_Param
4451 ( p_param_value => p_repair_type_id,
4452 p_param_name => 'P_REPAIR_TYPE_ID',
4453 p_api_name => lc_api_name);
4454
4455 Csd_Process_Util.Check_Reqd_Param
4456 ( p_param_value => p_object_version_number,
4457 p_param_name => 'P_OBJECT_VERSION_NUMBER',
4458 p_api_name => lc_api_name);
4459
4460
4461 -- STEP 2: Validate transition details, if one exists.
4462 -- 5362259, check to_flow_status_id
4463 IF P_TO_FLOW_STATUS_ID IS NULL THEN
4464 -- "Unable to update repair status. A new status is required for the transition."
4465 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_TO_STATUS_REQD');
4466 Fnd_Msg_Pub.ADD;
4467 RAISE Fnd_Api.G_EXC_ERROR;
4468 END IF;
4469
4470
4471 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4472 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4473 'Calling Cursor c_get_trans_flags');
4474 END IF;
4475
4476 OPEN c_get_trans_details;
4477 FETCH c_get_trans_details INTO l_flwsts_tran_id,
4478 l_wf_item_type,
4479 l_wf_process_name,
4480 l_reason_required_flag,
4481 l_capture_activity_flag,
4482 l_allow_all_resp_flag,
4483 l_to_status_code,
4484 l_to_flow_status_code,
4485 l_from_flow_status_code;
4486 CLOSE c_get_trans_details;
4487
4488 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4489 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4490 'After calling Cursor c_get_trans_details');
4491 END IF;
4492
4493 -- Validate whether the set up exists for transition of
4494 -- FROM flow status to TO flow status for the repair_type_id
4495 -- In the process, also check if the return
4496 -- reason is required.
4497 IF l_to_status_code IS NULL THEN
4498 -- "Unable to update repair status. The status transition
4499 -- is not valid for the current repair type."
4500 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_INVALID');
4501 Fnd_Msg_Pub.ADD;
4502 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4503 ELSIF (l_reason_required_flag = 'Y') AND (p_reason_code IS NULL) THEN
4504 -- "Unable to update repair status. A reason is required for the transition."
4505 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_REASON_REQD');
4506 Fnd_Msg_Pub.ADD;
4507 -- 5362259, chaging exception from G_EXC_UNEXPECTED_ERROR to G_EXC_ERROR
4508 RAISE Fnd_Api.G_EXC_ERROR;
4509 END IF;
4510
4511 -- STEP 3: Check access if requested.
4512
4513 -- We do not need to call the 'update allowed' function if the
4514 -- 'all resp' have access or we are requested to skip the check.
4515 IF (l_allow_all_resp_flag <> 'Y' AND p_check_access_flag = 'Y') THEN
4516
4517 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4518 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4519 'Calling Is_Flwsts_Update_Allowed');
4520 END IF;
4521
4522 IF NOT Is_Flwsts_Update_Allowed(p_repair_type_id => p_repair_type_id,
4523 p_from_flow_status_id => p_from_flow_status_id,
4524 p_to_flow_status_id => p_to_flow_status_id,
4525 p_responsibility_id => Fnd_Global.resp_id
4526 ) THEN
4527 -- Unable to update repair status. The user does not
4528 -- have access to update the repair status.
4529 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_NO_ACCESS');
4530 Fnd_Msg_Pub.ADD;
4531 -- rfieldma, 5494587,
4532 -- changing exception from G_EXC_UNEXPECTED_ERROR
4533 -- to G_EXC_ERROR so that the extra developer's error
4534 -- won't show.
4535 RAISE Fnd_Api.G_EXC_ERROR;
4536 END IF;
4537
4538 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4539 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4540 'After calling Is_Flwsts_Update_Allowed');
4541 END IF;
4542 END IF;
4543
4544 -- STEP 4: Validate reason code.
4545
4546 -- Validate reason code if passed
4547 IF p_reason_code IS NOT NULL THEN
4548
4549 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4550 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4551 'Calling Cursor c_check_status_reason');
4552 END IF;
4553
4554 OPEN c_check_status_reason;
4555 FETCH c_check_status_reason INTO l_reason_meaning;
4556 CLOSE c_check_status_reason;
4557
4558 IF l_reason_meaning IS NULL THEN
4559 -- "Unable to update repair status.
4560 -- Invalid reason selected for the transition."
4561 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_INVD_REASON');
4562 Fnd_Msg_Pub.ADD;
4563 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4564 END IF;
4565 END IF;
4566
4567 -- STEP 5: Launch Workflow.
4568
4569 IF (l_wf_item_type IS NOT NULL) THEN
4570
4571 SELECT TO_CHAR(CSD_WF_ITEM_KEY_S.NEXTVAL)
4572 INTO l_wf_item_key
4573 FROM DUAL;
4574
4575 l_flwsts_wf_rec.repair_line_id := p_repair_line_id;
4576 l_flwsts_wf_rec.repair_type_id := p_repair_type_id;
4577 l_flwsts_wf_rec.from_flow_status_id := p_from_flow_status_id;
4578 l_flwsts_wf_rec.to_flow_status_id := p_to_flow_status_id;
4579 l_flwsts_wf_rec.object_version_number := p_object_version_number;
4580 l_flwsts_wf_rec.wf_item_type := l_wf_item_type;
4581 l_flwsts_wf_rec.wf_item_key := l_wf_item_key;
4582 l_flwsts_wf_rec.wf_process_name := l_wf_process_name;
4583
4584 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4585 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4586 'Calling Launch_Flwsts_Wf');
4587 END IF;
4588
4589 Launch_Flwsts_Wf (
4590 p_api_version => 1.0,
4591 p_commit => Fnd_Api.G_FALSE,
4592 p_init_msg_list => Fnd_Api.G_FALSE,
4593 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
4594 x_return_status => x_return_status,
4595 x_msg_count => x_msg_count,
4596 x_msg_data => x_msg_data,
4597 p_flwsts_wf_rec => l_flwsts_wf_rec
4598 );
4599
4600 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
4601 -- "Unable to update repair status.
4602 -- Failed to create the workflow process."
4603 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_WF_FAIL');
4604 Fnd_Msg_Pub.ADD;
4605 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4606 END IF;
4607
4608 END IF;
4609
4610 -- STEP 6: Update CSD_REPAIRS with status details.
4611
4612 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4613 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4614 'Calling update Repairs');
4615 END IF;
4616
4617 --BEGIN
4618
4619 UPDATE CSD_REPAIRS
4620 SET flow_status_id = p_to_flow_status_id,
4621 status = l_to_status_code,
4622 status_reason_code = p_reason_code,
4623 date_closed = DECODE(l_to_status_code, 'C', SYSDATE, NULL),
4624 wf_item_key = NVL(l_wf_item_key, wf_item_key),
4625 wf_item_type = NVL(l_wf_item_type, wf_item_type),
4626 last_updated_by = Fnd_Global.USER_ID,
4627 last_update_date = SYSDATE,
4628 last_update_login = Fnd_Global.LOGIN_ID,
4629 object_version_number = object_version_number + 1
4630 WHERE repair_line_id = p_repair_line_id AND
4631 flow_status_id = p_from_flow_status_id; -- swai: bug 6937272 (FP of 6882484)
4632 -- object_version_number = p_object_version_number;
4633
4634 --x_object_version_number := p_object_version_number + 1;
4635
4636 --EXCEPTION
4637 -- Repair Order does not exist or user is
4638 -- trying to update older version.
4639 --WHEN NO_DATA_FOUND THEN
4640 -- Unable to update repair status. Another user may have
4641 -- updated the repair order. Please requery the
4642 -- original data and try again.
4643 --Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_OLD_VERSION');
4644 --Fnd_Msg_Pub.ADD;
4645 --RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4646 --END;
4647
4648 if(sql%rowcount > 0) then
4649 -- swai: bug 6937272 (FP of 6882484)
4650 -- x_object_version_number := p_object_version_number + 1;
4651 OPEN c_get_object_version_number(p_repair_line_id);
4652 FETCH c_get_object_version_number INTO x_object_version_number;
4653 CLOSE c_get_object_version_number;
4654 else
4655 --debug('object_Version mismatch');
4656 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_OLD_VERSION');
4657 Fnd_Msg_Pub.ADD;
4658 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4659 end if;
4660
4661 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4662 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4663 'After calling update Repairs');
4664 END IF;
4665
4666 -- STEP 7: Log 'Status Change' activity.
4667
4668 -- Log an activity if setup to do so.
4669 IF l_capture_activity_flag = 'Y' THEN
4670
4671 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4672 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4673 'Calling Repair History to log activity');
4674 END IF;
4675
4676
4677 Csd_To_Form_Repair_History.Validate_And_Write
4678 (p_Api_Version_Number => 1.0 ,
4679 p_init_msg_list => Fnd_Api.G_FALSE,
4680 p_commit => Fnd_Api.G_FALSE,
4681 p_validation_level => NULL,
4682 p_action_code => 0,
4683 px_REPAIR_HISTORY_ID => x_repair_history_id,
4684 p_OBJECT_VERSION_NUMBER => NULL,
4685 p_REQUEST_ID => NULL,
4686 p_PROGRAM_ID => NULL,
4687 p_PROGRAM_APPLICATION_ID => NULL,
4688 p_PROGRAM_UPDATE_DATE => NULL,
4689 p_CREATED_BY => Fnd_Global.USER_ID,
4690 p_CREATION_DATE => SYSDATE,
4691 p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
4692 p_LAST_UPDATE_DATE => SYSDATE,
4693 p_repair_line_id => p_repair_line_id,
4694 p_EVENT_CODE => lc_update_fs_event_code,
4695 p_EVENT_DATE => SYSDATE,
4696 -- p_QUANTITY => p_quantity,
4697 p_PARAMN1 => NULL,
4698 p_PARAMN2 => NULL,
4699 p_PARAMN3 => NULL,
4700 p_PARAMN10 => Fnd_Global.USER_ID,
4701 p_PARAMC1 => l_to_flow_status_code,
4702 p_PARAMC2 => l_from_flow_status_code,
4703 p_PARAMC3 => l_reason_meaning,
4704 p_PARAMC4 => NULL,
4705 p_PARAMC5 => NULL,
4706 p_PARAMC6 => p_comments,
4707 p_PARAMC7 => l_wf_item_type,
4708 p_PARAMC8 => l_wf_item_key,
4709 p_PARAMC9 => l_wf_process_name,
4710 p_LAST_UPDATE_LOGIN => Fnd_Global.CONC_LOGIN_ID,
4711 X_Return_Status => x_return_status,
4712 X_Msg_Count => x_msg_count,
4713 X_Msg_Data => x_msg_data
4714 );
4715
4716 IF x_return_status <> 'S' THEN
4717 -- Unable to update repair status. Adding repair activity
4718 -- process has failed.
4719 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_ACTY_FAIL');
4720 Fnd_Msg_Pub.ADD;
4721 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4722 END IF;
4723
4724 END IF;
4725
4726 -- STEP 8: Close SR if RO is closed.
4727 -- The procedure will check if all ROs are closed
4728 -- and then, based on a profile, close SR.
4729
4730 --debug('update_flow_status: Step 8');
4731
4732 IF l_to_status_code = lc_status_code_closed THEN
4733
4734 -- Incident Id must be NULL.
4735 Csd_Process_Pvt.Close_status
4736 ( p_api_version => 1.0,
4737 p_commit => Fnd_Api.G_FALSE,
4738 p_init_msg_list => Fnd_Api.G_FALSE,
4739 p_validation_level => Csd_Process_Util.G_VALID_LEVEL_FULL,
4740 p_incident_id => NULL,
4741 p_repair_line_id => p_repair_line_id,
4742 x_return_status => x_return_status,
4743 x_msg_count => x_msg_count,
4744 x_msg_data => x_msg_data
4745 );
4746
4747 IF x_return_status <> 'S' THEN
4748 -- Unable to update repair status. Adding repair activity
4749 -- process has failed.
4750 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_SR_FAIL');
4751 Fnd_Msg_Pub.ADD;
4752 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4753 END IF;
4754 END IF;
4755
4756 -- STEP 9: Launch the Business Event.
4757 -- Business Event is always launched (not optional).
4758 --debug('update_flow_status: Step 9');
4759
4760 l_ro_status_bevent_rec.repair_line_id := p_repair_line_id;
4761 l_ro_status_bevent_rec.from_flow_status_id := p_from_flow_status_id;
4762 l_ro_status_bevent_rec.to_flow_status_id := p_to_flow_status_id;
4763 l_ro_status_bevent_rec.object_version_number := p_object_version_number;
4764
4765 raise_ro_status_bevent
4766 ( p_ro_status_bevent_rec => l_ro_status_bevent_rec,
4767 p_commit => Fnd_Api.G_FALSE,
4768 x_return_status => x_return_status,
4769 x_msg_count => x_msg_count,
4770 x_msg_data => x_msg_data
4771 );
4772 --debug('update_flow_status: Step 10');
4773
4774 IF x_return_status <> 'S' THEN
4775 -- Unable to update repair status. Failed
4776 -- to Initialize the business event.
4777 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_BE_FAIL');
4778 Fnd_Msg_Pub.ADD;
4779 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4780 END IF;
4781
4782 --debug('update_flow_status: Step 11');
4783
4784 -- STEP 10: Log all milestones events for the transition.
4785
4786 l_repair_milestone_rec.repair_line_id := p_repair_line_id;
4787 -- All event will have same datetime.
4788 l_repair_milestone_rec.milestone_date := SYSDATE;
4789 l_repair_milestone_rec.object_version_number := 1;
4790
4791 FOR i_rec in c_get_flwsts_miles(l_flwsts_tran_id) LOOP
4792 -- Only code changes for each event.
4793 l_repair_milestone_rec.milestone_code := i_rec.milestone_code;
4794
4795 --debug('update_flow_status: Step 12');
4796 CSD_REPAIR_MILESTONES_PVT.Create_Repair_Milestone
4797 ( p_api_version => 1.0,
4798 p_commit => Fnd_Api.G_FALSE,
4799 p_init_msg_list => Fnd_Api.G_FALSE,
4800 p_validation_level => Csd_Process_Util.G_VALID_LEVEL_FULL,
4801 x_return_status => x_return_status,
4802 x_msg_count => x_msg_count,
4803 x_msg_data => x_msg_data,
4804 p_repair_milestone_rec => l_repair_milestone_rec,
4805 x_repair_milestone_id => x_repair_milestone_id
4806 );
4807 --debug('update_flow_status: Step 12');
4808 END LOOP;
4809
4810 -- Api body ends here
4811
4812 --debug('update_flow_status: Step 13');
4813 -- Standard check of p_commit.
4814 IF Fnd_Api.To_Boolean( p_commit ) THEN
4815 COMMIT WORK;
4816 END IF;
4817 --debug('update_flow_status: Step 14');
4818
4819 -- Standard call to get message count and IF count is get message info.
4820 Fnd_Msg_Pub.Count_And_Get
4821 (p_count => x_msg_count,
4822 p_data => x_msg_data );
4823
4824 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4825 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.END',
4826 'Leaving Update_Flow_Status');
4827 END IF;
4828
4829 EXCEPTION
4830 WHEN Fnd_Api.G_EXC_ERROR THEN
4831 ROLLBACK TO Update_Flow_Status;
4832 x_return_status := Fnd_Api.G_RET_STS_ERROR;
4833
4834 Fnd_Msg_Pub.Count_And_Get
4835 (p_count => x_msg_count,
4836 p_data => x_msg_data );
4837
4838 -- save message in debug log
4839 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4840 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
4841 'EXC_ERROR['||x_msg_data||']');
4842 END IF;
4843
4844 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
4845 ROLLBACK TO Update_Flow_Status;
4846 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4847
4848 -- save message in fnd stack
4849 IF Fnd_Msg_Pub.Check_Msg_Level
4850 (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
4851 THEN
4852 IF (Fnd_Log.LEVEL_STATEMENT >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4853 Fnd_Log.STRING(Fnd_Log.LEVEL_STATEMENT, lc_mod_name,
4854 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
4855 END IF;
4856 Fnd_Msg_Pub.Add_Exc_Msg
4857 (G_PKG_NAME ,
4858 lc_api_name );
4859 END IF;
4860
4861 Fnd_Msg_Pub.Count_And_Get
4862 ( p_count => x_msg_count,
4863 p_data => x_msg_data );
4864
4865 -- save message in debug log
4866 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4867 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
4868 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
4869 END IF;
4870
4871 WHEN OTHERS THEN
4872 ROLLBACK TO Update_Flow_Status;
4873 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4874
4875 -- save message in fnd stack
4876 IF Fnd_Msg_Pub.Check_Msg_Level
4877 (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
4878 THEN
4879 IF (Fnd_Log.LEVEL_STATEMENT >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4880 Fnd_Log.STRING(Fnd_Log.LEVEL_STATEMENT, lc_mod_name,
4881 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
4882 END IF;
4883 Fnd_Msg_Pub.Add_Exc_Msg
4884 (G_PKG_NAME ,
4885 lc_api_name );
4886 END IF;
4887
4888 Fnd_Msg_Pub.Count_And_Get
4889 (p_count => x_msg_count,
4890 p_data => x_msg_data );
4891
4892 -- save message in debug log
4893 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4894 -- create a seeded message
4895 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
4896 'SQL Message['||SQLERRM||']' );
4897 END IF;
4898
4899 END Update_Flow_Status;
4900
4901
4902 FUNCTION Is_Rt_Update_Allowed (
4903 p_from_repair_type_id IN NUMBER,
4904 p_to_repair_type_id IN NUMBER,
4905 p_common_flow_status_id IN NUMBER,
4906 p_responsibility_id IN NUMBER
4907 ) RETURN BOOLEAN IS
4908
4909 -- CONSTANTS --
4910 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.Is_Rt_Update_Allowed';
4911 lc_api_name CONSTANT VARCHAR2(30) := 'Is_Rt_Update_Allowed';
4912
4913 -- VARIABLES --
4914 l_dummy VARCHAR2(1) := NULL;
4915
4916 -- CURSORS --
4917 CURSOR c_is_rt_update_allowed IS
4918 SELECT 'x'
4919 FROM CSD_RT_TRANS_B RT_B
4920 WHERE RT_B.FROM_REPAIR_TYPE_ID = p_from_repair_type_id AND
4921 RT_B.TO_REPAIR_TYPE_ID = p_to_repair_type_id AND
4922 RT_B.COMMON_FLOW_STATUS_ID = p_common_flow_status_id AND
4923 ((RT_B.ALLOW_ALL_RESP_FLAG = 'Y') OR
4924 EXISTS
4925 (SELECT 'y'
4926 FROM CSD_RT_TRAN_RESPS RESP
4927 WHERE RESP.RT_TRAN_ID = RT_B.RT_TRAN_ID AND
4928 RESP.RESPONSIBILITY_ID = p_responsibility_id)
4929 );
4930
4931 BEGIN
4932
4933 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4934 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.BEGIN',
4935 'Entered Is_Rt_Update_Allowed');
4936 END IF;
4937
4938 -- Check the required parameters
4939 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4940 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
4941 'Checking required parameters');
4942 END IF;
4943
4944 -- Check the required parameters
4945 Csd_Process_Util.Check_Reqd_Param
4946 ( p_param_value => p_from_repair_type_id,
4947 p_param_name => 'P_FROM_REPAIR_TYPE_ID',
4948 p_api_name => lc_api_name);
4949
4950 Csd_Process_Util.Check_Reqd_Param
4951 ( p_param_value => p_to_repair_type_id,
4952 p_param_name => 'P_TO_REPAIR_TYPE_ID',
4953 p_api_name => lc_api_name);
4954
4955 Csd_Process_Util.Check_Reqd_Param
4956 ( p_param_value => p_common_flow_status_id,
4957 p_param_name => 'P_COMMON_FLOW_STATUS_ID',
4958 p_api_name => lc_api_name);
4959
4960 OPEN c_is_rt_update_allowed;
4961 FETCH c_is_rt_update_allowed INTO l_dummy;
4962 CLOSE c_is_rt_update_allowed;
4963
4964 -- If no records found then the responsibility
4965 -- does not have access to the transition
4966 IF l_dummy IS NULL THEN
4967 RETURN FALSE;
4968 ELSE
4969 RETURN TRUE;
4970 END IF;
4971
4972 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
4973 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.END',
4974 'Leaving Is_Rt_Update_Allowed');
4975 END IF;
4976
4977 END Is_Rt_Update_Allowed;
4978
4979 PROCEDURE Update_Repair_Type (
4980 p_api_version IN NUMBER,
4981 p_commit IN VARCHAR2,
4982 p_init_msg_list IN VARCHAR2,
4983 p_validation_level IN NUMBER,
4984 x_return_status OUT NOCOPY VARCHAR2,
4985 x_msg_count OUT NOCOPY NUMBER,
4986 x_msg_data OUT NOCOPY VARCHAR2,
4987 p_repair_line_id IN NUMBER,
4988 p_from_repair_type_id IN NUMBER,
4989 p_to_repair_type_id IN NUMBER,
4990 p_common_flow_status_id IN NUMBER,
4991 p_reason_code IN VARCHAR2,
4992 p_object_version_number IN NUMBER,
4993 x_object_version_number OUT NOCOPY NUMBER
4994 ) IS
4995
4996 -- CONSTANTS --
4997 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.update_repair_type';
4998 lc_api_name CONSTANT VARCHAR2(30) := 'Update_Repair_Type';
4999 lc_api_version CONSTANT NUMBER := 1.0;
5000 lc_update_rt_event_code CONSTANT VARCHAR2(5) := 'RTU';
5001
5002 -- VARIABLES --
5003 l_reason_required_flag VARCHAR2(1) := NULL;
5004 l_capture_activity_flag VARCHAR2(1) := NULL;
5005 x_repair_history_id NUMBER;
5006 l_dummy VARCHAR2(80) := NULL;
5007 l_from_repair_type VARCHAR2(80);
5008 l_to_repair_type VARCHAR2(80);
5009
5010 -- CURSOR --
5011 CURSOR c_get_trans_flags IS
5012 SELECT RT_TRANS.reason_required_flag,
5013 RT_TRANS.capture_activity_flag
5014 FROM CSD_RT_TRANS_B RT_TRANS
5015 WHERE RT_TRANS.from_repair_type_id = p_from_repair_type_id AND
5016 RT_TRANS.to_repair_type_id = p_to_repair_type_id AND
5017 RT_TRANS.common_flow_status_id = p_common_flow_status_id;
5018
5019 -- Query to validate the reason code
5020 CURSOR c_check_rt_reason IS
5021 SELECT meaning
5022 FROM FND_LOOKUPS RSN_LKUP
5023 WHERE
5024 RSN_LKUP.lookup_type = 'CSD_RT_TRANSITION_REASONS' AND
5025 RSN_LKUP.lookup_code = p_reason_code AND
5026 RSN_LKUP.enabled_flag = 'Y' AND
5027 TRUNC(SYSDATE) BETWEEN
5028 TRUNC(NVL(RSN_LKUP.start_date_active, SYSDATE)) AND
5029 TRUNC(NVL(RSN_LKUP.end_date_active, SYSDATE));
5030
5031 -- Query to derive from and to repair type name
5032 CURSOR c_get_repair_types IS
5033 SELECT CRTV.name name1, CRTV1.name name2
5034 FROM CSD_REPAIR_TYPES_Vl CRTV, CSD_REPAIR_TYPES_Vl CRTV1
5035 WHERE
5036 CRTV.REPAIR_TYPE_Id = p_from_repair_type_id AND
5037 CRTV1.REPAIR_TYPE_Id = p_to_repair_type_id;
5038
5039 BEGIN
5040
5041 -- Standard Start of API savepoint
5042 SAVEPOINT Update_Repair_Type;
5043
5044 -- Standard call to check for call compatibility.
5045 IF NOT Fnd_Api.Compatible_API_Call (lc_api_version,
5046 p_api_version,
5047 lc_api_name ,
5048 G_PKG_NAME )
5049 THEN
5050 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5051 END IF;
5052
5053 -- Initialize message list if p_init_msg_list is set to TRUE.
5054 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
5055 Fnd_Msg_Pub.initialize;
5056 END IF;
5057
5058 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5059 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.BEGIN',
5060 'Entered Update_Repair_Type');
5061 END IF;
5062
5063 -- Initialize API return status to success
5064 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5065
5066 -- Api body starts
5067
5068 -- Check the required parameters
5069 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5070 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5071 'Checking required parameters');
5072 END IF;
5073
5074 -- Check the required parameters
5075 Csd_Process_Util.Check_Reqd_Param
5076 ( p_param_value => p_repair_line_id,
5077 p_param_name => 'P_REPAIR_LINE_ID',
5078 p_api_name => lc_api_name);
5079
5080 Csd_Process_Util.Check_Reqd_Param
5081 ( p_param_value => p_from_repair_type_id,
5082 p_param_name => 'P_FROM_REPAIR_TYPE_ID',
5083 p_api_name => lc_api_name);
5084
5085 -- 5362259, repair type will be handled later with appropriate msg
5086 /* Csd_Process_Util.Check_Reqd_Param
5087 * ( p_param_value => p_to_repair_type_id,
5088 * p_param_name => 'P_TO_REPAIR_TYPE_ID',
5089 * p_api_name => lc_api_name);
5090 */
5091
5092 Csd_Process_Util.Check_Reqd_Param
5093 ( p_param_value => p_common_flow_status_id,
5094 p_param_name => 'P_COMMON_FLOW_STATUS_ID',
5095 p_api_name => lc_api_name);
5096
5097 Csd_Process_Util.Check_Reqd_Param
5098 ( p_param_value => p_object_version_number,
5099 p_param_name => 'P_OBJECT_VERSION_NUMBER',
5100 p_api_name => lc_api_name);
5101
5102
5103 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5104 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5105 'Calling Cursor c_get_trans_flags');
5106 END IF;
5107
5108 OPEN c_get_trans_flags;
5109 FETCH c_get_trans_flags INTO l_reason_required_flag, l_capture_activity_flag;
5110 CLOSE c_get_trans_flags;
5111
5112 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5113 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5114 'After calling Cursor c_get_trans_flags');
5115 END IF;
5116
5117 -- 5362259, validate p_to_repair_type_id and show error msg
5118 IF (p_to_repair_type_id IS NULL) THEN
5119 -- "Unable to update repair type. A new repair type is required for the transition."
5120 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_RT_TO_RT_REQD');
5121 Fnd_Msg_Pub.ADD;
5122 RAISE Fnd_Api.G_EXC_ERROR;
5123 END IF;
5124
5125
5126 -- Validate whether the set up exists for transition of
5127 -- FROM repair type to TO repair type for the current flow_status_id
5128 -- in CSD_REPAIRS. In the process, also check if the return
5129 -- reason is required.
5130 IF l_reason_required_flag IS NULL THEN
5131 -- "Unable to update repair type. The repair type transition
5132 -- is not valid for the current status."
5133 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_RT_TRANS_INVALID');
5134 Fnd_Msg_Pub.ADD;
5135 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5136 ELSIF (l_reason_required_flag = 'Y') AND (p_reason_code IS NULL) THEN
5137 -- "Unable to update repair type. A reason is required for the transition."
5138 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_RT_REASON_REQD');
5139 Fnd_Msg_Pub.ADD;
5140 --5362259, changing exeception from G_EXC_UPEXPECTED_ERROR to G_EXC_ERROR
5141 RAISE Fnd_Api.G_EXC_ERROR;
5142 END IF;
5143
5144 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5145 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5146 'Calling Is_Rt_Update_Allowed');
5147 END IF;
5148
5149 IF NOT Is_Rt_Update_Allowed(p_from_repair_type_id => p_from_repair_type_id,
5150 p_to_repair_type_id => p_to_repair_type_id,
5151 p_common_flow_status_id => p_common_flow_status_id,
5152 p_responsibility_id => Fnd_Global.resp_id
5153 ) THEN
5154 -- Unable to update repair type. The user does not
5155 -- have access to update the repair type.
5156 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_RT_TRANS_NO_ACCESS');
5157 Fnd_Msg_Pub.ADD;
5158 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5159 END IF;
5160
5161 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5162 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5163 'After calling Is_Rt_Update_Allowed');
5164 END IF;
5165
5166
5167 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5168 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5169 'Calling Cursor c_check_rt_reason');
5170 END IF;
5171
5172 -- Validate reason code if passed
5173 IF p_reason_code IS NOT NULL THEN
5174 OPEN c_check_rt_reason;
5175 FETCH c_check_rt_reason INTO l_dummy;
5176 CLOSE c_check_rt_reason;
5177
5178 IF l_dummy IS NULL THEN
5179 -- "Unable to update repair type.
5180 -- Invalid reason selected for the transition."
5181 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_RT_INVD_REASON');
5182 Fnd_Msg_Pub.ADD;
5183 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5184 END IF;
5185 END IF;
5186
5187 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5188 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5189 'calling update for Repair Type');
5190 END IF;
5191
5192 BEGIN
5193
5194 UPDATE CSD_REPAIRS
5195 SET repair_type_id = p_to_repair_type_id,
5196 last_updated_by = Fnd_Global.USER_ID,
5197 last_update_date = SYSDATE,
5198 last_update_login = Fnd_Global.LOGIN_ID,
5199 object_version_number = object_version_number + 1
5200 WHERE repair_line_id = p_repair_line_id AND
5201 object_version_number = p_object_version_number;
5202
5203 x_object_version_number := p_object_version_number + 1;
5204
5205 EXCEPTION
5206 -- Repair Order does not exist or user is
5207 -- trying to update older version.
5208 WHEN NO_DATA_FOUND THEN
5209 -- Unable to update repair type. Another user may have
5210 -- updated the repair order. Please requery the
5211 -- original data and try again.
5212 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_RT_TRANS_OLD_VERSION');
5213 Fnd_Msg_Pub.ADD;
5214 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5215 END;
5216
5217 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5218 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5219 'After calling update for Repair Type');
5220 END IF;
5221
5222 -- Log an activity if setup to do so.
5223 IF l_capture_activity_flag = 'Y' THEN
5224
5225 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5226 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5227 'Calling Repair History to log activity');
5228 END IF;
5229
5230 OPEN c_get_repair_types;
5231 FETCH c_get_repair_types INTO l_from_repair_type, l_to_repair_type;
5232 CLOSE c_get_repair_types;
5233
5234 /* Csd_To_Form_Repair_History.Validate_And_Write
5235 (p_Api_Version_Number => 1.0 ,
5236 p_init_msg_list => 'F',
5237 p_commit => 'F',
5238 p_validation_level => NULL,
5239 p_action_code => 0,
5240 px_REPAIR_HISTORY_ID => x_repair_history_id,
5241 p_OBJECT_VERSION_NUMBER => NULL,
5242 p_repair_line_id => p_repair_line_id,
5243 p_EVENT_CODE => lc_update_rt_event_code,
5244 p_EVENT_DATE => SYSDATE,
5245 p_PARAMN1 => p_from_repair_type_id,
5246 p_PARAMN2 => p_to_repair_type_id,
5247 p_PARAMN3 => Fnd_Global.USER_ID,
5248 p_PARAMC1 => p_reason_code,
5249 X_Return_Status => x_return_status,
5250 X_Msg_Count => x_msg_count,
5251 X_Msg_Data => x_msg_data
5252 );*/
5253 Csd_To_Form_Repair_History.Validate_And_Write
5254 (p_Api_Version_Number => 1.0 ,
5255 p_init_msg_list => 'F',
5256 p_commit => 'F',
5257 p_validation_level => NULL,
5258 p_action_code => 0,
5259 px_REPAIR_HISTORY_ID => x_repair_history_id,
5260 p_OBJECT_VERSION_NUMBER => NULL,
5261 p_REQUEST_ID => NULL,
5262 p_PROGRAM_ID => NULL,
5263 p_PROGRAM_APPLICATION_ID => NULL,
5264 p_PROGRAM_UPDATE_DATE => NULL,
5265 p_CREATED_BY => FND_GLOBAL.USER_ID, --bug#13455552
5266 p_CREATION_DATE => SYSDATE,
5267 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
5268 p_LAST_UPDATE_DATE => SYSDATE,
5269 p_repair_line_id => p_repair_line_id,
5270 p_EVENT_CODE => 'RTU',
5271 p_EVENT_DATE => SYSDATE,
5272 p_QUANTITY => NULL,
5273 p_PARAMN1 => p_from_repair_type_id,
5274 p_PARAMN2 => p_to_repair_type_id,
5275 p_PARAMN3 => fnd_global.user_id,--NULL,
5276 p_PARAMN4 => NULL,
5277 p_PARAMN5 => NULL,
5278 p_PARAMN6 => NULL,
5279 p_PARAMN7 => NULL,
5280 p_PARAMN8 => NULL,
5281 p_PARAMN9 => NULL,
5282 p_PARAMN10 => NULL,
5283 p_PARAMC1 => p_reason_code,
5284 p_PARAMC2 => l_dummy, -- reason
5285 p_PARAMC3 => l_from_repair_type,
5286 p_PARAMC4 => l_to_repair_type,
5287 p_PARAMC5 => NULL,
5288 p_PARAMC6 => NULL,
5289 p_PARAMC7 => NULL,
5290 p_PARAMC8 => NULL,
5291 p_PARAMC9 => NULL,
5292 p_PARAMC10 => NULL,
5293 p_PARAMD1 => NULL,
5294 p_PARAMD2 => NULL,
5295 p_PARAMD3 => NULL,
5296 p_PARAMD4 => NULL,
5297 p_PARAMD5 => NULL,
5298 p_PARAMD6 => NULL,
5299 p_PARAMD7 => NULL,
5300 p_PARAMD8 => NULL,
5301 p_PARAMD9 => NULL,
5302 p_PARAMD10 => NULL,
5303 p_ATTRIBUTE_CATEGORY => NULL,
5304 p_ATTRIBUTE1 => NULL,
5305 p_ATTRIBUTE2 => NULL,
5306 p_ATTRIBUTE3 => NULL,
5307 p_ATTRIBUTE4 => NULL,
5308 p_ATTRIBUTE5 => NULL,
5309 p_ATTRIBUTE6 => NULL,
5310 p_ATTRIBUTE7 => NULL,
5311 p_ATTRIBUTE8 => NULL,
5312 p_ATTRIBUTE9 => NULL,
5313 p_ATTRIBUTE10 => NULL,
5314 p_ATTRIBUTE11 => NULL,
5315 p_ATTRIBUTE12 => NULL,
5316 p_ATTRIBUTE13 => NULL,
5317 p_ATTRIBUTE14 => NULL,
5318 p_ATTRIBUTE15 => NULL,
5319 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
5320 X_Return_Status => x_return_status,
5321 X_Msg_Count => x_msg_count,
5322 X_Msg_Data => x_msg_data
5323 );
5324
5325
5326 IF x_return_status <> 'S' THEN
5327 -- Unable to update repair type. Adding repair activity
5328 -- process has failed.
5329 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_RT_ACTIVITY_FAILED');
5330 Fnd_Msg_Pub.ADD;
5331 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5332 END IF;
5333
5334 END IF;
5335
5336 -- Api body ends here
5337
5338 -- Standard check of p_commit.
5339 IF Fnd_Api.To_Boolean( p_commit ) THEN
5340 COMMIT WORK;
5341 END IF;
5342
5343 -- Standard call to get message count and IF count is get message info.
5344 Fnd_Msg_Pub.Count_And_Get
5345 (p_count => x_msg_count,
5346 p_data => x_msg_data );
5347
5348 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5349 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.END',
5350 'Leaving Update_Repair_Type');
5351 END IF;
5352
5353 EXCEPTION
5354 WHEN Fnd_Api.G_EXC_ERROR THEN
5355 ROLLBACK TO Update_Repair_Type;
5356 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5357
5358 Fnd_Msg_Pub.Count_And_Get
5359 (p_count => x_msg_count,
5360 p_data => x_msg_data );
5361
5362 -- save message in debug log
5363 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5364 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
5365 'EXC_ERROR['||x_msg_data||']');
5366 END IF;
5367
5368 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
5369 ROLLBACK TO Update_Repair_Type;
5370 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5371
5372 -- save message in fnd stack
5373 IF Fnd_Msg_Pub.Check_Msg_Level
5374 (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
5375 THEN
5376 IF (Fnd_Log.LEVEL_STATEMENT >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5377 Fnd_Log.STRING(Fnd_Log.LEVEL_STATEMENT, lc_mod_name,
5378 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
5379 END IF;
5380 Fnd_Msg_Pub.Add_Exc_Msg
5381 (G_PKG_NAME ,
5382 lc_api_name );
5383 END IF;
5384
5385 Fnd_Msg_Pub.Count_And_Get
5386 ( p_count => x_msg_count,
5387 p_data => x_msg_data );
5388
5389 -- save message in debug log
5390 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5391 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
5392 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
5393 END IF;
5394
5395 WHEN OTHERS THEN
5396 ROLLBACK TO Update_Repair_Type;
5397 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5398
5399 -- save message in fnd stack
5400 IF Fnd_Msg_Pub.Check_Msg_Level
5401 (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
5402 THEN
5403 IF (Fnd_Log.LEVEL_STATEMENT >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5404 Fnd_Log.STRING(Fnd_Log.LEVEL_STATEMENT, lc_mod_name,
5405 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
5406 END IF;
5407 Fnd_Msg_Pub.Add_Exc_Msg
5408 (G_PKG_NAME ,
5409 lc_api_name );
5410 END IF;
5411
5412 Fnd_Msg_Pub.Count_And_Get
5413 (p_count => x_msg_count,
5414 p_data => x_msg_data );
5415
5416 -- save message in debug log
5417 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5418 -- create a seeded message
5419 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
5420 'SQL Message['||SQLERRM||']' );
5421 END IF;
5422
5423 END Update_Repair_Type;
5424
5425 FUNCTION Is_Flwsts_Update_Allowed(
5426 p_repair_type_id IN NUMBER,
5427 p_from_flow_status_id IN NUMBER,
5428 p_to_flow_status_id IN NUMBER,
5429 p_responsibility_id IN NUMBER
5430 ) RETURN BOOLEAN IS
5431
5432 -- CONSTANTS --
5433 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.Is_Flwsts_Update_Allowed';
5434 lc_api_name CONSTANT VARCHAR2(30) := 'Is_Flwsts_Update_Allowed';
5435
5436 -- VARIABLES --
5437 l_dummy VARCHAR2(1) := NULL;
5438
5439 -- CURSORS --
5440 CURSOR c_is_flwsts_update_allowed IS
5441 SELECT 'x'
5442 FROM CSD_FLWSTS_TRANS_B RT_B
5443 WHERE RT_B.FROM_FLOW_STATUS_ID = p_from_flow_status_id AND
5444 RT_B.TO_FLOW_STATUS_ID = p_to_flow_status_id AND
5445 RT_B.REPAIR_TYPE_ID = p_repair_type_id AND
5446 (
5447 (RT_B.ALLOW_ALL_RESP_FLAG = 'Y') OR
5448 EXISTS
5449 (SELECT 'y'
5450 FROM CSD_FLWSTS_TRAN_RESPS RESP
5451 WHERE RESP. FLWSTS_TRAN_ID = RT_B.FLWSTS_TRAN_ID AND
5452 RESP.RESPONSIBILITY_ID = p_responsibility_id)
5453 );
5454
5455 BEGIN
5456
5457 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5458 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.BEGIN',
5459 'Entered Is_Flwsts_Update_Allowed');
5460 END IF;
5461
5462 -- Check the required parameters
5463 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5464 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5465 'Checking required parameters');
5466 END IF;
5467
5468 -- Check the required parameters
5469 Csd_Process_Util.Check_Reqd_Param
5470 ( p_param_value => p_from_flow_status_id,
5471 p_param_name => 'P_FROM_FLOW_STATUS_ID',
5472 p_api_name => lc_api_name);
5473
5474 Csd_Process_Util.Check_Reqd_Param
5475 ( p_param_value => p_to_flow_status_id,
5476 p_param_name => 'P_TO_FLOW_STATUS_ID',
5477 p_api_name => lc_api_name);
5478
5479 Csd_Process_Util.Check_Reqd_Param
5480 ( p_param_value => p_repair_type_id,
5481 p_param_name => 'P_REPAIR_TYPE_ID',
5482 p_api_name => lc_api_name);
5483
5484 OPEN c_is_flwsts_update_allowed;
5485 FETCH c_is_flwsts_update_allowed INTO l_dummy;
5486 CLOSE c_is_flwsts_update_allowed;
5487
5488 -- If no records found then the responsibility
5489 -- does not have access to the transition
5490 IF l_dummy IS NULL THEN
5491 RETURN FALSE;
5492 ELSE
5493 RETURN TRUE;
5494 END IF;
5495
5496 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5497 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.END',
5498 'Leaving Is_Flwsts_Update_Allowed');
5499 END IF;
5500
5501 END Is_Flwsts_Update_Allowed;
5502
5503 PROCEDURE Launch_Flwsts_Wf (
5504 p_api_version IN NUMBER,
5505 p_commit IN VARCHAR2,
5506 p_init_msg_list IN VARCHAR2,
5507 p_validation_level IN NUMBER,
5508 x_return_status OUT NOCOPY VARCHAR2,
5509 x_msg_count OUT NOCOPY NUMBER,
5510 x_msg_data OUT NOCOPY VARCHAR2,
5511 p_flwsts_wf_rec IN Flwsts_Wf_Rec_Type
5512 ) IS
5513
5514 -- CONSTANTS --
5515 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.CSD_REPAIRS_PVT.launch_flwsts_wf';
5516 lc_api_name CONSTANT VARCHAR2(30) := 'Launch_Flwsts_Wf';
5517 lc_api_version CONSTANT NUMBER := 1.0;
5518
5519 -- For setting WF engine threshold
5520 lc_wf_negative_threshold CONSTANT NUMBER := -1;
5521
5522 -- For WF Item Attributes
5523 lc_repair_line_id CONSTANT VARCHAR2(30) := 'CSD_REPAIR_LINE_ID';
5524 lc_repair_type_id CONSTANT VARCHAR2(30) := 'CSD_REPAIR_TYPE_ID';
5525 lc_from_flow_status_id CONSTANT VARCHAR2(30) := 'CSD_FROM_FLOW_STATUS_ID';
5526 lc_to_flow_status_id CONSTANT VARCHAR2(30) := 'CSD_TO_FLOW_STATUS_ID';
5527 lc_object_version_number CONSTANT VARCHAR2(30) := 'CSD_OBJECT_VERSION_NUMBER';
5528
5529 -- VARIABLES --
5530 l_wf_current_threshold NUMBER := NULL;
5531
5532 l_wf_aname_TabType Wf_Engine.NameTabTyp;
5533 l_wf_avalue_TabType Wf_Engine.NumTabTyp;
5534
5535 BEGIN
5536
5537 -- Standard Start of API savepoint
5538 SAVEPOINT Launch_Flwsts_Wf;
5539
5540 -- Standard call to check for call compatibility.
5541 IF NOT Fnd_Api.Compatible_API_Call (lc_api_version,
5542 p_api_version,
5543 lc_api_name,
5544 G_PKG_NAME)
5545 THEN
5546 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5547 END IF;
5548
5549 -- Initialize message list if p_init_msg_list is set to TRUE.
5550 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
5551 Fnd_Msg_Pub.initialize;
5552 END IF;
5553
5554 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5555 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.BEGIN',
5556 'Entered Launch_Flwsts_Wf');
5557 END IF;
5558
5559 -- Initialize API return status to success
5560 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
5561
5562 -- Api body starts
5563
5564 -- Check the required parameters
5565 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5566 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5567 'Checking required parameters');
5568 END IF;
5569
5570 -- Check the required parameters
5571 Csd_Process_Util.Check_Reqd_Param
5572 ( p_param_value => p_flwsts_wf_rec.repair_line_id,
5573 p_param_name => 'REPAIR_LINE_ID',
5574 p_api_name => lc_api_name);
5575
5576 Csd_Process_Util.Check_Reqd_Param
5577 ( p_param_value => p_flwsts_wf_rec.repair_type_id,
5578 p_param_name => 'REPAIR_TYPE_ID',
5579 p_api_name => lc_api_name);
5580
5581 Csd_Process_Util.Check_Reqd_Param
5582 ( p_param_value => p_flwsts_wf_rec.from_flow_status_id,
5583 p_param_name => 'FROM_FLOW_STATUS_ID',
5584 p_api_name => lc_api_name);
5585
5586 Csd_Process_Util.Check_Reqd_Param
5587 ( p_param_value => p_flwsts_wf_rec.to_flow_status_id,
5588 p_param_name => 'TO_FLOW_STATUS_ID',
5589 p_api_name => lc_api_name);
5590
5591 Csd_Process_Util.Check_Reqd_Param
5592 ( p_param_value => p_flwsts_wf_rec.object_version_number,
5593 p_param_name => 'OBJECT_VERSION_NUMBER',
5594 p_api_name => lc_api_name);
5595
5596 Csd_Process_Util.Check_Reqd_Param
5597 ( p_param_value => p_flwsts_wf_rec.wf_item_type,
5598 p_param_name => 'WF_ITEM_TYPE',
5599 p_api_name => lc_api_name);
5600
5601 Csd_Process_Util.Check_Reqd_Param
5602 ( p_param_value => p_flwsts_wf_rec.wf_item_key,
5603 p_param_name => 'WF_ITEM_KEY',
5604 p_api_name => lc_api_name);
5605
5606 Csd_Process_Util.Check_Reqd_Param
5607 ( p_param_value => p_flwsts_wf_rec.wf_process_name,
5608 p_param_name => 'WF_PROCESS_NAME',
5609 p_api_name => lc_api_name);
5610
5611 -- Get the current threshold
5612 -- yvchen: Bug 12888789, Don't change the Wf_Engine.threshold, so it will not defer the WF processing
5613 -- l_wf_current_threshold := Wf_Engine.threshold;
5614
5615 -- Defer the wf process
5616 -- yvchen: Bug 12888789, Don't change the Wf_Engine.threshold, so it will not defer the WF processing
5617 -- Wf_Engine.threshold := lc_wf_negative_threshold;
5618
5619 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5620 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5621 'Calling WF_ENGINE.CreateProcess');
5622 END IF;
5623
5624 Wf_Engine.CreateProcess(itemtype => p_flwsts_wf_rec.wf_item_type,
5625 itemkey => p_flwsts_wf_rec.wf_item_key,
5626 process => p_flwsts_wf_rec.wf_process_name --,
5627 -- user_key => NULL,
5628 -- owner_role => NULL
5629 );
5630
5631 l_wf_aname_TabType(1) := lc_repair_line_id;
5632 l_wf_avalue_TabType(1) := p_flwsts_wf_rec.repair_line_id;
5633
5634 l_wf_aname_TabType(2) := lc_repair_type_id;
5635 l_wf_avalue_TabType(2) := p_flwsts_wf_rec.repair_type_id;
5636
5637 l_wf_aname_TabType(3) := lc_from_flow_status_id;
5638 l_wf_avalue_TabType(3) := p_flwsts_wf_rec.from_flow_status_id;
5639
5640 l_wf_aname_TabType(4) := lc_to_flow_status_id;
5641 l_wf_avalue_TabType(4) := p_flwsts_wf_rec.to_flow_status_id;
5642
5643 l_wf_aname_TabType(5) := lc_object_version_number;
5644 l_wf_avalue_TabType(5) := p_flwsts_wf_rec.object_version_number;
5645
5646 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5647 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5648 'Calling WF_ENGINE.AddItemAttrNumberArray to add attributes');
5649 END IF;
5650
5651 Wf_Engine.AddItemAttrNumberArray(itemtype => p_flwsts_wf_rec.wf_item_type,
5652 itemkey => p_flwsts_wf_rec.wf_item_key,
5653 aname => l_wf_aname_TabType,
5654 avalue => l_wf_avalue_TabType
5655 );
5656
5657
5658 /*
5659 WF_ENGINE.AddItemAttr (itemtype => p_flwsts_wf_rec.wf_item_type,
5660 itemkey => p_flwsts_wf_rec.wf_item_key,
5661 aname => lc_repair_line_id,
5662 number_value => p_flwsts_wf_rec.repair_line_id
5663 );
5664
5665 WF_ENGINE.AddItemAttr (itemtype => p_flwsts_wf_rec.wf_item_type,
5666 itemkey => p_flwsts_wf_rec.wf_item_key,
5667 aname => lc_repair_type_id,
5668 number_value => p_flwsts_wf_rec.repair_type_id
5669 );
5670
5671 WF_ENGINE.AddItemAttr (itemtype => p_flwsts_wf_rec.wf_item_type,
5672 itemkey => p_flwsts_wf_rec.wf_item_key,
5673 aname => lc_from_flow_status_id,
5674 number_value => p_flwsts_wf_rec.from_flow_status_id
5675 );
5676
5677 WF_ENGINE.AddItemAttr (itemtype => p_flwsts_wf_rec.wf_item_type,
5678 itemkey => p_flwsts_wf_rec.wf_item_key,
5679 aname => lc_to_flow_status_id,
5680 number_value => p_flwsts_wf_rec.to_flow_status_id
5681 );
5682
5683 WF_ENGINE.AddItemAttr (itemtype => p_flwsts_wf_rec.wf_item_type,
5684 itemkey => p_flwsts_wf_rec.wf_item_key,
5685 aname => lc_object_version_number,
5686 number_value => p_flwsts_wf_rec.object_version_number
5687 );
5688 */
5689
5690 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5691 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name,
5692 'Calling WF_ENGINE.StartProcess');
5693 END IF;
5694
5695 -- The WF process is kicked off now in deferred mode
5696 Wf_Engine.StartProcess(itemtype => p_flwsts_wf_rec.wf_item_type,
5697 itemkey => p_flwsts_wf_rec.wf_item_key
5698 );
5699
5700
5701 -- Set engine to orginal threshold.
5702 -- Otherwise all WF process in this session will be deferred.
5703 -- yvchen: Bug 12888789, Don't change the Wf_Engine.threshold, so it will not defer the WF processing
5704 -- Wf_Engine.threshold := l_wf_current_threshold;
5705
5706 -- Api body ends here
5707
5708 -- Standard check of p_commit.
5709 IF Fnd_Api.To_Boolean( p_commit ) THEN
5710 COMMIT WORK;
5711 END IF;
5712
5713 -- Standard call to get message count and IF count is get message info.
5714 Fnd_Msg_Pub.Count_And_Get
5715 (p_count => x_msg_count,
5716 p_data => x_msg_data );
5717
5718 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5719 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.END',
5720 'Leaving Launch_Flwsts_Wf');
5721 END IF;
5722
5723 EXCEPTION
5724 WHEN Fnd_Api.G_EXC_ERROR THEN
5725 ROLLBACK TO Launch_Flwsts_Wf;
5726 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5727
5728 Fnd_Msg_Pub.Count_And_Get
5729 (p_count => x_msg_count,
5730 p_data => x_msg_data );
5731
5732 -- save message in debug log
5733 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5734 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
5735 'EXC_ERROR['||x_msg_data||']');
5736 END IF;
5737
5738 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
5739 ROLLBACK TO Launch_Flwsts_Wf;
5740 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5741
5742 -- save message in fnd stack
5743 IF Fnd_Msg_Pub.Check_Msg_Level
5744 (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
5745 THEN
5746 IF (Fnd_Log.LEVEL_STATEMENT >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5747 Fnd_Log.STRING(Fnd_Log.LEVEL_STATEMENT, lc_mod_name,
5748 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
5749 END IF;
5750 Fnd_Msg_Pub.Add_Exc_Msg
5751 (G_PKG_NAME ,
5752 lc_api_name );
5753 END IF;
5754
5755 Fnd_Msg_Pub.Count_And_Get
5756 ( p_count => x_msg_count,
5757 p_data => x_msg_data );
5758
5759 -- save message in debug log
5760 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5761 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
5762 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
5763 END IF;
5764
5765 WHEN OTHERS THEN
5766 ROLLBACK TO Launch_Flwsts_Wf;
5767 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5768
5769 -- save message in fnd stack
5770 IF Fnd_Msg_Pub.Check_Msg_Level
5771 (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
5772 THEN
5773 IF (Fnd_Log.LEVEL_STATEMENT >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5774 Fnd_Log.STRING(Fnd_Log.LEVEL_STATEMENT, lc_mod_name,
5775 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
5776 END IF;
5777 Fnd_Msg_Pub.Add_Exc_Msg
5778 (G_PKG_NAME ,
5779 lc_api_name );
5780 END IF;
5781
5782 Fnd_Msg_Pub.Count_And_Get
5783 (p_count => x_msg_count,
5784 p_data => x_msg_data );
5785
5786 -- save message in debug log
5787 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5788 -- create a seeded message
5789 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
5790 'SQL Message['||SQLERRM||']' );
5791 END IF;
5792
5793 END Launch_Flwsts_Wf;
5794
5795
5796
5797 -- *******************************************************
5798 -- API Name: UPDATE_RO_STATUS_WebSrvc
5799 -- Type : Private
5800 -- Pre-Req : None
5801 -- Parameters:
5802 -- IN
5803 -- p_api_version IN NUMBER,
5804 -- p_commit IN VARCHAR2,
5805 -- p_init_msg_list IN VARCHAR2,
5806 -- p_validation_level IN NUMBER,
5807 -- p_repair_line_id IN NUMEBR
5808 -- p_repair_status IN
5809 -- OUT
5810 -- x_return_status
5811 -- x_msg_count
5812 -- x_msg_data
5813 --
5814 -- Version : Current version 1.0
5815 -- Initial Version 1.0
5816 --
5817 -- Description : This API updates is a wrapper around the update_ro_Status
5818 -- private API. THis is used by the web service.
5819 --
5820 --
5821 -- ***********************************************************
5822 PROCEDURE UPDATE_RO_STATUS_WEBSRVC
5823 (
5824 p_api_version IN NUMBER,
5825 p_commit IN VARCHAR2,
5826 p_init_msg_list IN VARCHAR2,
5827 x_return_status OUT NOCOPY VARCHAR2,
5828 x_msg_count OUT NOCOPY NUMBER,
5829 x_msg_data OUT NOCOPY VARCHAR2,
5830 p_repair_line_id IN NUMBER,
5831 p_repair_status IN VARCHAR2,
5832 p_reason_code IN VARCHAR2,
5833 p_comments IN VARCHAR2,
5834 p_check_task_wip IN VARCHAR2,
5835 p_object_version_number IN NUMBER
5836
5837 ) IS
5838 l_repair_status_rec Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE;
5839 l_status_upd_control_rec Csd_Repairs_Pub.STATUS_UPD_CONTROL_REC_TYPE;
5840 l_object_Version_number NUMBER;
5841 l_return_status VARCHAR2(1);
5842
5843 lc_mod_name CONSTANT VARCHAR2(100) := 'csd.plsql.csd_repairs_pvt.update_ro_status_websrvc';
5844 lc_api_name CONSTANT VARCHAR2(30) := 'update_ro_status_websrvc';
5845 lc_api_version CONSTANT NUMBER := 1.0;
5846
5847 BEGIN
5848 -- Standard Start of API savepoint
5849 SAVEPOINT UPDATE_RO_STATUS_WEBSRVC_PVT;
5850
5851 -- Standard call to check for call compatibility.
5852 IF NOT Fnd_Api.Compatible_API_Call (lc_api_version,
5853 p_api_version,
5854 lc_api_name,
5855 G_PKG_NAME)
5856 THEN
5857 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
5858 END IF;
5859 --debug('security_group['||SYS_CONTEXT('FND','SECURITY_GROUP_ID')||']');
5860 --debug('resp_id['||SYS_CONTEXT('FND','RESP_ID')||']');
5861 --debug('resp_appl_id['||SYS_CONTEXT('FND','RESP_APPL_ID')||']');
5862 --debug('operatingunit['||SYS_CONTEXT('FND','ORG_ID')||']');
5863
5864 -- Initialize message list if p_init_msg_list is set to TRUE.
5865 IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
5866 Fnd_Msg_Pub.initialize;
5867 END IF;
5868
5869 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5870 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.BEGIN',
5871 'Entered UPDATE_RO_STATUS_WEBSRVC');
5872 END IF;
5873
5874 l_return_Status := Fnd_Api.G_RET_STS_SUCCESS;
5875
5876 --debug('In plsql api to update ro status for web service['||to_char(p_repair_line_id)||']');
5877 csd_repairs_util.Check_WebSrvc_Security(
5878 p_repair_line_id => p_repair_line_id,
5879 x_return_status => l_return_status);
5880
5881 IF(l_return_Status <> Fnd_Api.G_RET_STS_SUCCESS) THEN
5882 Fnd_Message.Set_Name('CSD', 'CSD_SECURITY_CHECK_FAILED');
5883 Fnd_Msg_Pub.ADD;
5884 RAISE Fnd_Api.G_EXC_ERROR;
5885 END IF;
5886
5887 l_repair_status_rec.repair_line_id := p_repair_line_id;
5888 l_repair_status_rec.repair_status := p_repair_status;
5889 l_repair_status_rec.reason_code := p_reason_code;
5890 l_repair_status_rec.comments := p_comments;
5891 l_repair_status_rec.object_version_number := p_object_version_number;
5892
5893 l_status_upd_control_rec.check_task_wip := p_check_task_wip;
5894
5895 --debug('calling update_ro_status private api');
5896 Csd_Repairs_Pvt.UPDATE_RO_STATUS(P_Api_Version => p_api_version,
5897 P_Commit => p_commit,
5898 P_Init_Msg_List => p_init_msg_list,
5899 P_Validation_Level => Fnd_Api.G_VALID_LEVEL_FULL,
5900 X_Return_Status => x_return_status,
5901 X_Msg_Count => x_msg_count,
5902 X_Msg_Data => x_msg_data,
5903 P_REPAIR_STATUS_REC => l_repair_status_rec,
5904 P_STATUS_CONTROL_REC => l_status_upd_control_rec,
5905 X_OBJECT_VERSION_NUMBER => l_object_Version_number);
5906 --
5907
5908 -- Standard check of p_commit.
5909 IF Fnd_Api.To_Boolean( p_commit ) THEN
5910 COMMIT WORK;
5911 END IF;
5912
5913 -- Standard call to get message count and IF count is get message info.
5914 Fnd_Msg_Pub.Count_And_Get
5915 (p_count => x_msg_count,
5916 p_data => x_msg_data );
5917
5918 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5919 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_mod_name || '.END',
5920 'Leaving UPDATE_RO_STATUS_WEBSRVC');
5921 END IF;
5922
5923 EXCEPTION
5924 WHEN Fnd_Api.G_EXC_ERROR THEN
5925 ROLLBACK TO UPDATE_RO_STATUS_WEBSRVC_PVT;
5926 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5927
5928 Fnd_Msg_Pub.Count_And_Get
5929 (p_count => x_msg_count,
5930 p_data => x_msg_data );
5931
5932 -- save message in debug log
5933 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5934 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
5935 'EXC_ERROR['||x_msg_data||']');
5936 END IF;
5937
5938 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
5939 ROLLBACK TO UPDATE_RO_STATUS_WEBSRVC_PVT;
5940 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5941
5942 -- save message in fnd stack
5943 IF Fnd_Msg_Pub.Check_Msg_Level
5944 (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
5945 THEN
5946 IF (Fnd_Log.LEVEL_STATEMENT >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5947 Fnd_Log.STRING(Fnd_Log.LEVEL_STATEMENT, lc_mod_name,
5948 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
5949 END IF;
5950 Fnd_Msg_Pub.Add_Exc_Msg
5951 (G_PKG_NAME ,
5952 lc_api_name );
5953 END IF;
5954
5955 Fnd_Msg_Pub.Count_And_Get
5956 ( p_count => x_msg_count,
5957 p_data => x_msg_data );
5958
5959 -- save message in debug log
5960 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5961 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
5962 'EXC_UNEXPECTED_ERROR['||x_msg_data||']');
5963 END IF;
5964
5965 WHEN OTHERS THEN
5966 ROLLBACK TO UPDATE_RO_STATUS_WEBSRVC_PVT;
5967 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5968
5969 -- save message in fnd stack
5970 IF Fnd_Msg_Pub.Check_Msg_Level
5971 (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
5972 THEN
5973 IF (Fnd_Log.LEVEL_STATEMENT >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5974 Fnd_Log.STRING(Fnd_Log.LEVEL_STATEMENT, lc_mod_name,
5975 'Adding message using FND_MSG_PUB.Add_Exc_Msg to FND_MSG stack');
5976 END IF;
5977 Fnd_Msg_Pub.Add_Exc_Msg
5978 (G_PKG_NAME ,
5979 lc_api_name );
5980 END IF;
5981
5982 Fnd_Msg_Pub.Count_And_Get
5983 (p_count => x_msg_count,
5984 p_data => x_msg_data );
5985
5986 -- save message in debug log
5987 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
5988 -- create a seeded message
5989 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION, lc_mod_name,
5990 'SQL Message['||SQLERRM||']' );
5991 END IF;
5992
5993 END UPDATE_RO_STATUS_WebSrvc;
5994
5995 /*-----------------------------------------------------------------*/
5996 /* procedure name: raise_ro_status_bevent */
5997 /* description : Procedure to raise a Business Even when the */
5998 /* status of the repair order changes */
5999 /*-----------------------------------------------------------------*/
6000 PROCEDURE Raise_RO_Status_BEvent
6001 (
6002 p_ro_status_bevent_rec IN ro_status_bevent_rec_type,
6003 p_commit IN VARCHAR2,
6004 x_return_status OUT NOCOPY VARCHAR2,
6005 x_msg_count OUT NOCOPY NUMBER,
6006 x_msg_data OUT NOCOPY VARCHAR2
6007 )
6008 IS
6009
6010 l_event_key Number;
6011 l_param_list wf_parameter_list_t := wf_parameter_list_t();
6012 l_param_t wf_parameter_t := wf_parameter_t(10,10);
6013 l_api_name Varchar2(30) := 'RAISE_RO_STATUS_BEVENT';
6014
6015 BEGIN
6016
6017 Savepoint ro_status_bevent_savepoint;
6018
6019 -- Initialize API return status to success
6020 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
6021
6022 -- Derive the Event Key
6023 Select csd_ro_status_bevent_key_s1.nextval into l_event_key from dual ;
6024
6025 -- Initialize Parameters
6026 l_param_list.extend;
6027 l_param_list(1) := wf_parameter_t('REPAIR_LINE_ID',p_ro_status_bevent_rec.repair_line_id);
6028
6029 l_param_list.extend;
6030 l_param_list(2) := wf_parameter_t('FROM_STATUS_ID',p_ro_status_bevent_rec.from_flow_status_id);
6031
6032 l_param_list.extend;
6033 l_param_list(3) := wf_parameter_t('TO_STATUS_ID',p_ro_status_bevent_rec.to_flow_status_id);
6034
6035 l_param_list.extend;
6036 l_param_list(4) := wf_parameter_t('OBJECT_VERSION_NUMBER',p_ro_status_bevent_rec.object_version_number);
6037
6038 -- Call to Raise the Business Event
6039 wf_event.raise
6040 ( p_event_name => 'oracle.apps.csd.repair.status.change',
6041 p_event_key => l_event_key,
6042 p_parameters => l_param_list);
6043
6044 -- Standard check of p_commit.
6045 If FND_API.To_Boolean( p_commit ) then
6046 commit;
6047 End if;
6048
6049 EXCEPTION
6050 When OTHERS then
6051 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6052 Rollback To ro_status_bevent_savepoint;
6053 If FND_MSG_PUB.Check_Msg_Level
6054 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
6055 FND_MSG_PUB.Add_Exc_Msg
6056 (G_PKG_NAME,
6057 l_api_name );
6058 End If;
6059 FND_MSG_PUB.Count_And_Get
6060 (p_count => x_msg_count,
6061 p_data => x_msg_data );
6062 END;
6063
6064 -- R12 development changes End...
6065
6066
6067 /*-------------------------------------------------------------------------------------*/
6068 /* Procedure name: UPDATE_RO_STATUS_WF */
6069 /* Description : Procedure called from workflow process to update repair order */
6070 /* status */
6071 /* */
6072 /* Called from : Workflow */
6073 /* PARAMETERS */
6074 /* IN */
6075 /* */
6076 /* itemtype - type of the current item */
6077 /* itemkey - key of the current item */
6078 /* actid - process activity instance id */
6079 /* funcmode - function execution mode ('RUN', 'CANCEL', 'TIMEOUT', ...) */
6080 /* OUT */
6081 /* result */
6082 /* - COMPLETE[:<result>] */
6083 /* activity has completed with the indicated result */
6084 /* - WAITING */
6085 /* activity is waiting for additional transitions */
6086 /* - DEFERED */
6087 /* execution should be defered to background */
6088 /* - NOTIFIED[:<notification_id>:<assigned_user>] */
6089 /* activity has notified an external entity that this */
6090 /* step must be performed. A call to wf_engine.CompleteActivty */
6091 /* will signal when this step is complete. Optional */
6092 /* return of notification ID and assigned user. */
6093 /* - ERROR[:<error_code>] */
6094 /* function encountered an error. */
6095 /* Change Hist : */
6096 /* 04/18/06 mshirkol Initial Creation. ( Fix for bug#5610891 ) */
6097 /*-------------------------------------------------------------------------------------*/
6098
6099 Procedure UPDATE_RO_STATUS_WF
6100 (itemtype in varchar2,
6101 itemkey in varchar2,
6102 actid in number,
6103 funcmode in varchar2,
6104 resultout in out nocopy varchar2) IS
6105
6106 l_line_id number;
6107 l_repair_line_id number;
6108 l_return_status varchar2(3);
6109 l_msg_count number;
6110 l_msg_data varchar2(2000);
6111 l_module_name varchar2(80);
6112
6113 --bug#9284580
6114 l_from_flow_status_id number;
6115 l_to_flow_status_id number;
6116 l_repair_type_id number;
6117 l_object_version_number number;
6118 x_object_version_number number;
6119 --bug#9284580
6120
6121 Cursor get_ro_details ( p_line_id in number ) is
6122 select
6123 csd.repair_line_id
6124 from
6125 cs_estimate_details est,
6126 csd_repairs csd
6127 where
6128 est.order_line_id = p_line_id
6129 and est.original_source_id = csd.repair_line_id;
6130
6131 BEGIN
6132
6133 --bug#9284580
6134 -- set the return status
6135 l_return_status := fnd_api.g_ret_sts_success;
6136 --bug#9284580
6137
6138 IF ( funcmode = 'RUN' ) THEN
6139
6140 l_line_id := to_number(itemkey);
6141
6142 --
6143 -- Derive the wf roles for the Contact id
6144 --
6145 Open get_ro_details (l_line_id);
6146 Fetch get_ro_details into l_repair_line_id;
6147 Close get_ro_details;
6148
6149
6150 --bug#9284580
6151 l_to_flow_status_id := fnd_profile.value('CSD_DEF_RO_STAT_FR_SHP');
6152
6153 -- get the 'from' flow status id.
6154 --
6155 begin
6156 select distinct cr.flow_status_id,
6157 cr.repair_type_id,
6158 cr.object_version_number
6159 into l_from_flow_status_id,
6160 l_repair_type_id,
6161 l_object_version_number
6162 from csd_repairs cr
6163 where cr.repair_line_id = l_repair_line_id;
6164 exception
6165 when no_data_found then
6166 -- should never get in here.
6167 null;
6168 end;
6169
6170 if l_to_flow_status_id <> l_from_flow_status_id then
6171 csd_repairs_pvt.update_flow_status(p_api_version => 1.0,
6172 p_commit => fnd_api.g_false,
6173 p_init_msg_list => fnd_api.g_false,
6174 p_validation_level => fnd_api.g_valid_level_full,
6175 x_return_status => l_return_status,
6176 x_msg_count => l_msg_count,
6177 x_msg_data => l_msg_data,
6178 p_repair_line_id => l_repair_line_id,
6179 p_repair_type_id => l_repair_type_id,
6180 p_from_flow_status_id => l_from_flow_status_id,
6181 p_to_flow_status_id => l_to_flow_status_id,
6182 p_reason_code => null,
6183 p_comments => null,
6184 p_check_access_flag => 'Y',
6185 p_object_version_number => l_object_version_number,
6186 x_object_version_number => x_object_version_number );
6187 end if;
6188 /*
6189 CSD_PROCESS_PVT.Close_status
6190 ( p_api_version => 1.0,
6191 p_commit => 'T',
6192 p_init_msg_list => 'T',
6193 p_validation_level => CSD_PROCESS_UTIL.G_VALID_LEVEL_FULL,
6194 p_incident_id => NULL,
6195 p_repair_line_id => l_repair_line_id,
6196 x_return_status => l_return_status,
6197 x_msg_count => l_msg_count,
6198 x_msg_data => l_msg_data
6199 );
6200 */
6201 --bug#9284580
6202
6203 IF NOT(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
6204
6205 resultout := 'COMPLETE:ERROR';
6206
6207 l_module_name := 'UPDATE_RO_STATUS';
6208
6209 CSD_REPAIRS_PVT.LAUNCH_WFEXCEPTION_BEVENT(
6210 p_return_status => l_return_status,
6211 p_msg_count => l_msg_count,
6212 p_msg_data => l_msg_data,
6213 p_repair_line_id => l_repair_line_id,
6214 p_module_name => l_module_name);
6215
6216 ELSE
6217 resultout := 'COMPLETE:SUCCESS';
6218 END IF;
6219
6220 return;
6221
6222 END IF;
6223
6224 EXCEPTION
6225 WHEN OTHERS THEN
6226 WF_CORE.CONTEXT('CSD_REPAIRS_PVT','UPDATE_RO_STATUS_WF',itemtype,
6227 itemkey,to_char(actid),funcmode);
6228 raise;
6229 END;
6230
6231 /*-------------------------------------------------------------------------------------*/
6232 /* Procedure name: LAUNCH_WFEXCEPTIONS_BEVENT */
6233 /* Description : Procedure to launch exceptions Business Event */
6234 /* */
6235 /* Called from : CSD_UPDATE_PROGRAMS_PVT */
6236 /* PARAMETERS */
6237 /* IN */
6238 /* p_return_status */
6239 /* p_msg_count */
6240 /* p_msg_data */
6241 /* p_repair_line_id */
6242 /* p_module_name */
6243 /* */
6244 /* Change Hist : */
6245 /* 04/18/06 mshirkol Initial Creation. ( Fix for bug#5610891 ) */
6246 /*-------------------------------------------------------------------------------------*/
6247 Procedure LAUNCH_WFEXCEPTION_BEVENT(
6248 p_return_status in varchar2,
6249 p_msg_count in number,
6250 p_msg_data in varchar2,
6251 p_repair_line_id in number,
6252 p_module_name in varchar2) IS
6253
6254 l_msg varchar2(2000);
6255 l_next_msg varchar2(2000);
6256 l_event_key number;
6257 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
6258 l_event_name varchar2(60) := 'oracle.apps.csd.repair.wfprocess.exceptions';
6259 l_message_code varchar2(3);
6260
6261 Cursor get_event_key is
6262 select CSD_WF_EXCEPTIONS_BEVENT_S1.nextval from dual;
6263
6264 BEGIN
6265
6266 -- Derive the message from the message stack
6267 l_msg := p_msg_data;
6268 l_message_code := p_return_status;
6269
6270 IF p_msg_count = 1 THEN
6271
6272 IF l_msg is null then
6273 l_msg := fnd_msg_pub.get(p_msg_index => 1,
6274 p_encoded => FND_API.G_FALSE );
6275 ELSE
6276 l_next_msg := fnd_msg_pub.get(p_msg_index => 1,
6277 p_encoded => FND_API.G_FALSE );
6278
6279 l_msg := substr(l_msg ||'-'||rtrim(l_next_msg),1,2000);
6280 END IF;
6281
6282 ELSIF p_msg_count > 1 THEN
6283
6284 FOR i in 1..p_msg_count LOOP
6285
6286 IF l_msg is null THEN
6287 l_msg := fnd_msg_pub.get(p_msg_index => i,
6288 p_encoded => FND_API.G_FALSE );
6289 ELSE
6290 l_next_msg := fnd_msg_pub.get(p_msg_index => i,
6291 p_encoded => FND_API.G_FALSE );
6292
6293 l_msg := substr(l_msg ||'-'||rtrim(l_next_msg),1,2000);
6294
6295 END IF;
6296
6297 END LOOP;
6298
6299 END IF;
6300
6301
6302 -- Derive the event key
6303 -- Derive this value from sequence..
6304 Open get_event_key;
6305 Fetch get_event_key into l_event_key;
6306 Close get_event_key;
6307
6308 IF l_msg is null THEN
6309 l_msg := ' ';
6310 END IF;
6311
6312 -- Initialize the Parameters
6313 l_parameter_list.extend;
6314 l_parameter_list(1):= wf_parameter_t('REPAIR_LINE_ID',to_char(p_repair_line_id));
6315
6316 l_parameter_list.extend;
6317 l_parameter_list(2):= wf_parameter_t('MODULE_NAME',p_module_name);
6318
6319 l_parameter_list.extend;
6320 l_parameter_list(3):= wf_parameter_t('MESSAGE_CODE',l_message_code);
6321
6322 l_parameter_list.extend;
6323 l_parameter_list(4) := wf_parameter_t('MESSAGE_TEXT',l_msg);
6324
6325 -- Call the Raise Event
6326 wf_event.raise(
6327 p_event_name => l_event_name,
6328 p_event_key => l_event_key,
6329 p_parameters => l_parameter_list);
6330
6331 commit;
6332
6333 END;
6334
6335 END Csd_Repairs_Pvt;