0 Replies Latest reply on Aug 22, 2014 3:03 PM by ora_nut

    Is it possible to find which responsibility created a requisition?

    ora_nut

      Hello,

       

      I'm trying to write a query that returns user, requisition and responsibility information.

      (For example, display a count of requisitions created per responsibility for a given user).

       

      I have a query that is somewhere close, but it's not linking the responsibility with the requisition, as it's returning a line for each responsibility that the user has rather than just the one used to create the requisition. Does anyone know of a way I could do this?

       

      Here's my query so far:

       

      SELECT  frt.responsibility_name,

          FU_created.user_name,

          papf.first_name,

          papf.last_name,

          FU_assigned.description user_desc,

          count(prh.segment1) orders_created   

      FROM po_requisition_headers_all prh,

                wf_item_activity_statuses wias,

                fnd_responsibility_tl frt,

                wf_user_roles wur,

                fnd_user FU_assigned,

                fnd_user FU_created,

                per_all_people_f papf,

                fnd_user_resp_groups furg,

                fnd_responsibility fr

      WHERE  prh.wf_item_type=wias.item_type

      AND prh.wf_item_key=wias.item_key

      AND wur.role_orig_system='FND_RESP'

      AND wur.expiration_date IS NULL

      AND FU_created.user_id=prh.created_by

      AND FU_created.user_name=wur.user_name

      AND wur.role_orig_system_id=frt.responsibility_id

      AND fu_created.user_id = furg.user_id

      AND furg.responsibility_id = fr.responsibility_id

      AND frt.responsibility_id = fr.responsibility_id

      AND FU_assigned.user_name = wias.assigned_user

      AND papf.business_group_id = 3124

      AND FU_created.employee_id = papf.person_id

      AND prh.creation_date > (sysdate - 365)

      group by frt.responsibility_name,

          FU_created.user_name,

          papf.first_name,

          papf.last_name,

          FU_assigned.description

          order by FU_assigned.description

       

      Thanks in advance,

      Hazel