[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_QUERY_TEXT1 := 'SELECT * FROM ' || p_TABLE_NAME ||' WHERE ';
qrycontext := DBMS_XMLGEN.newcontext('SELECT FND_GLOBAL.user_id,FND_GLOBAL.resp_id,FND_GLOBAL.resp_appl_id,FND_GLOBAL.server_id FROM DUAL') ;
OPEN cur FOR 'SELECT '||l_pk_names||' FROM '||p_table_name;
SELECT CSM_HA_PAYLOAD_DATA_S.nextval INTO l_pld_id FROM DUAL;
INSERT INTO CSM_HA_PAYLOAD_DATA(HA_PAYLOAD_ID ,OBJECT_NAME, PK_VALUE, PARENT_PAYLOAD_ID,DML_TYPE ,MOBILE_DATA, PROCESSED ,
CREATION_DATE , CREATED_BY , LAST_UPDATE_DATE ,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
VALUES(l_pld_id,p_TABLE_NAME,p_pk_value,l_pld_id,'I','Y','N',SYSDATE,1,SYSDATE,1,1);
FOR rec IN (SELECT NOTIFICATION_ID FROM CSM_AUTO_SYNC_NFN
WHERE CREATION_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.NOTIFICATION_ID;
FOR rec IN (SELECT TRACKING_ID FROM CSM_DEFERRED_NFN_INFO
WHERE CREATION_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.TRACKING_ID;
OPEN cur FOR ' SELECT NOTIFICATION_ID FROM CSM_SYNC_ERROR_NFN_INFO '||
' WHERE SYNC_SESSION_ID IN (SELECT SESSION_ID FROM '||asg_base.G_OLITE_SCHEMA||'.C$SYNC_HISTORY '||
' WHERE START_TIME >= :1)' USING G_HA_START_TIME;
FOR rec IN (SELECT INQ_OWNER,INQ_NAME, DEVICE_USER_NAME, DEFERRED_TRAN_ID,SEQUENCE
FROM ASG_DEFERRED_TRANINFO info, ASG_PUB_ITEM pi
WHERE info.OBJECT_NAME = pi.ITEM_ID
AND INQ_NAME IS NOT NULL
AND info.CREATION_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):= rec.DEVICE_USER_NAME;
l_pk_name_list.DELETE;l_pk_type_list.DELETE; l_pk_value_list.DELETE;
FOR rec IN (SELECT DEVICE_USER_NAME,DEFERRED_TRAN_ID,SEQUENCE
FROM ASG_DEFERRED_TRANINFO
WHERE CREATION_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.DEVICE_USER_NAME;
l_pk_name_list.DELETE;l_pk_type_list.DELETE; l_pk_value_list.DELETE;
FOR rec IN (SELECT DEVICE_USER_NAME,TRANID
FROM ASG_USERS_INQINFO
WHERE CREATION_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.DEVICE_USER_NAME;
l_pk_name_list.DELETE;l_pk_type_list.DELETE; l_pk_value_list.DELETE;
OPEN cur FOR 'SELECT SESSION_ID FROM '||asg_base.G_OLITE_SCHEMA||'.C$SYNC_HISTORY '
||'WHERE START_TIME >= :1' USING G_HA_START_TIME;
l_pk_name_list.DELETE;l_pk_type_list.DELETE; l_pk_value_list.DELETE;
OPEN cur FOR 'SELECT SESSION_ID,PUB_ITEM,PHASE FROM '
||asg_base.G_OLITE_SCHEMA||'.C$SYNC_HIS_PUB_ITEMS '
||'WHERE ROWNUM<2 AND START_TIME >= :1' USING G_HA_START_TIME;
FOR rec IN (SELECT DISTINCT USER_ID,HA_PARENT_PAYLOAD_ID
FROM ASG_SYSTEM_DIRTY_QUEUE a, ASG_USER b
WHERE HA_PARENT_PAYLOAD_ID IS NOT NULL
AND (DOWNLOAD_FLAG IS NULL OR TRANSACTION_ID IS NULL)
AND a.CLIENT_ID=b.USER_NAME)
LOOP
TRACK_MFS_REC_NO_PLD('ASG_SYSTEM_DIRTY_QUEUE', rec.HA_PARENT_PAYLOAD_ID||','||rec.USER_ID);
UPDATE FND_RESPONSIBILITY SET END_DATE=null
WHERE (APPLICATION_ID,RESPONSIBILITY_ID) IN (SELECT HA_APPLICATION_ID,HA_RESPONSIBILITY_ID
FROM CSM_HA_RESP_MAPPINGS)
AND END_DATE IS NOT NULL;
UPDATE FND_RESPONSIBILITY SET START_DATE=sysdate-1
WHERE (APPLICATION_ID,RESPONSIBILITY_ID) IN (SELECT HA_APPLICATION_ID,HA_RESPONSIBILITY_ID
FROM CSM_HA_RESP_MAPPINGS)
AND NVL(START_DATE,SYSDATE-1) > SYSDATE;
FOR rec IN (SELECT usr.USER_NAME,app.APPLICATION_SHORT_NAME app_name,mapp.HA_RESP_KEY resp_key
FROM FND_USER_RESP_GROUPS usr_rsp,
CSM_HA_RESP_MAPPINGS mapp,
FND_APPLICATION app,
FND_USER usr
WHERE SYSDATE BETWEEN nvl(usr_rsp.START_DATE,SYSDATE-1) AND nvl(usr_rsp.END_DATE,SYSDATE+1)
AND usr_rsp.USER_ID=usr.USER_ID
AND usr_rsp.RESPONSIBILITY_ID=mapp.RESPONSIBILITY_ID
AND usr_rsp.RESPONSIBILITY_APPLICATION_ID= mapp.APPLICATION_ID
AND mapp.HA_APPLICATION_ID=app.APPLICATION_ID
AND NOT EXISTS (SELECT 1 FROM FND_USER_RESP_GROUPS
WHERE USER_ID=usr.USER_ID
AND RESPONSIBILITY_ID=mapp.HA_RESPONSIBILITY_ID
AND RESPONSIBILITY_APPLICATION_ID=mapp.HA_APPLICATION_ID))
LOOP
FND_USER_PKG.ADDRESP(rec.USER_NAME,rec.APP_NAME,rec.RESP_KEY,'STANDARD',NULL,sysdate,null);
UPDATE FND_RESPONSIBILITY SET END_DATE=SYSDATE-1
WHERE (APPLICATION_ID,RESPONSIBILITY_ID) NOT IN (SELECT HA_APPLICATION_ID,HA_RESPONSIBILITY_ID
FROM CSM_HA_RESP_MAPPINGS)
AND NVL(END_DATE, SYSDATE+1) > SYSDATE-1
AND APPLICATION_ID not in (0,1);
UPDATE FND_CONCURRENT_PROGRAMS SET ENABLED_FLAG='Y'
WHERE (APPLICATION_ID,CONCURRENT_PROGRAM_ID) IN
(SELECT APPLICATION_ID,CONCURRENT_PROGRAM_ID FROM CSM_HA_ACTIVE_CONC_DATA);
FOR rec in (SELECT B.APPLICATION_SHORT_NAME APP_NAME, CONCURRENT_PROGRAM_NAME
FROM FND_CONCURRENT_PROGRAMS a, FND_APPLICATION b
WHERE (APPLICATION_ID,CONCURRENT_PROGRAM_ID) NOT IN
(SELECT APPLICATION_ID,CONCURRENT_PROGRAM_ID FROM CSM_HA_ACTIVE_CONC_DATA)
AND APPLICATION_ID NOT IN (0,1) -- non AOL
AND a.APPLICATION_ID = b.APPLICATION_ID)
LOOP
FND_PROGRAM.enable_program(rec.CONCURRENT_PROGRAM_NAME,rec.APP_NAME,'N');
UPDATE FND_CONCURRENT_PROGRAMS SET ENABLED_FLAG='N'
WHERE (APPLICATION_ID,CONCURRENT_PROGRAM_ID) NOT IN
(SELECT APPLICATION_ID,CONCURRENT_PROGRAM_ID FROM CSM_HA_ACTIVE_CONC_DATA)
AND APPLICATION_ID NOT IN (0,1); -- non AOL
SELECT application_id,request_group_id INTO l_app_id,l_grp_id from fnd_request_groups
where request_group_name='CSM_HA_REQUEST_GROUP';
FOR rec in (SELECT B.APPLICATION_SHORT_NAME APP_NAME, CONCURRENT_PROGRAM_NAME
FROM CSM_HA_ACTIVE_CONC_DATA a, FND_APPLICATION b
WHERE a.APPLICATION_ID = b.APPLICATION_ID
AND a.APPLICATION_ID NOT IN (0,1)
AND NOT EXISTS (SELECT 1 FROM FND_REQUEST_GROUP_UNITS
WHERE UNIT_APPLICATION_ID=a.APPLICATION_ID
AND REQUEST_UNIT_ID=a.CONCURRENT_PROGRAM_ID
AND APPLICATION_ID=l_app_id
AND REQUEST_GROUP_ID=l_grp_id))
LOOP
FND_PROGRAM.add_to_group(rec.CONCURRENT_PROGRAM_NAME,rec.APP_NAME,'CSM_HA_REQUEST_GROUP','CSM');
UPDATE FND_RESPONSIBILITY SET GROUP_APPLICATION_ID=l_app_id, REQUEST_GROUP_ID=l_grp_id
WHERE APPLICATION_ID NOT IN (0,1)
AND SYSDATE BETWEEN nvl(START_DATE,SYSDATE-1) AND nvl(END_DATE,SYSDATE+1)
AND REQUEST_GROUP_ID<>l_grp_id;
delete from WF_ITEM_ACTIVITY_STATUSES_H
where PROCESS_ACTIVITY in
(select INSTANCE_ID from WF_PROCESS_ACTIVITIES
where PROCESS_ITEM_TYPE = p_item_type
or ACTIVITY_ITEM_TYPE = p_item_type);
delete from WF_ITEM_ACTIVITY_STATUSES
where PROCESS_ACTIVITY in
(select INSTANCE_ID from WF_PROCESS_ACTIVITIES
where PROCESS_ITEM_TYPE = p_item_type
or ACTIVITY_ITEM_TYPE = p_item_type);
delete from wf_item_attribute_values
where ITEM_TYPE = p_item_type;
delete from wf_items
where ITEM_TYPE = p_item_type;
delete from wf_notification_attributes NA
where exists (select 'X' from wf_notifications N
where N.notification_id = NA.notification_id
and N.message_type = p_item_type);
delete from wf_comments WC
where exists (select 'X' from wf_notifications N
where N.notification_id = WC.notification_id
and N.message_type = p_item_type);
delete from wf_notifications
where message_type = p_item_type;
delete from wf_routing_rule_attributes RA
where exists (select 'X' from wf_routing_rules R
where R.rule_id = RA.rule_id
and R.message_type = p_item_type);
delete from wf_routing_rules
where message_type = p_item_type;
delete from wf_activity_transitions PAT
where exists (select 'X' from wf_process_activities PAC
where PAT.FROM_PROCESS_ACTIVITY = PAC.instance_id
and PAC.PROCESS_ITEM_TYPE = p_item_type);
delete from wf_activity_attr_values ATV
where exists (select 'X' from wf_process_activities PAC
where ATV.PROCESS_ACTIVITY_ID = PAC.instance_id
and PAC.PROCESS_ITEM_TYPE = p_item_type);
delete from wf_process_activities
where PROCESS_ITEM_TYPE = p_item_type;
delete from wf_activity_attributes_tl
where ACTIVITY_ITEM_TYPE = p_item_type;
delete from wf_activity_attributes
where ACTIVITY_ITEM_TYPE = p_item_type;
delete from wf_activities_tl ACTL
where ACTL.ITEM_TYPE = p_item_type
and not exists(select 'X' from wf_process_activities PAC
where PAC.ACTIVITY_ITEM_TYPE='WFSTD'
and PAC.ACTIVITY_ITEM_TYPE=p_item_type
and PAC.ACTIVITY_NAME = ACTL.NAME);
delete from wf_activities ACT
where ACT.ITEM_TYPE = p_item_type
and not exists(select 'X' from wf_process_activities PAC
where PAC.ACTIVITY_ITEM_TYPE='WFSTD'
and PAC.ACTIVITY_ITEM_TYPE=p_item_type
and PAC.ACTIVITY_NAME = ACT.NAME);
delete from wf_message_attributes_tl
where message_type = p_item_type;
delete from wf_message_attributes
where message_type = p_item_type;
delete from wf_messages_tl
where type = p_item_type;
delete from wf_messages
where type = p_item_type;
delete from wf_item_attributes_tl
where ITEM_TYPE = p_item_type;
delete from wf_item_attributes
where ITEM_TYPE = p_item_type;
delete from wf_lookups_tl LUC
where exists (select 'X' from WF_LOOKUP_TYPES_TL LUT
where LUT.ITEM_TYPE = p_item_type
and LUT.LOOKUP_TYPE = LUC.LOOKUP_TYPE);
delete from wf_lookup_types_tl
where ITEM_TYPE = p_item_type;
delete from wf_item_types_tl
where NAME = p_item_type;
delete from wf_item_types
where NAME = p_item_type;
FOR rec IN (SELECT WF_ITEM_TYPE,WF_EVENT_NAME,WF_EVENT_SUBSCRIPTION_GUID
FROM CSM_HA_ACTIVE_WF_COMPONENTS
WHERE AUTO_DISABLE_FLAG='Y' AND ENABLED_ON_RECORD='N')
LOOP
IF rec.WF_EVENT_NAME IS NOT NULL THEN
IF rec.WF_EVENT_SUBSCRIPTION_GUID IS NULL THEN
UPDATE wf_events SET STATUS='DISABLED' WHERE lower(name)=lower(rec.WF_EVENT_NAME);
UPDATE wf_event_subscriptions SET STATUS='DISABLED'
WHERE GUID=rec.WF_EVENT_SUBSCRIPTION_GUID
AND EVENT_FILTER_GUID = (select guid from wf_events where lower(name)=lower(rec.WF_EVENT_NAME));
SELECT 1 into l_t
from wf_item_types
where upper(NAME) = upper(rec.WF_ITEM_TYPE);
SELECT trim(PROFILE_OPTION_VALUE) INTO l_prf
FROM FND_PROFILE_OPTION_VALUES
WHERE (APPLICATION_ID,PROFILE_OPTION_ID) IN (SELECT APPLICATION_ID,PROFILE_OPTION_ID
FROM FND_PROFILE_OPTIONS
WHERE PROFILE_OPTION_NAME ='CSM_HA_MODE')
AND LEVEL_ID=10001 AND LEVEL_VALUE=0;
SELECT CSM_HA_SESSION_INFO_S.NEXTVAL FROM DUAL;
SELECT session_id,session_start_time,ha_payload_start
INTO G_HA_SESSION_SEQUENCE,G_HA_START_TIME,G_HA_PAYLOAD_SEQUENCE_START
FROM CSM_HA_SESSION_INFO
WHERE SESSION_ID = (SELECT MIN(SESSION_ID) FROM CSM_HA_SESSION_INFO WHERE SESSION_END_TIME IS NULL);
SELECT CSM_HA_PAYLOAD_DATA_S.nextval INTO G_HA_PAYLOAD_SEQUENCE_START FROM DUAL;
UPDATE CSM_HA_SESSION_INFO
SET SESSION_START_TIME=G_HA_START_TIME,
HA_PAYLOAD_START=G_HA_PAYLOAD_SEQUENCE_START
WHERE SESSION_ID=G_HA_SESSION_SEQUENCE;
SELECT CSM_HA_PAYLOAD_DATA_S.nextval INTO G_HA_PAYLOAD_SEQUENCE_START FROM DUAL;
INSERT INTO CSM_HA_SESSION_INFO(SESSION_ID, SESSION_START_TIME, SESSION_END_TIME,
HA_PAYLOAD_START, HA_PAYLOAD_END, STATUS,
COMMENTS, CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
VALUES( G_HA_SESSION_SEQUENCE, G_HA_START_TIME, G_HA_END_TIME,
G_HA_PAYLOAD_SEQUENCE_START, G_HA_PAYLOAD_SEQUENCE_END, 'STARTED',
'HA Session in Progress', SYSDATE, 1,
SYSDATE, 1,1);
SELECT HA_PAYLOAD_START,HA_PAYLOAD_END FROM CSM_HA_SESSION_INFO WHERE SESSION_ID = b_session_id;
SELECT SEQ_MAPPING_ID,SEQUENCE_OWNER||'.'||SEQUENCE_NAME ,INCREMENT_BY
FROM CSM_HA_SEQ_MAPPINGS
WHERE BUSINESS_OBJECT_NAME IN (SELECT OBJECT_NAME
FROM CSM_HA_PAYLOAD_DATA
WHERE HA_PAYLOAD_ID between b_payload_start AND b_payload_end
AND DML_TYPE='I');
CSM_UTIL_PKG.log( 'No Business Object Sequence seems to have been updated in this recording session',
'CSM_HA_EVENT_PKG.SAVE_SEQUENCE', FND_LOG.LEVEL_STATEMENT);
execute immediate 'SELECT ' ||l_sequence_list(j) || '.NEXTVAL - '||l_inc_list(j)||' FROM dual'
INTO l_curr_seq_value;
INSERT INTO CSM_HA_SESSION_SEQ_VALUES(SESSION_ID,SEQ_MAPPING_ID,
RECORDED_SEQUENCE,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
VALUES(p_session_id,l_seq_map_list(j),l_curr_seq_value,sysdate,1,sysdate,1,1);
UPDATE CSM_HA_SESSION_SEQ_VALUES
SET RECORDED_SEQUENCE = l_curr_seq_value
WHERE SESSION_ID=p_session_id
AND SEQ_MAPPING_ID = L_SEQ_MAP_LIST(J)
AND RECORDED_SEQUENCE < l_curr_seq_value;
execute immediate 'SELECT ' ||l_sequence_list(j) || '.NEXTVAL FROM dual'
INTO l_curr_seq_value;
UPDATE CSM_HA_SESSION_SEQ_VALUES
SET APPLY_SEQUENCE = l_curr_seq_value
WHERE SESSION_ID=p_session_id
and SEQ_MAPPING_ID = L_SEQ_MAP_LIST(J)
RETURNING APPLY_SEQUENCE,RECORDED_SEQUENCE INTO l_start_seq_value,l_end_seq_value;
/* This select actually increments the sequence value */
execute immediate 'SELECT ' ||L_SEQUENCE_LIST(J) || '.NEXTVAL FROM dual'
into L_APPLIED_SEQUENCE_VALUE;
UPDATE CSM_HA_SESSION_SEQ_VALUES
set AFTER_APPLY_SEQUENCE = L_APPLIED_SEQUENCE_VALUE
WHERE SESSION_ID=p_session_id
and SEQ_MAPPING_ID = L_SEQ_MAP_LIST(J);
UPDATE JTM_CON_REQUEST_DATA
SET EXECUTE_FLAG='N'
WHERE PRODUCT_CODE='CSM'
AND CATEGORY='LOOKUP'
AND (PACKAGE_NAME,PROCEDURE_NAME) NOT IN
(('CSM_LOBS_EVENT_PKG','CONC_DOWNLOAD_ATTACHMENTS'));
FOR rec IN (SELECT LINK_ID
FROM CS_INCIDENT_LINKS
WHERE CREATION_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.LINK_ID;
FOR rec IN (SELECT LINK_ID
FROM CS_INCIDENT_LINKS
WHERE CREATION_DATE < G_HA_START_TIME
AND LAST_UPDATE_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.LINK_ID;
FOR rec IN (SELECT LINK_ID
FROM CS_INCIDENT_LINKS_EXT
WHERE CREATION_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.LINK_ID;
FOR rec IN (SELECT LINK_ID
FROM CS_INCIDENT_LINKS
WHERE CREATION_DATE < G_HA_START_TIME
AND LAST_UPDATE_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.LINK_ID;
FOR rec IN (SELECT INCIDNT_ATTR_VAL_ID
FROM CUG_INCIDNT_ATTR_VALS_B
WHERE CREATION_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.INCIDNT_ATTR_VAL_ID;
FOR rec IN (SELECT INCIDNT_ATTR_VAL_ID
FROM CUG_INCIDNT_ATTR_VALS_B
WHERE CREATION_DATE < G_HA_START_TIME
AND LAST_UPDATE_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.INCIDNT_ATTR_VAL_ID;
FOR rec IN (SELECT ACCESS_HOUR_ID
FROM CSF_ACCESS_HOURS_B
WHERE CREATION_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.ACCESS_HOUR_ID;
FOR rec IN (SELECT ACCESS_HOUR_ID
FROM CSF_ACCESS_HOURS_B
WHERE CREATION_DATE < G_HA_START_TIME
AND LAST_UPDATE_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.ACCESS_HOUR_ID;
FOR rec IN (SELECT REQUIRED_SKILL_ID
FROM CSF_REQUIRED_SKILLS_B
WHERE CREATION_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.REQUIRED_SKILL_ID;
FOR rec IN (SELECT REQUIRED_SKILL_ID
FROM CSF_REQUIRED_SKILLS_B
WHERE CREATION_DATE < G_HA_START_TIME
AND LAST_UPDATE_DATE >= G_HA_START_TIME)
LOOP
l_pk_value_list(1):=rec.REQUIRED_SKILL_ID;
UPDATE CSM_HA_SESSION_INFO
SET SESSION_END_TIME = SYSTIMESTAMP,
HA_PAYLOAD_END = G_HA_PAYLOAD_SEQUENCE_END,
STATUS = 'COMPLETED',
COMMENTS = 'HA Recording session successfully completed.',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = 1
WHERE SESSION_ID = (SELECT MIN(SESSION_ID) FROM CSM_HA_SESSION_INFO WHERE SESSION_END_TIME IS NULL);
SELECT CSM_HA_PAYLOAD_DATA_S.nextval INTO G_HA_PAYLOAD_SEQUENCE_END FROM DUAL;
SELECT FOREIGN_KEY_COLUMN
FROM CSM_HA_AUX_MAPPINGS
WHERE BO_TABLE_NAME=AO_TABLE_NAME
AND BO_TABLE_NAME=p_object_name
AND AUX='N';
SELECT OBJECT_NAME,PK_VALUE INTO l_bo_name,l_pk_value
FROM CSM_HA_PAYLOAD_DATA
WHERE HA_PAYLOAD_ID=p_parent_payload_id;
FOR rec IN (SELECT AO_TABLE_NAME,FETCH_SQL
FROM CSM_HA_AUX_MAPPINGS
WHERE BO_TABLE_NAME=l_bo_name
AND AUX='Y'
AND BO_TABLE_NAME <> AO_TABLE_NAME
AND FETCH_SQL IS NOT NULL
AND ENABLED_FLAG='Y' ORDER BY AUX_MAPPING_ID)
LOOP
l_ax_pk_column := get_pk_column_name(rec.AO_TABLE_NAME);
SELECT CSM_HA_PAYLOAD_DATA_S.nextval INTO l_pld_id FROM DUAL;
INSERT INTO CSM_HA_PAYLOAD_DATA(HA_PAYLOAD_ID ,OBJECT_NAME , PK_VALUE, PARENT_PAYLOAD_ID,DML_TYPE , PAYLOAD, CONTEXT ,MOBILE_DATA, PROCESSED ,
STATUS ,COMMENTS ,CREATION_DATE , CREATED_BY , LAST_UPDATE_DATE ,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
VALUES(l_pld_id,p_TABLE_NAME,l_pk_values,NVL(p_parent_payload_id,l_pld_id),p_dml_type,xmltype(l_XML_PAYLOAD),xmltype(l_XML_CONTEXT),p_mobile_data,'N',NULL,NULL,SYSDATE,1,SYSDATE,1,1);
SELECT NVL(last_run_date,to_date(1,'J'))
FROM jtm_con_request_data
WHERE package_name = 'CSM_LOBS_EVENT_PKG'
AND procedure_name = 'CONC_DOWNLOAD_ATTACHMENTS';
FOR rec IN (SELECT DOCUMENT_ID FROM FND_DOCUMENTS a WHERE CREATION_DATE > l_last_run_date
AND NOT EXISTS(SELECT 1 FROM CSM_HA_PAYLOAD_DATA b
WHERE HA_PAYLOAD_ID > G_HA_PAYLOAD_SEQUENCE_START
AND OBJECT_NAME='FND_DOCUMENTS'
AND PK_VALUE=to_char(a.DOCUMENT_ID)
AND DML_TYPE='I'))
LOOP
l_PK_NAME_LIST(1):='DOCUMENT_ID'; l_PK_TYPE_LIST(1):='NUMBER'; l_pk_value_list(1):= to_char(rec.DOCUMENT_ID);
FOR rec IN (SELECT DOCUMENT_ID FROM FND_DOCUMENTS a WHERE CREATION_DATE < l_last_run_date
AND LAST_UPDATE_DATE > l_last_run_date
AND NOT EXISTS(SELECT 1 FROM CSM_HA_PAYLOAD_DATA b
WHERE HA_PAYLOAD_ID > G_HA_PAYLOAD_SEQUENCE_START
AND OBJECT_NAME='FND_DOCUMENTS'
AND PK_VALUE=to_char(a.DOCUMENT_ID)
AND DML_TYPE='U'))
LOOP
l_PK_NAME_LIST(1):='DOCUMENT_ID'; l_PK_TYPE_LIST(1):='NUMBER'; l_pk_value_list(1):= to_char(rec.DOCUMENT_ID);