2 Replies Latest reply on Mar 20, 2008 5:39 AM by 608922

    ASL Attrbiutes

    584849
      Hi,

      I need to get the PO number, the item number and their corresponding Approved Supplier name which has a record populated in the Attributes form.
      Navigation:
      Approved Supplier List->Query for Item->Attributes(source documents tab)

      I have come down to this query:
      SELECT DISTINCT
      hou.name operating_unit
      ,poh.segment1 po_num
      ,pol.line_num po_line_num
      ,poh.currency_code
      ,msi.segment1 item_num
      ,pol.unit_price po_unit_price
      ,pov.vendor_name
      from
      po.PO_ASL_ATTRIBUTES attr
      ,po.PO_ASL_DOCUMENTS doc
      ,po.PO_APPROVED_SUPPLIER_LIST asl
      ,po.po_vendors pov
      ,inv.mtl_system_items_b msi
      ,po.po_headers_all poh
      ,po.po_lines_all pol
      ,hr.hr_all_organization_units hou
      where
      asl.asl_id = attr.asl_id
      and asl.asl_id = doc.asl_id
      and msi.INVENTORY_ITEM_ID=pol.ITEM_ID
      and asl.VENDOR_ID = pov.vendor_id
      and msi.INVENTORY_ITEM_ID = asl.ITEM_ID
      and msi.organization_id = 1
      and poh.PO_HEADER_ID = doc.DOCUMENT_HEADER_ID
      and pol.po_line_id(+) = doc.DOCUMENT_LINE_ID
      and poh.type_lookup_code = 'BLANKET'
      and hou.ORGANIZATION_ID = poh.org_id
      and nvl(pol.cancel_flag, 'N') = 'N'
      and asl.ITEM_ID=pol.ITEM_ID

      But when I run this query for a particular item, I am getting 2 records, which is nto correct. Both the records are the approved Suppliers, but only one of them has a row populated in the Approved Supplier List->Query for Item->Attributes(source documents tab) form.

      Please let me know.

      Thanks and Regards,
      JA
        • 1. Re: ASL Attrbiutes
          628722
          The ASL you are talking about is it a Local ASL or a Global ASL..

          And as far as I remember we have something as using_organization_id which determines the Inv Org for which this ASL is defined.

          If it is -1 its a Global ASL, else if its some other value its a Local ASL.

          The PO_ASL_DOCUMENTS also has the USING_ORGANIZATION_ID column.

          So by adding USING_ORGANIZATION_ID in the select clause you can check that if you are getting two rows because of different USING_ORGANIZATION_ID.
          • 2. Re: ASL Attrbiutes
            608922
            Remove the outer join in your queyr and check if it works?

            and pol.po_line_id = doc.DOCUMENT_LINE_ID