APPS.PQH_BDGT_REALLOC_UTILITY SQL Statements

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

Line: 71

  SELECT NVL(sum(trnxamt.reallocation_amt),0), NVL(sum(trnxamt.reserved_amt),0)
  FROM   pqh_budget_pools fld,
         pqh_budget_pools trnx,
         pqh_bdgt_pool_realloctions trnxdtl,
         pqh_bdgt_pool_realloctions trnxamt
  WHERE  fld.pool_id = trnx.parent_pool_id
   AND   fld.approval_status=p_approval_status
   AND   fld.budget_unit_id = p_budget_unit_id
   AND   fld.entity_type = p_entity_type
   AND   trnx.pool_id = trnxdtl.pool_id
   AND   trnxdtl.reallocation_id = trnxamt.txn_detail_id
   AND   trnxamt.budget_period_id = p_budget_period_id
   AND   trnxamt.transaction_type = p_transaction_type;

Line: 87

  SELECT NVL(sum(trnxamt.reallocation_amt),0), NVL(sum(trnxamt.reserved_amt),0)
  FROM   pqh_budget_pools fld,
         pqh_budget_pools trnx,
         pqh_bdgt_pool_realloctions trnxdtl,
         pqh_bdgt_pool_realloctions trnxamt
  WHERE  fld.pool_id = trnx.parent_pool_id
   AND   fld.budget_unit_id = p_budget_unit_id
   AND   fld.entity_type = p_entity_type
   AND   trnx.pool_id = trnxdtl.pool_id
   AND   fld.approval_status = p_approval_status
   AND   trnxdtl.reallocation_id = trnxamt.txn_detail_id
   AND   trnxamt.transaction_type = p_transaction_type
   AND   trnxamt.entity_id = p_entity_id
   AND   NVL(trnxamt.start_date,to_date('31/12/4712','dd/mm/RRRR')) BETWEEN p_start_date and p_end_date
   AND   NVL(trnxamt.end_date,to_date('31/12/4712','dd/mm/RRRR')) BETWEEN p_start_date and p_end_date;

Line: 183

  SELECT  NVL(SUM(trnxamt.reallocation_amt),0), NVL(SUM(trnxamt.reserved_amt),0)
  FROM    pqh_budget_pools fld,
          pqh_budget_pools trnx,
          pqh_bdgt_pool_realloctions trnxdtl,
          pqh_bdgt_pool_realloctions trnxamt
  WHERE   fld.budget_unit_id = p_budget_unit_id
    AND   fld.entity_type = p_entity_type
    AND   fld.approval_status = p_approval_status
    AND   fld.pool_id = trnx.parent_pool_id
    AND   trnx.pool_id = trnxdtl.pool_id
    AND   trnxdtl.budget_detail_id = p_budget_detail_id
    AND   trnxamt.transaction_type = 'DD'
    AND   trnxdtl.reallocation_id = trnxamt.txn_detail_id;

Line: 198

  SELECT  NVL(SUM(trnxamt.reallocation_amt),0), NVL(SUM(trnxamt.reserved_amt),0)
  FROM    pqh_budget_pools fld,
          pqh_budget_pools trnx,
          pqh_bdgt_pool_realloctions trnxdtl,
          pqh_bdgt_pool_realloctions trnxamt
  WHERE   fld.budget_unit_id = p_budget_unit_id
    AND   fld.entity_type = p_entity_type
    AND   fld.approval_status = p_approval_status
    AND   fld.pool_id = trnx.parent_pool_id
    AND   trnx.pool_id = trnxdtl.pool_id
    AND   trnxdtl.reallocation_id = trnxamt.txn_detail_id
    AND   trnxdtl.entity_id = p_entity_id
    AND   trnxamt.transaction_type = 'RD'
    AND   NVL(trnxamt.start_date,TO_DATE('31/12/4712','dd/mm/RRRR')) BETWEEN p_start_date and p_end_date
    AND   NVL(trnxamt.end_date,TO_DATE('31/12/4712','dd/mm/RRRR')) BETWEEN p_start_date and p_end_date;

Line: 276

Select nvl(sum(trnxamt.reallocation_amt),0)
From pqh_bdgt_pool_realloctions trnxamt,
pqh_bdgt_pool_realloctions trnxdtl
where trnxamt.txn_detail_id = trnxdtl.reallocation_id
and  trnxdtl.pool_id= p_transaction_id
and  trnxamt.transaction_type = p_transaction_type;

Line: 328

