==Data Transformation: Export Lifecycle Actions== --General tab-- Name: Export Lifecycle Actions Description: Identifies items with lifecycle Relative Maturity values that are mismatched to items they appear on, such as a Prototype component on a Production assembly. A pending iteration is examined only if there's no released iteration available. Active: Yes Default: No Permanent: No --XML tab-- ' DECLARE @demote varchar(10) SET @demote = '< Return' DECLARE @suitable varchar(10) SET @suitable = '= OK =' SELECT CASE ItemClass WHEN 1 THEN 'Part' ELSE 'Document' END AS ItemClass , ItemOwner , ItemType , ItemNumber , ItemRevision , ItemLifecycle , LCAction , ItemName , UsedAs , CASE OnClass WHEN 1 THEN 'Part' ELSE 'Document' END AS OnClass , OnOwner , OnType , OnNumber , OnRevision , OnLifecycle , OnName FROM ( SELECT * , CASE WHEN OnRM < 0 THEN -- IF (child < parent) OR (child > 0) THEN warn CASE WHEN ItemRM < OnRM THEN @promote WHEN ItemRM > 0 THEN @demote ELSE @suitable END WHEN OnRM = 0 THEN -- IF child != 0 THEN warn CASE Sign(ItemRM) WHEN -1 THEN @promote WHEN 0 THEN @suitable WHEN 1 THEN @demote END WHEN OnRM > 0 THEN --IF (child > parent) OR (child < 0) THEN warn CASE WHEN ItemRM > OnRM THEN @demote WHEN ItemRM < 0 THEN @promote ELSE @suitable END END AS LCAction FROM ( SELECT Class_Child AS ItemClass , OwnerShort_Child AS ItemOwner , TypeShort_Child AS ItemType , Number_Child AS ItemNumber , Coalesce([Revision_Child],[PendingRevision_Child]+'*') AS ItemRevision , Coalesce([Lifecycle_Child],[PendingLifecycle_Child]) AS ItemLifecycle , Coalesce([RelativeMaturity_Child],[PendingRelativeMaturity_Child]) AS ItemRM , Description_Child AS ItemName , 'BOM' AS UsedAs , Class AS OnClass , OwnerShort AS OnOwner , TypeShort AS OnType , Number AS OnNumber , Coalesce([Revision],[PendingRevision]+'*') AS OnRevision , Coalesce([Lifecycle],[PendingLifecycle]) AS OnLifecycle , Coalesce([RelativeMaturity],[PendingRelativeMaturity]) AS OnRM , Description AS OnName FROM PDXpertDB.viewer.StructurePairView WHERE (([PendingRevision] IS NOT NULL) OR (IsCanceled = 0)) AND ([Number_Child] Is Not Null) UNION SELECT Class_Child AS ItemClass , OwnerShort_Child AS ItemOwner , TypeShort_Child AS ItemType , Number_Child AS ItemNumber , Coalesce([Revision_Child],[PendingRevision_Child]+'*') AS ItemRevision , Coalesce([Lifecycle_Child],[PendingLifecycle_Child]) AS ItemLifecycle , Coalesce([RelativeMaturity_Child],[PendingRelativeMaturity_Child]) AS ItemRM , Description_Child AS ItemName , 'Reference' AS UsedAs , Class AS OnClass , OwnerShort AS OnOwner , TypeShort AS OnType , Number AS OnNumber , Coalesce([Revision],[PendingRevision]+'*') AS OnRevision , Coalesce([Lifecycle],[PendingLifecycle]) AS OnLifecycle , Coalesce([RelativeMaturity],[PendingRelativeMaturity]) AS OnRM , Description AS OnName FROM PDXpertDB.viewer.ReferencePairView WHERE (([PendingRevision] IS NOT NULL) OR (IsCanceled = 0)) AND ([Number_Child] Is Not Null) UNION SELECT Class_Child AS ItemClass , OwnerShort_Child AS ItemOwner , TypeShort_Child AS ItemType , Number_Child AS ItemNumber , Coalesce([Revision_Child],[PendingRevision_Child]+'*') AS ItemRevision , Coalesce([Lifecycle_Child],[PendingLifecycle_Child]) AS ItemLifecycle , Coalesce([RelativeMaturity_Child],[PendingRelativeMaturity_Child]) AS ItemRM , Description_Child AS ItemName , 'Source' AS UsedAs , Class AS OnClass , OwnerShort AS OnOwner , TypeShort AS OnType , Number AS OnNumber , Coalesce([Revision],[PendingRevision]+'*') AS OnRevision , Coalesce([Lifecycle],[PendingLifecycle]) AS OnLifecycle , Coalesce([RelativeMaturity],[PendingRelativeMaturity]) AS OnRM , Description AS OnName FROM PDXpertDB.viewer.SourcePairView WHERE (([PendingRevision] IS NOT NULL) OR (IsCanceled = 0)) AND ([Number_Child] Is Not Null) ) AS GetData ) AS GetWarning WHERE LCAction <> @suitable ORDER BY ItemClass, ItemOwner, ItemType, ItemNumber, OnClass, OnOwner, OnType, OnNumber ]]> Table2