Clarity

Hi All,

I am using on demand portal. Actuals are coming from third party tool by using interface.

We have created one custom object to store the values which we are getting from interface

Actuals are posting by using transaction XOG IN. The actuals which are received from third party may change by day by day and month by month.

So, I need to check the values of previous and current , if the current values are greater than the previous, the i should xog in the difference in gel script.

For Example:

1.Previous value is 600 and current value is 650 , then i need to post= 50 (650-600)

2.If Previous value is 650 and current is 600, then i need to post = -50(650-600)

Could some one help me on this to have this conditions in GEL script.

Please advise.

Thanks,

Then

Hi

The requirement is not clear. my assumption is the current month information comes from interface which stores in custom objects and previous month means, whatever actual's posted in the system for corresponding resource.

Is it correct?

Thanks

Senthil

Yes. correct!

what are the columns defined in Custom objects?

Thanks

Senthil

Hi Senthil,

Thank you for your reply

I have attached the screen

You can build a single select query to get actual difference and build the XOG transactions.

<core:if test="${act_diff != 0}">
Then do this...............
</core:if>

Thanks

Senthil

Hi Thenmozhi,

As Senthil, mentioned in this thread , the object design needs to be clear.

For example, it seems like you are reading in actuals from a third party system in a custom object, are you storing the actuals , per day in separate rows ? Is the actuals being stored in a single column for respective dates.

For example , lets say you are storing the actuals on a per day basis , in separate rows :

By SQL approach you , write a query which compares the column , in this case "act_test" (supposedly holding the actual values), the following basic SQL could help you to get the previous and latest value, based upon the latest entry made. (Please note this query is just for example purpose, if you have bulk data then you will need to use Lag over partition , or Rank functions. You can check online examples for syntax)

Query 1:

SELECT *
from
(select rownum r1, act_test from odf_ca_sam_test_obj)t1,
(select rownum r2, act_test from odf_ca_sam_test_obj)t2
Where t2.r2 = (t1.r1 - 1)
And t1.act_test <= t2.act_test

Output:

Based upon your requirements that is previous value of actuals less than (<) or greater than (>) latest entered value, you can accordingly introduce a case statement in the SQL:

Query 2:

SELECT
(case when t1.act_test <t2.act_test then (t1.act_test - t2.act_test) else (t2.act_test - t1.act_test) end) test
from
(select rownum r1, act_test from odf_ca_sam_test_obj)t1,
(select rownum r2, act_test from odf_ca_sam_test_obj)t2
Where t2.r2 = (t1.r1 - 1)
And t1.act_test <= t2.act_test

Output:

Based upon your requirement you could either embed such queries in the GEL script and then XOG in the difference.

Otherwise, you can get all values in an initial SQL statement, similar to query 1, in the GEL script, store in variables and test it out in the GEL script, using the Core : IF tags

Example:

<core:if test="${act_test < act_test_1}">
Then do this...............
</core:if>

<core:if test="${act_test > act_test_1}">
Then do this...............
</core:if>

Hope this helps.

Regards,

Samik

Hi Samik,

thank you for your reply.

I will try this.

Regards,

Then

Slight modifications to the core tags, it should be represented as follows:

<core:if test="${act_test &lt; act_test_1}">
Then do this...............
</core:if>

<core:if test="${act_test &gt; act_test_1}">
Then do this...............
</core:if>

Regards,

Samik

Hi Samik,

I using the below query:

SELECT *
from
(select rownum r1, z_a_cic from odf_ca_ne_actual_if_his2)t1,
(select rownum r2, z_a_cic from odf_ca_ne_actual_if_his2)t2
Where t2.r2 = (t1.r1 - 1)
And t1.z_a_cic <= t2.z_a_cic

Output:

2 644 1 779
3 150 2 644

Here I have changed from 300 to 150. But here the output I could not understand what it is referring.

IS there any way to show the Old value and New value of the column?

Please advise.

Thanks,

Then

You should get previous actual values from transaction table and current values from custom object and compare the values.

You should check the following three tables to get previous actual values.

Sample Query as below. You need to get required column information from custom object when you build transaction XOG XML and actuals should be ACT_DIFF column value.

SELECT A.TRANSDATE, A.PROJECT_CODE, A.RESOURCE_CODE, A.TASK_ID, ....
, A.ACTUALS, B.QUANTITY, (A.ACTUALS - B.QUANTITY) ACT_DIFF 
FROM ODF_CA_CUSTOM_OBJECT A
LEFT JOIN
(
SELECT RESOURCE_CODE, PROJECT_CODE, SUM(QUANTITY) QUANTITY FROM
(

SELECT RESOURCE_CODE, PROJECT_CODE, QUANTITY FROM IMP_TRANSACTIONIMPORT
UNION ALL
SELECT RESOURCE_CODE, PROJECT_CODE, QUANTITY FROM PPA_TRANSCONTROL
UNION ALL
SELECT RESOURCE_CODE, PROJECT_CODE, QUANTITY FROM PPA_WIP
) GROUP BY RESOURCE_CODE, PROJECT_CODE
) B ON A.RESOURCE_CODE = B.RESOURCE_CODE AND A.PROJECT_CODE = B.RESOURCE_CODE

In your XML XOG In logic

<core:if test="${act_diff != 0}">
Then do this...............
</core:if>

You can get the expected result in a single query block.

Thanks

Senthil