[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIRS_UTIL
Source
1 Package BODY Csd_Repairs_Util AS
2 /* $Header: csdxutlb.pls 120.14.12010000.5 2008/12/19 02:00:27 swai ship $ */
3 --
4 -- Package name : CSD_REPAIRS_UTIL
5 -- Purpose : This package contains utility programs for the Depot
6 -- Repair module. Access is restricted to Oracle Depot
7 -- Repair Internal Development.
8 -- History :
9 -- Version Date Name Description
10 -- 115.0 12/18/99 pkdas Created.
11 -- 115.1 01/04/00 pkdas Added some program units.
12 -- 115.2 01/18/00 pkdas Added DATE_CLOSED to Convert_to_Repln_Rec_Type procedure.
13 -- 115.3 02/23/00 pkdas Added CONTRACT_LINE_ID to Convert_to_Repln_Rec_Type procedure.
14 -- 115.4 11/30/01 travi Added AUTO_PROCESS_RMA, OBJECT_VERSION_NUMBER and REPAIR_MODE
15 -- to Convert_to_Repln_Rec_Type
16 -- 115.5 01/14/02 travi Added Item_REVISION col
17 -- 115.19 05/19/05 vparvath Added check_task_n_wipjob proc for
18 -- R12 development.
19 -- 120.2 07/13/05 vparvath Added utility proc for webservice
20 --
21 -- NOTE :
22 --
23 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_REPAIRS_UTIL';
24 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdxutlb.pls';
25 g_debug NUMBER := Csd_Gen_Utility_Pvt.g_debug_level;
26
27 procedure debug( l_msg in varchar2)
28 is
29 --pragma autonomous_transaction;
30 begin
31 --dbms_output.put_line(substr(l_msg,1,255));
32 --dbms_output.put_line(substr(l_msg,255,255));
33 --insert into apps.vijay_debug(log_msg, timestamp) values(l_msg, sysdate);
34 --commit;
35 null;
36 end;
37 --
38 PROCEDURE Check_Reqd_Param (
39 p_param_value IN NUMBER,
40 p_param_name IN VARCHAR2,
41 p_api_name IN VARCHAR2
42 )
43 IS
44 --
45 BEGIN
46 --
47 IF (NVL(p_param_value,Fnd_Api.G_MISS_NUM) = Fnd_Api.G_MISS_NUM) THEN
48 Fnd_Message.SET_NAME('CSD','CSD_API_MISSING_PARAM');
49 Fnd_Message.SET_TOKEN('API_NAME',p_api_name);
50 Fnd_Message.SET_TOKEN('MISSING_PARAM',p_param_name);
51 Fnd_Msg_Pub.ADD;
52 RAISE Fnd_Api.G_EXC_ERROR;
53 END IF;
54 END Check_Reqd_Param;
55
56 PROCEDURE Check_Reqd_Param (
57 p_param_value IN VARCHAR2,
58 p_param_name IN VARCHAR2,
59 p_api_name IN VARCHAR2
60 )
61 IS
62 --
63 BEGIN
64 --
65 IF (NVL(p_param_value,Fnd_Api.G_MISS_CHAR) = Fnd_Api.G_MISS_CHAR) THEN
66 Fnd_Message.SET_NAME('CSD','CSD_API_MISSING_PARAM');
67 Fnd_Message.SET_TOKEN('API_NAME',p_api_name);
68 Fnd_Message.SET_TOKEN('MISSING_PARAM',p_param_name);
69 Fnd_Msg_Pub.ADD;
70 RAISE Fnd_Api.G_EXC_ERROR;
71 END IF;
72 END Check_Reqd_Param;
73
74 PROCEDURE Check_Reqd_Param (
75 p_param_value IN DATE,
76 p_param_name IN VARCHAR2,
77 p_api_name IN VARCHAR2
78 )
79 IS
80 --
81 BEGIN
82 --
83 IF (NVL(p_param_value,Fnd_Api.G_MISS_DATE) = Fnd_Api.G_MISS_DATE) THEN
84 Fnd_Message.SET_NAME('CSD','CSD_API_MISSING_PARAM');
85 Fnd_Message.SET_TOKEN('API_NAME',p_api_name);
86 Fnd_Message.SET_TOKEN('MISSING_PARAM',p_param_name);
87 Fnd_Msg_Pub.ADD;
88 RAISE Fnd_Api.G_EXC_ERROR;
89 END IF;
90 END Check_Reqd_Param;
91
92 --
93 -- bug#7151536, subhat.
94 -- changed the p_attr_values to IN OUT from IN
95 -- added a new parameter p_validate_only. This parameter would
96 -- indicate if we wish to default the required values or not.
97 -- If we need to validate and default then pass FND_API.G_FALSE
98 -- For validate only it will be default FND_API.G_TRUE.
99
100 FUNCTION Is_DescFlex_Valid
101 ( p_api_name IN VARCHAR2,
102 p_desc_flex_name IN VARCHAR2,
103 p_attr_values IN OUT NOCOPY CSD_REPAIRS_UTIL.DEF_Rec_Type,
104 -- bug#7151536, subhat.
105 p_validate_only IN VARCHAR2 := FND_API.G_TRUE
106 ) RETURN BOOLEAN IS
107 --
108 l_error_message VARCHAR2(2000);
109 l_return_status BOOLEAN := TRUE;
110 -- bug#7151536, subhat. new variables added
111 l_segment_count NUMBER;
112 l_column_name VARCHAR2(30);
113 l_values_or_ids varchar2(3);
114 -- end bug#7151536, subhat.
115 --
116 BEGIN
117 --
118 fnd_flex_descval.set_context_value(p_attr_values.attribute_category);
119 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attr_values.attribute1);
120 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_attr_values.attribute2);
121 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_attr_values.attribute3);
122 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_attr_values.attribute4);
123 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_attr_values.attribute5);
124 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_attr_values.attribute6);
125 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_attr_values.attribute7);
126 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_attr_values.attribute8);
127 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_attr_values.attribute9);
128 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_attr_values.attribute10);
129 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_attr_values.attribute11);
130 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_attr_values.attribute12);
131 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_attr_values.attribute13);
132 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_attr_values.attribute14);
133 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_attr_values.attribute15);
134 -- additional DFF columns(bug#7497907)
135 fnd_flex_descval.set_column_value('ATTRIBUTE16', p_attr_values.attribute16);
136 fnd_flex_descval.set_column_value('ATTRIBUTE17', p_attr_values.attribute17);
137 fnd_flex_descval.set_column_value('ATTRIBUTE18', p_attr_values.attribute18);
138 fnd_flex_descval.set_column_value('ATTRIBUTE19', p_attr_values.attribute19);
139 fnd_flex_descval.set_column_value('ATTRIBUTE20', p_attr_values.attribute20);
140 fnd_flex_descval.set_column_value('ATTRIBUTE21', p_attr_values.attribute21);
141 fnd_flex_descval.set_column_value('ATTRIBUTE22', p_attr_values.attribute22);
142 fnd_flex_descval.set_column_value('ATTRIBUTE23', p_attr_values.attribute23);
143 fnd_flex_descval.set_column_value('ATTRIBUTE24', p_attr_values.attribute24);
144 fnd_flex_descval.set_column_value('ATTRIBUTE25', p_attr_values.attribute25);
145 fnd_flex_descval.set_column_value('ATTRIBUTE26', p_attr_values.attribute26);
146 fnd_flex_descval.set_column_value('ATTRIBUTE27', p_attr_values.attribute27);
147 fnd_flex_descval.set_column_value('ATTRIBUTE28', p_attr_values.attribute28);
148 fnd_flex_descval.set_column_value('ATTRIBUTE29', p_attr_values.attribute29);
149 fnd_flex_descval.set_column_value('ATTRIBUTE30', p_attr_values.attribute30);
150 --
151 --
152 -- bug#7151536, subhat.
153 -- Flex engine doesnt default the values if we pass default value for
154 -- values_or_ids.
155 -- need to pass in 'V' (refer bug#2221725)
156 -- Pass in values_or_ids = 'V' only if p_validate_only is False.
157 if p_validate_only = FND_API.G_FALSE then
158 l_values_or_ids := 'V';
159 else
160 l_values_or_ids := 'I';
161 end if;
162 --
163 If NOT fnd_flex_descval.validate_desccols
164 (appl_short_name => 'CSD',
165 desc_flex_name => p_desc_flex_name,
166 values_or_ids => l_values_or_ids)
167 -- end bug#7151536, subhat.
168 then
169 l_error_message := fnd_flex_descval.error_message;
170 If fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
171 fnd_message.set_name('CSD', 'CSD_API_DESC_FLEX_ERR');
172 fnd_message.set_token('API_NAME', p_api_name);
173 fnd_message.set_token('DESC_FLEX_MSG', l_error_message);
174 fnd_msg_pub.add;
175 end if;
176 l_return_status := FALSE;
177 -- bug#7043215, subhat
178 -- added else to populate the derived/validated value to the global collection
179 -- Note: In case any new columns are added to CSD_REPAIRS DFF, they need to be added here too.
180 else
181 -- populate the out rec only if its in create mode.
182 if l_values_or_ids = 'V' then
183 l_segment_count := fnd_flex_descval.segment_count;
184 for i in 1 ..l_segment_count
185 loop
186 l_column_name := fnd_flex_descval.segment_column_name(i);
187
188 CASE l_column_name
189 WHEN 'ATTRIBUTE_CATEGORY' then
190 p_attr_values.attribute_category := fnd_flex_descval.segment_value(i);
191 WHEN 'ATTRIBUTE1' then
192 p_attr_values.attribute1 := fnd_flex_descval.segment_value(i);
193 WHEN 'ATTRIBUTE2' then
194 p_attr_values.attribute2 := fnd_flex_descval.segment_value(i);
195 WHEN 'ATTRIBUTE3' then
196 p_attr_values.attribute3 := fnd_flex_descval.segment_value(i);
197 WHEN 'ATTRIBUTE4' then
198 p_attr_values.attribute4 := fnd_flex_descval.segment_value(i);
199 WHEN 'ATTRIBUTE5' then
200 p_attr_values.attribute5 := fnd_flex_descval.segment_value(i);
201 WHEN 'ATTRIBUTE6' then
202 p_attr_values.attribute6 := fnd_flex_descval.segment_value(i);
203 WHEN 'ATTRIBUTE7' then
204 p_attr_values.attribute7 := fnd_flex_descval.segment_value(i);
205 WHEN 'ATTRIBUTE8' then
206 p_attr_values.attribute8 := fnd_flex_descval.segment_value(i);
207 WHEN 'ATTRIBUTE9' then
208 p_attr_values.attribute9 := fnd_flex_descval.segment_value(i);
209 WHEN 'ATTRIBUTE10' then
210 p_attr_values.attribute10 := fnd_flex_descval.segment_value(i);
211 WHEN 'ATTRIBUTE11' then
212 p_attr_values.attribute11 := fnd_flex_descval.segment_value(i);
213 WHEN 'ATTRIBUTE12' then
214 p_attr_values.attribute12 := fnd_flex_descval.segment_value(i);
215 WHEN 'ATTRIBUTE13' then
216 p_attr_values.attribute13 := fnd_flex_descval.segment_value(i);
217 WHEN 'ATTRIBUTE14' then
218 p_attr_values.attribute14 := fnd_flex_descval.segment_value(i);
219 WHEN 'ATTRIBUTE15' then
220 p_attr_values.attribute15 := fnd_flex_descval.segment_value(i);
221 --* additional DFF attributes, subhat(bug#7497907)
222 WHEN 'ATTRIBUTE16' then
223 p_attr_values.attribute16 := fnd_flex_descval.segment_value(i);
224 WHEN 'ATTRIBUTE17' then
225 p_attr_values.attribute17 := fnd_flex_descval.segment_value(i);
226 WHEN 'ATTRIBUTE18' then
227 p_attr_values.attribute18 := fnd_flex_descval.segment_value(i);
228 WHEN 'ATTRIBUTE19' then
229 p_attr_values.attribute19 := fnd_flex_descval.segment_value(i);
230 WHEN 'ATTRIBUTE20' then
231 p_attr_values.attribute20 := fnd_flex_descval.segment_value(i);
232 WHEN 'ATTRIBUTE21' then
233 p_attr_values.attribute21 := fnd_flex_descval.segment_value(i);
234 WHEN 'ATTRIBUTE22' then
235 p_attr_values.attribute22 := fnd_flex_descval.segment_value(i);
236 WHEN 'ATTRIBUTE23' then
237 p_attr_values.attribute23 := fnd_flex_descval.segment_value(i);
238 WHEN 'ATTRIBUTE24' then
239 p_attr_values.attribute24 := fnd_flex_descval.segment_value(i);
240 WHEN 'ATTRIBUTE25' then
241 p_attr_values.attribute25 := fnd_flex_descval.segment_value(i);
242 WHEN 'ATTRIBUTE26' then
243 p_attr_values.attribute26 := fnd_flex_descval.segment_value(i);
244 WHEN 'ATTRIBUTE27' then
245 p_attr_values.attribute27 := fnd_flex_descval.segment_value(i);
246 WHEN 'ATTRIBUTE28' then
247 p_attr_values.attribute28 := fnd_flex_descval.segment_value(i);
248 WHEN 'ATTRIBUTE29' then
249 p_attr_values.attribute29 := fnd_flex_descval.segment_value(i);
250 WHEN 'ATTRIBUTE30' then
251 p_attr_values.attribute30 := fnd_flex_descval.segment_value(i);
252 ELSE
253 null;
254 END CASE;
255 end loop;
256 end if;
257 END IF;
258 --
259 RETURN (l_return_status);
260 --
261 END Is_DescFlex_Valid;
262
263 PROCEDURE Convert_to_Repln_Rec_Type
264 (
265 p_REPAIR_NUMBER IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
266 p_INCIDENT_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
267 p_INVENTORY_ITEM_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
268 p_CUSTOMER_PRODUCT_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
269 p_UNIT_OF_MEASURE IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
270 p_REPAIR_TYPE_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
271 -- RESOURCE_GROUP Added by Vijay 10/28/2004
272 p_RESOURCE_GROUP IN NUMBER := Fnd_Api.G_MISS_NUM,
273 p_RESOURCE_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
274 p_PROJECT_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
275 p_TASK_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
276 p_UNIT_NUMBER IN VARCHAR2 := FND_API.G_MISS_CHAR, -- rfieldma, project_integration
277 p_CONTRACT_LINE_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
278 p_AUTO_PROCESS_RMA IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
279 p_REPAIR_MODE IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
280 p_OBJECT_VERSION_NUMBER IN NUMBER := Fnd_Api.G_MISS_NUM,
281 p_ITEM_REVISION IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
282 p_INSTANCE_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
283 p_STATUS IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
284 p_STATUS_REASON_CODE IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
285 p_DATE_CLOSED IN DATE := Fnd_Api.G_MISS_DATE,
286 p_APPROVAL_REQUIRED_FLAG IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
287 p_APPROVAL_STATUS IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
288 p_SERIAL_NUMBER IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
289 p_PROMISE_DATE IN DATE := Fnd_Api.G_MISS_DATE,
290 p_ATTRIBUTE_CATEGORY IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
291 p_ATTRIBUTE1 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
292 p_ATTRIBUTE2 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
293 p_ATTRIBUTE3 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
294 p_ATTRIBUTE4 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
295 p_ATTRIBUTE5 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
296 p_ATTRIBUTE6 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
297 p_ATTRIBUTE7 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
298 p_ATTRIBUTE8 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
299 p_ATTRIBUTE9 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
300 p_ATTRIBUTE10 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
301 p_ATTRIBUTE11 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
302 p_ATTRIBUTE12 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
303 p_ATTRIBUTE13 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
304 p_ATTRIBUTE14 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
305 p_ATTRIBUTE15 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
306 P_ATTRIBUTE16 IN VARCHAR2 := FND_API.G_MISS_CHAR,-- SUBHAT, DFF CHANGES(bug#7497907)
307 P_ATTRIBUTE17 IN VARCHAR2 := FND_API.G_MISS_CHAR,
308 P_ATTRIBUTE18 IN VARCHAR2 := FND_API.G_MISS_CHAR,
309 P_ATTRIBUTE19 IN VARCHAR2 := FND_API.G_MISS_CHAR,
310 P_ATTRIBUTE20 IN VARCHAR2 := FND_API.G_MISS_CHAR,
311 P_ATTRIBUTE21 IN VARCHAR2 := FND_API.G_MISS_CHAR,
312 P_ATTRIBUTE22 IN VARCHAR2 := FND_API.G_MISS_CHAR,
313 P_ATTRIBUTE23 IN VARCHAR2 := FND_API.G_MISS_CHAR,
314 P_ATTRIBUTE24 IN VARCHAR2 := FND_API.G_MISS_CHAR,
315 P_ATTRIBUTE25 IN VARCHAR2 := FND_API.G_MISS_CHAR,
316 P_ATTRIBUTE26 IN VARCHAR2 := FND_API.G_MISS_CHAR,
317 P_ATTRIBUTE27 IN VARCHAR2 := FND_API.G_MISS_CHAR,
318 P_ATTRIBUTE28 IN VARCHAR2 := FND_API.G_MISS_CHAR,
319 P_ATTRIBUTE29 IN VARCHAR2 := FND_API.G_MISS_CHAR,
320 P_ATTRIBUTE30 IN VARCHAR2 := FND_API.G_MISS_CHAR,
321 p_QUANTITY IN NUMBER := Fnd_Api.G_MISS_NUM,
322 p_QUANTITY_IN_WIP IN NUMBER := Fnd_Api.G_MISS_NUM,
323 p_QUANTITY_RCVD IN NUMBER := Fnd_Api.G_MISS_NUM,
324 p_QUANTITY_SHIPPED IN NUMBER := Fnd_Api.G_MISS_NUM,
325 p_CURRENCY_CODE IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
326 p_DEFAULT_PO_NUM IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
327 p_REPAIR_GROUP_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
328 p_RO_TXN_STATUS IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
329 p_ORDER_LINE_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
330 p_ORIGINAL_SOURCE_REFERENCE IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
331 p_ORIGINAL_SOURCE_HEADER_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
332 p_ORIGINAL_SOURCE_LINE_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
333 p_PRICE_LIST_HEADER_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
334 p_INVENTORY_ORG_ID IN NUMBER := Fnd_Api.G_MISS_NUM,
335 -- swai: bug 4666344 added problem description
336 p_PROBLEM_DESCRIPTION IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
337 p_RO_PRIORITY_CODE IN VARCHAR2 := Fnd_Api.G_MISS_CHAR, -- swai: R12
338 p_RESOLVE_BY_DATE IN DATE := Fnd_Api.G_MISS_DATE, -- rfieldma: 5355051
339 p_BULLETIN_CHECK_DATE IN DATE := Fnd_Api.G_MISS_DATE,
340 p_ESCALATION_CODE IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
341 x_Repln_Rec OUT NOCOPY Csd_Repairs_Pub.Repln_Rec_Type
342 ) IS
343 --
344 BEGIN
345
346 IF (g_debug > 0 ) THEN
347 Csd_Gen_Utility_Pvt.ADD( 'at begin convert rec');
348 END IF;
349
350
351 x_Repln_Rec.REPAIR_NUMBER := p_REPAIR_NUMBER;
352 x_Repln_Rec.INCIDENT_ID := p_INCIDENT_ID;
353 x_Repln_Rec.INVENTORY_ITEM_ID := p_INVENTORY_ITEM_ID;
354 x_Repln_Rec.CUSTOMER_PRODUCT_ID := p_CUSTOMER_PRODUCT_ID;
355 x_Repln_Rec.UNIT_OF_MEASURE := p_UNIT_OF_MEASURE;
356 x_Repln_Rec.REPAIR_TYPE_ID := p_REPAIR_TYPE_ID;
357 -- RESOURCE_GROUP Added by Vijay 10/28/2004
358 x_Repln_Rec.RESOURCE_GROUP := p_RESOURCE_GROUP;
359 x_Repln_Rec.RESOURCE_ID := p_RESOURCE_ID;
360 x_Repln_Rec.PROJECT_ID := p_PROJECT_ID;
361 x_Repln_Rec.TASK_ID := p_TASK_ID;
362 x_Repln_Rec.UNIT_NUMBER := p_UNIT_NUMBER; -- rfieldma, project integration
363 x_Repln_Rec.CONTRACT_LINE_ID := p_CONTRACT_LINE_ID;
364 x_Repln_Rec.AUTO_PROCESS_RMA := p_AUTO_PROCESS_RMA;
365 x_Repln_Rec.REPAIR_MODE := p_REPAIR_MODE;
366 x_Repln_Rec.OBJECT_VERSION_NUMBER := p_OBJECT_VERSION_NUMBER;
367 x_Repln_Rec.ITEM_REVISION := p_ITEM_REVISION;
368 x_Repln_Rec.INSTANCE_ID := p_INSTANCE_ID;
369 x_Repln_Rec.STATUS := p_STATUS;
370 x_Repln_Rec.STATUS_REASON_CODE := p_STATUS_REASON_CODE;
371 x_Repln_Rec.DATE_CLOSED := p_DATE_CLOSED;
372 x_Repln_Rec.APPROVAL_REQUIRED_FLAG := p_APPROVAL_REQUIRED_FLAG;
373 x_Repln_Rec.APPROVAL_STATUS := p_APPROVAL_STATUS;
374 x_Repln_Rec.SERIAL_NUMBER := p_SERIAL_NUMBER;
375 x_Repln_Rec.PROMISE_DATE := p_PROMISE_DATE;
376 x_Repln_Rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
377 x_Repln_Rec.ATTRIBUTE1 := p_ATTRIBUTE1;
378 x_Repln_Rec.ATTRIBUTE2 := p_ATTRIBUTE2;
379 x_Repln_Rec.ATTRIBUTE3 := p_ATTRIBUTE3;
380 x_Repln_Rec.ATTRIBUTE4 := p_ATTRIBUTE4;
381 x_Repln_Rec.ATTRIBUTE5 := p_ATTRIBUTE5;
382 x_Repln_Rec.ATTRIBUTE6 := p_ATTRIBUTE6;
383 x_Repln_Rec.ATTRIBUTE7 := p_ATTRIBUTE7;
384 x_Repln_Rec.ATTRIBUTE8 := p_ATTRIBUTE8;
385 x_Repln_Rec.ATTRIBUTE9 := p_ATTRIBUTE9;
386 x_Repln_Rec.ATTRIBUTE10 := p_ATTRIBUTE10;
387 x_Repln_Rec.ATTRIBUTE11 := p_ATTRIBUTE11;
388 x_Repln_Rec.ATTRIBUTE12 := p_ATTRIBUTE12;
389 x_Repln_Rec.ATTRIBUTE13 := p_ATTRIBUTE13;
390 x_Repln_Rec.ATTRIBUTE14 := p_ATTRIBUTE14;
391 x_Repln_Rec.ATTRIBUTE15 := p_ATTRIBUTE15;
392 x_Repln_Rec.QUANTITY := p_QUANTITY;
393 x_Repln_Rec.QUANTITY_IN_WIP := p_QUANTITY_IN_WIP;
394 x_Repln_Rec.QUANTITY_RCVD := p_QUANTITY_RCVD;
395 x_Repln_Rec.QUANTITY_SHIPPED := p_QUANTITY_SHIPPED;
396 x_Repln_Rec.CURRENCY_CODE := p_CURRENCY_CODE;
397 x_Repln_Rec.DEFAULT_PO_NUM := p_DEFAULT_PO_NUM;
398 x_Repln_Rec.REPAIR_GROUP_ID := p_REPAIR_GROUP_ID;
399 x_Repln_Rec.RO_TXN_STATUS := p_RO_TXN_STATUS;
400 x_Repln_Rec.ORDER_LINE_ID := p_ORDER_LINE_ID;
401 x_Repln_Rec.ORIGINAL_SOURCE_REFERENCE := p_ORIGINAL_SOURCE_REFERENCE;
402 x_Repln_Rec.ORIGINAL_SOURCE_HEADER_ID := p_ORIGINAL_SOURCE_HEADER_ID;
403 x_Repln_Rec.ORIGINAL_SOURCE_LINE_ID := p_ORIGINAL_SOURCE_LINE_ID;
404 x_Repln_Rec.PRICE_LIST_HEADER_ID := p_PRICE_LIST_HEADER_ID;
405 x_Repln_Rec.INVENTORY_ORG_ID := p_INVENTORY_ORG_ID;
406 -- swai: bug 4666344 added problem description
407 x_Repln_Rec.PROBLEM_DESCRIPTION := p_PROBLEM_DESCRIPTION;
408 x_Repln_Rec.RO_PRIORITY_CODE := p_RO_PRIORITY_CODE; -- swai: R12
409 x_Repln_Rec.RESOLVE_BY_DATE := p_RESOLVE_BY_DATE; -- rfieldma: 5355051
410 x_Repln_Rec.BULLETIN_CHECK_DATE := p_BULLETIN_CHECK_DATE;
411 x_Repln_Rec.ESCALATION_CODE := p_ESCALATION_CODE;
412 -- additional DFF attributes, subhat(bug#7497907)
413 x_Repln_Rec.ATTRIBUTE16 := p_ATTRIBUTE16;
414 x_Repln_Rec.ATTRIBUTE17 := p_ATTRIBUTE17;
415 x_Repln_Rec.ATTRIBUTE18 := p_ATTRIBUTE18;
416 x_Repln_Rec.ATTRIBUTE19 := p_ATTRIBUTE19;
417 x_Repln_Rec.ATTRIBUTE20 := p_ATTRIBUTE20;
418 x_Repln_Rec.ATTRIBUTE21 := p_ATTRIBUTE21;
419 x_Repln_Rec.ATTRIBUTE22 := p_ATTRIBUTE22;
420 x_Repln_Rec.ATTRIBUTE23 := p_ATTRIBUTE23;
421 x_Repln_Rec.ATTRIBUTE24 := p_ATTRIBUTE24;
422 x_Repln_Rec.ATTRIBUTE25 := p_ATTRIBUTE25;
423 x_Repln_Rec.ATTRIBUTE26 := p_ATTRIBUTE26;
424 x_Repln_Rec.ATTRIBUTE27 := p_ATTRIBUTE27;
425 x_Repln_Rec.ATTRIBUTE28 := p_ATTRIBUTE28;
426 x_Repln_Rec.ATTRIBUTE29 := p_ATTRIBUTE29;
427 x_Repln_Rec.ATTRIBUTE30 := p_ATTRIBUTE30;
428
429 END Convert_to_Repln_Rec_Type;
430
431 PROCEDURE Convert_to_DEF_Rec_Type
432 (
433 p_attribute_category IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
434 p_attribute1 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
435 p_attribute2 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
436 p_attribute3 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
437 p_attribute4 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
438 p_attribute5 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
439 p_attribute6 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
440 p_attribute7 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
441 p_attribute8 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
442 p_attribute9 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
443 p_attribute10 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
444 p_attribute11 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
445 p_attribute12 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
446 p_attribute13 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
447 p_attribute14 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
448 p_attribute15 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
449 p_attribute16 IN VARCHAR2 := FND_API.G_MISS_CHAR, -- subhat, dff changes(bug#7497907)
450 p_attribute17 IN VARCHAR2 := FND_API.G_MISS_CHAR,
451 p_attribute18 IN VARCHAR2 := FND_API.G_MISS_CHAR,
452 p_attribute19 IN VARCHAR2 := FND_API.G_MISS_CHAR,
453 p_attribute20 IN VARCHAR2 := FND_API.G_MISS_CHAR,
454 p_attribute21 IN VARCHAR2 := FND_API.G_MISS_CHAR,
455 p_attribute22 IN VARCHAR2 := FND_API.G_MISS_CHAR,
456 p_attribute23 IN VARCHAR2 := FND_API.G_MISS_CHAR,
457 p_attribute24 IN VARCHAR2 := FND_API.G_MISS_CHAR,
458 p_attribute25 IN VARCHAR2 := FND_API.G_MISS_CHAR,
459 p_attribute26 IN VARCHAR2 := FND_API.G_MISS_CHAR,
460 p_attribute27 IN VARCHAR2 := FND_API.G_MISS_CHAR,
461 p_attribute28 IN VARCHAR2 := FND_API.G_MISS_CHAR,
462 p_attribute29 IN VARCHAR2 := FND_API.G_MISS_CHAR,
463 p_attribute30 IN VARCHAR2 := FND_API.G_MISS_CHAR,
464 x_DEF_Rec OUT NOCOPY Csd_Repairs_Util.DEF_Rec_Type
465 ) IS
466 --
467 BEGIN
468 --
469 x_DEF_Rec.attribute_category := p_attribute_category;
470 x_DEF_Rec.attribute1 := p_attribute1;
471 x_DEF_Rec.attribute2 := p_attribute2;
472 x_DEF_Rec.attribute3 := p_attribute3;
473 x_DEF_Rec.attribute4 := p_attribute4;
474 x_DEF_Rec.attribute5 := p_attribute5;
475 x_DEF_Rec.attribute6 := p_attribute6;
476 x_DEF_Rec.attribute7 := p_attribute7;
477 x_DEF_Rec.attribute8 := p_attribute8;
478 x_DEF_Rec.attribute9 := p_attribute9;
479 x_DEF_Rec.attribute10 := p_attribute10;
480 x_DEF_Rec.attribute11 := p_attribute11;
481 x_DEF_Rec.attribute12 := p_attribute12;
482 x_DEF_Rec.attribute13 := p_attribute13;
483 x_DEF_Rec.attribute14 := p_attribute14;
484 x_DEF_Rec.attribute15 := p_attribute15;
485 x_DEF_Rec.attribute16 := p_attribute16; -- subhat, DFF changes(bug#7497907).
486 x_DEF_Rec.attribute17 := p_attribute17;
487 x_DEF_Rec.attribute18 := p_attribute18;
488 x_DEF_Rec.attribute19 := p_attribute19;
489 x_DEF_Rec.attribute20 := p_attribute20;
490 x_DEF_Rec.attribute21 := p_attribute21;
491 x_DEF_Rec.attribute22 := p_attribute22;
492 x_DEF_Rec.attribute23 := p_attribute23;
493 x_DEF_Rec.attribute24 := p_attribute24;
494 x_DEF_Rec.attribute25 := p_attribute25;
495 x_DEF_Rec.attribute26 := p_attribute26;
496 x_DEF_Rec.attribute27 := p_attribute27;
497 x_DEF_Rec.attribute28 := p_attribute28;
498 x_DEF_Rec.attribute29 := p_attribute29;
499 x_DEF_Rec.attribute30 := p_attribute30;
500 --
501 END Convert_to_DEF_Rec_Type;
502
503 PROCEDURE GET_ENTITLEMENTS
504 (
505 P_API_VERSION_NUMBER IN NUMBER,
506 P_INIT_MSG_LIST IN VARCHAR2 := 'F',
507 P_COMMIT IN VARCHAR2 := 'F',
508 P_CONTRACT_NUMBER IN VARCHAR2 := NULL,
509 P_SERVICE_LINE_ID IN NUMBER := NULL,
510 P_CUSTOMER_ID IN NUMBER := NULL,
511 P_SITE_ID IN NUMBER := NULL,
512 P_CUSTOMER_ACCOUNT_ID IN NUMBER := NULL,
513 P_SYSTEM_ID IN NUMBER := NULL,
514 P_INVENTORY_ITEM_ID IN NUMBER := NULL,
515 P_CUSTOMER_PRODUCT_ID IN NUMBER := NULL,
516 P_REQUEST_DATE IN DATE := NULL,
517 P_VALIDATE_FLAG IN VARCHAR2 := 'Y',
518 --Begin forwardporting bug fix for 2806199,2806661,2802141 By Vijay
519 P_BUSINESS_PROCESS_ID IN NUMBER DEFAULT NULL,
520 P_SEVERITY_ID IN NUMBER DEFAULT NULL,
521 P_TIME_ZONE_ID IN NUMBER DEFAULT NULL,
522 P_CALC_RESPTIME_FLAG IN VARCHAR2 DEFAULT NULL,
523 --End forwardporting bug fix for 2806199,2806661,2802141 By Vijay
524 X_ENT_CONTRACTS OUT NOCOPY Oks_Entitlements_Pub.GET_CONTOP_TBL,
525 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
526 X_MSG_COUNT OUT NOCOPY NUMBER,
527 X_MSG_DATA OUT NOCOPY VARCHAR2
528 )
529 IS
530 --
531 l_api_name VARCHAR2(30) := 'GET_ENTITLEMENTS';
532 l_input_param_rec Oks_Entitlements_Pub.get_contin_rec;
533 l_api_version_number CONSTANT NUMBER := 1.0;
534 --
535 BEGIN
536 --
537 SAVEPOINT Get_Entitlements_Pvt;
538 -- Standard call to check for call compatibility.
539 IF NOT Fnd_Api.Compatible_API_Call
540 (l_api_version_number,
541 p_api_version_number,
542 l_api_name,
543 G_PKG_NAME)
544 THEN
545 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
546 END IF;
547 -- Initialize message list if p_init_msg_list is set to TRUE.
548 IF Fnd_Api.to_Boolean(p_init_msg_list)
549 THEN
550 Fnd_Msg_Pub.initialize;
551 END IF;
552 -- Initialize API return status to success
553 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
554 --
555 -- API body
556 --
557 l_input_param_rec.contract_number := p_contract_number;
558 l_input_param_rec.service_line_id := p_service_line_id;
559 l_input_param_rec.party_id := p_customer_id;
560 l_input_param_rec.site_id := p_site_id;
561 l_input_param_rec.cust_acct_id := p_customer_account_id;
562 l_input_param_rec.system_id := p_system_id;
563 l_input_param_rec.item_id := p_inventory_item_id;
564 l_input_param_rec.product_id := p_customer_product_id;
565 l_input_param_rec.request_date := p_request_date;
566 l_input_param_rec.validate_flag := p_validate_flag;
567 --Begin forwardporting bug fix for 2806199,2806661,2802141 By Vijay
568 l_input_param_rec.calc_resptime_flag := NVL(p_calc_resptime_flag,'N');
569 l_input_param_rec.business_process_id := p_business_process_id;
570 l_input_param_rec.severity_id := p_severity_id;
571 l_input_param_rec.time_zone_id := p_time_zone_id;
572 --End forwardporting bug fix for 2806199,2806661,2802141 By Vijay
573 --
574 -- If the validate_flag is 'Y' then only the valid contract lines as of
575 -- 'request_date' are returned. If the validate_flag is 'N' then
576 -- all the contract lines - valid and invalid- are returned.
577 --
578 Oks_Entitlements_Pub.GET_CONTRACTS
579 (p_api_version => p_api_version_number,
580 p_init_msg_list => p_init_msg_list,
581 p_inp_rec => l_input_param_rec,
582 x_return_status => x_return_status,
583 x_msg_count => x_msg_count,
584 x_msg_data => x_msg_data,
585 x_ent_contracts => x_ent_contracts);
586 --
587 IF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
588 RAISE Fnd_Api.G_EXC_ERROR ;
589 ELSIF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
590 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
591 END IF ;
592 --
593 -- End of API body.
594 --
595 -- Standard check for p_commit
596 IF Fnd_Api.to_Boolean(p_commit)
597 THEN
598 COMMIT WORK;
599 END IF;
600 -- Standard call to get message count and if count is 1, get message info.
601 Fnd_Msg_Pub.Count_And_Get
602 (p_count => x_msg_count,
603 p_data => x_msg_data
604 );
605 --
606 EXCEPTION
607 WHEN Fnd_Api.G_EXC_ERROR THEN
608 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
609 (P_API_NAME => L_API_NAME
610 ,P_PKG_NAME => G_PKG_NAME
611 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
612 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
613 ,X_MSG_COUNT => X_MSG_COUNT
614 ,X_MSG_DATA => X_MSG_DATA
615 ,X_RETURN_STATUS => X_RETURN_STATUS);
616 -- RAISE;
617 --
618 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
619 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
620 (P_API_NAME => L_API_NAME
621 ,P_PKG_NAME => G_PKG_NAME
622 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
623 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
624 ,X_MSG_COUNT => X_MSG_COUNT
625 ,X_MSG_DATA => X_MSG_DATA
626 ,X_RETURN_STATUS => X_RETURN_STATUS);
627 -- RAISE;
628 --
629 WHEN OTHERS THEN
630 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
631 (P_API_NAME => L_API_NAME
632 ,P_PKG_NAME => G_PKG_NAME
633 ,P_EXCEPTION_LEVEL => Jtf_Plsql_Api.G_EXC_OTHERS
634 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
635 ,X_MSG_COUNT => X_MSG_COUNT
636 ,X_MSG_DATA => X_MSG_DATA
637 ,X_RETURN_STATUS => X_RETURN_STATUS);
638 -- RAISE;
639 --
640 END GET_ENTITLEMENTS;
641
642 PROCEDURE Get_KB_Element_Description
643 (
644 p_element_id IN NUMBER,
645 p_element_description OUT NOCOPY VARCHAR2
646 )
647 IS
648 --
649 l_element_desc CLOB;
650 l_amount BINARY_INTEGER := 32767;
651 l_position INTEGER := 1;
652 l_buffer VARCHAR2(32767);
653 l_chunksize INTEGER;
654 --
655 BEGIN
656 --
657 SELECT description
658 INTO l_element_desc
659 FROM cs_kb_elements_vl
660 WHERE element_id = p_element_id;
661 --
662 l_chunksize := DBMS_LOB.getchunksize(l_element_desc);
663 IF l_chunksize IS NOT NULL THEN
664 IF l_chunksize < l_amount THEN
665 l_amount := (l_amount/l_chunksize) * l_chunksize;
666 END IF;
667 END IF;
668 IF l_element_desc IS NOT NULL THEN
669 DBMS_LOB.READ
670 (lob_loc => l_element_desc,
671 amount => l_amount,
672 offset => l_position,
673 buffer => l_buffer
674 );
675 ELSE
676 l_buffer := NULL;
677 END IF;
678 --
679 p_element_description := l_buffer;
680 --
681 EXCEPTION
682 WHEN NO_DATA_FOUND THEN
683 p_element_description := NULL;
684 WHEN TOO_MANY_ROWS THEN
685 p_element_description := NULL;
686 WHEN OTHERS THEN
687 p_element_description := NULL;
688 --
689 END Get_KB_Element_Description;
690
691 -- R12 Development Begin
692 -- *******************************************************
693 -- API Name: check_task_n_wip
694 -- Type : Private
695 -- Pre-Req : None
696 -- Parameters:
697 -- IN
698 -- p_repair_line_id IN VARCHAR2,
699 -- p_repair_status IN VARCHAR2,
700 -- OUT
701 -- x_return_status
702 -- x_msg_count
703 -- x_msg_data
704 --
705 -- Version : Current version 1.0
706 -- Initial Version 1.0
707 --
708 -- Description : This API checks if there are any open tasks or wip jobs
709 -- for the repair order if the status is 'C'. If there are
710 -- open tasks or wipjobs depending on the mode, this api
711 -- returns FAILURE otherwise SUCCESS.
712 --
713 --
714 -- ***********************************************************
715 PROCEDURE Check_Task_N_Wipjob
716 (
717 p_repair_line_id IN NUMBER,
718 p_repair_status IN VARCHAR2,
719 x_return_status OUT NOCOPY VARCHAR2,
720 x_msg_count OUT NOCOPY NUMBER,
721 x_msg_data OUT NOCOPY VARCHAR2
722 ) IS
723
724 -- Local variables
725 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_TASK_N_WIPJOB';
726 l_debug_level NUMBER ;
727 l_repair_mode VARCHAR2(10);
728 l_tmp_char VARCHAR2(1);
729 C_TASK_MODE CONSTANT VARCHAR2(4) := 'TASK';
730 C_WIP_MODE CONSTANT VARCHAR2(3) := 'WIP';
731 C_CLOSED_STATUS CONSTANT VARCHAR2(1) := 'C';
732
733 --Cursor definitions
734
735 -- Cursor to check if there are open tasks for the
736 -- repair order.
737 CURSOR task_cur IS
738 SELECT 'x'
739 FROM jtf_tasks_b TASK, jtf_task_statuses_b status
740 WHERE TASK.source_object_id = p_repair_line_id
741 AND TASK.source_object_type_code = 'DR'
742 AND TASK.task_status_id = status.task_status_id
743 AND NVL(status.closed_flag,'N') <> 'Y'
744 AND ROWNUM =1;
745
746 -- Cursor to check open wip jobs for the repair order
747 CURSOR wipjob_cur IS
748 SELECT 'x' /* crx.repair_job_xref_id */
749 FROM csd_repair_job_xref crx, wip_discrete_jobs wdj
750 WHERE crx.repair_line_id = p_repair_line_id
751 AND crx.wip_entity_id = wdj.wip_entity_id
752 AND wdj.status_type NOT IN (4,5,7,12)
753 /* 4: Complete,5: Complete-No charges,7: Cancelled,12: Closed */
754 UNION ALL
755 SELECT 'x' /* repair_job_xref_id*/
756 FROM csd_repair_job_xref
757 WHERE repair_line_id = p_repair_line_id
758 AND wip_entity_id IS NULL;
759
760 -- Cursor to get the repair mode
761 CURSOR repair_details_cur IS
762 SELECT repair_mode FROM csd_repairs
763 WHERE repair_line_id = p_repair_line_id;
764
765
766
767 BEGIN
768
769 -- Initialize local variables.
770 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
771 l_debug_level := Fnd_Log.G_CURRENT_RUNTIME_LEVEL;
772
773 IF (Fnd_Log.LEVEL_PROCEDURE >= l_debug_level) THEN
774 Fnd_Log.STRING (Fnd_Log.LEVEL_PROCEDURE, 'csd.plsql.csd_repairs_util.check_task_n_wipjob.begin',
775 '-------------Entered check_task_n_wipjob----------------');
776 END IF;
777
778
779 -- Get the repair mode
780 OPEN repair_details_cur ;
781 FETCH repair_details_cur INTO l_repair_mode;
782 IF(repair_details_cur%NOTFOUND) THEN
783 CLOSE repair_details_Cur;
784 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
785 END IF;
786 CLOSE repair_details_cur;
787
788 /****
789 If the repair status is going to be closed, then if the mode is wip check for open wip jobs,
790 if the mode is task, check for open tasks. Return failure if there are open items, return
791 success if not.
792 ****/
793 IF( p_repair_status = C_CLOSED_STATUS) THEN
794
795 IF (Fnd_Log.LEVEL_EVENT >= l_debug_level) THEN
796 Fnd_Log.STRING (Fnd_Log.LEVEL_EVENT, 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
797 '-------------status is being changed to close,ro['||p_repair_line_id||']-----');
798 END IF;
799
800 IF (l_repair_mode = C_WIP_MODE) THEN
801
802 IF (Fnd_Log.LEVEL_STATEMENT >= l_debug_level) THEN
803 Fnd_Log.STRING (Fnd_Log.LEVEL_STATEMENT, 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
804 '-------------checking for open jobs---------------');
805 END IF;
806
807 OPEN wipjob_cur;
808 FETCH wipjob_cur INTO l_tmp_char;
809 IF(wipjob_cur%NOTFOUND) THEN
810 CLOSE wipjob_cur;
811 IF (Fnd_Log.LEVEL_STATEMENT >= l_debug_level) THEN
812 Fnd_Log.STRING (Fnd_Log.LEVEL_STATEMENT, 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
813 '-------------there are open jobs---------------');
814 END IF;
815 Fnd_Message.set_name('CSD', 'CSD_API_OPEN_WIP_JOBS');
816 Fnd_Msg_Pub.ADD;
817 RAISE Fnd_Api.G_EXC_ERROR ;
818 END IF;
819 CLOSE wipjob_cur;
820 ELSIF (l_repair_mode = C_TASK_MODE) THEN
821
822 IF (Fnd_Log.LEVEL_STATEMENT >= l_debug_level) THEN
823 Fnd_Log.STRING (Fnd_Log.LEVEL_STATEMENT, 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
824 '-------------checking for open tasks---------------');
825 END IF;
826
827 OPEN task_cur;
828 FETCH task_cur INTO l_tmp_char;
829 IF(task_cur%NOTFOUND) THEN
830 CLOSE task_cur;
831 IF (Fnd_Log.LEVEL_STATEMENT >= l_debug_level) THEN
832 Fnd_Log.STRING (Fnd_Log.LEVEL_STATEMENT, 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
833 '-------------there are open tasks---------------');
834 END IF;
835 Fnd_Message.set_name('CSD', 'CSD_API_OPEN_TASKS');
836 Fnd_Msg_Pub.ADD;
837 RAISE Fnd_Api.G_EXC_ERROR ;
838 END IF;
839 CLOSE task_cur;
840 END IF;
841 END IF;
842
843 IF (Fnd_Log.level_procedure >= l_debug_level) THEN
844 Fnd_Log.STRING (Fnd_Log.level_procedure, 'csd.plsql.csd_repairs_util.check_task_n_wipjob.end',
845 '-------------Leaving check_task_n_wipjob----------------');
846 END IF;
847
848 EXCEPTION
849 WHEN Fnd_Api.G_EXC_ERROR THEN
850 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
851 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
852 IF ( Fnd_Log.LEVEL_ERROR >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
853 Fnd_Log.STRING(Fnd_Log.LEVEL_ERROR,
854 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
855 'EXC_ERROR ['||x_msg_data||']');
856 END IF;
857
858 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
859 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
860 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data );
861 IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
862 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
863 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
864 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
865 END IF;
866
867 WHEN OTHERS THEN
868 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
869 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
870 THEN
871 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME , l_api_name );
872 END IF;
873 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data );
874
875 IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
876 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
877 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
878 'SQL Message ['||SQLERRM||']');
879 END IF;
880
881
882 END Check_Task_N_Wipjob;
883
884
885 -- *******************************************************
886 -- API Name: convert_Status_val_to_Id
887 -- Type : Private
888 -- Pre-Req : None
889 -- Parameters:
890 -- IN
891 -- p_status_rec IN CSD_REPAIRS_PUB.REPAIR_STATUS_REC_TYPE ,
892 -- OUT
893 -- x_status_rec OUT CSD_REPAIRS_PUB.REPAIR_STATUS_REC_TYPE ,
894 -- x_return_status
895 --
896 -- Version : Current version 1.0
897 -- Initial Version 1.0
898 --
899 -- Description : Converts value to Ids in the input repair status record.
900 --
901 -- ***********************************************************
902
903 PROCEDURE Convert_status_Val_to_Id(p_repair_status_rec IN Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE,
904 x_repair_status_rec OUT NOCOPY Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE,
905 x_return_status OUT NOCOPY VARCHAR2)
906 IS
907 l_debug_level NUMBER;
908
909 --Value to id conversion cursor
910 CURSOR repair_id_conv_cur(p_repair_number VARCHAR2) IS
911 SELECT REPAIR_LINE_ID
912 FROM CSD_REPAIRS
913 WHERE repair_number = p_repair_number;
914
915 -- Get status_id from status_Code
916 CURSOR flow_stat_cur(p_repair_status VARCHAR2) IS
917 SELECT FLOW_STATUS_ID
918 FROM CSD_FLOW_STATUSES_B
919 WHERE FLOW_STATUS_CODE = p_repair_status;
920
921
922 BEGIN
923
924
925 -- Initialize local variables.
926 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
927 l_debug_level := Fnd_Log.G_CURRENT_RUNTIME_LEVEL;
928
929 IF (Fnd_Log.LEVEL_PROCEDURE >= l_debug_level) THEN
930 Fnd_Log.STRING (Fnd_Log.LEVEL_PROCEDURE, 'csd.plsql.csd_repairs_util.Convert_Status_val_to_Id.begin',
931 '-------------Entered Convert_Status_val_to_Id----------------');
932 END IF;
933
934
935
936 x_repair_status_rec := p_repair_status_rec;
937
938 IF (p_repair_status_rec.repair_line_id IS NULL) THEN
939 -- ID based attribute is NULL or MISSING
940 IF (p_repair_status_rec.repair_number IS NULL) THEN
941 -- value based parameter is also NULL or MISSING
942 Fnd_Message.SET_NAME ('CSD', 'CSD_API_INV_REP_NUM');
943 Fnd_Msg_Pub.ADD;
944 x_return_status := Fnd_Api.G_RET_STS_ERROR;
945 ELSE
946 OPEN repair_id_conv_cur(p_repair_status_rec.repair_number);
947 FETCH repair_id_conv_cur INTO x_repair_status_rec.repair_line_id;
948 IF repair_id_conv_cur%NOTFOUND THEN
949 -- Id fetch was not successful
950 -- Conversion failed.
951 CLOSE repair_id_conv_cur;
952 Fnd_Message.SET_NAME ('CSD', 'CSD_API_INV_REP_NUM');
953 Fnd_Msg_Pub.ADD;
954 x_return_status := Fnd_Api.G_RET_STS_ERROR;
955 RAISE Fnd_Api.G_EXC_ERROR;
956 END IF;
957 CLOSE repair_id_conv_cur;
958 END IF;
959 ELSIF p_repair_status_rec.repair_line_id IS NOT NULL THEN
960 -- ID based attribute is present
961 x_repair_status_rec.repair_line_id:= p_repair_status_rec.repair_line_id ;
962 -- If the value based parameter is also passed, generate an
963 -- informational message.
964 IF (p_repair_status_rec.repair_number IS NOT NULL) THEN
965 Fnd_Message.SET_NAME('CSD', 'CSD_API_INPUT_IGNORE');
966 Fnd_Msg_Pub.ADD;
967 END IF;
968 END IF;
969
970
971 IF (p_repair_status_rec.repair_status_id IS NULL) THEN
972 -- ID based attribute is NULL or MISSING
973 IF (p_repair_status_rec.repair_status IS NULL) THEN
974 -- value based parameter is also NULL or MISSING
975 Fnd_Message.SET_NAME('CSD','CSD_INVALID_FLOW_STATUS');
976 Fnd_Msg_Pub.ADD;
977 x_return_status := Fnd_Api.G_RET_STS_ERROR;
978 ELSE
979 OPEN flow_stat_cur(p_repair_status_Rec.repair_status);
980 FETCH flow_stat_cur INTO x_repair_status_Rec.repair_status_id;
981 IF(flow_stat_cur%NOTFOUND) THEN
982 CLOSE flow_stat_cur;
983 Fnd_Message.SET_NAME('CSD','CSD_INVALID_FLOW_STATUS');
984 Fnd_Msg_Pub.ADD;
985 x_return_status := Fnd_Api.G_RET_STS_ERROR;
986 RAISE Fnd_Api.G_EXC_ERROR;
987 END IF;
988 CLOSE flow_stat_cur;
989 END IF;
990 ELSIF p_repair_status_rec.repair_status_id IS NOT NULL THEN
991 -- ID based attribute is present
992 x_repair_status_rec.repair_status_id:= p_repair_status_rec.repair_line_id ;
993 -- If the value based parameter is also passed, generate an
994 -- informational message.
995 IF (p_repair_status_rec.repair_status IS NOT NULL) THEN
996 Fnd_Message.SET_NAME('CSD', 'CSD_API_INPUT_IGNORE');
997 Fnd_Msg_Pub.ADD;
998 END IF;
999 END IF;
1000
1001
1002
1003 IF (Fnd_Log.level_procedure >= l_debug_level) THEN
1004 Fnd_Log.STRING (Fnd_Log.level_procedure, 'csd.plsql.csd_repairs_util.Convert_Status_Val_to_Id.end',
1005 '-------------Leaving Convert_Status_Val_to_Id----------------');
1006 END IF;
1007
1008 END Convert_status_Val_to_Id;
1009
1010 -- ***********************************************************
1011 -- API Name: Check_WebSrvc_Security
1012 -- Type : Private
1013 -- Pre-Req : None
1014 -- Parameters:
1015 -- IN
1016 -- p_repair_line_id IN VARCHAR2,
1017 -- OUT
1018 -- x_return_status
1019 --
1020 -- Version : Current version 1.0
1021 -- Initial Version 1.0
1022 --
1023 -- Description : This API checks the security for the web service
1024 -- call. The security is setup as the permission
1025 -- to the current logged in user. If the logged in
1026 -- user has access to 1) account on SR
1027 -- returns true otherwise it returns false.
1028 -- The two other permisions for 3rd party scenario's
1029 -- are not developed for now. We need to design
1030 -- that in conjunction with logistics enhancements
1031 -- for the 3rd scenario.
1032 -- 2) bill to party on SR
1033 -- 3) ship to party on SR
1034 --
1035 ---- ***********************************************************
1036 PROCEDURE Check_WebSrvc_Security
1037 (
1038 p_repair_line_id IN NUMBER,
1039 x_return_status OUT NOCOPY VARCHAR2
1040 ) IS
1041 l_return_Status VARCHAR2(1);
1042 l_predicate VARCHAR2(4000);
1043 l_table_alias VARCHAR2(80);
1044 l_Sql_str VARCHAR(8000);
1045 l_tmp_str VARCHAR2(30);
1046 C_CUST_PROD_ACTION_CODE CONSTANT VARCHAR2(30) := 'CUST_PROD';
1047 C_3RDPARTY_RMA_ACTION_TYPE CONSTANT VARCHAR2(30) := '3RDPARTY_RMA';
1048 C_3RDPARTY_SHIP_ACTION_TYPE CONSTANT VARCHAR2(30) := '3RDPARTY_SHIP';
1049
1050
1051 BEGIN
1052 /******************
1053 Call FND_DATA_SECURITY.GET_PREDICATE to get the SQL predicate for checking the permission for the current logged in user for the account ch''x'' eck. Parameters,
1054 p_object_name => Repair Order,
1055 p_grant_instance_type => 'SET',
1056 p_statement_type => 'EXISTS'
1057
1058 *****************/
1059
1060 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1061 /*!!!!!!!!!! Temporarily disabling the security
1062 return ;!!!!!*******/
1063
1064 l_table_alias := 'ro';
1065 debug('Calling getSecurity_predicate');
1066 Fnd_Data_Security.GET_SECURITY_PREDICATE(
1067 p_api_version => 1.0,
1068 p_function => NULL,
1069 p_object_name => 'CSD_RO_OBJ',
1070 p_grant_instance_type => 'SET',
1071 p_statement_type => 'EXISTS',
1072 x_predicate => l_predicate,
1073 x_return_status => l_return_status,
1074 p_table_alias => l_table_alias );
1075
1076
1077 debug('getSecurity_predicate return value['||l_Return_status||']');
1078 debug('success['||FND_API.G_RET_STS_SUCCESS||']');
1079 debug('getSecurity_predicate predicate value['||l_predicate||']');
1080
1081 IF(l_return_status <> FND_API.G_TRUE) THEN
1082 debug('returning l_return_status['||l_return_status||']');
1083 x_return_status := FND_API.G_RET_STS_ERROR;
1084 ELSE
1085 IF(l_predicate IS NOT NULL AND l_predicate <> '(1=1)') THEN
1086
1087 debug('forming sql str');
1088 l_sql_str := 'Select ''x'' from csd_repairs ro '
1089 || ' Where ro.repair_line_id = :1 and '
1090 || l_predicate ;
1091
1092 debug('l_sql before['||l_sql_str||']');
1093 BEGIN
1094 EXECUTE IMMEDIATE l_sql_str INTO l_tmp_Str
1095 USING p_repair_line_id ;
1096 EXCEPTION
1097 WHEN NO_DATA_FOUND THEN
1098 x_return_status := FND_API.G_RET_STS_ERROR;
1099 END;
1100 debug('l_tmp_Str after['||l_tmp_str||']');
1101 IF(l_tmp_str IS NULL ) THEN
1102 debug('returning failure because l_tmp_Str['||l_tmp_str||']');
1103 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1104 END IF;
1105
1106 ELSE
1107 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1108 END IF;
1109 END IF;
1110
1111 /************************
1112 Call FND_DATA_SECURITY.GET_PREDICATE to get the SQL predicate for checking
1113 the permission for the current logged in user for the bill to check. Parameters,
1114 p_object_name => 'Repair Logistics Line BillTo',
1115 p_grant_instance_type => 'SET',
1116 p_statement_type => 'EXISTS'
1117
1118 ************************/
1119 /********** Not done for R12 first release
1120 *********************************************
1121 Fnd_Data_Security.GET_SECURITY_PREDICATE(
1122 p_api_version => 1.0,
1123 p_function => NULL,
1124 p_object_name => 'Repair LogisticsLine BillTo',
1125 p_grant_instance_type => 'SET',
1126 p_user_name => Fnd_Global.USER_ID,
1127 p_statement_type => 'EXISTS',
1128 x_predicate => l_predicate,
1129 x_return_status => l_return_status,
1130 p_table_alias => l_table_alias );
1131
1132 IF(l_predicate IS NULL OR l_predicate <> '(1=1)') THEN
1133
1134 l_sql_str := ' Select ''x'' from csd_repairs_v ro, csd_product_txns_v prd, '
1135 + ' cs_estimate_details csd Where ro.repair_line_id = :1'
1136 + ' And prd.repair_line_id = ro.repair_line_id '
1137 + ' And prd.action_code = :2 '
1138 + ' And prd.action_type = :3 '
1139 + ' And csd.estimate_detail_id = prd.estimate_detail_id '
1140 + ' And ' + l_predicate
1141 ;
1142
1143 EXECUTE IMMEDIATE l_sql_str INTO l_tmp_Str
1144 USING TO_CHAR(p_repair_line_id),
1145 C_CUST_PROD_ACTION_CODE ,
1146 C_3RDPARTY_RMA_ACTION_TYPE ;
1147 IF(l_tmp_str IS NULL ) THEN
1148 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1149 END IF;
1150
1151 END IF;
1152 ************************************************
1153 ***********************************************/
1154
1155
1156 /************************
1157 Call FND_DATA_SECURITY.GET_PREDICATE to get the SQL predicate for checking the permission for the current logged in user for the ship to check. Parameters,
1158 p_object_name => 'Repair Logistics Line ShipTo',
1159 p_grant_instance_type => 'SET',
1160 p_statement_type => 'EXISTS'
1161 ************************/
1162 /************************************ Not done for
1163 R12 first release
1164 ***************************************
1165 Fnd_Data_Security.GET_SECURITY_PREDICATE(
1166 p_api_version => 1.0,
1167 p_function => NULL,
1168 p_object_name => 'Repair LogisticsLine ShipTo',
1169 p_grant_instance_type => 'SET',
1170 p_user_name => Fnd_Global.USER_ID,
1171 p_statement_type => 'EXISTS',
1172 x_predicate => l_predicate,
1173 x_return_status => l_return_status,
1174 p_table_alias => l_table_alias );
1175
1176 IF(l_predicate IS NULL OR l_predicate <> '(1=1)') THEN
1177
1178 l_sql_str := ' Select ''x'' from csd_repairs_v ro, csd_product_txns_v prd, '
1179 + ' cs_estimate_details csd Where ro.repair_line_id = :1'
1180 + ' And prd.repair_line_id = ro.repair_line_id '
1181 + ' And prd.action_code = :2 '
1182 + ' And prd.action_type = :3 '
1183 + ' And csd.estimate_detail_id = prd.estimate_detail_id '
1184 + ' And ' + l_predicate ;
1185
1186 EXECUTE IMMEDIATE l_sql_str INTO l_tmp_Str
1187 USING TO_CHAR(p_repair_line_id),
1188 C_CUST_PROD_ACTION_CODE ,
1189 C_3RDPARTY_SHIP_ACTION_TYPE ;
1190 IF(l_tmp_str IS NULL ) THEN
1191 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1192 END IF;
1193
1194 END IF;
1195 ************************************************
1196 ***********************************************/
1197
1198 debug('returning success');
1199 EXCEPTION
1200 WHEN OTHERS THEN
1201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1202 debug('sqlca.sqlcode ['||sqlcode ||']');
1203 debug('sqlca.sqlerrm['||sqlerrm||']');
1204
1205
1206 END check_Websrvc_security;
1207 -- R12 Development End
1208
1209
1210 /*-----------------------------------------------------------------*/
1211 /* procedure name: change_item_ib_owner */
1212 /* description : Procedure to Change the Install Base Owner for */
1213 /* a single item */
1214 /*-----------------------------------------------------------------*/
1215 PROCEDURE CHANGE_ITEM_IB_OWNER
1216 (
1217 p_create_tca_relation IN VARCHAR2 := NULL,
1218 p_instance_id IN NUMBER,
1219 p_new_owner_party_id IN NUMBER,
1220 p_new_owner_account_id IN NUMBER,
1221 p_current_owner_party_id IN NUMBER,
1222 x_return_status OUT NOCOPY VARCHAR2,
1223 x_msg_count OUT NOCOPY NUMBER,
1224 x_msg_data OUT NOCOPY VARCHAR2,
1225 x_tca_relation_id OUT NOCOPY NUMBER
1226 )
1227 IS
1228
1229 -- variables for update instance
1230 l_csiip_inst_party_id Number := NULL;
1231 l_csiip_obj_ver_num Number := NULL;
1232 l_instance_account_id Number := NULL;
1233 l_inst_acct_obj_ver_num Number := NULL;
1234 l_object_version_number Number := NULL;
1235 l_instance_rec csi_datastructures_pub.instance_rec;
1236 l_party_tbl csi_datastructures_pub.party_tbl;
1237 l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
1238 l_account_tbl csi_datastructures_pub.party_account_tbl;
1239 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
1240 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
1241 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
1242 l_txn_rec csi_datastructures_pub.transaction_rec;
1243 x_instance_id_lst csi_datastructures_pub.id_tbl;
1244
1245 -- variables for create TCA relationship
1246 l_current_owner_party_type Varchar2(150) := NULL;
1247 l_new_owner_party_type Varchar2(150) := NULL;
1248 l_relationship_rec hz_relationship_v2pub.relationship_rec_type;
1249 l_rel_party_id Number;
1250 l_rel_party_number Varchar2(150);
1251 l_tca_rel_in_params csd_ib_chown_cuhk.tca_rel_info_in_rec_type;
1252 l_tca_rel_out_params csd_ib_chown_cuhk.tca_rel_info_out_rec_type;
1253 l_tca_rel_count Number;
1254
1255 -- API variables
1256 l_api_name CONSTANT Varchar(30) := 'CHANGE_ITEM_IB_OWNER';
1257 l_api_version CONSTANT Number := 1.0;
1258
1259 -- Cursor to select the Instance party id
1260 Cursor c_instance_party(p_instance_id number) IS
1261 Select instance_party_id,
1262 object_version_number
1263 from csi_i_parties
1264 where instance_id = p_instance_id
1265 and relationship_type_code = 'OWNER'
1266 and sysdate between nvl(active_start_date, sysdate-1)
1267 and nvl(active_end_date, sysdate+1);
1268
1269 -- Cursor to derive the Instance details
1270 Cursor c_instance_details(p_instance_id number) IS
1271 Select object_version_number from csi_item_instances
1272 where instance_id = p_instance_id;
1273
1274 -- Cursor to derive the Instance Account Id
1275 Cursor c_instance_account(p_instance_party_id number) is
1276 Select ip_account_id,
1277 object_version_number
1278 from csi_ip_accounts
1279 where instance_party_id = p_instance_party_id;
1280
1281 -- Cursor to derive party information from hz_parties
1282 Cursor c_hz_parties_info(p_party_id number) is
1283 Select party_type
1284 from hz_parties
1285 where party_id = p_party_id;
1286
1287 -- cursor to get the number of party relationships for the given criteria
1288 Cursor c_tca_rel_count(p_subject_id number, p_subject_type varchar2,
1289 p_object_id number, p_object_type varchar2,
1290 p_relationship_code varchar2) is
1291 Select count(relationship_id)
1292 from hz_relationships
1293 where subject_id = p_subject_id
1294 and subject_type = p_subject_type
1295 and subject_table_name = 'HZ_PARTIES'
1296 and object_id = p_object_id
1297 and object_type = p_object_type
1298 and object_table_name = 'HZ_PARTIES'
1299 and relationship_code = p_relationship_code
1300 and sysdate between nvl(start_date, sysdate-1)
1301 and nvl(end_date, sysdate+1);
1302 BEGIN
1303
1304 savepoint CHANGE_ITEM_IB_OWNER;
1305
1306 if (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1307 fnd_log.STRING (fnd_log.level_procedure,
1308 'CSD.PLSQL.CSD_REPAIRS_UTIL.CHANGE_ITEM_IB_OWNER.BEGIN',
1309 'Enter - Change Item IB Owner');
1310 end if;
1311
1312 -- Initialize API return status to success
1313 x_return_status := FND_API.G_RET_STS_SUCCESS;
1314
1315 -- create a TCA relationship between the current owner and
1316 -- new owner, if desired
1317 if(p_create_tca_relation = fnd_api.g_true) then
1318
1319 -- get info for original owner
1320 Open c_hz_parties_info(p_current_owner_party_id);
1321 Fetch c_hz_parties_info
1322 into l_current_owner_party_type;
1323 Close c_hz_parties_info;
1324
1325 -- get info for new owner
1326 Open c_hz_parties_info(p_new_owner_party_id);
1327 Fetch c_hz_parties_info into l_new_owner_party_type;
1328 Close c_hz_parties_info;
1329
1330 -- get relationship type and code from custom hook procedure
1331 l_tca_rel_in_params.instance_id := p_instance_id;
1332 l_tca_rel_in_params.new_owner_party_id := p_new_owner_party_id;
1333 l_tca_rel_in_params.new_owner_account_id := p_new_owner_account_id;
1334 l_tca_rel_in_params.current_owner_party_id := p_current_owner_party_id;
1335 csd_ib_chown_cuhk.get_tca_rel_info (
1336 p_in_param => l_tca_rel_in_params,
1337 x_out_param => l_tca_rel_out_params
1338 );
1339 if NOT(l_tca_rel_out_params.return_status = FND_API.G_RET_STS_SUCCESS) then
1340 RAISE FND_API.G_EXC_ERROR;
1341 end if;
1342
1343 -- populate the relationship rec before calling API
1344 -- Assumption: the owners will always be from hz_parties
1345 l_relationship_rec.subject_id := p_new_owner_party_id;
1346 l_relationship_rec.subject_type := l_new_owner_party_type;
1347 l_relationship_rec.subject_table_name := 'HZ_PARTIES';
1348 l_relationship_rec.object_id := p_current_owner_party_id;
1349 l_relationship_rec.object_type := l_current_owner_party_type;
1350 l_relationship_rec.object_table_name := 'HZ_PARTIES';
1351 l_relationship_rec.relationship_code := l_tca_rel_out_params.relationship_code;
1352 l_relationship_rec.relationship_type := l_tca_rel_out_params.relationship_type;
1353 l_relationship_rec.start_date := SYSDATE;
1354 l_relationship_rec.created_by_module := 'CSDSR';
1355 l_relationship_rec.application_id := 516;
1356
1357 -- check if TCA relationship already exists
1358 Open c_tca_rel_count(l_relationship_rec.subject_id, l_relationship_rec.subject_type,
1359 l_relationship_rec.object_id, l_relationship_rec.object_type,
1360 l_relationship_rec.relationship_code);
1361 Fetch c_tca_rel_count
1362 into l_tca_rel_count;
1363 Close c_tca_rel_count;
1364
1365 -- only create the TCA relationship if one does not exist already
1366 if (l_tca_rel_count = 0 ) then
1367 -- create the TCA relation
1368 hz_relationship_v2pub.create_relationship(
1369 p_init_msg_list => fnd_api.g_false,
1370 p_relationship_rec => l_relationship_rec,
1371 x_relationship_id => x_tca_relation_id,
1372 x_party_id => l_rel_party_id,
1373 x_party_number => l_rel_party_number,
1374 x_return_status => x_return_status,
1375 x_msg_count => x_msg_count,
1376 x_msg_data => x_msg_data,
1377 p_create_org_contact => 'Y'
1378 );
1379 if NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1380 RAISE FND_API.G_EXC_ERROR;
1381 end if;
1382 end if; -- l_tca_rel_count = 0
1383 End If; -- p_create_tca_relation = true
1384
1385 -- Get the current instance info
1386 Open c_instance_party(p_instance_id);
1387 Fetch c_instance_party into l_csiip_inst_party_id,
1388 l_csiip_obj_ver_num;
1389 Close c_instance_party;
1390
1391 -- Get the current owner's account information
1392 Open c_instance_account(l_csiip_inst_party_id);
1393 Fetch c_instance_account into l_instance_account_id,
1394 l_inst_acct_obj_ver_num;
1395 Close c_instance_account;
1396
1397 -- Get additional information about the IB instancee
1398 Open c_instance_details(p_instance_id);
1399 Fetch c_instance_details into l_object_version_number;
1400 Close c_instance_details;
1401
1402
1403 -- Assign / Initialize values to the IB Rec type
1404 l_instance_rec.instance_id := p_instance_id;
1405 l_instance_rec.object_version_number := l_object_version_number;
1406
1407 l_party_tbl(1).instance_party_id := l_csiip_inst_party_id;
1408 l_party_tbl(1).instance_id := p_instance_id;
1409 l_party_tbl(1).party_source_table := 'HZ_PARTIES';
1410 l_party_tbl(1).party_id := p_new_owner_party_id;
1411 l_party_tbl(1).relationship_type_code := 'OWNER';
1412 l_party_tbl(1).contact_flag := 'N';
1413 l_party_tbl(1).object_version_number := l_csiip_obj_ver_num;
1414
1415 l_account_tbl(1).ip_account_id := l_instance_account_id;
1416 l_account_tbl(1).parent_tbl_index := 1;
1417 l_account_tbl(1).instance_party_id := l_csiip_inst_party_id;
1418 l_account_tbl(1).party_account_id := p_new_owner_account_id;
1419 l_account_tbl(1).relationship_type_code := 'OWNER';
1420 l_account_tbl(1).object_version_number := l_inst_acct_obj_ver_num;
1421
1422 l_txn_rec.transaction_date := sysdate;
1423 l_txn_rec.source_transaction_date := sysdate;
1424 l_txn_rec.transaction_type_id := 1;
1425
1426 -- Call the Update item instance API
1427 csi_item_instance_pub.update_item_instance
1428 (
1429 p_api_version => 1.0,
1430 p_commit => fnd_api.g_false,
1431 p_init_msg_list => fnd_api.g_true,
1432 p_validation_level => fnd_api.g_valid_level_full,
1433 p_instance_rec => l_instance_rec,
1434 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
1435 p_party_tbl => l_party_tbl,
1436 p_account_tbl => l_account_tbl,
1437 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
1438 p_org_assignments_tbl => l_org_assignments_tbl,
1439 p_asset_assignment_tbl => l_asset_assignment_tbl,
1440 p_txn_rec => l_txn_rec,
1441 x_instance_id_lst => x_instance_id_lst,
1442 x_return_status => x_return_status,
1443 x_msg_count => x_msg_count,
1444 x_msg_data => x_msg_data
1445 );
1446
1447 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1448 RAISE FND_API.G_EXC_ERROR;
1449 End If;
1450
1451 COMMIT WORK;
1452
1453 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1454 fnd_log.STRING (fnd_log.level_procedure,
1455 'CSD.PLSQL.CSD_REPAIRS_UTIL.CHANGE_ITEM_IB_OWNER.END',
1456 'Exit - Change Item IB Owner');
1457 End if;
1458
1459 EXCEPTION
1460 When FND_API.G_EXC_ERROR then
1461 Rollback To change_item_ib_owner;
1462 x_return_status := FND_API.G_RET_STS_ERROR ;
1463 FND_MSG_PUB.Count_And_Get
1464 (p_count => x_msg_count,
1465 p_data => x_msg_data );
1466
1467 When FND_API.G_EXC_UNEXPECTED_ERROR then
1468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1469 ROLLBACK TO change_item_ib_owner;
1470 FND_MSG_PUB.Count_And_Get
1471 ( p_count => x_msg_count,
1472 p_data => x_msg_data );
1473
1474 When OTHERS then
1475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1476 Rollback To change_item_ib_owner;
1477 If FND_MSG_PUB.Check_Msg_Level
1478 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
1479 FND_MSG_PUB.Add_Exc_Msg
1480 (G_PKG_NAME,
1481 l_api_name );
1482 End If;
1483 FND_MSG_PUB.Count_And_Get
1484 (p_count => x_msg_count,
1485 p_data => x_msg_data );
1486
1487 END CHANGE_ITEM_IB_OWNER;
1488
1489 --bug#5874431
1490 Procedure create_csd_index (p_sql_stmt IN varchar2,
1491 p_object IN varchar2
1492 ) is
1493
1494 lv_dummy1 VARCHAR2(2000);
1495 lv_dummy2 VARCHAR2(2000);
1496 lv_retval BOOLEAN;
1497 v_applsys_schema VARCHAR2(200);
1498 lv_prod_short_name VARCHAR2(30);
1499
1500 begin
1501 lv_retval := FND_INSTALLATION.GET_APP_INFO(
1502 'FND', lv_dummy1,lv_dummy2, v_applsys_schema);
1503
1504 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(512);
1505 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1506 application_short_name => lv_prod_short_name,
1507 statement_type => AD_DDL.CREATE_INDEX,
1508 statement => p_sql_stmt,
1509 object_name => p_object
1510 );
1511
1512 EXCEPTION
1513 WHEN OTHERS THEN
1514 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
1515 RAISE;
1516 end create_csd_index;
1517
1518 -- *******************************************************
1519 -- API Name: get_contract_resolve_by_date
1520 -- Type : Private
1521 -- Pre-Req : None
1522 -- Parameters:
1523 -- IN required?
1524 -- p_contract_line_id IN NUMBER, Y
1525 -- p_bus_proc_id IN NUMBER, Y
1526 -- p_severity_id IN NUMBER, Y
1527 -- p_request_date IN NUMBER, N - if not passed, use sysdate
1528 -- OUT
1529 -- x_return_status
1530 -- x_msg_count
1531 -- x_msg_data
1532 -- x_resolve_by_date OUT DATE
1533 --
1534 -- Version : Current version 1.0
1535 -- Initial Version 1.0
1536 --
1537 -- Description : rfieldma: 5355051
1538 -- calls oks_entitlements_pub.get_react_resolve_by
1539 -- return resolve_by_date
1540 --
1541 --
1542 --
1543 --
1544 -- ***********************************************************
1545 PROCEDURE get_contract_resolve_by_date
1546 (
1547 p_contract_line_id IN NUMBER,
1548 p_bus_proc_id IN NUMBER,
1549 p_severity_id IN NUMBER,
1550 p_request_date IN DATE := sysdate,
1551 x_return_status OUT NOCOPY VARCHAR2,
1552 x_msg_count OUT NOCOPY NUMBER,
1553 x_msg_data OUT NOCOPY VARCHAR2,
1554 x_resolve_by_date OUT NOCOPY DATE
1555 ) IS
1556 -- define constants
1557 lc_api_name CONSTANT VARCHAR2(50) := 'csd_repairs_util.get_contract_resolve_by_date';
1558
1559 -- define variables
1560 l_server_timezone_id NUMBER;
1561
1562 subtype r_input_rec is oks_entitlements_pub.grt_inp_rec_type;
1563 l_inp_rec r_input_rec;
1564
1565 subtype r_react_rec is oks_entitlements_pub.rcn_rsn_rec_type;
1566 l_react_rec r_react_rec;
1567
1568 subtype r_resolve_rec is oks_entitlements_pub.rcn_rsn_rec_type;
1569 l_resolve_rec r_resolve_rec;
1570
1571
1572 BEGIN
1573 SAVEPOINT get_contract_resolve_by_date;
1574 x_resolve_by_date := null; -- default this field to null before logic begins
1575
1576 -- call PROCEDURE Check_Reqd_Param (p_param_value IN NUMBER,
1577 -- p_param_name IN VARCHAR2,
1578 -- p_api_name IN VARCHAR2
1579 -- ) to check p_contract_line_id, p_bus_proc_id, p_severity
1580
1581 Check_Reqd_Param(p_contract_line_id,
1582 'p_contract_line_id',
1583 lc_api_name);
1584
1585 Check_Reqd_Param(p_bus_proc_id,
1586 'p_bus_proc_id',
1587 lc_api_name);
1588
1589 Check_Reqd_Param(p_severity_id,
1590 'p_severity_id',
1591 lc_api_name);
1592
1593 -- profile option for server_timezone_id must be set
1594 l_server_timezone_id := fnd_profile.value('SERVER_TIMEZONE_ID');
1595 IF (NVL(l_server_timezone_id,Fnd_Api.G_MISS_NUM) = Fnd_Api.G_MISS_NUM) THEN
1596 Fnd_Message.SET_NAME('CSD','CSD_CANNOT_GET_PROFILE_VALUE');
1597 Fnd_Message.SET_TOKEN('PROFILE',get_user_profile_option_name('SERVER_TIMEZONE_ID'));
1598 Fnd_Msg_Pub.ADD;
1599 RAISE Fnd_Api.G_EXC_ERROR;
1600 END IF;
1601
1602 l_inp_rec.contract_line_id := p_contract_line_id ;
1603 l_inp_rec.business_process_id := p_bus_proc_id ;
1604 l_inp_rec.severity_id := p_severity_id ;
1605 l_inp_rec.request_date := p_request_date ;
1606 l_inp_rec.time_zone_id := l_server_timezone_id ;
1607 l_inp_rec.category_rcn_rsn := OKS_ENTITLEMENTS_PUB.G_RESOLUTION;
1608 l_inp_rec.compute_option := OKS_ENTITLEMENTS_PUB.G_BEST ;
1609 l_inp_rec.dates_in_input_tz := 'N' ;
1610
1611 oks_entitlements_pub.get_react_resolve_by_time(
1612 p_api_version => 1.0,
1613 p_init_msg_list => FND_API.G_TRUE,
1614 p_inp_rec => l_inp_rec,
1615 x_return_status => x_return_status,
1616 x_msg_count => x_msg_count,
1617 x_msg_data => x_msg_data,
1618 x_react_rec => l_react_rec,
1619 x_resolve_rec => l_resolve_rec);
1620
1621 IF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
1622 RAISE Fnd_Api.G_EXC_ERROR ;
1623 ELSIF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
1624 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
1625 END IF;
1626
1627 x_resolve_by_date := l_resolve_rec.by_date_end;
1628
1629 EXCEPTION
1630 When FND_API.G_EXC_ERROR then
1631 Rollback To get_contract_resolve_by_date;
1632 x_return_status := FND_API.G_RET_STS_ERROR ;
1633 FND_MSG_PUB.Count_And_Get
1634 (p_count => x_msg_count,
1635 p_data => x_msg_data );
1636
1637 When FND_API.G_EXC_UNEXPECTED_ERROR then
1638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1639 ROLLBACK TO get_contract_resolve_by_date;
1640 FND_MSG_PUB.Count_And_Get
1641 ( p_count => x_msg_count,
1642 p_data => x_msg_data );
1643
1644 When OTHERS then
1645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1646 Rollback To get_contract_resolve_by_date;
1647 If FND_MSG_PUB.Check_Msg_Level
1648 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
1649 FND_MSG_PUB.Add_Exc_Msg
1650 (G_PKG_NAME,
1651 lc_api_name );
1652 End If;
1653 FND_MSG_PUB.Count_And_Get
1654 (p_count => x_msg_count,
1655 p_data => x_msg_data );
1656
1657 END get_contract_resolve_by_date;
1658
1659 -- *******************************************************
1660 -- API Name: get_user_profile_option_name
1661 -- Type : Private
1662 -- Pre-Req : None
1663 -- Parameters:
1664 -- IN required?
1665 -- p_profile_name IN VARHAR2 Y
1666 --
1667 -- Version : Current version 1.0
1668 -- Initial Version 1.0
1669 --
1670 -- Description : rfieldma: utility function
1671 -- returns language specific user profile
1672 -- option name
1673 --
1674 --
1675 --
1676 --
1677 -- ***********************************************************
1678 FUNCTION get_user_profile_option_name
1679 (
1680 p_profile_name IN VARCHAR2
1681 ) RETURN VARCHAR2
1682 IS
1683 -- define variables
1684 l_user_prof_name VARCHAR2(240);
1685
1686 -- define cursors
1687 CURSOR c_user_prof_name(p_profile_name VARCHAR2) IS
1688 SELECT user_profile_option_name
1689 FROM fnd_profile_options_tl
1690 WHERE profile_option_name = p_profile_name
1691 AND language = userenv('lang');
1692
1693 BEGIN
1694 OPEN c_user_prof_name(p_profile_name);
1695 FETCH c_user_prof_name
1696 INTO l_user_prof_name;
1697 CLOSE c_user_prof_name;
1698
1699 RETURN l_user_prof_name;
1700
1701 END get_user_profile_option_name;
1702
1703 -- bug#7497790, 12.1 FP,subhat.
1704 -- ***************************************************
1705 -- Automatically update the RO status when the item is received.
1706 -- The API receives the Repair line id and updates the RO status if the conditions are met.
1707 -- Parameters:
1708 -- p_event : Specify the event that is calling this program. Based on the event, the program logic might change.
1709 -- p_reason_code: The reason code for the status change defaulted to null
1710 -- p_comments: The comments for the flow status, defaulted to null
1711 -- p_validation_level: validation level for the routine. Pass fnd_api.g_valid_level_full to get the messages from the API
1712 -- pass fnd_api.g_valid_level_none will ignore all error messages and return success always. The error messages
1713 -- will be logged in the fnd_log_messages if logging is enabled
1714 --*****************************************************
1715
1716 procedure auto_update_ro_status(
1717 p_api_version in number,
1718 p_commit in varchar2,
1719 p_init_msg_list in varchar2,
1720 p_repair_line_id in number,
1721 x_return_status out nocopy varchar2,
1722 x_msg_count out nocopy number,
1723 x_msg_data out nocopy varchar2,
1724 p_event in varchar2,
1725 p_reason_code in varchar2 default null,
1726 p_comments in varchar2 default null,
1727 p_validation_level in number)
1728 is
1729 l_from_flow_status_id number;
1730 l_to_flow_status_id number;
1731 x_object_version_number number;
1732 l_repair_type_id number;
1733 l_object_version_number number;
1734 lc_log_level number := fnd_log.g_current_runtime_level;
1735 lc_procedure_level number := fnd_log.level_procedure;
1736 lc_mod_name varchar2(100) := 'csd.plsql.csd_repairs_util.auto_update_ro_status';
1737 lc_api_version_number number := 1.0;
1738 lc_api_name varchar2(60) := 'auto_update_ro_status';
1739 l_un_rcvd_lines_exists varchar2(3);
1740
1741 begin
1742
1743 -- standard API compatibility check.
1744 IF NOT Fnd_Api.Compatible_API_Call
1745 (lc_api_version_number,
1746 p_api_version,
1747 lc_api_name,
1748 G_PKG_NAME)
1749 THEN
1750 RAISE Fnd_Api.G_EXC_ERROR;
1751 END IF;
1752
1753 IF fnd_api.to_boolean(p_init_msg_list)
1754 THEN
1755 -- initialize message list
1756 fnd_msg_pub.initialize;
1757 END IF;
1758
1759 if lc_log_level >= lc_procedure_level then
1760 fnd_log.string(lc_log_level,lc_mod_name,'Begin auto_update_ro_status');
1761 end if;
1762
1763 -- set the return status
1764 x_return_status := fnd_api.g_ret_sts_success;
1765 -- check if the profile to enable the auto update of RO status is set to yes.
1766
1767 if p_event = 'RECEIVE' then
1768 -- check if all the RMA -- CUST_PROD and RMA-EXCHANGE combination is received.
1769 -- only update the RO status if all RMA -- CUST_PROD combination is received
1770 --
1771 if nvl(fnd_profile.value('CSD_ENABLE_AUTO_UPD_RO_STAT'),'N') = 'N'
1772 then
1773 if lc_log_level >= lc_procedure_level then
1774 fnd_log.string(lc_log_level,lc_mod_name,'Profile CSD: Enable Auto Update of Repair
1775 Order Status upon Receiving is not set to yes.');
1776 end if;
1777 if p_validation_level = fnd_api.g_valid_level_full then
1778 -- to do: If the caller API needs this API to raise the errors, set a message into message stack and raise an error to exception block.
1779 null;
1780 else
1781 return; -- return to the caller.
1782 end if;
1783 end if;
1784
1785 begin
1786 select 'x'
1787 into l_un_rcvd_lines_exists
1788 from (
1789 select 'x'
1790 from csd_product_transactions cpt
1791 where cpt.repair_line_id = p_repair_line_id and
1792 cpt.action_type = 'RMA' and
1793 cpt.action_code in ('CUST_PROD','EXCHANGE') and
1794 cpt.prod_txn_status <> 'RECEIVED'
1795 and rownum < 2
1796 ) where rownum < 2 ;
1797 exception
1798 when no_data_found then
1799 l_un_rcvd_lines_exists := null;
1800 end;
1801
1802 if l_un_rcvd_lines_exists is null then
1803
1804 l_to_flow_status_id := fnd_profile.value('CSD_DEF_RO_STAT_FR_RCV');
1805 if l_to_flow_status_id is null then
1806 if lc_log_level >= lc_procedure_level then
1807 fnd_log.string(lc_log_level,lc_mod_name,'Profile CSD: Default Repair Order Status After
1808 Receving is not set');
1809 end if;
1810 if p_validation_level = fnd_api.g_valid_level_full then
1811 -- to do: set a message and raise an error for the caller API.
1812 null;
1813 else
1814 return; -- exit the procedure. With the success status.
1815 end if;
1816 end if;
1817
1818 -- get the 'from' flow status id.
1819 --
1820 begin
1821 select distinct cr.flow_status_id,
1822 cr.repair_type_id,
1823 cr.object_version_number
1824 into l_from_flow_status_id,
1825 l_repair_type_id,
1826 l_object_version_number
1827 from csd_repairs cr
1828 where cr.repair_line_id = p_repair_line_id;
1829 exception
1830 when no_data_found then
1831 -- should never get in here.
1832 null;
1833 end;
1834
1835 if l_to_flow_status_id = l_from_flow_status_id then
1836 -- to and from are same. Do not update.
1837 if lc_log_level >= lc_procedure_level then
1838 fnd_log.string(lc_log_level,lc_mod_name,'the new status is same as the old status. Do not update the status');
1839 end if;
1840 if p_validation_level = fnd_api.g_valid_level_full then
1841 -- to do: set a message and raise an error.
1842 null;
1843 else
1844 return;
1845 end if;
1846 end if;
1847 -- call the update flow status API to update the RO status.
1848 if lc_log_level >= lc_procedure_level then
1849 fnd_log.string(lc_log_level,lc_mod_name,'calling csd_repairs_pvt.update_flow_status API');
1850 end if;
1851
1852 csd_repairs_pvt.update_flow_status(p_api_version => 1,
1853 p_commit => fnd_api.g_false,
1854 p_init_msg_list => fnd_api.g_false,
1855 p_validation_level => fnd_api.g_valid_level_full,
1856 x_return_status => x_return_status,
1857 x_msg_count => x_msg_count,
1858 x_msg_data => x_msg_data,
1859 p_repair_line_id => p_repair_line_id,
1860 p_repair_type_id => l_repair_type_id,
1861 p_from_flow_status_id => l_from_flow_status_id,
1862 p_to_flow_status_id => l_to_flow_status_id,
1863 p_reason_code => p_reason_code,
1864 p_comments => p_comments,
1865 p_check_access_flag => 'Y',
1866 p_object_version_number => l_object_version_number,
1867 x_object_version_number => x_object_version_number );
1868 if x_return_status <> fnd_api.g_ret_sts_success then
1869 if lc_log_level >= lc_procedure_level then
1870 fnd_log.string(lc_log_level,lc_mod_name,'Error in csd_repairs_pvt.update_flow_status
1871 ['||x_msg_data||']');
1872 end if;
1873
1874 if p_validation_level = fnd_api.g_valid_level_full then
1875 -- to do: set a message and raise an error;
1876 null;
1877 else
1878 -- set the return to status to success.
1879 x_return_status := fnd_api.g_ret_sts_success;
1880 return;
1881 end if;
1882 end if;
1883
1884 end if; -- l_all_lines_rcvd
1885 end if; -- p_event = 'RECEIVE'
1886
1887 if p_commit = fnd_api.g_true then
1888 commit;
1889 end if;
1890
1891 exception
1892 when fnd_api.g_exc_error then
1893 -- raising a error may not be a good idea, as it can potentially roll back
1894 -- entire transaction. For now, we can prefer to do nothing.
1895 x_return_status := fnd_api.g_ret_sts_success;
1896 null;
1897 end auto_update_ro_status;
1898 -- end bug#7497790, 12.1 FP, subhat.
1899
1900 -- *******************************************************
1901 -- API Name: default_ro_attrs_from_rule
1902 -- Type : Private
1903 -- Pre-Req : None
1904 -- Parameters:
1905 -- IN OUT required?
1906 -- px_repln_rec in out nocopy CSD_REPAIRS_PUB.REPLN_REC_TYPE Y
1907 --
1908 -- Version : Current version 1.0
1909 -- Initial Version 1.0
1910 --
1911 -- Description : swai: utility procedure added for bug 7657379
1912 -- defaults Repair Order attributes from defaulting
1913 -- rules into px_repln_rec if the field is not already
1914 -- set. Uses existing values in px_repln_rec to populate
1915 -- the rule input rec for defaulting rules.
1916 -- Currently, the following fields are defaulted if
1917 -- they are passed in as G_MISS:
1918 -- Inventory Org
1919 -- Repair Org
1920 -- Repair Owner
1921 -- Repair Priority
1922 -- Repair Type
1923 -- Note that the profile option value will be returned
1924 -- if no applicable rules exist. For Repair Types,
1925 -- the profile value returned in for profile
1926 -- 'CSD_DEFAULT_REPAIR_TYPE'
1927 -- ***********************************************************
1928 PROCEDURE DEFAULT_RO_ATTRS_FROM_RULE (
1929 p_api_version in number,
1930 p_commit in varchar2,
1931 p_init_msg_list in varchar2,
1932 px_repln_rec in out nocopy csd_repairs_pub.repln_rec_type,
1933 x_return_status out nocopy varchar2,
1934 x_msg_count out nocopy number,
1935 x_msg_data out nocopy varchar2)
1936 IS
1937 cursor c_def_wip (p_repair_type_id number) is
1938 select distinct repair_mode, nvl(auto_process_rma,'N')
1939 from csd_repair_types_vl
1940 where repair_type_id = p_repair_type_id;
1941
1942 CURSOR c_get_sr_info(p_incident_id number) is
1943 select customer_id,
1944 account_id,
1945 bill_to_site_use_id,
1946 ship_to_site_use_id,
1947 inventory_item_id,
1948 category_id,
1949 contract_id,
1950 problem_code,
1951 customer_product_id
1952 from CS_INCIDENTS_ALL_VL
1953 where incident_id = p_incident_id;
1954
1955 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
1956 l_attr_type VARCHAR2(25);
1957 l_attr_code VARCHAR2(25);
1958 l_default_val_num NUMBER;
1959 l_default_val_str VARCHAR(30);
1960 l_default_rule_id NUMBER;
1961 l_inv_org NUMBER;
1962 l_repair_org NUMBER; -- repair org id
1963 l_repair_type_id NUMBER; -- repair type id
1964 l_repair_mode VARCHAR2(30) := ''; -- repair mode display name
1965 l_auto_process_rma VARCHAR2(30) := '';
1966 lc_log_level number := fnd_log.g_current_runtime_level;
1967 lc_procedure_level number := fnd_log.level_procedure;
1968 lc_mod_name varchar2(100) := 'csd.plsql.csd_repairs_util.default_ro_attrs_from_rule';
1969 lc_api_version_number number := 1.0;
1970 lc_api_name varchar2(60) := 'default_ro_attrs_from_rule';
1971
1972 BEGIN
1973 -- standard API compatibility check.
1974 if NOT FND_API.Compatible_API_Call
1975 (lc_api_version_number,
1976 p_api_version,
1977 lc_api_name,
1978 G_PKG_NAME)
1979 then
1980 RAISE Fnd_Api.G_EXC_ERROR;
1981 end if;
1982
1983 if FND_API.to_boolean(p_init_msg_list) then
1984 -- initialize message list
1985 fnd_msg_pub.initialize;
1986 end if;
1987
1988 if lc_log_level >= lc_procedure_level then
1989 fnd_log.string(lc_log_level,lc_mod_name,'Begin default_ro_attrs_from_rule');
1990 end if;
1991
1992 -- set the return status
1993 x_return_status := fnd_api.g_ret_sts_success;
1994
1995 -- Assume SR Incident Id is available to get info for defaulting RO attributes
1996 open c_get_sr_info(px_repln_rec.incident_id);
1997 fetch c_get_sr_info into
1998 l_rule_input_rec.SR_CUSTOMER_ID,
1999 l_rule_input_rec.SR_CUSTOMER_ACCOUNT_ID,
2000 l_rule_input_rec.SR_BILL_TO_SITE_USE_ID,
2001 l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID,
2002 l_rule_input_rec.SR_ITEM_ID,
2003 l_rule_input_rec.SR_ITEM_CATEGORY_ID,
2004 l_rule_input_rec.SR_CONTRACT_ID,
2005 l_rule_input_rec.SR_PROBLEM_CODE,
2006 l_rule_input_rec.SR_INSTANCE_ID;
2007 close c_get_sr_info;
2008
2009 l_rule_input_rec.RO_ITEM_ID := px_repln_rec.INVENTORY_ITEM_ID;
2010
2011 /****************************** DEFAULT INVENTORY ORG ******************************/
2012 if (px_repln_rec.inventory_org_id = FND_API.G_MISS_NUM) then
2013 l_attr_type := 'CSD_DEF_ENTITY_ATTR_RO';
2014 l_attr_code := 'INV_ORG';
2015 l_default_val_num := null;
2016 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2017 p_api_version_number => 1.0,
2018 p_init_msg_list => fnd_api.g_false,
2019 p_commit => fnd_api.g_false,
2020 p_validation_level => fnd_api.g_valid_level_full,
2021 p_entity_attribute_type => l_attr_type,
2022 p_entity_attribute_code => l_attr_code,
2023 p_rule_input_rec => l_rule_input_rec,
2024 x_default_value => l_default_val_num,
2025 x_rule_id => l_default_rule_id,
2026 x_return_status => x_return_status,
2027 x_msg_count => x_msg_count,
2028 x_msg_data => x_msg_data
2029 );
2030
2031 if (x_return_status = fnd_api.g_ret_sts_success) then
2032 if (l_default_val_num is not null) then
2033 l_inv_org := l_default_val_num;
2034 else
2035 l_inv_org := to_number(fnd_profile.value('CSD_DEF_REP_INV_ORG'));
2036 end if;
2037 else
2038 RAISE FND_API.G_EXC_ERROR;
2039 end if;
2040
2041 if l_inv_org is not null then
2042 px_repln_rec.inventory_org_id := l_inv_org;
2043 end if;
2044 end if;
2045
2046 /****************************** DEFAULT REPAIR ORG ******************************/
2047 if (px_repln_rec.resource_group = FND_API.G_MISS_NUM) then
2048 l_attr_type := 'CSD_DEF_ENTITY_ATTR_RO';
2049 l_attr_code := 'REPAIR_ORG';
2050 l_default_val_num := null;
2051 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2052 p_api_version_number => 1.0,
2053 p_init_msg_list => fnd_api.g_false,
2054 p_commit => fnd_api.g_false,
2055 p_validation_level => fnd_api.g_valid_level_full,
2056 p_entity_attribute_type => l_attr_type,
2057 p_entity_attribute_code => l_attr_code,
2058 p_rule_input_rec => l_rule_input_rec,
2059 x_default_value => l_default_val_num,
2060 x_rule_id => l_default_rule_id,
2061 x_return_status => x_return_status,
2062 x_msg_count => x_msg_count,
2063 x_msg_data => x_msg_data
2064 );
2065 if (x_return_status = fnd_api.g_ret_sts_success) then
2066 if (l_default_val_num is not null) then
2067 l_repair_org := l_default_val_num;
2068 else
2069 l_repair_org := to_number(fnd_profile.value('CSD_DEFAULT_REPAIR_ORG'));
2070 end if;
2071 else
2072 RAISE FND_API.G_EXC_ERROR;
2073 end if;
2074
2075 if l_repair_org is not null then
2076 px_repln_rec.resource_group := l_repair_org;
2077 end if;
2078 end if;
2079
2080 /****************************** DEFAULT REPAIR OWNER ******************************/
2081 if (px_repln_rec.resource_id = FND_API.G_MISS_NUM) then
2082 l_attr_type := 'CSD_DEF_ENTITY_ATTR_RO';
2083 l_attr_code := 'REPAIR_OWNER';
2084 l_default_val_num := null;
2085 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2086 p_api_version_number => 1.0,
2087 p_init_msg_list => fnd_api.g_false,
2088 p_commit => fnd_api.g_false,
2089 p_validation_level => fnd_api.g_valid_level_full,
2090 p_entity_attribute_type => l_attr_type,
2091 p_entity_attribute_code => l_attr_code,
2092 p_rule_input_rec => l_rule_input_rec,
2093 x_default_value => l_default_val_num,
2094 x_rule_id => l_default_rule_id,
2095 x_return_status => x_return_status,
2096 x_msg_count => x_msg_count,
2097 x_msg_data => x_msg_data
2098 );
2099 if (x_return_status = fnd_api.g_ret_sts_success) then
2100 if (l_default_val_num is not null) then
2101 px_repln_rec.resource_id := l_default_val_num;
2102 end if;
2103 else
2104 RAISE FND_API.G_EXC_ERROR;
2105 end if;
2106 end if;
2107
2108 /****************************** DEFAULT REPAIR PRIORITY ******************************/
2109 if (px_repln_rec.ro_priority_code = FND_API.G_MISS_CHAR) then
2110 l_attr_type := 'CSD_DEF_ENTITY_ATTR_RO';
2111 l_attr_code := 'REPAIR_PRIORITY';
2112 l_default_val_num := null;
2113 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2114 p_api_version_number => 1.0,
2115 p_init_msg_list => fnd_api.g_false,
2116 p_commit => fnd_api.g_false,
2117 p_validation_level => fnd_api.g_valid_level_full,
2118 p_entity_attribute_type => l_attr_type,
2119 p_entity_attribute_code => l_attr_code,
2120 p_rule_input_rec => l_rule_input_rec,
2121 x_default_value => l_default_val_str,
2122 x_rule_id => l_default_rule_id,
2123 x_return_status => x_return_status,
2124 x_msg_count => x_msg_count,
2125 x_msg_data => x_msg_data
2126 );
2127 if (x_return_status = fnd_api.g_ret_sts_success) then
2128 if (l_default_val_str is not null) then
2129 px_repln_rec.ro_priority_code := l_default_val_str;
2130 end if;
2131 else
2132 RAISE FND_API.G_EXC_ERROR;
2133 end if;
2134 end if;
2135
2136 /****************************** DEFAULT REPAIR TYPE ******************************/
2137 if (px_repln_rec.repair_type_id = FND_API.G_MISS_NUM) then
2138 l_attr_type := 'CSD_DEF_ENTITY_ATTR_RO';
2139 l_attr_code := 'REPAIR_TYPE';
2140 l_default_val_num := null;
2141 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2142 p_api_version_number => 1.0,
2143 p_init_msg_list => fnd_api.g_false,
2144 p_commit => fnd_api.g_false,
2145 p_validation_level => fnd_api.g_valid_level_full,
2146 p_entity_attribute_type => l_attr_type,
2147 p_entity_attribute_code => l_attr_code,
2148 p_rule_input_rec => l_rule_input_rec,
2149 x_default_value => l_default_val_num,
2150 x_rule_id => l_default_rule_id,
2151 x_return_status => x_return_status,
2152 x_msg_count => x_msg_count,
2153 x_msg_data => x_msg_data
2154 );
2155
2156 if (x_return_status = fnd_api.g_ret_sts_success) then
2157 if (l_default_val_num is not null) then
2158 l_repair_type_id := l_default_val_num;
2159 else
2160 l_repair_type_id := to_number(fnd_profile.value('CSD_DEFAULT_REPAIR_TYPE'));
2161 end if;
2162
2163 if l_repair_type_id is not null then
2164 open c_def_wip (l_repair_type_id);
2165 fetch c_def_wip into l_repair_mode, l_auto_process_rma;
2166 close c_def_wip;
2167
2168 px_repln_rec.repair_type_id := l_repair_type_id;
2169
2170 -- repair mode must be the same as what is defined for the
2171 -- repair type, so override any value that was already there
2172 px_repln_rec.repair_mode := l_repair_mode;
2173
2174 -- allow user to override auto process default, so only default
2175 -- if no value specified.
2176 if (px_repln_rec.auto_process_rma = FND_API.G_MISS_NUM) then
2177 px_repln_rec.auto_process_rma := l_auto_process_rma;
2178 end if;
2179 end if;
2180 else
2181 RAISE FND_API.G_EXC_ERROR;
2182 end if;
2183 end if;
2184
2185 if p_commit = fnd_api.g_true then
2186 commit;
2187 end if;
2188
2189 EXCEPTION
2190 When FND_API.G_EXC_ERROR then
2191 x_return_status := FND_API.G_RET_STS_ERROR ;
2192 FND_MSG_PUB.Count_And_Get
2193 (p_count => x_msg_count,
2194 p_data => x_msg_data );
2195
2196 When FND_API.G_EXC_UNEXPECTED_ERROR then
2197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2198 FND_MSG_PUB.Count_And_Get
2199 ( p_count => x_msg_count,
2200 p_data => x_msg_data );
2201
2202 When OTHERS then
2203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2204 If FND_MSG_PUB.Check_Msg_Level
2205 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
2206 FND_MSG_PUB.Add_Exc_Msg
2207 (G_PKG_NAME,
2208 lc_api_name );
2209 End If;
2210 FND_MSG_PUB.Count_And_Get
2211 (p_count => x_msg_count,
2212 p_data => x_msg_data );
2213 END DEFAULT_RO_ATTRS_FROM_RULE;
2214
2215 END Csd_Repairs_Util;