1 Package Body PO_CREATE_REQUISITION_SV AS
2 /* $Header: POXCARQB.pls 120.8.12010000.3 2008/09/22 18:19:05 rohbansa 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
658
659 -- bug5176308
660 -- removed the cursor to get req number as the logic is moved to
661 -- an API
662
663 -- Cursor to get unique Requisition_Header_ID
664 CURSOR req_header_id_cur IS
665 SELECT po_requisition_headers_s.nextval
666 FROM sys.dual;
667
668 -- Cursor to get unique Requisition_Line_ID
669 CURSOR req_line_id_cur IS
670 SELECT po_requisition_lines_s.nextval
671 FROM sys.dual;
672
673 -- Cursor to get unique Distribution_id
674 CURSOR dist_line_id_cur IS
675 SELECT po_req_distributions_s.nextval
676 FROM sys.dual;
677
678 -- Cursor to get Accrual Account ID and Variance Account ID
679 -- For Destination Type Code INVENTORY get accrual account id
680 -- from MTL_PARAMETERS
681 -- Per Requisition Import program (pocis.opc).
682 CURSOR accrual_account_id_cur (p_destination_organization_id NUMBER) IS
683 SELECT mp.ap_accrual_account,
684 mp.invoice_price_var_account
685 FROM mtl_parameters mp
686 WHERE mp.organization_id = p_destination_organization_id;
687
688 -- Get Default Line Type
689 CURSOR line_type_cur (p_org_id NUMBER) IS
690 SELECT line_type_id
691 FROM PO_SYSTEM_PARAMETERS
692 WHERE org_id = p_org_id;
693
694 -- Get Item Description for a given Item ID
695 -- For the purpose of creating Approve Internal Requisition
696 -- it is assumed that the calling procedure will always pass the Item ID
697 -- so that Item Description can be derived.
698 CURSOR item_desc_cur(p_item_id NUMBER, p_orgn_id NUMBER) IS
699 SELECT description
700 FROM mtl_system_items_b
701 WHERE inventory_item_id = p_item_id
702 AND organization_id = p_orgn_id;
703
704 -- Get Item Category ID
705 -- As in Requisition Import
706 CURSOR item_category_cur(p_item_id NUMBER, p_destination_org_id NUMBER) IS
707 SELECT mic.category_id
708 FROM mtl_item_categories mic,
709 mtl_default_sets_view mdsv
710 WHERE mic.inventory_item_id = p_item_id
711 AND mic.organization_id = p_destination_org_id
712 AND mic.category_set_id = mdsv.category_set_id
713 AND mdsv.functional_area_id = 2;
714
715 -- For Source Type Code validation, if passed to the procedure
716 CURSOR source_type_cur (p_source_type_code VARCHAR2) IS
717 SELECT 'X'
718 FROM po_lookup_codes plc
719 WHERE plc.lookup_type = 'REQUISITION SOURCE TYPE'
720 AND plc.lookup_code = p_source_type_code;
721
722 -- For Destination Type Code validation, if passed to the procedure
723 CURSOR destination_type_cur (p_destination_type_code VARCHAR2) IS
724 SELECT 'X'
725 FROM po_lookup_codes plc
726 WHERE plc.lookup_type = 'DESTINATION TYPE'
727 AND plc.lookup_code = p_destination_type_code;
728
729 -- For Authorization Status validation, if passed to the procedure
730 CURSOR authorization_status_cur (p_authorization_status VARCHAR2) IS
731 SELECT 'X'
732 FROM po_lookup_codes plc
733 WHERE plc.lookup_type = 'AUTHORIZATION STATUS'
734 AND plc.lookup_code = p_authorization_status;
735
736 -- Get Set of Books ID for a given Org_ID - Mandatory in PO_REQ_DISTRIBUTIONS
737 CURSOR set_of_books_cur (p_organization_id NUMBER) IS
738 SELECT set_of_books_id
739 FROM hr_operating_units
740 WHERE organization_id = p_organization_id;
741
742 -- If encumbrance flag is 'Y' get the budget account
743 -- For Internal Req, Destination Type Code will be INVENTORY
744 -- Hence, it is assumed that the budget account will come
745 -- from MTL_PARAMETERS for the Item and the Destination Organization
746 CURSOR budget_account_cur (p_destination_organization_id NUMBER,
747 p_item_id NUMBER) IS
748 SELECT nvl (msi.encumbrance_account,mp.encumbrance_account)
749 FROM mtl_system_items msi,
750 mtl_parameters mp
751 WHERE msi.inventory_item_id = p_item_id
752 AND msi.organization_id = p_destination_organization_id
753 AND mp.organization_id = msi.organization_id;
754
755 -- Get Requisition Encumbrance Flag for the Set of Books
756 -- Based of this flag Budget Account will be populated
757 -- in PO_REQ_DISTRIBUTIONS
758 CURSOR req_encumbrance_cur (p_set_of_books_id NUMBER) IS
759 SELECT nvl (fsp.req_encumbrance_flag,'N')
760 FROM financials_system_parameters fsp
761 WHERE fsp.set_of_books_id = p_set_of_books_id;
762
763 -- Get Charge Account for the Item and Organization
764 CURSOR charge_account_cur (p_destination_organization_id NUMBER,
765 p_item_id NUMBER) IS
766 SELECT NVL(expense_account,-1)
767 FROM mtl_system_items
768 WHERE inventory_item_id = p_item_id
769 AND organization_id = p_destination_organization_id;
770
771 -- Get Unit_of_Measure from MTL_UNIT_OF_MEASURES, since OM passes
772 -- only UOM_CODE and PO requires UNIT_OF_MEASURE. This is being done
773 -- to fix the problem of line not showing up from POXRQVRQ form
774 CURSOR unit_of_measure_cur (p_uom_code VARCHAR2) IS
775 SELECT mum.unit_of_measure
776 FROM mtl_units_of_measure mum
777 WHERE mum.uom_code = p_uom_code;
778
779 BEGIN
780
781 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
782 -- initialize message list
783 FND_MSG_PUB.initialize;
784 END IF;
785
786 -- Standard call to check for call compatibility.
787
788 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
789 p_api_version,
790 l_api_name,
791 G_PKG_NAME)
792 THEN
793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
794 END IF;
795
796 -- initialize return status
797 x_return_status := FND_API.G_RET_STS_SUCCESS;
798
799 l_header_rec := px_header_rec;
800 l_line_tbl := px_line_table;
801
802 -- get all the values required to insert into po_requisition_header table
803 SELECT Sysdate INTO l_today FROM dual;
804
805 l_user_id := fnd_global.user_id;
806 l_login_id := fnd_global.login_id;
807
808
809 -- Get Requisition_header_id
810 OPEN req_header_id_cur;
811 FETCH req_header_id_cur into l_header_rec.requisition_header_id;
812 CLOSE req_header_id_cur;
813
814 -- bug5176308
815 l_header_rec.segment1 :=
816 PO_CORE_SV1.default_po_unique_identifier
817 ( x_table_name => 'PO_REQUISITION_HEADERS'
818 );
819
820 -- check for uniqueness of requisition_number
821 BEGIN
822
823 SELECT 'X' INTO l_dummy
824 FROM DUAL
825 WHERE NOT EXISTS
826 ( SELECT 'X'
827 FROM po_requisition_headers
828 WHERE Segment1 = l_header_rec.segment1);
829
830 EXCEPTION
831 WHEN NO_DATA_FOUND THEN
832 po_message_s.app_error('PO_ALL_ENTER_UNIQUE_VAL');
833 raise;
834 WHEN OTHERS THEN
835 po_message_s.sql_error('check_unique','010',sqlcode);
836 raise;
837 END;
838
839 -- Raise Error, if preparer_id IS NULL
840 IF l_header_rec.preparer_id IS NULL THEN
841 Raise INVALID_PREPARER_ID;
842 END IF;
843
844 -- Default Summary Flag to 'N', if NULL
845 IF l_header_rec.summary_flag IS NULL THEN
846 l_header_rec.summary_flag := 'N';
847 END IF;
848
849 -- Default Enabled Flag to 'Y', if NULL
850 IF l_header_rec.enabled_flag IS NULL THEN
851 l_header_rec.enabled_flag := 'Y';
852 END IF;
853
854 -- Default Transferred to OE Flag to 'Y', if NULL
855 -- This is done to make sure that these requisitions don't get
856 -- picked up at the time of populating order interface
857 IF l_header_rec.transferred_to_oe_flag IS NULL THEN
858 l_header_rec.transferred_to_oe_flag := 'Y';
859 END IF;
860
861 -- Default Authorization to APPROVED, if NULL
862 IF l_header_rec.authorization_status IS NULL THEN
863 l_header_rec.authorization_status := 'APPROVED';
864 ELSE
865 OPEN authorization_status_cur (l_header_rec.authorization_status);
866 FETCH authorization_status_cur INTO l_dummy;
867 IF authorization_status_cur%NOTFOUND THEN
868 CLOSE authorization_status_cur;
869 Raise INVALID_AUTH_STATUS;
870 END IF;
871 CLOSE authorization_status_cur;
872 END IF;
873
874 -- create approved requisition headers
875 -- insert into PO_REQUISITION_HEADERS
876 INSERT INTO po_requisition_headers(
877 org_id,
878 requisition_header_id,
879 preparer_id,
880 last_update_date,
881 last_updated_by,
882 segment1,
883 summary_flag,
884 enabled_flag,
885 segment2,
886 segment3,
887 segment4,
888 segment5,
889 start_date_active,
890 end_date_active,
891 last_update_login,
892 creation_date,
893 created_by,
894 description,
895 authorization_status,
896 note_to_authorizer,
897 type_lookup_code,
898 transferred_to_oe_flag,
899 attribute_category,
900 attribute1,
901 attribute2,
902 attribute3,
903 attribute4,
904 attribute5,
905 attribute6,
906 attribute7,
907 attribute8,
908 attribute9,
909 attribute10,
910 attribute11,
911 attribute12,
912 attribute13,
913 attribute14,
914 attribute15,
915 government_context,
916 closed_code,
917 tax_attribute_update_code --<eTax Integration R12>
918 ) VALUES (
919 l_header_rec.org_id,
920 l_header_rec.requisition_header_id,
921 l_header_rec.preparer_id,
922 l_today,
923 l_user_id,
924 l_header_rec.segment1,
925 l_header_rec.summary_flag,
926 l_header_rec.enabled_flag,
927 l_header_rec.segment2,
928 l_header_rec.segment3,
929 l_header_rec.segment4,
930 l_header_rec.segment5,
931 l_header_rec.start_date_active,
932 l_header_rec.end_date_active,
933 l_header_rec.last_update_login,
934 l_today,
935 l_user_id,
936 l_header_rec.description,
937 l_header_rec.authorization_status,
938 l_header_rec.note_to_authorizer,
939 l_header_rec.type_lookup_code,
940 l_header_rec.transferred_to_oe_flag,
941 l_header_rec.attribute_category,
942 l_header_rec.attribute1,
943 l_header_rec.attribute2,
944 l_header_rec.attribute3,
945 l_header_rec.attribute4,
946 l_header_rec.attribute5,
947 l_header_rec.attribute6,
948 l_header_rec.attribute7,
949 l_header_rec.attribute8,
950 l_header_rec.attribute9,
951 l_header_rec.attribute10,
952 l_header_rec.attribute11,
953 l_header_rec.attribute12,
954 l_header_rec.attribute13,
955 l_header_rec.attribute14,
956 l_header_rec.attribute15,
957 l_header_rec.government_context,
958 l_header_rec.closed_code ,
959 'CREATE' --<eTax Integration R12>
960 );
961
962 -- get all the values required to insert into po_requisition_lines table
963
964 -- line_type_id for Requisition
965 OPEN line_type_cur (l_header_rec.org_id);
966 FETCH line_type_cur INTO l_line_type_id;
967 CLOSE line_type_cur;
968
969 FOR I IN 1..l_line_tbl.COUNT LOOP
970
971 -- Get Set of Books Id
972 OPEN set_of_books_cur (l_line_tbl(i).org_id);
973 FETCH set_of_books_cur INTO l_set_of_books_id;
974 CLOSE set_of_books_cur;
975
976 -- get requisition_line_id
977 IF (l_line_tbl(i).Requisition_Line_Id is NULL) THEN
978 OPEN req_line_id_cur;
979 FETCH req_line_id_cur INTO l_line_tbl(i).requisition_line_id;
980 CLOSE req_line_id_cur;
981 END IF;
982
983 -- Assign Requisition Header ID
984 IF (l_line_tbl(i).requisition_header_id IS NULL) THEN
985 l_line_tbl(i).requisition_header_id := l_header_rec.requisition_header_id;
986 END IF;
987
988 -- Assign the default line_type_id if there isn't one
989 IF (l_line_tbl(i).line_type_id IS NULL) THEN
990 l_line_tbl(i).line_type_id := l_line_type_id;
991 END IF;
992
993 -- <SERVICES FPJ START>
994 -- Populate the values of order_type_lookup_code, purchase_basis
995 -- and matching_basis based on the line_type_id
996 BEGIN
997 SELECT order_type_lookup_code,
998 purchase_basis,
999 matching_basis
1000 INTO l_line_tbl(i).order_type_lookup_code,
1001 l_line_tbl(i).purchase_basis,
1002 l_line_tbl(i).matching_basis
1003 FROM po_line_types
1004 WHERE line_type_id = l_line_tbl(i).line_type_id;
1005 EXCEPTION
1006 WHEN OTHERS THEN
1007 null;
1008 END;
1009 -- <SERVICES FPJ END>
1010
1011 -- INVALID_LOCATION_ID and INVALID_DESTINATION_ORG exceptions
1012 -- were added as part of this package to avoid seeding new messages
1013 -- in OM when validating deliver_to_location_id and destination_organization_id
1014
1015 IF l_line_tbl(i).deliver_to_location_id IS NULL THEN
1016 Raise INVALID_LOCATION_ID;
1017 END IF;
1018
1019 IF l_line_tbl(i).destination_organization_id IS NULL THEN
1020 Raise INVALID_DESTINATION_ORG;
1021 END IF;
1022
1023 -- Get Item Description, if NULL
1024 -- It is assumed that whenever this procedure is called, Item ID will be
1025 -- passed. If Item Description is NULL, Raise ERROR.
1026 IF (l_line_tbl(i).item_description IS NULL) THEN
1027 OPEN item_desc_cur(l_line_tbl(i).item_id, l_line_tbl(i).destination_organization_id);
1028 FETCH item_desc_cur INTO l_line_tbl(i).item_description;
1029 IF item_desc_cur%NOTFOUND THEN
1030 CLOSE item_desc_cur;
1031 Raise INVALID_ITEM;
1032 END IF;
1033 CLOSE item_desc_cur;
1034 END IF;
1035
1036 -- Get Category ID of the Item
1037 IF (l_line_tbl(i).category_id IS NULL) THEN
1038 OPEN item_category_cur (l_line_tbl(i).item_id, l_line_tbl(i).destination_organization_id);
1039 FETCH item_category_cur INTO l_line_tbl(i).category_id;
1040 IF item_category_cur%NOTFOUND THEN
1041 CLOSE item_category_cur;
1042 Raise INVALID_ITEM_CATEGORY;
1043 END IF;
1044 CLOSE item_category_cur;
1045 END IF;
1046
1047 -- Derive Unit_of_Measure from Uom_Code passed from OM
1048 OPEN unit_of_measure_cur(l_line_tbl(i).uom_code);
1049 FETCH unit_of_measure_cur INTO l_line_tbl(i).unit_meas_lookup_code;
1050 IF unit_of_measure_cur%NOTFOUND THEN
1051 CLOSE unit_of_measure_cur;
1052 Raise INVALID_UNIT_OF_MEASURE;
1053 ELSE
1054 CLOSE unit_of_measure_cur;
1055 END IF;
1056
1057 /* Get Unit Price and Currency Code*/
1058 get_unit_price_prc (l_line_tbl(i).item_id
1059 ,l_line_tbl(i).source_organization_id
1060 ,l_line_tbl(i).destination_organization_id
1061 ,l_set_of_books_id
1062 ,d_chart_of_accounts_id
1063 ,l_line_tbl(i).currency_code
1064 ,l_line_tbl(i).unit_price );
1065
1066 -- Quantity MUST be > 0 for Requisition
1067 IF (l_line_tbl(i).quantity <= 0) THEN
1068 Raise INVALID_QUANTITY;
1069 END IF;
1070
1071 -- Default INVENTORY as Source_Type_Code for Internal Requisitions if not passed
1072 -- If passed, validate Source Type Code from PO_LOOKUP_CODES
1073 -- If Invalid, Raise Error
1074 IF (l_line_tbl(i).source_type_code IS NULL ) THEN
1075 l_line_tbl(i).source_type_code := 'INVENTORY';
1076 ELSE
1077 OPEN source_type_cur(l_line_tbl(i).source_type_code);
1078 FETCH source_type_cur INTO l_dummy;
1079 IF source_type_cur%NOTFOUND THEN
1080 CLOSE source_type_cur;
1081 Raise INVALID_SOURCE_TYPE;
1082 END IF;
1083 CLOSE source_type_cur;
1084 END IF;
1085
1086 -- Default Encumbered_Flag to 'N'
1087 -- Since the funds reservation API is not called from here, this flag is
1088 -- set to 'N'
1089 IF (l_line_tbl(i).encumbered_flag IS NULL) THEN
1090 l_line_tbl(i).encumbered_flag := 'N';
1091 END IF;
1092
1093 -- Default Cancel_flag to 'N'
1094 IF (l_line_tbl(i).cancel_flag IS NULL) THEN
1095 l_line_tbl(i).cancel_flag := 'N';
1096 END IF;
1097
1098 -- Default INVENTORY as Destination_Type_Code for Internal Requisitions if not passed
1099 -- If passed, validate Destination Type Code from PO_LOOKUP_CODES
1100 -- If Invalid, Raise Error
1101 IF (l_line_tbl(i).destination_type_code IS NULL ) THEN
1102 l_line_tbl(i).destination_type_code := 'INVENTORY';
1103 ELSE
1104 OPEN destination_type_cur(l_line_tbl(i).destination_type_code);
1105 FETCH destination_type_cur INTO l_dummy;
1106 IF destination_type_cur%NOTFOUND THEN
1107 CLOSE destination_type_cur;
1108 Raise INVALID_DESTINATION_TYPE;
1109 END IF;
1110 CLOSE destination_type_cur;
1111 END IF;
1112
1113 -- insert into po_requisition_lines table
1114 -- <SERVICES FPJ>
1115 -- Added order_type_lookup_code, purchase_basis and
1116 -- matching_basis
1117
1118
1119 IF (l_line_tbl(i).item_id IS NOT NULL) Then
1120
1121
1122 po_attribute_values_pvt.get_item_attributes_values(l_line_tbl(i).item_id, l_manufacturer_pn,l_manufacturer_name,
1123 l_lead_time,l_manufacturer_id) ;
1124
1125 END IF ;
1126
1127
1128 INSERT INTO po_requisition_lines(
1129 requisition_line_id,
1130 requisition_header_id,
1131 line_num,
1132 line_type_id,
1133 category_id,
1134 item_description,
1135 unit_meas_lookup_code,
1136 unit_price,
1137 quantity,
1138 deliver_to_location_id,
1139 to_person_id,
1140 last_update_date,
1141 last_updated_by,
1142 source_type_code,
1143 last_update_login,
1144 creation_date,
1145 created_by,
1146 item_id,
1147 item_revision,
1148 encumbered_flag,
1149 rfq_required_flag,
1150 need_by_date,
1151 source_organization_id,
1152 source_subinventory,
1153 destination_type_code,
1154 destination_organization_id,
1155 destination_subinventory,
1156 line_location_id,
1157 modified_by_agent_flag,
1158 parent_req_line_id,
1159 justification,
1160 note_to_agent,
1161 note_to_receiver,
1162 purchasing_agent_id,
1163 document_type_code,
1164 blanket_po_header_id,
1165 blanket_po_line_num,
1166 currency_code,
1167 rate_type,
1168 rate_date,
1169 rate,
1170 currency_unit_price,
1171 suggested_vendor_name,
1172 suggested_vendor_location,
1173 suggested_vendor_contact,
1174 suggested_vendor_phone,
1175 suggested_vendor_product_code,
1176 un_number_id,
1177 hazard_class_id,
1178 must_use_sugg_vendor_flag,
1179 reference_num,
1180 on_rfq_flag,
1181 urgent_flag,
1182 cancel_flag,
1183 quantity_cancelled,
1184 cancel_date,
1185 cancel_reason,
1186 closed_code,
1187 agent_return_note,
1188 changed_after_research_flag,
1189 vendor_id,
1190 vendor_site_id,
1191 vendor_contact_id,
1192 research_agent_id,
1193 wip_entity_id,
1194 wip_line_id,
1195 wip_repetitive_schedule_id,
1196 wip_operation_seq_num,
1197 wip_resource_seq_num,
1198 attribute_category,
1199 destination_context,
1200 inventory_source_context,
1201 vendor_source_context,
1202 attribute1,
1203 attribute2,
1204 attribute3,
1205 attribute4,
1206 attribute5,
1207 attribute6,
1208 attribute7,
1209 attribute8,
1210 attribute9,
1211 attribute10,
1212 attribute11,
1213 attribute12,
1214 attribute13,
1215 attribute14,
1216 attribute15,
1217 bom_resource_id,
1218 government_context,
1219 closed_reason,
1220 closed_date,
1221 transaction_reason_code,
1222 quantity_received,
1223 order_type_lookup_code,
1224 purchase_basis,
1225 matching_basis,
1226 org_id, -- <R12 MOAC>
1227 tax_attribute_update_code, --<eTax Integration R12>
1228 MANUFACTURER_ID, --bug 7387487
1229 MANUFACTURER_NAME,
1230 MANUFACTURER_PART_NUMBER
1231 ) VALUES (
1232 l_line_tbl(i).requisition_line_id,
1233 l_line_tbl(i).requisition_header_id,
1234 l_line_tbl(i).line_num,
1235 l_line_tbl(i).line_type_id,
1236 l_line_tbl(i).category_id,
1237 l_line_tbl(i).item_description,
1238 l_line_tbl(i).unit_meas_lookup_code,
1239 l_line_tbl(i).unit_price,
1240 l_line_tbl(i).quantity,
1241 l_line_tbl(i).deliver_to_location_id,
1242 l_line_tbl(i).to_person_id,
1243 l_today, -- last_update_date
1244 l_user_id, --last_updated_by
1245 l_line_tbl(i).source_type_code,
1246 l_login_id, --last_update_login
1247 l_today, --creation_date
1248 l_user_id, --created_by
1249 l_line_tbl(i).item_id,
1250 l_line_tbl(i).item_revision,
1251 l_line_tbl(i).encumbered_flag,
1252 l_line_tbl(i).rfq_required_flag,
1253 l_line_tbl(i).need_by_date,
1254 l_line_tbl(i).source_organization_id,
1255 l_line_tbl(i).source_subinventory,
1256 l_line_tbl(i).destination_type_code,
1257 l_line_tbl(i).destination_organization_id,
1258 l_line_tbl(i).destination_subinventory,
1259 l_line_tbl(i).line_location_id,
1260 l_line_tbl(i).modified_by_agent_flag,
1261 l_line_tbl(i).parent_req_line_id,
1262 l_line_tbl(i).justification,
1263 l_line_tbl(i).note_to_agent,
1264 l_line_tbl(i).note_to_receiver,
1265 l_line_tbl(i).purchasing_agent_id,
1266 l_line_tbl(i).document_type_code,
1267 l_line_tbl(i).blanket_po_header_id,
1268 l_line_tbl(i).blanket_po_line_num,
1269 l_line_tbl(i).currency_code,
1270 l_line_tbl(i).rate_type,
1271 l_line_tbl(i).rate_date,
1272 l_line_tbl(i).rate,
1273 l_line_tbl(i).currency_unit_price,
1274 l_line_tbl(i).suggested_vendor_name,
1275 l_line_tbl(i).suggested_vendor_location,
1276 l_line_tbl(i).suggested_vendor_contact,
1277 l_line_tbl(i).suggested_vendor_phone,
1278 l_line_tbl(i).suggested_vendor_product_code,
1279 l_line_tbl(i).un_number_id,
1280 l_line_tbl(i).hazard_class_id,
1281 l_line_tbl(i).must_use_sugg_vendor_flag,
1282 l_line_tbl(i).reference_num,
1283 l_line_tbl(i).on_rfq_flag,
1284 l_line_tbl(i).urgent_flag,
1285 l_line_tbl(i).cancel_flag,
1286 l_line_tbl(i).quantity_cancelled,
1287 l_line_tbl(i).cancel_date,
1288 l_line_tbl(i).cancel_reason,
1289 l_line_tbl(i).closed_code,
1290 l_line_tbl(i).agent_return_note,
1291 l_line_tbl(i).changed_after_research_flag,
1292 l_line_tbl(i).vendor_id,
1293 l_line_tbl(i).vendor_site_id,
1294 l_line_tbl(i).vendor_contact_id,
1295 l_line_tbl(i).research_agent_id,
1296 l_line_tbl(i).wip_entity_id,
1297 l_line_tbl(i).wip_line_id,
1298 l_line_tbl(i).wip_repetitive_schedule_id,
1299 l_line_tbl(i).wip_operation_seq_num,
1300 l_line_tbl(i).wip_resource_seq_num,
1301 l_line_tbl(i).attribute_category,
1302 l_line_tbl(i).destination_context,
1303 l_line_tbl(i).inventory_source_context,
1304 l_line_tbl(i).vendor_source_context,
1305 l_line_tbl(i).attribute1,
1306 l_line_tbl(i).attribute2,
1307 l_line_tbl(i).attribute3,
1308 l_line_tbl(i).attribute4,
1309 l_line_tbl(i).attribute5,
1310 l_line_tbl(i).attribute6,
1311 l_line_tbl(i).attribute7,
1312 l_line_tbl(i).attribute8,
1313 l_line_tbl(i).attribute9,
1314 l_line_tbl(i).attribute10,
1315 l_line_tbl(i).attribute11,
1316 l_line_tbl(i).attribute12,
1317 l_line_tbl(i).attribute13,
1318 l_line_tbl(i).attribute14,
1319 l_line_tbl(i).attribute15,
1320 l_line_tbl(i).bom_resource_id,
1321 l_line_tbl(i).government_context,
1322 l_line_tbl(i).closed_reason,
1323 l_line_tbl(i).closed_date,
1324 l_line_tbl(i).transaction_reason_code,
1325 l_line_tbl(i).quantity_received,
1326 l_line_tbl(i).order_type_lookup_code,
1327 l_line_tbl(i).purchase_basis,
1328 l_line_tbl(i).matching_basis,
1329 l_line_tbl(i).org_id, -- <R12 MOAC>
1330 'CREATE', --<eTax Integration R12>
1331 l_manufacturer_id,
1332 l_manufacturer_name,
1333 l_manufacturer_pn
1334 );
1335
1336 l_dist_rec.org_id := l_line_tbl(i).org_id; -- <R12 MOAC>
1337
1338 -- It is assumed that only 1 dIstribution line will be there for each
1339 -- INTERNAL Requisition. If Multiple Distributions Lines are to created
1340 -- This procedure should be modified
1341
1342 -- Get Distribution ID from the Distribution Sequence
1343 OPEN dist_line_id_cur;
1344 FETCH dist_line_id_cur INTO l_dist_rec.distribution_id;
1345 CLOSE dist_line_id_cur;
1346
1347 -- Assign Requisition Line ID if NULL
1348 l_dist_rec.requisition_line_id := l_line_tbl(i).requisition_line_id;
1349
1350 -- Assign Requisition Quantity if NULL
1351 l_dist_rec.req_line_quantity := l_line_tbl(i).quantity;
1352
1353 -- Assign Requisition Line Number as Distribution Number
1354 l_dist_rec.distribution_num := l_line_tbl(i).line_num;
1355
1356 -- Assign SYSDATE to gl_encumbered_date
1357 l_dist_rec.gl_encumbered_date := l_today;
1358 --s_chart_of_accounts_id := 101;
1359
1360 -- Get Charge Account ID
1361 l_dist_rec.code_combination_id := get_charge_account_fun
1362 (l_line_tbl(i).destination_organization_id,
1363 l_line_tbl(i).item_id,
1364 l_line_tbl(i).destination_subinventory);
1365
1366 -- Check for valid charge account. If Invalid Raise ERROR
1367 IF NOT valid_account_id_fun (l_dist_rec.code_combination_id,
1368 l_dist_rec.gl_encumbered_date,
1369 d_chart_of_accounts_id) THEN /* bug 5637277 replaced s_chart_of_accounts_id with d_chart_of_accounts_id */
1370 Raise INVALID_CHARGE_ACCOUNT;
1371 END IF;
1372
1373 -- Get Accrual Account ID and Variance Account ID for the
1374 --Destination Organization from MTL_PARAMETERS
1375
1376 OPEN accrual_account_id_cur (l_line_tbl(i).destination_organization_id);
1377 FETCH accrual_account_id_cur
1378 INTO l_dist_rec.accrual_account_id,
1379 l_dist_rec.variance_account_id;
1380 CLOSE accrual_account_id_cur;
1381
1382 -- Check for valid accrual account. If Invalid Raise ERROR
1383 IF NOT valid_account_id_fun (l_dist_rec.accrual_account_id,
1384 l_dist_rec.gl_encumbered_date,
1385 d_chart_of_accounts_id) THEN /* bug 5637277 replaced s_chart_of_accounts_id with d_chart_of_accounts_id */
1386 Raise INVALID_ACCRUAL_ACCOUNT;
1387 END IF;
1388
1389 -- Check for valid variance account. If Invalid Raise ERROR
1390 IF NOT valid_account_id_fun (l_dist_rec.variance_account_id,
1391 l_dist_rec.gl_encumbered_date,
1392 d_chart_of_accounts_id) THEN /* bug 5637277 replaced s_chart_of_accounts_id with d_chart_of_accounts_id */
1393 Raise INVALID_VARIANCE_ACCOUNT;
1394 END IF;
1395
1396 -- Assign Set of Books ID
1397 l_dist_rec.set_of_books_id := l_set_of_books_id;
1398
1399 -- Get Requisition Encumbrance Flag for Financial System Parameters
1400 -- If Req_Encumbrance_flag = 'Y' populate Budget Account ID for
1401 -- Req Distribution
1402 -- If gl_encumbered_flag = 'N' then don't populate gl_encumbered_date
1403 OPEN req_encumbrance_cur (l_dist_rec.set_of_books_id);
1404 FETCH req_encumbrance_cur INTO l_dist_rec.encumbered_flag;
1405 CLOSE req_encumbrance_cur;
1406 IF l_dist_rec.encumbered_flag = 'Y' THEN
1407 OPEN budget_account_cur (l_line_tbl(i).destination_organization_id,
1408 l_line_tbl(i).item_id);
1409 FETCH budget_account_cur INTO l_dist_rec.budget_account_id;
1410 CLOSE budget_account_cur;
1411 -- Check for valid budget account. If Invalid Raise ERROR
1412 IF NOT valid_account_id_fun (l_dist_rec.budget_account_id,
1413 l_dist_rec.gl_encumbered_date,
1414 d_chart_of_accounts_id) THEN /* bug 5637277 replaced s_chart_of_accounts_id with d_chart_of_accounts_id */
1415 Raise INVALID_BUDGET_ACCOUNT;
1416 END IF;
1417 ELSE
1418 l_dist_rec.gl_encumbered_date := '';
1419 END IF;
1420
1421
1422 INSERT INTO po_req_distributions
1423 (
1424 distribution_id
1425 ,last_update_date
1426 ,last_updated_by
1427 ,requisition_line_id
1428 ,set_of_books_id
1429 ,code_combination_id
1430 ,req_line_quantity
1431 ,last_update_login
1432 ,creation_date
1433 ,created_by
1434 ,encumbered_flag
1435 ,gl_encumbered_date
1436 ,gl_encumbered_period_name
1437 ,gl_cancelled_date
1438 ,failed_funds_lookup_code
1439 ,encumbered_amount
1440 ,budget_account_id
1441 ,accrual_account_id
1442 ,variance_account_id
1443 ,prevent_encumbrance_flag
1444 ,attribute_category
1445 ,attribute1
1446 ,attribute2
1447 ,attribute3
1448 ,attribute4
1449 ,attribute5
1450 ,attribute6
1451 ,attribute7
1452 ,attribute8
1453 ,attribute9
1454 ,attribute10
1455 ,attribute11
1456 ,attribute12
1457 ,attribute13
1458 ,attribute14
1459 ,attribute15
1460 ,government_context
1461 ,project_id
1462 ,task_id
1463 ,expenditure_type
1464 ,project_accounting_context
1465 ,expenditure_organization_id
1466 ,gl_closed_date
1467 ,source_req_distribution_id
1468 ,distribution_num
1469 ,project_related_flag
1470 ,expenditure_item_date
1471 ,org_id
1472 ,allocation_type
1473 ,allocation_value
1474 ,award_id
1475 ,end_item_unit_number
1476 ,recoverable_tax
1477 ,nonrecoverable_tax
1478 ,recovery_rate
1479 ,tax_recovery_override_flag
1480 ,oke_contract_line_id
1481 ,oke_contract_deliverable_id
1482 )
1483 VALUES
1484 (
1485 l_dist_rec.distribution_id
1486 ,l_today --last_update_date
1487 ,l_user_id --last_updated_by
1488 ,l_dist_rec.requisition_line_id
1489 ,l_dist_rec.set_of_books_id
1490 ,l_dist_rec.code_combination_id
1491 ,l_dist_rec.req_line_quantity
1492 ,l_login_id --last_update_login
1493 ,l_today --creation_date
1494 ,l_user_id --created_by
1495 ,l_dist_rec.encumbered_flag
1496 ,l_dist_rec.gl_encumbered_date
1497 ,l_dist_rec.gl_encumbered_period_name
1498 ,l_dist_rec.gl_cancelled_date
1499 ,l_dist_rec.failed_funds_lookup_code
1500 ,l_dist_rec.encumbered_amount
1501 ,l_dist_rec.budget_account_id
1502 ,l_dist_rec.accrual_account_id
1503 ,l_dist_rec.variance_account_id
1504 ,l_dist_rec.prevent_encumbrance_flag
1505 ,l_dist_rec.attribute_category
1506 ,l_dist_rec.attribute1
1507 ,l_dist_rec.attribute2
1508 ,l_dist_rec.attribute3
1509 ,l_dist_rec.attribute4
1510 ,l_dist_rec.attribute5
1511 ,l_dist_rec.attribute6
1512 ,l_dist_rec.attribute7
1513 ,l_dist_rec.attribute8
1514 ,l_dist_rec.attribute9
1515 ,l_dist_rec.attribute10
1516 ,l_dist_rec.attribute11
1517 ,l_dist_rec.attribute12
1518 ,l_dist_rec.attribute13
1519 ,l_dist_rec.attribute14
1520 ,l_dist_rec.attribute15
1521 ,l_dist_rec.government_context
1522 ,l_dist_rec.project_id
1523 ,l_dist_rec.task_id
1524 ,l_dist_rec.expenditure_type
1525 ,l_dist_rec.project_accounting_context
1526 ,l_dist_rec.expenditure_organization_id
1527 ,l_dist_rec.gl_closed_date
1528 ,l_dist_rec.source_req_distribution_id
1529 ,l_dist_rec.distribution_num
1530 ,l_dist_rec.project_related_flag
1531 ,l_dist_rec.expenditure_item_date
1532 ,l_dist_rec.org_id
1533 ,l_dist_rec.allocation_type
1534 ,l_dist_rec.allocation_value
1535 ,l_dist_rec.award_id
1536 ,l_dist_rec.end_item_unit_number
1537 ,l_dist_rec.recoverable_tax
1538 ,l_dist_rec.nonrecoverable_tax
1539 ,l_dist_rec.recovery_rate
1540 ,l_dist_rec.tax_recovery_override_flag
1541 ,l_dist_rec.oke_contract_line_id
1542 ,l_dist_rec.oke_contract_deliverable_id
1543 );
1544
1545 END LOOP;
1546
1547 -- Create Supply Record for the Requisition.
1548
1549 l_create_req_supply := PO_SUPPLY.create_req(l_header_rec.requisition_header_id,
1550 'REQ HDR');
1551
1552 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1553 RAISE FND_API.G_EXC_ERROR;
1554 END IF;
1555
1556
1557 fnd_msg_pub.count_and_get
1558 ( p_count => x_msg_count
1559 , p_data => x_msg_data);
1560
1561 px_header_rec := l_header_rec;
1562 px_line_table := l_line_tbl;
1563
1564
1565 EXCEPTION
1566 WHEN INVALID_ITEM THEN
1567 po_message_s.app_error('PO_RI_ITEM_DESC_MISMATCH');
1568 raise;
1569
1570 WHEN UNIT_PRICE_LT_0 THEN
1571 po_message_s.app_error('PO_RI_UNIT_PRICE_LT_0');
1572 raise;
1573
1574 WHEN INVALID_ITEM_CATEGORY THEN
1575 po_message_s.app_error('PO_RI_INVALID_CATEGORY_ID');
1576 raise;
1577
1578 WHEN INVALID_QUANTITY THEN
1579 po_message_s.app_error('PO_RI_QUANTITY_LE_0');
1580 raise;
1581
1582 WHEN INVALID_SOURCE_TYPE THEN
1583 po_message_s.app_error('PO_RI_INVALID_SOURCE_TYPE_CODE');
1584 raise;
1585
1586 WHEN INVALID_CHARGE_ACCOUNT THEN
1587 po_message_s.app_error('PO_RI_INVALID_CHARGE_ACC_ID');
1588 raise;
1589
1590 WHEN INVALID_ACCRUAL_ACCOUNT THEN
1591 po_message_s.app_error('PO_RI_INVALID_ACCRUAL_ACC_ID');
1592 raise;
1593
1594 WHEN INVALID_BUDGET_ACCOUNT THEN
1595 po_message_s.app_error('PO_RI_INVALID_BUDGET_ACC_ID');
1596 raise;
1597
1598 WHEN INVALID_VARIANCE_ACCOUNT THEN
1599 po_message_s.app_error('PO_RI_INVALID_VARIANCE_ACC_ID');
1600 raise;
1601
1602 WHEN INVALID_AUTH_STATUS THEN
1603 po_message_s.app_error('PO_RI_INVALID_AUTH_STATUS');
1604 raise;
1605
1606 WHEN INVALID_PREPARER_ID THEN
1607 po_message_s.app_error('PO_RI_INACTIVE_PREPARER');
1608 raise;
1609
1610 WHEN INVALID_LOCATION_ID THEN
1611 po_message_s.app_error('PO_RI_INVALID_LOCATION');
1612 raise;
1613
1614 WHEN INVALID_DESTINATION_ORG THEN
1615 po_message_s.app_error('PO_RI_INVALID_DEST_ORG');
1616 raise;
1617
1618 WHEN INVALID_UNIT_OF_MEASURE THEN
1619 po_message_s.app_error('PO_RI_M_INVALID_UOM');
1620 raise;
1621
1622 WHEN FND_API.G_EXC_ERROR THEN
1623 NULL;
1624
1625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1626 FND_MESSAGE.SET_NAME('PO', 'PO_UNEXPECTED_EXEC_ERRORS');
1627 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
1628 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
1629 FND_MSG_PUB.ADD;
1630 fnd_msg_pub.count_and_get
1631 ( p_count => x_msg_count
1632 , p_data => x_msg_data);
1633 x_return_status := FND_API.G_RET_STS_ERROR;
1634
1635 WHEN OTHERS THEN
1636 FND_MESSAGE.SET_NAME('PO', 'PO_UNEXPECTED_EXEC_ERRORS');
1637 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
1638 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
1639 FND_MSG_PUB.ADD;
1640 fnd_msg_pub.count_and_get
1641 ( p_count => x_msg_count
1642 , p_data => x_msg_data);
1643 x_return_status := FND_API.G_RET_STS_ERROR;
1644
1645 END;
1646
1647 END PO_CREATE_REQUISITION_SV;
[Home] [Help]