APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Procedure or Function ? and How to Use with OUT Parameters

Dj SteeleMay 3 2019 — edited May 3 2019

Hello Community,

I am using PL/SQL Developer 12.1.0.2.0 Oracle APEX 5.1.0.00.45 and Google Chrome Browser

This Question is in Regards to pulling Data for a Classic Report and Because its pulling Numeric Data ....

Is it best to put it in a "Procedure" or "Function" in Database ?

Leave it in a Process --After Submit on Page of APEX ?

If a "Procedure" then I'm having problems Relating the OUT Parameters to Variables in the "Procedure" ( Currently getting 0.00 Amounts for Fields in Report )

Below is the Table and Sample Data The Classic Report is based on

Below is the "Procedure" I have not turned into a Function yet

Is this how to Assign the OUT Parameter to Declared Variable ?

Part_Const_Compl := v_Part_Const_Compl

Thisis How I am Calling "Procedure" Currently in APEX in the "After Submit - Process"

begin

COST_DATA_DISB_COMPL_REPORT(:P375_JOB_PIECE,:P375_PART_CONST_COMPL);

end;

Currenlty getting 0.00 for Fields

Should Be Getting This

create or replace procedure "COST_DATA_DISB_COMPL_REPORT"

(Job_Piece IN Varchar2,

Part_Const_Compl OUT Varchar2

)

v_Part_Const_Compl Varchar2(15);

v_NonPART_CONST_COMPL Varchar2(15);

v_PART_CONST_DISB Varchar2(15);

v_NONPART_CONST_DISB Varchar2(15);

v_PART_ENG_COMPL Varchar2(15);

v_NONPART_ENG_COMPL Varchar2(15);

v_PART_ENG_DISB Varchar2(15);

v_NonPART_ENG_DISB Varchar2(15);

v_FMIS_CONST Varchar2(15);

v_FMIS_ENG Varchar2(15);

Begin

With get_debit_credit_ind AS

(

select c_acct_type, c_tran_type,

  Case When f\_debit\_credit\_ind IN ('A', 'D')

       Then f\_debit\_credit\_ind

       Else 'X'

  End as m\_debit\_credit\_ind

  ,

  a\_posting

from fdep.detail_postings@fdep_bidb dtlpost

Where c_job_piece = Job_Piece

-- and c_acct_type IN ('1','2','3','4','5')

and dtlpost.c_object like '47%'

and dtlpost.c_ref_type = 'CL'

and dtlpost.c_part_code = 1

and dtlpost.c_acct_type = 5

)

, get_m_posting AS

(

select d.*

    ,

    CASE

     WHEN   (c\_acct\_type, m\_debit\_credit\_ind)

           IN ( ('1','X')

            ,   ('2','A')

            ,   ('2','D')

            ,   ('3','D')

            ,   ('4','D')

            ,   ('5','A')

            ,   ('5','D')

              )

     THEN  -a\_posting

     ELSE   a\_posting

 END  AS m\_posting

FROM get_debit_credit_ind d

)

--,

-- Aggregate_Results As

Select

 to\_char(nvl(SUM ( CASE

          WHEN  c\_tran\_type  \<> 'D'

              THEN  0

          WHEN  c\_acct\_type = 5

              THEN  -m\_posting

          ELSE  +m\_posting

      END

    ),0),'999,999,990.99')  as "Part Const Compl"

into v_Part_Const_Compl

FROM get_m_posting

;

Part_Const_Compl := v_Part_Const_Compl

;

With get_debit_credit_ind AS

(

select c_acct_type, c_tran_type,

  Case When f\_debit\_credit\_ind IN ('A', 'D')

       Then f\_debit\_credit\_ind

       Else 'X'

  End as m\_debit\_credit\_ind

  ,

  a\_posting

from fdep.detail_postings@fdep_bidb dtlpost

Where c_job_piece = Job_Piece

-- and c_acct_type IN ('1','2','3','4','5')

and dtlpost.c_object like '47%'

and dtlpost.c_ref_type = 'CL'

and dtlpost.c_part_code = 2

and dtlpost.c_acct_type = 5

)

, get_m_posting AS

(

select d.*

    ,

    CASE

     WHEN   (c\_acct\_type, m\_debit\_credit\_ind)

           IN ( ('1','X')

            ,   ('2','A')

            ,   ('2','D')

            ,   ('3','D')

            ,   ('4','D')

            ,   ('5','A')

            ,   ('5','D')

              )

     THEN  -a\_posting

     ELSE   a\_posting

 END  AS m\_posting

FROM get_debit_credit_ind d

)

