Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-06550: line 4, column 18: PLS-00103: Encountered the symbol "IF" when e

896188Aug 13 2012 — edited Aug 13 2012
I am running the error as below while running the code. Can you please help me how to fix it
ORA-06550: line 4, column 18: PLS-00103: Encountered the symbol "IF" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set spe
DECLARE
    l_query          VARCHAR2(32676);
BEGIN
 
    l_query := q'!  SELECT DISTINCT  
	                       apex_item.checkbox( 1
                                              ,wo.wip_entity_id
                                              ,'onchange="javascript:CheckedAll(); setChkboxVal(this);"'
                                              ,:P0_CHK_LIST
                                              ,':'
                                            ) AS selecter
						  -- ,wo.work_order_status AS Status
                                           ,NVL((select STATUS from xxdl.xxdl_eam_its_pta_rollover WHERE wip_entity_id = wo.wip_entity_id),'') AS Status
						   ,wo.wip_entity_name AS "Work Order Number"
						   ,msn.asset_group_description AS "Asset Group"
						   ,wo.asset_number AS "Asset Number"
						   ,cust_prof.name As "Profile Class"
						   ,wo.wip_entity_id As "wip_entity_id"
                                                   ,wo.asset_group_id 
						   ,budget_department.project_id
						   ,budget_department.project_number
						   ,budget_department.task_id
						   ,budget_department.task_number
						   ,budget_department.award_id
						   ,budget_department.award_number 
				    FROM xxdl.xxdl_eam_wo_txn_accounts txn_acct
                                                ,apps.eam_work_orders_v wo
						,apps.mtl_eam_asset_numbers_v msn
						,apps.hz_cust_profile_classes cust_prof
						,(SELECT tasks.task_id
								,tasks.project_id
								,valid_pta.award_id
								,valid_pta.award_number
								,(SELECT DISTINCT project_number
								  FROM xxdl.xxdl_valid_pta_combinations
								  WHERE project_id = tasks.project_id
								  ) project_number
								,tasks.task_number
								,tasks.task_name
								,tasks.description
								,tasks.service_type_code
								,orgs.organization_id 
								,orgs.name department_code
								,orgs.attribute1 department_name
								,orgs.attribute4 budget_unit
						  FROM pa.pa_tasks tasks
							  ,xxdl.xxdl_valid_pta_combinations valid_pta
							  ,hr.hr_all_organization_units orgs
						  WHERE orgs.organization_id = tasks.carrying_out_organization_id
							AND valid_pta.task_id    = tasks.task_id
						 ) budget_department
					WHERE txn_acct.organization_id    = wo.organization_id 
					  AND msn.current_organization_id = wo.organization_id 
					  AND wo.organization_id          = :app_organization_id
					  AND txn_acct.cost_bucket        ='MATERIAL'
					  AND txn_acct.work_order_id      = wo.wip_entity_id
                                          AND msn.serial_number           = wo.asset_number
					 -- AND wo.work_order_status        = 'Draft'
					  AND budget_department.project_id = txn_acct.project_id
					  AND budget_department.task_id    = txn_acct.task_id
					  AND budget_department.award_id   = txn_acct.award_id
					  AND cust_prof.attribute1         = budget_department.budget_unit
					  AND cust_prof.status             = 'A'
					  AND NVL(cust_prof.attribute2,budget_department.department_code) = budget_department.department_code
                                          AND wo.wip_entity_id  not in (select wip_entity_id from xxdl.xxdl_eam_its_pta_rollover where status != 'DRAFT')!';  

 IF :P2_PROJECT IS NOT NULL THEN
       l_query := l_query ||q'[ AND trim(upper(budget_department.project_number)) =:P2_PROJECT ]';

  END IF;
		
		
	RETURN l_query;
	
EXCEPTION
WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20001,'The system faced an unhandled exception. Please contact System Administrators for resolution with the following error code :ITS-201 ('''||SQLERRM||''').'   );    
END;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 10 2012
Added on Aug 13 2012
2 comments
1,058 views