E9.2 R47011 selecting data more than the actual data selection

PranikaC

Member
Hi Experts

Can someone help me .

1)We have data coming into JDE tables F47011 and F47012 from Boomi directly connecting to ODBC/database and entering data into JDE tables F47011 and F4701.
2)Boomi is scheduled to enter the data (Sales order ) in F47011 and F47012 every 5 min
3)R47011 picks data from both tables(F47011 and F47012) and enters into Sales order tables F4211 and F4201 (F4211 editline BSFN's)
4)R47011 is scheduled to run after every 15 min
5) There are 2 versions of R47011 with different data selection where DCTO= SF and in other version where DCTO=SW and both versions of the report runs in single threaded queue
6) Now the problem is whenever the data volume is high the data selection of the report picks the record more than actual.
e.g.:-
a. data selection is where EDSP=Y and DCTO=SF and KCOO=00005 or 00037 etc
b.let say report runs at
1)Lets say at 12:00,R47011 runs and there were 50 records already in the table F47011 with date 8th may and time <=12:00 (or 11:55 am)
2)At 12:00,Boomi also runs at the same time and it inserted 20 records with time 12:00 and date 8th may 2022
3)Now when report R47011 was running at 12:00 ,it picked 50 records that were already there in table F47011(time- 11:55 am ) and also 20+records of Boomi(time-12:00) as both ran at the same time and time stamp them as 12:00 in F47011 (records with time stamp 11:55 and 12:00)
4) But the problem is that R47011 has also picked records that came after 12:00 e.g. records that came at 12:05,12:10,12:30,12:40 etc.

Can someone tell why this is happening? Is this because Boomi is directly updating tables and thats why when data selection picks records ,its picking more than actual data selection? What can be the solution for this? Can we make another check in the data selection for Date and time (where Time <= 12:00 so that it picks the records equals to or less than time when report runs)

Data selection as-where EDSP=Y and DCTO=SF and KCOO=00005 or 00037 and date =current date and Time<=current time?

Kindly suggest
 

DSauve

Legendary Poster
How about spreading out when the report and Boomi run so they're not trying to run at the same time? Maybe a minute or two difference in timing could help.
 

BOster

Legendary Poster
Hi Experts

Can someone help me .

1)We have data coming into JDE tables F47011 and F47012 from Boomi directly connecting to ODBC/database and entering data into JDE tables F47011 and F4701.
2)Boomi is scheduled to enter the data (Sales order ) in F47011 and F47012 every 5 min
3)R47011 picks data from both tables(F47011 and F47012) and enters into Sales order tables F4211 and F4201 (F4211 editline BSFN's)
4)R47011 is scheduled to run after every 15 min
5) There are 2 versions of R47011 with different data selection where DCTO= SF and in other version where DCTO=SW and both versions of the report runs in single threaded queue
6) Now the problem is whenever the data volume is high the data selection of the report picks the record more than actual.
e.g.:-
a. data selection is where EDSP=Y and DCTO=SF and KCOO=00005 or 00037 etc
b.let say report runs at
1)Lets say at 12:00,R47011 runs and there were 50 records already in the table F47011 with date 8th may and time <=12:00 (or 11:55 am)
2)At 12:00,Boomi also runs at the same time and it inserted 20 records with time 12:00 and date 8th may 2022
3)Now when report R47011 was running at 12:00 ,it picked 50 records that were already there in table F47011(time- 11:55 am ) and also 20+records of Boomi(time-12:00) as both ran at the same time and time stamp them as 12:00 in F47011 (records with time stamp 11:55 and 12:00)
4) But the problem is that R47011 has also picked records that came after 12:00 e.g. records that came at 12:05,12:10,12:30,12:40 etc.

Can someone tell why this is happening? Is this because Boomi is directly updating tables and thats why when data selection picks records ,its picking more than actual data selection? What can be the solution for this? Can we make another check in the data selection for Date and time (where Time <= 12:00 so that it picks the records equals to or less than time when report runs)

Data selection as-where EDSP=Y and DCTO=SF and KCOO=00005 or 00037 and date =current date and Time<=current time?

Kindly suggest
If you have not already done so, try make the KCOO part of your WHERE clause an IN instead of an OR. In other words

instead of
...KCOO = 00005 or KCOO = 00037
make it a list of values so the WHERE clause is
...KCOO in (00005, 00037)
 

PranikaC

Member
If you have not already done so, try make the KCOO part of your WHERE clause an IN instead of an OR. In other words

instead of
...KCOO = 00005 or KCOO = 00037
make it a list of values so the WHERE clause is
...KCOO in (00005, 00037)
Hi ,Thanks for your reply but my data selection is already where KCOO in(00005,00037) as i am using this data selection as outer data selection and not inside the report (using Set User selection).
 

kiran4hari

Well Known Member
This issue can only happen if two versions (same version) ran in parallel, the second one picks the records that are not yet processed by first version. The UBE picks up the records that exists in table at the time of data selection getting executed (running select query on table). After that it will not be aware of new records inserted in to the table.

May be this JOB was in Waiting Status for a long time and started late as Queue was not available. One of the possibilities.
 