Select pool_id
From   pqh_budget_pools
Where  parent_pool_id= p_folder_id;

Line: 377

Select count(*)
From   pqh_bdgt_pool_realloctions
Where  pool_id = p_transaction_id
And    transaction_type = p_transaction_type;

Line: 415

select transaction_id,entity_id,txn_detail_id
from pqh_realloc_txn_details_v
where transaction_id = p_trans_id
and entity_id =p_entity_id
and transaction_type = 'R';

Line: 459

Select count(pool_id)
From   pqh_budget_pools
Where  budget_version_id = p_budget_version_id
And    budget_unit_id = p_budget_unit_id
And    entity_type = p_entity_type
And    approval_status  = p_approval_status;

Line: 502

Select nvl(sum(trnxamt.reserved_amt),0)
From pqh_bdgt_pool_realloctions trnxamt,
pqh_bdgt_pool_realloctions trnxdtl
where trnxamt.txn_detail_id = trnxdtl.reallocation_id
and  trnxdtl.pool_id= p_transaction_id
and  trnxamt.transaction_type = p_transaction_type;

Line: 535

	select location_code
	into l_location_code
	from hr_organization_units_v
	where organization_id = p_organization_id;

Line: 540

       select location_code
       into l_location_code
       from hr_organization_units_v
       where organization_id= p_business_group_id;

Line: 724

      select user_name
      into p_next_name
      from fnd_user
      where user_id=p_member_user_id;

Line: 735

      select role_name
      into p_next_name
      from pqh_roles
      where role_id= p_member_role_id;

Line: 752

   select hr_general.DECODE_POSITION_LATEST_NAME (p_position_id)
   into p_next_name from dual;

Line: 762

   select person_id into l_person_id
   from per_assignments
   where assignment_id  = p_assignment_id;

Line: 766

   select user_name
   into p_next_name
   from fnd_user
   where employee_id=l_person_id;

Line: 803

 IS  SELECT *
     FROM   pqh_budget_pools
     WHERE  pool_id = p_transaction_id;

Line: 829

      pqh_budget_pools_api.update_reallocation_folder(p_effective_date=> hr_general.effective_date
                                                     ,p_folder_id => p_transaction_id
                                                     ,p_object_version_number=>l_pool_rec.object_version_number
                                                     ,p_business_group_id => l_pool_rec.business_group_id
                                                     ,p_approval_status=> 'A'
                                                     ,p_wf_transaction_category_id => l_pool_rec.wf_transaction_category_id );

Line: 890

select job_id,position_id,grade_id,organization_id
from pqh_budget_details
where budget_detail_id = p_budget_detail_id;

Line: 924

  cursor csr_pool_dtls is select name,
                                 hr_general.decode_lookup('PQH_BUDGET_ENTITY',ENTITY_TYPE),
                      hr_general.decode_shared_type(budget_unit_id),
                      hr_general.decode_lookup('PQH_REALLOC_TXN_STATUS',approval_status)
               from pqh_budget_pools
               where pool_id = p_transaction_id;

Line: 956

   SELECT business_group_id, NVL(approval_status,'P')
   FROM   pqh_budget_pools
  WHERE   pool_id = p_transaction_id;

Line: 980

  cursor csr_pool_dtls is select name,
                                 hr_general.decode_lookup('PQH_BUDGET_ENTITY',ENTITY_TYPE),
                      hr_general.decode_shared_type(budget_unit_id),
                      hr_general.decode_lookup('PQH_REALLOC_TXN_STATUS',approval_status)
               from pqh_budget_pools
               where pool_id = p_transaction_id;

Line: 1015

  cursor csr_pool_dtls is select name,
                                 hr_general.decode_lookup('PQH_BUDGET_ENTITY',ENTITY_TYPE),
                      hr_general.decode_shared_type(budget_unit_id),
                      hr_general.decode_lookup('PQH_REALLOC_TXN_STATUS',approval_status)
               from pqh_budget_pools
               where pool_id = p_transaction_id;

Line: 1050

  cursor csr_pool_dtls is select name,
                                 hr_general.decode_lookup('PQH_BUDGET_ENTITY',ENTITY_TYPE),
                      hr_general.decode_shared_type(budget_unit_id),
                      hr_general.decode_lookup('PQH_REALLOC_TXN_STATUS',approval_status)
               from pqh_budget_pools
               where pool_id = p_transaction_id;

Line: 1090

 IS  SELECT *
     FROM   pqh_budget_pools
     WHERE  pool_id = p_transaction_id;

