Deleting the Sourcing Rule Assignments API

 Active project Managers Query

SELECT DISTINCT hou.name org_name,

       ppa.name project_name,

       ppa.segment1 project_number,

       ppa.project_type,

   pe.full_name Project_Manager,

   amount_limit Purchase_Requisition_approval,

   pe.email_address,

       ppa.start_date,

       to_char(ppa.completion_date,'DD-MON-YYYY')completion_date ,

       ppa.closed_date,

       ppa.enabled_flag

  FROM apps.pa_project_parties ppp,

   apps.pa_projects_all ppa,

   apps.pa_project_role_types pprt,

   apps.per_all_people_f pe,

   apps.pa_project_assignments pa,

   apps.fnd_user u,

       apps.hr_operating_units hou,

  (SELECT paf.person_id,

  paf.assignment_type,

      pcr.amount_limit

     FROM apps.per_all_assignments_f paf,

      apps.per_jobs pj,

              apps.po_position_controls_all f,

      apps.po_control_groups_all g,

      apps.po_control_rules pcr

    WHERE TRUNC(SYSDATE) BETWEEN TRUNC(paf.effective_start_date) AND TRUNC(paf.effective_end_date)

      AND paf.primary_flag     = 'Y'

      AND NVL(paf.job_id, -99) = pj.job_id

          AND pj.job_id    = f.job_id

          AND paf.position_id    = f.position_id

          AND f.control_group_id   = g.control_group_id

          AND f.control_function_id = 8 --'Approve Purchase Requisitions'

  AND nvl(trunc(f.end_date),trunc(SYSDATE)) >= trunc(SYSDATE)

      AND g.enabled_flag       = 'Y'

      AND pcr.control_group_id = g.control_group_id

          AND nvl(pcr.inactive_date, sysdate + 1) > sysdate

  AND pcr.object_code      = 'DOCUMENT_TOTAL'

          AND g.org_id            IN (1)) PRD

  WHERE 1=1--ppa.project_id                                              = :p_project_id

 -- AND ppa.segment1 = '031-6072'

    AND DECODE(pa.assignment_id,NULL,pprt.meaning,pa.assignment_name) = 'Project Manager'

    AND ppa.project_status_code  NOT IN ('CLOSED','REJECTED')

    AND ppa.org_id IN (1)

    AND ppa.org_id   = hou.organization_id

    AND ppp.project_id                                                = ppa.project_id

    AND ppp.project_role_id                                           = pprt.project_role_id

    AND ppp.resource_source_id                                        = pe.person_id

    AND TRUNC(SYSDATE) BETWEEN TRUNC(ppp.start_date_active) AND NVL(TRUNC(ppp.end_date_active),SYSDATE)

    AND pe.effective_start_date  BETWEEN TRUNC(pe.effective_start_date) AND NVL(TRUNC(pe.effective_end_date),SYSDATE)

    AND NVL(pe.effective_end_date,SYSDATE)          >=TRUNC(SYSDATE)

    AND ppp.project_party_id            = pa.project_party_id(+)

AND NVL(prd.assignment_type,'-99') IN ('C',DECODE(DECODE(pe.current_employee_flag,'Y','Y',DECODE(pe.current_npw_flag,'Y','Y','N')),'Y','E', 'B'),'E', '-99')

AND ppp.resource_source_id          = prd.person_id(+)

AND u.employee_id (+)               = ppp.resource_source_id

AND ppp.object_type                 = 'PA_PROJECTS'

AND ppp.object_id                   = ppa.project_id

    AND nvl(trunc(ppa.closed_date), trunc(SYSDATE)) >= trunc(SYSDATE)

    AND TRUNC(SYSDATE) BETWEEN TRUNC(ppa.start_date) AND NVL(TRUNC(ppa.completion_date),SYSDATE)

ORDER BY hou.name,ppa.segment1;