[Home] [Help]
PACKAGE BODY: APPS.CSP_PICK_UTILS
Source
1 Package Body CSP_PICK_UTILS AS
2 /*$Header: cspgtpub.pls 120.7 2006/10/27 19:08:24 phegde noship $*/
3 --
4 --
5
6 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_PICK_UTILS';
7 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgtpub.pls';
8 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
9 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
10 G_MIN_QUANTITY NUMBER := 0;
11 G_MAX_QUANTITY NUMBER := 0;
12 G_SAFETY_FACTOR NUMBER := 0;
13 G_SAFETY_STOCK NUMBER := 0;
14 G_SERVICE_LEVEL NUMBER := 0;
15 G_EDQ_FACTOR NUMBER := 0;
16 G_ASL_FLAG Varchar2(1);
17 G_SAFETY_STOCK_FLAG Varchar2(1);
18
19 -- Start of comments
20 --
21 -- Procedure : create_pick
22 -- Purpose : Creates picklist headers and details for spares
23 -- It calls the Auto_Detail API of Oracle Inventory
24 -- which creates records in mtl_material_transactions_temp
25 -- and the lot and serial temp tables
26 --
27 -- MODIFICATION HISTORY
28 -- Person Date Comments
29 -- --------- ------ ------------------------------------------
30 -- Pushpa Hegde 12/27/99 Created
31 --
32 -- End of comments
33
34
35 PROCEDURE CSP_ASSIGN_GLOBAL_ORG_ID (P_ORG_ID NUMBER) is
36 begin
37 CSP_PICK_UTILS.GL_ORG_ID := P_ORG_ID;
38 End CSP_ASSIGN_GLOBAL_ORG_ID;
39
40 function CSP_GLOBAL_ORG_ID return number is
41 begin
42 return(GL_ORG_ID);
43 end CSP_GLOBAL_ORG_ID;
44 function CSP_PRODUCT_ORGANIZATION return number is
45 begin
46 return(G_PRODUCT_ORGANIZATION);
47 end CSP_PRODUCT_ORGANIZATION;
48
49 PROCEDURE create_pick( p_api_version_number IN NUMBER
50 ,x_return_status OUT NOCOPY VARCHAR2
51 ,x_msg_count OUT NOCOPY NUMBER
52 ,x_msg_data OUT NOCOPY VARCHAR2
53 ,p_order_by IN NUMBER
54 ,p_org_id IN NUMBER
55 ,p_move_order_header_id IN NUMBER
56 ,p_from_subinventory IN VARCHAR2
57 ,p_to_subinventory IN VARCHAR2
58 ,p_date_required IN DATE
59 ,p_created_by IN NUMBER
60 ,p_move_order_type IN NUMBER
61 ) IS
62
63 l_order_by VARCHAR2(30);
64 l_line_number NUMBER := 0;
65 l_txn_header_id NUMBER;
66 l_picklist_header_id NUMBER;
67 l_picklist_line_id NUMBER;
68 l_old_header_id NUMBER := null;
69 l_from_sub VARCHAR2(30) := null;
70 l_old_from_sub VARCHAR2(30) := null;
71 l_to_sub VARCHAR2(30) := null;
72 l_old_to_sub VARCHAR2(30) := null;
73 l_date_required DATE := null;
74 l_old_date_required DATE := null;
75 l_created_by NUMBER := null;
76 l_old_created_by NUMBER := null;
77 l_line_id NUMBER := null;
78 l_today DATE;
79 l_user_id NUMBER;
80 l_login_id NUMBER;
81 l_serial_control NUMBER;
82 l_serial_flag VARCHAR2(1);
83 l_num_of_rows NUMBER;
84 l_detailed_qty NUMBER;
85 l_transaction_temp_id NUMBER;
86 l_rev VARCHAR2(3);
87 l_from_loc_id NUMBER;
88 l_to_loc_id NUMBER;
89 l_lot_number VARCHAR2(80);
90 l_expiration_date DATE;
91 l_action_code NUMBER := 0; -- for insert
92 l_cpll_rows NUMBER;
93 l_api_version_number CONSTANT NUMBER := 1.0;
94 l_api_name CONSTANT VARCHAR2(20) := 'Create_Pick';
95 l_return_status VARCHAR2(1);
96 l_msg_count NUMBER;
97 l_msg_data VARCHAR2(240);
98 l_trolin_rec INV_Move_Order_PUB.Trolin_Rec_Type;
99 l_error_true BOOLEAN := FALSE;
100 l_prev_header_id NUMBER := null;
101 l_replen_line_id NUMBER := null;
102 l_replen_header_id NUMBER := null;
103 EXCP_USER_DEFINED EXCEPTION;
104
105 CURSOR mo_line_cur_header IS
106 SELECT mtrl.header_id
107 ,mtrl.line_id
108 ,mtrl.inventory_item_id
109 ,mtrl.from_subinventory_code
110 ,mtrl.to_subinventory_code
111 ,mtrl.date_required
112 ,mtrl.created_by
113 ,mtrh.move_order_type
114 ,mtrl.quantity_detailed
115 ,mtrl.quantity
116 FROM mtl_item_locations_kfv milk,
117 mtl_system_items_b_kfv msibk,
118 csp_moveorder_lines cmol,
119 mtl_txn_request_lines mtrl,
120 mtl_txn_request_headers mtrh
121 WHERE mtrl.line_status in (3,7)
122 AND mtrl.transaction_type_id = 64 -- Subinventory Transfer = 64, Account Transfer = 63
123 AND ( (p_move_order_type = 1 and mtrh.move_order_type = 1) -- Move Order Requistion
124 OR (p_move_order_type = 2 and mtrh.move_order_type = 2) -- Replenishment Move Orders
125 OR (p_move_order_type = 3 and mtrh.move_order_type in (1,2)) -- Replenishment and Requisition move orders
126 )
127 AND mtrl.organization_id = p_org_id
128 and mtrh.header_id = mtrl.header_id
129 --AND nvl(quantity_detailed, 0) < quantity
130 AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
131 AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
132 AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
133 AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
134 AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
135 AND milk.inventory_location_id(+) = mtrl.from_locator_id
136 AND milk.organization_id(+) = mtrl.organization_id
137 AND msibk.inventory_item_id(+) = mtrl.inventory_item_id
138 AND msibk.organization_id(+) = mtrl.organization_id
139 AND cmol.line_id = mtrl.line_id
140 ORDER BY mtrl.header_id, mtrl.from_subinventory_code, milk.concatenated_segments, msibk.concatenated_segments;
141
142 CURSOR mo_line_cur_from_sub IS
143 SELECT mtrl.header_id
144 ,mtrl.line_id
145 ,mtrl.inventory_item_id
146 ,mtrl.from_subinventory_code
147 ,mtrl.to_subinventory_code
148 ,mtrl.date_required
149 ,mtrl.created_by
150 ,mtrh.move_order_type
151 ,mtrl.quantity_detailed
152 ,mtrl.quantity
153 FROM mtl_item_locations_kfv milk,
154 mtl_system_items_b_kfv msibk,
155 csp_moveorder_lines cmol,
156 mtl_txn_request_lines mtrl,
157 mtl_txn_request_headers mtrh
158 WHERE mtrl.line_status in (3,7)
159 AND mtrl.transaction_type_id = 64 -- Subinventory Transfer = 64, Account Transfer = 63
160 AND ( (p_move_order_type = 1 and mtrh.move_order_type = 1) -- Move Order Requistion
161 OR (p_move_order_type = 2 and mtrh.move_order_type = 2) -- Replenishment Move Orders
162 OR (p_move_order_type = 3 and mtrh.move_order_type in (1,2)) -- Replenishment and Requisition move orders
163 )
164 AND mtrl.organization_id = p_org_id
165 and mtrh.header_id = mtrl.header_id
166 --AND nvl(quantity_detailed, 0) < quantity
167 AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
168 AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
169 AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
170 AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
171 AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
172 AND milk.inventory_location_id(+) = mtrl.from_locator_id
173 AND milk.organization_id(+) = mtrl.organization_id
174 AND msibk.inventory_item_id(+) = mtrl.inventory_item_id
175 AND msibk.organization_id(+) = mtrl.organization_id
176 AND cmol.line_id = mtrl.line_id
177 ORDER BY mtrl.from_subinventory_code, milk.concatenated_segments, msibk.concatenated_segments;
178
179 CURSOR mo_line_cur_to_sub IS
180 SELECT mtrl.header_id
181 ,mtrl.line_id
182 ,mtrl.inventory_item_id
183 ,mtrl.from_subinventory_code
184 ,mtrl.to_subinventory_code
185 ,mtrl.date_required
186 ,mtrl.created_by
187 ,mtrh.move_order_type
188 ,mtrl.quantity_detailed
189 ,mtrl.quantity
190 FROM mtl_item_locations_kfv milk,
191 mtl_system_items_b_kfv msibk,
192 csp_moveorder_lines cmol,
193 mtl_txn_request_lines mtrl,
194 mtl_txn_request_headers mtrh
195 WHERE mtrl.line_status in (3,7)
196 AND mtrl.transaction_type_id = 64 -- Subinventory Transfer = 64, Account Transfer = 63
197 AND ( (p_move_order_type = 1 and mtrh.move_order_type = 1) -- Move Order Requistion
198 OR (p_move_order_type = 2 and mtrh.move_order_type = 2) -- Replenishment Move Orders
199 OR (p_move_order_type = 3 and mtrh.move_order_type in (1,2)) -- Replenishment and Requisition move orders
200 )
201 AND mtrl.organization_id = p_org_id
202 and mtrh.header_id = mtrl.header_id
203 --AND nvl(quantity_detailed, 0) < quantity
204 AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
205 AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
206 AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
207 AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
208 AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
209 AND milk.inventory_location_id(+) = mtrl.from_locator_id
210 AND milk.organization_id(+) = mtrl.organization_id
211 AND msibk.inventory_item_id(+) = mtrl.inventory_item_id
212 AND msibk.organization_id(+) = mtrl.organization_id
213 AND cmol.line_id = mtrl.line_id
214 ORDER BY mtrl.to_subinventory_code, mtrl.from_subinventory_code, milk.concatenated_segments, msibk.concatenated_segments;
215
216 CURSOR mo_line_cur_date_reqd IS
217 SELECT mtrl.header_id
218 ,mtrl.line_id
219 ,mtrl.inventory_item_id
220 ,mtrl.from_subinventory_code
221 ,mtrl.to_subinventory_code
222 ,mtrl.date_required
223 ,mtrl.created_by
224 ,mtrh.move_order_type
225 ,mtrl.quantity_detailed
226 ,mtrl.quantity
227 FROM mtl_item_locations_kfv milk,
228 mtl_system_items_b_kfv msibk,
229 csp_moveorder_lines cmol,
230 mtl_txn_request_lines mtrl,
231 mtl_txn_request_headers mtrh
232 WHERE mtrl.line_status in (3,7)
233 AND mtrl.transaction_type_id = 64 -- Subinventory Transfer = 64, Account Transfer = 63
234 AND ( (p_move_order_type = 1 and mtrh.move_order_type = 1) -- Move Order Requistion
235 OR (p_move_order_type = 2 and mtrh.move_order_type = 2) -- Replenishment Move Orders
236 OR (p_move_order_type = 3 and mtrh.move_order_type in (1,2)) -- Replenishment and Requisition move orders
237 )
238 AND mtrl.organization_id = p_org_id
239 and mtrh.header_id = mtrl.header_id
240 --AND nvl(quantity_detailed, 0) < quantity
241 AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
242 AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
243 AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
244 AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
245 AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
246 AND milk.inventory_location_id(+) = mtrl.from_locator_id
247 AND milk.organization_id(+) = mtrl.organization_id
248 AND msibk.inventory_item_id(+) = mtrl.inventory_item_id
249 AND msibk.organization_id(+) = mtrl.organization_id
250 AND cmol.line_id = mtrl.line_id
251 ORDER BY mtrl.date_Required, mtrl.from_subinventory_code, milk.concatenated_segments, msibk.concatenated_segments;
252
253 CURSOR mo_line_cur_created_by IS
254 SELECT mtrl.header_id
255 ,mtrl.line_id
256 ,mtrl.inventory_item_id
257 ,mtrl.from_subinventory_code
258 ,mtrl.to_subinventory_code
259 ,mtrl.date_required
260 ,mtrl.created_by
261 ,mtrh.move_order_type
262 ,mtrl.quantity_detailed
263 ,mtrl.quantity
264 FROM mtl_item_locations_kfv milk,
265 mtl_system_items_b_kfv msibk,
266 csp_moveorder_lines cmol,
267 mtl_txn_request_lines mtrl,
268 mtl_txn_request_headers mtrh
269 WHERE mtrl.line_status in (3,7)
270 AND mtrl.transaction_type_id = 64 -- Subinventory Transfer = 64, Account Transfer = 63
271 AND ( (p_move_order_type = 1 and mtrh.move_order_type = 1) -- Move Order Requistion
272 OR (p_move_order_type = 2 and mtrh.move_order_type = 2) -- Replenishment Move Orders
273 OR (p_move_order_type = 3 and mtrh.move_order_type in (1,2)) -- Replenishment and Requisition move orders
274 )
275 AND mtrl.organization_id = p_org_id
276 and mtrh.header_id = mtrl.header_id
277 --AND nvl(quantity_detailed, 0) < quantity
278 AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
279 AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
280 AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
281 AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
282 AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
283 AND milk.inventory_location_id(+) = mtrl.from_locator_id
284 AND milk.organization_id(+) = mtrl.organization_id
285 AND msibk.inventory_item_id(+) = mtrl.inventory_item_id
286 AND msibk.organization_id(+) = mtrl.organization_id
287 AND cmol.line_id = mtrl.line_id
288 ORDER BY mtrl.created_by, mtrl.from_subinventory_code, milk.concatenated_segments, msibk.concatenated_segments;
289
290 CURSOR txn_temp_cur IS
291 SELECT transaction_temp_id
292 ,created_by
293 ,creation_date
294 ,last_updated_by
295 ,last_update_date
296 ,last_update_login
297 ,move_order_line_id
298 ,inventory_item_id
299 ,revision
300 ,transaction_quantity
301 ,transaction_uom
302 FROM mtl_material_transactions_temp
303 WHERE move_order_line_id = l_line_id
304 --AND transfer_subinventory = decode(l_to_sub, null, transfer_subinventory, l_to_sub)
305 --AND subinventory_code = decode(l_from_sub, null, subinventory_code, l_from_sub)
306 AND transaction_type_id = 64
307 AND organization_id = p_org_id;
308
309 CURSOR mo_replen_cur IS
310 SELECT mtrl.header_id
311 ,mtrl.line_id
312 FROM mtl_txn_request_headers mtrh
313 ,mtl_txn_request_lines mtrl
314 WHERE mtrl.header_id = mtrh.header_id
315 AND mtrl.line_status = 7
316 AND mtrh.move_order_type = 2
317 AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
318 AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
319 AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
320 AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
321 AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
322 AND mtrl.organization_id = mtrh.organization_id
323 AND mtrh.organization_id = p_org_id
324 ORDER BY mtrl.header_id, mtrl.line_id;
325
326 mo_replen_rec mo_replen_cur%ROWTYPE;
327 mo_line_rec mo_line_cur_header%ROWTYPE;
328 txn_temp_rec txn_temp_cur%ROWTYPE;
329
330 l_return_count NUMBER := 1;
331 t_msg_data varchar2(2000);
332 t_msg_dummy number;
333 BEGIN
334
335 -- Start of API savepoint
336 SAVEPOINT Create_Pick_PUB;
337
338 -- initialize message list
339 FND_MSG_PUB.initialize;
340
341 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
342 p_api_version_number,
343 l_api_name,
344 G_PKG_NAME) THEN
345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346 END IF;
347
348 x_return_status := FND_API.G_RET_STS_SUCCESS;
349
350 SELECT Sysdate INTO l_today FROM dual;
351 l_user_id := fnd_global.user_id;
352 l_login_id := fnd_global.login_id;
353
354 -- If p_move_order_type = 2 or 3 (include replenishment move orders) then
355 -- create records in spares move order tables
356 IF (p_move_order_type = 2 OR p_move_order_type = 3) THEN
357 OPEN mo_replen_cur;
358
359 LOOP
360 FETCH mo_replen_cur INTO mo_replen_rec;
361 EXIT WHEN mo_replen_cur%NOTFOUND;
362
363 BEGIN
364 SELECT line_id
365 INTO l_replen_line_id
366 FROM CSP_MOVEORDER_LINES
367 WHERE line_id = mo_replen_rec.line_id;
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 IF (nvl(l_prev_header_id, 0) <> mo_replen_rec.header_id) THEN
371 BEGIN
372 SELECT header_id
373 INTO l_replen_header_id
374 FROM CSP_MOVEORDER_HEADERS
375 WHERE HEADER_ID = mo_replen_rec.header_id;
376 EXCEPTION
377 WHEN NO_DATA_FOUND THEN
378 CSP_TO_FORM_MOHEADERS.Validate_And_Write(
379 P_Api_Version_Number => 1.0,
380 P_Init_Msg_List => FND_API.G_FALSE,
381 P_Commit => FND_API.G_FALSE,
382 p_validation_level => null,
383 p_action_code => l_action_code,/* 0 = insert, 1 = update, 2 = delete */
384 p_header_id => mo_replen_rec.header_id,
385 p_created_by => l_user_id,
386 p_CREATION_DATE => l_today,
387 p_LAST_UPDATED_BY => l_user_id,
388 p_LAST_UPDATE_DATE => l_today,
389 p_LAST_UPDATE_LOGIN => l_login_id,
390 p_carrier => null,
391 p_shipment_method => null,
392 p_autoreceipt_flag => 'Y',
393 p_attribute_category => null,
394 p_attribute1 => null,
395 p_attribute2 => null,
396 p_attribute3 => null,
397 p_attribute4 => null,
398 p_attribute5 => null,
399 p_attribute6 => null,
400 p_attribute7 => null,
401 p_attribute8 => null,
402 p_attribute9 => null,
403 p_attribute10 => null,
404 p_attribute11 => null,
405 p_attribute12 => null,
406 p_attribute13 => null,
407 p_attribute14 => null,
408 p_attribute15 => null,
409 p_location_id => null,
410 p_party_site_id => null,
411 X_Return_Status => l_return_status,
412 X_Msg_Count => l_msg_count,
413 X_Msg_Data => l_msg_data
414 );
415
416 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
417 CLOSE mo_replen_cur;
418 RAISE FND_API.G_EXC_ERROR;
419 END IF;
420
421 l_prev_header_id := mo_replen_rec.header_id;
422
423 WHEN OTHERS THEN
424 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
425 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
426 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
427 fnd_msg_pub.add;
428 RAISE EXCP_USER_DEFINED;
429 END;
430 END IF;
431
432 -- insert into csp_move_order_line
433 CSP_TO_FORM_MOLINES.Validate_And_write(
434 P_Api_Version_Number => 1.0,
435 P_Init_Msg_List => FND_API.G_FALSE,
436 P_Commit => FND_API.G_FALSE,
437 p_validation_level => null,
438 p_action_code => l_action_code,
439 P_line_id => mo_replen_rec.line_id,
440 p_CREATED_BY => l_user_id,
441 p_CREATION_DATE => l_today,
442 p_LAST_UPDATED_BY => l_user_id,
443 p_LAST_UPDATE_DATE => l_today,
444 p_LAST_UPDATED_LOGIN => l_login_id,
445 p_HEADER_ID => mo_replen_rec.header_id,
446 p_CUSTOMER_PO => null,
447 p_INCIDENT_ID => null,
448 p_TASK_ID => null,
449 p_TASK_ASSIGNMENT_ID => null,
450 p_COMMENTS => null,
451 p_ATTRIBUTE_CATEGORY => null,
452 p_ATTRIBUTE1 => null,
453 p_ATTRIBUTE2 => null,
454 p_ATTRIBUTE3 => null,
455 p_ATTRIBUTE4 => null,
456 p_ATTRIBUTE5 => null,
457 p_ATTRIBUTE6 => null,
458 p_ATTRIBUTE7 => null,
459 p_ATTRIBUTE8 => null,
460 p_ATTRIBUTE9 => null,
461 p_ATTRIBUTE10 => null,
462 p_ATTRIBUTE11 => null,
463 p_ATTRIBUTE12 => null,
464 p_ATTRIBUTE13 => null,
465 p_ATTRIBUTE14 => null,
466 p_ATTRIBUTE15 => null,
467 X_Return_Status => l_return_status,
468 X_Msg_Count => l_msg_count,
469 X_Msg_Data => l_msg_data
470 );
471
472 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
473 CLOSE mo_replen_cur;
474 RAISE FND_API.G_EXC_ERROR;
475 END IF;
476
477 WHEN OTHERS THEN
478 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
479 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
480 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
481 fnd_msg_pub.add;
482 RAISE EXCP_USER_DEFINED;
483 END;
484 END LOOP;
485
486 CLOSE mo_replen_cur;
487 END IF;
488
489 IF (p_order_by = 1) THEN
490 OPEN mo_line_cur_header;
491 ELSIF (p_order_by = 2) THEN
492 OPEN mo_line_cur_from_sub;
493 ELSIF (p_order_by = 3) THEN
494 OPEN mo_line_cur_to_sub;
495 ELSIF (p_order_by = 4) THEN
496 OPEN mo_line_cur_date_Reqd;
497 ELSIF (p_order_by = 5) THEN
498 OPEN mo_line_cur_created_by;
499 ELSE
500 RAISE FND_API.G_EXC_ERROR;
501 END IF;
502
503 LOOP
504 IF (p_order_by = 1) THEN
505 FETCH mo_line_cur_header INTO mo_line_rec;
506 ELSIF (p_order_by = 2) THEN
507 FETCH mo_line_cur_from_sub INTO mo_line_rec;
508 ELSIF (p_order_by = 3) THEN
509 FETCH mo_line_cur_to_sub INTO mo_line_rec;
510 ELSIF (p_order_by = 4) THEN
511 FETCH mo_line_cur_date_reqd INTO mo_line_rec;
512 ELSIF (p_order_by = 5) THEN
513 FETCH mo_line_cur_created_by INTO mo_line_rec;
514 ELSE
515 RAISE FND_API.G_EXC_ERROR;
516 END IF;
517
518 EXIT WHEN ((p_order_by = 1 and mo_line_cur_header%NOTFOUND) OR
519 (p_order_by = 2 and mo_line_cur_from_sub%NOTFOUND) OR
520 (p_order_by = 3 and mo_line_cur_to_sub%NOTFOUND) OR
521 (p_order_by =4 and mo_line_cur_date_reqd%NOTFOUND) OR
522 (p_order_by = 5 and mo_line_cur_created_by%NOTFOUND));
523
524 l_line_id := mo_line_rec.line_id;
525
526 SELECT mtl_material_transactions_s.nextval
527 INTO l_txn_header_id
528 FROM dual;
529
530 -- This code is used for auto detailing serial numbers
531
532 SELECT serial_number_control_code into l_serial_control
533 FROM mtl_system_items
534 WHERE inventory_item_id = mo_line_rec.inventory_item_id
535 AND organization_id = p_org_id;
536
537 IF l_serial_control = 1 THEN
538 l_serial_flag := fnd_api.g_false;
539 ELSE
540 l_serial_flag := fnd_api.g_true;
541 END IF;
542
543
544 INV_Replenish_Detail_PUB.Line_Details_PUB(
545 p_line_id => mo_line_rec.line_id,
546 x_number_of_rows => l_num_of_rows,
547 x_detailed_qty => l_detailed_qty,
548 x_return_status => l_return_status,
549 x_msg_count => l_msg_count,
550 x_msg_data => l_msg_data,
551 x_revision => l_rev,
552 x_locator_id => l_from_loc_id,
553 x_transfer_to_location => l_to_loc_id,
554 x_lot_number => l_lot_number,
555 x_expiration_date => l_expiration_date,
556 x_transaction_temp_id => l_transaction_temp_id,
557 p_transaction_header_id => l_txn_header_id,
558 p_transaction_mode => null,
559 p_move_order_type => mo_line_rec.move_order_type,
560 p_serial_flag => l_serial_flag
561 );
562
563 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
564 fnd_message.set_name ('CSP', 'CSP_MO_DETAILING_ERROR');
565 fnd_message.set_token ('MOVE_ORDER_LINE_ID', to_char(mo_line_rec.line_id), FALSE);
566 fnd_msg_pub.add;
567
568 for j in reverse 1..fnd_msg_pub.count_msg loop
569 fnd_msg_pub.get
570 ( j
571 , FND_API.G_FALSE
572 , t_msg_data
573 , t_msg_dummy
574 );
575
576 x_msg_data := x_msg_data || t_msg_data;
577
578 IF mod(l_return_count, 2) = 0 THEN
579 x_msg_data := x_msg_data || fnd_global.local_chr(10);
580 END IF;
581 l_return_count := l_return_count + 1;
582 end loop;
583 l_return_status := fnd_api.g_ret_sts_success;
584 x_msg_count := -1 ;
585 END IF;
586
587 IF (l_num_of_rows >= 1) THEN
588
589 -- update mtl_txn_request_lines with the detailed quantity
590 l_trolin_rec := INV_Trolin_util.Query_Row( mo_line_rec.line_id );
591 l_trolin_rec.quantity_detailed := l_detailed_qty;
592 l_trolin_rec.last_update_date := SYSDATE;
593 l_trolin_rec.last_updated_by := FND_GLOBAL.USER_ID;
594 l_trolin_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
595
596 INV_Trolin_Util.Update_Row(l_trolin_rec);
597
598 /* update mtl_txn_request_lines
599 set quantity_detailed = l_detailed_qty
600 where line_id = mo_line_rec.line_id; */
601
602
603 OPEN txn_temp_cur;
604
605 LOOP
606 FETCH txn_temp_cur INTO txn_temp_rec;
607 EXIT WHEN txn_temp_cur%NOTFOUND;
608
609 SELECT count(1)
610 INTO l_cpll_rows
611 FROM csp_picklist_lines
612 WHERE transaction_temp_id = txn_temp_rec.transaction_temp_id;
613
614 IF (nvl(l_cpll_rows, 0) = 0) THEN
615
616 IF (((p_order_by = 1) AND (l_old_header_id is NULL OR l_old_header_id <> mo_line_rec.header_id))
617 OR ((p_order_by = 2) AND (l_old_from_sub is NULL OR l_old_from_sub <> mo_line_rec.from_subinventory_code))
618 OR ((p_order_by = 3) AND (l_old_to_sub is NULL OR l_old_to_sub <> mo_line_rec.to_subinventory_code))
619 OR ((p_order_by = 4) AND (l_old_date_required is NULL OR l_old_date_required <> mo_line_rec.date_required))
620 OR ((p_order_by = 5) AND (l_old_created_by is NULL OR l_old_created_by <> mo_line_rec.created_by))
621 ) THEN
622
623 l_old_header_id := mo_line_rec.header_id;
624 l_old_from_sub := mo_line_rec.from_subinventory_code;
625 l_old_to_sub := mo_line_rec.to_subinventory_code;
626 l_old_date_required := mo_line_rec.date_required;
627 l_old_created_by := mo_line_rec.created_by;
628
629 l_line_number := 0; -- Initialize the line number
630
631 SELECT csp_picklist_headers_s1.nextval
632 INTO l_picklist_header_id
633 FROM dual;
634
635 CSP_PC_FORM_PICKHEADERS.Validate_And_Write (
636 P_Api_Version_Number => 1.0,
637 P_Init_Msg_List => FND_API.G_FALSE,
638 P_Commit => FND_API.G_FALSE,
639 p_validation_level => null,
640 p_action_code => l_action_code,
641 px_PICKLIST_HEADER_ID => l_picklist_header_id,
642 p_CREATED_BY => l_user_id,
643 p_CREATION_DATE => l_today,
644 p_LAST_UPDATED_BY => l_user_id,
645 p_LAST_UPDATE_DATE => l_today,
646 p_LAST_UPDATE_LOGIN => l_login_id,
647 p_ORGANIZATION_ID => p_org_id,
648 p_PICKLIST_NUMBER => l_picklist_header_id,
649 p_PICKLIST_STATUS => 1, -- open
650 p_DATE_CREATED => l_today,
651 p_DATE_CONFIRMED => null,
652 p_ATTRIBUTE_CATEGORY => null,
653 p_ATTRIBUTE1 => null,
654 p_ATTRIBUTE2 => null,
655 p_ATTRIBUTE3 => null,
656 p_ATTRIBUTE4 => null,
657 p_ATTRIBUTE5 => null,
658 p_ATTRIBUTE6 => null,
659 p_ATTRIBUTE7 => null,
660 p_ATTRIBUTE8 => null,
661 p_ATTRIBUTE9 => null,
662 p_ATTRIBUTE10 => null,
663 p_ATTRIBUTE11 => null,
664 p_ATTRIBUTE12 => null,
665 p_ATTRIBUTE13 => null,
666 p_ATTRIBUTE14 => null,
667 p_ATTRIBUTE15 => null,
668 X_Return_Status => l_return_status,
669 X_Msg_Count => l_msg_count,
670 X_Msg_Data => l_msg_data
671 );
672 END IF;
673
674 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
675 RAISE FND_API.G_EXC_ERROR;
676 END IF;
677
678 l_line_number := (l_line_number + 1);
679
680 SELECT csp_picklist_lines_s1.nextval
681 INTO l_picklist_line_id
682 FROM dual;
683
684 CSP_PC_FORM_PICKLINES.Validate_And_Write (
685 P_Api_Version_Number => 1.0
686 ,P_Init_Msg_List => FND_API.G_FALSE
687 ,P_Commit => FND_API.G_FALSE
688 ,p_validation_level => null
689 ,p_action_code => l_action_code /* 0 = insert, 1 = update, 2 = delete */
690 ,px_PICKLIST_LINE_ID => l_picklist_line_id
691 ,p_CREATED_BY => txn_temp_rec.created_by
692 ,p_CREATION_DATE => txn_temp_rec.creation_date
693 ,p_LAST_UPDATED_BY => txn_temp_rec.last_updated_by
694 ,p_LAST_UPDATE_DATE => txn_temp_rec.last_update_date
695 ,p_LAST_UPDATE_LOGIN => txn_temp_rec.last_update_login
696 ,p_PICKLIST_LINE_NUMBER => l_line_number
697 ,p_PICKLIST_HEADER_ID => l_picklist_header_id
698 ,p_LINE_ID => mo_line_rec.line_id
699 ,p_INVENTORY_ITEM_ID => txn_temp_rec.inventory_item_id
700 ,p_UOM_CODE => txn_temp_rec.transaction_uom
701 ,p_REVISION => txn_temp_rec.revision
702 ,p_QUANTITY_PICKED => txn_temp_rec.transaction_quantity
703 ,p_TRANSACTION_TEMP_ID => txn_temp_rec.transaction_temp_id
704 ,p_ATTRIBUTE_CATEGORY => null
705 ,p_ATTRIBUTE1 => null
706 ,p_ATTRIBUTE2 => null
707 ,p_ATTRIBUTE3 => null
708 ,p_ATTRIBUTE4 => null
709 ,p_ATTRIBUTE5 => null
710 ,p_ATTRIBUTE6 => null
711 ,p_ATTRIBUTE7 => null
712 ,p_ATTRIBUTE8 => null
713 ,p_ATTRIBUTE9 => null
714 ,p_ATTRIBUTE10 => null
715 ,p_ATTRIBUTE11 => null
716 ,p_ATTRIBUTE12 => null
717 ,p_ATTRIBUTE13 => null
718 ,p_ATTRIBUTE14 => null
719 ,p_ATTRIBUTE15 => null
720 ,x_return_status => l_return_status
721 ,x_msg_count => l_msg_count
722 ,x_msg_data => l_msg_data
723 );
724
725 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
726 RAISE FND_API.G_EXC_ERROR;
727 END IF;
728 END IF;
729 END LOOP;
730 CLOSE txn_temp_cur;
731
732 END IF;
733 END LOOP;
734
735 IF (p_order_by = 1) THEN
736 CLOSE mo_line_cur_header;
737 ELSIF (p_order_by = 2) THEN
738 CLOSE mo_line_cur_from_sub;
739 ELSIF (p_order_by = 3) THEN
740 CLOSE mo_line_cur_to_sub;
741 ELSIF (p_order_by = 4) THEN
742 CLOSE mo_line_cur_date_Reqd;
743 ELSIF (p_order_by = 5) THEN
744 CLOSE mo_line_cur_created_by;
745 ELSE
746 RAISE FND_API.G_EXC_ERROR;
747 END IF;
748
749
750 x_return_status := l_return_status;
751
752 EXCEPTION
753 WHEN EXCP_USER_DEFINED THEN
754 Rollback to Create_Pick_PUB;
755 fnd_msg_pub.count_and_get
756 ( p_count => x_msg_count
757 , p_data => x_msg_data);
758 x_return_status := FND_API.G_RET_STS_ERROR;
759
760 WHEN FND_API.G_EXC_ERROR THEN
761 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
762 P_API_NAME => L_API_NAME
763 ,P_PKG_NAME => G_PKG_NAME
764 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
765 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
766 ,X_MSG_COUNT => X_MSG_COUNT
767 ,X_MSG_DATA => X_MSG_DATA
768 ,X_RETURN_STATUS => X_RETURN_STATUS);
769 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
770 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
771 P_API_NAME => L_API_NAME
772 ,P_PKG_NAME => G_PKG_NAME
773 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
774 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
775 ,X_MSG_COUNT => X_MSG_COUNT
776 ,X_MSG_DATA => X_MSG_DATA
777 ,X_RETURN_STATUS => X_RETURN_STATUS);
778 WHEN OTHERS THEN
779 Rollback to Create_Pick_PUB;
780 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
781 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
782 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
783 fnd_msg_pub.add;
784 fnd_msg_pub.count_and_get
785 ( p_count => x_msg_count
786 , p_data => x_msg_data);
787 x_return_status := fnd_api.g_ret_sts_error;
788 END create_pick;
789
790
791 Procedure Confirm_Pick (
792 -- Start of Comments
793 -- Procedure : Confirm_Pick
794 -- Purpose : This procedure inserts the record into the csp_picklist_serial_lots tables based on the
795 -- msnt or the mtlt record associated with the picklist.
796 --
797 -- History :
798 -- UserID Date Comments
799 -- ----------- -------- --------------------------
800 -- klou 02/01/2000 Created.
801 --
802 -- NOTES:
803 --
804 --End of Comments
805 P_Api_Version_Number IN NUMBER
806 ,P_Init_Msg_List IN VARCHAR2
807 ,P_Commit IN VARCHAR2
808 ,p_validation_level IN NUMBER
809 ,p_picklist_header_id IN NUMBER
810 ,p_organization_id IN NUMBER
811 ,x_return_status OUT NOCOPY VARCHAR2
812 ,x_msg_count OUT NOCOPY NUMBER
813 ,x_msg_data OUT NOCOPY VARCHAR2
814 )
815
816 IS
817 l_api_version_number CONSTANT NUMBER := 1.0;
818 l_api_name CONSTANT VARCHAR2(30) := 'Confirm_Pick';
819 l_return_status VARCHAR2(1);
820 l_msg_count NUMBER := 0;
821 l_msg_data VARCHAR2(500);
822 l_commit VARCHAR2(1) := fnd_api.g_false;
823 l_check_existence NUMBER := 0;
824 EXCP_USER_DEFINED EXCEPTION;
825 l_picklist_serial_lot_id NUMBER;
826
827 TYPE picklist_line_Rec_Type IS RECORD
828 (
829 picklist_line_id NUMBER := NULL,
830 picklist_header_id NUMBER := NULL,
831 LINE_ID NUMBER := NULL,
832 INVENTORY_ITEM_ID NUMBER := NULL,
833 QUANTITY_PICKED NUMBER := NULL,
834 TRANSACTION_TEMP_ID NUMBER := NULL );
835
836 TYPE mtl_txn_serial_lot_Rec_Type IS RECORD
837 (
838 item_serial_control_code NUMBER := NULL,
839 item_lot_control_code NUMBER := NULL );
840
841 TYPE mtl_txn_lot_numbers_Rec_Type IS RECORD
842 (
843 transaction_temp_id NUMBER := NULL,
844 serial_transaction_temp_id NUMBER := NULL,
845 lot_number VARCHAR2(80) := NULL,
846 transaction_quantity NUMBER := NULL,
847 primary_quantity NUMBER := NULL );
848
849 l_picklist_line_rec picklist_line_Rec_Type;
850 l_mmtt_rec mtl_txn_serial_lot_Rec_Type;
851 l_lot_number_rec mtl_txn_lot_numbers_Rec_Type;
852
853 CURSOR l_Get_Picklist_Lines_Csr IS
854 SELECT picklist_line_id, picklist_header_id, line_id, inventory_item_id,
855 quantity_picked, transaction_temp_id
856 FROM csp_picklist_lines
857 WHERE picklist_header_id = p_picklist_header_id;
858
859 CURSOR l_Get_Mmtt_Csr (l_transaction_temp_id NUMBER) IS
860 SELECT item_serial_control_code, item_lot_control_code
861 FROM mtl_material_transactions_temp
862 WHERE organization_id = p_organization_id
863 AND transaction_temp_id = l_transaction_temp_id;
864
865 CURSOR l_Get_Mtlt_Csr (l_transaction_temp_id NUMBER) IS
866 SELECT transaction_temp_id, serial_transaction_temp_id, lot_number,
867 transaction_quantity, primary_quantity
868 FROM mtl_transaction_lots_temp
869 WHERE transaction_temp_id = l_transaction_temp_id;
870
871 -- define a subfunction to transact any serial record
872 Function transact_serial (
873 l_temp_id_ref IN NUMBER) RETURN BOOLEAN
874 is
875 /*
876 TYPE mtl_serial_numbers_Rec_Type IS RECORD(
877 transaction_temp_id NUMBER := NULL,
878 fm_serial_number VARCHAR2(30) := NULL,
879 to_serial_number VARCHAR2(30) := NULL );
880
881 l_serial_number_rec mtl_serial_numbers_Rec_Type;
882 CURSOR l_Get_Serial_Numbers_Csr IS
883 SELECT transaction_temp_id, fm_serial_number, to_serial_number
884 FROM mtl_serial_numbers_temp
885 WHERE transaction_temp_id = l_temp_id_ref;
886 */
887 l_msnt_tbl csp_pp_util.g_msnt_tbl_type;
888 l_tbl_index NUMBER := 1;
889 l_fm_prefix VARCHAR2(30);
890 l_to_prefix VARCHAR2(30);
891 l_fm_number VARCHAR2(30);
892 l_to_number VARCHAR2(30);
893 l_fm_serial_to_del VARCHAR2(30);
894 l_to_serial_to_del VARCHAR2(30);
895 l_temp_id_to_del NUMBER;
896 l_number_length NUMBER := 0;
897 l_total_serial_numbers NUMBER := 0;
898 l_index NUMBER := 0;
899
900 CURSOR l_Get_Serial_Numbers_Csr IS
901 SELECT * FROM mtl_serial_numbers_temp
902 WHERE transaction_temp_id = l_temp_id_ref;
903 BEGIN
904 OPEN l_Get_Serial_Numbers_Csr;
905 LOOP <<process_serial_records>>
906 -- FETCH l_Get_Serial_Numbers_Csr INTO l_serial_number_rec;
907 FETCH l_Get_Serial_Numbers_Csr Into l_msnt_tbl(l_tbl_index);
908 EXIT WHEN l_Get_Serial_Numbers_Csr%NOTFOUND;
909
910 -- Analyze the serial number range
911 csp_pp_util.split_prefix_num (
912 p_serial_number => l_msnt_tbl(l_tbl_index).fm_serial_number
913 ,p_prefix => l_fm_prefix
914 ,x_num => l_fm_number
915 );
916
917 csp_pp_util.split_prefix_num (
918 p_serial_number => l_msnt_tbl(l_tbl_index).to_serial_number
919 ,p_prefix => l_to_prefix
920 ,x_num => l_to_number
921 );
922
923 IF (l_fm_number IS NULL AND l_to_number IS NOT NULL)
924 -- OR (l_fm_number IS NOT NULL AND l_to_number IS NULL)
925 OR (nvl(to_number(l_to_number), l_fm_number) < to_number(l_fm_number)) THEN
926 fnd_message.set_name ('CSP', 'CSP_INVALID_SERIAL_RANGE');
927 fnd_msg_pub.add;
928 CLOSE l_Get_Mmtt_Csr;
929 CLOSE l_Get_Serial_Numbers_Csr;
930 CLOSE l_Get_Picklist_Lines_Csr;
931 RAISE EXCP_USER_DEFINED;
932 END IF;
933
934 l_fm_serial_to_del := l_msnt_tbl(l_tbl_index).fm_serial_number;
935 l_to_serial_to_del := l_msnt_tbl(l_tbl_index).to_serial_number;
936 l_temp_id_to_del := l_msnt_tbl(l_tbl_index).transaction_temp_id;
937 l_total_serial_numbers := to_number(l_to_number);
938
939 IF nvl(l_to_number, l_fm_number) = l_fm_number OR (l_to_number IS NULL AND l_fm_number IS NULL) THEN
940 l_total_serial_numbers := 1;
941 END IF;
942
943 IF l_total_serial_numbers = 1 THEN
944 CSP_Pick_SL_Util.Validate_And_Write (
945 P_Api_Version_Number => l_api_version_number,
946 P_Init_Msg_List => FND_API.G_TRUE,
947 P_Commit => l_commit,
948 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
949 p_action_code => 0,
950 px_PICKLIST_SERIAL_LOT_ID => l_picklist_serial_lot_id,
951 p_CREATED_BY => G_USER_ID,
952 p_CREATION_DATE => sysdate,
953 p_LAST_UPDATED_BY => G_USER_ID,
954 p_LAST_UPDATE_DATE => sysdate,
955 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
956 p_PICKLIST_LINE_ID => l_picklist_line_rec.picklist_line_id,
957 p_ORGANIZATION_ID => p_organization_id,
958 p_INVENTORY_ITEM_ID => l_picklist_line_rec.inventory_item_id,
959 p_QUANTITY => 1,
960 p_LOT_NUMBER => l_lot_number_rec.lot_number,
961 p_SERIAL_NUMBER => l_msnt_tbl(l_tbl_index).fm_serial_number,
962 X_Return_Status => l_return_status,
963 X_Msg_Count => l_msg_count,
964 X_Msg_Data => l_msg_data
965 );
966
967 IF l_return_status <> fnd_api.g_ret_sts_success THEN
968 CLOSE l_Get_Serial_Numbers_Csr;
969 RETURN FALSE;
970 END IF;
971 ELSE
972 l_index := to_number(l_fm_number);
973 l_number_length := length(l_fm_number);
974
975 WHILE l_index <= l_total_serial_numbers LOOP
976 CSP_Pick_SL_Util.Validate_And_Write (
977 P_Api_Version_Number => l_api_version_number,
978 P_Init_Msg_List => FND_API.G_TRUE,
979 P_Commit => l_commit,
980 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
981 p_action_code => 0,
982 px_PICKLIST_SERIAL_LOT_ID => l_picklist_serial_lot_id,
983 p_CREATED_BY => G_USER_ID,
984 p_CREATION_DATE => sysdate,
985 p_LAST_UPDATED_BY => G_USER_ID,
986 p_LAST_UPDATE_DATE => sysdate,
987 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
988 p_PICKLIST_LINE_ID => l_picklist_line_rec.picklist_line_id,
989 p_ORGANIZATION_ID => p_organization_id,
990 p_INVENTORY_ITEM_ID => l_picklist_line_rec.inventory_item_id,
991 p_QUANTITY => 1,
992 p_LOT_NUMBER => l_lot_number_rec.lot_number,
993 p_SERIAL_NUMBER => l_fm_prefix||lpad(to_char(l_index),l_number_length, '0'),
994 X_Return_Status => l_return_status,
995 X_Msg_Count => l_msg_count,
996 X_Msg_Data => l_msg_data );
997
998 IF l_return_status <> fnd_api.g_ret_sts_success THEN
999 CLOSE l_Get_Serial_Numbers_Csr;
1000 RETURN FALSE;
1001 END IF;
1002
1003 l_msnt_tbl(l_tbl_index).fm_serial_number := l_fm_prefix||lpad(to_char(l_index),l_number_length, '0');
1004 l_msnt_tbl(l_tbl_index).to_serial_number := l_fm_prefix||lpad(to_char(l_index),l_number_length, '0');
1005 l_msnt_tbl(l_tbl_index).serial_prefix := 1;
1006 l_msnt_tbl(l_tbl_index).creation_date := sysdate;
1007 l_msnt_tbl(l_tbl_index).last_update_date := sysdate;
1008
1009 csp_pp_util.insert_msnt(
1010 x_return_status => l_return_status
1011 ,p_msnt_tbl => l_msnt_tbl
1012 ,p_msnt_tbl_size => 1
1013 );
1014
1015 l_index := l_index + 1;
1016 END LOOP; -- end the while loop
1017 --Delete the existing serial temp records
1018 delete from mtl_serial_numbers_temp
1019 where transaction_temp_id = l_temp_id_to_del
1020 and fm_serial_number = l_fm_serial_to_del
1021 and to_serial_number = nvl(l_to_serial_to_del, to_serial_number);
1022
1023 If sql%notfound Then
1024 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1025 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1026 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1027 fnd_msg_pub.add;
1028 CLOSE l_Get_Serial_Numbers_Csr;
1029 RETURN FALSE;
1030 End If;
1031 End If;
1032 END LOOP process_serial_records;
1033
1034 If l_Get_Serial_Numbers_Csr%ROWCOUNT = 0 THEN
1035 fnd_message.set_name ('CSP', 'CSP_PICK_SERIAL_LOT_FAILURE');
1036 fnd_message.set_token('PICKLIST_HEADER_ID', to_char(p_picklist_header_id), FALSE);
1037 fnd_msg_pub.add;
1038 CLOSE l_Get_Serial_Numbers_Csr;
1039 Return False;
1040 End If;
1041
1042 IF l_Get_Serial_Numbers_Csr%ISOPEN THEN
1043 CLOSE l_Get_Serial_Numbers_Csr;
1044 END IF;
1045
1046 RETURN TRUE;
1047
1048 End transact_serial;
1049
1050 BEGIN
1051 -- Start of API savepoint
1052 SAVEPOINT Confirm_Pick_PUB;
1053 x_return_status := fnd_api.g_ret_sts_success;
1054
1055 -- initialize message list
1056 IF fnd_api.to_boolean(p_init_msg_list) THEN
1057 FND_MSG_PUB.initialize;
1058 END IF;
1059
1060 -- Standard call to check for call compatibility.
1061 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1062 p_api_version_number,
1063 l_api_name,
1064 G_PKG_NAME)
1065 THEN
1066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1067 END IF;
1068
1069 IF p_organization_id IS NULL THEN
1070 fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
1071 fnd_message.set_token ('PARAMETER', 'p_organization_id', TRUE);
1072 fnd_msg_pub.add;
1073 RAISE EXCP_USER_DEFINED;
1074 ELSE
1075 -- Check whether the organizaton exists.
1076 BEGIN
1077 select organization_id into l_check_existence
1078 from mtl_parameters
1079 where organization_id = p_organization_id;
1080 EXCEPTION
1081 WHEN NO_DATA_FOUND THEN
1082 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
1083 FND_MSG_PUB.ADD;
1084 RAISE EXCP_USER_DEFINED;
1085 WHEN OTHERS THEN
1086 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1087 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
1088 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1089 fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
1090 FND_MSG_PUB.ADD;
1091 RAISE EXCP_USER_DEFINED;
1092 END;
1093 END IF;
1094
1095 IF p_picklist_header_id IS NULL THEN
1096 fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
1097 fnd_message.set_token ('PARAMETER', 'p_picklist_header_id', TRUE);
1098 fnd_msg_pub.add;
1099 RAISE EXCP_USER_DEFINED;
1100 ELSE
1101 -- check whether the organizaton exists.
1102 BEGIN
1103 SELECT picklist_header_id INTO l_check_existence
1104 FROM csp_picklist_headers
1105 WHERE organization_id = p_organization_id
1106 AND picklist_header_id = p_picklist_header_id;
1107 EXCEPTION
1108 WHEN NO_DATA_FOUND THEN
1109 fnd_message.set_name ('CSP', 'CSP_INVALID_PICKLIST_HEADER');
1110 fnd_message.set_token ('HEADER_ID', to_char(p_picklist_header_id), TRUE);
1111 fnd_msg_pub.add;
1112 RAISE EXCP_USER_DEFINED;
1113 WHEN OTHERS THEN
1114 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1115 fnd_message.set_token('ERR_FIELD', 'p_picklist_header_id', TRUE);
1116 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1117 fnd_message.set_token('TABLE', 'csp_picklist_headers', TRUE);
1118 FND_MSG_PUB.ADD;
1119 RAISE EXCP_USER_DEFINED;
1120 END;
1121 END IF;
1122 -- get all picklist lines
1123 OPEN l_Get_Picklist_Lines_Csr;
1124 LOOP <<process_mmtt_records>>
1125 FETCH l_Get_Picklist_Lines_Csr INTO l_picklist_line_rec;
1126 EXIT WHEN l_Get_Picklist_Lines_Csr%NOTFOUND;
1127
1128 Update_Misc_MMTT (
1129 P_Api_Version_Number => p_api_version_number,
1130 P_Init_Msg_List => p_init_msg_list,
1131 P_Commit => fnd_api.g_false,
1132 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1133 p_transaction_temp_id => l_picklist_line_rec.transaction_temp_id,
1134 p_organization_id => p_organization_id,
1135 X_Return_Status => l_return_status,
1136 X_Msg_Count => l_msg_count,
1137 X_Msg_Data => l_msg_data );
1138
1139 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1140 CLOSE l_Get_Mmtt_Csr;
1141 CLOSE l_Get_Picklist_Lines_Csr;
1142 RAISE FND_API.G_EXC_ERROR;
1143 END IF;
1144
1145 -- Find the serial_control_code and the lot_control_code of the record in the mmtt table.
1146 OPEN l_Get_Mmtt_Csr(l_picklist_line_rec.transaction_temp_id);
1147 FETCH l_Get_Mmtt_Csr INTO l_mmtt_rec;
1148
1149 IF l_Get_Mmtt_Csr%NOTFOUND THEN
1150 fnd_message.set_name ('CSP', 'CSP_NO_TXN_RECORD');
1151 fnd_message.set_token ('PICKLIST_ID', to_char(l_picklist_line_rec.picklist_line_id), TRUE);
1152 fnd_msg_pub.add;
1153 CLOSE l_Get_Mmtt_Csr;
1154 CLOSE l_Get_Picklist_Lines_Csr;
1155 RAISE EXCP_USER_DEFINED;
1156 END IF;
1157
1158 --Let's handle the lot control case first.
1159 IF nvl(l_mmtt_rec.item_lot_control_code, 1) <> 1 THEN
1160
1161 OPEN l_Get_Mtlt_Csr(l_picklist_line_rec.transaction_temp_id);
1162 Loop <<process_lot_rec>>
1163 FETCH l_Get_Mtlt_Csr INTO l_lot_number_rec;
1164 Exit When l_Get_Mtlt_Csr%NOTFOUND;
1165
1166 If nvl(l_mmtt_rec.item_serial_control_code, 1) in (2, 5) Then
1167
1168 -- the item is also under serial control, find out the serial number in the mtl_serial_numbers_temp
1169 -- and insert it into the csp_picklist_serial_lots along with the lot number.
1170 IF not (transact_serial(l_lot_number_rec.serial_transaction_temp_id)) THEN
1171 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1172 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1173 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1174 fnd_msg_pub.add;
1175 CLOSE l_Get_Mmtt_Csr;
1176 CLOSE l_Get_Picklist_Lines_Csr;
1177 RAISE EXCP_USER_DEFINED;
1178 END IF;
1179
1180 Else
1181 -- the item is only under lot control, insert the lot number into the csp_picklist_serial_lots
1182 CSP_Pick_SL_Util.Validate_And_Write (
1183 P_Api_Version_Number => l_api_version_number,
1184 P_Init_Msg_List => FND_API.G_TRUE,
1185 P_Commit => l_commit,
1186 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1187 p_action_code => 0,
1188 px_PICKLIST_SERIAL_LOT_ID => l_picklist_serial_lot_id,
1189 p_CREATED_BY => G_USER_ID,
1190 p_CREATION_DATE => sysdate,
1191 p_LAST_UPDATED_BY => G_USER_ID,
1192 p_LAST_UPDATE_DATE => sysdate,
1193 p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
1194 p_PICKLIST_LINE_ID => l_picklist_line_rec.picklist_line_id,
1195 p_ORGANIZATION_ID => p_organization_id,
1196 p_INVENTORY_ITEM_ID => l_picklist_line_rec.inventory_item_id,
1197 p_QUANTITY => l_lot_number_rec.transaction_quantity,
1198 p_LOT_NUMBER => l_lot_number_rec.lot_number,
1199 p_SERIAL_NUMBER => null,
1200 X_Return_Status => l_return_status,
1201 X_Msg_Count => l_msg_count,
1202 X_Msg_Data => l_msg_data
1203 );
1204 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1205 CLOSE l_Get_Mmtt_Csr;
1206 CLOSE l_Get_Picklist_Lines_Csr;
1207 RAISE FND_API.G_EXC_ERROR;
1208 END IF;
1209 End If;
1210 End loop process_lot_rec;
1211
1212 IF l_Get_Mtlt_Csr%ROWCOUNT = 0 THEN
1213 fnd_message.set_name ('CSP', 'CSP_PICK_SERIAL_LOT_FAILURE');
1214 fnd_message.set_token('PICKLIST_HEADER_ID', to_char(p_picklist_header_id), FALSE);
1215 fnd_msg_pub.ADD;
1216 CLOSE l_Get_Mmtt_Csr;
1217 CLOSE l_Get_Picklist_Lines_Csr;
1218 RAISE EXCP_USER_DEFINED;
1219 ELSE
1220 CLOSE l_Get_Mtlt_Csr;
1221 END IF;
1222 ELSE
1223 l_lot_number_rec.transaction_quantity := null;
1224 l_lot_number_rec.lot_number := null;
1225
1226 -- the item is not under lot control. It can either be under serial control or no control at all.
1227 If nvl(l_mmtt_rec.item_serial_control_code, 1) in (2, 5) Then
1228 -- the item is under serial control, find out the serial number in the mtl_serial_numbers_temp
1229 -- and insert it into the csp_picklist_serial_lots.
1230 IF not (transact_serial(l_picklist_line_rec.transaction_temp_id)) THEN
1231 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1232 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1233 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1234 fnd_msg_pub.add;
1235 CLOSE l_Get_Mmtt_Csr;
1236 CLOSE l_Get_Picklist_Lines_Csr;
1237 RAISE EXCP_USER_DEFINED;
1238 END IF;
1239 Else -- the item is neither under serial control nor lot control. do nothing.
1240 NULL;
1241 End if;
1242 End If;
1243
1244 IF l_Get_Mmtt_Csr%ISOPEN THEN
1245 CLOSE l_Get_Mmtt_Csr;
1246 END IF;
1247 END LOOP process_mmtt_records;
1248
1249 IF l_Get_Picklist_Lines_Csr%ISOPEN THEN
1250 CLOSE l_Get_Picklist_Lines_Csr;
1251 END IF;
1252
1253 -- update the quantity_detailed in the mtl_txn_request_lines
1254 /* Save_Pick (
1255 P_Api_Version_Number => l_api_version_number
1256 ,P_Init_Msg_List => FND_API.G_TRUE
1257 ,P_Commit => FND_API.G_FALSE
1258 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1259 ,p_picklist_header_id => p_picklist_header_id
1260 ,p_organization_id => p_organization_id
1261 ,x_return_status => l_return_status
1262 ,x_msg_count => l_msg_count
1263 ,x_msg_data => l_msg_data
1264 );
1265
1266 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1267 RAISE FND_API.G_EXC_ERROR;
1268 END IF;
1269 */
1270 IF fnd_api.to_boolean(p_commit) THEN
1271 commit work;
1272 END IF;
1273
1274 EXCEPTION
1275 WHEN EXCP_USER_DEFINED THEN
1276 Rollback to Confirm_Pick_PUB;
1277 fnd_msg_pub.count_and_get
1278 ( p_count => x_msg_count
1279 , p_data => x_msg_data);
1280 x_return_status := FND_API.G_RET_STS_ERROR;
1281
1282 WHEN FND_API.G_EXC_ERROR THEN
1283 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1284 P_API_NAME => L_API_NAME
1285 ,P_PKG_NAME => G_PKG_NAME
1286 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1287 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1288 ,X_MSG_COUNT => X_MSG_COUNT
1289 ,X_MSG_DATA => X_MSG_DATA
1290 ,X_RETURN_STATUS => X_RETURN_STATUS);
1291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1292 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1293 P_API_NAME => L_API_NAME
1294 ,P_PKG_NAME => G_PKG_NAME
1295 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1296 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1297 ,X_MSG_COUNT => X_MSG_COUNT
1298 ,X_MSG_DATA => X_MSG_DATA
1299 ,X_RETURN_STATUS => X_RETURN_STATUS);
1300 WHEN OTHERS THEN
1301 Rollback to Confirm_Pick_PUB;
1302 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1303 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1304 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1305 fnd_msg_pub.add;
1306 fnd_msg_pub.count_and_get
1307 ( p_count => x_msg_count
1308 , p_data => x_msg_data);
1309 x_return_status := fnd_api.g_ret_sts_error;
1310
1311 END Confirm_Pick;
1312
1313 Procedure Update_Misc_MMTT (
1314 -- Start of Comments
1315 -- Procedure : Update_Misc_MMTT
1316 -- Purpose : This procedure updates the transaction source type, transaction type, and transaction action of the
1317 -- a mmtt temp table to 13 (Inventory), 2 (Inventory sub transfer) and 2 (Subinventory transfer), respectively.
1318 -- This procedure also updates the item_lot_control_code and the item_serial_control_code to that in the
1319 -- mtl_system_items table.
1320 --
1321 -- History :
1322 -- UserID Date Comments
1323 -- ----------- -------- --------------------------
1324 -- klou 04/25/00 Created.
1325 --
1326 -- NOTES:
1327 --
1328 --End of Comments
1329 P_Api_Version_Number IN NUMBER
1330 ,P_Init_Msg_List IN VARCHAR2
1331 ,P_Commit IN VARCHAR2
1332 ,p_validation_level IN NUMBER
1333 ,p_transaction_temp_id IN NUMBER
1334 ,p_organization_id IN NUMBER
1335 ,x_return_status OUT NOCOPY VARCHAR2
1336 ,x_msg_count OUT NOCOPY NUMBER
1337 ,x_msg_data OUT NOCOPY VARCHAR2
1338 )
1339
1340 IS
1341
1342 l_api_version_number CONSTANT NUMBER := 1.0;
1343 l_api_name CONSTANT VARCHAR2(50) := 'Update_Misc_MMTT';
1344 l_msg_data VARCHAR2(300);
1345 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
1346 l_check_existence NUMBER := 0;
1347 l_return_status VARCHAR2(1);
1348 l_msg_count NUMBER := 0;
1349 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1350 l_item_serial_control_code NUMBER;
1351 l_item_lot_control_code NUMBER;
1352 EXCP_USER_DEFINED EXCEPTION;
1353
1354 l_csp_mtltxn_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
1355
1356 CURSOR l_ml_records IS
1357 SELECT TRANSACTION_HEADER_ID ,
1358 TRANSACTION_TEMP_ID ,
1359 SOURCE_CODE ,
1360 SOURCE_LINE_ID ,
1361 TRANSACTION_MODE ,
1362 LOCK_FLAG ,
1363 LAST_UPDATE_DATE ,
1364 LAST_UPDATED_BY ,
1365 CREATION_DATE ,
1366 CREATED_BY ,
1367 LAST_UPDATE_LOGIN ,
1368 REQUEST_ID ,
1369 PROGRAM_APPLICATION_ID ,
1370 PROGRAM_ID ,
1371 PROGRAM_UPDATE_DATE ,
1372 INVENTORY_ITEM_ID ,
1373 REVISION ,
1374 ORGANIZATION_ID ,
1375 SUBINVENTORY_CODE ,
1376 LOCATOR_ID ,
1377 TRANSACTION_QUANTITY ,
1378 PRIMARY_QUANTITY ,
1379 TRANSACTION_UOM ,
1380 TRANSACTION_COST ,
1381 TRANSACTION_TYPE_ID ,
1382 TRANSACTION_ACTION_ID ,
1383 TRANSACTION_SOURCE_TYPE_ID ,
1384 TRANSACTION_SOURCE_ID ,
1385 TRANSACTION_SOURCE_NAME ,
1386 TRANSACTION_DATE ,
1387 ACCT_PERIOD_ID ,
1388 DISTRIBUTION_ACCOUNT_ID ,
1389 TRANSACTION_REFERENCE ,
1390 REQUISITION_LINE_ID ,
1391 REQUISITION_DISTRIBUTION_ID ,
1392 REASON_ID ,
1393 LOT_NUMBER ,
1394 LOT_EXPIRATION_DATE ,
1395 SERIAL_NUMBER ,
1396 RECEIVING_DOCUMENT ,
1397 DEMAND_ID ,
1398 RCV_TRANSACTION_ID ,
1399 MOVE_TRANSACTION_ID ,
1400 COMPLETION_TRANSACTION_ID ,
1401 WIP_ENTITY_TYPE ,
1402 SCHEDULE_ID ,
1403 REPETITIVE_LINE_ID ,
1404 EMPLOYEE_CODE ,
1405 PRIMARY_SWITCH ,
1406 SCHEDULE_UPDATE_CODE ,
1407 SETUP_TEARDOWN_CODE ,
1408 ITEM_ORDERING ,
1409 NEGATIVE_REQ_FLAG ,
1410 OPERATION_SEQ_NUM ,
1411 PICKING_LINE_ID ,
1412 TRX_SOURCE_LINE_ID ,
1413 TRX_SOURCE_DELIVERY_ID ,
1414 PHYSICAL_ADJUSTMENT_ID ,
1415 CYCLE_COUNT_ID ,
1416 RMA_LINE_ID ,
1417 CUSTOMER_SHIP_ID ,
1418 CURRENCY_CODE ,
1419 CURRENCY_CONVERSION_RATE ,
1420 CURRENCY_CONVERSION_TYPE ,
1421 CURRENCY_CONVERSION_DATE ,
1422 USSGL_TRANSACTION_CODE ,
1423 VENDOR_LOT_NUMBER ,
1424 ENCUMBRANCE_ACCOUNT ,
1425 ENCUMBRANCE_AMOUNT ,
1426 SHIP_TO_LOCATION ,
1427 SHIPMENT_NUMBER ,
1428 TRANSFER_COST ,
1429 TRANSPORTATION_COST ,
1430 TRANSPORTATION_ACCOUNT ,
1431 FREIGHT_CODE ,
1432 CONTAINERS ,
1433 WAYBILL_AIRBILL ,
1434 EXPECTED_ARRIVAL_DATE ,
1435 TRANSFER_SUBINVENTORY ,
1436 TRANSFER_ORGANIZATION ,
1437 TRANSFER_TO_LOCATION ,
1438 NEW_AVERAGE_COST ,
1439 VALUE_CHANGE ,
1440 PERCENTAGE_CHANGE ,
1441 MATERIAL_ALLOCATION_TEMP_ID ,
1442 DEMAND_SOURCE_HEADER_ID ,
1443 DEMAND_SOURCE_LINE ,
1444 DEMAND_SOURCE_DELIVERY ,
1445 ITEM_SEGMENTS ,
1446 ITEM_DESCRIPTION ,
1447 ITEM_TRX_ENABLED_FLAG ,
1448 ITEM_LOCATION_CONTROL_CODE ,
1449 ITEM_RESTRICT_SUBINV_CODE ,
1450 ITEM_RESTRICT_LOCATORS_CODE ,
1451 ITEM_REVISION_QTY_CONTROL_CODE ,
1452 ITEM_PRIMARY_UOM_CODE ,
1453 ITEM_UOM_CLASS ,
1454 ITEM_SHELF_LIFE_CODE ,
1455 ITEM_SHELF_LIFE_DAYS ,
1456 ITEM_LOT_CONTROL_CODE ,
1457 ITEM_SERIAL_CONTROL_CODE ,
1458 ITEM_INVENTORY_ASSET_FLAG ,
1459 ALLOWED_UNITS_LOOKUP_CODE ,
1460 DEPARTMENT_ID ,
1461 DEPARTMENT_CODE ,
1462 WIP_SUPPLY_TYPE ,
1463 SUPPLY_SUBINVENTORY ,
1464 SUPPLY_LOCATOR_ID ,
1465 VALID_SUBINVENTORY_FLAG ,
1466 VALID_LOCATOR_FLAG ,
1467 LOCATOR_SEGMENTS ,
1468 CURRENT_LOCATOR_CONTROL_CODE ,
1469 NUMBER_OF_LOTS_ENTERED ,
1470 WIP_COMMIT_FLAG ,
1471 NEXT_LOT_NUMBER ,
1472 LOT_ALPHA_PREFIX ,
1473 NEXT_SERIAL_NUMBER ,
1474 SERIAL_ALPHA_PREFIX ,
1475 SHIPPABLE_FLAG ,
1476 POSTING_FLAG ,
1477 REQUIRED_FLAG ,
1478 PROCESS_FLAG ,
1479 ERROR_CODE ,
1480 ERROR_EXPLANATION ,
1481 ATTRIBUTE_CATEGORY ,
1482 ATTRIBUTE1 ,
1483 ATTRIBUTE2 ,
1484 ATTRIBUTE3 ,
1485 ATTRIBUTE4 ,
1486 ATTRIBUTE5 ,
1487 ATTRIBUTE6 ,
1488 ATTRIBUTE7 ,
1489 ATTRIBUTE8 ,
1490 ATTRIBUTE9 ,
1491 ATTRIBUTE10 ,
1492 ATTRIBUTE11 ,
1493 ATTRIBUTE12 ,
1494 ATTRIBUTE13 ,
1495 ATTRIBUTE14 ,
1496 ATTRIBUTE15 ,
1497 MOVEMENT_ID ,
1498 RESERVATION_QUANTITY ,
1499 SHIPPED_QUANTITY ,
1500 TRANSACTION_LINE_NUMBER ,
1501 TASK_ID ,
1502 TO_TASK_ID ,
1503 SOURCE_TASK_ID ,
1504 PROJECT_ID ,
1505 SOURCE_PROJECT_ID ,
1506 PA_EXPENDITURE_ORG_ID ,
1507 TO_PROJECT_ID ,
1508 EXPENDITURE_TYPE ,
1509 FINAL_COMPLETION_FLAG ,
1510 TRANSFER_PERCENTAGE ,
1511 TRANSACTION_SEQUENCE_ID ,
1512 MATERIAL_ACCOUNT ,
1513 MATERIAL_OVERHEAD_ACCOUNT ,
1514 RESOURCE_ACCOUNT ,
1515 OUTSIDE_PROCESSING_ACCOUNT ,
1516 OVERHEAD_ACCOUNT ,
1517 FLOW_SCHEDULE ,
1518 COST_GROUP_ID ,
1519 DEMAND_CLASS ,
1520 QA_COLLECTION_ID ,
1521 KANBAN_CARD_ID ,
1522 OVERCOMPLETION_TRANSACTION_ID ,
1523 OVERCOMPLETION_PRIMARY_QTY ,
1524 OVERCOMPLETION_TRANSACTION_QTY ,
1525 --PROCESS_TYPE , --removed 01/13/00. process_type does not exist in the mmtt table.
1526 END_ITEM_UNIT_NUMBER ,
1527 SCHEDULED_PAYBACK_DATE ,
1528 LINE_TYPE_CODE ,
1529 PARENT_TRANSACTION_TEMP_ID ,
1530 PUT_AWAY_STRATEGY_ID ,
1531 PUT_AWAY_RULE_ID ,
1532 PICK_STRATEGY_ID ,
1533 PICK_RULE_ID ,
1534 COMMON_BOM_SEQ_ID ,
1535 COMMON_ROUTING_SEQ_ID ,
1536 COST_TYPE_ID ,
1537 ORG_COST_GROUP_ID ,
1538 MOVE_ORDER_LINE_ID ,
1539 TASK_GROUP_ID ,
1540 PICK_SLIP_NUMBER ,
1541 RESERVATION_ID ,
1542 TRANSACTION_STATUS ,
1543 STANDARD_OPERATION_ID ,
1544 TASK_PRIORITY ,
1545 -- ADDED by phegde 02/23
1546 WMS_TASK_TYPE ,
1547 PARENT_LINE_ID
1548 --SOURCE_LOT_NUMBER
1549 FROM mtl_material_transactions_temp
1550 WHERE transaction_temp_id = p_transaction_temp_id
1551 AND organization_id = p_organization_id;
1552 BEGIN
1553 SAVEPOINT Update_Misc_MMTT_PUB;
1554 x_return_status := fnd_api.g_ret_sts_success;
1555
1556 -- initialize message list
1557 IF fnd_api.to_boolean(p_init_msg_list) THEN
1558 FND_MSG_PUB.initialize;
1559 END IF;
1560
1561 -- Standard call to check for call compatibility.
1562 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1563 p_api_version_number,
1564 l_api_name,
1565 G_PKG_NAME)
1566 THEN
1567 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1568 END IF;
1569
1570 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
1571 IF p_organization_id IS NULL THEN
1572 fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
1573 fnd_message.set_token ('PARAMETER', 'p_organization_id', TRUE);
1574 fnd_msg_pub.add;
1575 RAISE EXCP_USER_DEFINED;
1576 ELSE
1577 -- check whether the organizaton exists.
1578 BEGIN
1579 select organization_id into l_check_existence
1580 from mtl_parameters
1581 where organization_id = p_organization_id;
1582 EXCEPTION
1583 WHEN NO_DATA_FOUND THEN
1584 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
1585 FND_MSG_PUB.ADD;
1586 RAISE EXCP_USER_DEFINED;
1587 WHEN OTHERS THEN
1588 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1589 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
1590 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1591 fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
1592 FND_MSG_PUB.ADD;
1593 RAISE EXCP_USER_DEFINED;
1594 END;
1595 END IF;
1596 END IF;
1597
1598 OPEN l_ml_records;
1599 FETCH l_ml_records INTO l_csp_mtltxn_rec;
1600
1601 IF l_ml_records%NOTFOUND THEN
1602 fnd_message.set_name ('CSP', 'CSP_NO_MO_TXN_RECORD');
1603 fnd_msg_pub.add;
1604 CLOSE l_ml_records;
1605 RAISE EXCP_USER_DEFINED;
1606 END IF;
1607
1608 CLOSE l_ml_records;
1609
1610 l_csp_mtltxn_rec.transaction_source_type_id := 13; -- Inventory
1611 l_csp_mtltxn_rec.transaction_type_id := 2; -- subinventory transfer type
1612 l_csp_mtltxn_rec.transaction_action_id := 2; -- subinventory tranfer
1613
1614 -- Check whether the item is under serial control and / or lot control.
1615 BEGIN
1616 SELECT nvl(lot_control_code, 1), nvl(serial_number_control_code,1)
1617 INTO l_item_lot_control_code, l_item_serial_control_code
1618 FROM MTL_SYSTEM_ITEMS_KFV
1619 WHERE inventory_item_id = l_csp_mtltxn_rec.inventory_item_id
1620 AND organization_id = l_csp_mtltxn_rec.organization_id;
1621
1622 IF nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) <> l_item_lot_control_code
1623 OR nvl(l_csp_mtltxn_rec.item_serial_control_code, 1) <> l_item_serial_control_code THEN
1624 l_csp_mtltxn_rec.item_lot_control_code := l_item_lot_control_code;
1625 l_csp_mtltxn_rec.item_serial_control_code := l_item_serial_control_code;
1626
1627 END IF;
1628 END;
1629
1630 CSP_Material_Transactions_PVT.Update_material_transactions(
1631 P_Api_Version_Number => p_api_version_number,
1632 P_Init_Msg_List => p_init_msg_list,
1633 P_Commit => fnd_api.g_false,
1634 p_validation_level => l_validation_level,
1635 P_CSP_Rec => l_csp_mtltxn_rec,
1636 X_Return_Status => l_return_status,
1637 X_Msg_Count => l_msg_count,
1638 X_Msg_Data => l_msg_data);
1639
1640 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1641 RAISE FND_API.G_EXC_ERROR;
1642 END IF;
1643
1644 x_return_status := l_return_status;
1645
1646 IF fnd_api.to_boolean(P_Commit) THEN
1647 commit work;
1648 END IF;
1649
1650 EXCEPTION
1651 WHEN EXCP_USER_DEFINED THEN
1652 Rollback to Update_Misc_MMTT_PUB;
1653 fnd_msg_pub.count_and_get
1654 ( p_count => x_msg_count
1655 , p_data => x_msg_data);
1656 x_return_status := FND_API.G_RET_STS_ERROR;
1657 WHEN FND_API.G_EXC_ERROR THEN
1658 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1659 P_API_NAME => L_API_NAME
1660 ,P_PKG_NAME => G_PKG_NAME
1661 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1662 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1663 ,X_MSG_COUNT => X_MSG_COUNT
1664 ,X_MSG_DATA => X_MSG_DATA
1665 ,X_RETURN_STATUS => X_RETURN_STATUS);
1666 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1667 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1668 P_API_NAME => L_API_NAME
1669 ,P_PKG_NAME => G_PKG_NAME
1670 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1671 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1672 ,X_MSG_COUNT => X_MSG_COUNT
1673 ,X_MSG_DATA => X_MSG_DATA
1674 ,X_RETURN_STATUS => X_RETURN_STATUS);
1675 WHEN OTHERS THEN
1676 Rollback to Update_Misc_MMTT_PUB;
1677 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1678 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1679 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1680 fnd_msg_pub.add;
1681 fnd_msg_pub.count_and_get
1682 ( p_count => x_msg_count
1683 , p_data => x_msg_data);
1684 x_return_status := fnd_api.g_ret_sts_error;
1685
1686 END Update_Misc_MMTT;
1687
1688
1689 ------------------------
1690 -- Start of Comments
1691 -- Procedure : Save_Pick
1692 -- Purpose : This procedure saves the headers and lines for the specified
1693 -- picklist and updates the quantity detailed in mtl_txn_request_lines
1694 --
1695 -- History :
1696 -- UserID Date Comments
1697 -- ----------- -------- --------------------------
1698 -- phegde 02/01/2000 Created.
1699 --
1700 -- NOTES:
1701 --
1702 --End of Comments
1703
1704 Procedure Save_Pick (
1705 P_Api_Version_Number IN NUMBER
1706 ,P_Init_Msg_List IN VARCHAR2
1707 ,P_Commit IN VARCHAR2
1708 ,p_validation_level IN NUMBER
1709 ,p_picklist_header_id IN NUMBER
1710 ,p_organization_id IN NUMBER
1711 ,x_return_status OUT NOCOPY VARCHAR2
1712 ,x_msg_count OUT NOCOPY NUMBER
1713 ,x_msg_data OUT NOCOPY VARCHAR2
1714 )
1715
1716 IS
1717 l_api_version_number CONSTANT NUMBER := 1.0;
1718 l_api_name CONSTANT VARCHAR2(30) := 'Save_Pick';
1719 l_return_status VARCHAR2(1);
1720 l_msg_count NUMBER := 0;
1721 l_msg_data VARCHAR2(500);
1722 l_commit VARCHAR2(1) := fnd_api.g_false;
1723 l_check_existence NUMBER := 0;
1724 EXCP_USER_DEFINED EXCEPTION;
1725
1726 CURSOR pickline_cur IS
1727 SELECT sum(quantity_picked) qty_det,
1728 line_id
1729 FROM csp_picklist_lines
1730 WHERE picklist_header_id = p_picklist_header_id
1731 GROUP BY line_id;
1732
1733 pickline_rec pickline_cur%ROWTYPE;
1734 l_trolin_rec INV_Move_Order_PUB.Trolin_Rec_Type;
1735 BEGIN
1736 -- Start of API savepoint
1737 SAVEPOINT Save_Pick_PUB;
1738 x_return_status := fnd_api.g_ret_sts_success;
1739
1740 -- initialize message list
1741 IF fnd_api.to_boolean(p_init_msg_list) THEN
1742 FND_MSG_PUB.initialize;
1743 END IF;
1744
1745 -- Standard call to check for call compatibility.
1746 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1747 p_api_version_number,
1748 l_api_name,
1749 G_PKG_NAME)
1750 THEN
1751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1752 END IF;
1753
1754 IF p_organization_id IS NULL THEN
1755 fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
1756 fnd_message.set_token ('PARAMETER', 'p_organization_id', TRUE);
1757 fnd_msg_pub.add;
1758 RAISE EXCP_USER_DEFINED;
1759 ELSE
1760 -- check whether the organizaton exists.
1761 BEGIN
1762 SELECT organization_id into l_check_existence
1763 FROM mtl_parameters
1764 WHERE organization_id = p_organization_id;
1765 EXCEPTION
1766 WHEN NO_DATA_FOUND THEN
1767 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
1768 FND_MSG_PUB.ADD;
1769 RAISE EXCP_USER_DEFINED;
1770 WHEN OTHERS THEN
1771 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1772 fnd_message.set_token('ERR_FIELD', 'p_organization_id', TRUE);
1773 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
1774 fnd_message.set_token('TABLE', 'mtl_organizations', TRUE);
1775 FND_MSG_PUB.ADD;
1776 RAISE EXCP_USER_DEFINED;
1777 END;
1778 END IF;
1779
1780 -- get all lines for this picklist_header
1781 OPEN pickline_cur;
1782
1783 LOOP
1784 FETCH pickline_cur INTO pickline_rec;
1785 EXIT WHEN pickline_cur%NOTFOUND;
1786
1787 l_trolin_rec := INV_Trolin_util.Query_Row( pickline_rec.line_id );
1788 l_trolin_rec.quantity_detailed := pickline_rec.qty_det;
1789 l_trolin_rec.last_update_date := SYSDATE;
1790 l_trolin_rec.last_updated_by := FND_GLOBAL.USER_ID;
1791 l_trolin_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
1792
1793 INV_Trolin_Util.Update_Row(l_trolin_rec);
1794 commit;
1795
1796 END LOOP;
1797
1798 CLOSE pickline_cur;
1799 x_return_status := fnd_api.G_ret_sts_success;
1800
1801 EXCEPTION
1802 WHEN EXCP_USER_DEFINED THEN
1803 Rollback to Save_Pick_PUB;
1804 fnd_msg_pub.count_and_get
1805 ( p_count => x_msg_count
1806 , p_data => x_msg_data);
1807 x_return_status := FND_API.G_RET_STS_ERROR;
1808
1809 WHEN FND_API.G_EXC_ERROR THEN
1810 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1811 P_API_NAME => L_API_NAME
1812 ,P_PKG_NAME => G_PKG_NAME
1813 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1814 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1815 ,X_MSG_COUNT => X_MSG_COUNT
1816 ,X_MSG_DATA => X_MSG_DATA
1817 ,X_RETURN_STATUS => X_RETURN_STATUS);
1818 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1819 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1820 P_API_NAME => L_API_NAME
1821 ,P_PKG_NAME => G_PKG_NAME
1822 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1823 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1824 ,X_MSG_COUNT => X_MSG_COUNT
1825 ,X_MSG_DATA => X_MSG_DATA
1826 ,X_RETURN_STATUS => X_RETURN_STATUS);
1827 WHEN OTHERS THEN
1828 Rollback to Save_Pick_PUB;
1829 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1830 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1831 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1832 fnd_msg_pub.add;
1833 fnd_msg_pub.count_and_get
1834 ( p_count => x_msg_count
1835 , p_data => x_msg_data);
1836 x_return_status := fnd_api.g_ret_sts_error;
1837
1838 END Save_Pick;
1839 Procedure Issue_Savepoint(p_Savepoint Varchar2) Is
1840 Begin
1841 SAVEPOINT p_Savepoint;
1842 End;
1843 Procedure Issue_Rollback(p_Savepoint Varchar2) Is
1844 Begin
1845 ROLLBACK TO p_Savepoint;
1846 End;
1847 Procedure Issue_Commit Is
1848 Begin
1849 COMMIT;
1850 End;
1851 Function Calculate_Min_Max(p_Subinventory Varchar2,
1852 p_Organization_Id Number,
1853 p_Edq_factor Number,
1854 p_Service_Level Number,
1855 p_Item_Cost Number,
1856 p_Awu Number,
1857 p_Lead_time Number,
1858 p_Standard_Deviation Number,
1859 p_Safety_Stock_Flag Varchar2,
1860 p_Asl_Flag Varchar2) RETURN NUMBER Is
1861 Cursor c_Safety_Factor(p_SL Number,p_Exp Number) Is
1862 Select SAFETY_FACTOR
1863 From CSP_SAFETY_FACTORS
1864 Where EXPOSURES = p_Exp
1865 And SERVICE_LEVEL = p_SL;
1866
1867 Cursor c_factor_Minmax Is
1868 Select MIN(Exposures) , MAX(Exposures)
1869 From CSP_SAFETY_FACTORS;
1870
1871 l_min Number;
1872 l_max Number;
1873 l_Safety_factor Number;
1874 l_Exposures Number := 0;
1875 l_Safety_Stock Number := 0;
1876 l_Edq Number := 0;
1877 l_Reorder_Point Number := 0;
1878 l_Service_Level Number;
1879 l_Edq_Factor Number;
1880 l_Asl_Flag Varchar2(1);
1881 l_Safety_Stock_Flag Varchar2(1);
1882 Begin
1883 l_Service_Level := p_Service_Level;
1884 l_Edq_factor := p_Edq_Factor;
1885 l_Safety_Stock_Flag := p_Safety_Stock_Flag;
1886 l_Asl_Flag := p_Asl_Flag;
1887
1888 G_SERVICE_LEVEL := l_Service_Level;
1889 G_EDQ_FACTOR := l_Edq_Factor;
1890 G_ASL_FLAG := l_Asl_Flag;
1891 G_SAFETY_STOCK_FLAG := l_Safety_Stock_Flag;
1892 -- Calculate Edq
1893 If nvl(p_Item_Cost,0) > 0 Then
1894 l_Edq := ROUND(l_Edq_Factor * (SQRT(52 * p_Awu * p_Item_Cost)/p_Item_Cost),4);
1895 Else l_Edq := 0;
1896 End If;
1897 -- Calculate Exposures
1898 If nvl(l_Edq,0) > 0 Then
1899 l_Exposures := ROUND(p_Awu * 52/l_Edq);
1900 Else l_Exposures := 0;
1901 End If;
1902 --- Get Safety Factor
1903 Open c_Factor_minmax;
1904 Fetch c_Factor_minmax INTO l_Min,l_max;
1905 Close c_Factor_minmax;
1906 If l_Exposures < l_min Then
1907 l_Exposures := l_min;
1908 Elsif l_Exposures > l_Max Then
1909 l_Exposures := l_Max;
1910 End If;
1911 Open c_Safety_Factor(l_Service_Level,l_Exposures);
1912 Fetch c_Safety_Factor INTO l_Safety_Factor;
1913 Close c_Safety_Factor;
1914 G_Safety_Factor := l_Safety_factor;
1915 -- Calculate Safety Stock
1916 If nvl(l_Safety_Stock_flag,'N') = 'N' Then
1917 l_Safety_Stock := 0;
1918 Else l_safety_Stock := ROUND(nvl(l_Safety_Factor,0) * nvl(p_Standard_Deviation,0),4);
1919 End If;
1920 -- Calculate Reorder Point
1921 l_Reorder_Point := nvl(p_Awu/7 * p_Lead_Time,0) + nvl(l_Safety_Stock,0);
1922 -- Calculate Minimum and Maximum Quantities
1923 G_min_Quantity := ROUND(nvl(l_Reorder_Point,0));
1924 G_Max_Quantity := ROUND(nvl(l_Reorder_Point,0) + nvl(l_Edq,0));
1925 G_safety_Stock := l_safety_stock;
1926
1927 -- If max is 0, min must be 0
1928 -- If max > 0, min must be > 0
1929 if nvl(g_max_quantity,0) = 0 then
1930 g_min_quantity := 0;
1931 else
1932 g_min_quantity := greatest(g_min_quantity,1);
1933 end if;
1934
1935 return 0;
1936 Exception
1937 When OTHERS Then
1938 return 1;
1939 End;
1940
1941 Function get_min_quantity(p_Subinventory Varchar2,
1942 p_Organization_Id Number,
1943 p_Edq_factor Number,
1944 p_Service_Level Number,
1945 p_Item_Cost Number,
1946 p_Awu Number,
1947 p_Lead_time Number,
1948 p_Standard_Deviation Number,
1949 p_Safety_Stock_Flag Varchar2,
1950 p_Asl_Flag Varchar2) RETURN NUMBER Is
1951
1952 Cursor c_Safety_Factor(p_SL Number,p_Exp Number) Is
1953 Select SAFETY_FACTOR
1954 From CSP_SAFETY_FACTORS
1955 Where EXPOSURES = p_Exp
1956 And SERVICE_LEVEL = p_SL;
1957
1958 Cursor c_factor_Minmax Is
1959 Select MIN(Exposures) , MAX(Exposures)
1960 From CSP_SAFETY_FACTORS;
1961
1962 l_min Number;
1963 l_max Number;
1964 l_Safety_factor Number;
1965 l_Exposures Number := 0;
1966 l_Safety_Stock Number := 0;
1967 l_Edq Number := 0;
1968 l_Reorder_Point Number := 0;
1969 l_Service_Level Number;
1970 l_Edq_Factor Number;
1971 l_Asl_Flag Varchar2(1);
1972 l_Safety_Stock_Flag Varchar2(1);
1973 Begin
1974 l_Service_Level := p_Service_Level;
1975 l_Edq_factor := p_Edq_Factor;
1976 l_Safety_Stock_Flag := p_Safety_Stock_Flag;
1977 l_Asl_Flag := p_Asl_Flag;
1978
1979 -- Calculate Edq
1980 If nvl(p_Item_Cost,0) > 0 Then
1981 l_Edq := ROUND(l_Edq_Factor * (SQRT(52 * p_Awu * p_Item_Cost)/p_Item_Cost),4);
1982 Else l_Edq := 0;
1983 End If;
1984 -- Calculate Exposures
1985 If nvl(l_Edq,0) > 0 Then
1986 l_Exposures := ROUND(p_Awu * 52/l_Edq);
1987 Else l_Exposures := 0;
1988 End If;
1989 --- Get Safety Factor
1990 Open c_Factor_minmax;
1991 Fetch c_Factor_minmax INTO l_Min,l_max;
1992 Close c_Factor_minmax;
1993 If l_Exposures < l_min Then
1994 l_Exposures := l_min;
1995 Elsif l_Exposures > l_Max Then
1996 l_Exposures := l_Max;
1997 End If;
1998 Open c_Safety_Factor(l_Service_Level,l_Exposures);
1999 Fetch c_Safety_Factor INTO l_Safety_Factor;
2000 Close c_Safety_Factor;
2001 G_Safety_Factor := l_Safety_factor;
2002 -- Calculate Safety Stock
2003 If nvl(l_Safety_Stock_flag,'N') = 'N' Then
2004 l_Safety_Stock := 0;
2005 Else l_safety_Stock := ROUND(nvl(l_Safety_Factor,0) * nvl(p_Standard_Deviation,0),4);
2006 End If;
2007 -- Calculate Reorder Point
2008 l_Reorder_Point := nvl(p_Awu/7 * p_Lead_Time,0) + nvl(l_Safety_Stock,0);
2009 -- Calculate Minimum and Maximum Quantities
2010 G_min_Quantity := ROUND(nvl(l_Reorder_Point,0));
2011 G_Max_Quantity := ROUND(nvl(l_Reorder_Point,0) + nvl(l_Edq,0));
2012 G_safety_Stock := l_safety_stock;
2013
2014 -- If max is 0, min must be 0
2015 -- If max > 0, min must be > 0
2016 if nvl(g_max_quantity,0) = 0 then
2017 g_min_quantity := 0;
2018 else
2019 g_min_quantity := greatest(g_min_quantity,1);
2020 end if;
2021
2022 return g_min_quantity;
2023 Exception
2024 When OTHERS Then
2025 return g_min_quantity;
2026 end;
2027
2028 Function get_max_quantity(p_Subinventory Varchar2,
2029 p_Organization_Id Number,
2030 p_Edq_factor Number,
2031 p_Service_Level Number,
2032 p_Item_Cost Number,
2033 p_Awu Number,
2034 p_Lead_time Number,
2035 p_Standard_Deviation Number,
2036 p_Safety_Stock_Flag Varchar2,
2037 p_Asl_Flag Varchar2) RETURN NUMBER Is
2038
2039 Cursor c_Safety_Factor(p_SL Number,p_Exp Number) Is
2040 Select SAFETY_FACTOR
2041 From CSP_SAFETY_FACTORS
2042 Where EXPOSURES = p_Exp
2043 And SERVICE_LEVEL = p_SL;
2044
2045 Cursor c_factor_Minmax Is
2046 Select MIN(Exposures) , MAX(Exposures)
2047 From CSP_SAFETY_FACTORS;
2048
2049 l_min Number;
2050 l_max Number;
2051 l_Safety_factor Number;
2052 l_Exposures Number := 0;
2053 l_Safety_Stock Number := 0;
2054 l_Edq Number := 0;
2055 l_Reorder_Point Number := 0;
2056 l_Service_Level Number;
2057 l_Edq_Factor Number;
2058 l_Asl_Flag Varchar2(1);
2059 l_Safety_Stock_Flag Varchar2(1);
2060 Begin
2061 l_Service_Level := p_Service_Level;
2062 l_Edq_factor := p_Edq_Factor;
2063 l_Safety_Stock_Flag := p_Safety_Stock_Flag;
2064 l_Asl_Flag := p_Asl_Flag;
2065
2066 -- Calculate Edq
2067 If nvl(p_Item_Cost,0) > 0 Then
2068 l_Edq := ROUND(l_Edq_Factor * (SQRT(52 * p_Awu * p_Item_Cost)/p_Item_Cost),4);
2069 Else l_Edq := 0;
2070 End If;
2071 -- Calculate Exposures
2072 If nvl(l_Edq,0) > 0 Then
2073 l_Exposures := ROUND(p_Awu * 52/l_Edq);
2074 Else l_Exposures := 0;
2075 End If;
2076 --- Get Safety Factor
2077 Open c_Factor_minmax;
2078 Fetch c_Factor_minmax INTO l_Min,l_max;
2079 Close c_Factor_minmax;
2080 If l_Exposures < l_min Then
2081 l_Exposures := l_min;
2082 Elsif l_Exposures > l_Max Then
2083 l_Exposures := l_Max;
2084 End If;
2085 Open c_Safety_Factor(l_Service_Level,l_Exposures);
2086 Fetch c_Safety_Factor INTO l_Safety_Factor;
2087 Close c_Safety_Factor;
2088 G_Safety_Factor := l_Safety_factor;
2089 -- Calculate Safety Stock
2090 If nvl(l_Safety_Stock_flag,'N') = 'N' Then
2091 l_Safety_Stock := 0;
2092 Else l_safety_Stock := ROUND(nvl(l_Safety_Factor,0) * nvl(p_Standard_Deviation,0),4);
2093 End If;
2094 -- Calculate Reorder Point
2095 l_Reorder_Point := nvl(p_Awu/7 * p_Lead_Time,0) + nvl(l_Safety_Stock,0);
2096 -- Calculate Minimum and Maximum Quantities
2097 G_min_Quantity := ROUND(nvl(l_Reorder_Point,0));
2098 G_Max_Quantity := ROUND(nvl(l_Reorder_Point,0) + nvl(l_Edq,0));
2099 G_safety_Stock := l_safety_stock;
2100
2101 -- If max is 0, min must be 0
2102 -- If max > 0, min must be > 0
2103 if nvl(g_max_quantity,0) = 0 then
2104 g_min_quantity := 0;
2105 else
2106 g_min_quantity := greatest(g_min_quantity,1);
2107 end if;
2108
2109 return g_max_quantity;
2110 Exception
2111 When OTHERS Then
2112 return g_max_quantity;
2113 end;
2114
2115 Function Get_SAFETY_FACTOR(p_Subinventory Varchar2,
2116 p_Organization_Id Number,
2117 p_Edq_factor Number,
2118 p_Service_Level Number,
2119 p_Item_Cost Number,
2120 p_Awu Number,
2121 p_Lead_time Number,
2122 p_Standard_Deviation Number,
2123 p_Safety_Stock_Flag Varchar2,
2124 p_Asl_Flag Varchar2) RETURN NUMBER Is
2125
2126 Cursor c_Safety_Factor(p_SL Number,p_Exp Number) Is
2127 Select SAFETY_FACTOR
2128 From CSP_SAFETY_FACTORS
2129 Where EXPOSURES = p_Exp
2130 And SERVICE_LEVEL = p_SL;
2131
2132 Cursor c_factor_Minmax Is
2133 Select MIN(Exposures) , MAX(Exposures)
2134 From CSP_SAFETY_FACTORS;
2135
2136 l_min Number;
2137 l_max Number;
2138 l_Safety_factor Number;
2139 l_Exposures Number := 0;
2140 l_Safety_Stock Number := 0;
2141 l_Edq Number := 0;
2142 l_Reorder_Point Number := 0;
2143 l_Service_Level Number;
2144 l_Edq_Factor Number;
2145 l_Asl_Flag Varchar2(1);
2146 l_Safety_Stock_Flag Varchar2(1);
2147 Begin
2148 l_Service_Level := p_Service_Level;
2149 l_Edq_factor := p_Edq_Factor;
2150 l_Safety_Stock_Flag := p_Safety_Stock_Flag;
2151 l_Asl_Flag := p_Asl_Flag;
2152
2153 -- Calculate Edq
2154 If nvl(p_Item_Cost,0) > 0 Then
2155 l_Edq := ROUND(l_Edq_Factor * (SQRT(52 * p_Awu * p_Item_Cost)/p_Item_Cost),4);
2156 Else l_Edq := 0;
2157 End If;
2158 -- Calculate Exposures
2159 If nvl(l_Edq,0) > 0 Then
2160 l_Exposures := ROUND(p_Awu * 52/l_Edq);
2161 Else l_Exposures := 0;
2162 End If;
2163 --- Get Safety Factor
2164 Open c_Factor_minmax;
2165 Fetch c_Factor_minmax INTO l_Min,l_max;
2166 Close c_Factor_minmax;
2167 If l_Exposures < l_min Then
2168 l_Exposures := l_min;
2169 Elsif l_Exposures > l_Max Then
2170 l_Exposures := l_Max;
2171 End If;
2172 Open c_Safety_Factor(l_Service_Level,l_Exposures);
2173 Fetch c_Safety_Factor INTO l_Safety_Factor;
2174 Close c_Safety_Factor;
2175 G_Safety_Factor := l_Safety_factor;
2176
2177 return G_SAFETY_FACTOR;
2178 Exception
2179 When OTHERS Then
2180 return G_SAFETY_FACTOR;
2181 End;
2182
2183 Function Get_SAFETY_STOCK(p_Subinventory Varchar2,
2184 p_Organization_Id Number,
2185 p_Edq_factor Number,
2186 p_Service_Level Number,
2187 p_Item_Cost Number,
2188 p_Awu Number,
2189 p_Lead_time Number,
2190 p_Standard_Deviation Number,
2191 p_Safety_Stock_Flag Varchar2,
2192 p_Asl_Flag Varchar2) RETURN NUMBER Is
2193 Cursor c_Safety_Factor(p_SL Number,p_Exp Number) Is
2194 Select SAFETY_FACTOR
2195 From CSP_SAFETY_FACTORS
2196 Where EXPOSURES = p_Exp
2197 And SERVICE_LEVEL = p_SL;
2198
2199 Cursor c_factor_Minmax Is
2200 Select MIN(Exposures) , MAX(Exposures)
2201 From CSP_SAFETY_FACTORS;
2202
2203 l_min Number;
2204 l_max Number;
2205 l_Safety_factor Number;
2206 l_Exposures Number := 0;
2207 l_Safety_Stock Number := 0;
2208 l_Edq Number := 0;
2209 l_Reorder_Point Number := 0;
2210 l_Service_Level Number;
2211 l_Edq_Factor Number;
2212 l_Asl_Flag Varchar2(1);
2213 l_Safety_Stock_Flag Varchar2(1);
2214 Begin
2215 l_Service_Level := p_Service_Level;
2216 l_Edq_factor := p_Edq_Factor;
2217 l_Safety_Stock_Flag := p_Safety_Stock_Flag;
2218 l_Asl_Flag := p_Asl_Flag;
2219
2220 G_SERVICE_LEVEL := l_Service_Level;
2221 G_EDQ_FACTOR := l_Edq_Factor;
2222 G_ASL_FLAG := l_Asl_Flag;
2223 G_SAFETY_STOCK_FLAG := l_Safety_Stock_Flag;
2224 -- Calculate Edq
2225 If nvl(p_Item_Cost,0) > 0 Then
2226 l_Edq := ROUND(l_Edq_Factor * (SQRT(52 * p_Awu * p_Item_Cost)/p_Item_Cost),4);
2227 Else l_Edq := 0;
2228 End If;
2229 -- Calculate Exposures
2230 If nvl(l_Edq,0) > 0 Then
2231 l_Exposures := ROUND(p_Awu * 52/l_Edq);
2232 Else l_Exposures := 0;
2233 End If;
2234 --- Get Safety Factor
2235 Open c_Factor_minmax;
2236 Fetch c_Factor_minmax INTO l_Min,l_max;
2237 Close c_Factor_minmax;
2238 If l_Exposures < l_min Then
2239 l_Exposures := l_min;
2240 Elsif l_Exposures > l_Max Then
2241 l_Exposures := l_Max;
2242 End If;
2243 Open c_Safety_Factor(l_Service_Level,l_Exposures);
2244 Fetch c_Safety_Factor INTO l_Safety_Factor;
2245 Close c_Safety_Factor;
2246 G_Safety_Factor := l_Safety_factor;
2247 -- Calculate Safety Stock
2248 If nvl(l_Safety_Stock_flag,'N') = 'N' Then
2249 l_Safety_Stock := 0;
2250 Else l_safety_Stock := ROUND(nvl(l_Safety_Factor,0) * nvl(p_Standard_Deviation,0),4);
2251 End If;
2252
2253 G_safety_Stock := l_safety_stock;
2254
2255 return G_SAFETY_STOCK;
2256 Exception
2257 When OTHERS Then
2258 return G_SAFETY_STOCK;
2259 End;
2260
2261 FUNCTION Get_Service_Level RETURN NUMBER Is
2262 Begin
2263 return (G_SERVICE_LEVEL);
2264 End;
2265
2266 FUNCTION Get_EDQ_FACTOR RETURN NUMBER Is
2267 Begin
2268 Return(G_EDQ_FACTOR);
2269 End;
2270 FUNCTION Get_SAFETY_STOCK_FLAG RETURN Varchar2 Is
2271 Begin
2272 Return(G_SAFETY_STOCK_FLAG);
2273 End;
2274 FUNCTION Get_ASL_FLAG RETURN Varchar2 Is
2275 Begin
2276 Return(G_ASL_FLAG);
2277 End;
2278
2279 -- get the name of an object using its definition in JTF_OBJETCS
2280 FUNCTION get_object_name
2281 ( p_object_type_code in varchar2
2282 , p_object_id in number
2283 ) return varchar2
2284 IS
2285 cursor c_ref is
2286 select select_id
2287 , select_name
2288 , from_table
2289 , where_clause
2290 from jtf_objects_vl
2291 where object_code = p_object_type_code;
2292 l_rec c_ref%rowtype;
2293 -- max data from jtf_objects_vl can be about 2600
2294 l_stmt varchar2(3000);
2295 -- highest max col length found in dom1151 = 421
2296 l_name varchar2(500) := null;
2297 BEGIN
2298 open c_ref;
2299 fetch c_ref into l_rec;
2300 if c_ref%notfound then
2301 close c_ref;
2302 return null;
2303 end if;
2304 close c_ref;
2305 l_stmt :=
2306 'SELECT '||l_rec.select_name||' FROM '||l_rec.from_table||' WHERE ';
2307 if l_rec.where_clause is not null then
2308 l_stmt := l_stmt||l_rec.where_clause||' AND ';
2309 end if;
2310 l_stmt := l_stmt||l_rec.select_id||' = :object_id';
2311 execute immediate l_stmt into l_name using p_object_id;
2312 return l_name;
2313 EXCEPTION
2314 when others then
2315 return null;
2316 END get_object_name;
2317 FUNCTION get_object_Type_meaning(p_object_type_code varchar2) return varchar2
2318 IS
2319 CURSOR csp_object_type is
2320 select Name
2321 from JTF_OBJECTS_VL
2322 where OBJECT_CODE =p_object_type_code;
2323
2324 l_object_type_name varchar2(200);
2325
2326 BEGIN
2327 l_object_type_name := null;
2328 open csp_object_type;
2329 FETCH csp_object_type INTO l_object_type_name;
2330 CLOSE csp_object_type;
2331 return l_object_type_name;
2332
2333 END get_object_Type_meaning;
2334 FUNCTION get_ret_sts_success return varchar2
2335 IS
2336 BEGIN
2337 return fnd_api.g_ret_sts_success;
2338 END get_ret_sts_success;
2339
2340 FUNCTION get_ret_sts_error return varchar2
2341 IS
2342 BEGIN
2343 return fnd_api.g_ret_sts_error;
2344 END get_ret_sts_error;
2345
2346 FUNCTION get_ret_sts_unexp_error return varchar2
2347 IS
2348 BEGIN
2349 return fnd_api.g_ret_sts_unexp_error;
2350 END get_ret_sts_unexp_error;
2351
2352 FUNCTION get_true return varchar2
2353 IS
2354 BEGIN
2355 return fnd_api.g_true;
2356 END get_true;
2357
2358 FUNCTION get_false return varchar2
2359 IS
2360 BEGIN
2361 return fnd_api.g_false;
2362 END get_false;
2363
2364 Function get_rs_cust_sequence return number
2365 IS
2366 l_sequence_number NUMBER;
2367 BEGIN
2368 SELECT CSP_RS_CUST_RELATIONS_s1.nextval into l_sequence_number from dual;
2369 return l_sequence_number;
2370 END get_rs_cust_sequence;
2371
2372 FUNCTION get_order_status(p_order_line_id NUMBER,
2373 p_flow_status_code VARCHAR2)
2374 return varchar2 IS
2375 l_status VARCHAR2(240) := NULL;
2376 l_released_count NUMBER;
2377 l_total_count NUMBER;
2378 CURSOR waybill_cur IS
2379 SELECT distinct waybill,
2380 name
2381 FROM wsh_new_deliveries wnd,
2382 wsh_delivery_Assignments wda,
2383 wsh_delivery_details wdd
2384 WHERE wnd.delivery_id = wda.delivery_id
2385 AND wdd.delivery_detail_id = wda.delivery_Detail_id
2386 AND wdd.source_line_id = p_order_line_id
2387 AND wdd.source_code = 'OE';
2388
2389 CURSOR qty_received_cur IS
2390 SELECT rsl.shipment_line_status_code,
2391 sum(rsl.quantity_received),
2392 rsl.unit_of_measure
2393 FROM po_Requisition_lines_all prl,
2394 oe_order_lines_all oola,
2395 rcv_shipment_lines rsl
2396 WHERE prl.requisition_line_id = rsl.requisition_line_id
2397 AND oola.source_document_line_id = prl.requisition_line_id
2398 AND oola.source_document_type_id = 10
2399 AND oola.line_id = p_order_line_id
2400 group by oola.line_id, rsl.shipment_line_status_code, rsl.unit_of_measure;
2401
2402 BEGIN
2403 l_status := p_flow_status_code;
2404 G_DELIVERY_NUMBER := NULL;
2405 G_WAYBILL := NULL ;
2406 G_RECEIVED_QTY := NULL ;
2407 G_RECEIVED_QTY_UOM := NULL ;
2408 G_STATUS_MEANING := NULL;
2409
2410 OPEN waybill_cur;
2411 FETCH waybill_cur INTO G_WAYBILL, G_DELIVERY_NUMBER;
2412 IF waybill_cur%NOTFOUND THEN
2413 null;
2414 END IF;
2415 CLOSE waybill_cur;
2416 -- begin
2417
2418 OPEN qty_received_cur;
2419 FETCH qty_received_cur INTO l_status, G_RECEIVED_QTY, G_RECEIVED_QTY_UOM;
2420 IF qty_received_cur%NOTFOUND THEN
2421 null;
2422 END IF;
2423 CLOSE qty_received_cur;
2424
2425 IF (p_flow_status_code IN ('SHIPPED', 'CLOSED')) THEN
2426 BEGIN
2427
2428 SELECT meaning
2429 INTO G_STATUS_MEANING
2430 FROM FND_LOOKUP_VALUES LV
2431 WHERE lookup_type = 'SHIPMENT LINE STATUS'
2432 AND lookup_code = l_status
2433 AND LANGUAGE = USERENV('LANG')
2434 AND VIEW_APPLICATION_ID = 201
2435 AND SECURITY_GROUP_ID = fnd_global.lookup_security_group(LV.LOOKUP_TYPE,
2436 LV.VIEW_APPLICATION_ID);
2437
2438 EXCEPTION
2439 WHEN NO_DATA_FOUND THEN
2440 l_status := p_flow_status_code;
2441 SELECT meaning
2442 INTO G_STATUS_MEANING
2443 FROM fnd_lookup_values lv
2444 WHERE lookup_type = 'LINE_FLOW_STATUS'
2445 AND lookup_code = p_flow_status_code
2446 AND LANGUAGE = userenv('LANG')
2447 AND VIEW_APPLICATION_ID = 660
2448 AND SECURITY_GROUP_ID = fnd_global.Lookup_Security_Group(lv.lookup_type,
2449 lv.view_application_id);
2450 WHEN OTHERS THEN
2451 null;
2452 END;
2453
2454 ELSIF (p_flow_status_code <> 'AWAITING_SHIPPING' AND
2455 p_flow_status_code <> 'PRODUCTION_COMPLETE') THEN
2456 SELECT meaning
2457 INTO G_STATUS_MEANING
2458 FROM fnd_lookup_values lv
2459 WHERE lookup_type = 'LINE_FLOW_STATUS'
2460 AND lookup_code = p_flow_status_code
2461 AND LANGUAGE = userenv('LANG')
2462 AND VIEW_APPLICATION_ID = 660
2463 AND SECURITY_GROUP_ID = fnd_global.Lookup_Security_Group(lv.lookup_type,
2464 lv.view_application_id);
2465 /* status is AWAITING_SHIPPING or PRODUCTION_COMPLETE, get value from shipping table */
2466 ELSE
2467 l_status := p_flow_status_code;
2468
2469 SELECT sum(decode(released_status, 'Y', 1, 0)), sum(1)
2470 INTO l_released_count, l_total_count
2471 FROM wsh_delivery_details
2472 WHERE source_line_id = p_order_line_id
2473 AND source_code = 'OE';
2474
2475 IF l_released_count = l_total_count THEN
2476 l_status := 'PICKED';
2477 SELECT meaning
2478 INTO G_STATUS_MEANING
2479 FROM fnd_lookup_values lv
2480 WHERE lookup_type = 'LINE_FLOW_STATUS'
2481 AND lookup_code = 'PICKED'
2482 AND LANGUAGE = userenv('LANG')
2483 AND VIEW_APPLICATION_ID = 660
2484 AND SECURITY_GROUP_ID = fnd_global.Lookup_Security_Group(lv.lookup_type,
2485 lv.view_application_id);
2486 ELSIF l_released_count < l_total_count and l_released_count <> 0 THEN
2487 l_status := 'PICKED_PARTIAL';
2488 SELECT meaning
2489 INTO G_STATUS_MEANING
2490 FROM fnd_lookup_values lv
2491 WHERE lookup_type = 'LINE_FLOW_STATUS'
2492 AND lookup_code = 'PICKED_PARTIAL'
2493 AND LANGUAGE = userenv('LANG')
2494 AND VIEW_APPLICATION_ID = 660
2495 AND SECURITY_GROUP_ID = fnd_global.Lookup_Security_Group(lv.lookup_type,
2496 lv.view_application_id);
2497 ELSE
2498 SELECT meaning
2499 INTO G_STATUS_MEANING
2500 FROM fnd_lookup_values lv
2501 WHERE lookup_type = 'LINE_FLOW_STATUS'
2502 AND lookup_code = l_status
2503 AND LANGUAGE = userenv('LANG')
2504 AND VIEW_APPLICATION_ID = 660
2505 AND SECURITY_GROUP_ID = fnd_global.Lookup_Security_Group(lv.lookup_type,
2506 lv.view_application_id);
2507 END IF;
2508 END IF;
2509 RETURN(l_status);
2510 END;
2511
2512 FUNCTION get_attribute_value(p_attribute_name VARCHAR2) return VARCHAR2 IS
2513 BEGIN
2514 IF p_attribute_name = 'DELIVERY_NUMBER' THEN
2515 RETURN(G_DELIVERY_NUMBER);
2516 ELSIF p_attribute_name = 'WAYBILL' THEN
2517 RETURN (G_WAYBILL);
2518 ELSIF p_Attribute_name = 'RECEIVED_QTY_UOM' THEN
2519 RETURN (G_RECEIVED_QTY_UOM);
2520 ELSIF p_attribute_name = 'STATUS_MEANING' THEN
2521 RETURN (G_STATUS_MEANING);
2522 END IF;
2523 END;
2524
2525 FUNCTION get_received_qty RETURN NUMBER IS
2526 Begin
2527 Return(G_RECEIVED_QTY);
2528 End;
2529
2530 FUNCTION get_adjusted_date(p_source_tz_id NUMBER,
2531 p_dest_tz_id NUMBER,
2532 p_source_day_time DATE) RETURN DATE IS
2533 l_return_status VARCHAR2(1);
2534 l_msg_count NUMBER := 0;
2535 l_msg_data VARCHAR2(500);
2536 l_dest_day_time DATE;
2537 BEGIN
2538 IF ((nvl(fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS'), 'N') = 'Y') AND
2539 p_source_tz_id <> p_dest_tz_id) THEN
2540 HZ_TIMEZONE_PUB.Get_Time(p_api_version => 1.0,
2541 p_init_msg_list => 'F',
2542 p_source_tz_id => p_source_tz_id,
2543 p_dest_tz_id => p_dest_tz_id,
2544 p_source_day_time=> p_source_day_time,
2545 x_dest_day_time => l_dest_day_time,
2546 x_return_status => l_return_status ,
2547 x_msg_count => l_msg_count ,
2548 x_msg_data => l_msg_data);
2549 ELSE
2550 l_dest_day_time := p_source_day_time;
2551 END IF;
2552
2553 return(l_dest_day_time);
2554 END;
2555 Function get_contact_info(p_incident_id NUMBER) return varchar2 IS
2556 l_contact varchar2(2000);
2557 cursor get_contact is
2558 select CONTACT_COMM_PREF,CONTACT_NAME
2559 from csf_po_contact_points_v
2560 where INCIDENT_ID = p_incident_id;
2561 begin
2562 g_contact_name := NULL;
2563
2564 OPEN get_contact;
2565 FETCH get_contact INTO l_contact,g_contact_name;
2566 CLOSE get_contact;
2567 return l_contact;
2568 END;
2569
2570 FUNCTION get_contact_name RETURN varchar2 IS
2571 Begin
2572 Return(G_contact_name);
2573 End;
2574
2575
2576 END; -- End of Package