ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [zz]Oracle EBS API: OM模块主要API示例

[zz]Oracle EBS API: OM模块主要API示例

原创 Linux操作系统 作者:idoo 时间:2009-11-30 16:41:07 0 删除 编辑

在Oracle EBS OM模块中,  一个典型订单流程包括: 下单, 挑库, 发货确认; 创建客户安装Install Base, 以及失效Install Base等业务. 

本文列出主要业务的API示例, 供客户化或系统集成时参考. (所有代码在11.5.9及11.5.10验证通过, 其他版本需做适当调整).

1, Book order
     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
     );   
 
2,  创建发货行
       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 );

3,  挑库发放
       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
            );

4,  发放确认
    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);

5,  创建Install Base
         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  

6, 失效Install Base
         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
          );   


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



具体示例代码请参加附件:   Oracle OM API Sample


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;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/318394/viewspace-621029/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-13

  • 博文量
    13
  • 访问量
    35579