查看: 11722|回复: 13
Oracle EBS OM 主要API示例 转载
论坛徽章:3
在OracleEBS OM模块中,&&一个典型订单流程包括: 下单, 挑库, 发货确认; 创建客户安装Install Base, 以及失效Install Base等业务.
本文列出主要业务的API示例, 供客户化或系统集成时参考. (所有在11.5.9及11.5.10验证通过, 其他版本需做适当调整).
1, Book order
& &&&Oe_Order_Pub.Process_Order
& &&&, 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
& && && & );& &
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
具体示例代码请参加附件:&&
create or replace package comms_om_new is
-- Author&&: Horis
-- Created : 11/11/: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_
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_
create or replace package body comms_om_new
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)
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;
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 (, 1036) AND
l_link_to_line_id IS NULL
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';
IF (funcmode = 'CANCEL') THEN
resultout := 'COMPELTE';
resultout := 'COMPLETE:COMPLETE';
OE_STANDARD_WF.Clear_Msg_C
WHEN OTHERS THEN
WF_CORE.CONTEXT('comms_om_new', 'main', itemtype, itemkey, to_char(actid), funcmode );
PROCEDURE book(x_return_status& &OUT&&VARCHAR2,
x_msg_count& && & OUT&&NUMBER,
x_msg_data& && &&&OUT&&VARCHAR2)
CURSOR cur_line IS
SELECT line_id,
ordered_quantity,
schedule_ship_date
FROM oe_order_lines_all
WHERE line_id = g_line_
i& && && && && && && &&&NUMBER:=0;
X_DEBUG_FILE& && && && &VARCHAR2(100);
--IN Parameters
l_header_rec& && && && & OE_ORDER_PUB.Header_Rec_T
l_action_request_tbl& &&&OE_ORDER_PUB.Request_Tbl_T
l_line_tbl& && && && && &OE_ORDER_PUB.Line_Tbl_T
--OUT Parameters
x_header_rec& && && && & OE_ORDER_PUB.Header_Rec_T
x_header_val_rec& && && &OE_ORDER_PUB.Header_Val_Rec_T
x_Header_Adj_tbl& && && &OE_ORDER_PUB.Header_Adj_Tbl_T
x_Header_Adj_val_tbl& &&&OE_ORDER_PUB.Header_Adj_Val_Tbl_T
x_Header_price_Att_tbl& &OE_ORDER_PUB.Header_Price_Att_Tbl_T
x_Header_Adj_Att_tbl& &&&OE_ORDER_PUB.Header_Adj_Att_Tbl_T
x_Header_Adj_Assoc_tbl& &OE_ORDER_PUB.Header_Adj_Assoc_Tbl_T
x_Header_Scredit_tbl& &&&OE_ORDER_PUB.Header_Scredit_Tbl_T
x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_T
x_line_tbl& && && && && &OE_ORDER_PUB.Line_Tbl_T
x_line_val_tbl& && && &&&OE_ORDER_PUB.Line_Val_Tbl_T
x_Line_Adj_tbl& && && &&&OE_ORDER_PUB.Line_Adj_Tbl_T
x_Line_Adj_val_tbl& && & OE_ORDER_PUB.Line_Adj_Val_Tbl_T
x_Line_price_Att_tbl& &&&OE_ORDER_PUB.Line_Price_Att_Tbl_T
x_Line_Adj_Att_tbl& && & OE_ORDER_PUB.Line_Adj_Att_Tbl_T
x_Line_Adj_Assoc_tbl& &&&OE_ORDER_PUB.Line_Adj_Assoc_Tbl_T
x_Line_Scredit_tbl& && & OE_ORDER_PUB.Line_Scredit_Tbl_T
x_Line_Scredit_val_tbl& &OE_ORDER_PUB.Line_Scredit_Val_Tbl_T
x_Lot_Serial_tbl& && && &OE_ORDER_PUB.Lot_Serial_Tbl_T
x_Lot_Serial_val_tbl& &&&OE_ORDER_PUB.Lot_Serial_Val_Tbl_T
x_action_request_tbl& &&&OE_ORDER_PUB.Request_Tbl_T
oe_debug_pub.
X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('TABLE');
oe_debug_pub.SetDebugLevel(1);
oe_msg_pub.
fnd_file.put_line(fnd_file.log, 'g_header_id!' || g_header_id);
fnd_global.apps_initialize( g_user_id, 2,NULL);&&--(user_id, resp_id, app_id, NULL)
l_header_rec& && && &&&:= Oe_Order_Pub.G_Miss_Header_R
l_header_rec.header_id := g_header_
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_action_request_tbl(1).request_type := oe_globals.g_book_
l_action_request_tbl(1).entity_code&&:= oe_globals.g_entity_
l_action_request_tbl(1).entity_id& & := g_header_
FOR row_line IN cur_line
l_line_tbl(i)& && && && && && &&&:= oe_order_pub.g_miss_line_
l_line_tbl(i).line_id& && && && &:= row_line.line_
l_line_tbl(i).shipped_quantity& &:= row_line.ordered_
l_line_tbl(i).schedule_ship_date := g_line_schedule_
l_line_tbl(i).operation& && && & := oe_globals.g_opr_
Oe_Order_Pub.Process_Order
, 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');
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'Failed:&&Booked');
x_return_status := SQLERRM;
PROCEDURE auto_full(errbuf& && && &OUT VARCHAR2,
retcode& && &&&OUT VARCHAR2,
p_line_id& && &IN NUMBER,
p_user_id& && &IN NUMBER
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;
g_line_id := p_line_
g_user_id := p_user_
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;
WHEN OTHERS THEN
l_bom_item_type_id := 1;
l_cfg_start_date :=
IF l_bom_item_type_id=1 THEN
&&wait_for_config&&
SELECT COUNT(*)
INTO l_child_count
FROM oe_order_lines_all oola
WHERE oola.link_to_line_id = g_line_
IF l_child_count=0 AND (sysdate-l_cfg_start_date)&1/24/6
--DBMS_LOCK.SLEEP(3);
GOTO wait_for_
INSERT INTO comms_log values('waiting');
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);
deal_install_base(l_return_status, l_msg_count, l_msg_data) ;
PROCEDURE pick_release(x_return_status& &OUT&&VARCHAR2,
x_msg_count& && & OUT&&NUMBER,
x_msg_data& && &&&OUT&&VARCHAR2)
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_
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);
--Initialize (user_id, resp_id, app_id, NULL)
fnd_global.apps_initialize( g_user_id, 2, NULL);
-- Initialize return status
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
FOR row_delivery_details IN cur_delivery_details
p_line_rows(1) := row_delivery_details.delivery_detail_
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_
fnd_file.put_line(fnd_file.log, 'Success:&&Auto create delivery');
-- Pick release.
p_delivery_id := x_del_rows(1);
p_delivery_name := TO_CHAR( x_del_rows(1) );
g_delivery_id := p_delivery_
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_
fnd_file.put_line(fnd_file.log, 'Success:&&Pick release');
--for pick confirm
&&wait_for_pick2&&
SELECT wdd.released_status
INTO l_picked_flag
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = row_delivery_details.delivery_detail_
WHEN OTHERS THEN
l_picked_flag := 'Y';
IF l_picked_flag&&'Y' THEN
GOTO wait_for_pick2;
ship_confirm(l_return_status, l_msg_count, l_msg_data, p_delivery_id);
END LOOP; --row_delivery_details
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_
x_msg_data := x_msg_summary || x_msg_
PROCEDURE ship_confirm(x_return_status& &OUT&&VARCHAR2,
x_msg_count& && & OUT&&NUMBER,
x_msg_data& && &&&OUT&&VARCHAR2,
p_delivery_id& &&&IN& &NUMBER)
-- 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);
--Initialize (user_id, resp_id, app_id, NULL)
fnd_global.apps_initialize( g_user_id, 2, NULL);
-- Initialize return status
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
-- Ship Confirming
p_delivery_name := TO_CHAR(p_delivery_id);
SELECT shipping_method_code
INTO l_ship_method_code
FROM oe_order_headers_all
WHERE header_id = g_header_
WHEN OTHERS THEN
l_ship_method_code := NULL;
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_&&-- 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_
fnd_file.put_line(fnd_file.log, 'Success:&&Ship confirm');
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_
x_msg_data := x_msg_summary || x_msg_
END SHIP_CONFIRM;
PROCEDURE submit_req
req_id&&NUMBER;
--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_
PROCEDURE create_install_base(p_line_id& && && &IN NUMBER,
x_return_status&&OUT VARCHAR2,
x_msg_count& && &OUT VARCHAR2,
x_msg_data& && & OUT VARCHAR2)
l_instance_rec& && && && & csi_datastructures_pub.instance_
l_ext_attrib_values_tbl& & csi_datastructures_pub.extend_attrib_values_
l_party_tbl& && && && && & csi_datastructures_pub.party_
l_account_tbl& && && && &&&csi_datastructures_pub.party_account_
l_pricing_attrib_tbl& && & csi_datastructures_pub.pricing_attribs_
l_org_assignments_tbl& && &csi_datastructures_pub.organization_units_
l_asset_assignment_tbl& &&&csi_datastructures_pub.instance_asset_
l_txn_rec& && && && && && &csi_datastructures_pub.transaction_
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';
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_ordered_quantity,
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_
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_
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_
SELECT csi_item_instances_s.nextval
INTO l_instance_id
l_instance_rec.instance_id := l_instance_
l_instance_rec.instance_number := l_instance_
l_instance_rec.external_reference := l_order_
l_instance_rec.inventory_item_id := l_inventory_item_
l_instance_rec.inv_master_organization_id := 86;
l_instance_rec.mfg_serial_number_flag := 'N';
l_instance_rec.quantity := l_ordered_
l_instance_rec.unit_of_measure := l_
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_
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
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;
WHEN OTHERS THEN
l_cust_account_id := NULL;
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
l_party_tbl(1).instance_party_id& && &:= l_instance_party_
l_party_tbl(1).instance_id& && && && &:= l_instance_
l_party_tbl(1).party_source_table& &&&:= 'HZ_PARTIES';
l_party_tbl(1).party_id& && && && && &:= row_user.party_
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
l_account_tbl(1).ip_account_id& && && & := l_ip_account_
l_account_tbl(1).instance_party_id& && &:= l_instance_party_
l_account_tbl(1).party_account_id& && & := l_cust_account_
l_account_tbl(1).relationship_type_code := 'DOCUMENT_USER';
l_account_tbl(1).active_start_date& && &:=
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 ');
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)
l_instance_id& && &&&NUMBER;
p_party_tbl& && && & csi_datastructures_pub.party_
p_txn_rec& && && && &csi_datastructures_pub.transaction_
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';
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_
FOR row_user IN cur_user(l_owner_party_id)
SELECT csi_i_parties_s.nextval
INTO l_i_parties_id
FROM DUAL;
/*p_relationship_tbl(1).RELATIONSHIP_ID& & & & & & & & := l_ii_rel_
p_relationship_tbl(1).RELATIONSHIP_TYPE_CODE& & & & := 'USED BY';
p_relationship_tbl(1).OBJECT_ID& & & & & & & & & & & & := row_user.party_
p_relationship_tbl(1).SUBJECT_ID& & & & & & & & & & & & := l_instance_
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,
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',
/* 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
--expire earily install base records
PROCEDURE expire_install_base(x_return_status& &OUT&&VARCHAR2,
x_msg_count& && & OUT&&NUMBER,
x_msg_data& && &&&OUT&&VARCHAR2)
l_instance_rec& && && & csi_datastructures_pub.instance_
l_txn_rec& && && && && &csi_datastructures_pub.transaction_
l_instance_id_lst& && & csi_datastructures_pub.id_
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_
no_need_expire& && && & EXCEPTION;
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_
FOR row_instance IN cur_instance(l_party_id, l_cust_account_id)
l_instance_rec.instance_id& && && &:= row_instance.instance_
l_instance_rec.instance_number& &&&:= row_instance.instance_
l_instance_rec.external_reference&&:= NULL;
l_instance_rec.inventory_item_id& &:= row_instance.inventory_item_
l_instance_rec.inv_master_organization_id := row_instance.inv_master_organization_
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
WHEN no_need_expire THEN
WHEN OTHERS THEN
PROCEDURE deal_install_base(x_return_status& &OUT&&VARCHAR2,
x_msg_count& && & OUT&&NUMBER,
x_msg_data& && &&&OUT&&VARCHAR2)
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_
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';
--get service number
--Get customer serial number and SIM attribute in quator
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;
WHEN OTHERS THEN
l_sim_num := NULL;
l_telephone_num := NULL;
IF l_sim_num IS NULL THEN
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;
WHEN OTHERS THEN
l_sim_num := NULL;
l_telephone_num := NULL;
--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;
--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 () THEN
FOR row_prod IN cur_prod
--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_
--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
--make product type for new install base
&&wait_for_install_base&&
SELECT COUNT(*)
INTO l_count
FROM csi_item_instances&&cii
WHERE cii.last_oe_order_line_id = g_line_
IF l_count=0 THEN
GOTO wait_for_install_
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_
--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_
--for suspension order, update status
IF l_line_type_id=1036 THEN
--update product lines at the same order
&&wait_for_all_install_base&&
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_
IF l_count&2 THEN
GOTO wait_for_all_install_
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
--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) ;
WHEN no_need_expire THEN
WHEN OTHERS THEN
END comms_om_
论坛徽章:9
这个很不错啊，mark一下，肯定有用的
论坛徽章:11
认证徽章论坛徽章:339
现在的项目不用OM，悲剧！
认证徽章论坛徽章:7
是很实用~顶了！
论坛徽章:55
这个好，。。。。。。
论坛徽章:11
很好很强大呀，这个东西
论坛徽章:17
支持　狠強大啊。
求职 : 论坛徽章:9
论坛徽章:0
这个不错，收藏！
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号