create or replace package comms_om_new is
-- Author : Horis
-- Created : 11/11/2004 9:22:32 AM
-- Purpose : Deal with order management process
PROCEDURE main(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out varchar2);
PROCEDURE book(x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2);
PROCEDURE auto_full(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_line_id IN NUMBER,
p_user_id IN NUMBER
);
PROCEDURE pick_release(x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2);
PROCEDURE ship_confirm(x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2,
p_delivery_id IN NUMBER);
PROCEDURE submit_req;
PROCEDURE create_install_base(p_line_id IN NUMBER,
x_return_status OUT VARCHAR2,
x_msg_count OUT VARCHAR2,
x_msg_data OUT VARCHAR2);
PROCEDURE create_install_base_rel(p_line_id IN NUMBER,
x_return_status OUT VARCHAR2,
x_msg_count OUT VARCHAR2,
x_msg_data OUT VARCHAR2);
PROCEDURE expire_install_base(x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2);
PROCEDURE deal_install_base(x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2);
end comms_om_new;
/
create or replace package body comms_om_new
is
g_header_id NUMBER;
g_order_number NUMBER;
g_line_id NUMBER;
g_line_ordered_quantity NUMBER;
g_line_schedule_date DATE;
g_user_id NUMBER := fnd_profile.value('USER_ID');
g_delivery_id NUMBER;
g_cur_party_id NUMBER;
g_cur_party_account_id NUMBER;
--for WF calling
PROCEDURE main(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out varchar2)
AS
l_return_status VARCHAR2(1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_book_flag VARCHAR2(1);
l_picked_flag VARCHAR2(1);
l_line_type NUMBER;
req_id NUMBER;
l_link_to_line_id NUMBER;
BEGIN
g_line_id := to_number(itemkey);
g_user_id := wf_engine.GetItemAttrNumber(itemtype,itemkey, 'USER_ID');
g_user_id := 1050;
IF (funcmode = 'RUN') THEN
SELECT ooha.header_id,
ooha.order_number,
oola.line_type_id,
oola.link_to_line_id
INTO g_header_id,
g_order_number,
l_line_type,
l_link_to_line_id
FROM oe_order_headers_all ooha,
oe_order_lines_all oola
WHERE ooha.header_id = oola.header_id
AND oola.line_id = g_line_id
AND ROWNUM = 1;
--1033 STRORDER, 1034 ChangePlan, 1036 Suspension
--for child item no need to deal
IF l_line_type IN (1033, 1034, 1036) AND
l_link_to_line_id IS NULL
THEN
req_id := fnd_request.submit_request('ONT', --application
'TBFAFL', --program
NULL, --description
NULL, --start_time
FALSE, --sub_request
g_line_id,g_user_id,chr(0),'','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','');
END IF; --for transation_type
resultout := 'COMPELTE:COMPLETE';
RETURN;
END IF;
IF (funcmode = 'CANCEL') THEN
resultout := 'COMPELTE';
RETURN;
END IF;
resultout := 'COMPLETE:COMPLETE';
OE_STANDARD_WF.Clear_Msg_Context;
return;
EXCEPTION
WHEN OTHERS THEN
WF_CORE.CONTEXT('comms_om_new', 'main', itemtype, itemkey, to_char(actid), funcmode );
RAISE;
END;
PROCEDURE book(x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2)
AS
CURSOR cur_line IS
SELECT line_id,
ordered_quantity,
schedule_ship_date
FROM oe_order_lines_all
WHERE line_id = g_line_id;
i NUMBER:=0;
X_DEBUG_FILE VARCHAR2(100);
--IN Parameters
l_header_rec OE_ORDER_PUB.Header_Rec_Type;
l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
--OUT Parameters
x_header_rec OE_ORDER_PUB.Header_Rec_Type;
x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
x_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
BEGIN
oe_debug_pub.initialize;
X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('TABLE');
oe_debug_pub.SetDebugLevel(1);
oe_msg_pub.initialize;
fnd_file.put_line(fnd_file.log, 'g_header_id!' || g_header_id);
fnd_global.apps_initialize( g_user_id, 21623, 660,NULL); --(user_id, resp_id, app_id, NULL)
l_header_rec := Oe_Order_Pub.G_Miss_Header_Rec;
l_header_rec.header_id := g_header_id;
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_action_request_tbl(1).request_type := oe_globals.g_book_order;
l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
l_action_request_tbl(1).entity_id := g_header_id;
i := 0;
FOR row_line IN cur_line
LOOP
i := i+1;
l_line_tbl(i) := oe_order_pub.g_miss_line_rec;
l_line_tbl(i).line_id := row_line.line_id;
l_line_tbl(i).shipped_quantity := row_line.ordered_quantity;
l_line_tbl(i).schedule_ship_date := g_line_schedule_date;
l_line_tbl(i).operation := oe_globals.g_opr_update;
END LOOP;
Oe_Order_Pub.Process_Order
( 1
, Fnd_Api.G_FALSE
, Fnd_Api.G_FALSE
, Fnd_Api.G_FALSE
, x_return_status
, x_msg_count
, x_msg_data
--IN PARAMETERS
, p_header_rec => l_header_rec
, p_line_tbl => l_line_tbl
, p_action_request_tbl => l_action_request_tbl
--OUT PARAMETERS
, x_header_rec => x_header_rec
, x_header_val_rec => x_header_val_rec
, x_Header_Adj_tbl => x_Header_Adj_tbl
, x_Header_Adj_val_tbl => x_Header_Adj_val_tbl
, x_Header_price_Att_tbl => x_Header_price_Att_tbl
, x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl
, x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl
, x_Header_Scredit_tbl => x_Header_Scredit_tbl
, x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl
, x_line_tbl => x_line_tbl
, x_line_val_tbl => x_line_val_tbl
, x_Line_Adj_tbl => x_Line_Adj_tbl
, x_Line_Adj_val_tbl => x_Line_Adj_val_tbl
, x_Line_price_Att_tbl => x_Line_price_Att_tbl
, x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl
, x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl
, x_Line_Scredit_tbl => x_Line_Scredit_tbl
, x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl
, x_Lot_Serial_tbl => x_Lot_Serial_tbl
, x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl
, x_action_request_tbl => x_action_request_tbl
);
fnd_file.put_line(fnd_file.log, 'Success: Booked');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
fnd_file.put_line(fnd_file.log, 'Failed: Booked');
x_return_status := SQLERRM;
END book;
PROCEDURE auto_full(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_line_id IN NUMBER,
p_user_id IN NUMBER
)
AS
l_return_status VARCHAR2(1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_picked_flag VARCHAR2(1);
l_line_type NUMBER;
l_cfg_start_date DATE;
l_bom_item_type_id NUMBER;
l_child_count NUMBER;
BEGIN
g_line_id := p_line_id;
g_user_id := p_user_id;
BEGIN
SELECT msib.bom_item_type,
oola.header_id,
oola.schedule_ship_date
INTO l_bom_item_type_id,
g_header_id,
g_line_schedule_date
FROM mtl_system_items_b msib,
oe_order_lines_all oola
WHERE msib.inventory_item_id = oola.inventory_item_id
AND oola.line_id = g_line_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
l_bom_item_type_id := 1;
END;
l_cfg_start_date := sysdate;
IF l_bom_item_type_id=1 THEN
<>
SELECT COUNT(*)
INTO l_child_count
FROM oe_order_lines_all oola
WHERE oola.link_to_line_id = g_line_id;
IF l_child_count=0 AND (sysdate-l_cfg_start_date)<1/24/6
THEN
--DBMS_LOCK.SLEEP(3);
GOTO wait_for_config;
INSERT INTO comms_log values('waiting');
commit;
END IF;
END IF; --for bom module
book(l_return_status, l_msg_count, l_msg_data );
pick_release(l_return_status, l_msg_count, l_msg_data);
submit_req;
deal_install_base(l_return_status, l_msg_count, l_msg_data) ;
END;
PROCEDURE pick_release(x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2)
AS
CURSOR cur_delivery_details IS
SELECT delivery_detail_id
FROM wsh_delivery_details
WHERE source_line_id = g_line_id
OR top_model_line_id = g_line_id;
p_api_version_number NUMBER :=1.0;
init_msg_list VARCHAR2(200);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
p_line_rows WSH_UTIL_CORE.ID_TAB_TYPE;
x_del_rows WSH_UTIL_CORE.ID_TAB_TYPE;
l_ship_method_code VARCHAR2(100);
i NUMBER;
l_commit VARCHAR2(30);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2(30);
x_trip_id VARCHAR2(30);
x_trip_name VARCHAR2(30);
fail_api EXCEPTION;
l_picked_flag VARCHAR2(10);
l_return_status VARCHAR2(1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
BEGIN
--Initialize (user_id, resp_id, app_id, NULL)
fnd_global.apps_initialize( g_user_id, 21623, 660, NULL);
-- Initialize return status
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
i := 0;
FOR row_delivery_details IN cur_delivery_details
LOOP
p_line_rows(1) := row_delivery_details.delivery_detail_id;
WSH_DELIVERY_DETAILS_PUB.AUTOCREATE_DELIVERIES(
p_api_version_number => 1.0,
p_init_msg_list => APPS.FND_API.G_TRUE,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_rows => p_line_rows,
x_del_rows => x_del_rows );
IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
RAISE fail_api;
ELSE
fnd_file.put_line(fnd_file.log, 'Success: Auto create delivery');
END IF;
-- Pick release.
p_delivery_id := x_del_rows(1);
p_delivery_name := TO_CHAR( x_del_rows(1) );
g_delivery_id := p_delivery_id;
wsh_deliveries_pub.delivery_action(
p_api_version_number => 1.0 ,
p_init_msg_list => null, -- IN VARCHAR2,
x_return_status => x_return_status, -- OUT VARCHAR2,
x_msg_count => x_msg_count, -- OUT NUMBER,
x_msg_data => x_msg_data, -- OUT VARCHAR2,
p_action_code => 'PICK-RELEASE', -- IN VARCHAR2,
p_delivery_id => p_delivery_id, -- IN NUMBER DEFAULT NULL,
p_delivery_name => p_delivery_name, -- IN VARCHAR2 DEFAULT NULL,
x_trip_id => x_trip_id, -- OUT VARCHAR2,
x_trip_name => x_trip_name -- OUT VARCHAR2
);
IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
RAISE fail_api;
ELSE
fnd_file.put_line(fnd_file.log, 'Success: Pick release');
END IF;
--for pick confirm
COMMIT;
<>
BEGIN
SELECT wdd.released_status
INTO l_picked_flag
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = row_delivery_details.delivery_detail_id;
EXCEPTION
WHEN OTHERS THEN
l_picked_flag := 'Y';
END;
IF l_picked_flag<>'Y' THEN
GOTO wait_for_pick2;
END IF;
ship_confirm(l_return_status, l_msg_count, l_msg_data, p_delivery_id);
END LOOP; --row_delivery_details
EXCEPTION
WHEN fail_api THEN
WSH_UTIL_CORE.get_messages('Y', x_msg_summary, x_msg_details, x_msg_count);
IF x_msg_count > 1 THEN
x_msg_data := x_msg_summary || x_msg_details;
ELSE
x_msg_data := x_msg_summary || x_msg_details;
END IF;
END pick_release;
PROCEDURE ship_confirm(x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2,
p_delivery_id IN NUMBER)
IS
-- Standard Parameters.
p_api_version_number NUMBER :=1.0;
init_msg_list VARCHAR2(200);
x_msg_details VARCHAR2(3000);
x_msg_summary VARCHAR2(3000);
p_validation_level NUMBER;
-- Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
p_delivery_name VARCHAR2(30);
p_action_code VARCHAR2(15);
p_asg_trip_id NUMBER;
p_asg_trip_name VARCHAR2(30);
p_asg_pickup_stop_id NUMBER;
p_asg_pickup_loc_id NUMBER;
p_asg_pickup_loc_code VARCHAR2(30);
p_asg_pickup_arr_date DATE;
p_asg_pickup_dep_date DATE;
p_asg_dropoff_stop_id NUMBER;
p_asg_dropoff_loc_id NUMBER;
p_asg_dropoff_loc_code VARCHAR2(30);
p_asg_dropoff_arr_date DATE;
p_asg_dropoff_dep_date DATE;
p_sc_action_flag VARCHAR2(10);
p_sc_intransit_flag VARCHAR2(10);
p_sc_close_trip_flag VARCHAR2(10);
p_sc_create_bol_flag VARCHAR2(10);
p_sc_stage_del_flag VARCHAR2(10);
p_sc_trip_ship_method VARCHAR2(30);
p_sc_actual_dep_date VARCHAR2(30);
p_sc_report_set_id NUMBER;
p_sc_report_set_name VARCHAR2(60);
p_wv_override_flag VARCHAR2(10);
x_trip_id VARCHAR2(30);
x_trip_name VARCHAR2(30);
/*Handle exceptions*/
fail_api EXCEPTION;
X_DEBUG_FILE VARCHAR2(100);
l_ship_method_code VARCHAR2(100);
BEGIN
--Initialize (user_id, resp_id, app_id, NULL)
fnd_global.apps_initialize( g_user_id, 21623, 660, NULL);
-- Initialize return status
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Ship Confirming
p_delivery_name := TO_CHAR(p_delivery_id);
BEGIN
SELECT shipping_method_code
INTO l_ship_method_code
FROM oe_order_headers_all
WHERE header_id = g_header_id;
EXCEPTION
WHEN OTHERS THEN
l_ship_method_code := NULL;
END;
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_sc_action_flag := 'S'; -- Ship entered quantity.
p_sc_intransit_flag := 'Y'; -- In transit flag is set to 'Y' closes the pickup stop and sets the delivery in transit.
p_sc_close_trip_flag := 'Y'; -- Close the trip after ship confirm
p_sc_trip_ship_method := l_ship_method_code; -- The ship method code
WSH_DELIVERIES_PUB.Delivery_Action(
p_api_version_number => 1.0,
p_init_msg_list => init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => p_action_code,
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => p_asg_trip_id,
p_asg_trip_name => p_asg_trip_name,
p_asg_pickup_stop_id => p_asg_pickup_stop_id,
p_asg_pickup_loc_id => p_asg_pickup_loc_id,
p_asg_pickup_loc_code => p_asg_pickup_loc_code,
p_asg_pickup_arr_date => p_asg_pickup_arr_date,
p_asg_pickup_dep_date => p_asg_pickup_dep_date,
p_asg_dropoff_stop_id => p_asg_dropoff_stop_id,
p_asg_dropoff_loc_id => p_asg_dropoff_loc_id,
p_asg_dropoff_loc_code => p_asg_dropoff_loc_code,
p_asg_dropoff_arr_date => p_asg_dropoff_arr_date,
p_asg_dropoff_dep_date => p_asg_dropoff_dep_date,
p_sc_action_flag => p_sc_action_flag,
p_sc_intransit_flag => p_sc_intransit_flag,
p_sc_close_trip_flag => p_sc_close_trip_flag,
p_sc_create_bol_flag => p_sc_create_bol_flag,
p_sc_stage_del_flag => p_sc_stage_del_flag,
p_sc_trip_ship_method => p_sc_trip_ship_method,
p_sc_actual_dep_date => p_sc_actual_dep_date,
p_sc_report_set_id => p_sc_report_set_id,
p_sc_report_set_name => p_sc_report_set_name,
p_wv_override_flag => p_wv_override_flag,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name);
IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
RAISE fail_api;
ELSE
fnd_file.put_line(fnd_file.log, 'Success: Ship confirm');
END IF;
COMMIT;
EXCEPTION
WHEN fail_api THEN
WSH_UTIL_CORE.get_messages('Y', x_msg_summary, x_msg_details, x_msg_count);
IF x_msg_count > 1 THEN
x_msg_data := x_msg_summary || x_msg_details;
ELSE
x_msg_data := x_msg_summary || x_msg_details;
END IF;
END SHIP_CONFIRM;
PROCEDURE submit_req
AS
req_id NUMBER;
BEGIN
--submit Process transaction interface
req_id := fnd_request.submit_request('INV', --application
'INCTCM', --program
NULL, --description
NULL, --start_time
FALSE, --sub_request
chr(0), '','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','');
fnd_file.put_line(fnd_file.log, 'Submit request: Process transaction interface');
--submit WIP Move Transaction Manager
req_id := fnd_request.submit_request('WIP', --application
'WICTMS', --program
NULL, --description
NULL, --start_time
FALSE, --sub_request
chr(0), '','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','',
'','','','','','','','','','');
fnd_file.put_line(fnd_file.log, 'Submit request: WIP Move Transaction Manager');
END submit_req;
PROCEDURE create_install_base(p_line_id IN NUMBER,
x_return_status OUT VARCHAR2,
x_msg_count OUT VARCHAR2,
x_msg_data OUT VARCHAR2)
AS
l_instance_rec csi_datastructures_pub.instance_rec;
l_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
l_party_tbl csi_datastructures_pub.party_tbl;
l_account_tbl csi_datastructures_pub.party_account_tbl;
l_pricing_attrib_tbl csi_datastructures_pub.pricing_attribs_tbl;
l_org_assignments_tbl csi_datastructures_pub.organization_units_tbl;
l_asset_assignment_tbl csi_datastructures_pub.instance_asset_tbl;
l_txn_rec csi_datastructures_pub.transaction_rec;
l_inventory_item_id NUMBER;
l_org_id NUMBER;
l_ordered_quantity NUMBER;
l_uom VARCHAR2(10);
l_instance_id NUMBER;
l_instance_party_id NUMBER;
l_ip_account_id NUMBER;
l_order_number NUMBER;
l_sold_to_org_id NUMBER;
l_ship_to_org_id NUMBER;
l_invoice_to_org_id NUMBER;
l_cur_party_id NUMBER;
l_cust_account_id NUMBER;
l_ship_to_site_id NUMBER;
l_invoice_to_site_id NUMBER;
l_user_party_id NUMBER;
cursor cur_user(p_party_id IN NUMBER) is
select hr.object_id party_id
FROM hz_relationships hr
where hr.subject_id = p_party_id
AND hr.relationship_code = 'DOCUMENT_USER'
AND hr.relationship_type = 'USERS';
BEGIN
SELECT oola.inventory_item_id,
oola.org_id,
oola.ordered_quantity,
oola.order_quantity_uom,
oola.ship_to_org_id,
oola.invoice_to_org_id,
ooha.order_number,
ooha.sold_to_org_id
INTO l_inventory_item_id,
l_org_id,
l_ordered_quantity,
l_uom,
l_ship_to_org_id,
l_invoice_to_org_id,
l_order_number,
l_sold_to_org_id
FROM oe_order_lines_all oola,
oe_order_headers_all ooha
WHERE oola.line_id = p_line_id
AND oola.header_id = ooha.header_id;
SELECT hpsu.party_site_id
INTO l_ship_to_site_id
FROM hz_party_site_uses hpsu
WHERE hpsu.party_site_use_id = l_ship_to_org_id;
SELECT hpsu.party_site_id
INTO l_invoice_to_site_id
FROM hz_party_site_uses hpsu
WHERE hpsu.party_site_use_id = l_invoice_to_org_id;
SELECT csi_item_instances_s.nextval
INTO l_instance_id
FROM dual;
l_instance_rec.instance_id := l_instance_id;
l_instance_rec.instance_number := l_instance_id;
l_instance_rec.external_reference := l_order_number;
l_instance_rec.inventory_item_id := l_inventory_item_id;
l_instance_rec.inv_master_organization_id := 86;
l_instance_rec.mfg_serial_number_flag := 'N';
l_instance_rec.quantity := l_ordered_quantity;
l_instance_rec.unit_of_measure := l_uom;
l_instance_rec.accounting_class_code := 'CUST_PROD';
l_instance_rec.instance_status_id := 10000;
l_instance_rec.customer_view_flag := NULL;
l_instance_rec.merchant_view_flag := NULL;
l_instance_rec.sellable_flag := NULL;
l_instance_rec.active_start_date := TRUNC(SYSDATE);
l_instance_rec.location_type_code := 'HZ_PARTY_SITES';
l_instance_rec.location_id := 249;
l_instance_rec.install_date := TRUNC(SYSDATE);
l_instance_rec.creation_complete_flag := 'Y';
l_instance_rec.version_label := 'AS_CREATED';
l_instance_rec.object_version_number := 1;
--get the user account id
SELECT hca.party_id,
hca.cust_account_id
INTO l_cur_party_id,
l_cust_account_id
FROM hz_cust_accounts hca
WHERE hca.cust_account_id = l_sold_to_org_id;
insert into comms_log values ('cur_party_id ' || l_cur_party_id);
FOR row_user IN cur_user(l_cur_party_id) LOOP
--get user's account id
BEGIN
SELECT hca.cust_account_id
INTO l_cust_account_id
FROM hz_cust_accounts hca
WHERE hca.party_id = row_user.party_id
AND ROWNUM=1;
EXCEPTION
WHEN OTHERS THEN
l_cust_account_id := NULL;
END;
insert into comms_log values ('user_party_id ' || row_user.party_id );
insert into comms_log values ('user_party_cunt_id ' || l_cust_account_id);
SELECT csi_i_parties_s.nextval
INTO l_instance_party_id
FROM dual;
l_party_tbl(1).instance_party_id := l_instance_party_id;
l_party_tbl(1).instance_id := l_instance_id;
l_party_tbl(1).party_source_table := 'HZ_PARTIES';
l_party_tbl(1).party_id := row_user.party_id;
l_party_tbl(1).relationship_type_code := 'DOCUMENT_USER';
l_party_tbl(1).contact_flag := 'N';
l_party_tbl(1).active_start_date := SYSDATE;
l_party_tbl(1).object_version_number := 1;
SELECT csi_ip_accounts_s.nextval
INTO l_ip_account_id
FROM dual;
l_account_tbl(1).ip_account_id := l_ip_account_id;
l_account_tbl(1).instance_party_id := l_instance_party_id;
l_account_tbl(1).party_account_id := l_cust_account_id;
l_account_tbl(1).relationship_type_code := 'DOCUMENT_USER';
l_account_tbl(1).active_start_date := sysdate;
l_account_tbl(1).BILL_TO_ADDRESS := 1170;
l_account_tbl(1).SHIP_TO_ADDRESS := 1170;
l_account_tbl(1).object_version_number := 1;
l_account_tbl(1).parent_tbl_index := 1;
l_account_tbl(1).call_contracts := 'Y';
l_txn_rec.transaction_date := TRUNC(SYSDATE);
l_txn_rec.SOURCE_TRANSACTION_DATE := TRUNC(SYSDATE);
l_txn_rec.TRANSACTION_TYPE_ID := 1;
l_txn_rec.OBJECT_VERSION_NUMBER := 1;
csi_item_instance_pub.create_item_instance(
p_api_version => 1.0 --IN NUMBER
,p_instance_rec => l_instance_rec -- IN OUT NOCOPY csi_datastructures_pub.instance_rec
,p_ext_attrib_values_tbl => l_ext_attrib_values_tbl -- IN OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl
,p_party_tbl => l_party_tbl --IN OUT NOCOPY csi_datastructures_pub.party_tbl
,p_account_tbl => l_account_tbl --IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
,p_pricing_attrib_tbl => l_pricing_attrib_tbl --IN OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl
,p_org_assignments_tbl => l_org_assignments_tbl --IN OUT NOCOPY csi_datastructures_pub.organization_units_tbl
,p_asset_assignment_tbl => l_asset_assignment_tbl --IN OUT NOCOPY csi_datastructures_pub.instance_asset_tbl
,p_txn_rec => l_txn_rec --IN OUT NOCOPY csi_datastructures_pub.transaction_rec
,x_return_status => x_return_status --OUT NOCOPY VARCHAR2
,x_msg_count => x_msg_count --OUT NOCOPY NUMBER
,x_msg_data => x_msg_data); --OUT NOCOPY VARCH
insert into comms_log values('create install base ');
END LOOP;
COMMIT;
END;
PROCEDURE create_install_base_rel(p_line_id IN NUMBER,
x_return_status OUT VARCHAR2,
x_msg_count OUT VARCHAR2,
x_msg_data OUT VARCHAR2)
AS
l_instance_id NUMBER;
p_party_tbl csi_datastructures_pub.party_tbl;
p_txn_rec csi_datastructures_pub.transaction_rec;
l_i_parties_id NUMBER;
l_owner_party_id NUMBER;
cursor cur_user(p_party_id IN NUMBER) is
select hr.object_id party_id
FROM hz_relationships hr
where hr.subject_id = p_party_id
AND hr.relationship_code = 'DOCUMENT_USER'
AND hr.relationship_type = 'USERS';
BEGIN
SELECT cii.instance_id, cii.owner_party_id
INTO l_instance_id, l_owner_party_id
FROM csi_item_instances cii
WHERE cii.last_oe_order_line_id = p_line_id;
FOR row_user IN cur_user(l_owner_party_id)
LOOP
SELECT csi_i_parties_s.nextval
INTO l_i_parties_id
FROM DUAL;
/*p_relationship_tbl(1).RELATIONSHIP_ID := l_ii_rel_id;
p_relationship_tbl(1).RELATIONSHIP_TYPE_CODE := 'USED BY';
p_relationship_tbl(1).OBJECT_ID := row_user.party_id;
p_relationship_tbl(1).SUBJECT_ID := l_instance_id;
p_relationship_tbl(1).SUBJECT_HAS_CHILD := 'N';
p_relationship_tbl(1).POSITION_REFERENCE := NULL;
p_relationship_tbl(1).ACTIVE_START_DATE := SYSDATE;
p_relationship_tbl(1).ACTIVE_END_DATE := SYSDATE;
p_relationship_tbl(1).DISPLAY_ORDER := NULL;
p_relationship_tbl(1).MANDATORY_FLAG := 'N';
p_relationship_tbl(1).OBJECT_VERSION_NUMBER := 1;
p_txn_rec.TRANSACTION_DATE := TRUNC(SYSDATE);
p_txn_rec.SOURCE_TRANSACTION_DATE := TRUNC(SYSDATE);
p_txn_rec.TRANSACTION_TYPE_ID :=1;
p_txn_rec.OBJECT_VERSION_NUMBER :=1;
*/
insert into csi_i_parties (
instance_party_id,
instance_id,
party_source_table,
party_id,
relationship_type_code,
contact_flag,
active_start_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
values( l_i_parties_id,
l_instance_id,
'HZ_PARTIES',
row_user.party_id,
'USED BY',
'N',
sysdate,
'-1',
sysdate,
'-1',
sysdate,
'-1',
1
);
/* csi_party_relationships_pub.create_inst_party_relationship
( p_api_version => 1.0 ,-- IN NUMBER
p_commit => 'Y' ,-- IN VARCHAR2
p_init_msg_list => 'N' ,-- IN VARCHAR2
p_validation_level => 0, -- IN NUMBER
p_party_tbl => p_party_tbl ,-- IN OUT NOCOPY csi_datastructures_pub.party_tbl
p_party_account_tbl=> p_party_account_tbl ,-- IN OUT NOCOPY csi_datastructures_pub.party_account_tbl
p_txn_rec => p_txn_rec ,-- IN OUT NOCOPY csi_datastructures_pub.transaction_rec
x_return_status => x_return_status ,-- OUT NOCOPY VARCHAR2
x_msg_count => x_msg_count ,-- OUT NOCOPY NUMBER
x_msg_data => x_msg_data -- OUT NOCOPY VARCHAR2
) ;*/
END LOOP;
COMMIT;
END;
--expire earily install base records
PROCEDURE expire_install_base(x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2)
AS
l_instance_rec csi_datastructures_pub.instance_rec;
l_txn_rec csi_datastructures_pub.transaction_rec;
l_instance_id_lst csi_datastructures_pub.id_tbl;
l_cust_account_id NUMBER;
l_party_id NUMBER;
l_line_type_id NUMBER;
--all install base records of earliy subscripiton
CURSOR cur_instance(p_party_id IN NUMBER, p_party_account_id IN NUMBER) IS
SELECT cii.instance_id,
cii.instance_number,
cii.inventory_item_id,
cii.inv_master_organization_id,
cii.active_start_date,
cii.active_end_date
FROM csi_item_instances cii
WHERE cii.owner_party_id = p_party_id
AND cii.owner_party_account_id = p_party_account_id
AND cii.last_oe_order_line_id <> g_line_id;
no_need_expire EXCEPTION;
BEGIN
SELECT oola.line_type_id,
hca.cust_account_id,
hca.party_id
INTO l_line_type_id,
l_cust_account_id,
l_party_id
FROM oe_order_lines_all oola,
oe_order_headers_all ooha,
hz_cust_accounts hca
WHERE oola.line_id = g_line_id
AND oola.header_id = ooha.header_id
AND hca.cust_account_id = ooha.sold_to_org_id
AND ROWNUM = 1;
--for Changeplan, expire existing item install base
IF l_line_type_id <> 1034 THEN
RAISE no_need_expire;
END IF;
FOR row_instance IN cur_instance(l_party_id, l_cust_account_id)
LOOP
l_instance_rec.instance_id := row_instance.instance_id;
l_instance_rec.instance_number := row_instance.instance_number;
l_instance_rec.external_reference := NULL;
l_instance_rec.inventory_item_id := row_instance.inventory_item_id;
l_instance_rec.inv_master_organization_id := row_instance.inv_master_organization_id;
l_instance_rec.active_end_date := TRUNC(SYSDATE);
l_txn_rec.transaction_date := TRUNC(SYSDATE);
l_txn_rec.SOURCE_TRANSACTION_DATE := TRUNC(SYSDATE);
l_txn_rec.TRANSACTION_TYPE_ID := 1;
l_txn_rec.OBJECT_VERSION_NUMBER := 1;
csi_item_instance_pub.expire_item_instance(
p_api_version => 1.0 -- IN NUMBER
,p_instance_rec => l_instance_rec -- IN csi_datastructures_pub.instance_rec
,p_txn_rec => l_txn_rec -- IN OUT NOCOPY csi_datastructures_pub.transaction_rec
,x_instance_id_lst => l_instance_id_lst-- OUT NOCOPY csi_datastructures_pub.id_tbl
,x_return_status => x_return_status-- OUT NOCOPY VARCHAR2
,x_msg_count => x_msg_count-- OUT NOCOPY NUMBER
,x_msg_data => x_msg_data-- OUT NOCOPY VARCHAR2
);
END LOOP;
COMMIT;
EXCEPTION
WHEN no_need_expire THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
PROCEDURE deal_install_base(x_return_status OUT VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2)
AS
l_cust_account_id NUMBER;
l_party_id NUMBER;
l_line_type_id NUMBER;
no_need_expire EXCEPTION;
l_inventory_item_id NUMBER;
l_product_type VARCHAR2(20);
l_item_number mtl_system_items_b.segment1%TYPE;
l_count NUMBER;
l_telephone_num VARCHAR2(20);
l_sim_num VARCHAR2(20);
l_return_status VARCHAR2(1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
--product subscribled earlier
CURSOR cur_prod IS
SELECT cii.instance_id,
cii.last_oe_order_line_id line_id
FROM csi_item_instances cii
WHERE cii.last_oe_order_line_id <> g_line_id
AND NVL(cii.attribute10,'E') = l_product_type
AND cii.owner_party_id = l_party_id
AND cii.owner_party_account_id = l_cust_account_id;
BEGIN
SELECT oola.line_type_id,
hca.cust_account_id,
hca.party_id,
oola.inventory_item_id
INTO l_line_type_id,
l_cust_account_id,
l_party_id,
l_inventory_item_id
FROM oe_order_lines_all oola,
oe_order_headers_all ooha,
hz_cust_accounts hca
WHERE oola.line_id = g_line_id
AND oola.header_id = ooha.header_id
AND hca.cust_account_id = ooha.sold_to_org_id
AND ROWNUM = 1;
SELECT msib.segment1
INTO l_item_number
FROM mtl_system_items_b msib
WHERE msib.organization_id = 86
AND msib.inventory_item_id = l_inventory_item_id
AND ROWNUM = 1;
IF l_item_number IN ('MOBILEPROD','MOBILECHANGEPLAN') THEN
l_product_type := 'M';
ELSIF l_item_number='BRODBAND' THEN
l_product_type := 'B';
ELSIF l_item_number='DIGITAL_LEASED_LINE_NATL' THEN
l_product_type := 'D';
END IF;
--get service number
--Get customer serial number and SIM attribute in quator
BEGIN
SELECT cn.service_num, cn.sim_num
INTO l_telephone_num, l_sim_num
FROM comms_numbers cn,
aso_quote_headers_all aqh,
aso_quote_lines_all aql,
oe_order_lines_all oola
WHERE oola.line_id = g_line_id
AND oola.header_id = aqh.order_id
AND aql.quote_header_id = aqh.quote_header_id
AND cn.quote_line_id = aql.quote_line_id
AND cn.app_id = 521
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
l_sim_num := NULL;
l_telephone_num := NULL;
END;
IF l_sim_num IS NULL THEN
BEGIN
SELECT cn.service_num, cn.sim_num
INTO l_telephone_num, l_sim_num
FROM comms_numbers cn
WHERE cn.quote_line_id = g_line_id
AND cn.app_id = 660
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
l_sim_num := NULL;
l_telephone_num := NULL;
END;
END IF;
--for change plan, get service number from old subscription
IF l_line_type_id=1034 AND l_sim_num IS NULL THEN
SELECT MAX(cii.serial_number), MAX(cii.attribute11)
INTO l_telephone_num, l_sim_num
FROM csi_item_instances cii
WHERE cii.owner_party_id = l_party_id
AND cii.instance_status_id = 10000;
END IF;
--1033, Staprodline2
--1034, ChangePlan2,
--1036, Suspension
--for Changeplan, expire existing item install base
--attribute10, 'M' Mobile, 'C' Cable, 'D' Degital, 'E' Expire
IF l_line_type_id IN (1034, 1036) THEN
FOR row_prod IN cur_prod
LOOP
--update product subscribled earliy
UPDATE csi_item_instances cii
SET cii.active_end_date = TRUNC(sysdate),
cii.instance_status_id = 1,
cii.attribute10 = 'E'
WHERE cii.instance_id = row_prod.instance_id;
--update product lines at the same order
UPDATE csi_item_instances cii
SET cii.active_end_date = TRUNC(sysdate),
cii.instance_status_id = 1,
cii.attribute10 = 'E'
WHERE cii.last_oe_order_line_id IN (
SELECT oola.line_id
FROM oe_order_lines_all oola
WHERE oola.top_model_line_id = row_prod.line_id
);
END LOOP;
END IF;
--make product type for new install base
<>
SELECT COUNT(*)
INTO l_count
FROM csi_item_instances cii
WHERE cii.last_oe_order_line_id = g_line_id;
IF l_count=0 THEN
GOTO wait_for_install_base;
END IF;
UPDATE csi_item_instances cii
SET cii.attribute10 = l_product_type,
cii.serial_number = l_telephone_num,
cii.attribute11 = l_sim_num
WHERE cii.last_oe_order_line_id = g_line_id;
--update comms_number for the lastest order line_id
IF l_line_type_id=1034 THEN
UPDATE comms_numbers cn
SET cn.quote_line_id = g_line_id,
cn.app_id = 660
WHERE cn.service_num = l_telephone_num;
END IF;
--for suspension order, update status
IF l_line_type_id=1036 THEN
--update product lines at the same order
<>
SELECT COUNT(*)
INTO l_count
FROM oe_order_lines_all oola,
oe_order_lines_all oola2
WHERE oola.header_id = oola2.header_id
AND oola2.line_id = g_line_id;
IF l_count<2 THEN
GOTO wait_for_all_install_base;
END IF;
UPDATE csi_item_instances cii
SET cii.instance_status_id = 10021
WHERE cii.last_oe_order_line_id IN (
SELECT oola.line_id
FROM oe_order_lines_all oola,
oe_order_lines_all oola2
WHERE oola.header_id = oola2.header_id
AND oola2.line_id = g_line_id
);
END IF;
--for starprod, create a child intall base for user
IF l_line_type_id=1033 THEN
create_install_base_rel(g_line_id,
l_return_status,
l_msg_count,
l_msg_data) ;
END IF;
COMMIT;
EXCEPTION
WHEN no_need_expire THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
END comms_om_new;
/
Saturday, August 7, 2010
Supply Chain Management APIS
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment