[Home] [Help]
PACKAGE BODY: APPS.FLM_RECEIPT_PUB
Source
1 PACKAGE BODY FLM_RECEIPT_PUB AS
2 /* $Header: FLMKRCVB.pls 120.4 2011/06/02 01:49:36 atjen noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(15) := 'FLM_RECEIPT_PUB';
5 G_LOG_MODULE CONSTANT VARCHAR2(30) := 'flm.plsql.' || G_PKG_NAME || '.';
6
7
8 FUNCTION get_employee_id
9 RETURN NUMBER
10 IS
11
12 CURSOR c_employee_id
13 IS
14 SELECT employee_id
15 FROM fnd_user
16 WHERE user_id = FND_GLOBAL.USER_ID;
17
18 l_employee_id NUMBER;
19
20 BEGIN
21
22 OPEN c_employee_id;
23 FETCH c_employee_id INTO l_employee_id;
24 CLOSE c_employee_id;
25
26 RETURN l_employee_id;
27
28 END get_employee_id;
29
30
31 FUNCTION get_group_id
32 RETURN NUMBER
33 IS
34
35 CURSOR c_group_id
36 IS
37 SELECT rcv_interface_groups_s.nextval
38 FROM DUAL;
39
40 l_group_id NUMBER;
41
42 BEGIN
43
44 OPEN c_group_id;
45 FETCH c_group_id INTO l_group_id;
46 CLOSE c_group_id;
47
48 RETURN l_group_id;
49
50 END get_group_id;
51
52
53 PROCEDURE validate
54 ( p_kanban_card_id IN NUMBER,
55 p_quantity IN NUMBER,
56 x_ret_status OUT NOCOPY VARCHAR2,
57 x_err_msg OUT NOCOPY VARCHAR2
58 )
59 IS
60
61 l_api_name CONSTANT VARCHAR2(30) := 'validate';
62 l_log_module CONSTANT VARCHAR2(60) := G_LOG_MODULE || l_api_name;
63
64 -- Cursor to fetch kanban card details
65 CURSOR c_kanban_details(p_kanban_card_id IN NUMBER)
66 IS
67 SELECT mkc.kanban_card_id,
68 mkc.supply_status,
69 mkc.card_status,
70 mkc.source_type,
71 mkc.inventory_item_id,
72 mkc.organization_id
73 FROM mtl_kanban_cards mkc
74 WHERE mkc.kanban_card_id = p_kanban_card_id;
75
76 -- Cursor to fetch item details
77 CURSOR c_item_details(p_item_id IN NUMBER, p_organization_id IN NUMBER)
78 IS
79 SELECT msi.lot_control_code,
80 msi.serial_number_control_code
81 FROM mtl_system_items msi
82 WHERE msi.inventory_item_id = p_item_id
83 AND msi.organization_id = p_organization_id;
84
85 -- Cursor to fetch PO details for source type supplier
86 CURSOR c_po_details(p_kanban_card_id IN NUMBER)
87 IS
88 SELECT poh.po_header_id,
89 poh.approved_flag,
90 poll.qty_rcv_tolerance,
91 pod.quantity_ordered,
92 pod.quantity_delivered
93 FROM po_headers_all poh,
94 po_line_locations_all poll,
95 po_distributions_all pod,
96 mtl_kanban_cards mkc,
97 mtl_kanban_card_activity mkca
98 WHERE poh.po_header_id = pod.po_header_id
99 AND poll.line_location_id = pod.line_location_id
100 AND pod.po_distribution_id = mkca.document_detail_id
101 AND mkca.kanban_card_id = mkc.kanban_card_id
102 AND mkca.replenishment_cycle_id = mkc.current_replnsh_cycle_id
103 AND mkc.kanban_card_id = p_kanban_card_id;
104
105 -- Cursor to fetch shipment totals for source type inter org
106 CURSOR c_ship_totals(p_kanban_card_id IN NUMBER)
107 IS
108 SELECT COUNT(rsl.shipment_line_id) num_of_ship_lines,
109 SUM(rsl.quantity_shipped) quantity_shipped,
110 SUM(rsl.quantity_received) quantity_received
111 FROM rcv_shipment_lines rsl,
112 mtl_kanban_cards mkc,
113 mtl_kanban_card_activity mkca
114 WHERE rsl.requisition_line_id = mkca.document_detail_id
115 AND mkca.kanban_card_id = mkc.kanban_card_id
116 AND mkca.replenishment_cycle_id = mkc.current_replnsh_cycle_id
117 AND mkc.kanban_card_id = p_kanban_card_id;
118
119 l_kanban_details_rec c_kanban_details%ROWTYPE;
120 l_item_details_rec c_item_details%ROWTYPE;
121 l_po_details_rec c_po_details%ROWTYPE;
122 l_ship_totals_rec c_ship_totals%ROWTYPE;
123
124 l_receivable_quantity NUMBER;
125
126 BEGIN
127
128 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
129 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
130 l_log_module || '.begin',
131 'Parameters: ' ||
132 'p_kanban_card_id = ' || p_kanban_card_id || ', ' ||
133 'p_quantity = ' || p_quantity);
134 END IF;
135
136 -- Initialize return status to success
137 x_ret_status := FND_API.G_RET_STS_SUCCESS;
138
139 -- Validate kanban card
140 OPEN c_kanban_details(p_kanban_card_id);
141 FETCH c_kanban_details INTO l_kanban_details_rec;
142 CLOSE c_kanban_details;
143
144 IF l_kanban_details_rec.kanban_card_id IS NULL THEN
145
146 x_ret_status := FND_API.G_RET_STS_ERROR;
147
148 fnd_message.set_name('FLM', 'FLM_INVALID_KANBAN');
149 x_err_msg := fnd_message.get;
150
151 RETURN;
152
153 END IF;
154
155 -- Validate quantity
156 IF p_quantity IS NULL OR p_quantity <= 0 THEN
157
158 x_ret_status := FND_API.G_RET_STS_ERROR;
159
160 fnd_message.set_name('FLM', 'FLM_ATTRIBUTE_INVALID');
161 fnd_message.set_token('ATTRIBUTE', 'Receipt Quantity');
162 x_err_msg := x_err_msg || fnd_message.get || FND_CONST.NEWLINE;
163
164 END IF;
165
166
167 -- Validate card status for 'Active'
168 IF l_kanban_details_rec.card_status <> INV_KANBAN_PVT.G_Card_Status_Active THEN
169
170 x_ret_status := FND_API.G_RET_STS_ERROR;
171
172 fnd_message.set_name('FLM', 'FLM_INVALID_CARD_STATUS');
173 x_err_msg := x_err_msg || fnd_message.get || FND_CONST.NEWLINE;
174
175 END IF;
176
177 -- Validate source type for supplier / inter org
178 IF l_kanban_details_rec.source_type NOT IN (INV_KANBAN_PVT.G_Source_Type_InterOrg, INV_KANBAN_PVT.G_Source_Type_Supplier) THEN
179
180 x_ret_status := FND_API.G_RET_STS_ERROR;
181
182 fnd_message.set_name('FLM', 'FLM_RCV_SRC_TYPE_ERR');
183 x_err_msg := x_err_msg || fnd_message.get || FND_CONST.NEWLINE;
184
185 END IF;
186
187 -- Validate supply status for In Process
188 IF TRUNC(l_kanban_details_rec.supply_status) <> INV_KANBAN_PVT.G_Supply_Status_InProcess THEN
189
190 x_ret_status := FND_API.G_RET_STS_ERROR;
191
192 fnd_message.set_name('FLM', 'FLM_INVALID_KANBAN_STATUS');
193 x_err_msg := x_err_msg || fnd_message.get || FND_CONST.NEWLINE;
194
195 RETURN;
196
197 END IF;
198
199
200 OPEN c_item_details(l_kanban_details_rec.inventory_item_id, l_kanban_details_rec.organization_id);
201 FETCH c_item_details INTO l_item_details_rec;
202 CLOSE c_item_details;
203
204 IF l_item_details_rec.lot_control_code <> 1 OR l_item_details_rec.serial_number_control_code <> 1 THEN
205
206 x_ret_status := FND_API.G_RET_STS_ERROR;
207
208 fnd_message.set_name('FLM', 'FLM_EKB_LOT_SERIAL_ERR');
209 x_err_msg := x_err_msg || fnd_message.get || FND_CONST.NEWLINE;
210
211 END IF;
212
213
214 IF l_kanban_details_rec.source_type = INV_KANBAN_PVT.G_Source_Type_Supplier THEN
215
216 OPEN c_po_details(p_kanban_card_id);
217 FETCH c_po_details INTO l_po_details_rec;
218 CLOSE c_po_details;
219
220 IF l_po_details_rec.po_header_id IS NULL THEN
221
222 x_ret_status := FND_API.G_RET_STS_ERROR;
223
224 fnd_message.set_name ('FLM', 'FLM_EKB_PO_NOT_EXISTS');
225 x_err_msg := x_err_msg || fnd_message.get || FND_CONST.NEWLINE;
226
227 ELSE
228
229 -- Check for Approved PO
230 IF l_po_details_rec.approved_flag <> 'Y' THEN
231
232 x_ret_status := FND_API.G_RET_STS_ERROR;
233
234 fnd_message.set_name('FLM', 'FLM_EKB_APPROVED_PO');
235 x_err_msg := x_err_msg || fnd_message.get || FND_CONST.NEWLINE;
236
237 END IF;
238
239 l_receivable_quantity := l_po_details_rec.quantity_ordered
240 + (NVL(l_po_details_rec.qty_rcv_tolerance, 0) *
241 l_po_details_rec.quantity_ordered / 100)
242 - l_po_details_rec.quantity_delivered;
243
244 END IF;
245
246 ELSIF l_kanban_details_rec.source_type = INV_KANBAN_PVT.G_Source_Type_InterOrg THEN
247
248 OPEN c_ship_totals(p_kanban_card_id);
249 FETCH c_ship_totals INTO l_ship_totals_rec;
250 CLOSE c_ship_totals;
251
252 IF l_ship_totals_rec.num_of_ship_lines = 0 THEN
253
254 x_ret_status := FND_API.G_RET_STS_ERROR;
255
256 fnd_message.set_name('FLM', 'FLM_EKB_SHIP_NOT_EXISTS');
257 x_err_msg := x_err_msg || fnd_message.get || FND_CONST.NEWLINE;
258
259 ELSE
260
261 l_receivable_quantity := l_ship_totals_rec.quantity_shipped - l_ship_totals_rec.quantity_received;
262
263 END IF;
264
265 END IF;
266
267
268 IF p_quantity > l_receivable_quantity THEN
269
270 x_ret_status := FND_API.G_RET_STS_ERROR;
271
272 fnd_message.set_name('FLM', 'FLM_RECEIVE_QTY_ERR');
273 x_err_msg := x_err_msg || fnd_message.get || FND_CONST.NEWLINE;
274
275 END IF;
276
277
278 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
279 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
280 l_log_module || '.end',
281 'x_ret_status = ' || x_ret_status || ', ' ||
282 'x_err_msg = ' || x_err_msg);
283 END IF;
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 x_err_msg := SQLERRM;
289
290 END validate;
291
292
293 PROCEDURE request_online
294 ( p_group_id IN VARCHAR2,
295 x_ret_status OUT NOCOPY VARCHAR2,
296 x_err_msg OUT NOCOPY VARCHAR2
297 )
298 IS
299
300 l_api_name CONSTANT VARCHAR2(30) := 'request_online';
301 l_log_module CONSTANT VARCHAR2(60) := G_LOG_MODULE || l_api_name;
302
303 l_status NUMBER;
304 l_timeout NUMBER;
305 l_outcome VARCHAR2(30);
306 l_message VARCHAR2(240);
307
308 l_val1 VARCHAR2(480);
309 l_val2 VARCHAR2(480);
310 l_val3 VARCHAR2(480);
311 l_val4 VARCHAR2(480);
312 l_val5 VARCHAR2(480);
313 l_val6 VARCHAR2(480);
314 l_val7 VARCHAR2(480);
315 l_val8 VARCHAR2(480);
316 l_val9 VARCHAR2(480);
317 l_val10 VARCHAR2(480);
318 l_val11 VARCHAR2(480);
319 l_val12 VARCHAR2(480);
320 l_val13 VARCHAR2(480);
321 l_val14 VARCHAR2(480);
322 l_val15 VARCHAR2(480);
323 l_val16 VARCHAR2(480);
324 l_val17 VARCHAR2(480);
325 l_val18 VARCHAR2(480);
326 l_val19 VARCHAR2(480);
327 l_val20 VARCHAR2(480);
328
329 l_delete_rows BOOLEAN := FALSE;
330
331 l_str VARCHAR2(4000);
332
333 BEGIN
334
335 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
336 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
337 l_log_module || '.begin',
338 'Parameters: p_group_id = ' || p_group_id);
339 END IF;
340
341 l_timeout := fnd_profile.value('INV_RPC_TIMEOUT');
342 IF (l_timeout is NULL) THEN
343 l_timeout := 300;
344 END IF;
345
346 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
347 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
348 l_log_module || '.calling_RCVTPO',
349 'Before fnd_transaction.synchronous');
350 END IF;
351
352 l_status := fnd_transaction.synchronous
353 ( l_timeout, l_outcome, l_message,
354 'PO', 'RCVTPO', 'ONLINE', p_group_id,
355 NULL, NULL, NULL, NULL, NULL, NULL,
356 NULL, NULL, NULL, NULL, NULL, NULL,
357 NULL, NULL, NULL, NULL, NULL, NULL
358 );
359
360 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
361 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
362 l_log_module || '.calling_RCVTPO',
363 'After fnd_transaction.synchronous: ' ||
364 'status = ' || l_status || ', ' ||
365 'outcome = ' || l_outcome || ', ' ||
366 'message = ' || l_message);
367 END IF;
368
369 IF (l_status = fnd_transaction.E_SUCCESS AND (l_outcome NOT IN ('WARNING', 'ERROR'))) THEN
370
371 x_ret_status := FND_API.G_RET_STS_SUCCESS;
372
373 ELSIF (l_status = fnd_transaction.E_TIMEOUT) THEN
374
375 x_ret_status := FND_API.G_RET_STS_ERROR;
376 x_err_msg := 'RCVTM timeout';
377
378 l_delete_rows := TRUE;
379
380 ELSIF (l_status = fnd_transaction.E_NOMGR) THEN
381
382 x_ret_status := FND_API.G_RET_STS_ERROR;
383 x_err_msg := 'RCV Manager not available';
384
385 l_delete_rows := TRUE;
386
387 ELSIF (l_status = fnd_transaction.E_OTHER OR (l_outcome IN ('WARNING', 'ERROR'))) THEN
388
389 x_ret_status := FND_API.G_RET_STS_ERROR;
390
391 l_status := fnd_transaction.get_values
392 ( l_val1, l_val2, l_val3, l_val4, l_val5,
393 l_val6, l_val7, l_val8, l_val9, l_val10,
394 l_val11, l_val12, l_val13, l_val14, l_val15,
395 l_val16, l_val17, l_val18, l_val19, l_val20
396 );
397
398 l_str := l_val1;
399
400 IF (l_val2 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val2; END IF;
401 IF (l_val3 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val3; END IF;
402 IF (l_val4 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val4; END IF;
403 IF (l_val5 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val5; END IF;
404 IF (l_val6 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val6; END IF;
405 IF (l_val7 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val7; END IF;
406 IF (l_val8 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val8; END IF;
407 IF (l_val9 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val9; END IF;
408 IF (l_val10 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val10; END IF;
409 IF (l_val11 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val11; END IF;
410 IF (l_val12 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val12; END IF;
411 IF (l_val13 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val13; END IF;
412 IF (l_val14 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val14; END IF;
413 IF (l_val15 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val15; END IF;
414 IF (l_val16 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val16; END IF;
415 IF (l_val17 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val17; END IF;
416 IF (l_val18 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val18; END IF;
417 IF (l_val19 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val19; END IF;
418 IF (l_val20 IS NOT NULL) THEN l_str := l_str || FND_CONST.NEWLINE || l_val20; END IF;
419
420 x_err_msg := l_str;
421
422 l_delete_rows := TRUE;
423
424 END IF;
425
426 IF (l_delete_rows) THEN
427
428 DELETE FROM rcv_transactions_interface
429 WHERE group_id = p_group_id;
430
431 COMMIT;
432
433 END IF;
434
435 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
436 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
437 l_log_module || '.end',
438 'x_ret_status = ' || x_ret_status || ', ' ||
439 'x_err_msg = ' || x_err_msg);
440 END IF;
441
442 END request_online;
443
444
445 PROCEDURE receive_supplier
446 ( p_kanban_card_id IN NUMBER,
447 p_receipt_quantity IN NUMBER,
448 x_receipt_num OUT NOCOPY NUMBER,
449 x_ret_status OUT NOCOPY VARCHAR2,
450 x_err_msg OUT NOCOPY VARCHAR2
451 )
452 IS
453
454 l_api_name CONSTANT VARCHAR2(30) := 'receive_supplier';
455 l_log_module CONSTANT VARCHAR2(60) := G_LOG_MODULE || l_api_name;
456
457 CURSOR c_rcv_details(p_kanban_card_id IN NUMBER)
458 IS
459 SELECT pol.category_id item_category_id,
460 pol.item_id item_id,
461 DECODE(msi.allow_item_desc_update_flag, 'Y',
462 pol.item_description,
463 NVL(msi.description, pol.item_description)
464 ) item_description,
465 pol.item_revision item_revision,
466 muom.uom_code uom_code,
467 poll.ship_to_location_id ship_to_location_id,
468 msi.primary_unit_of_measure primary_uom,
469 poh.vendor_id vendor_id,
470 poh.vendor_site_id vendor_site_id,
471 poll.ship_to_organization_id to_organization_id,
472 poll.receiving_routing_id routing_id,
473 poh.po_header_id po_header_id,
474 poll.po_release_id po_release_id,
475 pol.po_line_id po_line_id,
476 poll.line_location_id po_line_location_id,
477 NVL(poll.price_override, pol.unit_price) unit_price,
478 poh.currency_code currency_code,
479 poh.rate_type currency_conversion_type,
480 poh.rate currency_conversion_rate,
481 poh.rate_date currency_conversion_date,
482 pod.po_distribution_id po_distribution_id,
483 pod.deliver_to_location_id deliver_to_location_id,
484 mkc.subinventory_name subinventory_name,
485 mkc.locator_id locator_id,
486 NVL(poll.promised_date, poll.need_by_date) expected_receipt_date,
487 pol.vendor_product_num vendor_item_number,
488 poll.attribute_category attribute_category,
489 poll.attribute1 attribute1,
490 poll.attribute2 attribute2,
491 poll.attribute3 attribute3,
492 poll.attribute4 attribute4,
493 poll.attribute5 attribute5,
494 poll.attribute6 attribute6,
495 poll.attribute7 attribute7,
496 poll.attribute8 attribute8,
497 poll.attribute9 attribute9,
498 poll.attribute10 attribute10,
499 poll.attribute11 attribute11,
500 poll.attribute12 attribute12,
501 poll.attribute13 attribute13,
502 poll.attribute14 attribute14,
503 poll.attribute15 attribute15,
504 poll.government_context government_context,
505 msi.lot_control_code lot_control_code,
506 msi.serial_number_control_code serial_number_control_code,
507 poll.country_of_origin_code country_of_origin_code,
508 poll.secondary_quantity secondary_quantity,
509 poll.secondary_unit_of_measure secondary_uom,
510 poll.org_id org_id
511 FROM po_headers_all poh,
512 po_lines_all pol,
513 po_line_locations_all poll,
514 po_distributions_all pod,
515 mtl_system_items_vl msi,
516 mtl_units_of_measure muom,
517 mtl_kanban_cards mkc,
518 mtl_kanban_card_activity mkca
519 WHERE poh.po_header_id = pol.po_header_id
520 AND pol.po_line_id = poll.po_line_id
521 AND poll.line_location_id = pod.line_location_id
522 AND pod.po_distribution_id = mkca.document_detail_id
523 AND msi.inventory_item_id = mkc.inventory_item_id
524 AND msi.organization_id = mkc.organization_id
525 AND muom.unit_of_measure = msi.primary_unit_of_measure
526 AND mkca.kanban_card_id = mkc.kanban_card_id
527 AND mkca.replenishment_cycle_id = mkc.current_replnsh_cycle_id
528 AND mkc.kanban_card_id = p_kanban_card_id;
529
530 l_rcv_details_rec c_rcv_details%ROWTYPE;
531
532 l_rowid VARCHAR2(40);
533 l_shipment_header_id NUMBER;
534 l_interface_transaction_id NUMBER;
535 l_employee_id NUMBER;
536 l_group_id NUMBER;
537
538 BEGIN
539
540 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
541 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
542 l_log_module || '.begin',
543 'Parameters: ' ||
544 'p_kanban_card_id = ' || p_kanban_card_id || ', ' ||
545 'p_receipt_quantity = ' || p_receipt_quantity);
546 END IF;
547
548 -- Initialize return status to success
549 x_ret_status := FND_API.G_RET_STS_SUCCESS;
550
551
552 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
553 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
554 l_log_module || '.receive_details',
555 'Opening and Fetching c_rcv_details');
556 END IF;
557
558 OPEN c_rcv_details(p_kanban_card_id);
559 FETCH c_rcv_details INTO l_rcv_details_rec;
560 CLOSE c_rcv_details;
561
562 IF (l_rcv_details_rec.po_header_id IS NULL) THEN
563 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
564 x_err_msg := 'Receiving details can not be found';
565 RETURN;
566 END IF;
567
568
569 l_employee_id := get_employee_id;
570
571 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
572 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
573 l_log_module || '.rcv_shipment_headers',
574 'Before rcv_shipment_headers_pkg.insert_row');
575 END IF;
576
577 rcv_shipment_headers_pkg.insert_row
578 ( X_Rowid => l_rowid,
579 X_Shipment_Header_Id => l_shipment_header_id,
580 X_Last_Update_Date => SYSDATE,
581 X_Last_Updated_By => FND_GLOBAL.USER_ID,
582 X_Creation_Date => SYSDATE,
583 X_Created_By => FND_GLOBAL.USER_ID,
584 X_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
585 X_Receipt_Source_Code => 'VENDOR',
586 X_Vendor_Id => l_rcv_details_rec.vendor_id,
587 X_Vendor_Site_Id => l_rcv_details_rec.vendor_site_id,
588 X_Organization_Id => NULL,
589 X_ship_to_org_id => l_rcv_details_rec.to_organization_id,
590 X_Shipment_Num => NULL,
591 X_Receipt_Num => x_receipt_num,
592 X_Ship_To_Location_Id => l_rcv_details_rec.ship_to_location_id,
593 X_Bill_Of_Lading => NULL,
594 X_Packing_Slip => NULL,
595 X_Shipped_Date => NULL,
596 X_Freight_Carrier_Code => NULL,
597 X_Expected_Receipt_Date => l_rcv_details_rec.expected_receipt_date,
598 X_Employee_Id => l_employee_id,
599 X_Num_Of_Containers => NULL,
600 X_Waybill_Airbill_Num => NULL,
601 X_Comments => NULL,
602 X_Attribute_Category => l_rcv_details_rec.attribute_category,
603 X_Attribute1 => l_rcv_details_rec.attribute1,
604 X_Attribute2 => l_rcv_details_rec.attribute2,
605 X_Attribute3 => l_rcv_details_rec.attribute3,
606 X_Attribute4 => l_rcv_details_rec.attribute4,
607 X_Attribute5 => l_rcv_details_rec.attribute5,
608 X_Attribute6 => l_rcv_details_rec.attribute6,
609 X_Attribute7 => l_rcv_details_rec.attribute7,
610 X_Attribute8 => l_rcv_details_rec.attribute8,
611 X_Attribute9 => l_rcv_details_rec.attribute9,
612 X_Attribute10 => l_rcv_details_rec.attribute10,
613 X_Attribute11 => l_rcv_details_rec.attribute11,
614 X_Attribute12 => l_rcv_details_rec.attribute12,
615 X_Attribute13 => l_rcv_details_rec.attribute13,
616 X_Attribute14 => l_rcv_details_rec.attribute14,
617 X_Attribute15 => l_rcv_details_rec.attribute15,
618 X_Ussgl_Transaction_Code => NULL,
619 X_Government_Context => l_rcv_details_rec.government_context,
620 X_Request_Id => NULL,
621 X_Program_Application_Id => NULL,
622 X_Program_Id => NULL,
623 X_Program_Update_Date => NULL,
624 X_customer_id => NULL,
625 X_customer_site_id => NULL
626 );
627
628 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
629 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
630 l_log_module || '.rcv_shipment_headers',
631 'After rcv_shipment_headers_pkg.insert_row: ' ||
632 'l_shipment_header_id = ' || l_shipment_header_id || ', ' ||
633 'x_receipt_num = ' || x_receipt_num);
634 END IF;
635
636
637 l_group_id := get_group_id;
638
639 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
640 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
641 l_log_module || '.rcv_trx_interface',
642 'Before rcv_trx_interface_insert_pkg.insert_row');
643 END IF;
644
645 rcv_trx_interface_insert_pkg.insert_row
646 ( X_Rowid => l_rowid,
647 X_Interface_Transaction_Id => l_interface_transaction_id,
648 X_Group_Id => l_group_id,
649 X_Last_Update_Date => SYSDATE,
650 X_Last_Updated_By => FND_GLOBAL.USER_ID,
651 X_Creation_Date => SYSDATE,
652 X_Created_By => FND_GLOBAL.USER_ID,
653 X_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
654 X_Transaction_Type => 'RECEIVE',
655 X_Transaction_Date => SYSDATE,
656 X_Processing_Status_Code => 'PENDING',
657 X_Processing_Mode_Code => 'ONLINE',
658 X_Processing_Request_Id => NULL,
659 X_Transaction_Status_Code => 'PENDING',
660 X_Category_Id => l_rcv_details_rec.item_category_id,
661 X_Quantity => p_receipt_quantity,
662 X_Unit_Of_Measure => l_rcv_details_rec.primary_uom,
663 X_Interface_Source_Code => 'FLM',
664 X_Interface_Source_Line_Id => NULL,
665 X_Inv_Transaction_Id => NULL,
666 X_Item_Id => l_rcv_details_rec.item_id,
667 X_Item_Description => l_rcv_details_rec.item_description,
668 X_Item_Revision => l_rcv_details_rec.item_revision,
669 X_Uom_Code => l_rcv_details_rec.uom_code,
670 X_Employee_Id => l_employee_id,
671 X_Auto_Transact_Code => 'DELIVER',
672 X_Shipment_Header_Id => l_shipment_header_id,
673 X_Shipment_Line_Id => NULL,
674 X_Ship_To_Location_Id => l_rcv_details_rec.ship_to_location_id,
675 X_Primary_Quantity => p_receipt_quantity,
676 X_Primary_Unit_Of_Measure => l_rcv_details_rec.primary_uom,
677 X_Receipt_Source_Code => 'VENDOR',
678 X_Vendor_Id => l_rcv_details_rec.vendor_id,
679 X_Vendor_Site_Id => l_rcv_details_rec.vendor_site_id,
680 X_From_Organization_Id => NULL,
681 X_To_Organization_Id => l_rcv_details_rec.to_organization_id,
682 X_Routing_Header_Id => l_rcv_details_rec.routing_id,
683 X_Routing_Step_Id => 1,
684 X_Source_Document_Code => 'PO',
685 X_Parent_Transaction_Id => NULL,
686 X_Po_Header_Id => l_rcv_details_rec.po_header_id,
687 X_Po_Revision_Num => NULL,
688 X_Po_Release_Id => l_rcv_details_rec.po_release_id,
689 X_Po_Line_Id => l_rcv_details_rec.po_line_id,
690 X_Po_Line_Location_Id => l_rcv_details_rec.po_line_location_id,
691 X_Po_Unit_Price => l_rcv_details_rec.unit_price,
692 X_Currency_Code => l_rcv_details_rec.currency_code,
693 X_Currency_Conversion_Type => l_rcv_details_rec.currency_conversion_type,
694 X_Currency_Conversion_Rate => l_rcv_details_rec.currency_conversion_rate,
695 X_Currency_Conversion_Date => l_rcv_details_rec.currency_conversion_date,
696 X_Po_Distribution_Id => l_rcv_details_rec.po_distribution_id,
697 X_Requisition_Line_Id => NULL,
698 X_Req_Distribution_Id => NULL,
699 X_Charge_Account_Id => NULL,
700 X_Substitute_Unordered_Code => NULL,
701 X_Receipt_Exception_Flag => NULL,
702 X_Accrual_Status_Code => NULL,
703 X_Inspection_Status_Code => 'NOT INSPECTED',
704 X_Inspection_Quality_Code => NULL,
705 X_Destination_Type_Code => 'INVENTORY',
706 X_Deliver_To_Person_Id => l_employee_id,
707 X_Location_Id => l_rcv_details_rec.deliver_to_location_id,
708 X_Deliver_To_Location_Id => l_rcv_details_rec.deliver_to_location_id,
709 X_Subinventory => l_rcv_details_rec.subinventory_name,
710 X_Locator_Id => l_rcv_details_rec.locator_id,
711 X_Wip_Entity_Id => NULL,
712 X_Wip_Line_Id => NULL,
713 X_Department_Code => NULL,
714 X_Wip_Repetitive_Schedule_Id => NULL,
715 X_Wip_Operation_Seq_Num => NULL,
716 X_Wip_Resource_Seq_Num => NULL,
717 X_Bom_Resource_Id => NULL,
718 X_Shipment_Num => NULL,
719 X_Freight_Carrier_Code => NULL,
720 X_Bill_Of_Lading => NULL,
721 X_Packing_Slip => NULL,
722 X_Shipped_Date => NULL,
723 X_Expected_Receipt_Date => l_rcv_details_rec.expected_receipt_date,
724 X_Actual_Cost => NULL,
725 X_Transfer_Cost => NULL,
726 X_Transportation_Cost => NULL,
727 X_Transportation_Account_Id => NULL,
728 X_Num_Of_Containers => NULL,
729 X_Waybill_Airbill_Num => NULL,
730 X_Vendor_Item_Num => l_rcv_details_rec.vendor_item_number,
731 X_Vendor_Lot_Num => NULL,
732 X_Rma_Reference => NULL,
733 X_Comments => NULL,
734 X_Attribute_Category => l_rcv_details_rec.attribute_category,
735 X_Attribute1 => l_rcv_details_rec.attribute1,
736 X_Attribute2 => l_rcv_details_rec.attribute2,
737 X_Attribute3 => l_rcv_details_rec.attribute3,
738 X_Attribute4 => l_rcv_details_rec.attribute4,
739 X_Attribute5 => l_rcv_details_rec.attribute5,
740 X_Attribute6 => l_rcv_details_rec.attribute6,
741 X_Attribute7 => l_rcv_details_rec.attribute7,
742 X_Attribute8 => l_rcv_details_rec.attribute8,
743 X_Attribute9 => l_rcv_details_rec.attribute9,
744 X_Attribute10 => l_rcv_details_rec.attribute10,
745 X_Attribute11 => l_rcv_details_rec.attribute11,
746 X_Attribute12 => l_rcv_details_rec.attribute12,
747 X_Attribute13 => l_rcv_details_rec.attribute13,
748 X_Attribute14 => l_rcv_details_rec.attribute14,
749 X_Attribute15 => l_rcv_details_rec.attribute15,
750 X_Ship_Head_Attribute_Category => NULL,
751 X_Ship_Head_Attribute1 => NULL,
752 X_Ship_Head_Attribute2 => NULL,
753 X_Ship_Head_Attribute3 => NULL,
754 X_Ship_Head_Attribute4 => NULL,
755 X_Ship_Head_Attribute5 => NULL,
756 X_Ship_Head_Attribute6 => NULL,
757 X_Ship_Head_Attribute7 => NULL,
758 X_Ship_Head_Attribute8 => NULL,
759 X_Ship_Head_Attribute9 => NULL,
760 X_Ship_Head_Attribute10 => NULL,
761 X_Ship_Head_Attribute11 => NULL,
762 X_Ship_Head_Attribute12 => NULL,
763 X_Ship_Head_Attribute13 => NULL,
764 X_Ship_Head_Attribute14 => NULL,
765 X_Ship_Head_Attribute15 => NULL,
766 X_Ship_Line_Attribute_Category => NULL,
767 X_Ship_Line_Attribute1 => NULL,
768 X_Ship_Line_Attribute2 => NULL,
769 X_Ship_Line_Attribute3 => NULL,
770 X_Ship_Line_Attribute4 => NULL,
771 X_Ship_Line_Attribute5 => NULL,
772 X_Ship_Line_Attribute6 => NULL,
773 X_Ship_Line_Attribute7 => NULL,
774 X_Ship_Line_Attribute8 => NULL,
775 X_Ship_Line_Attribute9 => NULL,
776 X_Ship_Line_Attribute10 => NULL,
777 X_Ship_Line_Attribute11 => NULL,
778 X_Ship_Line_Attribute12 => NULL,
779 X_Ship_Line_Attribute13 => NULL,
780 X_Ship_Line_Attribute14 => NULL,
781 X_Ship_Line_Attribute15 => NULL,
782 X_Ussgl_Transaction_Code => NULL,
783 X_Government_Context => l_rcv_details_rec.government_context,
784 X_Reason_Id => NULL,
785 X_Destination_Context => NULL,
786 X_Source_Doc_Quantity => p_receipt_quantity,
787 X_Source_Doc_Unit_Of_Measure => l_rcv_details_rec.primary_uom,
788 X_Lot_Number_CC => l_rcv_details_rec.lot_control_code,
789 X_Serial_Number_CC => l_rcv_details_rec.serial_number_control_code,
790 X_QA_Collection_Id => NULL,
791 X_Country_of_Origin_Code => l_rcv_details_rec.country_of_origin_code,
792 X_oe_order_header_id => NULL,
793 X_oe_order_line_id => NULL,
794 X_customer_item_num => NULL,
795 X_customer_id => NULL,
796 X_customer_site_id => NULL,
797 X_put_away_rule_id => NULL,
798 X_put_away_strategy_id => NULL,
799 X_lpn_id => NULL,
800 X_transfer_lpn_id => NULL,
801 X_secondary_quantity => l_rcv_details_rec.secondary_quantity,
802 X_secondary_unit_of_measure => l_rcv_details_rec.secondary_uom,
803 p_org_id => l_rcv_details_rec.org_id
804 );
805
806 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
807 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
808 l_log_module || '.rcv_trx_interface',
809 'After rcv_trx_interface_insert_pkg.insert_row: ' ||
810 'l_interface_transaction_id = ' || l_interface_transaction_id);
811 END IF;
812
813
814 COMMIT;
815
816 request_online(p_group_id => l_group_id,
817 x_ret_status => x_ret_status,
818 x_err_msg => x_err_msg
819 );
820
821 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
822 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
823 l_log_module || '.request_online',
824 'After request_online: ' ||
825 'x_ret_status = ' || x_ret_status || ', ' ||
826 'x_err_msg = ' || x_err_msg);
827 END IF;
828
829 IF x_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
830 RETURN;
831 END IF;
832
833
834 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
835 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
836 l_log_module || '.receive_supplier_hook',
837 'Before FLM_KANBAN_CUSTOM_PKG.receive_supplier_hook');
838 END IF;
839
840 FLM_KANBAN_CUSTOM_PKG.receive_supplier_hook
841 ( p_kanban_card_id => p_kanban_card_id,
842 p_po_header_id => l_rcv_details_rec.po_header_id,
843 p_po_distribution_id => l_rcv_details_rec.po_distribution_id,
844 p_receipt_quantity => p_receipt_quantity,
845 x_return_status => x_ret_status,
846 x_msg_data => x_err_msg
847 );
848
849
850 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
851 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
852 l_log_module || '.end',
853 'x_receipt_num = ' || x_receipt_num || ', ' ||
854 'x_ret_status = ' || x_ret_status || ', ' ||
855 'x_err_msg = ' || x_err_msg);
856 END IF;
857
858 END receive_supplier;
859
860
861 PROCEDURE receive_interorg
862 ( p_kanban_card_id IN NUMBER,
863 p_receipt_quantity IN NUMBER,
864 x_receipt_num OUT NOCOPY NUMBER,
865 x_ret_status OUT NOCOPY VARCHAR2,
866 x_err_msg OUT NOCOPY VARCHAR2
867 )
868 IS
869
870 l_api_name CONSTANT VARCHAR2(30) := 'receive_interorg';
871 l_log_module CONSTANT VARCHAR2(60) := G_LOG_MODULE || l_api_name;
872
873 CURSOR c_shipment_lines(p_kanban_card_id IN NUMBER)
874 IS
875 SELECT rsl.category_id,
876 rsl.quantity_shipped - rsl.quantity_received quantity,
877 rsl.item_id,
878 rsl.item_description,
879 rsl.item_revision,
880 muom.uom_code,
881 rsl.shipment_header_id,
882 rsl.shipment_line_id,
883 rsl.ship_to_location_id,
884 rsl.primary_unit_of_measure,
885 rsl.from_organization_id,
886 rsl.to_organization_id,
887 rsl.routing_header_id,
888 rsl.source_document_code,
889 rsl.requisition_line_id,
890 rsl.req_distribution_id,
891 rsl.charge_account_id,
892 rsl.destination_type_code,
893 rsl.deliver_to_person_id,
894 rsl.deliver_to_location_id,
895 rsl.to_subinventory,
896 rsl.locator_id,
897 rsl.packing_slip,
898 rsl.transfer_cost,
899 rsl.transportation_cost,
900 rsl.transportation_account_id,
901 rsl.comments,
902 rsl.attribute_category,
903 rsl.attribute1,
904 rsl.attribute2,
905 rsl.attribute3,
906 rsl.attribute4,
907 rsl.attribute5,
908 rsl.attribute6,
909 rsl.attribute7,
910 rsl.attribute8,
911 rsl.attribute9,
912 rsl.attribute10,
913 rsl.attribute11,
914 rsl.attribute12,
915 rsl.attribute13,
916 rsl.attribute14,
917 rsl.attribute15,
918 rsl.ussgl_transaction_code,
919 rsl.government_context,
920 rsl.reason_id,
921 rsl.destination_context,
922 msi.lot_control_code,
923 msi.serial_number_control_code,
924 rsl.country_of_origin_code,
925 rsl.cost_group_id,
926 porl.org_id
927 FROM rcv_shipment_lines rsl,
928 po_requisition_lines_all porl,
929 mtl_system_items msi,
930 mtl_units_of_measure muom,
931 mtl_kanban_cards mkc,
932 mtl_kanban_card_activity mkca
933 WHERE rsl.requisition_line_id = porl.requisition_line_id
934 AND rsl.requisition_line_id = mkca.document_detail_id
935 AND msi.inventory_item_id = mkc.inventory_item_id
936 AND msi.organization_id = mkc.organization_id
937 AND muom.unit_of_measure = msi.primary_unit_of_measure
938 AND mkca.kanban_card_id = mkc.kanban_card_id
939 AND mkca.replenishment_cycle_id = mkc.current_replnsh_cycle_id
940 AND mkc.kanban_card_id = p_kanban_card_id
941 AND rsl.shipment_line_status_code IN ('EXPECTED', 'PARTIALLY RECEIVED')
942 ORDER BY rsl.shipment_header_id, rsl.line_num;
943
944 CURSOR c_shipment_header(p_shipment_header_id IN NUMBER)
945 IS
946 SELECT rowid,
947 shipment_header_id,
948 receipt_source_code,
949 vendor_id,
950 vendor_site_Id,
951 organization_Id,
952 ship_to_org_id,
953 shipment_num,
954 receipt_num,
955 ship_to_location_id,
956 bill_of_lading,
957 packing_slip,
958 shipped_date,
959 freight_carrier_code,
960 expected_receipt_date,
961 num_of_containers,
962 waybill_airbill_num,
963 comments,
964 attribute_category,
965 attribute1,
966 attribute2,
967 attribute3,
968 attribute4,
969 attribute5,
970 attribute6,
971 attribute7,
972 attribute8,
973 attribute9,
974 attribute10,
975 attribute11,
976 attribute12,
977 attribute13,
978 attribute14,
979 attribute15,
980 ussgl_transaction_code,
981 government_context,
982 request_id,
983 program_application_id,
984 program_id,
985 customer_id,
986 customer_site_id
987 FROM rcv_shipment_headers
988 WHERE shipment_header_id = p_shipment_header_id;
989
990 TYPE ship_lines_tbl_type IS TABLE OF c_shipment_lines%ROWTYPE INDEX BY BINARY_INTEGER;
991
992 l_ship_lines_tbl ship_lines_tbl_type;
993 l_ship_line_rec c_shipment_lines%ROWTYPE;
994 l_ship_header_rec c_shipment_header%ROWTYPE;
995
996 l_remaining_quantity NUMBER;
997 l_quantity NUMBER;
998 l_temp_receipt_num VARCHAR2(40);
999 l_receipt_exists NUMBER;
1000 l_employee_id NUMBER;
1001 l_group_id NUMBER;
1002 l_rowid VARCHAR2(40);
1003 l_interface_transaction_id NUMBER;
1004
1005 BEGIN
1006
1007 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1008 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1009 l_log_module || '.begin',
1010 'Parameters: ' ||
1011 'p_kanban_card_id = ' || p_kanban_card_id || ', ' ||
1012 'p_receipt_quantity = ' || p_receipt_quantity);
1013 END IF;
1014
1015 -- Initialize return status to success
1016 x_ret_status := FND_API.G_RET_STS_SUCCESS;
1017
1018 -- When there are multiple shipment lines, need to keep track of the
1019 -- remaining receipt quantity to carry over to subsequent shipment lines
1020 l_remaining_quantity := p_receipt_quantity;
1021
1022
1023 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1024 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1025 l_log_module || '.shipment_lines',
1026 'Begin looping cursor c_shipment_lines');
1027 END IF;
1028
1029 OPEN c_shipment_lines(p_kanban_card_id);
1030 FETCH c_shipment_lines BULK COLLECT INTO l_ship_lines_tbl;
1031 CLOSE c_shipment_lines;
1032
1033 FOR i IN l_ship_lines_tbl.FIRST..l_ship_lines_tbl.LAST LOOP
1034 l_ship_line_rec := l_ship_lines_tbl(i);
1035
1036 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1037 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1038 l_log_module || '.shipment_line',
1039 'shipment_header_id = ' || l_ship_line_rec.shipment_header_id || ', ' ||
1040 'shipment_line_id = ' || l_ship_line_rec.shipment_line_id);
1041 END IF;
1042
1043 l_employee_id := get_employee_id;
1044
1045 OPEN c_shipment_header(l_ship_line_rec.shipment_header_id);
1046 FETCH c_shipment_header INTO l_ship_header_rec;
1047 CLOSE c_shipment_header;
1048
1049 IF (l_ship_header_rec.shipment_header_id IS NULL) THEN
1050 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
1051 x_err_msg := 'Shipment header can not be found';
1052 RETURN;
1053 END IF;
1054
1055 IF l_ship_header_rec.receipt_num IS NULL THEN
1056
1057 -- Generate receipt number
1058 SELECT TO_CHAR(next_receipt_num + 1)
1059 INTO l_temp_receipt_num
1060 FROM rcv_parameters
1061 WHERE organization_id = l_ship_header_rec.ship_to_org_id
1062 FOR UPDATE OF next_receipt_num;
1063
1064 LOOP
1065 SELECT COUNT(*)
1066 INTO l_receipt_exists
1067 FROM rcv_shipment_headers rsh
1068 WHERE receipt_num = l_temp_receipt_num
1069 AND ship_to_org_id = l_ship_header_rec.ship_to_org_id;
1070
1071 IF l_receipt_exists = 0 THEN
1072 UPDATE rcv_parameters
1073 SET next_receipt_num = l_temp_receipt_num
1074 WHERE organization_id = l_ship_header_rec.ship_to_org_id;
1075
1076 EXIT;
1077 ELSE
1078 l_temp_receipt_num := TO_CHAR(TO_NUMBER(l_temp_receipt_num) + 1);
1079 END IF;
1080 END LOOP;
1081
1082 x_receipt_num := l_temp_receipt_num;
1083
1084 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1085 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1086 l_log_module || '.gen_receipt_num',
1087 'x_receipt_num = ' || x_receipt_num);
1088 END IF;
1089
1090 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1091 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1092 l_log_module || '.rcv_shipment_headers',
1093 'Calling rcv_shipment_headers_pkg.update_row');
1094 END IF;
1095
1096 rcv_shipment_headers_pkg.update_row
1097 ( X_Rowid => l_ship_header_rec.rowid,
1098 X_Shipment_Header_Id => l_ship_header_rec.shipment_header_id,
1099 X_Last_Update_Date => SYSDATE,
1100 X_Last_Updated_By => FND_GLOBAL.USER_ID,
1101 X_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
1102 X_Receipt_Source_Code => l_ship_header_rec.receipt_source_code,
1103 X_Vendor_Id => l_ship_header_rec.vendor_id,
1104 X_Vendor_Site_Id => l_ship_header_rec.vendor_site_id,
1105 X_Organization_Id => l_ship_header_rec.organization_Id,
1106 X_ship_to_org_id => l_ship_header_rec.ship_to_org_id,
1107 X_Shipment_Num => l_ship_header_rec.shipment_num,
1108 X_Receipt_Num => x_receipt_num,
1109 X_Ship_To_Location_Id => l_ship_header_rec.ship_to_location_id,
1110 X_Bill_Of_Lading => l_ship_header_rec.bill_of_lading,
1111 X_Packing_Slip => l_ship_header_rec.packing_slip,
1112 X_Shipped_Date => l_ship_header_rec.shipped_date,
1113 X_Freight_Carrier_Code => l_ship_header_rec.freight_carrier_code,
1114 X_Expected_Receipt_Date => l_ship_header_rec.expected_receipt_date,
1115 X_Employee_Id => l_employee_id,
1116 X_Num_Of_Containers => l_ship_header_rec.num_of_containers,
1117 X_Waybill_Airbill_Num => l_ship_header_rec.waybill_airbill_num,
1118 X_Comments => l_ship_header_rec.comments,
1119 X_Attribute_Category => l_ship_header_rec.attribute_category,
1120 X_Attribute1 => l_ship_header_rec.attribute1,
1121 X_Attribute2 => l_ship_header_rec.attribute2,
1122 X_Attribute3 => l_ship_header_rec.attribute3,
1123 X_Attribute4 => l_ship_header_rec.attribute4,
1124 X_Attribute5 => l_ship_header_rec.attribute5,
1125 X_Attribute6 => l_ship_header_rec.attribute6,
1126 X_Attribute7 => l_ship_header_rec.attribute7,
1127 X_Attribute8 => l_ship_header_rec.attribute8,
1128 X_Attribute9 => l_ship_header_rec.attribute9,
1129 X_Attribute10 => l_ship_header_rec.attribute10,
1130 X_Attribute11 => l_ship_header_rec.attribute11,
1131 X_Attribute12 => l_ship_header_rec.attribute12,
1132 X_Attribute13 => l_ship_header_rec.attribute13,
1133 X_Attribute14 => l_ship_header_rec.attribute14,
1134 X_Attribute15 => l_ship_header_rec.attribute15,
1135 X_Ussgl_Transaction_Code => l_ship_header_rec.ussgl_transaction_code,
1136 X_Government_Context => l_ship_header_rec.government_context,
1137 X_Request_Id => l_ship_header_rec.request_id,
1138 X_Program_Application_Id => l_ship_header_rec.program_application_id,
1139 X_Program_Id => l_ship_header_rec.program_id,
1140 X_Program_Update_Date => SYSDATE,
1141 X_customer_id => l_ship_header_rec.customer_id,
1142 X_customer_site_id => l_ship_header_rec.customer_site_id
1143 );
1144
1145 ELSE
1146
1147 x_receipt_num := l_ship_header_rec.receipt_num;
1148
1149 END IF;
1150
1151
1152 -- determine receipt quantity for current shipment line
1153 IF l_ship_line_rec.quantity > l_remaining_quantity THEN
1154 l_quantity := l_remaining_quantity;
1155 ELSE
1156 l_quantity := l_ship_line_rec.quantity;
1157 END IF;
1158
1159 -- keep track of the remaining receipt quantity for subsequent shipment lines
1160 l_remaining_quantity := l_remaining_quantity - l_quantity;
1161
1162 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1163 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1164 l_log_module || '.remaining_quantity',
1165 'l_quantity = ' || l_quantity || ', ' ||
1166 'l_remaining_quantity = ' || l_remaining_quantity);
1167 END IF;
1168
1169
1170 l_group_id := get_group_id;
1171
1172 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1173 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1174 l_log_module || '.rcv_trx_interface',
1175 'Before rcv_trx_interface_insert_pkg.insert_row');
1176 END IF;
1177
1178 rcv_trx_interface_insert_pkg.insert_row
1179 ( X_Rowid => l_rowid,
1180 X_Interface_Transaction_Id => l_interface_transaction_id,
1181 X_Group_Id => l_group_id,
1182 X_Last_Update_Date => SYSDATE,
1183 X_Last_Updated_By => FND_GLOBAL.USER_ID,
1184 X_Creation_Date => SYSDATE,
1185 X_Created_By => FND_GLOBAL.USER_ID,
1186 X_Last_Update_Login => FND_GLOBAL.LOGIN_ID,
1187 X_Transaction_Type => 'RECEIVE',
1188 X_Transaction_Date => SYSDATE,
1189 X_Processing_Status_Code => 'PENDING',
1190 X_Processing_Mode_Code => 'ONLINE',
1191 X_Processing_Request_Id => NULL,
1192 X_Transaction_Status_Code => 'PENDING',
1193 X_Category_Id => l_ship_line_rec.category_id,
1194 X_Quantity => l_quantity,
1195 X_Unit_Of_Measure => l_ship_line_rec.primary_unit_of_measure,
1196 X_Interface_Source_Code => 'FLM',
1197 X_Interface_Source_Line_Id => NULL,
1198 X_Inv_Transaction_Id => NULL,
1199 X_Item_Id => l_ship_line_rec.item_id,
1200 X_Item_Description => l_ship_line_rec.item_description,
1201 X_Item_Revision => l_ship_line_rec.item_revision,
1202 X_Uom_Code => l_ship_line_rec.uom_code,
1203 X_Employee_Id => l_employee_id,
1204 X_Auto_Transact_Code => 'DELIVER',
1205 X_Shipment_Header_Id => l_ship_line_rec.shipment_header_id,
1206 X_Shipment_Line_Id => l_ship_line_rec.shipment_line_id,
1207 X_Ship_To_Location_Id => l_ship_line_rec.ship_to_location_id,
1208 X_Primary_Quantity => l_quantity,
1209 X_Primary_Unit_Of_Measure => l_ship_line_rec.primary_unit_of_measure,
1210 X_Receipt_Source_Code => l_ship_header_rec.receipt_source_code,
1211 X_Vendor_Id => NULL,
1212 X_Vendor_Site_Id => NULL,
1213 X_From_Organization_Id => l_ship_line_rec.from_organization_id,
1214 X_To_Organization_Id => l_ship_line_rec.to_organization_id,
1215 X_Routing_Header_Id => l_ship_line_rec.routing_header_id,
1216 X_Routing_Step_Id => 1,
1217 X_Source_Document_Code => l_ship_line_rec.source_document_code,
1218 X_Parent_Transaction_Id => NULL,
1219 X_Po_Header_Id => NULL,
1220 X_Po_Revision_Num => NULL,
1221 X_Po_Release_Id => NULL,
1222 X_Po_Line_Id => NULL,
1223 X_Po_Line_Location_Id => NULL,
1224 X_Po_Unit_Price => NULL,
1225 X_Currency_Code => NULL,
1226 X_Currency_Conversion_Type => NULL,
1227 X_Currency_Conversion_Rate => NULL,
1228 X_Currency_Conversion_Date => NULL,
1229 X_Po_Distribution_Id => NULL,
1230 X_Requisition_Line_Id => l_ship_line_rec.requisition_line_id,
1231 X_Req_Distribution_Id => l_ship_line_rec.req_distribution_id,
1232 X_Charge_Account_Id => l_ship_line_rec.charge_account_id,
1233 X_Substitute_Unordered_Code => NULL,
1234 X_Receipt_Exception_Flag => NULL,
1235 X_Accrual_Status_Code => NULL,
1236 X_Inspection_Status_Code => 'NOT INSPECTED',
1237 X_Inspection_Quality_Code => NULL,
1238 X_Destination_Type_Code => l_ship_line_rec.destination_type_code,
1239 X_Deliver_To_Person_Id => l_ship_line_rec.deliver_to_person_id,
1240 X_Location_Id => l_ship_line_rec.deliver_to_location_id,
1241 X_Deliver_To_Location_Id => l_ship_line_rec.deliver_to_location_id,
1242 X_Subinventory => l_ship_line_rec.to_subinventory,
1243 X_Locator_Id => l_ship_line_rec.locator_id,
1244 X_Wip_Entity_Id => NULL,
1245 X_Wip_Line_Id => NULL,
1246 X_Department_Code => NULL,
1247 X_Wip_Repetitive_Schedule_Id => NULL,
1248 X_Wip_Operation_Seq_Num => NULL,
1249 X_Wip_Resource_Seq_Num => NULL,
1250 X_Bom_Resource_Id => NULL,
1251 X_Shipment_Num => l_ship_header_rec.shipment_num,
1252 X_Freight_Carrier_Code => l_ship_header_rec.freight_carrier_code,
1253 X_Bill_Of_Lading => l_ship_header_rec.bill_of_lading,
1254 X_Packing_Slip => l_ship_line_rec.packing_slip,
1255 X_Shipped_Date => l_ship_header_rec.shipped_date,
1256 X_Expected_Receipt_Date => l_ship_header_rec.expected_receipt_date,
1257 X_Actual_Cost => NULL,
1258 X_Transfer_Cost => l_ship_line_rec.transfer_cost,
1259 X_Transportation_Cost => l_ship_line_rec.transportation_cost,
1260 X_Transportation_Account_Id => l_ship_line_rec.transportation_account_id,
1261 X_Num_Of_Containers => l_ship_header_rec.num_of_containers,
1262 X_Waybill_Airbill_Num => l_ship_header_rec.waybill_airbill_num,
1263 X_Vendor_Item_Num => NULL,
1264 X_Vendor_Lot_Num => NULL,
1265 X_Rma_Reference => NULL,
1266 X_Comments => l_ship_line_rec.comments,
1267 X_Attribute_Category => l_ship_line_rec.attribute_category,
1268 X_Attribute1 => l_ship_line_rec.attribute1,
1269 X_Attribute2 => l_ship_line_rec.attribute2,
1270 X_Attribute3 => l_ship_line_rec.attribute3,
1271 X_Attribute4 => l_ship_line_rec.attribute4,
1272 X_Attribute5 => l_ship_line_rec.attribute5,
1273 X_Attribute6 => l_ship_line_rec.attribute6,
1274 X_Attribute7 => l_ship_line_rec.attribute7,
1275 X_Attribute8 => l_ship_line_rec.attribute8,
1276 X_Attribute9 => l_ship_line_rec.attribute9,
1277 X_Attribute10 => l_ship_line_rec.attribute10,
1278 X_Attribute11 => l_ship_line_rec.attribute11,
1279 X_Attribute12 => l_ship_line_rec.attribute12,
1280 X_Attribute13 => l_ship_line_rec.attribute13,
1281 X_Attribute14 => l_ship_line_rec.attribute14,
1282 X_Attribute15 => l_ship_line_rec.attribute15,
1283 X_Ship_Head_Attribute_Category => NULL,
1284 X_Ship_Head_Attribute1 => NULL,
1285 X_Ship_Head_Attribute2 => NULL,
1286 X_Ship_Head_Attribute3 => NULL,
1287 X_Ship_Head_Attribute4 => NULL,
1288 X_Ship_Head_Attribute5 => NULL,
1289 X_Ship_Head_Attribute6 => NULL,
1290 X_Ship_Head_Attribute7 => NULL,
1291 X_Ship_Head_Attribute8 => NULL,
1292 X_Ship_Head_Attribute9 => NULL,
1293 X_Ship_Head_Attribute10 => NULL,
1294 X_Ship_Head_Attribute11 => NULL,
1295 X_Ship_Head_Attribute12 => NULL,
1296 X_Ship_Head_Attribute13 => NULL,
1297 X_Ship_Head_Attribute14 => NULL,
1298 X_Ship_Head_Attribute15 => NULL,
1299 X_Ship_Line_Attribute_Category => NULL,
1300 X_Ship_Line_Attribute1 => NULL,
1301 X_Ship_Line_Attribute2 => NULL,
1302 X_Ship_Line_Attribute3 => NULL,
1303 X_Ship_Line_Attribute4 => NULL,
1304 X_Ship_Line_Attribute5 => NULL,
1305 X_Ship_Line_Attribute6 => NULL,
1306 X_Ship_Line_Attribute7 => NULL,
1307 X_Ship_Line_Attribute8 => NULL,
1308 X_Ship_Line_Attribute9 => NULL,
1309 X_Ship_Line_Attribute10 => NULL,
1310 X_Ship_Line_Attribute11 => NULL,
1311 X_Ship_Line_Attribute12 => NULL,
1312 X_Ship_Line_Attribute13 => NULL,
1313 X_Ship_Line_Attribute14 => NULL,
1314 X_Ship_Line_Attribute15 => NULL,
1315 X_Ussgl_Transaction_Code => l_ship_line_rec.ussgl_transaction_code,
1316 X_Government_Context => l_ship_line_rec.government_context,
1317 X_Reason_Id => l_ship_line_rec.reason_id,
1318 X_Destination_Context => l_ship_line_rec.destination_context,
1319 X_Source_Doc_Quantity => l_quantity,
1320 X_Source_Doc_Unit_Of_Measure => l_ship_line_rec.primary_unit_of_measure,
1321 X_Lot_Number_CC => l_ship_line_rec.lot_control_code,
1322 X_Serial_Number_CC => l_ship_line_rec.serial_number_control_code,
1323 X_QA_Collection_Id => NULL,
1324 X_Country_of_Origin_Code => l_ship_line_rec.country_of_origin_code,
1325 X_oe_order_header_id => NULL,
1326 X_oe_order_line_id => NULL,
1327 X_customer_item_num => NULL,
1328 X_customer_id => NULL,
1329 X_customer_site_id => NULL,
1330 X_put_away_rule_id => NULL,
1331 X_put_away_strategy_id => NULL,
1332 X_lpn_id => NULL,
1333 X_transfer_lpn_id => NULL,
1334 X_cost_group_id => l_ship_line_rec.cost_group_id,
1335 p_org_id => l_ship_line_rec.org_id
1336 );
1337
1338 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1339 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1340 l_log_module || '.rcv_trx_interface',
1341 'After rcv_trx_interface_insert_pkg.insert_row: ' ||
1342 'l_interface_transaction_id = ' || l_interface_transaction_id);
1343 END IF;
1344
1345
1346 COMMIT;
1347
1348 request_online(p_group_id => l_group_id,
1349 x_ret_status => x_ret_status,
1350 x_err_msg => x_err_msg
1351 );
1352
1353 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1354 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1355 l_log_module || '.request_online',
1356 'After request_online: ' ||
1357 'x_ret_status = ' || x_ret_status || ', ' ||
1358 'x_err_msg = ' || x_err_msg);
1359 END IF;
1360
1361 IF x_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
1362 RETURN;
1363 END IF;
1364
1365
1366 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1367 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1368 l_log_module || '.receive_interorg_hook',
1369 'Before FLM_KANBAN_CUSTOM_PKG.receive_interorg_hook');
1370 END IF;
1371
1372 FLM_KANBAN_CUSTOM_PKG.receive_interorg_hook
1373 ( p_kanban_card_id => p_kanban_card_id,
1374 p_shipment_header_id => l_ship_line_rec.shipment_header_id,
1375 p_shipment_line_id => l_ship_line_rec.shipment_line_id,
1376 p_receipt_quantity => p_receipt_quantity,
1377 x_return_status => x_ret_status,
1378 x_msg_data => x_err_msg
1379 );
1380
1381
1382 IF l_remaining_quantity = 0 THEN
1383 -- no more quantity to receive
1384 EXIT;
1385 END IF;
1386
1387 END LOOP;
1388
1389 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1390 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1391 l_log_module || '.end',
1392 'x_receipt_num = ' || x_receipt_num || ', ' ||
1393 'x_ret_status = ' || x_ret_status || ', ' ||
1394 'x_err_msg = ' || x_err_msg);
1395 END IF;
1396
1397 END receive_interorg;
1398
1399
1400 PROCEDURE kanban_receive
1401 ( p_kanban_card_id IN NUMBER,
1402 p_receipt_quantity IN NUMBER,
1403 p_process_flag IN VARCHAR2,
1404 p_receipt_number OUT NOCOPY NUMBER,
1405 x_ret_status OUT NOCOPY VARCHAR2,
1406 x_err_msg OUT NOCOPY VARCHAR2
1407 )
1408 IS
1409
1410 l_api_name CONSTANT VARCHAR2(30) := 'kanban_receive';
1411 l_log_module CONSTANT VARCHAR2(60) := G_LOG_MODULE || l_api_name;
1412
1413 CURSOR c_get_kanban_source_type(p_kanban_card_id IN NUMBER)
1414 IS
1415 SELECT source_type
1416 FROM mtl_kanban_cards
1417 WHERE kanban_card_id = p_kanban_card_id;
1418
1419 l_source_type NUMBER;
1420 l_ret_msg_code NUMBER;
1421
1422 BEGIN
1423
1424 SAVEPOINT kanban_receive;
1425
1426 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1427 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1428 l_log_module || '.begin',
1429 'Parameters: ' ||
1430 'p_kanban_card_id = ' || p_kanban_card_id || ', ' ||
1431 'p_receipt_quantity = ' || p_receipt_quantity || ', ' ||
1432 'p_process_flag = ' || p_process_flag);
1433 END IF;
1434
1435
1436 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1437 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1438 l_log_module || '.validate',
1439 'Calling validate');
1440 END IF;
1441
1442 validate(p_kanban_card_id => p_kanban_card_id,
1443 p_quantity => p_receipt_quantity,
1444 x_ret_status => x_ret_status,
1445 x_err_msg => x_err_msg);
1446
1447 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1448 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1449 l_log_module || '.validate',
1450 'After validate: ' ||
1451 'x_ret_status = ' || x_ret_status || ', ' ||
1452 'x_err_msg = ' || x_err_msg);
1453 END IF;
1454
1455 IF x_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
1456 RETURN;
1457 END IF;
1458
1459 IF p_process_flag = 'Y' THEN
1460
1461 OPEN c_get_kanban_source_type(p_kanban_card_id);
1462 FETCH c_get_kanban_source_type INTO l_source_type;
1463 CLOSE c_get_kanban_source_type;
1464
1465 IF l_source_type = INV_KANBAN_PVT.G_Source_Type_Supplier THEN
1466
1467 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1468 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1469 l_log_module || '.receive_supplier',
1470 'Calling receive_supplier');
1471 END IF;
1472
1473 receive_supplier(p_kanban_card_id,
1474 p_receipt_quantity,
1475 p_receipt_number,
1476 x_ret_status,
1477 x_err_msg);
1478
1479 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1480 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1481 l_log_module || '.receive_supplier',
1482 'After receive_supplier: ' ||
1483 'x_receipt_num = ' || p_receipt_number || ', ' ||
1484 'x_ret_status = ' || x_ret_status || ', ' ||
1485 'x_err_msg = ' || x_err_msg);
1486 END IF;
1487
1488 ELSIF l_source_type = INV_KANBAN_PVT.G_Source_Type_InterOrg THEN
1489
1490 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1491 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1492 l_log_module || '.receive_interorg',
1493 'Calling receive_interorg');
1494 END IF;
1495
1496 receive_interorg(p_kanban_card_id,
1497 p_receipt_quantity,
1498 p_receipt_number,
1499 x_ret_status,
1500 x_err_msg);
1501
1502 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1503 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1504 l_log_module || '.receive_interorg',
1505 'After receive_interorg: ' ||
1506 'x_receipt_num = ' || p_receipt_number || ', ' ||
1507 'x_ret_status = ' || x_ret_status || ', ' ||
1508 'x_err_msg = ' || x_err_msg);
1509 END IF;
1510
1511 END IF; -- l_source_type
1512
1513 END IF; -- p_process_flag
1514
1515 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1516 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1517 l_log_module || '.end',
1518 'p_receipt_number = ' || p_receipt_number || ', ' ||
1519 'x_ret_status = ' || x_ret_status || ', ' ||
1520 'x_err_msg = ' || x_err_msg);
1521 END IF;
1522
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 ROLLBACK TO kanban_receive;
1526 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR;
1527 x_err_msg := SQLERRM;
1528
1529 END kanban_receive;
1530
1531 END FLM_RECEIPT_PUB;