procedure

hi
i have proc which calculate a revenue amount

so now i want that revenue amount in a select query
how can i attain that
passing parameters are project, revenue type, and expenditure date
with this parameters proc will calculate the revenue amount, and i need that amount in the query

iamtechnicalAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
I'm guessing a little about how you generate the 'tasks' but see if the code below helps.

Assuming a couple of typos in your expected results, the code generates what I think you actually meant.


drop table tab1 purge;
create table tab1 (project varchar2(10), task varchar2(10), revenue number);

insert into tab1 values('D15877','0',1234);
insert into tab1 values('D15877','00-EXP',231);
insert into tab1 values('D15877','0',567);
insert into tab1 values('D15877','1',9867);
insert into tab1 values('D15267','0',1876);
insert into tab1 values('D15267','1',6789);
insert into tab1 values('D15267','01-EXP',9807);
commit;

create or replace function myFunc(in_project varchar2, in_task varchar2) return number
is
result	number;
begin
	select sum(revenue) into result
		from tab1
		where project = in_project and
			to_number(regexp_substr(task,'^[0-9]+')) = to_number(in_task);
	return result;
end;
/

show errors


select project, task, myFunc(project,task) total_revenue from
(
	select distinct project, to_number(regexp_substr(task,'^[0-9]+')) task
	from tab1
)
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Either convert the procedure to a function or create a function that calls the proc:


create or replace function myFunc(project, revenue_type, expenditure_date) return number is
myAmount number;
begin
   myproc(project,revenue_type, expenditure_date,myAmount);
   return myAmount;
end;
/

then: select myFunc('a','a',sysdate) from dual;

0
 
Rajkumar GsSoftware EngineerCommented:
If the revenue amount that is calculated in the stored procedure is stored in a variable, you can do like
SELECT @RevenueAmount AS RevenueAmount

Open in new window

in the stored procedure.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
This being in an Oracle PL/SQL zone, I assume Oracle.
0
 
iamtechnicalAuthor Commented:
@RajkumarGS

can you please elaborate it






















 
0
 
slightwv (䄆 Netminder) Commented:
>>can you please elaborate it

Are you using Oracle or SQL Server?  That example is SQL Server.
0
 
Rajkumar GsSoftware EngineerCommented:
I showed an example of T-SQL (SQL Server)
I am not sure about PL-SQL.
I think you may use similar logic in PL-SQL (Oracle), if you are in PL-SQL

If there is some variable in your stored procedure, that is storing the calculated value (that you need to return from stored procedure as select statement)
you can frame a select statement like
SELECT @RevenueAmount AS RevenueAmount
WHERE @RevenueAmount - could be varaiable and 'AS RevenueAmount' is the alias (column name) of calculated value in select query.
0
 
iamtechnicalAuthor Commented:
yeah
slightwv:
i am using
oracle
0
 
slightwv (䄆 Netminder) Commented:
Then you need to do what I suggested in http:#a35698690.
0
 
iamtechnicalAuthor Commented:
YEAH @ slightwv

i got u

but .... for the given project i am trying to calculate the revenue at task level.....that to
suppose project 123 has 00,exp-00, 01,exp-01 i need 00 and exp-00 into one revenue line and 01 and exp-01 into another revenue line i mean two different revenue o/p,

so how can i pass it in procedure level
0
 
slightwv (䄆 Netminder) Commented:
I'm not following.

You originally mention you have a procedure that calculates revenue.  
What does the procedure currently do now?  I assume it has one OUT parameter called revenue?
0
 
iamtechnicalAuthor Commented:
yeah you are right

its has only one.....but the thing is it shd calculate at task level ,

ex
project 123
has task 00, exp-00,01,02,exp02,03....
so now i want 4 output lines
say for task 00 and exp-00 one line
and for 01 one line
and for 02, exp-02 one line

but the thg is function can return only one value

0
 
slightwv (䄆 Netminder) Commented:
I'm confused.

Please post the procedure declaration you currently have.

So the question is really how to change the procedure to return more than one value?
or
How to use a procedure you already have in a SELECT statement?
or
Some combination of the two?

0
 
iamtechnicalAuthor Commented:
sorry for not being clear


