Here the user wants a menu, once he clicks on the menu then charges field should be populated.
Below things need to developed for the requirement.
1) setup for discounts at header level.
2) Database procedure for calling the API to populate the charges in OE_PRICE_ADJUSTMENTS table.
3) Personalization for creating menu, call the procedure by using the menu.
Solution:
1) Login to Order Management Super user, and navigate to Modifiers.
Pricing--> Modifiers
Setup the Modifier Type,Name and Number in header section, and in the line I have provided level as Order since my requirement is to populate at header
level.
Once setup is done the values can be queried in the tables QPFV_MODIFIER_HEADERS and QPFV_MODIFIER_LINES.
2) Create the database procedure to call the API OE_ORDER_PUB.PROCESS_ORDER for populating the charges.
In my requirement Sales Order is already present, which was created by user manually. Now I need to update the charges, in the manual process for populating
the charges user has to click on the Actions Button and then enter the charge Name, Amount and rest details, and there by the charges field will be populated
with the value.
In the automated process, now I am populated the charges lines by using the process_order API.
For this procedure, provide the Order Number as the parameter.
create or replace procedure XXAJ_CREATESALESORDER(p_so_num VARCHAR2) IS
v_api_version_number NUMBER := 1;
v_return_status VARCHAR2 (2000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
-- IN Variables --
v_header_rec oe_order_pub.header_rec_type;
v_line_tbl oe_order_pub.line_tbl_type;
v_action_request_tbl oe_order_pub.request_tbl_type;
v_line_adj_tbl oe_order_pub.line_adj_tbl_type;
v_hdr_adj_tbl oe_order_pub.Header_Adj_Tbl_Type;
v_list_header_id number;
v_list_line_id number;
v_operand varchar2(10);
v_ln_type_code varchar2(10);
v_operator varchar2(10);
v_phase_id number;
v_mod_level_code varchar2(10);
t_line_id number;
t_header_id number;
t_org_id number;
l_pricing_quantity NUMBER;
l_unit_selling_price NUMBER;
l_attribute1 NUMBER;
l_header_id NUMBER;
l_unit_price NUMBER;
l_operand NUMBER;
-- OUT Variables --
v_header_rec_out oe_order_pub.header_rec_type;
v_header_val_rec_out oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out oe_order_pub.line_tbl_type;
v_line_val_tbl_out oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out oe_order_pub.request_tbl_type;
v_msg_index NUMBER;
v_data VARCHAR2 (2000);
v_loop_count NUMBER;
v_debug_file VARCHAR2 (200);
b_return_status VARCHAR2 (200);
b_msg_count NUMBER;
b_msg_data VARCHAR2 (2000);
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting of script');
SELECT hdr.list_header_id,
lin.list_line_id
into v_list_header_id,
v_list_line_id
FROM QPFV_MODIFIER_HEADERS hdr,
QPFV_MODIFIER_LINES lin
WHERE 1=1
and lin.list_header_id = hdr.list_header_id
AND lin.list_line_type_code ='FREIGHT_CHARGE'
AND lin.modifier_level_code = 'ORDER'
AND hdr.name = 'Transport Charge -3';
select l.pricing_quantity,l.unit_selling_price,l.attribute1,h.header_id
INTO l_pricing_quantity ,
l_unit_selling_price ,
l_attribute1 ,
l_header_id
from OE_ORDER_HEADERS_ALL h,OE_ORDER_lines_ALL l
where l.header_id = h.header_id
AND h.order_number = p_so_num;
select l.unit_price INTO l_unit_price from po_headers_all h, po_lines_all l
where segment1 = l_attribute1
AND h.po_header_id = l.po_header_id;
-- Adjustment Variables
v_hdr_adj_tbl :=oe_order_pub.G_MISS_HEADER_ADJ_TBL;
v_hdr_adj_tbl(1).header_id := l_header_id;--3778;
v_hdr_adj_tbl(1).automatic_flag := 'N';
v_hdr_adj_tbl(1).list_line_type_code := 'FREIGHT_CHARGE';
v_hdr_adj_tbl(1).change_reason_code := 'MANUAL';
v_hdr_adj_tbl(1).operand := 5; -- The value which appears in charges field at header level
v_hdr_adj_tbl(1).charge_type_code := 'FREIGHT';
v_hdr_adj_tbl(1).change_reason_text := 'Manually applied adjustments';
v_hdr_adj_tbl(1).modifier_level_code := 'ORDER';
v_hdr_adj_tbl(1).updated_flag := 'Y';
v_hdr_adj_tbl(1).applied_flag := 'Y';
v_hdr_adj_tbl(1).arithmetic_operator := 'LUMPSUMP';
v_hdr_adj_tbl(1).adjusted_amount := '5';
v_hdr_adj_tbl(1).price_adjustment_id := oe_price_adjustments_s.nextval;
v_hdr_adj_tbl(1).creation_date := sysdate;
v_hdr_adj_tbl(1).created_by := FND_GLOBAL.USER_ID;
v_hdr_adj_tbl(1).last_update_date := sysdate;
v_hdr_adj_tbl(1).last_updated_by := FND_GLOBAL.USER_ID;
v_hdr_adj_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
v_hdr_adj_tbl(1).list_header_id := v_list_header_id;
v_hdr_adj_tbl(1).list_line_id := v_list_line_id;
v_hdr_adj_tbl(1).arithmetic_operator := 'LUMPSUM';
v_hdr_adj_tbl(1).orig_sys_discount_ref := 'OE_PRICE_ADJUSTMENTS'||v_hdr_adj_tbl(1).price_adjustment_id;
v_hdr_adj_tbl(1).operation := oe_globals.g_opr_create;
v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
DBMS_OUTPUT.PUT_LINE('Starting of API');
-- Calling the API to update the header details of an existing Order --
OE_ORDER_PUB.PROCESS_ORDER (
p_api_version_number => v_api_version_number
, p_header_rec => v_header_rec
, p_line_tbl => v_line_tbl
, p_Header_Adj_tbl => v_hdr_adj_tbl
-- OUT variables
, x_header_rec => v_header_rec_out
, x_header_val_rec => v_header_val_rec_out
, x_header_adj_tbl => v_header_adj_tbl_out
, x_header_adj_val_tbl => v_header_adj_val_tbl_out
, x_header_price_att_tbl => v_header_price_att_tbl_out
, x_header_adj_att_tbl => v_header_adj_att_tbl_out
, x_header_adj_assoc_tbl => v_header_adj_assoc_tbl_out
, x_header_scredit_tbl => v_header_scredit_tbl_out
, x_header_scredit_val_tbl => v_header_scredit_val_tbl_out
, x_line_tbl => v_line_tbl_out
, x_line_val_tbl => v_line_val_tbl_out
, x_line_adj_tbl => v_line_adj_tbl_out
, x_line_adj_val_tbl => v_line_adj_val_tbl_out
, x_line_price_att_tbl => v_line_price_att_tbl_out
, x_line_adj_att_tbl => v_line_adj_att_tbl_out
, x_line_adj_assoc_tbl => v_line_adj_assoc_tbl_out
, x_line_scredit_tbl => v_line_scredit_tbl_out
, x_line_scredit_val_tbl => v_line_scredit_val_tbl_out
, x_lot_serial_tbl => v_lot_serial_tbl_out
, x_lot_serial_val_tbl => v_lot_serial_val_tbl_out
, x_action_request_tbl => v_action_request_tbl_out
, x_return_status => v_return_status
, x_msg_count => v_msg_count
, x_msg_data => v_msg_data
);
DBMS_OUTPUT.PUT_LINE('Completion of API');
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Order Header Updation Success : '||v_header_rec_out.header_id);
ELSE
DBMS_OUTPUT.put_line ('Order Header Updation failed:'||v_msg_data);
ROLLBACK;
FOR i IN 1 .. v_msg_count
LOOP
v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| v_msg_data);
END LOOP;
END IF;
END xxaj_createsalesorder;
3) Now the final part is to call the database procedure from the forms personalization, here I have two steps.
i) Create a new Menu
When new form instance, go to actions and provide the Menu label for any Special Trigger.
ii) Now In condition the trigger event will be special event, go to actions click on built in , builtin type is execute procedure. Call the procedure with
the Order Number as parameter.
Also add another action execute_query to refersh the form, so that charges will appear immediately.
Now the requirement is accomplished.
Let me know your comments... this has worked perfectly for me.
