APPS.PA_ALTERNATE_TASK_PVT SQL Statements

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

Line: 172

			IF l_valid_status_tbl(i) = 'V' THEN  -- Insert records which are valid
				print_msg('Inside IF loop :: ');

Line: 175

			 select PA_TASKS_S.NEXTVAL INTO l_new_task_id from sys.dual;

Line: 178

			 INSERT INTO PA_ALTERNATE_TASKS (
			   ALT_TASK_ID			,
			   PROJ_ELEMENT_ID      ,
			   CBS_ELEMENT_ID		,
			   LAST_UPDATE_DATE     ,
			   LAST_UPDATED_BY      ,
			   CREATION_DATE        ,
			   CREATED_BY           ,
			   LAST_UPDATE_LOGIN    ,
			   ATTRIBUTE_CATEGORY 	,
			   ATTRIBUTE1     		,
			   ATTRIBUTE2     		,
			   ATTRIBUTE3      		,
			   ATTRIBUTE4      		,
			   ATTRIBUTE5      		,
			   ATTRIBUTE6      		,
			   ATTRIBUTE7      		,
			   ATTRIBUTE8      		,
			   ATTRIBUTE9      		,
			   ATTRIBUTE10     		,
			   ATTRIBUTE11     		,
			   ATTRIBUTE12     		,
			   ATTRIBUTE13     		,
			   ATTRIBUTE14     		,
			   ATTRIBUTE15
			   )
				VALUES (
				l_new_task_id					,
				p_Proj_Element_Id(i)			,
				p_Cbs_Element_Id(i)				,
				SysDate							,
				Fnd_Global.User_Id				,
				SysDate							,
				Fnd_Global.User_Id				,
				Fnd_Global.Login_Id				,
				l_attribute_category_tbl(i)		,
				l_attribute1(i)					,
				l_attribute2(i)					,
				l_attribute3(i)					,
				l_attribute4(i)					,
				l_attribute5(i)					,
				l_attribute6(i)					,
				l_attribute7(i)					,
				l_attribute8(i)					,
				l_attribute9(i)					,
				l_attribute10(i)				,
				l_attribute11(i)				,
				l_attribute12(i)				,
				l_attribute13(i)				,
				l_attribute14(i)				,
				l_attribute15(i)
				);

Line: 230

			print_msg('After Insert :: ');

Line: 246

*	This API is used to Delete existing Alternate Task.
* 	In this it needs to validate whether the alternate task can be deleted or not.
*
*/
PROCEDURE Delete_Alt_Task(
	p_Alt_Task_Id    	 	 IN		SYSTEM.pa_num_tbl_type	DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
	X_Return_Status       	 OUT NOCOPY Varchar2,
	X_Msg_Data 	      		 OUT NOCOPY Varchar2,
	X_Msg_Count 	      	 OUT NOCOPY Number)
IS
	l_valid_status_tbl     SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();

Line: 265

        pa_debug.init_err_stack('PA_ALTERNATE_TASK_PVT.Delete_Alt_Task');

Line: 269

	print_msg('PA_ALTERNATE_TASK_PVT.Delete_Alt_Task IN param(Scalar) values');

Line: 282

			IF l_valid_status_tbl(i) = 'V' THEN  -- Delete records which are valid

			 DELETE FROM PA_ALTERNATE_TASKS where ALT_TASK_ID = p_Alt_Task_Id(i);

Line: 292

print_msg('PA_ALTERNATE_TASK_PVT.Delete_Alt_Task :: End');

Line: 299

END Delete_Alt_Task;

Line: 316

	select 'Y' from dual where
	EXISTS
	(SELECT *
	 FROM PA_ALTERNATE_TASKS pat , PA_RBS_ELEMENTS pre
	 WHERE (pat.CBS_ELEMENT_ID = c_Cbs_Element_Id
	 AND pat.PROJ_ELEMENT_ID = c_Proj_Element_Id)
	 OR (pat.CBS_ELEMENT_ID = pre.RBS_ELEMENT_ID  --bug#16311830 checking if cost code is disabled
     AND pat.CBS_ELEMENT_ID = c_Cbs_Element_Id
     AND nvl(pre.ENABLE_FLAG,'Y') = 'N'));

