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