1 Package Body PO_CREATE_REQUISITION_SV AS
2 /* $Header: POXCARQB.pls 120.12.12020000.2 2013/02/10 11:33:42 vegajula ship $ */
3 --
4 -- Purpose: Create Internal Requisitions
5 --
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- kperiasa 08/01/01 Created new package body
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PO_CREATE_REQUISITION_SV';
12 G_FILE_NAME CONSTANT VARCHAR2(30) := 'POXCARQB.pls';
13
14 -- Logging global constants
15 D_PACKAGE_BASE CONSTANT VARCHAR2(100) := PO_LOG.get_package_base(G_PKG_NAME);
16
17 -- This procedure is to get the Unit Price for Internal Requisition
18 -- If the Currency Code is same for both Source and Destination Organization
19 -- get the Unit Cost for the Source Organization, otherwise
20 -- derive Unit Price based on the List Price and Conversion Rate
21 -- This procedure will have 2 OUT parameters viz. Currency Code and Unit Price
22
23 PROCEDURE get_unit_price_prc (p_item_id IN NUMBER,
24 p_source_organization_id IN NUMBER,
25 p_destination_organization_id IN NUMBER,
26 p_set_of_books_id IN NUMBER,
27 x_chart_of_account_id IN OUT NOCOPY NUMBER,
28 x_currency_code IN OUT NOCOPY VARCHAR2,
29 x_unit_price IN OUT NOCOPY NUMBER) IS
30
31 -- Get Functional Currency and Chart of Accounts ID of the SOB for Internal Requsitions
32 CURSOR currency_code_cur (p_organization_id NUMBER) IS
33 SELECT glsob.currency_code
34 ,glsob.chart_of_accounts_id
35 FROM gl_sets_of_books glsob,
36 org_organization_definitions ood
37 WHERE glsob.set_of_books_id = ood.set_of_books_id
38 AND ood.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 SELECT cic.item_cost
43 FROM cst_item_costs_for_gl_view cic,
44 mtl_parameters mp
45 WHERE cic.inventory_item_id = p_item_id
46 AND cic.organization_id = mp.cost_organization_id
47 AND cic.inventory_asset_flag = 1
48 AND mp.organization_id= p_source_organization_id;
49
50 -- Get Converted Unit Price for Purchase Requsitions
51 CURSOR converted_unit_price_int_csr (p_item_id NUMBER, p_source_organization_id NUMBER,
52 s_currency_code VARCHAR2, d_currency_code varchar2 ) IS
53 SELECT cic.item_cost *
54 round(gl_currency_api.get_closest_rate_sql
55 (s_currency_code,
56 d_currency_code,
57 trunc(sysdate),
58 psp.DEFAULT_RATE_TYPE,
59 30),10)
60 FROM cst_item_costs_for_gl_view cic,
61 mtl_parameters mp,
62 po_system_parameters psp
63 WHERE cic.inventory_item_id = p_item_id
64 AND cic.organization_id = mp.cost_organization_id
65 AND cic.inventory_asset_flag = 1
66 AND mp.organization_id= p_source_organization_id;
67
68 -- Get Converted Unit Price for Purchase Requsitions
69 CURSOR converted_unit_price_pur_cur (p_item_id NUMBER, p_source_organization_id NUMBER,
70 p_set_of_books_id NUMBER) IS
71 SELECT msi.list_price_per_unit *
72 round(gl_currency_api.get_closest_rate_sql
73 (p_set_of_books_id,
74 glsob.currency_code,
75 trunc(sysdate),
76 psp.DEFAULT_RATE_TYPE,
77 30),10)
78 FROM mtl_system_items msi,
79 gl_sets_of_books glsob,
80 org_organization_definitions ood,
81 po_system_parameters psp
82 WHERE msi.inventory_item_id = p_item_id
83 AND ood.organization_id = p_source_organization_id
84 AND msi.organization_id = ood.organization_id
85 AND glsob.set_of_books_id = ood.set_of_books_id;
86
87 s_currency_code VARCHAR2(15);
88 d_currency_code VARCHAR2(15);
89 d_chart_of_accounts_id NUMBER;
90 s_chart_of_accounts_id NUMBER;
91 l_unit_price NUMBER;
92 UNIT_PRICE_LT_0 EXCEPTION;
93 INVALID_UNIT_PRICE EXCEPTION;
94
95 l_module_name CONSTANT VARCHAR2(100) := 'get_unit_price_prc';
96 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE, l_module_name);
97 d_progress NUMBER;
98
99 BEGIN
100
101 IF (PO_LOG.d_proc) THEN
102 PO_LOG.proc_begin(d_module_base);
103 PO_LOG.proc_begin(d_module_base, 'p_item_id', p_item_id);
104 PO_LOG.proc_begin(d_module_base, 'p_source_organization_id', p_source_organization_id);
105 PO_LOG.proc_begin(d_module_base, 'p_destination_organization_id', p_destination_organization_id);
106 PO_LOG.proc_begin(d_module_base, 'p_set_of_books_id', p_set_of_books_id);
107 PO_LOG.proc_begin(d_module_base, 'x_chart_of_account_id', x_chart_of_account_id);
108 PO_LOG.proc_begin(d_module_base, 'x_currency_code', x_currency_code);
109 PO_LOG.proc_begin(d_module_base, 'x_unit_price', x_unit_price);
110 END IF;
111
112 -- Get the SOB Currency Code of the Source Organization ID
113 OPEN currency_code_cur(p_source_organization_id);
114 FETCH currency_code_cur INTO s_currency_code, s_chart_of_accounts_id;
115 CLOSE currency_code_cur;
116
117
118
119 -- Get SOB Currency Code of the Destination (Inventory) Organization
120 OPEN currency_code_cur(p_destination_organization_id);
121 FETCH currency_code_cur INTO d_currency_code, d_chart_of_accounts_id;
122 CLOSE currency_code_cur;
123
124 x_chart_of_account_id := d_chart_of_accounts_id; -- Bug 5637277
125
126 -- If Currency Code is same for both Destination and Source Organization
127 -- Get Item Cost of the Source Organization ID from cst_item_costs__for_gl_view
128 IF NVL(s_currency_code,'X') = NVL(d_currency_code,'X') THEN
129 -- Get Unit Cost
130 OPEN unit_price_cur (p_item_id, p_source_organization_id);
131 FETCH unit_price_cur INTO l_unit_price;
132 IF unit_price_cur%NOTFOUND THEN
133 CLOSE unit_price_cur;
134 Raise INVALID_UNIT_PRICE;
135 END IF;
136 CLOSE unit_price_cur;
137 IF l_unit_price < 0 THEN
138 Raise UNIT_PRICE_LT_0;
139 END IF;
140 ELSE /* Currency Code is different for Source and Destination Organization */
141 -- Get converted Unit price for internal requisition
142 -- Bug 7313047 - When creating internal requisition from Advance Planning workbench if the
143 -- source and destination org, have different functional currency,
144 -- then, we need to get the item cost from source org and convert it
145 -- to destination org's currency type.
146 IF p_source_organization_id IS NOT NULL THEN -- Bug 7313047
147 OPEN converted_unit_price_int_csr (p_item_id, p_source_organization_id, s_currency_code, d_currency_code);
148 FETCH converted_unit_price_int_csr INTO l_unit_price;
149 IF converted_unit_price_int_csr%NOTFOUND THEN
150 CLOSE converted_unit_price_int_csr; -- Bug 3468739
151 Raise INVALID_UNIT_PRICE;
152 END IF;
153 CLOSE converted_unit_price_int_csr; -- Bug 3468739
154 IF l_unit_price < 0 THEN
155 Raise UNIT_PRICE_LT_0;
156 END IF;
157 ELSE
158 -- Get converted Unit price for purchase requisition
159 OPEN converted_unit_price_pur_cur (p_item_id, p_source_organization_id, p_set_of_books_id);
160 FETCH converted_unit_price_pur_cur INTO l_unit_price;
161 IF converted_unit_price_pur_cur%NOTFOUND THEN
162 CLOSE converted_unit_price_pur_cur; -- Bug 3468739
163 Raise INVALID_UNIT_PRICE;
164 END IF;
165 CLOSE converted_unit_price_pur_cur; -- Bug 3468739
166 IF l_unit_price < 0 THEN
167 Raise UNIT_PRICE_LT_0;
168 END IF;
169 END IF; /* p_source_organization_id check */
170 END IF; /* Currency Check */
171
172 x_currency_code := d_currency_code;
173 x_unit_price := l_unit_price;
174 x_chart_of_account_id := d_chart_of_accounts_id; /* bug 5637277 replaced s_chart_of_accounts_id with d_chart_of_accounts_id */
175
176 IF (PO_LOG.d_proc) THEN
177 PO_LOG.proc_end(d_module_base);
178 PO_LOG.proc_end(d_module_base, 'x_currency_code',x_currency_code);
179 PO_LOG.proc_end(d_module_base, 'x_unit_price',x_unit_price);
180 PO_LOG.proc_end(d_module_base, 'x_chart_of_account_id',x_chart_of_account_id);
181 END IF;
182
183 EXCEPTION
184
185 WHEN UNIT_PRICE_LT_0 THEN
186 po_message_s.app_error('PO_RI_UNIT_PRICE_LT_0');
187 raise;
188
189 WHEN INVALID_UNIT_PRICE THEN
190 x_unit_price := 0;
191
192 END get_unit_price_prc;
193
194 -- This function is to check the subinventory type to derive
195 -- Code Combinatin ID. Function Returns Sub Inventory Type
196 -- 'ASSET' or 'EXPENSE'. If EXCEPTION, Returns 'X'
197
198 FUNCTION check_sub_inv_type_fun (p_destination_subinventory IN VARCHAR2,
199 p_destination_organization_id IN NUMBER )
200 RETURN VARCHAR2 IS
201
202 CURSOR asset_inventory_cur IS
203 SELECT asset_inventory
204 FROM mtl_secondary_inventories
205 WHERE secondary_inventory_name = NVL(p_destination_subinventory,'X')
206 AND organization_id = p_destination_organization_id;
207
208 l_asset_inventory NUMBER;
209 l_subinventory_type VARCHAR2(10) := 'X';
210
211 BEGIN
212 OPEN asset_inventory_cur;
213 FETCH asset_inventory_cur INTO l_asset_inventory;
214 CLOSE asset_inventory_cur;
215
216 IF (l_asset_inventory = 1) THEN
217 l_subinventory_type := 'ASSET';
218 ELSIF (l_asset_inventory = 2) then
219 l_subinventory_type := 'EXPENSE';
220 END IF;
221
222 RETURN l_subinventory_type;
223
224 EXCEPTION
225
226 WHEN OTHERS THEN
227 RETURN 'X';
228
229 END check_sub_inv_type_fun ;
230
231 -- This function is to check the item type to derive
232 -- Code Combinatin ID. Function Returns Item Type
233 -- 'ASSET' or 'EXPENSE'. If EXCEPTION, Returns 'X'
234
235 FUNCTION check_inv_item_type_fun ( p_destination_organization_id IN NUMBER,
236 p_item_id IN NUMBER)
237 RETURN VARCHAR2 IS
238
239 CURSOR item_type_cur IS
240 SELECT inventory_asset_flag
241 FROM mtl_system_items
242 WHERE organization_id = p_destination_organization_id
243 AND inventory_item_id = p_item_id;
244
245 l_item_type VARCHAR2(10) := 'X';
246 l_asset_flag VARCHAR2(1);
247
248 BEGIN
249
250 OPEN item_type_cur;
251 FETCH item_type_cur INTO l_asset_flag;
252 CLOSE item_type_cur;
253
254 IF l_asset_flag = 'Y' then
255 l_item_type := 'ASSET';
256 ELSE
257 l_item_type := 'EXPENSE';
258 END IF;
259
260 RETURN l_item_type;
261
262 EXCEPTION
263
264 WHEN OTHERS THEN
265 RETURN 'X';
266
267 END check_inv_item_type_fun;
268
269 -- This function is to default Code Combination ID for
270 -- Destination Type Code INVENTORY
271 -- Called in Process_Requisition
272
273 FUNCTION get_charge_account_fun (p_destination_organization_id IN NUMBER,
274 p_item_id IN NUMBER,
275 p_destination_subinventory IN VARCHAR DEFAULT NULL)
276 RETURN NUMBER IS
277
278 l_charge_account NUMBER := NULL;
279 l_item_type VARCHAR2(10);
280 l_subinventory_type VARCHAR2(10) := 'X';
281
282 l_module_name CONSTANT VARCHAR2(100) := 'get_charge_account_fun';
283 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE, l_module_name);
284 d_progress NUMBER;
285
286 BEGIN
287
288 d_progress := 10;
289
290 IF (PO_LOG.d_proc) THEN
291 PO_LOG.proc_begin(d_module_base);
292 PO_LOG.proc_begin(d_module_base, 'p_destination_organization_id', p_destination_organization_id);
293 PO_LOG.proc_begin(d_module_base, 'p_item_id', p_item_id);
294 PO_LOG.proc_begin(d_module_base, 'p_destination_subinventory', p_destination_subinventory);
295 END IF;
296
297 d_progress := 20;
298
299 l_item_type := check_inv_item_type_fun (p_destination_organization_id, p_item_id);
300
301 IF PO_LOG.d_stmt THEN
302 PO_LOG.stmt(d_module_base,d_progress,'l_item_type='||l_item_type);
303 END IF;
304
305 d_progress := 30;
306 IF l_item_type = 'EXPENSE' then
307
308 d_progress := 40;
309 -- Subinventory is provided
310 IF (p_destination_subinventory IS NOT NULL) THEN
311 BEGIN
312 d_progress := 50;
313 SELECT expense_account
314 INTO l_charge_account
315 FROM mtl_secondary_inventories
316 WHERE secondary_inventory_name = p_destination_subinventory
317 AND organization_id = p_destination_organization_id;
318 EXCEPTION
319 WHEN OTHERS THEN
320 NULL;
321 d_progress := 60;
322 END;
323 END IF;
324
325 IF PO_LOG.d_stmt THEN
326 PO_LOG.stmt(d_module_base,d_progress,'l_charge_account',l_charge_account);
327 END IF;
328
329 -- If Expense Account not available for the Subinventory and Org,
330 -- get expense account from Item Master for the Item and the Org
331 IF (l_charge_account IS NULL) THEN
332 BEGIN
333 d_progress := 70;
334 SELECT expense_account
335 INTO l_charge_account
336 FROM mtl_system_items
337 WHERE organization_id = p_destination_organization_id
338 AND inventory_item_id = p_item_id;
339 EXCEPTION
340 WHEN OTHERS THEN
341 NULL;
342 d_progress := 80;
343 END;
344 END IF;
345
346 IF PO_LOG.d_stmt THEN
347 PO_LOG.stmt(d_module_base,d_progress,'l_charge_account',l_charge_account);
348 END IF;
349
350 -- If Expense Account not available in Item Master, get account
351 -- from MTL_PARAMETERS for the Destination Organization
352 IF (l_charge_account IS NULL) THEN
353 BEGIN
354 d_progress := 90;
355 SELECT expense_account
356 INTO l_charge_account
357 FROM mtl_parameters
358 WHERE organization_id = p_destination_organization_id;
359 EXCEPTION
360 WHEN OTHERS THEN
361 NULL;
362 d_progress := 100;
363 END;
364 END IF;
365
366 IF PO_LOG.d_stmt THEN
367 PO_LOG.stmt(d_module_base,d_progress,'l_charge_account',l_charge_account);
368 END IF;
369
370 ELSE -- item type is ASSET
371
372 d_progress := 110;
373 --Check subinventory for Asset or Expense tracking.
374 IF (p_destination_subinventory IS NOT NULL) THEN
375 d_progress := 120;
376 l_subinventory_type := check_sub_inv_type_fun(p_destination_subinventory,
377 p_destination_organization_id);
378 END IF;
379
380 IF PO_LOG.d_stmt THEN
381 PO_LOG.stmt(d_module_base,d_progress,'l_subinventory_type' , l_subinventory_type);
382 END IF;
383
384 d_progress := 130;
385 -- Get the default account from the Organization if Subinventory Type is NOT
386 -- EXPENSE or ASSET
387 IF l_subinventory_type = 'X' then
388 BEGIN
389 d_progress := 140;
390 SELECT material_account
391 INTO l_charge_account
392 FROM mtl_parameters
393 WHERE organization_id = p_destination_organization_id;
394 EXCEPTION
395 WHEN OTHERS THEN
396 NULL;
397 d_progress := 150;
398 END;
399
400 IF PO_LOG.d_stmt THEN
401 PO_LOG.stmt(d_module_base,d_progress,'l_charge_account',l_charge_account);
402 END IF;
403
404 ELSIF l_subinventory_type = 'EXPENSE' THEN
405 -- Get Expense Account for the Subinventory
406 BEGIN
407 d_progress := 160;
408 SELECT expense_account
409 INTO l_charge_account
410 FROM mtl_secondary_inventories
411 WHERE secondary_inventory_name = p_destination_subinventory
412 AND organization_id = p_destination_organization_id;
413 EXCEPTION
414 WHEN OTHERS THEN
415 NULL;
416 d_progress := 170;
417 END;
418
419 IF PO_LOG.d_stmt THEN
420 PO_LOG.stmt(d_module_base,d_progress,'l_charge_account',l_charge_account);
421 END IF;
422
423 -- If charge account is NULL for the Subinventory, get the default account
424 -- for the Organization from MTL_PARAMETERS
425 IF (l_charge_account is NULL) THEN
426 BEGIN
427 d_progress := 180;
428 SELECT expense_account
429 INTO l_charge_account
430 FROM mtl_parameters
431 WHERE organization_id = p_destination_organization_id;
432 EXCEPTION
433 WHEN OTHERS THEN
434 NULL;
435 d_progress := 190;
436 END;
437 END IF;
438
439 IF PO_LOG.d_stmt THEN
440 PO_LOG.stmt(d_module_base,d_progress,'l_charge_account',l_charge_account);
441 END IF;
442
443 ELSE -- destination sub inventory type is ASSET
444 -- Get the Charge_Account for the Subinventory
445 BEGIN
446 d_progress := 200;
447 SELECT material_account
448 INTO l_charge_account
449 FROM mtl_secondary_inventories
450 WHERE secondary_inventory_name = p_destination_subinventory
451 AND organization_id = p_destination_organization_id;
452 EXCEPTION
453 WHEN OTHERS THEN
454 NULL;
455 d_progress := 210;
456 END;
457
458 IF PO_LOG.d_stmt THEN
459 PO_LOG.stmt(d_module_base,d_progress,'l_charge_account',l_charge_account);
460 END IF;
461
462 -- If Charge_account is not availabe for the Subinventory,
463 -- get it for the Destination Organization from MTL_PARAMETERS
464 IF (l_charge_account IS NULL) THEN
465 BEGIN
466 d_progress := 220;
467 SELECT material_account
468 INTO l_charge_account
469 FROM mtl_parameters
470 WHERE organization_id = p_destination_organization_id;
471 EXCEPTION
472 WHEN OTHERS THEN
473 NULL;
474 d_progress := 230;
475 END;
476 END IF;
477
478 IF PO_LOG.d_stmt THEN
479 PO_LOG.stmt(d_module_base,d_progress,'l_charge_account',l_charge_account);
480 END IF;
481 END IF; /* Sub Inventory Type */
482 END IF; /* Item Type Check */
483
484 IF (PO_LOG.d_proc) THEN
485 PO_LOG.proc_end(d_module_base);
486 PO_LOG.proc_end(d_module_base, 'l_charge_account',l_charge_account);
487 END IF;
488
489 RETURN (l_charge_account);
490 EXCEPTION
491 WHEN OTHERS THEN
492 IF (PO_LOG.d_exc) THEN
493 PO_LOG.exc(d_module_base, d_progress, 'Unhandled exception in '||l_module_name||': '||SQLERRM);
494 END IF;
495 NULL;
496 END get_charge_account_fun;
497
498
499 /*
500 Function to validate Code Combination IDs.
501 If INVALID function will return FALSE
502 */
503
504 FUNCTION valid_account_id_fun (p_ccid IN NUMBER,
505 p_gl_date IN DATE,
506 p_chart_of_accounts_id IN NUMBER)
507 RETURN BOOLEAN IS
508
509
510 l_module_name CONSTANT VARCHAR2(100) := 'valid_account_id_fun';
511 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE, l_module_name);
512 d_progress NUMBER;
513
514 CURSOR validate_ccid_cur IS
515 SELECT 'X'
516 FROM gl_code_combinations gcc
517 WHERE gcc.code_combination_id = p_ccid
518 AND gcc.enabled_flag = 'Y'
519 AND trunc(nvl(p_gl_date,SYSDATE))
520 BETWEEN trunc(nvl(start_date_active, nvl(p_gl_date,SYSDATE) ))
521 AND trunc(nvl (end_date_active, SYSDATE+1))
522 AND gcc.detail_posting_allowed_flag = 'Y'
523 AND gcc.chart_of_accounts_id= p_chart_of_accounts_id
524 AND gcc.summary_flag = 'N';
525
526 l_dummy VARCHAR2(1);
527
528 BEGIN
529
530 d_progress := 10;
531
532 IF (PO_LOG.d_proc) THEN
533 PO_LOG.proc_begin(d_module_base);
534 PO_LOG.proc_begin(d_module_base, 'p_ccid', p_ccid);
535 PO_LOG.proc_begin(d_module_base, 'p_gl_date', p_gl_date);
536 PO_LOG.proc_begin(d_module_base, 'p_chart_of_accounts_id', p_chart_of_accounts_id);
537 END IF;
538
539 d_progress := 20;
540 OPEN validate_ccid_cur;
541
542 d_progress := 30;
543 FETCH validate_ccid_cur INTO l_dummy;
544 d_progress := 40;
545 IF validate_ccid_cur%FOUND THEN
546 CLOSE validate_ccid_cur;
547
548 d_progress := 50;
549 IF (PO_LOG.d_proc) THEN
550 PO_LOG.proc_end(d_module_base);
551 PO_LOG.proc_end(d_module_base, 'return TRUE',0);
552 END IF;
553
554
555 RETURN TRUE;
556 ELSE
557 CLOSE validate_ccid_cur;
558
559 d_progress := 60;
560 IF (PO_LOG.d_proc) THEN
561 PO_LOG.proc_end(d_module_base);
562 PO_LOG.proc_end(d_module_base, 'return FALSE',1);
563 END IF;
564
565 RETURN FALSE;
566 END IF;
567
568 EXCEPTION
569
570 WHEN OTHERS THEN
571 IF (PO_LOG.d_exc) THEN
572 PO_LOG.exc(d_module_base, d_progress, 'Unhandled exception in '||l_module_name||': '||SQLERRM);
573 END IF;
574
575 return (FALSE);
576
577 END valid_account_id_fun;
578
579
580 /* Procedure to Process Internal Requisition. Most of the values will come from
581 the colling routine, however, PO_REQ_DISTRIBUTIONS table will be populated
582 within this Package.
583
584 This package assumes that there is only one distribution per order line, since
585 Order Line cannot be shipped to multiple locations.
586
587 Minimum validations are considered when populating PO_REQUISITION_HEADERS and
588 PO_REQUISITION_LINES. However, when populating PO_REQ_DISTRIBUTIONS,validations
589 related to ACCOUNTS are done.
590
591 This package assumes that Inventory Item ID will always be passed from the
592 calling routine, since ITEM_DESCRIPTION is a mandatory column in PO_REQUISITION_LINES.
593 */
594
595 PROCEDURE process_requisition(
596 p_api_version IN NUMBER := 1.0
597 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_TRUE
598 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
599 ,px_header_rec IN OUT NOCOPY po_create_requisition_sv.header_rec_type
600 ,px_line_table IN OUT NOCOPY po_create_requisition_sv.Line_Tbl_type
601 ,x_return_status OUT NOCOPY VARCHAR2
602 ,x_msg_count OUT NOCOPY NUMBER
603 ,x_msg_data OUT NOCOPY VARCHAR2
604 )
605 IS
606
607 l_api_version_number CONSTANT NUMBER := 1.0;
608 l_api_name CONSTANT VARCHAR2(30) := 'process_requisition';
609 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
610 l_msg_count NUMBER;
611 l_msg_data VARCHAR2(2000);
612 l_commit VARCHAR2(1) := FND_API.G_FALSE;
613 l_user_id NUMBER;
614 l_login_id NUMBER;
615 l_today DATE;
616
617 EXCP_USER_DEFINED EXCEPTION;
618 INVALID_ITEM EXCEPTION;
619 INVALID_ITEM_CATEGORY EXCEPTION;
620 INVALID_QUANTITY EXCEPTION;
621 INVALID_SOURCE_TYPE EXCEPTION;
622 INVALID_DESTINATION_TYPE EXCEPTION;
623 UNIT_PRICE_LT_0 EXCEPTION;
624 INVALID_UNIT_PRICE EXCEPTION;
625 INVALID_CHARGE_ACCOUNT EXCEPTION;
626 INVALID_ACCRUAL_ACCOUNT EXCEPTION;
627 INVALID_BUDGET_ACCOUNT EXCEPTION;
628 INVALID_VARIANCE_ACCOUNT EXCEPTION;
629 INVALID_AUTH_STATUS EXCEPTION;
630 INVALID_PREPARER_ID EXCEPTION;
631 INVALID_LOCATION_ID EXCEPTION;
632 INVALID_DESTINATION_ORG EXCEPTION;
633 INVALID_UNIT_OF_MEASURE EXCEPTION;
634
635 s_chart_of_accounts_id NUMBER;
636 s_currency_code VARCHAR2(15);
637 l_dummy VARCHAR2(1);
638 l_line_type_id po_requisition_lines.line_type_id%TYPE;
639 l_create_req_supply BOOLEAN;
640 l_set_of_books_id NUMBER;
641
642 l_header_rec po_create_requisition_sv.Header_Rec_Type;
643 l_line_rec po_create_requisition_sv.Line_Rec_Type;
644 l_line_tbl po_create_requisition_sv.Line_tbl_Type;
645 l_dist_rec po_create_requisition_sv.Dist_rec_Type;
646
647 l_msg varchar2(2000);
648
649 d_chart_of_accounts_id NUMBER; /* bug 5637277 - Please refer the bug for the details */
650
651
652 l_manufacturer_id po_requisition_lines_All.MANUFACTURER_ID%TYPE;
653 l_manufacturer_name PO_ATTRIBUTE_VALUES_TLP.manufacturer%TYPE;
654 l_manufacturer_pn PO_ATTRIBUTE_VALUES.manufacturer_part_num%TYPE;
655 l_lead_time PO_ATTRIBUTE_VALUES.lead_time%TYPE;
656
657 --Added for bug 13254403
658 l_return_value BOOLEAN;
659
660
661 -- bug5176308
662 -- removed the cursor to get req number as the logic is moved to
663 -- an API
664
665 -- Cursor to get unique Requisition_Header_ID
666 CURSOR req_header_id_cur IS
667 SELECT po_requisition_headers_s.nextval
668 FROM sys.dual;
669
670 -- Cursor to get unique Requisition_Line_ID
671 CURSOR req_line_id_cur IS
672 SELECT po_requisition_lines_s.nextval
673 FROM sys.dual;
674
675 -- Cursor to get unique Distribution_id
676 CURSOR dist_line_id_cur IS
677 SELECT po_req_distributions_s.nextval
678 FROM sys.dual;
679
680 -- Cursor to get Accrual Account ID and Variance Account ID
681 -- For Destination Type Code INVENTORY get accrual account id
682 -- from MTL_PARAMETERS
683 -- Per Requisition Import program (pocis.opc).
684 CURSOR accrual_account_id_cur (p_destination_organization_id NUMBER) IS
685 SELECT mp.ap_accrual_account,
686 mp.invoice_price_var_account
687 FROM mtl_parameters mp
688 WHERE mp.organization_id = p_destination_organization_id;
689
690 -- Get Default Line Type
691 CURSOR line_type_cur (p_org_id NUMBER) IS
692 SELECT line_type_id
693 FROM PO_SYSTEM_PARAMETERS
694 WHERE org_id = p_org_id;
695
696 -- Get Item Description for a given Item ID
697 -- For the purpose of creating Approve Internal Requisition
698 -- it is assumed that the calling procedure will always pass the Item ID
699 -- so that Item Description can be derived.
700 CURSOR item_desc_cur(p_item_id NUMBER, p_orgn_id NUMBER) IS
701 SELECT description
702 FROM mtl_system_items_b
703 WHERE inventory_item_id = p_item_id
704 AND organization_id = p_orgn_id;
705
706 -- Get Item Category ID
707 -- As in Requisition Import
708 CURSOR item_category_cur(p_item_id NUMBER, p_destination_org_id NUMBER) IS
709 SELECT mic.category_id
710 FROM mtl_item_categories mic,
711 mtl_default_sets_view mdsv
712 WHERE mic.inventory_item_id = p_item_id
713 AND mic.organization_id = p_destination_org_id
714 AND mic.category_set_id = mdsv.category_set_id
715 AND mdsv.functional_area_id = 2;
716
717 -- For Source Type Code validation, if passed to the procedure
718 CURSOR source_type_cur (p_source_type_code VARCHAR2) IS
719 SELECT 'X'
720 FROM po_lookup_codes plc
721 WHERE plc.lookup_type = 'REQUISITION SOURCE TYPE'
722 AND plc.lookup_code = p_source_type_code;
723
724 -- For Destination Type Code validation, if passed to the procedure
725 CURSOR destination_type_cur (p_destination_type_code VARCHAR2) IS
726 SELECT 'X'
727 FROM po_lookup_codes plc
728 WHERE plc.lookup_type = 'DESTINATION TYPE'
729 AND plc.lookup_code = p_destination_type_code;
730
731 -- For Authorization Status validation, if passed to the procedure
732 CURSOR authorization_status_cur (p_authorization_status VARCHAR2) IS
733 SELECT 'X'
734 FROM po_lookup_codes plc
735 WHERE plc.lookup_type = 'AUTHORIZATION STATUS'
736 AND plc.lookup_code = p_authorization_status;
737
738 -- Get Set of Books ID for a given Org_ID - Mandatory in PO_REQ_DISTRIBUTIONS
739 CURSOR set_of_books_cur (p_organization_id NUMBER) IS
740 SELECT set_of_books_id
741 FROM hr_operating_units
742 WHERE organization_id = p_organization_id;
743
744 -- If encumbrance flag is 'Y' get the budget account
745 -- For Internal Req, Destination Type Code will be INVENTORY
746 -- Hence, it is assumed that the budget account will come
747 -- from MTL_PARAMETERS for the Item and the Destination Organization
748 CURSOR budget_account_cur (p_destination_organization_id NUMBER,
749 p_item_id NUMBER) IS
750 SELECT nvl (msi.encumbrance_account,mp.encumbrance_account)
751 FROM mtl_system_items msi,
752 mtl_parameters mp
753 WHERE msi.inventory_item_id = p_item_id
754 AND msi.organization_id = p_destination_organization_id
755 AND mp.organization_id = msi.organization_id;
756
757 -- Get Requisition Encumbrance Flag for the Set of Books
758 -- Based of this flag Budget Account will be populated
759 -- in PO_REQ_DISTRIBUTIONS
760 CURSOR req_encumbrance_cur (p_set_of_books_id NUMBER) IS
761 SELECT nvl (fsp.req_encumbrance_flag,'N')
762 FROM financials_system_parameters fsp
763 WHERE fsp.set_of_books_id = p_set_of_books_id;
764
765 -- Get Charge Account for the Item and Organization
766 CURSOR charge_account_cur (p_destination_organization_id NUMBER,
767 p_item_id NUMBER) IS
768 SELECT NVL(expense_account,-1)
769 FROM mtl_system_items
770 WHERE inventory_item_id = p_item_id
771 AND organization_id = p_destination_organization_id;
772
773 -- Get Unit_of_Measure from MTL_UNIT_OF_MEASURES, since OM passes
774 -- only UOM_CODE and PO requires UNIT_OF_MEASURE. This is being done
775 -- to fix the problem of line not showing up from POXRQVRQ form
776 CURSOR unit_of_measure_cur (p_uom_code VARCHAR2) IS
777 SELECT mum.unit_of_measure
778 FROM mtl_units_of_measure mum
779 WHERE mum.uom_code = p_uom_code;
780
781 BEGIN
782
783 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
784 -- initialize message list
785 FND_MSG_PUB.initialize;
786 END IF;
787
788 -- Standard call to check for call compatibility.
789
790 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
791 p_api_version,
792 l_api_name,
793 G_PKG_NAME)
794 THEN
795 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
796 END IF;
797
798 -- initialize return status
799 x_return_status := FND_API.G_RET_STS_SUCCESS;
800
801 l_header_rec := px_header_rec;
802 l_line_tbl := px_line_table;
803
804 -- get all the values required to insert into po_requisition_header table
805 SELECT Sysdate INTO l_today FROM dual;
806
807 l_user_id := fnd_global.user_id;
808 l_login_id := fnd_global.login_id;
809
810
811 -- Get Requisition_header_id
812 OPEN req_header_id_cur;
813 FETCH req_header_id_cur into l_header_rec.requisition_header_id;
814 CLOSE req_header_id_cur;
815
816 -- bug5176308
817 l_header_rec.segment1 :=
818 PO_CORE_SV1.default_po_unique_identifier
819 ( x_table_name => 'PO_REQUISITION_HEADERS'
820 );
821
822 -- check for uniqueness of requisition_number
823 BEGIN
824
825 SELECT 'X' INTO l_dummy
826 FROM DUAL
827 WHERE NOT EXISTS
828 ( SELECT 'X'
829 FROM po_requisition_headers
830 WHERE Segment1 = l_header_rec.segment1);
831
832 EXCEPTION
833 WHEN NO_DATA_FOUND THEN
834 po_message_s.app_error('PO_ALL_ENTER_UNIQUE_VAL');
835 raise;
836 WHEN OTHERS THEN
837 po_message_s.sql_error('check_unique','010',sqlcode);
838 raise;
839 END;
840
841 -- Raise Error, if preparer_id IS NULL
842 IF l_header_rec.preparer_id IS NULL THEN
843 Raise INVALID_PREPARER_ID;
844 END IF;
845
846 -- Default Summary Flag to 'N', if NULL
847 IF l_header_rec.summary_flag IS NULL THEN
848 l_header_rec.summary_flag := 'N';
849 END IF;
850
851 -- Default Enabled Flag to 'Y', if NULL
852 IF l_header_rec.enabled_flag IS NULL THEN
853 l_header_rec.enabled_flag := 'Y';
854 END IF;
855
856 -- Default Transferred to OE Flag to 'Y', if NULL
857 -- This is done to make sure that these requisitions don't get
858 -- picked up at the time of populating order interface
859 IF l_header_rec.transferred_to_oe_flag IS NULL THEN
860 l_header_rec.transferred_to_oe_flag := 'Y';
861 END IF;
862
863 -- Default Authorization to APPROVED, if NULL
864 IF l_header_rec.authorization_status IS NULL THEN
865 l_header_rec.authorization_status := 'APPROVED';
866 ELSE
867 OPEN authorization_status_cur (l_header_rec.authorization_status);
868 FETCH authorization_status_cur INTO l_dummy;
869 IF authorization_status_cur%NOTFOUND THEN
870 CLOSE authorization_status_cur;
871 Raise INVALID_AUTH_STATUS;
872 END IF;
873 CLOSE authorization_status_cur;
874 END IF;
875
876 -- create approved requisition headers
877 -- insert into PO_REQUISITION_HEADERS
878 INSERT INTO po_requisition_headers(
879 org_id,
880 requisition_header_id,
881 preparer_id,
882 last_update_date,
883 last_updated_by,
884 segment1,
885 summary_flag,
886 enabled_flag,
887 segment2,
888 segment3,
889 segment4,
890 segment5,
891 start_date_active,
892 end_date_active,
893 last_update_login,
894 creation_date,
895 created_by,
896 description,
897 authorization_status,
898 note_to_authorizer,
899 type_lookup_code,
900 transferred_to_oe_flag,
901 attribute_category,
902 attribute1,
903 attribute2,
904 attribute3,
905 attribute4,
906 attribute5,
907 attribute6,
908 attribute7,
909 attribute8,
910 attribute9,
911 attribute10,
912 attribute11,
913 attribute12,
914 attribute13,
915 attribute14,
916 attribute15,
917 government_context,
918 closed_code,
919 tax_attribute_update_code --<eTax Integration R12>
920 ) VALUES (
921 l_header_rec.org_id,
922 l_header_rec.requisition_header_id,
923 l_header_rec.preparer_id,
924 l_today,
925 l_user_id,
926 l_header_rec.segment1,
927 l_header_rec.summary_flag,
928 l_header_rec.enabled_flag,
929 l_header_rec.segment2,
930 l_header_rec.segment3,
931 l_header_rec.segment4,
932 l_header_rec.segment5,
933 l_header_rec.start_date_active,
934 l_header_rec.end_date_active,
935 l_header_rec.last_update_login,
936 l_today,
937 l_user_id,
938 l_header_rec.description,
939 l_header_rec.authorization_status,
940 l_header_rec.note_to_authorizer,
941 l_header_rec.type_lookup_code,
942 l_header_rec.transferred_to_oe_flag,
943 l_header_rec.attribute_category,
944 l_header_rec.attribute1,
945 l_header_rec.attribute2,
946 l_header_rec.attribute3,
947 l_header_rec.attribute4,
948 l_header_rec.attribute5,
949 l_header_rec.attribute6,
950 l_header_rec.attribute7,
951 l_header_rec.attribute8,
952 l_header_rec.attribute9,
953 l_header_rec.attribute10,
954 l_header_rec.attribute11,
955 l_header_rec.attribute12,
956 l_header_rec.attribute13,
957 l_header_rec.attribute14,
958 l_header_rec.attribute15,
959 l_header_rec.government_context,
960 l_header_rec.closed_code ,
961 'CREATE' --<eTax Integration R12>
962 );
963
964 -- get all the values required to insert into po_requisition_lines table
965
966 -- line_type_id for Requisition
967 OPEN line_type_cur (l_header_rec.org_id);
968 FETCH line_type_cur INTO l_line_type_id;
969 CLOSE line_type_cur;
970
971 FOR I IN 1..l_line_tbl.COUNT LOOP
972
973 -- Get Set of Books Id
974 OPEN set_of_books_cur (l_line_tbl(i).org_id);
975 FETCH set_of_books_cur INTO l_set_of_books_id;
976 CLOSE set_of_books_cur;
977
978 -- get requisition_line_id
979 IF (l_line_tbl(i).Requisition_Line_Id is NULL) THEN
980 OPEN req_line_id_cur;
981 FETCH req_line_id_cur INTO l_line_tbl(i).requisition_line_id;
982 CLOSE req_line_id_cur;
983 END IF;
984
985 -- Assign Requisition Header ID
986 IF (l_line_tbl(i).requisition_header_id IS NULL) THEN
987 l_line_tbl(i).requisition_header_id := l_header_rec.requisition_header_id;
988 END IF;
989
990 -- Assign the default line_type_id if there isn't one
991 IF (l_line_tbl(i).line_type_id IS NULL) THEN
992 l_line_tbl(i).line_type_id := l_line_type_id;
993 END IF;
994
995 -- <SERVICES FPJ START>
996 -- Populate the values of order_type_lookup_code, purchase_basis
997 -- and matching_basis based on the line_type_id
998 BEGIN
999 SELECT order_type_lookup_code,
1000 purchase_basis,
1001 matching_basis
1002 INTO l_line_tbl(i).order_type_lookup_code,
1003 l_line_tbl(i).purchase_basis,
1004 l_line_tbl(i).matching_basis
1005 FROM po_line_types
1006 WHERE line_type_id = l_line_tbl(i).line_type_id;
1007 EXCEPTION
1008 WHEN OTHERS THEN
1009 null;
1010 END;
1011 -- <SERVICES FPJ END>
1012
1013 -- INVALID_LOCATION_ID and INVALID_DESTINATION_ORG exceptions
1014 -- were added as part of this package to avoid seeding new messages
1015 -- in OM when validating deliver_to_location_id and destination_organization_id
1016
1017 IF l_line_tbl(i).deliver_to_location_id IS NULL THEN
1018 Raise INVALID_LOCATION_ID;
1019 END IF;
1020
1021 IF l_line_tbl(i).destination_organization_id IS NULL THEN
1022 Raise INVALID_DESTINATION_ORG;
1023 END IF;
1024
1025 -- Get Item Description, if NULL
1026 -- It is assumed that whenever this procedure is called, Item ID will be
1027 -- passed. If Item Description is NULL, Raise ERROR.
1028 IF (l_line_tbl(i).item_description IS NULL) THEN
1029 OPEN item_desc_cur(l_line_tbl(i).item_id, l_line_tbl(i).destination_organization_id);
1030 FETCH item_desc_cur INTO l_line_tbl(i).item_description;
1031 IF item_desc_cur%NOTFOUND THEN
1032 CLOSE item_desc_cur;
1033 Raise INVALID_ITEM;
1034 END IF;
1035 CLOSE item_desc_cur;
1036 END IF;
1037
1038 -- Get Category ID of the Item
1039 IF (l_line_tbl(i).category_id IS NULL) THEN
1040 OPEN item_category_cur (l_line_tbl(i).item_id, l_line_tbl(i).destination_organization_id);
1041 FETCH item_category_cur INTO l_line_tbl(i).category_id;
1042 IF item_category_cur%NOTFOUND THEN
1043 CLOSE item_category_cur;
1044 Raise INVALID_ITEM_CATEGORY;
1045 END IF;
1046 CLOSE item_category_cur;
1047 END IF;
1048
1049 -- Derive Unit_of_Measure from Uom_Code passed from OM
1050 OPEN unit_of_measure_cur(l_line_tbl(i).uom_code);
1051 FETCH unit_of_measure_cur INTO l_line_tbl(i).unit_meas_lookup_code;
1052 IF unit_of_measure_cur%NOTFOUND THEN
1053 CLOSE unit_of_measure_cur;
1054 Raise INVALID_UNIT_OF_MEASURE;
1055 ELSE
1056 CLOSE unit_of_measure_cur;
1057 END IF;
1058
1059 /* Get Unit Price and Currency Code*/
1060 get_unit_price_prc (l_line_tbl(i).item_id
1061 ,l_line_tbl(i).source_organization_id
1062 ,l_line_tbl(i).destination_organization_id
1063 ,l_set_of_books_id
1064 ,d_chart_of_accounts_id
1065 ,l_line_tbl(i).currency_code
1066 ,l_line_tbl(i).unit_price );
1067
1068 -- Quantity MUST be > 0 for Requisition
1069 IF (l_line_tbl(i).quantity <= 0) THEN
1070 Raise INVALID_QUANTITY;
1071 END IF;
1072
1073 -- Default INVENTORY as Source_Type_Code for Internal Requisitions if not passed
1074 -- If passed, validate Source Type Code from PO_LOOKUP_CODES
1075 -- If Invalid, Raise Error
1076 IF (l_line_tbl(i).source_type_code IS NULL ) THEN
1077 l_line_tbl(i).source_type_code := 'INVENTORY';
1078 ELSE
1079 OPEN source_type_cur(l_line_tbl(i).source_type_code);
1080 FETCH source_type_cur INTO l_dummy;
1081 IF source_type_cur%NOTFOUND THEN
1082 CLOSE source_type_cur;
1083 Raise INVALID_SOURCE_TYPE;
1084 END IF;
1085 CLOSE source_type_cur;
1086 END IF;
1087
1088 -- Default Encumbered_Flag to 'N'
1089 -- Since the funds reservation API is not called from here, this flag is
1090 -- set to 'N'
1091 IF (l_line_tbl(i).encumbered_flag IS NULL) THEN
1092 l_line_tbl(i).encumbered_flag := 'N';
1093 END IF;
1094
1095 -- Default Cancel_flag to 'N'
1096 IF (l_line_tbl(i).cancel_flag IS NULL) THEN
1097 l_line_tbl(i).cancel_flag := 'N';
1098 END IF;
1099
1100 -- Default INVENTORY as Destination_Type_Code for Internal Requisitions if not passed
1101 -- If passed, validate Destination Type Code from PO_LOOKUP_CODES
1102 -- If Invalid, Raise Error
1103 IF (l_line_tbl(i).destination_type_code IS NULL ) THEN
1104 l_line_tbl(i).destination_type_code := 'INVENTORY';
1105 ELSE
1106 OPEN destination_type_cur(l_line_tbl(i).destination_type_code);
1107 FETCH destination_type_cur INTO l_dummy;
1108 IF destination_type_cur%NOTFOUND THEN
1109 CLOSE destination_type_cur;
1110 Raise INVALID_DESTINATION_TYPE;
1111 END IF;
1112 CLOSE destination_type_cur;
1113 END IF;
1114
1115 -- insert into po_requisition_lines table
1116 -- <SERVICES FPJ>
1117 -- Added order_type_lookup_code, purchase_basis and
1118 -- matching_basis
1119
1120
1121 IF (l_line_tbl(i).item_id IS NOT NULL) Then
1122
1123
1124 po_attribute_values_pvt.get_item_attributes_values(l_line_tbl(i).item_id, l_manufacturer_pn,l_manufacturer_name,
1125 l_lead_time,l_manufacturer_id) ;
1126
1127 END IF ;
1128
1129
1130 INSERT INTO po_requisition_lines(
1131 requisition_line_id,
1132 requisition_header_id,
1133 line_num,
1134 line_type_id,
1135 category_id,
1136 item_description,
1137 unit_meas_lookup_code,
1138 unit_price,
1139 quantity,
1140 deliver_to_location_id,
1141 to_person_id,
1142 last_update_date,
1143 last_updated_by,
1144 source_type_code,
1145 last_update_login,
1146 creation_date,
1147 created_by,
1148 item_id,
1149 item_revision,
1150 encumbered_flag,
1151 rfq_required_flag,
1152 need_by_date,
1153 source_organization_id,
1154 source_subinventory,
1155 destination_type_code,
1156 destination_organization_id,
1157 destination_subinventory,
1158 line_location_id,
1159 modified_by_agent_flag,
1160 parent_req_line_id,
1161 justification,
1162 note_to_agent,
1163 note_to_receiver,
1164 purchasing_agent_id,
1165 document_type_code,
1166 blanket_po_header_id,
1167 blanket_po_line_num,
1168 currency_code,
1169 rate_type,
1170 rate_date,
1171 rate,
1172 currency_unit_price,
1173 suggested_vendor_name,
1174 suggested_vendor_location,
1175 suggested_vendor_contact,
1176 suggested_vendor_phone,
1177 suggested_vendor_product_code,
1178 un_number_id,
1179 hazard_class_id,
1180 must_use_sugg_vendor_flag,
1181 reference_num,
1182 on_rfq_flag,
1183 urgent_flag,
1184 cancel_flag,
1185 quantity_cancelled,
1186 cancel_date,
1187 cancel_reason,
1188 closed_code,
1189 agent_return_note,
1190 changed_after_research_flag,
1191 vendor_id,
1192 vendor_site_id,
1193 vendor_contact_id,
1194 research_agent_id,
1195 wip_entity_id,
1196 wip_line_id,
1197 wip_repetitive_schedule_id,
1198 wip_operation_seq_num,
1199 wip_resource_seq_num,
1200 attribute_category,
1201 destination_context,
1202 inventory_source_context,
1203 vendor_source_context,
1204 attribute1,
1205 attribute2,
1206 attribute3,
1207 attribute4,
1208 attribute5,
1209 attribute6,
1210 attribute7,
1211 attribute8,
1212 attribute9,
1213 attribute10,
1214 attribute11,
1215 attribute12,
1216 attribute13,
1217 attribute14,
1218 attribute15,
1219 bom_resource_id,
1220 government_context,
1221 closed_reason,
1222 closed_date,
1223 transaction_reason_code,
1224 quantity_received,
1225 order_type_lookup_code,
1226 purchase_basis,
1227 matching_basis,
1228 org_id, -- <R12 MOAC>
1229 tax_attribute_update_code, --<eTax Integration R12>
1230 MANUFACTURER_ID, --bug 7387487
1231 MANUFACTURER_NAME,
1232 MANUFACTURER_PART_NUMBER
1233 ) VALUES (
1234 l_line_tbl(i).requisition_line_id,
1235 l_line_tbl(i).requisition_header_id,
1236 l_line_tbl(i).line_num,
1237 l_line_tbl(i).line_type_id,
1238 l_line_tbl(i).category_id,
1239 l_line_tbl(i).item_description,
1240 l_line_tbl(i).unit_meas_lookup_code,
1241 l_line_tbl(i).unit_price,
1242 l_line_tbl(i).quantity,
1243 l_line_tbl(i).deliver_to_location_id,
1244 l_line_tbl(i).to_person_id,
1245 l_today, -- last_update_date
1246 l_user_id, --last_updated_by
1247 l_line_tbl(i).source_type_code,
1248 l_login_id, --last_update_login
1249 l_today, --creation_date
1250 l_user_id, --created_by
1251 l_line_tbl(i).item_id,
1252 l_line_tbl(i).item_revision,
1253 l_line_tbl(i).encumbered_flag,
1254 l_line_tbl(i).rfq_required_flag,
1255 l_line_tbl(i).need_by_date,
1256 l_line_tbl(i).source_organization_id,
1257 l_line_tbl(i).source_subinventory,
1258 l_line_tbl(i).destination_type_code,
1259 l_line_tbl(i).destination_organization_id,
1260 l_line_tbl(i).destination_subinventory,
1261 l_line_tbl(i).line_location_id,
1262 l_line_tbl(i).modified_by_agent_flag,
1263 l_line_tbl(i).parent_req_line_id,
1264 l_line_tbl(i).justification,
1265 l_line_tbl(i).note_to_agent,
1266 l_line_tbl(i).note_to_receiver,
1267 l_line_tbl(i).purchasing_agent_id,
1268 l_line_tbl(i).document_type_code,
1269 l_line_tbl(i).blanket_po_header_id,
1270 l_line_tbl(i).blanket_po_line_num,
1271 l_line_tbl(i).currency_code,
1272 l_line_tbl(i).rate_type,
1273 l_line_tbl(i).rate_date,
1274 l_line_tbl(i).rate,
1275 l_line_tbl(i).currency_unit_price,
1276 l_line_tbl(i).suggested_vendor_name,
1277 l_line_tbl(i).suggested_vendor_location,
1278 l_line_tbl(i).suggested_vendor_contact,
1279 l_line_tbl(i).suggested_vendor_phone,
1280 l_line_tbl(i).suggested_vendor_product_code,
1281 l_line_tbl(i).un_number_id,
1282 l_line_tbl(i).hazard_class_id,
1283 l_line_tbl(i).must_use_sugg_vendor_flag,
1284 l_line_tbl(i).reference_num,
1285 l_line_tbl(i).on_rfq_flag,
1286 l_line_tbl(i).urgent_flag,
1287 l_line_tbl(i).cancel_flag,
1288 l_line_tbl(i).quantity_cancelled,
1289 l_line_tbl(i).cancel_date,
1290 l_line_tbl(i).cancel_reason,
1291 l_line_tbl(i).closed_code,
1292 l_line_tbl(i).agent_return_note,
1293 l_line_tbl(i).changed_after_research_flag,
1294 l_line_tbl(i).vendor_id,
1295 l_line_tbl(i).vendor_site_id,
1296 l_line_tbl(i).vendor_contact_id,
1297 l_line_tbl(i).research_agent_id,
1298 l_line_tbl(i).wip_entity_id,
1299 l_line_tbl(i).wip_line_id,
1300 l_line_tbl(i).wip_repetitive_schedule_id,
1301 l_line_tbl(i).wip_operation_seq_num,
1302 l_line_tbl(i).wip_resource_seq_num,
1303 l_line_tbl(i).attribute_category,
1304 l_line_tbl(i).destination_context,
1305 l_line_tbl(i).inventory_source_context,
1306 l_line_tbl(i).vendor_source_context,
1307 l_line_tbl(i).attribute1,
1308 l_line_tbl(i).attribute2,
1309 l_line_tbl(i).attribute3,
1310 l_line_tbl(i).attribute4,
1311 l_line_tbl(i).attribute5,
1312 l_line_tbl(i).attribute6,
1313 l_line_tbl(i).attribute7,
1314 l_line_tbl(i).attribute8,
1315 l_line_tbl(i).attribute9,
1316 l_line_tbl(i).attribute10,
1317 l_line_tbl(i).attribute11,
1318 l_line_tbl(i).attribute12,
1319 l_line_tbl(i).attribute13,
1320 l_line_tbl(i).attribute14,
1321 l_line_tbl(i).attribute15,
1322 l_line_tbl(i).bom_resource_id,
1323 l_line_tbl(i).government_context,
1324 l_line_tbl(i).closed_reason,
1325 l_line_tbl(i).closed_date,
1326 l_line_tbl(i).transaction_reason_code,
1327 l_line_tbl(i).quantity_received,
1328 l_line_tbl(i).order_type_lookup_code,
1329 l_line_tbl(i).purchase_basis,
1330 l_line_tbl(i).matching_basis,
1331 l_line_tbl(i).org_id, -- <R12 MOAC>
1332 'CREATE', --<eTax Integration R12>
1333 l_manufacturer_id,
1334 l_manufacturer_name,
1335 l_manufacturer_pn
1336 );
1337
1338 l_dist_rec.org_id := l_line_tbl(i).org_id; -- <R12 MOAC>
1339
1340 -- It is assumed that only 1 dIstribution line will be there for each
1341 -- INTERNAL Requisition. If Multiple Distributions Lines are to created
1342 -- This procedure should be modified
1343
1344 -- Get Distribution ID from the Distribution Sequence
1345 OPEN dist_line_id_cur;
1346 FETCH dist_line_id_cur INTO l_dist_rec.distribution_id;
1347 CLOSE dist_line_id_cur;
1348
1349 -- Assign Requisition Line ID if NULL
1350 l_dist_rec.requisition_line_id := l_line_tbl(i).requisition_line_id;
1351
1352 -- Assign Requisition Quantity if NULL
1353 l_dist_rec.req_line_quantity := l_line_tbl(i).quantity;
1354
1355 -- Assign Requisition Line Number as Distribution Number
1356 l_dist_rec.distribution_num := l_line_tbl(i).line_num;
1357
1358 -- Assign SYSDATE to gl_encumbered_date
1359 l_dist_rec.gl_encumbered_date := l_today;
1360 --s_chart_of_accounts_id := 101;
1361
1362 -- Get Charge Account ID
1363 l_dist_rec.code_combination_id := get_charge_account_fun
1364 (l_line_tbl(i).destination_organization_id,
1365 l_line_tbl(i).item_id,
1366 l_line_tbl(i).destination_subinventory);
1367
1368 -- Check for valid charge account. If Invalid Raise ERROR
1369 IF NOT valid_account_id_fun (l_dist_rec.code_combination_id,
1370 l_dist_rec.gl_encumbered_date,
1371 d_chart_of_accounts_id) THEN /* bug 5637277 replaced s_chart_of_accounts_id with d_chart_of_accounts_id */
1372 Raise INVALID_CHARGE_ACCOUNT;
1373 END IF;
1374
1375 -- Get Accrual Account ID and Variance Account ID for the
1376 --Destination Organization from MTL_PARAMETERS
1377
1378 OPEN accrual_account_id_cur (l_line_tbl(i).destination_organization_id);
1379 FETCH accrual_account_id_cur
1380 INTO l_dist_rec.accrual_account_id,
1381 l_dist_rec.variance_account_id;
1382 CLOSE accrual_account_id_cur;
1383
1384 -- Check for valid accrual account. If Invalid Raise ERROR
1385 IF NOT valid_account_id_fun (l_dist_rec.accrual_account_id,
1386 l_dist_rec.gl_encumbered_date,
1387 d_chart_of_accounts_id) THEN /* bug 5637277 replaced s_chart_of_accounts_id with d_chart_of_accounts_id */
1388 Raise INVALID_ACCRUAL_ACCOUNT;
1389 END IF;
1390
1391 -- Check for valid variance account. If Invalid Raise ERROR
1392 IF NOT valid_account_id_fun (l_dist_rec.variance_account_id,
1393 l_dist_rec.gl_encumbered_date,
1394 d_chart_of_accounts_id) THEN /* bug 5637277 replaced s_chart_of_accounts_id with d_chart_of_accounts_id */
1395 Raise INVALID_VARIANCE_ACCOUNT;
1396 END IF;
1397
1398 -- Assign Set of Books ID
1399 l_dist_rec.set_of_books_id := l_set_of_books_id;
1400
1401
1402 -- Get Requisition Encumbrance Flag for Financial System Parameters
1403 -- If Req_Encumbrance_flag = 'Y' populate Budget Account ID for
1404 -- Req Distribution
1405 -- If gl_encumbered_flag = 'N' then don't populate gl_encumbered_date
1406 OPEN req_encumbrance_cur (l_dist_rec.set_of_books_id);
1407 FETCH req_encumbrance_cur INTO l_dist_rec.encumbered_flag;
1408 CLOSE req_encumbrance_cur;
1409 IF l_dist_rec.encumbered_flag = 'Y' THEN
1410 OPEN budget_account_cur (l_line_tbl(i).destination_organization_id,
1411 l_line_tbl(i).item_id);
1412 FETCH budget_account_cur INTO l_dist_rec.budget_account_id;
1413 CLOSE budget_account_cur;
1414 -- Check for valid budget account. If Invalid Raise ERROR
1415 IF NOT valid_account_id_fun (l_dist_rec.budget_account_id,
1416 l_dist_rec.gl_encumbered_date,
1417 d_chart_of_accounts_id) THEN -- bug 5637277 replaced s_chart_of_accounts_id with d_chart_of_accounts_id
1418 Raise INVALID_BUDGET_ACCOUNT;
1419 END IF;
1420 ELSE
1421 l_dist_rec.gl_encumbered_date := '';
1422 END IF;
1423
1424
1425 INSERT INTO po_req_distributions
1426 (
1427 distribution_id
1428 ,last_update_date
1429 ,last_updated_by
1430 ,requisition_line_id
1431 ,set_of_books_id
1432 ,code_combination_id
1433 ,req_line_quantity
1434 ,last_update_login
1435 ,creation_date
1436 ,created_by
1437 ,encumbered_flag
1438 ,gl_encumbered_date
1439 ,gl_encumbered_period_name
1440 ,gl_cancelled_date
1441 ,failed_funds_lookup_code
1442 ,encumbered_amount
1443 ,budget_account_id
1444 ,accrual_account_id
1445 ,variance_account_id
1446 ,prevent_encumbrance_flag
1447 ,attribute_category
1448 ,attribute1
1449 ,attribute2
1450 ,attribute3
1451 ,attribute4
1452 ,attribute5
1453 ,attribute6
1454 ,attribute7
1455 ,attribute8
1456 ,attribute9
1457 ,attribute10
1458 ,attribute11
1459 ,attribute12
1460 ,attribute13
1461 ,attribute14
1462 ,attribute15
1463 ,government_context
1464 ,project_id
1465 ,task_id
1466 ,expenditure_type
1467 ,project_accounting_context
1468 ,expenditure_organization_id
1469 ,gl_closed_date
1470 ,source_req_distribution_id
1471 ,distribution_num
1472 ,project_related_flag
1473 ,expenditure_item_date
1474 ,org_id
1475 ,allocation_type
1476 ,allocation_value
1477 ,award_id
1478 ,end_item_unit_number
1479 ,recoverable_tax
1480 ,nonrecoverable_tax
1481 ,recovery_rate
1482 ,tax_recovery_override_flag
1483 ,oke_contract_line_id
1484 ,oke_contract_deliverable_id
1485 )
1486 VALUES
1487 (
1488 l_dist_rec.distribution_id
1489 ,l_today --last_update_date
1490 ,l_user_id --last_updated_by
1491 ,l_dist_rec.requisition_line_id
1492 ,l_dist_rec.set_of_books_id
1493 ,l_dist_rec.code_combination_id
1494 ,l_dist_rec.req_line_quantity
1495 ,l_login_id --last_update_login
1496 ,l_today --creation_date
1497 ,l_user_id --created_by
1498 ,NULL--l_dist_rec.encumbered_flag --Bug:12393759
1499 ,NULL--l_dist_rec.gl_encumbered_date --Bug:12393759
1500 ,NULL--l_dist_rec.gl_encumbered_period_name --Bug:12393759
1501 ,l_dist_rec.gl_cancelled_date
1502 ,l_dist_rec.failed_funds_lookup_code
1503 ,NULL--l_dist_rec.encumbered_amount --Bug:12393759
1504 ,l_dist_rec.budget_account_id
1505 ,l_dist_rec.accrual_account_id
1506 ,l_dist_rec.variance_account_id
1507 ,'Y'--l_dist_rec.prevent_encumbrance_flag --Bug:12393759
1508 ,l_dist_rec.attribute_category
1509 ,l_dist_rec.attribute1
1510 ,l_dist_rec.attribute2
1511 ,l_dist_rec.attribute3
1512 ,l_dist_rec.attribute4
1513 ,l_dist_rec.attribute5
1514 ,l_dist_rec.attribute6
1515 ,l_dist_rec.attribute7
1516 ,l_dist_rec.attribute8
1517 ,l_dist_rec.attribute9
1518 ,l_dist_rec.attribute10
1519 ,l_dist_rec.attribute11
1520 ,l_dist_rec.attribute12
1521 ,l_dist_rec.attribute13
1522 ,l_dist_rec.attribute14
1523 ,l_dist_rec.attribute15
1524 ,l_dist_rec.government_context
1525 ,l_dist_rec.project_id
1526 ,l_dist_rec.task_id
1527 ,l_dist_rec.expenditure_type
1528 ,l_dist_rec.project_accounting_context
1529 ,l_dist_rec.expenditure_organization_id
1530 ,l_dist_rec.gl_closed_date
1531 ,l_dist_rec.source_req_distribution_id
1532 ,l_dist_rec.distribution_num
1533 ,l_dist_rec.project_related_flag
1534 ,l_dist_rec.expenditure_item_date
1535 ,l_dist_rec.org_id
1536 ,l_dist_rec.allocation_type
1537 ,l_dist_rec.allocation_value
1538 ,l_dist_rec.award_id
1539 ,l_dist_rec.end_item_unit_number
1540 ,l_dist_rec.recoverable_tax
1541 ,l_dist_rec.nonrecoverable_tax
1542 ,l_dist_rec.recovery_rate
1543 ,l_dist_rec.tax_recovery_override_flag
1544 ,l_dist_rec.oke_contract_line_id
1545 ,l_dist_rec.oke_contract_deliverable_id
1546 );
1547
1548 END LOOP;
1549
1550 -- Create Supply Record for the Requisition.
1551
1552 l_create_req_supply := PO_SUPPLY.create_req(l_header_rec.requisition_header_id,
1553 'REQ HDR');
1554
1555 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1556 RAISE FND_API.G_EXC_ERROR;
1557 --Bug 13254403 Added call to maintain_mtl_supply
1558 ELSE
1559 l_return_value := PO_SUPPLY.maintain_mtl_supply;
1560 IF NOT l_return_value THEN
1561 RAISE FND_API.G_EXC_ERROR;
1562 END IF;
1563 END IF;
1564
1565
1566 fnd_msg_pub.count_and_get
1567 ( p_count => x_msg_count
1568 , p_data => x_msg_data);
1569
1570 px_header_rec := l_header_rec;
1571 px_line_table := l_line_tbl;
1572
1573
1574 EXCEPTION
1575 WHEN INVALID_ITEM THEN
1576 po_message_s.app_error('PO_RI_ITEM_DESC_MISMATCH');
1577 raise;
1578
1579 WHEN UNIT_PRICE_LT_0 THEN
1580 po_message_s.app_error('PO_RI_UNIT_PRICE_LT_0');
1581 raise;
1582
1583 WHEN INVALID_ITEM_CATEGORY THEN
1584 po_message_s.app_error('PO_RI_INVALID_CATEGORY_ID');
1585 raise;
1586
1587 WHEN INVALID_QUANTITY THEN
1588 po_message_s.app_error('PO_RI_QUANTITY_LE_0');
1589 raise;
1590
1591 WHEN INVALID_SOURCE_TYPE THEN
1592 po_message_s.app_error('PO_RI_INVALID_SOURCE_TYPE_CODE');
1593 raise;
1594
1595 WHEN INVALID_CHARGE_ACCOUNT THEN
1596 po_message_s.app_error('PO_RI_INVALID_CHARGE_ACC_ID');
1597 raise;
1598
1599 WHEN INVALID_ACCRUAL_ACCOUNT THEN
1600 po_message_s.app_error('PO_RI_INVALID_ACCRUAL_ACC_ID');
1601 raise;
1602
1603 WHEN INVALID_BUDGET_ACCOUNT THEN
1604 po_message_s.app_error('PO_RI_INVALID_BUDGET_ACC_ID');
1605 raise;
1606
1607 WHEN INVALID_VARIANCE_ACCOUNT THEN
1608 po_message_s.app_error('PO_RI_INVALID_VARIANCE_ACC_ID');
1609 raise;
1610
1611 WHEN INVALID_AUTH_STATUS THEN
1612 po_message_s.app_error('PO_RI_INVALID_AUTH_STATUS');
1613 raise;
1614
1615 WHEN INVALID_PREPARER_ID THEN
1616 po_message_s.app_error('PO_RI_INACTIVE_PREPARER');
1617 raise;
1618
1619 WHEN INVALID_LOCATION_ID THEN
1620 po_message_s.app_error('PO_RI_INVALID_LOCATION');
1621 raise;
1622
1623 WHEN INVALID_DESTINATION_ORG THEN
1624 po_message_s.app_error('PO_RI_INVALID_DEST_ORG');
1625 raise;
1626
1627 WHEN INVALID_UNIT_OF_MEASURE THEN
1628 po_message_s.app_error('PO_RI_M_INVALID_UOM');
1629 raise;
1630
1631 WHEN FND_API.G_EXC_ERROR THEN
1632 NULL;
1633
1634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1635 FND_MESSAGE.SET_NAME('PO', 'PO_UNEXPECTED_EXEC_ERRORS');
1636 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
1637 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
1638 FND_MSG_PUB.ADD;
1639 fnd_msg_pub.count_and_get
1640 ( p_count => x_msg_count
1641 , p_data => x_msg_data);
1642 x_return_status := FND_API.G_RET_STS_ERROR;
1643
1644 WHEN OTHERS THEN
1645 FND_MESSAGE.SET_NAME('PO', 'PO_UNEXPECTED_EXEC_ERRORS');
1646 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
1647 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
1648 FND_MSG_PUB.ADD;
1649 fnd_msg_pub.count_and_get
1650 ( p_count => x_msg_count
1651 , p_data => x_msg_data);
1652 x_return_status := FND_API.G_RET_STS_ERROR;
1653
1654 END;
1655
1656 END PO_CREATE_REQUISITION_SV;
[Home] [Help]