Line: 379

	SELECT 'Y' validation_success  /* If cursor returns a record, deletion is not allowed */
	FROM   DUAL
	WHERE  EXISTS (
                 SELECT 1
                 FROM   pa_resource_assignments r
                 WHERE  r.task_id = (select proj_element_id from pa_alternate_tasks where alt_task_id = c_Alt_Task_Id)
                 AND 	r.cbs_element_id = (select cbs_element_id from pa_alternate_tasks where alt_task_id = c_Alt_Task_Id)
				 );

Line: 389

	select project_id from pa_tasks where task_id =
	(select proj_element_id from pa_alternate_tasks where alt_task_id = c_Alt_Task_Id);

Line: 393

	Select proj_element_id,cbs_element_id
	From pa_alternate_tasks where alt_task_id = c_Alt_Task_Id;

Line: 423

									SELECT
									  1 into l_dummy
									FROM
									  sys.dual
									WHERE
									  exists (SELECT NULL
												FROM   po_distributions_all poa
							where  poa.project_id = l_project_id
												  AND  poa.TASK_ID = p_Alt_Task_Id(i));

Line: 459

									SELECT
									  1 into l_dummy
									FROM
									  sys.dual
									WHERE
									  exists (SELECT NULL
												FROM   po_req_distributions_all prd
												where  prd.project_id = l_project_id
												  AND  prd.TASK_ID = p_Alt_Task_Id(i));

Line: 496

									SELECT
									  1 into l_dummy
									FROM
									  sys.dual
									WHERE
									  exists (SELECT NULL
												FROM   ap_invoices_all aia
												where  aia.project_id = l_project_id
												  AND  aia.TASK_ID = p_Alt_Task_Id(i));

Line: 532

									SELECT
									  1 into l_dummy
									FROM
									  sys.dual
									WHERE
									  exists (SELECT NULL
												FROM   ap_invoice_distributions_all aid
												where  aid.project_id = l_project_id
												  AND  aid.TASK_ID = p_Alt_Task_Id(i));

Line: 569

												SELECT
												  1 into l_dummy
												FROM
												  sys.dual
												WHERE
												  exists (SELECT NULL
															FROM   ap_exp_report_dists_all er
											where  er.project_id = l_project_id
															  AND  er.TASK_ID = p_Alt_Task_Id(i));

Line: 605

									SELECT
									  1 into l_dummy
									FROM
									  sys.dual
									WHERE
									  exists (SELECT NULL
												FROM   mtl_material_transactions mtl
										where  mtl.project_id = l_project_id
												  AND  mtl.TASK_ID = p_Alt_Task_Id(i));

Line: 641

									SELECT
									  1 into l_dummy
									FROM
									  sys.dual
									WHERE
									  exists (SELECT NULL
												FROM   PA_EXPENDITURE_ITEMS_all pei
										where  pei.CBS_ELEMENT_ID = l_cbs_element_id
										and pei.task_id=l_proj_element_id);

Line: 678

									SELECT
									  1 into l_dummy
									FROM
									  sys.dual
									WHERE
									  exists (SELECT NULL
												FROM   pa_commitment_txns pct
										where  pct.CBS_ELEMENT_ID = p_Alt_Task_Id(i));

Line: 712

									SELECT
									  1 into l_dummy
									FROM
									  sys.dual
									WHERE
									  exists (SELECT NULL
												FROM   pa_draft_invoice_details_all pdi
										where  pdi.CC_TAX_TASK_ID = p_Alt_Task_Id(i));

