6 Replies Latest reply on Jun 20, 2014 8:58 AM by VimalRM

    R12 version of this query??

    Govind143

      SELECT DISTINCT i.set_of_books_id,

                      cc.segment6 natural_account_number2,

                      cc.segment1 location_code,

                      tb.invoice_id invoice_id1

        FROM ap_trial_balance tb, ap_invoices_all i, gl_code_combinations cc

      WHERE tb.invoice_id = i.invoice_id

            --AND i.set_of_books_id = 3

         AND tb.accounting_date <= :p_to_date

            --AND cc.segment1 = NVL(:p_office_code, cc.segment1)

         AND i.accts_pay_code_combination_id = cc.code_combination_id

         AND cc.segment6 = '208130'

         AND EXISTS (SELECT 'Employee'

                FROM po_vendors

               WHERE vendor_id = i.vendor_id

                 AND employee_id IS NOT NULL)

      GROUP BY i.set_of_books_id, tb.invoice_id, cc.segment6, cc.segment1

      HAVING SUM(nvl(tb.distribution_amount, 0)) - SUM(nvl(payment_amount, 0)) != 0

       

       

      Above is the query of 11i instance...Need to migrate this to R12.

      Can anyone post the R12 version of this query??

        • 1. Re: R12 version of this query??
          Neeraj_Shrivastava

          Hi,

           

          1. PO_VENDORS has been replaced with AP_SUPPLIERS in R12

          Joining Condition with AP_INVOICES_ALL

          AP_SUPPLIERS.vendor_id = AP_INVOICES_ALL.vendor_id

           

          2. AP_TRIAL_BALNACES has been replaced with XLA_TRIAL_BALANCES in R12.

          Joining Condition with AP_INVOICES_ALL

           

          WHERE xla_transaction_entities.source_id_int_1  = ap_invoices_all.invoice_id

          AND xla_trial_balances.entity_id = xla_transaction_entities.entity_id
          AND xte.application_id  = 200 --Payables Application

           

          Regards,

          Neeraj.

          • 2. Re: R12 version of this query??
            Hussein Sawwan-Oracle

            You need to fix the code so it would work with R12 -- With the help of the following docs/links you should be able to do so.

             

            eTRM

            http://etrm.oracle.com/

             

            Identifying Data Model Changes Between EBS 12.1.3 and Prior EBS Releases

            https://blogs.oracle.com/stevenChan/entry/ebs_data_model_1213

             

            EBS Data Model Comparison Report Overview [ID 1290886.1]

             

            EBS Seed Data Comparison Reports Now Available

            https://blogs.oracle.com/stevenChan/entry/ebs_seed_data_comparison_reports

             

            Thanks,

            Hussein

            • 3. Re: R12 version of this query??
              Govind143

              Hi Neeraj,

               

              xla_trial_balances.entity_id does not exist..infact there are two entity_id columns (Source & Applied_to).

              I am not sure if this is the correct joining conditions

               

              Thanks anyways

              • 4. Re: R12 version of this query??
                tvCa-Oracle

                Better debugging would be possible if you would show the errors of this query, along with the query itself, so we have an idea where the problem is already.

                This is assuming that the query does NOT actually run on R12, compared to the fact it may run but give wrong output, which is technically possible but just less likely.

                • 5. Re: R12 version of this query??
                  Govind143

                  SELECT DISTINCT i.set_of_books_id,

                                  cc.segment6 natural_account_number2,

                                  cc.segment1 location_code,

                                  tb.invoice_id invoice_id1

                    FROM ap_trial_balance tb, ap_invoices_all i, gl_code_combinations cc

                  WHERE tb.invoice_id = i.invoice_id

                     AND tb.accounting_date <= /*:p_to_date*/ SYSDATE

                     AND i.accts_pay_code_combination_id = cc.code_combination_id

                     AND cc.segment6 = '208130'

                     AND EXISTS (SELECT 'Employee'

                            FROM po_vendors

                           WHERE vendor_id = i.vendor_id

                             AND employee_id IS NOT NULL)

                  GROUP BY i.set_of_books_id, tb.invoice_id, cc.segment6, cc.segment1

                  HAVING SUM(nvl(tb.distribution_amount, 0)) - SUM(nvl(payment_amount, 0)) != 0;

                   

                   

                  This is the query.

                  This is not giving any error but as the table ap_trial_balance is not having any records in R12, so the whole query is not returning any records.

                  • 6. Re: R12 version of this query??
                    VimalRM

                    Hi,

                     

                    This Query Not Work in R12 Because of Ap_trial_Balances not Updated any Values in R12.

                    You Can useXla Tables like Xla_trial_balances,xla_ae_headers,xla_ae_lines and get output.

                     

                    Regards,

                    Vimal