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
Best answer by Shardha Weeratunga
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.
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.
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