/* Formatted on 2011/05/04 12:11 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FUNCTION nccr1 (project IN VARCHAR2, end_date IN DATE)
   RETURN NUMBER
IS
   v_lrec       NUMBER;
   --Labor  & Consultant Revenue & Reimbursable Expense Revenue
   v_cer        NUMBER;                           -- Conversion Event Revenue
   v_rew        NUMBER;                                --Revenue w exclusions
   v_rec        NUMBER;                         -- Reimbursable Expenses Cost
   v_celc       NUMBER;                     --Consultant Expense (Labor) Cost
--v_ec       number ;--Expenses Cost
   v_gr         NUMBER;                                --Type 2 Gross Revenue
   v_wor        NUMBER;                           --Total WriteOffs, Reserves
--   v_lbcb      NUMBER;               --Labor Billings incl consultant billing
--   v_ceb       NUMBER;                             --Conversion Event Billing
--   v_wop       NUMBER;                                     --WriteOff Percent
--   v_pwor      NUMBER;      -- Pro-rated Write-offs, reserves and adjustments
   v_project    VARCHAR2 (15);
   v_nccr       NUMBER;
   v_end_date   DATE;
--   v_lr        NUMBER;
BEGIN
   v_project := project;
   v_nccr := NULL;
   v_end_date := end_date;

   BEGIN
      --DBMS_OUTPUT.put_line('person_id is: '||v_person_id);
      SELECT   NVL (SUM (amount), 0)
          INTO v_lrec
          FROM (SELECT DISTINCT pei.project_id, pdr.amount
                           FROM pa_draft_revenue_items pdr,
                                xxcra_pa_exp_items_v pei,
                                pa_projects_all ppa,
                                pa_cust_rev_dist_lines_all pcd,
                                pa_draft_revenues_all pdra,
                                hr_all_organization_units hou
                          WHERE pei.project_id = pdr.project_id
                            AND pei.project_number = v_project
                            AND pei.expenditure_item_id =
                                                       pcd.expenditure_item_id
                            AND pcd.draft_revenue_item_line_num = pdr.line_num
                            AND pdr.revenue_category_code IN
                                   ('LABOR REVENUE',
                                    'CONSULTANT REVENUE',
                                    'REIMBURSABLE EXPENSES'
                                   )
                            AND pdra.project_id = pei.project_id
                            AND pdra.draft_revenue_num = pdr.draft_revenue_num
                            AND hou.organization_id =
                                              ppa.carrying_out_organization_id
                            AND pei.expenditure_item_date BETWEEN ppa.start_date
                                                              AND v_end_date
                            AND ppa.project_id = pei.project_id
                            AND pdra.transfer_status_code = 'A'
                            AND pdr.revenue_source NOT IN ('Event')
                            AND pei.task_id = pdr.task_id) "t1"
         WHERE 1 = 1
      GROUP BY project_id;

      DBMS_OUTPUT.put_line ('v_lrec is  ' || v_lrec);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_lrec := 0;
   END;

   BEGIN
      --DBMS_OUTPUT.put_line('person_id is: '||v_person_id);
      SELECT   NVL (SUM (amount), 0)
          INTO v_cer
          FROM (SELECT DISTINCT ppa.project_id, pdr.amount
                           FROM pa_projects_all ppa,
                                pa_events pe,
                                pa_draft_revenue_items pdr,
                                pa_draft_revenues_all pdra,
                                pa_cust_event_rdl_all pce,
                                hr_all_organization_units hou
                          WHERE segment1 = v_project
                            AND pe.event_type IN ('Converted Revenue')
                            AND pe.project_id = ppa.project_id
                            AND pdr.project_id = ppa.project_id
                            AND pce.task_id = pe.task_id
                            AND pce.event_num = pe.event_num
                            AND pe.task_id = pe.task_id
                            AND hou.organization_id =
                                              ppa.carrying_out_organization_id
                            AND pdr.revenue_category_code IN
                                   ('LABOR REVENUE', 'REIMBURSABLE EXPENSES')
                            AND pdra.transfer_status_code = 'A'
                            AND pdr.project_id = pdra.project_id
                            AND pdr.draft_revenue_num = pce.draft_revenue_num
                            AND pdr.line_num = pce.draft_revenue_item_line_num) "t1"
         WHERE 1 = 1
      GROUP BY project_id;

      DBMS_OUTPUT.put_line ('v_cer is  ' || v_cer);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_cer := 0;
   END;

   BEGIN
      v_rew := v_lrec + v_cer;
      DBMS_OUTPUT.put_line ('v_rew is  ' || v_rew);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Total Value error  ' || SQLERRM);
   END;

   BEGIN
      --DBMS_OUTPUT.put_line('person_id is: '||v_person_id);
      SELECT SUM (amount)
        INTO v_rec
        FROM (SELECT pcd.amount
                FROM pa_cost_distribution_lines_all pcd,
                     xxcra_pa_exp_items_v pei,
                     pa_projects_all ppa,
                     hr_all_organization_units hou
               WHERE pei.project_number = v_project
                 AND pei.project_id = pcd.project_id
                 AND pei.project_id = ppa.project_id
                 AND hou.organization_id = ppa.carrying_out_organization_id
--AND pEI.task_id = pCD.task_id
                 AND pei.revenue_category_code IN ('REIMBURSABLE EXPENSES')
                 AND pcd.transfer_status_code IN ('A', 'V')
                 AND pei.expenditure_item_date BETWEEN ppa.start_date
                                                   AND v_end_date
                 AND pei.expenditure_item_id = pcd.expenditure_item_id) "t1";
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_rec := 0;
   END;

   DBMS_OUTPUT.put_line ('v_rec is  ' || v_rec);

   BEGIN
      --DBMS_OUTPUT.put_line('person_id is: '||v_person_id);
      SELECT   NVL (SUM (amount), 0)
          INTO v_celc
          FROM (SELECT pei.project_id, pcd.amount
                  FROM pa_cost_distribution_lines_all pcd,
                       xxcra_pa_exp_items_v pei,
                       hr_all_organization_units hou,
                       pa_projects_all ppa
                 WHERE pei.project_number = v_project
                   AND pcd.transfer_status_code IN ('A', 'V')
                   AND pei.expenditure_type IN
                                           ('Consultants - Labor on invoice')
                   AND pei.project_id = pcd.project_id
                   AND pei.expenditure_item_id = pcd.expenditure_item_id
                   AND ppa.project_id = pei.project_id
                   AND pei.expenditure_item_date BETWEEN ppa.start_date
                                                     AND v_end_date
                   AND hou.organization_id = ppa.carrying_out_organization_id) t2
         WHERE 1 = 1
      GROUP BY project_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_celc := 0;
   END;

   DBMS_OUTPUT.put_line ('v_celc is  ' || v_celc);

   BEGIN
      v_gr := v_rew - v_rec - v_celc;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Total Value error  ' || SQLERRM);
   END;

   BEGIN
      SELECT   SUM (amount)
          INTO v_wor
          FROM (SELECT ppa.project_id, pdr.amount
                  FROM pa_projects_all ppa,
                       pa_events pe,
                       pa_draft_revenue_items pdr,
                       pa_cust_event_rdl_all pce,
                       hr_all_organization_units hou
                 WHERE segment1 = v_project
                   AND pe.project_id = ppa.project_id
                   AND pdr.project_id = ppa.project_id
                   AND pce.task_id = pe.task_id
                   AND pce.event_num = pe.event_num
                   AND pe.task_id = pe.task_id
                   AND pdr.revenue_category_code IN
                                                   ('WRITE-OFFS', 'RESERVES')
                   AND hou.organization_id = ppa.carrying_out_organization_id
                   AND pdr.draft_revenue_num = pce.draft_revenue_num
                   AND pdr.line_num = pce.draft_revenue_item_line_num) "t1"
         WHERE 1 = 1
      GROUP BY project_id;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         v_wor := 0;
   END;

   DBMS_OUTPUT.put_line ('v_wor is  ' || v_wor);

   BEGIN
      v_nccr := v_gr + v_wor;
      RETURN v_nccr;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Total Value error  ' || SQLERRM);
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Total Value error  ' || SQLERRM);
END nccr1;
/


above is the function which calculates revenue....it works on project level....but now i want it on task level as mentioned earlier.....
if i pass task as parameter it will consider only that task
but i need 00 and 00-exp into one
and 01, 00-exp into another and so on

that why i tried creating a procedure ....but for that also i am on square one
0
 
slightwv (䄆 Netminder) Commented:
Can you not change the function to accept the task as well as the project?

You also realize that dbms_output.put_line is just to display stuff to the screen.  It's technically not part of the process.

If you can provide a simplified test table with some sample data and expected results, I'll try to provide working code for you.


By simplified I'm thinking something like:
create table tab1 (project varchar2(10), task varchar2(10), revenue number);

You can add additional columns you feel are important.  Just add some sample data and expected results.
0
 
iamtechnicalAuthor Commented:
D15877      0      1234
D15877      00-EXP      231
D15877      0      567
D15877      1      9867
D15267      0      1876
D15267      1      6789
D15267      01-EXP      9807

@SLIGHTWV YEAH I TRIED THAT BY PASSING TASK...BUT I SHD TAKE CARE OF 00, 00-EXP in one single line

above is the test table

i am expecting o/p as


D15877      0      2032 --- 00,00-EXP COMBINED
D15877      1      9867
D15877      0      1876
D15877      1      16596
0
 
iamtechnicalAuthor Commented:
hey
@slightwv

nice answer man

got boosted up now

one more lasttttt request can you explain me to_number(regexp_substr(task,'^[0-9]+'))
0
 
iamtechnicalAuthor Commented:
thank you
0
 
slightwv (䄆 Netminder) Commented:
>>explain me to_number(regexp_substr(task,'^[0-9]+'))

The to_number just takes the string from the regexp_substr and makes it an actual number.

the regexp_substr basically says:

starting at the beginning of the string '^', return me only numbers '[0-9]' when you have one or more '+'.  The more important part is what isn't there:  It stops at the first non-number in the string.
0
 
iamtechnicalAuthor Commented:
hey how it will work if the task is exp-00 format
0
 
slightwv (䄆 Netminder) Commented:
I can see where this is going...  :)

regular expressions deal only in 'patterns'.  If you can define a pattern or patterns, then you can likely get a regular expression to massage/extract/manipulate it.

The common problem is you think there is a pattern then you find out developers or applications don't follow the 'pattern'.

What are all possible formats?  can it be exp-00-exp?

Will it be OK if you just extract all numbers?  can the task be 1exp-00?

What about numbers before or after the '-' at the beginning or end of the string?

As you can see, you need to make sure what you have before you can write the pattern matcher.

>>will work if the task is exp-00 format

Sort of the opposite but the 'end' of the string is a '$'.  So you want all numbers at the end of the string:

select regexp_substr('exp-00','[0-9]+$') from dual
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.