Incremental load issue

[vireshkolagimat]

Dear All,

I've implemented the incremental reload to extract data from Oracle EBS. However i am facing below issue.

Ex: I've an order number which was booked on 20-Sep-18 and status of the order was Picked. Later the order got closed and the status got changed to Shipped on say 17-Oct-18.

But still the status of the order is still showing as Picked instead of Shipped.

I've a column Last_modified_date and using the same column to get the max date to perform incremental load.

If do i full reload, it will show the order status as shipped.

Thanks for the suggestion and help.

Regards,

Viresh

[sudhakar_budde]

Hi Viresh,

First check all records of that Orderno = 1234 using sql query on your database client (e.g. Toad). This will show you the tracking of the status.

Then check what you are getting for the last_modified_date for that Orderno=1234, this way you know that you are using the correct date field to fetch the latest record.

If the status has changed more than once on a same day, then you may have multiple records? Just check that and adjust your query.

Hope this helps to get some idea on how to debug your issue....

Regards

SB

[sumeet-vaidya]

Hi Viresh,

As per the screenshot provided, Max(Date) = 20-Sep-2018 and the Status for the same is 'Picking'. so as per the scenario, incremental logic is working fine.

Please provide the incremental logic applied.

Regards

Sumeet

[vireshkolagimat]

Hi Sumeet,

Thanks for the reply. The date for shipped status should be 16 Oct. By mistake i put it as sep.

Below is the script for your reference.

// Set the variables

set _qvd_path = '..\Data\Qvds\Raw_Qvds\';

set _qvd_name = 'Sales_pipeline';

set vKeyField = ORDER_LINE_ID;

set vUpdateField = ORDER_LAST_UPDATE_DATE;

// 1. Check if qvd exists?

let vFileLen = FileSize('$(_qvd_path)$(_qvd_name).qvd');

trace '>> QVD Size: '$(vFileLen);

// 2. If QVD doesn't exist then perform a full reload

if len('$(vFileLen)') = 0 then

trace 'Full reload begins...';

$(_qvd_name):

LOAD * ;

SQL SELECT *

FROM APPS.XXEGC_QV_SALES_PIPELINE_V2;

let vRows = NoOfRows('$(_qvd_name)');

//exit Script

// 3. Max Daate field value into a QVD for the future reload

NewKey:

load

    num#(max(FieldValue('$(vUpdateField)', RecNo()))) as Key

AutoGenerate num#(FieldValueCount('$(vUpdateField)'));

let vNewKey = peek('Key', 0, 'NewKey');

let vKeyQVD = 'OrdersMaxKey_pipeline';

$(vKeyQVD):

load

    '$(vNewKey)' as MaxKey,

    '$(vRows)'   as [# of Rows Inserted],

    date(now())  as [Reload DateTime]

AutoGenerate(1) ; 

STORE  $(vKeyQVD)  into $(_qvd_path)$(vKeyQVD).qvd(qvd);

drop Table NewKey;

// 4. Store table data into QVD

store $(_qvd_name)  into $(_qvd_path)$(_qvd_name).qvd(qvd);

drop table $(_qvd_name);

//5. Reset variables

LET vNewKey = null();

LET vMaxKey = Null();

LET vRows   = null();

exit SCRIPT

// 1. if QVD exists then perform incremental reload

ELSEIF len('$(vFileLen)') > 0 then

TRACE 'Incremental reload begins...';

// 2. Extract Primary Key from the prior reload (stored in a qvd) for the WHERE clause

set vKeyQVD = 'OrdersMaxKey_pipeline';

$(vKeyQVD):

load

   date(MaxKey) as MaxKey

from

$(_qvd_path)$(vKeyQVD).qvd(qvd);

let vMaxKey = Peek('MaxKey');

trace '>>> Last Max Key value stored:  ' & $(vMaxKey);

drop table $(vKeyQVD);

// 3. Perform incremental reload

$(_qvd_name):

LOAD*  ;

SQL SELECT *

FROM APPS.XXEGC_QV_SALES_PIPELINE_V2

where  $(vUpdateField) > trunc(to_date('$(vMaxKey)','DD/MM/YYYY'));

let vRows = NoOfRows('$(_qvd_name)');

trace 'Total Rows:' $(vRows);

NewKey:

load

    num#(max(FieldValue('$(vUpdateField)', RecNo()))) as Key

AutoGenerate num#(FieldValueCount('$(vUpdateField)'));

// 4. Exit gracefully if no new records found

if $(vRows) = 0 then

  exit SCRIPT;

ELSE

// 5. Concatenate new records with records from the existing QVD

Concatenate($(_qvd_name))

load

   *

from

$(_qvd_path)$(_qvd_name).qvd(qvd)

Where not Exists($(vKeyField));

// 6. Store this table data into QVD

trace 'Storing table data into QVD';

store $(_qvd_name)  into $(_qvd_path)$(_qvd_name).qvd(qvd);

// 7. Store max Primary Key ID for the future incremental reload (QVD)

let vNewKey = peek('Key', 0, 'NewKey');

let vKeyQVD = 'OrdersMaxKey_pipeline';

$(vKeyQVD):

load

    '$(vNewKey)' as MaxKey,

    '$(vRows)'   as [# of Rows Inserted],

    date(now())  as [Reload DateTime]

AutoGenerate(1); 

drop table NewKey;

STORE  $(vKeyQVD)  into $(_qvd_path)$(vKeyQVD).qvd(qvd);

drop table $(_qvd_name);

Thanks.

[vireshkolagimat]

Hi, data looks fine at the back end.. The order status is shipped based on the last modified date.

The incremental load is not over writing the initial status.

Thank you.

[lorenzoconforti]

So basically you want to delete the old record and replace it with the new one? If that's the case the only way I can think of is to delete the updated records first and then to re-run the incremental load. See here: Incremental load to remove deleted records

[vireshkolagimat]

Hi, Thanks for the reply.

In my case the record is not deleted from the source database but only the status is getting changed.

what happens is when the order was booked initially it was in the Picked status and the next day the order got invoiced and status changed from picked to shipped.

Regards,

Viresh

[lorenzoconforti]

Understood, so, in my view you need first to delete the record from your table (i.e. the old record with the Picked status) and then load the incremental data. See the link I posted above