[Home] [Help]
PACKAGE BODY: APPS.CSD_INTERNAL_ORDERS_PVT
Source
4 -- Package name : CSD_INTERNAL_ORDERS_PVT
1 PACKAGE BODY CSD_INTERNAL_ORDERS_PVT AS
2 /* $Header: csdviorb.pls 120.9.12020000.4 2013/04/10 19:20:15 swai ship $ */
3 -- Start of Comments
5 -- Purpose : This package will contain all the procedures and functions used by the Internal.
6 -- Orders. Usage of this package is strictly confined to Oracle Depot Repair
7 -- Development.
8 --
9 -- History : 06/04/2010, Created by Sudheer Bhat
10 -- NOTE :
11 -- End of Comments
12
13 -- logging globals.
14 G_LEVEL_PROCEDURE NUMBER := FND_LOG.LEVEL_PROCEDURE;
15 G_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
16
17 G_RET_STS_SUCCESS VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
21 TYPE SN_ASSOCIATIVE_ARRAY IS TABLE OF VARCHAR2(3) INDEX BY VARCHAR2(30);
18 G_RET_STS_ERROR VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
19 G_DELIVERY_DETAIL_ID NUMBER;
20
22
23 PROCEDURE populate_rcv_int_tables(p_product_txn_id IN NUMBER,
24 p_receiving_subinv IN VARCHAR2,
25 x_request_group_id OUT NOCOPY NUMBER)
26 IS
27 l_hdr_interface_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
28 l_to_org_id NUMBER;
29 l_destn_ou NUMBER;
30 l_process_sts_pending CONSTANT VARCHAR2(10) := 'PENDING';
31 l_txn_type_new CONSTANT VARCHAR2(10) := 'NEW';
32 l_validation_flag CONSTANT VARCHAR2(1) := 'Y';
33 l_emp_id NUMBER;
34 l_lot_control_flag NUMBER;
35 l_serial_control_flag NUMBER;
36 l_primary_uom VARCHAR2(5);
37 l_inventory_item_id NUMBER;
38 l_subinventory VARCHAR2(30);
39 l_quantity JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
40 l_uom VARCHAR2(15);
41 l_from_org NUMBER;
42 l_requisition_line_id NUMBER;
43 l_deliver_to_location_id NUMBER;
44 l_intf_txn_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
45 l_fm_serial_num_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
46 l_to_serial_num_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
47 lc_api_name CONSTANT VARCHAR2(100) := 'CSD_INTERNAL_ORDERS_PVT.populate_rcv_int_tables';
48 l_shipment_header_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
49 l_shipment_line_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
50 l_shipment_num JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
51 l_shipped_date JTF_DATE_TABLE := JTF_DATE_TABLE();
52
53 BEGIN
54 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
55 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting item controls');
56 END IF;
57 -- get the item controls.
58 SELECT msi.serial_number_control_code, msi.lot_control_code, msi.primary_uom_code,
59 msi.inventory_item_id,mis.subinventory_code,cpt.rcv_into_org,cpt.rcv_into_ou,
60 msi.primary_unit_of_measure
61 INTO l_serial_control_flag,l_lot_control_flag,l_primary_uom,
62 l_inventory_item_id,l_subinventory,l_to_org_id,l_destn_ou,l_uom
63 FROM mtl_system_items_b msi, mtl_item_sub_defaults mis,csd_product_transactions cpt
64 WHERE cpt.product_transaction_id = p_product_txn_id
65 AND cpt.rcv_into_org = msi.organization_id
66 AND cpt.inventory_item_id = msi.inventory_item_id
67 AND msi.organization_id = mis.organization_id(+)
68 AND msi.inventory_item_id = mis.inventory_item_id(+);
69
70 -- 1. Get the requisition line id and from org information.
71 SELECT cpt.ship_from_org,prl.requisition_line_id,prl.deliver_to_location_id,
72 NVL(p_receiving_subinv,l_subinventory)
73 INTO l_from_org,l_requisition_line_id,l_deliver_to_location_id,l_subinventory
74 FROM csd_product_transactions cpt, po_requisition_lines_all prl
75 WHERE cpt.product_transaction_id = p_product_txn_id
76 AND cpt.req_header_id = prl.requisition_header_id
77 AND NVL(cpt.req_line_id,prl.requisition_line_id) = prl.requisition_line_id;
78
79 -- 2 .get the shipment header id and line id.
80 SELECT rsh.shipment_header_id, rsl.shipment_line_id,rsh.shipment_num,rsh.shipped_date,rsl.quantity_shipped
81 BULK COLLECT INTO l_shipment_header_id,l_shipment_line_id,l_shipment_num,l_shipped_date,l_quantity
82 FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl
83 WHERE rsl.requisition_line_id = l_requisition_line_id
84 AND rsh.shipment_header_id = rsl.shipment_header_id
85 AND nvl(rsl.quantity_received,0) = 0
86 AND rsl.mmt_transaction_id IN (SELECT mmt.transaction_id
87 FROM csd_product_transactions cpt,mtl_material_transactions mmt
88 WHERE cpt.delivery_detail_id = mmt.picking_line_id
89 AND cpt.product_transaction_id = p_product_txn_id
90 UNION ALL
91 SELECT mmt.transaction_id
92 FROM wsh_delivery_details wdd, csd_product_transactions cpt,mtl_material_transactions mmt
93 WHERE cpt.delivery_detail_id = wdd.split_from_delivery_detail_id
94 AND cpt.order_header_id = wdd.source_header_id
95 AND wdd.source_code = 'OE'
96 AND cpt.product_transaction_id = p_product_txn_id
97 AND wdd.delivery_detail_id = mmt.picking_line_id
98 AND NOT EXISTS(
99 SELECT 'exists'
100 FROM csd_product_transactions cpt1
101 WHERE wdd.delivery_detail_id = cpt1.delivery_detail_id
102 AND cpt.order_header_id = cpt1.order_header_id
103 )
104 );
105
106 IF l_shipment_header_id.COUNT = 0
107 THEN
108 FND_MESSAGE.SET_NAME('CSD','CSD_INT_SHIPMENT_MIS');
109 FND_MSG_PUB.ADD;
110 RAISE fnd_api.g_exc_error;
111 END IF;
112
113 -- the subinventory is not passed in and in the organization item setup,
114 -- default receiving subinventory is not setup. Raise the error.
115 IF l_subinventory IS NULL
116 THEN
117 RAISE no_data_found;
118 END IF;
119
120 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
121 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Inserting into rcv_headers_interface');
122 END IF;
123
124 -- populate the rcv transactions header.
125 SELECT rcv_interface_groups_s.NEXTVAL INTO x_request_group_id FROM dual;
126
127 l_hdr_interface_id.EXTEND(l_shipment_num.COUNT);
128
132 header_interface_id,
129 FOR k IN l_shipment_num.FIRST ..l_shipment_num.LAST
130 LOOP
131 INSERT INTO rcv_headers_interface (
133 group_id,
134 ship_to_organization_id,
135 expected_receipt_date, last_update_date,
136 last_updated_by, last_update_login, creation_date,
137 created_by, validation_flag, processing_status_code,
138 receipt_source_code, transaction_type,
139 shipped_Date,
140 shipment_num)
141 VALUES (rcv_headers_interface_s.NEXTVAL,
142 x_request_group_id,
143 l_to_org_id,
144 SYSDATE,SYSDATE,
145 fnd_global.user_id,fnd_global.login_id,SYSDATE,
146 fnd_global.user_id,l_validation_flag,l_process_sts_pending,
147 'INTERNAL ORDER',l_txn_type_new,
148 l_shipped_date(k),
149 l_shipment_num(k))
150 RETURNING header_interface_id
151 INTO l_hdr_interface_id(k);
152 END LOOP;
153
154 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
155 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting employee id and requisition information '||x_request_group_id);
156 END IF;
157
158 -- 2. get the employee id
159 csd_receive_util.get_employee_id (fnd_global.user_id, l_emp_id);
160
161
162 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
163 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Inserting into rcv_transactions_interface');
164 END IF;
165
166 -- 3. insert into rcv transactions interface table.
167 l_intf_txn_id.EXTEND(l_shipment_header_id.COUNT);
168
169 FOR i IN l_shipment_header_id.FIRST ..l_shipment_header_id.LAST
170 LOOP
171 INSERT INTO rcv_transactions_interface
172 (interface_transaction_id,
173 header_interface_id,
174 GROUP_ID,
175 transaction_date,
176 quantity,
177 unit_of_measure,
178 item_id,
179 item_revision,
180 to_organization_id,
181 ship_to_location_id,
182 subinventory,
183 last_update_date,
184 last_updated_by,
185 creation_date,
186 created_by,
187 last_update_login,
188 validation_flag,
189 source_document_code,
190 interface_source_code,
191 auto_transact_code,
192 receipt_source_code,
193 transaction_type,
194 processing_status_code,
195 processing_mode_code,
196 transaction_status_code,
197 category_id,
198 uom_code,
199 employee_id,
200 primary_quantity,
201 primary_unit_of_measure,
202 routing_header_id,
203 routing_step_id,
204 inspection_status_code,
205 destination_type_code,
206 expected_receipt_date,
207 destination_context,
208 use_mtl_lot,
209 use_mtl_serial,
210 source_doc_quantity,
211 source_doc_unit_of_measure,
212 requisition_line_id,
213 shipped_date,
214 shipment_num,
215 from_organization_id,
216 locator_id,
217 deliver_to_location_id,
218 shipment_header_id,
219 shipment_line_id,
220 org_id
221 )
222 VALUES (rcv_transactions_interface_s.NEXTVAL,
223 l_hdr_interface_id(i),
224 x_request_group_id,
225 SYSDATE,
226 l_quantity(i),
227 l_uom,
228 l_inventory_item_id,
229 null,
230 l_to_org_id,
231 null,
232 l_subinventory,
233 SYSDATE,
234 fnd_global.user_id,
235 SYSDATE,
236 fnd_global.user_id,
237 fnd_global.login_id,
238 'Y'
239 , 'REQ'
240 , 'RCV'
241 , 'DELIVER'
242 , 'INTERNAL ORDER'
243 , 'RECEIVE'
244 , 'PENDING'
245 , 'ONLINE'
246 , 'PENDING'
247 , null
248 , l_primary_uom
249 , l_emp_id
250 ,l_quantity(i)
251 ,l_uom
252 , 1
253 , 1
254 , 'NOT INSPECTED'
255 , 'INVENTORY'
256 , SYSDATE
257 , 'INVENTORY'
258 , l_lot_control_flag
259 , l_serial_control_flag
260 , l_quantity(i)
261 , l_uom
262 , l_requisition_line_id
263 , l_shipped_date(i)
264 , l_shipment_num(i)
265 , l_from_org
266 , null
267 , l_deliver_to_location_id
268 , l_shipment_header_id(i)
269 , l_shipment_line_id(i)
270 , l_destn_ou
271 )
272 RETURNING interface_transaction_id
273 INTO l_intf_txn_id(i);
274
275 -- no support for Lot numbers yet.
276
277 -- Populate the serial numbers interface.
278
279 IF l_serial_control_flag IN (2,5,6)
280 THEN
281 SELECT wsn.fm_serial_number,
282 wsn.to_serial_number
283 BULK COLLECT INTO
284 l_fm_serial_num_tbl,
285 l_to_serial_num_tbl
286 FROM wsh_delivery_details wdd,
287 wsh_serial_numbers wsn,
288 wsh_delivery_assignments wda
289 WHERE wda.delivery_id = to_number(l_shipment_num(i))
290 AND wda.delivery_detail_id = wdd.delivery_detail_id
291 AND wdd.source_code = 'OE'
292 AND wdd.delivery_detail_id = wsn.delivery_detail_id;
293
294 -- insert into mtl serial number interface.
295 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
299 INSERT INTO mtl_serial_numbers_interface
296 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Inserting into mtl_serial_numbers_interface total of '||l_fm_serial_num_tbl.COUNT||' records');
297 END IF;
298 FORALL j in 1 ..l_fm_serial_num_tbl.COUNT
300 (transaction_interface_id, source_code,
301 source_line_id, last_update_date, last_updated_by,
302 creation_date, created_by, last_update_login,
303 fm_serial_number,
304 to_serial_number,
305 process_flag,
306 product_transaction_id,
307 product_code
308 )
309 VALUES(l_intf_txn_id(i),'CSD',
310 1,SYSDATE,fnd_global.user_id,
311 SYSDATE,fnd_global.user_id,fnd_global.login_id,
312 l_fm_serial_num_tbl(j),
313 l_to_serial_num_tbl(j),
314 1,
315 l_intf_txn_id(i),
316 'RCV');
317
318 END IF;
319 END LOOP;
320 -- commit after all the interface tables are populated
321 COMMIT WORK;
322
323 END populate_rcv_int_tables;
324
325 PROCEDURE handle_partial_action (action IN VARCHAR2,
326 p_order_header_id IN NUMBER,
327 p_delivery_detail_id IN NUMBER,
328 p_delivery_detail_id_new IN NUMBER
329 )
330 IS
331 l_orig_delivery_detail_id NUMBER;
332 l_total_picked_quantity NUMBER;
333
334 BEGIN
335
336 IF action = 'PICK'
337 THEN
338 INSERT INTO csd_product_transactions
339 (product_transaction_id,
340 repair_line_id,
341 req_header_id,
342 order_header_id,
343 order_line_id,
344 exp_quantity,
345 inventory_item_id,
346 ship_from_ou,
347 ship_from_org,
348 rcv_into_ou,
349 rcv_into_org,
350 creation_date,
351 last_update_date,
352 last_update_login,
353 created_by,
354 last_updated_by,
355 quantity_available,
356 delivery_detail_id,
357 object_version_number,
358 req_line_id
359 )
360 SELECT csd_product_transactions_s1.nextval,
361 repair_line_id,
362 req_header_id,
363 order_header_id,
364 order_line_id,
365 exp_quantity,
366 inventory_item_id,
367 ship_from_ou,
368 ship_from_org,
369 rcv_into_ou,
370 rcv_into_org,
371 sysdate creation_date,
372 sysdate last_update_date,
373 fnd_global.login_id last_update_login,
374 fnd_global.user_id created_by,
375 fnd_global.user_id last_updated_by,
376 (NVL(quantity_available,exp_quantity) - quantity_picked) available_quantity,
377 p_delivery_detail_id,
378 to_number('1'),
379 req_line_id
380 FROM csd_product_transactions
381 WHERE order_header_id = p_order_header_id
382 AND delivery_detail_id = p_delivery_detail_id_new;
383
384 UPDATE csd_product_transactions SET quantity_available = 0
385 WHERE delivery_detail_id = p_delivery_detail_id_new;
386
387 END IF;
388 END handle_partial_action;
389
390 PROCEDURE create_internal_requisition(
391 p_api_version IN NUMBER,
392 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
393 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
394 p_product_txn_id IN NUMBER,
395 p_destination_ou IN NUMBER,
396 p_destination_org IN NUMBER,
397 p_destination_loc_id IN NUMBER,
398 p_source_ou IN NUMBER,
399 p_source_org IN NUMBER,
400 p_need_by_date IN DATE,
401 p_quantity IN NUMBER DEFAULT NULL,
402 p_source_subinv IN VARCHAR2 DEFAULT NULL,
403 p_destination_subinv IN VARCHAR2 DEFAULT NULL,
404 x_requisition OUT NOCOPY VARCHAR2,
405 x_requisition_id OUT NOCOPY NUMBER,
406 x_msg_count OUT NOCOPY NUMBER,
407 x_msg_data OUT NOCOPY VARCHAR2,
408 x_return_status OUT NOCOPY VARCHAR2)
409 IS
410 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_INTERNAL_ORDERS_PVT.create_internal_requisition';
411 lc_api_version CONSTANT NUMBER := 1.0;
412 l_product_txn_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
413 l_destination_ous JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
414 l_destination_orgs JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
415 l_destination_loc_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
416 l_source_ous JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
417 l_source_orgs JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
418 l_quantitys JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
419
420 BEGIN
421
422 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
423 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
424 END IF;
425
426 -- standard check for API compatibility.
427 IF NOT Fnd_Api.Compatible_API_Call
428 (lc_api_version,
429 p_api_version,
430 lc_api_name,
431 G_PKG_NAME)
432 THEN
433 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
434 END IF;
435
436 IF Fnd_Api.to_Boolean(p_init_msg_list)
437 THEN
438 Fnd_Msg_Pub.initialize;
439 END IF;
440
441 x_return_status := G_RET_STS_SUCCESS;
442
443 l_product_txn_ids.EXTEND;
444 l_destination_ous.EXTEND;
445 l_destination_orgs.EXTEND;
446 l_destination_loc_ids.EXTEND;
447 l_source_ous.EXTEND;
448 l_source_orgs.EXTEND;
449 l_quantitys.EXTEND;
450
451 l_product_txn_ids(1) := p_product_txn_id;
452 l_destination_ous(1) := p_destination_ou;
453 l_destination_orgs(1) := p_destination_org;
457 l_quantitys(1) := p_quantity;
454 l_destination_loc_ids(1) := p_destination_loc_id;
455 l_source_ous(1) := p_source_ou;
456 l_source_orgs(1) := p_source_org;
458
459 create_internal_requisition(
460 p_api_version => p_api_version,
461 p_init_msg_list => p_init_msg_list,
462 p_commit => p_commit,
463 p_product_txn_ids => l_product_txn_ids,
464 p_destination_ous => l_destination_ous,
465 p_destination_orgs => l_destination_orgs,
466 p_destination_loc_ids => l_destination_loc_ids,
467 p_source_ous => l_source_ous,
468 p_source_orgs => l_source_orgs,
469 p_need_by_date => p_need_by_date,
470 p_quantitys => l_quantitys,
471 x_requisition => x_requisition,
472 x_requisition_id => x_requisition_id,
473 x_msg_count => x_msg_count,
474 x_msg_data => x_msg_data,
475 x_return_status => x_return_status);
476
477 END create_internal_requisition;
478
479 PROCEDURE create_internal_requisition(
480 p_api_version IN NUMBER,
481 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
482 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
483 p_product_txn_ids IN JTF_NUMBER_TABLE,
484 p_destination_ous IN JTF_NUMBER_TABLE,
485 p_destination_orgs IN JTF_NUMBER_TABLE,
486 p_destination_loc_ids IN JTF_NUMBER_TABLE,
487 p_source_ous IN JTF_NUMBER_TABLE,
488 p_source_orgs IN JTF_NUMBER_TABLE,
489 p_need_by_date IN DATE,
490 p_quantitys IN JTF_NUMBER_TABLE,
491 p_source_subinvs IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
492 p_destination_subinvs IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
493 x_requisition OUT NOCOPY VARCHAR2,
494 x_requisition_id OUT NOCOPY NUMBER,
495 x_msg_count OUT NOCOPY NUMBER,
496 x_msg_data OUT NOCOPY VARCHAR2,
497 x_return_status OUT NOCOPY VARCHAR2)
498 IS
499 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_INTERNAL_ORDERS_PVT.create_internal_requisition';
500 lc_api_version CONSTANT NUMBER := 1.0;
501 l_user_id NUMBER := fnd_global.user_id;
502 l_person_id NUMBER;
503 l_currency_code VARCHAR2(15);
504 l_quantitys JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
505 l_item_descriptions JTF_VARCHAR2_TABLE_300 := JTF_VARCHAR2_TABLE_300();
506 l_uom_codes JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
507 l_inventory_item_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
508 l_request_id NUMBER;
509 l_material_accounts JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
510 l_success BOOLEAN := TRUE;
511 l_source_subinvs JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
512 l_destination_subinvs JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
513 x_phase VARCHAR2(15);
514 x_status VARCHAR2(10);
515 x_dev_phase VARCHAR2(15);
516 x_dev_status VARCHAR2(10);
517 x_message VARCHAR2(2000);
518
519 BEGIN
520
521 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
522 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
523 END IF;
524
525 -- standard check for API compatibility.
526 IF NOT Fnd_Api.Compatible_API_Call
527 (lc_api_version,
528 p_api_version,
529 lc_api_name,
530 G_PKG_NAME)
531 THEN
532 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
533 END IF;
534
535 IF Fnd_Api.to_Boolean(p_init_msg_list)
536 THEN
537 Fnd_Msg_Pub.initialize;
538 END IF;
539
540 x_return_status := G_RET_STS_SUCCESS;
541 -- if the source and destination subinventories are passed in then we will use them
542 -- else we will use dummy plsql tables.
543 IF p_source_subinvs IS NOT NULL
544 THEN
545 l_source_subinvs := p_source_subinvs;
546 ELSE
547 l_source_subinvs.EXTEND(p_product_txn_ids.COUNT);
548 END IF;
549
550 IF p_destination_subinvs IS NOT NULL
551 THEN
552 l_destination_subinvs := p_destination_subinvs;
553 ELSE
554 l_destination_subinvs.EXTEND(p_product_txn_ids.COUNT);
555 END IF;
556
557 -- the program logic. First create the internal requisition.
558 -- we dont do any validations here. We let the REQIMPORT program validate all
559 -- the values for us.
560 -- step 1. Get the person_id
561 SELECT employee_id
562 INTO l_person_id
563 FROM fnd_user
564 WHERE user_id = l_user_id;
565
566 -- step 2. get the currency code for the destination OU.
567 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
568 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting currency code');
569 END IF;
570
571 -- bug#12337175 subhat.
572 -- if the destination OU is not an inventory org, then this query may not retrieve the correct/no records.
573 -- So we should be looking for Operating Unit Information, and the set of books id maps to org_information3 for
574 -- org_information_context = Operating Unit Information
575 SELECT currency_code
576 INTO l_currency_code
577 FROM gl_sets_of_books,hr_organization_information
578 WHERE set_of_books_id = org_information3 --org_information1
579 AND organization_id = p_destination_ous(1)
580 AND org_information_context = 'Operating Unit Information'; --'Accounting Information';
581
582 -- step 3. get the quantity required.
583 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
584 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Currency code='||l_currency_code||'. Getting required quantity');
585 END IF;
586
587 FOR i IN 1 ..p_product_txn_ids.COUNT
588 LOOP
589 l_quantitys.EXTEND;
593 l_material_accounts.EXTEND;
590 l_inventory_item_ids.EXTEND;
591 l_item_descriptions.EXTEND;
592 l_uom_codes.EXTEND;
594 BEGIN
595 SELECT NVL(p_quantitys(i),cpt.exp_quantity),cpt.inventory_item_id,msi.description,msi.primary_uom_code
596 INTO l_quantitys(i),l_inventory_item_ids(i),l_item_descriptions(i),l_uom_codes(i)
597 FROM csd_product_transactions cpt,mtl_system_items_b msi
598 WHERE cpt.product_transaction_id = p_product_txn_ids(i)
599 AND cpt.inventory_item_id = msi.inventory_item_id
600 AND msi.organization_id = p_destination_orgs(i);
601 EXCEPTION
602 WHEN no_data_found THEN
603 FND_MESSAGE.SET_NAME('CSD','CSD_MIS_ITEM_ORG_ASG');
604 FND_MSG_PUB.ADD;
605 RAISE fnd_api.g_exc_error;
606 END;
607 -- step 4. get the material account id.
608 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
609 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting material account Id');
610 END IF;
611
612 SELECT material_account
613 INTO l_material_accounts(i)
614 FROM mtl_parameters
615 WHERE organization_id = p_destination_orgs(i);
616 END LOOP;
617
618 -- step 4. insert the records into requisitions interface.
619 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
620 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Before inserting into requisitions interface.');
621 END IF;
622
623 FORALL i IN 1 ..p_product_txn_ids.COUNT
624 INSERT INTO po_requisitions_interface_all (
625 interface_source_code,
626 destination_type_code,
627 authorization_status,
628 preparer_id, -- person id of the user name
629 quantity,
630 destination_organization_id,
631 deliver_to_location_id,
632 deliver_to_requestor_id,
633 source_type_code,
634 category_id,
635 item_description,
636 uom_code,
637 unit_price,
638 need_by_date,
639 wip_entity_id,
640 wip_operation_seq_num,
641 charge_account_id,
642 variance_account_id,
643 item_id,
644 wip_resource_seq_num,
645 suggested_vendor_id,
646 suggested_vendor_name,
647 suggested_vendor_site,
648 suggested_vendor_phone,
649 suggested_vendor_item_num,
650 currency_code,
651 project_id,
652 task_id,
653 project_accounting_context,
654 last_updated_by,
655 last_update_date,
656 created_by,
657 creation_date,
658 org_id,
659 reference_num,
660 interface_source_line_id,
661 source_organization_id,
662 source_subinventory,
663 destination_subinventory)
664 VALUES (
665 'CSD',
666 'INVENTORY',
667 'APPROVED',
668 l_person_id,
669 l_quantitys(i),
670 p_destination_orgs(i),
671 p_destination_loc_ids(i),
672 l_person_id,
673 'INVENTORY',
674 null,
675 l_item_descriptions(i),
676 l_uom_codes(i),
677 null,
678 p_need_by_date,
679 null,
680 null,
681 l_material_accounts(i),
682 null,
683 l_inventory_item_ids(i),
684 null,
685 null,
686 null,
687 null,
688 null,
689 null,
690 l_currency_code,
691 null,
692 null,
693 null,
694 l_user_id,
695 sysdate,
696 l_user_id,
697 sysdate,
698 p_destination_ous(i),
699 null,
700 p_product_txn_ids(1),
701 p_source_orgs(i),
702 l_source_subinvs(i),
703 l_destination_subinvs(i)
704 );
705 commit;
706 -- step 5. Call the concurrent program.
707 fnd_request.set_org_id (p_destination_ous(1));
708
709 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
710 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling REQIMPORT CP.');
711 END IF;
712 l_request_id := fnd_request.submit_request(
713 'PO', 'REQIMPORT', NULL, NULL, FALSE,'CSD', NULL, 'ALL',
714 NULL ,'N', 'Y' , chr(0), NULL, NULL, NULL,
715 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
716 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
717 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
718 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
719 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
720 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
721 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
722 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
723 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
724 ) ;
725
726 commit;
727
728 -- step 6. Wait for the requisition import to complete.
729 l_success := fnd_concurrent.wait_for_request(
730 request_id => l_request_id,
731 interval => 5,
732 phase => x_phase,
733 status => x_status,
734 dev_phase => x_dev_phase,
735 dev_status => x_dev_status,
736 message => x_message );
737 IF NOT l_success
738 THEN
739 x_return_status := G_RET_STS_ERROR;
740 x_msg_data := x_message;
741 RETURN;
742 END IF;
743
744 -- step 7. Get the requisition number.
745 BEGIN
746 SELECT segment1,requisition_header_id
747 INTO x_requisition,x_requisition_id
748 FROM po_requisition_headers_all
749 WHERE interface_source_line_id = p_product_txn_ids(1);
750 EXCEPTION
754 RAISE fnd_api.g_exc_error;
751 WHEN no_data_found THEN
752 fnd_message.set_name('CSD','CSD_INT_REQ_FAIL');
753 fnd_msg_pub.add;
755 END;
756
757 EXCEPTION
758 WHEN fnd_api.g_exc_error THEN
759 x_return_status := G_RET_STS_ERROR;
760
761 END create_internal_requisition;
762
763 PROCEDURE create_internal_move_orders(
764 errbuf OUT NOCOPY VARCHAR2,
765 retcode OUT NOCOPY VARCHAR2,
766 p_product_txn_id IN NUMBER,
767 p_destination_ou IN NUMBER,
768 p_destination_org IN NUMBER,
769 p_destination_loc_id IN NUMBER,
770 p_source_ou IN NUMBER,
771 p_source_org IN NUMBER,
772 p_need_by_date IN DATE,
773 p_quantity IN NUMBER DEFAULT NULL,
774 p_source_subinv IN VARCHAR2 DEFAULT NULL,
775 p_destination_subinv IN VARCHAR2 DEFAULT NULL
776 )
777 IS
778 lc_api_name VARCHAR2(80) := 'CSD_INTERNAL_ORDERS_PVT.create_internal_move_orders';
779 l_product_txn_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
780 l_destination_ous JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
781 l_destination_orgs JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
782 l_destination_loc_ids JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
783 l_source_ous JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
784 l_source_orgs JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
785 l_quantitys JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
786 l_source_subinvs JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
787 l_destination_subinvs JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
788
789 BEGIN
790
791 -- create internal requisition.
792 -- update the csd_product_transactions.
793 -- Create Internal Order po program.
794 -- order import program.
795 -- update csd_product_transactions.
796
797 -- Step 1. Create internal requisition.
798 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
799 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling CSD_INTERNAL_ORDERS_PVT.create_internal_requisition');
800 END IF;
801
802 l_product_txn_ids.EXTEND;
803 l_destination_ous.EXTEND;
804 l_destination_orgs.EXTEND;
805 l_destination_loc_ids.EXTEND;
806 l_source_ous.EXTEND;
807 l_source_orgs.EXTEND;
808 l_quantitys.EXTEND;
809 l_source_subinvs.EXTEND;
810 l_destination_subinvs.EXTEND;
811
812 l_product_txn_ids(1) := p_product_txn_id;
813 l_destination_ous(1) := p_destination_ou;
814 l_destination_orgs(1) := p_destination_org;
815 l_destination_loc_ids(1) := p_destination_loc_id;
816 l_source_ous(1) := p_source_ou;
817 l_source_orgs(1) := p_source_org;
818 l_quantitys(1) := p_quantity;
819 l_source_subinvs(1) := p_source_subinv;
820 l_destination_subinvs(1) := p_destination_subinv;
821
822 -- call the overloaded procedure.
823 create_internal_move_orders(
824 errbuf => errbuf,
825 retcode => retcode,
826 p_product_txn_ids => l_product_txn_ids,
827 p_destination_ous => l_destination_ous,
828 p_destination_orgs => l_destination_orgs,
829 p_destination_loc_ids => l_destination_loc_ids,
830 p_source_ous => l_source_ous,
831 p_source_orgs => l_source_orgs,
832 p_need_by_date => p_need_by_date,
833 p_quantitys => l_quantitys,
834 p_source_subinvs => l_source_subinvs,
835 p_destination_subinvs => l_destination_subinvs
836 );
837
838 END create_internal_move_orders;
839
840 PROCEDURE create_internal_move_orders(
841 errbuf OUT NOCOPY VARCHAR2,
842 retcode OUT NOCOPY VARCHAR2,
843 p_product_txn_ids IN JTF_NUMBER_TABLE,
844 p_destination_ous IN JTF_NUMBER_TABLE,
845 p_destination_orgs IN JTF_NUMBER_TABLE,
846 p_destination_loc_ids IN JTF_NUMBER_TABLE,
847 p_source_ous IN JTF_NUMBER_TABLE,
848 p_source_orgs IN JTF_NUMBER_TABLE,
849 p_need_by_date IN DATE,
850 p_quantitys IN JTF_NUMBER_TABLE,
851 p_source_subinvs IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL,
852 p_destination_subinvs IN JTF_VARCHAR2_TABLE_100 DEFAULT NULL
853 )
854 IS
855 lc_api_name VARCHAR2(80) := 'CSD_INTERNAL_ORDERS_PVT.create_internal_move_orders';
856 x_msg_data VARCHAR2(2000);
857 x_return_status VARCHAR2(1);
858 x_msg_count NUMBER;
859 x_requisition_number VARCHAR2(30);
860 x_requisition_id NUMBER;
861 l_request_id NUMBER;
862 l_success BOOLEAN := TRUE;
863 l_iso_header_id NUMBER;
864 l_iso_line_id NUMBER;
865 l_current_ou NUMBER := fnd_global.org_id;
866
867 BEGIN
868 -- create internal requisition.
869 -- update the csd_product_transactions.
870 -- Create Internal Order po program.
871 -- order import program.
872 -- update csd_product_transactions.
873
874 -- Step 1. Create internal requisition.
875 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
876 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling CSD_INTERNAL_ORDERS_PVT.create_internal_requisition');
877 END IF;
878 CSD_INTERNAL_ORDERS_PVT.create_internal_requisition(
879 p_api_version => 1.0,
880 p_product_txn_ids => p_product_txn_ids,
881 p_destination_ous => p_destination_ous,
882 p_destination_orgs => p_destination_orgs,
883 p_destination_loc_ids => p_destination_loc_ids,
887 p_quantitys => p_quantitys,
884 p_source_ous => p_source_ous,
885 p_source_orgs => p_source_orgs,
886 p_need_by_date => p_need_by_date,
888 p_source_subinvs => p_source_subinvs,
889 p_destination_subinvs => p_destination_subinvs,
890 x_requisition => x_requisition_number,
891 x_requisition_id => x_requisition_id,
892 x_msg_count => x_msg_count,
893 x_msg_data => x_msg_data,
894 x_return_status => x_return_status );
895
896 IF x_return_status <> G_RET_STS_SUCCESS
897 THEN
898 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
899 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Requisition creation has failed');
900 END IF;
901 RAISE fnd_api.g_exc_error;
902 END IF;
903
904 -- Step 2. Update the product transaction table with the requisition id.
905
906 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
907 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Requisition created.Update prod txn tbl with req id='||x_requisition_id);
908 END IF;
909 UPDATE csd_product_transactions SET req_header_id = x_requisition_id
910 WHERE product_transaction_id IN (SELECT * FROM TABLE(CAST(p_product_txn_ids AS JTF_NUMBER_TABLE)));
911
912 -- Step 3. Launch the Create Internal Orders program.
913 -- the code is factored and the create sales orders CP programs have been grouped together to avoid
914 -- code duplication.
915 -- swai: bug 14523152, FP of subhat, bug#14501090.
916 create_internal_sales_orders
917 (p_requisition_id => x_requisition_id,
918 p_so_operating_unit => p_source_ous(1),
919 p_ir_operating_unit => p_destination_ous(1),
920 p_operating_unit => l_current_ou,
921 x_msg_count => x_msg_count,
922 x_msg_data => x_msg_data,
923 x_return_status => x_return_status
924 );
925
926 IF x_return_status <> g_ret_sts_success
927 THEN
928 errbuf := x_msg_data;
929 retcode := 2;
930 RETURN;
931 END IF;
932
933 -- Step 6. Get the ISO order header id and line id and update csd_product_transactions.
934 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
935 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting ISO created and updating prod txn tbl.');
936 END IF;
937
938 FOR i IN (SELECT ooh.header_id,
939 ool.line_id,
940 ool.inventory_item_id,
941 pol.requisition_line_id,
942 wdd.delivery_detail_id
943 FROM oe_order_headers_all ooh,
944 oe_order_lines_all ool,
945 po_requisition_lines_all pol,
946 wsh_delivery_details wdd
947 WHERE ooh.orig_sys_document_ref = x_requisition_number
948 AND ooh.source_document_id = x_requisition_id
949 AND ooh.header_id = ool.header_id
950 AND pol.requisition_header_id = x_requisition_id
951 AND pol.requisition_line_id = ool.source_document_line_id
952 AND wdd.source_header_id = ooh.header_id
953 AND wdd.source_line_id = ool.line_id
954 AND wdd.source_code = 'OE'
955 )
956 LOOP
957 UPDATE csd_product_transactions SET req_line_id = i.requisition_line_id,order_header_id = i.header_id,
958 order_line_id = i.line_id,delivery_detail_id = i.delivery_detail_id
959 WHERE req_header_id = x_requisition_id
960 AND inventory_item_id = i.inventory_item_id;
961 END LOOP;
962
963 COMMIT WORK;
964
965 EXCEPTION
966 WHEN no_data_found THEN
967 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
968 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'ISO creation failed.');
969 END IF;
970 fnd_message.set_name('CSD','CSD_CREATE_ISO_FAIL');
971 fnd_msg_pub.add;
972 fnd_msg_pub.count_and_get(p_count => x_msg_count,
973 p_data => x_msg_data);
974 retcode := 2;
975 WHEN fnd_api.g_exc_error THEN
976 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
977 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'In g_exc_error');
978 END IF;
979 retcode := 2;
980 fnd_msg_pub.count_and_get(p_count => x_msg_count,
981 p_data => x_msg_data);
982 WHEN fnd_api.g_exc_unexpected_error THEN
983 null;
984 -- when others is specifically skipped here. Some thing totally unexpected has happened. Let the
985 -- plsql engine know the caller what it is along with the line number.
986 END create_internal_move_orders;
987
988 PROCEDURE pick_release_internal_order(
989 p_api_version IN NUMBER,
990 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
991 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
992 p_product_txn_id IN NUMBER,
993 p_order_header_id IN NUMBER,
994 p_orig_quantity IN NUMBER,
995 p_shipped_quantity IN NUMBER,
996 p_order_line_id IN NUMBER,
997 p_fm_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
998 p_to_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
999 p_is_sn_range IN VARCHAR2,
1000 p_is_reservable IN VARCHAR2,
1001 p_lot_num IN VARCHAR2,
1002 p_rev IN VARCHAR2,
1003 p_quantity_tbl IN JTF_NUMBER_TABLE,
1004 p_picking_rule_id IN NUMBER DEFAULT NULL,
1005 p_picking_subinv IN VARCHAR2 DEFAULT NULL,
1006 p_pick_from_org IN NUMBER DEFAULT NULL,
1007 x_msg_count OUT NOCOPY NUMBER,
1008 x_msg_data OUT NOCOPY VARCHAR2,
1012 l_prod_txn_rec csd_process_pvt.product_txn_rec;
1009 x_return_status OUT NOCOPY VARCHAR2)
1010 IS
1011 l_order_rec csd_process_pvt.om_interface_rec;
1013 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_INTERNAL_ORDERS_PVT.pick_release_internal_order';
1014 lc_api_version CONSTANT NUMBER := 1.0;
1015 l_user_id NUMBER := fnd_global.user_id;
1016 l_login_id NUMBER := fnd_global.conc_login_id;
1017 l_document_set_id NUMBER;
1018 l_order_type_id NUMBER;
1019 l_dummy VARCHAR2(1);
1020 l_picked_quantity NUMBER;
1021 l_delivery_detail_id NUMBER;
1022 l_requested_quantity NUMBER;
1023 l_delivery_detail_id_new NUMBER;
1024 BEGIN
1025
1026 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1027 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
1028 END IF;
1029
1030 -- standard check for API compatibility.
1031 IF NOT Fnd_Api.Compatible_API_Call
1032 (lc_api_version,
1033 p_api_version,
1034 lc_api_name,
1035 G_PKG_NAME)
1036 THEN
1037 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1038 END IF;
1039
1040 IF fnd_api.to_boolean(p_init_msg_list)
1041 THEN
1042 fnd_msg_pub.initialize;
1043 END IF;
1044
1045 G_DELIVERY_DETAIL_ID := NULL;
1046 -- if the shipped quantity is > 0 and < orig_quantity, then the OM order line was split.
1047 -- from a partial shipment/pick release of the previous transaction.
1048
1049 IF p_shipped_quantity > 0 AND p_shipped_quantity <> p_orig_quantity
1050 THEN
1051 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1052 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Getting the split line id');
1053 END IF;
1054 SELECT line_id
1055 INTO l_order_rec.order_line_id
1056 FROM oe_order_lines_all
1057 WHERE split_from_line_id = p_order_line_id
1058 AND header_id = p_order_header_id;
1059 ELSE
1060 l_order_rec.order_line_id := p_order_line_id;
1061 END IF;
1062
1063 IF p_picking_rule_id IS NULL OR p_picking_rule_id = 0 THEN
1064 -- get the default picking rule id.
1065 fnd_profile.get('CSD_DEF_PICK_RELEASE_RULE',l_order_rec.picking_rule_id);
1066 ELSE
1067 l_order_rec.picking_rule_id := p_picking_rule_id;
1068 END IF;
1069
1070 IF l_order_rec.picking_rule_id IS NULL
1071 THEN
1072 fnd_message.set_name('CSD', 'CSD_INV_PICKING_RULE_ID');
1073 fnd_message.set_token('PICKING_RULE_ID',l_order_rec.picking_rule_id );
1074 fnd_msg_pub.add;
1075 RAISE fnd_api.g_exc_error;
1076 END IF;
1077
1078 l_order_rec.order_header_id := p_order_header_id;
1079 l_order_rec.org_id := p_pick_from_org;
1080 l_order_rec.pick_from_subinventory := p_picking_subinv;
1081
1082 -- call the API to pick release the sales order.
1083 IF p_fm_serial_num_tbl.COUNT = 0 THEN
1084 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1085 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling csd_process_pvt.process_sales_order to pick release');
1086 END IF;
1087 csd_process_pvt.process_sales_order(
1088 p_api_version => 1.0,
1089 p_commit => fnd_api.g_false,
1090 p_init_msg_list => fnd_api.g_true,
1091 p_validation_level => fnd_api.g_valid_level_full,
1092 p_action => 'PICK-RELEASE',
1093 p_product_txn_rec => l_prod_txn_rec,
1094 p_order_rec => l_order_rec,
1095 x_return_status => x_return_status,
1096 x_msg_count => x_msg_count,
1097 x_msg_data => x_msg_data
1098 );
1099 IF x_return_status <> G_RET_STS_SUCCESS
1100 THEN
1101 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1102 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Pick release errored.'||x_msg_data);
1103 END IF;
1104 RAISE fnd_api.g_exc_error;
1105 END IF;
1106
1107 -- check if the line is back ordered.
1108 SELECT wdd.released_status,wdd.picked_quantity,wdd.delivery_detail_id,NVL(cpt.quantity_available,cpt.exp_quantity)
1109 INTO l_dummy,l_picked_quantity,l_delivery_detail_id,l_requested_quantity
1110 FROM wsh_delivery_details wdd,
1111 csd_product_transactions cpt
1112 WHERE cpt.product_transaction_id = p_product_txn_id
1113 AND cpt.delivery_detail_id = wdd.delivery_detail_id;
1114
1115 IF l_picked_quantity IS NULL
1116 THEN
1117 BEGIN
1118 SELECT delivery_detail_id,picked_quantity
1119 INTO l_delivery_detail_id_new,l_picked_quantity
1120 FROM wsh_delivery_details wdd
1121 WHERE wdd.split_from_delivery_detail_id = l_delivery_detail_id
1122 AND released_status = 'Y';
1123
1124 UPDATE csd_product_transactions SET delivery_detail_id = l_delivery_detail_id_new,quantity_picked = l_picked_quantity
1125 WHERE order_header_id = p_order_header_id
1126 AND delivery_detail_id = l_delivery_detail_id;
1127
1128 handle_partial_action
1129 (action => 'PICK',
1130 p_order_header_id => p_order_header_id,
1131 p_delivery_detail_id => l_delivery_detail_id,
1132 p_delivery_detail_id_new => l_delivery_detail_id_new
1133 );
1134
1135 UPDATE csd_product_transactions SET quantity_available = 0
1136 WHERE order_header_id = p_order_header_id
1137 AND delivery_detail_id = l_delivery_detail_id;
1138 EXCEPTION
1139 WHEN no_data_found THEN
1140 NULL;
1141 END;
1142 END IF;
1143
1144 IF l_dummy = 'B' OR l_dummy = 'R' AND l_picked_quantity IS NULL
1145 THEN
1146 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1147 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'The delivery has been backordered.');
1148 END IF;
1149 fnd_message.set_name('CSD','CSD_NOT_PICK_RELEASED');
1150 fnd_msg_pub.add;
1151 RAISE fnd_api.g_exc_error;
1152
1153 ELSIF l_dummy = 'S'
1154 THEN
1155 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1159 fnd_msg_pub.add;
1156 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'The line needs to be transacted using transact move order.');
1157 END IF;
1158 fnd_message.set_name('CSD','CSD_TRANSACT_MOVE_ORDER');
1160 RAISE fnd_api.g_exc_error;
1161
1162 ELSE
1163
1164 IF l_requested_quantity <> l_picked_quantity
1165 THEN
1166 BEGIN
1167 SELECT delivery_detail_id
1168 INTO l_delivery_detail_id_new
1169 FROM wsh_delivery_details wdd
1170 WHERE wdd.split_from_delivery_detail_id = l_delivery_detail_id
1171 AND released_status = 'B';
1172
1173 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1174 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Picking is partial.Splitting the product transaction id by making call to handle_partial_action');
1175 END IF;
1176
1177 UPDATE csd_product_transactions SET quantity_picked = l_picked_quantity
1178 WHERE order_header_id = p_order_header_id
1179 AND delivery_detail_id = l_delivery_detail_id;
1180
1181 handle_partial_action
1182 (action => 'PICK',
1183 p_order_header_id => p_order_header_id,
1184 p_delivery_detail_id => l_delivery_detail_id_new,
1185 p_delivery_detail_id_new => l_delivery_detail_id
1186 );
1187
1188 EXCEPTION
1189 WHEN no_data_found
1190 THEN
1191 NULL;
1192 END;
1193 END IF;
1194
1195 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1196 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating the picked quantity.');
1197 END IF;
1198
1199 UPDATE csd_product_transactions SET quantity_available = 0, quantity_picked = l_picked_quantity
1200 WHERE order_header_id = p_order_header_id
1201 AND delivery_detail_id = l_delivery_detail_id;
1202
1203 END IF;
1204
1205 ELSE
1206 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1207 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling pick_release to pick release');
1208 END IF;
1209 pick_release(
1210 p_api_version => 1.0,
1211 p_commit => fnd_api.g_false,
1212 p_init_msg_list => fnd_api.g_true,
1213 p_product_txn_id => p_product_txn_id,
1214 p_order_header_id => p_order_header_id,
1215 p_order_line_id => l_order_rec.order_line_id,
1216 p_fm_serial_num_tbl => p_fm_serial_num_tbl,
1217 p_to_serial_num_tbl => p_to_serial_num_tbl,
1218 p_is_sn_range => p_is_sn_range,
1219 p_is_reservable => null,
1220 p_lot_num => p_lot_num,
1221 p_rev => p_rev,
1222 p_quantity_tbl => p_quantity_tbl,
1223 p_picking_rule_id => l_order_rec.picking_rule_id,
1224 p_picking_subinv => p_picking_subinv,
1225 p_pick_from_org => l_order_rec.org_id,
1226 x_msg_count => x_msg_count,
1227 x_msg_data => x_msg_data,
1228 x_return_status => x_return_status
1229 );
1230
1231 IF x_return_status <> G_RET_STS_SUCCESS
1232 THEN
1233 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1234 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Pick release errored.'||x_msg_data);
1235 END IF;
1236 RAISE fnd_api.g_exc_error;
1237 END IF;
1238
1239 END IF;
1240
1241 -- update the product transaction record. Mark it as picked.
1242 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1243 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating ISO as released.');
1244 END IF;
1245 /*UPDATE csd_product_transactions SET release_sales_order_flag = 'Y',
1246 prod_txn_status = 'RELEASED'
1247 WHERE product_transaction_id = p_product_txn_id;*/
1248 UPDATE csd_product_transactions SET release_sales_order_flag = 'Y',
1249 prod_txn_status = 'RELEASED'
1250 WHERE order_header_id = p_order_header_id
1251 AND quantity_picked IS NOT NULL;
1252
1253 IF p_commit = fnd_api.g_true
1254 THEN
1255 COMMIT WORK;
1256 END IF;
1257
1258 EXCEPTION
1259 WHEN fnd_api.g_exc_error
1260 THEN
1261 x_return_status := G_RET_STS_ERROR;
1262 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1263 p_data => x_msg_data);
1264
1265 END pick_release_internal_order;
1266
1267
1268 PROCEDURE ship_confirm_internal_order(
1269 p_api_version IN NUMBER,
1270 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1271 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1272 p_product_txn_id IN NUMBER,
1273 p_order_header_id IN NUMBER,
1274 p_orig_quantity IN NUMBER,
1275 p_shipped_quantity IN NUMBER,
1276 p_order_line_id IN NUMBER,
1277 p_fm_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
1278 p_to_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
1279 p_is_sn_range IN VARCHAR2,
1280 p_is_reservable IN VARCHAR2,
1281 p_lot_num IN VARCHAR2,
1282 p_rev IN VARCHAR2,
1283 p_quantity_tbl IN JTF_NUMBER_TABLE,
1284 x_msg_count OUT NOCOPY NUMBER,
1285 x_msg_data OUT NOCOPY VARCHAR2,
1286 x_return_status OUT NOCOPY VARCHAR2)
1287 IS
1288
1289 lc_api_version CONSTANT NUMBER := 1.0;
1290 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_INTERNAL_ORDERS_PVT.ship_confirm_internal_order';
1291 l_serial_control_flag VARCHAR2(1);
1292 l_lot_control_flag VARCHAR2(1);
1293 l_rev_control_flag VARCHAR2(1);
1294 l_item_name VARCHAR2(240);
1295 l_order_rec csd_process_pvt.om_interface_rec;
1296 l_prod_txn_rec csd_process_pvt.product_txn_rec;
1297 l_counter NUMBER := 0;
1298 source_code VARCHAR2(15) := 'OE';
1299 changed_attributes wsh_delivery_details_pub.changedattributetabtype;
1300 l_serial_num_range_tab wsh_glbl_var_strct_grp.ddserialrangetabtype;
1304 p_delivery_name VARCHAR2(30);
1301 l_attribs_changed_flag BOOLEAN := FALSE;
1302 p_delivery_id NUMBER;
1303 p_action_code VARCHAR2(15);
1305 p_asg_trip_id NUMBER;
1306 p_asg_trip_name VARCHAR2(30);
1307 p_asg_pickup_stop_id NUMBER;
1308 p_asg_pickup_loc_id NUMBER;
1309 p_asg_pickup_loc_code VARCHAR2(30);
1310 p_asg_pickup_arr_date DATE;
1311 p_asg_pickup_dep_date DATE;
1312 p_asg_dropoff_stop_id NUMBER;
1313 p_asg_dropoff_loc_id NUMBER;
1314 p_asg_dropoff_loc_code VARCHAR2(30);
1315 p_asg_dropoff_arr_date DATE;
1316 p_asg_dropoff_dep_date DATE;
1317 p_sc_action_flag VARCHAR2(10);
1318 p_sc_intransit_flag VARCHAR2(10);
1319 p_sc_close_trip_flag VARCHAR2(10);
1320 p_sc_create_bol_flag VARCHAR2(10);
1321 p_sc_stage_del_flag VARCHAR2(10) := 'Y';
1322 p_sc_trip_ship_method VARCHAR2(30);
1323 p_sc_actual_dep_date VARCHAR2(30);
1324 p_sc_defer_interface_flag VARCHAR2(1);
1325 p_sc_report_set_id NUMBER;
1326 p_sc_report_set_name VARCHAR2(60);
1327 p_wv_override_flag VARCHAR2(10);
1328 x_trip_id VARCHAR2(30);
1329 x_trip_name VARCHAR2(30);
1330 x_msg_details VARCHAR2(3000);
1331 x_msg_summary VARCHAR2(3000);
1332 l_delivery_detail_id NUMBER;
1333 l_shipped_flag VARCHAR2(1) := 'Y';
1334 l_del_split_flag BOOLEAN := FALSE;
1335 l_quantity_shipped NUMBER;
1336
1337
1338 --bug#14632475 --bug#14644674
1339 l_delivery_org_id NUMBER;
1340 l_ship_param_info WSH_SHIPPING_PARAMS_GRP.Shipping_Params_Rec;
1341 --bug#14632475 --bug#14644674
1342
1343 BEGIN
1344
1345 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1346 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
1347 END IF;
1348
1349 -- standard check for API compatibility.
1350 IF NOT Fnd_Api.Compatible_API_Call
1351 (lc_api_version,
1352 p_api_version,
1353 lc_api_name,
1354 G_PKG_NAME)
1355 THEN
1356 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1357 END IF;
1358
1359 IF fnd_api.to_boolean(p_init_msg_list)
1360 THEN
1361 fnd_msg_pub.initialize;
1362 END IF;
1363
1364 -- if the item is reservable, the item would have been reserved as part of the pick release activity.
1365 -- if the item is not reservable, then we would need to pass in the serial number, revision and lot number
1366 -- information.
1367 IF NOT fnd_api.to_boolean(p_is_reservable)
1368 THEN
1369 -- get the item attributes.
1370 SELECT msi.serial_number_control_code,
1371 msi.revision_qty_control_code,
1372 msi.lot_control_code,
1373 msi.concatenated_segments
1374 INTO l_serial_control_flag,
1375 l_rev_control_flag,
1376 l_lot_control_flag,
1377 l_item_name
1378 FROM mtl_system_items_kfv msi, csd_product_transactions cpt
1379 WHERE cpt.product_transaction_id = p_product_txn_id
1380 AND cpt.inventory_item_id = msi.inventory_item_id
1381 AND msi.organization_id = fnd_profile.value('CSD_DEF_REP_INV_ORG');
1382
1383 -- item is serial controlled.
1384 IF l_serial_control_flag IN (2,5,6)
1385 THEN
1386 IF p_fm_serial_num_tbl.COUNT = 0
1387 THEN
1388 FND_MESSAGE.SET_NAME('CSD','CSD_API_SERIAL_NUM_REQD');
1389 FND_MSG_PUB.ADD;
1390 RAISE FND_API.G_EXC_ERROR;
1391 END IF;
1392 END IF;
1393
1394 -- item is lot controlled.
1395 IF l_lot_control_flag = 2
1396 THEN
1397 IF p_lot_num IS NULL
1398 THEN
1399 FND_MESSAGE.SET_NAME('CSD','CSD_LOT_NUMBER_REQD');
1400 FND_MESSAGE.SET_TOKEN('ITEM',l_item_name);
1401 FND_MSG_PUB.ADD;
1402 RAISE FND_API.G_EXC_ERROR;
1403 END IF;
1404 END IF;
1405
1406 -- item is revision controlled.
1407 IF l_rev_control_flag = 2
1408 THEN
1409 IF p_rev IS NULL
1410 THEN
1411 FND_MESSAGE.SET_NAME('CSD','CSD_ITEM_REVISION_REQD');
1412 FND_MESSAGE.SET_TOKEN('ITEM',l_item_name);
1413 FND_MSG_PUB.ADD;
1414 RAISE FND_API.G_EXC_ERROR;
1415 END IF;
1416 END IF;
1417 END IF; -- reservable check over.
1418
1419 -- get the delivery detail id.
1420 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1421 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Getting the delivery detail id for order header '||p_order_header_id);
1422 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Getting the delivery detail id for order line '||p_order_line_id);
1423 END IF;
1424
1425 BEGIN
1426 SELECT wdd.delivery_detail_id,wda.delivery_id
1427 INTO l_delivery_detail_id,p_delivery_id
1428 FROM wsh_delivery_assignments wda,
1429 wsh_delivery_details wdd,
1430 csd_product_transactions cpt
1431 WHERE cpt.product_transaction_id = p_product_txn_id
1432 AND wdd.delivery_detail_id = cpt.delivery_detail_id
1433 AND wdd.delivery_detail_id = wda.delivery_detail_id
1434 AND wdd.released_status = 'Y';
1435 EXCEPTION
1436 WHEN no_data_found THEN
1437 -- the order was possibly split.
1438 -- assumption. We assume that at any point, we will have one open delivery line.
1439 SELECT wdd.delivery_detail_id,wda.delivery_id
1440 INTO l_delivery_detail_id,p_delivery_id
1441 FROM wsh_delivery_assignments wda,
1442 wsh_delivery_details wdd,
1443 csd_product_transactions cpt
1444 WHERE cpt.product_transaction_id = p_product_txn_id
1445 AND wdd.split_from_delivery_detail_id = cpt.delivery_detail_id
1446 AND wdd.delivery_detail_id = wda.delivery_detail_id
1447 AND wdd.released_status = 'Y'
1448 AND wdd.source_code = 'OE';
1449 END;
1450 -- populate the SN's
1451 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1455 THEN
1452 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Populating the serial numbers');
1453 END IF;
1454 IF fnd_api.to_boolean(p_is_sn_range) AND p_fm_serial_num_tbl IS NOT NULL
1456 FOR j IN 1 ..p_fm_serial_num_tbl.COUNT
1457 LOOP
1458 l_serial_num_range_tab(j).delivery_detail_id := l_delivery_detail_id;
1459 l_serial_num_range_tab(j).from_serial_number := p_fm_serial_num_tbl(j);
1460 l_serial_num_range_tab(j).to_serial_number := p_to_serial_num_tbl(j);
1461 l_serial_num_range_tab(j).quantity := p_quantity_tbl(j);
1462 l_counter := l_counter + p_quantity_tbl(j);
1463 END LOOP;
1464
1465 ELSIF p_fm_serial_num_tbl IS NOT NULL
1466 THEN
1467 FOR j IN 1 ..p_fm_serial_num_tbl.COUNT
1468 LOOP
1469 l_serial_num_range_tab(j).delivery_detail_id := l_delivery_detail_id;
1470 l_serial_num_range_tab(j).from_serial_number := p_fm_serial_num_tbl(j);
1471 l_serial_num_range_tab(j).quantity := p_quantity_tbl(j);
1472 l_counter := l_counter+1;
1473 END LOOP;
1474 END IF;
1475
1476 -- call the update attributes API to update the shipping attributes.
1477 changed_attributes(1).delivery_detail_id := l_delivery_detail_id;
1478 l_attribs_changed_flag := TRUE;
1479 IF l_counter > 0
1480 THEN
1481 changed_attributes(1).shipped_quantity := l_counter;
1482
1483 END IF;
1484 IF p_orig_quantity > l_counter
1485 THEN
1486 changed_attributes(1).cycle_count_quantity := 0;
1487 l_del_split_flag := TRUE;
1488 END IF;
1489 IF p_rev IS NOT NULL
1490 THEN
1491 changed_attributes(1).revision := p_rev;
1492 END IF;
1493 IF p_lot_num IS NOT NULL
1494 THEN
1495 changed_attributes(1).lot_number := p_lot_num;
1496 END IF;
1497
1498 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1499 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes');
1500 END IF;
1501
1502 IF l_attribs_changed_flag
1503 THEN
1504 WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes(
1505 p_api_version_number => 1.0,
1506 p_init_msg_list => p_init_msg_list,
1507 p_commit => p_commit,
1508 x_return_status => x_return_status,
1509 x_msg_count => x_msg_count,
1510 x_msg_data => x_msg_data,
1511 p_changed_attributes => changed_attributes,
1512 p_source_code => source_code,
1513 p_serial_range_tab => l_serial_num_range_tab);
1514 END IF;
1515
1516 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1517 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1518 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Update shipping attributes failed '||x_msg_data);
1519 END IF;
1520 --dbms_output.put_line('Update shipping attributes failed '||x_msg_data);
1521 fnd_message.set_name('CSD','CSD_UPDATE_SHIPPING_FAILED');
1522 fnd_message.set_token('err_msg', x_msg_data);
1523 Fnd_Msg_Pub.ADD;
1524 RAISE fnd_api.g_exc_error;
1525 END IF;
1526 -- Return status check.
1527
1528 -- call the delivery action API.
1529
1530 p_action_code := 'CONFIRM';
1531 --p_delivery_id := l_delivery_id;
1532 p_delivery_name := TO_CHAR(p_delivery_id);
1533 p_sc_action_flag := 'S';
1534 p_sc_intransit_flag := 'Y';
1535 p_sc_close_trip_flag := 'Y';
1536 p_sc_defer_interface_flag := 'N';
1537
1538
1539 --bug#14632475 --bug#14644674
1540 select organization_id
1541 into l_delivery_org_id
1542 from wsh_delivery_details
1543 where delivery_detail_id = l_delivery_detail_id;
1544
1545 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1546 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'organization_id = '||l_delivery_org_id);
1547 END IF;
1548
1549 WSH_SHIPPING_PARAMS_GRP.Get_Shipping_Parameters(
1550 l_delivery_org_id,
1551 l_ship_param_info,
1552 x_return_status);
1553
1554 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1555 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'x_return_status(Get_Shipping_Parameters)='||x_return_status);
1556 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'DELIVERY_REPORT_SET_ID = '||l_ship_param_info.DELIVERY_REPORT_SET_ID);
1557 END IF;
1558
1559 p_sc_report_set_id := l_ship_param_info.DELIVERY_REPORT_SET_ID; -- check if it is seeded
1560
1561 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1562 --fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Get_Shipping_Parameters failed');
1563 RAISE fnd_api.g_exc_error;
1564 END IF;
1565
1566 --bug#14632475 --bug#14644674
1567
1568
1569 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1570 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling wsh_deliveries_pub.delivery_action for ship confirm action');
1571 END IF;
1572
1573
1574 wsh_deliveries_pub.delivery_action(
1575 p_api_version_number => 1.0,
1576 p_init_msg_list => p_init_msg_list,
1577 x_return_status => x_return_status,
1578 x_msg_count => x_msg_count,
1579 x_msg_data => x_msg_data,
1580 p_action_code => p_action_code,
1581 p_delivery_id => p_delivery_id,
1582 p_delivery_name => p_delivery_name,
1583 p_asg_trip_id => p_asg_trip_id,
1584 p_asg_trip_name => p_asg_trip_name,
1585 p_asg_pickup_stop_id => p_asg_pickup_stop_id,
1586 p_asg_pickup_loc_id => p_asg_pickup_loc_id,
1587 p_asg_pickup_loc_code => p_asg_pickup_loc_code,
1588 p_asg_pickup_arr_date => p_asg_pickup_arr_date,
1589 p_asg_pickup_dep_date => p_asg_pickup_dep_date,
1593 p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
1590 p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
1591 p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
1592 p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
1594 p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
1595 p_sc_action_flag => p_sc_action_flag,
1596 p_sc_intransit_flag => p_sc_intransit_flag,
1597 p_sc_close_trip_flag => p_sc_close_trip_flag,
1598 p_sc_create_bol_flag => p_sc_create_bol_flag,
1599 p_sc_stage_del_flag => p_sc_stage_del_flag,
1600 p_sc_trip_ship_method => p_sc_trip_ship_method,
1601 p_sc_actual_dep_date => p_sc_actual_dep_date,
1602 p_sc_report_set_id => p_sc_report_set_id,
1603 p_sc_report_set_name => p_sc_report_set_name,
1604 p_sc_defer_interface_flag => p_sc_defer_interface_flag,
1605 p_wv_override_flag => p_wv_override_flag,
1606 x_trip_id => x_trip_id,
1607 x_trip_name => x_trip_name);
1608
1609 IF (x_return_status <> Wsh_Util_Core.G_RET_STS_SUCCESS)
1610 THEN
1611 --debug(lc_api_name,'Ship confirm failed');
1612 BEGIN
1613 SELECT 'N'
1614 INTO l_shipped_flag
1615 FROM wsh_delivery_details wdd
1616 WHERE wdd.delivery_detail_id = l_delivery_detail_id
1617 AND wdd.released_status <> 'C';
1618 EXCEPTION
1619 WHEN no_data_found THEN
1620 l_shipped_flag :='Y';
1621 x_return_status := G_RET_STS_SUCCESS;
1622 END;
1623 IF l_shipped_flag = 'N' then
1624 FND_MESSAGE.SET_NAME('CSD','CSD_SHIP_CONFIRM_FAILED');
1625 FND_MESSAGE.SET_TOKEN('ERR_MSG',x_msg_data);
1626 FND_MSG_PUB.ADD;
1627 RAISE FND_API.G_EXC_ERROR;
1628 END IF;
1629 END IF;
1630
1631 -- update the csd_product_transactions table with the shipped quantity information.
1632 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1633 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating quantity shipped in prod txn tbl');
1634 END IF;
1635
1636 SELECT shipped_quantity
1637 INTO l_quantity_shipped
1638 FROM wsh_delivery_details
1639 WHERE delivery_detail_id = l_delivery_detail_id;
1640
1641 UPDATE csd_product_transactions SET quantity_shipped =
1642 (l_quantity_shipped + nvl(quantity_shipped,0) )
1643 WHERE order_header_id = p_order_header_id
1644 AND delivery_detail_id = (SELECT delivery_detail_id
1645 FROM csd_product_transactions
1646 WHERE product_transaction_id = p_product_txn_id
1647 ); --p_product_txn_id;
1648
1649 IF l_del_split_flag
1650 THEN
1651 null;
1652 END IF;
1653 IF fnd_api.to_boolean(p_commit)
1654 THEN
1655 COMMIT WORK;
1656 END IF;
1657
1658 EXCEPTION
1659 WHEN fnd_api.g_exc_error THEN
1660 /*wsh_util_core.get_messages('Y',
1661 x_msg_summary,
1662 x_msg_details,
1663 x_msg_count);
1664 IF x_msg_count > 1
1665 THEN
1666 x_msg_data := x_msg_summary || x_msg_details;
1667 ELSE
1668 x_msg_data := x_msg_summary;
1669 END IF;*/
1670 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1671 p_data => x_msg_data);
1672 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1673 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Error in ship confirm action '||x_msg_data);
1674 END IF;
1675
1676 x_return_status := fnd_api.g_ret_sts_error;
1677 WHEN no_data_found THEN
1678 FND_MESSAGE.SET_NAME('CSD','CSD_MISSING_DELIVERY');
1679 FND_MSG_PUB.ADD;
1680 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1681 p_data => x_msg_data);
1682 x_return_status := fnd_api.g_ret_sts_error;
1683 END ship_confirm_internal_order;
1684
1685 /********************************************************************************************/
1686 /* Function Name: IS_SERIAL_RANGE_VALID */
1687 /* Description: Validates if the generated SN range is valid or not. The moment we find the */
1688 /* range invalid, then we break. */
1689 /* Returns: 1 if the range is valid, 0 if its not */
1690 /********************************************************************************************/
1691
1692 PROCEDURE IS_SERIAL_RANGE_VALID(p_sn_range_tbl IN JTF_VARCHAR2_TABLE_100,
1693 p_inv_item_id IN NUMBER,
1694 p_current_org_id IN NUMBER,
1695 p_subinventory IN VARCHAR2 DEFAULT NULL,
1696 p_out OUT NOCOPY NUMBER)
1697 IS
1698 l_db_sns JTF_VARCHAR2_TABLE_100;
1699 --l_in_sn_associative_array SN_ASSOCIATIVE_ARRAY;
1700 l_db_sn_associative_array SN_ASSOCIATIVE_ARRAY;
1701 l_limit CONSTANT NUMBER := 1000;
1702
1703 CURSOR db_sn IS
1704 SELECT serial_number
1705 FROM mtl_serial_numbers
1706 WHERE inventory_item_id = p_inv_item_id
1707 AND current_organization_id = p_current_org_id
1708 AND current_status = 3 -- resides in stores.
1709 AND current_subinventory_code = nvl(p_subinventory,current_subinventory_code)
1710 AND serial_number IN (SELECT * FROM TABLE(CAST(p_sn_range_tbl as JTF_VARCHAR2_TABLE_100)));
1711
1712 BEGIN
1713 -- bulk collect 1000 SN's at a time from the DB.
1714
1715 OPEN db_sn;
1716 LOOP
1717
1718 FETCH db_sn
1719 BULK COLLECT INTO l_db_sns LIMIT l_limit;
1720 -- turn the collection into associative array.
1721
1722 FOR i IN 1 ..l_db_sns.COUNT
1723 LOOP
1724 l_db_sn_associative_array(l_db_sns(i)) := 'Y';
1725 END LOOP;
1726
1727 -- lookup the associative array to validate the range.
1728 FOR j IN 1 ..p_sn_range_tbl.COUNT
1732 p_out := 0;
1729 LOOP
1730 IF NOT l_db_sn_associative_array(p_sn_range_tbl(j)) = 'Y'
1731 THEN
1733 RETURN;
1734 END IF;
1735 END LOOP;
1736 EXIT WHEN l_db_sns.COUNT < l_limit;
1737 END LOOP;
1738 CLOSE db_sn;
1739 p_out := 1;
1740 EXCEPTION
1741 WHEN NO_DATA_FOUND THEN
1742 p_out := 0;
1743
1744 END IS_SERIAL_RANGE_VALID;
1745
1746 /*-----------------------------------------------------------------------------------------------------------*/
1747 /* procedure name: RECEIVE_INTERNAL_ORDER */
1748 /* description : Receives an item specified via Internal Sales Order. */
1749 /* Called from : Internal move orders API. */
1750 /* Input Parm : */
1751 /* */
1752 /* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
1753 /* fnd_api.g_ret_sts_success (success) */
1754 /* fnd_api.g_ret_sts_error (error) */
1755 /* fnd_api.g_ret_sts_unexp_error (unexpected) */
1756 /*-----------------------------------------------------------------------------------------------------------*/
1757
1758 PROCEDURE RECEIVE_INTERNAL_ORDER(p_api_version IN NUMBER,
1759 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1760 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1761 p_product_txn_id IN NUMBER,
1762 p_order_header_id IN NUMBER,
1763 p_order_line_id IN NUMBER,
1764 p_receiving_subinv IN VARCHAR2 DEFAULT NULL,
1765 x_return_status OUT NOCOPY VARCHAR2,
1766 x_msg_count OUT NOCOPY NUMBER,
1767 x_msg_data OUT NOCOPY VARCHAR2
1768 )
1769 IS
1770 lc_api_version CONSTANT NUMBER := 1.0;
1771 l_receive_tbl csd_receive_util.rcv_tbl_type;
1772 x_rcv_error_msg_tbl csd_receive_util.rcv_error_msg_tbl;
1773 l_dummy NUMBER;
1774 l_validation EXCEPTION;
1775 lc_api_name VARCHAR2(80) := 'CSD_INTERNAL_ORDERS_PVT.RECEIVE_INTERNAL_ORDER';
1776 x_request_group_id NUMBER;
1777 l_received_quantity NUMBER;
1778 l_delivery_detail_id NUMBER;
1779 BEGIN
1780 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1781 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
1782 END IF;
1783
1784 -- standard check for API compatibility.
1785 IF NOT Fnd_Api.Compatible_API_Call
1786 (lc_api_version,
1787 p_api_version,
1788 lc_api_name,
1789 G_PKG_NAME)
1790 THEN
1791 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1792 END IF;
1793
1794 IF fnd_api.to_boolean(p_init_msg_list)
1795 THEN
1796 fnd_msg_pub.initialize;
1797 END IF;
1798
1799 x_return_status := G_RET_STS_SUCCESS;
1800 -- find if there is atleast quantity of 1 shipped for this internal order.
1801 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1802 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Checking for quantity to be received.');
1803 END IF;
1804
1805 SELECT nvl(quantity_shipped,0),delivery_detail_id
1806 INTO l_dummy,l_delivery_detail_id
1807 FROM csd_product_transactions
1808 WHERE product_transaction_id = p_product_txn_id
1809 AND order_header_id = p_order_header_id
1810 AND order_line_id = p_order_line_id;
1811
1812 IF l_dummy = 0
1813 THEN
1814 FND_MESSAGE.SET_NAME('CSD','CSD_INT_ORD_NOT_SHIPPED');
1815 FND_MSG_PUB.ADD;
1816 RAISE l_validation;
1817 END IF;
1818
1819 -- validate if the internal requisition is already received based on shipping networks setup.
1820 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1821 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Validating if the IR is already received via shipping networks setup.');
1822 END IF;
1823 BEGIN
1824 SELECT SUM(rt.quantity)
1825 INTO l_received_quantity
1826 FROM csd_product_transactions cpt,
1827 po_requisition_lines_all prl,
1828 rcv_shipment_headers rsh,
1829 rcv_shipment_lines rsl,
1830 rcv_transactions rt
1831 WHERE cpt.product_transaction_id = p_product_txn_id
1832 AND cpt.req_header_id = prl.requisition_header_id
1833 AND rsl.requisition_line_id = prl.requisition_line_id
1834 AND rsl.shipment_header_id = rsh.shipment_header_id
1835 AND rt.transaction_type = 'RECEIVE'
1836 AND rt.shipment_header_id = rsh.shipment_header_id
1837 AND rsl.mmt_transaction_id IN (SELECT mmt.transaction_id
1838 FROM csd_product_transactions cpt,mtl_material_transactions mmt
1839 WHERE cpt.delivery_detail_id = mmt.picking_line_id
1840 AND cpt.product_transaction_id = p_product_txn_id
1841 UNION ALL
1842 SELECT mmt.transaction_id
1843 FROM wsh_delivery_details wdd, csd_product_transactions cpt,mtl_material_transactions mmt
1844 WHERE cpt.delivery_detail_id = wdd.split_from_delivery_detail_id
1845 AND cpt.order_header_id = wdd.source_header_id
1846 AND wdd.source_code = 'OE'
1847 AND cpt.product_transaction_id = p_product_txn_id
1851 FROM csd_product_transactions cpt1
1848 AND wdd.delivery_detail_id = mmt.picking_line_id
1849 AND NOT EXISTS(
1850 SELECT 'exists'
1852 WHERE wdd.delivery_detail_id = cpt1.delivery_detail_id
1853 AND cpt.order_header_id = cpt1.order_header_id
1854 )
1855 );
1856
1857 EXCEPTION
1858 WHEN no_data_found THEN
1859 l_received_quantity := 0;
1860 END;
1861
1862 IF l_received_quantity > 0 then
1863 -- update the received quantity on product transactions.
1864 IF (l_dummy - l_received_quantity) >= 0
1865 THEN
1866 UPDATE csd_product_transactions SET quantity_received = l_received_quantity
1867 WHERE order_header_id = p_order_header_id
1868 AND delivery_detail_id = l_delivery_detail_id; --p_product_txn_id;
1869
1870 IF fnd_api.to_boolean(p_commit)
1871 THEN
1872 COMMIT WORK;
1873 END IF;
1874 END IF;
1875
1876 IF l_received_quantity = l_dummy
1877 THEN
1878 FND_MESSAGE.SET_NAME('CSD','CSD_INT_ORD_RECEIVED');
1879 FND_MSG_PUB.ADD;
1880 x_msg_data := fnd_message.get;
1881 RETURN;
1882 END IF;
1883 END IF;
1884
1885 -- populate the rcv int tables.
1886 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1887 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling populate_rcv_int_tables to populate the receiving interfaces.');
1888 END IF;
1889 populate_rcv_int_tables(p_product_txn_id => p_product_txn_id,
1890 p_receiving_subinv => p_receiving_subinv,
1891 x_request_group_id => x_request_group_id);
1892
1893 -- call the receive API to receive the lines.
1894 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1895 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling csd_receive_pvt.rcv_req_online to auto receive internal requisitions');
1896 END IF;
1897 csd_receive_pvt.rcv_req_online
1898 (p_api_version => 1.0,
1899 p_init_msg_list => fnd_api.g_false,
1900 p_commit => fnd_api.g_false,
1901 p_validation_level => fnd_api.g_valid_level_full,
1902 x_return_status => x_return_status,
1903 x_msg_count => x_msg_count,
1904 x_msg_data => x_msg_data,
1905 p_request_group_id => x_request_group_id
1906 );
1907 IF x_return_status <> G_RET_STS_SUCCESS
1908 THEN
1909 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1910 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Error in online receipt of IR.');
1911 END IF;
1912 RAISE fnd_api.g_exc_error;
1913 END IF;
1914
1915 -- update the quantity received field in csd_product_transactions with receipt quantity.
1916 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1917 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating csd_product_transactions.received_quantity');
1918 END IF;
1919
1920 -- find out the transacted quantity from the rcv_transactions.
1921 SELECT SUM(quantity)
1922 INTO l_received_quantity
1923 FROM rcv_transactions
1924 WHERE group_id = x_request_group_id
1925 AND transaction_type = 'RECEIVE';
1926
1927 UPDATE csd_product_transactions SET quantity_received = (
1928 l_received_quantity + nvl(quantity_received,0))
1929 WHERE order_header_id = p_order_header_id
1930 AND delivery_detail_id = l_delivery_detail_id; --product_transaction_id = p_product_txn_id;
1931
1932 -- update the product transaction status.
1933 UPDATE csd_product_transactions cpt
1934 SET prod_txn_status =
1935 (SELECT 'RECEIVED'
1936 FROM csd_product_transactions
1937 WHERE product_transaction_id = cpt.product_transaction_id
1938 AND NVL(quantity_shipped,0) = exp_quantity
1939 UNION ALL
1940 SELECT 'RECEIVED'
1941 FROM csd_product_transactions
1942 WHERE product_transaction_id = cpt.product_transaction_id
1943 AND NVL(quantity_shipped,0) < exp_quantity
1944 AND quantity_shipped > 0
1945 )
1946 WHERE order_header_id = p_order_header_id
1947 AND delivery_detail_id = l_delivery_detail_id; --product_transaction_id = p_product_txn_id;
1948
1949 IF fnd_api.to_boolean(p_commit)
1950 THEN
1951 COMMIT WORK;
1952 END IF;
1953 EXCEPTION
1954 WHEN fnd_api.g_exc_error THEN
1955 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1956 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Execution error:'||x_msg_data);
1957 END IF;
1958 -- message has been already set and retrieved.
1959 x_return_status := G_RET_STS_ERROR;
1960
1961 WHEN l_validation THEN
1962 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1963 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Validation error:'||x_msg_data);
1964 END IF;
1965 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1966 p_data => x_msg_data);
1967 x_return_status := G_RET_STS_ERROR;
1968
1969 END RECEIVE_INTERNAL_ORDER;
1970
1971 PROCEDURE PICK_RELEASE(p_api_version IN NUMBER,
1972 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1973 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1974 p_product_txn_id IN NUMBER,
1975 p_order_header_id IN NUMBER,
1976 p_order_line_id IN NUMBER,
1977 p_fm_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
1978 p_to_serial_num_tbl IN JTF_VARCHAR2_TABLE_100,
1979 p_is_sn_range IN VARCHAR2,
1980 p_is_reservable IN VARCHAR2,
1984 p_picking_rule_id IN NUMBER DEFAULT NULL,
1981 p_lot_num IN VARCHAR2,
1982 p_rev IN VARCHAR2,
1983 p_quantity_tbl IN JTF_NUMBER_TABLE,
1985 p_picking_subinv IN VARCHAR2 DEFAULT NULL,
1986 p_pick_from_org IN NUMBER DEFAULT NULL,
1987 x_msg_count OUT NOCOPY NUMBER,
1988 x_msg_data OUT NOCOPY VARCHAR2,
1989 x_return_status OUT NOCOPY VARCHAR2
1990 )
1991 IS
1992 lc_api_version CONSTANT NUMBER := 1.0;
1993 lc_api_name VARCHAR2(80) := 'CSD_INTERNAL_ORDERS_PVT.PICK_RELEASE';
1994 l_batch_rec WSH_PICKING_BATCHES_PUB.batch_info_rec;
1995 l_order_type_id NUMBER;
1996 l_customer_id NUMBER;
1997 l_document_set_id NUMBER;
1998 lc_batch_prefix CONSTANT VARCHAR2(5) := 'DR';
1999 l_batch_name VARCHAR2(30);
2000 l_move_order_hdr_id NUMBER;
2001 l_reservable_flag VARCHAR2(2);
2002 l_reservation_id NUMBER;
2003 l_item_id NUMBER;
2004 l_org_id NUMBER;
2005 l_mmtt_temp_id NUMBER;
2006 l_fm_serial_number JTF_VARCHAR2_TABLE_100;
2007 l_to_serial_number JTF_VARCHAR2_TABLE_100;
2008 l_move_order_line_id NUMBER;
2009 l_delivery_status VARCHAR2(2);
2010 l_delivery_detail_id NUMBER;
2011 l_delivery_detail_id_new NUMBER;
2012 l_picked_qty NUMBER;
2013 l_order_line_id NUMBER := p_order_line_id;
2014 l_requested_qty NUMBER;
2015 l_picking_qty NUMBER := 0;
2016 l_transaction_uom VARCHAR2(10);
2017 i NUMBER;
2018 j NUMBER;
2019 l_dummy VARCHAR2(1) := 'N';
2020 x_batch_id NUMBER;
2021 x_request_id NUMBER;
2022 x_success NUMBER;
2023 x_number_of_rows NUMBER;
2024 x_detailed_qty NUMBER;
2025 x_detailed_qty2 NUMBER;
2026 x_revision VARCHAR2(3);
2027 x_locator_id NUMBER;
2028 x_transfer_to_location NUMBER;
2029 x_lot_number VARCHAR2(30);
2030 x_expiration_date DATE;
2031 x_record_id NUMBER;
2032
2033 l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
2034 l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
2035 x_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
2036 x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
2037
2038 CREATE_BATCH EXCEPTION;
2039 RELEASE_BATCH EXCEPTION;
2040 ALLOCATION_ERROR EXCEPTION;
2041
2042 BEGIN
2043 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2044 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
2045 END IF;
2046
2047 -- standard check for API compatibility.
2048 IF NOT Fnd_Api.Compatible_API_Call
2049 (lc_api_version,
2050 p_api_version,
2051 lc_api_name,
2052 G_PKG_NAME)
2053 THEN
2054 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2055 END IF;
2056
2057 IF fnd_api.to_boolean(p_init_msg_list)
2058 THEN
2059 fnd_msg_pub.initialize;
2060 END IF;
2061
2062 x_return_status := G_RET_STS_SUCCESS;
2063
2064 SAVEPOINT RELEASE_SALESORDER;
2065 -- get the delivery line status.
2066 SELECT wdd.released_status,wdd.requested_quantity,wdd.requested_quantity_uom,wdd.delivery_detail_id
2067 INTO l_delivery_status,l_requested_qty,l_transaction_uom,l_delivery_detail_id
2068 FROM wsh_delivery_details wdd,csd_product_transactions cpt
2069 WHERE wdd.delivery_detail_id = cpt.delivery_detail_id
2070 AND product_transaction_id = p_product_txn_id
2071 AND wdd.source_code = 'OE';
2072
2073 -- requested quantity is the quantity available for transaction.
2074 UPDATE csd_product_transactions SET quantity_available = l_requested_qty
2075 WHERE delivery_detail_id = l_delivery_detail_id;
2076
2077 IF NOT (l_delivery_status = 'S' OR l_delivery_status = 'Y') THEN
2078 -- fetch the customer details.
2079 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2080 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Fetching customer, order type and source document');
2081 END IF;
2082 SELECT sold_to_org_id,
2083 order_type_id,
2084 source_document_type_id
2085 INTO l_customer_id,
2086 l_order_type_id,
2087 l_document_set_id
2088 FROM oe_order_headers_all
2089 WHERE header_id = p_order_header_id;
2090
2091 -- get the defaults from picking rule.
2092 -- Auto pick confirm flag will be always set to N in this API
2093 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2094 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Fetching picking defaults');
2095 END IF;
2096
2097 SELECT NVL(document_set_id, l_document_set_id),
2098 'I',
2099 NVL(existing_rsvs_only_flag, 'N'),
2100 shipment_priority_code,
2101 p_order_header_id,
2102 l_delivery_detail_id,--NULL,
2103 l_order_type_id,
2104 NULL,
2105 l_customer_id,
2106 NULL,
2107 ship_method_code,
2108 NVL(p_picking_subinv, pick_from_subinventory),
2109 pick_from_locator_id,
2110 default_stage_subinventory,
2111 default_stage_locator_id,
2112 autodetail_pr_flag,
2113 'N',
2114 ship_set_number,
2115 NULL,
2116 NULL,
2117 NULL,
2118 NULL,
2119 NULL,
2120 pick_grouping_rule_id,
2121 pick_sequence_rule_id,
2122 NVL(p_pick_from_org, organization_id),
2123 project_id,
2124 task_id,
2125 include_planned_lines,
2126 autocreate_delivery_flag,
2127 allocation_method,
2128 l_delivery_detail_id
2129 INTO l_batch_rec.document_set_id,
2130 l_batch_rec.backorders_only_flag,
2131 l_batch_rec.existing_rsvs_only_flag,
2132 l_batch_rec.shipment_priority_code,
2133 l_batch_rec.order_header_id,
2134 l_batch_rec.delivery_detail_id,
2135 l_batch_rec.order_type_id,
2136 l_batch_rec.ship_from_location_id,
2137 l_batch_rec.customer_id,
2141 l_batch_rec.pick_from_locator_id,
2138 l_batch_rec.ship_to_location_id,
2139 l_batch_rec.ship_method_code,
2140 l_batch_rec.pick_from_subinventory,
2142 l_batch_rec.default_stage_subinventory,
2143 l_batch_rec.default_stage_locator_id,
2144 l_batch_rec.autodetail_pr_flag,
2145 l_batch_rec.auto_pick_confirm_flag,
2146 l_batch_rec.ship_set_number,
2147 l_batch_rec.inventory_item_id,
2148 l_batch_rec.from_requested_date,
2149 l_batch_rec.to_requested_date,
2150 l_batch_rec.from_scheduled_ship_date,
2151 l_batch_rec.to_scheduled_ship_date,
2152 l_batch_rec.pick_grouping_rule_id,
2153 l_batch_rec.pick_sequence_rule_id,
2154 l_batch_rec.organization_id,
2155 l_batch_rec.project_id,
2156 l_batch_rec.task_id,
2157 l_batch_rec.include_planned_lines,
2158 l_batch_rec.autocreate_delivery_flag,
2159 l_batch_rec.allocation_method,
2160 l_batch_rec.delivery_detail_id
2161 FROM WSH_PICKING_RULES
2162 WHERE PICKING_RULE_ID = p_picking_rule_id;
2163
2164 -- create the release batch.
2165 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2166 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Creating the release batch');
2167 END IF;
2168
2169 WSH_PICKING_BATCHES_GRP.Create_Batch(
2170 p_api_version_number => 1.0,
2171 p_init_msg_list => fnd_api.g_false,
2172 p_commit => fnd_api.g_false,
2173 x_return_status => x_return_status,
2174 x_msg_count => x_msg_count,
2175 x_msg_data => x_msg_data,
2176 p_rule_id => p_picking_rule_id,
2177 p_rule_name => NULL,
2178 p_batch_rec => l_batch_rec,
2179 p_batch_prefix => lc_batch_prefix,
2180 x_batch_id => x_batch_id
2181 );
2182 IF NOT x_return_status = G_RET_STS_SUCCESS THEN
2183 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2184 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'WSH_PICKING_BATCHES_GRP.Create_Batch has failed:'||x_msg_data);
2185 END IF;
2186 RAISE CREATE_BATCH;
2187 END IF;
2188
2189 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2190 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Created the release batch, calling release batch now');
2191 END IF;
2192
2193 wsh_picking_batches_pub.Release_Batch(
2194 p_api_version => 1.0,
2195 p_init_msg_list => fnd_api.g_false,
2196 p_commit => fnd_api.g_false,
2197 x_return_status => x_return_status,
2198 x_msg_count => x_msg_count,
2199 x_msg_data => x_msg_data,
2200 p_batch_id => x_batch_id,
2201 p_batch_name => l_batch_name,
2202 p_log_level => NULL,
2203 p_release_mode => 'ONLINE',
2204 x_request_id => x_request_id
2205 );
2206
2207 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2208 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'after wsh_picking_batches_pub.Release_Batch, return status:'||x_return_status);
2209 END IF;
2210 IF x_return_status = wsh_util_core.g_ret_sts_warning THEN
2211 -- treat warning as success.
2212 x_return_status := G_RET_STS_SUCCESS;
2213 END IF;
2214
2215 IF x_return_status = G_RET_STS_ERROR THEN
2216 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2217 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Release batch is failed!');
2218 END IF;
2219 RAISE RELEASE_BATCH;
2220 END IF;
2221 END IF;
2222
2223 -- the batch is released. Check if the allocation is automatically created. If not, need to create
2224 -- allocation.
2225 IF NOT l_delivery_status = 'Y' THEN
2226 BEGIN
2227 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2228 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Getting allocations if any for order header id:'||p_order_header_id);
2229 END IF;
2230
2231 SELECT mtrh.header_id,mmtt.transaction_temp_id,
2232 mtrl.line_id,wdd.delivery_detail_id
2233 INTO l_move_order_hdr_id,l_mmtt_temp_id,
2234 l_move_order_line_id,G_DELIVERY_DETAIL_ID
2235 FROM wsh_delivery_details wdd,
2236 mtl_txn_request_headers mtrh,
2237 mtl_txn_request_lines mtrl,
2238 mtl_material_transactions_temp mmtt,
2239 csd_product_transactions cpt
2240 WHERE wdd.delivery_detail_id = cpt.delivery_detail_id
2241 AND cpt.product_transaction_id = p_product_txn_id
2242 AND wdd.move_order_line_id = mtrl.line_id
2243 AND mtrl.header_id = mtrh.header_id
2244 AND mtrl.line_id = mmtt.move_order_line_id(+);
2245
2246 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2247 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Fetched move order header id '||l_move_order_hdr_id);
2248 END IF;
2249
2250 EXCEPTION
2251 WHEN no_data_found THEN
2252 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2253 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'No allocations were found!');
2254 END IF;
2255 l_move_order_hdr_id := 0;
2256 END;
2257
2258 IF NVL(l_mmtt_temp_id,0) = 0 THEN
2259 -- we would need to create the allocation here. We make use of inv_replenish_detail_pub.line_details
2260 -- to create the allocations.
2261 SELECT wdd.move_order_line_id
2262 INTO l_move_order_line_id
2263 FROM wsh_delivery_details wdd,
2264 csd_product_transactions cpt
2265 WHERE wdd.delivery_detail_id = cpt.delivery_detail_id
2266 AND cpt.product_transaction_id = p_product_txn_id;
2267
2268 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2269 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Creating the allocations for move line id'||l_move_order_line_id);
2270 END IF;
2271 inv_replenish_detail_pub.line_details_pub
2275 x_detailed_qty2 => x_detailed_qty2,
2272 (p_line_id => l_move_order_line_id,
2273 x_number_of_rows => x_number_of_rows,
2274 x_detailed_qty => x_detailed_qty,
2276 x_return_status => x_return_status,
2277 x_msg_count => x_msg_count,
2278 x_msg_data => x_msg_data,
2279 x_revision => x_revision,
2280 x_locator_id => x_locator_id,
2281 x_transfer_to_location => x_transfer_to_location,
2282 x_lot_number => x_lot_number,
2283 x_expiration_date => x_expiration_date,
2284 x_transaction_temp_id => l_mmtt_temp_id,
2285 p_transaction_header_id => NULL,
2286 p_transaction_mode => NULL,
2287 p_move_order_type => inv_globals.g_move_order_pick_wave,
2288 p_serial_flag => 'T',
2289 p_plan_tasks => FALSE,
2290 p_auto_pick_confirm => FALSE,
2291 p_commit => FALSE
2292 );
2293 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2294 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Returned from inv_replenish_detail_pub.line_details. Return status'||x_return_status);
2295 END IF;
2296
2297 IF x_return_status <> G_RET_STS_SUCCESS THEN
2298 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2299 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Error while trying to create allocations..'||x_msg_data);
2300 END IF;
2301 RAISE ALLOCATION_ERROR;
2302 END IF;
2303
2304 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2305 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Successfully created the allocation: Transaction temp id is='||l_mmtt_temp_id);
2306 END IF;
2307 END IF;
2308
2309 IF l_mmtt_temp_id <> 0 THEN
2310
2311 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2312 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Checking if the item is reservable..');
2313 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'mover order line id:'||l_move_order_line_id);
2314 END IF;
2315
2316 SELECT msi.reservable_type,msi.inventory_item_id,
2317 msi.organization_id
2318 INTO l_reservable_flag,l_item_id,
2319 l_org_id
2320 FROM mtl_system_items_b msi,
2321 mtl_txn_request_lines mtrl
2322 WHERE mtrl.inventory_item_id = msi.inventory_item_id
2323 AND mtrl.organization_id = msi.organization_id
2324 AND mtrl.line_id = l_move_order_line_id;
2325
2326 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2327 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Reservation flag:'||l_reservable_flag);
2328 END IF;
2329
2330 IF l_reservable_flag = 1 THEN
2331 BEGIN
2332 SELECT reservation_id
2333 INTO l_reservation_id
2334 FROM mtl_reservations mr
2335 WHERE mr.demand_source_line_id = l_order_line_id
2336 AND NVL(staged_flag,'N') = 'N';
2337 EXCEPTION
2338 WHEN no_data_found THEN
2339 l_reservation_id := NULL;
2340 END;
2341 END IF;
2342
2343 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2344 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Existing reservation id:'||l_reservation_id);
2345 END IF;
2346
2347 SELECT fm_serial_number, to_serial_number
2348 BULK COLLECT INTO
2349 l_fm_serial_number, l_to_serial_number
2350 FROM mtl_serial_numbers_temp
2351 WHERE transaction_temp_id = l_mmtt_temp_id;
2352
2353 -- unmark the serials.
2354 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2355 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Unmarking the existing range');
2356 END IF;
2357
2358 FOR i in 1 ..l_fm_serial_number.COUNT
2359 LOOP
2360 SERIAL_CHECK.inv_unmark_rsv_serial
2361 (from_serial_number => l_fm_serial_number(i),
2362 to_serial_number => l_to_serial_number(i),
2363 serial_code => 2,
2364 hdr_id => l_mmtt_temp_id,
2365 temp_id => l_mmtt_temp_id,
2366 lot_temp_id => l_mmtt_temp_id,
2367 p_inventory_item_id => l_item_id,
2368 p_update_reservation => 'T'
2369 );
2370 END LOOP;
2371
2372 -- delete all the existing serials from MSNT.
2373 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2374 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Deleting from MSNT for transaction_temp_id='||l_mmtt_temp_id);
2375 END IF;
2376
2377 DELETE FROM mtl_serial_numbers_temp WHERE transaction_temp_id = l_mmtt_temp_id;
2378
2379 -- mark the newly passed serials. And insert them into MSNT.
2380 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2381 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Marking the newly entered serial ranges');
2382 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Range count:'||p_fm_serial_num_tbl.COUNT);
2383
2384 END IF;
2385
2386 FOR j in 1 ..p_fm_serial_num_tbl.COUNT
2387 LOOP
2388
2389 SERIAL_CHECK.inv_mark_rsv_serial
2390 (from_serial_number => p_fm_serial_num_tbl(j),
2391 to_serial_number => p_to_serial_num_tbl(j),
2392 item_id => l_item_id,
2393 org_id => l_org_id,
2394 hdr_id => l_mmtt_temp_id,
2395 temp_id => l_mmtt_temp_id,
2396 lot_temp_id => l_mmtt_temp_id,
2397 p_reservation_id => l_reservation_id,
2398 p_update_reservation => fnd_api.g_true,
2399 success => x_success
2400 );
2401
2402 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2403 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Inserting into msnt='||l_mmtt_temp_id);
2404 END IF;
2405
2406 INSERT INTO mtl_serial_numbers_temp
2407 (transaction_temp_id,
2408 last_update_date,
2409 last_updated_by,
2410 creation_date,
2411 created_by,
2412 last_update_login,
2416 serial_prefix
2413 fm_serial_number,
2414 to_serial_number,
2415 group_header_id,
2417 )
2418 VALUES (l_mmtt_temp_id,
2419 sysdate,
2420 fnd_global.user_id,
2421 sysdate,
2422 fnd_global.user_id,
2423 fnd_global.login_id,
2424 p_fm_serial_num_tbl(j),
2425 p_to_serial_num_tbl(j),
2426 l_mmtt_temp_id,
2427 p_quantity_tbl(j)
2428 );
2429 l_picking_qty := l_picking_qty + p_quantity_tbl(j);
2430 END LOOP;
2431 END IF;
2432 -- if we are doing partial picking, then need to update the existing allocation details.
2433 -- the serial number allocation would have been already addressed above.
2434 IF l_picking_qty < l_requested_qty
2435 THEN
2436 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2437 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating the existing allocations for temp id ='||l_mmtt_temp_id);
2438 END IF;
2439 inv_missing_qty_actions_engine.update_allocation_qty
2440 (x_return_status => x_return_status,
2441 x_msg_data => x_msg_data,
2442 x_msg_count => x_msg_count,
2443 p_transaction_temp_id => l_mmtt_temp_id,
2444 p_confirmed_quantity => l_picking_qty,
2445 p_transaction_uom => l_transaction_uom
2446 );
2447 IF x_return_status <> G_RET_STS_SUCCESS
2448 THEN
2449 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2450 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Error in inv_missing_qty_actions_engine.update_allocation_qty. Msg is '||x_msg_data);
2451 END IF;
2452 RAISE ALLOCATION_ERROR;
2453 END IF;
2454
2455 -- we need to process the allocations such that a back order gets created for
2456 -- the remaining quantity.
2457 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2458 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling process action to back order remaining quantity ');
2459 END IF;
2460 inv_missing_qty_actions_engine.process_action
2461 (x_return_status => x_return_status,
2462 x_msg_data => x_msg_data,
2463 x_msg_count => x_msg_count,
2464 x_new_record_id => x_record_id,
2465 p_action => 1, -- back order
2466 p_transaction_temp_id => l_mmtt_temp_id,
2467 p_remaining_quantity => (l_requested_qty - l_picking_qty)
2468 );
2469 IF x_return_status <> G_RET_STS_SUCCESS
2470 THEN
2471 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2472 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Error in process_action API. Msg = '||x_msg_data);
2473 END IF;
2474 RAISE ALLOCATION_ERROR;
2475 END IF;
2476 END IF;
2477
2478 -- need to pick release the line.
2479 -- we just need to populate l_trolin_tbl table. No need to populate mmtt table type.
2480 -- even in the l_trolin_tbl we need to pass the correct move order line id.
2481 -- INV team says that the API queries all the remaining details itself.
2482 l_trolin_tbl(1).line_id := l_move_order_line_id;
2483 -- call the pick confirm api
2484 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2485 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm');
2486 END IF;
2487
2488 INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm
2489 (p_api_version_number => 1.0,
2490 p_init_msg_list => p_init_msg_list,
2491 p_commit => p_commit,
2492 x_return_status => x_return_status,
2493 x_msg_count => x_msg_count,
2494 x_msg_data => x_msg_data,
2495 p_move_order_type => inv_globals.g_move_order_pick_wave,
2496 p_transaction_mode => 1,
2497 p_trolin_tbl => l_trolin_tbl,
2498 p_mold_tbl => l_mold_tbl,
2499 x_mmtt_tbl => l_mold_tbl,
2500 x_trolin_tbl => l_trolin_tbl
2501 );
2502 IF x_return_status <> G_RET_STS_SUCCESS THEN
2503 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2504 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'ERROR in INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm');
2505 END IF;
2506 RAISE RELEASE_BATCH;
2507 END IF;
2508 END IF;
2509
2510 -- Check if the delivery is successfully pick confirmed.
2511 IF l_move_order_line_id IS NOT NULL
2512 THEN
2513 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2514 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Checking if the delivery is successfully pick released. DDID='||l_delivery_detail_id);
2515 END IF;
2516 SELECT released_status,delivery_detail_id,picked_quantity
2517 INTO l_delivery_status,l_delivery_detail_id_new,l_picked_qty
2518 FROM wsh_delivery_details
2519 WHERE move_order_line_id = l_move_order_line_id;
2520
2521 IF l_delivery_status <> 'Y'
2522 THEN
2523 RAISE RELEASE_BATCH;
2524 END IF;
2525 END IF;
2526
2527 IF l_picked_qty IS NOT NULL THEN
2528 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2529 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating the picked quantity: Quantity picked = '||l_picked_qty);
2530 END IF;
2531 UPDATE csd_product_transactions SET quantity_picked = l_picked_qty,
2532 delivery_detail_id = l_delivery_detail_id_new
2533 WHERE order_header_id = p_order_header_id
2534 AND delivery_detail_id = l_delivery_detail_id;
2535 END IF;
2536
2537 -- check if its a partial release.
2538 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2539 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Checking if the pick release was partial.');
2540 END IF;
2541 BEGIN
2542 SELECT 'Y'
2543 INTO l_dummy
2544 FROM csd_product_transactions
2545 WHERE order_header_id = p_order_header_id
2549 EXCEPTION
2546 AND product_transaction_id = p_product_txn_id
2547 AND quantity_picked < quantity_available
2548 AND quantity_picked IS NOT NULL;
2550 WHEN no_data_found
2551 THEN
2552 l_dummy := 'N';
2553 END;
2554
2555 IF l_dummy = 'Y'
2556 THEN
2557 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2558 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Picking is partial.Splitting the product transaction id by making call to handle_partial_action');
2559 END IF;
2560 handle_partial_action
2561 (action => 'PICK',
2562 p_order_header_id => p_order_header_id,
2563 p_delivery_detail_id => l_delivery_detail_id,
2564 p_delivery_detail_id_new => l_delivery_detail_id_new
2565 );
2566 ELSE
2567 -- not a partial pick. The quantity available to pick needs to be updated to 0.
2568 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2569 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Successfully picked the available quantity. Updating the quantity available to zero for this line');
2570 END IF;
2571
2572 UPDATE csd_product_transactions SET quantity_available = 0
2573 WHERE delivery_detail_id = l_delivery_detail_id_new;
2574 END IF;
2575
2576 IF fnd_api.to_boolean(p_commit)
2577 THEN
2578 COMMIT WORK;
2579 END IF;
2580
2581 EXCEPTION
2582 WHEN CREATE_BATCH THEN
2583 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2584 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'In create_batch exception');
2585 END IF;
2586 x_return_status := G_RET_STS_ERROR;
2587 ROLLBACK TO RELEASE_SALESORDER;
2588 WHEN RELEASE_BATCH THEN
2589 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2590 fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'In release_batch exception');
2591 END IF;
2592 fnd_message.set_name('CSD', 'CSD_PICK_RELEASE_FAIL');
2593 fnd_message.set_token('BATCH_NAME', l_batch_name);
2594 fnd_msg_pub.add;
2595 x_return_status := G_RET_STS_ERROR;
2596 ROLLBACK TO RELEASE_SALESORDER;
2597 WHEN NO_DATA_FOUND THEN
2598 fnd_message.set_name('CSD', 'CSD_INV_ORDER_HEADER_ID');
2599 fnd_message.set_token('ORDER_HEADER_ID',p_order_header_id);
2600 fnd_msg_pub.add;
2601 x_return_status := G_RET_STS_ERROR;
2602 ROLLBACK TO RELEASE_SALESORDER;
2603 WHEN ALLOCATION_ERROR THEN
2604 x_return_status := G_RET_STS_ERROR;
2605 ROLLBACK TO RELEASE_SALESORDER;
2606
2607 END PICK_RELEASE;
2608
2609 /*-----------------------------------------------------------------------------------------------------------*/
2610 /* Function name: GET_ALL_INTERNAL_ORDERS */
2611 /* Called from: RepairOrdersVO of search and update repair orders */
2612 /* Input param: p_repair_line_id repair line id. */
2613 /* Return value: returns all the internal orders for a particular repair order. Returns null if no */
2614 /* internal orders are found */
2615 /*-----------------------------------------------------------------------------------------------------------*/
2616
2617 FUNCTION GET_ALL_INTERNAL_ORDERS(p_repair_line_id IN NUMBER) RETURN VARCHAR2
2618 IS
2619 l_internal_orders_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
2620 l_ret_string VARCHAR2(1000);
2621
2622 BEGIN
2623
2624 SELECT DISTINCT order_number
2625 BULK COLLECT INTO l_internal_orders_tbl
2626 FROM csd_product_transactions cpt,
2627 oe_order_headers_all oeh
2628 WHERE cpt.repair_line_id = p_repair_line_id
2629 AND cpt.order_header_id = oeh.header_id
2630 AND cpt.action_type IS NULL;
2631
2632 IF l_internal_orders_tbl.COUNT = 0
2633 THEN
2634 RETURN l_ret_string;
2635 END IF;
2636
2637 l_ret_string := l_internal_orders_tbl(1);
2638
2639 FOR i in 2 ..l_internal_orders_tbl.COUNT
2640 LOOP
2641 l_ret_string := l_ret_string||', '||l_internal_orders_tbl(i);
2642 END LOOP;
2643
2644 RETURN l_ret_string;
2645
2646 END GET_ALL_INTERNAL_ORDERS;
2647
2648 /*-----------------------------------------------------------------------------------------------------------*/
2649 -- swai: bug 14523152, FP of subhat, bug#14501090.
2650
2651 PROCEDURE create_internal_sales_orders
2652 (p_requisition_id IN NUMBER,
2653 p_so_operating_unit IN NUMBER,
2654 p_ir_operating_unit IN NUMBER,
2655 p_operating_unit IN NUMBER,
2656 x_return_status OUT NOCOPY VARCHAR2,
2657 x_msg_count OUT NOCOPY NUMBER,
2658 x_msg_data OUT NOCOPY VARCHAR2
2659 )
2660 IS
2661 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_INTERNAL_ORDERS_PVT.create_internal_sales_orders';
2662 lc_api_version CONSTANT NUMBER := 1.0;
2663 l_success BOOLEAN;
2664 l_request_id NUMBER;
2665 x_phase VARCHAR2(15);
2666 x_status VARCHAR2(10);
2667 x_dev_phase VARCHAR2(15);
2668 x_dev_status VARCHAR2(10);
2669 x_message VARCHAR2(2000);
2670
2671 BEGIN
2672
2673 fnd_request.set_org_id (p_ir_operating_unit);
2674 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2675 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling POCISO to populate OM interface tables');
2676 END IF;
2677 l_request_id := fnd_request.submit_request(
2678 'PO', 'POCISO', NULL, NULL, FALSE );
2679 COMMIT;
2680
2681 -- wait for the request to complete.
2682 l_success := fnd_concurrent.wait_for_request(
2683 request_id => l_request_id,
2684 interval => 3,
2685 phase => x_phase,
2686 status => x_status,
2687 dev_phase => x_dev_phase,
2688 dev_status => x_dev_status,
2689 message => x_message );
2690 IF NOT l_success
2691 THEN
2692 x_msg_data := x_message;
2693 x_return_status := fnd_api.g_ret_sts_error;
2694 return;
2695 END IF;
2696
2697 -- step 4. Create the internal sales order. Call Import Orders CP.
2698 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2699 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling OEOIMP to create ISO');
2700 END IF;
2701 -- bug#12337175, subhat.
2702 -- The OU should be source OU. Since the ISO is always created in the source OU.
2703 -- change p_destination_ous to p_source_ous
2704 l_request_id := fnd_request.submit_request(
2705 'ONT', 'OEOIMP', NULL, NULL, FALSE,p_so_operating_unit,10, p_requisition_id,
2706 NULL,'N',1,4,NULL,NULL,NULL,'Y','N','Y',p_operating_unit,'Y');
2707
2708 --dbms_output.put_line('launched cp '||l_request_id);
2709 COMMIT;
2710
2711 -- wait for the ISO creation to complete.
2712 l_success := fnd_concurrent.wait_for_request(
2713 request_id => l_request_id,
2714 interval => 3,
2715 phase => x_phase,
2716 status => x_status,
2717 dev_phase => x_dev_phase,
2718 dev_status => x_dev_status,
2719 message => x_message );
2720
2721 IF NOT l_success
2722 THEN
2723 x_msg_data := x_message;
2724 x_return_status := fnd_api.g_ret_sts_error;
2725 return;
2726 END IF;
2727
2728 END create_internal_sales_orders;
2729
2730 END CSD_INTERNAL_ORDERS_PVT;