[Home] [Help]
PACKAGE BODY: APPS.CSP_TRANSACTIONS_PUB
Source
1 PACKAGE BODY CSP_TRANSACTIONS_PUB AS
2 /*$Header: csppttnb.pls 120.6 2008/02/18 06:27:08 htank ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSP_TRANSACTIONS_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(30) := 'csppttnb.pls';
6
7 -- Start of comments
8 --
9 -- Procedure Name : create_move_order_header
10 -- Description : Creates a move order header
11 -- Business Rules :
12 -- Parameters :
13 -- Version : 1.0
14 -- End of comments
15
16 procedure cancel_move_order_header(
17 p_header_id in number,
18 x_return_status OUT NOCOPY varchar2,
19 x_msg_count OUT NOCOPY number,
20 x_msg_data OUT NOCOPY varchar2) is
21
22 begin
23 inv_mo_admin_pub.Close_Order(
24 p_api_version => 1.0,
25 p_init_msg_list => fnd_api.g_false,
26 p_commit => fnd_api.g_false,
27 p_validation_level => fnd_api.g_valid_level_full,
28 p_header_Id => p_header_id,
29 x_msg_count => x_msg_count,
30 x_msg_data => x_msg_data,
31 x_return_status => x_return_status);
32 end cancel_move_order_header;
33
34 procedure cancel_move_order_line(
35 p_line_id in number,
36 x_return_status OUT NOCOPY varchar2,
37 x_msg_count OUT NOCOPY number,
38 x_msg_data OUT NOCOPY varchar2) is
39
40 l_quantity_delivered number;
41
42 cursor c_quantity_delivered is
43 select quantity_delivered
44 from mtl_txn_request_lines
45 where line_id = p_line_id;
46
47 begin
48 open c_quantity_delivered;
49 fetch c_quantity_delivered into l_quantity_delivered;
50 close c_quantity_delivered;
51
52 l_quantity_delivered := nvl(l_quantity_delivered,0);
53
54 if l_quantity_delivered = 0 then
55 inv_mo_admin_pub.cancel_line(
56 p_api_version => 1.0,
57 p_init_msg_list => fnd_api.g_false,
58 p_commit => fnd_api.g_false,
59 p_validation_level => fnd_api.g_valid_level_full,
60 p_line_id => p_line_id,
61 x_msg_count => x_msg_count,
62 x_msg_data => x_msg_data,
63 x_return_status => x_return_status);
64 else
65 inv_mo_admin_pub.close_line(
66 p_api_version => 1.0,
67 p_init_msg_list => fnd_api.g_false,
68 p_commit => fnd_api.g_false,
69 p_validation_level => fnd_api.g_valid_level_full,
70 p_line_id => p_line_id,
71 x_msg_count => x_msg_count,
72 x_msg_data => x_msg_data,
73 x_return_status => x_return_status);
74 end if;
75 end cancel_move_order_line;
76
77 procedure reject_move_order_line(
78 p_line_id in number,
79 x_return_status OUT NOCOPY varchar2,
80 x_msg_count OUT NOCOPY number,
81 x_msg_data OUT NOCOPY varchar2) is
82
83 l_header_id number;
84 l_trolin_tbl inv_move_order_pub.Trolin_Tbl_Type;
85 l_trolin_old_tbl inv_move_order_pub.trolin_tbl_type;
86 x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
87
88 cursor c_header_id is
89 select header_id
90 from mtl_txn_request_lines
91 where line_id = p_line_id;
92
93 begin
94 open c_header_id;
95 fetch c_header_id into l_header_id;
96 close c_header_id;
97
98 l_trolin_tbl(1).line_status := 9;
99 l_trolin_tbl(1).header_id := l_header_id;
100 l_trolin_tbl(1).line_id := p_line_id;
101 l_trolin_tbl(1).operation := inv_globals.g_opr_update;
102
103 inv_move_order_pub.process_move_order_line(
104 p_api_version_number => 1.0
105 , x_return_status => x_return_status
106 , x_msg_count => x_msg_count
107 , x_msg_data => x_msg_data
108 , p_trolin_tbl => l_trolin_tbl
109 , p_trolin_old_tbl => l_trolin_old_tbl
110 , x_trolin_tbl => x_trolin_tbl);
111
112 end reject_move_order_line;
113 PROCEDURE CREATE_MOVE_ORDER_HEADER
114 (px_header_id IN OUT NOCOPY NUMBER
115 ,p_request_number IN VARCHAR2
116 ,p_api_version IN NUMBER
117 ,p_Init_Msg_List IN VARCHAR2
118 ,p_commit IN VARCHAR2
119 ,p_date_required IN DATE
120 ,p_organization_id IN NUMBER
121 ,p_from_subinventory_code IN VARCHAR2
122 ,p_to_subinventory_code IN VARCHAR2
123 ,p_address1 IN VARCHAR2
124 ,p_address2 IN VARCHAR2
125 ,p_address3 IN VARCHAR2
126 ,p_address4 IN VARCHAR2
127 ,p_city IN VARCHAR2
128 ,p_postal_code IN VARCHAR2
129 ,p_state IN VARCHAR2
130 ,p_province IN VARCHAR2
131 ,p_country IN VARCHAR2
132 ,p_freight_carrier IN VARCHAR2
133 ,p_shipment_method IN VARCHAR2
134 ,p_autoreceipt_flag IN VARCHAR2
135 ,x_return_status OUT NOCOPY VARCHAR2
136 ,x_msg_count OUT NOCOPY NUMBER
137 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
138
139 l_mohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
140 l_mohdr_val_rec INV_Move_Order_PUB.Trohdr_Val_Rec_Type;
141 l_header_id number;
142
143 l_api_version_number CONSTANT NUMBER := 1.0;
144 l_api_name CONSTANT VARCHAR2(30) := 'Create_Move_Order_Header';
145 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
146 l_msg_count NUMBER;
147 l_msg_data VARCHAR2(240);
148 l_commit VARCHAR2(1) := FND_API.G_FALSE;
149 EXCP_USER_DEFINED EXCEPTION;
150
151 BEGIN
152 SAVEPOINT Create_Move_Order_Header_PUB;
153
154 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
155 -- initialize message list
156 FND_MSG_PUB.initialize;
157 END IF;
158
159 -- Standard call to check for call compatibility.
160 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
161 p_api_version,
162 l_api_name,
163 G_PKG_NAME)
164 THEN
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 END IF;
167
168 -- initialize return status
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170
171 -- check organization
172 IF p_organization_id IS NULL THEN
173 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
174 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
175 FND_MSG_PUB.ADD;
176 RAISE EXCP_USER_DEFINED;
177 END IF;
178
179 IF (px_header_id IS NOT NULL) THEN
180 BEGIN
181 SELECT header_id
182 INTO l_header_id
183 FROM mtl_txn_request_headers
184 WHERE header_id = px_header_id
185 AND organization_id = p_organization_id;
186
187 FND_MESSAGE.SET_NAME('CSP', 'CSP_PARAMETER_EXISTS');
188 FND_MESSAGE.SET_TOKEN('PARAMETER', 'px_header_id' , TRUE);
189 FND_MSG_PUB.ADD;
190 RAISE EXCP_USER_DEFINED;
191
192 EXCEPTION
193 WHEN no_data_found THEN
194 -- valid id
195 NULL;
196 END;
197 END IF;
198
199 -- initialize move order header record type
200 l_mohdr_rec.header_id := nvl(px_header_id, FND_API.G_MISS_NUM);
201 l_mohdr_rec.request_number := nvl(p_request_number, FND_API.G_MISS_CHAR);
202 l_mohdr_rec.created_by := nvl(fnd_global.user_id,1);
203 l_mohdr_rec.creation_date := sysdate;
204 l_mohdr_rec.date_required := p_date_required;
205 l_mohdr_rec.from_subinventory_code:= p_from_subinventory_code;
206 l_mohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
207 l_mohdr_rec.last_updated_by := nvl(fnd_global.user_id,1);
208 l_mohdr_rec.last_update_date := sysdate;
209 l_mohdr_rec.last_update_login := nvl(fnd_global.login_id,-1);
210 l_mohdr_rec.organization_id := p_organization_id;
211 l_mohdr_rec.status_date := sysdate;
212 l_mohdr_rec.to_subinventory_code := p_to_subinventory_code;
213 l_mohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
214 l_mohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REQUISITION;
215 l_mohdr_rec.db_flag := FND_API.G_TRUE;
216 l_mohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
217
218 -- call public api to create a record for move order header in Oracle Inventory
219 INV_Move_Order_PUB.Create_Move_order_Header(
220 p_api_version_number => 1,
221 p_init_msg_list => p_init_msg_list,
222 p_return_values => FND_API.G_TRUE,
223 p_commit => l_commit,
224 x_return_status => l_return_status,
225 x_msg_count => l_msg_count,
226 x_msg_data => l_msg_data,
227 p_trohdr_rec => l_mohdr_rec,
228 p_trohdr_val_rec => l_mohdr_val_rec,
229 x_trohdr_rec => l_mohdr_rec,
230 x_trohdr_val_rec => l_mohdr_val_rec
231 );
232
233 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
234 RAISE FND_API.G_EXC_ERROR;
235 ELSE
236 /* call table handlers for inserting into csp_move_order_header table*/
237 csp_to_form_moheaders.Validate_and_Write(
238 P_Api_Version_Number => 1.0
239 ,P_Init_Msg_List => p_init_msg_list
240 ,P_Commit => l_commit
241 ,p_validation_level => null
242 ,p_action_code => 0 -- 0 = insert, 1 = update, 2 = delete
243 ,p_header_id => l_mohdr_rec.header_id
244 ,p_created_by => nvl(fnd_global.user_id,1)
245 ,p_creation_date => sysdate
246 ,p_last_updated_by => nvl(fnd_global.user_id,1)
247 ,p_last_update_date => sysdate
248 ,p_last_update_login => nvl(fnd_global.login_id,-1)
249 ,p_carrier => p_freight_carrier
250 ,p_shipment_method => p_shipment_method
251 ,p_autoreceipt_flag => p_autoreceipt_flag
252 ,p_attribute_category => null
253 ,p_attribute1 => null
254 ,p_attribute2 => null
255 ,p_attribute3 => null
256 ,p_attribute4 => null
257 ,p_attribute5 => null
258 ,p_attribute6 => null
259 ,p_attribute7 => null
260 ,p_attribute8 => null
261 ,p_attribute9 => null
262 ,p_attribute10 => null
263 ,p_attribute11 => null
264 ,p_attribute12 => null
265 ,p_attribute13 => null
266 ,p_attribute14 => null
267 ,p_attribute15 => null
268 ,p_location_id => null
269 /*,p_address1 => p_address1
270 ,p_address2 => p_address2
271 ,p_address3 => p_address3
272 ,p_address4 => p_address4
273 ,p_city => p_city
274 ,p_postal_code => p_postal_code
275 ,p_state => p_state
276 ,p_province => p_province
277 ,p_country => p_country */
278 ,X_Return_Status => l_return_status
279 ,X_Msg_Count => l_msg_count
280 ,X_Msg_Data => l_msg_data
281 );
282
283 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
284 RAISE FND_API.G_EXC_ERROR;
285 END IF;
286
287 px_header_id := l_mohdr_rec.header_id;
288 END IF;
289
290 IF fnd_api.to_boolean(p_commit) THEN
291 commit work;
292 END IF;
293
294 fnd_msg_pub.count_and_get
295 ( p_count => x_msg_count
296 , p_data => x_msg_data);
297 /* Exception Block */
298 EXCEPTION
299 WHEN EXCP_USER_DEFINED THEN
300 Rollback to Create_Move_Order_Header_PUB;
301 x_return_status := FND_API.G_RET_STS_ERROR;
302 fnd_msg_pub.count_and_get
303 ( p_count => x_msg_count
304 , p_data => x_msg_data);
305 WHEN FND_API.G_EXC_ERROR THEN
306 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
307 P_API_NAME => L_API_NAME
308 ,P_PKG_NAME => G_PKG_NAME
309 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
310 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
311 ,X_MSG_COUNT => X_MSG_COUNT
312 ,X_MSG_DATA => X_MSG_DATA
313 ,X_RETURN_STATUS => X_RETURN_STATUS);
314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
315 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
316 P_API_NAME => L_API_NAME
317 ,P_PKG_NAME => G_PKG_NAME
318 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
319 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
320 ,X_MSG_COUNT => X_MSG_COUNT
321 ,X_MSG_DATA => X_MSG_DATA
322 ,X_RETURN_STATUS => X_RETURN_STATUS);
323 WHEN OTHERS THEN
324 Rollback to Create_Move_Order_Header_PUB;
325 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
326 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
327 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
328 FND_MSG_PUB.ADD;
329 fnd_msg_pub.count_and_get
330 ( p_count => x_msg_count
331 , p_data => x_msg_data);
332 x_return_status := FND_API.G_RET_STS_ERROR;
333
334 END CREATE_MOVE_ORDER_HEADER;
335
336
337 -- Start of comments
338 --
339 -- Procedure Name : create_move_order_lines
340 -- Descritpion : Creates move order lines
341 -- Business Rules :
342 -- Parameters :
343 -- Version : 1.0
344 -- End of comments
345
346 PROCEDURE CREATE_MOVE_ORDER_LINE
347 (p_api_version IN NUMBER
348 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
349 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
350 ,px_line_id IN OUT NOCOPY NUMBER
351 ,p_header_id IN NUMBER
352 ,p_organization_id IN NUMBER
353 ,p_from_subinventory_code IN VARCHAR2
354 ,p_from_locator_id IN NUMBER
355 ,p_inventory_item_id IN NUMBER
356 ,p_revision IN VARCHAR2
357 ,p_lot_number IN VARCHAR2
358 ,p_serial_number_start IN VARCHAR2
359 ,p_serial_number_end IN VARCHAR2
360 ,p_quantity IN NUMBER
361 ,p_uom_code IN VARCHAR2
362 ,p_quantity_delivered IN NUMBER
363 ,p_to_subinventory_code IN VARCHAR2
364 ,p_to_locator_id IN VARCHAR2
365 ,p_to_organization_id IN NUMBER
366 ,p_service_request IN VARCHAR2
367 ,p_task_id IN NUMBER
368 ,p_task_assignment_id IN NUMBER
369 ,p_customer_po IN VARCHAR2
370 ,p_date_required IN DATE
371 ,p_comments IN VARCHAR2
372 ,x_return_status OUT NOCOPY VARCHAR2
373 ,x_msg_count OUT NOCOPY NUMBER
374 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
375
376 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
377 l_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
378
379 l_api_version_number CONSTANT NUMBER := 1.0;
380 l_api_name CONSTANT VARCHAR2(30) := 'Create_Move_Order_Line';
381 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
382 l_msg_count NUMBER;
383 l_msg_data VARCHAR2(240);
384 l_commit VARCHAR2(1) := FND_API.G_FALSE;
385 EXCP_USER_DEFINED EXCEPTION;
386
387 l_line_num NUMBER := 0;
388 l_line_id NUMBER;
389 l_order_count NUMBER := 1; /* total number of lines */
390
391 BEGIN
392 SAVEPOINT Create_Move_Order_Line_PUB;
393
394 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
395 -- initialize message list
396 FND_MSG_PUB.initialize;
397 END IF;
398
399 -- Standard call to check for call compatibility.
400 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
401 p_api_version,
402 l_api_name,
403 G_PKG_NAME)
404 THEN
405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
406 END IF;
407
408 -- Initialize return_status
409 x_return_status := FND_API.G_RET_STS_SUCCESS;
410
411 -- check organization
412 IF p_organization_id IS NULL THEN
413 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
414 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
415 FND_MSG_PUB.ADD;
416 RAISE EXCP_USER_DEFINED;
417 END IF;
418
419 IF p_header_id IS NULL THEN
420 FND_MESSAGE.SET_NAME('CSP', 'CSP_MISSING_PARAMETERS');
421 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_header_id', TRUE);
422 FND_MSG_PUB.ADD;
423 RAISE EXCP_USER_DEFINED;
424 END IF;
425
426 IF (px_line_id IS NOT NULL) THEN
427 BEGIN
428 SELECT line_id
429 INTO l_line_id
430 FROM mtl_txn_request_lines
431 WHERE line_id = px_line_id
432 AND organization_id = p_organization_id;
433
434 FND_MESSAGE.SET_NAME('CSP', 'CSP_PARAMETER_EXISTS');
435 FND_MESSAGE.SET_TOKEN('PARAMETER', 'px_line_id' , TRUE);
436 FND_MSG_PUB.ADD;
437 RAISE EXCP_USER_DEFINED;
438
439 EXCEPTION
440 WHEN no_data_found THEN
441 -- valid id
442 NULL;
443 END;
444 END IF;
445
446 select nvl(max(line_number), 0)
447 into l_line_num
448 from mtl_txn_request_lines
449 where header_id = p_header_id;
450
451 l_line_num := l_line_num + 1;
452 l_trolin_tbl(l_order_count).header_id := p_header_id;
453 l_trolin_tbl(l_order_count).created_by := nvl(FND_GLOBAL.USER_ID,1);
454 l_trolin_tbl(l_order_count).creation_date := sysdate;
455 l_trolin_tbl(l_order_count).date_required := p_date_required;
456 l_trolin_tbl(l_order_count).from_subinventory_code:= p_from_subinventory_code;
457 l_trolin_tbl(l_order_count).from_locator_id := p_from_locator_id;
458 l_trolin_tbl(l_order_count).inventory_item_id := p_inventory_item_id;
459 l_trolin_tbl(l_order_count).revision := p_revision;
460 l_trolin_tbl(l_order_count).lot_number := p_lot_number;
461 l_trolin_tbl(l_order_count).serial_number_start := p_serial_number_start;
462 l_trolin_tbl(l_order_count).serial_number_end := p_serial_number_end;
463 l_trolin_tbl(l_order_count).last_updated_by := nvl(FND_GLOBAL.USER_ID,1);
464 l_trolin_tbl(l_order_count).last_update_date := sysdate;
465 l_trolin_tbl(l_order_count).last_update_login := nvl(FND_GLOBAL.LOGIN_ID, -1);
466 l_trolin_tbl(l_order_count).line_id := nvl(px_line_id,FND_API.G_MISS_NUM);
467 l_trolin_tbl(l_order_count).line_number := l_line_num;
468 l_trolin_tbl(l_order_count).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
469 l_trolin_tbl(l_order_count).transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
470 l_trolin_tbl(l_order_count).organization_id := p_organization_id;
471 l_trolin_tbl(l_order_count).to_organization_id := p_to_organization_id;
472 l_trolin_tbl(l_order_count).quantity := p_quantity;
473 l_trolin_tbl(l_order_count).quantity_delivered := p_quantity_delivered;
474 l_trolin_tbl(l_order_count).status_date := sysdate;
475 l_trolin_tbl(l_order_count).to_subinventory_code := p_to_subinventory_code;
476 l_trolin_tbl(l_order_count).to_locator_id := p_to_locator_id;
477 l_trolin_tbl(l_order_count).uom_code := p_uom_code;
478 l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
479 l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
480
481 INV_Move_Order_Pub.Create_Move_Order_Lines
482 ( p_api_version_number => 1.0 ,
483 p_init_msg_list => p_init_msg_list,
484 p_commit => l_commit,
485 p_return_values => FND_API.G_TRUE,
486 x_return_status => l_return_status,
487 x_msg_count => l_msg_count,
488 x_msg_data => l_msg_data,
489 p_trolin_tbl => l_trolin_tbl,
490 p_trolin_val_tbl => l_trolin_val_tbl,
491 x_trolin_tbl => l_trolin_tbl,
492 x_trolin_val_tbl => l_trolin_val_tbl
493 );
494
495 px_line_id := l_trolin_tbl(l_order_count).line_id;
496
497 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
498 RAISE FND_API.G_EXC_ERROR;
499 ELSE
500 /* call table handlers for inserting into csp_move_order_lines table*/
501 csp_to_form_molines.Validate_and_Write(
502 P_Api_Version_Number => 1.0
503 ,P_Init_Msg_List => p_init_msg_list
504 ,P_Commit => l_commit
505 ,p_validation_level => null
506 ,p_action_code => 0
507 ,P_line_id => l_trolin_tbl(l_order_count).line_id
508 ,p_CREATED_BY => nvl(fnd_global.user_id,1)
509 ,p_CREATION_DATE => sysdate
510 ,p_LAST_UPDATED_BY => nvl(fnd_global.user_id,1)
511 ,p_LAST_UPDATE_DATE => sysdate
512 ,p_LAST_UPDATED_LOGIN => nvl(fnd_global.login_id,-1)
513 ,p_HEADER_ID => p_header_id
514 ,p_CUSTOMER_PO => p_customer_po
515 ,p_INCIDENT_ID => p_service_request
516 ,p_TASK_ID => p_task_id
517 ,p_TASK_ASSIGNMENT_ID => p_task_assignment_id
518 ,p_COMMENTS => p_comments
519 ,p_attribute_category => null
520 ,p_attribute1 => null
521 ,p_attribute2 => null
522 ,p_attribute3 => null
523 ,p_attribute4 => null
524 ,p_attribute5 => null
525 ,p_attribute6 => null
526 ,p_attribute7 => null
527 ,p_attribute8 => null
528 ,p_attribute9 => null
529 ,p_attribute10 => null
530 ,p_attribute11 => null
531 ,p_attribute12 => null
532 ,p_attribute13 => null
533 ,p_attribute14 => null
534 ,p_attribute15 => null
535 ,X_Return_Status => l_return_status
536 ,X_Msg_Count => l_msg_count
537 ,X_Msg_Data => l_msg_data
538 );
539
540 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
541 RAISE FND_API.G_EXC_ERROR;
542 END IF;
543
544 px_line_id := l_trolin_tbl(l_order_count).line_id;
545 END IF;
546
547 IF fnd_api.to_boolean(p_commit) THEN
548 commit work;
549 END IF;
550 fnd_msg_pub.count_and_get
551 ( p_count => x_msg_count
552 , p_data => x_msg_data);
553 /* Exception Block */
554 EXCEPTION
555 WHEN EXCP_USER_DEFINED THEN
556 Rollback to Create_Move_Order_Line_PUB;
557 x_return_status := FND_API.G_RET_STS_ERROR;
558 fnd_msg_pub.count_and_get
559 ( p_count => x_msg_count
560 , p_data => x_msg_data);
561 WHEN FND_API.G_EXC_ERROR THEN
562 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
563 P_API_NAME => L_API_NAME
564 ,P_PKG_NAME => G_PKG_NAME
565 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
566 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
567 ,X_MSG_COUNT => X_MSG_COUNT
568 ,X_MSG_DATA => X_MSG_DATA
569 ,X_RETURN_STATUS => X_RETURN_STATUS);
570 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
571 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
572 P_API_NAME => L_API_NAME
573 ,P_PKG_NAME => G_PKG_NAME
574 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
575 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
576 ,X_MSG_COUNT => X_MSG_COUNT
577 ,X_MSG_DATA => X_MSG_DATA
578 ,X_RETURN_STATUS => X_RETURN_STATUS);
579 WHEN OTHERS THEN
580 Rollback to Create_Move_Order_Line_PUB;
581 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
582 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
583 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
584 FND_MSG_PUB.ADD;
585 fnd_msg_pub.count_and_get
586 ( p_count => x_msg_count
587 , p_data => x_msg_data);
588 x_return_status := FND_API.G_RET_STS_ERROR;
589
590
591 END CREATE_MOVE_ORDER_LINE;
592
593
594 -- Start of comments
595 --
596 -- Procedure Name : transact_material
597 -- Descritpion : Creates a material transaction in Inventory
598 -- Business Rules :
599 -- Parameters :
600 -- Version : 1.0
601 -- Change History : H Haugerud Added support for Intransit and Direct trans
602 -- End of comments
603
604 PROCEDURE TRANSACT_MATERIAL
605 (p_api_version IN NUMBER
606 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
607 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
608 ,px_transaction_id IN OUT NOCOPY NUMBER
609 ,px_transaction_header_id IN OUT NOCOPY NUMBER
610 ,p_inventory_item_id IN NUMBER
611 ,p_organization_id IN NUMBER
612 ,p_subinventory_code IN VARCHAR2
613 ,p_locator_id IN NUMBER
614 ,p_lot_number IN VARCHAR2
615 ,p_lot_expiration_date IN DATE
616 ,p_revision IN VARCHAR2
617 ,p_serial_number IN VARCHAR2 -- from serial number
618 ,p_to_serial_number IN VARCHAR2 := NULL
619 ,p_quantity IN NUMBER
620 ,p_uom IN VARCHAR2
621 ,p_source_id IN VARCHAR2
622 ,p_source_line_id IN NUMBER
623 ,p_transaction_type_id IN NUMBER
624 ,p_account_id IN NUMBER
625 ,p_transfer_to_subinventory IN VARCHAR2
626 ,p_transfer_to_locator IN NUMBER
627 ,p_transfer_to_organization IN NUMBER
628 ,p_online_process_flag IN BOOLEAN := TRUE
629 ,p_transaction_source_id IN NUMBER -- added by klou 03/30/20000
630 ,p_trx_source_line_id IN NUMBER -- added by klou 03/30/20000
631 ,p_transaction_source_name IN VARCHAR2
632 ,p_waybill_airbill IN VARCHAR2
633 ,p_shipment_number IN VARCHAR2
634 ,p_freight_code IN VARCHAR2
635 ,p_reason_id IN NUMBER
636 ,p_transaction_reference IN VARCHAR2
637 ,p_transaction_date IN DATE
638 ,p_expected_delivery_date IN DATE DEFAULT NULL
639 ,p_FINAL_COMPLETION_FLAG IN VARCHAR2 DEFAULT NULL
640 ,x_return_status OUT NOCOPY VARCHAR2
641 ,x_msg_count OUT NOCOPY NUMBER
642 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
643
644 l_transaction_action_id mtl_transaction_types.transaction_action_id%TYPE;
645 l_transaction_source_type_id mtl_transaction_types.transaction_source_type_id%TYPE;
646 l_transaction_header_id mtl_transactions_interface.transaction_header_id%TYPE;
647 l_transaction_interface_id mtl_transactions_interface.transaction_interface_id%TYPE;
648 l_acct_period_id org_acct_periods.acct_period_id%TYPE;
649 l_quantity NUMBER;
650 l_account_id NUMBER;
651 l_code_comb_id NUMBER;
652 l_subinv_type NUMBER;
653 l_inv_asset_flag VARCHAR2(1);
654
655 l_msg_count NUMBER;
656 l_msg_data VARCHAR2(240);
657 l_return_status VARCHAR2(1);
658
659 l_timeout NUMBER;
660 l_outcome BOOLEAN := TRUE;
661 l_error_code VARCHAR2(200);
662 l_error_explanation VARCHAR2(240);
663
664 l_api_version_number CONSTANT NUMBER := 1.0;
665 l_api_name CONSTANT VARCHAR2(20) := 'Transact_Material';
666 l_check_existence NUMBER := 0;
667 l_organization_id NUMBER;
668
669 l_prev_resp_id NUMBER;
670 l_prev_resp_appl_id NUMBER;
671 l_prev_user_id NUMBER;
672 l_resp_id NUMBER;
673 l_resp_appl_id NUMBER;
674 l_resp_set_flag BOOLEAN := FALSE;
675
676 p_org_id NUMBER;
677
678 EXCP_USER_DEFINED EXCEPTION;
679
680 l_lot_number_val VARCHAR2(80);
681 ln_count NUMBER;
682 p_lot_number_temp VARCHAR2(80);
683 --l_ship_number Varchar2(30);
684
685 l_process_flag number;
686 l_transaction_source_id number;
687 l_wip_entity_type number;
688 l_FINAL_COMPLETION_FLAG Varchar2(5);
689
690 CURSOR l_transaction_header_id_csr IS
691 SELECT mtl_material_transactions_s.nextval
692 FROM dual;
693
694
695 CURSOR l_acct_period_csr is
696 SELECT acct_period_id
697 FROM org_acct_periods
698 WHERE trunc(period_start_date) <= trunc(p_transaction_date)
699 AND trunc(schedule_close_date) >= trunc(p_transaction_date)
700 AND organization_id = p_organization_id
701 AND period_close_date is null
702 AND nvl(open_flag,'Y') = 'Y';
703
704 CURSOR l_resp_csr IS
705 SELECT application_id,
706 responsibility_id
707 FROM fnd_responsibility
708 WHERE responsibility_key = 'SPARES_MANAGEMENT';
709
710 CURSOR l_cost_of_acct(p_org_id Number,p_item_id Number) IS
711 SELECT cost_of_sales_account,inventory_asset_flag
712 FROM mtl_system_items_b
713 WHERE organization_id = p_org_id
714 AND inventory_item_id = p_item_id;
715
716 CURSOR l_subinv(p_org_id NUMBER,p_subinv VARCHAR2) IS
717 SELECT asset_inventory
718 FROM mtl_secondary_inventories
719 WHERE organization_id = p_org_id
720 AND secondary_inventory_name = p_subinv;
721
722 --- added the following local varibales for bug 3608969
723 l_retval number;
724 l_msg_cnt number;
725 l_trans_count number;
726 --------------------------------------------------------
727
728 BEGIN
729 Savepoint Transact_Material_PUB;
730
731 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
732 -- initialize message list
733 FND_MSG_PUB.initialize;
734 END IF;
735
736 -- Standard call to check for call compatibility.
737 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
738 p_api_version,
739 l_api_name,
740 G_PKG_NAME)
741 THEN
742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
743 END IF;
744
745 -- validating transaction_type_id
746 IF p_transaction_type_id IS NULL THEN
747 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
748 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_transaction_type_id', TRUE);
749 FND_MSG_PUB.ADD;
750 RAISE EXCP_USER_DEFINED;
751 END IF;
752
753 -- validating organization
754 IF p_organization_id IS NULL THEN
755 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
756 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
757 FND_MSG_PUB.ADD;
758 RAISE EXCP_USER_DEFINED;
759 END IF;
760
761 -- Check that item is an inventory_item, stockable, transactable and reservable
762 --
763 IF p_inventory_item_id IS NULL THEN
764 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
765 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_inventory_item_id ', TRUE);
766 FND_MSG_PUB.ADD;
767 RAISE EXCP_USER_DEFINED;
768 ELSE
769 BEGIN
770 -- validate whether the inventory_item_id exists in the given oranization_id
771 select inventory_item_id into l_check_existence
772 from mtl_system_items_kfv
773 where inventory_item_id = p_inventory_item_id
774 and organization_id = p_organization_id;
775 EXCEPTION
776 WHEN NO_DATA_FOUND THEN
777 fnd_message.set_name('INV', 'INV-NO ITEM RECROD');
778 fnd_msg_pub.add;
779 RAISE EXCP_USER_DEFINED;
780 WHEN OTHERS THEN
781 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
782 fnd_message.set_token('ERR_FIELD', 'p_inventory_item_id', TRUE);
783 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
784 fnd_message.set_token('TABLE', 'mtl_system_items', TRUE);
785 FND_MSG_PUB.ADD;
786 RAISE EXCP_USER_DEFINED;
787 END;
788 END IF;
789
790 --validating transaction_quantity
791 IF p_quantity IS NULL OR p_quantity < 0 THEN
792 fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
793 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_quantity', TRUE);
794 fnd_msg_pub.add;
795 RAISE EXCP_USER_DEFINED;
796 END IF;
797
798 --validating transaction_uom
799 IF p_uom IS NULL THEN
800 fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
801 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_uom', TRUE);
802 fnd_msg_pub.add;
803 RAISE EXCP_USER_DEFINED;
804 END IF;
805
806 -- Validating Account ID
807 IF p_account_id is not NULL THEN
808 BEGIN
809
810 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
811 SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
812 INTO p_org_id
813 from dual;
814
815 SElECT gcc.code_combination_id
816 INTO l_code_comb_id
817 FROM hr_operating_units hou,gl_sets_of_books gsob,
818 gl_code_combinations gcc
819 -- WHERE hou.organization_id = p_organization_id
820 WHERE hou.organization_id = p_org_id
821 AND hou.set_of_books_id = gsob.set_of_books_id
822 AND gsob.chart_of_accounts_id = gcc.chart_of_accounts_id
823 AND gcc.code_combination_id = p_account_id;
824
825 EXCEPTION
826 WHEN NO_DATA_FOUND THEN
827 fnd_message.set_name('CSP', 'CSP_INVALID_ACCOUNT');
828 fnd_msg_pub.add;
829 RAISE EXCP_USER_DEFINED;
830 WHEN OTHERS THEN
831 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
832 fnd_message.set_token('ERR_FIELD', 'p_account_id', TRUE);
833 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
834 fnd_message.set_token('TABLE', 'GL_code_combinations', TRUE);
835 FND_MSG_PUB.ADD;
836 RAISE EXCP_USER_DEFINED;
837 END;
838 END IF;
839
840
841 /* Removed the vaalidation of subinventory code by klou.
842 Subinvnetory code is a NULL column in the interface table.
843 --validating subinventory_code
844 IF p_subinventory_code IS NULL THEN
845 fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
846 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_subinventory_code', TRUE);
847 fnd_msg_pub.add;
848 RAISE EXCP_USER_DEFINED;
849 END IF;
850 */
851
852 IF (px_transaction_header_id IS NULL) THEN
853 OPEN l_transaction_header_id_csr;
854 FETCH l_transaction_header_id_csr into l_transaction_header_id;
855 CLOSE l_transaction_header_id_csr;
856 px_transaction_header_id := l_transaction_header_id;
857 ELSE
858 l_transaction_header_id := px_transaction_header_id;
859 END IF;
860
861 OPEN l_transaction_header_id_csr;
862 FETCH l_transaction_header_id_csr into l_transaction_interface_id;
863 CLOSE l_transaction_header_id_csr;
864
865 OPEN l_acct_period_csr;
866 FETCH l_acct_period_csr into l_acct_period_id;
867 CLOSE l_acct_period_csr;
868
869 IF l_acct_period_id is null THEN
870 x_msg_data := 'Cannot find open accounting period';
871 x_return_status := FND_API.G_RET_STS_ERROR;
872 END IF;
873
874 IF p_transaction_type_id in (21,32,02,01,93,35) THEN
875 l_quantity := p_quantity * (-1);
876 ELSE
877 l_quantity := p_quantity;
878 END IF;
879 l_account_id := p_account_id;
880 IF p_account_id is NULL THEN
881 IF p_transaction_type_id in(32,93) THEN -- Issuing transaction
882 OPEN l_cost_of_acct(p_organization_id,p_inventory_item_id);
883 FETCH l_cost_of_acct INTO l_account_id,l_inv_asset_flag;
884 CLOSE l_cost_of_acct;
885 ELSIF p_transaction_type_id in (42,94) THEN --- Receiving Transaction
886 OPEN l_subinv(p_organization_id,p_subinventory_code);
887 FETCH l_subinv INTO l_subinv_type;
888 CLOSE l_subinv;
889 IF l_subinv_type = 1 THEN --- Asset Subinventory
890 OPEN l_cost_of_acct(p_organization_id,p_inventory_item_id);
891 FETCH l_cost_of_acct INTO l_account_id,l_inv_asset_flag;
892 CLOSE l_cost_of_acct;
893 ELSIF l_subinv_type = 2 THEN -- Expense Subinventory
894 l_account_id := NULL;
895 END IF; -- End if for subinv type
896 END IF; -- End if for transaction type
897 END IF; -- End if for account id is NULL
898
899 -- bug # 6472464
900 -- removed validation of duplicate shipment number
901 -- this will be checked in wireless code
902 /*
903 If p_shipment_number is not null then
904 Begin
905 SELECT SHIPMENT_NUMBER INTO l_ship_number
906 FROM MTL_MATERIAL_TRANSACTIONS_TEMP M
907 WHERE M.SHIPMENT_NUMBER = p_shipment_number AND ROWNUM = 1;
908 Exception
909 When no_data_found then
910 l_ship_number := Null;
911 End;
912
913 If l_ship_number is Not Null then
914 fnd_message.set_name('INV','INV_SHIP_USED');
915 fnd_msg_pub.add;
916 RAISE EXCP_USER_DEFINED;
917 End if;
918
919 Begin
920 SELECT SHIPMENT_NUM INTO l_ship_number
921 FROM RCV_SHIPMENT_HEADERS M
922 WHERE M.SHIPMENT_NUM = p_shipment_number AND ROWNUM = 1;
923 Exception
924 When no_data_found then
925 l_ship_number := Null;
926 End;
927
928 If l_ship_number is Not Null then
929 fnd_message.set_name('INV','INV_SHIP_USED');
930 fnd_msg_pub.add;
931 RAISE EXCP_USER_DEFINED;
932 End if;
933
934 Begin
935 SELECT SHIPMENT_NUMBER INTO l_ship_number
936 FROM MTL_TRANSACTIONS_INTERFACE M
937 WHERE M.SHIPMENT_NUMBER = p_shipment_number AND ROWNUM = 1;
938 Exception
939 When no_data_found then
940 l_ship_number := Null;
941 End;
942
943 If l_ship_number is Not Null then
944 fnd_message.set_name('INV','INV_SHIP_USED');
945 fnd_msg_pub.add;
946 RAISE EXCP_USER_DEFINED;
947 End if;
948
949
950 End if;
951 */
952
953 If INSTR(p_transaction_source_name,'REPAIR_PO_WIP') > 0 then
954 l_WIP_ENTITY_TYPE := 1;
955 If p_transaction_type_id = 43 or p_transaction_type_id = 44 then
956 l_FINAL_COMPLETION_FLAG := p_FINAL_COMPLETION_FLAG;
957 Else
958 l_FINAL_COMPLETION_FLAG := NULL;
959 End if;
960 Else
961 l_WIP_ENTITY_TYPE := NULL;
962 l_FINAL_COMPLETION_FLAG := NULL;
963 End if;
964
965 INSERT INTO mtl_transactions_interface
966 ( source_code
967 , source_header_id
968 , source_line_id
969 , process_flag
970 , transaction_mode
971 , transaction_header_id
972 , transaction_interface_id
973 , inventory_item_id
974 , revision
975 , organization_id
976 , subinventory_code
977 , locator_id
978 , transaction_quantity
979 , transaction_uom
980 , transaction_date
981 , acct_period_id
982 , distribution_account_id
983 , transaction_source_name
984 , transaction_type_id
985 , transfer_subinventory
986 , transfer_locator
987 , transfer_organization
988 , last_update_date
989 , last_updated_by
990 , creation_date
991 , created_by
992 , last_update_login
993 , lock_flag --always set to 2 so that the transaction_manager will pick the record and assign it to the transaction_worker.
994 , transaction_source_id
995 , trx_source_line_id
996 , waybill_airbill
997 , shipment_number
998 , freight_code
999 , reason_id
1000 , transaction_reference
1001 , expected_arrival_date
1002 , WIP_ENTITY_TYPE
1003 , FINAL_COMPLETION_FLAG
1004 )
1005 VALUES
1006 ( nvl(p_source_id,'CSP')
1007 , 100 -- source header id
1008 , nvl(p_source_line_id,1)
1009 , 1 --process_flag yes
1010 , 2 --transaction_mode online
1011 , l_transaction_header_id
1012 , l_transaction_interface_id
1013 , p_inventory_item_id
1014 , p_revision
1015 , p_organization_id
1016 , p_subinventory_code
1017 , p_locator_id
1018 , l_quantity
1019 , p_uom
1020 , p_transaction_date --transaction_date
1021 , l_acct_period_id
1022 , l_account_id
1023 , p_transaction_source_name
1024 , p_transaction_type_id
1025 , p_transfer_to_subinventory
1026 , p_transfer_to_locator
1027 , p_transfer_to_organization
1028 , sysdate --last_update_date
1029 , nvl(fnd_global.user_id,1) --last_updated_by
1030 , sysdate --creation_date
1031 , nvl(fnd_global.user_id,1) --created_by
1032 , nvl(fnd_global.login_id,-1)
1033 , 2
1034 , decode(sign(p_transaction_source_id-1000000000000),-1,
1035 p_transaction_source_id,null)
1036 , decode(sign(greatest(p_transaction_source_id,p_trx_source_line_id)
1037 -1000000000000),-1,p_trx_source_line_id,null)
1038 , p_waybill_airbill
1039 , p_shipment_number
1040 , p_freight_code
1041 , p_reason_id
1042 , p_transaction_reference
1043 , p_expected_delivery_date
1044 , l_WIP_ENTITY_TYPE
1045 , l_FINAL_COMPLETION_FLAG
1046 );
1047
1048 /*
1049 COMMIT;
1050 select process_flag,transaction_source_id,wip_entity_type
1051 into l_process_flag,l_transaction_source_id,l_wip_entity_type
1052 from mtl_transactions_interface
1053 where transaction_header_id = l_transaction_header_id
1054 and transaction_interface_id = l_transaction_interface_id;
1055
1056 dbms_output.put_line('wip_entity_type '||l_wip_entity_type||' Process_flag '||l_process_flag||' transaction_source_id ' || l_transaction_source_id);
1057 */
1058
1059 p_lot_number_temp := p_lot_number;
1060
1061 /*
1062 select count(*)
1063 into ln_count
1064 from mtl_system_items
1065 where inventory_item_id = p_inventory_item_id
1066 and serial_number_control_code <> 1
1067 and lot_control_code = 2
1068 and organization_id = p_organization_id;
1069
1070 If ln_count > 0 and p_lot_number is null then
1071 */
1072
1073 If p_serial_number is not null and p_lot_number is null then
1074 Begin
1075 Select lot_number
1076 into l_lot_number_val
1077 From MTL_SERIAL_NUMBERS_VAL_V
1078 Where current_organization_id = p_organization_id
1079 and current_subinventory_code = p_subinventory_code
1080 and inventory_item_id = p_inventory_item_id
1081 and serial_number = p_serial_number
1082 and lot_number is not null;
1083 Exception
1084 When no_data_found then
1085 l_lot_number_val := null;
1086 End;
1087
1088 If l_lot_number_val is not null then
1089 p_lot_number_temp := l_lot_number_val;
1090 end if;
1091 End if;
1092
1093 -- End if;
1094
1095
1096
1097 IF p_lot_number_temp IS NOT NULL THEN
1098 INSERT INTO mtl_transaction_lots_interface
1099 ( transaction_interface_id
1100 , lot_number
1101 , lot_expiration_date
1102 , transaction_quantity
1103 , serial_transaction_temp_id
1104 , last_update_date
1105 , last_updated_by
1106 , creation_date
1107 , created_by
1108 , last_update_login
1109 )
1110 VALUES
1111 ( l_transaction_interface_id
1112 , p_lot_number_temp
1113 , p_lot_expiration_date
1114 , p_quantity
1115 , l_transaction_interface_id -- We will only have 1 serial number at a time
1116 , sysdate
1117 , nvl(fnd_global.user_id,-1)
1118 , sysdate
1119 , nvl(fnd_global.user_id,-1)
1120 , nvl(fnd_global.login_id,-1)
1121 );
1122 END IF;
1123
1124 IF p_serial_number IS NOT NULL THEN
1125 INSERT INTO mtl_serial_numbers_interface
1126 ( transaction_interface_id
1127 , fm_serial_number
1128 , to_serial_number
1129 , last_update_date
1130 , last_updated_by
1131 , creation_date
1132 , created_by
1133 , last_update_login)
1134 VALUES
1135 ( l_transaction_interface_id
1136 , p_serial_number
1137 , nvl(p_to_serial_number, p_serial_number)
1138 , sysdate --last_update_date
1139 , nvl(fnd_global.user_id,1) --last_updated_by
1140 , sysdate --creation_date
1141 , nvl(fnd_global.user_id,1) --created_by
1142 , nvl(fnd_global.login_id,-1) --last_update_login
1143 );
1144 END IF;
1145
1146 /*l_transaction_header_id := 2034265;*/
1147 --dbms_output.put_line('transaction_header_id ' || l_transaction_header_id);
1148
1149 -- If online_prcoess_flag is true then
1150 -- Call Inventory API for processing transactions in mtl_transactions_interface table
1151 --dbms_output.put_line('transact_material: after calling the apps_initialized');
1152
1153 IF (p_online_process_flag) THEN
1154
1155 --check to see if we need to do intiialization of responsibility
1156 l_prev_resp_id := fnd_global.resp_id;
1157
1158 IF (l_prev_resp_id IS NULL OR l_prev_resp_id = -1) THEN
1159 l_prev_resp_appl_id := fnd_global.resp_appl_id;
1160 l_prev_user_id := fnd_global.user_id;
1161 l_resp_set_flag := TRUE;
1162
1163 OPEN l_resp_csr;
1164 FETCH l_resp_csr into l_resp_appl_id, l_resp_id;
1165 CLOSE l_resp_csr;
1166
1167 fnd_global.apps_initialize(user_id => nvl(fnd_global.user_id, -1),
1168 resp_id => l_resp_id,
1169 resp_appl_id => l_resp_appl_id
1170 );
1171 END IF;
1172
1173 ---------------------------------------------------
1174 --- Added for bug 3608969
1175 Begin
1176 l_retval := INV_TXN_MANAGER_PUB.process_Transactions(p_api_version => 1,
1177 p_init_msg_list => fnd_api.g_false ,
1178 p_commit => fnd_api.g_false ,
1179 p_validation_level => fnd_api.g_valid_level_full ,
1180 x_return_status => l_return_status,
1181 x_msg_count => l_msg_cnt,
1182 x_msg_data => l_msg_data,
1183 x_trans_count => l_trans_count,
1184 p_table => 1,
1185 p_header_id => l_transaction_header_id);
1186
1187 if(l_retval <> 0) THEN
1188 l_outcome := false;
1189 select error_code, error_explanation
1190 into l_error_code, l_error_explanation
1191 from mtl_transactions_interface
1192 where transaction_header_id = l_transaction_header_id
1193 and rownum = 1;
1194 else
1195 l_outcome := true;
1196 end if;
1197
1198 EXCEPTION
1199 WHEN NO_DATA_FOUND THEN
1200 l_error_code := ' ';
1201 l_error_explanation := 'No Errors';
1202 l_outcome := true;
1203 WHEN TOO_MANY_ROWS THEN
1204 l_error_explanation:= fnd_message.get;
1205 l_outcome := false;
1206 WHEN OTHERS THEN
1207 l_outcome := false;
1208 END;
1209 ----------------------------------------------------
1210
1211
1212 /* commented for bug 3608969
1213 l_outcome := mtl_online_transaction_pub.process_online
1214 ( p_transaction_header_id => l_transaction_header_id
1215 , p_timeout => l_timeout
1216 , p_error_code => l_error_code
1217 , p_error_explanation => l_error_explanation
1218 );
1219 */
1220
1221 IF (l_resp_set_flag) THEN
1222 fnd_global.apps_initialize(user_id => l_prev_user_id,
1223 resp_id => l_prev_resp_id,
1224 resp_appl_id => l_prev_resp_appl_id);
1225 END IF;
1226
1227 IF (l_outcome = FALSE) THEN
1228 delete from mtl_transactions_interface where transaction_header_id = l_transaction_header_id;
1229 FND_MESSAGE.SET_NAME('CSP', 'CSP_TRANSACT_ERRORS');
1230 FND_MESSAGE.SET_TOKEN('ERROR_CODE', l_error_code, TRUE);
1231 FND_MESSAGE.SET_TOKEN('ERROR_EXPLANATION', l_error_explanation, FALSE);
1232 FND_MSG_PUB.ADD;
1233 RAISE EXCP_USER_DEFINED;
1234 END IF;
1235 END IF;
1236
1237 IF fnd_api.to_boolean(p_commit) THEN
1238 commit work;
1239 END IF;
1240
1241 x_return_status := FND_API.G_RET_STS_SUCCESS;
1242 -- dbms_output.put_line('TRANSACT_MATERIAL: returning successfully');
1243 fnd_msg_pub.count_and_get
1244 ( p_count => x_msg_count
1245 , p_data => x_msg_data);
1246 EXCEPTION
1247 WHEN EXCP_USER_DEFINED THEN
1248 If l_outcome = true then -- i.e. process_online has not been called.
1249 Rollback to Transact_Material_Pub;
1250 end if;
1251 x_return_status := FND_API.G_RET_STS_ERROR;
1252 fnd_msg_pub.count_and_get
1253 ( p_count => x_msg_count
1254 , p_data => x_msg_data);
1255 WHEN FND_API.G_EXC_ERROR THEN
1256 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1257 P_API_NAME => L_API_NAME
1258 ,P_PKG_NAME => G_PKG_NAME
1259 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1260 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1261 ,X_MSG_COUNT => X_MSG_COUNT
1262 ,X_MSG_DATA => X_MSG_DATA
1263 ,X_RETURN_STATUS => X_RETURN_STATUS);
1264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1265 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1266 P_API_NAME => L_API_NAME
1267 ,P_PKG_NAME => G_PKG_NAME
1268 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1269 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1270 ,X_MSG_COUNT => X_MSG_COUNT
1271 ,X_MSG_DATA => X_MSG_DATA
1272 ,X_RETURN_STATUS => X_RETURN_STATUS);
1273 WHEN OTHERS THEN
1274 If l_outcome = true then
1275 Rollback to Transact_Material_Pub;
1276 end if;
1277 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1278 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1279 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1280 fnd_msg_pub.add;
1281 fnd_msg_pub.count_and_get
1282 ( p_count => x_msg_count
1283 , p_data => x_msg_data);
1284 x_return_status := fnd_api.g_ret_sts_error;
1285
1286 END TRANSACT_MATERIAL;
1287
1288
1289 PROCEDURE transact_temp_record(
1290 /*$Header: csppttnb.pls 120.6 2008/02/18 06:27:08 htank ship $*/
1291 -- Start of Comments
1292 -- Procedure name : transact_temp_record
1293 -- Purpose : This procedure copies the data from the given mtl_material_transactions_temp record to the
1294 -- mtl_transactions_temp_interface table. It will also analyzed whether the item associated with the
1295 -- the given temp id is under serial or lot control. If the item is under under lot or serial control, this
1296 -- procedure inserts the necessary data into the mtl_lot_transactions_interface table or the
1297 -- mtl_serial_numbers_interface table. After the insertion completes, it deletes the existing record from
1298 -- the mtl_material_transactions_temp, mtl_transaction_lots_temp or the mtl_serial_numbers_temp tables.
1299 --
1300 -- History :
1301 -- Person Date Descriptions
1302 -- ------ ---- --------------
1303 -- klou 27-Mar-2000 created.
1304 --
1305 -- NOTES: If validations have been done in the precedent procedure from which this one is being called, doing a
1306 -- full validation here is unnecessary. To avoid repeating the same validations, you can set the
1307 -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
1308 -- responsibility to make sure all proper validations have been done before calling this procedure.
1309 -- You are recommended to let this procedure handle the validations if you are not sure.
1310 --
1311 -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
1312 -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
1313 -- to FND_API.G_VALID_LEVEL_FULL when making the call.
1314 -- End of Comments
1315
1316 P_Api_Version_Number IN NUMBER,
1317 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1318 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1319 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1320 p_transaction_temp_id IN NUMBER,
1321 px_transaction_header_id IN OUT NOCOPY NUMBER,
1322 p_online_process_flag IN BOOLEAN := FALSE,
1323 X_Return_Status OUT NOCOPY VARCHAR2,
1324 X_Msg_Count OUT NOCOPY NUMBER,
1325 X_Msg_Data OUT NOCOPY VARCHAR2
1326 )
1327 IS
1328 Cursor l_Get_Temp_Csr IS
1329 select
1330 transaction_temp_id ,
1331 transaction_source_id ,
1332 lot_number ,
1333 lot_expiration_date ,
1334 transaction_quantity ,
1335 move_order_line_id ,
1336 item_lot_control_code ,
1337 item_serial_control_code ,
1338 inventory_item_id ,
1339 organization_id ,
1340 subinventory_code ,
1341 locator_id ,
1342 revision ,
1343 TRANSACTION_UOM ,
1344 SOURCE_CODE ,
1345 source_line_id ,
1346 TRANSACTION_TYPE_ID ,
1347 distribution_account_id ,
1348 transfer_subinventory ,
1349 transfer_to_location ,
1350 transfer_organization ,
1351 trx_source_line_id ,
1352 expected_arrival_date
1353 from mtl_material_transactions_temp
1354 where transaction_temp_id = p_transaction_temp_id;
1355
1356 Cursor l_Get_Lot_Temp_Csr IS
1357 select transaction_temp_id, serial_transaction_temp_id,
1358 lot_number, lot_expiration_date, transaction_quantity
1359 from mtl_transaction_lots_temp
1360 where transaction_temp_id = p_transaction_temp_id;
1361
1362 Cursor l_Get_Serial_Temp_Csr(l_transaction_temp_id NUMBER) IS
1363 select transaction_temp_id, fm_serial_number, to_serial_number, serial_prefix from mtl_serial_numbers_temp
1364 where transaction_temp_id = l_transaction_temp_id;
1365
1366 Cursor l_Get_txn_header_id_csr IS
1367 SELECT mtl_material_transactions_s.nextval
1368 FROM dual;
1369
1370 Cursor l_Get_Mo_Header_id_csr(l_line_id NUMBER) IS
1371 select distinct header_id from csp_moveorder_lines
1372 where line_id = l_line_id;
1373
1374 Type l_lot_temp_type IS Record (
1375 transaction_temp_id NUMBER,
1376 serial_transaction_temp_id NUMBER,
1377 lot_number VARCHAR2(80),
1378 lot_expiration_date DATE,
1379 transaction_quantity NUMBER);
1380
1381 Type l_serial_temp_type IS Record (
1382 transaction_temp_id NUMBER,
1383 fm_serial_number VARCHAR2(30),
1384 to_serial_number VARCHAR2(30),
1385 serial_prefix NUMBER );
1386
1387 Type l_mtl_temp_type IS Record (
1388 transaction_temp_id NUMBER,
1389 transaction_source_id NUMBER,
1390 lot_number VARCHAR2(80),
1391 lot_expiration_date DATE,
1392 transaction_quantity NUMBER,
1393 move_order_line_id NUMBER,
1394 item_lot_control_code NUMBER,
1395 item_serial_control_code NUMBER,
1396 inventory_item_id NUMBER,
1397 organization_id NUMBER,
1398 subinventory_code VARCHAR2(10),
1399 locator_id NUMBER,
1400 revision VARCHAR2(3),
1401 TRANSACTION_UOM VARCHAR2(3),
1402 SOURCE_CODE VARCHAR2(30),
1403 source_line_id NUMBER,
1404 TRANSACTION_TYPE_ID NUMBER,
1405 distribution_account_id NUMBER,
1406 transfer_subinventory VARCHAR2(10),
1407 transfer_to_location NUMBER,
1408 transfer_organization NUMBER,
1409 trx_source_line_id NUMBER,
1410 expected_arrival_date DATE);
1411
1412 l_transaction_id NUMBER;
1413 l_Mo_header_id NUMBER;
1414 l_lot_temp_rec l_lot_temp_type;
1415 l_serial_temp_rec l_serial_temp_type;
1416 l_mtl_txn_temp_rec l_mtl_temp_type; --CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
1417 l_api_version_number CONSTANT NUMBER := 1.0;
1418 l_api_name CONSTANT VARCHAR2(50) := 'transact_temp_record';
1419 l_msg_data VARCHAR2(300);
1420 l_check_existence NUMBER := 0;
1421 l_return_status VARCHAR2(1);
1422 l_msg_count NUMBER := 0;
1423 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1424 l_transaction_header_id number := px_transaction_header_id;
1425 l_lot_number VARCHAR2(80);
1426 l_lot_expiration_date DATE;
1427 l_fm_serial_number VARCHAR2(30);
1428 l_to_serial_number VARCHAR2(30);
1429 l_qty_processed NUMBER := 0;
1430 l_moheader_id NUMBER;
1431 EXCP_USER_DEFINED EXCEPTION;
1432
1433 BEGIN
1434 Savepoint transact_temp_record_pub;
1435
1436 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1437 -- initialize message list
1438 FND_MSG_PUB.initialize;
1439 END IF;
1440
1441 -- Standard call to check for call compatibility.
1442 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1443 p_api_version_number,
1444 l_api_name,
1445 G_PKG_NAME)
1446 THEN
1447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1448 END IF;
1449
1450 IF p_validation_level = fnd_api.g_valid_level_full THEN
1451 IF p_transaction_temp_id IS NULL THEN
1452 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
1453 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_transaction_temp_id', FALSE);
1454 FND_MSG_PUB.ADD;
1455 RAISE EXCP_USER_DEFINED;
1456 ELSE
1457 BEGIN
1458 select transaction_temp_id into l_check_existence
1459 from mtl_material_transactions_temp
1460 where transaction_temp_id = p_transaction_temp_id;
1461 EXCEPTION
1462 WHEN NO_DATA_FOUND THEN
1463 fnd_message.set_name ('CSP', 'CSP_INVALID_TXN_TEMP_ID');
1464 fnd_message.set_token('ID', to_char(p_transaction_temp_id), FALSE);
1465 fnd_msg_pub.add;
1466 RAISE EXCP_USER_DEFINED;
1467 WHEN OTHERS THEN
1468 RAISE TOO_MANY_ROWS; -- this will really go to OTHERS exception.
1469 END;
1470 END IF;
1471 End If;
1472
1473 IF px_transaction_header_id IS NULL THEN
1474 Open l_Get_Txn_Header_id_csr;
1475 Fetch l_Get_Txn_Header_id_csr into l_transaction_header_id;
1476 Close l_Get_Txn_Header_id_csr;
1477 ELSE
1478 l_transaction_header_id := px_transaction_header_id;
1479 END IF;
1480
1481 -- begin to transact the transaction record
1482 -- Fetch the temp record into l_mtl_txn_temp_rec;
1483 Open l_Get_Temp_Csr;
1484 Fetch l_Get_Temp_Csr Into l_mtl_txn_temp_rec;
1485 If l_Get_Temp_Csr%NOTFOUND Then
1486 Close l_Get_Temp_Csr;
1487 fnd_message.set_name ('CSP', 'CSP_INVALID_TXN_TEMP_ID');
1488 fnd_message.set_token('ID', to_char(p_transaction_temp_id), FALSE);
1489 fnd_msg_pub.add;
1490 RAISE EXCP_USER_DEFINED;
1491 Else
1492 Close l_Get_Temp_Csr;
1493 End If;
1494
1495 -- find the move order header id for keeping track on the record in the mtl_material_transactions table.
1496 If l_mtl_txn_temp_rec.transaction_source_id is null then
1497 Open l_Get_Mo_Header_id_csr(l_mtl_txn_temp_rec.move_order_line_id);
1498 Fetch l_Get_Mo_Header_id_csr Into l_Mo_header_id;
1499 If l_Get_Mo_header_id_csr%NOTFOUND THEN
1500 Close l_Get_Mo_header_id_csr;
1501 fnd_message.set_name ('CSP', 'CSP_MOVEORDER_LINE_NO_PARENT');
1502 fnd_message.set_token ('LINE_ID', to_char(l_mtl_txn_temp_rec.move_order_line_id), FALSE);
1503 fnd_msg_pub.add;
1504 End If;
1505
1506 Close l_Get_Mo_header_id_csr;
1507 Else
1508 l_mo_header_id := l_mtl_txn_temp_rec.transaction_source_id;
1509 End If;
1510
1511 -- Analyze whether the item being transacted is under serial or lot control.
1512 -- case 1: only lot control
1513 If nvl(l_mtl_txn_temp_rec.item_lot_control_code, 1) <> 1 And
1514 nvl(l_mtl_txn_temp_rec.item_serial_control_code, 1) in (1, 6) Then
1515
1516 l_qty_processed := 0;
1517
1518 -- open the l_Get_Lot_Temp_Csr to find out the lot number and expiration date
1519 Open l_Get_Lot_Temp_Csr;
1520 Loop <<process_lot_loop_1>> -- there may be more than one lot record
1521 Fetch l_Get_Lot_Temp_Csr Into l_lot_temp_rec;
1522 Exit When l_Get_Lot_Temp_Csr%NOTFOUND;
1523
1524 IF l_qty_processed <= l_mtl_txn_temp_rec.transaction_quantity Then
1525 TRANSACT_MATERIAL
1526 (p_api_version => l_api_version_number
1527 ,p_Init_Msg_List => P_Init_Msg_List
1528 ,p_commit => l_commit
1529 ,px_transaction_id => l_transaction_id
1530 ,px_transaction_header_id => l_transaction_header_id
1531 ,p_inventory_item_id => l_mtl_txn_temp_rec.inventory_item_id
1532 ,p_organization_id => l_mtl_txn_temp_rec.organization_id
1533 ,p_subinventory_code => l_mtl_txn_temp_rec.subinventory_code
1534 ,p_locator_id => l_mtl_txn_temp_rec.locator_id
1535 ,p_lot_number => l_lot_temp_rec.lot_number
1536 ,p_lot_expiration_date => l_lot_temp_rec.lot_expiration_date
1537 ,p_revision => l_mtl_txn_temp_rec.revision
1538 ,p_serial_number => null
1539 ,p_to_serial_number => NULL
1540 ,p_quantity => l_lot_temp_rec.transaction_quantity
1541 ,p_uom => l_mtl_txn_temp_rec.TRANSACTION_UOM
1542 ,p_source_id => l_mtl_txn_temp_rec.SOURCE_CODE --TRANSACTION_SOURCE_ID
1543 ,p_source_line_id => l_mtl_txn_temp_rec.source_line_id
1544 ,p_transaction_type_id => l_mtl_txn_temp_rec.TRANSACTION_TYPE_ID
1545 ,p_account_id => l_mtl_txn_temp_rec.distribution_account_id
1546 ,p_transfer_to_subinventory => l_mtl_txn_temp_rec.transfer_subinventory
1547 ,p_transfer_to_locator => l_mtl_txn_temp_rec.transfer_to_location
1548 ,p_transfer_to_organization => l_mtl_txn_temp_rec.transfer_organization
1549 ,p_online_process_flag => p_online_process_flag
1550 ,p_transaction_source_id => l_mo_header_id
1551 ,p_trx_source_line_id => nvl(l_mtl_txn_temp_rec.trx_source_line_id, l_mtl_txn_temp_rec.move_order_line_id)
1552 ,p_expected_delivery_date => l_mtl_txn_temp_rec.expected_arrival_date
1553 ,x_return_status => l_return_status
1554 ,x_msg_count => l_msg_count
1555 ,x_msg_data => l_msg_data
1556 );
1557
1558 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1559 Close l_Get_Lot_Temp_Csr;
1560 RAISE FND_API.G_EXC_ERROR;
1561 END IF;
1562 l_qty_processed := l_qty_processed + l_lot_temp_rec.transaction_quantity;
1563 End If;
1564 End Loop; -- <<process_lot_loop_1>>
1565
1566 If l_Get_Lot_Temp_Csr%rowcount = 0 Then
1567 Close l_Get_Lot_Temp_Csr;
1568 fnd_message.set_name('CSP', 'CSP_NO_LOT_TXN_RECORD');
1569 fnd_msg_pub.add;
1570 RAISE EXCP_USER_DEFINED;
1571 End if;
1572
1573 If l_Get_Lot_Temp_Csr%ISOPEN Then
1574 Close l_Get_Lot_Temp_Csr;
1575 End if;
1576
1577 -- case 2: under both lot control and serial control
1578 Elsif nvl(l_mtl_txn_temp_rec.item_lot_control_code, 1) <> 1 And
1579 nvl(l_mtl_txn_temp_rec.item_serial_control_code, 1) in (2, 5) Then
1580 l_qty_processed := 0;
1581
1582 -- open the l_Get_Lot_Temp_Csr to find out the lot number and expiration date
1583 Open l_Get_Lot_Temp_Csr;
1584 Loop <<process_lot_loop_2>> -- there may be more than one lot record
1585 Fetch l_Get_Lot_Temp_Csr Into l_lot_temp_rec;
1586 Exit When l_Get_Lot_Temp_Csr%NOTFOUND;
1587
1588 -- for each lot record, it may have more than one serial record
1589 -- open the l_Get_Serial_Temp_Csr(l_transaction_temp_id NUMBER)
1590 Open l_Get_Serial_Temp_Csr(l_lot_temp_rec.serial_transaction_temp_id);
1591 Loop <<process_serial_loop_2>>
1592 Fetch l_Get_Serial_Temp_Csr Into l_serial_temp_rec;
1593 Exit when l_Get_Serial_Temp_Csr%NOTFOUND;
1594
1595 IF l_qty_processed <= l_mtl_txn_temp_rec.transaction_quantity Then
1596 TRANSACT_MATERIAL
1597 (p_api_version => l_api_version_number
1598 ,p_Init_Msg_List => P_Init_Msg_List
1599 ,p_commit => l_commit
1600 ,px_transaction_id => l_transaction_id
1601 ,px_transaction_header_id => l_transaction_header_id
1602 ,p_inventory_item_id => l_mtl_txn_temp_rec.inventory_item_id
1603 ,p_organization_id => l_mtl_txn_temp_rec.organization_id
1604 ,p_subinventory_code => l_mtl_txn_temp_rec.subinventory_code
1605 ,p_locator_id => l_mtl_txn_temp_rec.locator_id
1606 ,p_lot_number => l_lot_temp_rec.lot_number
1607 ,p_lot_expiration_date => l_lot_temp_rec.lot_expiration_date
1608 ,p_revision => l_mtl_txn_temp_rec.revision
1609 ,p_serial_number => l_serial_temp_rec.fm_serial_number
1610 ,p_to_serial_number => l_serial_temp_rec.to_serial_number
1611 ,p_quantity => nvl(l_serial_temp_rec.serial_prefix, 1)
1612 ,p_uom => l_mtl_txn_temp_rec.TRANSACTION_UOM
1613 ,p_source_id => l_mtl_txn_temp_rec.SOURCE_CODE --TRANSACTION_SOURCE_ID
1614 ,p_source_line_id => l_mtl_txn_temp_rec.source_line_id
1615 ,p_transaction_type_id => l_mtl_txn_temp_rec.TRANSACTION_TYPE_ID
1616 ,p_account_id => l_mtl_txn_temp_rec.distribution_account_id
1617 ,p_transfer_to_subinventory => l_mtl_txn_temp_rec.transfer_subinventory
1618 ,p_transfer_to_locator => l_mtl_txn_temp_rec.transfer_to_location
1619 ,p_transfer_to_organization => l_mtl_txn_temp_rec.transfer_organization
1620 ,p_online_process_flag => p_online_process_flag
1621 ,p_transaction_source_id => l_mo_header_id
1622 ,p_trx_source_line_id => nvl(l_mtl_txn_temp_rec.trx_source_line_id, l_mtl_txn_temp_rec.move_order_line_id)
1623 ,p_expected_delivery_date => l_mtl_txn_temp_rec.expected_arrival_date
1624 ,x_return_status => l_return_status
1625 ,x_msg_count => l_msg_count
1626 ,x_msg_data => l_msg_data
1627 );
1628
1629 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1630 Close l_Get_Lot_Temp_Csr;
1631 Close l_Get_Serial_Temp_Csr;
1632 RAISE FND_API.G_EXC_ERROR;
1633 END IF;
1634 l_qty_processed := l_qty_processed + nvl(l_serial_temp_rec.serial_prefix, 1);
1635 End If;
1636 End Loop; -- <<process_serial_loop_2>>
1637
1638 If l_Get_Serial_Temp_Csr%rowcount = 0 Then
1639 Close l_Get_Lot_Temp_Csr;
1640 Close l_Get_Serial_Temp_Csr;
1641 fnd_message.set_name('CSP', 'CSP_NO_SERIAL_TXN_RECORD');
1642 fnd_msg_pub.add;
1643 RAISE EXCP_USER_DEFINED;
1644 End If;
1645
1646 If l_Get_Serial_Temp_Csr%ISOPEN Then
1647 Close l_Get_Serial_Temp_Csr;
1648 End if;
1649 End Loop; --<<process_lot_loop_2>>
1650
1651 If l_Get_Lot_Temp_Csr%ISOPEN Then
1652 Close l_Get_Lot_Temp_Csr;
1653 End if;
1654
1655 -- case 3: only under serial control
1656 Elsif nvl(l_mtl_txn_temp_rec.item_lot_control_code, 1) = 1 And
1657 nvl(l_mtl_txn_temp_rec.item_serial_control_code, 1) in (2, 5) Then
1658 l_qty_processed := 0;
1659
1660 -- open the l_Get_Serial_Temp_Csr(l_transaction_temp_id NUMBER)
1661 Open l_Get_Serial_Temp_Csr(l_mtl_txn_temp_rec.transaction_temp_id);
1662 Loop <<process_serial_loop_3>>
1663 Fetch l_Get_Serial_Temp_Csr Into l_serial_temp_rec;
1664 Exit when l_Get_Serial_Temp_Csr%NOTFOUND;
1665
1666 IF l_qty_processed <= l_mtl_txn_temp_rec.transaction_quantity Then
1667 TRANSACT_MATERIAL
1668 (p_api_version => l_api_version_number
1669 ,p_Init_Msg_List => P_Init_Msg_List
1670 ,p_commit => l_commit
1671 ,px_transaction_id => l_transaction_id
1672 ,px_transaction_header_id => l_transaction_header_id
1673 ,p_inventory_item_id => l_mtl_txn_temp_rec.inventory_item_id
1674 ,p_organization_id => l_mtl_txn_temp_rec.organization_id
1675 ,p_subinventory_code => l_mtl_txn_temp_rec.subinventory_code
1676 ,p_locator_id => l_mtl_txn_temp_rec.locator_id
1677 ,p_lot_number => null
1678 ,p_lot_expiration_date => null
1679 ,p_revision => l_mtl_txn_temp_rec.revision
1680 ,p_serial_number => l_serial_temp_rec.fm_serial_number
1681 ,p_to_serial_number => l_serial_temp_rec.to_serial_number
1682 ,p_quantity => nvl(l_serial_temp_rec.serial_prefix, 1)
1683 ,p_uom => l_mtl_txn_temp_rec.TRANSACTION_UOM
1684 ,p_source_id => l_mtl_txn_temp_rec.SOURCE_CODE --TRANSACTION_SOURCE_ID
1685 ,p_source_line_id => l_mtl_txn_temp_rec.source_line_id
1686 ,p_transaction_type_id => l_mtl_txn_temp_rec.TRANSACTION_TYPE_ID
1687 ,p_account_id => l_mtl_txn_temp_rec.distribution_account_id
1688 ,p_transfer_to_subinventory => l_mtl_txn_temp_rec.transfer_subinventory
1689 ,p_transfer_to_locator => l_mtl_txn_temp_rec.transfer_to_location
1690 ,p_transfer_to_organization => l_mtl_txn_temp_rec.transfer_organization
1691 ,p_online_process_flag => p_online_process_flag
1692 ,p_transaction_source_id => l_mo_header_id
1693 ,p_trx_source_line_id => nvl(l_mtl_txn_temp_rec.trx_source_line_id, l_mtl_txn_temp_rec.move_order_line_id)
1694 ,p_expected_delivery_date => l_mtl_txn_temp_rec.expected_arrival_date
1695 ,x_return_status => l_return_status
1696 ,x_msg_count => l_msg_count
1697 ,x_msg_data => l_msg_data
1698 );
1699
1700 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1701 Close l_Get_Serial_Temp_Csr;
1702 RAISE FND_API.G_EXC_ERROR;
1703 END IF;
1704 l_qty_processed := l_qty_processed + l_serial_temp_rec.serial_prefix;
1705 End If;
1706 End Loop; --<<process_serial_loop_3>>
1707 If l_Get_Serial_Temp_Csr%ISOPEN Then
1708 Close l_Get_Serial_Temp_Csr;
1709 End if;
1710
1711 -- case 4: neither serial control nor lot control
1712 Else
1713 TRANSACT_MATERIAL
1714 (p_api_version => l_api_version_number
1715 ,p_Init_Msg_List => P_Init_Msg_List
1716 ,p_commit => l_commit
1717 ,px_transaction_id => l_transaction_id
1718 ,px_transaction_header_id => l_transaction_header_id
1719 ,p_inventory_item_id => l_mtl_txn_temp_rec.inventory_item_id
1720 ,p_organization_id => l_mtl_txn_temp_rec.organization_id
1721 ,p_subinventory_code => l_mtl_txn_temp_rec.subinventory_code
1722 ,p_locator_id => l_mtl_txn_temp_rec.locator_id
1723 ,p_lot_number => null
1724 ,p_lot_expiration_date => null
1725 ,p_revision => l_mtl_txn_temp_rec.revision
1726 ,p_serial_number => null
1727 ,p_to_serial_number => null
1728 ,p_quantity => l_mtl_txn_temp_rec.transaction_quantity
1729 ,p_uom => l_mtl_txn_temp_rec.TRANSACTION_UOM
1730 ,p_source_id => l_mtl_txn_temp_rec.SOURCE_CODE--TRANSACTION_SOURCE_ID
1731 ,p_source_line_id => l_mtl_txn_temp_rec.source_line_id
1732 ,p_transaction_type_id => l_mtl_txn_temp_rec.TRANSACTION_TYPE_ID
1733 ,p_account_id => l_mtl_txn_temp_rec.distribution_account_id
1734 ,p_transfer_to_subinventory => l_mtl_txn_temp_rec.transfer_subinventory
1735 ,p_transfer_to_locator => l_mtl_txn_temp_rec.transfer_to_location
1736 ,p_transfer_to_organization => l_mtl_txn_temp_rec.transfer_organization
1737 ,p_online_process_flag => p_online_process_flag
1738 ,p_transaction_source_id => l_mo_header_id
1739 ,p_trx_source_line_id => nvl(l_mtl_txn_temp_rec.trx_source_line_id, l_mtl_txn_temp_rec.move_order_line_id)
1740 ,p_expected_delivery_date => l_mtl_txn_temp_rec.expected_arrival_date
1741 ,x_return_status => l_return_status
1742 ,x_msg_count => l_msg_count
1743 ,x_msg_data => l_msg_data
1744 );
1745
1746 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1747 Close l_Get_Serial_Temp_Csr;
1748 RAISE FND_API.G_EXC_ERROR;
1749 END IF;
1750 End if;
1751
1752 IF fnd_api.to_boolean(p_commit) THEN
1753 commit work;
1754 END IF;
1755 x_return_status := l_return_status;
1756 fnd_msg_pub.count_and_get
1757 ( p_count => x_msg_count
1758 , p_data => x_msg_data);
1759 EXCEPTION
1760 WHEN EXCP_USER_DEFINED THEN
1761 Rollback to transact_temp_record_pub;
1762 x_return_status := FND_API.G_RET_STS_ERROR;
1763 fnd_msg_pub.count_and_get
1764 ( p_count => x_msg_count
1765 , p_data => x_msg_data);
1766 WHEN FND_API.G_EXC_ERROR THEN
1767 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1768 P_API_NAME => L_API_NAME
1769 ,P_PKG_NAME => G_PKG_NAME
1770 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1771 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1772 ,X_MSG_COUNT => X_MSG_COUNT
1773 ,X_MSG_DATA => X_MSG_DATA
1774 ,X_RETURN_STATUS => X_RETURN_STATUS);
1775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1776 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1777 P_API_NAME => L_API_NAME
1778 ,P_PKG_NAME => G_PKG_NAME
1779 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1780 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1781 ,X_MSG_COUNT => X_MSG_COUNT
1782 ,X_MSG_DATA => X_MSG_DATA
1783 ,X_RETURN_STATUS => X_RETURN_STATUS);
1784 WHEN OTHERS THEN
1785 Rollback to transact_temp_record_pub;
1786 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1787 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1788 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1789 fnd_msg_pub.add;
1790 fnd_msg_pub.count_and_get
1791 ( p_count => x_msg_count
1792 , p_data => x_msg_data);
1793 x_return_status := fnd_api.g_ret_sts_error;
1794 END transact_temp_record;
1795 END csp_transactions_pub;