3 Replies Latest reply on Jun 21, 2012 10:34 AM by makdutakdu

    link gl and po

    makdutakdu
      hi all

      i have a query in which i require po number and receipt number so in the below query i have added po_headers_all table and po_ditributions_all table
      but po number comes blank .kindly guide how to obtain po number in this query below
      SELECT   cc.segment1, jeh.je_source, jeh.je_category, jeh.doc_sequence_value,
               SUBSTR (   'Sub-Ldg:'
                       || jel.subledger_doc_sequence_value
                       || 'GL:'
                       || jeh.doc_sequence_value,
                       1,
                       30
                      ) REFERENCE,
               SUM (jel.accounted_dr) OVER (PARTITION BY jeh.doc_sequence_value),
               SUM (jel.accounted_cr) OVER (PARTITION BY jeh.doc_sequence_value),
               jel.effective_date, jel.subledger_doc_sequence_value,
               jel.subledger_doc_sequence_id, pha.segment1
          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 pda.code_combination_id(+) = cc.code_combination_id
           AND pha.po_header_id(+) = pda.po_header_id
           AND cc.chart_of_accounts_id = '101'
           AND jel.code_combination_id = cc.code_combination_id
           AND jel.status || ''
      /*ADVICE(24): In Oracle 8 strings of zero length assigned to CHAR variables
                    will blank-pad these rather than making them NULL [111] */
                               = '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'
                                                            )
                                                   )
      /*ADVICE(36): Complex expression not fully parenthesized [404] */
      
      GROUP BY cc.segment1,
               jeh.je_source,
               jeh.je_category,
               jel.subledger_doc_sequence_value,
               jel.subledger_doc_sequence_id,
               pha.segment1,
               SUBSTR (   'Sub-Ldg:'
                       || jel.subledger_doc_sequence_value
                       || 'GL:'
                       || jeh.doc_sequence_value,
                       1,
                       30
                      ),
               jel.accounted_dr,
               jel.accounted_cr,
               jel.effective_date,
               jeh.doc_sequence_value,
               jeh.currency_code
        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
        • 1. Re: link gl and po
          Mike F -Oracle
          Hi.

          There will always be a po_header_id in po_distributions_all and vice versa.
          Not sure you need this.

          AND pha.po_header_id(+) = pda.po_header_id

          Also, here are the links to the tables for gl_interface and then passed into gl_je_... tables.

          reference_21 field contains the type of journal lines, equal to "PO";
          reference_22 field contains the po_header_id for journal type "PO";
          reference_23 field contains the po_distribution_id for journal type "PO";
          reference_24 field contains the segment_1 (PO number) for journal type "PO";

          Hope that helps.
          Mike
          • 2. Re: link gl and po
            makdutakdu
            hi

            when i give
            AND pha.po_header_id = pda.po_header_id


            i get no rows returned..


            kindly guide
            thanking in advance
            • 3. Re: link gl and po
              makdutakdu
              hi all

              could anyone help to link gl and po ?


              kindly guide

              thanking in advance

              Edited by: makdutakdu on Jun 21, 2012 1:34 PM