I am trying to return a json result back. But, getting the ORA-02000: missing ) error. Below is the code, I have checked the opening and closing brackets '(' and ')'. Please advise.
with sesa_agent_person_appl as (
select
uia.APPLICATION_SEQ_NUM,
uia.person_id,
uia.agent_cd,
uiasd.applicant_id,
uiasd.apaa_application_number,
acai.admission_appl_number
from abc_int_appl uia
inner join abc_int_appl_sesa_dtl uiasd
on uia.APPLICATION_SEQ_NUM = uiasd.APPLICATION_SEQ_NUM
inner join ADM_COURSE_APPL_INSTANCE acai
on acai.apaa_application_number = uiasd.apaa_application_number
)
,agent_person_appl as (
select
aia.person_id,
aia.admission_appl_number
from admission_international_agent aia
where aia.primary_agency_ind = 'Y'
and aia.AGENT_ORG_UNIT_CD = v_query.AGENT_ORG_UNIT_CD
and (v_query.PERSON_ID is null or aia.PERSON_ID = v_query.PERSON_ID)
and (v_query.applicant_id is null or exists (
select pe_person_id
from alternate_person_id
where person_id_type = 'APPLCNT-ID'
and api_person_id = v_query.applicant_id
and pe_person_id = aia.PERSON_ID
))
union
select
person_id,
admission_appl_number
from sesa_agent_person_appl sapa
where sapa.agent_cd = v_query.AGENT_ORG_UNIT_CD
and (v_query.PERSON_ID is null or sapa.PERSON_ID = v_query.PERSON_ID)
and (v_query.applicant_id is null or sapa.applicant_id = v_query.applicant_id)
),
agent_person as ( /* all person with this agent */
select distinct person_id from agent_person_appl
),
agent_person_appl_detail as (
select
aa.*,
FIRST_VALUE(aa.doc_status_rank) OVER (PARTITION BY aa.person_id ORDER BY aa.appl_status_rank ASC, aa.doc_status_rank ASC) person_doc_rank,
FIRST_VALUE(aa.doc_status_rank) OVER (PARTITION BY aa.person_id, aa.application_number ORDER BY aa.appl_status_rank ASC, aa.doc_status_rank ASC ) appln_doc_rank --UAP# 343 Get the person application document status by application and the documents ranks.
from ABC_APPLY_APPLIC_APPL_STATUS_V aa
where exists ( /* filter application with current agent */
select 1 from agent_person_appl apa
where aa.person_id = apa.person_id
AND aa.admission_appl_number = apa.admission_appl_number
)
),
doc_status as (
select
ads.ADM_DOC_STATUS,
ads.EXPLANATION DOC_STATUS_EXPL,
uar.rank
from ABC_APPLY_ADS_REF uar, adm_doc_status ads
where uar.ADM_DOC_STATUS = ads.ADM_DOC_STATUS
)
SELECT JSON_OBJECT(
--document (
'Persons' is (
json_arrayagg(
'Person' is (
'PersonId' is ape.person_id,
'Surname' IS pe.surname,
'GivenNames' IS pe.given_names,
'PeDocStatusExpl' IS docs.doc_status_expl,
'PeOutcomeStatusExpl' IS uaasv.EXPL,
'PeOutcomeStatusRank' IS uaasv.RANK,
'ApplicantId' IS apapp.applicant_id,
'EmailAddress' IS apapp.email_address,
'Citizenship' IS
case when exists(
select 1 from abc_adm_letter_ref ualr
where data_type_cd = 'INT_CIT_CD'
AND logical_delete_dt IS NULL
AND ualr.data_type_value = ps.citizenship_cd
) THEN citizenship_international ELSE citizenship_domestic END,
'Applications' IS (
SELECT json_arrayagg(
--JSON_OBJECT(
'Application' IS (
'ApaaApplNumber' IS apaa_appl_number,
'AdmissionApplNumber' IS admission_appl_number,
'ApplicantId' IS pe_appl.applicant_id,
'SubmissionDt' IS to_char(submission_dt,format_date),
'ApplDocStatusExpl' IS ds.doc_status_expl,
'ApplOutcomeStatusExpl' IS uaasv.EXPL,
'ApplOutcomeStatusRank' IS uaasv.RANK,
(/* UAP-181 */ TODO CHECK
SELECT 'SApplicationType' IS apaa.s_application_type
FROM ap_admission_application appa
WHERE apaa.application_number = pe_appl.apaa_appl_number
AND apaa.applicant_id = pe_appl.applicant_id /* APAA_PK: APPLICANT_ID APPLICATION_NUMBER */
),
'Courses' IS (
SELECT json_arrayagg(
--JSON_OBJECT(
'Course' IS (
'CourseCd' IS course_cd,
'CrvVersionNumber' IS crv_version_number,
'CourseTitle' IS course_title,
'SequenceNumber' IS sequence_number,
'PreferenceNumber' IS preference_number,
'Start' IS start_tp,
'UnitSetTitle' IS unit_set_title,
'LocationCd' IS location_cd,
'AdmDocStatus' IS adm_doc_status,
'DocStatusExpl' IS doc_status_expl,
'AdmEntryQualStatus' IS adm_entry_qual_status,
'EntryQualStatusExpl' IS entry_qual_status_expl,
-- xmlelement("AdmOutcomeStatus", adm_outcome_status),
-- xmlelement("OutcomeStatusExpl", outcome_status_expl),
'OutcomeStatusExpl' IS uaasv.EXPL,
'OutcomeStatusRank' IS uaasv.RANK,
'AdmOutcomeStatus' IS adm_outcome_status,
'AdmOfferRespStatus' IS adm_offer_resp_status,
'OfferRespExpl' IS offer_resp_expl,
'OfferConditions' IS aad.cndtnl_offer_cndtn,
'ConditionsMetInd' IS conditions_met_ind
)--Course
)
from agent_person_appl_detail aad
LEFT OUTER JOIN abc_apply_appl_status_v uaasv ON uaasv.RANK = aad.appl_status_rank
WHERE aad.person_id = ape.person_id
AND aad.application_number = pe_appl.application_number
) --Courses
) --Application
)
FROM
(
select
APPLICATION_NUMBER,
max(applicant_id) applicant_id,
max(apaa_appl_number) apaa_appl_number,
max(admission_appl_number) admission_appl_number,
max(SUBMISSION_DT) SUBMISSION_DT,
min(aa.appln_doc_rank) appl_doc_rank,
min(aa.appl_status_rank) appl_appl_rank
from agent_person_appl_detail aa
where aa.person_id = ape.person_id
GROUP BY application_number /* nvl(apaa_appl_number, admission_appl_number)*/
) pe_appl
LEFT OUTER JOIN doc_status ds ON ds.RANK = pe_appl.appl_doc_rank
LEFT OUTER JOIN abc_apply_appl_status_v uaasv ON uaasv.RANK = pe_appl.appl_appl_rank
) --Applications Line 93
)--Person
)
) --Persons
--)
--version '1.0'
) AS json_in_clob INTO p_result_xml
from agent_person ape
inner join person pe on pe.person_id = ape.person_id
inner join (
select
person_id,
max(applicant_id) applicant_id,
min(person_doc_rank) pe_doc_status_rank,
min(APPL_STATUS_RANK) pe_appl_status_rank
from agent_person_appl_detail
group by person_id
) pes on pes.person_id = ape.person_id
left outer join doc_status docs on docs.rank = pes.pe_doc_status_rank
left outer join ABC_APPLY_APPL_STATUS_V uaasv on uaasv.rank = pes.pe_appl_status_rank
left outer join ap_applicant apapp on apapp.applicant_id = pes.applicant_id
left outer join person_statistics ps on ape.person_id = ps.person_id /* UAP-180 */
AND TRUNC(sysdate) BETWEEN ps.start_dt AND nvl(ps.end_dt, sysdate) -- CLST-2028
;
p_message_number := 0;
p_message := '';
/* log outcome*/
ORSK_ABC_APPLY_FUNC.log_outcome(
P_LOG_SN => v_new_log_sn,
P_OUTCOME => v_outcome,
P_OUTCOME_DETAIL => 'p_message_number = [' || p_message_number || '], '
|| 'p_message = ['|| p_message ||'],'
);