This discussion is archived
13 Replies Latest reply: May 19, 2010 9:49 AM by Luko RSS

OM Defaulting Rule Customization

fendy_chang Newbie
Currently Being Moderated
Hi All,

I have requirement to derive Order Header Sales Channel Code from Sales Person DFF attribute.
Try to custom using Sales Channel defaulting rule but referencing to ONT_HEADER_DEF_HDLR.g_record.salesrep_id always return FND_API.G_MISS_NUM value.
Any idea on how to solve this issue?

TIA,
Fendy
  • 1. Re: OM Defaulting Rule Customization
    Luko Journeyer
    Currently Being Moderated
    Hello,

    I had similar problem Default rule in Order Management 11.5.10.2 here you have solution 790103.1.

    Regards,
    Luko
  • 2. Re: OM Defaulting Rule Customization
    fendy_chang Newbie
    Currently Being Moderated
    Hi Luko,

    I already refer to ONT_HEADER_DEF_HDLR.g_record.salesrep_id not direct query to oe_order_headers_all table. But still the value always equal to FND_API.G_MISS_NUM.

    Regards,
    Fendy
  • 3. Re: OM Defaulting Rule Customization
    Luko Journeyer
    Currently Being Moderated
    Hello,

    Past your code it will help to reslove this issue.

    Regards,
    Luko
  • 4. Re: OM Defaulting Rule Customization
    fendy_chang Newbie
    Currently Being Moderated
    Hi Luko,

    Here is my code which i call under sales channel defaulting rules setup screen.

    CREATE OR REPLACE PACKAGE APPS.xx03ont_oe_default AS

    FUNCTION GET_SALES_CHANNEL
    ( p_database_object_name IN VARCHAR2
    ,p_attribute_code IN VARCHAR2
    )
    RETURN VARCHAR2;
    END xx03ont_oe_default;
    /

    CREATE OR REPLACE PACKAGE BODY APPS.xx03ont_oe_default AS
    FUNCTION GET_SALES_CHANNEL
    ( p_database_object_name IN VARCHAR2
    ,p_attribute_code IN VARCHAR2
    )
    RETURN VARCHAR2
    IS
    l_return VARCHAR2(100);
    BEGIN
    IF ONT_HEADER_Def_Hdlr.g_record.salesrep_id != FND_API.G_MISS_NUM THEN

    SELECT ATTRIBUTE2
    INTO l_return
    FROM ra_salesrep_territories
    WHERE salesrep_id = ONT_HEADER_DEF_HDLR.g_record.salesrep_id;
    --
    RETURN l_return;
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RETURN null;
    END;
    END xx03ont_oe_default;
    /

    FYI, i change the default sequence of Sales Person to 30 (default 50) lower than Sales Channel (50) so it will take place first.

    After review ONT_HEADER_DEF_HDLR, i suspect ONT_HEADER_Def_Hdlr.g_record.salesrep_id value = FND_API.G_MISS_NUM is due to inside ONT_HEADER_DEF_HDLR.default_record function defaulting for SALES_CHANNEL_CODE always take precedence than SALESREP_ID so ONT_HEADER_Def_Hdlr.g_record.salesrep_id have not been populated.

    l_attr:= 'SALES_CHANNEL_CODE';

    IF g_record.SALES_CHANNEL_CODE = FND_API.G_MISS_CHAR THEN
    -- Get the defaulting api registered in the AK AND default
    l_attr:=l_attr||' 1';
    g_record.SALES_CHANNEL_CODE := ONT_D1_SALES_CHANNEL_CODE.Get_Default_Value(g_record);
    l_attr:=l_attr||' 2';
    -- For UPDATE operations, check security if new defaulted value is not equal to old value
    IF g_record.operation = OE_GLOBALS.G_OPR_UPDATE THEN
    l_attr:=l_attr||' 3';
    IF NOT OE_GLOBALS.Equal(g_record.sales_channel_code, p_in_old_rec.sales_channel_code) THEN
    IF OE_HEADER_SECURITY.SALES_CHANNEL_CODE(p_record => g_record, x_on_operation_action     => l_action) = OE_PC_GLOBALS.YES THEN
    -- Raise error if security returns YES, operation IS CONSTRAINED
    RAISE FND_API.G_EXC_ERROR;
    END IF;
    OE_GLOBALS.G_ATTR_UPDATED_BY_DEF := 'Y';
    END IF;
    END IF;
    -- There is no validation api registered in the AK dictionary
    END IF;
    l_attr:= 'SALESREP_ID';

    IF g_record.SALESREP_ID = FND_API.G_MISS_NUM THEN
    -- Get the defaulting api registered in the AK AND default
    l_attr:=l_attr||' 1';
    g_record.SALESREP_ID := ONT_D1_SALESREP_ID.Get_Default_Value(g_record);
    l_attr:=l_attr||' 2';
    IF g_record.SALESREP_ID IS NULL
    AND p_in_old_rec.SALESREP_ID <> FND_API.G_MISS_NUM THEN
    g_record.SALESREP_ID := p_in_old_rec.SALESREP_ID;
    END IF;
    -- For UPDATE operations, check security if new defaulted value is not equal to old value
    IF g_record.operation = OE_GLOBALS.G_OPR_UPDATE THEN
    l_attr:=l_attr||' 3';
    IF NOT OE_GLOBALS.Equal(g_record.salesrep_id, p_in_old_rec.salesrep_id) THEN
    IF OE_HEADER_SECURITY.SALESREP(p_record => g_record, x_on_operation_action     => l_action) = OE_PC_GLOBALS.YES THEN
    -- Raise error if security returns YES, operation IS CONSTRAINED
    RAISE FND_API.G_EXC_ERROR;
    END IF;
    OE_GLOBALS.G_ATTR_UPDATED_BY_DEF := 'Y';
    END IF;
    END IF;
    IF g_record.SALESREP_ID IS NOT NULL THEN
    l_attr:=l_attr||' 4';
    -- Validate defaulted value if not null
    IF OE_VALIDATE.SALESREP(g_record.SALESREP_ID) THEN
    -- There is no dependent api registered in the AK dictionary
    NULL;
    l_attr:=l_attr||' 5';
    ELSE
    g_record.SALESREP_ID := NULL;
    l_attr:=l_attr||' 6';
    END IF;
    END IF;
    END IF;

    Any idea on how to resolve this issue?

    TIA,
    Fendy
  • 5. Re: OM Defaulting Rule Customization
    Luko Journeyer
    Currently Being Moderated
    Hello,

    Look at 414115.1. Look into impotent note point 6 you have to run request to recompile ONT_HEADER_DEF_HDLR and change sequence in it.

    Regards,
    Luko
  • 6. Re: OM Defaulting Rule Customization
    fendy_chang Newbie
    Currently Being Moderated
    Hi Luko,

    After running Default Generator from SRS window, ONT_HEADER_DEF_HDLR has been reordered.
    But still defaulting rule for sales channel which is derived from salesperson is not working.

    Does it need to create dependency between salesperson and sales channel by changing Oracle seeded package?

    TIA,
    Fendy
  • 7. Re: OM Defaulting Rule Customization
    Luko Journeyer
    Currently Being Moderated
    Hello,

    SELECT ATTRIBUTE2

    FROM ra_salesrep_territories


    This sql returns any data?

    Regards,
    Luko


    Past this to you pl/sql

    SELECT ATTRIBUTE2
    INTO l_return
    FROM ra_salesrep_territories
    WHERE where rownum<=1

    and retest your issue.

    Edited by: Luko on 2010-05-17 13:17

    Edited by: Luko on 2010-05-17 13:20
  • 8. Re: OM Defaulting Rule Customization
    fendy_chang Newbie
    Currently Being Moderated
    Hi Luko,

    Yes, the query return the result. Have change it to return just the first row, still the same.

    Regards,
    Fendy
  • 9. Re: OM Defaulting Rule Customization
    773287 Newbie
    Currently Being Moderated
    Fendy
    The second Option is most probably your solution. It will work Even if you change salesrep after defaulting sales channel. Other option is to default this in lines ( hope that field is there) based on header salesrep.
  • 10. Re: OM Defaulting Rule Customization
    fendy_chang Newbie
    Currently Being Moderated
    Hi Luko,

    Seems like its Oracle bugs.
    First time i open Sales Order Form, its trigger ONT_HEADER_Def_Hdlr package

    1)
    l_attr:= 'SALESREP_ID';

    IF g_record.SALESREP_ID = FND_API.G_MISS_NUM THEN
    -- Get the defaulting api registered in the AK AND default
    l_attr:=l_attr||' 1';
    g_record.SALESREP_ID := ONT_D1_SALESREP_ID.Get_Default_Value(g_record);
    l_attr:=l_attr||' 2';
    IF g_record.SALESREP_ID IS NULL
    AND p_in_old_rec.SALESREP_ID <> FND_API.G_MISS_NUM THEN
    g_record.SALESREP_ID := p_in_old_rec.SALESREP_ID;
    END IF;
    -- For UPDATE operations, check security if new defaulted value is not equal to old value
    IF g_record.operation = OE_GLOBALS.G_OPR_UPDATE THEN
    l_attr:=l_attr||' 3';
    IF NOT OE_GLOBALS.Equal(g_record.salesrep_id, p_in_old_rec.salesrep_id) THEN
    IF OE_HEADER_SECURITY.SALESREP(p_record => g_record, x_on_operation_action => l_action) = OE_PC_GLOBALS.YES THEN
    -- Raise error if security returns YES, operation IS CONSTRAINED
    RAISE FND_API.G_EXC_ERROR;
    END IF;
    OE_GLOBALS.G_ATTR_UPDATED_BY_DEF := 'Y';
    END IF;
    END IF;
    IF g_record.SALESREP_ID IS NOT NULL THEN
    l_attr:=l_attr||' 4';
    -- Validate defaulted value if not null
    IF OE_VALIDATE.SALESREP(g_record.SALESREP_ID) THEN
    -- There is no dependent api registered in the AK dictionary
    NULL;
    l_attr:=l_attr||' 5';
    ELSE
    g_record.SALESREP_ID := NULL;
    l_attr:=l_attr||' 6';
    END IF;
    END IF;
    END IF;

    which return g_record.SALESREP_ID = FND_API.G_MISS_NUM
    then l_attr:= 'SALES_CHANNEL_CODE' is being called

    2)
    IF g_record.SALES_CHANNEL_CODE = FND_API.G_MISS_CHAR THEN
    -- Get the defaulting api registered in the AK AND default
    l_attr:=l_attr||' 1';
    g_record.SALES_CHANNEL_CODE := ONT_D1_SALES_CHANNEL_CODE.Get_Default_Value(g_record);
    l_attr:=l_attr||' 2';
    -- For UPDATE operations, check security if new defaulted value is not equal to old value
    IF g_record.operation = OE_GLOBALS.G_OPR_UPDATE THEN
    l_attr:=l_attr||' 3';
    IF NOT OE_GLOBALS.Equal(g_record.sales_channel_code, p_in_old_rec.sales_channel_code) THEN
    IF OE_HEADER_SECURITY.SALES_CHANNEL_CODE(p_record => g_record, x_on_operation_action => l_action) = OE_PC_GLOBALS.YES THEN
    -- Raise error if security returns YES, operation IS CONSTRAINED
    RAISE FND_API.G_EXC_ERROR;
    END IF;
    OE_GLOBALS.G_ATTR_UPDATED_BY_DEF := 'Y';
    END IF;
    END IF;
    -- There is no validation api registered in the AK dictionary
    END IF;

    which return g_record.SALES_CHANNEL_CODE is null because calling to xx03ont_oe_default_pvt.sales_channel_code return null.

    So the next visit to this ONT_HEADER_Def_Hdlr package when bill to location is entered :
    g_record.SALESREP_ID is filled but g_record.SALESREP_ID is not, due to following condition does not meet

    IF g_record.SALES_CHANNEL_CODE = FND_API.G_MISS_CHAR THEN


    I try to change this package as following (pls see the bold)
    IF g_record.SALES_CHANNEL_CODE = FND_API.G_MISS_CHAR
    OR g_record.SALES_CHANNEL_CODE IS NULL THEN -- New Added

    -- Get the defaulting api registered in the AK AND default
    l_attr:=l_attr||' 1';
    g_record.SALES_CHANNEL_CODE := ONT_D1_SALES_CHANNEL_CODE.Get_Default_Value(g_record);
    l_attr:=l_attr||' 2';
    -- For UPDATE operations, check security if new defaulted value is not equal to old value
    IF g_record.operation = OE_GLOBALS.G_OPR_UPDATE THEN
    l_attr:=l_attr||' 3';
    IF NOT OE_GLOBALS.Equal(g_record.sales_channel_code, p_in_old_rec.sales_channel_code) THEN
    IF OE_HEADER_SECURITY.SALES_CHANNEL_CODE(p_record => g_record, x_on_operation_action => l_action) = OE_PC_GLOBALS.YES THEN
    -- Raise error if security returns YES, operation IS CONSTRAINED
    RAISE FND_API.G_EXC_ERROR;
    END IF;
    OE_GLOBALS.G_ATTR_UPDATED_BY_DEF := 'Y';
    END IF;
    END IF;
    -- There is no validation api registered in the AK dictionary
    IF g_record.SALES_CHANNEL_CODE = FND_API.G_MISS_CHAR THEN
    g_record.SALES_CHANNEL_CODE := NULL;
    END IF;
    END IF;

    After changing this package, its working. Unfortunately, this is seeded package. So i revert back the code and change to use form personalization instead.

    Let me know if you have another work around.

    TIA,
    Fendy
  • 11. Re: OM Defaulting Rule Customization
    Luko Journeyer
    Currently Being Moderated
    Hello

    After you put
    SELECT ATTRIBUTE2
    INTO l_return
    FROM ra_salesrep_territories
    WHERE where rownum<=1

    Do you have first value in form or do you have null??

    Regards,
    Luko

    Edited by: Luko on 2010-05-18 20:19
  • 12. Re: OM Defaulting Rule Customization
    fendy_chang Newbie
    Currently Being Moderated
    Hi Luko,

    Its return null value.

    Regards,
    Fendy
  • 13. Re: OM Defaulting Rule Customization
    Luko Journeyer
    Currently Being Moderated
    Hello,

    Sometimes in form you can see one value but different is posted to db( example filed FOB ). If you are sure that your sql ok, create SR in metalink.

    Regards,
    Luko

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points