|
|
|
Importing Membership Transactions
The purpose of this topic is to describe the process of setting up and maintaining an automated service to upload membership transaction records into the system.
There are 3 main Components of the process:
Unzip the file. It should create the following directory (folder) structure.
Note: The drive letter may be different.
Directory |
Purpose |
D:\mem_trx_upload |
Root directory under which all the folders are organized. |
D:\mem_trx_upload\badbox |
This folder should contain files with extension .bad. These files are generated by Oracle Sql Loader for the records which it was not able to upload into database. Typically there should not be any files in this folder but incase there are some files then someone need to look into it why they failed. |
D:\mem_trx_upload\code |
This folder contains the required code like the batch file and control file and .sql file required for the operation. It also contains an output file called outrun.txt which contains output of the scripts run. |
D:\mem_trx_upload\doc |
This folder contains the documents. |
D:\mem_trx_upload\inbox |
This folder is where the user needs to copy the files which they want to upload. |
D:\mem_trx_upload\logbox |
This folder will contain the log files (.log) generated by Oracle Sql Loader. These logs will tell how many records were uploaded, how many failed and why. |
D:\mem_trx_upload\outbox |
After a file is uploaded, it will be moved in this folder from the inbox folder. |
Setup a Control file. A control file determines what will be uploaded from the flat file and in what format it will be uploaded, etc.
A sample control file (located in folder D:\mem_trx_upload\code\mem_trx_upload.ctl) is provided. You can edit this file to suite your requirements or create a new one in order to match the file format.
options(skip=1) LOAD DATA APPEND INTO TABLE IMP_MEMTRX_TEMP FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( |
|
EXT_RESORT ,BATCH_ID ,SEQ_NO ,PMS_RESV_NO ,BEGIN_DATE ,MEMBERSHIP_TYPE ,MEMBERSHIP_CARD_NO ,TOTAL_BASE_POINTS ,TOTAL_BONUS_POINTS ,TOTAL_MISC_POINTS ,MEMBERSHIP_TRX_ID ,MEMBERSHIP_TRX_DATE ,IMPORT_DATE ,IMPORT_STATUS ,INTERFACE_ID ,RECORD_TYPE ,POINTS_CALCULATED_YN ,ADJUSTMENT_YN ,BILLING_GROUP ,BASE_BILLING_GROUP ,BONUS_BILLING_GROUP ) |
CHAR INTEGER EXTERNAL INTEGER EXTERNAL CHAR DATE (8) 'YYYYMMDD' CHAR CHAR INTEGER EXTERNAL INTEGER EXTERNAL INTEGER EXTERNAL CONSTANT "" CONSTANT "" SYSDATE CONSTANT "NEW" CONSTANT "OXI-OPMS" CONSTANT "OT" CONSTANT "Y" CONSTANT "Y" CHAR CHAR CHAR |
Note:
Setup a Batch file. The batch file executes commands and invokes various utilities like Oracle Sql Loader and Sql Plus.
A sample batch file (located in folder D:\mem_trx_upload\code\mem_trx_upload.bat) is provided. You can edit this file to suite your requirements or create a new one. In this file, you may wish to change values marked in bold.
The assumption is that the files are located on drive D: and File to upload have extension .lst.
You may also have to give path of sqlplus if required.
cd D:\mem_trx_upload\code
ren outrun.txt outrun2.bak
echo on
echo '############################## Process start #############################' >> outrun.txt
date /t >> outrun.txt
time /t >> outrun.txt
dir D:\mem_trx_upload\inbox\*.lst /b >> outrun.txt
for %%f in (D:\mem_trx_upload\inbox\*.lst) do D:\oracle\product\10.2.0\client_1\BIN\sqlldr sqlldr userid= v46orsdev/v46orsdev@rkv46ors data=%%f control= mem_trx_upload.ctl errors=50000 log=%%f.log >> outrun.txt
for %%a in (D:\mem_trx_upload\inbox\*.lst) do xcopy %%a D:\mem_trx_upload\outbox /y /f >> outrun.txt
for %%a in (D:\mem_trx_upload\inbox\*.bad) do xcopy %%a D:\mem_trx_upload\badbox /y /f >> outrun.txt
for %%a in (D:\mem_trx_upload\inbox\*.log) do xcopy %%a D:\mem_trx_upload\logbox /y /f >> outrun.txt
for %%a in (D:\mem_trx_upload\inbox\*.lst) do del %%a /q >> outrun.txt
for %%a in (D:\mem_trx_upload\inbox\*.bad) do del %%a /q >> outrun.txt
for %%a in (D:\mem_trx_upload\inbox\*.log) do del %%a /q >> outrun.txt
D:\10gDS\bin\sqlplusw.EXE [oracle user]/[oracle pass]@[oracle service]@runme.sql
type daily_rejects_results.LST >> outrun.txt
date /t >> outrun.txt
time /t >> outrun.txt
echo '############################## Process End ###############################' >> outrun.txt
type outrun2.bak >> outrun.txt
del outrun2.bak /q
del daily_rejects_results.LST /q
__________________________________________________________________
Now, change the D:\mem_trx_upload\code\runme.sql.
Make the following changes to the runme.sql SCRIPT
1) Update the initialization below before executing the script:
imp_data.set_user('SUPERVISOR', 'XXXXXXX', 'CRO');
2) Deleting old records which are uploaded till last week.
trunc(sysdate) - 7 change number of days accordingly.
Setup a service. Here is an example how to set up a service in Windows 2000 operating system.
Go to Start>Settings>Control Panel>Schedule Tasks>Add Schedule Task and run the wizard to setup a task and make to execute the .bat file. The service can be enabled or disabled as per requirement.
On daily basis the user should copy the flat file to be uploaded into inbox folder. The file should have .lst extension (however this can be changed in the .bat file ) and each file should have a unique file name so as not to overwrite older files. The schedule task will pick up this file and upload it into the OPERA database. Then it will move the file to outbox folder.
From time to time depending on the usage (say weekly) someone needs to look into to check if all records are getting uploaded and processed ok. Runme.sql script located in folder code is set to delete records successfully uploaded after a week.
Following tasks will need to be done
