[Home] [Help]
53: X_STATUS OUT NOCOPY VARCHAR2,
54: X_ERROR OUT NOCOPY NUMBER,
55: X_ERROR_MSG OUT NOCOPY VARCHAR2) IS
56: CURSOR C1 is
57: SELECT PSIG_STATUS from EDR_PSIG_DOCUMENTS
58: WHERE DOCUMENT_ID = P_DOCUMENT_ID;
59: L_document_status varchar2(240);
60: BEGIN
61: OPEN C1;
453: END;
454: end if;
455:
456: /* Generate a new Document ID */
457: select EDR_PSIG_DOCUMENTS_S.nextval into l_document_id from dual;
458:
459: /* Attachments Bug Fix. Added if statement */
460: if P_WF_NID is not NULL then
461: /* capture Attachemnts if Exists */
483: );
484: --Bug 4672801: end
485:
486: --Insert the row
487: INSERT into EDR_PSIG_DOCUMENTS
488: (
489: DOCUMENT_ID ,
490: PSIG_XML ,
491: PSIG_DOCUMENT ,
627: RAISE TIMEZONE_ERROR;
628: end if;
629:
630: --Generate a new Document ID
631: select EDR_PSIG_DOCUMENTS_S.nextval into l_document_id from dual;
632:
633: --Bug 4672801: start
634: EDR_UTILITIES.getWhoColumns
635: ( creation_date => l_creation_date ,
640: );
641: --Bug 4672801: end
642:
643: --Insert the document in pending status
644: INSERT into EDR_PSIG_DOCUMENTS
645: (DOCUMENT_ID,
646: PSIG_STATUS,
647: PSIG_TIMESTAMP,
648: --Bug 4672801: start
730: WHERE document_id =P_DOCUMENT_ID AND
731: SIGNATURE_STATUS='PENDING';
732: l_count number;
733: BEGIN
734: /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
735: edr_ctx_pkg.set_secure_attr;
736: /*12-26-2002 End*/
737: /* CHECK if document Status is Valid */
738: IF UPPER(P_STATUS) NOT IN ('CANCEL','COMPLETE','ERROR','PENDING','REJECTED','TIMEDOUT') then
768: END IF;
769:
770:
771: -- Document Exist
772: UPDATE EDR_PSIG_DOCUMENTS
773: set PSIG_STATUS=UPPER(p_STATUS),
774: --Bug 4672801: start
775: last_update_date = sysdate,
776: last_updated_by = fnd_global.user_id(),
850: --Bug 4073809 : end
851:
852:
853:
854: /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
855: edr_ctx_pkg.set_secure_attr;
856: /*12-26-2002 End*/
857:
858: /* Check if document is existing */
918: -- Bug 3604783 : Start
919: /* Recieve the document requester's display name */
920: l_doc_req_disp_name := edr_utilities.getuserdisplayname(p_psig_requester);
921:
922: update EDR_PSIG_DOCUMENTS
923: set
924: PSIG_XML=l_xml,
925: PSIG_DOCUMENT=l_document,
926: PSIG_DOCUMENTFORMAT=P_PSIG_DOCUMENTFORMAT,
977: WHERE document_id =P_DOCUMENT_ID AND
978: SIGNATURE_STATUS='PENDING';
979: l_count number;
980: BEGIN
981: /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
982: edr_ctx_pkg.set_secure_attr;
983: /*12-26-2002 End*/
984:
985: /* Check if document is existing */
1008: RAISE EDR_GENERIC_ERROR;
1009: END IF;
1010:
1011: /* Document Exist */
1012: UPDATE EDR_PSIG_DOCUMENTS
1013: set PSIG_STATUS='COMPLETE',
1014: --Bug 4672801: start
1015: last_update_date = sysdate,
1016: last_updated_by = fnd_global.user_id(),
1048: ) AS
1049: l_status varchar2(240);
1050:
1051: BEGIN
1052: /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1053: edr_ctx_pkg.set_secure_attr;
1054: /*12-26-2002 End*/
1055:
1056: /* Check if document is existing */
1067: RAISE EDR_INVALID_DOC_TRAN;
1068: END IF;
1069:
1070: /* Document Exist */
1071: UPDATE EDR_PSIG_DOCUMENTS
1072: set PSIG_STATUS='CANCEL',
1073: --Bug 4672801: start
1074: last_update_date = sysdate,
1075: last_updated_by = fnd_global.user_id(),
1147: l_signer_seq number :=0;
1148: --Bug 3330240: end
1149:
1150: BEGIN
1151: /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1152: edr_ctx_pkg.set_secure_attr;
1153: /*12-26-2002 End*/
1154:
1155: /* Check if Server time Zone is set to NULL and if error out */
1365: l_pending_count number;
1366: --Bug 3330240 : end
1367:
1368: BEGIN
1369: /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1370: edr_ctx_pkg.set_secure_attr;
1371: /*12-26-2002 End*/
1372:
1373: /* Check if Server time Zone is set to NULL and if error out */
1527: is SELECT DOCUMENT_ID,SIGNATURE_STATUS
1528: from EDR_PSIG_DETAILS
1529: where SIGNATURE_ID = P_SIGNATURE_ID;
1530: BEGIN
1531: /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1532: edr_ctx_pkg.set_secure_attr;
1533: /*12-26-2002 End*/
1534: /* Check if Signature Row Exist */
1535: OPEN C1;
1606: l_parameter_id NUMBER;
1607: L_ROWID ROWID;
1608:
1609: BEGIN
1610: /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1611: edr_ctx_pkg.set_secure_attr;
1612: /*12-26-2002 End*/
1613:
1614: /* Check if document is existing */
1612: /*12-26-2002 End*/
1613:
1614: /* Check if document is existing */
1615: SELECT PSIG_STATUS into l_status
1616: from EDR_PSIG_DOCUMENTS
1617: where DOCUMENT_ID=P_DOCUMENT_ID;
1618: IF l_status in ('PENDING','ERROR') THEN
1619: /* Document Exist */
1620: /* Process Table and inser rows in Table */
1731: where signature_id=P_SIGNATURE_ID and
1732: NAME=L_PARAM_NAME;
1733:
1734: BEGIN
1735: /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1736: edr_ctx_pkg.set_secure_attr;
1737: /*12-26-2002 End*/
1738:
1739: /* Check if document is existing */
1737: /*12-26-2002 End*/
1738:
1739: /* Check if document is existing */
1740: SELECT PSIG_STATUS into l_status
1741: from EDR_PSIG_DOCUMENTS
1742: where DOCUMENT_ID in
1743: (select Document_id from EDR_PSIG_DETAILS
1744: where signature_id=P_SIGNATURE_ID);
1745: IF l_status in ('PENDING','ERROR') THEN
1913: P_DOCUMENT.CREATED_BY,
1914: P_DOCUMENT.LAST_UPDATE_DATE,
1915: P_DOCUMENT.LAST_UPDATE_LOGIN,
1916: P_DOCUMENT.LAST_UPDATED_BY
1917: from EDR_PSIG_DOCUMENTS
1918: where DOCUMENT_ID=P_DOCUMENT_ID;
1919: /* Fetch Document Parameters */
1920: open DOC_PARAMS;
1921: Loop
2058: END getSignatureDetails;
2059:
2060:
2061: PROCEDURE updatePrintCount (
2062: P_DOC_ID IN edr_psig_documents.document_id%TYPE,
2063: P_NEW_COUNT OUT NOCOPY NUMBER
2064: ) IS PRAGMA AUTONOMOUS_TRANSACTION;
2065: BEGIN
2066:
2064: ) IS PRAGMA AUTONOMOUS_TRANSACTION;
2065: BEGIN
2066:
2067: SELECT print_count into P_NEW_COUNT
2068: FROM edr_psig_documents
2069: WHERE document_id = P_DOC_ID;
2070:
2071: P_NEW_COUNT := NVL(P_NEW_COUNT,0) + 1;
2072:
2069: WHERE document_id = P_DOC_ID;
2070:
2071: P_NEW_COUNT := NVL(P_NEW_COUNT,0) + 1;
2072:
2073: UPDATE edr_psig_documents
2074: SET
2075: print_count = P_NEW_COUNT,
2076: --Bug 4672801: start
2077: last_update_date = sysdate,
2247: from EDR_PSIG_DETAILS
2248: where SIGNATURE_ID = P_SIGNATURE_ID;
2249:
2250: BEGIN
2251: /*Start: Set the secure context to access edr_psig_documents table */
2252: edr_ctx_pkg.set_secure_attr;
2253: /* Check if Signature Row Exist */
2254: OPEN C1;
2255: FETCH C1 into l_document_id,l_sig_status;
2327: from EDR_PSIG_DETAILS
2328: where SIGNATURE_ID = P_SIGNATURE_ID;
2329:
2330: BEGIN
2331: /*Start: Set the secure context to access edr_psig_documents table */
2332: edr_ctx_pkg.set_secure_attr;
2333: /* Check if Signature Row Exist */
2334: OPEN C1;
2335: FETCH C1 into l_document_id,l_sig_status;
2396: --Bug 3330240 : end
2397:
2398: -- Bug 3170251 - Start
2399: -- Added a getter to get PSIG_XML (eRecord XML ) in CLOB.
2400: --This procedure gets the PSIG_XML from EDR_PSIG_DOCUMENTS table
2401: --for the given p_document_id i.e. eRecordId
2402:
2403: -- Start of comments
2404: -- API name : getERecordXML
2420: /* Set the security context to get access to PSIG_DOCUMENTS */
2421: edr_ctx_pkg.set_secure_attr;
2422:
2423: select psig_xml into x_psig_xml
2424: from edr_psig_documents
2425: where document_id = p_document_id;
2426: exception
2427: WHEN NO_DATA_FOUND then
2428: fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOCUMENT');
2538: )
2539:
2540: into l_result
2541:
2542: from edr_psig_documents evid,
2543: wf_events_vl wfev,
2544: fnd_lookup_values_vl lookup1
2545:
2546: where
2569:
2570: BEGIN
2571:
2572: --Create the query context based on the SQL QUERY.
2573: qryCtx := dbms_xmlgen.newContext('select xmltype(psig_xml) as ERECORD_XML from edr_psig_documents where document_id = ' || p_document_id);
2574:
2575: --We want the "
2576: dbms_xmlgen.setRowTag(qryCtx,null);
2577:
2713: PRINT.USER_DISPLAY_NAME as PRINT_REQUESTER_DISPLAY_NAME,
2714: PRINT.PRINT_REQUESTED_DATE as PRINT_EVENT_DATE,
2715: (select lookup3.meaning
2716: from fnd_lookup_values_vl lookup3,
2717: edr_psig_documents printevid
2718: where lookup3.lookup_code = printevid.psig_status
2719: and lookup3.lookup_type = 'EDR_DOCUMENT_STATUS'
2720: and printevid.document_id = print.print_document_id
2721: ) as PRINT_ERECORD_STATUS,
3110: --This cursor is used to fetch the e-record IDs identified by the event name and event key.
3111: cursor get_erecord_ids_cur(p_event_name VARCHAR2,
3112: p_event_key VARCHAR2) is
3113: select evid.document_id
3114: from edr_psig_documents evid
3115: where evid.event_name = p_event_name
3116: and evid.event_key = p_event_key;
3117:
3118: INVALID_EVENT_NAME_ERROR EXCEPTION;
3158: --If only the e-record ID is specified then copy the same into the e-record ID table type.
3159: elsif (p_event_name is null and p_event_key is null and p_erecord_id is not null) then
3160:
3161: select evid.document_id into l_erecord_ids(l_count + 1)
3162: from edr_psig_documents evid
3163: where evid.document_id = p_erecord_id;
3164: l_count := l_count +1;
3165:
3166: elsif (p_event_name is not null and p_event_key is not null and p_erecord_id is not null) then
3165:
3166: elsif (p_event_name is not null and p_event_key is not null and p_erecord_id is not null) then
3167:
3168: select evid.document_id into l_erecord_ids(l_count + 1)
3169: from edr_psig_documents evid
3170: where evid.event_name=p_event_name
3171: and evid.event_key = event_key
3172: and evid.document_id = p_erecord_id;
3173: l_count := l_count +1;