APPS.PA_ALLOC_UTILS SQL Statements

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

Line: 13

 SELECT '1'
 FROM dual
 WHERE EXISTS (SELECT 'Y'
                FROM  PA_ALLOC_RESOURCES  RE
                WHERE  RE.RESOURCE_LIST_MEMBER_ID=P_resource_list_member_id
               )
      OR EXISTS
               (SELECT 'Y'
               FROM   PA_ALLOC_RUN_SOURCE_DET SRC
               WHERE SRC.RESOURCE_LIST_MEMBER_ID=P_resource_list_member_id
               )
       OR EXISTS
               (SELECT 'Y'
               FROM   PA_ALLOC_RUN_BASIS_DET BASIS
               WHERE BASIS.RESOURCE_LIST_MEMBER_ID=P_resource_list_member_id
               );

Line: 58

 SELECT '1'
 FROM dual
 WHERE EXISTS (SELECT 'Y'
               FROM  PA_ALLOC_RULES_ALL PAL /* Bug 4185336 Changed PA_ALLOC_RULES to PA_ALLOC_RULES_ALL */
               WHERE  PAL.BASIS_RESOURCE_LIST_ID=P_resource_list_id
                OR    PAL.ALLOC_RESOURCE_LIST_ID=P_resource_list_id
               )
      OR EXISTS
               (SELECT 'Y'
               FROM  PA_ALLOC_RUNS_ALL PAR
               WHERE  PAR.BASIS_RESOURCE_LIST_ID=P_resource_list_id
                OR    PAR.ALLOC_RESOURCE_LIST_ID=P_resource_list_id
               );

Line: 103

 SELECT '1'
 FROM dual
 WHERE EXISTS (SELECT 'Y'
               FROM  PA_ALLOC_TXN_DETAILS TXN
               WHERE TXN.project_id=p_project_id)
      OR EXISTS
               (SELECT 'Y'
               FROM  PA_ALLOC_RULES_ALL  RULES
               WHERE RULES.offset_project_id=p_project_id)
      OR EXISTS
               (SELECT 'Y'
               FROM  PA_ALLOC_SOURCE_LINES SRCL
               WHERE SRCL.project_id=p_project_id)
      OR EXISTS
              ( SELECT 'Y'
               FROM  PA_ALLOC_TARGET_LINES TGTL
               WHERE TGTL.project_id=p_project_id)
      OR EXISTS
               (SELECT 'Y'
               FROM   PA_ALLOC_RUN_SOURCES  RSRC
               WHERE  RSRC.project_id=p_project_id)
      OR EXISTS
               (SELECT 'Y'
               FROM PA_ALLOC_RUN_TARGETS RTGT
               WHERE  RTGT.project_id=p_project_id);

Line: 163

 select task_id
 from pa_tasks
 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
 START WITH TASK_ID = p_task_id;

Line: 170

              SELECT 'Y'
               FROM  PA_ALLOC_RULES_ALL RULES
               WHERE RULES.offset_task_id=x_task_id;

Line: 175

               SELECT 'Y'
               FROM  PA_ALLOC_SOURCE_LINES SRCL
               WHERE SRCL.task_id=x_task_id;

Line: 180

               SELECT 'Y'
               FROM  PA_ALLOC_TARGET_LINES TGTL
               WHERE TGTL.task_id=x_task_id;

Line: 184

               SELECT 'Y'
               FROM  PA_ALLOC_TXN_DETAILS TXN
               WHERE TXN.task_id=x_task_id;

Line: 189

               SELECT 'Y'
               FROM   PA_ALLOC_RUN_SOURCES RSRC
               WHERE  RSRC.task_id=x_task_id;

Line: 194

               SELECT 'Y'
               FROM PA_ALLOC_RUN_TARGETS RTGT
               WHERE  RTGT.task_id=x_task_id;

