[Home] [Help]
PACKAGE BODY: APPS.CSP_TRANSACTIONS_PUB
Source
1 PACKAGE BODY CSP_TRANSACTIONS_PUB AS
2 /*$Header: csppttnb.pls 120.30.12020000.4 2013/02/11 08:52:32 htank ship $*/
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSP_TRANSACTIONS_PUB';
6 G_FILE_NAME CONSTANT VARCHAR2(30) := 'csppttnb.pls';
7
8 -- Start of comments
9 --
10 -- Procedure Name : create_move_order_header
11 -- Description : Creates a move order header
12 -- Business Rules :
13 -- Parameters :
14 -- Version : 1.0
15 -- End of comments
16
17 procedure cancel_move_order_header(
18 p_header_id in number,
19 x_return_status OUT NOCOPY varchar2,
20 x_msg_count OUT NOCOPY number,
21 x_msg_data OUT NOCOPY varchar2) is
22
23 begin
24 inv_mo_admin_pub.Close_Order(
25 p_api_version => 1.0,
26 p_init_msg_list => fnd_api.g_false,
27 p_commit => fnd_api.g_false,
28 p_validation_level => fnd_api.g_valid_level_full,
29 p_header_Id => p_header_id,
30 x_msg_count => x_msg_count,
31 x_msg_data => x_msg_data,
32 x_return_status => x_return_status);
33 end cancel_move_order_header;
34
35 procedure cancel_move_order_line(
36 p_line_id in number,
37 x_return_status OUT NOCOPY varchar2,
38 x_msg_count OUT NOCOPY number,
39 x_msg_data OUT NOCOPY varchar2) is
40
41 l_quantity_delivered number;
42 l_mo_header_id number;
43 l_other_line_id number;
44
45 cursor c_quantity_delivered is
46 select quantity_delivered
47 from mtl_txn_request_lines
48 where line_id = p_line_id;
49
50 begin
51
52 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
53 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
54 'csp.plsql.CSP_TRANSACTIONS_PUB.cancel_move_order_line',
55 'Begin');
56 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
57 'csp.plsql.CSP_TRANSACTIONS_PUB.cancel_move_order_line',
58 'p_line_id = ' || p_line_id);
59 end if;
60
61 open c_quantity_delivered;
62 fetch c_quantity_delivered into l_quantity_delivered;
63 close c_quantity_delivered;
64
65 l_quantity_delivered := nvl(l_quantity_delivered,0);
66
67 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
68 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
69 'csp.plsql.CSP_TRANSACTIONS_PUB.cancel_move_order_line',
70 'l_quantity_delivered = ' || l_quantity_delivered);
71 end if;
72
73 if l_quantity_delivered = 0 then
74 inv_mo_admin_pub.cancel_line(
75 p_api_version => 1.0,
76 p_init_msg_list => fnd_api.g_false,
77 p_commit => fnd_api.g_true,
78 p_validation_level => fnd_api.g_valid_level_full,
79 p_line_id => p_line_id,
80 x_msg_count => x_msg_count,
81 x_msg_data => x_msg_data,
82 x_return_status => x_return_status);
83 else
84 inv_mo_admin_pub.close_line(
85 p_api_version => 1.0,
86 p_init_msg_list => fnd_api.g_false,
87 p_commit => fnd_api.g_false,
88 p_validation_level => fnd_api.g_valid_level_full,
89 p_line_id => p_line_id,
90 x_msg_count => x_msg_count,
91 x_msg_data => x_msg_data,
92 x_return_status => x_return_status);
93 end if;
94
95 if x_return_status = 'S' then
96 l_other_line_id := 0;
97
98 select header_id
99 into l_mo_header_id
100 from MTL_TXN_REQUEST_lines
101 where line_id = p_line_id;
102
103 select count(line_id )
104 into l_other_line_id
105 from MTL_TXN_REQUEST_lines
106 where header_id = l_mo_header_id
107 and line_id <> p_line_id;
108
109 if l_other_line_id = 0 then
110 cancel_move_order_header(
111 p_header_id => l_mo_header_id,
112 x_msg_count => x_msg_count,
113 x_msg_data => x_msg_data,
114 x_return_status => x_return_status);
115
116 if x_return_status = 'S' then
117 commit;
118 end if;
119 end if;
120 end if;
121
122 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
123 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
124 'csp.plsql.CSP_TRANSACTIONS_PUB.cancel_move_order_line',
125 'x_return_status = ' || x_return_status
126 || ', x_msg_count = ' || x_msg_count
127 || ', x_msg_data = ' || x_msg_data);
128 end if;
129
130 end cancel_move_order_line;
131
132 procedure reject_move_order_line(
133 p_line_id in number,
134 x_return_status OUT NOCOPY varchar2,
135 x_msg_count OUT NOCOPY number,
136 x_msg_data OUT NOCOPY varchar2) is
137
138 l_header_id number;
139 l_trolin_tbl inv_move_order_pub.Trolin_Tbl_Type;
140 l_trolin_old_tbl inv_move_order_pub.trolin_tbl_type;
141 x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
142
143 cursor c_header_id is
144 select header_id
145 from mtl_txn_request_lines
146 where line_id = p_line_id;
147
148 begin
149 open c_header_id;
150 fetch c_header_id into l_header_id;
151 close c_header_id;
152
153 l_trolin_tbl(1).line_status := 9;
154 l_trolin_tbl(1).header_id := l_header_id;
155 l_trolin_tbl(1).line_id := p_line_id;
156 l_trolin_tbl(1).operation := inv_globals.g_opr_update;
157
158 inv_move_order_pub.process_move_order_line(
159 p_api_version_number => 1.0
160 , x_return_status => x_return_status
161 , x_msg_count => x_msg_count
162 , x_msg_data => x_msg_data
163 , p_trolin_tbl => l_trolin_tbl
164 , p_trolin_old_tbl => l_trolin_old_tbl
165 , x_trolin_tbl => x_trolin_tbl);
166
167 end reject_move_order_line;
168 PROCEDURE CREATE_MOVE_ORDER_HEADER
169 (px_header_id IN OUT NOCOPY NUMBER
170 ,p_request_number IN VARCHAR2
171 ,p_api_version IN NUMBER
172 ,p_Init_Msg_List IN VARCHAR2
173 ,p_commit IN VARCHAR2
174 ,p_date_required IN DATE
175 ,p_organization_id IN NUMBER
176 ,p_from_subinventory_code IN VARCHAR2
177 ,p_to_subinventory_code IN VARCHAR2
178 ,p_address1 IN VARCHAR2
179 ,p_address2 IN VARCHAR2
180 ,p_address3 IN VARCHAR2
181 ,p_address4 IN VARCHAR2
182 ,p_city IN VARCHAR2
183 ,p_postal_code IN VARCHAR2
184 ,p_state IN VARCHAR2
185 ,p_province IN VARCHAR2
186 ,p_country IN VARCHAR2
187 ,p_freight_carrier IN VARCHAR2
188 ,p_shipment_method IN VARCHAR2
189 ,p_autoreceipt_flag IN VARCHAR2
190 ,x_return_status OUT NOCOPY VARCHAR2
191 ,x_msg_count OUT NOCOPY NUMBER
192 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
193
194 l_mohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
195 l_mohdr_val_rec INV_Move_Order_PUB.Trohdr_Val_Rec_Type;
196 l_header_id number;
197
198 l_api_version_number CONSTANT NUMBER := 1.0;
199 l_api_name CONSTANT VARCHAR2(30) := 'Create_Move_Order_Header';
200 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
201 l_msg_count NUMBER;
202 l_msg_data VARCHAR2(240);
203 l_commit VARCHAR2(1) := FND_API.G_FALSE;
204 EXCP_USER_DEFINED EXCEPTION;
205
206 BEGIN
207 SAVEPOINT Create_Move_Order_Header_PUB;
208
209 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
210 -- initialize message list
211 FND_MSG_PUB.initialize;
212 END IF;
213
214 -- Standard call to check for call compatibility.
215 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
216 p_api_version,
217 l_api_name,
218 G_PKG_NAME)
219 THEN
220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 END IF;
222
223 -- initialize return status
224 x_return_status := FND_API.G_RET_STS_SUCCESS;
225
226 -- check organization
227 IF p_organization_id IS NULL THEN
228 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
229 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
230 FND_MSG_PUB.ADD;
231 RAISE EXCP_USER_DEFINED;
232 END IF;
233
234 IF (px_header_id IS NOT NULL) THEN
235 BEGIN
236 SELECT header_id
237 INTO l_header_id
238 FROM mtl_txn_request_headers
239 WHERE header_id = px_header_id
240 AND organization_id = p_organization_id;
241
242 FND_MESSAGE.SET_NAME('CSP', 'CSP_PARAMETER_EXISTS');
243 FND_MESSAGE.SET_TOKEN('PARAMETER', 'px_header_id' , TRUE);
244 FND_MSG_PUB.ADD;
245 RAISE EXCP_USER_DEFINED;
246
247 EXCEPTION
248 WHEN no_data_found THEN
249 -- valid id
250 NULL;
251 END;
252 END IF;
253
254 -- initialize move order header record type
255 l_mohdr_rec.header_id := nvl(px_header_id, FND_API.G_MISS_NUM);
256 l_mohdr_rec.request_number := nvl(p_request_number, FND_API.G_MISS_CHAR);
257 l_mohdr_rec.created_by := nvl(fnd_global.user_id,1);
258 l_mohdr_rec.creation_date := sysdate;
259 l_mohdr_rec.date_required := p_date_required;
260 l_mohdr_rec.from_subinventory_code:= p_from_subinventory_code;
261 l_mohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
262 l_mohdr_rec.last_updated_by := nvl(fnd_global.user_id,1);
263 l_mohdr_rec.last_update_date := sysdate;
264 l_mohdr_rec.last_update_login := nvl(fnd_global.login_id,-1);
265 l_mohdr_rec.organization_id := p_organization_id;
266 l_mohdr_rec.status_date := sysdate;
267 l_mohdr_rec.to_subinventory_code := p_to_subinventory_code;
268 l_mohdr_rec.transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
269 l_mohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REQUISITION;
270 l_mohdr_rec.db_flag := FND_API.G_TRUE;
271 l_mohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
272
273 -- call public api to create a record for move order header in Oracle Inventory
274 INV_Move_Order_PUB.Create_Move_order_Header(
275 p_api_version_number => 1,
276 p_init_msg_list => p_init_msg_list,
277 p_return_values => FND_API.G_TRUE,
278 p_commit => l_commit,
279 x_return_status => l_return_status,
280 x_msg_count => l_msg_count,
281 x_msg_data => l_msg_data,
282 p_trohdr_rec => l_mohdr_rec,
283 p_trohdr_val_rec => l_mohdr_val_rec,
284 x_trohdr_rec => l_mohdr_rec,
285 x_trohdr_val_rec => l_mohdr_val_rec
286 );
287
288 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
289 RAISE FND_API.G_EXC_ERROR;
290 ELSE
291 /* call table handlers for inserting into csp_move_order_header table*/
292 csp_to_form_moheaders.Validate_and_Write(
293 P_Api_Version_Number => 1.0
294 ,P_Init_Msg_List => p_init_msg_list
295 ,P_Commit => l_commit
296 ,p_validation_level => null
297 ,p_action_code => 0 -- 0 = insert, 1 = update, 2 = delete
298 ,p_header_id => l_mohdr_rec.header_id
299 ,p_created_by => nvl(fnd_global.user_id,1)
300 ,p_creation_date => sysdate
301 ,p_last_updated_by => nvl(fnd_global.user_id,1)
302 ,p_last_update_date => sysdate
303 ,p_last_update_login => nvl(fnd_global.login_id,-1)
304 ,p_carrier => p_freight_carrier
305 ,p_shipment_method => p_shipment_method
306 ,p_autoreceipt_flag => p_autoreceipt_flag
307 ,p_attribute_category => null
308 ,p_attribute1 => null
309 ,p_attribute2 => null
310 ,p_attribute3 => null
311 ,p_attribute4 => null
312 ,p_attribute5 => null
313 ,p_attribute6 => null
314 ,p_attribute7 => null
315 ,p_attribute8 => null
316 ,p_attribute9 => null
317 ,p_attribute10 => null
318 ,p_attribute11 => null
319 ,p_attribute12 => null
320 ,p_attribute13 => null
321 ,p_attribute14 => null
322 ,p_attribute15 => null
323 ,p_location_id => null
324 /*,p_address1 => p_address1
325 ,p_address2 => p_address2
326 ,p_address3 => p_address3
327 ,p_address4 => p_address4
328 ,p_city => p_city
329 ,p_postal_code => p_postal_code
330 ,p_state => p_state
331 ,p_province => p_province
332 ,p_country => p_country */
333 ,X_Return_Status => l_return_status
334 ,X_Msg_Count => l_msg_count
335 ,X_Msg_Data => l_msg_data
336 );
337
338 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
339 RAISE FND_API.G_EXC_ERROR;
340 END IF;
341
342 px_header_id := l_mohdr_rec.header_id;
343 END IF;
344
345 IF fnd_api.to_boolean(p_commit) THEN
346 commit work;
347 END IF;
348
349 fnd_msg_pub.count_and_get
350 ( p_count => x_msg_count
351 , p_data => x_msg_data);
352 /* Exception Block */
353 EXCEPTION
354 WHEN EXCP_USER_DEFINED THEN
355 Rollback to Create_Move_Order_Header_PUB;
356 x_return_status := FND_API.G_RET_STS_ERROR;
357 fnd_msg_pub.count_and_get
358 ( p_count => x_msg_count
359 , p_data => x_msg_data);
360 WHEN FND_API.G_EXC_ERROR THEN
361 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
362 P_API_NAME => L_API_NAME
363 ,P_PKG_NAME => G_PKG_NAME
364 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
365 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
366 ,X_MSG_COUNT => X_MSG_COUNT
367 ,X_MSG_DATA => X_MSG_DATA
368 ,X_RETURN_STATUS => X_RETURN_STATUS);
369 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
370 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
371 P_API_NAME => L_API_NAME
372 ,P_PKG_NAME => G_PKG_NAME
373 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
374 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
375 ,X_MSG_COUNT => X_MSG_COUNT
376 ,X_MSG_DATA => X_MSG_DATA
377 ,X_RETURN_STATUS => X_RETURN_STATUS);
378 WHEN OTHERS THEN
379 Rollback to Create_Move_Order_Header_PUB;
380 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
381 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
382 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
383 FND_MSG_PUB.ADD;
384 fnd_msg_pub.count_and_get
385 ( p_count => x_msg_count
386 , p_data => x_msg_data);
387 x_return_status := FND_API.G_RET_STS_ERROR;
388
389 END CREATE_MOVE_ORDER_HEADER;
390
391
392 -- Start of comments
393 --
394 -- Procedure Name : create_move_order_lines
395 -- Descritpion : Creates move order lines
396 -- Business Rules :
397 -- Parameters :
398 -- Version : 1.0
399 -- End of comments
400
401 PROCEDURE CREATE_MOVE_ORDER_LINE
402 (p_api_version IN NUMBER
403 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
404 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
405 ,px_line_id IN OUT NOCOPY NUMBER
406 ,p_header_id IN NUMBER
407 ,p_organization_id IN NUMBER
408 ,p_from_subinventory_code IN VARCHAR2
409 ,p_from_locator_id IN NUMBER
410 ,p_inventory_item_id IN NUMBER
411 ,p_revision IN VARCHAR2
412 ,p_lot_number IN VARCHAR2
413 ,p_serial_number_start IN VARCHAR2
414 ,p_serial_number_end IN VARCHAR2
415 ,p_quantity IN NUMBER
416 ,p_uom_code IN VARCHAR2
417 ,p_quantity_delivered IN NUMBER
418 ,p_to_subinventory_code IN VARCHAR2
419 ,p_to_locator_id IN VARCHAR2
420 ,p_to_organization_id IN NUMBER
421 ,p_service_request IN VARCHAR2
422 ,p_task_id IN NUMBER
423 ,p_task_assignment_id IN NUMBER
424 ,p_customer_po IN VARCHAR2
425 ,p_date_required IN DATE
426 ,p_comments IN VARCHAR2
427 ,x_return_status OUT NOCOPY VARCHAR2
428 ,x_msg_count OUT NOCOPY NUMBER
429 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
430
431 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
432 l_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
433
434 l_api_version_number CONSTANT NUMBER := 1.0;
435 l_api_name CONSTANT VARCHAR2(30) := 'Create_Move_Order_Line';
436 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
437 l_msg_count NUMBER;
438 l_msg_data VARCHAR2(240);
439 l_commit VARCHAR2(1) := FND_API.G_FALSE;
440 EXCP_USER_DEFINED EXCEPTION;
441
442 l_line_num NUMBER := 0;
443 l_line_id NUMBER;
444 l_order_count NUMBER := 1; /* total number of lines */
445
446 BEGIN
447 SAVEPOINT Create_Move_Order_Line_PUB;
448
449 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
450 -- initialize message list
451 FND_MSG_PUB.initialize;
452 END IF;
453
454 -- Standard call to check for call compatibility.
455 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
456 p_api_version,
457 l_api_name,
458 G_PKG_NAME)
459 THEN
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 END IF;
462
463 -- Initialize return_status
464 x_return_status := FND_API.G_RET_STS_SUCCESS;
465
466 -- check organization
467 IF p_organization_id IS NULL THEN
468 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
469 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
470 FND_MSG_PUB.ADD;
471 RAISE EXCP_USER_DEFINED;
472 END IF;
473
474 IF p_header_id IS NULL THEN
475 FND_MESSAGE.SET_NAME('CSP', 'CSP_MISSING_PARAMETERS');
476 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_header_id', TRUE);
477 FND_MSG_PUB.ADD;
478 RAISE EXCP_USER_DEFINED;
479 END IF;
480
481 IF (px_line_id IS NOT NULL) THEN
482 BEGIN
483 SELECT line_id
484 INTO l_line_id
485 FROM mtl_txn_request_lines
486 WHERE line_id = px_line_id
487 AND organization_id = p_organization_id;
488
489 FND_MESSAGE.SET_NAME('CSP', 'CSP_PARAMETER_EXISTS');
490 FND_MESSAGE.SET_TOKEN('PARAMETER', 'px_line_id' , TRUE);
491 FND_MSG_PUB.ADD;
492 RAISE EXCP_USER_DEFINED;
493
494 EXCEPTION
495 WHEN no_data_found THEN
496 -- valid id
497 NULL;
498 END;
499 END IF;
500
501 select nvl(max(line_number), 0)
502 into l_line_num
503 from mtl_txn_request_lines
504 where header_id = p_header_id;
505
506 l_line_num := l_line_num + 1;
507 l_trolin_tbl(l_order_count).header_id := p_header_id;
508 l_trolin_tbl(l_order_count).created_by := nvl(FND_GLOBAL.USER_ID,1);
509 l_trolin_tbl(l_order_count).creation_date := sysdate;
510 l_trolin_tbl(l_order_count).date_required := p_date_required;
511 l_trolin_tbl(l_order_count).from_subinventory_code:= p_from_subinventory_code;
512 l_trolin_tbl(l_order_count).from_locator_id := p_from_locator_id;
513 l_trolin_tbl(l_order_count).inventory_item_id := p_inventory_item_id;
514 l_trolin_tbl(l_order_count).revision := p_revision;
515 l_trolin_tbl(l_order_count).lot_number := p_lot_number;
516 l_trolin_tbl(l_order_count).serial_number_start := p_serial_number_start;
517 l_trolin_tbl(l_order_count).serial_number_end := p_serial_number_end;
518 l_trolin_tbl(l_order_count).last_updated_by := nvl(FND_GLOBAL.USER_ID,1);
519 l_trolin_tbl(l_order_count).last_update_date := sysdate;
520 l_trolin_tbl(l_order_count).last_update_login := nvl(FND_GLOBAL.LOGIN_ID, -1);
521 l_trolin_tbl(l_order_count).line_id := nvl(px_line_id,FND_API.G_MISS_NUM);
522 l_trolin_tbl(l_order_count).line_number := l_line_num;
523 l_trolin_tbl(l_order_count).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
524 l_trolin_tbl(l_order_count).transaction_type_id := INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR;
525 l_trolin_tbl(l_order_count).organization_id := p_organization_id;
526 l_trolin_tbl(l_order_count).to_organization_id := p_to_organization_id;
527 l_trolin_tbl(l_order_count).quantity := p_quantity;
528 l_trolin_tbl(l_order_count).quantity_delivered := p_quantity_delivered;
529 l_trolin_tbl(l_order_count).status_date := sysdate;
530 l_trolin_tbl(l_order_count).to_subinventory_code := p_to_subinventory_code;
531 l_trolin_tbl(l_order_count).to_locator_id := p_to_locator_id;
532 l_trolin_tbl(l_order_count).uom_code := p_uom_code;
533 l_trolin_tbl(l_order_count).db_flag := FND_API.G_TRUE;
534 l_trolin_tbl(l_order_count).operation := INV_GLOBALS.G_OPR_CREATE;
535
536 INV_Move_Order_Pub.Create_Move_Order_Lines
537 ( p_api_version_number => 1.0 ,
538 p_init_msg_list => p_init_msg_list,
539 p_commit => l_commit,
540 p_return_values => FND_API.G_TRUE,
541 x_return_status => l_return_status,
542 x_msg_count => l_msg_count,
543 x_msg_data => l_msg_data,
544 p_trolin_tbl => l_trolin_tbl,
545 p_trolin_val_tbl => l_trolin_val_tbl,
546 x_trolin_tbl => l_trolin_tbl,
547 x_trolin_val_tbl => l_trolin_val_tbl
548 );
549
550 px_line_id := l_trolin_tbl(l_order_count).line_id;
551
552 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
553 RAISE FND_API.G_EXC_ERROR;
554 ELSE
555 /* call table handlers for inserting into csp_move_order_lines table*/
556 csp_to_form_molines.Validate_and_Write(
557 P_Api_Version_Number => 1.0
558 ,P_Init_Msg_List => p_init_msg_list
559 ,P_Commit => l_commit
560 ,p_validation_level => null
561 ,p_action_code => 0
562 ,P_line_id => l_trolin_tbl(l_order_count).line_id
563 ,p_CREATED_BY => nvl(fnd_global.user_id,1)
564 ,p_CREATION_DATE => sysdate
565 ,p_LAST_UPDATED_BY => nvl(fnd_global.user_id,1)
566 ,p_LAST_UPDATE_DATE => sysdate
567 ,p_LAST_UPDATED_LOGIN => nvl(fnd_global.login_id,-1)
568 ,p_HEADER_ID => p_header_id
569 ,p_CUSTOMER_PO => p_customer_po
570 ,p_INCIDENT_ID => p_service_request
571 ,p_TASK_ID => p_task_id
572 ,p_TASK_ASSIGNMENT_ID => p_task_assignment_id
573 ,p_COMMENTS => p_comments
574 ,p_attribute_category => null
575 ,p_attribute1 => null
576 ,p_attribute2 => null
577 ,p_attribute3 => null
578 ,p_attribute4 => null
579 ,p_attribute5 => null
580 ,p_attribute6 => null
581 ,p_attribute7 => null
582 ,p_attribute8 => null
583 ,p_attribute9 => null
584 ,p_attribute10 => null
585 ,p_attribute11 => null
586 ,p_attribute12 => null
587 ,p_attribute13 => null
588 ,p_attribute14 => null
589 ,p_attribute15 => null
590 ,X_Return_Status => l_return_status
591 ,X_Msg_Count => l_msg_count
592 ,X_Msg_Data => l_msg_data
593 );
594
595 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
596 RAISE FND_API.G_EXC_ERROR;
597 END IF;
598
599 px_line_id := l_trolin_tbl(l_order_count).line_id;
600 END IF;
601
602 IF fnd_api.to_boolean(p_commit) THEN
603 commit work;
604 END IF;
605 fnd_msg_pub.count_and_get
606 ( p_count => x_msg_count
607 , p_data => x_msg_data);
608 /* Exception Block */
609 EXCEPTION
610 WHEN EXCP_USER_DEFINED THEN
611 Rollback to Create_Move_Order_Line_PUB;
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 fnd_msg_pub.count_and_get
614 ( p_count => x_msg_count
615 , p_data => x_msg_data);
616 WHEN FND_API.G_EXC_ERROR THEN
617 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
618 P_API_NAME => L_API_NAME
619 ,P_PKG_NAME => G_PKG_NAME
620 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
621 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
622 ,X_MSG_COUNT => X_MSG_COUNT
623 ,X_MSG_DATA => X_MSG_DATA
624 ,X_RETURN_STATUS => X_RETURN_STATUS);
625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
626 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
627 P_API_NAME => L_API_NAME
628 ,P_PKG_NAME => G_PKG_NAME
629 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
630 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
631 ,X_MSG_COUNT => X_MSG_COUNT
632 ,X_MSG_DATA => X_MSG_DATA
633 ,X_RETURN_STATUS => X_RETURN_STATUS);
634 WHEN OTHERS THEN
635 Rollback to Create_Move_Order_Line_PUB;
636 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
637 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
638 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
639 FND_MSG_PUB.ADD;
640 fnd_msg_pub.count_and_get
641 ( p_count => x_msg_count
642 , p_data => x_msg_data);
643 x_return_status := FND_API.G_RET_STS_ERROR;
644
645
646 END CREATE_MOVE_ORDER_LINE;
647
648
649 -- Start of comments
650 --
651 -- Procedure Name : transact_material
652 -- Descritpion : Creates a material transaction in Inventory
653 -- Business Rules :
654 -- Parameters :
655 -- Version : 1.0
656 -- Change History : H Haugerud Added support for Intransit and Direct trans
657 -- End of comments
658
659 PROCEDURE TRANSACT_MATERIAL
660 (p_api_version IN NUMBER
661 ,p_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE
662 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
663 ,px_transaction_id IN OUT NOCOPY NUMBER
664 ,px_transaction_header_id IN OUT NOCOPY NUMBER
665 ,p_inventory_item_id IN NUMBER
666 ,p_organization_id IN NUMBER
667 ,p_subinventory_code IN VARCHAR2
668 ,p_locator_id IN NUMBER
669 ,p_lot_number IN VARCHAR2
670 ,p_lot_expiration_date IN DATE
671 ,p_revision IN VARCHAR2
672 ,p_serial_number IN VARCHAR2 -- from serial number
673 ,p_to_serial_number IN VARCHAR2 := NULL
674 ,p_quantity IN NUMBER
675 ,p_uom IN VARCHAR2
676 ,p_source_id IN VARCHAR2
677 ,p_source_line_id IN NUMBER
678 ,p_transaction_type_id IN NUMBER
679 ,p_account_id IN NUMBER
680 ,p_transfer_to_subinventory IN VARCHAR2
681 ,p_transfer_to_locator IN NUMBER
682 ,p_transfer_to_organization IN NUMBER
683 ,p_online_process_flag IN BOOLEAN := TRUE
684 ,p_transaction_source_id IN NUMBER -- added by klou 03/30/20000
685 ,p_trx_source_line_id IN NUMBER -- added by klou 03/30/20000
686 ,p_transaction_source_name IN VARCHAR2
687 ,p_waybill_airbill IN VARCHAR2
688 ,p_shipment_number IN VARCHAR2
689 ,p_freight_code IN VARCHAR2
690 ,p_reason_id IN NUMBER
691 ,p_transaction_reference IN VARCHAR2
692 ,p_transaction_date IN DATE
693 ,p_expected_delivery_date IN DATE DEFAULT NULL
694 ,p_FINAL_COMPLETION_FLAG IN VARCHAR2 DEFAULT NULL
695 ,x_return_status OUT NOCOPY VARCHAR2
696 ,x_msg_count OUT NOCOPY NUMBER
697 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
698
699 l_transaction_action_id mtl_transaction_types.transaction_action_id%TYPE;
700 l_transaction_source_type_id mtl_transaction_types.transaction_source_type_id%TYPE;
701 l_transaction_header_id mtl_transactions_interface.transaction_header_id%TYPE;
702 l_transaction_interface_id mtl_transactions_interface.transaction_interface_id%TYPE;
703 l_acct_period_id org_acct_periods.acct_period_id%TYPE;
704 l_transaction_date date;
705 l_quantity NUMBER;
706 l_account_id NUMBER;
707 l_code_comb_id NUMBER;
708 l_subinv_type NUMBER;
709 l_inv_asset_flag VARCHAR2(1);
710
711 l_msg_count NUMBER;
712 l_msg_data VARCHAR2(240);
713 l_return_status VARCHAR2(1);
714
715 l_timeout NUMBER;
716 l_outcome BOOLEAN := TRUE;
717 l_error_code VARCHAR2(200);
718 l_error_explanation VARCHAR2(240);
719
720 l_api_version_number CONSTANT NUMBER := 1.0;
721 l_api_name CONSTANT VARCHAR2(20) := 'Transact_Material';
722 l_check_existence NUMBER := 0;
723 l_organization_id NUMBER;
724
725 l_prev_resp_id NUMBER;
726 l_prev_resp_appl_id NUMBER;
727 l_prev_user_id NUMBER;
728 l_resp_id NUMBER;
729 l_resp_appl_id NUMBER;
730 l_resp_set_flag BOOLEAN := FALSE;
731
732 p_org_id NUMBER;
733
734 EXCP_USER_DEFINED EXCEPTION;
735
736 l_lot_number_val VARCHAR2(80);
737 ln_count NUMBER;
738 p_lot_number_temp VARCHAR2(80);
739 --l_ship_number Varchar2(30);
740
741 l_process_flag number;
742 l_transaction_source_id number;
743 l_wip_entity_type number;
744 l_FINAL_COMPLETION_FLAG Varchar2(5);
745
746
747 l_src_rsc_type csp_sec_inventories.owner_resource_type%TYPE;
748 l_src_rsc_type_converted csp_sec_inventories.owner_resource_type%TYPE;
749 l_src_rsc_id csp_sec_inventories.owner_resource_id%TYPE;
750 l_src_username jtf_rs_resource_extns.user_name%TYPE;
751 l_dest_rsc_type csp_sec_inventories.owner_resource_type%TYPE;
752 l_dest_rsc_type_converted csp_sec_inventories.owner_resource_type%TYPE;
753 l_dest_rsc_id csp_sec_inventories.owner_resource_id%TYPE;
754 l_dest_username jtf_rs_resource_extns.user_name%TYPE;
755 l_item_code MTL_SYSTEM_ITEMS_B_KFV.concatenated_segments%TYPE;
756 l_source_org_code mtl_organizations.organization_code%TYPE;
757 l_dest_org_code mtl_organizations.organization_code%TYPE;
758 l_dest_source_name jtf_rs_resource_extns.source_name%TYPE;
759 l_src_source_name jtf_rs_resource_extns.source_name%TYPE;
760 l_uom_desc mtl_item_uoms_view.description%TYPE;
761 itemtype varchar2(20);
762
763
764 CURSOR c_source_owner_dtls IS
765 SELECT owner_resource_type, owner_resource_id
766 FROM csp_sec_inventories WHERE
767 organization_id = p_organization_id
768 AND secondary_inventory_name = p_subinventory_code;
769
770
771 CURSOR c_dest_owner_dtls IS
772 SELECT owner_resource_type, owner_resource_id
773 FROM csp_sec_inventories
774 WHERE organization_id = p_transfer_to_organization
775 AND secondary_inventory_name = p_transfer_to_subinventory;
776
777 CURSOR c_source_user_name IS
778 SELECT user_name, source_name FROM jtf_rs_resource_extns
779 WHERE resource_id = l_src_rsc_id and category = l_src_rsc_type_converted;
780
781 CURSOR c_dest_user_name IS
782 SELECT user_name, source_name FROM jtf_rs_resource_extns
783 WHERE resource_id = l_dest_rsc_id and category = l_dest_rsc_type_converted;
784
785 CURSOR c_item_code IS
786 SELECT concatenated_segments FROM MTL_SYSTEM_ITEMS_B_KFV
787 WHERE inventory_item_id=p_inventory_item_id and organization_id=p_organization_id;
788
789 CURSOR c_source_org_code IS
790 SELECT organization_code FROM mtl_organizations
791 WHERE Organization_id = p_organization_id;
792
793 CURSOR c_dest_org_code IS
794 SELECT organization_code FROM mtl_organizations
795 WHERE Organization_id = p_transfer_to_organization;
796
797 CURSOR c_uom_desc IS
798 SELECT description FROM mtl_item_uoms_view
799 WHERE Organization_id = p_organization_id
800 AND Inventory_item_id = p_inventory_item_id AND uom_code = p_uom;
801
802 CURSOR l_transaction_header_id_csr IS
803 SELECT mtl_material_transactions_s.nextval
804 FROM dual;
805
806
807 CURSOR l_acct_period_csr is
808 SELECT acct_period_id,
809 least(sysdate,
810 decode(sign(trunc(period_start_date)-(trunc(p_transaction_date))),
811 1,sysdate,p_transaction_date)) transaction_date
812 FROM org_acct_periods
813 WHERE (trunc(p_transaction_date)
814 between trunc(period_start_date)
815 and trunc(schedule_close_date)
816 OR trunc(sysdate)
817 between trunc(period_start_date)
818 and trunc(schedule_close_date))
819 AND organization_id = p_organization_id
820 AND period_close_date is null
821 AND nvl(open_flag,'Y') = 'Y'
822 ORDER BY period_start_date asc;
823
824 CURSOR l_resp_csr IS
825 SELECT application_id,
826 responsibility_id
827 FROM fnd_responsibility
828 WHERE responsibility_key = 'SPARES_MANAGEMENT';
829
830 CURSOR l_cost_of_acct(p_org_id Number,p_item_id Number) IS
831 SELECT cost_of_sales_account,inventory_asset_flag
832 FROM mtl_system_items_b
833 WHERE organization_id = p_org_id
834 AND inventory_item_id = p_item_id;
835
836 CURSOR l_subinv(p_org_id NUMBER,p_subinv VARCHAR2) IS
837 SELECT asset_inventory
838 FROM mtl_secondary_inventories
839 WHERE organization_id = p_org_id
840 AND secondary_inventory_name = p_subinv;
841
842 CURSOR transaction_id_cur IS
843 select transaction_id
844 from mtl_material_transactions
845 where transaction_set_id = l_transaction_header_id;
846
847 --- added the following local varibales for bug 3608969
848 l_retval number;
849 l_msg_cnt number;
850 l_trans_count number;
851 --------------------------------------------------------
852
853 BEGIN
854 Savepoint Transact_Material_PUB;
855
856 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
857 -- initialize message list
858 FND_MSG_PUB.initialize;
859 END IF;
860
861 -- Standard call to check for call compatibility.
862 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
863 p_api_version,
864 l_api_name,
865 G_PKG_NAME)
866 THEN
867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
868 END IF;
869
870 -- validating transaction_type_id
871 IF p_transaction_type_id IS NULL THEN
872 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
873 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_transaction_type_id', TRUE);
874 FND_MSG_PUB.ADD;
875 RAISE EXCP_USER_DEFINED;
876 END IF;
877
878 -- validating organization
879 IF p_organization_id IS NULL THEN
880 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
881 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_organization_id', TRUE);
882 FND_MSG_PUB.ADD;
883 RAISE EXCP_USER_DEFINED;
884 END IF;
885
886 -- Check that item is an inventory_item, stockable, transactable and reservable
887 --
888 IF p_inventory_item_id IS NULL THEN
889 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
890 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_inventory_item_id ', TRUE);
891 FND_MSG_PUB.ADD;
892 RAISE EXCP_USER_DEFINED;
893 ELSE
894 BEGIN
895 -- validate whether the inventory_item_id exists in the given oranization_id
896 select inventory_item_id into l_check_existence
897 from mtl_system_items_kfv
898 where inventory_item_id = p_inventory_item_id
899 and organization_id = p_organization_id;
900 EXCEPTION
901 WHEN NO_DATA_FOUND THEN
902 fnd_message.set_name('INV', 'INV-NO ITEM RECROD');
903 fnd_msg_pub.add;
904 RAISE EXCP_USER_DEFINED;
905 WHEN OTHERS THEN
906 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
907 fnd_message.set_token('ERR_FIELD', 'p_inventory_item_id', TRUE);
908 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
909 fnd_message.set_token('TABLE', 'mtl_system_items', TRUE);
910 FND_MSG_PUB.ADD;
911 RAISE EXCP_USER_DEFINED;
912 END;
913 END IF;
914
915 --validating transaction_quantity
916 IF p_quantity IS NULL OR p_quantity < 0 THEN
917 fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
918 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_quantity', TRUE);
919 fnd_msg_pub.add;
920 RAISE EXCP_USER_DEFINED;
921 END IF;
922
923 --validating transaction_uom
924 IF p_uom IS NULL THEN
925 fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
926 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_uom', TRUE);
927 fnd_msg_pub.add;
928 RAISE EXCP_USER_DEFINED;
929 END IF;
930
931 -- Validating Account ID
932 IF p_account_id is not NULL THEN
933 BEGIN
934
935 SELECT TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
936 SUBSTRB(USERENV('CLIENT_INFO'),1,10)))
937 INTO p_org_id
938 from dual;
939
940 SElECT gcc.code_combination_id
941 INTO l_code_comb_id
942 FROM hr_operating_units hou,gl_sets_of_books gsob,
943 gl_code_combinations gcc
944 -- WHERE hou.organization_id = p_organization_id
945 WHERE hou.organization_id = p_org_id
946 AND hou.set_of_books_id = gsob.set_of_books_id
947 AND gsob.chart_of_accounts_id = gcc.chart_of_accounts_id
948 AND gcc.code_combination_id = p_account_id;
949
950 EXCEPTION
951 WHEN NO_DATA_FOUND THEN
952 fnd_message.set_name('CSP', 'CSP_INVALID_ACCOUNT');
953 fnd_msg_pub.add;
954 RAISE EXCP_USER_DEFINED;
955 WHEN OTHERS THEN
956 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
957 fnd_message.set_token('ERR_FIELD', 'p_account_id', TRUE);
958 fnd_message.set_token('ROUTINE', l_api_name, TRUE);
959 fnd_message.set_token('TABLE', 'GL_code_combinations', TRUE);
960 FND_MSG_PUB.ADD;
961 RAISE EXCP_USER_DEFINED;
962 END;
963 END IF;
964
965
966 /* Removed the vaalidation of subinventory code by klou.
967 Subinvnetory code is a NULL column in the interface table.
968 --validating subinventory_code
969 IF p_subinventory_code IS NULL THEN
970 fnd_message.set_name ('CSP', 'CSP_MISSING_PARAMETERS');
971 FND_MESSAGE.SET_TOKEN('PARAMETER', 'p_subinventory_code', TRUE);
972 fnd_msg_pub.add;
973 RAISE EXCP_USER_DEFINED;
974 END IF;
975 */
976
977 IF (px_transaction_header_id IS NULL) THEN
978 OPEN l_transaction_header_id_csr;
979 FETCH l_transaction_header_id_csr into l_transaction_header_id;
980 CLOSE l_transaction_header_id_csr;
981 px_transaction_header_id := l_transaction_header_id;
982 ELSE
983 l_transaction_header_id := px_transaction_header_id;
984 END IF;
985
986 OPEN l_transaction_header_id_csr;
987 FETCH l_transaction_header_id_csr into l_transaction_interface_id;
988 CLOSE l_transaction_header_id_csr;
989
990 OPEN l_acct_period_csr;
991 FETCH l_acct_period_csr into l_acct_period_id, l_transaction_date;
992 CLOSE l_acct_period_csr;
993
994 IF l_acct_period_id is null THEN
995 x_msg_data := 'Cannot find open accounting period';
996 x_return_status := FND_API.G_RET_STS_ERROR;
997 END IF;
998
999 IF p_transaction_type_id in (21,32,02,01,93,35) THEN
1000 l_quantity := p_quantity * (-1);
1001 ELSE
1002 l_quantity := p_quantity;
1003 END IF;
1004 l_account_id := p_account_id;
1005 IF p_account_id is NULL THEN
1006 IF p_transaction_type_id in(32,93) THEN -- Issuing transaction
1007 OPEN l_cost_of_acct(p_organization_id,p_inventory_item_id);
1008 FETCH l_cost_of_acct INTO l_account_id,l_inv_asset_flag;
1009 CLOSE l_cost_of_acct;
1010 ELSIF p_transaction_type_id in (42,94) THEN --- Receiving Transaction
1011 OPEN l_subinv(p_organization_id,p_subinventory_code);
1012 FETCH l_subinv INTO l_subinv_type;
1013 CLOSE l_subinv;
1014 IF l_subinv_type = 1 THEN --- Asset Subinventory
1015 OPEN l_cost_of_acct(p_organization_id,p_inventory_item_id);
1016 FETCH l_cost_of_acct INTO l_account_id,l_inv_asset_flag;
1017 CLOSE l_cost_of_acct;
1018 ELSIF l_subinv_type = 2 THEN -- Expense Subinventory
1019 l_account_id := NULL;
1020 END IF; -- End if for subinv type
1021 END IF; -- End if for transaction type
1022 END IF; -- End if for account id is NULL
1023
1024 -- bug # 6472464
1025 -- removed validation of duplicate shipment number
1026 -- this will be checked in wireless code
1027 /*
1028 If p_shipment_number is not null then
1029 Begin
1030 SELECT SHIPMENT_NUMBER INTO l_ship_number
1031 FROM MTL_MATERIAL_TRANSACTIONS_TEMP M
1032 WHERE M.SHIPMENT_NUMBER = p_shipment_number AND ROWNUM = 1;
1033 Exception
1034 When no_data_found then
1035 l_ship_number := Null;
1036 End;
1037
1038 If l_ship_number is Not Null then
1039 fnd_message.set_name('INV','INV_SHIP_USED');
1040 fnd_msg_pub.add;
1041 RAISE EXCP_USER_DEFINED;
1042 End if;
1043
1044 Begin
1045 SELECT SHIPMENT_NUM INTO l_ship_number
1046 FROM RCV_SHIPMENT_HEADERS M
1047 WHERE M.SHIPMENT_NUM = p_shipment_number AND ROWNUM = 1;
1048 Exception
1049 When no_data_found then
1050 l_ship_number := Null;
1051 End;
1052
1053 If l_ship_number is Not Null then
1054 fnd_message.set_name('INV','INV_SHIP_USED');
1055 fnd_msg_pub.add;
1056 RAISE EXCP_USER_DEFINED;
1057 End if;
1058
1059 Begin
1060 SELECT SHIPMENT_NUMBER INTO l_ship_number
1061 FROM MTL_TRANSACTIONS_INTERFACE M
1062 WHERE M.SHIPMENT_NUMBER = p_shipment_number AND ROWNUM = 1;
1063 Exception
1064 When no_data_found then
1065 l_ship_number := Null;
1066 End;
1067
1068 If l_ship_number is Not Null then
1069 fnd_message.set_name('INV','INV_SHIP_USED');
1070 fnd_msg_pub.add;
1071 RAISE EXCP_USER_DEFINED;
1072 End if;
1073
1074
1075 End if;
1076 */
1077
1078 If INSTR(p_transaction_source_name,'REPAIR_PO_WIP') > 0 then
1079 l_WIP_ENTITY_TYPE := 1;
1080 If p_transaction_type_id = 43 or p_transaction_type_id = 44 then
1081 l_FINAL_COMPLETION_FLAG := p_FINAL_COMPLETION_FLAG;
1082 Else
1083 l_FINAL_COMPLETION_FLAG := NULL;
1084 End if;
1085 Else
1086 l_WIP_ENTITY_TYPE := NULL;
1087 l_FINAL_COMPLETION_FLAG := NULL;
1088 End if;
1089
1090 INSERT INTO mtl_transactions_interface
1091 ( source_code
1092 , source_header_id
1093 , source_line_id
1094 , process_flag
1095 , transaction_mode
1096 , transaction_header_id
1097 , transaction_interface_id
1098 , inventory_item_id
1099 , revision
1100 , organization_id
1101 , subinventory_code
1102 , locator_id
1103 , transaction_quantity
1104 , transaction_uom
1105 , transaction_date
1106 , acct_period_id
1107 , distribution_account_id
1108 , transaction_source_name
1109 , transaction_type_id
1110 , transfer_subinventory
1111 , transfer_locator
1112 , transfer_organization
1113 , last_update_date
1114 , last_updated_by
1115 , creation_date
1116 , created_by
1117 , last_update_login
1118 , lock_flag --always set to 2 so that the transaction_manager will pick the record and assign it to the transaction_worker.
1119 , transaction_source_id
1120 , trx_source_line_id
1121 , waybill_airbill
1122 , shipment_number
1123 , freight_code
1124 , reason_id
1125 , transaction_reference
1126 , expected_arrival_date
1127 , WIP_ENTITY_TYPE
1128 , FINAL_COMPLETION_FLAG
1129 )
1130 VALUES
1131 ( nvl(p_source_id,'CSP')
1132 , 100 -- source header id
1133 , nvl(p_source_line_id,1)
1134 , 1 --process_flag yes
1135 , 2 --transaction_mode online
1136 , l_transaction_header_id
1137 , l_transaction_interface_id
1138 , p_inventory_item_id
1139 , p_revision
1140 , p_organization_id
1141 , p_subinventory_code
1142 , p_locator_id
1143 , l_quantity
1144 , p_uom
1145 , l_transaction_date --transaction_date
1146 , l_acct_period_id
1147 , l_account_id
1148 , p_transaction_source_name
1149 , p_transaction_type_id
1150 , p_transfer_to_subinventory
1151 , p_transfer_to_locator
1152 , p_transfer_to_organization
1153 , sysdate --last_update_date
1154 , nvl(fnd_global.user_id,1) --last_updated_by
1155 , sysdate --creation_date
1156 , nvl(fnd_global.user_id,1) --created_by
1157 , nvl(fnd_global.login_id,-1)
1158 , 2
1159 , decode(sign(p_transaction_source_id-1000000000000),-1,
1160 p_transaction_source_id,null)
1161 , decode(sign(greatest(p_transaction_source_id,p_trx_source_line_id)
1162 -1000000000000),-1,p_trx_source_line_id,null)
1163 , p_waybill_airbill
1164 , p_shipment_number
1165 , p_freight_code
1166 , p_reason_id
1167 , p_transaction_reference
1168 , p_expected_delivery_date
1169 , l_WIP_ENTITY_TYPE
1170 , l_FINAL_COMPLETION_FLAG
1171 );
1172
1173 /*
1174 COMMIT;
1175 select process_flag,transaction_source_id,wip_entity_type
1176 into l_process_flag,l_transaction_source_id,l_wip_entity_type
1177 from mtl_transactions_interface
1178 where transaction_header_id = l_transaction_header_id
1179 and transaction_interface_id = l_transaction_interface_id;
1180
1181 dbms_output.put_line('wip_entity_type '||l_wip_entity_type||' Process_flag '||l_process_flag||' transaction_source_id ' || l_transaction_source_id);
1182 */
1183
1184 p_lot_number_temp := p_lot_number;
1185
1186 /*
1187 select count(*)
1188 into ln_count
1189 from mtl_system_items
1190 where inventory_item_id = p_inventory_item_id
1191 and serial_number_control_code <> 1
1192 and lot_control_code = 2
1193 and organization_id = p_organization_id;
1194
1195 If ln_count > 0 and p_lot_number is null then
1196 */
1197
1198 If p_serial_number is not null and p_lot_number is null then
1199 Begin
1200 Select lot_number
1201 into l_lot_number_val
1202 From MTL_SERIAL_NUMBERS_VAL_V
1203 Where current_organization_id = p_organization_id
1204 and current_subinventory_code = p_subinventory_code
1205 and inventory_item_id = p_inventory_item_id
1206 and serial_number = p_serial_number
1207 and lot_number is not null;
1208 Exception
1209 When no_data_found then
1210 l_lot_number_val := null;
1211 End;
1212
1213 If l_lot_number_val is not null then
1214 p_lot_number_temp := l_lot_number_val;
1215 end if;
1216 End if;
1217
1218 -- End if;
1219
1220
1221
1222 IF p_lot_number_temp IS NOT NULL THEN
1223 INSERT INTO mtl_transaction_lots_interface
1224 ( transaction_interface_id
1225 , lot_number
1226 , lot_expiration_date
1227 , transaction_quantity
1228 , serial_transaction_temp_id
1229 , last_update_date
1230 , last_updated_by
1231 , creation_date
1232 , created_by
1233 , last_update_login
1234 )
1235 VALUES
1236 ( l_transaction_interface_id
1237 , p_lot_number_temp
1238 , p_lot_expiration_date
1239 , p_quantity
1240 , l_transaction_interface_id -- We will only have 1 serial number at a time
1241 , sysdate
1242 , nvl(fnd_global.user_id,-1)
1243 , sysdate
1244 , nvl(fnd_global.user_id,-1)
1245 , nvl(fnd_global.login_id,-1)
1246 );
1247 END IF;
1248
1249 IF p_serial_number IS NOT NULL THEN
1250 INSERT INTO mtl_serial_numbers_interface
1251 ( transaction_interface_id
1252 , fm_serial_number
1253 , to_serial_number
1254 , last_update_date
1255 , last_updated_by
1256 , creation_date
1257 , created_by
1258 , last_update_login)
1259 VALUES
1260 ( l_transaction_interface_id
1261 , p_serial_number
1262 , nvl(p_to_serial_number, p_serial_number)
1263 , sysdate --last_update_date
1264 , nvl(fnd_global.user_id,1) --last_updated_by
1265 , sysdate --creation_date
1266 , nvl(fnd_global.user_id,1) --created_by
1267 , nvl(fnd_global.login_id,-1) --last_update_login
1268 );
1269 END IF;
1270
1271 /*l_transaction_header_id := 2034265;*/
1272 --dbms_output.put_line('transaction_header_id ' || l_transaction_header_id);
1273
1274 -- If online_prcoess_flag is true then
1275 -- Call Inventory API for processing transactions in mtl_transactions_interface table
1276 --dbms_output.put_line('transact_material: after calling the apps_initialized');
1277
1278 IF (p_online_process_flag) THEN
1279
1280 --check to see if we need to do intiialization of responsibility
1281 l_prev_resp_id := fnd_global.resp_id;
1282
1283 IF (l_prev_resp_id IS NULL OR l_prev_resp_id = -1) THEN
1284 l_prev_resp_appl_id := fnd_global.resp_appl_id;
1285 l_prev_user_id := fnd_global.user_id;
1286 l_resp_set_flag := TRUE;
1287
1288 OPEN l_resp_csr;
1289 FETCH l_resp_csr into l_resp_appl_id, l_resp_id;
1290 CLOSE l_resp_csr;
1291
1292 fnd_global.apps_initialize(user_id => nvl(fnd_global.user_id, -1),
1293 resp_id => l_resp_id,
1294 resp_appl_id => l_resp_appl_id
1295 );
1296 END IF;
1297
1298 ---------------------------------------------------
1299 --- Added for bug 3608969
1300 Begin
1301 l_retval := INV_TXN_MANAGER_PUB.process_Transactions(p_api_version => 1,
1302 p_init_msg_list => fnd_api.g_false ,
1303 p_commit => fnd_api.g_false ,
1304 p_validation_level => fnd_api.g_valid_level_full ,
1305 x_return_status => l_return_status,
1306 x_msg_count => l_msg_cnt,
1307 x_msg_data => l_msg_data,
1308 x_trans_count => l_trans_count,
1309 p_table => 1,
1310 p_header_id => l_transaction_header_id);
1311
1312 if(l_retval <> 0) THEN
1313 l_outcome := false;
1314 select error_code, error_explanation
1315 into l_error_code, l_error_explanation
1316 from mtl_transactions_interface
1317 where transaction_header_id = l_transaction_header_id
1318 and rownum = 1;
1319 else
1320 l_outcome := true;
1321 end if;
1322
1323 EXCEPTION
1324 WHEN NO_DATA_FOUND THEN
1325 l_error_code := ' ';
1326 l_error_explanation := 'No Errors';
1327 l_outcome := true;
1328 WHEN TOO_MANY_ROWS THEN
1329 l_error_explanation:= fnd_message.get;
1330 l_outcome := false;
1331 WHEN OTHERS THEN
1332 l_outcome := false;
1333 END;
1334 ----------------------------------------------------
1335
1336
1337 /* commented for bug 3608969
1338 l_outcome := mtl_online_transaction_pub.process_online
1339 ( p_transaction_header_id => l_transaction_header_id
1340 , p_timeout => l_timeout
1341 , p_error_code => l_error_code
1342 , p_error_explanation => l_error_explanation
1343 );
1344 */
1345
1346 IF (l_resp_set_flag) THEN
1347 fnd_global.apps_initialize(user_id => l_prev_user_id,
1348 resp_id => l_prev_resp_id,
1349 resp_appl_id => l_prev_resp_appl_id);
1350 END IF;
1351
1352 IF (l_outcome = FALSE) THEN
1353 delete from mtl_transactions_interface where transaction_header_id = l_transaction_header_id;
1354 FND_MESSAGE.SET_NAME('CSP', 'CSP_TRANSACT_ERRORS');
1355 FND_MESSAGE.SET_TOKEN('ERROR_CODE', l_error_code, TRUE);
1356 FND_MESSAGE.SET_TOKEN('ERROR_EXPLANATION', l_error_explanation, FALSE);
1357 FND_MSG_PUB.ADD;
1358 RAISE EXCP_USER_DEFINED;
1359 else
1360 OPEN transaction_id_cur;
1361 FETCH transaction_id_cur INTO px_transaction_id;
1362 CLOSE transaction_id_cur;
1363
1364
1365 if(p_transaction_type_id = 2 or p_transaction_type_id = 3) then
1366
1367 OPEN c_source_owner_dtls;
1368 FETCH c_source_owner_dtls INTO l_src_rsc_type,l_src_rsc_id;
1369 CLOSE c_source_owner_dtls;
1370 l_src_rsc_type_converted := csf_alerts_pub.category_type(l_src_rsc_type);
1371
1372 OPEN c_source_user_name;
1373 FETCH c_source_user_name into l_src_username,l_src_source_name;
1374 CLOSE c_source_user_name;
1375
1376 OPEN c_dest_owner_dtls;
1377 FETCH c_dest_owner_dtls INTO l_dest_rsc_type,l_dest_rsc_id;
1378 CLOSE c_dest_owner_dtls;
1379 l_dest_rsc_type_converted := csf_alerts_pub.category_type(l_dest_rsc_type);
1380
1381 OPEN c_dest_user_name;
1382 FETCH c_dest_user_name INTO l_dest_username,l_dest_source_name;
1383 CLOSE c_dest_user_name;
1384
1385 OPEN c_source_org_code;
1386 FETCH c_source_org_code INTO l_source_org_code;
1387 CLOSE c_source_org_code;
1388
1389 OPEN c_dest_org_code;
1390 FETCH c_dest_org_code INTO l_dest_org_code;
1391 CLOSE c_dest_org_code;
1392
1393 OPEN c_item_code;
1394 FETCH c_item_code INTO l_item_code;
1395 CLOSE c_item_code;
1396
1397 OPEN c_uom_desc;
1398 FETCH c_uom_desc INTO l_uom_desc;
1399 CLOSE c_uom_desc;
1400
1401
1402 itemtype := 'CSPSITXN';
1403
1404 wf_engine.createprocess(itemtype => itemtype,
1405 itemkey => px_transaction_id,
1406 process => 'CSPSITXN_PROCESS');
1407 wf_engine.setItemAttrText(itemtype => itemtype,
1408 itemkey => px_transaction_id,
1409 aname => 'ITEMNAME',
1410 avalue => l_item_code);
1411 wf_engine.setItemAttrText(itemtype => itemtype,
1412 itemkey => px_transaction_id,
1413 aname => 'MTLQUANTITY',
1414 avalue => p_quantity);
1415 wf_engine.setItemAttrText(itemtype => itemtype,
1416 itemkey => px_transaction_id,
1417 aname => 'UOM',
1418 avalue => l_uom_desc);
1419 wf_engine.setItemAttrText(itemtype => itemtype,
1420 itemkey => px_transaction_id,
1421 aname => 'TO_ORG_CODE',
1422 avalue => l_dest_org_code);
1423 wf_engine.setItemAttrText(itemtype => itemtype,
1424 itemkey => px_transaction_id,
1425 aname => 'FROM_ORG_CODE',
1426 avalue => l_source_org_code);
1427 wf_engine.setItemAttrText(itemtype => itemtype,
1428 itemkey => px_transaction_id,
1429 aname => 'TO_SUBINV',
1430 avalue => p_transfer_to_subinventory);
1431 wf_engine.setItemAttrText(itemtype => itemtype,
1432 itemkey => px_transaction_id,
1433 aname => 'FROM_SUBINV',
1434 avalue => p_subinventory_code);
1435 wf_engine.setItemAttrText(itemtype => itemtype,
1436 itemkey => px_transaction_id,
1437 aname => 'SOURCEORGUSER',
1438 avalue => l_src_username);
1439 wf_engine.setItemAttrText(itemtype => itemtype,
1440 itemkey => px_transaction_id,
1441 aname => 'DESTORGUSER',
1442 avalue => l_dest_username);
1443 wf_engine.setItemAttrText(itemtype => itemtype,
1444 itemkey => px_transaction_id,
1445 aname => 'DEST_USER_FULL_NAME',
1446 avalue => l_dest_source_name);
1447 wf_engine.setItemAttrText(itemtype => itemtype,
1448 itemkey => px_transaction_id,
1449 aname => 'SRC_USER_FULL_NAME',
1450 avalue => l_src_source_name);
1451 IF p_serial_number IS NOT NULL THEN
1452 wf_engine.setItemAttrText(itemtype => itemtype,
1453 itemkey => px_transaction_id,
1454 aname => 'SERIAL_NUMBER',
1455 avalue => p_serial_number);
1456 ELSE
1457 wf_engine.setItemAttrText(itemtype => itemtype,
1458 itemkey => px_transaction_id,
1459 aname => 'SERIAL_NUMBER',
1460 avalue => '-');
1461 END IF;
1462 IF p_to_serial_number IS NOT NULL THEN
1463 wf_engine.setItemAttrText(itemtype => itemtype,
1464 itemkey => px_transaction_id,
1465 aname => 'TO_SERIAL_NUMBER',
1466 avalue => p_to_serial_number);
1467 ELSIF p_serial_number IS NOT NULL THEN
1468 wf_engine.setItemAttrText(itemtype => itemtype,
1469 itemkey => px_transaction_id,
1470 aname => 'TO_SERIAL_NUMBER',
1471 avalue => p_serial_number);
1472 ELSE
1473 wf_engine.setItemAttrText(itemtype => itemtype,
1474 itemkey => px_transaction_id,
1475 aname => 'TO_SERIAL_NUMBER',
1476 avalue => '-');
1477 END IF;
1478 IF p_revision IS NOT NULL THEN
1479 wf_engine.setItemAttrText(itemtype => itemtype,
1480 itemkey => px_transaction_id,
1481 aname => 'REVISION',
1482 avalue => p_revision);
1483 ELSE
1484 wf_engine.setItemAttrText(itemtype => itemtype,
1485 itemkey => px_transaction_id,
1486 aname => 'REVISION',
1487 avalue => '-');
1488 END IF;
1489 IF p_lot_number IS NOT NULL THEN
1490 wf_engine.setItemAttrText(itemtype => itemtype,
1491 itemkey => px_transaction_id,
1492 aname => 'LOT_NUMBER',
1493 avalue => p_lot_number);
1494 ELSE
1495 wf_engine.setItemAttrText(itemtype => itemtype,
1496 itemkey => px_transaction_id,
1497 aname => 'LOT_NUMBER',
1498 avalue => '-');
1499 END IF;
1500 wf_engine.startprocess(itemtype => itemtype,
1501 itemkey => px_transaction_id);
1502 END IF;
1503
1504
1505
1506 END IF;
1507 END IF;
1508
1509 IF fnd_api.to_boolean(p_commit) THEN
1510 commit work;
1511 END IF;
1512
1513 x_return_status := FND_API.G_RET_STS_SUCCESS;
1514 x_msg_data := l_msg_data;
1515 -- dbms_output.put_line('TRANSACT_MATERIAL: returning successfully');
1516 /*
1517 fnd_msg_pub.count_and_get
1518 ( p_count => x_msg_count
1519 , p_data => x_msg_data); */
1520 EXCEPTION
1521 WHEN EXCP_USER_DEFINED THEN
1522 If l_outcome = true then -- i.e. process_online has not been called.
1523 Rollback to Transact_Material_Pub;
1524 end if;
1525 x_return_status := FND_API.G_RET_STS_ERROR;
1526 fnd_msg_pub.count_and_get
1527 ( p_count => x_msg_count
1528 , p_data => x_msg_data);
1529 WHEN FND_API.G_EXC_ERROR THEN
1530 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1531 P_API_NAME => L_API_NAME
1532 ,P_PKG_NAME => G_PKG_NAME
1533 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1534 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1535 ,X_MSG_COUNT => X_MSG_COUNT
1536 ,X_MSG_DATA => X_MSG_DATA
1537 ,X_RETURN_STATUS => X_RETURN_STATUS);
1538 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1539 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1540 P_API_NAME => L_API_NAME
1541 ,P_PKG_NAME => G_PKG_NAME
1542 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1543 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
1544 ,X_MSG_COUNT => X_MSG_COUNT
1545 ,X_MSG_DATA => X_MSG_DATA
1546 ,X_RETURN_STATUS => X_RETURN_STATUS);
1547 WHEN OTHERS THEN
1548 If l_outcome = true then
1549 Rollback to Transact_Material_Pub;
1550 end if;
1551 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
1552 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1553 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
1554 fnd_msg_pub.add;
1555 fnd_msg_pub.count_and_get
1556 ( p_count => x_msg_count
1557 , p_data => x_msg_data);
1558 x_return_status := fnd_api.g_ret_sts_error;
1559
1560 END TRANSACT_MATERIAL;
1561
1562
1563 PROCEDURE transact_temp_record(
1564 /*$Header: csppttnb.pls 120.30.12020000.4 2013/02/11 08:52:32 htank ship $*/
1565 -- Start of Comments
1566 -- Procedure name : transact_temp_record
1567 -- Purpose : This procedure copies the data from the given mtl_material_transactions_temp record to the
1568 -- mtl_transactions_temp_interface table. It will also analyzed whether the item associated with the
1569 -- the given temp id is under serial or lot control. If the item is under under lot or serial control, this
1570 -- procedure inserts the necessary data into the mtl_lot_transactions_interface table or the
1571 -- mtl_serial_numbers_interface table. After the insertion completes, it deletes the existing record from
1572 -- the mtl_material_transactions_temp, mtl_transaction_lots_temp or the mtl_serial_numbers_temp tables.
1573 --
1574 -- History :
1575 -- Person Date Descriptions
1576 -- ------ ---- --------------
1577 -- klou 27-Mar-2000 created.
1578 --
1579 -- NOTES: If validations have been done in the precedent procedure from which this one is being called, doing a
1580 -- full validation here is unnecessary. To avoid repeating the same validations, you can set the
1581 -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
1582 -- responsibility to make sure all proper validations have been done before calling this procedure.
1583 -- You are recommended to let this procedure handle the validations if you are not sure.
1584 --
1585 -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
1586 -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
1587 -- to FND_API.G_VALID_LEVEL_FULL when making the call.
1588 -- End of Comments
1589
1590 P_Api_Version_Number IN NUMBER,
1591 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1592 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1593 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1594 p_transaction_temp_id IN NUMBER,
1595 px_transaction_header_id IN OUT NOCOPY NUMBER,
1596 p_online_process_flag IN BOOLEAN := FALSE,
1597 X_Return_Status OUT NOCOPY VARCHAR2,
1598 X_Msg_Count OUT NOCOPY NUMBER,
1599 X_Msg_Data OUT NOCOPY VARCHAR2
1600 )
1601 IS
1602 Cursor l_Get_Temp_Csr IS
1603 select
1604 transaction_temp_id ,
1605 transaction_source_id ,
1606 lot_number ,
1607 lot_expiration_date ,
1608 transaction_quantity ,
1609 move_order_line_id ,
1610 item_lot_control_code ,
1611 item_serial_control_code ,
1612 inventory_item_id ,
1613 organization_id ,
1614 subinventory_code ,
1615 locator_id ,
1616 revision ,
1617 TRANSACTION_UOM ,
1618 SOURCE_CODE ,
1619 source_line_id ,
1620 TRANSACTION_TYPE_ID ,
1621 distribution_account_id ,
1622 transfer_subinventory ,
1623 transfer_to_location ,
1624 transfer_organization ,
1625 trx_source_line_id ,
1626 expected_arrival_date
1627 from mtl_material_transactions_temp
1628 where transaction_temp_id = p_transaction_temp_id;
1629
1630 Cursor l_Get_Lot_Temp_Csr IS
1631 select transaction_temp_id, serial_transaction_temp_id,
1632 lot_number, lot_expiration_date, transaction_quantity
1633 from mtl_transaction_lots_temp
1634 where transaction_temp_id = p_transaction_temp_id;
1635
1636 Cursor l_Get_Serial_Temp_Csr(l_transaction_temp_id NUMBER) IS
1637 select transaction_temp_id, fm_serial_number, to_serial_number, serial_prefix from mtl_serial_numbers_temp
1638 where transaction_temp_id = l_transaction_temp_id;
1639
1640 Cursor l_Get_txn_header_id_csr IS
1641 SELECT mtl_material_transactions_s.nextval
1642 FROM dual;
1643
1644 Cursor l_Get_Mo_Header_id_csr(l_line_id NUMBER) IS
1645 select distinct header_id from csp_moveorder_lines
1646 where line_id = l_line_id;
1647
1648 Type l_lot_temp_type IS Record (
1649 transaction_temp_id NUMBER,
1650 serial_transaction_temp_id NUMBER,
1651 lot_number VARCHAR2(80),
1652 lot_expiration_date DATE,
1653 transaction_quantity NUMBER);
1654
1655 Type l_serial_temp_type IS Record (
1656 transaction_temp_id NUMBER,
1657 fm_serial_number VARCHAR2(30),
1658 to_serial_number VARCHAR2(30),
1659 serial_prefix NUMBER );
1660
1661 Type l_mtl_temp_type IS Record (
1662 transaction_temp_id NUMBER,
1663 transaction_source_id NUMBER,
1664 lot_number VARCHAR2(80),
1665 lot_expiration_date DATE,
1666 transaction_quantity NUMBER,
1667 move_order_line_id NUMBER,
1668 item_lot_control_code NUMBER,
1669 item_serial_control_code NUMBER,
1670 inventory_item_id NUMBER,
1671 organization_id NUMBER,
1672 subinventory_code VARCHAR2(10),
1673 locator_id NUMBER,
1674 revision VARCHAR2(3),
1675 TRANSACTION_UOM VARCHAR2(3),
1676 SOURCE_CODE VARCHAR2(30),
1677 source_line_id NUMBER,
1678 TRANSACTION_TYPE_ID NUMBER,
1679 distribution_account_id NUMBER,
1680 transfer_subinventory VARCHAR2(10),
1681 transfer_to_location NUMBER,
1682 transfer_organization NUMBER,
1683 trx_source_line_id NUMBER,
1684 expected_arrival_date DATE);
1685
1686 l_transaction_id NUMBER;
1687 l_Mo_header_id NUMBER;
1688 l_lot_temp_rec l_lot_temp_type;
1689 l_serial_temp_rec l_serial_temp_type;
1690 l_mtl_txn_temp_rec l_mtl_temp_type; --CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
1691 l_api_version_number CONSTANT NUMBER := 1.0;
1692 l_api_name CONSTANT VARCHAR2(50) := 'transact_temp_record';
1693 l_msg_data VARCHAR2(300);
1694 l_check_existence NUMBER := 0;
1695 l_return_status VARCHAR2(1);
1696 l_msg_count NUMBER := 0;
1697 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1698 l_transaction_header_id number := px_transaction_header_id;
1699 l_lot_number VARCHAR2(80);
1700 l_lot_expiration_date DATE;
1701 l_fm_serial_number VARCHAR2(30);
1702 l_to_serial_number VARCHAR2(30);
1703 l_qty_processed NUMBER := 0;
1704 l_moheader_id NUMBER;
1705 EXCP_USER_DEFINED EXCEPTION;
1706
1707 BEGIN
1708 Savepoint transact_temp_record_pub;
1709
1710 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
1711 -- initialize message list
1712 FND_MSG_PUB.initialize;
1713 END IF;
1714
1715 -- Standard call to check for call compatibility.
1716 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1717 p_api_version_number,
1718 l_api_name,
1719 G_PKG_NAME)
1720 THEN
1721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1722 END IF;
1723
1724 IF p_validation_level = fnd_api.g_valid_level_full THEN
1725 IF p_transaction_temp_id IS NULL THEN
1726 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
1727 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_transaction_temp_id', FALSE);
1728 FND_MSG_PUB.ADD;
1729 RAISE EXCP_USER_DEFINED;
1730 ELSE
1731 BEGIN
1732 select transaction_temp_id into l_check_existence
1733 from mtl_material_transactions_temp
1734 where transaction_temp_id = p_transaction_temp_id;
1735 EXCEPTION
1736 WHEN NO_DATA_FOUND THEN
1737 fnd_message.set_name ('CSP', 'CSP_INVALID_TXN_TEMP_ID');
1738 fnd_message.set_token('ID', to_char(p_transaction_temp_id), FALSE);
1739 fnd_msg_pub.add;
1740 RAISE EXCP_USER_DEFINED;
1741 WHEN OTHERS THEN
1742 RAISE TOO_MANY_ROWS; -- this will really go to OTHERS exception.
1743 END;
1744 END IF;
1745 End If;
1746
1747 IF px_transaction_header_id IS NULL THEN
1748 Open l_Get_Txn_Header_id_csr;
1749 Fetch l_Get_Txn_Header_id_csr into l_transaction_header_id;
1750 Close l_Get_Txn_Header_id_csr;
1751 ELSE
1752 l_transaction_header_id := px_transaction_header_id;
1753 END IF;
1754
1755 -- begin to transact the transaction record
1756 -- Fetch the temp record into l_mtl_txn_temp_rec;
1757 Open l_Get_Temp_Csr;
1758 Fetch l_Get_Temp_Csr Into l_mtl_txn_temp_rec;
1759 If l_Get_Temp_Csr%NOTFOUND Then
1760 Close l_Get_Temp_Csr;
1761 fnd_message.set_name ('CSP', 'CSP_INVALID_TXN_TEMP_ID');
1762 fnd_message.set_token('ID', to_char(p_transaction_temp_id), FALSE);
1763 fnd_msg_pub.add;
1764 RAISE EXCP_USER_DEFINED;
1765 Else
1766 Close l_Get_Temp_Csr;
1767 End If;
1768
1769 -- find the move order header id for keeping track on the record in the mtl_material_transactions table.
1770 If l_mtl_txn_temp_rec.transaction_source_id is null then
1771 Open l_Get_Mo_Header_id_csr(l_mtl_txn_temp_rec.move_order_line_id);
1772 Fetch l_Get_Mo_Header_id_csr Into l_Mo_header_id;
1773 If l_Get_Mo_header_id_csr%NOTFOUND THEN
1774 Close l_Get_Mo_header_id_csr;
1775 fnd_message.set_name ('CSP', 'CSP_MOVEORDER_LINE_NO_PARENT');
1776 fnd_message.set_token ('LINE_ID', to_char(l_mtl_txn_temp_rec.move_order_line_id), FALSE);
1777 fnd_msg_pub.add;
1778 End If;
1779
1780 Close l_Get_Mo_header_id_csr;
1781 Else
1782 l_mo_header_id := l_mtl_txn_temp_rec.transaction_source_id;
1783 End If;
1784
1785 -- Analyze whether the item being transacted is under serial or lot control.
1786 -- case 1: only lot control
1787 If nvl(l_mtl_txn_temp_rec.item_lot_control_code, 1) <> 1 And
1788 nvl(l_mtl_txn_temp_rec.item_serial_control_code, 1) in (1, 6) Then
1789
1790 l_qty_processed := 0;
1791
1792 -- open the l_Get_Lot_Temp_Csr to find out the lot number and expiration date
1793 Open l_Get_Lot_Temp_Csr;
1794 Loop <<process_lot_loop_1>> -- there may be more than one lot record
1795 Fetch l_Get_Lot_Temp_Csr Into l_lot_temp_rec;
1796 Exit When l_Get_Lot_Temp_Csr%NOTFOUND;
1797
1798 IF l_qty_processed <= l_mtl_txn_temp_rec.transaction_quantity Then
1799 TRANSACT_MATERIAL
1800 (p_api_version => l_api_version_number
1801 ,p_Init_Msg_List => P_Init_Msg_List
1802 ,p_commit => l_commit
1803 ,px_transaction_id => l_transaction_id
1804 ,px_transaction_header_id => l_transaction_header_id
1805 ,p_inventory_item_id => l_mtl_txn_temp_rec.inventory_item_id
1806 ,p_organization_id => l_mtl_txn_temp_rec.organization_id
1807 ,p_subinventory_code => l_mtl_txn_temp_rec.subinventory_code
1808 ,p_locator_id => l_mtl_txn_temp_rec.locator_id
1809 ,p_lot_number => l_lot_temp_rec.lot_number
1810 ,p_lot_expiration_date => l_lot_temp_rec.lot_expiration_date
1811 ,p_revision => l_mtl_txn_temp_rec.revision
1812 ,p_serial_number => null
1813 ,p_to_serial_number => NULL
1814 ,p_quantity => l_lot_temp_rec.transaction_quantity
1815 ,p_uom => l_mtl_txn_temp_rec.TRANSACTION_UOM
1816 ,p_source_id => l_mtl_txn_temp_rec.SOURCE_CODE --TRANSACTION_SOURCE_ID
1817 ,p_source_line_id => l_mtl_txn_temp_rec.source_line_id
1818 ,p_transaction_type_id => l_mtl_txn_temp_rec.TRANSACTION_TYPE_ID
1819 ,p_account_id => l_mtl_txn_temp_rec.distribution_account_id
1820 ,p_transfer_to_subinventory => l_mtl_txn_temp_rec.transfer_subinventory
1821 ,p_transfer_to_locator => l_mtl_txn_temp_rec.transfer_to_location
1822 ,p_transfer_to_organization => l_mtl_txn_temp_rec.transfer_organization
1823 ,p_online_process_flag => p_online_process_flag
1824 ,p_transaction_source_id => l_mo_header_id
1825 ,p_trx_source_line_id => nvl(l_mtl_txn_temp_rec.trx_source_line_id, l_mtl_txn_temp_rec.move_order_line_id)
1826 ,p_expected_delivery_date => l_mtl_txn_temp_rec.expected_arrival_date
1827 ,x_return_status => l_return_status
1828 ,x_msg_count => l_msg_count
1829 ,x_msg_data => l_msg_data
1830 );
1831
1832 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1833 Close l_Get_Lot_Temp_Csr;
1834 RAISE FND_API.G_EXC_ERROR;
1835 END IF;
1836 l_qty_processed := l_qty_processed + l_lot_temp_rec.transaction_quantity;
1837 End If;
1838 End Loop; -- <<process_lot_loop_1>>
1839
1840 If l_Get_Lot_Temp_Csr%rowcount = 0 Then
1841 Close l_Get_Lot_Temp_Csr;
1842 fnd_message.set_name('CSP', 'CSP_NO_LOT_TXN_RECORD');
1843 fnd_msg_pub.add;
1844 RAISE EXCP_USER_DEFINED;
1845 End if;
1846
1847 If l_Get_Lot_Temp_Csr%ISOPEN Then
1848 Close l_Get_Lot_Temp_Csr;
1849 End if;
1850
1851 -- case 2: under both lot control and serial control
1852 Elsif nvl(l_mtl_txn_temp_rec.item_lot_control_code, 1) <> 1 And
1853 nvl(l_mtl_txn_temp_rec.item_serial_control_code, 1) in (2, 5) Then
1854 l_qty_processed := 0;
1855
1856 -- open the l_Get_Lot_Temp_Csr to find out the lot number and expiration date
1857 Open l_Get_Lot_Temp_Csr;
1858 Loop <<process_lot_loop_2>> -- there may be more than one lot record
1859 Fetch l_Get_Lot_Temp_Csr Into l_lot_temp_rec;
1860 Exit When l_Get_Lot_Temp_Csr%NOTFOUND;
1861
1862 -- for each lot record, it may have more than one serial record
1863 -- open the l_Get_Serial_Temp_Csr(l_transaction_temp_id NUMBER)
1864 Open l_Get_Serial_Temp_Csr(l_lot_temp_rec.serial_transaction_temp_id);
1865 Loop <<process_serial_loop_2>>
1866 Fetch l_Get_Serial_Temp_Csr Into l_serial_temp_rec;
1867 Exit when l_Get_Serial_Temp_Csr%NOTFOUND;
1868
1869 IF l_qty_processed <= l_mtl_txn_temp_rec.transaction_quantity Then
1870 TRANSACT_MATERIAL
1871 (p_api_version => l_api_version_number
1872 ,p_Init_Msg_List => P_Init_Msg_List
1873 ,p_commit => l_commit
1874 ,px_transaction_id => l_transaction_id
1875 ,px_transaction_header_id => l_transaction_header_id
1876 ,p_inventory_item_id => l_mtl_txn_temp_rec.inventory_item_id
1877 ,p_organization_id => l_mtl_txn_temp_rec.organization_id
1878 ,p_subinventory_code => l_mtl_txn_temp_rec.subinventory_code
1879 ,p_locator_id => l_mtl_txn_temp_rec.locator_id
1880 ,p_lot_number => l_lot_temp_rec.lot_number
1881 ,p_lot_expiration_date => l_lot_temp_rec.lot_expiration_date
1882 ,p_revision => l_mtl_txn_temp_rec.revision
1883 ,p_serial_number => l_serial_temp_rec.fm_serial_number
1884 ,p_to_serial_number => l_serial_temp_rec.to_serial_number
1885 ,p_quantity => nvl(l_serial_temp_rec.serial_prefix, 1)
1886 ,p_uom => l_mtl_txn_temp_rec.TRANSACTION_UOM
1887 ,p_source_id => l_mtl_txn_temp_rec.SOURCE_CODE --TRANSACTION_SOURCE_ID
1888 ,p_source_line_id => l_mtl_txn_temp_rec.source_line_id
1889 ,p_transaction_type_id => l_mtl_txn_temp_rec.TRANSACTION_TYPE_ID
1890 ,p_account_id => l_mtl_txn_temp_rec.distribution_account_id
1891 ,p_transfer_to_subinventory => l_mtl_txn_temp_rec.transfer_subinventory
1892 ,p_transfer_to_locator => l_mtl_txn_temp_rec.transfer_to_location
1893 ,p_transfer_to_organization => l_mtl_txn_temp_rec.transfer_organization
1894 ,p_online_process_flag => p_online_process_flag
1895 ,p_transaction_source_id => l_mo_header_id
1896 ,p_trx_source_line_id => nvl(l_mtl_txn_temp_rec.trx_source_line_id, l_mtl_txn_temp_rec.move_order_line_id)
1897 ,p_expected_delivery_date => l_mtl_txn_temp_rec.expected_arrival_date
1898 ,x_return_status => l_return_status
1899 ,x_msg_count => l_msg_count
1900 ,x_msg_data => l_msg_data
1901 );
1902
1903 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1904 Close l_Get_Lot_Temp_Csr;
1905 Close l_Get_Serial_Temp_Csr;
1906 RAISE FND_API.G_EXC_ERROR;
1907 END IF;
1908 l_qty_processed := l_qty_processed + nvl(l_serial_temp_rec.serial_prefix, 1);
1909 End If;
1910 End Loop; -- <<process_serial_loop_2>>
1911
1912 If l_Get_Serial_Temp_Csr%rowcount = 0 Then
1913 Close l_Get_Lot_Temp_Csr;
1914 Close l_Get_Serial_Temp_Csr;
1915 fnd_message.set_name('CSP', 'CSP_NO_SERIAL_TXN_RECORD');
1916 fnd_msg_pub.add;
1917 RAISE EXCP_USER_DEFINED;
1918 End If;
1919
1920 If l_Get_Serial_Temp_Csr%ISOPEN Then
1921 Close l_Get_Serial_Temp_Csr;
1922 End if;
1923 End Loop; --<<process_lot_loop_2>>
1924
1925 If l_Get_Lot_Temp_Csr%ISOPEN Then
1926 Close l_Get_Lot_Temp_Csr;
1927 End if;
1928
1929 -- case 3: only under serial control
1930 Elsif nvl(l_mtl_txn_temp_rec.item_lot_control_code, 1) = 1 And
1931 nvl(l_mtl_txn_temp_rec.item_serial_control_code, 1) in (2, 5) Then
1932 l_qty_processed := 0;
1933
1934 -- open the l_Get_Serial_Temp_Csr(l_transaction_temp_id NUMBER)
1935 Open l_Get_Serial_Temp_Csr(l_mtl_txn_temp_rec.transaction_temp_id);
1936 Loop <<process_serial_loop_3>>
1937 Fetch l_Get_Serial_Temp_Csr Into l_serial_temp_rec;
1938 Exit when l_Get_Serial_Temp_Csr%NOTFOUND;
1939
1940 IF l_qty_processed <= l_mtl_txn_temp_rec.transaction_quantity Then
1941 TRANSACT_MATERIAL
1942 (p_api_version => l_api_version_number
1943 ,p_Init_Msg_List => P_Init_Msg_List
1944 ,p_commit => l_commit
1945 ,px_transaction_id => l_transaction_id
1946 ,px_transaction_header_id => l_transaction_header_id
1947 ,p_inventory_item_id => l_mtl_txn_temp_rec.inventory_item_id
1948 ,p_organization_id => l_mtl_txn_temp_rec.organization_id
1949 ,p_subinventory_code => l_mtl_txn_temp_rec.subinventory_code
1950 ,p_locator_id => l_mtl_txn_temp_rec.locator_id
1951 ,p_lot_number => null
1952 ,p_lot_expiration_date => null
1953 ,p_revision => l_mtl_txn_temp_rec.revision
1954 ,p_serial_number => l_serial_temp_rec.fm_serial_number
1955 ,p_to_serial_number => l_serial_temp_rec.to_serial_number
1956 ,p_quantity => nvl(l_serial_temp_rec.serial_prefix, 1)
1957 ,p_uom => l_mtl_txn_temp_rec.TRANSACTION_UOM
1958 ,p_source_id => l_mtl_txn_temp_rec.SOURCE_CODE --TRANSACTION_SOURCE_ID
1959 ,p_source_line_id => l_mtl_txn_temp_rec.source_line_id
1960 ,p_transaction_type_id => l_mtl_txn_temp_rec.TRANSACTION_TYPE_ID
1961 ,p_account_id => l_mtl_txn_temp_rec.distribution_account_id
1962 ,p_transfer_to_subinventory => l_mtl_txn_temp_rec.transfer_subinventory
1963 ,p_transfer_to_locator => l_mtl_txn_temp_rec.transfer_to_location
1964 ,p_transfer_to_organization => l_mtl_txn_temp_rec.transfer_organization
1965 ,p_online_process_flag => p_online_process_flag
1966 ,p_transaction_source_id => l_mo_header_id
1967 ,p_trx_source_line_id => nvl(l_mtl_txn_temp_rec.trx_source_line_id, l_mtl_txn_temp_rec.move_order_line_id)
1968 ,p_expected_delivery_date => l_mtl_txn_temp_rec.expected_arrival_date
1969 ,x_return_status => l_return_status
1970 ,x_msg_count => l_msg_count
1971 ,x_msg_data => l_msg_data
1972 );
1973
1974 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1975 Close l_Get_Serial_Temp_Csr;
1976 RAISE FND_API.G_EXC_ERROR;
1977 END IF;
1978 l_qty_processed := l_qty_processed + l_serial_temp_rec.serial_prefix;
1979 End If;
1980 End Loop; --<<process_serial_loop_3>>
1981 If l_Get_Serial_Temp_Csr%ISOPEN Then
1982 Close l_Get_Serial_Temp_Csr;
1983 End if;
1984
1985 -- case 4: neither serial control nor lot control
1986 Else
1987 TRANSACT_MATERIAL
1988 (p_api_version => l_api_version_number
1989 ,p_Init_Msg_List => P_Init_Msg_List
1990 ,p_commit => l_commit
1991 ,px_transaction_id => l_transaction_id
1992 ,px_transaction_header_id => l_transaction_header_id
1993 ,p_inventory_item_id => l_mtl_txn_temp_rec.inventory_item_id
1994 ,p_organization_id => l_mtl_txn_temp_rec.organization_id
1995 ,p_subinventory_code => l_mtl_txn_temp_rec.subinventory_code
1996 ,p_locator_id => l_mtl_txn_temp_rec.locator_id
1997 ,p_lot_number => null
1998 ,p_lot_expiration_date => null
1999 ,p_revision => l_mtl_txn_temp_rec.revision
2000 ,p_serial_number => null
2001 ,p_to_serial_number => null
2002 ,p_quantity => l_mtl_txn_temp_rec.transaction_quantity
2003 ,p_uom => l_mtl_txn_temp_rec.TRANSACTION_UOM
2004 ,p_source_id => l_mtl_txn_temp_rec.SOURCE_CODE--TRANSACTION_SOURCE_ID
2005 ,p_source_line_id => l_mtl_txn_temp_rec.source_line_id
2006 ,p_transaction_type_id => l_mtl_txn_temp_rec.TRANSACTION_TYPE_ID
2007 ,p_account_id => l_mtl_txn_temp_rec.distribution_account_id
2008 ,p_transfer_to_subinventory => l_mtl_txn_temp_rec.transfer_subinventory
2009 ,p_transfer_to_locator => l_mtl_txn_temp_rec.transfer_to_location
2010 ,p_transfer_to_organization => l_mtl_txn_temp_rec.transfer_organization
2011 ,p_online_process_flag => p_online_process_flag
2012 ,p_transaction_source_id => l_mo_header_id
2013 ,p_trx_source_line_id => nvl(l_mtl_txn_temp_rec.trx_source_line_id, l_mtl_txn_temp_rec.move_order_line_id)
2014 ,p_expected_delivery_date => l_mtl_txn_temp_rec.expected_arrival_date
2015 ,x_return_status => l_return_status
2016 ,x_msg_count => l_msg_count
2017 ,x_msg_data => l_msg_data
2018 );
2019
2020 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2021 Close l_Get_Serial_Temp_Csr;
2022 RAISE FND_API.G_EXC_ERROR;
2023 END IF;
2024 End if;
2025
2026 IF fnd_api.to_boolean(p_commit) THEN
2027 commit work;
2028 END IF;
2029 x_return_status := l_return_status;
2030 fnd_msg_pub.count_and_get
2031 ( p_count => x_msg_count
2032 , p_data => x_msg_data);
2033 EXCEPTION
2034 WHEN EXCP_USER_DEFINED THEN
2035 Rollback to transact_temp_record_pub;
2036 x_return_status := FND_API.G_RET_STS_ERROR;
2037 fnd_msg_pub.count_and_get
2038 ( p_count => x_msg_count
2039 , p_data => x_msg_data);
2040 WHEN FND_API.G_EXC_ERROR THEN
2041 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2042 P_API_NAME => L_API_NAME
2043 ,P_PKG_NAME => G_PKG_NAME
2044 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2045 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2046 ,X_MSG_COUNT => X_MSG_COUNT
2047 ,X_MSG_DATA => X_MSG_DATA
2048 ,X_RETURN_STATUS => X_RETURN_STATUS);
2049 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2050 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2051 P_API_NAME => L_API_NAME
2052 ,P_PKG_NAME => G_PKG_NAME
2053 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2054 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2055 ,X_MSG_COUNT => X_MSG_COUNT
2056 ,X_MSG_DATA => X_MSG_DATA
2057 ,X_RETURN_STATUS => X_RETURN_STATUS);
2058 WHEN OTHERS THEN
2059 Rollback to transact_temp_record_pub;
2060 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2061 fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
2062 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
2063 fnd_msg_pub.add;
2064 fnd_msg_pub.count_and_get
2065 ( p_count => x_msg_count
2066 , p_data => x_msg_data);
2067 x_return_status := fnd_api.g_ret_sts_error;
2068 END transact_temp_record;
2069
2070
2071 PROCEDURE transact_items_transfer (
2072 P_Api_Version_Number IN NUMBER,
2073 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2074 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2075 p_Trans_Items IN OUT NOCOPY Trans_Items_Tbl_Type,
2076 p_Trans_Type_id IN NUMBER,
2077 X_Return_Status OUT NOCOPY VARCHAR2,
2078 X_Msg_Count OUT NOCOPY NUMBER,
2079 X_Msg_Data OUT NOCOPY VARCHAR2
2080 ) IS
2081
2082 PX_TRANSACTION_ID NUMBER;
2083 PX_TRANSACTION_HEADER_ID NUMBER;
2084 inx1 PLS_INTEGER;
2085 v_temp_error_msg varchar2(2000);
2086 BEGIN
2087
2088 PX_TRANSACTION_ID := NULL;
2089 PX_TRANSACTION_HEADER_ID := NULL;
2090
2091 FOR inx1 IN 1..p_Trans_Items.COUNT LOOP
2092 CSP_TRANSACTIONS_PUB.TRANSACT_MATERIAL(
2093 P_API_VERSION => P_Api_Version_Number,
2094 P_INIT_MSG_LIST => FND_API.G_TRUE,
2095 P_COMMIT => FND_API.G_TRUE,
2096 PX_TRANSACTION_ID => PX_TRANSACTION_ID,
2097 PX_TRANSACTION_HEADER_ID => PX_TRANSACTION_HEADER_ID,
2098 P_INVENTORY_ITEM_ID => p_Trans_Items(inx1).INVENTORY_ITEM_ID,
2099 P_ORGANIZATION_ID => p_Trans_Items(inx1).FRM_ORGANIZATION_ID,
2100 P_SUBINVENTORY_CODE => p_Trans_Items(inx1).FRM_SUBINVENTORY_CODE,
2101 P_LOCATOR_ID => p_Trans_Items(inx1).FRM_LOCATOR_ID,
2102 P_LOT_NUMBER => p_Trans_Items(inx1).LOT_NUMBER,
2103 P_LOT_EXPIRATION_DATE => NULL,
2104 P_REVISION => p_Trans_Items(inx1).REVISION,
2105 P_SERIAL_NUMBER => p_Trans_Items(inx1).SERIAL_NUMBER,
2106 P_TO_SERIAL_NUMBER => p_Trans_Items(inx1).TO_SERIAL_NUMBER,
2107 P_QUANTITY => p_Trans_Items(inx1).QUANTITY,
2108 P_UOM => p_Trans_Items(inx1).UOM_CODE,
2109 P_SOURCE_ID => NULL,
2110 P_SOURCE_LINE_ID => NULL,
2111 P_TRANSACTION_TYPE_ID => p_Trans_Type_id,
2112 P_ACCOUNT_ID => NULL,
2113 P_TRANSFER_TO_SUBINVENTORY => p_Trans_Items(inx1).TO_SUBINVENTORY_CODE,
2114 P_TRANSFER_TO_LOCATOR => p_Trans_Items(inx1).TO_LOCATOR_ID,
2115 P_TRANSFER_TO_ORGANIZATION => p_Trans_Items(inx1).TO_ORGANIZATION_ID,
2116 P_ONLINE_PROCESS_FLAG => TRUE,
2117 P_TRANSACTION_SOURCE_ID => NULL,
2118 P_TRX_SOURCE_LINE_ID => NULL,
2119 P_TRANSACTION_SOURCE_NAME => NULL,
2120 P_WAYBILL_AIRBILL => p_Trans_Items(inx1).WAYBILL_AIRBILL,
2121 P_SHIPMENT_NUMBER => p_Trans_Items(inx1).SHIPMENT_NUMBER,
2122 P_FREIGHT_CODE => p_Trans_Items(inx1).FREIGHT_CODE,
2123 P_REASON_ID => p_Trans_Items(inx1).REASON_ID,
2124 P_TRANSACTION_REFERENCE => NULL,
2125 P_TRANSACTION_DATE => sysdate,
2126 P_EXPECTED_DELIVERY_DATE => NULL,
2127 P_FINAL_COMPLETION_FLAG => NULL,
2128 X_RETURN_STATUS => X_Return_Status,
2129 X_MSG_COUNT => X_Msg_Count,
2130 X_MSG_DATA => X_Msg_Data
2131 );
2132
2133 IF X_Return_Status <> 'S' THEN
2134 v_temp_error_msg := X_Msg_Data;
2135 p_Trans_Items(inx1).ERROR_MSG := v_temp_error_msg;
2136 END IF;
2137 END LOOP;
2138
2139 X_Return_Status := 'S';
2140
2141 END transact_items_transfer;
2142
2143 PROCEDURE transact_subinv_transfer (
2144 P_Api_Version_Number IN NUMBER,
2145 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2146 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2147 p_Trans_Items IN OUT NOCOPY Trans_Items_Tbl_Type,
2148 X_Return_Status OUT NOCOPY VARCHAR2,
2149 X_Msg_Count OUT NOCOPY NUMBER,
2150 X_Msg_Data OUT NOCOPY VARCHAR2
2151 ) IS
2152 BEGIN
2153 transact_items_transfer(
2154 P_Api_Version_Number => P_Api_Version_Number,
2155 P_Init_Msg_List => P_Init_Msg_List,
2156 P_Commit => P_Commit,
2157 p_Trans_Items => p_Trans_Items,
2158 p_Trans_Type_id => 2, -- Subinventory Transfer
2159 X_Return_Status => X_Return_Status,
2160 X_Msg_Count => X_Msg_Count,
2161 X_Msg_Data => X_Msg_Data
2162 );
2163 END transact_subinv_transfer;
2164
2165
2166 PROCEDURE transact_intorg_transfer (
2167 P_Api_Version_Number IN NUMBER,
2168 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2169 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2170 p_Trans_Items IN OUT NOCOPY Trans_Items_Tbl_Type,
2171 p_if_intransit IN BOOLEAN,
2172 X_Return_Status OUT NOCOPY VARCHAR2,
2173 X_Msg_Count OUT NOCOPY NUMBER,
2174 X_Msg_Data OUT NOCOPY VARCHAR2
2175 ) IS
2176
2177 v_Trans_Type_id number;
2178 BEGIN
2179
2180 v_Trans_Type_id := 3; -- Direct Org Transfer
2181 IF p_if_intransit THEN
2182 v_Trans_Type_id := 21; -- Intransit Shipment
2183 END IF;
2184
2185 transact_items_transfer(
2186 P_Api_Version_Number => P_Api_Version_Number,
2187 P_Init_Msg_List => P_Init_Msg_List,
2188 P_Commit => P_Commit,
2189 p_Trans_Items => p_Trans_Items,
2190 p_Trans_Type_id => v_Trans_Type_id,
2191 X_Return_Status => X_Return_Status,
2192 X_Msg_Count => X_Msg_Count,
2193 X_Msg_Data => X_Msg_Data
2194 );
2195 END transact_intorg_transfer;
2196
2197 PROCEDURE create_move_order (
2198 p_Trans_Items IN OUT NOCOPY Trans_Items_Tbl_Type,
2199 p_date_required IN DATE,
2200 p_comments IN VARCHAR2,
2201 x_move_order_number OUT NOCOPY VARCHAR2,
2202 x_return_status OUT NOCOPY VARCHAR2,
2203 x_msg_count OUT NOCOPY NUMBER,
2204 x_msg_data OUT NOCOPY VARCHAR2
2205 ) IS
2206
2207 CURSOR c_REQUEST_NUMBER (v_move_order_id number )IS
2208 SELECT REQUEST_NUMBER
2209 FROM MTL_TXN_REQUEST_HEADERS
2210 WHERE HEADER_ID = v_move_order_id;
2211
2212 l_move_order_id NUMBER;
2213 l_line_id NUMBER;
2214 inx1 PLS_INTEGER;
2215 v_temp_error_msg varchar2(2000);
2216 BEGIN
2217
2218 l_move_order_id := NULL;
2219 l_line_id := NULL;
2220
2221 -- INSERT HEADER FIRST
2222
2223 CSP_TRANSACTIONS_PUB.CREATE_MOVE_ORDER_HEADER(
2224 px_header_id => l_move_order_id
2225 ,p_request_number => null
2226 ,p_api_version => 1.0
2227 ,p_Init_Msg_List => FND_API.G_TRUE
2228 ,p_commit => FND_API.G_TRUE
2229 ,p_date_required => NVL(p_date_required, sysdate)
2230 ,p_organization_id => p_Trans_Items(1).FRM_ORGANIZATION_ID
2231 ,p_from_subinventory_code => p_Trans_Items(1).FRM_SUBINVENTORY_CODE
2232 ,p_to_subinventory_code => p_Trans_Items(1).TO_SUBINVENTORY_CODE
2233 ,p_address1 => null
2234 ,p_address2 => null
2235 ,p_address3 => null
2236 ,p_address4 => null
2237 ,p_city => null
2238 ,p_postal_code => null
2239 ,p_state => null
2240 ,p_province => null
2241 ,p_country => null
2242 ,p_freight_carrier => null
2243 ,p_shipment_method => null
2244 ,p_autoreceipt_flag => null
2245 ,x_return_status => x_return_status
2246 ,x_msg_count => x_msg_count
2247 ,x_msg_data => x_msg_data );
2248
2249
2250 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2251 fnd_msg_pub.get
2252 ( p_msg_index => x_msg_count
2253 , p_encoded => FND_API.G_FALSE
2254 , p_data => x_msg_data
2255 , p_msg_index_out => x_msg_count
2256 );
2257 RAISE FND_API.G_EXC_ERROR;
2258 ELSE
2259 FOR inx1 IN 1..p_Trans_Items.COUNT LOOP
2260 CSP_TRANSACTIONS_PUB.CREATE_MOVE_ORDER_LINE
2261 (p_api_version => 1.0
2262 ,p_Init_Msg_List => FND_API.G_TRUE
2263 ,p_commit => FND_API.G_TRUE
2264 ,px_line_id => l_line_id
2265 ,p_header_id => l_move_order_id
2266 ,p_organization_id => p_Trans_Items(inx1).FRM_ORGANIZATION_ID
2267 ,p_from_subinventory_code => p_Trans_Items(inx1).FRM_SUBINVENTORY_CODE
2268 ,p_from_locator_id => p_Trans_Items(inx1).FRM_LOCATOR_ID
2269 ,p_inventory_item_id => p_Trans_Items(inx1).INVENTORY_ITEM_ID
2270 ,p_revision => p_Trans_Items(inx1).REVISION
2271 ,p_lot_number => p_Trans_Items(inx1).LOT_NUMBER
2272 ,p_serial_number_start => p_Trans_Items(inx1).SERIAL_NUMBER
2273 ,p_serial_number_end => p_Trans_Items(inx1).SERIAL_NUMBER
2274 ,p_quantity => p_Trans_Items(inx1).QUANTITY
2275 ,p_uom_code => p_Trans_Items(inx1).UOM_CODE
2276 ,p_quantity_delivered => null
2277 ,p_to_subinventory_code => p_Trans_Items(inx1).TO_SUBINVENTORY_CODE
2278 ,p_to_locator_id => p_Trans_Items(inx1).TO_LOCATOR_ID
2279 ,p_to_organization_id => p_Trans_Items(inx1).TO_ORGANIZATION_ID
2280 ,p_service_request => null
2281 ,p_task_id => null
2282 ,p_task_assignment_id => null
2283 ,p_customer_po => null
2284 ,p_date_required => NVL(p_date_required, sysdate)
2285 ,p_comments => p_comments
2286 ,x_return_status => x_return_status
2287 ,x_msg_count => x_msg_count
2288 ,x_msg_data => x_msg_data );
2289
2290 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2291 fnd_msg_pub.get
2292 ( p_msg_index => x_msg_count
2293 , p_encoded => FND_API.G_FALSE
2294 , p_data => x_msg_data
2295 , p_msg_index_out => x_msg_count
2296 );
2297 p_Trans_Items(inx1).ERROR_MSG := x_msg_data;
2298 end if;
2299
2300 END LOOP;
2301
2302 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2303 COMMIT WORK;
2304 /*
2305 open c_REQUEST_NUMBER(l_move_order_id);
2306 fetch c_REQUEST_NUMBER into x_move_order_number;
2307 close c_REQUEST_NUMBER;
2308 */
2309 x_move_order_number := to_char(l_line_id);
2310
2311 END IF;
2312
2313 EXCEPTION
2314 WHEN OTHERS THEN
2315 x_return_status := FND_API.G_RET_STS_ERROR;
2316 END create_move_order;
2317
2318 PROCEDURE receive_requirement_trans (
2319 p_trans_header_id IN NUMBER,
2320 p_trans_line_id IN NUMBER,
2321 p_trans_record IN Trans_Items_Rec_Type,
2322 p_trans_type IN VARCHAR2,
2323 p_req_line_detail_id IN NUMBER,
2324 p_close_short IN VARCHAR2,
2325 x_return_status OUT NOCOPY VARCHAR2,
2326 x_msg_count OUT NOCOPY NUMBER,
2327 x_msg_data OUT NOCOPY VARCHAR2
2328 ) IS
2329
2330 l_actual_trans_type varchar2(15);
2331 l_Trans_Items Trans_Items_Tbl_Type;
2332 l_task_id number;
2333 l_requirement_line_id number;
2334 l_new_reservation_id number;
2335 l_req_line_detail_id number;
2336 l_RESERVATION_REC CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
2337 l_if_intransit boolean;
2338
2339 l_inv_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
2340 l_serial_number inv_reservation_global.serial_number_tbl_type;
2341 l_relieved_quantity NUMBER;
2342 l_relieved_quantity1 NUMBER;
2343 l_remaining_quantity NUMBER;
2344
2345 l_mo_transaction_header_id number;
2346
2347 cursor check_task_req is
2348 select h.task_id, l.requirement_line_id
2349 from
2350 csp_requirement_headers h,
2351 csp_requirement_lines l,
2352 csp_req_line_details d
2353 where d.req_line_detail_id = p_req_line_detail_id
2354 and d.requirement_line_id = l.requirement_line_id
2355 and l.requirement_header_id = h.requirement_header_id
2356 and h.task_id is not null;
2357
2358 -- bug # 9525245
2359 l_oe_header_id number;
2360 l_oe_line_id number;
2361 l_rcv_ship_header_id number;
2362 l_rcv_ship_line_id number;
2363 l_receive_hdr_rec CSP_RECEIVE_PVT.rcv_hdr_rec_type;
2364 l_receive_rec_tbl CSP_RECEIVE_PVT.rcv_rec_tbl_type;
2365 l_rcv_rec_type CSP_RECEIVE_PVT.rcv_rec_type;
2366
2367 cursor get_rcv_header_data is
2368 select
2369 rsh.shipment_header_id,
2370 rsl.shipment_line_id,
2371 'INTERNAL' SOURCE_TYPE_CODE,
2372 rsh.receipt_source_code,
2373 rsh.shipment_num,
2374 rsh.ship_to_org_id,
2375 rsh.bill_of_lading,
2376 rsh.packing_slip,
2377 rsh.shipped_date,
2378 rsh.freight_carrier_code,
2379 rsh.expected_receipt_date,
2380 rsh.waybill_airbill_num,
2381 rsh.RECEIPT_NUM
2382 from
2383 RCV_SHIPMENT_LINES rsl,
2384 RCV_SHIPMENT_HEADERS rsh
2385 where
2386 rsl.shipment_line_id = l_rcv_ship_line_id
2387 and rsh.shipment_header_id = rsl.shipment_header_id
2388 and rsh.receipt_source_code = 'INTERNAL ORDER';
2389
2390 cursor get_rcv_line_data is
2391 select
2392 'INTERNAL' SOURCE_TYPE_CODE,
2393 order_type_code,
2394 item_id,
2395 item_revision,
2396 item_category_id,
2397 item_description,
2398 from_organization_id,
2399 ordered_qty,
2400 ordered_uom,
2401 decode(SERIAL_NUMBER_CONTROL_CODE, 1, TRANSACTION_QTY, 1),
2402 REQ_LINE_ID,
2403 receipt_source_code,
2404 lot_num,
2405 PRIMARY_UOM,
2406 PRIMARY_UOM_CLASS,
2407 SERIAL_NUM,
2408 TO_ORGANIZATION_ID,
2409 DESTINATION_SUBINVENTORY,
2410 DESTINATION_TYPE_CODE,
2411 ROUTING_ID,
2412 SHIP_TO_LOCATION_ID,
2413 ENFORCE_SHIP_TO_LOCATION_CODE,
2414 SET_OF_BOOKS_ID_SOB,
2415 CURRENCY_CODE_SOB,
2416 SERIAL_NUMBER_CONTROL_CODE,
2417 LOT_CONTROL_CODE,
2418 LOT_QUANTITY,
2419 item_revision
2420 from
2421 CSP_RECEIVE_lines_V
2422 where
2423 rcv_shipment_header_id = l_rcv_ship_header_id
2424 and rcv_shipment_line_id = l_rcv_ship_line_id
2425 and nvl(SERIAL_NUM, -999) = nvl(p_trans_record.SERIAL_NUMBER, -999);
2426
2427 l_total_reserved_qty number;
2428 l_total_req_qty number;
2429 l_already_res_qty number;
2430 l_hdr_need_by date;
2431 l_res_exists number;
2432
2433 cursor c_dest_info is
2434 SELECT h.destination_organization_id,
2435 h.destination_subinventory
2436 FROM csp_requirement_headers h,
2437 csp_requirement_lines l,
2438 csp_req_line_details d
2439 WHERE d.req_line_detail_id = p_req_line_detail_id
2440 AND d.requirement_line_id = l.requirement_line_id
2441 AND l.requirement_header_id = h.requirement_header_id;
2442
2443 l_req_dest_org_id number;
2444 l_req_dest_subinv varchar2(100);
2445
2446 BEGIN
2447 /*
2448 Logic in brief
2449 - if p_trans_type is 'RES' then actual transaction can be
2450 Subinventory Transfer, Inter-Org Transfer (Direct) or
2451 Inter-Org Transfer (Intransit)
2452 - if it is MO/IO/PO then will call another API
2453 - For 'RES'
2454 -> Get actual transaction based on Source and Destination
2455 -> Call API to complete the transaction
2456 -> Remove reservation from actual Source
2457 -> Create a new reservation on destination
2458 (Only if requirement is for a Task)
2459 -> Stamp this new reservation_id to requirement line detail record
2460 */
2461
2462 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2463 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2464 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2465 'Begin');
2466 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2467 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2468 'p_trans_type=' || p_trans_type);
2469
2470 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2471 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2472 'p_trans_header_id=' || p_trans_header_id);
2473 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2474 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2475 'p_trans_line_id=' || p_trans_line_id);
2476 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2477 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2478 'p_req_line_detail_id=' || p_req_line_detail_id);
2479 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2480 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2481 'p_close_short=' || p_close_short);
2482
2483 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2484 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2485 'p_trans_record.INVENTORY_ITEM_ID=' || p_trans_record.INVENTORY_ITEM_ID);
2486 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2487 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2488 'p_trans_record.REVISION=' || p_trans_record.REVISION);
2489 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2490 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2491 'p_trans_record.QUANTITY=' || p_trans_record.QUANTITY);
2492 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2493 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2494 'p_trans_record.UOM_CODE=' || p_trans_record.UOM_CODE);
2495 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2496 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2497 'p_trans_record.LOT_NUMBER=' || p_trans_record.LOT_NUMBER);
2498 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2499 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2500 'p_trans_record.SERIAL_NUMBER=' || p_trans_record.SERIAL_NUMBER);
2501 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2502 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2503 'p_trans_record.FRM_ORGANIZATION_ID=' || p_trans_record.FRM_ORGANIZATION_ID);
2504 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2505 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2506 'p_trans_record.FRM_SUBINVENTORY_CODE=' || p_trans_record.FRM_SUBINVENTORY_CODE);
2507 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2508 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2509 'p_trans_record.FRM_LOCATOR_ID=' || p_trans_record.FRM_LOCATOR_ID);
2510 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2511 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2512 'p_trans_record.TO_ORGANIZATION_ID=' || p_trans_record.TO_ORGANIZATION_ID);
2513 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2514 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2515 'p_trans_record.TO_SUBINVENTORY_CODE=' || p_trans_record.TO_SUBINVENTORY_CODE);
2516 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2517 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2518 'p_trans_record.TO_LOCATOR_ID=' || p_trans_record.TO_LOCATOR_ID);
2519 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2520 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2521 'p_trans_record.TO_SERIAL_NUMBER=' || p_trans_record.TO_SERIAL_NUMBER);
2522 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2523 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2524 'p_trans_record.WAYBILL_AIRBILL=' || p_trans_record.WAYBILL_AIRBILL);
2525 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2526 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2527 'p_trans_record.FREIGHT_CODE=' || p_trans_record.FREIGHT_CODE);
2528 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2529 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2530 'p_trans_record.SHIPMENT_NUMBER=' || p_trans_record.SHIPMENT_NUMBER);
2531 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2532 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2533 'p_trans_record.PACKLIST_LINE_ID=' || p_trans_record.PACKLIST_LINE_ID);
2534 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2535 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2536 'p_trans_record.TEMP_TRANSACTION_ID=' || p_trans_record.TEMP_TRANSACTION_ID);
2537 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2538 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2539 'p_trans_record.ERROR_MSG=' || p_trans_record.ERROR_MSG);
2540 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2541 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2542 'p_trans_record.SHIPMENT_LINE_ID=' || p_trans_record.SHIPMENT_LINE_ID);
2543
2544 end if;
2545
2546 if p_trans_type = 'RES' then
2547
2548
2549 l_remaining_quantity := 0;
2550
2551 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2552 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2553 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2554 'p_close_short=' || p_close_short);
2555 end if;
2556
2557 if p_close_short = 'Y' then
2558 CSP_SCH_INT_PVT.CANCEL_RESERVATION(
2559 p_reserv_id => p_trans_header_id,
2560 x_return_status => x_return_status,
2561 x_msg_data => x_msg_count,
2562 x_msg_count => x_msg_data
2563 );
2564
2565 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2566 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2567 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2568 'After calling CSP_SCH_INT_PVT.CANCEL_RESERVATION...');
2569 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2570 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2571 'x_return_status=' || x_return_status ||
2572 ', x_msg_count=' || x_msg_count ||
2573 ', x_msg_data=' || x_msg_data);
2574 end if;
2575 else
2576 l_inv_reservation_rec.reservation_id := p_trans_header_id;
2577 l_relieved_quantity := p_trans_record.QUANTITY;
2578
2579 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2580 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2581 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2582 'Before calling inv_reservation_pub.relieve_reservation for reservation_id = '
2583 || l_inv_reservation_rec.reservation_id || ' and qty = ' || l_relieved_quantity);
2584 end if;
2585
2586 inv_reservation_pub.relieve_reservation(
2587 p_api_version_number => 1.0
2588 ,p_init_msg_lst => fnd_api.g_false
2589 ,x_return_status => x_return_status
2590 ,x_msg_count => x_msg_count
2591 ,x_msg_data => x_msg_data
2592 ,p_rsv_rec => l_inv_reservation_rec
2593 ,p_primary_relieved_quantity => l_relieved_quantity
2594 ,p_relieve_all => fnd_api.g_false
2595 ,p_original_serial_number => l_serial_number
2596 ,p_validation_flag => fnd_api.g_true
2597 ,x_primary_relieved_quantity => l_relieved_quantity1
2598 ,x_primary_remain_quantity => l_remaining_quantity
2599 );
2600
2601 fnd_msg_pub.count_and_get
2602 ( p_count => x_msg_count
2603 , p_data => x_msg_data);
2604
2605 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2606 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2607 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2608 'After calling inv_reservation_pub.relieve_reservation...');
2609 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2610 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2611 'x_return_status=' || x_return_status ||
2612 ', x_msg_count=' || x_msg_count ||
2613 ', x_msg_data=' || x_msg_data ||
2614 ', l_remaining_quantity=' || l_remaining_quantity);
2615 end if;
2616
2617 end if;
2618
2619 if x_return_status <> 'S' or x_msg_data is not NULL then
2620 return;
2621 end if;
2622
2623 ------------
2624 l_actual_trans_type := getPartsReturnOrderType(p_trans_record.FRM_ORGANIZATION_ID,
2625 p_trans_record.FRM_SUBINVENTORY_CODE,
2626 p_trans_record.TO_ORGANIZATION_ID,
2627 p_trans_record.TO_SUBINVENTORY_CODE);
2628
2629 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2630 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2631 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2632 'l_actual_trans_type=' || l_actual_trans_type);
2633 end if;
2634
2635 l_Trans_Items(1) := p_trans_record;
2636
2637 if l_actual_trans_type = 'SUBINVTRANS' then
2638
2639 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2640 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2641 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2642 'Before calling transact_subinv_transfer...');
2643 end if;
2644
2645 transact_subinv_transfer(
2646 P_Api_Version_Number => 1.0,
2647 P_Init_Msg_List => FND_API.G_TRUE,
2648 P_Commit => FND_API.G_TRUE,
2649 p_Trans_Items => l_Trans_Items,
2650 X_Return_Status => x_return_status,
2651 X_Msg_Count => x_msg_count,
2652 X_Msg_Data => x_msg_data
2653 );
2654
2655 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2656 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2657 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2658 'After calling transact_subinv_transfer...');
2659 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2660 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2661 'x_return_status=' || x_return_status ||
2662 ', x_msg_count=' || x_msg_count ||
2663 ', x_msg_data=' || x_msg_data);
2664 end if;
2665
2666 elsif l_actual_trans_type like 'INTORG%' then
2667
2668 l_if_intransit := false;
2669 if l_actual_trans_type = 'INTORG_I' then
2670 l_if_intransit := true;
2671 end if;
2672
2673 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2674 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2675 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2676 'Before calling transact_intorg_transfer...');
2677 end if;
2678
2679 transact_intorg_transfer(
2680 P_Api_Version_Number => 1.0,
2681 P_Init_Msg_List => FND_API.G_TRUE,
2682 P_Commit => FND_API.G_FALSE,
2683 p_Trans_Items => l_Trans_Items,
2684 p_if_intransit => l_if_intransit,
2685 X_Return_Status => x_return_status,
2686 X_Msg_Count => x_msg_count,
2687 X_Msg_Data => x_msg_data
2688 );
2689
2690 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2691 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2692 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2693 'After calling transact_intorg_transfer...');
2694 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2695 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2696 'x_return_status=' || x_return_status ||
2697 ', x_msg_count=' || x_msg_count ||
2698 ', x_msg_data=' || x_msg_data);
2699 end if;
2700
2701 end if;
2702
2703
2704 if x_return_status = 'S'
2705 and x_msg_data is NULL
2706 and l_Trans_Items(1).ERROR_MSG is NULL then
2707
2708
2709
2710 if x_return_status = 'S' then
2711 -- check this is for a task
2712 open check_task_req;
2713 fetch check_task_req into l_task_id, l_requirement_line_id;
2714 if check_task_req%notfound then
2715 --close check_task_req;
2716
2717 -- remove this req_line_details link
2718 if l_remaining_quantity = 0 or p_close_short = 'Y' then
2719 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2720 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2721 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2722 'Before calling csp_req_line_details_pkg.delete_row...');
2723 end if;
2724 csp_req_line_details_pkg.delete_row(p_req_line_detail_id);
2725 commit;
2726 end if;
2727
2728 else
2729 -- create new reservation
2730 l_RESERVATION_REC.need_by_date := sysdate;
2731 l_RESERVATION_REC.organization_id := p_trans_record.TO_ORGANIZATION_ID;
2732 l_RESERVATION_REC.sub_inventory_code := p_trans_record.TO_SUBINVENTORY_CODE;
2733 l_RESERVATION_REC.item_id := p_trans_record.INVENTORY_ITEM_ID;
2734 l_RESERVATION_REC.item_uom_code := p_trans_record.UOM_CODE;
2735 l_RESERVATION_REC.quantity_needed := p_trans_record.QUANTITY;
2736 l_RESERVATION_REC.revision := p_trans_record.REVISION;
2737 l_RESERVATION_REC.line_id := l_requirement_line_id;
2738
2739 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2740 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2741 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2742 'Before calling CSP_SCH_INT_PVT.CREATE_RESERVATION...');
2743 end if;
2744
2745 l_new_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(
2746 p_reservation_parts => l_RESERVATION_REC,
2747 x_return_status => x_return_status,
2748 x_msg_data => x_msg_data);
2749
2750 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2751 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2752 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2753 'After calling CSP_SCH_INT_PVT.CREATE_RESERVATION...');
2754 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2755 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2756 'x_return_status=' || x_return_status ||
2757 ', x_msg_data=' || x_msg_data ||
2758 ', l_new_reservation_id=' || l_new_reservation_id);
2759 end if;
2760
2761 if ((x_return_status = 'S') and (l_new_reservation_id is not null)) then
2762 l_req_line_detail_id := p_req_line_detail_id;
2763
2764 if l_remaining_quantity = 0 or p_close_short = 'Y' then
2765
2766 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2767 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2768 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2769 'Before calling csp_req_line_details_pkg.Update_Row...');
2770 end if;
2771
2772 csp_req_line_details_pkg.Update_Row(px_REQ_LINE_DETAIL_ID => l_req_line_detail_id,
2773 p_REQUIREMENT_LINE_ID => l_requirement_line_id,
2774 p_CREATED_BY => FND_GLOBAL.user_id,
2775 p_CREATION_DATE => sysdate,
2776 p_LAST_UPDATED_BY => FND_GLOBAL.user_id,
2777 p_LAST_UPDATE_DATE => sysdate,
2778 p_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id,
2779 p_SOURCE_TYPE => 'RES',
2780 p_SOURCE_ID => l_new_reservation_id);
2781 else
2782
2783 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2784 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2785 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2786 'Before calling csp_req_line_details_pkg.Insert_Row...');
2787 end if;
2788
2789 csp_req_line_details_pkg.Insert_Row(
2790 px_REQ_LINE_DETAIL_ID => l_req_line_detail_id,
2791 p_REQUIREMENT_LINE_ID => l_requirement_line_id,
2792 p_CREATED_BY => FND_GLOBAL.user_id,
2793 p_CREATION_DATE => sysdate,
2794 p_LAST_UPDATED_BY => FND_GLOBAL.user_id,
2795 p_LAST_UPDATE_DATE => sysdate,
2796 p_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id,
2797 p_SOURCE_TYPE => 'RES',
2798 p_SOURCE_ID => l_new_reservation_id);
2799 end if;
2800 commit;
2801 end if;
2802
2803 end if;
2804 close check_task_req;
2805
2806 end if;
2807 end if;
2808 elsif p_trans_type = 'MO' then
2809 -- call MO receiving API
2810 CSP_MO_MTLTXNS_UTIL.confirm_receipt(P_Api_Version_Number => 1.0,
2811 P_Init_Msg_List => FND_API.G_FALSE,
2812 P_Commit => FND_API.G_FALSE,
2813 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2814 p_packlist_line_id => p_trans_record.PACKLIST_LINE_ID,
2815 p_organization_id => p_trans_record.TO_ORGANIZATION_ID,
2816 p_transaction_temp_id => p_trans_record.TEMP_TRANSACTION_ID,
2817 p_quantity_received => p_trans_record.QUANTITY,
2818 p_to_subinventory_code => p_trans_record.TO_SUBINVENTORY_CODE,
2819 p_to_locator_id => p_trans_record.TO_LOCATOR_ID,
2820 p_serial_number => p_trans_record.SERIAL_NUMBER,
2821 p_lot_number => p_trans_record.LOT_NUMBER,
2822 p_revision => p_trans_record.REVISION,
2823 p_receiving_option => 0, --0 = receiving normal, 1 = receipt short, 2 = over receipt (but do not close the packlist and move order, 3 = over receipt (close everything)
2824 px_transaction_header_id => l_mo_transaction_header_id,
2825 p_process_flag => fnd_api.g_true,
2826 X_Return_Status => x_return_status,
2827 X_Msg_Count => x_msg_count,
2828 X_Msg_Data => x_msg_data);
2829
2830 elsif p_trans_type = 'IO' then
2831
2832 l_oe_header_id := p_trans_header_id;
2833 l_oe_line_id := p_trans_line_id;
2834 l_rcv_ship_line_id := p_trans_record.SHIPMENT_LINE_ID;
2835
2836 open get_rcv_header_data;
2837 fetch get_rcv_header_data into
2838 l_rcv_ship_header_id,
2839 l_rcv_ship_line_id,
2840 l_receive_hdr_rec.source_type_code,
2841 l_receive_hdr_rec.receipt_source_code,
2842 l_receive_hdr_rec.rcv_shipment_num,
2843 l_receive_hdr_rec.ship_to_org_id,
2844 l_receive_hdr_rec.bill_of_lading,
2845 l_receive_hdr_rec.packing_slip,
2846 l_receive_hdr_rec.shipped_date,
2847 l_receive_hdr_rec.freight_carrier_code,
2848 l_receive_hdr_rec.expected_receipt_date,
2849 l_receive_hdr_rec.waybill_airbill_num,
2850 l_receive_hdr_rec.receipt_num;
2851 close get_rcv_header_data;
2852
2853 l_receive_hdr_rec.receipt_header_id := l_rcv_ship_header_id;
2854
2855 open get_rcv_line_data;
2856 fetch get_rcv_line_data into
2857 l_rcv_rec_type.source_type_code,
2858 l_rcv_rec_type.order_type_code,
2859 l_rcv_rec_type.item_id,
2860 l_rcv_rec_type.item_revision,
2861 l_rcv_rec_type.item_category_id,
2862 l_rcv_rec_type.item_description,
2863 l_rcv_rec_type.from_organization_id,
2864 l_rcv_rec_type.ordered_qty,
2865 l_rcv_rec_type.ordered_uom,
2866 l_rcv_rec_type.transaction_quantity,
2867 l_rcv_rec_type.req_line_id,
2868 l_rcv_rec_type.receipt_source_code,
2869 l_rcv_rec_type.lot_number,
2870 l_rcv_rec_type.primary_uom,
2871 l_rcv_rec_type.primary_uom_class,
2872 l_rcv_rec_type.fm_serial_number,
2873 l_rcv_rec_type.to_organization_id,
2874 l_rcv_rec_type.destination_subinventory,
2875 l_rcv_rec_type.destination_type_code,
2876 l_rcv_rec_type.routing_id,
2877 l_rcv_rec_type.ship_to_location_id,
2878 l_rcv_rec_type.enforce_ship_to_location_code,
2879 l_rcv_rec_type.set_of_books_id_sob,
2880 l_rcv_rec_type.currency_code_sob,
2881 l_rcv_rec_type.serial_number_control_code,
2882 l_rcv_rec_type.lot_control_code,
2883 l_rcv_rec_type.lot_quantity,
2884 l_rcv_rec_type.item_revision;
2885 close get_rcv_line_data;
2886
2887 if l_rcv_rec_type.serial_number_control_code = 1 then
2888 l_rcv_rec_type.transaction_quantity := p_trans_record.QUANTITY;
2889 end if;
2890
2891 -- we should change destination subinv from the part req's destination subinv
2892 -- this change is required to make sure other tech can receive a part
2893 -- which was processed and shipped for a different tech
2894 -- condition is old and new destination org is same and only subinv is different
2895 open c_dest_info;
2896 fetch c_dest_info into l_req_dest_org_id, l_req_dest_subinv;
2897 close c_dest_info;
2898
2899 if l_req_dest_org_id = l_rcv_rec_type.to_organization_id
2900 and l_req_dest_subinv <> l_rcv_rec_type.destination_subinventory then
2901 l_rcv_rec_type.destination_subinventory := l_req_dest_subinv;
2902 end if;
2903
2904 l_rcv_rec_type.transaction_uom := l_rcv_rec_type.ordered_uom;
2905 l_rcv_rec_type.rcv_shipment_header_id := l_rcv_ship_header_id;
2906 l_rcv_rec_type.rcv_shipment_line_id := l_rcv_ship_line_id;
2907 l_rcv_rec_type.oe_order_header_id := l_oe_header_id;
2908 l_rcv_rec_type.oe_order_line_id := l_oe_line_id;
2909 l_rcv_rec_type.to_serial_number := l_rcv_rec_type.fm_serial_number;
2910 l_rcv_rec_type.product_code := 'RCV';
2911 l_rcv_rec_type.locator_id := p_trans_record.TO_LOCATOR_ID;
2912 l_receive_rec_tbl(1) := l_rcv_rec_type;
2913
2914 CSP_RECEIVE_PVT.receive_shipments(
2915 P_Api_Version_Number => 1.0,
2916 P_init_Msg_List => 'T',
2917 P_Commit => 'T',
2918 P_Validation_Level => 100,
2919 p_receive_hdr_rec => l_receive_hdr_rec,
2920 p_receive_rec_tbl => l_receive_rec_tbl,
2921 X_Return_Status => x_return_status,
2922 X_Msg_Count => x_msg_count,
2923 X_Msg_Data => x_msg_data
2924 );
2925
2926 if x_return_status = 'S' then
2927 -- bug 12681895
2928 open check_task_req;
2929 fetch check_task_req into l_task_id, l_requirement_line_id;
2930 close check_task_req;
2931
2932 if l_task_id is not null then
2933
2934 SELECT NVL(h.need_by_date, sysdate)
2935 INTO l_hdr_need_by
2936 FROM csp_requirement_headers h,
2937 csp_requirement_lines l
2938 WHERE l.requirement_line_id = l_requirement_line_id
2939 AND l.requirement_header_id = h.requirement_header_id;
2940
2941 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2942 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2943 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2944 'l_hdr_need_by = ' || to_char(l_hdr_need_by, 'DD-MON-YYYY HH24:MI:SS'));
2945 end if;
2946
2947 -- create new reservation
2948 l_RESERVATION_REC.need_by_date := l_hdr_need_by;
2949 l_RESERVATION_REC.organization_id := p_trans_record.TO_ORGANIZATION_ID;
2950 l_RESERVATION_REC.sub_inventory_code := p_trans_record.TO_SUBINVENTORY_CODE;
2951 l_RESERVATION_REC.item_id := p_trans_record.INVENTORY_ITEM_ID;
2952 l_RESERVATION_REC.item_uom_code := p_trans_record.UOM_CODE;
2953 l_RESERVATION_REC.quantity_needed := p_trans_record.QUANTITY;
2954 l_RESERVATION_REC.revision := l_rcv_rec_type.item_revision;
2955 l_RESERVATION_REC.line_id := l_requirement_line_id;
2956
2957 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2958 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2959 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2960 'Before calling CSP_SCH_INT_PVT.CREATE_RESERVATION...');
2961 end if;
2962
2963 l_new_reservation_id := CSP_SCH_INT_PVT.CREATE_RESERVATION(
2964 p_reservation_parts => l_RESERVATION_REC,
2965 x_return_status => x_return_status,
2966 x_msg_data => x_msg_data);
2967
2968 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2969 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2970 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2971 'After calling CSP_SCH_INT_PVT.CREATE_RESERVATION...');
2972 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2973 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2974 'x_return_status=' || x_return_status ||
2975 ', x_msg_data=' || x_msg_data ||
2976 ', l_new_reservation_id=' || l_new_reservation_id);
2977 end if;
2978
2979 if ((x_return_status = 'S') and (l_new_reservation_id is not null)) then
2980 l_req_line_detail_id := p_req_line_detail_id;
2981
2982 -- check how much reserved
2983 select reservation_quantity into l_total_reserved_qty
2984 from mtl_reservations where reservation_id = l_new_reservation_id;
2985
2986 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2987 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
2988 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
2989 'l_total_reserved_qty = ' || l_total_reserved_qty);
2990 end if;
2991
2992 SELECT nvl(SUM(mr.reservation_quantity), 0)
2993 INTO l_already_res_qty
2994 FROM mtl_reservations mr,
2995 csp_req_line_details cd
2996 WHERE mr.reservation_id = cd.source_id
2997 AND cd.source_type = 'RES'
2998 AND cd.requirement_line_id = l_requirement_line_id
2999 AND cd.source_id <> l_new_reservation_id;
3000
3001 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3002 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3003 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
3004 'l_already_res_qty = ' || l_already_res_qty);
3005 end if;
3006
3007 -- check how much required
3008 SELECT req.quantity
3009 INTO l_total_req_qty
3010 FROM po_requisition_lines_all req,
3011 oe_order_lines_all ord,
3012 csp_req_line_details csp
3013 WHERE req.requisition_line_id = ord.source_document_line_id
3014 AND ord.line_id = csp.source_id
3015 AND csp.source_type = 'IO'
3016 AND csp.req_line_detail_id = l_req_line_detail_id;
3017
3018 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3019 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3020 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
3021 'l_total_req_qty = ' || l_total_req_qty);
3022 end if;
3023
3024 SELECT COUNT(*)
3025 INTO l_res_exists
3026 FROM csp_req_line_details
3027 WHERE requirement_line_id = l_requirement_line_id
3028 AND source_type = 'RES'
3029 AND source_id = l_new_reservation_id;
3030
3031 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3032 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3033 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
3034 'l_res_exists = ' || l_res_exists);
3035 end if;
3036
3037 if (l_total_reserved_qty + l_already_res_qty) >= l_total_req_qty then
3038
3039 if l_res_exists = 0 then
3040 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3041 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3042 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
3043 'Before calling csp_req_line_details_pkg.Insert_Row...');
3044 end if;
3045
3046 l_req_line_detail_id := null;
3047 csp_req_line_details_pkg.Insert_Row(px_REQ_LINE_DETAIL_ID => l_req_line_detail_id,
3048 p_REQUIREMENT_LINE_ID => l_requirement_line_id,
3049 p_CREATED_BY => FND_GLOBAL.user_id,
3050 p_CREATION_DATE => sysdate,
3051 p_LAST_UPDATED_BY => FND_GLOBAL.user_id,
3052 p_LAST_UPDATE_DATE => sysdate,
3053 p_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id,
3054 p_SOURCE_TYPE => 'RES',
3055 p_SOURCE_ID => l_new_reservation_id);
3056 else
3057 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3058 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3059 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
3060 'deleting.... l_req_line_detail_id = ' || l_req_line_detail_id);
3061 end if;
3062 --csp_req_line_details_pkg.delete_row(l_req_line_detail_id);
3063 end if;
3064 else
3065
3066 if l_res_exists = 0 then
3067 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3068 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3069 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
3070 'Before calling csp_req_line_details_pkg.Insert_Row...');
3071 end if;
3072
3073 l_req_line_detail_id := null;
3074 csp_req_line_details_pkg.Insert_Row(
3075 px_REQ_LINE_DETAIL_ID => l_req_line_detail_id,
3076 p_REQUIREMENT_LINE_ID => l_requirement_line_id,
3077 p_CREATED_BY => FND_GLOBAL.user_id,
3078 p_CREATION_DATE => sysdate,
3079 p_LAST_UPDATED_BY => FND_GLOBAL.user_id,
3080 p_LAST_UPDATE_DATE => sysdate,
3081 p_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id,
3082 p_SOURCE_TYPE => 'RES',
3083 p_SOURCE_ID => l_new_reservation_id);
3084 end if;
3085
3086 end if; -- if l_remaining_quantity = 0 then
3087 end if; -- if ((x_return_status = 'S') and
3088 end if; -- if l_task_id is not null then
3089 commit;
3090 end if; -- if x_return_status = 'S' then
3091 end if; -- if IO
3092
3093 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3094 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3095 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
3096 'Leaving receive_requirement_trans...');
3097 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3098 'csp.plsql.CSP_TRANSACTIONS_PUB.receive_requirement_trans',
3099 'x_return_status=' || x_return_status ||
3100 ', x_msg_count=' || x_msg_count ||
3101 ', x_msg_data=' || x_msg_data);
3102 end if;
3103
3104 END receive_requirement_trans;
3105
3106 FUNCTION getPartsReturnOrderType (
3107 p_source_org_id IN NUMBER,
3108 p_source_subinv IN VARCHAR2,
3109 p_dest_org_id IN NUMBER,
3110 p_dest_subinv IN VARCHAR2
3111 ) RETURN VARCHAR2 IS
3112
3113 v_ret_order_type varchar2(15);
3114 v_auto_receipt_flag varchar2(1);
3115 v_stocking_site_type varchar2(30);
3116 v_intransit_flag number;
3117 v_internal_order_req_flag number;
3118
3119 CURSOR get_auto_receipt_flag(cp_org_id number, cp_subinv varchar2) IS
3120 select autoreceipt_flag
3121 from csp_sec_inventories
3122 where organization_id = cp_org_id
3123 and secondary_inventory_name = cp_subinv;
3124
3125 CURSOR getStockingSiteType(cp_org_id number, cp_subinv varchar2) IS
3126 select nvl(stocking_site_type, 'TECHNICIAN')
3127 from csp_stocking_site_details_v
3128 where organization_id = cp_org_id
3129 and nvl(subinventory_code, 'NULL') = nvl(cp_subinv, 'NULL');
3130
3131 CURSOR getIntransitDetail(cp_s_org_id number, cp_d_org_id number) IS
3132 select internal_order_required_flag,
3133 INTRANSIT_TYPE
3134 from MTL_SHIPPING_NETWORK_VIEW
3135 where from_organization_id = cp_s_org_id
3136 and to_organization_id = cp_d_org_id;
3137 BEGIN
3138 v_ret_order_type := null;
3139 v_stocking_site_type := 'TECHNICIAN'; -- default assum 'TECHNICIAN'
3140
3141 IF p_source_org_id = p_dest_org_id THEN
3142 if nvl(p_source_subinv, 'NULL') = nvl(p_dest_subinv, 'NULL') then
3143 v_ret_order_type := 'RES';
3144 else
3145 -- bug # 13345661
3146 -- even if same organization, if the source is Manned Warehouse,
3147 -- we should create Internal Order
3148 open getStockingSiteType(p_source_org_id, p_source_subinv);
3149 fetch getStockingSiteType into v_stocking_site_type;
3150 close getStockingSiteType;
3151
3152 if v_stocking_site_type = 'MANNED' then
3153 v_ret_order_type := 'IO';
3154 else
3155 open get_auto_receipt_flag(p_dest_org_id, p_dest_subinv);
3156 fetch get_auto_receipt_flag into v_auto_receipt_flag;
3157 close get_auto_receipt_flag;
3158
3159 if v_auto_receipt_flag = 'Y' then
3160 v_ret_order_type := 'SUBINVTRANS';
3161 else
3162 v_ret_order_type := 'MO';
3163 end if;
3164 end if;
3165 end if;
3166 ELSE
3167 open getStockingSiteType(p_source_org_id, p_source_subinv);
3168 fetch getStockingSiteType into v_stocking_site_type;
3169 close getStockingSiteType;
3170
3171 if v_stocking_site_type = 'TECHNICIAN' then
3172 open getIntransitDetail(p_source_org_id, p_dest_org_id);
3173 fetch getIntransitDetail into v_internal_order_req_flag, v_intransit_flag;
3174 close getIntransitDetail;
3175
3176 if v_internal_order_req_flag = 2 then -- IO not required
3177 if v_intransit_flag = 1 then -- Direct
3178 v_ret_order_type := 'INTORG_D';
3179 else
3180 v_ret_order_type := 'INTORG_I';
3181 end if;
3182 else
3183 v_ret_order_type := 'IO'; -- I'm not sure if it is correct
3184 end if;
3185 else -- source is warehouse
3186 if v_stocking_site_type = 'MANNED' then
3187 v_ret_order_type := 'IO';
3188 else
3189 v_ret_order_type := 'INTORG_D';
3190 end if;
3191 end if;
3192 END IF;
3193
3194 return (v_ret_order_type);
3195 END getPartsReturnOrderType;
3196
3197 function res_for_rcv_trans(p_subscription_guid IN RAW,
3198 p_event IN OUT NOCOPY wf_event_t) return varchar2 is
3199
3200 l_shipment_header_id number;
3201 l_reservation_parts CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
3202 l_reservation_id NUMBER;
3203 l_req_line_detali_id NUMBER;
3204 x_return_Status varchar2(1);
3205 x_msg_data varchar2(4000);
3206
3207 cursor c_ship_detail is
3208 SELECT rsl.to_organization_id AS organization_id,
3209 rsl.to_subinventory AS subinv_code,
3210 rsl.item_id,
3211 rsl.item_revision AS revision,
3212 rsl.quantity_received AS rcv_qty,
3213 (SELECT quantity
3214 FROM po_requisition_lines_all
3215 WHERE requisition_line_id = oola.source_document_line_id
3216 ) AS ord_qty,
3217 mmt.transaction_uom AS uom,
3218 NVL(crh.need_by_date, sysdate) AS need_by_date,
3219 crld.req_line_detail_id,
3220 crl.requirement_line_id
3221 FROM csp_requirement_headers crh,
3222 csp_requirement_lines crl,
3223 csp_req_line_details crld,
3224 rcv_shipment_lines rsl,
3225 oe_order_lines_all oola,
3226 mtl_material_transactions mmt
3227 WHERE rsl.SHIPMENT_HEADER_ID = l_shipment_header_id
3228 AND rsl.ROUTING_HEADER_ID = -1 -- only direct shipment receive
3229 AND rsl.mmt_transaction_id = mmt.transaction_id
3230 AND rsl.requisition_line_id = oola.source_document_line_id
3231 AND oola.line_id = crld.source_id
3232 AND crld.source_type = 'IO'
3233 AND crld.requirement_line_id = crl.requirement_line_id
3234 AND crl.requirement_header_id = crh.requirement_header_id
3235 AND crh.task_id IS NOT NULL;
3236
3237 l_res_exists number;
3238 l_already_res_qty number;
3239 l_total_reserved_qty number;
3240
3241 begin
3242
3243 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3244 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3245 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3246 'begin...');
3247 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3248 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3249 'event name = ' || p_event.geteventname());
3250 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3251 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3252 'event key = ' || p_event.geteventkey());
3253 end if;
3254
3255 l_shipment_header_id := p_event.GetValueForParameter('SHIPMENT_HEADER_ID');
3256
3257 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3258 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3259 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3260 'l_shipment_header_id = ' || l_shipment_header_id);
3261 end if;
3262
3263 for grd in c_ship_detail loop
3264
3265 l_reservation_id := NULL;
3266 l_reservation_parts.need_by_date := grd.need_by_date;
3267 l_reservation_parts.organization_id := grd.organization_id ;
3268 l_reservation_parts.item_id := grd.item_id;
3269 l_reservation_parts.item_uom_code := grd.uom;
3270 l_reservation_parts.quantity_needed := grd.rcv_qty ;
3271 l_reservation_parts.sub_inventory_code := grd.subinv_code ;
3272 l_reservation_parts.revision := grd.revision;
3273
3274 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3275 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3276 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3277 'l_reservation_parts.need_by_date = '
3278 || to_char(l_reservation_parts.need_by_date, 'DD-MON-YYYY HH24:MI:SS'));
3279 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3280 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3281 'l_reservation_parts.organization_id = ' || l_reservation_parts.organization_id);
3282 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3283 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3284 'l_reservation_parts.item_id = ' || l_reservation_parts.item_id);
3285 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3286 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3287 'l_reservation_parts.item_uom_code = ' || l_reservation_parts.item_uom_code);
3288 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3289 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3290 'l_reservation_parts.quantity_needed = ' || l_reservation_parts.quantity_needed);
3291 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3292 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3293 'l_reservation_parts.sub_inventory_code = ' || l_reservation_parts.sub_inventory_code);
3294 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3295 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3296 'l_reservation_parts.revision = ' || l_reservation_parts.revision);
3297 end if;
3298
3299 l_reservation_id := csp_sch_int_pvt.CREATE_RESERVATION(l_reservation_parts
3300 ,x_return_status
3301 ,x_msg_data );
3302
3303 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3304 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3305 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3306 'l_reservation_id = ' || l_reservation_id);
3307 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3308 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3309 'x_return_status = ' || x_return_status);
3310 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3311 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3312 'x_msg_data = ' || x_msg_data);
3313 end if;
3314
3315
3316 SELECT COUNT(*)
3317 INTO l_res_exists
3318 FROM csp_req_line_details
3319 WHERE requirement_line_id = grd.requirement_line_id
3320 AND source_type = 'RES'
3321 AND source_id = l_reservation_id;
3322
3323 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3324 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3325 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3326 'l_res_exists = ' || l_res_exists);
3327 end if;
3328
3329 l_req_line_detali_id := NULL;
3330
3331 IF l_reservation_id IS NOT NULL AND l_reservation_id > 0 THEN
3332
3333 if l_res_exists = 0 then
3334 csp_req_line_details_pkg.insert_row(px_req_line_detail_id => l_req_line_detali_id
3335 ,p_requirement_line_id => grd.requirement_line_id
3336 ,p_created_by => FND_GLOBAL.user_id
3337 ,p_creation_date => sysdate
3338 ,p_last_updated_by => FND_GLOBAL.user_id
3339 ,p_last_update_date => sysdate
3340 ,p_last_update_login => FND_GLOBAL.login_id
3341 ,p_source_type => 'RES'
3342 ,p_source_id => l_reservation_id );
3343 end if;
3344
3345 select reservation_quantity into l_total_reserved_qty
3346 from mtl_reservations where reservation_id = l_reservation_id;
3347
3348 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3349 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3350 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3351 'l_total_reserved_qty = ' || l_total_reserved_qty);
3352 end if;
3353
3354 SELECT nvl(SUM(mr.reservation_quantity), 0)
3355 INTO l_already_res_qty
3356 FROM mtl_reservations mr,
3357 csp_requirement_lines cl,
3358 csp_req_line_details cd
3359 WHERE mr.reservation_id = cd.source_id
3360 AND cd.source_type = 'RES'
3361 AND cl.requirement_line_id = grd.requirement_line_id
3362 AND cd.requirement_line_id = cl.requirement_line_id
3363 AND cd.source_id <> l_reservation_id;
3364
3365 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3366 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3367 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3368 'l_already_res_qty = ' || l_already_res_qty);
3369 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3370 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3371 'grd.ord_qty = ' || grd.ord_qty);
3372 end if;
3373
3374 IF (l_total_reserved_qty + l_already_res_qty) >= grd.ord_qty THEN
3375 csp_req_line_details_pkg.delete_row(grd.req_line_detail_id);
3376 END IF;
3377
3378 END IF;
3379
3380 end loop; -- for grd in c_ship_detail loop
3381
3382 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3383 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3384 'csp.plsql.CSP_TRANSACTIONS_PUB.res_for_rcv_trans',
3385 'done...');
3386 end if;
3387
3388 return 'SUCCESS';
3389 end res_for_rcv_trans;
3390
3391 function gen_numbers(n in number default null)
3392 return csparray PIPELINED
3393 is
3394 begin
3395 for i in 1 .. nvl(n,999999999)
3396 loop
3397 pipe row(i);
3398 end loop;
3399 return;
3400 end gen_numbers;
3401
3402 END csp_transactions_pub;