APPS.FPA_MAIN_PROCESS_PVT SQL Statements

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

Line: 41

    SELECT
    PRTL.NAME,
    PCTL.NAME,
    CC.CLASS_CODE,
    PCC.CLASS_CATEGORY
    FROM
         FPA_AW_PC_INFO_V PCS,
         FPA_AW_PORTF_HEADERS_V PRTF,
         FPA_OBJECTS_TL PRTL,
         FPA_OBJECTS_TL PCTL,
         PA_CLASS_CATEGORIES PCC,
         PA_CLASS_CODES CC
    WHERE
    PCTL.ID = PCS.PLANNING_CYCLE AND
    PCTL.OBJECT = 'PLANNING_CYCLE' AND
    PRTL.ID = PCS.PORTFOLIO AND
    PRTL.OBJECT = 'PORTFOLIO' AND
    PCS.PC_CATEGORY = PCC.CLASS_CATEGORY_ID AND
    PCS.PORTFOLIO   = PRTF.PORTFOLIO AND
    PRTF.PORTFOLIO_CLASS_CODE = CC.CLASS_CODE_ID AND
    PCS.PLANNING_CYCLE = C_PC_ID AND
    PRTL.LANGUAGE = USERENV('LANG') AND -- Bug Ref # 6450301
    PCTL.LANGUAGE = USERENV('LANG');

Line: 83

     SELECT PLC.NAME, PLB.NAME
     INTO   L_PC_ATTRIBUTES_REC.COST_FIN_PLAN, L_PC_ATTRIBUTES_REC.BENEFIT_FIN_PLAN
     FROM   PA_FIN_PLAN_TYPES_TL PLC, PA_FIN_PLAN_TYPES_TL PLB
     WHERE
        FND_PROFILE.VALUE('PJP_FINANCIAL_PLAN_TYPE_COST') = PLC.FIN_PLAN_TYPE_ID AND
        FND_PROFILE.VALUE('PJP_FINANCIAL_PLAN_TYPE_BENEFIT') = PLB.FIN_PLAN_TYPE_ID AND
        PLC.LANGUAGE = USERENV('LANG') AND -- Bug Ref # 6450301
        PLB.LANGUAGE = USERENV('LANG');

Line: 246

                          aname    => 'FPA_PRTF_SELECTION_CATEGORY',
                          avalue   => fnd_profile.value('PJP_PORTFOLIO_CLASS_CATEGORY'));

Line: 545

SELECT item_type,item_key,root_activity
  FROM wf_items
 WHERE item_type = 'FPAPJP'
   AND (item_key  = 'FPAL'|| p_pc_id OR item_key  = 'FPAF'|| p_pc_id OR item_key = to_char(p_pc_id))
   AND end_date IS NULL;

Line: 596

   l_parameter_list.DELETE;

Line: 637

   SELECT DISTINCT u.user_name, prd.organization_id
     FROM pa_project_parties ppp,
          pa_project_role_types pprt,
          per_all_people_f pe,
          per_all_assignments_f prd,
          fnd_user u ,
      FPA_AW_PC_INFO_V pc
    WHERE ppp.resource_type_id = 101
      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 trunc(NVL(ppp.end_date_active,sysdate))
      AND trunc(sysdate) BETWEEN trunc(pe.effective_start_date)   AND trunc(pe.effective_end_date)
      AND ppp.resource_source_id = prd.person_id
      AND prd.primary_flag = 'Y'
      AND prd.assignment_type = 'E'
      AND trunc(sysdate) BETWEEN trunc(prd.effective_start_date)  AND trunc(prd.effective_end_date)
      AND u.employee_id = ppp.resource_source_id
      and trunc(sysdate) BETWEEN trunc(u.start_date)  AND trunc(NVL(u.end_date,sysdate))
      AND ppp.object_type = 'PJP_PORTFOLIO'
      AND ppp.object_id = pc.portfolio
      AND ((p_approver = 'Y' and p_analyst = 'Y' and pprt.project_role_type IN ('PORTFOLIO_APPROVER', 'PORTFOLIO_ANALYST')) OR
           (p_approver = 'Y' and p_analyst = 'N' and pprt.project_role_type = 'PORTFOLIO_APPROVER') OR
           (p_approver = 'N' and p_analyst = 'Y' and pprt.project_role_type = 'PORTFOLIO_ANALYST'))
      AND pc.planning_cycle = p_pcid;

Line: 663

   SELECT DISTINCT ppp.user_name, ppp.organization_id -- select to fetch persons attached to the role defined in the access list
     FROM pa_dist_list_items i,
          pa_project_parties_v ppp,
          pa_object_dist_lists podl,
          fnd_user u
    WHERE i.recipient_type = 'PROJECT_ROLE'
      AND i.list_id = podl.list_id
      AND podl.object_id = p_pcid
      AND podl.object_type = 'PJP_PLANNING_CYCLE'
      AND ppp.resource_type_id = 101
      AND ppp.project_role_id = i.recipient_id
      and ppp.user_id IS NOT NULL -- To prevent invalid user name error during workflow
      and ppp.user_id = u.user_id
      and trunc(sysdate) BETWEEN trunc(u.start_date)  AND trunc(NVL(u.end_date,sysdate))
    UNION
   SELECT DISTINCT u.user_name, a.organization_id    -- select to fetch persons defined in the access list
   FROM pa_dist_list_items i,
        pa_object_dist_lists podl,
        hz_parties hzp,
        fnd_user u,
        per_all_assignments_f a, per_all_people_f p
   WHERE i.recipient_type = 'HZ_PARTY'
      AND i.list_id = podl.list_id
      AND podl.object_id = p_pcid
      AND podl.object_type = 'PJP_PLANNING_CYCLE'
      AND hzp.party_id =  i.recipient_id
      AND hzp.party_type = 'PERSON'
      AND u.person_party_id = hzp.party_id
      and trunc(sysdate) BETWEEN trunc(u.start_date)  AND trunc(NVL(u.end_date,sysdate))
      AND p.person_id = a.person_id
      AND hzp.party_id = p.party_id (+);

