Change the User Password from Backend

begin
   fnd_user_pkg.updateuser (x_user_name                 => 'USER_NAME',
                            x_owner                     => 'CUST',
                            x_unencrypted_password      => 'CHOOSE_PASSWORD',
                            x_password_date             => TO_DATE ('2', 'J')
                           );
   COMMIT;
END;
/

Selecting Multiple Values in Concurrent Program Paramater

Requirement:
To have a concurrent program parameter where he can select multiple value from the value set. Solution: Create 2 parameter, first one with the value set which will have the values from which the values need to be selected. The second parameter will be a free text field which will hold the selected values from the first value set.

Steps to achieve the desired solution 
1) Create a value set which will hold the values. Let say the name is “XXTK_MULTIPLE_VALUE_SET”
Explanation This value set will be holding the values from which the user will be selecting the values into the second parameter.

2) Create a concurrent program program and create PARAMETER-1 and assign the above value set to it.

3) Create second parameter and do the following setup
3.1) Value Set: 240 Characters
3.2) Default Type: SQL Statement
3.3) Default Value: SELECT xxoa_multiple_parameter.return_value(:$FLEX$.XXTK_MULTIPLE_VALUE_SET) FROM DUAL

ExplanationThe select statement will take the input as the selected value from the first parameter and will return the concatenated values.

4) In the Default value we have used a function, so now we have to create that function.

CREATE OR REPLACE PACKAGE xxoa_multiple_parameter

AS

g_var VARCHAR2 (3200) := NULL;

g_time DATE;

 

FUNCTION return_value (i_para VARCHAR2)

RETURN VARCHAR2;

END;

/

 

CREATE OR REPLACE PACKAGE BODY xxoa_multiple_parameter

AS

FUNCTION return_value (I_para VARCHAR2)

RETURN VARCHAR2

AS

l_len NUMBER := LENGTH (I_para);

BEGIN

IF g_time IS NULL

THEN

g_time := SYSDATE;

g_var := i_para;

ELSE

IF (((SYSDATE - g_time) * 60 * 60 * 24) > 30)

THEN

g_time := SYSDATE;

g_var := i_para;

ELSE

IF ( NVL (LENGTH (g_var), 0)

- NVL (LENGTH (REPLACE (g_var, i_para, NULL)), 0)

- l_len != 0

)

THEN

IF g_var IS NULL

THEN

g_var := i_para;

ELSE

g_var := i_para || ',' || g_var;

END IF;

ELSE

g_var := REPLACE (g_var, i_para);

END IF;

END IF;

END IF;

 

g_var := REGEXP_REPLACE (REGEXP_REPLACE (g_var, '^,|,$', ''), ',,', ',');

RETURN g_var;

END return_value;

END xxoa_multiple_parameter;

Explanation This function takes the input from the first parameter (Value Set) and initialize the GLOBAL PARAMETER of TIME and parameter. It checks whether the initialize is done before 90 sec or not. If no, then it will concatenate the values and will return the new string of values. If the user wants to remove selected value value then he has to select the value again and the function will remove the value. Also before returning the final string of values the function will remove the comma from starting or at the end of the string.

Explanation The reason for creating 2 parameters are that the first parameter will be holding the parameter from the value set which is of no use. The second parameter is of use as that will hold the selected values.

Steps to Use
1) Select the concurrent program and select the Value from the Value set

After selecting the first value

2) Selecting the second value from the value set

3) Now Select a value to remove it from selected list. Selecting EMP

4) Once the value is selected, that value will be removed from the list

Limitations 
1) After submitting the values, user has to close the form of concurrent program and again has to open it. To Avoid this a time frame of 90 second is given. After 90 seconds the values will be reset to null.

OM:Common Errors in Order Booking

This document highlights the common errors or issues which occurs when we create Orders in Order Management. This document will be frequently updated as and when i will come across new errors. If you would like to contribute in this list then anyone is most welcome.

