Extraction of Manufacturers Tab of Item Screen of Web Client
Hello All,
I am trying to extract information displayed on Tab of manufacturers in Item Screen of Web Client. I have extracted most of the data. But when I try to get data according to the REV drop down. In other words, when I try to get data of manufacturers according to the revision of Items available in REV drop down. I get duplication in data. So far I was able to write the following query.
SELECT *
FROM (SELECT DISTINCT i.item_number,
N1.DESCRIPTION CLASS,
N2.DESCRIPTION “ITEM_TYPE”,
r.rev_number || ‘ ‘ || crev.change_number AS rev,
m.name “mfrname”,
mp.part_number AS “mfrpartnumber”,
le.description AS “MfrPartLifecyclePhase”,
le1.entryvalue AS “PreferredStatus”,
le3.ENTRYVALUE AS “SummaryCompliance”,
le4.entryvalue AS “mfrPartList03”,
p2.date01 AS “mfrPartDate01”,
mb.change_in,
mb.change_out
–,mp.compliancy
FROM MANUFACTURERS m
INNER JOIN MANU_PARTS mp
ON m.id = mp.MANU_ID
INNER JOIN manu_by mb
ON mp.id = mb.MANU_PART
INNER JOIN page_two p2
ON m.id = p2.id
LEFT OUTER JOIN listentry le1
ON le1.entryid = mb.prefer_status
AND le1.parentid = 2249
AND le1.langid = 0
LEFT OUTER JOIN nodetable le
ON le.id = mp.STATUS
INNER JOIN item i
ON i.id = mb.AGILE_PART
LEFT OUTER JOIN listentry le3
ON le3.entryid = mp.compliancy
AND le3.parentid = 2000001019
INNER JOIN (SELECT p2.list03, mp2.id
FROM manu_parts mp2
INNER JOIN
page_two p2
ON mp2.id = p2.id) t
ON t.id = mp.id
LEFT OUTER JOIN listentry le4
ON le4.entryid = t.LIST03
INNER JOIN nodetable n1
ON i.class = n1.id
INNER JOIN nodetable n2
ON i.subclass = n2.id
INNER JOIN rev r
ON –i.id
i.id = r.item
–and r.released =1
–and r.latest_flag = 1
INNER JOIN change crev
ON r.change = crev.id
–and mb.agile_part = crev.item
INNER JOIN CHANGE c2
ON mb.change_in = c2.id
INNER JOIN CHANGE COUT
ON mb.change_out = cout.id
WHERE 1 = 1
AND i.class = 10000
AND i.subclass = 40267
AND le1.entryvalue IS NOT NULL
AND mb.active = 1
AND (MB.change_out = 0 AND mb.change_in <> 0))
WHERE 1 = 1 AND item_number = ‘300-499’
ORDER BY rev;
Can anybody please tell me how can I remove this duplication. I am totally lost on this one.
It *might* be because of the following : LEFT OUTER JOIN listentry le4 ON le4.entryid = t.LIST03. Normally, you should always specify the list ID (i.e. AND le1.parentid = 2249) when getting something from LISTENTRY. And also specifying that ACTIVE in (0,1) and LANGID=0 is a good thing.
And specifying that “(MB.change_out = 0 AND mb.change_in <> 0)” in your query will only get you data for the latest revision. So trying to get rev data for anything other than the latest rev, you should remove that from the query (and things WILL get much more complicated trying to use CHANGE_IN and CHANGE_OUT, see below).
I have found that building a table is MUCH easier than trying to put everything together in a single query. I can categorically tell you that that is not how Agile works on the inside, complex queries would slow things down *immensely*. And the application server keeps a good amount of the configuration in memory, for faster conversion of IDs to text values after data has been retrieved from the database. Using PL*SQL, you can set the values for each attribute/chunk of data, in order, with simple/efficient queries. And this also allows not doing anything if the attribute values are null, instead of having a lot of outer joins.
You have a good start there, but need to be able to add things in easy pieces instead of making what you have more complex (and possibly introducing errors). Create a table to hold the data, convert what you have into adding/updating data values into that table and things should be simpler.
Note that to correctly use CHANGE_IN/CHANGE_OUT, you need to do something on the order of “select <attributes> from manu_by where change_in IN (0,<list of chg IDs for current and all previous revs>) and (change_out=0 OR change_out NOT IN (<list of chg IDs for current and all previous revs>) )”. Build your query and execute using DBMS_SQL.PARSE, and then you can list the AML for every revision of an item, and not just the latest revision.
