[Home] [Help]
PACKAGE BODY: APPS.CSP_PARTS_ORDER
Source
1 PACKAGE BODY CSP_PARTS_ORDER AS
2 /* $Header: cspvpodb.pls 120.72.12020000.6 2013/03/01 05:20:36 htank ship $ */
3
4 --
5 -- Purpose: Create/Update/Cancel Internal Parts Order for Spares
6 --
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- --------- ------ ------------------------------------------
10 -- phegde 05/01/01 Created new package body
11
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'csp_parts_order';
13 G_FILE_NAME CONSTANT VARCHAR2(30) := 'cspvpodb.pls';
14
15 -- This procedure is to get the Unit Price for Internal Requisition
16 -- If the Currency Code is same for both Source and Destination Organization
17 -- get the Unit Cost for the Source Organization, otherwise
18 -- derive Unit Price based on the List Price and Conversion Rate
19 -- This procedure will have 2 OUT parameters viz. Currency Code and Unit Price
20 PROCEDURE get_unit_price_prc (p_item_id IN NUMBER,
21 p_source_organization_id IN NUMBER,
22 p_destination_organization_id IN NUMBER,
23 p_set_of_books_id IN NUMBER,
24 x_chart_of_account_id IN OUT NOCOPY NUMBER,
25 x_currency_code IN OUT NOCOPY VARCHAR2,
26 x_unit_price IN OUT NOCOPY NUMBER,
27 x_item_cost OUT NOCOPY NUMBER) IS
28
29 -- Get Functional Currency and Chart of Accounts ID of the SOB for Internal Requsitions
30
31 CURSOR currency_code_cur (p_organization_id NUMBER) IS
32 SELECT glsob.currency_code
33 ,glsob.chart_of_accounts_id
34 FROM gl_sets_of_books glsob,
35 hr_organization_information hoi
36 WHERE glsob.set_of_books_id = hoi.org_information1
37 AND hoi.org_information_context ||'' = 'Accounting Information'
38 AND hoi.organization_id = p_organization_id;
39
40 -- Get Unit Price for Internal Requsitions
41 CURSOR unit_price_cur (p_item_id NUMBER, p_source_organization_id NUMBER) IS
42
43 SELECT cic.item_cost
44 FROM cst_item_costs_for_gl_view cic,
45 mtl_parameters mp
46 WHERE cic.inventory_item_id = p_item_id
47 AND cic.organization_id = mp.cost_organization_id
48 AND cic.inventory_asset_flag = 1
49 AND mp.organization_id= p_source_organization_id;
50
51 CURSOR converted_unit_price_cur (p_item_id NUMBER, p_source_organization_id NUMBER,
52 p_set_of_books_id NUMBER) IS
53 SELECT msi.list_price_per_unit *
54 round(gl_currency_api.get_closest_rate_sql
55 (p_set_of_books_id,
56 glsob.currency_code,
57 trunc(sysdate),
58 psp.DEFAULT_RATE_TYPE,
59 30),10)
60 FROM mtl_system_items msi,
61 gl_sets_of_books glsob,
62 hr_organization_information hoi,
63 po_system_parameters psp
64 WHERE msi.inventory_item_id = p_item_id
65 AND hoi.organization_id = p_source_organization_id
66 AND hoi.org_information_context = 'Accounting Information'
67 AND msi.organization_id = hoi.organization_id
68 AND glsob.set_of_books_id = hoi.org_information1;
69
70 CURSOR conversion_rate_cur IS
71 SELECT round(gl_currency_api.get_closest_rate_sql
72 (p_set_of_books_id,
73 glsob.currency_code,
74 trunc(sysdate),
75 psp.DEFAULT_RATE_TYPE,
76 30),10)
77 FROM gl_sets_of_books glsob,
78 hr_organization_information hoi,
79 po_system_parameters psp
80 WHERE hoi.organization_id = p_source_organization_id
81 AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information'
82 AND glsob.set_of_books_id = hoi.org_information1;
83
84 s_currency_code VARCHAR2(15);
85 d_currency_code VARCHAR2(15);
86 d_chart_of_accounts_id NUMBER;
87 s_chart_of_accounts_id NUMBER;
88 l_unit_price NUMBER;
89 UNIT_PRICE_LT_0 EXCEPTION;
90 INVALID_UNIT_PRICE EXCEPTION;
91 l_conversion_rate NUMBER;
92 BEGIN
93
94 -- Get the SOB Currency Code of the Source Organization ID
95 OPEN currency_code_cur(p_source_organization_id);
96 FETCH currency_code_cur INTO s_currency_code, s_chart_of_accounts_id;
97 CLOSE currency_code_cur;
98
99 -- Get SOB Currency Code of the Destination (Inventory) Organization
100 OPEN currency_code_cur(p_destination_organization_id);
101 FETCH currency_code_cur INTO d_currency_code, d_chart_of_accounts_id;
102 CLOSE currency_code_cur;
103
104 -- If Currency Code is same for both Destination and Source Organization
105 -- Get Item Cost of the Source Organization ID from cst_item_costs__for_gl_view
106 -- Get Unit Cost
107 OPEN unit_price_cur (p_item_id, p_source_organization_id);
108 FETCH unit_price_cur INTO l_unit_price;
109 x_item_cost := l_unit_price;
110 IF unit_price_cur%NOTFOUND THEN
111 CLOSE unit_price_cur;
112 Raise INVALID_UNIT_PRICE;
113 END IF;
114 CLOSE unit_price_cur;
115 IF l_unit_price < 0 THEN
116 Raise UNIT_PRICE_LT_0;
117 END IF;
118
119 IF NVL(s_currency_code,'X') <> NVL(d_currency_code,'X') THEN
120 /* Currency Code is different for Source and Destination Organization */
121
122 OPEN conversion_rate_cur;
123 FETCH conversion_rate_cur INTO l_conversion_Rate;
124 CLOSE conversion_Rate_cur;
125
126 IF (l_conversion_rate = -1 OR l_conversion_rate = -2) THEN
127 l_conversion_Rate := 1;
128 END IF;
129 l_unit_price := l_unit_price * l_conversion_rate;
130
131 IF l_unit_price < 0 THEN
132 Raise UNIT_PRICE_LT_0;
133 END IF;
134 END IF; /* Currency Check */
135
136 x_currency_code := d_currency_code;
137 x_unit_price := l_unit_price;
138 x_chart_of_account_id := d_chart_of_accounts_id;
139
140 EXCEPTION
141
142 WHEN UNIT_PRICE_LT_0 THEN
143 po_message_s.app_error('PO_RI_UNIT_PRICE_LT_0');
144 raise;
145
146 WHEN INVALID_UNIT_PRICE THEN
147 x_unit_price := 0;
148 x_currency_code := d_currency_code;
149 x_chart_of_account_id := d_chart_of_accounts_id;
150 END get_unit_price_prc;
151
152
153 -- This function is to check the subinventory type to derive
154 -- Code Combinatin ID. Function Returns Sub Inventory Type
155 -- 'ASSET' or 'EXPENSE'. If EXCEPTION, Returns 'X'
156
157 FUNCTION check_sub_inv_type_fun (p_destination_subinventory IN VARCHAR2,
158 p_destination_organization_id IN NUMBER )
159 RETURN VARCHAR2 IS
160
161 CURSOR asset_inventory_cur IS
162 SELECT asset_inventory
163 FROM mtl_secondary_inventories
164 WHERE secondary_inventory_name = NVL(p_destination_subinventory,'X')
165 AND organization_id = p_destination_organization_id;
166
167 l_asset_inventory NUMBER;
168 l_subinventory_type VARCHAR2(10) := 'X';
169
170 BEGIN
171 OPEN asset_inventory_cur;
172 FETCH asset_inventory_cur INTO l_asset_inventory;
173 CLOSE asset_inventory_cur;
174
175 IF (l_asset_inventory = 1) THEN
176 l_subinventory_type := 'ASSET';
177 ELSIF (l_asset_inventory = 2) then
178 l_subinventory_type := 'EXPENSE';
179 END IF;
180
181 RETURN l_subinventory_type;
182
183 EXCEPTION
184
185 WHEN OTHERS THEN
186 RETURN 'X';
187
188 END check_sub_inv_type_fun ;
189
190 -- This function is to check the item type to derive
191 -- Code Combinatin ID. Function Returns Item Type
192 -- 'ASSET' or 'EXPENSE'. If EXCEPTION, Returns 'X'
193
194 FUNCTION check_inv_item_type_fun ( p_destination_organization_id IN NUMBER,
195 p_item_id IN NUMBER)
196 RETURN VARCHAR2 IS
197
198 CURSOR item_type_cur IS
199 SELECT inventory_asset_flag
200 FROM mtl_system_items
201 WHERE organization_id = p_destination_organization_id
202 AND inventory_item_id = p_item_id;
203
204 l_item_type VARCHAR2(10) := 'X';
205 l_asset_flag VARCHAR2(1);
206
207 BEGIN
208
209 OPEN item_type_cur;
210 FETCH item_type_cur INTO l_asset_flag;
211 CLOSE item_type_cur;
212
213 IF l_asset_flag = 'Y' then
214 l_item_type := 'ASSET';
215 ELSE
216 l_item_type := 'EXPENSE';
217 END IF;
218
219 RETURN l_item_type;
220
221 EXCEPTION
222
223 WHEN OTHERS THEN
224 RETURN 'X';
225
226 END check_inv_item_type_fun;
227
228 -- This function is to default Code Combination ID for
229 -- Destination Type Code INVENTORY
230 -- Called in Process_Requisition
231
232 FUNCTION get_charge_account_fun (p_destination_organization_id IN NUMBER,
233 p_item_id IN NUMBER,
234 p_destination_subinventory IN VARCHAR)
235
236 RETURN NUMBER IS
237
238 l_charge_account NUMBER;
239 l_item_type VARCHAR2(10);
240 l_subinventory_type VARCHAR2(10) := 'X';
241
242 BEGIN
243
244 l_item_type := check_inv_item_type_fun (p_destination_organization_id, p_item_id);
245
246
247 IF l_item_type = 'EXPENSE' then
248
249 -- Subinventory is provided
250 IF (p_destination_subinventory IS NOT NULL) THEN
251 BEGIN
252 SELECT expense_account
253 INTO l_charge_account
254 FROM mtl_secondary_inventories
255 WHERE secondary_inventory_name = p_destination_subinventory
256 AND organization_id = p_destination_organization_id;
257 EXCEPTION
258 WHEN OTHERS THEN
259 l_charge_account := 0;
260 END;
261 END IF;
262
263 -- If Expense Account not available for the Subinventory and Org,
264 -- get expense account from Item Master for the Item and the Org
265 IF (l_charge_account IS NULL) THEN
266 BEGIN
267 SELECT expense_account
268 INTO l_charge_account
269 FROM mtl_system_items
270 WHERE organization_id = p_destination_organization_id
271 and inventory_item_id = p_item_id;
272 EXCEPTION
273 WHEN OTHERS THEN
274 l_charge_account := 0;
275 END;
276 END IF;
277
278 -- If Expense Account not available in Item Master, get account
279 -- from MTL_PARAMETERS for the Destination Organization
280 IF (l_charge_account IS NULL) THEN
281 BEGIN
282 SELECT expense_account
283 INTO l_charge_account
284 FROM mtl_parameters
285 WHERE organization_id = p_destination_organization_id;
286
287 EXCEPTION
288 WHEN OTHERS THEN
289 l_charge_account := 0;
290 END;
291 END IF;
292
293 ELSE -- item type is ASSET
294
295 --Check subinventory for Asset or Expense tracking.
296 IF (p_destination_subinventory IS NOT NULL) THEN
297
298 l_subinventory_type := check_sub_inv_type_fun(p_destination_subinventory,
299 p_destination_organization_id);
300 END IF;
301
302 -- Get the default account from the Organization if Subinventory Type is NOT
303
304 -- EXPENSE or ASSET
305 IF l_subinventory_type = 'X' then
306 BEGIN
307 SELECT material_account
308 INTO l_charge_account
309 FROM mtl_parameters
310 WHERE organization_id = p_destination_organization_id;
311 EXCEPTION
312 WHEN OTHERS THEN
313 l_charge_account := 1111;
314 END;
315 ELSIF l_subinventory_type = 'EXPENSE' THEN
316 -- Get Expense Account for the Subinventory
317 BEGIN
318 SELECT expense_account
319 INTO l_charge_account
320 FROM mtl_secondary_inventories
321 WHERE secondary_inventory_name = p_destination_subinventory
322 AND organization_id = p_destination_organization_id;
323 EXCEPTION
324 WHEN OTHERS THEN
325 l_charge_account := 1112;
326 END;
327 -- If charge account is NULL for the Subinventory, get the default account
328
329
330 -- for the Organization from MTL_PARAMETERS
331 IF (l_charge_account is NULL) THEN
332 BEGIN
333 SELECT expense_account
334 INTO l_charge_account
335 FROM mtl_parameters
336 WHERE organization_id = p_destination_organization_id;
337 EXCEPTION
338 WHEN OTHERS THEN
339 l_charge_account := 1113;
340 END;
341 END IF;
342 ELSE -- destination sub inventory type is ASSET
343 -- Get the Charge_Account for the Subinventory
344 BEGIN
345 SELECT material_account
346 INTO l_charge_account
347 FROM mtl_secondary_inventories
348 WHERE secondary_inventory_name = p_destination_subinventory
349 AND organization_id = p_destination_organization_id;
350 EXCEPTION
351 WHEN OTHERS THEN
352 l_charge_account := 1114;
353 END;
354
355 -- If Charge_account is not availabe for the Subinventory,
356 -- get it for the Destination Organization from MTL_PARAMETERS
357 IF (l_charge_account IS NULL) THEN
358 BEGIN
359 SELECT material_account
360 INTO l_charge_account
361 FROM mtl_parameters
362 WHERE organization_id = p_destination_organization_id;
363 EXCEPTION
364 WHEN OTHERS THEN
365 l_charge_account := 1115;
366 END;
367 END IF;
368 END IF; /* Sub Inventory Type */
369 END IF; /* Item Type Check */
370
371 RETURN (l_charge_account);
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 RETURN (1115);
376 END get_charge_account_fun;
377
378
379 /*
380 Function to validate Code Combination IDs.
381 If INVALID function will return FALSE
382 */
383
384 FUNCTION valid_account_id_fun (p_ccid IN NUMBER,
385 p_gl_date IN DATE,
386 p_chart_of_accounts_id IN NUMBER)
387 RETURN BOOLEAN IS
388
389 CURSOR validate_ccid_cur IS
390 SELECT 'X'
391 FROM gl_code_combinations gcc
392 WHERE gcc.code_combination_id = p_ccid
393 AND gcc.enabled_flag = 'Y'
394 AND trunc(nvl(p_gl_date,SYSDATE))
395 BETWEEN trunc(nvl(start_date_active, nvl(p_gl_date,SYSDATE) ))
396 AND trunc(nvl (end_date_active, SYSDATE+1))
397 AND gcc.detail_posting_allowed_flag = 'Y'
398 AND gcc.chart_of_accounts_id= p_chart_of_accounts_id
399 AND gcc.summary_flag = 'N';
400
401 l_dummy VARCHAR2(1);
402
403 BEGIN
404
405 OPEN validate_ccid_cur;
406 FETCH validate_ccid_cur INTO l_dummy;
407 IF validate_ccid_cur%FOUND THEN
408 CLOSE validate_ccid_cur;
409 return TRUE;
410 ELSE
411 CLOSE validate_ccid_cur;
412 return FALSE;
413 END IF;
414
415 EXCEPTION
416
417 WHEN OTHERS THEN
418 return (FALSE);
419
420 END valid_account_id_fun;
421
422 PROCEDURE Cancel_Order(
423 /* p_api_version IN NUMBER
424 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_TRUE
425 ,p_commit IN VARCHAR2 := FND_API.G_FALSE */
426 p_header_rec IN csp_parts_requirement.header_rec_type
427 ,p_line_table IN csp_parts_requirement.Line_Tbl_type
428 ,p_process_Type IN VARCHAR2
429 ,x_return_status OUT NOCOPY VARCHAR2
430 ,x_msg_count OUT NOCOPY NUMBER
431 ,x_msg_data OUT NOCOPY VARCHAR2
432 ) IS
433 l_api_version_number CONSTANT NUMBER := 1.0;
434 l_api_name CONSTANT VARCHAR2(30) := 'cancel_order';
435 l_line_tbl CSP_PARTS_REQUIREMENT.Line_tbl_type;
436 l_line_rec CSP_PARTS_REQUIREMENT.Line_rec_type;
437 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
438 l_msg_count NUMBER;
439 l_msg_data VARCHAR2(2000);
440
441 -- Record and table types for oe process_order
442 l_oe_line_tbl OE_Order_PUB.line_tbl_type;
443 lx_oe_line_tbl OE_Order_PUB.line_tbl_type;
444 l_oe_line_old_tbl OE_Order_PUB.line_tbl_type;
445 l_oe_header_rec oe_order_pub.header_rec_type := OE_ORDER_PUB.G_MISS_HEADER_REC;
446 lx_oe_header_rec oe_order_pub.header_rec_type := OE_ORDER_PUB.G_MISS_HEADER_REC;
447 l_oe_Header_Val_rec oe_order_pub.header_val_rec_type;
448 l_oe_header_adj_tbl oe_order_pub.header_adj_tbl_type;
449 l_oe_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;
450 l_oe_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
451 l_oe_Header_Adj_Att_Tbl oe_order_pub.header_adj_att_tbl_type;
452 l_oe_Header_Adj_Assoc_Tbl oe_order_pub.header_adj_assoc_tbl_type;
453 l_oe_header_scr_tbl OE_ORDER_PUB.header_scredit_tbl_type;
454 l_oe_Header_Scredit_Val_Tbl OE_ORDER_PUB.header_scredit_Val_tbl_type;
455 l_oe_line_rec oe_order_pub.line_rec_type;
456 l_oe_Line_Val_Tbl oe_order_pub.line_Val_tbl_type;
457 l_oe_line_adj_tbl oe_order_pub.line_adj_tbl_type;
458 l_oe_Line_Adj_Val_Tbl oe_order_pub.line_adj_val_tbl_type;
459 l_oe_Line_Price_Att_Tbl oe_order_pub.line_price_att_tbl_type;
460 l_oe_Line_Adj_Att_Tbl oe_order_pub.line_adj_att_tbl_type;
461 l_oe_Line_Adj_Assoc_tbl oe_order_pub.Line_Adj_Assoc_Tbl_Type;
462 l_oe_line_scr_tbl oe_order_pub.line_scredit_tbl_type;
463 l_oe_Line_Scredit_Val_Tbl oe_order_pub.line_scredit_val_tbl_type;
464 l_oe_Lot_Serial_Tbl oe_order_pub.lot_serial_tbl_type;
465 l_oe_Lot_Serial_Val_Tbl oe_order_pub.lot_serial_val_tbl_type;
466 l_oe_Request_Tbl_Type oe_order_pub.Request_tbl_type;
467 l_oe_control_rec OE_GLOBALS.Control_Rec_Type;
468
469 l_oe_header_id NUMBER;
470 j number := 0;
471 l_msg VARCHAR2(2000);
472
473 /*
474 CURSOR get_new_context(p_new_org_id number) IS
475 SELECT org_information2 ,
476 org_information3 ,
477 org_information4
478 FROM hr_organization_information hou
479 WHERE hou.organization_id = p_new_org_id
480 AND hou.org_information1 = 'FIELD_SERVICE'
481 AND hou.org_information_context = 'CS_USER_CONTEXT';
482 */
483
484 orig_org_id number;
485 orig_user_id number;
486 orig_resp_id number;
487 orig_resp_appl_id number;
488 new_org_id number;
489 new_user_id number;
490 new_resp_id number;
491 new_resp_appl_id number;
492 l_source_operating_unit number;
493 l_org_id number;
494 l_user_id number;
495 new_user varchar2(240);
496 l_module_name varchar2(100) := 'csp.plsql.csp_parts_order.Cancel_Order';
497
498 BEGIN
499
500 SAVEPOINT Cancel_Order_PUB;
501
502 -- initialize return status
503 x_return_status := FND_API.G_RET_STS_SUCCESS;
504
505 l_user_id := nvl(fnd_global.user_id, 0) ;
506 fnd_profile.get('RESP_ID',orig_resp_id);
507 fnd_profile.get('RESP_APPL_ID',orig_resp_appl_id);
508
509 BEGIN
510 l_org_id := mo_global.get_current_org_id;
511
512 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
513 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
514 'l_org_id = ' || l_org_id);
515 end if;
516
517 if l_org_id is null then
518 po_moac_utils_pvt.INITIALIZE;
519 l_org_id := mo_global.get_current_org_id;
520 end if;
521
522 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
523 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
524 'l_org_id = ' || l_org_id);
525 end if;
526
527 po_moac_utils_pvt.set_org_context(l_org_id);
528 EXCEPTION
529 WHEN NO_DATA_FOUND THEN
530 RAISE NO_DATA_FOUND;
531 END;
532
533 l_oe_header_id := p_header_rec.order_header_id;
534 l_line_tbl := p_line_table;
535
536 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
537 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
538 'l_oe_header_id = ' || l_oe_header_id);
539 end if;
540
541 IF (p_process_type = 'REQUISITION') THEN
542 FOR I in 1..l_line_tbl.count LOOP
543 update po_requisition_lines
544 set quantity_cancelled = l_line_Tbl(I).quantity,
545 cancel_flag = 'Y',
546 cancel_reason = l_line_tbl(I).change_reason,
547 cancel_date = sysdate
548 where requisition_line_id = l_line_tbl(I).requisition_line_id;
549
550 -- update mtl_supply data for the requisition
551 IF NOT po_supply.po_req_supply(
552 p_docid => null,
553 p_lineid => l_line_Tbl(I).requisition_line_id,
554 p_shipid => null,
555 p_action => 'Remove_Req_Line_Supply',
556 p_recreate_flag => NULL,
557 p_qty => NULL,
558 p_receipt_date => NULL) THEN
559
560 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
561 token1 => 'FILE',
562 value1 => 'PO_SUPPLY',
563 token2 => 'ERR_NUMBER',
564 value2 => '035',
565 token3 => 'SUBROUTINE',
566 value3 => 'PO_REQ_SUPPLY()');
567 RAISE FND_API.G_EXC_ERROR;
568 END IF;
569 END LOOP;
570 ELSE
571 IF(l_oe_header_id IS NOT NULL) THEN
572
573 -- source operating unit
574 BEGIN
575 SELECT org_id
576 INTO l_source_operating_unit
577 FROM OE_ORDER_HEADERS_ALL
578 WHERE header_id = l_oe_header_id;
579 EXCEPTION
580 WHEN NO_DATA_FOUND THEN
581 RAISE NO_DATA_FOUND;
582 END;
583
584 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
585 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
586 'l_source_operating_unit = ' || l_source_operating_unit);
587 end if;
588
589 IF (l_source_operating_unit <> l_org_id) THEN
590 /*
591 OPEN get_new_context(l_source_operating_unit);
592 FETCH get_new_context
593 INTO new_user_id,new_resp_id,new_resp_appl_id;
594 CLOSE get_new_context;
595 */
596
597 fnd_profile.get('CSP_USER_TEST', new_user);
598 new_user_id := substr(new_user, 1, instr(new_user, '~') - 1);
599 new_user := substr(ltrim(new_user, new_user_id), 3);
600 new_resp_id := substr(new_user, 1, instr(new_user, '~') - 1);
601 new_resp_appl_id := substr(ltrim(new_user, new_resp_id), 3);
602
603
604 IF new_resp_id is not null and
605 new_user_id is not null and
606 new_resp_appl_id is not null THEN
607 fnd_global.apps_initialize(new_user_id,new_resp_id,new_resp_appl_id);
608 mo_global.set_policy_context('S',l_source_operating_unit);
609 ELSE
610 --dbms_application_info.set_client_info(l_source_operating_unit);
611 mo_global.set_policy_context('S',l_source_operating_unit);
612 END IF;
613 END If;
614
615 oe_header_util.Query_Row(
616 p_header_id => l_oe_header_id,
617 x_header_rec => l_oe_header_rec);
618
619 /* l_oe_header_rec.cancelled_flag := 'Y';
620 l_oe_header_rec.flow_status_code := 'CANCELLED';
621 l_oe_header_rec.open_flag := 'N';
622 l_oe_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
623 l_oe_header_rec.change_reason := p_header_rec.change_reason;
624 l_oe_header_rec.change_comments := p_header_rec.change_comments;
625 */
626 oe_line_util.Query_Rows
627 (p_header_id => l_oe_header_id,
628 x_line_tbl => l_oe_line_old_tbl
629 );
630
631 For I in 1 .. l_oe_line_old_tbl.count LOOP
632 IF nvl(l_oe_line_old_tbl(i).shipped_quantity,0) = 0 AND
633 Nvl(l_oe_line_old_tbl(i).cancelled_flag,'N') <> 'Y' AND
634 Nvl(l_oe_line_old_tbl(i).ordered_quantity,0) <> 0 THEN
635 J := J + 1;
636 l_oe_line_tbl(J) := l_oe_line_old_tbl(I);
637 l_oe_line_tbl(J).db_flag := FND_API.G_TRUE;
638 l_oe_line_tbl(J).cancelled_quantity := l_oe_line_old_tbl(J).ordered_quantity;
639
640 l_oe_line_tbl(j).ordered_quantity :=0;
641 l_oe_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
642 l_oe_line_tbl(j).change_reason := p_header_rec.change_reason;
643
644
645 l_oe_line_tbl(j).change_comments := p_header_Rec.change_comments;
646
647 l_oe_line_tbl(j).cancelled_flag := 'Y';
648 l_oe_line_tbl(j).flow_status_code := 'CANCELLED';
649 l_oe_line_tbl(j).source_document_line_id := l_oe_line_old_tbl(J).source_document_line_id;
650
651
652 l_oe_line_tbl(j).open_flag := 'N';
653 End If;
654 end loop;
655
656 ELSE -- IF (l_oe_header_id IS NULL) THEN
657
658 FOR I in 1..l_line_tbl.count LOOP
659 BEGIN
660 SELECT org_id
661 INTO l_source_operating_unit
662 FROM oe_order_lines_all
663 WHERE line_id = l_line_tbl(i).order_line_id;
664 EXCEPTION
665 WHEN NO_DATA_FOUND THEN
666 RAISE NO_DATA_FOUND;
667 END;
668
669 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
670 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
671 'l_source_operating_unit = ' || l_source_operating_unit);
672 end if;
673
674 IF (l_source_operating_unit <> l_org_id) THEN
675 /*
676 OPEN get_new_context(l_source_operating_unit);
677 FETCH get_new_context
678 INTO new_user_id,new_resp_id,new_resp_appl_id;
679 CLOSE get_new_context;
680 */
681
682 fnd_profile.get('CSP_USER_TEST', new_user);
683 new_user_id := substr(new_user, 1, instr(new_user, '~') - 1);
684 new_user := substr(ltrim(new_user, new_user_id), 3);
685 new_resp_id := substr(new_user, 1, instr(new_user, '~') - 1);
686 new_resp_appl_id := substr(ltrim(new_user, new_resp_id), 3);
687
688 IF new_resp_id is not null and
689 new_user_id is not null and
690 new_resp_appl_id is not null THEN
691 fnd_global.apps_initialize(new_user_id,new_resp_id,new_resp_appl_id);
692 mo_global.set_policy_context('S',l_source_operating_unit);
693 ELSE
694 --dbms_application_info.set_client_info(l_source_operating_unit);
695 mo_global.set_policy_context('S',l_source_operating_unit);
696 END IF;
697 END If;
698
699 l_oe_line_old_tbl(i) := oe_line_util.Query_Row(l_line_tbl(i).order_line_id);
700
701
702 IF nvl(l_oe_line_old_tbl(i).shipped_quantity,0) = 0 AND
703 Nvl(l_oe_line_old_tbl(i).cancelled_flag,'N') <> 'Y' AND
704 Nvl(l_oe_line_old_tbl(i).ordered_quantity,0) <> 0 THEN
705 J := J + 1;
706 l_oe_line_tbl(J) := l_oe_line_old_tbl(I);
707 l_oe_line_tbl(J).db_flag := FND_API.G_TRUE;
708 l_oe_line_tbl(J).cancelled_quantity := l_oe_line_tbl(J).ordered_quantity;
709
710 l_oe_line_tbl(j).ordered_quantity :=0;
711 l_oe_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
712 l_oe_line_tbl(j).change_reason := p_line_table(i).change_reason;
713
714 l_oe_line_tbl(j).change_comments := p_line_table(i).change_comments;
715
716 l_oe_line_tbl(j).cancelled_flag := 'Y';
717 l_oe_line_tbl(j).flow_status_code := 'CANCELLED';
718 l_oe_line_tbl(j).source_document_line_id := l_oe_line_old_tbl(J).source_document_line_id;
719
720
721 l_oe_line_tbl(j).open_flag := 'N';
722 End If;
723 END LOOP;
724
725 oe_header_util.Query_Row(
726 p_header_id => l_oe_line_old_tbl(1).header_id,
727 x_header_rec => l_oe_header_rec);
728
729 END If;
730
731 IF l_oe_line_tbl.count = 0 THEN
732 FND_MESSAGE.SET_NAME('ONT','OE_NO_ELIGIBLE_LINES');
733 FND_MESSAGE.SET_TOKEN('ORDER',
734 l_oe_header_rec.Order_Number, FALSE);
735 FND_MSG_PUB.ADD;
736 fnd_msg_pub.count_and_get
737 ( p_count => x_msg_count
738 , p_data => x_msg_data);
739
740 x_return_status := FND_API.G_RET_STS_ERROR;
741
742 RAISE FND_API.G_EXC_ERROR;
743 END IF;
744
745 l_oe_control_rec.controlled_operation := TRUE;
746 l_oe_control_rec.change_attributes := TRUE;
747 l_oe_control_rec.validate_entity := TRUE;
748 l_oe_control_rec.write_to_DB := TRUE;
749 l_oe_control_rec.default_attributes := FALSE;
750 l_oe_control_rec.process := FALSE;
751
752 -- Instruct API to retain its caches
753 l_oe_control_rec.clear_api_cache := FALSE;
754 l_oe_control_rec.clear_api_requests := FALSE;
755
756 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
757 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
758 'Starting OW Debug...');
759
760 oe_debug_pub.G_FILE := NULL;
761 oe_debug_pub.debug_on;
762 oe_debug_pub.initialize;
763 oe_debug_pub.setdebuglevel(5);
764
765 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
766 'OE Debug File : '|| OE_DEBUG_PUB.Set_Debug_Mode('FILE'));
767 end if;
768
769 -- Call to Process Order
770 OE_Order_PUB.Process_Order(
771 p_org_id => l_source_operating_unit
772 , p_api_version_number => l_api_version_number
773 ,p_init_msg_list => FND_API.G_TRUE
774 ,p_return_values => FND_API.G_FALSE
775 ,p_action_commit => FND_API.G_FALSE
776 -- Passing just the entity records that are a part of this order
777 ,p_header_rec => l_oe_header_rec
778 ,p_line_tbl => l_oe_line_tbl
779 ,p_old_line_tbl => l_oe_line_old_tbl
780 -- OUT variables
781 ,x_header_rec => lx_oe_header_rec
782 ,x_header_val_rec => l_oe_Header_Val_rec
783 ,x_header_adj_tbl => l_oe_header_adj_tbl
784 ,x_Header_Adj_val_tbl => l_oe_header_adj_val_tbl
785 ,x_Header_price_Att_tbl => l_oe_header_price_att_tbl
786 ,x_Header_Adj_Att_tbl => l_oe_Header_Adj_Att_Tbl
787 ,x_Header_Adj_Assoc_tbl => l_oe_Header_Adj_Assoc_Tbl
788 ,x_header_scredit_tbl => l_oe_header_scr_tbl
789 ,x_Header_Scredit_val_tbl => l_oe_Header_Scredit_Val_Tbl
790 ,x_line_tbl => lx_oe_line_tbl
791 ,x_line_val_tbl => l_oe_Line_Val_Tbl
792 ,x_line_adj_tbl => l_oe_line_adj_tbl
793 ,x_Line_Adj_val_tbl => l_oe_Line_Adj_Val_Tbl
794 ,x_Line_price_Att_tbl => l_oe_Line_Price_Att_Tbl
795 ,x_Line_Adj_Att_tbl => l_oe_Line_Adj_Att_Tbl
796 ,x_Line_Adj_Assoc_tbl => l_oe_Line_Adj_Assoc_Tbl
797 ,x_Line_Scredit_tbl => l_oe_line_scr_tbl
798 ,x_Line_Scredit_val_tbl => l_oe_Line_Scredit_Val_Tbl
799 ,x_Lot_Serial_tbl => l_oe_Lot_Serial_Tbl
800 ,x_Lot_Serial_val_tbl => l_oe_Lot_Serial_Val_Tbl
801 ,x_action_request_tbl => l_oe_Request_Tbl_Type
802 ,x_return_status => l_return_status
803 ,x_msg_count => l_msg_count
804 ,x_msg_data => l_msg_data
805 );
806
807 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
808 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
809 'done ... with l_return_status = ' || l_return_status);
810 -- Stopping OE Debug...
811 oe_debug_pub.debug_off;
812 end if;
813
814 IF (l_source_operating_unit <> l_org_id) THEN
815 fnd_global.apps_initialize(l_user_id,orig_resp_id,orig_resp_appl_id);
816 mo_global.set_policy_context('S',l_org_id);
817 END If;
818
819 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
820 x_return_status := l_return_status;
821 for counter in REVERSE 1..l_msg_count Loop
822 l_msg := OE_MSG_PUB.Get(counter,FND_API.G_FALSE) ;
823 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
824 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
825 FND_MSG_PUB.ADD;
826 fnd_msg_pub.count_and_get
827 ( p_count => x_msg_count
828 , p_data => x_msg_data);
829 End loop;
830 RAISE FND_API.G_EXC_ERROR;
831 ELSE
832 -- cancel the internal requisitions lines and header for the above order lines and header
833
834 FOR I in 1..lx_oe_line_tbl.count LOOP
835 update po_requisition_lines_all
836 set quantity_cancelled = lx_oe_line_Tbl(I).cancelled_quantity,
837 cancel_flag = 'Y',
838 cancel_reason = lx_oe_line_tbl(I).change_reason,
839 cancel_date = sysdate
840 where requisition_line_id = lx_oe_line_tbl(I).source_document_line_id;
841
842
843 -- update mtl_supply data for the requisition
844 /* IF NOT po_supply.po_req_supply(
845 p_docid => null,
846 p_lineid => lx_oe_line_Tbl(I).source_document_line_id,
847 p_shipid => null,
848 p_action => 'Remove_Req_Line_Supply',
849 p_recreate_flag => NULL,
850 p_qty => NULL,
851 p_receipt_date => NULL) THEN
852 */
853
854 BEGIN
855 UPDATE mtl_supply
856 SET quantity = 0
857 , change_flag = 'Y'
858 WHERE supply_type_code = 'REQ'
859 AND req_line_id = lx_oe_line_Tbl(I).source_document_line_id;
860 EXCEPTION
861 when no_data_found THEN
862
863 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
864 token1 => 'FILE',
865 value1 => 'PO_SUPPLY',
866 token2 => 'ERR_NUMBER',
867 value2 => '035',
868 token3 => 'SUBROUTINE',
869 value3 => 'PO_REQ_SUPPLY()');
870 RAISE FND_API.G_EXC_ERROR;
871 END;
872 -- END IF;
873 END LOOP;
874
875 -- cancel header separately
876 IF (p_header_rec.order_header_id IS NOT NULL) THEN
877 IF (l_source_operating_unit <> l_org_id) THEN
878 IF new_resp_id is not null and
879 new_resp_appl_id is not null THEN
880 fnd_global.apps_initialize(new_user_id,new_resp_id,new_resp_appl_id);
881 ELSE
882 --dbms_application_info.set_client_info(l_source_operating_unit);
883 mo_global.set_policy_context('S',l_source_operating_unit);
884 END IF;
885 END If;
886
887 oe_header_util.Query_Row(
888 p_header_id => l_oe_header_id,
889 x_header_rec => l_oe_header_rec);
890
891 l_oe_header_rec.cancelled_flag := 'Y';
892 l_oe_header_rec.flow_status_code := 'CANCELLED';
893 l_oe_header_rec.open_flag := 'N';
894 l_oe_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
895 l_oe_header_rec.change_reason := p_header_rec.change_reason;
896 l_oe_header_rec.change_comments := p_header_rec.change_comments;
897
898 l_oe_control_rec.controlled_operation := TRUE;
899 l_oe_control_rec.change_attributes := TRUE;
900 l_oe_control_rec.validate_entity := TRUE;
901 l_oe_control_rec.write_to_DB := TRUE;
902 l_oe_control_rec.default_attributes := FALSE;
903 l_oe_control_rec.process := FALSE;
904
905 -- Instruct API to retain its caches
906 l_oe_control_rec.clear_api_cache := FALSE;
907 l_oe_control_rec.clear_api_requests := FALSE;
908
909
910 For I in 1 .. l_oe_line_old_tbl.count LOOP
911 l_oe_line_old_tbl(I).operation := null;
912 l_oe_line_Tbl(i).operation := null;
913 END LOOP;
914
915 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
916 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
917 'Starting OW Debug...');
918
919 oe_debug_pub.G_FILE := NULL;
920 oe_debug_pub.debug_on;
921 oe_debug_pub.initialize;
922 oe_debug_pub.setdebuglevel(5);
923
924 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
925 'OE Debug File : '|| OE_DEBUG_PUB.Set_Debug_Mode('FILE'));
926 end if;
927
928 -- call to Process Order
929 OE_Order_PUB.Process_Order(
930 p_org_id => l_source_operating_unit
931 ,p_api_version_number => l_api_version_number
932 ,p_init_msg_list => FND_API.G_TRUE
933 ,p_return_values => FND_API.G_FALSE
934 ,p_action_commit => FND_API.G_FALSE
935 -- Passing just the entity records that are a part of this order
936 ,p_header_rec => l_oe_header_rec
937 ,p_line_tbl => l_oe_line_tbl
938 ,p_old_line_tbl => l_oe_line_old_tbl
939 -- OUT variables
940 ,x_header_rec => lx_oe_header_rec
941 ,x_header_val_rec => l_oe_Header_Val_rec
942 ,x_header_adj_tbl => l_oe_header_adj_tbl
943 ,x_Header_Adj_val_tbl => l_oe_header_adj_val_tbl
944 ,x_Header_price_Att_tbl => l_oe_header_price_att_tbl
945 ,x_Header_Adj_Att_tbl => l_oe_Header_Adj_Att_Tbl
946 ,x_Header_Adj_Assoc_tbl => l_oe_Header_Adj_Assoc_Tbl
947 ,x_header_scredit_tbl => l_oe_header_scr_tbl
948 ,x_Header_Scredit_val_tbl => l_oe_Header_Scredit_Val_Tbl
949 ,x_line_tbl => lx_oe_line_tbl
950 ,x_line_val_tbl => l_oe_Line_Val_Tbl
951 ,x_line_adj_tbl => l_oe_line_adj_tbl
952 ,x_Line_Adj_val_tbl => l_oe_Line_Adj_Val_Tbl
953 ,x_Line_price_Att_tbl => l_oe_Line_Price_Att_Tbl
954 ,x_Line_Adj_Att_tbl => l_oe_Line_Adj_Att_Tbl
955 ,x_Line_Adj_Assoc_tbl => l_oe_Line_Adj_Assoc_Tbl
956 ,x_Line_Scredit_tbl => l_oe_line_scr_tbl
957 ,x_Line_Scredit_val_tbl => l_oe_Line_Scredit_Val_Tbl
958 ,x_Lot_Serial_tbl => l_oe_Lot_Serial_Tbl
959 ,x_Lot_Serial_val_tbl => l_oe_Lot_Serial_Val_Tbl
960 ,x_action_request_tbl => l_oe_Request_Tbl_Type
961 ,x_return_status => l_return_status
962 ,x_msg_count => l_msg_count
963 ,x_msg_data => l_msg_data
964 );
965
966 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
967 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
968 'done ... with l_return_status = ' || l_return_status);
969 -- Stopping OE Debug...
970 oe_debug_pub.debug_off;
971 end if;
972
973 IF (l_source_operating_unit <> l_org_id) THEN
974 fnd_global.apps_initialize(l_user_id,orig_resp_id,orig_resp_appl_id);
975 mo_global.set_policy_context('S',l_org_id);
976 END If;
977
978 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
979 x_return_status := l_return_status;
980 for counter in REVERSE 1..l_msg_count Loop
981 l_msg := OE_MSG_PUB.Get(counter,FND_API.G_FALSE) ;
982 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
983 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
984 FND_MSG_PUB.ADD;
985 fnd_msg_pub.count_and_get
986 ( p_count => x_msg_count
987 , p_data => x_msg_data);
988 End loop;
989 RAISE FND_API.G_EXC_ERROR;
990 END If;
991 END IF;
992 END IF;
993 END If;
994
995 -- Bug 13417397. Setting the change_flag back to NULL
996 FOR I in 1..lx_oe_line_tbl.count LOOP
997 BEGIN
998 update mtl_supply
999 set change_flag = NULL
1000 where supply_type_code = 'REQ'
1001 and req_line_id = lx_oe_line_Tbl(I).source_document_line_id;
1002 EXCEPTION
1003 WHEN no_data_found THEN
1004 null;
1005 END;
1006 END LOOP;
1007
1008 fnd_msg_pub.count_and_get
1009 ( p_count => x_msg_count
1010 , p_data => x_msg_data);
1011
1012 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1013 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
1014 'exiting with x_return_status = ' || x_return_status);
1015 end if;
1016
1017 EXCEPTION
1018 WHEN FND_API.G_EXC_ERROR THEN
1019 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1020 P_API_NAME => L_API_NAME
1021 ,P_PKG_NAME => G_PKG_NAME
1022 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1023 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1024 ,X_MSG_COUNT => X_MSG_COUNT
1025 ,X_MSG_DATA => X_MSG_DATA
1026 ,X_RETURN_STATUS => X_RETURN_STATUS);
1027 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1028 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1029 P_API_NAME => L_API_NAME
1030 ,P_PKG_NAME => G_PKG_NAME
1031 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1032 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1033 ,X_MSG_COUNT => X_MSG_COUNT
1034 ,X_MSG_DATA => X_MSG_DATA
1035 ,X_RETURN_STATUS => X_RETURN_STATUS);
1036 WHEN OTHERS THEN
1037 Rollback to Cancel_order_pub;
1038 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1039 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
1040 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
1041 FND_MSG_PUB.ADD;
1042 fnd_msg_pub.count_and_get
1043 ( p_count => x_msg_count
1044 , p_data => x_msg_data);
1045 x_return_status := FND_API.G_RET_STS_ERROR;
1046
1047 END;
1048
1049
1050 PROCEDURE cancel_order_line(
1051 p_order_line_id IN NUMBER,
1052 p_cancel_reason IN Varchar2,
1053 x_return_status OUT NOCOPY VARCHAR2,
1054 x_msg_count OUT NOCOPY NUMBER,
1055 x_msg_data OUT NOCOPY VARCHAR2) IS
1056 l_header_rec csp_parts_requirement.header_rec_type;
1057 l_line_table csp_parts_requirement.line_tbl_type;
1058 begin
1059 l_line_table(1).order_line_id := p_order_line_id;
1060 l_line_table(1).change_reason := p_cancel_reason;
1061 Cancel_Order(
1062 p_header_rec => l_header_rec,
1063 p_line_table => l_line_table,
1064 p_process_Type => 'ORDER',
1065 x_return_status => x_return_status,
1066 x_msg_count => x_msg_count,
1067 x_msg_data => x_msg_data);
1068 end;
1069
1070 /**************************************************************************
1071 ***************************************************************************
1072 ***************************************************************************
1073 PROCESS_ORDER
1074 ***************************************************************************
1075 ***************************************************************************
1076 ***************************************************************************/
1077 PROCEDURE process_order(
1078 p_api_version IN NUMBER
1079 ,p_Init_Msg_List IN VARCHAR2
1080 ,p_commit IN VARCHAR2
1081 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.header_rec_type
1082 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
1083 ,p_process_type IN VARCHAR2
1084 ,p_book_order IN VARCHAR2
1085 ,x_return_status OUT NOCOPY VARCHAR2
1086 ,x_msg_count OUT NOCOPY NUMBER
1087 ,x_msg_data OUT NOCOPY VARCHAR2
1088 )
1089 IS
1090 l_action_request_tbl oe_order_pub.request_tbl_type;
1091 l_api_version_number CONSTANT NUMBER := 1.0;
1092 l_api_name CONSTANT VARCHAR2(30) := 'process_order';
1093 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1094 l_msg_count NUMBER;
1095 l_msg_data VARCHAR2(4000);
1096 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1097 l_user_id NUMBER;
1098 l_login_id NUMBER;
1099 l_today DATE;
1100
1101 EXCP_USER_DEFINED EXCEPTION;
1102 INVALID_CHARGE_ACCOUNT EXCEPTION;
1103 INVALID_ACCRUAL_ACCOUNT EXCEPTION;
1104 INVALID_BUDGET_ACCOUNT EXCEPTION;
1105 INVALID_VARIANCE_ACCOUNT EXCEPTION;
1106
1107 l_org_id NUMBER;
1108 l_set_of_books_id NUMBER;
1109 l_request_id NUMBER;
1110 l_order_source_id NUMBER := 10;
1111 l_orig_sys_document_ref VARCHAR2(50);
1112 l_change_sequence VARCHAR2(10);
1113 l_validate_only VARCHAR2(1);
1114 l_init_msg_list VARCHAR2(240);
1115 l_rowid NUMBER;
1116 l_dummy NUMBER;
1117 l_segment1 VARCHAR2(240);
1118 l_employee_id NUMBER := -1;
1119 l_unit_meas_lookup_code VARCHAR2(25);
1120 l_category_id NUMBER;
1121 l_price_list_id NUMBER;
1122 l_line_price_list_id NUMBER;
1123 l_currency_code VARCHAR2(3);
1124 l_unit_price NUMBER;
1125 l_chart_of_Accounts_id NUMBER;
1126
1127 l_customer_id NUMBER;
1128 l_cust_acct_id NUMBER;
1129 l_site_use_id NUMBER;
1130 l_line_type_id NUMBER;
1131 l_order_line_type_id NUMBER;
1132 l_order_line_category_code VARCHAR2(30);
1133 l_order_number NUMBER;
1134 l_source_operating_unit NUMBER;
1135
1136 l_header_rec csp_parts_requirement.header_rec_type;
1137 l_line_rec csp_parts_requirement.line_rec_type;
1138 l_line_tbl csp_parts_requirement.Line_tbl_type;
1139 l_dist_Rec csp_parts_order.req_dist_rec_type;
1140
1141 l_transferred_to_oe_flag VARCHAR2(1) := 'Y';
1142 l_msg varchar2(2000);
1143 -- Record and table types for oe process_order
1144 l_oe_header_rec oe_order_pub.header_rec_type := OE_ORDER_PUB.G_MISS_HEADER_REC;
1145 lx_oe_header_rec oe_order_pub.header_rec_type := OE_ORDER_PUB.G_MISS_HEADER_REC;
1146 l_oe_Header_Val_rec oe_order_pub.header_val_rec_type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
1147
1148 l_oe_header_adj_tbl oe_order_pub.header_adj_tbl_type;
1149 l_oe_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;
1150 l_oe_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
1151 l_oe_Header_Adj_Att_Tbl oe_order_pub.header_adj_att_tbl_type;
1152 l_oe_Header_Adj_Assoc_Tbl oe_order_pub.header_adj_assoc_tbl_type;
1153 l_oe_header_scr_tbl OE_ORDER_PUB.header_scredit_tbl_type;
1154 l_oe_Header_Scredit_Val_Tbl OE_ORDER_PUB.header_scredit_Val_tbl_type;
1155 l_oe_line_rec oe_order_pub.line_rec_type := OE_ORDER_PUB.G_MISS_LINE_REC;
1156 l_oe_line_tbl oe_order_pub.line_tbl_type := OE_ORDER_PUB.G_MISS_LINE_TBL;
1157 lx_oe_line_tbl oe_order_pub.line_tbl_type := OE_ORDER_PUB.G_MISS_LINE_TBL;
1158 l_oe_Line_Val_Tbl oe_order_pub.line_Val_tbl_type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
1159 l_oe_line_adj_tbl oe_order_pub.line_adj_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
1160 l_oe_Line_Adj_Val_Tbl oe_order_pub.line_adj_val_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
1161 l_oe_Line_Price_Att_Tbl oe_order_pub.line_price_att_tbl_type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
1162 l_oe_Line_Adj_Att_Tbl oe_order_pub.line_adj_att_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
1163 l_oe_Line_Adj_Assoc_tbl oe_order_pub.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
1164 l_oe_line_scr_tbl oe_order_pub.line_scredit_tbl_type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
1165 l_oe_Line_Scredit_Val_Tbl oe_order_pub.line_scredit_val_tbl_type;
1166 l_oe_Lot_Serial_Tbl oe_order_pub.lot_serial_tbl_type;
1167 l_oe_Lot_Serial_Val_Tbl oe_order_pub.lot_serial_val_tbl_type;
1168 l_oe_Request_Tbl_Type oe_order_pub.Request_tbl_type := OE_ORDER_PUB.G_MISS_REQUEST_TBL;
1169 l_oe_control_rec OE_GLOBALS.Control_Rec_Type;
1170
1171 CURSOR rowid_cur IS
1172 SELECT rowid FROM PO_REQUISITION_HEADERS
1173 WHERE requisition_header_id = l_header_rec.requisition_header_id;
1174
1175 -- Get requisition_number (PO_REQUSITION_HEADERS.segment1)
1176 CURSOR req_number_cur IS
1177 SELECT to_char(current_max_unique_identifier + 1)
1178 FROM po_unique_identifier_control
1179 WHERE table_name = 'PO_REQUISITION_HEADERS'
1180 FOR UPDATE OF current_max_unique_identifier;
1181
1182 -- Get unique requisition_header_id
1183 CURSOR req_header_id_cur IS
1184 SELECT po_requisition_headers_s.nextval
1185 FROM sys.dual;
1186
1187 -- Get unique requisition_line_id
1188 CURSOR req_line_id_cur IS
1189 SELECT po_requisition_lines_s.nextval
1190 FROM sys.dual;
1191
1192 -- Cursor to get unique Distribution_id
1193 CURSOR dist_line_id_cur IS
1194 SELECT po_req_distributions_s.nextval
1195 FROM sys.dual;
1196
1197 -- Cursor to get Accrual Account ID and Variance Account ID
1198 -- For Destination Type Code INVENTORY get accrual account id
1199 -- from MTL_PARAMETERS
1200 -- Per Requisition Import program (pocis.opc).
1201 CURSOR accrual_account_id_cur (p_destination_organization_id NUMBER) IS
1202 SELECT mp.ap_accrual_account,
1203 mp.invoice_price_var_account
1204 FROM mtl_parameters mp
1205 WHERE mp.organization_id = p_destination_organization_id;
1206
1207 -- Get Item Category ID
1208 -- As in Requisition Import
1209 CURSOR item_category_cur(p_item_id NUMBER, p_destination_org_id NUMBER) IS
1210 SELECT mic.category_id
1211 FROM mtl_item_categories mic,
1212 mtl_default_sets_view mdsv
1213 WHERE mic.inventory_item_id = p_item_id
1214 AND mic.organization_id = p_destination_org_id
1215 AND mic.category_set_id = mdsv.category_set_id
1216 AND mdsv.functional_area_id = 2;
1217
1218 -- Get Set of Books ID for a given Org_ID - Mandatory in PO_REQ_DISTRIBUTIONS
1219
1220 CURSOR set_of_books_cur (p_organization_id NUMBER) IS
1221 SELECT set_of_books_id
1222 FROM hr_operating_units
1223 WHERE organization_id = p_organization_id;
1224
1225 -- If encumbrance flag is 'Y' get the budget account
1226 -- For Internal Req, Destination Type Code will be INVENTORY
1227 -- Hence, it is assumed that the budget account will come
1228 -- from MTL_PARAMETERS for the Item and the Destination Organization
1229 CURSOR budget_account_cur (p_destination_organization_id NUMBER,
1230 p_item_id NUMBER) IS
1231 SELECT nvl (msi.encumbrance_account,mp.encumbrance_account)
1232 FROM mtl_system_items msi,
1233 mtl_parameters mp
1234 WHERE msi.inventory_item_id = p_item_id
1235 AND msi.organization_id = p_destination_organization_id
1236 AND mp.organization_id = msi.organization_id;
1237
1238 -- Get Requisition Encumbrance Flag for the Set of Books
1239 -- Based on this flag Budget Account will be populated
1240 -- in PO_REQ_DISTRIBUTIONS
1241 CURSOR req_encumbrance_cur (p_set_of_books_id NUMBER) IS
1242 SELECT nvl (fsp.req_encumbrance_flag,'N')
1243 FROM financials_system_parameters fsp
1244 WHERE fsp.set_of_books_id = p_set_of_books_id;
1245
1246 -- Get Charge Account for the Item and Organization
1247 CURSOR charge_account_cur (p_destination_organization_id NUMBER,
1248 p_item_id NUMBER) IS
1249 SELECT NVL(expense_account,-1)
1250 FROM mtl_system_items
1251 WHERE inventory_item_id = p_item_id
1252 AND organization_id = p_destination_organization_id;
1253
1254 -- Get Unit_of_Measure from MTL_UNIT_OF_MEASURES, since OM passes
1255 -- only UOM_CODE and PO requires UNIT_OF_MEASURE. This is being done
1256 -- to fix the problem of line not showing up from POXRQVRQ form
1257 CURSOR unit_of_measure_cur (p_uom_code VARCHAR2) IS
1258 SELECT mum.unit_of_measure
1259 FROM mtl_units_of_measure mum
1260 WHERE mum.uom_code = p_uom_code;
1261
1262 -- Get default line type
1263 CURSOR line_type_cur IS
1264 SELECT psp.line_type_id,
1265 plt.order_type_lookup_code,
1266 plt.purchase_basis,
1267 plt.matching_basis
1268 FROM PO_SYSTEM_PARAMETERS_ALL psp,
1269 PO_LINE_TYPES plt
1270 WHERE psp.org_id = l_org_id
1271 AND plt.line_type_id = psp.line_type_id;
1272
1273 l_line_type_rec line_Type_cur%ROWTYPE;
1274
1275 -- Get preparer_id
1276 CURSOR employee_id_cur IS
1277 SELECT employee_id
1278 FROM fnd_user
1279 WHERE user_id = l_user_id;
1280
1281 -- Get site_use_id for the inventory location id
1282 CURSOR cust_site_cur IS
1283 SELECT pol.customer_id, pol.site_use_id, cust_acct.cust_account_id
1284 FROM PO_LOCATION_ASSOCIATIONS_ALL pol,
1285 HZ_CUST_ACCT_SITES_ALL cust_acct,
1286 HZ_CUST_SITE_USES_ALL site_use
1287 WHERE pol.location_id = l_header_rec.ship_to_location_id
1288 AND site_use.site_use_id = pol.site_use_id
1289 AND cust_acct.cust_acct_site_id = site_use.cust_acct_site_id
1290 AND pol.org_id = l_source_operating_unit;
1291
1292 CURSOR get_cust_site_id IS
1293 SELECT cust_acct.cust_acct_site_id,
1294 pol.customer_id,
1295 (SELECT cust_acct2.party_site_id
1296 FROM HZ_CUST_ACCT_SITES_ALL cust_acct2,
1297 HZ_CUST_SITE_USES_ALL site_use2
1298 WHERE cust_acct2.party_site_id = cust_acct.party_site_id
1299 AND cust_acct2.org_id = l_source_operating_unit
1300 AND cust_acct2.cust_account_id = cust_acct.cust_account_id
1301 AND cust_acct2.cust_acct_site_id = site_use2.cust_acct_site_id
1302 AND site_use2.site_use_code = 'SHIP_TO'
1303 AND site_use2.status = 'A'
1304 AND cust_acct2.status = 'A'
1305 ),
1306 pol.org_id
1307 FROM PO_LOCATION_ASSOCIATIONS_ALL pol,
1308 HZ_CUST_SITE_USES_ALL site_use,
1309 HZ_CUST_ACCT_SITES_ALL cust_acct
1310 WHERE pol.location_id = l_header_rec.ship_to_location_id
1311 AND pol.site_use_id = site_use.site_use_id
1312 AND site_use.cust_acct_site_id = cust_acct.cust_acct_site_id;
1313
1314 CURSOR get_cust_site_id2 IS
1315 SELECT cust_acct.cust_acct_site_id,
1316 pol.customer_id,
1317 (SELECT cust_acct2.party_site_id
1318 FROM HZ_CUST_ACCT_SITES_ALL cust_acct2
1319 WHERE cust_acct2.party_site_id = cust_acct.party_site_id
1320 AND cust_acct2.org_id = l_source_operating_unit
1321 AND cust_acct2.cust_account_id = cust_acct.cust_account_id
1322 AND cust_acct2.status = 'A'
1323 ),
1324 pol.org_id,
1325 (SELECT cust_acct2.cust_acct_site_id
1326 FROM HZ_CUST_ACCT_SITES_ALL cust_acct2
1327 WHERE cust_acct2.party_site_id = cust_acct.party_site_id
1328 AND cust_acct2.org_id = l_source_operating_unit
1329 AND cust_acct2.cust_account_id = cust_acct.cust_account_id
1330 )
1331 FROM PO_LOCATION_ASSOCIATIONS_ALL pol,
1332 HZ_CUST_SITE_USES_ALL site_use,
1333 HZ_CUST_ACCT_SITES_ALL cust_acct
1334 WHERE pol.location_id = l_header_rec.ship_to_location_id
1335 AND pol.site_use_id = site_use.site_use_id
1336 AND site_use.cust_acct_site_id = cust_acct.cust_acct_site_id;
1337
1338 l_tmp_org_id number;
1339 l_party_site_id number;
1340 l_cust_site_id number;
1341 l_cust_acct_site_use_id NUMBER;
1342 v_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
1343 v_cust_site_use_rec hz_cust_account_site_v2pub.CUST_SITE_USE_REC_TYPE;
1344 v_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
1345 x_cust_acct_site_id NUMBER;
1346 x_site_use_id NUMBER;
1347 temp_bill_to_site_id number;
1348 temp_ship_to_use_id number;
1349
1350 CURSOR get_new_cust_ids IS
1351 select
1352 cust_acct.cust_acct_site_id,
1353 site_use.site_use_id
1354 from
1355 HZ_CUST_SITE_USES_ALL site_use,
1356 HZ_CUST_ACCT_SITES_ALL cust_acct
1357 where
1358 cust_acct.party_site_id = l_party_site_id
1359 -- AND cust_acct.cust_account_id = l_customer_id -- bug # 12545721
1360 AND cust_acct.org_id = l_source_operating_unit
1361 AND cust_acct.cust_acct_site_id = site_use.cust_acct_site_id
1362 AND site_use.site_use_code = 'SHIP_TO'
1363 AND site_use.status = 'A'
1364 AND rownum = 1;
1365
1366 cursor get_primary_bill_to (p_operating_unit number) is
1367 select site_use_id, cust_acct_site_id from HZ_CUST_SITE_USES_ALL where cust_acct_site_id in
1368 (select hsa.cust_acct_site_id from HZ_CUST_ACCT_SITES_ALL hsa
1369 where hsa.cust_account_id = l_cust_acct_id
1370 and hsa.status = 'A'
1371 and hsa.org_id = p_operating_unit)
1372 and site_use_code = 'BILL_TO'
1373 and primary_flag = 'Y'
1374 and status = 'A';
1375
1376 v_primary_bill_site_use_id number;
1377 v_primary_bill_site_id number;
1378
1379 cursor get_cust_acct_site_uses is
1380 SELECT site_use_id
1381 FROM hz_cust_site_uses_all
1382 WHERE cust_acct_site_id = l_cust_site_id
1383 AND status = 'A'
1384 AND site_use_code NOT IN
1385 (SELECT site_use_code
1386 FROM hz_cust_site_uses_all
1387 WHERE cust_acct_site_id = x_cust_acct_site_id
1388 AND status = 'A'
1389 );
1390
1391 v_ship_bill_site number;
1392 v_bill_site_id number;
1393 v_bill_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
1394 v_pri_bill_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
1395 x_bill_acct_site_id number;
1396 x_pri_bill_acct_site_id number;
1397 l_bill_acct_site_use_id number;
1398 v_bill_site_use_rec hz_cust_account_site_v2pub.CUST_SITE_USE_REC_TYPE;
1399 v_pri_bill_site_use_rec hz_cust_account_site_v2pub.CUST_SITE_USE_REC_TYPE;
1400 v_bill_cust_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
1401 v_pri_bill_cust_prf_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
1402
1403 cursor get_bill_site_id is
1404 select cust_acct_site_id
1405 from HZ_CUST_SITE_USES_ALL
1406 where site_use_code = 'BILL_TO'
1407 and site_use_id = v_ship_bill_site;
1408
1409 cursor check_bill_to_location is
1410 select newu.site_use_id
1411 from HZ_CUST_SITE_USES_ALL orgu,
1412 HZ_CUST_ACCT_SITES_ALL orgs,
1413 HZ_CUST_SITE_USES_ALL newu,
1414 HZ_CUST_ACCT_SITES_ALL news
1415 where orgu.site_use_code = 'BILL_TO'
1416 and orgu.site_use_id = v_ship_bill_site
1417 and orgu.cust_acct_site_id = orgs.cust_acct_site_id
1418 and news.party_site_id = orgs.party_site_id
1419 and news.cust_acct_site_id = newu.cust_acct_site_id
1420 and newu.site_use_code = 'BILL_TO'
1421 and newu.location = orgu.location
1422 and news.org_id = l_source_operating_unit;
1423
1424 l_existing_bill_to number;
1425
1426 /*
1427 cursor get_bill_acct_site_uses is
1428 select site_use_id
1429 from hz_cust_site_uses_all
1430 where cust_acct_site_id = v_bill_site_id
1431 and site_use_id = v_ship_bill_site;
1432 */
1433
1434 -- end of bug # 7759059
1435
1436 -- Get Item Description for a given Item ID
1437 -- For the purpose of creating Approved Internal Requisition
1438 -- it is assumed that the calling procedure will always pass the Item ID
1439 -- so that Item Description can be derived.
1440 CURSOR item_Desc_cur(p_item_id NUMBER, p_orgn_id NUMBER) IS
1441 SELECT description
1442 FROM mtl_system_items_b
1443 WHERE inventory_item_id = p_item_id
1444 AND organization_id = p_orgn_id;
1445 -- Bug 14494663 - Not required as in case of special ship address as we cannot create address
1446 /* CURSOR rs_loc_exists_cur(p_inv_loc_id NUMBER, p_resource_id NUMBER, p_resource_type VARCHAR2) IS
1447
1448 SELECT ps.location_id site_loc_id
1449 from csp_rs_cust_relations rcr,
1450 hz_cust_acct_sites cas,
1451 hz_cust_site_uses csu,
1452 po_location_associations pla,
1453 hz_party_sites ps
1454 where rcr.customer_id = cas.cust_account_id
1455 and cas.cust_acct_site_id = csu.cust_acct_site_id (+)
1456 and csu.site_use_code = 'SHIP_TO'
1457 and csu.site_use_id = pla.site_use_id
1458 and cas.party_site_id = ps.party_site_id
1459 and rcr.resource_type = p_resource_type
1460 and rcr.resource_id = p_resource_id
1461 and pla.location_id = p_inv_loc_id;*/
1462
1463 CURSOR address_type_cur(p_rqmt_header_id NUMBER) IS
1464 SELECT crh.address_type,
1465 crh.ship_to_location_id,
1466 decode(crh.task_assignment_id,null,crh.resource_id,jta.resource_id),
1467 decode(crh.task_assignment_id,null,crh.resource_type,jta.resource_type_code)
1468 from jtf_task_assignments jta,
1469 csp_requirement_headers crh
1470 where jta.task_assignment_id(+) = crh.task_assignment_id
1471 and crh.requirement_header_id = p_rqmt_header_id;
1472
1473 /* bug # 8474563
1474 CURSOR get_new_context(p_new_org_id number) IS
1475 SELECT org_information2 ,
1476 org_information3 ,
1477 org_information4
1478 FROM hr_organization_information hou
1479 WHERE hou.organization_id = p_new_org_id
1480 AND hou.org_information1 = 'FIELD_SERVICE'
1481 AND hou.org_information_context = 'CS_USER_CONTEXT';
1482 */
1483
1484 -- bug # 6471559
1485 cursor get_bill_to_for_sr (p_rqmt_header_id NUMBER) IS
1486 SELECT site_use.site_use_id, cia.ship_to_contact_id, cia.bill_to_contact_id
1487 FROM HZ_CUST_ACCT_SITES_ALL cust_acct,
1488 HZ_CUST_SITE_USES_ALL site_use,
1489 hz_party_site_uses hpsu,
1490 cs_incidents_all cia,
1491 csp_requirement_headers_v req
1492 WHERE req.requirement_header_id = p_rqmt_header_id
1493 and cia.incident_id = req.incident_id
1494 and cust_acct.cust_account_id = cia.bill_to_account_id
1495 and hpsu.party_site_use_id = cia.bill_to_site_use_id
1496 and cust_acct.party_site_id = hpsu.party_site_id
1497 and cust_acct.cust_acct_site_id = site_use.cust_acct_site_id
1498 and site_use.site_use_code = 'BILL_TO'
1499 and site_use.org_id = cia.org_id
1500 and site_use.status = 'A';
1501
1502 l_bill_to_site_use_id number;
1503
1504 cursor get_res_primary_bill_to(p_resource_id NUMBER, p_resource_type VARCHAR2) IS
1505 select csu.site_use_id
1506 from
1507 csp_rs_cust_relations cr,
1508 hz_cust_acct_sites cas,
1509 hz_cust_site_uses csu
1510 where
1511 cr.resource_type = p_resource_type
1512 and cr.resource_id = p_resource_id
1513 and cr.customer_id = cas.cust_account_id
1514 and cas.bill_to_flag = 'P'
1515 and cas.cust_acct_site_id = csu.cust_acct_site_id
1516 and csu.site_use_code = 'BILL_TO';
1517
1518 l_sr_org_id number;
1519 new_user VARCHAR2(240);
1520
1521 cursor c_sr_src_bill_to (v_rqmt_header_id NUMBER, v_src_org_id number) is
1522 SELECT site_use.site_use_id
1523 FROM HZ_CUST_ACCT_SITES_ALL cust_acct,
1524 HZ_CUST_SITE_USES_ALL site_use,
1525 hz_party_site_uses hpsu,
1526 cs_incidents_all cia,
1527 csp_requirement_headers_v req
1528 WHERE req.requirement_header_id = v_rqmt_header_id
1529 and req.address_type in ('C', 'T') -- bug # 14743823
1530 and cia.incident_id = req.incident_id
1531 and cust_acct.cust_account_id = cia.bill_to_account_id
1532 and hpsu.party_site_use_id = cia.bill_to_site_use_id
1533 and cust_acct.party_site_id = hpsu.party_site_id
1534 and cust_acct.cust_acct_site_id = site_use.cust_acct_site_id
1535 and site_use.site_use_code = 'BILL_TO'
1536 and site_use.org_id = v_src_org_id
1537 and site_use.status = 'A';
1538
1539 -- bug # 9320107
1540 l_ship_to_contact_id number;
1541 l_invoice_to_contact_id number;
1542 l_pr_ship_to_contact_id number;
1543
1544 l_ship_to_contact_id_final number;
1545 l_invoice_to_contact_id_final number;
1546
1547 cursor get_valid_contact_id (v_party_id number, v_org_id number) is
1548 SELECT ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
1549 FROM
1550 HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
1551 HZ_CUST_SITE_USES_ALL SITE_USE,
1552 HZ_CUST_ACCT_SITES_all ADDR
1553 WHERE
1554 ACCT_ROLE.party_id = v_party_id
1555 AND ACCT_ROLE.CUST_ACCOUNT_ID = ADDR.CUST_ACCOUNT_ID
1556 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1557 AND ADDR.CUST_ACCT_SITE_ID = SITE_USE.CUST_ACCT_SITE_ID
1558 AND SITE_USE.SITE_USE_ID = v_org_id
1559 AND SITE_USE.STATUS = 'A'
1560 AND ADDR.STATUS ='A'
1561 AND ACCT_ROLE.STATUS = 'A'
1562 AND ROWNUM = 1;
1563
1564 CURSOR validate_order_type (v_org_id number, v_order_type_id number) IS
1565 SELECT 1
1566 FROM oe_transaction_types_all ot
1567 WHERE ot.org_id = v_org_id
1568 AND ot.transaction_type_id = v_order_type_id
1569 AND sysdate BETWEEN NVL(ot.start_date_active, sysdate-1)
1570 AND NVL(ot.end_date_active, sysdate+1);
1571
1572 orig_org_id number;
1573 orig_user_id number;
1574 orig_resp_id number;
1575 orig_resp_appl_id number;
1576 new_org_id number;
1577 new_user_id number;
1578 new_resp_id number;
1579 new_resp_appl_id number;
1580
1581 l_address_type VARCHAR2(30);
1582 l_ship_to_location_id NUMBER;
1583 l_site_loc_id NUMBER;
1584 l_resource_id NUMBER;
1585 l_resource_type VARCHAR2(240);
1586 l_object_version_number NUMBER;
1587 l_item_cost NUMBER;
1588 l_scheduling_code VARCHAR2(30);
1589 l_dest_operating_unit number;
1590 l_first_org_id number;
1591 l_is_valid_order_type number;
1592
1593 BEGIN
1594
1595 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1596 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1597 'csp.plsql.csp_parts_order.process_order',
1598 'Begin');
1599 end if;
1600
1601 SAVEPOINT Process_Order_PUB;
1602
1603 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1604 -- initialize message list
1605 FND_MSG_PUB.initialize;
1606 END IF;
1607
1608 -- Standard call to check for call compatibility.
1609 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1610 p_api_version,
1611 l_api_name,
1612 G_PKG_NAME)
1613 THEN
1614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1615 END IF;
1616
1617 -- initialize return status
1618 x_return_status := FND_API.G_RET_STS_SUCCESS;
1619
1620 l_user_id := nvl(fnd_global.user_id, 0) ;
1621 fnd_profile.get('RESP_ID',orig_resp_id);
1622 fnd_profile.get('RESP_APPL_ID',orig_resp_appl_id);
1623
1624 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1625 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1626 'csp.plsql.csp_parts_order.process_order',
1627 'l_user_id = ' || l_user_id
1628 || ', orig_resp_id = ' || orig_resp_id
1629 || ', orig_resp_appl_id = ' || orig_resp_appl_id);
1630 end if;
1631
1632 l_header_rec := px_header_rec;
1633 l_line_tbl := px_line_table;
1634
1635 IF (l_line_Tbl.count <= 0 AND l_header_rec.operation <> CSP_PARTS_ORDER.G_OPR_CANCEL) THEN
1636 return;
1637 END IF;
1638
1639 if l_header_rec.ship_to_location_id is null then
1640 select
1641 nvl(sub.location_id, org.location_id)
1642 into l_header_rec.ship_to_location_id
1643 from
1644 MTL_SECONDARY_INVENTORIES sub,
1645 hr_all_organization_units org
1646 where org.organization_id = l_header_rec.dest_organization_id
1647 and org.organization_id = sub.organization_id(+)
1648 and sub.secondary_inventory_name(+) = l_header_rec.dest_subinventory;
1649 end if;
1650
1651 -- get all the values required to insert into po_requisition_header table
1652 SELECT Sysdate INTO l_today FROM dual;
1653 l_user_id := nvl(fnd_global.user_id, 0) ;
1654 l_login_id := nvl(fnd_global.login_id, -1);
1655
1656 -- operating unit
1657 BEGIN
1658 /*SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
1659 INTO l_org_id
1660 FROM dual;*/
1661
1662 l_org_id := mo_global.get_current_org_id;
1663 l_first_org_id := l_org_id;
1664
1665 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1666 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1667 'csp.plsql.csp_parts_order.process_order',
1668 'Original l_org_id from context = ' || l_org_id);
1669 end if;
1670
1671 BEGIN
1672 SELECT operating_unit
1673 INTO l_dest_operating_unit
1674 FROM org_organization_Definitions
1675 WHERE organization_id = l_header_rec.dest_organization_id;
1676 EXCEPTION
1677 WHEN NO_DATA_FOUND THEN
1678 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
1679 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_dest_operating_unit', FALSE);
1680 FND_MSG_PUB.ADD;
1681 RAISE EXCP_USER_DEFINED;
1682 END;
1683
1684 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1685 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1686 'csp.plsql.csp_parts_order.process_order',
1687 'l_dest_operating_unit = ' || l_dest_operating_unit);
1688 end if;
1689
1690 if l_dest_operating_unit is not null
1691 and l_dest_operating_unit <> nvl(l_org_id, -999) then
1692
1693 l_org_id := l_dest_operating_unit;
1694
1695 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1696 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1697 'csp.plsql.csp_parts_order.process_order',
1698 'l_org_id changed to = ' || l_org_id);
1699 end if;
1700
1701 end if;
1702
1703 po_moac_utils_pvt.set_org_context(l_org_id);
1704 l_sr_org_id := l_org_id;
1705
1706 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1707 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1708 'csp.plsql.csp_parts_order.process_order',
1709 'Setting org context for l_org_id = ' || l_org_id);
1710 end if;
1711
1712 EXCEPTION
1713 WHEN NO_DATA_FOUND THEN
1714 null;
1715 END;
1716
1717 -- Get Set of Books Id.
1718 -- this is a required field for po_Req_distributions
1719 OPEN set_of_books_cur (l_org_id);
1720 FETCH set_of_books_cur INTO l_set_of_books_id;
1721 CLOSE set_of_books_cur;
1722
1723 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1724 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1725 'csp.plsql.csp_parts_order.process_order',
1726 'l_header_rec.operation = ' || l_header_rec.operation);
1727 end if;
1728
1729 IF (l_header_rec.operation = CSP_PARTS_ORDER.G_OPR_CANCEL) THEN
1730 Cancel_Order( p_header_rec => l_header_rec,
1731 p_line_table => l_line_tbl,
1732 p_process_type => p_process_type,
1733 x_return_status => l_return_status,
1734 x_msg_count => l_msg_count,
1735 x_msg_data => l_msg_data
1736 );
1737
1738 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1739 RAISE FND_API.G_EXC_ERROR;
1740 END IF;
1741 ELSE
1742
1743 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1744 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1745 'csp.plsql.csp_parts_order.process_order',
1746 'p_process_Type = ' || p_process_Type);
1747 end if;
1748
1749 IF (p_process_Type = 'BOTH' or p_process_type = 'ORDER') THEN
1750 -- if address type is special check to see if the location exists in the engineers list
1751 -- if it does not exist, add it to the list
1752 IF (l_header_rec.requirement_header_id IS NOT NULL) THEN
1753 OPEN address_type_cur(l_header_rec.requirement_header_id);
1754 FETCH address_type_cur INTO l_address_type, l_ship_to_location_id, l_resource_id, l_resource_type;
1755 CLOSE address_type_cur;
1756
1757 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1758 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1759 'csp.plsql.csp_parts_order.process_order',
1760 'l_address_type = ' || l_address_type
1761 || ', l_ship_to_location_id = ' || l_ship_to_location_id
1762 || ', l_resource_id = ' || l_resource_id
1763 || ', l_resource_type = ' || l_resource_type);
1764 end if;
1765
1766 -- bug 12401673
1767 -- as we change ship to address, we should not use DB data
1768 -- we should get preference to whatever data passed to this API
1769
1770 l_address_type := nvl(l_header_rec.address_type, l_address_type);
1771 l_ship_to_location_id := nvl(l_header_rec.ship_to_location_id, l_ship_to_location_id);
1772
1773 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1774 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1775 'csp.plsql.csp_parts_order.process_order',
1776 'l_address_type = ' || l_address_type
1777 || ', l_ship_to_location_id = ' || l_ship_to_location_id);
1778 end if;
1779
1780 IF (l_address_type = 'S') THEN
1781 -- Bug 14494663 - Not required as in case of special ship address as we cannot create address
1782 /*OPEN rs_loc_exists_cur(l_ship_to_location_id, l_resource_id, l_resource_type);
1783 FETCH rs_loc_exists_cur INTO l_site_loc_id;
1784 IF (rs_loc_exists_cur%NOTFOUND) THEN
1785 -- call ship_to_address_handler for creating resource address
1786 csp_ship_to_address_pvt.ship_to_address_handler(
1787 P_TASK_ASSIGNMENT_ID => l_header_rec.task_assignment_id
1788 ,P_RESOURCE_TYPE => l_resource_type
1789 ,P_RESOURCE_ID => l_resource_id
1790 ,P_CUSTOMER_ID => l_customer_id
1791 ,P_LOCATION_ID => l_ship_to_location_id
1792 ,P_STYLE => null
1793 ,P_ADDRESS_LINE_1 => null
1794 ,P_ADDRESS_LINE_2 => null
1795 ,P_ADDRESS_LINE_3 => null
1796 ,P_COUNTRY => null
1797 ,P_POSTAL_CODE => null
1798 ,P_REGION_1 => null
1799 ,P_REGION_2 => null
1800 ,P_REGION_3 => null
1801 ,P_TOWN_OR_CITY => null
1802 ,P_TAX_NAME => null
1803 ,P_TELEPHONE_NUMBER_1 => null
1804 ,P_TELEPHONE_NUMBER_2 => null
1805 ,P_TELEPHONE_NUMBER_3 => null
1806 ,P_LOC_INFORMATION13 => null
1807 ,P_LOC_INFORMATION14 => null
1808 ,P_LOC_INFORMATION15 => null
1809 ,P_LOC_INFORMATION16 => null
1810 ,P_LOC_INFORMATION17 => null
1811 ,P_LOC_INFORMATION18 => null
1812 ,P_LOC_INFORMATION19 => null
1813 ,P_LOC_INFORMATION20 => null
1814 ,P_TIMEZONE => null
1815 ,P_PRIMARY_FLAG => null
1816 ,P_STATUS => null
1817 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
1818 ,p_API_VERSION_NUMBER => l_api_version_number
1819 ,P_INIT_MSG_LIST => 'T'
1820 ,P_COMMIT => 'F'
1821 ,P_ATTRIBUTE_CATEGORY => NULL
1822 ,P_ATTRIBUTE1 => NULL
1823 ,P_ATTRIBUTE2 => NULL
1824 ,P_ATTRIBUTE3 => NULL
1825 ,P_ATTRIBUTE4 => NULL
1826 ,P_ATTRIBUTE5 => NULL
1827 ,P_ATTRIBUTE6 => NULL
1828 ,P_ATTRIBUTE7 => NULL
1829 ,P_ATTRIBUTE8 => NULL
1830 ,P_ATTRIBUTE9 => NULL
1831 ,P_ATTRIBUTE10 => NULL
1832 ,P_ATTRIBUTE11 => NULL
1833 ,P_ATTRIBUTE12 => NULL
1834 ,P_ATTRIBUTE13 => NULL
1835 ,P_ATTRIBUTE14 => NULL
1836 ,P_ATTRIBUTE15 => NULL
1837 ,P_ATTRIBUTE16 => NULL
1838 ,P_ATTRIBUTE17 => NULL
1839 ,P_ATTRIBUTE18 => NULL
1840 ,P_ATTRIBUTE19 => NULL
1841 ,P_ATTRIBUTE20 => NULL
1842 ,X_RETURN_STATUS => l_return_status
1843 ,X_MSG_COUNT => l_msg_count
1844 ,X_MSG_DATA => l_msg_data
1845 );
1846
1847 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1848 RAISE FND_API.G_EXC_ERROR;
1849 END IF;
1850
1851 END If;
1852 CLOSE rs_loc_exists_cur;*/
1853
1854 open get_res_primary_bill_to(l_resource_id, l_resource_type);
1855 fetch get_res_primary_bill_to into l_bill_to_site_use_id;
1856 close get_res_primary_bill_to;
1857
1858 -- bug # 6471559
1859 ELSIF (l_address_type = 'C') THEN
1860 open get_bill_to_for_sr(l_header_rec.requirement_header_id);
1861 fetch get_bill_to_for_sr into l_bill_to_site_use_id, l_ship_to_contact_id, l_invoice_to_contact_id;
1862 close get_bill_to_for_sr;
1863
1864 l_sr_org_id := null;
1865 SELECT cia.org_id
1866 INTO l_sr_org_id
1867 FROM cs_incidents_all cia,
1868 csp_requirement_headers_v req
1869 WHERE req.requirement_header_id = l_header_rec.requirement_header_id
1870 AND cia.incident_id = req.incident_id;
1871
1872 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1873 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1874 'csp.plsql.csp_parts_order.process_order',
1875 'l_sr_org_id = ' || l_sr_org_id);
1876 end if;
1877
1878 ELSIF (l_address_type = 'R') THEN
1879 open get_res_primary_bill_to(l_resource_id, l_resource_type);
1880 fetch get_res_primary_bill_to into l_bill_to_site_use_id;
1881 close get_res_primary_bill_to;
1882 END If;
1883
1884 -- bug # 13707506
1885 -- use ship to contact id while creating an internal order
1886 select nvl(ship_to_contact_id, -9999)
1887 into l_pr_ship_to_contact_id
1888 from csp_requirement_headers
1889 where requirement_header_id = l_header_rec.requirement_header_id;
1890
1891 if l_pr_ship_to_contact_id <> -9999 then
1892 SELECT party_id
1893 into l_ship_to_contact_id
1894 FROM HZ_CUST_ACCOUNT_ROLES
1895 WHERE cust_account_role_id = l_pr_ship_to_contact_id;
1896 end if;
1897 -- end of bug # 13707506
1898
1899 END IF;
1900 END If;
1901
1902
1903
1904 IF (l_header_rec.operation = G_OPR_CREATE) THEN
1905
1906 IF (p_process_Type IN ('REQUISITION', 'BOTH')) THEN
1907
1908 -- requisition_header_id
1909 IF l_header_rec.requisition_header_id is null then
1910 OPEN req_header_id_cur;
1911 FETCH req_header_id_cur into l_header_rec.requisition_header_id;
1912 CLOSE req_header_id_cur;
1913 END IF;
1914
1915 -- Requisition_number
1916 IF l_header_rec.requisition_number IS NULL THEN
1917 OPEN req_number_cur;
1918 FETCH req_number_cur INTO l_header_rec.requisition_number;
1919 UPDATE po_unique_identifier_control
1920 SET current_max_unique_identifier
1921 = current_max_unique_identifier + 1
1922 WHERE CURRENT of req_number_cur;
1923 CLOSE req_number_cur;
1924 END IF;
1925
1926 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1927 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1928 'csp.plsql.csp_parts_order.process_order',
1929 'l_header_rec.requisition_header_id = ' || l_header_rec.requisition_header_id
1930 || ', l_header_rec.requisition_number = ' || l_header_rec.requisition_number);
1931 end if;
1932
1933 -- preparer id
1934 IF l_user_id IS NOT NULL THEN
1935 OPEN employee_id_cur;
1936 FETCH employee_id_cur into l_employee_id;
1937 CLOSE employee_id_cur;
1938
1939 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1940 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1941 'csp.plsql.csp_parts_order.process_order',
1942 'l_employee_id = ' || l_employee_id);
1943 end if;
1944
1945 -- bug 12805692
1946 -- if employee_id not found then use CSP_EMPLOYEE_ID profile value
1947 IF l_employee_id is null then
1948 FND_PROFILE.GET('CSP_EMPLOYEE_ID', l_employee_id);
1949 end if;
1950
1951 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1952 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1953 'csp.plsql.csp_parts_order.process_order',
1954 'l_employee_id = ' || l_employee_id);
1955 end if;
1956
1957 END IF;
1958
1959 -- check for uniqueness of requisition_number
1960 BEGIN
1961
1962 SELECT 1 INTO l_dummy
1963 FROM DUAL
1964 WHERE NOT EXISTS
1965 ( SELECT 1
1966 FROM po_requisition_headers
1967 WHERE Segment1 = l_header_rec.requisition_number)
1968 AND NOT EXISTS
1969 ( SELECT 1
1970 FROM po_history_requisitions phr
1971 WHERE phr.segment1 = l_header_rec.requisition_number);
1972
1973 EXCEPTION
1974 WHEN NO_DATA_FOUND THEN
1975 po_message_s.app_error('PO_ALL_ENTER_UNIQUE');
1976 raise;
1977 WHEN OTHERS THEN
1978 po_message_s.sql_error('check_unique','010',sqlcode);
1979 raise;
1980 END;
1981
1982 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1983 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1984 'csp.plsql.csp_parts_order.process_order',
1985 'Inserting data into PO_REQUISITION_HEADERS');
1986 end if;
1987 -- create approved requisition headers
1988 -- insert into PO_REQUISITION_HEADERS
1989 INSERT INTO PO_REQUISITION_HEADERS(
1990 org_id,
1991 requisition_header_id,
1992 preparer_id,
1993 last_update_date,
1994 last_updated_by,
1995 segment1,
1996 summary_flag,
1997 enabled_flag,
1998 segment2,
1999 segment3,
2000 segment4,
2001 segment5,
2002 start_date_active,
2003 end_date_active,
2004 last_update_login,
2005 creation_date,
2006 created_by,
2007 description,
2008 authorization_status,
2009 note_to_authorizer,
2010 type_lookup_code,
2011 transferred_to_oe_flag,
2012 attribute_category,
2013 attribute1,
2014 attribute2,
2015 attribute3,
2016 attribute4,
2017 attribute5,
2018 on_line_flag,
2019 preliminary_research_flag,
2020 research_complete_flag,
2021 preparer_finished_flag,
2022 preparer_finished_date,
2023 agent_return_flag,
2024 agent_return_note,
2025 cancel_flag,
2026 attribute6,
2027 attribute7,
2028 attribute8,
2029 attribute9,
2030 attribute10,
2031 attribute11,
2032 attribute12,
2033 attribute13,
2034 attribute14,
2035 attribute15,
2036 ussgl_transaction_code,
2037 government_context,
2038 interface_source_code,
2039 interface_source_line_id,
2040 closed_code
2041 ) VALUES (
2042 l_org_id,
2043 l_header_rec.requisition_header_id,
2044 l_employee_id,
2045 l_today,
2046 nvl(l_user_id, 1),
2047 l_header_Rec.requisition_number,
2048 'N', -- summary_flag
2049 'Y', -- Enabled_Flag
2050 null,
2051 null,
2052 null,
2053 null,
2054 null, -- Start_Date_Active
2055 null, -- End_Date_Active
2056 nvl(l_login_id, -1), -- Last_Update_Login
2057 l_today, -- Creation_Date
2058 nvl(l_user_id, 1), -- Created_By
2059 l_header_rec.description, -- Description
2060 'APPROVED', -- Authorization_Status
2061 null, -- note to Authorizor
2062 'INTERNAL', -- Type_Lookup_Code; need to confirm this. po_lookup_codes has different values for document_type
2063
2064 l_transferred_to_oe_flag, -- X_Transferred_To_Oe_Flag
2065 null,
2066 null,
2067 null,
2068 null,
2069 null,
2070 null,
2071 null,
2072 null,
2073 null,
2074 null,
2075 null,
2076 null,
2077 null,
2078 null,
2079 null,
2080 null,
2081 null,
2082 null,
2083 null,
2084 null,
2085 null,
2086 null,
2087 null,
2088 null,
2089 null,
2090 null,
2091 'CSP',
2092 null,
2093 null
2094 );
2095
2096 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2097 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2098 'csp.plsql.csp_parts_order.process_order',
2099 'Inserting data into PO_ACTION_HISTORY');
2100 end if;
2101 -- insert 2 lines of history, one for SUBMIT and one for APPROVE
2102 INSERT into PO_ACTION_HISTORY
2103 (object_id,
2104 object_type_code,
2105 object_sub_type_code,
2106 sequence_num,
2107 last_update_date,
2108 last_updated_by,
2109 creation_date,
2110 created_by,
2111 action_code,
2112 action_date,
2113 employee_id,
2114 note,
2115 object_revision_num,
2116 last_update_login,
2117 request_id,
2118 program_application_id,
2119 program_id,
2120 program_update_date,
2121 approval_path_id,
2122 offline_code)
2123 VALUES
2124 (l_header_rec.requisition_header_id,
2125 'REQUISITION',
2126 'INTERNAL',
2127 0,
2128 sysdate,
2129 fnd_global.user_id,
2130 sysdate,
2131 fnd_global.user_id,
2132 'SUBMIT',
2133 sysdate,
2134 l_employee_id,
2135 null,
2136 null,
2137 fnd_global.login_id,
2138 0,
2139 0,
2140 0,
2141 '',
2142 null,
2143 '' );
2144
2145 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2146 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2147 'csp.plsql.csp_parts_order.process_order',
2148 'Inserting data into PO_ACTION_HISTORY');
2149 end if;
2150
2151 INSERT into PO_ACTION_HISTORY
2152 (object_id,
2153 object_type_code,
2154 object_sub_type_code,
2155 sequence_num,
2156 last_update_date,
2157 last_updated_by,
2158 creation_date,
2159 created_by,
2160 action_code,
2161 action_date,
2162 employee_id,
2163 note,
2164 object_revision_num,
2165 last_update_login,
2166 request_id,
2167 program_application_id,
2168 program_id,
2169 program_update_date,
2170 approval_path_id,
2171 offline_code)
2172 VALUES
2173 (l_header_rec.requisition_header_id,
2174 'REQUISITION',
2175 'INTERNAL',
2176 1,
2177 sysdate,
2178 fnd_global.user_id,
2179 sysdate,
2180 fnd_global.user_id,
2181 'APPROVE',
2182 sysdate,
2183 l_employee_id,
2184 null,
2185 null,
2186 fnd_global.login_id,
2187 0,
2188 0,
2189 0,
2190 '',
2191 null,
2192 '' );
2193
2194 END IF;
2195
2196 IF (p_process_Type IN ('ORDER', 'BOTH')) THEN
2197
2198 BEGIN
2199 SELECT operating_unit
2200 INTO l_source_operating_unit
2201 FROM org_organization_Definitions
2202 WHERE organization_id = l_line_tbl(1).source_organization_id;
2203 EXCEPTION
2204 WHEN NO_DATA_FOUND THEN
2205 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
2206 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'source_org_operating_unit', FALSE);
2207 FND_MSG_PUB.ADD;
2208 RAISE EXCP_USER_DEFINED;
2209 END;
2210
2211 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2212 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2213 'csp.plsql.csp_parts_order.process_order',
2214 'l_org_id = ' || l_org_id
2215 || ', l_source_operating_unit = ' || l_source_operating_unit);
2216 end if;
2217
2218 BEGIN
2219 IF (l_sr_org_id <> l_source_operating_unit) THEN
2220 -- bug # 7644078
2221 -- Here we are going to change the operating unit
2222 -- so hlding old BILL TO from SR will not be a valid one
2223 -- now, OM code will take proper BILL TO
2224
2225 -- bug # 12653874
2226 -- try one more time, if we can get the bill to from SR
2227 l_bill_to_site_use_id := null;
2228 open c_sr_src_bill_to(l_header_rec.requirement_header_id, l_source_operating_unit);
2229 fetch c_sr_src_bill_to into l_bill_to_site_use_id;
2230 close c_sr_src_bill_to;
2231
2232 l_ship_to_contact_id := null;
2233 l_invoice_to_contact_id := null;
2234 END IF;
2235
2236 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2237 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2238 'csp.plsql.csp_parts_order.process_order',
2239 'l_bill_to_site_use_id = ' || l_bill_to_site_use_id);
2240 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2241 'csp.plsql.csp_parts_order.process_order',
2242 'l_ship_to_contact_id = ' || l_ship_to_contact_id);
2243 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2244 'csp.plsql.csp_parts_order.process_order',
2245 'l_invoice_to_contact_id = ' || l_invoice_to_contact_id);
2246 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2247 'csp.plsql.csp_parts_order.process_order',
2248 'original l_header_rec.order_type_id = ' || l_header_rec.order_type_id);
2249 end if;
2250
2251 l_is_valid_order_type := -999;
2252 open validate_order_type(l_source_operating_unit, l_header_rec.order_type_id);
2253 fetch validate_order_type into l_is_valid_order_type;
2254 close validate_order_type;
2255
2256 if l_is_valid_order_type <> 1 then
2257 SELECT ORDER_TYPE_ID
2258 INTO l_header_rec.order_type_id
2259 FROM PO_SYSTEM_PARAMETERS_ALL
2260 WHERE ORG_ID = l_source_operating_unit;
2261 end if;
2262
2263 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2264 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2265 'csp.plsql.csp_parts_order.process_order',
2266 'after validation l_header_rec.order_type_id = ' || l_header_rec.order_type_id);
2267 end if;
2268
2269 -- get all required information for passing to process_orders
2270 SELECT hdr.price_list_id,
2271 hdr.currency_code,
2272 hdr.default_outbound_line_type_id,
2273 line.price_list_id,
2274 line.order_category_code,
2275 nvl(line.scheduling_level_code, hdr.scheduling_level_code)
2276 INTO l_price_list_id,
2277 l_currency_code,
2278 l_order_line_type_id,
2279 l_line_price_list_id,
2280 l_order_line_category_code,
2281 l_scheduling_code
2282 FROM oe_transaction_types_all hdr,
2283 oe_transaction_types_all line
2284 WHERE hdr.transaction_Type_id = l_header_rec.order_type_id
2285 AND line.transaction_type_id = hdr.default_outbound_line_type_id
2286 AND hdr.org_id = l_source_operating_unit
2287 AND line.org_id = l_source_operating_unit;
2288 EXCEPTION
2289 WHEN NO_DATA_FOUND THEN
2290 -- exception handler
2291 null;
2292 WHEN OTHERS THEN
2293 -- exception handler
2294 null;
2295 END;
2296
2297 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2298 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2299 'csp.plsql.csp_parts_order.process_order',
2300 'l_price_list_id = ' || l_price_list_id
2301 || ', l_currency_code = ' || l_currency_code
2302 || ', l_order_line_type_id = ' || l_order_line_type_id
2303 || ', l_line_price_list_id = ' || l_line_price_list_id
2304 || ', l_order_line_category_code = ' || l_order_line_category_code
2305 || ', l_scheduling_code = ' || l_scheduling_code);
2306 end if;
2307
2308 IF (l_currency_code IS NULL) THEN
2309 BEGIN
2310 SELECT glsob.CURRENCY_CODE
2311 INTO l_currency_code
2312 FROM GL_SETS_OF_BOOKS GLSOB,
2313 FINANCIALS_SYSTEM_PARAMS_ALL FSP
2314 WHERE GLSOB.SET_OF_BOOKS_ID=FSP.SET_OF_BOOKS_ID
2315 AND nvl(FSP.org_id,-1) = l_source_operating_unit;
2316
2317 EXCEPTION
2318 WHEN NO_DATA_FOUND THEN
2319 null;
2320 WHEN OTHERS THEN
2321 null;
2322
2323 END;
2324 END IF;
2325
2326 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2327 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2328 'csp.plsql.csp_parts_order.process_order',
2329 'l_currency_code = ' || l_currency_code);
2330 end if;
2331
2332 -- get customer and ship to site
2333 OPEN cust_site_cur;
2334 FETCH cust_site_cur INTO l_customer_id, l_site_use_id, l_cust_acct_id;
2335 CLOSE cust_site_cur;
2336
2337 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2338 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2339 'csp.plsql.csp_parts_order.process_order',
2340 'l_customer_id = ' || l_customer_id
2341 || ', l_site_use_id = ' || l_site_use_id
2342 || ', l_cust_acct_id = ' || l_cust_acct_id);
2343 end if;
2344
2345 -- htank
2346 -- bug # 7759059
2347
2348 IF l_customer_id IS NULL THEN
2349 OPEN get_cust_site_id;
2350 FETCH get_cust_site_id INTO l_cust_site_id, l_customer_id, l_party_site_id, l_tmp_org_id;
2351 CLOSE get_cust_site_id;
2352
2353 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2354 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2355 'csp.plsql.csp_parts_order.process_order',
2356 'l_cust_site_id=' || l_cust_site_id
2357 || ', l_customer_id=' || l_customer_id
2358 || ', l_party_site_id=' || l_party_site_id
2359 || ', l_tmp_org_id=' || l_tmp_org_id);
2360 end if;
2361
2362 IF l_party_site_id IS NULL THEN
2363
2364 -- check if the site is already present
2365 open get_cust_site_id2;
2366 fetch get_cust_site_id2 into l_cust_site_id, l_customer_id, l_party_site_id, l_tmp_org_id, x_cust_acct_site_id;
2367 close get_cust_site_id2;
2368
2369 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2370 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2371 'csp.plsql.csp_parts_order.process_order',
2372 'l_cust_site_id=' || l_cust_site_id
2373 || ', l_customer_id=' || l_customer_id
2374 || ', l_party_site_id=' || l_party_site_id
2375 || ', l_tmp_org_id=' || l_tmp_org_id
2376 || ', x_cust_acct_site_id = ' || x_cust_acct_site_id);
2377 end if;
2378
2379 if l_party_site_id IS NULL THEN
2380
2381 -- get cust_site record in current ou
2382 --dbms_application_info.set_client_info(l_tmp_org_id);
2383 po_moac_utils_pvt.set_org_context(l_tmp_org_id);
2384 hz_cust_account_site_v2pub.get_cust_acct_site_rec (
2385 p_init_msg_list => FND_API.G_TRUE,
2386 p_cust_acct_site_id => l_cust_site_id,
2387 x_cust_acct_site_rec => v_cust_acct_site_rec,
2388 x_return_status => x_return_status,
2389 x_msg_count => x_msg_count,
2390 x_msg_data => x_msg_data);
2391 --dbms_application_info.set_client_info(l_org_id);
2392 po_moac_utils_pvt.set_org_context(l_tmp_org_id);
2393
2394 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2395 l_msg := x_msg_data;
2396 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2397 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2398 FND_MSG_PUB.ADD;
2399 fnd_msg_pub.count_and_get
2400 ( p_count => x_msg_count
2401 , p_data => x_msg_data);
2402 x_return_status := FND_API.G_RET_STS_ERROR;
2403 --dbms_application_info.set_client_info(l_org_id);
2404 RAISE FND_API.G_EXC_ERROR;
2405 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2406
2407 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2408 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2409 'csp.plsql.csp_parts_order.process_order',
2410 'populated v_cust_acct_site_rec');
2411 end if;
2412
2413 v_cust_acct_site_rec.cust_acct_site_id := NULL;
2414 v_cust_acct_site_rec.tp_header_id := NULL;
2415 v_cust_acct_site_rec.language := NULL;
2416 v_cust_acct_site_rec.created_by_module := 'CSPSHIPAD';
2417 v_cust_acct_site_rec.org_id := l_source_operating_unit;
2418
2419 -- now create same site in source ou
2420 --dbms_application_info.set_client_info(l_source_operating_unit);
2421 po_moac_utils_pvt.set_org_context(l_source_operating_unit);
2422 hz_cust_account_site_v2pub.create_cust_acct_site (
2423 p_init_msg_list => FND_API.G_TRUE,
2424 p_cust_acct_site_rec => v_cust_acct_site_rec,
2425 x_cust_acct_site_id => x_cust_acct_site_id,
2426 x_return_status => x_return_status,
2427 x_msg_count => x_msg_count,
2428 x_msg_data => x_msg_data);
2429 --dbms_application_info.set_client_info(l_org_id);
2430 po_moac_utils_pvt.set_org_context(l_org_id);
2431
2432 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2433 l_msg := x_msg_data;
2434 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2435 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2436 FND_MSG_PUB.ADD;
2437 fnd_msg_pub.count_and_get
2438 ( p_count => x_msg_count
2439 , p_data => x_msg_data);
2440 x_return_status := FND_API.G_RET_STS_ERROR;
2441 RAISE FND_API.G_EXC_ERROR;
2442 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2443
2444 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2445 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2446 'csp.plsql.csp_parts_order.process_order',
2447 'x_cust_acct_site_id=' || x_cust_acct_site_id);
2448 end if;
2449
2450 end if; -- if l_party_site_id IS NULL THEN # 2
2451
2452 -- now fetch all site uses records and copy them to source ou
2453 open get_cust_acct_site_uses;
2454 LOOP
2455 fetch get_cust_acct_site_uses into l_cust_acct_site_use_id;
2456 EXIT WHEN get_cust_acct_site_uses%NOTFOUND;
2457
2458 --dbms_application_info.set_client_info(l_tmp_org_id);
2459 po_moac_utils_pvt.set_org_context(l_tmp_org_id);
2460 hz_cust_account_site_v2pub.get_cust_site_use_rec (
2461 p_init_msg_list => FND_API.G_TRUE,
2462 p_site_use_id => l_cust_acct_site_use_id,
2463 x_cust_site_use_rec => v_cust_site_use_rec,
2464 x_customer_profile_rec => v_customer_profile_rec,
2465 x_return_status => x_return_status,
2466 x_msg_count => x_msg_count,
2467 x_msg_data => x_msg_data);
2468 --dbms_application_info.set_client_info(l_org_id);
2469 po_moac_utils_pvt.set_org_context(l_org_id);
2470
2471 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2472 l_msg := x_msg_data;
2473 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2474 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2475 FND_MSG_PUB.ADD;
2476 fnd_msg_pub.count_and_get
2477 ( p_count => x_msg_count
2478 , p_data => x_msg_data);
2479 x_return_status := FND_API.G_RET_STS_ERROR;
2480 --dbms_application_info.set_client_info(l_org_id);
2481 RAISE FND_API.G_EXC_ERROR;
2482 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2483
2484 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2485 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2486 'csp.plsql.csp_parts_order.process_order',
2487 'populated v_cust_site_use_rec');
2488 end if;
2489
2490 v_cust_site_use_rec.site_use_id := NULL;
2491 v_cust_site_use_rec.primary_flag := 'N';
2492 v_cust_site_use_rec.created_by_module := 'CSPSHIPAD';
2493 --v_cust_site_use_rec.location := 'CSP_LOCATION';
2494 v_cust_site_use_rec.org_id := l_source_operating_unit;
2495 v_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
2496 v_cust_site_use_rec.tax_code := NULL;
2497
2498 if v_cust_site_use_rec.site_use_code = 'SHIP_TO' then
2499 if v_cust_site_use_rec.bill_to_site_use_id is not null then
2500 v_ship_bill_site := v_cust_site_use_rec.bill_to_site_use_id;
2501 open get_bill_site_id;
2502 fetch get_bill_site_id into v_bill_site_id;
2503 close get_bill_site_id;
2504
2505 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2506 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2507 'csp.plsql.csp_parts_order.process_order',
2508 'v_bill_site_id = ' || v_bill_site_id);
2509 end if;
2510
2511 if v_bill_site_id <> l_cust_site_id then
2512
2513 open check_bill_to_location;
2514 fetch check_bill_to_location into l_existing_bill_to;
2515 close check_bill_to_location;
2516
2517 if l_existing_bill_to is null then
2518
2519 -- do lots of stuff here
2520 po_moac_utils_pvt.set_org_context(l_tmp_org_id);
2521 hz_cust_account_site_v2pub.get_cust_acct_site_rec (
2522 p_init_msg_list => FND_API.G_TRUE,
2523 p_cust_acct_site_id => v_bill_site_id,
2524 x_cust_acct_site_rec => v_bill_acct_site_rec,
2525 x_return_status => x_return_status,
2526 x_msg_count => x_msg_count,
2527 x_msg_data => x_msg_data);
2528 po_moac_utils_pvt.set_org_context(l_tmp_org_id);
2529
2530 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2531 l_msg := x_msg_data;
2532 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2533 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2534 FND_MSG_PUB.ADD;
2535 fnd_msg_pub.count_and_get
2536 ( p_count => x_msg_count
2537 , p_data => x_msg_data);
2538 x_return_status := FND_API.G_RET_STS_ERROR;
2539 RAISE FND_API.G_EXC_ERROR;
2540 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2541
2542 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2543 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2544 'csp.plsql.csp_parts_order.process_order',
2545 'populated v_bill_acct_site_rec');
2546 end if;
2547
2548 v_bill_acct_site_rec.cust_acct_site_id := NULL;
2549 v_bill_acct_site_rec.tp_header_id := NULL;
2550 v_bill_acct_site_rec.language := NULL;
2551 v_bill_acct_site_rec.created_by_module := 'CSPSHIPAD';
2552 v_bill_acct_site_rec.org_id := l_source_operating_unit;
2553
2554 -- now create same site in source ou
2555 po_moac_utils_pvt.set_org_context(l_source_operating_unit);
2556 hz_cust_account_site_v2pub.create_cust_acct_site (
2557 p_init_msg_list => FND_API.G_TRUE,
2558 p_cust_acct_site_rec => v_bill_acct_site_rec,
2559 x_cust_acct_site_id => x_bill_acct_site_id,
2560 x_return_status => x_return_status,
2561 x_msg_count => x_msg_count,
2562 x_msg_data => x_msg_data);
2563 po_moac_utils_pvt.set_org_context(l_org_id);
2564
2565 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2566 l_msg := x_msg_data;
2567 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2568 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2569 FND_MSG_PUB.ADD;
2570 fnd_msg_pub.count_and_get
2571 ( p_count => x_msg_count
2572 , p_data => x_msg_data);
2573 x_return_status := FND_API.G_RET_STS_ERROR;
2574 RAISE FND_API.G_EXC_ERROR;
2575 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2576
2577 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2578 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2579 'csp.plsql.csp_parts_order.process_order',
2580 'x_bill_acct_site_id=' || x_bill_acct_site_id);
2581 end if;
2582
2583 /*
2584 open get_bill_acct_site_uses;
2585 fetch get_bill_acct_site_uses into l_bill_acct_site_use_id;
2586 close get_bill_acct_site_uses;
2587 */
2588 l_bill_acct_site_use_id := v_ship_bill_site;
2589
2590 po_moac_utils_pvt.set_org_context(l_tmp_org_id);
2591 hz_cust_account_site_v2pub.get_cust_site_use_rec (
2592 p_init_msg_list => FND_API.G_TRUE,
2593 p_site_use_id => l_bill_acct_site_use_id,
2594 x_cust_site_use_rec => v_bill_site_use_rec,
2595 x_customer_profile_rec => v_bill_cust_profile_rec,
2596 x_return_status => x_return_status,
2597 x_msg_count => x_msg_count,
2598 x_msg_data => x_msg_data);
2599 po_moac_utils_pvt.set_org_context(l_org_id);
2600
2601 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2602 l_msg := x_msg_data;
2603 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2604 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2605 FND_MSG_PUB.ADD;
2606 fnd_msg_pub.count_and_get
2607 ( p_count => x_msg_count
2608 , p_data => x_msg_data);
2609 x_return_status := FND_API.G_RET_STS_ERROR;
2610 RAISE FND_API.G_EXC_ERROR;
2611 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2612
2613 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2614 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2615 'csp.plsql.csp_parts_order.process_order',
2616 'populated v_bill_site_use_rec');
2617 end if;
2618
2619 v_bill_site_use_rec.site_use_id := NULL;
2620 v_bill_site_use_rec.primary_flag := 'N';
2621 v_bill_site_use_rec.created_by_module := 'CSPSHIPAD';
2622 v_bill_site_use_rec.org_id := l_source_operating_unit;
2623 v_bill_site_use_rec.cust_acct_site_id := x_bill_acct_site_id;
2624 v_bill_site_use_rec.tax_code := NULL;
2625
2626 po_moac_utils_pvt.set_org_context(l_source_operating_unit);
2627 hz_cust_account_site_v2pub.create_cust_site_use (
2628 p_init_msg_list => FND_API.G_TRUE,
2629 p_cust_site_use_rec => v_bill_site_use_rec,
2630 p_customer_profile_rec => v_bill_cust_profile_rec,
2631 p_create_profile => FND_API.G_FALSE,
2632 p_create_profile_amt => FND_API.G_FALSE,
2633 x_site_use_id => x_site_use_id,
2634 x_return_status => x_return_status,
2635 x_msg_count => x_msg_count,
2636 x_msg_data => x_msg_data);
2637 po_moac_utils_pvt.set_org_context(l_org_id);
2638
2639 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2640 l_msg := x_msg_data;
2641 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2642 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2643 FND_MSG_PUB.ADD;
2644 fnd_msg_pub.count_and_get
2645 ( p_count => x_msg_count
2646 , p_data => x_msg_data);
2647 x_return_status := FND_API.G_RET_STS_ERROR;
2648 po_moac_utils_pvt.set_org_context(l_org_id);
2649 RAISE FND_API.G_EXC_ERROR;
2650 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2651
2652 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2653 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2654 'csp.plsql.csp_parts_order.process_order',
2655 'x_site_use_id=' || x_site_use_id);
2656 end if;
2657
2658 v_cust_site_use_rec.bill_to_site_use_id := x_site_use_id;
2659
2660 else
2661 v_cust_site_use_rec.bill_to_site_use_id := l_existing_bill_to;
2662 end if;
2663
2664 else
2665 v_cust_site_use_rec.bill_to_site_use_id := null;
2666 end if;
2667 end if;
2668 end if;
2669
2670 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2671 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2672 'csp.plsql.csp_parts_order.process_order',
2673 'v_cust_site_use_rec.site_use_code=' || v_cust_site_use_rec.site_use_code
2674 || ', v_cust_site_use_rec.bill_to_site_use_id=' || v_cust_site_use_rec.bill_to_site_use_id);
2675 end if;
2676
2677 --dbms_application_info.set_client_info(l_source_operating_unit);
2678 po_moac_utils_pvt.set_org_context(l_source_operating_unit);
2679 hz_cust_account_site_v2pub.create_cust_site_use (
2680 p_init_msg_list => FND_API.G_TRUE,
2681 p_cust_site_use_rec => v_cust_site_use_rec,
2682 p_customer_profile_rec => v_customer_profile_rec,
2683 p_create_profile => FND_API.G_FALSE,
2684 p_create_profile_amt => FND_API.G_FALSE,
2685 x_site_use_id => x_site_use_id,
2686 x_return_status => x_return_status,
2687 x_msg_count => x_msg_count,
2688 x_msg_data => x_msg_data);
2689 --dbms_application_info.set_client_info(l_org_id);
2690 po_moac_utils_pvt.set_org_context(l_org_id);
2691
2692 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2693 l_msg := x_msg_data;
2694 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2695 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2696 FND_MSG_PUB.ADD;
2697 fnd_msg_pub.count_and_get
2698 ( p_count => x_msg_count
2699 , p_data => x_msg_data);
2700 x_return_status := FND_API.G_RET_STS_ERROR;
2701 --dbms_application_info.set_client_info(l_org_id);
2702 po_moac_utils_pvt.set_org_context(l_org_id);
2703 RAISE FND_API.G_EXC_ERROR;
2704 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2705
2706 if v_cust_site_use_rec.site_use_code = 'BILL_TO' then
2707 temp_bill_to_site_id := x_site_use_id;
2708 end if;
2709
2710 if v_cust_site_use_rec.site_use_code = 'SHIP_TO' then
2711 temp_ship_to_use_id := x_site_use_id;
2712 end if;
2713
2714 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2715 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2716 'csp.plsql.csp_parts_order.process_order',
2717 'x_site_use_id=' || x_site_use_id);
2718 end if;
2719
2720 END LOOP;
2721 close get_cust_acct_site_uses;
2722
2723 l_party_site_id := v_cust_acct_site_rec.party_site_id;
2724
2725 END IF; -- IF l_party_site_id IS NULL THEN
2726
2727 --dbms_application_info.set_client_info(l_org_id);
2728
2729 open get_new_cust_ids;
2730 fetch get_new_cust_ids into x_cust_acct_site_id, temp_ship_to_use_id;
2731 close get_new_cust_ids;
2732
2733 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2734 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2735 'csp.plsql.csp_parts_order.process_order',
2736 'x_cust_acct_site_id=' || x_cust_acct_site_id
2737 || ', temp_ship_to_use_id=' || temp_ship_to_use_id);
2738 end if;
2739
2740 -- update inventory_location link for this new site_use
2741 --dbms_application_info.set_client_info(l_source_operating_unit);
2742 po_moac_utils_pvt.set_org_context(l_source_operating_unit);
2743 arp_clas_pkg.insert_po_loc_associations(
2744 p_inventory_location_id => l_header_rec.ship_to_location_id,
2745 p_inventory_organization_id => l_source_operating_unit,
2746 p_customer_id => l_customer_id,
2747 p_address_id => x_cust_acct_site_id,
2748 p_site_use_id => temp_ship_to_use_id,
2749 x_return_status => x_return_status,
2750 x_msg_count => x_msg_count,
2751 x_msg_data => x_msg_data);
2752 --dbms_application_info.set_client_info(l_org_id);
2753 po_moac_utils_pvt.set_org_context(l_org_id);
2754
2755 OPEN cust_site_cur;
2756 FETCH cust_site_cur INTO l_customer_id, l_site_use_id, l_cust_acct_id;
2757 CLOSE cust_site_cur;
2758
2759 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2760 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2761 'csp.plsql.csp_parts_order.process_order',
2762 'l_customer_id=' || l_customer_id
2763 || ', l_site_use_id=' || l_site_use_id
2764 || ', l_cust_acct_id=' || l_cust_acct_id);
2765 end if;
2766
2767 END IF; --IF l_customer_id IS NULL THEN
2768 -- end of bug # 7759059
2769
2770 -- bug # 8850605
2771 -- check for primary bill to
2772 -- if not available, copy it from destination OU assuming there is
2773 -- one Primary Bill To available for the customer
2774 open get_primary_bill_to(l_source_operating_unit);
2775 fetch get_primary_bill_to into v_primary_bill_site_use_id, v_primary_bill_site_id;
2776 if get_primary_bill_to%notfound then
2777 close get_primary_bill_to;
2778
2779 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2780 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2781 'csp.plsql.csp_parts_order.process_order',
2782 'Primary Bill To is not available for source OU... Creating...');
2783 end if;
2784
2785 if l_tmp_org_id is null then
2786 l_tmp_org_id := l_org_id;
2787 end if;
2788
2789 open get_primary_bill_to(l_tmp_org_id);
2790 fetch get_primary_bill_to into v_primary_bill_site_use_id, v_primary_bill_site_id;
2791 close get_primary_bill_to;
2792
2793 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2794 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2795 'csp.plsql.csp_parts_order.process_order',
2796 'v_primary_bill_site_use_id = ' || v_primary_bill_site_use_id
2797 || ', v_primary_bill_site_id = ' || v_primary_bill_site_id);
2798 end if;
2799
2800 -- do lots of stuff here
2801 po_moac_utils_pvt.set_org_context(l_tmp_org_id);
2802 hz_cust_account_site_v2pub.get_cust_acct_site_rec (
2803 p_init_msg_list => FND_API.G_TRUE,
2804 p_cust_acct_site_id => v_primary_bill_site_id,
2805 x_cust_acct_site_rec => v_pri_bill_acct_site_rec,
2806 x_return_status => x_return_status,
2807 x_msg_count => x_msg_count,
2808 x_msg_data => x_msg_data);
2809 po_moac_utils_pvt.set_org_context(l_org_id);
2810
2811 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2812 l_msg := x_msg_data;
2813 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2814 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2815 FND_MSG_PUB.ADD;
2816 fnd_msg_pub.count_and_get
2817 ( p_count => x_msg_count
2818 , p_data => x_msg_data);
2819 x_return_status := FND_API.G_RET_STS_ERROR;
2820 RAISE FND_API.G_EXC_ERROR;
2821 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2822
2823 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2824 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2825 'csp.plsql.csp_parts_order.process_order',
2826 'populated v_pri_bill_acct_site_rec');
2827 end if;
2828
2829 v_pri_bill_acct_site_rec.cust_acct_site_id := NULL;
2830 v_pri_bill_acct_site_rec.tp_header_id := NULL;
2831 v_pri_bill_acct_site_rec.language := NULL;
2832 v_pri_bill_acct_site_rec.created_by_module := 'CSPSHIPAD';
2833 v_pri_bill_acct_site_rec.org_id := l_source_operating_unit;
2834
2835 -- now create same site in source ou
2836 po_moac_utils_pvt.set_org_context(l_source_operating_unit);
2837 hz_cust_account_site_v2pub.create_cust_acct_site (
2838 p_init_msg_list => FND_API.G_TRUE,
2839 p_cust_acct_site_rec => v_pri_bill_acct_site_rec,
2840 x_cust_acct_site_id => x_pri_bill_acct_site_id,
2841 x_return_status => x_return_status,
2842 x_msg_count => x_msg_count,
2843 x_msg_data => x_msg_data);
2844 po_moac_utils_pvt.set_org_context(l_org_id);
2845
2846 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2847 l_msg := x_msg_data;
2848 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2849 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2850 FND_MSG_PUB.ADD;
2851 fnd_msg_pub.count_and_get
2852 ( p_count => x_msg_count
2853 , p_data => x_msg_data);
2854 x_return_status := FND_API.G_RET_STS_ERROR;
2855 RAISE FND_API.G_EXC_ERROR;
2856 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2857
2858 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2859 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2860 'csp.plsql.csp_parts_order.process_order',
2861 'x_bill_acct_site_id=' || x_pri_bill_acct_site_id);
2862 end if;
2863
2864
2865 po_moac_utils_pvt.set_org_context(l_tmp_org_id);
2866 hz_cust_account_site_v2pub.get_cust_site_use_rec (
2867 p_init_msg_list => FND_API.G_TRUE,
2868 p_site_use_id => v_primary_bill_site_use_id,
2869 x_cust_site_use_rec => v_pri_bill_site_use_rec,
2870 x_customer_profile_rec => v_pri_bill_cust_prf_rec,
2871 x_return_status => x_return_status,
2872 x_msg_count => x_msg_count,
2873 x_msg_data => x_msg_data);
2874 po_moac_utils_pvt.set_org_context(l_org_id);
2875
2876 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2877 l_msg := x_msg_data;
2878 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2879 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2880 FND_MSG_PUB.ADD;
2881 fnd_msg_pub.count_and_get
2882 ( p_count => x_msg_count
2883 , p_data => x_msg_data);
2884 x_return_status := FND_API.G_RET_STS_ERROR;
2885 RAISE FND_API.G_EXC_ERROR;
2886 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2887
2888 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2889 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2890 'csp.plsql.csp_parts_order.process_order',
2891 'populated v_pri_bill_site_use_rec');
2892 end if;
2893
2894 v_pri_bill_site_use_rec.site_use_id := NULL;
2895 v_pri_bill_site_use_rec.primary_flag := 'N';
2896 v_pri_bill_site_use_rec.created_by_module := 'CSPSHIPAD';
2897 v_pri_bill_site_use_rec.org_id := l_source_operating_unit;
2898 v_pri_bill_site_use_rec.cust_acct_site_id := x_pri_bill_acct_site_id;
2899 v_pri_bill_site_use_rec.tax_code := NULL;
2900
2901 po_moac_utils_pvt.set_org_context(l_source_operating_unit);
2902 hz_cust_account_site_v2pub.create_cust_site_use (
2903 p_init_msg_list => FND_API.G_TRUE,
2904 p_cust_site_use_rec => v_pri_bill_site_use_rec,
2905 p_customer_profile_rec => v_pri_bill_cust_prf_rec,
2906 p_create_profile => FND_API.G_FALSE,
2907 p_create_profile_amt => FND_API.G_FALSE,
2908 x_site_use_id => x_site_use_id,
2909 x_return_status => x_return_status,
2910 x_msg_count => x_msg_count,
2911 x_msg_data => x_msg_data);
2912 po_moac_utils_pvt.set_org_context(l_org_id);
2913
2914 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2915 l_msg := x_msg_data;
2916 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2917 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2918 FND_MSG_PUB.ADD;
2919 fnd_msg_pub.count_and_get
2920 ( p_count => x_msg_count
2921 , p_data => x_msg_data);
2922 x_return_status := FND_API.G_RET_STS_ERROR;
2923 po_moac_utils_pvt.set_org_context(l_org_id);
2924 RAISE FND_API.G_EXC_ERROR;
2925 END IF; -- IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2926
2927 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2928 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2929 'csp.plsql.csp_parts_order.process_order',
2930 'x_site_use_id=' || x_site_use_id);
2931 end if;
2932 else
2933 close get_primary_bill_to;
2934 end if;
2935
2936
2937 -- SETTING UP THE ORDER PROCESS HEADER RECORD
2938 -- order_header_id
2939 IF l_header_rec.order_header_id IS NULL THEN
2940 select oe_order_headers_s.nextval
2941 into l_header_rec.order_header_id
2942 from dual;
2943 END IF;
2944
2945 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2946 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2947 'csp.plsql.csp_parts_order.process_order',
2948 'l_header_rec.order_header_id = ' || l_header_rec.order_header_id);
2949 end if;
2950
2951 -- Required attributes (e.g. Order Type and Customer)
2952 l_oe_header_rec.header_id := l_header_rec.order_header_id;
2953 --l_order_number := OE_Default_header.Get_Order_Number;
2954 l_oe_header_rec.order_number := null; --l_header_rec.order_header_id;
2955 l_oe_header_rec.version_number := 1;
2956 l_oe_header_rec.order_type_id := l_header_rec.order_type_id;
2957 l_oe_header_rec.org_id := l_source_operating_unit;
2958 l_oe_header_rec.sold_to_org_id := l_cust_acct_id;
2959 l_oe_header_rec.ship_to_org_id := l_site_use_id;
2960
2961 -- bug # 6471559
2962 if l_bill_to_site_use_id is not NULL then
2963 l_oe_header_rec.invoice_to_org_id := l_bill_to_site_use_id;
2964 end if;
2965
2966
2967 if l_ship_to_contact_id is not null then
2968
2969 open get_valid_contact_id(l_ship_to_contact_id, l_oe_header_rec.ship_to_org_id);
2970 fetch get_valid_contact_id into l_ship_to_contact_id_final;
2971 close get_valid_contact_id;
2972
2973 if l_ship_to_contact_id_final is not null then
2974 l_oe_header_rec.ship_to_contact_id := l_ship_to_contact_id_final;
2975 end if;
2976 end if;
2977
2978 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2979 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2980 'csp.plsql.csp_parts_order.process_order',
2981 'l_oe_header_rec.ship_to_contact_id = ' || l_oe_header_rec.ship_to_contact_id);
2982 end if;
2983
2984 if l_invoice_to_contact_id is not null and l_oe_header_rec.invoice_to_org_id is not null then
2985
2986 open get_valid_contact_id(l_invoice_to_contact_id, l_oe_header_rec.invoice_to_org_id);
2987 fetch get_valid_contact_id into l_invoice_to_contact_id_final;
2988 close get_valid_contact_id;
2989
2990 if l_invoice_to_contact_id_final is not null then
2991 l_oe_header_rec.INVOICE_TO_CONTACT_ID := l_invoice_to_contact_id_final;
2992 end if;
2993 end if;
2994
2995 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2996 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2997 'csp.plsql.csp_parts_order.process_order',
2998 'l_oe_header_rec.INVOICE_TO_CONTACT_ID = ' || l_oe_header_rec.INVOICE_TO_CONTACT_ID);
2999 end if;
3000
3001
3002 l_oe_header_rec.ORIG_SYS_DOCUMENT_REF := l_header_rec.requisition_number;
3003 l_oe_header_rec.SOURCE_DOCUMENT_ID := l_header_rec.requisition_header_id;
3004 l_oe_header_rec.transactional_curr_code := l_currency_code;
3005 l_oe_header_rec.open_flag := 'Y';
3006
3007 /*
3008 if nvl(p_book_order, 'Y') = 'Y' then
3009 l_oe_header_rec.booked_flag := l_line_tbl(1).booked_flag; --N;
3010 else
3011 l_oe_header_rec.booked_flag := 'N';
3012 end if;
3013 */
3014
3015 l_oe_header_rec.order_source_id := l_order_source_id;
3016 l_oe_header_rec.source_document_type_id := l_order_source_id;
3017
3018 -- bug 8220079
3019 l_oe_header_rec.shipping_method_code := nvl(l_header_rec.shipping_method_code, FND_API.G_MISS_CHAR);
3020
3021
3022 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3023 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3024 'csp.plsql.csp_parts_order.process_order',
3025 'l_oe_header_rec.shipping_method_code = ' || l_oe_header_rec.shipping_method_code);
3026 end if;
3027
3028 -- Other attributes
3029 l_oe_header_rec.price_list_id := l_price_list_id;
3030 END IF;
3031
3032 -- Indicates to process order that a new header is being created
3033 l_oe_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
3034
3035 ELSIF (l_header_rec.operation = G_OPR_UPDATE) THEN
3036 IF (l_header_rec.order_header_id is null) THEN
3037 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
3038 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_header_rec.order_header_id', FALSE);
3039
3040 FND_MSG_PUB.ADD;
3041 RAISE EXCP_USER_DEFINED;
3042 END IF;
3043
3044 oe_header_util.Query_Row(
3045 p_header_id => l_header_rec.order_header_id,
3046 x_header_rec => l_oe_header_rec);
3047
3048 -- Indicates to process order that header is to be updated
3049 l_oe_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
3050 l_oe_header_rec.booked_flag := l_line_tbl(1).booked_flag;
3051
3052 END IF;
3053
3054 /*IF (p_process_type in ('ORDER', 'BOTH')) THEN
3055 -- Required attributes (e.g. Order Type and Customer)
3056 l_oe_header_rec.header_id := l_header_rec.order_header_id;
3057 --l_order_number := OE_Default_header.Get_Order_Number;
3058 l_oe_header_rec.order_number := l_header_rec.order_header_id;
3059 l_oe_header_rec.version_number := 1;
3060 l_oe_header_rec.order_type_id := l_header_rec.order_type_id;
3061 l_oe_header_rec.org_id := l_org_id;
3062 l_oe_header_rec.sold_to_org_id := l_cust_acct_id;
3063 l_oe_header_rec.ship_to_org_id := l_site_use_id;
3064 l_oe_header_rec.ORIG_SYS_DOCUMENT_REF := l_header_rec.requisition_number;
3065 l_oe_header_rec.SOURCE_DOCUMENT_ID := l_header_rec.requisition_header_id;
3066 l_oe_header_rec.transactional_curr_code := l_currency_code;
3067 l_oe_header_rec.open_flag := 'Y';
3068 l_oe_header_rec.booked_flag := l_line_tbl(1).booked_flag; --N;
3069 l_oe_header_rec.order_source_id := l_order_source_id;
3070 l_oe_header_rec.source_document_type_id := l_order_source_id;
3071
3072 -- Other attributes
3073 l_oe_header_rec.price_list_id := l_price_list_id;
3074 END IF;
3075 */
3076
3077 -- get all the values required to insert into po_requisition_lines table
3078
3079 -- line_type_id for Requisition
3080 OPEN line_type_cur;
3081 FETCH line_type_cur INTO l_line_type_rec;
3082 CLOSE line_type_cur;
3083
3084 FOR I IN 1..l_line_tbl.COUNT LOOP
3085
3086 IF (l_header_rec.operation = G_OPR_CREATE) THEN
3087
3088 IF (p_process_type in ('REQUISITION', 'BOTH')) THEN
3089 -- get requisition_line_id
3090 IF (l_line_tbl(i).Requisition_Line_Id is NULL) THEN
3091 OPEN req_line_id_cur;
3092 FETCH req_line_id_cur INTO l_line_tbl(i).requisition_line_id;
3093 CLOSE req_line_id_cur;
3094 END IF;
3095
3096 OPEN item_desc_cur(l_line_tbl(i).inventory_item_id, l_header_rec.dest_organization_id);
3097 FETCH item_desc_cur INTO l_line_tbl(i).item_description;
3098 IF item_Desc_cur%NOTFOUND THEN
3099 CLOSE item_desc_cur;
3100 FND_MESSAGE.SET_NAME ('CSP', 'CSP_INVALID_ITEM_ORGN');
3101 FND_PROFILE.GET('CS_INV_VALIDATION_ORG', l_line_tbl(i).source_organization_id); -- taking dummy variable as we are going to throw an error only
3102
3103 if l_line_tbl(i).source_organization_id is not null then
3104 SELECT concatenated_segments
3105 INTO l_line_tbl(i).item_description -- taking dummy variable as we are going to throw an error only
3106 FROM mtl_system_items_kfv
3107 WHERE organization_id = l_line_tbl(i).source_organization_id
3108 and inventory_item_id = l_line_tbl(i).inventory_item_id;
3109
3110 select organization_code
3111 into l_line_tbl(i).ATTRIBUTE1 -- taking dummy variable as we are going to throw an error only
3112 from mtl_parameters
3113 where organization_id = l_header_rec.dest_organization_id;
3114
3115 FND_MESSAGE.SET_TOKEN ('ITM', l_line_tbl(i).item_description, FALSE);
3116 FND_MESSAGE.SET_TOKEN ('ORG', l_line_tbl(i).ATTRIBUTE1, FALSE);
3117 end if;
3118
3119 FND_MSG_PUB.ADD;
3120 RAISE FND_API.G_EXC_ERROR;
3121 END IF;
3122 CLOSE item_desc_cur;
3123
3124
3125 --IF (l_line_tbl(i).item_description IS NULL) THEN
3126 OPEN item_desc_cur(l_line_tbl(i).inventory_item_id, l_line_tbl(i).source_organization_id);
3127 FETCH item_desc_cur INTO l_line_tbl(i).item_description;
3128 IF item_Desc_cur%NOTFOUND THEN
3129 CLOSE item_desc_cur;
3130 FND_MESSAGE.SET_NAME ('ONT', 'OE_INVALID_ITEM_WHSE');
3131 FND_MSG_PUB.ADD;
3132 RAISE FND_API.G_EXC_ERROR;
3133 END IF;
3134 CLOSE item_desc_cur;
3135 --END IF;
3136
3137 -- Get Category ID of the Item
3138 OPEN item_category_cur (l_line_tbl(i).inventory_item_id, l_header_rec.dest_organization_id);
3139 FETCH item_category_cur INTO l_category_id;
3140 CLOSE item_category_cur;
3141
3142 -- Derive Unit_of_Measure from Uom_Code
3143 OPEN unit_of_measure_cur(l_line_tbl(i).unit_of_measure);
3144 FETCH unit_of_measure_cur INTO l_unit_meas_lookup_code;
3145 CLOSE unit_of_measure_cur;
3146
3147 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3148 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3149 'csp.plsql.csp_parts_order.process_order',
3150 'Getting Unit Price...');
3151 end if;
3152
3153 /* Get Unit Price and Currency Code*/
3154 get_unit_price_prc (l_line_tbl(i).inventory_item_id
3155 ,l_line_tbl(i).source_organization_id
3156 ,l_header_rec.dest_organization_id
3157 ,l_set_of_books_id
3158 ,l_chart_of_accounts_id
3159 ,l_currency_code
3160 ,l_unit_price
3161 ,l_item_cost );
3162
3163 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3164 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3165 'csp.plsql.csp_parts_order.process_order',
3166 'Inserting data into PO_REQUISITION_LINES...');
3167 end if;
3168
3169 -- insert into po_requisition_lines table
3170 INSERT INTO PO_REQUISITION_LINES(
3171 org_id,
3172 requisition_line_id,
3173 requisition_header_id,
3174 line_num,
3175 line_type_id,
3176 category_id,
3177 item_description,
3178 unit_meas_lookup_code,
3179 unit_price,
3180 quantity,
3181 deliver_to_location_id,
3182 to_person_id,
3183 last_update_date,
3184 last_updated_by,
3185 source_type_code,
3186 last_update_login,
3187 creation_date,
3188 created_by,
3189 item_id,
3190 item_revision,
3191 encumbered_flag,
3192 rfq_required_flag,
3193 need_by_date,
3194 source_organization_id,
3195 source_subinventory,
3196 destination_type_code,
3197 destination_organization_id,
3198 destination_subinventory,
3199 cancel_flag,
3200 order_type_lookup_code,
3201 purchase_basis,
3202 matching_basis,
3203 transferred_to_oe_flag
3204 ) VALUES (
3205 l_org_id,
3206 l_line_tbl(i).requisition_line_id,
3207 l_header_rec.requisition_header_id,
3208 l_line_tbl(i).line_num,
3209 l_line_type_rec.line_type_id, -- Line_Type_Id
3210 nvl(l_Category_id, 1), -- Category_id
3211 l_line_tbl(i).item_description, -- Item_Description
3212 nvl(l_unit_meas_lookup_code, l_line_tbl(i).unit_of_measure), -- Unit_Meas_Lookup_Code
3213 l_unit_price,
3214 l_line_tbl(i).ordered_quantity,
3215 l_header_rec.ship_to_location_id, -- Deliver_To_Location_Id
3216 l_employee_id, -- To_Person_Id
3217 l_today, -- Last_Update_Date
3218 nvl(l_user_id, -1), -- Last_Updated_By
3219 'INVENTORY', -- Source_Type_Code
3220 nvl(l_login_id, -1),
3221 l_today, -- Creation_Date
3222 nvl(l_user_id, -1),
3223 l_line_tbl(i).inventory_item_id,
3224 l_line_tbl(i).revision,
3225 'N', -- Encumbered_flag
3226 'N', -- X_Rfq_Required_Flag
3227 l_header_rec.need_by_date,
3228 l_line_tbl(i).source_organization_id,
3229 l_line_tbl(i).source_subinventory,
3230 'INVENTORY', -- Destination_Type_Code
3231 l_header_rec.dest_organization_id,
3232 nvl(l_line_tbl(i).dest_subinventory, l_header_rec.dest_subinventory), /* Bug 7242187*/
3233 'N',
3234 l_line_type_rec.order_type_lookup_code,
3235 l_line_type_rec.purchase_basis,
3236 l_line_Type_rec.matching_basis, --Cancel_Flag
3237 l_transferred_to_oe_flag
3238 );
3239
3240 -- create req distributions
3241 -- It is assumed that only 1 dIstribution line will be there for each
3242 -- INTERNAL Requisition. If Multiple Distributions Lines are to created
3243 -- this procedure should be modified
3244
3245 -- Get Distribution ID from the Distribution Sequence
3246 OPEN dist_line_id_cur;
3247 FETCH dist_line_id_cur INTO l_dist_rec.distribution_id;
3248 CLOSE dist_line_id_cur;
3249
3250 -- Assign Requisition Line ID if NULL
3251 l_dist_rec.requisition_line_id := l_line_tbl(i).requisition_line_id;
3252 l_dist_rec.org_id := l_org_id;
3253
3254 -- Assign Requisition Quantity if NULL
3255 l_dist_rec.req_line_quantity := l_line_tbl(i).ordered_quantity;
3256
3257 -- Assign Requisition Line Number as Distribution Number
3258 l_dist_rec.distribution_num := l_line_tbl(i).line_num;
3259
3260 -- Assign SYSDATE to gl_encumbered_date
3261 l_dist_rec.gl_encumbered_date := l_today;
3262 l_dist_rec.prevent_encumbrance_flag := 'N';
3263 --s_chart_of_accounts_id := 101;
3264
3265 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3266 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3267 'csp.plsql.csp_parts_order.process_order',
3268 'l_header_rec.dest_organization_id = ' || l_header_rec.dest_organization_id);
3269 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3270 'csp.plsql.csp_parts_order.process_order',
3271 'l_line_tbl(i).inventory_item_id = ' || l_line_tbl(i).inventory_item_id);
3272 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3273 'csp.plsql.csp_parts_order.process_order',
3274 'l_line_Tbl(i).dest_subinventory = ' || l_line_Tbl(i).dest_subinventory);
3275 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3276 'csp.plsql.csp_parts_order.process_order',
3277 'l_header_rec.dest_subinventory = ' || l_header_rec.dest_subinventory);
3278 end if;
3279
3280 -- Get Charge Account ID
3281 l_dist_rec.code_combination_id := get_charge_account_fun
3282 (l_header_rec.dest_organization_id,
3283 l_line_tbl(i).inventory_item_id,
3284 nvl(l_line_Tbl(i).dest_subinventory, l_header_rec.dest_subinventory)); -- bug # 12433536 scheduler does not pass l_line_Tbl(i).dest_subinventory
3285
3286
3287 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3288 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3289 'csp.plsql.csp_parts_order.process_order',
3290 'Checking Valid Account Id...');
3291 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3292 'csp.plsql.csp_parts_order.process_order',
3293 'l_dist_rec.code_combination_id = '
3294 || l_dist_rec.code_combination_id);
3295 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3296 'csp.plsql.csp_parts_order.process_order',
3297 'l_dist_rec.gl_encumbered_date = '
3298 || to_char(l_dist_rec.gl_encumbered_date, 'DD-MON-YYYY HH24:MI:SS'));
3299 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3300 'csp.plsql.csp_parts_order.process_order',
3301 'l_chart_of_accounts_id = '
3302 || l_chart_of_accounts_id);
3303 end if;
3304
3305 -- Check for valid charge account. If Invalid Raise ERROR
3306 IF NOT valid_account_id_fun (l_dist_rec.code_combination_id,
3307 l_dist_rec.gl_encumbered_date,
3308 l_chart_of_accounts_id) THEN
3309 Raise INVALID_CHARGE_ACCOUNT;
3310 END IF;
3311
3312 -- Get Accrual Account ID and Variance Account ID for the
3313 --Destination Organization from MTL_PARAMETERS
3314
3315 OPEN accrual_account_id_cur (l_header_Rec.dest_organization_id);
3316 FETCH accrual_account_id_cur
3317 INTO l_dist_rec.accrual_account_id,
3318 l_dist_rec.variance_account_id;
3319 CLOSE accrual_account_id_cur;
3320
3321 -- Check for valid accrual account. If Invalid Raise ERROR
3322 IF NOT valid_account_id_fun (l_dist_rec.accrual_account_id,
3323 l_dist_rec.gl_encumbered_date,
3324 l_chart_of_accounts_id) THEN
3325 Raise INVALID_ACCRUAL_ACCOUNT;
3326 END IF;
3327
3328 -- Check for valid variance account. If Invalid Raise ERROR
3329 IF NOT valid_account_id_fun (l_dist_rec.variance_account_id,
3330 l_dist_rec.gl_encumbered_date,
3331 l_chart_of_accounts_id) THEN
3332 Raise INVALID_VARIANCE_ACCOUNT;
3333 END IF;
3334
3335 -- Assign Set of Books ID
3336 l_dist_rec.set_of_books_id := l_set_of_books_id;
3337
3338 -- Get Requisition Encumbrance Flag for Financial System Parameters
3339
3340 -- If Req_Encumbrance_flag = 'Y' populate Budget Account ID for
3341 -- Req Distribution
3342 -- If gl_encumbered_flag = 'N' then don't populate gl_encumbered_date
3343
3344 OPEN req_encumbrance_cur (l_dist_rec.set_of_books_id);
3345 FETCH req_encumbrance_cur INTO l_dist_rec.encumbered_flag;
3346 CLOSE req_encumbrance_cur;
3347
3348 -- bug # 12359711
3349 l_dist_rec.prevent_encumbrance_flag := l_dist_rec.encumbered_flag;
3350 l_dist_rec.encumbered_flag := 'N';
3351
3352 IF l_dist_rec.encumbered_flag = 'Y' THEN
3353 OPEN budget_account_cur (l_header_rec.dest_organization_id,
3354 l_line_tbl(i).inventory_item_id);
3355 FETCH budget_account_cur INTO l_dist_rec.budget_account_id;
3356 CLOSE budget_account_cur;
3357
3358 -- Check for valid budget account. If Invalid Raise ERROR
3359 IF NOT valid_account_id_fun (l_dist_rec.budget_account_id,
3360 l_dist_rec.gl_encumbered_date,
3361 l_chart_of_accounts_id) THEN
3362 Raise INVALID_BUDGET_ACCOUNT;
3363 END IF;
3364 ELSE
3365 l_dist_rec.gl_encumbered_date := '';
3366 END IF;
3367
3368 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3369 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3370 'csp.plsql.csp_parts_order.process_order',
3371 'prevent_encumbrance_flag = ' || l_dist_rec.prevent_encumbrance_flag
3372 || ', encumbered_flag = ' || l_dist_rec.encumbered_flag);
3373 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3374 'csp.plsql.csp_parts_order.process_order',
3375 'Inserting data into po_req_distributions ...');
3376 end if;
3377
3378 -- create po_req_distributions
3379 INSERT INTO po_req_distributions(
3380 distribution_id
3381 ,last_update_date
3382 ,last_updated_by
3383 ,requisition_line_id
3384 ,set_of_books_id
3385 ,code_combination_id
3386 ,req_line_quantity
3387 ,last_update_login
3388 ,creation_date
3389 ,created_by
3390 ,encumbered_flag
3391 ,gl_encumbered_date
3392 ,gl_encumbered_period_name
3393 ,gl_cancelled_date
3394 ,failed_funds_lookup_code
3395 ,encumbered_amount
3396 ,budget_account_id
3397 ,accrual_account_id
3398 ,variance_account_id
3399 ,prevent_encumbrance_flag
3400 ,attribute_category
3401 ,attribute1
3402 ,attribute2
3403 ,attribute3
3404 ,attribute4
3405 ,attribute5
3406 ,attribute6
3407 ,attribute7
3408 ,attribute8
3409 ,attribute9
3410 ,attribute10
3411 ,attribute11
3412 ,attribute12
3413 ,attribute13
3414 ,attribute14
3415 ,attribute15
3416 ,ussgl_transaction_code
3417 ,government_context
3418 ,project_id
3419 ,task_id
3420 ,expenditure_type
3421 ,project_accounting_context
3422 ,expenditure_organization_id
3423 ,gl_closed_date
3424 ,source_req_distribution_id
3425 ,distribution_num
3426 ,project_related_flag
3427 ,expenditure_item_date
3428 ,org_id
3429 ,allocation_type
3430 ,allocation_value
3431 ,award_id
3432 ,end_item_unit_number
3433 ,recoverable_tax
3434 ,nonrecoverable_tax
3435 ,recovery_rate
3436 ,tax_recovery_override_flag
3437 ,oke_contract_line_id
3438 ,oke_contract_deliverable_id
3439 )
3440 VALUES
3441 (
3442 l_dist_rec.distribution_id
3443 ,l_today --last_update_date
3444 ,l_user_id --last_updated_by
3445 ,l_dist_rec.requisition_line_id
3446 ,l_dist_rec.set_of_books_id
3447 ,l_dist_rec.code_combination_id
3448 ,l_dist_rec.req_line_quantity
3449 ,l_login_id --last_update_login
3450 ,l_today --creation_date
3451 ,l_user_id --created_by
3452 ,l_dist_rec.encumbered_flag
3453 ,l_dist_rec.gl_encumbered_date
3454 ,l_dist_rec.gl_encumbered_period_name
3455 ,l_dist_rec.gl_cancelled_date
3456 ,l_dist_rec.failed_funds_lookup_code
3457 ,l_dist_rec.encumbered_amount
3458 ,l_dist_rec.budget_account_id
3459 ,l_dist_rec.accrual_account_id
3460 ,l_dist_rec.variance_account_id
3461 ,l_dist_rec.prevent_encumbrance_flag
3462 ,l_dist_rec.attribute_category
3463 ,l_dist_rec.attribute1
3464 ,l_dist_rec.attribute2
3465 ,l_dist_rec.attribute3
3466 ,l_dist_rec.attribute4
3467 ,l_dist_rec.attribute5
3468 ,l_dist_rec.attribute6
3469 ,l_dist_rec.attribute7
3470 ,l_dist_rec.attribute8
3471 ,l_dist_rec.attribute9
3472 ,l_dist_rec.attribute10
3473 ,l_dist_rec.attribute11
3474 ,l_dist_rec.attribute12
3475 ,l_dist_rec.attribute13
3476 ,l_dist_rec.attribute14
3477 ,l_dist_rec.attribute15
3478 ,l_dist_rec.ussgl_transaction_code
3479 ,l_dist_rec.government_context
3480 ,l_dist_rec.project_id
3481 ,l_dist_rec.task_id
3482 ,l_dist_rec.expenditure_type
3483 ,l_dist_rec.project_accounting_context
3484 ,l_dist_rec.expenditure_organization_id
3485 ,l_dist_rec.gl_closed_date
3486 ,l_dist_rec.source_req_distribution_id
3487 ,l_dist_rec.distribution_num
3488 ,l_dist_rec.project_related_flag
3489 ,l_dist_rec.expenditure_item_date
3490 ,l_dist_rec.org_id
3491 ,l_dist_rec.allocation_type
3492 ,l_dist_rec.allocation_value
3493 ,l_dist_rec.award_id
3494 ,l_dist_rec.end_item_unit_number
3495 ,l_dist_rec.recoverable_tax
3496 ,l_dist_rec.nonrecoverable_tax
3497 ,l_dist_rec.recovery_rate
3498 ,l_dist_rec.tax_recovery_override_flag
3499 ,l_dist_rec.oke_contract_line_id
3500 ,l_dist_rec.oke_contract_deliverable_id
3501 );
3502
3503
3504 END IF;
3505
3506 IF (p_process_type in ('ORDER', 'BOTH')) THEN
3507 -- SETTING UP THE ORDER PROCESS LINE RECORD
3508
3509 /* Same as 115.10 bug 5362711 but for R12 */
3510 /* Get Unit Price and Currency Code*/
3511 get_unit_price_prc (l_line_tbl(i).inventory_item_id
3512 ,l_line_tbl(i).source_organization_id
3513 ,l_header_rec.dest_organization_id
3514 ,l_set_of_books_id
3515 ,l_chart_of_accounts_id
3516 ,l_currency_code
3517 ,l_unit_price
3518 ,l_item_cost );
3519
3520 IF l_line_tbl(i).order_line_id IS NULL THEN
3521 select oe_order_lines_s.nextval
3522 into l_line_tbl(i).order_line_id
3523 from dual;
3524 END IF;
3525
3526 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3527 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3528 'csp.plsql.csp_parts_order.process_order',
3529 'l_line_tbl(i).order_line_id ('
3530 || to_char(i) || ') = ' || l_line_tbl(i).order_line_id);
3531 end if;
3532
3533 l_oe_line_rec.org_id := l_source_operating_unit;
3534 l_oe_line_rec.header_id := l_oe_header_rec.header_id;
3535 l_oe_line_rec.line_id := l_line_tbl(i).order_line_id;
3536 l_oe_line_rec.line_number := l_line_tbl(i).line_num;
3537 /*
3538 IF (nvl(l_scheduling_code, 'THREE') = 'THREE' OR
3539 nvl(l_scheduling_code, 'THREE') = 'FOUR') THEN
3540 l_oe_line_rec.reserved_quantity := l_line_tbl(i).ordered_quantity;
3541 END IF;
3542 */
3543 l_oe_line_rec.line_type_id := l_order_line_type_id;
3544 l_oe_line_rec.inventory_item_id := l_line_tbl(i).inventory_item_id;
3545 l_oe_line_rec.item_revision := l_line_tbl(i).revision;
3546 l_oe_line_rec.order_quantity_uom := l_line_tbl(i).unit_of_measure;
3547 IF (l_line_price_list_id IS NOT NULL) THEN
3548 l_oe_line_rec.price_list_id := l_line_price_list_id;
3549 END IF;
3550 l_oe_line_rec.ORIG_SYS_DOCUMENT_REF := l_header_rec.requisition_number;
3551 l_oe_line_rec.ORIG_SYS_LINE_REF := l_line_tbl(i).line_num;
3552 l_oe_line_rec.ship_from_org_id := l_line_tbl(i).source_organization_id;
3553 IF (l_oe_line_rec.subinventory IS NOT NULL) THEN
3554 l_oe_line_rec.subinventory := l_line_tbl(i).source_subinventory;
3555 END IF;
3556 l_oe_line_rec.request_date := nvl(l_header_rec.need_by_date,sysdate);
3557
3558
3559 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3560 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3561 'csp.plsql.csp_parts_order.process_order',
3562 'l_line_tbl(i).arrival_date = ' || l_line_tbl(i).arrival_date);
3563 end if;
3564
3565 if l_line_tbl(i).arrival_date is not null then
3566 l_oe_line_rec.promise_date := l_line_tbl(i).arrival_date;
3567 l_oe_line_rec.actual_arrival_date := l_line_tbl(i).arrival_date;
3568 --l_oe_line_rec.request_date := l_line_tbl(i).arrival_date;
3569 end if;
3570
3571 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3572 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3573 'csp.plsql.csp_parts_order.process_order',
3574 'l_oe_line_rec.promise_date = ' || l_oe_line_rec.promise_date);
3575 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3576 'csp.plsql.csp_parts_order.process_order',
3577 'l_oe_line_rec.request_date = ' || l_oe_line_rec.request_date);
3578 end if;
3579
3580 l_oe_line_rec.sold_to_org_id := l_cust_acct_id;
3581 l_oe_line_rec.ship_to_org_id := l_site_use_id;
3582
3583 -- bug # 6471559
3584 if l_bill_to_site_use_id is not NULL then
3585 l_oe_line_rec.invoice_to_org_id := l_bill_to_site_use_id;
3586 end if;
3587
3588 if l_oe_header_rec.ship_to_contact_id is not null then
3589 l_oe_line_rec.ship_to_contact_id := l_oe_header_rec.ship_to_contact_id;
3590 end if;
3591
3592 if l_oe_header_rec.INVOICE_TO_CONTACT_ID is not null then
3593 l_oe_line_rec.INVOICE_TO_CONTACT_ID := l_oe_header_rec.INVOICE_TO_CONTACT_ID;
3594 end if;
3595
3596 l_oe_line_rec.line_category_code := l_order_line_category_code;
3597 l_oe_line_rec.order_source_id := l_order_source_id;
3598 l_oe_line_rec.source_document_type_id := l_order_source_id;
3599 l_oe_line_rec.source_document_id := l_header_rec.requisition_header_id;
3600 l_oe_line_rec.source_document_line_id := l_line_tbl(i).requisition_line_id;
3601 l_oe_line_rec.ship_set := l_line_tbl(i).ship_complete;
3602 l_oe_line_Rec.shipping_method_code := nvl(l_line_tbl(i).shipping_method_code, FND_API.G_MISS_CHAR);
3603
3604 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3605 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3606 'csp.plsql.csp_parts_order.process_order',
3607 'l_oe_line_Rec.shipping_method_code = ' || l_oe_line_Rec.shipping_method_code);
3608 end if;
3609
3610 l_oe_line_Rec.calculate_price_flag := 'N';
3611 l_oe_line_Rec.unit_list_price := l_unit_price;
3612 l_oe_line_Rec.unit_Selling_price := l_unit_price;
3613 l_oe_line_Rec.open_flag := 'Y';
3614 l_oe_line_rec.ordered_quantity := l_line_tbl(i).ordered_quantity;
3615
3616 /*
3617 if nvl(p_book_order, 'Y') = 'Y' then
3618 l_oe_line_rec.booked_flag := l_line_tbl(i).booked_Flag; --N;
3619 else
3620 l_oe_line_rec.booked_flag := 'N';
3621 end if;
3622 */
3623
3624 l_oe_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
3625 END IF;
3626
3627 ELSIF (l_header_rec.operation = CSP_PARTS_ORDER.G_OPR_UPDATE) THEN
3628 IF (p_process_Type = 'REQUISITION') THEN
3629 IF (l_line_Tbl(I).requisition_line_id IS NULL) THEN
3630 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
3631 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_line_rec.requisition_line_id', FALSE);
3632 FND_MSG_PUB.ADD;
3633 RAISE EXCP_USER_DEFINED;
3634 END IF;
3635
3636 -- update requisition line table with new quantity
3637 -- quantity is the only change allowed
3638 update po_requisition_lines
3639 set quantity = l_line_tbl(I).ordered_quantity
3640 where requisition_line_id = l_line_Tbl(I).requisition_line_id;
3641
3642 -- update req distributions with new quantity
3643 update po_req_distributions
3644 set req_line_quantity = l_line_tbl(i).ordered_quantity
3645 where requisition_line_id = l_line_tbl(i).requisition_line_id;
3646
3647 -- update mtl_supply data for the requisition
3648 IF NOT po_supply.po_req_supply(
3649 p_docid => null,
3650 p_lineid => l_line_Tbl(I).requisition_line_id,
3651 p_shipid => null,
3652 p_action => 'Update_Req_Line_Qty',
3653 p_recreate_flag => NULL,
3654 p_qty => l_line_tbl(i).ordered_quantity,
3655 p_receipt_date => NULL) THEN
3656
3657 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
3658 token1 => 'FILE',
3659 value1 => 'PO_SUPPLY',
3660 token2 => 'ERR_NUMBER',
3661 value2 => '035',
3662 token3 => 'SUBROUTINE',
3663 value3 => 'PO_REQ_SUPPLY()');
3664 RAISE FND_API.G_EXC_ERROR;
3665 END IF;
3666
3667 ELSIF (p_process_type in ('ORDER', 'BOTH')) THEN
3668 IF (l_line_tbl(i).order_line_id IS NULL) THEN
3669 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
3670 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_line_rec.order_line_id', FALSE);
3671
3672 FND_MSG_PUB.ADD;
3673 RAISE EXCP_USER_DEFINED;
3674 END IF;
3675
3676 -- l_oe_line_Rec := oe_line_util.Query_Row(l_line_tbl(i).order_line_id);
3677
3678 l_oe_line_rec.line_id := l_line_tbl(i).order_line_id;
3679 l_oe_line_rec.booked_flag := l_line_tbl(i).booked_Flag;
3680 l_oe_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
3681 END If;
3682
3683 END IF;
3684
3685 --l_oe_line_rec.ordered_quantity := l_line_tbl(i).ordered_quantity;
3686 --l_oe_line_rec.booked_flag := l_line_tbl(i).booked_Flag; --N;
3687
3688 -- Adding this record to the line table to be passed to process order
3689 l_oe_line_tbl(i) := l_oe_line_rec;
3690
3691 END LOOP;
3692
3693 -- create supply information for requisitions created
3694 IF (p_process_type in ('REQUISITION', 'BOTH') AND l_header_Rec.operation = G_OPR_CREATE) THEN
3695
3696 /*IF NOT po_supply.po_req_supply(
3697 p_docid => l_header_rec.requisition_header_id,
3698 p_lineid => null,
3699 p_shipid => null,
3700 p_action => 'Approve_Req_Supply',
3701 p_recreate_flag => NULL,
3702 p_qty => NULL,
3703 p_receipt_date => NULL) THEN
3704
3705
3706 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
3707 token1 => 'FILE',
3708 value1 => 'PO_SUPPLY',
3709 token2 => 'ERR_NUMBER',
3710 value2 => '005',
3711 token3 => 'SUBROUTINE',
3712 value3 => 'PO_REQ_SUPPLY()');
3713 END IF;
3714 */
3715 BEGIN
3716
3717 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3718 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3719 'csp.plsql.csp_parts_order.process_order',
3720 'Deleting record from mtl_supply...');
3721 end if;
3722
3723 DELETE FROM mtl_supply ms1
3724 WHERE ms1.supply_source_id IN
3725 (
3726 SELECT pl.requisition_line_id
3727 FROM po_requisition_lines pl
3728 WHERE pl.requisition_header_id = l_header_rec.requisition_header_id
3729 AND NVL(pl.modified_by_agent_flag, 'N') <> 'Y'
3730 AND NVL(pl.closed_code, 'OPEN') = 'OPEN'
3731 AND NVL(pl.cancel_flag, 'N') = 'N'
3732 AND pl.line_location_id IS NULL
3733 )
3734 AND ms1.supply_type_code = 'REQ';
3735
3736 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3737 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3738 'csp.plsql.csp_parts_order.process_order',
3739 'Inserting data into mtl_supply...');
3740 end if;
3741
3742 INSERT INTO mtl_supply
3743 (supply_type_code,
3744 supply_source_id,
3745 last_updated_by,
3746 last_update_date,
3747 last_update_login,
3748 created_by,
3749 creation_date,
3750 req_header_id,
3751 req_line_id,
3752 item_id,
3753 item_revision,
3754 quantity,
3755 unit_of_measure,
3756 receipt_date,
3757 need_by_date,
3758 destination_type_code,
3759 location_id,
3760 from_organization_id,
3761 from_subinventory,
3762 to_organization_id,
3763 to_subinventory,
3764 change_flag)
3765 SELECT 'REQ',
3766 prl.requisition_line_id,
3767 last_updated_by,
3768 last_update_date,
3769 last_update_login,
3770 created_by,
3771 creation_date,
3772 prl.requisition_header_id,
3773 prl.requisition_line_id,
3774 prl.item_id,
3775 decode(prl.source_type_code,'INVENTORY', null,
3776 prl.item_revision),
3777 prl.quantity - ( nvl(prl.QUANTITY_CANCELLED, 0) +
3778 nvl(prl.QUANTITY_DELIVERED, 0) ),
3779 prl.unit_meas_lookup_code,
3780 prl.need_by_date,
3781 prl.need_by_date,
3782 prl.destination_type_code,
3783 prl.deliver_to_location_id,
3784 prl.source_organization_id,
3785 prl.source_subinventory,
3786 prl.destination_organization_id,
3787 prl.destination_subinventory,
3788 'Y'
3789 FROM po_requisition_lines prl
3790 WHERE prl.requisition_header_id = l_header_rec.requisition_header_id
3791 AND nvl(prl.modified_by_agent_flag,'N') <> 'Y'
3792 AND nvl(prl.CLOSED_CODE,'OPEN') = 'OPEN'
3793 AND nvl(prl.CANCEL_FLAG, 'N') = 'N'
3794 -- <Doc Manager Rewrite R12>: Filter out amount basis
3795 AND prl.matching_basis <> 'AMOUNT'
3796 AND prl.line_location_id is null
3797 AND not exists
3798 (SELECT 'supply exists'
3799 FROM mtl_supply ms
3800 WHERE ms.supply_type_code = 'REQ'
3801 AND ms.supply_source_id = prl.requisition_line_id);
3802 EXCEPTION
3803 when no_data_found THEN
3804
3805 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
3806 token1 => 'FILE',
3807 value1 => 'PO_SUPPLY',
3808 token2 => 'ERR_NUMBER',
3809 value2 => '005',
3810 token3 => 'SUBROUTINE',
3811 value3 => 'PO_REQ_SUPPLY()');
3812 RAISE FND_API.G_EXC_ERROR;
3813 END;
3814
3815
3816 END IF;
3817 BEGIN
3818 update mtl_supply
3819 set expected_delivery_date = nvl(l_header_rec.need_by_date, sysdate),
3820 need_by_date = nvl(l_header_rec.need_by_date, sysdate)
3821 where req_header_id = l_header_rec.requisition_header_id;
3822 EXCEPTION
3823 WHEN no_data_found THEN
3824 null;
3825 END;
3826
3827 IF (p_process_type in ('ORDER', 'BOTH')) THEN
3828 -- cross operating unit order
3829 IF (l_source_operating_unit <> l_org_id) THEN
3830 /*
3831 OPEN get_new_context(l_source_operating_unit);
3832 FETCH get_new_context
3833 INTO new_user_id,new_resp_id,new_resp_appl_id;
3834 CLOSE get_new_context;
3835 */
3836 fnd_profile.get('CSP_IO_USER', new_user);
3837 new_user_id := substr(new_user, 1, instr(new_user, '~') - 1);
3838 new_user := substr(ltrim(new_user, new_user_id), 3);
3839 new_resp_id := substr(new_user, 1, instr(new_user, '~') - 1);
3840 new_resp_appl_id := substr(ltrim(new_user, new_resp_id), 3);
3841
3842 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3843 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3844 'csp.plsql.csp_parts_order.process_order',
3845 'new_user = ' || new_user
3846 || ', new_user_id = ' || new_user_id
3847 || ', new_resp_id = ' || new_resp_id
3848 || ', new_resp_appl_id = ' || new_resp_appl_id);
3849 end if;
3850
3851 IF new_resp_id is not null and
3852 new_user_id is not null and
3853 new_resp_appl_id is not null THEN
3854 fnd_global.apps_initialize(new_user_id,new_resp_id,new_resp_appl_id);
3855 mo_global.set_policy_context('S', l_source_operating_unit);
3856 /* fnd_profile.get('ORG_ID',new_org_id); --Operating Unit for the new context.
3857 IF l_source_operating_unit <> new_org_id THEN
3858 FND_MESSAGE.Set_Name('CS','CS_CHG_NEW_CONTEXT_OU_NOT_MATCH'); --to be seeded.
3859 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
3860 FND_MSG_PUB.Add;
3861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3862 END IF;
3863 */
3864 ELSE
3865 --dbms_application_info.set_client_info(l_source_operating_unit);
3866 mo_global.set_policy_context('S', l_source_operating_unit);
3867 END IF;
3868 END If;
3869
3870 -- CONTROL RECORD
3871 -- Use the default settings
3872 l_oe_control_rec.controlled_operation := FALSE;
3873 l_oe_control_rec.default_Attributes := TRUE;
3874
3875 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3876 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3877 'csp.plsql.csp_parts_order.process_order',
3878 'Now finally calling OE_Order_PUB.Process_Order for operation = '
3879 || l_oe_header_rec.operation);
3880 end if;
3881
3882 -- CALL TO PROCESS ORDER
3883 IF (l_oe_header_rec.operation = OE_GLOBALS.G_OPR_CREATE) THEN
3884
3885 -- CALL TO PROCESS ORDER
3886 l_action_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
3887
3888 if nvl(p_book_order, 'Y') = 'Y' then
3889 l_action_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;
3890 end if;
3891 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3892 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csp.plsql.csp_parts_order.process_order',
3893 'start calling requirement order dff hook...');
3894 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3895 'csp.plsql.csp.plsql.csp_parts_order.process_order',
3896 'Values of before calling the hook api PR Header : Attribute 1 : ' || px_header_rec.ATTRIBUTE1 || ' , ' ||
3897 'Attribute 2 : ' || px_header_rec.ATTRIBUTE2 || ' , ' ||
3898 'Attribute 3 : ' || px_header_rec.ATTRIBUTE3 || ' , ' ||
3899 'Attribute 4 :' || px_header_rec.ATTRIBUTE4 || ' , ' ||
3900 'Attribute 5: ' || px_header_rec.ATTRIBUTE5 || ' , ' ||
3901 'Attribute 6: ' || px_header_rec.ATTRIBUTE6 || ' , ' ||
3902 'Attribute 7: ' || px_header_rec.ATTRIBUTE7 || ' , ' ||
3903 'Attribute 8: ' || px_header_rec.ATTRIBUTE8 || ' , ' ||
3904 'Attribute 9: ' || px_header_rec.ATTRIBUTE9 || ' , ' ||
3905 'Attribute 10: ' || px_header_rec.ATTRIBUTE10 || ' , ' ||
3906 'Attribute 11:' || px_header_rec.ATTRIBUTE11 || ' , ' ||
3907 'Attribute 12: ' || px_header_rec.ATTRIBUTE12 || ' , ' ||
3908 'Attribute 13: ' || px_header_rec.ATTRIBUTE13 || ' , ' ||
3909 'Attribute 14: ' || px_header_rec.ATTRIBUTE14 || ' , ' ||
3910 'Attribute 15: ' || px_header_rec.ATTRIBUTE15);
3911 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3912 'csp.plsql.csp.plsql.csp_parts_order.process_order',
3913 'Values of before calling the hook api OE Header : Attribute 1: ' || l_oe_header_rec.ATTRIBUTE1 || ' , ' ||
3914 'Attribute 2: ' || l_oe_header_rec.ATTRIBUTE2 || ' , ' ||
3915 'Attribute 3: ' || l_oe_header_rec.ATTRIBUTE3 || ' , ' ||
3916 'Attribute 4: ' || l_oe_header_rec.ATTRIBUTE4 || ' , ' ||
3917 'Attribute 5: ' || l_oe_header_rec.ATTRIBUTE5 || ' , ' ||
3918 'Attribute 6: ' || l_oe_header_rec.ATTRIBUTE6 || ' , ' ||
3919 'Attribute 7: ' || l_oe_header_rec.ATTRIBUTE7 || ' , ' ||
3920 'Attribute 8: ' || l_oe_header_rec.ATTRIBUTE8 || ' , ' ||
3921 'Attribute 9: ' || l_oe_header_rec.ATTRIBUTE9 || ' , ' ||
3922 'Attribute 10: ' || l_oe_header_rec.ATTRIBUTE10 || ' , ' ||
3923 'Attribute 11: ' || l_oe_header_rec.ATTRIBUTE11 || ' , ' ||
3924 'Attribute 12: ' || l_oe_header_rec.ATTRIBUTE12 || ' , ' ||
3925 'Attribute 13:' || l_oe_header_rec.ATTRIBUTE13 || ' , ' ||
3926 'Attribute 14: ' || l_oe_header_rec.ATTRIBUTE14 || ' , ' ||
3927 'Attribute 15: ' || l_oe_header_rec.ATTRIBUTE15);
3928 for k in 1..px_line_table.count loop
3929 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3930 'csp.plsql.csp.plsql.csp_parts_order.process_order',
3931 'Values of before calling the hook api PR Line' || k || ': Attribute 1: ' || px_line_table(k).ATTRIBUTE1 || ' , ' ||
3932 'Attribute 2: ' || px_line_table(k).ATTRIBUTE2 || ' , ' ||
3933 'Attribute 3: ' || px_line_table(k).ATTRIBUTE3 || ' , ' ||
3934 'Attribute 4: ' || px_line_table(k).ATTRIBUTE4 || ' , ' ||
3935 'Attribute 5: ' || px_line_table(k).ATTRIBUTE5 || ' , ' ||
3936 'Attribute 6: ' || px_line_table(k).ATTRIBUTE6 || ' , ' ||
3937 'Attribute 7: ' || px_line_table(k).ATTRIBUTE7 || ' , ' ||
3938 'Attribute 8: ' || px_line_table(k).ATTRIBUTE8 || ' , ' ||
3939 'Attribute 9: ' || px_line_table(k).ATTRIBUTE9 || ' , ' ||
3940 'Attribute 10: ' || px_line_table(k).ATTRIBUTE10 || ' , ' ||
3941 'Attribute 11: ' || px_line_table(k).ATTRIBUTE11 || ' , ' ||
3942 'Attribute 12: ' || px_line_table(k).ATTRIBUTE12 || ' , ' ||
3943 'Attribute 13: ' || px_line_table(k).ATTRIBUTE13 || ' , ' ||
3944 'Attribute 14: ' || px_line_table(k).ATTRIBUTE14 || ' , ' ||
3945 'Attribute 15: ' || px_line_table(k).ATTRIBUTE15);
3946 end loop;
3947 for k in 1..l_oe_line_tbl.count loop
3948 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3949 'csp.plsql.csp.plsql.csp_parts_order.process_order',
3950 'Values of before calling the hook api OE Line' || k || ': Attribute 1: ' || l_oe_line_tbl(k).ATTRIBUTE1 || ' , ' ||
3951 'Attribute 2: ' || l_oe_line_tbl(k).ATTRIBUTE2 || ' , ' ||
3952 'Attribute 3: ' || l_oe_line_tbl(k).ATTRIBUTE3 || ' , ' ||
3953 'Attribute 4: ' || l_oe_line_tbl(k).ATTRIBUTE4 || ' , ' ||
3954 'Attribute 5: ' || l_oe_line_tbl(k).ATTRIBUTE5 || ' , ' ||
3955 'Attribute 6: ' || l_oe_line_tbl(k).ATTRIBUTE6 || ' , ' ||
3956 'Attribute 7: ' || l_oe_line_tbl(k).ATTRIBUTE7 || ' , ' ||
3957 'Attribute 8: ' || l_oe_line_tbl(k).ATTRIBUTE8 || ' , ' ||
3958 'Attribute 9: ' || l_oe_line_tbl(k).ATTRIBUTE9 || ' , ' ||
3959 'Attribute 10: ' || l_oe_line_tbl(k).ATTRIBUTE10 || ' , ' ||
3960 'Attribute 11: ' || l_oe_line_tbl(k).ATTRIBUTE11 || ' , ' ||
3961 'Attribute 12: ' || l_oe_line_tbl(k).ATTRIBUTE12 || ' , ' ||
3962 'Attribute 13: ' || l_oe_line_tbl(k).ATTRIBUTE13 || ' , ' ||
3963 'Attribute 14: ' || l_oe_line_tbl(k).ATTRIBUTE14 || ' , ' ||
3964 'Attribute 15: ' || l_oe_line_tbl(k).ATTRIBUTE15);
3965 end loop;
3966
3967 end if;
3968 csp_process_order_hook.update_oe_dff_info(
3969 px_req_header_rec => px_header_rec
3970 ,px_req_line_table => px_line_table
3971 ,px_oe_header_rec => l_oe_header_rec
3972 ,px_oe_line_table => l_oe_line_tbl);
3973 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3974 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3975 'csp.plsql.csp.plsql.csp_parts_order.process_order',
3976 'Values of after calling the hook api PR Header : Attribute 1 : ' || px_header_rec.ATTRIBUTE1 || ' , ' ||
3977 'Attribute 2 : ' || px_header_rec.ATTRIBUTE2 || ' , ' ||
3978 'Attribute 3 : ' || px_header_rec.ATTRIBUTE3 || ' , ' ||
3979 'Attribute 4 :' || px_header_rec.ATTRIBUTE4 || ' , ' ||
3980 'Attribute 5: ' || px_header_rec.ATTRIBUTE5 || ' , ' ||
3981 'Attribute 6: ' || px_header_rec.ATTRIBUTE6 || ' , ' ||
3982 'Attribute 7: ' || px_header_rec.ATTRIBUTE7 || ' , ' ||
3983 'Attribute 8: ' || px_header_rec.ATTRIBUTE8 || ' , ' ||
3984 'Attribute 9: ' || px_header_rec.ATTRIBUTE9 || ' , ' ||
3985 'Attribute 10: ' || px_header_rec.ATTRIBUTE10 || ' , ' ||
3986 'Attribute 11:' || px_header_rec.ATTRIBUTE11 || ' , ' ||
3987 'Attribute 12: ' || px_header_rec.ATTRIBUTE12 || ' , ' ||
3988 'Attribute 13: ' || px_header_rec.ATTRIBUTE13 || ' , ' ||
3989 'Attribute 14: ' || px_header_rec.ATTRIBUTE14 || ' , ' ||
3990 'Attribute 15: ' || px_header_rec.ATTRIBUTE15);
3991 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3992 'csp.plsql.csp.plsql.csp_parts_order.process_order',
3993 'Values of after calling the hook api OE Header : Attribute 1: ' || l_oe_header_rec.ATTRIBUTE1 || ' , ' ||
3994 'Attribute 2: ' || l_oe_header_rec.ATTRIBUTE2 || ' , ' ||
3995 'Attribute 3: ' || l_oe_header_rec.ATTRIBUTE3 || ' , ' ||
3996 'Attribute 4: ' || l_oe_header_rec.ATTRIBUTE4 || ' , ' ||
3997 'Attribute 5: ' || l_oe_header_rec.ATTRIBUTE5 || ' , ' ||
3998 'Attribute 6: ' || l_oe_header_rec.ATTRIBUTE6 || ' , ' ||
3999 'Attribute 7: ' || l_oe_header_rec.ATTRIBUTE7 || ' , ' ||
4000 'Attribute 8: ' || l_oe_header_rec.ATTRIBUTE8 || ' , ' ||
4001 'Attribute 9: ' || l_oe_header_rec.ATTRIBUTE9 || ' , ' ||
4002 'Attribute 10: ' || l_oe_header_rec.ATTRIBUTE10 || ' , ' ||
4003 'Attribute 11: ' || l_oe_header_rec.ATTRIBUTE11 || ' , ' ||
4004 'Attribute 12: ' || l_oe_header_rec.ATTRIBUTE12 || ' , ' ||
4005 'Attribute 13:' || l_oe_header_rec.ATTRIBUTE13 || ' , ' ||
4006 'Attribute 14: ' || l_oe_header_rec.ATTRIBUTE14 || ' , ' ||
4007 'Attribute 15: ' || l_oe_header_rec.ATTRIBUTE15);
4008 for k in 1..px_line_table.count loop
4009 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4010 'csp.plsql.csp.plsql.csp_parts_order.process_order',
4011 'Values of after calling the hook api PR Line' || k || ': Attribute 1: ' || px_line_table(k).ATTRIBUTE1 || ' , ' ||
4012 'Attribute 2: ' || px_line_table(k).ATTRIBUTE2 || ' , ' ||
4013 'Attribute 3: ' || px_line_table(k).ATTRIBUTE3 || ' , ' ||
4014 'Attribute 4: ' || px_line_table(k).ATTRIBUTE4 || ' , ' ||
4015 'Attribute 5: ' || px_line_table(k).ATTRIBUTE5 || ' , ' ||
4016 'Attribute 6: ' || px_line_table(k).ATTRIBUTE6 || ' , ' ||
4017 'Attribute 7: ' || px_line_table(k).ATTRIBUTE7 || ' , ' ||
4018 'Attribute 8: ' || px_line_table(k).ATTRIBUTE8 || ' , ' ||
4019 'Attribute 9: ' || px_line_table(k).ATTRIBUTE9 || ' , ' ||
4020 'Attribute 10: ' || px_line_table(k).ATTRIBUTE10 || ' , ' ||
4021 'Attribute 11: ' || px_line_table(k).ATTRIBUTE11 || ' , ' ||
4022 'Attribute 12: ' || px_line_table(k).ATTRIBUTE12 || ' , ' ||
4023 'Attribute 13: ' || px_line_table(k).ATTRIBUTE13 || ' , ' ||
4024 'Attribute 14: ' || px_line_table(k).ATTRIBUTE14 || ' , ' ||
4025 'Attribute 15: ' || px_line_table(k).ATTRIBUTE15);
4026 end loop;
4027 for k in 1..l_oe_line_tbl.count loop
4028 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4029 'csp.plsql.csp.plsql.csp_parts_order.process_order',
4030 'Values of after calling the hook api OE Line' || k || ': Attribute 1: ' || l_oe_line_tbl(k).ATTRIBUTE1 || ' , ' ||
4031 'Attribute 2: ' || l_oe_line_tbl(k).ATTRIBUTE2 || ' , ' ||
4032 'Attribute 3: ' || l_oe_line_tbl(k).ATTRIBUTE3 || ' , ' ||
4033 'Attribute 4: ' || l_oe_line_tbl(k).ATTRIBUTE4 || ' , ' ||
4034 'Attribute 5: ' || l_oe_line_tbl(k).ATTRIBUTE5 || ' , ' ||
4035 'Attribute 6: ' || l_oe_line_tbl(k).ATTRIBUTE6 || ' , ' ||
4036 'Attribute 7: ' || l_oe_line_tbl(k).ATTRIBUTE7 || ' , ' ||
4037 'Attribute 8: ' || l_oe_line_tbl(k).ATTRIBUTE8 || ' , ' ||
4038 'Attribute 9: ' || l_oe_line_tbl(k).ATTRIBUTE9 || ' , ' ||
4039 'Attribute 10: ' || l_oe_line_tbl(k).ATTRIBUTE10 || ' , ' ||
4040 'Attribute 11: ' || l_oe_line_tbl(k).ATTRIBUTE11 || ' , ' ||
4041 'Attribute 12: ' || l_oe_line_tbl(k).ATTRIBUTE12 || ' , ' ||
4042 'Attribute 13: ' || l_oe_line_tbl(k).ATTRIBUTE13 || ' , ' ||
4043 'Attribute 14: ' || l_oe_line_tbl(k).ATTRIBUTE14 || ' , ' ||
4044 'Attribute 15: ' || l_oe_line_tbl(k).ATTRIBUTE15);
4045 end loop;
4046 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csp.plsql.csp_parts_order.process_order',
4047 'end of calling requirement order dff hook...');
4048 end if;
4049 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4050 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csp.plsql.csp_parts_order.process_order',
4051 'Starting OW Debug...');
4052
4053 oe_debug_pub.G_FILE := NULL;
4054 oe_debug_pub.debug_on;
4055 oe_debug_pub.initialize;
4056 oe_debug_pub.setdebuglevel(5);
4057
4058 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csp.plsql.csp_parts_order.process_order',
4059 'OE Debug File : '|| OE_DEBUG_PUB.Set_Debug_Mode('FILE'));
4060
4061 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csp.plsql.csp_parts_order.process_order',
4062 'l_line_tbl.count = '|| l_line_tbl.count);
4063 end if;
4064
4065 OE_Order_PUB.Process_Order(
4066 p_org_id => l_source_operating_unit
4067 ,p_api_version_number => l_api_version_number
4068 ,p_init_msg_list => FND_API.G_TRUE
4069 ,p_return_values => FND_API.G_FALSE
4070 ,p_action_commit => FND_API.G_FALSE
4071 -- Passing just the entity records that are a part of this order
4072
4073 ,p_header_rec => l_oe_header_rec
4074 ,p_line_tbl => l_oe_line_tbl
4075 ,p_action_request_tbl => l_action_request_tbl
4076 -- OUT variables
4077 ,x_header_rec => lx_oe_header_rec
4078 ,x_header_val_rec => l_oe_Header_Val_rec
4079 ,x_header_adj_tbl => l_oe_header_adj_tbl
4080 ,x_Header_Adj_val_tbl => l_oe_header_adj_val_tbl
4081 ,x_Header_price_Att_tbl => l_oe_header_price_att_tbl
4082 ,x_Header_Adj_Att_tbl => l_oe_Header_Adj_Att_Tbl
4083 ,x_Header_Adj_Assoc_tbl => l_oe_Header_Adj_Assoc_Tbl
4084 ,x_header_scredit_tbl => l_oe_header_scr_tbl
4085 ,x_Header_Scredit_val_tbl => l_oe_Header_Scredit_Val_Tbl
4086 ,x_line_tbl => lx_oe_line_tbl
4087 ,x_line_val_tbl => l_oe_Line_Val_Tbl
4088 ,x_line_adj_tbl => l_oe_line_adj_tbl
4089 ,x_Line_Adj_val_tbl => l_oe_Line_Adj_Val_Tbl
4090 ,x_Line_price_Att_tbl => l_oe_Line_Price_Att_Tbl
4091 ,x_Line_Adj_Att_tbl => l_oe_Line_Adj_Att_Tbl
4092 ,x_Line_Adj_Assoc_tbl => l_oe_Line_Adj_Assoc_Tbl
4093 ,x_Line_Scredit_tbl => l_oe_line_scr_tbl
4094 ,x_Line_Scredit_val_tbl => l_oe_Line_Scredit_Val_Tbl
4095 ,x_Lot_Serial_tbl => l_oe_Lot_Serial_Tbl
4096 ,x_Lot_Serial_val_tbl => l_oe_Lot_Serial_Val_Tbl
4097 ,x_action_request_tbl => l_oe_Request_Tbl_Type
4098 ,x_return_status => l_return_status
4099 ,x_msg_count => l_msg_count
4100 ,x_msg_data => l_msg_data
4101 );
4102 ELSE
4103 --p_action_request_tbl => l_action_request_tbl
4104 OE_Order_PUB.Process_Order(
4105 p_org_id => l_source_operating_unit
4106 ,p_api_version_number => l_api_version_number
4107 ,p_init_msg_list => FND_API.G_TRUE
4108 ,p_return_values => FND_API.G_FALSE
4109 ,p_action_commit => FND_API.G_FALSE
4110 -- Passing just the entity records that are a part of this order
4111 -- OUT variables
4112 ,x_header_rec => lx_oe_header_rec
4113 ,x_header_val_rec => l_oe_Header_Val_rec
4114 ,x_header_adj_tbl => l_oe_header_adj_tbl
4115 ,x_Header_Adj_val_tbl => l_oe_header_adj_val_tbl
4116 ,x_Header_price_Att_tbl => l_oe_header_price_att_tbl
4117 ,x_Header_Adj_Att_tbl => l_oe_Header_Adj_Att_Tbl
4118 ,x_Header_Adj_Assoc_tbl => l_oe_Header_Adj_Assoc_Tbl
4119 ,x_header_scredit_tbl => l_oe_header_scr_tbl
4120 ,x_Header_Scredit_val_tbl => l_oe_Header_Scredit_Val_Tbl
4121 ,x_line_tbl => lx_oe_line_tbl
4122 ,x_line_val_tbl => l_oe_Line_Val_Tbl
4123 ,x_line_adj_tbl => l_oe_line_adj_tbl
4124 ,x_Line_Adj_val_tbl => l_oe_Line_Adj_Val_Tbl
4125 ,x_Line_price_Att_tbl => l_oe_Line_Price_Att_Tbl
4126 ,x_Line_Adj_Att_tbl => l_oe_Line_Adj_Att_Tbl
4127 ,x_Line_Adj_Assoc_tbl => l_oe_Line_Adj_Assoc_Tbl
4128 ,x_Line_Scredit_tbl => l_oe_line_scr_tbl
4129 ,x_Line_Scredit_val_tbl => l_oe_Line_Scredit_Val_Tbl
4130 ,x_Lot_Serial_tbl => l_oe_Lot_Serial_Tbl
4131 ,x_Lot_Serial_val_tbl => l_oe_Lot_Serial_Val_Tbl
4132 ,x_action_request_tbl => l_oe_Request_Tbl_Type
4133 ,x_return_status => l_return_status
4134 ,x_msg_count => l_msg_count
4135 ,x_msg_data => l_msg_data
4136 );
4137 END IF;
4138
4139 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4140 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csp.plsql.csp_parts_order.process_order',
4141 'Stopping OE debug ...');
4142 oe_debug_pub.debug_off;
4143 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csp.plsql.csp_parts_order.process_order',
4144 'l_return_status = ' || l_return_status);
4145 end if;
4146
4147 -- dbms_application_info.set_client_info(l_org_id);
4148 --IF (l_source_operating_unit <> l_org_id) THEN
4149 fnd_global.apps_initialize(l_user_id,orig_resp_id,orig_resp_appl_id);
4150
4151 if l_first_org_id is not null then
4152 mo_global.set_org_context(l_first_org_id,null,'CSF');
4153 end if;
4154 --END If;
4155
4156 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4157 for counter in REVERSE 1..l_msg_count Loop
4158 l_msg := OE_MSG_PUB.Get(counter,FND_API.G_FALSE) ;
4159 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
4160 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
4161 FND_MSG_PUB.ADD;
4162 fnd_msg_pub.count_and_get
4163 ( p_count => x_msg_count
4164 , p_data => x_msg_data);
4165 End loop;
4166 x_return_status := FND_API.G_RET_STS_ERROR;
4167
4168 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4169 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4170 'csp.plsql.csp_parts_order.process_order',
4171 'Error in OE_Order_PUB.Process_Order API... Message = '
4172 || l_msg);
4173 end if;
4174
4175 RAISE FND_API.G_EXC_ERROR;
4176 ELSE
4177 -- assign output variables with respected values if operation is CREATE
4178
4179 IF (l_header_Rec.operation = G_OPR_CREATE) THEN
4180 l_header_rec.order_header_id := lx_oe_header_rec.header_id;
4181
4182 FOR i in 1..lx_oe_line_tbl.count LOOP
4183 l_line_tbl(i).order_line_id := lx_oe_line_tbl(i).line_id;
4184 END LOOP;
4185 px_header_rec := l_header_rec;
4186 px_line_table := l_line_tbl;
4187 ELSIF (l_header_rec.operation = G_OPR_UPDATE) THEN
4188 -- update requisition line table with new quantity
4189 -- quantity is the only change allowed
4190 FOR i in 1..lx_oe_line_tbl.count LOOP
4191 IF (lx_oe_line_tbl(I).ordered_quantity IS NOT NULL OR
4192 lx_oe_line_tbl(I).ordered_quantity <> FND_API.G_MISS_NUM) THEN
4193 update po_requisition_lines
4194 set quantity = lx_oe_line_tbl(I).ordered_quantity
4195 where requisition_line_id = lx_oe_line_Tbl(I).source_document_line_id;
4196
4197
4198 -- update req distributions
4199 update po_req_distributions
4200 set req_line_quantity = lx_oe_line_tbl(I).ordered_quantity
4201 where requisition_line_id = lx_oe_line_Tbl(I).source_document_line_id;
4202
4203
4204 -- update mtl_supply data for the requisition
4205 IF NOT po_supply.po_req_supply(
4206 p_docid => null,
4207 p_lineid => lx_oe_line_Tbl(I).source_document_line_id,
4208 p_shipid => null,
4209 p_action => 'Update_Req_Line_Qty',
4210 p_recreate_flag => NULL,
4211 p_qty => lx_oe_line_tbl(I).ordered_quantity,
4212 p_receipt_date => NULL) THEN
4213
4214 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
4215 token1 => 'FILE',
4216 value1 => 'PO_SUPPLY',
4217 token2 => 'ERR_NUMBER',
4218 value2 => '035',
4219 token3 => 'SUBROUTINE',
4220 value3 => 'PO_REQ_SUPPLY()');
4221 RAISE FND_API.G_EXC_ERROR;
4222 END IF;
4223 END IF;
4224 END LOOP;
4225 END If;
4226 END IF;
4227 END IF;
4228
4229 px_header_rec := l_header_rec;
4230 px_line_table := l_line_tbl;
4231
4232 fnd_msg_pub.count_and_get
4233 ( p_count => x_msg_count
4234 , p_data => x_msg_data);
4235
4236 END If;
4237
4238 -- Bug 13417397. Setting the change_flag back to NULL
4239 BEGIN
4240 update mtl_supply
4241 set change_flag = NULL
4242 where req_header_id = l_header_rec.requisition_header_id;
4243 EXCEPTION
4244 WHEN no_data_found THEN
4245 null;
4246 END;
4247
4248 -- bug # 12568146
4249 if x_return_status is null then
4250 x_return_status := FND_API.G_RET_STS_SUCCESS;
4251 end if;
4252
4253 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4254 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4255 'csp.plsql.csp_parts_order.process_order',
4256 'process_order API returning with x_return_status = ' || x_return_status);
4257 end if;
4258
4259 EXCEPTION
4260 WHEN FND_API.G_EXC_ERROR THEN
4261 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
4262 P_API_NAME => L_API_NAME
4263 ,P_PKG_NAME => G_PKG_NAME
4264 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4265 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
4266 ,X_MSG_COUNT => X_MSG_COUNT
4267 ,X_MSG_DATA => X_MSG_DATA
4268 ,X_RETURN_STATUS => X_RETURN_STATUS);
4269 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4270 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
4271 P_API_NAME => L_API_NAME
4272 ,P_PKG_NAME => G_PKG_NAME
4273 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4274 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
4275 ,X_MSG_COUNT => X_MSG_COUNT
4276 ,X_MSG_DATA => X_MSG_DATA
4277 ,X_RETURN_STATUS => X_RETURN_STATUS);
4278
4279 WHEN INVALID_CHARGE_ACCOUNT THEN
4280 po_message_s.app_error('PO_RI_INVALID_CHARGE_ACC_ID');
4281 raise;
4282
4283 WHEN INVALID_ACCRUAL_ACCOUNT THEN
4284 po_message_s.app_error('PO_RI_INVALID_ACCRUAL_ACC_ID');
4285 raise;
4286
4287 WHEN INVALID_BUDGET_ACCOUNT THEN
4288 po_message_s.app_error('PO_RI_INVALID_BUDGET_ACC_ID');
4289 raise;
4290
4291 WHEN INVALID_VARIANCE_ACCOUNT THEN
4292 po_message_s.app_error('PO_RI_INVALID_VARIANCE_ACC_ID');
4293 raise;
4294
4295 WHEN OTHERS THEN
4296 Rollback to process_order_pub;
4297 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
4298 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
4299 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
4300 FND_MSG_PUB.ADD;
4301 fnd_msg_pub.count_and_get
4302 ( p_count => x_msg_count
4303 , p_data => x_msg_data);
4304 x_return_status := FND_API.G_RET_STS_ERROR;
4305 END;
4306
4307
4308
4309
4310 /**************************************************************************
4311 ***************************************************************************
4312 ***************************************************************************
4313 PROCESS_PURCHASE_REQUSITION
4314 ***************************************************************************
4315 ***************************************************************************
4316 ***************************************************************************/
4317
4318
4319 PROCEDURE process_purchase_req(
4320 p_api_version IN NUMBER
4321 ,p_Init_Msg_List IN VARCHAR2
4322 ,p_commit IN VARCHAR2
4323 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.header_rec_type
4324 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
4325 ,x_return_status OUT NOCOPY VARCHAR2
4326 ,x_msg_count OUT NOCOPY NUMBER
4327 ,x_msg_data OUT NOCOPY VARCHAR2
4328 )
4329 IS
4330
4331 l_api_version_number CONSTANT NUMBER := 1.0;
4332 l_api_name CONSTANT VARCHAR2(30) := 'process_purchase_req';
4333 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4334 l_msg_count NUMBER;
4335 l_msg_data VARCHAR2(2000);
4336 l_commit VARCHAR2(1) := FND_API.G_FALSE;
4337 l_user_id NUMBER;
4338 l_login_id NUMBER;
4339 l_today DATE;
4340 l_employee_id NUMBER;
4341 l_org_id NUMBER;
4342 l_line_type_id NUMBER;
4343 l_dummy NUMBER;
4344
4345 l_header_rec csp_parts_requirement.header_rec_type;
4346 l_line_rec csp_parts_requirement.line_rec_type;
4347 l_line_tbl csp_parts_requirement.Line_tbl_type;
4348
4349 l_gl_encumbered_date DATE;
4350 l_prevent_encumbrance_flag VARCHAR2(1);
4351 l_chart_of_accounts_id NUMBER;
4352 l_charge_account_id NUMBER;
4353 l_unit_of_measure VARCHAR2(30);
4354 l_justification VARCHAR2(480);
4355 l_note_to_buyer VARCHAR2(480);
4356 l_note1_id NUMBER;
4357 l_note1_title VARCHAR2(80);
4358 l_SUGGESTED_VENDOR_ID NUMBER;
4359 l_SUGGESTED_VENDOR_NAME VARCHAR2(240);
4360 l_source_organization_id NUMBER;
4361 l_autosource_flag VARCHAR2(10);
4362 l_dest_operating_unit number;
4363
4364 l_planner_employee_id NUMBER;
4365
4366 l_VENDOR_ID NUMBER;
4367 l_VENDOR_SITE_ID NUMBER;
4368
4369 EXCP_USER_DEFINED EXCEPTION;
4370 INVALID_CHARGE_ACCOUNT EXCEPTION;
4371
4372 -- Get requisition_number (PO_REQUSITION_HEADERS.segment1)
4373 CURSOR req_number_cur IS
4374 SELECT to_char(current_max_unique_identifier + 1)
4375 FROM po_unique_identifier_control
4376 WHERE table_name = 'PO_REQUISITION_HEADERS'
4377 FOR UPDATE OF current_max_unique_identifier;
4378
4379 -- Get unique requisition_header_id
4380 CURSOR req_header_id_cur IS
4381 SELECT po_requisition_headers_s.nextval
4382 FROM sys.dual;
4383
4384 -- Get unique requisition_line_id
4385 CURSOR req_line_id_cur IS
4386 SELECT po_requisition_lines_s.nextval
4387 FROM sys.dual;
4388
4389 -- Get preparer_id
4390 CURSOR employee_id_cur IS
4391 SELECT employee_id
4392 FROM fnd_user
4393 WHERE user_id = l_user_id;
4394
4395 BEGIN
4396
4397 SAVEPOINT Process_Order_PUB;
4398
4399 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
4400 -- initialize message list
4401 FND_MSG_PUB.initialize;
4402 END IF;
4403
4404 -- Standard call to check for call compatibility.
4405 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
4406 p_api_version,
4407 l_api_name,
4408 G_PKG_NAME)
4409 THEN
4410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4411 END IF;
4412
4413 -- initialize return status
4414 x_return_status := FND_API.G_RET_STS_SUCCESS;
4415
4416 l_header_rec := px_header_rec;
4417 l_line_tbl := px_line_table;
4418
4419 -- Get data for populating who columns
4420 SELECT Sysdate INTO l_today FROM dual;
4421 l_user_id := nvl(fnd_global.user_id, 0) ;
4422 l_login_id := nvl(fnd_global.login_id, -1);
4423
4424 -- operating unit
4425 -- changed for bug 11847583
4426 BEGIN
4427 l_org_id := mo_global.get_current_org_id;
4428
4429 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4430 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csp.plsql.csp_parts_order.process_purchase_req',
4431 'Original l_org_id from context = ' || l_org_id);
4432 end if;
4433
4434 BEGIN
4435 SELECT operating_unit
4436 INTO l_dest_operating_unit
4437 FROM org_organization_Definitions
4438 WHERE organization_id = l_header_rec.dest_organization_id;
4439 EXCEPTION
4440 WHEN NO_DATA_FOUND THEN
4441 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
4442 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_dest_operating_unit', FALSE);
4443 FND_MSG_PUB.ADD;
4444 RAISE EXCP_USER_DEFINED;
4445 END;
4446
4447 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4448 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csp.plsql.csp_parts_order.process_purchase_req',
4449 'l_dest_operating_unit = ' || l_dest_operating_unit);
4450 end if;
4451
4452 if l_dest_operating_unit is not null and l_dest_operating_unit <> nvl(l_org_id, -999) then
4453 l_org_id := l_dest_operating_unit;
4454
4455 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4456 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csp.plsql.csp_parts_order.process_purchase_req',
4457 'l_org_id changed to = ' || l_org_id);
4458 end if;
4459 end if;
4460 po_moac_utils_pvt.set_org_context(l_org_id);
4461 EXCEPTION
4462 WHEN NO_DATA_FOUND THEN
4463 null;
4464 END;
4465
4466 -- requisition_header_id
4467 IF l_header_rec.requisition_header_id is null then
4468 OPEN req_header_id_cur;
4469 FETCH req_header_id_cur into l_header_rec.requisition_header_id;
4470 CLOSE req_header_id_cur;
4471 END IF;
4472
4473 -- Requisition_number
4474 -- IF l_header_rec.requisition_number IS NULL THEN
4475 OPEN req_number_cur;
4476 FETCH req_number_cur INTO l_header_rec.requisition_number;
4477 UPDATE po_unique_identifier_control
4478 SET current_max_unique_identifier
4479 = current_max_unique_identifier + 1
4480 WHERE CURRENT of req_number_cur;
4481 CLOSE req_number_cur;
4482 --END IF;
4483
4484 -- preparer id
4485 IF l_user_id IS NOT NULL THEN
4486 OPEN employee_id_cur;
4487 FETCH employee_id_cur into l_employee_id;
4488 CLOSE employee_id_cur;
4489 END IF;
4490
4491 -- check for uniqueness of requisition_number
4492 BEGIN
4493
4494 SELECT 1 INTO l_dummy
4495 FROM DUAL
4496 WHERE NOT EXISTS
4497 ( SELECT 1
4498 FROM po_requisition_headers
4499 WHERE Segment1 = l_header_rec.requisition_number)
4500 AND NOT EXISTS
4501 ( SELECT 1
4502 FROM po_history_requisitions phr
4503 WHERE phr.segment1 = l_header_rec.requisition_number);
4504
4505 EXCEPTION
4506 WHEN NO_DATA_FOUND THEN
4507 po_message_s.app_error('PO_ALL_ENTER_UNIQUE');
4508 raise;
4509 WHEN OTHERS THEN
4510 po_message_s.sql_error('check_unique','010',sqlcode);
4511 raise;
4512 END;
4513
4514 FND_PROFILE.GET('CSP_PO_LINE_TYPE', l_line_Type_id);
4515
4516 FOR I IN 1..l_line_tbl.COUNT LOOP
4517
4518 -- get requisition_line_id
4519 IF (l_line_tbl(i).Requisition_Line_Id is NULL) THEN
4520 OPEN req_line_id_cur;
4521 FETCH req_line_id_cur INTO l_line_tbl(i).requisition_line_id;
4522 CLOSE req_line_id_cur;
4523 END IF;
4524
4525 -- Assign SYSDATE to gl_encumbered_date
4526 l_gl_encumbered_date := l_today;
4527 l_prevent_encumbrance_flag := 'N';
4528
4529 -- Get Charge Account ID
4530 l_charge_account_id := get_charge_account_fun(l_header_rec.dest_organization_id,
4531 l_line_tbl(i).inventory_item_id,
4532 l_line_tbl(i).dest_subinventory);
4533
4534 BEGIN
4535 SELECT unit_of_measure
4536 INTO l_unit_of_measure
4537 FROM mtl_item_uoms_view
4538 WHERE organization_id = l_header_rec.dest_organization_id
4539 AND inventory_item_id = l_line_Tbl(i).inventory_item_id
4540 AND uom_code = l_line_Tbl(i).unit_of_measure;
4541 EXCEPTION
4542 WHEN NO_DATA_FOUND THEN
4543 l_unit_of_measure := l_line_tbl(i).unit_of_measure;
4544 END;
4545
4546 /*
4547 -- Check for valid charge account. If Invalid Raise ERROR
4548 IF NOT valid_account_id_fun(l_charge_account_id,
4549 l_gl_encumbered_date,
4550 l_chart_of_accounts_id) THEN
4551 Raise INVALID_CHARGE_ACCOUNT;
4552 END IF;
4553 */
4554
4555
4556 If l_header_rec.CALLED_FROM = 'REPAIR_EXECUTION' then
4557 l_justification := l_header_rec.JUSTIFICATION;
4558 l_note_to_buyer := l_header_rec.NOTE_TO_BUYER;
4559 l_note1_id := l_header_rec.note1_id;
4560 l_note1_title := l_header_rec.note1_title;
4561 l_SUGGESTED_VENDOR_ID := l_header_rec.SUGGESTED_VENDOR_ID;
4562 l_SUGGESTED_VENDOR_NAME := l_header_rec.SUGGESTED_VENDOR_NAME;
4563 l_source_organization_id := l_line_tbl(i).source_organization_id;
4564 l_autosource_flag := 'N';
4565 Begin
4566 Select employee_id into l_planner_employee_id From MTL_PLANNERS
4567 Where Organization_id = l_header_rec.dest_organization_id
4568 and Planner_code = ( Select Planner_code from mtl_system_items_b
4569 where inventory_item_id = l_line_tbl(i).inventory_item_id and organization_id =
4570 l_header_rec.dest_organization_id )
4571 and nvl(DISABLE_DATE,SYSDATE+1) > SYSDATE;
4572 Exception
4573 When no_data_found then
4574 l_planner_employee_id := Null;
4575 When others then
4576 l_planner_employee_id := Null;
4577 End;
4578 If l_planner_employee_id is not null then
4579 l_employee_id := l_planner_employee_id;
4580 End if;
4581
4582 Begin
4583 SELECT ORG_INFORMATION3, ORG_INFORMATION4
4584 INTO l_VENDOR_ID, l_VENDOR_SITE_ID
4585 FROM HR_ORGANIZATION_INFORMATION
4586 WHERE ORGANIZATION_ID = l_source_organization_id
4587 and org_information_context = 'Customer/Supplier Association';
4588 Exception
4589 When no_data_found then
4590 l_VENDOR_SITE_ID := Null;
4591 When others then
4592 l_VENDOR_SITE_ID := Null;
4593 End;
4594
4595 Else
4596 l_justification := to_char(l_header_rec.need_by_date, 'DD-MON-RRRR HH:MI:SS');
4597 l_note_to_buyer := l_line_tbl(i).shipping_method_code;
4598 l_note1_id := null;
4599 l_note1_title := null;
4600 l_SUGGESTED_VENDOR_ID := null;
4601 l_SUGGESTED_VENDOR_NAME := null;
4602 l_VENDOR_SITE_ID := null;
4603 l_source_organization_id := null;
4604 l_autosource_flag := null;
4605 End if;
4606
4607
4608 -- Insert into ReqImport Interface tables
4609 INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
4610 (CREATION_DATE,
4611 CREATED_BY,
4612 LAST_UPDATE_DATE,
4613 LAST_UPDATED_BY,
4614 LAST_UPDATE_LOGIN,
4615 INTERFACE_SOURCE_CODE,
4616 --INTERFACE_SOURCE_LINE_ID,
4617 SOURCE_TYPE_CODE,
4618 REQUISITION_TYPE,
4619 DESTINATION_TYPE_CODE,
4620 QUANTITY,
4621 UOM_CODE,
4622 UNIT_OF_MEASURE,
4623 --UNIT_PRICE,
4624 AUTHORIZATION_STATUS,
4625 PREPARER_ID,
4626 ITEM_ID,
4627 CHARGE_ACCOUNT_ID,
4628 DESTINATION_ORGANIZATION_ID,
4629 DESTINATION_SUBINVENTORY,
4630 DELIVER_TO_LOCATION_ID,
4631 DELIVER_TO_REQUESTOR_ID,
4632 NEED_BY_DATE,
4633 ORG_ID,
4634 LINE_TYPE_ID,
4635 REQ_NUMBER_SEGMENT1,
4636 REQUISITION_HEADER_ID,
4637 REQUISITION_LINE_ID,
4638 REFERENCE_NUM,
4639 JUSTIFICATION,
4640 NOTE_TO_BUYER,
4641 --TRANSACTION_REASON_CODE
4642 NOTE1_ID,
4643 NOTE1_TITLE,
4644 SUGGESTED_VENDOR_ID,
4645 SUGGESTED_VENDOR_NAME,
4646 SUGGESTED_VENDOR_SITE_ID,
4647 source_organization_id,
4648 AUTOSOURCE_FLAG
4649 )
4650 VALUES
4651 (l_today, --creation_date
4652 l_user_id, --created_by
4653 l_today, -- last_update_date
4654 l_user_id, -- last_update_login
4655 l_login_id, --last_update_login
4656 'CSP', -- interface_source_code
4657 'VENDOR',
4658 'PURCHASE',
4659 'INVENTORY',
4660 l_line_tbl(i).ordered_quantity,
4661 l_line_tbl(i).unit_of_measure,
4662 l_unit_of_measure,
4663 'APPROVED',
4664 l_employee_id,
4665 l_line_tbl(i).inventory_item_id,
4666 l_charge_Account_id,
4667 l_header_Rec.dest_organization_id,
4668 l_line_tbl(i).dest_subinventory,
4669 l_header_rec.ship_to_location_id,
4670 l_employee_id,
4671 nvl(l_line_tbl(i).need_by_date, l_header_rec.need_by_date),
4672 l_org_id,
4673 l_line_Type_id,
4674 l_header_rec.requisition_number,
4675 l_header_rec.requisition_header_id,
4676 l_line_tbl(i).requisition_line_id,
4677 l_header_rec.requirement_header_id,
4678 l_justification,
4679 l_note_to_buyer,
4680 --'Spares Parts Order'
4681 l_note1_id,
4682 l_note1_title,
4683 l_SUGGESTED_VENDOR_ID,
4684 l_SUGGESTED_VENDOR_NAME,
4685 l_VENDOR_SITE_ID,
4686 l_source_organization_id,
4687 l_autosource_flag
4688 );
4689 END LOOP;
4690
4691 px_header_rec := l_header_rec;
4692 px_line_Table := l_line_Tbl;
4693
4694 IF (p_commit = FND_API.G_TRUE) THEN
4695 commit;
4696 END IF;
4697
4698 fnd_msg_pub.count_and_get( p_count => x_msg_count
4699 , p_data => x_msg_data);
4700
4701 EXCEPTION
4702 WHEN FND_API.G_EXC_ERROR THEN
4703 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
4704 P_API_NAME => L_API_NAME
4705 ,P_PKG_NAME => G_PKG_NAME
4706 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4707 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
4708 ,X_MSG_COUNT => X_MSG_COUNT
4709 ,X_MSG_DATA => X_MSG_DATA
4710 ,X_RETURN_STATUS => X_RETURN_STATUS);
4711 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4712 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
4713 P_API_NAME => L_API_NAME
4714 ,P_PKG_NAME => G_PKG_NAME
4715 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
4716 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
4717 ,X_MSG_COUNT => X_MSG_COUNT
4718 ,X_MSG_DATA => X_MSG_DATA
4719 ,X_RETURN_STATUS => X_RETURN_STATUS);
4720
4721 WHEN INVALID_CHARGE_ACCOUNT THEN
4722 po_message_s.app_error('PO_RI_INVALID_CHARGE_ACC_ID');
4723 raise;
4724
4725 WHEN OTHERS THEN
4726 Rollback to process_order_pub;
4727 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
4728 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
4729 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
4730 FND_MSG_PUB.ADD;
4731 fnd_msg_pub.count_and_get
4732 ( p_count => x_msg_count
4733 , p_data => x_msg_data);
4734 x_return_status := FND_API.G_RET_STS_ERROR;
4735 END;
4736
4737 PROCEDURE book_order (
4738 p_oe_header_id IN NUMBER
4739 ,x_return_status OUT NOCOPY VARCHAR2
4740 ,x_msg_count OUT NOCOPY NUMBER
4741 ,x_msg_data OUT NOCOPY VARCHAR2
4742 ) IS
4743 l_module_name varchar2(100) := 'csp.plsql.csp_parts_order.book_order';
4744 l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
4745 l_org_id number;
4746 l_org_org_id number;
4747
4748 l_header_rec OE_ORDER_PUB.Header_Rec_Type;
4749 l_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
4750 l_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
4751 l_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
4752 l_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
4753 l_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
4754 l_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
4755 l_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
4756 l_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
4757 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
4758 l_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
4759 l_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
4760 l_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
4761 l_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
4762 l_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
4763 l_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
4764 l_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
4765 l_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
4766 l_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
4767 l_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
4768 lx_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
4769
4770 begin
4771
4772 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4773 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4774 'Begin...');
4775 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4776 'p_oe_header_id = ' || p_oe_header_id);
4777 end if;
4778
4779 select org_id into l_org_id from oe_order_headers_all where header_id = p_oe_header_id;
4780 l_org_org_id := mo_global.get_current_org_id;
4781
4782 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4783 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4784 'l_org_id = ' || l_org_id);
4785 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4786 'l_org_org_id = ' || l_org_org_id);
4787 end if;
4788
4789 if l_org_org_id is null then
4790 po_moac_utils_pvt.INITIALIZE;
4791 l_org_org_id := mo_global.get_current_org_id;
4792 end if;
4793
4794 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4795 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4796 'l_org_id = ' || l_org_id);
4797 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4798 'l_org_org_id = ' || l_org_org_id);
4799 end if;
4800
4801 if l_org_id <> nvl(l_org_org_id, -999) and l_org_id is not null then
4802 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4803 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4804 'changing context to l_org_id = ' || l_org_id);
4805 end if;
4806 po_moac_utils_pvt.set_org_context(l_org_id);
4807 end if;
4808
4809 l_action_request_tbl := OE_ORDER_PUB.G_MISS_REQUEST_TBL;
4810 l_action_request_tbl(1).request_type := oe_globals.g_book_order;
4811 l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
4812 l_action_request_tbl(1).entity_id := p_oe_header_id;
4813
4814 OE_ORDER_PUB.process_order(
4815 p_api_version_number => 1.0,
4816 p_org_id => l_org_id,
4817 p_init_msg_list => FND_API.G_TRUE,
4818 p_return_values => FND_API.G_FALSE,
4819 p_action_commit => FND_API.G_FALSE,
4820 x_return_status => x_return_status,
4821 x_msg_count => x_msg_count,
4822 x_msg_data => x_msg_data,
4823 p_action_request_tbl => l_action_request_tbl,
4824 -- OUT parameters
4825 x_header_rec => l_header_rec,
4826 x_header_val_rec => l_header_val_rec,
4827 x_Header_Adj_tbl => l_Header_Adj_tbl,
4828 x_Header_Adj_val_tbl => l_Header_Adj_val_tbl,
4829 x_Header_price_Att_tbl => l_Header_price_Att_tbl,
4830 x_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl,
4831 x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl,
4832 x_Header_Scredit_tbl => l_Header_Scredit_tbl,
4833 x_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl,
4834 x_line_tbl => l_line_tbl,
4835 x_line_val_tbl => l_line_val_tbl,
4836 x_Line_Adj_tbl => l_Line_Adj_tbl,
4837 x_Line_Adj_val_tbl => l_Line_Adj_val_tbl,
4838 x_Line_price_Att_tbl => l_Line_price_Att_tbl,
4839 x_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl,
4840 x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl,
4841 x_Line_Scredit_tbl => l_Line_Scredit_tbl,
4842 x_Line_Scredit_val_tbl => l_Line_Scredit_val_tbl,
4843 x_Lot_Serial_tbl => l_Lot_Serial_tbl,
4844 x_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl,
4845 x_action_request_tbl => lx_action_request_tbl
4846 );
4847
4848 if l_org_id <> l_org_org_id then
4849 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4850 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4851 'changing context to l_org_org_id = ' || l_org_org_id);
4852 end if;
4853 po_moac_utils_pvt.set_org_context(l_org_org_id);
4854 end if;
4855
4856 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4857 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4858 'After calling OE_ORDER_PUB.process_order ...');
4859 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4860 'x_return_status = ' || x_return_status);
4861 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4862 'x_msg_count = ' || x_msg_count);
4863 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4864 'x_msg_data = ' || x_msg_data);
4865 end if;
4866
4867 if x_return_status = FND_API.G_RET_STS_SUCCESS then
4868 commit;
4869 end if;
4870
4871 end;
4872
4873 PROCEDURE upd_oe_line_ship_method (
4874 p_oe_line_id IN NUMBER
4875 ,p_ship_method IN VARCHAR2
4876 ,x_return_status OUT NOCOPY VARCHAR2
4877 ,x_msg_count OUT NOCOPY NUMBER
4878 ,x_msg_data OUT NOCOPY VARCHAR2
4879 ) IS
4880 l_module_name varchar2(100) := 'csp.plsql.csp_parts_order.upd_oe_line_ship_method';
4881 l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
4882 l_org_id number;
4883 l_org_org_id number;
4884 l_ship_from_org_id number;
4885 l_arrival_date date;
4886
4887 l_header_rec OE_ORDER_PUB.Header_Rec_Type;
4888 l_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
4889 l_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
4890 l_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
4891 l_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
4892 l_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
4893 l_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
4894 l_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
4895 l_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
4896 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
4897 l_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
4898 l_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
4899 l_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
4900 l_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
4901 l_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
4902 l_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
4903 l_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
4904 l_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
4905 l_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
4906 l_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
4907 lx_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
4908
4909 l_line_tbl1 OE_ORDER_PUB.Line_Tbl_Type;
4910
4911 cursor c_get_arrival_date is
4912 SELECT csp.arrival_date
4913 FROM CSP_SHIPPING_DETAILS_V csp,
4914 csp_requirement_headers ch,
4915 csp_requirement_lines cl,
4916 csp_req_line_details cld
4917 WHERE csp.organization_id = l_ship_from_org_id
4918 AND csp.shipping_method = p_ship_method
4919 AND cld.source_id = p_oe_line_id
4920 AND cld.requirement_line_id = cl.requirement_line_id
4921 AND cl.requirement_header_id = ch.requirement_header_id
4922 AND csp.to_location_id = ch.ship_to_location_id
4923 AND csp.location_source = 'HR';
4924
4925 BEGIN
4926
4927 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4928 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4929 'Begin...');
4930 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4931 'p_oe_line_id = ' || p_oe_line_id
4932 || ', p_ship_method = ' || p_ship_method);
4933 end if;
4934
4935 select org_id into l_org_id from oe_order_lines_all where line_id = p_oe_line_id;
4936 l_org_org_id := mo_global.get_current_org_id;
4937
4938 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4939 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4940 'l_org_id = ' || l_org_id);
4941 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4942 'l_org_org_id = ' || l_org_org_id);
4943 end if;
4944
4945 if l_org_org_id is null then
4946 po_moac_utils_pvt.INITIALIZE;
4947 l_org_org_id := mo_global.get_current_org_id;
4948 end if;
4949
4950 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4951 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4952 'l_org_id = ' || l_org_id);
4953 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4954 'l_org_org_id = ' || l_org_org_id);
4955 end if;
4956
4957 if l_org_id <> nvl(l_org_org_id, -999) and l_org_id is not null then
4958 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4959 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4960 'changing context to l_org_id = ' || l_org_id);
4961 end if;
4962 po_moac_utils_pvt.set_org_context(l_org_id);
4963 end if;
4964
4965 l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
4966 l_line_tbl(1).line_id := p_oe_line_id;
4967 l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
4968 l_line_tbl(1).shipping_method_code := p_ship_method;
4969
4970 -- bug # 12664116
4971 select ship_from_org_id into l_ship_from_org_id from oe_order_lines_all where line_id = p_oe_line_id;
4972
4973 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4974 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4975 'l_ship_from_org_id = ' || l_ship_from_org_id);
4976 end if;
4977
4978 open c_get_arrival_date;
4979 fetch c_get_arrival_date into l_arrival_date;
4980 close c_get_arrival_date;
4981
4982 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4983 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4984 'l_arrival_date = ' || to_char(nvl(l_arrival_date, sysdate), 'DD-MON-YYYY HH24:MI:SS'));
4985 end if;
4986
4987 if l_arrival_date is not null then
4988 l_line_tbl(1).actual_arrival_date := l_arrival_date;
4989 l_line_tbl(1).promise_date := l_arrival_date;
4990 --l_line_tbl(1).request_date := l_arrival_date;
4991 end if;
4992
4993
4994 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
4995 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
4996 'Starting OW Debug...');
4997
4998 oe_debug_pub.G_FILE := NULL;
4999 oe_debug_pub.debug_on;
5000 oe_debug_pub.initialize;
5001 oe_debug_pub.setdebuglevel(5);
5002
5003 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5004 'OE Debug File : '|| OE_DEBUG_PUB.Set_Debug_Mode('FILE'));
5005
5006 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5007 'l_line_tbl.count = '|| l_line_tbl.count);
5008 end if;
5009
5010 OE_ORDER_PUB.process_order(
5011 p_api_version_number => 1.0,
5012 p_org_id => l_org_id,
5013 p_init_msg_list => FND_API.G_TRUE,
5014 p_return_values => FND_API.G_FALSE,
5015 p_action_commit => FND_API.G_FALSE,
5016 x_return_status => x_return_status,
5017 x_msg_count => x_msg_count,
5018 x_msg_data => x_msg_data,
5019 p_action_request_tbl => l_action_request_tbl,
5020 p_line_tbl => l_line_tbl,
5021 p_header_rec => l_header_rec,
5022 -- OUT parameters
5023 x_header_rec => l_header_rec,
5024 x_header_val_rec => l_header_val_rec,
5025 x_Header_Adj_tbl => l_Header_Adj_tbl,
5026 x_Header_Adj_val_tbl => l_Header_Adj_val_tbl,
5027 x_Header_price_Att_tbl => l_Header_price_Att_tbl,
5028 x_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl,
5029 x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl,
5030 x_Header_Scredit_tbl => l_Header_Scredit_tbl,
5031 x_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl,
5032 x_line_tbl => l_line_tbl1,
5033 x_line_val_tbl => l_line_val_tbl,
5034 x_Line_Adj_tbl => l_Line_Adj_tbl,
5035 x_Line_Adj_val_tbl => l_Line_Adj_val_tbl,
5036 x_Line_price_Att_tbl => l_Line_price_Att_tbl,
5037 x_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl,
5038 x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl,
5039 x_Line_Scredit_tbl => l_Line_Scredit_tbl,
5040 x_Line_Scredit_val_tbl => l_Line_Scredit_val_tbl,
5041 x_Lot_Serial_tbl => l_Lot_Serial_tbl,
5042 x_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl,
5043 x_action_request_tbl => lx_action_request_tbl
5044 );
5045
5046 if l_org_id <> l_org_org_id then
5047 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5048 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5049 'changing context to l_org_org_id = ' || l_org_org_id);
5050 end if;
5051 po_moac_utils_pvt.set_org_context(l_org_org_id);
5052 end if;
5053
5054 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5055 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5056 'After calling OE_ORDER_PUB.process_order ...');
5057 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5058 'x_return_status = ' || x_return_status);
5059 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5060 'x_msg_count = ' || x_msg_count);
5061 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5062 'x_msg_data = ' || x_msg_data);
5063
5064 -- Stopping OE Debug...
5065 oe_debug_pub.debug_off;
5066 end if;
5067
5068 if x_return_status = FND_API.G_RET_STS_SUCCESS then
5069 commit;
5070 end if;
5071
5072 END;
5073
5074 PROCEDURE upd_oe_ship_to_add (
5075 p_req_header_id IN NUMBER
5076 ,p_new_hr_loc_id IN NUMBER
5077 ,p_new_add_type IN VARCHAR2
5078 ,p_update_req_header IN VARCHAR2
5079 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5080 ,x_return_status OUT NOCOPY VARCHAR2
5081 ,x_msg_count OUT NOCOPY NUMBER
5082 ,x_msg_data OUT NOCOPY VARCHAR2
5083 ) IS
5084 l_module_name varchar2(100) := 'csp.plsql.csp_parts_order.upd_oe_ship_to_add';
5085 l_org_id number;
5086 l_org_org_id number;
5087 l_booked_orders number;
5088 l_header_rec csp_parts_requirement.header_rec_type;
5089 l_header_can_rec csp_parts_requirement.header_rec_type;
5090 l_lines_tbl csp_parts_requirement.Line_Tbl_type;
5091 l_oe_header_rec oe_order_pub.header_rec_type := OE_ORDER_PUB.G_MISS_HEADER_REC;
5092 l_oe_line_tbl OE_Order_PUB.line_tbl_type;
5093 l_dest_org_id number;
5094 l_dest_subinv varchar2(40);
5095 l_need_by_date date;
5096 l_req_line_id number;
5097 l_req_line_dtl_rec CSP_REQ_LINE_DETAILS_PVT.Req_Line_Details_Tbl_Type;
5098 l_req_line_dtl_id number;
5099 l_req_hdr_pvt_rec CSP_Requirement_headers_PVT.REQUIREMENT_HEADER_Rec_Type;
5100 l_cancel_reason varchar2(200);
5101
5102 cursor check_io_status is
5103 SELECT count(1)
5104 FROM csp_requirement_lines cl,
5105 csp_req_line_details cld,
5106 oe_order_lines_all oel
5107 WHERE cl.requirement_header_id = p_req_header_id
5108 AND cl.requirement_line_id = cld.requirement_line_id
5109 AND cld.source_type = 'IO'
5110 AND cld.source_id = oel.line_id
5111 AND oel.booked_flag = 'Y';
5112
5113 cursor get_booked_order_num is
5114 SELECT oeh.order_number
5115 FROM csp_requirement_lines cl,
5116 csp_req_line_details cld,
5117 oe_order_lines_all oel,
5118 oe_order_headers_all oeh
5119 WHERE cl.requirement_header_id = p_req_header_id
5120 AND cl.requirement_line_id = cld.requirement_line_id
5121 AND cld.source_type = 'IO'
5122 AND cld.source_id = oel.line_id
5123 AND oel.booked_flag = 'Y'
5124 AND oel.header_id = oeh.header_id
5125 AND rownum = 1;
5126
5127 cursor get_order_header_ids is
5128 SELECT distinct oel.header_id
5129 FROM csp_requirement_lines cl,
5130 csp_req_line_details cld,
5131 oe_order_lines_all oel
5132 WHERE cl.requirement_header_id = p_req_header_id
5133 AND cl.requirement_line_id = cld.requirement_line_id
5134 AND cld.source_type = 'IO'
5135 AND cld.source_id = oel.line_id
5136 AND oel.booked_flag = 'N'
5137 AND oel.cancelled_flag = 'N'
5138 AND oel.open_flag = 'Y';
5139
5140 cursor get_req_header_data is
5141 SELECT destination_organization_id,
5142 destination_subinventory,
5143 need_by_date
5144 FROM csp_requirement_headers
5145 WHERE requirement_header_id = p_req_header_id;
5146
5147 cursor get_req_line_id (v_oe_line_id number) is
5148 SELECT requirement_line_id,
5149 req_line_detail_id
5150 FROM csp_req_line_details
5151 WHERE source_type= 'IO'
5152 AND source_id = v_oe_line_id;
5153
5154 BEGIN
5155 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5156 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5157 'Begin...');
5158 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5159 'p_req_header_id = ' || p_req_header_id
5160 || ', p_new_hr_loc_id = ' || p_new_hr_loc_id
5161 || ', p_update_req_header = ' || p_update_req_header
5162 || ', p_new_add_type = ' || p_new_add_type);
5163 end if;
5164
5165 l_booked_orders := 0;
5166 open check_io_status;
5167 fetch check_io_status into l_booked_orders;
5168 close check_io_status;
5169
5170 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5171 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5172 'l_booked_orders = ' || l_booked_orders);
5173 end if;
5174
5175 if l_booked_orders > 0 then
5176 open get_booked_order_num;
5177 fetch get_booked_order_num into l_booked_orders;
5178 close get_booked_order_num;
5179
5180 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5181 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5182 'Raising error as l_booked_orders > 0 ');
5183 end if;
5184
5185 FND_MESSAGE.SET_NAME('CSP', 'CSP_NO-SHIP_CNG_BOOK_IO');
5186 FND_MESSAGE.SET_TOKEN('ORDER_NUM', l_booked_orders, FALSE);
5187 FND_MSG_PUB.ADD;
5188 fnd_msg_pub.count_and_get
5189 ( p_count => x_msg_count
5190 , p_data => x_msg_data);
5191 x_return_status := FND_API.G_RET_STS_ERROR;
5192
5193 else -- we will do stuff here
5194
5195 -- logic in brief
5196 -- first cancel all the orders
5197 -- create orders again with new ship_to_address
5198 -- while creating orders make sure you create order per operating unit
5199
5200 savepoint csp_upd_oe_ship_to_add;
5201
5202 open get_req_header_data;
5203 fetch get_req_header_data into l_dest_org_id, l_dest_subinv, l_need_by_date;
5204 close get_req_header_data;
5205
5206 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5207 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5208 'Fetched data from req header... ');
5209 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5210 'l_dest_org_id = ' || l_dest_org_id
5211 || ', l_dest_subinv = ' || l_dest_subinv
5212 || ', l_need_by_date = ' || l_need_by_date);
5213 end if;
5214
5215 -- lets get orders which we need to cancel
5216
5217 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5218 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5219 'fetching orders to be cancelled...');
5220 end if;
5221
5222 for cr in get_order_header_ids loop
5223 l_header_can_rec.order_header_id := cr.header_id;
5224
5225 -- bug # 12559884
5226 -- put Cancel reason code for cancelling a line
5227 -- value will be picked from the profile CSP_CANCEL_REASON
5228 fnd_profile.get('CSP_CANCEL_REASON', l_cancel_reason);
5229 l_header_can_rec.change_reason := l_cancel_reason;
5230
5231 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5232 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5233 'processing l_header_can_rec.order_header_id = ' || l_header_can_rec.order_header_id);
5234 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5235 'l_header_can_rec.change_reason = ' || l_header_can_rec.change_reason);
5236 end if;
5237
5238 oe_header_util.Query_Row(
5239 p_header_id => cr.header_id,
5240 x_header_rec => l_oe_header_rec);
5241
5242 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5243 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5244 'fetched l_oe_header_rec...');
5245 end if;
5246
5247 l_header_rec.dest_organization_id := l_dest_org_id;
5248 l_header_rec.dest_subinventory := l_dest_subinv;
5249 l_header_rec.need_by_date := l_need_by_date;
5250 l_header_rec.ship_to_location_id := p_new_hr_loc_id; -- important
5251 l_header_rec.address_type := p_new_add_type; -- important
5252 l_header_rec.requirement_header_id := p_req_header_id;
5253 l_header_rec.operation := 'CREATE';
5254 l_header_rec.shipping_method_code := l_oe_header_rec.shipping_method_code;
5255 l_header_rec.order_type_id := l_oe_header_rec.order_type_id;
5256
5257 oe_line_util.Query_Rows(
5258 p_header_id => cr.header_id,
5259 x_line_tbl => l_oe_line_tbl);
5260
5261 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5262 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5263 'fetched l_oe_line_tbl...');
5264 end if;
5265
5266 l_req_line_dtl_rec := CSP_REQ_LINE_DETAILS_PVT.G_MISS_Req_Line_Details_TBL;
5267 for i in 1..l_oe_line_tbl.count loop
5268
5269 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5270 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5271 'processing line # ' || i
5272 || ' for item_id = ' || l_oe_line_tbl(i).inventory_item_id);
5273 end if;
5274
5275 l_lines_tbl(i).booked_flag := 'Y';
5276 l_lines_tbl(i).dest_subinventory := l_dest_subinv;
5277 l_lines_tbl(i).inventory_item_id := l_oe_line_tbl(i).inventory_item_id;
5278 l_lines_tbl(i).need_by_date := l_need_by_date;
5279 l_lines_tbl(i).ordered_quantity := l_oe_line_tbl(i).ordered_quantity;
5280 l_lines_tbl(i).revision := l_oe_line_tbl(i).item_revision;
5281 l_lines_tbl(i).shipping_method_code := l_oe_line_tbl(i).shipping_method_code;
5282 l_lines_tbl(i).source_organization_id := l_oe_line_tbl(i).ship_from_org_id;
5283 l_lines_tbl(i).source_subinventory := l_oe_line_tbl(i).subinventory;
5284 l_lines_tbl(i).unit_of_measure := l_oe_line_tbl(i).order_quantity_uom;
5285 --l_lines_tbl(i).arrival_date := l_oe_line_tbl(i).request_date;
5286 l_lines_tbl(i).line_num := i;
5287
5288 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5289 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5290 'l_oe_line_tbl(i).line_id = ' || l_oe_line_tbl(i).line_id);
5291 end if;
5292
5293 open get_req_line_id(l_oe_line_tbl(i).line_id);
5294 fetch get_req_line_id into l_req_line_id, l_req_line_dtl_id;
5295 close get_req_line_id;
5296
5297 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5298 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5299 'l_req_line_id = ' || l_req_line_id);
5300 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5301 'l_req_line_dtl_id = ' || l_req_line_dtl_id);
5302 end if;
5303
5304 l_lines_tbl(i).requirement_line_id := l_req_line_id;
5305
5306 l_req_line_dtl_rec(i).REQ_LINE_DETAIL_ID := l_req_line_dtl_id;
5307 l_req_line_dtl_rec(i).REQUIREMENT_LINE_ID := l_req_line_id;
5308 l_req_line_dtl_rec(i).SOURCE_TYPE := 'IO';
5309 end loop;
5310
5311 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5312 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5313 'Before trying to cancel the order...');
5314 end if;
5315
5316 Cancel_Order(
5317 p_header_rec => l_header_can_rec,
5318 p_line_table => l_lines_tbl,
5319 p_process_Type => 'ORDER',
5320 x_return_status => x_return_status,
5321 x_msg_count => x_msg_count,
5322 x_msg_data => x_msg_data
5323 );
5324
5325 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5326 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5327 'after cancel the order... x_return_status = ' || x_return_status);
5328 end if;
5329
5330 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
5331 rollback to csp_upd_oe_ship_to_add;
5332
5333 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5334 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5335 'x_return_status = ' || x_return_status);
5336 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5337 'x_msg_count = ' || x_msg_count);
5338 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5339 'x_msg_data = ' || x_msg_data);
5340 end if;
5341
5342 else
5343 -- try to create the order :)
5344 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5345 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5346 'before calling process_order...');
5347 end if;
5348
5349 process_order(
5350 p_api_version => 1.0
5351 ,p_Init_Msg_List => FND_API.G_TRUE
5352 ,p_commit => FND_API.G_FALSE
5353 ,px_header_rec => l_header_rec
5354 ,px_line_table => l_lines_tbl
5355 ,p_process_type => 'BOTH'
5356 ,p_book_order => 'N'
5357 ,x_return_status => x_return_status
5358 ,x_msg_count => x_msg_count
5359 ,x_msg_data => x_msg_data
5360 );
5361
5362 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5363 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5364 'after calling process_order... x_return_status = ' || x_return_status);
5365 end if;
5366
5367 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
5368 rollback to csp_upd_oe_ship_to_add;
5369
5370 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5371 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5372 'x_return_status = ' || x_return_status);
5373 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5374 'x_msg_count = ' || x_msg_count);
5375 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5376 'x_msg_data = ' || x_msg_data);
5377 end if;
5378
5379 else
5380 -- update the csp_req_line_details
5381 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5382 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5383 'populating l_req_line_dtl_rec...');
5384 end if;
5385
5386 for i in 1..l_lines_tbl.count loop
5387 for j in 1..l_req_line_dtl_rec.count loop
5388 if l_req_line_dtl_rec(j).REQUIREMENT_LINE_ID = l_lines_tbl(i).requirement_line_id then
5389 l_req_line_dtl_rec(j).SOURCE_ID := l_lines_tbl(i).order_line_id;
5390
5391 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5392 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5393 'new SOURCE_ID for REQUIREMENT_LINE_ID is = '
5394 || l_req_line_dtl_rec(j).REQUIREMENT_LINE_ID
5395 || ' -> ' || l_req_line_dtl_rec(j).SOURCE_ID);
5396 end if;
5397
5398 end if;
5399 end loop;
5400 end loop;
5401
5402 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5403 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5404 'before calling update req line details...');
5405 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5406 'l_req_line_dtl_rec.count = ' || l_req_line_dtl_rec.count);
5407 end if;
5408
5409 CSP_REQ_LINE_DETAILS_PVT.Update_req_line_details(
5410 P_Api_Version_Number => 1.0,
5411 P_Req_Line_Details_Tbl => l_req_line_dtl_rec,
5412 X_Return_Status => x_return_status,
5413 X_Msg_Count => x_msg_count,
5414 X_Msg_Data => x_msg_data
5415 );
5416
5417 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5418 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5419 'after CSP_REQ_LINE_DETAILS_PVT.Update_req_line_details... x_return_status = ' || x_return_status);
5420 end if;
5421
5422 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
5423 rollback to csp_upd_oe_ship_to_add;
5424
5425 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5426 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5427 'x_return_status = ' || x_return_status);
5428 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5429 'x_msg_count = ' || x_msg_count);
5430 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5431 'x_msg_data = ' || x_msg_data);
5432 end if;
5433 end if;
5434
5435 end if;
5436 end if;
5437
5438 end loop;
5439
5440 if x_return_status = FND_API.G_RET_STS_SUCCESS then
5441
5442 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5443 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5444 'p_update_req_header = ' || p_update_req_header);
5445 end if;
5446
5447 if nvl(p_update_req_header, 'N') = 'Y' then
5448 -- update req header with new ship_to_location_id
5449 l_req_hdr_pvt_rec.REQUIREMENT_HEADER_ID := p_req_header_id;
5450 l_req_hdr_pvt_rec.SHIP_TO_LOCATION_ID := p_new_hr_loc_id;
5451 l_req_hdr_pvt_rec.ADDRESS_TYPE := p_new_add_type;
5452
5453 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5454 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5455 'before calling CSP_Requirement_headers_PVT.Update_requirement_headers...');
5456 end if;
5457
5458 CSP_Requirement_headers_PVT.Update_requirement_headers(
5459 P_Api_Version_Number => 1.0,
5460 P_REQUIREMENT_HEADER_Rec => l_req_hdr_pvt_rec,
5461 X_Return_Status => x_return_status,
5462 X_Msg_Count => x_msg_count,
5463 X_Msg_Data => x_msg_data
5464 );
5465
5466 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5467 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5468 'after calling CSP_Requirement_headers_PVT.Update_requirement_headers... x_return_status = ' || x_return_status);
5469 end if;
5470
5471 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
5472 rollback to csp_upd_oe_ship_to_add;
5473
5474 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5475 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5476 'x_return_status = ' || x_return_status);
5477 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5478 'x_msg_count = ' || x_msg_count);
5479 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5480 'x_msg_data = ' || x_msg_data);
5481 end if;
5482 else
5483 if p_commit = FND_API.G_TRUE then
5484 commit;
5485 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5486 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5487 'transaction commited...');
5488 end if;
5489 end if;
5490 end if;
5491 end if;
5492 end if;
5493 end if;
5494
5495 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
5496 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module_name,
5497 'returning...');
5498 end if;
5499 END;
5500
5501 END;