[Home] [Help]
PACKAGE BODY: APPS.CSP_PARTS_ORDER
Source
1 PACKAGE BODY CSP_PARTS_ORDER AS
2 /* $Header: cspvpodb.pls 120.17.12010000.6 2008/12/24 07:40:59 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
414
411 CLOSE validate_ccid_cur;
412 return FALSE;
413 END IF;
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 CURSOR get_new_context(p_new_org_id number) IS
474 SELECT org_information2 ,
475 org_information3 ,
476 org_information4
477 FROM hr_organization_information hou
478 WHERE hou.organization_id = p_new_org_id
479 AND hou.org_information1 = 'FIELD_SERVICE'
480 AND hou.org_information_context = 'CS_USER_CONTEXT';
481
482 orig_org_id number;
483 orig_user_id number;
484 orig_resp_id number;
485 orig_resp_appl_id number;
486 new_org_id number;
487 new_user_id number;
488 new_resp_id number;
489 new_resp_appl_id number;
490 l_source_operating_unit number;
491 l_org_id number;
492 l_user_id number;
493
494 BEGIN
495
496 SAVEPOINT Cancel_Order_PUB;
497
498 -- initialize return status
499 x_return_status := FND_API.G_RET_STS_SUCCESS;
500
501 l_user_id := nvl(fnd_global.user_id, 0) ;
502 fnd_profile.get('RESP_ID',orig_resp_id);
503 fnd_profile.get('RESP_APPL_ID',orig_resp_appl_id);
504
505 BEGIN
506 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
507 INTO l_org_id
508 FROM dual;
509 po_moac_utils_pvt.set_org_context(l_org_id);
510 EXCEPTION
511 WHEN NO_DATA_FOUND THEN
512 RAISE NO_DATA_FOUND;
513 END;
514
515 l_oe_header_id := p_header_rec.order_header_id;
516 l_line_tbl := p_line_table;
517
518 IF (p_process_type = 'REQUISITION') THEN
519 FOR I in 1..l_line_tbl.count LOOP
520 update po_requisition_lines
521 set quantity_cancelled = l_line_Tbl(I).quantity,
522 cancel_flag = 'Y',
523 cancel_reason = l_line_tbl(I).change_reason,
524 cancel_date = sysdate
525 where requisition_line_id = l_line_tbl(I).requisition_line_id;
526
527 -- update mtl_supply data for the requisition
531 p_shipid => null,
528 IF NOT po_supply.po_req_supply(
529 p_docid => null,
530 p_lineid => l_line_Tbl(I).requisition_line_id,
532 p_action => 'Remove_Req_Line_Supply',
533 p_recreate_flag => NULL,
534 p_qty => NULL,
535 p_receipt_date => NULL) THEN
536
537 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
538 token1 => 'FILE',
539 value1 => 'PO_SUPPLY',
540 token2 => 'ERR_NUMBER',
541 value2 => '035',
542 token3 => 'SUBROUTINE',
543 value3 => 'PO_REQ_SUPPLY()');
544 RAISE FND_API.G_EXC_ERROR;
545 END IF;
546 END LOOP;
547 ELSE
548 IF(l_oe_header_id IS NOT NULL) THEN
549
550 -- source operating unit
551 BEGIN
552 SELECT org_id
553 INTO l_source_operating_unit
554 FROM OE_ORDER_HEADERS_ALL
555 WHERE header_id = l_oe_header_id;
556 EXCEPTION
557 WHEN NO_DATA_FOUND THEN
558 RAISE NO_DATA_FOUND;
559 END;
560
561 IF (l_source_operating_unit <> l_org_id) THEN
562 OPEN get_new_context(l_source_operating_unit);
563 FETCH get_new_context
564 INTO new_user_id,new_resp_id,new_resp_appl_id;
565 CLOSE get_new_context;
566
567 IF new_resp_id is not null and
568 new_resp_appl_id is not null THEN
569 fnd_global.apps_initialize(new_user_id,new_resp_id,new_resp_appl_id);
570 ELSE
571 dbms_application_info.set_client_info(l_source_operating_unit);
572 END IF;
573 END If;
574
575 oe_header_util.Query_Row(
576 p_header_id => l_oe_header_id,
577 x_header_rec => l_oe_header_rec);
578
579 /* l_oe_header_rec.cancelled_flag := 'Y';
580 l_oe_header_rec.flow_status_code := 'CANCELLED';
581 l_oe_header_rec.open_flag := 'N';
582 l_oe_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
583 l_oe_header_rec.change_reason := p_header_rec.change_reason;
584 l_oe_header_rec.change_comments := p_header_rec.change_comments;
585 */
586 oe_line_util.Query_Rows
587 (p_header_id => l_oe_header_id,
588 x_line_tbl => l_oe_line_old_tbl
589 );
590
591 For I in 1 .. l_oe_line_old_tbl.count LOOP
592 IF nvl(l_oe_line_old_tbl(i).shipped_quantity,0) = 0 AND
593 Nvl(l_oe_line_old_tbl(i).cancelled_flag,'N') <> 'Y' AND
594 Nvl(l_oe_line_old_tbl(i).ordered_quantity,0) <> 0 THEN
595 J := J + 1;
596 l_oe_line_tbl(J) := l_oe_line_old_tbl(I);
597 l_oe_line_tbl(J).db_flag := FND_API.G_TRUE;
598 l_oe_line_tbl(J).cancelled_quantity := l_oe_line_old_tbl(J).ordered_quantity;
599
600 l_oe_line_tbl(j).ordered_quantity :=0;
601 l_oe_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
602 l_oe_line_tbl(j).change_reason := p_header_rec.change_reason;
603
604
605 l_oe_line_tbl(j).change_comments := p_header_Rec.change_comments;
606
607 l_oe_line_tbl(j).cancelled_flag := 'Y';
608 l_oe_line_tbl(j).flow_status_code := 'CANCELLED';
609 l_oe_line_tbl(j).source_document_line_id := l_oe_line_old_tbl(J).source_document_line_id;
610
611
612 l_oe_line_tbl(j).open_flag := 'N';
613 End If;
614 end loop;
615
616 ELSE -- IF (l_oe_header_id IS NULL) THEN
617
618 FOR I in 1..l_line_tbl.count LOOP
619 BEGIN
620 SELECT org_id
621 INTO l_source_operating_unit
622 FROM oe_order_lines_all
623 WHERE line_id = l_line_tbl(i).order_line_id;
624 EXCEPTION
625 WHEN NO_DATA_FOUND THEN
626 RAISE NO_DATA_FOUND;
627 END;
628 IF (l_source_operating_unit <> l_org_id) THEN
629 OPEN get_new_context(l_source_operating_unit);
630 FETCH get_new_context
631 INTO new_user_id,new_resp_id,new_resp_appl_id;
632 CLOSE get_new_context;
633
634 IF new_resp_id is not null and
635 new_resp_appl_id is not null THEN
636 fnd_global.apps_initialize(new_user_id,new_resp_id,new_resp_appl_id);
637 ELSE
638 dbms_application_info.set_client_info(l_source_operating_unit);
639 END IF;
640 END If;
641
642 l_oe_line_old_tbl(i) := oe_line_util.Query_Row(l_line_tbl(i).order_line_id);
643
644
645 IF nvl(l_oe_line_old_tbl(i).shipped_quantity,0) = 0 AND
646 Nvl(l_oe_line_old_tbl(i).cancelled_flag,'N') <> 'Y' AND
650 l_oe_line_tbl(J).db_flag := FND_API.G_TRUE;
647 Nvl(l_oe_line_old_tbl(i).ordered_quantity,0) <> 0 THEN
648 J := J + 1;
649 l_oe_line_tbl(J) := l_oe_line_old_tbl(I);
651 l_oe_line_tbl(J).cancelled_quantity := l_oe_line_tbl(J).ordered_quantity;
652
653 l_oe_line_tbl(j).ordered_quantity :=0;
654 l_oe_line_tbl(j).operation := OE_GLOBALS.G_OPR_UPDATE;
655 l_oe_line_tbl(j).change_reason := p_line_table(i).change_reason;
656
657 l_oe_line_tbl(j).change_comments := p_line_table(i).change_comments;
658
659 l_oe_line_tbl(j).cancelled_flag := 'Y';
660 l_oe_line_tbl(j).flow_status_code := 'CANCELLED';
661 l_oe_line_tbl(j).source_document_line_id := l_oe_line_old_tbl(J).source_document_line_id;
662
663
664 l_oe_line_tbl(j).open_flag := 'N';
665 End If;
666 END LOOP;
667
668 oe_header_util.Query_Row(
669 p_header_id => l_oe_line_old_tbl(1).header_id,
670 x_header_rec => l_oe_header_rec);
671
672 END If;
673
674 IF l_oe_line_tbl.count = 0 THEN
675 FND_MESSAGE.SET_NAME('ONT','OE_NO_ELIGIBLE_LINES');
676 FND_MESSAGE.SET_TOKEN('ORDER',
677 l_oe_header_rec.Order_Number, FALSE);
678 FND_MSG_PUB.ADD;
679 fnd_msg_pub.count_and_get
680 ( p_count => x_msg_count
681 , p_data => x_msg_data);
682
683 x_return_status := FND_API.G_RET_STS_ERROR;
684
685 RAISE FND_API.G_EXC_ERROR;
686 END IF;
687
688 l_oe_control_rec.controlled_operation := TRUE;
689 l_oe_control_rec.change_attributes := TRUE;
690 l_oe_control_rec.validate_entity := TRUE;
691 l_oe_control_rec.write_to_DB := TRUE;
692 l_oe_control_rec.default_attributes := FALSE;
693 l_oe_control_rec.process := FALSE;
694
695 -- Instruct API to retain its caches
696 l_oe_control_rec.clear_api_cache := FALSE;
697 l_oe_control_rec.clear_api_requests := FALSE;
698
699 -- Call to Process Order
700 OE_Order_PUB.Process_Order(
701 p_org_id => l_source_operating_unit
702 , p_api_version_number => l_api_version_number
703 ,p_init_msg_list => FND_API.G_TRUE
704 ,p_return_values => FND_API.G_FALSE
705 ,p_action_commit => FND_API.G_FALSE
706 -- Passing just the entity records that are a part of this order
707 ,p_header_rec => l_oe_header_rec
708 ,p_line_tbl => l_oe_line_tbl
709 ,p_old_line_tbl => l_oe_line_old_tbl
710 -- OUT variables
711 ,x_header_rec => lx_oe_header_rec
712 ,x_header_val_rec => l_oe_Header_Val_rec
713 ,x_header_adj_tbl => l_oe_header_adj_tbl
714 ,x_Header_Adj_val_tbl => l_oe_header_adj_val_tbl
715 ,x_Header_price_Att_tbl => l_oe_header_price_att_tbl
716 ,x_Header_Adj_Att_tbl => l_oe_Header_Adj_Att_Tbl
717 ,x_Header_Adj_Assoc_tbl => l_oe_Header_Adj_Assoc_Tbl
718 ,x_header_scredit_tbl => l_oe_header_scr_tbl
719 ,x_Header_Scredit_val_tbl => l_oe_Header_Scredit_Val_Tbl
720 ,x_line_tbl => lx_oe_line_tbl
721 ,x_line_val_tbl => l_oe_Line_Val_Tbl
722 ,x_line_adj_tbl => l_oe_line_adj_tbl
723 ,x_Line_Adj_val_tbl => l_oe_Line_Adj_Val_Tbl
724 ,x_Line_price_Att_tbl => l_oe_Line_Price_Att_Tbl
725 ,x_Line_Adj_Att_tbl => l_oe_Line_Adj_Att_Tbl
726 ,x_Line_Adj_Assoc_tbl => l_oe_Line_Adj_Assoc_Tbl
727 ,x_Line_Scredit_tbl => l_oe_line_scr_tbl
728 ,x_Line_Scredit_val_tbl => l_oe_Line_Scredit_Val_Tbl
729 ,x_Lot_Serial_tbl => l_oe_Lot_Serial_Tbl
730 ,x_Lot_Serial_val_tbl => l_oe_Lot_Serial_Val_Tbl
731 ,x_action_request_tbl => l_oe_Request_Tbl_Type
732 ,x_return_status => l_return_status
733 ,x_msg_count => l_msg_count
734 ,x_msg_data => l_msg_data
735 );
736
737 IF (l_source_operating_unit <> l_org_id) THEN
738 fnd_global.apps_initialize(l_user_id,orig_resp_id,orig_resp_appl_id);
739 END If;
740
741 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
742 for counter in REVERSE 1..l_msg_count Loop
743 l_msg := OE_MSG_PUB.Get(counter,FND_API.G_FALSE) ;
744 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
745 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
746 FND_MSG_PUB.ADD;
747 fnd_msg_pub.count_and_get
748 ( p_count => x_msg_count
749 , p_data => x_msg_data);
750 End loop;
751 RAISE FND_API.G_EXC_ERROR;
752 ELSE
753 -- cancel the internal requisitions lines and header for the above order lines and header
754
755 FOR I in 1..lx_oe_line_tbl.count LOOP
756 update po_requisition_lines
757 set quantity_cancelled = lx_oe_line_Tbl(I).cancelled_quantity,
761 where requisition_line_id = lx_oe_line_tbl(I).source_document_line_id;
758 cancel_flag = 'Y',
759 cancel_reason = lx_oe_line_tbl(I).change_reason,
760 cancel_date = sysdate
762
763
764 -- update mtl_supply data for the requisition
765 /* IF NOT po_supply.po_req_supply(
766 p_docid => null,
767 p_lineid => lx_oe_line_Tbl(I).source_document_line_id,
768 p_shipid => null,
769 p_action => 'Remove_Req_Line_Supply',
770 p_recreate_flag => NULL,
771 p_qty => NULL,
772 p_receipt_date => NULL) THEN
773 */
774
775 BEGIN
776 UPDATE mtl_supply
777 SET quantity = 0
778 , change_flag = 'Y'
779 WHERE supply_type_code = 'REQ'
780 AND req_line_id = lx_oe_line_Tbl(I).source_document_line_id;
781 EXCEPTION
782 when no_data_found THEN
783
784 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
785 token1 => 'FILE',
786 value1 => 'PO_SUPPLY',
787 token2 => 'ERR_NUMBER',
788 value2 => '035',
789 token3 => 'SUBROUTINE',
790 value3 => 'PO_REQ_SUPPLY()');
791 RAISE FND_API.G_EXC_ERROR;
792 END;
793 -- END IF;
794 END LOOP;
795
796 -- cancel header separately
797 IF (p_header_rec.order_header_id IS NOT NULL) THEN
798 IF (l_source_operating_unit <> l_org_id) THEN
799 IF new_resp_id is not null and
800 new_resp_appl_id is not null THEN
801 fnd_global.apps_initialize(new_user_id,new_resp_id,new_resp_appl_id);
802 ELSE
803 dbms_application_info.set_client_info(l_source_operating_unit);
804 END IF;
805 END If;
806
807 oe_header_util.Query_Row(
808 p_header_id => l_oe_header_id,
809 x_header_rec => l_oe_header_rec);
810
811 l_oe_header_rec.cancelled_flag := 'Y';
812 l_oe_header_rec.flow_status_code := 'CANCELLED';
813 l_oe_header_rec.open_flag := 'N';
814 l_oe_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
815 l_oe_header_rec.change_reason := p_header_rec.change_reason;
816 l_oe_header_rec.change_comments := p_header_rec.change_comments;
817
818 l_oe_control_rec.controlled_operation := TRUE;
819 l_oe_control_rec.change_attributes := TRUE;
820 l_oe_control_rec.validate_entity := TRUE;
821 l_oe_control_rec.write_to_DB := TRUE;
822 l_oe_control_rec.default_attributes := FALSE;
823 l_oe_control_rec.process := FALSE;
824
825 -- Instruct API to retain its caches
826 l_oe_control_rec.clear_api_cache := FALSE;
827 l_oe_control_rec.clear_api_requests := FALSE;
828
829
830 For I in 1 .. l_oe_line_old_tbl.count LOOP
831 l_oe_line_old_tbl(I).operation := null;
832 l_oe_line_Tbl(i).operation := null;
833 END LOOP;
834
835 -- call to Process Order
836 OE_Order_PUB.Process_Order(
837 p_api_version_number => l_api_version_number
838 ,p_init_msg_list => FND_API.G_TRUE
839 ,p_return_values => FND_API.G_FALSE
840 ,p_action_commit => FND_API.G_FALSE
841 -- Passing just the entity records that are a part of this order
842 ,p_header_rec => l_oe_header_rec
843 ,p_line_tbl => l_oe_line_tbl
844 ,p_old_line_tbl => l_oe_line_old_tbl
845 -- OUT variables
846 ,x_header_rec => lx_oe_header_rec
847 ,x_header_val_rec => l_oe_Header_Val_rec
848 ,x_header_adj_tbl => l_oe_header_adj_tbl
849 ,x_Header_Adj_val_tbl => l_oe_header_adj_val_tbl
850 ,x_Header_price_Att_tbl => l_oe_header_price_att_tbl
851 ,x_Header_Adj_Att_tbl => l_oe_Header_Adj_Att_Tbl
852 ,x_Header_Adj_Assoc_tbl => l_oe_Header_Adj_Assoc_Tbl
853 ,x_header_scredit_tbl => l_oe_header_scr_tbl
854 ,x_Header_Scredit_val_tbl => l_oe_Header_Scredit_Val_Tbl
855 ,x_line_tbl => lx_oe_line_tbl
856 ,x_line_val_tbl => l_oe_Line_Val_Tbl
857 ,x_line_adj_tbl => l_oe_line_adj_tbl
858 ,x_Line_Adj_val_tbl => l_oe_Line_Adj_Val_Tbl
859 ,x_Line_price_Att_tbl => l_oe_Line_Price_Att_Tbl
860 ,x_Line_Adj_Att_tbl => l_oe_Line_Adj_Att_Tbl
861 ,x_Line_Adj_Assoc_tbl => l_oe_Line_Adj_Assoc_Tbl
862 ,x_Line_Scredit_tbl => l_oe_line_scr_tbl
863 ,x_Line_Scredit_val_tbl => l_oe_Line_Scredit_Val_Tbl
864 ,x_Lot_Serial_tbl => l_oe_Lot_Serial_Tbl
868 ,x_msg_count => l_msg_count
865 ,x_Lot_Serial_val_tbl => l_oe_Lot_Serial_Val_Tbl
866 ,x_action_request_tbl => l_oe_Request_Tbl_Type
867 ,x_return_status => l_return_status
869 ,x_msg_data => l_msg_data
870 );
871
872 IF (l_source_operating_unit <> l_org_id) THEN
873 fnd_global.apps_initialize(l_user_id,orig_resp_id,orig_resp_appl_id);
874 END If;
875
876 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
877 for counter in REVERSE 1..l_msg_count Loop
878 l_msg := OE_MSG_PUB.Get(counter,FND_API.G_FALSE) ;
879 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
880 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
881 FND_MSG_PUB.ADD;
882 fnd_msg_pub.count_and_get
883 ( p_count => x_msg_count
884 , p_data => x_msg_data);
885 End loop;
886 RAISE FND_API.G_EXC_ERROR;
887 END If;
888 END IF;
889 END IF;
890 END If;
891
892 fnd_msg_pub.count_and_get
893 ( p_count => x_msg_count
894 , p_data => x_msg_data);
895
896 EXCEPTION
897 WHEN FND_API.G_EXC_ERROR THEN
898 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
899 P_API_NAME => L_API_NAME
900 ,P_PKG_NAME => G_PKG_NAME
901 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
902 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
903 ,X_MSG_COUNT => X_MSG_COUNT
904 ,X_MSG_DATA => X_MSG_DATA
905 ,X_RETURN_STATUS => X_RETURN_STATUS);
906 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
907 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
908 P_API_NAME => L_API_NAME
909 ,P_PKG_NAME => G_PKG_NAME
910 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
911 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
912 ,X_MSG_COUNT => X_MSG_COUNT
913 ,X_MSG_DATA => X_MSG_DATA
914 ,X_RETURN_STATUS => X_RETURN_STATUS);
915 WHEN OTHERS THEN
916 Rollback to Cancel_order_pub;
917 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
918 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
919 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
920 FND_MSG_PUB.ADD;
921 fnd_msg_pub.count_and_get
922 ( p_count => x_msg_count
923 , p_data => x_msg_data);
924 x_return_status := FND_API.G_RET_STS_ERROR;
925
926 END;
927
928
929 PROCEDURE cancel_order_line(
930 p_order_line_id IN NUMBER,
931 p_cancel_reason IN Varchar2,
932 x_return_status OUT NOCOPY VARCHAR2,
933 x_msg_count OUT NOCOPY NUMBER,
934 x_msg_data OUT NOCOPY VARCHAR2) IS
935 l_header_rec csp_parts_requirement.header_rec_type;
936 l_line_table csp_parts_requirement.line_tbl_type;
937 begin
938 l_line_table(1).order_line_id := p_order_line_id;
939 l_line_table(1).change_reason := p_cancel_reason;
940 Cancel_Order(
941 p_header_rec => l_header_rec,
942 p_line_table => l_line_table,
943 p_process_Type => 'ORDER',
944 x_return_status => x_return_status,
945 x_msg_count => x_msg_count,
946 x_msg_data => x_msg_data);
947 end;
948
949 /**************************************************************************
950 ***************************************************************************
951 ***************************************************************************
952 PROCESS_ORDER
953 ***************************************************************************
954 ***************************************************************************
955 ***************************************************************************/
956 PROCEDURE process_order(
957 p_api_version IN NUMBER
958 ,p_Init_Msg_List IN VARCHAR2
959 ,p_commit IN VARCHAR2
960 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.header_rec_type
961 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
962 ,p_process_type IN VARCHAR2
963 ,x_return_status OUT NOCOPY VARCHAR2
964 ,x_msg_count OUT NOCOPY NUMBER
965 ,x_msg_data OUT NOCOPY VARCHAR2
966 )
967 IS
968 l_action_request_tbl oe_order_pub.request_tbl_type;
969 l_api_version_number CONSTANT NUMBER := 1.0;
970 l_api_name CONSTANT VARCHAR2(30) := 'process_order';
971 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
972 l_msg_count NUMBER;
973 l_msg_data VARCHAR2(4000);
974 l_commit VARCHAR2(1) := FND_API.G_FALSE;
975 l_user_id NUMBER;
976 l_login_id NUMBER;
977 l_today DATE;
978
979 EXCP_USER_DEFINED EXCEPTION;
980 INVALID_CHARGE_ACCOUNT EXCEPTION;
981 INVALID_ACCRUAL_ACCOUNT EXCEPTION;
985 l_org_id NUMBER;
982 INVALID_BUDGET_ACCOUNT EXCEPTION;
983 INVALID_VARIANCE_ACCOUNT EXCEPTION;
984
986 l_set_of_books_id NUMBER;
987 l_request_id NUMBER;
988 l_order_source_id NUMBER := 10;
989 l_orig_sys_document_ref VARCHAR2(50);
990 l_change_sequence VARCHAR2(10);
991 l_validate_only VARCHAR2(1);
992 l_init_msg_list VARCHAR2(240);
993 l_rowid NUMBER;
994 l_dummy NUMBER;
995 l_segment1 VARCHAR2(240);
996 l_employee_id NUMBER := -1;
997 l_unit_meas_lookup_code VARCHAR2(25);
998 l_category_id NUMBER;
999 l_price_list_id NUMBER;
1000 l_line_price_list_id NUMBER;
1001 l_currency_code VARCHAR2(3);
1002 l_unit_price NUMBER;
1003 l_chart_of_Accounts_id NUMBER;
1004
1005 l_customer_id NUMBER;
1006 l_cust_acct_id NUMBER;
1007 l_site_use_id NUMBER;
1008 l_line_type_id NUMBER;
1009 l_order_line_type_id NUMBER;
1010 l_order_line_category_code VARCHAR2(30);
1011 l_order_number NUMBER;
1012 l_source_operating_unit NUMBER;
1013
1014 l_header_rec csp_parts_requirement.header_rec_type;
1015 l_line_rec csp_parts_requirement.line_rec_type;
1016 l_line_tbl csp_parts_requirement.Line_tbl_type;
1017 l_dist_Rec csp_parts_order.req_dist_rec_type;
1018
1019 l_transferred_to_oe_flag VARCHAR2(1) := 'Y';
1020 l_msg varchar2(2000);
1021 -- Record and table types for oe process_order
1022 l_oe_header_rec oe_order_pub.header_rec_type := OE_ORDER_PUB.G_MISS_HEADER_REC;
1023 lx_oe_header_rec oe_order_pub.header_rec_type := OE_ORDER_PUB.G_MISS_HEADER_REC;
1024 l_oe_Header_Val_rec oe_order_pub.header_val_rec_type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;
1025
1026 l_oe_header_adj_tbl oe_order_pub.header_adj_tbl_type;
1027 l_oe_header_adj_val_tbl oe_order_pub.header_adj_val_tbl_type;
1028 l_oe_header_price_att_tbl oe_order_pub.header_price_att_tbl_type;
1029 l_oe_Header_Adj_Att_Tbl oe_order_pub.header_adj_att_tbl_type;
1030 l_oe_Header_Adj_Assoc_Tbl oe_order_pub.header_adj_assoc_tbl_type;
1031 l_oe_header_scr_tbl OE_ORDER_PUB.header_scredit_tbl_type;
1032 l_oe_Header_Scredit_Val_Tbl OE_ORDER_PUB.header_scredit_Val_tbl_type;
1033 l_oe_line_rec oe_order_pub.line_rec_type := OE_ORDER_PUB.G_MISS_LINE_REC;
1034 l_oe_line_tbl oe_order_pub.line_tbl_type := OE_ORDER_PUB.G_MISS_LINE_TBL;
1035 lx_oe_line_tbl oe_order_pub.line_tbl_type := OE_ORDER_PUB.G_MISS_LINE_TBL;
1036 l_oe_Line_Val_Tbl oe_order_pub.line_Val_tbl_type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;
1037 l_oe_line_adj_tbl oe_order_pub.line_adj_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
1038 l_oe_Line_Adj_Val_Tbl oe_order_pub.line_adj_val_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;
1039 l_oe_Line_Price_Att_Tbl oe_order_pub.line_price_att_tbl_type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;
1040 l_oe_Line_Adj_Att_Tbl oe_order_pub.line_adj_att_tbl_type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;
1041 l_oe_Line_Adj_Assoc_tbl oe_order_pub.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;
1042 l_oe_line_scr_tbl oe_order_pub.line_scredit_tbl_type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;
1043 l_oe_Line_Scredit_Val_Tbl oe_order_pub.line_scredit_val_tbl_type;
1044 l_oe_Lot_Serial_Tbl oe_order_pub.lot_serial_tbl_type;
1045 l_oe_Lot_Serial_Val_Tbl oe_order_pub.lot_serial_val_tbl_type;
1046 l_oe_Request_Tbl_Type oe_order_pub.Request_tbl_type := OE_ORDER_PUB.G_MISS_REQUEST_TBL;
1047 l_oe_control_rec OE_GLOBALS.Control_Rec_Type;
1048
1049 CURSOR rowid_cur IS
1050 SELECT rowid FROM PO_REQUISITION_HEADERS
1051 WHERE requisition_header_id = l_header_rec.requisition_header_id;
1052
1053 -- Get requisition_number (PO_REQUSITION_HEADERS.segment1)
1054 CURSOR req_number_cur IS
1055 SELECT to_char(current_max_unique_identifier + 1)
1056 FROM po_unique_identifier_control
1057 WHERE table_name = 'PO_REQUISITION_HEADERS'
1058 FOR UPDATE OF current_max_unique_identifier;
1059
1060 -- Get unique requisition_header_id
1061 CURSOR req_header_id_cur IS
1062 SELECT po_requisition_headers_s.nextval
1063 FROM sys.dual;
1064
1065 -- Get unique requisition_line_id
1066 CURSOR req_line_id_cur IS
1067 SELECT po_requisition_lines_s.nextval
1068 FROM sys.dual;
1069
1070 -- Cursor to get unique Distribution_id
1071 CURSOR dist_line_id_cur IS
1072 SELECT po_req_distributions_s.nextval
1073 FROM sys.dual;
1074
1075 -- Cursor to get Accrual Account ID and Variance Account ID
1076 -- For Destination Type Code INVENTORY get accrual account id
1077 -- from MTL_PARAMETERS
1078 -- Per Requisition Import program (pocis.opc).
1079 CURSOR accrual_account_id_cur (p_destination_organization_id NUMBER) IS
1080 SELECT mp.ap_accrual_account,
1081 mp.invoice_price_var_account
1082 FROM mtl_parameters mp
1083 WHERE mp.organization_id = p_destination_organization_id;
1084
1085 -- Get Item Category ID
1086 -- As in Requisition Import
1087 CURSOR item_category_cur(p_item_id NUMBER, p_destination_org_id NUMBER) IS
1088 SELECT mic.category_id
1089 FROM mtl_item_categories mic,
1090 mtl_default_sets_view mdsv
1091 WHERE mic.inventory_item_id = p_item_id
1095
1092 AND mic.organization_id = p_destination_org_id
1093 AND mic.category_set_id = mdsv.category_set_id
1094 AND mdsv.functional_area_id = 2;
1096 -- Get Set of Books ID for a given Org_ID - Mandatory in PO_REQ_DISTRIBUTIONS
1097
1098 CURSOR set_of_books_cur (p_organization_id NUMBER) IS
1099 SELECT set_of_books_id
1100 FROM hr_operating_units
1101 WHERE organization_id = p_organization_id;
1102
1103 -- If encumbrance flag is 'Y' get the budget account
1104 -- For Internal Req, Destination Type Code will be INVENTORY
1105 -- Hence, it is assumed that the budget account will come
1106 -- from MTL_PARAMETERS for the Item and the Destination Organization
1107 CURSOR budget_account_cur (p_destination_organization_id NUMBER,
1108 p_item_id NUMBER) IS
1109 SELECT nvl (msi.encumbrance_account,mp.encumbrance_account)
1110 FROM mtl_system_items msi,
1111 mtl_parameters mp
1112 WHERE msi.inventory_item_id = p_item_id
1113 AND msi.organization_id = p_destination_organization_id
1114 AND mp.organization_id = msi.organization_id;
1115
1116 -- Get Requisition Encumbrance Flag for the Set of Books
1117 -- Based on this flag Budget Account will be populated
1118 -- in PO_REQ_DISTRIBUTIONS
1119 CURSOR req_encumbrance_cur (p_set_of_books_id NUMBER) IS
1120 SELECT nvl (fsp.req_encumbrance_flag,'N')
1121 FROM financials_system_parameters fsp
1122 WHERE fsp.set_of_books_id = p_set_of_books_id;
1123
1124 -- Get Charge Account for the Item and Organization
1125 CURSOR charge_account_cur (p_destination_organization_id NUMBER,
1126 p_item_id NUMBER) IS
1127 SELECT NVL(expense_account,-1)
1128 FROM mtl_system_items
1129 WHERE inventory_item_id = p_item_id
1130 AND organization_id = p_destination_organization_id;
1131
1132 -- Get Unit_of_Measure from MTL_UNIT_OF_MEASURES, since OM passes
1133 -- only UOM_CODE and PO requires UNIT_OF_MEASURE. This is being done
1134 -- to fix the problem of line not showing up from POXRQVRQ form
1135 CURSOR unit_of_measure_cur (p_uom_code VARCHAR2) IS
1136 SELECT mum.unit_of_measure
1137 FROM mtl_units_of_measure mum
1138 WHERE mum.uom_code = p_uom_code;
1139
1140 -- Get default line type
1141 CURSOR line_type_cur IS
1142 SELECT psp.line_type_id,
1143 plt.order_type_lookup_code,
1144 plt.purchase_basis,
1145 plt.matching_basis
1146 FROM PO_SYSTEM_PARAMETERS_ALL psp,
1147 PO_LINE_TYPES plt
1148 WHERE psp.org_id = l_org_id
1149 AND plt.line_type_id = psp.line_type_id;
1150
1151 l_line_type_rec line_Type_cur%ROWTYPE;
1152
1153 -- Get preparer_id
1154 CURSOR employee_id_cur IS
1155 SELECT employee_id
1156 FROM fnd_user
1157 WHERE user_id = l_user_id;
1158
1159 -- Get site_use_id for the inventory location id
1160 CURSOR cust_site_cur IS
1161 SELECT pol.customer_id, pol.site_use_id, cust_acct.cust_account_id
1162 FROM PO_LOCATION_ASSOCIATIONS_ALL pol,
1163 HZ_CUST_ACCT_SITES_ALL cust_acct,
1164 HZ_CUST_SITE_USES_ALL site_use
1165 WHERE pol.location_id = l_header_rec.ship_to_location_id
1166 AND site_use.site_use_id = pol.site_use_id
1167 AND cust_acct.cust_acct_site_id = site_use.cust_acct_site_id;
1168
1169 -- Get Item Description for a given Item ID
1170 -- For the purpose of creating Approved Internal Requisition
1171 -- it is assumed that the calling procedure will always pass the Item ID
1172 -- so that Item Description can be derived.
1173 CURSOR item_Desc_cur(p_item_id NUMBER, p_orgn_id NUMBER) IS
1174 SELECT description
1175 FROM mtl_system_items_b
1176 WHERE inventory_item_id = p_item_id
1177 AND organization_id = p_orgn_id;
1178
1179 CURSOR rs_loc_exists_cur(p_inv_loc_id NUMBER, p_resource_id NUMBER, p_resource_type VARCHAR2) IS
1180
1181 SELECT ps.location_id site_loc_id
1182 from csp_rs_cust_relations rcr,
1183 hz_cust_acct_sites cas,
1184 hz_cust_site_uses csu,
1185 po_location_associations pla,
1186 hz_party_sites ps
1187 where rcr.customer_id = cas.cust_account_id
1188 and cas.cust_acct_site_id = csu.cust_acct_site_id (+)
1189 and csu.site_use_code = 'SHIP_TO'
1190 and csu.site_use_id = pla.site_use_id
1191 and cas.party_site_id = ps.party_site_id
1192 and rcr.resource_type = p_resource_type
1193 and rcr.resource_id = p_resource_id
1194 and pla.location_id = p_inv_loc_id;
1195
1196 CURSOR address_type_cur(p_rqmt_header_id NUMBER) IS
1197 SELECT crh.address_type,
1198 crh.ship_to_location_id,
1199 decode(crh.task_assignment_id,null,crh.resource_id,jta.resource_id),
1200 decode(crh.task_assignment_id,null,crh.resource_type,jta.resource_type_code)
1201 from jtf_task_assignments jta,
1202 csp_requirement_headers crh
1203 where jta.task_assignment_id(+) = crh.task_assignment_id
1204 and crh.requirement_header_id = p_rqmt_header_id;
1205
1206 CURSOR get_new_context(p_new_org_id number) IS
1207 SELECT org_information2 ,
1208 org_information3 ,
1209 org_information4
1210 FROM hr_organization_information hou
1211 WHERE hou.organization_id = p_new_org_id
1212 AND hou.org_information1 = 'FIELD_SERVICE'
1216 cursor get_bill_to_for_sr (p_rqmt_header_id NUMBER) IS
1213 AND hou.org_information_context = 'CS_USER_CONTEXT';
1214
1215 -- bug # 6471559
1217 SELECT site_use.site_use_id
1218 FROM HZ_CUST_ACCT_SITES_ALL cust_acct,
1219 HZ_CUST_SITE_USES_ALL site_use,
1220 hz_party_site_uses hpsu,
1221 cs_incidents_all cia,
1222 csp_requirement_headers_v req
1223 WHERE req.requirement_header_id = p_rqmt_header_id
1224 and cia.incident_id = req.incident_id
1225 and cust_acct.cust_account_id = cia.bill_to_account_id
1226 and hpsu.party_site_use_id = cia.bill_to_site_use_id
1227 and cust_acct.party_site_id = hpsu.party_site_id
1228 and cust_acct.cust_acct_site_id = site_use.cust_acct_site_id
1229 and site_use.site_use_code = 'BILL_TO'
1230 and site_use.org_id = cia.org_id;
1231
1232 l_bill_to_site_use_id number;
1233
1234 orig_org_id number;
1235 orig_user_id number;
1236 orig_resp_id number;
1237 orig_resp_appl_id number;
1238 new_org_id number;
1239 new_user_id number;
1240 new_resp_id number;
1241 new_resp_appl_id number;
1242
1243 l_address_type VARCHAR2(30);
1244 l_ship_to_location_id NUMBER;
1245 l_site_loc_id NUMBER;
1246 l_resource_id NUMBER;
1247 l_resource_type VARCHAR2(240);
1248 l_object_version_number NUMBER;
1249 l_item_cost NUMBER;
1250 l_scheduling_code VARCHAR2(30);
1251 BEGIN
1252
1253 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1254 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1255 'csp.plsql.csp_parts_order.process_order',
1256 'Begin');
1257 end if;
1258
1259 SAVEPOINT Process_Order_PUB;
1260
1261 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1262 -- initialize message list
1263 FND_MSG_PUB.initialize;
1264 END IF;
1265
1266 -- Standard call to check for call compatibility.
1267 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1268 p_api_version,
1269 l_api_name,
1270 G_PKG_NAME)
1271 THEN
1272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1273 END IF;
1274
1275 -- initialize return status
1276 x_return_status := FND_API.G_RET_STS_SUCCESS;
1277
1278 l_user_id := nvl(fnd_global.user_id, 0) ;
1279 fnd_profile.get('RESP_ID',orig_resp_id);
1280 fnd_profile.get('RESP_APPL_ID',orig_resp_appl_id);
1281
1282 l_header_rec := px_header_rec;
1283 l_line_tbl := px_line_table;
1284
1285 IF (l_line_Tbl.count <= 0 AND l_header_rec.operation <> CSP_PARTS_ORDER.G_OPR_CANCEL) THEN
1286 return;
1287 END IF;
1288
1289 -- get all the values required to insert into po_requisition_header table
1290 SELECT Sysdate INTO l_today FROM dual;
1291 l_user_id := nvl(fnd_global.user_id, 0) ;
1292 l_login_id := nvl(fnd_global.login_id, -1);
1293
1294 -- operating unit
1295 BEGIN
1296 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
1297 INTO l_org_id
1298 FROM dual;
1299 po_moac_utils_pvt.set_org_context(l_org_id);
1300
1301 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1302 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1303 'csp.plsql.csp_parts_order.process_order',
1304 'Setting org context for l_org_id = ' || l_org_id);
1305 end if;
1306
1307 EXCEPTION
1308 WHEN NO_DATA_FOUND THEN
1309 null;
1310 END;
1311
1312 -- Get Set of Books Id.
1313 -- this is a required field for po_Req_distributions
1314 OPEN set_of_books_cur (l_org_id);
1315 FETCH set_of_books_cur INTO l_set_of_books_id;
1316 CLOSE set_of_books_cur;
1317
1318 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1319 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1320 'csp.plsql.csp_parts_order.process_order',
1321 'l_header_rec.operation = ' || l_header_rec.operation);
1322 end if;
1323
1324 IF (l_header_rec.operation = CSP_PARTS_ORDER.G_OPR_CANCEL) THEN
1325 Cancel_Order( p_header_rec => l_header_rec,
1326 p_line_table => l_line_tbl,
1327 p_process_type => p_process_type,
1328 x_return_status => l_return_status,
1329 x_msg_count => l_msg_count,
1330 x_msg_data => l_msg_data
1331 );
1332
1333 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1334 RAISE FND_API.G_EXC_ERROR;
1335 END IF;
1336 ELSE
1337
1338 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1339 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1340 'csp.plsql.csp_parts_order.process_order',
1341 'p_process_Type = ' || p_process_Type);
1342 end if;
1343
1344 IF (p_process_Type = 'BOTH' or p_process_type = 'ORDER') THEN
1345 -- if address type is special check to see if the location exists in the engineers list
1349 FETCH address_type_cur INTO l_address_type, l_ship_to_location_id, l_resource_id, l_resource_type;
1346 -- if it does not exist, add it to the list
1347 IF (l_header_rec.requirement_header_id IS NOT NULL) THEN
1348 OPEN address_type_cur(l_header_rec.requirement_header_id);
1350 CLOSE address_type_cur;
1351
1352 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1353 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1354 'csp.plsql.csp_parts_order.process_order',
1355 'l_address_type = ' || l_address_type
1356 || ', l_ship_to_location_id = ' || l_ship_to_location_id
1357 || ', l_resource_id = ' || l_resource_id
1358 || ', l_resource_type = ' || l_resource_type);
1359 end if;
1360
1361 IF (l_address_type = 'S') THEN
1362 OPEN rs_loc_exists_cur(l_ship_to_location_id, l_resource_id, l_resource_type);
1363 FETCH rs_loc_exists_cur INTO l_site_loc_id;
1364 IF (rs_loc_exists_cur%NOTFOUND) THEN
1365 -- call ship_to_address_handler for creating resource address
1366 csp_ship_to_address_pvt.ship_to_address_handler(
1367 P_TASK_ASSIGNMENT_ID => l_header_rec.task_assignment_id
1368 ,P_RESOURCE_TYPE => l_resource_type
1369 ,P_RESOURCE_ID => l_resource_id
1370 ,P_CUSTOMER_ID => l_customer_id
1371 ,P_LOCATION_ID => l_ship_to_location_id
1372 ,P_STYLE => null
1373 ,P_ADDRESS_LINE_1 => null
1374 ,P_ADDRESS_LINE_2 => null
1375 ,P_ADDRESS_LINE_3 => null
1376 ,P_COUNTRY => null
1377 ,P_POSTAL_CODE => null
1378 ,P_REGION_1 => null
1379 ,P_REGION_2 => null
1380 ,P_REGION_3 => null
1381 ,P_TOWN_OR_CITY => null
1382 ,P_TAX_NAME => null
1383 ,P_TELEPHONE_NUMBER_1 => null
1384 ,P_TELEPHONE_NUMBER_2 => null
1385 ,P_TELEPHONE_NUMBER_3 => null
1386 ,P_LOC_INFORMATION13 => null
1387 ,P_LOC_INFORMATION14 => null
1388 ,P_LOC_INFORMATION15 => null
1389 ,P_LOC_INFORMATION16 => null
1390 ,P_LOC_INFORMATION17 => null
1391 ,P_LOC_INFORMATION18 => null
1392 ,P_LOC_INFORMATION19 => null
1393 ,P_LOC_INFORMATION20 => null
1394 ,P_TIMEZONE => null
1395 ,P_PRIMARY_FLAG => null
1396 ,P_STATUS => null
1397 ,P_OBJECT_VERSION_NUMBER => l_object_version_number
1398 ,p_API_VERSION_NUMBER => l_api_version_number
1399 ,P_INIT_MSG_LIST => 'T'
1400 ,P_COMMIT => 'F'
1401 ,P_ATTRIBUTE_CATEGORY => NULL
1402 ,P_ATTRIBUTE1 => NULL
1403 ,P_ATTRIBUTE2 => NULL
1404 ,P_ATTRIBUTE3 => NULL
1405 ,P_ATTRIBUTE4 => NULL
1406 ,P_ATTRIBUTE5 => NULL
1407 ,P_ATTRIBUTE6 => NULL
1408 ,P_ATTRIBUTE7 => NULL
1409 ,P_ATTRIBUTE8 => NULL
1410 ,P_ATTRIBUTE9 => NULL
1411 ,P_ATTRIBUTE10 => NULL
1412 ,P_ATTRIBUTE11 => NULL
1413 ,P_ATTRIBUTE12 => NULL
1414 ,P_ATTRIBUTE13 => NULL
1415 ,P_ATTRIBUTE14 => NULL
1416 ,P_ATTRIBUTE15 => NULL
1417 ,P_ATTRIBUTE16 => NULL
1418 ,P_ATTRIBUTE17 => NULL
1419 ,P_ATTRIBUTE18 => NULL
1420 ,P_ATTRIBUTE19 => NULL
1421 ,P_ATTRIBUTE20 => NULL
1422 ,X_RETURN_STATUS => l_return_status
1423 ,X_MSG_COUNT => l_msg_count
1424 ,X_MSG_DATA => l_msg_data
1425 );
1426
1427 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1428 RAISE FND_API.G_EXC_ERROR;
1429 END IF;
1430
1431 END If;
1432 CLOSE rs_loc_exists_cur;
1433 -- bug # 6471559
1434 ELSIF (l_address_type = 'C') THEN
1435 open get_bill_to_for_sr(l_header_rec.requirement_header_id);
1436 fetch get_bill_to_for_sr into l_bill_to_site_use_id;
1437 close get_bill_to_for_sr;
1438 END If;
1439 END IF;
1440 END If;
1441
1442 IF (l_header_rec.operation = G_OPR_CREATE) THEN
1443
1444 IF (p_process_Type IN ('REQUISITION', 'BOTH')) THEN
1445
1446 -- requisition_header_id
1450 CLOSE req_header_id_cur;
1447 IF l_header_rec.requisition_header_id is null then
1448 OPEN req_header_id_cur;
1449 FETCH req_header_id_cur into l_header_rec.requisition_header_id;
1451 END IF;
1452
1453 -- Requisition_number
1454 IF l_header_rec.requisition_number IS NULL THEN
1455 OPEN req_number_cur;
1456 FETCH req_number_cur INTO l_header_rec.requisition_number;
1457 UPDATE po_unique_identifier_control
1458 SET current_max_unique_identifier
1459 = current_max_unique_identifier + 1
1460 WHERE CURRENT of req_number_cur;
1461 CLOSE req_number_cur;
1462 END IF;
1463
1464 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1465 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1466 'csp.plsql.csp_parts_order.process_order',
1467 'l_header_rec.requisition_header_id = ' || l_header_rec.requisition_header_id
1468 || ', l_header_rec.requisition_number = ' || l_header_rec.requisition_number);
1469 end if;
1470
1471 -- preparer id
1472 IF l_user_id IS NOT NULL THEN
1473 OPEN employee_id_cur;
1474 FETCH employee_id_cur into l_employee_id;
1475 CLOSE employee_id_cur;
1476 END IF;
1477
1478 -- check for uniqueness of requisition_number
1479 BEGIN
1480
1481 SELECT 1 INTO l_dummy
1482 FROM DUAL
1483 WHERE NOT EXISTS
1484 ( SELECT 1
1485 FROM po_requisition_headers
1486 WHERE Segment1 = l_header_rec.requisition_number)
1487 AND NOT EXISTS
1488 ( SELECT 1
1489 FROM po_history_requisitions phr
1490 WHERE phr.segment1 = l_header_rec.requisition_number);
1491
1492 EXCEPTION
1493 WHEN NO_DATA_FOUND THEN
1494 po_message_s.app_error('PO_ALL_ENTER_UNIQUE');
1495 raise;
1496 WHEN OTHERS THEN
1497 po_message_s.sql_error('check_unique','010',sqlcode);
1498 raise;
1499 END;
1500
1501 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1502 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1503 'csp.plsql.csp_parts_order.process_order',
1504 'Inserting data into PO_REQUISITION_HEADERS');
1505 end if;
1506
1507 -- create approved requisition headers
1508 -- insert into PO_REQUISITION_HEADERS
1509 INSERT INTO PO_REQUISITION_HEADERS(
1510 org_id,
1511 requisition_header_id,
1512 preparer_id,
1513 last_update_date,
1514 last_updated_by,
1515 segment1,
1516 summary_flag,
1517 enabled_flag,
1518 segment2,
1519 segment3,
1520 segment4,
1521 segment5,
1522 start_date_active,
1523 end_date_active,
1524 last_update_login,
1525 creation_date,
1526 created_by,
1527 description,
1528 authorization_status,
1529 note_to_authorizer,
1530 type_lookup_code,
1531 transferred_to_oe_flag,
1532 attribute_category,
1533 attribute1,
1534 attribute2,
1535 attribute3,
1536 attribute4,
1537 attribute5,
1538 on_line_flag,
1539 preliminary_research_flag,
1540 research_complete_flag,
1541 preparer_finished_flag,
1542 preparer_finished_date,
1543 agent_return_flag,
1544 agent_return_note,
1545 cancel_flag,
1546 attribute6,
1547 attribute7,
1548 attribute8,
1549 attribute9,
1550 attribute10,
1551 attribute11,
1552 attribute12,
1553 attribute13,
1554 attribute14,
1555 attribute15,
1556 ussgl_transaction_code,
1557 government_context,
1558 interface_source_code,
1559 interface_source_line_id,
1560 closed_code
1561 ) VALUES (
1562 l_org_id,
1563 l_header_rec.requisition_header_id,
1564 l_employee_id,
1565 l_today,
1566 nvl(l_user_id, 1),
1567 l_header_Rec.requisition_number,
1571 null,
1568 'N', -- summary_flag
1569 'Y', -- Enabled_Flag
1570 null,
1572 null,
1573 null,
1574 null, -- Start_Date_Active
1575 null, -- End_Date_Active
1576 nvl(l_login_id, -1), -- Last_Update_Login
1577 l_today, -- Creation_Date
1578 nvl(l_user_id, 1), -- Created_By
1579 l_header_rec.description, -- Description
1580 'APPROVED', -- Authorization_Status
1581 null, -- note to Authorizor
1582 'INTERNAL', -- Type_Lookup_Code; need to confirm this. po_lookup_codes has different values for document_type
1583
1584 'Y', -- X_Transferred_To_Oe_Flag
1585 null,
1586 null,
1587 null,
1588 null,
1589 null,
1590 null,
1591 null,
1592 null,
1593 null,
1594 null,
1595 null,
1596 null,
1597 null,
1598 null,
1599 null,
1600 null,
1601 null,
1602 null,
1603 null,
1604 null,
1605 null,
1606 null,
1607 null,
1608 null,
1609 null,
1610 null,
1611 null,
1612 null,
1613 null
1614 );
1615
1616 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1617 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1618 'csp.plsql.csp_parts_order.process_order',
1619 'Inserting data into PO_ACTION_HISTORY');
1620 end if;
1621
1622 -- insert 2 lines of history, one for SUBMIT and one for APPROVE
1623 INSERT into PO_ACTION_HISTORY
1624 (object_id,
1625 object_type_code,
1626 object_sub_type_code,
1627 sequence_num,
1628 last_update_date,
1629 last_updated_by,
1630 creation_date,
1631 created_by,
1632 action_code,
1633 action_date,
1634 employee_id,
1635 note,
1636 object_revision_num,
1637 last_update_login,
1638 request_id,
1639 program_application_id,
1640 program_id,
1641 program_update_date,
1642 approval_path_id,
1643 offline_code)
1644 VALUES
1645 (l_header_rec.requisition_header_id,
1646 'REQUISITION',
1647 'INTERNAL',
1648 0,
1649 sysdate,
1650 fnd_global.user_id,
1651 sysdate,
1652 fnd_global.user_id,
1653 'SUBMIT',
1654 sysdate,
1655 l_employee_id,
1656 null,
1657 null,
1658 fnd_global.login_id,
1659 0,
1660 0,
1661 0,
1662 '',
1663 null,
1664 '' );
1665
1666 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1667 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1668 'csp.plsql.csp_parts_order.process_order',
1669 'Inserting data into PO_ACTION_HISTORY');
1670 end if;
1671
1672 INSERT into PO_ACTION_HISTORY
1673 (object_id,
1674 object_type_code,
1675 object_sub_type_code,
1676 sequence_num,
1677 last_update_date,
1678 last_updated_by,
1679 creation_date,
1680 created_by,
1681 action_code,
1682 action_date,
1683 employee_id,
1684 note,
1685 object_revision_num,
1686 last_update_login,
1687 request_id,
1688 program_application_id,
1689 program_id,
1690 program_update_date,
1691 approval_path_id,
1695 'REQUISITION',
1692 offline_code)
1693 VALUES
1694 (l_header_rec.requisition_header_id,
1696 'INTERNAL',
1697 1,
1698 sysdate,
1699 fnd_global.user_id,
1700 sysdate,
1701 fnd_global.user_id,
1702 'APPROVE',
1703 sysdate,
1704 l_employee_id,
1705 null,
1706 null,
1707 fnd_global.login_id,
1708 0,
1709 0,
1710 0,
1711 '',
1712 null,
1713 '' );
1714
1715 END IF;
1716
1717 IF (p_process_Type IN ('ORDER', 'BOTH')) THEN
1718
1719 BEGIN
1720 SELECT operating_unit
1721 INTO l_source_operating_unit
1722 FROM org_organization_Definitions
1723 WHERE organization_id = l_line_tbl(1).source_organization_id;
1724 EXCEPTION
1725 WHEN NO_DATA_FOUND THEN
1726 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
1727 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'source_org_operating_unit', FALSE);
1728 FND_MSG_PUB.ADD;
1729 RAISE EXCP_USER_DEFINED;
1730 END;
1731
1732 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1733 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1734 'csp.plsql.csp_parts_order.process_order',
1735 'l_org_id = ' || l_org_id
1736 || ', l_source_operating_unit = ' || l_source_operating_unit);
1737 end if;
1738
1739 BEGIN
1740 IF (l_org_id <> l_source_operating_unit) THEN
1741 SELECT ORDER_TYPE_ID
1742 INTO l_header_rec.order_type_id
1743 FROM PO_SYSTEM_PARAMETERS_ALL
1744 WHERE nvl(ORG_ID,-1) = l_source_operating_unit;
1745 -- bug # 7644078
1746 -- Here we are going to change the operating unit
1747 -- so hlding old BILL TO from SR will not be a valid one
1748 -- now, OM code will take proper BILL TO
1749 l_bill_to_site_use_id := NULL;
1750 ELSE
1751 l_source_operating_unit := l_org_id;
1752 END IF;
1753
1754 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1755 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1756 'csp.plsql.csp_parts_order.process_order',
1757 'l_header_rec.order_type_id = ' || l_header_rec.order_type_id);
1758 end if;
1759
1760 -- get all required information for passing to process_orders
1761 SELECT hdr.price_list_id,
1762 hdr.currency_code,
1763 hdr.default_outbound_line_type_id,
1764 line.price_list_id,
1765 line.order_category_code,
1766 nvl(line.scheduling_level_code, hdr.scheduling_level_code)
1767 INTO l_price_list_id,
1768 l_currency_code,
1769 l_order_line_type_id,
1770 l_line_price_list_id,
1771 l_order_line_category_code,
1772 l_scheduling_code
1773 FROM oe_transaction_types_all hdr,
1774 oe_transaction_types_all line
1775 WHERE hdr.transaction_Type_id = l_header_rec.order_type_id
1776 AND line.transaction_type_id = hdr.default_outbound_line_type_id
1777 AND hdr.org_id = l_source_operating_unit
1778 AND line.org_id = l_source_operating_unit;
1779 EXCEPTION
1780 WHEN NO_DATA_FOUND THEN
1781 -- exception handler
1782 null;
1783 WHEN OTHERS THEN
1784 -- exception handler
1785 null;
1786 END;
1787
1788 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1789 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1790 'csp.plsql.csp_parts_order.process_order',
1791 'l_price_list_id = ' || l_price_list_id
1792 || ', l_currency_code = ' || l_currency_code
1793 || ', l_order_line_type_id = ' || l_order_line_type_id
1794 || ', l_line_price_list_id = ' || l_line_price_list_id
1795 || ', l_order_line_category_code = ' || l_order_line_category_code
1796 || ', l_scheduling_code = ' || l_scheduling_code);
1797 end if;
1798
1799 IF (l_currency_code IS NULL) THEN
1800 BEGIN
1801 SELECT glsob.CURRENCY_CODE
1802 INTO l_currency_code
1803 FROM GL_SETS_OF_BOOKS GLSOB,
1807
1804 FINANCIALS_SYSTEM_PARAMS_ALL FSP
1805 WHERE GLSOB.SET_OF_BOOKS_ID=FSP.SET_OF_BOOKS_ID
1806 AND nvl(FSP.org_id,-1) = l_source_operating_unit;
1808 EXCEPTION
1809 WHEN NO_DATA_FOUND THEN
1810 null;
1811 WHEN OTHERS THEN
1812 null;
1813
1814 END;
1815 END IF;
1816
1817 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1818 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1819 'csp.plsql.csp_parts_order.process_order',
1820 'l_currency_code = ' || l_currency_code);
1821 end if;
1822
1823 -- get customer and ship to site
1824 OPEN cust_site_cur;
1825 FETCH cust_site_cur INTO l_customer_id, l_site_use_id, l_cust_acct_id;
1826 CLOSE cust_site_cur;
1827
1828 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1829 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1830 'csp.plsql.csp_parts_order.process_order',
1831 'l_customer_id = ' || l_customer_id
1832 || ', l_site_use_id = ' || l_site_use_id
1833 || ', l_cust_acct_id = ' || l_cust_acct_id);
1834 end if;
1835
1836 -- SETTING UP THE ORDER PROCESS HEADER RECORD
1837 -- order_header_id
1838 IF l_header_rec.order_header_id IS NULL THEN
1839 select oe_order_headers_s.nextval
1840 into l_header_rec.order_header_id
1841 from dual;
1842 END IF;
1843
1844 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1845 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1846 'csp.plsql.csp_parts_order.process_order',
1847 'l_header_rec.order_header_id = ' || l_header_rec.order_header_id);
1848 end if;
1849
1850 -- Required attributes (e.g. Order Type and Customer)
1851 l_oe_header_rec.header_id := l_header_rec.order_header_id;
1852 --l_order_number := OE_Default_header.Get_Order_Number;
1853 l_oe_header_rec.order_number := null; --l_header_rec.order_header_id;
1854 l_oe_header_rec.version_number := 1;
1855 l_oe_header_rec.order_type_id := l_header_rec.order_type_id;
1856 l_oe_header_rec.org_id := l_source_operating_unit;
1857 l_oe_header_rec.sold_to_org_id := l_cust_acct_id;
1858 l_oe_header_rec.ship_to_org_id := l_site_use_id;
1859
1860 -- bug # 6471559
1861 if l_bill_to_site_use_id is not NULL then
1862 l_oe_header_rec.invoice_to_org_id := l_bill_to_site_use_id;
1863 end if;
1864
1865 l_oe_header_rec.ORIG_SYS_DOCUMENT_REF := l_header_rec.requisition_number;
1866 l_oe_header_rec.SOURCE_DOCUMENT_ID := l_header_rec.requisition_header_id;
1867 l_oe_header_rec.transactional_curr_code := l_currency_code;
1868 l_oe_header_rec.open_flag := 'Y';
1869 l_oe_header_rec.booked_flag := l_line_tbl(1).booked_flag; --N;
1870 l_oe_header_rec.order_source_id := l_order_source_id;
1871 l_oe_header_rec.source_document_type_id := l_order_source_id;
1872
1873 -- Other attributes
1874 l_oe_header_rec.price_list_id := l_price_list_id;
1875 END IF;
1876
1877 -- Indicates to process order that a new header is being created
1878 l_oe_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
1879
1880 ELSIF (l_header_rec.operation = G_OPR_UPDATE) THEN
1881 IF (l_header_rec.order_header_id is null) THEN
1882 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
1883 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_header_rec.order_header_id', FALSE);
1884
1885 FND_MSG_PUB.ADD;
1886 RAISE EXCP_USER_DEFINED;
1887 END IF;
1888
1889 oe_header_util.Query_Row(
1890 p_header_id => l_header_rec.order_header_id,
1891 x_header_rec => l_oe_header_rec);
1892
1893 -- Indicates to process order that header is to be updated
1894 l_oe_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
1895 l_oe_header_rec.booked_flag := l_line_tbl(1).booked_flag;
1896
1897 END IF;
1898
1899 /*IF (p_process_type in ('ORDER', 'BOTH')) THEN
1900 -- Required attributes (e.g. Order Type and Customer)
1901 l_oe_header_rec.header_id := l_header_rec.order_header_id;
1902 --l_order_number := OE_Default_header.Get_Order_Number;
1903 l_oe_header_rec.order_number := l_header_rec.order_header_id;
1904 l_oe_header_rec.version_number := 1;
1905 l_oe_header_rec.order_type_id := l_header_rec.order_type_id;
1906 l_oe_header_rec.org_id := l_org_id;
1907 l_oe_header_rec.sold_to_org_id := l_cust_acct_id;
1908 l_oe_header_rec.ship_to_org_id := l_site_use_id;
1909 l_oe_header_rec.ORIG_SYS_DOCUMENT_REF := l_header_rec.requisition_number;
1910 l_oe_header_rec.SOURCE_DOCUMENT_ID := l_header_rec.requisition_header_id;
1911 l_oe_header_rec.transactional_curr_code := l_currency_code;
1915 l_oe_header_rec.source_document_type_id := l_order_source_id;
1912 l_oe_header_rec.open_flag := 'Y';
1913 l_oe_header_rec.booked_flag := l_line_tbl(1).booked_flag; --N;
1914 l_oe_header_rec.order_source_id := l_order_source_id;
1916
1917 -- Other attributes
1918 l_oe_header_rec.price_list_id := l_price_list_id;
1919 END IF;
1920 */
1921
1922 -- get all the values required to insert into po_requisition_lines table
1923
1924 -- line_type_id for Requisition
1925 OPEN line_type_cur;
1926 FETCH line_type_cur INTO l_line_type_rec;
1927 CLOSE line_type_cur;
1928
1929 FOR I IN 1..l_line_tbl.COUNT LOOP
1930
1931 IF (l_header_rec.operation = G_OPR_CREATE) THEN
1932
1933 IF (p_process_type in ('REQUISITION', 'BOTH')) THEN
1934 -- get requisition_line_id
1935 IF (l_line_tbl(i).Requisition_Line_Id is NULL) THEN
1936 OPEN req_line_id_cur;
1937 FETCH req_line_id_cur INTO l_line_tbl(i).requisition_line_id;
1938 CLOSE req_line_id_cur;
1939 END IF;
1940
1941 IF (l_line_tbl(i).item_description IS NULL) THEN
1942 OPEN item_desc_cur(l_line_tbl(i).inventory_item_id, l_line_tbl(i).source_organization_id);
1943 FETCH item_desc_cur INTO l_line_tbl(i).item_description;
1944 IF item_Desc_cur%NOTFOUND THEN
1945 CLOSE item_desc_cur;
1946 FND_MESSAGE.SET_NAME ('ONT', 'OE_INVALID_ITEM_WHSE');
1947 FND_MSG_PUB.ADD;
1948 RAISE FND_API.G_EXC_ERROR;
1949 END IF;
1950 CLOSE item_desc_cur;
1951 END IF;
1952
1953 -- Get Category ID of the Item
1954 OPEN item_category_cur (l_line_tbl(i).inventory_item_id, l_header_rec.dest_organization_id);
1955 FETCH item_category_cur INTO l_category_id;
1956 CLOSE item_category_cur;
1957
1958 -- Derive Unit_of_Measure from Uom_Code
1959 OPEN unit_of_measure_cur(l_line_tbl(i).unit_of_measure);
1960 FETCH unit_of_measure_cur INTO l_unit_meas_lookup_code;
1961 CLOSE unit_of_measure_cur;
1962
1963 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1964 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1965 'csp.plsql.csp_parts_order.process_order',
1966 'Getting Unit Price...');
1967 end if;
1968
1969 /* Get Unit Price and Currency Code*/
1970 get_unit_price_prc (l_line_tbl(i).inventory_item_id
1971 ,l_line_tbl(i).source_organization_id
1972 ,l_header_rec.dest_organization_id
1973 ,l_set_of_books_id
1974 ,l_chart_of_accounts_id
1975 ,l_currency_code
1976 ,l_unit_price
1977 ,l_item_cost );
1978
1979 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1980 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1981 'csp.plsql.csp_parts_order.process_order',
1982 'Inserting data into PO_REQUISITION_LINES...');
1983 end if;
1984
1985 -- insert into po_requisition_lines table
1986 INSERT INTO PO_REQUISITION_LINES(
1987 org_id,
1988 requisition_line_id,
1989 requisition_header_id,
1990 line_num,
1991 line_type_id,
1992 category_id,
1993 item_description,
1994 unit_meas_lookup_code,
1995 unit_price,
1996 quantity,
1997 deliver_to_location_id,
1998 to_person_id,
1999 last_update_date,
2000 last_updated_by,
2001 source_type_code,
2002 last_update_login,
2003 creation_date,
2004 created_by,
2005 item_id,
2006 item_revision,
2007 encumbered_flag,
2008 rfq_required_flag,
2009 need_by_date,
2010 source_organization_id,
2011 source_subinventory,
2012 destination_type_code,
2013 destination_organization_id,
2014 destination_subinventory,
2015 cancel_flag,
2016 order_type_lookup_code,
2017 purchase_basis,
2018 matching_basis
2019 ) VALUES (
2020 l_org_id,
2021 l_line_tbl(i).requisition_line_id,
2022 l_header_rec.requisition_header_id,
2023 l_line_tbl(i).line_num,
2024 l_line_type_rec.line_type_id, -- Line_Type_Id
2025 nvl(l_Category_id, 1), -- Category_id
2026 l_line_tbl(i).item_description, -- Item_Description
2030 l_header_rec.ship_to_location_id, -- Deliver_To_Location_Id
2027 nvl(l_unit_meas_lookup_code, l_line_tbl(i).unit_of_measure), -- Unit_Meas_Lookup_Code
2028 l_unit_price,
2029 l_line_tbl(i).ordered_quantity,
2031 l_employee_id, -- To_Person_Id
2032 l_today, -- Last_Update_Date
2033 nvl(l_user_id, -1), -- Last_Updated_By
2034 'INVENTORY', -- Source_Type_Code
2035 nvl(l_login_id, -1),
2036 l_today, -- Creation_Date
2037 nvl(l_user_id, -1),
2038 l_line_tbl(i).inventory_item_id,
2039 l_line_tbl(i).revision,
2040 'N', -- Encumbered_flag
2041 'N', -- X_Rfq_Required_Flag
2042 l_header_rec.need_by_date,
2043 l_line_tbl(i).source_organization_id,
2044 l_line_tbl(i).source_subinventory,
2045 'INVENTORY', -- Destination_Type_Code
2046 l_header_rec.dest_organization_id,
2047 nvl(l_line_tbl(i).dest_subinventory, l_header_rec.dest_subinventory), /* Bug 7242187*/
2048 'N',
2049 l_line_type_rec.order_type_lookup_code,
2050 l_line_type_rec.purchase_basis,
2051 l_line_Type_rec.matching_basis --Cancel_Flag
2052 );
2053
2054 -- create req distributions
2055 -- It is assumed that only 1 dIstribution line will be there for each
2056 -- INTERNAL Requisition. If Multiple Distributions Lines are to created
2057 -- this procedure should be modified
2058
2059 -- Get Distribution ID from the Distribution Sequence
2060 OPEN dist_line_id_cur;
2061 FETCH dist_line_id_cur INTO l_dist_rec.distribution_id;
2062 CLOSE dist_line_id_cur;
2063
2064 -- Assign Requisition Line ID if NULL
2065 l_dist_rec.requisition_line_id := l_line_tbl(i).requisition_line_id;
2066 l_dist_rec.org_id := l_org_id;
2067
2068 -- Assign Requisition Quantity if NULL
2069 l_dist_rec.req_line_quantity := l_line_tbl(i).ordered_quantity;
2070
2071 -- Assign Requisition Line Number as Distribution Number
2072 l_dist_rec.distribution_num := l_line_tbl(i).line_num;
2073
2074 -- Assign SYSDATE to gl_encumbered_date
2075 l_dist_rec.gl_encumbered_date := l_today;
2076 l_dist_rec.prevent_encumbrance_flag := 'N';
2077 --s_chart_of_accounts_id := 101;
2078
2079 -- Get Charge Account ID
2080 l_dist_rec.code_combination_id := get_charge_account_fun
2081 (l_header_rec.dest_organization_id,
2082 l_line_tbl(i).inventory_item_id,
2083 l_line_Tbl(i).dest_subinventory);
2084
2085 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2086 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2087 'csp.plsql.csp_parts_order.process_order',
2088 'Checking Valid Account Id...');
2089 end if;
2090
2091
2092 -- Check for valid charge account. If Invalid Raise ERROR
2093 IF NOT valid_account_id_fun (l_dist_rec.code_combination_id,
2094 l_dist_rec.gl_encumbered_date,
2095 l_chart_of_accounts_id) THEN
2096 Raise INVALID_CHARGE_ACCOUNT;
2097 END IF;
2098
2099 -- Get Accrual Account ID and Variance Account ID for the
2100 --Destination Organization from MTL_PARAMETERS
2101
2102 OPEN accrual_account_id_cur (l_header_Rec.dest_organization_id);
2103 FETCH accrual_account_id_cur
2104 INTO l_dist_rec.accrual_account_id,
2105 l_dist_rec.variance_account_id;
2106 CLOSE accrual_account_id_cur;
2107
2108 -- Check for valid accrual account. If Invalid Raise ERROR
2109 IF NOT valid_account_id_fun (l_dist_rec.accrual_account_id,
2110 l_dist_rec.gl_encumbered_date,
2111 l_chart_of_accounts_id) THEN
2112 Raise INVALID_ACCRUAL_ACCOUNT;
2113 END IF;
2114
2115 -- Check for valid variance account. If Invalid Raise ERROR
2116 IF NOT valid_account_id_fun (l_dist_rec.variance_account_id,
2117 l_dist_rec.gl_encumbered_date,
2118 l_chart_of_accounts_id) THEN
2119 Raise INVALID_VARIANCE_ACCOUNT;
2120 END IF;
2121
2122 -- Assign Set of Books ID
2123 l_dist_rec.set_of_books_id := l_set_of_books_id;
2124
2125 -- Get Requisition Encumbrance Flag for Financial System Parameters
2126
2130
2127 -- If Req_Encumbrance_flag = 'Y' populate Budget Account ID for
2128 -- Req Distribution
2129 -- If gl_encumbered_flag = 'N' then don't populate gl_encumbered_date
2131 OPEN req_encumbrance_cur (l_dist_rec.set_of_books_id);
2132 FETCH req_encumbrance_cur INTO l_dist_rec.encumbered_flag;
2133 CLOSE req_encumbrance_cur;
2134
2135 IF l_dist_rec.encumbered_flag = 'Y' THEN
2136 OPEN budget_account_cur (l_header_rec.dest_organization_id,
2137 l_line_tbl(i).inventory_item_id);
2138 FETCH budget_account_cur INTO l_dist_rec.budget_account_id;
2139 CLOSE budget_account_cur;
2140
2141 -- Check for valid budget account. If Invalid Raise ERROR
2142 IF NOT valid_account_id_fun (l_dist_rec.budget_account_id,
2143 l_dist_rec.gl_encumbered_date,
2144 l_chart_of_accounts_id) THEN
2145 Raise INVALID_BUDGET_ACCOUNT;
2146 END IF;
2147 ELSE
2148 l_dist_rec.gl_encumbered_date := '';
2149 END IF;
2150
2151 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2152 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2153 'csp.plsql.csp_parts_order.process_order',
2154 'Inserting data into po_req_distributions ...');
2155 end if;
2156
2157 -- create po_req_distributions
2158 INSERT INTO po_req_distributions(
2159 distribution_id
2160 ,last_update_date
2161 ,last_updated_by
2162 ,requisition_line_id
2163 ,set_of_books_id
2164 ,code_combination_id
2165 ,req_line_quantity
2166 ,last_update_login
2167 ,creation_date
2168 ,created_by
2169 ,encumbered_flag
2170 ,gl_encumbered_date
2171 ,gl_encumbered_period_name
2172 ,gl_cancelled_date
2173 ,failed_funds_lookup_code
2174 ,encumbered_amount
2175 ,budget_account_id
2176 ,accrual_account_id
2177 ,variance_account_id
2178 ,prevent_encumbrance_flag
2179 ,attribute_category
2180 ,attribute1
2181 ,attribute2
2182 ,attribute3
2183 ,attribute4
2184 ,attribute5
2185 ,attribute6
2186 ,attribute7
2187 ,attribute8
2188 ,attribute9
2189 ,attribute10
2190 ,attribute11
2191 ,attribute12
2192 ,attribute13
2193 ,attribute14
2194 ,attribute15
2195 ,ussgl_transaction_code
2196 ,government_context
2197 ,project_id
2198 ,task_id
2199 ,expenditure_type
2200 ,project_accounting_context
2201 ,expenditure_organization_id
2202 ,gl_closed_date
2203 ,source_req_distribution_id
2204 ,distribution_num
2205 ,project_related_flag
2206 ,expenditure_item_date
2207 ,org_id
2208 ,allocation_type
2209 ,allocation_value
2210 ,award_id
2211 ,end_item_unit_number
2212 ,recoverable_tax
2213 ,nonrecoverable_tax
2214 ,recovery_rate
2215 ,tax_recovery_override_flag
2216 ,oke_contract_line_id
2217 ,oke_contract_deliverable_id
2218 )
2219 VALUES
2220 (
2221 l_dist_rec.distribution_id
2222 ,l_today --last_update_date
2223 ,l_user_id --last_updated_by
2224 ,l_dist_rec.requisition_line_id
2225 ,l_dist_rec.set_of_books_id
2226 ,l_dist_rec.code_combination_id
2227 ,l_dist_rec.req_line_quantity
2228 ,l_login_id --last_update_login
2229 ,l_today --creation_date
2230 ,l_user_id --created_by
2231 ,l_dist_rec.encumbered_flag
2232 ,l_dist_rec.gl_encumbered_date
2233 ,l_dist_rec.gl_encumbered_period_name
2234 ,l_dist_rec.gl_cancelled_date
2235 ,l_dist_rec.failed_funds_lookup_code
2236 ,l_dist_rec.encumbered_amount
2237 ,l_dist_rec.budget_account_id
2238 ,l_dist_rec.accrual_account_id
2239 ,l_dist_rec.variance_account_id
2240 ,l_dist_rec.prevent_encumbrance_flag
2241 ,l_dist_rec.attribute_category
2242 ,l_dist_rec.attribute1
2243 ,l_dist_rec.attribute2
2244 ,l_dist_rec.attribute3
2245 ,l_dist_rec.attribute4
2246 ,l_dist_rec.attribute5
2247 ,l_dist_rec.attribute6
2248 ,l_dist_rec.attribute7
2249 ,l_dist_rec.attribute8
2250 ,l_dist_rec.attribute9
2251 ,l_dist_rec.attribute10
2252 ,l_dist_rec.attribute11
2256 ,l_dist_rec.attribute15
2253 ,l_dist_rec.attribute12
2254 ,l_dist_rec.attribute13
2255 ,l_dist_rec.attribute14
2257 ,l_dist_rec.ussgl_transaction_code
2258 ,l_dist_rec.government_context
2259 ,l_dist_rec.project_id
2260 ,l_dist_rec.task_id
2261 ,l_dist_rec.expenditure_type
2262 ,l_dist_rec.project_accounting_context
2263 ,l_dist_rec.expenditure_organization_id
2264 ,l_dist_rec.gl_closed_date
2265 ,l_dist_rec.source_req_distribution_id
2266 ,l_dist_rec.distribution_num
2267 ,l_dist_rec.project_related_flag
2268 ,l_dist_rec.expenditure_item_date
2269 ,l_dist_rec.org_id
2270 ,l_dist_rec.allocation_type
2271 ,l_dist_rec.allocation_value
2272 ,l_dist_rec.award_id
2273 ,l_dist_rec.end_item_unit_number
2274 ,l_dist_rec.recoverable_tax
2275 ,l_dist_rec.nonrecoverable_tax
2276 ,l_dist_rec.recovery_rate
2277 ,l_dist_rec.tax_recovery_override_flag
2278 ,l_dist_rec.oke_contract_line_id
2279 ,l_dist_rec.oke_contract_deliverable_id
2280 );
2281
2282
2283 END IF;
2284
2285 IF (p_process_type in ('ORDER', 'BOTH')) THEN
2286 -- SETTING UP THE ORDER PROCESS LINE RECORD
2287
2288 /* Same as 115.10 bug 5362711 but for R12 */
2289 /* Get Unit Price and Currency Code*/
2290 get_unit_price_prc (l_line_tbl(i).inventory_item_id
2291 ,l_line_tbl(i).source_organization_id
2292 ,l_header_rec.dest_organization_id
2293 ,l_set_of_books_id
2294 ,l_chart_of_accounts_id
2295 ,l_currency_code
2296 ,l_unit_price
2297 ,l_item_cost );
2298
2299 IF l_line_tbl(i).order_line_id IS NULL THEN
2300 select oe_order_lines_s.nextval
2301 into l_line_tbl(i).order_line_id
2302 from dual;
2303 END IF;
2304
2305 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2306 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2307 'csp.plsql.csp_parts_order.process_order',
2308 'l_line_tbl(i).order_line_id ('
2309 || to_char(i) || ') = ' || l_line_tbl(i).order_line_id);
2310 end if;
2311
2312 l_oe_line_rec.org_id := l_source_operating_unit;
2313 l_oe_line_rec.header_id := l_oe_header_rec.header_id;
2314 l_oe_line_rec.line_id := l_line_tbl(i).order_line_id;
2315 l_oe_line_rec.line_number := l_line_tbl(i).line_num;
2316 /*
2317 IF (nvl(l_scheduling_code, 'THREE') = 'THREE' OR
2318 nvl(l_scheduling_code, 'THREE') = 'FOUR') THEN
2319 l_oe_line_rec.reserved_quantity := l_line_tbl(i).ordered_quantity;
2320 END IF;
2321 */
2322 l_oe_line_rec.line_type_id := l_order_line_type_id;
2323 l_oe_line_rec.inventory_item_id := l_line_tbl(i).inventory_item_id;
2324 l_oe_line_rec.item_revision := l_line_tbl(i).revision;
2325 l_oe_line_rec.order_quantity_uom := l_line_tbl(i).unit_of_measure;
2326 IF (l_line_price_list_id IS NOT NULL) THEN
2327 l_oe_line_rec.price_list_id := l_line_price_list_id;
2328 END IF;
2329 l_oe_line_rec.ORIG_SYS_DOCUMENT_REF := l_header_rec.requisition_number;
2330 l_oe_line_rec.ORIG_SYS_LINE_REF := l_line_tbl(i).line_num;
2331 l_oe_line_rec.ship_from_org_id := l_line_tbl(i).source_organization_id;
2332 IF (l_oe_line_rec.subinventory IS NOT NULL) THEN
2333 l_oe_line_rec.subinventory := l_line_tbl(i).source_subinventory;
2334 END IF;
2335 l_oe_line_rec.request_date := nvl(l_header_rec.need_by_date,sysdate);
2336 l_oe_line_rec.promise_date := nvl(l_header_rec.need_by_date,sysdate);
2337 l_oe_line_rec.actual_arrival_date := l_line_tbl(i).arrival_date;
2338 l_oe_line_rec.sold_to_org_id := l_cust_acct_id;
2339 l_oe_line_rec.ship_to_org_id := l_site_use_id;
2340
2341 -- bug # 6471559
2342 if l_bill_to_site_use_id is not NULL then
2343 l_oe_line_rec.invoice_to_org_id := l_bill_to_site_use_id;
2344 end if;
2345
2346 l_oe_line_rec.line_category_code := l_order_line_category_code;
2347 l_oe_line_rec.order_source_id := l_order_source_id;
2348 l_oe_line_rec.source_document_type_id := l_order_source_id;
2349 l_oe_line_rec.source_document_id := l_header_rec.requisition_header_id;
2350 l_oe_line_rec.source_document_line_id := l_line_tbl(i).requisition_line_id;
2351 l_oe_line_rec.ship_set := l_line_tbl(i).ship_complete;
2352 l_oe_line_Rec.shipping_method_code := nvl(l_line_tbl(i).shipping_method_code, FND_API.G_MISS_CHAR);
2353 l_oe_line_Rec.calculate_price_flag := 'N';
2354 l_oe_line_Rec.unit_list_price := l_item_cost;
2355 l_oe_line_Rec.unit_Selling_price := l_item_cost;
2356 l_oe_line_Rec.open_flag := 'Y';
2360 END IF;
2357 l_oe_line_rec.ordered_quantity := l_line_tbl(i).ordered_quantity;
2358 l_oe_line_rec.booked_flag := l_line_tbl(i).booked_Flag; --N;
2359 l_oe_line_rec.operation := OE_GLOBALS.G_OPR_CREATE;
2361
2362 ELSIF (l_header_rec.operation = CSP_PARTS_ORDER.G_OPR_UPDATE) THEN
2363 IF (p_process_Type = 'REQUISITION') THEN
2364 IF (l_line_Tbl(I).requisition_line_id IS NULL) THEN
2365 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
2366 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_line_rec.requisition_line_id', FALSE);
2367 FND_MSG_PUB.ADD;
2368 RAISE EXCP_USER_DEFINED;
2369 END IF;
2370
2371 -- update requisition line table with new quantity
2372 -- quantity is the only change allowed
2373 update po_requisition_lines
2374 set quantity = l_line_tbl(I).ordered_quantity
2375 where requisition_line_id = l_line_Tbl(I).requisition_line_id;
2376
2377 -- update req distributions with new quantity
2378 update po_req_distributions
2379 set req_line_quantity = l_line_tbl(i).ordered_quantity
2380 where requisition_line_id = l_line_tbl(i).requisition_line_id;
2381
2382 -- update mtl_supply data for the requisition
2383 IF NOT po_supply.po_req_supply(
2384 p_docid => null,
2385 p_lineid => l_line_Tbl(I).requisition_line_id,
2386 p_shipid => null,
2387 p_action => 'Update_Req_Line_Qty',
2388 p_recreate_flag => NULL,
2389 p_qty => l_line_tbl(i).ordered_quantity,
2390 p_receipt_date => NULL) THEN
2391
2392 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
2393 token1 => 'FILE',
2394 value1 => 'PO_SUPPLY',
2395 token2 => 'ERR_NUMBER',
2396 value2 => '035',
2397 token3 => 'SUBROUTINE',
2398 value3 => 'PO_REQ_SUPPLY()');
2399 RAISE FND_API.G_EXC_ERROR;
2400 END IF;
2401
2402 ELSIF (p_process_type in ('ORDER', 'BOTH')) THEN
2403 IF (l_line_tbl(i).order_line_id IS NULL) THEN
2404 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
2405 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'l_line_rec.order_line_id', FALSE);
2406
2407 FND_MSG_PUB.ADD;
2408 RAISE EXCP_USER_DEFINED;
2409 END IF;
2410
2411 -- l_oe_line_Rec := oe_line_util.Query_Row(l_line_tbl(i).order_line_id);
2412
2413 l_oe_line_rec.line_id := l_line_tbl(i).order_line_id;
2414 l_oe_line_rec.booked_flag := l_line_tbl(i).booked_Flag;
2415 l_oe_line_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
2416 END If;
2417
2418 END IF;
2419
2420 --l_oe_line_rec.ordered_quantity := l_line_tbl(i).ordered_quantity;
2421 --l_oe_line_rec.booked_flag := l_line_tbl(i).booked_Flag; --N;
2422
2423 -- Adding this record to the line table to be passed to process order
2424 l_oe_line_tbl(i) := l_oe_line_rec;
2425
2426 END LOOP;
2427
2428 -- create supply information for requisitions created
2429 IF (p_process_type in ('REQUISITION', 'BOTH') AND l_header_Rec.operation = G_OPR_CREATE) THEN
2430
2431 /*IF NOT po_supply.po_req_supply(
2432 p_docid => l_header_rec.requisition_header_id,
2433 p_lineid => null,
2434 p_shipid => null,
2435 p_action => 'Approve_Req_Supply',
2436 p_recreate_flag => NULL,
2437 p_qty => NULL,
2438 p_receipt_date => NULL) THEN
2439
2440
2441 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
2442 token1 => 'FILE',
2443 value1 => 'PO_SUPPLY',
2444 token2 => 'ERR_NUMBER',
2445 value2 => '005',
2446 token3 => 'SUBROUTINE',
2447 value3 => 'PO_REQ_SUPPLY()');
2448 END IF;
2449 */
2450 BEGIN
2451
2452 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2453 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2454 'csp.plsql.csp_parts_order.process_order',
2455 'Deleting record from mtl_supply...');
2456 end if;
2457
2458 DELETE FROM mtl_supply ms1
2459 WHERE ms1.supply_source_id IN
2460 (
2461 SELECT pl.requisition_line_id
2462 FROM po_requisition_lines pl
2463 WHERE pl.requisition_header_id = l_header_rec.requisition_header_id
2464 AND NVL(pl.modified_by_agent_flag, 'N') <> 'Y'
2465 AND NVL(pl.closed_code, 'OPEN') = 'OPEN'
2466 AND NVL(pl.cancel_flag, 'N') = 'N'
2467 AND pl.line_location_id IS NULL
2471 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2468 )
2469 AND ms1.supply_type_code = 'REQ';
2470
2472 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2473 'csp.plsql.csp_parts_order.process_order',
2474 'Inserting data into mtl_supply...');
2475 end if;
2476
2477 INSERT INTO mtl_supply
2478 (supply_type_code,
2479 supply_source_id,
2480 last_updated_by,
2481 last_update_date,
2482 last_update_login,
2483 created_by,
2484 creation_date,
2485 req_header_id,
2486 req_line_id,
2487 item_id,
2488 item_revision,
2489 quantity,
2490 unit_of_measure,
2491 receipt_date,
2492 need_by_date,
2493 destination_type_code,
2494 location_id,
2495 from_organization_id,
2496 from_subinventory,
2497 to_organization_id,
2498 to_subinventory,
2499 change_flag)
2500 SELECT 'REQ',
2501 prl.requisition_line_id,
2502 last_updated_by,
2503 last_update_date,
2504 last_update_login,
2505 created_by,
2506 creation_date,
2507 prl.requisition_header_id,
2508 prl.requisition_line_id,
2509 prl.item_id,
2510 decode(prl.source_type_code,'INVENTORY', null,
2511 prl.item_revision),
2512 prl.quantity - ( nvl(prl.QUANTITY_CANCELLED, 0) +
2513 nvl(prl.QUANTITY_DELIVERED, 0) ),
2514 prl.unit_meas_lookup_code,
2515 prl.need_by_date,
2516 prl.need_by_date,
2517 prl.destination_type_code,
2518 prl.deliver_to_location_id,
2519 prl.source_organization_id,
2520 prl.source_subinventory,
2521 prl.destination_organization_id,
2522 prl.destination_subinventory,
2523 'Y'
2524 FROM po_requisition_lines prl
2525 WHERE prl.requisition_header_id = l_header_rec.requisition_header_id
2526 AND nvl(prl.modified_by_agent_flag,'N') <> 'Y'
2527 AND nvl(prl.CLOSED_CODE,'OPEN') = 'OPEN'
2528 AND nvl(prl.CANCEL_FLAG, 'N') = 'N'
2529 -- <Doc Manager Rewrite R12>: Filter out amount basis
2530 AND prl.matching_basis <> 'AMOUNT'
2531 AND prl.line_location_id is null
2532 AND not exists
2533 (SELECT 'supply exists'
2534 FROM mtl_supply ms
2535 WHERE ms.supply_type_code = 'REQ'
2536 AND ms.supply_source_id = prl.requisition_line_id);
2537 EXCEPTION
2538 when no_data_found THEN
2539
2540 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
2541 token1 => 'FILE',
2542 value1 => 'PO_SUPPLY',
2543 token2 => 'ERR_NUMBER',
2544 value2 => '005',
2545 token3 => 'SUBROUTINE',
2546 value3 => 'PO_REQ_SUPPLY()');
2547 RAISE FND_API.G_EXC_ERROR;
2548 END;
2549
2550
2551 END IF;
2552 BEGIN
2553 update mtl_supply
2554 set expected_delivery_date = nvl(l_header_rec.need_by_date, sysdate),
2555 need_by_date = nvl(l_header_rec.need_by_date, sysdate)
2556 where req_header_id = l_header_rec.requisition_header_id;
2557 EXCEPTION
2558 WHEN no_data_found THEN
2559 null;
2560 END;
2561
2562 IF (p_process_type in ('ORDER', 'BOTH')) THEN
2563 -- cross operating unit order
2564 IF (l_source_operating_unit <> l_org_id) THEN
2565 OPEN get_new_context(l_source_operating_unit);
2566 FETCH get_new_context
2567 INTO new_user_id,new_resp_id,new_resp_appl_id;
2568 CLOSE get_new_context;
2569
2570 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2571 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2572 'csp.plsql.csp_parts_order.process_order',
2573 'new_user_id = ' || new_user_id
2574 || ', new_resp_id = ' || new_resp_id
2575 || ', new_resp_appl_id = ' || new_resp_appl_id);
2576 end if;
2577
2578 IF new_resp_id is not null and
2579 new_resp_appl_id is not null THEN
2580 fnd_global.apps_initialize(new_user_id,new_resp_id,new_resp_appl_id);
2581 mo_global.set_org_context(l_source_operating_unit,null,'CSF');
2582 /* fnd_profile.get('ORG_ID',new_org_id); --Operating Unit for the new context.
2583 IF l_source_operating_unit <> new_org_id THEN
2584 FND_MESSAGE.Set_Name('CS','CS_CHG_NEW_CONTEXT_OU_NOT_MATCH'); --to be seeded.
2588 END IF;
2585 FND_MESSAGE.Set_Token('ROUTINE',l_api_name_full);
2586 FND_MSG_PUB.Add;
2587 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2589 */
2590 ELSE
2591 dbms_application_info.set_client_info(l_source_operating_unit);
2592 END IF;
2593 END If;
2594
2595 -- CONTROL RECORD
2596 -- Use the default settings
2597 l_oe_control_rec.controlled_operation := FALSE;
2598 l_oe_control_rec.default_Attributes := TRUE;
2599
2600 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2601 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2602 'csp.plsql.csp_parts_order.process_order',
2603 'Now finally calling OE_Order_PUB.Process_Order for operation = '
2604 || l_oe_header_rec.operation);
2605 end if;
2606
2607 -- CALL TO PROCESS ORDER
2608 IF (l_oe_header_rec.operation = OE_GLOBALS.G_OPR_CREATE) THEN
2609 -- CALL TO PROCESS ORDER
2610 l_action_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;
2611 l_action_request_tbl(1).request_type := OE_GLOBALS.G_BOOK_ORDER;
2612 OE_Order_PUB.Process_Order(
2613 p_org_id => l_source_operating_unit
2614 ,p_api_version_number => l_api_version_number
2615 ,p_init_msg_list => FND_API.G_TRUE
2616 ,p_return_values => FND_API.G_FALSE
2617 ,p_action_commit => FND_API.G_FALSE
2618 -- Passing just the entity records that are a part of this order
2619
2620 ,p_header_rec => l_oe_header_rec
2621 ,p_line_tbl => l_oe_line_tbl
2622 ,p_action_request_tbl => l_action_request_tbl
2623 -- OUT variables
2624 ,x_header_rec => lx_oe_header_rec
2625 ,x_header_val_rec => l_oe_Header_Val_rec
2626 ,x_header_adj_tbl => l_oe_header_adj_tbl
2627 ,x_Header_Adj_val_tbl => l_oe_header_adj_val_tbl
2628 ,x_Header_price_Att_tbl => l_oe_header_price_att_tbl
2629 ,x_Header_Adj_Att_tbl => l_oe_Header_Adj_Att_Tbl
2630 ,x_Header_Adj_Assoc_tbl => l_oe_Header_Adj_Assoc_Tbl
2631 ,x_header_scredit_tbl => l_oe_header_scr_tbl
2632 ,x_Header_Scredit_val_tbl => l_oe_Header_Scredit_Val_Tbl
2633 ,x_line_tbl => lx_oe_line_tbl
2634 ,x_line_val_tbl => l_oe_Line_Val_Tbl
2635 ,x_line_adj_tbl => l_oe_line_adj_tbl
2636 ,x_Line_Adj_val_tbl => l_oe_Line_Adj_Val_Tbl
2637 ,x_Line_price_Att_tbl => l_oe_Line_Price_Att_Tbl
2638 ,x_Line_Adj_Att_tbl => l_oe_Line_Adj_Att_Tbl
2639 ,x_Line_Adj_Assoc_tbl => l_oe_Line_Adj_Assoc_Tbl
2640 ,x_Line_Scredit_tbl => l_oe_line_scr_tbl
2641 ,x_Line_Scredit_val_tbl => l_oe_Line_Scredit_Val_Tbl
2642 ,x_Lot_Serial_tbl => l_oe_Lot_Serial_Tbl
2643 ,x_Lot_Serial_val_tbl => l_oe_Lot_Serial_Val_Tbl
2644 ,x_action_request_tbl => l_oe_Request_Tbl_Type
2645 ,x_return_status => l_return_status
2646 ,x_msg_count => l_msg_count
2647 ,x_msg_data => l_msg_data
2648 );
2649 ELSE
2650 --p_action_request_tbl => l_action_request_tbl
2651 OE_Order_PUB.Process_Order(
2652 p_org_id => l_source_operating_unit
2653 ,p_api_version_number => l_api_version_number
2654 ,p_init_msg_list => FND_API.G_TRUE
2655 ,p_return_values => FND_API.G_FALSE
2656 ,p_action_commit => FND_API.G_FALSE
2657 -- Passing just the entity records that are a part of this order
2658 -- OUT variables
2659 ,x_header_rec => lx_oe_header_rec
2660 ,x_header_val_rec => l_oe_Header_Val_rec
2661 ,x_header_adj_tbl => l_oe_header_adj_tbl
2662 ,x_Header_Adj_val_tbl => l_oe_header_adj_val_tbl
2663 ,x_Header_price_Att_tbl => l_oe_header_price_att_tbl
2664 ,x_Header_Adj_Att_tbl => l_oe_Header_Adj_Att_Tbl
2665 ,x_Header_Adj_Assoc_tbl => l_oe_Header_Adj_Assoc_Tbl
2666 ,x_header_scredit_tbl => l_oe_header_scr_tbl
2667 ,x_Header_Scredit_val_tbl => l_oe_Header_Scredit_Val_Tbl
2668 ,x_line_tbl => lx_oe_line_tbl
2669 ,x_line_val_tbl => l_oe_Line_Val_Tbl
2670 ,x_line_adj_tbl => l_oe_line_adj_tbl
2671 ,x_Line_Adj_val_tbl => l_oe_Line_Adj_Val_Tbl
2672 ,x_Line_price_Att_tbl => l_oe_Line_Price_Att_Tbl
2673 ,x_Line_Adj_Att_tbl => l_oe_Line_Adj_Att_Tbl
2674 ,x_Line_Adj_Assoc_tbl => l_oe_Line_Adj_Assoc_Tbl
2675 ,x_Line_Scredit_tbl => l_oe_line_scr_tbl
2676 ,x_Line_Scredit_val_tbl => l_oe_Line_Scredit_Val_Tbl
2677 ,x_Lot_Serial_tbl => l_oe_Lot_Serial_Tbl
2681 ,x_msg_count => l_msg_count
2678 ,x_Lot_Serial_val_tbl => l_oe_Lot_Serial_Val_Tbl
2679 ,x_action_request_tbl => l_oe_Request_Tbl_Type
2680 ,x_return_status => l_return_status
2682 ,x_msg_data => l_msg_data
2683 );
2684 END IF;
2685 -- dbms_application_info.set_client_info(l_org_id);
2686 IF (l_source_operating_unit <> l_org_id) THEN
2687 fnd_global.apps_initialize(l_user_id,orig_resp_id,orig_resp_appl_id);
2688 mo_global.set_org_context(l_org_id,null,'CSF');
2689
2690 END If;
2691
2692 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2693 for counter in REVERSE 1..l_msg_count Loop
2694 l_msg := OE_MSG_PUB.Get(counter,FND_API.G_FALSE) ;
2695 FND_MESSAGE.SET_NAME('CSP', 'CSP_PROCESS_ORDER_ERRORS');
2696 FND_MESSAGE.SET_TOKEN('OM_MSG', l_msg, FALSE);
2697 FND_MSG_PUB.ADD;
2698 fnd_msg_pub.count_and_get
2699 ( p_count => x_msg_count
2700 , p_data => x_msg_data);
2701 End loop;
2702 x_return_status := FND_API.G_RET_STS_ERROR;
2703
2704 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2705 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2706 'csp.plsql.csp_parts_order.process_order',
2707 'Error in OE_Order_PUB.Process_Order API... Message = '
2708 || l_msg);
2709 end if;
2710
2711 RAISE FND_API.G_EXC_ERROR;
2712 ELSE
2713 -- assign output variables with respected values if operation is CREATE
2714
2715 IF (l_header_Rec.operation = G_OPR_CREATE) THEN
2716 l_header_rec.order_header_id := lx_oe_header_rec.header_id;
2717
2718 FOR i in 1..lx_oe_line_tbl.count LOOP
2719 l_line_tbl(i).order_line_id := lx_oe_line_tbl(i).line_id;
2720 END LOOP;
2721 px_header_rec := l_header_rec;
2722 px_line_table := l_line_tbl;
2723 ELSIF (l_header_rec.operation = G_OPR_UPDATE) THEN
2724 -- update requisition line table with new quantity
2725 -- quantity is the only change allowed
2726 FOR i in 1..lx_oe_line_tbl.count LOOP
2727 IF (lx_oe_line_tbl(I).ordered_quantity IS NOT NULL OR
2728 lx_oe_line_tbl(I).ordered_quantity <> FND_API.G_MISS_NUM) THEN
2729 update po_requisition_lines
2730 set quantity = lx_oe_line_tbl(I).ordered_quantity
2731 where requisition_line_id = lx_oe_line_Tbl(I).source_document_line_id;
2732
2733
2734 -- update req distributions
2735 update po_req_distributions
2736 set req_line_quantity = lx_oe_line_tbl(I).ordered_quantity
2737 where requisition_line_id = lx_oe_line_Tbl(I).source_document_line_id;
2738
2739
2740 -- update mtl_supply data for the requisition
2741 IF NOT po_supply.po_req_supply(
2742 p_docid => null,
2743 p_lineid => lx_oe_line_Tbl(I).source_document_line_id,
2744 p_shipid => null,
2745 p_action => 'Update_Req_Line_Qty',
2746 p_recreate_flag => NULL,
2747 p_qty => lx_oe_line_tbl(I).ordered_quantity,
2748 p_receipt_date => NULL) THEN
2749
2750 PO_MESSAGE_S.APP_ERROR(error_name => 'PO_ALL_TRACE_ERROR',
2751 token1 => 'FILE',
2752 value1 => 'PO_SUPPLY',
2753 token2 => 'ERR_NUMBER',
2754 value2 => '035',
2755 token3 => 'SUBROUTINE',
2756 value3 => 'PO_REQ_SUPPLY()');
2757 RAISE FND_API.G_EXC_ERROR;
2758 END IF;
2759 END IF;
2760 END LOOP;
2761 END If;
2762 END IF;
2763 END IF;
2764
2765 px_header_rec := l_header_rec;
2766 px_line_table := l_line_tbl;
2767
2768 fnd_msg_pub.count_and_get
2769 ( p_count => x_msg_count
2770 , p_data => x_msg_data);
2771
2772 END If;
2773 EXCEPTION
2774 WHEN FND_API.G_EXC_ERROR THEN
2775 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2776 P_API_NAME => L_API_NAME
2777 ,P_PKG_NAME => G_PKG_NAME
2778 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2779 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2780 ,X_MSG_COUNT => X_MSG_COUNT
2781 ,X_MSG_DATA => X_MSG_DATA
2782 ,X_RETURN_STATUS => X_RETURN_STATUS);
2783 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2784 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2785 P_API_NAME => L_API_NAME
2786 ,P_PKG_NAME => G_PKG_NAME
2787 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2788 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2789 ,X_MSG_COUNT => X_MSG_COUNT
2793 WHEN INVALID_CHARGE_ACCOUNT THEN
2790 ,X_MSG_DATA => X_MSG_DATA
2791 ,X_RETURN_STATUS => X_RETURN_STATUS);
2792
2794 po_message_s.app_error('PO_RI_INVALID_CHARGE_ACC_ID');
2795 raise;
2796
2797 WHEN INVALID_ACCRUAL_ACCOUNT THEN
2798 po_message_s.app_error('PO_RI_INVALID_ACCRUAL_ACC_ID');
2799 raise;
2800
2801 WHEN INVALID_BUDGET_ACCOUNT THEN
2802 po_message_s.app_error('PO_RI_INVALID_BUDGET_ACC_ID');
2803 raise;
2804
2805 WHEN INVALID_VARIANCE_ACCOUNT THEN
2806 po_message_s.app_error('PO_RI_INVALID_VARIANCE_ACC_ID');
2807 raise;
2808
2809 WHEN OTHERS THEN
2810 Rollback to process_order_pub;
2811 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2812 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
2813 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
2814 FND_MSG_PUB.ADD;
2815 fnd_msg_pub.count_and_get
2816 ( p_count => x_msg_count
2817 , p_data => x_msg_data);
2818 x_return_status := FND_API.G_RET_STS_ERROR;
2819 END;
2820
2821
2822
2823
2824 /**************************************************************************
2825 ***************************************************************************
2826 ***************************************************************************
2827 PROCESS_PURCHASE_REQUSITION
2828 ***************************************************************************
2829 ***************************************************************************
2830 ***************************************************************************/
2831
2832
2833 PROCEDURE process_purchase_req(
2834 p_api_version IN NUMBER
2835 ,p_Init_Msg_List IN VARCHAR2
2836 ,p_commit IN VARCHAR2
2837 ,px_header_rec IN OUT NOCOPY csp_parts_requirement.header_rec_type
2838 ,px_line_table IN OUT NOCOPY csp_parts_requirement.Line_Tbl_type
2839 ,x_return_status OUT NOCOPY VARCHAR2
2840 ,x_msg_count OUT NOCOPY NUMBER
2841 ,x_msg_data OUT NOCOPY VARCHAR2
2842 )
2843 IS
2844
2845 l_api_version_number CONSTANT NUMBER := 1.0;
2846 l_api_name CONSTANT VARCHAR2(30) := 'process_purchase_req';
2847 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2848 l_msg_count NUMBER;
2849 l_msg_data VARCHAR2(2000);
2850 l_commit VARCHAR2(1) := FND_API.G_FALSE;
2851 l_user_id NUMBER;
2852 l_login_id NUMBER;
2853 l_today DATE;
2854 l_employee_id NUMBER;
2855 l_org_id NUMBER;
2856 l_line_type_id NUMBER;
2857 l_dummy NUMBER;
2858
2859 l_header_rec csp_parts_requirement.header_rec_type;
2860 l_line_rec csp_parts_requirement.line_rec_type;
2861 l_line_tbl csp_parts_requirement.Line_tbl_type;
2862
2863 l_gl_encumbered_date DATE;
2864 l_prevent_encumbrance_flag VARCHAR2(1);
2865 l_chart_of_accounts_id NUMBER;
2866 l_charge_account_id NUMBER;
2867 l_unit_of_measure VARCHAR2(30);
2868 l_justification VARCHAR2(480);
2869 l_note_to_buyer VARCHAR2(480);
2870 l_note1_id NUMBER;
2871 l_note1_title VARCHAR2(80);
2872 l_SUGGESTED_VENDOR_ID NUMBER;
2873 l_SUGGESTED_VENDOR_NAME VARCHAR2(240);
2874 l_source_organization_id NUMBER;
2875 l_autosource_flag VARCHAR2(10);
2876
2877 EXCP_USER_DEFINED EXCEPTION;
2878 INVALID_CHARGE_ACCOUNT EXCEPTION;
2879
2880 -- Get requisition_number (PO_REQUSITION_HEADERS.segment1)
2881 CURSOR req_number_cur IS
2882 SELECT to_char(current_max_unique_identifier + 1)
2883 FROM po_unique_identifier_control
2884 WHERE table_name = 'PO_REQUISITION_HEADERS'
2885 FOR UPDATE OF current_max_unique_identifier;
2886
2887 -- Get unique requisition_header_id
2888 CURSOR req_header_id_cur IS
2889 SELECT po_requisition_headers_s.nextval
2890 FROM sys.dual;
2891
2892 -- Get unique requisition_line_id
2893 CURSOR req_line_id_cur IS
2894 SELECT po_requisition_lines_s.nextval
2895 FROM sys.dual;
2896
2897 -- Get preparer_id
2898 CURSOR employee_id_cur IS
2899 SELECT employee_id
2900 FROM fnd_user
2901 WHERE user_id = l_user_id;
2902
2903 BEGIN
2904
2905 SAVEPOINT Process_Order_PUB;
2906
2907 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
2908 -- initialize message list
2909 FND_MSG_PUB.initialize;
2910 END IF;
2911
2912 -- Standard call to check for call compatibility.
2913 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2914 p_api_version,
2915 l_api_name,
2916 G_PKG_NAME)
2917 THEN
2918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2919 END IF;
2920
2921 -- initialize return status
2922 x_return_status := FND_API.G_RET_STS_SUCCESS;
2923
2924 l_header_rec := px_header_rec;
2925 l_line_tbl := px_line_table;
2926
2927 -- Get data for populating who columns
2928 SELECT Sysdate INTO l_today FROM dual;
2929 l_user_id := nvl(fnd_global.user_id, 0) ;
2930 l_login_id := nvl(fnd_global.login_id, -1);
2931
2932 -- operating unit
2933 BEGIN
2934 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
2935 INTO l_org_id
2936 FROM dual;
2937 po_moac_utils_pvt.set_org_context(l_org_id);
2938 EXCEPTION
2939 WHEN NO_DATA_FOUND THEN
2940 null;
2941 END;
2942
2943 -- requisition_header_id
2944 IF l_header_rec.requisition_header_id is null then
2945 OPEN req_header_id_cur;
2946 FETCH req_header_id_cur into l_header_rec.requisition_header_id;
2947 CLOSE req_header_id_cur;
2948 END IF;
2949
2950 -- Requisition_number
2951 -- IF l_header_rec.requisition_number IS NULL THEN
2952 OPEN req_number_cur;
2953 FETCH req_number_cur INTO l_header_rec.requisition_number;
2954 UPDATE po_unique_identifier_control
2955 SET current_max_unique_identifier
2956 = current_max_unique_identifier + 1
2957 WHERE CURRENT of req_number_cur;
2958 CLOSE req_number_cur;
2959 --END IF;
2960
2961 -- preparer id
2962 IF l_user_id IS NOT NULL THEN
2963 OPEN employee_id_cur;
2964 FETCH employee_id_cur into l_employee_id;
2965 CLOSE employee_id_cur;
2966 END IF;
2967
2968 -- check for uniqueness of requisition_number
2969 BEGIN
2970
2971 SELECT 1 INTO l_dummy
2972 FROM DUAL
2973 WHERE NOT EXISTS
2974 ( SELECT 1
2975 FROM po_requisition_headers
2976 WHERE Segment1 = l_header_rec.requisition_number)
2977 AND NOT EXISTS
2978 ( SELECT 1
2979 FROM po_history_requisitions phr
2980 WHERE phr.segment1 = l_header_rec.requisition_number);
2981
2982 EXCEPTION
2983 WHEN NO_DATA_FOUND THEN
2984 po_message_s.app_error('PO_ALL_ENTER_UNIQUE');
2985 raise;
2986 WHEN OTHERS THEN
2987 po_message_s.sql_error('check_unique','010',sqlcode);
2988 raise;
2989 END;
2990
2991 FND_PROFILE.GET('CSP_PO_LINE_TYPE', l_line_Type_id);
2992
2993 FOR I IN 1..l_line_tbl.COUNT LOOP
2994
2995 -- get requisition_line_id
2996 IF (l_line_tbl(i).Requisition_Line_Id is NULL) THEN
2997 OPEN req_line_id_cur;
2998 FETCH req_line_id_cur INTO l_line_tbl(i).requisition_line_id;
2999 CLOSE req_line_id_cur;
3000 END IF;
3001
3002 -- Assign SYSDATE to gl_encumbered_date
3003 l_gl_encumbered_date := l_today;
3004 l_prevent_encumbrance_flag := 'N';
3005
3006 -- Get Charge Account ID
3007 l_charge_account_id := get_charge_account_fun(l_header_rec.dest_organization_id,
3008 l_line_tbl(i).inventory_item_id,
3009 l_line_tbl(i).dest_subinventory);
3010
3011 BEGIN
3012 SELECT unit_of_measure
3013 INTO l_unit_of_measure
3014 FROM mtl_item_uoms_view
3015 WHERE organization_id = l_header_rec.dest_organization_id
3016 AND inventory_item_id = l_line_Tbl(i).inventory_item_id
3017 AND uom_code = l_line_Tbl(i).unit_of_measure;
3018 EXCEPTION
3019 WHEN NO_DATA_FOUND THEN
3020 l_unit_of_measure := l_line_tbl(i).unit_of_measure;
3021 END;
3022
3023 /*
3024 -- Check for valid charge account. If Invalid Raise ERROR
3025 IF NOT valid_account_id_fun(l_charge_account_id,
3026 l_gl_encumbered_date,
3027 l_chart_of_accounts_id) THEN
3028 Raise INVALID_CHARGE_ACCOUNT;
3029 END IF;
3030 */
3031
3032
3033 If l_header_rec.CALLED_FROM = 'REPAIR_EXECUTION' then
3034 l_justification := l_header_rec.JUSTIFICATION;
3035 l_note_to_buyer := l_header_rec.NOTE_TO_BUYER;
3036 l_note1_id := l_header_rec.note1_id;
3037 l_note1_title := l_header_rec.note1_title;
3038 l_SUGGESTED_VENDOR_ID := l_header_rec.SUGGESTED_VENDOR_ID;
3039 l_SUGGESTED_VENDOR_NAME := l_header_rec.SUGGESTED_VENDOR_NAME;
3040 l_source_organization_id := l_line_tbl(i).source_organization_id;
3041 l_autosource_flag := 'N';
3042 Else
3043 l_justification := to_char(l_header_rec.need_by_date, 'DD-MON-RRRR HH:MI:SS');
3044 l_note_to_buyer := l_line_tbl(i).shipping_method_code;
3045 l_note1_id := null;
3046 l_note1_title := null;
3047 l_SUGGESTED_VENDOR_ID := null;
3048 l_SUGGESTED_VENDOR_NAME := null;
3049 l_source_organization_id := null;
3050 l_autosource_flag := null;
3051 End if;
3052
3053
3054 -- Insert into ReqImport Interface tables
3055 INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
3056 (CREATION_DATE,
3057 CREATED_BY,
3058 LAST_UPDATE_DATE,
3059 LAST_UPDATED_BY,
3060 LAST_UPDATE_LOGIN,
3061 INTERFACE_SOURCE_CODE,
3062 --INTERFACE_SOURCE_LINE_ID,
3063 SOURCE_TYPE_CODE,
3064 REQUISITION_TYPE,
3065 DESTINATION_TYPE_CODE,
3066 QUANTITY,
3067 UOM_CODE,
3068 UNIT_OF_MEASURE,
3069 --UNIT_PRICE,
3070 AUTHORIZATION_STATUS,
3071 PREPARER_ID,
3072 ITEM_ID,
3073 CHARGE_ACCOUNT_ID,
3074 DESTINATION_ORGANIZATION_ID,
3075 DESTINATION_SUBINVENTORY,
3076 DELIVER_TO_LOCATION_ID,
3077 DELIVER_TO_REQUESTOR_ID,
3078 NEED_BY_DATE,
3079 ORG_ID,
3080 LINE_TYPE_ID,
3081 REQ_NUMBER_SEGMENT1,
3082 REQUISITION_HEADER_ID,
3083 REQUISITION_LINE_ID,
3084 REFERENCE_NUM,
3085 JUSTIFICATION,
3086 NOTE_TO_BUYER,
3087 --TRANSACTION_REASON_CODE
3088 NOTE1_ID,
3089 NOTE1_TITLE,
3090 SUGGESTED_VENDOR_ID,
3091 SUGGESTED_VENDOR_NAME,
3092 source_organization_id,
3093 AUTOSOURCE_FLAG
3094 )
3095 VALUES
3096 (l_today, --creation_date
3097 l_user_id, --created_by
3098 l_today, -- last_update_date
3099 l_user_id, -- last_update_login
3100 l_login_id, --last_update_login
3101 'Spares', -- interface_source_code
3102 'VENDOR',
3103 'PURCHASE',
3104 'INVENTORY',
3105 l_line_tbl(i).ordered_quantity,
3106 l_line_tbl(i).unit_of_measure,
3107 l_unit_of_measure,
3108 'INCOMPLETE',
3109 l_employee_id,
3110 l_line_tbl(i).inventory_item_id,
3111 l_charge_Account_id,
3112 l_header_Rec.dest_organization_id,
3113 l_line_tbl(i).dest_subinventory,
3114 l_header_rec.ship_to_location_id,
3115 l_employee_id,
3116 nvl(l_line_tbl(i).need_by_date, l_header_rec.need_by_date),
3117 l_org_id,
3118 l_line_Type_id,
3119 l_header_rec.requisition_number,
3120 l_header_rec.requisition_header_id,
3121 l_line_tbl(i).requisition_line_id,
3122 l_header_rec.requirement_header_id,
3123 l_justification,
3124 l_note_to_buyer,
3125 --'Spares Parts Order'
3126 l_note1_id,
3127 l_note1_title,
3128 l_SUGGESTED_VENDOR_ID,
3129 l_SUGGESTED_VENDOR_NAME,
3130 l_source_organization_id,
3131 l_autosource_flag
3132 );
3133 END LOOP;
3134
3135 px_header_rec := l_header_rec;
3136 px_line_Table := l_line_Tbl;
3137
3138 IF (p_commit = FND_API.G_TRUE) THEN
3139 commit;
3140 END IF;
3141
3142 fnd_msg_pub.count_and_get( p_count => x_msg_count
3143 , p_data => x_msg_data);
3144
3145 EXCEPTION
3146 WHEN FND_API.G_EXC_ERROR THEN
3147 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3148 P_API_NAME => L_API_NAME
3149 ,P_PKG_NAME => G_PKG_NAME
3150 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3151 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
3152 ,X_MSG_COUNT => X_MSG_COUNT
3153 ,X_MSG_DATA => X_MSG_DATA
3154 ,X_RETURN_STATUS => X_RETURN_STATUS);
3155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3156 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3157 P_API_NAME => L_API_NAME
3158 ,P_PKG_NAME => G_PKG_NAME
3159 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3160 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
3161 ,X_MSG_COUNT => X_MSG_COUNT
3162 ,X_MSG_DATA => X_MSG_DATA
3163 ,X_RETURN_STATUS => X_RETURN_STATUS);
3164
3165 WHEN INVALID_CHARGE_ACCOUNT THEN
3166 po_message_s.app_error('PO_RI_INVALID_CHARGE_ACC_ID');
3167 raise;
3168
3169 WHEN OTHERS THEN
3170 Rollback to process_order_pub;
3171 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
3172 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, FALSE);
3173 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, FALSE);
3174 FND_MSG_PUB.ADD;
3175 fnd_msg_pub.count_and_get
3176 ( p_count => x_msg_count
3177 , p_data => x_msg_data);
3178 x_return_status := FND_API.G_RET_STS_ERROR;
3179 END;
3180
3181 END;