4 Replies Latest reply on Jan 6, 2011 4:51 AM by User12642515-Oracle

    How to know if an Item has a sourcing rule connected to it from backend?

    514497
      Hi All,

      I have a requirement from user requesting to pull items which have a primary vendor and a sourcing rule attached to it, i.e., an Item can be used in more than one blanket PO's. But I need to know which is the primary vendor/ vendor site which has a Blanket PO attached to it.

      From front end navigation is Pusrchasing Super User responsibility
      Supply Base --> Approved Supplier List
      Query for the item
      In the Key attributes all the suppliers and supplier sites are listed for that item. Among which only one supplier/supplier site has the attributes (source documents like Blanket PO, line num etc)attached to it. I need to consider only that supplier for that Item which will be a primary vendor.

      How can I do that from the back end?

      Any ideas please !!!!
      Thanks,
      Prathima
        • 1. Re: How to know if an Item has a sourcing rule connected to it from backend?
          NitinSDarji
          Hi,

          Following tables should have the information:
          po_approved_supplier_list (Approved Supplier list)

          po_asl_attributes (ASL Attributes, you can joint it with po_approved_supplier_list using asl_id)

          po_asl_documents (ASL Documents, you can joint it with po_approved_supplier_list using asl_id)

          Hope this helps.

          Regards,
          Nitin Darji
          • 2. Re: How to know if an Item has a sourcing rule connected to it from backend?
            514497
            Hi,

            I did join all those tables, it didnot help. Below is the query I am using.

            FROM
            po.po_headers_all poh
            ,po.po_lines_all pol
            ,po.po_vendors pov
            ,hr.hr_all_organization_units hou
            ,inv.mtl_system_items_b msi
            ,bom.cst_item_costs cost
            ,po.po_vendor_sites_all povs
            --,apps.po_asl_documents_v asldoc
            ,po.po_asl_attributes aslattr
            ,po.po_asl_documents asldoc
            ,po.po_approved_supplier_list aslsupp
            WHERE
            poh.po_header_id = pol.po_header_id
            and pov.vendor_id = poh.vendor_id
            and poh.org_id = hou.organization_id
            and hou.organization_id = :p_operating_unit
            and poh.currency_code = :p_currency
            --and poh.creation_date  between :po_creation_date_from and :po_creation_date_to
            and poh.type_lookup_code = 'BLANKET'
            and msi.inventory_item_id = pol.item_id
            and cost.INVENTORY_ITEM_ID = pol.ITEM_ID
            and pov.vendor_id = povs.vendor_id
            --and (round(cost.item_cost,5) - round((:p_rate * pol.unit_price),5)) <> 0
            and cost.organization_id = 1
            and msi.organization_id = 1
            and cost.cost_type_id in (3,1000) --- Pending cost type , Canadian Pending Cost Type
            and nvl(upper (pol.closed_code),'OPEN') not in('CANCELLED', 'CLOSED', 'FINALLY CLOSED', 'REJECTED')
            and nvl(upper (poh.closed_code),'OPEN') not in('CANCELLED', 'CLOSED', 'FINALLY CLOSED', 'REJECTED')
            and nvl(pol.cancel_flag, 'N') = 'N'
            --and msi.segment1 in (380440,329719,380446,380447,187345)
            and msi.segment1 = '329719'
            and aslsupp.asl_id = aslattr.asl_id (+)
            and aslsupp.asl_id = asldoc.asl_id (+)
            and aslsupp.using_organization_id = aslattr.using_organization_id (+)
            and aslsupp.using_organization_id = asldoc.using_organization_id (+)
            and aslsupp.VENDOR_SITE_ID = povs.VENDOR_SITE_ID
            and aslsupp.ITEM_ID = msi.INVENTORY_ITEM_ID
            --and po_approved_supplier_list.DISABLE_FLAG = 'N'
            and asldoc.DOCUMENT_HEADER_ID = poh.po_header_id
            and aslattr.ITEM_ID = msi.INVENTORY_ITEM_ID
            and asldoc.DOCUMENT_LINE_ID is not null
            and asldoc.DOCUMENT_LINE_ID = pol.PO_LINE_ID
            and aslsupp.VENDOR_ID = pov.vendor_id (+)
            --and aslsupp.PRIMARY_VENDOR_ITEM is not null
            and pol.vendor_product_num = aslsupp.primary_vendor_item

            Am I missing any join?? I have been trying all the possible scenarios.

            Thanks,
            Prathima
            • 3. Re: How to know if an Item has a sourcing rule connected to it from backend?
              514497
              Can anyone throw some light on this please?

              Thanks,
              Prathima
              • 4. Re: How to know if an Item has a sourcing rule connected to it from backend?
                User12642515-Oracle
                HI,

                I have a requirement, API for loading sourcing rules in APPS and attaching the same to Assignment sets at item level..
                I found that ,these are the API's for that..
                MRP_SOURCING_RULE_PUB.PROCESS_SOURCING_RULE ,MRP_SRC_ASSIGNMENT_PUB.Process_Assignment
                Please provide me the code for that.
                Can you please send to my mail -- viiji.4you@gmail.com

                Thanks in advance...
                Vijaya