This code was developed on Oracle Applications 11.5.10.2. For R12, may be you need to do some modifications.
CREATE OR REPLACE PROCEDURE xx_parts_sales_order_api (
p_parts_header_id IN NUMBER,
p_order_type_id IN NUMBER,
p_order_type IN VARCHAR2,
p_msg OUT VARCHAR2
)
AS
/**************************************************************************
* Copyright (c) 200 **** ****
* All rights reserved
**************************************************************************
* HEADER
* $Id: G.prc v 1.1
*
* PROGRAM NAME
* Parts_Sales_Order_Api.prc
*
* DESCRIPTION
* Procedure to create SO from Dealer Orders
*
* USAGE
* Procedure to create SO from Dealer Orders
*
* PARAMETERS
* ==========
* NAME DESCRIPTION
* ----------------- ------------------------------------------------------
** DEPENDENCIES
* No
** CALLED BY
* DCreate SO Button
*
* HISTORY
* =======*
* VERSION DATE AUTHOR(S) DESCRIPTION
* ------- ----------- --------------- ------------------------------------
* 1.1 24-Aug-2008 XXX XXXX
*************************************************************************/
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
/*****************PARAMETERS****************************************************/
l_debug_level NUMBER := 0;
-- OM DEBUG LEVEL (MAX 5)
l_org NUMBER := fnd_profile.VALUE ('ORG_ID');
-- OPERATING UNIT
l_no_orders NUMBER := 1;
-- NO OF ORDERS It will be always one as it is created from the form
l_user NUMBER := fnd_profile.VALUE ('USER_ID');
-- USER
l_resp NUMBER := fnd_profile.VALUE ('RESP_ID');
-- RESPONSIBLILTY
l_appl NUMBER
:= fnd_profile.VALUE ('RESP_APPL_ID');
-- ORDER MANAGEMENT
/*****************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;
/**************************Cursor to fetch data from the Dealer Order Entry Form********************************/
CURSOR cur_parts_so
IS
SELECT inventory_item_id, item_name, quantity, unit_selling_price,
line_discount, line_number, parts_header_id, uom
FROM xx_dealparts_lines_v gdl
WHERE gdl.parts_header_id = p_parts_header_id;
cur_parts_rec cur_parts_so%ROWTYPE;
/*****************************CURSOR TO FETCH LINES FOR RESERVATIONS*******************************************/
CURSOR cur_reserve_line
IS
SELECT header_id, line_id, line_type_id, order_quantity_uom,
ship_from_org_id, inventory_item_id, item_revision,
NULL subinventory_code, ' ' demand_source_name,
2 demand_source_type_id, ordered_quantity
FROM oe_order_lines_v
WHERE attribute15 = p_parts_header_id;
cur_reserve_line_rec cur_reserve_line%ROWTYPE;
ln_reservation_id NUMBER;
ln_reservation_status VARCHAR2 (1);
ln_reserved_quantity NUMBER;
/***************************************************************************************************************/
l_msg_index NUMBER;
l_data VARCHAR2 (2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2 (200);
ln_sold_to_org_id NUMBER;
ln_price_list_id NUMBER;
lv_sell_price_curr VARCHAR2 (3);
ln_line_no NUMBER;
-- book API vars
b_return_status VARCHAR2 (200);
b_msg_count NUMBER;
b_msg_data VARCHAR2 (2000);
BEGIN
DBMS_APPLICATION_INFO.set_client_info (l_org);
/*****************INITIALIZE DEBUG INFO*************************************/
IF (l_debug_level > 0)
THEN
l_debug_file := oe_debug_pub.set_debug_mode ('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel (l_debug_level);
oe_msg_pub.initialize;
END IF;
DBMS_OUTPUT.put_line ('Step 1');
/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize (l_user, l_resp, l_appl);
-- pass IN user_id, responsibility_id, AND application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.g_miss_header_rec;
/*********Write validations for getting variable values**************************/
BEGIN
SELECT dealer_id
INTO ln_sold_to_org_id
FROM xx_dealparts_headers_v gdh
WHERE gdh.parts_header_id = p_parts_header_id;
EXCEPTION
WHEN OTHERS
THEN
p_msg := 'Check customer setup Bill To Location';
DBMS_OUTPUT.put_line ('Check customer setup Bill To Location');
END;
BEGIN
SELECT DISTINCT price_list_id
INTO ln_price_list_id
FROM oe_order_types_v
WHERE order_type_id = p_order_type_id;
EXCEPTION
WHEN OTHERS
THEN
p_msg :=
'Check Price List against the Order Type - ' || p_order_type;
DBMS_OUTPUT.put_line
( 'Check Price List against the Order Type - '
|| p_order_type
);
END;
BEGIN
SELECT currency_code
INTO lv_sell_price_curr
FROM qp_secu_list_headers_v
WHERE list_header_id = ln_price_list_id;
EXCEPTION
WHEN OTHERS
THEN
p_msg := 'Check selling price currency in parts price list';
DBMS_OUTPUT.put_line
('Check selling price currency in parts price list');
END;
/*****************POPULATE REQUIRED ATTRIBUTES **********************************/
DBMS_OUTPUT.put_line ('Step 2');
l_header_rec.operation := oe_globals.g_opr_create;
l_header_rec.order_type_id := p_order_type_id;
l_header_rec.sold_to_org_id := ln_sold_to_org_id;
l_header_rec.price_list_id := ln_price_list_id;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := lv_sell_price_curr;
l_header_rec.flow_status_code := 'ENTERED';
l_header_rec.cust_po_number := NULL;
l_header_rec.attribute14 := p_parts_header_id;
DBMS_OUTPUT.put_line ('Step 3');
/*****************INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
/*****************INITIALIZE LINE RECORD********************************/
OPEN cur_parts_so;
LOOP
FETCH cur_parts_so
INTO cur_parts_rec;
EXIT WHEN cur_parts_so%NOTFOUND;
ln_line_no := NVL (ln_line_no, 0) + 1;
l_line_tbl (ln_line_no) := oe_order_pub.g_miss_line_rec;
l_line_tbl (ln_line_no).operation := oe_globals.g_opr_create;
l_line_tbl (ln_line_no).inventory_item_id :=
cur_parts_rec.inventory_item_id;
--32039;--201775;
l_line_tbl (ln_line_no).ordered_quantity := cur_parts_rec.quantity;
l_line_tbl (ln_line_no).calculate_price_flag := 'N';
l_line_tbl (ln_line_no).tax_exempt_flag := 'S';
l_line_tbl (ln_line_no).attribute14 := cur_parts_rec.line_number;
l_line_tbl (ln_line_no).attribute15 := cur_parts_rec.parts_header_id;
l_line_tbl (ln_line_no).unit_list_price :=
( ( NVL (cur_parts_rec.quantity, 0)
* NVL (cur_parts_rec.unit_selling_price, 0)
)
- ( ( NVL (cur_parts_rec.quantity, 0)
* NVL (cur_parts_rec.unit_selling_price, 0)
)
* (NVL (cur_parts_rec.line_discount, 0) / 100)
)
)
/ cur_parts_rec.quantity;
l_line_tbl (ln_line_no).unit_selling_price :=
( ( NVL (cur_parts_rec.quantity, 0)
* NVL (cur_parts_rec.unit_selling_price, 0)
)
- ( ( NVL (cur_parts_rec.quantity, 0)
* NVL (cur_parts_rec.unit_selling_price, 0)
)
* (NVL (cur_parts_rec.line_discount, 0) / 100)
)
)
/ cur_parts_rec.quantity;
DBMS_OUTPUT.put_line ('Step 4');
END LOOP;
CLOSE cur_parts_so;
FOR i IN 1 .. l_no_orders
LOOP -- BEGIN LOOP
/*****************CALLTO PROCESS ORDER API*********************************/
DBMS_OUTPUT.put_line ('Step 5');
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,
-- OUT variables
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
);
DBMS_OUTPUT.put_line ( 'Step 6 - '
|| l_return_status
|| ' - '
|| l_debug_level
|| ' - '
|| SQLERRM
|| ' - '
|| fnd_api.g_ret_sts_success
);
/************************************************************************/
-- open cur_reserve_line;
FOR cur_reserve_line_rec IN cur_reserve_line
LOOP
ln_reservation_id := NULL;
ln_reservation_status := NULL;
ln_reserved_quantity := NULL;
cur_reserve_line_rec.header_id := NULL;
cur_reserve_line_rec.header_id := NULL;
cur_reserve_line_rec.order_quantity_uom := NULL;
cur_reserve_line_rec.inventory_item_id := NULL;
cur_reserve_line_rec.ship_from_org_id := NULL;
-- p_msg := cur_reserve_line_rec.header_id||' - '||cur_reserve_line_rec.line_id;
--Fetch cur_reserve_line into cur_reserve_line_rec;
--EXIT WHEN cur_reserve_line%NOTFOUND;
xx_so_reservation_api.insert_reservations
(p_reservation_date => SYSDATE,
p_ship_from_org_id => cur_reserve_line_rec.ship_from_org_id,
p_inventory_item_id => cur_reserve_line_rec.inventory_item_id,
p_demand_source_type_id => cur_reserve_line_rec.demand_source_type_id,
p_demand_source_name => NULL
--cur_reserve_line_rec.demand_source_name
,
p_demand_source_header_id => cur_reserve_line_rec.header_id,
p_demand_source_line_id => cur_reserve_line_rec.line_id,
p_uom_code => cur_reserve_line_rec.order_quantity_uom,
p_quantity => cur_reserve_line_rec.ordered_quantity,
p_revision => cur_reserve_line_rec.item_revision
-- Item revision\par
,
p_subinventory_code => NULL
--cur_reserve_line_rec.subinventory_code-- Subinventory Code\par
,
p_locator_id => NULL -- Locator ID\par
,
p_lot_number => NULL -- Lot Number\par
,
p_reservation_id => ln_reservation_id
-- Reservation ID\par
,
p_status => ln_reservation_status
-- Status 'S' - Success, 'E' - error\par
,
p_reserved_qty => ln_reserved_quantity
);
END LOOP;
--close cur_reserve_line;
/*****************CHECK RETURN STATUS***********************************/
IF l_return_status = fnd_api.g_ret_sts_success
THEN
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('success');
END IF;
COMMIT;
oe_order_book_util.complete_book_eligible
(1.0,
fnd_api.g_false,
l_header_rec_out.header_id,
b_return_status,
b_msg_count,
b_msg_data
);
COMMIT;
ELSE
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('failure');
END IF;
ROLLBACK;
END IF;
END LOOP; -- END LOOP
/*****************DISPLAY RETURN STATUS FLAGS******************************/
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('process ORDER ret status IS: ' || l_return_status
);
DBMS_OUTPUT.put_line ('process ORDER msg DATA IS: ' || l_msg_data);
DBMS_OUTPUT.put_line ('process ORDER msg COUNT IS: ' || l_msg_count);
DBMS_OUTPUT.put_line ( 'HEADER.order_number IS: '
|| TO_CHAR (l_header_rec_out.order_number)
);
DBMS_OUTPUT.put_line ( 'HEADER.return_status IS: '
|| l_header_rec_out.return_status
);
DBMS_OUTPUT.put_line ( 'HEADER.booked_flag IS: '
|| l_header_rec_out.booked_flag
);
DBMS_OUTPUT.put_line ( 'HEADER.header_id IS: '
|| l_header_rec_out.header_id
);
DBMS_OUTPUT.put_line ( 'HEADER.order_source_id IS: '
|| l_header_rec_out.order_source_id
);
DBMS_OUTPUT.put_line ( 'HEADER.flow_status_code IS: '
|| l_header_rec_out.flow_status_code
);
END IF;
/*****************DISPLAY ERROR MSGS*************************************/
IF (l_debug_level > 0)
THEN
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
);
DBMS_OUTPUT.put_line ('message IS: ' || l_data);
DBMS_OUTPUT.put_line ('message INDEX IS: ' || l_msg_index);
END LOOP;
END IF;
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('DEBUG = ' || oe_debug_pub.g_debug);
DBMS_OUTPUT.put_line ( 'DEBUG LEVEL = '
|| TO_CHAR (oe_debug_pub.g_debug_level)
);
DBMS_OUTPUT.put_line ( 'DEBUG FILE = '
|| oe_debug_pub.g_dir
|| '/'
|| oe_debug_pub.g_file
);
DBMS_OUTPUT.put_line
('****************************************************');
oe_debug_pub.debug_off;
END IF;
END;
No comments:
Post a Comment