[Home] [Help]
1: PACKAGE BODY INV_MAINTAIN_RESERVATION_PUB AS
2: /* $Header: INVPMRVB.pls 120.43.12020000.6 2013/02/27 04:54:31 tachen ship $*/
3:
4: g_dummy_sn_tbl inv_reservation_global.serial_number_tbl_type;
5:
6: -- CodeReview.SU.03 Define a global variable for l_Debug
7: g_debug Number := Nvl(Fnd_Profile.Value('INV_DEBUG_TRACE'),0);
8:
216: p_init_msg_lst in varchar2,
217: x_return_status out nocopy varchar2,
218: x_msg_count out nocopy number,
219: x_msg_data out nocopy varchar2,
220: p_mtl_maintain_rsv_rec in inv_reservation_global.mtl_maintain_rsv_rec_type,
221: p_delete_flag in varchar2,
222: p_sort_by_criteria in number,
223: x_quantity_modified out nocopy number ) is
224:
228: c_module_name constant varchar2(2000) := 'inv.plsql.inv_maintain_reservations.reduce_reservation';
229: c_debug_enabled constant number := 1 ;
230: c_action_supply constant number := 0;
231: c_action_demand constant number := 1;
232: c_cancel_order_no constant number := inv_reservation_global.g_cancel_order_no ;
233:
234: -- l_debug number := nvl(fnd_profile.value('inv_debug_trace'), 0);
235: l_fnd_log_message varchar2(2000);
236: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
232: c_cancel_order_no constant number := inv_reservation_global.g_cancel_order_no ;
233:
234: -- l_debug number := nvl(fnd_profile.value('inv_debug_trace'), 0);
235: l_fnd_log_message varchar2(2000);
236: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
237: l_query_input inv_reservation_global.mtl_reservation_rec_type;
238: l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type ;
239: l_mtl_reservation_tbl_count number ;
240: l_sort_by_criteria number ;
233:
234: -- l_debug number := nvl(fnd_profile.value('inv_debug_trace'), 0);
235: l_fnd_log_message varchar2(2000);
236: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
237: l_query_input inv_reservation_global.mtl_reservation_rec_type;
238: l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type ;
239: l_mtl_reservation_tbl_count number ;
240: l_sort_by_criteria number ;
241: l_error_code number ;
234: -- l_debug number := nvl(fnd_profile.value('inv_debug_trace'), 0);
235: l_fnd_log_message varchar2(2000);
236: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
237: l_query_input inv_reservation_global.mtl_reservation_rec_type;
238: l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type ;
239: l_mtl_reservation_tbl_count number ;
240: l_sort_by_criteria number ;
241: l_error_code number ;
242:
241: l_error_code number ;
242:
243: -- following variables for calling transfer reservation api
244: l_is_transfer_supply varchar2(1);
245: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
246: -- convert_rsv_rec is used to convert expected_qty to primary_expected_uom_code
247: l_convert_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
248: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
249: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
243: -- following variables for calling transfer reservation api
244: l_is_transfer_supply varchar2(1);
245: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
246: -- convert_rsv_rec is used to convert expected_qty to primary_expected_uom_code
247: l_convert_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
248: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
249: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
250: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
251: l_reservation_id number ;
244: l_is_transfer_supply varchar2(1);
245: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
246: -- convert_rsv_rec is used to convert expected_qty to primary_expected_uom_code
247: l_convert_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
248: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
249: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
250: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
251: l_reservation_id number ;
252: l_primary_uom_code varchar2(10);
245: l_original_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
246: -- convert_rsv_rec is used to convert expected_qty to primary_expected_uom_code
247: l_convert_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
248: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
249: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
250: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
251: l_reservation_id number ;
252: l_primary_uom_code varchar2(10);
253: l_primary_expected_qty number ;
246: -- convert_rsv_rec is used to convert expected_qty to primary_expected_uom_code
247: l_convert_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
248: l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
249: l_original_serial_number inv_reservation_global.serial_number_tbl_type;
250: l_to_serial_number inv_reservation_global.serial_number_tbl_type;
251: l_reservation_id number ;
252: l_primary_uom_code varchar2(10);
253: l_primary_expected_qty number ;
254: l_to_primary_transaction_qty number;
326: elsif (p_mtl_maintain_rsv_rec.action = c_action_demand) then
327: l_source_header_id := p_mtl_maintain_rsv_rec.demand_source_header_id;
328: end if;
329:
330: if (p_mtl_maintain_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_wip) then
331: if (g_debug= c_debug_enabled) then
332: mydebug ('before calling get_wip_entity_type' , c_api_name, 9);
333: end if;
334: inv_reservation_pvt.get_wip_entity_type
366: if upper(nvl(p_delete_flag,'N')) = 'N' then
367: -- codereview.su.06. swap action types for sorting criteria
368: if p_mtl_maintain_rsv_rec.action = c_action_demand then
369: if p_sort_by_criteria is null then
370: l_sort_by_criteria := inv_reservation_global.g_query_supply_rcpt_date_asc;
371: else
372: l_sort_by_criteria := p_sort_by_criteria ;
373: end if;
374: elsif p_mtl_maintain_rsv_rec.action = c_action_supply then
372: l_sort_by_criteria := p_sort_by_criteria ;
373: end if;
374: elsif p_mtl_maintain_rsv_rec.action = c_action_supply then
375: if p_sort_by_criteria is null then
376: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
377: else
378: l_sort_by_criteria := p_sort_by_criteria ;
379: end if;
380: end if;
409: -- supply source header is required column
410: -- codereview.su.07. source_header_id is required for following source types
411: -- po, wip, int req, ext req, asn, intrasit
412: -- not required for inventory source type
413: if (p_mtl_maintain_rsv_rec.supply_source_type_id <> inv_reservation_global.g_source_type_inv) then
414: check_reqd_param (
415: p_param_value => p_mtl_maintain_rsv_rec.supply_source_header_id,
416: p_param_name => 'p_mtl_maintain_rsv_rec.supply_source_header',
417: p_api_name => c_api_name );
420: -- suppply source line is required column
421: -- codereview.su.07. source_line_id is required for following source types
422: -- po, int req, ext req, asn, intrasit
423: -- not required for inventory,wip
424: if (p_mtl_maintain_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_wip
425: and l_wip_entity_type_id in (inv_reservation_global.g_wip_source_type_batch
426: ,inv_reservation_global.g_wip_source_type_cmro)) then
427: check_reqd_param (
428: p_param_value => p_mtl_maintain_rsv_rec.supply_source_line_id,
421: -- codereview.su.07. source_line_id is required for following source types
422: -- po, int req, ext req, asn, intrasit
423: -- not required for inventory,wip
424: if (p_mtl_maintain_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_wip
425: and l_wip_entity_type_id in (inv_reservation_global.g_wip_source_type_batch
426: ,inv_reservation_global.g_wip_source_type_cmro)) then
427: check_reqd_param (
428: p_param_value => p_mtl_maintain_rsv_rec.supply_source_line_id,
429: p_param_name => 'p_mtl_maintain_rsv_rec.supply_source_line_id',
422: -- po, int req, ext req, asn, intrasit
423: -- not required for inventory,wip
424: if (p_mtl_maintain_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_wip
425: and l_wip_entity_type_id in (inv_reservation_global.g_wip_source_type_batch
426: ,inv_reservation_global.g_wip_source_type_cmro)) then
427: check_reqd_param (
428: p_param_value => p_mtl_maintain_rsv_rec.supply_source_line_id,
429: p_param_name => 'p_mtl_maintain_rsv_rec.supply_source_line_id',
430: p_api_name => c_api_name );
429: p_param_name => 'p_mtl_maintain_rsv_rec.supply_source_line_id',
430: p_api_name => c_api_name );
431: elsif (p_mtl_maintain_rsv_rec.supply_source_type_id
432: not in
433: (inv_reservation_global.g_source_type_inv,
434: inv_reservation_global.g_source_type_wip,
435: inv_reservation_global.g_source_type_account,
436: inv_reservation_global.g_source_type_account_alias)) then
437: check_reqd_param (
430: p_api_name => c_api_name );
431: elsif (p_mtl_maintain_rsv_rec.supply_source_type_id
432: not in
433: (inv_reservation_global.g_source_type_inv,
434: inv_reservation_global.g_source_type_wip,
435: inv_reservation_global.g_source_type_account,
436: inv_reservation_global.g_source_type_account_alias)) then
437: check_reqd_param (
438: p_param_value => p_mtl_maintain_rsv_rec.supply_source_line_id,
431: elsif (p_mtl_maintain_rsv_rec.supply_source_type_id
432: not in
433: (inv_reservation_global.g_source_type_inv,
434: inv_reservation_global.g_source_type_wip,
435: inv_reservation_global.g_source_type_account,
436: inv_reservation_global.g_source_type_account_alias)) then
437: check_reqd_param (
438: p_param_value => p_mtl_maintain_rsv_rec.supply_source_line_id,
439: p_param_name => 'p_mtl_maintain_rsv_rec.supply_source_line_id',
432: not in
433: (inv_reservation_global.g_source_type_inv,
434: inv_reservation_global.g_source_type_wip,
435: inv_reservation_global.g_source_type_account,
436: inv_reservation_global.g_source_type_account_alias)) then
437: check_reqd_param (
438: p_param_value => p_mtl_maintain_rsv_rec.supply_source_line_id,
439: p_param_name => 'p_mtl_maintain_rsv_rec.supply_source_line_id',
440: p_api_name => c_api_name );
443:
444: -- suppply source line detail required column
445: -- codereview.su.07. source_line_detail is required for following source types
446: -- asn
447: if p_mtl_maintain_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_asn then
448: check_reqd_param (
449: p_param_value => p_mtl_maintain_rsv_rec.supply_source_line_detail,
450: p_param_name => 'p_mtl_maintain_rsv_rec.supply_source_line_detail',
451: p_api_name => c_api_name );
472: p_api_name => c_api_name );
473:
474: -- demand source header is required column
475: -- codereview.su.07. source_header_id is not required for inv source types
476: if p_mtl_maintain_rsv_rec.demand_source_type_id <> inv_reservation_global.g_source_type_inv then
477: check_reqd_param (
478: p_param_value => p_mtl_maintain_rsv_rec.demand_source_header_id,
479: p_param_name => 'p_mtl_maintain_rsv_rec.demand_source_header',
480: p_api_name => c_api_name );
481: end if; -- check source header id
482:
483: -- demandy source line is required column
484: -- codereview.su.07. demand_source_line_id is not required for inv source types
485: if (p_mtl_maintain_rsv_rec.demand_source_type_id = inv_reservation_global.g_source_type_wip
486: and l_wip_entity_type_id in (inv_reservation_global.g_wip_source_type_cmro,
487: inv_reservation_global.g_wip_source_type_batch)) then
488: check_reqd_param (
489: p_param_value => p_mtl_maintain_rsv_rec.demand_source_line_id,
482:
483: -- demandy source line is required column
484: -- codereview.su.07. demand_source_line_id is not required for inv source types
485: if (p_mtl_maintain_rsv_rec.demand_source_type_id = inv_reservation_global.g_source_type_wip
486: and l_wip_entity_type_id in (inv_reservation_global.g_wip_source_type_cmro,
487: inv_reservation_global.g_wip_source_type_batch)) then
488: check_reqd_param (
489: p_param_value => p_mtl_maintain_rsv_rec.demand_source_line_id,
490: p_param_name => 'p_mtl_maintain_rsv_rec.demand_source_line_id',
483: -- demandy source line is required column
484: -- codereview.su.07. demand_source_line_id is not required for inv source types
485: if (p_mtl_maintain_rsv_rec.demand_source_type_id = inv_reservation_global.g_source_type_wip
486: and l_wip_entity_type_id in (inv_reservation_global.g_wip_source_type_cmro,
487: inv_reservation_global.g_wip_source_type_batch)) then
488: check_reqd_param (
489: p_param_value => p_mtl_maintain_rsv_rec.demand_source_line_id,
490: p_param_name => 'p_mtl_maintain_rsv_rec.demand_source_line_id',
491: p_api_name => c_api_name );
489: p_param_value => p_mtl_maintain_rsv_rec.demand_source_line_id,
490: p_param_name => 'p_mtl_maintain_rsv_rec.demand_source_line_id',
491: p_api_name => c_api_name );
492: elsif (p_mtl_maintain_rsv_rec.demand_source_type_id not in
493: (inv_reservation_global.g_source_type_inv,inv_reservation_global.g_source_type_account,
494: inv_reservation_global.g_source_type_account_alias)) then
495: check_reqd_param (
496: p_param_value => p_mtl_maintain_rsv_rec.demand_source_line_id,
497: p_param_name => 'p_mtl_maintain_rsv_rec.demand_source_line_id',
490: p_param_name => 'p_mtl_maintain_rsv_rec.demand_source_line_id',
491: p_api_name => c_api_name );
492: elsif (p_mtl_maintain_rsv_rec.demand_source_type_id not in
493: (inv_reservation_global.g_source_type_inv,inv_reservation_global.g_source_type_account,
494: inv_reservation_global.g_source_type_account_alias)) then
495: check_reqd_param (
496: p_param_value => p_mtl_maintain_rsv_rec.demand_source_line_id,
497: p_param_name => 'p_mtl_maintain_rsv_rec.demand_source_line_id',
498: p_api_name => c_api_name );
638:
639: l_staged_rec_exists := 'N' ;
640: for i in 1..l_mtl_reservation_tbl_count loop
641: if l_mtl_reservation_tbl(i).supply_source_type_id
642: = inv_reservation_global.g_source_type_inv
643: and nvl(l_mtl_reservation_tbl(i).staged_flag, 'N') = 'Y' then
644: l_staged_rec_exists := 'Y' ;
645: if (g_debug= c_debug_enabled) then
646: mydebug ('Staged reservation exists' ,c_api_name,1);
665: -- codereview.su.10 move it to begining of loop and assign record itself
666: l_original_rsv_rec := l_mtl_reservation_tbl(i) ;
667: if ( l_mtl_reservation_tbl(i).orig_supply_source_type_id
668: <> l_mtl_reservation_tbl(i).supply_source_type_id )
669: and l_mtl_reservation_tbl(i).supply_source_type_id = inv_reservation_global.g_source_type_asn then
670: l_original_rsv_rec.reservation_id :=
671: l_mtl_reservation_tbl(i).reservation_id ;
672: if (g_debug= c_debug_enabled) then
673: mydebug ('ASN reservation. Need to transfer to the
673: mydebug ('ASN reservation. Need to transfer to the
674: original supply' ,c_api_name,1);
675: end if;
676: if p_mtl_maintain_rsv_rec.action = c_action_supply then
677: l_to_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_po ;
678: l_to_rsv_rec.supply_source_header_id := l_original_rsv_rec.supply_source_header_id;
679: l_to_rsv_rec.supply_source_line_id := l_original_rsv_rec.supply_source_line_id;
680: --codereview.su.11 assign supply_source_line_detail value too
681: l_to_rsv_rec.supply_source_line_detail := l_original_rsv_rec.supply_source_line_detail;
682: l_is_transfer_supply := fnd_api.g_true ;
683: -- codereview.su.12 comment out else statement. not required
684: /**************************
685: else
686: l_to_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_po ;
687: l_to_rsv_rec.demand_source_header_id := l_original_rsv_rec.demand_source_header_id;
688: l_to_rsv_rec.demand_source_line_id := l_original_rsv_rec.demand_source_line_id;
689: l_to_rsv_rec.demand_source_delivery := null;
690: l_is_transfer_supply := fnd_api.g_false;
987:
988: for i in 1..l_mtl_reservation_tbl_count loop
989: -- dbms_output.put_line('looping through reservation records');
990: if l_mtl_reservation_tbl(i).supply_source_type_id
991: = inv_reservation_global.g_source_type_inv
992: and nvl(l_mtl_reservation_tbl(i).staged_flag, 'N') = 'Y' then
993: -- skip record
994: -- dbms_output.put_line('skipping record');
995: null;
1278: end reduce_reservation;
1279: --this procedure initializes the reservation record - only done when a new requisition reservation
1280: --is created
1281: --
1282: procedure build_res_rec (x_reservation_rec out nocopy inv_reservation_global.mtl_reservation_rec_type)
1283: is
1284:
1285: -- define constants for api version and api name
1286: c_api_version_number constant number := 1.0;
1288: c_module_name constant varchar2(2000) := 'inv.plsql.inv_maintain_reservation_pub';
1289: c_debug_enabled constant number := 1 ;
1290: l_fnd_log_message varchar2(2000);
1291:
1292: l_rsv inv_reservation_global.mtl_reservation_rec_type;
1293:
1294: begin
1295: --this procedure initializes the mtl_reservation record
1296: l_fnd_log_message := 'begining of procedure :';
1308: l_rsv.requirement_date := null;
1309: l_rsv.organization_id := null; -- org id
1310: l_rsv.inventory_item_id := null; -- item id
1311: l_rsv.demand_source_type_id :=
1312: inv_reservation_global.g_source_type_oe; -- order entry
1313: l_rsv.demand_source_name := null;
1314: l_rsv.demand_source_header_id := null; -- oe order number
1315: l_rsv.demand_source_line_id := null; -- oe order line number
1316: l_rsv.demand_source_delivery := null;
1330: l_rsv.autodetail_group_id := null;
1331: l_rsv.external_source_code := null;
1332: l_rsv.external_source_line_id := null;
1333: l_rsv.supply_source_type_id :=
1334: inv_reservation_global.g_source_type_req;
1335: l_rsv.supply_source_header_id := null; -- po req header id
1336: l_rsv.supply_source_line_id := null; -- po req line id
1337: l_rsv.supply_source_name := null;
1338: l_rsv.supply_source_line_detail := null;
1433: --l_debug number := nvl(fnd_profile.value('inv_debug_trace'), 0);
1434: l_fnd_log_message varchar2(2000);
1435: l_msg_index_out number;
1436:
1437: l_rsv inv_reservation_global.mtl_reservation_rec_type;
1438: l_return_status varchar2(1);
1439: --l_msg_count number := 0;
1440: --l_msg_data varchar2(2000);
1441: l_qty number;
1482: mydebug(l_fnd_log_message,c_api_name,9);
1483: end if;
1484:
1485: select decode (h.source_document_type_id, 10,
1486: inv_reservation_global.g_source_type_internal_ord,
1487: inv_reservation_global.g_source_type_oe )
1488: into l_demand_source_type_id
1489: from oe_order_headers_all h, oe_order_lines_all l
1490: where h.header_id = l.header_id
1483: end if;
1484:
1485: select decode (h.source_document_type_id, 10,
1486: inv_reservation_global.g_source_type_internal_ord,
1487: inv_reservation_global.g_source_type_oe )
1488: into l_demand_source_type_id
1489: from oe_order_headers_all h, oe_order_lines_all l
1490: where h.header_id = l.header_id
1491: and l.line_id = p_demand_source_line_id;
1692: ,p_supply_source_line_id IN NUMBER DEFAULT NULL
1693: ,p_supply_source_type_id IN NUMBER
1694: ,p_project_id IN NUMBER
1695: ,p_task_id IN NUMBER
1696: ,x_rsv_array OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
1697: ,x_record_count OUT NOCOPY NUMBER
1698: ,x_msg_count OUT NOCOPY NUMBER
1699: ,x_msg_data OUT NOCOPY VARCHAR2
1700: ,x_return_status OUT NOCOPY VARCHAR2)
1708: --l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1709: l_Fnd_Log_Message VARCHAR2(2000);
1710: l_Msg_Index_Out Number;
1711:
1712: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1713: --l_msg_count NUMBER;
1714: --l_msg_data VARCHAR2(240);
1715: l_rsv_id NUMBER;
1716: l_return_status VARCHAR2(1);
1714: --l_msg_data VARCHAR2(240);
1715: l_rsv_id NUMBER;
1716: l_return_status VARCHAR2(1);
1717: l_error_code NUMBER;
1718: l_rsv_array inv_reservation_global.mtl_reservation_tbl_type;
1719: l_record_count NUMBER;
1720: l_error_text VARCHAR2(2000);
1721: BEGIN
1722: l_Fnd_Log_message := 'Begining of procedure :';
1745: FND_MESSAGE.SET_NAME('INV','INV_API_NULL_SOURCE_TYPE_ID');
1746: FND_MSG_PUB.Add;
1747: RAISE fnd_api.g_exc_unexpected_error;
1748: ELSIF p_supply_source_type_id NOT IN
1749: (inv_reservation_global.g_source_type_po,
1750: inv_reservation_global.g_source_type_internal_req,
1751: inv_reservation_global.g_source_type_req) THEN
1752: FND_MESSAGE.SET_NAME('INV','INV_API_INVALID_SOURCE_TYPE_ID');
1753: FND_MSG_PUB.Add;
1746: FND_MSG_PUB.Add;
1747: RAISE fnd_api.g_exc_unexpected_error;
1748: ELSIF p_supply_source_type_id NOT IN
1749: (inv_reservation_global.g_source_type_po,
1750: inv_reservation_global.g_source_type_internal_req,
1751: inv_reservation_global.g_source_type_req) THEN
1752: FND_MESSAGE.SET_NAME('INV','INV_API_INVALID_SOURCE_TYPE_ID');
1753: FND_MSG_PUB.Add;
1754: RAISE fnd_api.g_exc_unexpected_error;
1747: RAISE fnd_api.g_exc_unexpected_error;
1748: ELSIF p_supply_source_type_id NOT IN
1749: (inv_reservation_global.g_source_type_po,
1750: inv_reservation_global.g_source_type_internal_req,
1751: inv_reservation_global.g_source_type_req) THEN
1752: FND_MESSAGE.SET_NAME('INV','INV_API_INVALID_SOURCE_TYPE_ID');
1753: FND_MSG_PUB.Add;
1754: RAISE fnd_api.g_exc_unexpected_error;
1755: END IF;
1772: , x_msg_count => x_msg_count
1773: , x_msg_data => x_msg_data
1774: , p_query_input => l_rsv_rec
1775: , p_lock_records => fnd_api.g_false
1776: , p_sort_by_req_date => inv_reservation_global.g_query_demand_ship_date_asc
1777: , p_cancel_order_mode => inv_reservation_global.g_cancel_order_no
1778: , x_mtl_reservation_tbl => l_rsv_array
1779: , x_mtl_reservation_tbl_count => l_record_count
1780: , x_error_code => l_error_code
1773: , x_msg_data => x_msg_data
1774: , p_query_input => l_rsv_rec
1775: , p_lock_records => fnd_api.g_false
1776: , p_sort_by_req_date => inv_reservation_global.g_query_demand_ship_date_asc
1777: , p_cancel_order_mode => inv_reservation_global.g_cancel_order_no
1778: , x_mtl_reservation_tbl => l_rsv_array
1779: , x_mtl_reservation_tbl_count => l_record_count
1780: , x_error_code => l_error_code
1781: );
1961: -- this procedure checks whether a reservation exists
1962: --
1963: FUNCTION EXISTS_RESERVATION( p_supply_source_header_id IN NUMBER DEFAULT NULL
1964: , p_supply_source_line_id IN NUMBER DEFAULT NULL
1965: , p_supply_source_type_id IN NUMBER DEFAULT inv_reservation_global.g_source_type_po)
1966:
1967: RETURN BOOLEAN IS
1968: -- Define Constants for API version and API name
1969: C_api_version_number CONSTANT NUMBER := 1.0;
1974: --l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1975: l_Fnd_Log_Message VARCHAR2(2000);
1976: l_Msg_Index_Out Number;
1977:
1978: l_rsv_array inv_reservation_global.mtl_reservation_tbl_type;
1979: l_record_count NUMBER;
1980: l_msg_count NUMBER;
1981: l_msg_data VARCHAR2(2000);
1982: l_return_status VARCHAR2(1);
2068: (
2069: p_req_line_id IN NUMBER
2070: ,p_project_id IN NUMBER
2071: ,p_task_id IN NUMBER
2072: ,x_res_array OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
2073: ,x_record_count OUT NOCOPY NUMBER
2074: )
2075: IS
2076: -- Define Constants for API version and API name
2118:
2119:
2120: QUERY_RES
2121: (p_supply_source_line_id => p_req_line_id
2122: ,p_supply_source_type_id => inv_reservation_global.g_source_type_req
2123: ,p_project_id => p_project_id
2124: ,p_task_id => p_task_id
2125: ,x_rsv_array => x_res_array
2126: ,x_record_count => l_record_count
2194: --l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2195: l_Fnd_Log_Message VARCHAR2(2000);
2196: l_Msg_Index_Out Number;
2197:
2198: l_rsv inv_reservation_global.mtl_reservation_rec_type;
2199: l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
2200: --l_msg_count NUMBER;
2201: --l_msg_data VARCHAR2(240);
2202: l_rsv_id NUMBER;
2195: l_Fnd_Log_Message VARCHAR2(2000);
2196: l_Msg_Index_Out Number;
2197:
2198: l_rsv inv_reservation_global.mtl_reservation_rec_type;
2199: l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
2200: --l_msg_count NUMBER;
2201: --l_msg_data VARCHAR2(240);
2202: l_rsv_id NUMBER;
2203: l_return_status VARCHAR2(1);
2449: --l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2450: l_Fnd_Log_Message VARCHAR2(2000);
2451: l_Msg_Index_Out Number;
2452:
2453: l_rsv inv_reservation_global.mtl_reservation_rec_type;
2454: l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
2455: --l_msg_count NUMBER;
2456: --l_msg_data VARCHAR2(240);
2457: l_rsv_id NUMBER;
2450: l_Fnd_Log_Message VARCHAR2(2000);
2451: l_Msg_Index_Out Number;
2452:
2453: l_rsv inv_reservation_global.mtl_reservation_rec_type;
2454: l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
2455: --l_msg_count NUMBER;
2456: --l_msg_data VARCHAR2(240);
2457: l_rsv_id NUMBER;
2458: l_return_status VARCHAR2(1);
2756: --l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2757: l_Fnd_Log_Message VARCHAR2(2000);
2758: l_Msg_Index_Out Number;
2759:
2760: l_rsv inv_reservation_global.mtl_reservation_rec_type;
2761: --l_msg_count NUMBER;
2762: --l_msg_data VARCHAR2(240);
2763: l_rsv_id NUMBER;
2764: --l_dummy_sn inv_reservation_global.serial_number_tbl_type;
2760: l_rsv inv_reservation_global.mtl_reservation_rec_type;
2761: --l_msg_count NUMBER;
2762: --l_msg_data VARCHAR2(240);
2763: l_rsv_id NUMBER;
2764: --l_dummy_sn inv_reservation_global.serial_number_tbl_type;
2765: l_return_status VARCHAR2(1);
2766: BEGIN
2767: l_Fnd_Log_message := 'Begining of procedure :';
2768: -- Dbms_output.Put_line('Begining Of Procedure');
3261: SELECT sum(nvl(primary_reservation_quantity,0)) sum_pri_res_qty, primary_uom_code
3262: FROM mtl_reservations
3263: WHERE demand_source_header_id = v_demand_header_id
3264: AND demand_source_line_id = v_demand_line_id
3265: AND demand_source_type_id in (inv_reservation_global.g_source_type_oe,
3266: inv_reservation_global.g_source_type_internal_ord)
3267: GROUP BY primary_uom_code;
3268: -- AND supply_source_type_id = inv_reservation_global.g_source_type_inv;
3269: -- comment out last restriction supply_source_type_id = inv_reservation_global.g_source_type_inv
3262: FROM mtl_reservations
3263: WHERE demand_source_header_id = v_demand_header_id
3264: AND demand_source_line_id = v_demand_line_id
3265: AND demand_source_type_id in (inv_reservation_global.g_source_type_oe,
3266: inv_reservation_global.g_source_type_internal_ord)
3267: GROUP BY primary_uom_code;
3268: -- AND supply_source_type_id = inv_reservation_global.g_source_type_inv;
3269: -- comment out last restriction supply_source_type_id = inv_reservation_global.g_source_type_inv
3270: -- since starting from r12, we will start to open manual reservations for WIP job....
3264: AND demand_source_line_id = v_demand_line_id
3265: AND demand_source_type_id in (inv_reservation_global.g_source_type_oe,
3266: inv_reservation_global.g_source_type_internal_ord)
3267: GROUP BY primary_uom_code;
3268: -- AND supply_source_type_id = inv_reservation_global.g_source_type_inv;
3269: -- comment out last restriction supply_source_type_id = inv_reservation_global.g_source_type_inv
3270: -- since starting from r12, we will start to open manual reservations for WIP job....
3271:
3272:
3265: AND demand_source_type_id in (inv_reservation_global.g_source_type_oe,
3266: inv_reservation_global.g_source_type_internal_ord)
3267: GROUP BY primary_uom_code;
3268: -- AND supply_source_type_id = inv_reservation_global.g_source_type_inv;
3269: -- comment out last restriction supply_source_type_id = inv_reservation_global.g_source_type_inv
3270: -- since starting from r12, we will start to open manual reservations for WIP job....
3271:
3272:
3273: /* Bug# 3085721, This Cursor will check if the reservation is already
3278: SELECT 'Exists'
3279: FROM mtl_reservations
3280: WHERE supply_source_header_id = v_requisition_header_id
3281: AND supply_source_line_id = v_requisition_line_id
3282: AND Supply_source_type_id=inv_reservation_global.g_source_type_req;
3283:
3284: CURSOR get_po_shipment(v_po_header_id IN NUMBER) IS
3285: SELECT pll.po_header_id
3286: ,pll.po_line_id
3330: , sum(nvl(primary_reservation_quantity,0)) primary_reservation_quantity
3331: FROM mtl_reservations
3332: WHERE supply_source_header_id = v_po_header_id
3333: AND supply_source_line_id = v_po_line_location_id
3334: AND supply_source_type_id = inv_reservation_global.g_source_type_po
3335: AND Nvl(project_id,-99) = nvl(v_project_id, -99)
3336: AND Nvl(task_id,-99) = nvl(v_task_id, -99)
3337: GROUP BY reservation_uom_code
3338: , primary_uom_code;
3583: l_temp_res_qty NUMBER;
3584: l_lead_time NUMBER; -- Bug# 2984835.
3585: l_res_exists VARCHAR2(10); -- Bug# 3085721.
3586:
3587: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
3588: l_rsv_array inv_reservation_global.mtl_reservation_tbl_type;
3589:
3590: l_record_count NUMBER;
3591: l_qty_avail_to_reserve NUMBER;
3584: l_lead_time NUMBER; -- Bug# 2984835.
3585: l_res_exists VARCHAR2(10); -- Bug# 3085721.
3586:
3587: l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
3588: l_rsv_array inv_reservation_global.mtl_reservation_tbl_type;
3589:
3590: l_record_count NUMBER;
3591: l_qty_avail_to_reserve NUMBER;
3592: l_qty_avail NUMBER;
3618: get_req_line_po_shipment_rec get_req_line_of_po_shipment%ROWTYPE;
3619: get_pt_count_po_shipment_rec get_pt_count_po_shipment%ROWTYPE;
3620: get_pt_po_shipment_rec get_proj_task_of_po_shipment%ROWTYPE;
3621:
3622: l_mtl_maint_rsv_rec inv_reservation_global.mtl_maintain_rsv_rec_type;
3623:
3624: get_po_res_qty_rec get_po_res_qty%ROWTYPE;
3625: get_po_shipment_rel_rec get_po_shipment_for_release%ROWTYPE;
3626: get_req_hdr_lines_rec get_req_hdr_lines%ROWTYPE;
3630:
3631: get_rcv_transaction_rec get_rcv_transaction%ROWTYPE;
3632: get_rcv_transaction_asn_rec get_rcv_transaction_asn%ROWTYPE;
3633: get_rcv_txn_int_req_rec get_rcv_txn_int_req%ROWTYPE;
3634: p_mtl_maintain_rsv_rec inv_reservation_global.mtl_maintain_rsv_rec_type;
3635: get_source_doc_code_rec get_source_doc_code%ROWTYPE;
3636: l_delete_flag VARCHAR2(1);
3637: l_sort_by_criteria Number;
3638: --l_transaction_id NUMBER;
3866: mydebug(l_Fnd_Log_Message, c_api_name,9);
3867: END IF;
3868: -- Get the source_type_code to determine the requistion type
3869: IF Upper(interface_record_rec.source_type_code) = 'INVENTORY' THEN
3870: l_supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
3871: else
3872: l_supply_source_type_id := inv_reservation_global.g_source_type_req;
3873: END IF;
3874:
3868: -- Get the source_type_code to determine the requistion type
3869: IF Upper(interface_record_rec.source_type_code) = 'INVENTORY' THEN
3870: l_supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
3871: else
3872: l_supply_source_type_id := inv_reservation_global.g_source_type_req;
3873: END IF;
3874:
3875: /* Bug# 8524455, changed interface_record_rec.uom_code to
3876: sales_order_record.order_quantity_uom below */
3997: , x_msg_count =>x_msg_count --OUT NOCOPY NUMBER
3998: , x_msg_data =>x_msg_data --OUT NOCOPY VARCHAR2
3999: , p_organization_id =>get_po_shipment_rec.ship_to_organization_id--IN NUMBER default null
4000: , p_item_id =>get_po_shipment_rec.item_id --IN NUMBER default null
4001: , p_supply_source_type_id =>inv_reservation_global.g_source_type_po --IN NUMBER
4002: , p_supply_source_header_id =>p_header_id --IN NUMBER
4003: , p_supply_source_line_id =>get_po_shipment_rec.line_location_id --IN NUMBER
4004: , p_project_id =>get_req_line_po_shipment_rec.project_id--IN NUMBER default null
4005: , p_task_id =>get_req_line_po_shipment_rec.task_id --IN NUMBER default null
4057:
4058: update_res
4059: (p_supply_source_header_id => l_rsv_rec.supply_source_header_id
4060: ,p_supply_source_line_id => l_rsv_rec.supply_source_line_id
4061: ,p_supply_source_type_id => inv_reservation_global.g_source_type_req
4062: ,p_primary_reservation_quantity => l_qty_avail_to_reserve
4063: ,p_project_id => get_req_line_po_shipment_rec.project_id
4064: ,p_task_id => get_req_line_po_shipment_rec.task_id
4065: ,p_reservation_id => l_rsv_rec.reservation_id
4088: TRANSFER_RES
4089: (p_from_reservation_id =>l_rsv_rec.reservation_id
4090: ,p_from_source_header_id =>l_rsv_rec.supply_source_header_id
4091: ,p_from_source_line_id =>l_rsv_rec.supply_source_line_id
4092: ,p_supply_source_type_id =>inv_reservation_global.g_source_type_req
4093: ,p_to_source_header_id =>p_header_id
4094: ,p_to_source_line_id =>get_po_shipment_rec.line_location_id
4095: ,p_to_supply_source_type_id =>inv_reservation_global.g_source_type_po
4096: ,p_primary_uom_code =>l_rsv_rec.primary_uom_code
4091: ,p_from_source_line_id =>l_rsv_rec.supply_source_line_id
4092: ,p_supply_source_type_id =>inv_reservation_global.g_source_type_req
4093: ,p_to_source_header_id =>p_header_id
4094: ,p_to_source_line_id =>get_po_shipment_rec.line_location_id
4095: ,p_to_supply_source_type_id =>inv_reservation_global.g_source_type_po
4096: ,p_primary_uom_code =>l_rsv_rec.primary_uom_code
4097: ,p_primary_res_quantity =>l_primary_res_quantity
4098: ,x_msg_count => x_msg_count
4099: ,x_msg_data => x_msg_data
4180: TRANSFER_RES
4181: (p_from_reservation_id =>l_rsv_rec.reservation_id
4182: ,p_from_source_header_id =>l_rsv_rec.supply_source_header_id
4183: ,p_from_source_line_id =>l_rsv_rec.supply_source_line_id
4184: ,p_supply_source_type_id =>inv_reservation_global.g_source_type_req
4185: ,p_to_source_header_id =>p_header_id
4186: ,p_to_source_line_id =>get_po_shipment_rec.line_location_id
4187: ,p_to_supply_source_type_id =>inv_reservation_global.g_source_type_po
4188: ,p_primary_uom_code =>l_rsv_rec.primary_uom_code
4183: ,p_from_source_line_id =>l_rsv_rec.supply_source_line_id
4184: ,p_supply_source_type_id =>inv_reservation_global.g_source_type_req
4185: ,p_to_source_header_id =>p_header_id
4186: ,p_to_source_line_id =>get_po_shipment_rec.line_location_id
4187: ,p_to_supply_source_type_id =>inv_reservation_global.g_source_type_po
4188: ,p_primary_uom_code =>l_rsv_rec.primary_uom_code
4189: ,p_primary_res_quantity =>l_rsv_rec.primary_reservation_quantity
4190: ,x_msg_count =>x_msg_count
4191: ,x_msg_data =>x_msg_data
4235: -- delete_flag to yes. delete all reservations for that
4236: -- supply line.
4237: -- calling reduce_reservation API
4238: l_delete_flag := 'Y';
4239: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
4240: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
4241: l_mtl_maint_rsv_rec.organization_id := get_po_shipment_rec.ship_to_organization_id;
4242: l_mtl_maint_rsv_rec.inventory_item_id := get_po_shipment_rec.item_id;
4243: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
4239: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
4240: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
4241: l_mtl_maint_rsv_rec.organization_id := get_po_shipment_rec.ship_to_organization_id;
4242: l_mtl_maint_rsv_rec.inventory_item_id := get_po_shipment_rec.item_id;
4243: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
4244: l_mtl_maint_rsv_rec.supply_source_header_id := p_header_id;
4245: l_mtl_maint_rsv_rec.supply_source_line_id := get_po_shipment_rec.line_location_id;
4246: --l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
4247:
4278: /***** Call reduce reservations instead of delete reservations
4279: DELETE_RES
4280: (p_supply_source_header_id => p_header_id
4281: ,p_supply_source_line_id => get_po_shipment_rec.line_location_id
4282: ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
4283: ,x_msg_count => x_msg_count
4284: ,x_msg_data => x_msg_data
4285: ,x_return_status => l_return_status);
4286: IF g_debug= C_Debug_Enabled THEN
4320: , x_msg_count => x_msg_count
4321: , x_msg_data => x_msg_data
4322: , p_organization_id => get_po_shipment_rec.ship_to_organization_id
4323: , p_item_id => get_po_shipment_rec.item_id
4324: , p_supply_source_type_id => inv_reservation_global.g_source_type_po
4325: , p_supply_source_header_id => p_header_id
4326: , p_supply_source_line_id => get_po_shipment_rec.line_location_id
4327: , p_project_id => get_pt_po_shipment_rec.project_id
4328: , p_task_id => get_pt_po_shipment_rec.task_id
4357:
4358: IF (get_po_res_qty_rec.primary_reservation_quantity > l_qty_avail) THEN
4359: -- calling reduce_reservation API
4360: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
4361: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
4362: l_mtl_maint_rsv_rec.organization_id := get_po_shipment_rec.ship_to_organization_id;
4363: l_mtl_maint_rsv_rec.inventory_item_id := get_po_shipment_rec.item_id;
4364: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
4365: l_mtl_maint_rsv_rec.supply_source_header_id := p_header_id;
4360: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
4361: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
4362: l_mtl_maint_rsv_rec.organization_id := get_po_shipment_rec.ship_to_organization_id;
4363: l_mtl_maint_rsv_rec.inventory_item_id := get_po_shipment_rec.item_id;
4364: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
4365: l_mtl_maint_rsv_rec.supply_source_header_id := p_header_id;
4366: l_mtl_maint_rsv_rec.supply_source_line_id := get_po_shipment_rec.line_location_id;
4367: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
4368: --l_mtl_maint_rsv_rec.expected_quantity := get_po_res_qty_rec.primary_reservation_quantity - l_qty_avail_to_reserve;
4601: l_Fnd_Log_Message := 'p_supply_source_header_id :' || get_rcv_transaction_rec.po_header_id;
4602: mydebug(l_Fnd_Log_Message, c_api_name,9);
4603: l_Fnd_Log_Message := 'p_supply_source_line_id : '|| get_rcv_transaction_rec.po_line_location_id;
4604: mydebug(l_Fnd_Log_Message, c_api_name,9);
4605: l_Fnd_Log_Message := 'p_supply_source_type_id : '|| inv_reservation_global.g_source_type_po;
4606: mydebug(l_Fnd_Log_Message, c_api_name,9);
4607: l_Fnd_Log_Message := 'p_project_id : '|| get_rcv_transaction_rec.project_id;
4608: mydebug(l_Fnd_Log_Message, c_api_name,9);
4609: l_Fnd_Log_Message := 'p_task_id: '||get_rcv_transaction_rec.task_id;
4610: mydebug(l_Fnd_Log_Message, c_api_name,9);
4611: END IF;
4612: query_res(p_supply_source_header_id => get_rcv_transaction_rec.po_header_id
4613: ,p_supply_source_line_id => get_rcv_transaction_rec.po_line_location_id
4614: ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
4615: ,p_project_id => get_rcv_transaction_rec.project_id
4616: ,p_task_id => get_rcv_transaction_rec.task_id
4617: ,x_rsv_array => l_rsv_array
4618: ,x_record_count => l_record_count
4672: TRANSFER_RES
4673: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
4674: ,p_from_source_header_id => get_rcv_transaction_rec.po_header_id
4675: ,p_from_source_line_id => get_rcv_transaction_rec.po_line_location_id
4676: ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
4677: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
4678: ,p_subinventory_code => get_rcv_transaction_rec.subinventory
4679: ,p_locator_id => get_rcv_transaction_rec.locator_id
4680: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
4673: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
4674: ,p_from_source_header_id => get_rcv_transaction_rec.po_header_id
4675: ,p_from_source_line_id => get_rcv_transaction_rec.po_line_location_id
4676: ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
4677: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
4678: ,p_subinventory_code => get_rcv_transaction_rec.subinventory
4679: ,p_locator_id => get_rcv_transaction_rec.locator_id
4680: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
4681: ,p_revision => get_rcv_transaction_rec.item_revision
4722: TRANSFER_RES
4723: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
4724: ,p_from_source_header_id => get_rcv_transaction_rec.po_header_id
4725: ,p_from_source_line_id => get_rcv_transaction_rec.po_line_location_id
4726: ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
4727: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
4728: ,p_subinventory_code => get_rcv_transaction_rec.subinventory
4729: ,p_locator_id => get_rcv_transaction_rec.locator_id
4730: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
4723: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
4724: ,p_from_source_header_id => get_rcv_transaction_rec.po_header_id
4725: ,p_from_source_line_id => get_rcv_transaction_rec.po_line_location_id
4726: ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
4727: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
4728: ,p_subinventory_code => get_rcv_transaction_rec.subinventory
4729: ,p_locator_id => get_rcv_transaction_rec.locator_id
4730: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
4731: ,p_revision => get_rcv_transaction_rec.item_revision
4917:
4918: query_res
4919: (p_supply_source_header_id => get_rcv_transaction_asn_rec.po_header_id
4920: ,p_supply_source_line_id => get_rcv_transaction_asn_rec.po_line_location_id
4921: ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
4922: ,p_project_id => get_rcv_transaction_asn_rec.project_id
4923: ,p_task_id => get_rcv_transaction_asn_rec.task_id
4924: ,x_rsv_array => l_rsv_array
4925: ,x_record_count => l_record_count
4983: TRANSFER_RES
4984: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
4985: , p_from_source_header_id => get_rcv_transaction_asn_rec.po_header_id
4986: ,p_from_source_line_id => get_rcv_transaction_asn_rec.po_line_location_id
4987: ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
4988: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
4989: ,p_subinventory_code => get_rcv_transaction_rec.subinventory
4990: ,p_locator_id => get_rcv_transaction_rec.locator_id
4991: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
4984: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
4985: , p_from_source_header_id => get_rcv_transaction_asn_rec.po_header_id
4986: ,p_from_source_line_id => get_rcv_transaction_asn_rec.po_line_location_id
4987: ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
4988: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
4989: ,p_subinventory_code => get_rcv_transaction_rec.subinventory
4990: ,p_locator_id => get_rcv_transaction_rec.locator_id
4991: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
4992: ,p_revision => get_rcv_transaction_asn_rec.item_revision
5038: TRANSFER_RES
5039: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
5040: ,p_from_source_header_id => get_rcv_transaction_asn_rec.po_header_id
5041: ,p_from_source_line_id => get_rcv_transaction_asn_rec.po_line_location_id
5042: ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
5043: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
5044: ,p_subinventory_code => get_rcv_transaction_asn_rec.subinventory
5045: ,p_locator_id => get_rcv_transaction_asn_rec.locator_id
5046: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
5039: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
5040: ,p_from_source_header_id => get_rcv_transaction_asn_rec.po_header_id
5041: ,p_from_source_line_id => get_rcv_transaction_asn_rec.po_line_location_id
5042: ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
5043: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
5044: ,p_subinventory_code => get_rcv_transaction_asn_rec.subinventory
5045: ,p_locator_id => get_rcv_transaction_asn_rec.locator_id
5046: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
5047: ,p_revision => get_rcv_transaction_asn_rec.item_revision
5257:
5258: query_res
5259: (p_supply_source_header_id => get_rcv_txn_int_req_rec.requisition_header_id
5260: ,p_supply_source_line_id => get_rcv_txn_int_req_rec.requisition_line_id
5261: ,p_supply_source_type_id => inv_reservation_global.g_source_type_internal_req
5262: ,p_project_id => get_rcv_txn_int_req_rec.project_id
5263: ,p_task_id => get_rcv_txn_int_req_rec.task_id
5264: ,x_rsv_array => l_rsv_array
5265: ,x_record_count => l_record_count
5310: TRANSFER_RES
5311: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
5312: ,p_from_source_header_id => get_rcv_txn_int_req_rec.requisition_header_id
5313: ,p_from_source_line_id => get_rcv_txn_int_req_rec.requisition_line_id
5314: ,p_supply_source_type_id => inv_reservation_global.g_source_type_internal_req
5315: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
5316: ,p_subinventory_code => get_rcv_txn_int_req_rec.subinventory
5317: ,p_locator_id => get_rcv_txn_int_req_rec.locator_id
5318: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
5311: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
5312: ,p_from_source_header_id => get_rcv_txn_int_req_rec.requisition_header_id
5313: ,p_from_source_line_id => get_rcv_txn_int_req_rec.requisition_line_id
5314: ,p_supply_source_type_id => inv_reservation_global.g_source_type_internal_req
5315: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
5316: ,p_subinventory_code => get_rcv_txn_int_req_rec.subinventory
5317: ,p_locator_id => get_rcv_txn_int_req_rec.locator_id
5318: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
5319: ,p_revision => get_rcv_txn_int_req_rec.item_revision
5332: TRANSFER_RES
5333: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
5334: ,p_from_source_header_id => get_rcv_txn_int_req_rec.requisition_header_id
5335: ,p_from_source_line_id => get_rcv_txn_int_req_rec.requisition_line_id
5336: ,p_supply_source_type_id => inv_reservation_global.g_source_type_internal_req
5337: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
5338: ,p_subinventory_code => get_rcv_txn_int_req_rec.subinventory
5339: ,p_locator_id => get_rcv_txn_int_req_rec.locator_id
5340: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
5333: (p_from_reservation_id => l_rsv_array(l_record_index).reservation_id
5334: ,p_from_source_header_id => get_rcv_txn_int_req_rec.requisition_header_id
5335: ,p_from_source_line_id => get_rcv_txn_int_req_rec.requisition_line_id
5336: ,p_supply_source_type_id => inv_reservation_global.g_source_type_internal_req
5337: ,p_to_supply_source_type_id => inv_reservation_global.g_source_type_inv
5338: ,p_subinventory_code => get_rcv_txn_int_req_rec.subinventory
5339: ,p_locator_id => get_rcv_txn_int_req_rec.locator_id
5340: ,p_lot_number => NVL(get_rcv_lot_number_rec.lot_num, NULL)
5341: ,p_revision => get_rcv_txn_int_req_rec.item_revision
5464: , x_msg_count =>x_msg_count
5465: , x_msg_data =>x_msg_data
5466: , p_organization_id =>get_po_shipment_rel_rec.ship_to_organization_id
5467: , p_item_id =>get_po_shipment_rel_rec.item_id
5468: , p_supply_source_type_id =>inv_reservation_global.g_source_type_po
5469: , p_supply_source_header_id =>get_po_shipment_rel_rec.po_header_id
5470: , p_supply_source_line_id =>get_po_shipment_rel_rec.line_location_id
5471: , p_project_id =>get_req_line_po_shipment_rec.project_id
5472: , p_task_id =>get_req_line_po_shipment_rec.task_id
5506: IF (l_rsv_rec.primary_reservation_quantity > l_qty_avail_to_reserve) THEN
5507: update_res
5508: (p_supply_source_header_id => l_rsv_rec.supply_source_header_id
5509: ,p_supply_source_line_id => l_rsv_rec.supply_source_line_id
5510: ,p_supply_source_type_id => inv_reservation_global.g_source_type_req
5511: ,p_primary_reservation_quantity => l_qty_avail_to_reserve
5512: ,p_project_id => get_req_line_po_shipment_rec.project_id
5513: ,p_task_id => get_req_line_po_shipment_rec.task_id
5514: ,p_reservation_id => l_rsv_rec.reservation_id
5535: TRANSFER_RES
5536: (p_from_reservation_id =>l_rsv_rec.reservation_id
5537: ,p_from_source_header_id =>l_rsv_rec.supply_source_header_id
5538: ,p_from_source_line_id =>l_rsv_rec.supply_source_line_id
5539: ,p_supply_source_type_id =>inv_reservation_global.g_source_type_req
5540: ,p_to_source_header_id =>get_po_shipment_rel_rec.po_header_id
5541: ,p_to_source_line_id =>get_po_shipment_rel_rec.line_location_id
5542: ,p_to_supply_source_type_id =>inv_reservation_global.g_source_type_po
5543: ,p_primary_uom_code =>l_rsv_rec.primary_uom_code
5538: ,p_from_source_line_id =>l_rsv_rec.supply_source_line_id
5539: ,p_supply_source_type_id =>inv_reservation_global.g_source_type_req
5540: ,p_to_source_header_id =>get_po_shipment_rel_rec.po_header_id
5541: ,p_to_source_line_id =>get_po_shipment_rel_rec.line_location_id
5542: ,p_to_supply_source_type_id =>inv_reservation_global.g_source_type_po
5543: ,p_primary_uom_code =>l_rsv_rec.primary_uom_code
5544: ,p_primary_res_quantity =>l_primary_res_quantity
5545: ,x_msg_count =>x_msg_count
5546: ,x_msg_data =>x_msg_data
5596: -- reduce reservations instead
5597: -- DELETE_RES
5598: -- (p_supply_source_header_id => get_po_shipment_rel_rec.po_header_id
5599: -- ,p_supply_source_line_id => get_po_shipment_rel_rec.line_location_id
5600: --,p_supply_source_type_id => inv_reservation_global.g_source_type_po
5601: -- ,x_msg_count => x_msg_count
5602: -- ,x_msg_data => x_msg_data
5603: -- ,x_return_status => l_return_status);
5604: -- Call reduce reservations instead of delete
5607: -- delete_flag to yes. delete all reservations for that
5608: -- supply line.
5609: -- calling reduce_reservation API
5610: l_delete_flag := 'Y';
5611: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
5612: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
5613: l_mtl_maint_rsv_rec.organization_id := get_po_shipment_rel_rec.ship_to_organization_id;
5614: l_mtl_maint_rsv_rec.inventory_item_id := get_po_shipment_rel_rec.item_id;
5615: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
5611: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
5612: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
5613: l_mtl_maint_rsv_rec.organization_id := get_po_shipment_rel_rec.ship_to_organization_id;
5614: l_mtl_maint_rsv_rec.inventory_item_id := get_po_shipment_rel_rec.item_id;
5615: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
5616: l_mtl_maint_rsv_rec.supply_source_header_id := get_po_shipment_rel_rec.po_header_id;
5617: l_mtl_maint_rsv_rec.supply_source_line_id := get_po_shipment_rel_rec.line_location_id;
5618: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
5619:
5679: , x_msg_count => x_msg_count --OUT NOCOPY NUMBER
5680: , x_msg_data => x_msg_data --OUT NOCOPY VARCHAR2
5681: , p_organization_id => get_po_shipment_rel_rec.ship_to_organization_id--IN NUMBER default null
5682: , p_item_id => get_po_shipment_rel_rec.item_id--IN NUMBER default null
5683: , p_supply_source_type_id => inv_reservation_global.g_source_type_po --IN NUMBER
5684: , p_supply_source_header_id => get_po_shipment_rel_rec.po_header_id --IN NUMBER ?? how do we query for blank PO
5685: , p_supply_source_line_id => get_po_shipment_rel_rec.line_location_id --IN NUMBER
5686: , p_project_id => get_pt_po_shipment_rec.project_id--IN NUMBER default null
5687: , p_task_id => get_pt_po_shipment_rec.task_id --IN NUMBER default null
5712: ,l_po_primary_qty);*/
5713: IF (get_po_res_qty_rec.primary_reservation_quantity > l_qty_avail) THEN
5714: -- calling reduce_reservation API get_pt_po_shipment_rec
5715: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
5716: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
5717: l_mtl_maint_rsv_rec.organization_id
5718: := get_po_shipment_rel_rec.ship_to_organization_id;
5719: l_mtl_maint_rsv_rec.inventory_item_id := get_po_shipment_rel_rec.item_id;
5720:
5717: l_mtl_maint_rsv_rec.organization_id
5718: := get_po_shipment_rel_rec.ship_to_organization_id;
5719: l_mtl_maint_rsv_rec.inventory_item_id := get_po_shipment_rel_rec.item_id;
5720:
5721: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
5722: l_mtl_maint_rsv_rec.supply_source_header_id := get_po_shipment_rel_rec.po_header_id;
5723: l_mtl_maint_rsv_rec.supply_source_line_id := get_po_shipment_rel_rec.line_location_id;
5724: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
5725: --l_mtl_maint_rsv_rec.expected_quantity :=
5785: mydebug ('Removing req supply for req line :'|| get_req_hdr_lines_rec.requisition_line_id , c_api_name,9);
5786: END IF;
5787:
5788: IF Upper(get_req_hdr_lines_rec.source_type_code) = 'INVENTORY' THEN
5789: l_supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
5790: else
5791: l_supply_source_type_id := inv_reservation_global.g_source_type_req;
5792: END IF;
5793:
5787:
5788: IF Upper(get_req_hdr_lines_rec.source_type_code) = 'INVENTORY' THEN
5789: l_supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
5790: else
5791: l_supply_source_type_id := inv_reservation_global.g_source_type_req;
5792: END IF;
5793:
5794: IF EXISTS_RESERVATION(p_supply_source_header_id => p_header_id,
5795: p_supply_source_line_id => get_req_hdr_lines_rec.requisition_line_id,
5823: END;
5824:
5825:
5826: IF Upper(l_source_type_code) = 'INVENTORY' THEN
5827: l_supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
5828: else
5829: l_supply_source_type_id := inv_reservation_global.g_source_type_req;
5830: END IF;
5831:
5825:
5826: IF Upper(l_source_type_code) = 'INVENTORY' THEN
5827: l_supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
5828: else
5829: l_supply_source_type_id := inv_reservation_global.g_source_type_req;
5830: END IF;
5831:
5832: IF EXISTS_RESERVATION(p_supply_source_line_id => p_line_id,
5833: p_supply_source_type_id => l_supply_source_type_id) THEN
5857: EXIT WHEN get_po_shipment%NOTFOUND;
5858:
5859: --DELETE_RES (p_supply_source_header_id => p_header_id
5860: -- ,p_supply_source_line_id => get_po_shipment_rec.line_location_id
5861: -- ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
5862: -- ,x_msg_count => x_msg_count
5863: -- ,x_msg_data => x_msg_data
5864: -- ,x_return_status => l_return_status);
5865: IF g_debug= C_Debug_Enabled THEN
5865: IF g_debug= C_Debug_Enabled THEN
5866: mydebug ('Removing po line location. po line loc.:'|| get_po_shipment_rec.line_location_id,c_api_name,9);
5867: END IF;
5868: l_delete_flag := 'Y';
5869: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
5870: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
5871: l_mtl_maint_rsv_rec.organization_id :=
5872: get_po_shipment_rec.ship_to_organization_id;
5873: l_mtl_maint_rsv_rec.inventory_item_id :=
5871: l_mtl_maint_rsv_rec.organization_id :=
5872: get_po_shipment_rec.ship_to_organization_id;
5873: l_mtl_maint_rsv_rec.inventory_item_id :=
5874: get_po_shipment_rec.item_id;
5875: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
5876: l_mtl_maint_rsv_rec.supply_source_header_id := p_header_id;
5877: l_mtl_maint_rsv_rec.supply_source_line_id :=
5878: get_po_shipment_rec.line_location_id;
5879: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
5933: EXIT WHEN get_line_loc_for_po_line%NOTFOUND;
5934:
5935: -- DELETE_RES (p_supply_source_header_id => l_po_header_id
5936: -- ,p_supply_source_line_id => get_distr_rec.line_location_id
5937: -- ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
5938: -- ,x_msg_count => x_msg_count
5939: -- ,x_msg_data => x_msg_data
5940: -- ,x_return_status => l_return_status);
5941: IF g_debug= C_Debug_Enabled THEN
5942: mydebug ('Cancel line.Removing po line location. po line loc.:'|| get_line_loc_rec.line_location_id,c_api_name,9);
5943: END IF;
5944:
5945: l_delete_flag := 'Y';
5946: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
5947: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
5948: l_mtl_maint_rsv_rec.organization_id :=
5949: get_line_loc_rec.ship_to_organization_id;
5950: l_mtl_maint_rsv_rec.inventory_item_id :=
5948: l_mtl_maint_rsv_rec.organization_id :=
5949: get_line_loc_rec.ship_to_organization_id;
5950: l_mtl_maint_rsv_rec.inventory_item_id :=
5951: get_line_loc_rec.item_id;
5952: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
5953: l_mtl_maint_rsv_rec.supply_source_header_id := l_po_header_id;
5954: l_mtl_maint_rsv_rec.supply_source_line_id :=
5955: get_line_loc_rec.line_location_id;
5956: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
6009: -- EXIT WHEN get_distr_for_po_shipment%NOTFOUND;
6010: --
6011: -- DELETE_RES (p_supply_source_header_id => l_po_header_id
6012: -- ,p_supply_source_line_id => get_distr_rec.line_location_id
6013: -- ,p_supply_source_type_id => inv_reservation_global.g_source_type_po--
6014: -- ,x_msg_count => x_msg_count
6015: ---- ,x_msg_data => x_msg_data
6016: -- ,x_return_status => l_return_status);
6017: --
6021: FETCH get_line_loc_for_po_shipment INTO get_line_loc_rec;
6022: CLOSE get_line_loc_for_po_shipment;
6023:
6024: l_delete_flag := 'Y';
6025: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
6026: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
6027: l_mtl_maint_rsv_rec.organization_id := get_line_loc_rec.ship_to_organization_id;
6028: l_mtl_maint_rsv_rec.inventory_item_id := get_line_loc_rec.item_id;
6029: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
6025: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
6026: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
6027: l_mtl_maint_rsv_rec.organization_id := get_line_loc_rec.ship_to_organization_id;
6028: l_mtl_maint_rsv_rec.inventory_item_id := get_line_loc_rec.item_id;
6029: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
6030: l_mtl_maint_rsv_rec.supply_source_header_id := get_line_loc_rec.po_header_id;
6031: l_mtl_maint_rsv_rec.supply_source_line_id := p_line_location_id;
6032: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
6033:
6086: -- EXIT WHEN get_distr_for_po_release%NOTFOUND;
6087:
6088: -- DELETE_RES ( p_supply_source_header_id => l_po_header_id
6089: -- ,p_supply_source_line_id => get_distr_rec.line_location_id
6090: -- ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
6091: -- ,x_msg_count => x_msg_count
6092: -- ,x_msg_data => x_msg_data
6093: -- ,x_return_status => l_return_status);
6094:
6102: EXIT WHEN get_po_shipment_for_release%NOTFOUND;
6103:
6104: --DELETE_RES (p_supply_source_header_id => p_header_id
6105: -- ,p_supply_source_line_id => get_po_shipment_rec.line_location_id
6106: -- ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
6107: -- ,x_msg_count => x_msg_count
6108: -- ,x_msg_data => x_msg_data
6109: -- ,x_return_status => l_return_status);
6110: IF g_debug= C_Debug_Enabled THEN
6111: mydebug ('Removing po line location. po line loc.:'|| get_po_shipment_rel_rec.line_location_id,c_api_name,9);
6112: END IF;
6113:
6114: l_delete_flag := 'Y';
6115: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
6116: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
6117: --bug8578392 change get_po_shipment_rec.ship_to_organization_id to get_po_shipment_rel_rec.ship_to_organization_id
6118: l_mtl_maint_rsv_rec.organization_id := get_po_shipment_rel_rec.ship_to_organization_id;
6119: --bug8578392 change get_po_shipment_rec.item_id to get_po_shipment_rel_rec.item_id
6117: --bug8578392 change get_po_shipment_rec.ship_to_organization_id to get_po_shipment_rel_rec.ship_to_organization_id
6118: l_mtl_maint_rsv_rec.organization_id := get_po_shipment_rel_rec.ship_to_organization_id;
6119: --bug8578392 change get_po_shipment_rec.item_id to get_po_shipment_rel_rec.item_id
6120: l_mtl_maint_rsv_rec.inventory_item_id := get_po_shipment_rel_rec.item_id;
6121: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
6122: l_mtl_maint_rsv_rec.supply_source_header_id := l_po_header_id;
6123: --bug8578392 change get_po_shipment_rec.line_location_id to get_po_shipment_rel_rec.line_location_id
6124: l_mtl_maint_rsv_rec.supply_source_line_id := get_po_shipment_rel_rec.line_location_id;
6125: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
6180: -- EXIT WHEN get_distr_for_po_shipment%NOTFOUND;
6181:
6182: -- DELETE_RES (p_supply_source_header_id => l_po_header_id
6183: -- ,p_supply_source_line_id => get_distr_rec.line_location_id
6184: -- ,p_supply_source_type_id => inv_reservation_global.g_source_type_po
6185: -- ,x_msg_count => x_msg_count
6186: -- ,x_msg_data => x_msg_data
6187: -- ,x_return_status => l_return_status);
6188: --
6192: FETCH get_line_loc_for_po_shipment INTO get_line_loc_rec;
6193: CLOSE get_line_loc_for_po_shipment;
6194:
6195: l_delete_flag := 'Y';
6196: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
6197: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
6198: l_mtl_maint_rsv_rec.organization_id :=
6199: get_line_loc_rec.ship_to_organization_id;
6200: l_mtl_maint_rsv_rec.inventory_item_id :=
6198: l_mtl_maint_rsv_rec.organization_id :=
6199: get_line_loc_rec.ship_to_organization_id;
6200: l_mtl_maint_rsv_rec.inventory_item_id :=
6201: get_line_loc_rec.item_id;
6202: l_mtl_maint_rsv_rec.supply_source_type_id:= inv_reservation_global.g_source_type_po;
6203: l_mtl_maint_rsv_rec.supply_source_header_id := get_line_loc_rec.po_header_id;
6204: l_mtl_maint_rsv_rec.supply_source_line_id := p_line_location_id;
6205: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
6206:
6251: BEGIN
6252: SELECT Nvl(SUM(primary_reservation_quantity),0) INTO
6253: l_primary_res_qty FROM mtl_reservations
6254: WHERE supply_source_type_id =
6255: inv_reservation_global.g_source_type_internal_req AND
6256: supply_source_header_id = p_header_id AND
6257: supply_source_line_id = p_line_id;
6258: EXCEPTION
6259: WHEN no_data_found THEN
6282: IF (p_ordered_quantity = 0) THEN
6283: -- delete all reservations for that req line
6284: -- calling reduce_reservation API
6285: l_delete_flag := 'Y';
6286: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
6287: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
6288: l_mtl_maint_rsv_rec.organization_id := l_organization_id;
6289: l_mtl_maint_rsv_rec.inventory_item_id := l_inventory_item_id;
6290: --bug #5498904 populated l_mtl_maint_rsv_rec.supply_source_type_id
6287: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
6288: l_mtl_maint_rsv_rec.organization_id := l_organization_id;
6289: l_mtl_maint_rsv_rec.inventory_item_id := l_inventory_item_id;
6290: --bug #5498904 populated l_mtl_maint_rsv_rec.supply_source_type_id
6291: l_mtl_maint_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
6292: l_supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
6293: l_mtl_maint_rsv_rec.supply_source_header_id := p_header_id;
6294: l_mtl_maint_rsv_rec.supply_source_line_id := p_line_id;
6295: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
6288: l_mtl_maint_rsv_rec.organization_id := l_organization_id;
6289: l_mtl_maint_rsv_rec.inventory_item_id := l_inventory_item_id;
6290: --bug #5498904 populated l_mtl_maint_rsv_rec.supply_source_type_id
6291: l_mtl_maint_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
6292: l_supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
6293: l_mtl_maint_rsv_rec.supply_source_header_id := p_header_id;
6294: l_mtl_maint_rsv_rec.supply_source_line_id := p_line_id;
6295: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
6296:
6331: -- reduce the reservations to the ordered qty
6332: -- Set the source type as internal req
6333:
6334: l_mtl_maint_rsv_rec.action := 0;--supply is reduced
6335: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
6336: l_mtl_maint_rsv_rec.organization_id := l_organization_id;
6337: l_mtl_maint_rsv_rec.inventory_item_id := l_inventory_item_id;
6338: --bug #5498904 populated l_mtl_maint_rsv_rec.supply_source_type_id
6339: l_mtl_maint_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
6335: l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
6336: l_mtl_maint_rsv_rec.organization_id := l_organization_id;
6337: l_mtl_maint_rsv_rec.inventory_item_id := l_inventory_item_id;
6338: --bug #5498904 populated l_mtl_maint_rsv_rec.supply_source_type_id
6339: l_mtl_maint_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
6340: l_supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
6341: l_mtl_maint_rsv_rec.supply_source_header_id := p_header_id;
6342: l_mtl_maint_rsv_rec.supply_source_line_id := p_line_id;
6343: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
6336: l_mtl_maint_rsv_rec.organization_id := l_organization_id;
6337: l_mtl_maint_rsv_rec.inventory_item_id := l_inventory_item_id;
6338: --bug #5498904 populated l_mtl_maint_rsv_rec.supply_source_type_id
6339: l_mtl_maint_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
6340: l_supply_source_type_id := inv_reservation_global.g_source_type_internal_req;
6341: l_mtl_maint_rsv_rec.supply_source_header_id := p_header_id;
6342: l_mtl_maint_rsv_rec.supply_source_line_id := p_line_id;
6343: -- l_mtl_maint_rsv_rec.supply_source_line_detail := NULL;
6344: l_mtl_maint_rsv_rec.expected_quantity := p_ordered_quantity;