Line: 287

 SELECT '1'
 FROM dual
 WHERE EXISTS (SELECT '1'
               FROM  PA_ALLOC_TXN_DETAILS TXN
               WHERE TXN.task_id=p_task_id)
      OR EXISTS
              ( SELECT '1'
               FROM  PA_ALLOC_RULES_ALL RULES
               WHERE RULES.offset_task_id=p_task_id)
      OR EXISTS
               (SELECT '1'
               FROM  PA_ALLOC_TARGET_LINES TGTL
               WHERE TGTL.task_id=p_task_id)
      OR EXISTS
               (SELECT '1'
               FROM PA_ALLOC_RUN_TARGETS RTGT
               WHERE  RTGT.task_id=p_task_id);

Line: 306

 SELECT '1'
 FROM dual
 WHERE EXISTS (SELECT '1'
               FROM  PA_ALLOC_SOURCE_LINES SRCL
               WHERE SRCL.task_id=p_task_id)
       OR EXISTS
              ( SELECT '1'
               FROM  PA_ALLOC_RUN_SOURCES RSRC
               WHERE RSRC.task_id=p_task_id);

Line: 317

 SELECT top_task_id
 FROM pa_tasks
 WHERE task_id=p_tsk_id;

Line: 379

 SELECT '1'
 FROM dual
 WHERE EXISTS (SELECT 'Y'
               FROM  PA_ALLOC_RULES_ALL
               WHERE basis_budget_type_code=p_budget_type_code)
      OR EXISTS
               (SELECT 'Y'
               FROM  PA_ALLOC_RUNS_ALL
               WHERE basis_budget_type_code=p_budget_type_code);

Line: 422

 SELECT '1'
 FROM dual
 WHERE EXISTS (SELECT 'Y'
               FROM  PA_ALLOC_RULES_ALL
               WHERE basis_budget_entry_method_code=p_bem_code)
      OR EXISTS
               (SELECT 'Y'
               FROM  PA_ALLOC_RUNS_ALL
               WHERE basis_budget_entry_method_code=p_bem_code);

Line: 468

		Select 'Y' into l_exists
		  from pa_alloc_rules_all /* Bug 4185336 Changed from pa_alloc_rules to pa_alloc_rules_all */
		 where
			  (
				(	ALLOC_RESOURCE_STRUCT_TYPE  = 'RBS'
					and
					Alloc_resource_list_id = P_RBS_ID
				)
				or
				(
					BASIS_RESOURCE_STRUCT_TYPE  = 'RBS'
					and
					BASIS_resource_list_id = P_RBS_ID
				)
			  )
		   and rownum = 1;

Line: 491

		Select 'Y' into l_exists
		  from pa_alloc_runs_all  /* Bug 4185336 Changed from pa_alloc_rules to pa_alloc_rules_all */
		 where (
				(	ALLOC_RESOURCE_STRUCT_TYPE  = 'RBS'
					and
					Alloc_resource_list_id = P_RBS_ID
				)
				or
				(
					BASIS_RESOURCE_STRUCT_TYPE  = 'RBS'
					and
					BASIS_resource_list_id = P_RBS_ID
				)
			  )
		   and rownum = 1;

Line: 527

		Select 'Y' into l_exists
		  From PA_ALLOC_RULES_ALL par, PA_ALLOC_RESOURCES pr
		 Where par.rule_id = pr.rule_id
		   and pr.RESOURCE_LIST_MEMBER_ID = P_RBS_ELEMENT_ID
		   and decode (pr.member_type , 'S' , par.ALLOC_RESOURCE_STRUCT_TYPE  ,
										'B' , par.BASIS_RESOURCE_STRUCT_TYPE
					  ) = 'RBS'
		   and rownum = 1;

Line: 542

		Select 'Y' into l_exists
		  From pa_alloc_runs_all par, PA_ALLOC_RUN_SOURCE_DET pars
		 Where par.run_id = pars.run_id
		   and par.rule_id = pars.rule_id
		   and RESOURCE_LIST_MEMBER_ID = P_RBS_ELEMENT_ID
		   and par.ALLOC_RESOURCE_STRUCT_TYPE  = 'RBS'
		   and rownum = 1;

