Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Ujas Bhatt
    Due Date Calculation Formula based on Terms Date and Terms...
    Topic posted May 7, 2019 by Ujas BhattBronze Trophy: 5,000+ Points, tagged OBI Answers, OBIEE Answers, OTBI, Reports, Tip 
    134 Views, 10 Comments
    Title:
    Due Date Calculation Formula based on Terms Date and Terms Name in OTBI Report
    Summary:
    Looking for a formula to derive Due Date based on Terms Date and Terms Name in OTBI Repoert
    Content:

    Hi All,

    I have a requirement to derive Invoice Due Date based on Terms date and terms name.  I am using following subject area which has Terms Date and Terms Name

    OTBI Subject Area :-  [Payable Invoices - Transaction Real Time]

    I want to calculate Invoice Due date based on Terms Date and Terms name on the Payable invoices. Please suggest the formula or custom SQL to achieve this.

    Appreciate your help !!!

    Example:-

    Terms Date  =  05/07/2019 (MM/DD/YYYY)

    Terms Name = Net30  (Or can be Net15, Net60, Net90, Immediate etc,.)

    Result:-  Due Date = 06/06/2019

     

    Comment

     

    • Beyond the Crown

      There might be an easier way but one way might be to this but you can try to "Add a calculated Measure" and create "Bins" off of the Terms Name. For example Net30 would be = 30, Net 60 = 60. Then take the result and timestampAdd to the Terms Date.

    • Beyond the Crown

      This would be a good idea/enhancement request to ask for too. For them to bring in the Due Date in OTBI.

      • Marybeth Snodgrass

        I agree. There should be a due_date in the AP Transactions subject area. The only due date currently is in the AP Installments subject area, which isn't helpful if you're trying to create a report reflecting outstanding invoices that are overdue.

    • Gopinath Kartheesan

      Due date is available under "Payables Invoices - Installments Real Time" subject area >  Invoices Installment Details. Please try that and see if that works

      • Ujas Bhatt

        Yes however i am not merging two subject areas for deriving due date.  I would like to use only one subject area and [Payable Invoices - Transaction Real Time] and want to achieve this result.

        • Gopinath Kartheesan

          Most of the details under Payables Invoices Transactions subject area might be available under the Installments subject area as well.  But is there any specific reason you don't want to merge 2 subject areas?

        • Ujas Bhatt

          When we merge two subject area's, somehow the results doesn't seem to be accurate inspite of using the dimensions and other related attributes.  Its good to merge two subject areas however in our case merging Installment Real Time and Transaction Real time isn't fetching the right data results.  Do you have any better idea or example to address this?

    • Ujas Bhatt

      When we merge two subject area's, somehow the results doesn't seem to be accurate inspite of using the dimensions and other related attributes.  Its good to merge two subject areas however in our case merging Installment Real Time and Transaction Real time isn't fetching the right data results.  Do you have any better idea or example to address this?

      • Gopinath Kartheesan

        Could you please provide the Catalog file for the report that you built with the respective dimensions and facts?  I can try to diagnose why it's not providing correct results by merging the 2 SAs

    • Marybeth Snodgrass

      You should add this to the idea lab. Seems that oracle is more responsive to enhancement requests on those forums.