This discussion is archived
10 Replies Latest reply: Jul 30, 2013 9:04 AM by User474617-OC RSS

Help on building up a Hierarchical Query

User474617-OC Newbie
Currently Being Moderated

Hello

 

 

I have a requirement to build the approval group based on invoice amount on the invoice line in payable. For Example if the invoice line amount is 10000k then I want to send the invoice for approval till level 3 approval authority from the requester on the invoice line.. I have created a query as below and pas.person_id I am passing 62 (this is the person id of the requester on the invoice_line and approval starts from requester). By using this query i am getting the full approval hierarchical query from requester to CEO but i don't know how to restrict the records to fetch till approval hierarchy 3, in addition in case if I don't have any person for approval authority 3 then i should pick next immediate higher authority person (in this case 4)....Note: current requester don't have any approval authority and in case if the requester itself has approval authority 3 then  query should only return requester record.

 

Can anybody help me on this?

 

 

 

select

z.person_id,z.full_name,z.job_id, z.level1, pj.name,pj.approval_authority

from  PER_JOBS_VL pj,

(select distinct pp.person_id,pp.full_name, pas.job_id,LEVEL level1 from per_all_people_f pp,

  per_all_assignments_f pas

where pp.person_id= pas.person_id

start with pas.person_id = 62

connect by prior  pas.supervisor_id = pp.person_id

and trunc(sysdate) between trunc(pas.effective_start_date) and trunc(pas.effective_end_date)

and trunc(sysdate) between trunc(pas.effective_start_date) and trunc(pas.effective_end_date)

order by LEVEL )z

where pj.job_id = z.job_id

order by 4

 

 

 

 

PERSON_ID,FULL_NAME,JOB_ID,LEVEL1,JOB_NAME,APPROVAL_AUTHORITY

62,      aaa,       64,     1,     Buyer,

63,      bbb,       66,      2,    Director,  4

121,    ccc,       66,      3,    Director,  4

68,      ddd,      1296,   4,    CFO,       7

71,      eee,      1259,   5,    CEO,       7

 