Line: 697

select distinct u.user_name
  from fpa_aw_sce_info_v sc,
       fpa_aw_proj_info_v proj,
       pa_project_players pp,
       fnd_user u
where proj.scenario = sc.scenario
  and sc.planning_cycle = p_pcid
  and sc.approved_flag = 1
  and pp.project_id = proj.project
  and pp.resource_type_id =101
  and pp.project_role_type = 'PROJECT MANAGER'
  and u.employee_id = pp.person_id
      and trunc(sysdate) BETWEEN trunc(u.start_date)  AND trunc(NVL(u.end_date,sysdate))
  AND trunc(sysdate) BETWEEN trunc(pp.start_date_active)   AND trunc(NVL(pp.end_date_active,sysdate));

Line: 716

SELECT 'T' FROM (
    SELECT
      organization_id_child
    FROM
      per_org_structure_elements
    WHERE
      org_structure_version_id = p_hier_version_id
      CONNECT BY PRIOR organization_id_child = organization_id_parent
      AND PRIOR org_structure_version_id = p_hier_version_id
      START WITH organization_id_parent = p_portfolio_org_id  )
    WHERE organization_id_child = p_user_org_id;

Line: 737

   select
   org_structure_version_id into l_pjp_org_version_id
   from
       per_org_structure_versions
   where
       organization_structure_id = FND_PROFILE.VALUE('PJP_ORGANIZATION_HIERARCHY')
       and (trunc(sysdate) between trunc(date_from) and trunc(nvl(date_to,
       sysdate)));

Line: 746

   select
        portfolio_organization into l_portfolio_org_id
   from
        fpa_aw_pcs_v pcp, fpa_aw_portf_headers_v ph
   where
        pcp.portfolio = ph.portfolio
        and pcp.planning_cycle = p_pcid;

Line: 1363

      SELECT meaning
        INTO l_scenario_name
        FROM fpa_lookups_v
       WHERE lookup_code = 'FPA_INITIAL_SCENARIO_NAME';

Line: 1382

      SELECT pc_discount_rate * 100,
             pc_funding
        INTO l_discount_rate,
             l_scenario_funds
        FROM fpa_aw_pc_disc_funds_v
       WHERE planning_cycle = l_pc_id;

Line: 1416

                     'Calling fpa_scenario_pvt.update_scenario_disc_rate.');

Line: 1420

   fpa_scenario_pvt.update_scenario_disc_rate(
      p_api_version   => 1.0,
      p_scenario_id   => l_scenario_id,
      p_discount_rate => l_discount_rate,
      x_return_status => l_return_status,
      x_msg_count     => l_msg_count,
      x_msg_data      => l_msg_data);

Line: 1431

                     'Calling fpa_scenario_pvt.update_scenario_funds_avail.');

Line: 1435

   fpa_scenario_pvt.update_scenario_funds_avail(
      p_api_version    => 1.0,
      p_scenario_id    => l_scenario_id,
      p_scenario_funds => l_scenario_funds,
      x_return_status  => l_return_status,
      x_msg_count      => l_msg_count,
      x_msg_data       => l_msg_data);

Line: 1447

                     'Calling fpa_scenario_pvt.update_scenario_initial_flag.');

Line: 1451

   fpa_scenario_pvt.update_scenario_initial_flag(
      p_api_version   => 1.0,
      p_scenario_id   => l_scenario_id,
      x_return_status => l_return_status,
      x_msg_count     => l_msg_count,
      x_msg_data      => l_msg_data);

Line: 1461

                     'Calling fpa_scenario_pvt.update_scenario_working_flag.');

Line: 1465

   fpa_scenario_pvt.update_scenario_working_flag(
      p_api_version   => 1.0,
      p_scenario_id   => l_scenario_id,
      x_return_status => l_return_status,
      x_msg_count     => l_msg_count,
      x_msg_data      => l_msg_data);

Line: 2099

   dbms_aw.execute('UPDATE');

Line: 2192

    select scenario
     into l_sce_id
     from fpa_aw_sce_info_v
    where approved_flag = 1
      and planning_cycle = p_itemkey;

Line: 2215

         'Calling FPA_SCORECARDS_PVT.Update_Scenario_App_Scores.'
       );

Line: 2219

     FPA_SCORECARDS_PVT.Update_Scenario_App_Scores
       (p_api_version  => 1.0,
        p_init_msg_list => FND_API.G_FALSE,
        p_scenario_id => l_sce_id,
        x_return_status => l_return_status,
        x_msg_count => l_msg_count,
        x_msg_data => l_msg_data
        );

Line: 2232

         'Calling FPA_Project_PVT.UPDATE_PROJ_FUNDING_STATUS.'
       );

Line: 2236

    FPA_Project_PVT.UPDATE_PROJ_FUNDING_STATUS
    (
      p_api_version => 1.0,
      p_init_msg_list => FND_API.G_FALSE,
      p_commit => FND_API.G_FALSE,
      p_appr_scenario_id => l_sce_id,
      x_return_status => l_return_status,
      x_msg_count => l_msg_count,
      x_msg_data => l_msg_data);

Line: 2310

   dbms_aw.execute('UPDATE');

Line: 2400

       SELECT proj.project BULK COLLECT
         INTO l_project_id_tbl
         From fpa_aw_projs_v proj
        WHERE proj.planning_cycle = l_last_pc_id;

Line: 2550

     dbms_aw.execute('UPDATE');