Craig_Potter

Reputable Poster
Hi Experts

Can someone help me .

1)We have data coming into JDE tables F47011 and F47012 from Boomi directly connecting to ODBC/database and entering data into JDE tables F47011 and F4701.
2)Boomi is scheduled to enter the data (Sales order ) in F47011 and F47012 every 5 min
3)R47011 picks data from both tables(F47011 and F47012) and enters into Sales order tables F4211 and F4201 (F4211 editline BSFN's)
4)R47011 is scheduled to run after every 15 min
5) There are 2 versions of R47011 with different data selection where DCTO= SF and in other version where DCTO=SW and both versions of the report runs in single threaded queue
6) Now the problem is whenever the data volume is high the data selection of the report picks the record more than actual.
e.g.:-
a. data selection is where EDSP=Y and DCTO=SF and KCOO=00005 or 00037 etc
b.let say report runs at
1)Lets say at 12:00,R47011 runs and there were 50 records already in the table F47011 with date 8th may and time <=12:00 (or 11:55 am)
2)At 12:00,Boomi also runs at the same time and it inserted 20 records with time 12:00 and date 8th may 2022
3)Now when report R47011 was running at 12:00 ,it picked 50 records that were already there in table F47011(time- 11:55 am ) and also 20+records of Boomi(time-12:00) as both ran at the same time and time stamp them as 12:00 in F47011 (records with time stamp 11:55 and 12:00)
4) But the problem is that R47011 has also picked records that came after 12:00 e.g. records that came at 12:05,12:10,12:30,12:40 etc.

Can someone tell why this is happening? Is this because Boomi is directly updating tables and thats why when data selection picks records ,its picking more than actual data selection? What can be the solution for this? Can we make another check in the data selection for Date and time (where Time <= 12:00 so that it picks the records equals to or less than time when report runs)

Data selection as-where EDSP=Y and DCTO=SF and KCOO=00005 or 00037 and date =current date and Time<=current time?

Kindly suggest
Also assume that data selection that says "data selection is where EDSP=Y and DCTO=SF and KCOO=00005 or 00037 etc" is actually EDSP !=Y OR EDSP=blank/null
 

PranikaC

Member
This issue can only happen if two versions (same version) ran in parallel, the second one picks the records that are not yet processed by first version. The UBE picks up the records that exists in table at the time of data selection getting executed (running select query on table). After that it will not be aware of new records inserted in to the table.

May be this JOB was in Waiting Status for a long time and started late as Queue was not available. One of the possibilities.
I actaully ran 2 different versions of the same report on different single threaded queue.So 2 versions were running in parallel but data selection was different for 2 versions (where DCTO=SF and for another version where DCTO=SW).So chances are less that it picked records more than actual data selection but the customer has reported this problem.

Just to understand clearly what you said. Do you mean :-
(1)Let says first run of the report ran at 12:00 pm and picked 100 records and it started processing them and took approx 1 hour (lets say till 1 pm)
(2)Another instance of the report (same version as above)is in waiting since 1 hour and it will keep picking records in data selection that keep coming from Boomi into tables F47011 and F47012 (as Boomi is inserting records after every 5 min)
3)So this second report will pick all records that will come in JDE tables from 12:00 to 1 pm and then will process all records at 1 pm

Currently the set up is same as i mentioned above but the problem is that this second report is also picking the records that came at 1:30 pm ,1:40 pm or 1:150 pm?

Can we apply the date and time solution as i mentioned above?? Please suggest . Thanks
 

kiran4hari

Well Known Member
I actaully ran 2 different versions of the same report on different single threaded queue.So 2 versions were running in parallel but data selection was different for 2 versions (where DCTO=SF and for another version where DCTO=SW).So chances are less that it picked records more than actual data selection but the customer has reported this problem.

Just to understand clearly what you said. Do you mean :-
(1)Let says first run of the report ran at 12:00 pm and picked 100 records and it started processing them and took approx 1 hour (lets say till 1 pm)
(2)Another instance of the report (same version as above)is in waiting since 1 hour and it will keep picking records in data selection that keep coming from Boomi into tables F47011 and F47012 (as Boomi is inserting records after every 5 min)
3)So this second report will pick all records that will come in JDE tables from 12:00 to 1 pm and then will process all records at 1 pm

Currently the set up is same as i mentioned above but the problem is that this second report is also picking the records that came at 1:30 pm ,1:40 pm or 1:150 pm?

Can we apply the date and time solution as i mentioned above?? Please suggest . Thanks
Your understanding is correct in the 3 points you have mentioned above.

The second report should not pick records that got inserted after 1 PM as it already executed the SQL and selected the records for processing. Once UBE selects records for processing, it only works with that data set. This UBE will not have any visibility of the records that got inserted later. (Unless there are customizations added to process it this way)

I am not sure how reliable time in JDE tables especially for data selection purposes. I hope the current time in Boomi and JDE are in sync. Its not like one is using CST and the other is using EST.

The last option is to see if you can enable logs and check the SQL Query to see if it helps. You can check execution details and check the number of records processed.
 
Top