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!

Calculating supplier balance with sql

Discussion in 'Oracle Financials' started by Makus, Jun 13, 2014.

  1. Makus

    Makus Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Italy
    Hello to all,

    in our office we work with Oracle Financial 11 and often we use the function of the Oracle Payable module to calculate a supplier balance owed: //docs.oracle.com/cd/A60725_05/html/comnls/us/ap/invadj07.htm.

    Someone has a sql query to reproduce the function of this button? We are experimenting some problem with prepayments and associated invoices.

    Many thanks.

    Marco - Italy
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Marco,

    I don't have EBS R11 but R12 and in this version, the supplier balance is calculated in a (very complex) query within the Oracle form (component name: APXINWKB.fmb), Unfortunately, there is no such package I know of and if it's something done in the form in R12 then most probably it would be similar in R11. Try to check the form name and request a developer to see it he can extract the query.

    I do have a simple query which gives the balance of the outstanding invoices for a given supplier:

    Code (SQL):
    SELECT
    NVL(PS.gross_amount,0),
    NVL(PS.amount_remaining,0),
    I.invoice_id
    FROM AP_INVOICES_ALL I,
      AP_PAYMENT_SCHEDULES_ALL PS,
      AP_SUPPLIER_SITES_ALL VS
    WHERE 1                  =1
    AND PS.amount_remaining != 0
    AND PS.invoice_id        = I.invoice_id
    AND NVL(I.payment_status_flag, 'N')  != 'Y'
    AND I.cancelled_date               IS NULL
    AND VS.vendor_site_id               = I.vendor_site_id
    AND NOT (I.invoice_type_lookup_code = 'PREPAYMENT'
    AND NVL(I.payment_status_flag, 'N') = 'N')
    AND I.vendor_id                     = <your_vendor_id>
    ;
     
    As I said, it's a "simplified" version: it does not take into account currencies, discounts, pre-payments etc...

    You can build up on this and share with the "club" if you have a more advanced version.
     
  3. Makus

    Makus Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Italy
    Hi Rajen,

    many thanks for your help :)

    This week at work we will start to build a query with your code as a starting point.

    When we are sure that it works properly I will publish our results on the forum.

    Regards!

    Marco - Italy
     
  4. Makus

    Makus Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Italy
    Hi Rajen,

    as promised here is the solution that we have developed in our office to simulate the suppliers balance owed of Oracle Financials R11.

    It is a hybrid approach: a query to extract "raw" records and an Excel pivot to combine and represent the data.

    The invoices applied to prepayments have in the field PREPAY_DISTRIBUTION_ID the same code found in the field INVOICE_DISTRIBUTION_ID of the prepayment: we use this information as a link in the Excel pivot.

    Code (SQL):

    SELECT
      APL.ORG_ID,
      APL.INVOICE_TYPE_LOOKUP_CODE AS DOCUMENT_TYPE,
      APL.INVOICE_ID               AS DOCUMENT_ID,
      APD.INVOICE_DISTRIBUTION_ID  AS LINK_ID,
      APL.INVOICE_CURRENCY_CODE    AS CURRENCY,
      APD.AMOUNT,
      POV.VENDOR_NAME,
      POV.SEGMENT1    AS VENDOR_CODE,
      APL.INVOICE_NUM AS DOCUMENT_NUM,
      APL.PAYMENT_STATUS_FLAG
    FROM
      AP.AP_INVOICE_DISTRIBUTIONS_ALL APD
    INNER JOIN AP.AP_INVOICES_ALL APL
    ON
      APD.INVOICE_ID = APL.INVOICE_ID
    INNER JOIN PO.PO_VENDORS POV
    ON
      APL.VENDOR_ID = POV.VENDOR_ID
    WHERE
      APL.INVOICE_TYPE_LOOKUP_CODE    = 'PREPAYMENT'
    AND APD.LINE_TYPE_LOOKUP_CODE    <> 'TAX'
    AND APL.PAYMENT_STATUS_FLAG       = 'Y'
    AND APL.INVOICE_AMOUNT           <> 0
    AND APL.EARLIEST_SETTLEMENT_DATE IS NOT NULL
    AND POV.SEGMENT1                  = :SUPPLIER_CODE
    UNION ALL
    SELECT
      APL.ORG_ID,
      APL.INVOICE_TYPE_LOOKUP_CODE AS DOCUMENT_TYPE,
      APL.INVOICE_ID               AS DOCUMENT_ID,
      APD.PREPAY_DISTRIBUTION_ID   AS LINK_ID,
      APL.INVOICE_CURRENCY_CODE    AS CURRENCY,
      APD.AMOUNT,
      POV.VENDOR_NAME,
      POV.SEGMENT1    AS VENDOR_CODE,
      APL.INVOICE_NUM AS DOCUMENT_NUM,
      APL.PAYMENT_STATUS_FLAG
    FROM
      AP.AP_INVOICE_DISTRIBUTIONS_ALL APD
    INNER JOIN AP.AP_INVOICES_ALL APL
    ON
      APD.INVOICE_ID = APL.INVOICE_ID
    INNER JOIN PO.PO_VENDORS POV
    ON
      APL.VENDOR_ID = POV.VENDOR_ID
    WHERE
      APL.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
    AND APD.PREPAY_DISTRIBUTION_ID IS NOT NULL
    AND APD.LINE_TYPE_LOOKUP_CODE  <> 'TAX'
    AND POV.SEGMENT1                = :SUPPLIER_CODE
    UNION ALL
    SELECT
      APL.ORG_ID,
      APL.INVOICE_TYPE_LOOKUP_CODE AS DOCUMENT_TYPE,
      APL.INVOICE_ID               AS DOCUMENT_ID,
      APD.PREPAY_DISTRIBUTION_ID   AS LINK_ID,
      APL.INVOICE_CURRENCY_CODE    AS CURRENCY,
      APD.AMOUNT,
      POV.VENDOR_NAME,
      POV.SEGMENT1    AS VENDOR_CODE,
      APL.INVOICE_NUM AS DOCUMENT_NUM,
      APL.PAYMENT_STATUS_FLAG
    FROM
      AP.AP_INVOICE_DISTRIBUTIONS_ALL APD
    INNER JOIN AP.AP_INVOICES_ALL APL
    ON
      APD.INVOICE_ID = APL.INVOICE_ID
    INNER JOIN PO.PO_VENDORS POV
    ON
      APL.VENDOR_ID = POV.VENDOR_ID
    WHERE
      APL.INVOICE_TYPE_LOOKUP_CODE NOT IN ('DEBIT', 'PREPAYMENT', 'AWT', 'EXPENSE REPORT', 'MIXED')
    AND APD.PREPAY_DISTRIBUTION_ID     IS NULL
    AND APL.PAYMENT_STATUS_FLAG        <> 'Y'
    AND APL.INVOICE_AMOUNT             <> 0
    AND POV.SEGMENT1                    = :SUPPLIER_CODE;
     
    In the first SELECT we extract the distributions of the paid prepayments (the criteria EARLIEST_SETTLEMENT_DATE IS NOT NULL ignores permanent prepayments).

    In the second SELECT we extract the distributions of the invoices applied to some prepayment.

    In the third SELECT we extract the distributions of the invoices not paid and not applied to prepayments.

    We copy the result set of the query in two Excel sheets: on the first the invoices to be paid (column LINK_ID empty), on the second prepayments and applied invoices (column LINK_ID populated).

    On the first sheet we obtain the sum of the unpaid invoices, on the second sheet we create a pivot with the LINK_ID field in the row labels to subtract applied invoices to prepayments and filter out the prepayments fully applied.

    The balance owed is unpaid invoices minus available prepayments.

    Thanks for your help!

    Marco - Italy
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Marco,

    That's great and wisely done :) ... even with the UNION ALL it should be running quite fast and above all giving you your expected results (I hope so !).

    Sometimes, it's better to have a simple (or moderate) SQL to extract "raw" data and process with a 3rd Party tool (Excel or any other) than writing & debugging a query of 20-30 tables with joins, outer joins, union etc... which take a long while to execute and may "bomb out" one day ... as we may not have tested all scenarios (EBS Data model, as you know, is quite complex).

    Anyway, thanks for sharing with the community - really appreciated and am sure will be very useful for the members of the "club" also.