10 Replies Latest reply: Jul 30, 2013 11:04 AM by User474617-OC RSS

    Help on building up a Hierarchical Query

    User474617-OC

      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

          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

            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

              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

                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

                  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

                    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


                      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

                        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

                          Thanks a lot for your help on this query!

                          • 10. Re: Help on building up a Hierarchical Query
                            User474617-OC

                            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!