1 2 3 Previous Next 41 Replies Latest reply: Aug 8, 2012 10:03 AM by 954128 RSS

    Code Explanation

    954128
      Hi Gary,

      Please help me out with the code. Can you please explain it to me. thanks.
        • 1. Re: Code Explanation
          damorgan
          Please be considerate of others and post once, and only once, on a subject.

          Thank you.

          BTW: The reason, most likely, that no one responded until I did is because you did not read the FAQ and you do not seem to appreciate what you are asking of volunteers.
          • 2. Re: Code Explanation
            Etbin
            Sorry cannot figure out more than Daniel did.
            PROCEDURE show_item_detail(fv_item_number          in varchar2              default null,
                                       fv_item_category_code   in varchar2              default null,
                                       fv_category_page_number in number                default 1,
                                       fv_section              in varchar2              default 'highlights',
                                       fv_user                 in varchar2              default null,
                                       fv_lang                 in varchar2              default null,
                                       fv_page_code            in varchar2              default null,
                                       v_error_message         in varchar2              default null,
                                       fv_gift_certificate     in number                default null,
                                       fv_gift_code            in varchar2              default null,
                                       fv_order_id             in varchar2              default null,
                                       fv_fname                in varchar2              default null,
                                       fv_lname                in varchar2              default null,
                                       fv_email                in varchar2              default null,
                                       fv_message              in varchar2              default null,
                                       ppt_master_item         in gpt_master_item_type  default gpt_master_item,
                                       ppt_relship_type        in gpt_relship_type_type default gpt_relship_type,
                                       pn_page_count           in number                default null,
                                       fv_start_price          in varchar2              default null,
                                       fv_end_price            in varchar2              default null,
                                       pn_show_photo           in number                default 0,
                                       pn_show_item_host       in number                default 0,
                                       X                       in number                default 0,
                                       Y                       in number                default 0
                                      )
            is
            
              cursor lcr_script is
              select script_id
                from coe_scripts
               where display_code = DECODE(cad$user_api.Get_User_Country_Base,'CAN','TUPCANONLY','USA','TUPUSAONLY');
            
              ln_track_count    number;
              ln_trace          number := nvl(cad$param.get_parameter_number_value('SHOW_ITEM_TRACE'), 0);
              le_no_item_chosen exception;
              le_show_pdc       exception;
              
            begin
            
              if ln_trace = 1 then
                dbms_session.set_sql_trace(true);
              end if;
            /* if they don't have a item number; it must be a legacy call to 'category' */
              if fv_item_number is null and fv_item_category_code is not null then
                owa_util.redirect_url('!tw$shop.p_category?pv_ic_code='||fv_item_category_code);
              end if;
            
              gv_sli_search_url := null;
            /*
            -- htp.p(' Page Start => '|| to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
            -- htp.br; 
            */
            /* Get the Parameter values. */
              gv_procedure_name := 'tup_show_item.show_item_detail';
              gv_item_number := fv_item_number;
              if fv_item_category_code = '0' then
                gv_category_code := null;
              else
                gv_category_code := fv_item_category_code;
              end if;
              gv_category_page_num := nvl(fv_category_page_number, 1);
              gv_error_message := v_error_message;
              gn_page_number := to_number(gv_category_page_num);
              gn_gift_certificate := fv_gift_certificate;
              gv_gift_code := fv_gift_code;
              gv_fname := fv_fname;
              gv_lname := fv_lname;
              gv_email := fv_email;
              gv_message := fv_message;
              gv_section := fv_section;
              gv_user := fv_user;
              gv_lang := fv_lang;
              gn_order_id := fv_order_id;
              gn_page_count := pn_page_count;
              gn_start_price := fv_start_price;
              gn_end_price := fv_end_price;
            /*
            -- htp.p('Item_number: ' || gv_item_number );
            -- htp.br;
            */
              if ppt_master_item.count > 0 then
            /* htp.p('alert("Array Count:'|| to_char(ppt_master_item.count)||' ");'); */
                gpt_master_item := ppt_master_item;
                gpt_relship_type := ppt_relship_type;
              end if;
            
            /* Product Demonstration Central */
              if gv_category_code = gkv_pdc_category and gv_item_number is null then
                gb_show_pdc := true;
              elsif gv_item_number is not null and pn_show_photo = 1 then
                gb_show_item_photo := true;
              elsif pn_show_item_host = 1 then
                gb_show_item_host := true;
              end if;
            
              if gv_item_number = gkv_cm_item_code then
                raise ge_no_parent_item;
              end if;
            
            /* Check for Static Pages and Access Privileges */
              if not f_check_access_privilege (fv_item_number => gv_item_number) then
                return;
              end if;
            
              open lcr_script;
              fetch lcr_script into gn_script_id;
              close lcr_script;
            
            /* need to category info for WebTrends and for whether the catcode is a by price. */
              GRD_CATEGORY := twx$category.f_nav_get(gv_category_code);
            
            /* Get Category */
              if GRD_CATEGORY.price.by_price then
                if gv_item_number = gkv_gift_certificate then
                  gv_def_category := gv_category_code;
                  gv_category_code := gkv_gc_category;
                else
                  gv_category_code := nvl(gv_category_code, gkv_def_price_category);
                end if;
              elsif gv_item_number is not null and gv_category_code is null then
                open gcr_get_category (cpv_item_number => gv_item_number);
                fetch gcr_get_category into gv_category_code;
                close gcr_get_category;
              end if;
            
              if gv_category_code is null then
            /*
            --  htp.p('No Category. ');
            --  owa_util.redirect_url('home');
            --  return;
            */
                raise ge_no_parent_item;
              end if;
            
              if (gn_order_id is null and gn_gift_certificate is null) and gv_error_message is null then
                gn_order_id := coe$www.get_current_order_id;
              end if;
            /*
            -- htp.p(' Begin Get data => '|| to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
            -- htp.br;
            */
              p_retrieve_data;
            /*
            -- htp.p(' End Get data => '|| to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
            -- htp.br;
            */
              gv_refer_url := gv_procedure_name||'?fv_item_number='||gv_item_number||'';
              gv_sli_search_url := gv_procedure_name||'?fv_item_number='||gv_item_number||'';
            
              if fv_item_category_code is not null then
                gv_refer_url := gv_refer_url || '&fv_item_category_code='||nvl(gv_def_category, fv_item_category_code)||'';
              end if;
            
              if gn_start_price is not null or gn_end_price is not null then
                gv_refer_url := gv_refer_url || '&fv_start_price=' || to_char(nvl(gn_start_price, 0))
                                             || '&fv_end_price='||to_char(nvl(gn_end_price,999))||'';
              end if;
            
              gv_page_ref_url := gkv_default_path||gv_refer_url;
            
              if fv_category_page_number is not null then
                gv_refer_url := gv_refer_url || '&fv_category_page_number='||gv_category_page_num||'';
              end if;
            
              if gv_section is not null then
                gv_refer_url := gv_refer_url || '&fv_section='||gv_section||'';
              end if;
            /*
            -- htp.p(' Begin Show page => '|| to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
            -- htp.br;
            */
            /* Display Page */
              p_show_page(fv_item_category_code,fv_page_code);
            /*
            -- htp.p(' End Show Page => '|| to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
            -- htp.br;
            */
              if ln_trace = 1 then
                dbms_session.set_sql_trace(false);
              end if;
              
            exception
              when ge_no_parent_item
              then coe$include.no_item_available;
              when le_no_item_chosen
              then coe$include.no_item_available;
              when ge_ip_address_restricted
              then coe$include.generic_error_page;
              when others
              then htp.p('Error: '|| sqlerrm);
                   htp.br;
                   owa_util.redirect_url('home');
            end show_item_detail;
            Regards

            Etbin
            • 3. Re: Code Explanation
              damorgan
              Thanks for formatting it ... I will not waste my time formatting other people's code ... but now I can definitely state that this code will never compile and never run and never do anything.

              Here's another undeclared variable.
              GRD_CATEGORY := twx$category.f_nav_get(gv_category_code);
              GRD_CATEGORY is defined where?

              To the OP ... this is not worth spending 5 minutes on. Throw it away and get the business rules.

              Here's some code that will accomplish just as much as what you posted with far less effort
              CREATE OR REPLACE PROCEDURE blivet AUTHID DEFINER IS
              BEGIN
                x := 1;
              END blivet;
              /
              it too will never compile and never run.
              • 4. Re: Code Explanation
                954128
                Hi. Thank you for your help and I really appreciate all your timing on helping me.
                • 5. Re: Code Explanation
                  954128
                  Hi,

                  I appreciate your help. Thank you. Here is what this code does. Please help me out figuring the error message.

                  For example. I have 2 promotion items item A and item B.

                  Item A : Limit one, at a discounted price with each $50 order.
                  (ie. Item A is eligible for the discounted price only when we made $50 purchase.)

                  Item B : Limit one,at a discounted price with each $100 order.

                  Now, to qualify for both offers, the customer must place $150 in their shopping cart (not including the discounted product price).

                  When the discounted item is listed for promotion, it is already assigned with the eligible dollor amount.

                  When a customer has made purchase of $100, they can qualify to purchase 2 $50 promotion items. But my issue is it does'nt allow me to qualify for that. Instead its throwing up a message, that Minimum dollor amount is required to qualify.

                  ie : total of $150 should be made to qualify.

                  That is the code where that actually works. Can you please help me out figuring the error message. thanks.

                  Regards,

                  Sara
                  • 6. Re: Code Explanation
                    Venkadesh Raja
                    951125 wrote:
                    Hi,

                    I appreciate your help. Thank you. Here is what this code does. Please help me out figuring the error message.

                    For example. I have 2 promotion items item A and item B.

                    Item A : Limit one, at a discounted price with each $50 order.
                    (ie. Item A is eligible for the discounted price only when we made $50 purchase.)
                    I don't see any forumula or any variable defined like
                    if item.purchase >=50 then
                    --Apply Discount;
                    end if;
                    • 7. Re: Code Explanation
                      Etbin
                      If Minimum dollor amount is required to qualify is the message text, the operations/manipulations you describe are most probably done elsewhere.
                      Try
                      select *
                        from user_source
                       where instr(text,'Minimum dollor amount is required to qualify') > 0 
                      /* some exact portion of message text or use upper(text)/lower(text): dollor looks like a typo */
                      to find the procedure/package body/ ... that is generating the message
                      If not successful, use [url http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_2064.htm#i1588578]all_source to identify the owner

                      Regards

                      Etbin
                      • 8. Re: Code Explanation
                        954128
                        Hi Venkadesh,

                        Thank you so much for your time. Can you please help me in formatting the code to my scenario. Thanks.

                        Regards,

                        Sara
                        • 9. Re: Code Explanation
                          954128
                          Hi Etbin,

                          Thank you so much. Let me try what you suggested and see what happens. Thanks.

                          Regards,

                          Sara
                          • 10. Re: Code Explanation
                            954128
                            When i tried,

                            **select * from user_source where instr (text,'minimum dollor amount is required to qualify') > 0**

                            When executing the result set is,

                            Name       Type     Line    Text.

                            Can you please explain me what is happening in the above sql query. Please. Thank you. I would be greatful to you.

                            Regards

                            Sara
                            • 11. Re: Code Explanation
                              APC
                              951125 wrote:

                              Can you please explain me what is happening in the above sql query. Please. Thank you. I would be greatful to you.
                              That query returns no rows because no program in your user's schema contains that string. Just to remind you, 'minimum dollor amount is required to qualify' was the error message you said you are getting and you want to know why. As I have already pointed out, it doesn't appear in the code you posted so presumably some other program is spitting it out.

                              Please read [url https://forums.oracle.com/forums/thread.jspa?messageID=10503807#10503807]my response to your previous question. This is your codebase. This is your system and your business domain. You are supposed to understand the business logic and the suite of programs which implement it. If you don't then you need to ask your colleagues or your boss, because there is no chance that a bunch of random strangers will be able to explain your system to you.

                              Good luck, APC
                              • 12. Re: Code Explanation
                                Etbin
                                Try
                                select *
                                  from all_source
                                 where instr(lower(text),'amount is required to qualify') > 0
                                Oracle's dictionary table <tt> all_source </tt> will be searched for the string <tt> 'amount is required to qualify' </tt>
                                If rows are found you'd discover the database object and its owner within the respective columns.
                                If no data is found it means no Database procedure, function, package, trigger contains that string so most probably doesn't generate the error message you mentioned.

                                Regards

                                Etbin
                                • 13. Re: Code Explanation
                                  954128
                                  Thank you. But my point is when I tried select * from user_source, the result set is showing me all the fields in the name,type,line and text column. Here the type column has the default value has package. My job is to make modifications on the existing code which satisfies the condition,
                                  when a customer made a purchase of $100.00, he is eligible for two $50 promotion item.

                                  But currently, its showing that, each promotion item is eligible only for the particular dollor amount.
                                  ie) Item A (promotion item) -----------> Actual price $19.00

                                  -----------> Discounted price $ 10.00

                                  ------------> Promotion is valid if the total purchase is $ 50.00


                                  Item B (Promotion item) ----------------> Actual Price $35.00

                                  ----------------> Discounted price $25.00

                                  ----------------> Promotion is valid if the total purchase is $100.00

                                  Currently how it is implementing is, If suppose, a customer has made total of $100.00, he is eligble only for Item B.

                                  But what I need to fullfil is, if the total transcation is made $100.00 or above, so the customer is eligible for both Item A and Item B. This is what I need.

                                  Can you please help me out. Thanks. I would really appreciatre your help and will never forget it.

                                  Regards,

                                  Sara
                                  • 14. Re: Code Explanation
                                    954128
                                    Thank you. But my point is when I tried select * from user_source, the result set is showing me all the fields in the name,type,line and text column. Here the type column has the default value has package. My job is to make modifications on the existing code which satisfies the condition,
                                    when a customer made a purchase of $100.00, he is eligible for two $50 promotion item.

                                    But currently, its showing that, each promotion item is eligible only for the particular dollor amount.
                                    ie) Item A (promotion item) -----------> Actual price $19.00

                                    -----------> Discounted price $ 10.00


                                    ------------> Promotion is valid if the total purchase is $ 50.00



                                    Item B (Promotion item) ----------------> Actual Price $35.00


                                    ----------------> Discounted price $25.00


                                    ----------------> Promotion is valid if the total purchase is $100.00

                                    Currently how it is implementing is, If suppose, a customer has made total of $100.00, he is eligble only for Item B.

                                    But what I need to fullfil is, if the total transcation is made $100.00 or above, so the customer is eligible for both Item A and Item B. This is what I need.

                                    Can you please help me out. Thanks. I would really appreciatre your help and will never forget it.

                                    Regards,

                                    Sara
                                    1 2 3 Previous Next