13 Replies Latest reply: May 19, 2010 11:49 AM by Luko RSS

    OM Defaulting Rule Customization

    fendy_chang
      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
          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
            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
              Hello,

              Past your code it will help to reslove this issue.

              Regards,
              Luko
              • 4. Re: OM Defaulting Rule Customization
                fendy_chang
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                Hi Luko,

                                Its return null value.

                                Regards,
                                Fendy
                                • 13. Re: OM Defaulting Rule Customization
                                  Luko
                                  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