[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PSIG_STATUS from EDR_PSIG_DOCUMENTS
WHERE DOCUMENT_ID = P_DOCUMENT_ID;
SELECT SIGNATURE_STATUS from EDR_PSIG_DETAILS
WHERE SIGNATURE_ID = P_SIGNATURE_ID;
SELECT count(*) from FND_USER
WHERE USER_NAME = P_USER;
select category_id into l_eres_category_id
from fnd_document_categories_vl
where name = 'ERES';
select category_id into l_category_id
from fnd_document_categories_vl
where name = l_category_name;
X_last_update_login => l_login_id,
X_program_application_id => null,
X_program_id => null,
X_request_id => null,
X_automatically_added_flag => 'N',
--Bug 4006844: Start
--Pass the category ID
X_from_category_id => l_category_id,
--Bug 4006844: End
X_to_category_id => l_eres_category_id);
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
select EDR_PSIG_DOCUMENTS_S.nextval into l_document_id from dual;
/* Insert the document in pending status */
/* Recieve the CLOB */
l_XML:=P_PSIG_XML;
last_update_date => l_last_update_date ,
last_updated_by => l_last_updated_by ,
last_update_login => l_last_update_login
);
INSERT into EDR_PSIG_DOCUMENTS
(
DOCUMENT_ID ,
PSIG_XML ,
PSIG_DOCUMENT ,
PSIG_DOCUMENTFORMAT ,
PSIG_TIMESTAMP ,
PSIG_TIMEZONE ,
DOCUMENT_REQUESTER ,
DOC_REQ_DISP_NAME ,
PSIG_STATUS ,
PSIG_SOURCE ,
EVENT_NAME ,
--Bug 4672801: start
--EVENT_KEY
EVENT_KEY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
--Bug 4672801: end
)
values
(
l_document_id ,
L_XML ,
l_document ,
P_PSIG_DOCUMENTFORMAT ,
sysdate ,
l_server_timezone ,
P_PSIG_REQUESTER ,
L_DOC_REQ_DISP_NAME ,
'PENDING' ,
P_PSIG_SOURCE ,
P_EVENT_NAME ,
--Bug 4672801: start
--P_EVENT_KEY
P_EVENT_KEY,
l_CREATION_DATE,
l_CREATED_BY,
l_LAST_UPDATE_DATE,
l_LAST_UPDATED_BY,
l_LAST_UPDATE_LOGIN
--Bug 4672801: end
);
EDR_TRANS_ACKN_PVT.INSERT_ROW
( p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_TRUE ,
p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data ,
p_erecord_id => l_document_id ,
p_trans_status => EDR_CONSTANTS_GRP.g_no_ack_status ,
p_ackn_by => null ,
p_ackn_note => null ,
x_ackn_id => l_ackn_id
);
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
select EDR_PSIG_DOCUMENTS_S.nextval into l_document_id from dual;
last_update_date => l_last_update_date ,
last_updated_by => l_last_updated_by ,
last_update_login => l_last_update_login
);
INSERT into EDR_PSIG_DOCUMENTS
(DOCUMENT_ID,
PSIG_STATUS,
PSIG_TIMESTAMP,
--Bug 4672801: start
--PSIG_TIMEZONE
PSIG_TIMEZONE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
--Bug 4672801: end
)
values
(l_document_id,
'PENDING',
sysdate,
--Bug 4672801: start
--l_server_timezone
l_server_timezone,
l_CREATION_DATE,
l_CREATED_BY,
l_LAST_UPDATE_DATE,
l_LAST_UPDATED_BY,
l_LAST_UPDATE_LOGIN
--Bug 4672801: end
);
EDR_TRANS_ACKN_PVT.INSERT_ROW
( p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_TRUE ,
p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data ,
p_erecord_id => l_document_id ,
p_trans_status => EDR_CONSTANTS_GRP.g_no_ack_status ,
p_ackn_by => null ,
p_ackn_note => null ,
x_ackn_id => l_ackn_id
);
L_LAST_UPDATE_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER;
SELECT count(*) from EDR_PSIG_DETAILS
WHERE document_id =P_DOCUMENT_ID AND
SIGNATURE_STATUS='PENDING';
UPDATE EDR_PSIG_DOCUMENTS
set PSIG_STATUS=UPPER(p_STATUS),
--Bug 4672801: start
last_update_date = sysdate,
last_updated_by = fnd_global.user_id(),
last_update_login = fnd_global.login_id()
--Bug 4672801: end
where DOCUMENT_ID=P_DOCUMENT_ID;
EDR_PSIG.UPDATE_PSIG_USER_DETAILS( P_DOCUMENT_ID => P_DOCUMENT_ID);
/* Document Update Procedure
IN:
PSIG_XML
PSIG_DOCUMENT
PSIG_DOCUMENTFORMAT
PSIG_REQUESTER
PSIG_SOURCE
EVENT_NAME
EVENT_KEY
DOCUMENT_ID
Description :
This procedure will update a docuemnt
*/
PROCEDURE updateDocument
(
P_DOCUMENT_ID IN NUMBER,
P_PSIG_XML IN CLOB DEFAULT NULL,
P_PSIG_DOCUMENT IN CLOB DEFAULT NULL,
P_PSIG_DOCUMENTFORMAT IN VARCHAR2 DEFAULT NULL,
P_PSIG_REQUESTER IN VARCHAR2,
P_PSIG_SOURCE IN VARCHAR2 DEFAULT NULL,
P_EVENT_NAME IN VARCHAR2 DEFAULT NULL,
P_EVENT_KEY IN VARCHAR2 DEFAULT NULL,
p_WF_NID IN NUMBER DEFAULT NULL,
P_ERROR OUT NOCOPY NUMBER,
P_ERROR_MSG OUT NOCOPY VARCHAR2
) IS
l_document_id NUMBER;
update EDR_PSIG_DOCUMENTS
set
PSIG_XML=l_xml,
PSIG_DOCUMENT=l_document,
PSIG_DOCUMENTFORMAT=P_PSIG_DOCUMENTFORMAT,
DOCUMENT_REQUESTER=p_PSIG_REQUESTER,
DOC_REQ_DISP_NAME=DECODE(DOC_REQ_DISP_NAME, NULL,
L_DOC_REQ_DISP_NAME, DOC_REQ_DISP_NAME),
PSIG_SOURCE=P_PSIG_SOURCE,
EVENT_NAME=P_EVENT_NAME,
EVENT_KEY=P_EVENT_KEY,
--Bug 4672801: start
last_update_date = sysdate,
last_updated_by = fnd_global.user_id(),
last_update_login = fnd_global.login_id()
--Bug 4672801: end
where DOCUMENT_ID=P_DOCUMENT_ID;
END updateDocument;
SELECT count(*) from EDR_PSIG_DETAILS
WHERE document_id =P_DOCUMENT_ID AND
SIGNATURE_STATUS='PENDING';
UPDATE EDR_PSIG_DOCUMENTS
set PSIG_STATUS='COMPLETE',
--Bug 4672801: start
last_update_date = sysdate,
last_updated_by = fnd_global.user_id(),
last_update_login = fnd_global.login_id()
--Bug 4672801: end
where DOCUMENT_ID=P_DOCUMENT_ID;
UPDATE EDR_PSIG_DOCUMENTS
set PSIG_STATUS='CANCEL',
--Bug 4672801: start
last_update_date = sysdate,
last_updated_by = fnd_global.user_id(),
last_update_login = fnd_global.login_id()
--Bug 4672801: end
where DOCUMENT_ID=P_DOCUMENT_ID;
Select count(*)
from EDR_PSIG_DETAILS
where document_id=p_document_id and
USER_NAME=P_USER_NAME and
NVL(ORIGINAL_RECIPIENT,0)=NVL(P_ORIGINAL_RECIPIENT,0);
Select count(*)
from EDR_PSIG_DETAILS
where document_id=p_document_id and
USER_NAME=P_USER_NAME and
NVL(ORIGINAL_RECIPIENT,0)=NVL(P_ORIGINAL_RECIPIENT,0) and
NVL(ADHOC_STATUS,'0') <> 'DELETED';
select (nvl(max(signature_sequence),0) +1) into l_signer_seq from edr_psig_details
where document_id = P_DOCUMENT_ID;
IF (P_ADHOC_STATUS not in ('ADDED','DELETED') ) then
fnd_message.set_name('EDR','EDR_PSIG_INVALID_ADHOC_STATUS');
select EDR_PSIG_DETAILS_S.nextval into l_signature_id from dual;
/* Insert signature Details */
--Bug 3330240 : Start
--Inert the adhoc_status, signature_sequence also
-- and also the 5 WHO columns
/*
INSERT into EDR_PSIG_DETAILS
(
SIGNATURE_ID,
DOCUMENT_ID,
USER_NAME,
ORIGINAL_RECIPIENT,
SIGNATURE_OVERRIDING_COMMENTS,
SIGNATURE_STATUS
)
values
(
L_SIGNATURE_ID,
P_DOCUMENT_ID,
P_USER_NAME,
P_ORIGINAL_RECIPIENT,
P_OVERRIDING_COMMENTS,
'PENDING'
);
INSERT into EDR_PSIG_DETAILS
(
SIGNATURE_ID,
DOCUMENT_ID,
USER_NAME,
ORIGINAL_RECIPIENT,
SIGNATURE_OVERRIDING_COMMENTS,
SIGNATURE_STATUS,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN ,
ADHOC_STATUS,
SIGNATURE_SEQUENCE
)
values
(
L_SIGNATURE_ID,
P_DOCUMENT_ID,
P_USER_NAME,
P_ORIGINAL_RECIPIENT,
P_OVERRIDING_COMMENTS,
'PENDING',
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
P_ADHOC_STATUS,
l_signer_seq
);
select Signature_id from EDR_PSIG_DETAILS
where DOCUMENT_ID=P_DOCUMENT_ID AND
decode(P_ORIGINAL_RECIPIENT,NULL,USER_NAME,ORIGINAL_RECIPIENT)
= nvl(P_ORIGINAL_RECIPIENT,P_USER_NAME)
AND SIGNATURE_STATUS <> 'COMPLETE';
select Signature_id from EDR_PSIG_DETAILS
where DOCUMENT_ID=P_DOCUMENT_ID AND
decode(P_ORIGINAL_RECIPIENT,NULL,USER_NAME,ORIGINAL_RECIPIENT)
= nvl(P_ORIGINAL_RECIPIENT,P_USER_NAME)
AND SIGNATURE_STATUS not in ('COMPLETE','CANCEL');
select count(*) into l_pending_count from edr_psig_details
where signature_status = 'PENDING'
and signature_sequence < (select signature_sequence
from edr_psig_details
where signature_id = l_signature_id)
and document_id = P_DOCUMENT_ID
and adhoc_status <> 'DELETED';
/* UPDATE signature Details */
/*
UPDATE EDR_PSIG_DETAILS
SET
EVIDENCE_STORE_ID=P_EVIDENCE_STORE_ID,
USER_RESPONSE=P_USER_RESPONSE,
ORIGINAL_RECIPIENT=nvl(P_ORIGINAL_RECIPIENT,ORIGINAL_RECIPIENT),
USER_NAME=P_USER_NAME,
SIGNATURE_OVERRIDING_COMMENTS=SIGNATURE_OVERRIDING_COMMENTS||' '||P_OVERRIDING_COMMENTS,
SIGNATURE_TIMESTAMP=SYSDATE,
SIGNATURE_TIMEZONE=l_server_timezone,
SIGNATURE_STATUS='COMPLETE'
WHERE DOCUMENT_ID=P_DOCUMENT_ID AND
decode(P_ORIGINAL_RECIPIENT,NULL,USER_NAME,ORIGINAL_RECIPIENT)
=nvl(P_ORIGINAL_RECIPIENT,P_USER_NAME);
UPDATE EDR_PSIG_DETAILS
SET
EVIDENCE_STORE_ID=P_EVIDENCE_STORE_ID,
USER_RESPONSE=P_USER_RESPONSE,
ORIGINAL_RECIPIENT=nvl(P_ORIGINAL_RECIPIENT,ORIGINAL_RECIPIENT),
USER_NAME=P_USER_NAME,
SIGNATURE_OVERRIDING_COMMENTS=SIGNATURE_OVERRIDING_COMMENTS||' '||P_OVERRIDING_COMMENTS,
SIGNATURE_TIMESTAMP=SYSDATE,
SIGNATURE_TIMEZONE=l_server_timezone,
SIGNATURE_STATUS='COMPLETE',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
USER_DISPLAY_NAME = L_USER_DISPLAY_NAME,
ORIG_SYSTEM = L_ORIG_SYSTEM,
ORIG_SYSTEM_ID = L_ORIG_SYSTEM_ID
WHERE SIGNATURE_ID = l_SIGNATURE_ID;
is SELECT DOCUMENT_ID,SIGNATURE_STATUS
from EDR_PSIG_DETAILS
where SIGNATURE_ID = P_SIGNATURE_ID;
UPDATE EDR_PSIG_DETAILS
set SIGNATURE_STATUS='CANCEL'
where SIGNATURE_ID=P_SIGNATURE_ID;
UPDATE EDR_PSIG_DETAILS
set SIGNATURE_STATUS='CANCEL',
LAST_UPDATE_DATE=SYSDATE,
LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN=FND_GLOBAL.LOGIN_ID
where SIGNATURE_ID=P_SIGNATURE_ID;
SELECT PSIG_STATUS into l_status
from EDR_PSIG_DOCUMENTS
where DOCUMENT_ID=P_DOCUMENT_ID;
select parameter_id into l_parameter_id from EDR_PSIG_DOC_PARAMS_VL
where document_id=P_DOCUMENT_ID and
NAME=P_PARAMETERS(i).PARAM_NAME;
EDR_PSIG_DOC_PARAMS_PKG.UPDATE_ROW (
X_PARAMETER_ID => L_PARAMETER_ID,
X_DOCUMENT_ID => P_DOCUMENT_ID,
X_NAME => P_PARAMETERS(i).PARAM_NAME,
X_VALUE => P_PARAMETERS(i).PARAM_VALUE,
X_DISPLAY_NAME => NVL(P_PARAMETERS(i).PARAM_DISPLAYNAME,P_PARAMETERS(i).PARAM_NAME),
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
/* Insert a ROW */
select EDR_PSIG_PARAMS_S.nextval into l_parameter_id from dual;
EDR_PSIG_DOC_PARAMS_PKG.INSERT_ROW(
X_ROWID => L_ROWID,
X_PARAMETER_ID => L_PARAMETER_ID,
X_DOCUMENT_ID => P_DOCUMENT_ID,
X_NAME => P_PARAMETERS(i).PARAM_NAME,
X_VALUE => P_PARAMETERS(i).PARAM_VALUE,
X_DISPLAY_NAME => NVL(P_PARAMETERS(i).PARAM_DISPLAYNAME,P_PARAMETERS(i).PARAM_NAME),
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
/* Delete Document Parameters */
PROCEDURE deleteDocumentParameter
(
P_DOCUMENT_ID IN NUMBER,
P_PARAMETER_NAME IN VARCHAR,
P_ERROR OUT NOCOPY NUMBER,
P_ERROR_MSG OUT NOCOPY VARCHAR2
)
IS
l_status varchar2(240);
END deleteDocumentParameter;
/* Delete All Document Parameters */
PROCEDURE deleteAllDocumentParams
(
P_DOCUMENT_ID IN NUMBER,
P_ERROR OUT NOCOPY NUMBER,
P_ERROR_MSG OUT NOCOPY VARCHAR2
)
IS
BEGIN
fnd_message.set_name('EDR','EDR_PSIG_OBSOLETE_CALL');
P_ERROR_MSG:= fnd_message.get();END deleteAllDocumentParams;
select parameter_id from EDR_PSIG_SIGN_PARAMS_VL
where signature_id=P_SIGNATURE_ID and
NAME=L_PARAM_NAME;
SELECT PSIG_STATUS into l_status
from EDR_PSIG_DOCUMENTS
where DOCUMENT_ID in
(select Document_id from EDR_PSIG_DETAILS
where signature_id=P_SIGNATURE_ID);
/* Insert a ROW */
select EDR_PSIG_PARAMS_S.nextval into l_parameter_id from dual;
EDR_PSIG_SIGN_PARAMS_PKG.INSERT_ROW(
X_ROWID => L_ROWID,
X_PARAMETER_ID => L_PARAMETER_ID,
X_SIGNATURE_ID => P_SIGNATURE_ID,
X_NAME => P_PARAMETERS(i).PARAM_NAME,
X_VALUE => P_PARAMETERS(i).PARAM_VALUE,
X_DISPLAY_NAME => NVL(P_PARAMETERS(i).PARAM_DISPLAYNAME,P_PARAMETERS(i).PARAM_NAME),
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
EDR_PSIG_SIGN_PARAMS_PKG.UPDATE_ROW(
X_PARAMETER_ID => L_PARAMETER_ID,
X_SIGNATURE_ID => P_SIGNATURE_ID,
X_NAME => P_PARAMETERS(i).PARAM_NAME,
X_VALUE => P_PARAMETERS(i).PARAM_VALUE,
X_DISPLAY_NAME => NVL(P_PARAMETERS(i).PARAM_DISPLAYNAME,P_PARAMETERS(i).PARAM_NAME),
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
/* Delete Signature Parameters */
PROCEDURE deleteSignatureParameter
(
P_SIGNATURE_ID IN NUMBER,
P_PARAMETER_NAME IN VARCHAR,
P_ERROR OUT NOCOPY NUMBER,
P_ERROR_MSG OUT NOCOPY VARCHAR2
)
IS
BEGIN
fnd_message.set_name('EDR','EDR_PSIG_OBSOLETE_CALL');
END deleteSignatureParameter;
/* Delete All Signature Parameters */
PROCEDURE deleteAllSignatureParams
(
P_SIGNATURE_ID IN NUMBER,
P_ERROR OUT NOCOPY NUMBER,
P_ERROR_MSG OUT NOCOPY VARCHAR2
)
IS
BEGIN
fnd_message.set_name('EDR','EDR_PSIG_OBSOLETE_CALL');
END deleteAllSignatureParams;
is SELECT NAME,VALUE,DISPLAY_NAME
from EDR_PSIG_DOC_PARAMS_VL
where DOCUMENT_ID=P_DOCUMENT_ID;
is SELECT SIGNATURE_ID,
DOCUMENT_ID,
EVIDENCE_STORE_ID,
USER_NAME,
USER_RESPONSE,
SIGNATURE_TIMESTAMP,
SIGNATURE_TIMEZONE,
SIGNATURE_STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
--Bug 3101047 : Start
DECODE(SIGNATURE_TIMESTAMP,NULL,EDR_UTILITIES.GETUSERDISPLAYNAME(USER_NAME), USER_DISPLAY_NAME)USER_DISPLAY_NAME
--Bug 3101047 : End
FROM EDR_PSIG_DETAILS
where DOCUMENT_ID=P_DOCUMENT_ID;
SELECT
DOCUMENT_ID,
PSIG_XML,
PSIG_DOCUMENT,
PSIG_DOCUMENTFORMAT,
PSIG_TIMESTAMP,
PSIG_TIMEZONE,
DOCUMENT_REQUESTER,
PSIG_STATUS,
PSIG_SOURCE,
EVENT_NAME,
EVENT_KEY,
PRINT_COUNT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY
into
P_DOCUMENT.DOCUMENT_ID,
P_DOCUMENT.PSIG_XML,
P_DOCUMENT.PSIG_DOCUMENT,
P_DOCUMENT.PSIG_DOCUMENTFORMAT,
P_DOCUMENT.PSIG_TIMESTAMP,
P_DOCUMENT.PSIG_TIMEZONE,
P_DOCUMENT.DOCUMENT_REQUESTER,
P_DOCUMENT.PSIG_STATUS,
P_DOCUMENT.PSIG_SOURCE,
P_DOCUMENT.EVENT_NAME,
P_DOCUMENT.EVENT_KEY,
P_DOCUMENT.PRINT_COUNT,
P_DOCUMENT.CREATION_DATE,
P_DOCUMENT.CREATED_BY,
P_DOCUMENT.LAST_UPDATE_DATE,
P_DOCUMENT.LAST_UPDATE_LOGIN,
P_DOCUMENT.LAST_UPDATED_BY
from EDR_PSIG_DOCUMENTS
where DOCUMENT_ID=P_DOCUMENT_ID;
P_SIGNATURES(i).LAST_UPDATE_DATE,
P_SIGNATURES(i).LAST_UPDATE_LOGIN,
P_SIGNATURES(i).LAST_UPDATED_BY,
--Bug 3101047 : Start
P_SIGNATURES(i).USER_DISPLAY_NAME;
is SELECT PARAMETER_ID
from EDR_PSIG_SIGN_PARAMS_VL
where SIGNATURE_ID =P_SIGNATURE_ID;
is SELECT NAME,VALUE,DISPLAY_NAME
from EDR_PSIG_SIGN_PARAMS_VL
where SIGNATURE_ID=P_SIGNATURE_ID;
is SELECT SIGNATURE_ID,
DOCUMENT_ID,
EVIDENCE_STORE_ID,
USER_NAME,
USER_RESPONSE,
SIGNATURE_TIMESTAMP,
SIGNATURE_TIMEZONE,
SIGNATURE_STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
--Bug 3101047 : Start
DECODE(SIGNATURE_TIMESTAMP,NULL,EDR_UTILITIES.GETUSERDISPLAYNAME(USER_NAME), USER_DISPLAY_NAME)USER_DISPLAY_NAME
--Bug 3101047 : End
FROM EDR_PSIG_DETAILS
where SIGNATURE_ID=P_SIGNATURE_ID;
P_SIGNATUREDETAILS.LAST_UPDATE_DATE,
P_SIGNATUREDETAILS.LAST_UPDATE_LOGIN,
P_SIGNATUREDETAILS.LAST_UPDATED_BY,
--Bug 3101047 : Start
P_SIGNATUREDETAILS.USER_DISPLAY_NAME;
PROCEDURE updatePrintCount (
P_DOC_ID IN edr_psig_documents.document_id%TYPE,
P_NEW_COUNT OUT NOCOPY NUMBER
) IS PRAGMA AUTONOMOUS_TRANSACTION;
SELECT print_count into P_NEW_COUNT
FROM edr_psig_documents
WHERE document_id = P_DOC_ID;
UPDATE edr_psig_documents
SET
print_count = P_NEW_COUNT,
--Bug 4672801: start
last_update_date = sysdate,
last_updated_by = fnd_global.user_id(),
last_update_login = fnd_global.login_id()
--Bug 4672801: end
WHERE document_id = P_DOC_ID;
END updatePrintCount;
SELECT SIGNATURE_ID from EDR_PSIG_DETAILS
WHERE DOCUMENT_ID = P_DOCUMENT_ID
AND ORIGINAL_RECIPIENT = P_ORIGINAL_RECIPIENT
AND USER_NAME = P_USER_NAME
AND SIGNATURE_STATUS = P_SIGNATURE_STATUS;
SELECT ADHOC_STATUS from EDR_PSIG_DETAILS
WHERE SIGNATURE_ID = P_SIGNATURE_ID;
/* Delete the adhoc user */
procedure DELETE_ADHOC_USER ( P_SIGNATURE_ID IN NUMBER,
X_ERROR OUT NOCOPY NUMBER,
X_ERROR_MSG OUT NOCOPY VARCHAR2)
is
l_document_id NUMBER;
is SELECT DOCUMENT_ID,SIGNATURE_STATUS, ADHOC_STATUS
from EDR_PSIG_DETAILS
where SIGNATURE_ID = P_SIGNATURE_ID;
DELETE FROM EDR_PSIG_DETAILS
where SIGNATURE_ID=P_SIGNATURE_ID;
END DELETE_ADHOC_USER;
/* Update the Signer Sequence for the record */
procedure UPDATE_SIGNATURE_SEQUENCE ( P_SIGNATURE_ID in number,
P_SIGNATURE_SEQUENCE in number,
X_ERROR OUT NOCOPY number,
X_ERROR_MSG OUT NOCOPY varchar2)
is
l_document_id NUMBER;
is SELECT DOCUMENT_ID,SIGNATURE_STATUS
from EDR_PSIG_DETAILS
where SIGNATURE_ID = P_SIGNATURE_ID;
UPDATE EDR_PSIG_DETAILS
set SIGNATURE_SEQUENCE=P_SIGNATURE_SEQUENCE,
LAST_UPDATE_DATE=SYSDATE,
LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN=FND_GLOBAL.LOGIN_ID
where SIGNATURE_ID=P_SIGNATURE_ID;
END UPDATE_SIGNATURE_SEQUENCE;
/* Update the Adhoc Status for the record */
procedure UPDATE_ADHOC_STATUS ( P_SIGNATURE_ID in number,
P_ADHOC_STATUS in varchar2,
X_ERROR OUT NOCOPY number,
X_ERROR_MSG OUT NOCOPY varchar2)
is
l_document_id NUMBER;
is SELECT DOCUMENT_ID,SIGNATURE_STATUS
from EDR_PSIG_DETAILS
where SIGNATURE_ID = P_SIGNATURE_ID;
IF (P_ADHOC_STATUS not in ('ADDED','DELETED') ) then
fnd_message.set_name('EDR','EDR_PSIG_INVALID_ADHOC_STATUS');
UPDATE EDR_PSIG_DETAILS
set ADHOC_STATUS=P_ADHOC_STATUS,
LAST_UPDATE_DATE=SYSDATE,
LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN=FND_GLOBAL.LOGIN_ID
where SIGNATURE_ID=P_SIGNATURE_ID;
END UPDATE_ADHOC_STATUS;
select psig_xml into x_psig_xml
from edr_psig_documents
where document_id = p_document_id;
PROCEDURE UPDATE_PSIG_USER_DETAILS(P_DOCUMENT_ID IN NUMBER)
IS
l_user_name VARCHAR2(100);
select USER_NAME from EDR_PSIG_DETAILS where DOCUMENT_ID=p_document_id
and SIGNATURE_STATUS = 'PENDING'
and USER_DISPLAY_NAME is null
and ORIG_SYSTEM is null
and ORIG_SYSTEM_ID is null;
update EDR_PSIG_DETAILS
set
USER_DISPLAY_NAME=l_user_display_name,
ORIG_SYSTEM=l_orig_system,
ORIG_SYSTEM_ID=l_orig_system_id
where
USER_NAME=l_user_name
and DOCUMENT_ID=p_document_id;
FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','UPDATE_PSIG_USER_DETAILS');
END UPDATE_PSIG_USER_DETAILS;
select XMLELEMENT("ERECORD_HEADER_DETAILS",
XMLFOREST(evid.event_name as EVENT_NAME,
wfev.display_name as EVENT_DISPLAY_NAME,
evid.event_key as EVENT_KEY,
evid.document_id as ERECORD_ID,
evid.PSIG_TIMESTAMP as EVENT_DATE,
evid.psig_timezone as TIMEZONE,
evid.psig_source as APPLICATION_SOURCE,
evid.DOCUMENT_REQUESTER as DOCUMENT_REQUESTER,
evid.DOC_REQ_DISP_NAME DOCUMENT_REQUESTER_DISP_NAME,
lookup1.meaning as DOCUMENT_STATUS,
evid.print_count as PRINT_COUNT,
evid.creation_date as CREATION_DATE,
evid.created_by as CREATED_BY,
evid.last_update_date as LAST_UPDATE_DATE,
evid.last_updated_by as LAST_UPDATED_BY,
evid.last_update_login as LAST_UPDATE_LOGIN,
(select xmlagg(xmlelement("DOC_PARAMS",
xmlforest(param1.name as PARAM_NAME,
param1.value as PARAM_VALUE)
))
from edr_psig_doc_params_vl param1
where param1.document_id = evid.document_id
) as DOC_PARAM_DETAILS
)
)
into l_result
from edr_psig_documents evid,
wf_events_vl wfev,
fnd_lookup_values_vl lookup1
where
evid.event_name = wfev.name
and lookup1.lookup_type = 'EDR_DOCUMENT_STATUS'
and lookup1.lookup_code = evid.psig_status
and evid.document_id = p_document_id;
qryCtx := dbms_xmlgen.newContext('select xmltype(psig_xml) as ERECORD_XML from edr_psig_documents where document_id = ' || p_document_id);
select extract(dbms_xmlgen.getXMLType(qryCtx),'/ERECORD_XML/*')
into l_result
from dual;
SELECT xmlelement("ERECORD_XML",l_result)
into l_result
from dual;
select xmlelement("ERECORD_SIGNATURE_DETAILS",
xmlagg(xmlelement("SIGNER_DETAIL",
xmlforest(psig.signature_sequence as SIGNATURE_SEQUENCE,
psig.user_name as SIGNER_USER_NAME,
psig.user_display_name as SIGNER_DISPLAY_NAME,
psig.user_response as SIGNER_RESPONSE,
psig.signature_timestamp as SIGNING_DATE,
psig.SIGNATURE_OVERRIDING_COMMENTS as OVERRIDING_DETAILS,
(select xmlagg(xmlelement("SIGNATURE_PARAMS",
xmlforest(param1.name as PARAM_NAME,
param1.value as PARAM_VALUE)
)
)
from edr_psig_sign_params_vl param1
where param1.signature_id = psig.signature_id
) as SIGNATURE_PARAM_DETAILS
)
)
)
)
into l_result
from edr_psig_details psig
where
psig.document_id = p_document_id;
select extract(l_result,'/ERECORD_SIGNATURE_DETAILS/*') into l_temp from dual;
select xmlelement("ACKNOWLEDGEMENT_DETAILS",
xmlforest(ackn.ackn_id as acknowledgement_id,
ackn.ackn_date as acknowledgement_date,
lookup1.meaning as acknowledgement_status,
ackn.ackn_by as acknowledged_by,
ackn.ackn_note as acknowledgement_comment,
ackn.CREATED_BY as CREATED_BY,
ackn.CREATION_DATE as CREATION_DATE,
ackn.LAST_UPDATED_BY as LAST_UPDATED_BY,
ackn.LAST_UPDATE_LOGIN as LAST_UPDATE_LOGIN,
ackn.LAST_UPDATE_DATE as LAST_UPDATE_DATE
)
)
into l_result
from edr_Trans_ackn ackn,
fnd_lookup_values_vl lookup1
where ackn.erecord_id = p_document_id
and lookup1.lookup_type = 'EDR_TRANS_STATUS'
and lookup1.lookup_code = ackn.transaction_status;
select xmlelement("PRINT_HISTORY_DETAILS",
xmlagg(xmlelement("PRINT_DETAILS",
xmlforest(print.print_document_id as PRINT_ERECORD_ID,
print.PRINT_COUNT as PRINT_COUNT,
PRINT.PRINT_REQUESTED_BY as PRINT_REQUESTER,
PRINT.USER_DISPLAY_NAME as PRINT_REQUESTER_DISPLAY_NAME,
PRINT.PRINT_REQUESTED_DATE as PRINT_EVENT_DATE,
(select lookup3.meaning
from fnd_lookup_values_vl lookup3,
edr_psig_documents printevid
where lookup3.lookup_code = printevid.psig_status
and lookup3.lookup_type = 'EDR_DOCUMENT_STATUS'
and printevid.document_id = print.print_document_id
) as PRINT_ERECORD_STATUS,
print.CREATED_BY as CREATED_BY,
print.CREATION_DATE as CREATION_DATE,
print.LAST_UPDATED_BY as LAST_UPDATED_BY,
print.LAST_UPDATE_LOGIN as LAST_UPDATE_LOGIN,
print.LAST_UPDATE_DATE as LAST_UPDATE_DATE
)
)
)
)
into l_result
from edr_psig_print_history print
where print.document_id = p_document_id;
select extract(l_result,'/PRINT_HISTORY_DETAILS/*') into l_temp from dual;
select rel.parent_erecord_id into l_parent_erecord_id
from edr_event_relationship rel
where child_erecord_id = p_document_id;
select xmlconcat(l_result,l_current_xml) into l_result from dual;
select xmlconcat(l_result,l_current_xml) into l_result from dual;
select xmlconcat(l_result,l_current_xml) into l_result from dual;
select xmlconcat(l_result,l_current_xml) into l_result from dual;
select xmlconcat(l_result,l_current_xml) into l_result from dual;
select xmlelement("ERECORD",l_result) into l_result from dual;
select xmlelement("PARENT_ERECORD_DETAILS",l_result) into l_result from dual;
select xmlElement
(
"ERECORD",
extract(l_this_Node,'/ERECORD/*'),
xmlElement
(
"CHILD_ERECORD_DETAILS",
l_result
)
)
into l_this_Node
from dual;
select xmlconcat(l_this_Node,l_result) into l_this_Node from dual;
select level,rel.CHILD_ERECORD_ID CHILD_ERECORD_ID
from EDR_EVENT_RELATIONSHIP rel
connect by prior CHILD_ERECORD_ID = PARENT_ERECORD_ID
start with PARENT_ERECORD_ID = p_document_id and CHILD_ERECORD_ID <> p_document_id;
select xmlconcat(l_result_xml_tbl(i),l_current_xml) into l_result_xml_tbl(i) from dual;
select xmlconcat(l_result_xml_tbl(i),l_current_xml) into l_result_xml_tbl(i) from dual;
select xmlconcat(l_result_xml_tbl(i),l_current_xml) into l_result_xml_tbl(i) from dual;
select xmlconcat(l_result_xml_tbl(i),l_current_xml) into l_result_xml_tbl(i) from dual;
select xmlelement("ERECORD",l_result_xml_tbl(i)) into l_result_xml_tbl(i) from dual;
select xmlelement("CHILD_ERECORD_DETAILS",l_result) into l_result from dual;
select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
select xmlelement("ERECORD",l_current_result) into l_current_result from dual;
select xmlconcat(l_result,l_current_result) into l_result from dual;
select xmlelement("ERECORDS",l_result) into l_result from dual;
select evid.document_id
from edr_psig_documents evid
where evid.event_name = p_event_name
and evid.event_key = p_event_key;
select evid.document_id into l_erecord_ids(l_count + 1)
from edr_psig_documents evid
where evid.document_id = p_erecord_id;
select evid.document_id into l_erecord_ids(l_count + 1)
from edr_psig_documents evid
where evid.event_name=p_event_name
and evid.event_key = event_key
and evid.document_id = p_erecord_id;
update edr_psig_details
set signature_status = null,
LAST_UPDATE_DATE=SYSDATE,
LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN=FND_GLOBAL.LOGIN_ID
where document_id = p_document_id
and signature_status = 'PENDING';