Thanks!

  • 1. Re: Help on building up a Hierarchical Query
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    It depends on your data, and the results you want from that data.

    It sounds like you need to add another condition to the CONNECT BY clause:

     

    select    z.person_id,z.full_name,z.job_id, z.level1, pj.name,pj.approval_authority

    from      PER_JOBS_VL pj

    ,         (

                   select distinct

                           pp.person_id,pp.full_name, pas.job_id,LEVEL level1

                   from    per_all_people_f       pp

                   ,       per_all_assignments_f  pas

                   where   pp.person_id   = pas.person_id

                   start with   pas.person_id  = 62

                   connect by   prior  pas.supervisor_id = pp.person_id

                           and  trunc (sysdate) between trunc (pas.effective_start_date)

                                                and     trunc (pas.effective_end_date)

      --  Why is the condition above repeated below?

                          and  trunc (sysdate) between trunc (pas.effective_start_date)

                                               and     trunc (pas.effective_end_date)

                          and  approval_hierarchy  <= 3      -- *****  NEW  *****

         --        order by LEVEL  -- ORDER BY here would be lost in the main query

              ) z

    where     pj.job_id  = z.job_id

    order by  4

    ;

     

    Since I don't have your tables, and don't know what results you want, all I can do is guess.

     

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

    Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.

    Simplify the problem as much as possible.  Remove all tables and columns that play no role in this problem.

    Always say which version of Oracle you're using (for example, 11.2.0.2.0).

    See the forum FAQ: https://forums.oracle.com/message/9362002

  • 2. Re: Help on building up a Hierarchical Query
    User474617-OC Newbie
    Currently Being Moderated

    CREATE TABLE APPS.PER_ALL_PEOPLE_F_TEMP

    (

      PERSON_ID  NUMBER,

      FULL_NAME  VARCHAR2(100 BYTE)

    );

     

    SET DEFINE OFF;

    Insert into APPS.PER_ALL_PEOPLE_F_TEMP

       (PERSON_ID, FULL_NAME)

    Values

       (62, 'aaa');

    Insert into APPS.PER_ALL_PEOPLE_F_TEMP

       (PERSON_ID, FULL_NAME)

    Values

       (63, 'bbb');

    Insert into APPS.PER_ALL_PEOPLE_F_TEMP

       (PERSON_ID, FULL_NAME)

    Values

       (68, 'ccc');

    Insert into APPS.PER_ALL_PEOPLE_F_TEMP

       (PERSON_ID, FULL_NAME)

    Values

       (121, 'ddd');

    Insert into APPS.PER_ALL_PEOPLE_F_TEMP

       (PERSON_ID, FULL_NAME)

    Values

       (4710, 'eee');

    COMMIT;

     

     

    CREATE TABLE APPS.PER_ALL_ASSIGNMENTS_F_TEMP

    (

      ASSIGNMENT_ID  NUMBER,

      JOB_ID         NUMBER,

      PERSON_ID      NUMBER,

      SUPERVISOR_ID  NUMBER

    );

     

    SET DEFINE OFF;

    Insert into APPS.PER_ALL_ASSIGNMENTS_F_TEMP

       (ASSIGNMENT_ID, JOB_ID, PERSON_ID, SUPERVISOR_ID)

    Values

       (62, 64, 62, 63);

    Insert into APPS.PER_ALL_ASSIGNMENTS_F_TEMP

       (ASSIGNMENT_ID, JOB_ID, PERSON_ID, SUPERVISOR_ID)

    Values

       (63, 66, 63, 121);

    Insert into APPS.PER_ALL_ASSIGNMENTS_F_TEMP

       (ASSIGNMENT_ID, JOB_ID, PERSON_ID, SUPERVISOR_ID)

    Values

       (68, 1296, 68, 4710);

    Insert into APPS.PER_ALL_ASSIGNMENTS_F_TEMP

       (ASSIGNMENT_ID, JOB_ID, PERSON_ID, SUPERVISOR_ID)

    Values

       (121, 66, 121, 68);

    Insert into APPS.PER_ALL_ASSIGNMENTS_F_TEMP

       (ASSIGNMENT_ID, JOB_ID, PERSON_ID)

    Values

       (4815, 1259, 4710);

    COMMIT;

     

     

     

     

     

     

     

    CREATE TABLE APPS.PER_JOBS_VL_TEMP

    (

      JOB_ID              NUMBER,

      NAME                VARCHAR2(100 BYTE),

      APPROVAL_AUTHORITY  NUMBER

    )

     

     

     

    SET DEFINE OFF;

    Insert into APPS.PER_JOBS_VL_TEMP

       (JOB_ID, NAME)

    Values

       (64, 'Buyer');

    Insert into APPS.PER_JOBS_VL_TEMP

       (JOB_ID, NAME, APPROVAL_AUTHORITY)

    Values

       (66, 'Director', 4);

    Insert into APPS.PER_JOBS_VL_TEMP

       (JOB_ID, NAME, APPROVAL_AUTHORITY)

    Values

       (62, 'Manager', 3);

    Insert into APPS.PER_JOBS_VL_TEMP

       (JOB_ID, NAME, APPROVAL_AUTHORITY)

    Values

       (1296, 'CFO', 7);

    Insert into APPS.PER_JOBS_VL_TEMP

       (JOB_ID, NAME, APPROVAL_AUTHORITY)

    Values

       (1259, 'CEO', 7);

    COMMIT;

     

     

    Current Query

    ---------------

    SELECT z.person_id,

             z.full_name,

             z.job_id,

             z.level1,

             pj.name,

             pj.approval_authority

        FROM PER_JOBS_VL_TEMP pj,

             (    SELECT DISTINCT pp.person_id,

                                  pp.full_name,

                                  pas.job_id,

                                  LEVEL level1

                    FROM per_all_people_f_TEMP pp, per_all_assignments_f_TEMP pas

                   WHERE pp.person_id = pas.person_id

              START WITH pas.person_id = 62

              CONNECT BY PRIOR pas.supervisor_id = pp.person_id) z

       WHERE pj.job_id = z.job_id

    ORDER BY 4

     

     

    current output

    ---------------------

    PERSON_ID,    FULL_NAME,    JOB_ID,        LEVEL1,        NAME,        APPROVAL_AUTHORITY

    62,        aaa,        64,        1,        Buyer,

    63,        bbb,        66,        2,        Director,     4

    121,        ddd,        66,        3,        Director,    4

    68,        ccc,        1296,        4,        CFO,        7

    4710,        eee,        1259,        5,        CEO,        7

     

    Expected Output

     

    If i give approval authority 3 then i am expecting below records since level 3 approval authority person not there so get the next imeediate high approval authority person

    PERSON_ID,    FULL_NAME,    JOB_ID,        LEVEL1,        NAME,        APPROVAL_AUTHORITY

    62,        aaa,        64,        1,        Buyer,

    63,        bbb,        66,        2,        Director,     4

     

     

    If i give approval authority 5, since there is no 5 approval authority next immediate high approval authority person should be fetched i.e. 7

    PERSON_ID,    FULL_NAME,    JOB_ID,        LEVEL1,        NAME,        APPROVAL_AUTHORITY

    62,        aaa,        64,        1,        Buyer,

    63,        bbb,        66,        2,        Director,     4

    68,        ccc,        1296,        4,        CFO,        7

     

     

    If i give approval authority 7

    PERSON_ID,    FULL_NAME,    JOB_ID,        LEVEL1,        NAME,        APPROVAL_AUTHORITY

    62,        aaa,        64,        1,        Buyer,

    63,        bbb,        66,        2,        Director,     4

    68,        ccc,        1296,        4,        CFO,        7

  • 3. Re: Help on building up a Hierarchical Query
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    What do you mean when you say "If I give approval authority 5 ..."?

    Do you mean that you want to set a parameter at run time?  If so, here's one way to do that:

     

     

    VARIABLE  approval_authority_limit NUMBER

    EXEC     :approval_authority_limit := 3;

     

     

    SELECT    pp.person_id

    ,         pp.full_name

    ,         pas.job_id

    ,         LEVEL  AS level1

    ,         pj.name

    ,         pj.approval_authority

    FROM      per_jobs_vl_temp            pj

    JOIN      per_all_assignments_f_temp  pas  ON  pas.job_id    = pj.job_id

    JOIN      per_all_people_f_temp       pp   ON  pp.person_id  = pas.person_id

    START WITH   pas.person_id = 62

    CONNECT BY   PRIOR pas.supervisor_id  = pp.person_id

            AND  NVL ( PRIOR pj.approval_authority

                     , 0

                     )                    < :approval_authority_limit

    ORDER BY  level1

    ;

     

    You may want to join all 3 tables in a sub-query first, and then do the CONNECT BY in the main query.  Sometimes, performance is better that way.

  • 4. Re: Help on building up a Hierarchical Query
    User474617-OC Newbie
    Currently Being Moderated

    Thanks! Yes, approval authority will be derived dynamically. I am finding an issue when ever i give approval authority 7, i am getting two records fro approval authority 4 but I want one record for each approval authority.

  • 5. Re: Help on building up a Hierarchical Query
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Sorry, I missed the requirement about only 1 row in the result set for each approval_authority.

    That's an example of a Top-N Query, which can be done like this:

     

    WITH got_r_num   AS

    (

        SELECT    pp.person_id

        ,   pp.full_name

        ,         pas.job_id

        ,         LEVEL  AS level1

        ,         pj.name

        ,         pj.approval_authority

        ,   ROW_NUMBER () OVER ( PARTITION BY  pj.approval_authority

                                 ORDER BY      ROWNUM

                               )  AS r_num

        FROM   per_jobs_vl_temp            pj

        JOIN   per_all_assignments_f_temp  pas  ON  pas.job_id    = pj.job_id

        JOIN   per_all_people_f_temp       pp   ON  pp.person_id  = pas.person_id

        START WITH   pas.person_id = 62

        CONNECT BY   PRIOR pas.supervisor_id  = pp.person_id

                AND  NVL ( PRIOR pj.approval_authority

                         , 0

                         )                    < :approval_authority_limit

    )

    SELECT    person_id, full_name, job_id, level1, name, approval_authority

    FROM      got_r_num

    WHERE     r_num    = 1

    ORDER BY  level1

    ;

     

    Notice the the sub-query, got_r_num, is almost identical to what we had before.

    Depending on your version, you may need to do the CONNECT BY in a separate sub-query first, and then call ROW_NUMBER in another sub-query.  In some old versions, analytic functions (such as ROW_NUMBER) caused CONNECT BY to give wrong results when done in the same sub-query.

  • 6. Re: Help on building up a Hierarchical Query
    User474617-OC Newbie
    Currently Being Moderated

    Thanks! Can you help me how to join both queries and what will the final query? We are in "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production" database.

  • 7. Re: Help on building up a Hierarchical Query
    Frank Kulash Guru
    Currently Being Moderated


    Hi,

     

     

     

    966281 wrote:

     

    Thanks! Can you help me how to join both queries and what will the final query?  ...

    Sorry, I don't understand.

    The first query I posted doesn't do what you want.

    The second query I posted does (as far as I know).  Why do you want to join them?  Given that you do, what are the results that you want to see?  (Since the results depend on :approval_authority_limit, you should give a couple of different values for :approval_authority_limit and the results you want for each one.)

  • 8. Re: Help on building up a Hierarchical Query
    User474617-OC Newbie
    Currently Being Moderated

    I got the query

     

    SELECTperson_id, full_name, job_id, level1, name, approval_authority

    FROM

    (SELECT pp.person_id,
           pp.full_name,
           pas.job_id,
           LEVEL AS level1,
           pj.name,
           pj.approval_authority,
           ROW_NUMBER ()
              OVER (PARTITION BY pj.approval_authority ORDER BY ROWNUM)
              AS r_num
      FROM per_jobs_vl_temp pj
           JOIN per_all_assignments_f_temp pas
              ON pas.job_id = pj.job_id
           JOIN per_all_people_f_temp pp
              ON pp.person_id = pas.person_id

    START WITH pas.person_id = 62

    CONNECT BY PRIOR pas.supervisor_id = pp.person_id

           AND NVL (PRIOR pj.approval_authority, 0) <
                  :approval_authority_limit )
    where r_num= 1
    ORDER BY  level1  
  • 9. Re: Help on building up a Hierarchical Query
    User474617-OC Newbie
    Currently Being Moderated

    Thanks a lot for your help on this query!

  • 10. Re: Help on building up a Hierarchical Query
    User474617-OC Newbie
    Currently Being Moderated

    Hi

     

    Now I am trying to join more table to your query I am getting error. Can you please help..

     

    Query I got from you

    --------------------------------------

    SELECT    person_id, full_name, job_id, level1, name, approval_authority

    FROM

    (    SELECT pp.person_id,

               pp.full_name,

               pas.job_id,

               LEVEL AS level1,

               pj.name,

               pj.approval_authority,

               ROW_NUMBER ()

                  OVER (PARTITION BY pj.approval_authority ORDER BY ROWNUM)

                  AS r_num

          FROM per_jobs_vl pj

               JOIN per_all_assignments_f pas

                  ON pas.job_id = pj.job_id

               JOIN per_all_people_f pp

                  ON pp.person_id = pas.person_id

    where 1=1

    and trunc(sysdate) between trunc(pas.effective_start_date) and trunc(pas.effective_end_date)

    and trunc(sysdate) between trunc(pp.effective_start_date) and trunc(pp.effective_end_date)          

    START WITH pas.person_id = 62

    CONNECT BY PRIOR pas.supervisor_id = pp.person_id

               AND NVL (PRIOR pj.approval_authority, 0) <

                      :approval_authority_limit )

    where r_num    = 1

    ORDER BY  level1     

     

     

    I modified to include more tables

    ---------------------------------------------------------

    SELECT    ail.invoice_id,ail.line_number,ail.amount,

    person_id, full_name, job_id, level1, name, approval_authority

    FROM

    ap_invoice_lines_all ail, ap_invoice_distributions_all aid,

    (    SELECT

    --            ail.invoice_id,

      --          ail.line_number,

                pp.person_id,

               pp.full_name,

               pas.job_id,

               LEVEL AS level1,

               pj.name,

               pj.approval_authority,

               ROW_NUMBER ()

                  OVER (PARTITION BY pj.approval_authority ORDER BY ROWNUM)

                  AS r_num

          FROM per_jobs_vl pj,

                  --ap_invoice_lines_all_temp ail1, ap_invoice_distributions_all_temp aid1,

                per_all_assignments_f pas,

                per_all_people_f pp

    where 1=1

    and pp.person_id = pas.person_id

    and pas.job_id = pj.job_id

    --and ail1.invoice_id = aid1.invoice_id

    --and ail1.line_number = aid1.invoice_line_number

    --and ail1.invoice_id = ail.invoice_id

    ---and ail1.line_number = ail.line_number

    --and pp.person_id = ail.REQUESTER_ID --62

    --and ail.invoice_id = 46044 and aid.invoice_line_number=1

    and trunc(sysdate) between trunc(pas.effective_start_date) and trunc(pas.effective_end_date)

    and trunc(sysdate) between trunc(pp.effective_start_date) and trunc(pp.effective_end_date)           

    START WITH pas.person_id = 62--ail.REQUESTER_ID --62

    --(select requeter_id from ap_invoice_lines_all_temp where invoice_id = ail.invoice_id and line_number = ail.line_number)

    CONNECT BY PRIOR pas.supervisor_id = pp.person_id

               AND NVL (PRIOR pj.approval_authority, 0) <

                      :approval_authority_limit )

    where r_num    = 1

    and ail.invoice_id = aid.invoice_id

    and ail.line_number = aid.invoice_line_number

    and ail.amount >0

    --and pp.person_id = ail.REQUESTER_ID --62

    and ail.invoice_id = 46044 --and aid.invoice_line_number=1

    ORDER BY  2,level1 

     

     

     

    CREATE TABLE AP_INVOICE_LINES_ALL_TEMP

    (

      INVOICE_ID                      NUMBER(15)    NOT NULL,

      LINE_NUMBER                     NUMBER        NOT NULL,

      LINE_TYPE_LOOKUP_CODE           VARCHAR2(25 BYTE) NOT NULL,

      REQUESTER_ID                    NUMBER(15),

      ORG_ID                          NUMBER(15)    DEFAULT NULL,

      AMOUNT                          NUMBER

      );

     

     

    SET DEFINE OFF;

    Insert into AP_INVOICE_LINES_ALL_TEMP

       (INVOICE_ID, LINE_NUMBER, LINE_TYPE_LOOKUP_CODE, REQUESTER_ID, ORG_ID, AMOUNT)

    Values

       (46044, 1, 'ITEM', 62, 84,

        2500);

    Insert into AP_INVOICE_LINES_ALL_TEMP

       (INVOICE_ID, LINE_NUMBER, LINE_TYPE_LOOKUP_CODE, REQUESTER_ID, ORG_ID, AMOUNT)

    Values

       (46044, 2, 'MISCELLANEOUS', 6035, 84,

        300);

    Insert into AP_INVOICE_LINES_ALL_TEMP

       (INVOICE_ID, LINE_NUMBER, LINE_TYPE_LOOKUP_CODE, REQUESTER_ID, ORG_ID, AMOUNT)

    Values

       (46044, 3, 'FREIGHT', 1632, 84,

        200);

    Insert into AP_INVOICE_LINES_ALL_TEMP

       (INVOICE_ID, LINE_NUMBER, LINE_TYPE_LOOKUP_CODE, ORG_ID, AMOUNT)

    Values

       (46044, 4, 'TAX', 84, 0);

    Insert into AP_INVOICE_LINES_ALL_TEMP

       (INVOICE_ID, LINE_NUMBER, LINE_TYPE_LOOKUP_CODE, ORG_ID, AMOUNT)

    Values

       (46044, 5, 'TAX', 84, 0);

    Insert into AP_INVOICE_LINES_ALL_TEMP

       (INVOICE_ID, LINE_NUMBER, LINE_TYPE_LOOKUP_CODE, ORG_ID, AMOUNT)

    Values

       (46044, 6, 'ITEM', 84, 10000.02);

    Insert into AP_INVOICE_LINES_ALL_TEMP

       (INVOICE_ID, LINE_NUMBER, LINE_TYPE_LOOKUP_CODE, REQUESTER_ID, ORG_ID, AMOUNT)

    Values

       (46044, 7, 'ITEM', 6035, 84,

        25000.01);

    COMMIT;

     

     

    CREATE TABLE fnd_lookup_values_vl_temp

    (

      LOOKUP_TYPE  VARCHAR2(25 BYTE) NOT NULL,

      ATTRIBUTE1                      VARCHAR2(25 BYTE) NOT NULL,

      ATTRIBUTE2                      VARCHAR2(25 BYTE) NOT NULL,

      ATTRIBUTE3                      VARCHAR2(25 BYTE) NOT NULL,

      ATTRIBUTE4                      VARCHAR2(25 BYTE) NOT NULL,

      ATTRIBUTE5                      VARCHAR2(25 BYTE) NOT NULL

      );

     

    SET DEFINE OFF;

    Insert into APPS.fnd_lookup_values_vl_temp

       (LOOKUP_TYPE, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5)

    Values

       ('DISTR_APRV_LVL', '84', '0', '10000', 'Inventory',

        '3');

    Insert into APPS.fnd_lookup_values_vl_temp

       (LOOKUP_TYPE, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5)

    Values

       ('DISTR_APRV_LVL', '84', '10000.01', '25000.00', 'Inventory',

        '4');

    Insert into APPS.fnd_lookup_values_vl_temp

       (LOOKUP_TYPE, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5)

    Values

       ('DISTR_APRV_LVL', '84', '25000.01', '50000.00', 'Inventory',

        '5');

    COMMIT;

     

     

     

     

    approval_authority_limit will be retried from the below query for each line (based on invoice line amount the approval authority will fetched from the below query)

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    select

    flv.attribute5 from  ap_invoice_lines_all_temp ail,

    fnd_lookup_values_vl_temp flv

    where

    flv.lookup_type = 'DISTR_APRV_LVL'

    and flv.attribute1 = ail.org_id

    and ail.amount between flv.attribute2 and flv.attribute3

    and ail.invoice_id = 46044

    --and ail.line_number=1

     

     

     

     

    requester_id will be retried from the below query for each line

    ----------------------------------------------------------------------------------------------------

    select requester_id from ap_invoice_lines_all_temp

    where invoice_id = 46044

    --and line_number = 1

     

     

    My Expected results should be as follow (for line 1 and 7)

    --------------------------------------------------------------------------

    Invoice_id             Line_Number      Amount            person_id            job_id        level      Name   Apporval_authority

    46044                           1                  2500                62                       64            1       Buyer             

    46044                           1                  2500                63                       66            2       Director                4

    46044                           7                  25000.01          62                       64            1       Buyer             

    46044                           7                  25000.01          63                       66            2       Director                4

    46044                           7                  25000.01          68                       1296         3      CFO                      7

     

    Please help on this...

     

    Thanks!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points