-- #New: 04.04.2017/A.Becker+J.Fes
-- #Doc: list for IR all IRS where IR is used
-- #Doc: Param ir_q.puid = :ir_puid
-- #Hint: on Update, please also update IR_WhereUsedCount.sql
SELECT distinct
 i_q.pitem_id             AS ItemId_q,
 i_q.puid                 AS ItemPUID_q,
 ir_q.pitem_revision_id   AS IrId_q,
 ir_q.puid                AS IrPIUD_q,
 bvr.pis_precise          AS BOMIsPrecise_q,
 i.pitem_id               AS ItemId_u,
 i.puid                   AS ItemPuid_u,
 irUsed.pitem_revision_id AS IrId_u,
 irUsed.puid              AS IrPUID_u
FROM
 infodba.PITEMREVISION    ir_q
 INNER JOIN infodba.PITEM i_q ON (i_q.puid = ir_q.ritems_tagu)
 INNER JOIN infodba.PPSOCCURRENCE bo ON (   bo.rchild_itemu = ir_q.puid -- PRECISE
                                         or bo.rchild_itemu = ir_q.ritems_tagu -- IMPRECISE
                                        )
 INNER JOIN infodba.PPSBOMVIEWREVISION bvr ON (bvr.puid = bo.rparent_bvru)
 INNER JOIN infodba.PSTRUCTURE_REVISIONS sr on (sr.pvalu_0 = bvr.puid)
 INNER JOIN infodba.PITEMREVISION ir ON (ir.puid = sr.puid)     -- SELECT ir.pitem_revision_id AS IrId, ir.puid AS IrPuid
 INNER JOIN infodba.PITEM i ON (i.puid = ir.ritems_tagu)                -- SELECT i.pitem_id AS ItemId
 INNER JOIN infodba.PITEMREVISION irUsed ON (    (bvr.pis_precise = 1 AND irUsed.puid = ir.puid)  -- PRECISE only
                                              or (bvr.pis_precise = 0 AND irUsed.ritems_tagu = i.puid)   -- IMPRECISE alle IR of Item
                                              )
WHERE
 ir_q.puid = :ir_puid
-- #Exa: TC83
-- ir_q.puid in ('gAcNslByTzDfjB', 'A4bNslByTzDfjB' )
-- BG-KBG-03/B        'gAcNslByTzDfjB'
-- BG-Einzelteil-01/C 'A4bNslByTzDfjB'
ORDER BY i_q.pitem_id, ir_q.pitem_revision_id

