[Home] [Help]
PACKAGE BODY: APPS.CS_CHARGE_CREATE_ORDER_PVT
Source
1 PACKAGE BODY CS_Charge_Create_Order_PVT as
2 /* $Header: csxvchob.pls 120.52.12020000.8 2013/05/03 09:15:57 bkanimoz ship $ */
3
4 /*********** Global Variables ********************************/
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_Charge_Create_Order_PVT' ;
6
7
8 /**************************************************************
9 --
10 --Private global variables and functions
11 --
12 *************************************************************/
13
14 G_MAXERRPARMLEN constant number := 200;
15 G_MAXERRLEN constant number := 512;
16 g_oraerrmsg varchar2(600);
17 j number := 1;
18
19
20 procedure get_who_info
21 (
22 p_login_id out NOCOPY number,
23 p_user_id out NOCOPY number
24 ) is
25 begin
26 p_login_id := FND_GLOBAL.Login_Id;
27 p_user_id := FND_GLOBAL.User_Id;
28
29 end get_who_info;
30
31
32 procedure Get_acct_from_party_site
33 (
34 p_party_site_id in number,
35 p_sold_to_customer_party in number,
36 p_sold_to_customer_account in number,
37 p_org_id in number DEFAULT -1,--bug# 4870037
38 p_site_use_flag in VARCHAR2 DEFAULT 'E',--bug# 4870037
39 p_site_use_code in varchar2,
40 x_account_id out NOCOPY number
41 ) is
42
43 -- Bug 6655006
44 Cursor active_accts(p_party_id number) is
45 SELECT CUST_ACCOUNT_ID
46 FROM HZ_CUST_ACCOUNTS_ALL
47 WHERE PARTY_ID = p_party_id
48 AND STATUS = 'A';
49
50 Cursor acct_site_csr(p_org_id number, p_party_site_id number, p_account_id number) IS
51 SELECT cust_acct_site_id
52 FROM hz_cust_acct_sites_all
53 WHERE cust_account_id = p_account_id and
54 party_site_id = p_party_site_id and
55 org_id = p_org_id and
56 status = 'A';
57
58 Cursor acct_site_use_csr(p_cust_acct_site_id number, p_site_use_code varchar2) IS
59 SELECT site_use_id
60 FROM hz_cust_site_uses_all
61 WHERE cust_acct_site_id = p_cust_acct_site_id and
62 site_use_code = p_site_use_code and
63 status = 'A';
64
65 Cursor get_address is
66 SELECT b.address1
67 FROM hz_party_sites a, hz_locations b
68 WHERE a.location_id = b.location_id
69 AND a.party_site_id = p_party_site_id;
70
71 Cursor get_inv_org_name is
72 SELECT org.organization_name
73 FROM org_organization_definitions org
74 WHERE org.organization_id = p_org_id;
75
76 l_party_id number := null;
77 l_cust_acct_site_use varchar2(1) := 'N';
78 l_site_use_id NUMBER;
79 l_bill_ship_addr varchar2(300);
80 l_inv_org_name varchar2(300);
81
82 BEGIN
83
84 IF p_party_site_id IS NULL THEN
85 x_account_id := FND_API.G_MISS_NUM;
86 ELSE
87 SELECT party_id
88 INTO l_party_id
89 FROM HZ_PARTY_SITES
90 WHERE party_site_id = p_party_site_id;
91
92 IF l_party_id = p_sold_to_customer_party THEN
93 IF p_sold_to_customer_account is not null then
94 x_account_id := p_sold_to_customer_account;
95 ELSE
96 x_account_id := FND_API.G_MISS_NUM;
97 END IF;
98 ELSIF l_party_id <> p_sold_to_customer_party THEN
99 BEGIN
100
101 /* SELECT min(CUST_ACCOUNT_ID)
102 INTO x_account_id
103 FROM HZ_CUST_ACCOUNTS_ALL
104 WHERE PARTY_ID = l_party_id
105 AND STATUS = 'A'
106 AND CUST_ACCOUNT_ID is not null;*/
107 /*
108 --Begin : Bug# 4870037
109 IF p_site_use_flag = 'B' THEN
110 SELECT min(CUST_ACCOUNT_ID)
111 INTO x_account_id
112 FROM HZ_CUST_ACCOUNTS_ALL h1
113 WHERE h1.PARTY_ID = l_party_id
114 AND h1.STATUS = 'A'
115 AND h1.CUST_ACCOUNT_ID is not null
116 AND EXISTS (SELECT '1' FROM hz_cust_acct_sites_all h2
117 WHERE h2.party_site_id = p_party_site_id
118 AND h2.CUST_ACCOUNT_ID = h1.CUST_ACCOUNT_ID
119 AND h2.STATUS = 'A'
120 AND h2.ORG_ID = p_org_id
121 AND h2.BILL_TO_FLAG = 'Y');
122 ELSIF p_site_use_flag = 'S' THEN
123 SELECT min(CUST_ACCOUNT_ID)
124 INTO x_account_id
125 FROM HZ_CUST_ACCOUNTS_ALL h1
126 WHERE h1.PARTY_ID = l_party_id
127 AND h1.STATUS = 'A'
128 AND h1.CUST_ACCOUNT_ID is not null
129 AND EXISTS (SELECT '1' FROM hz_cust_acct_sites_all h2
130 WHERE h2.party_site_id = p_party_site_id
131 AND h2.CUST_ACCOUNT_ID = h1.CUST_ACCOUNT_ID
132 AND h2.STATUS = 'A'
133 AND h2.ORG_ID = p_org_id
134 AND h2.SHIP_TO_FLAG = 'Y');
135 END IF;
136 --End : Bug# 4870037
137 */
138 /* Commented the above fix using the minimum logic and the following new logic would loop till it finds an account with
139 valid account site and usage */
140
141 For i in active_accts(l_party_id) loop
142 x_account_id := i.cust_account_id;
143 For j in acct_site_csr(p_org_id, p_party_site_id, i.cust_account_id) loop
144
145 Open acct_site_use_csr(j.cust_acct_site_id, p_site_use_code);
146 fetch acct_site_use_csr into l_site_use_id;
147 IF acct_site_use_csr%FOUND THEN
148 l_cust_acct_site_use := 'Y';
149 Exit;
150 END IF;
151 Close acct_site_use_csr;
152
153 End loop;
154 If nvl(l_cust_acct_site_use,'N') = 'Y' then
155 exit;
156 End if;
157 End loop;
158
159 If nvl(l_cust_acct_site_use,'N') = 'N' then
160 If fnd_profile.value('CS_SR_ACTION_MISS_ACCT') = 'CHG_ABORT_SUB' Then
161 Open get_address;
162 Fetch get_address into l_bill_ship_addr;
163 Close get_address;
164
165 Open get_inv_org_name;
166 Fetch get_inv_org_name into l_inv_org_name;
167 Close get_inv_org_name;
168
169 FND_MESSAGE.Set_Name('CS','CS_SR_NO_VALID_ACCT_SITE_USE');
170 FND_MESSAGE.Set_Token('BILL_SHIP_ADDR',l_bill_ship_addr);
171 FND_MESSAGE.Set_Token('OPER_UNIT',l_inv_org_name);
172 FND_MSG_PUB.Add;
173 RAISE FND_API.G_EXC_ERROR;
174 End if;
175 End if;
176 EXCEPTION
177 WHEN NO_DATA_FOUND THEN
178 x_account_id := FND_API.G_MISS_NUM;
179 END;
180
181 IF x_account_id is null THEN
182 x_account_id := FND_API.G_MISS_NUM;
183 END IF;
184 END IF;
185 END IF;
186 END Get_acct_from_party_site;
187
188
189
190 PROCEDURE validate_acct_site_uses
191 (
192 p_org_id IN number,
193 p_party_site_id IN number,
194 p_account_id IN number,
195 p_site_use_code IN varchar2,
196 x_msg_data OUT NOCOPY VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_return_status OUT NOCOPY VARCHAR2
199 ) IS
200
201 Cursor acct_site_csr(p_org_id number, p_party_site_id number, p_account_id number, p_status varchar2) IS
202 SELECT cust_acct_site_id
203 FROM hz_cust_acct_sites_all
204 WHERE cust_account_id = p_account_id and
205 party_site_id = p_party_site_id and
206 org_id = p_org_id and
207 status = p_status;
208
209 Cursor acct_site_use_csr(p_cust_acct_site_id number, p_site_use_code varchar2, p_status varchar2) IS
210 SELECT site_use_id
211 FROM hz_cust_site_uses_all
212 WHERE cust_acct_site_id = p_cust_acct_site_id and
213 site_use_code = p_site_use_code and
214 status = p_status;
215
216 Cursor get_address is
217 SELECT b.address1
218 FROM hz_party_sites a, hz_locations b
219 WHERE a.location_id = b.location_id
220 AND a.party_site_id = p_party_site_id;
221
222 Cursor get_inv_org_name is
223 SELECT org.organization_name
224 FROM org_organization_definitions org
225 WHERE org.organization_id = p_org_id;
226
227 l_cust_acct_site_id number := null;
228 l_site_use_id number := null;
229 l_bill_ship_addr varchar2(300);
230 l_inv_org_name varchar2(300);
231
232 BEGIN
233
234 x_return_status := FND_API.G_RET_STS_SUCCESS;
235
236 IF p_org_id is not null and
237 p_party_site_id is not null and
238 p_account_id is not null THEN
239
240 Open get_address;
241 Fetch get_address into l_bill_ship_addr;
242 Close get_address;
243
244 Open get_inv_org_name;
245 Fetch get_inv_org_name into l_inv_org_name;
246 Close get_inv_org_name;
247
248 -- Check if an active customer account site exists.
249 open acct_site_csr(p_org_id, p_party_site_id, p_account_id, 'A');
250 fetch acct_site_csr into l_cust_acct_site_id;
251 IF acct_site_csr%NOTFOUND THEN
252 close acct_site_csr;
253 -- Check if an inactive customer account site exists.
254 open acct_site_csr(p_org_id, p_party_site_id, p_account_id, 'I');
255 fetch acct_site_csr into l_cust_acct_site_id;
256 IF acct_site_csr%NOTFOUND THEN
257 close acct_site_csr;
258
259 If fnd_profile.value('CS_SR_ACTION_MISS_ACCT') = 'CHG_ABORT_SUB' Then
260 FND_MESSAGE.Set_Name('CS','CS_SR_NO_VALID_ACCT_SITE_USE');
261 FND_MESSAGE.Set_Token('BILL_SHIP_ADDR',l_bill_ship_addr);
262 FND_MESSAGE.Set_Token('OPER_UNIT',l_inv_org_name);
263 FND_MSG_PUB.Add;
264 RAISE FND_API.G_EXC_ERROR;
265 End if;
266 ELSE
267 close acct_site_csr;
268 -- Raise error if active customer account site doesn't exist but an inactive
269 -- custoemr account site exist. Charges will raise a clear error message instead of
270 -- passing this data to OC.
271 x_return_status := FND_API.G_RET_STS_ERROR;
272 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_INVALID_CUST_ACCT_SITE');
273 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', l_cust_acct_site_id);
274 FND_MESSAGE.SET_TOKEN('PARTY_SITE_ID', p_party_site_id);
275 FND_MSG_PUB.Add;
276 RAISE FND_API.G_EXC_ERROR;
277 END IF;
278 ELSE
279 close acct_site_csr;
280
281 -- Check if an active account site use exists.
282 IF p_site_use_code is not null THEN
283
284 open acct_site_use_csr(l_cust_acct_site_id, p_site_use_code, 'A');
285 fetch acct_site_use_csr into l_site_use_id;
286 IF acct_site_use_csr%NOTFOUND THEN
287 close acct_site_use_csr;
288
289 -- Check if an inactive account site use exists.
290 open acct_site_use_csr(l_cust_acct_site_id, p_site_use_code, 'I');
291 fetch acct_site_use_csr into l_site_use_id;
292 IF acct_site_use_csr%NOTFOUND THEN
293 close acct_site_use_csr;
294
295 If fnd_profile.value('CS_SR_ACTION_MISS_ACCT') = 'CHG_ABORT_SUB' Then
296 FND_MESSAGE.Set_Name('CS','CS_SR_NO_VALID_ACCT_SITE_USE');
297 FND_MESSAGE.Set_Token('BILL_SHIP_ADDR',l_bill_ship_addr);
298 FND_MESSAGE.Set_Token('OPER_UNIT',l_inv_org_name);
299 FND_MSG_PUB.Add;
300 RAISE FND_API.G_EXC_ERROR;
301 End if;
302 ELSE
303 close acct_site_use_csr;
304 -- Raise error if active bill to or ship to account site use doesn't exist but an
305 -- inactive bill to or ship to account site use exist. Charges will raise a clear
306 -- error message instead of passing this data to OC.
307 x_return_status := FND_API.G_RET_STS_ERROR;
308 FND_MESSAGE.SET_NAME('CS', 'CS_CHG_INVALID_ACCT_SITE_USE');
309 FND_MESSAGE.SET_TOKEN('SITE_USE_CODE', p_site_use_code);
310 FND_MESSAGE.SET_TOKEN('ACCT_SITE_USE_ID', l_site_use_id);
311 FND_MESSAGE.SET_TOKEN('ACCT_SITE_ID', l_cust_acct_site_id);
312 FND_MSG_PUB.Add;
313 RAISE FND_API.G_EXC_ERROR;
314 END IF;
315 ELSE
316 close acct_site_use_csr;
317 END IF;
318
319 END IF;
320 END IF;
321 END IF;
322
323 EXCEPTION
324 WHEN FND_API.G_EXC_ERROR THEN
325 x_return_status := FND_API.G_RET_STS_ERROR;
326
327 FND_MSG_PUB.Count_And_Get
328 (p_count => x_msg_count,
329 p_data => x_msg_data);
330
331
332 WHEN OTHERS THEN
333 g_oraerrmsg := substrb(sqlerrm,1,G_MAXERRLEN);
334 fnd_message.set_name('CS','CS_CHG_SUBMIT_ORDER_FAILED');
335 fnd_message.set_token('ROUTINE','CS_Charge_Create_Order_PKG.Submit_Order');
336 fnd_message.set_token('REASON',g_oraerrmsg);
337 app_exception.raise_exception;
338
339 END validate_acct_site_uses;
340 --
341 -- Changes for 11.5.10
342 -- procedure to update charges schema with error messages.
343 --
344 Procedure Update_Errors (p_estimate_detail_id IN NUMBER,
345 p_line_submitted IN VARCHAR2,
346 p_submit_restriction_message IN VARCHAR2,
347 p_submit_error_message IN VARCHAR2,
348 p_submit_from_system IN VARCHAR2
349 ) IS
350
351 pragma AUTONOMOUS_TRANSACTION;
352
353 -- DEADLOCK_DETECTED EXCEPTION ;
354 -- PRAGMA EXCEPTION_INIT(DEADLOCK_DETECTED,-60);
355
356 BEGIN
357
358 -- Standard Start of API Savepoint
359 -- SAVEPOINT Update_Errors;
360
361 IF p_estimate_detail_id IS NOT NULL THEN
362 UPDATE CS_ESTIMATE_DETAILS
363 SET line_submitted = p_line_submitted,
364 submit_restriction_message = p_submit_restriction_message,
365 submit_error_message = p_submit_error_message,
366 submit_from_system = p_submit_from_system,
367 last_update_date = sysdate, -- bug 8838622
368 last_update_login = fnd_global.login_id, -- bug 8838622
369 last_updated_by = fnd_global.user_id -- bug 8838622
370 WHERE Estimate_Detail_Id = p_estimate_detail_id;
371
372 -- dbms_output.put_line('submit_error_message' || substr(p_submit_error_message,1,100));
373
374
375 END IF;
376
377 commit;
378 -- dbms_output.put_line('In the Update_Errors');
379
380 EXCEPTION
381 -- WHEN DEADLOCK_DETECTED THEN
382 -- dbms_output.put_line('dead lock detected');
383 -- ROLLBACK;
384 -- NULL;
385
386 WHEN OTHERS THEN
387 ROLLBACK;
388 FND_MESSAGE.SET_NAME('CS', 'CS_DB_ERROR');
389 FND_MESSAGE.SET_TOKEN(token => 'PROG_NAME', value => 'Cs_Charge_Create_Order_PVT.Update_Errors');
390 FND_MESSAGE.SET_TOKEN(token => 'SQLCODE', value => SQLCODE);
391 FND_MESSAGE.SET_TOKEN(token => 'SQLERRM', value => SQLERRM);
392 FND_MSG_PUB.add;
393
394 END Update_Errors;
395
396
397
398 --
399 -- End of Update_Errors
400 --
401 --
402 -- *******************************************************
403 -- Start of Comments
404 -- *******************************************************
405 -- API Name: Submit_Order
406 -- Type : Public
407 -- Purpose : This API is for submitting an order.
408 -- It is intended for use by the owning module only; contrast to published API.
409 -- Pre-Req :
410 -- Parameters:
411 -- IN
412 -- p_api_version IN NUMBER Required
413 -- p_init_msg_list IN VARCHAR2 Optional
414 -- p_commit IN VARCHAR2 Optional
415 -- p_validation_level IN NUMBER Optional
416 -- p_incident_id IN NUMBER Required
417 -- p_party_id IN NUMBER Required
418 -- p_account_id IN NUMBER Optional see bug#2447927,
419 -- changed p_account_id to optional param.
420 -- p_book_order_flag IN VARCHAR2 Optional
421 -- p_submit_source IN VARCHAR2 Optional
422 -- p_submit_from_system IN VARCHAR2 Optional
423 -- p_book_order_flag IN VARCHAR2 Optional
424 -- OUT:
425 -- x_return_status OUT NOCOPY VARCHAR2
426 -- x_msg_count OUT NOCOPY NUMBER
427 -- x_msg_data OUT NOCOPY VARCHAR2
428
429 -- SSHILPAM 11-Feb-2010 Bug 9312433: CSI api should be called when no instance number is present also
430 -- BKANIMOZ 10-Jul-2012 Bug 9727572 Added start and end date active to cursor get_parent_instance.
431 -- Version : Current version 1.0
432 -- End of Comments
433 --
434 PROCEDURE Submit_Order(
435 p_api_version IN NUMBER,
436 p_init_msg_list IN VARCHAR2,
437 p_commit IN VARCHAR2,
438 p_validation_level IN NUMBER,
439 p_incident_id IN NUMBER,
440 p_party_id IN NUMBER,
441 p_account_id IN NUMBER,
442 p_book_order_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
443 p_submit_source IN VARCHAR2 := FND_API.G_MISS_CHAR,
444 p_submit_from_system IN VARCHAR2 := FND_API.G_MISS_CHAR,
445 x_return_status OUT NOCOPY VARCHAR2,
446 x_msg_count OUT NOCOPY NUMBER,
447 x_msg_data OUT NOCOPY VARCHAR2
448 )
449 IS
450 l_api_name CONSTANT VARCHAR2(30) := 'Submit_Order' ;
451 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
452 l_log_module CONSTANT VARCHAR2(255) := 'cs.plsql.' || l_api_name_full || '.';
453 l_api_version CONSTANT NUMBER := 1.0 ;
454
455 -- l_debug number := ASO_DEBUG_PUB.G_DEBUG_LEVEL ;
456
457 l_billing_flag VARCHAR2(30) ;
458 l_inv_item_id NUMBER ;
459 l_unit_code VARCHAR2(3) ;
460
461 l_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
462 l_header_rec_default ASO_QUOTE_PUB.Qte_Header_Rec_Type;
463 l_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
464 l_Line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
465 l_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_tbl_Type;
466 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
467 l_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
468 l_ln_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type; /* Credit Card 9358401 */
469 l_lot_serial_tbl ASO_ORDER_INT.Lot_Serial_Tbl_Type;
470 l_line_price_adj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
471 l_charges_rec_type CS_Charge_Details_PUB.Charges_Rec_Type;
472
473 i NUMBER:=0;
474 k NUMBER:=0;
475 lx_return_status VARCHAR2(1);
476 lx_msg_count NUMBER;
477 lx_msg_data VARCHAR2(2000);
478 l_workflow_process_id NUMBER;
479
480 -- x_return_status VARCHAR2(1);
481 -- x_msg_count NUMBER;
482 -- x_msg_data VARCHAR2(2000);
483 oe_x_msg_count NUMBER;
484 oe_x_msg_data VARCHAR2(2000);
485
486 x_order_header_rec ASO_ORDER_INT.Order_Header_rec_type;
487 x_order_line_tbl ASO_ORDER_INT.Order_Line_tbl_type;
488 x_order_header_id NUMBER;
489 l_unit_selling_price NUMBER;
490 l_profile_option VARCHAR2(33) ;
491 l_control_rec ASO_ORDER_INT.control_rec_type;
492 l_login_id number;
493 l_user_id number;
494 l_ordered_quantity number;
495 l_ship_ordered_quantity number;
496 l_inv_org_id number;
497 l_record_found VARCHAR2(1) := 'N';
498 l_ship_to_cust_account_id number;
499 l_invoice_to_cust_account_id number;
500 l_ib_lot_number VARCHAR2(30); -- Bug 8284773
501 /* Added for bug 14673342*/
502 TYPE Charges_Order_num IS TABLE OF NUMBER(20)
503 INDEX BY BINARY_INTEGER;
504 l_order_number Charges_Order_num;
505 l_order_num_count Number:=0;
506 l_tempCount Number :=0;
507 l_found_flag Number(1):=0;
508 l_creditcard_count Number(6):=0;
509 /* End for bug 14673342*/
510
511 -- Fix for bug:3509921
512 l_incident_number NUMBER;
513 Resource_Busy EXCEPTION; --7117301
514 PRAGMA EXCEPTION_INIT(Resource_Busy, -00054); --7117301
515
516 -- Following cursor fetches charge lines that are not submitted to
517 -- order management as orders or returns.
518
519 CURSOR Fetch_Est_Dtl(p_incident_id NUMBER,p_inv_org_id NUMBER,p_ctrl_submit_source VARCHAR2,
520 p_ctrl_orig_source VARCHAR2,p_ctrl_source VARCHAR2) IS
521 SELECT edt.incident_id,
522 edt.org_id,
523 edt.estimate_detail_id,
524 edt.currency_code,
525 edt.conversion_type_code,
526 edt.conversion_rate,
527 edt.conversion_rate_date,
528 edt.business_process_id,
529 edt.txn_billing_type_id,
530 edt.price_list_header_id,
531 edt.inventory_item_id,
532 edt.item_revision,
533 edt.unit_of_measure_code,
534 edt.quantity_required,
535 edt.selling_price,
536 edt.after_warranty_cost,
537 edt.invoice_to_org_id,
538 edt.ship_to_org_id,
539 edt.customer_product_id,
540 edt.installed_cp_return_by_date,
541 edt.new_cp_return_by_date, -- Bug 4586140
542 edt.add_to_order_flag,
543 edt.order_header_id,
544 edt.rollup_flag,
545 edt.purchase_order_num,
546 edt.return_reason_code,
547 edt.serial_number return_serial_number,
548 tt.LINE_ORDER_CATEGORY_CODE line_category_code,
549 edt.organization_id,
550 edt.transaction_inventory_org,
551 edt.transaction_sub_inventory,
552 edt.invoice_to_account_id,
553 edt.ship_to_account_id,
554 edt.ship_to_contact_id,
555 edt.bill_to_contact_id,
556 edt.bill_to_party_id,
557 edt.ship_to_party_id,
558 tb.order_type_id,
559 tb.line_type_id,
560 i.comms_nl_trackable_flag,
561 sr.customer_id,
562 sr.account_id,
563 sr.incident_number,
564 tbt.billing_type,
565 cbtc.rollup_item_id,
566 cbtc.billing_category,
567 edt.list_price , -- 4870210
568 i.item_type item_type_code, --6523849
569 /* Credit Card 9358401 */
570 edt.instrument_payment_use_id,
571 sr.instrument_payment_use_id sr_instr_payment_use_id, /* Added for bug 14673342*/
572 tt.calculate_price_flag, -- shachoud 122 Charges Logistics
573 edt.shipping_method, -- 12.1.3 Charges Logistics Project
574 edt.arrival_date_time, -- 12.1.3 Charges Logistics Project
575 edt.cost, -- 12.1.3 Charges Logistics Project
576 edt.available_quantity, -- 12.1.3 Charges Logistics Project
577 edt.parent_instance_id,
578 sr.system_id, -- Bug 11936035
579 edt.no_charge_flag, -- bug 12907768,
580 edt.project_id, --12.2.2 Service Projects Integration
581 edt.project_task_id --12.2.2 Service Projects Integration
582 FROM CS_ESTIMATE_DETAILS edt,
583 CS_TXN_BILLING_OETXN_ALL tb,
584 CS_TXN_BILLING_TYPES tbt,
585 MTL_SYSTEM_ITEMS_KFV i,
586 CS_INCIDENTS_ALL_B sr,
587 CS_TRANSACTION_TYPES_B tt,
588 cs_billing_type_categories cbtc
589 WHERE edt.incident_id = p_incident_id AND
590 edt.interface_to_oe_flag = 'Y' AND
591 edt.order_line_id IS NULL AND
592 edt.charge_line_type = 'ACTUAL' AND
593 edt.txn_billing_type_id = tb.txn_billing_type_id(+) AND
594 --edt.org_id = tb.org_id (+) AND
595 nvl(edt.org_id, '-999') = nvl(tb.org_id, '-999') AND
596 edt.inventory_item_id = i.inventory_item_id AND
597 nvl(i.organization_id,-999) = nvl(p_inv_org_id,-999) AND
598 edt.incident_id = sr.incident_id AND
599 edt.txn_billing_type_id = tbt.txn_billing_type_id AND
600 tt.transaction_type_id = tbt.transaction_type_id AND
601 tbt.billing_type = cbtc.billing_type AND
602 edt.line_submitted = 'N' AND
603 ((edt.original_source_code = nvl(p_ctrl_orig_source,ORIGINAL_SOURCE_CODE)
604 and edt.source_code = nvl(p_ctrl_source,SOURCE_CODE)) OR
605 (p_ctrl_submit_source = 'DR'
606 and original_source_code = 'SR'
607 and edt.source_code = 'DR'))
608 order by edt.org_id,edt.estimate_detail_id
609 For Update of edt.estimate_detail_id nowait; -- Bug 7632716 -- Uncommented for bug 13556915
610 -- For Update nowait; -- for cross ou --7117301
611
612 TYPE t_EstDTLTAB IS TABLE OF Fetch_Est_Dtl%rowtype
613 INDEX BY BINARY_INTEGER;
614
615 EstDTLTAB T_EstDTLTAB;
616
617 -- Changes for 11.5.10
618 TYPE t_msgtable IS TABLE OF VARCHAR2(2000)
619 INDEX BY BINARY_INTEGER;
620
621 msg_table t_msgtable;
622 temp_tab VARCHAR2(8000);
623
624 --
625 -- Following Cursor fetches the Order_Number from Charge lines that have
626 -- already been submitted to Order Management .
627
628 CURSOR Fetch_Est_Ord_Dtl(p_currency_code varchar2,p_price_list_header_id number,
629 p_invoice_to_org_id number,p_ship_to_org_id number ,
630 p_purchase_order_num varchar2,p_txn_billing_type_id number,
631 p_org_id number,p_order_type_id number,p_book_order_flag varchar2) IS
632 SELECT nvl(max(edt.order_header_id),-999)
633 FROM CS_ESTIMATE_DETAILS edt,
634 CS_TXN_BILLING_OETXN_ALL tb,
635 OE_ORDER_HEADERS_ALL oe
636 WHERE edt.incident_id = p_incident_id AND
637 edt.currency_code = p_currency_code AND
638 nvl(edt.invoice_to_org_id,-999) = nvl(p_invoice_to_org_id,-999) AND
639 nvl(edt.ship_to_org_id,-999) = nvl(p_ship_to_org_id,-999) AND
640 nvl(edt.org_id,-999) = nvl(p_org_id,-999) AND
641 nvl(edt.purchase_order_num,'-999') = nvl(p_purchase_order_num,'-999') AND
642 edt.order_header_id is not null AND
643 edt.order_line_id is not null AND
644 edt.interface_to_oe_flag = 'Y' AND
645 edt.txn_billing_type_id = tb.txn_billing_type_id AND
646 nvl(edt.org_id,-999) = nvl(tb.org_id,-999) AND
647 tb.order_type_id = p_order_type_id AND
648 edt.order_header_id = oe.header_id AND
649 oe.open_flag = 'Y' AND
650 oe.booked_flag = decode(p_book_order_flag,'N','N','Y');
651
652
653 -- NOT Needed since org_id is now at the line level.
654 -- Get the Incident_Org_id
655 -- Cursor Get_Org_Id is
656 -- SELECT org_id
657 -- FROM cs_incidents_all_b
658 -- WHERE incident_id = p_incident_id;
659
660 -- Get the PO from Order_Header
661 Cursor Cust_Po(p_order_header_id number) is
662 SELECT nvl(cust_po_number,'-999')
663 FROM oe_order_headers_all
664 WHERE header_id = p_order_header_id;
665
666 -- Get the Modifier_header_id
667 CURSOR Get_Modifier_Header(p_list_line_id number) is
668 SELECT list_header_id
669 FROM qp_list_lines
670 WHERE list_line_id = p_list_line_id;
671
672 --
673 --BUG 4287842
674
675 CURSOR get_inv_item_id(p_instance_id number) IS
676 select inventory_item_id from csi_item_instances
677 where instance_id = p_instance_id;
678 --
679 CURSOR acct_from_party(p_party_id number) IS
680 SELECT count(cust_account_id)
681 FROM HZ_CUST_ACCOUNTS_ALL
682 WHERE party_id = p_party_id
683 AND NVL(status, 'A') = 'A';
684 --
685 --
686 l_inventory_item_id NUMBER;
687 l_order_type_id NUMBER := 0;
688 l_order_header_id NUMBER;
689 l_line_type_id NUMBER := 0;
690 l_org_id NUMBER;
691 l_purchase_order_num VARCHAR2(50); --added by cnemalik
692
693 l_OM_ERROR EXCEPTION;
694 l_IB_ERROR EXCEPTION;
695
696 l_dummy NUMBER;
697
698 -- The following are reqd to call installbase API.
699 -- 11.5.5 Intstalled Base definitions
700 -- l_line_inst_dtl_rec cs_inst_detail_pub.line_inst_dtl_rec_type;
701 -- l_line_inst_dtl_desc_flex CS_INSTALLEDBASE_PUB.DFF_REC_TYPE;
702
703 -- 11.5.6 Intstalled Base definitions
704 csi_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
705 csi_txn_line_rec_null csi_t_datastructures_grp.txn_line_rec;
706 csi_txn_line_detail_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
707 csi_txn_party_detail_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
708 csi_txn_pty_acct_detail_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
709 csi_txn_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
710 csi_txn_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
711 csi_txn_ext_attrib_vals_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
712 csi_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
713 --
714 l_sub_type_id NUMBER;
715 l_source_type_id NUMBER;
716
717 l_src_reference_reqd VARCHAR2(1);
718 l_src_change_owner VARCHAR2(1);
719 l_src_change_owner_to_code VARCHAR2(1);
720 l_src_return_reqd_flag VARCHAR2(1);
721
722 l_non_src_reference_reqd VARCHAR2(1);
723 l_non_src_change_owner VARCHAR2(1);
724 l_non_src_change_owner_to_code VARCHAR2(1);
725
726 l_internal_party_id NUMBER;
727 l_instance_party_id NUMBER;
728 l_update_ib_flag VARCHAR2(1);
729
730 l_ib_serial_number VARCHAR2(30);
731
732 -- Common Intstalled Base definitions
733 xi_return_status VARCHAR2(30);
734 xi_msg_count NUMBER;
735 xi_msg_data VARCHAR2(2000);
736 xi_line_inst_detail_id NUMBER;
737 xi_object_version_number NUMBER;
738 l_transaction_type_id NUMBER;
739 --
740 --
741 -- Variables for creating Line_adjustment records
742 l_modifier_header_id NUMBER := 0;
743 l_modifier_line_id NUMBER := 0;
744 l_operand NUMBER := 0;
745 l_adjusted_amount NUMBER := 0;
746 l_before_warranty_cost NUMBER := 0;
747 l_return_quantity NUMBER; -- Fix bug 2930729
748
749 l_file varchar2(80);
750 l_acct_no NUMBER;
751
752 -- Added for bug:5408354
753 l_arith_operator qp_list_lines.arithmetic_operator%type;
754 l_currency_code qp_list_headers.currency_code%type;
755 L_API_ERROR_WITH_FND_MESSAGE EXCEPTION;
756 --
757 --
758 -- Added this to get account_id after submitting an order to OM.
759 --
760 l_account_id NUMBER;
761
762 -- Changes for 11.5.10
763 --
764 l_book_order_flag VARCHAR2(1);
765 l_book_order_profile VARCHAR2(1);
766 l_order_status_flag VARCHAR2(1);
767 l_ctrl_orig_source VARCHAR2(30);
768 l_ctrl_source VARCHAR2(30);
769 --
770 CURSOR get_order_status(p_order_header_id number) IS
771 SELECT booked_flag
772 FROM oe_order_headers_all
773 WHERE header_id = p_order_header_id;
774
775
776 CURSOR Get_party_number is
777 SELECT party_number
778 FROM hz_parties
779 WHERE party_id = p_party_id;
780
781 l_party_number hz_parties.party_number%type;
782 --
783 --
784 -- For Events
785
786 wf_resp_appl_id number;
787 wf_resp_id number;
788 wf_user_id number;
789 --
790 orig_org_id number;
791 orig_user_id number;
792 orig_resp_id number;
793 orig_resp_appl_id number;
794 new_org_id number;
795 new_user_id number;
796 new_resp_id number;
797 new_resp_appl_id number;
798
799
800 --bug 13826788
801
802 lv_incident_id NUMBER;
803 l_org_contact_id NUMBER;
804
805 cursor c_org_contact is
806 select c.org_contact_id
807 from cs_hz_sr_contact_points a
808 ,hz_relationships b
809 ,hz_org_contacts c
810 where a.incident_id = lv_incident_id
811 and a.party_id = b.party_id
812 and b.relationship_id = c.party_relationship_id
813 and a.primary_flag='Y'
814 and a.contact_type in ('PERSON','ORGANIZATION','PARTY_RELATIONSHIP') ;--bug 8473497
815
816
817 -- Bug 9312433
818 Cursor c_sub_type(p_sub_type_id number) is
819 SELECT SRC_REFERENCE_REQD
820 FROM CSI_IB_TXN_TYPES
821 WHERE sub_type_id = p_sub_type_id;
822
823 l_src_ref_reqd varchar2(3);
824
825 -- Bug 8203856
826 Cursor get_parent_instance(p_child_instance_id Number) is
827 SELECT object_id
828 FROM csi_ii_relationships
829 WHERE subject_id = p_child_instance_id
830 AND nvl(active_start_date, sysdate-1) < sysdate --Bug 9727572
831 AND nvl(active_end_date , sysdate+1) > sysdate; --Bug 9727572
832
833
834 l_parent_instance_id Number;
835 -- End Bug 8203856
836
837 BEGIN
838
839 -- dbms_application_info.set_client_info('204');
840
841
842 -- Standard Start of API Savepoint
843 SAVEPOINT CS_Charge_Create_Order_PVT;
844
845 -- Standard Call to check API compatibility
846 IF NOT FND_API.Compatible_API_Call( l_api_version,
847 p_api_version,
848 l_api_name,
849 G_PKG_NAME) THEN
850 RAISE FND_API.G_EXC_ERROR ;
851 END IF;
852
853 -- Initialize message list if p_init_msg_list is set to TRUE.
854 IF FND_API.to_Boolean( p_init_msg_list ) THEN
855 FND_MSG_PUB.initialize;
856 END IF;
857
858 -- Initialize API return status to success
859 x_return_status := FND_API.G_RET_STS_SUCCESS;
860 ----------------------- FND Logging -----------------------------------
861 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
862 THEN
863 FND_LOG.String
864 ( FND_LOG.level_procedure , L_LOG_MODULE || 'start'
865 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
866 );
867 FND_LOG.String
868 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
869 , 'p_api_version:' || p_api_version
870 );
871 FND_LOG.String
872 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
873 , 'p_init_msg_list:' || p_init_msg_list
874 );
875 FND_LOG.String
876 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
877 , 'p_commit:' || p_commit
878 );
879 FND_LOG.String
880 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
881 , 'p_validation_level:' || p_validation_level
882 );
883 FND_LOG.String
884 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
885 , 'p_incident_id:' || p_incident_id
886 );
887 FND_LOG.String
888 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
889 , 'p_party_id:' || p_party_id
890 );
891 FND_LOG.String
892 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
893 , 'p_account_id:' || p_account_id
894 );
895 FND_LOG.String
896 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
897 , 'p_book_order_flag:' || p_book_order_flag
898 );
899 FND_LOG.String
900 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
901 , 'p_submit_source:' || p_submit_source
902 );
903 FND_LOG.String
904 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
905 , 'p_submit_from_system:' || p_submit_from_system
906 );
907
908 END IF;
909 --
910 -- API body
911 --
912 -- Local Procedure
913
914 -- Validate parameters
915 IF (p_incident_id is null) THEN
916 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
917 THEN
918 FND_LOG.String
919 ( FND_LOG.level_statement
920 , L_LOG_MODULE || 'get_request_info_end'
921 , 'invalid input parameter :' || 'p_incident_id'
922 );
923 END IF;
924 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
925 FND_MESSAGE.Set_Token('PARAM','p_incident_id');
926 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
927 FND_MSG_PUB.Add;
928 RAISE FND_API.G_EXC_ERROR;
929 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
930 END IF;
931 IF (p_party_id is null) THEN
932 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
933 THEN
934 FND_LOG.String
935 ( FND_LOG.level_statement, L_LOG_MODULE || 'invalid input parameter :'
936 , 'p_party_id'
937 );
938 END IF;
939 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
940 FND_MESSAGE.Set_Token('PARAM','p_party_id');
941 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
942 FND_MSG_PUB.Add;
943 RAISE FND_API.G_EXC_ERROR;
944 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
945 END IF;
946
947 -- Added validations for 11.5.10 parameters. There is no validation required
948 -- for submit_from_system parameter.
949 --
950 IF (p_book_order_flag NOT IN ('Y','N',FND_API.G_MISS_CHAR)) THEN
951 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
952 THEN
953 FND_LOG.String
954 ( FND_LOG.level_statement, L_LOG_MODULE || 'invalid input parameter :'
955 , 'p_book_order_flag'
956 );
957 END IF;
958 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
959 FND_MESSAGE.Set_Token('PARAM','p_book_order_flag');
960 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
961 FND_MSG_PUB.Add;
962 RAISE FND_API.G_EXC_ERROR;
963 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
964 END IF;
965
966 IF (p_submit_source NOT IN ('SR','DR','FS',FND_API.G_MISS_CHAR)) THEN
967 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
968 THEN
969 FND_LOG.String
970 ( FND_LOG.level_statement, L_LOG_MODULE || 'invalid input parameter :'
971 , 'p_submit_source'
972 );
973 END IF;
974 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
975 FND_MESSAGE.Set_Token('PARAM','p_submit_source');
976 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
977 FND_MSG_PUB.Add;
978 RAISE FND_API.G_EXC_ERROR;
979 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
980 END IF;
981 --- End of 11.5.10 parameter validations
982 ---
983 get_who_info(l_login_id,l_user_id);
984
985 -- Verify the account on the SR party
986 OPEN acct_from_party(p_party_id);
987 FETCH acct_from_party
988 INTO l_acct_no;
989 CLOSE acct_from_party;
990
991 IF (l_acct_no > 0) and (p_account_id is null) THEN
992 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
993 THEN
994 FND_LOG.String
995 ( FND_LOG.level_statement, L_LOG_MODULE , 'Missing account id'
996 );
997 END IF;
998 FND_MESSAGE.Set_Name('CS','CS_CHG_NO_ACCT_NUM_IN_SR');
999 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
1000 FND_MSG_PUB.Add;
1001 RAISE FND_API.G_EXC_ERROR;
1002 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1003
1004 ELSIF l_acct_no = 0 then
1005 If fnd_profile.value('CS_SR_ACTION_MISS_ACCT') = 'CHG_ABORT_SUB' Then
1006 Open Get_party_number;
1007 Fetch Get_party_number into l_party_number;
1008 Close Get_party_number;
1009
1010 FND_MESSAGE.Set_Name('CS','CS_SR_NO_VALID_ACCT');
1011 FND_MESSAGE.Set_Token('SOLD_TO_PARTY',l_party_number);
1012 FND_MSG_PUB.Add;
1013 RAISE FND_API.G_EXC_ERROR;
1014 End if;
1015 END IF;
1016
1017 -- Get modifier for OM adjustment lines
1018 BEGIN
1019 l_modifier_line_id := fnd_profile.value_specific('CS_CHARGE_DEFAULT_MODIFIER');
1020 -- dbms_output.put_line('Default_Modifier' || l_modifier_line_id);
1021 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1022 THEN
1023 FND_LOG.String
1024 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1025 , 'The Value of profile CS_CHARGE_DEFAULT_MODIFIER :' || l_modifier_line_id
1026 );
1027 END IF;
1028
1029 IF (l_modifier_line_id IS NOT NULL) THEN
1030 OPEN Get_Modifier_Header(l_modifier_line_id);
1031 FETCH Get_Modifier_Header
1032 INTO l_modifier_header_id;
1033 CLOSE Get_Modifier_Header;
1034 END IF;
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_DEFAULT_MODIFIER');
1038 FND_MSG_PUB.Add;
1039 RAISE FND_API.G_EXC_ERROR;
1040 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1041 END;
1042
1043 --
1044 -- Check the value of book_order_flag.Changes for 11.5.10
1045 --
1046 BEGIN
1047
1048 l_book_order_profile := fnd_profile.value('CS_CHG_CREATE_BOOKED_ORDERS');
1049 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1050 THEN
1051 FND_LOG.String
1052 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1053 , 'The Value of profile CS_CHG_CREATE_BOOKED_ORDERS :' || l_book_order_profile
1054 );
1055 END IF;
1056 -- Set the value of the profile as 'N', if the profile has no value set.
1057 IF l_book_order_profile IS NULL THEN
1058 l_book_order_profile := 'N';
1059 END IF;
1060
1061 IF p_book_order_flag IS NOT NULL OR
1062 p_book_order_flag = FND_API.G_MISS_CHAR THEN
1063 l_book_order_flag := p_book_order_flag;
1064
1065 ELSIF p_book_order_flag IS NULL THEN
1066 l_book_order_flag := l_book_order_profile;
1067 END IF;
1068
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
1072 FND_MESSAGE.Set_Token('PARAM','p_book_order_flag');
1073 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
1074 FND_MSG_PUB.Add;
1075 RAISE FND_API.G_EXC_ERROR;
1076 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1077
1078 END;
1079 -- End of Book Order Flag
1080 --
1081 -- Changes for 11.5.10. Validate submit source.
1082 --
1083 IF p_submit_source = 'SR' THEN
1084 l_ctrl_orig_source := 'SR';
1085 l_ctrl_source := 'SR';
1086 ELSIF p_submit_source = 'DR' THEN
1087 l_ctrl_orig_source := 'DR';
1088 l_ctrl_source := NULL;
1089 ELSIF p_submit_source = 'FS' THEN
1090 l_ctrl_orig_source := 'SR';
1091 l_ctrl_source := 'SD';
1092 ELSIF p_submit_source = FND_API.G_MISS_CHAR THEN
1093 l_ctrl_orig_source := NULL;
1094 l_ctrl_source := NULL;
1095 END IF;
1096
1097 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1098 THEN
1099 FND_LOG.String
1100 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1101 , 'The defaulted value of parameter l_ctrl_orig_source:' || l_ctrl_orig_source
1102 );
1103 FND_LOG.String
1104 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1105 , 'The defaulted value of parameter l_ctrl_source:' || l_ctrl_source
1106 );
1107 END IF;
1108
1109 --
1110 -- End of Validate Submit Source for 11.5.10.
1111 --
1112 -- NOT NEEDED since org_id is now at the line level.
1113 -- Get the Incident_Org_id
1114 -- OPEN Get_Org_Id;
1115 -- FETCH Get_Org_Id
1116 -- INTO l_org_id;
1117 -- CLOSE Get_Org_Id;
1118
1119 -- Get Inventory_Org_Id
1120 --
1121 l_inv_org_id := cs_std.Get_Item_Valdn_Orgzn_ID;
1122
1123 -- dbms_output.put_line('Inventory_Org ' || l_inv_org_id);
1124
1125 OPEN Fetch_Est_Dtl(p_incident_id,l_inv_org_id,p_submit_source,
1126 l_ctrl_orig_source,l_ctrl_source);
1127
1128 LOOP
1129 i := i+1;
1130 FETCH Fetch_Est_Dtl
1131 INTO EstDtlTab(i);
1132 EXIT WHEN Fetch_Est_Dtl%NOTFOUND;
1133
1134 -- dbms_output.put_line('Estimate_Detail_Id ' || EstDtlTab(i).estimate_detail_id);
1135
1136 l_record_found := 'Y';
1137 --
1138 --
1139 -- l_control_rec.calculate_price := FND_API.G_FALSE;
1140 --l_control_rec.calculate_price := EstDtlTab(i).calculate_price_flag; -- shachoud project
1141
1142 -- Bug 12907768
1143 -- ADDED IF CONDITION FOR NO_CHARGE_FLAG : If the Calculate Price flag is Yes and No Charge is also checked then Calculate price has
1144 -- to be passed as FREEZE.
1145
1146
1147 IF EstDtlTab(i).calculate_price_flag = 'Y' THEN
1148 if EstDtlTab(i).NO_CHARGE_FLAG = 'N' THEN
1149 l_control_rec.calculate_price := FND_API.G_TRUE;
1150 else
1151 l_control_rec.calculate_price := FND_API.G_FALSE;
1152 end if;
1153 ELSE
1154 l_control_rec.calculate_price := FND_API.G_FALSE;
1155 END IF;
1156 -- End of bug 12907768
1157
1158 l_org_id := EstDtlTab(i).org_id;
1159
1160 OPEN Fetch_Est_Ord_Dtl(EstDtlTab(i).currency_code,EstDtlTab(i).price_list_header_id,
1161 EstDtlTab(i).invoice_to_org_id,EstDtlTab(i).ship_to_org_id,
1162 EstDtlTab(i).purchase_order_num ,EstDtlTab(i).txn_billing_type_id,
1163 l_org_id, EstDtlTab(i).Order_Type_Id,l_book_order_flag);
1164 FETCH Fetch_Est_Ord_Dtl
1165 INTO l_order_header_id;
1166 CLOSE Fetch_Est_Ord_Dtl;
1167
1168 -- dbms_output.put_line('In the begin');
1169 --
1170 -- Added for 11.5.10
1171 -- Checking the status of the order for add_to_order or
1172 -- when adding a line to an existing order.
1173
1174 -- dbms_output.put_line('Add_to_Order_Flag' || EstDtlTab(i).add_to_order_flag);
1175 -- dbms_output.put_line('order_header_id' || l_order_header_id);
1176
1177 IF (EstDtlTab(i).add_to_order_flag = 'Y'
1178 OR l_order_header_id <> -999
1179 )
1180 AND EstDtlTab(i).add_to_order_flag <> 'F' --5649493
1181 THEN
1182
1183 IF EstDtlTab(i).order_header_id IS NULL THEN
1184
1185 OPEN get_order_status(l_order_header_id);
1186 FETCH Get_Order_Status
1187 INTO l_order_status_flag;
1188 CLOSE Get_Order_Status;
1189
1190 ELSE
1191
1192 OPEN get_order_status(EstDtlTab(i).order_header_id);
1193 FETCH Get_Order_Status
1194 INTO l_order_status_flag;
1195 CLOSE Get_Order_Status;
1196
1197 END IF;
1198
1199 -- dbms_output.put_line('Order_Status_Flag' || l_order_status_flag);
1200
1201 -- OM order_status is null till the records have been comitted.
1202 IF (l_order_status_flag = 'Y' or
1203 l_order_status_flag IS NULL or
1204 l_order_status_flag = 'N') and
1205 l_book_order_flag = 'N' THEN
1206
1207 l_control_rec.book_flag := FND_API.G_FALSE;
1208
1209 ELSIF (l_order_status_flag = 'Y' and
1210 l_book_order_flag = 'Y') THEN
1211
1212 l_control_rec.book_flag := FND_API.G_FALSE;
1213
1214 ELSIF l_order_status_flag = 'N' and
1215 l_book_order_flag = 'Y' THEN
1216
1217 l_control_rec.book_flag := FND_API.G_TRUE;
1218
1219 END IF;
1220
1221 ELSIF (EstDtlTab(i).add_to_order_flag = 'N') or
1222 (EstDtlTab(i).add_to_order_flag IS NULL) or
1223 -- fix bug:3667208
1224 (EstDtlTab(i).add_to_order_flag = 'F')
1225 --and (l_order_header_id = -999 ) --5649493
1226 THEN
1227
1228 IF l_book_order_flag = 'Y' THEN
1229 l_control_rec.book_flag := FND_API.G_TRUE;
1230 ELSIF l_book_order_flag = 'N' THEN
1231 l_control_rec.book_flag := FND_API.G_FALSE;
1232 END IF;
1233 END IF;
1234
1235 -- dbms_output.put_line('Book_Flag' || l_control_rec.book_flag);
1236 --
1237 -- End of 11.5.10 changes for order status.
1238 --
1239 -- VALIDATE CATEGORY CODE
1240 IF EstDtlTab(i).line_category_code <> 'RETURN'
1241 AND EstDtlTab(i).line_category_code <> 'ORDER' THEN
1242 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_CAT_CODE');
1243 FND_MSG_PUB.Add;
1244 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1245 p_count => x_msg_count,
1246 p_data => x_msg_data);
1247
1248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1249
1250 END IF;
1251
1252 IF EstDtlTab(i).quantity_required is null
1253 OR EstDtlTab(i).quantity_required = 0 THEN
1254 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_QTY');
1255 FND_MSG_PUB.Add;
1256 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1257 p_count => x_msg_count,
1258 p_data => x_msg_data);
1259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1260 END IF;
1261
1262 -- GET ORDER_TYPE AND LINE_TYPE
1263 BEGIN
1264 l_order_type_id := EstDtlTab(i).order_type_id;
1265 l_line_type_id := EstDtlTab(i).line_type_id;
1266
1267 IF l_order_type_id = 0
1268 or l_order_type_id is null
1269 or l_line_type_id = 0
1270 or l_line_type_id is null then
1271 RAISE L_OM_ERROR;
1272
1273 END IF;
1274
1275 EXCEPTION
1276 WHEN L_OM_ERROR THEN
1277 FND_MESSAGE.Set_Name('CS','CS_CHG_DEFINE_OMTYPES');
1278 FND_MSG_PUB.Add;
1279 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1280 p_count => x_msg_count,
1281 p_data => x_msg_data);
1282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1283 END;
1284 --
1285 --
1286 -- dbms_output.put_line('After Order Type and Line Type');
1287 --
1288 -- Clear ASO datastructures
1289 l_line_tbl.delete;
1290 l_line_dtl_tbl.delete;
1291 l_ln_shipment_tbl.delete;
1292 l_hd_payment_tbl.delete;
1293 l_ln_payment_tbl.delete; /* Credit Card 9358401 */
1294 l_line_price_adj_tbl.delete;
1295 l_header_rec := l_header_rec_default;
1296
1297 l_header_rec.invoice_to_party_id := NULL;
1298 l_header_rec.invoice_to_party_site_id := NULL;
1299 l_header_rec.invoice_to_cust_account_id := NULL;
1300
1301 l_lot_serial_tbl := ASO_ORDER_INT.G_MISS_Lot_Serial_Tbl;
1302 --
1303 -- Order_type_id moved to create order section. fix bug:3557645
1304 -- l_header_rec.order_type_id := l_order_type_id;
1305
1306 --
1307 -- Populate Flexfiled with G_MISS_CHAR so defaulting rules will work.
1308 -- Passing flexfield values as g_miss_char both for new orders/add to orders.
1309 l_header_rec.attribute1 := FND_API.G_MISS_CHAR;
1310 l_header_rec.attribute2 := FND_API.G_MISS_CHAR;
1311 l_header_rec.attribute3 := FND_API.G_MISS_CHAR;
1312 l_header_rec.attribute4 := FND_API.G_MISS_CHAR;
1313 l_header_rec.attribute5 := FND_API.G_MISS_CHAR;
1314 l_header_rec.attribute6 := FND_API.G_MISS_CHAR;
1315 l_header_rec.attribute7 := FND_API.G_MISS_CHAR;
1316 l_header_rec.attribute8 := FND_API.G_MISS_CHAR;
1317 l_header_rec.attribute9 := FND_API.G_MISS_CHAR;
1318 l_header_rec.attribute10 := FND_API.G_MISS_CHAR;
1319 l_header_rec.attribute11 := FND_API.G_MISS_CHAR;
1320 l_header_rec.attribute12 := FND_API.G_MISS_CHAR;
1321 l_header_rec.attribute13 := FND_API.G_MISS_CHAR;
1322 l_header_rec.attribute14 := FND_API.G_MISS_CHAR;
1323 l_header_rec.attribute15 := FND_API.G_MISS_CHAR;
1324
1325 -- FIX Bug:3667208
1326 IF (EstDtlTab(i).add_to_order_flag = 'F') THEN
1327
1328 l_header_rec.order_type_id := l_order_type_id;
1329 l_header_rec.org_id := EstDtlTab(i).org_id;
1330 l_header_rec.quote_source_code := 'Service Billing'; -- Lookup value 7
1331 l_header_rec.party_id := p_party_id ;
1332
1333 IF EstDtlTab(i).invoice_to_account_id IS NULL THEN
1334 Get_acct_from_party_site (
1335 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
1336 p_sold_to_customer_party => p_party_id,
1337 p_sold_to_customer_account => p_account_id,
1338 p_org_id => EstDtlTab(i).org_id,--Bug# 4870037
1339 p_site_use_flag => 'B',--Bug# 4870037
1340 p_site_use_code => 'BILL_TO',
1341 x_account_id => l_invoice_to_cust_account_id);
1342 ELSE
1343 --l_header_rec.invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1344 l_invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1345 END IF;
1346
1347 l_header_rec.invoice_to_party_id := EstDtlTab(i).bill_to_contact_id; --Bugfix :7164996
1348 l_header_rec.invoice_to_party_site_id := EstDtlTab(i).invoice_to_org_id;
1349 l_header_rec.cust_account_id := p_account_id;
1350 l_header_rec.invoice_to_cust_account_id := l_invoice_to_cust_account_id;
1351
1352 IF EstDtlTab(i).ship_to_account_id IS NULL THEN
1353 Get_acct_from_party_site (
1354 p_party_site_id => EstDtlTab(i).ship_to_org_id,
1355 p_sold_to_customer_party => p_party_id,
1356 p_sold_to_customer_account => p_account_id,
1357 p_org_id => EstDtlTab(i).org_id,
1358 p_site_use_flag => 'S',
1359 p_site_use_code => 'SHIP_TO',
1360 x_account_id => l_ship_to_cust_account_id);
1361 ELSE
1362 --l_hd_shipment_tbl(j).ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1363 l_ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1364 END IF;
1365
1366 l_hd_shipment_tbl(j).ship_to_party_id := EstDtlTab(i).ship_to_contact_id; --Bugfix :7164996
1367 l_hd_shipment_tbl(j).ship_to_party_site_id := EstDtlTab(i).ship_to_org_id;
1368 l_hd_shipment_tbl(j).ship_from_org_id := EstDtlTab(i).transaction_inventory_org;
1369 l_hd_shipment_tbl(j).ship_to_cust_account_id := l_ship_to_cust_account_id;
1370 --12.1.3 Charges Logistics Project
1371 l_hd_shipment_tbl(j).ship_method_code := EstDtlTab(i).shipping_method;
1372 l_hd_shipment_tbl(j).promise_date := EstDtlTab(i).arrival_date_time;
1373
1374 l_header_rec.quote_header_id := P_INCIDENT_ID;
1375
1376 BEGIN
1377 -- Fix for the bug:3509921
1378 l_incident_number := to_number(EstDtlTab(i).incident_number);
1379
1380 l_header_rec.original_system_reference := EstDtlTab(i).incident_number;
1381 l_header_rec.quote_number := EstDtlTab(i).incident_number;
1382
1383 -- dbms_output.put_line('Passing number incident_number');
1384
1385 EXCEPTION
1386 WHEN OTHERS THEN
1387 l_header_rec.original_system_reference := EstDtlTab(i).incident_number; --Bug 7170849
1388
1389 -- dbms_output.put_line('Passing character incident_number');
1390
1391 END;
1392
1393 --
1394 l_header_rec.order_type_id := l_order_type_id;
1395
1396 IF EstDtlTab(i).conversion_type_code = 'User' THEN
1397 l_header_rec.exchange_rate := EstDtlTab(i).conversion_rate;
1398 l_header_rec.exchange_type_code := EstDtlTab(i).conversion_type_code;
1399 l_header_rec.exchange_rate_date := EstDtlTab(i).conversion_rate_date;
1400 ELSIF EstDtlTab(i).conversion_type_code = 'Corporate' THEN
1401 l_header_rec.exchange_type_code := EstDtlTab(i).conversion_type_code;
1402 l_header_rec.exchange_rate_date := EstDtlTab(i).conversion_rate_date;
1403 END IF;
1404
1405 l_header_rec.price_list_id := EstDtlTab(i).price_list_header_id;
1406 l_header_rec.currency_code := EstDtlTab(i).currency_code ;
1407
1408 --
1409 IF EstDtlTab(i).purchase_order_num <> '-999' THEN
1410 l_hd_payment_tbl(j).payment_type_code := NULL;
1411 -- Fix bug:5210040
1412 -- l_hd_payment_tbl(j).payment_ref_number := EstDtlTab(i).purchase_order_num;
1413 l_hd_payment_tbl(j).cust_po_number := EstDtlTab(i).purchase_order_num;
1414 END IF;
1415 --
1416 END IF; -- END OF CREATING A NEW ORDER FOR A CHARGE LINE.NEW ORDER IS CREATED IF
1417 -- ADD_TO_ORDER_FLAG = 'F'
1418 --
1419 --
1420 /* Added for bug `14673342*/
1421
1422 l_found_flag :=0;
1423 IF ((nvl(EstDtlTab(i).add_to_order_flag,'N') = 'N') AND
1424 (l_order_header_id <> -999 )AND
1425 ( nvl(fnd_profile.value('CS_UPD_CC_OPEN_ORDER'),'UPDATE_ORDER')
1426 = 'CREATE_ORDER' and
1427 nvl(fnd_profile.value('CS_CC_TO_OM_HEADER'),'N') = 'Y')) THEN
1428
1429 for l_tempCount in 1..l_order_num_count loop
1430 IF l_order_number(l_tempCount)= l_order_header_id then
1431 l_found_flag := 1;
1432 exit;
1433 END IF;
1434 END LOOP;
1435 IF (l_found_flag =0) THEN
1436 l_order_header_id :=-999;
1437 END IF;
1438 END IF;
1439 /* End for bug `14673342*/
1440
1441 /**************** ADDING A LINE TO AN EXISTING ORDER *************/
1442
1443 IF (EstDtlTab(i).add_to_order_flag = 'Y') or (l_order_header_id <> -999) THEN
1444 IF (EstDtlTab(i).add_to_order_flag = 'Y') then
1445 l_header_rec.order_id := EstDtlTab(i).order_header_id;
1446 --
1447 OPEN Cust_Po(EstDtlTab(i).order_header_id);
1448 FETCH Cust_Po
1449 INTO l_purchase_order_num;
1450 CLOSE Cust_Po;
1451 --
1452 ELSIF (l_order_header_id <> -999) then
1453 l_header_rec.order_id := l_order_header_id;
1454 --
1455 OPEN Cust_Po(l_order_header_id);
1456 FETCH Cust_Po
1457 INTO l_purchase_order_num;
1458 CLOSE Cust_Po;
1459 --
1460 END IF;
1461
1462 l_line_tbl(j).operation_code := 'CREATE';
1463 l_ln_shipment_tbl(j).operation_code := 'CREATE';
1464
1465 -- following added by cnemalik
1466 IF (l_purchase_order_num = '-999') AND
1467 (EstDtlTab(i).purchase_order_num <> '-999') THEN
1468 -- Fix bug:51051400
1469 -- l_hd_payment_tbl(j).payment_ref_number := EstDtlTab(i).purchase_order_num;
1470 l_hd_payment_tbl(j).payment_type_code := NULL;
1471 l_hd_payment_tbl(j).cust_po_number := EstDtlTab(i).purchase_order_num;
1472
1473 ELSIF EstDtlTab(i).purchase_order_num <> '-999' AND
1474 l_purchase_order_num <> EstDtlTab(i).purchase_order_num THEN
1475
1476 ROLLBACK TO CS_Charge_Create_Order_PVT;
1477 FND_MESSAGE.Set_Name('CS', 'CS_CHG_INVALID_PO');
1478 FND_MESSAGE.Set_Token('PURCHASE_ORDER_NUM',l_purchase_order_num);
1479 FND_MSG_PUB.Add; -- 5455064
1480 -- APP_EXCEPTION.Raise_Exception;
1481 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1482 p_count => x_msg_count,
1483 p_data => x_msg_data);
1484
1485 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1486
1487 END IF;
1488 -- This value needs to be passed for add_to_order/update_order as well.
1489 -- Fix for the bug 2123535
1490 l_header_rec.quote_source_code := 'Service Billing'; -- Lookup value 7
1491 Begin
1492 -- Fix for the bug 5463554
1493 l_incident_number := to_number(EstDtlTab(i).incident_number); -- Bug 8857796
1494
1495 l_header_rec.original_system_reference := EstDtlTab(i).incident_number;
1496 l_header_rec.quote_number := EstDtlTab(i).incident_number;
1497 /* Bug 8857796 */
1498 Exception
1499 When others then
1500 l_header_rec.original_system_reference := EstDtlTab(i).incident_number;
1501 End;
1502 /* Bug 8857796 */
1503 ELSE /* create a new order */
1504 -- Populate Flexfiled with G_MISS_CHAR so defaulting rules will work
1505 -- Moved to before add_to_order/new_order.
1506 /* l_header_rec.attribute1 := FND_API.G_MISS_CHAR;
1507 l_header_rec.attribute2 := FND_API.G_MISS_CHAR;
1508 l_header_rec.attribute3 := FND_API.G_MISS_CHAR;
1509 l_header_rec.attribute4 := FND_API.G_MISS_CHAR;
1510 l_header_rec.attribute5 := FND_API.G_MISS_CHAR;
1511 l_header_rec.attribute6 := FND_API.G_MISS_CHAR;
1512 l_header_rec.attribute7 := FND_API.G_MISS_CHAR;
1513 l_header_rec.attribute8 := FND_API.G_MISS_CHAR;
1514 l_header_rec.attribute9 := FND_API.G_MISS_CHAR;
1515 l_header_rec.attribute10 := FND_API.G_MISS_CHAR;
1516 l_header_rec.attribute11 := FND_API.G_MISS_CHAR;
1517 l_header_rec.attribute12 := FND_API.G_MISS_CHAR;
1518 l_header_rec.attribute13 := FND_API.G_MISS_CHAR;
1519 l_header_rec.attribute14 := FND_API.G_MISS_CHAR;
1520 l_header_rec.attribute15 := FND_API.G_MISS_CHAR; */
1521 --
1522 l_header_rec.order_type_id := l_order_type_id;
1523 l_header_rec.org_id := EstDtlTab(i).org_id;
1524 l_header_rec.quote_source_code := 'Service Billing'; -- Lookup value 7
1525 l_header_rec.party_id := p_party_id ;
1526
1527 -- dbms_output.put_line('In the begin');
1528 -- IF ACCOUNT_ID IS NULL, THEN OC CREATES AN ACCT FROM INVOICE_TO_PARTY_SITE_ID
1529 /* Get_acct_from_party_site (
1530 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
1531 p_sold_to_customer_party => p_party_id,
1532 p_sold_to_customer_account => p_account_id,
1533 x_account_id => l_header_rec.invoice_to_cust_account_id); */
1534
1535 IF EstDtlTab(i).invoice_to_account_id IS NULL THEN
1536 Get_acct_from_party_site (
1537 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
1538 p_sold_to_customer_party => p_party_id,
1539 p_sold_to_customer_account => p_account_id,
1540 p_org_id => EstDtlTab(i).org_id,
1541 p_site_use_flag => 'B',
1542 p_site_use_code => 'BILL_TO',
1543 x_account_id => l_invoice_to_cust_account_id);
1544 ELSE
1545 --l_header_rec.invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1546 l_invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1547 END IF;
1548
1549 l_header_rec.invoice_to_party_id := EstDtlTab(i).bill_to_contact_id; --Bugfix :7164996
1550 l_header_rec.invoice_to_party_site_id := EstDtlTab(i).invoice_to_org_id;
1551 l_header_rec.cust_account_id := p_account_id;
1552 l_header_rec.invoice_to_cust_account_id := l_invoice_to_cust_account_id;
1553
1554 /*Get_acct_from_party_site (
1555 p_party_site_id => EstDtlTab(i).ship_to_org_id,
1556 p_sold_to_customer_party => p_party_id,
1557 p_sold_to_customer_account => p_account_id,
1558 x_account_id => l_hd_shipment_tbl(j).ship_to_cust_account_id); */
1559
1560 IF EstDtlTab(i).ship_to_account_id IS NULL THEN
1561 Get_acct_from_party_site (
1562 p_party_site_id => EstDtlTab(i).ship_to_org_id,
1563 p_sold_to_customer_party => p_party_id,
1564 p_sold_to_customer_account => p_account_id,
1565 p_org_id => EstDtlTab(i).org_id,
1566 p_site_use_flag => 'S',
1567 p_site_use_code => 'SHIP_TO',
1568 x_account_id => l_ship_to_cust_account_id);
1569 ELSE
1570 --l_hd_shipment_tbl(j).ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1571 l_ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1572 END IF;
1573
1574 l_hd_shipment_tbl(j).ship_to_party_id := EstDtlTab(i).ship_to_contact_id; --Bugfix :7164996
1575 l_hd_shipment_tbl(j).ship_to_party_site_id := EstDtlTab(i).ship_to_org_id;
1576 l_hd_shipment_tbl(j).ship_from_org_id := EstDtlTab(i).transaction_inventory_org;
1577 l_hd_shipment_tbl(j).ship_to_cust_account_id := l_ship_to_cust_account_id;
1578
1579 --12.1.3 Charges Logistics Project
1580 l_hd_shipment_tbl(j).ship_method_code := EstDtlTab(i).shipping_method;
1581 l_hd_shipment_tbl(j).promise_date := EstDtlTab(i).arrival_date_time;
1582
1583 l_header_rec.quote_header_id := P_INCIDENT_ID;
1584
1585 BEGIN
1586 -- Fix for the bug:3509921
1587 l_incident_number := to_number(EstDtlTab(i).incident_number);
1588
1589 l_header_rec.original_system_reference := EstDtlTab(i).incident_number;
1590 l_header_rec.quote_number := EstDtlTab(i).incident_number;
1591
1592 -- dbms_output.put_line('Passing number incident_number');
1593
1594 EXCEPTION
1595 WHEN OTHERS THEN
1596 NULL;
1597 -- dbms_output.put_line('Passing character incident_number');
1598 l_header_rec.original_system_reference := EstDtlTab(i).incident_number;
1599
1600 END;
1601
1602 --
1603 l_header_rec.order_type_id := l_order_type_id;
1604 IF EstDtlTab(i).conversion_type_code = 'User' THEN
1605 l_header_rec.exchange_rate := EstDtlTab(i).conversion_rate;
1606 l_header_rec.exchange_type_code := EstDtlTab(i).conversion_type_code;
1607 l_header_rec.exchange_rate_date := EstDtlTab(i).conversion_rate_date;
1608 ELSIF EstDtlTab(i).conversion_type_code = 'Corporate' THEN
1609 l_header_rec.exchange_type_code := EstDtlTab(i).conversion_type_code;
1610 END IF;
1611
1612 l_header_rec.price_list_id := EstDtlTab(i).price_list_header_id;
1613 l_header_rec.currency_code := EstDtlTab(i).currency_code ;
1614
1615 --
1616 IF EstDtlTab(i).purchase_order_num <> '-999' THEN
1617 l_hd_payment_tbl(j).payment_type_code := NULL;
1618 -- Fix bug:5210040
1619 -- l_hd_payment_tbl(j).payment_ref_number := EstDtlTab(i).purchase_order_num;
1620 l_hd_payment_tbl(j).cust_po_number := EstDtlTab(i).purchase_order_num;
1621 END IF;
1622 --
1623 END IF; -- END OF ADDING AN ORDER LINE TO AN EXISTING ORDER
1624 --
1625 -- Moved to create order section for bug:3557645
1626 -- l_header_rec.price_list_id := EstDtlTab(i).price_list_header_id;
1627 -- l_header_rec.currency_code := EstDtlTab(i).currency_code ;
1628
1629 -- VALIDATION FOR ROLLUP FLAG
1630 --
1631 --
1632 IF EstDtlTab(i).rollup_flag = 'Y' THEN
1633
1634 /* SELECT billing_type
1635 INTO l_billing_flag
1636 FROM cs_txn_billing_types
1637 WHERE txn_billing_type_id = EstDtlTab(i).txn_billing_type_id; */
1638
1639 /*
1640 SELECT cbtc.billing_category
1641 INTO l_billing_flag
1642 FROM cs_txn_billing_types ctbt, cs_billing_type_categories cbtc
1643 WHERE ctbt.txn_billing_type_id = EstDtlTab(i).txn_billing_type_id
1644 AND ctbt.billing_type = cbtc.billing_type;
1645
1646 IF l_billing_flag = 'L' THEN
1647 l_inv_item_id := FND_PROFILE.VALUE_SPECIFIC('CS_REPAIR_DEFAULT_LABOR_ITEM');
1648 l_profile_option := 'CS_REPAIR_DEFAULT_LABOR_ITEM';
1649 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1650 THEN
1651 FND_LOG.String
1652 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1653 , 'The Value of profile CS_REPAIR_DEFAULT_LABOR_ITEM :' || l_inv_item_id
1654 );
1655 END IF;
1656 ELSIF l_billing_flag = 'M' THEN
1657 l_inv_item_id := FND_PROFILE.VALUE_SPECIFIC('CS_REPAIR_DEFAULT_MATERIAL_ITEM');
1658 l_profile_option := 'CS_REPAIR_DEFAULT_MATERIAL_ITEM';
1659 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1660 THEN
1661 FND_LOG.String
1662 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1663 , 'The Value of profile CS_REPAIR_DEFAULT_MATERIAL_ITEM :' || l_inv_item_id
1664 );
1665 END IF;
1666 ELSIF l_billing_flag = 'E' THEN
1667 l_inv_item_id := FND_PROFILE.VALUE_SPECIFIC('CS_REPAIR_DEFAULT_EXPENSE_ITEM');
1668 l_profile_option := 'CS_REPAIR_DEFAULT_EXPENSE_ITEM';
1669 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1670 THEN
1671 FND_LOG.String
1672 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1673 , 'The Value of profile CS_REPAIR_DEFAULT_EXPENSE_ITEM :' || l_inv_item_id
1674 );
1675 END IF;
1676 END IF;
1677
1678 IF l_inv_item_id = NULL THEN */
1679 IF EstDtlTab(i).rollup_item_id IS NULL THEN
1680 --FND_MESSAGE.Set_Name('CS', 'CS_CHG_DEFINE_PROFILE_OPTION');
1681 --FND_MESSAGE.Set_Token('PROFILE_OPTION', l_profile_option);
1682 FND_MESSAGE.Set_Name('CS', 'CS_CHG_BILLING_TYPE_NO_ROLLUP');
1683 FND_MSG_PUB.Add;
1684 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1685 p_count => x_msg_count,
1686 p_data => x_msg_data);
1687 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1688
1689 ELSE
1690 l_inv_item_id := EstDtlTab(i).rollup_item_id;
1691 END IF;
1692
1693 -- Get the Primary_Unit_of_Measure.
1694 SELECT primary_uom_code
1695 INTO l_unit_code
1696 FROM mtl_system_items
1697 WHERE inventory_item_id = l_inv_item_id AND
1698 organization_id = CS_STD.Get_Item_Valdn_Orgzn_ID;
1699
1700 l_line_tbl(j).inventory_item_id := l_inv_item_id ;
1701 l_line_tbl(j).UOM_code := l_unit_code ;
1702 --Bug 8474403
1703 l_line_tbl(j).item_revision := null;
1704
1705 ELSE -- If rollup_flag is not 'Y'
1706 l_line_tbl(j).inventory_item_id := EstDtlTab(i).inventory_item_id;
1707 l_line_tbl(j).UOM_code := EstDtlTab(i).unit_of_measure_code ;
1708 --Bug 8474403
1709 l_line_tbl(j).item_revision := EstDtlTab(i).item_revision;
1710
1711 END IF ; -- ROLLUP FLAG
1712
1713 /* Get_acct_from_party_site (
1714 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
1715 p_sold_to_customer_party => p_party_id,
1716 p_sold_to_customer_account => p_account_id,
1717 x_account_id => l_header_rec.invoice_to_cust_account_id); */
1718
1719 IF EstDtlTab(i).invoice_to_account_id IS NULL THEN
1720 Get_acct_from_party_site (
1721 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
1722 p_sold_to_customer_party => p_party_id,
1723 p_sold_to_customer_account => p_account_id,
1724 p_org_id => EstDtlTab(i).org_id,
1725 p_site_use_flag => 'B',
1726 p_site_use_code => 'BILL_TO',
1727 x_account_id => l_invoice_to_cust_account_id);
1728 ELSE
1729 --l_header_rec.invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1730 l_invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1731 END IF;
1732
1733 l_header_rec.invoice_to_cust_account_id := l_invoice_to_cust_account_id;
1734 l_line_tbl(j).item_type_code := EstDtlTab(i).item_type_code; -- 6523849
1735 l_line_tbl(j).invoice_to_party_id := EstDtlTab(i).bill_to_contact_id; --Bugfix :7164996
1736 l_line_tbl(j).invoice_to_party_site_id := EstDtlTab(i).invoice_to_org_id;
1737 l_line_tbl(j).invoice_to_cust_account_id := l_header_rec.invoice_to_cust_account_id;
1738 l_line_tbl(j).order_line_type_id := l_line_type_id;
1739
1740 -- Bug
1741 l_line_tbl(j).subinventory := EstDtlTab(i).transaction_sub_inventory;
1742
1743 IF EstDtlTab(i).line_category_code = 'RETURN' THEN
1744 l_ordered_quantity := (EstDtlTab(i).quantity_required * -1);
1745 l_line_tbl(j).quantity := l_ordered_quantity;
1746 ELSE
1747 l_line_tbl(j).quantity := EstDtlTab(i).quantity_required ;
1748 END IF;
1749
1750 l_line_tbl(j).quote_line_id := EstDtlTab(i).estimate_detail_id ;
1751 l_line_tbl(j).price_list_id := EstDtlTab(i).price_list_header_id ;
1752
1753 l_line_tbl(j).project_id := EstDtlTab(i).project_id; --12.2.2 Service Projects Integration
1754 l_line_tbl(j).task_id := EstDtlTab(i).project_task_id;--12.2.2 Service Projects Integration
1755
1756 l_unit_selling_price := (nvl(EstDtlTab(i).after_warranty_cost,0)/EstDtlTab(i).quantity_required);
1757 l_line_tbl(j).line_list_price := nvl(EstDtlTab(i).list_price,0) ; -- 4870210
1758 -- l_line_tbl(j).line_list_price := nvl(EstDtlTab(i).selling_price,0) ;
1759 l_line_tbl(j).line_quote_price := l_unit_selling_price;
1760 l_line_tbl(j).line_category_code := EstDtlTab(i).line_category_code;
1761
1762 /*Get_acct_from_party_site (
1763 p_party_site_id => EstDtlTab(i).ship_to_org_id,
1764 p_sold_to_customer_party => p_party_id,
1765 p_sold_to_customer_account => p_account_id,
1766 x_account_id => l_ln_shipment_tbl(j).ship_to_cust_account_id); */
1767
1768 IF EstDtlTab(i).ship_to_account_id IS NULL THEN
1769 Get_acct_from_party_site (
1770 p_party_site_id => EstDtlTab(i).ship_to_org_id,
1771 p_sold_to_customer_party => p_party_id,
1772 p_sold_to_customer_account => p_account_id,
1773 p_org_id => EstDtlTab(i).org_id,
1774 p_site_use_flag => 'S',
1775 p_site_use_code => 'SHIP_TO',
1776 x_account_id => l_ship_to_cust_account_id);
1777 ELSE
1778 --l_hd_shipment_tbl(j).ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1779 l_ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1780 END IF;
1781
1782 l_ln_shipment_tbl(j).ship_to_party_id := EstDtlTab(i).ship_to_contact_id; --Bugfix :7164996
1783 l_ln_shipment_tbl(j).ship_to_party_site_id := EstDtlTab(i).ship_to_org_id;
1784 l_ln_shipment_tbl(j).quote_line_id := EstDtlTab(i).estimate_detail_id;
1785 l_ln_shipment_tbl(j).quote_header_id := EstDtlTab(i).incident_id;
1786 l_ln_shipment_tbl(j).ship_from_org_id := EstDtlTab(i).transaction_inventory_org;
1787 l_ln_shipment_tbl(j).ship_to_cust_account_id := l_ship_to_cust_account_id;
1788
1789 --12.1.3 Charges Logistics Project
1790 l_ln_shipment_tbl(j).ship_method_code := EstDtlTab(i).shipping_method;
1791 l_ln_shipment_tbl(j).promise_date := EstDtlTab(i).arrival_date_time;
1792
1793 IF EstDtlTab(i).line_category_code = 'RETURN' then
1794 l_ship_ordered_quantity := (EstDtlTab(i).quantity_required * (-1));
1795 l_ln_shipment_tbl(j).quantity := l_ship_ordered_quantity;
1796 ELSE
1797 l_ln_shipment_tbl(j).quantity := EstDtlTab(i).quantity_required;
1798 END IF;
1799
1800 l_ln_shipment_tbl(j).qte_line_index := j;
1801
1802 IF EstDtlTab(i).line_category_code = 'RETURN' THEN
1803 -- return reason code needs to be passed in line_dtl_tbl
1804 l_line_dtl_tbl(j).return_reason_code := EstDtlTab(i).return_reason_code;
1805 -- l_line_dtl_tbl(j).qte_line_index := j; -- 6523849
1806 END IF; -- for return
1807
1808 l_line_dtl_tbl(j).qte_line_index := j; -- 6523849
1809 l_line_dtl_tbl(j).quote_line_id := EstDtlTab(i).estimate_detail_id; -- 6523849
1810
1811 --
1812 --
1813 -- Passing Values for Creating Line Adjustment record.
1814 --
1815 --
1816 -- << Start >> Changed the logic of modifier based Calculation based on bug 5408354
1817 IF l_modifier_line_id IS NOT NULL
1818 AND (nvl(EstDtlTab(i).selling_price, 0) * EstDtlTab(i).quantity_required ) <> EstDtlTab(i).after_warranty_cost
1819 THEN
1820
1821 Begin
1822
1823 SELECT l.arithmetic_operator,h.currency_code
1824 INTO l_arith_operator, l_currency_code
1825 FROM qp_list_headers h,
1826 qp_list_lines l
1827 WHERE h.list_header_id = l.list_header_id
1828 AND l.list_line_id = l_modifier_line_id;
1829
1830 If l_arith_operator Not In ('%','AMT') Then
1831 --Only amount-based and percent-based modifiers are supported as default modifier in Charges.
1832 FND_MESSAGE.Set_Name('CS','CS_CHG_AMT_PER_MODIF_SUPP');
1833 FND_MSG_PUB.Add;
1834 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1835 p_count => x_msg_count,
1836 p_data => x_msg_data);
1837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1838 End If;
1839
1840 End;
1841
1842 If NVL(l_currency_code,'@~') <> EstDtlTab(i).currency_code
1843 And l_arith_operator = 'AMT'
1844 Then
1845 --Default modifier currency must be the same as charge line currency.
1846 FND_MESSAGE.Set_Name('CS','CS_CHG_SAME_CURR_REQD');
1847 FND_MSG_PUB.Add;
1848 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1849 p_count => x_msg_count,
1850 p_data => x_msg_data);
1851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1852
1853 End If;
1854
1855 l_before_warranty_cost := (nvl(EstDtlTab(i).selling_price, 0) * EstDtlTab(i).quantity_required );
1856 -- Added according to PMs proposed bug fix
1857 IF (EstDtlTab(i).list_price = 0 AND
1858 l_before_warranty_cost <> 0 AND l_arith_operator <> 'AMT') THEN
1859 FND_MESSAGE.Set_Name('CS','CS_CHG_AMT_MODIF_0_PRICE_ITEM');
1860 FND_MSG_PUB.Add;
1861 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1862 p_count => x_msg_count,
1863 p_data => x_msg_data);
1864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1865 END IF;
1866
1867 IF l_before_warranty_cost = 0
1868 Then
1869 If l_arith_operator = 'AMT' Then
1870 l_operand := -1 * nvl (EstDtlTab(i).after_warranty_cost, 0);
1871 Else
1872 --Default modifier must be amount-based in order to support overrides of zero priced items.
1873 FND_MESSAGE.Set_Name('CS','CS_CHG_AMT_MODIF_0_PRICE_ITEM');
1874 FND_MSG_PUB.Add;
1875 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1876 p_count => x_msg_count,
1877 p_data => x_msg_data);
1878 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1879 End If;
1880 Elsif l_before_warranty_cost > 0
1881 Then
1882 If l_arith_operator = 'AMT' Then
1883 l_operand := nvl(EstDtlTab(i).selling_price, 0) - nvl(EstDtlTab(i).after_warranty_cost,0)/EstDtlTab(i).quantity_required;
1884 ElsIf l_arith_operator = '%' Then
1885 l_operand := (l_before_warranty_cost - (nvl(EstDtlTab(i).after_warranty_cost, 0))) /l_before_warranty_cost * 100;
1886 End If;
1887 --Added this code for bug 14709062
1888 Elsif l_before_warranty_cost < 0 Then
1889 If l_arith_operator = 'AMT' Then
1890 l_operand := nvl(EstDtlTab(i).selling_price, 0) - nvl(EstDtlTab(i).after_warranty_cost,0)/EstDtlTab(i).quantity_required;
1891 ElsIf l_arith_operator = '%' Then
1892 l_operand := (l_before_warranty_cost - (nvl(EstDtlTab(i).after_warranty_cost, 0))) /l_before_warranty_cost * 100;
1893 End If;
1894 End IF;
1895
1896 IF EstDtlTab(i).line_category_code = 'RETURN'
1897 THEN
1898 l_return_quantity := (EstDtlTab(i).quantity_required * (-1));
1899 l_adjusted_amount := (EstDtlTab(i).after_warranty_cost/l_return_quantity) - EstDtlTab(i).selling_price;
1900 ELSE
1901 l_adjusted_amount := (EstDtlTab(i).after_warranty_cost/EstDtlTab(i).quantity_required) - EstDtlTab(i).selling_price;
1902 END IF;
1903
1904 l_line_price_adj_tbl(j).operation_code := 'CREATE';
1905 l_line_price_adj_tbl(j).qte_line_index := 1;
1906 l_line_price_adj_tbl(j).Modifier_header_id := l_modifier_header_id;
1907 l_line_price_adj_tbl(j).Modifier_line_id := l_modifier_line_id;
1908 l_line_price_adj_tbl(j).operand := l_operand;
1909 l_line_price_adj_tbl(j).adjusted_amount := l_adjusted_amount;
1910 l_line_price_adj_tbl(j).updated_flag := 'Y';
1911 l_line_price_adj_tbl(j).applied_flag := 'Y';
1912 l_line_price_adj_tbl(j).change_reason_code := 'MANUAL';
1913 l_line_price_adj_tbl(j).change_reason_text := 'Manually Applied';
1914 l_line_tbl(j).line_list_price := nvl(EstDtlTab(i).selling_price,0); --5408354
1915 END IF;
1916 -- << End >> Changed the logic of modifier based Calculation based on bug 5408354
1917
1918
1919
1920
1921 /* IF (l_modifier_line_id IS NOT NULL) THEN
1922
1923 l_before_warranty_cost := (nvl(EstDtlTab(i).list_price,0) *
1924 EstDtlTab(i).quantity_required); --4870210
1925 IF l_before_warranty_cost = 0 THEN
1926 l_operand := 100;
1927 ELSE
1928 l_operand := (l_before_warranty_cost -
1929 (nvl(EstDtlTab(i).after_warranty_cost,0)))/l_before_warranty_cost * 100;
1930 END IF;
1931
1932 -- Fix bug 2930729
1933 IF EstDtlTab(i).line_category_code = 'RETURN' then
1934 l_return_quantity := (EstDtlTab(i).quantity_required * (-1));
1935 l_adjusted_amount := (EstDtlTab(i).after_warranty_cost/l_return_quantity) - EstDtlTab(i).list_price;
1936 ELSE
1937 l_adjusted_amount := (EstDtlTab(i).after_warranty_cost/EstDtlTab(i).quantity_required) - EstDtlTab(i).list_price;
1938 END IF;
1939
1940 l_line_price_adj_tbl(j).operation_code := 'CREATE';
1941 l_line_price_adj_tbl(j).qte_line_index := 1;
1942 l_line_price_adj_tbl(j).Modifier_header_id := l_modifier_header_id;
1943 l_line_price_adj_tbl(j).Modifier_line_id := l_modifier_line_id;
1944 l_line_price_adj_tbl(j).operand := l_operand;
1945 -- Bug 2930729 l_line_price_adj_tbl(j).adjusted_amount := l_operand;
1946 l_line_price_adj_tbl(j).adjusted_amount := l_adjusted_amount; -- Bug 2930729
1947 l_line_price_adj_tbl(j).updated_flag := 'Y';
1948 l_line_price_adj_tbl(j).applied_flag := 'Y';
1949 l_line_price_adj_tbl(j).change_reason_code := 'MANUAL';
1950 l_line_price_adj_tbl(j).change_reason_text := 'Manually Applied';
1951
1952 END IF; */
1953 --
1954 --
1955 -- Populate return_serial_number value in OM if the item is not ib_trackable and line type is return.
1956 --
1957 IF (EstDtlTab(i).comms_nl_trackable_flag = 'N' and
1958 EstDtlTab(i).line_category_code = 'RETURN' and
1959 EstDtlTab(i).return_serial_number IS NOT NULL) THEN
1960
1961 l_lot_serial_tbl(1).lot_number := FND_API.G_MISS_CHAR;
1962 l_lot_serial_tbl(1).lot_serial_id := FND_API.G_MISS_NUM;
1963 l_lot_serial_tbl(1).quantity := abs(EstDtlTab(i).quantity_required);
1964 l_lot_serial_tbl(1).from_serial_number := EstDtlTab(i).return_serial_number;
1965 l_lot_serial_tbl(1).to_serial_number := EstDtlTab(i).return_serial_number;
1966 l_lot_serial_tbl(1).operation := 'CREATE';
1967 l_lot_serial_tbl(1).line_index := j;
1968 END IF;
1969 --
1970 --
1971 -- Populate Install Base API records
1972 --
1973 --
1974 IF EstDtlTab(i).comms_nl_trackable_flag = 'Y' THEN
1975 -- Get Transaction type information
1976 BEGIN
1977 SELECT a.transaction_type_id,
1978 b.sub_type_id,
1979 c.transaction_type_id,
1980 nvl(b.src_reference_reqd,'N'),
1981 b.src_change_owner,
1982 b.src_change_owner_to_code,
1983 nvl(b.non_src_reference_reqd,'N'),
1984 b.non_src_change_owner,
1985 b.non_src_change_owner_to_code,
1986 nvl(b.update_ib_flag,'N'),
1987 b.src_return_reqd -- Bug 4586140
1988 INTO l_transaction_type_id,
1989 l_sub_type_id,
1990 l_source_type_id,
1991 l_src_reference_reqd,
1992 l_src_change_owner,
1993 l_src_change_owner_to_code,
1994 l_non_src_reference_reqd,
1995 l_non_src_change_owner,
1996 l_non_src_change_owner_to_code,
1997 l_update_ib_flag,
1998 l_src_return_reqd_flag -- Bug 4586140
1999 FROM CS_TXN_BILLING_TYPES a,
2000 csi_txn_sub_types b,
2001 csi_txn_types c
2002 WHERE a.txn_billing_type_id = EstDtlTab(i).txn_billing_type_id
2003 AND a.transaction_type_id = b.cs_transaction_type_id
2004 AND b.transaction_type_id = c.transaction_type_id
2005 AND c.source_application_id = 660
2006 AND c.source_transaction_type =
2007 decode(EstDtlTab(i).line_category_code,'RETURN', 'RMA_RECEIPT', 'ORDER', 'OM_SHIPMENT',null);
2008 EXCEPTION
2009 WHEN TOO_MANY_ROWS THEN
2010 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_TOO_MANY_SOURCES');
2011 FND_MSG_PUB.Add;
2012 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2013 p_count => x_msg_count,
2014 p_data => x_msg_data);
2015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2016
2017 WHEN NO_DATA_FOUND THEN
2018 l_update_ib_flag := 'N';
2019 /* Commenting out this exception as part of IB changes */
2020 /* FND_MESSAGE.Set_Name('CS','CS_CHG_IB_NO_VALID_TXN_TYPE');
2021 FND_MSG_PUB.Add;
2022 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2023 p_count => x_msg_count,
2024 p_data => x_msg_data);
2025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
2026 END;
2027
2028 -- Populate installation details if there is instance information and
2029 -- update_ib_flag is 'Y',but do not raise any error message.
2030 -- Bug fix 3564034
2031
2032 IF l_update_ib_flag = 'Y' THEN
2033 IF EstDtlTab(i).customer_product_id is not null THEN
2034 -- For return lines for IB, pass serial number to OM
2035 IF EstDtlTab(i).line_category_code = 'RETURN' THEN
2036 l_ib_serial_number := null;
2037 BEGIN
2038 select serial_number,
2039 lot_number -- Bug 8284773
2040 into l_ib_serial_number,
2041 l_ib_lot_number --Bug 8284773
2042 from CSI_ITEM_INSTANCES
2043 where instance_id = EstDtlTab(i).customer_product_id;
2044 EXCEPTION
2045 -- Serial_number is available only if the instance is serialized
2046 /* WHEN NO_DATA_FOUND THEN
2047 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_MISSING_INSTANCE');
2048 FND_MSG_PUB.Add;
2049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
2050 WHEN OTHERS THEN
2051 RAISE;
2052 END;
2053 IF l_ib_serial_number is not null THEN
2054 l_lot_serial_tbl(1).lot_number := FND_API.G_MISS_CHAR;
2055 l_lot_serial_tbl(1).lot_serial_id := FND_API.G_MISS_NUM;
2056 l_lot_serial_tbl(1).quantity := abs(EstDtlTab(i).quantity_required);
2057 l_lot_serial_tbl(1).from_serial_number := l_ib_serial_number;
2058 l_lot_serial_tbl(1).to_serial_number := l_ib_serial_number;
2059 l_lot_serial_tbl(1).operation := 'CREATE';
2060 l_lot_serial_tbl(1).line_index := j;
2061 END IF;
2062 --Bug 8284773
2063 IF l_ib_lot_number is not null THEN
2064 l_lot_serial_tbl(1).lot_number := l_ib_lot_number;
2065 l_lot_serial_tbl(1).lot_serial_id := FND_API.G_MISS_NUM;
2066 l_lot_serial_tbl(1).quantity := abs(EstDtlTab(i).quantity_required);
2067 l_lot_serial_tbl(1).operation := 'CREATE';
2068 l_lot_serial_tbl(1).line_index := j;
2069 END IF;
2070 -- End Bug 8284773
2071 /* ELSE
2072 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_MISSING_INSTANCE');
2073 FND_MSG_PUB.Add;
2074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
2075 END IF;
2076 END IF;
2077
2078
2079 /** Raise an error message , if the src_reference_reqd flag or the non_src_reference_reqd_flag
2080 are 'Y',update_ib_flag is 'Y' and the instance_id is null ***/
2081
2082 IF (l_src_reference_reqd = 'Y' OR
2083 l_non_src_reference_reqd = 'Y') AND
2084 l_update_ib_flag = 'Y' AND
2085 EstDtlTab(i).customer_product_id IS NULL THEN
2086
2087 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_MISSING_INSTANCE');
2088 FND_MSG_PUB.Add;
2089 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2090 END IF;
2091 --
2092 -- Clear IB structures
2093 --
2094 csi_txn_line_rec := csi_txn_line_rec_null;
2095 csi_txn_line_detail_tbl.delete;
2096 csi_txn_party_detail_tbl.delete;
2097 csi_txn_pty_acct_detail_tbl.delete;
2098 csi_txn_ii_rltns_tbl.delete;
2099 csi_txn_org_assgn_tbl.delete;
2100 csi_txn_ext_attrib_vals_tbl.delete;
2101 csi_txn_systems_tbl.delete;
2102 l_internal_party_id := null;
2103 l_instance_party_id := null;
2104
2105 --
2106 -- Create Transaction Line Record
2107 --
2108 csi_txn_line_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
2109 csi_txn_line_rec.source_transaction_type_id := l_source_type_id;
2110
2111 --
2112 -- Create IB Transaction Line Detail and Relationship Record(s)
2113 -- Fix for bug:3564034
2114 IF EstDtlTab(i).line_category_code = 'RETURN' and
2115 EstDtlTab(i).customer_product_id is not null then
2116 csi_txn_line_detail_tbl(1).sub_type_id := l_sub_type_id;
2117 csi_txn_line_detail_tbl(1).source_transaction_flag := 'Y';
2118 IF (l_src_reference_reqd = 'Y' OR
2119 l_src_reference_reqd = 'N') AND
2120 (EstDtlTab(i).Customer_product_id is not null) THEN
2121 csi_txn_line_detail_tbl(1).instance_id := EstDtlTab(i).Customer_Product_Id;
2122 csi_txn_line_detail_tbl(1).instance_exists_flag := 'Y';
2123 -- 12.1.3 Charges Logistics Project
2124 If EstDtlTab(i).parent_instance_id is not null Then
2125 csi_txn_line_detail_tbl(1).parent_instance_id := EstDtlTab(i).parent_instance_id;
2126 Else
2127 -- Bug 8203856
2128 open get_parent_instance(EstDtlTab(i).Customer_Product_Id);
2129 Fetch get_parent_instance into l_parent_instance_id;
2130 If get_parent_instance%FOUND Then
2131 csi_txn_line_detail_tbl(1).parent_instance_id := l_parent_instance_id;
2132 End If;
2133 Close get_parent_instance;
2134 End If;
2135 -- End Bug 8203856
2136 ELSE
2137 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_INVALID_RET_TXN_DATA');
2138 FND_MSG_PUB.Add;
2139 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2140 p_count => x_msg_count,
2141 p_data => x_msg_data);
2142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2143 -- null;
2144
2145 END IF;
2146
2147 -- Bug 11936035
2148 IF EstDtlTab(i).System_id is not null THEN
2149 csi_txn_line_detail_tbl(1).csi_system_id := EstDtlTab(i).system_id;
2150 ELSE
2151 csi_txn_line_detail_tbl(1).csi_system_id := FND_API.G_MISS_NUM;
2152 END IF;
2153 --end Bug 11936035
2154 IF EstDtlTab(i).Item_revision is not null THEN
2155 csi_txn_line_detail_tbl(1).inventory_revision := EstDtlTab(i).Item_revision;
2156 ELSE
2157 csi_txn_line_detail_tbl(1).inventory_revision := FND_API.G_MISS_CHAR;
2158 END IF;
2159
2160 csi_txn_line_detail_tbl(1).inventory_item_id := EstDtlTab(i).Inventory_Item_id;
2161 csi_txn_line_detail_tbl(1).inv_organization_id := CS_STD.Get_Item_Valdn_Orgzn_ID;
2162 csi_txn_line_detail_tbl(1).unit_of_measure := EstDtlTab(i).unit_of_measure_code;
2163 csi_txn_line_detail_tbl(1).quantity := abs(EstDtlTab(i).quantity_required);
2164
2165 IF EstDtlTab(i).installed_cp_return_by_date is not null THEN
2166 csi_txn_line_detail_tbl(1).return_by_date := EstDtlTab(i).installed_cp_return_by_date;
2167 ELSE
2168 csi_txn_line_detail_tbl(1).return_by_date := FND_API.G_MISS_DATE;
2169 END IF;
2170
2171 ELSE -- Shipment Line
2172 csi_txn_line_detail_tbl(1).sub_type_id := l_sub_type_id;
2173 csi_txn_line_detail_tbl(1).source_transaction_flag := 'Y';
2174
2175 csi_txn_line_detail_tbl(1).instance_id := FND_API.G_MISS_NUM;
2176 csi_txn_line_detail_tbl(1).instance_exists_flag := 'N';
2177
2178 IF EstDtlTab(i).Item_revision is not null THEN
2179 csi_txn_line_detail_tbl(1).inventory_revision := EstDtlTab(i).Item_revision;
2180 ELSE
2181 csi_txn_line_detail_tbl(1).inventory_revision := FND_API.G_MISS_CHAR;
2182 END IF;
2183
2184 --Bug 11936035
2185 IF EstDtlTab(i).System_id is not null THEN
2186 csi_txn_line_detail_tbl(1).csi_system_id := EstDtlTab(i).system_id;
2187 ELSE
2188 csi_txn_line_detail_tbl(1).csi_system_id := FND_API.G_MISS_NUM;
2189 END IF;
2190 --end Bug 11936035
2191
2192 csi_txn_line_detail_tbl(1).inventory_item_id := EstDtlTab(i).Inventory_Item_id;
2193 csi_txn_line_detail_tbl(1).inv_organization_id := CS_STD.Get_Item_Valdn_Orgzn_ID;
2194 csi_txn_line_detail_tbl(1).unit_of_measure := EstDtlTab(i).unit_of_measure_code;
2195 csi_txn_line_detail_tbl(1).quantity := abs(EstDtlTab(i).quantity_required);
2196
2197 /*
2198 -- Bug 4586140
2199 IF l_src_return_reqd_flag = 'Y' THEN
2200 IF EstDtlTab(i).installed_cp_return_by_date is not null THEN
2201 csi_txn_line_detail_tbl(1).return_by_date := EstDtlTab(i).installed_cp_return_by_date;
2202 ELSE
2203 csi_txn_line_detail_tbl(1).return_by_date := FND_API.G_MISS_DATE;
2204 END IF;
2205 END IF;
2206 */
2207 -- Commented the above and uncommented the below IF block for Bug# 5136853.
2208 IF EstDtlTab(i).new_cp_return_by_date is not null THEN
2209 csi_txn_line_detail_tbl(1).return_by_date := EstDtlTab(i).new_cp_return_by_date;
2210 ELSE
2211 csi_txn_line_detail_tbl(1).return_by_date := FND_API.G_MISS_DATE;
2212 END IF;
2213
2214 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level THEN
2215 FND_LOG.String ( FND_LOG.level_procedure , L_LOG_MODULE , 'Bug 12626780 Start of Validation:'||EstDtlTab(i).Customer_product_id);
2216 END IF;
2217
2218 -- Referenced IB (non-source)
2219 IF l_non_src_reference_reqd = 'Y'
2220 OR l_non_src_reference_reqd = 'N'
2221 AND EstDtlTab(i).Customer_product_id is not null THEN
2222 csi_txn_line_detail_tbl(2).sub_type_id := l_sub_type_id;
2223 csi_txn_line_detail_tbl(2).source_transaction_flag := 'N';
2224
2225 IF EstDtlTab(i).Customer_product_id is not null THEN
2226 csi_txn_line_detail_tbl(2).instance_id := EstDtlTab(i).Customer_Product_Id;
2227 csi_txn_line_detail_tbl(2).instance_exists_flag := 'Y';
2228 -- 12.1.3 Charges Logistics Project
2229 If EstDtlTab(i).parent_instance_id is not null then
2230 csi_txn_line_detail_tbl(2).parent_instance_id := EstDtlTab(i).parent_instance_id;
2231 Else
2232 -- Bug 8203856
2233 open get_parent_instance(EstDtlTab(i).Customer_Product_Id);
2234 Fetch get_parent_instance into l_parent_instance_id;
2235 If get_parent_instance%FOUND Then
2236 csi_txn_line_detail_tbl(2).parent_instance_id := l_parent_instance_id;
2237 End If;
2238 Close get_parent_instance;
2239 -- End Bug 8203856
2240 End If;
2241 -- fix bug:3593660
2242 csi_txn_line_detail_tbl(2).assc_txn_line_detail_id := 1;
2243
2244 /* Do not raise any error message if instance is missing.New 11.5.10 changes */
2245
2246 /* ELSE
2247 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_INVALID_SHP_TXN_DATA');
2248 FND_MSG_PUB.Add;
2249 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2250 p_count => x_msg_count,
2251 p_data => x_msg_data);
2252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
2253 END IF;
2254
2255 /* BUG 4287842 for a loaner shipment, installation details should display the ITEM for which the instance_number was selected. Currently it displays the Item irrespective of the reference number*/
2256
2257 OPEN get_inv_item_id(EstDtlTab(i).customer_product_id);
2258 FETCH get_inv_item_id
2259 INTO l_inventory_item_id;
2260 CLOSE get_inv_item_id;
2261
2262 csi_txn_line_detail_tbl(2).inventory_item_id := l_inventory_item_id;
2263 csi_txn_line_detail_tbl(2).inv_organization_id := CS_STD.Get_Item_Valdn_Orgzn_ID;
2264 csi_txn_line_detail_tbl(2).unit_of_measure := EstDtlTab(i).unit_of_measure_code;
2265 csi_txn_line_detail_tbl(2).quantity := abs(EstDtlTab(i).quantity_required);
2266
2267 /*
2268 IF EstDtlTab(i).Item_revision is not null THEN
2269 csi_txn_line_detail_tbl(2).inventory_revision := EstDtlTab(i).Item_revision;
2270 ELSE
2271 csi_txn_line_detail_tbl(2).inventory_revision := FND_API.G_MISS_CHAR;
2272 END IF;
2273 */
2274
2275 -- Uncommented the below IF block for Bug# 5136853.
2276 IF EstDtlTab(i).installed_cp_return_by_date is not null THEN
2277 csi_txn_line_detail_tbl(2).return_by_date := EstDtlTab(i).installed_cp_return_by_date;
2278 ELSE
2279 csi_txn_line_detail_tbl(2).return_by_date := FND_API.G_MISS_DATE;
2280 END IF;
2281
2282
2283 -- Create relationship between two txn line detail records
2284 csi_txn_ii_rltns_tbl(1).subject_id := 2;
2285 csi_txn_ii_rltns_tbl(1).object_id := 1;
2286 csi_txn_ii_rltns_tbl(1).relationship_type_code := 'REPLACED-BY';
2287 --Srini Bug 12626780
2288 Else
2289 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level THEN
2290 FND_LOG.String ( FND_LOG.level_procedure , L_LOG_MODULE , ' Bug 12626780 Else condition:');
2291 END IF;
2292
2293 csi_txn_line_detail_tbl(2).sub_type_id := l_sub_type_id;
2294 csi_txn_line_detail_tbl(2).source_transaction_flag := 'N';
2295
2296 IF EstDtlTab(i).parent_instance_id is not null THEN
2297 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level THEN
2298 FND_LOG.String ( FND_LOG.level_procedure , L_LOG_MODULE , ' Bug 12626780 Parent instance id :'||EstDtlTab(i).parent_instance_id);
2299 END IF;
2300
2301
2302 If EstDtlTab(i).parent_instance_id is not null then
2303 csi_txn_line_detail_tbl(2).instance_id := EstDtlTab(i).parent_instance_id;
2304 csi_txn_line_detail_tbl(2).instance_exists_flag := 'Y';
2305 End If;
2306
2307 csi_txn_line_detail_tbl(2).assc_txn_line_detail_id := 1;
2308
2309 END IF;
2310
2311 csi_txn_line_detail_tbl(2).inventory_item_id := EstDtlTab(i).Inventory_Item_id;
2312 csi_txn_line_detail_tbl(2).inv_organization_id := CS_STD.Get_Item_Valdn_Orgzn_ID;
2313 csi_txn_line_detail_tbl(2).unit_of_measure := EstDtlTab(i).unit_of_measure_code;
2314 csi_txn_line_detail_tbl(2).quantity := abs(EstDtlTab(i).quantity_required);
2315
2316 IF EstDtlTab(i).installed_cp_return_by_date is not null THEN
2317 csi_txn_line_detail_tbl(2).return_by_date := EstDtlTab(i).installed_cp_return_by_date;
2318 ELSE
2319 csi_txn_line_detail_tbl(2).return_by_date := FND_API.G_MISS_DATE;
2320 END IF;
2321 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level THEN
2322 FND_LOG.String ( FND_LOG.level_procedure , L_LOG_MODULE , ' Bug 12626780 Component Info :');
2323 END IF;
2324
2325 csi_txn_ii_rltns_tbl(1).subject_id := 1;
2326 csi_txn_ii_rltns_tbl(1).object_id := 2;
2327 csi_txn_ii_rltns_tbl(1).relationship_type_code := 'COMPONENT-OF';
2328 --End srini Bug 12626780
2329
2330 END IF; -- IF l_non_src_reference_reqd = 'Y'
2331 END IF; -- IF EstDtlTab(i).line_category_code = 'RETURN'
2332
2333 --
2334 -- Create IB Party details and account details records,when p_account_id is not null.
2335 --
2336 IF EstDtlTab(i).account_id IS NOT NULL THEN
2337 IF EstDtlTab(i).line_category_code = 'RETURN' then
2338 IF l_src_change_owner = 'Y' THEN
2339 IF l_src_change_owner_to_code = 'I' THEN
2340 BEGIN
2341 SELECT internal_party_id
2342 INTO l_internal_party_id
2343 FROM csi_install_parameters
2344 WHERE rownum = 1;
2345
2346 IF l_internal_party_id is null THEN
2347 RAISE l_IB_ERROR;
2348 END IF;
2349 EXCEPTION
2350 WHEN OTHERS THEN
2351 FND_MESSAGE.Set_Name('CS','CS_CHG_INTERNAL_PARTY_NOT_DEF');
2352 FND_MSG_PUB.Add;
2353 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2354 p_count => x_msg_count,
2355 p_data => x_msg_data);
2356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2357 END;
2358
2359 -- Start Bug 8209077
2360 DECLARE
2361
2362 -- Start bug 9823087
2363 /* CURSOR c_instance_party is
2364 SELECT instance_party_id,
2365 relationship_type_code
2366 FROM CSI_I_PARTIES
2367 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
2368 --AND relationship_type_code = 'OWNER' -- Bug 8209077
2369 AND party_id = (SELECT party_id
2370 FROM hz_cust_accounts
2371 WHERE cust_account_id = EstDtlTab(i).account_id);*/
2372
2373 CURSOR c_instance_party_owner is
2374 SELECT instance_party_id,
2375 relationship_type_code
2376 FROM CSI_I_PARTIES
2377 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
2378 AND relationship_type_code = 'OWNER'
2379 AND party_id = (SELECT party_id
2380 FROM hz_cust_accounts
2381 WHERE cust_account_id = EstDtlTab(i).account_id);
2382
2383 CURSOR c_instance_party is
2384 SELECT instance_party_id,
2385 relationship_type_code
2386 FROM CSI_I_PARTIES
2387 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
2388 AND relationship_type_code <> 'OWNER'
2389 AND party_id = (SELECT party_id
2390 FROM hz_cust_accounts
2391 WHERE cust_account_id = EstDtlTab(i).account_id);
2392 -- End bug 9823087
2393 k Number;
2394
2395 BEGIN
2396 k := 0;
2397
2398 -- Start bug 9823087
2399 For i in c_instance_party_owner loop
2400 k := k + 1;
2401 csi_txn_party_detail_tbl(k).instance_party_id := i.instance_party_id;
2402 csi_txn_party_detail_tbl(k).party_source_table := 'HZ_PARTIES';
2403 csi_txn_party_detail_tbl(k).party_source_id := l_internal_party_id;
2404 csi_txn_party_detail_tbl(k).relationship_type_code := i.relationship_type_code;
2405 csi_txn_party_detail_tbl(k).txn_line_details_index := k;
2406 csi_txn_party_detail_tbl(k).contact_flag := 'N';
2407 --csi_txn_pty_acct_detail_tbl.delete;
2408 End loop;
2409
2410 For i in c_instance_party loop
2411 k := k + 1;
2412 csi_txn_party_detail_tbl(k).instance_party_id := i.instance_party_id;
2413 csi_txn_party_detail_tbl(k).party_source_table := 'HZ_PARTIES';
2414 csi_txn_party_detail_tbl(k).party_source_id := l_internal_party_id;
2415 csi_txn_party_detail_tbl(k).relationship_type_code := i.relationship_type_code;
2416 csi_txn_party_detail_tbl(k).txn_line_details_index := k;
2417 csi_txn_party_detail_tbl(k).contact_flag := 'N';
2418 --csi_txn_pty_acct_detail_tbl.delete;
2419 End loop;
2420
2421 /*For i in c_instance_party loop
2422 k := k + 1;
2423 csi_txn_party_detail_tbl(k).instance_party_id := i.instance_party_id;
2424 csi_txn_party_detail_tbl(k).party_source_table := 'HZ_PARTIES';
2425 csi_txn_party_detail_tbl(k).party_source_id := l_internal_party_id;
2426 csi_txn_party_detail_tbl(k).relationship_type_code := i.relationship_type_code;
2427 csi_txn_party_detail_tbl(k).txn_line_details_index := k;
2428 csi_txn_party_detail_tbl(k).contact_flag := 'N';
2429 --csi_txn_pty_acct_detail_tbl.delete;
2430 End loop;*/
2431 End;
2432
2433 /* BEGIN
2434 SELECT instance_party_id
2435 INTO l_instance_party_id
2436 FROM CSI_I_PARTIES
2437 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
2438 AND relationship_type_code = 'OWNER'
2439 AND party_id = (SELECT party_id
2440 FROM hz_cust_accounts
2441 WHERE cust_account_id = EstDtlTab(i).account_id);
2442
2443 EXCEPTION
2444 WHEN OTHERS THEN
2445 FND_MESSAGE.Set_Name('CS','CS_CHG_INSTANCE_NOT_OWN_BY_PTY');
2446 FND_MSG_PUB.Add;
2447 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2448 p_count => x_msg_count,
2449 p_data => x_msg_data);
2450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2451 END;
2452
2453 csi_txn_party_detail_tbl(1).instance_party_id := l_instance_party_id;
2454 csi_txn_party_detail_tbl(1).party_source_table := 'HZ_PARTIES';
2455 csi_txn_party_detail_tbl(1).party_source_id := l_internal_party_id;
2456 csi_txn_party_detail_tbl(1).relationship_type_code := 'OWNER';
2457 csi_txn_party_detail_tbl(1).txn_line_details_index := 1;
2458 csi_txn_party_detail_tbl(1).contact_flag := 'N';*/
2459 csi_txn_pty_acct_detail_tbl.delete;
2460 ELSE
2461 FND_MESSAGE.Set_Name('CS','CS_CHG_RTN_TO_EXT_PTY_NOT_SUP');
2462 FND_MSG_PUB.Add;
2463 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2464 p_count => x_msg_count,
2465 p_data => x_msg_data);
2466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2467 END IF; -- IF l_src_change_owner_to_code = 'I'
2468
2469 --taklam
2470 ---NEW CODE
2471 ELSIF l_src_change_owner = 'N' AND l_src_change_owner_to_code is NULL THEN
2472 BEGIN
2473 SELECT internal_party_id
2474 INTO l_internal_party_id
2475 FROM csi_install_parameters
2476 WHERE rownum = 1;
2477
2478
2479 IF l_internal_party_id is null THEN
2480 RAISE l_IB_ERROR;
2481 END IF;
2482 EXCEPTION
2483 WHEN OTHERS THEN
2484 FND_MESSAGE.Set_Name('CS','CS_CHG_INTERNAL_PARTY_NOT_DEF');
2485 FND_MSG_PUB.Add;
2486 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2487 p_count => x_msg_count,
2488 p_data => x_msg_data);
2489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2490 END;
2491
2492 -- Start Bug 8209077
2493 DECLARE
2494
2495 /*Cursor c_instance_party1 is
2496 SELECT instance_party_id,
2497 relationship_type_code
2498 FROM CSI_I_PARTIES
2499 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
2500 --AND relationship_type_code = 'OWNER'
2501 AND party_id IN (l_internal_party_id,EstDtlTab(i).customer_id);*/
2502
2503 Cursor c_instance_party1_owner is
2504 SELECT instance_party_id,
2505 relationship_type_code
2506 FROM CSI_I_PARTIES
2507 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
2508 AND relationship_type_code = 'OWNER'
2509 AND party_id IN (l_internal_party_id,EstDtlTab(i).customer_id);
2510
2511
2512 Cursor c_instance_party1 is
2513 SELECT instance_party_id,
2514 relationship_type_code
2515 FROM CSI_I_PARTIES
2516 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
2517 AND relationship_type_code <> 'OWNER'
2518 AND party_id IN (l_internal_party_id,EstDtlTab(i).customer_id);
2519 -- End bug 9823087
2520
2521 k number;
2522 BEGIN
2523 IF EstDtlTab(i).Customer_Product_Id is not NULL THEN
2524 k := 0;
2525
2526 /*For i in c_instance_party1 loop
2527 k := k + 1;
2528 csi_txn_party_detail_tbl(k).instance_party_id := i.instance_party_id;
2529 csi_txn_party_detail_tbl(k).party_source_table := 'HZ_PARTIES';
2530 csi_txn_party_detail_tbl(k).party_source_id := l_internal_party_id;
2531 csi_txn_party_detail_tbl(k).relationship_type_code := i.relationship_type_code;
2532 csi_txn_party_detail_tbl(k).txn_line_details_index := k;
2533 csi_txn_party_detail_tbl(k).contact_flag := 'N';
2534 End Loop;*/
2535
2536 For i in c_instance_party1_owner loop
2537 k := k + 1;
2538 csi_txn_party_detail_tbl(k).instance_party_id := i.instance_party_id;
2539 csi_txn_party_detail_tbl(k).party_source_table := 'HZ_PARTIES';
2540 csi_txn_party_detail_tbl(k).party_source_id := l_internal_party_id;
2541 csi_txn_party_detail_tbl(k).relationship_type_code := i.relationship_type_code;
2542 csi_txn_party_detail_tbl(k).txn_line_details_index := k;
2543 csi_txn_party_detail_tbl(k).contact_flag := 'N';
2544 End Loop;
2545
2546 For i in c_instance_party1 loop
2547 k := k + 1;
2548 csi_txn_party_detail_tbl(k).instance_party_id := i.instance_party_id;
2549 csi_txn_party_detail_tbl(k).party_source_table := 'HZ_PARTIES';
2550 csi_txn_party_detail_tbl(k).party_source_id := l_internal_party_id;
2551 csi_txn_party_detail_tbl(k).relationship_type_code := i.relationship_type_code;
2552 csi_txn_party_detail_tbl(k).txn_line_details_index := k;
2553 csi_txn_party_detail_tbl(k).contact_flag := 'N';
2554 End Loop;
2555
2556 /* BEGIN
2557 IF EstDtlTab(i).Customer_Product_Id is not NULL THEN
2558 SELECT instance_party_id
2559 INTO l_instance_party_id
2560 FROM CSI_I_PARTIES
2561 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
2562 AND relationship_type_code = 'OWNER'
2563 AND party_id IN (l_internal_party_id,EstDtlTab(i).customer_id);*/
2564 ELSE
2565 NULL; -- submit successful. But, do not create installation details.
2566 END IF;
2567
2568 /* EXCEPTION
2569 WHEN OTHERS THEN
2570 FND_MESSAGE.Set_Name('CS','CS_CHG_INSTANCE_NOT_OWN_BY_PTY');
2571 FND_MSG_PUB.Add;
2572 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2573 p_count => x_msg_count,
2574 p_data => x_msg_data);
2575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;*/
2576 END;
2577 /*
2578 csi_txn_party_detail_tbl(1).instance_party_id := l_instance_party_id;
2579 csi_txn_party_detail_tbl(1).party_source_table := 'HZ_PARTIES';
2580 csi_txn_party_detail_tbl(1).party_source_id := l_internal_party_id;
2581 csi_txn_party_detail_tbl(1).relationship_type_code := 'OWNER';
2582 csi_txn_party_detail_tbl(1).txn_line_details_index := 1;
2583 csi_txn_party_detail_tbl(1).contact_flag := 'N';*/
2584 csi_txn_pty_acct_detail_tbl.delete;
2585 --taklam
2586
2587 ELSE
2588 csi_txn_party_detail_tbl.delete;
2589 csi_txn_pty_acct_detail_tbl.delete;
2590 END IF; -- IF l_src_change_owner = 'Y'
2591
2592 ELSE -- shipment line
2593 IF l_src_change_owner = 'Y' THEN
2594 -- Create Party record
2595 csi_txn_party_detail_tbl(1).party_source_table := 'HZ_PARTIES';
2596 csi_txn_party_detail_tbl(1).party_source_id := EstDtlTab(i).customer_id;
2597 csi_txn_party_detail_tbl(1).relationship_type_code := 'OWNER';
2598 csi_txn_party_detail_tbl(1).txn_line_details_index := 1;
2599 csi_txn_party_detail_tbl(1).contact_flag := 'N';
2600
2601 -- Create Account record
2602 csi_txn_pty_acct_detail_tbl(1).account_id := EstDtlTab(i).account_id;
2603 csi_txn_pty_acct_detail_tbl(1).relationship_type_code := 'OWNER';
2604 csi_txn_pty_acct_detail_tbl(1).txn_party_details_index := 1;
2605 csi_txn_pty_acct_detail_tbl(1).active_start_date := sysdate;
2606 END IF;
2607 END IF; -- IF EstDtlTab(i).line_category_code = 'RETURN'
2608
2609 END IF; --IF account_id is not null.
2610
2611 END IF; -- IF Update_Ib_Flag = Y
2612
2613 END IF; -- If COMMS_NL_TRACKABLE_FLAG = Y
2614
2615 --
2616 -- The ASO debug statements are replaced with the FND Logging
2617
2618 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2619 THEN
2620 FND_LOG.String
2621 ( FND_LOG.level_statement, L_LOG_MODULE , '==================================================='
2622 );
2623 FND_LOG.String
2624 ( FND_LOG.level_statement, L_LOG_MODULE , 'Beginning Charges submission'
2625 );
2626 FND_LOG.String
2627 ( FND_LOG.level_statement, L_LOG_MODULE , 'Item revision is: ' || EstDtlTab(i).item_revision
2628 );
2629 FND_LOG.String
2630 ( FND_LOG.level_statement, L_LOG_MODULE , 'Estimate Detail_id is: ' || EstDtlTab(i).estimate_detail_id
2631 );
2632 FND_LOG.String
2633 ( FND_LOG.level_statement, L_LOG_MODULE , 'Selling_price: '|| EstDtlTab(i).selling_price
2634 ) ;
2635 FND_LOG.String
2636 ( FND_LOG.level_statement, L_LOG_MODULE , 'After Warranty Cost: ' || EstDtlTab(i).after_warranty_cost
2637 );
2638 FND_LOG.String
2639 ( FND_LOG.level_statement, L_LOG_MODULE , 'Incident Id is: '|| EstDtlTab(i).incident_id
2640 );
2641 FND_LOG.String
2642 ( FND_LOG.level_statement, L_LOG_MODULE , 'Charges Invoice to party site id: ' || EstDtlTab(i).invoice_to_org_id
2643 );
2644 FND_LOG.String
2645 ( FND_LOG.level_statement, L_LOG_MODULE , 'Charges Ship to party site id: ' || EstDtlTab(i).ship_to_org_id
2646 );
2647 /* Credit Card 9358401 */
2648 FND_LOG.String
2649 ( FND_LOG.level_statement, L_LOG_MODULE , 'Charges Ship to Instr Assignment id: ' || EstDtlTab(i).instrument_payment_use_id
2650 );
2651 /* Credit Card 9358401 */
2652 /* Credit Card 14673342 */
2653 FND_LOG.String
2654 ( FND_LOG.level_statement, L_LOG_MODULE , 'Charges Ship to Instr Assignment
2655 id: ' || EstDtlTab(i).sr_instr_payment_use_id
2656 );
2657 /* Credit Card 14673342 */
2658
2659 FND_LOG.String
2660 ( FND_LOG.level_statement, L_LOG_MODULE , ' '
2661 );
2662
2663 FND_LOG.String
2664 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_control_rec.book_flag is: ' || l_control_rec.book_flag
2665 );
2666 FND_LOG.String
2667 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_control_rec.calculate_price is: ' || l_control_rec.calculate_price
2668 );
2669 FND_LOG.String
2670 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.Order_id is: '|| l_header_rec.order_id
2671 );
2672 FND_LOG.String
2673 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.Order_type_id is: '|| l_header_rec.order_type_id
2674 );
2675 FND_LOG.String
2676 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.quote_source_code is: '|| l_header_rec.quote_source_code
2677 );
2678 FND_LOG.String
2679 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute1 is: '|| l_header_rec.attribute1
2680 );
2681 FND_LOG.String
2682 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute2 is: '|| l_header_rec.attribute2
2683 );
2684 FND_LOG.String
2685 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute3 is: '|| l_header_rec.attribute3
2686 );
2687 FND_LOG.String
2688 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute4 is: '|| l_header_rec.attribute4
2689 );
2690 FND_LOG.String
2691 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute5 is: '|| l_header_rec.attribute5
2692 );
2693 FND_LOG.String
2694 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute6 is: '|| l_header_rec.attribute6
2695 );
2696 FND_LOG.String
2697 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute7 is: '|| l_header_rec.attribute7
2698 );
2699 FND_LOG.String
2700 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute8 is: '|| l_header_rec.attribute8
2701 );
2702 FND_LOG.String
2703 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute9 is: '|| l_header_rec.attribute9
2704 );
2705 FND_LOG.String
2706 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute10 is: '|| l_header_rec.attribute10
2707 );
2708 FND_LOG.String
2709 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute11 is: '|| l_header_rec.attribute11
2710 );
2711 FND_LOG.String
2712 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute12 is: '|| l_header_rec.attribute12
2713 );
2714 FND_LOG.String
2715 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute13 is: '|| l_header_rec.attribute13
2716 );
2717 FND_LOG.String
2718 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute14 is: '|| l_header_rec.attribute14
2719 );
2720 FND_LOG.String
2721 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute15 is: '|| l_header_rec.attribute15
2722 );
2723 FND_LOG.String
2724 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.Org_Id is: '|| l_header_rec.org_id
2725 );
2726 FND_LOG.String
2727 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.party_id (Customer_id) is: ' || l_header_rec.party_id
2728 );
2729 FND_LOG.String
2730 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.cust_account_id (Customer_account_id) is: ' || l_header_rec.cust_account_id
2731 );
2732 FND_LOG.String
2733 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.quote_header_id is: ' || l_header_rec.quote_header_id
2734 );
2735 FND_LOG.String
2736 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.original_system_reference is: ' || l_header_rec.original_system_reference
2737 );
2738 FND_LOG.String
2739 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.quote_number is: ' || l_header_rec.quote_number
2740 );
2741 FND_LOG.String
2742 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.exchange_rate is: '|| l_header_rec.exchange_rate
2743 );
2744 FND_LOG.String
2745 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.exchange_type_code is: '|| l_header_rec.exchange_type_code
2746 );
2747 FND_LOG.String
2748 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.exchange_rate_date is: '|| l_header_rec.exchange_rate_date
2749 );
2750 FND_LOG.String
2751 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.price_list_id is: ' || l_header_rec.price_list_id
2752 );
2753 FND_LOG.String
2754 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.currency_code is: ' || l_header_rec.currency_code
2755 );
2756 FND_LOG.String
2757 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_Header_rec.invoice_to_cust_account_id: ' || l_header_rec.invoice_to_cust_account_id
2758 );
2759 FND_LOG.String
2760 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_Header_rec.invoice_to_party_id: ' || l_header_rec.invoice_to_party_id
2761 );
2762 FND_LOG.String
2763 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_Header_rec.invoice_to_party_site_id: ' || l_header_rec.invoice_to_party_site_id
2764 );
2765 FND_LOG.String
2766 ( FND_LOG.level_statement, L_LOG_MODULE , 'Update_Ib_Flag: ' || l_update_ib_flag
2767 );
2768 FND_LOG.String
2769 ( FND_LOG.level_statement, L_LOG_MODULE , 'Src_Reference_Reqd_Flag: ' || l_src_reference_reqd
2770 );
2771 FND_LOG.String
2772 ( FND_LOG.level_statement, L_LOG_MODULE , 'Non_Src_Reference_Reqd_Flag: ' || l_non_src_reference_reqd
2773 );
2774
2775 if l_hd_payment_tbl.count = 0 then
2776 FND_LOG.String
2777 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_payment_tbl does not exist'
2778 );
2779 else
2780 FND_LOG.String
2781 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_payment_tbl.cust_po_number is: '|| l_hd_payment_tbl(j).cust_po_number
2782 );
2783 FND_LOG.String
2784 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_payment_tbl.payment_type_code is: '|| l_hd_payment_tbl(j).payment_type_code
2785 );
2786 end if;
2787
2788 if l_hd_shipment_tbl.count = 0 then
2789 FND_LOG.String
2790 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_shipment_tbl does not exist'
2791 );
2792 else
2793 FND_LOG.String
2794 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_shipment_tbl.ship_to_cust_account_id: ' || l_hd_shipment_tbl(j).ship_to_cust_account_id
2795 );
2796 FND_LOG.String
2797 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_shipment_tbl.ship_to_party_id: ' || l_hd_shipment_tbl(j).ship_to_party_id
2798 );
2799 FND_LOG.String
2800 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_shipment_tbl.ship_to_party_site_id: ' || l_hd_shipment_tbl(j).ship_to_party_site_id
2801 );
2802 end if;
2803
2804 if l_line_tbl.count = 0 then
2805 FND_LOG.String
2806 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl does not exist'
2807 );
2808 else
2809 FND_LOG.String
2810 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.operation_code is: '|| l_line_tbl(j).operation_code
2811 );
2812 FND_LOG.String
2813 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.inventory_item_id is: ' || l_line_tbl(j).inventory_item_id
2814 );
2815 FND_LOG.String
2816 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.UOM_code is: ' || l_line_tbl(j).UOM_code
2817 );
2818 FND_LOG.String
2819 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.order_line_type_id is: ' || l_line_tbl(j).order_line_type_id
2820 );
2821 FND_LOG.String
2822 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.quantity is: ' || l_line_tbl(j).quantity
2823 );
2824 FND_LOG.String
2825 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.quote_line_id is: ' || l_line_tbl(j).quote_line_id
2826 );
2827 FND_LOG.String
2828 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.price_list_id is: ' || l_line_tbl(j).price_list_id
2829 );
2830 FND_LOG.String
2831 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.line_list_price is: ' || l_line_tbl(j).line_list_price
2832 );
2833 FND_LOG.String
2834 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.line_quote_price is: ' || l_line_tbl(j).line_quote_price
2835 );
2836 FND_LOG.String
2837 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.line_category_code is: ' || l_line_tbl(j).line_category_code
2838 );
2839 FND_LOG.String
2840 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.invoice_to_cust_account_id: ' || l_line_tbl(j).invoice_to_cust_account_id
2841 );
2842 FND_LOG.String
2843 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.invoice_to_party_id: ' || l_line_tbl(j).invoice_to_party_id
2844 );
2845 FND_LOG.String
2846 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.invoice_to_party_site_id: ' || l_line_tbl(j).invoice_to_party_site_id
2847 );
2848 end if;
2849
2850 if l_ln_shipment_tbl.count = 0 then
2851 FND_LOG.String
2852 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl does not exist'
2853 );
2854 else
2855 FND_LOG.String
2856 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.operation_code is: '|| l_ln_shipment_tbl(j).operation_code
2857 );
2858 FND_LOG.String
2859 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.ship_to_cust_account_id: ' || l_ln_shipment_tbl(j).ship_to_cust_account_id
2860 );
2861 FND_LOG.String
2862 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.ship_to_party_id: ' || l_ln_shipment_tbl(j).ship_to_party_id
2863 );
2864 FND_LOG.String
2865 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.ship_to_party_site_id: ' || l_ln_shipment_tbl(j).ship_to_party_site_id
2866 );
2867 FND_LOG.String
2868 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.quote_line_id is: ' || l_ln_shipment_tbl(j).quote_line_id
2869 );
2870 FND_LOG.String
2871 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.quote_header_id is: ' || l_ln_shipment_tbl(j).quote_header_id
2872 );
2873 FND_LOG.String
2874 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.quantity is: ' || l_ln_shipment_tbl(j).quantity
2875 );
2876 FND_LOG.String
2877 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.qte_line_index is: ' || l_ln_shipment_tbl(j).qte_line_index
2878 );
2879 end if;
2880
2881 if l_line_dtl_tbl.count = 0 then
2882 FND_LOG.String
2883 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_dtl_tbl does not exist'
2884 );
2885 else
2886 FND_LOG.String
2887 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_dtl_tbl.return_reason_code is: ' || l_line_dtl_tbl(j).return_reason_code
2888 );
2889 FND_LOG.String
2890 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_dtl_tbl.qte_line_index is: ' || l_line_dtl_tbl(j).qte_line_index
2891 );
2892 end if;
2893
2894 if l_line_price_adj_tbl.count = 0 then
2895 FND_LOG.String
2896 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl does not exist'
2897 );
2898 else
2899 FND_LOG.String
2900 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.operation_code is: ' || l_line_price_adj_tbl(j).operation_code
2901 );
2902 FND_LOG.String
2903 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.qte_line_index is: ' || l_line_price_adj_tbl(j).qte_line_index
2904 );
2905 FND_LOG.String
2906 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.modifier_header_id is: ' || l_line_price_adj_tbl(j).modifier_header_id
2907 );
2908 FND_LOG.String
2909 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.modifier_line_id is: ' || l_line_price_adj_tbl(j).modifier_line_id
2910 );
2911 FND_LOG.String
2912 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.operand is: ' || l_line_price_adj_tbl(j).operand
2913 );
2914 FND_LOG.String
2915 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.adjusted_amount is: ' || l_line_price_adj_tbl(j).adjusted_amount
2916 );
2917 FND_LOG.String
2918 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.updated_flag is: ' || l_line_price_adj_tbl(j).updated_flag
2919 );
2920 FND_LOG.String
2921 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.applied_flag is: ' || l_line_price_adj_tbl(j).applied_flag
2922 );
2923 FND_LOG.String
2924 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.change_reason_code is: ' || l_line_price_adj_tbl(j).change_reason_code
2925 );
2926 FND_LOG.String
2927 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.change_reason_text is: ' || l_line_price_adj_tbl(j).change_reason_text
2928 );
2929 end if;
2930
2931 if l_lot_serial_tbl.count = 0 then
2932 FND_LOG.String
2933 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl does not exist'
2934 );
2935 else
2936 FND_LOG.String
2937 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.lot_number is: ' || l_lot_serial_tbl(1).lot_number
2938 );
2939 FND_LOG.String
2940 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.lot_serial_id is: ' || l_lot_serial_tbl(1).lot_serial_id
2941 );
2942 FND_LOG.String
2943 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.quantity is: ' || l_lot_serial_tbl(1).quantity
2944 );
2945 FND_LOG.String
2946 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.from_serial_number is: ' || l_lot_serial_tbl(1).from_serial_number
2947 );
2948 FND_LOG.String
2949 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.to_serial_number is: ' || l_lot_serial_tbl(1).to_serial_number
2950 );
2951 FND_LOG.String
2952 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.operation is: ' || l_lot_serial_tbl(1).operation
2953 );
2954 FND_LOG.String
2955 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.line_index is: ' || l_lot_serial_tbl(1).line_index
2956 );
2957 end if;
2958
2959 FND_LOG.String
2960 ( FND_LOG.level_statement, L_LOG_MODULE , 'Ending Charges submission'
2961 );
2962 FND_LOG.String
2963 ( FND_LOG.level_statement, L_LOG_MODULE , '==================================================='
2964 );
2965
2966 END IF;
2967
2968
2969 -- Validate the ship to customer account site and account site use are correct.
2970 validate_acct_site_uses(
2971 p_org_id => EstDtlTab(i).org_id,
2972 p_party_site_id => EstDtlTab(i).ship_to_org_id,
2973 p_account_id => l_ship_to_cust_account_id,
2974 p_site_use_code => 'SHIP_TO',
2975 x_msg_data => x_msg_data,
2976 x_msg_count => x_msg_count,
2977 x_return_status => x_return_status);
2978
2979 -- dbms_output.put_line('In the validate_acct_site_call');
2980 -- dbms_output.put_line('Return_Status' || x_return_status);
2981
2982 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2983 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2984 p_count => x_msg_count,
2985 p_data => x_msg_data);
2986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2987 END IF;
2988
2989 -- Validate the bill to customer account site and account site use are correct.
2990 validate_acct_site_uses(
2991 p_org_id => EstDtlTab(i).org_id,
2992 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
2993 p_account_id => l_invoice_to_cust_account_id,
2994 p_site_use_code => 'BILL_TO',
2995 x_msg_data => x_msg_data,
2996 x_msg_count => x_msg_count,
2997 x_return_status => x_return_status);
2998
2999 -- dbms_output.put_line('In the validate_acct_site_call2');
3000 -- dbms_output.put_line('Return_Status2' || x_return_status);
3001
3002 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3003 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
3004 p_count => x_msg_count,
3005 p_data => x_msg_data);
3006 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3007 END IF;
3008 --
3009 -- bug 13826788
3010 --Pass the org_contact_id value
3011 lv_incident_id := EstDtlTab(i).incident_id;
3012
3013 open c_org_contact;
3014 fetch c_org_contact into l_org_contact_id;
3015 if c_org_contact%NOTFOUND THEN
3016 l_org_contact_id := null; ----bug 8473497
3017 end if;
3018 close c_org_contact;
3019
3020 l_header_rec.org_contact_id := l_org_contact_id;
3021
3022
3023 -- r12 code start
3024
3025 If fnd_profile.value('CS_SR_RESTRICT_OPERATING_UNIT')='Y' then -- Bug 10375246
3026
3027 IF (MO_GLOBAL.check_valid_org(l_org_id) ='N')
3028 THEN
3029 FND_MSG_PUB.initialize;
3030 FND_MESSAGE.Set_Name('CS','CS_CHG_NEW_CONTEXT_OU_MISMATCH') ;
3031 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full) ;
3032 FND_MSG_PUB.Add ;
3033 raise FND_API.G_EXC_ERROR ;
3034 END IF;
3035
3036 End if;
3037
3038 mo_global.set_policy_context('S',l_org_id);
3039
3040
3041
3042 -- end r12
3043 /* Credit Card 9358401 */
3044 IF EstDtlTab(i).instrument_payment_use_id is not null then
3045 BEGIN
3046 /* Credit Card 9358401 */
3047 l_ln_payment_tbl(j).qte_line_index :=1;
3048 l_ln_payment_tbl(j).operation_code :=OE_GLOBALS.G_OPR_CREATE;
3049 l_ln_payment_tbl(j).PAYMENT_TYPE_CODE :='CREDIT_CARD';
3050 SELECT INSTRUMENT_ID
3051 INTO l_ln_payment_tbl(j).instrument_id
3052 FROM iby_pmt_instr_uses_all
3053 WHERE INSTRUMENT_PAYMENT_USE_ID =
3054 EstDtlTab(i).instrument_payment_use_id;
3055
3056 /* commented for bug 14673342
3057 l_ln_payment_tbl(j).instr_assignment_id :=
3058 EstDtlTab(i).instrument_payment_use_id;*/
3059
3060 EXCEPTION
3061 WHEN OTHERS THEN
3062 /* Since instrument_payment_use_id is taken from the table there
3063 * should not be any excpetion. So no exception handler is required */
3064 NULL;
3065 END;
3066 END IF;
3067
3068 /* Added for bug 14673342*/
3069 IF EstDtlTab(i).sr_instr_payment_use_id is not null And
3070 nvl(fnd_profile.value('CS_CC_TO_OM_HEADER'),'N') = 'Y' then
3071 BEGIN
3072 l_hd_payment_tbl(j).qte_line_index :=1;
3073 IF (EstDtlTab(i).add_to_order_flag = 'Y') or
3074 (l_order_header_id <> -999) THEN
3075 l_hd_payment_tbl(j).operation_code :=OE_GLOBALS.G_OPR_UPDATE;
3076 ELSE
3077 l_hd_payment_tbl(j).operation_code :=OE_GLOBALS.G_OPR_CREATE;
3078 END IF;
3079 l_hd_payment_tbl(j).PAYMENT_TYPE_CODE :='CREDIT_CARD';
3080 l_hd_payment_tbl(j).PAYMENT_REF_NUMBER :=1;
3081
3082 BEGIN
3083 SELECT payment_number
3084 INTO l_hd_payment_tbl(j).PAYMENT_REF_NUMBER
3085 FROM oe_payments
3086 WHERE header_id =l_order_header_id
3087 AND payment_level_code ='ORDER'
3088 AND payment_type_code ='CREDIT_CARD'
3089 AND PAYMENT_COLLECTION_EVENT='INVOICE';
3090 EXCEPTION
3091 WHEN NO_DATA_FOUND THEN
3092 l_hd_payment_tbl(j).PAYMENT_REF_NUMBER :=1;
3093 l_hd_payment_tbl(j).operation_code :=OE_GLOBALS.G_OPR_CREATE;
3094 WHEN Others THEN
3095 raise;
3096 END;
3097
3098 SELECT INSTRUMENT_ID
3099 INTO l_hd_payment_tbl(j).instrument_id
3100 FROM iby_pmt_instr_uses_all
3101 WHERE INSTRUMENT_PAYMENT_USE_ID =
3102 EstDtlTab(i).sr_instr_payment_use_id;
3103
3104 l_hd_payment_tbl(j).instr_assignment_id :=
3105 EstDtlTab(i).sr_instr_payment_use_id;
3106
3107 EXCEPTION
3108 WHEN OTHERS THEN
3109 /* Since instrument_payment_use_id is taken from the table there
3110 should not be any excpetion. So no exception handler is required
3111 */
3112 NULL;
3113 END;
3114 ELSIF EstDtlTab(i).sr_instr_payment_use_id is null And
3115 nvl(fnd_profile.value('CS_CC_TO_OM_HEADER'),'N') = 'Y' then
3116 BEGIN
3117 SELECT payment_number
3118 INTO l_hd_payment_tbl(j).PAYMENT_REF_NUMBER
3119 FROM oe_payments
3120 WHERE header_id =l_order_header_id
3121 AND payment_level_code ='ORDER'
3122 AND payment_type_code ='CREDIT_CARD'
3123 AND PAYMENT_COLLECTION_EVENT='INVOICE';
3124
3125 l_hd_payment_tbl(j).qte_line_index :=1;
3126 l_hd_payment_tbl(j).PAYMENT_TYPE_CODE := 'CREDIT_CARD';
3127 l_hd_payment_tbl(j).operation_code :=OE_GLOBALS.G_OPR_DELETE;
3128 l_hd_payment_tbl(j).instr_assignment_id := NULL;
3129 EXCEPTION
3130 WHEN NO_DATA_FOUND THEN
3131 NULL;
3132 WHEN Others THEN
3133 raise;
3134 END;
3135
3136 END IF;
3137 /* End bug 14673342*/
3138
3139 -- CALL OC'S CREATE_ORDER API FOR CREATING A NEW ORDER
3140 -- UPDATE ORDER API FOR ADDING TO AN EXISTING ORDER
3141
3142 IF (EstDtlTab(i).add_to_order_flag = 'F') THEN
3143
3144 /* Start bug 11839090, vpremach */
3145 IF jtf_usr_hks.Ok_To_Execute('CS_CHARGE_DETAILS_PVT','Create_Order','B', 'C') THEN
3146 CS_CHARGE_DETAILS_CUHK.Create_order(
3147 P_Api_Version_Number => 1.0,
3148 l_header_rec => l_header_rec,
3149 l_hd_shipment_tbl => l_hd_shipment_tbl,
3150 l_line_tbl => l_line_tbl,
3151 l_line_dtl_tbl => l_line_dtl_tbl,
3152 l_ln_shipment_tbl => l_ln_shipment_tbl,
3153 l_hd_payment_tbl => l_hd_payment_tbl,
3154 l_ln_payment_tbl => l_ln_payment_tbl,
3155 l_line_price_adj_tbl => l_line_price_adj_tbl,
3156 l_lot_serial_tbl => l_lot_serial_tbl,
3157 l_control_rec => l_control_rec,
3158 X_Return_Status => x_return_status,
3159 X_Msg_Count => x_msg_count,
3160 X_Msg_Data => x_msg_data
3161 );
3162 END IF;
3163 /* End bug 11839090, vpremach */
3164
3165 ASO_ORDER_INT.Create_order(
3166 P_Api_Version_Number => 1.0,
3167 P_Qte_Rec => l_header_rec,
3168 P_Header_Shipment_Tbl => l_hd_shipment_tbl,
3169 P_Qte_Line_Tbl => l_line_tbl,
3170 P_Qte_Line_Dtl_Tbl => l_line_dtl_tbl,
3171 P_Line_Shipment_Tbl => l_ln_shipment_tbl,
3172 P_Header_Payment_Tbl => l_hd_payment_tbl,
3173 P_Line_Payment_Tbl => l_ln_payment_tbl,/* Credit Card 9358401 */
3174 P_Line_Price_Adj_Tbl => l_line_price_adj_tbl,
3175 P_Lot_Serial_Tbl => l_lot_serial_tbl,
3176 P_Control_Rec => l_control_rec,
3177 X_Order_Header_Rec => x_order_header_rec,
3178 X_Order_Line_Tbl => x_order_line_tbl,
3179 X_Return_Status => x_return_status,
3180 X_Msg_Count => x_msg_count,
3181 X_Msg_Data => x_msg_data
3182 );
3183
3184 ELSIF (EstDtlTab(i).add_to_order_flag = 'Y') or
3185 (l_order_header_id <> -999) THEN
3186
3187 /* Start bug 11839090, vpremach */
3188 IF jtf_usr_hks.Ok_To_Execute('CS_CHARGE_DETAILS_PVT','Update_Order','B', 'C') THEN
3189 CS_CHARGE_DETAILS_CUHK.Update_Order(
3190 P_Api_Version_Number => 1.0,
3191 l_header_rec => l_header_rec,
3192 l_line_tbl => l_line_tbl,
3193 l_line_dtl_tbl => l_line_dtl_tbl,
3194 l_ln_shipment_tbl => l_ln_shipment_tbl,
3195 l_hd_payment_tbl => l_hd_payment_tbl,
3196 l_ln_payment_tbl => l_ln_payment_tbl,
3197 l_line_price_adj_tbl => l_line_price_adj_tbl,
3198 l_lot_serial_tbl => l_lot_serial_tbl,
3199 l_control_rec => l_control_rec,
3200 X_Order_Header_Rec => x_order_header_rec,
3201 X_Order_Line_Tbl => x_order_line_tbl,
3202 X_Return_Status => x_return_status,
3203 X_Msg_Count => x_msg_count,
3204 X_Msg_Data => x_msg_data
3205 );
3206 End If;
3207 /* End bug 11839090, vpremach */
3208
3209 -- dbms_output.put_line('Calling Update_Order');
3210
3211 ASO_ORDER_INT.Update_order(
3212 P_Api_Version_Number => 1.0,
3213 P_Qte_Rec => l_header_rec,
3214 P_Qte_Line_Tbl => l_line_tbl,
3215 P_Qte_Line_Dtl_Tbl => l_line_dtl_tbl,
3216 P_Line_Shipment_Tbl => l_ln_shipment_tbl,
3217 P_Header_Payment_Tbl => l_hd_payment_tbl,
3218 P_Line_Payment_Tbl => l_ln_payment_tbl,/* Credit Card 9358401 */
3219 P_Line_Price_Adj_Tbl => l_line_price_adj_tbl,
3220 P_Lot_Serial_Tbl => l_lot_serial_tbl,
3221 P_Control_Rec => l_control_rec,
3222 X_Order_Header_Rec => x_order_header_rec,
3223 X_Order_Line_Tbl => x_order_line_tbl,
3224 X_Return_Status => x_return_status,
3225 X_Msg_Count => x_msg_count,
3226 X_Msg_Data => x_msg_data
3227 );
3228 ELSE
3229
3230 /* Start bug 11839090, vpremach */
3231 IF jtf_usr_hks.Ok_To_Execute('CS_CHARGE_DETAILS_PVT','Create_Order','B', 'C') THEN
3232 CS_CHARGE_DETAILS_CUHK.Create_order(
3233 P_Api_Version_Number => 1.0,
3234 l_header_rec => l_header_rec,
3235 l_hd_shipment_tbl => l_hd_shipment_tbl,
3236 l_line_tbl => l_line_tbl,
3237 l_line_dtl_tbl => l_line_dtl_tbl,
3238 l_ln_shipment_tbl => l_ln_shipment_tbl,
3239 l_hd_payment_tbl => l_hd_payment_tbl,
3240 l_ln_payment_tbl => l_ln_payment_tbl,
3241 l_line_price_adj_tbl => l_line_price_adj_tbl,
3242 l_lot_serial_tbl => l_lot_serial_tbl,
3243 l_control_rec => l_control_rec,
3244 X_Return_Status => x_return_status,
3245 X_Msg_Count => x_msg_count,
3246 X_Msg_Data => x_msg_data
3247 );
3248 END IF;
3249 /* End bug 11839090, vpremach */
3250
3251 -- dbms_output.put_line('Calling Create_Order');
3252
3253 ASO_ORDER_INT.Create_order(
3254 P_Api_Version_Number => 1.0,
3255 P_Qte_Rec => l_header_rec,
3256 P_Header_Shipment_Tbl => l_hd_shipment_tbl,
3257 P_Qte_Line_Tbl => l_line_tbl,
3258 P_Qte_Line_Dtl_Tbl => l_line_dtl_tbl,
3259 P_Line_Shipment_Tbl => l_ln_shipment_tbl,
3260 P_Header_Payment_Tbl => l_hd_payment_tbl,
3261 P_Line_Payment_Tbl => l_ln_payment_tbl,/* Credit Card 9358401 */
3262 P_Line_Price_Adj_Tbl => l_line_price_adj_tbl,
3263 P_Lot_Serial_Tbl => l_lot_serial_tbl,
3264 P_Control_Rec => l_control_rec,
3265 X_Order_Header_Rec => x_order_header_rec,
3266 X_Order_Line_Tbl => x_order_line_tbl,
3267 X_Return_Status => x_return_status,
3268 X_Msg_Count => x_msg_count,
3269 X_Msg_Data => x_msg_data
3270 );
3271
3272 END IF; -- Add_To_Order
3273
3274
3275 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3276 -- moved this to elsif below.
3277 -- X_ORDER_HEADER_ID := X_ORDER_HEADER_REC.ORDER_HEADER_ID;
3278
3279 IF X_ORDER_HEADER_REC.ORDER_HEADER_ID IS NULL THEN
3280 FND_MESSAGE.Set_Name('CS','CS_CHG_NO_ORD_NUM_RETURNED');
3281 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
3282 FND_MSG_PUB.Add;
3283 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
3284 p_count => x_msg_count,
3285 p_data => x_msg_data);
3286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3287
3288 -- Need to verify if we need to trap this error message
3289 --
3290
3291 ELSIF X_ORDER_HEADER_REC.ORDER_HEADER_ID IS NOT NULL THEN
3292 X_ORDER_HEADER_ID := X_ORDER_HEADER_REC.ORDER_HEADER_ID;
3293 END IF;
3294 -- Moving this to after IB call so that the record is not locked
3295 -- fix bug:3545283
3296 /* FOR k in 1..x_order_line_tbl.count LOOP
3297
3298 -- UPDATE ESTIMATE_DETAILS WITH ORDER_HEADER_ID AND ORDER_LINE_ID.
3299 -- Changes for 11.5.10.
3300 -- NULL Values should be passed for submit_error_message,
3301 -- submit_restriction_message and line_submitted columns as the
3302 -- order creation is successful.
3303
3304 Update_Estimate_Details(EstDtlTab(i).estimate_detail_id,
3305 x_order_header_id,
3306 x_order_line_tbl(k).order_line_id,
3307 NULL,
3308 NULL,
3309 NULL,
3310 p_submit_from_system);
3311
3312
3313 END LOOP; */
3314
3315 -- Initialize xi_return_status.
3316
3317 xi_return_status := FND_API.G_RET_STS_SUCCESS;
3318
3319 /* This will call the wrapper API to raise the Business Event oracle.apps.cs.chg.Charges.submitted
3320 and their subscriptions.
3321 All the custom code exists in the subscriptions/rule functions
3322 created by customers, which should be subscribed to this charges event in
3323 order to execute their code.
3324 Presently we are not shipping any seeded subscriptions.
3325 Some parameters were initialized to NULL , which will be changed later.
3326 As there are no workflows attached to this, l_workflow_process_id is not required for
3327 processing.
3328 The mandatory parameters are event code(SUBMIT_CHARGES) and
3329 event key(estimate_detail_id). l_charges_rec_type can utilized for future modifications*/
3330
3331 l_charges_rec_type.incident_id := EstDtlTab(i).incident_id;
3332 l_charges_rec_type.org_id := EstDtlTab(i).org_id;
3333 wf_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
3334 wf_resp_id := FND_GLOBAL.RESP_ID;
3335 wf_user_id := FND_GLOBAL.USER_ID;
3336
3337 CS_CHG_WF_EVENT_PKG.RAISE_SUBMITCHARGES_EVENT(
3338 p_api_version => 1.0,
3339 p_init_msg_list => FND_API.G_FALSE,
3340 p_commit => FND_API.G_FALSE,
3341 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3342 p_event_code => 'SUBMIT_CHARGES',
3343 p_estimate_detail_id => EstDtlTab(i).Estimate_Detail_Id,
3344 p_USER_ID => wf_user_id,
3345 p_RESP_ID => wf_resp_id,
3346 p_RESP_APPL_ID => wf_resp_appl_id,
3347 p_est_detail_rec => l_charges_rec_type,
3348 p_wf_process_id => NULL,
3349 p_owner_id => NULL,
3350 p_wf_manual_launch => 'N' ,
3351 x_wf_process_id => l_workflow_process_id,
3352 x_return_status => lx_return_status,
3353 x_msg_count => lx_msg_count,
3354 x_msg_data => lx_msg_data );
3355
3356 if ( lx_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3357 -- do nothing in this API. The BES wrapper API will have to trap this
3358 -- situation.
3359 null;
3360 end if;
3361
3362 -- Installbase Call if the Order creation is successful
3363 --
3364 --IF EstDtlTab(i).billing_type = 'M' THEN
3365 IF EstDtlTab(i).billing_category = 'M' THEN
3366 -- 11.5.6 Installation details call
3367 -- Bug fix:3564034
3368 -- Create installtion details if an instance is available and update_ib_flag = 'Y'
3369 --
3370 IF (EstDtlTab(i).comms_nl_trackable_flag = 'Y')
3371 -- commented for the bug:3800010
3372 -- and (EstDtlTab(i).customer_product_id IS NOT NULL)
3373 and l_update_ib_flag = 'Y' THEN
3374
3375 -- assign order line id to source transaction id.
3376 csi_txn_line_rec.source_transaction_id := x_order_line_tbl(1).order_line_id;
3377
3378 --Checking for account_id.If account_id is null then we need to derive one
3379 --created by OC.
3380 IF EstDtlTab(i).account_id is null then
3381 BEGIN
3382 SELECT cust_account_id
3383 INTO l_account_id
3384 FROM hz_cust_accounts_all
3385 WHERE party_id = p_party_id
3386 AND status = 'A';
3387 EXCEPTION
3388 WHEN NO_DATA_FOUND THEN
3389 FND_MESSAGE.Set_Name('CS','CS_CHG_NO_ACCT_CREATED');
3390 FND_MSG_PUB.Add;
3391
3392 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
3393 p_count => x_msg_count,
3394 p_data => x_msg_data);
3395 END;
3396 --
3397 -- Create IB Party details and account details records
3398 --
3399 IF EstDtlTab(i).line_category_code = 'RETURN' then
3400 IF l_src_change_owner = 'Y' THEN
3401 IF l_src_change_owner_to_code = 'I' THEN
3402
3403 BEGIN
3404 SELECT internal_party_id
3405 INTO l_internal_party_id
3406 FROM csi_install_parameters
3407 WHERE rownum = 1;
3408
3409 IF l_internal_party_id is null THEN
3410 RAISE l_IB_ERROR;
3411 END IF;
3412 EXCEPTION
3413 WHEN OTHERS THEN
3414 FND_MESSAGE.Set_Name('CS','CS_CHG_INTERNAL_PARTY_NOT_DEF');
3415 FND_MSG_PUB.Add;
3416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3417 END;
3418
3419 SELECT instance_party_id
3420 INTO l_instance_party_id
3421 FROM CSI_I_PARTIES
3422 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
3423 AND relationship_type_code = 'OWNER'
3424 AND party_id = (SELECT party_id
3425 FROM hz_cust_accounts
3426 WHERE cust_account_id = l_account_id);
3427
3428 csi_txn_party_detail_tbl(1).instance_party_id := l_instance_party_id;
3429 csi_txn_party_detail_tbl(1).party_source_table := 'HZ_PARTIES';
3430 csi_txn_party_detail_tbl(1).party_source_id := l_internal_party_id;
3431 csi_txn_party_detail_tbl(1).relationship_type_code := 'OWNER';
3432 csi_txn_party_detail_tbl(1).txn_line_details_index := 1;
3433 csi_txn_party_detail_tbl(1).contact_flag := 'N';
3434 csi_txn_pty_acct_detail_tbl.delete;
3435 ELSE
3436 FND_MESSAGE.Set_Name('CS','CS_CHG_RTN_TO_EXT_PTY_NOT_SUP');
3437 FND_MSG_PUB.Add;
3438 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
3439 p_count => x_msg_count,
3440 p_data => x_msg_data);
3441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3442
3443 END IF; -- IF l_src_change_owner_to_code = 'I'
3444 ELSE
3445 csi_txn_party_detail_tbl.delete;
3446 csi_txn_pty_acct_detail_tbl.delete;
3447 END IF; -- IF l_src_change_owner = 'Y'
3448
3449 ELSE -- shipment line
3450 IF l_src_change_owner = 'Y' THEN
3451 -- Create Party record
3452 csi_txn_party_detail_tbl(1).party_source_table := 'HZ_PARTIES';
3453 csi_txn_party_detail_tbl(1).party_source_id := EstDtlTab(i).customer_id;
3454 csi_txn_party_detail_tbl(1).relationship_type_code := 'OWNER';
3455 csi_txn_party_detail_tbl(1).txn_line_details_index := 1;
3456 csi_txn_party_detail_tbl(1).contact_flag := 'N';
3457
3458 -- Create Account record
3459 csi_txn_pty_acct_detail_tbl(1).account_id := l_account_id;
3460 csi_txn_pty_acct_detail_tbl(1).relationship_type_code := 'OWNER';
3461 csi_txn_pty_acct_detail_tbl(1).txn_party_details_index := 1;
3462 csi_txn_pty_acct_detail_tbl(1).active_start_date := sysdate;
3463
3464 END IF;
3465
3466 END IF; -- IF EstDtlTab(i).line_category_code = 'RETURN'
3467
3468 END IF; -- EstDtlTab.account_id is null
3469
3470 -- Bug 9312433
3471 Open c_sub_type(l_sub_type_id);
3472 Fetch c_sub_type into l_src_ref_reqd;
3473 close c_sub_type;
3474 --
3475 --
3476 -- fix for bug:3800010
3477 IF EstDtlTab(i).line_category_code = 'RETURN' and
3478 (nvl(l_src_ref_reqd,'N') = 'N' OR (l_src_ref_reqd = 'Y' AND EstDtlTab(i).customer_product_id IS NOT NULL)) THEN
3479 --and (EstDtlTab(i).customer_product_id IS NOT NULL) THEN -- commented for bug 9312433
3480 -- Now call the create transaction Details API.
3481 csi_t_txn_details_grp.create_transaction_dtls(
3482 p_api_version => 1.0,
3483 p_commit => fnd_api.g_false,
3484 p_init_msg_list => fnd_api.g_false,
3485 p_validation_level => fnd_api.g_valid_level_full,
3486 px_txn_line_rec => csi_txn_line_rec ,
3487 px_txn_line_detail_tbl => csi_txn_line_detail_tbl,
3488 px_txn_party_detail_tbl => csi_txn_party_detail_tbl ,
3489 px_txn_pty_acct_detail_tbl => csi_txn_pty_acct_detail_tbl,
3490 px_txn_ii_rltns_tbl => csi_txn_ii_rltns_tbl,
3491 px_txn_org_assgn_tbl => csi_txn_org_assgn_tbl,
3492 px_txn_ext_attrib_vals_tbl => csi_txn_ext_attrib_vals_tbl,
3493 px_txn_systems_tbl => csi_txn_systems_tbl,
3494 x_return_status => x_return_status,
3495 x_msg_count => x_msg_count,
3496 x_msg_data => x_msg_data);
3497
3498 ELSIF EstDtlTab(i).line_category_code = 'ORDER' THEN
3499 -- Now call the create transaction Details API.
3500 csi_t_txn_details_grp.create_transaction_dtls(
3501 p_api_version => 1.0,
3502 p_commit => fnd_api.g_false,
3503 p_init_msg_list => fnd_api.g_false,
3504 p_validation_level => fnd_api.g_valid_level_full,
3505 px_txn_line_rec => csi_txn_line_rec ,
3506 px_txn_line_detail_tbl => csi_txn_line_detail_tbl,
3507 px_txn_party_detail_tbl => csi_txn_party_detail_tbl ,
3508 px_txn_pty_acct_detail_tbl => csi_txn_pty_acct_detail_tbl,
3509 px_txn_ii_rltns_tbl => csi_txn_ii_rltns_tbl,
3510 px_txn_org_assgn_tbl => csi_txn_org_assgn_tbl,
3511 px_txn_ext_attrib_vals_tbl => csi_txn_ext_attrib_vals_tbl,
3512 px_txn_systems_tbl => csi_txn_systems_tbl,
3513 x_return_status => x_return_status,
3514 x_msg_count => x_msg_count,
3515 x_msg_data => x_msg_data);
3516
3517 END IF; -- fix bug:3800010
3518
3519 END IF; -- comms_nl_trackable_flag
3520
3521 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3522
3523 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
3524 p_count => x_msg_count,
3525 p_data => x_msg_data);
3526
3527 -- dbms_output.put_line('message status' || x_return_status);
3528 -- dbms_output.put_line('message_data' || substr(x_msg_data,1,200));
3529 -- dbms_output.put_line('message_count' || x_msg_count);
3530
3531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3532
3533 END IF;
3534
3535 -- FND_MESSAGE.Set_Encoded(xi_msg_data);
3536 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3537
3538
3539 END IF; -- if billing flag = 'M'
3540
3541 /* Bug 14673342*/
3542
3543 l_found_flag := 0;
3544 IF (x_order_header_id is NOT NULL
3545 AND nvl(fnd_profile.value('CS_UPD_CC_OPEN_ORDER'),'UPDATE_ORDER')
3546 = 'CREATE_ORDER'
3547 AND nvl(fnd_profile.value('CS_CC_TO_OM_HEADER'),'N') = 'Y') THEN
3548 FOR l_tempCount in 1..l_order_num_count loop
3549 IF l_order_number(l_tempCount) = l_order_header_id then
3550 l_found_flag := 1;
3551 EXIT;
3552 END IF;
3553 END LOOP;
3554 IF (l_found_flag =0) THEN
3555 l_order_num_count := l_order_num_count +1;
3556 l_order_number(l_order_num_count) := x_order_header_id;
3557 END IF;
3558 END IF;
3559 /* End of Bug 14673342*/
3560
3561 -- Update charge lines with order details
3562 -- Fix for bug:3545283
3563
3564 FOR k in 1..x_order_line_tbl.count LOOP
3565
3566 -- UPDATE ESTIMATE_DETAILS WITH ORDER_HEADER_ID AND ORDER_LINE_ID.
3567 -- Changes for 11.5.10.
3568 -- NULL Values should be passed for submit_error_message,
3569 -- submit_restriction_message and line_submitted columns as the
3570 -- order creation is successful.
3571
3572 Update_Estimate_Details(EstDtlTab(i).estimate_detail_id,
3573 x_order_header_id,
3574 x_order_line_tbl(k).order_line_id,
3575 NULL,
3576 NULL,
3577 NULL,
3578 p_submit_from_system);
3579
3580
3581 END LOOP;
3582 --
3583 -- IF THE ORDER CREATION IS NOT SUCCESSFUL
3584 ELSIF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3585
3586 IF x_msg_count > 0 THEN
3587 -- Get next message
3588
3589 -- FND_MSG_PUB.initialize;
3590 /* FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
3591 p_count => x_msg_count,
3592 p_data => x_msg_data); */
3593
3594 for k in 1..x_msg_count loop
3595 FND_MSG_PUB.get(p_encoded => 'F',
3596 p_data=>x_msg_data,
3597 p_msg_index_out=>l_dummy);
3598 end loop;
3599
3600 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3601
3602 /* for k in 1..x_msg_count loop
3603 FND_MSG_PUB.get(p_encoded => 'F',
3604 p_data=>x_msg_data,
3605 p_msg_index_out=>l_dummy);
3606
3607 -- changes for 11.5.10
3608 msg_table(k) := x_msg_data;
3609 temp_tab := (temp_tab || msg_table(k));
3610
3611
3612 end loop; */
3613 --
3614 -- Changes for 11.5.10
3615 -- Recording error messages in the Charges schema as an autonomous
3616 -- transaction.
3617 /* Update_Errors(p_estimate_detail_id => EstDtlTab(i).estimate_detail_id,
3618 p_line_submitted => 'N',
3619 p_submit_restriction_message => NULL,
3620 p_submit_error_message => temp_tab,
3621 p_submit_from_system => p_submit_from_system); */
3622
3623 END IF; -- end of message count
3624
3625 IF x_msg_count = 0 THEN
3626 -- dbms_output.put_line('Before set_name');
3627 FND_MESSAGE.Set_Name('CS','CS_CHG_OM_ERR_WITH_NO_MSG');
3628 FND_MSG_PUB.Add;
3629 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
3630 p_count => x_msg_count,
3631 p_data => x_msg_data);
3632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3633
3634 END IF;
3635
3636 -- FND_MESSAGE.Set_Encoded(x_msg_data);
3637 -- FND_MSG_PUB.Add;
3638 -- RAISE FND_API.G_EXC_ERROR;
3639
3640 END IF;
3641
3642 END LOOP;
3643 Close Fetch_Est_Dtl; -- Bug 7632716
3644
3645
3646 -- If no records are been submitted to OM, return a message.
3647 IF l_record_found = 'N' THEN
3648 FND_MESSAGE.Set_Name('CS','CS_CHG_NO_CHARGES_SUBMITTED');
3649 --FND_MESSAGE.SET_TOKEN('INCIDENT_ID', p_incident_id, TRUE);
3650 --FND_MESSAGE.SET_TOKEN('PARTY_ID', p_party_id, TRUE);
3651 --FND_MESSAGE.SET_TOKEN('ACCOUNT_ID', p_account_id, TRUE);
3652 FND_MSG_PUB.Add;
3653 RAISE FND_API.G_EXC_ERROR;
3654 END IF;
3655
3656 --
3657 -- End of API body
3658 --
3659
3660 -- Standard check of p_commit.
3661 IF FND_API.To_Boolean( p_commit ) THEN
3662 COMMIT WORK;
3663 END IF;
3664
3665 -- Standard call to get message count and if count is 1, get message info.
3666 FND_MSG_PUB.Count_And_Get
3667 ( p_count => x_msg_count,
3668 p_data => x_msg_data
3669 );
3670
3671
3672 EXCEPTION
3673 -- This exception is for SR level
3674 --
3675 --7117301
3676 WHEN RESOURCE_BUSY THEN
3677 FND_MESSAGE.Set_Name('CS','CS_CH_SUBMIT_IN_PROCESS');
3678 FND_MSG_PUB.Add;
3679 RAISE FND_API.G_EXC_ERROR;
3680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3681 --7117301
3682 WHEN FND_API.G_EXC_ERROR THEN
3683 ROLLBACK TO CS_Charge_Create_Order_PVT;
3684 x_return_status := FND_API.G_RET_STS_ERROR;
3685 FND_MSG_PUB.Count_And_Get
3686 ( p_count => x_msg_count,
3687 p_data => x_msg_data
3688 );
3689
3690 --
3691 -- This exception is for Charge Line Level
3692
3693 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3694
3695 -- updating charges schema with errors for 11.5.10
3696 --
3697 -- dbms_output.put_line('Charge_Line_Id before calling update_errors' || EstDtlTab(i).estimate_detail_id);
3698 -- dbms_output.put_line('message data before calling update_errors' || substr(x_msg_data,1,200));
3699 ROLLBACK TO CS_Charge_Create_Order_PVT; --7117301
3700 Update_Errors(p_estimate_detail_id => EstDtlTab(i).estimate_detail_id,
3701 p_line_submitted => 'N',
3702 p_submit_restriction_message => NULL,
3703 p_submit_error_message => x_msg_data,
3704 p_submit_from_system => p_submit_from_system);
3705
3706 FND_MESSAGE.Set_Encoded(x_msg_data);
3707 --ROLLBACK TO CS_Charge_Create_Order_PVT; --7117301
3708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3709
3710 WHEN OTHERS THEN
3711 g_oraerrmsg := substrb(sqlerrm,1,G_MAXERRLEN);
3712 fnd_message.set_name('CS','CS_CHG_SUBMIT_ORDER_FAILED');
3713 fnd_message.set_token('ROUTINE',l_api_name_full);
3714 fnd_message.set_token('REASON',g_oraerrmsg);
3715 FND_MSG_PUB.Add;
3716 IF FND_MSG_PUB.Check_Msg_Level
3717 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3718 THEN
3719 FND_MSG_PUB.Add_Exc_Msg
3720 ( G_PKG_NAME,
3721 l_api_name
3722 );
3723 END IF;
3724
3725 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
3726 p_count => x_msg_count,
3727 p_data => x_msg_data);
3728
3729 --
3730 -- Adding the error message to charges schema for 11.5.10
3731 --
3732 ROLLBACK TO CS_Charge_Create_Order_PVT; --7117301
3733 Update_Errors(p_estimate_detail_id => EstDtlTab(i).estimate_detail_id,
3734 p_line_submitted => 'N',
3735 p_submit_restriction_message => NULL,
3736 p_submit_error_message => x_msg_data,
3737 p_submit_from_system => p_submit_from_system);
3738
3739 FND_MESSAGE.Set_Encoded(x_msg_data);
3740
3741
3742 --ROLLBACK TO CS_Charge_Create_Order_PVT; --7117301
3743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3744
3745 END Submit_Order;
3746
3747 /***************************************************************************/
3748 -- Update Charges table.
3749 /***************************************************************************/
3750 PROCEDURE Update_Estimate_Details (
3751 p_Estimate_Detail_Id IN NUMBER,
3752 p_order_header_Id IN NUMBER,
3753 p_order_line_Id IN NUMBER,
3754 p_line_submitted IN VARCHAR2,
3755 p_submit_restriction_message IN VARCHAR2,-- new
3756 p_submit_error_message IN VARCHAR2,-- new
3757 p_submit_from_system IN VARCHAR2 -- new
3758 ) IS
3759
3760 l_api_name CONSTANT VARCHAR2(30) := 'Update_Estimate_Details' ;
3761 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
3762 l_log_module CONSTANT VARCHAR2(255) := 'cs.plsql.' || l_api_name_full || '.';
3763
3764 BEGIN
3765
3766 ----------------------- FND Logging -----------------------------------
3767 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3768 THEN
3769 FND_LOG.String
3770 ( FND_LOG.level_procedure , L_LOG_MODULE || 'start'
3771 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
3772 );
3773 FND_LOG.String
3774 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3775 , 'p_Estimate_Detail_Id:' || p_Estimate_Detail_Id
3776 );
3777 FND_LOG.String
3778 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3779 , 'p_order_header_Id:' || p_order_header_Id
3780 );
3781 FND_LOG.String
3782 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3783 , 'p_order_line_Id:' || p_order_line_Id
3784 );
3785 FND_LOG.String
3786 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3787 , 'p_line_submitted:' || p_line_submitted
3788 );
3789 FND_LOG.String
3790 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3791 , 'p_submit_restriction_message:' || p_submit_restriction_message
3792 );
3793 FND_LOG.String
3794 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3795 , 'p_submit_error_message:' || p_submit_error_message
3796 );
3797 FND_LOG.String
3798 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3799 , 'p_submit_from_system:' || p_submit_from_system
3800 );
3801 END IF;
3802
3803 UPDATE CS_ESTIMATE_DETAILS
3804 SET Order_Header_Id = p_order_header_Id,
3805 Order_Line_Id = p_order_line_Id,
3806 line_submitted = p_line_submitted,
3807 submit_restriction_message = p_submit_restriction_message,
3808 submit_error_message = p_submit_error_message,
3809 submit_from_system = p_submit_from_system,
3810 last_update_date = sysdate,
3811 last_update_login = fnd_global.login_id, --6027992
3812 last_updated_by = fnd_global.user_id --6027992
3813 WHERE Estimate_Detail_Id = p_estimate_detail_id;
3814
3815 EXCEPTION
3816 WHEN NO_DATA_FOUND THEN
3817 Raise No_Data_Found;
3818
3819 end Update_Estimate_Details;
3820
3821 End CS_Charge_Create_Order_PVT;