Line: 555

		Select 'Y' into l_exists
		  From pa_alloc_runs_all par, PA_ALLOC_RUN_BASIS_DET pars
		 Where par.run_id = pars.run_id
		   and par.rule_id = pars.rule_id
		   and RESOURCE_LIST_MEMBER_ID = P_RBS_ELEMENT_ID
		   and par.BASIS_RESOURCE_STRUCT_TYPE  = 'RBS'
		   and rownum = 1;

Line: 593

	Select ALLOC_RESOURCE_STRUCT_TYPE ,
		   BASIS_RESOURCE_STRUCT_TYPE
	  Into l_source_res_struct_type ,
	 	   l_basis_res_struct_type
      From pa_alloc_rules_all
	 Where Rule_ID = p_Rule_Id;

Line: 601

			select prlm.alias
			  Into X_name
		 	  from pa_resource_list_members prlm
		   	 where prlm.resource_list_member_id = p_resource_id;

Line: 606

			Select pa_alloc_utils.Get_Resource_Name_TL (RBS_ELEMENT_NAME_ID)
			  Into X_name
			  From pa_rbs_elements
		 	 Where rbs_element_id = p_resource_id
			   And rownum = 1;

Line: 615

			select prlm.alias
			  Into X_name
		 	  from pa_resource_list_members prlm
		   	 where prlm.resource_list_member_id = p_resource_id;

Line: 620

			select pa_alloc_utils.Get_Resource_Name_TL (RBS_ELEMENT_NAME_ID)
			  Into X_name
			  from pa_rbs_elements
		 	 where rbs_element_id = p_resource_id
			   and rownum = 1;

Line: 631

 API Desc : This procedure will be update the new element id to the allocation rules's source resource list member's id
			and basis resource list member's id when new version of RBS is created
 API Created Date : 02-Apr-04
 API Created By : Vthakkar

 History :

	07-JAN-2005 VTHAKKAR Changed the API for bug 4107924

*/
Procedure ASSOCIATE_RBS_TO_ALLOC_RULE (
										p_rbs_header_id		IN NUMBER    ,
										p_rbs_version_id	IN NUMBER	 ,
										x_return_status     OUT NOCOPY VARCHAR2 ,
										x_error_code        OUT NOCOPY VARCHAR2
									  )
IS
	CURSOR LV_ALLOC_CUR IS SELECT * FROM PA_ALLOC_RULES_ALL
	                        WHERE alloc_resource_list_id = p_rbs_header_id
							   OR basis_resource_list_id = p_rbs_header_id;

Line: 659

		/* Source Update */

		IF NVL(ALLOC_CUR.alloc_resource_struct_type,'RL') = 'RBS' and ALLOC_CUR.alloc_resource_list_id = p_rbs_header_id
		   and NVL(ALLOC_CUR.ALLOC_RBS_VERSION,0) < p_rbs_version_id Then

			Update pa_alloc_rules_all
			   Set alloc_rbs_version = p_rbs_version_id
			 Where RULE_ID = ALLOC_CUR.RULE_ID;

Line: 668

			Update pa_alloc_resources ARS
			   Set resource_list_member_id = (
											select new.rbs_element_id
											  from pa_rbs_elements new
												  ,pa_rbs_elements old
											 where new.element_identifier = old.element_identifier
											   and old.rbs_version_Id     = ALLOC_CUR.alloc_rbs_version
											   and new.rbs_version_Id     = p_rbs_version_id
											   and old.rbs_element_id     = ars.resource_list_member_id
											   and new.user_created_flag = 'N'
										  )
			 Where ARS.RULE_ID = ALLOC_CUR.RULE_ID
			   AND ARS.MEMBER_TYPE = 'S';

Line: 685

		/* Basis Update */

		IF NVL(ALLOC_CUR.basis_resource_struct_type,'RL') = 'RBS' and ALLOC_CUR.basis_resource_list_id = p_rbs_header_id
		   and NVL(ALLOC_CUR.BASIS_RBS_VERSION,0) < p_rbs_version_id Then

			Update pa_alloc_rules_all
			   Set basis_rbs_version = p_rbs_version_id
			 Where RULE_ID = ALLOC_CUR.RULE_ID;