Line: 761

									SELECT 1
										INTO x_exist
										FROM AP_EXPENSE_REPORT_LINES_ALL A
										WHERE A.TASK_ID IS NOT NULL
									AND A.TASK_ID = p_Alt_Task_Id(i)
									AND EXISTS ( SELECT 1 FROM AP_EXPENSE_REPORT_HEADERS_ALL B
												WHERE A.REPORT_HEADER_ID = B.REPORT_HEADER_ID
												AND B.SOURCE <> 'Oracle Project Accounting'
												AND B.VOUCHNO = 0)
									AND rownum = 1;

Line: 835

	select CBS_ELEMENT_ID from PA_ALTERNATE_TASKS
	where PROJ_ELEMENT_ID = p_Source_Task_Id;

Line: 846

	select CBS_ELEMENT_ID
	BULK COLLECT INTO l_Cbs_Element_Id
	from PA_ALTERNATE_TASKS
	where PROJ_ELEMENT_ID = p_Source_Task_Id;

Line: 891

	select 'Y' from dual where
	EXISTS
	(SELECT *
	 FROM pa_projects_all
	 WHERE project_id = c_project_id
	 AND CBS_VERSION_ID IS NOT NULL
	 AND CBS_ENABLE_FLAG = 'Y');

Line: 926

	select 'Y' from dual where
	EXISTS
	(SELECT *
	 FROM PA_ALTERNATE_TASKS
	 WHERE proj_element_id = c_Task_Id
	 AND cbs_element_id = c_Cbs_Element_Id
	 );

Line: 935

	select 'Y' from dual where
	EXISTS
	(SELECT *
	 FROM pa_projects_all
	 WHERE project_id = c_Project_Id
	 AND CBS_VERSION_ID =
	 (select rbs_version_id from pa_rbs_elements
	 where rbs_element_id = c_Cbs_Element_Id)
	 );

Line: 1023

	select 'Y' from dual where
	EXISTS
	(SELECT *
	 FROM PA_ALTERNATE_TASKS
	 WHERE proj_element_id = c_Task_Id
	 AND cbs_element_id = (select rbs_element_id from pa_rbs_elements
	where cost_code = c_Cost_Code
	and rbs_version_id =
	(select cbs_version_id from pa_projects_all where project_id = c_Project_Id))
	 );

Line: 1035

	select 'Y' from dual where
	EXISTS
	(select * from pa_rbs_elements
	where cost_code = c_Cost_Code
	and rbs_version_id =
	(select cbs_version_id from pa_projects_all where project_id = c_Project_Id)
	 );

Line: 1119

	select project_id from pa_budget_versions where
	budget_version_id = p_budget_version_id;

Line: 1123

	select 'Y' from dual where
	EXISTS
	(select * from pa_proj_fp_options
		where (REVENUE_FIN_PLAN_LEVEL_CODE = 'P' OR
		COST_FIN_PLAN_LEVEL_CODE = 'P' OR
		ALL_FIN_PLAN_LEVEL_CODE = 'P') and FIN_PLAN_VERSION_ID = p_budget_version_id
	 );

Line: 1132

	select 'Y' from dual where
	EXISTS
	(select * from pa_proj_fp_options
	where (GEN_ALL_ETC_SRC_CODE = 'RESOURCE_SCHEDULE' OR
		GEN_ALL_SRC_CODE = 'RESOURCE_SCHEDULE' OR
		GEN_REV_SRC_CODE = 'RESOURCE_SCHEDULE' OR
		GEN_COST_SRC_CODE = 'RESOURCE_SCHEDULE'OR
		GEN_COST_ETC_SRC_CODE = 'RESOURCE_SCHEDULE' OR
		GEN_REV_ETC_SRC_CODE = 'RESOURCE_SCHEDULE') and FIN_PLAN_VERSION_ID = p_budget_version_id
	 );

