1 PACKAGE BODY PO_SHARED_PROC_PVT AS
2 /* $Header: POXVSPSB.pls 120.0 2005/06/01 16:45:49 appldev noship $ */
3
4 -- Debugging booleans used to bypass logging when turned off
5 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
6 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
7
8 g_pkg_name CONSTANT VARCHAR2(20) := 'PO_SHARED_PROC_PVT';
9 g_module_prefix CONSTANT VARCHAR2(30) := 'po.plsql.' || g_pkg_name || '.';
10
11 -- Transaction flows are supported if INV FPJ or higher is installed
12 g_is_txn_flow_supported CONSTANT BOOLEAN :=
13 (INV_CONTROL.get_current_release_level >= INV_RELEASE.get_j_release_level);
14
15
16 --------------------------------------------------------------------------------
17 --Start of Comments
18 --Name: check_transaction_flow
19 --Pre-reqs:
20 -- None.
21 --Modifies:
22 -- FND_LOG
23 -- FND_MSG_PUB on error.
24 --Locks:
25 -- None.
26 --Function:
27 -- Check if an inventory transaction flow exists for input parameters given.
28 -- Appends to the API message list upon error.
29 --Parameters:
30 --IN:
31 --p_init_msg_list
32 --p_start_ou_id
33 -- Start OU of the transaction flow.
34 --p_end_ou_id
35 -- End OU of the transaction flow. Defaults to OU of p_ship_to_org_id if this
36 -- is NULL.
37 --p_ship_to_org_id
38 -- The ship-to organization of the transaction flow.
39 --p_item_category_id
40 -- Item category ID of the transaction flow, if one exists.
41 --p_transaction_date
42 --OUT:
43 --x_return_status
44 -- FND_API.g_ret_sts_success - if the procedure completed successfully
45 -- FND_API.g_ret_sts_error - if an error occurred
46 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
47 --x_transaction_flow_header_id
48 -- The unique header ID of the transaction flow, if any valid inter-company
49 -- relationship exists. If not flow was found, then this is NULL.
50 -- (MTL_TRANSACTION_FLOW_HEADERS.header_id%TYPE)
51 --End of Comments
52 --------------------------------------------------------------------------------
53 PROCEDURE check_transaction_flow
54 (
55 p_init_msg_list IN VARCHAR2,
56 x_return_status OUT NOCOPY VARCHAR2,
57 p_start_ou_id IN NUMBER,
58 p_end_ou_id IN NUMBER,
59 p_ship_to_org_id IN NUMBER,
60 p_item_category_id IN NUMBER,
61 p_transaction_date IN DATE,
62 x_transaction_flow_header_id OUT NOCOPY NUMBER
63 )
64 IS
65
66 l_end_ou_id MTL_TRANSACTION_FLOW_HEADERS.end_org_id%TYPE := p_end_ou_id;
67
68 l_qual_code_tbl INV_TRANSACTION_FLOW_PUB.number_tbl;
69 l_qual_val_tbl INV_TRANSACTION_FLOW_PUB.number_tbl;
70
71 l_new_accounting_flag MTL_TRANSACTION_FLOW_HEADERS.new_accounting_flag%TYPE;
72 l_txn_flow_exists VARCHAR2(1);
73
74 l_progress VARCHAR2(3);
75 l_return_status VARCHAR2(1);
76 l_msg_count NUMBER;
77 l_msg_data VARCHAR2(2000);
78
79 BEGIN
80 l_progress := '000';
81
82 -- Start standard API initialization
83 IF FND_API.to_boolean(p_init_msg_list) THEN
84 FND_MSG_PUB.initialize;
85 END IF;
86 x_return_status := FND_API.g_ret_sts_success;
87 -- End standard API initialization
88
89 l_progress := '010';
90
91 IF g_debug_stmt THEN
92 PO_DEBUG.debug_stmt
93 (p_log_head => g_module_prefix||'check_transaction_flow',
94 p_token => 'invoked',
95 p_message => 'startou ID: '||p_start_ou_id||' endou ID: '||
96 p_end_ou_id||' shipto org: '||p_ship_to_org_id||
97 ' item cat ID: '||p_item_category_id||' txn date: '||
98 TO_CHAR(p_transaction_date,'DD-MON-RRRR HH24:MI:SS'));
99 END IF;
100
101 -- Make sure that transaction flows are supported
102 IF (NOT g_is_txn_flow_supported) THEN
103 -- Transaction flows not supported, so return immediately
104 x_transaction_flow_header_id := NULL;
105 IF g_debug_stmt THEN
106 PO_DEBUG.debug_stmt
107 (p_log_head => g_module_prefix||'check_transaction_flow',
108 p_token => l_progress,
109 p_message => 'Transaction flows not supported');
110 END IF;
111 RETURN;
112 END IF;
113
114 -- Default the End operating unit if it is NULL
115 IF (l_end_ou_id IS NULL) THEN
116 l_progress := '020';
117 PO_CORE_S.get_inv_org_ou_id(x_return_status => x_return_status,
118 p_inv_org_id => p_ship_to_org_id,
119 x_ou_id => l_end_ou_id);
120
121 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
122 RAISE FND_API.g_exc_unexpected_error;
123 END IF;
124 END IF;
125
126 l_progress := '030';
127
128 -- Never use a transaction flow if the start and end OU's are equal
129 IF (p_start_ou_id = l_end_ou_id) THEN
130 x_transaction_flow_header_id := NULL;
131 IF g_debug_stmt THEN
132 PO_DEBUG.debug_stmt
133 (p_log_head => g_module_prefix||'check_transaction_flow',
134 p_token => l_progress,
135 p_message => 'Start OU and End OU same, so just return');
136 END IF;
137 RETURN;
138 END IF;
139
140 -- Initialize tables if have item_category_id
141 IF (p_item_category_id IS NOT NULL) THEN
142 l_qual_code_tbl(1) := get_inv_qualifier_code;
143 l_qual_val_tbl(1) := p_item_category_id;
144 END IF;
145
146 l_progress := '040';
147
148 -- Try to get a valid transaction flow
149 INV_TRANSACTION_FLOW_PUB.check_transaction_flow
150 (p_api_version => 1.0,
151 x_return_status => x_return_status,
152 x_msg_count => l_msg_count,
153 x_msg_data => l_msg_data,
154 p_start_operating_unit => p_start_ou_id,
155 p_end_operating_unit => l_end_ou_id,
156 p_flow_type => INV_TRANSACTION_FLOW_PUB.g_procuring_flow_type,
157 p_organization_id => p_ship_to_org_id,
158 p_qualifier_code_tbl => l_qual_code_tbl,
159 p_qualifier_value_tbl => l_qual_val_tbl,
160 p_transaction_date => p_transaction_date,
161 x_header_id => x_transaction_flow_header_id,
162 x_new_accounting_flag => l_new_accounting_flag,
163 x_transaction_flow_exists => l_txn_flow_exists);
164
165 IF (x_return_status = FND_API.g_ret_sts_error) THEN
166 l_progress := '050';
167 RAISE FND_API.g_exc_error;
168 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
169 l_progress := '060';
170 RAISE FND_API.g_exc_unexpected_error;
171 END IF;
172
173 l_progress := '070';
174
175 -- Null out the header ID if the txn flow does not exist
176 IF (l_txn_flow_exists IS NULL) OR
177 (l_txn_flow_exists <> INV_TRANSACTION_FLOW_PUB.g_transaction_flow_found)
178 THEN
179 x_transaction_flow_header_id := NULL;
180 END IF;
181
182 IF g_debug_stmt THEN
183 PO_DEBUG.debug_stmt
184 (p_log_head => g_module_prefix||'check_transaction_flow',
185 p_token => l_progress,
186 p_message => 'transaction flow = '||x_transaction_flow_header_id);
187 END IF;
188
189 EXCEPTION
190 WHEN FND_API.g_exc_error THEN
191 x_return_status := FND_API.g_ret_sts_error;
192 x_transaction_flow_header_id := NULL;
193 IF g_debug_stmt THEN
194 PO_DEBUG.debug_stmt
195 (p_log_head => g_module_prefix||'check_transaction_flow',
196 p_token => l_progress,
197 p_message => 'Expected error occurred.');
198 END IF;
199 WHEN FND_API.g_exc_unexpected_error THEN
200 x_return_status := FND_API.g_ret_sts_unexp_error;
201 x_transaction_flow_header_id := NULL;
202 IF g_debug_unexp THEN
203 PO_DEBUG.debug_exc
204 (p_log_head => g_module_prefix||'check_transaction_flow',
205 p_progress => l_progress);
206 END IF;
207 WHEN OTHERS THEN
208 x_return_status := FND_API.g_ret_sts_unexp_error;
209 x_transaction_flow_header_id := NULL;
210 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
211 p_procedure_name => 'check_transaction_flow',
212 p_error_text => 'Progress: '||l_progress||
213 ' Error: '||SUBSTRB(SQLERRM,1,215));
214 IF g_debug_unexp THEN
215 PO_DEBUG.debug_exc
216 (p_log_head => g_module_prefix||'check_transaction_flow',
217 p_progress => l_progress);
218 END IF;
219 END check_transaction_flow;
220
221 ---------------------------------------------------------------------------
222 --Start of Comments
223 --Name: get_coa_from_inv_org
224 --Pre-reqs:
225 -- None.
226 --Modifies:
227 -- None
228 --Locks:
229 -- None.
230 --Function:
231 -- To get the Chart of Account (COA) tied to a Set of Books that, in turn, is
232 -- tied to a Operating Unit to which a given Inventory Org belongs.
233 --Parameters:
234 --IN:
235 --p_inv_org_id
236 -- The ID of an Inventory Organization
237 --Returns:
238 -- NUMBER: The COA of SOB tied to the OU to which a given Inv Org belongs.
239 -- -1, if there is a NO_DATA_FOUND exception in the query.
240 --Testing:
241 --End of Comments
242 ---------------------------------------------------------------------------
243 FUNCTION get_coa_from_inv_org
244 (
245 p_inv_org_id IN NUMBER
246 )
247 RETURN NUMBER
248 IS
249 l_coa_id GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
250 BEGIN
251 --SQL WHAT: Derive the COA tied to a Set of Books that, in turn, is
252 -- tied to a Operating Unit to which a given Inventory Org belongs.
253 --SQL WHY: To define the Destination Account flexfield structure.
254 SELECT gsb.chart_of_accounts_id
255 INTO l_coa_id
256 FROM gl_sets_of_books gsb,
257 hr_organization_information hoi,
258 mtl_parameters mp
259 WHERE mp.organization_id = p_inv_org_id
260 AND mp.organization_id = hoi.organization_id
261 AND hoi.org_information_context = 'Accounting Information'
262 AND TO_NUMBER(hoi.org_information1) = gsb.set_of_books_id;
263
264 return(l_coa_id);
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 return -1; -- may be raised as an exception in the calling program
268 WHEN OTHERS THEN
269 IF ( g_debug_unexp )
270 THEN
271 PO_DEBUG.debug_exc ( p_log_head => g_module_prefix || 'get_coa_from_inv_org'
272 , p_progress => '000' );
273 END IF;
274 RAISE;
275 END get_coa_from_inv_org;
276
277
278 ---------------------------------------------------------------------------
279 --Start of Comments
280 --Name: get_ou_and_coa_from_inv_org
281 --Pre-reqs:
282 -- None.
283 --Modifies:
284 -- None
285 --Locks:
286 -- None.
287 --Function:
288 -- To get the Chart of Account (COA) tied to a Set of Books that, in turn, is
289 -- tied to a Operating Unit to which a given Inventory Org belongs.
290 -- Also, get the OU's org ID to which the Inventory Org belongs.
291 --Parameters:
292 --IN:
293 --p_inv_org_id
294 -- The ID of an Inventory Organization
295 --OUT:
296 --x_ou_id OUT NOCOPY NUMBER
297 -- The OU's org ID to which the Inventory Org belongs.
298 --x_coa_id OUT NOCOPY NUMBER,
299 -- The Chart of Account (COA) tied to a Set of Books that, in turn, is
300 -- tied to a Operating Unit to which a given Inventory Org belongs.
301 --x_return_status OUT NOCOPY VARCHAR2
302 -- FND_API.g_ret_sts_success: if the query is executed successfully.
303 -- FND_API.g_ret_sts_error: if NO_DATA_FOUND exception occurs.
304 -- FND_API.g_ret_sts_unexp_error: if any other exception occurs.
305 --Testing:
306 --End of Comments
307 ---------------------------------------------------------------------------
308 PROCEDURE get_ou_and_coa_from_inv_org
309 (
310 p_inv_org_id IN NUMBER,
311 x_coa_id OUT NOCOPY NUMBER,
312 x_ou_id OUT NOCOPY NUMBER,
313 x_return_status OUT NOCOPY VARCHAR2
314 )
315 IS
316 BEGIN
317 --SQL WHAT: Derive the COA tied to a Set of Books that, in turn, is
318 -- tied to a Operating Unit to which a given Inventory Org belongs.
319 -- Also, get the OU's org ID to which the Inventory Org belongs.
320 --SQL WHY: To define the Destination Account flexfield structure.
321 SELECT TO_NUMBER(hoi.org_information3), gsb.chart_of_accounts_id
322 INTO x_ou_id, x_coa_id
323 FROM gl_sets_of_books gsb,
324 hr_organization_information hoi,
325 mtl_parameters mp
326 WHERE mp.organization_id = p_inv_org_id
327 AND mp.organization_id = hoi.organization_id
328 AND hoi.org_information_context = 'Accounting Information'
329 AND TO_NUMBER(hoi.org_information1) = gsb.set_of_books_id;
330
331 x_return_status := FND_API.g_ret_sts_success;
332 EXCEPTION
333 WHEN NO_DATA_FOUND THEN
334 -- may be raised as an exception in the calling program
335 x_return_status := FND_API.g_ret_sts_error;
336 WHEN OTHERS THEN
337 IF ( g_debug_stmt )
338 THEN
339 PO_DEBUG.debug_exc ( p_log_head => g_module_prefix || 'get_ou_and_coa_from_inv_org'
340 , p_progress => '000' );
341 END IF;
342 x_return_status := FND_API.g_ret_sts_unexp_error;
343 END get_ou_and_coa_from_inv_org;
344
345 ---------------------------------------------------------------------------
346 --Start of Comments
347 --Name: is_SPS_distribution
348 --Pre-reqs:
349 -- None.
350 --Modifies:
351 -- None
352 --Locks:
353 -- None.
354 --Function:
355 -- Determines if it is a Shared Procurement Services (SPS) distribution.
356 --Parameters:
357 --IN:
358 -- p_document_type_code
359 -- : The PO's document type code.
360 -- p_ship_to_ou_id NUMBER
361 -- : Destination(Ship-to) OU's org ID
362 -- p_purchasing_ou_id NUMBER,
363 -- : Purchasing OU's org ID
364 -- p_transaction_flow_header_id NUMBER,
365 -- : Transaction flow's header ID, if a Txn flow exists between the
366 -- DOU and POU
367 -- p_project_id NUMBER,
368 -- : Project ID specified on the distribution
369 -- p_destination_type_code VARCHAR2
370 -- : Destination Type Code specified on the distribution
371 --OUT:
372 -- None
373 --RETURN:
374 -- BOOLEAN -- TRUE : if it is a SPS distribution
375 -- FALSE: otherwise
376 --Testing:
377 --End of Comments
378 ---------------------------------------------------------------------------
379 FUNCTION is_SPS_distribution
380 (
381 p_destination_type_code IN VARCHAR2,
382 p_document_type_code IN VARCHAR2,
383 p_purchasing_ou_id IN NUMBER,
384 p_project_id IN NUMBER,
385 p_ship_to_ou_id IN NUMBER,
386 p_transaction_flow_header_id IN NUMBER
387 )
388 RETURN BOOLEAN
389 IS
390 l_is_SPS_distribution BOOLEAN := FALSE;
391 BEGIN
392 -- A distribution is a SPS distribution, if it meets the following 4
393 -- conditions:
394 -- 1. The PO is a Standard PO.
395 -- 2. DOU <> POU.
396 -- 3. A transaction flow is defined between DOU and POU.
397 -- 4. For Expense destination types, NO Project is specified on the
398 -- distribution.
399 l_is_SPS_distribution := FALSE;
400 IF ( (p_document_type_code = 'STANDARD') AND
401 (p_ship_to_ou_id <> p_purchasing_ou_id) AND
402 (p_transaction_flow_header_id IS NOT NULL) ) THEN
403 l_is_SPS_distribution := TRUE;
404 END IF;
405
406 -- <bug 3379488>
407 -- Removed the project related checks since we have decided not to allow
408 -- the entry for project information if there is a trx flow defined for expense dest.
409
410 RETURN l_is_SPS_distribution;
411 END is_SPS_distribution;
412
413 -------------------------------------------------------------------------------
414 --Start of Comments
415 --Name: IS_PA_PROJECT_REFERENCED
416 --Pre-reqs:
417 -- Assumes that project_id is necessary and sufficient indicator of project reference.
418 --Modifies:
419 -- None.
420 --Locks:
421 -- None.
422 --Function:
423 -- Checks whether destination type is expense for the line and project_id field
424 -- on the corresponding distribution lines is not NULL
425 --Parameters:
426 --IN:
427 --p_requisition_line_id
428 -- The unique identifier of requisition line to be examined
429 --Returns:
430 -- TRUE if PA_PROJECT is referenced, FALSE otherwise
431 --Notes:
432 -- None
433 --Testing:
434 -- None
435 --End of Comments
436 -------------------------------------------------------------------------------
437 FUNCTION is_pa_project_referenced
438 (
439 p_requisition_line_id IN NUMBER
440 )
441 RETURN BOOLEAN
442 IS
443 l_valid_pa_project_referenced VARCHAR2(1);
444 BEGIN
445 --SQL WHAT: Matches destination_type_code as expense at line level
446 -- Also looks for project information at distribution level
447 --SQL WHY: This check is required as in SPS project we need to
448 -- prevent procurement across OUs in this scenerio
449
450 SELECT 'Y'
451 INTO l_valid_pa_project_referenced
452 FROM po_requisition_lines_all prl
453 WHERE prl.requisition_line_id = p_requisition_line_id
454 AND prl.destination_type_code = 'EXPENSE'
455 AND EXISTS
456 (SELECT 'valid pa information'
457 FROM po_req_distributions_all prd -- <HTMLAC>
458 WHERE prd.requisition_line_id = p_requisition_line_id
459 AND prd.project_id IS NOT NULL);
460
461 IF l_valid_pa_project_referenced = 'Y' THEN
462 RETURN (TRUE);
463 ELSE
464 RETURN (FALSE);
465 END IF;
466
467 EXCEPTION
468 WHEN OTHERS THEN
469 RETURN (FALSE);
470 END is_pa_project_referenced;
471
472 -------------------------------------------------------------------------------
473 --Start of Comments
474 --Name: Validate_cross_ou_purchasing
475 --Pre-reqs:
476 -- None
477 --Modifies:
478 -- None
479 --Locks:
480 -- None.
481 --Function:
482 -- This function validates that its OK to cross operating unit boundaries
483 -- for creation of Purchase Order. Following checks are done
484 -- (checks 1, 2, 4, 5, 6, 7, 8, 9, and 10 are performed only for Standard POs;
485 -- for Global Agreements, these checks will be performed upon creation
486 -- of the Standard PO release).
487 -- 1. Line is not VMI enabled
488 -- 2. Consigned relationship should not exist
489 -- 3. Item is valid in the involved Operating Units
490 -- 4. PA Project reference for destination type of expense should not exist
491 -- 5. Destination Inv Org should not be OPM enabled
492 -- 6. Following scenario should not exist: Destination OU is same as
493 -- Purchasing OU but is different from Req OU
494 -- 7. Transaction flow between Purchasing OU and Destination OU should exist
495 -- 8. For Services Line type, if 'HR: Cross Business Groups' is NO then ROU
496 -- and POU should rollup to the same Business Group
497 -- 9. If the deliver-to-location on req Line is customer location then
498 -- OM family pack J should be installed
499 -- 10.If Encumbrance is enabled for Purchasing/Requesting Org, then
500 -- Purchasing Org should not be different than Requesting Org
501 --Parameters:
502 --IN
503 --p_api_version
504 -- standard parameter which specifies the API version
505 --p_requisition_line_id
506 -- The req line which needs to investigated
507 --p_requesting_org_id
508 -- The OU of requisition raising OU
509 --p_purchasing_org_id
510 -- The OU where PO will be created
511 --p_item_id
512 -- Can be NULL
513 --p_source_doc_id
514 -- If a GA is referenced then this parameter contains
515 -- the GA header_id otherwise its NULL
516 --p_vmi_flag
517 -- 'Y' or 'N' indicating whether line is vmi enabled
518 --p_cons_from_supp_flag
519 -- 'Y' or 'N' corresponding to consigned_from_supp_flag
520 -- This attribute is obtained from ASL attributes.
521 --p_document_type DEFAULT 'STANDARD'
522 -- Document Type of the outcome document.
523 --OUT
524 --x_return_status
525 -- Standard return status parameter. This parameter tells
526 -- whether all validations are passed.
527 --x_errormsg_name
528 -- This parameter contains relevant message when return status is
529 -- not G_RET_STS_SUCCESS
530 --Notes:
531 -- None
532 --Testing:
533 -- None
534 --End of Comments
535 -------------------------------------------------------------------------------
536 PROCEDURE validate_cross_ou_purchasing
537 (
538 p_api_version IN NUMBER,
539 p_requisition_line_id IN NUMBER,
540 p_requesting_org_id IN NUMBER,
541 p_purchasing_org_id IN NUMBER,
542 p_item_id IN NUMBER,
543 p_source_doc_id IN NUMBER,
544 p_vmi_flag IN VARCHAR2,
545 p_cons_from_supp_flag IN VARCHAR2,
546 x_return_status OUT NOCOPY VARCHAR2,
547 x_error_msg_name OUT NOCOPY VARCHAR2,
548 p_document_type IN VARCHAR2 := 'STANDARD' -- <HTMLAC>
549 )
550 IS
551
552 l_pa_project BOOLEAN := FALSE;
553 l_transaction_flow_status VARCHAR2(1);
554 l_dest_inv_org_ou_id PO_SYSTEM_PARAMETERS_ALL.org_id%TYPE;
555 l_transaction_flow_header_id MTL_TRANSACTION_FLOW_HEADERS.header_id%TYPE;
556 l_category_id PO_REQUISITION_LINES_ALL.category_id%TYPE;
557 l_api_name CONSTANT VARCHAR2 (30) := 'validate_cross_ou_purchasing';
558 l_api_version CONSTANT NUMBER := 1.0;
559 l_dest_inv_org_id PO_REQUISITION_LINES_ALL.destination_organization_id%TYPE;
560 l_owning_org_id PO_HEADERS_ALL.org_id%TYPE;
561 l_deliver_to_location_id PO_REQUISITION_LINES_ALL.deliver_to_location_id%TYPE;
562 l_is_customer_location VARCHAR2(1) := 'N';
563 l_progress VARCHAR2(3) := '000';
564 l_item_valid_status VARCHAR2(1);
565 l_item_valid_msg_name FND_NEW_MESSAGES.message_name%TYPE;
566 x_app_name VARCHAR2(10);
567 l_purchase_basis PO_REQUISITION_LINES_ALL.purchase_basis%TYPE;
568 l_pou_bus_group_id HR_ALL_ORGANIZATION_UNITS.business_group_id%TYPE;
569 l_rou_bus_group_id HR_ALL_ORGANIZATION_UNITS.business_group_id%TYPE;
570 l_item_in_linv_pou VARCHAR2(1) := 'Y'; -- Bug 3433867
571
572 BEGIN
573
574 l_progress := '001';
575 -- Standard call to check for call compatibility.
576 IF NOT fnd_api.compatible_api_call (l_api_version,
577 p_api_version,
578 l_api_name,
579 g_pkg_name
580 )
581 THEN
582 RAISE fnd_api.g_exc_unexpected_error;
583 END IF;
584
585 l_progress := '002';
586
587 -- Bug 3379488: Only perform the original checks when ROU and POU are not
588 -- the same; perform a new check for project information of expense
589 -- destination in the ELSE clause (when ROU and POU are the same)
590 IF p_requesting_org_id <> p_purchasing_org_id THEN
591
592 l_progress := '003';
593
594 --CHECK 1: The line should not be VMI enabled
595
596 IF ( p_document_type = 'STANDARD' ) THEN -- <HTMLAC>
597
598 IF p_vmi_flag = 'Y' THEN
599 x_error_msg_name := 'PO_CROSS_OU_VMI_CHECK';
600 x_return_status := FND_API.G_RET_STS_ERROR;
601 Return;
602 END IF;
603
604 END IF;
605
606 l_progress := '004';
607
608 --CHECK 2: If Consigned relationship exists then we error out
609
610 IF ( p_document_type = 'STANDARD' ) THEN -- <HTMLAC>
611
612 IF p_cons_from_supp_flag = 'Y' THEN
613 x_error_msg_name := 'PO_CROSS_OU_CONSIGNED_CHECK';
614 x_return_status := FND_API.G_RET_STS_ERROR;
615 Return;
616 END IF;
617
618 END IF;
619
620 l_progress := '005';
621 --CHECK 3: Item should be valid in following OUs -
622 --For Lines with GA reference: Requesting, Owning and Purchasing
623 --For lines with no source doc info: Requesting and Purchasing
624 IF PO_GA_PVT.is_global_agreement(p_source_doc_id) THEN
625 l_owning_org_id := PO_GA_PVT.get_org_id(p_source_doc_id);
626 do_item_validity_checks(
627 p_item_id => p_item_id,
628 p_org_id => p_purchasing_org_id,
629 p_valid_org_id => l_owning_org_id,
630 p_do_osp_check => TRUE,
631 x_return_status => l_item_valid_status,
632 x_item_valid_msg_name => l_item_valid_msg_name);
633
634 IF l_item_valid_status = FND_API.G_RET_STS_ERROR THEN
635 x_error_msg_name := l_item_valid_msg_name;
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 Return;
638 END IF;
639
640 do_item_validity_checks(
641 p_item_id => p_item_id,
642 p_org_id => p_requesting_org_id,
643 p_valid_org_id => l_owning_org_id,
644 p_do_osp_check => TRUE,
645 x_return_status => l_item_valid_status,
646 x_item_valid_msg_name => l_item_valid_msg_name);
647
648 IF l_item_valid_status = FND_API.G_RET_STS_ERROR THEN
649 x_error_msg_name := l_item_valid_msg_name;
650 x_return_status := FND_API.G_RET_STS_ERROR;
651 Return;
652 END IF;
653
654 ELSE
655 do_item_validity_checks(
656 p_item_id => p_item_id,
657 p_org_id => p_purchasing_org_id,
658 p_valid_org_id => p_requesting_org_id,
659 p_do_osp_check => FALSE,
660 x_return_status => l_item_valid_status,
661 x_item_valid_msg_name => l_item_valid_msg_name);
662
663 IF l_item_valid_status = FND_API.G_RET_STS_ERROR THEN
664 x_error_msg_name := l_item_valid_msg_name;
665 x_return_status := FND_API.G_RET_STS_ERROR;
666 Return;
667 END IF;
668
669 END IF;
670
671 l_progress := '006';
672
673 --CHECK 4:Procurement across OUs is not supported for PA Projects
674 --Check whether destination type is expense for the line and project_id
675 --field on the corresponding distribution line is not NULL
676
677 IF ( p_document_type = 'STANDARD' ) THEN -- <HTMLAC>
678
679 l_pa_project := is_pa_project_referenced(p_requisition_line_id);
680
681 IF l_pa_project THEN
682 x_error_msg_name := 'PO_CROSS_OU_PA_PROJECT_CHECK';
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 Return;
685 END IF;
686
687 END IF;
688
689 l_progress := '007';
690 --SQL WHAT: Get the necessary information from the given requisition line
691 --SQL WHY: This information is needed for validation checks later
692 SELECT prl.category_id,
693 nvl(prl.destination_organization_id, fsp.inventory_organization_id),
694 prl.deliver_to_location_id
695 INTO l_category_id, l_dest_inv_org_id, l_deliver_to_location_id
696 FROM po_requisition_lines_all prl, financials_system_params_all fsp
697 WHERE prl.requisition_line_id = p_requisition_line_id
698 AND prl.org_id = fsp.org_id; -- Bug 3379488: Use base table for joins
699
700 -- MC INVCONV START
701 /**
702 l_progress := '008';
703
704 --CHECK 5:Procurment across OUs is not supported for OPM enabled
705 --destination inventory orgs
706
707 IF ( p_document_type = 'STANDARD' ) THEN -- <HTMLAC>
708
709 IF (PO_GML_DB_COMMON.check_process_org(l_dest_inv_org_id) = 'Y') THEN
710
711 x_error_msg_name := 'PO_CROSS_OU_OPM_INV_CHECK';
712 x_return_status := FND_API.G_RET_STS_ERROR;
713 Return;
714
715 END IF;
716
717 END IF;
718 **/
719 -- MC INVCONV END
720
721 l_progress := '009';
722 --Get the operating unit of destination inv org
723 IF (l_dest_inv_org_id IS NOT NULL) THEN
724 PO_CORE_S.get_inv_org_ou_id(x_return_status => x_return_status,
725 p_inv_org_id => l_dest_inv_org_id,
726 x_ou_id => l_dest_inv_org_ou_id);
727
728 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
729 RAISE FND_API.g_exc_unexpected_error;
730 END IF;
731 END IF;
732
733 l_progress := '010';
734
735 --CHECK 6:If (DOU=POU)<>ROU then we are preventing the PO creation.
736 --This is scoped out due to accounting complexities
737
738 IF ( p_document_type = 'STANDARD' ) THEN -- <HTMLAC>
739
740 IF (l_dest_inv_org_ou_id = p_purchasing_org_id AND
741
742 l_dest_inv_org_ou_id <> p_requesting_org_id) THEN
743 x_error_msg_name := 'PO_CROSS_OU_DEST_OU_CHECK';
744 x_return_status := FND_API.G_RET_STS_ERROR;
745 Return;
746
747 END IF;
748
749 END IF;
750
751 l_progress := '011';
752
753 --CHECK 7:Procurement across OUs is allowed only when a valid
754 --transaction flow exists between Purchasing OU and Destination OU.
755 --If the category_id is NOT NULL then we use it to find flow. The
756 --API forst check if a flow exists with given category otherwise it
757 --tries to find a non category specific transaction flow.
758
759 IF ( p_document_type = 'STANDARD' ) THEN -- <HTMLAC>
760
761 Check_transaction_flow(
762 p_init_msg_list => FND_API.G_FALSE,
763 x_return_status => l_transaction_flow_status,
764 p_start_ou_id => p_purchasing_org_id,
765 p_end_ou_id => l_dest_inv_org_ou_id,
766 p_ship_to_org_id => l_dest_inv_org_id,
767 p_item_category_id => l_category_id,
768 p_transaction_date => SYSDATE,
769 x_transaction_flow_header_id => l_transaction_flow_header_id
770 );
771
772 l_progress := '012';
773
774 --If transaction flow does not exist then return error
775
776 IF ((l_transaction_flow_status <> FND_API.G_RET_STS_SUCCESS) OR
777 l_transaction_flow_header_id is NULL) THEN
778 x_error_msg_name := 'PO_CROSS_OU_TRNX_FLOW_CHECK';
779 x_return_status := FND_API.G_RET_STS_ERROR;
780 Return;
781 END IF;
782
783 -- Bug 3433867 Start
784 -- CHECK 7.5: Need to validate the item in the logical inv org of
785 -- the POU if valid transaction flow exists and item id is not null
786
787 IF ( ( l_transaction_flow_header_id IS NOT NULL )
788 AND ( p_item_id IS NOT NULL ) )
789 THEN
790 check_item_in_linv_pou
791 (x_return_status => l_transaction_flow_status,
792 p_item_id => p_item_id,
793 p_transaction_flow_header_id => l_transaction_flow_header_id,
794 x_item_in_linv_pou => l_item_in_linv_pou);
795
796 IF (l_transaction_flow_status <> FND_API.g_ret_sts_success) OR
797 (l_item_in_linv_pou <> 'Y')
798 THEN
799 x_error_msg_name := 'PO_ITEM_IN_LINV_POU_CHECK';
800 x_return_status := FND_API.G_RET_STS_ERROR;
801 return;
802 END IF;
803
804 END IF;
805 -- Bug 3433867 End
806
807 END IF; -- ( p_document_type = 'STANDARD' )
808
809 l_progress := '013';
810
811 --CHECK 8:For Services Line type, if 'HR: Cross Business Groups' is NO
812 --then ROU and POU should rollup to the same Business Group
813 --Identify if the line type is SERVICE type
814
815 IF ( p_document_type = 'STANDARD' ) THEN -- <HTMLAC>
816
817 IF nvl(hr_general.get_xbg_profile, 'N') = 'N' THEN
818
819 SELECT prl.purchase_basis,
820 hrou_po.business_group_id,
821 hrou_req.business_group_id
822 INTO l_purchase_basis,
823 l_pou_bus_group_id,
824 l_rou_bus_group_id
825 FROM po_requisition_lines_all prl,
826 hr_all_organization_units hrou_po,
827 hr_all_organization_units hrou_req
828 WHERE prl.requisition_line_id = p_requisition_line_id
829 AND hrou_po.organization_id = p_purchasing_org_id
830 AND hrou_req.organization_id = p_requesting_org_id;
831
832 IF (l_purchase_basis = 'TEMP LABOR') AND
833 (l_pou_bus_group_id <> l_rou_bus_group_id)
834 THEN
835 x_error_msg_name := 'PO_CROSS_OU_SERVICES_CHECK';
836 x_return_status := FND_API.G_RET_STS_ERROR;
837 Return;
838 END IF;
839 END IF; --cross business group profile check
840
841 END IF;
842
843 l_progress := '014';
844 --CHECK 9:If the deliver-to-location on req Line is customer location then
845 --OM family pack J should be installed to handle cross OU purchasing in
846 --international drop ship scenerio.
847 BEGIN
848 SELECT 'Y'
849 INTO l_is_customer_location
850 FROM HZ_LOCATIONS
851 WHERE nvl(address_expiration_date, trunc(sysdate + 1)) > trunc(sysdate)
852 AND location_id = l_deliver_to_location_id;
853 EXCEPTION
854 WHEN NO_DATA_FOUND THEN
855 l_is_customer_location := 'N';
856 WHEN OTHERS THEN
857 RAISE;
858 END;
859
860 IF (l_is_customer_location = 'Y' AND
861 (NOT OE_CODE_CONTROL.code_release_level >= '110510')) THEN
862 x_error_msg_name := 'PO_CROSS_OU_CUST_LOC_CHECK';
863 x_return_status := FND_API.G_RET_STS_ERROR;
864 Return;
865 END IF;
866
867 --<Bug 3313252 mbhargav START>
868 l_progress := '015';
869
870 --CHECK 10: If Encumbrance is enabled for Purchasing/Requesting Org and
871 --purchasing org is different than requesting org then we error out
872
873 IF ( p_document_type = 'STANDARD' ) THEN -- <HTMLAC>
874
875 IF p_requesting_org_id <> p_purchasing_org_id AND
876 (PO_CORE_S.is_encumbrance_on(p_doc_type => 'ANY',p_org_id => p_requesting_org_id)
877 OR
878 PO_CORE_S.is_encumbrance_on(p_doc_type => 'ANY',p_org_id => p_purchasing_org_id)
879 )
880 THEN
881 x_error_msg_name := 'PO_GA_ENCUMBRANCE_CHECK';
882 x_return_status := FND_API.G_RET_STS_ERROR;
883 Return;
884 END IF;
885
886 END IF;
887 --<Bug 3313252 mbhargav END>
888
889 ELSE -- p_requesting_org_id = p_purchasing_org_id
890
891 l_progress := '016';
892 l_pa_project := is_pa_project_referenced(p_requisition_line_id);
893
894 IF l_pa_project THEN
895
896 l_progress := '017';
897 --SQL WHAT: Get the necessary information from the given requisition line
898 --SQL WHY: This information is needed for validation checks later
899 SELECT prl.category_id,
900 nvl(prl.destination_organization_id,
901 fsp.inventory_organization_id),
902 prl.deliver_to_location_id
903 INTO l_category_id, l_dest_inv_org_id, l_deliver_to_location_id
904 FROM po_requisition_lines_all prl, financials_system_params_all fsp
905 WHERE prl.requisition_line_id = p_requisition_line_id
906 AND prl.org_id = fsp.org_id;
907
908 l_progress := '018';
909
910 --Get the operating unit of destination inv org
911 IF (l_dest_inv_org_id IS NOT NULL) THEN
912 PO_CORE_S.get_inv_org_ou_id(x_return_status => x_return_status,
913 p_inv_org_id => l_dest_inv_org_id,
914 x_ou_id => l_dest_inv_org_ou_id);
915
916 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
917 RAISE FND_API.g_exc_unexpected_error;
918 END IF;
919 END IF;
920
921 l_progress := '019';
922 check_transaction_flow(
923 p_init_msg_list => FND_API.G_FALSE,
924 x_return_status => l_transaction_flow_status,
925 p_start_ou_id => p_purchasing_org_id,
926 p_end_ou_id => l_dest_inv_org_ou_id,
927 p_ship_to_org_id => l_dest_inv_org_id,
928 p_item_category_id => l_category_id,
929 p_transaction_date => SYSDATE,
930 x_transaction_flow_header_id => l_transaction_flow_header_id
931 );
932
933 l_progress := '020';
934 IF (l_transaction_flow_status <> FND_API.g_ret_sts_success) THEN
935 RAISE FND_API.g_exc_unexpected_error;
936 END IF;
937
938 l_progress := '021';
939 --If transaction flow exists and project is attached, return error
940 IF l_transaction_flow_header_id is NOT NULL THEN
941 x_error_msg_name := 'PO_CROSS_OU_PA_PROJECT_CHECK';
942 x_return_status := FND_API.G_RET_STS_ERROR;
943 return;
944 END IF;
945
946 END IF; -- l_pa_project is TRUE
947
948 END IF; -- p_requesting_org_id != p_purchasing_org_id
949 -- Bug 3379488 End
950
951 --All checks passed. Return success
952 x_return_status := FND_API.G_RET_STS_SUCCESS;
953
954 EXCEPTION
955 WHEN fnd_api.g_exc_error THEN
956 x_return_status := fnd_api.g_ret_sts_error;
957 fnd_message.parse_encoded(FND_MSG_PUB.get,x_app_name,x_error_msg_name);
958 WHEN fnd_api.g_exc_unexpected_error THEN
959 x_return_status := fnd_api.g_ret_sts_unexp_error;
960 fnd_message.parse_encoded(FND_MSG_PUB.get,x_app_name,x_error_msg_name);
961 WHEN OTHERS THEN
962 x_return_status := fnd_api.g_ret_sts_unexp_error;
963 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name,
964 SUBSTRB(SQLERRM , 1 , 200) || ' at location ' || l_progress);
965 fnd_message.parse_encoded(FND_MSG_PUB.get,x_app_name,x_error_msg_name);
966 END validate_cross_ou_purchasing;
967
968
969 -----------------------------------------------------------------------<HTMLAC>
970 -------------------------------------------------------------------------------
971 --Start of Comments
972 --Name: validate_cross_ou_tbl
973 --Pre-reqs:
974 -- None
975 --Modifies:
976 -- None
977 --Locks:
978 -- None.
979 --Function:
980 -- Wrapper procedure allowing nested table inputs and outputs for
981 -- bulk processing on procedure validate_cross_ou_purchasing.
982 --Parameters:
983 --IN
984 --p_req_line_id_tbl
985 -- Nested table of Req Line IDs which need to be validated
986 --p_requesting_org_id_tbl
987 -- Nested table containing Req Lines' OUs
988 --p_purchasing_org_id
989 -- OU where PO will be created
990 --p_document_type
991 -- Document Type of the outcome document.
992 --p_item_id_tbl
993 -- Nested table containing Item Id of Req Line
994 --p_source_doc_id_tbl
995 -- Nested table containing header ID of GA (if one is referenced);
996 -- otherwise contains NULL
997 --p_vmi_flag_tbl
998 -- Nested table of 'Y' or 'N' indicating whether line is vmi enabled
999 --p_consigned_flag_tbl
1000 -- Nested table of 'Y' or 'N' corresponding to consigned_from_supp_flag
1001 -- This attribute is obtained from ASL attributes.
1002 --OUT
1003 --x_valid_flag_tbl
1004 -- Nested table of 'Y' or 'N' indicating whether all validations passed
1005 --x_error_msg_tbl
1006 -- Nested table of error messages for each line when that line failed
1007 -- validation (this is not G_RET_STS_SUCCESS)
1008 --Notes:
1009 -- None
1010 --Testing:
1011 -- None
1012 --End of Comments
1013 -------------------------------------------------------------------------------
1014 -------------------------------------------------------------------------------
1015 PROCEDURE validate_cross_ou_tbl
1016 (
1017 p_req_line_id_tbl IN PO_TBL_NUMBER
1018 , p_requesting_org_id_tbl IN PO_TBL_NUMBER
1019 , p_purchasing_org_id IN NUMBER
1020 , p_document_type IN VARCHAR2
1021 , p_item_id_tbl IN PO_TBL_NUMBER
1022 , p_source_doc_id_tbl IN PO_TBL_NUMBER
1023 , p_vmi_flag_tbl IN PO_TBL_VARCHAR1
1024 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
1025 , x_valid_flag_tbl OUT NOCOPY PO_TBL_VARCHAR1
1026 , x_error_msg_tbl OUT NOCOPY PO_TBL_VARCHAR30
1027 )
1028 IS
1029 l_return_status VARCHAR2(1);
1030 l_error_msg_name VARCHAR2(30);
1031 l_valid_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
1032 l_error_msg_tbl PO_TBL_VARCHAR30 := PO_TBL_VARCHAR30();
1033
1034 BEGIN
1035 l_valid_flag_tbl.extend(p_req_line_id_tbl.COUNT);
1036 l_error_msg_tbl.extend(p_req_line_id_tbl.COUNT);
1037
1038 -- Loop through all Req Line IDs and run validation on each line.
1039
1040 FOR i IN p_req_line_id_tbl.FIRST..p_req_line_id_tbl.LAST
1041 LOOP
1042
1043 validate_cross_ou_purchasing
1044 ( p_api_version => 1.0
1045 , p_requisition_line_id => p_req_line_id_tbl(i)
1046 , p_requesting_org_id => p_requesting_org_id_tbl(i)
1047 , p_purchasing_org_id => p_purchasing_org_id
1048 , p_item_id => p_item_id_tbl(i)
1049 , p_source_doc_id => p_source_doc_id_tbl(i)
1050 , p_vmi_flag => p_vmi_flag_tbl(i)
1051 , p_cons_from_supp_flag => p_consigned_flag_tbl(i)
1052 , x_return_status => l_return_status
1053 , x_error_msg_name => l_error_msg_name
1054 , p_document_type => p_document_type
1055 );
1056
1057 -- Set OUT parameters.
1058 --
1059 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS )
1060 THEN
1061 l_valid_flag_tbl(i) := 'Y';
1062 l_error_msg_tbl(i) := NULL;
1063 ELSE
1064 l_valid_flag_tbl(i) := 'N';
1065 l_error_msg_tbl(i) := l_error_msg_name;
1066 END IF;
1067
1068 END LOOP;
1069
1070 x_valid_flag_tbl := l_valid_flag_tbl;
1071 x_error_msg_tbl := l_error_msg_tbl;
1072
1073 END validate_cross_ou_tbl;
1074
1075
1076 --------------------------------------------------------------------------------
1077 --Start of Comments
1078 --Name: check_item_in_inventory_org
1079 --Pre-reqs:
1080 -- None.
1081 --Modifies:
1082 -- FND_LOG
1083 -- FND_MSG_PUB
1084 --Locks:
1085 -- None.
1086 --Function:
1087 -- Checks if p_item_id and p_item_revision are available in p_inv_org_id.
1088 -- Appends to API message list upon error.
1089 --Parameters:
1090 --IN:
1091 --p_init_msg_list
1092 --p_item_id
1093 -- The item ID.
1094 --p_item_revision
1095 -- The item revision of the item ID.
1096 --p_inv_org_id
1097 -- The inventory org ID to be validated against.
1098 --OUT:
1099 --x_return_status
1100 -- FND_API.g_ret_sts_success - if the procedure completed successfully
1101 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
1102 --x_in_inv_org
1103 -- TRUE if:
1104 -- - item is a one-time item
1105 -- OR
1106 -- - item exists in p_inv_org_id
1107 -- - item revision is available in the inventory org
1108 -- OR
1109 -- - item exists in p_inv_org_id
1110 -- - item revision is NULL
1111 -- FALSE otherwise.
1112 --End of Comments
1113 --------------------------------------------------------------------------------
1114 PROCEDURE check_item_in_inventory_org
1115 (
1116 p_init_msg_list IN VARCHAR2,
1117 x_return_status OUT NOCOPY VARCHAR2,
1118 p_item_id IN NUMBER,
1119 p_item_revision IN VARCHAR2,
1120 p_inv_org_id IN NUMBER,
1121 x_in_inv_org OUT NOCOPY BOOLEAN
1122 )
1123 IS
1124
1125 l_valid_flag VARCHAR2(1);
1126 l_progress VARCHAR2(3);
1127
1128 BEGIN
1129 l_progress := '000';
1130
1131 -- Start standard API initialization
1132 IF FND_API.to_boolean(p_init_msg_list) THEN
1133 FND_MSG_PUB.initialize;
1134 END IF;
1135 x_return_status := FND_API.g_ret_sts_success;
1136 -- End standard API initialization
1137
1138 l_progress := '010';
1139
1140 IF g_debug_stmt THEN
1141 PO_DEBUG.debug_stmt
1142 (p_log_head => g_module_prefix||'check_item_in_inventory_org',
1143 p_token => 'invoked',
1144 p_message => 'item ID: '||p_item_id||' item rev: '||
1145 p_item_revision||' invorg ID: '||p_inv_org_id);
1146 END IF;
1147
1148 IF (p_item_id IS NOT NULL) THEN -- If not a one-time item
1149
1150 BEGIN
1151
1152 IF (p_item_revision IS NULL) THEN
1153 l_progress := '020';
1154 SELECT 'Y'
1155 INTO l_valid_flag
1156 FROM mtl_system_items_b msi,
1157 mtl_parameters mp
1158 WHERE msi.inventory_item_id = p_item_id
1159 AND msi.organization_id = p_inv_org_id
1160 AND mp.organization_id = msi.organization_id;
1161 ELSE
1162 l_progress := '030';
1163
1164 --SQL What: Check that item and revision exist in dest org
1165 --SQL Why: To validate the item and revision
1166 SELECT 'Y'
1167 INTO l_valid_flag
1168 FROM mtl_system_items_b msi,
1169 mtl_item_revisions_b mir,
1170 mtl_parameters mp
1171 WHERE msi.inventory_item_id = p_item_id
1172 AND msi.organization_id = p_inv_org_id
1173 AND mir.organization_id = msi.organization_id
1174 AND mir.inventory_item_id = msi.inventory_item_id
1175 AND mir.revision = p_item_revision
1176 AND mp.organization_id = msi.organization_id;
1177 END IF;
1178
1179 -- Successful query means that item exists in inventory org
1180 x_in_inv_org := TRUE;
1181
1182 EXCEPTION
1183 WHEN NO_DATA_FOUND THEN
1184 l_progress := '040';
1185 x_in_inv_org := FALSE;
1186 END;
1187
1188 ELSE
1189 -- one-time items always pass this check
1190 l_progress := '050';
1191 x_in_inv_org := TRUE;
1192
1193 END IF; --< if item_id not null >
1194
1195 IF g_debug_stmt THEN
1196 PO_DEBUG.debug_var
1197 (p_log_head => g_module_prefix||'check_item_in_inventory_org',
1198 p_progress => l_progress,
1199 p_name => 'x_in_inv_org',
1200 p_value => x_in_inv_org);
1201 END IF;
1202
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 x_return_status := FND_API.g_ret_sts_unexp_error;
1206 x_in_inv_org := FALSE;
1207 FND_MSG_PUB.add_exc_msg
1208 (p_pkg_name => g_pkg_name,
1209 p_procedure_name => 'check_item_in_inventory_org',
1210 p_error_text => 'Progress: '||l_progress||' Error: '||
1211 SUBSTRB(SQLERRM,1,215));
1212 IF g_debug_unexp THEN
1213 PO_DEBUG.debug_exc
1214 (p_log_head => g_module_prefix||'check_item_in_inventory_org',
1215 p_progress => l_progress);
1216 END IF;
1217 END check_item_in_inventory_org;
1218
1219 --------------------------------------------------------------------------------
1220 --Start of Comments
1221 --Name: validate_ship_to_org
1222 --Pre-reqs:
1223 -- None.
1224 --Modifies:
1225 -- FND_LOG
1226 -- FND_MSG_PUB
1227 --Locks:
1228 -- None.
1229 --Function:
1230 -- Validates the ship-to org with the current OU. The ship-to org is valid
1231 -- if:
1232 -- - It is within the current set of books
1233 -- OR
1234 -- - It is outside the current set of books
1235 -- - A valid inter-company relationship (i.e. transaction flow) exists
1236 -- between the current OU
1237 -- - The current OU does not use encumbrance
1238 -- - The ship-to org is not an OPM org
1239 --
1240 -- Appends to the API message list upon error.
1241 --Parameters:
1242 --IN:
1243 --p_init_msg_list
1244 -- Standard API parameter to initialize message list.
1245 --p_ship_to_org_id
1246 -- The ship-to org ID
1247 --p_item_category_id
1248 -- The category ID of the line item for this shipment
1249 --OUT:
1250 --x_return_status
1251 -- FND_API.g_ret_sts_success - if the procedure completed successfully
1252 -- FND_API.g_ret_sts_error - if an error occurred
1253 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
1254 --x_is_valid
1255 -- TRUE if validation succeeds. FALSE otherwise.
1256 --x_in_current_sob
1257 -- TRUE if the ship-to org is in the current OU's set of books.
1258 -- FALSE otherwise.
1259 --x_check_txn_flow
1260 -- TRUE if it is allowable to check for transaction flows.
1261 -- FALSE otherwise.
1262 --x_transaction_flow_header_id
1263 -- The unique header ID of the transaction flow, if any valid inter-company
1264 -- relationship exists. If no flow is found, then this is set to NULL.
1265 -- (MTL_TRANSACTION_FLOW_HEADERS.header_id%TYPE)
1266 --End of Comments
1267 --------------------------------------------------------------------------------
1268 PROCEDURE validate_ship_to_org
1269 (
1270 p_init_msg_list IN VARCHAR2,
1271 x_return_status OUT NOCOPY VARCHAR2,
1272 p_ship_to_org_id IN NUMBER,
1273 p_item_category_id IN NUMBER,
1274 p_item_id IN NUMBER, -- Bug 3433867
1275 x_is_valid OUT NOCOPY BOOLEAN,
1276 x_in_current_sob OUT NOCOPY BOOLEAN,
1277 x_check_txn_flow OUT NOCOPY BOOLEAN,
1278 x_transaction_flow_header_id OUT NOCOPY NUMBER
1279 )
1280 IS
1281
1282 l_current_ou_id FINANCIALS_SYSTEM_PARAMS_ALL.org_id%TYPE;
1283 l_current_sob_id FINANCIALS_SYSTEM_PARAMS_ALL.set_of_books_id%TYPE;
1284 l_current_p_enc_flag FINANCIALS_SYSTEM_PARAMS_ALL.purch_encumbrance_flag%TYPE;
1285 l_current_r_enc_flag FINANCIALS_SYSTEM_PARAMS_ALL.req_encumbrance_flag%TYPE;
1286
1287 l_end_ou_id NUMBER; -- query converts to number
1288 l_ship_to_org_sob_id NUMBER; -- query converts to number
1289 l_ship_to_org_opm_flag MTL_PARAMETERS.process_enabled_flag%TYPE;
1290
1291 l_return_status VARCHAR2(1);
1292 l_progress VARCHAR2(3);
1293
1294 l_item_in_linv_pou VARCHAR2(1):= 'Y'; -- Bug 3433867
1295
1296 BEGIN
1297 l_progress := '000';
1298
1299 -- Start standard API initialization
1300 IF FND_API.to_boolean(p_init_msg_list) THEN
1301 FND_MSG_PUB.initialize;
1302 END IF;
1303 x_return_status := FND_API.g_ret_sts_success;
1304 -- End standard API initialization
1305
1306 l_progress := '010';
1307
1308 IF g_debug_stmt THEN
1309 PO_DEBUG.debug_stmt
1310 (p_log_head => g_module_prefix||'validate_ship_to_org',
1311 p_token => 'invoked',
1312 p_message => 'shipto org ID: '||p_ship_to_org_id||
1313 ' item cat ID: '||p_item_category_id);
1314 END IF;
1315
1316 l_progress := '020';
1317
1318 SELECT org_id,
1319 set_of_books_id,
1320 NVL(purch_encumbrance_flag, 'N'),
1321 NVL(req_encumbrance_flag, 'N')
1322 INTO l_current_ou_id,
1323 l_current_sob_id,
1324 l_current_p_enc_flag,
1325 l_current_r_enc_flag
1326 FROM financials_system_parameters;
1327
1328 l_progress := '030';
1329
1330 --SQL What: Get ship-to Org related info
1331 --SQL Why: Need to call txn flow API and perform extra txn flow validation
1332 SELECT TO_NUMBER(hoi.org_information3),
1333 TO_NUMBER(hoi.org_information1)
1334 -- MC INVCONV START
1335 --mp.process_enabled_flag
1336 -- MC INVCONV END
1337 INTO l_end_ou_id,
1338 l_ship_to_org_sob_id
1339 --l_ship_to_org_opm_flag
1340 FROM hr_organization_information hoi
1341 --mtl_parameters mp
1342 WHERE
1343 -- MC INVCONV START
1344 hoi.organization_id = p_ship_to_org_id
1345 --mp.organization_id = p_ship_to_org_id
1346 --AND mp.organization_id = hoi.organization_id
1347 -- MC INVCONV END
1348 AND hoi.org_information_context = 'Accounting Information';
1349
1350 l_progress := '040';
1351
1352 x_in_current_sob := (l_current_sob_id = l_ship_to_org_sob_id);
1353
1354 IF (x_in_current_sob) THEN
1355 -- ship-to org is in the current OU's SOB. Need to check for a valid
1356 -- transaction flow for accounting purposes only if they are supported
1357 x_check_txn_flow := g_is_txn_flow_supported;
1358 x_is_valid := (NOT x_check_txn_flow);
1359
1360 ELSIF (g_is_txn_flow_supported) AND
1361 (l_current_p_enc_flag = 'N') AND
1362 (l_current_r_enc_flag = 'N')
1363 -- MC INVCONV START
1364 -- AND (l_ship_to_org_opm_flag <> 'Y')
1365 -- MC INVCONV END
1366 THEN
1367 -- Ship-to org is outside SOB, but transaction flows are supported,
1368 -- encumbrance is off in current OU, and ship-to org not OPM org.
1369 --
1370 -- In this scenario, ship-to org cannot be valid without a txn flow, so
1371 -- need to check for one
1372 x_check_txn_flow := TRUE;
1373 x_is_valid := FALSE;
1374
1375 ELSE
1376 -- Cannot be a valid ship-to org because it is outside the current SOB
1377 -- and it is not allowable use transaction flows in this scenario
1378 x_check_txn_flow := FALSE;
1379 x_is_valid := FALSE;
1380 END IF;
1381
1382 l_progress := '050';
1383
1384 IF (x_check_txn_flow) THEN
1385
1386 l_progress := '060';
1387
1388 -- Try to get a valid transaction flow
1389 check_transaction_flow
1390 (p_init_msg_list => FND_API.g_false,
1391 x_return_status => l_return_status,
1392 p_start_ou_id => l_current_ou_id,
1393 p_end_ou_id => l_end_ou_id,
1394 p_ship_to_org_id => p_ship_to_org_id,
1395 p_item_category_id => p_item_category_id,
1396 p_transaction_date => SYSDATE,
1397 x_transaction_flow_header_id => x_transaction_flow_header_id);
1398
1399 IF (l_return_status = FND_API.g_ret_sts_error) THEN
1400 l_progress := '070';
1401 RAISE FND_API.g_exc_error;
1402 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
1403 l_progress := '080';
1404 RAISE FND_API.g_exc_unexpected_error;
1405 END IF;
1406
1407 l_progress := '090';
1408
1409 -- Bug 3433867 Start
1410 -- Need to validate the item in the logical inv org of the POU if a
1411 -- valid transaction flow exists and item id is not null
1412 IF x_transaction_flow_header_id IS NOT NULL AND p_item_id IS NOT NULL THEN
1413 check_item_in_linv_pou
1414 (x_return_status => l_return_status,
1415 p_item_id => p_item_id,
1416 p_transaction_flow_header_id => x_transaction_flow_header_id,
1417 x_item_in_linv_pou => l_item_in_linv_pou);
1418 IF l_return_status = FND_API.g_ret_sts_error THEN
1419 l_progress := '091';
1420 RAISE FND_API.g_exc_error;
1421 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1422 l_progress := '092';
1423 RAISE FND_API.g_exc_unexpected_error;
1424 ELSIF l_return_status = FND_API.g_ret_sts_success
1425 AND (l_item_in_linv_pou <> 'Y') THEN
1426 x_is_valid := FALSE;
1427 x_transaction_flow_header_id := NULL;
1428 ELSE
1429 x_is_valid := TRUE;
1430 END IF;
1431 END IF;
1432 -- Bug 3433867 End
1433
1434 IF ((x_transaction_flow_header_id IS NULL) AND (NOT x_in_current_sob))
1435 THEN
1436 -- Transaction does not exist, and ship-to org is outside SOB
1437 x_is_valid := FALSE;
1438
1439 ELSE
1440 x_is_valid := TRUE;
1441 END IF;
1442
1443 END IF; --< if check txn flow >
1444
1445 IF g_debug_stmt THEN
1446 PO_DEBUG.debug_var
1447 (p_log_head => g_module_prefix||'validate_ship_to_org',
1448 p_progress => l_progress,
1449 p_name => 'x_in_current_sob',
1450 p_value => x_in_current_sob);
1451 PO_DEBUG.debug_var
1452 (p_log_head => g_module_prefix||'validate_ship_to_org',
1453 p_progress => l_progress,
1454 p_name => 'x_check_txn_flow',
1455 p_value => x_check_txn_flow);
1456 PO_DEBUG.debug_var
1457 (p_log_head => g_module_prefix||'validate_ship_to_org',
1458 p_progress => l_progress,
1459 p_name => 'x_is_valid',
1460 p_value => x_is_valid);
1461 END IF;
1462
1463 EXCEPTION
1464 WHEN FND_API.g_exc_error THEN
1465 -- There should be an error message appended to the API message list
1466 x_return_status := FND_API.g_ret_sts_error;
1467 x_is_valid := FALSE;
1468 IF g_debug_stmt THEN
1469 PO_DEBUG.debug_stmt
1470 (p_log_head => g_module_prefix||'validate_ship_to_org',
1471 p_token => l_progress,
1472 p_message => 'Expected error occurred.');
1473 END IF;
1474 WHEN FND_API.g_exc_unexpected_error THEN
1475 -- There should be an error message appended to the API message list
1476 x_return_status := FND_API.g_ret_sts_unexp_error;
1477 x_is_valid := FALSE;
1478 IF g_debug_unexp THEN
1479 PO_DEBUG.debug_exc
1480 (p_log_head => g_module_prefix||'validate_ship_to_org',
1481 p_progress => l_progress);
1482 END IF;
1483 WHEN OTHERS THEN
1484 x_return_status := FND_API.g_ret_sts_unexp_error;
1485 x_is_valid := FALSE;
1486 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
1487 p_procedure_name => 'validate_ship_to_org',
1488 p_error_text => 'Progress: '||l_progress||
1489 ' Error: '||SUBSTRB(SQLERRM,1,215));
1490 IF g_debug_unexp THEN
1491 PO_DEBUG.debug_exc
1492 (p_log_head => g_module_prefix||'validate_ship_to_org',
1493 p_progress => l_progress);
1494 END IF;
1495 END validate_ship_to_org;
1496
1497 --------------------------------------------------------------------------------
1498 --Start of Comments
1499 --Name: is_txn_flow_supported
1500 --Pre-reqs:
1501 -- None.
1502 --Modifies:
1503 -- None.
1504 --Locks:
1505 -- None.
1506 --Function:
1507 -- Determines if the current installation supports transaction flows.
1508 --Returns:
1509 -- TRUE if transaction flows are supported in the current installation.
1510 -- FALSE otherwise.
1511 --End of Comments
1512 --------------------------------------------------------------------------------
1513 FUNCTION is_txn_flow_supported RETURN BOOLEAN
1514 IS
1515 BEGIN
1516 RETURN g_is_txn_flow_supported;
1517 END;
1518
1519 --------------------------------------------------------------------------------
1520 --Start of Comments
1521 --Name: get_inv_qualifier_code
1522 --Pre-reqs:
1523 -- None.
1524 --Modifies:
1525 -- None.
1526 --Locks:
1527 -- None.
1528 --Function:
1529 -- Returns the global variable INV_TRANSACTION_FLOW_PUB.g_qualifier_code
1530 --Returns:
1531 -- INV_TRANSACTION_FLOW_PUB.g_qualifier_code.
1532 --End of Comments
1533 --------------------------------------------------------------------------------
1534 FUNCTION get_inv_qualifier_code RETURN NUMBER
1535 IS
1536 BEGIN
1537 RETURN INV_TRANSACTION_FLOW_PUB.g_qualifier_code;
1538 END get_inv_qualifier_code;
1539
1540 --------------------------------------------------------------------------------
1541 --Start of Comments
1542 --Name: do_item_validity_checks
1543 --Pre-reqs:
1544 -- None.
1545 --Modifies:
1546 -- None.
1547 --Locks:
1548 -- None.
1549 --Function:
1550 -- Returns the global variable INV_TRANSACTION_FLOW_PUB.g_qualifier_code
1551 --Parameters:
1552 --IN
1553 --p_item_id Item to check
1554 --p_org_id The org where Item is to be verified
1555 --p_valid_org_id The org where Item is VALID
1556 --p_do_osp_check The parameter which tells whether OSP error needs to be reported
1557 --OUT
1558 --x_return_status Returns error if any of the three checks fail
1559 --x_item_valid_msg_data The error message corresponding to the error. Returns
1560 -- NULL if x_return_status is SUCCESS
1561 --End of Comments
1562 --------------------------------------------------------------------------------
1563 PROCEDURE do_item_validity_checks(
1564 p_item_id IN NUMBER,
1565 p_org_id IN NUMBER,
1566 p_valid_org_id IN NUMBER,
1567 p_do_osp_check IN BOOLEAN,
1568 x_return_status OUT NOCOPY VARCHAR2,
1569 x_item_valid_msg_name OUT NOCOPY VARCHAR2)
1570 IS
1571 l_is_purchasable BOOLEAN;
1572 l_is_same_uom_class BOOLEAN;
1573 l_is_not_osp_item BOOLEAN;
1574
1575 BEGIN
1576 PO_GA_PVT.validate_item(
1577 x_return_status => x_return_status,
1578 p_item_id => p_item_id,
1579 p_org_id => p_org_id,
1580 p_valid_org_id => p_valid_org_id,
1581 x_is_purchasable => l_is_purchasable,
1582 x_is_same_uom_class => l_is_same_uom_class,
1583 x_is_not_osp_item => l_is_not_osp_item);
1584
1585 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
1586 RAISE FND_API.g_exc_unexpected_error;
1587 END IF;
1588
1589 IF NOT l_is_purchasable
1590 THEN
1591 x_item_valid_msg_name := 'PO_CROSS_OU_ITEM_PURCH_CHECK';
1592 RAISE FND_API.g_exc_error;
1593
1594 ELSIF NOT l_is_same_uom_class
1595 THEN
1596 x_item_valid_msg_name := 'PO_CROSS_OU_UOM_CHECK';
1597 RAISE FND_API.g_exc_error;
1598
1599 ELSIF (p_do_osp_check AND NOT l_is_not_osp_item)
1600 THEN
1601 x_item_valid_msg_name := 'PO_CROSS_OU_OSP_CHECK';
1602 RAISE FND_API.g_exc_error;
1603 END IF;
1604
1605 EXCEPTION
1606 WHEN FND_API.g_exc_error THEN
1607 x_return_status := FND_API.g_ret_sts_error;
1608 WHEN FND_API.g_exc_unexpected_error THEN
1609 x_return_status := FND_API.g_ret_sts_unexp_error;
1610 WHEN OTHERS THEN
1611 x_return_status := FND_API.g_ret_sts_unexp_error;
1612 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
1613 p_procedure_name => 'do_item_validity_checks',
1614 p_error_text => NULL);
1615 IF g_debug_unexp THEN
1616 PO_DEBUG.debug_exc
1617 (p_log_head => g_module_prefix||'do_item_validity_checks',
1618 p_progress => '500');
1619 END IF;
1620 END do_item_validity_checks;
1621
1622 ---------------------------------------------------------------------------
1623 --Start of Comments
1624 --Name: get_logical_inv_org_id
1625 --Pre-reqs:
1626 -- None.
1627 --Modifies:
1628 -- None
1629 --Locks:
1630 -- None.
1631 --Function:
1632 -- Get the org id for the Logical Inventory Org associated with
1633 -- the given Transaction Flow. This LINV would belong to the POU.
1634 --Parameters:
1635 --IN:
1636 -- p_transaction_flow_header_id
1637 -- : The Transaction Flow Header ID associated with the Transaction flow
1638 -- between the POU and the DOU
1639 --OUT:
1640 -- None.
1641 --RETURN
1642 -- NUMBER: The Org ID of the Logical Inventory Org.
1643 --Testing:
1644 --End of Comments
1645 ---------------------------------------------------------------------------
1646 FUNCTION get_logical_inv_org_id(p_transaction_flow_header_id IN NUMBER)
1647 RETURN NUMBER
1648 IS
1649 l_logical_inv_org_id NUMBER := NULL;
1650 BEGIN
1651 --SQL WHAT: Get the org id for the Logical Inventory Org associated with
1652 -- the given Transaction Flow. This LINV would belong to the POU.
1653 --SQL WHY: The LINV's org id is used in several queries where the SPS
1654 -- Charge Account is being derived.
1655 --NOTE: This function is called only when the TRANSACTION_FLOW_HEADER_ID
1656 -- is NOT NULL.
1657 SELECT from_organization_id
1658 INTO l_logical_inv_org_id
1659 FROM MTL_TRANSACTION_FLOW_LINES
1660 WHERE header_id = p_transaction_flow_header_id AND
1661 line_number = 1;
1662
1663 RETURN l_logical_inv_org_id;
1664 EXCEPTION
1665 WHEN NO_DATA_FOUND THEN
1666 RETURN NULL; -- May be raised as an exception in the calling procedure.
1667 WHEN OTHERS THEN
1668 RAISE;
1669 END get_logical_inv_org_id;
1670
1671 --------------------------------------------------------------------------------
1672 --Start of Comments
1673 --Name: get_po_setup_parameters
1674 --Pre-reqs:
1675 -- None.
1676 --Modifies:
1677 -- None.
1678 --Locks:
1679 -- None.
1680 --Function:
1681 -- Returns the out parameters from po setup
1682 --Parameters:
1683 --IN
1684 --p_org_id The operating unit
1685 --OUT
1686 --x_po_num_code Containg the value from column user_defined_po_num_code(MANUAL/AUTOMATIC)
1687 --x_po_num_type Containg the value from column manual_po_num_type(NUMERIC/ALPHANUMERIC)
1688 --End of Comments
1689 --------------------------------------------------------------------------------
1690 PROCEDURE get_po_setup_parameters(
1691 p_org_id IN NUMBER,
1692 x_po_num_code OUT NOCOPY VARCHAR2,
1693 x_po_num_type OUT NOCOPY VARCHAR2)
1694 IS
1695 BEGIN
1696 select user_defined_po_num_code, manual_po_num_type
1697 into x_po_num_code, x_po_num_type
1698 from po_system_parameters_all
1699 where org_id = p_org_id;
1700 EXCEPTION
1701 when others then
1702 IF ( g_debug_stmt )
1703 THEN
1704 PO_DEBUG.debug_exc ( p_log_head => g_module_prefix || 'get_po_setup_parameters'
1705 , p_progress => '000' );
1706 END IF;
1707 RAISE;
1708 END;
1709
1710 -- Bug 3433867: added the following procedure to perform extra item validation
1711 -- for Shared Procurement
1712 --------------------------------------------------------------------------------
1713 --Start of Comments
1714 --Name: check_item_in_linv_pou
1715 --Pre-reqs:
1716 -- None
1717 --Modifies:
1718 -- None
1719 --Locks:
1720 -- None
1721 --Function:
1722 -- Checks if an item exists in the logical inventory org of the POU of a given
1723 -- transaction flow
1724 --Parameters:
1725 --IN
1726 --p_transaction_flow_header_id
1727 -- Transaction flow header ID, unqiue identifier of transaction flows
1728 --p_item_id
1729 -- Item ID, unqiue identifier of items
1730 --OUT
1731 --x_return_status:
1732 -- FND_API.g_ret_sts_success: if the procedure completed successfully
1733 -- FND_API.g_ret_sts_unexp_error: if unexpected error occured
1734 --x_item_in_linv_pou
1735 -- If 'Y', the item exists in the logical inventory org of the POU; otherwise,
1736 -- it does not
1737 --End of Comments
1738 --------------------------------------------------------------------------------
1739 PROCEDURE check_item_in_linv_pou
1740 (
1741 x_return_status OUT NOCOPY VARCHAR2,
1742 p_item_id IN NUMBER,
1743 p_transaction_flow_header_id IN NUMBER,
1744 x_item_in_linv_pou OUT NOCOPY VARCHAR2
1745 )
1746 IS
1747 l_progress VARCHAR2(3) := '000';
1748 l_log_head CONSTANT VARCHAR2(100):= g_module_prefix
1749 ||'check_item_in_linv_pou';
1750 l_logical_inv_org_id MTL_TRANSACTION_FLOW_LINES.from_organization_id%TYPE;
1751 l_item_in_inv_org BOOLEAN;
1752
1753 BEGIN
1754 l_progress := '010';
1755
1756 IF g_debug_stmt THEN
1757 PO_DEBUG.debug_stmt(p_log_head => l_log_head,
1758 p_token => l_progress,
1759 p_message => 'Transaction flow header id: '
1760 || p_transaction_flow_header_id
1761 || 'Item id: ' || p_item_id);
1762 END IF;
1763
1764 x_return_status := FND_API.g_ret_sts_success;
1765 x_item_in_linv_pou := 'N';
1766
1767 IF p_transaction_flow_header_id IS NULL THEN
1768 return;
1769 ELSIF p_item_id IS NULL THEN
1770 x_item_in_linv_pou := 'Y';
1771 return;
1772 END IF;
1773
1774 l_progress := '020';
1775 l_logical_inv_org_id := get_logical_inv_org_id
1776 (p_transaction_flow_header_id => p_transaction_flow_header_id);
1777
1778 IF g_debug_stmt THEN
1779 PO_DEBUG.debug_stmt(p_log_head => l_log_head,
1780 p_token => l_progress,
1781 p_message => 'POUs logical inventory org id: '
1782 || l_logical_inv_org_id);
1783 END IF;
1784
1785 l_progress := '030';
1786 IF l_logical_inv_org_id IS NOT NULL THEN
1787 check_item_in_inventory_org(
1788 p_init_msg_list => 'T',
1789 x_return_status => x_return_status,
1790 p_item_id => p_item_id,
1791 p_item_revision => NULL,
1792 p_inv_org_id => l_logical_inv_org_id,
1793 x_in_inv_org => l_item_in_inv_org
1794 );
1795 IF x_return_status <> FND_API.g_ret_sts_success THEN
1796 l_progress := '040';
1797 RAISE FND_API.g_exc_unexpected_error;
1798 END IF;
1799 ELSE
1800 l_progress := '050';
1801 x_return_status := FND_API.g_ret_sts_unexp_error;
1802 RAISE FND_API.g_exc_unexpected_error;
1803 END IF;
1804
1805 l_progress := '060';
1806
1807 IF l_item_in_inv_org THEN
1808 x_item_in_linv_pou := 'Y';
1809 END IF;
1810
1811 EXCEPTION
1812 WHEN OTHERS THEN
1813 x_return_status := FND_API.g_ret_sts_unexp_error;
1814 x_item_in_linv_pou := 'N';
1815 IF g_debug_unexp THEN
1816 PO_DEBUG.debug_exc(p_log_head => l_log_head,
1817 p_progress => l_progress);
1818 END IF;
1819 END check_item_in_linv_pou;
1820
1821 END PO_SHARED_PROC_PVT;