Requery form data block is empty after updating notification attributes
Hello,
we build a form to approve/reject open WF notifications.
The form data block is based on wf_notifications,
wf_local_user_roles, wf_notification_attributes and wf_comments and some additional PSB tables.
We use the following API's to perform the update:
wf_notification.SetAttrText( nid => l_notification_id
, aname => 'RESULT'
, avalue => l_result);
wf_notification.respond(nid => l_notification_id
,responder => name_in('FORM_GLOBALS.USER_NAME')
,respond_comment => name_in('FORM_GLOBALS.REDEN_AFWIJZING') );
First the query shows 3 notifications and after updating one notification as shown above a requery is performed on the datablock and no records are shown anymore.
If I remove the code wf_notification.SetAttrText then the 2 remaining notifications are shown.
It looks like the wf_notification.SetAttrText is conflicting with my view.
This is my view:
SELECT distinct 'B'||pbr.budget_revision_id mutatienummer
, pbr.budget_revision_id
, xp.dienst
, pbr.justification mutatiereden
, pbr.requestor aanvrager_id
, nvl(fu_req.DESCRIPTION, fu_req.USER_NAME) aanvrager
, pbr.CREATION_DATE aanvraag_datum
, wlur.USER_ORIG_SYSTEM_ID user_id
, wlur.user_name user_name
, wn.notification_id notification_id
, wn.subject subject
, wn.message_type wf_item_type
, pbr.ATTRIBUTE2 transactiesoort
, pbr.ATTRIBUTE4 categorie
, pbr.ATTRIBUTE5 raadsvoorstel
, SUBSTR(pbr.attribute1, 4) begrotingsstuk
, SUBSTR(pbr.attribute3, 4) status
, SUBSTR(pbr.attribute6, 4) dienstgroep
, SUBSTR(pbr.attribute7, 4) concerngroep
, decode(SUBSTR(pbr.attribute3, 4),'Bij D.C.',null,nvl(fu_from.DESCRIPTION, fu_from.USER_NAME)) Fiatteur_bij_DC
, decode(nvl(pbr.attribute9,'X'),nvl(pbr.attribute10,'Z'),'J','N') initiele_structuur_wijz
, decode(pbr.ATTRIBUTE2,'Structuurwijziging','J','N') structuurwijziging
, pbr.budget_group_id budget_group_id
, xrb.bedrijf aangevraagd_door_dienst
, wn.begin_date datum_melding
, pbr.attribute8 te_wijzigen_kst_dr_prod
, pbr.attribute9 oude_structuur
, pbr.attribute10 nieuwe_structuur
, pbr.attribute11 ingangsjaar_struct_wijz
, to_date(fnd_date.canonical_to_date(pbr.attribute12)) ingangsdatum_struct_wijz
, pbr.attribute13 looptijd_mutatie
from wf_notifications wn
, wf_local_user_roles wlur
, wf_notification_attributes wna
, psb_budget_revisions pbr
, fnd_user fu_req
, fnd_user fu_from
, wf_comments wc
, psb_budget_revision_lines pbl
, psb_budget_revision_accounts pbra
, gl_code_combinations gcc
, xxpsb_parameters_vw xp
, psb_budget_group_resp bgr
, xxpsb_respons_bedrijf_vw xrb
where wn.message_type = 'PSBBR'
and wn.message_name = 'NOTIFY_APPROVERS_OF_SUBMISSION'
and wn.status = 'OPEN'
and wlur.role_name = wn.recipient_role
and wna.notification_id = wn.notification_id
and wna.NAME = 'BUDGET_REVISION_ID'
and pbr.BUDGET_REVISION_ID = to_number(wna.number_value)
and wlur.USER_NAME = fnd_profile.value('USERNAME')
and fu_req.user_id = pbr.requestor
and fu_from.user_name(+) = wc.from_user
and wc.notification_id(+) = wn.notification_id
and pbr.budget_revision_id = pbl.budget_revision_id
and pbl.budget_revision_acct_line_id = pbra.budget_revision_acct_line_id
and pbra.code_combination_id = gcc.code_combination_id
and gcc.segment1 like xp.code||'%'
and bgr.budget_group_id = pbr.budget_group_id
and bgr.responsibility_id(+) = xrb.responsibility_id
and bgr.responsibility_type = 'R'