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

wsh_picking_batches_pub

DECLARE
  vOrgId        NUMBER := 00;
  xReturnStatus VARCHAR2(1);
  xMsgCount     NUMBER;
  xMsgData      VARCHAR2(2000);
  pMagIdx       NUMBER(15);
  xNewBatchId   NUMBER;
  vBatchInfoRec wsh_picking_batches_pub.batch_info_rec;
  vRuleId       NUMBER;
  vRuleName     VARCHAR2(30);
  vBatchPrefix  VARCHAR2(200);
  xRequestId    NUMBER;
BEGIN
  fnd_global.apps_initialize(user_id => 00, resp_id => 00,resp_appl_id => 00);
  mo_global.set_policy_context('S', vOrgId);
  mo_global.init('ONT');
  vBatchInfoRec.Delivery_Id             := 00; --wsh_new_deliveries.delivery_id
  vBatchInfoRec.Backorders_Only_Flag    := 'I'; --E:Unrelease / O:Backordered / I:All / M:Replenishment Completed
  vBatchInfoRec.Existing_Rsvs_Only_Flag := 'N';
  vBatchInfoRec.Customer_Id             := 00; --wsh_new_deliveries.customer_id
  --vBatchInfoRec.Order_Header_Id          := 00;

  vBatchInfoRec.Default_Stage_Subinventory := 'XX'; ----mtl_secondary_inventories.secondary_inventory_name
  vBatchInfoRec.Pick_From_Subinventory     := 'XX'; --mtl_secondary_inventories.secondary_inventory_name
  vBatchInfoRec.Organization_Id            := 00;
  vBatchInfoRec.Include_Planned_Lines      := 'N';
  vBatchInfoRec.Autocreate_Delivery_Flag   := 'Y';
  vBatchInfoRec.Autodetail_Pr_Flag         := 'Y';
  vBatchInfoRec.allocation_method          := 'I';
  --vBatchInfoRec.Pick_From_locator_Id       := NULL;
  vBatchInfoRec.Auto_Pick_Confirm_Flag := 'Y';
  vBatchInfoRec.Autopack_Flag          := 'Y';
  vBatchInfoRec.actual_departure_date  := fnd_date.canonical_to_date('2020/01/01');
  --vBatchInfoRec.Ship_Confirm_Rule_Id       := 00;
  --vBatchInfoRec.Ship_Confirm_Rule_Name     := 'XX';
  --vBatchInfoRec.Document_Set_Id            := 00;
  --vBatchInfoRec.Pick_Grouping_Rule_Id      := 00;
  vRuleId      := 00; --wsh_picking_rules_v.picking_rule_id 
  vRuleName    := 'XX'; --wsh_picking_rules_v.picking_rule_name
  vBatchPrefix := NULL;

  wsh_picking_batches_pub.create_batch(p_api_version => 1.0,
                                       p_init_msg_list => fnd_api.g_true,
                                       p_commit => fnd_api.g_true,
                                       x_return_status => xReturnStatus,
                                       x_msg_count => xMsgCount,
                                       x_msg_data => xMsgData,
                                       p_rule_id => vRuleId,
                                       p_rule_name => vRuleName,
                                       p_batch_rec => vBatchInfoRec,
                                       p_batch_prefix => vBatchPrefix,
                                       x_batch_id => xNewBatchId);

  IF xReturnStatus = fnd_api.g_ret_sts_success THEN
    DBMS_OUTPUT.put_line('Pick Release Batch Got Created Sucessfully ' ||
                         xNewBatchId);
  ELSE
    DBMS_OUTPUT.put_line('Message count ' || xMsgCount);
    IF xMsgCount = 1 THEN
      DBMS_OUTPUT.put_line('Message data ' || xMsgData);
    ELSIF xMsgCount > 1 THEN
      LOOP
        pMagIdx  := pMagIdx + 1;
        xMsgData := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);
        IF xMsgData IS NULL THEN
          EXIT;
        END IF;
        DBMS_OUTPUT.put_line('Message' || pMagIdx || '---' || xMsgData);
      END LOOP;
    END IF;
  END IF;

  wsh_picking_batches_pub.release_batch(p_api_version => 1.0,
                                        p_init_msg_list => fnd_api.g_true,
                                        p_commit => fnd_api.g_true,
                                        x_return_status => xReturnStatus,
                                        x_msg_count => xMsgCount,
                                        x_msg_data => xMsgData,
                                        p_batch_id => xNewBatchId,
                                        p_batch_name => NULL,
                                        p_log_level => 1,
                                        --p_release_mode => 'CONCURRENT',
                                        p_release_mode => 'ONLINE',
                                        -- (online or concurrent)
                                        x_request_id => xRequestId);

  IF xReturnStatus = fnd_api.g_ret_sts_success THEN
    dbms_output.put_line('Pick Selection List Generation ' || xRequestId);
  ELSE
    DBMS_OUTPUT.put_line('Message count ' || xMsgCount);
    IF xMsgCount = 1 THEN
      DBMS_OUTPUT.put_line('Message data ' || xMsgData);
    ELSIF xMsgCount > 1 THEN
      LOOP
        pMagIdx  := pMagIdx + 1;
        xMsgData := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);
        IF xMsgData IS NULL THEN
          EXIT;
        END IF;
        DBMS_OUTPUT.put_line('Message' || pMagIdx || '---' || xMsgData);
      END LOOP;
    END IF;
  END IF;
