13 Replies Latest reply on Jul 2, 2012 5:29 AM by rramasam-Oracle

    to link between GL AND PO

    makdutakdu
      hi

      can anyone guide me on linking the following gl tables

      gl_code_combinations cc,
      gl_je_lines jel,
      gl_je_headers jeh

      with the po tables
                     po_distributions_all pda,
      po_headers_all pha
      i am just giving the join part of the query
      
      select....
      .................................
      from 
      FROM gl_code_combinations cc,
                  gl_je_lines jel,
                  gl_je_headers jeh,
                     
      WHERE   cc.segment1 between  :P_SEGMENT1 and NVL(  :P_SEGMENT2,:P_SEGMENT1) 
       and   cc.CHART_OF_ACCOUNTS_ID = '101'
       AND   jel.code_combination_id = cc.code_combination_id
      AND   jel.status || '' = 'P'
      AND   jeh.je_header_id = jel.je_header_id +0
      AND   jeh.actual_flag = 'A'
      --and jel.PERIOD_NAME between :p_from_period and nvl(:p_to_period,:p_from_period)
      and jel.EFFECTIVE_DATE between to_date(:p_from_period,'Mon-RR') and last_day(to_date(nvl(:p_to_period,:p_from_period),'Mon-RR'))
      
      i did the linking between gl and po
      
      as shown below
      select .......
      ....
      FROM gl_code_combinations cc,
                  gl_je_lines jel,
                  gl_je_headers jeh,
                      po_distributions_all pda,
               po_headers_all pha
      WHERE   cc.segment1 between  :P_SEGMENT1 and NVL(  :P_SEGMENT2,:P_SEGMENT1) 
       and   cc.CHART_OF_ACCOUNTS_ID = '101'
       and pha.po_header_id = pda.po_header_id(+)
          AND pda.code_combination_id = cc.code_combination_id
      AND   jel.code_combination_id = cc.code_combination_id
      AND   jel.status || '' = 'P'
      AND   jeh.je_header_id = jel.je_header_id +0
      AND   jeh.actual_flag = 'A'
      --and jel.PERIOD_NAME between :p_from_period and nvl(:p_to_period,:p_from_period)
      and jel.EFFECTIVE_DATE between to_date(:p_from_period,'Mon-RR') and last_day(to_date(nvl(:p_to_period,:p_from_period),'Mon-RR'))
      but i get now rows returned
      kindly guide

      thanking in advance
        • 1. Re: to link between GL AND PO
          PranitSaha
          Hi,

          Following SQL joins all the tables mentioned.


          SELECT pha.segment1,pda.po_distribution_id,gcc.code_combination_id,
          gjl.je_line_num,gjh.je_header_id
          FROM
          po_headers_all pha,
          po_distributions_all pda,
          gl_code_combinations gcc,
          gl_je_headers gjh,
          gl_je_lines gjl
          WHERE pha.po_header_id = pda.po_header_id
          AND pda.code_combination_id = gcc.code_combination_id
          AND gcc.code_combination_id = gjl.code_combination_id
          AND gjl.je_header_id = gjh.je_header_id

          Hope this helps.

          Thanks,

          PS.
          • 2. Re: to link between GL AND PO
            makdutakdu
            hi

            i tried do the joins like you mentioned
            still i am getting no rows returned
            why :(
            does this have something to do with the conditions mentioned in have clause

            i am giving the from and where part of the query i tried
            select -----
            ------
            FROM gl_code_combinations cc,
                        gl_je_lines jel,
                        gl_je_headers jeh,
                            po_distributions_all pda,
                     po_headers_all pha
            WHERE   cc.segment1 between  :P_SEGMENT1 and NVL(  :P_SEGMENT2,:P_SEGMENT1) 
             and   cc.CHART_OF_ACCOUNTS_ID = '101'
             and pha.po_header_id = pda.po_header_id
                 AND pda.code_combination_id = cc.code_combination_id
            AND   jel.code_combination_id = cc.code_combination_id
            AND   jel.status || '' = 'P'
            AND   jeh.je_header_id = jel.je_header_id
            AND   jeh.actual_flag = 'A'
            
            and jel.EFFECTIVE_DATE between to_date(:p_from_period,'Mon-RR') and last_day(to_date(nvl(:p_to_period,:p_from_period),'Mon-RR'))
            group by cc.segment1,jeh.je_source,jeh.JE_CATEGORY ,jel.SUBLEDGER_DOC_SEQUENCE_VALUE , jel.SUBLEDGER_DOC_SEQUENCE_ID,
            SUBSTR( 'Sub-Ldg:'|| jel.SUBLEDGER_DOC_SEQUENCE_VALUE||'GL:'||jeh.DOC_SEQUENCE_VALUE,1,30),
            jel.description,jel.EFFECTIVE_DATE,
               jeh.DOC_SEQUENCE_VALUE,JEH.CURRENCY_CODE,pha.segment1
            having        
            (DECODE(SIGN(nvl(sum(jel.ACCOUNTED_DR),0)- nvl(sum(jel.ACCOUNTED_CR),0)),1,nvl(sum(jel.ACCOUNTED_DR),0)- nvl(sum(jel.ACCOUNTED_CR),0)) !=0
            or  DECODE(SIGN(nvl(sum(jel.ACCOUNTED_DR),0)- nvl(sum(jel.ACCOUNTED_CR),0)),-1,nvl(sum(jel.ACCOUNTED_DR),0)- nvl(sum(jel.ACCOUNTED_CR),0))!=0)
            order by  jel.EFFECTIVE_DATE, REFERENCE, jel.SUBLEDGER_DOC_SEQUENCE_VALUE
            kindly guide
            thanking in advance
            • 3. Re: to link between GL AND PO
              PranitSaha
              Yes, the issue is not with the joins but with the conditions.

              Try to work with one condition at a time by commenting others and see which is causing the issue.

              Thanks,

              PS.
              • 4. Re: to link between GL AND PO
                makdutakdu
                hi

                i tried commenting all the conditions one by one and found out

                when the following is commented the query returns output,all this time i was giving value of cc.segment1 =130999 which is the inventory clearing account
                and they have PO numbers too when i check in erp
                but this query gives no rows returned for '130999'
                select  ...
                ..
                
                FROM gl_code_combinations cc,
                            gl_je_lines jel,
                            gl_je_headers jeh,
                                po_distributions_all pda,
                         po_headers_all pha
                WHERE  
                -- cc.segment1 between  :P_SEGMENT1 and NVL(  :P_SEGMENT2,:P_SEGMENT1) ---- when this is commented
                  cc.CHART_OF_ACCOUNTS_ID = '101'
                 and pha.po_header_id = pda.po_header_id
                     AND pda.code_combination_id = cc.code_combination_id
                AND   jel.code_combination_id = cc.code_combination_id
                AND   jel.status || '' = 'P'
                AND   jeh.je_header_id = jel.je_header_id
                AND   jeh.actual_flag = 'A'
                and cc.segment1='511407'
                
                and jel.EFFECTIVE_DATE between to_date(:p_from_period,'Mon-RR') and last_day(to_date(nvl(:p_to_period,:p_from_period),'Mon-RR'))
                group by cc.segment1,jeh.je_source,jeh.JE_CATEGORY ,jel.SUBLEDGER_DOC_SEQUENCE_VALUE , jel.SUBLEDGER_DOC_SEQUENCE_ID,
                SUBSTR( 'Sub-Ldg:'|| jel.SUBLEDGER_DOC_SEQUENCE_VALUE||'GL:'||jeh.DOC_SEQUENCE_VALUE,1,30),
                jel.description,jel.EFFECTIVE_DATE,
                   jeh.DOC_SEQUENCE_VALUE,JEH.CURRENCY_CODE,pha.segment1
                having        
                (DECODE(SIGN(nvl(sum(jel.ACCOUNTED_DR),0)- nvl(sum(jel.ACCOUNTED_CR),0)),1,nvl(sum(jel.ACCOUNTED_DR),0)- nvl(sum(jel.ACCOUNTED_CR),0)) !=0
                or  DECODE(SIGN(nvl(sum(jel.ACCOUNTED_DR),0)- nvl(sum(jel.ACCOUNTED_CR),0)),-1,nvl(sum(jel.ACCOUNTED_DR),0)- nvl(sum(jel.ACCOUNTED_CR),0))!=0)
                order by  jel.EFFECTIVE_DATE, REFERENCE, jel.SUBLEDGER_DOC_SEQUENCE_VALUE
                
                is there any way to get the required output even when 130999 is passed in cc.segment1,should any other gl ,po tables be included in the joins?
                i am absolutely clueless on what to do

                kindly guide

                thanking in advance
                • 5. Re: to link between GL AND PO
                  PranitSaha
                  Try with:

                  cc.segment1 between TO_NUMBER(:P_SEGMENT1) and TO_NUMBER(NVL( :P_SEGMENT2,:P_SEGMENT1)) ---- when this is commented

                  Thanks,

                  PS.
                  • 6. Re: to link between GL AND PO
                    makdutakdu
                    hi

                    i tried with the cc.segment commented however the output does not contain the details related to the inventory clearing account 130999.
                    should i join the gl tables with receiving transactions table to get the po number in my query to achieve the desired result

                    can you please guide on how to link gl with receiving transactions tables


                    kindly guide
                    thanking in advance
                    • 7. Re: to link between GL AND PO
                      rramasam-Oracle
                      From the SQLs, I do not understand what you are trying to achieve.

                      You are using the code_combination_id to join po_distributions_all and gl_code_combinations which is indicating that you are looking for charge account. But in the explanation you have indicated that it is inventory clearing account. I am not sure if you are expecting the same value as charge account itself. This may not be the case.

                      You are trying to join the po_distributions_all and gl_je_lines via code_combination_id. This is a potential cartesian join as there is NO reference link between pda and gl_je_lines. To find the accounting entries related to a entity you should lookup by correct reference columns.

                      - Raja
                      • 8. Re: to link between GL AND PO
                        makdutakdu
                        hi

                        i am just giving the join part of the original query
                         
                        select....
                        .................................
                        from 
                        FROM gl_code_combinations cc,
                                    gl_je_lines jel,
                                    gl_je_headers jeh,
                                       
                        WHERE   cc.segment1 between  :P_SEGMENT1 and NVL(  :P_SEGMENT2,:P_SEGMENT1) 
                         and   cc.CHART_OF_ACCOUNTS_ID = '101'
                         AND   jel.code_combination_id = cc.code_combination_id
                        AND   jel.status || '' = 'P'
                        AND   jeh.je_header_id = jel.je_header_id +0
                        AND   jeh.actual_flag = 'A'
                        --and jel.PERIOD_NAME between :p_from_period and nvl(:p_to_period,:p_from_period)
                        and jel.EFFECTIVE_DATE between to_date(:p_from_period,'Mon-RR') and last_day(to_date(nvl(:p_to_period,:p_from_period),'Mon-RR'))
                        
                        the requiremnt from the client is
                        to get po number also in the above query
                        kindly guide
                        thanking in advance
                        • 9. Re: to link between GL AND PO
                          rramasam-Oracle
                          It is still not clear. Are you saying that you have the following SQL and you want to retrieve the PO Number also along with it?

                          If yes, what is the current SQL is trying to do? The SQL you have shown below is trying to look for Actual entries in Posted status for the accounts with segment1 as some specific value. Is this what you are tying look for? what are the journal lines exactly?

                          Note: The table gl_je_lines has a set columns known as reference columns. Depending on the type of accounting entry the subledger populates different values in each reference columns. If you are trying to join the journal line back to the subledger transaction, you should use the reference columns to join.

                          - Raja
                           
                          SELECT .....................................
                            FROM gl_code_combinations cc,
                                 gl_je_lines jel,
                                 gl_je_headers jeh,
                                 <transaction_table>
                                 <po tables>
                           WHERE cc.segment1 between :P_SEGMENT1 and NVL(  :P_SEGMENT2,:P_SEGMENT1) 
                             AND cc.CHART_OF_ACCOUNTS_ID = '101'
                             AND jel.code_combination_id = cc.code_combination_id
                             AND jel.status || '' = 'P'
                             AND jeh.je_header_id = jel.je_header_id +0
                             AND jeh.actual_flag = 'A'
                             AND jel.EFFECTIVE_DATE BETWEEN TO_DATE(:p_from_period,'Mon-RR') AND LAST_DAY(to_date(nvl(:p_to_period,:p_from_period),'Mon-RR'))
                             ---
                             <AND join jel with transaction table with reference columns>
                             <AND transaction table with po tables via po_distribution_id and etc>
                             ---   
                          • 10. Re: to link between GL AND PO
                            makdutakdu
                            hi

                            can u please mention the po tables and transaction table

                            kindly guide
                            thanking in advance
                            • 11. Re: to link between GL AND PO
                              rramasam-Oracle
                              We cannot help in solving the problem without knowing the details. Please indicate the exactly what the SQL is trying to do.
                              • 12. Re: to link between GL AND PO
                                makdutakdu
                                hi

                                this is the query of a report showing Inventory clearing Account and Inventory Accrual Account.depending on the params given inv clearing account and inventory accrual account details are shown,in this the requiremnt is to include PO number too
                                • 13. Re: to link between GL AND PO
                                  rramasam-Oracle
                                  11.5.10 or R12?

                                  Anyway you can try using the following tables. The gl lines can be joined to mtl_transaction_accounts / rcv_receiving_sub_ledger which in turn can be joined to mtl_material_transactions / rcv_transactions and the join back to po distributions table.

                                  - Raja

                                  Edited by: rramasam on Jul 2, 2012 10:56 AM
                                  1 person found this helpful