6 Replies Latest reply on Jun 8, 2011 12:43 PM by Numberless

    Blanket Releases Amount

    Numberless
      Hi all,
      When we go to po summary form and query for BPA(blanket agreement), you can see a field, "Released" (AMT_RELEASED_DSP, if check EXAMINE). It is the sum of all blanket releases. Where can i find the field in the database. I looked up most of the purchasing tables, but couldn't find where the sum of all releases is stored. If anyone has any idea, please advice.
      Thanks in advance,
      kwin.
        • 1. Re: Blanket Releases Amount
          Sandeep Gandhi, Consultant
          Oracle calculates the "Released" field based on the po releases when you query the form. It is not stored anywhere.

          Hope this answers your question,
          Sandeep Gandhi
          • 2. Re: Blanket Releases Amount
            suvam2006
            Hi,

            IF the release amount is not gettig stored any where, Then in case of Data Migration Activity, How we are going to get the amount extracted(or claculated)


            Thanks
            Suvam
            • 3. Re: Blanket Releases Amount
              Sandeep Gandhi, Consultant
              Are you migrating out of Oracle OR in to Oracle?
              If you are migrating data in to Oracle, you will create the Blanket PO and then you will create the Blanket Releases. Oracle will automatically calculated the released amount for displaying it whenever you open the PO on screen.

              If you are migrating data out of Oracle to a different system, then you will have to write queries that do the calculation.

              Sandeep Gandhi
              • 4. Re: Blanket Releases Amount
                suvam2006
                Thanks Sandip,

                Yes we are migrating out to oracle
                But not to a different system, but to another oracle Installation.

                As per my assumpton the PO FORM internally calculate the total release amount , based on the number Standard POs released against the perticular BPA.
                But not sure what exact calculation it does.

                Can you please provide me the sample query, which actually does the calculation.
                If not query, then if you can provide the logic also...will be helpful

                Thanks
                Suvam
                • 5. Re: Blanket Releases Amount
                  Sandeep Gandhi, Consultant
                  You can start with the following
                  SELECT   pha.segment1 po#, SUM (price_override * quantity) released_amount
                      FROM po_headers_all pha, po_line_locations_all plla
                     WHERE plla.po_header_id = pha.po_header_id
                       AND plla.po_release_id IS NOT NULL
                  GROUP BY pha.segment1
                  Add conditions to exclude unapproved POs, cancelled shipments etc.

                  Hope this helps,
                  Sandeep Gandhi
                  • 6. Re: Blanket Releases Amount
                    Numberless
                    Hi All,
                    For those who are trying to calculate the Blankets release Amount, I used the following query.


                    SELECT NVL(SUM (DECODE (POL.order_type_lookup_code,
                    'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0),
                    'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0),
                    (NVL(PLL.quantity, 0) - NVL(PLL.quantity_cancelled, 0))
                    * NVL(PLL.price_override, 0))),0) AMT
                    FROM po.po_line_locations_all pll,
                    po.po_headers_all poh,
                    po.po_lines_all POL
                    WHERE poh.po_header_id = POL.po_header_id
                    AND POL.po_line_id = PLL.po_line_id
                    AND pll.shipment_type NOT IN ('PRICE BREAK');

                    This is for my requirement only. You can include other conditions based upon your reqs.
                    Hope this helps.

                    Thanks,
                    kwin.