CREATE OR REPLACE PROCEDURE APPS.CLC_MTL_RESERVATIONS_PRG (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_org_code VARCHAR2
)
AS
l_rsv_old inv_reservation_global.mtl_reservation_rec_type;
l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (4000);
l_rsv_id NUMBER;
l_dummy_sn inv_reservation_global.serial_number_tbl_type;
l_status VARCHAR2 (1);
l_quantity_reserved NUMBER;
l_cnt number :=0;
l_err number :=0;
v_err_msg varchar2(4000);
cursor c_resv (l_org_code number)is
select ooha.order_number,oola.header_id, oola.line_id, msi.segment1,--mr.RESERVATION_ID,mr.ORGANIZATION_ID--,wdd.RELEASED_STATUS
mr.reservation_id
, mr.requirement_date
, mr.organization_id
, mr.inventory_item_id
, mr.demand_source_type_id
, mr.demand_source_name
, mr.demand_source_header_id
, mr.demand_source_line_id
, mr.demand_source_delivery
, mr.primary_uom_code
, mr.primary_uom_id
, mr.secondary_uom_code
, mr.secondary_uom_id
, mr.reservation_uom_code
, mr.reservation_uom_id
, mr.reservation_quantity
, mr.primary_reservation_quantity
, mr.secondary_reservation_quantity
, mr.detailed_quantity
, mr.secondary_detailed_quantity
, mr.autodetail_group_id
, mr.external_source_code
, mr.external_source_line_id
, mr.supply_source_type_id
, mr.supply_source_header_id
, mr.supply_source_line_id
, mr.supply_source_name
, mr.supply_source_line_detail
, mr.revision
, mr.subinventory_code
, mr.subinventory_id
, mr.locator_id
, mr.lot_number
, mr.lot_number_id
, mr.pick_slip_number
, mr.lpn_id
, mr.attribute_category
, mr.attribute1
, mr.attribute2
, mr.attribute3
, mr.attribute4
, mr.attribute5
, mr.attribute6
, mr.attribute7
, mr.attribute8
, mr.attribute9
, mr.attribute10
, mr.attribute11
, mr.attribute12
, mr.attribute13
, mr.attribute14
, mr.attribute15
, mr.ship_ready_flag
, mr.staged_flag
, mr.crossdock_flag
, mr.crossdock_criteria_id
, mr.demand_source_line_detail
, mr.serial_reservation_quantity
, mr.supply_receipt_date
, mr.demand_ship_date
, mr.project_id
, mr.task_id
, mr.orig_supply_source_type_id
, mr.orig_supply_source_header_id
, mr.orig_supply_source_line_id
, mr.orig_supply_source_line_detail
, mr.orig_demand_source_type_id
, mr.orig_demand_source_header_id
, mr.orig_demand_source_line_id
, mr.orig_demand_source_line_detail
, mr.serial_number
from MTL_RESERVATIONS mr,
oe_order_lines_all oola,
oe_order_headers_all ooha,
mtl_system_items_b msi
--wsh_delivery_details wdd
where
ooha.header_id =oola.header_id
and ooha.org_id =oola.org_id
and oola.line_id = mr.DEMAND_SOURCE_LINE_ID
and mr.INVENTORY_ITEM_ID =msi.INVENTORY_ITEM_ID
and mr.ORGANIZATION_ID = msi.ORGANIZATION_ID
--and wdd.SOURCE_HEADER_ID = ooha.header_id
--and wdd.SOURCE_LINE_ID = oola.line_id
and mr.DEMAND_SOURCE_TYPE_ID=2
and mr.ORGANIZATION_ID =l_org_code
and mr.LOCATOR_ID is not null
and not exists (select 'A' from MTL_RESERVATIONS_TEMP mrt where mrt.RESERVATION_ID= mr.RESERVATION_ID and mrt.ORGANIZATION_ID = mr.ORGANIZATION_ID)
--and mr.RESERVATION_ID =1859071
--and mr.INVENTORY_ITEM_ID=32299
/*and (wdd.RELEASED_STATUS ='R'
or not exists (select 'A' from wsh_delivery_assignments wda, wsh_new_deliveries wnd
where wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id));*/
/*and wdd.RELEASED_STATUS ='R'
and not exists (select 'A' from wsh_delivery_assignments wda, wsh_new_deliveries wnd
where wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id);*/
and mr.SHIP_READY_FLAG is null;
/*select oola.header_id, oola.line_id, mr.RESERVATION_ID
from MTL_RESERVATIONS mr,
oe_order_lines_all oola
where
oola.line_id = mr.DEMAND_SOURCE_LINE_ID
and mr.DEMAND_SOURCE_TYPE_ID=2
and ORGANIZATION_ID =l_org_code
--and mr.INVENTORY_ITEM_ID=32299
and not exists (select 'A' from wsh_delivery_details wdd
where wdd.SOURCE_HEADER_ID =oola.HEADER_ID and
wdd.SOURCE_LINE_ID=oola.line_id);*/
BEGIN
--fnd_global.apps_initialize (10688,50606,401);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'Initializing Apps');
FND_GLOBAL.Apps_Initialize (FND_GLOBAL.USER_ID,
FND_GLOBAL.RESP_ID,
FND_GLOBAL.RESP_APPL_ID);
--- Could be "located" by order_header_id, order_line_id, item and warehouse - in case Several reservations That Could be updated
fnd_file.put_line (fnd_file.LOG, 'p_org_code-' ||p_org_code);
l_cnt :=0;
l_err :=0;
FOR l_resv IN c_resv (p_org_code)
LOOP
--l_rsv_old.reservation_id := l_resv.reservation_id;
l_rsv_old.reservation_id := l_resv.reservation_id;
l_rsv_old.requirement_date := l_resv.requirement_date;
l_rsv_old.organization_id := l_resv.organization_id;
l_rsv_old.inventory_item_id := l_resv.inventory_item_id;
l_rsv_old.demand_source_type_id := l_resv.demand_source_type_id;
l_rsv_old.demand_source_name := l_resv.demand_source_name;
l_rsv_old.demand_source_header_id := l_resv.demand_source_header_id;
l_rsv_old.demand_source_line_id := l_resv.demand_source_line_id;
l_rsv_old.demand_source_delivery := l_resv.demand_source_delivery;
l_rsv_old.primary_uom_code := l_resv.primary_uom_code;
l_rsv_old.primary_uom_id := l_resv.primary_uom_id;
l_rsv_old.secondary_uom_code := l_resv.secondary_uom_code;
l_rsv_old.secondary_uom_id := l_resv.secondary_uom_id;
l_rsv_old.reservation_uom_code := l_resv.reservation_uom_code;
l_rsv_old.reservation_uom_id := l_resv.reservation_uom_id;
l_rsv_old.reservation_quantity := l_resv.reservation_quantity;
l_rsv_old.primary_reservation_quantity := l_resv.primary_reservation_quantity;
l_rsv_old.secondary_reservation_quantity := l_resv.secondary_reservation_quantity;
l_rsv_old.detailed_quantity := l_resv.detailed_quantity;
l_rsv_old.secondary_detailed_quantity := l_resv.secondary_detailed_quantity;
l_rsv_old.autodetail_group_id := l_resv.autodetail_group_id;
l_rsv_old.external_source_code := l_resv.external_source_code;
l_rsv_old.external_source_line_id := l_resv.external_source_line_id;
l_rsv_old.supply_source_type_id := l_resv.supply_source_type_id;
l_rsv_old.supply_source_header_id := l_resv.supply_source_header_id;
l_rsv_old.supply_source_line_id := l_resv.supply_source_line_id;
l_rsv_old.supply_source_name := l_resv.supply_source_name;
l_rsv_old.supply_source_line_detail := l_resv.supply_source_line_detail;
l_rsv_old.revision := l_resv.revision;
l_rsv_old.subinventory_code := l_resv.subinventory_code;
l_rsv_old.subinventory_id := l_resv.subinventory_id;
l_rsv_old.locator_id := l_resv.locator_id;
l_rsv_old.lot_number := l_resv.lot_number;
l_rsv_old.lot_number_id := l_resv.lot_number_id;
l_rsv_old.pick_slip_number := l_resv.pick_slip_number;
l_rsv_old.lpn_id := l_resv.lpn_id;
l_rsv_old.attribute_category := l_resv.attribute_category;
l_rsv_old.attribute1 := l_resv.attribute1;
l_rsv_old.attribute2 := l_resv.attribute2;
l_rsv_old.attribute3 := l_resv.attribute3;
l_rsv_old.attribute4 := l_resv.attribute4;
l_rsv_old.attribute5 := l_resv.attribute5;
l_rsv_old.attribute6 := l_resv.attribute6;
l_rsv_old.attribute7 := l_resv.attribute7;
l_rsv_old.attribute8 := l_resv.attribute8;
l_rsv_old.attribute9 := l_resv.attribute9;
l_rsv_old.attribute10 := l_resv.attribute10;
l_rsv_old.attribute11 := l_resv.attribute11;
l_rsv_old.attribute12 := l_resv.attribute12;
l_rsv_old.attribute13 := l_resv.attribute13;
l_rsv_old.attribute14 := l_resv.attribute14;
l_rsv_old.attribute15 := l_resv.attribute15;
l_rsv_old.ship_ready_flag := l_resv.ship_ready_flag;
l_rsv_old.staged_flag := l_resv.staged_flag;
l_rsv_old.crossdock_flag := l_resv.crossdock_flag;
l_rsv_old.crossdock_criteria_id := l_resv.crossdock_criteria_id;
l_rsv_old.demand_source_line_detail := l_resv.demand_source_line_detail;
l_rsv_old.serial_reservation_quantity := l_resv.serial_reservation_quantity;
l_rsv_old.supply_receipt_date := l_resv.supply_receipt_date;
l_rsv_old.demand_ship_date := l_resv.demand_ship_date;
l_rsv_old.project_id := l_resv.project_id;
l_rsv_old.task_id := l_resv.task_id;
l_rsv_old.orig_supply_source_type_id := l_resv.orig_supply_source_type_id;
l_rsv_old.orig_supply_source_header_id := l_resv.orig_supply_source_header_id;
l_rsv_old.orig_supply_source_line_id := l_resv.orig_supply_source_line_id;
l_rsv_old.orig_supply_source_line_detail := l_resv.orig_supply_source_line_detail;
l_rsv_old.orig_demand_source_type_id := l_resv.orig_demand_source_type_id;
l_rsv_old.orig_demand_source_header_id := l_resv.orig_demand_source_header_id;
l_rsv_old.orig_demand_source_line_id := l_resv.orig_demand_source_line_id;
l_rsv_old.orig_demand_source_line_detail := l_resv.orig_demand_source_line_detail;
l_rsv_old.serial_number := l_resv.serial_number;
-- Specify the new values
--l_rsv_new.reservation_id := l_resv.reservation_id;
--l_rsv_new.LOCATOR_ID := null;
l_rsv_new.reservation_id := l_resv.reservation_id;
l_rsv_new.requirement_date := l_resv.requirement_date;
l_rsv_new.organization_id := l_resv.organization_id;
l_rsv_new.inventory_item_id := l_resv.inventory_item_id;
l_rsv_new.demand_source_type_id := l_resv.demand_source_type_id;
l_rsv_new.demand_source_name := l_resv.demand_source_name;
l_rsv_new.demand_source_header_id := l_resv.demand_source_header_id;
l_rsv_new.demand_source_line_id := l_resv.demand_source_line_id;
l_rsv_new.demand_source_delivery := l_resv.demand_source_delivery;
l_rsv_new.primary_uom_code := l_resv.primary_uom_code;
l_rsv_new.primary_uom_id := l_resv.primary_uom_id;
l_rsv_new.secondary_uom_code := l_resv.secondary_uom_code;
l_rsv_new.secondary_uom_id := l_resv.secondary_uom_id;
l_rsv_new.reservation_uom_code := l_resv.reservation_uom_code;
l_rsv_new.reservation_uom_id := l_resv.reservation_uom_id;
l_rsv_new.reservation_quantity := l_resv.reservation_quantity;
l_rsv_new.primary_reservation_quantity := l_resv.primary_reservation_quantity;
l_rsv_new.secondary_reservation_quantity := l_resv.secondary_reservation_quantity;
l_rsv_new.detailed_quantity := l_resv.detailed_quantity;
l_rsv_new.secondary_detailed_quantity := l_resv.secondary_detailed_quantity;
l_rsv_new.autodetail_group_id := l_resv.autodetail_group_id;
l_rsv_new.external_source_code := l_resv.external_source_code;
l_rsv_new.external_source_line_id := l_resv.external_source_line_id;
l_rsv_new.supply_source_type_id := l_resv.supply_source_type_id;
l_rsv_new.supply_source_header_id := l_resv.supply_source_header_id;
l_rsv_new.supply_source_line_id := l_resv.supply_source_line_id;
l_rsv_new.supply_source_name := l_resv.supply_source_name;
l_rsv_new.supply_source_line_detail := l_resv.supply_source_line_detail;
l_rsv_new.revision := l_resv.revision;
l_rsv_new.subinventory_code := l_resv.subinventory_code;
l_rsv_new.subinventory_id := l_resv.subinventory_id;
l_rsv_new.locator_id := null;--l_resv.locator_id;
l_rsv_new.lot_number := l_resv.lot_number;
l_rsv_new.lot_number_id := l_resv.lot_number_id;
l_rsv_new.pick_slip_number := l_resv.pick_slip_number;
l_rsv_new.lpn_id := l_resv.lpn_id;
l_rsv_new.attribute_category := l_resv.attribute_category;
l_rsv_new.attribute1 := l_resv.attribute1;
l_rsv_new.attribute2 := l_resv.attribute2;
l_rsv_new.attribute3 := l_resv.attribute3;
l_rsv_new.attribute4 := l_resv.attribute4;
l_rsv_new.attribute5 := l_resv.attribute5;
l_rsv_new.attribute6 := l_resv.attribute6;
l_rsv_new.attribute7 := l_resv.attribute7;
l_rsv_new.attribute8 := l_resv.attribute8;
l_rsv_new.attribute9 := l_resv.attribute9;
l_rsv_new.attribute10 := l_resv.attribute10;
l_rsv_new.attribute11 := l_resv.attribute11;
l_rsv_new.attribute12 := l_resv.attribute12;
l_rsv_new.attribute13 := l_resv.attribute13;
l_rsv_new.attribute14 := l_resv.attribute14;
l_rsv_new.attribute15 := l_resv.attribute15;
l_rsv_new.ship_ready_flag := l_resv.ship_ready_flag;
l_rsv_new.staged_flag := l_resv.staged_flag;
l_rsv_new.crossdock_flag := l_resv.crossdock_flag;
l_rsv_new.crossdock_criteria_id := l_resv.crossdock_criteria_id;
l_rsv_new.demand_source_line_detail := l_resv.demand_source_line_detail;
l_rsv_new.serial_reservation_quantity := l_resv.serial_reservation_quantity;
l_rsv_new.supply_receipt_date := l_resv.supply_receipt_date;
l_rsv_new.demand_ship_date := l_resv.demand_ship_date;
l_rsv_new.project_id := l_resv.project_id;
l_rsv_new.task_id := l_resv.task_id;
l_rsv_new.orig_supply_source_type_id := l_resv.orig_supply_source_type_id;
l_rsv_new.orig_supply_source_header_id := l_resv.orig_supply_source_header_id;
l_rsv_new.orig_supply_source_line_id := l_resv.orig_supply_source_line_id;
l_rsv_new.orig_supply_source_line_detail := l_resv.orig_supply_source_line_detail;
l_rsv_new.orig_demand_source_type_id := l_resv.orig_demand_source_type_id;
l_rsv_new.orig_demand_source_header_id := l_resv.orig_demand_source_header_id;
l_rsv_new.orig_demand_source_line_id := l_resv.orig_demand_source_line_id;
l_rsv_new.orig_demand_source_line_detail := l_resv.orig_demand_source_line_detail;
l_rsv_new.serial_number := l_resv.serial_number;
v_err_msg := null;
l_msg_data := null;
l_msg_count := null;
-- fnd_file.put_line (fnd_file.LOG, 'v_err_msg-'|| v_err_msg ||'l_msg_data-'|| l_msg_data ||'l_msg_count-' ||l_msg_count);
-- fnd_file.put_line (fnd_file.LOG, 'inv_reservation_pub.update_reservation start');
inv_reservation_pub.transfer_reservation (
p_api_version_number => 1.0
, P_init_msg_lst => fnd_api.g_true
, X_return_status => l_status
, X_msg_count => l_msg_count
, X_msg_data => l_msg_data
, p_is_transfer_supply => fnd_api.g_true
, P_original_rsv_rec => l_rsv_old
, P_to_rsv_rec => l_rsv_new
, P_original_serial_number => l_dummy_sn -- no serial contorl
, p_to_serial_number => L_dummy_sn -- no serial control
, P_validation_flag => fnd_api.g_true
--, P_check_availability => fnd_api.g_false
, P_over_reservation_flag => 0
, x_to_reservation_id => l_rsv_id
);
IF l_status = fnd_api.g_ret_sts_success THEN
--dbms_output.put_line ( 'reservation updated');
insert into MTL_RESERVATIONS_TEMP values (l_resv.reservation_id,l_resv.order_number,l_resv.header_id, l_resv.line_id, l_resv.segment1,sysdate,l_resv.ORGANIZATION_ID);
fnd_file.put_line (fnd_file.LOG, 'reservation updated for Order Number' || l_resv.order_number ||' reservation_id:' ||l_resv.RESERVATION_ID ||' Item:' ||l_resv.segment1 );
l_cnt :=l_cnt+1;
ELSE
IF l_msg_count>= 1 THEN
FOR I IN 1..l_msg_count
LOOP
--dbms_output.put_line ( SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255) );
--fnd_file.put_line (fnd_file.log, I || || SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255) '.');
fnd_file.put_line (fnd_file.LOG, 'reservation error out for Order Number' || l_resv.order_number ||' reservation_id:' ||l_resv.RESERVATION_ID ||' Item:' ||l_resv.segment1 ||'-'||SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255));
--fnd_file.put_line (fnd_file.LOG, 'reservation error out for Order Number' || l_resv.order_number ||' reservation_id:' ||l_resv.RESERVATION_ID ||' Item:' ||l_resv.segment1 ||'-'||substr(l_msg_data,1,255));
END LOOP;
/* fnd_file.put_line (fnd_file.LOG, 'l_msg_count-' ||l_msg_count);
fnd_file.put_line (fnd_file.LOG, 'length(l_msg_data)-' ||length(l_msg_data));
fnd_file.put_line (fnd_file.LOG, 'l_msg_data-' ||replace(l_msg_data,chr(10),''));
FOR k IN 1 ..l_msg_count
LOOP
if length(l_msg_data) < 4000 then
v_err_msg :=
l_msg_data || '~' || fnd_msg_pub.get (p_msg_index => k,
p_encoded => 'F');
end if;
END LOOP;
-- fnd_file.put_line (fnd_file.LOG, 'v_err_msg-'|| replace(v_err_msg,chr(10),'') ||'l_msg_data-'|| replace(l_msg_data,chr(10),''), ||'l_msg_count-' ||l_msg_count);
fnd_file.put_line (fnd_file.LOG, 'v_err_msg-' ||replace(l_msg_data,chr(10),''));
--l_msg_data :=null;
--fnd_file.put_line (fnd_file.LOG, 'reservation error out for Order Number' || l_resv.order_number ||' reservation_id:' ||l_resv.RESERVATION_ID ||' Item:' ||l_resv.segment1 ||'-'||v_err_msg);
*/
l_err :=l_err+1;
END IF;
END IF;
COMMIT;
END LOOP;
fnd_file.put_line (fnd_file.LOG, 'total reservation updated succesfully:' ||l_cnt );
-- fnd_file.put_line (fnd_file.LOG, 'total reservation updated succesfully:' ||l_err );
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ' OTHER exception:- '||sqlerrm);
END CLC_MTL_RESERVATIONS_PRG;
/
No comments:
Post a Comment