APPS.PA_PROJECTS_MAINT_UTILS SQL Statements

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

Line: 29

          SELECT organization_id
          INTO x_organization_id
          FROM hr_organization_units
          WHERE organization_id = p_organization_id;

Line: 37

          SELECT organization_id
          INTO x_organization_id
          FROM hr_organization_units
          WHERE name  = p_name;

Line: 86

          SELECT project_status_code
          INTO x_project_status_code
          FROM pa_project_statuses
          WHERE project_status_code = p_project_status_code;

Line: 94

          SELECT project_status_code
          INTO x_project_status_code
          FROM pa_project_statuses
          WHERE project_status_name = p_project_status_name;

Line: 151

          SELECT customer_id
          INTO x_customer_id
          FROM ra_customers
          WHERE customer_id = p_customer_id;

Line: 157

          SELECT cust_acct.cust_account_id
                INTO x_customer_id
          FROM
                hz_parties party,
                hz_cust_accounts cust_acct
          WHERE
                cust_acct.party_id = party.party_id
            and cust_acct.cust_account_id = p_customer_id;

Line: 174

          SELECT customer_id
          INTO x_customer_id
          FROM ra_customers
          WHERE customer_name = p_customer_name;

Line: 180

          SELECT cust_acct.cust_account_id
            INTO x_customer_id
          FROM
                hz_parties party,
                hz_cust_accounts cust_acct
          WHERE
                cust_acct.party_id = party.party_id
            and substrb(party.party_name,1,50) = p_customer_name;

Line: 252

            select  probability_member_id
            from    pa_probability_members
            where probability_list_id =
                 (select probability_list_id from pa_project_types where
                  project_type = p_project_type)
            and probability_percentage = p_probability_percentage;

Line: 261

            select  probability_member_id
        from    pa_probability_members
        where probability_list_id =
          (select probability_list_id from pa_project_types_all
           where project_type = p_project_type
           and org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID )
        and probability_percentage = p_probability_percentage;

Line: 272

          SELECT probability_member_id
          INTO x_probability_member_id
          FROM pa_probability_members
          WHERE probability_member_id = p_probability_member_id;

Line: 334

            select  probability_member_id
            into x_probability_member_id
            from    pa_probability_members
            where probability_list_id =
                 (select probability_list_id from pa_project_types where
                  project_type = p_project_type)
            and probability_percentage = p_probability_percentage;

Line: 342

            select  probability_member_id
            into x_probability_member_id
            from    pa_probability_members
            where probability_list_id =
                 (select probability_list_id from pa_project_types_all where
                  project_type = p_project_type
              and org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID ) --MOAC Changes: Bug 4363092: removed nvl usage with org_id
            and probability_percentage = p_probability_percentage;

Line: 352

         SELECT probability_member_id
         INTO x_probability_member_id
         FROM pa_probability_members
         WHERE probability_list_id = p_probability_list_id
         AND probability_percentage = p_probability_percentage;

Line: 412

          SELECT calendar_id
          INTO x_calendar_id
          FROM jtf_calendars_b --Used _b table:Bug 4352162
          WHERE calendar_id = p_calendar_id;

Line: 420

          SELECT calendar_id
          INTO x_calendar_id
          FROM jtf_calendars_vl
          WHERE calendar_name = p_calendar_name;

Line: 467

Select PPP.RESOURCE_SOURCE_ID
FROM PA_PROJECT_PARTIES         PPP  ,
     PA_PROJECT_ROLE_TYPES      PPRT
WHERE
    PPP.PROJECT_ID                      = c_project_id
AND PPP.PROJECT_ROLE_ID                 = PPRT.PROJECT_ROLE_ID
AND PPRT.PROJECT_ROLE_TYPE              ='PROJECT MANAGER'
AND trunc(sysdate)  between trunc(PPP.start_date_active)
AND                         NVL(trunc(PPP.end_date_active),sysdate)
AND ppp.object_type = 'PA_PROJECTS'
AND ppp.object_id = c_project_id
AND resource_type_id = 101;

Line: 515

