While creating procedure, I am getting this error:
Error(6384,1): PLS-00103: Encountered the symbol "END"
PROCEDURE INS_WORKFLOW_FTTX_PARTIAL
(
PJOB_PROGRESS_ID IN TBL_FIBER_INV_JOB_PROGRESS.JOB_PROGRESS_ID%TYPE,
PSTATUS_ID IN TBL_FIBER_INV_JOB_PROGRESS.STATUS_ID%TYPE,
PFSA_UG_ACTUAL IN TBL_FIBER_INV_JOB_PROGRESS.FSA_UG_ACTUAL%TYPE,
PFSA_AR_ACTUAL IN TBL_FIBER_INV_JOB_PROGRESS.FSA_AERIAL_ACTUAL%TYPE,
PMDU_ACTUAL_LENGTH IN TBL_FIBER_INV_JOB_PROGRESS.FSA_MDU_ACTUAL%TYPE,
PAPPROVED_BY IN TBL_FIBER_INV_JOB_PROGRESS.APPROVED_BY%TYPE,
PREJECTED_BY IN TBL_FIBER_INV_JOB_PROGRESS.REJECTED_BY%TYPE,
PAPPROV_REJECT_REMARK IN TBL_FIBER_INV_JOB_PROGRESS.APPROV_REJECT_REMARK%TYPE,
PUMS_GROUP_ASS_BY_ID IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_BY_ID%TYPE,
PUMS_GROUP_ASS_BY_NAME IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_BY_NAME%TYPE,
PUMS_GROUP_ASS_TO_ID IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_TO_ID%TYPE,
PUMS_GROUP_ASS_TO_NAME IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_TO_NAME%TYPE,
PHOTO_ACCEPTANCE_DATE IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_ACCEPTENCE_DATE%TYPE,
PSPVENDORXML IN XMLTYPE,
POUTMSG OUT NVARCHAR2
)
AS
VCNTSPVENCNT NUMBER :=0;
VJOB_ID NUMBER := 0;
BEGIN
SELECT JOB_ID INTO VJOB_ID FROM TBL_FIBER_INV_JOB_PROGRESS WHERE JOB_PROGRESS_ID = PJOB_PROGRESS_ID;
SELECT COUNT(JOB_PROGRESS_ID)
INTO VCOUNTERCHILD FROM TBL_FIBER_INV_JOB_PROGRESS c INNER JOIN TBL_FIBER_INV_JOBS p
ON c.job_id=p.job_id and c.job_id = VJOB_ID and to_char(p.CREATED_DATE,'dd/mm/yyyy') = to_char(sysdate + 1,'dd/mm/yyyy') ;
IF VCOUNTERCHILD = 0
THEN BEGIN
INSERT INTO TBL_FIBER_INV_JOB_PROGRESS
(
JOB_ID,
STATUS_ID,
HOTO_OFFERED_DATE,
APPROV_REJECT_REMARK,
HOTO_OFFERED_LENGTH,
NE_SPAN_LENGTH,
CREATED_BY,
UMS_GROUP_ASS_BY_ID,
UMS_GROUP_ASS_BY_NAME,
UMS_GROUP_ASS_TO_ID,
UMS_GROUP_ASS_TO_NAME,
UMS_GROUP_ASS_TO_DATE,
MODIFIED_BY,
MODIFIED_DATE,
APPROVED_BY,
APPROVED_DATE,
FSA_UG_ACTUAL,
FSA_AERIAL_ACTUAL,
FSA_MDU_ACTUAL
)
VALUES
(
VJOB_ID,
PSTATUS_ID,
TO_DATE(PHOTO_OFFERED_DATE,'DD/MM/YYYY'),
PAPPROV_REJECT_REMARK,
PHOTO_OFFERED_LENGTH,
PNELENGTH,
PCREATED_BY,
PUMS_GROUP_ASS_BY_ID,
PUMS_GROUP_ASS_BY_NAME,
PUMS_GROUP_ASS_TO_ID,
PUMS_GROUP_ASS_TO_NAME,
SYSDATE,
PAPPROVED_BY,
SYSDATE,
PCREATED_BY,
SYSDATE,
PFSA_UG_ACTUAL,
PFSA_AERIAL_ACTUAL,
PFSA_MDU_ACTUAL
)RETURNING JOB_PROGRESS_ID INTO PJOB_PROGRESS_ID;
END;
IF PJOB_PROGRESS_ID > 0
THEN
POUTMSG := 'SUCCESS|'||PJOB_PROGRESS_ID;
UPDATE TBL_FIBER_INV_MSTDATA SET REQUEST_ID = VJOB_ID WHERE SPAN_LINK_ID = SPLKID AND MAINTENANCE_ZONE_CODE = PMAINTENANCEZONECODE;
COMMIT;
ELSE
POUTMSG := 'WARNING|Record not created - Already a child record for job id '||PJOB_PROGRESS_ID_OLD||' ';
END IF;
END;
END;
END INS_WORKFLOW_FTTX_PARTIAL;
It is about poor formatting. If you indented code as you should, you'd easier spot what's wrong (missing END IF, superfluous END):
PROCEDURE ins_workflow_fttx_partial (
pjob_progress_id IN tbl_fiber_inv_job_progress.job_progress_id%TYPE,
pstatus_id IN tbl_fiber_inv_job_progress.status_id%TYPE,
pfsa_ug_actual IN tbl_fiber_inv_job_progress.fsa_ug_actual%TYPE,
pfsa_ar_actual IN tbl_fiber_inv_job_progress.fsa_aerial_actual%TYPE,
pmdu_actual_length IN tbl_fiber_inv_job_progress.fsa_mdu_actual%TYPE,
papproved_by IN tbl_fiber_inv_job_progress.approved_by%TYPE,
prejected_by IN tbl_fiber_inv_job_progress.rejected_by%TYPE,
papprov_reject_remark IN tbl_fiber_inv_job_progress.approv_reject_remark%TYPE,
pums_group_ass_by_id IN tbl_fiber_inv_job_progress.ums_group_ass_by_id%TYPE,
pums_group_ass_by_name IN tbl_fiber_inv_job_progress.ums_group_ass_by_name%TYPE,
pums_group_ass_to_id IN tbl_fiber_inv_job_progress.ums_group_ass_to_id%TYPE,
pums_group_ass_to_name IN tbl_fiber_inv_job_progress.ums_group_ass_to_name%TYPE,
photo_acceptance_date IN tbl_fiber_inv_job_progress.hoto_acceptence_date%TYPE,
pspvendorxml IN XMLTYPE,
poutmsg OUT NVARCHAR2
) AS
vcntspvencnt NUMBER := 0;
vjob_id NUMBER := 0;
BEGIN
SELECT
job_id
INTO vjob_id
FROM
tbl_fiber_inv_job_progress
WHERE
job_progress_id = pjob_progress_id;
SELECT
COUNT(job_progress_id)
INTO vcounterchild
FROM
tbl_fiber_inv_job_progress c
INNER JOIN tbl_fiber_inv_jobs p ON c.job_id = p.job_id
AND c.job_id = vjob_id
AND to_char(p.created_date, 'dd/mm/yyyy') = to_char(sysdate + 1, 'dd/mm/yyyy');
IF vcounterchild = 0 THEN
BEGIN
INSERT INTO tbl_fiber_inv_job_progress (
job_id,
status_id,
hoto_offered_date,
approv_reject_remark,
hoto_offered_length,
ne_span_length,
created_by,
ums_group_ass_by_id,
ums_group_ass_by_name,
ums_group_ass_to_id,
ums_group_ass_to_name,
ums_group_ass_to_date,
modified_by,
modified_date,
approved_by,
approved_date,
fsa_ug_actual,
fsa_aerial_actual,
fsa_mdu_actual
) VALUES (
vjob_id,
pstatus_id,
to_date(photo_offered_date, 'DD/MM/YYYY'),
papprov_reject_remark,
photo_offered_length,
pnelength,
pcreated_by,
pums_group_ass_by_id,
pums_group_ass_by_name,
pums_group_ass_to_id,
pums_group_ass_to_name,
sysdate,
papproved_by,
sysdate,
pcreated_by,
sysdate,
pfsa_ug_actual,
pfsa_aerial_actual,
pfsa_mdu_actual
) RETURNING job_progress_id INTO pjob_progress_id;
END;
IF pjob_progress_id > 0 THEN
poutmsg := 'SUCCESS|' || pjob_progress_id;
UPDATE tbl_fiber_inv_mstdata
SET
request_id = vjob_id
WHERE
span_link_id = splkid
AND maintenance_zone_code = pmaintenancezonecode;
COMMIT;
ELSE
poutmsg := 'WARNING|Record not created - Already a child record for job id '
|| pjob_progress_id_old
|| ' ';
END IF;
END IF;
END ins_workflow_fttx_partial;