Enterprise Data Manager Guide

6.4.3 HotFix 1
- 6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.4
- 6.4.3

Back Next

Customizing Data Archive Metadata Example

A practical example of Data Archive Metadata definition is as follows:

Import the metadata from the source database. Create a node for respective Application under the intended Application Version. For example, add “BOM Bills of Material” as Application to Oracle Version 11.5.10.

Add an Entity to created Application module. For example: “BOM - Standard Cost Update (11.5.10)” to the Application module “BOM Bills of Material.”

Define one or more parameters for the entity defined above. Examples of parameters are indicated in the following table:

Order	Column	Name	Data type	Required	List of Values	Security group
1	p_cost_org_id	Cost Organization	Number		INV Organization
2	p_update_date	Update Date	Time	true	INV Accounting Period (PF-8)	Organization Unit

The final SQL query for List of Values “INV Organization” and “INV Accounting Period (PF-8)” reads as follows:

List of Value Name	SQL Test Query
INV Organization	SELECT A.organization_name “Organization”, A.organization_code “Code”, TO_CHAR(A.organization_id) FROM ORG_ORGANIZATION_DEFINITIONS A WHERE A.inventory_enabled_flag = 'Y' ORDER BY A.organization_name, A.organization_name
INV Accounting Period (PF-8)	SELECT A.period_name “Period Name “, T.organization_name “Organization Name”, T.organization_code “Organization Code”, A.period_name “Period Name “, T.organization_code “Organization Code”, T.organization_name “Organization Name”, TO_CHAR(A.schedule_close_date, 'DD-MON-YYYY') FROM ORG_ACCT_PERIODS A, ORG_ORGANIZATION_DEFINITIONS T WHERE A.open_flag = 'N' AND A.organization_id = T.organization_id AND T.organization_id = NVL(:params.param1,T.organization_id)

List of Value Name

SQL Test Query

INV Organization

SELECT A.organization_name “Organization”, A.organization_code “Code”, TO_CHAR(A.organization_id)

FROM ORG_ORGANIZATION_DEFINITIONS A

WHERE A.inventory_enabled_flag = 'Y' ORDER BY A.organization_name, A.organization_name

INV Accounting Period (PF-8)

SELECT A.period_name “Period Name “, T.organization_name “Organization Name”, T.organization_code “Organization Code”, A.period_name “Period Name “, T.organization_code “Organization Code”, T.organization_name “Organization Name”, TO_CHAR(A.schedule_close_date, 'DD-MON-YYYY')

FROM ORG_ACCT_PERIODS A, ORG_ORGANIZATION_DEFINITIONS T

WHERE A.open_flag = 'N' AND A.organization_id = T.organization_id AND T.organization_id = NVL(:params.param1,T.organization_id)

It is assumed that these List of Values have been created and available under the “List of Value tab” of the Explorer Pane.

Insert default Steps for Archive Definition under the Entity “BOM - Standard Cost Update (11.5.10).”

Add required Interim Tables. For example, for the Entity “BOM - Standard Cost Update (11.5.10)”, add an Interim Table with the following FROM and WHERE clauses:

Clause Type	Statement
FROM	bom.Cst_Cost_Updates C, inv.Mtl_Parameters Mp
WHERE	C.Organization_Id =decode(:p_cost_org_id,null,c.organization_id,:p_cost_org_id) And C.Organization_Id = Mp.Organization_Id And trunc(Update_Date) <= :P_Update_Date and Mp.Primary_Cost_Method=1

Add Tables (to the “Tables” tab of this Interim Table) that will participate in the archive job execution, and define their respective INSERT, DELETE and UPDATE statements. For this example, specify the following information:

Order	Name	Insert Statement	Delete Statement
1	CST_STD_COST_ADJ_VALUES	a.cost_update_id in (select cost_update_id from XA04156_BOM1 x where x.master_org_flag = 'Y' and x.purgeable_flag = 'Y')	a.cost_update_id in (select cost_update_id from XA04156_BOM1 x where x.master_org_flag = 'Y' and x.purgeable_flag = 'Y')
2	CST_STANDARD_COSTS	A.cost_update_id IN (Select cost_update_id From XA04156_BOM1 x where x.purgeable_flag = 'Y')	A.cost_update_id IN (Select cost_update_id From XA04156_BOM1 x where x.purgeable_flag = 'Y')
3	CST_ELEMENTAL_COSTS	A.cost_update_id IN (Select cost_update_id From XA04156_BOM1 x where x.purgeable_flag = 'Y')	A.cost_update_id IN (Select cost_update_id From XA04156_BOM1 x where x.purgeable_flag = 'Y')
4	CST_COST_UPDATES	A.cost_update_id IN (Select cost_update_id From XA04156_BOM1 x where x.purgeable_flag = 'Y')	A.cost_update_id IN (Select cost_update_id From XA04156_BOM1 x where x.purgeable_flag = 'Y')

Add the Default Columns. For example, for the Interim table created above, define the following Default Columns:

Order	Name	Type	Length	PK	Select Clause
1	cost_update_id	Number		true	C.Cost_Update_Id
2	cost_update_date	Date		false	C.Update_Date
3	org_id	Number		false	mp.Organization_Id
4	master_org_flag	Char	1	false	decode(Mp.Organization_Id, mp.cost_organization_id,'Y','N')
5	purgeable_flag	Char	1	false	'Y'
6	stats_date	Date		false

Define Business Rules. For example, on the Interim Table generated for “BOM - Standard Cost Update,” define the Business Rule “HAS_NONPURGEABLE_STD_COSTS” with the following information:

Description. Cost Update has standard costs that are not being purged in this cycle.

Business rule order. 1

Condition. A.cost_update_id in (select cost_update_id From bom.cst_standard_costs b where not exists (select 1 from XA04156_BOM1 x where x.purgeable_flag = 'Y' and b.cost_update_id = x.cost_update_id and x.org_id=b.organization_id))

Generate default Indexes.

Specify reporting Statements. For example:

Order	Statement Label	Literals	Group
1	COST_UPDT_DT	A	Primary

A Parameter i.e. “cost_update_date” is specified here for the Statement which relies on “INTERIM_VALUE.”

Rename Saved Search

Table of Contents

Enterprise Data Manager Guide

Enterprise Data Manager Guide

Customizing Data Archive Metadata Example

Customizing Data Archive Metadata Example