[Home] [Help]
PACKAGE BODY: APPS.CSP_RECEIVE_PVT
Source
1 PACKAGE BODY CSP_RECEIVE_PVT AS
2 /* $Header: cspvrcvb.pls 120.6 2008/04/25 11:18:01 htank ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSP_RECEIVE_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspvrcvb.pls';
6
7 Procedure Get_Messages(x_message OUT NOCOPY Varchar2) Is
8 l_msg_index_out NUMBER;
9 x_msg_data_temp Varchar2(2000);
10 x_msg_data Varchar2(4000);
11 Begin
12 If fnd_msg_pub.count_msg > 0 Then
13 FOR i IN REVERSE 1..fnd_msg_pub.count_msg Loop
14 fnd_msg_pub.get(p_msg_index => i,
15 p_encoded => 'F',
16 p_data => x_msg_data_temp,
17 p_msg_index_out => l_msg_index_out);
18 x_msg_data := x_msg_data || x_msg_data_temp;
19 End Loop;
20 x_message := substr(x_msg_data,1,2000);
21 -- fnd_msg_pub.delete_msg;
22 End if;
23 End;
24
25 PROCEDURE gen_receipt_num(
26 x_receipt_num OUT NOCOPY VARCHAR2
27 , p_organization_id NUMBER
28 , x_return_status OUT NOCOPY VARCHAR2
29 , x_msg_count OUT NOCOPY NUMBER
30 , x_msg_data OUT NOCOPY VARCHAR2
31 ) IS
32 PRAGMA AUTONOMOUS_TRANSACTION;
33 l_receipt_exists NUMBER;
34 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
35 l_msg_count NUMBER;
36 l_msg_data VARCHAR2(400);
37 l_progress VARCHAR2(10);
38 l_receipt_code VARCHAR2(25);
39 l_api_name VARCHAR2(25) := 'gen_receipt_num';
40 BEGIN
41 x_return_status := fnd_api.g_ret_sts_success;
42
43 UPDATE rcv_parameters
44 SET next_receipt_num = next_receipt_num + 1
45 WHERE organization_id = p_organization_id
46 RETURNING next_receipt_num INTO x_receipt_num;
47
48 COMMIT;
49
50 BEGIN
51 SELECT 1
52 INTO l_receipt_exists
53 FROM rcv_shipment_headers rsh
54 WHERE receipt_num = x_receipt_num
55 AND ship_to_org_id = p_organization_id;
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 l_receipt_exists := 0;
59 WHEN OTHERS THEN
60 RAISE;
61 END;
62
63 IF (l_receipt_exists = 1) THEN
64 fnd_message.set_name('CSP','CSP_RECEIPT_NUM_EXISTS');
65 fnd_message.set_token('RECEIPT',x_receipt_num,false);
66 fnd_msg_pub.add;
67 RAISE FND_API.G_EXC_ERROR;
68 END IF;
69
70 EXCEPTION
71 WHEN FND_API.G_EXC_ERROR THEN
72 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
73 P_API_NAME => L_API_NAME
74 ,P_PKG_NAME => G_PKG_NAME
75 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
76 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
77 ,X_MSG_COUNT => X_MSG_COUNT
78 ,X_MSG_DATA => X_MSG_DATA
79 ,X_RETURN_STATUS => X_RETURN_STATUS);
80 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
81 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
82 P_API_NAME => L_API_NAME
83 ,P_PKG_NAME => G_PKG_NAME
84 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
85 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
86 ,X_MSG_COUNT => X_MSG_COUNT
87 ,X_MSG_DATA => X_MSG_DATA
88 ,X_RETURN_STATUS => X_RETURN_STATUS);
89 WHEN OTHERS THEN
90 fnd_message.set_name('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
91 fnd_message.set_token('ROUTINE',l_api_name,false);
92 fnd_message.set_token('SQLERRM',sqlerrm,false);
93 fnd_msg_pub.add;
94 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
95 P_API_NAME => L_API_NAME
96 ,P_PKG_NAME => G_PKG_NAME
97 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
98 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
99 ,X_MSG_COUNT => X_MSG_COUNT
100 ,X_MSG_DATA => X_MSG_DATA
101 ,X_RETURN_STATUS => X_RETURN_STATUS);
102 END gen_receipt_num;
103
104 FUNCTION get_employee (emp_id OUT NOCOPY number,
105 emp_name OUT NOCOPY varchar2,
106 location_id OUT NOCOPY number,
107 location_code OUT NOCOPY varchar2,
108 is_buyer OUT NOCOPY BOOLEAN,
109 emp_flag OUT NOCOPY BOOLEAN
110 )
111 RETURN BOOLEAN IS
112
113 X_user_id varchar2(80) ;/* stores the user id */
114 X_emp_id NUMBER := 0 ; /* stores the employee_id */
115 X_location_id NUMBER := 0 ; /* stores the location_id */
116 X_emp_name VARCHAR2(240) := '' ; /* stores the employee_name */
117
118 X_location_code hr_locations_all.location_code%TYPE :='';
119 X_buyer_code VARCHAR2(1) := 'Y' ; /* dummy, stores buyer status */
120 mesg_buffer VARCHAR2(2000) := '' ; /* for handling error messages */
121 X_progress varchar2(3) := '';
122 l_api_name Varchar2(25) := 'get_employee';
123 x_msg_count number;
124 x_msg_data varchar2(4000);
125 x_return_status varchar2(1);
126
127 BEGIN
128 /* get user id */
129
130 FND_PROFILE.GET('USER_ID', X_user_id);
131 if X_user_id is null then
132 fnd_message.set_name('CSP','CSP_INVALID_USER_ID');
133 fnd_msg_pub.add;
134 return False;
135 end if;
136 BEGIN
137 SELECT HR.EMPLOYEE_ID,
138 HR.FULL_NAME,
139 NVL(HR.LOCATION_ID,0)
140 INTO X_emp_id,
141 X_emp_name,
142 X_location_id
143 FROM FND_USER FND, PER_EMPLOYEES_CURRENT_X HR
144 WHERE FND.USER_ID = X_user_id
145 AND FND.EMPLOYEE_ID = HR.EMPLOYEE_ID
146 AND ROWNUM = 1;
147 /* if no rows selected
148 then user is not an employee
149 else user is an employee */
150
151 emp_flag := TRUE;
152
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 /* the user is not an employee */
156 emp_flag := FALSE ;
157 return(TRUE) ;
158 WHEN OTHERS THEN
159 raise;
160 END ;
161
162
163 /* get location_code */
164
165 IF (X_location_id <> 0) THEN
166 BEGIN
167
168 /* if location id belongs to an org
169 if the org is in the current set of books
170 return location code
171 else
172 return location id is 0
173
174 */
175 select hr.location_code
176 into x_location_code
177 from hr_locations hr,
178 financials_system_parameters fsp,
179 hr_organization_information hoi
180 where hr.location_id = x_location_id
181 and hr.inventory_organization_id = hoi.organization_id
182 and to_char(fsp.set_of_books_id) = hoi.org_information1
183 AND ROWNUM = 1;
184
185 EXCEPTION
186 WHEN NO_DATA_FOUND THEN
187 X_location_id := 0 ;
188 WHEN OTHERS THEN
189 raise;
190 END ;
191 END IF ;
192
193 /* check if employee is a buyer */
194
195 BEGIN
196 SELECT 'Y'
197 INTO X_buyer_code
198 FROM PO_AGENTS
199 WHERE agent_id = X_emp_id
200 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1)
201 AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
202
203 /* if no rows returned
204 then user is not a buyer
205 else user is a buyer */
206
207 is_buyer := TRUE ;
208
209 EXCEPTION
210 WHEN NO_DATA_FOUND THEN
211 /* user is not a buyer */
212 is_buyer := FALSE ;
213 WHEN OTHERS THEN
214 raise;
215 END ;
216
217
218 /* assign all the local variables to the parameters */
219
220 emp_id := X_emp_id;
221 emp_name := X_emp_name ;
222
223
224 IF (X_location_id <> 0) THEN
225 location_id := X_location_id ;
226 location_code := X_location_code ;
227 ELSE
228 location_id := '' ;
229 location_code := '' ;
230 END IF ;
231
232 return(TRUE);
233 exception
234 WHEN FND_API.G_EXC_ERROR THEN
235 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
236 P_API_NAME => L_API_NAME
237 ,P_PKG_NAME => G_PKG_NAME
238 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
239 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
240 ,X_MSG_COUNT => X_MSG_COUNT
241 ,X_MSG_DATA => X_MSG_DATA
242 ,X_RETURN_STATUS => X_RETURN_STATUS);
243 return FALSE;
244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
245 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
246 P_API_NAME => L_API_NAME
247 ,P_PKG_NAME => G_PKG_NAME
248 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
249 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
250 ,X_MSG_COUNT => X_MSG_COUNT
251 ,X_MSG_DATA => X_MSG_DATA
252 ,X_RETURN_STATUS => X_RETURN_STATUS);
253 return FALSE;
254 WHEN OTHERS THEN
255 fnd_message.set_name('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
256 fnd_message.set_token('ROUTINE',l_api_name,false);
257 fnd_message.set_token('SQLERRM',sqlerrm,false);
258 fnd_msg_pub.add;
259 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
260 P_API_NAME => L_API_NAME
261 ,P_PKG_NAME => G_PKG_NAME
262 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
263 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
264 ,X_MSG_COUNT => X_MSG_COUNT
265 ,X_MSG_DATA => X_MSG_DATA
266 ,X_RETURN_STATUS => X_RETURN_STATUS);
267 return FALSE;
268 END get_employee ;
269
270 PROCEDURE receive_shipments
271 (P_Api_Version_Number IN NUMBER,
272 P_init_Msg_List IN VARCHAR2,
273 P_Commit IN VARCHAR2,
274 P_Validation_Level IN NUMBER,
275 p_receive_hdr_rec IN rcv_hdr_rec_type,
276 p_receive_rec_tbl IN rcv_rec_tbl_type,
277 X_Return_Status OUT NOCOPY VARCHAR2,
278 X_Msg_Count OUT NOCOPY NUMBER,
279 X_Msg_Data OUT NOCOPY VARCHAR2) IS
280 l_sqlcode NUMBER;
281 l_sqlerrm Varchar2(4000);
282 l_api_name CONSTANT VARCHAR2(30) := 'receive_shipments';
283 l_api_version_number CONSTANT NUMBER := 1.0;
284 l_return_status_full VARCHAR2(1);
285 x_message VARCHAR2(2000);
286
287 Cursor c_lot_exists(p_lot_number Varchar2,p_item_id number, p_org_id number) Is
288 Select 'Y'
289 FROM mtl_lot_numbers
290 WHERE lot_number = Ltrim(Rtrim(p_lot_number))
291 AND inventory_item_id = p_item_id
292 AND organization_id = p_org_id;
293
294 Cursor c_serial_exists(p_serial_number Varchar2,p_item_id number) Is
295 Select 'Y'
296 FROM mtl_serial_numbers
297 WHERE serial_number = p_serial_number
298 AND inventory_item_id = p_item_id;
299
300 Cursor c_Subinventory(p_inv_loc_assignment_id number) Is
301 Select subinventory_code,organization_id
302 from csp_inv_loc_assignments
303 where csp_inv_loc_assignment_id = p_inv_loc_assignment_id;
304
305 l_organization_id Number;
306 l_subinventory Varchar2(10);
307 l_lot_exists Varchar2(1);
308 l_serial_exists Varchar2(1);
309 l_lot_interface_id Number;
310 l_serial_interface_id Number;
311 x_serial_transaction_temp_id Number;
312 x_interface_transaction_id Number;
313 l_header_interface_id Number;
314 l_group_id Number;
315 l_source_doc_code Varchar2(25);
316 L_rcv_transaction_rec rcv_rec_type;
317
318
319 l_employee_id NUMBER;
320 l_employee_name VARCHAR2(240);
321 l_location_code VARCHAR2(60);
322 l_location_id NUMBER;
323 l_is_buyer BOOLEAN;
324 l_emp_flag BOOLEAN;
325
326 l_serial_interface_inserted BOOLEAN;
327
328 BEGIN
329 SAVEPOINT receive_shipments_pvt;
330 MO_GLOBAL.init('CSF');
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332
333 -- Standard call to check for call compatibility.
334 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
335 p_api_version_number,
336 l_api_name,
337 G_PKG_NAME)
338 THEN
339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
340 END IF;
341
342
343 -- Initialize message list
344 IF fnd_api.to_boolean(p_init_msg_list) THEN
345 fnd_msg_pub.initialize;
346 END IF;
347
348
349 -- Initialize API return status to SUCCESS
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351
352 If NOT get_employee (l_employee_id,
353 l_employee_name,
354 l_location_id ,
355 l_location_code ,
356 l_is_buyer ,
357 l_emp_flag
358 ) Then
359 GET_MESSAGES(x_msg_data);
360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
361 End If;
362
363 --
364 -- API body
365 --
366 insert_rcv_hdr_interface
367 (P_Api_Version_Number => 1.0
368 ,P_init_Msg_List => FND_API.G_FALSE
369 ,P_Commit => FND_API.G_FALSE
370 ,P_Validation_Level => p_validation_level
371 ,X_Return_Status => X_return_Status
372 ,X_Msg_Count => X_Msg_Count
373 ,X_Msg_Data => X_Msg_Data
374 ,p_header_interface_id => p_receive_hdr_rec.header_interface_id
375 ,p_group_id => p_receive_hdr_rec.group_id
376 ,p_source_type_code => p_receive_hdr_rec.source_type_code
377 ,p_receipt_source_code => p_receive_hdr_rec.receipt_source_code
378 ,p_vendor_id => p_receive_hdr_rec.vendor_id
379 ,p_vendor_site_id => p_receive_hdr_rec.vendor_site_id
380 ,p_ship_to_org_id => p_receive_hdr_rec.ship_to_org_id
384 ,p_bill_of_lading => p_receive_hdr_rec.bill_of_lading
381 ,p_shipment_num => p_receive_hdr_rec.rcv_shipment_num
382 ,p_receipt_header_id => p_receive_hdr_rec.receipt_header_id
383 ,p_receipt_num => p_receive_hdr_rec.receipt_num
385 ,p_packing_slip => p_receive_hdr_rec.packing_slip
386 ,p_shipped_date => p_receive_hdr_rec.shipped_date
387 ,p_freight_carrier_code => p_receive_hdr_rec.freight_carrier_code
388 ,p_expected_receipt_date => p_receive_hdr_rec.expected_receipt_date
389 ,p_employee_id => nvl(p_receive_hdr_rec.employee_id,l_employee_id)
390 ,p_waybill_airbill_num => p_receive_hdr_rec.waybill_airbill_num
391 ,p_usggl_transaction_code => p_receive_hdr_rec.usggl_transaction_code
392 ,p_processing_request_id => p_receive_hdr_rec.processing_request_id
393 ,p_customer_id => p_receive_hdr_rec.customer_id
394 ,p_customer_site_id => p_receive_hdr_rec.customer_site_id
395 ,x_header_interface_id => l_header_interface_id
396 ,x_group_id => l_group_id);
397 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
398 Get_Messages(X_MSG_DATA);
399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 End If;
401 FOR i in 1..p_receive_rec_tbl.COUNT LOOP
402
403 If p_receive_rec_tbl(i).inv_loc_assignment_id is not null Then
404 open c_subinventory(p_receive_rec_tbl(i).inv_loc_assignment_id);
405 fetch c_subinventory into l_subinventory,l_organization_id;
406 close c_subinventory;
407 else
408 l_organization_id := p_receive_rec_tbl(i).to_organization_id;
409 l_subinventory := p_receive_rec_tbl(i).destination_subinventory;
410 End If;
411 l_rcv_transaction_rec := p_receive_rec_tbl(i);
412 l_rcv_transaction_rec.header_interface_id := l_header_interface_id;
413 l_rcv_transaction_rec.group_id := l_group_id;
414 l_rcv_transaction_rec.employee_id := l_employee_id;
415 l_rcv_transaction_rec.to_organization_id := l_organization_id;
416 l_rcv_transaction_rec.destination_subinventory := l_subinventory;
417 l_rcv_transaction_rec.primary_quantity :=
418 rcv_transactions_interface_sv.convert_into_correct_qty(
419 l_rcv_transaction_rec.transaction_quantity,
420 l_rcv_transaction_rec.transaction_uom,
421 l_rcv_transaction_rec.item_id,
422 l_rcv_transaction_rec.primary_uom);
423 insert_rcv_txn_interface
424 (P_Api_Version_Number => 1.0
425 ,P_init_Msg_List => FND_API.G_FALSE
426 ,P_Commit => FND_API.G_FALSE
427 ,P_Validation_Level => p_Validation_Level
428 ,X_Return_Status => X_return_Status
429 ,X_Msg_Count => X_Msg_Count
430 ,X_Msg_Data => X_Msg_Data
431 ,x_interface_transaction_id => x_interface_transaction_id
432 ,p_receive_rec => l_rcv_transaction_rec);
433
434 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
435 Get_Messages(X_MSG_DATA);
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
437 End If;
438 l_serial_interface_inserted := FALSE;
439 l_serial_interface_id := null;
440 l_lot_interface_id := null;
441 -- Lot/Serial
442 If p_receive_rec_tbl(i).fm_serial_number is not null then
443 insert_serial_interface (
444 p_api_version => 1.0
445 , p_init_msg_list => FND_API.G_FALSE
446 , x_return_status => x_return_status
447 , x_msg_count => x_msg_count
448 , x_msg_data => x_msg_data
449 , px_transaction_interface_id => l_serial_interface_id
450 , p_product_transaction_id => x_interface_transaction_id
451 , p_product_code => p_receive_rec_tbl(i).product_code
452 , p_fm_serial_number => p_receive_rec_tbl(i).fm_serial_number
453 , p_to_serial_number => p_receive_rec_tbl(i).to_serial_number);
454 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
455 Get_Messages(X_MSG_DATA);
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 End If;
458 end if;
459 If p_receive_rec_tbl(i).lot_number is not null then
460 insert_lots_interface (
461 p_api_version => 1.0
462 , p_init_msg_list => FND_API.G_FALSE
463 , x_return_status => x_return_status
464 , x_msg_count => x_msg_count
465 , x_msg_data => x_msg_data
466 , p_transaction_interface_id => l_lot_interface_id
467 , p_lot_number => p_receive_rec_tbl(i).lot_number
468 , p_transaction_quantity => p_receive_rec_tbl(i).lot_quantity
469 , p_primary_quantity => p_receive_rec_tbl(i).lot_primary_quantity
470 , p_organization_id => p_receive_rec_tbl(i).to_organization_id
471 , p_inventory_item_id => p_receive_rec_tbl(i).item_id
472 , p_serial_transaction_temp_id => l_serial_interface_id
473 , p_product_transaction_id => x_interface_transaction_id
474 , p_product_code => p_receive_rec_tbl(i).product_code);
475 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
479 End If;
476 Get_Messages(X_MSG_DATA);
477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
478 End If;
480
481 END LOOP;
482
483 COMMIT;
484
485 --- Reestablish the save point
486 SAVEPOINT receive_shipments_pvt;
487 --- Process the interface record
488 rcv_online_request (p_group_id => l_group_id,
489 x_return_status => x_return_status,
490 x_msg_data => x_msg_data);
491 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
492 GET_MESSAGES(x_msg_data);
493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
494 End If;
495 --- Commit;
496 COMMIT;
497 EXCEPTION
498 WHEN FND_API.G_EXC_ERROR THEN
499 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
500 P_API_NAME => L_API_NAME
501 ,P_PKG_NAME => G_PKG_NAME
502 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
503 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
504 ,X_MSG_COUNT => X_MSG_COUNT
505 ,X_MSG_DATA => X_MESSAGE
506 ,X_RETURN_STATUS => X_RETURN_STATUS);
507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
508 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
509 P_API_NAME => L_API_NAME
510 ,P_PKG_NAME => G_PKG_NAME
511 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
512 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
513 ,X_MSG_COUNT => X_MSG_COUNT
514 ,X_MSG_DATA => X_MESSAGE
515 ,X_RETURN_STATUS => X_RETURN_STATUS);
516 WHEN OTHERS THEN
517 fnd_message.set_name('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
518 fnd_message.set_token('ROUTINE',l_api_name,false);
519 fnd_message.set_token('SQLERRM',sqlerrm,false);
520 fnd_msg_pub.add;
521 get_messages(x_msg_data);
522 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
523 P_API_NAME => L_API_NAME
524 ,P_PKG_NAME => G_PKG_NAME
525 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
526 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
527 ,X_MSG_COUNT => X_MSG_COUNT
528 ,X_MSG_DATA => X_MESSAGE
529 ,X_RETURN_STATUS => X_RETURN_STATUS);
530 END;
531
532 PROCEDURE insert_rcv_hdr_interface
533 (P_Api_Version_Number IN NUMBER,
534 P_init_Msg_List IN VARCHAR2,
535 P_Commit IN VARCHAR2,
536 P_Validation_Level IN NUMBER,
537 X_Return_Status OUT NOCOPY VARCHAR2,
538 X_Msg_Count OUT NOCOPY NUMBER,
539 X_Msg_Data OUT NOCOPY VARCHAR2,
540 p_header_interface_id IN NUMBER,
541 p_group_id IN NUMBER,
542 p_receipt_source_code IN VARCHAR2,
543 p_source_type_code IN VARCHAR2,
544 p_vendor_id IN NUMBER,
545 p_vendor_site_id IN NUMBER,
546 p_ship_to_org_id IN NUMBER,
547 p_shipment_num IN VARCHAR2,
548 p_receipt_header_id IN NUMBER,
549 p_receipt_num IN VARCHAR2,
550 p_bill_of_lading IN VARCHAR2,
551 p_packing_slip IN VARCHAR2,
552 p_shipped_date IN DATE,
553 p_freight_carrier_code IN VARCHAR2,
554 p_expected_receipt_date IN DATE,
555 p_employee_id IN NUMBER,
556 p_waybill_airbill_num IN VARCHAR2,
557 p_usggl_transaction_code IN VARCHAR2,
558 p_processing_request_id IN NUMBER,
559 p_customer_id IN NUMBER,
560 p_customer_site_id IN NUMBER,
561 x_header_interface_id OUT NOCOPY NUMBER,
562 x_group_id OUT NOCOPY NUMBER) IS
563
564 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RCV_HDR_INTERFACE';
565 l_api_version_number CONSTANT NUMBER := 1.0;
566
567 l_header_interface_id NUMBER;
568 l_group_id NUMBER;
569 l_receipt_num NUMBER;
570 l_receipt_header_id NUMBER;
571 l_shipment_num VARCHAR2(30);
572
573 BEGIN
574 SAVEPOINT insert_rcv_hdr_interface_pvt;
575
576 -- Standard call to check for call compatibility.
577 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
578 p_api_version_number,
579 l_api_name,
580 G_PKG_NAME)
581 THEN
582 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
583 END IF;
584
585 -- Initialize message list if p_init_msg_list is set to TRUE.
586 IF fnd_api.to_boolean(p_init_msg_list) THEN
587 fnd_msg_pub.initialize;
588 END IF;
589
590 x_return_status := FND_API.G_RET_STS_SUCCESS;
591
592
593 l_header_interface_id := p_header_interface_id;
594 If (l_header_interface_id IS NULL) THEN
595 SELECT rcv_headers_interface_s.NEXTVAL
596 INTO l_header_interface_id
597 FROM sys.dual;
598 End If;
599
600 l_group_id := p_group_id;
601 If (l_group_id IS NULL) THEN
602 SELECT rcv_interface_groups_s.NEXTVAL
603 INTO l_group_id
604 FROM sys.dual;
605 End If;
606 l_receipt_num := p_receipt_num;
607 If l_receipt_num is NULL Then
608 gen_receipt_num(
609 x_receipt_num => l_receipt_num
610 , p_organization_id => p_ship_to_org_id
614 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
611 , x_return_status => x_return_status
612 , x_msg_count => x_msg_count
613 , x_msg_data => x_msg_data);
615 GET_MESSAGES(x_msg_data);
616 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
617 End If;
618 End If;
619 IF p_source_type_code = 'INTERNAL' THEN
620 l_receipt_header_id := p_receipt_header_id;
621 l_shipment_num := p_shipment_num;
622 ELSE
623 l_receipt_header_id := NULL;
624 l_shipment_num := NULL;
625 END IF;
626
627 INSERT INTO RCV_HEADERS_INTERFACE (
628 header_interface_id
629 , group_id
630 , processing_status_code
631 , transaction_type
632 , validation_flag
633 , auto_transact_code
634 , last_update_date
635 , last_updated_by
636 , creation_date
637 , created_by
638 , last_update_login
639 , receipt_source_code
640 , vendor_id
641 , vendor_site_id
642 , ship_to_organization_id
643 , shipment_num
644 , receipt_header_id
645 , receipt_num
646 , bill_of_lading
647 , packing_slip
648 , shipped_date
649 , freight_carrier_code
650 , expected_receipt_date
651 , employee_id
652 , waybill_airbill_num
653 , usggl_transaction_code
654 , processing_request_id
655 , customer_id
656 , customer_site_id)
657 VALUES
658 (l_header_interface_id
659 ,l_group_id
660 ,'PENDING'
661 ,'NEW'
662 ,'Y'
663 ,'RECEIVE'
664 , SYSDATE
665 , FND_GLOBAL.USER_ID
666 , SYSDATE
667 , FND_GLOBAL.USER_ID
668 , FND_GLOBAL.LOGIN_ID
669 , p_receipt_source_code
670 , p_vendor_id
671 , p_vendor_site_id
672 , p_ship_to_org_id
673 , l_shipment_num
674 , l_receipt_header_id
675 , l_receipt_num
676 , p_bill_of_lading
677 , p_packing_slip
678 , p_shipped_date
679 , p_freight_carrier_code
680 , nvl(p_expected_receipt_date,sysdate)
681 , p_employee_id
682 , p_waybill_airbill_num
683 , p_usggl_transaction_code
684 , p_processing_request_id
685 , p_customer_id
686 , p_customer_site_id);
687
688 x_header_interface_id := l_header_interface_id;
689 x_group_id := l_group_id;
690
691 EXCEPTION
692 WHEN FND_API.G_EXC_ERROR THEN
693 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
694 P_API_NAME => L_API_NAME
695 ,P_PKG_NAME => G_PKG_NAME
696 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
697 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
698 ,X_MSG_COUNT => X_MSG_COUNT
699 ,X_MSG_DATA => X_MSG_DATA
700 ,X_RETURN_STATUS => X_RETURN_STATUS);
701 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
702 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
703 P_API_NAME => L_API_NAME
704 ,P_PKG_NAME => G_PKG_NAME
705 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
706 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
707 ,X_MSG_COUNT => X_MSG_COUNT
708 ,X_MSG_DATA => X_MSG_DATA
709 ,X_RETURN_STATUS => X_RETURN_STATUS);
710 WHEN OTHERS THEN
711 fnd_message.set_name('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
712 fnd_message.set_token('ROUTINE',l_api_name,false);
713 fnd_message.set_token('SQLERRM',sqlerrm,false);
714 fnd_msg_pub.add;
715 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
716 P_API_NAME => L_API_NAME
717 ,P_PKG_NAME => G_PKG_NAME
718 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
719 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
720 ,X_MSG_COUNT => X_MSG_COUNT
721 ,X_MSG_DATA => X_MSG_DATA
722 ,X_RETURN_STATUS => X_RETURN_STATUS);
723 END;
724
725
726 PROCEDURE insert_rcv_txn_interface
727 (P_Api_Version_Number IN NUMBER,
728 P_init_Msg_List IN VARCHAR2,
729 P_Commit IN VARCHAR2,
730 P_Validation_Level IN NUMBER,
731 X_Return_Status OUT NOCOPY VARCHAR2,
732 X_Msg_Count OUT NOCOPY NUMBER,
733 X_Msg_Data OUT NOCOPY VARCHAR2,
734 x_interface_transaction_id OUT NOCOPY NUMBER,
735 p_receive_rec IN rcv_rec_type) IS
736 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RCV_TXN_INTERFACE';
737 l_api_version_number CONSTANT NUMBER := 1.0;
738
739 l_transaction_interface_id Number;
740 l_source_code Number;
741 l_source_line_id Number;
742 l_interface_transaction_id NUMBER;
743 x_message Varchar2(2000);
744 l_rcv_transaction_rec rcv_rec_type;
745 l_auto_transact_code VARCHAR2(30);
746 l_shipment_line_id NUMBER;
747 l_primary_uom VARCHAR2(25);
748 l_blind_receiving_flag VARCHAR2(1);
749 l_receipt_source_code VARCHAR2(30);
750 l_vendor_id NUMBER;
751 l_vendor_site_id NUMBER;
752 l_from_org_id NUMBER;
753 l_to_org_id NUMBER;
754 l_source_doc_code VARCHAR2(30);
755 l_po_header_id NUMBER;
759 l_po_distribution_id NUMBER;
756 l_po_release_id NUMBER;
757 l_po_line_id NUMBER;
758 l_po_line_location_id NUMBER;
760 l_req_line_id NUMBER;
761 l_sub_unordered_code VARCHAR2(30);
762 l_deliver_to_person_id NUMBER;
763 l_location_id NUMBER;
764 l_deliver_to_location_id NUMBER;
765 l_subinventory VARCHAR2(10);
766 l_locator_id NUMBER;
767 l_wip_entity_id NUMBER;
768 l_wip_line_id NUMBER;
769 l_department_code VARCHAR2(30);
770 l_wip_rep_sched_id NUMBER;
771 l_wip_oper_seq_num NUMBER;
772 l_wip_res_seq_num NUMBER;
773 l_bom_resource_id NUMBER;
774 l_oe_order_header_id NUMBER;
775 l_oe_order_line_id NUMBER;
776 l_customer_item_num NUMBER;
777 l_customer_id NUMBER;
778 l_customer_site_id NUMBER;
779 l_rate NUMBER;
780 l_rate_date DATE;
781 l_rate_gl NUMBER;
782 l_shipment_header_id NUMBER;
783 l_header_interface_id NUMBER;
784 l_lpn_group_id NUMBER;
785 l_num_of_distributions NUMBER;
786 l_validation_flag VARCHAR2(1);
787 l_project_id NUMBER;
788 l_task_id NUMBER;
789
790 x_available_qty NUMBER;
791 x_ordered_qty NUMBER;
792 x_primary_qty NUMBER;
793 x_tolerable_qty NUMBER;
794 x_uom VARCHAR2(25);
795 x_primary_uom VARCHAR2(25);
796 x_valid_ship_to_location BOOLEAN;
797 x_num_of_distributions NUMBER;
798 x_po_distribution_id NUMBER;
799 x_destination_type_code VARCHAR2(30);
800 x_destination_type_dsp VARCHAR2(80);
801 x_deliver_to_location_id NUMBER;
802 x_deliver_to_location VARCHAR2(80);
803 x_deliver_to_person_id NUMBER;
804 x_deliver_to_person VARCHAR2(240);
805 x_deliver_to_sub VARCHAR2(10);
806 x_deliver_to_locator_id NUMBER;
807 x_wip_entity_id NUMBER;
808 x_wip_repetitive_schedule_id NUMBER;
809 x_wip_line_id NUMBER;
810 x_wip_operation_seq_num NUMBER;
811 x_wip_resource_seq_num NUMBER;
812 x_bom_resource_id NUMBER;
813 x_to_organization_id NUMBER;
814 x_job VARCHAR2(80);
815 x_line_num VARCHAR2(10);
816 x_sequence NUMBER;
817 x_department VARCHAR2(40);
818 x_enforce_ship_to_loc VARCHAR2(30);
819 x_allow_substitutes VARCHAR2(3);
820 x_routing_id NUMBER;
821 x_qty_rcv_tolerance NUMBER;
822 x_qty_rcv_exception VARCHAR2(30);
823 x_days_early_receipt NUMBER;
824 x_days_late_receipt NUMBER;
825 x_rcv_days_exception VARCHAR2(30);
826 x_item_revision VARCHAR2(3);
827 x_locator_control NUMBER;
828 x_inv_destinations BOOLEAN;
829 x_rate NUMBER;
830 x_rate_date DATE;
831 x_project_id NUMBER;
832 x_task_id NUMBER;
833 x_req_line_id NUMBER;
834 x_pos NUMBER;
835 x_oe_order_line_id NUMBER;
836 x_item_id NUMBER;
837 x_org_id NUMBER;
838 x_category_id NUMBER;
839 x_category_set_id NUMBER;
840 x_routing_name VARCHAR2(240);
841 l_operating_unit NUMBER;
842 BEGIN
843 SAVEPOINT insert_rcv_txn_interface_pvt;
844
845 -- Standard call to check for call compatibility.
846 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
847 p_api_version_number,
848 l_api_name,
849 G_PKG_NAME)
850 THEN
851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
852 END IF;
853
854 -- Initialize message list if p_init_msg_list is set to TRUE.
855 IF fnd_api.to_boolean(p_init_msg_list) THEN
856 fnd_msg_pub.initialize;
857 END IF;
858
859 x_return_status := FND_API.G_RET_STS_SUCCESS;
860
861 l_rcv_transaction_rec := p_receive_rec;
862
863 l_interface_transaction_id := l_rcv_transaction_rec.interface_transaction_id;
864 If (l_interface_transaction_id IS NULL) THEN
865 SELECT rcv_transactions_interface_s.NEXTVAL
866 INTO l_interface_transaction_id
867 FROM sys.dual;
868 END IF;
869
870 -- call defaulting api
871 begin
872 rcv_receipts_query_sv.post_query(
873 l_rcv_transaction_rec.po_line_location_id
874 , l_rcv_transaction_rec.rcv_shipment_line_id
875 , l_rcv_transaction_rec.receipt_source_code
876 , l_rcv_transaction_rec.to_organization_id
877 , l_rcv_transaction_rec.item_id
878 , l_rcv_transaction_rec.primary_uom_class
879 , l_rcv_transaction_rec.ship_to_location_id
880 , l_rcv_transaction_rec.vendor_id
881 , l_rcv_transaction_rec.customer_id
885 , x_tolerable_qty
882 , l_rcv_transaction_rec.item_rev_control_flag_to
883 , x_available_qty
884 , x_primary_qty
886 , x_uom
887 , x_primary_uom
888 , x_valid_ship_to_location
889 , x_num_of_distributions
890 , x_po_distribution_id
891 , x_destination_type_code
892 , x_destination_type_dsp
893 , x_deliver_to_location_id
894 , x_deliver_to_location
895 , x_deliver_to_person_id
896 , x_deliver_to_person
897 , x_deliver_to_sub
898 , x_deliver_to_locator_id
899 , x_wip_entity_id
900 , x_wip_repetitive_schedule_id
901 , x_wip_line_id
902 , x_wip_operation_seq_num
903 , x_wip_resource_seq_num
904 , x_bom_resource_id
905 , x_to_organization_id
906 , x_job
907 , x_line_num
908 , x_sequence
909 , x_department
910 , x_enforce_ship_to_loc
911 , x_allow_substitutes
912 , x_routing_id
913 , x_qty_rcv_tolerance
914 , x_qty_rcv_exception
915 , x_days_early_receipt
916 , x_days_late_receipt
917 , x_rcv_days_exception
918 , x_item_revision
919 , x_locator_control
920 , x_inv_destinations
921 , x_rate
922 , x_rate_date
923 , l_rcv_transaction_rec.asn_type
924 , l_rcv_transaction_rec.oe_order_header_id
925 , l_rcv_transaction_rec.oe_order_line_id
926 , l_rcv_transaction_rec.from_organization_id);
927 EXCEPTION
928 WHEN OTHERS THEN
929 fnd_message.set_name('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
930 fnd_message.set_token('ROUTINE','rcv_receipts_query_sv.post_query',false);
931 fnd_message.set_token('SQLERRM',sqlerrm,false);
932 fnd_msg_pub.add;
933 get_messages(x_msg_data);
934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
935 END;
936
937
938 IF (NOT x_valid_ship_to_location) THEN
939 l_rcv_transaction_rec.ship_to_location_id := '';
940 END IF;
941
942 l_to_org_id := l_rcv_transaction_rec.to_organization_id;
943
944 BEGIN
945 SELECT BLIND_RECEIVING_FLAG
946 INTO l_blind_receiving_flag
947 FROM rcv_parameters
948 WHERE organization_id = l_to_org_id;
949
950 EXCEPTION
951 WHEN OTHERS THEN
952 fnd_message.set_name('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
953 fnd_message.set_token('ROUTINE','get receiving flag',false);
954 fnd_message.set_token('SQLERRM',sqlerrm,false);
955 fnd_msg_pub.add;
956 get_messages(x_msg_data);
957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
958 END;
959
960 IF l_blind_receiving_flag = 'Y' THEN
961 l_rcv_transaction_rec.ordered_qty := 0;
962 END IF;
963
964 --l_rcv_transaction_rec.destination_type_code_hold := l_rcv_transactions_rec.destination_type_code;
965
966 IF l_rcv_transaction_rec.routing_id is NULL THEN
967 l_rcv_transaction_rec.routing_id := x_routing_id;
968 END IF;
969
970 IF l_rcv_transaction_rec.routing_id IN('1', '2') THEN
971 l_rcv_transaction_rec.destination_type_code := 'RECEIVING';
972 l_rcv_transaction_rec.po_distribution_id := x_po_distribution_id;
973 l_rcv_transaction_rec.deliver_to_location_id := x_deliver_to_location_id;
974 l_rcv_transaction_rec.deliver_to_person_id := x_deliver_to_person_id;
975
976 IF (x_wip_entity_id > 0) THEN
977 l_rcv_transaction_rec.wip_entity_id := x_wip_entity_id;
978 l_rcv_transaction_rec.wip_line_id := x_wip_line_id;
979 l_rcv_transaction_rec.wip_repetitive_schedule_id := x_wip_repetitive_schedule_id;
980 l_rcv_transaction_rec.wip_operation_seq_num := x_wip_operation_seq_num;
981 l_rcv_transaction_rec.wip_resource_seq_num := x_wip_resource_seq_num;
982 l_rcv_transaction_rec.bom_resource_id := x_bom_resource_id;
983 END IF;
984
985 IF x_num_of_distributions <= 1 THEN
986 l_rate := l_rcv_transaction_rec.currency_conversion_rate_pod ;
987 l_rate_date := l_rcv_transaction_rec.currency_conversion_date_pod;
988 END IF;
989
990 IF l_rcv_transaction_rec.match_option <> 'P' THEN
991 l_rate_date := SYSDATE;
992 l_rcv_transaction_rec.currency_conversion_date_pod := SYSDATE;
993
994 IF (l_rcv_transaction_rec.currency_code <> l_rcv_transaction_rec.currency_code_sob) THEN
995 l_rate := l_rcv_transaction_rec.currency_conversion_rate;
996
997 IF (l_rcv_transaction_rec.currency_conversion_type <> 'User') THEN
998 BEGIN
999 l_rate_gl :=
1000 gl_currency_api.get_rate(
1001 l_rcv_transaction_rec.set_of_books_id_sob
1002 , l_rcv_transaction_rec.currency_code
1003 , l_rcv_transaction_rec.currency_conversion_date_pod
1004 , l_rcv_transaction_rec.currency_conversion_type
1005 );
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 l_rate_gl := NULL;
1009 END;
1010 END IF;
1011
1012 l_rate := l_rate_gl;
1013 END IF;
1014 END IF;
1015
1016 /* IF (
1017 x_req_line_id IS NOT NULL
1018 AND x_deliver_to_locator_id IS NOT NULL
1019 AND NVL(l_rcv_transaction_rec.receipt_source_code, 'VENDOR') <> 'VENDOR'
1020 ) THEN
1021 SELECT project_id
1022 , task_id
1026 WHERE requisition_line_id = x_req_line_id;
1023 INTO x_project_id
1024 , x_task_id
1025 FROM po_req_distributions
1027 END IF;
1028
1029 IF x_project_id IS NOT NULL THEN
1030 pjm_project_locator.get_defaultprojectlocator(
1031 p_organization_id
1032 , x_deliver_to_locator_id
1033 , x_project_id
1034 , x_task_id
1035 , x_deliver_to_locator_id);
1036 END IF; */
1037 END IF;
1038
1039 l_receipt_source_code := l_rcv_transaction_rec.receipt_source_code;
1040 l_source_doc_code := l_rcv_transaction_rec.order_type_code;
1041 l_to_org_id := l_rcv_transaction_rec.to_organization_id;
1042 l_sub_unordered_code := l_rcv_transaction_rec.substitute_receipt;
1043
1044 IF l_rcv_transaction_rec.source_type_code IN('VENDOR', 'ASN') THEN
1045 l_vendor_id := l_rcv_transaction_rec.vendor_id;
1046 l_vendor_site_id := l_rcv_transaction_rec.vendor_site_id;
1047 l_po_header_id := l_rcv_transaction_rec.po_header_id;
1048 l_po_release_id := l_rcv_transaction_rec.po_release_id;
1049 l_po_line_id := l_rcv_transaction_rec.po_line_id;
1050 l_po_line_location_id := l_rcv_transaction_rec.po_line_location_id;
1051 ELSIF l_rcv_transaction_rec.source_type_code = 'INTERNAL' THEN
1052 l_req_line_id := l_rcv_transaction_rec.req_line_id;
1053 l_from_org_id := l_rcv_transaction_rec.from_organization_id;
1054 l_shipment_line_id := l_rcv_transaction_rec.rcv_shipment_line_id;
1055 END IF;
1056
1057
1058 IF l_rcv_transaction_rec.destination_type_code = 'RECEIVING' THEN
1059 l_auto_transact_code := 'RECEIVE';
1060 l_location_id := l_rcv_transaction_rec.ship_to_location_id;
1061 l_subinventory := l_rcv_transaction_rec.destination_subinventory;
1062 l_locator_id := l_rcv_transaction_rec.locator_id;
1063 ELSE
1064 l_auto_transact_code := 'DELIVER';
1065 l_po_distribution_id := l_rcv_transaction_rec.po_distribution_id;
1066 l_deliver_to_person_id := l_rcv_transaction_rec.deliver_to_person_id;
1067 l_deliver_to_location_id := l_rcv_transaction_rec.deliver_to_location_id;
1068 l_subinventory := l_rcv_transaction_rec.destination_subinventory;
1069 l_locator_id := l_rcv_transaction_rec.locator_id;
1070 l_location_id := l_rcv_transaction_rec.deliver_to_location_id;
1071
1072 IF l_rcv_transaction_rec.source_type_code IN('VENDOR', 'ASN') THEN
1073 l_wip_entity_id := l_rcv_transaction_rec.wip_entity_id;
1074 l_wip_line_id := l_rcv_transaction_rec.wip_line_id;
1075 l_department_code := l_rcv_transaction_rec.department_code;
1076 l_wip_rep_sched_id := l_rcv_transaction_rec.wip_repetitive_schedule_id;
1077 l_wip_oper_seq_num := l_rcv_transaction_rec.wip_operation_seq_num;
1078 l_wip_res_seq_num := l_rcv_transaction_rec.wip_resource_seq_num;
1079 l_bom_resource_id := l_rcv_transaction_rec.bom_resource_id;
1080 END IF;
1081 END IF;
1082
1083 l_sub_unordered_code := l_rcv_transaction_rec.substitute_receipt;
1084
1085 IF l_rcv_transaction_rec.source_type_code = 'INTERNAL' THEN
1086 l_shipment_header_id := l_rcv_transaction_rec.rcv_shipment_header_id;
1087 ELSE
1088 l_shipment_header_id := NULL;
1089 END IF;
1090
1091 l_lpn_group_id := l_rcv_transaction_rec.group_id;
1092 l_validation_flag := 'Y';
1093 l_header_interface_id := l_rcv_transaction_rec.header_interface_id;
1094 l_project_id := NULL;
1095 l_task_id := NULL;
1096
1097 l_operating_unit := fnd_profile.value(name => 'DEFAULT_ORG_ID');
1098
1099 -- populate DB items in rcv_transaction block
1100 INSERT INTO rcv_transactions_interface
1101 (
1102 interface_transaction_id
1103 , header_interface_id
1104 , GROUP_ID
1105 , last_update_date
1106 , last_updated_by
1107 , creation_date
1108 , created_by
1109 , last_update_login
1110 , transaction_type
1111 , transaction_date
1112 , processing_status_code
1113 , processing_mode_code
1114 , processing_request_id
1115 , transaction_status_code
1116 , category_id
1117 , quantity
1118 , unit_of_measure
1119 , interface_source_code
1120 , interface_source_line_id
1121 , inv_transaction_id
1122 , item_id
1123 , item_description
1124 , item_revision
1125 , uom_code
1126 , employee_id
1127 , auto_transact_code
1128 , shipment_header_id
1129 , shipment_line_id
1130 , ship_to_location_id
1131 , primary_quantity
1132 , primary_unit_of_measure
1133 , receipt_source_code
1134 , vendor_id
1135 , vendor_site_id
1136 , from_organization_id
1137 , to_organization_id
1138 , routing_header_id
1139 , routing_step_id
1140 , source_document_code
1141 , parent_transaction_id
1142 , po_header_id
1143 , po_revision_num
1144 , po_release_id
1145 , po_line_id
1149 , currency_conversion_type
1146 , po_line_location_id
1147 , po_unit_price
1148 , currency_code
1150 , currency_conversion_rate
1151 , currency_conversion_date
1152 , po_distribution_id
1153 , requisition_line_id
1154 , req_distribution_id
1155 , charge_account_id
1156 , substitute_unordered_code
1157 , receipt_exception_flag
1158 , accrual_status_code
1159 , inspection_status_code
1160 , inspection_quality_code
1161 , destination_type_code
1162 , deliver_to_person_id
1163 , location_id
1164 , deliver_to_location_id
1165 , subinventory
1166 , locator_id
1167 , wip_entity_id
1168 , wip_line_id
1169 , department_code
1170 , wip_repetitive_schedule_id
1171 , wip_operation_seq_num
1172 , wip_resource_seq_num
1173 , bom_resource_id
1174 , shipment_num
1175 , freight_carrier_code
1176 , bill_of_lading
1177 , packing_slip
1178 , shipped_date
1179 , expected_receipt_date
1180 , actual_cost
1181 , transfer_cost
1182 , transportation_cost
1183 , transportation_account_id
1184 , num_of_containers
1185 , waybill_airbill_num
1186 , vendor_item_num
1187 , vendor_lot_num
1188 , rma_reference
1189 , comments
1190 , attribute_category
1191 , attribute1
1192 , attribute2
1193 , attribute3
1194 , attribute4
1195 , attribute5
1196 , attribute6
1197 , attribute7
1198 , attribute8
1199 , attribute9
1200 , attribute10
1201 , attribute11
1202 , attribute12
1203 , attribute13
1204 , attribute14
1205 , attribute15
1206 , ship_head_attribute_category
1207 , ship_head_attribute1
1208 , ship_head_attribute2
1209 , ship_head_attribute3
1210 , ship_head_attribute4
1211 , ship_head_attribute5
1212 , ship_head_attribute6
1213 , ship_head_attribute7
1214 , ship_head_attribute8
1215 , ship_head_attribute9
1216 , ship_head_attribute10
1217 , ship_head_attribute11
1218 , ship_head_attribute12
1219 , ship_head_attribute13
1220 , ship_head_attribute14
1221 , ship_head_attribute15
1222 , ship_line_attribute_category
1223 , ship_line_attribute1
1224 , ship_line_attribute2
1225 , ship_line_attribute3
1226 , ship_line_attribute4
1227 , ship_line_attribute5
1228 , ship_line_attribute6
1229 , ship_line_attribute7
1230 , ship_line_attribute8
1231 , ship_line_attribute9
1232 , ship_line_attribute10
1233 , ship_line_attribute11
1234 , ship_line_attribute12
1235 , ship_line_attribute13
1236 , ship_line_attribute14
1237 , ship_line_attribute15
1238 , ussgl_transaction_code
1239 , government_context
1240 , reason_id
1241 , destination_context
1242 , source_doc_quantity
1243 , source_doc_unit_of_measure
1244 , use_mtl_lot
1245 , use_mtl_serial
1246 , qa_collection_id
1247 , country_of_origin_code
1248 , oe_order_header_id
1249 , oe_order_line_id
1250 , customer_item_num
1251 , customer_id
1252 , customer_site_id
1253 , mobile_txn
1254 , lpn_group_id
1255 , validation_flag
1256 --, project_id
1257 --, task_id
1258 ,org_id
1259 )
1260 VALUES (
1261 l_interface_transaction_id
1262 , l_header_interface_id --l_rcv_transaction_rec.header_interface_id
1263 , l_rcv_transaction_rec.group_id
1264 , SYSDATE
1265 , FND_GLOBAL.USER_ID
1266 , SYSDATE
1267 , FND_GLOBAL.USER_ID
1268 , FND_GLOBAL.LOGIN_ID
1269 , 'RECEIVE'
1270 , SYSDATE
1271 , 'PENDING' /* Processing status code */
1272 , 'ONLINE'
1273 , NULL
1274 , 'PENDING' /* Transaction status code */
1275 , l_rcv_transaction_rec.item_category_id
1276 , l_rcv_transaction_rec.transaction_quantity
1277 , l_rcv_transaction_rec.transaction_uom
1278 , l_rcv_transaction_rec.product_code /* interface source code */
1279 , NULL /* interface source line id */
1280 , NULL /* inv_transaction id */
1281 , l_rcv_transaction_rec.item_id
1282 , l_rcv_transaction_rec.item_description
1286 , l_auto_transact_code /* Auto transact code */
1283 , l_rcv_transaction_rec.item_revision
1284 , l_rcv_transaction_rec.uom_code
1285 , l_rcv_transaction_rec.employee_id
1287 , l_shipment_header_id /* shipment header id */
1288 , l_shipment_line_id /* shipment line id */
1289 , l_rcv_transaction_rec.ship_to_location_id
1290 , l_rcv_transaction_rec.primary_quantity /* primary quantity */
1291 , l_rcv_transaction_rec.primary_uom /* primary uom */
1292 , l_receipt_source_code /* receipt source code */
1293 , l_vendor_id
1294 , l_vendor_site_id
1295 , l_from_org_id /* from org id */
1296 , l_to_org_id /* to org id */
1297 , l_rcv_transaction_rec.routing_id
1298 , 1 /* routing step id*/
1299 , l_source_doc_code /* source document code */
1300 , NULL /* Parent trx id */
1301 , l_po_header_id
1302 , NULL /* PO Revision number */
1303 , l_po_release_id
1304 , l_po_line_id
1305 , l_po_line_location_id
1306 , l_rcv_transaction_rec.unit_price
1307 , l_rcv_transaction_rec.currency_code /* Currency_Code */
1308 , l_rcv_transaction_rec.currency_conversion_type
1309 , l_rcv_transaction_rec.currency_conversion_rate
1310 , TRUNC(l_rcv_transaction_rec.currency_conversion_date)
1311 , l_po_distribution_id /* po_distribution_Id */
1312 , l_req_line_id
1313 , l_rcv_transaction_rec.req_distribution_id
1314 , NULL /* Charge_Account_Id */
1315 , l_sub_unordered_code /* Substitute_Unordered_Code */
1316 , l_rcv_transaction_rec.receipt_exception /* Receipt_Exception_Flag forms check box?*/
1317 , NULL /* Accrual_Status_Code */
1318 , 'NOT INSPECTED' /* Inspection_Status_Code */
1319 , NULL /* Inspection_Quality_Code */
1320 , l_rcv_transaction_rec.destination_type_code /* Destination_Type_Code */
1321 , l_deliver_to_person_id /* Deliver_To_Person_Id */
1322 , l_location_id /* Location_Id */
1323 , l_deliver_to_location_id /* Deliver_To_Location_Id */
1324 , l_subinventory /* Subinventory */
1325 , l_locator_id /* Locator_Id */
1326 , l_wip_entity_id /* Wip_Entity_Id */
1327 , l_wip_line_id /* Wip_Line_Id */
1328 , l_department_code /* Department_Code */
1329 , l_wip_rep_sched_id /* Wip_Repetitive_Schedule_Id */
1330 , l_wip_oper_seq_num /* Wip_Operation_Seq_Num */
1331 , l_wip_res_seq_num /* Wip_Resource_Seq_Num */
1332 , l_bom_resource_id /* Bom_Resource_Id */
1333 , l_rcv_transaction_rec.rcv_shipment_number
1334 , NULL
1335 , NULL /* Bill_Of_Lading */
1336 , NULL /* Packing_Slip */
1337 , TRUNC(l_rcv_transaction_rec.shipped_date)
1338 , TRUNC(l_rcv_transaction_rec.expected_receipt_date) /* Expected_Receipt_Date */
1339 , NULL /* Actual_Cost */
1340 , NULL /* Transfer_Cost */
1341 , NULL /* Transportation_Cost */
1342 , NULL /* Transportation_Account_Id */
1343 , NULL /* Num_Of_Containers */
1344 , NULL /* Waybill_Airbill_Num */
1345 , l_rcv_transaction_rec.vendor_item_number /* Vendor_Item_Num */
1346 , l_rcv_transaction_rec.vendor_lot_num /* Vendor_Lot_Num */
1347 , NULL /* Rma_Reference */
1348 , l_rcv_transaction_rec.comments /* Comments ? from form*/
1349 , l_rcv_transaction_rec.attribute_category /* Attribute_Category */
1350 , l_rcv_transaction_rec.attribute1 /* Attribute1 */
1351 , l_rcv_transaction_rec.attribute2 /* Attribute2 */
1352 , l_rcv_transaction_rec.attribute3 /* Attribute3 */
1353 , l_rcv_transaction_rec.attribute4 /* Attribute4 */
1354 , l_rcv_transaction_rec.attribute5 /* Attribute5 */
1355 , l_rcv_transaction_rec.attribute6 /* Attribute6 */
1356 , l_rcv_transaction_rec.attribute7 /* Attribute7 */
1357 , l_rcv_transaction_rec.attribute8 /* Attribute8 */
1358 , l_rcv_transaction_rec.attribute9 /* Attribute9 */
1359 , l_rcv_transaction_rec.attribute10 /* Attribute10 */
1360 , l_rcv_transaction_rec.attribute11 /* Attribute11 */
1361 , l_rcv_transaction_rec.attribute12 /* Attribute12 */
1362 , l_rcv_transaction_rec.attribute13 /* Attribute13 */
1363 , l_rcv_transaction_rec.attribute14 /* Attribute14 */
1364 , l_rcv_transaction_rec.attribute15 /* Attribute15 */
1365 , NULL /* Ship_Head_Attribute_Category */
1366 , NULL /* Ship_Head_Attribute1 */
1367 , NULL /* Ship_Head_Attribute2 */
1368 , NULL /* Ship_Head_Attribute3 */
1369 , NULL /* Ship_Head_Attribute4 */
1370 , NULL /* Ship_Head_Attribute5 */
1371 , NULL /* Ship_Head_Attribute6 */
1372 , NULL /* Ship_Head_Attribute7 */
1373 , NULL /* Ship_Head_Attribute8 */
1374 , NULL /* Ship_Head_Attribute9 */
1375 , NULL /* Ship_Head_Attribute10 */
1376 , NULL /* Ship_Head_Attribute11 */
1377 , NULL /* Ship_Head_Attribute12 */
1381 , NULL /* Ship_Line_Attribute_Category */
1378 , NULL /* Ship_Head_Attribute13 */
1379 , NULL /* Ship_Head_Attribute14 */
1380 , NULL /* Ship_Head_Attribute15 */
1382 , NULL /* Ship_Line_Attribute1 */
1383 , NULL /* Ship_Line_Attribute2 */
1384 , NULL /* Ship_Line_Attribute3 */
1385 , NULL /* Ship_Line_Attribute4 */
1386 , NULL /* Ship_Line_Attribute5 */
1387 , NULL /* Ship_Line_Attribute6 */
1388 , NULL /* Ship_Line_Attribute7 */
1389 , NULL /* Ship_Line_Attribute8 */
1390 , NULL /* Ship_Line_Attribute9 */
1391 , NULL /* Ship_Line_Attribute10 */
1392 , NULL /* Ship_Line_Attribute11 */
1393 , NULL /* Ship_Line_Attribute12 */
1394 , NULL /* Ship_Line_Attribute13 */
1395 , NULL /* Ship_Line_Attribute14 */
1396 , NULL /* Ship_Line_Attribute15 */
1397 , l_rcv_transaction_rec.ussgl_transaction_code /* Ussgl_Transaction_Code */
1398 , l_rcv_transaction_rec.government_context /* Government_Context */
1399 , l_rcv_transaction_rec.reason_id /* ? */
1400 , l_rcv_transaction_rec.destination_type_code /* Destination_Context */
1401 , l_rcv_transaction_rec.ordered_qty
1402 , l_rcv_transaction_rec.ordered_uom
1403 , l_rcv_transaction_rec.lot_control_code
1404 , l_rcv_transaction_rec.serial_number_control_code
1405 , NULL
1406 , l_rcv_transaction_rec.country_of_origin_code
1407 , l_oe_order_header_id
1408 , l_oe_order_line_id
1409 , l_customer_item_num
1410 , l_customer_id
1411 , l_customer_site_id
1412 , 'N' /* mobile_txn */
1413 , NULL -- l_lpn_group_id
1414 , l_validation_flag
1415 --, l_project_id
1416 --, l_task_id
1417 ,l_operating_unit
1418 );
1419
1420 x_interface_transaction_id := l_interface_transaction_id;
1421 EXCEPTION
1422 WHEN FND_API.G_EXC_ERROR THEN
1423 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1424 P_API_NAME => L_API_NAME
1425 ,P_PKG_NAME => G_PKG_NAME
1426 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1427 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1428 ,X_MSG_COUNT => X_MSG_COUNT
1429 ,X_MSG_DATA => X_MESSAGE
1430 ,X_RETURN_STATUS => X_RETURN_STATUS);
1431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1432 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1433 P_API_NAME => L_API_NAME
1434 ,P_PKG_NAME => G_PKG_NAME
1435 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1436 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1437 ,X_MSG_COUNT => X_MSG_COUNT
1438 ,X_MSG_DATA => X_MESSAGE
1439 ,X_RETURN_STATUS => X_RETURN_STATUS);
1440 WHEN OTHERS THEN
1441 fnd_message.set_name('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
1442 fnd_message.set_token('ROUTINE',l_api_name,false);
1443 fnd_message.set_token('SQLERRM',sqlerrm,false);
1444 fnd_msg_pub.add;
1445 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1446 P_API_NAME => L_API_NAME
1447 ,P_PKG_NAME => G_PKG_NAME
1448 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1449 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1450 ,X_MSG_COUNT => X_MSG_COUNT
1451 ,X_MSG_DATA => X_MESSAGE
1452 ,X_RETURN_STATUS => X_RETURN_STATUS);
1453 END;
1454
1455 PROCEDURE insert_lots_interface (
1456 p_api_version IN NUMBER
1457 , p_init_msg_list IN VARCHAR2
1458 , x_return_status OUT NOCOPY VARCHAR2
1459 , x_msg_count OUT NOCOPY NUMBER
1460 , x_msg_data OUT NOCOPY VARCHAR2
1461 , p_serial_transaction_temp_id IN NUMBER
1462 , p_transaction_interface_id IN NUMBER
1463 , p_lot_number IN VARCHAR2
1464 , p_transaction_quantity IN NUMBER
1465 , p_primary_quantity IN NUMBER
1466 , p_organization_id IN NUMBER
1467 , p_inventory_item_id IN NUMBER
1468 , p_product_transaction_id IN NUMBER
1469 , p_product_code IN VARCHAR2) IS
1470 CURSOR c_mln_attributes( v_lot_number VARCHAR2
1471 , v_inventory_item_id NUMBER
1472 , v_organization_id NUMBER) IS
1473 SELECT lot_number
1474 , expiration_date
1475 , description
1476 , vendor_name
1477 , supplier_lot_number
1478 , grade_code
1479 , origination_date
1480 , date_code
1481 , status_id
1482 , change_date
1483 , age
1484 , retest_date
1485 , maturity_date
1486 , item_size
1487 , color
1488 , volume
1489 , volume_uom
1490 , place_of_origin
1491 , best_by_date
1492 , LENGTH
1493 , length_uom
1494 , recycled_content
1495 , thickness
1499 , curl_wrinkle_fold
1496 , thickness_uom
1497 , width
1498 , width_uom
1500 , vendor_id
1501 , territory_code
1502 , lot_attribute_category
1503 , c_attribute1
1504 , c_attribute2
1505 , c_attribute3
1506 , c_attribute4
1507 , c_attribute5
1508 , c_attribute6
1509 , c_attribute7
1510 , c_attribute8
1511 , c_attribute9
1512 , c_attribute10
1513 , c_attribute11
1514 , c_attribute12
1515 , c_attribute13
1516 , c_attribute14
1517 , c_attribute15
1518 , c_attribute16
1519 , c_attribute17
1520 , c_attribute18
1521 , c_attribute19
1522 , c_attribute20
1523 , d_attribute1
1524 , d_attribute2
1525 , d_attribute3
1526 , d_attribute4
1527 , d_attribute5
1528 , d_attribute6
1529 , d_attribute7
1530 , d_attribute8
1531 , d_attribute9
1532 , d_attribute10
1533 , n_attribute1
1534 , n_attribute2
1535 , n_attribute3
1536 , n_attribute4
1537 , n_attribute5
1538 , n_attribute6
1539 , n_attribute7
1540 , n_attribute8
1541 , n_attribute9
1542 , n_attribute10
1543 FROM mtl_lot_numbers
1544 WHERE lot_number = Ltrim(Rtrim(v_lot_number))
1545 AND inventory_item_id = v_inventory_item_id
1546 AND organization_id = v_organization_id;
1547
1548
1549 l_lot_number mtl_lot_numbers.lot_number%type;
1550 l_expiration_date mtl_lot_numbers.expiration_date%type;
1551 l_description mtl_lot_numbers.description%type;
1552 l_vendor_name mtl_lot_numbers.vendor_name%type;
1553 l_supplier_lot_number mtl_lot_numbers.supplier_lot_number%type;
1554 l_grade_code mtl_lot_numbers.grade_code%type;
1555 l_origination_date mtl_lot_numbers.origination_date%type;
1556 l_date_code mtl_lot_numbers.date_code%type;
1557 l_status_id mtl_lot_numbers.status_id%type;
1558 l_change_date mtl_lot_numbers.change_date%type;
1559 l_age mtl_lot_numbers.age%type;
1560 l_retest_date mtl_lot_numbers.retest_date%type;
1561 l_maturity_date mtl_lot_numbers.maturity_date%type;
1562 l_item_size mtl_lot_numbers.item_size%type;
1563 l_color mtl_lot_numbers.color%type;
1564 l_volume mtl_lot_numbers.volume%type;
1565 l_volume_uom mtl_lot_numbers.volume_uom%type;
1566 l_place_of_origin mtl_lot_numbers.place_of_origin%type;
1567 l_best_by_date mtl_lot_numbers.best_by_date%type;
1568 l_length mtl_lot_numbers.length%type;
1569 l_length_uom mtl_lot_numbers.length_uom%type;
1570 l_recycled_content mtl_lot_numbers.recycled_content%type;
1571 l_thickness mtl_lot_numbers.thickness%type;
1572 l_thickness_uom mtl_lot_numbers.thickness_uom%type;
1573 l_width mtl_lot_numbers.width%type;
1574 l_width_uom mtl_lot_numbers.width_uom%type;
1575 l_curl_wrinkle_fold mtl_lot_numbers.curl_wrinkle_fold%type;
1576 l_vendor_id mtl_lot_numbers.vendor_id%type;
1577 l_territory_code mtl_lot_numbers.territory_code%type;
1578 l_lot_attribute_category mtl_lot_numbers.lot_attribute_category%type;
1579 l_c_attribute1 mtl_lot_numbers.c_attribute1%type ;
1580 l_c_attribute2 mtl_lot_numbers.c_attribute2%type;
1581 l_c_attribute3 mtl_lot_numbers.c_attribute3%type;
1582 l_c_attribute4 mtl_lot_numbers.c_attribute4%type;
1583 l_c_attribute5 mtl_lot_numbers.c_attribute5%type;
1584 l_c_attribute6 mtl_lot_numbers.c_attribute6%type;
1585 l_c_attribute7 mtl_lot_numbers.c_attribute7%type;
1586 l_c_attribute8 mtl_lot_numbers.c_attribute8%type;
1587 l_c_attribute9 mtl_lot_numbers.c_attribute9%type;
1588 l_c_attribute10 mtl_lot_numbers.c_attribute10%type ;
1589 l_c_attribute11 mtl_lot_numbers.c_attribute11%type ;
1590 l_c_attribute12 mtl_lot_numbers.c_attribute12%type;
1591 l_c_attribute13 mtl_lot_numbers.c_attribute13%type;
1592 l_c_attribute14 mtl_lot_numbers.c_attribute14%type;
1593 l_c_attribute15 mtl_lot_numbers.c_attribute15%type;
1594 l_c_attribute16 mtl_lot_numbers.c_attribute16%type;
1595 l_c_attribute17 mtl_lot_numbers.c_attribute17%type;
1596 l_c_attribute18 mtl_lot_numbers.c_attribute18%type;
1597 l_c_attribute19 mtl_lot_numbers.c_attribute19%type;
1598 l_c_attribute20 mtl_lot_numbers.c_attribute20%type;
1599 l_d_attribute1 mtl_lot_numbers.d_attribute1%type ;
1600 l_d_attribute2 mtl_lot_numbers.d_attribute2%type;
1601 l_d_attribute3 mtl_lot_numbers.d_attribute3%type;
1602 l_d_attribute4 mtl_lot_numbers.d_attribute4%type;
1603 l_d_attribute5 mtl_lot_numbers.d_attribute5%type;
1604 l_d_attribute6 mtl_lot_numbers.d_attribute6%type;
1605 l_d_attribute7 mtl_lot_numbers.d_attribute7%type;
1606 l_d_attribute8 mtl_lot_numbers.d_attribute8%type;
1607 l_d_attribute9 mtl_lot_numbers.d_attribute9%type;
1608 l_d_attribute10 mtl_lot_numbers.d_attribute10%type;
1612 l_n_attribute4 mtl_lot_numbers.n_attribute4%type;
1609 l_n_attribute1 mtl_lot_numbers.n_attribute1%type ;
1610 l_n_attribute2 mtl_lot_numbers.n_attribute2%type ;
1611 l_n_attribute3 mtl_lot_numbers.n_attribute3%type ;
1613 l_n_attribute5 mtl_lot_numbers.n_attribute5%type;
1614 l_n_attribute6 mtl_lot_numbers.n_attribute6%type;
1615 l_n_attribute7 mtl_lot_numbers.n_attribute7%type ;
1616 l_n_attribute8 mtl_lot_numbers.n_attribute8%type;
1617 l_n_attribute9 mtl_lot_numbers.n_attribute9%type;
1618 l_n_attribute10 mtl_lot_numbers.n_attribute10%type ;
1619 l_source_code mtl_transaction_lots_interface.source_code%TYPE;
1620 l_source_line_id mtl_transaction_lots_interface.source_line_id%TYPE;
1621 l_serial_control_code mtl_system_items.serial_number_control_code%TYPE;
1622
1623 l_api_version CONSTANT NUMBER := 1.0;
1624 l_api_name CONSTANT VARCHAR2(30) := 'insert_lots_interface';
1625 l_transaction_interface_id Number;
1626
1627 x_message Varchar2(4000);
1628
1629 BEGIN
1630
1631 -- Standard call to check for call compatibility.
1632 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
1633 l_api_name, G_PKG_NAME) THEN
1634 RAISE fnd_api.g_exc_unexpected_error;
1635 END IF;
1636
1637 -- Initialize message list if p_init_msg_list is set to TRUE.
1638 IF fnd_api.to_boolean(p_init_msg_list) THEN
1639 fnd_msg_pub.initialize;
1640 END IF;
1641
1642 --Initialize the return status
1643 x_return_status := FND_API.G_RET_STS_SUCCESS;
1644
1645 l_transaction_interface_id := p_transaction_interface_id;
1646 If (l_transaction_interface_id IS NULL) THEN
1647 SELECT mtl_material_transactions_s.NEXTVAL
1648 INTO l_transaction_interface_id
1649 FROM sys.dual;
1650 END IF;
1651
1652 OPEN c_mln_attributes(p_lot_number, p_inventory_item_id, p_organization_id);
1653 FETCH c_mln_attributes INTO
1654 l_lot_number
1655 , l_expiration_date
1656 , l_description
1657 , l_vendor_name
1658 , l_supplier_lot_number
1659 , l_grade_code
1660 , l_origination_date
1661 , l_date_code
1662 , l_status_id
1663 , l_change_date
1664 , l_age
1665 , l_retest_date
1666 , l_maturity_date
1667 , l_item_size
1668 , l_color
1669 , l_volume
1670 , l_volume_uom
1671 , l_place_of_origin
1672 , l_best_by_date
1673 , l_length
1674 , l_length_uom
1675 , l_recycled_content
1676 , l_thickness
1677 , l_thickness_uom
1678 , l_width
1679 , l_width_uom
1680 , l_curl_wrinkle_fold
1681 , l_vendor_id
1682 , l_territory_code
1683 , l_lot_attribute_category
1684 , l_c_attribute1
1685 , l_c_attribute2
1686 , l_c_attribute3
1687 , l_c_attribute4
1688 , l_c_attribute5
1689 , l_c_attribute6
1690 , l_c_attribute7
1691 , l_c_attribute8
1692 , l_c_attribute9
1693 , l_c_attribute10
1694 , l_c_attribute11
1695 , l_c_attribute12
1696 , l_c_attribute13
1697 , l_c_attribute14
1698 , l_c_attribute15
1699 , l_c_attribute16
1700 , l_c_attribute17
1701 , l_c_attribute18
1702 , l_c_attribute19
1703 , l_c_attribute20
1704 , l_d_attribute1
1705 , l_d_attribute2
1706 , l_d_attribute3
1707 , l_d_attribute4
1708 , l_d_attribute5
1709 , l_d_attribute6
1710 , l_d_attribute7
1711 , l_d_attribute8
1712 , l_d_attribute9
1713 , l_d_attribute10
1714 , l_n_attribute1
1715 , l_n_attribute2
1716 , l_n_attribute3
1717 , l_n_attribute4
1718 , l_n_attribute5
1719 , l_n_attribute6
1720 , l_n_attribute7
1721 , l_n_attribute8
1722 , l_n_attribute9
1723 , l_n_attribute10;
1724 CLOSE c_mln_attributes;
1725 INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
1726 transaction_interface_id
1727 , source_code
1728 , source_line_id
1729 , product_code
1730 , product_transaction_id
1731 , last_update_date
1732 , last_updated_by
1733 , creation_date
1734 , created_by
1735 , last_update_login
1736 , lot_number
1737 , lot_expiration_date
1738 , transaction_quantity
1739 , primary_quantity
1740 , serial_transaction_temp_id
1741 , description
1742 , vendor_name
1743 , supplier_lot_number
1744 , origination_date
1745 , date_code
1746 , grade_code
1747 , change_date
1748 , maturity_date
1749 , status_id
1750 , retest_date
1751 , age
1752 , item_size
1753 , color
1757 , best_by_date
1754 , volume
1755 , volume_uom
1756 , place_of_origin
1758 , length
1759 , length_uom
1760 , recycled_content
1761 , thickness
1762 , thickness_uom
1763 , width
1764 , width_uom
1765 , curl_wrinkle_fold
1766 , lot_attribute_category
1767 , c_attribute1
1768 , c_attribute2
1769 , c_attribute3
1770 , c_attribute4
1771 , c_attribute5
1772 , c_attribute6
1773 , c_attribute7
1774 , c_attribute8
1775 , c_attribute9
1776 , c_attribute10
1777 , c_attribute11
1778 , c_attribute12
1779 , c_attribute13
1780 , c_attribute14
1781 , c_attribute15
1782 , c_attribute16
1783 , c_attribute17
1784 , c_attribute18
1785 , c_attribute19
1786 , c_attribute20
1787 , d_attribute1
1788 , d_attribute2
1789 , d_attribute3
1790 , d_attribute4
1791 , d_attribute5
1792 , d_attribute6
1793 , d_attribute7
1794 , d_attribute8
1795 , d_attribute9
1796 , d_attribute10
1797 , n_attribute1
1798 , n_attribute2
1799 , n_attribute3
1800 , n_attribute4
1801 , n_attribute5
1802 , n_attribute6
1803 , n_attribute7
1804 , n_attribute8
1805 , n_attribute9
1806 , n_attribute10
1807 , vendor_id
1808 , territory_code
1809 )
1810 VALUES (
1811 l_transaction_interface_id
1812 , 1
1813 , -1
1814 , p_product_code
1815 , p_product_transaction_id
1816 , SYSDATE
1817 , FND_GLOBAL.USER_ID
1818 , SYSDATE
1819 , FND_GLOBAL.USER_ID
1820 , FND_GLOBAL.LOGIN_ID
1821 , Ltrim(Rtrim(p_lot_number))
1822 , l_expiration_date
1823 , p_transaction_quantity
1824 , p_primary_quantity
1825 , p_serial_transaction_temp_id
1826 , l_description
1827 , l_vendor_name
1828 , l_supplier_lot_number
1829 , l_origination_date
1830 , l_date_code
1831 , l_grade_code
1832 , l_change_date
1833 , l_maturity_date
1834 , l_status_id
1835 , l_retest_date
1836 , l_age
1837 , l_item_size
1838 , l_color
1839 , l_volume
1840 , l_volume_uom
1841 , l_place_of_origin
1842 , l_best_by_date
1843 , l_length
1844 , l_length_uom
1845 , l_recycled_content
1846 , l_thickness
1847 , l_thickness_uom
1848 , l_width
1849 , l_width_uom
1850 , l_curl_wrinkle_fold
1851 , l_lot_attribute_category
1852 , l_c_attribute1
1853 , l_c_attribute2
1854 , l_c_attribute3
1855 , l_c_attribute4
1856 , l_c_attribute5
1857 , l_c_attribute6
1858 , l_c_attribute7
1859 , l_c_attribute8
1860 , l_c_attribute9
1861 , l_c_attribute10
1862 , l_c_attribute11
1863 , l_c_attribute12
1864 , l_c_attribute13
1865 , l_c_attribute14
1866 , l_c_attribute15
1867 , l_c_attribute16
1868 , l_c_attribute17
1869 , l_c_attribute18
1870 , l_c_attribute19
1871 , l_c_attribute20
1872 , l_d_attribute1
1873 , l_d_attribute2
1874 , l_d_attribute3
1875 , l_d_attribute4
1876 , l_d_attribute5
1877 , l_d_attribute6
1878 , l_d_attribute7
1879 , l_d_attribute8
1880 , l_d_attribute9
1881 , l_d_attribute10
1882 , l_n_attribute1
1883 , l_n_attribute2
1884 , l_n_attribute3
1885 , l_n_attribute4
1886 , l_n_attribute5
1887 , l_n_attribute6
1888 , l_n_attribute7
1889 , l_n_attribute8
1890 , l_n_attribute9
1891 , l_n_attribute10
1892 , l_vendor_id
1893 , l_territory_code
1894 );
1895
1896 EXCEPTION
1897 WHEN FND_API.G_EXC_ERROR THEN
1898 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1899 P_API_NAME => L_API_NAME
1900 ,P_PKG_NAME => G_PKG_NAME
1901 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1902 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1903 ,X_MSG_COUNT => X_MSG_COUNT
1904 ,X_MSG_DATA => X_MESSAGE
1905 ,X_RETURN_STATUS => X_RETURN_STATUS);
1906 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1907 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1908 P_API_NAME => L_API_NAME
1909 ,P_PKG_NAME => G_PKG_NAME
1910 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1914 ,X_RETURN_STATUS => X_RETURN_STATUS);
1911 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1912 ,X_MSG_COUNT => X_MSG_COUNT
1913 ,X_MSG_DATA => X_MESSAGE
1915 WHEN OTHERS THEN
1916 fnd_message.set_name('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
1917 fnd_message.set_token('ROUTINE',l_api_name,false);
1918 fnd_message.set_token('SQLERRM',sqlerrm,false);
1919 fnd_msg_pub.add;
1920 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1921 P_API_NAME => L_API_NAME
1922 ,P_PKG_NAME => G_PKG_NAME
1923 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1924 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1925 ,X_MSG_COUNT => X_MSG_COUNT
1926 ,X_MSG_DATA => X_MSG_DATA
1927 ,X_RETURN_STATUS => X_RETURN_STATUS);
1928 END;
1929
1930 /*----------------------------------------------------------------------------
1931 * PROCEDURE:
1932 * Description:
1933 * This procedure inserts a record into MTL_SERIAL_NUMBERS_INTERFACE
1934 * Generate transaction_interface_id if the parameter is NULL
1935 * Generate product_transaction_id if the parameter is NULL
1936 * The insert logic is based on the parameter p_att_exist.
1937 * If p_att_exist is "N" Then (attributes are not available in table)
1938 * Read the input parameters (including attributes) into a PL/SQL table
1939 * Insert one record into MSNI with the from and to serial numbers passed
1940 * Else
1941 * Loop through each serial number between the from and to serial number
1942 * Fetch the attributes into one row of the PL/SQL table and
1943 * For each row in the PL/SQL table, insert one MSNI record
1944 * End If
1945 *
1946 * @param p_api_version - Version of the API
1947 * @param p_init_msg_list - Flag to initialize message list
1948 * @param x_return_status
1949 * Return status indicating Success (S), Error (E), Unexpected Error (U)
1950 * @param x_msg_count
1951 * Number of messages in message list
1952 * @param x_msg_data
1953 * Stacked messages text
1954 * @param p_transaction_interface_id - MTLI.Interface Transaction ID
1955 * @param p_fm_serial_number - From Serial Number
1956 * @param p_to_serial_number - To Serial Number
1957 * @param p_organization_id - Organization ID
1958 * @param p_inventory_item_id - Inventory Item ID
1959 * @param p_status_id - Material Status for the lot
1960 * @param p_product_transaction_id - Product Transaction Id. This parameter
1961 * is stamped with the transaction identifier with
1962 * @param p_product_code - Code of the product creating this record
1963 * @param p_att_exist - Flag to indicate if attributes exist
1964 * @param p_update_msn - Flag to update MSN with attributes
1965 * @param named attributes - Named attributes
1966 * @param C Attributes - Character atributes (1 - 20)
1967 * @param D Attributes - Date atributes (1 - 10)
1968 * @param N Attributes - Number atributes (1 - 10)
1969 * @param p_attribute_cateogry - Attribute Category
1970 * @param Attribute1-15 - Serial Attributes
1971 *
1972 * @ return: NONE
1973 *---------------------------------------------------------------------------*/
1974
1975 PROCEDURE insert_serial_interface(
1976 p_api_version IN NUMBER
1977 , p_init_msg_list IN VARCHAR2
1978 , x_return_status OUT NOCOPY VARCHAR2
1979 , x_msg_count OUT NOCOPY NUMBER
1980 , x_msg_data OUT NOCOPY VARCHAR2
1981 , px_transaction_interface_id IN OUT NOCOPY NUMBER
1982 , p_product_transaction_id IN NUMBER
1983 , p_product_code IN VARCHAR2
1984 , p_fm_serial_number IN VARCHAR2
1985 , p_to_serial_number IN VARCHAR2
1986 ) IS
1987
1988 l_api_version CONSTANT NUMBER := 1.0;
1989 l_api_name CONSTANT VARCHAR2(30) := 'insert_serial_interface';
1990
1991 l_transaction_interface_id Number;
1992
1993 x_message varchar2(4000);
1994 BEGIN
1995
1996 -- Standard call to check for call compatibility.
1997 IF NOT fnd_api.compatible_api_call(l_api_version,
1998 p_api_version,
1999 l_api_name,
2000 G_PKG_NAME) THEN
2001 RAISE fnd_api.g_exc_unexpected_error;
2002 END IF;
2003
2004 -- Initialize message list if p_init_msg_list is set to TRUE.
2005 IF fnd_api.to_boolean(p_init_msg_list) THEN
2006 fnd_msg_pub.initialize;
2007 END IF;
2008
2009 --Initialize the return status
2010 x_return_status := fnd_api.g_ret_sts_success;
2011
2012 --Generate transaction_interface_id if necessary
2013 l_transaction_interface_id := px_transaction_interface_id;
2014 IF (l_transaction_interface_id IS NULL) THEN
2015 SELECT mtl_material_transactions_s.NEXTVAL
2016 INTO l_transaction_interface_id
2017 FROM sys.dual;
2018 END IF;
2019
2020 Insert into MTL_SERIAL_NUMBERS_INTERFACE
2021 (
2025 Process_flag,
2022 transaction_interface_id,
2023 Source_Code,
2024 Source_Line_Id,
2026 Last_Update_Date,
2027 Last_Updated_By,
2028 Last_update_login,
2029 Creation_Date,
2030 Created_By,
2031 Fm_Serial_Number,
2032 To_Serial_Number,
2033 PRODUCT_CODE,
2034 PRODUCT_TRANSACTION_ID)
2035 VALUES
2036 (
2037 l_transaction_interface_id,
2038 1,
2039 -1,
2040 1,
2041 SYSDATE
2042 ,FND_GLOBAL.USER_ID
2043 ,FND_GLOBAL.LOGIN_ID
2044 ,sysdate
2045 ,FND_GLOBAL.USER_ID
2046 ,p_fm_serial_number
2047 ,p_to_serial_number
2048 ,p_product_code
2049 ,p_product_transaction_id);
2050
2051 px_transaction_interface_id := l_transaction_interface_id;
2052
2053
2054 EXCEPTION
2055 WHEN FND_API.G_EXC_ERROR THEN
2056 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2057 P_API_NAME => L_API_NAME
2058 ,P_PKG_NAME => G_PKG_NAME
2059 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2060 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2061 ,X_MSG_COUNT => X_MSG_COUNT
2062 ,X_MSG_DATA => X_MESSAGE
2063 ,X_RETURN_STATUS => X_RETURN_STATUS);
2064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2065 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2066 P_API_NAME => L_API_NAME
2067 ,P_PKG_NAME => G_PKG_NAME
2068 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2069 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2070 ,X_MSG_COUNT => X_MSG_COUNT
2071 ,X_MSG_DATA => X_MESSAGE
2072 ,X_RETURN_STATUS => X_RETURN_STATUS);
2073 WHEN OTHERS THEN
2074 fnd_message.set_name('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
2075 fnd_message.set_token('ROUTINE',l_api_name,false);
2076 fnd_message.set_token('SQLERRM',sqlerrm,false);
2077 fnd_msg_pub.add;
2078 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2079 P_API_NAME => L_API_NAME
2080 ,P_PKG_NAME => G_PKG_NAME
2081 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
2082 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2083 ,X_MSG_COUNT => X_MSG_COUNT
2084 ,X_MSG_DATA => X_MESSAGE
2085 ,X_RETURN_STATUS => X_RETURN_STATUS);
2086 END;
2087
2088 PROCEDURE rcv_online_request (p_group_id IN NUMBER,
2089 x_return_status OUT NOCOPY VARCHAR2,
2090 x_msg_data OUT NOCOPY VARCHAR2)
2091 IS
2092 rc NUMBER;
2093 l_api_name varchar2(20) := 'rcv_online_request';
2094 l_timeout NUMBER := 300;
2095 l_outcome VARCHAR2(200) := NULL;
2096 l_message VARCHAR2(2000) := NULL;
2097 l_return_status VARCHAR2(5) := fnd_api.g_ret_sts_success;
2098 l_msg_count NUMBER;
2099 x_str varchar2(6000) := NULL;
2100 DELETE_ROWS BOOLEAN := FALSE;
2101 r_val1 varchar2(300) := NULL;
2102 r_val2 varchar2(300) := NULL;
2103 r_val3 varchar2(300) := NULL;
2104 r_val4 varchar2(300) := NULL;
2105 r_val5 varchar2(300) := NULL;
2106 r_val6 varchar2(300) := NULL;
2107 r_val7 varchar2(300) := NULL;
2108 r_val8 varchar2(300) := NULL;
2109 r_val9 varchar2(300) := NULL;
2110 r_val10 varchar2(300) := NULL;
2111 r_val11 varchar2(300) := NULL;
2112 r_val12 varchar2(300) := NULL;
2113 r_val13 varchar2(300) := NULL;
2114 r_val14 varchar2(300) := NULL;
2115 r_val15 varchar2(300) := NULL;
2116 r_val16 varchar2(300) := NULL;
2117 r_val17 varchar2(300) := NULL;
2118 r_val18 varchar2(300) := NULL;
2119 r_val19 varchar2(300) := NULL;
2120 r_val20 varchar2(300) := NULL;
2121 l_progress VARCHAR2(10) := '10';
2122 x_msg_count number;
2123 po_message varchar2(2000);
2124 BEGIN
2125 x_return_status := fnd_api.g_ret_sts_success;
2126 -- debug_handler := UTL_FILE.FOPEN('/slot06/oracle/SCMC1MQ1db/9.2.0/temp', 'FieldServicePortal.log','a');
2127
2128
2129 rc := fnd_transaction.synchronous
2130 (
2131 l_timeout, l_outcome, l_message, 'PO', 'RCVTPO',
2132 'ONLINE',p_group_id,
2133 NULL, NULL, NULL, NULL, NULL, NULL,
2134 NULL, NULL, NULL, NULL, NULL, NULL,
2135 NULL, NULL, NULL, NULL, NULL, NULL);
2136 /* if UTL_FILE.is_open(debug_handler) then
2137 UTL_FILE.PUT_LINE(debug_handler,'RC ' || rc);
2138 UTL_FILE.PUT_LINE(debug_handler,'l_outcome ' || l_outcome);
2139 UTL_FILE.PUT_LINE(debug_handler,'l_message ' || l_message);
2140 UTL_FILE.FCLOSE(debug_handler);
2141 END IF;*/
2142 IF (rc = 1) THEN
2143 fnd_message.set_name('FND', 'TM-TIMEOUT');
2144 FND_MESSAGE.set_name('FND','CONC-Error running standalone');
2145 fnd_message.set_token('PROGRAM', 'Receiving Transaction Manager - RCVOLTM');
2146 fnd_message.set_token('REQUEST', p_group_id);
2147 fnd_message.set_token('REASON', x_str);
2148 fnd_msg_pub.ADD;
2149 x_return_status := fnd_api.g_ret_sts_error;
2150 ELSIF (rc = 2) THEN
2151 fnd_message.set_name('FND', 'TM-SVC LOCK HANDLE FAILED');
2152 FND_MESSAGE.set_name('FND','CONC-Error running standalone');
2156 fnd_msg_pub.ADD;
2153 fnd_message.set_token('PROGRAM', 'Receiving Transaction Manager - RCVOLTM');
2154 fnd_message.set_token('REQUEST', p_group_id);
2155 fnd_message.set_token('REASON', x_str);
2157 x_return_status := fnd_api.g_ret_sts_error;
2158 ELSIF (rc = 3 or
2159 (l_outcome IN ('WARNING', 'ERROR'))) THEN
2160 BEGIN
2161 select ERROR_MESSAGE INTO po_message
2162 from po_interface_errors pie,
2163 RCV_HEADERS_INTERFACE rhi
2164 where BATCH_ID = p_group_id
2165 and TABLE_NAME = 'RCV_HEADERS_INTERFACE'
2166 and pie.interface_header_id = rhi.header_interface_id
2167 and PROCESSING_STATUS_CODE = 'ERROR' ;
2168 FND_MESSAGE.set_name('CSP','CSP_PO_RECEIVE_ERROR');
2169 fnd_message.set_token('ERROR_MESSAGE', po_message );
2170 fnd_msg_pub.ADD;
2171 x_return_status := fnd_api.g_ret_sts_error;
2172 Exception
2173 when NO_DATA_FOUND THEN
2174
2175 rc := fnd_transaction.get_values
2176 (
2177 r_val1, r_val2, r_val3, r_val4, r_val5, r_val6,
2178 r_val7, r_val8, r_val9, r_val10, r_val11, r_val12,
2179 r_val13, r_val14, r_val15, r_val16, r_val17, r_val18,
2180 r_val19, r_val20);
2181 x_str := r_val1;
2182 /* debug_handler := UTL_FILE.FOPEN('/slot06/oracle/SCMC1MQ1db/9.2.0/temp', 'FieldServicePortal.log','a');
2183 if UTL_FILE.is_open(debug_handler) then
2184 UTL_FILE.PUT_LINE(debug_handler,'r_val1 ' || r_val11);
2185 UTL_FILE.PUT_LINE(debug_handler,'r_val2 ' || r_val12);
2186 UTL_FILE.PUT_LINE(debug_handler,'r_val3' || r_val13);
2187 UTL_FILE.PUT_LINE(debug_handler,'r_val4' || r_val14);
2188 UTL_FILE.PUT_LINE(debug_handler,'r_val5 ' || r_val15);
2189 UTL_FILE.PUT_LINE(debug_handler,'r_val6 ' || r_val16);
2190 UTL_FILE.PUT_LINE(debug_handler,'r_val7 ' || r_val17);
2191 UTL_FILE.PUT_LINE(debug_handler,'r_val8 ' || r_val18);
2192 UTL_FILE.PUT_LINE(debug_handler,'r_val9 ' || r_val19);
2193 UTL_FILE.PUT_LINE(debug_handler,'r_va20 ' || r_val20);
2194
2195 FND_MSG_PUB.get(p_data => x_msg_data,
2196 p_msg_index_out => x_msg_count);
2197 UTL_FILE.PUT_LINE(debug_handler,'meesage count' || FND_MSG_PUB.Count_Msg);
2198 UTL_FILE.PUT_LINE(debug_handler,'x_msg_data ' || x_msg_data);
2199 UTL_FILE.PUT_LINE(debug_handler,'x_msg_data ' || x_msg_count);
2200 UTL_FILE.FCLOSE(debug_handler);
2201 END IF;*/
2202
2203 IF (r_val2 IS NOT NULL) THEN
2204 x_str := x_str || ' ' || r_val2;
2205 END IF;
2206 IF (r_val3 IS NOT NULL) THEN
2207 x_str := x_str || ' ' || r_val3;
2208 END IF;
2209 IF (r_val4 IS NOT NULL) THEN
2210 x_str := x_str || ' ' || r_val4;
2211 END IF;
2212 IF (r_val5 IS NOT NULL) THEN
2213 x_str := x_str || ' ' || r_val5;
2214 END IF;
2215 IF (r_val6 IS NOT NULL) THEN
2216 x_str := x_str || ' ' || r_val6;
2217 END IF;
2218 IF (r_val7 IS NOT NULL) THEN
2219 x_str := x_str || ' ' || r_val7;
2220 END IF;
2221 IF (r_val8 IS NOT NULL) THEN
2222 x_str := x_str || ' ' || r_val8;
2223 END IF;
2224 IF (r_val9 IS NOT NULL) THEN
2225 x_str := x_str || ' ' || r_val9;
2226 END IF;
2227 IF (r_val10 IS NOT NULL) THEN
2228 x_str := x_str || ' ' || r_val10;
2229 END IF;
2230 IF (r_val11 IS NOT NULL) THEN
2231 x_str := x_str || ' '|| r_val11;
2232 END IF;
2233 IF (r_val12 IS NOT NULL) THEN
2234 x_str := x_str || ' ' || r_val12;
2235 END IF;
2236 IF (r_val13 IS NOT NULL) THEN
2237 x_str := x_str || ' ' || r_val13;
2238 END IF;
2239 IF (r_val14 IS NOT NULL) THEN
2240 x_str := x_str || ' '|| r_val14;
2241 END IF;
2242 IF (r_val15 IS NOT NULL) THEN
2243 x_str := x_str || ' '|| r_val15;
2244 END IF;
2245 IF (r_val16 IS NOT NULL) THEN
2246 x_str := x_str || ' '|| r_val16;
2247 END IF;
2248 IF (r_val17 IS NOT NULL) THEN
2249 x_str := x_str || ' ' || r_val17;
2250 END IF;
2251 IF (r_val18 IS NOT NULL) THEN
2252 x_str := x_str || ' '|| r_val18;
2253 END IF;
2254 IF (r_val19 IS NOT NULL) THEN
2255 x_str := x_str || ' '|| r_val19;
2256 END IF;
2257 IF (r_val20 IS NOT NULL) THEN
2258 x_str := x_str || ' ' || r_val20;
2259 END IF;
2260 FND_MESSAGE.set_name('FND','CONC-Error running standalone');
2261 fnd_message.set_token('PROGRAM', 'Receiving Transaction Manager - RCVOLTM');
2262 fnd_message.set_token('REQUEST', p_group_id);
2263 fnd_message.set_token('REASON', x_str);
2264 fnd_msg_pub.ADD;
2265 x_return_status := fnd_api.g_ret_sts_error;
2266 END;
2267 END IF;
2268 EXCEPTION
2269 WHEN OTHERS THEN
2270 fnd_message.set_name('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
2271 fnd_message.set_token('ROUTINE',l_api_name,FALSE);
2272 fnd_message.set_token('SQLERRM',sqlerrm,FALSE);
2273 fnd_msg_pub.add;
2274 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2275 END rcv_online_request;
2276
2277 /*
2278 Function : USER_INPUT_REQUIRED
2279 */
2280
2281 FUNCTION USER_INPUT_REQUIRED(p_header_id number)
2282 RETURN VARCHAR2 IS
2283 l_header_id number;
2284 l_count number;
2285 l_locator_controlled Varchar2(1);
2286
2287 CURSOR check_serial_lot_revision (v_header_id Number) is
2288 select ms.po_header_id
2289 from mtl_supply ms,
2290 po_lines_all pla,
2291 mtl_system_items_b msi
2292 where msi.organization_id = ms.to_organization_id
2293 and msi.inventory_item_id = ms.item_id
2294 and pla.po_line_id = ms.po_line_id
2295 and ms.po_header_id = v_header_id
2296 and (msi.serial_number_control_code <> 1
2297 or msi.lot_control_code <> 1
2298 or (msi.revision_qty_control_code = 2 and pla.item_revision is null));
2299
2300
2301 CURSOR find_all_items(v_header_id number) is
2302 select ms.item_id,
2303 ms.to_organization_id,
2304 ms.to_subinventory
2305 from mtl_supply ms
2306 where ms.po_header_id = v_header_id;
2307
2308 CURSOR locator_check (v_org_id Number, v_item_id number, v_sub_inv varchar2) IS
2309 select 'Y'
2310 from mtl_parameters a,
2314 and a.organization_id = c.organization_id
2311 mtl_system_items_b b,
2312 mtl_secondary_inventories c
2313 where a.organization_id = b.organization_id
2315 and a.organization_id = v_org_id
2316 and b.inventory_item_id = v_item_id
2317 and c.secondary_inventory_name = v_sub_inv
2318 and ( a.stock_locator_control_code in (2,3) --Org Control should be 2 or 3
2319 OR a.stock_locator_control_code = 4 AND c.locator_type in (2,3) --org Control 4 and sub control 2 or 3
2320 OR a.stock_locator_control_code = 4 AND c.locator_type = 5 AND b.location_control_code in (2,3) );
2321
2322
2323
2324 begin
2325 l_header_id := p_header_id;
2326 l_locator_controlled := 'N'; -- not locator controlled
2327 l_count := 0;
2328
2329 --First Check Serial Lot and Revision Control of Item
2330 OPEN check_serial_lot_revision(l_header_id);
2331 FETCH check_serial_lot_revision INTO l_count;
2332 CLOSE check_serial_lot_revision;
2333
2334 IF (nvl(l_count,0) > 0) THEN
2335 RETURN 'Y';
2336 END IF;
2337
2338 --Checking for Locator
2339 for r_find_all_items in find_all_items(l_header_id)
2340 LOOP
2341 --Now we need to find out whethere this is Locator Controlled Item for this org and Sub
2342 OPEN locator_check (r_find_all_items.TO_ORGANIZATION_ID ,
2343 r_find_all_items.ITEM_ID,
2344 r_find_all_items.TO_SUBINVENTORY);
2345 FETCH locator_check INTO l_locator_controlled;
2346 CLOSE locator_check;
2347 IF (l_locator_controlled = 'Y') THEN
2348 RETURN 'Y';
2349 END IF;
2350 END LOOP;
2351
2352 RETURN 'N';
2353
2354 END USER_INPUT_REQUIRED;
2355
2356
2357
2358 END CSP_RECEIVE_PVT;