The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT into edr_erecord_id_temp (document_id) values (p_Event_id);
insert into EDR_REDLINE_TRANS_DATA (EVENT_ID,PRE_XML_DATA,POST_XML_DATA,ERR_CODE,ERR_MSG,DIFF_XML,APPENDIX_GEN_XML) values (p_event_id,L_XML_DOCUMENT,empty_clob(), retcode, errmsg, empty_clob(),empty_clob());
/* Update workflow threshold to default threshold */
WF_ENGINE.THRESHOLD := 50;
/* Temp Table Insertion */
l_event_name:=P_eventP.getEventName( );
/* Insert into Temp tables for Further Processing */
wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','Event Name Assigned');
wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','CLOB Assignment Complete and insert being executed');
INSERT INTO EDR_ERECORDS(EVENT_ID,
EVENT_NAME,
EVENT_KEY,
EVENT_USER_KEY_LABEL,
EVENT_USER_KEY_VALUE,
EVENT_TIMESTAMP,
EVENT_TIMEZONE,
ERECORD_XML,
ERECORD_SIGNATURE_STATUS,
XML_MAP_NAME,
AME_TRANSACTION_TYPE,
AUDIT_GROUP,
TRANSACTION_AUDIT_ID)
Values(p_event_id,
l_event_name,
l_event_key,
l_event_user_key_label,
l_event_user_key_value,
SYSDATE,
NULL, -- Still figuring out how to populate this
l_temp_clob,
'PENDING',
P_map_code,
p_ame_transaction_type,
p_audit_group,
l_transaction_audit_id);
wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','Insertion Complete in EDR_ERECORDS');
/* Insert into Temp tables for Further Processing */
SELECT EDR_ESIGNATURES_S.NEXTVAL into l_edr_signature_id from DUAL;
INSERT into EDR_ESIGNATURES(SIGNATURE_ID,
EVENT_ID,
EVENT_NAME,
USER_NAME,
SIGNATURE_SEQUENCE,
SIGNATURE_STATUS,
ORIGINAL_RECIPIENT,
SIGNATURE_OVERRIDING_COMMENTS)
values(l_edr_signature_id,
p_event_id,
l_event_name,
l_OVERRIDING_APPROVER,
P_approverList(i).approver_order_number,
'PENDING',
L_FND_USER,
L_OVERRIDING_COMMENTS);
wf_log_pkg.string(3, 'EDR_PSIG_rule.create_pageflow','Insert Complete into EDR_ESIGNATURES');
wf_log_pkg.string(3, 'EDR_PSIG_RULE.create_pageflow','AME Approver list inserted into temp table');
/* Update workflow threshold to saved value */
WF_ENGINE.THRESHOLD := l_cur_threshold;
/* Update workflow threshold to saved value */
WF_ENGINE.THRESHOLD := l_cur_threshold;
select DIFF_XML into x_output from EDR_REDLINE_TRANS_DATA where EVENT_ID = p_edr_event_id ;
EDR_PSIG.updateDocument(P_DOCUMENT_ID => l_document_id,
P_PSIG_XML => L_XML_DOCUMENT,
P_PSIG_DOCUMENT => L_EREC_OUTPUT,
P_PSIG_DOCUMENTFORMAT => l_output_format,
P_PSIG_REQUESTER => l_requester,
P_PSIG_SOURCE => l_source_application,
P_EVENT_NAME => l_event_name,
P_EVENT_KEY => l_event_key,
P_WF_NID => NULL,
P_ERROR => l_error,
P_ERROR_MSG => l_error_msg);
wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code during update document '||l_error);
wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg during update document '||l_error_msg);
wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Updated Document in eRecords Repository with Document ID '||l_document_id);
update fnd_attached_documents
set pk1_value = l_document_id
where entity_name = EDR_CONSTANTS_GRP.g_erecord_entity_name
and pk1_value = p_edr_event_id; */
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',
X_from_category_id => null,
X_to_category_id => null);
fnd_attached_documents2_pkg.delete_attachments
(X_entity_name => G_TEMP_ENTITY_NAME,
X_pk1_value => to_char(p_edr_event_id),
X_pk2_value => null,
X_pk3_value => null,
X_pk4_value => null,
X_pk5_value => null,
--Bug 4381237: Start
--pass this flag as Y so that the attachments to TEMPERECORD
--are deleted. this is ok NOW because we are using our
--own attachment api above. That would not bring the base
--entities attachment into picture
--X_delete_document_flag => 'N',
X_delete_document_flag => 'Y',
--Bug 4381237: End
X_automatically_added_flag => 'N');
select count(*) INTO l_no_enabled_eres_sub
from wf_events a, wf_event_subscriptions b
where a.GUID = b.EVENT_FILTER_GUID
and a.name = l_parent_event
and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
--Bug No 4912782- Start
and b.source_type = 'LOCAL'
and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
select count(*) INTO l_no_enabled_eres_sub
from wf_events a, wf_event_subscriptions b
where a.GUID = b.EVENT_FILTER_GUID
and a.name = l_parent_event
and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
and b.STATUS = 'ENABLED'
--Bug No 4912782- Start
and b.source_type = 'LOCAL'
and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
select count(*) INTO l_no_enabled_eres_sub
from wf_events a, wf_event_subscriptions b
where a.GUID = b.EVENT_FILTER_GUID
and a.name = p_event.event_name
and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
and b.STATUS = 'ENABLED'
--Bug No 4912782- Start
and b.source_type = 'LOCAL'
and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
/* Select APPLICATION_ID of the Event. This is required by AME. Assumption made here
is OWNER_TAG will always be set to application Short Name */
SELECT application_id,APPLICATION_SHORT_NAME into l_application_id,l_application_code
FROM FND_APPLICATION A, WF_EVENTS B
WHERE A.APPLICATION_SHORT_NAME = B.OWNER_TAG
AND B.NAME=P_EVENT.getEventName( );
insert into EDR_FINAL_XML_GT(event_name,event_key,event_xml)
values(l_event_name,l_event_key,l_event_data);
select event_xml
into l_event_data
from EDR_FINAL_XML_GT;
delete edr_erecord_id_temp;
SELECT EDR_ERECORDS_S.NEXTVAL into l_edr_Event_id from DUAL;
INSERT into edr_erecord_id_temp (document_id) values (l_edr_Event_id);
update EDR_FINAL_XML_GT
set event_xml = l_erecord_data
where event_name = l_event_name
and event_key = l_event_key;
wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','updated edr_final_xml_gt with the final xml');
delete from EDR_FINAL_XML_GT where event_name = l_event_name and event_key=l_event_key;
select ltrim(rtrim(application_name)) into l_application_name
from ame_Calling_Apps
where FND_APPLICATION_ID=l_application_id and
TRANSACTION_TYPE_ID=NVL(l_ame_transaction_type,P_EVENT.getEventName( ))
--Bug 4652277: Start
--and end_Date is null;
l_ruleids.delete;
l_rulenames.delete;
delete edr_erecord_id_temp;
SELECT EDR_ERECORDS_S.NEXTVAL into l_edr_Event_id from DUAL;
INSERT into edr_erecord_id_temp (document_id) values (l_edr_Event_id);
delete edr_erecord_id_temp;
SELECT EDR_ERECORDS_S.NEXTVAL into l_edr_Event_id from DUAL;
INSERT into edr_erecord_id_temp (document_id) values (l_edr_Event_id);
select DIFF_XML into L_XML_DOCUMENT from EDR_REDLINE_TRANS_DATA where EVENT_ID = l_edr_event_id ;
/* select Unique event Id to be posted in temp tables */
/* 24-FEB-2003: CJ: commented one line below as the erecord id is already available
in the local variable and added a line to delete the erecord id from temp
table */
/* SELECT EDR_ERECORDS_S.NEXTVAL into l_edr_Event_id from DUAL; */
/* Select the workflow process for the subscription */
select wf_process_type,wf_process_name
into l_wftype, l_wfprocess
from wf_event_subscriptions
where guid = p_subscription_guid;