Friday 25 November 2011

Setting up default currency rule for the customer

Steps:-

(1)Create a package named, APPS.OE_DEFAULT_TEST_PKG and a function named, Get_CUST_PROFILE_CURRENCY(p_database_object_name IN VARCHAR2,

p_attribute_code IN VARCHAR2) RETURN VARCHAR2;

(2)description of the function,

FUNCTION Get_CUST_PROFILE_CURRENCY

(p_database_object_name IN VARCHAR2

,p_attribute_code IN VARCHAR2)

RETURN VARCHAR2

IS

l_cust_org_id NUMBER;

l_cust_account_profile_id NUMBER;

l_currency_code VARCHAR2(3) := NULL;

l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;

BEGIN

IF ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id IS NOT NULL

AND ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id <> FND_API.G_MISS_NUM

THEN

l_cust_org_id := ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id;

BEGIN

SELECT CUST_ACCOUNT_PROFILE_ID

INTO l_cust_account_profile_id

from HZ_CUSTOMER_PROFILES

where CUST_ACCOUNT_ID = l_cust_org_id

and site_use_id is null;

SELECT CURRENCY_CODE

INTO l_currency_code

FROM

(SELECT CURRENCY_CODE

FROM HZ_CUST_PROFILE_AMTS

WHERE site_use_id is null and (CUST_ACCOUNT_ID= l_cust_org_id)

and (CUST_ACCOUNT_PROFILE_ID=l_cust_account_profile_id)

ORDER BY currency_code)

WHERE rownum = 1;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN NULL;

END;

END IF;

RETURN l_currency_code;

EXCEPTION

WHEN OTHERS THEN

IF l_debug_level > 0 THEN

oe_debug_pub.add('Error in Get_CUST_PROFILE_CURRENCY') ;

oe_debug_pub.add('Error :'||substr(sqlerrm,1,200)) ;

END IF;

RETURN NULL;

END Get_CUST_PROFILE_CURRENCY;

(3)Go to ,Order Management responsibility > setup > Rules > Defaulting

(4)Make query.It will show the results.

(5)Select,

Application-Order Management

Entity - Order Header

Nextly select the attribute "currency" and press the "Defaulting Rules.." tab.

(6)This will open the Attribute Defaulting Rule window.

(7)Disable the seeded defaulting rule and add the customize rule in default condition section.

Nextly create the default sourcing rule,

(I)Source type - Related Record , Default Source value-Price List(Related Object).Currency(Related Attribute).

(II) Source Type -PL/SQL API , Default Source value -OE_DEFAULT_TEST_PKG(Related Object), Get_CUST_PROFILE_CURRENCY(Related Attribute).

(8)Save the work.



Friday 11 November 2011

Steps to create a dependent value set in Oracle Apps

In order to create a valueset which is dependent on another valueset do the following steps:
1. Create a valueset which is the parent valueset.
2. Create a child valueset whose value is fetched after selecting a value of the parent valueset.

A complete example of the dependent valueset creation is done as follows:

1. Create a parent valueset.
Value Set Name: MJIL_ORGANIZATION_ID
Validation Type: Table
Maximum Size: 5


Edit Information:-
Table Application: Human Resources
Table Name: HR_ALL_ORGANIZATION_UNITS_TL
Table Columns Value: ORGANIZATION_ID
Type: Char
Size: 5
Table Columns Meaning: Name
Type: VarChar2
Size: 240




2. Create another valueset which is dependent on the previous valueset.
Value Set Name: MJIL_CUST_INVOICE_DEP_ORG_ID
Validation Type: Table
Maximum Size: 20





Edit Information:-
Table Application: Receivables
Table Name: RA_CUSTOMER_TRX_ALL
Table Columns Value: TRX_NUMBER
Type: Char
Size: 20

Where/Order By
WHERE INTERFACE_HEADER_ATTRIBUTE10=:$FLEX$.MJIL_ORGANIZATION_ID
ORDER BY TRX_NUMBER DESC