[Home] [Help]
PACKAGE BODY: APPS.CSD_REPAIR_MANAGER_UTIL
Source
1 PACKAGE BODY CSD_REPAIR_MANAGER_UTIL as
2 /* $Header: csdurpmb.pls 120.7.12020000.3 2013/04/09 05:14:15 subhat ship $ */
3 -- Start of Comments
4 -- Package name : CSD_REPAIR_MANAGER_UTIL
5 -- Purpose : This package will be used as utility package for repair manager portal
6 --
7 --
8 -- History : 15/07/2009, Created by Sudheer Bhat
9 -- History :
10 -- History :
11 -- NOTE :
12 -- End of Comments
13
14 -- cache the item_id||org_id - threshold days. This way, we need not run the SQL again and again.
15 TYPE aging_cache is table of NUMBER INDEX BY BINARY_INTEGER;
16 g_aging_cache aging_cache;
17
18 -- cache the business process.
19 -- business process should be same for all the repair types. But no chances taken!
20 TYPE business_process_cache is table of NUMBER index by binary_integer;
21 g_business_process_cache business_process_cache;
22
23 -- this value stores the install site use id used during creation of instances.
24 -- we just need to get this value once and reuse.
25
26 TYPE repair_caching_rec is record
27 ( business_process_id number,
28 auto_process_rma varchar2(1),
29 repair_mode varchar2(10)
30 );
31
32 TYPE repair_attrib_cache is table of repair_caching_rec index by binary_integer;
33 g_repair_attrib_cache repair_attrib_cache;
34
35 g_approval_required_flag varchar2(1) := fnd_profile.value('CSD_CUST_APPROVAL_REQD');
36 g_install_site_use_id number;
37 g_severity_id number;
38 g_contract_id number;
39 g_bill_to_use_id number;
40 g_problem_code number;
41 g_contract_service_id number;
42
43 -- private routines.
44
45 procedure prepare_error_message_table
46 (lx_error_message_table IN OUT NOCOPY JTF_VARCHAR2_TABLE_1000,
47 l_msg_count IN NUMBER DEFAULT 0,
48 l_msg_data IN VARCHAR2 DEFAULT NULL,
49 l_errored_field IN VARCHAR2,
50 l_repair_line_id IN NUMBER
51 );
52
53 procedure prepare_error_message_table
54 (lx_error_message_table IN OUT NOCOPY JTF_VARCHAR2_TABLE_1000,
55 l_msg_count IN NUMBER DEFAULT 0,
56 l_msg_data IN VARCHAR2 DEFAULT NULL,
57 l_errored_field IN VARCHAR2,
58 l_repair_line_id IN NUMBER
59 )
60 is
61 l_message_temp varchar2(2000);
62 x_msg_index_out number;
63 l_index_count number := 0;
64 begin
65 -- case1: there is nothing in the message stack
66 l_index_count := lx_error_message_table.count;
67 if l_msg_count = 0 then
68 if l_msg_data is null then
69 fnd_msg_pub.get(1,
70 'F',
71 l_message_temp,
72 x_msg_index_out
73 );
74 end if;
75 -- add the message to out table.
76 l_message_temp := l_repair_line_id||'-'||l_errored_field||'--'||l_message_temp;
77 lx_error_message_table.extend;
78 lx_error_message_table(l_index_count+1)
79 := l_message_temp;
80 end if;
81
82 -- if message count is greater then 0 then, get the messages from stack.
83 if l_msg_count > 0 then
84 if l_msg_count = 1 and l_msg_data is not null then
85 lx_error_message_table(l_repair_line_id||'-'||l_errored_field)
86 := l_msg_data;
87 else
88
89 for i in 1 ..l_msg_count
90 loop
91 fnd_msg_pub.get(i,
92 'F',
93 l_message_temp,
94 x_msg_index_out
95 );
96 l_message_temp := l_repair_line_id||'-'||l_errored_field||'--'||l_message_temp;
97 l_index_count := l_index_count + 1;
98 lx_error_message_table.extend;
99 lx_error_message_table(l_index_count)
100 := l_message_temp;
101 end loop;
102 end if;
103 end if;
104
105 end prepare_error_message_table;
106
107 procedure get_repln_rec(l_interface_rec IN csd_repairs_interface%rowtype,
108 lx_repln_rec OUT NOCOPY csd_repairs_pub.repln_rec_type);
109
110 procedure get_repln_rec(l_interface_rec IN csd_repairs_interface%rowtype,
111 lx_repln_rec OUT NOCOPY csd_repairs_pub.repln_rec_type)
112 is
113 begin
114
115 lx_repln_rec.REPAIR_NUMBER := l_interface_rec.REPAIR_NUMBER;
116 lx_repln_rec.INCIDENT_ID := l_interface_rec.INCIDENT_ID;
117 lx_repln_rec.INVENTORY_ITEM_ID := l_interface_rec.INVENTORY_ITEM_ID;
118 lx_repln_rec.CUSTOMER_PRODUCT_ID := l_interface_rec.CUSTOMER_PRODUCT_ID;
119 lx_repln_rec.UNIT_OF_MEASURE := l_interface_rec.UNIT_OF_MEASURE;
120 lx_repln_rec.REPAIR_TYPE_ID := l_interface_rec.REPAIR_TYPE_ID;
121 --lx_repln_rec.RESOURCE_GROUP := l_interface_rec.RESOURCE_GROUP;
122 lx_repln_rec.RESOURCE_ID := l_interface_rec.RESOURCE_ID;
123 lx_repln_rec.PROJECT_ID := l_interface_rec.PROJECT_ID;
124 lx_repln_rec.TASK_ID := l_interface_rec.TASK_ID;
125 lx_repln_rec.UNIT_NUMBER := l_interface_rec.UNIT_NUMBER;
126 --lx_repln_rec.integration := l_interface_rec.integration;
127 lx_repln_rec.CONTRACT_LINE_ID := l_interface_rec.CONTRACT_LINE_ID;
128 lx_repln_rec.AUTO_PROCESS_RMA := l_interface_rec.AUTO_PROCESS_RMA;
129 lx_repln_rec.REPAIR_MODE := l_interface_rec.REPAIR_MODE;
130 --lx_repln_rec.OBJECT_VERSION_NUMBER :=l_interface_rec.OBJECT_VERSION_NUMBER;
131 lx_repln_rec.ITEM_REVISION := l_interface_rec.ITEM_REVISION;
132 lx_repln_rec.INSTANCE_ID := l_interface_rec.INSTANCE_ID;
133 lx_repln_rec.STATUS := l_interface_rec.STATUS;
134 lx_repln_rec.STATUS_REASON_CODE := l_interface_rec.STATUS_REASON_CODE;
135 lx_repln_rec.DATE_CLOSED := l_interface_rec.DATE_CLOSED;
136 lx_repln_rec.APPROVAL_REQUIRED_FLAG := l_interface_rec.APPROVAL_REQUIRED_FLAG;
137 lx_repln_rec.APPROVAL_STATUS := l_interface_rec.APPROVAL_STATUS;
138 lx_repln_rec.SERIAL_NUMBER := l_interface_rec.SERIAL_NUMBER;
139 lx_repln_rec.PROMISE_DATE := l_interface_rec.PROMISE_DATE;
140 lx_repln_rec.ATTRIBUTE_CATEGORY := l_interface_rec.ATTRIBUTE_CATEGORY;
141 lx_repln_rec.ATTRIBUTE1 := l_interface_rec.ATTRIBUTE1;
142 lx_repln_rec.ATTRIBUTE2 := l_interface_rec.ATTRIBUTE2;
143 lx_repln_rec.ATTRIBUTE3 := l_interface_rec.ATTRIBUTE3;
144 lx_repln_rec.ATTRIBUTE4 := l_interface_rec.ATTRIBUTE4;
145 lx_repln_rec.ATTRIBUTE5 := l_interface_rec.ATTRIBUTE5;
146 lx_repln_rec.ATTRIBUTE6 := l_interface_rec.ATTRIBUTE6;
147 lx_repln_rec.ATTRIBUTE7 := l_interface_rec.ATTRIBUTE7;
148 lx_repln_rec.ATTRIBUTE8 := l_interface_rec.ATTRIBUTE8;
149 lx_repln_rec.ATTRIBUTE9 := l_interface_rec.ATTRIBUTE9;
150 lx_repln_rec.ATTRIBUTE10 := l_interface_rec.ATTRIBUTE10;
151 lx_repln_rec.ATTRIBUTE11 := l_interface_rec.ATTRIBUTE11;
152 lx_repln_rec.ATTRIBUTE12 := l_interface_rec.ATTRIBUTE12;
153 lx_repln_rec.ATTRIBUTE13 := l_interface_rec.ATTRIBUTE13;
154 lx_repln_rec.ATTRIBUTE14 := l_interface_rec.ATTRIBUTE14;
155 lx_repln_rec.ATTRIBUTE15 := l_interface_rec.ATTRIBUTE15;
156 lx_repln_rec.QUANTITY := l_interface_rec.QUANTITY;
157 lx_repln_rec.QUANTITY_IN_WIP := l_interface_rec.QUANTITY_IN_WIP;
158 lx_repln_rec.QUANTITY_RCVD := l_interface_rec.QUANTITY_RCVD;
159 lx_repln_rec.QUANTITY_SHIPPED := l_interface_rec.QUANTITY_SHIPPED;
160 lx_repln_rec.CURRENCY_CODE := l_interface_rec.CURRENCY_CODE;
161 lx_repln_rec.DEFAULT_PO_NUM := l_interface_rec.DEFAULT_PO_NUM;
162 lx_repln_rec.REPAIR_GROUP_ID := l_interface_rec.REPAIR_GROUP_ID;
163 lx_repln_rec.RO_TXN_STATUS := l_interface_rec.RO_TXN_STATUS;
164 lx_repln_rec.ORDER_LINE_ID := l_interface_rec.ORDER_LINE_ID;
165 lx_repln_rec.ORIGINAL_SOURCE_REFERENCE := l_interface_rec.ORIGINAL_SOURCE_REFERENCE;
166 lx_repln_rec.ORIGINAL_SOURCE_HEADER_ID := l_interface_rec.ORIGINAL_SOURCE_HEADER_ID;
167 lx_repln_rec.ORIGINAL_SOURCE_LINE_ID := l_interface_rec.ORIGINAL_SOURCE_LINE_ID;
168 lx_repln_rec.PRICE_LIST_HEADER_ID := l_interface_rec.PRICE_LIST_HEADER_ID;
169 lx_repln_rec.SUPERCESSION_INV_ITEM_ID := l_interface_rec.SUPERCESSION_INV_ITEM_ID;
170 lx_repln_rec.FLOW_STATUS_ID := l_interface_rec.FLOW_STATUS_ID;
171 -- lx_repln_rec.FLOW_STATUS_CODE := l_interface_rec.FLOW_STATUS_CODE;
172 -- lx_repln_rec.FLOW_STATUS := l_interface_rec.FLOW_STATUS;
173 lx_repln_rec.INVENTORY_ORG_ID := l_interface_rec.INVENTORY_ORG_ID;
174 lx_repln_rec.PROBLEM_DESCRIPTION := l_interface_rec.PROBLEM_DESCRIPTION;
175 lx_repln_rec.RO_PRIORITY_CODE := l_interface_rec.RO_PRIORITY_CODE;
176 lx_repln_rec.RESOLVE_BY_DATE := l_interface_rec.RESOLVE_BY_DATE;
177 lx_repln_rec.BULLETIN_CHECK_DATE := l_interface_rec.BULLETIN_CHECK_DATE;
178 lx_repln_rec.ESCALATION_CODE := l_interface_rec.ESCALATION_CODE;
179 lx_repln_rec.REPAIR_YIELD_QUANTITY := l_interface_rec.REPAIR_YIELD_QUANTITY;
180 lx_repln_rec.ATTRIBUTE16 := l_interface_rec.ATTRIBUTE16;
181 lx_repln_rec.ATTRIBUTE17 := l_interface_rec.ATTRIBUTE17;
182 lx_repln_rec.ATTRIBUTE18 := l_interface_rec.ATTRIBUTE18;
183 lx_repln_rec.ATTRIBUTE19 := l_interface_rec.ATTRIBUTE19;
184 lx_repln_rec.ATTRIBUTE20 := l_interface_rec.ATTRIBUTE20;
185 lx_repln_rec.ATTRIBUTE21 := l_interface_rec.ATTRIBUTE21;
186 lx_repln_rec.ATTRIBUTE22 := l_interface_rec.ATTRIBUTE22;
187 lx_repln_rec.ATTRIBUTE23 := l_interface_rec.ATTRIBUTE23;
188 lx_repln_rec.ATTRIBUTE24 := l_interface_rec.ATTRIBUTE24;
189 lx_repln_rec.ATTRIBUTE25 := l_interface_rec.ATTRIBUTE25;
190 lx_repln_rec.ATTRIBUTE26 := l_interface_rec.ATTRIBUTE26;
191 lx_repln_rec.ATTRIBUTE27 := l_interface_rec.ATTRIBUTE27;
192 lx_repln_rec.ATTRIBUTE28 := l_interface_rec.ATTRIBUTE28;
193 lx_repln_rec.ATTRIBUTE29 := l_interface_rec.ATTRIBUTE29;
194 lx_repln_rec.ATTRIBUTE30 := l_interface_rec.ATTRIBUTE30;
195
196 end get_repln_rec;
197
198 procedure write_cp_output(p_group_id in number);
199 procedure write_cp_output(p_group_id in number)
200 is
201
202 l_incident_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
203 l_repair_line_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
204
205 l_repair_numbers JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
206 l_incident_numbers JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
207 l_serial_numbers JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
208 l_instance_numbers JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
209
210 begin
211
212 -- get the repair line ids
213 select repair_line_id
214 bulk collect into l_repair_line_ids
215 from csd_repairs_interface
216 where group_id = p_group_id
217 and processing_phase = 2;
218
219 if l_repair_line_ids.count > 0 then
220 select cr.repair_number,cs.incident_number,cr.serial_number,csi.instance_number
221 bulk collect into l_repair_numbers,l_incident_numbers,l_serial_numbers,l_instance_numbers
222 from csd_repairs cr,
223 cs_incidents_all_b cs,
224 csi_item_instances csi
225 where cr.incident_id = cs.incident_id
226 and cr.repair_line_id in
227 (select * from table(cast(l_repair_line_ids as JTF_NUMBER_TABLE)))
228 and cr.customer_product_id = csi.instance_id(+);
229 end if;
230
231 fnd_file.put_line(fnd_file.output,rpad('Successful Records',30,' '));
232 fnd_file.put_line(fnd_file.output,rpad('Group: '||p_group_id,30,' '));
233 fnd_file.put_line(fnd_file.output,rpad('-',150,'-'));
234
235 fnd_file.put(fnd_file.output,rpad('Service Request',20,' '));
236 fnd_file.put(fnd_file.output,rpad('Repair Number', 25,' '));
237 fnd_file.put(fnd_file.output,rpad('Serial Number', 25,' '));
238 fnd_file.put_line(fnd_file.output,rpad('Instance Number', 25,' '));
239
240 fnd_file.put_line(fnd_file.output,rpad('-',150,'-'));
241
242 for i in 1 ..l_repair_numbers.count
243 loop
244 fnd_file.put(fnd_file.output,rpad(l_incident_numbers(i),20,' '));
245 fnd_file.put(fnd_file.output,rpad(l_repair_numbers(i), 25,' '));
246 fnd_file.put(fnd_file.output,rpad(nvl(l_serial_numbers(i),' '), 25,' '));
247 fnd_file.put_line(fnd_file.output,rpad(nvl(l_instance_numbers(i),' '), 25,' '));
248 end loop;
249
250 end write_cp_output;
251
252 PROCEDURE create_internal_orders(p_api_version IN NUMBER,
253 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
254 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
255 p_repln_tbl IN csd_repairs_pub.repln_tbl_type,
256 p_repair_line_ids IN JTF_NUMBER_TABLE,
257 x_return_status OUT NOCOPY VARCHAR2,
258 x_msg_count OUT NOCOPY NUMBER,
259 x_msg_data OUT NOCOPY VARCHAR2)
260 IS
261 lc_api_version CONSTANT NUMBER := 1.0;
262 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_REPAIR_MANAGER_UTIL.create_internal_orders';
263 l_source_ou NUMBER := fnd_profile.value('ORG_ID');
264 l_source_org NUMBER := fnd_profile.value('CSD_DEF_REP_INV_ORG');
265 l_dest_ou NUMBER := fnd_profile.value('ORG_ID');
266 l_dest_org NUMBER;
267 l_dest_loc_id NUMBER;
268 TYPE l_prod_txn_tbl_type IS TABLE OF CSD_PRODUCT_TRANSACTIONS%ROWTYPE INDEX BY BINARY_INTEGER;
269 l_prod_txn_tbl l_prod_txn_tbl_type;
270 TYPE int_order_item_qty IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
271 int_org_item_qty_tbl int_order_item_qty;
272 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
273 TYPE generic_num_assoc_arry IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
274 l_destn_org_tbl generic_num_assoc_arry;
275 l_destn_loc_tbl generic_num_assoc_arry;
276 l_product_txn_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
277 l_default_rule_id NUMBER;
278 l_included_item_ids generic_num_assoc_arry;
279 l_order_header_id NUMBER;
280 l_order_line_id NUMBER;
281 l_req_hdr_id NUMBER;
282 l_delivery_detail_id NUMBER;
283 errbuf VARCHAR2(2000);
284 retcode NUMBER;
285 l_ord_product_txn_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
286 l_ord_destination_ous JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
287 l_ord_destination_orgs JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
288 l_ord_destination_locs JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
289 l_ord_source_ous JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
290 l_ord_source_orgs JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
291 l_ord_quantitys JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
292 l_index NUMBER := 0;
293
294 BEGIN
295 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
296 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
297 'Begin');
298 END IF;
299
300 -- standard check for API compatibility.
301 IF NOT Fnd_Api.Compatible_API_Call
302 (lc_api_version,
303 p_api_version,
304 lc_api_name,
305 G_PKG_NAME)
306 THEN
307 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
308 END IF;
309
310 IF fnd_api.to_boolean(p_init_msg_list)
311 THEN
312 fnd_msg_pub.initialize;
313 END IF;
314
315 --
316 x_return_status := fnd_api.g_ret_sts_success;
317 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
318 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
319 'Looping through repair line tbl: Count = '||p_repln_tbl.COUNT);
320 END IF;
321 FOR i IN 1 ..p_repln_tbl.COUNT
322 LOOP
323 IF int_org_item_qty_tbl.EXISTS(p_repln_tbl(i).inventory_item_id) THEN
324 int_org_item_qty_tbl(p_repln_tbl(i).inventory_item_id) := int_org_item_qty_tbl(p_repln_tbl(i).inventory_item_id)
325 + p_repln_tbl(i).quantity;
326 ELSE
327 int_org_item_qty_tbl(p_repln_tbl(i).inventory_item_id) := p_repln_tbl(i).quantity;
328 END IF;
329 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
330 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
331 'Checking for destination org and location');
332 END IF;
333
334 IF NOT l_destn_org_tbl.EXISTS(p_repln_tbl(i).inventory_item_id)
335 THEN
336 l_rule_input_rec.repair_line_id := p_repair_line_ids(i);
337 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE (
338 p_api_version_number => 1.0,
339 p_init_msg_list => FND_API.G_TRUE,
340 p_commit => FND_API.G_TRUE,
341 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
342 p_entity_attribute_type => 'CSD_DEF_ENTITY_ATTR_RO',
343 p_entity_attribute_code => 'RMA_RCV_ORG',
344 p_rule_input_rec => l_rule_input_rec,
345 x_default_value => l_dest_org,
346 x_rule_id => l_default_rule_id, -- swai: 12.1.1 ER 7233924
347 x_return_status => x_return_status,
348 x_msg_count => x_msg_count,
349 x_msg_data => x_msg_data
350 );
351 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
352 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
353 'Derived destination org = '||l_dest_org);
354 END IF;
355 IF l_dest_org IS NULL
356 THEN
357 csd_bulk_receive_util.write_to_conc_log(p_msg_count => 1,
358 p_msg_data => 'Defaulting rules for the receive into organization is not setup.. Exiting');
359 RAISE fnd_api.g_exc_error;
360 ELSE
361 l_destn_org_tbl(p_repln_tbl(i).inventory_item_id) := l_dest_org;
362 END IF;
363
364 -- get the default internal location for the destination organization.
365 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
366 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
367 'Deriving dest location for = '||l_dest_org||' OU = '||l_dest_ou);
368 END IF;
369
370 SELECT loc.location_id
371 INTO l_destn_loc_tbl(l_dest_org)
372 FROM hr_locations_all loc,
373 po_location_associations_all ploc,
374 --ra_customers cust -- bug#12610896, subhat
375 hz_cust_accounts cust
376 WHERE loc.inventory_organization_id = l_dest_org
377 AND loc.location_id = ploc.location_id
378 AND ploc.org_id = l_dest_ou
379 AND ploc.customer_id = cust.cust_account_id -- cust.customer_id, bug#12610896, subhat
380 AND cust.customer_type = 'I'
381 AND rownum = 1;
382
383 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
384 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
385 'Derived destination location = '||l_destn_loc_tbl(l_dest_org));
386 END IF;
387 END IF;
388
389 l_prod_txn_tbl(i).repair_line_id := p_repair_line_ids(i);
390 l_prod_txn_tbl(i).inventory_item_id := p_repln_tbl(i).inventory_item_id;
391 l_prod_txn_tbl(i).ship_from_ou := l_source_ou;
392 l_prod_txn_tbl(i).ship_from_org := l_source_org;
393 l_prod_txn_tbl(i).rcv_into_ou := l_dest_ou;
394 l_prod_txn_tbl(i).rcv_into_org := l_dest_org;
395
396 END LOOP;
397
398 -- need to populate the expected quantity on the product transaction table.
399 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
400 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
401 'Updating the expected quantity');
402 END IF;
403 FOR i IN 1 ..l_prod_txn_tbl.COUNT
404 LOOP
405 l_prod_txn_tbl(i).exp_quantity := int_org_item_qty_tbl(l_prod_txn_tbl(i).inventory_item_id);
406 END LOOP;
407
408 -- insert the records into the product transactions table.
409 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
410 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
411 'Forall insert into csd_product_transactions. Number of product txn lines = '||l_prod_txn_tbl.COUNT);
412 END IF;
413
414 FORALL i IN 1 ..l_prod_txn_tbl.COUNT
415 INSERT INTO csd_product_transactions
416 (product_transaction_id,
417 repair_line_id,
418 exp_quantity,
419 inventory_item_id,
420 ship_from_ou,
421 ship_from_org,
422 rcv_into_ou,
423 rcv_into_org,
424 creation_date,
425 created_by,
426 last_update_date,
427 last_update_login,
428 last_updated_by,
429 object_version_number
430 )
431 VALUES
432 (csd_product_transactions_s1.nextval,
433 l_prod_txn_tbl(i).repair_line_id,
434 l_prod_txn_tbl(i).exp_quantity,
435 l_prod_txn_tbl(i).inventory_item_id,
436 l_prod_txn_tbl(i).ship_from_ou,
437 l_prod_txn_tbl(i).ship_from_org,
438 l_prod_txn_tbl(i).rcv_into_ou,
439 l_prod_txn_tbl(i).rcv_into_org,
440 SYSDATE,
441 fnd_global.user_id,
442 SYSDATE,
443 fnd_global.login_id,
444 fnd_global.user_id,
445 1
446 )
447 RETURNING product_transaction_id BULK COLLECT INTO l_product_txn_ids;
448
449 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
450 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
451 'Creating one internal order for the entire batch.');
452 END IF;
453 -- We will pick up the distinct items from the product transaction table
454 -- and put them under same internal requisition, sales order.
455 -- Thus if below is the batch in create repair orders screem
456 -- Item Qty Serial Qty
457 -- CSD002 5 5
458 -- CSD001 10
459 -- AS54888 100
460 -- CSD002 10 5
461
462 -- The resulting internal order will have the following lines with the below quantity requirements.
463 -- Item Qty Serial Qty
464 -- CSD002 15 15
465 -- CSD001 10
466 -- AS54888 100
467
468 FOR i IN 1 ..l_product_txn_ids.COUNT
469 LOOP
470 IF NOT l_included_item_ids.EXISTS(l_prod_txn_tbl(i).inventory_item_id)
471 THEN
472 l_ord_product_txn_ids.EXTEND;
473 l_ord_destination_ous.EXTEND;
474 l_ord_destination_orgs.EXTEND;
475 l_ord_destination_locs.EXTEND;
476 l_ord_source_ous.EXTEND;
477 l_ord_source_orgs.EXTEND;
478 l_ord_quantitys.EXTEND;
479
480 l_included_item_ids(l_prod_txn_tbl(i).inventory_item_id) := 1;
481 l_index := l_index + 1;
482 l_ord_product_txn_ids(l_index) := l_product_txn_ids(i);
483 l_ord_destination_ous(l_index) := l_prod_txn_tbl(i).rcv_into_ou;
484 l_ord_destination_orgs(l_index) := l_prod_txn_tbl(i).rcv_into_org;
485 l_ord_destination_locs(l_index) := l_destn_loc_tbl(l_prod_txn_tbl(i).rcv_into_org);
486 l_ord_source_ous(l_index) := l_prod_txn_tbl(i).ship_from_ou;
487 l_ord_source_orgs(l_index) := l_prod_txn_tbl(i).ship_from_org;
488 l_ord_quantitys(l_index) := l_prod_txn_tbl(i).exp_quantity;
489 END IF;
490 END LOOP;
491
492 -- call the API to create the internal order.
493 CSD_INTERNAL_ORDERS_PVT.create_internal_move_orders
494 (errbuf => errbuf,
495 retcode => retcode,
496 p_product_txn_ids => l_ord_product_txn_ids,
497 p_destination_ous => l_ord_destination_ous,
498 p_destination_orgs => l_ord_destination_orgs,
499 p_destination_loc_ids => l_ord_destination_locs,
500 p_source_ous => l_ord_source_ous,
501 p_source_orgs => l_ord_source_orgs,
502 p_need_by_date => SYSDATE + 1,
503 p_quantitys => l_ord_quantitys
504 );
505
506 IF retcode = 2
507 THEN
508 RAISE fnd_api.g_exc_error;
509 END IF;
510 -- update the values for the other product transaction lines also.
511 FOR i IN (SELECT req_header_id,req_line_id,order_header_id,order_line_id,inventory_item_id,delivery_detail_id
512 FROM csd_product_transactions
513 WHERE product_transaction_id IN (SELECT * FROM TABLE(CAST(l_ord_product_txn_ids AS JTF_NUMBER_TABLE)))
514 )
515 LOOP
516 UPDATE csd_product_transactions SET req_header_id = i.req_header_id,req_line_id = i.req_line_id,
517 order_header_id = i.order_header_id,order_line_id = i.order_line_id,delivery_detail_id = i.delivery_detail_id
518 WHERE product_transaction_id IN (SELECT * FROM TABLE(CAST(l_product_txn_ids AS JTF_NUMBER_TABLE)))
519 AND req_header_id IS NULL;
520 END LOOP;
521
522 EXCEPTION
523 WHEN fnd_api.g_exc_error THEN
524 csd_bulk_receive_util.write_to_conc_log(p_msg_count => x_msg_count,
525 p_msg_data => x_msg_data);
526 END create_internal_orders;
527
528 /******************************************************************************/
529 /* Function Name: get_item_quality_threshold */
530 /* Description: Returns the applicable quality threshold thats set up. */
531 /* @param p_inventory_item_id */
532 /* @param p_organization_id */
533 /******************************************************************************/
534 FUNCTION get_item_quality_threshold(p_inventory_item_id IN NUMBER,
535 p_organization_id IN NUMBER,
536 p_item_revision IN VARCHAR2) RETURN NUMBER
537 IS
538 l_threshold NUMBER := 0;
539 BEGIN
540
541 begin
542 select cqt.threshold_qty
543 into l_threshold
544 from csd_quality_thresholds_b cqt
545 where cqt.inventory_org_id = p_organization_id
546 and cqt.inventory_item_id = p_inventory_item_id
547 and nvl(cqt.item_revision,'-1') = nvl(p_item_revision,'-1');
548
549 return l_threshold;
550 exception
551 when no_data_found then
552 null;
553 end;
554
555 select cqt.threshold_qty
556 into l_threshold
557 from csd_quality_thresholds_b cqt,
558 mtl_item_categories mic
559 where cqt.inventory_org_id = p_organization_id
560 and cqt.item_category_id = mic.category_id
561 and mic.organization_id = p_organization_id
562 and mic.inventory_item_id = p_inventory_item_id;
563
564 return l_threshold;
565 EXCEPTION
566 WHEN NO_DATA_FOUND THEN
567 return l_threshold;
568
569 END get_item_quality_threshold;
570
571 /******************************************************************************/
572 /* Function Name: get_aging_threshold */
573 /* Description: Returns the applicable aging threshold thats set up. */
574 /* @param p_inventory_item_id */
575 /* @param p_organization_id */
576 /******************************************************************************/
577 FUNCTION get_aging_threshold(p_organization_id IN NUMBER,
578 p_inventory_item_id IN NUMBER,
579 p_repair_type_id IN NUMBER,
580 p_flow_status_id IN NUMBER,
581 p_revision IN VARCHAR2,
582 p_repair_line_id IN NUMBER) RETURN NUMBER
583 IS
584 l_aging_threshold NUMBER DEFAULT 999999999999;
585
586 BEGIN
587 --check if the value exists in the cache.
588 /*if g_aging_cache.exists(p_organization_id||p_inventory_item_id) then
589 l_aging_threshold := g_aging_cache(p_organization_id||p_inventory_item_id);
590 return l_aging_threshold;
591 end if;*/
592 -- subhat. Added a filter on repair order status.
593 begin
594
595 SELECT to_number('1')
596 into l_aging_threshold
597 FROM csd_aging_thresholds_b cat,
598 csd_repairs cr ,
599 (select * from csd_repair_history
600 where repair_line_id = p_repair_line_id and event_code = 'SC') crh ,
601 csd_flow_statuses_b cfs
602 WHERE cat.inventory_item_id = p_inventory_item_id
603 AND cat.inventory_org_id = p_organization_id
604 AND cr.repair_line_id = p_repair_line_id
605 AND cr.repair_type_id = nvl(cat.repair_type_id,cr.repair_type_id)
606 AND cr.inventory_item_id = cat.inventory_item_id
607 AND decode(p_revision,null,'1',cat.item_revision) = nvl(p_revision,'1')
608 AND cr.repair_line_id = crh.repair_line_id(+)
609 AND cr.flow_status_id = cfs.flow_status_id
610 AND cfs.flow_status_code = DECODE(crh.event_code,'SC',crh.paramc1,cfs.flow_status_code)
611 AND DECODE(cat.flow_status_id, NULL,
612 (SELECT SYSDATE-creation_date
613 FROM csd_repairs
614 WHERE repair_line_id = cr.repair_line_id
615 ), DECODE(crh.paramc1, NULL,
616 (SELECT SYSDATE-creation_date
617 FROM csd_repairs
618 WHERE repair_line_id = cr.repair_line_id
619 AND flow_status_id = cat.flow_status_id -- bug#8972971, subhat
620 ),
621 (SELECT SYSDATE - crh1.creation_date
622 FROM csd_repair_history crh1
623 WHERE crh1.repair_line_id = crh.repair_line_id
624 AND crh1.paramc1 = crh.paramc1
625 AND rownum = 1
626 ) ) ) > cat.threshold_days ;
627
628 --add it to cache.
629 --g_aging_cache(p_organization_id||p_inventory_item_id||p_revision) := l_aging_threshold;
630 return l_aging_threshold;
631 exception
632 when no_data_found then
633 null;
634 end;
635
636 /*select threshold_days
637 into l_aging_threshold
638 from csd_aging_thresholds_b
639 where inventory_org_id = p_organization_id
640 and p_inventory_item_id =
641 ( select mic.inventory_item_id
642 from mtl_item_categories mic
643 where mic.organization_id = p_organization_id
644 and mic.category_id = item_category_id
645 and mic.inventory_item_id = p_inventory_item_id
646 );*/
647 SELECT to_number('1')
648 INTO l_aging_threshold
649 FROM csd_aging_thresholds_b cat,
650 csd_repairs cr ,
651 (select * from csd_repair_history
652 where repair_line_id = p_repair_line_id and event_code = 'SC') crh,
653 csd_flow_statuses_b cfs
654 WHERE cat.inventory_org_id = p_organization_id
655 AND p_inventory_item_id in (
656 select mic.inventory_item_id
657 from mtl_item_categories mic
658 where mic.organization_id = p_organization_id
659 and mic.category_id = cat.item_category_id
660 and mic.inventory_item_id = p_inventory_item_id
661 )
662
663 AND cr.repair_line_id = p_repair_line_id
664 AND cr.repair_type_id = nvl(cat.repair_type_id,cr.repair_type_id)
665 AND decode(p_revision,null,'1',cat.item_revision) = nvl(p_revision,'1')
666 AND cr.repair_line_id = crh.repair_line_id(+)
667 AND cr.flow_status_id = cfs.flow_status_id
668 AND cfs.flow_status_code = DECODE(crh.event_code,'SC',crh.paramc1,cfs.flow_status_code)
669 AND DECODE(cat.flow_status_id, NULL,
670 (SELECT SYSDATE-creation_date
671 FROM csd_repairs
672 WHERE repair_line_id = cr.repair_line_id
673 ), DECODE(crh.paramc1, NULL,
674 (SELECT SYSDATE-creation_date
675 FROM csd_repairs
676 WHERE repair_line_id = cr.repair_line_id
677 AND flow_status_id = cat.flow_status_id -- bug#8972971, subhat
678 ),
679 (SELECT SYSDATE - crh1.creation_date
680 FROM csd_repair_history crh1
681 WHERE crh1.repair_line_id = crh.repair_line_id
682 AND crh1.paramc1 = crh.paramc1
683 AND rownum = 1
684 ) ) ) > cat.threshold_days ;
685 --add it to cache.
686 --g_aging_cache(p_organization_id||p_inventory_item_id||p_revision) := l_aging_threshold;
687 return l_aging_threshold;
688
689 EXCEPTION
690 WHEN no_data_found THEN
691 return l_aging_threshold;
692 END get_aging_threshold;
693
694 /******************************************************************************/
695 /* Procedure Name: mass_update_repair_orders */
696 /* Description: This procedure provides a utility to mass update the repair */
697 /* orders. The procedure treats each logical action as a seperate*/
698 /* transaction. */
699 /******************************************************************************/
700 PROCEDURE mass_update_repair_orders(p_api_version IN NUMBER DEFAULT 1.0,
701 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
702 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
703 p_repair_line_ids IN JTF_NUMBER_TABLE,
704 p_from_ro_status IN JTF_NUMBER_TABLE,
705 p_orig_ro_type_ids IN JTF_NUMBER_TABLE,
706 p_ro_obj_ver_nos IN JTF_NUMBER_TABLE,
707 p_to_ro_status IN NUMBER DEFAULT NULL,
708 p_ro_type_id IN NUMBER DEFAULT NULL,
709 p_ro_owner_id IN NUMBER DEFAULT NULL,
710 p_ro_org_id IN NUMBER DEFAULT NULL,
711 p_ro_priority_id IN NUMBER DEFAULT NULL,
712 p_ro_escalation_code IN VARCHAR2 DEFAULT NULL,
713 p_note_type IN VARCHAR2 DEFAULT NULL,
714 p_note_visibility IN VARCHAR2 DEFAULT NULL,
715 p_attach_title IN VARCHAR2 DEFAULT NULL,
716 p_attach_descr IN VARCHAR2 DEFAULT NULL,
717 p_attach_cat_id IN NUMBER DEFAULT NULL,
718 p_attach_type IN VARCHAR2 DEFAULT NULL,
719 p_attach_file IN BLOB DEFAULT NULL,
720 p_attach_url IN VARCHAR2 DEFAULT NULL,
721 p_attach_text IN VARCHAR2 DEFAULT NULL,
722 p_file_name IN VARCHAR2 DEFAULT NULL,
723 p_content_type IN VARCHAR2 DEFAULT NULL,
724 p_note_text IN VARCHAR2 DEFAULT NULL,
725 x_return_status OUT NOCOPY VARCHAR2,
726 x_msg_count OUT NOCOPY NUMBER,
727 x_msg_data OUT NOCOPY VARCHAR2,
728 l_error_messages_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_1000,
729 p_ro_promise_date IN DATE DEFAULT NULL
730 )
731 IS
732
733 l_api_version_number constant number := 1.0;
734 l_api_name constant varchar2(100) := 'CSD_REPAIR_MANAGER_UTIL.MASS_UPDATE_REPAIR_ORDERS';
735 TYPE l_repln_rec_tbl_type is table of Csd_Repairs_Pub.REPLN_Rec_Type
736 index by binary_integer;
737 l_repln_rec_tbl l_repln_rec_tbl_type;
738 l_repobj_ver JTF_NUMBER_TABLE := p_ro_obj_ver_nos;
739 --l_repair_line_ids JTF_NUMBER_TABLE;
740 l_update_ros boolean default false;
741 x_object_version_number number;
742 x_jtf_note_id number;
743
744
745 BEGIN
746
747 savepoint mass_update_repair_orders;
748
749 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
750 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
751 'Begin mass update API');
752 END IF;
753 -- standard check for API compatibility.
754 IF NOT Fnd_Api.Compatible_API_Call
755 (l_api_version_number,
756 p_api_version,
757 l_api_name,
758 G_PKG_NAME)
759 THEN
760 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
761 END IF;
762
763 IF Fnd_Api.to_Boolean(p_init_msg_list)
764 THEN
765 Fnd_Msg_Pub.initialize;
766 END IF;
767
768 -- dump the API params for debug purpose.
769
770 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
771 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
772 'Dump all the input params');
773 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
774 'p_to_ro_status = '||p_to_ro_status);
775 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
776 'p_ro_type_id = '||p_ro_type_id);
777 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
778 'p_ro_owner_id = '||p_ro_owner_id);
779 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
780 'p_ro_org_id = '||p_ro_org_id);
781 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
782 'p_ro_priority_id = '||p_ro_priority_id);
783 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
784 'p_ro_escalation_code = '||p_ro_escalation_code);
785 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
786 'p_note_type = '||p_note_type);
787 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
788 'p_note_visibility = '||p_note_visibility);
789 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
790 'p_attach_title = '||p_attach_title);
791 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
792 'p_attach_descr = '||p_attach_descr);
793 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
794 'p_attach_cat_id = '||p_attach_cat_id);
795 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
796 'p_attach_cat_id = '||p_attach_cat_id);
797 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
798 'p_attach_type = '||p_attach_type);
799 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
800 'p_attach_url = '||p_attach_url);
801 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
802 'p_attach_text = '||p_attach_text);
803
804 FOR i in 1 ..p_repair_line_ids.COUNT
805 LOOP
806 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
807 'p_repair_line_id('||i||') = '||p_repair_line_ids(i));
808 END LOOP;
809 END IF;
810 -- initialize the error messages table.
811 l_error_messages_tbl := JTF_VARCHAR2_TABLE_1000();
812
813 --
814 -- find out the values for update
815 --
816 -- processing logic.
817 -- each and every update action will be treated as a seperate sub transaction.
818 -- an error there will rollback only those changes and rest of the changes can still
819 -- go in.
820 -- eg. if we have repair status update, repair type update and ro owner update.
821 -- if status update fails only status update is rolled back, ro type update and
822 -- owner update should still be committed.
823
824 FOR J in 1 ..p_repair_line_ids.COUNT
825 LOOP
826 if p_to_ro_status is not null then
827 if NOT csd_repairs_pvt.is_flwsts_update_allowed
828 (p_repair_type_id => p_orig_ro_type_ids(j),
829 p_from_flow_status_id => p_from_ro_status(j) ,
830 p_to_flow_status_id => p_to_ro_status,
831 p_responsibility_id => FND_GLOBAL.RESP_ID)
832 then
833 -- flow status update is not allowed.
834 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_FLWSTS_NO_ACCESS');
835 Fnd_Msg_Pub.ADD;
836 prepare_error_message_table(l_error_messages_tbl,
837 0,
838 null,
839 'FLOW_STATUS',
840 p_repair_line_ids(j));
841 else
842 -- update the flow status.
843 begin
844 savepoint update_flow_status;
845 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
846 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
847 'Call update flow status API to update the status');
848 END IF;
849 csd_repairs_pvt.update_flow_status
850 ( p_api_version => 1.0,
851 p_commit => p_commit,
852 p_init_msg_list => p_init_msg_list,
853 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
854 x_return_status => x_return_status,
855 x_msg_count => x_msg_count,
856 x_msg_data => x_msg_Data,
857 p_repair_line_id => p_repair_line_ids(j),
858 p_repair_type_id => p_orig_ro_type_ids(j),
859 p_from_flow_Status_id => p_from_ro_status(j),
860 p_to_flow_status_id => p_to_ro_status,
861 p_reason_code => null,
862 p_comments => null,
863 p_check_access_flag => 'Y',
864 p_object_version_number => l_repobj_ver(j),
865 x_object_version_number => x_object_version_number
866 );
867
868 if x_return_status <> fnd_api.g_ret_sts_success then
869 prepare_error_message_table(l_error_messages_tbl,
870 x_msg_count,
871 x_msg_data,
872 'FLOW_STATUS',
873 p_repair_line_ids(j));
874 raise fnd_api.g_exc_error;
875 end if;
876
877 l_repobj_ver(j) := x_object_version_number;
878 exception
879 when fnd_api.g_exc_error then
880 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
881 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
882 'Error in update flow status API:'||x_msg_data);
883 END IF;
884 rollback to update_flow_status;
885 when others then
886 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
887 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
888 'When others exception:'||SQLERRM);
889 END IF;
890 rollback to update_flow_status;
891 raise;
892 end;
893
894 end if;
895 end if; -- end ro status is not null
896
897 -- repair type update
898 if p_ro_type_id is not null then
899 if NOT csd_repairs_pvt.is_rt_update_allowed
900 (p_from_repair_type_id => p_orig_ro_type_ids(j),
901 p_to_repair_type_id => p_ro_type_id,
902 p_common_flow_status_id => p_from_ro_status(j),
903 p_responsibility_id => fnd_global.resp_id)
904 then
905 -- repair type update is not allowed. log a message.
906 Fnd_Message.Set_Name('CSD', 'CSD_FLEX_RT_TRANS_NO_ACCESS');
907 Fnd_Msg_Pub.ADD;
908 prepare_error_message_table(l_error_messages_tbl,
909 0,
910 null,
911 'REPAIR_TYPE',
912 p_repair_line_ids(j));
913 else
914 -- update the repair type.
915 begin
916 savepoint update_repair_type;
917 x_return_status := FND_API.G_RET_STS_SUCCESS;
918 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
919 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
920 'Call update repair type API to update the status');
921 END IF;
922
923 csd_repairs_pvt.update_repair_type
924 ( p_api_version => 1.0,
925 p_commit => p_commit,
926 p_init_msg_list => p_init_msg_list,
927 p_validation_level => fnd_api.g_valid_level_full,
928 x_return_status => x_return_status,
929 x_msg_count => x_msg_count,
930 x_msg_data => x_msg_data,
931 p_repair_line_id => p_repair_line_ids(j),
932 p_from_repair_type_id => p_orig_ro_type_ids(j),
933 p_to_repair_type_id => p_ro_type_id,
934 p_common_flow_status_id => p_from_ro_status(j),
935 p_reason_code => null,
936 p_object_Version_number => l_repobj_ver(j),
937 x_object_version_number => x_object_version_number);
938
939 if x_return_status <> fnd_api.g_ret_sts_success then
940 prepare_error_message_table(l_error_messages_tbl,
941 x_msg_count,
942 x_msg_data,
943 'REPAIR_TYPE',
944 p_repair_line_ids(j));
945 raise fnd_api.g_exc_error;
946 end if;
947
948 l_repobj_ver(j) := x_object_version_number;
949 exception
950 when fnd_api.g_exc_error then
951 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
952 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
953 'Error in update repair type API:'||x_msg_data||' message count'||x_msg_count);
954 END IF;
955 rollback to update_repair_type;
956
957 when others then
958 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
959 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
960 'When others exception:'||SQLERRM);
961 END IF;
962 rollback to update_flow_status;
963 raise;
964 end;
965
966 end if;
967 end if; -- end ro type update.
968
969 -- update the remaining fields.
970 -- these can be updated by calling update_repair_order API.
971 --l_repln_rec_tbl(j).repair_line_id := p_repair_line_ids(j);
972 x_return_status := FND_API.G_RET_STS_SUCCESS;
973
974 l_repln_rec_tbl(j).object_version_number := l_repobj_ver(j);
975
976 if p_ro_owner_id is not null then
977 l_repln_rec_tbl(j).resource_id := p_ro_owner_id;
978 l_update_ros := true;
979 end if;
980
981 if p_ro_org_id is not null then
982 -- bug#8914410, subhat.
983 --l_repln_rec_tbl(j).repair_group_id := p_ro_org_id;
984 l_repln_rec_tbl(j).resource_group := p_ro_org_id;
985 l_update_ros := true;
986 end if;
987
988 if p_ro_priority_id is not null then
989 l_repln_rec_tbl(j).ro_priority_code := p_ro_priority_id;
990 l_update_ros := true;
991 end if;
992
993 if p_ro_escalation_code is not null then
994 l_repln_rec_tbl(j).escalation_code := p_ro_escalation_code;
995 l_update_ros := true;
996 end if;
997
998 -- support promise date update.
999 if p_ro_promise_date is not null then
1000 l_repln_rec_tbl(j).promise_date := p_ro_promise_date;
1001 l_update_ros := true;
1002 end if;
1003 END LOOP;
1004 if l_update_ros then
1005 -- update all the repair orders.
1006 for k in 1 ..l_repln_rec_tbl.count
1007 loop
1008 begin
1009 savepoint update_repair_order;
1010 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1011 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
1012 'Call update repair order API');
1013 END IF;
1014 csd_repairs_pvt.update_repair_order
1015 (p_api_version_number => 1.0,
1016 p_init_msg_list => p_init_msg_list,
1017 p_commit => p_commit,
1018 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
1019 p_repair_line_id => p_repair_line_ids(k),
1020 p_repln_rec => l_repln_rec_tbl(k),
1021 x_return_status => x_return_Status,
1022 x_msg_count => x_msg_count,
1023 x_msg_data => x_msg_data);
1024
1025 if x_return_status <> fnd_api.g_ret_sts_success then
1026 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1027 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
1028 'Return status from update_repair_order '||x_return_status);
1029 END IF;
1030 prepare_error_message_table(l_error_messages_tbl,
1031 x_msg_count,
1032 x_msg_data,
1033 'OTHER_ATTRIBUTES',
1034 p_repair_line_ids(k));
1035 raise fnd_api.g_exc_error;
1036 end if;
1037 exception
1038 when fnd_api.g_exc_error then
1039 rollback to update_repair_order;
1040 end;
1041 end loop;
1042 end if;
1043
1044 -- add attachments to the repair orders.
1045 if p_attach_type = 'File' and p_attach_file is not null then
1046 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1047 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
1048 'Adding file type attachment');
1049 END IF;
1050 x_return_status := FND_API.G_RET_STS_SUCCESS;
1051
1052 mass_create_attachments(p_api_version => 1.0,
1053 p_commit => p_commit,
1054 p_init_msg_list => p_init_msg_list,
1055 p_repair_line_ids => p_repair_line_ids,
1056 p_attach_type => 'FILE',
1057 p_file_input => p_attach_file,
1058 p_attach_cat_id => p_attach_cat_id,
1059 p_attach_descr => p_attach_descr,
1060 p_attach_title => p_attach_title,
1061 p_file_name => p_file_name,
1062 p_content_type => p_content_type,
1063 x_return_status => x_return_status,
1064 x_msg_count => x_msg_count,
1065 x_msg_data => x_msg_data
1066 );
1067 if x_return_status <> fnd_api.g_ret_sts_success then
1068 prepare_error_message_table(l_error_messages_tbl,
1069 x_msg_count,
1070 x_msg_data,
1071 'ATTACHMENT',
1072 p_repair_line_ids(1));
1073 end if;
1074
1075 elsif p_attach_type = 'Url' and p_attach_url is not null then
1076 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1077 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
1078 'Adding URL type attachment');
1079 END IF;
1080 x_return_status := FND_API.G_RET_STS_SUCCESS;
1081
1082 mass_create_attachments(p_api_version => 1.0,
1083 p_commit => p_commit,
1084 p_init_msg_list => p_init_msg_list,
1085 p_repair_line_ids => p_repair_line_ids,
1086 p_attach_type => 'URL',
1087 p_attach_cat_id => p_attach_cat_id,
1088 p_attach_descr => p_attach_descr,
1089 p_attach_title => p_attach_title,
1090 p_url => p_attach_url,
1091 x_return_status => x_return_status,
1092 x_msg_count => x_msg_count,
1093 x_msg_data => x_msg_data
1094 );
1095 if x_return_status <> fnd_api.g_ret_sts_success then
1096 prepare_error_message_table(l_error_messages_tbl,
1097 x_msg_count,
1098 x_msg_data,
1099 'ATTACHMENT',
1100 p_repair_line_ids(1));
1101 end if;
1102 elsif p_attach_type = 'Text' and p_attach_text is not null then
1103 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1104 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
1105 'Adding Text type attachment');
1106 END IF;
1107 x_return_status := FND_API.G_RET_STS_SUCCESS;
1108
1109 mass_create_attachments(p_api_version => 1.0,
1110 p_commit => p_commit,
1111 p_init_msg_list => p_init_msg_list,
1112 p_repair_line_ids => p_repair_line_ids,
1113 p_attach_type => 'TEXT',
1114 p_attach_cat_id => p_attach_cat_id,
1115 p_attach_descr => p_attach_descr,
1116 p_attach_title => p_attach_title,
1117 p_text => p_attach_text,
1118 x_return_status => x_return_status,
1119 x_msg_count => x_msg_count,
1120 x_msg_data => x_msg_data
1121 );
1122 if x_return_status <> fnd_api.g_ret_sts_success then
1123 prepare_error_message_table(l_error_messages_tbl,
1124 x_msg_count,
1125 x_msg_data,
1126 'ATTACHMENT',
1127 p_repair_line_ids(1));
1128 end if;
1129 end if;
1130
1131 -- last step. Create the note.
1132 if p_note_text is not null then
1133 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1134 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
1135 'Creating the notes for the selected repair orders');
1136 END IF;
1137 for l in 1 ..p_repair_line_ids.count
1138 loop
1139
1140 begin
1141 savepoint add_notes_to_ros;
1142 x_return_status := FND_API.G_RET_STS_SUCCESS;
1143 jtf_notes_pub.create_note
1144 ( p_api_version => 1.0,
1145 p_init_msg_list => p_init_msg_list,
1146 p_commit => p_commit,
1147 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1148 x_return_status => x_return_status,
1149 x_msg_count => x_msg_count,
1150 x_msg_data => x_msg_data,
1151 p_note_status => p_note_visibility,
1152 p_source_object_id => p_repair_line_ids(l),
1153 p_source_object_code => 'DR',
1154 p_notes => p_note_text,
1155 p_entered_date => sysdate,
1156 p_note_type => p_note_type,
1157 x_jtf_note_id => x_jtf_note_id
1158 );
1159 if x_return_status <> fnd_api.g_ret_sts_success then
1160 prepare_error_message_table(l_error_messages_tbl,
1161 x_msg_count,
1162 x_msg_data,
1163 'NOTES',
1164 p_repair_line_ids(l));
1165 raise fnd_api.g_exc_error;
1166 end if;
1167 exception
1168 when fnd_api.g_exc_error then
1169 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1170 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
1171 'Error while trying to create the note:'||x_msg_data);
1172 END IF;
1173 rollback to add_notes_to_ros;
1174 end;
1175 end loop;
1176 x_return_status := FND_API.G_RET_STS_SUCCESS;
1177 end if;
1178
1179 EXCEPTION
1180 WHEN FND_API.G_EXC_ERROR THEN
1181 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1182 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
1183 'Exc Error '||x_msg_data);
1184 END IF;
1185 rollback to mass_update_repair_orders;
1186 WHEN OTHERS THEN
1187 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1188 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, l_api_name,
1189 'In when others :'||SQLERRM);
1190 END IF;
1191 rollback to mass_update_repair_orders;
1192 raise;
1193 END mass_update_repair_orders;
1194
1195 /******************************************************************************/
1196 /* Procedure Name: mass_create_attachments */
1197 /* Description: The api provides utility to create attachments for a set of */
1198 /* repair orders. The API gets called from mass_update_repair_orders */
1199 /******************************************************************************/
1200 PROCEDURE mass_create_attachments(p_api_version IN NUMBER DEFAULT 1.0,
1201 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1202 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1203 p_repair_line_ids IN JTF_NUMBER_TABLE,
1204 p_attach_type IN VARCHAR2,
1205 p_attach_cat_id IN NUMBER,
1206 p_attach_descr IN VARCHAR2 DEFAULT NULL,
1207 p_attach_title IN VARCHAR2,
1208 p_file_input IN BLOB DEFAULT NULL,
1209 p_url IN VARCHAR2 DEFAULT NULL,
1210 p_text IN VARCHAR2 DEFAULT NULL,
1211 p_file_name IN VARCHAR2 DEFAULT NULL,
1212 p_content_type IN VARCHAR2 DEFAULT NULL,
1213 x_return_status OUT NOCOPY VARCHAR2,
1214 x_msg_count OUT NOCOPY NUMBER,
1215 x_msg_data OUT NOCOPY VARCHAR2
1216 )
1217 IS
1218 lc_api_name constant varchar(100) := 'CSD_REPAIR_MANAGER_UTIL.MASS_CREATE_ATTACHMENTS';
1219 l_api_version_number constant number := 1.0;
1220 lc_data_type_short_text constant number := 1;
1221 lc_data_type_long_text constant number := 2;
1222 lc_data_type_file constant number := 6;
1223 lc_data_type_url constant number := 5;
1224 l_data_type number;
1225
1226 x_rowid_tmp varchar2(100);
1227 x_document_id_tmp number;
1228
1229 l_user_id number := fnd_global.user_id;
1230 x_media_id number;
1231 l_file_format varchar2(20);
1232 l_oracle_charset varchar2(30);
1233 l_seq_num number;
1234
1235 BEGIN
1236 savepoint mass_create_attachments;
1237 -- standard stuff.
1238 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1239 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1240 'Begin mass create API');
1241 END IF;
1242 -- standard check for API compatibility.
1243 IF NOT Fnd_Api.Compatible_API_Call
1244 (l_api_version_number,
1245 p_api_version,
1246 lc_api_name,
1247 G_PKG_NAME)
1248 THEN
1249 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1250 END IF;
1251
1252 IF Fnd_Api.to_Boolean(p_init_msg_list)
1253 THEN
1254 Fnd_Msg_Pub.initialize;
1255 END IF;
1256 x_return_status := FND_API.G_RET_STS_SUCCESS;
1257 l_data_type := CASE p_attach_type
1258 when 'FILE' then lc_data_type_file
1259 when 'URL' then lc_data_type_url
1260 when 'TEXT' then lc_data_type_short_text
1261 END;
1262 -- common stuff. need to insert a record into fnd_documents table.
1263 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1264 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1265 'Before calling Fnd_Documents_Pkg.Insert_Row');
1266 END IF;
1267
1268 Fnd_Documents_Pkg.Insert_Row
1269 (X_Rowid => x_rowid_tmp,
1270 X_document_id => x_document_id_tmp,
1271 X_creation_date => sysdate,
1272 X_created_by => l_user_id,
1273 X_last_update_date => sysdate,
1274 X_last_updated_by => l_user_id,
1275 X_last_update_login => l_user_id,
1276 X_datatype_id => l_data_type,
1277 X_category_id => p_attach_cat_id,
1278 X_security_type => 4,
1279 X_publish_flag => 'Y',
1280 X_usage_type => 'O',
1281 X_language => userenv('LANG'),
1282 x_url => p_url,
1283 x_title => p_attach_title,
1284 x_description => p_attach_descr,
1285 x_media_id => x_media_id,
1286 x_file_name => p_file_name);
1287
1288 -- create the corresponding record in the document tables.
1289 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1290 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1291 'Inserting attachments of type short text');
1292 END IF;
1293
1294 if l_data_type = 1 then
1295 insert into fnd_documents_short_text
1296 ( media_id,
1297 short_text
1298 )
1299 values
1300 ( x_media_id,
1301 p_text
1302 );
1303
1304 elsif l_data_type = 6 then
1305 l_file_format := substr(p_content_type,0,instr(p_content_type,'/')-1);
1306
1307 -- the file format field in DB can hold only 10 charecters.
1308 -- for general files from linux system the file format is usually greater
1309 -- than 10 characters. To avoid error during insert we substr it to 10 characters.
1310 if length(l_file_format) > 10 then
1311 l_file_format := substr(l_file_format,0,10);
1312 end if;
1313
1314 select value into l_oracle_charset
1315 from nls_database_parameters
1316 where parameter = 'NLS_CHARACTERSET';
1317
1318 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1319 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1320 'Inserting attachments of type file into fnd_lobs table');
1321 END IF;
1322
1323 insert into fnd_lobs
1324 ( file_id,
1325 file_name,
1326 file_content_type,
1327 upload_date,
1328 expiration_date,
1329 program_name,
1330 program_tag,
1331 file_data,
1332 language,
1333 oracle_charset,
1334 file_format
1335 )
1336 values
1337 ( x_media_id,
1338 p_file_name,
1339 p_content_type,
1340 sysdate,
1341 null,
1342 null,
1343 null,
1344 p_file_input,
1345 userenv('LANG'),
1346 l_oracle_charset,
1347 l_file_format
1348 );
1349 end if;
1350
1351 -- create the records in the fnd_attached_documents for all the repair_line_ids.
1352 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1353 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1354 'Creating records in the fnd_attached_documents for all the passed repair orders');
1355 END IF;
1356 forall i in 1 ..p_repair_line_ids.count
1357
1358 INSERT INTO fnd_attached_documents
1359 (attached_document_id,
1360 document_id,
1361 creation_date,
1362 created_by,
1363 last_update_date,
1364 last_updated_by,
1365 last_update_login,
1366 seq_num,
1367 entity_name,
1368 pk1_value,
1369 pk2_value,
1370 pk3_value,
1371 pk4_value,
1372 pk5_value,
1373 automatically_added_flag,
1374 program_application_id,
1375 program_id,
1376 program_update_date,
1377 request_id,
1378 attribute_category,
1379 attribute1, attribute2,
1380 attribute3, attribute4,
1381 attribute5, attribute6,
1382 attribute7, attribute8,
1383 attribute9, attribute10,
1384 attribute11, attribute12,
1385 attribute13, attribute14,
1386 attribute15,
1387 column1,
1388 category_id)
1389
1390 (select
1391 fnd_attached_documents_s.NEXTVAL,
1392 x_document_id_tmp,
1393 sysdate,
1394 l_user_id,
1395 sysdate,
1396 l_user_id,
1397 l_user_id,
1398 5,
1399 --'Csd_RepairLineId',
1400 'CSD_REPAIRS', -- bug#9908690, subhat
1401 p_repair_line_ids(i),
1402 null,
1403 null,
1404 null,
1405 null,
1406 'Y',
1407 FND_GLOBAL.prog_appl_id,
1408 null,
1409 sysdate,
1410 null,
1411 null,
1412 null,null,
1413 null,null,
1414 null,null,
1415 null,null,
1416 null,null,
1417 null,null,
1418 null,null,
1419 null,
1420 null,
1421 p_attach_cat_id
1422 from dual);
1423 EXCEPTION
1424 WHEN OTHERS THEN
1425 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1426 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1427 'In when others exception: SQLERRM '||SQLERRM);
1428 END IF;
1429 x_return_status := FND_API.G_RET_STS_ERROR;
1430 x_msg_count := 1;
1431 x_msg_data := SQLERRM;
1432 rollback to mass_create_attachments;
1433 raise;
1434 END mass_create_attachments;
1435
1436 /******************************************************************************/
1437 /* Procedure Name: mass_create_repair_orders */
1438 /* Description: This is a OAF wrapper for creation of SR and repair orders. */
1439 /* OAF can call this with multiple records too. */
1440 /******************************************************************************/
1441 PROCEDURE mass_create_repair_orders(p_api_version IN NUMBER DEFAULT 1.0,
1442 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1443 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1444 p_item_ids IN JTF_NUMBER_TABLE,
1445 p_serial_numbers IN JTF_VARCHAR2_TABLE_100,
1446 p_quantity IN JTF_NUMBER_TABLE,
1447 p_uom_code IN JTF_VARCHAR2_TABLE_100,
1448 p_external_ref IN JTF_VARCHAR2_TABLE_100,
1449 p_lot_nums IN JTF_VARCHAR2_TABLE_100,
1450 p_item_revisions IN JTF_VARCHAR2_TABLE_100,
1451 p_repair_type_ids IN JTF_NUMBER_TABLE,
1452 p_instance_ids IN JTF_NUMBER_TABLE,
1453 p_serial_ctrl_flag IN JTF_NUMBER_TABLE,
1454 p_rev_ctrl_flag IN JTF_NUMBER_TABLE,
1455 p_ib_ctrl_flag IN JTF_VARCHAR2_TABLE_100,
1456 p_party_id IN NUMBER,
1457 p_account_id IN NUMBER,
1458 x_return_status OUT NOCOPY VARCHAR2,
1459 x_msg_count OUT NOCOPY NUMBER,
1460 x_msg_data OUT NOCOPY VARCHAR2,
1461 x_incident_id OUT NOCOPY NUMBER)
1462 IS
1463 l_api_version_number number := 1.0;
1464 lc_api_name constant varchar2(100) := 'CSD_REPAIR_MANAGER_UTIL.MASS_CREATE_REPAIR_ORDERS';
1465
1466 l_repln_tbl csd_repairs_pub.repln_tbl_type;
1467
1468 x_repair_line_id number;
1469 l_sr_rec sr_rec_type;
1470 l_repair_line_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1471
1472 BEGIN
1473
1474 -- standard stuff.
1475 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1476 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1477 'Begin mass create repair orders API');
1478 END IF;
1479 -- standard check for API compatibility.
1480 IF NOT Fnd_Api.Compatible_API_Call
1481 (l_api_version_number,
1482 p_api_version,
1483 lc_api_name,
1484 G_PKG_NAME)
1485 THEN
1486 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1487 END IF;
1488
1489 IF Fnd_Api.to_Boolean(p_init_msg_list)
1490 THEN
1491 Fnd_Msg_Pub.initialize;
1492 END IF;
1493 x_return_status := FND_API.G_RET_STS_SUCCESS;
1494
1495 -- this is wrapper around create_sr_repair_order API.
1496 -- we will prepare the SR and repair rec types and call the create_sr_repair_order
1497 -- api.
1498
1499 -- prepare the repln tbl_type.
1500 l_sr_rec.sr_account_id := p_account_id;
1501 l_sr_rec.sr_party_id := p_party_id;
1502
1503 for i in 1 ..p_item_ids.count
1504 loop
1505 l_repln_tbl(i).inventory_item_id := p_item_ids(i);
1506 l_repln_tbl(i).serial_number := p_serial_numbers(i);
1507 l_repln_tbl(i).quantity := p_quantity(i);
1508 l_repln_tbl(i).unit_of_measure := p_uom_code(i);
1509 l_repln_tbl(i).item_revision := p_item_revisions(i);
1510 l_repln_tbl(i).repair_type_id := p_repair_type_ids(i);
1511 l_repln_tbl(i).customer_product_id := p_instance_ids(i);
1512
1513 -- call the API to process the repair orders.
1514 create_sr_repair_order
1515 (p_api_version => 1,
1516 p_init_msg_list => fnd_api.g_false,
1517 p_commit => fnd_api.g_false,
1518 p_sr_rec => l_sr_rec,
1519 p_repln_rec => l_repln_tbl(i),
1520 p_rev_ctrl_flag => p_rev_ctrl_flag(i),
1521 p_serial_ctrl_flag => p_serial_ctrl_flag(i),
1522 p_ib_ctrl_flag => p_ib_ctrl_flag(i),
1523 x_incident_id => x_incident_id,
1524 x_repair_line_id => x_repair_line_id,
1525 x_return_status => x_return_status,
1526 x_msg_count => x_msg_count,
1527 x_msg_data => x_msg_data,
1528 p_external_reference => p_external_ref(i),
1529 p_lot_num => p_lot_nums(i)
1530 );
1531 -- create the internal order if the profile is set.
1532 l_repair_line_ids.EXTEND;
1533 l_repair_line_ids(i) := x_repair_line_id;
1534
1535 end loop;
1536 IF NVL(fnd_profile.value('CSD_AUTO_CREATE_INT_ORDER'),'N') = 'Y'
1537 THEN
1538 create_internal_orders(p_api_version => 1.0,
1539 p_init_msg_list => fnd_api.g_true,
1540 p_commit => fnd_api.g_false,
1541 p_repln_tbl => l_repln_tbl,
1542 p_repair_line_ids => l_repair_line_ids,
1543 x_return_status => x_return_status,
1544 x_msg_count => x_msg_count,
1545 x_msg_data => x_msg_data);
1546 END IF;
1547
1548 END mass_create_repair_orders;
1549
1550 /******************************************************************************/
1551 /* Procedure Name: mass_create_repair_orders_cp */
1552 /* Description: The concurrent wrapper to process the records from */
1553 /* csd_repairs_interface table. The API does minimal validation and then */
1554 /* calls create_sr_repair_order in a loop. */
1555 /******************************************************************************/
1556 procedure mass_create_repair_orders_cp(errbuf out nocopy varchar2,
1557 retcode out nocopy varchar2,
1558 p_one_sr_per_group in varchar2 default 'Y',
1559 p_group_id in number
1560 )
1561 is
1562 lc_api_name varchar2(100) := 'csd_repair_manager_util.mass_create_repair_orders_cp';
1563 --l_interface_rec_type csd.csd_repairs_interface%rowtype;
1564 type l_interface_tbl_type is table of csd_repairs_interface%rowtype index by binary_integer;
1565 --type repln_tbl_type is table of csd_repairs_pub.repln_rec_type;
1566 l_repln_tbl csd_repairs_pub.repln_tbl_type;
1567 l_interface_tbl l_interface_tbl_type;
1568 l_sr_tbl sr_tbl_type;
1569 l_dummy varchar2(1);
1570 l_continue_further boolean := true;
1571 x_incident_id number;
1572 x_repair_line_id number;
1573 x_return_status varchar2(3);
1574 x_msg_data varchar2(2000);
1575 x_msg_count number;
1576 l_repair_line_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1577
1578 begin
1579 -- the program logic.
1580 -- Step 1. Check if there is bare minimum information required to create the
1581 -- service request (with the assistance from Depot profiles).
1582 -- step 2. Check if there is bare minimum information for creation of repair orders.
1583 -- step 3. Prepare the service request record.
1584 -- step 4. Prepare the repair order table type.
1585 -- step 5. Call the helper API's to create the repair orders and product transaction lines.
1586
1587 -- Error logging. All the errors will be recorded in the FND_LOG_MESSAGES now.
1588 -- @to do. An interface errors table to log all the error messages.
1589 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1590 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1591 'Begin processing for group_id ='||p_group_id||' p_one_sr_per_group '||p_one_sr_per_group);
1592 END IF;
1593
1594 -- we will generate the transaction ids to identify each row.
1595 update csd_repairs_interface set transaction_id = csd_repairs_interface_s2.nextval
1596 where group_id = p_group_id;
1597
1598 select *
1599 bulk collect into l_interface_tbl
1600 from csd_repairs_interface
1601 where group_id = p_group_id
1602 and processing_phase = 1;
1603
1604 -- mark all the records as in progress for this group.
1605
1606 update csd_repairs_interface set processing_phase = 2
1607 where group_id = p_group_id;
1608
1609 -- we will commit the changes here. So that any subsequent reads will not pick up these records.
1610 commit;
1611
1612 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1613 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1614 'Found '||l_interface_tbl.count||' records to process');
1615 END IF;
1616 for i in 1 ..l_interface_tbl.count
1617 loop
1618 -- step 1.
1619 -- check if there is minimum information required to create the Service request.
1620 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1621 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1622 'Checking for SR account and party id');
1623 END IF;
1624 if l_interface_tbl(i).sr_incident_id is not null then
1625 -- user has passed incident id. Lets check if it exists in the system.
1626 begin
1627 select 'X'
1628 into l_dummy
1629 from cs_incidents_all_b
1630 where incident_number = l_interface_tbl(i).sr_incident_id;
1631 l_sr_tbl(i).sr_incident_id := l_interface_tbl(i).sr_incident_id;
1632 l_sr_tbl(i).create_sr_flag := 'N';
1633 exception
1634 when no_data_found then
1635 -- the passed incident id is invalid. skip the record.
1636 update csd_repairs_interface set processing_phase = 3
1637 where transaction_id = l_interface_tbl(i).transaction_id;
1638 l_continue_further := false;
1639 end;
1640
1641 elsif l_interface_tbl(i).sr_incident_id is null then
1642 if not (l_interface_tbl(i).sr_account_id is not null and
1643 l_interface_tbl(i).sr_party_id is not null ) then
1644 -- not enough data to create a new SR. So we cannot create RO for this rec.
1645 update csd_repairs_interface set processing_phase = 3
1646 where transaction_id = l_interface_tbl(i).transaction_id;
1647 l_continue_further := false;
1648 else
1649 l_sr_tbl(i).sr_account_id := l_interface_tbl(i).sr_account_id;
1650 l_sr_tbl(i).sr_party_id := l_interface_tbl(i).sr_party_id;
1651 l_sr_tbl(i).sr_incident_summary := l_interface_tbl(i).sr_incident_summary;
1652 l_sr_tbl(i).sr_bill_to_site_use_id := l_interface_tbl(i).sr_bill_to_site_use_id;
1653 l_sr_tbl(i).sr_ship_to_site_use_id := l_interface_tbl(i).sr_ship_to_site_use_id;
1654 l_sr_tbl(i).sr_type_id := l_interface_tbl(i).sr_type_id;
1655 l_sr_tbl(i).sr_status_id := l_interface_tbl(i).sr_status_id;
1656 l_sr_tbl(i).sr_severity_id := l_interface_tbl(i).sr_severity_id;
1657 l_sr_tbl(i).sr_urgency_id := l_interface_tbl(i).sr_urgency_id;
1658 l_sr_tbl(i).sr_owner_id := l_interface_tbl(i).sr_owner_id;
1659 l_sr_tbl(i).create_sr_flag := 'Y';
1660 end if;
1661 end if;
1662 -- step 2. Checking if we have enough information to create the repair order.
1663 if l_continue_further then
1664 if (l_interface_tbl(i).inventory_item_id is not null and
1665 l_interface_tbl(i).quantity is not null) then
1666 get_repln_rec(l_interface_tbl(i),
1667 l_repln_tbl(i));
1668 -- check if UOM is passed. if not, we will use the primary UOM.
1669 -- all the validations are as part of the core API's.
1670 if l_repln_tbl(i).unit_of_measure is null then
1671 select primary_uom_code
1672 into l_repln_tbl(i).unit_of_measure
1673 from mtl_system_items_b
1674 where inventory_item_id = l_repln_tbl(i).inventory_item_id
1675 and organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
1676 end if;
1677 else
1678 -- the record is not eligible for the RO creation.
1679 update csd_repairs_interface set processing_phase = 3
1680 where transaction_id = l_interface_tbl(i).transaction_id;
1681 end if;
1682 end if;
1683 -- Create the SR, RO and product transaction lines.
1684 csd_repair_manager_util.create_sr_repair_order
1685 (p_api_version => 1.0,
1686 p_init_msg_list => fnd_api.g_true,
1687 p_commit => fnd_api.g_false,
1688 p_sr_rec => l_sr_tbl(i),
1689 p_repln_rec => l_repln_tbl(i),
1690 p_rev_ctrl_flag => l_interface_tbl(i).revision_control_flag,
1691 p_serial_ctrl_flag => l_interface_tbl(i).serial_control_flag,
1692 p_ib_ctrl_flag => l_interface_tbl(i).ib_control_flag,
1693 x_incident_id => x_incident_id,
1694 x_repair_line_id => x_repair_line_id,
1695 x_return_status => x_return_status,
1696 x_msg_count => x_msg_count,
1697 x_msg_data => x_msg_data,
1698 p_external_reference => l_interface_tbl(i).external_reference,
1699 p_lot_num => l_interface_tbl(i).lot_number
1700 );
1701 if x_return_status <> fnd_api.g_ret_sts_success then
1702 update csd_repairs_interface set processing_phase = 4
1703 where transaction_id = l_interface_tbl(i).transaction_id;
1704 else
1705 update csd_repairs_interface set incident_id = x_incident_id,repair_line_id = x_repair_line_id
1706 where transaction_id = l_interface_tbl(i).transaction_id;
1707
1708
1709 if p_one_sr_per_group <> 'Y' then
1710 x_incident_id := null;
1711 end if;
1712 end if;
1713 -- create the internal order if the profile is set.
1714 l_repair_line_ids.EXTEND;
1715 l_repair_line_ids(i) := x_repair_line_id;
1716
1717 end loop;
1718
1719 IF NVL(fnd_profile.value('CSD_AUTO_CREATE_INT_ORDER'),'N') = 'Y'
1720 THEN
1721 create_internal_orders(p_api_version => 1.0,
1722 p_init_msg_list => fnd_api.g_true,
1723 p_commit => fnd_api.g_false,
1724 p_repln_tbl => l_repln_tbl,
1725 p_repair_line_ids => l_repair_line_ids,
1726 x_return_status => x_return_status,
1727 x_msg_count => x_msg_count,
1728 x_msg_data => x_msg_data);
1729 END IF;
1730
1731 -- write the concurrent program output.
1732 write_cp_output(p_group_id);
1733 -- delete all successfully processed records.
1734 delete from csd_repairs_interface where
1735 group_id = p_group_id and processing_phase = 2;
1736 commit;
1737 exception
1738 when fnd_api.g_exc_error then
1739 null;
1740 end mass_create_repair_orders_cp;
1741
1742 /******************************************************************************/
1743 /* Procedure Name: create_sr_repair_order */
1744 /* Description: Creates a service request, and repair order. The API delegates*/
1745 /* the call to private API's for creation of these entities. Upon creating*/
1746 /* repair orders, the API will also enter default logistics line. */
1747 /******************************************************************************/
1748 procedure create_sr_repair_order(p_api_version IN NUMBER,
1749 p_init_msg_list in varchar2 default fnd_api.g_false,
1750 p_commit in varchar2 default fnd_api.g_false,
1751 p_sr_rec in sr_rec_type,
1752 p_repln_rec in OUT NOCOPY csd_repairs_pub.repln_rec_type,
1753 p_rev_ctrl_flag in number,
1754 p_serial_ctrl_flag in number,
1755 p_ib_ctrl_flag in varchar2,
1756 x_incident_id IN OUT NOCOPY number,
1757 x_repair_line_id out nocopy number,
1758 x_return_status out nocopy varchar2,
1759 x_msg_count out nocopy number,
1760 x_msg_data out nocopy varchar2,
1761 p_external_reference in varchar2 default null,
1762 p_lot_num in varchar2 default null
1763 )
1764 is
1765 l_api_version_number constant number := 1.0;
1766 l_service_request_rec CSD_PROCESS_PVT.SERVICE_REQUEST_REC := CSD_PROCESS_UTIL.SR_REC;
1767 x_incident_number varchar2(30);
1768 l_sr_notes_tbl cs_servicerequest_pub.notes_table;
1769 lc_api_name constant varchar2(100) := 'csd_repair_manager_util.create_sr_repair_order';
1770 l_instance_rec csd_mass_rcv_pvt.instance_rec_type;
1771 l_create_instance boolean default false;
1772 l_rule_input_rec CSD_RULES_ENGINE_PVT.CSD_RULE_INPUT_REC_TYPE;
1773
1774 l_repair_type_id number;
1775 l_default_rule_id number;
1776 l_business_process_id number;
1777 l_server_tz_id number;
1778 l_ent_contracts OKS_ENTITLEMENTS_PUB.GET_CONTOP_TBL;
1779 l_calc_resptime_flag Varchar2(1) := 'Y';
1780 l_contract_pl_id number;
1781 l_profile_pl_id number;
1782 l_currency_code varchar2(5);
1783 --x_repair_line_id number;
1784 x_repair_number varchar2(30);
1785 l_auto_process_rma varchar2(1);
1786 l_approval_required varchar2(1);
1787 l_repair_mode varchar2(10);
1788 l_instance_id number;
1789 l_owner_party_id number;
1790 l_crt_inst_party_relation VARCHAR2(30) := fnd_profile.value('CSD_DEF_INST_PTY_RELTN');
1791 l_relationship VARCHAR2(30);
1792 l_create_tca_rel_in CSD_IB_CHOWN_CUHK.CREATE_TCA_REL_IN_REC_TYPE;
1793 l_create_tca_rel_out CSD_IB_CHOWN_CUHK.CREATE_TCA_REL_OUT_REC_TYPE;
1794 x_relationship_created VARCHAR2(1);
1795 x_tca_relation_id NUMBER;
1796 l_sr_created BOOLEAN;
1797
1798 begin
1799
1800 -- create SR.
1801 -- get the party type.
1802 savepoint create_sr_repair_order;
1803
1804 -- standard stuff.
1805 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1806 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1807 'Begin mass create repair orders API');
1808 END IF;
1809 -- standard check for API compatibility.
1810 IF NOT Fnd_Api.Compatible_API_Call
1811 (l_api_version_number,
1812 p_api_version,
1813 lc_api_name,
1814 G_PKG_NAME)
1815 THEN
1816 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1817 END IF;
1818
1819 IF Fnd_Api.to_Boolean(p_init_msg_list)
1820 THEN
1821 Fnd_Msg_Pub.initialize;
1822 END IF;
1823
1824 x_return_status := FND_API.G_RET_STS_SUCCESS;
1825
1826 if x_incident_id is null then
1827 begin
1828 select party_type
1829 into l_service_request_rec.caller_type
1830 from hz_parties
1831 where party_id = p_sr_rec.sr_party_id;
1832 exception
1833 when no_data_found then
1834 -- invalid party.
1835 raise fnd_api.g_exc_error;
1836 end;
1837
1838 if p_sr_rec.sr_bill_to_site_use_id is null then
1839 -- get the primary bill to site and ship to site.
1840 Select hpu.party_site_use_id
1841 into l_service_request_rec.bill_to_site_use_id
1842 from hz_party_sites hps,
1843 hz_party_site_uses hpu
1844 where
1845 hps.party_id = p_sr_rec.sr_party_id
1846 and hps.party_site_id = hpu.party_site_id
1847 and hpu.site_use_type = 'BILL_TO'
1848 and hpu.primary_per_type = 'Y'
1849 and rownum < 2;
1850 else
1851 l_service_request_rec.bill_to_site_use_id := p_sr_rec.sr_bill_to_site_use_id;
1852 end if;
1853
1854 if p_sr_rec.sr_ship_to_site_use_id is null then
1855 -- get the primary ship to site.
1856 Select hpu.party_site_use_id
1857 into l_service_request_rec.ship_to_site_use_id
1858 from hz_party_sites hps,
1859 hz_party_site_uses hpu
1860 where
1861 hps.party_id = p_sr_rec.sr_party_id
1862 and hps.party_site_id = hpu.party_site_id
1863 and hpu.site_use_type = 'SHIP_TO'
1864 and hpu.primary_per_type = 'Y'
1865 and rownum < 2;
1866 else
1867 l_service_request_rec.ship_to_site_use_id := p_sr_rec.sr_ship_to_site_use_id;
1868 end if;
1869
1870 -- assign and initialize service request rec.
1871 l_service_request_rec.request_date := sysdate;
1872
1873 if p_sr_rec.sr_type_id is null then
1874 l_service_request_rec.type_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_TYPE');
1875 else
1876 l_service_request_rec.type_id := p_sr_rec.sr_type_id;
1877 end if;
1878
1879 if p_sr_rec.sr_status_id is null then
1880 l_service_request_rec.status_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_STATUS');
1881 else
1882 l_service_request_rec.status_id := p_sr_rec.sr_status_id;
1883 end if;
1884
1885 if p_sr_rec.sr_severity_id is null then
1886 l_service_request_rec.severity_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_SEVERITY');
1887 else
1888 l_service_request_rec.severity_id := p_sr_rec.sr_severity_id;
1889 end if;
1890
1891 if p_sr_rec.sr_urgency_id is null then
1892 l_service_request_rec.urgency_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_URGENCY');
1893 else
1894 l_service_request_rec.urgency_id := p_sr_rec.sr_urgency_id;
1895 end if;
1896
1897 if p_sr_rec.sr_owner_id is null then
1898 l_service_request_rec.owner_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_OWNER');
1899 else
1900 l_service_request_rec.owner_id := p_sr_rec.sr_owner_id;
1901 end if;
1902
1903 if p_sr_rec.sr_incident_summary is null then
1904 l_service_request_rec.summary := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_SUMMARY');
1905 else
1906 l_service_request_rec.summary := p_sr_rec.sr_incident_summary;
1907 end if;
1908
1909 l_service_request_rec.sr_creation_channel := 'PHONE';
1910 l_service_request_rec.resource_type := FND_PROFILE.value('CS_SR_DEFAULT_OWNER_TYPE');
1911 l_service_request_rec.customer_id := p_sr_rec.sr_party_id;
1912 l_service_request_rec.account_id := p_sr_rec.sr_account_id;
1913 l_service_request_rec.customer_number := null;
1914 l_service_request_rec.customer_product_id := null;
1915 l_service_request_rec.cp_ref_number := null;
1916 l_service_request_rec.inv_item_revision := null;
1917 l_service_request_rec.inventory_item_id := null;
1918 l_service_request_rec.inventory_org_id := null;
1919 l_service_request_rec.current_serial_number := null;
1920 l_service_request_rec.original_order_number := null;
1921 l_service_request_rec.purchase_order_num := null;
1922 l_service_request_rec.problem_code := null;
1923 l_service_request_rec.exp_resolution_date := null;
1924 l_service_request_rec.contract_id := null;
1925 l_service_request_rec.cust_po_number := null;
1926 l_service_request_rec.cp_revision_id := null;
1927 l_service_request_rec.sr_contact_point_id := null;
1928 l_service_request_rec.party_id := null;
1929 l_service_request_rec.contact_point_id := null;
1930 l_service_request_rec.contact_point_type := null;
1931 l_service_request_rec.primary_flag := null;
1932 l_service_request_rec.contact_type := null;
1933 l_service_request_rec.owner_group_id := NULL;
1934 l_service_request_rec.publish_flag := '';
1935
1936
1937 -- Call the Service Request API
1938 CSD_PROCESS_PVT.process_service_request
1939 ( p_api_version => 1.0,
1940 p_commit => fnd_api.g_false,
1941 p_init_msg_list => fnd_api.g_true,
1942 p_validation_level => fnd_api.g_valid_level_full,
1943 p_action => 'CREATE',
1944 p_incident_id => NULL,
1945 p_service_request_rec => l_service_request_rec,
1946 p_notes_tbl => l_sr_notes_tbl,
1947 x_incident_id => x_incident_id,
1948 x_incident_number => x_incident_number,
1949 x_return_status => x_return_status,
1950 x_msg_count => x_msg_count,
1951 x_msg_data => x_msg_data
1952 );
1953 if x_return_status <> fnd_api.g_ret_sts_success then
1954 raise fnd_api.g_exc_error;
1955 end if;
1956 end if;
1957
1958 -- create RO
1959 -- case1. revision controlled item.
1960 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1961 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
1962 'Try to default in the Revision if its not entered');
1963 END IF;
1964 if p_rev_ctrl_flag <> 1 then
1965 if p_repln_rec.item_revision is null and p_repln_rec.serial_number is not null then
1966 -- no revision is passed in. Need to get the default revision.
1967 select revision
1968 into p_repln_rec.item_revision
1969 from mtl_serial_numbers
1970 where serial_number = p_repln_rec.serial_number
1971 and inventory_item_id = p_repln_rec.inventory_item_id;
1972 end if;
1973 end if;
1974 -- case 2. check for ib control. Check if we need to create instance.
1975 if p_ib_ctrl_flag = 'Y' then
1976 if p_repln_rec.customer_product_id is null and p_repln_rec.serial_number is not null then
1977 -- if its Serial controlled. Check if the Instance already exists.
1978 begin
1979 select instance_id
1980 into l_instance_id
1981 from csi_item_instances
1982 where serial_number = p_repln_rec.serial_number
1983 and inventory_item_id = p_repln_rec.inventory_item_id;
1984 exception
1985 when no_data_found then
1986 l_create_instance := true;
1987 end;
1988 elsif p_repln_rec.customer_product_id is null and p_repln_rec.serial_number is not null then
1989 l_create_instance := true;
1990 elsif p_repln_rec.customer_product_id is null and p_repln_rec.serial_number is null then
1991 l_create_instance := true;
1992 else
1993 l_instance_id := p_repln_rec.customer_product_id;
1994 end if;
1995 if p_external_reference is not null then
1996 -- validate if there is an existing instance for the external ref.
1997 if l_instance_id is not null and p_external_reference is not null
1998 then
1999 update_external_reference(p_external_reference,
2000 l_instance_id,
2001 x_return_status,
2002 x_msg_count,
2003 x_msg_data);
2004 else
2005 begin
2006 select instance_id
2007 into l_instance_id
2008 from csi_item_instances
2009 where inventory_item_id = p_repln_rec.inventory_item_id
2010 and external_reference = p_external_reference;
2011 l_create_instance := false;
2012 update_external_reference(p_external_reference,
2013 l_instance_id,
2014 x_return_status,
2015 x_msg_count,
2016 x_msg_data);
2017 exception
2018 when no_data_found then
2019 l_create_instance := true;
2020 end;
2021 end if;
2022 end if; -- ext ref check.
2023 if l_create_instance then
2024 -- create a new instance.
2025
2026 -- get the party site use id. Cache this value. Needs to be executed only once.
2027 if g_install_site_use_id is null then
2028 Select ship_to_site_use_id,
2029 contract_id,
2030 bill_to_site_use_id,
2031 problem_code,
2032 incident_severity_id,
2033 contract_service_id
2034 into g_install_site_use_id,
2035 g_contract_id,
2036 g_bill_to_use_id,
2037 g_problem_code,
2038 g_severity_id,
2039 g_contract_service_id
2040 from cs_incidents_all_b
2041 where incident_id = x_incident_id;
2042 end if;
2043
2044 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2045 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2046 'Calling Create a new instance API');
2047 END IF;
2048 l_instance_rec.party_site_use_id := g_install_site_use_id;
2049 l_instance_rec.inventory_item_id := p_repln_rec.inventory_item_id;
2050 l_instance_rec.instance_id := null;
2051 l_instance_rec.instance_number := null;
2052 l_instance_rec.serial_number := p_repln_rec.serial_number;
2053 l_instance_rec.lot_number := p_lot_num;
2054 l_instance_rec.quantity := 1;
2055 l_instance_rec.uom := p_repln_rec.unit_of_measure;
2056 l_instance_rec.party_id := p_sr_rec.sr_party_id;
2057 l_instance_rec.account_id := p_sr_rec.sr_account_id;
2058 l_instance_rec.mfg_serial_number_flag := 'N';
2059 l_instance_rec.item_revision := p_repln_rec.item_revision;
2060 l_instance_rec.external_reference := p_external_reference;
2061
2062 csd_mass_rcv_pvt.create_item_instance (
2063 p_api_version => 1.0,
2064 p_init_msg_list => fnd_api.g_false,
2065 p_commit => fnd_api.g_false,
2066 p_validation_level => fnd_api.g_valid_level_full,
2067 x_return_status => x_return_status,
2068 x_msg_count => x_msg_count,
2069 x_msg_data => x_msg_data,
2070 px_instance_rec => l_instance_rec,
2071 x_instance_id => l_instance_id );
2072
2073 if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2074 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2075 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2076 'Error during instance creation '||x_msg_data);
2077 END IF;
2078
2079 end if;
2080 end if; -- end create instance.
2081 end if; -- end ib check.
2082
2083 IF l_instance_id IS NOT NULL AND NOT l_create_instance
2084 THEN
2085 -- need to check if the instance is owned by the customer and whether we need to
2086 -- create an instance association.
2087 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2088 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2089 'Checking for instance relation/ownership.');
2090 END IF;
2091
2092 BEGIN
2093 SELECT relationship_type_code
2094 INTO l_relationship
2095 FROM csi_i_parties
2096 WHERE instance_id = l_instance_id
2097 AND party_id = p_sr_rec.sr_party_id
2098 AND SYSDATE BETWEEN NVL(active_start_date, SYSDATE - 1) AND NVL(active_end_date,SYSDATE+1)
2099 AND relationship_type_code = NVL(l_crt_inst_party_relation,relationship_type_code)
2100 AND ROWNUM < 2;
2101 EXCEPTION
2102 WHEN NO_DATA_FOUND THEN
2103 l_relationship := NULL;
2104 END;
2105
2106 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2107 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2108 'l_crt_inst_party_relation is '||l_crt_inst_party_relation||' and l_relationship is '||l_relationship);
2109 END IF;
2110
2111 IF l_crt_inst_party_relation IS NOT NULL AND l_relationship IS NULL
2112 THEN
2113 -- need to create a new association.
2114 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2115 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2116 'Calling csd_repairs_util.create_inst_party_relation');
2117 END IF;
2118
2119 csd_repairs_util.create_inst_party_relation
2120 (p_api_version_number => 1.0,
2121 p_commit => 'F',
2122 p_init_msg_list => 'F',
2123 x_return_status => x_return_status,
2124 x_msg_count => x_msg_count,
2125 x_msg_data => x_msg_data,
2126 x_relationship_created => x_relationship_created,
2127 p_sr_party_id => p_sr_rec.sr_party_id,
2128 p_sr_account_id => p_sr_rec.sr_account_id,
2129 p_instance_id => l_instance_id,
2130 p_relationship_type => l_crt_inst_party_relation
2131 );
2132
2133 IF x_return_status <> fnd_api.g_ret_sts_success
2134 THEN
2135 RAISE fnd_api.g_exc_error;
2136 END IF;
2137
2138 ELSIF l_crt_inst_party_relation IS NULL AND l_relationship IS NULL
2139 THEN
2140 -- need to change the ownership if the owners differ.
2141 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2142 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2143 'Checking if the change ownership can be carried out.');
2144 END IF;
2145 -- we use bulk receive change IB owner profile here too. We cannot just go with the
2146 -- function security because in forms there are two popups before the ownership is actually carried out.
2147 -- todo: may be the profile needs to be renamed to be more generic.
2148
2149 IF l_owner_party_id <> p_sr_rec.sr_party_id
2150 THEN
2151 IF fnd_function.test('CSDREPLN_CHOWN_IB') AND fnd_profile.value('CSD_BLK_RCV_CHG_IB_OWNER') = 'Y'
2152 THEN
2153 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2154 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2155 'All conditions for change ownership met. Trying to change the instance ownership. Instance id:'||l_instance_id);
2156 END IF;
2157 -- change the ib ownership.
2158 l_create_tca_rel_in.instance_id := l_instance_id;
2159 l_create_tca_rel_in.new_owner_account_id := p_sr_rec.sr_account_id;
2160 l_create_tca_rel_in.new_owner_party_id := p_sr_rec.sr_party_id;
2161 l_create_tca_rel_in.current_owner_party_id := l_owner_party_id;
2162
2163 csd_ib_chown_cuhk.get_create_tca_rel_flag
2164 (p_in_param => l_create_tca_rel_in,
2165 x_out_param => l_create_tca_rel_out
2166 );
2167
2168 csd_repairs_util.change_item_ib_owner
2169 (p_create_tca_relation => l_create_tca_rel_out.create_tca_rel_flag,
2170 p_instance_id => l_instance_id,
2171 p_new_owner_account_id => p_sr_rec.sr_account_id,
2172 p_new_owner_party_id => p_sr_rec.sr_party_id,
2173 p_current_owner_party_id => l_owner_party_id,
2174 x_return_status => x_return_status,
2175 x_msg_count => x_msg_count,
2176 x_msg_data => x_msg_data,
2177 x_tca_relation_id => x_tca_relation_id
2178 );
2179 IF x_return_status <> fnd_api.g_ret_sts_success
2180 THEN
2181 RAISE fnd_api.g_exc_error;
2182 END IF;
2183
2184 ELSE
2185 -- the change ownership condition not met. exiting.
2186 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2187 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2188 'Change ownership criteria not met. Make sure the change ownerhsip function is granted and profile CSD_ALWAYS_CREATE_IB_INSTANCE is set to Always create instance');
2189 END IF;
2190
2191 fnd_message.set_name('CSD', 'CSD_INVALID_CUST_PROD');
2192 fnd_msg_pub.ADD;
2193 RAISE fnd_api.g_exc_error;
2194 END IF;
2195
2196 END IF;
2197 END IF;
2198 END IF;
2199
2200 -- defaults from service request
2201 if g_install_site_use_id is null then
2202 Select ship_to_site_use_id,
2203 contract_id,
2204 bill_to_site_use_id,
2205 problem_code,
2206 incident_severity_id,
2207 contract_service_id
2208 into g_install_site_use_id,
2209 g_contract_id,
2210 g_bill_to_use_id,
2211 g_problem_code,
2212 g_severity_id,
2213 g_contract_service_id
2214 from cs_incidents_all_b
2215 where incident_id = x_incident_id;
2216 end if;
2217 -- get the default repair type applying the defaulting rule.
2218 l_rule_input_rec.sr_customer_id := p_sr_rec.sr_party_id;
2219 l_rule_input_rec.sr_customer_account_id := p_sr_rec.sr_account_id;
2220 l_rule_input_rec.sr_bill_to_site_use_id := g_bill_to_use_id;
2221 l_rule_input_rec.sr_ship_to_site_use_id := g_install_site_use_id;
2222 l_rule_input_rec.sr_problem_code := g_problem_code;
2223 l_rule_input_rec.sr_contract_id := g_contract_id;
2224
2225 l_rule_input_rec.ro_item_id := p_repln_rec.inventory_item_id;
2226
2227 -- if the user hasnt passed the repair type then only default it.
2228 if p_repln_rec.repair_type_id is null then
2229
2230 l_repair_type_id := null;
2231 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2232 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2233 'Calling CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE to get default repair type');
2234 END IF;
2235
2236 CSD_RULES_ENGINE_PVT.GET_DEFAULT_VALUE_FROM_RULE(
2237 p_api_version_number => 1.0,
2238 p_init_msg_list => fnd_api.g_false,
2239 p_commit => fnd_api.g_false,
2240 p_validation_level => fnd_api.g_valid_level_full,
2241 p_entity_attribute_type => 'CSD_DEF_ENTITY_ATTR_RO',
2242 p_entity_attribute_code => 'REPAIR_TYPE',
2243 p_rule_input_rec => l_rule_input_rec,
2244 x_default_value => l_repair_type_id,
2245 x_rule_id => l_default_rule_id,
2246 x_return_status => x_return_status,
2247 x_msg_count => x_msg_count,
2248 x_msg_data => x_msg_data
2249 );
2250
2251 if l_default_rule_id is null then
2252 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2253 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2254 'No Default rules set up for repair type. Fetching it from profile');
2255 END IF;
2256 l_repair_type_id := FND_PROFILE.VALUE('CSD_DEFAULT_REPAIR_TYPE');
2257 end if;
2258
2259 if l_repair_type_id is null then
2260 -- trouble! We cant process this record. No further processing for this and hop
2261 -- over to next.
2262 -- to do.
2263 null;
2264 end if;
2265 else
2266 l_repair_type_id := p_repln_rec.repair_type_id;
2267 end if;
2268
2269 -- get the business process.
2270 if NOT g_repair_attrib_cache.exists(l_repair_type_id) then
2271 select business_process_id,
2272 auto_process_rma,
2273 repair_mode
2274 into l_business_process_id,
2275 l_auto_process_rma,
2276 l_repair_mode
2277 from csd_repair_types_b
2278 where repair_type_id = l_repair_type_id;
2279
2280 g_repair_attrib_cache(l_repair_type_id).business_process_id := l_business_process_id;
2281 g_repair_attrib_cache(l_repair_type_id).auto_process_rma := l_auto_process_rma;
2282 g_repair_attrib_cache(l_repair_type_id).repair_mode := l_repair_mode;
2283 else
2284 l_business_process_id := g_repair_attrib_cache(l_repair_type_id).business_process_id;
2285 l_auto_process_rma := g_repair_attrib_cache(l_repair_type_id).auto_process_rma;
2286 l_repair_mode := g_repair_attrib_cache(l_repair_type_id).repair_mode;
2287 end if;
2288 -- get the contract information.
2289 -- we can possibly cache the information here.
2290 fnd_profile.get('SERVER_TIMEZONE_ID', l_server_tz_id);
2291 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2292 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2293 'Calling CSD_REPAIRS_UTIL.GET_ENTITLEMENTS to get contracts if any.');
2294 END IF;
2295 CSD_REPAIRS_UTIL.GET_ENTITLEMENTS(
2296 p_api_version_number => 1.0,
2297 p_init_msg_list => fnd_api.g_false,
2298 p_commit => fnd_api.g_false,
2299 p_contract_number => null,
2300 p_service_line_id => null,
2301 p_customer_id => p_sr_rec.sr_party_id ,
2302 p_site_id => g_install_site_use_id,
2303 p_customer_account_id => p_sr_rec.sr_account_id,
2304 p_system_id => null,
2305 p_inventory_item_id => p_repln_rec.inventory_item_id,
2306 p_customer_product_id => l_instance_id,
2307 p_request_date => trunc(sysdate),
2308 p_validate_flag => 'Y',
2309 p_business_process_id => l_business_process_id,
2310 p_severity_id => g_severity_id,
2311 p_time_zone_id => l_server_tz_id,
2312 P_CALC_RESPTIME_FLAG => l_calc_resptime_flag,
2313 x_ent_contracts => l_ent_contracts,
2314 x_return_status => x_return_status,
2315 x_msg_count => x_msg_count,
2316 x_msg_data => x_msg_data);
2317
2318 if l_ent_contracts.count = 0 then
2319 p_repln_rec.contract_line_id := null;
2320 Else
2321
2322 For l_index in l_ent_contracts.FIRST..l_Ent_contracts.LAST
2323 Loop
2324 if (g_contract_id = l_ent_contracts(l_index).contract_id and
2325 g_contract_service_id = l_ent_contracts(l_index).service_line_id) then
2326
2327 p_repln_rec.contract_line_id := l_ent_contracts(l_index).service_line_id;
2328 exit;
2329
2330 end if;
2331 End Loop;
2332
2333 If (p_repln_rec.contract_line_id is null or
2334 p_repln_rec.contract_line_id = fnd_api.g_miss_num) then
2335 p_repln_rec.contract_line_id := l_ent_contracts(1).service_line_id;
2336 End if;
2337 end if;
2338
2339 -- get the ro pl,currency.
2340 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2341 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2342 'Calling csd_process_util.get_ro_default_curr_pl for defaulting pricelist and currency');
2343 END IF;
2344
2345 csd_process_util.get_ro_default_curr_pl
2346 ( p_api_version => 1.0,
2347 p_init_msg_list => fnd_api.g_false,
2348 p_incident_id => x_incident_id,
2349 p_repair_type_id => l_repair_type_id,
2350 p_ro_contract_line_id => p_repln_rec.contract_line_id,
2351 x_contract_pl_id => l_contract_pl_id,
2352 x_profile_pl_id => l_profile_pl_id,
2353 x_currency_code => l_currency_code,
2354 x_return_status => x_return_status,
2355 x_msg_count => x_msg_count,
2356 x_msg_data => x_msg_data );
2357
2358 If ( l_contract_pl_id is not null) then
2359 p_repln_rec.price_list_header_id := l_contract_pl_id;
2360 Elsif ( l_profile_pl_id is not null ) then
2361 p_repln_rec.price_list_header_id := l_profile_pl_id;
2362 End if;
2363
2364 p_repln_rec.currency_code := l_currency_code;
2365
2366 -- determine the repair order status.
2367 if ( p_repln_rec.serial_number is null and p_serial_ctrl_flag <> 1
2368 and p_repln_rec.quantity > 1 )
2369 then
2370 p_repln_rec.status := 'D';
2371 else
2372 p_repln_rec.status := 'O';
2373 end if;
2374
2375 -- populate the repair rec.
2376 -- bug#8919683, subhat.
2377 -- If there is null value passed in, we will try to default the attributes.
2378 if p_repln_rec.inventory_org_id is null then
2379 p_repln_rec.inventory_org_id := fnd_api.g_miss_num;
2380 end if;
2381 if p_repln_rec.resource_group is null then
2382 p_repln_rec.resource_group := fnd_api.g_miss_num;
2383 end if;
2384 if p_repln_rec.ro_priority_code is null then
2385 p_repln_rec.ro_priority_code := fnd_api.g_miss_char;
2386 end if;
2387 if p_repln_rec.resource_id is null then
2388 p_repln_rec.resource_id := fnd_api.g_miss_num;
2389 end if;
2390 p_repln_rec.incident_id := x_incident_id;
2391 p_repln_rec.customer_product_id := l_instance_id;
2392 p_repln_rec.auto_process_rma := l_auto_process_rma;
2393 p_repln_rec.approval_required_flag := g_approval_required_flag;
2394 p_repln_rec.repair_type_id := l_repair_type_id;
2395 p_repln_rec.repair_group_id := null;
2396 p_repln_rec.repair_type_id := l_repair_type_id;
2397 p_repln_rec.repair_mode := l_repair_mode;
2398 p_repln_rec.incident_id := x_incident_id;
2399
2400 -- call the create repair orders API.
2401 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2402 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2403 'Just before calling CSD_REPAIRS_PVT.Create_Repair_Order to create repair order');
2404 END IF;
2405 x_repair_line_id := null;
2406
2407 CSD_REPAIRS_PVT.Create_Repair_Order
2408 (p_api_version_number => 1.0,
2409 p_commit => fnd_api.g_false,
2410 p_init_msg_list => fnd_api.g_true,
2411 p_validation_level => fnd_api.g_valid_level_full,
2412 p_repair_line_id => x_repair_line_id,
2413 p_Repln_Rec => p_repln_rec,
2414 x_repair_line_id => x_repair_line_id,
2415 x_repair_number => x_repair_number,
2416 x_return_status => x_return_status,
2417 x_msg_count => x_msg_count,
2418 x_msg_data => x_msg_data
2419 );
2420
2421 if x_return_status <> fnd_api.g_ret_sts_success then
2422 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2423 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2424 'Error in creation of repair order for item = '||p_repln_rec.inventory_item_id);
2425 END IF;
2426 raise fnd_api.g_exc_error;
2427 end if;
2428
2429 -- create the default logistics lines
2430 csd_process_pvt.create_default_prod_txn
2431 (p_api_version => 1.0,
2432 p_commit => fnd_api.g_false,
2433 p_init_msg_list => fnd_api.g_true,
2434 p_validation_level => fnd_api.g_valid_level_full,
2435 p_repair_line_id => x_repair_line_id,
2436 x_return_status => x_return_status,
2437 x_msg_count => x_msg_count,
2438 x_msg_data => x_msg_data);
2439
2440 if x_return_status <> fnd_api.g_ret_sts_success then
2441 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2442 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2443 'Error in creation of product transaction lines '||x_msg_data);
2444 END IF;
2445 raise fnd_api.g_exc_error;
2446 end if;
2447
2448 if fnd_api.to_boolean(p_commit) then
2449 commit work;
2450 end if;
2451 exception
2452 when fnd_api.g_exc_error then
2453 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2454 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2455 'In exec error exception '||x_msg_data);
2456 END IF;
2457 x_return_status := fnd_api.g_ret_sts_error;
2458 rollback to create_sr_repair_order;
2459 when no_data_found then
2460 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2461 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2462 'In No data found exception ');
2463 END IF;
2464 x_return_status := fnd_api.g_ret_sts_error;
2465 rollback to create_sr_repair_order;
2466 when others then
2467 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2468 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE, lc_api_name,
2469 'In when others exception.'||SQLERRM);
2470 END IF;
2471 rollback to create_sr_repair_order;
2472 raise;
2473 end create_sr_repair_order;
2474
2475 procedure update_external_reference
2476 (p_external_reference in varchar2,
2477 p_instance_id in number,
2478 x_return_status OUT NOCOPY varchar2,
2479 x_msg_count OUT NOCOPY number,
2480 x_msg_data OUT NOCOPY varchar2)
2481 is
2482 lc_api_name constant varchar2(100) := 'csd_repair_manager_util.update_external_reference';
2483 l_object_version_number number;
2484 l_instance_rec csi_datastructures_pub.instance_rec;
2485 l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2486 l_party_tbl csi_datastructures_pub.party_tbl;
2487 l_account_tbl csi_datastructures_pub.party_account_tbl;
2488 l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
2489 l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
2490 l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
2491 l_txn_rec csi_datastructures_pub.transaction_rec;
2492 x_instance_id_lst csi_datastructures_pub.id_tbl;
2493 x_msg_index_out number;
2494 begin
2495
2496 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2497 fnd_log.STRING (fnd_log.level_procedure,
2498 lc_api_name,
2499 'begin');
2500 End if;
2501
2502 select object_version_number
2503 into l_object_version_number
2504 from csi_item_instances
2505 where instance_id = p_instance_id;
2506
2507 l_instance_rec.instance_id := p_instance_id;
2508 l_instance_rec.external_reference := p_external_reference;
2509 l_instance_rec.object_version_number := l_object_version_number;
2510
2511 l_txn_rec.transaction_date := sysdate;
2512 l_txn_rec.source_transaction_date := sysdate;
2513 l_txn_rec.transaction_type_id := 1;
2514 -- call the update item instance API.
2515 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2516 fnd_log.STRING (fnd_log.level_procedure,
2517 lc_api_name,
2518 'Before calling csi_item_instance_pub.update_item_instance');
2519 End if;
2520 csi_item_instance_pub.update_item_instance
2521 (
2522 p_api_version => 1.0,
2523 p_commit => fnd_api.g_false,
2524 p_init_msg_list => fnd_api.g_true,
2525 p_validation_level => fnd_api.g_valid_level_full,
2526 p_instance_rec => l_instance_rec,
2527 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
2528 p_party_tbl => l_party_tbl,
2529 p_account_tbl => l_account_tbl,
2530 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
2531 p_org_assignments_tbl => l_org_assignments_tbl,
2532 p_asset_assignment_tbl => l_asset_assignment_tbl,
2533 p_txn_rec => l_txn_rec,
2534 x_instance_id_lst => x_instance_id_lst,
2535 x_return_status => x_return_status,
2536 x_msg_count => x_msg_count,
2537 x_msg_data => x_msg_data
2538 );
2539
2540 if not (x_return_status = FND_API.g_ret_sts_success) then
2541 raise fnd_api.g_exc_error;
2542 end if;
2543
2544
2545 exception
2546 when fnd_api.g_exc_error then
2547 FOR j in 1 ..x_msg_count
2548 LOOP
2549 FND_MSG_PUB.Get(
2550 p_msg_index => j,
2551 p_encoded => 'F',
2552 p_data => x_msg_data,
2553 p_msg_index_out => x_msg_index_out);
2554 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2555 fnd_log.STRING (fnd_log.level_procedure,
2556 lc_api_name,
2557 'Update external ref err '||x_msg_count||' Message '||x_msg_data);
2558 End if;
2559
2560 END LOOP;
2561 when no_data_found then
2562 If (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) then
2563 fnd_log.STRING (fnd_log.level_procedure,
2564 lc_api_name,
2565 'Cannot get the object version number.Invalid Instance Id passed');
2566 End if;
2567 end update_external_reference;
2568
2569 /**
2570 Aggregates the given string table type of JTF_VARCHAR2_TABLE.
2571 The return string would be , delimited list of individual
2572 entries in the table type. If the minimum DB version for
2573 APPS becomes 11g use inbuilt STRAGGR method instead of this.
2574 **/
2575
2576 FUNCTION string_aggregate(input IN JTF_VARCHAR2_TABLE_100)
2577 RETURN VARCHAR2
2578 IS
2579
2580 l_value VARCHAR2(4000) := '';
2581 l_first BOOLEAN := TRUE;
2582
2583 BEGIN
2584 IF input.COUNT = 0
2585 THEN
2586 RETURN l_value;
2587 END IF;
2588
2589 FOR i IN 1 ..input.COUNT
2590 LOOP
2591 IF l_first
2592 THEN
2593 l_value := input(i);
2594 l_first := FALSE;
2595 ELSE
2596 l_value := l_value||','||input(i);
2597 END IF;
2598 END LOOP;
2599 RETURN l_value;
2600 END string_aggregate;
2601
2602 END CSD_REPAIR_MANAGER_UTIL;