Presently I came across one of my friend's requirement for an IFB to delete all records from S_ORG_EXT table that were created on the previous day and with value of SRV_PROVDR_FLG as Y, so that IFB can be scheduled to run on a particular interval and delete process can be automated.
Here, I am sharing it with you guys and you can make use of it on any table and for deleting records in any time period just by changing the parameters given, as per your requirement.
[Siebel Interface Manager]
PROCESS = Acc_Delete
[Acc_Delete]
TYPE=SHELL
INCLUDE=Exp_record
[Exp_record]
TRANSACTION SQL = "DELETE FROM SIEBEL.S_ORG_EXT WHERE SRV_PROVDR_FLG='Y' AND FLOOR(SYSDATE-CREATED)='1'"
TYPE = EXPORT
BATCH = 1
TABLE = EIM_ACCOUNT
EXPORT MATCHES = S_ORG_EXT,(ROW_ID IS NULL)
Here, I am sharing it with you guys and you can make use of it on any table and for deleting records in any time period just by changing the parameters given, as per your requirement.
[Siebel Interface Manager]
PROCESS = Acc_Delete
[Acc_Delete]
TYPE=SHELL
INCLUDE=Exp_record
[Exp_record]
TRANSACTION SQL = "DELETE FROM SIEBEL.S_ORG_EXT WHERE SRV_PROVDR_FLG='Y' AND FLOOR(SYSDATE-CREATED)='1'"
TYPE = EXPORT
BATCH = 1
TABLE = EIM_ACCOUNT
EXPORT MATCHES = S_ORG_EXT,(ROW_ID IS NULL)
Hi Varun,
ReplyDeleteCan you please explain EXPORT MATCHES = S_ORG_EXT,(ROW_ID IS NULL)?
How EIM will export the data from S_ORG_EXT whose ROW_ID is null?
Ya Naimisha, you are right!!..no records will be exported and that's needed to satisfy the requirement here...Export process is included here only to make the IFB run. If you are coming across a requirement like here, where you need to include SYSDATE in IFB file, you can't include it with DELETE MATCHES parameter. You can only specify it with TRANSACTION SQL or SESSION SQL like parameters (Since the statements what we specify inside these parameter will be treated as normal DB queries).
DeleteHere, the criteria for the data you needed to delete is given in TRANSACTION SQL parameter (this parameter will be executed first before the eim process begins). Once this parameter is executed, export process will happen satisfying criteria given in EXPORT MATCHES parameter and thus IFB gets successfully executed (no data gets exported here).
Hope you got what I explained.
Yes I got it.
DeleteThanks alot.
Isn't it just equivalent to running a DELETE statement on the database ? Didn't understand the point of runnig an EIM job.
ReplyDeleteHi Joseph,
DeleteIts just equal to running an SQL against DB but as I pointed, my friend's requirement was to achieve it via an IFB and to schedule it, this was build.
yes You can execute directly SQL statement against s_org_ext table. why do we need eim job? Is it good practice? is it a interview questions?
ReplyDeleteRunning updates via SQL is not a recommended practice for Siebel and even Oracle clearly mentions that in the book shelf. But to reduce time, we might usually go for SQL updates which is not recommended.
Delete