1 Reply Latest reply on Oct 12, 2013 8:54 PM by Cuauhtemoc Amox

    query returns multiple rows

    Azadare M

      HI All,

      My query returns multiple rows...below is query:



      SELECT DISTINCT pha.po_header_id, pha.agent_id

                          INTO l_po_header_id, l_agent_id

                          FROM po_lines_all pla,

                               po_headers_all pha,

                               po_asl_documents pad,

                               po_approved_supplier_list pasl

                         WHERE     pad.asl_id = pasl.asl_id

                               AND pha.po_header_id = pad.document_header_id

                               AND pha.type_lookup_code = 'BLANKET'

                               AND pha.authorization_status = 'APPROVED'

                               AND pasl.item_id = pla.item_id

                               AND pla.po_line_id = pad.document_line_id

                               AND NVL (pha.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')

                               AND NVL (pla.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')

                               AND NVL (pha.cancel_flag, 'N') = 'N'

                               AND NVL (pha.user_hold_flag, 'N') = 'N'

                               AND NVL (pha.frozen_flag, 'N') = 'N'

                               AND NVL (pla.cancel_flag, 'N') = 'N'

                               --and pad.using_organization_id = i.using_organization_id

                               AND (   pad.using_organization_id = -1

                                    OR pad.using_organization_id = :jdsu_cmpo_qship.organization_id)

                               AND pasl.item_id = :jdsu_cmpo_qship.inventory_item_id

                               AND TRUNC (SYSDATE) BETWEEN TRUNC (pha.start_date) AND TRUNC (pha.end_date)

                               AND pasl.asl_id = i.asl_id;



      This item is assigned to both organizations 170 and 291 for CM_NAME

      I assume if I filter the PO# for ASL enabled items based on OU of the responsibility(for example 290 for responsibility "CM User – 290"),


      Could any one tell me the exact condition from which I can get only the result for OU based?