[API] Sample of API to copy sales order (12.1.3)

 DECLARE

  vOrgId        oe_order_headers_all.org_id%TYPE := 00;

  vHeaderType   oe_transaction_types_all.transaction_type_id%TYPE := 00;

  vLineType     oe_transaction_types_all.transaction_type_id%TYPE := 00;

  vOriHeaderId  oe_order_headers_all.header_id%TYPE := 00;

  recCopy       oe_order_copy_util.copy_rec_type := oe_order_copy_util.get_copy_rec;

  headertab     oe_globals.selected_record_tbl;

  linetab       oe_globals.selected_record_tbl;

  newHeaderId   oe_order_headers_all.header_id%TYPE;

  vReturnStatus VARCHAR2(1);

  vMessageCount NUMBER;

  vMessageData  VARCHAR2(240);

  vMessage      VARCHAR2(2000);

  xOrderNumber oe_order_headers_v.order_number%TYPE;

  xOrderType   oe_order_headers_v.order_type%TYPE;

  xOrderSource VARCHAR2(200); --oe_order_headers_v.order_source%TYPE;

BEGIN

  headertab.delete;

  fnd_global.apps_initialize(00, 00, 660);

  mo_global.init('ONT');

  mo_global.set_policy_context('S', vOrgId);

  recCopy.api_version_number := 1;

  recCopy.init_msg_list := fnd_api.g_true;

  recCopy.copy_order := fnd_api.g_true;

  recCopy.hdr_count := 1;

  recCopy.expiration_date := NULL;

  recCopy.transaction_name := NULL;

  recCopy.copy_transaction_name := fnd_api.g_true;

  recCopy.copy_expiration_date := fnd_api.g_false;

  recCopy.version_number := 0;

  recCopy.line_version_number := 0;

  recCopy.append_to_header_id := NULL;

  recCopy.manual_order_number := NULL;

  recCopy.manual_quote_number := NULL;

  recCopy.hdr_payments := fnd_api.g_true;

  recCopy.hdr_type := vHeaderType;

  recCopy.line_type := vLineType;

  headertab(1).id1 := vOriHeaderId;

  --除非特定項次,否則多數無須設定 

  --因屬於複製行為,多數使用在Order Return建立上

  --recCopy.commit := fnd_api.g_false;

  --recCopy.hdr_info := fnd_api.g_false; 

  --recCopy.hdr_descflex := fnd_api.g_true;

  --recCopy.hdr_credit_card_details := fnd_api.g_false;

  --recCopy.hed.hdr_scredits:= fnd_api.g_true; 

  --recCopy.hed.hdr_attchmnts := fnd_api.g_true; 

  --recCopy.hdr_holds := fnd_api.g_true; 

  --recCopy.all_lines := fnd_api.g_false; 

  --recCopy.line_count := 1;

  --recCopy.incl_cancelled := fnd_api.g_flase;

  --recCopy.line_price_mode := 1;

  --recCopy.line_discount_id       := NULL;

  --recCopy.line_descflex          := fnd_api.g_true;

  --recCopy.line_holds             := fnd_api.g_false;

  --recCopy.line_scredits          := fnd_api.g_false;

  --recCopy.line_attchmnts         := fnd_api.g_true;

  --recCopy.line_payments          := fnd_api.g_false;

  --recCopy.default_null_values    := fnd_api.g_false;

  --recCopy.new_phase              := fnd_api.g_false;

  --recCopy.version_reason_code    := NULL;

  --recCopy.comments               := 1910066;

  --recCopy.phase_change_flag      := fnd_api.g_true;

  --recCopy.line_phase_change_flag := fnd_api.g_true;

  --recCopy.copy_complete_config   := fnd_api.g_true;

  --recCopy.source_block_type      := 'LINE';

  oe_order_copy_util.copy_order(p_copy_rec => recCopy,

                                p_hdr_id_tbl => headertab,

                                p_line_id_tbl => linetab,

                                x_header_id => newHeaderId,

                                x_return_status => vReturnStatus,

                                x_msg_count => vMessageCount,

                                x_msg_data => vMessageData);

  IF vReturnStatus <> fnd_api.g_ret_sts_success THEN

    dbms_output.put_line(vMessageData);

    FOR i IN 1 .. vMessageCount

    LOOP

      vMessage := oe_msg_pub.get(i, 'F');

      dbms_output.put_line(vMessage);

    END LOOP;

  ELSE

    oe_order_book_util.complete_book_eligible(p_api_version_number => 1,

                                              p_init_msg_list => fnd_api.g_true,

                                              p_header_id => newHeaderId,

                                              x_return_status => vReturnStatus,

                                              x_msg_count => vMessageCount,

                                              x_msg_data => vMessageData);

    IF vReturnStatus <> fnd_api.g_ret_sts_success THEN

      FOR i IN 1 .. vMessageCount

      LOOP

        vMessage := oe_msg_pub.get(i, 'F');

        dbms_output.put_line(vMessage);

      END LOOP;

    ELSE

    

      oe_header_util.Get_Order_Info(p_header_id => newHeaderId,

                                    x_order_number => xOrderNumber,

                                    x_order_type => xOrderType,

                                    x_order_source => xOrderSource);

    

      dbms_output.put_line('Order Number:' || xOrderNumber);

    END IF;

  END IF;

