[Home] [Help]
PACKAGE BODY: APPS.INV_PO_ITEMVALID_MDTR
Source
1 PACKAGE BODY INV_PO_ITEMVALID_MDTR AS
2 -- $Header: INVMPO1B.pls 120.1 2011/04/15 14:50:44 qyou ship $ --
3 --+===========================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+===========================================================================+
8 --| FILENAME |
9 --| INVMPO1S.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Check the VMI/Consigned Enabled for a given item/organization |
13 --| combinations. |
14 --| This mediator package is used to access PO objects from |
15 --| INV product. |
16 --| |
17 --| PROCEDURES: |
18 --| Check_VmiOrConsign_Enabled |
19 --| Check_Consign_Enabled |
20 --| |
21 --| FUNCTIONS: |
22 --| |
23 --| HISTORY |
24 --| 2003/21/07 dpenmats Created. |
25 --| 2005/07/03 myerrams Updated the file to comply with File.Sql.46 |
26 --| GSCC Standard, Bug No: 4202824 |
27 --| |
28 --+===========================================================================+
29
30 --=============================================================================
31 -- TYPE DECLARATIONS
32 --=============================================================================
33
34 --=============================================================================
35 -- CONSTANTS
36 --=============================================================================
37
38 G_MODULE_PREFIX CONSTANT VARCHAR2(50) := 'INV.plsql.'||G_PKG_NAME || '.';
39
40 --=============================================================================
41 -- GLOBAL VARIABLES
42 --=============================================================================
43
44 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
45
46 --=============================================================================
47 -- PROCEDURES AND FUNCTIONS
48 --=============================================================================
49
50 ---=========================================================================
51 -- PROCEDURE : Check_VmiOrConsign_Enabled
52 -- PARAMETERS:
53 -- p_api_version REQUIRED. API version
54 -- p_init_msg_list REQUIRED. FND_API.G_TRUE to reset the message list
55 -- FND_API.G_FALSE to not reset it.
56 -- If pass NULL, it means FND_API.G_FALSE.
57 -- x_return_status REQUIRED. Value can be
58 -- FND_API.G_RET_STS_SUCCESS
59 -- FND_API.G_RET_STS_ERROR
60 -- FND_API.G_RET_STS_UNEXP_ERROR
61 -- x_msg_count REQUIRED. Number of messages on the message list
62 -- x_msg_data REQUIRED. Return message data if message count is 1
63 -- p_item_id REQUIRED. Inventory Item Id
64 -- p_organization_id REQUIRED. Inventory organization ID
65 -- x_vmiorconsign_flag REQUIRED. Vmi or consigned enabled flag
66 -- 'Y' indicates a valid vmi or cosigned
67 -- inventory exists
68 -- 'N' indicates no valid vmi or consigned
69 -- COMMENT : This procedure is called by Items form and Item open interface
70 -- to decide whether there exist a valid vmi or consigned
71 -- for a particular item/organization combination
72 --=========================================================================
73
74 PROCEDURE Check_VmiOrConsign_Enabled
75 ( p_api_version IN NUMBER
76 , p_init_msg_list IN VARCHAR2
77 , x_return_status OUT NOCOPY VARCHAR2
78 , x_msg_count OUT NOCOPY NUMBER
79 , x_msg_data OUT NOCOPY VARCHAR2
80 , p_item_id IN NUMBER
81 , p_organization_id IN NUMBER
82 , x_vmiorconsign_flag OUT NOCOPY VARCHAR2
83 )
84 IS
85
86 l_api_name CONSTANT VARCHAR2(30) := 'Check_VmiOrConsign_Enabled';
87 l_api_version CONSTANT NUMBER := 1.0;
88 --l_vmiorconsign_flag VARCHAR2(1) := 'N';
89 l_consign_Flag VARCHAR2(1) := 'N'; -- bug 12359866
90 l_vmi_Flag VARCHAR2(1) := 'N'; -- bug 12359866
91
92 BEGIN
93
94 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
95 -- IF (g_fnd_debug = 'Y') THEN
96 IF (g_fnd_debug = 'Y'and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
97 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
98 , G_MODULE_PREFIX || l_api_name || '.invoked'
99 , 'Entry');
100 END IF;
101
102 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
103 FND_MSG_PUB.initialize;
104 END IF;
105
106 IF NOT FND_API.Compatible_API_Call( l_api_version
107 , p_api_version
108 , l_api_name
109 , G_PKG_NAME
110 )
111 THEN
112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
113 END IF;
114
115 x_return_status := FND_API.G_RET_STS_SUCCESS;
116 -- End API initialization
117
118 -- bug 12359866, replace query with new API
119 PO_AUTOSOURCE_SV.get_VmiOrConsignEnabled_info(
120 p_item_id => p_item_id,
121 p_organization_id => p_organization_id,
122 x_VmiEnabled_flag => l_vmi_Flag,
123 x_consignEnabled_flag => l_consign_Flag
124 );
125 /*
126 SELECT 'Y'
127 INTO l_vmiorconsign_flag
128 FROM po_approved_supplier_list pasl,
129 po_asl_attributes paa,
130 po_asl_status_rules pasr
131 WHERE pasl.item_id = p_item_id
132 AND pasl.using_organization_id IN (-1,p_organization_id)
133 AND pasl.asl_id = paa.asl_id
134 AND pasr.business_rule = '2_SOURCING'
135 AND pasr.allow_action_flag = 'Y'
136 AND pasr.status_id = pasl.asl_status_id
137 AND (disable_flag IS NULL OR disable_flag = 'N')
138 AND paa.using_organization_id = (SELECT max(paa2.using_organization_id)
139 FROM po_asl_attributes paa2
140 WHERE paa2.asl_id = pasl.asl_id
141 AND paa2.using_organization_id IN (-1,p_organization_id))
142 AND (paa.consigned_from_supplier_flag='Y' OR paa.enable_vmi_flag='Y')
143 AND rownum=1;
144
145 x_vmiorconsign_flag := l_vmiorconsign_flag;
146 */
147
148 if (l_vmi_Flag = 'Y' and l_consign_Flag = 'Y' ) then
149 x_vmiorconsign_flag := 'Y';
150 end if;
151
152 FND_MSG_PUB.Count_And_Get
153 ( p_count => x_msg_count,
154 p_data => x_msg_data
155 );
156 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
157 -- IF (g_fnd_debug = 'Y') THEN
158 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
159 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
160 , G_MODULE_PREFIX || l_api_name || '.invoked'
161 , 'Exit');
162 END IF;
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN
165 x_vmiorconsign_flag := 'N';
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 WHEN FND_API.G_EXC_ERROR THEN
168 FND_MSG_PUB.Count_And_Get
169 ( p_count => x_msg_count
170 , p_data => x_msg_data
171 );
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
174 FND_MSG_PUB.Count_And_Get
175 ( p_count => x_msg_count
176 , p_data => x_msg_data
177 );
178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
179 WHEN OTHERS THEN
180 FND_MSG_PUB.Count_And_Get
181 ( p_count => x_msg_count
182 , p_data => x_msg_data
183 );
184
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
186 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
187 -- IF (g_fnd_debug = 'Y')
188 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
189 THEN
190 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
191 , G_MODULE_PREFIX || l_api_name || '.others_exception'
192 , 'Exception');
193 END IF;
194 END Check_VmiOrConsign_Enabled;
195
196 ---=========================================================================
197 -- PROCEDURE : Check_Consign_Enabled
198 -- PARAMETERS:
199 -- p_api_version REQUIRED. API version
200 -- p_init_msg_list REQUIRED. FND_API.G_TRUE to reset the message list
201 -- FND_API.G_FALSE to not reset it.
202 -- If pass NULL, it means FND_API.G_FALSE.
203 -- x_return_status REQUIRED. Value can be
204 -- FND_API.G_RET_STS_SUCCESS
205 -- FND_API.G_RET_STS_ERROR
206 -- FND_API.G_RET_STS_UNEXP_ERROR
207 -- x_msg_count REQUIRED. Number of messages on the message list
208 -- x_msg_data REQUIRED. Return message data if message count is 1
209 -- p_item_id REQUIRED. Inventory Item Id
210 -- p_organization_id REQUIRED. Inventory organization ID
211 -- x_vmiorconsign_flag REQUIRED. Vmi or consigned enabled flag
212 -- 'Y' indicates a valid vmi or cosigned
213 -- inventory exists
214 -- 'N' indicates no valid vmi or consigned
215 -- COMMENT : This procedure is called by Items form and Item open interface
216 -- to decide whether there exist a valid vmi or consigned
217 -- for a particular item/organization combination
218 --=========================================================================
219
220 PROCEDURE Check_Consign_Enabled
221 ( p_api_version IN NUMBER
222 , p_init_msg_list IN VARCHAR2
223 , x_return_status OUT NOCOPY VARCHAR2
224 , x_msg_count OUT NOCOPY NUMBER
225 , x_msg_data OUT NOCOPY VARCHAR2
226 , p_item_id IN NUMBER
227 , p_organization_id IN NUMBER
228 , x_consign_flag OUT NOCOPY VARCHAR2
229 )
230 IS
231
232 l_api_name CONSTANT VARCHAR2(30) := 'Check_Consign_Enabled';
233 l_api_version CONSTANT NUMBER := 1.0;
234 l_consign_Flag VARCHAR2(1) := 'N';
235 l_profile VARCHAR2(1);
236 l_vmi_Flag VARCHAR2(1) := 'N'; -- bug 12359866
237
238 BEGIN
239 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
240 -- IF (g_fnd_debug = 'Y') THEN
241 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
242 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
243 , G_MODULE_PREFIX || l_api_name || '.invoked'
244 , 'Entry');
245 END IF;
246
247 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
248 FND_MSG_PUB.initialize;
249 END IF;
250
251 IF NOT FND_API.Compatible_API_Call( l_api_version
252 , p_api_version
253 , l_api_name
254 , G_PKG_NAME
255 )
256 THEN
257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
258 END IF;
259
260 x_return_status := FND_API.G_RET_STS_SUCCESS;
261
262 IF fnd_profile.value('AP_SUPPLIER_CONSIGNED_ENABLED') <> 'Y' THEN
263 x_consign_flag := 'N';
264 ELSE
265 -- bug 12359866, replace query with new API
266 PO_AUTOSOURCE_SV.get_VmiOrConsignEnabled_info(
267 p_item_id => p_item_id,
268 p_organization_id => p_organization_id,
269 x_VmiEnabled_flag => l_vmi_Flag,
270 x_consignEnabled_flag => l_consign_Flag
271 );
272 /*
273 SELECT 'Y'
274 INTO l_consign_flag
275 FROM po_approved_supplier_list pasl,
276 po_asl_attributes paa,
277 po_asl_status_rules pasr
278 WHERE pasl.item_id = p_item_id
279 AND pasl.using_organization_id IN (-1,p_organization_id)
280 AND pasl.asl_id = paa.asl_id
281 AND pasr.business_rule = '2_SOURCING'
282 AND pasr.allow_action_flag = 'Y'
283 AND pasr.status_id = pasl.asl_status_id
284 AND (disable_flag IS NULL OR disable_flag = 'N')
285 AND paa.using_organization_id = (SELECT max(paa2.using_organization_id)
286 FROM po_asl_attributes paa2
287 WHERE paa2.asl_id = pasl.asl_id
288 AND paa2.using_organization_id IN (-1,p_organization_id))
289 AND paa.consigned_from_supplier_flag='Y'
290 AND rownum=1;
291 */
292
293 x_consign_flag := l_consign_flag;
294 END IF;
295
296 FND_MSG_PUB.Count_And_Get
297 ( p_count => x_msg_count,
298 p_data => x_msg_data
299 );
300 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
301 -- IF (g_fnd_debug = 'Y') THEN
302 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
303 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
304 , G_MODULE_PREFIX || l_api_name || '.invoked'
305 , 'Exit');
306 END IF;
307 EXCEPTION
308 WHEN NO_DATA_FOUND THEN
309 x_consign_flag := 'N';
310 x_return_status := FND_API.G_RET_STS_ERROR;
311 WHEN FND_API.G_EXC_ERROR THEN
312 FND_MSG_PUB.Count_And_Get
313 ( p_count => x_msg_count
314 , p_data => x_msg_data
315 );
316 x_return_status := FND_API.G_RET_STS_ERROR;
317 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
318 FND_MSG_PUB.Count_And_Get
319 ( p_count => x_msg_count
323 WHEN OTHERS THEN
320 , p_data => x_msg_data
321 );
322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
324 FND_MSG_PUB.Count_And_Get
325 ( p_count => x_msg_count
326 , p_data => x_msg_data
327 );
328
329 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
330 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
331 -- IF (g_fnd_debug = 'Y')
332 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
333 THEN
334 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
335 , G_MODULE_PREFIX || l_api_name || '.others_exception'
336 , 'Exception');
337 END IF;
338 END Check_Consign_Enabled;
339
340 END INV_PO_ITEMVALID_MDTR;
341