[Home] [Help]
PACKAGE BODY: APPS.INV_PO_ITEMVALID_MDTR
Source
1 PACKAGE BODY INV_PO_ITEMVALID_MDTR AS
2 -- $Header: INVMPO1B.pls 120.0 2005/05/25 05:16:24 appldev noship $ --
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
90 BEGIN
91
92 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
93 -- IF (g_fnd_debug = 'Y') THEN
94 IF (g_fnd_debug = 'Y'and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
95 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
96 , G_MODULE_PREFIX || l_api_name || '.invoked'
97 , 'Entry');
98 END IF;
99
100 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
101 FND_MSG_PUB.initialize;
102 END IF;
103
104 IF NOT FND_API.Compatible_API_Call( l_api_version
105 , p_api_version
106 , l_api_name
107 , G_PKG_NAME
108 )
109 THEN
110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
111 END IF;
112
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114 -- End API initialization
115
116 SELECT 'Y'
117 INTO l_vmiorconsign_flag
118 FROM po_approved_supplier_list pasl,
119 po_asl_attributes paa,
120 po_asl_status_rules pasr
121 WHERE pasl.item_id = p_item_id
122 AND pasl.using_organization_id IN (-1,p_organization_id)
123 AND pasl.asl_id = paa.asl_id
124 AND pasr.business_rule = '2_SOURCING'
125 AND pasr.allow_action_flag = 'Y'
126 AND pasr.status_id = pasl.asl_status_id
127 AND (disable_flag IS NULL OR disable_flag = 'N')
128 AND paa.using_organization_id = (SELECT max(paa2.using_organization_id)
129 FROM po_asl_attributes paa2
130 WHERE paa2.asl_id = pasl.asl_id
131 AND paa2.using_organization_id IN (-1,p_organization_id))
132 AND (paa.consigned_from_supplier_flag='Y' OR paa.enable_vmi_flag='Y')
133 AND rownum=1;
134
135 x_vmiorconsign_flag := l_vmiorconsign_flag;
136 FND_MSG_PUB.Count_And_Get
137 ( p_count => x_msg_count,
138 p_data => x_msg_data
139 );
140 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
141 -- IF (g_fnd_debug = 'Y') THEN
142 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
143 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
144 , G_MODULE_PREFIX || l_api_name || '.invoked'
145 , 'Exit');
146 END IF;
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 x_vmiorconsign_flag := 'N';
150 x_return_status := FND_API.G_RET_STS_ERROR;
151 WHEN FND_API.G_EXC_ERROR THEN
152 FND_MSG_PUB.Count_And_Get
153 ( p_count => x_msg_count
154 , p_data => x_msg_data
155 );
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
158 FND_MSG_PUB.Count_And_Get
159 ( p_count => x_msg_count
160 , p_data => x_msg_data
161 );
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
163 WHEN OTHERS THEN
164 FND_MSG_PUB.Count_And_Get
165 ( p_count => x_msg_count
166 , p_data => x_msg_data
167 );
168
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
171 -- IF (g_fnd_debug = 'Y')
172 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
173 THEN
174 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
175 , G_MODULE_PREFIX || l_api_name || '.others_exception'
176 , 'Exception');
177 END IF;
178 END Check_VmiOrConsign_Enabled;
179
180 ---=========================================================================
181 -- PROCEDURE : Check_Consign_Enabled
182 -- PARAMETERS:
183 -- p_api_version REQUIRED. API version
184 -- p_init_msg_list REQUIRED. FND_API.G_TRUE to reset the message list
185 -- FND_API.G_FALSE to not reset it.
186 -- If pass NULL, it means FND_API.G_FALSE.
187 -- x_return_status REQUIRED. Value can be
188 -- FND_API.G_RET_STS_SUCCESS
189 -- FND_API.G_RET_STS_ERROR
190 -- FND_API.G_RET_STS_UNEXP_ERROR
191 -- x_msg_count REQUIRED. Number of messages on the message list
192 -- x_msg_data REQUIRED. Return message data if message count is 1
193 -- p_item_id REQUIRED. Inventory Item Id
194 -- p_organization_id REQUIRED. Inventory organization ID
195 -- x_vmiorconsign_flag REQUIRED. Vmi or consigned enabled flag
196 -- 'Y' indicates a valid vmi or cosigned
197 -- inventory exists
198 -- 'N' indicates no valid vmi or consigned
199 -- COMMENT : This procedure is called by Items form and Item open interface
200 -- to decide whether there exist a valid vmi or consigned
201 -- for a particular item/organization combination
202 --=========================================================================
203
204 PROCEDURE Check_Consign_Enabled
205 ( p_api_version IN NUMBER
206 , p_init_msg_list IN VARCHAR2
207 , x_return_status OUT NOCOPY VARCHAR2
208 , x_msg_count OUT NOCOPY NUMBER
209 , x_msg_data OUT NOCOPY VARCHAR2
210 , p_item_id IN NUMBER
211 , p_organization_id IN NUMBER
212 , x_consign_flag OUT NOCOPY VARCHAR2
213 )
214 IS
215
216 l_api_name CONSTANT VARCHAR2(30) := 'Check_Consign_Enabled';
217 l_api_version CONSTANT NUMBER := 1.0;
218 l_consign_flag VARCHAR2(1) := 'N';
219 l_profile VARCHAR2(1);
220
221 BEGIN
222 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
223 -- IF (g_fnd_debug = 'Y') THEN
224 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
225 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
226 , G_MODULE_PREFIX || l_api_name || '.invoked'
227 , 'Entry');
228 END IF;
229
230 IF FND_API.to_boolean(NVL(p_init_msg_list, FND_API.G_FALSE)) THEN
231 FND_MSG_PUB.initialize;
232 END IF;
233
234 IF NOT FND_API.Compatible_API_Call( l_api_version
235 , p_api_version
236 , l_api_name
237 , G_PKG_NAME
238 )
239 THEN
240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
241 END IF;
242
243 x_return_status := FND_API.G_RET_STS_SUCCESS;
244
245 IF fnd_profile.value('AP_SUPPLIER_CONSIGNED_ENABLED') <> 'Y' THEN
246 x_consign_flag := 'N';
247 ELSE
248
249 SELECT 'Y'
250 INTO l_consign_flag
251 FROM po_approved_supplier_list pasl,
252 po_asl_attributes paa,
253 po_asl_status_rules pasr
254 WHERE pasl.item_id = p_item_id
255 AND pasl.using_organization_id IN (-1,p_organization_id)
256 AND pasl.asl_id = paa.asl_id
257 AND pasr.business_rule = '2_SOURCING'
258 AND pasr.allow_action_flag = 'Y'
259 AND pasr.status_id = pasl.asl_status_id
260 AND (disable_flag IS NULL OR disable_flag = 'N')
261 AND paa.using_organization_id = (SELECT max(paa2.using_organization_id)
262 FROM po_asl_attributes paa2
263 WHERE paa2.asl_id = pasl.asl_id
264 AND paa2.using_organization_id IN (-1,p_organization_id))
265 AND paa.consigned_from_supplier_flag='Y'
266 AND rownum=1;
267
268 x_consign_flag := l_consign_flag;
269 END IF;
270
271 FND_MSG_PUB.Count_And_Get
272 ( p_count => x_msg_count,
273 p_data => x_msg_data
274 );
275 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
276 -- IF (g_fnd_debug = 'Y') THEN
277 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
278 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
279 , G_MODULE_PREFIX || l_api_name || '.invoked'
280 , 'Exit');
281 END IF;
282 EXCEPTION
283 WHEN NO_DATA_FOUND THEN
284 x_consign_flag := 'N';
285 x_return_status := FND_API.G_RET_STS_ERROR;
286 WHEN FND_API.G_EXC_ERROR THEN
287 FND_MSG_PUB.Count_And_Get
288 ( p_count => x_msg_count
289 , p_data => x_msg_data
290 );
291 x_return_status := FND_API.G_RET_STS_ERROR;
292 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
293 FND_MSG_PUB.Count_And_Get
294 ( p_count => x_msg_count
295 , p_data => x_msg_data
296 );
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 WHEN OTHERS THEN
299 FND_MSG_PUB.Count_And_Get
300 ( p_count => x_msg_count
301 , p_data => x_msg_data
302 );
303
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
305 --myerrams, changed the following code to comply with File.Sql.46 GSCC standard, Bug: 4202824
306 -- IF (g_fnd_debug = 'Y')
307 IF (g_fnd_debug = 'Y' and FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
308 THEN
309 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED
310 , G_MODULE_PREFIX || l_api_name || '.others_exception'
311 , 'Exception');
312 END IF;
313 END Check_Consign_Enabled;
314
315 END INV_PO_ITEMVALID_MDTR;
316