Line: 1107

  pqh_budget_pools_api.update_reallocation_folder(p_effective_date=> hr_general.effective_date
                                                 ,p_folder_id => p_transaction_id
                                                 ,p_object_version_number=>l_pool_rec.object_version_number
                                                 ,p_business_group_id => l_pool_rec.business_group_id
                                                 ,p_approval_status=> 'R'
                                                 ,p_wf_transaction_category_id => l_pool_rec.wf_transaction_category_id
,p_validate => l_validate );

Line: 1156

Select  role_id,role_name
From pqh_roles
Where role_type_cd = 'BUDGET'
And enable_flag = 'Y'
And(  business_group_id = l_business_group_id
OR business_group_id IS NULL );

Line: 1165

Select  business_group_id
From pqh_budget_pools
Where pool_id = p_transaction_id;

Line: 1170

Select max(substr(item_key,(length(l_transaction_category_id||'-'||p_transaction_id))+2,6) )
From wf_items
Where item_type='PQHGEN'
And item_key like l_transaction_category_id||'-'||p_transaction_id||'%';

Line: 1244

  cursor c0 is select  name,
                       hr_general.decode_lookup('PQH_BUDGET_ENTITY',ENTITY_TYPE),
                       hr_general.decode_shared_type(budget_unit_id),
                       hr_general.decode_lookup('PQH_REALLOC_TXN_STATUS',approval_status)
               from pqh_budget_pools
               where pool_id = p_transaction_id;

Line: 1272

PROCEDURE update_folder_approval_status (p_transaction_id in number, p_action_flag in varchar2)
IS
/* This procedure updates the folder approval status as 'P' (Pending)
   if the action flag is I (InBox) , F (Forward)
 */

Cursor csr_pool_dtls IS
Select *
From pqh_budget_pools
Where pool_id = p_transaction_id;

Line: 1284

l_proc   varchar2(72) := g_package||'update_folder_approval_status';

Line: 1300

         pqh_budget_pools_api.update_reallocation_folder(p_effective_date=> hr_general.effective_date
                                                 ,p_folder_id => p_transaction_id
                                                 ,p_object_version_number=>l_pool_rec.object_version_number
                                                 ,p_business_group_id => l_pool_rec.business_group_id
                                                 ,p_approval_status=> 'P'
                                                 ,p_wf_transaction_category_id => l_pool_rec.wf_transaction_category_id);

Line: 1311

End update_folder_approval_status;

Line: 1312

PROCEDURE bgt_dummy_folder_delete(p_business_group_id IN number)
is

/*
This procedure deletes all folders whose approval_status is T (created)
i.e the folders which are created but not routed.
*/
Cursor csr_approval_status_T_folders IS
Select pool_id
From pqh_budget_pools
Where approval_status = 'T'
And business_group_id = p_business_group_id
AND creation_date < sysdate - 2;

Line: 1327

l_proc    varchar2(72) := g_package ||'bgt_dummy_folder_delete';

Line: 1341

        /* In the procedure pqh_budget_pools_swi.bgt_realloc_delete
           node_type T is marked as Transaction.
           So here passed TCF (Tempory Created Folder) as node type
           to distinguish with Transaction
         */
       BEGIN
         pqh_budget_pools_swi.bgt_realloc_delete(p_node_type => 'TCF'
                                            ,p_node_id   =>l_folder_id);

Line: 1360

END bgt_dummy_folder_delete;

Line: 1368

   SELECT routing_category_id
   FROM   pqh_routing_categories
   WHERE  transaction_category_id = p_txn_catg_id;

Line: 1414

    select bgt.budget_start_date
    from   pqh_budgets bgt,
           pqh_budget_versions bvr,
           pqh_budget_pools fld
    where  fld.pool_id = p_transaction_id
    and    fld.budget_version_id = bvr.budget_version_id
    and    bvr.budget_id = bgt.budget_id;

Line: 1485

SELECT prevent_approver_skip
into l_status
from pqh_transaction_categories
where transaction_category_id = p_transaction_category_id;

Line: 1551

  SELECT stt.shared_type_name
    FROM per_shared_types st, per_shared_types_tl stt
   WHERE st.lookup_type       = 'BUDGET_MEASUREMENT_TYPE'
     AND(st.business_group_id = HR_GENERAL.get_business_group_id OR
         st.business_group_id IS NULL)
     AND st.shared_type_id    = p_budget_unit_id
     AND stt.shared_type_id   = st.shared_type_id
     AND stt.language = USERENV('LANG');