Select PE.full_name
FROM PA_EMPLOYEES PE
WHERE PE.PERSON_ID = p_person_id;

Line: 522

Select PE.full_name
FROM per_all_people_f PE
WHERE PE.PERSON_ID = p_person_id
  AND trunc(sysdate) between PE.EFFECTIVE_START_DATE AND PE.EFFECTIVE_END_DATE; --Included by avaithia for Bug 3448680

Line: 564

Select PPC.customer_id, NVL(PPC.customer_bill_split,0) bill_split, substrb(PCV.customer_name,1,50)
FROM PA_PROJECT_CUSTOMERS       PPC,
     PA_CUSTOMERS_V             PCV
WHERE PPC.project_id    = c_project_id
AND   PPC.customer_id   = PCV.customer_id
ORDER BY bill_split DESC, customer_name;

Line: 605

Select PPC.customer_id, NVL(PPC.customer_bill_split,0) bill_split, substrb(PCV.customer_name,1,50)
FROM PA_PROJECT_CUSTOMERS       PPC,
     PA_CUSTOMERS_V             PCV
WHERE PPC.project_id    = c_project_id
AND   PPC.customer_id   = PCV.customer_id
ORDER BY bill_split DESC, customer_name;

Line: 641

     SELECT '1'
     FROM pa_expenditure_items pai, --Bug#3088387pa_tasks t,
          pa_cost_distribution_lines pcd
     WHERE pai.project_id=c_project_id AND --Bug#3088387t.task_id AND
           pai.expenditure_item_id=pcd.expenditure_item_id ;

Line: 650

     SELECT '1'
     FROM pa_draft_revenues
     WHERE project_id=p_project_id;

Line: 656

     SELECT '1'
     FROM pa_draft_invoices
     WHERE project_id=p_project_id;

Line: 701

PROCEDURE CHECK_CLASS_CATG_CAN_DELETE (p_object_id     NUMBER,
                                       p_object_type   VARCHAR2,
                                       p_class_category VARCHAR2,
                                       x_return_status  OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
                                       x_error_msg_code OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
CURSOR class_delrec (c_class_category VARCHAR2)
IS
SELECT  autoaccounting_flag
FROM   pa_class_categories
WHERE  class_category = c_class_category;

Line: 719

  SELECT project_id
  INTO l_project_id
  FROM pa_tasks
  WHERE task_id = p_object_id;

Line: 755

                            p_procedure_name => 'CHECK_CLASS_CATG_CAN_DELETE',
                            p_error_text     => SUBSTRB(SQLERRM,1,240));

Line: 758

END CHECK_CLASS_CATG_CAN_DELETE;

Line: 789

SELECT 'x'
FROM PA_PROJECT_CLASSES
WHERE object_id     = c_object_id
AND   object_type   = c_object_type
AND   class_category = c_class_category
AND   class_code     = c_class_code;

Line: 844

SELECT 'x'
FROM PA_PROJECT_CLASSES_V PAC,
     PA_CLASS_CATEGORIES  PCC
WHERE PAC.object_id = c_object_id AND
PAC.object_type = c_object_type AND
PAC.class_category = c_class_category AND
PAC.class_category = PCC.class_category AND
PCC.pick_one_code_only_flag = 'Y';

Line: 899

SELECT created_from_project_id
FROM PA_PROJECTS
WHERE project_id = c_project_id;

Line: 905

SELECT 'x'
FROM PA_OVERRIDE_FIELDS_V
WHERE pa_source_template_id = c_created_from_project_id
AND   pa_field_name            = 'CLASSIFICATION'
AND   UPPER(type)           = c_class_category;

Line: 964

SELECT project_system_status_code
FROM   PA_PROJECT_STATUSES
WHERE  project_status_code = c_project_status_code;

Line: 1013

SELECT project_type_class_code
FROM pa_project_types
WHERE project_type = l_project_type;

Line: 1056

 select business_group_id,
        org_id
   from pa_implementations;

Line: 1061

 select jobs.job_group_id
   from per_job_groups           jobs
  where jobs.business_group_id = p_business_group_id
    and jobs.internal_name = 'HR_'||to_char(jobs.business_group_id);

