Enterprise Data Manager Guide

6.5 HotFix 1
- 6.5.1
- 6.5
- 6.4.4
- 6.4.3 HotFix 1
- 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 the respective application under the intended Application Version. For example, add “BOM Bills of Material” as an Application to Oracle Version 11.5.10. The node you create is called the Application Module.

Add an entity to the 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. The following table shows examples of parameters:

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 Values

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: