[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.28.12010000.4 2008/11/14 12:07:05 shachoud 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 --srini
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 --srini
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
368 WHERE Estimate_Detail_Id = p_estimate_detail_id;
369
370 -- dbms_output.put_line('submit_error_message' || substr(p_submit_error_message,1,100));
371
372
373 END IF;
374
375 commit;
376 -- dbms_output.put_line('In the Update_Errors');
377
378 EXCEPTION
379 -- WHEN DEADLOCK_DETECTED THEN
380 -- dbms_output.put_line('dead lock detected');
381 -- ROLLBACK;
382 -- NULL;
383
384 WHEN OTHERS THEN
385 ROLLBACK;
386 FND_MESSAGE.SET_NAME('CS', 'CS_DB_ERROR');
387 FND_MESSAGE.SET_TOKEN(token => 'PROG_NAME', value => 'Cs_Charge_Create_Order_PVT.Update_Errors');
388 FND_MESSAGE.SET_TOKEN(token => 'SQLCODE', value => SQLCODE);
389 FND_MESSAGE.SET_TOKEN(token => 'SQLERRM', value => SQLERRM);
390 FND_MSG_PUB.add;
391
392 END Update_Errors;
393 --
394 -- End of Update_Errors
395 --
396 --
397 -- *******************************************************
398 -- Start of Comments
399 -- *******************************************************
400 -- API Name: Submit_Order
401 -- Type : Public
402 -- Purpose : This API is for submitting an order.
403 -- It is intended for use by the owning module only; contrast to published API.
404 -- Pre-Req :
405 -- Parameters:
406 -- IN
407 -- p_api_version IN NUMBER Required
408 -- p_init_msg_list IN VARCHAR2 Optional
409 -- p_commit IN VARCHAR2 Optional
410 -- p_validation_level IN NUMBER Optional
411 -- p_incident_id IN NUMBER Required
412 -- p_party_id IN NUMBER Required
413 -- p_account_id IN NUMBER Optional see bug#2447927,
414 -- changed p_account_id to optional param.
415 -- p_book_order_flag IN VARCHAR2 Optional
416 -- p_submit_source IN VARCHAR2 Optional
417 -- p_submit_from_system IN VARCHAR2 Optional
418 -- p_book_order_flag IN VARCHAR2 Optional
419 -- OUT:
420 -- x_return_status OUT NOCOPY VARCHAR2
421 -- x_msg_count OUT NOCOPY NUMBER
422 -- x_msg_data OUT NOCOPY VARCHAR2
423 -- Version : Current version 1.0
424 -- End of Comments
425 --
426 PROCEDURE Submit_Order(
427 p_api_version IN NUMBER,
428 p_init_msg_list IN VARCHAR2,
429 p_commit IN VARCHAR2,
430 p_validation_level IN NUMBER,
431 p_incident_id IN NUMBER,
432 p_party_id IN NUMBER,
433 p_account_id IN NUMBER,
434 p_book_order_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
435 p_submit_source IN VARCHAR2 := FND_API.G_MISS_CHAR,
436 p_submit_from_system IN VARCHAR2 := FND_API.G_MISS_CHAR,
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2
440 )
441 IS
442 l_api_name CONSTANT VARCHAR2(30) := 'Submit_Order' ;
443 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
444 l_log_module CONSTANT VARCHAR2(255) := 'cs.plsql.' || l_api_name_full || '.';
445 l_api_version CONSTANT NUMBER := 1.0 ;
446
447 -- l_debug number := ASO_DEBUG_PUB.G_DEBUG_LEVEL ;
448
449 l_billing_flag VARCHAR2(30) ;
450 l_inv_item_id NUMBER ;
451 l_unit_code VARCHAR2(3) ;
452
453 l_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
454 l_header_rec_default ASO_QUOTE_PUB.Qte_Header_Rec_Type;
455 l_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
456 l_Line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
457 l_hd_shipment_tbl ASO_QUOTE_PUB.Shipment_tbl_Type;
458 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
459 l_hd_payment_tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
460 l_lot_serial_tbl ASO_ORDER_INT.Lot_Serial_Tbl_Type;
461 l_line_price_adj_tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
462 l_charges_rec_type CS_Charge_Details_PUB.Charges_Rec_Type;
463
464 i NUMBER:=0;
465 k NUMBER:=0;
466 lx_return_status VARCHAR2(1);
467 lx_msg_count NUMBER;
468 lx_msg_data VARCHAR2(2000);
469 l_workflow_process_id NUMBER;
470
471 -- x_return_status VARCHAR2(1);
472 -- x_msg_count NUMBER;
473 -- x_msg_data VARCHAR2(2000);
474 oe_x_msg_count NUMBER;
475 oe_x_msg_data VARCHAR2(2000);
476
477 x_order_header_rec ASO_ORDER_INT.Order_Header_rec_type;
478 x_order_line_tbl ASO_ORDER_INT.Order_Line_tbl_type;
479 x_order_header_id NUMBER;
480 l_unit_selling_price NUMBER;
481 l_profile_option VARCHAR2(33) ;
482 l_control_rec ASO_ORDER_INT.control_rec_type;
483 l_login_id number;
484 l_user_id number;
485 l_ordered_quantity number;
486 l_ship_ordered_quantity number;
487 l_inv_org_id number;
488 l_record_found VARCHAR2(1) := 'N';
489 l_ship_to_cust_account_id number;
490 l_invoice_to_cust_account_id number;
491
492 -- Fix for bug:3509921
493 l_incident_number NUMBER;
494 Resource_Busy EXCEPTION; --7117301
495 PRAGMA EXCEPTION_INIT(Resource_Busy, -00054); --7117301
496
497 -- Following cursor fetches charge lines that are not submitted to
498 -- order management as orders or returns.
499
500 CURSOR Fetch_Est_Dtl(p_incident_id NUMBER,p_inv_org_id NUMBER,p_ctrl_submit_source VARCHAR2,
501 p_ctrl_orig_source VARCHAR2,p_ctrl_source VARCHAR2) IS
502 SELECT edt.incident_id,
503 edt.org_id,
504 edt.estimate_detail_id,
505 edt.currency_code,
506 edt.conversion_type_code,
507 edt.conversion_rate,
508 edt.conversion_rate_date,
509 edt.business_process_id,
510 edt.txn_billing_type_id,
511 edt.price_list_header_id,
512 edt.inventory_item_id,
513 edt.item_revision,
514 edt.unit_of_measure_code,
515 edt.quantity_required,
516 edt.selling_price,
517 edt.after_warranty_cost,
518 edt.invoice_to_org_id,
519 edt.ship_to_org_id,
520 edt.customer_product_id,
521 edt.installed_cp_return_by_date,
522 edt.new_cp_return_by_date, -- Bug 4586140
523 edt.add_to_order_flag,
524 edt.order_header_id,
525 edt.rollup_flag,
526 edt.purchase_order_num,
527 edt.return_reason_code,
528 edt.serial_number return_serial_number,
529 tt.LINE_ORDER_CATEGORY_CODE line_category_code,
530 edt.organization_id,
531 edt.transaction_inventory_org,
532 edt.invoice_to_account_id,
533 edt.ship_to_account_id,
534 edt.ship_to_contact_id,
535 edt.bill_to_contact_id,
536 edt.bill_to_party_id,
537 edt.ship_to_party_id,
538 tb.order_type_id,
539 tb.line_type_id,
540 i.comms_nl_trackable_flag,
541 sr.customer_id,
542 sr.account_id,
543 sr.incident_number,
544 tbt.billing_type,
545 cbtc.rollup_item_id,
546 cbtc.billing_category,
547 edt.list_price , -- 4870210
548 i.item_type item_type_code --6523849
549 FROM CS_ESTIMATE_DETAILS edt,
550 CS_TXN_BILLING_OETXN_ALL tb,
551 CS_TXN_BILLING_TYPES tbt,
552 MTL_SYSTEM_ITEMS_KFV i,
553 CS_INCIDENTS_ALL_B sr,
554 CS_TRANSACTION_TYPES_B tt,
555 cs_billing_type_categories cbtc
556 WHERE edt.incident_id = p_incident_id AND
557 edt.interface_to_oe_flag = 'Y' AND
558 edt.order_line_id IS NULL AND
559 edt.charge_line_type = 'ACTUAL' AND
560 edt.txn_billing_type_id = tb.txn_billing_type_id(+) AND
561 --edt.org_id = tb.org_id (+) AND
562 nvl(edt.org_id, '-999') = nvl(tb.org_id, '-999') AND
563 edt.inventory_item_id = i.inventory_item_id AND
564 nvl(i.organization_id,-999) = nvl(p_inv_org_id,-999) AND
565 edt.incident_id = sr.incident_id AND
566 edt.txn_billing_type_id = tbt.txn_billing_type_id AND
567 tt.transaction_type_id = tbt.transaction_type_id AND
568 tbt.billing_type = cbtc.billing_type AND
569 edt.line_submitted = 'N' AND
570 ((edt.original_source_code = nvl(p_ctrl_orig_source,ORIGINAL_SOURCE_CODE)
571 and edt.source_code = nvl(p_ctrl_source,SOURCE_CODE)) OR
572 (p_ctrl_submit_source = 'DR'
573 and original_source_code = 'SR'
574 and edt.source_code = 'DR'))
575 order by edt.org_id,edt.estimate_detail_id
576 For Update nowait; -- for cross ou --7117301
577
578 TYPE t_EstDTLTAB IS TABLE OF Fetch_Est_Dtl%rowtype
579 INDEX BY BINARY_INTEGER;
580
581 EstDTLTAB T_EstDTLTAB;
582
583 -- Changes for 11.5.10
584 TYPE t_msgtable IS TABLE OF VARCHAR2(2000)
585 INDEX BY BINARY_INTEGER;
586
587 msg_table t_msgtable;
588 temp_tab VARCHAR2(8000);
589
590 --
591 -- Following Cursor fetches the Order_Number from Charge lines that have
592 -- already been submitted to Order Management .
593
594 CURSOR Fetch_Est_Ord_Dtl(p_currency_code varchar2,p_price_list_header_id number,
595 p_invoice_to_org_id number,p_ship_to_org_id number ,
596 p_purchase_order_num varchar2,p_txn_billing_type_id number,
597 p_org_id number,p_order_type_id number,p_book_order_flag varchar2) IS
598 SELECT nvl(max(edt.order_header_id),-999)
599 FROM CS_ESTIMATE_DETAILS edt,
600 CS_TXN_BILLING_OETXN_ALL tb,
601 OE_ORDER_HEADERS_ALL oe
602 WHERE edt.incident_id = p_incident_id AND
603 edt.currency_code = p_currency_code AND
604 nvl(edt.invoice_to_org_id,-999) = nvl(p_invoice_to_org_id,-999) AND
605 nvl(edt.ship_to_org_id,-999) = nvl(p_ship_to_org_id,-999) AND
606 nvl(edt.org_id,-999) = nvl(p_org_id,-999) AND
607 nvl(edt.purchase_order_num,'-999') = nvl(p_purchase_order_num,'-999') AND
608 edt.order_header_id is not null AND
609 edt.order_line_id is not null AND
610 edt.interface_to_oe_flag = 'Y' AND
611 edt.txn_billing_type_id = tb.txn_billing_type_id AND
612 nvl(edt.org_id,-999) = nvl(tb.org_id,-999) AND
613 tb.order_type_id = p_order_type_id AND
614 edt.order_header_id = oe.header_id AND
615 oe.open_flag = 'Y' AND
616 oe.booked_flag = decode(p_book_order_flag,'N','N','Y');
617
618
619 -- NOT Needed since org_id is now at the line level.
620 -- Get the Incident_Org_id
621 -- Cursor Get_Org_Id is
622 -- SELECT org_id
623 -- FROM cs_incidents_all_b
624 -- WHERE incident_id = p_incident_id;
625
626 -- Get the PO from Order_Header
627 Cursor Cust_Po(p_order_header_id number) is
628 SELECT nvl(cust_po_number,'-999')
629 FROM oe_order_headers_all
630 WHERE header_id = p_order_header_id;
631
632 -- Get the Modifier_header_id
633 CURSOR Get_Modifier_Header(p_list_line_id number) is
634 SELECT list_header_id
635 FROM qp_list_lines
636 WHERE list_line_id = p_list_line_id;
637
638 --
639 --BUG 4287842
640
641 CURSOR get_inv_item_id(p_instance_id number) IS
642 select inventory_item_id from csi_item_instances
643 where instance_id = p_instance_id;
644 --
645 CURSOR acct_from_party(p_party_id number) IS
646 SELECT count(cust_account_id)
647 FROM HZ_CUST_ACCOUNTS_ALL
648 WHERE party_id = p_party_id
649 AND NVL(status, 'A') = 'A';
650 --
651 --
652 l_inventory_item_id NUMBER;
653 l_order_type_id NUMBER := 0;
654 l_order_header_id NUMBER;
655 l_line_type_id NUMBER := 0;
656 l_org_id NUMBER;
657 l_purchase_order_num VARCHAR2(50); --added by cnemalik
658
659 l_OM_ERROR EXCEPTION;
660 l_IB_ERROR EXCEPTION;
661
662 l_dummy NUMBER;
663
664 -- The following are reqd to call installbase API.
665 -- 11.5.5 Intstalled Base definitions
666 -- l_line_inst_dtl_rec cs_inst_detail_pub.line_inst_dtl_rec_type;
667 -- l_line_inst_dtl_desc_flex CS_INSTALLEDBASE_PUB.DFF_REC_TYPE;
668
669 -- 11.5.6 Intstalled Base definitions
670 csi_txn_line_rec csi_t_datastructures_grp.txn_line_rec;
671 csi_txn_line_rec_null csi_t_datastructures_grp.txn_line_rec;
672 csi_txn_line_detail_tbl csi_t_datastructures_grp.txn_line_detail_tbl;
673 csi_txn_party_detail_tbl csi_t_datastructures_grp.txn_party_detail_tbl;
674 csi_txn_pty_acct_detail_tbl csi_t_datastructures_grp.txn_pty_acct_detail_tbl;
675 csi_txn_ii_rltns_tbl csi_t_datastructures_grp.txn_ii_rltns_tbl;
676 csi_txn_org_assgn_tbl csi_t_datastructures_grp.txn_org_assgn_tbl;
677 csi_txn_ext_attrib_vals_tbl csi_t_datastructures_grp.txn_ext_attrib_vals_tbl;
678 csi_txn_systems_tbl csi_t_datastructures_grp.txn_systems_tbl;
679 --
680 l_sub_type_id NUMBER;
681 l_source_type_id NUMBER;
682
683 l_src_reference_reqd VARCHAR2(1);
684 l_src_change_owner VARCHAR2(1);
685 l_src_change_owner_to_code VARCHAR2(1);
686 l_src_return_reqd_flag VARCHAR2(1);
687
688 l_non_src_reference_reqd VARCHAR2(1);
689 l_non_src_change_owner VARCHAR2(1);
690 l_non_src_change_owner_to_code VARCHAR2(1);
691
692 l_internal_party_id NUMBER;
693 l_instance_party_id NUMBER;
694 l_update_ib_flag VARCHAR2(1);
695
696 l_ib_serial_number VARCHAR2(30);
697
698 -- Common Intstalled Base definitions
699 xi_return_status VARCHAR2(30);
700 xi_msg_count NUMBER;
701 xi_msg_data VARCHAR2(2000);
702 xi_line_inst_detail_id NUMBER;
703 xi_object_version_number NUMBER;
704 l_transaction_type_id NUMBER;
705 --
706 --
707 -- Variables for creating Line_adjustment records
708 l_modifier_header_id NUMBER := 0;
709 l_modifier_line_id NUMBER := 0;
710 l_operand NUMBER := 0;
711 l_adjusted_amount NUMBER := 0;
712 l_before_warranty_cost NUMBER := 0;
713 l_return_quantity NUMBER; -- Fix bug 2930729
714
715 l_file varchar2(80);
716 l_acct_no NUMBER;
717
718 -- Added for bug:5408354
719 l_arith_operator qp_list_lines.arithmetic_operator%type;
720 l_currency_code qp_list_headers.currency_code%type;
721 L_API_ERROR_WITH_FND_MESSAGE EXCEPTION;
722 --
723 --
724 -- Added this to get account_id after submitting an order to OM.
725 --
726 l_account_id NUMBER;
727
728 -- Changes for 11.5.10
729 --
730 l_book_order_flag VARCHAR2(1);
731 l_book_order_profile VARCHAR2(1);
732 l_order_status_flag VARCHAR2(1);
733 l_ctrl_orig_source VARCHAR2(30);
734 l_ctrl_source VARCHAR2(30);
735 --
736 CURSOR get_order_status(p_order_header_id number) IS
737 SELECT booked_flag
738 FROM oe_order_headers_all
739 WHERE header_id = p_order_header_id;
740
741 --srini
742 CURSOR Get_party_number is
743 SELECT party_number
744 FROM hz_parties
745 WHERE party_id = p_party_id;
746
747 l_party_number hz_parties.party_number%type;
748 --
749 --
750 -- For Events
751
752 wf_resp_appl_id number;
753 wf_resp_id number;
754 wf_user_id number;
755 --
756 orig_org_id number;
757 orig_user_id number;
758 orig_resp_id number;
759 orig_resp_appl_id number;
760 new_org_id number;
761 new_user_id number;
762 new_resp_id number;
763 new_resp_appl_id number;
764
765 BEGIN
766
767 -- dbms_application_info.set_client_info('204');
768
769
770 -- Standard Start of API Savepoint
771 SAVEPOINT CS_Charge_Create_Order_PVT;
772
773 -- Standard Call to check API compatibility
774 IF NOT FND_API.Compatible_API_Call( l_api_version,
775 p_api_version,
776 l_api_name,
777 G_PKG_NAME) THEN
778 RAISE FND_API.G_EXC_ERROR ;
779 END IF;
780
781 -- Initialize message list if p_init_msg_list is set to TRUE.
782 IF FND_API.to_Boolean( p_init_msg_list ) THEN
783 FND_MSG_PUB.initialize;
784 END IF;
785
786 -- Initialize API return status to success
787 x_return_status := FND_API.G_RET_STS_SUCCESS;
788
789 ----------------------- FND Logging -----------------------------------
790 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
791 THEN
792 FND_LOG.String
793 ( FND_LOG.level_procedure , L_LOG_MODULE || 'start'
794 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
795 );
796 FND_LOG.String
797 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
798 , 'p_api_version:' || p_api_version
799 );
800 FND_LOG.String
801 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
802 , 'p_init_msg_list:' || p_init_msg_list
803 );
804 FND_LOG.String
805 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
806 , 'p_commit:' || p_commit
807 );
808 FND_LOG.String
809 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
810 , 'p_validation_level:' || p_validation_level
811 );
812 FND_LOG.String
813 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
814 , 'p_incident_id:' || p_incident_id
815 );
816 FND_LOG.String
817 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
818 , 'p_party_id:' || p_party_id
819 );
820 FND_LOG.String
821 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
822 , 'p_account_id:' || p_account_id
823 );
824 FND_LOG.String
825 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
826 , 'p_book_order_flag:' || p_book_order_flag
827 );
828 FND_LOG.String
829 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
830 , 'p_submit_source:' || p_submit_source
831 );
832 FND_LOG.String
833 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
834 , 'p_submit_from_system:' || p_submit_from_system
835 );
836
837 END IF;
838 --
839 -- API body
840 --
841 -- Local Procedure
842
843 -- Validate parameters
844 IF (p_incident_id is null) THEN
845 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
846 THEN
847 FND_LOG.String
848 ( FND_LOG.level_statement
849 , L_LOG_MODULE || 'get_request_info_end'
850 , 'invalid input parameter :' || 'p_incident_id'
851 );
852 END IF;
853 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
854 FND_MESSAGE.Set_Token('PARAM','p_incident_id');
855 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
856 FND_MSG_PUB.Add;
857 RAISE FND_API.G_EXC_ERROR;
858 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
859 END IF;
860 IF (p_party_id is null) THEN
861 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
862 THEN
863 FND_LOG.String
864 ( FND_LOG.level_statement, L_LOG_MODULE || 'invalid input parameter :'
865 , 'p_party_id'
866 );
867 END IF;
868 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
869 FND_MESSAGE.Set_Token('PARAM','p_party_id');
870 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
871 FND_MSG_PUB.Add;
872 RAISE FND_API.G_EXC_ERROR;
873 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
874 END IF;
875
876 -- Added validations for 11.5.10 parameters. There is no validation required
877 -- for submit_from_system parameter.
878 --
879 IF (p_book_order_flag NOT IN ('Y','N',FND_API.G_MISS_CHAR)) THEN
880 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
881 THEN
882 FND_LOG.String
883 ( FND_LOG.level_statement, L_LOG_MODULE || 'invalid input parameter :'
884 , 'p_book_order_flag'
885 );
886 END IF;
887 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
888 FND_MESSAGE.Set_Token('PARAM','p_book_order_flag');
889 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
890 FND_MSG_PUB.Add;
891 RAISE FND_API.G_EXC_ERROR;
892 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
893 END IF;
894
895 IF (p_submit_source NOT IN ('SR','DR','FS',FND_API.G_MISS_CHAR)) THEN
896 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
897 THEN
898 FND_LOG.String
899 ( FND_LOG.level_statement, L_LOG_MODULE || 'invalid input parameter :'
900 , 'p_submit_source'
901 );
902 END IF;
903 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
904 FND_MESSAGE.Set_Token('PARAM','p_submit_source');
905 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
906 FND_MSG_PUB.Add;
907 RAISE FND_API.G_EXC_ERROR;
908 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
909 END IF;
910 --- End of 11.5.10 parameter validations
911 ---
912 get_who_info(l_login_id,l_user_id);
913
914 -- Verify the account on the SR party
915 OPEN acct_from_party(p_party_id);
916 FETCH acct_from_party
917 INTO l_acct_no;
918 CLOSE acct_from_party;
919
920 IF (l_acct_no > 0) and (p_account_id is null) THEN
921 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
922 THEN
923 FND_LOG.String
924 ( FND_LOG.level_statement, L_LOG_MODULE , 'Missing account id'
925 );
926 END IF;
927 FND_MESSAGE.Set_Name('CS','CS_CHG_NO_ACCT_NUM_IN_SR');
928 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
929 FND_MSG_PUB.Add;
930 RAISE FND_API.G_EXC_ERROR;
931 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
932 --srini
933 ELSIF l_acct_no = 0 then
934 If fnd_profile.value('CS_SR_ACTION_MISS_ACCT') = 'CHG_ABORT_SUB' Then
935 Open Get_party_number;
936 Fetch Get_party_number into l_party_number;
937 Close Get_party_number;
938
939 FND_MESSAGE.Set_Name('CS','CS_SR_NO_VALID_ACCT');
940 FND_MESSAGE.Set_Token('SOLD_TO_PARTY',l_party_number);
941 FND_MSG_PUB.Add;
942 RAISE FND_API.G_EXC_ERROR;
943 End if;
944 END IF;
945
946 -- Get modifier for OM adjustment lines
947 BEGIN
948 l_modifier_line_id := fnd_profile.value_specific('CS_CHARGE_DEFAULT_MODIFIER');
949 -- dbms_output.put_line('Default_Modifier' || l_modifier_line_id);
950 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
951 THEN
952 FND_LOG.String
953 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
954 , 'The Value of profile CS_CHARGE_DEFAULT_MODIFIER :' || l_modifier_line_id
955 );
956 END IF;
957
958 IF (l_modifier_line_id IS NOT NULL) THEN
959 OPEN Get_Modifier_Header(l_modifier_line_id);
960 FETCH Get_Modifier_Header
961 INTO l_modifier_header_id;
962 CLOSE Get_Modifier_Header;
963 END IF;
964 EXCEPTION
965 WHEN OTHERS THEN
966 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_DEFAULT_MODIFIER');
967 FND_MSG_PUB.Add;
968 RAISE FND_API.G_EXC_ERROR;
969 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
970 END;
971
972 --
973 -- Check the value of book_order_flag.Changes for 11.5.10
974 --
975 BEGIN
976
977 l_book_order_profile := fnd_profile.value('CS_CHG_CREATE_BOOKED_ORDERS');
978 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
979 THEN
980 FND_LOG.String
981 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
982 , 'The Value of profile CS_CHG_CREATE_BOOKED_ORDERS :' || l_book_order_profile
983 );
984 END IF;
985 -- Set the value of the profile as 'N', if the profile has no value set.
986 IF l_book_order_profile IS NULL THEN
987 l_book_order_profile := 'N';
988 END IF;
989
990 IF p_book_order_flag IS NOT NULL OR
991 p_book_order_flag = FND_API.G_MISS_CHAR THEN
992 l_book_order_flag := p_book_order_flag;
993
994 ELSIF p_book_order_flag IS NULL THEN
995 l_book_order_flag := l_book_order_profile;
996 END IF;
997
998 EXCEPTION
999 WHEN OTHERS THEN
1000 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_SUBMIT_PARAMS');
1001 FND_MESSAGE.Set_Token('PARAM','p_book_order_flag');
1002 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
1003 FND_MSG_PUB.Add;
1004 RAISE FND_API.G_EXC_ERROR;
1005 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006
1007 END;
1008 -- End of Book Order Flag
1009 --
1010 -- Changes for 11.5.10. Validate submit source.
1011 --
1012 IF p_submit_source = 'SR' THEN
1013 l_ctrl_orig_source := 'SR';
1014 l_ctrl_source := 'SR';
1015 ELSIF p_submit_source = 'DR' THEN
1016 l_ctrl_orig_source := 'DR';
1017 l_ctrl_source := NULL;
1018 ELSIF p_submit_source = 'FS' THEN
1019 l_ctrl_orig_source := 'SR';
1020 l_ctrl_source := 'SD';
1021 ELSIF p_submit_source = FND_API.G_MISS_CHAR THEN
1022 l_ctrl_orig_source := NULL;
1023 l_ctrl_source := NULL;
1024 END IF;
1025
1026 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1027 THEN
1028 FND_LOG.String
1029 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1030 , 'The defaulted value of parameter l_ctrl_orig_source:' || l_ctrl_orig_source
1031 );
1032 FND_LOG.String
1033 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1034 , 'The defaulted value of parameter l_ctrl_source:' || l_ctrl_source
1035 );
1036 END IF;
1037
1038 --
1039 -- End of Validate Submit Source for 11.5.10.
1040 --
1041 -- NOT NEEDED since org_id is now at the line level.
1042 -- Get the Incident_Org_id
1043 -- OPEN Get_Org_Id;
1044 -- FETCH Get_Org_Id
1045 -- INTO l_org_id;
1046 -- CLOSE Get_Org_Id;
1047
1048 -- Get Inventory_Org_Id
1049 --
1050 l_inv_org_id := cs_std.Get_Item_Valdn_Orgzn_ID;
1051
1052 -- dbms_output.put_line('Inventory_Org ' || l_inv_org_id);
1053
1054 OPEN Fetch_Est_Dtl(p_incident_id,l_inv_org_id,p_submit_source,
1055 l_ctrl_orig_source,l_ctrl_source);
1056
1057 LOOP
1058 i := i+1;
1059 FETCH Fetch_Est_Dtl
1060 INTO EstDtlTab(i);
1061 EXIT WHEN Fetch_Est_Dtl%NOTFOUND;
1062
1063 -- dbms_output.put_line('Estimate_Detail_Id ' || EstDtlTab(i).estimate_detail_id);
1064
1065 l_record_found := 'Y';
1066 --
1067 --
1068 l_control_rec.calculate_price := FND_API.G_FALSE;
1069 l_org_id := EstDtlTab(i).org_id;
1070
1071 OPEN Fetch_Est_Ord_Dtl(EstDtlTab(i).currency_code,EstDtlTab(i).price_list_header_id,
1072 EstDtlTab(i).invoice_to_org_id,EstDtlTab(i).ship_to_org_id,
1073 EstDtlTab(i).purchase_order_num ,EstDtlTab(i).txn_billing_type_id,
1074 l_org_id, EstDtlTab(i).Order_Type_Id,l_book_order_flag);
1075 FETCH Fetch_Est_Ord_Dtl
1076 INTO l_order_header_id;
1077 CLOSE Fetch_Est_Ord_Dtl;
1078
1079 -- dbms_output.put_line('In the begin');
1080 --
1081 -- Added for 11.5.10
1082 -- Checking the status of the order for add_to_order or
1083 -- when adding a line to an existing order.
1084
1085 -- dbms_output.put_line('Add_to_Order_Flag' || EstDtlTab(i).add_to_order_flag);
1086 -- dbms_output.put_line('order_header_id' || l_order_header_id);
1087
1088 IF (EstDtlTab(i).add_to_order_flag = 'Y'
1089 OR l_order_header_id <> -999
1090 )
1091 AND EstDtlTab(i).add_to_order_flag <> 'F' --5649493
1092 THEN
1093
1094 IF EstDtlTab(i).order_header_id IS NULL THEN
1095
1096 OPEN get_order_status(l_order_header_id);
1097 FETCH Get_Order_Status
1098 INTO l_order_status_flag;
1099 CLOSE Get_Order_Status;
1100
1101 ELSE
1102
1103 OPEN get_order_status(EstDtlTab(i).order_header_id);
1104 FETCH Get_Order_Status
1105 INTO l_order_status_flag;
1106 CLOSE Get_Order_Status;
1107
1108 END IF;
1109
1110 -- dbms_output.put_line('Order_Status_Flag' || l_order_status_flag);
1111
1112 -- OM order_status is null till the records have been comitted.
1113 IF (l_order_status_flag = 'Y' or
1114 l_order_status_flag IS NULL or
1115 l_order_status_flag = 'N') and
1116 l_book_order_flag = 'N' THEN
1117
1118 l_control_rec.book_flag := FND_API.G_FALSE;
1119
1120 ELSIF (l_order_status_flag = 'Y' and
1121 l_book_order_flag = 'Y') THEN
1122
1123 l_control_rec.book_flag := FND_API.G_FALSE;
1124
1125 ELSIF l_order_status_flag = 'N' and
1126 l_book_order_flag = 'Y' THEN
1127
1128 l_control_rec.book_flag := FND_API.G_TRUE;
1129
1130 END IF;
1131
1132 ELSIF (EstDtlTab(i).add_to_order_flag = 'N') or
1133 (EstDtlTab(i).add_to_order_flag IS NULL) or
1134 -- fix bug:3667208
1135 (EstDtlTab(i).add_to_order_flag = 'F')
1136 --and (l_order_header_id = -999 ) --5649493
1137 THEN
1138
1139 IF l_book_order_flag = 'Y' THEN
1140 l_control_rec.book_flag := FND_API.G_TRUE;
1141 ELSIF l_book_order_flag = 'N' THEN
1142 l_control_rec.book_flag := FND_API.G_FALSE;
1143 END IF;
1144 END IF;
1145
1146 -- dbms_output.put_line('Book_Flag' || l_control_rec.book_flag);
1147 --
1148 -- End of 11.5.10 changes for order status.
1149 --
1150 -- VALIDATE CATEGORY CODE
1151 IF EstDtlTab(i).line_category_code <> 'RETURN'
1152 AND EstDtlTab(i).line_category_code <> 'ORDER' THEN
1153 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_CAT_CODE');
1154 FND_MSG_PUB.Add;
1155 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1156 p_count => x_msg_count,
1157 p_data => x_msg_data);
1158
1159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1160
1161 END IF;
1162
1163 IF EstDtlTab(i).quantity_required is null
1164 OR EstDtlTab(i).quantity_required = 0 THEN
1165 FND_MESSAGE.Set_Name('CS','CS_CHG_INVALID_QTY');
1166 FND_MSG_PUB.Add;
1167 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1168 p_count => x_msg_count,
1169 p_data => x_msg_data);
1170 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1171 END IF;
1172
1173 -- GET ORDER_TYPE AND LINE_TYPE
1174 BEGIN
1175 l_order_type_id := EstDtlTab(i).order_type_id;
1176 l_line_type_id := EstDtlTab(i).line_type_id;
1177
1178 IF l_order_type_id = 0
1179 or l_order_type_id is null
1180 or l_line_type_id = 0
1181 or l_line_type_id is null then
1182 RAISE L_OM_ERROR;
1183
1184 END IF;
1185
1186 EXCEPTION
1187 WHEN L_OM_ERROR THEN
1188 FND_MESSAGE.Set_Name('CS','CS_CHG_DEFINE_OMTYPES');
1189 FND_MSG_PUB.Add;
1190 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1191 p_count => x_msg_count,
1192 p_data => x_msg_data);
1193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1194 END;
1195 --
1196 --
1197 -- dbms_output.put_line('After Order Type and Line Type');
1198 --
1199 -- Clear ASO datastructures
1200 l_line_tbl.delete;
1201 l_line_dtl_tbl.delete;
1202 l_ln_shipment_tbl.delete;
1203 l_hd_payment_tbl.delete;
1204 l_line_price_adj_tbl.delete;
1205 l_header_rec := l_header_rec_default;
1206
1207 l_header_rec.invoice_to_party_id := NULL;
1208 l_header_rec.invoice_to_party_site_id := NULL;
1209 l_header_rec.invoice_to_cust_account_id := NULL;
1210
1211 l_lot_serial_tbl := ASO_ORDER_INT.G_MISS_Lot_Serial_Tbl;
1212 --
1213 -- Order_type_id moved to create order section. fix bug:3557645
1214 -- l_header_rec.order_type_id := l_order_type_id;
1215
1216 --
1217 -- Populate Flexfiled with G_MISS_CHAR so defaulting rules will work.
1218 -- Passing flexfield values as g_miss_char both for new orders/add to orders.
1219 l_header_rec.attribute1 := FND_API.G_MISS_CHAR;
1220 l_header_rec.attribute2 := FND_API.G_MISS_CHAR;
1221 l_header_rec.attribute3 := FND_API.G_MISS_CHAR;
1222 l_header_rec.attribute4 := FND_API.G_MISS_CHAR;
1223 l_header_rec.attribute5 := FND_API.G_MISS_CHAR;
1224 l_header_rec.attribute6 := FND_API.G_MISS_CHAR;
1225 l_header_rec.attribute7 := FND_API.G_MISS_CHAR;
1226 l_header_rec.attribute8 := FND_API.G_MISS_CHAR;
1227 l_header_rec.attribute9 := FND_API.G_MISS_CHAR;
1228 l_header_rec.attribute10 := FND_API.G_MISS_CHAR;
1229 l_header_rec.attribute11 := FND_API.G_MISS_CHAR;
1230 l_header_rec.attribute12 := FND_API.G_MISS_CHAR;
1231 l_header_rec.attribute13 := FND_API.G_MISS_CHAR;
1232 l_header_rec.attribute14 := FND_API.G_MISS_CHAR;
1233 l_header_rec.attribute15 := FND_API.G_MISS_CHAR;
1234
1235 -- FIX Bug:3667208
1236 IF (EstDtlTab(i).add_to_order_flag = 'F') THEN
1237
1238 l_header_rec.order_type_id := l_order_type_id;
1239 l_header_rec.org_id := EstDtlTab(i).org_id;
1240 l_header_rec.quote_source_code := 'Service Billing'; -- Lookup value 7
1241 l_header_rec.party_id := p_party_id ;
1242
1243 IF EstDtlTab(i).invoice_to_account_id IS NULL THEN
1244 Get_acct_from_party_site (
1245 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
1246 p_sold_to_customer_party => p_party_id,
1247 p_sold_to_customer_account => p_account_id,
1248 p_org_id => EstDtlTab(i).org_id,--Bug# 4870037
1249 p_site_use_flag => 'B',--Bug# 4870037
1250 p_site_use_code => 'BILL_TO',
1251 x_account_id => l_invoice_to_cust_account_id);
1252 ELSE
1253 --l_header_rec.invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1254 l_invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1255 END IF;
1256
1257 l_header_rec.invoice_to_party_id := EstDtlTab(i).bill_to_contact_id; --Bugfix :7164996
1258 l_header_rec.invoice_to_party_site_id := EstDtlTab(i).invoice_to_org_id;
1259 l_header_rec.cust_account_id := p_account_id;
1260 l_header_rec.invoice_to_cust_account_id := l_invoice_to_cust_account_id;
1261
1262 IF EstDtlTab(i).ship_to_account_id IS NULL THEN
1263 Get_acct_from_party_site (
1264 p_party_site_id => EstDtlTab(i).ship_to_org_id,
1265 p_sold_to_customer_party => p_party_id,
1266 p_sold_to_customer_account => p_account_id,
1267 p_org_id => EstDtlTab(i).org_id,
1268 p_site_use_flag => 'S',
1269 p_site_use_code => 'SHIP_TO',
1270 x_account_id => l_ship_to_cust_account_id);
1271 ELSE
1272 --l_hd_shipment_tbl(j).ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1273 l_ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1274 END IF;
1275
1276 l_hd_shipment_tbl(j).ship_to_party_id := EstDtlTab(i).ship_to_contact_id; --Bugfix :7164996
1277 l_hd_shipment_tbl(j).ship_to_party_site_id := EstDtlTab(i).ship_to_org_id;
1278 l_hd_shipment_tbl(j).ship_from_org_id := EstDtlTab(i).transaction_inventory_org;
1279 l_hd_shipment_tbl(j).ship_to_cust_account_id := l_ship_to_cust_account_id;
1280
1281 l_header_rec.quote_header_id := P_INCIDENT_ID;
1282
1283 BEGIN
1284 -- Fix for the bug:3509921
1285 l_incident_number := to_number(EstDtlTab(i).incident_number);
1286
1287 l_header_rec.original_system_reference := EstDtlTab(i).incident_number;
1288 l_header_rec.quote_number := EstDtlTab(i).incident_number;
1289
1290 -- dbms_output.put_line('Passing number incident_number');
1291
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 NULL;
1295
1296 -- dbms_output.put_line('Passing character incident_number');
1297
1298 END;
1299
1300 --
1301 l_header_rec.order_type_id := l_order_type_id;
1302
1303 IF EstDtlTab(i).conversion_type_code = 'User' THEN
1304 l_header_rec.exchange_rate := EstDtlTab(i).conversion_rate;
1305 l_header_rec.exchange_type_code := EstDtlTab(i).conversion_type_code;
1306 l_header_rec.exchange_rate_date := EstDtlTab(i).conversion_rate_date;
1307 ELSIF EstDtlTab(i).conversion_type_code = 'Corporate' THEN
1308 l_header_rec.exchange_type_code := EstDtlTab(i).conversion_type_code;
1309 l_header_rec.exchange_rate_date := EstDtlTab(i).conversion_rate_date;
1310 END IF;
1311
1312 l_header_rec.price_list_id := EstDtlTab(i).price_list_header_id;
1313 l_header_rec.currency_code := EstDtlTab(i).currency_code ;
1314
1315 --
1316 IF EstDtlTab(i).purchase_order_num <> '-999' THEN
1317 l_hd_payment_tbl(j).payment_type_code := NULL;
1318 -- Fix bug:5210040
1319 -- l_hd_payment_tbl(j).payment_ref_number := EstDtlTab(i).purchase_order_num;
1320 l_hd_payment_tbl(j).cust_po_number := EstDtlTab(i).purchase_order_num;
1321 END IF;
1322 --
1323 END IF; -- END OF CREATING A NEW ORDER FOR A CHARGE LINE.NEW ORDER IS CREATED IF
1324 -- ADD_TO_ORDER_FLAG = 'F'
1325 --
1326 --
1327 /**************** ADDING A LINE TO AN EXISTING ORDER *************/
1328
1329 IF (EstDtlTab(i).add_to_order_flag = 'Y') or (l_order_header_id <> -999) THEN
1330 IF (EstDtlTab(i).add_to_order_flag = 'Y') then
1331 l_header_rec.order_id := EstDtlTab(i).order_header_id;
1332 --
1333 OPEN Cust_Po(EstDtlTab(i).order_header_id);
1334 FETCH Cust_Po
1335 INTO l_purchase_order_num;
1336 CLOSE Cust_Po;
1337 --
1338 ELSIF (l_order_header_id <> -999) then
1339 l_header_rec.order_id := l_order_header_id;
1340 --
1341 OPEN Cust_Po(l_order_header_id);
1342 FETCH Cust_Po
1343 INTO l_purchase_order_num;
1344 CLOSE Cust_Po;
1345 --
1346 END IF;
1347
1348 l_line_tbl(j).operation_code := 'CREATE';
1349 l_ln_shipment_tbl(j).operation_code := 'CREATE';
1350
1351 -- following added by cnemalik
1352 IF (l_purchase_order_num = '-999') AND
1353 (EstDtlTab(i).purchase_order_num <> '-999') THEN
1354 -- Fix bug:51051400
1355 -- l_hd_payment_tbl(j).payment_ref_number := EstDtlTab(i).purchase_order_num;
1356 l_hd_payment_tbl(j).payment_type_code := NULL;
1357 l_hd_payment_tbl(j).cust_po_number := EstDtlTab(i).purchase_order_num;
1358
1359 ELSIF EstDtlTab(i).purchase_order_num <> '-999' AND
1360 l_purchase_order_num <> EstDtlTab(i).purchase_order_num THEN
1361
1362 ROLLBACK TO CS_Charge_Create_Order_PVT;
1363 FND_MESSAGE.Set_Name('CS', 'CS_CHG_INVALID_PO');
1364 FND_MESSAGE.Set_Token('PURCHASE_ORDER_NUM',l_purchase_order_num);
1365 FND_MSG_PUB.Add; -- 5455064
1366 -- APP_EXCEPTION.Raise_Exception;
1367 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1368 p_count => x_msg_count,
1369 p_data => x_msg_data);
1370
1371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1372
1373 END IF;
1374 -- This value needs to be passed for add_to_order/update_order as well.
1375 -- Fix for the bug 2123535
1376 l_header_rec.quote_source_code := 'Service Billing'; -- Lookup value 7
1377 -- Fix for the bug 5463554
1378 l_header_rec.original_system_reference := EstDtlTab(i).incident_number;
1379 l_header_rec.quote_number := EstDtlTab(i).incident_number;
1380
1381 ELSE /* create a new order */
1382 -- Populate Flexfiled with G_MISS_CHAR so defaulting rules will work
1383 -- Moved to before add_to_order/new_order.
1384 /* l_header_rec.attribute1 := FND_API.G_MISS_CHAR;
1385 l_header_rec.attribute2 := FND_API.G_MISS_CHAR;
1386 l_header_rec.attribute3 := FND_API.G_MISS_CHAR;
1387 l_header_rec.attribute4 := FND_API.G_MISS_CHAR;
1388 l_header_rec.attribute5 := FND_API.G_MISS_CHAR;
1389 l_header_rec.attribute6 := FND_API.G_MISS_CHAR;
1390 l_header_rec.attribute7 := FND_API.G_MISS_CHAR;
1391 l_header_rec.attribute8 := FND_API.G_MISS_CHAR;
1392 l_header_rec.attribute9 := FND_API.G_MISS_CHAR;
1393 l_header_rec.attribute10 := FND_API.G_MISS_CHAR;
1394 l_header_rec.attribute11 := FND_API.G_MISS_CHAR;
1395 l_header_rec.attribute12 := FND_API.G_MISS_CHAR;
1396 l_header_rec.attribute13 := FND_API.G_MISS_CHAR;
1397 l_header_rec.attribute14 := FND_API.G_MISS_CHAR;
1398 l_header_rec.attribute15 := FND_API.G_MISS_CHAR; */
1399 --
1400 l_header_rec.order_type_id := l_order_type_id;
1401 l_header_rec.org_id := EstDtlTab(i).org_id;
1402 l_header_rec.quote_source_code := 'Service Billing'; -- Lookup value 7
1403 l_header_rec.party_id := p_party_id ;
1404
1405 -- dbms_output.put_line('In the begin');
1406 -- IF ACCOUNT_ID IS NULL, THEN OC CREATES AN ACCT FROM INVOICE_TO_PARTY_SITE_ID
1407 /* Get_acct_from_party_site (
1408 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
1409 p_sold_to_customer_party => p_party_id,
1410 p_sold_to_customer_account => p_account_id,
1411 x_account_id => l_header_rec.invoice_to_cust_account_id); */
1412
1413 IF EstDtlTab(i).invoice_to_account_id IS NULL THEN
1414 Get_acct_from_party_site (
1415 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
1416 p_sold_to_customer_party => p_party_id,
1417 p_sold_to_customer_account => p_account_id,
1418 p_org_id => EstDtlTab(i).org_id,
1419 p_site_use_flag => 'B',
1420 p_site_use_code => 'BILL_TO',
1421 x_account_id => l_invoice_to_cust_account_id);
1422 ELSE
1423 --l_header_rec.invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1424 l_invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1425 END IF;
1426
1427 l_header_rec.invoice_to_party_id := EstDtlTab(i).bill_to_contact_id; --Bugfix :7164996
1428 l_header_rec.invoice_to_party_site_id := EstDtlTab(i).invoice_to_org_id;
1429 l_header_rec.cust_account_id := p_account_id;
1430 l_header_rec.invoice_to_cust_account_id := l_invoice_to_cust_account_id;
1431
1432 /*Get_acct_from_party_site (
1433 p_party_site_id => EstDtlTab(i).ship_to_org_id,
1434 p_sold_to_customer_party => p_party_id,
1435 p_sold_to_customer_account => p_account_id,
1436 x_account_id => l_hd_shipment_tbl(j).ship_to_cust_account_id); */
1437
1438 IF EstDtlTab(i).ship_to_account_id IS NULL THEN
1439 Get_acct_from_party_site (
1440 p_party_site_id => EstDtlTab(i).ship_to_org_id,
1441 p_sold_to_customer_party => p_party_id,
1442 p_sold_to_customer_account => p_account_id,
1443 p_org_id => EstDtlTab(i).org_id,
1444 p_site_use_flag => 'S',
1445 p_site_use_code => 'SHIP_TO',
1446 x_account_id => l_ship_to_cust_account_id);
1447 ELSE
1448 --l_hd_shipment_tbl(j).ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1449 l_ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1450 END IF;
1451
1452 l_hd_shipment_tbl(j).ship_to_party_id := EstDtlTab(i).ship_to_contact_id; --Bugfix :7164996
1453 l_hd_shipment_tbl(j).ship_to_party_site_id := EstDtlTab(i).ship_to_org_id;
1454 l_hd_shipment_tbl(j).ship_from_org_id := EstDtlTab(i).transaction_inventory_org;
1455 l_hd_shipment_tbl(j).ship_to_cust_account_id := l_ship_to_cust_account_id;
1456
1457 l_header_rec.quote_header_id := P_INCIDENT_ID;
1458
1459 BEGIN
1460 -- Fix for the bug:3509921
1461 l_incident_number := to_number(EstDtlTab(i).incident_number);
1462
1463 l_header_rec.original_system_reference := EstDtlTab(i).incident_number;
1464 l_header_rec.quote_number := EstDtlTab(i).incident_number;
1465
1466 -- dbms_output.put_line('Passing number incident_number');
1467
1468 EXCEPTION
1469 WHEN OTHERS THEN
1470 NULL;
1471 -- dbms_output.put_line('Passing character incident_number');
1472 l_header_rec.original_system_reference := EstDtlTab(i).incident_number;
1473
1474 END;
1475
1476 --
1477 l_header_rec.order_type_id := l_order_type_id;
1478 IF EstDtlTab(i).conversion_type_code = 'User' THEN
1479 l_header_rec.exchange_rate := EstDtlTab(i).conversion_rate;
1480 l_header_rec.exchange_type_code := EstDtlTab(i).conversion_type_code;
1481 l_header_rec.exchange_rate_date := EstDtlTab(i).conversion_rate_date;
1482 ELSIF EstDtlTab(i).conversion_type_code = 'Corporate' THEN
1483 l_header_rec.exchange_type_code := EstDtlTab(i).conversion_type_code;
1484 END IF;
1485
1486 l_header_rec.price_list_id := EstDtlTab(i).price_list_header_id;
1487 l_header_rec.currency_code := EstDtlTab(i).currency_code ;
1488
1489 --
1490 IF EstDtlTab(i).purchase_order_num <> '-999' THEN
1491 l_hd_payment_tbl(j).payment_type_code := NULL;
1492 -- Fix bug:5210040
1493 -- l_hd_payment_tbl(j).payment_ref_number := EstDtlTab(i).purchase_order_num;
1494 l_hd_payment_tbl(j).cust_po_number := EstDtlTab(i).purchase_order_num;
1495 END IF;
1496 --
1497 END IF; -- END OF ADDING AN ORDER LINE TO AN EXISTING ORDER
1498 --
1499 -- Moved to create order section for bug:3557645
1500 -- l_header_rec.price_list_id := EstDtlTab(i).price_list_header_id;
1501 -- l_header_rec.currency_code := EstDtlTab(i).currency_code ;
1502
1503 -- VALIDATION FOR ROLLUP FLAG
1504 --
1505 --
1506 IF EstDtlTab(i).rollup_flag = 'Y' THEN
1507
1508 /* SELECT billing_type
1509 INTO l_billing_flag
1510 FROM cs_txn_billing_types
1511 WHERE txn_billing_type_id = EstDtlTab(i).txn_billing_type_id; */
1512
1513 /*
1514 SELECT cbtc.billing_category
1515 INTO l_billing_flag
1516 FROM cs_txn_billing_types ctbt, cs_billing_type_categories cbtc
1517 WHERE ctbt.txn_billing_type_id = EstDtlTab(i).txn_billing_type_id
1518 AND ctbt.billing_type = cbtc.billing_type;
1519
1520 IF l_billing_flag = 'L' THEN
1521 l_inv_item_id := FND_PROFILE.VALUE_SPECIFIC('CS_REPAIR_DEFAULT_LABOR_ITEM');
1522 l_profile_option := 'CS_REPAIR_DEFAULT_LABOR_ITEM';
1523 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1524 THEN
1525 FND_LOG.String
1526 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1527 , 'The Value of profile CS_REPAIR_DEFAULT_LABOR_ITEM :' || l_inv_item_id
1528 );
1529 END IF;
1530 ELSIF l_billing_flag = 'M' THEN
1531 l_inv_item_id := FND_PROFILE.VALUE_SPECIFIC('CS_REPAIR_DEFAULT_MATERIAL_ITEM');
1532 l_profile_option := 'CS_REPAIR_DEFAULT_MATERIAL_ITEM';
1533 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1534 THEN
1535 FND_LOG.String
1536 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1537 , 'The Value of profile CS_REPAIR_DEFAULT_MATERIAL_ITEM :' || l_inv_item_id
1538 );
1539 END IF;
1540 ELSIF l_billing_flag = 'E' THEN
1541 l_inv_item_id := FND_PROFILE.VALUE_SPECIFIC('CS_REPAIR_DEFAULT_EXPENSE_ITEM');
1542 l_profile_option := 'CS_REPAIR_DEFAULT_EXPENSE_ITEM';
1543 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
1544 THEN
1545 FND_LOG.String
1546 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
1547 , 'The Value of profile CS_REPAIR_DEFAULT_EXPENSE_ITEM :' || l_inv_item_id
1548 );
1549 END IF;
1550 END IF;
1551
1552 IF l_inv_item_id = NULL THEN */
1553 IF EstDtlTab(i).rollup_item_id IS NULL THEN
1554 --FND_MESSAGE.Set_Name('CS', 'CS_CHG_DEFINE_PROFILE_OPTION');
1555 --FND_MESSAGE.Set_Token('PROFILE_OPTION', l_profile_option);
1556 FND_MESSAGE.Set_Name('CS', 'CS_CHG_BILLING_TYPE_NO_ROLLUP');
1557 FND_MSG_PUB.Add;
1558 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1559 p_count => x_msg_count,
1560 p_data => x_msg_data);
1561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1562
1563 ELSE
1564 l_inv_item_id := EstDtlTab(i).rollup_item_id;
1565 END IF;
1566
1567 -- Get the Primary_Unit_of_Measure.
1568 SELECT primary_uom_code
1569 INTO l_unit_code
1570 FROM mtl_system_items
1571 WHERE inventory_item_id = l_inv_item_id AND
1572 organization_id = CS_STD.Get_Item_Valdn_Orgzn_ID;
1573
1574 l_line_tbl(j).inventory_item_id := l_inv_item_id ;
1575 l_line_tbl(j).UOM_code := l_unit_code ;
1576 -- not currently supported by OC l_line_tbl(j).item_revision := null;
1577
1578 ELSE -- If rollup_flag is not 'Y'
1579 l_line_tbl(j).inventory_item_id := EstDtlTab(i).inventory_item_id;
1580 l_line_tbl(j).UOM_code := EstDtlTab(i).unit_of_measure_code ;
1581 -- not currently supported by OC l_line_tbl(j).item_revision := EstDtlTab(i).item_revision;
1582
1583 END IF ; -- ROLLUP FLAG
1584
1585 /* Get_acct_from_party_site (
1586 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
1587 p_sold_to_customer_party => p_party_id,
1588 p_sold_to_customer_account => p_account_id,
1589 x_account_id => l_header_rec.invoice_to_cust_account_id); */
1590
1591 IF EstDtlTab(i).invoice_to_account_id IS NULL THEN
1592 Get_acct_from_party_site (
1593 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
1594 p_sold_to_customer_party => p_party_id,
1595 p_sold_to_customer_account => p_account_id,
1596 p_org_id => EstDtlTab(i).org_id,
1597 p_site_use_flag => 'B',
1598 p_site_use_code => 'BILL_TO',
1599 x_account_id => l_invoice_to_cust_account_id);
1600 ELSE
1601 --l_header_rec.invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1602 l_invoice_to_cust_account_id := EstDtlTab(i).invoice_to_account_id;
1603 END IF;
1604
1605 l_header_rec.invoice_to_cust_account_id := l_invoice_to_cust_account_id;
1606 l_line_tbl(j).item_type_code := EstDtlTab(i).item_type_code; -- 6523849
1607 l_line_tbl(j).invoice_to_party_id := EstDtlTab(i).bill_to_contact_id; --Bugfix :7164996
1608 l_line_tbl(j).invoice_to_party_site_id := EstDtlTab(i).invoice_to_org_id;
1609 l_line_tbl(j).invoice_to_cust_account_id := l_header_rec.invoice_to_cust_account_id;
1610 l_line_tbl(j).order_line_type_id := l_line_type_id;
1611
1612 IF EstDtlTab(i).line_category_code = 'RETURN' THEN
1613 l_ordered_quantity := (EstDtlTab(i).quantity_required * -1);
1614 l_line_tbl(j).quantity := l_ordered_quantity;
1615 ELSE
1616 l_line_tbl(j).quantity := EstDtlTab(i).quantity_required ;
1617 END IF;
1618
1619 l_line_tbl(j).quote_line_id := EstDtlTab(i).estimate_detail_id ;
1620 l_line_tbl(j).price_list_id := EstDtlTab(i).price_list_header_id ;
1621
1622 l_unit_selling_price := (nvl(EstDtlTab(i).after_warranty_cost,0)/EstDtlTab(i).quantity_required);
1623 l_line_tbl(j).line_list_price := nvl(EstDtlTab(i).list_price,0) ; -- 4870210
1624 -- l_line_tbl(j).line_list_price := nvl(EstDtlTab(i).selling_price,0) ;
1625 l_line_tbl(j).line_quote_price := l_unit_selling_price;
1626 l_line_tbl(j).line_category_code := EstDtlTab(i).line_category_code;
1627
1628 /*Get_acct_from_party_site (
1629 p_party_site_id => EstDtlTab(i).ship_to_org_id,
1630 p_sold_to_customer_party => p_party_id,
1631 p_sold_to_customer_account => p_account_id,
1632 x_account_id => l_ln_shipment_tbl(j).ship_to_cust_account_id); */
1633
1634 IF EstDtlTab(i).ship_to_account_id IS NULL THEN
1635 Get_acct_from_party_site (
1636 p_party_site_id => EstDtlTab(i).ship_to_org_id,
1637 p_sold_to_customer_party => p_party_id,
1638 p_sold_to_customer_account => p_account_id,
1639 p_org_id => EstDtlTab(i).org_id,
1640 p_site_use_flag => 'S',
1641 p_site_use_code => 'SHIP_TO',
1642 x_account_id => l_ship_to_cust_account_id);
1643 ELSE
1644 --l_hd_shipment_tbl(j).ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1645 l_ship_to_cust_account_id := EstDtlTab(i).ship_to_account_id;
1646 END IF;
1647
1648 l_ln_shipment_tbl(j).ship_to_party_id := EstDtlTab(i).ship_to_contact_id; --Bugfix :7164996
1649 l_ln_shipment_tbl(j).ship_to_party_site_id := EstDtlTab(i).ship_to_org_id;
1650 l_ln_shipment_tbl(j).quote_line_id := EstDtlTab(i).estimate_detail_id;
1651 l_ln_shipment_tbl(j).quote_header_id := EstDtlTab(i).incident_id;
1652 l_ln_shipment_tbl(j).ship_from_org_id := EstDtlTab(i).transaction_inventory_org;
1653 l_ln_shipment_tbl(j).ship_to_cust_account_id := l_ship_to_cust_account_id;
1654
1655 IF EstDtlTab(i).line_category_code = 'RETURN' then
1656 l_ship_ordered_quantity := (EstDtlTab(i).quantity_required * (-1));
1657 l_ln_shipment_tbl(j).quantity := l_ship_ordered_quantity;
1658 ELSE
1659 l_ln_shipment_tbl(j).quantity := EstDtlTab(i).quantity_required;
1660 END IF;
1661
1662 l_ln_shipment_tbl(j).qte_line_index := j;
1663
1664 IF EstDtlTab(i).line_category_code = 'RETURN' THEN
1665 -- return reason code needs to be passed in line_dtl_tbl
1666 l_line_dtl_tbl(j).return_reason_code := EstDtlTab(i).return_reason_code;
1667 -- l_line_dtl_tbl(j).qte_line_index := j; -- 6523849
1668 END IF; -- for return
1669
1670 l_line_dtl_tbl(j).qte_line_index := j; -- 6523849
1671 l_line_dtl_tbl(j).quote_line_id := EstDtlTab(i).estimate_detail_id; -- 6523849
1672
1673 --
1674 --
1675 -- Passing Values for Creating Line Adjustment record.
1676 --
1677 --
1678 -- << Start >> Changed the logic of modifier based Calculation based on bug 5408354
1679 IF l_modifier_line_id IS NOT NULL
1680 AND (nvl(EstDtlTab(i).selling_price, 0) * EstDtlTab(i).quantity_required ) <> EstDtlTab(i).after_warranty_cost
1681 THEN
1682
1683 Begin
1684
1685 SELECT l.arithmetic_operator,h.currency_code
1686 INTO l_arith_operator, l_currency_code
1687 FROM qp_list_headers h,
1688 qp_list_lines l
1689 WHERE h.list_header_id = l.list_header_id
1690 AND l.list_line_id = l_modifier_line_id;
1691
1692 If l_arith_operator Not In ('%','AMT') Then
1693 --Only amount-based and percent-based modifiers are supported as default modifier in Charges.
1694 FND_MESSAGE.Set_Name('CS','CS_CHG_AMT_PER_MODIF_SUPP');
1695 FND_MSG_PUB.Add;
1696 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1697 p_count => x_msg_count,
1698 p_data => x_msg_data);
1699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1700 End If;
1701
1702 End;
1703
1704 If NVL(l_currency_code,'@~') <> EstDtlTab(i).currency_code
1705 And l_arith_operator = 'AMT'
1706 Then
1707 --Default modifier currency must be the same as charge line currency.
1708 FND_MESSAGE.Set_Name('CS','CS_CHG_SAME_CURR_REQD');
1709 FND_MSG_PUB.Add;
1710 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1711 p_count => x_msg_count,
1712 p_data => x_msg_data);
1713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1714
1715 End If;
1716
1717 l_before_warranty_cost := (nvl(EstDtlTab(i).selling_price, 0) * EstDtlTab(i).quantity_required );
1718 -- Added according to PMs proposed bug fix
1719 IF (EstDtlTab(i).list_price = 0 AND
1720 l_before_warranty_cost <> 0 AND l_arith_operator <> 'AMT') THEN
1721 FND_MESSAGE.Set_Name('CS','CS_CHG_AMT_MODIF_0_PRICE_ITEM');
1722 FND_MSG_PUB.Add;
1723 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1724 p_count => x_msg_count,
1725 p_data => x_msg_data);
1726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1727 END IF;
1728
1729 IF l_before_warranty_cost = 0
1730 Then
1731 If l_arith_operator = 'AMT' Then
1732 l_operand := -1 * nvl (EstDtlTab(i).after_warranty_cost, 0);
1733 Else
1734 --Default modifier must be amount-based in order to support overrides of zero priced items.
1735 FND_MESSAGE.Set_Name('CS','CS_CHG_AMT_MODIF_0_PRICE_ITEM');
1736 FND_MSG_PUB.Add;
1737 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1738 p_count => x_msg_count,
1739 p_data => x_msg_data);
1740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1741 End If;
1742 Elsif l_before_warranty_cost > 0
1743 Then
1744 If l_arith_operator = 'AMT' Then
1745 l_operand := nvl(EstDtlTab(i).selling_price, 0) - nvl(EstDtlTab(i).after_warranty_cost,0)/EstDtlTab(i).quantity_required;
1746 ElsIf l_arith_operator = '%' Then
1747 l_operand := (l_before_warranty_cost - (nvl(EstDtlTab(i).after_warranty_cost, 0))) /l_before_warranty_cost * 100;
1748 End If;
1749 End IF;
1750
1751 IF EstDtlTab(i).line_category_code = 'RETURN'
1752 THEN
1753 l_return_quantity := (EstDtlTab(i).quantity_required * (-1));
1754 l_adjusted_amount := (EstDtlTab(i).after_warranty_cost/l_return_quantity) - EstDtlTab(i).selling_price;
1755 ELSE
1756 l_adjusted_amount := (EstDtlTab(i).after_warranty_cost/EstDtlTab(i).quantity_required) - EstDtlTab(i).selling_price;
1757 END IF;
1758
1759 l_line_price_adj_tbl(j).operation_code := 'CREATE';
1760 l_line_price_adj_tbl(j).qte_line_index := 1;
1761 l_line_price_adj_tbl(j).Modifier_header_id := l_modifier_header_id;
1762 l_line_price_adj_tbl(j).Modifier_line_id := l_modifier_line_id;
1763 l_line_price_adj_tbl(j).operand := l_operand;
1764 l_line_price_adj_tbl(j).adjusted_amount := l_adjusted_amount;
1765 l_line_price_adj_tbl(j).updated_flag := 'Y';
1766 l_line_price_adj_tbl(j).applied_flag := 'Y';
1767 l_line_price_adj_tbl(j).change_reason_code := 'MANUAL';
1768 l_line_price_adj_tbl(j).change_reason_text := 'Manually Applied';
1769 l_line_tbl(j).line_list_price := nvl(EstDtlTab(i).selling_price,0); --5408354
1770 END IF;
1771 -- << End >> Changed the logic of modifier based Calculation based on bug 5408354
1772
1773
1774
1775
1776 /* IF (l_modifier_line_id IS NOT NULL) THEN
1777
1778 l_before_warranty_cost := (nvl(EstDtlTab(i).list_price,0) *
1779 EstDtlTab(i).quantity_required); --4870210
1780 IF l_before_warranty_cost = 0 THEN
1781 l_operand := 100;
1782 ELSE
1783 l_operand := (l_before_warranty_cost -
1784 (nvl(EstDtlTab(i).after_warranty_cost,0)))/l_before_warranty_cost * 100;
1785 END IF;
1786
1787 -- Fix bug 2930729
1788 IF EstDtlTab(i).line_category_code = 'RETURN' then
1789 l_return_quantity := (EstDtlTab(i).quantity_required * (-1));
1790 l_adjusted_amount := (EstDtlTab(i).after_warranty_cost/l_return_quantity) - EstDtlTab(i).list_price;
1791 ELSE
1792 l_adjusted_amount := (EstDtlTab(i).after_warranty_cost/EstDtlTab(i).quantity_required) - EstDtlTab(i).list_price;
1793 END IF;
1794
1795 l_line_price_adj_tbl(j).operation_code := 'CREATE';
1796 l_line_price_adj_tbl(j).qte_line_index := 1;
1797 l_line_price_adj_tbl(j).Modifier_header_id := l_modifier_header_id;
1798 l_line_price_adj_tbl(j).Modifier_line_id := l_modifier_line_id;
1799 l_line_price_adj_tbl(j).operand := l_operand;
1800 -- Bug 2930729 l_line_price_adj_tbl(j).adjusted_amount := l_operand;
1801 l_line_price_adj_tbl(j).adjusted_amount := l_adjusted_amount; -- Bug 2930729
1802 l_line_price_adj_tbl(j).updated_flag := 'Y';
1803 l_line_price_adj_tbl(j).applied_flag := 'Y';
1804 l_line_price_adj_tbl(j).change_reason_code := 'MANUAL';
1805 l_line_price_adj_tbl(j).change_reason_text := 'Manually Applied';
1806
1807 END IF; */
1808 --
1809 --
1810 -- Populate return_serial_number value in OM if the item is not ib_trackable and line type is return.
1811 --
1812 IF (EstDtlTab(i).comms_nl_trackable_flag = 'N' and
1813 EstDtlTab(i).line_category_code = 'RETURN' and
1814 EstDtlTab(i).return_serial_number IS NOT NULL) THEN
1815
1816 l_lot_serial_tbl(1).lot_number := FND_API.G_MISS_CHAR;
1817 l_lot_serial_tbl(1).lot_serial_id := FND_API.G_MISS_NUM;
1818 l_lot_serial_tbl(1).quantity := abs(EstDtlTab(i).quantity_required);
1819 l_lot_serial_tbl(1).from_serial_number := EstDtlTab(i).return_serial_number;
1820 l_lot_serial_tbl(1).to_serial_number := EstDtlTab(i).return_serial_number;
1821 l_lot_serial_tbl(1).operation := 'CREATE';
1822 l_lot_serial_tbl(1).line_index := j;
1823 END IF;
1824 --
1825 --
1826 -- Populate Install Base API records
1827 --
1828 --
1829 IF EstDtlTab(i).comms_nl_trackable_flag = 'Y' THEN
1830
1831 -- Get Transaction type information
1832 BEGIN
1833 SELECT a.transaction_type_id,
1834 b.sub_type_id,
1835 c.transaction_type_id,
1836 nvl(b.src_reference_reqd,'N'),
1837 b.src_change_owner,
1838 b.src_change_owner_to_code,
1839 nvl(b.non_src_reference_reqd,'N'),
1840 b.non_src_change_owner,
1841 b.non_src_change_owner_to_code,
1842 nvl(b.update_ib_flag,'N'),
1843 b.src_return_reqd -- Bug 4586140
1844 INTO l_transaction_type_id,
1845 l_sub_type_id,
1846 l_source_type_id,
1847 l_src_reference_reqd,
1848 l_src_change_owner,
1849 l_src_change_owner_to_code,
1850 l_non_src_reference_reqd,
1851 l_non_src_change_owner,
1852 l_non_src_change_owner_to_code,
1853 l_update_ib_flag,
1854 l_src_return_reqd_flag -- Bug 4586140
1855 FROM CS_TXN_BILLING_TYPES a,
1856 csi_txn_sub_types b,
1857 csi_txn_types c
1858 WHERE a.txn_billing_type_id = EstDtlTab(i).txn_billing_type_id
1859 AND a.transaction_type_id = b.cs_transaction_type_id
1860 AND b.transaction_type_id = c.transaction_type_id
1861 AND c.source_application_id = 660
1862 AND c.source_transaction_type =
1863 decode(EstDtlTab(i).line_category_code,'RETURN', 'RMA_RECEIPT', 'ORDER', 'OM_SHIPMENT',null);
1864 EXCEPTION
1865 WHEN TOO_MANY_ROWS THEN
1866 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_TOO_MANY_SOURCES');
1867 FND_MSG_PUB.Add;
1868 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1869 p_count => x_msg_count,
1870 p_data => x_msg_data);
1871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1872
1873 WHEN NO_DATA_FOUND THEN
1874 l_update_ib_flag := 'N';
1875 /* Commenting out this exception as part of IB changes */
1876 /* FND_MESSAGE.Set_Name('CS','CS_CHG_IB_NO_VALID_TXN_TYPE');
1877 FND_MSG_PUB.Add;
1878 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1879 p_count => x_msg_count,
1880 p_data => x_msg_data);
1881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
1882 END;
1883
1884 -- Populate installation details if there is instance information and
1885 -- update_ib_flag is 'Y',but do not raise any error message.
1886 -- Bug fix 3564034
1887
1888 IF l_update_ib_flag = 'Y' THEN
1889 IF EstDtlTab(i).customer_product_id is not null THEN
1890 -- For return lines for IB, pass serial number to OM
1891 IF EstDtlTab(i).line_category_code = 'RETURN' THEN
1892 l_ib_serial_number := null;
1893 BEGIN
1894 select serial_number
1895 into l_ib_serial_number
1896 from CSI_ITEM_INSTANCES
1897 where instance_id = EstDtlTab(i).customer_product_id;
1898 EXCEPTION
1899 -- Serial_number is available only if the instance is serialized
1900 /* WHEN NO_DATA_FOUND THEN
1901 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_MISSING_INSTANCE');
1902 FND_MSG_PUB.Add;
1903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
1904 WHEN OTHERS THEN
1905 RAISE;
1906 END;
1907 IF l_ib_serial_number is not null THEN
1908 l_lot_serial_tbl(1).lot_number := FND_API.G_MISS_CHAR;
1909 l_lot_serial_tbl(1).lot_serial_id := FND_API.G_MISS_NUM;
1910 l_lot_serial_tbl(1).quantity := abs(EstDtlTab(i).quantity_required);
1911 l_lot_serial_tbl(1).from_serial_number := l_ib_serial_number;
1912 l_lot_serial_tbl(1).to_serial_number := l_ib_serial_number;
1913 l_lot_serial_tbl(1).operation := 'CREATE';
1914 l_lot_serial_tbl(1).line_index := j;
1915 END IF;
1916 /* ELSE
1917 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_MISSING_INSTANCE');
1918 FND_MSG_PUB.Add;
1919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
1920 END IF;
1921 END IF;
1922
1923
1924 /** Raise an error message , if the src_reference_reqd flag or the non_src_reference_reqd_flag
1925 are 'Y',update_ib_flag is 'Y' and the instance_id is null ***/
1926
1927 IF (l_src_reference_reqd = 'Y' OR
1928 l_non_src_reference_reqd = 'Y') AND
1929 l_update_ib_flag = 'Y' AND
1930 EstDtlTab(i).customer_product_id IS NULL THEN
1931
1932 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_MISSING_INSTANCE');
1933 FND_MSG_PUB.Add;
1934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1935 END IF;
1936
1937 --
1938 -- Clear IB structures
1939 --
1940 csi_txn_line_rec := csi_txn_line_rec_null;
1941 csi_txn_line_detail_tbl.delete;
1942 csi_txn_party_detail_tbl.delete;
1943 csi_txn_pty_acct_detail_tbl.delete;
1944 csi_txn_ii_rltns_tbl.delete;
1945 csi_txn_org_assgn_tbl.delete;
1946 csi_txn_ext_attrib_vals_tbl.delete;
1947 csi_txn_systems_tbl.delete;
1948 l_internal_party_id := null;
1949 l_instance_party_id := null;
1950
1951 --
1952 -- Create Transaction Line Record
1953 --
1954 csi_txn_line_rec.source_transaction_table := 'OE_ORDER_LINES_ALL';
1955 csi_txn_line_rec.source_transaction_type_id := l_source_type_id;
1956
1957 --
1958 -- Create IB Transaction Line Detail and Relationship Record(s)
1959 -- Fix for bug:3564034
1960 IF EstDtlTab(i).line_category_code = 'RETURN' and
1961 EstDtlTab(i).customer_product_id is not null then
1962 csi_txn_line_detail_tbl(1).sub_type_id := l_sub_type_id;
1963 csi_txn_line_detail_tbl(1).source_transaction_flag := 'Y';
1964
1965 IF (l_src_reference_reqd = 'Y' OR
1966 l_src_reference_reqd = 'N') AND
1967 (EstDtlTab(i).Customer_product_id is not null) THEN
1968 csi_txn_line_detail_tbl(1).instance_id := EstDtlTab(i).Customer_Product_Id;
1969 csi_txn_line_detail_tbl(1).instance_exists_flag := 'Y';
1970 ELSE
1971 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_INVALID_RET_TXN_DATA');
1972 FND_MSG_PUB.Add;
1973 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
1974 p_count => x_msg_count,
1975 p_data => x_msg_data);
1976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1977 -- null;
1978
1979 END IF;
1980
1981 IF EstDtlTab(i).Item_revision is not null THEN
1982 csi_txn_line_detail_tbl(1).inventory_revision := EstDtlTab(i).Item_revision;
1983 ELSE
1984 csi_txn_line_detail_tbl(1).inventory_revision := FND_API.G_MISS_CHAR;
1985 END IF;
1986
1987 csi_txn_line_detail_tbl(1).inventory_item_id := EstDtlTab(i).Inventory_Item_id;
1988 csi_txn_line_detail_tbl(1).inv_organization_id := CS_STD.Get_Item_Valdn_Orgzn_ID;
1989 csi_txn_line_detail_tbl(1).unit_of_measure := EstDtlTab(i).unit_of_measure_code;
1990 csi_txn_line_detail_tbl(1).quantity := abs(EstDtlTab(i).quantity_required);
1991
1992 IF EstDtlTab(i).installed_cp_return_by_date is not null THEN
1993 csi_txn_line_detail_tbl(1).return_by_date := EstDtlTab(i).installed_cp_return_by_date;
1994 ELSE
1995 csi_txn_line_detail_tbl(1).return_by_date := FND_API.G_MISS_DATE;
1996 END IF;
1997
1998 ELSE -- Shipment Line
1999 csi_txn_line_detail_tbl(1).sub_type_id := l_sub_type_id;
2000 csi_txn_line_detail_tbl(1).source_transaction_flag := 'Y';
2001
2002 csi_txn_line_detail_tbl(1).instance_id := FND_API.G_MISS_NUM;
2003 csi_txn_line_detail_tbl(1).instance_exists_flag := 'N';
2004
2005 IF EstDtlTab(i).Item_revision is not null THEN
2006 csi_txn_line_detail_tbl(1).inventory_revision := EstDtlTab(i).Item_revision;
2007 ELSE
2008 csi_txn_line_detail_tbl(1).inventory_revision := FND_API.G_MISS_CHAR;
2009 END IF;
2010
2011 csi_txn_line_detail_tbl(1).inventory_item_id := EstDtlTab(i).Inventory_Item_id;
2012 csi_txn_line_detail_tbl(1).inv_organization_id := CS_STD.Get_Item_Valdn_Orgzn_ID;
2013 csi_txn_line_detail_tbl(1).unit_of_measure := EstDtlTab(i).unit_of_measure_code;
2014 csi_txn_line_detail_tbl(1).quantity := abs(EstDtlTab(i).quantity_required);
2015
2016 /*
2017 -- Bug 4586140
2018 IF l_src_return_reqd_flag = 'Y' THEN
2019 IF EstDtlTab(i).installed_cp_return_by_date is not null THEN
2020 csi_txn_line_detail_tbl(1).return_by_date := EstDtlTab(i).installed_cp_return_by_date;
2021 ELSE
2022 csi_txn_line_detail_tbl(1).return_by_date := FND_API.G_MISS_DATE;
2023 END IF;
2024 END IF;
2025 */
2026 -- Commented the above and uncommented the below IF block for Bug# 5136853.
2027 IF EstDtlTab(i).new_cp_return_by_date is not null THEN
2028 csi_txn_line_detail_tbl(1).return_by_date := EstDtlTab(i).new_cp_return_by_date;
2029 ELSE
2030 csi_txn_line_detail_tbl(1).return_by_date := FND_API.G_MISS_DATE;
2031 END IF;
2032
2033 -- Referenced IB (non-source)
2034 IF l_non_src_reference_reqd = 'Y'
2035 OR l_non_src_reference_reqd = 'N'
2036 AND EstDtlTab(i).Customer_product_id is not null THEN
2037 csi_txn_line_detail_tbl(2).sub_type_id := l_sub_type_id;
2038 csi_txn_line_detail_tbl(2).source_transaction_flag := 'N';
2039
2040 IF EstDtlTab(i).Customer_product_id is not null THEN
2041 csi_txn_line_detail_tbl(2).instance_id := EstDtlTab(i).Customer_Product_Id;
2042 csi_txn_line_detail_tbl(2).instance_exists_flag := 'Y';
2043 -- fix bug:3593660
2044 csi_txn_line_detail_tbl(2).assc_txn_line_detail_id := 1;
2045
2046 /* Do not raise any error message if instance is missing.New 11.5.10 changes */
2047
2048 /* ELSE
2049 FND_MESSAGE.Set_Name('CS','CS_CHG_IB_INVALID_SHP_TXN_DATA');
2050 FND_MSG_PUB.Add;
2051 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2052 p_count => x_msg_count,
2053 p_data => x_msg_data);
2054 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
2055 END IF;
2056
2057 /* 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*/
2058
2059 OPEN get_inv_item_id(EstDtlTab(i).customer_product_id);
2060 FETCH get_inv_item_id
2061 INTO l_inventory_item_id;
2062 CLOSE get_inv_item_id;
2063 csi_txn_line_detail_tbl(2).inventory_item_id := l_inventory_item_id;
2064 csi_txn_line_detail_tbl(2).inv_organization_id := CS_STD.Get_Item_Valdn_Orgzn_ID;
2065 csi_txn_line_detail_tbl(2).unit_of_measure := EstDtlTab(i).unit_of_measure_code;
2066 csi_txn_line_detail_tbl(2).quantity := abs(EstDtlTab(i).quantity_required);
2067
2068 /*
2069 IF EstDtlTab(i).Item_revision is not null THEN
2070 csi_txn_line_detail_tbl(2).inventory_revision := EstDtlTab(i).Item_revision;
2071 ELSE
2072 csi_txn_line_detail_tbl(2).inventory_revision := FND_API.G_MISS_CHAR;
2073 END IF;
2074 */
2075
2076 -- Uncommented the below IF block for Bug# 5136853.
2077 IF EstDtlTab(i).installed_cp_return_by_date is not null THEN
2078 csi_txn_line_detail_tbl(2).return_by_date := EstDtlTab(i).installed_cp_return_by_date;
2079 ELSE
2080 csi_txn_line_detail_tbl(2).return_by_date := FND_API.G_MISS_DATE;
2081 END IF;
2082
2083
2084 -- Create relationship between two txn line detail records
2085 csi_txn_ii_rltns_tbl(1).subject_id := 2;
2086 csi_txn_ii_rltns_tbl(1).object_id := 1;
2087 csi_txn_ii_rltns_tbl(1).relationship_type_code := 'REPLACED-BY';
2088
2089 END IF; -- IF l_non_src_reference_reqd = 'Y'
2090 END IF; -- IF EstDtlTab(i).line_category_code = 'RETURN'
2091
2092 --
2093 -- Create IB Party details and account details records,when p_account_id is not null.
2094 --
2095 IF EstDtlTab(i).account_id IS NOT NULL THEN
2096 IF EstDtlTab(i).line_category_code = 'RETURN' then
2097 IF l_src_change_owner = 'Y' THEN
2098 IF l_src_change_owner_to_code = 'I' THEN
2099 BEGIN
2100 SELECT internal_party_id
2101 INTO l_internal_party_id
2102 FROM csi_install_parameters
2103 WHERE rownum = 1;
2104
2105 IF l_internal_party_id is null THEN
2106 RAISE l_IB_ERROR;
2107 END IF;
2108 EXCEPTION
2109 WHEN OTHERS THEN
2110 FND_MESSAGE.Set_Name('CS','CS_CHG_INTERNAL_PARTY_NOT_DEF');
2111 FND_MSG_PUB.Add;
2112 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2113 p_count => x_msg_count,
2114 p_data => x_msg_data);
2115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2116 END;
2117
2118 BEGIN
2119 SELECT instance_party_id
2120 INTO l_instance_party_id
2121 FROM CSI_I_PARTIES
2122 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
2123 AND relationship_type_code = 'OWNER'
2124 AND party_id = (SELECT party_id
2125 FROM hz_cust_accounts
2126 WHERE cust_account_id = EstDtlTab(i).account_id);
2127
2128 EXCEPTION
2129 WHEN OTHERS THEN
2130 FND_MESSAGE.Set_Name('CS','CS_CHG_INSTANCE_NOT_OWN_BY_PTY');
2131 FND_MSG_PUB.Add;
2132 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2133 p_count => x_msg_count,
2134 p_data => x_msg_data);
2135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2136 END;
2137
2138 csi_txn_party_detail_tbl(1).instance_party_id := l_instance_party_id;
2139 csi_txn_party_detail_tbl(1).party_source_table := 'HZ_PARTIES';
2140 csi_txn_party_detail_tbl(1).party_source_id := l_internal_party_id;
2141 csi_txn_party_detail_tbl(1).relationship_type_code := 'OWNER';
2142 csi_txn_party_detail_tbl(1).txn_line_details_index := 1;
2143 csi_txn_party_detail_tbl(1).contact_flag := 'N';
2144 csi_txn_pty_acct_detail_tbl.delete;
2145 ELSE
2146 FND_MESSAGE.Set_Name('CS','CS_CHG_RTN_TO_EXT_PTY_NOT_SUP');
2147 FND_MSG_PUB.Add;
2148 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2149 p_count => x_msg_count,
2150 p_data => x_msg_data);
2151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2152 END IF; -- IF l_src_change_owner_to_code = 'I'
2153
2154 --taklam
2155 ---NEW CODE
2156 ELSIF l_src_change_owner = 'N' AND l_src_change_owner_to_code is NULL THEN
2157 BEGIN
2158 SELECT internal_party_id
2159 INTO l_internal_party_id
2160 FROM csi_install_parameters
2161 WHERE rownum = 1;
2162
2163
2164 IF l_internal_party_id is null THEN
2165 RAISE l_IB_ERROR;
2166 END IF;
2167 EXCEPTION
2168 WHEN OTHERS THEN
2169 FND_MESSAGE.Set_Name('CS','CS_CHG_INTERNAL_PARTY_NOT_DEF');
2170 FND_MSG_PUB.Add;
2171 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2172 p_count => x_msg_count,
2173 p_data => x_msg_data);
2174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2175 END;
2176
2177 BEGIN
2178 IF EstDtlTab(i).Customer_Product_Id is not NULL THEN
2179 SELECT instance_party_id
2180 INTO l_instance_party_id
2181 FROM CSI_I_PARTIES
2182 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
2183 AND relationship_type_code = 'OWNER'
2184 AND party_id IN (l_internal_party_id,EstDtlTab(i).customer_id);
2185 ELSE
2186 NULL; -- submit successful. But, do not create installation details.
2187 END IF;
2188
2189 EXCEPTION
2190 WHEN OTHERS THEN
2191 FND_MESSAGE.Set_Name('CS','CS_CHG_INSTANCE_NOT_OWN_BY_PTY');
2192 FND_MSG_PUB.Add;
2193 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2194 p_count => x_msg_count,
2195 p_data => x_msg_data);
2196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2197 END;
2198
2199 csi_txn_party_detail_tbl(1).instance_party_id := l_instance_party_id;
2200 csi_txn_party_detail_tbl(1).party_source_table := 'HZ_PARTIES';
2201 csi_txn_party_detail_tbl(1).party_source_id := l_internal_party_id;
2202 csi_txn_party_detail_tbl(1).relationship_type_code := 'OWNER';
2203 csi_txn_party_detail_tbl(1).txn_line_details_index := 1;
2204 csi_txn_party_detail_tbl(1).contact_flag := 'N';
2205 csi_txn_pty_acct_detail_tbl.delete;
2206 --taklam
2207
2208 ELSE
2209 csi_txn_party_detail_tbl.delete;
2210 csi_txn_pty_acct_detail_tbl.delete;
2211 END IF; -- IF l_src_change_owner = 'Y'
2212
2213 ELSE -- shipment line
2214 IF l_src_change_owner = 'Y' THEN
2215 -- Create Party record
2216 csi_txn_party_detail_tbl(1).party_source_table := 'HZ_PARTIES';
2217 csi_txn_party_detail_tbl(1).party_source_id := EstDtlTab(i).customer_id;
2218 csi_txn_party_detail_tbl(1).relationship_type_code := 'OWNER';
2219 csi_txn_party_detail_tbl(1).txn_line_details_index := 1;
2220 csi_txn_party_detail_tbl(1).contact_flag := 'N';
2221
2222 -- Create Account record
2223 csi_txn_pty_acct_detail_tbl(1).account_id := EstDtlTab(i).account_id;
2224 csi_txn_pty_acct_detail_tbl(1).relationship_type_code := 'OWNER';
2225 csi_txn_pty_acct_detail_tbl(1).txn_party_details_index := 1;
2226 csi_txn_pty_acct_detail_tbl(1).active_start_date := sysdate;
2227 END IF;
2228 END IF; -- IF EstDtlTab(i).line_category_code = 'RETURN'
2229
2230 END IF; --IF account_id is not null.
2231
2232 END IF; -- IF Update_Ib_Flag = Y
2233
2234 END IF; -- If COMMS_NL_TRACKABLE_FLAG = Y
2235
2236 --
2237 -- The ASO debug statements are replaced with the FND Logging
2238
2239 IF FND_LOG.level_statement >= FND_LOG.g_current_runtime_level
2240 THEN
2241 FND_LOG.String
2242 ( FND_LOG.level_statement, L_LOG_MODULE , '==================================================='
2243 );
2244 FND_LOG.String
2245 ( FND_LOG.level_statement, L_LOG_MODULE , 'Beginning Charges submission'
2246 );
2247 FND_LOG.String
2248 ( FND_LOG.level_statement, L_LOG_MODULE , 'Item revision is: ' || EstDtlTab(i).item_revision
2249 );
2250 FND_LOG.String
2251 ( FND_LOG.level_statement, L_LOG_MODULE , 'Estimate Detail_id is: ' || EstDtlTab(i).estimate_detail_id
2252 );
2253 FND_LOG.String
2254 ( FND_LOG.level_statement, L_LOG_MODULE , 'Selling_price: '|| EstDtlTab(i).selling_price
2255 ) ;
2256 FND_LOG.String
2257 ( FND_LOG.level_statement, L_LOG_MODULE , 'After Warranty Cost: ' || EstDtlTab(i).after_warranty_cost
2258 );
2259 FND_LOG.String
2260 ( FND_LOG.level_statement, L_LOG_MODULE , 'Incident Id is: '|| EstDtlTab(i).incident_id
2261 );
2262 FND_LOG.String
2263 ( FND_LOG.level_statement, L_LOG_MODULE , 'Charges Invoice to party site id: ' || EstDtlTab(i).invoice_to_org_id
2264 );
2265 FND_LOG.String
2266 ( FND_LOG.level_statement, L_LOG_MODULE , 'Charges Ship to party site id: ' || EstDtlTab(i).ship_to_org_id
2267 );
2268 FND_LOG.String
2269 ( FND_LOG.level_statement, L_LOG_MODULE , ' '
2270 );
2271
2272 FND_LOG.String
2273 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_control_rec.book_flag is: ' || l_control_rec.book_flag
2274 );
2275 FND_LOG.String
2276 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_control_rec.calculate_price is: ' || l_control_rec.calculate_price
2277 );
2278 FND_LOG.String
2279 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.Order_id is: '|| l_header_rec.order_id
2280 );
2281 FND_LOG.String
2282 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.Order_type_id is: '|| l_header_rec.order_type_id
2283 );
2284 FND_LOG.String
2285 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.quote_source_code is: '|| l_header_rec.quote_source_code
2286 );
2287 FND_LOG.String
2288 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute1 is: '|| l_header_rec.attribute1
2289 );
2290 FND_LOG.String
2291 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute2 is: '|| l_header_rec.attribute2
2292 );
2293 FND_LOG.String
2294 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute3 is: '|| l_header_rec.attribute3
2295 );
2296 FND_LOG.String
2297 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute4 is: '|| l_header_rec.attribute4
2298 );
2299 FND_LOG.String
2300 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute5 is: '|| l_header_rec.attribute5
2301 );
2302 FND_LOG.String
2303 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute6 is: '|| l_header_rec.attribute6
2304 );
2305 FND_LOG.String
2306 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute7 is: '|| l_header_rec.attribute7
2307 );
2308 FND_LOG.String
2309 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute8 is: '|| l_header_rec.attribute8
2310 );
2311 FND_LOG.String
2312 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute9 is: '|| l_header_rec.attribute9
2313 );
2314 FND_LOG.String
2315 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute10 is: '|| l_header_rec.attribute10
2316 );
2317 FND_LOG.String
2318 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute11 is: '|| l_header_rec.attribute11
2319 );
2320 FND_LOG.String
2321 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute12 is: '|| l_header_rec.attribute12
2322 );
2323 FND_LOG.String
2324 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute13 is: '|| l_header_rec.attribute13
2325 );
2326 FND_LOG.String
2327 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute14 is: '|| l_header_rec.attribute14
2328 );
2329 FND_LOG.String
2330 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.attribute15 is: '|| l_header_rec.attribute15
2331 );
2332 FND_LOG.String
2333 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.Org_Id is: '|| l_header_rec.org_id
2334 );
2335 FND_LOG.String
2336 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.party_id (Customer_id) is: ' || l_header_rec.party_id
2337 );
2338 FND_LOG.String
2339 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.cust_account_id (Customer_account_id) is: ' || l_header_rec.cust_account_id
2340 );
2341 FND_LOG.String
2342 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.quote_header_id is: ' || l_header_rec.quote_header_id
2343 );
2344 FND_LOG.String
2345 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.original_system_reference is: ' || l_header_rec.original_system_reference
2346 );
2347 FND_LOG.String
2348 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.quote_number is: ' || l_header_rec.quote_number
2349 );
2350 FND_LOG.String
2351 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.exchange_rate is: '|| l_header_rec.exchange_rate
2352 );
2353 FND_LOG.String
2354 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.exchange_type_code is: '|| l_header_rec.exchange_type_code
2355 );
2356 FND_LOG.String
2357 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.exchange_rate_date is: '|| l_header_rec.exchange_rate_date
2358 );
2359 FND_LOG.String
2360 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.price_list_id is: ' || l_header_rec.price_list_id
2361 );
2362 FND_LOG.String
2363 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_header_rec.currency_code is: ' || l_header_rec.currency_code
2364 );
2365 FND_LOG.String
2366 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_Header_rec.invoice_to_cust_account_id: ' || l_header_rec.invoice_to_cust_account_id
2367 );
2368 FND_LOG.String
2369 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_Header_rec.invoice_to_party_id: ' || l_header_rec.invoice_to_party_id
2370 );
2371 FND_LOG.String
2372 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_Header_rec.invoice_to_party_site_id: ' || l_header_rec.invoice_to_party_site_id
2373 );
2374 FND_LOG.String
2375 ( FND_LOG.level_statement, L_LOG_MODULE , 'Update_Ib_Flag: ' || l_update_ib_flag
2376 );
2377 FND_LOG.String
2378 ( FND_LOG.level_statement, L_LOG_MODULE , 'Src_Reference_Reqd_Flag: ' || l_src_reference_reqd
2379 );
2380 FND_LOG.String
2381 ( FND_LOG.level_statement, L_LOG_MODULE , 'Non_Src_Reference_Reqd_Flag: ' || l_non_src_reference_reqd
2382 );
2383
2384 if l_hd_payment_tbl.count = 0 then
2385 FND_LOG.String
2386 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_payment_tbl does not exist'
2387 );
2388 else
2389 FND_LOG.String
2390 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_payment_tbl.cust_po_number is: '|| l_hd_payment_tbl(j).cust_po_number
2391 );
2392 FND_LOG.String
2393 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_payment_tbl.payment_type_code is: '|| l_hd_payment_tbl(j).payment_type_code
2394 );
2395 end if;
2396
2397 if l_hd_shipment_tbl.count = 0 then
2398 FND_LOG.String
2399 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_shipment_tbl does not exist'
2400 );
2401 else
2402 FND_LOG.String
2403 ( 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
2404 );
2405 FND_LOG.String
2406 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_hd_shipment_tbl.ship_to_party_id: ' || l_hd_shipment_tbl(j).ship_to_party_id
2407 );
2408 FND_LOG.String
2409 ( 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
2410 );
2411 end if;
2412
2413 if l_line_tbl.count = 0 then
2414 FND_LOG.String
2415 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl does not exist'
2416 );
2417 else
2418 FND_LOG.String
2419 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.operation_code is: '|| l_line_tbl(j).operation_code
2420 );
2421 FND_LOG.String
2422 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.inventory_item_id is: ' || l_line_tbl(j).inventory_item_id
2423 );
2424 FND_LOG.String
2425 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.UOM_code is: ' || l_line_tbl(j).UOM_code
2426 );
2427 FND_LOG.String
2428 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.order_line_type_id is: ' || l_line_tbl(j).order_line_type_id
2429 );
2430 FND_LOG.String
2431 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.quantity is: ' || l_line_tbl(j).quantity
2432 );
2433 FND_LOG.String
2434 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.quote_line_id is: ' || l_line_tbl(j).quote_line_id
2435 );
2436 FND_LOG.String
2437 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.price_list_id is: ' || l_line_tbl(j).price_list_id
2438 );
2439 FND_LOG.String
2440 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.line_list_price is: ' || l_line_tbl(j).line_list_price
2441 );
2442 FND_LOG.String
2443 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.line_quote_price is: ' || l_line_tbl(j).line_quote_price
2444 );
2445 FND_LOG.String
2446 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.line_category_code is: ' || l_line_tbl(j).line_category_code
2447 );
2448 FND_LOG.String
2449 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.invoice_to_cust_account_id: ' || l_line_tbl(j).invoice_to_cust_account_id
2450 );
2451 FND_LOG.String
2452 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.invoice_to_party_id: ' || l_line_tbl(j).invoice_to_party_id
2453 );
2454 FND_LOG.String
2455 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_tbl.invoice_to_party_site_id: ' || l_line_tbl(j).invoice_to_party_site_id
2456 );
2457 end if;
2458
2459 if l_ln_shipment_tbl.count = 0 then
2460 FND_LOG.String
2461 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl does not exist'
2462 );
2463 else
2464 FND_LOG.String
2465 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.operation_code is: '|| l_ln_shipment_tbl(j).operation_code
2466 );
2467 FND_LOG.String
2468 ( 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
2469 );
2470 FND_LOG.String
2471 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.ship_to_party_id: ' || l_ln_shipment_tbl(j).ship_to_party_id
2472 );
2473 FND_LOG.String
2474 ( 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
2475 );
2476 FND_LOG.String
2477 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.quote_line_id is: ' || l_ln_shipment_tbl(j).quote_line_id
2478 );
2479 FND_LOG.String
2480 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.quote_header_id is: ' || l_ln_shipment_tbl(j).quote_header_id
2481 );
2482 FND_LOG.String
2483 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.quantity is: ' || l_ln_shipment_tbl(j).quantity
2484 );
2485 FND_LOG.String
2486 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_ln_shipment_tbl.qte_line_index is: ' || l_ln_shipment_tbl(j).qte_line_index
2487 );
2488 end if;
2489
2490 if l_line_dtl_tbl.count = 0 then
2491 FND_LOG.String
2492 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_dtl_tbl does not exist'
2493 );
2494 else
2495 FND_LOG.String
2496 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_dtl_tbl.return_reason_code is: ' || l_line_dtl_tbl(j).return_reason_code
2497 );
2498 FND_LOG.String
2499 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_dtl_tbl.qte_line_index is: ' || l_line_dtl_tbl(j).qte_line_index
2500 );
2501 end if;
2502
2503 if l_line_price_adj_tbl.count = 0 then
2504 FND_LOG.String
2505 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl does not exist'
2506 );
2507 else
2508 FND_LOG.String
2509 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.operation_code is: ' || l_line_price_adj_tbl(j).operation_code
2510 );
2511 FND_LOG.String
2512 ( 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
2513 );
2514 FND_LOG.String
2515 ( 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
2516 );
2517 FND_LOG.String
2518 ( 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
2519 );
2520 FND_LOG.String
2521 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.operand is: ' || l_line_price_adj_tbl(j).operand
2522 );
2523 FND_LOG.String
2524 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.adjusted_amount is: ' || l_line_price_adj_tbl(j).adjusted_amount
2525 );
2526 FND_LOG.String
2527 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.updated_flag is: ' || l_line_price_adj_tbl(j).updated_flag
2528 );
2529 FND_LOG.String
2530 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.applied_flag is: ' || l_line_price_adj_tbl(j).applied_flag
2531 );
2532 FND_LOG.String
2533 ( 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
2534 );
2535 FND_LOG.String
2536 ( 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
2537 );
2538 end if;
2539
2540 if l_lot_serial_tbl.count = 0 then
2541 FND_LOG.String
2542 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl does not exist'
2543 );
2544 else
2545 FND_LOG.String
2546 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.lot_number is: ' || l_lot_serial_tbl(1).lot_number
2547 );
2548 FND_LOG.String
2549 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.lot_serial_id is: ' || l_lot_serial_tbl(1).lot_serial_id
2550 );
2551 FND_LOG.String
2552 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.quantity is: ' || l_lot_serial_tbl(1).quantity
2553 );
2554 FND_LOG.String
2555 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.from_serial_number is: ' || l_lot_serial_tbl(1).from_serial_number
2556 );
2557 FND_LOG.String
2558 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.to_serial_number is: ' || l_lot_serial_tbl(1).to_serial_number
2559 );
2560 FND_LOG.String
2561 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.operation is: ' || l_lot_serial_tbl(1).operation
2562 );
2563 FND_LOG.String
2564 ( FND_LOG.level_statement, L_LOG_MODULE , 'l_lot_serial_tbl.line_index is: ' || l_lot_serial_tbl(1).line_index
2565 );
2566 end if;
2567
2568 FND_LOG.String
2569 ( FND_LOG.level_statement, L_LOG_MODULE , 'Ending Charges submission'
2570 );
2571 FND_LOG.String
2572 ( FND_LOG.level_statement, L_LOG_MODULE , '==================================================='
2573 );
2574
2575 END IF;
2576
2577
2578 -- Validate the ship to customer account site and account site use are correct.
2579 validate_acct_site_uses(
2580 p_org_id => EstDtlTab(i).org_id,
2581 p_party_site_id => EstDtlTab(i).ship_to_org_id,
2582 p_account_id => l_ship_to_cust_account_id,
2583 p_site_use_code => 'SHIP_TO',
2584 x_msg_data => x_msg_data,
2585 x_msg_count => x_msg_count,
2586 x_return_status => x_return_status);
2587
2588 -- dbms_output.put_line('In the validate_acct_site_call');
2589 -- dbms_output.put_line('Return_Status' || x_return_status);
2590
2591 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2592 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2593 p_count => x_msg_count,
2594 p_data => x_msg_data);
2595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2596 END IF;
2597
2598 -- Validate the bill to customer account site and account site use are correct.
2599 validate_acct_site_uses(
2600 p_org_id => EstDtlTab(i).org_id,
2601 p_party_site_id => EstDtlTab(i).invoice_to_org_id,
2602 p_account_id => l_invoice_to_cust_account_id,
2603 p_site_use_code => 'BILL_TO',
2604 x_msg_data => x_msg_data,
2605 x_msg_count => x_msg_count,
2606 x_return_status => x_return_status);
2607
2608 -- dbms_output.put_line('In the validate_acct_site_call2');
2609 -- dbms_output.put_line('Return_Status2' || x_return_status);
2610
2611 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2612 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2613 p_count => x_msg_count,
2614 p_data => x_msg_data);
2615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2616 END IF;
2617 --
2618
2619 -- r12 code start
2620
2621 IF (MO_GLOBAL.check_valid_org(l_org_id) ='N')
2622 THEN
2623 FND_MSG_PUB.initialize;
2624 FND_MESSAGE.Set_Name('CS','CS_CHG_NEW_CONTEXT_OU_MISMATCH') ;
2625 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full) ;
2626 FND_MSG_PUB.Add ;
2627 raise FND_API.G_EXC_ERROR ;
2628 END IF;
2629
2630 mo_global.set_policy_context('S',l_org_id);
2631
2632 -- end r12
2633
2634 -- CALL OC'S CREATE_ORDER API FOR CREATING A NEW ORDER
2635 -- UPDATE ORDER API FOR ADDING TO AN EXISTING ORDER
2636
2637 IF (EstDtlTab(i).add_to_order_flag = 'F') THEN
2638
2639 ASO_ORDER_INT.Create_order(
2640 P_Api_Version_Number => 1.0,
2641 P_Qte_Rec => l_header_rec,
2642 P_Header_Shipment_Tbl => l_hd_shipment_tbl,
2643 P_Qte_Line_Tbl => l_line_tbl,
2644 P_Qte_Line_Dtl_Tbl => l_line_dtl_tbl,
2645 P_Line_Shipment_Tbl => l_ln_shipment_tbl,
2646 P_Header_Payment_Tbl => l_hd_payment_tbl,
2647 P_Line_Price_Adj_Tbl => l_line_price_adj_tbl,
2648 P_Lot_Serial_Tbl => l_lot_serial_tbl,
2649 P_Control_Rec => l_control_rec,
2650 X_Order_Header_Rec => x_order_header_rec,
2651 X_Order_Line_Tbl => x_order_line_tbl,
2652 X_Return_Status => x_return_status,
2653 X_Msg_Count => x_msg_count,
2654 X_Msg_Data => x_msg_data
2655 );
2656
2657 ELSIF (EstDtlTab(i).add_to_order_flag = 'Y') or
2658 (l_order_header_id <> -999) THEN
2659
2660 -- dbms_output.put_line('Calling Update_Order');
2661
2662 ASO_ORDER_INT.Update_order(
2663 P_Api_Version_Number => 1.0,
2664 P_Qte_Rec => l_header_rec,
2665 P_Qte_Line_Tbl => l_line_tbl,
2666 P_Qte_Line_Dtl_Tbl => l_line_dtl_tbl,
2667 P_Line_Shipment_Tbl => l_ln_shipment_tbl,
2668 P_Header_Payment_Tbl => l_hd_payment_tbl,
2669 P_Line_Price_Adj_Tbl => l_line_price_adj_tbl,
2670 P_Lot_Serial_Tbl => l_lot_serial_tbl,
2671 P_Control_Rec => l_control_rec,
2672 X_Order_Header_Rec => x_order_header_rec,
2673 X_Order_Line_Tbl => x_order_line_tbl,
2674 X_Return_Status => x_return_status,
2675 X_Msg_Count => x_msg_count,
2676 X_Msg_Data => x_msg_data
2677 );
2678 ELSE
2679
2680 -- dbms_output.put_line('Calling Create_Order');
2681
2682 ASO_ORDER_INT.Create_order(
2683 P_Api_Version_Number => 1.0,
2684 P_Qte_Rec => l_header_rec,
2685 P_Header_Shipment_Tbl => l_hd_shipment_tbl,
2686 P_Qte_Line_Tbl => l_line_tbl,
2687 P_Qte_Line_Dtl_Tbl => l_line_dtl_tbl,
2688 P_Line_Shipment_Tbl => l_ln_shipment_tbl,
2689 P_Header_Payment_Tbl => l_hd_payment_tbl,
2690 P_Line_Price_Adj_Tbl => l_line_price_adj_tbl,
2691 P_Lot_Serial_Tbl => l_lot_serial_tbl,
2692 P_Control_Rec => l_control_rec,
2693 X_Order_Header_Rec => x_order_header_rec,
2694 X_Order_Line_Tbl => x_order_line_tbl,
2695 X_Return_Status => x_return_status,
2696 X_Msg_Count => x_msg_count,
2697 X_Msg_Data => x_msg_data
2698 );
2699
2700 END IF; -- Add_To_Order
2701
2702
2703 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2704 -- moved this to elsif below.
2705 -- X_ORDER_HEADER_ID := X_ORDER_HEADER_REC.ORDER_HEADER_ID;
2706
2707 IF X_ORDER_HEADER_REC.ORDER_HEADER_ID IS NULL THEN
2708 FND_MESSAGE.Set_Name('CS','CS_CHG_NO_ORD_NUM_RETURNED');
2709 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
2710 FND_MSG_PUB.Add;
2711 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2712 p_count => x_msg_count,
2713 p_data => x_msg_data);
2714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2715
2716 -- Need to verify if we need to trap this error message
2717 --
2718
2719 ELSIF X_ORDER_HEADER_REC.ORDER_HEADER_ID IS NOT NULL THEN
2720 X_ORDER_HEADER_ID := X_ORDER_HEADER_REC.ORDER_HEADER_ID;
2721 END IF;
2722 -- Moving this to after IB call so that the record is not locked
2723 -- fix bug:3545283
2724 /* FOR k in 1..x_order_line_tbl.count LOOP
2725
2726 -- UPDATE ESTIMATE_DETAILS WITH ORDER_HEADER_ID AND ORDER_LINE_ID.
2727 -- Changes for 11.5.10.
2728 -- NULL Values should be passed for submit_error_message,
2729 -- submit_restriction_message and line_submitted columns as the
2730 -- order creation is successful.
2731
2732 Update_Estimate_Details(EstDtlTab(i).estimate_detail_id,
2733 x_order_header_id,
2734 x_order_line_tbl(k).order_line_id,
2735 NULL,
2736 NULL,
2737 NULL,
2738 p_submit_from_system);
2739
2740
2741 END LOOP; */
2742
2743 -- Initialize xi_return_status.
2744
2745 xi_return_status := FND_API.G_RET_STS_SUCCESS;
2746
2747 /* This will call the wrapper API to raise the Business Event oracle.apps.cs.chg.Charges.submitted
2748 and their subscriptions.
2749 All the custom code exists in the subscriptions/rule functions
2750 created by customers, which should be subscribed to this charges event in
2751 order to execute their code.
2752 Presently we are not shipping any seeded subscriptions.
2753 Some parameters were initialized to NULL , which will be changed later.
2754 As there are no workflows attached to this, l_workflow_process_id is not required for
2755 processing.
2756 The mandatory parameters are event code(SUBMIT_CHARGES) and
2757 event key(estimate_detail_id). l_charges_rec_type can utilized for future modifications*/
2758
2759 l_charges_rec_type.incident_id := EstDtlTab(i).incident_id;
2760 l_charges_rec_type.org_id := EstDtlTab(i).org_id;
2761 wf_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
2762 wf_resp_id := FND_GLOBAL.RESP_ID;
2763 wf_user_id := FND_GLOBAL.USER_ID;
2764
2765 CS_CHG_WF_EVENT_PKG.RAISE_SUBMITCHARGES_EVENT(
2766 p_api_version => 1.0,
2767 p_init_msg_list => FND_API.G_FALSE,
2768 p_commit => FND_API.G_FALSE,
2769 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2770 p_event_code => 'SUBMIT_CHARGES',
2771 p_estimate_detail_id => EstDtlTab(i).Estimate_Detail_Id,
2772 p_USER_ID => wf_user_id,
2773 p_RESP_ID => wf_resp_id,
2774 p_RESP_APPL_ID => wf_resp_appl_id,
2775 p_est_detail_rec => l_charges_rec_type,
2776 p_wf_process_id => NULL,
2777 p_owner_id => NULL,
2778 p_wf_manual_launch => 'N' ,
2779 x_wf_process_id => l_workflow_process_id,
2780 x_return_status => lx_return_status,
2781 x_msg_count => lx_msg_count,
2782 x_msg_data => lx_msg_data );
2783
2784 if ( lx_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2785 -- do nothing in this API. The BES wrapper API will have to trap this
2786 -- situation.
2787 null;
2788 end if;
2789
2790 -- Installbase Call if the Order creation is successful
2791 --
2792 --IF EstDtlTab(i).billing_type = 'M' THEN
2793 IF EstDtlTab(i).billing_category = 'M' THEN
2794 -- 11.5.6 Installation details call
2795 -- Bug fix:3564034
2796 -- Create installtion details if an instance is available and update_ib_flag = 'Y'
2797 --
2798 IF (EstDtlTab(i).comms_nl_trackable_flag = 'Y')
2799 -- commented for the bug:3800010
2800 -- and (EstDtlTab(i).customer_product_id IS NOT NULL)
2801 and l_update_ib_flag = 'Y' THEN
2802
2803 -- assign order line id to source transaction id.
2804 csi_txn_line_rec.source_transaction_id := x_order_line_tbl(1).order_line_id;
2805
2806 --Checking for account_id.If account_id is null then we need to derive one
2807 --created by OC.
2808 IF EstDtlTab(i).account_id is null then
2809 BEGIN
2810 SELECT cust_account_id
2811 INTO l_account_id
2812 FROM hz_cust_accounts_all
2813 WHERE party_id = p_party_id
2814 AND status = 'A';
2815 EXCEPTION
2816 WHEN NO_DATA_FOUND THEN
2817 FND_MESSAGE.Set_Name('CS','CS_CHG_NO_ACCT_CREATED');
2818 FND_MSG_PUB.Add;
2819
2820 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2821 p_count => x_msg_count,
2822 p_data => x_msg_data);
2823 END;
2824 --
2825 -- Create IB Party details and account details records
2826 --
2827 IF EstDtlTab(i).line_category_code = 'RETURN' then
2828 IF l_src_change_owner = 'Y' THEN
2829 IF l_src_change_owner_to_code = 'I' THEN
2830
2831 BEGIN
2832 SELECT internal_party_id
2833 INTO l_internal_party_id
2834 FROM csi_install_parameters
2835 WHERE rownum = 1;
2836
2837 IF l_internal_party_id is null THEN
2838 RAISE l_IB_ERROR;
2839 END IF;
2840 EXCEPTION
2841 WHEN OTHERS THEN
2842 FND_MESSAGE.Set_Name('CS','CS_CHG_INTERNAL_PARTY_NOT_DEF');
2843 FND_MSG_PUB.Add;
2844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2845 END;
2846
2847 SELECT instance_party_id
2848 INTO l_instance_party_id
2849 FROM CSI_I_PARTIES
2850 WHERE instance_id = EstDtlTab(i).Customer_Product_Id
2851 AND relationship_type_code = 'OWNER'
2852 AND party_id = (SELECT party_id
2853 FROM hz_cust_accounts
2854 WHERE cust_account_id = l_account_id);
2855
2856 csi_txn_party_detail_tbl(1).instance_party_id := l_instance_party_id;
2857 csi_txn_party_detail_tbl(1).party_source_table := 'HZ_PARTIES';
2858 csi_txn_party_detail_tbl(1).party_source_id := l_internal_party_id;
2859 csi_txn_party_detail_tbl(1).relationship_type_code := 'OWNER';
2860 csi_txn_party_detail_tbl(1).txn_line_details_index := 1;
2861 csi_txn_party_detail_tbl(1).contact_flag := 'N';
2862 csi_txn_pty_acct_detail_tbl.delete;
2863 ELSE
2864 FND_MESSAGE.Set_Name('CS','CS_CHG_RTN_TO_EXT_PTY_NOT_SUP');
2865 FND_MSG_PUB.Add;
2866 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2867 p_count => x_msg_count,
2868 p_data => x_msg_data);
2869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2870
2871 END IF; -- IF l_src_change_owner_to_code = 'I'
2872 ELSE
2873 csi_txn_party_detail_tbl.delete;
2874 csi_txn_pty_acct_detail_tbl.delete;
2875 END IF; -- IF l_src_change_owner = 'Y'
2876
2877 ELSE -- shipment line
2878 IF l_src_change_owner = 'Y' THEN
2879 -- Create Party record
2880 csi_txn_party_detail_tbl(1).party_source_table := 'HZ_PARTIES';
2881 csi_txn_party_detail_tbl(1).party_source_id := EstDtlTab(i).customer_id;
2882 csi_txn_party_detail_tbl(1).relationship_type_code := 'OWNER';
2883 csi_txn_party_detail_tbl(1).txn_line_details_index := 1;
2884 csi_txn_party_detail_tbl(1).contact_flag := 'N';
2885
2886 -- Create Account record
2887 csi_txn_pty_acct_detail_tbl(1).account_id := l_account_id;
2888 csi_txn_pty_acct_detail_tbl(1).relationship_type_code := 'OWNER';
2889 csi_txn_pty_acct_detail_tbl(1).txn_party_details_index := 1;
2890 csi_txn_pty_acct_detail_tbl(1).active_start_date := sysdate;
2891
2892 END IF;
2893
2894 END IF; -- IF EstDtlTab(i).line_category_code = 'RETURN'
2895
2896 END IF; -- EstDtlTab.account_id is null
2897
2898 --
2899 --
2900 -- fix for bug:3800010
2901 IF EstDtlTab(i).line_category_code = 'RETURN'
2902 and (EstDtlTab(i).customer_product_id IS NOT NULL) THEN
2903 -- Now call the create transaction Details API.
2904 csi_t_txn_details_grp.create_transaction_dtls(
2905 p_api_version => 1.0,
2906 p_commit => fnd_api.g_false,
2907 p_init_msg_list => fnd_api.g_false,
2908 p_validation_level => fnd_api.g_valid_level_full,
2909 px_txn_line_rec => csi_txn_line_rec ,
2910 px_txn_line_detail_tbl => csi_txn_line_detail_tbl,
2911 px_txn_party_detail_tbl => csi_txn_party_detail_tbl ,
2912 px_txn_pty_acct_detail_tbl => csi_txn_pty_acct_detail_tbl,
2913 px_txn_ii_rltns_tbl => csi_txn_ii_rltns_tbl,
2914 px_txn_org_assgn_tbl => csi_txn_org_assgn_tbl,
2915 px_txn_ext_attrib_vals_tbl => csi_txn_ext_attrib_vals_tbl,
2916 px_txn_systems_tbl => csi_txn_systems_tbl,
2917 x_return_status => x_return_status,
2918 x_msg_count => x_msg_count,
2919 x_msg_data => x_msg_data);
2920
2921 ELSIF EstDtlTab(i).line_category_code = 'ORDER' THEN
2922 -- Now call the create transaction Details API.
2923 csi_t_txn_details_grp.create_transaction_dtls(
2924 p_api_version => 1.0,
2925 p_commit => fnd_api.g_false,
2926 p_init_msg_list => fnd_api.g_false,
2927 p_validation_level => fnd_api.g_valid_level_full,
2928 px_txn_line_rec => csi_txn_line_rec ,
2929 px_txn_line_detail_tbl => csi_txn_line_detail_tbl,
2930 px_txn_party_detail_tbl => csi_txn_party_detail_tbl ,
2931 px_txn_pty_acct_detail_tbl => csi_txn_pty_acct_detail_tbl,
2932 px_txn_ii_rltns_tbl => csi_txn_ii_rltns_tbl,
2933 px_txn_org_assgn_tbl => csi_txn_org_assgn_tbl,
2934 px_txn_ext_attrib_vals_tbl => csi_txn_ext_attrib_vals_tbl,
2935 px_txn_systems_tbl => csi_txn_systems_tbl,
2936 x_return_status => x_return_status,
2937 x_msg_count => x_msg_count,
2938 x_msg_data => x_msg_data);
2939
2940 END IF; -- fix bug:3800010
2941
2942 END IF; -- comms_nl_trackable_flag
2943
2944 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2945
2946 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2947 p_count => x_msg_count,
2948 p_data => x_msg_data);
2949
2950 -- dbms_output.put_line('message status' || x_return_status);
2951 -- dbms_output.put_line('message_data' || substr(x_msg_data,1,200));
2952 -- dbms_output.put_line('message_count' || x_msg_count);
2953
2954 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2955
2956 END IF;
2957
2958 -- FND_MESSAGE.Set_Encoded(xi_msg_data);
2959 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2960
2961
2962 END IF; -- if billing flag = 'M'
2963
2964
2965 -- Update charge lines with order details
2966 -- Fix for bug:3545283
2967
2968 FOR k in 1..x_order_line_tbl.count LOOP
2969
2970 -- UPDATE ESTIMATE_DETAILS WITH ORDER_HEADER_ID AND ORDER_LINE_ID.
2971 -- Changes for 11.5.10.
2972 -- NULL Values should be passed for submit_error_message,
2973 -- submit_restriction_message and line_submitted columns as the
2974 -- order creation is successful.
2975
2976 Update_Estimate_Details(EstDtlTab(i).estimate_detail_id,
2977 x_order_header_id,
2978 x_order_line_tbl(k).order_line_id,
2979 NULL,
2980 NULL,
2981 NULL,
2982 p_submit_from_system);
2983
2984
2985 END LOOP;
2986 --
2987 -- IF THE ORDER CREATION IS NOT SUCCESSFUL
2988 ELSIF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2989
2990 IF x_msg_count > 0 THEN
2991 -- Get next message
2992
2993 -- FND_MSG_PUB.initialize;
2994 /* FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
2995 p_count => x_msg_count,
2996 p_data => x_msg_data); */
2997
2998 for k in 1..x_msg_count loop
2999 FND_MSG_PUB.get(p_encoded => 'F',
3000 p_data=>x_msg_data,
3001 p_msg_index_out=>l_dummy);
3002 end loop;
3003
3004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3005
3006 /* for k in 1..x_msg_count loop
3007 FND_MSG_PUB.get(p_encoded => 'F',
3008 p_data=>x_msg_data,
3009 p_msg_index_out=>l_dummy);
3010
3011 -- changes for 11.5.10
3012 msg_table(k) := x_msg_data;
3013 temp_tab := (temp_tab || msg_table(k));
3014
3015
3016 end loop; */
3017 --
3018 -- Changes for 11.5.10
3019 -- Recording error messages in the Charges schema as an autonomous
3020 -- transaction.
3021 /* Update_Errors(p_estimate_detail_id => EstDtlTab(i).estimate_detail_id,
3022 p_line_submitted => 'N',
3023 p_submit_restriction_message => NULL,
3024 p_submit_error_message => temp_tab,
3025 p_submit_from_system => p_submit_from_system); */
3026
3027 END IF; -- end of message count
3028
3029 IF x_msg_count = 0 THEN
3030 -- dbms_output.put_line('Before set_name');
3031 FND_MESSAGE.Set_Name('CS','CS_CHG_OM_ERR_WITH_NO_MSG');
3032 FND_MSG_PUB.Add;
3033 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
3034 p_count => x_msg_count,
3035 p_data => x_msg_data);
3036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3037
3038 END IF;
3039
3040 -- FND_MESSAGE.Set_Encoded(x_msg_data);
3041 -- FND_MSG_PUB.Add;
3042 -- RAISE FND_API.G_EXC_ERROR;
3043
3044 END IF;
3045
3046 END LOOP;
3047
3048 -- If no records are been submitted to OM, return a message.
3049 IF l_record_found = 'N' THEN
3050 FND_MESSAGE.Set_Name('CS','CS_CHG_NO_CHARGES_SUBMITTED');
3051 --FND_MESSAGE.SET_TOKEN('INCIDENT_ID', p_incident_id, TRUE);
3052 --FND_MESSAGE.SET_TOKEN('PARTY_ID', p_party_id, TRUE);
3053 --FND_MESSAGE.SET_TOKEN('ACCOUNT_ID', p_account_id, TRUE);
3054 FND_MSG_PUB.Add;
3055 RAISE FND_API.G_EXC_ERROR;
3056 END IF;
3057
3058 --
3059 -- End of API body
3060 --
3061
3062 -- Standard check of p_commit.
3063 IF FND_API.To_Boolean( p_commit ) THEN
3064 COMMIT WORK;
3065 END IF;
3066
3067 -- Standard call to get message count and if count is 1, get message info.
3068 FND_MSG_PUB.Count_And_Get
3069 ( p_count => x_msg_count,
3070 p_data => x_msg_data
3071 );
3072
3073
3074 EXCEPTION
3075 -- This exception is for SR level
3076 --
3077 --7117301
3078 WHEN RESOURCE_BUSY THEN
3079 FND_MESSAGE.Set_Name('CS','CS_CH_SUBMIT_IN_PROCESS');
3080 FND_MSG_PUB.Add;
3081 RAISE FND_API.G_EXC_ERROR;
3082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3083 --7117301
3084 WHEN FND_API.G_EXC_ERROR THEN
3085 ROLLBACK TO CS_Charge_Create_Order_PVT;
3086 x_return_status := FND_API.G_RET_STS_ERROR;
3087 FND_MSG_PUB.Count_And_Get
3088 ( p_count => x_msg_count,
3089 p_data => x_msg_data
3090 );
3091
3092 --
3093 -- This exception is for Charge Line Level
3094
3095 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3096
3097 -- updating charges schema with errors for 11.5.10
3098 --
3099 -- dbms_output.put_line('Charge_Line_Id before calling update_errors' || EstDtlTab(i).estimate_detail_id);
3100 -- dbms_output.put_line('message data before calling update_errors' || substr(x_msg_data,1,200));
3101 ROLLBACK TO CS_Charge_Create_Order_PVT; --7117301
3102 Update_Errors(p_estimate_detail_id => EstDtlTab(i).estimate_detail_id,
3103 p_line_submitted => 'N',
3104 p_submit_restriction_message => NULL,
3105 p_submit_error_message => x_msg_data,
3106 p_submit_from_system => p_submit_from_system);
3107
3108 FND_MESSAGE.Set_Encoded(x_msg_data);
3109 --ROLLBACK TO CS_Charge_Create_Order_PVT; --7117301
3110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3111
3112 WHEN OTHERS THEN
3113 g_oraerrmsg := substrb(sqlerrm,1,G_MAXERRLEN);
3114 fnd_message.set_name('CS','CS_CHG_SUBMIT_ORDER_FAILED');
3115 fnd_message.set_token('ROUTINE',l_api_name_full);
3116 fnd_message.set_token('REASON',g_oraerrmsg);
3117 FND_MSG_PUB.Add;
3118 IF FND_MSG_PUB.Check_Msg_Level
3119 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3120 THEN
3121 FND_MSG_PUB.Add_Exc_Msg
3122 ( G_PKG_NAME,
3123 l_api_name
3124 );
3125 END IF;
3126
3127 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
3128 p_count => x_msg_count,
3129 p_data => x_msg_data);
3130
3131 --
3132 -- Adding the error message to charges schema for 11.5.10
3133 --
3134 ROLLBACK TO CS_Charge_Create_Order_PVT; --7117301
3135 Update_Errors(p_estimate_detail_id => EstDtlTab(i).estimate_detail_id,
3136 p_line_submitted => 'N',
3137 p_submit_restriction_message => NULL,
3138 p_submit_error_message => x_msg_data,
3139 p_submit_from_system => p_submit_from_system);
3140
3141 FND_MESSAGE.Set_Encoded(x_msg_data);
3142
3143
3144 --ROLLBACK TO CS_Charge_Create_Order_PVT; --7117301
3145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3146
3147 END Submit_Order;
3148
3149 /***************************************************************************/
3150 -- Update Charges table.
3151 /***************************************************************************/
3152 PROCEDURE Update_Estimate_Details (
3153 p_Estimate_Detail_Id IN NUMBER,
3154 p_order_header_Id IN NUMBER,
3155 p_order_line_Id IN NUMBER,
3156 p_line_submitted IN VARCHAR2,
3157 p_submit_restriction_message IN VARCHAR2,-- new
3158 p_submit_error_message IN VARCHAR2,-- new
3159 p_submit_from_system IN VARCHAR2 -- new
3160 ) IS
3161
3162 l_api_name CONSTANT VARCHAR2(30) := 'Update_Estimate_Details' ;
3163 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || l_api_name ;
3164 l_log_module CONSTANT VARCHAR2(255) := 'cs.plsql.' || l_api_name_full || '.';
3165
3166 BEGIN
3167
3168 ----------------------- FND Logging -----------------------------------
3169 IF FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level
3170 THEN
3171 FND_LOG.String
3172 ( FND_LOG.level_procedure , L_LOG_MODULE || 'start'
3173 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
3174 );
3175 FND_LOG.String
3176 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3177 , 'p_Estimate_Detail_Id:' || p_Estimate_Detail_Id
3178 );
3179 FND_LOG.String
3180 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3181 , 'p_order_header_Id:' || p_order_header_Id
3182 );
3183 FND_LOG.String
3184 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3185 , 'p_order_line_Id:' || p_order_line_Id
3186 );
3187 FND_LOG.String
3188 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3189 , 'p_line_submitted:' || p_line_submitted
3190 );
3191 FND_LOG.String
3192 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3193 , 'p_submit_restriction_message:' || p_submit_restriction_message
3194 );
3195 FND_LOG.String
3196 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3197 , 'p_submit_error_message:' || p_submit_error_message
3198 );
3199 FND_LOG.String
3200 ( FND_LOG.level_procedure , L_LOG_MODULE || ''
3201 , 'p_submit_from_system:' || p_submit_from_system
3202 );
3203 END IF;
3204
3205 UPDATE CS_ESTIMATE_DETAILS
3206 SET Order_Header_Id = p_order_header_Id,
3207 Order_Line_Id = p_order_line_Id,
3208 line_submitted = p_line_submitted,
3209 submit_restriction_message = p_submit_restriction_message,
3210 submit_error_message = p_submit_error_message,
3211 submit_from_system = p_submit_from_system,
3212 last_update_date = sysdate,
3213 last_update_login = fnd_global.login_id, --6027992
3214 last_updated_by = fnd_global.user_id --6027992
3215 WHERE Estimate_Detail_Id = p_estimate_detail_id;
3216
3217 EXCEPTION
3218 WHEN NO_DATA_FOUND THEN
3219 Raise No_Data_Found;
3220
3221 end Update_Estimate_Details;
3222
3223 End CS_Charge_Create_Order_PVT;