1 PACKAGE BODY PO_THIRD_PARTY_STOCK_GRP as
2 --$Header: POXGTPSB.pls 120.7.12020000.2 2013/02/10 14:17:35 vegajula ship $
3
4 --+===========================================================================+
5 --| Copyright (c) 2002 Oracle Corporation |
6 --| Redwood Shores, California, USA |
7 --| All rights reserved. |
8 --+===========================================================================+
9 --| |
10 --| FILENAME : POXGTPSB.pls |
11 --| |
12 --| DESCRIPTION: This package is used to the VMI and consigned from |
13 --| supplier validation |
14 --| |
15 --| FUNCTION/PROCEDURE: validate_local_asl |
16 --| validate_global_asl |
17 --| exist_tps_asl |
18 --| get_asl_attributes |
19 --| validate_supplier_purge |
20 --| validate_supplier_merge |
21 --| is_expense_item |
22 --| consigned_status_affected |
23 --| |
24 --| |
25 --| HISTORY: Created : 18-SEP-2002 : fdubois |
26 --| Modified: 22-SEP-2002 : fdubois |
27 --| added exist_tps_asl |
28 --+===========================================================================+
29
30 --=============================================
31 -- CONSTANTS
32 --=============================================
33 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'po.plsql.' || G_PKG_NAME || '.';
34
35 --=============================================
36 -- GLOBAL VARIABLES
37 --=============================================
38 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
39 -- <ACHTML R12 START>
40 D_PACKAGE_BASE CONSTANT VARCHAR2(50) := PO_LOG.get_package_base('PO_THIRD_PARTY_STOCK_GRP');
41
42 D_get_consigned_flag CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'get_consigned_flag');
43 -- <ACHTML R12 END>
44
45 --==========================================================================
46 -- FUNCTION NAME: Validate_Local_Asl
47 --
48 -- DESCRIPTION: the function returns TRUE if the Local ASL can be
49 -- VMI or Consigned from Supplier for the IN parameters
50 -- (define the ASL and the validation type). False
51 -- otherwise. It then also returns the Validation Error
52 -- Message name
53 --
54 -- PARAMETERS: In: p_api_version Standard API parameter
55 -- p_init_msg_list Standard API parameter
56 -- p_commit Standard API parameter
57 -- p_validation_level Standard API parameter
58 -- p_inventory_item_id Item identifier
59 -- p_supplier_site_id Supplier site identifier
60 -- p_inventory_org_id Inventory Organization
61 -- p_validation_type Validation to perform:
62 -- VMI or SUP_CONS
63 --
64 -- Out: x_return_status Standard API parameter
65 -- x_msg_count Standard API parameter
66 -- x_msg_data Standard API parameter
67 -- x_validation_error_name Error message name
68 --
69 -- Return: TRUE if OK to have Local VMI/Consigned from supplier ASL
70 --
71 --
72 -- DESIGN REFERENCES: ASL_CONSSUP_DLD.doc
73 --
74 --
75 -- CHANGE HISTORY: 18-Sep-02 FDUBOIS Created.
76 -- 15-Jan-03 VMA Add standard API parameters to comply
77 -- with PL/SQL API standard.
78 -- 16-Jan-03 VMA Bug #2660359: Added validation for
79 -- automatic PO/REQ numbering for
80 -- Consigned/VMI.
81 -- 21-Jan-03 VMA Bug #2723366: Added validation for
82 -- non-transactable or non-stockable
83 -- item.
84 -- 28-Jan-03 VMA Bug #2660359: Removed validation for
85 -- automatic numbering for Consigned/VMI
86 -- due to a change in functional
87 -- requirement.
88 -- 21-Mar-03 VMA Bug #2862335: Added validation for AX.
89 -- 03-Apr-03 VMA Bug #2885607: Added check for AX
90 -- profile option
91 -- 10-Feb-04 VMA Bug #3170458: Added return value in
92 -- exception handling block
93 --===========================================================================
94 FUNCTION validate_local_asl
95 ( p_api_version IN NUMBER
96 , p_init_msg_list IN VARCHAR2
97 , p_commit IN VARCHAR2
98 , p_validation_level IN NUMBER
99 , x_return_status OUT NOCOPY VARCHAR2
100 , x_msg_count OUT NOCOPY NUMBER
101 , x_msg_data OUT NOCOPY VARCHAR2
102 , p_inventory_item_id IN NUMBER
103 , p_supplier_site_id IN NUMBER
104 , p_inventory_org_id IN NUMBER
105 , p_validation_type IN VARCHAR2
106 , x_validation_error_name OUT NOCOPY VARCHAR2
107 )
108 RETURN BOOLEAN IS
109
110 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Local_ASL';
111 l_api_version CONSTANT NUMBER := 1.0;
112 l_inv_app_id CONSTANT NUMBER := 401;
113
114 l_purch_flag FINANCIALS_SYSTEM_PARAMS_ALL.PURCH_ENCUMBRANCE_FLAG%TYPE;
115 l_req_flag FINANCIALS_SYSTEM_PARAMS_ALL.REQ_ENCUMBRANCE_FLAG%TYPE;
116 l_sob_id FINANCIALS_SYSTEM_PARAMS_ALL.SET_OF_BOOKS_ID%TYPE;
117 l_whse_code IC_WHSE_MST.WHSE_CODE%TYPE;
118 l_OSP_flag MTL_SYSTEM_ITEMS.OUTSIDE_OPERATION_FLAG%TYPE;
119 l_bom MTL_SYSTEM_ITEMS.BOM_ITEM_TYPE%TYPE;
120 l_replenish MTL_SYSTEM_ITEMS.REPLENISH_TO_ORDER_FLAG%TYPE;
121 l_autoconfig MTL_SYSTEM_ITEMS.AUTO_CREATED_CONFIG_FLAG%TYPE;
122 l_base MTL_SYSTEM_ITEMS.BASE_ITEM_ID%TYPE;
123 l_eam MTL_SYSTEM_ITEMS.EAM_ITEM_TYPE%TYPE;
124 l_asset MTL_SYSTEM_ITEMS.INVENTORY_ASSET_FLAG%TYPE;
125 l_transactable MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG%TYPE;
126 l_stockable MTL_SYSTEM_ITEMS.STOCK_ENABLED_FLAG%TYPE;
127 l_po_num_code PO_SYSTEM_PARAMETERS.USER_DEFINED_PO_NUM_CODE%TYPE;
128 l_req_num_code PO_SYSTEM_PARAMETERS.USER_DEFINED_REQ_NUM_CODE%TYPE;
129
130
131 e_fail_validation EXCEPTION;
132
133 BEGIN
134
135 IF g_fnd_debug = 'Y' THEN
136 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
137 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
138 , G_MODULE_PREFIX || l_api_name || '.invoked'
139 , 'Entry');
140 END IF;
141 END IF;
142
143 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
144 FND_MSG_PUB.initialize;
145 END IF;
146
147 IF NOT FND_API.Compatible_API_Call( l_api_version
148 , p_api_version
149 , l_api_name
150 , G_PKG_NAME
151 )
152 THEN
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 END IF;
155
156 x_return_status := FND_API.G_RET_STS_SUCCESS;
157
158
159 -- First Validate the Encumbrance for the local ASL
160 -- get the encumbrance flags for the OU linked to the vendor site id
161 --
162 -- Bug #2682335: Also retrieve the Set of Books ID for AX validation.
163 --
164 SELECT fspa.purch_encumbrance_flag,
165 fspa.req_encumbrance_flag,
166 fspa.set_of_books_id
167 INTO l_purch_flag ,
168 l_req_flag ,
169 l_sob_id
170 FROM FINANCIALS_SYSTEM_PARAMS_ALL fspa ,
171 po_vendor_sites_all pvs
172 WHERE pvs.vendor_site_id = p_supplier_site_id
173 AND NVL(fspa.org_id,-99) = NVL(pvs.org_id,-99) ;
174
175 -- *** ENCUMBRANCE ACCOUNTING VALIDATION ***
176 -- First check for the encumbrance
177 IF l_purch_flag = 'Y' OR l_req_flag = 'Y'
178 THEN
179 -- Set the Validation error message
180 IF p_validation_type = 'VMI' THEN
181 x_validation_error_name := 'PO_VMI_ENCUMBRANCE_ENABLED' ;
182 ELSIF p_validation_type = 'SUP_CONS' THEN
183 x_validation_error_name := 'PO_SUP_CONS_ENCUMBRANCE' ;
184 END IF ;
185 -- Fail validation
186 RAISE e_fail_validation;
187 END IF ;
188
189
190 --- *** Bug 2862335: AX Validation for Consign ASL ***
191 --- *** Bug 2885607: Add check for AX profile option ***
192 --- Consign is not allowed if AX's support for Consign is not installed
193 --- and the operating unit uses AX for inventory
194 /*Bug#4340538 Commented the below piece of code since the AX packages are obsoleted
195 IF p_validation_type = 'SUP_CONS' THEN
196 IF NVL(FND_PROFILE.value('AX_SUPPLIER_CONSIGNED_ENABLED'), 'N') = 'N'
197 AND AX_SETUP_PKG.ax_exists(p_sob_id => l_sob_id,
198 p_appl_id => l_inv_app_id)
199 THEN
200 x_validation_error_name := 'PO_SUP_CONS_AX';
201 RAISE e_fail_validation;
202 END IF;
203 END IF;
204 */
205 -- get the needed info to the VMI and Consigned from supplier
206 -- local ASL validation
207
208 SELECT -- INVCONV imst.whse_code OPM Org code
209 msi.outside_operation_flag , -- OSP flag
210 msi.bom_item_type ,
211 msi.replenish_to_order_flag ,
212 msi.auto_created_config_flag ,
213 msi.base_item_id ,
214 msi.eam_item_type ,
215 msi.inventory_asset_flag ,
216 NVL(msi.mtl_transactions_enabled_flag, 'N'),
217 NVL(msi.stock_enabled_flag, 'N')
218 INTO -- INVCONV l_whse_code
219 l_OSP_flag ,
220 l_bom ,
221 l_replenish ,
222 l_autoconfig,
223 l_base ,
224 l_eam ,
225 l_asset ,
226 l_transactable,
227 l_stockable
228 FROM MTL_SYSTEM_ITEMS msi
229 -- INVCONV IC_WHSE_MST imst
230 WHERE -- INVCONV msi.organization_id = imst.mtl_organization_id (+)
231 msi.inventory_item_id = p_inventory_item_id
232 AND msi.organization_id = p_inventory_org_id ;
233
234 -- *** OPM ITEM validation
235 -- INVCONV
236 /* IF l_whse_code IS NOT NULL THEN
237 -- Set the Validation error message
238 IF p_validation_type = 'VMI' THEN
239 x_validation_error_name := 'PO_VMI_OPM_ORG_LOCAL' ;
240 ELSIF p_validation_type = 'SUP_CONS' THEN
241 x_validation_error_name := 'PO_SUP_CONS_OPM_ORG_LOCAL' ;
242 END IF ;
243 RAISE e_fail_validation;
244 END IF ; */
245 -- End INVCONV
246
247 -- *** OSP ITEM validation
248 IF l_OSP_flag = 'Y' THEN
249 -- Set the Validation error message
250 IF p_validation_type = 'VMI' THEN
251 x_validation_error_name := 'PO_VMI_OSP_ITEM' ;
252 ELSIF p_validation_type = 'SUP_CONS' THEN
253 x_validation_error_name := 'PO_SUP_CONS_OSP_ITEM' ;
254 END IF ;
255 RAISE e_fail_validation;
256 END IF ;
257
258 -- *** CTO ITEM VALIDATION ***
259 -- check for CTO Item
260 IF l_bom IN (1,2)
261 OR ( l_replenish = 'Y' AND l_base IS NULL AND l_autoconfig = 'Y')
262 THEN
263 -- Set the Validation error message
264 IF p_validation_type = 'VMI' THEN
265 x_validation_error_name := 'PO_VMI_CTO_ITEM' ;
266 ELSIF p_validation_type = 'SUP_CONS' THEN
267 x_validation_error_name := 'PO_SUP_CONS_CTO_ITEM' ;
268 END IF ;
269 RAISE e_fail_validation;
270 END IF ;
271
272 -- *** EAM ITEM VALIDATION ***
273 -- check for EAM Item
274 IF l_eam IS NOT NULL THEN
275 -- Set the Validation error message
276 IF p_validation_type = 'VMI' THEN
277 x_validation_error_name := 'PO_VMI_EAM_ITEM' ;
278 ELSIF p_validation_type = 'SUP_CONS' THEN
279 x_validation_error_name := 'PO_SUP_CONS_EAM_ITEM' ;
280 END IF ;
281 RAISE e_fail_validation;
282 END IF ;
283
284 -- *** Inventory Asset ITEM VALIDATION ***
285 -- check for Inventory Item
286 -- Bug 3582786 : moved the 'raise' inside the condition
287 -- for consigned
288 IF l_asset <> 'Y' THEN
289 -- Set the Validation error message
290 IF p_validation_type = 'SUP_CONS' THEN
291 x_validation_error_name := 'PO_SUP_CONS_INV_ASSET_ITEM' ;
292 RAISE e_fail_validation;
293 END IF ;
294 END IF ;
295
296 -- *** TRANSACTABLE AND STOCKABLE ITEM VALIDATION ***
297 -- check for non-transactable or non-stockable Item
298 -- Bug 3582786 : extended the validation for VMI also
299 IF (l_transactable = 'N' OR l_stockable = 'N') THEN
300 -- Set the Validation error message
301 IF p_validation_type = 'VMI' THEN
302 x_validation_error_name := 'PO_VMI_NON_TRX_STOCK_ITEM';
303 ELSIF p_validation_type = 'SUP_CONS' THEN
304 x_validation_error_name := 'PO_SUP_CONS_NON_TRX_STOCK_ITEM';
305 END IF ;
306 RAISE e_fail_validation;
307 END IF;
308
309 -- Pass validation if e_fail_validation has not been raised
310 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
311 , p_data => x_msg_data);
312 IF g_fnd_debug = 'Y' THEN
313 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
314 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
315 , G_MODULE_PREFIX || l_api_name || '.invoked'
316 , 'Exit');
317 END IF;
318 END IF;
319 RETURN TRUE;
320
321 EXCEPTION
322 WHEN e_fail_validation THEN
323 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
324 , p_data => x_msg_data);
325 IF g_fnd_debug = 'Y' THEN
326 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
327 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
328 , G_MODULE_PREFIX || l_api_name || '.invoked'
329 , 'Exit');
330 END IF;
331 END IF;
332 RETURN FALSE ;
333
334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
335
336 x_validation_error_name := 'PO_SUP_CONS_UNEXPECTED_ERROR';
337 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
338 , p_data => x_msg_data);
339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
340 IF g_fnd_debug = 'Y' THEN
341 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
342 FND_LOG.String( FND_LOG.LEVEL_UNEXPECTED
343 , G_MODULE_PREFIX || l_api_name || '.unexpected_error'
344 , 'Incompatible API version');
345 END IF;
346 END IF;
347 RETURN FALSE;
348
349 WHEN OTHERS THEN
350 x_validation_error_name := 'PO_SUP_CONS_UNEXPECTED_ERROR';
351 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
352 , p_data => x_msg_data);
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354 IF g_fnd_debug = 'Y' THEN
355 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
356 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
357 , G_MODULE_PREFIX || l_api_name || '.others_exception'
358 , 'Exception');
359 END IF;
360 END IF;
361 RETURN FALSE;
362
363 END validate_local_asl ;
364
365 --===========================================================================
366 -- FUNCTION NAME: Validate_Global_Asl
367 --
368 -- DESCRIPTION: the function returns TRUE if the Global ASL can be
369 -- VMI or Consigned from supplier for the IN parameters
370 -- (define the ASL). False otherwise. It then also
371 -- returns the Validation Error Message name
372 --
373 -- PARAMETERS: In: p_api_version Standard API parameter
374 -- p_init_msg_list Standard API parameter
375 -- p_commit Standard API parameter
376 -- p_validation_level Standard API parameter
377 -- p_inventory_item_id Item identifier
378 -- p_supplier_site_id Supplier site identifier
379 -- p_validation_type Validation to perform:
380 -- VMI or SUP_CONS
381 --
382 -- Out: x_return_status Standard API parameter
383 -- x_msg_count Standard API parameter
384 -- x_msg_data Standard API parameter
385 -- x_validation_error_name Error message name
386 --
387 -- Return: TRUE if OK to have Global VMI/Consigned ASL
388 --
389 --
390 -- DESIGN REFERENCES: ASL_CONSSUP_DLD.doc
391 --
392 -- CHANGE HISTORY: 22-Sep-02 FDUBOIS Created.
393 -- 15-Jan-03 VMA Bug #2677786: Add standard API
394 -- parameters to comply with PL/SQL
395 -- API standard.
396 -- 16-Jan-03 VMA Bug #2660359: Added validation for
397 -- automatic PO/REQ numbering for
398 -- Consigned/VMI.
399 -- 21-Jan-03 VMA Bug #2723366: Added validation for
400 -- non-transactable or non-stockable
401 -- item.
402 -- 28-Jan-03 VMA Bug #2660359: Removed validation for
403 -- automatic numbering for Consigned/VMI
404 -- due to a change in functional
405 -- requirement.
406 -- 21-Mar-03 VMA Bug #2862335: Added validation for AX
407 -- 03-Apr-03 VMA Bug #2885607: Added check for AX
408 -- profile option
409 -- 10-Feb-04 VMA Bug #3170458: Added return value in
410 -- exception handling block
411 --===========================================================================
412 FUNCTION validate_global_asl
413 ( p_api_version IN NUMBER
414 , p_init_msg_list IN VARCHAR2
415 , p_commit IN VARCHAR2
416 , p_validation_level IN NUMBER
417 , x_return_status OUT NOCOPY VARCHAR2
418 , x_msg_count OUT NOCOPY NUMBER
419 , x_msg_data OUT NOCOPY VARCHAR2
420 , p_inventory_item_id IN NUMBER
421 , p_supplier_site_id IN NUMBER
422 , p_validation_type IN VARCHAR2
423 , x_validation_error_name OUT NOCOPY VARCHAR2
424 )
425 RETURN BOOLEAN IS
426
427 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Global_ASL';
428 l_api_version CONSTANT NUMBER := 1.0;
429 l_inv_app_id CONSTANT NUMBER := 401;
430
431 l_purch_flag FINANCIALS_SYSTEM_PARAMS_ALL.PURCH_ENCUMBRANCE_FLAG%TYPE;
432 l_req_flag FINANCIALS_SYSTEM_PARAMS_ALL.REQ_ENCUMBRANCE_FLAG%TYPE;
433 l_sob_id FINANCIALS_SYSTEM_PARAMS_ALL.SET_OF_BOOKS_ID%TYPE;
434 l_po_num_code PO_SYSTEM_PARAMETERS.USER_DEFINED_PO_NUM_CODE%TYPE;
435 l_req_num_code PO_SYSTEM_PARAMETERS.USER_DEFINED_REQ_NUM_CODE%TYPE;
436
437 e_fail_validation EXCEPTION;
438
439 -- This cursor brings the info needed to validate the Global ASL
440 -- For INV and consign from supplier.
441 -- It brings back the Item/Inventory Org Info for all inventory Org
442 -- / Item linked to the SOB that is associated with the supplier site
443 -- Operating Unit
444 cursor c_valid_global_asl (p_inventory_item_id NUMBER ,
445 p_supplier_site_id NUMBER ) is
446 select hoi.organization_id ,
447 DECODE(HOI.ORG_INFORMATION_CONTEXT, 'Accounting Information',
448 TO_NUMBER(HOI.ORG_INFORMATION3), TO_NUMBER(NULL)) operating_unit ,
449 -- INVCONV imst.whse_code ,
450 msi.item_type ,
451 msi.outside_operation_flag ,
452 msi.eam_item_type ,
453 msi.base_item_id ,
454 msi.bom_item_type ,
455 msi.replenish_to_order_flag ,
456 msi.auto_created_config_flag ,
457 msi.inventory_asset_flag ,
458 msi.mtl_transactions_enabled_flag ,
459 msi.stock_enabled_flag
460 from gl_sets_of_books gsob ,
461 hr_organization_units hou ,
462 hr_organization_information hoi ,
463 mtl_parameters mp ,
464 hr_organization_information hoi2 ,
465 mtl_system_items msi
466 -- INVCONV ic_whse_mst imst
467 where HOU.ORGANIZATION_ID = HOI.ORGANIZATION_ID
468 and HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
469 and HOI.ORG_INFORMATION_CONTEXT||'' ='Accounting Information'
470 and HOI.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)
471 and hoi.organization_id = hoi2.organization_id
472 and hoi2.org_information_context = 'CLASS'
473 and hoi2.org_information1 = 'INV'
474 and msi.organization_id = hoi.organization_id
475 and msi.inventory_item_id = p_inventory_item_id
476 -- INVCONV and hoi.organization_id = imst.mtl_organization_id (+)
477 and GSOB.SET_OF_BOOKS_ID = (
478 select set_of_books_id
479 from po_vendor_sites_all pvsa ,
480 financials_system_params_all fspa
481 where pvsa.vendor_site_id = p_supplier_site_id
482 and NVL(fspa.org_id,-99)= NVL(pvsa.org_id,-99)
483 ) ;
484
485 BEGIN
486
487 IF g_fnd_debug = 'Y' THEN
488 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
489 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
490 , G_MODULE_PREFIX || l_api_name || '.invoked'
491 , 'Entry');
492 END IF;
493 END IF;
494
495 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
496 FND_MSG_PUB.initialize;
497 END IF;
498
499 IF NOT FND_API.Compatible_API_Call( l_api_version
500 , p_api_version
501 , l_api_name
502 , G_PKG_NAME
503 )
504 THEN
505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
506 END IF;
507
508 x_return_status := FND_API.G_RET_STS_SUCCESS;
509
510 -- First Validate the Encumbrance for the GLobal ASL
511 -- get the encumbrance flags for the OU linked to the vendor site id
512 --
513 -- Bug #2682335: Also retrieve the Set of Books ID for AX validation.
514 --
515 SELECT fspa.purch_encumbrance_flag,
516 fspa.req_encumbrance_flag,
517 fspa.set_of_books_id
518 INTO l_purch_flag ,
519 l_req_flag ,
520 l_sob_id
521 FROM FINANCIALS_SYSTEM_PARAMS_ALL fspa ,
522 po_vendor_sites_all pvs
523 WHERE pvs.vendor_site_id = p_supplier_site_id
524 AND NVL(fspa.org_id,-99) = NVL(pvs.org_id,-99) ;
525
526 -- *** ENCUMBRANCE ACCOUNTING VALIDATION ***
527 -- First check for the encumbrance
528 IF l_purch_flag = 'Y' OR l_req_flag = 'Y'
529 THEN
530 -- Set the Validation error message
531 IF p_validation_type = 'VMI' THEN
532 x_validation_error_name := 'PO_VMI_ENCUMBRANCE_ENABLED' ;
533 ELSIF p_validation_type = 'SUP_CONS' THEN
534 x_validation_error_name := 'PO_SUP_CONS_ENCUMBRANCE' ;
535 END IF ;
536 -- Fail validation
537 RAISE e_fail_validation;
538 END IF ;
539
540 --- *** Bug 2862335: AX Validation for Consign ASL ***
541 --- *** Bug 2885607: Add check for AX profile option ***
542 --- Consign is not allowed if AX's support for Consign is not installed
543 --- and the operating unit uses AX for inventory
544 /*Bug#4340538 Commented the below piece of code since the AX packages are obsoleted
545
546 IF p_validation_type = 'SUP_CONS' THEN
547 IF NVL(FND_PROFILE.value('AX_SUPPLIER_CONSIGNED_ENABLED'), 'N') = 'N'
548 AND AX_SETUP_PKG.ax_exists(p_sob_id => l_sob_id,
549 p_appl_id => l_inv_app_id)
550 THEN
551 x_validation_error_name := 'PO_SUP_CONS_AX';
552 RAISE e_fail_validation;
553 END IF;
554 END IF;
555 */
556
557
558 -- Fetch the cursor into the record and loop
559 FOR c_valid_global_asl_rec IN
560 c_valid_global_asl(p_inventory_item_id, p_supplier_site_id)
561 LOOP
562
563 -- INVCONV
564 -- *** OPM ITEM VALIDATION ***
565 -- First check for OPM Item
566 /* IF c_valid_global_asl_rec.whse_code IS NOT NULL
567 THEN
568 -- Set the Validation error message
569 IF p_validation_type = 'VMI' THEN
570 x_validation_error_name := 'PO_VMI_OPM_ORG_GLOBAL' ;
571 exit; -- exit the loop
572 ELSIF p_validation_type = 'SUP_CONS' THEN
573 x_validation_error_name := 'PO_SUP_CONS_OPM_ORG_GLOBAL' ;
574 exit; -- exit the loop
575 END IF;
576 END IF; */
577 -- End INVCONV
578
579 -- *** OSP ITEM VALIDATION ***
580 -- First check for OSP Item
581 IF c_valid_global_asl_rec.outside_operation_flag = 'Y'
582 THEN
583 IF p_validation_type = 'VMI' THEN
584 x_validation_error_name := 'PO_VMI_OSP_ITEM' ;
585 exit; -- exit the loop
586 ELSIF p_validation_type = 'SUP_CONS' THEN
587 x_validation_error_name := 'PO_SUP_CONS_OSP_ITEM' ;
588 exit ; -- exit the loop
589 END IF ;
590 END IF ;
591
592 -- *** CTO ITEM VALIDATION ***
593 -- First check for CTO Item
594 IF c_valid_global_asl_rec.bom_item_type IN (1,2)
595 OR ( c_valid_global_asl_rec.replenish_to_order_flag = 'Y' AND
596 c_valid_global_asl_rec.base_item_id IS NULL AND
597 c_valid_global_asl_rec.auto_created_config_flag = 'Y')
598 THEN
599 -- Set the Validation error message
600 IF p_validation_type = 'VMI' THEN
601 x_validation_error_name := 'PO_VMI_CTO_ITEM' ;
602 exit ; -- exit the loop
603 ELSIF p_validation_type = 'SUP_CONS' THEN
604 x_validation_error_name := 'PO_SUP_CONS_CTO_ITEM' ;
605 exit ; -- exit the loop
606 END IF ;
607 END IF ;
608
609 -- *** EAM ITEM VALIDATION ***
610 -- First check for EAM Item
611 IF c_valid_global_asl_rec.eam_item_type IS NOT NULL
612 THEN
613 -- Set the Validation error message
614 IF p_validation_type = 'VMI' THEN
615 x_validation_error_name := 'PO_VMI_EAM_ITEM' ;
616 exit ; -- exit the loop
617 ELSIF p_validation_type = 'SUP_CONS' THEN
618 x_validation_error_name := 'PO_SUP_CONS_EAM_ITEM' ;
619 exit ; -- exit the loop
620 END IF ;
621 END IF ;
622
623 -- *** Inventory Asset ITEM VALIDATION ***
624 -- check for Inventory Item
625 IF c_valid_global_asl_rec.inventory_asset_flag <> 'Y' THEN
626 -- Set the Validation error message
627 IF p_validation_type = 'SUP_CONS' THEN
628 x_validation_error_name := 'PO_SUP_CONS_INV_ASSET_ITEM' ;
629 exit ; -- exit the loop
630 END IF ;
631 END IF ;
632
633 -- *** TRANSACTABLE AND STOCKABLE ITEM VALIDATION ***
634 -- check for non-transactable or non-stockable Item
635 -- Bug 3582786 : extended the validation for VMI also
636 IF (NVL(c_valid_global_asl_rec.mtl_transactions_enabled_flag, 'N') = 'N'
637 OR NVL(c_valid_global_asl_rec.stock_enabled_flag, 'N') = 'N')
638 THEN
639 IF p_validation_type = 'VMI' THEN
640 x_validation_error_name := 'PO_VMI_NON_TRX_STOCK_ITEM';
641 exit;
642 ELSIF p_validation_type = 'SUP_CONS' THEN
643 x_validation_error_name := 'PO_SUP_CONS_NON_TRX_STOCK_ITEM';
644 exit;
645 END IF ;
646 END IF;
647
648 END LOOP ;
649
650
651 -- Test if one record failed the validation
652 IF x_validation_error_name IS NOT NULL
653 THEN
654 RAISE e_fail_validation;
655 END IF;
656
657
658 -- Pass validation if e_fail_validation has not been raised
659 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
660 , p_data => x_msg_data);
661 IF g_fnd_debug = 'Y' THEN
662 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
663 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
664 , G_MODULE_PREFIX || l_api_name || '.invoked'
665 , 'Exit');
666 END IF;
667 END IF;
668 RETURN TRUE ;
669
670 EXCEPTION
671 WHEN e_fail_validation THEN
672 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
673 , p_data => x_msg_data);
674 IF g_fnd_debug = 'Y' THEN
675 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
676 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
677 , G_MODULE_PREFIX || l_api_name || '.invoked'
678 , 'Exit');
679 END IF;
680 END IF;
681 RETURN FALSE ;
682
683 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
684
685 x_validation_error_name := 'PO_SUP_CONS_UNEXPECTED_ERROR';
686 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
687 , p_data => x_msg_data);
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689 IF g_fnd_debug = 'Y' THEN
690 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
691 FND_LOG.String( FND_LOG.LEVEL_UNEXPECTED
692 , G_MODULE_PREFIX || l_api_name || '.unexpected_error'
693 , 'Incompatible API version');
694 END IF;
695 END IF;
696 RETURN FALSE;
697
698 WHEN OTHERS THEN
699
700 x_validation_error_name := 'PO_SUP_CONS_UNEXPECTED_ERROR';
701 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
702 , p_data => x_msg_data);
703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
704 IF g_fnd_debug = 'Y' THEN
705 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
706 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
707 , G_MODULE_PREFIX || l_api_name || '.others_exception'
708 , 'Exception');
709 END IF;
710 END IF;
711 RETURN FALSE;
712
713 END validate_global_asl ;
714
715 --===========================================================================
716 -- FUNCTION NAME: Exist_TPS_ASL
717 --
718 -- DESCRIPTION: the function returns TRUE if there exist a
719 -- VMI/Consined ASL within the Operating Unit.
720 -- If there is none it returns FALSE.
721 --
722 -- PARAMETERS:
723 -- Return: TRUE if exists VMI/Consigned ASL
724 --
725 -- DESIGN REFERENCES: APXSSFSO_CONSSUP_DLD.doc
726 --
727 -- CHANGE HISTORY: 26-Sep-02 FDUBOIS Created.
728 -- 14-OCT-02 FDUBOIS Added logic for the function
729 --===========================================================================
730 FUNCTION Exist_TPS_ASL RETURN BOOLEAN IS
731
732 l_exist_TPS_ASL BOOLEAN ;
733 l_count_exist_TPS_ASL NUMBER ;
734
735 BEGIN
736 -- Returns 1 if there exist a VMI or consign ASL within the current OU
737 SELECT count('x')
738 INTO l_count_exist_TPS_ASL
739 FROM dual
740 WHERE exists
741 ( SELECT 'X'
742 FROM po_approved_supplier_list pasl,
743 po_vendor_sites pvs ,
744 po_asl_status_rules_v pasr ,
745 po_asl_attributes paa
746 WHERE pasl.vendor_site_id = pvs.vendor_site_id
747 AND pasr.status_id = pasl.asl_status_id
748 AND pasr.business_rule like '2_SOURCING'
749 AND pasr.allow_action_flag like 'Y'
750 AND ( pasl.disable_flag = 'N'
751 OR pasl.disable_flag IS NULL)
752 AND paa.asl_id = pasl.asl_id
753 AND ( paa.enable_vmi_flag = 'Y'
754 OR paa.consigned_from_supplier_flag = 'Y')
755 ) ;
756
757 -- Assign the boolean value depending on the return count
758 IF l_count_exist_TPS_ASL = 1 THEN
759 l_exist_TPS_ASL := TRUE ;
760 ELSE
761 l_exist_TPS_ASL := FALSE ;
762 END IF ;
763
764 RETURN l_exist_TPS_ASL;
765
766 END exist_tps_asl ;
767
768 --===========================================================================
769 -- API NAME : Validate_Supplier_Purge
770 -- API TYPE : Public
771 -- DESCRIPTION : Checks whether a supplier can be
772 -- purged according to Consigned Inventory criteria.
773 -- A supplier cannot be purged if any of its vendor site
774 -- has on hand consigned stock. The function returns
775 -- 'TRUE' is the supplier does not have any on hand
776 -- consigned stock - in this case the supplier may be
777 -- purged. The function returns 'FALSE' if the supplier
778 -- has on hand consigned stock - in this case, the
779 -- supplier should not be purged.
780 --
781 -- PARAMETERS : p_vendor_id
782 --
783 -- RETURN : 'TRUE' if the purge may proceed; 'FALSE' if the purge
784 -- should not proceed.
785 --
786 -- DESIGN DOC : SUPPUR_CONSSUP_DLD.doc
787 --
788 -- HISTORY : 11-12-02 vma Created
789 -- 12-12-02 vma The function Supplier_Owns_Tps in
790 -- INV_SUPPLIER_OWNED_STOCK_GRP
791 -- has been moved to
792 -- PO_INV_THIRD_PARTY_STOCK_MDTR.
793 -- Modify call accordingly.
794 --===========================================================================
795 FUNCTION Validate_Supplier_Purge(p_vendor_id IN NUMBER)
796 RETURN VARCHAR2 IS
797
798 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Supplier_Purge';
799 l_return_value VARCHAR2(10);
800
801 -- <DOC PURGE FPJ START>
802 l_return_value_tmp VARCHAR2(10);
803 l_rtn_status VARCHAR2(1);
804 l_msg_count NUMBER;
805 l_msg_data VARCHAR2(2000);
806 -- <DOC PURGE FPJ END>
807
808 BEGIN
809 IF g_fnd_debug = 'Y' THEN
810 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
811 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
812 , G_MODULE_PREFIX || l_api_name || '.invoked'
813 , 'Entry');
814 END IF;
815 END IF;
816
817 IF PO_INV_THIRD_PARTY_STOCK_MDTR.Supplier_Owns_Tps(p_vendor_id) THEN
818 l_return_value := 'FALSE';
819 ELSE
820 l_return_value := 'TRUE';
821 END IF;
822
823 -- <DOC PURGE FPJ START>
824 -- Integrate with Sourcing to make sure that vendor purge is allowed for
825 -- this vendor
826
827 IF (l_return_value = 'TRUE') THEN
828
829 l_return_value_tmp :=
830 PON_VENDOR_PURGE_GRP.validate_vendor_purge
831 ( p_api_version => 1.0,
832 p_init_msg_list => FND_API.G_TRUE,
833 x_return_status => l_rtn_status,
834 x_msg_count => l_msg_count,
835 x_msg_data => l_msg_data,
836 p_vendor_id => p_vendor_id
837 );
838
839 IF (l_return_value_tmp = 'N') THEN
840
841 IF g_fnd_debug = 'Y' THEN
842 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
843 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
844 , G_MODULE_PREFIX || l_api_name || '.010'
845 , 'PON rejects vnd_id ' || p_vendor_id ||
846 'from purge list');
847 END IF;
848 END IF;
849
850 l_return_value := 'FALSE';
851 END IF;
852 END IF;
853
854 -- <DOC PURGE FPJ END>
855
856
857 IF g_fnd_debug = 'Y' THEN
858 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
859 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
860 , G_MODULE_PREFIX || l_api_name || '.invoked'
861 , 'Exit');
862 END IF;
863 END IF;
864
865 RETURN l_return_value;
866
867 END Validate_Supplier_Purge;
868
869 --===========================================================================
870 -- API NAME : Validate_Supplier_Merge
871 -- TYPE : Public
872 -- Pre-condition : Supplier site exists. If the supplier site does not
873 -- exist, x_can_merge will contain value FND_API.G_TRUE
874 -- DESCRIPTION : Checks whether a supplier site cannot be
875 -- merged according to Consigned/VMI criteria.
876 -- A merge should fail if for the FROM supplier site:
877 -- - on hand quantity exists in consigned or VMI stock
878 -- - open consigned shipments exist
879 -- - open consumption advices exist
880 -- - open VMI release lines exist
881 -- ('open' meaning neither FINALLY CLOSED nor CANCELLED)
882 --
883 -- PARAMETERS : p_api_version Standard API parameter
884 -- p_init_msg_list Standard API parameter
885 -- p_commit Standard API parameter
886 -- p_validation_level Standard API parameter
887 -- x_return_status Standard API parameter
888 -- x_msg_count Standard API parameter
889 -- x_msg_data Standard API parameter
890 -- p_vendor_site_id Vendor site id
891 -- x_can_merge FND_API.G_FALSE if the supplier
892 -- site cannot be merged;
893 -- FND_API.G_TRUE otherwise.
894 -- x_validation_error Name of validation error.
895 -- 'PO_SUP_CONS_FAIL_MERGE_TPS' if
896 -- merge should fail because on hand
897 -- consigned/VMI stock exists;
898 -- 'PO_SUP_CONS_FAIL_MERGE_DOC' if
899 -- merge should fail because open PO
900 -- documents exist.
901 -- p_vendor_id Vendor ID
902 --
903 -- DESIGN DOC : SUPPUR_CONSSUP_DLD.doc
904 --
905 -- HISTORY : 11-12-02 vma Created
906 -- 12-12-02 vma The function Sup_Site_Owns_Tps in
907 -- INV_SUPPLIER_OWNED_STOCK_GRP
908 -- has been moved to
909 -- PO_INV_THIRD_PARTY_STOCK_MDTR.
910 -- Modify call accordingly.
911 -- Added standard API parameters to
912 -- comply with PL/SQL coding standard.
913 --===========================================================================
914 PROCEDURE Validate_Supplier_Merge
915 ( p_api_version IN NUMBER
916 , p_init_msg_list IN VARCHAR2
917 , p_commit IN VARCHAR2
918 , p_validation_level IN NUMBER
919 , x_return_status OUT NOCOPY VARCHAR2
920 , x_msg_count OUT NOCOPY NUMBER
921 , x_msg_data OUT NOCOPY VARCHAR2
922 , p_vendor_site_id IN NUMBER
923 , p_vendor_id IN NUMBER
924 , x_can_merge OUT NOCOPY VARCHAR2
925 , x_validation_error OUT NOCOPY VARCHAR2
926 )
927 IS
928 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Supplier_Merge';
929 l_api_version CONSTANT NUMBER := 1.0;
930 l_open Number;
931
932 BEGIN
933
934 IF g_fnd_debug = 'Y' THEN
935 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
936 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
937 , G_MODULE_PREFIX || l_api_name || '.invoked'
938 , 'Entry');
939 END IF;
940 END IF;
941
942 -- Start API initialization
943 IF FND_API.to_boolean(p_init_msg_list) THEN
944 FND_MSG_PUB.initialize;
945 END IF;
946
947 IF NOT FND_API.Compatible_API_Call( l_api_version
948 , p_api_version
949 , l_api_name
950 , G_PKG_NAME)
951 THEN
952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
953 END IF;
954
955 x_return_status := FND_API.G_RET_STS_SUCCESS;
956 -- End API Initialization
957
958 x_can_merge := FND_API.G_TRUE;
959
960 IF FND_PROFILE.value('PO_SUPPLIER_CONSIGNED_ENABLED') = 'Y' AND
961 FND_PROFILE.value('INV_SUPPLIER_CONSIGNED_ENABLED') = 'Y'
962 THEN
963
964 -- Check for on hand Consigned and VMI stock
965 IF PO_INV_THIRD_PARTY_STOCK_MDTR.Sup_Site_Owns_Tps(p_vendor_site_id) THEN
966 x_can_merge := FND_API.G_FALSE;
967 x_validation_error := 'PO_SUP_CONS_FAIL_MERGE_TPS';
968
969 ELSE
970
971 -- Check for open PO documents:
972 -- 1. Consumption advices (standard PO)
973 -- 2. Consumption advices (release)
974 -- 3. VMI release lines
975 -- 4. Consigned shipments
976 SELECT COUNT('x')
977 INTO l_open
978 FROM dual
979 WHERE EXISTS
980 (SELECT 'x'
981 FROM po_headers_all
982 WHERE consigned_consumption_flag = 'Y'
983 AND type_lookup_code = 'STANDARD'
984 AND vendor_site_id = p_vendor_site_id
985 AND vendor_id = p_vendor_id --bug 3649022
986 AND NVL(closed_code, 'a') <> 'FINALLY CLOSED'
987 AND NVL(cancel_flag, 'N') = 'N')
988 OR EXISTS
989 (SELECT 'x'
990 FROM po_releases_all por, po_headers_all poh
991 WHERE por.consigned_consumption_flag = 'Y'
992 AND poh.vendor_site_id = p_vendor_site_id
993 AND poh.vendor_id = p_vendor_id --bug 3649022
994 AND poh.po_header_id = por.po_header_id
995 AND nvl(por.closed_code, 'a') <> 'FINALLY CLOSED'
996 AND nvl(por.cancel_flag, 'N') = 'N')
997 OR EXISTS
998 (SELECT 'x'
999 FROM po_line_locations_all pol, po_releases_all por,
1000 po_headers_all poh
1001 WHERE poh.vendor_site_id = p_vendor_site_id
1002 AND poh.vendor_id = p_vendor_id --bug 3649022
1003 AND por.po_header_id = poh.po_header_id
1004 AND pol.po_release_id = por.po_release_id
1005 AND pol.vmi_flag = 'Y'
1006 AND NVL(pol.closed_code, 'a') <> 'FINALLY CLOSED'
1007 AND NVL(pol.cancel_flag, 'N') = 'N')
1008 OR EXISTS
1009 (SELECT 'x'
1010 FROM po_line_locations_all pol, po_headers_all poh
1011 WHERE poh.vendor_site_id = p_vendor_site_id
1012 AND poh.vendor_id = p_vendor_id --bug 3649022
1013 AND poh.po_header_id = pol.po_header_id
1014 AND pol.consigned_flag = 'Y'
1015 AND nvl(pol.closed_code, 'a') <> 'FINALLY CLOSED'
1016 AND nvl(pol.cancel_flag, 'N') = 'N');
1017
1018 IF l_open = 1 THEN
1019 x_can_merge := FND_API.G_FALSE;
1020 x_validation_error := 'PO_SUP_CONS_FAIL_MERGE_DOC';
1021 END IF;
1022
1023 END IF;
1024 END IF;
1025
1026 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1027 p_data => x_msg_data);
1028
1029 IF g_fnd_debug = 'Y' THEN
1030 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1031 FND_LOG.string( FND_LOG.LEVEL_PROCEDURE
1032 , G_MODULE_PREFIX || l_api_name || '.invoked'
1033 , 'Exit');
1034 END IF;
1035 END IF;
1036
1037 EXCEPTION
1038
1039 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1040 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1041 p_data => x_msg_data);
1042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1043 IF g_fnd_debug = 'Y' THEN
1044 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1045 FND_LOG.String( FND_LOG.LEVEL_UNEXPECTED
1046 , G_MODULE_PREFIX || l_api_name || '.unexpected_error'
1047 , 'Incompatible API version');
1048 END IF;
1049 END IF;
1050
1051 WHEN OTHERS THEN
1052 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1053 p_data => x_msg_data);
1054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1055 IF g_fnd_debug = 'Y' THEN
1056 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1057 FND_LOG.String( FND_LOG.LEVEL_UNEXPECTED
1058 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1059 , 'Exception');
1060 END IF;
1061 END IF;
1062
1063 END Validate_Supplier_Merge;
1064
1065 --=============================================================================
1066 -- API NAME : Get_Asl_Attributes
1067 -- TYPE : PUBLIC
1068 -- PRE-CONDITION : The inventory_item_id, vendor_id, vendor_site_id and
1069 -- using organization_id passed in should be not NULL, or else
1070 -- all the out parameters will have NULL values
1071 -- DESCRIPTION : This procedure returns the Consigned from Supplier
1072 -- and VMI setting of the ASL entry that corresponds to
1073 -- the passed in item/supplier/supplier site/organization
1074 -- combination, as OUT parameters.
1075 -- PARAMETERS :
1076 -- p_api_version REQUIRED. API version
1077 -- p_init_msg_list REQUIRED. FND_API.G_TRUE to reset the
1078 -- message list.
1079 -- NULL value is regarded as
1080 -- FND_API.G_FALSE.
1081 -- x_return_status REQUIRED. Value can be
1082 -- FND_API.G_RET_STS_SUCCESS
1083 -- FND_API.G_RET_STS_ERROR
1084 -- FND_API.G_RET_STS_UNEXP_ERROR
1085 -- x_msg_count REQUIRED. Number of messages on the message
1086 -- list
1087 -- x_msg_data REQUIRED. Return message data if message
1088 -- count is 1
1089 -- p_inventory_item_id REQUIRED. Item identifier.
1090 -- p_vendor_id REQUIRED. Supplier identifier.
1091 -- p_vendor_site_id REQUIRED. Supplier site identifier.
1092 -- p_using_organization_id REQUIRED. Identifier of the organization to
1093 -- which the shipments are delivered
1094 -- to.
1095 -- x_consigned_from_supplier_flag REQUIRED. Consigned setting of the ASL
1096 -- x_enable_vmi_flag REQUIRED. VMI setting of the ASL
1097 -- x_last_billing_date REQUIRED. Last date when the consigned
1098 -- consumption concurrent program
1099 -- ran
1100 -- x_consigned_billing_cycle REQUIRED. The number of days before
1101 -- summarizing the consigned POs
1102 -- received and transfer the
1103 -- goods to regular stock
1104 -- EXCEPTIONS :
1105 --
1106 --=============================================================================
1107 PROCEDURE Get_Asl_Attributes
1108 ( p_api_version IN NUMBER
1109 , p_init_msg_list IN VARCHAR2
1110 , x_return_status OUT NOCOPY VARCHAR2
1111 , x_msg_count OUT NOCOPY NUMBER
1112 , x_msg_data OUT NOCOPY VARCHAR2
1113 , p_inventory_item_id IN NUMBER
1114 , p_vendor_id IN NUMBER
1115 , p_vendor_site_id IN NUMBER
1116 , p_using_organization_id IN NUMBER
1117 , x_consigned_from_supplier_flag OUT NOCOPY VARCHAR2
1118 , x_enable_vmi_flag OUT NOCOPY VARCHAR2
1119 , x_last_billing_date OUT NOCOPY DATE
1120 , x_consigned_billing_cycle OUT NOCOPY NUMBER
1121 )
1122 IS
1123 l_api_name CONSTANT VARCHAR2(30) := 'Get_Asl_Attributes';
1124 l_api_version CONSTANT NUMBER := 1.0;
1125
1126 l_asl_id NUMBER := NULL;
1127 l_vendor_product_num
1128 PO_APPROVED_SUPPLIER_LIS_VAL_V.PRIMARY_VENDOR_ITEM%TYPE := NULL;
1129 l_purchasing_uom
1130 PO_ASL_ATTRIBUTES.PURCHASING_UNIT_OF_MEASURE%TYPE := NULL;
1131 l_using_organization_id NUMBER
1132 := p_using_organization_id;
1133 l_vmi_min_qty NUMBER := NULL;
1134 l_vmi_max_qty NUMBER := NULL;
1135 l_vmi_auto_replenish_flag
1136 PO_ASL_ATTRIBUTES.ENABLE_VMI_AUTO_REPLENISH_FLAG%TYPE := NULL;
1137 l_vmi_replenishment_approval
1138 PO_ASL_ATTRIBUTES.VMI_REPLENISHMENT_APPROVAL%TYPE := NULL;
1139
1140 BEGIN
1141
1142 IF (g_fnd_debug = 'Y')
1143 THEN
1144 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1145 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1146 , G_MODULE_PREFIX || l_api_name || '.invoked'
1147 , 'Entry');
1148 END IF;
1149 END IF;
1150
1151 -- Start API initialization
1152 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
1153 FND_MSG_PUB.initialize;
1154 END IF;
1155
1156 IF NOT FND_API.Compatible_API_Call( l_api_version
1157 , p_api_version
1158 , l_api_name
1159 , G_PKG_NAME
1160 )
1161 THEN
1162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1163 END IF;
1164
1165 x_return_status := FND_API.G_RET_STS_SUCCESS;
1166 -- End API initialization
1167
1168 x_enable_vmi_flag := null;
1169 x_consigned_from_supplier_flag := null;
1170 x_last_billing_date := null;
1171 x_consigned_billing_cycle := null;
1172
1173 IF(p_inventory_item_id is not null
1174 AND p_vendor_id is not null
1175 AND p_vendor_site_id is not null
1176 AND p_using_organization_id is not null)
1177 THEN
1178 PO_AUTOSOURCE_SV.get_asl_info
1179 ( x_item_id => p_inventory_item_id
1180 , x_vendor_id => p_vendor_id
1181 , x_vendor_site_id => p_vendor_site_id
1182 , x_using_organization_id => l_using_organization_id
1183 , x_asl_id => l_asl_id
1184 , x_vendor_product_num => l_vendor_product_num
1185 , x_purchasing_uom => l_purchasing_uom
1186 , x_consigned_from_supplier_flag => x_consigned_from_supplier_flag
1187 , x_enable_vmi_flag => x_enable_vmi_flag
1188 , x_last_billing_date => x_last_billing_date
1189 , x_consigned_billing_cycle => x_consigned_billing_cycle
1190 , x_vmi_min_qty => l_vmi_min_qty
1191 , x_vmi_max_qty => l_vmi_max_qty
1192 , x_vmi_auto_replenish_flag => l_vmi_auto_replenish_flag
1193 , x_vmi_replenishment_approval => l_vmi_replenishment_approval
1194 );
1195 END IF;
1196
1197 FND_MSG_PUB.Count_And_Get
1198 ( p_count => x_msg_count,
1199 p_data => x_msg_data
1200 );
1201
1202 IF (g_fnd_debug = 'Y') THEN
1203 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1204 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1205 , G_MODULE_PREFIX || l_api_name || '.invoked'
1206 , 'Exit');
1207 END IF;
1208 END IF;
1209
1210 EXCEPTION
1211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1212 FND_MSG_PUB.Count_And_Get
1213 ( p_count => x_msg_count
1214 , p_data => x_msg_data
1215 );
1216
1217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1218 IF (g_fnd_debug = 'Y')
1219 THEN
1220 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1221 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1222 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
1223 , 'Exception');
1224 END IF;
1225 END IF;
1226
1227 WHEN OTHERS THEN
1228 FND_MSG_PUB.Count_And_Get
1229 ( p_count => x_msg_count
1230 , p_data => x_msg_data
1231 );
1232
1233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1234 IF (g_fnd_debug = 'Y')
1235 THEN
1236 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1237 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1238 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1239 , 'Exception');
1240 END IF;
1241 END IF;
1242 END get_asl_attributes;
1243
1244 --=============================================================================
1245 -- API NAME : Get_Item_Inv_Asset_Flag
1246 -- TYPE : PUBLIC
1247 -- PRE-CONDITION : Item must exist, or else the NO_DATA_FOUND exception
1248 -- would be thrown and the out parameter
1249 -- x_inventory_asset_flag would be set to NULL.
1250 -- DESCRIPTION : Get the INVENTORY_ASSET_FLAG for a particular item. This
1251 -- procedure is typically for determining whether an item is
1252 -- expense or not.
1253 -- PARAMETERS :
1254 -- p_api_version REQUIRED. API version
1255 -- p_init_msg_list REQUIRED. FND_API.G_TRUE to reset the message
1256 -- list.
1257 -- NULL value is regarded as
1258 -- FND_API.G_FALSE.
1259 -- x_return_status REQUIRED. Value can be
1260 -- FND_API.G_RET_STS_SUCCESS
1261 -- FND_API.G_RET_STS_ERROR
1262 -- FND_API.G_RET_STS_UNEXP_ERROR
1263 -- x_msg_count REQUIRED. Number of messages on the message list
1264 -- x_msg_data REQUIRED. Return message data if message count
1265 -- is 1
1266 -- p_organization_id REQUIRED. Identifier of the organization to
1267 -- which the item was assigned to
1268 -- p_inventory_item_id REQUIRED. Item identifier.
1269 -- x_inventory_asset_flag REQUIRED. Inventory Asset Flag of the specified
1270 -- item.
1271 -- EXCEPTIONS :
1272 --
1273 --=============================================================================
1274 PROCEDURE Get_Item_Inv_Asset_Flag
1275 ( p_api_version IN NUMBER
1276 , p_init_msg_list IN VARCHAR2
1277 , x_return_status OUT NOCOPY VARCHAR2
1278 , x_msg_count OUT NOCOPY NUMBER
1279 , x_msg_data OUT NOCOPY VARCHAR2
1280 , p_organization_id IN NUMBER
1281 , p_inventory_item_id IN NUMBER
1282 , x_inventory_asset_flag OUT NOCOPY VARCHAR2
1283 )
1284 IS
1285 l_api_name CONSTANT VARCHAR2(30) := 'Get_Item_Inv_Asset_Flag';
1286 l_api_version CONSTANT NUMBER := 1.0;
1287
1288 BEGIN
1289
1290 IF (g_fnd_debug = 'Y')
1291 THEN
1292 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1293 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1294 , G_MODULE_PREFIX || l_api_name || '.invoked'
1295 , 'Entry');
1296 END IF;
1297 END IF;
1298
1299 -- Start API initialization
1300 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
1301 FND_MSG_PUB.initialize;
1302 END IF;
1303
1304 IF NOT FND_API.Compatible_API_Call( l_api_version
1305 , p_api_version
1306 , l_api_name
1307 , G_PKG_NAME
1308 )
1309 THEN
1310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1311 END IF;
1312
1313 x_return_status := FND_API.G_RET_STS_SUCCESS;
1314 -- End API initialization
1315
1316 SELECT NVL(inventory_asset_flag, 'N')
1317 INTO x_inventory_asset_flag
1318 FROM mtl_system_items_b
1319 WHERE inventory_item_id = p_inventory_item_id
1320 AND organization_id = p_organization_id;
1321
1322 FND_MSG_PUB.Count_And_Get
1323 ( p_count => x_msg_count,
1324 p_data => x_msg_data
1325 );
1326
1327 IF (g_fnd_debug = 'Y') THEN
1328 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1329 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1330 , G_MODULE_PREFIX || l_api_name || '.invoked'
1331 , 'Exit');
1332 END IF;
1333 END IF;
1334
1335 EXCEPTION
1336 WHEN NO_DATA_FOUND THEN
1337 x_inventory_asset_flag := NULL;
1338 FND_MSG_PUB.Count_And_Get
1339 ( p_count => x_msg_count
1340 , p_data => x_msg_data
1341 );
1342 x_return_status := FND_API.G_RET_STS_ERROR;
1343 IF (g_fnd_debug = 'Y')
1344 THEN
1345 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1346 FND_LOG.string(FND_LOG.LEVEL_ERROR
1347 , G_MODULE_PREFIX || l_api_name || '.no_data_found'
1348 , 'Exception');
1349 END IF;
1350 END IF;
1351 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1352 FND_MSG_PUB.Count_And_Get
1353 ( p_count => x_msg_count
1354 , p_data => x_msg_data
1355 );
1356
1357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1358 IF (g_fnd_debug = 'Y')
1359 THEN
1360 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1361 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1362 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
1363 , 'Exception');
1364 END IF;
1365 END IF;
1366 WHEN OTHERS THEN
1367 FND_MSG_PUB.Count_And_Get
1368 ( p_count => x_msg_count
1369 , p_data => x_msg_data
1370 );
1371
1372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1373 IF (g_fnd_debug = 'Y')
1374 THEN
1375 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1376 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1377 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1378 , 'Exception');
1379 END IF;
1380 END IF;
1381
1382 END Get_Item_Inv_Asset_Flag;
1383
1384 --=============================================================================
1385 -- API NAME : Consigned_Status_Affected
1386 -- TYPE : PUBLIC
1387 -- PRE-CONDITION : None
1388 -- DESCRIPTION : Returns 'Y' to the out parameter x_consigned_status_affected
1389 -- if the passed in vendor and vendor site would lead to changes
1390 -- of the the consigned status on any child shipments that
1391 -- belong to the PO specified by the passed in PO_HEADER_ID
1392 -- PARAMETERS :
1393 -- p_api_version REQUIRED. API version
1394 -- p_init_msg_list REQUIRED. FND_API.G_TRUE to reset the
1395 -- message list.
1396 -- NULL value is regarded as
1397 -- FND_API.G_FALSE.
1398 -- x_return_status REQUIRED. Value can be
1399 -- FND_API.G_RET_STS_SUCCESS
1400 -- FND_API.G_RET_STS_ERROR
1401 -- FND_API.G_RET_STS_UNEXP_ERROR
1402 -- x_msg_count REQUIRED. Number of messages on the message
1403 -- list
1404 -- x_msg_data REQUIRED. Return message data if message
1405 -- count is 1
1406 -- p_vendor_id REQUIRED. Supplier identifier.
1407 -- p_vendor_site_id REQUIRED. Supplier Site identifier.
1408 -- p_po_header_id REQUIRED. Header identifier of the PO to be
1409 -- validated
1410 -- x_consigned_status_affected REQUIRED. Y if any of the shipment lines
1411 -- would change in the consigned
1412 -- status if adopting the passed in
1413 -- vendor and vendor site. N otherwise.
1414 -- EXCEPTIONS :
1415 --
1416 --=============================================================================
1417 PROCEDURE Consigned_Status_Affected
1418 ( p_api_version IN NUMBER
1419 , p_init_msg_list IN VARCHAR2
1420 , x_return_status OUT NOCOPY VARCHAR2
1421 , x_msg_count OUT NOCOPY NUMBER
1422 , x_msg_data OUT NOCOPY VARCHAR2
1423 , p_vendor_id IN NUMBER
1424 , p_vendor_site_id IN NUMBER
1425 , p_po_header_id IN NUMBER
1426 , x_consigned_status_affected OUT NOCOPY VARCHAR2
1427 )
1428 IS
1429 l_api_name CONSTANT VARCHAR2(30) := 'Consigned_Status_Affected';
1430 l_api_version CONSTANT NUMBER := 1.0;
1431
1432 l_found BOOLEAN := FALSE;
1433 --l_line_location_id NUMBER := NULL;--Bug 14664015
1434 l_item_id NUMBER := NULL;
1435 l_ship_to_organization_id NUMBER := NULL;
1436 l_consigned_flag
1437 PO_LINE_LOCATIONS.CONSIGNED_FLAG%TYPE := NULL;
1438 l_consigned_from_supplier_flag
1439 PO_ASL_ATTRIBUTES.CONSIGNED_FROM_SUPPLIER_FLAG%TYPE := NULL;
1440 l_enable_vmi_flag
1441 PO_ASL_ATTRIBUTES.ENABLE_VMI_FLAG%TYPE := NULL;
1442 l_last_billing_date DATE := NULL;
1443 l_consigned_billing_cycle NUMBER := NULL;
1444
1445 CURSOR C is
1446 SELECT DISTINCT --pll.line_location_id, --Bug 14664015
1447 pl.item_id,
1448 pll.ship_to_organization_id,
1449 pll.consigned_flag
1450 FROM po_line_locations_all pll,
1451 po_lines_all pl
1452 WHERE pll.po_header_id = p_po_header_id
1453 AND pl.po_line_id = pll.po_line_id;
1454
1455 BEGIN
1456
1457 IF (g_fnd_debug = 'Y')
1458 THEN
1459 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1460 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1461 , G_MODULE_PREFIX || l_api_name || '.invoked'
1462 , 'Entry');
1463 END IF;
1464 END IF;
1465
1466 -- Start API initialization
1467 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
1468 FND_MSG_PUB.initialize;
1469 END IF;
1470
1471 IF NOT FND_API.Compatible_API_Call( l_api_version
1472 , p_api_version
1473 , l_api_name
1474 , G_PKG_NAME
1475 )
1476 THEN
1477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1478 END IF;
1479
1480 x_return_status := FND_API.G_RET_STS_SUCCESS;
1481 -- End API initialization
1482
1483 x_consigned_status_affected := NULL;
1484
1485 OPEN C;
1486 LOOP
1487
1488 FETCH C into --l_line_location_id,--Bug 14664015
1489 l_item_id,
1490 l_ship_to_organization_id,
1491 l_consigned_flag;
1492
1493 EXIT WHEN C%NOTFOUND OR x_consigned_status_affected = 'Y';
1494
1495 po_third_party_stock_grp.get_asl_attributes
1496 ( p_api_version => 1.0
1497 , p_init_msg_list => NULL
1498 , x_return_status => x_return_status
1499 , x_msg_count => x_msg_count
1500 , x_msg_data => x_msg_data
1501 , p_inventory_item_id => l_item_id
1502 , p_vendor_id => p_vendor_id
1503 , p_vendor_site_id => p_vendor_site_id
1504 , p_using_organization_id => l_ship_to_organization_id
1505 , x_consigned_from_supplier_flag => l_consigned_from_supplier_flag
1506 , x_enable_vmi_flag => l_enable_vmi_flag
1507 , x_last_billing_date => l_last_billing_date
1508 , x_consigned_billing_cycle => l_consigned_billing_cycle
1509 );
1510
1511 IF(NVL(l_consigned_from_supplier_flag, 'N') <> NVL(l_consigned_flag, 'N'))
1512 THEN
1513 x_consigned_status_affected := 'Y';
1514 ELSE
1515 x_consigned_status_affected := 'N';
1516 END IF;
1517
1518 END LOOP;
1519 CLOSE C;
1520
1521 FND_MSG_PUB.Count_And_Get
1522 ( p_count => x_msg_count,
1523 p_data => x_msg_data
1524 );
1525
1526 IF (g_fnd_debug = 'Y') THEN
1527 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1528 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1529 , G_MODULE_PREFIX || l_api_name || '.invoked'
1530 , 'Exit');
1531 END IF;
1532 END IF;
1533
1534 EXCEPTION
1535 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1536 FND_MSG_PUB.Count_And_Get
1537 ( p_count => x_msg_count
1538 , p_data => x_msg_data
1539 );
1540
1541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1542 IF (g_fnd_debug = 'Y')
1543 THEN
1544 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1545 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1546 , G_MODULE_PREFIX || l_api_name || '.unexpected_exception'
1547 , 'Exception');
1548 END IF;
1549 END IF;
1550 WHEN OTHERS THEN
1551 FND_MSG_PUB.Count_And_Get
1552 ( p_count => x_msg_count
1553 , p_data => x_msg_data
1554 );
1555
1556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557 IF (g_fnd_debug = 'Y')
1558 THEN
1559 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1560 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
1561 , G_MODULE_PREFIX || l_api_name || '.others_exception'
1562 , 'Exception');
1563 END IF;
1564 END IF;
1565
1566 END Consigned_Status_Affected;
1567
1568 -- <ACHTML R12 START>
1569 -------------------------------------------------------------------------------
1570 --Start of Comments
1571 --Name: get_consigned_flag
1572 --Pre-reqs:
1573 -- None
1574 --Modifies:
1575 -- None
1576 --Locks:
1577 -- None
1578 --Function:
1579 -- Gets the consigned flag for the shipment based on the operating unit, item,
1580 -- supplier, and site. If either the operating unit or item passed in is null,
1581 -- the consigned flag returned is 'N'.
1582 --Parameters:
1583 --IN:
1584 --p_org_id
1585 -- The operating unit ID.
1586 --p_item_id
1587 -- The item ID.
1588 --p_supplier_id
1589 -- The supplier ID.
1590 --p_site_id
1591 -- The site ID.
1592 --RETURNS:
1593 -- The 'Y' or 'N' consigned flag for the shipment.
1594 --Notes:
1595 -- None
1596 --Testing:
1597 -- None
1598 --End of Comments
1599 -------------------------------------------------------------------------------
1600 FUNCTION get_consigned_flag(
1601 p_org_id IN NUMBER,
1602 p_item_id IN NUMBER,
1603 p_supplier_id IN NUMBER,
1604 p_site_id IN NUMBER,
1605 p_inv_org_id IN NUMBER --Bug 5976612 Added this new parameter.
1606 ) RETURN VARCHAR2
1607 IS
1608 d_mod CONSTANT VARCHAR2(100) := D_get_consigned_flag;
1609 d_position NUMBER := 0;
1610
1611 l_consigned_flag PO_ASL_ATTRIBUTES.consigned_from_supplier_flag%TYPE;
1612 l_msg_data VARCHAR2(2000);
1613 l_return_status VARCHAR2(1);
1614 l_msg_count NUMBER;
1615 l_asset_flag MTL_SYSTEM_ITEMS_B.inventory_asset_flag%TYPE;
1616 l_enable_vmi_flag PO_ASL_ATTRIBUTES.enable_vmi_flag%TYPE;
1617 l_last_billing_date PO_ASL_ATTRIBUTES.last_billing_date%TYPE;
1618 l_cons_billing_cycle PO_ASL_ATTRIBUTES.consigned_billing_cycle%TYPE;
1619 -- <BUG 4951605>
1620 l_inventory_org_id
1621 FINANCIALS_SYSTEM_PARAMS_ALL.inventory_organization_id%TYPE;
1622
1623 e_invalid_asset_flag EXCEPTION;
1624 e_invalid_consigned_flag EXCEPTION;
1625 BEGIN
1626 IF (PO_LOG.d_proc) THEN
1627 PO_LOG.proc_begin(d_mod,'p_org_id',p_org_id);
1628 PO_LOG.proc_begin(d_mod,'p_item_id',p_item_id);
1629 PO_LOG.proc_begin(d_mod,'p_supplier_id',p_supplier_id);
1630 PO_LOG.proc_begin(d_mod,'p_site_id',p_site_id);
1631 END IF;
1632
1633 -- When either item ID or org ID is null, don't call get_item_inv_asset_flag
1634 -- because it will error out. Instead, just set consigned flag to 'N' and
1635 -- return that value.
1636 IF (p_item_id IS NULL OR (p_org_id IS NULL AND p_inv_org_id IS NULL)) -- Bug 5976612 Added the 'p_inv_org_id' check
1637 THEN
1638 l_consigned_flag := 'N';
1639
1640 IF (PO_LOG.d_proc) THEN
1641 PO_LOG.proc_return(d_mod, l_consigned_flag);
1642 END IF;
1643
1644 RETURN l_consigned_flag;
1645 END IF;
1646
1647 /* Bug 5976612
1648 1.) Check if p_org_id is NOT NULL, If YES, then run the below query to fetch the inv org id
1649 to get the inv asset flag
1650 2.) Check if p_inv_org_id is NOT NULL, if YES, then don't run the query. Take the value of p_inv_org_id into
1651 l_inventory_org_id
1652 */
1653
1654 IF p_inv_org_id IS NULL
1655 THEN
1656
1657 -- <BUG 4951605 START>
1658 -- Get the inventory org ID corresponding to the purchasing org ID.
1659 SELECT inventory_organization_id
1660 INTO l_inventory_org_id
1661 FROM financials_system_params_all
1662 WHERE org_id = p_org_id;
1663 -- <BUG 4951605 END>
1664
1665 ELSE
1666 l_inventory_org_id :=p_inv_org_id;
1667 END IF; --Bug 5976612 end
1668
1669
1670
1671
1672 PO_THIRD_PARTY_STOCK_GRP.get_item_inv_asset_flag(
1673 p_api_version => 1.0,
1674 p_init_msg_list => NULL,
1675 x_return_status => l_return_status, -- OUT
1676 x_msg_count => l_msg_count, -- OUT
1677 x_msg_data => l_msg_data, -- OUT
1678 p_organization_id => l_inventory_org_id, -- IN <BUG 4951605>
1679 p_inventory_item_id => p_item_id, -- IN
1680 x_inventory_asset_flag => l_asset_flag -- OUT
1681 );
1682
1683 d_position := 10;
1684
1685 IF PO_LOG.d_stmt THEN
1686 PO_LOG.stmt(d_mod,d_position,'asset flag is: ' || l_asset_flag || ' and l_return_status is: ' || l_return_status);
1687 END IF;
1688
1689 d_position := 20;
1690
1691 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1692 THEN
1693 RAISE e_invalid_asset_flag;
1694 END IF;
1695
1696 d_position := 30;
1697
1698 -- Set the consigned setting on the ASL as 'N' if the item is an
1699 -- expense item
1700 IF (NVL(l_asset_flag, 'N') = 'N')
1701 THEN
1702 l_consigned_flag := 'N';
1703
1704 IF (PO_LOG.d_proc) THEN
1705 PO_LOG.proc_return(d_mod, l_consigned_flag);
1706 END IF;
1707
1708 RETURN l_consigned_flag;
1709 END IF;
1710
1711 d_position := 40;
1712
1713 IF PO_LOG.d_stmt THEN
1714 PO_LOG.stmt(d_mod,d_position,'consigned flag before call to get_asl_attributes is: ' || l_consigned_flag);
1715 END IF;
1716
1717 PO_THIRD_PARTY_STOCK_GRP.get_asl_attributes(
1718 p_api_version => 1.0,
1719 p_init_msg_list => NULL,
1720 x_return_status => l_return_status, -- OUT
1721 x_msg_count => l_msg_count, -- OUT
1722 x_msg_data => l_msg_data, -- OUT
1723 p_inventory_item_id => p_item_id, -- IN
1724 p_vendor_id => p_supplier_id, -- IN
1725 p_vendor_site_id => p_site_id, -- IN
1726 p_using_organization_id => l_inventory_org_id, -- IN <BUG 4951605>
1727 x_consigned_from_supplier_flag => l_consigned_flag, -- OUT
1728 x_enable_vmi_flag => l_enable_vmi_flag, -- OUT
1729 x_last_billing_date => l_last_billing_date, -- OUT
1730 x_consigned_billing_cycle => l_cons_billing_cycle -- OUT
1731 );
1732
1733 d_position := 50;
1734
1735 IF PO_LOG.d_stmt THEN
1736 PO_LOG.stmt(d_mod,d_position,'consigned flag after call to get_asl_attributes is: ' || l_consigned_flag || ' and l_return_status is: ' || l_return_status);
1737 END IF;
1738
1739 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1740 THEN
1741 RAISE e_invalid_consigned_flag;
1742 END IF;
1743
1744 l_consigned_flag := NVL(l_consigned_flag, 'N');
1745
1746 IF (PO_LOG.d_proc) THEN
1747 PO_LOG.proc_return(d_mod, l_consigned_flag);
1748 END IF;
1749
1750 RETURN l_consigned_flag;
1751 EXCEPTION
1752 WHEN e_invalid_asset_flag THEN
1753 IF (PO_LOG.d_exc) THEN
1754 PO_LOG.exc(d_mod, d_position, 'An error occured in get_consigned_flag: e_invalid_asset_flag');
1755 END IF;
1756
1757 RAISE;
1758 WHEN e_invalid_consigned_flag THEN
1759 IF (PO_LOG.d_exc) THEN
1760 PO_LOG.exc(d_mod, d_position, 'An error occured in get_consigned_flag: e_invalid_consigned_flag');
1761 END IF;
1762
1763 RAISE;
1764 WHEN OTHERS THEN
1765 IF (PO_LOG.d_exc) THEN
1766 PO_LOG.exc(d_mod, d_position, 'An error occured in get_consigned_flag');
1767 END IF;
1768
1769 RAISE;
1770 END get_consigned_flag;
1771 -- <ACHTML R12 END>
1772 --<bug#4939669 START>
1773 -----------------------------------------------------------------------------
1774 --Start of Comments
1775 --Name: IS_ASL_CONSIGNED_FROM_SUPPLIER
1776 --Pre-reqs:
1777 --Modifies:
1778 --Locks:
1779 --Function:
1780 -- Checks if any of the given destination organizations has an asl
1781 -- for a given item with the consigned_from_supplier flag set to Y
1782 --Parameters:
1783 --IN:
1784 --p_use_ship_to_org_ids
1785 -- Table of destination organization_ids
1786 --p_item_id
1787 -- Inventory item id of the item whose ASL would be scanned in the list
1788 -- of destination organizations
1789 --p_vendor_id
1790 -- VendorId for which we need to find the ASL
1791 --p_vendor_site_id
1792 -- Vendor Site Id for which we need to find the ASL
1793 --OUT:
1794 --x_consigned_from_supplier_flag
1795 -- Holds Y or N depending on wether any Destination Org has the
1796 -- consigned_from_supplier flag set to Y or not
1797 --Notes:
1798 --End of Comments
1799 -----------------------------------------------------------------------------
1800
1801 PROCEDURE IS_ASL_CONSIGNED_FROM_SUPPLIER(p_use_ship_to_org_ids IN PO_TBL_NUMBER,
1802 p_item_id IN NUMBER,
1803 p_vendor_id IN NUMBER,
1804 p_vendor_site_id IN NUMBER,
1805 x_consigned_from_supplier_flag OUT NOCOPY VARCHAR2)IS
1806 l_return_status varchar2(1) := NULL;
1807 l_msg_count number := NULL;
1808 l_msg_data varchar2(2000) := NULL;
1809 l_item_inv_asset_flag mtl_system_items_b.inventory_asset_flag%TYPE := 'N';
1810 l_enable_vmi_flag po_asl_attributes.enable_vmi_flag%TYPE := NULL;
1811 l_last_billing_date date := NULL;
1812 l_consigned_billing_cycle number := NULL;
1813 l_module_name CONSTANT VARCHAR2(100) := 'IS_ASL_CONSIGNED_FROM_SUPPLIER';
1814 d_module_base CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base( D_PACKAGE_BASE, l_module_name);
1815 d_progress NUMBER;
1816 BEGIN
1817 IF (PO_LOG.d_proc) THEN
1818 PO_LOG.proc_begin(d_module_base);
1819 PO_LOG.proc_begin(d_module_base, 'p_item_id', p_item_id);
1820 PO_LOG.proc_begin(d_module_base, 'p_vendor_id', p_vendor_id);
1821 PO_LOG.proc_begin(d_module_base, 'p_vendor_site_id', p_vendor_site_id);
1822 IF(p_use_ship_to_org_ids <> null)THEN
1823 FOR i in 1..p_use_ship_to_org_ids.count LOOP
1824 PO_LOG.proc_begin(d_module_base, 'p_use_ship_to_org_ids('||i||')', p_use_ship_to_org_ids(i));
1825 END LOOP;
1826 END IF;
1827 END IF;
1828 d_progress :=10;
1829 FOR i in 1..p_use_ship_to_org_ids.count LOOP
1830 IF (p_use_ship_to_org_ids(i) IS NOT NULL AND p_item_id IS NOT NULL AND
1831 p_vendor_id is not null AND p_vendor_site_id is not null) THEN
1832 d_progress :=20;
1833 PO_THIRD_PARTY_STOCK_GRP.Get_Item_Inv_Asset_Flag(p_api_version => 1.0,
1834 p_init_msg_list => NULL,
1835 x_return_status => l_return_status,
1836 x_msg_count => l_msg_count,
1837 x_msg_data => l_msg_data,
1838 p_organization_id => p_use_ship_to_org_ids(i),
1839 p_inventory_item_id => p_item_id,
1840 x_inventory_asset_flag => l_item_inv_asset_flag);
1841 IF PO_LOG.d_stmt THEN
1842 PO_LOG.stmt(d_module_base,d_progress,'l_item_inv_asset_flag('||i||')= '||l_item_inv_asset_flag);
1843 PO_LOG.stmt(d_module_base,d_progress,'x_return_status('||i||')= '||l_return_status);
1844 END IF;
1845 END IF;
1846 -- if the item is not an expense item
1847 d_progress :=20;
1848 IF (l_item_inv_asset_flag = 'Y') THEN
1849 PO_THIRD_PARTY_STOCK_GRP.get_asl_attributes(p_api_version => 1.0,
1850 p_init_msg_list => NULL,
1851 x_return_status => l_return_status,
1852 x_msg_count => l_msg_count,
1853 x_msg_data => l_msg_data,
1854 p_inventory_item_id => p_item_id,
1855 p_vendor_id => p_vendor_id,
1856 p_vendor_site_id => p_vendor_site_id,
1857 p_using_organization_id => p_use_ship_to_org_ids(i),
1858 x_consigned_from_supplier_flag => x_consigned_from_supplier_flag,
1859 x_enable_vmi_flag => l_enable_vmi_flag,
1860 x_last_billing_date => l_last_billing_date,
1861 x_consigned_billing_cycle => l_consigned_billing_cycle);
1862 IF PO_LOG.d_stmt THEN
1863 PO_LOG.stmt(d_module_base,d_progress,'x_consigned_from_supplier_flag=('||i||')= '||x_consigned_from_supplier_flag);
1864 END IF;
1865
1866 IF(x_consigned_from_supplier_flag='Y')THEN
1867 RETURN;
1868 END IF;
1869
1870 END IF;
1871 END LOOP;
1872
1873 IF (PO_LOG.d_proc) THEN
1874 PO_LOG.proc_end(d_module_base);
1875 END IF;
1876 EXCEPTION
1877 WHEN OTHERS THEN
1878 IF (PO_LOG.d_exc) THEN
1879 PO_LOG.exc(d_module_base, d_progress, SQLCODE || SQLERRM);
1880 END IF;
1881 RAISE;
1882 END;
1883 --<bug#4939669 END>
1884 END PO_THIRD_PARTY_STOCK_GRP;
[Home] [Help]