Existem mecanismos que facilitam integrações entre os módulos, por exemplo para se criar uma ordem de venda, linhas, modificadores, créditos, pode se utilizar de duas formas, ou por API ou com integração direta pela interface, lembrando que as validações são as mesmas para as duas opções.
Abaixo segue exemplo de criação de Ordens de Venda:
Criação por API:
DECLARE
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;
-- 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;
BEGIN
fnd_global.apps_initialize(26300, 21623,660);
mo_global.set_policy_context('S', 0 );
--EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE= ''AMERICAN''';
mo_global.init ('ONT');
v_header_rec := NULL;
v_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
v_header_rec.org_id := 0;
v_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
v_header_rec.order_source_id := 1024;
v_header_rec.orig_sys_document_ref := 'PASO' || TO_CHAR(SYSDATE,'YYYYMMDDHH24MISSSSS');
v_header_rec.salesrep_id := 1000;
v_header_rec.ordered_date := SYSDATE;
v_header_rec.cust_po_number := 'Ordem de Compra cliente';
v_header_rec.order_type_id := 1285;
v_header_rec.sold_from_org_id := 196;
v_header_rec.sold_to_org_id := 3084;
v_header_rec.ship_to_org_id := 3375;
v_header_rec.invoice_to_org_id := 3374;
v_header_rec.price_list_id := 6680;
v_header_rec.fob_point_code := NULL;
v_header_rec.transactional_curr_code := 'USD';
v_header_rec.request_date := SYSDATE;
v_header_rec.booked_flag := 'Y';
v_header_rec.force_apply_flag := 'Y';
v_header_rec.payment_term_id := 1008;
v_header_rec.created_by := fnd_global.user_id;
v_header_rec.creation_date := sysdate;
v_header_rec.last_updated_by := fnd_global.user_id;
v_header_rec.last_update_date := sysdate;
v_header_rec.last_update_login := fnd_global.login_id;
v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
v_line_tbl (1) := oe_order_pub.g_miss_line_rec;
v_line_tbl (1).operation := oe_globals.g_opr_create;
v_line_tbl (1).inventory_item_id := 808632;
v_line_tbl (1).ordered_quantity := 1;
v_line_tbl (1).request_date := SYSDATE;
v_line_tbl (1).schedule_ship_date := SYSDATE;
v_line_tbl (1).calculate_price_flag := 'Y';
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);
DBMS_OUTPUT.PUT_LINE('Completion of API');
IF v_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Order Import Success : '||v_header_rec_out.order_number);
ELSE
DBMS_OUTPUT.put_line ('Order Import 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;
———————————————————-
Criação via Interface.
DECLARE v_oe_headers ont.oe_headers_iface_all%rowtype; v_oe_lines ont.oe_lines_iface_all%rowtype; BEGIN v_oe_headers := NULL; v_oe_headers.operation_code := 'INSERT'; v_oe_headers.order_source_id := v_order_source_id; v_oe_headers.orig_sys_document_ref := v_id_referencia||'-'||to_char(sysdate,'yymmddhh24misssss'); v_oe_headers.org_id := v_org_id; v_oe_headers.ordered_date := v_data; v_oe_headers.order_type_id := v_order_type_id; v_oe_headers.sold_from_org_id := v_organization_id; v_oe_headers.sold_to_org_id := v_cust_account_id; v_oe_headers.customer_id := v_cust_account_id; v_oe_headers.ship_to_org_id := v_ship_site_use_id; v_oe_headers.invoice_to_org_id := v_bill_site_use_id; v_oe_headers.salesrep_id := -3; v_oe_headers.price_list_id := v_price_list_id; v_oe_headers.fob_point_code := v_fob_point_code; v_oe_headers.transactional_curr_code := v_currency_code; v_oe_headers.request_date := v_data; v_oe_headers.booked_flag := 'Y'; v_oe_headers.force_apply_flag := 'Y'; v_oe_headers.payment_term_id := 1005; v_oe_headers.created_by := fnd_global.user_id; v_oe_headers.creation_date := sysdate; v_oe_headers.last_updated_by := fnd_global.user_id; v_oe_headers.last_update_date := sysdate; v_oe_headers.last_update_login := fnd_global.login_id; INSERT INTO ont.oe_headers_iface_all values v_oe_headers; v_oe_lines.order_source_id := v_order_source_id; v_oe_lines.orig_sys_document_ref := v_oe_headers.orig_sys_document_ref; v_oe_lines.orig_sys_line_ref := v_id_referencia_linha; v_oe_lines.org_id := v_org_id; v_oe_lines.sold_from_org_id := v_organization_id; v_oe_lines.sold_to_org_id := v_cust_account_id; v_oe_lines.ship_from_org_id := v_organization_id; v_oe_lines.ship_to_org_id := v_ship_site_use_id; v_oe_lines.line_number := v_line_number; v_oe_lines.item_type_code := 'STANDARD'; v_oe_lines.request_date := v_request_date; v_oe_lines.inventory_item_id := v_inventory_item_id; v_oe_lines.ordered_quantity := v_quantidade; v_oe_lines.fob_point_code := v_fob_point_code; v_oe_lines.order_quantity_uom := v_primary_uom_code; v_oe_lines.price_list_id := v_price_list_id; v_oe_lines.unit_selling_price := v_preco_unitario; -- v_oe_lines.created_by := fnd_global.user_id; v_oe_lines.creation_date := sysdate; v_oe_lines.last_updated_by := fnd_global.user_id; v_oe_lines.last_update_date := sysdate; v_oe_lines.last_update_login := fnd_global.login_id; INSERT INTO ont.oe_lines_iface_all VALUES v_oe_lines; COMMIT; -- Submete concorrente v_request_id := fnd_request.submit_request ( 'ONT', 'OEOIMP', '', NULL, FALSE, --Parametros p_org_id, p_order_source_id, p_orig_sys_document_ref, NULL, 'N', 1, 4, NULL, NULL, NULL, 'N', 'Y' ); -- Aguarda conclusão IF NOT (fnd_concurrent.wait_for_request(v_request_id, 0, 0, v_phase, v_status, v_dev_phase, v_dev_status, v_message ) ) THEN raise_application_error(-20001,'O concorrente não foi concluído com sucesso.'); END IF; END;
No final desses processos a Ordem de venda será criada, essas opções de API’s e interfaces que a Oracle disponibiliza, facilita a integrações com outros sistemas
Neste processo também, podemos alterar, dividir ou cancelar linhas de uma ordem de venda. Basta alterar o corpo do código com as informações abaixo.
Alterar Linhas
v_line_rec(i).operation := OE_GLOBALS.G_OPR_UPDATE; v_line_rec(i).line_id := p_line_rec(i).oe_line_id; v_line_rec(i).request_date := p_line_rec(i).request_date; v_line_rec(i).schedule_ship_date := p_line_rec(i).schedule_ship_date; v_line_rec(i).change_reason := p_line_rec(i).cancel_reason;
Cancelar Linhas
DECLARE
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;
-- 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_rec 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;
BEGIN
mo_global.init ('ONT');
mo_global.set_policy_context ('S', 81);
fnd_global.apps_initialize (1139, 21623, 660); -- user_id, resp_id, resp_appl_id
oe_msg_pub.initialize;
oe_debug_pub.initialize;
v_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
v_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
v_line_tbl(1).header_id := 14084;
v_line_tbl(1).line_id := 13035;
v_line_tbl(1).ordered_quantity := 0;
v_line_tbl(1).cancelled_flag := 'Y';
v_line_tbl(1).change_reason := 'NOT PROVIDED';
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,
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 ('Line Cancelation in Existing Order is Success ');
ELSE
DBMS_OUTPUT.put_line ('Line Cancelation in Existing Order 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;
Dividir Linhas
DECLARE
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;
-- 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_rec 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;
BEGIN
mo_global.init ('ONT');
mo_global.set_policy_context ('S', 81);
fnd_global.apps_initialize (1139, 21623, 660); -- user_id, resp_id, resp_appl_id
oe_msg_pub.initialize;
oe_debug_pub.initialize;
v_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
v_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
v_line_tbl(1).header_id := 14084;
v_line_tbl(1).line_id := 10078;
v_line_tbl(1).split_by := 1139;
v_line_tbl(1).split_action_code := 'SPLIT';
v_line_tbl(1).ordered_quantity := 1;
v_line_tbl(2).operation := OE_GLOBALS.G_OPR_CREATE;
v_line_tbl(2).split_by := 1139;
v_line_tbl(2).split_action_code := 'SPLIT';
v_line_tbl(2).split_from_line_id := 10078;
v_line_tbl(2).ordered_quantity := 2;
v_line_tbl(2).split_request_date := 'Y';
v_line_tbl(2).request_date := SYSDATE;
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,
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 Import Success : ' || v_header_rec_out.order_number);
ELSE
DBMS_OUTPUT.put_line ('Order Import 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;
Deixe uma resposta