Sno	Issue Type	Issue	Solution
1	Entering Order	Item not coming in Order	1) Check whether the item is CUSTOMER_ORDERED or not in Inventory screen.
2	Entering Order	The Item Specified is invalid or does not exist in the warehouse you specified. Please enter a valid Item-Warehouse Combination	1) check whether the item is present in the warehouse. To see the warehouse navigate to Others tab and see. Now navigate to Inv screen and validate the organization assignment for this item
3	Booking Order	Order is not eligible for booking. Check workflow status for this order.	Check the transaction type and see whether the Item is valid for that transaction or not
4	Booking Order	Payment Term is required on a booked order.	Enter the Payment team at Header and Line level Order->Other tab
5	Booking Order	Line Order is struck to Book Status	1) Check the availability of Item 2) Check whether the item is OE Transact able and Shippable on INV or not
6	Booking Order	Line directly going to Closed status instead of AWAITING SHIPMENT	1) Check whether the item is OE Transact able and Shippable on INV or not
7	Booking Order	SHIP CONFIRM TAB NOT ENABLED	1) Check whether the user is having the shipping rule or not. For that Go to OM superuser-> Shipping Grants-> Enter the user to who will be doing the shipping.
8	After ShippedConfirmed	Invoice Not moving to AR Interface tables	Open Setup/Transaction Types/Define Find your Mixed Transaction type Look at the Finance tab and check that you have populated an invoice source and Receivables Transaction Type.Make a note of these and then open AR Go to Setup/Transactions/Transaction Types and find using Receivables Transaction type from above Check that Open Receivables is checked and Post to GL too. Also check there is a Credit Memo Type populated.Now go to Setup/Transactions/Sources and call up the invoice source you noted from above Check that the type is Imported and Auto Invoice Options tab fields are populated.If there is anything missing from above setup, this may be why you are not getting an invoice.
9	While creating Invoice	You must supply an invoice number when your batch source indicates manual invoice numbering; otherwise you must leave invoice number blank	1) Check whether the AR transaction type is of Manual or Automatic by SETUP-> TRANSACTION->SOURCE and query for the transaction type 2) If the transaction type is manual then either change the transaction type for the AR Invoices or enter the transaction number at Control: Auto Invoice: Interface Lines.
10	Oracle Hold	You are not authorized to Release this Hold	1) This error comes when the user is trying to release a hold manually. 2) Check whether the responsibility is having Grant to release the hold OM ->Setup-> Hold-> Query for Hold Name and check the Authorized Responsibilities.

11

Oracle Import

At least one standard or model line with this line number already exists on this order. Please change the line number.

1) This error comes when the user is trying to import the order via interface. 2) Check whether the Item at Master Org is not inactive.

Difference Between MTL_PARAMETERS and ORG_ORGANIZATION_DEFINITIONS

Here is the difference between these two ...

MTL_PARAMETERS : It is the base table to store Organization information.

ORG_ORGANIZATION_DEFINITIONS : it is a view and depends on MTL_PARAMETERS, GL_LEDGERS and HR_ORGANIZATION_UNITS

Create Reservation using API

Hi Guys,

Please find below Tested code to create reservation using standard  API.



PROCEDURE call_reserve (p_qty               IN NUMBER,
                           p_item_id           IN NUMBER,
                           p_organziation_id   IN NUMBER,
                           p_lot_number        IN VARCHAR2,
                           p_uom_code          IN VARCHAR2) --, x_error OUT VARCHAR2)
   IS
      ln_user_id                NUMBER := FND_GLOBAL.User_ID;
      ld_sysdate                DATE := SYSDATE;
      ln_login_id               NUMBER := FND_GLOBAL.Conc_Login_ID;
      lv_return_status          VARCHAR2 (1) := FND_API.G_RET_STS_SUCCESS;
      ln_msg_count              NUMBER;
      lv_msg_data               VARCHAR2 (3000);
      ln_group_id               NUMBER := 9999;
      l_msg_index_out           NUMBER;
      l_error_message           VARCHAR2 (1000);
      l_serial_number           inv_reservation_global.serial_number_tbl_type;
      lr_orig_rsv_rec           inv_reservation_global.mtl_reservation_rec_type;
      l_rsv_rec                 inv_reservation_global.mtl_reservation_rec_type;
      lr_orig_serial_number     inv_reservation_global.serial_number_tbl_type;
      x_serial_number           INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
      ln_msg_index              NUMBER;
      l_init_msg_list           VARCHAR2 (2) := FND_API.G_TRUE;
      gv_res_source_typ_name    VARCHAR2(100):='Custom Source Name';
      x_quantity_reserved       NUMBER := 0;
      x_reservation_id          NUMBER := 0;
      ln_aft_adj_qty            NUMBER;

      ln_reserve_qty            NUMBER;
      lv_subinv_code            VARCHAR2 (30);
      ln_location_id            NUMBER;
      x_error                   VARCHAR2 (255);
      ln_avlbl_to_reserve_qty   NUMBER;


      ln_organization_id        NUMBER;
      ln_inventory_item_id      NUMBER;
      lv_uom_code               VARCHAR2 (30);
      lv_lot_number             VARCHAR2 (100);

      ln_already_reserved_qty NUMBER;
   BEGIN
      ln_reserve_qty := p_qty;
      lv_subinv_code := gv_reservation_subinv;
      ln_organization_id := p_organziation_id;
      ln_location_id := find_location (ln_organization_id, lv_subinv_code);----------------Function to Fetch Locator ID
      ln_inventory_item_id := p_item_id;
      lv_uom_code := p_uom_code;
      lv_lot_number := p_lot_number;



