Link to home
Start Free TrialLog in
Avatar of Frank Danny
Frank DannyFlag for United States of America

asked on

Why don't I see Below columns in my Stored Procedure under TMP_RS?

I am PL/SQL newbie.Can anyone please tell me,Why  below columns under TMP_RS are not in my stored procedure i.e activity_search?I did  run  Stored procedure and observed under TMP_RS I see columns but I do not see anywhere in Stored Procedure/code, columns were set either by alias or anything.Am I missing something here?Below is TMP_RS results for below procedureUser generated image

[b]procedure [/b]activity_search (
    in_user_id                    in twp_user.id%type
    ,in_start_date                    in activity.activity_start_date%type
    ,in_end_date                    in activity.activity_end_date%type
    ,in_activity_type_ids            in varchar2
    ,in_description                    in varchar2
    ,in_subject                        in varchar2
    ,in_tickers                        in varchar2
    ,in_user_name                    in varchar2
    ,in_department_ids                in varchar2
    ,in_sort_by                        in varchar2
    ,in_folder_id                    in varchar2
    ,in_contact_folder_ids            in varchar2
    ,in_contact_folder_flag            in char
    ,in_company_folder_ids            in varchar2
    ,in_company_folder_flag            in char
    ,in_contact_email_flag            in varchar2
    ,in_record_per_set                in number
    ,in_attendee_id                    in number
    ,in_followup_start_date         in activity.follow_up_date%type
    ,in_followup_end_date           in activity.follow_up_date%type
    ,in_followup_user_ids           in varchar2
    ,in_activity_subtype_ids        in varchar2
    ,in_company_type_ids            in varchar2
    ,in_coverage_ids                in varchar2
    ,out_search_id                  out number
    ,out_retcode                    out number
    ,tmp_rs                        in out rs_cursor) is

    l_sql_statement                varchar2(32767) := null;
    l_sql_statement_2                varchar2(32767) := null;
    l_workaround_sql                varchar2(32767);
    tmp_cursor                    small_cursor;
    l_activity_ids                    varchar2(32767);
    tmp_twp_user_folder                twp_user_folder%rowtype;
    l_search_id                    number(12);
    l_counter                        pls_integer := 0;
    l_contact_ids                    varchar2(32767);
    type result_rectype is table of number index by binary_integer;
    activity_result_rectype            result_rectype;

    l_user_name                    varchar2(2000) := null;
    l_activity_owner                pls_integer := 0;
    l_company_folder_flag          char := null;
    l_contact_folder_flag          char := null;

begin
    out_retcode := 0;
    select seq_activity_search_result.nextval into out_search_id from dual;

    -- 15NOV2003: For better performance, rewrite the following in_user_name condition
    -- if in_user_name is not null then
    --    l_user_name := format_user_name(in_user_name);
    -- end if;

    -- 15NOV2003: New for performance
    if in_user_name is not null then
--        if instr(in_user_name, ',') = 0 then
--            l_user_name := null;
--            l_activity_owner := 1;
--        else
            l_user_name := in_user_name;
            l_activity_owner := 2;
--        end if;
    end if;