Line: 694

			Update pa_alloc_resources ARS
			   Set resource_list_member_id = (
										select new.rbs_element_id
										  from pa_rbs_elements new
											  ,pa_rbs_elements old
					                     where new.element_identifier = old.element_identifier
									       and old.rbs_version_Id     = ALLOC_CUR.basis_rbs_version
										   and new.rbs_version_Id     = p_rbs_version_id
	                                       and old.rbs_element_id     = ars.resource_list_member_id
										   and new.user_created_flag = 'N'
									  )
			  Where ARS.RULE_ID = ALLOC_CUR.RULE_ID
			    AND ARS.MEMBER_TYPE = 'B';

Line: 713

	Update pa_alloc_resources ARS
	   Set resource_list_member_id = (
									 select new.rbs_element_id
		                               from pa_rbs_elements new
			                               ,pa_rbs_elements old
				                           ,pa_alloc_rules_all ar
					                  where ar.alloc_resource_list_id = p_rbs_header_id
						                and decode ( ars.member_type ,
														'S' , nvl(ar.alloc_resource_struct_type,'RL') ,
														'B' , nvl(ar.basis_resource_struct_type,'RL')
													) = 'RBS'
							            and ar.rule_id = ars.rule_id
									    and new.element_identifier = old.element_identifier
									    and old.rbs_version_Id     = ar.alloc_rbs_version
										and new.rbs_version_Id     = p_rbs_version_id
	                                    and old.rbs_element_id     = ars.resource_list_member_id
									  )
	  Where Exists (
					 select 1
					   From pa_alloc_rules_all arl
					  Where arl.Rule_Id = Ars.Rule_Id
						ANd decode ( ars.member_type ,
										'S' , nvl(arl.alloc_resource_struct_type,'RL') ,
										'B' , nvl(arl.basis_resource_struct_type,'RL')
								   ) = 'RBS'
				   );

Line: 739

	Update pa_alloc_rules_all
	   Set alloc_rbs_version = p_rbs_version_id
	 Where nvl(alloc_resource_struct_type,'RL') = 'RBS'
	   And alloc_resource_list_id = p_rbs_header_id;

Line: 743

	Update pa_alloc_rules_all
	   Set basis_rbs_version = p_rbs_version_id
	 Where nvl(basis_resource_struct_type,'RL') = 'RBS'
	   And basis_resource_list_id = p_rbs_header_id;

Line: 774

			Select Name

			  Into X_Resource_List_Name
 			  From pa_resource_lists_v
			 Where RESOURCE_LIST_ID = p_resource_list_id;

Line: 780

			Select Name
			  Into X_Resource_List_Name
 			  From pa_rbs_headers_v
			 Where RBS_HEADER_ID = p_resource_list_id;

Line: 807

	  Is Select slf.alias , prt.alias
	  from pa_resource_list_members slf , pa_resource_list_members prt
	 Where prt.resource_list_member_id (+) = slf.parent_member_id
	   and slf.resource_list_member_id     = p_resource_id;

Line: 812

	   IS SELECT pa_alloc_utils.Get_Resource_Name_TL(ele.rbs_element_name_id) Resource_Name
	        FROM pa_rbs_elements ele
	     CONNECT BY PRIOR ele.parent_element_id = ele.rbs_element_id
		   START WITH ele.rbs_element_id = p_resource_id
		   ORDER BY ele.rbs_level DESC;

Line: 874

	Select Name
	  Into X_Rbs_Ver_Name
	  From pa_rbs_versions_v
	 Where Rbs_Version_Id = P_Rbs_Ver_Id;

Line: 897

	Select resource_name
	  Into x_resource_name
	  From pa_rbs_element_names_tl
	 Where rbs_element_name_id = p_rbs_element_name_id
	   and language = USERENV('LANG'); /* Added for bug 3960634 */