Line: 1097

Select bill_job_group_id,
       labor_std_bill_rate_schdl,
       non_labor_std_bill_rate_schdl
from pa_projects
where project_id = c_project_id;

Line: 1107

SELECT 'Y' FROM pa_std_bill_rate_schedules
 WHERE SCHEDULE_TYPE = 'JOB'
   AND JOB_GROUP_ID  = c_bill_job_group_id
   AND (STD_BILL_RATE_SCHEDULE = c_lab_std_bill_rate_schdl
        or STD_BILL_RATE_SCHEDULE = c_non_lab_std_bill_rate_schdl);

Line: 1157

SELECT created_from_project_id
FROM PA_PROJECTS_ALL
WHERE project_id = p_project_id;

Line: 1163

SELECT 'x'
FROM PA_OPTIONS OPT1, PA_PROJECT_OPTIONS OPT2
/* Commented for Bug 2499051
, PA_PROJECTS_ALL PROJ
*/
WHERE opt1.option_code          = opt2.option_code
AND   opt1.OPTION_FUNCTION_NAME = p_option_code
AND   opt2.project_id           = p_template_id;

Line: 1231

SELECT sum(pc.code_percentage)
FROM PA_PROJECT_CLASSES pc
WHERE pc.object_id = p_object_id
AND   pc.object_type = p_object_type
AND   pc.class_category = p_class_category;

Line: 1239

SELECT pc.class_category, pc.code_percentage
FROM   PA_PROJECT_CLASSES pc
WHERE  rowid = p_rowid;

Line: 1301

SELECT 'Y'
FROM PA_VALID_CATEGORIES_V
WHERE class_category = c_class_category
AND   object_type_id = c_object_type_id;

Line: 1348

   SELECT allow_percent_flag
   INTO l_allow_percent_flag
   FROM pa_class_categories
   WHERE class_category = p_class_category;

Line: 1386

SELECT pcc.class_category, mandatory_flag
FROM   pa_class_categories pcc,
       pa_project_classes ppc
WHERE pcc.class_category <> ppc.class_category
AND   ppc.object_id = c_object_id
AND   ppc.object_type = c_object_type
AND  trunc(sysdate) between trunc(pcc.start_date_active)
     and trunc(nvl(pcc.end_date_active, sysdate));

Line: 1397

SELECT 1
FROM PA_VALID_CATEGORIES vc,
     PA_PROJECTS_ALL ppa,
     PA_PROJECT_TYPES_ALL ppta
WHERE vc.mandatory_flag = 'Y'
AND  vc.class_category = c_class_category
AND  ppa.project_id = p_object_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  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));

Line: 1480

          SELECT currency_code
          INTO x_agreement_currency
          FROM fnd_currencies --Used base table:Bug 4352162
          WHERE currency_code = p_agreement_currency
            AND nvl(enabled_flag, 'Y') = 'Y'
            AND sysdate between decode(start_date_active, null, sysdate, start_date_active)
                            AND decode (end_date_active, null, sysdate, end_date_active);

Line: 1491

          SELECT currency_code
          INTO x_agreement_currency
          FROM fnd_currencies_vl
          WHERE name = p_agreement_currency_name
            AND nvl(enabled_flag, 'Y') = 'Y'
            AND sysdate between decode(start_date_active, null, sysdate, start_date_active)
                            AND decode (end_date_active, null, sysdate, end_date_active);

Line: 1551

          SELECT organization_id
          INTO x_agreement_org_id
          FROM pa_organizations_project_v
          WHERE organization_id = p_agreement_org_id;

Line: 1559

          SELECT organization_id
          INTO x_agreement_org_id
          FROM pa_organizations_project_v
          WHERE name = p_agreement_org_name;

Line: 1606

SELECT class_code, code_percentage
FROM   PA_PROJECT_CLASSES
WHERE  object_id = c_object_id
AND    object_type = c_object_type
AND    class_category = c_class_category
ORDER BY class_code;

Line: 1664