/*
    if in_user_name is not null then
        if instr(in_username, ',')

        select login into l_user_name from twp_user where id=in_user_id;
        if upper(trim(l_user_name)) = upper(trim(in_user_name)) then
            l_user_name := null;
            l_activity_owner := 1;
        else
            l_user_name := format_user_name(in_user_name);
            l_activity_owner := 2;
        end if;
    end if;
*/

    ins_debug_stmt('AC','Procedure: Activity Search Start, Search ID: '||out_search_id||' l_use_rname: '||l_user_name||' in_user_name '||in_user_name);
    /*
    ins_debug_stmt('AC','start date: '||in_start_date);
    ins_debug_stmt('AC','end date: '||in_end_date);
    ins_debug_stmt('AC','activity_type_ids: '||in_activity_type_ids);
    ins_debug_stmt('AC','description: '||in_description);
    ins_debug_stmt('AC','sort by: '||in_sort_by);
    ins_debug_stmt('AC','folder id: '||in_folder_id);
    ins_debug_stmt('AC','contact folder ids: '||in_contact_folder_ids);
    ins_debug_stmt('AC','contact folder flag: '||in_contact_folder_flag);
    ins_debug_stmt('AC','company folder id: '||in_company_folder_ids);
    ins_debug_stmt('AC','company folder flag: '||in_company_folder_flag);
    ins_debug_stmt('AC','attendee id: '||in_attendee_id);
    ins_debug_stmt('AC','subject: '||in_subject);
    ins_debug_stmt('AC','tickers: '||in_tickers);
    ins_debug_stmt('AC','username: '||in_user_name);
    ins_debug_stmt('AC','department id: '||in_department_ids);
    */
    -- Nothing was filter, only display 500 rows
    if (in_start_date is null and in_end_date is null and in_activity_type_ids is null and in_description is null
        and in_subject is null and in_tickers is null and in_user_name is null
        and in_sort_by is null and in_folder_id = 0 and in_contact_folder_ids is null
        and in_company_folder_ids is null
        and in_attendee_id is null
        and in_followup_start_date is null
        and in_followup_end_date is null
        and in_followup_user_ids is null
        and in_activity_subtype_ids is null
        and in_coverage_ids is null
        and in_company_type_ids is null) then
        select 'select * from '
                ||'(select a.id,0 company_id,0 contact_id,a.activity_start_date,a.last_update from activity a '
                ||' where a.department_id in ('|| in_department_ids ||')'
                ||' order by 4 desc nulls last, 5 desc) '
                ||' where rownum<501'
        --     select 'select a.id, 0 company_id, 0 contact_id, a.activity_start_date, a.last_update from activity a '
        --    ||' where rownum < 501 '
        into l_sql_statement
        from dual;

    -- Only filter the activity type
    elsif (in_start_date is null and in_end_date is null and in_activity_type_ids is not null and in_description is null
        and in_subject is null and in_tickers is null and in_user_name is null
        and in_sort_by is null and in_folder_id = 0 and in_contact_folder_ids is null
        and in_company_folder_ids is null
        and in_attendee_id is null
        and in_followup_start_date is null
        and in_followup_end_date is null
        and in_followup_user_ids is null
        and in_activity_subtype_ids is null
        and in_coverage_ids is null
        and in_company_type_ids is null) then
        select 'select * from '
                ||'(select a.id,0 company_id,0 contact_id,a.activity_start_date,a.last_update from activity a '
                ||' where a.activity_type_plist_id in ('||in_activity_type_ids||')'
                ||' and a.department_id in ('|| in_department_ids ||')'
                ||' order by 4 desc nulls last, 5 desc) '
                ||' where rownum<501'
        into l_sql_statement
        from dual;

    -- Filtering combination
    else
        select 'select distinct a.id,0 company_id,0 contact_id,a.activity_start_date,a.last_update from activity a'
            ||decode(in_tickers, null, null, ',activity_ticker at')
            -- 15NOV2003: ||decode(in_user_name, null, null, ',twp_user tu')
            -- ||decode(l_activity_owner, 0, null, 1, null, 2, ',twp_user tu')
            ||decode(in_folder_id, null, null, 0, null, ',twp_user_activity_folder fc')
            ||decode(in_contact_folder_ids
                ,null, decode(in_company_folder_ids, null, null, 0, null, ',contact_activity_if ca')
                ,0, null
                ,decode(in_contact_folder_flag
                        ,null, ',contact_activity_if ca'
                        , 'N', ',contact_activity_if ca'
                        , 'Y', ',contact_activity_if ca,twp_user_contact_folder tccf'
                      )
                    )
            ||decode(in_company_folder_ids
                ,null, null
                ,0, null
                ,decode(in_company_folder_flag
                        ,null, null
                        , 'N', null
                        , 'Y', ',twp_user_company_folder tcof'
                      )
                    )
            ||decode(in_coverage_ids
            ,null,null
            ,0, null
            ,', contact_activity_if ca2 ')
            ||decode(in_followup_user_ids
                 ,null, null
                 ,0, null
                 ,', activity_followup af')
            ||decode(in_company_type_ids,
                null, null,
                0, null,
                ' ,contact_activity_if ctcaif, company_type_if ctif')
            ||' where 0=0'
            ||decode(in_folder_id, null, null, 0, null,' and a.id=fc.activity_id and fc.folder_id in ('||in_folder_id||')')
            ||decode(in_contact_folder_ids
                ,null, null
                ,0, null
                ,decode(in_contact_folder_flag
                        ,null, ' and a.id=ca.activity_id and ca.contact_id in ('||in_contact_folder_ids||')'
                        ,'N',  ' and a.id=ca.activity_id and ca.contact_id in ('||in_contact_folder_ids||')'
                        ,'Y',  ' and a.id=ca.activity_id and ca.contact_id=tccf.contact_id and tccf.folder_id in ('||in_contact_folder_ids||')'
                      )
                    )
            ||decode(in_company_folder_ids
                ,null, null
                ,0, null
                ,decode(in_company_folder_flag
                        ,null, ' and a.id=ca.activity_id and ca.company_id in ('||in_company_folder_ids||')'
                        ,'N',  ' and a.id=ca.activity_id and ca.company_id in ('||in_company_folder_ids||')'
                        ,'Y',  ' and a.id=ca.activity_id and ca.company_id=tcof.company_id and tcof.folder_id in ('||in_company_folder_ids||')'
                      )
                    )
            --05152013 adding in ability to filter by coverage
            ||decode(in_coverage_ids
            ,null, null
            ,0, null
            , ' and a.id = ca2.activity_id and ca2.company_id in (select distinct company_id from company_coverage where company_id is not null and twp_user_id in (' || in_coverage_ids ||'))'
            )
            ||decode(in_tickers, null, null, ' and a.id = at.activity_id and at.ticker in ('||in_tickers||')')
            -- 15NOV2003: ||decode(in_user_name, null, null, ' and tu.id = a.owner_twp_user_id and tu.login in ('||l_user_name||')')
            -- replace the above line in the following decode statement
            ||decode(l_activity_owner
                    ,0, null
                    ,1, ' and a.owner_twp_user_id = '||in_user_id
                    ,2, ' and a.owner_twp_user_id in ('||l_user_name||')'
                    )
            ||decode(in_description, null, null, ' and upper(a.comments) like '||'''%'||upper(in_description)||'%''')
            ||decode(in_subject, null, null, ' and upper(a.subject) like '||'''%'||upper(in_subject)||'%''')
            ||decode(in_activity_type_ids, null, null, ' and a.activity_type_plist_id in ('||in_activity_type_ids||')')
            ||decode(in_activity_subtype_ids, null, null, ' and a.activity_sub_type_plist_id in ('||in_activity_subtype_ids||')')
            ||decode(in_department_ids, null, null, ' and (a.department_id in ('||in_department_ids||') and a.department_id is not null)')
            ||decode(in_start_date, null, null,
                decode(in_end_date, null, ' and trunc(activity_start_date) =''' || in_start_date || '''',
                ' and trunc(a.activity_start_date) >= ''' || in_start_date || ''' and trunc(a.activity_start_date) <= ''' || in_end_date || ''''))
            ||decode(in_followup_start_date
                ,null, null
                ,decode(in_followup_end_date
                    ,null, ' and trunc(a.follow_up_date) = ''' || in_followup_start_date || ''''
                    ,' and trunc(a.follow_up_date) >= ''' || in_followup_start_date || ''' and trunc(a.follow_up_date) <= ''' || in_followup_end_date || ''''))
            ||decode(in_followup_user_ids
                ,null, null
                ,' and a.id = af.activity_id and af.twp_user_id in (' || in_followup_user_ids || ')')
            ||decode(in_company_type_ids,
                null, null,
                0, null,
                ' and ctcaif.activity_id = a.id and ctcaif.company_id = ctif.company_id and ctif.company_type_plist_id in (' || in_company_type_ids || ')')
        into l_sql_statement
        from dual;
    end if;

    if in_attendee_id is not null then
        select 'select a.id,0 company_id,0 contact_id,a.activity_start_date,a.last_update from activity a,twp_user_activity_if tuaf'
            ||decode(in_tickers, null, null, ',activity_ticker at')
            ||decode(in_contact_folder_ids,null,null,0,null,',contact_activity_if ca' )
            ||decode(in_followup_user_ids
                 ,null, null
                 ,0, null
                 ,', activity_followup af')
            -- ||decode(in_folder_id, null, null, 0, null, ',twp_user_activity_folder fc')
            ||' where 0=0 and tuaf.activity_id=a.id and tuaf.twp_user_id in ('||in_attendee_id||')'
            -- ||decode(in_folder_id, null, null, 0, null,' and a.id=fc.activity_id and fc.folder_id in ('||in_folder_id||')')
            ||decode(in_contact_folder_ids,null,null,0,null,' and a.id=ca.activity_id and ca.contact_id in ('||in_contact_folder_ids||')')
            ||decode(in_tickers, null, null, ' and a.id = at.activity_id and at.ticker in ('||in_tickers||')')
            ||decode(in_description, null, null, ' and upper(a.comments) like '||'''%'||upper(in_description)||'%''')
            ||decode(in_subject, null, null, ' and upper(a.subject) like '||'''%'||upper(in_subject)||'%''')
            ||decode(in_activity_type_ids, null, null, ' and a.activity_type_plist_id in ('||in_activity_type_ids||')')
            ||decode(in_activity_subtype_ids, null, null, ' and a.activity_sub_type_plist_id in ('||in_activity_subtype_ids||')')
            ||decode(in_department_ids, null, null, ' and (a.department_id in ('||in_department_ids||') and a.department_id is not null)')
            ||decode(in_start_date, null, null,
                decode(in_end_date, null, ' and trunc(activity_start_date) =''' || in_start_date || '''',
                ' and trunc(a.activity_start_date) >= ''' || in_start_date || ''' and trunc(a.activity_start_date) <= ''' || in_end_date || ''''))
            ||decode(in_followup_start_date
                ,null, null
                ,decode(in_followup_end_date
                    ,null, ' and trunc(a.follow_up_date) = ''' || in_followup_start_date || ''''
                    ,' and trunc(a.follow_up_date) >= ''' || in_followup_start_date || ''' and trunc(a.follow_up_date) <= ''' || in_followup_end_date || ''''))
            ||decode(in_followup_user_ids
                ,null, null
                ,' and a.id = af.activity_id and af.twp_user_id in (' || in_followup_user_ids || ')')
        into l_sql_statement_2
        from dual;
    end if;




    if l_sql_statement_2 is not null then
        l_sql_statement := l_sql_statement ||' union '||l_sql_statement_2;
    end if;

    --    l_sql_statement := l_sql_statement ||' order by '||nvl(in_sort_by, g_default_sort_by);
    l_sql_statement := l_sql_statement ||' order by 4 desc nulls last, 5 desc';

    ins_debug_stmt('AC','Activity Search, SQL: '||l_sql_statement);
    l_workaround_sql := l_sql_statement;
    open tmp_cursor for l_workaround_sql;

    ins_debug_stmt('AC','Begin Build Statement');
     build_statement (in_user_id
            ,out_search_id
            ,out_retcode
            ,tmp_cursor);

    ins_debug_stmt('AC','After Build Statement');

    -- If the user wants to get the email address for all the contacts that were part of activities that matched the search criteria
    -- just return a cursor of the email addresses
    if in_contact_email_flag = 'Y' then
        l_sql_statement := 'select distinct c.email_addr from contact c, contact_activity_if ca, activity_search_result asr'
                ||' where c.id = ca.contact_id and ca.activity_id = asr.activity_id and asr.search_id='||out_search_id||' and c.email_addr is not null';
        ins_debug_stmt('AC','Final run SQL: '||l_sql_statement);
        open tmp_rs for l_sql_statement;
    else
        run_query (
            out_search_id
            ,out_retcode
            ,1
            ,in_record_per_set
            ,tmp_rs);
    end if;

    ins_debug_stmt('AC','Finished run query');

exception
    when OTHERS then
        out_retcode := -1;

end;

--

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I'm not sure what tool you are using in the image so I'm not sure what I'm looking at.

My guess is you are asking where the headers like PARENT_ACTIVITY_ID are coming from.

Just a guess because I don't have the tool you are looking at to test with but:
The OUT parameter is a cursor and defined as "IN OUT".    My guess is it is getting the values from the INPUT cursor that is being passed in.
Avatar of Frank Danny

ASKER

@slightwv  Thank  you!  I am  using  oracle sql developer and yes you are  right.. I am asking where the headers are coming from?
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>I am asking where the headers are coming from?

OK, are you assigning a value to tmp_rs  before clicking on whatever you are clicking on?
@slightwv  I am not  assigning any  value  to  tmp_rs
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you @slightwv...........I found  headers in  run_query
Thank  you