Shan,

I’ve tried twice to post it here, but it does not seem to be going through.  I’m not sure if the content is being restricted or the length of the post.
If you email me directly at steven.LaLonde@powellind.com, I will send it to you.

Steve

Dear Shan,

I have recently completed a SQL query to create a date-specific BOM report, if you think that would be helpful.  This is for the BOM only. While I would love to also get attributes at said specified date, it gets pretty complicated to do so.  First it depends on whether the attributes in question are change controlled or not.  If not, you must rely solely on the item_history table as opposed to the redline_attribute table.  In my case, the attributes of interest are indeed change controlled, but if the attribute did not have a change on the ECO just prior to the specified date, then what? Do you take the current value?  That may not be what the value was at the time, because it may have been revised since then.  So, it would become an exercise in going back, potentially through multiple ECOs, to figure out what the value was at the time.  Long story short… we simply opted to keep ours a BOM report, and keep attributes out of it.

Regards,
Steve LaLonde

Raju,

I’ve expanded on Arif’s answer just a little.  I would add the DISTINCT keyword (as Matt did).  This will prevent duplicate records, which may happen especially if your company makes use of sites. If your BOMs can vary between one site and the next, then you may even want to specify which site in the WHERE clause. 
If you also want your BOMs to be ordered in a way that is similar to how Agile might show an exploded BOM, then you may want to add a “path” that properly connects your find numbers.  Note that Matt used an ORDER BY statement which orders the exploded BOM numerically.  That’s fine as long as there is no duplication of numbers in your sub-assemblies. If there are, all of the 1’s will come first, followed by the 2’s, etc.  At our company we use find numbers up to 4 digits long.  In order to create a proper sort path, I must pad the find numbers with zeros.  See my query below.  Hopefully it will make sense.

Regards,
Steve

SELECT  distinct :assembly AS PARENT_ITEM,
 a.item_number,
 LEVEL, A.FIND_NUMBER
 ,SYS_CONNECT_BY_PATH(LPAD(A.FIND_NUMBER,4,’0′), ‘|’) ITEM_PATH
      FROM   AGILE.BOM A
START WITH   (A.ITEM = (SELECT   DISTINCT i.id
                          FROM   agile.item i, agile.bom b
                         WHERE   i.id = b.item AND i.item_number = :assembly)
              AND NVL (A.change_out, 0) = 0
              AND (SUBSTR (A.flags, 5, 1) = 1)
              AND A.id NOT IN (SELECT   C.prior_bom
                                 FROM   AGILE.bom C
                                WHERE   C.item = A.item))
CONNECT BY       PRIOR A.COMPONENT = A.ITEM
             AND NVL (A.change_out, 0) = 0
             AND (SUBSTR (A.flags, 5, 1) = 1)
             AND A.id NOT IN (SELECT   B.prior_bom
                                FROM   AGILE.bom B
                               WHERE   B.item = A.item)
ORDER BY ITEM_PATH;

Laura,
Here is a SQL query example that may be use. In my case I was only looking for history of change-controlled changes on a subclass we named ‘Raw Material’  The Action ID in our case (‘Modify Change Controlled’) was 615.  I probably looked that value up somewhere, but don’t recall where at the moment.  This query takes the DETAILS string apart to get some useful info.  Obviously, you’ll have to tweak it to suit your needs.

Steve

Select I.Item_Number
, I.Description
, Substr(H.Details,Instr(H.Details,’:<‘)+2,Instr(H.Details,’>WAS’)-1-Instr(H.Details,’:<‘)-1) ATTRIB
, H.Details, H.Revnumber, H.Timestamp, U.Loginid
, Substr(H.Details,Instr(H.Details,’WAS<‘)+4,Instr(H.Details,’>IS’)-1-Instr(H.Details,’WAS<‘)-3) Value_Was
, Substr(H.Details,Instr(H.Details,’IS<‘)+3,Instr(H.Details,’>’||Chr(1)||’H’)-1-Instr(H.Details,’IS<‘)-2) Value_Is
From Agile.Item_P2p3 I
inner join agile.nodetable N on (I.subclass = N.id)
Inner Join Agile.Item_History H On I.Id = H.Item
Inner Join Agile.Agileuser U On H.User_Id = U.Id
Where 1=1
And Action = 615  –ID FOR ‘Modify Change Controlled’
And H.Details Like ‘%Page Three.%’
and n.description = ‘Raw Material’
  order by 1;

Hello Anand. Yes, you may use an rtf template to generate an xls report.  the version does not seem to support xlsx, but xls only.
In fact, as long as you pass the correct extension name to the function, you should be able to generate the following file types from a single rtf template: PDF, XLS, HTML, and RTF.

As an interesting side note, the xls file that is generated is actually an html file. You can rename it with an html extension and it will open in a browser rather than Excel.

Steve

It may be worth noting that most object types (ITEMS, CHANGES, etc.) also have database VIEWS that already put the base object together with Page 2 and/or Page 3.  For example, there are views called ITEM_P2, ITEM_P2P3, CHANGE_P2, and CHANGE_P2P3.  I generally prefer those over querying directly from either the PAGE_TWO or PAGE_THREE tables.  Hope that  helps as well.

Steve

Manu,  While I do not have a spreadsheet example handy at the moment, some of our users have had better luck using the Parent-child template, rather than the level template. If I recall, this would mean that you have a PARENT column and a CHILD column.  The parent item part number shows up in as many rows as there are level 1 child items.  If the child items have components of their own, then they get their own rows, but they are now the parent item, etc.  In this type of import, there is no need to specify the level.  Naturally, you still need columns to specify other BOM specific attributes, like find number, quantity, etc.  If the items already exists, then the spreadsheet should be fairly simple, but if you intend to have this import create items that do not currently exist, then obviously, you have to add much more information. Hope this helps.

Steve