[Home] [Help]
PACKAGE BODY: APPS.CSP_MO_MTLTXNS_UTIL
Source
1 PACKAGE BODY CSP_MO_MTLTXNS_UTIL AS
2 /*$Header: cspgtmub.pls 120.1 2006/07/20 05:39:33 hhaugeru noship $*/
3 -- Start of Comments
4 -- Package name : CSP_MO_MTLTXNS_UTIL
5 -- Purpose : This package includes the procedures that handle material transactions associated with any move orders.
6 -- History
7 -- 08-Feb-00, Add standard messages and validations.
8 -- 29-Dec-99, Vernon Lou.
9 --
10 -- NOTE :
11 -- End of Comments
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_MO_MTLTXNS_UTIL';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgtmub.pls';
15 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
16 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
17
18 PROCEDURE Update_Order_Line_Status(
19 /* $Header: cspgtmub.pls 120.1 2006/07/20 05:39:33 hhaugeru noship $ */
20 -- Start of Comments
21 -- Procedure name : update_order_line_status
22 -- Purpose : This procedure updates the line status of a move order in the mtl_txn_request_lines table.
23 -- History :
24 -- Person Date Descriptions
25 -- ------ ---- --------------
26 -- klou 03-Jan-2000 Added options for NONE or FULL validations.
27 -- klou 01-Jan-2000 created.
28 --
29 -- NOTES: If validations have been done in the precedent procedure from which this one is being called, doing a
30 -- full validation here is unnecessary. To avoid repeating the same validations, you can set the
31 -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
32 -- responsibility to make sure all proper validations have been done before calling this procedure.
33 -- You are recommended to let this procedure handle the validations if you are not sure.
34 --
35 -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
36 -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
37 -- to FND_API.G_VALID_LEVEL_FULL when making the call.
38 -- End of Comments
39
40 P_Api_Version_Number IN NUMBER,
41 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
42 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
43 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44 p_organization_id IN NUMBER,
45 p_move_order_line_id IN NUMBER,
46 X_Return_Status OUT NOCOPY VARCHAR2,
47 X_Msg_Count OUT NOCOPY NUMBER,
48 X_Msg_Data OUT NOCOPY VARCHAR2
49 )
50 IS
51
52 l_api_version_number CONSTANT NUMBER := 1.0;
53 l_api_name CONSTANT VARCHAR2(50) := 'Update_Order_Line_Sts';
54 l_msg_data VARCHAR2(300);
55 l_check_existence NUMBER := 0;
56 l_return_status VARCHAR2(1);
57 l_msg_count NUMBER := 0;
58 l_commit VARCHAR2(1) := FND_API.G_FALSE;
59 l_creation_date DATE;
60 l_last_update_date DATE;
61 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
62 l_header_id NUMBER;
63 EXCP_USER_DEFINED EXCEPTION;
64
65 BEGIN
66 SAVEPOINT Update_Order_Line_Sts_PUB;
67 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
68 -- initialize message list
69 FND_MSG_PUB.initialize;
70 END IF;
71 -- Standard call to check for call compatibility.
72 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
73 p_api_version_number,
74 l_api_name,
75 G_PKG_NAME)
76 THEN
77 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
78 END IF;
79
80 IF p_validation_level = fnd_api.g_valid_level_full THEN
81 -- Notes: if validations have been done in the precedence procedure which this one is being from, doing a full
82 -- validation here is not necessary. The users can set the p_validation_level to fnd_api.g_valid_level_none
83 -- if they do not want to repeat the same validations. However, it is their responsibility to make sure
84 -- all proper validations have been done before calling this procedure. It is recommended that they let
85 -- this procedure handle the validations except that they know what they are doing.
86
87 -- validate p_organization_id
88 IF p_organization_id IS NULL THEN
89 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
90 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', FALSE);
91 FND_MSG_PUB.ADD;
92 RAISE EXCP_USER_DEFINED;
93 ELSE
94 BEGIN
95 select organization_id into l_check_existence
96 from mtl_parameters
97 where organization_id = p_organization_id ;
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
101 FND_MSG_PUB.ADD;
102 RAISE EXCP_USER_DEFINED;
103 WHEN OTHERS THEN
104 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
105 fnd_message.set_token('ERR_FIELD', 'p_organization_id', FALSE);
106 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
107 fnd_message.set_token('TABLE', 'mtl_organizations', FALSE);
108 FND_MSG_PUB.ADD;
109 RAISE EXCP_USER_DEFINED;
110 END;
111 END IF;
112
113
114 -- validate p_move_order_line_id
115 IF p_move_order_line_id IS NULL THEN
116 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
117 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_move_order_line_id', FALSE);
118 FND_MSG_PUB.ADD;
119 RAISE EXCP_USER_DEFINED;
120 ELSE
121 -- validate whether the move_order_line_id exists in the mmtt table
122 BEGIN
123 SELECT transaction_temp_id into l_check_existence
124 FROM mtl_material_transactions_temp
125 WHERE move_order_line_id = p_move_order_line_id
126 AND organization_id = p_organization_id;
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 fnd_message.set_name ('CSP', 'CSP_INVALID_MOVEORDER_LINE');
130 fnd_message.set_token ('LINE_ID', to_char(p_move_order_line_id), FALSE);
131 fnd_msg_pub.add;
132 RAISE EXCP_USER_DEFINED;
133 WHEN TOO_MANY_ROWS THEN
134 -- This is normal. One move order line id can map to many transaction_temp_id's.
135 NULL;
136 WHEN OTHERS THEN
137 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
138 fnd_message.set_token('ERR_FIELD', 'p_move_order_line_id', FALSE);
139 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
140 fnd_message.set_token('TABLE', 'mtl_material_transactions_temp', FALSE);
141 FND_MSG_PUB.ADD;
142 RAISE EXCP_USER_DEFINED;
143 END;
144
145 -- validate whether the move_order_line_id exists
146 BEGIN
147 select header_id into l_header_id
148 from csp_moveorder_lines
149 where line_id = p_move_order_line_id;
150 EXCEPTION
151 WHEN NO_DATA_FOUND THEN
152 fnd_message.set_name ('CSP', 'CSP_INVALID_MOVEORDER_LINE');
153 fnd_message.set_token ('LINE_ID', to_char(p_move_order_line_id), FALSE);
154 fnd_msg_pub.add;
155 RAISE EXCP_USER_DEFINED;
156 WHEN OTHERS THEN
157 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
158 fnd_message.set_token('ERR_FIELD', 'p_move_order_line_id', FALSE);
159 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
160 fnd_message.set_token('TABLE', 'csp_moveorder_lines', FALSE);
161 FND_MSG_PUB.ADD;
162 RAISE EXCP_USER_DEFINED;
163 END;
164 END IF;
165 END IF;
166
167 -- call a core apps api to update the line status.
168 -- Since the core apps api does not return a status, we have to catch the exception it may throw.
169 BEGIN
170
171 INV_Trolin_Util.Update_Row_Status
172 ( p_line_id => p_move_order_line_id,
173 p_status => 5 -- update status to 5 = closed
174 );
175 /*
176 update mtl_txn_request_lines
177 set line_status = 5
178 where line_id = p_move_order_line_id;
179
180 IF fnd_api.to_boolean(p_commit) THEN
181 commit work;
182 END IF;
183 */
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 RAISE FND_API.G_EXC_ERROR;
189 END;
190
191 IF fnd_api.to_boolean(p_commit) THEN
192 commit work;
193 END IF;
194 x_return_status := FND_API.G_RET_STS_SUCCESS;
195 EXCEPTION
196 WHEN EXCP_USER_DEFINED THEN
197 Rollback to Update_Order_Line_Sts_PUB;
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 fnd_msg_pub.count_and_get
200 ( p_count => x_msg_count
201 , p_data => x_msg_data);
202 WHEN FND_API.G_EXC_ERROR THEN
203 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
204 P_API_NAME => L_API_NAME
205 ,P_PKG_NAME => G_PKG_NAME
206 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
207 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
208 ,X_MSG_COUNT => X_MSG_COUNT
209 ,X_MSG_DATA => X_MSG_DATA
210 ,X_RETURN_STATUS => X_RETURN_STATUS);
211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
212 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
213 P_API_NAME => L_API_NAME
214 ,P_PKG_NAME => G_PKG_NAME
215 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
216 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
217 ,X_MSG_COUNT => X_MSG_COUNT
218 ,X_MSG_DATA => X_MSG_DATA
219 ,X_RETURN_STATUS => X_RETURN_STATUS);
220 WHEN OTHERS THEN
221 Rollback to Update_Order_Line_Sts_PUB;
222 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
223 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
224 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
225 fnd_msg_pub.add;
226 fnd_msg_pub.count_and_get
227 ( p_count => x_msg_count
228 , p_data => x_msg_data);
229 x_return_status := fnd_api.g_ret_sts_error;
230 END Update_Order_Line_Status;
231
232
233 FUNCTION validate_mo_line_status (
234 /* $Header: cspgtmub.pls 120.1 2006/07/20 05:39:33 hhaugeru noship $ */
235 -- Start of Comments
236 -- Function name : validate_mo_line_status
237 -- Purpose : This function checks whether the status of all order lines of a move order has been closed.
238 -- It returns fnd_api.g_true if the status of all order lines has been closed.
239 -- Otherwise, it returns fnd_api.g_false.
240 -- History :
241 -- Person Date Descriptions
242 -- ------ ---- --------------
243 -- klou 03-Jan-2000 Add options for NONE or FULL validations.
244 -- klou 01-Jan-2000 created.
245 --
246 -- NOTES:
247 -- End of Comments
248 p_move_order_header_id IN NUMBER,
249 p_status_to_be_validated IN NUMBER)
250 RETURN VARCHAR2
251 IS
252 l_line_id NUMBER;
253 l_line_status NUMBER := -1;
254 CURSOR l_mo_header_csr IS
255 SELECT line_id
256 FROM CSP_MOVEORDER_LINES
257 WHERE header_id = p_move_order_header_id;
258
259 BEGIN
260 OPEN l_mo_header_csr;
261 LOOP
262 FETCH l_mo_header_csr INTO l_line_id;
263 EXIT WHEN l_mo_header_csr%NOTFOUND;
264
265 BEGIN
266 SELECT line_status INTO l_line_status
267 FROM mtl_txn_request_lines
268 WHERE line_id = l_line_id;
269
270 IF l_line_status <> p_status_to_be_validated THEN
271 CLOSE l_mo_header_csr;
272 RETURN fnd_api.g_false;
273 END IF;
274 EXCEPTION
275 WHEN OTHERS THEN
276 CLOSE l_mo_header_csr;
277 RETURN fnd_api.g_false;
278 END;
279
280 END LOOP;
281
282
283 IF l_mo_header_csr%rowcount = 0 THEN
284 IF l_mo_header_csr%ISOPEN THEN
285 CLOSE l_mo_header_csr;
286 END IF;
287 RETURN fnd_api.g_false;
288 END IF;
289
290 IF l_mo_header_csr%ISOPEN THEN
291 CLOSE l_mo_header_csr;
292 END IF;
293
294 RETURN fnd_api.g_true;
295
296 END validate_mo_line_status;
297
298
299 PROCEDURE confirm_receipt (
300 -- Start of Comments
301 -- Procedure name : confirm_receipt
302 -- Purpose : This procedure initiates the material transactions for manual receipt orders when the
303 -- users confirm that they receive the shipped orders. In addition to updating the mmtt table,
304 -- it also updates quantity_received column in the csp_packlist_lines table.
305 --
306 -- History :
307 -- Person Date Descriptions
308 -- ------ ---- --------------
309 -- klou 13-Apr-2000 Modify the logic so that misc. receipt is used to handle over receipt and
310 -- misc. issue is used to handle under receipt. Over receiving serial controlled items
311 -- is not allowed but will be implemented in the future release.
312 -- klou 05-Apr-2000 Add over receiving and receipt short.
313 -- klou 22-Mar-2000 Add logic to move data from the temp table to the interface table. This is required for
314 -- calling the process_online procedure.
315 -- klou 08-Feb-2000 Add standard messages.
316 -- klou 22-Jan-2000 Include codes to handle material transactions for item under serial and/or lot control.
317 -- klou 07-Jan-2000 include codes to check whether the quantity_received exceed the
318 -- (shipped_quantity - quantity_received). add p_to_subiventroy_code
319 -- and p_to_locator_id.
320 -- klou 03-Jan-2000 modify to take move_order_line_id instead of header_id.
321 -- klou 01-Jan-2000 created.
322 --
323 -- NOTES: If validations have been done in the precedent procedure from which this one is being called, doing a
324 -- full validation here is unnecessary. To avoid repeating the same validations, you can set the
325 -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
326 -- responsibility to make sure all proper validations have been done before calling this procedure.
327 -- You are recommended to let this procedure handle the validations if you are not sure.
328 --
329 -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
330 -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
331 -- to FND_API.G_VALID_LEVEL_FULL when making the call.
332 -- End of Comments
333
334 P_Api_Version_Number IN NUMBER,
335 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
336 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
337 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
338 p_packlist_line_id IN NUMBER,
339 p_organization_id IN NUMBER,
340 p_transaction_temp_id IN NUMBER,
344 p_serial_number IN VARCHAR2 := NULL,
341 p_quantity_received IN NUMBER,
342 p_to_subinventory_code IN VARCHAR2 := NULL,
343 p_to_locator_id IN NUMBER := NULL,
345 p_lot_number IN VARCHAR2 := NULL,
346 p_revision IN VARCHAR2 := NULL,
347 p_receiving_option IN NUMBER := 0, --0 = receiving normal, 1 = receipt short, 2 = over receipt (but do not close the packlist and move order, 3 = over receipt (close everything)
348 px_transaction_header_id IN OUT NOCOPY NUMBER,
349 p_process_flag IN VARCHAR2 := fnd_api.g_false,
350 X_Return_Status OUT NOCOPY VARCHAR2,
351 X_Msg_Count OUT NOCOPY NUMBER,
352 X_Msg_Data OUT NOCOPY VARCHAR2)
353
354 IS
355 l_line_id NUMBER;
356 l_api_version_number CONSTANT NUMBER := 1.0;
357 l_api_name CONSTANT VARCHAR2(50) := 'confirm_receipt';
358 l_msg_data VARCHAR2(300);
359 l_check_existence NUMBER := 0;
360 l_return_status VARCHAR2(1);
361 l_msg_count NUMBER := 0;
362 l_commit VARCHAR2(1) := FND_API.G_FALSE;
363 l_creation_date DATE;
364 l_last_update_date DATE;
365 l_header_id NUMBER;
366 l_packlist_header_id NUMBER;
367 l_packlist_header_status VARCHAR2(30) := NULL;
368 l_packlist_line_status VARCHAR2(30) := NULL;
369 l_count NUMBER;
370
371 -- for inserting data, the validation_level should be none
372 -- because we do not want to call core apps standard validations.
373 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
374 l_move_order_line_id NUMBER;
375 l_csp_mtltxn_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
376 l_csp_mtltxn_over_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
377 l_csp_mtltxn_bak_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
378 l_csp_mtltxn_misc_issue_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
379 l_mtlt_tbl csp_pp_util.g_mtlt_tbl_type;
380 l_msnt_tbl csp_pp_util.g_msnt_tbl_type;
381 l_trolin_rec INV_Move_Order_PUB.Trolin_Rec_Type;
382 l_packlist_headers_rec CSP_packlist_headers_PVT.PLH_Rec_Type;
383
384 -- Use 1 as the starting index because it is what the core apps API uses.
385 -- we are not going to update this index becase there is only one record in the
386 -- msnt that is coresponding to the l_packlist_sl_rec.serial_number.
387 l_index NUMBER := 1;
388 l_transaction_temp_id NUMBER := NULL;
389 l_txn_temp_id_to_be_removed NUMBER := NULL;
390 l_outcome BOOLEAN := TRUE;
391 l_error_code VARCHAR2(200);
392 l_error_explanation VARCHAR2(240);
393 l_transaction_header_id NUMBER := px_transaction_header_id;
394 --l_txn_header_id_cleaned NUMBER := NULL;
395 l_temp_id_to_be_processed NUMBER;
396 l_quantity_shipped NUMBER := 0;
397 l_quantity_received NUMBER := 0;
398 l_avail_qty NUMBER := 0;
399 l_recv_less_than_txn VARCHAR2(1) := fnd_api.g_false;
400 l_org_received_qty NUMBER;
401 EXCP_USER_DEFINED EXCEPTION;
402
403 CURSOR l_ml_records(l_transaction_temp_id NUMBER) IS
404 SELECT
405 TRANSACTION_HEADER_ID ,
406 TRANSACTION_TEMP_ID ,
407 SOURCE_CODE ,
408 SOURCE_LINE_ID ,
409 TRANSACTION_MODE ,
410 LOCK_FLAG ,
411 LAST_UPDATE_DATE ,
412 LAST_UPDATED_BY ,
413 CREATION_DATE ,
414 CREATED_BY ,
415 LAST_UPDATE_LOGIN ,
416 REQUEST_ID ,
417 PROGRAM_APPLICATION_ID ,
418 PROGRAM_ID ,
419 PROGRAM_UPDATE_DATE ,
420 INVENTORY_ITEM_ID ,
421 REVISION ,
422 ORGANIZATION_ID ,
423 SUBINVENTORY_CODE ,
424 LOCATOR_ID ,
425 TRANSACTION_QUANTITY ,
426 PRIMARY_QUANTITY ,
427 TRANSACTION_UOM ,
428 TRANSACTION_COST ,
429 TRANSACTION_TYPE_ID ,
430 TRANSACTION_ACTION_ID ,
431 TRANSACTION_SOURCE_TYPE_ID ,
432 TRANSACTION_SOURCE_ID ,
433 TRANSACTION_SOURCE_NAME ,
434 TRANSACTION_DATE ,
435 ACCT_PERIOD_ID ,
436 DISTRIBUTION_ACCOUNT_ID ,
437 TRANSACTION_REFERENCE ,
438 REQUISITION_LINE_ID ,
439 REQUISITION_DISTRIBUTION_ID ,
440 REASON_ID ,
441 LOT_NUMBER ,
442 LOT_EXPIRATION_DATE ,
443 SERIAL_NUMBER ,
447 MOVE_TRANSACTION_ID ,
444 RECEIVING_DOCUMENT ,
445 DEMAND_ID ,
446 RCV_TRANSACTION_ID ,
448 COMPLETION_TRANSACTION_ID ,
449 WIP_ENTITY_TYPE ,
450 SCHEDULE_ID ,
451 REPETITIVE_LINE_ID ,
452 EMPLOYEE_CODE ,
453 PRIMARY_SWITCH ,
454 SCHEDULE_UPDATE_CODE ,
455 SETUP_TEARDOWN_CODE ,
456 ITEM_ORDERING ,
457 NEGATIVE_REQ_FLAG ,
458 OPERATION_SEQ_NUM ,
459 PICKING_LINE_ID ,
460 TRX_SOURCE_LINE_ID ,
461 TRX_SOURCE_DELIVERY_ID ,
462 PHYSICAL_ADJUSTMENT_ID ,
463 CYCLE_COUNT_ID ,
464 RMA_LINE_ID ,
465 CUSTOMER_SHIP_ID ,
466 CURRENCY_CODE ,
467 CURRENCY_CONVERSION_RATE ,
468 CURRENCY_CONVERSION_TYPE ,
469 CURRENCY_CONVERSION_DATE ,
470 USSGL_TRANSACTION_CODE ,
471 VENDOR_LOT_NUMBER ,
472 ENCUMBRANCE_ACCOUNT ,
473 ENCUMBRANCE_AMOUNT ,
474 SHIP_TO_LOCATION ,
475 SHIPMENT_NUMBER ,
476 TRANSFER_COST ,
477 TRANSPORTATION_COST ,
478 TRANSPORTATION_ACCOUNT ,
479 FREIGHT_CODE ,
480 CONTAINERS ,
481 WAYBILL_AIRBILL ,
482 EXPECTED_ARRIVAL_DATE ,
483 TRANSFER_SUBINVENTORY ,
484 TRANSFER_ORGANIZATION ,
485 TRANSFER_TO_LOCATION ,
486 NEW_AVERAGE_COST ,
487 VALUE_CHANGE ,
488 PERCENTAGE_CHANGE ,
489 MATERIAL_ALLOCATION_TEMP_ID ,
490 DEMAND_SOURCE_HEADER_ID ,
491 DEMAND_SOURCE_LINE ,
492 DEMAND_SOURCE_DELIVERY ,
493 ITEM_SEGMENTS ,
494 ITEM_DESCRIPTION ,
495 ITEM_TRX_ENABLED_FLAG ,
496 ITEM_LOCATION_CONTROL_CODE ,
497 ITEM_RESTRICT_SUBINV_CODE ,
498 ITEM_RESTRICT_LOCATORS_CODE ,
499 ITEM_REVISION_QTY_CONTROL_CODE ,
500 ITEM_PRIMARY_UOM_CODE ,
501 ITEM_UOM_CLASS ,
502 ITEM_SHELF_LIFE_CODE ,
503 ITEM_SHELF_LIFE_DAYS ,
504 ITEM_LOT_CONTROL_CODE ,
505 ITEM_SERIAL_CONTROL_CODE ,
506 ITEM_INVENTORY_ASSET_FLAG ,
507 ALLOWED_UNITS_LOOKUP_CODE ,
508 DEPARTMENT_ID ,
509 DEPARTMENT_CODE ,
510 WIP_SUPPLY_TYPE ,
511 SUPPLY_SUBINVENTORY ,
512 SUPPLY_LOCATOR_ID ,
513 VALID_SUBINVENTORY_FLAG ,
514 VALID_LOCATOR_FLAG ,
515 LOCATOR_SEGMENTS ,
516 CURRENT_LOCATOR_CONTROL_CODE ,
517 NUMBER_OF_LOTS_ENTERED ,
518 WIP_COMMIT_FLAG ,
519 NEXT_LOT_NUMBER ,
520 LOT_ALPHA_PREFIX ,
521 NEXT_SERIAL_NUMBER ,
522 SERIAL_ALPHA_PREFIX ,
523 SHIPPABLE_FLAG ,
524 POSTING_FLAG ,
525 REQUIRED_FLAG ,
526 PROCESS_FLAG ,
527 ERROR_CODE ,
528 ERROR_EXPLANATION ,
529 ATTRIBUTE_CATEGORY ,
530 ATTRIBUTE1 ,
531 ATTRIBUTE2 ,
532 ATTRIBUTE3 ,
533 ATTRIBUTE4 ,
534 ATTRIBUTE5 ,
535 ATTRIBUTE6 ,
536 ATTRIBUTE7 ,
537 ATTRIBUTE8 ,
538 ATTRIBUTE9 ,
539 ATTRIBUTE10 ,
540 ATTRIBUTE11 ,
541 ATTRIBUTE12 ,
542 ATTRIBUTE13 ,
543 ATTRIBUTE14 ,
544 ATTRIBUTE15 ,
545 MOVEMENT_ID ,
546 RESERVATION_QUANTITY ,
547 SHIPPED_QUANTITY ,
548 TRANSACTION_LINE_NUMBER ,
549 TASK_ID ,
550 TO_TASK_ID ,
551 SOURCE_TASK_ID ,
552 PROJECT_ID ,
553 SOURCE_PROJECT_ID ,
554 PA_EXPENDITURE_ORG_ID ,
555 TO_PROJECT_ID ,
556 EXPENDITURE_TYPE ,
557 FINAL_COMPLETION_FLAG ,
558 TRANSFER_PERCENTAGE ,
559 TRANSACTION_SEQUENCE_ID ,
560 MATERIAL_ACCOUNT ,
561 MATERIAL_OVERHEAD_ACCOUNT ,
562 RESOURCE_ACCOUNT ,
563 OUTSIDE_PROCESSING_ACCOUNT ,
564 OVERHEAD_ACCOUNT ,
568 QA_COLLECTION_ID ,
565 FLOW_SCHEDULE ,
566 COST_GROUP_ID ,
567 DEMAND_CLASS ,
569 KANBAN_CARD_ID ,
570 OVERCOMPLETION_TRANSACTION_ID ,
571 OVERCOMPLETION_PRIMARY_QTY ,
572 OVERCOMPLETION_TRANSACTION_QTY ,
573 --PROCESS_TYPE , --removed 01/13/00. process_type does not exist in the mmtt table.
574 END_ITEM_UNIT_NUMBER ,
575 SCHEDULED_PAYBACK_DATE ,
576 LINE_TYPE_CODE ,
577 PARENT_TRANSACTION_TEMP_ID ,
578 PUT_AWAY_STRATEGY_ID ,
579 PUT_AWAY_RULE_ID ,
580 PICK_STRATEGY_ID ,
581 PICK_RULE_ID ,
582 COMMON_BOM_SEQ_ID ,
583 COMMON_ROUTING_SEQ_ID ,
584 COST_TYPE_ID ,
585 ORG_COST_GROUP_ID ,
586 MOVE_ORDER_LINE_ID ,
587 TASK_GROUP_ID ,
588 PICK_SLIP_NUMBER ,
589 RESERVATION_ID ,
590 TRANSACTION_STATUS ,
591 STANDARD_OPERATION_ID ,
592 TASK_PRIORITY ,
593 -- ADDED by phegde 02/23
594 WMS_TASK_TYPE ,
595 PARENT_LINE_ID
596 -- SOURCE_LOT_NUMBER
597 FROM mtl_material_transactions_temp
598 WHERE transaction_temp_id = l_transaction_temp_id
599 AND organization_id = p_organization_id;
600 CURSOR l_Get_Shipped_Received_Qty IS
601 SELECT nvl(quantity_shipped, 0), nvl(quantity_received, 0)
602 FROM CSP_Packlist_Lines
603 WHERE packlist_line_id = p_packlist_line_id
604 and organization_id = p_organization_id;
605 CURSOR l_Get_Moveorder_Headers(l_line_id NUMBER) IS
606 SELECT distinct header_id
607 FROM csp_moveorder_lines
608 WHERE line_id = l_line_id;
609 CURSOR l_Get_txn_header_id_csr IS
610 SELECT mtl_material_transactions_s.nextval
611 FROM dual;
612 CURSOR l_Get_Packlist_Status_Csr(l_packlist_header_id NUMBER) IS
613 SELECT packlist_status
614 FROM csp_packlist_headers
615 WHERE packlist_header_id = l_packlist_header_id;
616 CURSOR l_Get_Acct_Period_Csr is
617 SELECT acct_period_id
618 FROM org_acct_periods
619 WHERE trunc(period_start_date) <= trunc(sysdate)
620 AND trunc(schedule_close_date) >= trunc(sysdate)
621 AND organization_id = p_organization_id
622 AND period_close_date is null
623 AND nvl(open_flag,'Y') = 'Y';
624 CURSOR l_Get_Serial_Temp_id_Csr IS
625 SELECT MTL_MATERIAL_TRANSACTIONS_S.nextval FROM dual;
626
627 CURSOR l_Get_Packlist_Csr (l_packlist_header_id NUMBER) Is
628 SELECT
629 PACKLIST_HEADER_ID ,
630 CREATED_BY ,
631 CREATION_DATE ,
632 LAST_UPDATED_BY ,
633 LAST_UPDATE_DATE ,
634 LAST_UPDATE_LOGIN ,
635 ORGANIZATION_ID ,
636 PACKLIST_NUMBER ,
637 SUBINVENTORY_CODE ,
638 PACKLIST_STATUS ,
639 DATE_CREATED ,
640 DATE_PACKED ,
641 DATE_SHIPPED ,
642 DATE_RECEIVED ,
643 CARRIER ,
644 SHIPMENT_METHOD ,
645 WAYBILL ,
646 COMMENTS ,
647 LOCATION_ID,
648 PARTY_SITE_ID,
649 ATTRIBUTE_CATEGORY ,
650 ATTRIBUTE1 ,
651 ATTRIBUTE2 ,
652 ATTRIBUTE3 ,
653 ATTRIBUTE4 ,
654 ATTRIBUTE5 ,
655 ATTRIBUTE6 ,
656 ATTRIBUTE7 ,
657 ATTRIBUTE8 ,
658 ATTRIBUTE9 ,
659 ATTRIBUTE10 ,
660 ATTRIBUTE11 ,
661 ATTRIBUTE12 ,
662 ATTRIBUTE13 ,
663 ATTRIBUTE14 ,
664 ATTRIBUTE15
665 From CSP_PACKLIST_HEADERS
666 WHERE organization_id = p_organization_id
667 AND packlist_header_id = l_packlist_header_id;
668
669 -- Added to fix bug 1321353.
670 CURSOR l_Get_Org_Qty_Csr Is
671 Select nvl(quantity_shipped,0)-nvl(quantity_received,0)
672 From csp_packlist_lines
673 Where packlist_line_id = p_packlist_line_id
674 And organization_id = p_organization_id;
675 BEGIN
676 SAVEPOINT confirm_receipt_PUB;
677
678 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
679 -- initialize message list
680 FND_MSG_PUB.initialize;
681 END IF;
682 -- Standard call to check for call compatibility.
683 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
684 p_api_version_number,
685 l_api_name,
686 G_PKG_NAME)
687 THEN
688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
689 END IF;
690
691 IF p_validation_level = fnd_api.g_valid_level_full THEN
692 -- Notes: if validations have been done in the precedence procedure which this one is being called, doing a full
693 -- validation here is not necessary. You can set the p_validation_level to fnd_api.g_valid_level_none
694 -- if you do not want to repeat the same validations. However, it is your responsibility to make sure
698 -- validate p_organization_id
695 -- all proper validations have been done before calling this procedure. It is recommended that you let
696 -- this procedure handle the validations except that you know what you are doing.
697
699 IF p_organization_id IS NULL THEN
700 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
701 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', FALSE);
702 FND_MSG_PUB.ADD;
703 RAISE EXCP_USER_DEFINED;
704 ELSE
705 BEGIN
706 select organization_id into l_check_existence
707 from mtl_parameters
708 where organization_id = p_organization_id;
709 EXCEPTION
710 WHEN NO_DATA_FOUND THEN
711 FND_MESSAGE.SET_NAME ('INV', 'INVALID ORGANIZATION');
712 FND_MSG_PUB.ADD;
713 RAISE EXCP_USER_DEFINED;
714 WHEN OTHERS THEN
715 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
716 fnd_message.set_token('ERR_FIELD', 'p_organization_id', FALSE);
717 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
718 fnd_message.set_token('TABLE', 'mtl_organizations', FALSE);
719 FND_MSG_PUB.ADD;
720 RAISE EXCP_USER_DEFINED;
721 END;
722 END IF;
723
724 END IF; -- end full validations
725
726 -- Validate the p_packlist_line_id
727 IF nvl(p_packlist_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
728 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
729 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_packlist_line_id', FALSE);
730 FND_MSG_PUB.ADD;
731 RAISE EXCP_USER_DEFINED;
732 ELSE
733 -- get the packlist_header_id
734 BEGIN
735 select packlist_header_id into l_packlist_header_id
736 from csp_packlist_lines
737 where organization_id = p_organization_id
738 and packlist_line_id = p_packlist_line_id;
739 EXCEPTION
740 WHEN NO_DATA_FOUND THEN
741 fnd_message.set_name ('CSP', 'CSP_INVALID_PACKLIST_LINE');
742 fnd_message.set_token ('LINE_ID', to_char(p_packlist_line_id), FALSE);
743 fnd_msg_pub.add;
744 RAISE EXCP_USER_DEFINED;
745 WHEN OTHERS THEN
746 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
747 fnd_message.set_token('ERR_FIELD', 'p_packlist_line_id', FALSE);
748 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
749 fnd_message.set_token('TABLE', 'csp_packlist_lines', FALSE);
750 fnd_msg_pub.add;
751 RAISE EXCP_USER_DEFINED;
752 END;
753
754 -- Check whether the packlist already has a status of 'Received' (3) or of 'Received Short' (4).
755 -- If true, raise an exception because we should not process a closed packlist.
756 OPEN l_Get_Packlist_Status_Csr(l_packlist_header_id);
757 FETCH l_Get_Packlist_Status_Csr INTO l_packlist_header_status;
758 CLOSE l_Get_Packlist_Status_Csr;
759
760 IF l_packlist_header_status IS NULL THEN
761 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
762 fnd_message.set_token('ERR_FIELD', 'p_packlist_header_status', FALSE);
763 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
764 fnd_message.set_token('TABLE', 'CSP_PACKLIST_HEADERS', FALSE);
765 fnd_msg_pub.add;
766 RAISE EXCP_USER_DEFINED;
767 END IF;
768
769 IF l_packlist_header_status = '3' THEN
770 fnd_message.set_name('CSP', 'CSP_INVALID_PACKLIST_TXN');
771 fnd_message.set_token('PACKLIST_HEADER_ID', to_char(l_packlist_header_id), FALSE);
772 fnd_msg_pub.add;
773 RAISE EXCP_USER_DEFINED;
774 END IF;
775
776 IF p_validation_level = fnd_api.g_valid_level_full THEN
777 BEGIN
778 select packlist_line_id into l_check_existence
779 from csp_packlist_lines
780 where packlist_line_id = p_packlist_line_id
781 and organization_id = p_organization_id;
782 EXCEPTION
783 WHEN NO_DATA_FOUND THEN
784 fnd_message.set_name ('CSP', 'CSP_INVALID_PACKLIST_LINE');
785 fnd_message.set_token ('LINE_ID', to_char(p_packlist_line_id), FALSE);
786 fnd_msg_pub.add;
787 RAISE EXCP_USER_DEFINED;
788 WHEN OTHERS THEN
789 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
790 fnd_message.set_token('ERR_FIELD', 'p_packlist_line_id', FALSE);
791 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
792 fnd_message.set_token('TABLE', 'csp_packlist_lines', FALSE);
793 fnd_msg_pub.add;
794 RAISE EXCP_USER_DEFINED;
795 END;
796 END IF;
797 END IF;
798
799 -- Validate the quantity received
803 -- update quantity_received in the csp_packlist_lines table
800 IF nvl(p_quantity_received, fnd_api.g_miss_num) = fnd_api.g_miss_num OR p_quantity_received < 0 THEN
801 /* IF p_quantity_received = 0 AND p_receiving_option = 1 THEN
802 -- close the move order and ship order because the receiving option is receiving short.
804 CSP_PL_SHIP_UTIL.Update_Packlist_Sts_Qty (
805 P_Api_Version_Number => l_api_version_number,
806 P_Init_Msg_List => FND_API.G_true,
807 P_Commit => l_commit,
808 p_validation_level => l_validation_level,
809 p_organization_id => p_organization_id,
810 p_packlist_line_id => p_packlist_line_id,
811 p_line_status => '4',
812 p_quantity_packed => NULL,
813 p_quantity_shipped => NULL,
814 p_quantity_received => 0,
815 x_return_status => l_return_status,
816 x_msg_count => l_msg_count,
817 x_msg_data => l_msg_data
818 );
819
820 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
821 RAISE FND_API.G_EXC_ERROR;
822 END IF;
823
824 IF fnd_api.to_boolean(CSP_PL_SHIP_UTIL.validate_pl_line_status(l_packlist_header_id, '3', true)) THEN
825 -- update the packlist_header_status
826 CSP_PL_SHIP_UTIL.update_packlist_header_sts (
827 P_Api_Version_Number => l_api_version_number,
828 P_Init_Msg_List => FND_API.G_true,
829 P_Commit => l_commit,
830 p_validation_level => l_validation_level,
831 p_packlist_header_id => l_packlist_header_id,
832 p_organization_id => p_organization_id,
833 p_packlist_status => '3',
834 x_return_status => l_return_status,
835 x_msg_count => l_msg_count,
836 x_msg_data => l_msg_data );
837 END IF;
838
839 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
840 RAISE FND_API.G_EXC_ERROR;
841 END IF;
842
843 OPEN l_ml_records(p_transaction_temp_id);
844 FETCH l_ml_records INTO l_csp_mtltxn_rec;
845
846 IF l_ml_records%NOTFOUND AND (p_receiving_option IN (0, 1)) THEN
847 fnd_message.set_name ('CSP', 'CSP_NO_MO_TXN_RECORD');
848 fnd_msg_pub.add;
849 CLOSE l_ml_records;
850 RAISE EXCP_USER_DEFINED;
851 END IF;
852 CLOSE l_ml_records;
853
854 l_move_order_line_id := l_csp_mtltxn_rec.move_order_line_id;
855
856 -- Update the header_status of the mtl_txn_request_headers table.
857 OPEN l_Get_Moveorder_Headers(l_move_order_line_id);
858 FETCH l_Get_Moveorder_Headers INTO l_header_id;
859 IF l_Get_Moveorder_Headers%NOTFOUND THEN
860 CLOSE l_Get_Moveorder_Headers;
861 fnd_message.set_name ('CSP', 'CSP_MOVEORDER_LINE_NO_PARENT');
862 fnd_message.set_token ('LINE_ID', to_char(l_move_order_line_id), FALSE);
863 fnd_msg_pub.add;
864 RAISE EXCP_USER_DEFINED;
865 END IF;
866 CLOSE l_Get_Moveorder_Headers;
867
868 INV_Trohdr_Util.Update_Row_Status
869 ( p_header_id => l_header_id,
870 p_status => 5);
871
872 Under_Over_Receipt (
873 p_transaction_temp_id => p_transaction_temp_id,
874 p_receiving_option => p_receiving_option,
875 px_transaction_header_id => l_transaction_header_id, --l_txn_header_id_cleaned,
876 p_discrepancy_qty => (-1 * l_csp_mtltxn_rec.transaction_quantity),
877 X_Return_Status => l_return_status,
878 X_Msg_Count => l_msg_count,
879 X_Msg_Data => l_msg_data);
880
881 IF l_transaction_header_id IS NULL THEN
882 -- messages have been set in the Under_Over_Receipt
883 RAISE EXCP_USER_DEFINED;
884 END IF;
885 GOTO END_JOB;
886 ELSE
887 fnd_message.set_name ('CSP', 'CSP_INVALID_QUANTITY_RECEIVED');
888 fnd_msg_pub.add;
889 RAISE EXCP_USER_DEFINED;
890 END IF; */
891 fnd_message.set_name ('CSP', 'CSP_INVALID_QUANTITY_RECEIVED');
892 fnd_msg_pub.add;
893 RAISE EXCP_USER_DEFINED;
897 IF l_Get_Shipped_Received_Qty%NOTFOUND THEN
894 ELSE
895 OPEN l_Get_Shipped_Received_Qty;
896 FETCH l_Get_Shipped_Received_Qty INTO l_quantity_shipped, l_quantity_received;
898 fnd_message.set_name ('CSP', 'CSP_INVALID_PACKLIST_LINE');
899 fnd_message.set_token ('LINE_ID', to_char(p_packlist_line_id), FALSE);
900 fnd_msg_pub.add;
901 CLOSE l_Get_Shipped_Received_Qty;
902 RAISE EXCP_USER_DEFINED;
903 END IF;
904 CLOSE l_Get_Shipped_Received_Qty;
905 END IF;
906
907 -- Validate the new to_subinventory and to_locator.
908 IF p_validation_level = fnd_api.g_valid_level_full THEN
909 IF p_to_subinventory_code IS NOT NULL THEN
910 DECLARE
911 l_subinventory_code VARCHAR2(10);
912 BEGIN
913 SELECT secondary_inventory_name into l_subinventory_code
914 FROM mtl_secondary_inventories
915 WHERE secondary_inventory_name = p_to_subinventory_code
916 AND organization_id = p_organization_id
917 AND nvl(disable_date, sysdate+1) > sysdate;
918 EXCEPTION
919 WHEN NO_DATA_FOUND THEN
920 fnd_message.set_name ('INV', 'INV-NO SUBINVENTORY RECORD');
921 fnd_message.set_token ('SUBINV', p_to_subinventory_code, FALSE);
922 fnd_message.set_token ('ORG', to_char(p_organization_id), FALSE);
923 fnd_msg_pub.add;
924 RAISE EXCP_USER_DEFINED;
925 WHEN OTHERS THEN
926 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
927 fnd_message.set_token('ERR_FIELD', 'p_to_subinventory_code', FALSE);
928 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
929 fnd_message.set_token('TABLE', 'csp_packlist_lines', FALSE);
930 fnd_msg_pub.add;
931 RAISE EXCP_USER_DEFINED;
932 END;
933 END IF;
934 IF p_to_locator_id IS NOT NULL THEN
935 BEGIN
936 SELECT inventory_location_id into l_check_existence
937 FROM mtl_item_locations
938 WHERE inventory_location_id = p_to_locator_id
939 AND organization_id = organization_id
940 AND subinventory_code = p_to_subinventory_code
941 AND nvl(disable_date, sysdate+1) > sysdate;
942 EXCEPTION
943 WHEN NO_DATA_FOUND THEN
944 fnd_message.set_name('INV', 'INV_LOCATOR_NOT_AVAILABLE');
945 fnd_msg_pub.add;
946 RAISE EXCP_USER_DEFINED;
947 WHEN OTHERS THEN
948 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
949 fnd_message.set_token('ERR_FIELD', 'p_locator_id', FALSE);
950 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
951 fnd_message.set_token('TABLE', 'mtl_item_locations', FALSE);
952 fnd_msg_pub.ADD;
953 RAISE EXCP_USER_DEFINED;
954 END;
955 END IF;
956 END IF;
957
958 -- It is now ready for the transactions
959 OPEN l_ml_records(p_transaction_temp_id);
960 FETCH l_ml_records INTO l_csp_mtltxn_rec;
961 IF l_ml_records%NOTFOUND AND (p_receiving_option IN (0, 1)) THEN
962 fnd_message.set_name ('CSP', 'CSP_NO_MO_TXN_RECORD');
963 fnd_msg_pub.add;
964 CLOSE l_ml_records;
965 RAISE EXCP_USER_DEFINED;
966 ELSIF l_ml_records%NOTFOUND AND (p_receiving_option IN (2, 3)) THEN
967 -- In this case, the user may have received all the items. This situation praticularly happens when
968 -- the user over receipts a series of serial items. For example, temp_id = 1001 is designated to receive
969 -- item_s of serial numbers from SN0 - SN4. Since confirm_receipt transacts one serial number at a time,
970 -- after the user received SN0, SN1, SN2 and SN3, the transaction quantity of temp_id 1001 is deducted to
971 -- 1. Finally the user received SN4, the temp record is finally deleted after online process. For over receipt,
972 -- the user may want to receive SN5 which is tied to the deleted temp_id. What we need to do is to reconstruct
973 -- the temp record before proceeding further.
974 CLOSE l_ml_records;
975
976 -- Prepare to create the mtl_material_transactions_temp record
977 l_csp_mtltxn_rec.transaction_temp_id := p_transaction_temp_id;
978 l_csp_mtltxn_rec.organization_id := p_organization_id;
979
980 SELECT line_id, inventory_item_id, to_subinventory_code, to_locator_id, uom_code
981 INTO l_csp_mtltxn_rec.move_order_line_id, l_csp_mtltxn_rec.inventory_item_id,
982 l_csp_mtltxn_rec.transfer_subinventory, l_csp_mtltxn_rec.transfer_to_location,
983 l_csp_mtltxn_rec.transaction_uom
984 FROM mtl_txn_request_lines
985 WHERE line_id = (SELECT line_id FROM csp_packlist_lines WHERE packlist_line_id = p_packlist_line_id)
986 AND organization_id = p_organization_id;
987
988 -- Find the serial control code, lot control code and item primary uom.
992 FROM mtl_system_items
989 SELECT primary_uom_code, serial_number_control_code, lot_control_code
990 INTO l_csp_mtltxn_rec.item_primary_uom_code, l_csp_mtltxn_rec.item_serial_control_code,
991 l_csp_mtltxn_rec.item_lot_control_code
993 WHERE inventory_item_id = l_csp_mtltxn_rec.inventory_item_id
994 AND organization_id = p_organization_id;
995
996 -- Find the Account Period ID
997 OPEN l_Get_Acct_Period_Csr;
998 FETCH l_Get_Acct_Period_Csr INTO l_csp_mtltxn_rec.acct_period_id;
999 CLOSE l_Get_Acct_Period_Csr;
1000
1001 l_csp_mtltxn_rec.transfer_subinventory := nvl(p_to_subinventory_code, l_csp_mtltxn_rec.transfer_subinventory);
1002 l_csp_mtltxn_rec.transfer_to_location := nvl(p_to_locator_id, l_csp_mtltxn_rec.transfer_to_location);
1003 l_csp_mtltxn_rec.transaction_quantity := p_quantity_received;
1004 l_csp_mtltxn_rec.primary_quantity := p_quantity_received;
1005 l_csp_mtltxn_rec.transaction_source_type_id := 13; -- Inventory
1006 l_csp_mtltxn_rec.transaction_type_id := 2; -- subinventory transfer type
1007 l_csp_mtltxn_rec.transaction_action_id := 2; -- subinventory tranfer
1008 l_csp_mtltxn_rec.process_flag := 'Y';
1009 l_csp_mtltxn_rec.transaction_status := 3;
1010 l_csp_mtltxn_rec.LAST_UPDATE_DATE := sysdate;
1011 l_csp_mtltxn_rec.CREATION_DATE := sysdate;
1012 l_csp_mtltxn_rec.created_by := g_user_id;
1013 l_csp_mtltxn_rec.last_update_login := g_login_id;
1014 l_csp_mtltxn_rec.last_updated_by := g_user_id;
1015
1016 -- change the revision to the specific revision when p_reivison is not null, 05/31/00 klou.
1017 l_csp_mtltxn_rec.revision := nvl(p_revision, l_csp_mtltxn_rec.revision);
1018
1019 IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_rec, p_quantity_received)) THEN
1020 -- Messages are set in the Convert_Temp_UOM function.
1021 RAISE EXCP_USER_DEFINED;
1022 END IF;
1023
1024 CSP_Material_Transactions_PVT.Create_material_transactions(
1025 P_Api_Version_Number => p_api_version_number,
1026 P_Init_Msg_List => p_init_msg_list,
1027 P_Commit => l_commit,
1028 p_validation_level => p_validation_level,
1029 P_CSP_Rec => l_csp_mtltxn_rec,
1030 X_TRANSACTION_TEMP_ID => l_transaction_temp_id,
1031 X_Return_Status => l_return_status,
1032 X_Msg_Count => l_msg_count,
1033 X_Msg_Data => l_msg_data
1034 );
1035
1036 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1037 RAISE FND_API.G_EXC_ERROR;
1038 END IF;
1039
1040 -- Now we have to check whether we need to insert into the mtlt and msnt tables.
1041 IF nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) <> 1 THEN
1042 IF nvl(p_lot_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1043 fnd_message.set_name('CSP', 'CSP_OVER_RECEIPT_LOT_MISSED');
1044 fnd_message.set_token('ITEM_NAME', csp_pp_util.get_item_name(l_csp_mtltxn_rec.inventory_item_id), FALSE);
1045 fnd_msg_pub.add;
1046 RAISE EXCP_USER_DEFINED;
1047 END IF;
1048
1049 -- Insert the record into the mtlt.
1050 l_mtlt_tbl(l_index).transaction_temp_id := p_transaction_temp_id;
1051 l_mtlt_tbl(l_index).last_update_date := sysdate;
1052 l_mtlt_tbl(l_index).last_updated_by := g_user_id;
1053 l_mtlt_tbl(l_index).creation_date := sysdate;
1054 l_mtlt_tbl(l_index).created_by := g_user_id;
1055 l_mtlt_tbl(l_index).last_update_login := g_login_id;
1056 l_mtlt_tbl(l_index).transaction_quantity := l_csp_mtltxn_rec.transaction_quantity;
1057 l_mtlt_tbl(l_index).primary_quantity := l_csp_mtltxn_rec.primary_quantity;
1058 l_mtlt_tbl(l_index).lot_number := p_lot_number;
1059
1060 SELECT expiration_date INTO l_mtlt_tbl(l_index).lot_expiration_date
1061 FROM mtl_lot_numbers
1062 WHERE inventory_item_id = l_csp_mtltxn_rec.inventory_item_id
1063 AND organization_id = p_organization_id
1064 AND lot_number = p_lot_number;
1065
1066 -- create a mtlt record
1067 csp_pp_util.insert_mtlt(
1068 x_return_status => l_return_status
1069 ,p_mtlt_tbl => l_mtlt_tbl
1070 ,p_mtlt_tbl_size => 1
1071 );
1072
1073 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1074 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
1075 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1076 fnd_message.set_token ('TABLE', 'MTL_TRANSACTION_LOTS_TEMP', FALSE);
1080
1077 fnd_msg_pub.add;
1078 RAISE EXCP_USER_DEFINED;
1079 END IF;
1081 -- If the item is also under serial control, we need to insert into the msnt.
1082 IF nvl(l_csp_mtltxn_rec.item_serial_control_code, 1) in (2,5) THEN
1083
1084 IF nvl(p_serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1085 fnd_message.set_name ('CSP', 'CSP_OVER_RECEIPT_SERIAL_MISSED');
1086 fnd_message.set_token('ITEM_NAME', csp_pp_util.get_item_name(l_csp_mtltxn_rec.inventory_item_id), FALSE);
1087 fnd_msg_pub.add;
1088 RAISE EXCP_USER_DEFINED;
1089 ELSE
1090 IF p_quantity_received <> 1 THEN
1091 fnd_message.set_name('CSP', 'CSP_OVER_RECEIPT_SERIAL_QTY');
1092 fnd_message.set_token('ITEM_NAME', csp_pp_util.get_item_name(l_csp_mtltxn_rec.inventory_item_id), FALSE);
1093 fnd_msg_pub.add;
1094 RAISE EXCP_USER_DEFINED;
1095 END IF;
1096 END IF;
1097
1098
1099 -- create a transaction_serial_temp_id.
1100 OPEN l_Get_Serial_Temp_id_Csr;
1101 FETCH l_Get_Serial_Temp_id_Csr INTO l_mtlt_tbl(l_index).serial_transaction_temp_id;
1102 CLOSE l_Get_Serial_Temp_id_Csr;
1103
1104 -- update the mtlt record
1105 update mtl_transaction_lots_temp
1106 set serial_transaction_temp_id = l_mtlt_tbl(l_index).serial_transaction_temp_id
1107 where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
1108 and lot_number = l_mtlt_tbl(l_index).lot_number;
1109
1110 If (SQL%NOTFOUND) then
1111 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
1112 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1113 fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', TRUE);
1114 fnd_msg_pub.add;
1115 RAISE EXCP_USER_DEFINED;
1116 End If;
1117
1118 -- create the msnt recrod
1119 l_msnt_tbl(l_index).transaction_temp_id := l_mtlt_tbl(l_index).serial_transaction_temp_id;
1120 l_msnt_tbl(l_index).last_update_date := sysdate;
1121 l_msnt_tbl(l_index).last_updated_by := g_user_id;
1122 l_msnt_tbl(l_index).creation_date := sysdate;
1123 l_msnt_tbl(l_index).created_by := g_user_id;
1124 l_msnt_tbl(l_index).last_update_login := g_login_id;
1125 l_msnt_tbl(l_index).fm_serial_number := p_serial_number;
1126 l_msnt_tbl(l_index).to_serial_number := p_serial_number;
1127 l_msnt_tbl(l_index).serial_prefix := 1;
1128
1129 csp_pp_util.insert_msnt(
1130 x_return_status => l_return_status
1131 ,p_msnt_tbl => l_msnt_tbl
1132 ,p_msnt_tbl_size => 1
1133 );
1134
1135 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1136 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
1137 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1138 fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', TRUE);
1139 fnd_msg_pub.add;
1140 RAISE EXCP_USER_DEFINED;
1141 END IF;
1142 END IF;
1143 ELSIF nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) = 1 AND
1144 nvl(l_csp_mtltxn_rec.item_serial_control_code, 1) in (2, 5) THEN
1145 -- Serial control only
1146 IF nvl(p_serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1147 fnd_message.set_name ('CSP', 'CSP_OVER_RECEIPT_SERIAL_MISSED');
1148 fnd_message.set_token('ITEM_NAME', csp_pp_util.get_item_name(l_csp_mtltxn_rec.inventory_item_id), FALSE);
1149 fnd_msg_pub.add;
1150 RAISE EXCP_USER_DEFINED;
1151 ELSE
1152 IF p_quantity_received <> 1 THEN
1153 fnd_message.set_name('CSP', 'CSP_OVER_RECEIPT_SERIAL_QTY');
1154 fnd_message.set_token('ITEM_NAME', csp_pp_util.get_item_name(l_csp_mtltxn_rec.inventory_item_id), FALSE);
1155 fnd_msg_pub.add;
1156 RAISE EXCP_USER_DEFINED;
1157 END IF;
1158 END IF;
1159 l_msnt_tbl(l_index).transaction_temp_id := p_transaction_temp_id;
1160 l_msnt_tbl(l_index).last_update_date := sysdate;
1161 l_msnt_tbl(l_index).last_updated_by := g_user_id;
1162 l_msnt_tbl(l_index).creation_date := sysdate;
1166 l_msnt_tbl(l_index).to_serial_number := p_serial_number;
1163 l_msnt_tbl(l_index).created_by := g_user_id;
1164 l_msnt_tbl(l_index).last_update_login := g_login_id;
1165 l_msnt_tbl(l_index).fm_serial_number := p_serial_number;
1167 l_msnt_tbl(l_index).serial_prefix := 1;
1168
1169 csp_pp_util.insert_msnt(
1170 x_return_status => l_return_status
1171 ,p_msnt_tbl => l_msnt_tbl
1172 ,p_msnt_tbl_size => 1
1173 );
1174
1175 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1176 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
1177 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1178 fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', TRUE);
1179 fnd_msg_pub.add;
1180 RAISE EXCP_USER_DEFINED;
1181 END IF;
1182 ELSE NULL; -- already taken care.
1183 END IF;
1184
1185 Under_Over_Receipt (
1186 p_transaction_temp_id => p_transaction_temp_id,
1187 p_receiving_option => p_receiving_option,
1188 px_transaction_header_id => l_transaction_header_id, --l_txn_header_id_cleaned,
1189 p_discrepancy_qty => l_csp_mtltxn_rec.transaction_quantity,
1190 X_Return_Status => l_return_status,
1191 X_Msg_Count => l_msg_count,
1192 X_Msg_Data => l_msg_data);
1193
1194 IF l_transaction_header_id IS NULL THEN
1195 -- messages have been set in the Under_Over_Receipt
1196 RAISE EXCP_USER_DEFINED;
1197 END IF;
1198 ELSE
1199 CLOSE l_ml_records;
1200 END IF;
1201
1202 l_move_order_line_id := l_csp_mtltxn_rec.move_order_line_id;
1203
1204 -- Open the l_Get_Moveorder_Headers to get the header_id to prepare the update of the
1205 -- header status in the mtl_txn_request_headers.
1206 OPEN l_Get_Moveorder_Headers(l_move_order_line_id);
1207 FETCH l_Get_Moveorder_Headers INTO l_header_id;
1208 IF l_Get_Moveorder_Headers%NOTFOUND THEN
1209 CLOSE l_Get_Moveorder_Headers;
1210 fnd_message.set_name ('CSP', 'CSP_MOVEORDER_LINE_NO_PARENT');
1211 fnd_message.set_token ('LINE_ID', to_char(l_move_order_line_id), FALSE);
1212 fnd_msg_pub.add;
1213 RAISE EXCP_USER_DEFINED;
1214 END IF;
1215 CLOSE l_Get_Moveorder_Headers;
1216
1217
1218 -- Check whether the item is subinventory restricted.
1219 -- If yes, we have to make sure the p_to_subinventory_code is in the restricted list.
1220 -- The process_online procedure will take care of the validations so it can be optional to do them here.
1221 IF p_validation_level = fnd_api.g_valid_level_full THEN
1222 IF p_to_subinventory_code IS NOT NULL THEN
1223 DECLARE
1224 l_restrict_sub_code NUMBER;
1225 l_inventory_item_id NUMBER := l_csp_mtltxn_rec.inventory_item_id;
1226 BEGIN
1227 select restrict_subinventories_code into l_restrict_sub_code
1228 from mtl_system_items
1229 where inventory_item_id = l_inventory_item_id
1230 and organization_id = p_organization_id;
1231 IF l_restrict_sub_code = 1 THEN -- the item is subinventory-restricted
1232 DECLARE
1233 l_restricted_sub VARCHAR2(10);
1234 BEGIN
1235 select secondary_inventory into l_restricted_sub
1236 from mtl_item_sub_inventories
1237 where organization_id = p_organization_id
1238 and inventory_item_id = l_inventory_item_id
1239 and secondary_inventory = p_to_subinventory_code;
1240 EXCEPTION
1241 WHEN NO_DATA_FOUND THEN
1242 fnd_message.set_name ('CSP', 'CSP_ITEM_SUB_VIOLATION');
1243 fnd_message.set_token ('SUB', p_to_subinventory_code, FALSE);
1244 fnd_msg_pub.add;
1245 RAISE EXCP_USER_DEFINED;
1246 WHEN OTHERS THEN
1247 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1248 fnd_message.set_token('ERR_FIELD', 'p_to_subinventory_code', FALSE);
1249 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
1250 fnd_message.set_token('TABLE', 'mtl_item_sub_inventories', FALSE);
1251 fnd_msg_pub.ADD;
1252 RAISE EXCP_USER_DEFINED;
1253 END;
1254 END IF;
1255 EXCEPTION
1256 WHEN NO_DATA_FOUND THEN
1257 fnd_message.set_name ('INV', 'INV-NO ITEM RECORD');
1258 fnd_msg_pub.add;
1259 RAISE EXCP_USER_DEFINED;
1260 WHEN OTHERS THEN
1264 RAISE EXCP_USER_DEFINED;
1261 fnd_message.set_name('CSP', 'CSP_ITEM_UNEXPECTED_ERRORS');
1262 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
1263 fnd_msg_pub.ADD;
1265 END;
1266 END IF;
1267 IF p_to_locator_id IS NOT NULL THEN
1268 -- check whether the item is under restrict locators
1269 DECLARE
1270 l_restrict_locators_code NUMBER;
1271 l_inventory_item_id NUMBER := l_csp_mtltxn_rec.inventory_item_id;
1272 BEGIN
1273 SELECT restrict_locators_code INTO l_restrict_locators_code
1274 FROM mtl_system_items
1275 WHERE inventory_item_id = l_inventory_item_id
1276 AND organization_id = p_organization_id;
1277
1278 IF l_restrict_locators_code = 1 THEN -- locators restricted to a predefined list.
1279 BEGIN
1280 SELECT locator_id INTO l_check_existence
1281 FROM mtl_item_loc_defaults
1282 WHERE inventory_item_id = l_inventory_item_id
1283 AND organization_id = p_organization_id
1284 AND locator_id = p_to_locator_id
1285 AND subinventory_code = p_to_subinventory_code;
1286 EXCEPTION
1287 WHEN NO_DATA_FOUND THEN
1288 fnd_message.set_name ('INV', 'INV_LOCATOR_NOT_AVAILABLE');
1289 fnd_msg_pub.add;
1290 RAISE EXCP_USER_DEFINED;
1291 WHEN OTHERS THEN
1292 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
1293 fnd_message.set_token('ERR_FIELD', 'p_to_locator_id', FALSE);
1294 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
1295 fnd_message.set_token('TABLE', 'mtl_item_loc_defaults', FALSE);
1296 fnd_msg_pub.ADD;
1297 RAISE EXCP_USER_DEFINED;
1298 END;
1299 END IF;
1300 END;
1301 END IF;
1302 END IF;
1303
1304 -- Check whether a new mmtt record is required.
1305 IF p_quantity_received > l_csp_mtltxn_rec.transaction_quantity THEN
1306
1307 -- In this case, the user has to specify a p_receiving_option either 2 or 3.
1308 IF p_receiving_option NOT IN (2, 3) THEN
1309 fnd_message.set_name('CSP', 'CSP_INVALID_OVER_RECEIPT_QTY');
1310 fnd_msg_pub.add;
1311 RAISE EXCP_USER_DEFINED;
1312 End If;
1313
1314 l_csp_mtltxn_over_rec := l_csp_mtltxn_rec;
1315
1316 -- Find the quantity_shipped - quantity_received for that packlist line id. Please see bug 1321353.
1317 Open l_Get_Org_Qty_Csr;
1318 Fetch l_Get_Org_Qty_Csr Into l_org_received_qty;
1319 Close l_Get_Org_Qty_Csr;
1320 If l_org_received_qty < 0 Then
1321 fnd_message.set_name('CSP', 'CSP_INVALID_OVER_RECEIPT_QTY');
1322 fnd_msg_pub.add;
1323 RAISE EXCP_USER_DEFINED;
1324 End If;
1325
1326 If l_org_received_qty < l_csp_mtltxn_rec.transaction_quantity Then
1327 -- In this case, we need to create two mmtt records. One for the l_org_received_qty, one for the over receipt quantity.
1328 -- We also need to update the existing mmtt record to the quantity of (transaction_quantity - l_org_received_qty).
1329 l_csp_mtltxn_bak_rec := l_csp_mtltxn_rec;
1330
1331 IF p_to_subinventory_code IS NOT NULL THEN
1332 l_csp_mtltxn_rec.transfer_subinventory := p_to_subinventory_code;
1333 l_csp_mtltxn_rec.transfer_to_location := p_to_locator_id;
1334 END IF;
1335
1336 -- change the revision to the specific revision when p_reivison is not null, 05/31/00 klou.
1337 l_csp_mtltxn_rec.revision := nvl(p_revision, l_csp_mtltxn_rec.revision);
1338
1339 -- Set the transaction_quantity = l_org_received_qty.
1340 l_csp_mtltxn_rec.transaction_quantity := l_org_received_qty;
1341
1342 IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_rec,
1343 (l_org_received_qty))) THEN
1344 -- Messages are set in the Convert_Temp_UOM function.
1345 RAISE EXCP_USER_DEFINED;
1346 ELSE
1347 l_csp_mtltxn_rec.primary_quantity := l_org_received_qty;
1348 END IF;
1349
1350 l_csp_mtltxn_rec.transaction_temp_id := NULL;
1351 l_csp_mtltxn_rec.creation_date := sysdate;
1352 l_csp_mtltxn_rec.last_update_date := sysdate;
1353
1354 CSP_Material_Transactions_PVT.Create_material_transactions(
1355 P_Api_Version_Number => p_api_version_number,
1356 P_Init_Msg_List => p_init_msg_list,
1357 P_Commit => l_commit,
1358 p_validation_level => p_validation_level,
1359 P_CSP_Rec => l_csp_mtltxn_rec,
1360 X_TRANSACTION_TEMP_ID => l_transaction_temp_id,
1364
1361 X_Return_Status => l_return_status,
1362 X_Msg_Count => l_msg_count,
1363 X_Msg_Data => l_msg_data);
1365 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1366 RAISE FND_API.G_EXC_ERROR;
1367 END IF;
1368 l_csp_mtltxn_rec.transaction_temp_id := l_transaction_temp_id;
1369
1370 l_temp_id_to_be_processed := l_csp_mtltxn_rec.transaction_temp_id;
1371
1372 -- Update the existing mmtt record.
1373 -- This mmtt record is intended to stay in the temp table for the next shipment.
1374 l_csp_mtltxn_bak_rec.transaction_quantity := l_csp_mtltxn_bak_rec.transaction_quantity - l_org_received_qty;
1375 IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_rec,
1376 (l_csp_mtltxn_bak_rec.transaction_quantity))) THEN
1377 -- Messages are set in the Convert_Temp_UOM function.
1378 RAISE EXCP_USER_DEFINED;
1379 ELSE
1380 l_csp_mtltxn_bak_rec.primary_quantity := l_csp_mtltxn_bak_rec.transaction_quantity;
1381 END IF;
1382
1383 CSP_Material_Transactions_PVT.Update_material_transactions(
1384 P_Api_Version_Number => p_api_version_number,
1385 P_Init_Msg_List => p_init_msg_list,
1386 P_Commit => l_commit,
1387 p_validation_level => p_validation_level,
1388 P_CSP_Rec => l_csp_mtltxn_rec,
1389 X_Return_Status => l_return_status,
1390 X_Msg_Count => l_msg_count,
1391 X_Msg_Data => l_msg_data);
1392
1393 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1394 RAISE FND_API.G_EXC_ERROR;
1395 END IF;
1396
1397 Elsif l_org_received_qty = l_csp_mtltxn_rec.transaction_quantity Then
1398
1399 -- update the transfer to subinventory, transfer to location id and revision of the
1400 -- remaining temp record.
1401 IF p_to_subinventory_code IS NOT NULL THEN
1402 l_csp_mtltxn_rec.transfer_subinventory := p_to_subinventory_code;
1403 l_csp_mtltxn_rec.transfer_to_location := p_to_locator_id;
1404 END IF;
1405
1406 -- change the revision to the specific revision when p_reivison is not null, 05/31/00 klou.
1407 l_csp_mtltxn_rec.revision := nvl(p_revision, l_csp_mtltxn_rec.revision);
1408
1409 CSP_Material_Transactions_PVT.Update_material_transactions(
1410 P_Api_Version_Number => p_api_version_number,
1411 P_Init_Msg_List => p_init_msg_list,
1412 P_Commit => fnd_api.g_false,
1413 p_validation_level => l_validation_level,
1414 P_CSP_Rec => l_csp_mtltxn_rec,
1415 X_Return_Status => l_return_status,
1416 X_Msg_Count => l_msg_count,
1417 X_Msg_Data => l_msg_data
1418 );
1419
1420 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1421 RAISE FND_API.G_EXC_ERROR;
1422 END IF;
1423 l_temp_id_to_be_processed := l_csp_mtltxn_rec.transaction_temp_id;
1424
1425 Else -- this is an error.
1426 fnd_message.set_name('CSP', 'CSP_INVALID_OVER_RECEIPT_QTY');
1427 fnd_msg_pub.add;
1428 RAISE EXCP_USER_DEFINED;
1429 End If;
1430
1431 l_csp_mtltxn_over_rec.creation_date := sysdate;
1432 l_csp_mtltxn_over_rec.last_update_date := sysdate;
1433 l_csp_mtltxn_over_rec.transaction_quantity := p_quantity_received - l_org_received_qty;
1434
1435 -- change the revision to the specific revision when p_reivison is not null, 05/31/00 klou.
1436 l_csp_mtltxn_over_rec.revision := nvl(p_revision, l_csp_mtltxn_over_rec.revision);
1437
1438 IF p_to_subinventory_code IS NOT NULL THEN
1439 /* For misc. receipt, the destination subinventory should be stored in the subinventory_code and locator_id.
1440 Storing these information in the transfer_subinventory and transafer_to_location causes the misc. receipt
1441 to error. */
1442 l_csp_mtltxn_over_rec.subinventory_code := p_to_subinventory_code;
1443 l_csp_mtltxn_over_rec.locator_id := p_to_locator_id;
1444
1445 l_csp_mtltxn_over_rec.transfer_subinventory := p_to_subinventory_code;
1446 l_csp_mtltxn_over_rec.transfer_to_location := p_to_locator_id;
1447 ELSE
1448 l_csp_mtltxn_over_rec.subinventory_code := l_csp_mtltxn_over_rec.transfer_subinventory;
1449 l_csp_mtltxn_over_rec.locator_id := l_csp_mtltxn_over_rec.transfer_to_location;
1450
1451 END IF;
1452
1453 IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_over_rec,
1457 ELSE
1454 (l_csp_mtltxn_over_rec.transaction_quantity))) THEN
1455 -- Messages are set in the Convert_Temp_UOM function.
1456 RAISE EXCP_USER_DEFINED;
1458 l_csp_mtltxn_over_rec.primary_quantity := l_csp_mtltxn_over_rec.transaction_quantity;
1459 END IF;
1460
1461 l_csp_mtltxn_over_rec.transaction_temp_id := NULL;
1462
1463 CSP_Material_Transactions_PVT.Create_material_transactions(
1464 P_Api_Version_Number => p_api_version_number,
1465 P_Init_Msg_List => p_init_msg_list,
1466 P_Commit => l_commit,
1467 p_validation_level => p_validation_level,
1468 P_CSP_Rec => l_csp_mtltxn_over_rec,
1469 X_TRANSACTION_TEMP_ID => l_transaction_temp_id,
1470 X_Return_Status => l_return_status,
1471 X_Msg_Count => l_msg_count,
1472 X_Msg_Data => l_msg_data);
1473
1474 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1475 RAISE FND_API.G_EXC_ERROR;
1476 END IF;
1477
1478 l_csp_mtltxn_over_rec.transaction_temp_id :=l_transaction_temp_id;
1479
1480 IF nvl(l_csp_mtltxn_over_rec.item_serial_control_code, 1) in (2,5) THEN
1481 -- This case should not happen because we always transact one serial number at time.
1482 -- Eventually, the transaction_qunatity is either 1 or the whole transaction temp record
1483 -- was deleted when the last expected serial number is transacted.
1484 fnd_message.set_name('CSP', 'CSP_OVER_RECEIPT_SERIAL_QTY');
1485 fnd_message.set_token('ITEM_NAME', csp_pp_util.get_item_name(l_csp_mtltxn_over_rec.inventory_item_id), FALSE);
1486 fnd_msg_pub.add;
1487 RAISE EXCP_USER_DEFINED;
1488 END IF;
1489 IF nvl(l_csp_mtltxn_over_rec.item_lot_control_code, 1) <> 1 THEN
1490 IF nvl(p_lot_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1491 fnd_message.set_name('CSP', 'CSP_OVER_RECEIPT_LOT_MISSED');
1492 fnd_message.set_token('ITEM_NAME', csp_pp_util.get_item_name(l_csp_mtltxn_over_rec.inventory_item_id), FALSE);
1493 fnd_msg_pub.add;
1494 RAISE EXCP_USER_DEFINED;
1495 END IF;
1496
1497 DECLARE
1498 l_mtlt_tbl csp_pp_util.g_mtlt_tbl_type;
1499 l_index NUMBER := 1;
1500 CURSOR l_Get_Mtlt IS
1501 SELECT * FROM mtl_transaction_lots_temp
1502 WHERE transaction_temp_id = l_csp_mtltxn_rec.transaction_temp_id -- Get the lot record form the original temp id
1503 ORDER BY TRANSACTION_QUANTITY DESC;
1504 -- Verify whether there is lot record in the mtlt table.
1505 BEGIN
1506 OPEN l_Get_Mtlt;
1507 FETCH l_Get_Mtlt INTO l_mtlt_tbl(l_index);
1508 IF l_Get_Mtlt%rowcount = 0 THEN
1509 fnd_message.set_name ('CSP', 'CSP_NO_LOT_TXN_RECORD');
1510 fnd_msg_pub.add;
1511 CLOSE l_Get_Mtlt;
1512 RAISE EXCP_USER_DEFINED;
1513 END IF;
1514 CLOSE l_Get_Mtlt;
1515
1516 l_mtlt_tbl(l_index).transaction_temp_id := l_csp_mtltxn_over_rec.transaction_temp_id;
1517 l_mtlt_tbl(l_index).lot_number := p_lot_number;
1518 l_mtlt_tbl(l_index).transaction_quantity := l_csp_mtltxn_over_rec.transaction_quantity;
1519 l_mtlt_tbl(l_index).primary_quantity := l_csp_mtltxn_over_rec.primary_quantity;
1520
1521 csp_pp_util.insert_mtlt(
1522 x_return_status => l_return_status
1523 ,p_mtlt_tbl => l_mtlt_tbl
1524 ,p_mtlt_tbl_size => 1
1525 );
1526
1527 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1528 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
1529 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1530 fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
1531 fnd_msg_pub.add;
1532 RAISE EXCP_USER_DEFINED;
1533 END IF;
1534
1535 EXCEPTION
1536 WHEN NO_DATA_FOUND THEN
1537 fnd_message.set_name ('CSP', 'CSP_OVER_RECEIPT_LOT_QTY');
1538 fnd_message.set_token ('LOT_NUMBER', p_lot_number, FALSE);
1539 fnd_msg_pub.add;
1540 RAISE EXCP_USER_DEFINED;
1541 WHEN OTHERS THEN
1542 RAISE EXCP_USER_DEFINED;
1543 END;
1544 END IF;
1545
1546 Under_Over_Receipt (
1547 p_transaction_temp_id => l_csp_mtltxn_over_rec.transaction_temp_id,
1548 p_receiving_option => p_receiving_option,
1549 px_transaction_header_id => l_transaction_header_id, --l_txn_header_id_cleaned,
1550 p_discrepancy_qty => l_csp_mtltxn_over_rec.transaction_quantity,
1554
1551 X_Return_Status => l_return_status,
1552 X_Msg_Count => l_msg_count,
1553 X_Msg_Data => l_msg_data);
1555 IF l_transaction_header_id IS NULL THEN
1556 -- messages have been set in the Under_Over_Receipt
1557 RAISE EXCP_USER_DEFINED;
1558 END IF;
1559
1560 -- Update the mtl_txn_request_lines and mtl_txn_request_headers tables.
1561 BEGIN
1562 select nvl(quantity, 0) into l_trolin_rec.quantity
1563 from mtl_txn_request_lines
1564 where line_id = l_move_order_line_id;
1565
1566 select nvl(quantity_delivered,0) into l_trolin_rec.quantity_delivered
1567 from mtl_txn_request_lines
1568 where line_id = l_move_order_line_id;
1569
1570 EXCEPTION
1571 WHEN OTHERS THEN
1572 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1573 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1574 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1575 fnd_msg_pub.add;
1576 RAISE EXCP_USER_DEFINED;
1577 END;
1578
1579 -- Update order line status and order header status
1580 IF l_trolin_rec.quantity_delivered = l_trolin_rec.quantity
1581 OR (p_receiving_option = 3) THEN
1582
1583 INV_Trolin_Util.Update_Row_Status
1584 ( p_line_id => l_move_order_line_id,
1585 p_status => 5 ); -- update status to 5 = closed
1586
1587 INV_Trohdr_Util.Update_Row_Status
1588 ( p_header_id =>l_header_id,
1589 p_status => 5); -- update status to 5 = closed
1590
1591 END IF; -- end the update_line_status block
1592
1593 ELSIF p_quantity_received = l_csp_mtltxn_rec.transaction_quantity THEN
1594 IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_rec, p_quantity_received)) THEN
1595 -- Messages are set at the Convert_Temp_UOM function.
1596 RAISE EXCP_USER_DEFINED;
1597 ELSE
1598 l_csp_mtltxn_rec.primary_quantity := l_csp_mtltxn_rec.transaction_quantity;
1599 END IF;
1600
1601 IF p_to_subinventory_code IS NOT NULL THEN
1602 l_csp_mtltxn_rec.transfer_subinventory := p_to_subinventory_code;
1603 l_csp_mtltxn_rec.transfer_to_location := p_to_locator_id;
1604 END IF;
1605
1606 -- change the revision to the specific revision when p_reivison is not null, 05/31/00 klou.
1607 l_csp_mtltxn_rec.revision := nvl(p_revision, l_csp_mtltxn_rec.revision);
1608
1609 CSP_Material_Transactions_PVT.Update_material_transactions(
1610 P_Api_Version_Number => p_api_version_number,
1611 P_Init_Msg_List => p_init_msg_list,
1612 P_Commit => fnd_api.g_false,
1613 p_validation_level => l_validation_level,
1614 P_CSP_Rec => l_csp_mtltxn_rec,
1615 X_Return_Status => l_return_status,
1616 X_Msg_Count => l_msg_count,
1617 X_Msg_Data => l_msg_data
1618 );
1619
1620 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1621 RAISE FND_API.G_EXC_ERROR;
1622 END IF;
1623
1624 l_temp_id_to_be_processed := l_csp_mtltxn_rec.transaction_temp_id;
1625
1626 BEGIN
1627 select nvl(quantity, 0) into l_trolin_rec.quantity
1628 from mtl_txn_request_lines
1629 where line_id = l_move_order_line_id;
1630
1631 select nvl(quantity_delivered,0) into l_trolin_rec.quantity_delivered
1632 from mtl_txn_request_lines
1633 where line_id = l_move_order_line_id;
1634
1635 EXCEPTION
1636 WHEN OTHERS THEN
1637 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1638 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1639 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1640 fnd_msg_pub.add;
1641 RAISE EXCP_USER_DEFINED;
1642 END;
1643
1644 -- update order line status and order header status
1645 IF l_trolin_rec.quantity_delivered = l_trolin_rec.quantity THEN
1646 INV_Trolin_Util.Update_Row_Status
1647 ( p_line_id => l_move_order_line_id,
1648 p_status => 5 ); -- update status to 5 = closed
1649
1650 -- check whether all the line statuses of the order have been closed.
1651 -- If they are all closed, update the header status.
1652 IF FND_API.to_boolean(validate_mo_line_status (l_header_id, 5)) THEN
1653 -- update the header status in the mtl_txn_request_headers
1654 -- call a core apps api to update the line status.
1655 -- Since the core apps api does not return a status, we have to catch the exception
1656 -- it may throw.
1660 p_status => 5); -- update status to 5 = closed
1657 BEGIN
1658 INV_Trohdr_Util.Update_Row_Status
1659 ( p_header_id => l_header_id,
1661
1662 x_return_status := FND_API.G_RET_STS_SUCCESS;
1663 EXCEPTION
1664 WHEN OTHERS THEN
1665 RAISE FND_API.G_EXC_ERROR;
1666 END;
1667 END IF;
1668 END IF; -- end the update_line_status block
1669
1670 ELSE -- p_quantity_received less than transaction_quantity
1671 -- 1. create a new mmtt record having transaction_quantity = p_quantity_received
1672 -- and process_flag = 'Y' and transaction_status = 3.
1673 -- 2. update the transaction_quantity of the existing one to the remaining quantity.
1674 l_recv_less_than_txn := fnd_api.g_true;
1675
1676 IF p_receiving_option NOT IN (0, 1) THEN
1677 fnd_message.set_name('CSP', 'CSP_INVALID_OVER_RECEIPT_QTY');
1678 fnd_msg_pub.add;
1679 RAISE EXCP_USER_DEFINED;
1680 END IF;
1681 l_csp_mtltxn_bak_rec := l_csp_mtltxn_rec;
1682
1683 DECLARE -- beginning of main transaction
1684 CURSOR l_Get_Packlist_SL IS
1685 SELECT PACKLIST_SERIAL_LOT_ID,
1686 CREATED_BY,
1687 CREATION_DATE,
1688 LAST_UPDATED_BY,
1689 LAST_UPDATE_DATE,
1690 LAST_UPDATE_LOGIN,
1691 PACKLIST_LINE_ID,
1692 ORGANIZATION_ID,
1693 INVENTORY_ITEM_ID,
1694 QUANTITY,
1695 LOT_NUMBER,
1696 SERIAL_NUMBER
1697 FROM CSP_Packlist_Serial_Lots
1698 WHERE packlist_line_id = p_packlist_line_id
1699 AND organization_id = p_organization_id;
1700 l_packlist_sl_rec CSP_Pack_Serial_Lots_PVT.plsl_Rec_Type;
1701 l_serial_number VARCHAR2(30):= NULL;
1702 l_lot_number VARCHAR2(80) := NULL;
1703 l_serial_lot_flag VARCHAR2(1) := fnd_api.g_false;
1704
1705 BEGIN
1706 If p_quantity_received > 0 Then
1707 l_csp_mtltxn_rec.transaction_quantity := p_quantity_received;
1708 l_csp_mtltxn_rec.primary_quantity := p_quantity_received;
1709 l_csp_mtltxn_rec.transaction_temp_id := NULL;
1710 l_move_order_line_id := l_csp_mtltxn_rec.move_order_line_id;
1711
1712 IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_rec, p_quantity_received)) THEN
1713 -- Messages are set in the Convert_Temp_UOM function.
1714 RAISE EXCP_USER_DEFINED;
1715 ELSE
1716 l_csp_mtltxn_rec.primary_quantity := l_csp_mtltxn_rec.transaction_quantity;
1717 END IF;
1718
1719 -- Change the revision to the specific revision when p_reivison is not null, 05/31/00 klou.
1720 l_csp_mtltxn_rec.revision := nvl(p_revision, l_csp_mtltxn_rec.revision);
1721
1722 IF p_to_subinventory_code IS NOT NULL THEN
1723 l_csp_mtltxn_rec.transfer_subinventory := p_to_subinventory_code;
1724 l_csp_mtltxn_rec.transfer_to_location := p_to_locator_id;
1725 END IF;
1726
1727 CSP_Material_Transactions_PVT.Create_material_transactions(
1728 P_Api_Version_Number => p_api_version_number,
1729 P_Init_Msg_List => p_init_msg_list,
1730 P_Commit => l_commit,
1731 p_validation_level => p_validation_level,
1732 P_CSP_Rec => l_csp_mtltxn_rec,
1733 X_TRANSACTION_TEMP_ID => l_transaction_temp_id,
1734 X_Return_Status => l_return_status,
1735 X_Msg_Count => l_msg_count,
1736 X_Msg_Data => l_msg_data);
1737
1738 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1739 RAISE FND_API.G_EXC_ERROR;
1740 END IF;
1741 l_temp_id_to_be_processed := l_transaction_temp_id;
1742 -- update the existing mmtt record
1743 l_csp_mtltxn_bak_rec.transaction_quantity := l_csp_mtltxn_bak_rec.transaction_quantity - p_quantity_received;
1744 -- l_csp_mtltxn_bak_rec.primary_quantity := l_csp_mtltxn_bak_rec.primary_quantity - p_quantity_received;
1745 l_csp_mtltxn_bak_rec.last_update_date := sysdate;
1746
1747 IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_bak_rec, l_csp_mtltxn_bak_rec.transaction_quantity)) THEN
1748 -- Messages are set in the Convert_Temp_UOM function.
1749 RAISE EXCP_USER_DEFINED;
1750 ELSE
1751 l_csp_mtltxn_bak_rec.primary_quantity := l_csp_mtltxn_bak_rec.transaction_quantity;
1755 P_Api_Version_Number => p_api_version_number,
1752 END IF;
1753
1754 CSP_Material_Transactions_PVT.Update_material_transactions(
1756 P_Init_Msg_List => p_init_msg_list,
1757 P_Commit => fnd_api.g_false,
1758 p_validation_level => l_validation_level,
1759 P_CSP_Rec => l_csp_mtltxn_bak_rec,
1760 X_Return_Status => l_return_status,
1761 X_Msg_Count => l_msg_count,
1762 X_Msg_Data => l_msg_data
1763 );
1764
1765 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1766 RAISE FND_API.G_EXC_ERROR;
1767 END IF;
1768
1769 -- Check whether we need to update the msnt and the mtlt tables.
1770 OPEN l_Get_Packlist_SL;
1771 LOOP
1772 FETCH l_Get_Packlist_SL INTO l_packlist_sl_rec;
1773 EXIT WHEN l_Get_Packlist_SL%NOTFOUND;
1774 IF p_serial_number IS NULL THEN
1775 l_serial_number := l_packlist_sl_rec.serial_number;
1776 IF p_lot_number IS NULL THEN
1777 l_lot_number := l_packlist_sl_rec.lot_number;
1778 ELSE
1779 l_lot_number := p_lot_number;
1780 END IF;
1781
1782 IF l_lot_number = l_packlist_sl_rec.lot_number OR
1783 l_lot_number IS NULL THEN
1784 Transact_Serial_Lots (
1785 p_new_transaction_temp_id => l_transaction_temp_id,
1786 p_old_transaction_temp_id => l_csp_mtltxn_bak_rec.transaction_temp_id,
1787 p_lot_number => l_lot_number,
1788 p_serial_number => l_serial_number,
1789 p_qty_received => p_quantity_received,
1790 X_Return_Status => l_return_status,
1791 X_Msg_Count => l_msg_count,
1792 X_Msg_Data => l_msg_data);
1793 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1794 CLOSE l_Get_Packlist_SL;
1795 RAISE FND_API.G_EXC_ERROR;
1796 END IF;
1797 END IF;
1798
1799 l_serial_lot_flag := fnd_api.g_true;
1800 ELSE
1801 l_serial_number := p_serial_number;
1802 IF p_lot_number IS NULL THEN
1803 l_lot_number := l_packlist_sl_rec.lot_number;
1804 ELSE
1805 l_lot_number := p_lot_number;
1806 END IF;
1807 IF (l_lot_number = l_packlist_sl_rec.lot_number OR
1808 l_lot_number IS NULL) AND
1809 l_serial_number = l_packlist_sl_rec.serial_number THEN
1810 Transact_Serial_Lots (
1811 p_new_transaction_temp_id => l_transaction_temp_id,
1812 p_old_transaction_temp_id => l_csp_mtltxn_bak_rec.transaction_temp_id,
1813 p_lot_number => l_lot_number,
1814 p_serial_number => l_serial_number,
1815 p_qty_received => p_quantity_received,
1816 X_Return_Status => l_return_status,
1817 X_Msg_Count => l_msg_count,
1818 X_Msg_Data => l_msg_data);
1819 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1820 CLOSE l_Get_Packlist_SL;
1821 RAISE FND_API.G_EXC_ERROR;
1822 END IF;
1823 l_serial_lot_flag := fnd_api.g_true;
1824 EXIT;
1825 END IF;
1826 END IF;
1827 END LOOP;
1828 IF NOT fnd_api.to_boolean(l_serial_lot_flag) AND
1829 l_Get_Packlist_SL%rowcount <> 0 THEN
1830 fnd_message.set_name ('CSP', 'CSP_NO_SERIAL_LOT_PACKLIST');
1831 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1832 fnd_msg_pub.add;
1833 CLOSE l_Get_Packlist_SL;
1834 RAISE EXCP_USER_DEFINED;
1835 END IF;
1836 IF l_Get_Packlist_SL%ISOPEN THEN
1837 CLOSE l_Get_Packlist_SL;
1838 END IF;
1839 End If; --end p_quantity_received > 0
1840
1841 IF p_receiving_option = 1 THEN
1842 DECLARE -- main misc issue txn
1843 l_misc_issue_qty NUMBER := 0;
1844 BEGIN
1848
1845 Open l_Get_Org_Qty_Csr;
1846 Fetch l_Get_Org_Qty_Csr Into l_org_received_qty;
1847 Close l_Get_Org_Qty_Csr;
1849 If l_org_received_qty < 0 Then
1850 fnd_message.set_name('CSP', 'CSP_INVALID_OVER_RECEIPT_QTY');
1851 fnd_msg_pub.add;
1852 RAISE EXCP_USER_DEFINED;
1853 End If;
1854
1855 -- Now we need to do a misc. issue of which the txn qty = l_org_received_qty - p_quantity_received
1856 l_misc_issue_qty := l_org_received_qty - p_quantity_received;
1857
1858 If l_misc_issue_qty = l_csp_mtltxn_bak_rec.transaction_quantity Then
1859 -- 1. Update the move order header.
1860 -- 2. Transact l_csp_mtltxn_bak_rec as misc issue.
1861 -- Receipt short: update the header status
1862 INV_Trohdr_Util.Update_Row_Status ( p_header_id => l_header_id,
1863 p_status => 5);
1864
1865 l_csp_mtltxn_misc_issue_rec := l_csp_mtltxn_bak_rec;
1866 Else
1867 -- 1. Create a new temp record whose transaction_quantity = l_misc_issue_qty.
1868 -- 2. Update the l_csp_mtltxn_bak_rec such that the
1869 -- transaction_quantity = l_csp_mtltxn_bak_rec.transaction_quantity - l_misc_issue_qty.
1870 -- * Please be aware that receiving short of serial items is not available at release 2.
1871
1872 l_csp_mtltxn_misc_issue_rec := l_csp_mtltxn_bak_rec;
1873 l_csp_mtltxn_misc_issue_rec.transaction_quantity := l_misc_issue_qty;
1874 l_csp_mtltxn_misc_issue_rec.creation_date := sysdate;
1875
1876 IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_misc_issue_rec,
1877 (l_csp_mtltxn_misc_issue_rec.transaction_quantity))) THEN
1878 -- Messages are set in the Convert_Temp_UOM function.
1879 RAISE EXCP_USER_DEFINED;
1880 ELSE
1881 l_csp_mtltxn_misc_issue_rec.primary_quantity := l_csp_mtltxn_misc_issue_rec.transaction_quantity;
1882 END IF;
1883
1884 l_csp_mtltxn_misc_issue_rec.transaction_temp_id := NULL;
1885
1886 CSP_Material_Transactions_PVT.Create_material_transactions(
1887 P_Api_Version_Number => p_api_version_number,
1888 P_Init_Msg_List => p_init_msg_list,
1889 P_Commit => l_commit,
1890 p_validation_level => p_validation_level,
1891 P_CSP_Rec => l_csp_mtltxn_misc_issue_rec,
1892 X_TRANSACTION_TEMP_ID => l_transaction_temp_id,
1893 X_Return_Status => l_return_status,
1894 X_Msg_Count => l_msg_count,
1895 X_Msg_Data => l_msg_data);
1896
1897 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1898 RAISE FND_API.G_EXC_ERROR;
1899 END IF;
1900
1901 l_csp_mtltxn_misc_issue_rec.transaction_temp_id :=l_transaction_temp_id;
1902 --//// Take care of the msnt and mtlt records when the whole packlist is closed in short, i.e. p_quantity_received=0
1903 -- Check whether we need to update the msnt and the mtlt tables.
1904 If p_quantity_received = 0 Then
1905 OPEN l_Get_Packlist_SL;
1906 LOOP
1907 FETCH l_Get_Packlist_SL INTO l_packlist_sl_rec;
1908 EXIT WHEN l_Get_Packlist_SL%NOTFOUND;
1909 l_lot_number := l_packlist_sl_rec.lot_number;
1910 l_serial_number := l_packlist_sl_rec.serial_number;
1911
1912 IF l_serial_number IS NULL AND l_lot_number IS NOT NULL THEN
1913 -- Items only under lot control.
1914 Transact_Serial_Lots (
1915 p_new_transaction_temp_id => l_transaction_temp_id,
1916 p_old_transaction_temp_id => l_csp_mtltxn_bak_rec.transaction_temp_id,
1917 p_lot_number => l_lot_number,
1918 p_serial_number => l_serial_number,
1919 p_qty_received => l_misc_issue_qty,
1920 X_Return_Status => l_return_status,
1921 X_Msg_Count => l_msg_count,
1922 X_Msg_Data => l_msg_data);
1923 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1927 l_serial_lot_flag := fnd_api.g_true;
1924 CLOSE l_Get_Packlist_SL;
1925 RAISE FND_API.G_EXC_ERROR;
1926 END IF;
1928 ELSIF l_serial_number IS NOT NULL THEN
1929 -- The item is under serial control. It may be or may not be under lot control.
1930 -- However, whether it is under lot control or not, it is not important to us
1931 -- because we have to transact one serial number at a time.
1932 Transact_Serial_Lots (
1933 p_new_transaction_temp_id => l_transaction_temp_id,
1934 p_old_transaction_temp_id => l_csp_mtltxn_bak_rec.transaction_temp_id,
1935 p_lot_number => l_lot_number,
1936 p_serial_number => l_serial_number,
1937 p_qty_received => 1,
1938 X_Return_Status => l_return_status,
1939 X_Msg_Count => l_msg_count,
1940 X_Msg_Data => l_msg_data);
1941 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1942 CLOSE l_Get_Packlist_SL;
1943 RAISE FND_API.G_EXC_ERROR;
1944 END IF;
1945 l_serial_lot_flag := fnd_api.g_true;
1946 ELSE
1947 -- This is an error. The csp_packlist_serial_lots table should not contain any error
1948 -- that has neither serial number nor lot number.
1949 l_serial_lot_flag := fnd_api.g_false;
1950 exit;
1951 END IF;
1952 END LOOP;
1953 IF NOT fnd_api.to_boolean(l_serial_lot_flag) AND
1954 l_Get_Packlist_SL%rowcount <> 0 THEN
1955 fnd_message.set_name ('CSP', 'CSP_NO_SERIAL_LOT_PACKLIST');
1956 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1957 fnd_msg_pub.add;
1958 CLOSE l_Get_Packlist_SL;
1959 RAISE EXCP_USER_DEFINED;
1960 END IF;
1961 IF l_Get_Packlist_SL%ISOPEN THEN
1962 CLOSE l_Get_Packlist_SL;
1963 END IF;
1964 End If; -- end p_quantity_received = 0
1965
1966 --//////////////////////
1967 -- update the existing record
1968 l_csp_mtltxn_bak_rec.transaction_quantity := l_csp_mtltxn_bak_rec.transaction_quantity - l_misc_issue_qty;
1969 l_csp_mtltxn_bak_rec.last_update_date := sysdate;
1970
1971 IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_bak_rec, l_csp_mtltxn_bak_rec.transaction_quantity)) THEN
1972 -- Messages are set in the Convert_Temp_UOM function.
1973 RAISE EXCP_USER_DEFINED;
1974 ELSE
1975 l_csp_mtltxn_bak_rec.primary_quantity := l_csp_mtltxn_bak_rec.transaction_quantity;
1976 END IF;
1977
1978 CSP_Material_Transactions_PVT.Update_material_transactions(
1979 P_Api_Version_Number => p_api_version_number,
1980 P_Init_Msg_List => p_init_msg_list,
1981 P_Commit => fnd_api.g_false,
1982 p_validation_level => l_validation_level,
1983 P_CSP_Rec => l_csp_mtltxn_bak_rec,
1984 X_Return_Status => l_return_status,
1985 X_Msg_Count => l_msg_count,
1986 X_Msg_Data => l_msg_data
1987 );
1988
1989 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1990 RAISE FND_API.G_EXC_ERROR;
1991 END IF;
1992 End If;
1993
1994 -- Initialize the misc. issue.
1995 Under_Over_Receipt (
1996 p_transaction_temp_id => l_csp_mtltxn_misc_issue_rec.transaction_temp_id,
1997 p_receiving_option => p_receiving_option,
1998 px_transaction_header_id => l_transaction_header_id,
1999 p_discrepancy_qty => (-1 * l_csp_mtltxn_misc_issue_rec.transaction_quantity),
2000 X_Return_Status => l_return_status,
2001 X_Msg_Count => l_msg_count,
2002 X_Msg_Data => l_msg_data);
2006 RAISE EXCP_USER_DEFINED;
2003
2004 IF l_transaction_header_id IS NULL THEN
2005 -- messages were set at the sub function level.
2007 END IF;
2008 END; -- end main misc issue txn
2009 END IF; -- end p_receiving_option = 0
2010 END; -- End of main transaction (p_quantity_received < transaction_quantity).
2011 END IF;
2012
2013 -- Define the packlist line status.
2014 -- 4 = received short, 3 = received, 2 = shipped, 1 = open
2015 IF p_receiving_option = 0 THEN
2016 IF l_quantity_shipped = (l_quantity_received + p_quantity_received) THEN
2017 l_packlist_line_status := '3';
2018 END IF;
2019 ELSIF p_receiving_option = 1 THEN
2020 l_packlist_line_status := '4'; -- receipt short.
2021 ELSIF p_receiving_option = 3 THEN -- over receipt, but close the packlist and move order
2022 l_packlist_line_status := '3';
2023 ELSE
2024 l_packlist_line_status := NULL;
2025 END IF;
2026
2027 -- Update quantity_received in the csp_packlist_lines table
2028 CSP_PL_SHIP_UTIL.Update_Packlist_Sts_Qty (
2029 P_Api_Version_Number => l_api_version_number,
2030 P_Init_Msg_List => FND_API.G_true,
2031 P_Commit => l_commit,
2032 p_validation_level => l_validation_level,
2033 p_organization_id => p_organization_id,
2034 p_packlist_line_id => p_packlist_line_id,
2035 p_line_status => l_packlist_line_status,
2036 p_quantity_packed => NULL,
2037 p_quantity_shipped => NULL,
2038 p_quantity_received => (l_quantity_received + p_quantity_received),
2039 x_return_status => l_return_status,
2040 x_msg_count => l_msg_count,
2041 x_msg_data => l_msg_data
2042 );
2043
2044 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2045 RAISE FND_API.G_EXC_ERROR;
2046 END IF;
2047
2048 -- Define the packlist header status
2049 IF p_receiving_option = 0 OR p_receiving_option = 3 THEN
2050 BEGIN
2051 SELECT count(packlist_line_status)
2052 INTO l_count
2053 FROM csp_packlist_lines
2054 WHERE packlist_header_id = l_packlist_header_id
2055 AND packlist_line_status = 4;
2056 EXCEPTION
2057 WHEN no_data_found THEN
2058 null;
2059 END;
2060
2061 IF fnd_api.to_boolean(CSP_PL_SHIP_UTIL.validate_pl_line_status(l_packlist_header_id, '3', true)) THEN
2062 IF (l_count > 0) THEN
2063 l_packlist_header_status := '4';
2064 ELSE
2065 l_packlist_header_status := '3';
2066 END IF;
2067 ELSE
2068 l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
2069 END IF;
2070 ELSIF p_receiving_option = 1 THEN
2071 IF fnd_api.to_boolean(CSP_PL_SHIP_UTIL.validate_pl_line_status(l_packlist_header_id, '3', true)) THEN
2072 l_packlist_header_status := '4';
2073 ELSE
2074 l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
2075 END IF;
2076 ELSE
2077 l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
2078 END IF;
2079
2080 -- Update the date_received and the packlist_status in the packlist. klou 5/17/00.
2081 Open l_Get_Packlist_Csr(l_packlist_header_id);
2082 Fetch l_Get_Packlist_Csr Into l_packlist_headers_rec;
2083 If l_Get_Packlist_Csr%NOTFOUND Then
2084 fnd_message.set_name ('CSP', 'CSP_INVALID_PACKLIST_LINE');
2085 fnd_message.set_token ('LINE_ID', to_char(p_packlist_line_id), FALSE);
2086 fnd_msg_pub.add;
2087 Close l_Get_Packlist_Csr;
2088 RAISE EXCP_USER_DEFINED;
2089 Else
2090 Close l_Get_Packlist_Csr;
2091 End If;
2092
2093 l_packlist_headers_rec.date_received := sysdate;
2094 l_packlist_headers_rec.last_update_date := sysdate;
2095 l_packlist_headers_rec.packlist_status := l_packlist_header_status;
2096
2097 -- Call the CSP_Packlist_Headers_PVT.Update_packlist_headers to update the packlist_status.
2098 CSP_Packlist_Headers_PVT.Update_packlist_headers(
2099 P_Api_Version_Number => l_api_version_number,
2100 P_Init_Msg_List => p_init_msg_list,
2101 P_Commit => FND_API.G_FALSE,
2102 p_validation_level => l_validation_level,
2103 P_Identity_Salesforce_Id => NULL,
2104 P_PLH_Rec => l_packlist_headers_rec,
2105 X_Return_Status => l_return_status,
2106 X_Msg_Count => l_msg_count,
2107 X_Msg_Data => l_msg_data);
2108
2109 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2110 RAISE FND_API.G_EXC_ERROR;
2111 END IF;
2112
2113 If l_temp_id_to_be_processed IS NOT NULL THEN
2114 -- We are now ready to insert the data into interface table and delete the record in the temp table
2115 OPEN l_ml_records(l_temp_id_to_be_processed);
2116 FETCH l_ml_records INTO l_csp_mtltxn_rec;
2117 Close l_ml_records;
2118 -- Get the transaction header id
2122 Close l_Get_txn_header_id_csr;
2119 Open l_Get_txn_header_id_csr;
2120 IF l_transaction_header_id IS NULL THEN
2121 Fetch l_Get_txn_header_id_csr Into l_transaction_header_id;
2123 END IF;
2124 -- Call the csp_transactions_pub.transact_temp_record
2125 csp_transactions_pub.transact_temp_record (
2126 P_Api_Version_Number => l_api_version_number,
2127 P_Init_Msg_List => FND_API.G_true,
2128 P_Commit => l_commit,
2129 p_validation_level => l_validation_level,
2130 p_transaction_temp_id => l_temp_id_to_be_processed,
2131 px_transaction_header_id => l_transaction_header_id,
2132 p_online_process_flag => FALSE,
2133 X_Return_Status => l_return_status,
2134 X_Msg_Count => l_msg_count,
2135 X_Msg_Data => l_msg_data );
2136
2137 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2139 END IF;
2140
2141 -- before calling the online process manager, we need to remove the temp record
2142 IF not fnd_api.to_boolean(Clean_Up (l_temp_id_to_be_processed)) THEN
2143 RAISE EXCP_USER_DEFINED;
2144 END IF;
2145 End If;
2146 <<END_JOB>>
2147 -- Call the process_online to submit a concurrent request.
2148 IF fnd_api.to_boolean(p_process_flag) THEN
2149 IF NOT Call_Online ( p_transaction_header_id => l_transaction_header_id) THEN
2150 l_outcome := FALSE;
2151 x_return_status := FND_API.G_RET_STS_ERROR;
2152 GOTO END_ALL;
2153 END IF;
2154 END IF;
2155
2156 px_transaction_header_id := l_transaction_header_id;
2157
2158 IF fnd_api.to_boolean(p_commit) THEN
2159 commit work;
2160 END IF;
2161 x_return_status := FND_API.G_RET_STS_SUCCESS;
2162
2163 <<END_ALL>>
2164 fnd_msg_pub.count_and_get
2165 ( p_count => x_msg_count
2166 , p_data => x_msg_data);
2167 EXCEPTION
2168 WHEN EXCP_USER_DEFINED THEN
2169 Rollback to confirm_receipt_PUB;
2170 x_return_status := FND_API.G_RET_STS_ERROR;
2171 fnd_msg_pub.count_and_get
2172 ( p_count => x_msg_count
2173 , p_data => x_msg_data);
2174 WHEN FND_API.G_EXC_ERROR THEN
2175 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2176 P_API_NAME => L_API_NAME
2177 ,P_PKG_NAME => G_PKG_NAME
2178 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2179 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2180 ,X_MSG_COUNT => X_MSG_COUNT
2181 ,X_MSG_DATA => X_MSG_DATA
2182 ,X_RETURN_STATUS => X_RETURN_STATUS);
2183 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2184 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2185 P_API_NAME => L_API_NAME
2186 ,P_PKG_NAME => G_PKG_NAME
2187 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2188 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2189 ,X_MSG_COUNT => X_MSG_COUNT
2190 ,X_MSG_DATA => X_MSG_DATA
2191 ,X_RETURN_STATUS => X_RETURN_STATUS);
2192 WHEN OTHERS THEN
2193 Rollback to confirm_receipt_PUB;
2194 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2195 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
2196 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
2197 fnd_msg_pub.add;
2198 fnd_msg_pub.count_and_get
2199 ( p_count => x_msg_count
2200 , p_data => x_msg_data);
2201 x_return_status := fnd_api.g_ret_sts_error;
2202 END confirm_receipt;
2203
2204 Function Clean_Up (p_transaction_temp_id IN NUMBER)
2205 Return VARCHAR2
2206 IS
2207 l_api_version_number CONSTANT NUMBER := 1.0;
2208 l_api_name CONSTANT VARCHAR2(50) := 'Clean_Up';
2209 l_msg_count NUMBER := 0;
2210 l_msg_data VARCHAR2(300);
2211 l_check_existence NUMBER := 0;
2212 l_return_status VARCHAR2(1);
2213 l_mtlt_tbl csp_pp_util.g_mtlt_tbl_type;
2214 l_index NUMBER := 1;
2215 l_temp_id_to_be_processed NUMBER := p_transaction_temp_id;
2216 l_csp_mtltxn_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
2217 CURSOR l_ml_records IS
2218 SELECT
2219 TRANSACTION_HEADER_ID ,
2220 TRANSACTION_TEMP_ID ,
2221 SOURCE_CODE ,
2222 SOURCE_LINE_ID ,
2223 TRANSACTION_MODE ,
2224 LOCK_FLAG ,
2225 LAST_UPDATE_DATE ,
2226 LAST_UPDATED_BY ,
2227 CREATION_DATE ,
2228 CREATED_BY ,
2229 LAST_UPDATE_LOGIN ,
2230 REQUEST_ID ,
2231 PROGRAM_APPLICATION_ID ,
2232 PROGRAM_ID ,
2233 PROGRAM_UPDATE_DATE ,
2234 INVENTORY_ITEM_ID ,
2235 REVISION ,
2236 ORGANIZATION_ID ,
2240 PRIMARY_QUANTITY ,
2237 SUBINVENTORY_CODE ,
2238 LOCATOR_ID ,
2239 TRANSACTION_QUANTITY ,
2241 TRANSACTION_UOM ,
2242 TRANSACTION_COST ,
2243 TRANSACTION_TYPE_ID ,
2244 TRANSACTION_ACTION_ID ,
2245 TRANSACTION_SOURCE_TYPE_ID ,
2246 TRANSACTION_SOURCE_ID ,
2247 TRANSACTION_SOURCE_NAME ,
2248 TRANSACTION_DATE ,
2249 ACCT_PERIOD_ID ,
2250 DISTRIBUTION_ACCOUNT_ID ,
2251 TRANSACTION_REFERENCE ,
2252 REQUISITION_LINE_ID ,
2253 REQUISITION_DISTRIBUTION_ID ,
2254 REASON_ID ,
2255 LOT_NUMBER ,
2256 LOT_EXPIRATION_DATE ,
2257 SERIAL_NUMBER ,
2258 RECEIVING_DOCUMENT ,
2259 DEMAND_ID ,
2260 RCV_TRANSACTION_ID ,
2261 MOVE_TRANSACTION_ID ,
2262 COMPLETION_TRANSACTION_ID ,
2263 WIP_ENTITY_TYPE ,
2264 SCHEDULE_ID ,
2265 REPETITIVE_LINE_ID ,
2266 EMPLOYEE_CODE ,
2267 PRIMARY_SWITCH ,
2268 SCHEDULE_UPDATE_CODE ,
2269 SETUP_TEARDOWN_CODE ,
2270 ITEM_ORDERING ,
2271 NEGATIVE_REQ_FLAG ,
2272 OPERATION_SEQ_NUM ,
2273 PICKING_LINE_ID ,
2274 TRX_SOURCE_LINE_ID ,
2275 TRX_SOURCE_DELIVERY_ID ,
2276 PHYSICAL_ADJUSTMENT_ID ,
2277 CYCLE_COUNT_ID ,
2278 RMA_LINE_ID ,
2279 CUSTOMER_SHIP_ID ,
2280 CURRENCY_CODE ,
2281 CURRENCY_CONVERSION_RATE ,
2282 CURRENCY_CONVERSION_TYPE ,
2283 CURRENCY_CONVERSION_DATE ,
2284 USSGL_TRANSACTION_CODE ,
2285 VENDOR_LOT_NUMBER ,
2286 ENCUMBRANCE_ACCOUNT ,
2287 ENCUMBRANCE_AMOUNT ,
2288 SHIP_TO_LOCATION ,
2289 SHIPMENT_NUMBER ,
2290 TRANSFER_COST ,
2291 TRANSPORTATION_COST ,
2292 TRANSPORTATION_ACCOUNT ,
2293 FREIGHT_CODE ,
2294 CONTAINERS ,
2295 WAYBILL_AIRBILL ,
2296 EXPECTED_ARRIVAL_DATE ,
2297 TRANSFER_SUBINVENTORY ,
2298 TRANSFER_ORGANIZATION ,
2299 TRANSFER_TO_LOCATION ,
2300 NEW_AVERAGE_COST ,
2301 VALUE_CHANGE ,
2302 PERCENTAGE_CHANGE ,
2303 MATERIAL_ALLOCATION_TEMP_ID ,
2304 DEMAND_SOURCE_HEADER_ID ,
2305 DEMAND_SOURCE_LINE ,
2306 DEMAND_SOURCE_DELIVERY ,
2307 ITEM_SEGMENTS ,
2308 ITEM_DESCRIPTION ,
2309 ITEM_TRX_ENABLED_FLAG ,
2310 ITEM_LOCATION_CONTROL_CODE ,
2311 ITEM_RESTRICT_SUBINV_CODE ,
2312 ITEM_RESTRICT_LOCATORS_CODE ,
2313 ITEM_REVISION_QTY_CONTROL_CODE ,
2314 ITEM_PRIMARY_UOM_CODE ,
2315 ITEM_UOM_CLASS ,
2316 ITEM_SHELF_LIFE_CODE ,
2317 ITEM_SHELF_LIFE_DAYS ,
2318 ITEM_LOT_CONTROL_CODE ,
2319 ITEM_SERIAL_CONTROL_CODE ,
2320 ITEM_INVENTORY_ASSET_FLAG ,
2321 ALLOWED_UNITS_LOOKUP_CODE ,
2322 DEPARTMENT_ID ,
2323 DEPARTMENT_CODE ,
2324 WIP_SUPPLY_TYPE ,
2325 SUPPLY_SUBINVENTORY ,
2326 SUPPLY_LOCATOR_ID ,
2327 VALID_SUBINVENTORY_FLAG ,
2328 VALID_LOCATOR_FLAG ,
2329 LOCATOR_SEGMENTS ,
2330 CURRENT_LOCATOR_CONTROL_CODE ,
2331 NUMBER_OF_LOTS_ENTERED ,
2332 WIP_COMMIT_FLAG ,
2333 NEXT_LOT_NUMBER ,
2334 LOT_ALPHA_PREFIX ,
2335 NEXT_SERIAL_NUMBER ,
2336 SERIAL_ALPHA_PREFIX ,
2337 SHIPPABLE_FLAG ,
2338 POSTING_FLAG ,
2339 REQUIRED_FLAG ,
2340 PROCESS_FLAG ,
2341 ERROR_CODE ,
2342 ERROR_EXPLANATION ,
2343 ATTRIBUTE_CATEGORY ,
2344 ATTRIBUTE1 ,
2345 ATTRIBUTE2 ,
2346 ATTRIBUTE3 ,
2347 ATTRIBUTE4 ,
2348 ATTRIBUTE5 ,
2349 ATTRIBUTE6 ,
2350 ATTRIBUTE7 ,
2351 ATTRIBUTE8 ,
2352 ATTRIBUTE9 ,
2353 ATTRIBUTE10 ,
2354 ATTRIBUTE11 ,
2355 ATTRIBUTE12 ,
2356 ATTRIBUTE13 ,
2360 RESERVATION_QUANTITY ,
2357 ATTRIBUTE14 ,
2358 ATTRIBUTE15 ,
2359 MOVEMENT_ID ,
2361 SHIPPED_QUANTITY ,
2362 TRANSACTION_LINE_NUMBER ,
2363 TASK_ID ,
2364 TO_TASK_ID ,
2365 SOURCE_TASK_ID ,
2366 PROJECT_ID ,
2367 SOURCE_PROJECT_ID ,
2368 PA_EXPENDITURE_ORG_ID ,
2369 TO_PROJECT_ID ,
2370 EXPENDITURE_TYPE ,
2371 FINAL_COMPLETION_FLAG ,
2372 TRANSFER_PERCENTAGE ,
2373 TRANSACTION_SEQUENCE_ID ,
2374 MATERIAL_ACCOUNT ,
2375 MATERIAL_OVERHEAD_ACCOUNT ,
2376 RESOURCE_ACCOUNT ,
2377 OUTSIDE_PROCESSING_ACCOUNT ,
2378 OVERHEAD_ACCOUNT ,
2379 FLOW_SCHEDULE ,
2380 COST_GROUP_ID ,
2381 DEMAND_CLASS ,
2382 QA_COLLECTION_ID ,
2383 KANBAN_CARD_ID ,
2384 OVERCOMPLETION_TRANSACTION_ID ,
2385 OVERCOMPLETION_PRIMARY_QTY ,
2386 OVERCOMPLETION_TRANSACTION_QTY ,
2387 --PROCESS_TYPE , --removed 01/13/00. process_type does not exist in the mmtt table.
2388 END_ITEM_UNIT_NUMBER ,
2389 SCHEDULED_PAYBACK_DATE ,
2390 LINE_TYPE_CODE ,
2391 PARENT_TRANSACTION_TEMP_ID ,
2392 PUT_AWAY_STRATEGY_ID ,
2393 PUT_AWAY_RULE_ID ,
2394 PICK_STRATEGY_ID ,
2395 PICK_RULE_ID ,
2396 COMMON_BOM_SEQ_ID ,
2397 COMMON_ROUTING_SEQ_ID ,
2398 COST_TYPE_ID ,
2399 ORG_COST_GROUP_ID ,
2400 MOVE_ORDER_LINE_ID ,
2401 TASK_GROUP_ID ,
2402 PICK_SLIP_NUMBER ,
2403 RESERVATION_ID ,
2404 TRANSACTION_STATUS ,
2405 STANDARD_OPERATION_ID ,
2406 TASK_PRIORITY ,
2407 -- ADDED by phegde 02/23
2408 WMS_TASK_TYPE ,
2409 PARENT_LINE_ID
2410 -- SOURCE_LOT_NUMBER
2411 FROM mtl_material_transactions_temp
2412 WHERE transaction_temp_id = p_transaction_temp_id;
2413 BEGIN
2414 OPEN l_ml_records;
2415 FETCH l_ml_records INTO l_csp_mtltxn_rec;
2416 IF l_ml_records%NOTFOUND THEN
2417 fnd_message.set_name ('CSP', 'CSP_NO_MO_TXN_RECORD');
2418 fnd_msg_pub.add;
2419 CLOSE l_ml_records;
2420 RAISE FND_API.G_EXC_ERROR;
2421 END IF;
2422 CLOSE l_ml_records;
2423 -- case 1: if lot control , delete the record in mmtt table and mtlt table.
2424 If nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) <> 1 And
2425 nvl(l_csp_mtltxn_rec.item_serial_control_code, 1) in (1, 6) Then
2426
2427 delete from mtl_transaction_lots_temp
2428 where transaction_temp_id = l_temp_id_to_be_processed;
2429
2430 delete from mtl_material_transactions_temp
2431 where transaction_temp_id = l_temp_id_to_be_processed;
2432 -- case 2: if lot control and serial control, delete the record in the mmtt table, the mtlt table and the msnt table
2433 Elsif nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) <> 1 And
2434 nvl(l_csp_mtltxn_rec.item_serial_control_code, 1) in (2, 5) Then
2435 Declare
2436 Cursor l_Get_Serial_Lot_id_Csr Is
2437 select serial_transaction_temp_id from mtl_transaction_lots_temp
2438 where transaction_temp_id = l_temp_id_to_be_processed;
2439 l_serial_temp_id_del NUMBER;
2440 Begin
2441 Open l_Get_Serial_Lot_id_Csr;
2442 Loop
2443 Fetch l_Get_Serial_Lot_id_Csr Into l_Serial_Temp_Id_Del;
2444 Exit When l_Get_Serial_Lot_id_Csr%NotFound;
2445
2446 delete from mtl_serial_numbers_temp
2447 where transaction_temp_id = l_Serial_Temp_Id_Del;
2448 End Loop;
2449 If l_Get_Serial_Lot_id_Csr%ROWCOUNT = 0 Then
2450 FND_MESSAGE.SET_NAME('CSP', 'CSP_RECEIPT_SERIAL_LOT_FAILURE');
2451 FND_MSG_PUB.ADD;
2452 Close l_Get_Serial_Lot_id_Csr;
2453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2454 End If;
2455
2456 Close l_Get_Serial_Lot_id_Csr;
2457 End;
2458
2459 delete from mtl_transaction_lots_temp
2460 where transaction_temp_id = l_temp_id_to_be_processed;
2461
2462 delete from mtl_material_transactions_temp
2463 where transaction_temp_id = l_temp_id_to_be_processed;
2464 -- case 3: if serial control, delete the record in the mmtt table and the msnt table
2465 Elsif nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) = 1 And
2466 nvl(l_csp_mtltxn_rec.item_serial_control_code, 1) in (2,5) Then
2467
2471 delete from mtl_material_transactions_temp
2468 delete from mtl_serial_numbers_temp
2469 where transaction_temp_id = l_temp_id_to_be_processed;
2470
2472 where transaction_temp_id = l_temp_id_to_be_processed;
2473 Else
2474 -- case 4: neither serial control nor lot control, delete the record in the mmtt table
2475 delete from mtl_material_transactions_temp
2476 where transaction_temp_id = l_temp_id_to_be_processed;
2477 End If;
2478
2479 Return fnd_api.g_true;
2480 EXCEPTION
2481 WHEN OTHERS THEN
2482 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2483 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
2484 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
2485 fnd_msg_pub.add;
2486 Return fnd_api.g_false;
2487 END Clean_Up;
2488
2489 Function Call_Online (p_transaction_header_id NUMBER)
2490 Return Boolean
2491 Is
2492 l_timeout NUMBER := null;
2493 l_outcome BOOLEAN := TRUE;
2494 l_error_code VARCHAR2(200);
2495 l_error_explanation VARCHAR2(240);
2496 Type l_Interface_Type IS Record (
2497 transaction_source_id NUMBER,
2498 trx_source_line_id NUMBER,
2499 organization_id NUMBER );
2500 l_Interface_Rec l_Interface_Type;
2501 CURSOR l_Get_Interface_Csr IS
2502 SELECT transaction_source_id, trx_source_line_id, organization_id
2503 FROM mtl_transactions_interface
2504 WHERE transaction_header_id = p_transaction_header_id;
2505 Begin
2506 -- Commit the changes before calling mtl_onlien_transaction_pub.process_online
2507 commit;
2508
2509 OPEN l_Get_Interface_Csr;
2510
2511 -- Note: Auto commit will be performed in the call of the mtl_online_transaction_pub.process_online.
2512 -- If the process_online failed, there was no way to rollback what was previously transacted.
2513 l_outcome := mtl_online_transaction_pub.process_online
2514 ( p_transaction_header_id => p_transaction_header_id
2515 , p_timeout => l_timeout
2516 , p_error_code => l_error_code
2517 , p_error_explanation => l_error_explanation
2518 );
2519
2520 If (l_outcome = FALSE) Then
2521 FND_MESSAGE.SET_NAME('CSP', 'CSP_TRANSACT_ERRORS');
2522 --FND_MESSAGE.SET_TOKEN('ERROR_CODE', l_error_code, FALSE);
2523 -- FND_MESSAGE.SET_TOKEN('ERROR_EXPLANATION', l_error_explanation, FALSE);
2524 FND_MESSAGE.SET_TOKEN('TRANSACTION_HEADER_ID', to_char(p_transaction_header_id), FALSE);
2525 FND_MSG_PUB.ADD;
2526 CLOSE l_Get_Interface_Csr;
2527 Return l_outcome;
2528 Else
2529 Begin
2530 LOOP
2531 FETCH l_Get_Interface_Csr INTO l_Interface_Rec;
2532 EXIT WHEN l_Get_Interface_Csr%NOTFOUND;
2533 -- Update the move_order_line_id in the mtl_material_transactions so that the transaction record
2534 -- has track-back history to the move order.
2535 -- We need to do this post transaction update because there is no such column move_order_line_id in the
2536 -- mtl_transactions_interface table.
2537 update mtl_material_transactions
2538 set move_order_line_id = l_Interface_Rec.trx_source_line_id
2539 where transaction_source_id = l_Interface_Rec.transaction_source_id
2540 and trx_source_line_id = l_Interface_Rec.trx_source_line_id
2541 and organization_id = l_Interface_Rec.organization_id;
2542 END LOOP;
2543 IF l_Get_Interface_Csr%ISOPEN THEN
2544 CLOSE l_Get_Interface_Csr;
2545 END IF;
2546 End; -- If there is an error, it must be SQL%NOTFOUND. This will be caught by the OTHERS exception of the main block.
2547 End If;
2548 Return true;
2549 EXCEPTION
2550 WHEN OTHERS THEN
2551 IF l_Get_Interface_Csr%ISOPEN THEN
2552 CLOSE l_Get_Interface_Csr;
2553 END IF;
2554 Return false;
2555 End Call_Online;
2556
2557 Procedure Under_Over_Receipt (
2558 -- Start of Comments
2559 -- Procedure name : Under_Over_Receipt
2560 -- Purpose : This procedure handles the under or over receipt.
2561 -- Login : In the case of under receipt, this procedure updates the transfer_subinventory and
2562 -- transfer_to_location of the temp record to NULL, and the transaction_action_type to misc. issue.
2563 -- It then moves the data from the temp table to the interface table. It returns the transaction_header_id.
2564 -- In the case of over receipt, this procedure updates the subinventory_code and locator_id
2565 -- to NULL, and the transaction_action_type to misc. receipt. It then moves the data from the
2566 -- temp table to the interface table. It returns the transaction_header_id.
2567 -- This procedure also cleans the record in the temp table.
2568 -- This procedure sets the px_transaction_header_id to NULL when it fails.
2569 --
2570 -- History :
2571 -- Person Date Descriptions
2572 -- ------ ---- --------------
2573 -- klou 13-Apr-2000 created.
2574 -- End of Comments
2578 px_transaction_header_id IN OUT NOCOPY NUMBER,
2575
2576 p_transaction_temp_id IN NUMBER,
2577 p_receiving_option IN NUMBER,
2579 p_discrepancy_qty IN NUMBER := 0,
2580 X_Return_Status OUT NOCOPY VARCHAR2,
2581 X_Msg_Count OUT NOCOPY NUMBER,
2582 X_Msg_Data OUT NOCOPY VARCHAR2)
2583
2584 IS
2585 l_api_version_number CONSTANT NUMBER := 1.0;
2586 l_api_name CONSTANT VARCHAR2(50) := 'Under_Over_Receipt';
2587 l_msg_data VARCHAR2(300);
2588 l_commit VARCHAR2(1) := fnd_api.g_false;
2589 l_check_existence NUMBER := 0;
2590 l_return_status VARCHAR2(1);
2591 l_msg_count NUMBER := 0;
2592 l_mtlt_tbl csp_pp_util.g_mtlt_tbl_type;
2593 l_index NUMBER := 1;
2594 l_transaction_header_id NUMBER := NULL;
2595 l_account_id NUMBER := NULL;
2596 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
2597 --l_trolin_rec INV_Move_Order_PUB.Trolin_Rec_Type;
2598 l_csp_mtltxn_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
2599 EXCP_USER_DEFINED EXCEPTION;
2600 Cursor l_Get_txn_header_id_csr IS
2601 SELECT mtl_material_transactions_s.nextval
2602 FROM dual;
2603
2604 CURSOR l_ml_records IS
2605 SELECT
2606 TRANSACTION_HEADER_ID ,
2607 TRANSACTION_TEMP_ID ,
2608 SOURCE_CODE ,
2609 SOURCE_LINE_ID ,
2610 TRANSACTION_MODE ,
2611 LOCK_FLAG ,
2612 LAST_UPDATE_DATE ,
2613 LAST_UPDATED_BY ,
2614 CREATION_DATE ,
2615 CREATED_BY ,
2616 LAST_UPDATE_LOGIN ,
2617 REQUEST_ID ,
2618 PROGRAM_APPLICATION_ID ,
2619 PROGRAM_ID ,
2620 PROGRAM_UPDATE_DATE ,
2621 INVENTORY_ITEM_ID ,
2622 REVISION ,
2623 ORGANIZATION_ID ,
2624 SUBINVENTORY_CODE ,
2625 LOCATOR_ID ,
2626 TRANSACTION_QUANTITY ,
2627 PRIMARY_QUANTITY ,
2628 TRANSACTION_UOM ,
2629 TRANSACTION_COST ,
2630 TRANSACTION_TYPE_ID ,
2631 TRANSACTION_ACTION_ID ,
2632 TRANSACTION_SOURCE_TYPE_ID ,
2633 TRANSACTION_SOURCE_ID ,
2634 TRANSACTION_SOURCE_NAME ,
2635 TRANSACTION_DATE ,
2636 ACCT_PERIOD_ID ,
2637 DISTRIBUTION_ACCOUNT_ID ,
2638 TRANSACTION_REFERENCE ,
2639 REQUISITION_LINE_ID ,
2640 REQUISITION_DISTRIBUTION_ID ,
2641 REASON_ID ,
2642 LOT_NUMBER ,
2643 LOT_EXPIRATION_DATE ,
2644 SERIAL_NUMBER ,
2645 RECEIVING_DOCUMENT ,
2646 DEMAND_ID ,
2647 RCV_TRANSACTION_ID ,
2648 MOVE_TRANSACTION_ID ,
2649 COMPLETION_TRANSACTION_ID ,
2650 WIP_ENTITY_TYPE ,
2651 SCHEDULE_ID ,
2652 REPETITIVE_LINE_ID ,
2653 EMPLOYEE_CODE ,
2654 PRIMARY_SWITCH ,
2655 SCHEDULE_UPDATE_CODE ,
2656 SETUP_TEARDOWN_CODE ,
2657 ITEM_ORDERING ,
2658 NEGATIVE_REQ_FLAG ,
2659 OPERATION_SEQ_NUM ,
2660 PICKING_LINE_ID ,
2661 TRX_SOURCE_LINE_ID ,
2662 TRX_SOURCE_DELIVERY_ID ,
2663 PHYSICAL_ADJUSTMENT_ID ,
2664 CYCLE_COUNT_ID ,
2665 RMA_LINE_ID ,
2666 CUSTOMER_SHIP_ID ,
2667 CURRENCY_CODE ,
2668 CURRENCY_CONVERSION_RATE ,
2669 CURRENCY_CONVERSION_TYPE ,
2670 CURRENCY_CONVERSION_DATE ,
2671 USSGL_TRANSACTION_CODE ,
2672 VENDOR_LOT_NUMBER ,
2673 ENCUMBRANCE_ACCOUNT ,
2674 ENCUMBRANCE_AMOUNT ,
2675 SHIP_TO_LOCATION ,
2676 SHIPMENT_NUMBER ,
2677 TRANSFER_COST ,
2678 TRANSPORTATION_COST ,
2679 TRANSPORTATION_ACCOUNT ,
2680 FREIGHT_CODE ,
2681 CONTAINERS ,
2682 WAYBILL_AIRBILL ,
2683 EXPECTED_ARRIVAL_DATE ,
2684 TRANSFER_SUBINVENTORY ,
2685 TRANSFER_ORGANIZATION ,
2686 TRANSFER_TO_LOCATION ,
2687 NEW_AVERAGE_COST ,
2688 VALUE_CHANGE ,
2689 PERCENTAGE_CHANGE ,
2693 DEMAND_SOURCE_DELIVERY ,
2690 MATERIAL_ALLOCATION_TEMP_ID ,
2691 DEMAND_SOURCE_HEADER_ID ,
2692 DEMAND_SOURCE_LINE ,
2694 ITEM_SEGMENTS ,
2695 ITEM_DESCRIPTION ,
2696 ITEM_TRX_ENABLED_FLAG ,
2697 ITEM_LOCATION_CONTROL_CODE ,
2698 ITEM_RESTRICT_SUBINV_CODE ,
2699 ITEM_RESTRICT_LOCATORS_CODE ,
2700 ITEM_REVISION_QTY_CONTROL_CODE ,
2701 ITEM_PRIMARY_UOM_CODE ,
2702 ITEM_UOM_CLASS ,
2703 ITEM_SHELF_LIFE_CODE ,
2704 ITEM_SHELF_LIFE_DAYS ,
2705 ITEM_LOT_CONTROL_CODE ,
2706 ITEM_SERIAL_CONTROL_CODE ,
2707 ITEM_INVENTORY_ASSET_FLAG ,
2708 ALLOWED_UNITS_LOOKUP_CODE ,
2709 DEPARTMENT_ID ,
2710 DEPARTMENT_CODE ,
2711 WIP_SUPPLY_TYPE ,
2712 SUPPLY_SUBINVENTORY ,
2713 SUPPLY_LOCATOR_ID ,
2714 VALID_SUBINVENTORY_FLAG ,
2715 VALID_LOCATOR_FLAG ,
2716 LOCATOR_SEGMENTS ,
2717 CURRENT_LOCATOR_CONTROL_CODE ,
2718 NUMBER_OF_LOTS_ENTERED ,
2719 WIP_COMMIT_FLAG ,
2720 NEXT_LOT_NUMBER ,
2721 LOT_ALPHA_PREFIX ,
2722 NEXT_SERIAL_NUMBER ,
2723 SERIAL_ALPHA_PREFIX ,
2724 SHIPPABLE_FLAG ,
2725 POSTING_FLAG ,
2726 REQUIRED_FLAG ,
2727 PROCESS_FLAG ,
2728 ERROR_CODE ,
2729 ERROR_EXPLANATION ,
2730 ATTRIBUTE_CATEGORY ,
2731 ATTRIBUTE1 ,
2732 ATTRIBUTE2 ,
2733 ATTRIBUTE3 ,
2734 ATTRIBUTE4 ,
2735 ATTRIBUTE5 ,
2736 ATTRIBUTE6 ,
2737 ATTRIBUTE7 ,
2738 ATTRIBUTE8 ,
2739 ATTRIBUTE9 ,
2740 ATTRIBUTE10 ,
2741 ATTRIBUTE11 ,
2742 ATTRIBUTE12 ,
2743 ATTRIBUTE13 ,
2744 ATTRIBUTE14 ,
2745 ATTRIBUTE15 ,
2746 MOVEMENT_ID ,
2747 RESERVATION_QUANTITY ,
2748 SHIPPED_QUANTITY ,
2749 TRANSACTION_LINE_NUMBER ,
2750 TASK_ID ,
2751 TO_TASK_ID ,
2752 SOURCE_TASK_ID ,
2753 PROJECT_ID ,
2754 SOURCE_PROJECT_ID ,
2755 PA_EXPENDITURE_ORG_ID ,
2756 TO_PROJECT_ID ,
2757 EXPENDITURE_TYPE ,
2758 FINAL_COMPLETION_FLAG ,
2759 TRANSFER_PERCENTAGE ,
2760 TRANSACTION_SEQUENCE_ID ,
2761 MATERIAL_ACCOUNT ,
2762 MATERIAL_OVERHEAD_ACCOUNT ,
2763 RESOURCE_ACCOUNT ,
2764 OUTSIDE_PROCESSING_ACCOUNT ,
2765 OVERHEAD_ACCOUNT ,
2766 FLOW_SCHEDULE ,
2767 COST_GROUP_ID ,
2768 DEMAND_CLASS ,
2769 QA_COLLECTION_ID ,
2770 KANBAN_CARD_ID ,
2771 OVERCOMPLETION_TRANSACTION_ID ,
2772 OVERCOMPLETION_PRIMARY_QTY ,
2773 OVERCOMPLETION_TRANSACTION_QTY ,
2774 -- PROCESS_TYPE , --removed 01/13/00. process_type does not exist in the mmtt table.
2775 END_ITEM_UNIT_NUMBER ,
2776 SCHEDULED_PAYBACK_DATE ,
2777 LINE_TYPE_CODE ,
2778 PARENT_TRANSACTION_TEMP_ID ,
2779 PUT_AWAY_STRATEGY_ID ,
2780 PUT_AWAY_RULE_ID ,
2781 PICK_STRATEGY_ID ,
2782 PICK_RULE_ID ,
2783 COMMON_BOM_SEQ_ID ,
2784 COMMON_ROUTING_SEQ_ID ,
2785 COST_TYPE_ID ,
2786 ORG_COST_GROUP_ID ,
2787 MOVE_ORDER_LINE_ID ,
2788 TASK_GROUP_ID ,
2789 PICK_SLIP_NUMBER ,
2790 RESERVATION_ID ,
2791 TRANSACTION_STATUS ,
2792 STANDARD_OPERATION_ID ,
2793 TASK_PRIORITY ,
2794 -- ADDED by phegde 02/23
2795 WMS_TASK_TYPE ,
2796 PARENT_LINE_ID
2797 -- SOURCE_LOT_NUMBER
2798 FROM mtl_material_transactions_temp
2799 WHERE transaction_temp_id = p_transaction_temp_id;
2800
2801 CURSOR l_Get_Mtlt IS
2802 SELECT * FROM mtl_transaction_lots_temp
2803 WHERE transaction_temp_id = p_transaction_temp_id
2804 ORDER BY TRANSACTION_QUANTITY DESC;
2805 BEGIN
2806 IF p_receiving_option NOT IN (1, 2, 3) THEN
2807 fnd_message.set_name('CSP', 'CSP_INVALID_OVER_RECEIPT_QTY');
2808 fnd_msg_pub.add;
2809 RAISE EXCP_USER_DEFINED;
2810 END IF;
2811
2812 OPEN l_ml_records;
2816 fnd_msg_pub.add;
2813 FETCH l_ml_records INTO l_csp_mtltxn_rec;
2814 IF l_ml_records%NOTFOUND THEN
2815 fnd_message.set_name ('CSP', 'CSP_NO_MO_TXN_RECORD');
2817 CLOSE l_ml_records;
2818 RAISE FND_API.G_EXC_ERROR;
2819 END IF;
2820 CLOSE l_ml_records;
2821
2822 l_csp_mtltxn_rec.distribution_account_id := Get_CSP_Acccount_ID (l_csp_mtltxn_rec.organization_id);
2823
2824 IF l_csp_mtltxn_rec.distribution_account_id IS NULL THEN
2825 fnd_message.set_name('CSP', 'CSP_RECEIPT_ACCOUNT_NOT_FOUND');
2826 fnd_msg_pub.add;
2827 RAISE FND_API.G_EXC_ERROR;
2828 END IF;
2829
2830 IF p_receiving_option = 1 THEN -- receiving short
2831 l_csp_mtltxn_rec.transfer_subinventory := NULL;
2832 l_csp_mtltxn_rec.transfer_to_location := NULL;
2833 l_csp_mtltxn_rec.transaction_type_id := 32; -- misc. issue
2834 l_csp_mtltxn_rec.transaction_action_id := 1; -- issue from stores
2835 l_csp_mtltxn_rec.transaction_source_type_id := 13; -- inventory
2836 ELSE -- must be over receiving
2837 /* l_csp_mtltxn_rec.subinventory_code := NULL; --
2838 l_csp_mtltxn_rec.locator_id := NULL; */
2839 l_csp_mtltxn_rec.transfer_subinventory := NULL;
2840 l_csp_mtltxn_rec.transfer_to_location := NULL;
2841 l_csp_mtltxn_rec.transaction_type_id := 42; -- misc. receipt
2842 l_csp_mtltxn_rec.transaction_action_id := 27; -- receipt into stores
2843 l_csp_mtltxn_rec.transaction_source_type_id := 13; -- inventory
2844 END IF;
2845 /* 06/08/2000 klou: do not need to update the quantity delivered because this will mess up the move in quantity
2846 in the move order status form.
2847 -- Update the quantity_delivered to reflect the over-received quantity.
2848 l_trolin_rec := INV_Trolin_util.Query_Row(l_csp_mtltxn_rec.move_order_line_id );
2849 l_trolin_rec.quantity_delivered := nvl(l_trolin_rec.quantity_delivered,0) + p_discrepancy_qty;
2850 l_trolin_rec.last_update_date := SYSDATE;
2851 l_trolin_rec.last_updated_by := FND_GLOBAL.USER_ID;
2852 l_trolin_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
2853 INV_Trolin_Util.Update_Row(l_trolin_rec);
2854 */
2855
2856 CSP_Material_Transactions_PVT.Update_material_transactions(
2857 P_Api_Version_Number => l_api_version_number,
2858 P_Init_Msg_List => FND_API.G_TRUE,
2859 P_Commit => FND_API.G_FALSE,
2860 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2861 P_CSP_Rec => l_csp_mtltxn_rec,
2862 X_Return_Status => l_return_status ,
2863 X_Msg_Count => l_msg_count,
2864 X_Msg_Data => l_msg_data);
2865
2866 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2867 RAISE FND_API.G_EXC_ERROR;
2868 END IF;
2869
2870 -- get the transaction header id
2871 Open l_Get_txn_header_id_csr;
2872 Fetch l_Get_txn_header_id_csr Into l_transaction_header_id;
2873 Close l_Get_txn_header_id_csr;
2874
2875 -- Call the csp_transactions_pub.transact_temp_record
2876 csp_transactions_pub.transact_temp_record (
2877 P_Api_Version_Number => l_api_version_number,
2878 P_Init_Msg_List => FND_API.G_true,
2879 P_Commit => l_commit,
2880 p_validation_level => l_validation_level,
2881 p_transaction_temp_id => p_transaction_temp_id,
2882 px_transaction_header_id => l_transaction_header_id,
2883 p_online_process_flag => FALSE,
2884 X_Return_Status => l_return_status,
2885 X_Msg_Count => l_msg_count,
2886 X_Msg_Data => l_msg_data );
2887
2888 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2890 END IF;
2891
2892 If NOT fnd_api.to_boolean(clean_up(p_transaction_temp_id)) THEN
2893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2894 END IF;
2895 px_transaction_header_id := l_transaction_header_id;
2896 EXCEPTION
2897 WHEN OTHERS THEN
2898 x_return_status := FND_API.G_RET_STS_ERROR;
2899 px_transaction_header_id := NULL;
2900 END Under_Over_Receipt;
2901
2902
2903 Function Get_CSP_Acccount_ID (p_organization_id NUMBER)
2904 Return NUMBER
2905 Is
2906 l_account_id NUMBER;
2907 Cursor l_Get_Account_Id_Csr IS
2908 Select distribution_account
2909 From mtl_generic_dispositions
2910 Where upper(segment1) = 'CSP_RECEIPT'
2911 And organization_id = p_organization_id
2912 And trunc(nvl(effective_date, sysdate-1)) <= trunc(sysdate)
2913 And trunc(nvl(disable_date, sysdate+1)) >= trunc(sysdate);
2914 Begin
2915 Open l_Get_Account_Id_Csr;
2916 Fetch l_Get_Account_Id_Csr Into l_account_id;
2917 Close l_Get_Account_Id_Csr;
2918 Return l_account_id;
2919 End Get_CSP_Acccount_ID;
2920
2921
2922 Procedure Transact_Serial_Lots (
2923 -- This procedure was created specifically for use in the CSP confirm receipt transactions.
2924 p_new_transaction_temp_id IN NUMBER,
2925 p_old_transaction_temp_id IN NUMBER,
2926 p_lot_number IN VARCHAR2,
2927 p_serial_number IN VARCHAR2,
2928 p_qty_received IN NUMBER,
2929 X_Return_Status OUT NOCOPY VARCHAR2,
2933 l_mtlt_tbl csp_pp_util.g_mtlt_tbl_type;
2930 X_Msg_Count OUT NOCOPY NUMBER,
2931 X_Msg_Data OUT NOCOPY VARCHAR2)
2932 IS
2934 l_msnt_tbl csp_pp_util.g_msnt_tbl_type;
2935
2936 -- use 1 as the starting index because it is what the core apps API uses.
2937 -- we are not going to update this index becase there is only one record in the
2938 -- msnt that is coresponding to the l_packlist_sl_rec.serial_number.
2939 l_index NUMBER := 1;
2940 l_api_name VARCHAR2(30) := 'Transact_Serial_Lots';
2941 l_return_status VARCHAR2(1);
2942 l_msg_data VARCHAR2(300);
2943 l_msg_count NUMBER;
2944 l_prefix VARCHAR2(30);
2945 l_num VARCHAR2(30);
2946 l_temp_fm_prefix VARCHAR2(30);
2947 l_temp_fm_num VARCHAR2(30);
2948 l_temp_to_prefix VARCHAR2(30);
2949 l_temp_to_num VARCHAR2(30);
2950 l_number_length NUMBER; -- the length of the number section of the serial numbers
2951 l_new_fm_num NUMBER;
2952 l_new_to_num NUMBER;
2953 l_done_flag VARCHAR2(1) := fnd_api.g_true;
2954 l_serial_case NUMBER := NULL; -- 0 = requires to create a new mtlt record.
2955 l_quantity_remained NUMBER := p_qty_received;
2956 l_old_serial_temp_id NUMBER;
2957 l_new_serial_temp_id NUMBER;
2958 EXCP_USER_DEFINED EXCEPTION;
2959
2960 CURSOR l_Get_Mtlt IS
2961 SELECT * FROM mtl_transaction_lots_temp
2962 WHERE transaction_temp_id = p_old_transaction_temp_id
2963 AND lot_number = p_lot_number
2964 ORDER BY TRANSACTION_QUANTITY DESC;
2965 CURSOR l_Get_Serial_Temp_id_Csr IS SELECT MTL_MATERIAL_TRANSACTIONS_S.nextval FROM dual;
2966
2967 --- Subfunction transact_serial
2968 Procedure transact_serial(
2969 -- p_new_transaction_temp_id IN NUMBER,
2970 -- p_old_transaction_temp_id IN NUMBER,
2971 p_temp_id_ref IN NUMBER,
2972 p_new_temp_id IN NUMBER
2973 ) --return boolean
2974 is
2975 CURSOR l_Get_Msnt(l_transaction_temp_id NUMBER) IS
2976 SELECT * FROM mtl_serial_numbers_temp
2977 WHERE transaction_temp_id = l_transaction_temp_id; --p_old_transaction_temp_id;
2978 l_new_temp_id NUMBER := p_new_temp_id;
2979 begin
2980
2981 OPEN l_Get_Msnt(p_temp_id_ref);
2982 LOOP
2983 FETCH l_Get_Msnt INTO l_msnt_tbl(l_index);
2984 EXIT WHEN l_Get_Msnt%NOTFOUND;
2985
2986 -- l_serial_transaction_temp_id := l_msnt_tbl(l_index).transaction_temp_id; -- removed on 03/30/00
2987
2988 -- analyze the serial number range
2989 csp_pp_util.split_prefix_num (
2990 p_serial_number => p_serial_number
2991 ,p_prefix => l_prefix
2992 ,x_num => l_num
2993 );
2994 csp_pp_util.split_prefix_num (
2995 p_serial_number => l_msnt_tbl(l_index).fm_serial_number
2996 ,p_prefix => l_temp_fm_prefix
2997 ,x_num => l_temp_fm_num
2998 );
2999 csp_pp_util.split_prefix_num (
3000 p_serial_number => l_msnt_tbl(l_index).to_serial_number
3001 ,p_prefix => l_temp_to_prefix
3002 ,x_num => l_temp_to_num
3003 );
3004
3005 l_number_length := length(l_num);
3006
3007 IF l_prefix = l_temp_fm_prefix AND l_num IS NOT NULL
3008 AND l_temp_fm_num IS NOT NULL AND l_temp_to_num IS NOT NULL THEN
3009
3010 IF to_number(l_num) > to_number(l_temp_fm_num)
3011 AND to_number(l_num) < to_number(l_temp_to_num) THEN
3012 -- Example case:
3013 -- fm_serial_number = serial00, to_serial_number = serial09
3014 -- p_serial_number = serial04.
3015 -- Split the serial number range into 3 records.
3016 -- The first one for the l_temp_fm_num to the l_new_to_num.
3017 -- The second one for the l_new_to_num to the l_temp_to_num.
3018 -- The last one for the l_packlist_sl_rec.serial_number to be transacted as
3019 -- the new transaction_temp_id.
3020 -- In any cases, we can recycle the l_msnt_tbl table.
3021
3022 -- First msnt record: just need to update the existing record.
3023 l_new_to_num := to_number(l_num) - 1; --- to_number(l_temp_fm_num);
3024 update mtl_serial_numbers_temp
3025 set to_serial_number = l_prefix||lpad(to_char(l_new_to_num),l_number_length, '0'),
3026 last_update_date = sysdate,
3027 serial_prefix = l_new_to_num - to_number(l_temp_fm_num) + 1
3028 where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
3029 and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
3033 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3030 and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
3031
3032 If (SQL%NOTFOUND) then
3034 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3035 fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3036 fnd_msg_pub.add;
3037 CLOSE l_Get_Msnt;
3038 RAISE EXCP_USER_DEFINED;
3039 End If;
3040
3041 -- Second msnt record: create a new record with new serial number range.
3042 l_new_fm_num := to_number(l_num) + 1;
3043 l_msnt_tbl(l_index).fm_serial_number
3044 := l_prefix||lpad(to_char(l_new_fm_num),l_number_length, '0');
3045 l_msnt_tbl(l_index).serial_prefix := to_number(l_temp_to_num) - l_new_fm_num + 1;
3046 l_msnt_tbl(l_index).creation_date := sysdate;
3047 l_msnt_tbl(l_index).last_update_date := sysdate;
3048
3049 -- create a new msnt record based on the above information.
3050 csp_pp_util.insert_msnt(
3051 x_return_status => l_return_status
3052 ,p_msnt_tbl => l_msnt_tbl
3053 ,p_msnt_tbl_size => 1
3054 );
3055
3056 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3057 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3058 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3059 fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3060 fnd_msg_pub.add;
3061 CLOSE l_Get_Msnt;
3062 RAISE EXCP_USER_DEFINED;
3063 END IF;
3064
3065 -- Last record: create a new msnt record having the l_new_transation_temp_id
3066 l_msnt_tbl(l_index).transaction_temp_id := l_new_temp_id;
3067 l_msnt_tbl(l_index).fm_serial_number := p_serial_number;
3068 l_msnt_tbl(l_index).to_serial_number := p_serial_number;
3069 l_msnt_tbl(l_index).serial_prefix := 1;
3070 l_msnt_tbl(l_index).creation_date := sysdate;
3071 l_msnt_tbl(l_index).last_update_date := sysdate;
3072
3073 csp_pp_util.insert_msnt(
3074 x_return_status => l_return_status
3075 ,p_msnt_tbl => l_msnt_tbl
3076 ,p_msnt_tbl_size => 1
3077 );
3078
3079 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3080 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3081 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3082 fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3083 fnd_msg_pub.add;
3084 CLOSE l_Get_Msnt;
3085 RAISE EXCP_USER_DEFINED;
3086 END IF;
3087
3088 l_done_flag := fnd_api.g_true;
3089 l_serial_case := 0;
3090 exit;
3091
3092 ELSIF l_temp_fm_num = l_num AND to_number(l_num) < to_number(l_temp_to_num) THEN
3093 -- Example case:
3094 -- fm_serial_number = serial00, to_serial_number = serial09
3095 -- p_serial_number = serial00.
3096 -- split the existing msnt record into 2 records.
3097 -- The first record: update the frm_serial_number of the exsiting record to the l_new_fm_num.
3098 -- The second record: create a new msnt record having the l_new_transation_temp_id and the p_serial_number.
3099
3100 -- First record: update the existing record such that the fm_serial_number = p_serial_number + 1.
3101 l_new_fm_num := to_number(l_num) + 1;
3102 update mtl_serial_numbers_temp
3103 set fm_serial_number = l_prefix||lpad(to_char(l_new_fm_num),l_number_length, '0'),
3104 last_update_date = sysdate,
3105 serial_prefix = to_number(l_temp_to_num) - l_new_fm_num +1
3106 where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
3107 and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
3108 and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
3109
3110 If (SQL%NOTFOUND) then
3111 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3112 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3113 fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3114 fnd_msg_pub.add;
3118
3115 CLOSE l_Get_Msnt;
3116 RAISE EXCP_USER_DEFINED;
3117 End If;
3119 -- Second record:
3120 l_msnt_tbl(l_index).transaction_temp_id := l_new_temp_id;
3121 l_msnt_tbl(l_index).fm_serial_number := p_serial_number;
3122 l_msnt_tbl(l_index).to_serial_number := p_serial_number;
3123 l_msnt_tbl(l_index).serial_prefix := 1;
3124 l_msnt_tbl(l_index).creation_date := sysdate;
3125 l_msnt_tbl(l_index).last_update_date := sysdate;
3126
3127 csp_pp_util.insert_msnt(
3128 x_return_status => l_return_status
3129 ,p_msnt_tbl => l_msnt_tbl
3130 ,p_msnt_tbl_size => 1
3131 );
3132
3133 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3134 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3135 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3136 fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3137 fnd_msg_pub.add;
3138 CLOSE l_Get_Msnt;
3139 RAISE EXCP_USER_DEFINED;
3140 END IF;
3141
3142 l_done_flag := fnd_api.g_true;
3143 l_serial_case := 0;
3144 exit;
3145
3146 ELSIF l_temp_to_num = l_num AND to_number(l_temp_fm_num) < to_number(l_num) THEN
3147 -- Example case:
3148 -- fm_serial_number = serial00, to_serial_number = serial09
3149 -- p_serial_number = serial09.
3150 -- split the existing msnt record into 2 records.
3151 -- The first record: update the to_serial_number of the existing record to the l_new_to_num.
3152 -- The second record: create a new msnt record having the l_new_transation_temp_id and the p_serial_number.
3153
3154 -- First record
3155 l_new_to_num := to_number(l_num) - 1;
3156 update mtl_serial_numbers_temp
3157 set to_serial_number = l_prefix||lpad(to_char(l_new_to_num),l_number_length, '0'),
3158 last_update_date = sysdate,
3159 serial_prefix = l_new_to_num - to_number(l_temp_fm_num) + 1
3160 where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
3161 and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
3162 and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
3163
3164 If (SQL%NOTFOUND) then
3165 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3166 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3167 fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3168 fnd_msg_pub.add;
3169 CLOSE l_Get_Msnt;
3170 RAISE EXCP_USER_DEFINED;
3171 End If;
3172
3173 -- Second record:
3174 l_msnt_tbl(l_index).transaction_temp_id := l_new_temp_id;
3175 l_msnt_tbl(l_index).fm_serial_number := p_serial_number;
3176 l_msnt_tbl(l_index).to_serial_number := p_serial_number;
3177 l_msnt_tbl(l_index).serial_prefix := 1;
3178 l_msnt_tbl(l_index).creation_date := sysdate;
3179 l_msnt_tbl(l_index).last_update_date := sysdate;
3180
3181 csp_pp_util.insert_msnt(
3182 x_return_status => l_return_status
3183 ,p_msnt_tbl => l_msnt_tbl
3184 ,p_msnt_tbl_size => 1
3185 );
3186
3187 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3188 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3189 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3190 fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3191 fnd_msg_pub.add;
3192 CLOSE l_Get_Msnt;
3193 RAISE EXCP_USER_DEFINED;
3194 END IF;
3195
3196 l_done_flag := fnd_api.g_true;
3197 l_serial_case := 0;
3198 exit;
3199
3200 ELSIF l_num = l_temp_to_num AND l_num = l_temp_fm_num THEN
3201 -- Example case:
3202 -- fm_serial_number = serial00, to_serial_number = serial00
3203 -- p_serial_number = serial00.
3204 -- This is a case which fm_serial_number = to_serial_number.
3205 -- In this case, we just need to update the transaction_temp_id to the l_new_temp_id.
3206 l_done_flag := fnd_api.g_true;
3207 l_serial_case := 1;
3208
3212 and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
3209 update mtl_serial_numbers_temp
3210 set transaction_temp_id = l_new_temp_id
3211 where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
3213 and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
3214
3215 If (SQL%NOTFOUND) then
3216 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3217 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3218 fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3219 fnd_msg_pub.add;
3220 CLOSE l_Get_Msnt;
3221 RAISE EXCP_USER_DEFINED;
3222 End If;
3223
3224 exit;
3225 ELSE
3226 -- Example case:
3227 -- fm_serial_number = serial00, to_serial_number = serial09
3228 -- p_serial_number = serial11. Not in the record being examined, can be in next one.
3229 -- start another loop
3230 l_serial_case := null;
3231 l_done_flag := fnd_api.g_false;
3232 END IF;
3233
3234 ELSIF l_prefix = l_temp_fm_prefix AND l_num IS NULL
3235 AND l_temp_to_num IS NULL AND l_temp_fm_num IS NULL THEN
3236 -- Example case:
3237 -- fm_serial_number = serial, to_serial_number = serial
3238 -- p_serial_number = serial.
3239 -- There is no serial number range in this case.
3240 -- This is a case which fm_serial_number = to_serial_number.
3241 -- In this case, we just need to update the transaction_temp_id to the l_new_temp_id.
3242 l_done_flag := fnd_api.g_true;
3243 l_serial_case := 1;
3244 update mtl_serial_numbers_temp
3245 set transaction_temp_id = l_new_temp_id
3246 where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
3247 and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
3248 and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
3249
3250 If (SQL%NOTFOUND) then
3251 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3252 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3253 fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3254 fnd_msg_pub.add;
3255 CLOSE l_Get_Msnt;
3256 RAISE EXCP_USER_DEFINED;
3257 End If;
3258 exit;
3259 ELSE
3260 -- start another loop
3261 l_serial_case := null;
3262 l_done_flag := fnd_api.g_false;
3263 END IF;
3264 END LOOP;
3265
3266 IF (l_Get_Msnt%rowcount = 0 AND p_serial_number IS NOT NULL) OR
3267 (l_Get_Msnt%rowcount <> 0 AND NOT fnd_api.to_boolean(l_done_flag)) THEN
3268 fnd_message.set_name ('CSP', 'CSP_NO_SERIAL_TXN_RECORD');
3269 fnd_msg_pub.add;
3270 IF l_Get_Msnt%ISOPEN THEN
3271 CLOSE l_Get_Msnt;
3272 END IF;
3273 RAISE EXCP_USER_DEFINED;
3274 END IF;
3275
3276 IF l_Get_Msnt%ISOPEN THEN
3277 CLOSE l_Get_Msnt;
3278 END IF;
3279 End transact_serial;
3280 ---- End transact_serial sub_function
3281
3282 BEGIN
3283 SAVEPOINT Transact_Serial_Lots_PUB;
3284
3285 -- case 1: serial_control = true, lot_control = false
3286 IF p_serial_number IS NOT NULL
3287 AND p_lot_number IS NULL THEN
3288 transact_serial(
3289 p_temp_id_ref => p_old_transaction_temp_id,
3290 p_new_temp_id => p_new_transaction_temp_id);
3291
3292 -- case 2: serial_control = true, lot_control = true
3293 ELSIF p_lot_number IS NOT NULL
3294 AND p_serial_number IS NOT NULL THEN
3295 DECLARE
3296 Cursor l_Get_Mtlt_Lot_No IS
3297 SELECT * FROM mtl_transaction_lots_temp
3298 WHERE transaction_temp_id = p_old_transaction_temp_id
3299 AND lot_number = p_lot_number;
3300 BEGIN
3301
3302 OPEN l_Get_Mtlt_Lot_No;
3303 -- This loop only loops once if everything is correct. Otherwise, exceptions should be thrown.
3304 FETCH l_Get_Mtlt_Lot_No INTO l_mtlt_tbl(l_index);
3305 IF l_Get_Mtlt_Lot_No%rowcount = 0 THEN
3306 fnd_message.set_name ('CSP', 'CSP_NO_LOT_TXN_RECORD');
3307 fnd_msg_pub.add;
3308 CLOSE l_Get_Mtlt_Lot_No;
3309 RAISE EXCP_USER_DEFINED;
3310 END IF;
3311
3312 IF l_Get_Mtlt%ISOPEN THEN
3313 CLOSE l_Get_Mtlt_Lot_No;
3314 END IF;
3315
3316 -- For items also under serial control, we transact one serial number at a time.
3320 IF l_quantity_remained > 0 THEN
3317 -- l_quantity_remained was initialized to p_quantity_received.
3318 l_quantity_remained := l_mtlt_tbl(l_index).transaction_quantity - l_quantity_remained;
3319
3321 -- update the existing transaction_quantity to l_quantity_remained.
3322 -- create a new record.
3323 update mtl_transaction_lots_temp
3324 set transaction_quantity = l_quantity_remained,
3325 primary_quantity = l_quantity_remained
3326 where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
3327 and serial_transaction_temp_id = l_mtlt_tbl(l_index).serial_transaction_temp_id
3328 and lot_number = l_mtlt_tbl(l_index).lot_number;
3329
3330 If (SQL%NOTFOUND) then
3331 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3332 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3333 fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
3334 fnd_msg_pub.add;
3335 CLOSE l_Get_Mtlt;
3336 RAISE EXCP_USER_DEFINED;
3337 End If;
3338
3339 l_old_serial_temp_id := l_mtlt_tbl(l_index).serial_transaction_temp_id;
3340
3341 Open l_Get_Serial_Temp_id_Csr;
3342 Fetch l_Get_Serial_Temp_id_Csr Into l_new_serial_temp_id;
3343 Close l_Get_Serial_Temp_id_Csr;
3344
3345 -- create a new record
3346 l_mtlt_tbl(l_index).transaction_quantity := p_qty_received;
3347 l_mtlt_tbl(l_index).primary_quantity := p_qty_received;
3348 l_mtlt_tbl(l_index).transaction_temp_id := p_new_transaction_temp_id;
3349 l_mtlt_tbl(l_index).serial_transaction_temp_id := l_new_serial_temp_id;
3350
3351 csp_pp_util.insert_mtlt(
3352 x_return_status => l_return_status
3353 ,p_mtlt_tbl => l_mtlt_tbl
3354 ,p_mtlt_tbl_size => 1
3355 );
3356
3357 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3358 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3359 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3360 fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
3361 fnd_msg_pub.add;
3362 CLOSE l_Get_Mtlt;
3363 RAISE EXCP_USER_DEFINED;
3364 ELSE
3365 -- transact the serial number
3366 transact_serial(p_temp_id_ref => l_old_serial_temp_id,
3367 p_new_temp_id => l_new_serial_temp_id);
3368 END IF;
3369
3370 ELSIF l_quantity_remained < 0 THEN
3371 -- For serial controlled items that are also under lot-control, this case should not
3372 -- happen because confirm receipt is transacting one serial number at time.
3373 fnd_message.set_name ('CSP', 'CSP_INVALID_SERIAL_QTY');
3374 fnd_msg_pub.add;
3375 --l_msg_data := 'Unexpected errors.';
3376 CLOSE l_Get_Mtlt;
3377 RAISE EXCP_USER_DEFINED;
3378
3379 ELSE
3380 -- l_quantity_remained = 0, this case should not happen either.
3381 -- The p_quantity_received must be 1 when the item is under serial control.
3382 -- If the serial number in the msnt is the last one to be transacted
3383 -- i.e. when fm_serial_number = to_serial_number, the l_serial_case should be 1.
3384 fnd_message.set_name ('CSP', 'CSP_INVALID_SERIAL_QTY');
3385 fnd_msg_pub.add;
3386 CLOSE l_Get_Mtlt;
3387 RAISE EXCP_USER_DEFINED;
3388
3389 END IF;
3390 END; -- end block
3391 -- case 3: serial_control = false, lot_control = true
3392 ELSIF p_lot_number IS NOT NULL
3393 AND p_serial_number IS NULL THEN
3394
3395 DECLARE
3396 l_qty_received NUMBER := p_qty_received;
3397 BEGIN
3398 OPEN l_Get_Mtlt;
3399 LOOP
3400 FETCH l_Get_Mtlt INTO l_mtlt_tbl(l_index);
3401 EXIT WHEN l_Get_Mtlt%NOTFOUND;
3402 l_quantity_remained := l_mtlt_tbl(l_index).transaction_quantity - l_qty_received;
3403 IF l_quantity_remained > 0 THEN
3404 -- update the existing transaction_quantity to l_quantity_remained.
3405 -- create a new record.
3406 update mtl_transaction_lots_temp
3407 set transaction_quantity = l_quantity_remained,
3408 primary_quantity = l_quantity_remained
3409 where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
3413 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3410 and lot_number = l_mtlt_tbl(l_index).lot_number;
3411
3412 If (SQL%NOTFOUND) then
3414 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3415 fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
3416 fnd_msg_pub.add;
3417 CLOSE l_Get_Mtlt;
3418 RAISE EXCP_USER_DEFINED;
3419 End If;
3420
3421 -- create a new record
3422 l_mtlt_tbl(l_index).transaction_quantity := l_qty_received;
3423 l_mtlt_tbl(l_index).primary_quantity := l_qty_received;
3424 l_mtlt_tbl(l_index).transaction_temp_id := p_new_transaction_temp_id;
3425
3426 csp_pp_util.insert_mtlt(
3427 x_return_status => l_return_status
3428 ,p_mtlt_tbl => l_mtlt_tbl
3429 ,p_mtlt_tbl_size => 1
3430 );
3431
3432 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3433 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3434 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3435 fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
3436 fnd_msg_pub.add;
3437 CLOSE l_Get_Mtlt;
3438 RAISE EXCP_USER_DEFINED;
3439 END IF;
3440 exit; -- exit the loop because the quantity_received is less than the transaction_quantity
3441
3442 ELSIF l_quantity_remained < 0 THEN
3443 -- get ready for the next loop.
3444 -- l_quantity_remained := abs(l_quantity_remained);
3445 l_qty_received := abs(l_quantity_remained);
3446 ELSE -- l_quantity_remained = 0 because the quantity_received = transaction_quantity
3447 -- update the existing record to the p_new_transaction_temp_id
3448 update mtl_transaction_lots_temp
3449 set transaction_temp_id = p_new_transaction_temp_id
3450 where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
3451 and lot_number = l_mtlt_tbl(l_index).lot_number;
3452 If (SQL%NOTFOUND) then
3453 fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3454 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3455 fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
3456 fnd_msg_pub.add;
3457 CLOSE l_Get_Mtlt;
3458 RAISE EXCP_USER_DEFINED;
3459 End If;
3460 END IF;
3461 END LOOP;
3462
3463 IF l_Get_Mtlt%rowcount = 0 THEN
3464 fnd_message.set_name ('CSP', 'CSP_NO_LOT_TXN_RECORD');
3465 fnd_msg_pub.add;
3466 --l_msg_data := 'Could not find any records in the mtl_transaction_lots_temp table.';
3467 CLOSE l_Get_Mtlt;
3468 RAISE EXCP_USER_DEFINED;
3469 END IF;
3470
3471 IF l_Get_Mtlt%ISOPEN THEN
3472 CLOSE l_Get_Mtlt;
3473 END IF;
3474 END;
3475 Else
3476 NULL;
3477 End if;
3478 x_return_status := FND_API.G_RET_STS_SUCCESS;
3479 fnd_msg_pub.count_and_get
3480 ( p_count => x_msg_count
3481 , p_data => x_msg_data);
3482 EXCEPTION
3483 WHEN EXCP_USER_DEFINED THEN
3484 Rollback to Transact_Serial_Lots_PUB;
3485 x_return_status := FND_API.G_RET_STS_ERROR;
3486 fnd_msg_pub.count_and_get
3487 ( p_count => x_msg_count
3488 , p_data => x_msg_data);
3489 WHEN FND_API.G_EXC_ERROR THEN
3490 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3491 P_API_NAME => L_API_NAME
3492 ,P_PKG_NAME => G_PKG_NAME
3493 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3494 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
3495 ,X_MSG_COUNT => X_MSG_COUNT
3496 ,X_MSG_DATA => X_MSG_DATA
3497 ,X_RETURN_STATUS => X_RETURN_STATUS);
3498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3499 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3500 P_API_NAME => L_API_NAME
3501 ,P_PKG_NAME => G_PKG_NAME
3502 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3503 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
3504 ,X_MSG_COUNT => X_MSG_COUNT
3505 ,X_MSG_DATA => X_MSG_DATA
3506 ,X_RETURN_STATUS => X_RETURN_STATUS);
3507 WHEN OTHERS THEN
3508 Rollback to Transact_Serial_Lots_PUB;
3509 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
3510 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3511 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
3512 fnd_msg_pub.add;
3513 fnd_msg_pub.count_and_get
3514 ( p_count => x_msg_count
3515 , p_data => x_msg_data);
3516 x_return_status := fnd_api.g_ret_sts_error;
3517 END Transact_Serial_Lots;
3518
3519 FUNCTION Convert_Temp_UOM (p_csp_mtltxn_rec IN OUT NOCOPY CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type,
3520 p_quantity_convert IN NUMBER)
3521 RETURN VARCHAR2
3522 -- This function calculates the primary quantity of a temp record if the transaction_uom is different
3523 -- from the item_primary_uom. It returns true if the conversion is successful. Otherwise, it
3524 -- insert error messages into the message stack and returns false.
3525 IS
3526 BEGIN
3527 IF p_csp_mtltxn_rec.transaction_uom <> nvl(p_csp_mtltxn_rec.item_primary_uom_code, p_csp_mtltxn_rec.transaction_uom)
3528 THEN
3529 p_csp_mtltxn_rec.primary_quantity := inv_convert.inv_um_convert (
3530 item_id => p_csp_mtltxn_rec.inventory_item_id,
3531 precision => 38,
3532 from_quantity => p_quantity_convert,
3533 from_unit => p_csp_mtltxn_rec.transaction_uom,
3534 to_unit => p_csp_mtltxn_rec.item_primary_uom_code,
3535 from_name => p_csp_mtltxn_rec.transaction_uom,
3536 to_name => p_csp_mtltxn_rec.item_primary_uom_code);
3537 IF p_csp_mtltxn_rec.primary_quantity = -99999 THEN
3538 fnd_message.set_name ('INV', 'INV_INVALID_UOM_CONV');
3539 fnd_message.set_token ('VALUE1', p_csp_mtltxn_rec.transaction_uom, TRUE);
3540 fnd_message.set_token ('VALUE2', p_csp_mtltxn_rec.item_primary_uom_code, TRUE);
3541 fnd_msg_pub.add;
3542 RETURN fnd_api.g_false;
3543 END IF;
3544 END IF;
3545 RETURN fnd_api.g_true;
3546 END Convert_Temp_UOM;
3547
3548 END CSP_MO_MTLTXNS_UTIL;