END;

[API] oe_line_status_pub

Example : 

DECLARE

  vLineId oe_order_lines_all.line_id%TYPE := 00;

  xResult VARCHAR2(1);

BEGIN

  oe_line_status_pub.Get_ship_Status(p_line_id => vLineId,

                                     x_result => xResult);

  dbms_output.put_line('Shiped ? => ' || xResult);

  oe_line_status_pub.Get_Cancelled_status(p_line_id => vLineId,

                                          x_result => xResult);

  dbms_output.put_line('Cancelled ? => ' || xResult);

  oe_line_status_pub.Get_Closed_Status(p_line_id => vLineId,

                                       x_result => xResult);

  dbms_output.put_line('Closed ? => ' || xResult);

  oe_line_status_pub.Get_Invoiced_Status(p_line_id => vLineId,

                                         x_result => xResult);

  dbms_output.put_line('Invoiced ? => ' || xResult);

  oe_line_status_pub.Get_pick_Status(p_line_id => vLineId,

                                     x_result => xResult);

  dbms_output.put_line('in Pick  ? => ' || xResult);

END;

[API] oe_order_pub.get_order

DECLARE

  vApiVersionNumber    NUMBER;

  vInitMsgList         VARCHAR2(200);

  xReturnValues        VARCHAR2(200);

  xReturnStatus        VARCHAR2(1);

  xMsgCount            NUMBER;

  xMsgData             VARCHAR2(200);

  vHeaderId            NUMBER;

  vOrgId               NUMBER;

  xHeaderRec           apps.oe_order_pub.header_rec_type;

  xHeaderValRec        apps.oe_order_pub.header_val_rec_type;

  xHeaderAdjTbl        apps.oe_order_pub.header_adj_tbl_type;

  xHeaderAdjValTbl     apps.oe_order_pub.header_adj_val_tbl_type;

  xHeaderPriceAttTbl   apps.oe_order_pub.header_price_att_tbl_type;

  xHeaderAdjAttTbl     apps.oe_order_pub.header_adj_att_tbl_type;

  xHeaderAdjAssocTbl   apps.oe_order_pub.header_adj_assoc_tbl_type;

  xHeaderScreditTbl    apps.oe_order_pub.header_scredit_tbl_type;

  xHeaderScreditValTbl apps.oe_order_pub.header_scredit_val_tbl_type;

  xHeaderPaymentTbl    apps.oe_order_pub.header_payment_tbl_type;

  xHeaderPaymentValTbl apps.oe_order_pub.header_payment_val_tbl_type;

  xLineTbl             apps.oe_order_pub.line_tbl_type;

  xLineValTbl          apps.oe_order_pub.line_val_tbl_type;

  xLineAdjTbl          apps.oe_order_pub.line_adj_tbl_type;

  xLineAdjValTbl       apps.oe_order_pub.line_adj_val_tbl_type;

  xLinePriceAttTbl     apps.oe_order_pub.line_price_att_tbl_type;

  xLineAdjAttTbl       apps.oe_order_pub.line_adj_att_tbl_type;

  xLineAdjAssocTbl     apps.oe_order_pub.line_adj_assoc_tbl_type;

  xLineScreditTbl      apps.oe_order_pub.line_scredit_tbl_type;

  xLineScreditValTbl   apps.oe_order_pub.line_scredit_val_tbl_type;

  xLinePaymentTbl      apps.oe_order_pub.line_payment_tbl_type;

  xLinePaymentValTbl   apps.oe_order_pub.line_payment_val_tbl_type;

  XLotSerialTbl        apps.oe_order_pub.lot_serial_tbl_type;

  xLotSerialValTbl     apps.oe_order_pub.lot_serial_val_tbl_type;

