Querying sys_history_line tables

[poyntzj]

I have a requirement to query against the sys_history_line table and retrieve some information.

I had a request to display the "fulfilment" date on a Portal record.

On checking a couple of records, I can query the sys_history_set and then the sys_history_line

I soon realised this was not great for the portal so I have added a new field to the sc_request and sc_req_item tables.

However, now I am being asked to back populate the data

i have had a look at pulling from sys_audit and from the sys_history_line tables.

the later are better for my needs.

I am however finding that when I run a Background Script to get some data I am offten getting a lot of null information for the field I am after.

Script: REQ0015788 : b76ef6ec6f1782402a3e9accbb3ee4ac : 2015-10-21 10:03:36
*** Script: REQ0015776 : 43b86b0d6f6346007115ca727d3ee404 : 2015-11-04 12:40:15
*** Script: REQ0015763 : 6d2d55df6f5f4200b872129e5d3ee424 : 2015-10-29 16:01:48
*** Script: REQ0015756 : 22db3cd76f5f4200b872129e5d3ee43b : 2015-11-05 10:49:29
*** Script: REQ0015750 : d05479956f970200b872129e5d3ee47a : 2015-11-05 10:49:30
*** Script: REQ0015749 : 421179d16f970200b872129e5d3ee4c9 : 2015-11-05 10:49:32
*** Script: REQ0015744 : c2d7c15d6f570200b872129e5d3ee439 : 2015-10-29 16:24:22
*** Script: REQ0015737 : cc16a0496f930200b872129e5d3ee435 : null
*** Script: REQ0015605 : f35b82dc6f5f42402a3e9accbb3ee4eb : null
*** Script: REQ0015600 : 74f84a5c6f5f42402a3e9accbb3ee492 : null

If I now go into the record REQ0015744 and come straight back out and run the Backgroun Script again, I now see the information

*** Script: REQ0015788 : b76ef6ec6f1782402a3e9accbb3ee4ac : 2015-10-21 10:03:36
*** Script: REQ0015776 : 43b86b0d6f6346007115ca727d3ee404 : 2015-11-04 12:40:15
*** Script: REQ0015763 : 6d2d55df6f5f4200b872129e5d3ee424 : 2015-10-29 16:01:48
*** Script: REQ0015756 : 22db3cd76f5f4200b872129e5d3ee43b : 2015-11-05 10:49:29
*** Script: REQ0015750 : d05479956f970200b872129e5d3ee47a : 2015-11-05 10:49:30
*** Script: REQ0015749 : 421179d16f970200b872129e5d3ee4c9 : 2015-11-05 10:49:32
*** Script: REQ0015744 : c2d7c15d6f570200b872129e5d3ee439 : 2015-10-29 16:24:22
*** Script: REQ0015737 : cc16a0496f930200b872129e5d3ee435 : null
*** Script: REQ0015605 : f35b82dc6f5f42402a3e9accbb3ee4eb : null

arrgh Is there any way I can make this work - it will be a background task run once for the sc_request and sc_req_item tables

It is as if I need to open and inspect each record or somehow force the history tables to work

I did try a query against the sys_audit table and it was seemingly having a similar issue

any ideas would be gratefully received

[poyntzj]

I shall have a look at this a little later this week

Cheers

[bernyalvarado]

Sounds good Julian!

The business rule is really straight forward (since it's just one line of code). Please let me know if you run into any issues or if you have any further questions.

Thanks,

Berny

[poyntzj]

Tried and it on a set of test records it has worked as intended.

Cheers