Query to fetch Dependent Value Set Values for given Independent Value Set in Oracle APPS

  SELECT ffvs_major.flex_value_set_id   major_flex_value_set_id,
         ffvs_major.flex_value_set_name major_flex_value_set_name,
         ffvs_major.description         major_set_description,
         ffvs_major.validation_type     major_validation_type,
         ffv_major.flex_value           major_flex_value,
         ffvt_major.description         major_value_description,
         ffv_major.enabled_flag         major_enabled_flag,
         ffv_major.last_update_date     major_last_update_date,
         ffv_major.last_updated_by      major_last_updated_by,
         ffv_major.attribute1           major_attribute1,
         ffv_major.attribute2           major_attribute2,
         ffv_major.attribute3           major_attribute3
        ,ffvs_minor.flex_value_set_id   minor_flex_value_set_id,
         ffvs_minor.flex_value_set_name minor_flex_value_set_name,
         ffvs_minor.description         minor_set_description,
         ffvs_minor.validation_type     minor_validation_type,
         ffv_major.flex_value             minor_flex_value,
         ffvt_minor.description         minor_value_description,
         ffv_minor.enabled_flag         minor_enabled_flag,
         ffv_minor.last_update_date     minor_last_update_date,
         ffv_minor.last_updated_by         minor_last_updated_by,
         ffv_minor.attribute1             minor_attribute1,
         ffv_minor.attribute2             minor_attribute2,
         ffv_minor.attribute3             minor_attribute3
    FROM fnd_flex_value_sets ffvs_major
        ,fnd_flex_values ffv_major
        ,fnd_flex_values_tl ffvt_major
        ,fnd_flex_value_sets ffvs_minor
        ,fnd_flex_values ffv_minor
        ,fnd_flex_values_tl ffvt_minor
   WHERE ffvs_major.flex_value_set_id = ffv_major.flex_value_set_id
     AND ffv_major.flex_value_id = ffvt_major.flex_value_id
     AND ffvt_major.language = USERENV ('LANG')
     AND UPPER(ffvs_major.flex_value_set_name) = 'XX_INDEPENDENT_VS'--<<Independent Value Set Name>>
     AND ffvs_major.flex_value_set_id = ffvs_minor.parent_flex_value_set_id
     AND ffv_major.flex_value = 'XX_IND_VALUE_SET_VALUE'--<<Independent Value Set Value>>
     AND ffv_major.flex_value = ffv_minor.parent_flex_value_low
     AND ffvs_minor.flex_value_set_id = ffv_minor.flex_value_set_id
     AND ffv_minor.flex_value_id = ffvt_minor.flex_value_id
     AND ffvt_minor.language = USERENV ('LANG')
ORDER BY ffv_major.flex_value ASC

Why COA Structure Separated from the Instance(Oracle EBS GL vs Oracle Fusion GL)

• In R12 EBS, Data Access Sets were limited to ledgers that shared the same COA and Calendar.We intended to expand Data Access Sets to be set at the COA structure level where you have the same shape, but you might have different value sets for different countries.

• Best practice is to have a one to one relationship between the chart of accounts structure and charts of accounts instance.

• Do NOT have multiple COA instances share the same structure.

Oracle Payroll Tables in Oracle Apps

PAY_ELEMENT_TYPES_F:-
This is a date track enabled table. This table stores the details about all the elements in the system. The Primary key is ELEMENT_TYPE_ID and the two date fields. This is usually used to get the name of the element, as ELEMENT_TYPE_ID is used in a lot of places to refer to the element.

PAY_ELEMENT_LINKS_F:-
This is also a date track enabled table. This table stores the details on the links. The primary key is: ELEMENT_LINK_ID and the two date tracked columns, stores the ELEMENT_TYPE_ID as the foreign key.

PAY_INPUT_VALUES_F:-
This is also Date track enabled table. This table stores the Input values for each element.  The primary key is INPUT_VALUE_ID and the two date tracked columns. This table also holds the ELEMENT_TYPE_ID as a foreign key to PAY_ELEMENT_TYPES_F. This can be used to pull in the Element input value name.

PAY_ELEMENT_ENTRIES_F:- This is also Date tracked table. This one stores the details about the element entries. The table stores the Entries with the ASSIGNMENT_ID and the ELEMENT_LINK_ID as foreign key. The Primary key is ELEMENT_ENTRY_ID. This table also links itself to PAY_ELEMENT_TYPES_F with storing ELEMENT_TYPE_ID as a foreign key.

PAY_ELEMENT_ENTRY_VALUES_F:- This is also Date tracked table. It stores the values for each entry. This table has only 6 columns. Out of which,  the Primary key is: ELEMENT_ENTRY_VALUE_ID and the two date tracked columns, it stores the ELEMENT_ENTRY_ID as the foreign key to PAY_ELEMENT_ENTRIES_F and the SCREEN_ENTRY_VALUE stores the actual value of the Input Value. The INPUT_VALUE_ID column links the table to the Input values table (PAY_INPUT_VALUES_F).

