APPS.PA_PROJECT_VERIFY_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 11

    SELECT enable_top_task_customer_flag
    FROM pa_projects_all
    WHERE project_id = x_project_id;

Line: 20

    SELECT nvl(DATE_EFF_FUNDS_CONSUMPTION,'N')
    FROM
    pa_projects_all
    WHERE project_id = x_project_id;

Line: 49

        SELECT NULL
        INTO dummy
        FROM sys.dual
        WHERE exists (
          SELECT NULL
          FROM    PA_PROJECT_CUSTOMERS
          WHERE   PROJECT_ID = x_project_id
          GROUP   BY PROJECT_ID
          HAVING SUM(CUSTOMER_BILL_SPLIT) = 100);

Line: 60

        SELECT NULL
        INTO dummy
        FROM PA_PROJECT_CUSTOMERS
        WHERE PROJECT_ID = x_project_id
        AND rownum < 2;

Line: 92

    SELECT NULL
    INTO dummy
    FROM sys.dual
    WHERE exists (
      SELECT NULL
      FROM    PA_PROJECT_CUSTOMERS CUST
      WHERE   CUST.PROJECT_ID = x_project_id
      AND     CUST.CUSTOMER_BILL_SPLIT > 0
      AND     NOT EXISTS (SELECT NULL
                  FROM    PA_PROJECT_CONTACTS CONT
                  WHERE   CONT.PROJECT_ID = x_project_id
                  AND     CONT.CUSTOMER_ID=  CUST.CUSTOMER_ID
                  AND     CONT.PROJECT_CONTACT_TYPE_CODE = 'BILLING'));

Line: 131

    SELECT NULL
    FROM    PA_VALID_CATEGORIES_V VC,
            PA_PROJECTS_ALL PPA,
            PA_PROJECT_TYPES_ALL PPTA
    WHERE   VC.MANDATORY_FLAG = 'Y'
    AND     PPA.PROJECT_ID = x_project_id
    AND     PPA.PROJECT_TYPE = PPTA.PROJECT_TYPE
    AND     nvl(PPA.ORG_ID, -99) = nvl(PPTA.ORG_ID, -99)
    AND     VC.OBJECT_TYPE_ID = PPTA.PROJECT_TYPE_ID
    AND     NOT EXISTS (SELECT NULL
                        FROM   PA_PROJECT_CLASSES PC
                       WHERE   PC.PROJECT_ID = x_project_id
                         AND   PC.CLASS_CATEGORY = VC.CLASS_CATEGORY);

Line: 148

    SELECT NULL
      FROM DUAL
     WHERE EXISTS
    (
    SELECT 1
    FROM   PA_CLASS_CATEGORIES cc,
           PA_VALID_CATEGORIES vc,
           PA_PROJECT_TYPES_ALL PPTA,
           PA_PROJECTS_ALL PPA
    WHERE  VC.CLASS_CATEGORY = CC.CLASS_CATEGORY
    AND    TRUNC(SYSDATE) BETWEEN TRUNC(CC.START_DATE_ACTIVE)
                              AND TRUNC(NVL(CC.END_DATE_ACTIVE, SYSDATE))
    AND    VC.OBJECT_TYPE_ID = PPTA.PROJECT_TYPE_ID
    AND    TRUNC(SYSDATE) BETWEEN TRUNC(PPTA.START_DATE_ACTIVE)
			      AND TRUNC(NVL(PPTA.END_DATE_ACTIVE, SYSDATE))
    AND    VC.MANDATORY_FLAG = 'Y'
    AND    PPA.PROJECT_ID = x_project_id
    AND    PPA.PROJECT_TYPE = PPTA.PROJECT_TYPE
    AND    PPA.ORG_ID = PPTA.ORG_ID --MOAC Changes: Bug 4363092: removed nvl usage with org_id
    AND    NOT EXISTS (SELECT NULL
                        FROM   PA_PROJECT_CLASSES PC
                       WHERE   PC.PROJECT_ID = x_project_id
                         AND   PC.CLASS_CATEGORY = VC.CLASS_CATEGORY)
    );

