7 Replies Latest reply on Jan 27, 2011 7:05 AM by makdutakdu

    to get supplier name based on latest approved date

    makdutakdu
      hi

      in PO SUPER USER -Purchase Order Summary ,when we enter the item number in Item,rev field a new form Purchase Order Lines opens
      in this form there are many lines of PO numbers ,suppliers(vendors),approved date ,can we get the supplier name for which the approved date is most recent


      kindly guide
      thanking in advance
        • 1. Re: to get supplier name based on latest approved date
          S.PDASH
          Hi,
          Basically system picks up the list in Ascending order of PO when you mention the Item in the Purchase Order Summary form and query. Please check the code behind PO_LINES_INQ_V (view)...and check if changes can be done as per your business requirement but through Oracle seeded Purchase Order Summary form it is not possible to fullfill your requirement.

          Hope it helps.

          Kind Regards,
          S.P DASH
          • 2. Re: to get supplier name based on latest approved date
            704514
            Hi makdutakdu,

            In the Purchase Order Line go to Menu option: Folder > Show Field
            Look for field Approved Date and select it.

            Now you have the approved date in the screen if you click over the Prompt Approved Date you can order by desc or asc.

            Last thing you have to do is Save the folder, using save as in menu option Folder > Approved Date.

            Only ensure to uncheck Include Query.

            Please let me know if you have additional issues.
            • 3. Re: to get supplier name based on latest approved date
              makdutakdu
              hi

              in my system i can see the latest approved date wise but i have to add an inline query on the most recent vendor based on approved date to an already existing query
              meaning just the first row data(most recent supplier) should be captured in the query

              when i do the following query , i am taking an eg of the item_no 1020101001
              select msib.segment1,max(pha.approved_date) from
              po_headers_all pha,
              po_lines_all pla,
              po_vendors pv,
              mtl_system_items_b msib
              where
              pha.po_header_id = pla.po_header_id
              and pla.item_id = msib.inventory_item_id
              and pv.vendor_id=pha.vendor_id
              and msib.segment1='1020101001'
              --and pha.approved_date=max(pha.approved_date)
              group by 
              --item_id.
              msib.segment1
              this is the output i get 
              
              segment1           MAX(PHA.APPROVED_DATE)
              1020101001    1/9/2011 3:16:48 PM
              when i do the following query
              select msib.segment1,max(pha.approved_date) ,pv.vendor_name from
              po_headers_all pha,
              po_lines_all pla,
              po_vendors pv,
              mtl_system_items_b msib
              where
              pha.po_header_id = pla.po_header_id
              and pla.item_id = msib.inventory_item_id
              and pv.vendor_id=pha.vendor_id
              and msib.segment1='1020101001'
              --and pha.approved_date=max(pha.approved_date)
              group by 
              --item_id.
              msib.segment1
              ,pv.vendor_name
              segment1           MAX(PHA.APPROVED_DATE)  VENDOR_NAME
              
              1020101001    7/21/2008 9:09:20 AM   TRIZAC  ABU DHABI
              1020101001   1/9/2011 3:16:48 PM        SAUDI CEMENT COMPANY
              1020101001   2/14/2010 4:03:46 PM     UNION CEMENT NORCEM CO.
              1020101001  5/19/2010 3:08:32 PM      AS CIMENTO SANAYI VE TICARET A.S.
              i require the most recent vendor_name for a particular item ( i require the output as below)

              segment1 MAX(PHA.APPROVED_DATE) VENDOR_NAME


              1020101001 1/9/2011 3:16:48 PM SAUDI CEMENT COMPANY

              kindly guide me
              thanking in advance
              • 4. Re: to get supplier name based on latest approved date
                704514
                There are several ways you can do it.

                One of them is:

                Note the organization_id bind var, I think you should use it but it depends on your PO setup.
                select segment1, Max (approved_date), vendor_name from
                (select msib.segment1,pha.approved_date ,pv.vendor_name 
                from
                po_headers_all pha,
                po_lines_all pla,
                po_vendors pv,
                mtl_system_items_b msib
                where
                pha.po_header_id = pla.po_header_id
                and pla.item_id = msib.inventory_item_id
                and pv.vendor_id=pha.vendor_id
                and msib.segment1=:your_item
                and msib.organization_id=:your_org_id 
                order by 2 desc)
                where rownum=1
                group by 
                segment1
                ,vendor_name;
                • 5. Re: to get supplier name based on latest approved date
                  makdutakdu
                  hi

                  this is the orignal query
                  SELECT msi.organization_id, TO_CHAR (m_trans.transaction_date, 'YYYY') YEAR,
                         TO_CHAR (m_trans.transaction_date, 'YYYY-MM') y_period,MSI.MIN_MINMAX_QUANTITY,  MSI.MAX_MINMAX_QUANTITY,
                         msi.segment1 item_no, msi.segment2 part_num, msi.segment3 old_no,
                         msi.description item_description, msi.primary_unit_of_measure uom,
                         m_trans.primary_quantity * -1 trx_qty, cst.item_cost,
                         (SELECT SUM (q.transaction_quantity)
                            FROM mtl_onhand_quantities q
                           WHERE q.inventory_item_id = msi.inventory_item_id
                             AND q.organization_id = msi.organization_id
                             AND q.organization_id <> 103) on_hand
                    FROM mtl_material_transactions m_trans,
                         mtl_system_items msi,
                         cst_item_costs cst
                   WHERE m_trans.inventory_item_id = msi.inventory_item_id
                     AND m_trans.organization_id = msi.organization_id
                     AND msi.organization_id <> 103
                     AND m_trans.transaction_type_id IN (100, 35, 43, 33, 1, 17, 51, 1, 63)
                     AND msi.inventory_item_id(+) = cst.inventory_item_id
                     AND msi.organization_id(+) = cst.organization_id
                  to the above when i included max(approved_date) inorder to get latest vendor_name for item ,due to the group by ,vendor_name for all the dates were coming
                  which is why i thought of an inline query

                  when i try ur query
                  one row only comes but the value for approved_date remains blank

                  SEGMENT1 MAX(PHA.APPROVED_DATE) VENDOR_NAME
                  1020101001 SAUDI CEMENT COMPANY

                  kindly guide me on how to accomodate ur query inside the main one above

                  thanking in advance
                  • 6. Re: to get supplier name based on latest approved date
                    704514
                    Maybe it is null because that order has not been approved, add the condition
                    pha.approved_date is not null
                    • 7. Re: to get supplier name based on latest approved date
                      makdutakdu
                      hi

                      when i try like this with the bind variable commented,it comes only for one item ,can we have ot for all items and their respective recent venor_names
                      select segment1, Max (approved_date), vendor_name from
                      (select msib.segment1,pha.approved_date ,pv.vendor_name 
                      from
                      po_headers_all pha,
                      po_lines_all pla,
                      po_vendors pv,
                      mtl_system_items_b msib
                      where
                      pha.po_header_id = pla.po_header_id
                      and pla.item_id = msib.inventory_item_id
                      and pv.vendor_id=pha.vendor_id
                      and pha.approved_date is not null
                      --and msib.segment1=:your_item
                      --and msib.organization_id=:your_org_id 
                      order by 2 desc)
                      where rownum=1
                      group by 
                      segment1
                      ,vendor_name;
                      kindly guide

                      thanking in advance