--,

-- Aggregate_Results As

Select

 to\_char(nvl(SUM ( CASE

          WHEN  c\_tran\_type  \<> 'D'

              THEN  0

          WHEN  c\_acct\_type = 5

              THEN  -m\_posting

          ELSE  +m\_posting

      END

    ),0),'999,999,990.99')  as "NonPart Const Compl"

into v_NonPART_CONST_COMPL

FROM get_m_posting

;

select to_char(nvl(sum(a_disb_amt),0),'999,999,990.99') as "Part Const Disb"

into v_PART_CONST_DISB

from fdep.pfs_detail_records@fdep_bidb

where c_jpnum = Job_Piece

and c_objcd like '4%' -- Const

and c_Partcode In (1,4) -- participating as "Part Const Disb"

and (EXISTS (select null from jbp.fmis5_head_data fmis5 where fmis5.c_jpno = Job_Piece)

      and c\_fedcode is not null)

    OR

    (NOT EXISTS (select null from jbp.fmis5\_head\_data fmis5 where fmis5.c\_jpno = Job\_Piece)

      and c\_fedcode is null);

select to_char(nvl(sum(a_disb_amt),0),'999,999,990.99') as "Non Part Const Disb"

into v_NONPART_CONST_DISB

from fdep.pfs_detail_records@fdep_bidb

where c_jpnum = Job_Piece

and c_objcd like '4%' -- Const

and c_Partcode Not In (1,4) -- non participating

and (EXISTS (select null from jbp.fmis5_head_data fmis5 where fmis5.c_jpno = Job_Piece)

      and c\_fedcode is not null)

    OR

    (NOT EXISTS (select null from jbp.fmis5\_head\_data fmis5 where fmis5.c\_jpno = Job\_Piece)

      and c\_fedcode is null)

;

With get_debit_credit_ind AS

(

select c_acct_type, c_tran_type,

  Case When f\_debit\_credit\_ind IN ('A', 'D')

       Then f\_debit\_credit\_ind

       Else 'X'

  End as m\_debit\_credit\_ind

  ,

  a\_posting

from odot2.detail_postings_vw dtlpost

where dtlpost.c_job_piece = Job_Piece

and dtlpost.c_object like '15%'

and dtlpost.c_ref_type = 'CL'

and dtlpost.c_part_code = 2

and dtlpost.c_acct_type = 5

)

, get_m_posting AS

(

select d.*

    ,

    CASE

     WHEN   (c\_acct\_type, m\_debit\_credit\_ind)

           IN ( ('1','X')

            ,   ('2','A')

            ,   ('2','D')

            ,   ('3','D')

            ,   ('4','D')

            ,   ('5','A')

            ,   ('5','D')

              )

     THEN  -a\_posting

     ELSE   a\_posting

 END  AS m\_posting

FROM get_debit_credit_ind d

)

--,

-- Aggregate_Results As

Select

 to\_char(nvl(SUM ( CASE

          WHEN  c\_tran\_type  \<> 'D'

              THEN  0

          WHEN  c\_acct\_type = 5

              THEN  -m\_posting

          ELSE  +m\_posting

      END

    ),0),'999,999,990.99')  as "Part Eng Compl"

into v_PART_ENG_COMPL

FROM get_m_posting

;

With get_debit_credit_ind AS

(

select c_acct_type, c_tran_type,

  Case When f\_debit\_credit\_ind IN ('A', 'D')

       Then f\_debit\_credit\_ind

       Else 'X'

  End as m\_debit\_credit\_ind

  ,

  a\_posting

from odot2.detail_postings_vw dtlpost

where dtlpost.c_job_piece = Job_Piece

and dtlpost.c_object like '15%'

and dtlpost.c_ref_type = 'CL'

and dtlpost.c_part_code = 1

and dtlpost.c_acct_type = 5

)

, get_m_posting AS

(

select d.*

    ,

    CASE

     WHEN   (c\_acct\_type, m\_debit\_credit\_ind)

           IN ( ('1','X')

            ,   ('2','A')

            ,   ('2','D')

            ,   ('3','D')

            ,   ('4','D')

            ,   ('5','A')

            ,   ('5','D')

              )

     THEN  -a\_posting

     ELSE   a\_posting

 END  AS m\_posting

FROM get_debit_credit_ind d

)

--,

-- Aggregate_Results As