END;

[IBY] iby_ext_bankacct_pub.get_ext_bank_acct

 DECLARE

  vBankAccountId iby_ext_bank_accounts.ext_bank_account_id%TYPE := 00;

  vInitMsgList   VARCHAR2(1) := fnd_api.g_false;

  xRetrunStatus  VARCHAR2(1);

  xMsgCount      NUMBER;

  xMsgData       VARCHAR2(2000);

  xBankAccount   iby_ext_bankacct_pub.extbankacct_rec_type;

  xResponse      iby_fndcpt_common_pub.result_rec_type;

  vCount         NUMBER := 0;

BEGIN

  iby_ext_bankacct_pub.get_ext_bank_acct(p_api_version => 1.0,

                                         p_init_msg_list => vInitMsgList,

                                         p_bankacct_id => vBankAccountId,

                                         x_return_status => xRetrunStatus,

                                         x_msg_count => xMsgCount,

                                         x_msg_data => xMsgData,

                                         x_bankacct => xBankAccount,

                                         x_response => xResponse);

  dbms_output.put_line('API Return => ' || xRetrunStatus);

  dbms_output.put_line('Bank Account Number : ' || xBankAccount.bank_account_num);

  dbms_output.put_line('Bank Account Name   : ' || xBankAccount.bank_account_name);

  IF xRetrunStatus <> fnd_api.g_ret_sts_success THEN

    IF xMsgCount = 1 THEN

      DBMS_OUTPUT.put_line('Error =>' || xMsgData);

    ELSE

      LOOP

        vCount   := vCount + 1;

        xMsgData := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);

        IF xMsgData IS NULL THEN

          EXIT;

        END IF;

        DBMS_OUTPUT.put_line('Error' || vCount || '=>' || xMsgData);

      END LOOP;

    END IF;

  END IF;

END;

rcv_accrual_sv.get_quantities

DECLARE

  ordered_po_qty    NUMBER;

  received_po_qty   NUMBER;

  corrected_po_qty  NUMBER;

  delivered_po_qty  NUMBER;

  rtv_po_qty        NUMBER;

  billed_po_qty     NUMBER;

  accepted_po_qty   NUMBER;

  rejected_po_qty   NUMBER;

  ordered_txn_qty   NUMBER;

  received_txn_qty  NUMBER;

  corrected_txn_qty NUMBER;

  delivered_txn_qty NUMBER;

  rtv_txn_qty       NUMBER;

  billed_txn_qty    NUMBER;

  accepted_txn_qty  NUMBER;

  rejected_txn_qty  NUMBER;

