Facing Issue in calculating percentage for an Analysis in OTBI

Palak Karma

Summary:

We are trying to create a custom analysis to calculate the month wise percentage of delivered amount being early received including tolerances. We have calculated delivered amount and early received amount as a new calculate measure in the analysis but when we are trying to calculate the percentage of Delivered amount being early received using these two calculated measures, it is throwing an error. Could anyone help us with this issue?

Content (required):

Below are the formulae we used to achieve the same:

Subject Area: Receiving-Purchase Receipts Real Time

Delivered Amount: SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price")

Early Receipt Amount: CASE WHEN CAST("- Purchase Receipt Line Details"."Creation Date Line" AS DATE) < CAST("Purchase Order Schedule Detail"."Need By Date" AS DATE)-"Additional Schedule Information"."Early Receipt Tolerance Days" THEN ("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price") ELSE SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price")-SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price") END

(Aggregation Rule: Sum, Else Condition in Early receipt amount is given to get '0' as value for the orders that does not have early receipt)

Version (include the version you are using, if applicable):

Oracle Fusion Cloud Applications 22D (11.13.22.10.0)

Code Snippet (add any code snippets that support your topic, if applicable):

Jahnavi-Oracle

Hi Palak,

Can you paste the error here as well to understand the issue better.

Jahnavi-Oracle

Also a question on the formula, Isn't the SUM aggregation missing in the then clause for the Early Receipt Amount?

CASE WHEN CAST("- Purchase Receipt Line Details"."Creation Date Line" AS DATE) < CAST("Purchase Order Schedule Detail"."Need By Date" AS DATE)-"Additional Schedule Information"."Early Receipt Tolerance Days"

THEN SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price")

ELSE SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price")

-SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price") END

Palak Karma

@Jahnavi-Oracle

This is formula we used to calculate percentage:

(((CASE WHEN CAST("- Purchase Receipt Line Details"."Creation Date Line" AS DATE) < CAST("Purchase Order Schedule Detail"."Need By Date" AS DATE)-"Additional Schedule Information"."Early Receipt Tolerance Days" THEN ("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price") ELSE SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price")-SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price") END) by "Purchase Order Header Details"."Order")/(SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price") by "Purchase Order Header Details"."Order"))*100

Basically it is: (Early Received Amount/ Delivered Amount)*100

and the error shown is mentioned below:

Formula syntax is invalid.

[nQSError: 10058] A general error has occurred. (HY000)

[nQSError: 43113] Message returned from OBIS. (HY000)

[nQSError: 27002] Near : Syntax error (HY000)

[nQSError: 26012] . (HY000)

SQL Issued: SELECT (((CASE WHEN CAST("- Purchase Receipt Line Details"."Creation Date Line" AS DATE) < CAST("Purchase Order Schedule Detail"."Need By Date" AS DATE)-"Additional Schedule Information"."Early Receipt Tolerance Days" THEN ("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price") ELSE SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price")-SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price") END) by "Purchase Order Header Details"."Order")/(SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price") by "Purchase Order Header Details"."Order"))*100 FROM "Receiving - Purchase Receipts Real Time"

Palak Karma

@Jahnavi-Oracle

Using SUM Function in the Formula itself is giving incorrect values. Therefore, we put aggregation rule as sum for this Early received amount calculated measure so that the analysis can calculate SUM on the rows of "Early received amount" Column

Bhaskar Konar

Hi @Palak Karma ,

It seems you're using BY clause outside of Aggregation Function.

Your code has been changed a bit and now has no syntax error. Can you please check this one?

( SUM(CASE  WHEN  CAST("- Purchase Receipt Line Details"."Creation Date Line" AS DATE) <  CAST("Purchase Order Schedule Detail"."Need By Date" AS DATE) - "Additional Schedule Information"."Early Receipt Tolerance Days"  THEN ("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price")  ELSE  SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price") - SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price")  END by "Purchase Order Header Details"."Order") / (SUM("- Purchase Receipt Line Details"."Delivered Quantity"*"Purchase Order Lines Details"."Price" by "Purchase Order Header Details"."Order")) ) * 100

Hope this help.

Cheers,

Jahnavi-Oracle

Hi Palak, The error is related to the syntax of the formula. In the comparison of the case when statement the date is being subtracted from days , that doesn't seem to be correct, Can you check it once again.

CAST("- Purchase Receipt Line Details"."Creation Date Line" AS DATE) < CAST("Purchase Order Schedule Detail"."Need By Date" AS DATE)-"Additional Schedule Information"."Early Receipt Tolerance Days"

Also the above comment from Bhaskar also needs to be taken care of to avoid the syntax error.

Palak Karma

@Jahnavi-Oracle @Bhaskar Koduri

Thank you for the help. I will try this and let you know the results.