Select

 to\_char(nvl(SUM ( CASE

          WHEN  c\_tran\_type  \<> 'D'

              THEN  0

          WHEN  c\_acct\_type = 5

              THEN  -m\_posting

          ELSE  +m\_posting

      END

    ),0),'999,999,990.99') as "NonPart Eng Compl"

Into v_NONPART_ENG_COMPL

FROM get_m_posting

;

select to_char(nvl(sum(a_disb_amt),0),'999,999,990.99') as "Part Eng Disb"

into v_PART_ENG_DISB

from fdep.pfs_detail_records@fdep_bidb

where c_jpnum = Job_Piece

and c_objcd Not like '4%' -- Eng

and c_Partcode In (1,4) -- participating

and (EXISTS (select null from jbp.fmis5_head_data fmis5 where fmis5.c_jpno = Job_Piece)

      and c\_fedcode is not null)

    OR

    (NOT EXISTS (select null from jbp.fmis5\_head\_data fmis5 where fmis5.c\_jpno = Job\_Piece)

      and c\_fedcode is null)

;

select to_char(nvl(sum(a_disb_amt),0),'999,999,990.99') as "NonPart Eng Disb"

into v_NONPART_ENG_DISB

from fdep.pfs_detail_records@fdep_bidb

where c_jpnum = Job_Piece

and c_objcd Not like '4%' -- Eng

and c_Partcode Not In (1,4) -- non participating

and (EXISTS (select null from jbp.fmis5_head_data fmis5 where fmis5.c_jpno = Job_Piece)

      and c\_fedcode is not null)

    OR

    (NOT EXISTS (select null from jbp.fmis5\_head\_data fmis5 where fmis5.c\_jpno = Job\_Piece)

      and c\_fedcode is null)

;

select to_char(nvl(sum(a_fed_amt),0),'999,999,990.99') as "Fed Participation Construction"

into v_FMIS_CONST

from jbp.fmis5_detail_data dd

where c_jpno = Job_Piece

 and c\_imprv\_type != 17

 and c\_mod\_no = 

     (select max(c\_mod\_no) 

      from  jbp.fmis5\_detail\_data dd1 

      where dd.c\_jpno = dd1.c\_jpno and dd1.c\_mod\_no \< 999)

;

--FMIS5 Detail Data Engineering Improvement Type = 17

select to_char(nvl(sum(a_fed_amt),0),'999,999,990.99') as "Fed Participation Eng"

into v_FMIS_ENG

from jbp.fmis5_detail_data dd

where c_jpno = Job_Piece

 and c\_imprv\_type = 17

 and c\_mod\_no = 

     (select max(c\_mod\_no) 

      from  jbp.fmis5\_detail\_data dd1 

      where dd.c\_jpno = dd1.c\_jpno and dd1.c\_mod\_no \< 999)

;

dbms_output.put_line(v_Part_Const_Compl);

dbms_output.put_line(v_NonPART_CONST_COMPL) ;

dbms_output.put_line(v_PART_CONST_DISB) ;

dbms_output.put_line(v_NONPART_CONST_DISB) ;

dbms_output.put_line(v_PART_ENG_COMPL) ;

dbms_output.put_line(v_NONPART_ENG_COMPL) ;

dbms_output.put_line(v_PART_ENG_DISB) ;

dbms_output.put_line(v_NonPART_ENG_DISB) ;

dbms_output.put_line(v_FMIS_CONST) ;

dbms_output.put_line(v_FMIS_ENG) ;

End COST_DATA_DISB_COMPL_REPORT;

CREATE TABLE DETAIL_POSTINGS

(

C_FUND VARCHAR2(4) NOT NULL ENABLE,

C_ACCOUNT VARCHAR2(6) NOT NULL ENABLE,

D_POSTING DATE,

C_ACCT_TYPE VARCHAR2(1) NOT NULL ENABLE,

C_TRAN_TYPE VARCHAR2(1) NOT NULL ENABLE,

C_REF_TYPE VARCHAR2(2) NOT NULL ENABLE,

C_REF_NO VARCHAR2(10) NOT NULL ENABLE,

A_POSTING NUMBER(11,2) NOT NULL ENABLE,

F_DEBIT_CREDIT_IND VARCHAR2(1) NOT NULL ENABLE,

C_PART_CODE VARCHAR2(1),

C_JOB_PIECE VARCHAR2(7),

T_OSF_FY VARCHAR2(4),

C_OBJECT VARCHAR2(6)

);