Criar, Alterar, Dividir e Cancelar Linhas de Ordens de Venda

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;

Sem título

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

Powered by WordPress.com. Tema: Baskerville 2 por Anders Noren

Acima ↑

%d blogueiros gostam disto: