What is the SQL or formula to multiply two fields together in the same view / table as a custom field ?
IFS Apps 10 (EE)
Purchase Receipts History (tbwPurchaseReceiptHistory) Qty Arrived * Price Per Purch Unit/Base
Solved
How do I multiply two fields into a custom field ?
+6Best answer by Tomas Ruderfelt
Here is an example, not using the view in the previous post but instead a join:
Arguments:
v.order_no,v.line_no,v.release_no,v.receipt_no
Select statement:
SELECT r.qty_arrived * (buy_unit_price/price_conv_factor)
FROM purchase_order_line l ,purchase_receipt_info r
WHERE r.order_no = :order_no
AND r.line_no = :line_no
AND r.release_no = :release_no
AND r.receipt_no = :receipt_no
AND l.order_no = r.order_no
AND l.line_no = r.line_no
AND l.release_no = r.release_no
+17I’m subscribing to this to see how many different answers there will be! :-)
In Apps9 I’d do something like this - assuming BUY_UNIT_PRICE and QTY_ARRIVED are fields in the view already
((buy_unit_price)*(qty_arrived))
Linda
Linda
1 person likes this
+6PRODQ wrote:
I’m subscribing to this to see how many different answers there will be! :-)
In Apps9 I’d do something like this - assuming BUY_UNIT_PRICE and QTY_ARRIVED are fields in the view already
((buy_unit_price)*(qty_arrived))
Linda
Yes Linda, the fields already exist in the view.
+6I’ve tried a basic calculation as stated above but that doesn’t work.
Thought anyone ?
Hi
Are you able to share your full SQL query?
If in APPS10 - I think the view would be
PURCHASE_RECEIPT_STAT_UIV
usually when you have created a new custom field the view would be slightly different and the ending would be cfv.
So the view would then become PURCHASE_RECEIPT_STAT_UIV_CFV - you would want to query from this view to access the new field.
This would normally allow you to access the new fields. In my example below the new custom field (TEST) is prefixed with CF$_
If you are able to share your SQL I could take a look.
Kind Regards,
Dan
1 person likes this
Here is an example, not using the view in the previous post but instead a join:
Arguments:
v.order_no,v.line_no,v.release_no,v.receipt_no
Select statement:
SELECT r.qty_arrived * (buy_unit_price/price_conv_factor)
FROM purchase_order_line l ,purchase_receipt_info r
WHERE r.order_no = :order_no
AND r.line_no = :line_no
AND r.release_no = :release_no
AND r.receipt_no = :receipt_no
AND l.order_no = r.order_no
AND l.line_no = r.line_no
AND l.release_no = r.release_no
1 person likes this
+6Thank you for all your suggestions… the solution that works for us is the one from Tomas.
Your help is very much appreciated. 😀
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.