Line: 175

    SELECT NULL
      FROM DUAL
     WHERE EXISTS (SELECT 1
                     from PA_CLASS_CATEGORIES CC
                    WHERE CC.MANDATORY_FLAG = 'Y'
                      AND CC.OBJECT_TYPE = 'PA_PROJECTS'
                      AND CC.ALL_TYPES_VALID_FLAG = 'Y'
                      AND TRUNC(SYSDATE) BETWEEN TRUNC(CC.START_DATE_ACTIVE)
					     AND TRUNC(NVL(CC.END_DATE_ACTIVE, SYSDATE))
                      AND NOT EXISTS(SELECT   NULL
                                       FROM   PA_PROJECT_CLASSES PC
                                      WHERE   PC.PROJECT_ID = x_project_id
                                        AND   PC.CLASS_CATEGORY = CC.CLASS_CATEGORY)
    );

Line: 193

    This View PA_PROJECT_CLASS_TOTALS_V has two select statements joined by UNION
    The 1st select statement is for sort_order A and C / the 2nd select statement for B

    So,the query can be based directly on the base table as in 1st select statement of the view
    CURSOR C2
    IS
    SELECT NULL
    FROM   PA_PROJECT_CLASS_TOTALS_V
    WHERE  project_id = x_project_id
    AND    sort_order = 'A';

Line: 208

    SELECT NULL
    FROM PA_PROJECT_CLASSES
    WHERE  project_id = x_project_id
    AND    OBJECT_TYPE = 'PA_PROJECTS'
    AND    decode(PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(object_id,object_type, class_category, 'N'), NULL, 'C', 'A') = 'A'
    ;

Line: 283

      SELECT NVL(Start_Date,trunc(Sysdate)) FROM Pa_Projects_all -- Bug#3807805 : Modified Pa_Projects to Pa_Projects_all
      WHERE   PROJECT_ID = x_project_id;

Line: 286

     /* Added the following cursor instead of select statement to
        handle the "too many rows selected" condition.
        Bug fix for # 824266 */

    CURSOR c2 IS
     SELECT START_DATE_ACTIVE,END_DATE_ACTIVE
     FROM    PA_PROJECT_PLAYERS
     WHERE   PROJECT_ID = x_project_id
     AND     PROJECT_ROLE_TYPE = 'PROJECT MANAGER';

Line: 305

        SELECT TRUNC(Sysdate) INTO proj_start_date FROM Dual;

Line: 371

    SELECT 'x'  INTO dummy
    FROM PA_BUDGET_VERSIONS bv,
		    PA_BUDGET_TYPES bt
    WHERE
    bv.budget_type_code = bt.budget_type_code
    AND bt.budget_amount_code = 'R';

Line: 401

    SELECT 'x'  INTO dummy
    FROM PA_BUDGET_VERSIONS bv,
		    PA_BUDGET_TYPES bt
    WHERE
    bv.budget_type_code = bt.budget_type_code
    AND bt.budget_amount_code = 'C';

Line: 438

    SELECT NULL
    INTO dummy
    FROM sys.dual
    WHERE exists (
      select  NULL
      from    pa_events e
      ,       pa_event_Types et
      ,       pa_tasks t
      where   nvl(e.task_id,t.task_id) = t.task_id
	and	e.project_id = t.project_id
      and     e.event_type = et.event_Type
      and     t.project_id = x_project_id
      and     e.completion_date is not null
      having  sum(nvl(decode(et.event_type_classification,
                      'INVOICE REDUCTION',-e.bill_amount,
                                           e.bill_amount),0)) =
              (select sum(nvl(revenue,0))
              from pa_subbudgets s
              ,       pa_tasks t
              where   s.project_id = x_project_id
              and     s.budget_Type_code= 'DRAFT'
              and     s.task_id = t.task_id(+)
              and     t.task_id = t.top_task_id
              ));

Line: 469

        select to_char(sum(nvl(decode(et.event_type_classification,
                        'INVOICE REDUCTION', -e.bill_amount,
                                              e.bill_amount),0)))
        into x_eamt_token_value
        from    pa_events e
        ,       pa_event_Types et
        ,       pa_tasks t
        where   nvl(e.task_id, t.task_id) = t.task_id
        and	e.project_id = t.project_id
        and     e.event_Type = et.event_Type
        and     t.project_id = x_project_id
        and     e.completion_date is not null;