[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;

DBMS_METADATA

參考網址 : https://www.itread01.com/article/1503040590.html

範例 A : 

SELECT table_name, ddlTable,
dbms_lob.substr(lob_loc => ddlTable,
                amount  => dbms_lob.getlength(ddlTable) ,
                offset  => 1)
from
(
SELECT utable.table_name,
       dbms_metadata.get_ddl('TABLE', utable.table_name) ddlTable
FROM   user_tables utable
WHERE  rownum <= 10
) tabs

範例 B : 

DECLARE

  vObjectName VARCHAR2(40) := 'FND_API';
  vObjectType VARCHAR2(40) := 'PACKAGE';
  xClob       CLOB;
  vClobLength NUMBER;
  vChar       VARCHAR2(1);

  TYPE rLine IS RECORD(
    idx  NUMBER,
    data VARCHAR2(2000));
  TYPE tLine IS TABLE OF rLine;
  tblLine tLine := tLine();
BEGIN
 
  xClob       := dbms_metadata.get_ddl(vObjectType, vObjectName);
  vClobLength := dbms_lob.getlength(xClob);
  FOR i IN 1 .. vClobLength
  LOOP
    vChar := dbms_lob.substr(lob_loc => xClob, amount => 1, offset => i);
    IF vChar = chr(10) THEN
      tblLine.extend();
    ELSE
     tblLine(tblLine.last).data := tblLine(tblLine.last).data || vChar;
    END IF;
  END LOOP;
 
  dbms_output.enable(100000);
 
  IF tblLine IS NOT empty THEN
     FOR i IN tblLine.first .. tblLine.last LOOP
        dbms_output.put_line(tblLine(i).data);
     END LOOP;
  END IF;
END;

DBMS_ASSERT

DBMS_ASSERT.ENQUOTE_LITERAL => 回傳字串前後加上 ' 

DBMS_ASSERT.ENQUOTE_NAME => 回傳字串前後加上 " 

DBMS_ASSERT.SQL_OBJECT_NAME => 尋找是否存在符合的物件 

 select table_name, dbms_assert.enquote_literal(table_name) , dbms_assert.enquote_name(table_name) from all_tables where rownum <= 1 

 

DECLARE
  vobject1 VARCHAR2(80) := 'dual';
  vobject2 VARCHAR2(80) := 'dualx';
  vresult1 VARCHAR(80);
  vresult2 VARCHAR(80);
BEGIN
  BEGIN
    vresult1 := dbms_assert.sql_object_name(vobject1);
    IF vresult1 IS NOT NULL THEN
      dbms_output.put_line('Found Object:' || vresult1);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(dbms_utility.format_error_stack);
  END;

  BEGIN
    vresult2 := dbms_assert.sql_object_name(vobject2);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(dbms_utility.format_error_stack);
  END;
END;