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