Skip to main content

Hi,

I tried to create a custom field, in PLSQL developer value is getting correctly. But when I deploy the same in custom field, value is coming incorrectly. 

What could be the possible reason, Please help.

Code- in back end

SELECT c.order_no,c.BUY_QTY_DUE SalesQty,c.QTY_ASSIGNED ReservedQty, 
CASE WHEN c.QTY_ASSIGNED/*Reserved Qty*/ >= c.BUY_QTY_DUE/*Sales Qty*/ 
THEN 'Fully Reserved'
WHEN c.QTY_ASSIGNED IN (NULL,0) THEN 'Released'
WHEN c.QTY_ASSIGNED < c.BUY_QTY_DUE THEN 'partially Reserved'
  END Status  
FROM CUSTOMER_ORDER_LINE c,CUSTOMER_ORDER d
WHERE c.order_no= '&order_no' 
AND c.order_no=d.order_no

Correct result for status in plsql developer

Code- in custom field- (Read only field)

SELECT CASE WHEN c.QTY_ASSIGNED >= c.BUY_QTY_DUE
THEN 'Fully Reserved'
WHEN c.QTY_ASSIGNED IN (NULL,0) THEN 'Released'
WHEN c.QTY_ASSIGNED < c.BUY_QTY_DUE THEN 'partially Reserved'
  END Status  
FROM CUSTOMER_ORDER_LINE c,CUSTOMER_ORDER d
WHERE c.order_no= :order_no
AND c.order_no=d.order_no

Result

here the result is incorrect(red highlighted box)- Detailed status is our custom field
and that value should be ‘Released’

Please help, how to sort this out, 

I have tried giving single quote to ‘0’ , used equal to, like operators etc, still I get this incorrect result. 

 

Thanks in advance

Hi @proharikg 

Lets forget the fact that the SQL query is giving the expected output for a moment. If you have a look at the method generated for this custom field, it only accepts one parameter, that is the order no.

 

FUNCTION Get_Cf$_Detailed_Status (
   order_no_ IN VARCHAR2 ) RETURN VARCHAR2
IS
   temp_ VARCHAR2(32000);
   CURSOR get_attr IS
   SELECT CASE
         WHEN c.QTY_ASSIGNED >= c.BUY_QTY_DUE THEN
          'Fully Reserved'
         WHEN c.QTY_ASSIGNED IN (NULL, 0) THEN
          'Released'
         WHEN c.QTY_ASSIGNED < c.BUY_QTY_DUE THEN
          'partially Reserved'
       END Status
  FROM CUSTOMER_ORDER_LINE c, CUSTOMER_ORDER d
 WHERE c.order_no = order_no_
   AND c.order_no = d.order_no;
BEGIN
   OPEN get_attr;
   FETCH get_attr INTO temp_;
   CLOSE get_attr;
   RETURN temp_;
END Get_Cf$_Detailed_Status;

 

So if you pass only the order no and expect different results Customer order line vice, its not correct right. In your PLSQL output screen capture I could see that the order no is the same for all the lines. So if the above method gets executed multiple times for same order no, it will provide the same results.

 

To get this issue sorted out, or to expect different results customer order line vice, I believe you need to pass the customer order line keys(ORDER_NO, LINE_NO, REL_NO, LINE_ITEM_NO) into your custom field. Please try this out.

Hope this information will help.

 

/Shardha

Hi @proharikg 

Lets forget the fact that the SQL query is giving the expected output for a moment. If you have a look at the method generated for this custom field, it only accepts one parameter, that is the order no.

 

FUNCTION Get_Cf$_Detailed_Status (
   order_no_ IN VARCHAR2 ) RETURN VARCHAR2
IS
   temp_ VARCHAR2(32000);
   CURSOR get_attr IS
   SELECT CASE
         WHEN c.QTY_ASSIGNED >= c.BUY_QTY_DUE THEN
          'Fully Reserved'
         WHEN c.QTY_ASSIGNED IN (NULL, 0) THEN
          'Released'
         WHEN c.QTY_ASSIGNED < c.BUY_QTY_DUE THEN
          'partially Reserved'
       END Status
  FROM CUSTOMER_ORDER_LINE c, CUSTOMER_ORDER d
 WHERE c.order_no = order_no_
   AND c.order_no = d.order_no;
BEGIN
   OPEN get_attr;
   FETCH get_attr INTO temp_;
   CLOSE get_attr;
   RETURN temp_;
END Get_Cf$_Detailed_Status;

 

So if you pass only the order no and expect different results Customer order line vice, its not correct right. In your PLSQL output screen capture I could see that the order no is the same for all the lines. So if the above method gets executed multiple times for same order no, it will provide the same results.

 

To get this issue sorted out, or to expect different results customer order line vice, I believe you need to pass the customer order line keys(ORDER_NO, LINE_NO, REL_NO, LINE_ITEM_NO) into your custom field. Please try this out.

Hope this information will help.

 

/Shardha

Hi Shardha,

 

Thank you so much..

I tried this- ORDER_NO, LINE_NO, REL_NO, LINE_ITEM_NO

and it worked.

 

regards,

Hari

 

Terms & ConditionsCookie settings