PAY_PAYROLL_ACTIONS:- This table logs all the actions taken by the Payroll Engine. Primary key is PAYROLL_ACTION_ID, and it logs in each and every activity. The Table is capable enough to store a lot of information as it has got columns to store all kind of data used in Payroll. It does not populate all the columns / row. However it logs in only the ones those are needed.

PAY_RUN_RESULTS:- This table stores the status related to the elements against the assignment actions. The primary key is RUN_RESULT_ID. ELEMENT_TYPE_ID and ASSIGNMENT_ACTION_ID are the two-other important foreign keys.

PAY_RUN_RESULT_VALUES:- This table takes the RUN_RESULT_ID and the INPUT_VALUE_ID and stores the Value obtained by the Payroll Engine.

Few More tables:-

Table Name	Date Tracked?	Primary Key	Description
PAY_ACCRUAL_PLANS	No	ACCRUAL_PLAN_ID	Stores details on the Accrual Plans. ACCRUAL_PLAN_ELEMENT_TYPE_ID can be used for Element value links.
			Total Compensation -> Basic -> Accrual Plans
PAY_PAYROLLS_F	Yes	PAYROLL_ID	Stores the Payroll related data.
			Payroll-> Description
PAY_ASSIGNMENT_ACTIONS	No	ASSIGNMENT_ACTION_ID	Stores the Payroll actions against Assignments with action status and sequence. Links with PAYROLL_ACTION_ID.
PAY_ASSIGNMENT_LATEST_BALANCES	No		Logs the Balances with DEFINED_BALANCE_ID with each and every assignment action, related to ASSIGNMENT_ACTION_ID.
PAY_ASSIGNMENT_LINK_USAGES_F	Yes	ASSIGNMENT_LINK_USAGE_ID	Logs the assignment and element links with date tracking capabilities. ELEMENT_LINK_ID and ASSIGNMENT_ID are the two foreign keys.
PAY_BALANCE_ATTRIBUTES	No	BALANCE_ATTRIBUTE_ID	Stores the Balance attributes.
			Total Compensation -> Basic -> Balance-> Attributes(B)
PAY_BALANCE_CATEGORIES_F	Yes	BALANCE_CATEGORY_ID	Stores the Balance Categories that can be used while defining Balances.
PAY_BALANCE_DIMENSIONS	No	BALANCE_DIMENSION_ID	Stores the Balance Dimensions.
PAY_BALANCE_FEEDS_F	Yes	BALANCE_FEED_ID	Stores the Balance Feeds.
PAY_BALANCE_TYPES	No	BALANCE_TYPE_ID	Stores the Balances.
PAY_BATCH_LINES	No	BATCH_LINE_ID	Logs the Batch lines at the Batch element entries. Linking the BATCH_ID, ASSIGNMENT_ID and ELEMENT_TYPE_ID. Logs the BATCH_LINE_STATUS.
PAY_COSTS	No	COST_ID	Logs the Cost and the balances as Debits or Credits with respect to each RUN_RESULT_ID and ASSIGNMENT_ACTION_ID.
PAY_COST_ALLOCATIONS_F	Yes	COST_ALLOCATION_ID	Logs the Cost against a COST_ALLOCATION_KEYFLEX_ID, with reference to assignment Ids. This is where costing is logged.
PAY_COST_ALLOCATION_KEYFLEX	No	COST_ALLOCATION_KEYFLEX_ID	The Cost allocation KFF table.
PAY_DEFINED_BALANCES	No	DEFINED_BALANCE_ID	Clubs the Balance with its Dimensions, using BALANCE_TYPE_ID and BALANCE_DIMENSION_ID.
PAY_ELEMENT_CLASSIFICATIONS	No	CLASSIFICATION_ID	Stores the Classifications.
			Total Compensation -> Basic -> Classification
PAY_ELEMENT_SETS	No	ELEMENT_SET_ID	Stores details on Element Sets.
			Payroll-> Element Set
PAY_ENTRY_PROCESS_DETAILS	No		Logs the details of Run results with RUN_RESULT_ID and ELEMENT_ENTRY_ID.
PAY_LINK_INPUT_VALUES_F	Yes	LINK_INPUT_VALUE_ID	Clubs Input Values with the Element links.
PAY_MESSAGE_LINES	No		Logs Payroll Messages
PAY_PAYMENT_TYPES	No	PAYMENT_TYPE_ID	Stores the Payment Methods.
			Payroll-> Payment Methods
PAY_PEOPLE_GROUPS	No	PEOPLE_GROUP_ID	The People Group KFF table.
PAY_PRE_PAYMENTS	No	PRE_PAYMENT_ID	Logs the Prepayment information against ASSIGNMENT_ACTION_ID with the Monetary Values.
PAY_QUICKPAY_INCLUSIONS	No		Logs quickpay actions against ASSIGNMENT_ACTION_ID and ELEMENT_ENTRY_ID
PAY_RETRO_DEFINITIONS	No	RETRO_DEFINITION_ID	Stores Retro Definitions.
PAY_RUN_BALANCES	No	RUN_BALANCE_ID	Stores the Balance Updates against ASSIGNMENT_ID and DEFINED_BALANCE_ID.
PAY_RUN_TYPES_F	Yes	RUN_TYPE_ID	Stores the different Payroll Run Types.