Line: 1144

	select 'Y' from dual where
	EXISTS
	(select * from pa_proj_fp_options
		where (GEN_REV_INCL_BILL_EVENT_FLAG = 'Y' OR
		GEN_ALL_INCL_BILL_EVENT_FLAG = 'Y') and FIN_PLAN_VERSION_ID = p_budget_version_id
	 );

Line: 1152

	select 'Y' from dual where
	EXISTS
	(select * from pa_proj_fp_options
		where (REVENUE_DERIVATION_METHOD = 'EVENT' ) and FIN_PLAN_VERSION_ID = p_budget_version_id
	 );

Line: 1236

SELECT DECODE(COUNT(*),0,'N','Y') DISABLED_EXISTS
from pa_alternate_tasks pat , pa_rbs_elements pre , pa_proj_elements ppe
where pat.cbs_element_id = pre.rbs_element_id
and ppe.proj_element_id = pat.proj_element_id
and pre.ENABLE_FLAG='N' and ppe.project_id=P_Project_Id;

Line: 1259

	SELECT PRE.RBS_ELEMENT_ID CBS_ELEMENT_ID
	FROM PA_RBS_ELEMENTS PRE , PA_PROJECTS_ALL PPA
	WHERE PRE.RBS_VERSION_ID=PPA.CBS_VERSION_ID
	AND   PPA.PROJECT_ID=p_project_id
	AND   PRE.COST_CODE=p_cost_code;

Line: 1273

PROCEDURE Delete_ALL_Alt_Task(
	p_project_id    	 	 IN		NUMBER,
	X_Return_Status       	 OUT NOCOPY Varchar2,
	X_Msg_Data 	      		 OUT NOCOPY Varchar2)
IS
	l_valid_status_tbl     SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE();

Line: 1290

        pa_debug.init_err_stack('PA_ALTERNATE_TASK_PVT.Delete_ALL_Alt_Task');

Line: 1293

	SELECT DISTINCT ALT_TASK_ID BULK COLLECT INTO l_alt_task_id_tbl
	FROM PA_PROJ_ELEMENTS PPE, PA_ALTERNATE_TASKS PAT
	WHERE PPE.PROJECT_ID=p_project_id
	AND PAT.PROJ_ELEMENT_ID=PPE.PROJ_ELEMENT_ID;

Line: 1299

			  PA_ALTERNATE_TASK_PVT.Delete_Alt_Task(
					p_Alt_Task_Id =>l_alt_task_id_tbl,
					X_Return_Status => X_Return_Status,
					X_Msg_Data =>X_Msg_Data,
					X_Msg_Count=>X_Msg_Count);

Line: 1305

print_msg('PA_ALTERNATE_TASK_PVT.Delete_Alt_Task :: End');

Line: 1310

END Delete_ALL_Alt_Task;

Line: 1330

		Select distinct PPE1.Proj_element_id Source_Task_Id, PPE2.Proj_element_id Target_Task_Id
		From PA_PROJ_ELEMENTS PPE1, PA_PROJ_ELEMENTS PPE2
		Where PPE1.name=PPE2.name AND PPE1.Element_Number=PPE2.Element_Number AND
		PPE1.project_id=p_Source_Project_Id AND PPE2.project_id=p_Target_Project_Id
		and PPE1.Proj_element_id not in (Select pt.Task_Id from pa_tasks pt where pt.project_id=p_Source_Project_Id)
		and PPE2.Proj_element_id not in (Select pt.Task_Id from pa_tasks pt where pt.project_id=p_Target_Project_Id)
		and PPE1.OBJECT_TYPE=PPE2.OBJECT_TYPE and PPE1.OBJECT_TYPE='PA_TASKS'
		UNION
    	Select distinct PPE1.task_id Source_Task_Id, PPE2.task_id Target_Task_Id
		From PA_TASKS PPE1, PA_TASKS PPE2
		Where PPE1.task_name=PPE2.task_name AND PPE1.task_Number=PPE2.task_Number AND
		PPE1.project_id=p_Source_Project_Id AND PPE2.project_id=p_Target_Project_Id;