Home » Applications » Oracle Fusion Apps & E-Business Suite » Order Management (R12)
 Order Management [message #472362] |
Fri, 20 August 2010 03:41 |
lokeshsurana
Messages: 212
Registered: April 2010
Location: India
|
Senior Member |
|
|
Hi
Its related to generate bill of Asset Usage
my process flow is...
as Sales Order generate(standard form)
-> Bill Scheduling (Custom Form)
-> Asset usage entry
->run the request Custom Billing process (it launch workflow the code is included below)
->Usage approval
-> Bill approval
Currently if default asset usage is 200 and customer uses 200 then bill generate correctly but if customer use 220 then extra charge for 20 is not getting added
The code is as follow ->
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;
please provide way to resolve this problem............
|
|
|
|
Goto Forum:
Current Time: Mon May 05 08:02:47 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
