Sample script for Reserve Lot number for the order
PROCEDURE xx_bdg_Reserve_attribs (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_org_id NUMBER
)
IS
v_api_version_number NUMBER := 1;
v_return_status VARCHAR2 (2000);
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
x_msg_dat VARCHAR2 (4000);
l_out_index NUMBER;
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_ship_to_location NUMBER;
l_bill_to_location NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_error_flag VARCHAR2 (1) := ‘N’;
l_item_identifier_type hz_cust_site_uses_all.item_cross_ref_pref%TYPE;
g_debug_point VARCHAR2 (100);
g_debug VARCHAR2 (5)
:= fnd_profile.VALUE (‘AFLOG_LEVEL’);
— 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_line_price_att_tbl oe_order_pub.line_price_att_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 c
IS
SELECT DISTINCT order_number
FROM xx_bdg_so_stg
WHERE status = ‘SO CREATED’
AND interface_status = ‘S’
AND organization_id = p_org_id;
CURSOR c1 (p_order_number VARCHAR2)
IS
SELECT stg.batch_no, ooha.header_id, oola.line_id,
oola.inventory_item_id, stg.lot_number, oola.ordered_quantity,
oola.order_quantity_uom, stg.stg_id,PRE_STAGE_TRX_NO
FROM xx_bdg_so_stg stg,
oe_order_headers_all ooha,
oe_order_lines_all oola
WHERE stg.order_number = p_order_number
AND stg.status = ‘SO CREATED’
AND stg.interface_status = ‘S’
AND stg.order_number = ooha.order_number
AND stg.customer_id = ooha.sold_to_org_id
AND ooha.header_id = oola.header_id
AND stg.inventory_item_id = oola.inventory_item_id
AND stg.quantity = oola.ordered_quantity
AND stg.uom = oola.order_quantity_uom
AND stg.stg_id = oola.attribute10
AND stg.organization_id = p_org_id;
BEGIN
DBMS_OUTPUT.put_line (‘Starting of script’);
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 c_rec IN c
LOOP
l_error_flag := ‘N’;
FOR c1_rec IN c1 (c_rec.order_number)
LOOP
IF l_error_flag = ‘N’
THEN
DBMS_OUTPUT.put_line (‘Inner loop of script c_rec.order_number’|| c_rec.order_number);
v_header_rec := oe_order_pub.g_miss_header_rec;
v_header_rec.operation := oe_globals.g_opr_update;
v_header_rec.header_id := c1_rec.header_id;
l_count := 1;
v_line_tbl (l_count) := oe_order_pub.g_miss_line_rec;
v_line_tbl (l_count).operation := oe_globals.g_opr_update;
v_line_tbl (l_count).inventory_item_id :=
c1_rec.inventory_item_id;
v_line_tbl (l_count).line_id := c1_rec.line_id;
v_line_price_att_tbl (l_count) :=
oe_order_pub.g_miss_line_price_att_rec;
v_line_price_att_tbl (l_count).operation :=
oe_globals.g_opr_create;
v_line_price_att_tbl (l_count).pricing_context := ‘BATCH’;
v_line_price_att_tbl (l_count).pricing_attribute1 :=
c1_rec.lot_number;
v_line_price_att_tbl (l_count).flex_title :=
‘QP_ATTR_DEFNS_PRICING’;
v_line_price_att_tbl (l_count).line_index := 1;
l_batch_no := c1_rec.batch_no;
DBMS_OUTPUT.put_line ( l_batch_no
|| ‘ ‘
|| c1_rec.inventory_item_id
|| ‘ ‘
|| l_customer_id
|| ‘ ‘
|| c1_rec.lot_number
|| ‘ ‘
|| c1_rec.stg_id
);
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_line_price_att_tbl => v_line_price_att_tbl,
p_action_request_tbl => v_action_request_tbl,
p_line_adj_tbl => v_line_adj_tbl,
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’);
DBMS_OUTPUT.put_line (‘Return Status: ‘ || v_return_status);
IF v_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ( ‘Order Import Success : ‘
|| v_header_rec_out.header_id
|| ‘ ‘
|| v_header_rec_out.order_number
);
UPDATE xx_bdg_so_stg
SET interface_date = SYSDATE,
interface_status = fnd_api.g_ret_sts_success,
status = ‘LOT RESERVED’,
order_number = v_header_rec_out.order_number,
so_creation_date = SYSDATE,
so_header_id = v_header_rec_out.header_id,
error_message = NULL
WHERE 1 = 1 –batch_no = l_batch_no
AND stg_id = c1_rec.stg_id
and status = ‘SO CREATED’;
ELSE
DBMS_OUTPUT.put_line ( ‘Lot Creation failed:’
|| v_msg_data
|| ‘-‘
|| v_msg_count
);
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 (v_msg_data);
DBMS_OUTPUT.put_line (i || ‘) ‘ || v_msg_data);
END LOOP;
l_error_flag := ‘Y’;
UPDATE xx_bdg_so_stg
SET interface_status = v_return_status,
error_message = v_msg_data
WHERE 1 = 1
AND stg_id = c1_rec.stg_id;
COMMIT;
END IF;
ELSE
UPDATE xx_bdg_so_stg
SET interface_status = ‘E’,
error_message =’LOT Reserve Issue’
WHERE batch_no = l_batch_no;
END IF;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Unexceped Error:’ || SQLERRM);
END;