http://oracleappspractices.blogspot.com/2016/12/get-on-hand-available-to-transact.html

      ln_avlbl_to_reserve_qty :=
         (XXX_LOT_RESERVATION_PKG.get_available_qty (------------------------Separate Function to get Available Quantity By Using inv_quantity_tree_pub.query_quantities
             ln_organization_id,
             ln_inventory_item_id,
             gv_reservation_subinv,
             find_location (ln_organization_id, gv_reservation_subinv),
             lv_lot_number,
             0));

    BEGIN
       
          select NVL(reservation_quantity,0)
      INTO ln_already_reserved_qty
      from mtl_reservations
          where demand_source_name=gv_res_source_typ_name
          and inventory_item_id=ln_inventory_item_id
          and organization_id=ln_organization_id
          and lot_number=lv_lot_number;


        EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    WHEN OTHERS THEN
     XXX_log_messages_pkg.display_msg (' Error while calculating Already reserved Quantity inside call_reserve procedure  ' || SQLERRM);
   
    END;

        ln_reserve_qty := NVL(ln_reserve_qty,0) - NVL(ln_already_reserved_qty,0);

      IF ln_avlbl_to_reserve_qty >= ln_reserve_qty
      THEN
         l_rsv_rec.organization_id := ln_organization_id;
         l_rsv_rec.inventory_item_id := ln_inventory_item_id;
         l_rsv_rec.requirement_date := SYSDATE;
         l_rsv_rec.demand_source_type_id := gv_reservation_source_id;
         l_rsv_rec.supply_source_type_id := gv_reservation_source_id;
         l_rsv_rec.demand_source_name := gv_res_source_typ_name;
         l_rsv_rec.primary_reservation_quantity := ln_reserve_qty; --rec_process_data.adjustment_qty;
         l_rsv_rec.primary_uom_code := lv_uom_code;
         l_rsv_rec.subinventory_code := lv_subinv_code; --rec_process_data.subinventory_code;
         l_rsv_rec.demand_source_header_id := NULL;
         l_rsv_rec.demand_source_line_id := NULL;
         l_rsv_rec.reservation_uom_code := lv_uom_code;
         l_rsv_rec.reservation_quantity := ln_reserve_qty;
         --l_rsv_rec.secondary_reservation_quantity := 100;
         l_rsv_rec.supply_source_header_id := NULL;
         l_rsv_rec.supply_source_line_id := NULL;
         l_rsv_rec.supply_source_name := NULL;
         l_rsv_rec.supply_source_line_detail := NULL;
         l_rsv_rec.lot_number := lv_lot_number;
         l_rsv_rec.serial_number := NULL;
         l_rsv_rec.ship_ready_flag := NULL;
         l_rsv_rec.attribute15 := NULL;
         l_rsv_rec.attribute14 := NULL;
         l_rsv_rec.attribute13 := NULL;
         l_rsv_rec.attribute12 := NULL;
         l_rsv_rec.attribute11 := NULL;
         l_rsv_rec.attribute10 := NULL;
         l_rsv_rec.attribute9 := NULL;
         l_rsv_rec.attribute8 := NULL;
         l_rsv_rec.attribute7 := NULL;
         l_rsv_rec.attribute6 := NULL;
         l_rsv_rec.attribute5 := NULL;
         l_rsv_rec.attribute4 := NULL;
         l_rsv_rec.attribute3 := NULL;
         l_rsv_rec.attribute2 := NULL;
         l_rsv_rec.attribute1 := NULL;
         l_rsv_rec.attribute_category := NULL;
         l_rsv_rec.lpn_id := NULL;
         l_rsv_rec.pick_slip_number := NULL;
         l_rsv_rec.lot_number_id := NULL;
         l_rsv_rec.locator_id := ln_location_id; --rec_process_data.location_id ;---inventory_location_id ;-- NULL ;
         l_rsv_rec.subinventory_id := NULL;
         l_rsv_rec.revision := NULL;
         l_rsv_rec.external_source_line_id := NULL;
         l_rsv_rec.external_source_code := NULL;
         l_rsv_rec.autodetail_group_id := NULL;
         l_rsv_rec.reservation_uom_id := NULL;
         l_rsv_rec.primary_uom_id := NULL;
         l_rsv_rec.demand_source_delivery := NULL;
         l_rsv_rec.crossdock_flag := 'N';
         l_rsv_rec.secondary_uom_code := NULL;
         l_rsv_rec.detailed_quantity := NULL; --lrec_batch_details.shipped_quantity;
         l_rsv_rec.secondary_detailed_quantity := NULL; --ln_shipped_quantity;--lrec_batch_details.shipped_quantity;
         ln_msg_count := NULL;
         lv_msg_data := NULL;
         lv_return_status := NULL;


         INV_RESERVATION_PUB.Create_Reservation (
            P_API_VERSION_NUMBER         => 1.0,
            P_INIT_MSG_LST               => l_init_msg_list,
            P_RSV_REC                    => l_rsv_rec,
            P_SERIAL_NUMBER              => l_serial_number,
            P_PARTIAL_RESERVATION_FLAG   => FND_API.G_FALSE,
            P_FORCE_RESERVATION_FLAG     => FND_API.G_FALSE,
            P_PARTIAL_RSV_EXISTS         => FALSE,
            P_VALIDATION_FLAG            => FND_API.G_TRUE,
            X_SERIAL_NUMBER              => x_serial_number,
            X_RETURN_STATUS              => lv_return_status,
            X_MSG_COUNT                  => ln_msg_count,
            X_MSG_DATA                   => lv_msg_data,
            X_QUANTITY_RESERVED          => x_quantity_reserved,
            X_RESERVATION_ID             => x_reservation_id);


         IF lv_return_status = fnd_api.g_ret_sts_success
         THEN
            XXX_log_messages_pkg.display_msg (
               'Quantity succesfully reserved by the API');

            UPDATE XXX_NVCFG_ORCLGEN_INV_SYNC
               SET PROCESSING_STATUS =
                      PROCESSING_STATUS || '  ' ||   'Reservation Hold Successfully Done For Quantity : '
                      || ln_reserve_qty,
                   ACTION_PERFORMED =
                      ACTION_PERFORMED || '   ' || gv_reservation_action
             WHERE LOT_NUMBER = lv_LOT_NUMBER AND Request_id = gn_request_id;
         ELSE
            IF ln_msg_count = 0
            THEN
               x_error := 'Error by Reserve Order API ' || lv_msg_data;
               XXX_log_messages_pkg.display_msg (x_error);

               UPDATE XXX_NVCFG_ORCLGEN_INV_SYNC
                  SET PROCESSING_STATUS = PROCESSING_STATUS || '  ' || x_error
                WHERE     LOT_NUMBER = lv_LOT_NUMBER
                      AND Request_id = gn_request_id;
            ELSE
               FOR l_index IN 1 .. ln_msg_count
               LOOP
                  fnd_msg_pub.get (p_msg_index       => l_index,
                                   p_data            => lv_msg_data,
                                   p_encoded         => 'F',
                                   p_msg_index_out   => l_msg_index_out);
                  x_error := 'Error by Reserve Order API ' || lv_msg_data;
                  XXX_log_messages_pkg.display_msg (x_error);
               END LOOP;
            END IF;
         END IF;
      ELSE
         UPDATE XXX_NVCFG_ORCLGEN_INV_SYNC
            SET PROCESSING_STATUS =
                   PROCESSING_STATUS ||  '  ' ||  'Reserve Order API fails due to allocation to reserve qty is less then the adjustmend qty.'
          WHERE     lot_number = lv_lot_number
                AND TRUNC (PROCESSING_DATE) = TRUNC (SYSDATE)
                AND ROW_PROCESSED_FLAG = 'N';
      END IF;
   --END LOOP;

   EXCEPTION
      WHEN OTHERS
      THEN
         x_error :=
            'Exception in call_reserve_order ' || SQLCODE || '-' || SQLERRM;
         XXX_log_messages_pkg.display_msg (x_error);
   END;

