[Home] [Help]
PACKAGE BODY: APPS.CSD_RETURNS_UTIL
Source
1 PACKAGE BODY CSD_RETURNS_UTIL AS
2 /* $Header: csduretb.pls 120.6.12020000.3 2013/04/09 05:13:32 subhat ship $ */
3 -- Start of Comments
4 -- Package name : CSD_RETURNS_UTIL
5 -- Purpose : Aug-21-2011 subhat created
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_RETURNS_UTIL';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csduretb.pls';
12
13 G_LEVEL_PROCEDURE NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 G_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
15
16 G_RET_STS_SUCCESS VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
17 G_RET_STS_ERROR VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
18
19 TYPE selling_price_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
20 G_SELLING_PRICE_CACHE selling_price_tbl;
21
22 TYPE estimate_constants IS RECORD
23 (transaction_type_id NUMBER,
24 txn_billing_type_id NUMBER,
25 business_process_id NUMBER
26 );
27
28 TYPE estimate_constants_tbl IS TABLE OF estimate_constants INDEX BY BINARY_INTEGER;
29 G_ESTIMATE_CONSTANTS_CACHE estimate_constants_tbl;
30
31 /** Private routine.
32 */
33 PROCEDURE create_card
34 (p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2,
36 p_commit IN VARCHAR2,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2,
40 p_card_instrument IN iby_fndcpt_setup_pub.creditcard_rec_type,
41 x_card_id OUT NOCOPY NUMBER,
42 x_response OUT NOCOPY iby_fndcpt_common_pub.result_rec_type
43 );
44
45 PROCEDURE get_warranty_status
46 (p_api_version IN NUMBER DEFAULT 1.0,
47 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE,
48 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
49 p_item_ids IN JTF_NUMBER_TABLE,
50 p_serial_nums IN JTF_VARCHAR2_TABLE_100,
51 p_customer_id IN NUMBER,
52 p_account_id IN NUMBER,
53 p_bill_to_site_use_id IN NUMBER,
54 p_ship_to_site_use_id IN NUMBER,
55 p_instance_ids IN JTF_NUMBER_TABLE,
56 x_contract_ids OUT NOCOPY JTF_NUMBER_TABLE,
57 x_contract_line_ids OUT NOCOPY JTF_NUMBER_TABLE,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2
61 )
62 IS
63 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_RETURNS_UTIL.get_warranty_status';
64 lc_api_version CONSTANT NUMBER := 1.0;
65 l_business_process NUMBER;
66 x_ent_contracts oks_entitlements_pub.get_contop_tbl;
67
68 BEGIN
69
70 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
71 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
72 END IF;
73
74 -- standard check for API compatibility.
75 IF NOT Fnd_Api.Compatible_API_Call
76 (lc_api_version,
77 p_api_version,
78 lc_api_name,
79 G_PKG_NAME)
80 THEN
81 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
82 END IF;
83
84 IF Fnd_Api.to_Boolean(p_init_msg_list) THEN
85 Fnd_Msg_Pub.initialize;
86 END IF;
87
88 x_return_status := G_RET_STS_SUCCESS;
89 -- processing logic.
90 -- loop through the each item to see if its under warranty or not.
91 -- any errors during calculation of warranty to be treated as item not under warranty.
92 x_contract_ids := JTF_NUMBER_TABLE();
93 x_contract_line_ids := JTF_NUMBER_TABLE();
94
95 FOR i IN 1 ..p_item_ids.COUNT
96 LOOP
97 -- clean up contract table type.
98 x_ent_contracts.DELETE;
99 -- get the business process if for Depot Repair.
100 SELECT business_process_id
101 INTO l_business_process
102 FROM cs_business_processes
103 WHERE NAME = 'Depot Repair'
104 AND ROWNUM < 2;
105 -- call the API to get the contract details.
106 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
107 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling csd_repairs_util.GET_ENTITLEMENTS for item_id = '||p_item_ids(i));
108 END IF;
109 csd_repairs_util.GET_ENTITLEMENTS
110 ( p_api_version_number => 1.0,
111 p_init_msg_list => fnd_api.g_true,
112 p_commit => fnd_api.g_false,
113 p_contract_number => NULL,
114 p_service_line_id => NULL,
115 p_customer_id => p_customer_id,
116 p_site_id => p_ship_to_site_use_id,
117 p_customer_account_id => p_account_id,
118 p_system_id => NULL,
119 p_inventory_item_id => p_item_ids(i),
120 p_customer_product_id => p_instance_ids(i),
121 p_request_date => TRUNC(SYSDATE),
122 p_validate_flag => 'Y',
123 p_business_process_id => l_business_process,
124 p_severity_id => fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_SEVERITY'),
125 p_time_zone_id => TO_NUMBER(fnd_profile.value('SERVER_TIMEZONE_ID')),
126 p_calc_resptime_flag => 'Y',
127 x_ent_contracts => x_ent_contracts,
128 x_return_status => x_return_status,
129 x_msg_count => x_msg_count,
130 x_msg_data => x_msg_data);
131
132 IF x_ent_contracts.COUNT > 0
133 THEN
134 x_contract_ids.EXTEND;
135 x_contract_ids(i) := x_ent_contracts(1).contract_id;
136 x_contract_line_ids.EXTEND;
137 x_contract_line_ids(i) := x_ent_contracts(1).service_line_id;
138 ELSE
139 x_contract_ids.EXTEND;
140 x_contract_ids(i) := NULL;
141 x_contract_line_ids.EXTEND;
142 x_contract_line_ids(i) := NULL;
143 END IF;
144 END LOOP;
145 END get_warranty_status;
146
147 PROCEDURE return_parameter_value
148 (p_api_version IN NUMBER DEFAULT 1.0,
149 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE,
150 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
151 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
152 p_param_code IN VARCHAR2,
153 p_return_type IN NUMBER,
154 p_customer_id IN NUMBER,
155 p_bill_to_site_use_id IN NUMBER,
156 p_ship_to_site_use_id IN NUMBER,
157 p_item_id IN NUMBER,
158 p_item_category_id IN NUMBER,
159 p_contract_id IN NUMBER,
160 x_param_value OUT NOCOPY VARCHAR2,
161 x_return_status OUT NOCOPY VARCHAR2,
162 x_msg_count OUT NOCOPY NUMBER,
163 x_msg_data OUT NOCOPY VARCHAR2
164 )
165 IS
166 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_RETURNS_UTIL.return_parameter_value';
167 lc_api_version CONSTANT NUMBER := 1.0;
168 l_rule_input_rec CSD_PARAMETER_EFFECTIVITY_PVT.RULE_INPUT_REC_TYPE;
169 l_param_id NUMBER;
170
171 BEGIN
172 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
173 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
174 END IF;
175
176 -- standard check for API compatibility.
177 IF NOT Fnd_Api.Compatible_API_Call
178 (lc_api_version,
179 p_api_version,
180 lc_api_name,
181 G_PKG_NAME)
182 THEN
183 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
184 END IF;
185
186 IF Fnd_Api.to_Boolean(p_init_msg_list) THEN
187 Fnd_Msg_Pub.initialize;
188 END IF;
189
190 x_return_status := G_RET_STS_SUCCESS;
191
192 l_rule_input_rec.return_type := p_return_type;
193 l_rule_input_rec.customer_id := p_customer_id;
194 l_rule_input_rec.bill_to_site_use_id := p_bill_to_site_use_id;
195 l_rule_input_rec.ship_to_site_use_id := p_ship_to_site_use_id;
196 l_rule_input_rec.item_id := p_item_id;
197 l_rule_input_rec.item_category_id := p_item_category_id;
198 l_rule_input_rec.contract_id := p_contract_id;
199
200 -- get the parameter_id
201 BEGIN
202 SELECT parameter_id
203 INTO l_param_id
204 FROM csd_parameters
205 WHERE param_code = p_param_code;
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 RETURN;
209 END;
210
211 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
212 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling CSD_PARAMETER_EFFECTIVITY_PVT.PARAMETER_VALUE for param_id = '||l_param_id);
213 END IF;
214
215 CSD_PARAMETER_EFFECTIVITY_PVT.PARAMETER_VALUE
216 (p_api_version => 1.0,
217 p_init_msg_list => p_init_msg_list,
218 p_commit => p_commit,
219 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
220 p_param_id => l_param_id,
221 p_rule_input_rec => l_rule_input_rec,
222 x_param_value => x_param_value,
223 x_return_status => x_return_status,
224 x_msg_count => x_msg_count,
225 x_msg_data => x_msg_data
226 );
227
228 END return_parameter_value;
229
230 PROCEDURE get_estimated_price
231 (p_api_version IN NUMBER DEFAULT 1.0,
232 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE,
233 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
234 p_item_ids IN JTF_NUMBER_TABLE,
235 p_contract_ids IN JTF_NUMBER_TABLE,
236 p_contract_line_ids IN JTF_NUMBER_TABLE,
237 p_repair_type_ids IN JTF_NUMBER_TABLE,
238 p_uom_codes IN JTF_VARCHAR2_TABLE_100,
239 p_quantities IN JTF_NUMBER_TABLE,
240 p_pricing_ATTRIBUTE1 IN JTF_VARCHAR2_TABLE_300,
241 p_pricing_ATTRIBUTE2 IN JTF_VARCHAR2_TABLE_300,
242 p_pricing_ATTRIBUTE3 IN JTF_VARCHAR2_TABLE_300,
243 p_pricing_ATTRIBUTE4 IN JTF_VARCHAR2_TABLE_300,
244 p_pricing_ATTRIBUTE5 IN JTF_VARCHAR2_TABLE_300,
245 p_pricing_ATTRIBUTE6 IN JTF_VARCHAR2_TABLE_300,
246 p_pricing_ATTRIBUTE7 IN JTF_VARCHAR2_TABLE_300,
247 p_pricing_ATTRIBUTE8 IN JTF_VARCHAR2_TABLE_300,
248 p_pricing_ATTRIBUTE9 IN JTF_VARCHAR2_TABLE_300,
249 p_pricing_ATTRIBUTE10 IN JTF_VARCHAR2_TABLE_300,
250 p_pricing_ATTRIBUTE11 IN JTF_VARCHAR2_TABLE_300,
251 p_pricing_ATTRIBUTE12 IN JTF_VARCHAR2_TABLE_300,
252 p_pricing_ATTRIBUTE13 IN JTF_VARCHAR2_TABLE_300,
253 p_pricing_ATTRIBUTE14 IN JTF_VARCHAR2_TABLE_300,
254 p_pricing_ATTRIBUTE15 IN JTF_VARCHAR2_TABLE_300,
255 p_pricing_ATTRIBUTE16 IN JTF_VARCHAR2_TABLE_300,
256 p_pricing_ATTRIBUTE17 IN JTF_VARCHAR2_TABLE_300,
257 p_pricing_ATTRIBUTE18 IN JTF_VARCHAR2_TABLE_300,
258 p_pricing_ATTRIBUTE19 IN JTF_VARCHAR2_TABLE_300,
259 p_pricing_ATTRIBUTE20 IN JTF_VARCHAR2_TABLE_300,
260 p_pricing_ATTRIBUTE21 IN JTF_VARCHAR2_TABLE_300,
261 p_pricing_ATTRIBUTE22 IN JTF_VARCHAR2_TABLE_300,
262 p_pricing_ATTRIBUTE23 IN JTF_VARCHAR2_TABLE_300,
263 p_pricing_ATTRIBUTE24 IN JTF_VARCHAR2_TABLE_300,
264 p_pricing_ATTRIBUTE25 IN JTF_VARCHAR2_TABLE_300,
265 p_pricing_ATTRIBUTE26 IN JTF_VARCHAR2_TABLE_300,
266 p_pricing_ATTRIBUTE27 IN JTF_VARCHAR2_TABLE_300,
267 p_pricing_ATTRIBUTE28 IN JTF_VARCHAR2_TABLE_300,
268 p_pricing_ATTRIBUTE29 IN JTF_VARCHAR2_TABLE_300,
269 p_pricing_ATTRIBUTE30 IN JTF_VARCHAR2_TABLE_300,
270 p_pricing_ATTRIBUTE31 IN JTF_VARCHAR2_TABLE_300,
271 p_pricing_ATTRIBUTE32 IN JTF_VARCHAR2_TABLE_300,
272 p_pricing_ATTRIBUTE33 IN JTF_VARCHAR2_TABLE_300,
273 p_pricing_ATTRIBUTE34 IN JTF_VARCHAR2_TABLE_300,
274 p_pricing_ATTRIBUTE35 IN JTF_VARCHAR2_TABLE_300,
275 p_pricing_ATTRIBUTE36 IN JTF_VARCHAR2_TABLE_300,
276 p_pricing_ATTRIBUTE37 IN JTF_VARCHAR2_TABLE_300,
277 p_pricing_ATTRIBUTE38 IN JTF_VARCHAR2_TABLE_300,
278 p_pricing_ATTRIBUTE39 IN JTF_VARCHAR2_TABLE_300,
279 p_pricing_ATTRIBUTE40 IN JTF_VARCHAR2_TABLE_300,
280 p_pricing_ATTRIBUTE41 IN JTF_VARCHAR2_TABLE_300,
281 p_pricing_ATTRIBUTE42 IN JTF_VARCHAR2_TABLE_300,
282 p_pricing_ATTRIBUTE43 IN JTF_VARCHAR2_TABLE_300,
283 p_pricing_ATTRIBUTE44 IN JTF_VARCHAR2_TABLE_300,
284 p_pricing_ATTRIBUTE45 IN JTF_VARCHAR2_TABLE_300,
285 p_pricing_ATTRIBUTE46 IN JTF_VARCHAR2_TABLE_300,
286 p_pricing_ATTRIBUTE47 IN JTF_VARCHAR2_TABLE_300,
287 p_pricing_ATTRIBUTE48 IN JTF_VARCHAR2_TABLE_300,
288 p_pricing_ATTRIBUTE49 IN JTF_VARCHAR2_TABLE_300,
289 p_pricing_ATTRIBUTE50 IN JTF_VARCHAR2_TABLE_300,
290 p_pricing_ATTRIBUTE51 IN JTF_VARCHAR2_TABLE_300,
291 p_pricing_ATTRIBUTE52 IN JTF_VARCHAR2_TABLE_300,
292 p_pricing_ATTRIBUTE53 IN JTF_VARCHAR2_TABLE_300,
293 p_pricing_ATTRIBUTE54 IN JTF_VARCHAR2_TABLE_300,
294 p_pricing_ATTRIBUTE55 IN JTF_VARCHAR2_TABLE_300,
295 p_pricing_ATTRIBUTE56 IN JTF_VARCHAR2_TABLE_300,
296 p_pricing_ATTRIBUTE57 IN JTF_VARCHAR2_TABLE_300,
297 p_pricing_ATTRIBUTE58 IN JTF_VARCHAR2_TABLE_300,
298 p_pricing_ATTRIBUTE59 IN JTF_VARCHAR2_TABLE_300,
299 p_pricing_ATTRIBUTE60 IN JTF_VARCHAR2_TABLE_300,
300 p_pricing_ATTRIBUTE61 IN JTF_VARCHAR2_TABLE_300,
301 p_pricing_ATTRIBUTE62 IN JTF_VARCHAR2_TABLE_300,
302 p_pricing_ATTRIBUTE63 IN JTF_VARCHAR2_TABLE_300,
303 p_pricing_ATTRIBUTE64 IN JTF_VARCHAR2_TABLE_300,
304 p_pricing_ATTRIBUTE65 IN JTF_VARCHAR2_TABLE_300,
305 p_pricing_ATTRIBUTE66 IN JTF_VARCHAR2_TABLE_300,
306 p_pricing_ATTRIBUTE67 IN JTF_VARCHAR2_TABLE_300,
307 p_pricing_ATTRIBUTE68 IN JTF_VARCHAR2_TABLE_300,
308 p_pricing_ATTRIBUTE69 IN JTF_VARCHAR2_TABLE_300,
309 p_pricing_ATTRIBUTE70 IN JTF_VARCHAR2_TABLE_300,
310 p_pricing_ATTRIBUTE71 IN JTF_VARCHAR2_TABLE_300,
311 p_pricing_ATTRIBUTE72 IN JTF_VARCHAR2_TABLE_300,
312 p_pricing_ATTRIBUTE73 IN JTF_VARCHAR2_TABLE_300,
313 p_pricing_ATTRIBUTE74 IN JTF_VARCHAR2_TABLE_300,
314 p_pricing_ATTRIBUTE75 IN JTF_VARCHAR2_TABLE_300,
315 p_pricing_ATTRIBUTE76 IN JTF_VARCHAR2_TABLE_300,
316 p_pricing_ATTRIBUTE77 IN JTF_VARCHAR2_TABLE_300,
317 p_pricing_ATTRIBUTE78 IN JTF_VARCHAR2_TABLE_300,
318 p_pricing_ATTRIBUTE79 IN JTF_VARCHAR2_TABLE_300,
319 p_pricing_ATTRIBUTE80 IN JTF_VARCHAR2_TABLE_300,
320 p_pricing_ATTRIBUTE81 IN JTF_VARCHAR2_TABLE_300,
321 p_pricing_ATTRIBUTE82 IN JTF_VARCHAR2_TABLE_300,
322 p_pricing_ATTRIBUTE83 IN JTF_VARCHAR2_TABLE_300,
323 p_pricing_ATTRIBUTE84 IN JTF_VARCHAR2_TABLE_300,
324 p_pricing_ATTRIBUTE85 IN JTF_VARCHAR2_TABLE_300,
325 p_pricing_ATTRIBUTE86 IN JTF_VARCHAR2_TABLE_300,
326 p_pricing_ATTRIBUTE87 IN JTF_VARCHAR2_TABLE_300,
327 p_pricing_ATTRIBUTE88 IN JTF_VARCHAR2_TABLE_300,
328 p_pricing_ATTRIBUTE89 IN JTF_VARCHAR2_TABLE_300,
329 p_pricing_ATTRIBUTE90 IN JTF_VARCHAR2_TABLE_300,
330 p_pricing_ATTRIBUTE91 IN JTF_VARCHAR2_TABLE_300,
331 p_pricing_ATTRIBUTE92 IN JTF_VARCHAR2_TABLE_300,
332 p_pricing_ATTRIBUTE93 IN JTF_VARCHAR2_TABLE_300,
333 p_pricing_ATTRIBUTE94 IN JTF_VARCHAR2_TABLE_300,
334 p_pricing_ATTRIBUTE95 IN JTF_VARCHAR2_TABLE_300,
335 p_pricing_ATTRIBUTE96 IN JTF_VARCHAR2_TABLE_300,
336 p_pricing_ATTRIBUTE97 IN JTF_VARCHAR2_TABLE_300,
337 p_pricing_ATTRIBUTE98 IN JTF_VARCHAR2_TABLE_300,
338 p_pricing_ATTRIBUTE99 IN JTF_VARCHAR2_TABLE_300,
339 p_pricing_ATTRIBUTE100 IN JTF_VARCHAR2_TABLE_300,
340 p_pricing_CONTEXT IN JTF_VARCHAR2_TABLE_300,
341 x_prices OUT NOCOPY JTF_NUMBER_TABLE,
342 x_price_lists OUT NOCOPY JTF_NUMBER_TABLE,
343 x_return_status OUT NOCOPY VARCHAR2,
344 x_currency OUT NOCOPY VARCHAR2,
345 x_msg_count OUT NOCOPY NUMBER,
346 x_msg_data OUT NOCOPY VARCHAR2,
347 p_account_id IN NUMBER
348 )
349 IS
350 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_RETURNS_UTIL.get_estimated_price';
351 lc_api_version CONSTANT NUMBER := 1.0;
352 l_selling_price NUMBER;
353 l_contract_discount NUMBER;
354 l_pricing_attr_rec csd_process_util.pricing_attr_rec;
355 l_txn_billing_type_id NUMBER;
356 l_org_id NUMBER := fnd_profile.value('ORG_ID');
357 x_contract_pl_id NUMBER;
358 x_profile_pl_id NUMBER;
359
360 BEGIN
361 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
362 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
363 END IF;
364
365 -- standard check for API compatibility.
366 IF NOT Fnd_Api.Compatible_API_Call
367 (lc_api_version,
368 p_api_version,
369 lc_api_name,
370 G_PKG_NAME)
371 THEN
372 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
373 END IF;
374
375 IF Fnd_Api.to_Boolean(p_init_msg_list) THEN
376 Fnd_Msg_Pub.initialize;
377 END IF;
378
379 x_return_status := G_RET_STS_SUCCESS;
380 x_prices := JTF_NUMBER_TABLE();
381 x_price_lists := JTF_NUMBER_TABLE();
382 -- processing logic.
383 -- For each item entered, if the calculate charges is yes, then we will calculate the
384 -- charge by making call to advanced pricing, and then a call to calculate the contract discount.
385 -- the final charge will be price - discount.
386 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
387 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin Loop');
388 END IF;
389 FOR i IN 1 ..p_item_ids.COUNT
390 LOOP
391 x_price_lists.extend;
392 x_prices.extend;
393 -- step:1 Get the pricelist and currency.
394 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
395 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting the price list for iteration:'||i);
396 END IF;
397 csd_process_util.get_ro_default_curr_pl
398 (p_api_version => 1.0,
399 p_init_msg_list => p_init_msg_list,
400 p_incident_id => NULL,
401 p_repair_type_id => p_repair_type_ids(i),
402 p_ro_contract_line_id => p_contract_line_ids(i),
403 x_contract_pl_id => x_contract_pl_id,
404 x_profile_pl_id => x_profile_pl_id,
405 x_currency_code => x_currency,
406 x_return_status => x_return_status,
407 x_msg_count => x_msg_count,
408 x_msg_data => x_msg_data
409 );
410 IF x_contract_pl_id IS NOT NULL
411 THEN
412 x_price_lists(i) := x_contract_pl_id;
413 ELSE
414 x_price_lists(i) := x_profile_pl_id;
415 END IF;
416
417 -- step2: Calculate the selling price.
418 l_pricing_attr_rec.PRICING_ATTRIBUTE1 := p_PRICING_ATTRIBUTE1(i);
419 l_pricing_attr_rec.PRICING_ATTRIBUTE2 := p_PRICING_ATTRIBUTE2(i);
420 l_pricing_attr_rec.PRICING_ATTRIBUTE3 := p_PRICING_ATTRIBUTE3(i);
421 l_pricing_attr_rec.PRICING_ATTRIBUTE4 := p_PRICING_ATTRIBUTE4(i);
422 l_pricing_attr_rec.PRICING_ATTRIBUTE5 := p_PRICING_ATTRIBUTE5(i);
423 l_pricing_attr_rec.PRICING_ATTRIBUTE6 := p_PRICING_ATTRIBUTE6(i);
424 l_pricing_attr_rec.PRICING_ATTRIBUTE7 := p_PRICING_ATTRIBUTE7(i);
425 l_pricing_attr_rec.PRICING_ATTRIBUTE8 := p_PRICING_ATTRIBUTE8(i);
426 l_pricing_attr_rec.PRICING_ATTRIBUTE9 := p_PRICING_ATTRIBUTE9(i);
427 l_pricing_attr_rec.PRICING_ATTRIBUTE10 := p_PRICING_ATTRIBUTE10(i);
428 l_pricing_attr_rec.PRICING_ATTRIBUTE11 := p_PRICING_ATTRIBUTE11(i);
429 l_pricing_attr_rec.PRICING_ATTRIBUTE12 := p_PRICING_ATTRIBUTE12(i);
430 l_pricing_attr_rec.PRICING_ATTRIBUTE13 := p_PRICING_ATTRIBUTE13(i);
431 l_pricing_attr_rec.PRICING_ATTRIBUTE14 := p_PRICING_ATTRIBUTE14(i);
432 l_pricing_attr_rec.PRICING_ATTRIBUTE15 := p_PRICING_ATTRIBUTE15(i);
433 l_pricing_attr_rec.PRICING_ATTRIBUTE16 := p_PRICING_ATTRIBUTE16(i);
434 l_pricing_attr_rec.PRICING_ATTRIBUTE17 := p_PRICING_ATTRIBUTE17(i);
435 l_pricing_attr_rec.PRICING_ATTRIBUTE18 := p_PRICING_ATTRIBUTE18(i);
436 l_pricing_attr_rec.PRICING_ATTRIBUTE19 := p_PRICING_ATTRIBUTE19(i);
437 l_pricing_attr_rec.PRICING_ATTRIBUTE20 := p_PRICING_ATTRIBUTE20(i);
438 l_pricing_attr_rec.PRICING_ATTRIBUTE21 := p_PRICING_ATTRIBUTE21(i);
439 l_pricing_attr_rec.PRICING_ATTRIBUTE22 := p_PRICING_ATTRIBUTE22(i);
440 l_pricing_attr_rec.PRICING_ATTRIBUTE23 := p_PRICING_ATTRIBUTE23(i);
441 l_pricing_attr_rec.PRICING_ATTRIBUTE24 := p_PRICING_ATTRIBUTE24(i);
442 l_pricing_attr_rec.PRICING_ATTRIBUTE25 := p_PRICING_ATTRIBUTE25(i);
443 l_pricing_attr_rec.PRICING_ATTRIBUTE26 := p_PRICING_ATTRIBUTE26(i);
444 l_pricing_attr_rec.PRICING_ATTRIBUTE27 := p_PRICING_ATTRIBUTE27(i);
445 l_pricing_attr_rec.PRICING_ATTRIBUTE28 := p_PRICING_ATTRIBUTE28(i);
446 l_pricing_attr_rec.PRICING_ATTRIBUTE29 := p_PRICING_ATTRIBUTE29(i);
447 l_pricing_attr_rec.PRICING_ATTRIBUTE30 := p_PRICING_ATTRIBUTE30(i);
448 l_pricing_attr_rec.PRICING_ATTRIBUTE31 := p_PRICING_ATTRIBUTE31(i);
449 l_pricing_attr_rec.PRICING_ATTRIBUTE32 := p_PRICING_ATTRIBUTE32(i);
450 l_pricing_attr_rec.PRICING_ATTRIBUTE33 := p_PRICING_ATTRIBUTE33(i);
451 l_pricing_attr_rec.PRICING_ATTRIBUTE34 := p_PRICING_ATTRIBUTE34(i);
452 l_pricing_attr_rec.PRICING_ATTRIBUTE35 := p_PRICING_ATTRIBUTE35(i);
453 l_pricing_attr_rec.PRICING_ATTRIBUTE36 := p_PRICING_ATTRIBUTE36(i);
454 l_pricing_attr_rec.PRICING_ATTRIBUTE37 := p_PRICING_ATTRIBUTE37(i);
455 l_pricing_attr_rec.PRICING_ATTRIBUTE38 := p_PRICING_ATTRIBUTE38(i);
456 l_pricing_attr_rec.PRICING_ATTRIBUTE39 := p_PRICING_ATTRIBUTE39(i);
457 l_pricing_attr_rec.PRICING_ATTRIBUTE40 := p_PRICING_ATTRIBUTE40(i);
458 l_pricing_attr_rec.PRICING_ATTRIBUTE41 := p_PRICING_ATTRIBUTE41(i);
459 l_pricing_attr_rec.PRICING_ATTRIBUTE42 := p_PRICING_ATTRIBUTE42(i);
460 l_pricing_attr_rec.PRICING_ATTRIBUTE43 := p_PRICING_ATTRIBUTE43(i);
461 l_pricing_attr_rec.PRICING_ATTRIBUTE44 := p_PRICING_ATTRIBUTE44(i);
462 l_pricing_attr_rec.PRICING_ATTRIBUTE45 := p_PRICING_ATTRIBUTE45(i);
463 l_pricing_attr_rec.PRICING_ATTRIBUTE46 := p_PRICING_ATTRIBUTE46(i);
464 l_pricing_attr_rec.PRICING_ATTRIBUTE47 := p_PRICING_ATTRIBUTE47(i);
465 l_pricing_attr_rec.PRICING_ATTRIBUTE48 := p_PRICING_ATTRIBUTE48(i);
466 l_pricing_attr_rec.PRICING_ATTRIBUTE49 := p_PRICING_ATTRIBUTE49(i);
467 l_pricing_attr_rec.PRICING_ATTRIBUTE50 := p_PRICING_ATTRIBUTE50(i);
468 l_pricing_attr_rec.PRICING_ATTRIBUTE51 := p_PRICING_ATTRIBUTE51(i);
469 l_pricing_attr_rec.PRICING_ATTRIBUTE52 := p_PRICING_ATTRIBUTE52(i);
470 l_pricing_attr_rec.PRICING_ATTRIBUTE53 := p_PRICING_ATTRIBUTE53(i);
471 l_pricing_attr_rec.PRICING_ATTRIBUTE54 := p_PRICING_ATTRIBUTE54(i);
472 l_pricing_attr_rec.PRICING_ATTRIBUTE55 := p_PRICING_ATTRIBUTE55(i);
473 l_pricing_attr_rec.PRICING_ATTRIBUTE56 := p_PRICING_ATTRIBUTE56(i);
474 l_pricing_attr_rec.PRICING_ATTRIBUTE57 := p_PRICING_ATTRIBUTE57(i);
475 l_pricing_attr_rec.PRICING_ATTRIBUTE58 := p_PRICING_ATTRIBUTE58(i);
476 l_pricing_attr_rec.PRICING_ATTRIBUTE59 := p_PRICING_ATTRIBUTE59(i);
477 l_pricing_attr_rec.PRICING_ATTRIBUTE60 := p_PRICING_ATTRIBUTE60(i);
478 l_pricing_attr_rec.PRICING_ATTRIBUTE61 := p_PRICING_ATTRIBUTE61(i);
479 l_pricing_attr_rec.PRICING_ATTRIBUTE62 := p_PRICING_ATTRIBUTE62(i);
480 l_pricing_attr_rec.PRICING_ATTRIBUTE63 := p_PRICING_ATTRIBUTE63(i);
481 l_pricing_attr_rec.PRICING_ATTRIBUTE64 := p_PRICING_ATTRIBUTE64(i);
482 l_pricing_attr_rec.PRICING_ATTRIBUTE65 := p_PRICING_ATTRIBUTE65(i);
483 l_pricing_attr_rec.PRICING_ATTRIBUTE66 := p_PRICING_ATTRIBUTE66(i);
484 l_pricing_attr_rec.PRICING_ATTRIBUTE67 := p_PRICING_ATTRIBUTE67(i);
485 l_pricing_attr_rec.PRICING_ATTRIBUTE68 := p_PRICING_ATTRIBUTE68(i);
486 l_pricing_attr_rec.PRICING_ATTRIBUTE69 := p_PRICING_ATTRIBUTE69(i);
487 l_pricing_attr_rec.PRICING_ATTRIBUTE70 := p_PRICING_ATTRIBUTE70(i);
488 l_pricing_attr_rec.PRICING_ATTRIBUTE71 := p_PRICING_ATTRIBUTE71(i);
489 l_pricing_attr_rec.PRICING_ATTRIBUTE72 := p_PRICING_ATTRIBUTE72(i);
490 l_pricing_attr_rec.PRICING_ATTRIBUTE73 := p_PRICING_ATTRIBUTE73(i);
491 l_pricing_attr_rec.PRICING_ATTRIBUTE74 := p_PRICING_ATTRIBUTE74(i);
492 l_pricing_attr_rec.PRICING_ATTRIBUTE75 := p_PRICING_ATTRIBUTE75(i);
493 l_pricing_attr_rec.PRICING_ATTRIBUTE76 := p_PRICING_ATTRIBUTE76(i);
494 l_pricing_attr_rec.PRICING_ATTRIBUTE77 := p_PRICING_ATTRIBUTE77(i);
495 l_pricing_attr_rec.PRICING_ATTRIBUTE78 := p_PRICING_ATTRIBUTE78(i);
496 l_pricing_attr_rec.PRICING_ATTRIBUTE79 := p_PRICING_ATTRIBUTE79(i);
497 l_pricing_attr_rec.PRICING_ATTRIBUTE80 := p_PRICING_ATTRIBUTE80(i);
498 l_pricing_attr_rec.PRICING_ATTRIBUTE81 := p_PRICING_ATTRIBUTE81(i);
499 l_pricing_attr_rec.PRICING_ATTRIBUTE82 := p_PRICING_ATTRIBUTE82(i);
500 l_pricing_attr_rec.PRICING_ATTRIBUTE83 := p_PRICING_ATTRIBUTE83(i);
501 l_pricing_attr_rec.PRICING_ATTRIBUTE84 := p_PRICING_ATTRIBUTE84(i);
502 l_pricing_attr_rec.PRICING_ATTRIBUTE85 := p_PRICING_ATTRIBUTE85(i);
503 l_pricing_attr_rec.PRICING_ATTRIBUTE86 := p_PRICING_ATTRIBUTE86(i);
504 l_pricing_attr_rec.PRICING_ATTRIBUTE87 := p_PRICING_ATTRIBUTE87(i);
505 l_pricing_attr_rec.PRICING_ATTRIBUTE88 := p_PRICING_ATTRIBUTE88(i);
506 l_pricing_attr_rec.PRICING_ATTRIBUTE89 := p_PRICING_ATTRIBUTE89(i);
507 l_pricing_attr_rec.PRICING_ATTRIBUTE90 := p_PRICING_ATTRIBUTE90(i);
508 l_pricing_attr_rec.PRICING_ATTRIBUTE91 := p_PRICING_ATTRIBUTE91(i);
509 l_pricing_attr_rec.PRICING_ATTRIBUTE92 := p_PRICING_ATTRIBUTE92(i);
510 l_pricing_attr_rec.PRICING_ATTRIBUTE93 := p_PRICING_ATTRIBUTE93(i);
511 l_pricing_attr_rec.PRICING_ATTRIBUTE94 := p_PRICING_ATTRIBUTE94(i);
512 l_pricing_attr_rec.PRICING_ATTRIBUTE95 := p_PRICING_ATTRIBUTE95(i);
513 l_pricing_attr_rec.PRICING_ATTRIBUTE96 := p_PRICING_ATTRIBUTE96(i);
514 l_pricing_attr_rec.PRICING_ATTRIBUTE97 := p_PRICING_ATTRIBUTE97(i);
515 l_pricing_attr_rec.PRICING_ATTRIBUTE98 := p_PRICING_ATTRIBUTE98(i);
516 l_pricing_attr_rec.PRICING_ATTRIBUTE99 := p_PRICING_ATTRIBUTE99(i);
517 l_pricing_attr_rec.PRICING_ATTRIBUTE100 := p_PRICING_ATTRIBUTE100(i);
518 l_pricing_attr_rec.PRICING_CONTEXT := p_PRICING_CONTEXT(i);
519
520 -- call the API.
521 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
522 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling csd_process_util.get_charge_selling_price');
523 END IF;
524 csd_process_util.get_charge_selling_price
525 (p_inventory_item_id => p_item_ids(i),
526 p_price_list_header_id => x_price_lists(i),
527 p_unit_of_measure_code => p_uom_codes(i),
528 p_currency_code => x_currency,
529 p_quantity_required => p_quantities(i),
530 p_account_id => p_account_id,
531 p_org_id => l_org_id,
532 p_pricing_rec => l_pricing_attr_rec,
533 x_selling_price => l_selling_price,
534 x_return_status => x_return_status,
535 x_msg_count => x_msg_count,
536 x_msg_data => x_msg_data
537 );
538 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
539 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Returned from csd_process_util.get_charge_selling_price. Return status:'||x_return_status||' message:'||x_msg_data);
540 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Price : '||l_selling_price);
541 END IF;
542 -- if the selling price is 0 or null then no need to call the contracts API.
543 IF NVL(l_selling_price,0) = 0
544 THEN
545 x_prices(i) := 0;
546 -- selling price is > 0. Call contracts discount API to get the discounts.
547 ELSE
548 IF p_contract_ids(i) IS NOT NULL AND p_contract_line_ids(i) IS NOT NULL
549 THEN
550 -- get the txn billing type id
551 SELECT txn_billing_type_id
552 INTO l_txn_billing_type_id
553 FROM csd_repair_types_sar_vl
554 WHERE repair_type_id = p_repair_type_ids(i)
555 AND billing_type = 'M';
556 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
557 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling csd_charge_line_util.get_discountedprice to get warranty discounts:');
558 END IF;
559 --
560 csd_charge_line_util.get_discountedprice
561 (p_api_version => 1.0,
562 p_init_msg_list => p_init_msg_list,
563 p_contract_line_id => p_contract_line_ids(i),
564 p_repair_type_id => p_repair_type_ids(i),
565 p_txn_billing_type_id => l_txn_billing_type_id,
566 p_coverage_txn_grp_id => NULL,
567 p_extended_price => l_selling_price*p_quantities(i),
568 p_no_charge_flag => 'N',
569 x_discounted_price => l_contract_discount,
570 x_return_status => x_return_status,
571 x_msg_count => x_msg_count,
572 x_msg_data => x_msg_data
573 );
574 x_prices(i) := NVL(l_contract_discount,0);
575 ELSE
576 x_prices(i) := l_selling_price*p_quantities(i);
577 END IF;
578 END IF;
579 G_SELLING_PRICE_CACHE(p_item_ids(i)) := NVL(l_selling_price,0);
580 END LOOP;
581
582 END get_estimated_price;
583
584 PROCEDURE process_credit_card
585 (p_api_version IN NUMBER DEFAULT 1.0,
586 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE,
587 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
588 p_cc_number IN VARCHAR2,
589 p_cch_name IN VARCHAR2,
590 p_expiry_date IN DATE,
591 p_card_issuer_code IN VARCHAR2,
592 p_party_id IN NUMBER,
593 p_account_id IN NUMBER,
594 p_bill_to_site_use_id IN NUMBER,
595 p_ship_to_site_use_id IN NUMBER,
596 p_amount IN NUMBER,
597 p_currency_code IN VARCHAR2,
598 p_authorize_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
599 p_create_txn_extn_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
600 p_add_instr_asgn_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE,
601 p_order_id IN VARCHAR2,
602 x_instr_id OUT NOCOPY NUMBER,
603 x_txn_id OUT NOCOPY NUMBER,
604 x_authorized_flag OUT NOCOPY VARCHAR2,
605 x_return_status OUT NOCOPY VARCHAR2,
606 x_msg OUT NOCOPY VARCHAR2
607 )
608 IS
609 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_RETURNS_UTIL.process_credit_card';
610 lc_api_version CONSTANT NUMBER := 1.0;
611 l_payer_context_rec iby_fndcpt_common_pub.payercontext_rec_type;
612 l_payerattributes_rec iby_fndcpt_setup_pub.payerattributes_rec_type;
613 l_creditcard_rec iby_fndcpt_setup_pub.creditcard_rec_type;
614 l_instr_assign_rec iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
615 l_instrument_rec iby_fndcpt_setup_pub.pmtinstrument_rec_type;
616 l_trxnextension_rec iby_fndcpt_trxn_pub.trxnextension_rec_type;
617 l_payee_context_rec iby_fndcpt_trxn_pub.payeecontext_rec_type;
618 l_authattribs_rec iby_fndcpt_trxn_pub.authattribs_rec_type;
619 l_amount_rec iby_fndcpt_trxn_pub.amount_rec_type;
620 l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
621 l_cust_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
622 l_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
623 l_amount NUMBER := p_amount;
624 x_cust_acct_site_id NUMBER;
625 x_authresult_rec iby_fndcpt_trxn_pub.authresult_rec_type;
626 x_result_rec_type iby_fndcpt_common_pub.result_rec_type;
627 x_payer_attrib_id NUMBER;
628 x_assign_id NUMBER;
629 x_msg_count NUMBER;
630 x_transaction_id NUMBER;
631 x_acct_site_use_id NUMBER;
632
633
634 BEGIN
635 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
636 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
637 END IF;
638
639 -- standard check for API compatibility.
640 IF NOT Fnd_Api.Compatible_API_Call
641 (lc_api_version,
642 p_api_version,
643 lc_api_name,
644 G_PKG_NAME)
645 THEN
646 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
647 END IF;
648
649 IF Fnd_Api.to_Boolean(p_init_msg_list) THEN
650 Fnd_Msg_Pub.initialize;
651 END IF;
652
653 x_return_status := G_RET_STS_SUCCESS;
654 -- check if the payee already exists. If not register the payee.
655 l_payer_context_rec.payment_function := 'CUSTOMER_PAYMENT';
656 l_payer_context_rec.party_id := p_party_id;
657 l_payer_context_rec.org_type := 'OPERATING_UNIT';
658 l_payer_context_rec.org_id := fnd_profile.value('ORG_ID');
659 l_payer_context_rec.cust_account_id := p_account_id;
660
661 -- establish the save point.
662 SAVEPOINT process_credit_card;
663 -- get the default account_site_id
664 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
665 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting default account site use id for account:'||p_account_id);
666 END IF;
667 BEGIN
668 SELECT site_use_id
669 INTO l_payer_context_rec.account_site_id
670 FROM hz_cust_site_uses_all u,
671 hz_cust_acct_sites_all s
672 WHERE s.cust_account_id = p_account_id
673 AND u.cust_acct_site_id = s.cust_acct_site_id
674 AND NVL(u.primary_flag,'N') = 'Y'
675 AND ROWNUM < 2;
676 EXCEPTION
677 WHEN NO_DATA_FOUND THEN
678 -- we need to create a new account site and a account site use of BILL_TO type. The IBY API's need the account site
679 -- to create the transaction extension.
680
681 l_cust_acct_site_rec.cust_account_id := p_account_id;
682
683 SELECT party_site_id
684 INTO l_cust_acct_site_rec.party_site_id
685 FROM hz_party_site_uses
686 WHERE party_site_use_id = p_bill_to_site_use_id;
687
688 l_cust_acct_site_rec.status := 'A';
689 l_cust_acct_site_rec.created_by_module := 'CSDSR';
690 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
691 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site to create a default account site');
692 END IF;
693 hz_cust_account_site_v2pub.create_cust_acct_site
694 (p_init_msg_list => p_init_msg_list,
695 p_cust_acct_site_rec => l_cust_acct_site_rec,
696 x_cust_acct_site_id => x_cust_acct_site_id,
697 x_return_status => x_return_status,
698 x_msg_count => x_msg_count,
699 x_msg_data => x_msg
700 );
701
702 IF x_return_status <> fnd_api.g_ret_sts_success
703 THEN
704 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
705 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in creating default customer account site.');
706 END IF;
707 RAISE fnd_api.g_exc_error;
708 END IF;
709 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
710 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Returned from hz_cust_account_site_v2pub.create_cust_acct_site.');
711 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Status:'||x_return_status);
712 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Message:'||x_msg);
713 END IF;
714
715 -- create the bill to site use for the account.
716 l_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
717 l_cust_site_use_rec.status := 'A';
718 l_cust_site_use_rec.created_by_module := 'CSDSR';
719 l_cust_site_use_rec.application_id := 512;
720 l_cust_site_use_rec.primary_flag := 'Y';
721 l_cust_site_use_rec.site_use_code := 'BILL_TO';
722
723 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
724 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use to create a default account site use (BILL_TO).');
725 END IF;
726
727 hz_cust_account_site_v2pub.create_cust_site_use
728 (p_init_msg_list => p_init_msg_list,
729 p_cust_site_use_rec => l_cust_site_use_rec,
730 p_customer_profile_rec => l_cust_profile_rec,
731 p_create_profile => fnd_api.g_false,
732 x_return_status => x_return_status,
733 x_msg_count => x_msg_count,
734 x_msg_data => x_msg,
735 x_site_use_id => x_acct_site_use_id
736 );
737
738 IF x_return_status <> fnd_api.g_ret_sts_success
739 THEN
740 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
741 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in creating default customer account site use.');
742 END IF;
743 RAISE fnd_api.g_exc_error;
744 END IF;
745 l_payer_context_rec.account_site_id := x_acct_site_use_id;
746 END;
747
748 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
749 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling iby_fndcpt_setup_pub.set_payer_attributes');
750 END IF;
751 iby_fndcpt_setup_pub.set_payer_attributes
752 (p_api_version => 1.0,
753 p_init_msg_list => p_init_msg_list,
754 p_commit => p_commit,
755 x_return_status => x_return_Status,
756 x_msg_count => x_msg_count,
757 x_msg_data => x_msg,
758 p_payer => l_payer_context_rec,
759 p_payer_attributes => l_payerattributes_rec,
760 x_payer_attribs_id => x_payer_attrib_id,
761 x_response => x_result_rec_type
762 );
763
764 IF x_return_status <> fnd_api.g_ret_sts_success
765 THEN
766 x_msg := x_result_rec_type.result_message;
767 x_return_status := fnd_api.g_ret_sts_error;
768 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
769 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Card registration failed. The message is:'||x_msg);
770 END IF;
771 RAISE fnd_api.g_exc_error;
772 END IF;
773
774 -- register the credit card.
775 l_creditcard_rec.card_holder_name := p_cch_name;
776 --l_creditcard_rec.billing_address_id := p_bill_to_site_use_id;
777 l_creditcard_rec.card_number := p_cc_number;
778 l_creditcard_rec.expiration_date := p_expiry_date;
779 l_creditcard_rec.instrument_type := 'CREDITCARD';
780 l_creditcard_rec.owner_id := p_party_id;
781
782 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
783 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Registering the card.');
784 END IF;
785 -- here we call a autonomous transaction to save the CC details. This is required because before calling
786 -- authorization API, the IBY code requires us to commit the card details.
787 Create_Card
788 (p_api_version => 1.0,
789 p_init_msg_list => p_init_msg_list,
790 p_commit => fnd_api.g_true,
791 x_return_status => x_return_Status,
792 x_msg_count => x_msg_count,
793 x_msg_data => x_msg,
794 p_card_instrument => l_creditcard_rec,
795 x_card_id => x_instr_id,
796 x_response => x_result_rec_type
797 );
798
799 IF x_return_status <> fnd_api.g_ret_sts_success
800 THEN
801 x_msg := x_result_rec_type.result_message;
802 x_return_status := fnd_api.g_ret_sts_error;
803 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
804 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Card registration failed. Message:'||x_result_rec_type.result_message);
805 END IF;
806 RAISE fnd_api.g_exc_error;
807 END IF;
808
809 -- create an assignment between the instr and payer.
810 l_Instrument_rec.instrument_id := x_instr_id;
811 l_Instrument_rec.instrument_type := 'CREDITCARD';
812
813 l_instr_assign_rec.instrument := l_Instrument_rec;
814 l_instr_assign_rec.start_date := SYSDATE;
815
816 -- call API to create the assignment.
817 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
818 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling instrument assignment API.');
819 END IF;
820
821 iby_fndcpt_setup_pub.set_payer_instr_assignment
822 (p_api_version => 1.0,
823 p_init_msg_list => p_init_msg_list,
824 p_commit => p_commit,
825 x_return_status => x_return_Status,
826 x_msg_count => x_msg_count,
827 x_msg_data => x_msg,
828 p_payer => l_payer_context_rec,
829 p_assignment_attribs => l_instr_assign_rec,
830 x_assign_id => x_assign_id,
831 x_response => x_result_rec_type
832 );
833
834 IF x_return_status <> fnd_api.g_ret_sts_success
835 THEN
836 x_msg := x_result_rec_type.result_message;
837 x_return_status := fnd_api.g_ret_sts_error;
838 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
839 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in adding instrument assignment. Message is:'||x_result_rec_type.result_message);
840 END IF;
841 RAISE fnd_api.g_exc_error;
842 END IF;
843
844 IF p_authorize_flag = fnd_api.g_true
845 THEN
846 -- create a transaction extension
847 l_TrxnExtension_rec.order_id := p_order_id;
848 l_TrxnExtension_rec.originating_application_id := 512;
849 l_TrxnExtension_rec.trxn_ref_number1 := 'CSD_REPAIRORDER';
850 l_TrxnExtension_rec.trxn_ref_number1 := 'ONLINE_RETURNS';
851 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
852 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Creating an transaction extension.');
853 END IF;
854 iby_fndcpt_trxn_pub.Create_Transaction_Extension
855 (p_api_version => 1.0,
856 p_init_msg_list => p_init_msg_list,
857 p_commit => p_commit,
858 x_return_status => x_return_Status,
859 x_msg_count => x_msg_count,
860 x_msg_data => x_msg,
861 p_payer => l_payer_context_rec,
862 p_pmt_channel => 'CREDIT_CARD',
863 p_instr_assignment => x_assign_id,
864 p_trxn_attribs => l_TrxnExtension_rec,
865 x_entity_id => x_transaction_id,
866 x_response => x_result_rec_type
867 );
868
869 IF x_return_status <> fnd_api.g_ret_sts_success
870 THEN
871 x_msg := x_result_rec_type.result_message;
872 x_return_status := fnd_api.g_ret_sts_error;
873 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
874 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error while creating the transaction extension. Message is:'||x_result_rec_type.result_message);
875 END IF;
876 RAISE fnd_api.g_exc_error;
877 END IF;
878 -- we need to authorize the payment.
879 -- step 1. prepare the amount rec.
880 -- if the amount is zero, then we need to pass a minimum amount for the autorization API.
881 -- for now, we pass 0.01 as amount. This is minimum possible for most of the currencies. But need to find out a better way of arriving at a minimum possible amount
882 -- based on the currency.
883 IF NVL(l_amount,0) = 0
884 THEN
885 l_amount := 0.01;
886 END IF;
887 l_amount_rec.value := l_amount;
888 l_amount_rec.currency_code := p_currency_code;
889
890 -- step 2. prepare the payee type rec.
891 l_payee_context_rec.org_type := 'OPERATING_UNIT';
892 l_payee_context_rec.org_id := fnd_profile.value('ORG_ID');
893
894 -- step 3. Prepare the authorization attributes rec type.
895 -- at this point I dont think we need to pass any specific values to this rec.
896 -- the default values are good as per IBY documentation.
897
898 -- step 4. Call the authorization API.
899 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
900 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling create_authorization API to authorize the payment.');
901 END IF;
902 iby_fndcpt_trxn_pub.create_authorization
903 (p_api_version => 1.0,
904 p_init_msg_list => p_init_msg_list,
905 x_return_status => x_return_status,
906 x_msg_count => x_msg_count,
907 x_msg_data => x_msg,
908 p_payer => l_payer_context_rec,
909 p_payer_equivalency => iby_fndcpt_common_pub.g_payer_equiv_upward,
910 p_payee => l_payee_context_rec,
911 p_trxn_entity_id => x_transaction_id,
912 p_auth_attribs => l_authattribs_rec,
913 p_amount => l_amount_rec,
914 x_auth_result => x_authresult_rec,
915 x_response => x_result_rec_type
916 );
917
918 IF x_return_status <> fnd_api.g_ret_sts_success
919 THEN
920 x_msg := x_authresult_rec.paymentsys_msg;
921 x_return_status := fnd_api.g_ret_sts_error;
922 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
923 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in creating the authorization. Message is:'||x_msg);
924 END IF;
925 RAISE fnd_api.g_exc_error;
926 END IF;
927 x_authorized_flag := fnd_api.g_true;
928 INSERT INTO csd_cc_trxn_xref(instrument_id,transaction_id,creation_date,created_by,last_updated_by,last_update_date,last_update_login)
929 VALUES
930 (x_instr_id,
931 x_transaction_id,
932 sysdate,
933 fnd_global.user_id,
934 fnd_global.user_id,
935 sysdate,
936 fnd_global.login_id
937 );
938 END IF;
939 x_txn_id := x_transaction_id;
940 IF (fnd_api.to_boolean(p_commit))
941 THEN
942 COMMIT WORK;
943 END IF;
944
945 EXCEPTION
946 WHEN fnd_api.g_exc_error THEN
947 ROLLBACK TO process_credit_card;
948 WHEN OTHERS THEN
949 x_return_status := g_ret_sts_error;
950 ROLLBACK TO process_credit_card;
951 END process_credit_card;
952
953 PROCEDURE create_address
954 (p_api_version IN NUMBER DEFAULT 1.0,
955 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE,
956 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
957 p_party_id IN VARCHAR2,
958 p_site_use_type IN VARCHAR2,
959 p_country IN VARCHAR2,
960 p_address1 IN VARCHAR2,
961 p_address2 IN VARCHAR2,
962 p_address3 IN VARCHAR2,
963 p_address4 IN VARCHAR2,
964 p_city IN VARCHAR2,
965 p_postal_code IN VARCHAR2,
966 p_state IN VARCHAR2,
967 p_province IN VARCHAR2,
968 p_county IN VARCHAR2,
969 p_address_style IN VARCHAR2,
970 p_address_lines_phonetic IN VARCHAR2,
971 p_location_id IN NUMBER DEFAULT NULL,
972 x_location_id OUT NOCOPY NUMBER,
973 x_party_site_id OUT NOCOPY NUMBER,
974 x_party_site_use_id OUT NOCOPY NUMBER,
975 x_return_status OUT NOCOPY VARCHAR2,
976 x_msg_count OUT NOCOPY NUMBER,
977 x_msg_data OUT NOCOPY VARCHAR2
978 )
979 IS
980 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_RETURNS_UTIL.create_address';
981 lc_api_version CONSTANT NUMBER := 1.0;
982 l_location_rec hz_location_v2pub.location_rec_type;
983 l_party_site_use_rec hz_party_site_v2pub.party_site_use_rec_type;
984 l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
985 l_location_id NUMBER := p_location_id;
986 x_party_site_number VARCHAR2(30);
987
988 BEGIN
989 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
990 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
991 END IF;
992
993 -- standard check for API compatibility.
994 IF NOT Fnd_Api.Compatible_API_Call
995 (lc_api_version,
996 p_api_version,
997 lc_api_name,
998 G_PKG_NAME)
999 THEN
1000 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1001 END IF;
1002
1003 IF Fnd_Api.to_Boolean(p_init_msg_list) THEN
1004 Fnd_Msg_Pub.initialize;
1005 END IF;
1006
1007 x_return_status := G_RET_STS_SUCCESS;
1008
1009 SAVEPOINT create_address;
1010
1011 IF l_location_id IS NULL
1012 THEN
1013 -- we need to create the location first.
1014 -- populate the location record.
1015 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1016 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Populating the location record.');
1017 END IF;
1018 l_location_rec.country := p_country;
1019 l_location_rec.address1 := p_address1;
1020 l_location_rec.address2 := p_address2;
1021 l_location_rec.address3 := p_address3;
1022 l_location_rec.address4 := p_address4;
1023 l_location_rec.city := p_city;
1024 l_location_rec.postal_code := p_postal_code;
1025 l_location_rec.state := p_state;
1026 l_location_rec.province := p_province;
1027 l_location_rec.county := p_county;
1028 l_location_rec.address_style := p_address_style;
1029 l_location_rec.created_by_module := 'CSDSR';
1030
1031 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1032 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Before calling HZ_LOCATION_V2PUB.create_location');
1033 END IF;
1034
1035 HZ_LOCATION_V2PUB.create_location
1036 (p_init_msg_list => p_init_msg_list,
1037 p_location_rec => l_location_rec,
1038 --p_do_addr_val => 'Y',
1039 x_location_id => x_location_id,
1040 x_return_status => x_return_status,
1041 x_msg_count => x_msg_count,
1042 x_msg_data => x_msg_data
1043 );
1044 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1045 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Returned from HZ_LOCATION_V2PUB.create_location');
1046 END IF;
1047
1048 IF x_return_status <> fnd_api.g_ret_sts_success
1049 THEN
1050 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1051 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error while creating the location. The error message is:'||x_msg_data);
1052 END IF;
1053 RAISE fnd_api.g_exc_error;
1054 END IF;
1055 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1056 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Successfully created the location. The location id is ='||x_location_id);
1057 END IF;
1058 l_location_id := x_location_id;
1059 END IF;
1060
1061 -- create a party site for the location created.
1062 -- create it only if we are asked to.
1063 IF p_site_use_type IS NOT NULL
1064 THEN
1065 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1066 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Populating l_party_site_rec for creation of party site');
1067 END IF;
1068 l_party_site_rec.party_id := p_party_id;
1069 l_party_site_rec.location_id := l_location_id;
1070 l_party_site_rec.identifying_address_flag := 'Y';
1071 l_party_site_rec.status := 'A';
1072 l_party_site_rec.created_by_module := 'CSDSR';
1073 -- call API to create the party site.
1074 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1075 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Before calling HZ_PARTY_SITE_V2PUB.create_party_site');
1076 END IF;
1077 HZ_PARTY_SITE_V2PUB.create_party_site
1078 (p_init_msg_list => p_init_msg_list,
1079 p_party_site_rec => l_party_site_rec,
1080 x_party_site_id => x_party_site_id,
1081 x_party_site_number => x_party_site_number,
1082 x_return_status => x_return_status,
1083 x_msg_count => x_msg_count,
1084 x_msg_data => x_msg_data
1085 );
1086 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1087 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Returned from HZ_PARTY_SITE_V2PUB.create_party_site');
1088 END IF;
1089
1090 IF x_return_status <> fnd_api.g_ret_sts_success
1091 THEN
1092 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1093 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error while creating the party site. The error message is:'||x_msg_data);
1094 END IF;
1095 RAISE fnd_api.g_exc_error;
1096 END IF;
1097 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1098 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Successfully created the party site. The site id is ='||x_party_site_id);
1099 END IF;
1100
1101 -- create party site use.
1102 -- populate the party site use rec.
1103 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1104 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Populating party site use rec.');
1105 END IF;
1106 l_party_site_use_rec.party_site_id := x_party_site_id;
1107 l_party_site_use_rec.site_use_type := p_site_use_type;
1108 l_party_site_use_rec.primary_per_type := 'Y';
1109 l_party_site_use_rec.status := 'A';
1110 l_party_site_use_rec.application_id := 512;
1111 l_party_site_use_rec.created_by_module := 'CSDSR';
1112
1113 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1114 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling HZ_PARTY_SITE_V2PUB.create_party_site_use');
1115 END IF;
1116 HZ_PARTY_SITE_V2PUB.create_party_site_use
1117 (p_init_msg_list => p_init_msg_list,
1118 p_party_site_use_rec => l_party_site_use_rec,
1119 x_party_site_use_id => x_party_site_use_id,
1120 x_return_status => x_return_status,
1121 x_msg_count => x_msg_count,
1122 x_msg_data => x_msg_data
1123 );
1124
1125 IF x_return_status <> fnd_api.g_ret_sts_success
1126 THEN
1127 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1128 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error while creating the party site use. The error message is:'||x_msg_data);
1129 END IF;
1130 RAISE fnd_api.g_exc_error;
1131 END IF;
1132 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1133 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Successfully created the party site. The site use id is ='||x_party_site_use_id);
1134 END IF;
1135 END IF;
1136
1137 IF fnd_api.to_boolean(p_commit)
1138 THEN
1139 COMMIT WORK;
1140 END IF;
1141 EXCEPTION
1142 WHEN fnd_api.g_exc_error THEN
1143 ROLLBACK TO create_address;
1144 x_return_status := fnd_api.g_ret_sts_error;
1145 END create_address;
1146
1147 PROCEDURE process_returns
1148 (p_api_version IN NUMBER DEFAULT 1.0,
1149 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE,
1150 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1151 p_party_id IN NUMBER,
1152 p_account_id IN NUMBER,
1153 p_item_ids IN JTF_NUMBER_TABLE,
1154 p_contract_ids IN JTF_NUMBER_TABLE,
1155 p_contract_line_ids IN JTF_NUMBER_TABLE,
1156 p_repair_type_ids IN JTF_NUMBER_TABLE,
1157 p_uom_codes IN JTF_VARCHAR2_TABLE_100,
1158 p_quantities IN JTF_NUMBER_TABLE,
1159 p_price_lists IN JTF_NUMBER_TABLE,
1160 p_external_refs IN JTF_VARCHAR2_TABLE_100,
1161 p_serial_nums IN JTF_VARCHAR2_TABLE_100,
1162 p_bill_to_addr_use_id IN NUMBER,
1163 p_ship_to_addr_use_id IN NUMBER,
1164 p_instance_ids IN JTF_NUMBER_TABLE,
1165 p_pricing_ATTRIBUTE1 IN JTF_VARCHAR2_TABLE_300, -- pricing attributes start
1166 p_pricing_ATTRIBUTE2 IN JTF_VARCHAR2_TABLE_300,
1167 p_pricing_ATTRIBUTE3 IN JTF_VARCHAR2_TABLE_300,
1168 p_pricing_ATTRIBUTE4 IN JTF_VARCHAR2_TABLE_300,
1169 p_pricing_ATTRIBUTE5 IN JTF_VARCHAR2_TABLE_300,
1170 p_pricing_ATTRIBUTE6 IN JTF_VARCHAR2_TABLE_300,
1171 p_pricing_ATTRIBUTE7 IN JTF_VARCHAR2_TABLE_300,
1172 p_pricing_ATTRIBUTE8 IN JTF_VARCHAR2_TABLE_300,
1173 p_pricing_ATTRIBUTE9 IN JTF_VARCHAR2_TABLE_300,
1174 p_pricing_ATTRIBUTE10 IN JTF_VARCHAR2_TABLE_300,
1175 p_pricing_ATTRIBUTE11 IN JTF_VARCHAR2_TABLE_300,
1176 p_pricing_ATTRIBUTE12 IN JTF_VARCHAR2_TABLE_300,
1177 p_pricing_ATTRIBUTE13 IN JTF_VARCHAR2_TABLE_300,
1178 p_pricing_ATTRIBUTE14 IN JTF_VARCHAR2_TABLE_300,
1179 p_pricing_ATTRIBUTE15 IN JTF_VARCHAR2_TABLE_300,
1180 p_pricing_ATTRIBUTE16 IN JTF_VARCHAR2_TABLE_300,
1181 p_pricing_ATTRIBUTE17 IN JTF_VARCHAR2_TABLE_300,
1182 p_pricing_ATTRIBUTE18 IN JTF_VARCHAR2_TABLE_300,
1183 p_pricing_ATTRIBUTE19 IN JTF_VARCHAR2_TABLE_300,
1184 p_pricing_ATTRIBUTE20 IN JTF_VARCHAR2_TABLE_300,
1185 p_pricing_ATTRIBUTE21 IN JTF_VARCHAR2_TABLE_300,
1186 p_pricing_ATTRIBUTE22 IN JTF_VARCHAR2_TABLE_300,
1187 p_pricing_ATTRIBUTE23 IN JTF_VARCHAR2_TABLE_300,
1188 p_pricing_ATTRIBUTE24 IN JTF_VARCHAR2_TABLE_300,
1189 p_pricing_ATTRIBUTE25 IN JTF_VARCHAR2_TABLE_300,
1190 p_pricing_ATTRIBUTE26 IN JTF_VARCHAR2_TABLE_300,
1191 p_pricing_ATTRIBUTE27 IN JTF_VARCHAR2_TABLE_300,
1192 p_pricing_ATTRIBUTE28 IN JTF_VARCHAR2_TABLE_300,
1193 p_pricing_ATTRIBUTE29 IN JTF_VARCHAR2_TABLE_300,
1194 p_pricing_ATTRIBUTE30 IN JTF_VARCHAR2_TABLE_300,
1195 p_pricing_ATTRIBUTE31 IN JTF_VARCHAR2_TABLE_300,
1196 p_pricing_ATTRIBUTE32 IN JTF_VARCHAR2_TABLE_300,
1197 p_pricing_ATTRIBUTE33 IN JTF_VARCHAR2_TABLE_300,
1198 p_pricing_ATTRIBUTE34 IN JTF_VARCHAR2_TABLE_300,
1199 p_pricing_ATTRIBUTE35 IN JTF_VARCHAR2_TABLE_300,
1200 p_pricing_ATTRIBUTE36 IN JTF_VARCHAR2_TABLE_300,
1201 p_pricing_ATTRIBUTE37 IN JTF_VARCHAR2_TABLE_300,
1202 p_pricing_ATTRIBUTE38 IN JTF_VARCHAR2_TABLE_300,
1203 p_pricing_ATTRIBUTE39 IN JTF_VARCHAR2_TABLE_300,
1204 p_pricing_ATTRIBUTE40 IN JTF_VARCHAR2_TABLE_300,
1205 p_pricing_ATTRIBUTE41 IN JTF_VARCHAR2_TABLE_300,
1206 p_pricing_ATTRIBUTE42 IN JTF_VARCHAR2_TABLE_300,
1207 p_pricing_ATTRIBUTE43 IN JTF_VARCHAR2_TABLE_300,
1208 p_pricing_ATTRIBUTE44 IN JTF_VARCHAR2_TABLE_300,
1209 p_pricing_ATTRIBUTE45 IN JTF_VARCHAR2_TABLE_300,
1210 p_pricing_ATTRIBUTE46 IN JTF_VARCHAR2_TABLE_300,
1211 p_pricing_ATTRIBUTE47 IN JTF_VARCHAR2_TABLE_300,
1212 p_pricing_ATTRIBUTE48 IN JTF_VARCHAR2_TABLE_300,
1213 p_pricing_ATTRIBUTE49 IN JTF_VARCHAR2_TABLE_300,
1214 p_pricing_ATTRIBUTE50 IN JTF_VARCHAR2_TABLE_300,
1215 p_pricing_ATTRIBUTE51 IN JTF_VARCHAR2_TABLE_300,
1216 p_pricing_ATTRIBUTE52 IN JTF_VARCHAR2_TABLE_300,
1217 p_pricing_ATTRIBUTE53 IN JTF_VARCHAR2_TABLE_300,
1218 p_pricing_ATTRIBUTE54 IN JTF_VARCHAR2_TABLE_300,
1219 p_pricing_ATTRIBUTE55 IN JTF_VARCHAR2_TABLE_300,
1220 p_pricing_ATTRIBUTE56 IN JTF_VARCHAR2_TABLE_300,
1221 p_pricing_ATTRIBUTE57 IN JTF_VARCHAR2_TABLE_300,
1222 p_pricing_ATTRIBUTE58 IN JTF_VARCHAR2_TABLE_300,
1223 p_pricing_ATTRIBUTE59 IN JTF_VARCHAR2_TABLE_300,
1224 p_pricing_ATTRIBUTE60 IN JTF_VARCHAR2_TABLE_300,
1225 p_pricing_ATTRIBUTE61 IN JTF_VARCHAR2_TABLE_300,
1226 p_pricing_ATTRIBUTE62 IN JTF_VARCHAR2_TABLE_300,
1227 p_pricing_ATTRIBUTE63 IN JTF_VARCHAR2_TABLE_300,
1228 p_pricing_ATTRIBUTE64 IN JTF_VARCHAR2_TABLE_300,
1229 p_pricing_ATTRIBUTE65 IN JTF_VARCHAR2_TABLE_300,
1230 p_pricing_ATTRIBUTE66 IN JTF_VARCHAR2_TABLE_300,
1231 p_pricing_ATTRIBUTE67 IN JTF_VARCHAR2_TABLE_300,
1232 p_pricing_ATTRIBUTE68 IN JTF_VARCHAR2_TABLE_300,
1233 p_pricing_ATTRIBUTE69 IN JTF_VARCHAR2_TABLE_300,
1234 p_pricing_ATTRIBUTE70 IN JTF_VARCHAR2_TABLE_300,
1235 p_pricing_ATTRIBUTE71 IN JTF_VARCHAR2_TABLE_300,
1236 p_pricing_ATTRIBUTE72 IN JTF_VARCHAR2_TABLE_300,
1237 p_pricing_ATTRIBUTE73 IN JTF_VARCHAR2_TABLE_300,
1238 p_pricing_ATTRIBUTE74 IN JTF_VARCHAR2_TABLE_300,
1239 p_pricing_ATTRIBUTE75 IN JTF_VARCHAR2_TABLE_300,
1240 p_pricing_ATTRIBUTE76 IN JTF_VARCHAR2_TABLE_300,
1241 p_pricing_ATTRIBUTE77 IN JTF_VARCHAR2_TABLE_300,
1242 p_pricing_ATTRIBUTE78 IN JTF_VARCHAR2_TABLE_300,
1243 p_pricing_ATTRIBUTE79 IN JTF_VARCHAR2_TABLE_300,
1244 p_pricing_ATTRIBUTE80 IN JTF_VARCHAR2_TABLE_300,
1245 p_pricing_ATTRIBUTE81 IN JTF_VARCHAR2_TABLE_300,
1246 p_pricing_ATTRIBUTE82 IN JTF_VARCHAR2_TABLE_300,
1247 p_pricing_ATTRIBUTE83 IN JTF_VARCHAR2_TABLE_300,
1248 p_pricing_ATTRIBUTE84 IN JTF_VARCHAR2_TABLE_300,
1249 p_pricing_ATTRIBUTE85 IN JTF_VARCHAR2_TABLE_300,
1250 p_pricing_ATTRIBUTE86 IN JTF_VARCHAR2_TABLE_300,
1251 p_pricing_ATTRIBUTE87 IN JTF_VARCHAR2_TABLE_300,
1252 p_pricing_ATTRIBUTE88 IN JTF_VARCHAR2_TABLE_300,
1253 p_pricing_ATTRIBUTE89 IN JTF_VARCHAR2_TABLE_300,
1254 p_pricing_ATTRIBUTE90 IN JTF_VARCHAR2_TABLE_300,
1255 p_pricing_ATTRIBUTE91 IN JTF_VARCHAR2_TABLE_300,
1256 p_pricing_ATTRIBUTE92 IN JTF_VARCHAR2_TABLE_300,
1257 p_pricing_ATTRIBUTE93 IN JTF_VARCHAR2_TABLE_300,
1258 p_pricing_ATTRIBUTE94 IN JTF_VARCHAR2_TABLE_300,
1259 p_pricing_ATTRIBUTE95 IN JTF_VARCHAR2_TABLE_300,
1260 p_pricing_ATTRIBUTE96 IN JTF_VARCHAR2_TABLE_300,
1261 p_pricing_ATTRIBUTE97 IN JTF_VARCHAR2_TABLE_300,
1262 p_pricing_ATTRIBUTE98 IN JTF_VARCHAR2_TABLE_300,
1263 p_pricing_ATTRIBUTE99 IN JTF_VARCHAR2_TABLE_300,
1264 p_pricing_ATTRIBUTE100 IN JTF_VARCHAR2_TABLE_300,
1265 p_pricing_CONTEXT IN JTF_VARCHAR2_TABLE_300, -- end pricing attributes.
1266 p_return_attribute_category IN JTF_VARCHAR2_TABLE_200, -- return attributes start.
1267 p_return_attribute1 IN JTF_VARCHAR2_TABLE_200,
1268 p_return_attribute2 IN JTF_VARCHAR2_TABLE_200,
1269 p_return_attribute3 IN JTF_VARCHAR2_TABLE_200,
1270 p_return_attribute4 IN JTF_VARCHAR2_TABLE_200,
1271 p_return_attribute5 IN JTF_VARCHAR2_TABLE_200,
1272 p_return_attribute6 IN JTF_VARCHAR2_TABLE_200,
1273 p_return_attribute7 IN JTF_VARCHAR2_TABLE_200,
1274 p_return_attribute8 IN JTF_VARCHAR2_TABLE_200,
1275 p_return_attribute9 IN JTF_VARCHAR2_TABLE_200,
1276 p_return_attribute10 IN JTF_VARCHAR2_TABLE_200,
1277 p_return_attribute11 IN JTF_VARCHAR2_TABLE_200,
1278 p_return_attribute12 IN JTF_VARCHAR2_TABLE_200,
1279 p_return_attribute13 IN JTF_VARCHAR2_TABLE_200,
1280 p_return_attribute14 IN JTF_VARCHAR2_TABLE_200,
1281 p_return_attribute15 IN JTF_VARCHAR2_TABLE_200,
1282 p_return_attribute16 IN JTF_VARCHAR2_TABLE_200,
1283 p_return_attribute17 IN JTF_VARCHAR2_TABLE_200,
1284 p_return_attribute18 IN JTF_VARCHAR2_TABLE_200,
1285 p_return_attribute19 IN JTF_VARCHAR2_TABLE_200,
1286 p_return_attribute20 IN JTF_VARCHAR2_TABLE_200,
1287 p_return_attribute21 IN JTF_VARCHAR2_TABLE_200,
1288 p_return_attribute22 IN JTF_VARCHAR2_TABLE_200,
1289 p_return_attribute23 IN JTF_VARCHAR2_TABLE_200,
1290 p_return_attribute24 IN JTF_VARCHAR2_TABLE_200,
1291 p_return_attribute25 IN JTF_VARCHAR2_TABLE_200,
1292 p_return_attribute26 IN JTF_VARCHAR2_TABLE_200,
1293 p_return_attribute27 IN JTF_VARCHAR2_TABLE_200,
1294 p_return_attribute28 IN JTF_VARCHAR2_TABLE_200,
1295 p_return_attribute29 IN JTF_VARCHAR2_TABLE_200,
1296 p_return_attribute30 IN JTF_VARCHAR2_TABLE_200, -- end return attributes.
1297 p_cc_txn_id IN NUMBER,
1298 p_currency_code IN VARCHAR2,
1299 p_email IN VARCHAR2,
1300 x_incident_number OUT NOCOPY VARCHAR2,
1301 x_incident_id OUT NOCOPY NUMBER,
1302 x_return_status OUT NOCOPY VARCHAR2,
1303 x_msg_count OUT NOCOPY NUMBER,
1304 x_msg_data OUT NOCOPY VARCHAR2,
1305 p_calculate_price IN JTF_VARCHAR2_TABLE_100,
1306 p_prices IN JTF_NUMBER_TABLE,
1307 p_ret_reasons IN JTF_VARCHAR2_TABLE_100,
1308 p_return_type_ids IN JTF_NUMBER_TABLE,
1309 p_cc_trxn_id IN NUMBER DEFAULT NULL,
1310 p_num_of_attachments IN JTF_NUMBER_TABLE,
1311 p_attachment_title IN JTF_VARCHAR2_TABLE_100,
1312 p_attachment_descr IN JTF_VARCHAR2_TABLE_300,
1313 p_attachment_catId IN JTF_NUMBER_TABLE,
1314 p_attachment_type IN JTF_VARCHAR2_TABLE_100,
1315 p_attachment_url IN JTF_VARCHAR2_TABLE_4000,
1316 p_attachment_text IN JTF_VARCHAR2_TABLE_4000,
1317 p_attachment_file IN JTF_BLOB_TABLE,
1318 p_attachment_file_name IN JTF_VARCHAR2_TABLE_2100,
1319 p_attachment_file_type IN JTF_VARCHAR2_TABLE_100
1320 )
1321 IS
1322 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_RETURNS_UTIL.process_returns';
1323 lc_api_version CONSTANT NUMBER := 1.0;
1324 l_service_request_rec csd_process_pvt.service_request_rec := csd_process_util.sr_rec;
1325 l_party_type VARCHAR2(30);
1326 l_sr_notes_tbl cs_servicerequest_pub.notes_table;
1327 l_serial_flag JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1328 l_ib_control_flag JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1329 l_item_ids_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1330 l_param_value VARCHAR2(500);
1331 l_instance_rec csd_mass_rcv_pvt.instance_rec_type;
1332 l_repln_rec csd_repairs_pub.repln_rec_type;
1333 l_estimate_lines_rec csd_repair_estimate_pvt.repair_estimate_line_rec;
1334 l_estimate_rec csd_repair_estimate_pvt.repair_estimate_rec;
1335 l_estimate_id NUMBER;
1336 l_ro_sc_ids_tbl csd_ro_bulletins_pvt.csd_ro_sc_ids_tbl_type;
1337 l_ib_exists VARCHAR2(2) := 'N';
1338 l_contact_point_id NUMBER;
1339 l_create_contact BOOLEAN := FALSE;
1340 l_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
1341 l_email_rec hz_contact_point_v2pub.email_rec_type;
1342 l_contact_party_id NUMBER;
1343 l_current_party_id NUMBER;
1344 l_ret_reason VARCHAR2(4000);
1345 l_bill_to_site_id NUMBER;
1346 l_ship_to_site_id NUMBER;
1347 l_first BOOLEAN := TRUE;
1348 x_instance_id NUMBER;
1349 x_repair_line_id NUMBER;
1350 x_repair_number VARCHAR2(30);
1351 x_estimate_line_id NUMBER;
1352 x_msg_index_out NUMBER;
1353 x_profile_pl_id NUMBER;
1354 x_currency VARCHAR2(5);
1355 x_contract_pl_id NUMBER;
1356 x_tca_relation_id NUMBER;
1357 x_request_id NUMBER;
1358 retcode VARCHAR2(100);
1359 errbuff VARCHAR2(2000);
1360 l_attach_index NUMBER := 1;
1361 l_repair_line_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1362 l_crt_inst_party_relation VARCHAR2(30) := fnd_profile.value('CSD_DEF_INST_PTY_RELTN');
1363 l_relationship VARCHAR2(30);
1364 x_relationship_created VARCHAR2(1);
1365
1366 TYPE item_attributes_rec IS RECORD
1367 (serial_control_code NUMBER,
1368 ib_trackable_flag VARCHAR2(2)
1369 );
1370 TYPE item_attributes_tbl IS TABLE OF item_attributes_rec INDEX BY BINARY_INTEGER;
1371 l_item_attributes_tbl item_attributes_tbl;
1372
1373 BEGIN
1374
1375 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1376 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin process_returns API');
1377 END IF;
1378
1379 -- standard check for API compatibility.
1380 IF NOT Fnd_Api.Compatible_API_Call
1381 (lc_api_version,
1382 p_api_version,
1383 lc_api_name,
1384 G_PKG_NAME)
1385 THEN
1386 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1387 END IF;
1388
1389 IF Fnd_Api.to_Boolean(p_init_msg_list)
1390 THEN
1391 Fnd_Msg_Pub.initialize;
1392 END IF;
1393
1394 -- init the return status.
1395 x_return_status := fnd_api.g_ret_sts_success;
1396
1397 -- the API will create one service request per return request.
1398 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1399 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Initializing the SR rec');
1400 END IF;
1401 l_service_request_rec.customer_id := p_party_id;
1402 l_service_request_rec.account_id := p_account_id;
1403 l_service_request_rec.request_date := sysdate;
1404 l_service_request_rec.type_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_TYPE');
1405 l_service_request_rec.status_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_STATUS');
1406 l_service_request_rec.severity_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_SEVERITY');
1407 l_service_request_rec.urgency_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_URGENCY');
1408 l_service_request_rec.closed_date := null;
1409 l_service_request_rec.owner_id := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_OWNER');
1410 l_service_request_rec.owner_group_id := NULL;
1411 l_service_request_rec.publish_flag := '';
1412 l_service_request_rec.summary := fnd_profile.value('CSD_BLK_RCV_DEFAULT_SR_SUMMARY');
1413 l_service_request_rec.resource_type := FND_PROFILE.value('CS_SR_DEFAULT_OWNER_TYPE');
1414 l_service_request_rec.bill_to_site_use_id := p_bill_to_addr_use_id;
1415 l_service_request_rec.ship_to_site_use_id := p_ship_to_addr_use_id;
1416 -- get the party type.
1417 SELECT party_type
1418 INTO l_party_type
1419 FROM hz_parties
1420 WHERE party_id = p_party_id;
1421 l_service_request_rec.caller_type := l_party_type;
1422 l_service_request_rec.customer_number := null;
1423 l_service_request_rec.customer_product_id := null;
1424 l_service_request_rec.cp_ref_number := null;
1425 l_service_request_rec.inv_item_revision := null;
1426 l_service_request_rec.inventory_item_id := null;
1427 l_service_request_rec.inventory_org_id := null;
1428 l_service_request_rec.current_serial_number := null;
1429 l_service_request_rec.original_order_number := null;
1430 l_service_request_rec.purchase_order_num := null;
1431 l_service_request_rec.problem_code := null;
1432 l_service_request_rec.exp_resolution_date := null;
1433 l_service_request_rec.sr_creation_channel := 'PHONE';
1434 -- if email is not null then process it.
1435 IF p_email IS NOT NULL
1436 THEN
1437
1438 BEGIN
1439 IF l_party_type = 'PERSON'
1440 THEN
1441 SELECT contacts.contact_point_id,contacts.owner_table_id
1442 INTO l_contact_point_id,l_contact_party_id
1443 FROM hz_contact_points contacts,hz_parties parties
1444 WHERE contacts.owner_table_name = 'HZ_PARTIES'
1445 AND contacts.email_address = p_email
1446 AND parties.party_id = p_party_id
1447 AND contacts.owner_table_id = parties.party_id
1448 AND contacts.status = 'A'
1449 AND ROWNUM < 2;
1450 ELSE
1451 SELECT contacts.contact_point_id,contacts.owner_table_id
1452 INTO l_contact_point_id,l_contact_party_id
1453 FROM hz_contact_points contacts,hz_parties parties,fnd_user usr
1454 WHERE contacts.owner_table_name = 'HZ_PARTIES'
1455 AND contacts.email_address = p_email
1456 AND usr.user_id = fnd_global.user_id
1457 AND parties.party_id = usr.customer_id
1458 AND contacts.owner_table_id = parties.party_id
1459 AND contacts.status = 'A'
1460 AND ROWNUM < 2;
1461 END IF;
1462 EXCEPTION
1463 WHEN NO_DATA_FOUND THEN
1464 l_create_contact := TRUE;
1465 IF l_party_type = 'PERSON'
1466 THEN
1467 l_contact_party_id := p_party_id;
1468 ELSE
1469 SELECT customer_id
1470 INTO l_contact_party_id
1471 FROM fnd_user
1472 WHERE user_id = fnd_global.user_id;
1473 END IF;
1474 END;
1475
1476 IF l_create_contact
1477 THEN
1478 -- need to call hz api to create the contact point.
1479 l_contact_point_rec.contact_point_type := 'EMAIL';
1480 l_contact_point_rec.owner_table_name := 'HZ_PARTIES';
1481 l_contact_point_rec.owner_table_id := l_contact_party_id;
1482 l_contact_point_rec.created_by_module := 'CSDSR';
1483 l_contact_point_rec.application_id := 512;
1484
1485 l_email_rec.email_address := p_email;
1486
1487 -- call API to create the contact point.
1488 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1489 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling HZ_CONTACT_POINT_V2PUB.create_email_contact_point'||x_msg_data);
1490 END IF;
1491 HZ_CONTACT_POINT_V2PUB.create_email_contact_point
1492 (p_init_msg_list => p_init_msg_list,
1493 p_contact_point_rec => l_contact_point_rec,
1494 p_email_rec => l_email_rec,
1495 x_contact_point_id => l_contact_point_id,
1496 x_return_status => x_return_status,
1497 x_msg_count => x_msg_count,
1498 x_msg_data => x_msg_data
1499 );
1500 END IF;
1501 END IF;
1502 IF l_contact_point_id IS NOT NULL
1503 THEN
1504 l_service_request_rec.contact_point_id := l_contact_point_id;
1505 l_service_request_rec.contact_point_type := 'EMAIL';
1506 l_service_request_rec.primary_flag := 'Y';
1507 IF l_party_type = 'PERSON'
1508 THEN
1509 SELECT party.party_type,party.party_id
1510 INTO l_service_request_rec.contact_type,l_service_request_rec.party_id
1511 FROM hz_parties party
1512 WHERE party.party_id = p_party_id;
1513 ELSE
1514 SELECT party.party_type,party.party_id
1515 INTO l_service_request_rec.contact_type,l_service_request_rec.party_id
1516 FROM hz_parties party,fnd_user usr
1517 WHERE usr.user_id = fnd_global.user_id
1518 AND usr.customer_id = party.party_id;
1519 END IF;
1520 ELSE
1521 l_service_request_rec.party_id := null;
1522 l_service_request_rec.contact_point_id := null;
1523 l_service_request_rec.contact_point_type := null;
1524 l_service_request_rec.primary_flag := null;
1525 l_service_request_rec.contact_type := null;
1526 END IF;
1527
1528 -- call to user hook procedure.
1529 CSD_RETURNS_CUHK.before_sr_creation
1530 (p_api_version => 1.0,
1531 p_commit => fnd_api.g_false,
1532 p_init_msg_list => fnd_api.g_true,
1533 p_service_req_rec => l_service_request_rec,
1534 x_return_status => x_return_status,
1535 x_msg_count => x_msg_count,
1536 x_msg_data => x_msg_data
1537 );
1538 -- Call the Service Request API
1539 CSD_PROCESS_PVT.process_service_request
1540 (p_api_version => 1.0,
1541 p_commit => fnd_api.g_false,
1542 p_init_msg_list => fnd_api.g_true,
1543 p_validation_level => fnd_api.g_valid_level_full,
1544 p_action => 'CREATE',
1545 p_incident_id => NULL,
1546 p_service_request_rec => l_service_request_rec,
1547 p_notes_tbl => l_sr_notes_tbl,
1548 x_incident_id => x_incident_id,
1549 x_incident_number => x_incident_number,
1550 x_return_status => x_return_status,
1551 x_msg_count => x_msg_count,
1552 x_msg_data => x_msg_data
1553 );
1554
1555 IF NOT x_return_status = FND_API.G_RET_STS_SUCCESS
1556 THEN
1557 -- need to present a good error message to the web user. set it here. To do.
1558 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1559 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in SR creation. Error msg is = '||x_msg_data);
1560 END IF;
1561 RAISE FND_API.G_EXC_ERROR;
1562 END IF;
1563 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1564 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Created the SR, incident id = '||x_incident_id);
1565 END IF;
1566 -- call to user hook procedure.
1567 CSD_RETURNS_CUHK.after_sr_creation
1568 (p_api_version => 1.0,
1569 p_commit => fnd_api.g_false,
1570 p_init_msg_list => fnd_api.g_true,
1571 p_incident_id => x_incident_id,
1572 x_return_status => x_return_status,
1573 x_msg_count => x_msg_count,
1574 x_msg_data => x_msg_data
1575 );
1576
1577 -- need to create the repair orders.
1578 -- check if the instance is present.
1579 -- few of the parameters needs to be applied here.
1580 -- bulk fetch item controls.
1581
1582 SELECT serial_number_control_code,comms_nl_trackable_flag,inventory_item_id
1583 BULK COLLECT INTO
1584 l_serial_flag,l_ib_control_flag,l_item_ids_tbl
1585 FROM mtl_system_items_b
1586 WHERE inventory_item_id IN
1587 (SELECT * FROM TABLE(CAST(p_item_ids AS JTF_NUMBER_TABLE))
1588 )
1589 AND organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
1590
1591 -- bug#14094846, subhat.
1592 FOR i IN 1 ..l_item_ids_tbl.COUNT
1593 LOOP
1594 IF NOT l_item_attributes_tbl.EXISTS(l_item_ids_tbl(i))
1595 THEN
1596 l_item_attributes_tbl(l_item_ids_tbl(i)).serial_control_code := l_serial_flag(i);
1597 l_item_attributes_tbl(l_item_ids_tbl(i)).ib_trackable_flag := l_ib_control_flag(i);
1598 END IF;
1599 END LOOP;
1600
1601 FOR i IN 1 ..p_item_ids.COUNT
1602 LOOP
1603 IF p_serial_nums.COUNT <> 0 AND p_serial_nums(i) IS NOT NULL
1604 THEN
1605 IF NVL(l_item_attributes_tbl(p_item_ids(i)).ib_trackable_flag ,'N') = 'Y'
1606 THEN
1607 BEGIN
1608 SELECT 'Y',instance_id,owner_party_id
1609 INTO l_ib_exists,x_instance_id,l_current_party_id
1610 FROM csi_item_instances
1611 WHERE inventory_item_id = p_item_ids(i)
1612 AND serial_number = p_serial_nums(i);
1613 EXCEPTION
1614 WHEN NO_DATA_FOUND THEN
1615 l_ib_exists := 'N';
1616 END;
1617 END IF;
1618
1619 ELSIF p_external_refs(i) IS NOT NULL
1620 THEN
1621 IF NVL(l_item_attributes_tbl(p_item_ids(i)).ib_trackable_flag ,'N') = 'Y'
1622 THEN
1623 BEGIN
1624 SELECT 'Y',instance_id,owner_party_id
1625 INTO l_ib_exists,x_instance_id,l_current_party_id
1626 FROM csi_item_instances
1627 WHERE inventory_item_id = p_item_ids(i)
1628 AND external_reference = p_external_refs(i);
1629 EXCEPTION
1630 WHEN NO_DATA_FOUND THEN
1631 l_ib_exists := 'N';
1632 END;
1633 END IF;
1634
1635 END IF;
1636 IF l_ib_exists = 'Y'
1637 THEN
1638
1639 BEGIN
1640 SELECT relationship_type_code
1641 INTO l_relationship
1642 FROM csi_i_parties
1643 WHERE instance_id = x_instance_id
1644 AND party_id = p_party_id
1645 AND SYSDATE BETWEEN NVL(active_start_date, SYSDATE - 1) AND NVL(active_end_date,SYSDATE+1)
1646 AND relationship_type_code = NVL(l_crt_inst_party_relation,relationship_type_code)
1647 AND ROWNUM < 2;
1648 EXCEPTION
1649 WHEN NO_DATA_FOUND THEN
1650 l_relationship := NULL;
1651 END;
1652
1653 IF l_crt_inst_party_relation IS NOT NULL AND l_relationship IS NULL
1654 THEN
1655
1656 csd_repairs_util.create_inst_party_relation
1657 (p_api_version_number => 1.0,
1658 p_commit => 'F',
1659 p_init_msg_list => 'F',
1660 x_return_status => x_return_status,
1661 x_msg_count => x_msg_count,
1662 x_msg_data => x_msg_data,
1663 x_relationship_created => x_relationship_created,
1664 p_sr_party_id => p_party_id,
1665 p_sr_account_id => p_account_id,
1666 p_instance_id => x_instance_id,
1667 p_relationship_type => l_crt_inst_party_relation
1668 );
1669
1670 IF x_return_status <> fnd_api.g_ret_sts_success
1671 THEN
1672 RAISE fnd_api.g_exc_error;
1673 END IF;
1674
1675 ELSIF l_current_party_id <> p_party_id AND (l_crt_inst_party_relation IS NULL AND l_relationship IS NULL)
1676 THEN
1677 return_parameter_value
1678 (p_api_version => 1.0,
1679 p_init_msg_list => p_init_msg_list,
1680 p_commit => p_commit,
1681 p_param_code => 'CHANGE_IB_OWNERSHIP',
1682 p_return_type => NULL,
1683 p_customer_id => p_party_id,
1684 p_bill_to_site_use_id => p_bill_to_addr_use_id,
1685 p_ship_to_site_use_id => p_ship_to_addr_use_id,
1686 p_item_id => p_item_ids(i),
1687 p_item_category_id => NULL,
1688 p_contract_id => p_contract_ids(i),
1689 x_param_value => l_param_value,
1690 x_return_status => x_return_status,
1691 x_msg_count => x_msg_count,
1692 x_msg_data => x_msg_data
1693 );
1694
1695 IF NVL(l_param_value,'N') = 'Y'
1696 THEN
1697 -- change IB ownership
1698 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1699 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'calling csd_repairs_util.change_item_ib_owner to change IB owner');
1700 END IF;
1701 csd_repairs_util.change_item_ib_owner
1702 (p_instance_id => x_instance_id,
1703 p_new_owner_party_id => p_party_id,
1704 p_new_owner_account_id => p_account_id,
1705 p_current_owner_party_id => l_current_party_id,
1706 x_return_status => x_return_status,
1707 x_msg_count => x_msg_count,
1708 x_msg_data => x_msg_data,
1709 x_tca_relation_id => x_tca_relation_id
1710 );
1711
1712 IF x_return_status <> fnd_api.g_ret_sts_success
1713 THEN
1714 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1715 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in change owner. Message is: '||x_msg_data);
1716 END IF;
1717 RAISE fnd_api.g_exc_error;
1718 END IF;
1719 END IF;
1720 END IF;
1721 END IF;
1722
1723
1724 IF (p_instance_ids IS NULL OR p_instance_ids(i) IS NULL) AND NVL(l_item_attributes_tbl(p_item_ids(i)).ib_trackable_flag ,'N') = 'Y' AND l_ib_exists = 'N'
1725 THEN
1726 -- check if we need to create the instance id.
1727 l_param_value := null;
1728 return_parameter_value
1729 (p_api_version => 1.0,
1730 p_init_msg_list => p_init_msg_list,
1731 p_commit => p_commit,
1732 p_param_code => 'CREATE_IB_FOR_RET',
1733 p_return_type => NULL,
1734 p_customer_id => p_party_id,
1735 p_bill_to_site_use_id => p_bill_to_addr_use_id,
1736 p_ship_to_site_use_id => p_ship_to_addr_use_id,
1737 p_item_id => p_item_ids(i),
1738 p_item_category_id => NULL,
1739 p_contract_id => p_contract_ids(i),
1740 x_param_value => l_param_value,
1741 x_return_status => x_return_status,
1742 x_msg_count => x_msg_count,
1743 x_msg_data => x_msg_data
1744 );
1745
1746 IF NVL(l_param_value,'N') = 'Y'
1747 THEN
1748 -- create ib instance.
1749 l_instance_rec.party_site_use_id := p_ship_to_addr_use_id;
1750 l_instance_rec.inventory_item_id := p_item_ids(i);
1751 l_instance_rec.instance_id := NULL;
1752 l_instance_rec.instance_number := NULL;
1753 l_instance_rec.serial_number := p_serial_nums(i);
1754 l_instance_rec.lot_number := NULL;
1755 l_instance_rec.quantity := 1;
1756 l_instance_rec.uom := p_uom_codes(i);
1757 l_instance_rec.party_id := p_party_id;
1758 l_instance_rec.account_id := p_account_id;
1759 l_instance_rec.mfg_serial_number_flag := 'N';
1760 l_instance_rec.item_revision := NULL;
1761 l_instance_rec.external_reference := NULL;
1762 csd_mass_rcv_pvt.create_item_instance
1763 (p_api_version => 1.0,
1764 p_init_msg_list => fnd_api.g_false,
1765 p_commit => fnd_api.g_false,
1766 p_validation_level => fnd_api.g_valid_level_full,
1767 x_return_status => x_return_status,
1768 x_msg_count => x_msg_count,
1769 x_msg_data => x_msg_data,
1770 px_instance_rec => l_instance_rec,
1771 x_instance_id => x_instance_id
1772 );
1773
1774 IF x_return_status <> fnd_api.g_ret_sts_success
1775 THEN
1776 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1777 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in csd_mass_rcv_pvt.create_item_instance. Message is: '||x_msg_data);
1778 END IF;
1779 RAISE fnd_api.g_exc_error;
1780 END IF;
1781
1782 END IF;
1783 END IF;
1784
1785 -- Automatically create a relationship with the owner.
1786 -- all the other parameters would have been calculated at different stages till now. So no need to create any more of it.
1787
1788 -- assign the values to the repair order rec.
1789 l_repln_rec.contract_line_id := p_contract_line_ids(i);
1790 IF p_contract_line_ids(i) IS NULL
1791 THEN
1792 l_repln_rec.resolve_by_date := NULL;
1793 END IF;
1794 IF p_price_lists IS NULL OR p_price_lists(i) IS NULL
1795 THEN
1796 -- get the currency code and price list.
1797 csd_process_util.get_ro_default_curr_pl
1798 (p_api_version => 1.0,
1799 p_init_msg_list => p_init_msg_list,
1800 p_incident_id => NULL,
1801 p_repair_type_id => p_repair_type_ids(i),
1802 p_ro_contract_line_id => p_contract_line_ids(i),
1803 x_contract_pl_id => x_contract_pl_id,
1804 x_profile_pl_id => x_profile_pl_id,
1805 x_currency_code => x_currency,
1806 x_return_status => x_return_status,
1807 x_msg_count => x_msg_count,
1808 x_msg_data => x_msg_data
1809 );
1810 IF x_contract_pl_id IS NOT NULL
1811 THEN
1812 l_repln_rec.price_list_header_id := x_contract_pl_id;
1813 ELSE
1814 l_repln_rec.price_list_header_id := x_profile_pl_id;
1815 END IF;
1816 l_repln_rec.currency_code := x_currency;
1817 ELSE
1818 l_repln_rec.price_list_header_id := p_price_lists(i);
1819 l_repln_rec.currency_code := p_currency_code;
1820 END IF;
1821
1822 IF NVL(l_item_attributes_tbl(p_item_ids(i)).serial_control_code ,1) IN (2,5,6) AND p_quantities(i) > 1
1823 THEN
1824 l_repln_rec.status := 'D';
1825 ELSE
1826 l_repln_rec.status := 'O';
1827 END IF;
1828 l_repln_rec.inventory_org_id := fnd_api.g_miss_num;
1829 l_repln_rec.resource_group := fnd_api.g_miss_num;
1830 l_repln_rec.ro_priority_code := fnd_api.g_miss_char;
1831 l_repln_rec.resource_id := fnd_api.g_miss_num;
1832 l_repln_rec.incident_id := x_incident_id;
1833 l_repln_rec.customer_product_id := x_instance_id;
1834 l_repln_rec.repair_type_id := p_repair_type_ids(i);
1835 l_repln_rec.repair_group_id := null;
1836 l_repln_rec.inventory_item_id := p_item_ids(i);
1837 l_repln_rec.unit_of_measure := p_uom_codes(i);
1838 l_repln_rec.approval_required_flag := NVL(fnd_profile.value('CSD_CUST_APPROVAL_REQD'),'N');
1839 l_repln_rec.quantity := p_quantities(i);
1840 l_repln_rec.serial_number := p_serial_nums(i);
1841
1842 -- get the autoprocess RMA flag.
1843 SELECT NVL(auto_process_rma,'N'),repair_mode
1844 INTO l_repln_rec.auto_process_rma,l_repln_rec.repair_mode
1845 FROM csd_repair_types_b
1846 WHERE repair_type_id = l_repln_rec.repair_type_id;
1847
1848 -- assign the flexfield attributes.
1849 l_repln_rec.return_attribute_category :=p_return_attribute_category(i);
1850 l_repln_rec.return_attribute1 :=p_return_attribute1(i);
1851 l_repln_rec.return_attribute2 :=p_return_attribute2(i);
1852 l_repln_rec.return_attribute3 :=p_return_attribute3(i);
1853 l_repln_rec.return_attribute4 :=p_return_attribute4(i);
1854 l_repln_rec.return_attribute5 :=p_return_attribute5(i);
1855 l_repln_rec.return_attribute6 :=p_return_attribute6(i);
1856 l_repln_rec.return_attribute7 :=p_return_attribute7(i);
1857 l_repln_rec.return_attribute8 :=p_return_attribute8(i);
1858 l_repln_rec.return_attribute9 :=p_return_attribute9(i);
1859 l_repln_rec.return_attribute10 :=p_return_attribute10(i);
1860 l_repln_rec.return_attribute11 :=p_return_attribute11(i);
1861 l_repln_rec.return_attribute12 :=p_return_attribute12(i);
1862 l_repln_rec.return_attribute13 :=p_return_attribute13(i);
1863 l_repln_rec.return_attribute14 :=p_return_attribute14(i);
1864 l_repln_rec.return_attribute15 :=p_return_attribute15(i);
1865 l_repln_rec.return_attribute16 :=p_return_attribute16(i);
1866 l_repln_rec.return_attribute17 :=p_return_attribute17(i);
1867 l_repln_rec.return_attribute18 :=p_return_attribute18(i);
1868 l_repln_rec.return_attribute19 :=p_return_attribute19(i);
1869 l_repln_rec.return_attribute20 :=p_return_attribute20(i);
1870 l_repln_rec.return_attribute21 :=p_return_attribute21(i);
1871 l_repln_rec.return_attribute22 :=p_return_attribute22(i);
1872 l_repln_rec.return_attribute23 :=p_return_attribute23(i);
1873 l_repln_rec.return_attribute24 :=p_return_attribute24(i);
1874 l_repln_rec.return_attribute25 :=p_return_attribute25(i);
1875 l_repln_rec.return_attribute26 :=p_return_attribute26(i);
1876 l_repln_rec.return_attribute27 :=p_return_attribute27(i);
1877 l_repln_rec.return_attribute28 :=p_return_attribute28(i);
1878 l_repln_rec.return_attribute29 :=p_return_attribute29(i);
1879 l_repln_rec.return_attribute30 :=p_return_attribute30(i);
1880 l_repln_rec.return_type_id := p_return_type_ids(i);
1881
1882 -- call user hook API.
1883 CSD_RETURNS_CUHK.before_repair_order
1884 (p_api_version => 1.0,
1885 p_commit => p_commit,
1886 p_init_msg_list => p_init_msg_list,
1887 p_incident_id => x_incident_id,
1888 p_repln_rec => l_repln_rec,
1889 x_return_status => x_return_status,
1890 x_msg_count => x_msg_count,
1891 x_msg_data => x_msg_data
1892 );
1893 -- call the create repair order API.
1894 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1895 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling CSD_REPAIRS_PVT.Create_Repair_Order.');
1896 END IF;
1897 CSD_REPAIRS_PVT.Create_Repair_Order
1898 (p_api_version_number => 1.0,
1899 p_commit => p_commit,
1900 p_init_msg_list => p_init_msg_list,
1901 p_validation_level => fnd_api.g_valid_level_full,
1902 p_repair_line_id => x_repair_line_id,
1903 p_Repln_Rec => l_repln_rec,
1904 x_repair_line_id => x_repair_line_id,
1905 x_repair_number => x_repair_number,
1906 x_return_status => x_return_status,
1907 x_msg_count => x_msg_count,
1908 x_msg_data => x_msg_data
1909 );
1910
1911 IF x_return_status <> fnd_api.g_ret_sts_success
1912 THEN
1913 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1914 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Error in CSD_REPAIRS_PVT.Create_Repair_Order. Message is: '||x_msg_data);
1915 END IF;
1916 RAISE fnd_api.g_exc_error;
1917 END IF;
1918
1919 CSD_RETURNS_CUHK.after_repair_order
1920 (p_api_version => 1.0,
1921 p_commit => p_commit,
1922 p_init_msg_list => p_init_msg_list,
1923 p_incident_id => x_incident_id,
1924 p_repair_line_id => x_repair_line_id,
1925 x_return_status => x_return_status,
1926 x_msg_count => x_msg_count,
1927 x_msg_data => x_msg_data
1928 );
1929
1930 -- link the service bulletins if any. We are specifically looking for the shipping instructions bulletin.
1931 CSD_RO_BULLETINS_PVT.LINK_BULLETINS_TO_RO
1932 (p_api_version_number => 1.0,
1933 p_init_msg_list => p_init_msg_list,
1934 p_commit => p_commit,
1935 p_validation_level => fnd_api.g_valid_level_full,
1936 p_repair_line_id => x_repair_line_id,
1937 px_ro_sc_ids_tbl => l_ro_sc_ids_tbl,
1938 x_return_status => x_return_status,
1939 x_msg_count => x_msg_count,
1940 x_msg_data => x_msg_data
1941 );
1942
1943 -- link the attachments to the repair order.
1944 FOR k IN 1 ..p_num_of_attachments(i)
1945 LOOP
1946 l_repair_line_id_tbl.extend;
1947 l_repair_line_id_tbl(1) := x_repair_line_id;
1948 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1949 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling csd_repair_manager_util.mass_create_attachments to create attachments.');
1950 END IF;
1951 -- call the create attachments API.
1952 csd_repair_manager_util.mass_create_attachments
1953 (p_api_version => 1.0,
1954 p_commit => p_commit,
1955 p_init_msg_list => p_init_msg_list,
1956 p_repair_line_ids => l_repair_line_id_tbl,
1957 p_attach_type => UPPER(p_attachment_type(l_attach_index)),
1958 p_file_input => p_attachment_file(l_attach_index),
1959 p_attach_cat_id => p_attachment_catId(l_attach_index),
1960 p_attach_descr => p_attachment_descr(l_attach_index),
1961 p_attach_title => p_attachment_title(l_attach_index),
1962 p_file_name => p_attachment_file_name(l_attach_index),
1963 p_content_type => p_attachment_file_type(l_attach_index),
1964 p_text => p_attachment_text(l_attach_index),
1965 x_return_status => x_return_status,
1966 x_msg_count => x_msg_count,
1967 x_msg_data => x_msg_data
1968 );
1969
1970 l_attach_index := l_attach_index + 1;
1971 l_repair_line_id_tbl.DELETE(1);
1972 END LOOP;
1973
1974 -- we need to create an estimate line too.
1975 IF NVL(p_calculate_price(i),'N') = 'Y'
1976 THEN
1977 -- we need a repair estimate header to be saved.
1978 l_estimate_rec.repair_estimate_id := NULL;
1979 l_estimate_rec.repair_line_id := x_repair_line_id;
1980 l_estimate_rec.note_id := NULL;
1981 l_estimate_rec.estimate_date := SYSDATE;
1982 l_estimate_rec.estimate_status := 'NEW'; -- travi
1983 l_estimate_rec.lead_time := NULL;
1984 l_estimate_rec.lead_time_uom := NULL;
1985 l_estimate_rec.work_summary := l_service_request_rec.summary;
1986 l_estimate_rec.po_number := NULL;
1987 l_estimate_rec.estimate_reason_code := NULL;
1988 l_estimate_rec.last_update_date := sysdate;
1989 l_estimate_rec.creation_date := sysdate;
1990 l_estimate_rec.last_updated_by := NULL;
1991 l_estimate_rec.created_by := NULL;
1992 l_estimate_rec.last_update_login := NULL;
1993 l_estimate_rec.attribute1 := NULL;
1994 l_estimate_rec.attribute2 := NULL;
1995 l_estimate_rec.attribute3 := NULL;
1996 l_estimate_rec.attribute4 := NULL;
1997 l_estimate_rec.attribute5 := NULL;
1998 l_estimate_rec.attribute6 := NULL;
1999 l_estimate_rec.attribute7 := NULL;
2000 l_estimate_rec.attribute8 := NULL;
2001 l_estimate_rec.attribute9 := NULL;
2002 l_estimate_rec.attribute10 := NULL;
2003 l_estimate_rec.attribute11 := NULL;
2004 l_estimate_rec.attribute12 := NULL;
2005 l_estimate_rec.attribute13 := NULL;
2006 l_estimate_rec.attribute14 := NULL;
2007 l_estimate_rec.attribute15 := NULL;
2008 l_estimate_rec.context := NULL;
2009 l_estimate_rec.estimate_freeze_flag := NULL;
2010 l_estimate_rec.object_version_number := 1;
2011 l_estimate_rec.security_group_id := NULL;
2012 l_estimate_rec.not_to_exceed := NULL;
2013
2014 -- call API to create the repair estimate.
2015 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2016 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling csd_repair_estimate_pvt.create_repair_estimate for repair line id= '||x_repair_line_id);
2017 END IF;
2018 csd_repair_estimate_pvt.create_repair_estimate
2019 (p_api_version => 1.0,
2020 p_commit => p_commit,
2021 p_init_msg_list => p_init_msg_list,
2022 p_validation_level => 0,
2023 x_estimate_rec => l_estimate_rec,
2024 x_estimate_id => l_estimate_id,
2025 x_return_status => x_return_status,
2026 x_msg_count => x_msg_count,
2027 x_msg_data => x_msg_data
2028 );
2029
2030 IF x_return_status <> fnd_api.g_ret_sts_success
2031 THEN
2032 RAISE fnd_api.g_exc_error;
2033 END IF;
2034
2035 -- create the estimate lines.
2036 l_estimate_lines_rec.repair_line_id := x_repair_line_id;
2037 l_estimate_lines_rec.repair_estimate_line_id := null;
2038 l_estimate_lines_rec.repair_estimate_id := l_estimate_rec.repair_estimate_id;
2039 l_estimate_lines_rec.estimate_detail_id := null;
2040 l_estimate_lines_rec.incident_id := x_incident_id;
2041 IF NOT G_ESTIMATE_CONSTANTS_CACHE.EXISTS(p_item_ids(i))
2042 THEN
2043 SELECT service.txn_billing_type_id,service.transaction_type_id,types.business_process_id
2044 INTO G_ESTIMATE_CONSTANTS_CACHE(p_item_ids(i)).txn_billing_type_id,
2045 G_ESTIMATE_CONSTANTS_CACHE(p_item_ids(i)).transaction_type_id,G_ESTIMATE_CONSTANTS_CACHE(p_item_ids(i)).business_process_id
2046 FROM csd_repair_types_sar_vl service,csd_repair_types_b types
2047 WHERE service.repair_type_id = p_repair_type_ids(i)
2048 AND service.repair_type_id = types.repair_type_id
2049 AND service.billing_type = 'M';
2050 END IF;
2051 l_estimate_lines_rec.transaction_type_id := G_ESTIMATE_CONSTANTS_CACHE(p_item_ids(i)).transaction_type_id;
2052 l_estimate_lines_rec.business_process_id := G_ESTIMATE_CONSTANTS_CACHE(p_item_ids(i)).business_process_id;
2053 l_estimate_lines_rec.txn_billing_type_id := G_ESTIMATE_CONSTANTS_CACHE(p_item_ids(i)).txn_billing_type_id;
2054 l_estimate_lines_rec.original_source_id := null;
2055 l_estimate_lines_rec.original_source_code := null;
2056 l_estimate_lines_rec.source_id := null;
2057 l_estimate_lines_rec.source_code := null;
2058
2059 l_estimate_lines_rec.item_cost := NULL;
2060 l_estimate_lines_rec.customer_product_id := null;
2061 l_estimate_lines_rec.reference_number := null;
2062 l_estimate_lines_rec.item_revision := null;
2063 l_estimate_lines_rec.justification_notes := null;
2064 l_estimate_lines_rec.estimate_status := 'NEW';
2065 l_estimate_lines_rec.order_number := null;
2066 l_estimate_lines_rec.purchase_order_num := NULL;
2067 l_estimate_lines_rec.source_number := null;
2068 l_estimate_lines_rec.status := 'O';
2069 l_estimate_lines_rec.currency_code := p_currency_code;
2070 l_estimate_lines_rec.line_category_code := 'ORDER';
2071 l_estimate_lines_rec.unit_of_measure_code := p_uom_codes(i);
2072 l_estimate_lines_rec.original_source_number := null;
2073 l_estimate_lines_rec.order_header_id := null;
2074 l_estimate_lines_rec.order_line_id := null;
2075 l_estimate_lines_rec.inventory_item_id := p_item_ids(i);
2076 l_estimate_lines_rec.after_warranty_cost := p_prices(i);
2077 l_estimate_lines_rec.selling_price := G_SELLING_PRICE_CACHE(p_item_ids(i));
2078 l_estimate_lines_rec.original_system_reference := null;
2079 l_estimate_lines_rec.estimate_quantity := p_quantities(i);
2080 l_estimate_lines_rec.serial_number := null;
2081 l_estimate_lines_rec.lot_number := null;
2082 l_estimate_lines_rec.instance_id := null;
2083 l_estimate_lines_rec.instance_number := null;
2084 l_estimate_lines_rec.price_list_id := p_price_lists(i);
2085 l_estimate_lines_rec.contract_line_id := p_contract_line_ids(i);
2086 l_estimate_lines_rec.contract_id := p_contract_ids(i);
2087 l_estimate_lines_rec.contract_number := NULL;
2088 l_estimate_lines_rec.coverage_id := NULL;
2089 l_estimate_lines_rec.coverage_txn_group_id := NULL;
2090 l_estimate_lines_rec.coverage_bill_rate_id := NULL;
2091 l_estimate_lines_rec.sub_inventory := NULL;
2092
2093 l_estimate_lines_rec.organization_id := fnd_profile.value('CSD_DEF_REP_INV_ORG');
2094 l_estimate_lines_rec.invoice_to_org_id := NULL;
2095 l_estimate_lines_rec.ship_to_org_id := NULL;
2096 l_estimate_lines_rec.no_charge_flag := 'N';
2097 l_estimate_lines_rec.return_reason := NULL; --p_ret_reasons(i);
2098 l_estimate_lines_rec.return_by_date := SYSDATE;
2099 l_estimate_lines_rec.last_update_date := SYSDATE;
2100 l_estimate_lines_rec.creation_date := SYSDATE;
2101 l_estimate_lines_rec.last_updated_by := fnd_global.user_id;
2102 l_estimate_lines_rec.created_by := fnd_global.user_id;
2103 l_estimate_lines_rec.last_update_login := fnd_global.login_id;
2104 l_estimate_lines_rec.attribute1 := null;
2105 l_estimate_lines_rec.attribute2 := null;
2106 l_estimate_lines_rec.attribute3 := null;
2107 l_estimate_lines_rec.attribute4 := null;
2108 l_estimate_lines_rec.attribute5 := null;
2109 l_estimate_lines_rec.attribute6 := null;
2110 l_estimate_lines_rec.attribute7 := null;
2111 l_estimate_lines_rec.attribute8 := null;
2112 l_estimate_lines_rec.attribute9 := null;
2113 l_estimate_lines_rec.attribute10 := null;
2114 l_estimate_lines_rec.attribute11 := null;
2115 l_estimate_lines_rec.attribute12 := null;
2116 l_estimate_lines_rec.attribute13 := null;
2117 l_estimate_lines_rec.attribute14 := null;
2118 l_estimate_lines_rec.attribute15 := null;
2119 l_estimate_lines_rec.context := null;
2120 l_estimate_lines_rec.object_version_number := 1;
2121 l_estimate_lines_rec.security_group_id := null;
2122 l_estimate_lines_rec.resource_id := null;
2123 l_estimate_lines_rec.override_charge_flag := 'N';
2124 l_estimate_lines_rec.interface_to_om_flag := 'N';
2125 l_estimate_lines_rec.charge_line_type := 'ESTIMATE';
2126 l_estimate_lines_rec.apply_contract_discount := 'N';
2127 l_estimate_lines_rec.est_line_source_type_code := 'MANUAL';
2128 l_estimate_lines_rec.est_line_source_id1 := null;
2129 l_estimate_lines_rec.est_line_source_id2 := null;
2130 l_estimate_lines_rec.ro_service_code_id := null;
2131 l_estimate_lines_rec.contract_discount_amount := G_SELLING_PRICE_CACHE(p_item_ids(i))*p_quantities(i) - p_prices(i);
2132
2133 -- Initialize the pricing rec.
2134
2135 l_estimate_lines_rec.pricing_context := p_pricing_context(i);
2136 l_estimate_lines_rec.pricing_attribute1 := p_pricing_attribute1(i);
2137 l_estimate_lines_rec.pricing_attribute2 := p_pricing_attribute2(i);
2138 l_estimate_lines_rec.pricing_attribute3 := p_pricing_attribute3(i);
2139 l_estimate_lines_rec.pricing_attribute4 := p_pricing_attribute4(i);
2140 l_estimate_lines_rec.pricing_attribute5 := p_pricing_attribute5(i);
2141 l_estimate_lines_rec.pricing_attribute6 := p_pricing_attribute6(i);
2142 l_estimate_lines_rec.pricing_attribute7 := p_pricing_attribute7(i);
2143 l_estimate_lines_rec.pricing_attribute8 := p_pricing_attribute8(i);
2144 l_estimate_lines_rec.pricing_attribute9 := p_pricing_attribute9(i);
2145 l_estimate_lines_rec.pricing_attribute10 := p_pricing_attribute10(i);
2146 l_estimate_lines_rec.pricing_attribute11 := p_pricing_attribute11(i);
2147 l_estimate_lines_rec.pricing_attribute12 := p_pricing_attribute12(i);
2148 l_estimate_lines_rec.pricing_attribute13 := p_pricing_attribute13(i);
2149 l_estimate_lines_rec.pricing_attribute14 := p_pricing_attribute14(i);
2150 l_estimate_lines_rec.pricing_attribute15 := p_pricing_attribute15(i);
2151 l_estimate_lines_rec.pricing_attribute16 := p_pricing_attribute16(i);
2152 l_estimate_lines_rec.pricing_attribute17 := p_pricing_attribute17(i);
2153 l_estimate_lines_rec.pricing_attribute18 := p_pricing_attribute18(i);
2154 l_estimate_lines_rec.pricing_attribute19 := p_pricing_attribute19(i);
2155 l_estimate_lines_rec.pricing_attribute20 := p_pricing_attribute20(i);
2156 l_estimate_lines_rec.pricing_attribute21 := p_pricing_attribute21(i);
2157 l_estimate_lines_rec.pricing_attribute22 := p_pricing_attribute22(i);
2158 l_estimate_lines_rec.pricing_attribute23 := p_pricing_attribute23(i);
2159 l_estimate_lines_rec.pricing_attribute24 := p_pricing_attribute24(i);
2160 l_estimate_lines_rec.pricing_attribute25 := p_pricing_attribute25(i);
2161 l_estimate_lines_rec.pricing_attribute26 := p_pricing_attribute26(i);
2162 l_estimate_lines_rec.pricing_attribute27 := p_pricing_attribute27(i);
2163 l_estimate_lines_rec.pricing_attribute28 := p_pricing_attribute28(i);
2164 l_estimate_lines_rec.pricing_attribute29 := p_pricing_attribute29(i);
2165 l_estimate_lines_rec.pricing_attribute30 := p_pricing_attribute30(i);
2166 l_estimate_lines_rec.pricing_attribute31 := p_pricing_attribute31(i);
2167 l_estimate_lines_rec.pricing_attribute32 := p_pricing_attribute32(i);
2168 l_estimate_lines_rec.pricing_attribute33 := p_pricing_attribute33(i);
2169 l_estimate_lines_rec.pricing_attribute34 := p_pricing_attribute34(i);
2170 l_estimate_lines_rec.pricing_attribute35 := p_pricing_attribute35(i);
2171 l_estimate_lines_rec.pricing_attribute36 := p_pricing_attribute36(i);
2172 l_estimate_lines_rec.pricing_attribute37 := p_pricing_attribute37(i);
2173 l_estimate_lines_rec.pricing_attribute38 := p_pricing_attribute38(i);
2174 l_estimate_lines_rec.pricing_attribute39 := p_pricing_attribute39(i);
2175 l_estimate_lines_rec.pricing_attribute40 := p_pricing_attribute40(i);
2176 l_estimate_lines_rec.pricing_attribute41 := p_pricing_attribute41(i);
2177 l_estimate_lines_rec.pricing_attribute42 := p_pricing_attribute42(i);
2178 l_estimate_lines_rec.pricing_attribute43 := p_pricing_attribute43(i);
2179 l_estimate_lines_rec.pricing_attribute44 := p_pricing_attribute44(i);
2180 l_estimate_lines_rec.pricing_attribute45 := p_pricing_attribute45(i);
2181 l_estimate_lines_rec.pricing_attribute46 := p_pricing_attribute46(i);
2182 l_estimate_lines_rec.pricing_attribute47 := p_pricing_attribute47(i);
2183 l_estimate_lines_rec.pricing_attribute48 := p_pricing_attribute48(i);
2184 l_estimate_lines_rec.pricing_attribute49 := p_pricing_attribute49(i);
2185 l_estimate_lines_rec.pricing_attribute50 := p_pricing_attribute50(i);
2186 l_estimate_lines_rec.pricing_attribute51 := p_pricing_attribute51(i);
2187 l_estimate_lines_rec.pricing_attribute52 := p_pricing_attribute52(i);
2188 l_estimate_lines_rec.pricing_attribute53 := p_pricing_attribute53(i);
2189 l_estimate_lines_rec.pricing_attribute54 := p_pricing_attribute54(i);
2190 l_estimate_lines_rec.pricing_attribute55 := p_pricing_attribute55(i);
2191 l_estimate_lines_rec.pricing_attribute56 := p_pricing_attribute56(i);
2192 l_estimate_lines_rec.pricing_attribute57 := p_pricing_attribute57(i);
2193 l_estimate_lines_rec.pricing_attribute58 := p_pricing_attribute58(i);
2194 l_estimate_lines_rec.pricing_attribute59 := p_pricing_attribute59(i);
2195 l_estimate_lines_rec.pricing_attribute60 := p_pricing_attribute60(i);
2196 l_estimate_lines_rec.pricing_attribute61 := p_pricing_attribute61(i);
2197 l_estimate_lines_rec.pricing_attribute62 := p_pricing_attribute62(i);
2198 l_estimate_lines_rec.pricing_attribute63 := p_pricing_attribute63(i);
2199 l_estimate_lines_rec.pricing_attribute64 := p_pricing_attribute64(i);
2200 l_estimate_lines_rec.pricing_attribute65 := p_pricing_attribute65(i);
2201 l_estimate_lines_rec.pricing_attribute66 := p_pricing_attribute66(i);
2202 l_estimate_lines_rec.pricing_attribute67 := p_pricing_attribute67(i);
2203 l_estimate_lines_rec.pricing_attribute68 := p_pricing_attribute68(i);
2204 l_estimate_lines_rec.pricing_attribute69 := p_pricing_attribute69(i);
2205 l_estimate_lines_rec.pricing_attribute70 := p_pricing_attribute70(i);
2206 l_estimate_lines_rec.pricing_attribute71 := p_pricing_attribute71(i);
2207 l_estimate_lines_rec.pricing_attribute72 := p_pricing_attribute72(i);
2208 l_estimate_lines_rec.pricing_attribute73 := p_pricing_attribute73(i);
2209 l_estimate_lines_rec.pricing_attribute74 := p_pricing_attribute74(i);
2210 l_estimate_lines_rec.pricing_attribute75 := p_pricing_attribute75(i);
2211 l_estimate_lines_rec.pricing_attribute76 := p_pricing_attribute76(i);
2212 l_estimate_lines_rec.pricing_attribute77 := p_pricing_attribute77(i);
2213 l_estimate_lines_rec.pricing_attribute78 := p_pricing_attribute78(i);
2214 l_estimate_lines_rec.pricing_attribute79 := p_pricing_attribute79(i);
2215 l_estimate_lines_rec.pricing_attribute80 := p_pricing_attribute80(i);
2216 l_estimate_lines_rec.pricing_attribute81 := p_pricing_attribute81(i);
2217 l_estimate_lines_rec.pricing_attribute82 := p_pricing_attribute82(i);
2218 l_estimate_lines_rec.pricing_attribute83 := p_pricing_attribute83(i);
2219 l_estimate_lines_rec.pricing_attribute84 := p_pricing_attribute84(i);
2220 l_estimate_lines_rec.pricing_attribute85 := p_pricing_attribute85(i);
2221 l_estimate_lines_rec.pricing_attribute86 := p_pricing_attribute86(i);
2222 l_estimate_lines_rec.pricing_attribute87 := p_pricing_attribute87(i);
2223 l_estimate_lines_rec.pricing_attribute88 := p_pricing_attribute88(i);
2224 l_estimate_lines_rec.pricing_attribute89 := p_pricing_attribute89(i);
2225 l_estimate_lines_rec.pricing_attribute90 := p_pricing_attribute90(i);
2226 l_estimate_lines_rec.pricing_attribute91 := p_pricing_attribute91(i);
2227 l_estimate_lines_rec.pricing_attribute92 := p_pricing_attribute92(i);
2228 l_estimate_lines_rec.pricing_attribute93 := p_pricing_attribute93(i);
2229 l_estimate_lines_rec.pricing_attribute94 := p_pricing_attribute94(i);
2230 l_estimate_lines_rec.pricing_attribute95 := p_pricing_attribute95(i);
2231 l_estimate_lines_rec.pricing_attribute96 := p_pricing_attribute96(i);
2232 l_estimate_lines_rec.pricing_attribute97 := p_pricing_attribute97(i);
2233 l_estimate_lines_rec.pricing_attribute98 := p_pricing_attribute98(i);
2234 l_estimate_lines_rec.pricing_attribute99 := p_pricing_attribute99(i);
2235 l_estimate_lines_rec.pricing_attribute100 := p_pricing_attribute100(i);
2236
2237 -- call the create estimate lines API.
2238 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2239 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling csd_repair_estimate_pvt.create_repair_estimate_lines for repair line id= '||x_repair_line_id);
2240 END IF;
2241 csd_repair_estimate_pvt.create_repair_estimate_lines
2242 (p_api_version => 1.0,
2243 p_commit => p_commit,
2244 p_init_msg_list => p_init_msg_list,
2245 p_validation_level => 0,
2246 x_estimate_line_rec => l_estimate_lines_rec,
2247 x_estimate_line_id => x_estimate_line_id,
2248 x_return_status => x_return_status,
2249 x_msg_count => x_msg_count,
2250 x_msg_data => x_msg_data
2251 );
2252
2253 IF x_return_status <> fnd_api.g_ret_sts_success
2254 THEN
2255 RAISE fnd_api.g_exc_error;
2256 END IF;
2257 END IF;
2258 IF l_first
2259 THEN
2260 l_ret_reason := l_ret_reason||NVL(p_ret_reasons(i),' ');
2261 l_first := FALSE;
2262 ELSE
2263 l_ret_reason := l_ret_reason||','||NVL(p_ret_reasons(i),' ');
2264 END IF;
2265 END LOOP;
2266
2267 IF p_cc_trxn_id IS NOT NULL AND p_cc_trxn_id > 0
2268 THEN
2269 UPDATE csd_cc_trxn_xref SET incident_id = x_incident_id
2270 WHERE transaction_id = p_cc_trxn_id;
2271 END IF;
2272
2273 -- get the bill to site and ship to site ids.
2274 SELECT bill.party_site_id, ship.party_site_id
2275 INTO l_bill_to_site_id, l_ship_to_site_id
2276 FROM hz_party_site_uses bill, hz_party_site_uses ship
2277 WHERE bill.party_site_use_id = p_bill_to_addr_use_id
2278 AND ship.party_site_use_id = p_ship_to_addr_use_id;
2279
2280 -- submit a request to create the logistics lines.
2281 x_request_id := fnd_request.submit_request
2282 (application => 'CSD',
2283 program => 'CSDCRLOG',
2284 argument1 => x_incident_id,
2285 argument2 => l_ret_reason,
2286 argument3 => l_bill_to_site_id,
2287 argument4 => l_ship_to_site_id
2288 );
2289 IF fnd_api.to_boolean(p_commit)
2290 THEN
2291 COMMIT WORK;
2292 END IF;
2293
2294 EXCEPTION
2295 WHEN fnd_api.g_exc_error THEN
2296 FOR j in 1 ..x_msg_count
2297 LOOP
2298 FND_MSG_PUB.Get(
2299 p_msg_index => j,
2300 p_encoded => 'F',
2301 p_data => x_msg_data,
2302 p_msg_index_out => x_msg_index_out);
2303 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2304 THEN
2305 fnd_log.STRING (fnd_log.level_procedure,lc_api_name,'Error count '||x_msg_count||' Message '||x_msg_data);
2306 END IF;
2307 END LOOP;
2308 x_return_status := fnd_api.g_ret_sts_error;
2309
2310 END process_returns;
2311
2312 FUNCTION get_credit_card(p_incident_id IN NUMBER) RETURN VARCHAR2
2313 IS
2314 l_cc_number iby_creditcard.masked_cc_number%TYPE;
2315 BEGIN
2316 SELECT masked_cc_number
2317 INTO l_cc_number
2318 FROM iby_creditcard cc, csd_cc_trxn_xref cctrxn
2319 WHERE cc.instrid = cctrxn.instrument_id
2320 AND cctrxn.incident_id = p_incident_id;
2321 RETURN l_cc_number;
2322 EXCEPTION
2323 WHEN NO_DATA_FOUND THEN
2324 l_cc_number := NULL;
2325 RETURN l_cc_number;
2326 END get_credit_card;
2327
2328 PROCEDURE create_logistics_lines_cp
2329 (errbuf OUT NOCOPY VARCHAR2,
2330 retcode OUT NOCOPY VARCHAR2,
2331 p_incident_id IN NUMBER,
2332 p_return_reason_code IN VARCHAR2,
2333 p_invoice_to_org_id IN NUMBER,
2334 p_ship_to_org_id IN NUMBER
2335 )
2336 IS
2337 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_RETURNS_UTIL.create_logistics_lines_cp';
2338 l_repair_line_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
2339 l_return_reason_code VARCHAR2(4000) := p_return_reason_code;
2340 l_ret_reason VARCHAR2(30);
2341 x_return_status VARCHAR2(1);
2342 x_msg_count NUMBER;
2343 x_msg_data VARCHAR2(2000);
2344
2345 BEGIN
2346 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2347 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'begin the api to create logistics lines..');
2348 END IF;
2349
2350 SELECT repair_line_id
2351 BULK COLLECT INTO l_repair_line_ids
2352 FROM csd_repairs
2353 WHERE incident_id = p_incident_id;
2354
2355 FOR i IN 1 ..l_repair_line_ids.COUNT
2356 LOOP
2357 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2358 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling create default product transaction for repair line id:'||l_repair_line_ids(i));
2359 END IF;
2360 IF INSTR(l_return_reason_code,',') = 0
2361 THEN
2362 l_ret_reason := l_return_reason_code;
2363 l_return_reason_code := null;
2364 ELSE
2365 l_ret_reason := SUBSTR(l_return_reason_code,1,INSTR(l_return_reason_code,',') - 1);
2366 l_return_reason_code := SUBSTR(l_return_reason_code,INSTR(l_return_reason_code,',')+1,LENGTH(l_return_reason_code));
2367 END IF;
2368
2369 IF l_ret_reason = ' '
2370 THEN
2371 l_ret_reason := NULL;
2372 END IF;
2373
2374 csd_process_pvt.create_default_prod_txn
2375 (p_api_version => 1.0,
2376 p_commit => fnd_api.g_false,
2377 p_init_msg_list => fnd_api.g_true,
2378 p_validation_level => 100,
2379 p_repair_line_id => l_repair_line_ids(i),
2380 p_return_reason_code => l_ret_reason,
2381 p_invoice_to_org_id => p_invoice_to_org_id,
2382 p_ship_to_org_id => p_ship_to_org_id,
2383 x_return_status => x_return_status,
2384 x_msg_count => x_msg_count,
2385 x_msg_data => x_msg_data
2386 );
2387 END LOOP;
2388 END create_logistics_lines_cp;
2389
2390 PROCEDURE create_card
2391 (p_api_version IN NUMBER,
2392 p_init_msg_list IN VARCHAR2,
2393 p_commit IN VARCHAR2,
2394 x_return_status OUT NOCOPY VARCHAR2,
2395 x_msg_count OUT NOCOPY NUMBER,
2396 x_msg_data OUT NOCOPY VARCHAR2,
2397 p_card_instrument IN iby_fndcpt_setup_pub.creditcard_rec_type,
2398 x_card_id OUT NOCOPY NUMBER,
2399 x_response OUT NOCOPY iby_fndcpt_common_pub.result_rec_type
2400 )
2401 IS
2402 PRAGMA AUTONOMOUS_TRANSACTION;
2403 BEGIN
2404 iby_fndcpt_setup_pub.Create_Card
2405 (p_api_version => p_api_version,
2406 p_init_msg_list => p_init_msg_list,
2407 p_commit => fnd_api.g_true,
2408 x_return_status => x_return_Status,
2409 x_msg_count => x_msg_count,
2410 x_msg_data => x_msg_data,
2411 p_card_instrument => p_card_instrument,
2412 x_card_id => x_card_id,
2413 x_response => x_response
2414 );
2415 IF x_return_status = fnd_api.g_ret_sts_success
2416 THEN
2417 COMMIT WORK;
2418 ELSE
2419 ROLLBACK;
2420 END IF;
2421 END create_card;
2422 END CSD_RETURNS_UTIL;