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