This my pl/sql PL/pgSQL function. I am facing syntax error in this function. Can you please help me to solve this problem.
ERROR: syntax error at or near "$2"
LINE 1: SELECT $1 FOR $2 IN(select abl.ka003_position_lk_id as ...
^
QUERY: SELECT $1 FOR $2 IN(select abl.ka003_position_lk_id as posit, sum(abl.nooflabors*abl.hours) as totalhours from ahcc_boq_labor abl where ahcc_boq_item_id= $3 group by abl.ka003_position_lk_id) LOOP $4 = $5
CONTEXT: SQL statement in PL/PgSQL function "ahcc_proj_budget_cpy_plan1" near line 83
********** Error **********
ERROR: syntax error at or near "$2"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "ahcc_proj_budget_cpy_plan1" near line 83
Here is my code:
CREATE OR REPLACE FUNCTION ahcc_proj_budget_cpy_plan1(p_pinstance_id character varying)
RETURNS void AS
$BODY$ DECLARE
v_Process CHAR(1);
v_project_id VARCHAR(32);
v_projbudget VARCHAR(32);
v_client VARCHAR(32);
v_petty NUMERIC;
v_org VARCHAR(32);
v_mrl NUMERIC;
v_hr NUMERIC;
v_eqp NUMERIC;
v_createdby VARCHAR(32);
v_updatedby VARCHAR(32);
v_record_id VARCHAR(32);
v_ResultStr VARCHAR(120);
v_status VARCHAR(32);
v_message VARCHAR(255);
v_userid VARCHAR(32);
v_wbs_id VARCHAR(32);
v_boq_id VARCHAR(32);
v_boqitem_id VARCHAR(32);
v_positionId VARCHAR(255);
v_totalhours VARCHAR(255);
v_mproduct_id VARCHAR(255);
v_quantity VARCHAR(255);
v_hours VARCHAR(255);
v_material VARCHAR(255);
v_mquantity VARCHAR(255);
Cur_boq RECORD;
Cur_wbs RECORD;
Cur_hr RECORD;
Cur_eqp RECORD;
Cur_mrl RECORD;
BEGIN
-- Update AD_PInstance by setting IsProcessing='Y'
RAISE NOTICE '%','Updating PInstance - Processing ' || p_PInstance_ID ;
PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ;
BEGIN
select record_id,ad_user_id into v_record_id,v_userid from ad_pinstance where ad_pinstance_id=p_PInstance_ID;
select ahcc_project_id,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID,
CREATEDBY, UPDATEDBY,process
into v_project_id,v_projbudget,v_client,v_org,v_createdby,v_updatedby,v_process
from ahcc_proj_budget where ahcc_proj_budget_id=v_Record_ID;
update ahcc_proj_budget set process='N' where Process='Y';
select count(*) into v_mrl from ahcc_proj_budget_mrl where ahcc_proj_budget_id=v_record_id;
select count(*) into v_hr from ahcc_proj_budget_hr where ahcc_proj_budget_id=v_record_id;
select count(*) into v_eqp from ahcc_proj_budget_eqp where ahcc_proj_budget_id=v_record_id;
IF (v_mrl<>0 OR v_hr<>0 OR v_eqp<>0) THEN
delete from ahcc_proj_budget_mrl where ahcc_proj_budget_id=v_record_id;
delete from ahcc_proj_budget_hr where ahcc_proj_budget_id=v_record_id;
delete from ahcc_proj_budget_eqp where ahcc_proj_budget_id=v_record_id;
END IF;
select count(*) into v_mrl from ahcc_proj_budget_mrl where ahcc_proj_budget_id=v_record_id;
select count(*) into v_hr from ahcc_proj_budget_hr where ahcc_proj_budget_id=v_record_id;
select count(*) into v_eqp from ahcc_proj_budget_eqp where ahcc_proj_budget_id=v_record_id;
IF (v_mrl=0 AND v_hr=0 AND v_eqp=0) THEN
FOR Cur_wbs IN (select apwbs.ahcc_project_wbs_id , apbi.ahcc_boq_item_id
from ahcc_project_boqitem apbi
left join (select ahcc_project_wbs_id from ahcc_project_wbs
where ahcc_project_wbs_id not in(select wbs_parent_id from ahcc_project_wbs)
and ahcc_project_id=v_project_id) apwbs
on apbi.ahcc_project_wbs_id=apwbs.ahcc_project_wbs_id)
LOOP
v_wbs_id = Cur_wbs.apwbs.ahcc_project_wbs_id;
v_boq_id = Cur_wbs.apbi.ahcc_boq_item_id;
FOR Cur_boq IN(SELECT ahcc_boq_item_id FROM connectby('ahcc_boq_item','ahcc_boq_item_id',
'boqitem_parentid','boqitem_name',v_boq_id,0 ,'/') AS
t(ahcc_boq_item_id text, boqc_parentid text, level int, branch text ,pos int))
LOOP
v_boqitem_id = Cur_boq.ahcc_boq_item_id
FOR Cur_hr IN(select abl.ka003_position_lk_id as posit, sum(abl.nooflabors*abl.hours) as totalhours
from ahcc_boq_labor abl
where ahcc_boq_item_id=v_boqitem_id
group by abl.ka003_position_lk_id)
LOOP
v_positionId = Cur_hr.posit;
v_totalhours = Cur_hr.totalhours;
INSERT INTO AHCC_Proj_budget_hr
(
AHCC_Proj_budget_hr_ID,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
CREATED, CREATEDBY, UPDATED, UPDATEDBY,ka003_position_lk_id,hourprice,totalprice,total_hour)
VALUES
(
GET_UUID(),v_projbudget,v_client,v_org,'Y',TO_DATE(NOW()),v_createdby,TO_DATE(NOW()),v_updatedby,
v_positionId,0,0,v_totalhours
);
END LOOP;
FOR Cur_eqp IN(select abe.m_product_id as product,sum(abe.equipment_quantity) as qty,sum(abe.noofhours) as hour
from ahcc_boq_equipment abe where ahcc_boq_item_id=v_boqitem_id
group by abe.m_product_id)
LOOP
v_mproduct_id = Cur_eqp.m_product_id;
v_quantity = Cur_eqp.qty;
v_hours = Cur_eqp.hour;
INSERT INTO AHCC_Proj_budget_eqp
(
AHCC_Proj_budget_eqp_ID,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
CREATED, CREATEDBY, UPDATED, UPDATEDBY,m_product_id,no_of_equipment,hour,hourprice,totalprice)
VALUES
(
GET_UUID(),v_projbudget,v_client,v_org,'Y',TO_DATE(NOW()),v_createdby,TO_DATE(NOW()),v_updatedby,
v_mproduct_id,v_quantity,v_hours,0,0
);
END LOOP;
FOR Cur_mrl IN(select abm.m_product_id, sum(abm.material_quantity) as qty from ahcc_boq_material abm
where ahcc_boq_item_id=v_boqitem_id
group by abm.m_product_id)
LOOP
v_material = Cur_mrl.m_product_id;
v_mquantity = Cur_mrl.qty;
INSERT INTO AHCC_Proj_budget_mrl
(
AHCC_Proj_budget_mrl_ID,ahcc_proj_budget_id, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE,
CREATED, CREATEDBY, UPDATED, UPDATEDBY,m_product_id,quantity,productprice,totalprice)
VALUES
(
GET_UUID(),v_projbudget,v_client,v_org,'Y',TO_DATE(NOW()),v_createdby,TO_DATE(NOW()),v_updatedby,
v_material,v_mquantity,0,0
);
END LOOP;
END LOOP;
END LOOP;
END IF;
RAISE NOTICE '%','Updating PInstance - Finished ' || v_Message ;
PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 1, v_Message) ;
RETURN;
END; -- BODY
EXCEPTION
WHEN OTHERS THEN
v_ResultStr:= '@ERROR=' || SQLERRM;
RAISE NOTICE '%',v_ResultStr ;
PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
RETURN;
END ; $BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION ahcc_proj_budget_cpy_plan1(character varying) OWNER TO tad;
