CREATE OR REPLACE PACKAGE BODY APPS.XX1003_SO_API
AS
PROCEDURE SO_CREATE
(errbuf IN OUT NOCOPY VARCHAR2
,errcode IN OUT NUMBER
,headerid IN NUMBER
,lineid IN NUMBER
,bill_seq_id IN NUMBER
,quantity IN NUMBER
,B_type IN VARCHAR2 DEFAULT 'STH'
,asset_number IN VARCHAR2 DEFAULT NULL
,user_line_desc IN VARCHAR2 DEFAULT NULL)
AS
--
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
--
----------------INPUT VARIABLES FOR PROCESS_ORDER API-------------------------
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.Request_Tbl_Type;
----------------OUT VARIABLES FOR PROCESS_ORDER API---------------------------
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
--
l_msg_index NUMBER;
l_data VARCHAR2(2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2(200);
--
-- Variable declarations ------
--
v_item_qty NUMBER;
v_inventory_item_id NUMBER;
v_party_id NUMBER;
v_sold_to_id NUMBER;
v_price_list NUMBER;
v_attribute1 VARCHAR2(100);
v_attribute2 VARCHAR2(100);
v_attribute3 VARCHAR2(100);
v_attribute4 VARCHAR2(100);
v_attribute5 VARCHAR2(100);
v_attribute6 VARCHAR2(100);
v_attribute7 VARCHAR2(100);
v_attribute8 VARCHAR2(100);
v_attribute9 VARCHAR2(100);
v_attribute10 VARCHAR2(100);
v_attribute11 VARCHAR2(100);
v_attribute12 VARCHAR2(100);
v_pricing_attribute1 VARCHAR2(100);
v_pricing_attribute2 VARCHAR2(100);
v_pricing_attribute3 VARCHAR2(100);
v_pricing_context VARCHAR2(100);
v_line_type_id NUMBER;
v_org_id NUMBER;
v_rtrn VARCHAR2(10) := '
';
--
--
CURSOR c_so_line IS
SELECT
org_id
,line_type_id
,inventory_item_id
,price_list_id
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
FROM
apps.oe_order_lines_all
WHERE
line_id = lineid;
--
--
CURSOR c_so_line_price IS
SELECT
PRICING_CONTEXT
,PRICING_ATTRIBUTE1
,PRICING_ATTRIBUTE2
,PRICING_ATTRIBUTE3
FROM
apps.oe_order_price_attribs
WHERE
line_id = lineid;
--
--
BEGIN
fnd_file.put_line(fnd_file.LOG,'- Begin ---------------------------------------------------');
-- Get all local Values from the order line ---
--
OPEN c_so_line;
FETCH c_so_line INTO v_org_id
,v_line_type_id
,v_inventory_item_id
,v_price_list
,v_attribute1
,v_attribute2
,v_attribute3
,v_attribute4
,v_attribute5
,v_attribute6
,v_attribute7
,v_attribute8
,v_attribute9
,v_attribute10
,v_attribute11
,v_attribute12;
CLOSE c_so_line;
--
----------------INITIALIZE DEBUG INFO-------------------------------------
--
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.INITIALIZE;
oe_debug_pub.setdebuglevel(5);
Oe_Msg_Pub.INITIALIZE;
--
----------------INITIALIZE ENVIRONMENT-------------------------------------
fnd_global.apps_initialize(1090,50370,7000);
#NAME?
----------------INITIALIZE HEADER RECORD------------------------------
l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
----------------POPULATE REQUIRED ATTRIBUTES ----------------------------------
l_header_rec.header_id := headerid;
l_header_rec.OPERATION := OE_GLOBALS.G_OPR_UPDATE;
----------------INITIALIZE ACTION REQUEST RECORD-------------------------------------
-- l_action_request_tbl(1) := OE_ORDER_PUB.G_MISS_REQUEST_REC;
----------------INITIALIZE LINE RECORD--------------------------------
--
l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
l_line_tbl(1).INVENTORY_ITEM_ID := v_inventory_item_id;
l_line_tbl(1).LINE_TYPE_ID := v_line_type_id;
l_line_tbl(1).ORDERED_QUANTITY := quantity;
l_line_tbl(1).price_list_id := v_price_list;
l_line_tbl(1).attribute1 := asset_number;--v_attribute1;
l_line_tbl(1).attribute2 := v_attribute2;
l_line_tbl(1).attribute3 := v_attribute3;
l_line_tbl(1).attribute4 := v_attribute4;
l_line_tbl(1).attribute5 := B_type;
l_line_tbl(1).attribute6 := v_attribute6;
l_line_tbl(1).attribute7 := v_attribute7;
l_line_tbl(1).attribute8 := v_attribute8;
l_line_tbl(1).attribute9 := v_attribute9;
l_line_tbl(1).attribute10 := 'AUTO';
--l_line_tbl(1).attribute11 := lineid;
l_line_tbl(1).attribute11 := bill_seq_id;
l_line_tbl(1).attribute12 := v_attribute12;
l_line_tbl(1).source_document_id := headerid;
l_line_tbl(1).source_document_line_id := lineid;
l_line_tbl(1).source_document_type_id := 2;
l_line_tbl(1).USER_ITEM_DESCRIPTION := user_line_desc;
l_line_tbl(1).OPERATION := OE_GLOBALS.G_OPR_CREATE;
--
----------------CALL TO PROCESS ORDER API---------------------------------
--
OE_Order_PUB.Process_Order
(p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
--
--- If Pricing Context found then insert else do nothing
BEGIN
--
OPEN c_so_line_price;
FETCH c_so_line_price
INTO v_pricing_context
,v_pricing_attribute1
,v_pricing_attribute2
,v_pricing_attribute3;
CLOSE c_so_line_price;
--
IF v_pricing_context IS NOT NULL THEN
--
fnd_file.put_line(fnd_file.LOG,'B_TYPE IS: ' || B_type);
fnd_file.put_line(fnd_file.LOG,'Line.line_id IS: ' || l_line_tbl_out(1).line_id);
errbuf := errbuf || v_rtrn || 'B_TYPE IS: ' || B_type ;
errbuf := errbuf || v_rtrn || 'Line.line_id IS: ' || l_line_tbl_out(1).line_id ;
--
UPDATE OE_ORDER_PRICE_ATTRIBS
SET PRICING_ATTRIBUTE3=B_type
WHERE HEADER_ID = l_header_rec.header_id
AND LINE_ID = l_line_tbl_out(1).line_id;
--
END IF;
--
END;
--
----------------Assign return status-----------------------------------
--
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
--
fnd_file.put_line(fnd_file.LOG,'API Success');
errbuf := errbuf || v_rtrn || 'API Success' ;
errcode := 0;
--
ELSE
--
fnd_file.put_line(fnd_file.LOG,'API Failure');
errbuf := errbuf || v_rtrn || 'API Failure' ;
ROLLBACK;
errcode := 2;
--
END IF;
--
---------------Display error messages-------------------------------------
--
FOR i IN 1 .. l_msg_count LOOP
--
Oe_Msg_Pub.GET
(p_msg_index => i
,p_encoded => Fnd_Api.G_FALSE
,p_data => l_data
,p_msg_index_out => l_msg_index);
fnd_file.put_line(fnd_file.LOG,'Message is: ' || l_data);
fnd_file.put_line(fnd_file.LOG,'Message index is: ' || l_msg_index);
errbuf := errbuf || v_rtrn || 'Message is: ' || l_data;
errbuf := errbuf || v_rtrn || 'Message index is: ' || l_msg_index;
--
END LOOP;
--
----------------Display return status flags------------------------------
--
fnd_file.put_line(fnd_file.LOG,'Process ORDER ret status IS: ' || l_return_status);
--fnd_file.put_line(fnd_file.LOG,'Process ORDER msg data IS: ' || l_msg_data);
fnd_file.put_line(fnd_file.LOG,'Process ORDER msg COUNT IS: ' || l_msg_count);
fnd_file.put_line(fnd_file.LOG,'Header.order_number IS: ' || TO_CHAR(l_header_rec_out.order_number));
fnd_file.put_line(fnd_file.LOG,'Header.return_status IS: ' || l_header_rec_out.return_status);
fnd_file.put_line(fnd_file.LOG,'Header.booked_flag IS: ' || l_header_rec_out.booked_flag);
fnd_file.put_line(fnd_file.LOG,'Header.header_id IS: ' || l_header_rec_out.header_id);
fnd_file.put_line(fnd_file.LOG,'Header.order_source_id IS: ' || l_header_rec_out.order_source_id);
fnd_file.put_line(fnd_file.LOG,'Header.flow_status_code IS: ' || l_header_rec_out.flow_status_code);
fnd_file.put_line(fnd_file.LOG,'Debug = ' || OE_DEBUG_PUB.G_DEBUG);
fnd_file.put_line(fnd_file.LOG,'Debug Level = ' || TO_CHAR(OE_DEBUG_PUB.G_DEBUG_LEVEL));
fnd_file.put_line(fnd_file.LOG,'Debug File = ' || OE_DEBUG_PUB.G_DIR||''||OE_DEBUG_PUB.G_FILE);
fnd_file.put_line(fnd_file.LOG,'- End ---------------------------------------------------');
OE_DEBUG_PUB.DEBUG_OFF;
errbuf := errbuf || v_rtrn || 'Process ORDER ret status IS: ' || l_return_status ;
--errbuf := errbuf || v_rtrn || 'Process ORDER msg data IS: ' || l_msg_data ;
errbuf := errbuf || v_rtrn || 'Process ORDER msg COUNT IS: ' || l_msg_count ;
errbuf := errbuf || v_rtrn || 'Header.order_number IS: ' || TO_CHAR(l_header_rec_out.order_number) ;
errbuf := errbuf || v_rtrn || 'Header.return_status IS: ' || l_header_rec_out.return_status ;
errbuf := errbuf || v_rtrn || 'Header.booked_flag IS: ' || l_header_rec_out.booked_flag ;
errbuf := errbuf || v_rtrn || 'Header.header_id IS: ' || l_header_rec_out.header_id ;
errbuf := errbuf || v_rtrn || 'Header.order_source_id IS: ' || l_header_rec_out.order_source_id ;
errbuf := errbuf || v_rtrn || 'Header.flow_status_code IS: ' || l_header_rec_out.flow_status_code ;
errbuf := errbuf || v_rtrn || 'Debug = ' || OE_DEBUG_PUB.G_DEBUG ;
errbuf := errbuf || v_rtrn || 'Debug Level = ' || TO_CHAR(OE_DEBUG_PUB.G_DEBUG_LEVEL) ;
errbuf := errbuf || v_rtrn || 'Debug File = ' || OE_DEBUG_PUB.G_DIR||''||OE_DEBUG_PUB.G_FILE ;
--
END SO_CREATE;
END XX1003_SO_API;
] 
Blog
Search
Help
Members
Register
Login
Home
