1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to JOIN AP_INVOICES_ALL with GL_CODE_COMBINATIONS

Discussion in 'Oracle Financials' started by manikandan.mscit, May 12, 2009.

  1. manikandan.mscit

    manikandan.mscit Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Anybody Know How to JOIN AP_INVOICES_ALL with GL_CODE_COMBINATIONS(using CCID)
     
  2. Kirti

    Kirti Forum Advisor

    Messages:
    46
    Likes Received:
    12
    Trophy Points:
    130
    You mean you want to check the CCID of invoice distributions right?

    AP_INVOICES_ALL --> invoice_id --> AP_INVOICE_DISTRIBUTIONS_ALL

    AP_INVOICE_DISTRIBUTIONS_AL --> dist_code_combination_id

    You can check the dist_code_combination_id with gl ccid
     
  3. manikandan.mscit

    manikandan.mscit Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thank You for Previous Comment!

    Can u please solve the following query join problem:

    Code (SQL):
    SELECT   aida.invoice_id,
             aia.invoice_date invoice_date,
             aia.invoice_num,
             lc_type.displayed_field vendor_type,
             pv.vendor_name,
             pv.segment1 vendor_code,
             aia.invoice_type_lookup_code,
             aida.amount amount
    FROM     ap_invoices_all aia,
             po_vendors pv,
             po_lookup_codes lc_type,
             gl_code_combinations gcc,
             ap_invoice_distributions_all aida
    WHERE
            aia.vendor_id = pv.vendor_id
            AND aida.invoice_id = aia.invoice_id
            AND aida.org_id = :p_org_id
            AND aida.dist_code_combination_id = gcc.code_combination_id  
            AND aia.invoice_type_lookup_code = 'STANDARD'
            AND aia.invoice_num NOT LIKE '%TDS-SI%'
            AND aia.payment_status_flag IN ('N', 'P','Y')
            AND TRUNC (aia.invoice_date) <= :p_as_on_date      
             
            AND lc_type.lookup_code(+) = pv.vendor_type_lookup_code
            AND lc_type.lookup_type(+) = 'VENDOR TYPE'
            AND lc_type.displayed_field = NVL (:p_vendor_type, lc_type.displayed_field)
            AND TO_NUMBER (gcc.segment3) BETWEEN NVL (:p_from_nacc, gcc.segment3)
                                          AND NVL (:p_to_nacc, gcc.segment3)
            AND  apps.ap_invoices_pkg.get_approval_status
                         (aia.invoice_id,
                          aia.invoice_amount,
                          aia.payment_status_flag,
                          aia.invoice_type_lookup_code
                         ) NOT IN ('NEVER APPROVED')
    GROUP BY aida.invoice_id,
             aia.invoice_date,
             aia.invoice_num,
             lc_type.displayed_field,
             pv.vendor_name,
             pv.segment1,
             aia.invoice_type_lookup_code,
             aida.amount
             apps.ap_invoices_pkg.get_approval_status
                         (aia.invoice_id,
                          aia.invoice_amount,
                          aia.payment_status_flag,
                          aia.invoice_type_lookup_code
                         )
    ORDER BY 4,5, aia.invoice_num;  
    For this sql Query statement "No row returned" occurs.

    Advance Thank You!
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    i don't think it's a problem with join conditions. you have either no actual data for the logic you are trying to implement or you have not implemented your logic correctly.
     
  5. manikandan.mscit

    manikandan.mscit Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Acctually I need to get the following details

    invoice_id,
    invoice_date,
    invoice_num,
    invoice_type_lookup_code,
    amount amount

    from ap_invoices_all, dependent on From(Account.No) and TO(Acc.No) from gl_code_combinations. So I used some joins for AP & GL.

    --Refer previous post for the SQL Query.I didn't get the result.(No row returned)

    Anyone please solve the query.

    Thank You in Advance!
     
  6. sum1nth

    sum1nth Guest

    Code (SQL):
    SELECT aia.invoice_date,
           aia.invoice_num,
           aia.invoice_id,
           aia.invoice_type_lookup_code
           FROM ap_invoices_all aia
           ,ap_invoice_distributions_all aida
           ,gl_code_combinations gcc
           WHERE
           aia.invoice_id = aida.invoice_id
           AND aida.dist_code_combination_id=gcc.code_combination_id
           AND gcc.segment1||gcc.segment2||gcc.segment3||gcc.segment4||gcc.segment5 = :p_from_accno
           AND gcc.segment1||gcc.segment2||gcc.segment3||gcc.segment4||gcc.segment5 = :p_to_accno