1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Want to update subinventory and locators in routing through api/interface

Discussion in 'Oracle Apps Technical' started by saurabhdj, Sep 28, 2015.

  1. saurabhdj

    saurabhdj Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    75
    Hello Guruz,

    We have task where we need to 'Update' "completion_subinventory" [subinventory] and "Locator" columns from Routing. We need to update Subinventory and Locator with "blank" values.

    We tried it with interface and API too in 'Update' mode.
    When any-other "Subinevntory" and "locator" value is given, it is getting updated perfectly [in "Routing] but we are unable 'Update' the Subinventory" and "Locators" with "blank" values

    Question is how shall we achieve it?
    Method 1 we tried:-
    ====================

    INSERT INTO bom_op_routings_interface (assembly_item_number, organization_code, process_flag,completion_subinventory, transaction_type, routing_type)
    VALUES('00015-001', 'KO1', 1,NULL, 'UPDATE', 1 );
    commit;

    Method 2 we tried:-
    ======================

    DECLARE
    v_null VARCHAR (10);

    begin
    v_null:= NULL;
    DBMS_OUTPUT.put_line('Printing Null: ' || v_null)

    INSERT INTO bom_op_routings_interface (assembly_item_number, organization_code, process_flag, completion_subinventory, transaction_type, routing_type )
    VALUES('00015-001', 'K01', 1,v_null, 'UPDATE', 1 );

    commit;

    END;
    ========

    By both of above approaches, expected output could not achieved.
    So how can we 'update' 'blank' values in "Subinevntory" and "locator" fields for Routing?

    Thanks !
     
  2. saurabhdj

    saurabhdj Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    75
    ok hiii here we go .. i got the answer to this question.

    with reference from Oracle Doc ID 287488.1,
    For API
    ----------
    To properly null out values you would need to use one of the following special values defined in the API:

    FND_API.G_MISS_NUM for NUMBER type.
    FND_API.G_MISS_CHAR for VARCHAR2 type.
    FND_API.G_MISS_DATE for DATE type.

    So if you want to reset PERSON_FIRST_NAME to null, you need to call the API and pass it:
    PERSON_FIRST_NAME := FND_API.G_MISS_CHAR;

    For Interface
    -----------------
    Similarly for interfaces, for same acquirement you can pass 'chr(0)' in the column[with char datattype] value which we want to set to blank/null.

    Hope this helps... :)
    Thank you!