[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIRS_UTIL
Source
1 Package BODY Csd_Repairs_Util AS
2 /* $Header: csdxutlb.pls 120.21.12020000.5 2013/04/09 05:15:50 subhat 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 p_RO_WARRANTY_STATUS_CODE IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
342 x_Repln_Rec OUT NOCOPY Csd_Repairs_Pub.Repln_Rec_Type
343 ) IS
344 --
345 BEGIN
346
347 IF (g_debug > 0 ) THEN
348 Csd_Gen_Utility_Pvt.ADD( 'at begin convert rec');
349 END IF;
350
351
352 x_Repln_Rec.REPAIR_NUMBER := p_REPAIR_NUMBER;
353 x_Repln_Rec.INCIDENT_ID := p_INCIDENT_ID;
354 x_Repln_Rec.INVENTORY_ITEM_ID := p_INVENTORY_ITEM_ID;
355 x_Repln_Rec.CUSTOMER_PRODUCT_ID := p_CUSTOMER_PRODUCT_ID;
356 x_Repln_Rec.UNIT_OF_MEASURE := p_UNIT_OF_MEASURE;
357 x_Repln_Rec.REPAIR_TYPE_ID := p_REPAIR_TYPE_ID;
358 -- RESOURCE_GROUP Added by Vijay 10/28/2004
359 x_Repln_Rec.RESOURCE_GROUP := p_RESOURCE_GROUP;
360 x_Repln_Rec.RESOURCE_ID := p_RESOURCE_ID;
361 x_Repln_Rec.PROJECT_ID := p_PROJECT_ID;
362 x_Repln_Rec.TASK_ID := p_TASK_ID;
363 x_Repln_Rec.UNIT_NUMBER := p_UNIT_NUMBER; -- rfieldma, project integration
364 x_Repln_Rec.CONTRACT_LINE_ID := p_CONTRACT_LINE_ID;
365 x_Repln_Rec.AUTO_PROCESS_RMA := p_AUTO_PROCESS_RMA;
366 x_Repln_Rec.REPAIR_MODE := p_REPAIR_MODE;
367 x_Repln_Rec.OBJECT_VERSION_NUMBER := p_OBJECT_VERSION_NUMBER;
368 x_Repln_Rec.ITEM_REVISION := p_ITEM_REVISION;
369 x_Repln_Rec.INSTANCE_ID := p_INSTANCE_ID;
370 x_Repln_Rec.STATUS := p_STATUS;
371 x_Repln_Rec.STATUS_REASON_CODE := p_STATUS_REASON_CODE;
372 x_Repln_Rec.DATE_CLOSED := p_DATE_CLOSED;
373 x_Repln_Rec.APPROVAL_REQUIRED_FLAG := p_APPROVAL_REQUIRED_FLAG;
374 x_Repln_Rec.APPROVAL_STATUS := p_APPROVAL_STATUS;
375 x_Repln_Rec.SERIAL_NUMBER := p_SERIAL_NUMBER;
376 x_Repln_Rec.PROMISE_DATE := p_PROMISE_DATE;
377 x_Repln_Rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
378 x_Repln_Rec.ATTRIBUTE1 := p_ATTRIBUTE1;
379 x_Repln_Rec.ATTRIBUTE2 := p_ATTRIBUTE2;
380 x_Repln_Rec.ATTRIBUTE3 := p_ATTRIBUTE3;
381 x_Repln_Rec.ATTRIBUTE4 := p_ATTRIBUTE4;
382 x_Repln_Rec.ATTRIBUTE5 := p_ATTRIBUTE5;
383 x_Repln_Rec.ATTRIBUTE6 := p_ATTRIBUTE6;
384 x_Repln_Rec.ATTRIBUTE7 := p_ATTRIBUTE7;
385 x_Repln_Rec.ATTRIBUTE8 := p_ATTRIBUTE8;
386 x_Repln_Rec.ATTRIBUTE9 := p_ATTRIBUTE9;
387 x_Repln_Rec.ATTRIBUTE10 := p_ATTRIBUTE10;
388 x_Repln_Rec.ATTRIBUTE11 := p_ATTRIBUTE11;
389 x_Repln_Rec.ATTRIBUTE12 := p_ATTRIBUTE12;
390 x_Repln_Rec.ATTRIBUTE13 := p_ATTRIBUTE13;
391 x_Repln_Rec.ATTRIBUTE14 := p_ATTRIBUTE14;
392 x_Repln_Rec.ATTRIBUTE15 := p_ATTRIBUTE15;
393 x_Repln_Rec.QUANTITY := p_QUANTITY;
394 x_Repln_Rec.QUANTITY_IN_WIP := p_QUANTITY_IN_WIP;
395 x_Repln_Rec.QUANTITY_RCVD := p_QUANTITY_RCVD;
396 x_Repln_Rec.QUANTITY_SHIPPED := p_QUANTITY_SHIPPED;
397 x_Repln_Rec.CURRENCY_CODE := p_CURRENCY_CODE;
398 x_Repln_Rec.DEFAULT_PO_NUM := p_DEFAULT_PO_NUM;
399 x_Repln_Rec.REPAIR_GROUP_ID := p_REPAIR_GROUP_ID;
400 x_Repln_Rec.RO_TXN_STATUS := p_RO_TXN_STATUS;
401 x_Repln_Rec.ORDER_LINE_ID := p_ORDER_LINE_ID;
402 x_Repln_Rec.ORIGINAL_SOURCE_REFERENCE := p_ORIGINAL_SOURCE_REFERENCE;
403 x_Repln_Rec.ORIGINAL_SOURCE_HEADER_ID := p_ORIGINAL_SOURCE_HEADER_ID;
404 x_Repln_Rec.ORIGINAL_SOURCE_LINE_ID := p_ORIGINAL_SOURCE_LINE_ID;
405 x_Repln_Rec.PRICE_LIST_HEADER_ID := p_PRICE_LIST_HEADER_ID;
406 x_Repln_Rec.INVENTORY_ORG_ID := p_INVENTORY_ORG_ID;
407 -- swai: bug 4666344 added problem description
408 x_Repln_Rec.PROBLEM_DESCRIPTION := p_PROBLEM_DESCRIPTION;
409 x_Repln_Rec.RO_PRIORITY_CODE := p_RO_PRIORITY_CODE; -- swai: R12
410 x_Repln_Rec.RESOLVE_BY_DATE := p_RESOLVE_BY_DATE; -- rfieldma: 5355051
411 x_Repln_Rec.BULLETIN_CHECK_DATE := p_BULLETIN_CHECK_DATE;
412 x_Repln_Rec.ESCALATION_CODE := p_ESCALATION_CODE;
413 x_Repln_Rec.RO_WARRANTY_STATUS_CODE := p_RO_WARRANTY_STATUS_CODE;
414 -- additional DFF attributes, subhat(bug#7497907)
415 x_Repln_Rec.ATTRIBUTE16 := p_ATTRIBUTE16;
416 x_Repln_Rec.ATTRIBUTE17 := p_ATTRIBUTE17;
417 x_Repln_Rec.ATTRIBUTE18 := p_ATTRIBUTE18;
418 x_Repln_Rec.ATTRIBUTE19 := p_ATTRIBUTE19;
419 x_Repln_Rec.ATTRIBUTE20 := p_ATTRIBUTE20;
420 x_Repln_Rec.ATTRIBUTE21 := p_ATTRIBUTE21;
421 x_Repln_Rec.ATTRIBUTE22 := p_ATTRIBUTE22;
422 x_Repln_Rec.ATTRIBUTE23 := p_ATTRIBUTE23;
423 x_Repln_Rec.ATTRIBUTE24 := p_ATTRIBUTE24;
424 x_Repln_Rec.ATTRIBUTE25 := p_ATTRIBUTE25;
425 x_Repln_Rec.ATTRIBUTE26 := p_ATTRIBUTE26;
426 x_Repln_Rec.ATTRIBUTE27 := p_ATTRIBUTE27;
427 x_Repln_Rec.ATTRIBUTE28 := p_ATTRIBUTE28;
428 x_Repln_Rec.ATTRIBUTE29 := p_ATTRIBUTE29;
429 x_Repln_Rec.ATTRIBUTE30 := p_ATTRIBUTE30;
430
431 END Convert_to_Repln_Rec_Type;
432
433 PROCEDURE Convert_to_DEF_Rec_Type
434 (
435 p_attribute_category IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
436 p_attribute1 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
437 p_attribute2 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
438 p_attribute3 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
439 p_attribute4 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
440 p_attribute5 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
441 p_attribute6 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
442 p_attribute7 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
443 p_attribute8 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
444 p_attribute9 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
445 p_attribute10 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
446 p_attribute11 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
447 p_attribute12 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
448 p_attribute13 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
449 p_attribute14 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
450 p_attribute15 IN VARCHAR2 := Fnd_Api.G_MISS_CHAR,
451 p_attribute16 IN VARCHAR2 := FND_API.G_MISS_CHAR, -- subhat, dff changes(bug#7497907)
452 p_attribute17 IN VARCHAR2 := FND_API.G_MISS_CHAR,
453 p_attribute18 IN VARCHAR2 := FND_API.G_MISS_CHAR,
454 p_attribute19 IN VARCHAR2 := FND_API.G_MISS_CHAR,
455 p_attribute20 IN VARCHAR2 := FND_API.G_MISS_CHAR,
456 p_attribute21 IN VARCHAR2 := FND_API.G_MISS_CHAR,
457 p_attribute22 IN VARCHAR2 := FND_API.G_MISS_CHAR,
458 p_attribute23 IN VARCHAR2 := FND_API.G_MISS_CHAR,
459 p_attribute24 IN VARCHAR2 := FND_API.G_MISS_CHAR,
460 p_attribute25 IN VARCHAR2 := FND_API.G_MISS_CHAR,
461 p_attribute26 IN VARCHAR2 := FND_API.G_MISS_CHAR,
462 p_attribute27 IN VARCHAR2 := FND_API.G_MISS_CHAR,
463 p_attribute28 IN VARCHAR2 := FND_API.G_MISS_CHAR,
464 p_attribute29 IN VARCHAR2 := FND_API.G_MISS_CHAR,
465 p_attribute30 IN VARCHAR2 := FND_API.G_MISS_CHAR,
466 x_DEF_Rec OUT NOCOPY Csd_Repairs_Util.DEF_Rec_Type
467 ) IS
468 --
469 BEGIN
470 --
471 x_DEF_Rec.attribute_category := p_attribute_category;
472 x_DEF_Rec.attribute1 := p_attribute1;
473 x_DEF_Rec.attribute2 := p_attribute2;
474 x_DEF_Rec.attribute3 := p_attribute3;
475 x_DEF_Rec.attribute4 := p_attribute4;
476 x_DEF_Rec.attribute5 := p_attribute5;
477 x_DEF_Rec.attribute6 := p_attribute6;
478 x_DEF_Rec.attribute7 := p_attribute7;
479 x_DEF_Rec.attribute8 := p_attribute8;
480 x_DEF_Rec.attribute9 := p_attribute9;
481 x_DEF_Rec.attribute10 := p_attribute10;
482 x_DEF_Rec.attribute11 := p_attribute11;
483 x_DEF_Rec.attribute12 := p_attribute12;
484 x_DEF_Rec.attribute13 := p_attribute13;
485 x_DEF_Rec.attribute14 := p_attribute14;
486 x_DEF_Rec.attribute15 := p_attribute15;
487 x_DEF_Rec.attribute16 := p_attribute16; -- subhat, DFF changes(bug#7497907).
488 x_DEF_Rec.attribute17 := p_attribute17;
489 x_DEF_Rec.attribute18 := p_attribute18;
490 x_DEF_Rec.attribute19 := p_attribute19;
491 x_DEF_Rec.attribute20 := p_attribute20;
492 x_DEF_Rec.attribute21 := p_attribute21;
493 x_DEF_Rec.attribute22 := p_attribute22;
494 x_DEF_Rec.attribute23 := p_attribute23;
495 x_DEF_Rec.attribute24 := p_attribute24;
496 x_DEF_Rec.attribute25 := p_attribute25;
497 x_DEF_Rec.attribute26 := p_attribute26;
498 x_DEF_Rec.attribute27 := p_attribute27;
499 x_DEF_Rec.attribute28 := p_attribute28;
500 x_DEF_Rec.attribute29 := p_attribute29;
501 x_DEF_Rec.attribute30 := p_attribute30;
502 --
503 END Convert_to_DEF_Rec_Type;
504
505 PROCEDURE GET_ENTITLEMENTS
506 (
507 P_API_VERSION_NUMBER IN NUMBER,
508 P_INIT_MSG_LIST IN VARCHAR2 := 'F',
509 P_COMMIT IN VARCHAR2 := 'F',
510 P_CONTRACT_NUMBER IN VARCHAR2 := NULL,
511 P_SERVICE_LINE_ID IN NUMBER := NULL,
512 P_CUSTOMER_ID IN NUMBER := NULL,
513 P_SITE_ID IN NUMBER := NULL,
514 P_CUSTOMER_ACCOUNT_ID IN NUMBER := NULL,
515 P_SYSTEM_ID IN NUMBER := NULL,
516 P_INVENTORY_ITEM_ID IN NUMBER := NULL,
517 P_CUSTOMER_PRODUCT_ID IN NUMBER := NULL,
518 P_REQUEST_DATE IN DATE := NULL,
519 P_VALIDATE_FLAG IN VARCHAR2 := 'Y',
520 --Begin forwardporting bug fix for 2806199,2806661,2802141 By Vijay
521 P_BUSINESS_PROCESS_ID IN NUMBER DEFAULT NULL,
522 P_SEVERITY_ID IN NUMBER DEFAULT NULL,
523 P_TIME_ZONE_ID IN NUMBER DEFAULT NULL,
524 P_CALC_RESPTIME_FLAG IN VARCHAR2 DEFAULT NULL,
525 --End forwardporting bug fix for 2806199,2806661,2802141 By Vijay
526 X_ENT_CONTRACTS OUT NOCOPY Oks_Entitlements_Pub.GET_CONTOP_TBL,
527 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
528 X_MSG_COUNT OUT NOCOPY NUMBER,
529 X_MSG_DATA OUT NOCOPY VARCHAR2
530 )
531 IS
532 --
533 l_api_name VARCHAR2(30) := 'GET_ENTITLEMENTS';
534 l_input_param_rec Oks_Entitlements_Pub.get_contin_rec;
535 l_api_version_number CONSTANT NUMBER := 1.0;
536 --
537 BEGIN
538 --
539 SAVEPOINT Get_Entitlements_Pvt;
540 -- Standard call to check for call compatibility.
541 IF NOT Fnd_Api.Compatible_API_Call
542 (l_api_version_number,
543 p_api_version_number,
544 l_api_name,
545 G_PKG_NAME)
546 THEN
547 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
548 END IF;
549 -- Initialize message list if p_init_msg_list is set to TRUE.
550 IF Fnd_Api.to_Boolean(p_init_msg_list)
551 THEN
552 Fnd_Msg_Pub.initialize;
553 END IF;
554 -- Initialize API return status to success
555 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
556 --
557 -- API body
558 --
559 l_input_param_rec.contract_number := p_contract_number;
560 l_input_param_rec.service_line_id := p_service_line_id;
561 l_input_param_rec.party_id := p_customer_id;
562 l_input_param_rec.site_id := p_site_id;
563 l_input_param_rec.cust_acct_id := p_customer_account_id;
564 l_input_param_rec.system_id := p_system_id;
565 l_input_param_rec.item_id := p_inventory_item_id;
566 l_input_param_rec.product_id := p_customer_product_id;
567 l_input_param_rec.request_date := p_request_date;
568 l_input_param_rec.validate_flag := p_validate_flag;
569 --Begin forwardporting bug fix for 2806199,2806661,2802141 By Vijay
570 l_input_param_rec.calc_resptime_flag := NVL(p_calc_resptime_flag,'N');
571 l_input_param_rec.business_process_id := p_business_process_id;
572 l_input_param_rec.severity_id := p_severity_id;
573 l_input_param_rec.time_zone_id := p_time_zone_id;
574 --End forwardporting bug fix for 2806199,2806661,2802141 By Vijay
575 --
576 -- If the validate_flag is 'Y' then only the valid contract lines as of
577 -- 'request_date' are returned. If the validate_flag is 'N' then
578 -- all the contract lines - valid and invalid- are returned.
579 --
580 Oks_Entitlements_Pub.GET_CONTRACTS
581 (p_api_version => p_api_version_number,
582 p_init_msg_list => p_init_msg_list,
583 p_inp_rec => l_input_param_rec,
584 x_return_status => x_return_status,
585 x_msg_count => x_msg_count,
586 x_msg_data => x_msg_data,
587 x_ent_contracts => x_ent_contracts);
588 --
589 IF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
590 RAISE Fnd_Api.G_EXC_ERROR ;
591 ELSIF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
592 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
593 END IF ;
594 --
595 -- End of API body.
596 --
597 -- Standard check for p_commit
598 IF Fnd_Api.to_Boolean(p_commit)
599 THEN
600 COMMIT WORK;
601 END IF;
602 -- Standard call to get message count and if count is 1, get message info.
603 Fnd_Msg_Pub.Count_And_Get
604 (p_count => x_msg_count,
605 p_data => x_msg_data
606 );
607 --
608 EXCEPTION
609 WHEN Fnd_Api.G_EXC_ERROR THEN
610 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
611 (P_API_NAME => L_API_NAME
612 ,P_PKG_NAME => G_PKG_NAME
613 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_ERROR
614 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
615 ,X_MSG_COUNT => X_MSG_COUNT
616 ,X_MSG_DATA => X_MSG_DATA
617 ,X_RETURN_STATUS => X_RETURN_STATUS);
618 -- RAISE;
619 --
620 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
621 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
622 (P_API_NAME => L_API_NAME
623 ,P_PKG_NAME => G_PKG_NAME
624 ,P_EXCEPTION_LEVEL => Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR
625 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
626 ,X_MSG_COUNT => X_MSG_COUNT
627 ,X_MSG_DATA => X_MSG_DATA
628 ,X_RETURN_STATUS => X_RETURN_STATUS);
629 -- RAISE;
630 --
631 WHEN OTHERS THEN
632 Jtf_Plsql_Api.HANDLE_EXCEPTIONS
633 (P_API_NAME => L_API_NAME
634 ,P_PKG_NAME => G_PKG_NAME
635 ,P_EXCEPTION_LEVEL => Jtf_Plsql_Api.G_EXC_OTHERS
636 ,P_PACKAGE_TYPE => Jtf_Plsql_Api.G_PVT
637 ,X_MSG_COUNT => X_MSG_COUNT
638 ,X_MSG_DATA => X_MSG_DATA
639 ,X_RETURN_STATUS => X_RETURN_STATUS);
640 -- RAISE;
641 --
642 END GET_ENTITLEMENTS;
643
644 PROCEDURE Get_KB_Element_Description
645 (
646 p_element_id IN NUMBER,
647 p_element_description OUT NOCOPY VARCHAR2
648 )
649 IS
650 --
651 l_element_desc CLOB;
652 l_amount BINARY_INTEGER := 32767;
653 l_position INTEGER := 1;
654 l_buffer VARCHAR2(32767);
655 l_chunksize INTEGER;
656 --
657 BEGIN
658 --
659 SELECT description
660 INTO l_element_desc
661 FROM cs_kb_elements_vl
662 WHERE element_id = p_element_id;
663 --
664 l_chunksize := DBMS_LOB.getchunksize(l_element_desc);
665 IF l_chunksize IS NOT NULL THEN
666 IF l_chunksize < l_amount THEN
667 l_amount := (l_amount/l_chunksize) * l_chunksize;
668 END IF;
669 END IF;
670 IF l_element_desc IS NOT NULL THEN
671 DBMS_LOB.READ
672 (lob_loc => l_element_desc,
673 amount => l_amount,
674 offset => l_position,
675 buffer => l_buffer
676 );
677 ELSE
678 l_buffer := NULL;
679 END IF;
680 --
681 p_element_description := l_buffer;
682 --
683 EXCEPTION
684 WHEN NO_DATA_FOUND THEN
685 p_element_description := NULL;
686 WHEN TOO_MANY_ROWS THEN
687 p_element_description := NULL;
688 WHEN OTHERS THEN
689 p_element_description := NULL;
690 --
691 END Get_KB_Element_Description;
692
693 -- R12 Development Begin
694 -- *******************************************************
695 -- API Name: check_task_n_wip
696 -- Type : Private
697 -- Pre-Req : None
698 -- Parameters:
699 -- IN
700 -- p_repair_line_id IN VARCHAR2,
701 -- p_repair_status IN VARCHAR2,
702 -- OUT
703 -- x_return_status
704 -- x_msg_count
705 -- x_msg_data
706 --
707 -- Version : Current version 1.0
708 -- Initial Version 1.0
709 --
710 -- Description : This API checks if there are any open tasks or wip jobs
711 -- for the repair order if the status is 'C'. If there are
712 -- open tasks or wipjobs depending on the mode, this api
713 -- returns FAILURE otherwise SUCCESS.
714 --
715 --
716 -- ***********************************************************
717 PROCEDURE Check_Task_N_Wipjob
718 (
719 p_repair_line_id IN NUMBER,
720 p_repair_status IN VARCHAR2,
721 x_return_status OUT NOCOPY VARCHAR2,
722 x_msg_count OUT NOCOPY NUMBER,
723 x_msg_data OUT NOCOPY VARCHAR2
724 ) IS
725
726 -- Local variables
727 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_TASK_N_WIPJOB';
728 l_debug_level NUMBER ;
729 l_repair_mode VARCHAR2(10);
730 l_tmp_char VARCHAR2(1);
731 C_TASK_MODE CONSTANT VARCHAR2(4) := 'TASK';
732 C_WIP_MODE CONSTANT VARCHAR2(3) := 'WIP';
733 C_CLOSED_STATUS CONSTANT VARCHAR2(1) := 'C';
734
735 --Cursor definitions
736
737 -- Cursor to check if there are open tasks for the
738 -- repair order.
739 CURSOR task_cur IS
740 SELECT 'x'
741 FROM jtf_tasks_b TASK, jtf_task_statuses_b status
742 WHERE TASK.source_object_id = p_repair_line_id
743 AND TASK.source_object_type_code = 'DR'
744 AND TASK.task_status_id = status.task_status_id
745 AND NVL(status.closed_flag,'N') <> 'Y'
746 AND ROWNUM =1;
747
748 -- Cursor to check open wip jobs for the repair order
749 CURSOR wipjob_cur IS
750 SELECT 'x' /* crx.repair_job_xref_id */
751 FROM csd_repair_job_xref crx, wip_discrete_jobs wdj
752 WHERE crx.repair_line_id = p_repair_line_id
753 AND crx.wip_entity_id = wdj.wip_entity_id
754 AND wdj.status_type NOT IN (4,5,7,12)
755 /* 4: Complete,5: Complete-No charges,7: Cancelled,12: Closed */
756 UNION ALL
757 SELECT 'x' /* repair_job_xref_id*/
758 FROM csd_repair_job_xref
759 WHERE repair_line_id = p_repair_line_id
760 AND wip_entity_id IS NULL;
761
762 -- Cursor to get the repair mode
763 CURSOR repair_details_cur IS
764 SELECT repair_mode FROM csd_repairs
765 WHERE repair_line_id = p_repair_line_id;
766
767
768
769 BEGIN
770
771 -- Initialize local variables.
772 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
773 l_debug_level := Fnd_Log.G_CURRENT_RUNTIME_LEVEL;
774
775 IF (Fnd_Log.LEVEL_PROCEDURE >= l_debug_level) THEN
776 Fnd_Log.STRING (Fnd_Log.LEVEL_PROCEDURE, 'csd.plsql.csd_repairs_util.check_task_n_wipjob.begin',
777 '-------------Entered check_task_n_wipjob----------------');
778 END IF;
779
780
781 -- Get the repair mode
782 OPEN repair_details_cur ;
783 FETCH repair_details_cur INTO l_repair_mode;
784 IF(repair_details_cur%NOTFOUND) THEN
785 CLOSE repair_details_Cur;
786 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
787 END IF;
788 CLOSE repair_details_cur;
789
790 /****
791 If the repair status is going to be closed, then if the mode is wip check for open wip jobs,
792 if the mode is task, check for open tasks. Return failure if there are open items, return
793 success if not.
794 ****/
795 IF( p_repair_status = C_CLOSED_STATUS) THEN
796
797 IF (Fnd_Log.LEVEL_EVENT >= l_debug_level) THEN
798 Fnd_Log.STRING (Fnd_Log.LEVEL_EVENT, 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
799 '-------------status is being changed to close,ro['||p_repair_line_id||']-----');
800 END IF;
801
802 IF (l_repair_mode = C_WIP_MODE) THEN
803
804 IF (Fnd_Log.LEVEL_STATEMENT >= l_debug_level) THEN
805 Fnd_Log.STRING (Fnd_Log.LEVEL_STATEMENT, 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
806 '-------------checking for open jobs---------------');
807 END IF;
808
809 OPEN wipjob_cur;
810 FETCH wipjob_cur INTO l_tmp_char;
811 IF(wipjob_cur%NOTFOUND) THEN
812 CLOSE wipjob_cur;
813 IF (Fnd_Log.LEVEL_STATEMENT >= l_debug_level) THEN
814 Fnd_Log.STRING (Fnd_Log.LEVEL_STATEMENT, 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
815 '-------------there are open jobs---------------');
816 END IF;
817 Fnd_Message.set_name('CSD', 'CSD_API_OPEN_WIP_JOBS');
818 Fnd_Msg_Pub.ADD;
819 RAISE Fnd_Api.G_EXC_ERROR ;
820 END IF;
821 CLOSE wipjob_cur;
822 ELSIF (l_repair_mode = C_TASK_MODE) THEN
823
824 IF (Fnd_Log.LEVEL_STATEMENT >= l_debug_level) THEN
825 Fnd_Log.STRING (Fnd_Log.LEVEL_STATEMENT, 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
826 '-------------checking for open tasks---------------');
827 END IF;
828
829 OPEN task_cur;
830 FETCH task_cur INTO l_tmp_char;
831 IF(task_cur%NOTFOUND) THEN
832 CLOSE task_cur;
833 IF (Fnd_Log.LEVEL_STATEMENT >= l_debug_level) THEN
834 Fnd_Log.STRING (Fnd_Log.LEVEL_STATEMENT, 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
835 '-------------there are open tasks---------------');
836 END IF;
837 Fnd_Message.set_name('CSD', 'CSD_API_OPEN_TASKS');
838 Fnd_Msg_Pub.ADD;
839 RAISE Fnd_Api.G_EXC_ERROR ;
840 END IF;
841 CLOSE task_cur;
842 END IF;
843 END IF;
844
845 IF (Fnd_Log.level_procedure >= l_debug_level) THEN
846 Fnd_Log.STRING (Fnd_Log.level_procedure, 'csd.plsql.csd_repairs_util.check_task_n_wipjob.end',
847 '-------------Leaving check_task_n_wipjob----------------');
848 END IF;
849
850 EXCEPTION
851 WHEN Fnd_Api.G_EXC_ERROR THEN
852 x_return_status := Fnd_Api.G_RET_STS_ERROR ;
853 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
854 IF ( Fnd_Log.LEVEL_ERROR >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
855 Fnd_Log.STRING(Fnd_Log.LEVEL_ERROR,
856 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
857 'EXC_ERROR ['||x_msg_data||']');
858 END IF;
859
860 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
861 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
862 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data );
863 IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
864 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
865 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
866 'EXC_UNEXPECTED_ERROR ['||x_msg_data||']');
867 END IF;
868
869 WHEN OTHERS THEN
870 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
871 IF Fnd_Msg_Pub.Check_Msg_Level (Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
872 THEN
873 Fnd_Msg_Pub.Add_Exc_Msg (G_PKG_NAME , l_api_name );
874 END IF;
875 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data );
876
877 IF ( Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL ) THEN
878 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,
879 'csd.plsql.csd_repairs_util.check_task_n_wipjob',
880 'SQL Message ['||SQLERRM||']');
881 END IF;
882
883
884 END Check_Task_N_Wipjob;
885
886
887 -- *******************************************************
888 -- API Name: convert_Status_val_to_Id
889 -- Type : Private
890 -- Pre-Req : None
891 -- Parameters:
892 -- IN
893 -- p_status_rec IN CSD_REPAIRS_PUB.REPAIR_STATUS_REC_TYPE ,
894 -- OUT
895 -- x_status_rec OUT CSD_REPAIRS_PUB.REPAIR_STATUS_REC_TYPE ,
896 -- x_return_status
897 --
898 -- Version : Current version 1.0
899 -- Initial Version 1.0
900 --
901 -- Description : Converts value to Ids in the input repair status record.
902 --
903 -- ***********************************************************
904
905 PROCEDURE Convert_status_Val_to_Id(p_repair_status_rec IN Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE,
906 x_repair_status_rec OUT NOCOPY Csd_Repairs_Pub.REPAIR_STATUS_REC_TYPE,
907 x_return_status OUT NOCOPY VARCHAR2)
908 IS
909 l_debug_level NUMBER;
910
911 --Value to id conversion cursor
912 CURSOR repair_id_conv_cur(p_repair_number VARCHAR2) IS
913 SELECT REPAIR_LINE_ID
914 FROM CSD_REPAIRS
915 WHERE repair_number = p_repair_number;
916
917 -- Get status_id from status_Code
918 CURSOR flow_stat_cur(p_repair_status VARCHAR2) IS
919 SELECT FLOW_STATUS_ID
920 FROM CSD_FLOW_STATUSES_B
921 WHERE FLOW_STATUS_CODE = p_repair_status;
922
923
924 BEGIN
925
926
927 -- Initialize local variables.
928 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
929 l_debug_level := Fnd_Log.G_CURRENT_RUNTIME_LEVEL;
930
931 IF (Fnd_Log.LEVEL_PROCEDURE >= l_debug_level) THEN
932 Fnd_Log.STRING (Fnd_Log.LEVEL_PROCEDURE, 'csd.plsql.csd_repairs_util.Convert_Status_val_to_Id.begin',
933 '-------------Entered Convert_Status_val_to_Id----------------');
934 END IF;
935
936
937
938 x_repair_status_rec := p_repair_status_rec;
939
940 IF (p_repair_status_rec.repair_line_id IS NULL) THEN
941 -- ID based attribute is NULL or MISSING
942 IF (p_repair_status_rec.repair_number IS NULL) THEN
943 -- value based parameter is also NULL or MISSING
944 Fnd_Message.SET_NAME ('CSD', 'CSD_API_INV_REP_NUM');
945 Fnd_Msg_Pub.ADD;
946 x_return_status := Fnd_Api.G_RET_STS_ERROR;
947 ELSE
948 OPEN repair_id_conv_cur(p_repair_status_rec.repair_number);
949 FETCH repair_id_conv_cur INTO x_repair_status_rec.repair_line_id;
950 IF repair_id_conv_cur%NOTFOUND THEN
951 -- Id fetch was not successful
952 -- Conversion failed.
953 CLOSE repair_id_conv_cur;
954 Fnd_Message.SET_NAME ('CSD', 'CSD_API_INV_REP_NUM');
955 Fnd_Msg_Pub.ADD;
956 x_return_status := Fnd_Api.G_RET_STS_ERROR;
957 RAISE Fnd_Api.G_EXC_ERROR;
958 END IF;
959 CLOSE repair_id_conv_cur;
960 END IF;
961 ELSIF p_repair_status_rec.repair_line_id IS NOT NULL THEN
962 -- ID based attribute is present
963 x_repair_status_rec.repair_line_id:= p_repair_status_rec.repair_line_id ;
964 -- If the value based parameter is also passed, generate an
965 -- informational message.
966 IF (p_repair_status_rec.repair_number IS NOT NULL) THEN
967 Fnd_Message.SET_NAME('CSD', 'CSD_API_INPUT_IGNORE');
968 Fnd_Msg_Pub.ADD;
969 END IF;
970 END IF;
971
972
973 IF (p_repair_status_rec.repair_status_id IS NULL) THEN
974 -- ID based attribute is NULL or MISSING
975 IF (p_repair_status_rec.repair_status IS NULL) THEN
976 -- value based parameter is also NULL or MISSING
977 Fnd_Message.SET_NAME('CSD','CSD_INVALID_FLOW_STATUS');
978 Fnd_Msg_Pub.ADD;
979 x_return_status := Fnd_Api.G_RET_STS_ERROR;
980 ELSE
981 OPEN flow_stat_cur(p_repair_status_Rec.repair_status);
982 FETCH flow_stat_cur INTO x_repair_status_Rec.repair_status_id;
983 IF(flow_stat_cur%NOTFOUND) THEN
984 CLOSE flow_stat_cur;
985 Fnd_Message.SET_NAME('CSD','CSD_INVALID_FLOW_STATUS');
986 Fnd_Msg_Pub.ADD;
987 x_return_status := Fnd_Api.G_RET_STS_ERROR;
988 RAISE Fnd_Api.G_EXC_ERROR;
989 END IF;
990 CLOSE flow_stat_cur;
991 END IF;
992 ELSIF p_repair_status_rec.repair_status_id IS NOT NULL THEN
993 -- ID based attribute is present
994 x_repair_status_rec.repair_status_id:= p_repair_status_rec.repair_status_id; --bug#14121546
995 -- If the value based parameter is also passed, generate an
996 -- informational message.
997 IF (p_repair_status_rec.repair_status IS NOT NULL) THEN
998 Fnd_Message.SET_NAME('CSD', 'CSD_API_INPUT_IGNORE');
999 Fnd_Msg_Pub.ADD;
1000 END IF;
1001 END IF;
1002
1003
1004
1005 IF (Fnd_Log.level_procedure >= l_debug_level) THEN
1006 Fnd_Log.STRING (Fnd_Log.level_procedure, 'csd.plsql.csd_repairs_util.Convert_Status_Val_to_Id.end',
1007 '-------------Leaving Convert_Status_Val_to_Id----------------');
1008 END IF;
1009
1010 END Convert_status_Val_to_Id;
1011
1012 -- ***********************************************************
1013 -- API Name: Check_WebSrvc_Security
1014 -- Type : Private
1015 -- Pre-Req : None
1016 -- Parameters:
1017 -- IN
1018 -- p_repair_line_id IN VARCHAR2,
1019 -- OUT
1020 -- x_return_status
1021 --
1022 -- Version : Current version 1.0
1023 -- Initial Version 1.0
1024 --
1025 -- Description : This API checks the security for the web service
1026 -- call. The security is setup as the permission
1027 -- to the current logged in user. If the logged in
1028 -- user has access to 1) account on SR
1029 -- returns true otherwise it returns false.
1030 -- The two other permisions for 3rd party scenario's
1031 -- are not developed for now. We need to design
1032 -- that in conjunction with logistics enhancements
1033 -- for the 3rd scenario.
1034 -- 2) bill to party on SR
1035 -- 3) ship to party on SR
1036 --
1037 ---- ***********************************************************
1038 PROCEDURE Check_WebSrvc_Security
1039 (
1040 p_repair_line_id IN NUMBER,
1041 x_return_status OUT NOCOPY VARCHAR2
1042 ) IS
1043 l_return_Status VARCHAR2(1);
1044 l_predicate VARCHAR2(4000);
1045 l_table_alias VARCHAR2(80);
1046 l_Sql_str VARCHAR(8000);
1047 l_tmp_str VARCHAR2(30);
1048 C_CUST_PROD_ACTION_CODE CONSTANT VARCHAR2(30) := 'CUST_PROD';
1049 C_3RDPARTY_RMA_ACTION_TYPE CONSTANT VARCHAR2(30) := '3RDPARTY_RMA';
1050 C_3RDPARTY_SHIP_ACTION_TYPE CONSTANT VARCHAR2(30) := '3RDPARTY_SHIP';
1051
1052
1053 BEGIN
1054 /******************
1055 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,
1056 p_object_name => Repair Order,
1057 p_grant_instance_type => 'SET',
1058 p_statement_type => 'EXISTS'
1059
1060 *****************/
1061
1062 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1063 /*!!!!!!!!!! Temporarily disabling the security
1064 return ;!!!!!*******/
1065
1066 l_table_alias := 'ro';
1067 debug('Calling getSecurity_predicate');
1068 Fnd_Data_Security.GET_SECURITY_PREDICATE(
1069 p_api_version => 1.0,
1070 p_function => NULL,
1071 p_object_name => 'CSD_RO_OBJ',
1072 p_grant_instance_type => 'SET',
1073 p_statement_type => 'EXISTS',
1074 x_predicate => l_predicate,
1075 x_return_status => l_return_status,
1076 p_table_alias => l_table_alias );
1077
1078
1079 debug('getSecurity_predicate return value['||l_Return_status||']');
1080 debug('success['||FND_API.G_RET_STS_SUCCESS||']');
1081 debug('getSecurity_predicate predicate value['||l_predicate||']');
1082
1083 IF(l_return_status <> FND_API.G_TRUE) THEN
1084 debug('returning l_return_status['||l_return_status||']');
1085 x_return_status := FND_API.G_RET_STS_ERROR;
1086 ELSE
1087 IF(l_predicate IS NOT NULL AND l_predicate <> '(1=1)') THEN
1088
1089 debug('forming sql str');
1090 l_sql_str := 'Select ''x'' from csd_repairs ro '
1091 || ' Where ro.repair_line_id = :1 and '
1092 || l_predicate ;
1093
1094 debug('l_sql before['||l_sql_str||']');
1095 BEGIN
1096 EXECUTE IMMEDIATE l_sql_str INTO l_tmp_Str
1097 USING p_repair_line_id ;
1098 EXCEPTION
1099 WHEN NO_DATA_FOUND THEN
1100 x_return_status := FND_API.G_RET_STS_ERROR;
1101 END;
1102 debug('l_tmp_Str after['||l_tmp_str||']');
1103 IF(l_tmp_str IS NULL ) THEN
1104 debug('returning failure because l_tmp_Str['||l_tmp_str||']');
1105 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1106 END IF;
1107
1108 ELSE
1109 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1110 END IF;
1111 END IF;
1112
1113 /************************
1114 Call FND_DATA_SECURITY.GET_PREDICATE to get the SQL predicate for checking
1115 the permission for the current logged in user for the bill to check. Parameters,
1116 p_object_name => 'Repair Logistics Line BillTo',
1117 p_grant_instance_type => 'SET',
1118 p_statement_type => 'EXISTS'
1119
1120 ************************/
1121 /********** Not done for R12 first release
1122 *********************************************
1123 Fnd_Data_Security.GET_SECURITY_PREDICATE(
1124 p_api_version => 1.0,
1125 p_function => NULL,
1126 p_object_name => 'Repair LogisticsLine BillTo',
1127 p_grant_instance_type => 'SET',
1128 p_user_name => Fnd_Global.USER_ID,
1129 p_statement_type => 'EXISTS',
1130 x_predicate => l_predicate,
1131 x_return_status => l_return_status,
1132 p_table_alias => l_table_alias );
1133
1134 IF(l_predicate IS NULL OR l_predicate <> '(1=1)') THEN
1135
1136 l_sql_str := ' Select ''x'' from csd_repairs_v ro, csd_product_txns_v prd, '
1137 + ' cs_estimate_details csd Where ro.repair_line_id = :1'
1138 + ' And prd.repair_line_id = ro.repair_line_id '
1139 + ' And prd.action_code = :2 '
1140 + ' And prd.action_type = :3 '
1141 + ' And csd.estimate_detail_id = prd.estimate_detail_id '
1142 + ' And ' + l_predicate
1143 ;
1144
1145 EXECUTE IMMEDIATE l_sql_str INTO l_tmp_Str
1146 USING TO_CHAR(p_repair_line_id),
1147 C_CUST_PROD_ACTION_CODE ,
1148 C_3RDPARTY_RMA_ACTION_TYPE ;
1149 IF(l_tmp_str IS NULL ) THEN
1150 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1151 END IF;
1152
1153 END IF;
1154 ************************************************
1155 ***********************************************/
1156
1157
1158 /************************
1159 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,
1160 p_object_name => 'Repair Logistics Line ShipTo',
1161 p_grant_instance_type => 'SET',
1162 p_statement_type => 'EXISTS'
1163 ************************/
1164 /************************************ Not done for
1165 R12 first release
1166 ***************************************
1167 Fnd_Data_Security.GET_SECURITY_PREDICATE(
1168 p_api_version => 1.0,
1169 p_function => NULL,
1170 p_object_name => 'Repair LogisticsLine ShipTo',
1171 p_grant_instance_type => 'SET',
1172 p_user_name => Fnd_Global.USER_ID,
1173 p_statement_type => 'EXISTS',
1174 x_predicate => l_predicate,
1175 x_return_status => l_return_status,
1176 p_table_alias => l_table_alias );
1177
1178 IF(l_predicate IS NULL OR l_predicate <> '(1=1)') THEN
1179
1180 l_sql_str := ' Select ''x'' from csd_repairs_v ro, csd_product_txns_v prd, '
1181 + ' cs_estimate_details csd Where ro.repair_line_id = :1'
1182 + ' And prd.repair_line_id = ro.repair_line_id '
1183 + ' And prd.action_code = :2 '
1184 + ' And prd.action_type = :3 '
1185 + ' And csd.estimate_detail_id = prd.estimate_detail_id '
1186 + ' And ' + l_predicate ;
1187
1188 EXECUTE IMMEDIATE l_sql_str INTO l_tmp_Str
1189 USING TO_CHAR(p_repair_line_id),
1190 C_CUST_PROD_ACTION_CODE ,
1191 C_3RDPARTY_SHIP_ACTION_TYPE ;
1192 IF(l_tmp_str IS NULL ) THEN
1193 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1194 END IF;
1195
1196 END IF;
1197 ************************************************
1198 ***********************************************/
1199
1200 debug('returning success');
1201 EXCEPTION
1202 WHEN OTHERS THEN
1203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1204 debug('sqlca.sqlcode ['||sqlcode ||']');
1205 debug('sqlca.sqlerrm['||sqlerrm||']');
1206
1207
1208 END check_Websrvc_security;
1209 -- R12 Development End
1210
1211
1212 /*-----------------------------------------------------------------*/
1213 /* procedure name: change_item_ib_owner */
1214 /* description : Procedure to Change the Install Base Owner for */
1215 /* a single item */
1216 /*-----------------------------------------------------------------*/
1217 PROCEDURE CHANGE_ITEM_IB_OWNER
1218 (
1219 p_create_tca_relation IN VARCHAR2 := NULL,
1220 p_instance_id IN NUMBER,
1221 p_new_owner_party_id IN NUMBER,
1222 p_new_owner_account_id IN NUMBER,
1223 p_current_owner_party_id IN NUMBER,
1224 x_return_status OUT NOCOPY VARCHAR2,
1225 x_msg_count OUT NOCOPY NUMBER,
1226 x_msg_data OUT NOCOPY VARCHAR2,
1227 x_tca_relation_id OUT NOCOPY NUMBER
1228 )
1229 IS
1230
1231 -- variables for update instance
1232 l_csiip_inst_party_id Number := NULL;
1233 l_csiip_obj_ver_num Number := NULL;
1234 l_instance_account_id Number := NULL;
1235 l_inst_acct_obj_ver_num Number := NULL;
1236 l_object_version_number Number := NULL;
1237 l_instance_rec csi_datastructures_pub.instance_rec;
1238 l_party_tbl csi_datastructures_pub.party_tbl;
1239 l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
1240 l_account_tbl csi_datastructures_pub.party_account_tbl;
1241 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
1242 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
1243 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
1244 l_txn_rec csi_datastructures_pub.transaction_rec;
1245 x_instance_id_lst csi_datastructures_pub.id_tbl;
1246
1247 -- variables for create TCA relationship
1248 l_current_owner_party_type Varchar2(150) := NULL;
1249 l_new_owner_party_type Varchar2(150) := NULL;
1250 l_relationship_rec hz_relationship_v2pub.relationship_rec_type;
1251 l_rel_party_id Number;
1252 l_rel_party_number Varchar2(150);
1253 l_tca_rel_in_params csd_ib_chown_cuhk.tca_rel_info_in_rec_type;
1254 l_tca_rel_out_params csd_ib_chown_cuhk.tca_rel_info_out_rec_type;
1255 l_tca_rel_count Number;
1256
1257 --bug#8508030
1258 l_bill_to_address Number;
1259 l_ship_to_address Number;
1260 --bug#8508030
1261
1262 -- API variables
1263 l_api_name CONSTANT Varchar(30) := 'CHANGE_ITEM_IB_OWNER';
1264 l_api_version CONSTANT Number := 1.0;
1265
1266 -- Cursor to select the Instance party id
1267 Cursor c_instance_party(p_instance_id number) IS
1268 Select instance_party_id,
1269 object_version_number
1270 from csi_i_parties
1271 where instance_id = p_instance_id
1272 and relationship_type_code = 'OWNER'
1273 and sysdate between nvl(active_start_date, sysdate-1)
1274 and nvl(active_end_date, sysdate+1);
1275
1276 -- Cursor to derive the Instance details
1277 Cursor c_instance_details(p_instance_id number) IS
1278 Select object_version_number from csi_item_instances
1279 where instance_id = p_instance_id;
1280
1281 -- Cursor to derive the Instance Account Id
1282 Cursor c_instance_account(p_instance_party_id number) is
1283 Select ip_account_id,
1284 object_version_number
1285 from csi_ip_accounts
1286 where instance_party_id = p_instance_party_id;
1287
1288 -- Cursor to derive party information from hz_parties
1289 Cursor c_hz_parties_info(p_party_id number) is
1290 Select party_type
1291 from hz_parties
1292 where party_id = p_party_id;
1293
1294 -- cursor to get the number of party relationships for the given criteria
1295 Cursor c_tca_rel_count(p_subject_id number, p_subject_type varchar2,
1296 p_object_id number, p_object_type varchar2,
1297 p_relationship_code varchar2) is
1298 Select count(relationship_id)
1299 from hz_relationships
1300 where subject_id = p_subject_id
1301 and subject_type = p_subject_type
1302 and subject_table_name = 'HZ_PARTIES'
1303 and object_id = p_object_id
1304 and object_type = p_object_type
1305 and object_table_name = 'HZ_PARTIES'
1306 and relationship_code = p_relationship_code
1307 and sysdate between nvl(start_date, sysdate-1)
1308 and nvl(end_date, sysdate+1);
1309
1310 --bug#8508030
1311 Cursor get_bill_to_ship_to_address(p_instance_id number) IS
1312 SELECT bill_to_address,ship_to_address
1313 FROM CSI_IP_ACCOUNTS
1314 WHERE INSTANCE_PARTY_ID =
1315 (SELECT instance_party_id FROM CSI_I_PARTIES
1316 WHERE INSTANCE_ID=p_instance_id
1317 AND relationship_type_code='OWNER');
1318 --bug#8508030
1319
1320 BEGIN
1321
1322 savepoint CHANGE_ITEM_IB_OWNER;
1323
1324 if (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1325 fnd_log.STRING (fnd_log.level_procedure,
1326 'CSD.PLSQL.CSD_REPAIRS_UTIL.CHANGE_ITEM_IB_OWNER.BEGIN',
1327 'Enter - Change Item IB Owner');
1328 end if;
1329
1330 -- Initialize API return status to success
1331 x_return_status := FND_API.G_RET_STS_SUCCESS;
1332
1333 -- create a TCA relationship between the current owner and
1334 -- new owner, if desired
1335 if(p_create_tca_relation = fnd_api.g_true) then
1336
1337 -- get info for original owner
1338 Open c_hz_parties_info(p_current_owner_party_id);
1339 Fetch c_hz_parties_info
1340 into l_current_owner_party_type;
1341 Close c_hz_parties_info;
1342
1343 -- get info for new owner
1344 Open c_hz_parties_info(p_new_owner_party_id);
1345 Fetch c_hz_parties_info into l_new_owner_party_type;
1346 Close c_hz_parties_info;
1347
1348 -- get relationship type and code from custom hook procedure
1349 l_tca_rel_in_params.instance_id := p_instance_id;
1350 l_tca_rel_in_params.new_owner_party_id := p_new_owner_party_id;
1351 l_tca_rel_in_params.new_owner_account_id := p_new_owner_account_id;
1352 l_tca_rel_in_params.current_owner_party_id := p_current_owner_party_id;
1353 csd_ib_chown_cuhk.get_tca_rel_info (
1354 p_in_param => l_tca_rel_in_params,
1355 x_out_param => l_tca_rel_out_params
1356 );
1357 if NOT(l_tca_rel_out_params.return_status = FND_API.G_RET_STS_SUCCESS) then
1358 RAISE FND_API.G_EXC_ERROR;
1359 end if;
1360
1361 -- populate the relationship rec before calling API
1362 -- Assumption: the owners will always be from hz_parties
1363 l_relationship_rec.subject_id := p_new_owner_party_id;
1364 l_relationship_rec.subject_type := l_new_owner_party_type;
1365 l_relationship_rec.subject_table_name := 'HZ_PARTIES';
1366 l_relationship_rec.object_id := p_current_owner_party_id;
1367 l_relationship_rec.object_type := l_current_owner_party_type;
1368 l_relationship_rec.object_table_name := 'HZ_PARTIES';
1369 l_relationship_rec.relationship_code := l_tca_rel_out_params.relationship_code;
1370 l_relationship_rec.relationship_type := l_tca_rel_out_params.relationship_type;
1371 l_relationship_rec.start_date := SYSDATE;
1372 l_relationship_rec.created_by_module := 'CSDSR';
1373 l_relationship_rec.application_id := 516;
1374
1375 -- check if TCA relationship already exists
1376 Open c_tca_rel_count(l_relationship_rec.subject_id, l_relationship_rec.subject_type,
1377 l_relationship_rec.object_id, l_relationship_rec.object_type,
1378 l_relationship_rec.relationship_code);
1379 Fetch c_tca_rel_count
1380 into l_tca_rel_count;
1381 Close c_tca_rel_count;
1382
1383 -- only create the TCA relationship if one does not exist already
1384 if (l_tca_rel_count = 0 ) then
1385 -- create the TCA relation
1386 hz_relationship_v2pub.create_relationship(
1387 p_init_msg_list => fnd_api.g_false,
1388 p_relationship_rec => l_relationship_rec,
1389 x_relationship_id => x_tca_relation_id,
1390 x_party_id => l_rel_party_id,
1391 x_party_number => l_rel_party_number,
1392 x_return_status => x_return_status,
1393 x_msg_count => x_msg_count,
1394 x_msg_data => x_msg_data,
1395 p_create_org_contact => 'Y'
1396 );
1397 if NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1398 RAISE FND_API.G_EXC_ERROR;
1399 end if;
1400 end if; -- l_tca_rel_count = 0
1401 End If; -- p_create_tca_relation = true
1402
1403 -- Get the current instance info
1404 Open c_instance_party(p_instance_id);
1405 Fetch c_instance_party into l_csiip_inst_party_id,
1406 l_csiip_obj_ver_num;
1407 Close c_instance_party;
1408
1409 -- Get the current owner's account information
1410 Open c_instance_account(l_csiip_inst_party_id);
1411 Fetch c_instance_account into l_instance_account_id,
1412 l_inst_acct_obj_ver_num;
1413 Close c_instance_account;
1414
1415 -- Get additional information about the IB instancee
1416 Open c_instance_details(p_instance_id);
1417 Fetch c_instance_details into l_object_version_number;
1418 Close c_instance_details;
1419
1420
1421 -- Assign / Initialize values to the IB Rec type
1422 l_instance_rec.instance_id := p_instance_id;
1423 l_instance_rec.object_version_number := l_object_version_number;
1424
1425 l_party_tbl(1).instance_party_id := l_csiip_inst_party_id;
1426 l_party_tbl(1).instance_id := p_instance_id;
1427 l_party_tbl(1).party_source_table := 'HZ_PARTIES';
1428 l_party_tbl(1).party_id := p_new_owner_party_id;
1429 l_party_tbl(1).relationship_type_code := 'OWNER';
1430 l_party_tbl(1).contact_flag := 'N';
1431 l_party_tbl(1).object_version_number := l_csiip_obj_ver_num;
1432
1433 l_account_tbl(1).ip_account_id := l_instance_account_id;
1434 l_account_tbl(1).parent_tbl_index := 1;
1435 l_account_tbl(1).instance_party_id := l_csiip_inst_party_id;
1436 l_account_tbl(1).party_account_id := p_new_owner_account_id;
1437 l_account_tbl(1).relationship_type_code := 'OWNER';
1438 l_account_tbl(1).object_version_number := l_inst_acct_obj_ver_num;
1439
1440 --bug#8508030
1441 -- Get existing bill_to and ship_to address of the IB instancee
1442 Open get_bill_to_ship_to_address(p_instance_id);
1443 Fetch get_bill_to_ship_to_address into l_bill_to_address, l_ship_to_address;
1444 Close get_bill_to_ship_to_address;
1445
1446 --pass the original bill_to and ship_to address back. If this not pass,
1447 --it will set the bill to and shipp to address to null value
1448 l_account_tbl(1).bill_to_address := l_bill_to_address;
1449 l_account_tbl(1).ship_to_address := l_ship_to_address;
1450 --bug#8508030
1451
1452 l_txn_rec.transaction_date := sysdate;
1453 l_txn_rec.source_transaction_date := sysdate;
1454 l_txn_rec.transaction_type_id := 1;
1455
1456 -- Call the Update item instance API
1457 csi_item_instance_pub.update_item_instance
1458 (
1459 p_api_version => 1.0,
1460 p_commit => fnd_api.g_false,
1461 p_init_msg_list => fnd_api.g_true,
1462 p_validation_level => fnd_api.g_valid_level_full,
1463 p_instance_rec => l_instance_rec,
1464 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
1465 p_party_tbl => l_party_tbl,
1466 p_account_tbl => l_account_tbl,
1467 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
1468 p_org_assignments_tbl => l_org_assignments_tbl,
1469 p_asset_assignment_tbl => l_asset_assignment_tbl,
1470 p_txn_rec => l_txn_rec,
1471 x_instance_id_lst => x_instance_id_lst,
1472 x_return_status => x_return_status,
1473 x_msg_count => x_msg_count,
1474 x_msg_data => x_msg_data
1475 );
1476
1477 If NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
1478 RAISE FND_API.G_EXC_ERROR;
1479 End If;
1480
1481 COMMIT WORK;
1482
1483 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
1484 fnd_log.STRING (fnd_log.level_procedure,
1485 'CSD.PLSQL.CSD_REPAIRS_UTIL.CHANGE_ITEM_IB_OWNER.END',
1486 'Exit - Change Item IB Owner');
1487 End if;
1488
1489 EXCEPTION
1490 When FND_API.G_EXC_ERROR then
1491 Rollback To change_item_ib_owner;
1492 x_return_status := FND_API.G_RET_STS_ERROR ;
1493 FND_MSG_PUB.Count_And_Get
1494 (p_count => x_msg_count,
1495 p_data => x_msg_data );
1496
1497 When FND_API.G_EXC_UNEXPECTED_ERROR then
1498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1499 ROLLBACK TO change_item_ib_owner;
1500 FND_MSG_PUB.Count_And_Get
1501 ( p_count => x_msg_count,
1502 p_data => x_msg_data );
1503
1504 When OTHERS then
1505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1506 Rollback To change_item_ib_owner;
1507 If FND_MSG_PUB.Check_Msg_Level
1508 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
1509 FND_MSG_PUB.Add_Exc_Msg
1510 (G_PKG_NAME,
1511 l_api_name );
1512 End If;
1513 FND_MSG_PUB.Count_And_Get
1514 (p_count => x_msg_count,
1515 p_data => x_msg_data );
1516
1517 END CHANGE_ITEM_IB_OWNER;
1518
1519 --bug#5874431
1520 Procedure create_csd_index (p_sql_stmt IN varchar2,
1521 p_object IN varchar2
1522 ) is
1523
1524 lv_dummy1 VARCHAR2(2000);
1525 lv_dummy2 VARCHAR2(2000);
1526 lv_retval BOOLEAN;
1527 v_applsys_schema VARCHAR2(200);
1528 lv_prod_short_name VARCHAR2(30);
1529
1530 begin
1531 lv_retval := FND_INSTALLATION.GET_APP_INFO(
1532 'FND', lv_dummy1,lv_dummy2, v_applsys_schema);
1533
1534 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(512);
1535 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1536 application_short_name => lv_prod_short_name,
1537 statement_type => AD_DDL.CREATE_INDEX,
1538 statement => p_sql_stmt,
1539 object_name => p_object
1540 );
1541
1542 EXCEPTION
1543 WHEN OTHERS THEN
1544 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
1545 RAISE;
1546 end create_csd_index;
1547
1548 -- *******************************************************
1549 -- API Name: get_contract_resolve_by_date
1550 -- Type : Private
1551 -- Pre-Req : None
1552 -- Parameters:
1553 -- IN required?
1554 -- p_contract_line_id IN NUMBER, Y
1555 -- p_bus_proc_id IN NUMBER, Y
1556 -- p_severity_id IN NUMBER, Y
1557 -- p_request_date IN NUMBER, N - if not passed, use sysdate
1558 -- OUT
1559 -- x_return_status
1560 -- x_msg_count
1561 -- x_msg_data
1562 -- x_resolve_by_date OUT DATE
1563 --
1564 -- Version : Current version 1.0
1565 -- Initial Version 1.0
1566 --
1567 -- Description : rfieldma: 5355051
1568 -- calls oks_entitlements_pub.get_react_resolve_by
1569 -- return resolve_by_date
1570 --
1571 --
1572 --
1573 --
1574 -- ***********************************************************
1575 PROCEDURE get_contract_resolve_by_date
1576 (
1577 p_contract_line_id IN NUMBER,
1578 p_bus_proc_id IN NUMBER,
1579 p_severity_id IN NUMBER,
1580 p_request_date IN DATE := sysdate,
1581 x_return_status OUT NOCOPY VARCHAR2,
1582 x_msg_count OUT NOCOPY NUMBER,
1583 x_msg_data OUT NOCOPY VARCHAR2,
1584 x_resolve_by_date OUT NOCOPY DATE
1585 ) IS
1586 -- define constants
1587 lc_api_name CONSTANT VARCHAR2(50) := 'csd_repairs_util.get_contract_resolve_by_date';
1588
1589 -- define variables
1590 l_server_timezone_id NUMBER;
1591
1592 subtype r_input_rec is oks_entitlements_pub.grt_inp_rec_type;
1593 l_inp_rec r_input_rec;
1594
1595 subtype r_react_rec is oks_entitlements_pub.rcn_rsn_rec_type;
1596 l_react_rec r_react_rec;
1597
1598 subtype r_resolve_rec is oks_entitlements_pub.rcn_rsn_rec_type;
1599 l_resolve_rec r_resolve_rec;
1600
1601
1602 BEGIN
1603 SAVEPOINT get_contract_resolve_by_date;
1604 x_resolve_by_date := null; -- default this field to null before logic begins
1605
1606 -- call PROCEDURE Check_Reqd_Param (p_param_value IN NUMBER,
1607 -- p_param_name IN VARCHAR2,
1608 -- p_api_name IN VARCHAR2
1609 -- ) to check p_contract_line_id, p_bus_proc_id, p_severity
1610
1611 Check_Reqd_Param(p_contract_line_id,
1612 'p_contract_line_id',
1613 lc_api_name);
1614
1615 Check_Reqd_Param(p_bus_proc_id,
1616 'p_bus_proc_id',
1617 lc_api_name);
1618
1619 Check_Reqd_Param(p_severity_id,
1620 'p_severity_id',
1621 lc_api_name);
1622
1623 -- profile option for server_timezone_id must be set
1624 l_server_timezone_id := fnd_profile.value('SERVER_TIMEZONE_ID');
1625 IF (NVL(l_server_timezone_id,Fnd_Api.G_MISS_NUM) = Fnd_Api.G_MISS_NUM) THEN
1626 Fnd_Message.SET_NAME('CSD','CSD_CANNOT_GET_PROFILE_VALUE');
1627 Fnd_Message.SET_TOKEN('PROFILE',get_user_profile_option_name('SERVER_TIMEZONE_ID'));
1628 Fnd_Msg_Pub.ADD;
1629 RAISE Fnd_Api.G_EXC_ERROR;
1630 END IF;
1631
1632 l_inp_rec.contract_line_id := p_contract_line_id ;
1633 l_inp_rec.business_process_id := p_bus_proc_id ;
1634 l_inp_rec.severity_id := p_severity_id ;
1635 l_inp_rec.request_date := p_request_date ;
1636 l_inp_rec.time_zone_id := l_server_timezone_id ;
1637 l_inp_rec.category_rcn_rsn := OKS_ENTITLEMENTS_PUB.G_RESOLUTION;
1638 l_inp_rec.compute_option := OKS_ENTITLEMENTS_PUB.G_BEST ;
1639 l_inp_rec.dates_in_input_tz := 'N' ;
1640
1641 oks_entitlements_pub.get_react_resolve_by_time(
1642 p_api_version => 1.0,
1643 p_init_msg_list => FND_API.G_TRUE,
1644 p_inp_rec => l_inp_rec,
1645 x_return_status => x_return_status,
1646 x_msg_count => x_msg_count,
1647 x_msg_data => x_msg_data,
1648 x_react_rec => l_react_rec,
1649 x_resolve_rec => l_resolve_rec);
1650
1651 IF (x_return_status = Fnd_Api.G_RET_STS_ERROR) THEN
1652 RAISE Fnd_Api.G_EXC_ERROR ;
1653 ELSIF (x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR) THEN
1654 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR ;
1655 END IF;
1656
1657 x_resolve_by_date := l_resolve_rec.by_date_end;
1658
1659 EXCEPTION
1660 When FND_API.G_EXC_ERROR then
1661 Rollback To get_contract_resolve_by_date;
1662 x_return_status := FND_API.G_RET_STS_ERROR ;
1663 FND_MSG_PUB.Count_And_Get
1664 (p_count => x_msg_count,
1665 p_data => x_msg_data );
1666
1667 When FND_API.G_EXC_UNEXPECTED_ERROR then
1668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1669 ROLLBACK TO get_contract_resolve_by_date;
1670 FND_MSG_PUB.Count_And_Get
1671 ( p_count => x_msg_count,
1672 p_data => x_msg_data );
1673
1674 When OTHERS then
1675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1676 Rollback To get_contract_resolve_by_date;
1677 If FND_MSG_PUB.Check_Msg_Level
1678 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
1679 FND_MSG_PUB.Add_Exc_Msg
1680 (G_PKG_NAME,
1681 lc_api_name );
1682 End If;
1683 FND_MSG_PUB.Count_And_Get
1684 (p_count => x_msg_count,
1685 p_data => x_msg_data );
1686
1687 END get_contract_resolve_by_date;
1688
1689 -- *******************************************************
1690 -- API Name: get_user_profile_option_name
1691 -- Type : Private
1692 -- Pre-Req : None
1693 -- Parameters:
1694 -- IN required?
1695 -- p_profile_name IN VARHAR2 Y
1696 --
1697 -- Version : Current version 1.0
1698 -- Initial Version 1.0
1699 --
1700 -- Description : rfieldma: utility function
1701 -- returns language specific user profile
1702 -- option name
1703 --
1704 --
1705 --
1706 --
1707 -- ***********************************************************
1708 FUNCTION get_user_profile_option_name
1709 (
1710 p_profile_name IN VARCHAR2
1711 ) RETURN VARCHAR2
1712 IS
1713 -- define variables
1714 l_user_prof_name VARCHAR2(240);
1715
1716 -- define cursors
1717 CURSOR c_user_prof_name(p_profile_name VARCHAR2) IS
1718 SELECT user_profile_option_name
1719 FROM fnd_profile_options_tl
1720 WHERE profile_option_name = p_profile_name
1721 AND language = userenv('lang');
1722
1723 BEGIN
1724 OPEN c_user_prof_name(p_profile_name);
1725 FETCH c_user_prof_name
1726 INTO l_user_prof_name;
1727 CLOSE c_user_prof_name;
1728
1729 RETURN l_user_prof_name;
1730
1731 END get_user_profile_option_name;
1732
1733 -- bug#7497790, 12.1 FP,subhat.
1734 -- ***************************************************
1735 -- Automatically update the RO status when the item is received.
1736 -- The API receives the Repair line id and updates the RO status if the conditions are met.
1737 -- Parameters:
1738 -- p_event : Specify the event that is calling this program. Based on the event, the program logic might change.
1739 -- p_reason_code: The reason code for the status change defaulted to null
1740 -- p_comments: The comments for the flow status, defaulted to null
1741 -- p_validation_level: validation level for the routine. Pass fnd_api.g_valid_level_full to get the messages from the API
1742 -- pass fnd_api.g_valid_level_none will ignore all error messages and return success always. The error messages
1743 -- will be logged in the fnd_log_messages if logging is enabled
1744 --*****************************************************
1745
1746 procedure auto_update_ro_status(
1747 p_api_version in number,
1748 p_commit in varchar2,
1749 p_init_msg_list in varchar2,
1750 p_repair_line_id in number,
1751 x_return_status out nocopy varchar2,
1752 x_msg_count out nocopy number,
1753 x_msg_data out nocopy varchar2,
1754 p_event in varchar2,
1755 p_reason_code in varchar2 default null,
1756 p_comments in varchar2 default null,
1757 p_validation_level in number)
1758 is
1759 l_from_flow_status_id number;
1760 l_to_flow_status_id number;
1761 x_object_version_number number;
1762 l_repair_type_id number;
1763 l_object_version_number number;
1764 lc_log_level number := fnd_log.g_current_runtime_level;
1765 lc_procedure_level number := fnd_log.level_procedure;
1766 lc_mod_name varchar2(100) := 'csd.plsql.csd_repairs_util.auto_update_ro_status';
1767 lc_api_version_number number := 1.0;
1768 lc_api_name varchar2(60) := 'auto_update_ro_status';
1769 l_un_rcvd_lines_exists varchar2(3);
1770
1771 begin
1772
1773 -- standard API compatibility check.
1774 IF NOT Fnd_Api.Compatible_API_Call
1775 (lc_api_version_number,
1776 p_api_version,
1777 lc_api_name,
1778 G_PKG_NAME)
1779 THEN
1780 RAISE Fnd_Api.G_EXC_ERROR;
1781 END IF;
1782
1783 IF fnd_api.to_boolean(p_init_msg_list)
1784 THEN
1785 -- initialize message list
1786 fnd_msg_pub.initialize;
1787 END IF;
1788
1789 if lc_log_level >= lc_procedure_level then
1790 fnd_log.string(lc_log_level,lc_mod_name,'Begin auto_update_ro_status');
1791 end if;
1792
1793 -- set the return status
1794 x_return_status := fnd_api.g_ret_sts_success;
1795 -- check if the profile to enable the auto update of RO status is set to yes.
1796
1797 if p_event = 'RECEIVE' then
1798 -- check if all the RMA -- CUST_PROD and RMA-EXCHANGE combination is received.
1799 -- only update the RO status if all RMA -- CUST_PROD combination is received
1800 --
1801 if nvl(fnd_profile.value('CSD_ENABLE_AUTO_UPD_RO_STAT'),'N') = 'N'
1802 then
1803 if lc_log_level >= lc_procedure_level then
1804 fnd_log.string(lc_log_level,lc_mod_name,'Profile CSD: Enable Auto Update of Repair
1805 Order Status upon Receiving is not set to yes.');
1806 end if;
1807 if p_validation_level = fnd_api.g_valid_level_full then
1808 -- 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.
1809 null;
1810 else
1811 return; -- return to the caller.
1812 end if;
1813 end if;
1814
1815 begin
1816 select 'x'
1817 into l_un_rcvd_lines_exists
1818 from (
1819 select 'x'
1820 from csd_product_transactions cpt
1821 where cpt.repair_line_id = p_repair_line_id and
1822 cpt.action_type = 'RMA' and
1823 cpt.action_code in ('CUST_PROD','EXCHANGE') and
1824 cpt.prod_txn_status <> 'RECEIVED'
1825 and rownum < 2
1826 ) where rownum < 2 ;
1827 exception
1828 when no_data_found then
1829 l_un_rcvd_lines_exists := null;
1830 end;
1831
1832 if l_un_rcvd_lines_exists is null then
1833
1834 l_to_flow_status_id := fnd_profile.value('CSD_DEF_RO_STAT_FR_RCV');
1835 if l_to_flow_status_id is null then
1836 if lc_log_level >= lc_procedure_level then
1837 fnd_log.string(lc_log_level,lc_mod_name,'Profile CSD: Default Repair Order Status After
1838 Receving is not set');
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 for the caller API.
1842 null;
1843 else
1844 return; -- exit the procedure. With the success status.
1845 end if;
1846 end if;
1847
1848 -- get the 'from' flow status id.
1849 --
1850 begin
1851 select distinct cr.flow_status_id,
1852 cr.repair_type_id,
1853 cr.object_version_number
1854 into l_from_flow_status_id,
1855 l_repair_type_id,
1856 l_object_version_number
1857 from csd_repairs cr
1858 where cr.repair_line_id = p_repair_line_id;
1859 exception
1860 when no_data_found then
1861 -- should never get in here.
1862 null;
1863 end;
1864
1865 if l_to_flow_status_id = l_from_flow_status_id then
1866 -- to and from are same. Do not update.
1867 if lc_log_level >= lc_procedure_level then
1868 fnd_log.string(lc_log_level,lc_mod_name,'the new status is same as the old status. Do not update the status');
1869 end if;
1870 if p_validation_level = fnd_api.g_valid_level_full then
1871 -- to do: set a message and raise an error.
1872 null;
1873 else
1874 return;
1875 end if;
1876 end if;
1877 -- call the update flow status API to update the RO status.
1878 if lc_log_level >= lc_procedure_level then
1879 fnd_log.string(lc_log_level,lc_mod_name,'calling csd_repairs_pvt.update_flow_status API');
1880 end if;
1881
1882 csd_repairs_pvt.update_flow_status(p_api_version => 1,
1883 p_commit => fnd_api.g_false,
1884 p_init_msg_list => fnd_api.g_false,
1885 p_validation_level => fnd_api.g_valid_level_full,
1886 x_return_status => x_return_status,
1887 x_msg_count => x_msg_count,
1888 x_msg_data => x_msg_data,
1889 p_repair_line_id => p_repair_line_id,
1890 p_repair_type_id => l_repair_type_id,
1891 p_from_flow_status_id => l_from_flow_status_id,
1892 p_to_flow_status_id => l_to_flow_status_id,
1893 p_reason_code => p_reason_code,
1894 p_comments => p_comments,
1895 p_check_access_flag => 'Y',
1896 p_object_version_number => l_object_version_number,
1897 x_object_version_number => x_object_version_number );
1898 if x_return_status <> fnd_api.g_ret_sts_success then
1899 if lc_log_level >= lc_procedure_level then
1900 fnd_log.string(lc_log_level,lc_mod_name,'Error in csd_repairs_pvt.update_flow_status
1901 ['||x_msg_data||']');
1902 end if;
1903
1904 if p_validation_level = fnd_api.g_valid_level_full then
1905 -- to do: set a message and raise an error;
1906 null;
1907 else
1908 -- set the return to status to success.
1909 x_return_status := fnd_api.g_ret_sts_success;
1910 return;
1911 end if;
1912 end if;
1913
1914 end if; -- l_all_lines_rcvd
1915 end if; -- p_event = 'RECEIVE'
1916
1917 if p_commit = fnd_api.g_true then
1918 commit;
1919 end if;
1920
1921 exception
1922 when fnd_api.g_exc_error then
1923 -- raising a error may not be a good idea, as it can potentially roll back
1924 -- entire transaction. For now, we can prefer to do nothing.
1925 x_return_status := fnd_api.g_ret_sts_success;
1926 null;
1927 end auto_update_ro_status;
1928 -- end bug#7497790, 12.1 FP, subhat.
1929
1930 -- *******************************************************
1931 -- API Name: default_ro_attrs_from_rule
1932 -- Type : Private
1933 -- Pre-Req : None
1934 -- Parameters:
1935 -- IN OUT required?
1936 -- px_repln_rec in out nocopy CSD_REPAIRS_PUB.REPLN_REC_TYPE Y
1937 --
1938 -- Version : Current version 1.0
1939 -- Initial Version 1.0
1940 --
1941 -- Description : swai: utility procedure added for bug 7657379
1942 -- defaults Repair Order attributes from defaulting
1943 -- rules into px_repln_rec if the field is not already
1944 -- set. Uses existing values in px_repln_rec to populate
1945 -- the rule input rec for defaulting rules.
1946 -- Currently, the following fields are defaulted if
1947 -- they are passed in as G_MISS:
1948 -- Inventory Org
1949 -- Repair Org
1950 -- Repair Owner
1951 -- Repair Priority
1952 -- Repair Type
1953 -- Note that the profile option value will be returned
1954 -- if no applicable rules exist. For Repair Types,
1955 -- the profile value returned in for profile
1956 -- 'CSD_DEFAULT_REPAIR_TYPE'
1957 -- ***********************************************************
1958 PROCEDURE DEFAULT_RO_ATTRS_FROM_RULE (
1959 p_api_version in number,
1960 p_commit in varchar2,
1961 p_init_msg_list in varchar2,
1962 px_repln_rec in out nocopy csd_repairs_pub.repln_rec_type,
1963 x_return_status out nocopy varchar2,
1964 x_msg_count out nocopy number,
1965 x_msg_data out nocopy varchar2)
1966 IS
1967 cursor c_def_wip (p_repair_type_id number) is
1968 select distinct repair_mode, nvl(auto_process_rma,'N')
1969 from csd_repair_types_vl
1970 where repair_type_id = p_repair_type_id;
1971
1972 CURSOR c_get_sr_info(p_incident_id number) is
1973 select customer_id,
1974 account_id,
1975 bill_to_site_use_id,
1976 ship_to_site_use_id,
1977 inventory_item_id,
1978 category_id,
1979 contract_id,
1980 problem_code,
1981 customer_product_id
1982 from CS_INCIDENTS_ALL_VL
1983 where incident_id = p_incident_id;
1984
1985 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
1986 l_attr_type VARCHAR2(25);
1987 l_attr_code VARCHAR2(25);
1988 l_default_val_num NUMBER;
1989 l_default_val_str VARCHAR(30);
1990 l_default_rule_id NUMBER;
1991 l_inv_org NUMBER;
1992 l_repair_org NUMBER; -- repair org id
1993 l_repair_type_id NUMBER; -- repair type id
1994 l_repair_mode VARCHAR2(30) := ''; -- repair mode display name
1995 l_auto_process_rma VARCHAR2(30) := '';
1996 lc_log_level number := fnd_log.g_current_runtime_level;
1997 lc_procedure_level number := fnd_log.level_procedure;
1998 lc_mod_name varchar2(100) := 'csd.plsql.csd_repairs_util.default_ro_attrs_from_rule';
1999 lc_api_version_number number := 1.0;
2000 lc_api_name varchar2(60) := 'default_ro_attrs_from_rule';
2001
2002 BEGIN
2003 -- standard API compatibility check.
2004 if NOT FND_API.Compatible_API_Call
2005 (lc_api_version_number,
2006 p_api_version,
2007 lc_api_name,
2008 G_PKG_NAME)
2009 then
2010 RAISE Fnd_Api.G_EXC_ERROR;
2011 end if;
2012
2013 if FND_API.to_boolean(p_init_msg_list) then
2014 -- initialize message list
2015 fnd_msg_pub.initialize;
2016 end if;
2017
2018 if lc_log_level >= lc_procedure_level then
2019 fnd_log.string(lc_log_level,lc_mod_name,'Begin default_ro_attrs_from_rule');
2020 end if;
2021
2022 -- set the return status
2023 x_return_status := fnd_api.g_ret_sts_success;
2024
2025 -- Assume SR Incident Id is available to get info for defaulting RO attributes
2026 open c_get_sr_info(px_repln_rec.incident_id);
2027 fetch c_get_sr_info into
2028 l_rule_input_rec.SR_CUSTOMER_ID,
2029 l_rule_input_rec.SR_CUSTOMER_ACCOUNT_ID,
2030 l_rule_input_rec.SR_BILL_TO_SITE_USE_ID,
2031 l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID,
2032 l_rule_input_rec.SR_ITEM_ID,
2033 l_rule_input_rec.SR_ITEM_CATEGORY_ID,
2034 l_rule_input_rec.SR_CONTRACT_ID,
2035 l_rule_input_rec.SR_PROBLEM_CODE,
2036 l_rule_input_rec.SR_INSTANCE_ID;
2037 close c_get_sr_info;
2038
2039 l_rule_input_rec.RO_ITEM_ID := px_repln_rec.INVENTORY_ITEM_ID;
2040
2041 /****************************** DEFAULT INVENTORY ORG ******************************/
2042 if (px_repln_rec.inventory_org_id = FND_API.G_MISS_NUM) then
2043 l_attr_type := 'CSD_DEF_ENTITY_ATTR_RO';
2044 l_attr_code := 'INV_ORG';
2045 l_default_val_num := null;
2046 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2047 p_api_version_number => 1.0,
2048 p_init_msg_list => fnd_api.g_false,
2049 p_commit => fnd_api.g_false,
2050 p_validation_level => fnd_api.g_valid_level_full,
2051 p_entity_attribute_type => l_attr_type,
2052 p_entity_attribute_code => l_attr_code,
2053 p_rule_input_rec => l_rule_input_rec,
2054 x_default_value => l_default_val_num,
2055 x_rule_id => l_default_rule_id,
2056 x_return_status => x_return_status,
2057 x_msg_count => x_msg_count,
2058 x_msg_data => x_msg_data
2059 );
2060
2061 if (x_return_status = fnd_api.g_ret_sts_success) then
2062 if (l_default_val_num is not null) then
2063 l_inv_org := l_default_val_num;
2064 else
2065 l_inv_org := to_number(fnd_profile.value('CSD_DEF_REP_INV_ORG'));
2066 end if;
2067 else
2068 RAISE FND_API.G_EXC_ERROR;
2069 end if;
2070
2071 if l_inv_org is not null then
2072 px_repln_rec.inventory_org_id := l_inv_org;
2073 end if;
2074 end if;
2075
2076 /****************************** DEFAULT REPAIR ORG ******************************/
2077 if (px_repln_rec.resource_group = FND_API.G_MISS_NUM) then
2078 l_attr_type := 'CSD_DEF_ENTITY_ATTR_RO';
2079 l_attr_code := 'REPAIR_ORG';
2080 l_default_val_num := null;
2081 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2082 p_api_version_number => 1.0,
2083 p_init_msg_list => fnd_api.g_false,
2084 p_commit => fnd_api.g_false,
2085 p_validation_level => fnd_api.g_valid_level_full,
2086 p_entity_attribute_type => l_attr_type,
2087 p_entity_attribute_code => l_attr_code,
2088 p_rule_input_rec => l_rule_input_rec,
2089 x_default_value => l_default_val_num,
2090 x_rule_id => l_default_rule_id,
2091 x_return_status => x_return_status,
2092 x_msg_count => x_msg_count,
2093 x_msg_data => x_msg_data
2094 );
2095 if (x_return_status = fnd_api.g_ret_sts_success) then
2096 if (l_default_val_num is not null) then
2097 l_repair_org := l_default_val_num;
2098 else
2099 l_repair_org := to_number(fnd_profile.value('CSD_DEFAULT_REPAIR_ORG'));
2100 end if;
2101 else
2102 RAISE FND_API.G_EXC_ERROR;
2103 end if;
2104
2105 if l_repair_org is not null then
2106 px_repln_rec.resource_group := l_repair_org;
2107 end if;
2108 end if;
2109
2110 /****************************** DEFAULT REPAIR OWNER ******************************/
2111 if (px_repln_rec.resource_id = FND_API.G_MISS_NUM) then
2112 l_attr_type := 'CSD_DEF_ENTITY_ATTR_RO';
2113 l_attr_code := 'REPAIR_OWNER';
2114 l_default_val_num := null;
2115 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2116 p_api_version_number => 1.0,
2117 p_init_msg_list => fnd_api.g_false,
2118 p_commit => fnd_api.g_false,
2119 p_validation_level => fnd_api.g_valid_level_full,
2120 p_entity_attribute_type => l_attr_type,
2121 p_entity_attribute_code => l_attr_code,
2122 p_rule_input_rec => l_rule_input_rec,
2123 x_default_value => l_default_val_num,
2124 x_rule_id => l_default_rule_id,
2125 x_return_status => x_return_status,
2126 x_msg_count => x_msg_count,
2127 x_msg_data => x_msg_data
2128 );
2129 if (x_return_status = fnd_api.g_ret_sts_success) then
2130 if (l_default_val_num is not null) then
2131 px_repln_rec.resource_id := l_default_val_num;
2132 end if;
2133 else
2134 RAISE FND_API.G_EXC_ERROR;
2135 end if;
2136 end if;
2137
2138 /****************************** DEFAULT REPAIR PRIORITY ******************************/
2139 if (px_repln_rec.ro_priority_code = FND_API.G_MISS_CHAR) then
2140 l_attr_type := 'CSD_DEF_ENTITY_ATTR_RO';
2141 l_attr_code := 'REPAIR_PRIORITY';
2142 l_default_val_num := null;
2143 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2144 p_api_version_number => 1.0,
2145 p_init_msg_list => fnd_api.g_false,
2146 p_commit => fnd_api.g_false,
2147 p_validation_level => fnd_api.g_valid_level_full,
2148 p_entity_attribute_type => l_attr_type,
2149 p_entity_attribute_code => l_attr_code,
2150 p_rule_input_rec => l_rule_input_rec,
2151 x_default_value => l_default_val_str,
2152 x_rule_id => l_default_rule_id,
2153 x_return_status => x_return_status,
2154 x_msg_count => x_msg_count,
2155 x_msg_data => x_msg_data
2156 );
2157 if (x_return_status = fnd_api.g_ret_sts_success) then
2158 if (l_default_val_str is not null) then
2159 px_repln_rec.ro_priority_code := l_default_val_str;
2160 end if;
2161 else
2162 RAISE FND_API.G_EXC_ERROR;
2163 end if;
2164 end if;
2165
2166 /****************************** DEFAULT REPAIR TYPE ******************************/
2167 if (px_repln_rec.repair_type_id = FND_API.G_MISS_NUM) then
2168 l_attr_type := 'CSD_DEF_ENTITY_ATTR_RO';
2169 l_attr_code := 'REPAIR_TYPE';
2170 l_default_val_num := null;
2171 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2172 p_api_version_number => 1.0,
2173 p_init_msg_list => fnd_api.g_false,
2174 p_commit => fnd_api.g_false,
2175 p_validation_level => fnd_api.g_valid_level_full,
2176 p_entity_attribute_type => l_attr_type,
2177 p_entity_attribute_code => l_attr_code,
2178 p_rule_input_rec => l_rule_input_rec,
2179 x_default_value => l_default_val_num,
2180 x_rule_id => l_default_rule_id,
2181 x_return_status => x_return_status,
2182 x_msg_count => x_msg_count,
2183 x_msg_data => x_msg_data
2184 );
2185
2186 if (x_return_status = fnd_api.g_ret_sts_success) then
2187 if (l_default_val_num is not null) then
2188 l_repair_type_id := l_default_val_num;
2189 else
2190 l_repair_type_id := to_number(fnd_profile.value('CSD_DEFAULT_REPAIR_TYPE'));
2191 end if;
2192
2193 if l_repair_type_id is not null then
2194 open c_def_wip (l_repair_type_id);
2195 fetch c_def_wip into l_repair_mode, l_auto_process_rma;
2196 close c_def_wip;
2197
2198 px_repln_rec.repair_type_id := l_repair_type_id;
2199
2200 -- repair mode must be the same as what is defined for the
2201 -- repair type, so override any value that was already there
2202 px_repln_rec.repair_mode := l_repair_mode;
2203
2204 -- allow user to override auto process default, so only default
2205 -- if no value specified.
2206 if (px_repln_rec.auto_process_rma = FND_API.G_MISS_NUM) then
2207 px_repln_rec.auto_process_rma := l_auto_process_rma;
2208 end if;
2209 end if;
2210 else
2211 RAISE FND_API.G_EXC_ERROR;
2212 end if;
2213 end if;
2214
2215 if p_commit = fnd_api.g_true then
2216 commit;
2217 end if;
2218
2219 EXCEPTION
2220 When FND_API.G_EXC_ERROR then
2221 x_return_status := FND_API.G_RET_STS_ERROR ;
2222 FND_MSG_PUB.Count_And_Get
2223 (p_count => x_msg_count,
2224 p_data => x_msg_data );
2225
2226 When FND_API.G_EXC_UNEXPECTED_ERROR then
2227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2228 FND_MSG_PUB.Count_And_Get
2229 ( p_count => x_msg_count,
2230 p_data => x_msg_data );
2231
2232 When OTHERS then
2233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2234 If FND_MSG_PUB.Check_Msg_Level
2235 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)then
2236 FND_MSG_PUB.Add_Exc_Msg
2237 (G_PKG_NAME,
2238 lc_api_name );
2239 End If;
2240 FND_MSG_PUB.Count_And_Get
2241 (p_count => x_msg_count,
2242 p_data => x_msg_data );
2243 END DEFAULT_RO_ATTRS_FROM_RULE;
2244
2245 /**************************************************************************/
2246 /* Procedure: create_requisition */
2247 /* Description: This will insert the records into requisition interface */
2248 /* table and subsequently launches the concurrent request to */
2249 /* create the requisitions.If the concurrent program is */
2250 /* launched successfully then the concurrent request id is */
2251 /* is returned. */
2252 /* Created by: subhat - 02-09-09 */
2253 /**************************************************************************/
2254
2255 PROCEDURE create_requisition
2256 (
2257 p_api_version_number IN NUMBER,
2258 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2259 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2260 x_return_status OUT NOCOPY VARCHAR2,
2261 x_msg_count OUT NOCOPY NUMBER,
2262 x_msg_data OUT NOCOPY VARCHAR2,
2263 p_wip_entity_id_tbl IN JTF_NUMBER_TABLE,
2264 p_quantity_tbl IN JTF_NUMBER_TABLE,
2265 p_uom_code_tbl IN VARCHAR2_TABLE_100,
2266 p_op_seq_num_tbl IN JTF_NUMBER_TABLE,
2267 p_item_id_tbl IN JTF_NUMBER_TABLE,
2268 p_item_description_tbl IN VARCHAR2_TABLE_100,
2269 p_organization_id IN NUMBER,
2270 x_request_id OUT NOCOPY NUMBER,
2271 p_repair_line_id IN NUMBER DEFAULT NULL
2272 ) IS
2273
2274 lc_api_version CONSTANT NUMBER default 1.0;
2275 lc_api_name CONSTANT VARCHAR2(30) := 'CREATE_REQUISITION';
2276 lc_mod_name CONSTANT VARCHAR2(40) := 'csd_repairs_util.create_requisition';
2277
2278 l_person_id NUMBER;
2279 l_material_account JTF_NUMBER_TABLE;
2280 l_material_variance_account JTF_NUMBER_TABLE;
2281 l_currency VARCHAR2(30);
2282 l_project_id JTF_NUMBER_TABLE;
2283 l_task_id JTF_NUMBER_TABLE;
2284 l_location_id JTF_NUMBER_TABLE;
2285 l_ou_id NUMBER;
2286
2287 l_previous_wip_entity NUMBER;
2288 l_previous_op_seq_num NUMBER;
2289 l_needby_date DATE;
2290 l_dummy NUMBER;
2291 l_wip_entity_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2292 l_item_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2293 l_item_description_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2294 l_uom_code_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2295 l_quantity_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2296 l_source_org_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2297 l_source_subinv_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2298 l_op_seq_num_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2299 l_mtl_txn_dtls_tab csd_hv_wip_job_pvt.mtl_txn_dtls_tbl_type;
2300 l_mtl_txn_dtls_upd csd_hv_wip_job_pvt.mtl_txn_dtls_tbl_type;
2301
2302 x_use_custom_sourcing BOOLEAN := FALSE;
2303 x_requisition_type JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2304 x_op_created VARCHAR2(1);
2305
2306 begin
2307 savepoint create_requisition;
2308 -- standard check.
2309 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2310 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Begin Create_requisition');
2311 END IF;
2312
2313 if not fnd_api.compatible_api_call(
2314 lc_api_version,
2315 p_api_version_number,
2316 lc_api_name,
2317 g_pkg_name) then
2318 raise fnd_api.g_exc_unexpected_error;
2319 end if;
2320
2321 IF fnd_api.to_boolean(p_init_msg_list) THEN
2322 fnd_msg_pub.initialize;
2323 END IF;
2324
2325 -- initialize return status.
2326 x_return_status := FND_API.g_ret_sts_success;
2327
2328 -- get the person_id for the current user.
2329 select employee_id
2330 into l_person_id
2331 from fnd_user
2332 where user_id = fnd_global.user_id;
2333
2334 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2335 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2336 lc_mod_name,
2337 'Fetched the person_id '||l_person_id||' for user_id '||fnd_global.user_id);
2338 END IF;
2339
2340 -- check if the need by date profile is set up properly. The requisition import program
2341 -- needs the need by date for MRP or INV planned items. Right now we assume that all items
2342 -- are planned.
2343 -- the need by date can possibly be left null only when INVENTORY_PLANNED_FLAG = 6 and
2344 -- MRP_PLANNED_FLAG = 6 in mtl_system_items_b for a particular item.
2345 l_dummy := to_number(fnd_profile.value('CSD_REQUISITION_LEAD_TIME'));
2346
2347 if nvl(l_dummy,-1) <= -1 then
2348 FND_MESSAGE.SET_NAME('CSD','CSD_REQ_LEAD_TIME_NOT_SET');
2349 FND_MSG_PUB.ADD;
2350 raise fnd_api.g_exc_error;
2351 end if;
2352
2353 l_needby_date := sysdate + l_dummy;
2354 --
2355 -- initialize the collections used for bulk binding.
2356 l_project_id := JTF_NUMBER_TABLE();
2357 l_task_id := JTF_NUMBER_TABLE();
2358 l_material_account := JTF_NUMBER_TABLE();
2359 l_material_variance_account:= JTF_NUMBER_TABLE();
2360 l_location_id := JTF_NUMBER_TABLE();
2361
2362 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2363 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Calling the user hook procedure csd_logistics_cuhk.source_parts');
2364 END IF;
2365
2366 csd_logistics_cuhk.source_parts
2367 (p_repair_line_id => p_repair_line_id,
2368 p_wip_entity_id => p_wip_entity_id_tbl,
2369 p_item_id => p_item_id_tbl,
2370 p_quantity => p_quantity_tbl,
2371 p_requesting_org => p_organization_id,
2372 p_op_seq_num => p_op_seq_num_tbl,
2373 x_use_custom_sourcing => x_use_custom_sourcing,
2374 x_wip_entity_id => l_wip_entity_id_tbl,
2375 x_item_id => l_item_id_tbl,
2376 x_item_desc => l_item_description_tbl,
2377 x_uom_code => l_uom_code_tbl,
2378 x_quantity => l_quantity_tbl,
2379 x_source_org_id => l_source_org_id_tbl,
2380 x_source_subinventory => l_source_subinv_tbl,
2381 x_op_seq_num => l_op_seq_num_tbl,
2382 x_requisition_type => x_requisition_type,
2383 x_return_status => x_return_status,
2384 x_msg_data => x_msg_data,
2385 x_msg_count => x_msg_count
2386 );
2387
2388 IF x_return_status <> fnd_api.g_ret_sts_success
2389 THEN
2390 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2391 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'The custom procedure raised an error status, exiting the API.');
2392 END IF;
2393 RAISE fnd_api.g_exc_error;
2394 END IF;
2395
2396 -- if the user hook doesn't implement sourcing, we will copy the input parameters into the l_*
2397 -- variables.
2398 IF NOT NVL(x_use_custom_sourcing,FALSE)
2399 THEN
2400
2401 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2402 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Copying the input parameters into the local variables');
2403 END IF;
2404
2405 l_wip_entity_id_tbl := JTF_NUMBER_TABLE();
2406 l_item_id_tbl := JTF_NUMBER_TABLE();
2407 l_item_description_tbl := JTF_VARCHAR2_TABLE_100();
2408 l_uom_code_tbl := JTF_VARCHAR2_TABLE_100();
2409 l_quantity_tbl := JTF_NUMBER_TABLE();
2410 l_source_org_id_tbl := JTF_NUMBER_TABLE();
2411 l_source_subinv_tbl := JTF_VARCHAR2_TABLE_100();
2412 l_op_seq_num_tbl := JTF_NUMBER_TABLE();
2413 x_requisition_type := JTF_VARCHAR2_TABLE_100();
2414
2415 l_wip_entity_id_tbl.EXTEND(p_wip_entity_id_tbl.COUNT);
2416 l_item_id_tbl.EXTEND(p_wip_entity_id_tbl.COUNT);
2417 l_item_description_tbl.EXTEND(p_wip_entity_id_tbl.COUNT);
2418 l_uom_code_tbl.EXTEND(p_wip_entity_id_tbl.COUNT);
2419 l_quantity_tbl.EXTEND(p_wip_entity_id_tbl.COUNT);
2420 l_source_org_id_tbl.EXTEND(p_wip_entity_id_tbl.COUNT);
2421 l_source_subinv_tbl.EXTEND(p_wip_entity_id_tbl.COUNT);
2422 l_op_seq_num_tbl.EXTEND(p_wip_entity_id_tbl.COUNT);
2423 x_requisition_type.EXTEND(p_wip_entity_id_tbl.COUNT);
2424
2425 FOR i in 1 ..p_wip_entity_id_tbl.COUNT
2426 LOOP
2427 l_wip_entity_id_tbl(i) := p_wip_entity_id_tbl(i);
2428 l_item_id_tbl(i) := p_item_id_tbl(i);
2429 l_item_description_tbl(i):= p_item_description_tbl(i);
2430 l_uom_code_tbl(i) := p_uom_code_tbl(i);
2431 l_quantity_tbl(i) := p_quantity_tbl(i);
2432 l_source_org_id_tbl(i) := NULL;
2433 l_source_subinv_tbl(i) := NULL;
2434 l_op_seq_num_tbl(i) := p_op_seq_num_tbl(i);
2435 x_requisition_type(i) := 'V';
2436 END LOOP;
2437
2438 END IF;
2439 -- populate the material variance and project details.
2440 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2441 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2442 lc_mod_name,
2443 'Fetching material variance and project details along with deliver to location');
2444 END IF;
2445 for i in 1 ..l_wip_entity_id_tbl.count
2446 loop
2447 -- extend it everytime.
2448 l_project_id.extend;
2449 l_task_id.extend;
2450 l_material_account.extend;
2451 l_material_variance_account.extend;
2452 l_location_id.extend;
2453
2454 -- execute the sql only if wip_entity_id is different from previous execution.
2455 if ( nvl(l_previous_wip_entity,-1) = l_wip_entity_id_tbl(i)) then
2456 l_material_account(i) := l_material_account(i-1);
2457 l_material_variance_account(i) := l_material_variance_account(i-1);
2458 l_project_id(i) := l_project_id(i-1);
2459 l_task_id(i) := l_task_id(i-1);
2460 else
2461 select wdj.material_account, wdj.material_variance_account, wdj.project_id, wdj.task_id
2462 into l_material_account(i),l_material_variance_account(i), l_project_id(i), l_task_id(i)
2463 from wip_discrete_jobs wdj
2464 where wdj.wip_entity_id = l_wip_entity_id_tbl(i)
2465 and wdj.organization_id = p_organization_id;
2466 end if;
2467
2468 -- get the deliver_to location
2469 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2470 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Getting the deliver to location.');
2471 END IF;
2472
2473 if ( nvl(l_previous_wip_entity,-1) = l_wip_entity_id_tbl(i) and
2474 nvl(l_previous_op_seq_num,-1) = l_op_seq_num_tbl(i) ) then
2475 l_location_id(i) := l_location_id(i-1);
2476 else
2477 select bd.location_id
2478 into l_location_id(i)
2479 from bom_departments bd, wip_operations wo
2480 where bd.department_id = wo.department_id
2481 and bd.organization_id = wo.organization_id
2482 and wo.wip_entity_id = l_wip_entity_id_tbl(i)
2483 and wo.operation_seq_num = l_op_seq_num_tbl(i)
2484 and wo.organization_id = p_organization_id;
2485 end if;
2486 l_previous_wip_entity := l_wip_entity_id_tbl(i);
2487 l_previous_op_seq_num := l_op_seq_num_tbl(i);
2488 end loop;
2489
2490 -- get the currency code and the ou_id.
2491 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2492 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
2493 lc_mod_name,'Fetching currency code and OU');
2494 END IF;
2495
2496 select gb.currency_code, to_number(ho.ORG_INFORMATION3)
2497 into l_currency, l_ou_id
2498 from hr_organization_information ho, gl_sets_of_books gb
2499 where gb.set_of_books_id = ho.ORG_INFORMATION1
2500 and ho.organization_id = p_organization_id
2501 and ho.ORG_INFORMATION_CONTEXT = 'Accounting Information';
2502
2503 -- bulk bind the variables for the insert.
2504 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2505 FND_LOG.STRING( FND_LOG.LEVEL_EVENT,
2506 lc_mod_name,
2507 'Just before calling the FORALL insert to po_requisitions_interface_all');
2508 END IF;
2509
2510 forall j in 1 ..l_wip_entity_id_tbl.count
2511 insert into po_requisitions_interface_all (
2512 interface_source_code,
2513 destination_type_code,
2514 authorization_status,
2515 preparer_id, -- person id of the user name
2516 quantity,
2517 destination_organization_id,
2518 deliver_to_location_id,
2519 deliver_to_requestor_id,
2520 source_type_code,
2521 category_id,
2522 item_description,
2523 uom_code,
2524 unit_price,
2525 need_by_date,
2526 wip_entity_id,
2527 wip_operation_seq_num,
2528 charge_account_id,
2529 variance_account_id,
2530 item_id,
2531 wip_resource_seq_num,
2532 suggested_vendor_id,
2533 suggested_vendor_name,
2534 suggested_vendor_site,
2535 suggested_vendor_phone,
2536 suggested_vendor_item_num,
2537 currency_code,
2538 project_id,
2539 task_id,
2540 project_accounting_context,
2541 last_updated_by,
2542 last_update_date,
2543 created_by,
2544 creation_date,
2545 org_id,
2546 reference_num,
2547 source_organization_id,
2548 source_subinventory,
2549 autosource_flag,
2550 group_code
2551 )
2552 values (
2553 'CSD',
2554 'INVENTORY',
2555 'INCOMPLETE',
2556 l_person_id,
2557 l_quantity_tbl(j),
2558 p_organization_id,
2559 l_location_id(j),
2560 l_person_id,
2561 decode(x_requisition_type(j),'I','INVENTORY','V','VENDOR',decode(l_source_org_id_tbl(j),null,'VENDOR','INVENTORY')),
2562 null,
2563 l_item_description_tbl(j),
2564 l_uom_code_tbl(j),
2565 0,
2566 l_needby_date ,
2567 l_wip_entity_id_tbl(j),
2568 l_op_seq_num_tbl(j),
2569 l_material_account(j),
2570 l_material_variance_account(j),
2571 l_item_id_tbl(j),
2572 null,
2573 null,
2574 null,
2575 null,
2576 null,
2577 null,
2578 l_currency,
2579 l_project_id(j),
2580 l_task_id(j),
2581 decode(nvl(l_project_id(j),-1),-1, 'N','Y'),
2582 fnd_global.user_id,
2583 sysdate,
2584 fnd_global.user_id,
2585 sysdate,
2586 l_ou_id,
2587 null,
2588 l_source_org_id_tbl(j),
2589 l_source_subinv_tbl(j),
2590 'Y',
2591 decode(x_requisition_type(j),'I','INVENTORY','V','VENDOR',decode(l_source_org_id_tbl(j),null,'VENDOR','INVENTORY'))
2592 );
2593
2594 -- call out the CP to create the requisition and if required the sales orders.
2595 x_request_id := fnd_request.submit_request(
2596 application => 'CSD', program => 'CSDCRREQ', argument1 => l_ou_id, argument2 => l_ou_id, argument3 => p_repair_line_id);
2597
2598 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2599 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'The concurrent request id is :'||x_request_id);
2600 END IF;
2601
2602 IF x_request_id <= 0
2603 THEN
2604 raise fnd_api.g_exc_error;
2605 END IF;
2606
2607 -- need to call the mat_issue_update procedure.
2608 l_previous_wip_entity := NULL;
2609 FOR k IN 1 ..p_wip_entity_id_tbl.COUNT
2610 LOOP
2611 IF NVL(l_previous_wip_entity,-1) <> p_wip_entity_id_tbl(k)
2612 THEN
2613 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2614 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Calling the csd_hdv_wip_job_pvt.update_mat_issue_quantities for wip_entity_id '||p_wip_entity_id_tbl(k));
2615 END IF;
2616
2617 CSD_HV_WIP_JOB_PVT.update_mat_issue_quantities
2618 (p_api_version_number => 1.0,
2619 p_init_msg_list => fnd_api.g_false,
2620 p_commit => fnd_api.g_false,
2621 p_validation_level => 100,
2622 x_return_status => x_return_status,
2623 x_msg_count => x_msg_count,
2624 x_msg_data => x_msg_data,
2625 p_wip_entity_id => p_wip_entity_id_tbl(k)
2626 );
2627 IF x_return_status <> fnd_api.g_ret_sts_success
2628 THEN
2629 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2630 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Error in update_mat_issue_quantities API for wip_entity_id '||p_wip_entity_id_tbl(k));
2631 END IF;
2632 RAISE fnd_api.g_exc_error;
2633 END IF;
2634 l_previous_wip_entity := p_wip_entity_id_tbl(k);
2635
2636 END IF;
2637 END LOOP;
2638
2639 -- check for substitutes.
2640 -- Logic.
2641 -- go through the actual requested and the derived materials.
2642 -- when there is a discrepency, then we have a substitute.
2643
2644 FOR k IN 1 ..p_wip_entity_id_tbl.COUNT
2645 LOOP
2646 IF l_item_id_tbl(k) <> p_item_id_tbl(k)
2647 THEN
2648 -- update the existing line to have material requirement as 0 and create/update the new line for l_item_id_tbl(k)
2649 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2650 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Item Id '||p_item_id_tbl(k)||' is being replaced by item id '||l_item_id_tbl(k));
2651 END IF;
2652
2653 l_mtl_txn_dtls_upd(k).inventory_item_id := p_item_id_tbl(k);
2654 l_mtl_txn_dtls_upd(k).required_quantity := 0;
2655 l_mtl_txn_dtls_upd(k).wip_entity_id := p_wip_entity_id_tbl(k);
2656 l_mtl_txn_dtls_upd(k).operation_seq_num := p_op_seq_num_tbl(k);
2657 l_mtl_txn_dtls_upd(k).organization_id := p_organization_id;
2658 l_mtl_txn_dtls_upd(k).new_row := 'N';
2659 l_mtl_txn_dtls_upd(k).transaction_quantity := 0;
2660 SELECT wip_transaction_detail_id
2661 INTO l_mtl_txn_dtls_upd(k).wip_transaction_detail_id
2662 FROM csd_wip_transaction_details
2663 WHERE wip_entity_id = p_wip_entity_id_tbl(k)
2664 AND inventory_item_id = p_item_id_tbl(k)
2665 AND operation_seq_num = p_op_seq_num_tbl(k);
2666
2667 l_mtl_txn_dtls_tab(k).inventory_item_id := l_item_id_tbl(k);
2668 l_mtl_txn_dtls_tab(k).transaction_uom := l_uom_code_tbl(k);
2669 l_mtl_txn_dtls_tab(k).wip_entity_id := l_wip_entity_id_tbl(k);
2670 l_mtl_txn_dtls_tab(k).operation_seq_num := l_op_seq_num_tbl(k);
2671 l_mtl_txn_dtls_tab(k).organization_id := p_organization_id;
2672 -- need to check if the item/op/job combination already exists.
2673 BEGIN
2674 SELECT 'N',wip_transaction_detail_id,NVL(cwt.transaction_quantity,
2675 wro.required_quantity-wro.quantity_issued)+l_quantity_tbl(k)
2676 INTO l_mtl_txn_dtls_tab(k).new_row,l_mtl_txn_dtls_tab(k).wip_transaction_detail_id,l_mtl_txn_dtls_tab(k).required_quantity
2677 FROM csd_wip_transaction_details cwt,wip_requirement_operations wro
2678 WHERE cwt.wip_entity_id = l_wip_entity_id_tbl(k)
2679 AND cwt.inventory_item_id = l_item_id_tbl(k)
2680 AND cwt.operation_seq_num = l_op_seq_num_tbl(k)
2681 AND cwt.wip_entity_id = wro.wip_entity_id
2682 AND cwt.operation_seq_num = wro.operation_seq_num
2683 AND cwt.inventory_item_id = wro.inventory_item_id;
2684 EXCEPTION
2685 WHEN no_data_found THEN
2686 l_mtl_txn_dtls_tab(k).new_row := 'Y';
2687 l_mtl_txn_dtls_tab(k).required_quantity := l_quantity_tbl(k);
2688 END;
2689 l_mtl_txn_dtls_tab(k).transaction_quantity := l_mtl_txn_dtls_tab(k).required_quantity ;
2690 l_mtl_txn_dtls_tab(k).supply_subinventory := fnd_profile.value('CSD_DEF_HV_SUBINV');
2691 END IF;
2692 END LOOP;
2693
2694 IF l_mtl_txn_dtls_tab.COUNT > 0
2695 THEN
2696 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2697 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Calling process_save_mtl_txn_dtls to update the required quantity to 0 since its substituted.');
2698 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'The number of records is '||l_mtl_txn_dtls_upd.COUNT);
2699 END IF;
2700 csd_hv_wip_job_pvt.process_save_mtl_txn_dtls
2701 (p_api_version_number => 1,
2702 p_init_msg_list => fnd_api.g_false,
2703 p_commit => fnd_api.g_false,
2704 p_validation_level => 100,
2705 x_return_status => x_return_status,
2706 x_msg_count => x_msg_count,
2707 x_msg_data => x_msg_data,
2708 p_mtl_txn_dtls_tbl => l_mtl_txn_dtls_upd,
2709 x_op_created => x_op_created
2710 );
2711
2712 IF x_return_status <> fnd_api.g_ret_sts_success
2713 THEN
2714 RAISE fnd_api.g_exc_error;
2715 END IF;
2716
2717 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2718 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Calling process_save_mtl_txn_dtls to create new material requirements.');
2719 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'The number of records is '||l_mtl_txn_dtls_tab.COUNT);
2720 END IF;
2721
2722 csd_hv_wip_job_pvt.process_save_mtl_txn_dtls
2723 (p_api_version_number => 1,
2724 p_init_msg_list => fnd_api.g_false,
2725 p_commit => fnd_api.g_false,
2726 p_validation_level => 100,
2727 x_return_status => x_return_status,
2728 x_msg_count => x_msg_count,
2729 x_msg_data => x_msg_data,
2730 p_mtl_txn_dtls_tbl => l_mtl_txn_dtls_tab,
2731 x_op_created => x_op_created
2732 );
2733 IF x_return_status <> fnd_api.g_ret_sts_success
2734 THEN
2735 RAISE fnd_api.g_exc_error;
2736 END IF;
2737 END IF;
2738
2739 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2740 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,lc_mod_name,'Bulk updating all the substitute material requirement lines.');
2741 END IF;
2742 FOR k IN 1 ..l_mtl_txn_dtls_tab.COUNT
2743 LOOP
2744 UPDATE csd_wip_transaction_details cwt SET substitutes = l_mtl_txn_dtls_upd(k).inventory_item_id
2745 WHERE cwt.wip_entity_id = l_mtl_txn_dtls_tab(k).wip_entity_id
2746 AND cwt.operation_seq_num = l_mtl_txn_dtls_tab(k).operation_seq_num
2747 AND cwt.inventory_item_id = l_mtl_txn_dtls_tab(k).inventory_item_id;
2748
2749 UPDATE csd_wip_transaction_details SET is_substituted = 'Y'
2750 WHERE wip_entity_id = l_mtl_txn_dtls_upd(k).wip_entity_id
2751 AND operation_seq_num = l_mtl_txn_dtls_upd(k).operation_seq_num
2752 AND inventory_item_id = l_mtl_txn_dtls_upd(k).inventory_item_id;
2753
2754 END LOOP;
2755
2756 if fnd_api.to_boolean(p_commit) then
2757 commit work;
2758 end if;
2759 exception
2760 when fnd_api.g_exc_error then
2761 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2762 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
2763 lc_mod_name,
2764 'Execution error in the API');
2765 END IF;
2766 Fnd_Msg_Pub.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data );
2767 x_return_Status := fnd_api.g_ret_sts_error;
2768 rollback to create_requisition;
2769 when fnd_api.g_exc_unexpected_error then
2770 IF ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2771 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,
2772 lc_mod_name,
2773 'Unexpected error');
2774 END IF;
2775 x_return_status := fnd_api.g_ret_sts_error;
2776 rollback to create_requisition;
2777 /*when others then
2778 raise;*/
2779 END create_requisition;
2780
2781 PROCEDURE create_requisition_cp
2782 (
2783 errbuf OUT NOCOPY VARCHAR2,
2784 retcode OUT NOCOPY VARCHAR2,
2785 p_ir_operating_unit IN NUMBER DEFAULT NULL,
2786 p_so_operating_unit IN NUMBER DEFAULT NULL,
2787 p_repair_line_id IN NUMBER DEFAULT NULL,
2788 p_requisition_id IN NUMBER DEFAULT NULL
2789 )
2790 IS
2791 l_ir_operating_unit NUMBER := p_ir_operating_unit;
2792 l_so_operating_unit NUMBER := p_so_operating_unit;
2793 l_success BOOLEAN;
2794 l_request_id NUMBER;
2795 l_requisition_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2796 l_authorization JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2797 l_req_type JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2798 l_create_iso BOOLEAN := TRUE;
2799 l_dummy VARCHAR2(1);
2800 x_phase VARCHAR2(15);
2801 x_status VARCHAR2(10);
2802 x_dev_phase VARCHAR2(15);
2803 x_dev_status VARCHAR2(10);
2804 x_message VARCHAR2(2000);
2805 x_return_status VARCHAR2(1);
2806 x_msg_count NUMBER;
2807 x_msg_data VARCHAR2(2000);
2808
2809 BEGIN
2810
2811 IF l_ir_operating_unit IS NULL
2812 THEN
2813 l_ir_operating_unit := fnd_global.org_id;
2814 END IF;
2815
2816 IF p_requisition_id IS NULL
2817 THEN
2818 BEGIN
2819 SELECT 'Y'
2820 INTO l_dummy
2821 FROM po_requisitions_interface_all
2822 WHERE interface_source_code = 'CSD'
2823 AND process_flag IS NULL
2824 AND ROWNUM < 2;
2825 EXCEPTION
2826 WHEN no_data_found THEN
2827 NULL;
2828 END;
2829
2830 IF l_dummy ='Y'
2831 THEN
2832 -- submit asynchronous request to create the requisition.
2833 -- post MOAC, we need to set the org context for the concurrent request.
2834 fnd_request.set_org_id (l_ir_operating_unit);
2835
2836 l_request_id := fnd_request.submit_request(
2837 'PO', 'REQIMPORT', NULL, NULL, FALSE,'CSD', NULL, 'NULL',
2838 NULL ,'N', 'Y' , chr(0), NULL, NULL, NULL,
2839 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2840 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2841 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2842 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2843 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2844 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2845 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2846 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2847 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
2848 );
2849
2850 If l_request_id <= 0 then
2851 -- error during concurrent request submission.
2852 fnd_file.put_line(fnd_file.log,'There was an error in launching the Requisition Import program');
2853 raise fnd_api.g_exc_error;
2854 end if;
2855
2856 -- commit.
2857 COMMIT WORK;
2858 -- wait for the request to complete.
2859 l_success := fnd_concurrent.wait_for_request
2860 (request_id => l_request_id,
2861 interval => 3,
2862 phase => x_phase,
2863 status => x_status,
2864 dev_phase => x_dev_phase,
2865 dev_status => x_dev_status,
2866 message => x_message
2867 );
2868
2869 IF NOT l_success
2870 THEN
2871 errbuf := x_message;
2872 retcode := 2;
2873 return;
2874 END IF;
2875
2876 BEGIN
2877 SELECT requisition_header_id,authorization_status,type_lookup_code
2878 BULK COLLECT INTO l_requisition_id,l_authorization,l_req_type
2879 FROM po_requisition_headers_all
2880 WHERE request_id = l_request_id
2881 AND creation_date > SYSDATE - 1;
2882
2883 FOR i IN 1 ..l_requisition_id.COUNT
2884 LOOP
2885 IF NOT l_authorization(i) = 'APPROVED' OR NOT l_req_type(i) = 'INTERNAL'
2886 THEN
2887 fnd_file.put_line(fnd_file.log,'Cannot create ISO for requisition id:'||l_requisition_id(i));
2888 l_create_iso := FALSE;
2889 l_requisition_id.DELETE(i);
2890 END IF;
2891 END LOOP;
2892 END;
2893 ELSE
2894 -- SELECT all the requisition id's originated from CSD and are not yet interfaced to OM.
2895 SELECT prh.requisition_header_id
2896 BULK COLLECT INTO l_requisition_id
2897 FROM po_requisition_headers_all prh,po_requisition_lines_all prl,
2898 csd_repair_job_xref crj,csd_wip_transaction_details cwt
2899 WHERE crj.wip_entity_id = cwt.wip_entity_id
2900 AND cwt.wip_entity_id = prl.wip_entity_id
2901 AND cwt.inventory_item_id = prl.item_id
2902 AND cwt.operation_seq_num = prl.wip_operation_seq_num
2903 AND prl.requisition_header_id = prh.requisition_header_id
2904 AND prh.type_lookup_code = 'INTERNAL'
2905 AND prh.authorization_status = 'APPROVED';
2906
2907 END IF;
2908 ELSE
2909 l_requisition_id.EXTEND;
2910 l_requisition_id(1) := p_requisition_id;
2911 END IF;
2912
2913 FOR i IN 1 ..l_requisition_id.COUNT
2914 LOOP
2915 csd_internal_orders_pvt.create_internal_sales_orders
2916 (p_requisition_id => l_requisition_id(i),
2917 p_so_operating_unit => NVL(l_so_operating_unit,l_ir_operating_unit),
2918 p_ir_operating_unit => l_ir_operating_unit,
2919 p_operating_unit => NVL(l_so_operating_unit,l_ir_operating_unit),
2920 x_return_status => x_return_status,
2921 x_msg_count => x_msg_count,
2922 x_msg_data => x_msg_data
2923 );
2924
2925 IF x_return_status <> fnd_api.g_ret_sts_success
2926 THEN
2927 RAISE fnd_api.g_exc_error;
2928 END IF;
2929 END LOOP;
2930 l_create_iso := FALSE;
2931
2932 FORALL i IN 1 ..l_requisition_id.COUNT
2933 -- insert/update the records in the csd_product_transactions table.
2934 MERGE INTO csd_product_transactions cpt
2935 USING (SELECT ooh.header_id,ool.line_id,prl.requisition_line_id,prl.requisition_header_id,wdd.delivery_detail_id,
2936 ooh.org_id source_ou,prl.org_id destination_ou,prl.destination_organization_id dest_org,prl.source_organization_id source_org,
2937 prl.quantity,prl.item_id
2938 FROM oe_order_headers_all ooh, oe_order_lines_all ool,
2939 po_requisition_lines_all prl,wsh_delivery_details wdd
2940 WHERE ooh.source_document_id = l_requisition_id(i)
2941 AND ooh.header_id = ool.header_id
2942 AND ool.source_document_line_id = prl.requisition_line_id
2943 AND prl.requisition_header_id = l_requisition_id(i)
2944 AND ooh.header_id = wdd.source_header_id
2945 AND ool.line_id = wdd.source_line_id
2946 AND wdd.source_code = 'OE') a
2947 ON (cpt.req_header_id = l_requisition_id(i) )
2948 WHEN MATCHED THEN UPDATE SET cpt.order_header_id = NVL(cpt.order_header_id,a.header_id),cpt.order_line_id = NVL(cpt.order_line_id,a.line_id),
2949 cpt.delivery_detail_id = NVL(cpt.delivery_detail_id,a.delivery_detail_id),cpt.req_line_id = NVL(cpt.req_line_id,a.requisition_line_id)
2950 WHEN NOT MATCHED THEN INSERT
2951 (product_transaction_id,
2952 repair_line_id,
2953 exp_quantity,
2954 inventory_item_id,
2955 ship_from_ou,
2956 ship_from_org,
2957 rcv_into_ou,
2958 rcv_into_org,
2959 creation_date,
2960 created_by,
2961 last_update_date,
2962 last_update_login,
2963 last_updated_by,
2964 object_version_number,
2965 order_header_id,
2966 order_line_id,
2967 req_header_id,
2968 req_line_id,
2969 delivery_detail_id)
2970 VALUES
2971 (csd_product_transactions_s1.nextval,
2972 p_repair_line_id,
2973 a.quantity,
2974 a.item_id,
2975 a.source_ou,
2976 a.source_org,
2977 a.destination_ou,
2978 a.dest_org,
2979 sysdate,
2980 fnd_global.user_id,
2981 sysdate,
2982 fnd_global.login_id,
2983 fnd_global.user_id,
2984 1,
2985 a.header_id,
2986 a.line_id,
2987 a.requisition_header_id,
2988 a.requisition_line_id,
2989 a.delivery_detail_id);
2990
2991 EXCEPTION
2992 WHEN fnd_api.g_exc_error THEN
2993 retcode := 2;
2994 errbuf := x_message;
2995 END create_requisition_cp;
2996
2997 -- bug#13885502, subhat. The pseudocode of the below routine is outlined here.
2998 -- currently the retry event is not coded in the API.
2999 -- validate the order line id.
3000 -- validate its a valid OM order line id originating from depot repair.
3001 -- make a call out to user hooks to derive the service item.
3002 -- if the user hook returns null, then call depot defaulting rules to determine if the
3003 -- item being shipped out is covered under warranty.
3004 -- service item is not null, derive the service attributes.
3005 -- create a new line under the order header for the service item with necessary service
3006 -- information.
3007 -- once the new service line is added, call the OKS CP to create the warranty for the
3008 -- service line.
3009 -- if contracts creation successful, add a new repair activity.
3010
3011 PROCEDURE create_repair_warranty
3012 (
3013 p_api_version_number IN NUMBER,
3014 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3015 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3016 x_return_status OUT NOCOPY VARCHAR2,
3017 x_msg_count OUT NOCOPY NUMBER,
3018 x_msg_data OUT NOCOPY VARCHAR2,
3019 x_contract_number OUT NOCOPY VARCHAR2,
3020 p_order_line_id IN NUMBER
3021 )
3022 IS
3023 lc_api_version CONSTANT NUMBER DEFAULT 1.0;
3024 lc_api_name CONSTANT VARCHAR2(30) := 'create_repair_warranty';
3025 lc_mod_name CONSTANT VARCHAR2(50) := 'csd_repairs_util.create_repair_warranty';
3026
3027 l_repair_line_id NUMBER;
3028 l_incident_id NUMBER;
3029 l_service_item_id NUMBER;
3030 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
3031 x_rule_id NUMBER;
3032 l_line_tbl oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;
3033 l_header_id NUMBER;
3034 l_serial_number mtl_serial_numbers.serial_number%TYPE;
3035 l_instance_id NUMBER;
3036 l_service_start_date DATE;
3037 l_service_period VARCHAR2(3);
3038 l_ship_inv_item_id NUMBER;
3039 l_service_duration NUMBER;
3040 l_service_starting_delay NUMBER;
3041 l_service_durn_period_code VARCHAR2(3);
3042 l_dummy VARCHAR2(3);
3043 l_conversion NUMBER;
3044 l_req_completed BOOLEAN;
3045 l_activity_rec csd_update_programs_pvt.activity_rec_type;
3046 l_contract_number okc_k_headers_all_b.contract_number%TYPE;
3047 l_contract_description okc_k_headers_tl.short_description%TYPE;
3048 l_item_name VARCHAR2(800);
3049 l_duration_desc VARCHAR2(15);
3050 l_org_id NUMBER;
3051 l_ship_from_org_id NUMBER;
3052
3053 CURSOR c_all_mtl_transactions(p_repair_line_id NUMBER)
3054 IS
3055 SELECT mmt.reason_id,mmt.inventory_item_id,wdj.primary_item_id,wro.organization_id
3056 FROM mtl_material_transactions mmt, wip_requirement_operations wro,
3057 csd_repair_job_xref crj, wip_discrete_jobs wdj
3058 WHERE crj.repair_line_id = p_repair_line_id
3059 AND crj.wip_entity_id = wro.wip_entity_id
3060 AND wro.operation_seq_num = mmt.operation_seq_num
3061 AND wro.organization_id = mmt.organization_id
3062 AND wro.wip_entity_id = mmt.transaction_source_id
3063 AND wro.inventory_item_id = mmt.inventory_item_id
3064 AND wro.wip_entity_id = wdj.wip_entity_id;
3065
3066 x_request_id NUMBER;
3067 x_phase VARCHAR2(15);
3068 x_status VARCHAR2(10);
3069 x_dev_phase VARCHAR2(15);
3070 x_dev_status VARCHAR2(10);
3071 x_message VARCHAR2(2000);
3072 x_service_end_date DATE;
3073 x_service_duration NUMBER;
3074 x_service_duration_code VARCHAR2(3);
3075 x_msg_index_out number;
3076
3077 -- OM datastructures.
3078 l_header_rec OE_ORDER_PUB.Header_Rec_Type;
3079 l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
3080 l_header_adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
3081 l_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_Type;
3082 l_header_scr_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
3083 l_line_scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
3084 l_request_rec OE_ORDER_PUB.Request_Rec_Type ;
3085
3086 x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
3087 x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
3088 x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
3089 x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
3090 x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
3091 x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
3092 x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
3093 x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
3094 x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
3095 x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
3096 x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
3097 x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
3098 x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
3099 x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
3100 x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
3101 x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
3102 x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
3103 x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
3104 x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
3105 x_line_tbl oe_order_pub.line_tbl_type;
3106 x_header_rec OE_ORDER_PUB.Header_Rec_Type;
3107 x_debug_file varchar2(100);
3108
3109 p_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
3110 p_old_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;
3111 p_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
3112 p_old_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
3113 p_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
3114 p_old_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
3115 p_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
3116 p_old_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;
3117 p_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
3118 p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;
3119 p_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
3120 p_old_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;
3121 p_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
3122 p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;
3123 p_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
3124 p_old_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;
3125 p_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
3126 p_old_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;
3127 p_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
3128 p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
3129 p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
3130 p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
3131 p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
3132 p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
3133 p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
3134 p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
3135 p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
3136 p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
3137 p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
3138 p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
3139 p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
3140 p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
3141 p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
3142 p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
3143 p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
3144 p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;
3145 p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
3146 p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;
3147 p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
3148 p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;
3149 p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type := OE_ORDER_PUB.G_MISS_REQUEST_TBL;
3150
3151 BEGIN
3152 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3153 THEN
3154 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Begin create_repair_warranty');
3155 -- log the input parameters.
3156 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'The passed parameters are :');
3157 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'p_order_line_id => '||p_order_line_id);
3158 END IF;
3159
3160 -- standard check.
3161 IF NOT fnd_api.compatible_api_call(
3162 lc_api_version,
3163 p_api_version_number,
3164 lc_api_name,
3165 g_pkg_name) THEN
3166 RAISE fnd_api.g_exc_unexpected_error;
3167 END IF;
3168
3169 IF fnd_api.to_boolean(p_init_msg_list)
3170 THEN
3171 fnd_msg_pub.initialize;
3172 END IF;
3173 -- initialize return status.
3174 x_return_status := fnd_api.g_ret_sts_success;
3175
3176 -- validate the order line id.
3177 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3178 THEN
3179 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Validating the order line id.');
3180 END IF;
3181
3182 SELECT cpt.repair_line_id,oeh.header_id,ced.inventory_item_id,cr.incident_id
3183 INTO l_repair_line_id,l_header_id,l_ship_inv_item_id,l_incident_id
3184 FROM oe_order_lines_all oel, oe_order_headers_all oeh,
3185 cs_estimate_details ced, csd_product_transactions cpt,wsh_delivery_details wdd,
3186 csd_repairs cr
3187 WHERE oel.line_id = p_order_line_id
3188 AND oel.header_id = oeh.header_id
3189 AND oeh.header_id = ced.order_header_id
3190 AND oel.line_id = ced.order_line_id
3191 AND ced.estimate_detail_id = cpt.estimate_detail_id
3192 AND wdd.source_header_id = oeh.header_id
3193 AND wdd.source_line_id = oel.line_id
3194 AND wdd.source_code = 'OE'
3195 AND cpt.action_type = 'SHIP'
3196 AND cpt.repair_line_id = cr.repair_line_id;
3197
3198 -- fetch the serial number
3199 BEGIN
3200 SELECT serial_number
3201 INTO l_serial_number
3202 FROM(
3203 SELECT fm_serial_number serial_number
3204 FROM wsh_serial_numbers wsn,
3205 wsh_delivery_details wdd
3206 WHERE wdd.source_header_id = l_header_id
3207 AND wdd.source_line_id = p_order_line_id
3208 AND wdd.source_code = 'OE'
3209 AND wdd.delivery_detail_id = wsn.delivery_detail_id
3210 UNION ALL
3211 SELECT wdd.serial_number serial_number
3212 FROM wsh_delivery_details wdd
3213 WHERE wdd.source_header_id = l_header_id
3214 AND wdd.source_line_id = p_order_line_id
3215 AND wdd.source_code = 'OE'
3216 UNION ALL
3217 SELECT msnt.fm_serial_number
3218 FROM wsh_delivery_details wdd,
3219 mtl_serial_numbers_temp msnt
3220 WHERE wdd.source_header_id = l_header_id
3221 AND wdd.source_line_id = p_order_line_id
3222 AND wdd.source_code = 'OE'
3223 AND wdd.transaction_temp_id = msnt.transaction_temp_id
3224 ) WHERE rownum < 2;
3225 EXCEPTION
3226 WHEN no_data_found THEN
3227 l_serial_number := NULL;
3228 END;
3229 IF l_serial_number IS NULL
3230 THEN
3231 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3232 THEN
3233 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Serial number is null. Warranty cannot be created. Returning');
3234 END IF;
3235 RETURN;
3236 END IF;
3237 -- check if there exists a service item for this.
3238 -- make a call to user hook procedure so that customers can provide their own logic to derive the service item.
3239 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3240 THEN
3241 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Calling the user hook procedure csd_logistics_cuhk.service_item');
3242 END IF;
3243 csd_logistics_cuhk.service_item
3244 (p_order_line_id => p_order_line_id,
3245 p_repair_line_id => l_repair_line_id,
3246 x_return_status => x_return_status,
3247 x_msg_data => x_msg_data,
3248 x_msg_count => x_msg_count,
3249 x_service_item_id => l_service_item_id
3250 );
3251
3252 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3253 THEN
3254 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Returned from csd_logistics_cuhk.service_item. The service item returned is '||NVL(l_service_item_id,-999));
3255 END IF;
3256
3257 IF x_return_status <> fnd_api.g_ret_sts_success
3258 THEN
3259 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3260 THEN
3261 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'The user hook procedure returns a non success code. Raising the exception.');
3262 END IF;
3263 raise fnd_api.g_exc_error;
3264 END IF;
3265
3266 IF l_service_item_id IS NULL
3267 THEN
3268 l_rule_input_rec.repair_line_id := l_repair_line_id;
3269 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE
3270 (p_api_version_number => 1.0,
3271 p_init_msg_list => fnd_api.g_false,
3272 p_commit => fnd_api.g_false,
3273 p_validation_level => fnd_api.g_valid_level_full,
3274 p_entity_attribute_type => 'CSD_DEF_ENTITY_ATTR_RO',
3275 p_entity_attribute_code => 'SERVICE_WARRANTY',
3276 p_rule_input_rec => l_rule_input_rec,
3277 x_default_value => l_service_item_id,
3278 x_rule_id => x_rule_id,
3279 x_return_status => x_return_status,
3280 x_msg_count => x_msg_count,
3281 x_msg_data => x_msg_data
3282 );
3283 IF x_return_status <> fnd_api.g_ret_sts_success
3284 THEN
3285 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3286 THEN
3287 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Error occured in calling rules engine.');
3288 END IF;
3289 RAISE fnd_api.g_exc_error;
3290 END IF;
3291 END IF;
3292
3293 -- need to walk through all the materials transacted and check if there exists
3294 -- atleast one service. The moment we find a service, we exit.
3295
3296 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3297 THEN
3298 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Service item from call to rule engine is:'||l_service_item_id);
3299 END IF;
3300
3301 IF l_service_item_id IS NULL
3302 THEN
3303 FOR i IN c_all_mtl_transactions(l_repair_line_id)
3304 LOOP
3305
3306 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3307 THEN
3308 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Calling rule engine with disp code = '||i.reason_id||' item id = '||i.inventory_item_id||' Org id = '||i.organization_id);
3309 END IF;
3310 l_rule_input_rec.wip_mtl_disp_code_id := i.reason_id;
3311 l_rule_input_rec.wip_mtl_txn_item_id := i.inventory_item_id;
3312 l_rule_input_rec.wip_entity_item_id := i.primary_item_id;
3313 l_rule_input_rec.wip_inv_org_id := i.organization_id;
3314 l_rule_input_rec.repair_line_id := l_repair_line_id;
3315
3316 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE
3317 (p_api_version_number => 1.0,
3318 p_init_msg_list => fnd_api.g_false,
3319 p_commit => fnd_api.g_false,
3320 p_validation_level => fnd_api.g_valid_level_full,
3321 p_entity_attribute_type => 'CSD_DEF_ENTITY_ATTR_RO',
3322 p_entity_attribute_code => 'SERVICE_WARRANTY',
3323 p_rule_input_rec => l_rule_input_rec,
3324 x_default_value => l_service_item_id,
3325 x_rule_id => x_rule_id,
3326 x_return_status => x_return_status,
3327 x_msg_count => x_msg_count,
3328 x_msg_data => x_msg_data
3329 );
3330 IF x_return_status <> fnd_api.g_ret_sts_success
3331 THEN
3332 RAISE fnd_api.g_exc_error;
3333 END IF;
3334
3335 EXIT WHEN l_service_item_id IS NOT NULL;
3336 END LOOP;
3337 END IF;
3338
3339 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3340 THEN
3341 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Service item from call to rule engine(for disp code) is:'||l_service_item_id);
3342 END IF;
3343
3344 IF l_service_item_id IS NULL
3345 THEN
3346 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3347 THEN
3348 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Service item is null. Returning..');
3349 END IF;
3350 RETURN;
3351 END IF;
3352
3353 -- get the instance id.
3354 BEGIN
3355 SELECT instance_id
3356 INTO l_instance_id
3357 FROM csi_item_instances
3358 WHERE inventory_item_id = l_ship_inv_item_id
3359 AND serial_number = l_serial_number;
3360 EXCEPTION
3361 WHEN NO_DATA_FOUND THEN
3362 fnd_message.set_name('CSD','CSD_IB_INSTANCE_MISSING');
3363 fnd_message.set_token('SERIAL_NUM',l_serial_number);
3364 END;
3365 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3366 THEN
3367 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'The instance id is :'||l_instance_id);
3368 END IF;
3369
3370 -- get the service item defaults.
3371 SELECT service_duration_period_code, service_duration, service_starting_delay
3372 INTO l_service_durn_period_code,l_service_duration,l_service_starting_delay
3373 FROM mtl_system_items_b
3374 WHERE inventory_item_id = l_service_item_id
3375 AND organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
3376
3377 -- the service item is not null, create the line in OM.
3378 -- get the ship from org id
3379 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE
3380 (p_api_version_number => 1.0,
3381 p_init_msg_list => fnd_api.g_false,
3382 p_commit => fnd_api.g_false,
3383 p_validation_level => fnd_api.g_valid_level_full,
3384 p_entity_attribute_type => 'CSD_DEF_ENTITY_ATTR_RO',
3385 p_entity_attribute_code => 'SHIP_FROM_ORG',
3386 p_rule_input_rec => l_rule_input_rec,
3387 x_default_value => l_ship_from_org_id,
3388 x_rule_id => x_rule_id,
3389 x_return_status => x_return_status,
3390 x_msg_count => x_msg_count,
3391 x_msg_data => x_msg_data
3392 );
3393 IF l_ship_from_org_id IS NULL
3394 THEN
3395 l_ship_from_org_id := fnd_profile.value('CSD_DEF_REP_INV_ORG');
3396 END IF;
3397
3398 l_line_tbl(1) := oe_order_pub.g_miss_line_rec;
3399 l_line_tbl(1).inventory_item_id := l_service_item_id;
3400 l_line_tbl(1).ordered_quantity := 1;
3401 l_line_tbl(1).order_quantity_uom := l_service_durn_period_code;
3402 l_line_tbl(1).header_id := l_header_id;
3403 l_line_tbl(1).ship_from_org_id := l_ship_from_org_id;
3404 l_line_tbl(1).operation := oe_globals.g_opr_create;
3405 l_line_tbl(1).service_reference_type_code := 'CUSTOMER_PRODUCT';
3406 l_line_tbl(1).service_reference_line_id := l_instance_id;
3407
3408 -- derive the service start date and service end dates.
3409 IF l_service_starting_delay IS NULL OR l_service_starting_delay = 0
3410 THEN
3411 l_service_start_date := SYSDATE;
3412 ELSE
3413 l_service_start_date := SYSDATE + l_service_starting_delay;
3414 END IF;
3415
3416 -- calculate the service end date.
3417 -- call OKS public API to get the end date.
3418 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3419 THEN
3420 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Calling oks_omint_pub.get_duration to get the duration.');
3421 END IF;
3422 oks_omint_pub.get_duration
3423 (p_api_version => 11.5,
3424 p_init_msg_list => fnd_api.g_false,
3425 x_msg_count => x_msg_count,
3426 x_msg_data => x_msg_data,
3427 x_return_status => x_return_status,
3428 p_customer_id => NULL,
3429 p_system_id => NULL,
3430 p_service_duration => l_service_duration,
3431 p_service_period => l_service_durn_period_code,
3432 p_start_date => l_service_start_date,
3433 p_end_date => NULL,
3434 x_service_duration => x_service_duration,
3435 x_service_period => x_service_duration_code,
3436 x_new_end_date => x_service_end_date
3437 );
3438
3439 IF x_return_status <> fnd_api.g_ret_sts_success
3440 THEN
3441 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3442 THEN
3443 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Error in oks_omint_pub.get_duration. Error msg is: '||x_msg_data);
3444 END IF;
3445 RAISE fnd_api.g_exc_error;
3446
3447 END IF;
3448
3449 l_line_tbl(1).service_start_date := l_service_start_date;
3450 l_line_tbl(1).service_end_date := x_service_end_date;
3451 l_line_tbl(1).service_duration := l_service_duration;
3452 l_line_tbl(1).service_period := l_service_durn_period_code;
3453
3454 -- get the operating unit.
3455 l_org_id := csd_process_util.get_org_id(l_incident_id);
3456
3457 -- call the OE API.
3458 mo_global.init('ONT');
3459 mo_global.set_policy_context('S',l_org_id);
3460
3461 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3462 THEN
3463 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Calling oe_order_pub.process_order');
3464 END IF;
3465 oe_order_pub.process_order
3466 (p_api_version_number => 1.0
3467 , p_init_msg_list => fnd_api.g_false
3468 , p_return_values => fnd_api.g_false
3469 , p_action_commit => fnd_api.g_false
3470 , x_return_status => x_return_status
3471 , x_msg_count => x_msg_count
3472 , x_msg_data => x_msg_data
3473 , p_header_rec => l_header_rec
3474 , p_line_tbl => l_line_tbl
3475 , p_action_request_tbl => l_action_request_tbl
3476 , p_org_id => l_org_id
3477 -- out parameters
3478 , x_header_rec => x_header_rec
3479 , x_header_val_rec => x_header_val_rec
3480 , x_header_adj_tbl => x_header_adj_tbl
3481 , x_header_adj_val_tbl => x_header_adj_val_tbl
3482 , x_header_price_att_tbl => x_header_price_att_tbl
3483 , x_header_adj_att_tbl => x_header_adj_att_tbl
3484 , x_header_adj_assoc_tbl => x_header_adj_assoc_tbl
3485 , x_header_scredit_tbl => x_header_scredit_tbl
3486 , x_header_scredit_val_tbl => x_header_scredit_val_tbl
3487 , x_line_tbl => x_line_tbl
3488 , x_line_val_tbl => x_line_val_tbl
3489 , x_line_adj_tbl => x_line_adj_tbl
3490 , x_line_adj_val_tbl => x_line_adj_val_tbl
3491 , x_line_price_att_tbl => x_line_price_att_tbl
3492 , x_line_adj_att_tbl => x_line_adj_att_tbl
3493 , x_line_adj_assoc_tbl => x_line_adj_assoc_tbl
3494 , x_line_scredit_tbl => x_line_scredit_tbl
3495 , x_line_scredit_val_tbl => x_line_scredit_val_tbl
3496 , x_lot_serial_tbl => x_lot_serial_tbl
3497 , x_lot_serial_val_tbl => x_lot_serial_val_tbl
3498 , x_action_request_tbl => x_action_request_tbl
3499 );
3500
3501 IF x_return_status <> fnd_api.g_ret_sts_success
3502 THEN
3503
3504 if x_msg_count > 1
3505 then
3506 for i in 1 ..x_msg_count
3507 loop
3508 fnd_msg_pub.get(1,'F',x_msg_data,x_msg_index_out);
3509 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3510 THEN
3511 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, lc_mod_name, 'msg is :'||x_msg_data);
3512 END IF;
3513 end loop;
3514 else
3515 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3516 p_data => x_msg_data
3517 );
3518 end if;
3519 RAISE fnd_api.g_exc_error;
3520 END IF;
3521
3522 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3523 THEN
3524 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Running workflow back ground process to process the deferred activities.');
3525 END IF;
3526
3527 wf_engine.background
3528 (itemtype => 'OEOL'
3529 );
3530
3531 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3532 THEN
3533 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'newly created line id is :'||x_line_tbl(1).line_id);
3534 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Starting the loop!');
3535 END IF;
3536 -- successfully added the service item to order. Call OKS CP to create the warranty contract.
3537 -- before calling the API, we need to poll for the order line to appear in the oks_reprocessing table.
3538 -- This is needed since OM line fulfilment is a deferred workflow activity.
3539
3540 LOOP
3541 BEGIN
3542 SELECT 'Y'
3543 INTO l_dummy
3544 FROM oks_reprocessing
3545 WHERE order_id = l_header_id
3546 AND order_line_id = x_line_tbl(1).line_id;
3547 EXCEPTION
3548 WHEN NO_DATA_FOUND THEN
3549 NULL;
3550 END;
3551 EXIT WHEN l_dummy = 'Y';
3552 dbms_lock.sleep(3);
3553 END LOOP;
3554
3555 x_request_id := fnd_request.submit_request('OKS','OKSREPROC',NULL,NULL,FALSE,'SEL',NULL);
3556
3557 IF x_request_id <= 0
3558 THEN
3559 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3560 THEN
3561 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Error occurred while submitting the concurrent request. Program name is:OKSREPROC');
3562 END IF;
3563 RAISE fnd_api.g_exc_error;
3564 END IF;
3565
3566 -- we need to commit the work here (for the CP to be launched)
3567 COMMIT WORK ;
3568 -- wait for the request.
3569 l_req_completed := fnd_concurrent.wait_for_request
3570 (request_id => x_request_id,
3571 interval => 5,
3572 phase => x_phase,
3573 status => x_status,
3574 dev_phase => x_dev_phase,
3575 dev_status => x_dev_status,
3576 message => x_message
3577 );
3578
3579 IF NOT l_req_completed
3580 THEN
3581 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3582 THEN
3583 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Error occurred while waiting for the request to complete. The error message is :'||x_message);
3584 END IF;
3585 RAISE fnd_api.g_exc_error;
3586 END IF;
3587 -- add a new repair activity to mark the creation of repair warranty.
3588 -- 1. get the contract number and its short description
3589 BEGIN
3590 SELECT okh.contract_number,okhtl.short_description
3591 INTO l_contract_number,l_contract_description
3592 FROM oks_reprocessing okr,okc_k_headers_all_b okh,okc_k_headers_tl okhtl
3593 WHERE okr.order_line_id = x_line_tbl(1).line_id
3594 AND okr.order_id = l_header_id
3595 AND okr.contract_id = okh.id
3596 AND okh.id = okhtl.id
3597 AND okhtl.LANGUAGE = userenv('LANG');
3598 EXCEPTION
3599 WHEN NO_DATA_FOUND THEN
3600 -- no contracts created. Raise the error.
3601 fnd_message.set_name('CSD','CSD_SER_WARRANTY_CR_ERR');
3602 fnd_message.set_token('ORDER_LINE_ID',x_line_tbl(1).line_id);
3603 RAISE fnd_api.g_exc_error;
3604 END;
3605
3606 -- get the item name
3607 SELECT concatenated_segments
3608 INTO l_item_name
3609 FROM mtl_system_items_kfv
3610 WHERE organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG')
3611 AND inventory_item_id = l_ship_inv_item_id;
3612
3613 -- get the duration name
3614 SELECT unit_of_measure
3615 INTO l_duration_desc
3616 FROM mtl_units_of_measure_vl
3617 WHERE uom_code = l_service_durn_period_code;
3618 -- 2. init the activity rec.
3619 l_activity_rec.repair_line_id := l_repair_line_id;
3620 l_activity_rec.event_code := 'WI';
3621 l_activity_rec.action_code := 0;
3622 l_activity_rec.event_date := SYSDATE;
3623 l_activity_rec.quantity := 1;
3624 l_activity_rec.paramc1 := l_contract_number;
3625 l_activity_rec.paramc2 := l_contract_description;
3626 l_activity_rec.paramc3 := l_item_name;
3627 l_activity_rec.paramc4 := l_serial_number;
3628 l_activity_rec.paramc5 := l_service_start_date;
3629 l_activity_rec.paramc6 := l_service_duration||' '||l_duration_desc;
3630 l_activity_rec.object_version_number := null;
3631
3632 -- 3. log the activity.
3633 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3634 THEN
3635 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Calling csd_update_programs_pvt.log_activity');
3636 END IF;
3637 csd_update_programs_pvt.log_activity
3638 (p_api_version => 1.0,
3639 p_commit => p_commit,
3640 p_init_msg_list => fnd_api.g_false,
3641 p_validation_level => fnd_api.g_valid_level_full,
3642 x_return_status => x_return_status,
3643 x_msg_count => x_msg_count,
3644 x_msg_data => x_msg_data,
3645 p_activity_rec => l_activity_rec
3646 );
3647 IF x_return_status <> fnd_api.g_ret_sts_success
3648 THEN
3649 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3650 THEN
3651 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'csd_update_programs_pvt.log_activity. Error msg is: '||x_msg_data);
3652 END IF;
3653 RAISE fnd_api.g_exc_error;
3654 END IF;
3655
3656 IF fnd_api.to_boolean(p_commit)
3657 THEN
3658 COMMIT WORK;
3659 END IF;
3660
3661 EXCEPTION
3662 WHEN NO_DATA_FOUND THEN
3663 x_return_status := fnd_api.g_ret_sts_error;
3664 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3665 p_data => x_msg_data
3666 );
3667
3668 WHEN fnd_api.g_exc_error THEN
3669 x_return_status := fnd_api.g_ret_sts_error;
3670 fnd_msg_pub.count_and_get(p_count => x_msg_count,
3671 p_data => x_msg_data
3672 );
3673
3674 END create_repair_warranty;
3675
3676 PROCEDURE create_inst_party_relation
3677 (
3678 p_api_version_number IN NUMBER,
3679 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3680 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3681 x_return_status OUT NOCOPY VARCHAR2,
3682 x_msg_count OUT NOCOPY NUMBER,
3683 x_msg_data OUT NOCOPY VARCHAR2,
3684 x_relationship_created OUT NOCOPY VARCHAR2,
3685 p_sr_party_id IN NUMBER,
3686 p_sr_account_id IN NUMBER,
3687 p_instance_id IN NUMBER,
3688 p_relationship_type IN VARCHAR2
3689 ) IS
3690
3691 lc_api_version CONSTANT NUMBER DEFAULT 1.0;
3692 lc_api_name CONSTANT VARCHAR2(30) := 'create_inst_party_relation';
3693 lc_mod_name CONSTANT VARCHAR2(50) := 'csd_repairs_util.create_inst_party_relation';
3694
3695 l_exists VARCHAR2(1);
3696
3697 -- csi datastructures.
3698 l_party_tbl csi_datastructures_pub.party_tbl;
3699 l_party_acct_tbl csi_datastructures_pub.party_account_tbl;
3700 l_txn_rec csi_datastructures_pub.transaction_rec;
3701 l_oks_txn_inst_tbl oks_ibint_pub.txn_instance_tbl;
3702
3703 BEGIN
3704
3705 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3706 THEN
3707 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Begin create_inst_party_relation');
3708 -- log the input parameters.
3709 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'The passed parameters are :');
3710 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'p_sr_party_id => '||p_sr_party_id);
3711 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'p_sr_account_id => '||p_sr_account_id);
3712 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'p_instance_id => '||p_instance_id);
3713 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'p_relationship_type => '||p_relationship_type);
3714 END IF;
3715
3716
3717 -- standard check.
3718 IF NOT fnd_api.compatible_api_call(
3719 lc_api_version,
3720 p_api_version_number,
3721 lc_api_name,
3722 g_pkg_name) THEN
3723 RAISE fnd_api.g_exc_unexpected_error;
3724 END IF;
3725
3726 IF fnd_api.to_boolean(p_init_msg_list)
3727 THEN
3728 fnd_msg_pub.initialize;
3729 END IF;
3730
3731 -- initialize return status.
3732 x_return_status := fnd_api.g_ret_sts_success;
3733 -- initialize the relation created flag.
3734 x_relationship_created := fnd_api.g_true;
3735
3736 -- check if there exists a valid relationship for the customer/account and relationship type.
3737 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3738 THEN
3739 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Checking if there exists a valid relationship for passed in relationship type');
3740
3741 END IF;
3742
3743 BEGIN
3744 SELECT 'Y'
3745 INTO l_exists
3746 FROM csi_i_parties
3747 WHERE instance_id = p_instance_id
3748 AND relationship_type_code = p_relationship_type
3749 AND party_id = p_sr_party_id
3750 AND SYSDATE BETWEEN NVL(active_start_date,SYSDATE - 1) AND NVL(active_end_date, SYSDATE + 1);
3751
3752 x_relationship_created := fnd_api.g_false;
3753 RETURN;
3754
3755 EXCEPTION
3756 WHEN NO_DATA_FOUND THEN
3757 l_exists := 'N';
3758 END;
3759
3760 -- initialize the csi_data_structures.
3761 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3762 THEN
3763 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Populating the required CSI data structures.');
3764 END IF;
3765
3766 l_party_tbl(1).instance_id := p_instance_id;
3767 l_party_tbl(1).party_id := p_sr_party_id;
3768 l_party_tbl(1).contact_flag := 'N';
3769 l_party_tbl(1).active_start_date := SYSDATE;
3770 l_party_tbl(1).relationship_type_code := p_relationship_type;
3771 l_party_tbl(1).party_source_table := 'HZ_PARTIES';
3772
3773 l_txn_rec.transaction_id := NULL;
3774 l_txn_rec.transaction_date := SYSDATE;
3775 l_txn_rec.source_transaction_date := SYSDATE;
3776 l_txn_rec.transaction_type_id := 1;
3777 l_txn_rec.txn_sub_type_id := NULL;
3778 l_txn_rec.source_group_ref_id := NULL;
3779 l_txn_rec.source_group_ref := '';
3780 l_txn_rec.source_header_ref_id := NULL;
3781 l_txn_rec.source_header_ref := '';
3782 l_txn_rec.source_line_ref_id := NULL;
3783 l_txn_rec.source_line_ref := '';
3784 l_txn_rec.source_dist_ref_id1 := NULL;
3785 l_txn_rec.source_dist_ref_id2 := NULL;
3786 l_txn_rec.inv_material_transaction_id := NULL;
3787 l_txn_rec.transaction_quantity := NULL;
3788 l_txn_rec.transaction_uom_code := '';
3789 l_txn_rec.transacted_by := NULL;
3790 l_txn_rec.transaction_status_code := '';
3791 l_txn_rec.transaction_action_code := '';
3792 l_txn_rec.message_id := NULL;
3793 l_txn_rec.object_version_number := NULL;
3794 l_txn_rec.split_reason_code := '';
3795
3796 -- call the CSI API.
3797 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3798 THEN
3799 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Calling csi_party_relationships_pub.create_inst_party_relationship');
3800 END IF;
3801
3802 SAVEPOINT create_inst_party_relation;
3803
3804 csi_party_relationships_pub.create_inst_party_relationship
3805 (p_api_version => 1.0,
3806 p_commit => p_commit,
3807 p_init_msg_list => fnd_api.g_false,
3808 p_party_tbl => l_party_tbl,
3809 p_party_account_tbl => l_party_acct_tbl,
3810 p_txn_rec => l_txn_rec,
3811 p_oks_txn_inst_tbl => l_oks_txn_inst_tbl,
3812 x_return_status => x_return_status,
3813 x_msg_count => x_msg_count,
3814 x_msg_data => x_msg_data
3815 );
3816
3817 IF x_return_status <> fnd_api.g_ret_sts_success
3818 THEN
3819 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3820 THEN
3821 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'The csi_party_relationships_pub.create_inst_party_relationship API returned a non success return status.');
3822 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'The error count:'||x_msg_count);
3823 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'The error msg:'||x_msg_data);
3824 END IF;
3825
3826 RAISE fnd_api.g_exc_error;
3827 END IF;
3828
3829 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3830 THEN
3831 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'Successfully created the instance party relationship. Exiting '||lc_mod_name);
3832 END IF;
3833
3834 EXCEPTION
3835 WHEN fnd_api.g_exc_error THEN
3836 x_relationship_created := fnd_api.g_false;
3837 ROLLBACK TO create_inst_party_relation;
3838
3839 WHEN fnd_api.g_exc_unexpected_error THEN
3840 x_relationship_created := fnd_api.g_false;
3841
3842 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3843 THEN
3844 fnd_log.string(fnd_log.level_statement, lc_mod_name, 'The api version check failed!');
3845 END IF;
3846
3847 END create_inst_party_relation;
3848
3849 END Csd_Repairs_Util;