BEGIN

  mo_global.set_policy_context('S',00);

  rcv_accrual_sv.get_quantities(top_transaction_id    => 001,

                                ordered_po_qty        => ordered_po_qty,

                                received_po_qty       => received_po_qty,

                                corrected_po_qty      => corrected_po_qty,

                                delivered_po_qty      => delivered_po_qty,

                                rtv_po_qty            => rtv_po_qty,

                                billed_po_qty         => billed_po_qty,

                                accepted_po_qty       => accepted_po_qty,

                                rejected_po_qty       => rejected_po_qty,

                                ordered_txn_qty       => ordered_txn_qty,

                                received_txn_qty      => received_txn_qty,

                                corrected_txn_qty     => corrected_txn_qty,

                                delivered_txn_qty     => delivered_txn_qty,

                                rtv_txn_qty           => rtv_txn_qty,

                                billed_txn_qty        => billed_txn_qty,

                                accepted_txn_qty      => accepted_txn_qty,

                                rejected_txn_qty      => rejected_txn_qty,

                                p_accrual_cutoff_date => SYSDATE);

  dbms_output.put_line('ordered_po_qty:=>' || ordered_po_qty);

  dbms_output.put_line('received_po_qty:=>' || received_po_qty);

  dbms_output.put_line('corrected_po_qty:=>' || corrected_po_qty);

  dbms_output.put_line('delivered_po_qty:=>' || delivered_po_qty);

  dbms_output.put_line('rtv_po_qty:=>' || rtv_po_qty);

  dbms_output.put_line('billed_po_qty:=>' || billed_po_qty);

  dbms_output.put_line('accepted_po_qty:=>' || accepted_po_qty);

  dbms_output.put_line('rejected_po_qty:=>' || rejected_po_qty);

  dbms_output.put_line('ordered_txn_qty:=>' || ordered_txn_qty);

  dbms_output.put_line('received_txn_qty:=>' || received_txn_qty);

  dbms_output.put_line('corrected_txn_qty:=>' || corrected_txn_qty);

  dbms_output.put_line('delivered_txn_qty:=>' || delivered_txn_qty);

  dbms_output.put_line('rtv_txn_qty:=>' || rtv_txn_qty);

  dbms_output.put_line('billed_txn_qty:=>' || billed_txn_qty);

  dbms_output.put_line('accepted_txn_qty:=>' || accepted_txn_qty);

  dbms_output.put_line('rejected_txn_qty:=>' || rejected_txn_qty);

END;

[User] Create user by api

 DECLARE

  vEmpNum    hr_employees.employee_num%TYPE := 'xxx';

  vStartDate fnd_user.start_date%TYPE := SYSDATE;

  vEndDate   fnd_user.end_date%TYPE := NULL;

  vPassword  fnd_user.web_password%TYPE;

  vSessionId fnd_user.session_number%TYPE := userenv('sessionid');

  vEmp       hr_employees%ROWTYPE;

BEGIN

  SELECT *

  INTO   vEmp

  FROM   hr_employees he

  WHERE  he.employee_num = vEmpNum;

  vPassword := dbms_random.string('p', 10);

  fnd_user_pkg.CreateUser(x_user_name => vEmp.employee_num, x_owner => NULL,

                          x_unencrypted_password => vPassword,

                          x_session_number => vSessionId,

                          x_start_date => vStartDate, x_end_date => vEndDate,

                          x_employee_id => vEmp.employee_id);

  dbms_output.put_line('Password : ' || vPassword);

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line(dbms_utility.format_error_stack);

END;

[PO] Close PO => Header / Line / Shipment

 DECLARE

  xReturnStatus VARCHAR2(1);

  xResult       BOOLEAN;

BEGIN

  fnd_global.apps_initialize(user_id => 00, resp_id => 00,resp_appl_id => 00);

  xResult := po_actions.close_po(p_docid => 00,

                                  p_doctyp => 'PO',

                                  p_docsubtyp => 'STANDARD',

                                  p_lineid => null, -- if want to close line

                                  p_shipid => null, -- if want to close shipment

                                  p_action => 'CLOSE',

                                  p_reason => 'Test',

                                  p_calling_mode => 'PO', 

                                  p_conc_flag => 'N',

                                  p_return_code => xReturnStatus,

                                  p_auto_close => 'N',

                                  p_action_date => SYSDATE,

                                  p_origin_doc_id => null);

END;

Subinventory Transfer by API & Interface

Example => 

DECLARE

  vMTI mtl_transactions_interface%ROWTYPE;

  vApiVersioin  NUMBER := 1.0;

  vInitMsgList  VARCHAR2(2) := FND_API.G_TRUE;

  VCommit       VARCHAR2(2) := FND_API.G_FALSE;

  xReturnStatus VARCHAR2(2);

  xMsgCount     NUMBER := 0;

  xMsgData      VARCHAR2(255);

  xTransCount   NUMBER;

  vResult       NUMBER;

  vHeaderId     NUMBER := -100;