SELECT sum(code_percentage)
FROM   PA_PROJECT_CLASSES
WHERE  object_id = p_object_id
AND    object_type = p_object_type
AND    class_category = p_class_category;

Line: 1676

SELECT total_100_percent_flag
FROM   PA_VALID_CATEGORIES_V vc,
       PA_PROJECTS_ALL ppa,
       PA_PROJECT_TYPES_ALL ppta
WHERE  ppa.project_id = p_object_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    vc.class_category = p_class_category;

Line: 1691

SELECT total_100_percent_flag
  FROM  PA_CLASS_CATEGORIES
  WHERE object_type     = p_object_type
  AND   class_category  = p_class_category;

Line: 1748

SELECT ppta.project_type_id
FROM   PA_PROJECT_TYPES_ALL ppta,
       PA_PROJECTS_ALL ppa
WHERE  ppa.project_id = p_object_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

Line: 1784

   SELECT message_name
     FROM FND_NEW_MESSAGES
    WHERE message_text = l_exception
/* added for Bug 2634995 */
    AND  application_id = fnd_global.resp_appl_id
    AND language_code = userenv('LANG');

Line: 1792

   SELECT CLASS_CATEGORY, CLASS_CODES, TOTAL_PERCENTAGE, EXCEPTIONS, SORT_ORDER
     FROM pa_project_class_totals_v
    WHERE project_id = p_project_id
      AND SORT_ORDER IN ('A','B')
    ORDER BY sort_order;

Line: 1855

  SELECT count(*)
  FROM sys.dual
  WHERE EXISTS
  (SELECT NULL
   FROM   pa_expenditure_items_all pai,
          pa_tasks t,
          pa_cost_distribution_lines_all pcd
   WHERE  pai.task_id = t.task_id
   AND    pai.expenditure_item_id = pcd.expenditure_item_id
   AND    t.project_id = p_project_id);

Line: 1868

  SELECT template_flag
  FROM   pa_projects_all
  WHERE  project_id = p_project_id;

Line: 1874

  SELECT carrying_out_organization_id
  FROM   PA_PROJECTS_ALL
  WHERE  project_id = p_project_id;

Line: 1948

  SELECT probability_member_id, expected_approval_date
  FROM   PA_PROJECTS_ALL
  WHERE  project_id = p_project_id;

Line: 1995

       SELECT class_code_id
       FROM   pa_class_codes
       WHERE  class_category  = p_classcategory
         AND  class_code      = p_classcode_name;

Line: 2011

          SELECT class_code_id
          INTO   x_classcode_id
          FROM  pa_class_codes
          WHERE class_category = p_classcategory
            AND class_code_id  = p_classcode_id;

Line: 2056

             SELECT class_code_id
             INTO   x_classcode_id
             FROM   pa_class_codes
             WHERE  class_category  = p_classcategory
             AND    class_code      = p_classcode_name;

Line: 2114

       SELECT class_category_id
       FROM   pa_class_categories
       WHERE  class_category  = p_class_category_name;

Line: 2129

          SELECT class_category_id
          INTO   x_class_category_id
          FROM  pa_class_categories
          WHERE class_category_id = p_class_category_id;

Line: 2173

             SELECT class_category_id
             INTO   x_class_category_id
             FROM   pa_class_categories
             WHERE  class_category  = p_class_category_name;

Line: 2225

SELECT mandatory_flag
FROM   pa_class_categories
WHERE  class_category = p_class_category;

Line: 2230

SELECT ps.project_system_status_code
FROM   pa_project_statuses ps, pa_projects_all pp
WHERE  pp.project_id = p_project_id
AND    pp.project_status_code = ps.project_status_code;

Line: 2237

SELECT mandatory_flag
  FROM pa_valid_categories pvc
 WHERE class_category = p_class_category
   AND EXISTS  (SELECT 'X'
                  FROM pa_project_types_all pta
         WHERE pta.project_type_id = pvc.object_type_id
           AND EXISTS  (SELECT 'X'
                          FROM pa_projects_all ppa
                     WHERE pta.project_type = ppa.project_type
                   AND project_id = p_project_id));