Hi Scott
If Aras ever reintroduce their community awards, you already set the bar quite high for the "best description images for complex forum questions" category [emoticon:44a8a53ad3364ea78a16c5a3229f75bb]
Is your final goal a TreeGridView that shows the BOM based on the Oracle data? (So you don´t really create new items, but do something like a federated view?)
Or do you want to rebuilt the BOM / add new BOM items? For this use case, a Query Definition sounds like the hardest way to do the task.
Is this a task you do once for data migration, or shall the data be updated on a regular basis?
Hello Angela,
Thanks for the Shout out!
This is a data Migration/Transformation/Loading excercise. A tree Grid View will never be applied.
The Oracle mBOM Relational Data (structure) will be use to construct PLM eBOMS and PLM mBOMs, then it will be chucked.
It is being done for 1 division now and will be done for other divisions after the completion of this initial effort.
I wanted to use a Query Definition based:
1) Performance
2) Easily adaptable to other Divisions.
3) Trying to aviod the use of SQL.
4) Easier to pass off to the client for re-use.
The client has implemented mBOMs before eBOMs which will makes this very challenging assignment.
Here is an updated Data Model.
Fortunately, the Aras award for “Customer who asked the Aras support team the strangest and most bizarre questions about trivial details in Innovator” will forever remain in my hands.[emoticon:0e469978eb8947d18b9c41123db3725a]
Unfortunately I am not the best person to answer questions regarding Query Definition. Maybe somebody else knows more about this one?
Question that I have right now:
- How can we "add" new items based on "get" Query Definition? Do you have done something like this before?
- Do you made some attempts to build the Query Definition already? You mentioned that you want to build a query with DISTINCT. I wonder if this is possible with the regular Query Definition. You can build more complex Query Definitions if you don´t use the visual editor but edit the items that build the query directly (unhide the relationship in the Query Definition ItemType). But this will lead to the same problem you will have with SQL -> it´s not obvious for other users anymore.
It´s not impossible to "pass" SQL components to other users. You can use the SQL ItemType and use a Method that does a sample call. In my case I have a whole bunch of Methods and SQL stuff with the name “Admin” or similar that is just intended to document certain special tasks. I even have a complete folder on the main server with "SQL scripts" for all kind of maintenance stuff, like repairs, cleanup, data validation, procedure tests, etc. .
But I understand the advantage of using the Query Definition, so I hope there is a solution!
Angela,
I am using the SQL Item to get the Unique Assembly item_number out of a 1.7 million row table.
And in fact .... do you remember the question I had about returning the data set to my Method?
Well, I figured what I was doing wrong. At the end of the SQL procedure I was not doing:
1) A "Select * From @AssyIds
Create Procedure stg_GetUniqueAssyIdsWithOrg(@creByDiv nVarchar(10))
as
Begin
Declare @AssyIds Table (rh_ora_mfg_org nVarChar(3), rh_ora_mfg_bom_assy_item_id nVarChar(20))
Insert @AssyIds
Select Distinct omm.rh_Ora_Mfg_Org, omm.rh_Ora_Mfg_Bom_Assy_Item_Id
From [innovator].rh_Oracle_MfgItem_MfgItem as omm
Where omm.rh_Plm_Cre_By_Div = 'AC'
Order By omm.rh_Ora_Mfg_Org Desc
Select * From @AssyIds
End
Also
2) I Created a new Item to contain the result data. The item to get the procedure and run the procedure
[getUniqueAssyIdsByOr] is different than the one to hold the data [uniqueAssyId]
When I used the same Item for both the data was not passed.
Item getUniqueAssyIdsByOrg = m_dal.NewItem("SQL", "SQL PROCESS");
getUniqueAssyIdsByOrg.setProperty("name", "stg_GetUniqueAssyIdsWithOrg");
getUniqueAssyIdsByOrg.setProperty("PROCESS", "CALL");
getUniqueAssyIdsByOrg.setProperty("ARG1", airCreatedByDiv);
Item uniqueAssyIds = getUniqueAssyIdsByOrg.apply();
Now ... I don't know why this worked .... But it did.
I do hope that Aras adds this capability to the Query Definition.
Also, have you seen any documentation that explains how to use the relationships instead of the GUI.
I have done it when I replicated the BOM Structure (PE_MultiLevelReport) but all I did was replicate what Aras provided. I have no idea what I was actually doing.
