5 Replies Latest reply: Oct 29, 2013 1:06 PM by user10615659 RSS

    How to model EBS PO, REQ and Receipts in the RPD

    Lance Botha

      Hi All,

       

      I'm trying to design and model purchase orders, requisitions and receipts in the RPD, but I’m coming stuck and need some help.  My end goal is to be able to create the following reports in answers which report real time off an EBS database:

       

      1. Return all Requisitions (it may not have a Purchase Order)

      2. Return all Requisitions and Purchase Orders (whether or not the Requisition has a Purchase Order)

      3. Return all Requisitions, Purchase Orders, receipts (whether or not the Requisition has a Purchase Order or receipt)

       

      4. Return all Purchase Orders (it may not have a requisition)

      5. Return all Purchase Orders and Requisitions (whether or not the Purchase Order has a requisition)

      6. Return all Purchase Orders, Requisitions and receipts (whether or not the Purchase Order has a requisition or receipt)

       

      7. Return all receipts

      8. Return all receipts and Purchase Orders (whether or not the receipts has a Purchase Orders)

      9. Return all receipts, Purchase Orders and Requisitions (whether or not the receipts has a Purchase Orders or requisition)

       

      My dilemma is that I'm not sure where to start modelling these tables in the recommended star topology.  It's never as easy as a simple 'Sales Fact' in the middle and 'Various Dimensions' around, as you would read in various places. How should we model po_headers_all, po_lines_all and po_distributions_all for example, which is contains a mixture of facts and dimension.

       

      Here is some of the EBS Fact & Dimension Tables I believe would need modelled based on the above report requirements:

       

      po_requisition_headers_all

      po_requisition_lines_all

      po_req_distributions_all

       

      po_headers_all

      po_lines_all

      po_line_locations_all

      po_distributions_all

       

      rcv_transactions

      rcv_shipment_headers

      rcv_shipment_lines

       

      po_vendors

      gl_code_combinations

      per_people_f

      fnd_user

       

      Any ideas where to start (or stop)?

       

      Thanks,

       

      Lance

       

        • 1. Re: How to model EBS PO, REQ and Receipts in the RPD
          user10615659

          you need to make a database view  with

           

          1.  All Requisitions from source with related Purchase order and receipts or with out them ( outerjoin)

          union

          2. All Purchase orders with receipts or without receipts (outerjoin) and with out Requisitions ( not in or null)

          union

          3. All Receipts without Purchase orders(null) and with out Requisitions(null)

           

          note that you need to create a primary key for maintaining the granularity. the least level ID can do this job.

          but since you "Union" data you can make use of some key generator or identity.

           

          then use this view and import to physical layer. create 2 alias one for fact and one for dimension.

          join fact and dimension using the same key

           

          create a new Subject area and drag and drop fact and dimension. (note: for a star schema you need bare minimum a fact and and dimension, here we are making use of the same table as fact and dimension join 1:1 though typically its n:1 still this will work)

          do the same with presentation layer.

           

          The above design is suggested based on an assumption that there is no other (table/view) dimension involved and there are no measure apart from count.

           

          I suggest a view because you want the report real time. Please use appropriate filters to get the data in the view so that it doesn't bring performance issue on source system or on the report e.g. a day's or a week's data.

           

          keep refreshing the view in frequent intervals.

          Or you can try putting them in stored proc. and call the stored proc every time you run the report ( disable cache). This you will do it in physical layer.

           

          mark if this helps

          • 2. Re: How to model EBS PO, REQ and Receipts in the RPD
            Lance Botha

            Thanks for taking your time to reply.

             

            Unfortunately they method you suggested is the only method I can think of achieving the result, but with that approach I'll have the following issue.  I'll need to create and maintain 3 DB views which each might contain some duplicate information from the other view.  The performance of the view which has requisitions as the source with an outer-join to purchase orders would be slow and unnecessary when the report needed to be developed only requires information on Requisitions.

             

            I was learning towards a solution which involved creating 3 separate views for each area (Requisitions, Purchase Orders and Receipts) and modeling the joins within the RPD rather than in the views itself.  This way, you could create a report only on Requisitions if required, or Requisitions and whether it has a Purchase Orders or not.  Going back to the good old discoverer days, this was possible but now seems tricky, especially if you're trying to follow Oracle's Best Practices in RPD design and modelling.

             

            Cheers,

             

            Lance

            • 3. Re: How to model EBS PO, REQ and Receipts in the RPD
              user10615659

              I think I answered your question on how to make a subject area with no facts and dimensions.

              You can change your design as per your need. But if you want to build a Subject area without facts and still make it work, the approach I mentioned will be of help.

               

              If this makes sense mark as helpful or correct as applicable.

               

              regards

              Senthil

              • 4. Re: How to model EBS PO, REQ and Receipts in the RPD
                Lance Botha

                Senthil, my second post indicated that the answer you provided wasn't helpful and i will be looking for someone else to comment.  As stated in my original question "My dilemma is that I'm not sure where to start modelling these tables in the recommended star topology".  What you provided is not what I am asking for, therefore the answer was unhelpful.

                 

                Please let someone else respond, because this is not a competition on how many helpful / correct answers you can receive.

                • 5. Re: How to model EBS PO, REQ and Receipts in the RPD
                  user10615659

                  if you want a star with requisition, purchase order and receipts you need another table which will behave as a fact.

                  So you have to build a fact with foreign keys from these 3 tables which are related. If you know any table that will readily do that, for your criteria of reporting, make use of it. if not you need to create a new one.

                   

                  The fact needs to be made the way I mentioned in the earlier post (requistion Union Purchase order union receipts).

                   

                  there are many ways to model a report/subject area. If you have a better approach pls enlighten me.

                   

                  Besides there is no need for me to compete in getting you started or to bring you out of your Dilemma.