We have a daily scheduled data extractor to extract data from oracle database with incremental script ( below script), when we cross verified the data after some point of time let’s say 1 month we found that some data is missing it’s not up to date. please check the below script and advise...
// Loading AR Customer Sites
SET vQvdFile='$(vFolderSourceData)CustomerMaster.qvd';
LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);
Set vToday = date(Today(),'DD-MMM-YYYY hh:mi:ss');
Trace >>> Running Customr Sites Scripts;
Trace >>> Running Script at $(vToday);
IF $(vQvdExists) THEN
maxdateTab:
LOAD max(Last_Update_Date) as maxdate
FROM $(vQvdFile) (qvd);
LET vIncrementalExpression = 'WHERE LAST_UPDATE_DATE >= ' & chr(39) & date(peek('maxdate'),'DD-MMM-YYYY') &chr(39) ;
DROP table maxdateTab;
ELSE
LET vIncrementalExpression = '';
END IF
TRACE >>> SQL Query Where Clause : $(vIncrementalExpression);
[AR_CustomerSites] :
Load Distinct
COMPANY as Company,
CUSTOMER_ID as Customer_Id,
CUSTOMER_NUMBER as Customer_Number,
CUSTOMER_NAME as Customer_Name,
PARTY_ID as Party_Id,
PARTY_SITE_ID as Party_Site_Id,
CREATION_DATE as Creation_Date,
CREATED_BY as Created_By,
CUSTOMER_CATEGORY_CODE as Customer_Category_Code,
TRADE_LICENCE_NO as Trade_License_No,
TRADE_LIC_EXP_DATE as Trade_License_Expiry_Date,
SALESREP_ID as Salesrep_Id,
ADDRESS1 as Address1,
ADDRESS2 as Address2,
ADDRESS3 as Address3,
ADDRESS4 as Address4,
CITY as City,
COUNTRY_CODE as Country_Code,
PO_BOX_NUMBER as PO_Box,
SITE_NAME as Site_Name,
SITE_CODE as Site_Code,
SITE_USE_CODE as Site_Use_Code,
SITE_USE_ID as Site_Use_Id,
LOCATION_ID as Location_Id,
LOCATION_CODE as Location_Code,
ORG_ID as Org_Id,
ORIG_SYSTEM_REFERENCE as Org_System_Reference,
CUST_SITE_STATUS as Cust_Site_Status,
CREDIT_LIMIT as Credit_Limit,
TERM_ID as Payment_Term_Id,
PARTY_SITE_NAME as Pasty_Site_Name,
CREDIT_HOLD as Credit_Hold,
CREDIT_CHECK as Credit_Check,
COLLECTOR as Collector,
GL_ID_REC as Code_Combination_Id,
REC_ACCOUNT as GL_Account,
PHONE as Phone,
FAX as Fax,
CONTACT_PERSON as Contact_Person,
CONTACT_PERSON_PHONE as Contact_Person_Phone,
LAST_UPDATE_DATE as Last_Update_Date,
CREDIT_HOLD_STATUS as Credit_Hold_Status ;
SQL select
COMPANY,
CUSTOMER_ID,
CUSTOMER_NUMBER,
CUSTOMER_NAME,
PARTY_ID,
PARTY_SITE_ID,
CREATION_DATE,
CREATED_BY,
CUSTOMER_CATEGORY_CODE,
TRADE_LICENCE_NO,
TRADE_LIC_EXP_DATE,
SALESREP_ID,
ADDRESS1,
ADDRESS2,
ADDRESS3,
ADDRESS4,
CITY,
COUNTRY_CODE,
PO_BOX_NUMBER,
SITE_NAME,
SITE_CODE,
SITE_USE_CODE,
SITE_USE_ID,
LOCATION_ID,
LOCATION_CODE,
ORG_ID,
ORIG_SYSTEM_REFERENCE,
CUST_SITE_STATUS,
CREDIT_LIMIT,
TERM_ID,
PARTY_SITE_NAME,
CREDIT_HOLD,
CREDIT_CHECK,
COLLECTOR,
GL_ID_REC,
REC_ACCOUNT,
PHONE,
FAX,
CONTACT_PERSON,
CONTACT_PERSON_PHONE,
LAST_UPDATE_DATE,
CREDIT_HOLD_STATUS
from APPS.XXBI_CUST_SITE_DETAILS $(vIncrementalExpression) ;
LET vNumRows = NoOfRows('AR_CustomerSites');
TRACE >>> Table --> AR_CustomerSites New records loaded with : $(vNumRows);
IF $(vQvdExists) THEN
CONCATENATE (AR_CustomerSites)
LOAD * FROM $(vQvdFile) (qvd)
WHERE NOT exists(Party_Site_Id);
END IF
STORE [AR_CustomerSites] into $(vQvdFile) (qvd);
LET vNumRows = NoOfRows('AR_CustomerSites');
TRACE >>> Table --> AR_CustomerSites Table loaded with : $(vNumRows);
Drop Table [AR_CustomerSites];
rergards
Madhu