Sub inventory Transfer Using Standard API

Hi Guys,

I Recently , got a requirement to make sub inventory transfer using the standard API.

So Here is the code.


  PROCEDURE call_subinv_transfer (p_qty               IN NUMBER,
                                   p_subinv            IN VARCHAR2,
                                   p_item_id           IN NUMBER,
                                   p_organziation_id   IN NUMBER,
                                   p_uom_code          IN VARCHAR2,
                                   p_transaction_ref   IN VARCHAR2,
                                   p_lot_number        IN VARCHAR2,
                                   p_exp_date             DATE,
                                   p_status_id            NUMBER)
   IS
      lv_transaction_source_name    VARCHAR2 (30);
      ln_trnasaction_type_id        NUMBER;
      lv_source_code                VARCHAR2 (30)
                                       := 'XXX_Subinventory_Source_Name';
      gv_reservation_subinv VARCHAR2(30) := 'XXX_SUBINV_CODE';
      gd_date DATE := SYSDATE;

      re_exception                  EXCEPTION;


      ln_transaction_interface_id   NUMBER;
      lv_return_value               VARCHAR2 (50);
      lv_pt_return_status           VARCHAR2 (10);
      ln_msg_idx_cnt                NUMBER;
      lv_pt_msg_data                VARCHAR2 (4000);
      ln_trans_count                NUMBER;
      ln_row_count                  NUMBER;
      l_rowid                       NUMBER;
      ln_qty                        NUMBER;

      lv_subinv                     VARCHAR2 (100);
      ln_item_id                    NUMBER;
      ln_organization_id            NUMBER;
      lv_uom_code                   VARCHAR2 (30);
      lv_transaction_ref            VARCHAR2 (100);
      lv_lot_number                 VARCHAR2 (100);
      ld_exp_date                   DATE;
      ln_status_id                  NUMBER;
      ln_adj_qty                    NUMBER;

      x_retcode                     NUMBER;
      x_errbuf                      VARCHAR2 (250);
   BEGIN
      x_retcode := 0;
      x_errbuf := NULL;

      ln_qty := p_qty;
      lv_subinv := p_subinv;
      ln_item_id := p_item_id;
      ln_organization_id := p_organziation_id;
      lv_uom_code := p_uom_code;
      lv_transaction_ref := p_transaction_ref;
      lv_lot_number := p_lot_number;
      ld_exp_date := p_exp_Date;
      ln_status_id := p_status_id;



 --     xxx_FNDLOG.LOG ('Starting Subinv Procedure');

      ln_transaction_interface_id := mtl_material_transactions_s.NEXTVAL;

