Book Sales Order

Sample script to book the sales Order

PROCEDURE xx_book_bdg_so (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_org_id NUMBER
)
AS
v_api_version_number NUMBER := 1;
v_return_status VARCHAR2 (2000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
l_price_list_id NUMBER;
l_customer_id NUMBER;
l_salesrep_id NUMBER;
l_err_flag VARCHAR2 (1) := ‘N’;
l_count NUMBER := 0;
l_batch_no NUMBER;
l_user_id NUMBER;
l_appl_id NUMBER;
l_resp_id NUMBER;
— 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;
— 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;

CURSOR c1
IS
SELECT DISTINCT ooh.header_id, ooh.order_number, pre_stage_trx_no
FROM xxx_bdg_so_stg stg, apps.oe_order_headers_all ooh
WHERE stg.order_number = ooh.order_number
–AND stg.reservation_id IS NOT NULL reservation is taken care by trigger itself. not populated
AND stg.status = ‘LOT RESERVED’
AND stg.interface_status = ‘S’
AND organization_id = p_org_id;
/*
SELECT header_id,
order_number
FROM oe_order_headers_all
WHERE order_number IN (11991800125);
*/
BEGIN
DBMS_OUTPUT.put_line (‘Starting of script’);
— Setting the Enviroment —
mo_global.init (‘ONT’);

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘XAVIER’;

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘India Local Order Management’
AND NVL (TRUNC (end_date), TRUNC (SYSDATE)) >= TRUNC (SYSDATE);

fnd_global.apps_initialize (user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_appl_id
);
mo_global.set_policy_context (‘S’, 82);

FOR c1_rec IN c1
LOOP

v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
v_action_request_tbl (1).request_type := oe_globals.g_book_order;
v_action_request_tbl (1).entity_code := oe_globals.g_entity_header;
v_action_request_tbl (1).entity_id := c1_rec.header_id;
v_action_request_tbl (1).entity_index := 1;
DBMS_OUTPUT.put_line (‘Starting of API’);

BEGIN
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_action_request_tbl => v_action_request_tbl,
p_line_adj_tbl => v_line_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
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘API raised error ‘ || SQLERRM);
END;

DBMS_OUTPUT.put_line (‘Completion of API’);
fnd_file.put_line (fnd_file.LOG, ‘Start Book return status ‘|| v_return_status);

IF v_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ( ‘Order Booking Success : ‘
|| v_action_request_tbl (1).entity_id
|| ‘ ‘
|| v_header_rec_out.order_number
);
fnd_file.put_line (fnd_file.LOG, ‘Start Book return success ‘|| v_return_status);

UPDATE xxx_bdg_so_stg
SET interface_date = SYSDATE,
interface_status =
(SELECT DECODE (flow_status_code,
‘BOOKED’, fnd_api.g_ret_sts_success,
‘E’
)
FROM oe_order_headers_all
WHERE header_id = c1_rec.header_id),
status =
(SELECT DECODE (flow_status_code,
‘BOOKED’, ‘SO BOOKED’,
‘LOT RESERVED’
)
FROM oe_order_headers_all
WHERE header_id = c1_rec.header_id),
so_booked_date = SYSDATE
WHERE order_number = c1_rec.order_number
AND status = ‘LOT RESERVED’
AND organization_id = p_org_id;

FOR rec_1 IN (SELECT *
FROM xxx_bdg_so_stg
WHERE order_number = c1_rec.order_number
AND status = ‘LOT RESERVED’
AND organization_id = p_org_id)
LOOP
begin
UPDATE xapex.xxx_bgt_cdt_dbt_dtls_tb
SET validated_flag =
(SELECT DECODE (flow_status_code,
‘BOOKED’, ‘SO BOOKED’,
‘LOT RESERVED’
)
FROM oe_order_headers_all
WHERE header_id = rec_1.so_header_id)
WHERE cn_nc_id = rec_1.pre_stage_trx_no
and validated_flag = ‘LOT RESERVED’;

UPDATE x_ra_bdg_cn_intr_lines_stg
SET status_flag =
(SELECT DECODE (flow_status_code,
‘BOOKED’, ‘SO BOOKED’,
‘LOT RESERVED’
)
FROM oe_order_headers_all
WHERE header_id = rec_1.so_header_id)
WHERE to_char(interface_line_attribute1) = to_char(rec_1.pre_stage_trx_no)
and status_flag = ‘LOT RESERVED’;
exception when others then
null;
end;
END LOOP;

COMMIT;
ELSE

fnd_file.put_line (fnd_file.LOG, ‘Order Booking failed:’ || v_msg_data);

DBMS_OUTPUT.put_line (‘Order Booking 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;
begin
UPDATE apps.xxx_bdg_so_stg
SET interface_status = v_return_status,
error_message = ‘LOT RESERVED ISSUE’ || v_msg_data
WHERE order_number = c1_rec.order_number
AND status = ‘LOT RESERVED’
AND organization_id = p_org_id;

end;
COMMIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Unexceped Error:’ || SQLERRM);
END;

Recent Posts