[Home] [Help]
PACKAGE BODY: APPS.CSP_PICK_SL_UTIL
Source
1 PACKAGE BODY CSP_Pick_SL_Util AS
2 /*$Header: cspgtslb.pls 120.0 2005/05/25 11:31:02 appldev noship $*/
3 -- Start of Comments
4 -- Package name : CSP_Pick_SL_Util
5 -- Purpose : A wrapper to prepare data to call the update, delete and insert procedures of the
6 -- csp_pick_serial_lots_PVT.
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- --------- ------ ------------------------------------------
10 -- klou 01/28/00 Created.
11 --
12 -- NOTES: If validations have been done in the precedent procedure from which this one is being called, doing a
13 -- full validation here is unnecessary. To avoid repeating the same validations, you can set the
14 -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
15 -- responsibility to make sure all proper validations have been done before calling this procedure.
16 -- You are recommended to let this procedure handle the validations if you are not sure.
17 --
18 -- NOTES: This procedure does not consider the fnd_api.g_miss_num and fnd_api.g_miss_char.
19 --
20 -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
21 -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
22 -- to FND_API.G_VALID_LEVEL_FULL when making the call.
23 --
24 -- End of Comments
25
26 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_Pick_SL_Util';
27 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgtslb.pls';
28 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
29 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
30
31 PROCEDURE Validate_And_Write (
32 P_Api_Version_Number IN NUMBER,
33 P_Init_Msg_List IN VARCHAR2 := FND_API.G_TRUE,
34 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
35 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
36 p_action_code IN NUMBER,
37 px_PICKLIST_SERIAL_LOT_ID IN OUT NOCOPY NUMBER,
38 p_CREATED_BY IN NUMBER,
39 p_CREATION_DATE IN DATE,
40 p_LAST_UPDATED_BY IN NUMBER,
41 p_LAST_UPDATE_DATE IN DATE,
42 p_LAST_UPDATE_LOGIN IN NUMBER,
43 p_PICKLIST_LINE_ID IN NUMBER,
44 p_ORGANIZATION_ID IN NUMBER,
45 p_INVENTORY_ITEM_ID IN NUMBER,
46 p_QUANTITY IN NUMBER,
47 p_LOT_NUMBER IN VARCHAR2,
48 p_SERIAL_NUMBER IN VARCHAR2,
49 X_Return_Status OUT NOCOPY VARCHAR2,
50 X_Msg_Count OUT NOCOPY NUMBER,
51 X_Msg_Data OUT NOCOPY VARCHAR2
52 )
53 IS
54 -- csp standard declarations
55 l_api_version_number CONSTANT NUMBER := 1.0;
56 l_api_name CONSTANT VARCHAR2(30) := 'Validate_And_Write';
57 l_msg_data VARCHAR2(300);
58 l_check_existence NUMBER := 0;
59 l_return_status VARCHAR2(1);
60 l_msg_count NUMBER := 0;
61 l_commit VARCHAR2(1) := FND_API.G_FALSE;
62 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
63 EXCP_USER_DEFINED EXCEPTION;
64
65 -- customers declarations
66 l_picklist_Serial_Lot_ID NUMBER := NULL;
67 l_psl_rec csp_pick_serial_lots_PVT.psl_Rec_Type;
68
69 BEGIN
70 SAVEPOINT Validate_And_Write_PUB;
71 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
72 -- initialize message list
73 FND_MSG_PUB.initialize;
74 END IF;
75
76 -- Standard call to check for call compatibility.
77 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
78 p_api_version_number,
79 l_api_name,
80 G_PKG_NAME)
81 THEN
82 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
83 END IF;
84
85 -- validate p_organization_id
86 IF p_organization_id IS NULL THEN
87 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
88 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
89 FND_MSG_PUB.ADD;
90 RAISE EXCP_USER_DEFINED;
91 ELSE
92 IF p_validation_level = fnd_api.g_valid_level_full THEN
93 BEGIN
94 select organization_id into l_check_existence
95 from mtl_parameters
96 where organization_id = p_organization_id;
97 EXCEPTION
98 WHEN NO_DATA_FOUND THEN
99 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
100 FND_MSG_PUB.ADD;
101 RAISE EXCP_USER_DEFINED;
102 WHEN OTHERS THEN
103 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
104 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
105 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
106 fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
107 FND_MSG_PUB.ADD;
108 RAISE EXCP_USER_DEFINED;
109 END;
110 END IF;
111 NULL;
112 END IF;
113
114
115 IF p_action_code IN (0, 1) THEN
116 IF p_validation_level = fnd_api.g_valid_level_full THEN
117 -- valide packlist_line_id
118 IF p_picklist_line_id IS NULL THEN
119 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
120 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_packlist_line_id', TRUE);
121 FND_MSG_PUB.ADD;
122 RAISE EXCP_USER_DEFINED;
123 END IF;
124 BEGIN
125 SELECT picklist_line_id INTO l_check_existence
126 FROM csp_picklist_lines
127 WHERE picklist_line_id = p_picklist_line_id;
128
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 -- the following error message needs to be changed to the appropriate one once Apps is up again.
132 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
133 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_packlist_line_id', TRUE);
134 FND_MSG_PUB.ADD;
135 RAISE EXCP_USER_DEFINED;
136 WHEN OTHERS THEN
137 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
138 fnd_message.set_token('ERR_FIELD', 'p_picklist_line_id', TRUE);
139 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
140 fnd_message.set_token('TABLE', 'csp_picklist_lines', TRUE);
141 FND_MSG_PUB.ADD;
142 RAISE EXCP_USER_DEFINED;
143 END;
144 END IF;
145 END IF;
146
147 IF p_action_code IN (1, 2) THEN
148 IF px_PICKLIST_SERIAL_LOT_ID IS NULL THEN
149 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
150 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'px_picklist_serial_lot_id', TRUE);
151 FND_MSG_PUB.ADD;
152 RAISE EXCP_USER_DEFINED;
153 END IF;
154
155 BEGIN
156 -- validate whether the px_picklist_serial_lot_id exists.
157 SELECT picklist_serial_lot_id INTO l_check_existence
158 FROM CSP_Picklist_Serial_Lots
159 WHERE picklist_serial_lot_id = px_picklist_serial_lot_id
160 AND picklist_line_id = p_picklist_line_id;
161
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 -- the following error message needs to be changed to the appropriate one once Apps is up again.
165 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
166 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'px_picklist_serial_lot_id', TRUE);
167 FND_MSG_PUB.ADD;
168 RAISE EXCP_USER_DEFINED;
169 WHEN OTHERS THEN
170 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
171 fnd_message.set_token('ERR_FIELD', 'px_picklist_serial_lot_id', TRUE);
172 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
173 fnd_message.set_token('TABLE', 'csp_picklist_serial_lots', TRUE);
174 FND_MSG_PUB.ADD;
175 RAISE EXCP_USER_DEFINED;
176 END;
177 END IF;
178
179
180 l_psl_rec.PICKLIST_SERIAL_LOT_ID := px_PICKLIST_SERIAL_LOT_ID;
181 l_psl_rec.CREATED_BY := p_CREATED_BY;
182 l_psl_rec.CREATION_DATE := p_CREATION_DATE;
183 l_psl_rec.LAST_UPDATED_BY := p_LAST_UPDATED_BY;
184 l_psl_rec.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE;
185 l_psl_rec.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN;
186 l_psl_rec.PICKLIST_LINE_ID := p_PICKLIST_LINE_ID;
187 l_psl_rec.ORGANIZATION_ID := p_ORGANIZATION_ID;
188 l_psl_rec.INVENTORY_ITEM_ID := p_INVENTORY_ITEM_ID;
189 l_psl_rec.QUANTITY := p_QUANTITY;
190 l_psl_rec.LOT_NUMBER := p_LOT_NUMBER;
191 l_psl_rec.SERIAL_NUMBER := p_SERIAL_NUMBER;
192
193
194 IF p_action_code = 0 THEN
195 -- call the csp_pick_serial_lots_PVT.Create_pick_serial_lots
196 IF p_CREATION_DATE IS NULL THEN
197 l_psl_rec.CREATION_DATE := sysdate;
198 END IF;
199
200 IF p_LAST_UPDATE_DATE IS NULL THEN
201 l_psl_rec.LAST_UPDATE_DATE := sysdate;
202 END IF;
203
204 csp_pick_serial_lots_PVT.Create_pick_serial_lots (
205 P_Api_Version_Number => l_Api_Version_Number,
206 P_Init_Msg_List => p_Init_Msg_List,
207 P_Commit => l_Commit,
208 p_validation_level => l_validation_level,
209 P_psl_Rec => l_psl_rec,
210 X_PICKLIST_SERIAL_LOT_ID => l_picklist_serial_lot_id,
211 X_Return_Status => l_return_status,
212 X_Msg_Count => l_msg_count,
213 X_Msg_Data => l_msg_data);
214
215 ELSIF p_action_code = 1 THEN
216 -- call the csp_pick_serial_lots_PVT.Update_pick_serial_lots
217 IF p_LAST_UPDATE_DATE IS NULL THEN
218 l_psl_rec.LAST_UPDATE_DATE := sysdate;
219 END IF;
220
221 csp_pick_serial_lots_PVT.Update_pick_serial_lots(
222 P_Api_Version_Number => l_Api_Version_Number,
223 P_Init_Msg_List => p_Init_Msg_List,
224 P_Commit => l_Commit,
225 p_validation_level => l_validation_level,
226 P_Identity_Salesforce_Id => NULL,
227 P_psl_Rec => l_psl_rec,
228 X_Return_Status => l_return_status,
229 X_Msg_Count => l_msg_count,
230 X_Msg_Data => l_msg_data);
231
232 ELSIF p_action_code = 2 THEN
233 -- call the csp_pick_serial_lots_PVT.Create_pick_serial_lots
234 csp_pick_serial_lots_PVT.Delete_pick_serial_lots(
235 P_Api_Version_Number => l_Api_Version_Number,
236 P_Init_Msg_List => p_Init_Msg_List,
237 P_Commit => l_Commit,
238 p_validation_level => l_validation_level,
239 P_Identity_Salesforce_Id => NULL,
240 P_psl_Rec => l_psl_rec,
241 X_Return_Status => l_return_status,
242 X_Msg_Count => l_msg_count,
243 X_Msg_Data => l_msg_data);
244
245 ELSE
246 fnd_message.set_name('INV', 'INV-INVALID ACTION');
247 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
248 fnd_msg_pub.add;
249 RAISE EXCP_USER_DEFINED;
250 END IF;
251
252 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
253 RAISE FND_API.G_EXC_ERROR;
254 ELSE
255 x_return_status := fnd_api.g_ret_sts_success;
256 px_PICKLIST_SERIAL_LOT_ID := l_picklist_serial_lot_id;
257 IF fnd_api.to_boolean(p_commit) THEN
258 commit work;
259 END IF;
260 END IF;
261 EXCEPTION
262 WHEN EXCP_USER_DEFINED THEN
263 x_return_status := FND_API.G_RET_STS_ERROR;
264 fnd_msg_pub.count_and_get
265 ( p_count => x_msg_count
266 , p_data => x_msg_data);
267
268 --for debugging purpose
269 -- x_msg_data := l_msg_data;
270
271 WHEN FND_API.G_EXC_ERROR THEN
272 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
273 P_API_NAME => L_API_NAME
274 ,P_PKG_NAME => G_PKG_NAME
275 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
276 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
277 ,X_MSG_COUNT => X_MSG_COUNT
278 ,X_MSG_DATA => X_MSG_DATA
279 ,X_RETURN_STATUS => X_RETURN_STATUS);
280
281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
282 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
283 P_API_NAME => L_API_NAME
284 ,P_PKG_NAME => G_PKG_NAME
285 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
286 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
287 ,X_MSG_COUNT => X_MSG_COUNT
288 ,X_MSG_DATA => X_MSG_DATA
289 ,X_RETURN_STATUS => X_RETURN_STATUS);
290
291 WHEN OTHERS THEN
292 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
293 P_API_NAME => L_API_NAME
294 ,P_PKG_NAME => G_PKG_NAME
295 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
296 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
297 ,X_MSG_COUNT => X_MSG_COUNT
298 ,X_MSG_DATA => X_MSG_DATA
299 ,X_RETURN_STATUS => X_RETURN_STATUS);
300
301 END Validate_And_Write;
302
303 END CSP_Pick_SL_Util;