BEGIN

  

  fnd_global.apps_initialize(00, 00, 00);

  

  --1 transaction

  vMTI.Transaction_Interface_Id := mtl_material_transactions_s.NEXTVAL;

  vMTI.Transaction_Header_Id    := vHeaderId;

  vMTI.source_code              := 'Subinventory Transfer1';

  vMTI.source_line_id           := 0;

  vMTI.source_header_id         := 0;

  vMTI.process_flag             := 1;

  vMTI.transaction_mode         := 3;

  vMTI.Last_Update_Date         := SYSDATE;

  vMTI.Last_Updated_By          := fnd_global.user_id;

  vMTI.Created_By               := fnd_global.user_id;

  vMTI.Creation_Date            := SYSDATE;

  vMTI.Organization_Id          := 00;

  vMTI.Inventory_Item_Id        := 12345;

  vMTI.Transaction_Quantity     := 2;

  vMTI.Transaction_Uom          := 'PCS';

  vMTI.Transaction_Date         := SYSDATE;

  vMTI.Transaction_Type_Id      := 2;

  vMTI.Subinventory_Code        := 'XX01';

  vMTI.Locator_Id               := 001;

  vMTI.Transfer_Subinventory    := 'XX02';

  vMTI.Transfer_Locator         := 002;

  vMTI.Lock_Flag                := 2;

  

  INSERT INTO mtl_transactions_interface

  VALUES vMTI;

  --2 transaction

  vMTI.Transaction_Interface_Id := mtl_material_transactions_s.NEXTVAL;

  vMTI.source_code              := 'Subinventory Transfer2';

  vMTI.Transaction_Quantity     := 3;

  INSERT INTO mtl_transactions_interface

  VALUES vMTI;

  

  VResult := inv_txn_manager_pub.process_Transactions(p_api_version => vApiVersioin,

                                                      p_init_msg_list => vInitMsgList,

                                                      p_commit => vCommit,

                                                      p_validation_level => fnd_api.g_valid_level_full,

                                                      x_return_status => xReturnStatus,

                                                      x_msg_count => xMsgCount,

                                                      x_msg_data => xMsgData,

                                                      x_trans_count => xTransCount,

                                                      p_table => 1,

                                                      p_header_id => vHeaderId);

  IF (xReturnStatus <> FND_API.G_RET_STS_SUCCESS) THEN

    dbms_output.put_line(xTransCount);

    dbms_output.put_line('Error Message :' || xMsgData);

  END IF;

  IF (xReturnStatus = FND_API.G_RET_STS_SUCCESS) THEN

    dbms_output.put_line('Transaction Processed Successfully');

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('Exception Occured :');

    dbms_output.put_line(SQLCODE || ':' || SQLERRM);

END;

[API] bill_exists & routing_exists & revision_exists

wip_common.bill_exists

wip_common.routing_exists

wip_common.revision_exists

inv_inv_lovs & return ref cursor

範例 : 

DECLARE

  vRefcursorOrg     SYS_REFCURSOR;

  vOrganizationId   mtl_parameters.organization_id%TYPE;

  vOrganizationCode mtl_parameters.organization_code%TYPE;

  vOrganizationName hr_organization_units.name%TYPE;

  vTemp             NUMBER;

BEGIN

  inv_inv_lovs.get_all_orgs(x_organizations => vRefcursorOrg,

                            p_organization_code => '%');

  LOOP

    FETCH vRefcursorOrg

      INTO vOrganizationId,

           vOrganizationCode,

           vOrganizationName,

           vTemp;

    EXIT WHEN vRefcursorOrg%NOTFOUND;

    dbms_output.put_line('OrganizationId :' || vOrganizationId ||

                         ',OrganizationCode:' || vOrganizationCode ||

                         ',OrganizationName:' || vOrganizationName);

  END LOOP;

  CLOSE vRefcursorOrg;

END;

utility functions [GMF_UTILITIES_GRP]

gmf_utilities_grp.get_account_code

gmf_utilities_grp.get_account_desc

gmf_utilities_grp.get_cost_category

gmf_utilities_grp.get_item_number

gmf_utilities_grp.get_legal_entity

gmf_utilities_grp.get_organization_code

gmf_utilities_grp.get_organization_name

Pass Null Values

From the TCA Technical Implementation Guide:

For an Update API, if a particular attribute value is not passed into the API, then the database retains the existing value. In order to set a database value to null, 

the calling program must explicitly set the attribute value to one of the following constants, based on the data type of the attribute.

FND_API.G_MISS_NUM for NUMBER type.

FND_API.G_MISS_CHAR for VARCHAR2 type.

FND_API.G_MISS_DATE for DATE type.

These are pre-defined values in the FND_API Package (fndapis.pls)

G_MISS_NUM CONSTANT NUMBER:= 9.99E125

G_MISS_CHAR CONSTANT VARCHAR2(1):= chr(0)

G_MISS_DATE CONSTANT DATE:= TO_DATE('1','j')