I worked on a project once where we had different lines of products maintained by different product managers across several sales territories and different currencies.
When an order was placed the price list was not specified on the order header but instead it was selected automatically on lines based on the sales channel, territory and the line of products the item belonged to.
This worked fine with one exception; the currency on the order header defaulted from the order type instead of the customer site. Prices were tailored to a specific market and specific currency and we could not simply create a multicurrency price list. Instead we have created a mandatory flexfield on the customer site and used the PL/SQL defaulting rules to dynamically default it on the order.
In the defaulting order screen you can select PL/SQL as the source type and specify the Package and Function name
The above shows the seeded values where the currency is taken from the Set of Books.
Note that there is no way to pass a specific order number or ID, the values will be taken from the current ONT_HEADER_DEF_HDLR.g_record which is automatically populated by the application for any order.
Here is the code I used to default currency from Attribute11 of site use flexfield:
FUNCTION Get_DEFAULT_CURRENCY
( p_database_object_name IN VARCHAR2
,p_attribute_code IN VARCHAR2)
RETURN VARCHAR2 IS
l_invoice_to_org_id NUMBER := APPS.ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id;
l_currency VARCHAR2(4);
l_debug_level CONSTANT NUMBER := apps.oe_debug_pub.g_debug_level;
BEGIN
IF l_debug_level > 0 THEN
oe_debug_pub.add('ENTER Get_sales_channel');
oe_debug_pub.add('Sold To Org ID :'||l_invoice_to_org_id );
END IF;
IF l_invoice_to_org_id is not null
AND l_invoice_to_org_id <> apps.fnd_api.g_miss_num
THEN
BEGIN
--Here is the actual code
select attribute11
into l_currency
from HZ_cust_SITE_USES_ALL situs
where situs.site_use_id=l_invoice_to_org_id;
RETURN l_currency;
EXCEPTION
-- Return null if there is noinvoice to site
WHEN NO_DATA_FOUND THEN
RETURN NULL';
END;
-- Return null if there is no customer
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF l_debug_level > 0 THEN
apps.oe_debug_pub.add('Error in Get_Currency') ;
apps.oe_debug_pub.add('Error :'||substr(sqlerrm,1,200)) ;
END IF;
RETURN NULL;
END Get_DEFAULT_CURRENCY;
Dear Piotr Belter,
ReplyDeleteThanks for a very useful post. My function below seems to not work. Could you please kindly guide. Thanks.
FUNCTION Get_installed_location
( p_database_object_name IN VARCHAR2
,p_attribute_code IN VARCHAR2)
RETURN VARCHAR2 IS
l_sold_to_org_id NUMBER := APPS.ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id;
l_sold_cust_account_id NUMBER;
l_end_customer_id NUMBER := APPS.ONT_HEADER_DEF_HDLR.g_record.end_customer_id;
l_installed_location VARCHAR2(25);
l_currency VARCHAR2(4);
l_debug_level CONSTANT NUMBER := apps.oe_debug_pub.g_debug_level;
BEGIN
IF l_debug_level > 0 THEN
oe_debug_pub.add('ENTER Get_sales_channel');
oe_debug_pub.add('Sold To Org ID :'||l_sold_to_org_id );
END IF;
BEGIN
SELECT cust_Account_id
into l_sold_cust_account_id
FROM HZ_CUST_ACCOUNTS CA
WHERE CA.CUST_ACCOUNT_ID = l_sold_to_org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN l_sold_cust_account_id := NULL;
END;
IF l_sold_cust_account_id != l_end_customer_id
THEN
BEGIN
SELECT SUBSTR(P.PARTY_NAME,1,25)
into l_installed_location
FROM HZ_CUST_ACCOUNTS CA
,HZ_PARTIES P
WHERE CA.CUST_ACCOUNT_ID = l_sold_to_org_id
and CA.PARTY_ID = P.PARTY_ID;
RETURN l_installed_location;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF l_debug_level > 0 THEN
apps.oe_debug_pub.add('Error in Get_Currency') ;
apps.oe_debug_pub.add('Error :'||substr(sqlerrm,1,200)) ;
END IF;
RETURN NULL;
END Get_installed_location;