6 Replies Latest reply on Mar 24, 2010 10:01 AM by 760914

    Return nothing when run Open Purchase order report(by Buyer)

    760914
      We implement iPro/PO in Oracle. I finished the PO setup. But now user report that they are not able to generate any output for a particular Buyer when they run the Open Purchase Orders Report(by Buyer) report in the PO module. Even I leave the parameter wide open for the request, it also does not return any information.
      Does it relate to the PO setting in system? Anyone can kindly guide me how to check such problem?
        • 1. Re: Return nothing when run Open Purchase order report(by Buyer)
          S.PDASH
          Hi
          1) Are facing this issue for this particular buyer only?

          2) Are you able to query (from frontend) any PO belongs to the particular buyer?

          Sample of this report:
          ==========================================================================

          <SOB> Open Purchase Orders by Buyer Report Date: 18-MAR-2010 10:41
          test


          Report Parameters

          Buyer: AFFILIATE, UPO
          Vendors From:
          To:
          <SOB> Open Purchase Orders by Buyer Report Date: 18-MAR-2010 10:41
          test Page: 2 of 2144

          Buyer: AFFILIATE, UPO Vendor: XXXXXXXXXXXXXXXXXXXXXXXXX

          PO Number-Release Line Currency Line Type Category Item Rev Description
          ------------------- ---- -------- --------- -------------------- -------------------- --- ------------------------------------------
          Quantity/Amount Quantity/Amount Quantity/Amount Percent
          Shipment Date Unit Price Unit Ordered Received Billed Due Closed Status
          --------- --------- --------------- -------- --------------- --------------- --------------- ------- --------------
          1000001.1 1 SEK Goods UA.UA 56411279 KVICKLAB HOLDER
          1 07-APR-06 19015.01661 Each 7.0000 7.0000 0.0000 0 Closed For
          Receiving
          1000001.1 2 SEK Goods UA.UA 56411495 5 Liter Tank Kit
          1 07-APR-06 20506.38206 Each 6.0000 6.0000 0.0000 0 Closed For



          Regards,
          S.P DASH
          • 2. Re: Return nothing when run Open Purchase order report(by Buyer)
            760914
            Hi S.P DASH,
            Thanks for your reply.

            I ran this report with one particular buyer. I also checked the POs from frontend under particular buyer's name. There are some opening POs belong to this buyer.
            I also tried to run a report just leaving the Buyer name blank. But also no records return.

            We did some new PO setting recently for this country. Report is working well in other coutnry. So I suppose there should be some setup problem cause this issue.
            Just have no idea which part may cause this issue. Buyer setup, or PO setup? And how to check?
            Please kindly hlep.

            Thanks and regards,
            Kyla
            • 3. Re: Return nothing when run Open Purchase order report(by Buyer)
              S.PDASH
              Hi Kyla,

              PO raised by the buyer is 'STANDARD','BLANKET','PLANNED' ? What is the PO Status..? It should be "OPEN"..

              Query used in this report is as below :

              Query - Company
              ------------------------
              SELECT gsb.name c_company
              , fsp.inventory_organization_id c_organization_id
              , gsb.currency_code C_CURRENCY_BASE
              , gsb.chart_of_accounts_id STRUCTURE_ACC
              , mdv.structure_id STRUCTURE_CAT
              , mdv.category_set_id c_category_set_id
              , flo1.meaning c_yes
              , flo2.meaning c_no
              FROM gl_sets_of_books gsb
              , financials_system_parameters fsp
              , mtl_default_sets_view mdv
              , fnd_lookups flo1
              , fnd_lookups flo2
              WHERE gsb.set_of_books_id = fsp.set_of_books_id
              AND mdv.functional_area_id = 2
              AND flo1.lookup_type = 'YES_NO'
              AND flo1.lookup_code = 'Y'
              AND flo2.lookup_type = 'YES_NO'
              AND flo2.lookup_code = 'N'
              ---------------------
              Query - Buyer
              -----------------------
              SELECT distinct(pov.vendor_name||papf.full_name)
              , papf.full_name Buyer
              , pov.vendor_name Vendor
              , pov.vendor_id
              , papf. person_id employee_id
              FROM PER_ALL_PEOPLE_F PAPF
              , po_vendors pov
              , po_headers poh
              WHERE poh.agent_id = papf.person_id
              AND poh.vendor_id = pov.vendor_id
              AND poh.type_lookup_code in ('STANDARD','BLANKET','PLANNED')
              AND nvl(poh.closed_code,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
              AND nvl(poh.cancel_flag,'N') = 'N'
              /* AND papf.full_name = nvl(:P_buyer,papf.full_name) */
              /* Bug#2453022 Commented out the above condition and replaced
              it as follows as the user parameter P_Buyer will now return the id and not the name */
              AND papf.person_id = nvl(:P_buyer,papf.person_id)
              AND pov.vendor_name BETWEEN nvl(:P_vendor_from,pov.vendor_name)
              AND nvl(:P_vendor_to,pov.vendor_name)
              AND EXISTS (select 'x'
              from po_lines pol
              , po_line_locations pll
              , po_releases por
              where pol.po_header_id = poh.po_header_id
              and pol.po_line_id = pll.po_line_id
              and nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
              and nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
              and nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
              and nvl(pll.cancel_flag,'N') = 'N'
              and nvl(pol.cancel_flag,'N') = 'N'
              and nvl(por.cancel_flag,'N') = 'N'
              and pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED')
              and pll.po_release_id = por.po_release_id(+))
              AND PAPF.EMPLOYEE_NUMBER IS NOT NULL
              AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND
              PAPF.EFFECTIVE_END_DATE
              AND DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE',
              HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F',PAPF.PERSON_ID,
              PAPF.PERSON_TYPE_ID,
              PAPF.EMPLOYEE_NUMBER,PAPF.APPLICANT_NUMBER )) = 'TRUE'
              AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF.BUSINESS_GROUP_ID ,
              HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF.BUSINESS_GROUP_ID
              ORDER BY papf.full_name
              , pov.vendor_name

              --------------
              Query - PO
              ----------------
              SELECT distinct(pol.line_num) Line
              , poh.segment1 ||decode(por.release_num,null,'','-')|| por.release_num PO_Number_Release
              , poh.currency_code C_CURRENCY
              , plt.line_type Line_Type
              , &P_FLEX_CAT C_FLEX_CAT
              , &P_FLEX_ITEM C_FLEX_ITEM
              , pol.item_revision Rev
              , pol.item_description Description
              , pol.po_header_id
              , pol.po_line_id
              , poh.vendor_id
              , poh.agent_id
              , psp.manual_po_num_type
              , poh.segment1
              , por.release_num
              , nvl(por.po_release_id,-1) release_id
              FROM po_line_locations pll
              , mtl_system_items msi
              , mtl_categories mca
              , po_lines pol
              , po_releases por
              , po_headers poh
              , po_line_types plt
              , po_system_parameters psp
              WHERE poh.po_header_id = pol.po_header_id
              AND pol.po_line_id = pll.po_line_id
              AND pll.po_release_id = por.po_release_id(+)
              AND pol.line_type_id = plt.line_type_id
              AND pol.item_id = msi.inventory_item_id(+)
              AND msi.organization_id(+) = :c_organization_id
              AND pol.category_id = mca.category_id
              AND nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
              AND nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
              AND nvl(poh.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
              AND nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
              AND nvl(pll.cancel_flag,'N') = 'N'
              AND nvl(pol.cancel_flag,'N') = 'N'
              AND nvl(poh.cancel_flag,'N') = 'N'
              AND nvl(por.cancel_flag,'N') = 'N'
              AND pll.shipment_type in ('STANDARD', 'BLANKET', 'SCHEDULED')
              ORDER BY decode(psp.manual_po_num_type,'NUMERIC',
              --bug#3614924
              decode(rtrim(poh.segment1,'0123456789'),NULL,to_number(poh.segment1))
              , null)
              , decode(psp.manual_po_num_type,'NUMERIC',null, poh.segment1)
              , por.release_num
              , pol.line_num

              ---------------------------
              Query - Shipment
              ----------------------------
              SELECT pll.shipment_num Shipment
              , nvl(pll.promised_date,pll.need_by_date) P_Date
              , pol.unit_meas_lookup_code Unit
              , DECODE (POL.order_type_lookup_code, /* <SERVICES FPJ> */
              'RATE', PLL.amount,
              'FIXED PRICE', PLL.amount,
              PLL.quantity) Ordered
              , DECODE (POL.order_type_lookup_code, /* <SERVICES FPJ> */
              'RATE', PLL.amount_received,
              'FIXED PRICE', PLL.amount_received,
              PLL.quantity_received) Received
              , DECODE (POL.order_type_lookup_code, /* <SERVICES FPJ> */
              'RATE', PLL.amount_billed,
              'FIXED PRICE', PLL.amount_billed,
              PLL.quantity_billed) Billed
              , pll.price_override Unit_Price
              , DECODE (POL.order_type_lookup_code, /* <SERVICES FPJ> */
              'RATE', (PLL.amount - NVL(PLL.amount_received, 0))/
              DECODE(NVL(PLL.amount, 0), 0, 1, PLL.amount),
              'FIXED PRICE', (PLL.amount - NVL(PLL.amount_received, 0))/
              DECODE(NVL(PLL.amount, 0), 0, 1, PLL.amount),
              (NVL(PLL.quantity, 0) - NVL(PLL.quantity_received, 0))/
              DECODE (NVL(PLL.quantity, 0), 0, 1, PLL.quantity)) * 100 Percent_Due
              , plc.displayed_field Open_For
              , pll.po_line_id
              , nvl(pll.po_release_id,-1) join_release_id
              FROM po_line_locations pll
              , po_lines pol
              , po_lookup_codes plc
              WHERE pol.po_line_id = pll.po_line_id
              AND nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
              AND nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
              AND nvl(pll.cancel_flag,'N') = 'N'
              AND nvl(pol.cancel_flag,'N') = 'N'
              AND plc.lookup_type = 'DOCUMENT STATE'
              AND plc.lookup_code = nvl(pll.closed_code, 'OPEN')
              AND pll.shipment_type in ('STANDARD', 'BLANKET', 'SCHEDULED')
              ORDER BY pll.shipment_num
              • 4. Re: Return nothing when run Open Purchase order report(by Buyer)
                760914
                hi S.P DASH,
                The PO is in 'Approved', the status is 'Open'. It is Standard PO.
                For the Query, becuase I do not have all the access to all tables. So just check effective status for buyer, vendor and PO.
                Seems they are all active.
                Is there any setup I need to check before I run the report, Open Purchase order report(by Buyer) ?

                Thank you very much for your reply.

                Kyla
                • 5. Re: Return nothing when run Open Purchase order report(by Buyer)
                  S.PDASH
                  Hi Kyla,
                  Even the PO User guide does not say much about this report, I mean the pre-requisite setup required.

                  It says :
                  "The Open Purchase Orders Report (by Buyer) lists all or specific open purchase orders that relate to buyers. Purchasing lets you specify the
                  open purchase orders you want to review. *An open purchase order is one that references an item that your supplier has not yet fully billed or*
                  *received.* The report excludes closed, final closed, and cancelled orders."

                  Could you please check, setup --> Purchasing --> Document Types (standard Purchase order) .. Security level is set as : Public & Acess level is set as : Full.

                  I will recommend you to raise a SR with Oracle for your issue.

                  Sorry for not able to help you much on this report issue.

                  Regards,
                  S.P DASH
                  • 6. Re: Return nothing when run Open Purchase order report(by Buyer)
                    760914
                    S.P DASH, thanks very much. If I get any solution for this, I will update it.