--------------------Store transaction type id in Any lookup, so that it can be used in future customizations---------

      SELECT LOOKUP_CODE
        INTO ln_trnasaction_type_id
        FROM fnd_lookup_values
       WHERE     lookup_type = 'XXX_LOOKUP_CODE'-------------
             AND enabled_flag = 'Y'
             AND TAG = 'SUB_INV_TRANSFER'
             AND LANGUAGE(+) = USERENV ('LANG')
             AND TRUNC (SYSDATE) BETWEEN NVL (start_date_active, SYSDATE - 1)
                                     AND NVL (end_date_active, SYSDATE + 1);

      BEGIN
         INSERT INTO MTL_TRANSACTIONS_INTERFACE (transaction_interface_id,
                                                 transaction_header_id,
                                                 distribution_account_id,
                                                 inventory_item_id,
                                                 source_code,
                                                 source_header_id,
                                                 source_line_id,
                                                 transaction_source_name,
                                                 transaction_source_id,
                                                 process_flag,
                                                 transaction_mode,
                                                 transaction_quantity,
                                                 organization_id,
                                                 subinventory_code,
                                                 locator_id,
                                                 transfer_organization,
                                                 transfer_subinventory,
                                                 transfer_locator,
                                                 transaction_type_id,
                                                 transaction_date,
                                                 transaction_uom,
                                                 reason_id,
                                                 created_by,
                                                 creation_date,
                                                 last_updated_by,
                                                 last_update_date,
                                                 transaction_reference)
                 VALUES (
                           ln_transaction_interface_id,
                           ln_transaction_interface_id,
                           '',
                           ln_item_id,
                           lv_source_code,
                           ln_transaction_interface_id,
                           ln_transaction_interface_id,
                           '',
                           '',
                           1,
                           2,
                           ln_qty,
                           ln_organization_id,
                           gv_reservation_subinv,
                           find_location (ln_organization_id,---------------------Function created to fetch locator id , based on organization id and subinventory code
                                          gv_reservation_subinv),
                           '',
                           p_subinv,
                           find_location (ln_organization_id, p_subinv),
                           ln_trnasaction_type_id,
                           gd_date,
                           lv_uom_code,
                           '',
                           gn_user_id,
                           gd_Date,
                           gn_user_id,
                           gd_Date,
                           lv_transaction_ref);
      EXCEPTION
         WHEN OTHERS
         THEN
            xxx_FNDLOG.LOG ('Error in Interface  ' || SQLERRM);
      END;


     -- xxx_fndlog.LOG ('Inserting data in LoT interface Table');

      BEGIN
         INSERT
           INTO MTL_TRANSACTION_LOTS_INTERFACE (transaction_interface_id,
                                                last_update_date,
                                                last_updated_by,
                                                creation_date,
                                                created_by,
                                                lot_number,
                                                lot_expiration_date,
                                                transaction_quantity,
                                                status_id)
         VALUES (ln_transaction_interface_id,
                 gd_Date,
                 gn_user_id,
                 gd_Date,
                 gn_user_id,
                 lv_lot_number,
                 ld_exp_date,
                 ln_qty,
                 ln_status_id);
      EXCEPTION
         WHEN OTHERS
         THEN
            xxx_FNDLOG.LOG ('Error in Lot Interface  ' || SQLERRM);
      END;



      -- Call the Inventory Transaction Manager for the specific transaction
      lv_return_value :=
         inv_txn_manager_pub.process_transactions (
            p_api_version     => 1.0,
            x_return_status   => lv_pt_return_status,
            x_msg_count       => ln_msg_idx_cnt,
            x_msg_data        => lv_pt_msg_data,
            x_trans_count     => ln_trans_count,
            p_table           => 1,
            p_header_id       => ln_transaction_interface_id);
      xxx_FNDLOG.LOG (
         ' Standard API Starts lv_pt_return_status' || lv_pt_return_status);

      IF NVL (lv_pt_return_status, 'E') = 'E'
      THEN
         BEGIN
            SELECT error_explanation
              INTO lv_pt_msg_data
              FROM mtl_transactions_interface
             WHERE transaction_interface_id = ln_transaction_interface_id;

            xxx_FNDLOG.LOG (
                  ' Error in call_subinv_transfer For Lot :   '
               || lv_lot_number
               || ' -  '
               || lv_pt_msg_data);
         END;

         x_errbuf :=
               'Error Occurred in inv_txn_manager_pub.process_transactions.. Error Details -'
            || lv_pt_msg_data;

         RAISE re_exception;
      END IF;

      IF lv_pt_return_status = 'S'
      THEN
       
         xxx_log_messages_pkg.display_msg (
               'Material Transaction Processor Successfully Transfered:'
            || x_errbuf);
      END IF;

      xxx_log_messages_pkg.display_msg (
         'Error in Material Transaction Processor: ' || x_errbuf);
   --    END LOOP;
   EXCEPTION
      WHEN re_exception
      THEN
         x_retcode := 2;
         xxx_FNDLOG.LOG (' Error in Process data  ' || SQLERRM);
      WHEN OTHERS
      THEN
         x_retcode := 2;
         x_errbuf := ' Error Occured in Process data .-' || SQLERRM;
   END;