219: begin
220: begin
221: select cert_id
222: into mycert_id
223: from wf_dig_certs
224: where cert_type = 'PSIG'
225: and fingerprint = P_User;
226: exception
227: when no_data_found then mycert_id := -1;
229: end;
230:
231: if (mycert_id = -1) then
232:
233: insert into wf_dig_certs (cert,cert_id,cert_type,parent_cert_id,
234: owner_id, owner_domain, valid, sot_flag, intermediate_flag,
235: fingerprint, expire)
236: (select P_User, wf_dig_certs_s.nextval,'PSIG', wf_dig_certs_s.currval,
237: U.User_ID, 'U', 'Y', 'Y', 'N', P_User, null
232:
233: insert into wf_dig_certs (cert,cert_id,cert_type,parent_cert_id,
234: owner_id, owner_domain, valid, sot_flag, intermediate_flag,
235: fingerprint, expire)
236: (select P_User, wf_dig_certs_s.nextval,'PSIG', wf_dig_certs_s.currval,
237: U.User_ID, 'U', 'Y', 'Y', 'N', P_User, null
238: from fnd_user U
239: where user_name = P_User
240: and not exists
238: from fnd_user U
239: where user_name = P_User
240: and not exists
241: (select cert_id
242: from wf_dig_certs
243: where cert_type = 'PSIG'
244: and fingerprint = P_User)
245: );
246:
245: );
246:
247: select cert_id
248: into mycert_id
249: from wf_dig_certs
250: where cert_type = 'PSIG'
251: and fingerprint = P_User;
252: end if;
253:
330:
331: /* get info from cert table */
332: Select Owner_domain, Owner_ID
333: into act_type, act_id
334: From WF_Dig_Certs
335: where Cert_ID = act_cert_id;
336:
337: /* messy code to determine authorization. */
338: if (req_type = 'W') then
625: -- existing cert_id else return -1
626: if (existing_certid <> -1) then
627: SELECT owner_id
628: INTO existing_userid
629: FROM wf_dig_certs
630: WHERE cert_id = existing_certid;
631:
632: if (existing_userid = v_user_id) then
633: return existing_certid;
636: end if;
637: end if;
638:
639: --get the next value from the sequence for the cert_id
640: SELECT wf_dig_certs_s.nextval
641: INTO seqVal
642: FROM DUAL;
643:
644: --insert the certificate into the table
641: INTO seqVal
642: FROM DUAL;
643:
644: --insert the certificate into the table
645: insert into WF_DIG_CERTS(CERT, CERT_ID, CERT_TYPE, PARENT_CERT_ID,
646: OWNER_ID, OWNER_DOMAIN, VALID, SOT_FLAG,
647: INTERMEDIATE_FLAG, FINGERPRINT, EXPIRE,
648: SECURITY_GROUP_ID, SUBJECTDN)
649: values( p_cert, seqVal, p_cert_type, p_parent_cert_id,
680:
681: select
682: CERT into v_certificate
683: from
684: WF_DIG_CERTS
685: where CERT_TYPE='X509' and CERT_ID=p_cert_id;
686:
687: return v_certificate;
688:
703:
704: --cursor for getting all the certificates with the given fingerprint
705: cursor cert_cursor is
706: select CERT_ID, CERT
707: from WF_DIG_CERTS
708: where CERT_TYPE = 'X509'
709: and FINGERPRINT = P_Fingerprint;
710:
711: V_Certificate CLOB;