BEGIN

  vApiVersionNumber := 1;

  vInitMsgList      := FND_API.G_FALSE;

  xReturnValues     := NULL;

  vHeaderId         := 00;

  vOrgId            := 00;

  fnd_global.apps_initialize(user_id => 00, resp_id => 00,

                             resp_appl_id => 660);

  mo_global.set_policy_context('S', vOrgId);

  mo_global.init('ONT');

  oe_order_pub.get_order(p_api_version_number => vApiVersionNumber,

                         p_init_msg_list => vInitMsgList,

                         p_return_values => xReturnValues,

                         x_return_status => xReturnStatus,

                         x_msg_count => xMsgCount, x_msg_data => xMsgData,

                         p_header_id => vHeaderId, p_org_id => vOrgId,

                         --p_operating_unit => p_operating_unit,

                         x_header_rec => xHeaderRec,

                         x_header_val_rec => xHeaderValRec,

                         x_header_adj_tbl => xHeaderAdjTbl,

                         x_header_adj_val_tbl => xHeaderAdjValTbl,

                         x_header_price_att_tbl => xHeaderPriceAttTbl,

                         x_header_adj_att_tbl => xHeaderAdjAttTbl,

                         x_header_adj_assoc_tbl => xHeaderAdjAssocTbl,

                         x_header_scredit_tbl => xHeaderScreditTbl,

                         x_header_scredit_val_tbl => xHeaderScreditValTbl,

                         x_header_payment_tbl => xHeaderPaymentTbl,

                         x_header_payment_val_tbl => xHeaderPaymentValTbl,

                         x_line_tbl => xLineTbl,

                         x_line_val_tbl => xLineValTbl,

                         x_line_adj_tbl => xLineAdjTbl,

                         x_line_adj_val_tbl => xLineAdjValTbl,

                         x_line_price_att_tbl => xLinePriceAttTbl,

                         x_line_adj_att_tbl => xLineAdjAttTbl,

                         x_line_adj_assoc_tbl => xLineAdjAssocTbl,

                         x_line_scredit_tbl => xLineScreditTbl,

                         x_line_scredit_val_tbl => xLineScreditValTbl,

                         x_line_payment_tbl => xLinePaymentTbl,

                         x_line_payment_val_tbl => xLinePaymentValTbl,

                         x_lot_serial_tbl => xLotSerialTbl,

                         x_lot_serial_val_tbl => xLotSerialValTbl);

  DBMS_OUTPUT.PUT_LINE('Retrun Status : ' || xReturnStatus);

  DBMS_OUTPUT.PUT_LINE('Message Count : ' || xMsgCount);

  DBMS_OUTPUT.PUT_LINE('Message Data  : ' || xMsgData);

  DBMS_OUTPUT.PUT_LINE('Order number  : ' || xHeaderRec.order_number);

  FOR i IN xLineTbl.first .. xLineTbl.last LOOP

  DBMS_OUTPUT.PUT_LINE('Line Number   : ' || xLineTbl(i).line_number);

  DBMS_OUTPUT.PUT_LINE('Ordered Item  : ' || xLineTbl(i).ordered_item); 

  DBMS_OUTPUT.PUT_LINE('Ordered Quantity = ' || xLineTbl(i).ordered_quantity);

  DBMS_OUTPUT.PUT_LINE('Shipped Quantity = ' || xLineTbl(i).shipped_quantity);

  END LOOP;

  IF xReturnStatus = fnd_api.g_ret_sts_success THEN

    --COMMIT;

    DBMS_OUTPUT.put_line('Sucessfull');

  ELSE

    DBMS_OUTPUT.put_line('Failed with the error :');

    --ROLLBACK;

    FOR i IN 1 .. xMsgCount

    LOOP

      xMsgData := oe_msg_pub.get(p_msg_index => i, p_encoded => 'F');

      dbms_output.put_line(i || ') ' || xMsgData);

    END LOOP;

  END IF;

END;

[API] Re-calculate TAX

--This API works same as ‘Actions->Calculate Tax’ from Sales Order Form (OEXOEORD)

DECLARE

 vHeaderId oe_order_headers_all.header_id%TYPE := 000;

 xReturnStatus varcahr2(1); 

BEGIN 

   om_tax_util.calculate_tax(p_header_id => vHeaderId , x_return_status => xReturnStatus);

   IF (xReturnStatus = fnd_api.g_ret_sts_success) THEN 

      COMMIT;

   ELSE

      dbms_output.put_line('Failed in Re-Calculating TAX');

      ROLLBACK;

   END IF;

END;

[API] oe_oe_totals_summary

oe_oe_totals_summary.total_ordered_qty

oe_oe_totals_summary.get_order_amount

oe_oe_totals_summary.line_total

oe_oe_totals_summary.order_subtotals

oe_oe_totals_summary.order_totals

oe_oe_totals_summary.price_adjustments

...

[API] get header record

DECLARE

  vHeaderId  oe_order_headers_all.header_id%TYPE := 1595;

  xHeaderRec oe_order_pub.Header_Rec_Type;

BEGIN

  xHeaderRec := oe_header_util.Query_Row(p_header_id => vHeaderId);

  dbms_output.put_line(xHeaderRec.booked_flag);

  dbms_output.put_line(xHeaderRec.order_number);

  dbms_output.put_line(oe_id_to_value.Salesrep(p_salesrep_id => xHeaderRec.salesrep_id));

  dbms_output.put_line(oe_id_to_value.Flow_Status(p_flow_status_code => xHeaderRec.flow_status_code));

END;

[API] get line record

DECLARE

  vLineId oe_order_lines.line_id%TYPE := 00;

  xOrderLineRec oe_order_pub.line_rec_type;

begin

  xOrderLineRec := oe_line_util.query_row(p_line_id => vLineId);

  dbms_output.put_line(xOrderLineRec.ordered_item);

  dbms_output.put_line(xOrderLineRec.ordered_quantity);

  dbms_output.put_line(xOrderLineRec.unit_list_price); 

  dbms_output.put_line(xOrderLineRec.unit_selling_price);

end;