• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

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

0
iamtechnical
Asked:
iamtechnical
  • 10
  • 9
  • 2
2 Solutions
 
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
 
slightwv (䄆 Netminder) Commented:
This being in an Oracle PL/SQL zone, I assume Oracle.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now