View: GR_PHYSICAL_PROPERTY_XML

Product:	GR - Process Manufacturing Regulatory Management
Description:	View for Inventory Item and its associated Physical Properties details
Implementation/DBA Data:	APPS.GR_PHYSICAL_PROPERTY_XML

View Text

SELECT A.ORGANIZATION_ID ORGNID
, A.INVENTORY_ITEM_ID ITEMID
, MAX(DECODE(C.XML_ELEMENT
, 'CONSUMERUSE'
, G.MEANING)) CONSUMERUSE
, MAX(DECODE(C.XML_ELEMENT
, 'PRODUCTUSE'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) PRODUCTUSE
, MAX(DECODE(C.XML_ELEMENT
, 'TRADESECRETNUMBER'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) TRADESECRETNUMBER
, MAX(DECODE(C.XML_ELEMENT
, 'CARCENGENICITY'
, G.MEANING)) CARCENGENICITY
, MAX(DECODE(C.XML_ELEMENT
, 'AUTOIGNITIONTEMP1UNIT'
, G.MEANING)) AUTOIGNITIONTEMP1UNIT
, MAX(DECODE(C.XML_ELEMENT
, 'AUTOIGNITIONTEMP1'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) AUTOIGNITIONTEMP1
, MAX(DECODE(C.XML_ELEMENT
, 'LOWERFLAMELIMIT'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) LOWERFLAMELIMIT
, MAX(DECODE(C.XML_ELEMENT
, 'UPPERFLAMELIMIT'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) UPPERFLAMELIMIT
, MAX(DECODE(C.XML_ELEMENT
, 'FLASHPOINT1METHODUSED'
, G.MEANING)) FLASHPOINT1METHODUSED
, MAX(DECODE(C.XML_ELEMENT
, 'FLASHPOINT1TEMPUNIT'
, G.MEANING)) FLASHPOINT1TEMPUNIT
, MAX(DECODE(C.XML_ELEMENT
, 'FLASHPOINT1'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) FLASHPOINT1
, MAX(DECODE(C.XML_ELEMENT
, 'FLASHPOINT2'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) FLASHPOINT2
, MAX(DECODE(C.XML_ELEMENT
, 'BOILINGPOINT1TEMPERATUREUNIT'
, G.MEANING)) BOILINGPOINT1TEMPERATUREUNIT
, MAX(DECODE(C.XML_ELEMENT
, 'BOILINGPOINT2'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) BOILINGPOINT2
, MAX(DECODE(C.XML_ELEMENT
, 'BOILINGPOINT1'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) BOILINGPOINT1
, MAX(DECODE(C.XML_ELEMENT
, 'COLOR'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) COLOR
, MAX(DECODE(C.XML_ELEMENT
, 'EVAPORATIONRATE1QUALIFER'
, '=')) EVAPORATIONRATE1QUALIFER
, MAX(DECODE(C.XML_ELEMENT
, 'EVAPORATIONRATE1'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) EVAPORATIONRATE1
, MAX(DECODE(C.XML_ELEMENT
, 'FREEZINGPOINT1'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) FREEZINGPOINT1
, MAX(DECODE(C.XML_ELEMENT
, 'FREEZINGPOINT1UNIT'
, G.MEANING)) FREEZINGPOINT1UNIT
, MAX(DECODE(C.XML_ELEMENT
, 'IMMISCIBLEINWATER'
, G.MEANING)) IMMISCIBLEINWATER
, MAX(DECODE(C.XML_ELEMENT
, 'MELTINGPOINT1'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) MELTINGPOINT1
, MAX(DECODE(C.XML_ELEMENT
, 'MELTINGPOINT1TEMPUNIT'
, G.MEANING)) MELTINGPOINT1TEMPUNIT
, MAX(DECODE(C.XML_ELEMENT
, 'MOISTUREABSORBING'
, G.MEANING)) MOISTUREABSORBING
, MAX(DECODE(C.XML_ELEMENT
, 'MOLECULARWEIGHT'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) MOLECULARWEIGHT
, MAX(DECODE(C.XML_ELEMENT
, 'MUTAGENICITY'
, G.MEANING)) MUTAGENICITY
, MAX(DECODE(C.XML_ELEMENT
, 'ODOR'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) ODOR
, MAX(DECODE(C.XML_ELEMENT
, 'PH2'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) PH2
, MAX(DECODE(C.XML_ELEMENT
, 'PH1'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) PH1
, MAX(DECODE(C.XML_ELEMENT
, 'SKINIRRITANT'
, G.MEANING)) SKINIRRITANT
, MAX(DECODE(C.XML_ELEMENT
, 'SOLUBILITYINETHANOL'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) SOLUBILITYINETHANOL
, MAX(DECODE(C.XML_ELEMENT
, 'SOLUBILITYINFATS'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) SOLUBILITYINFATS
, MAX(DECODE(C.XML_ELEMENT
, 'SOLUBILITYINGLYCEROL'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) SOLUBILITYINGLYCEROL
, MAX(DECODE(C.XML_ELEMENT
, 'OTHERSOLUBILITY'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) OTHERSOLUBILITY
, MAX(DECODE(C.XML_ELEMENT
, 'SOLUBILITYINWATER'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) SOLUBILITYINWATER
, MAX(DECODE(C.XML_ELEMENT
, 'SPECIFICGRAVITY1'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) SPECIFICGRAVITY1
, MAX(DECODE(C.XML_ELEMENT
, 'VAPORDENSITYUNIT'
, G.MEANING)) VAPORDENSITYUNIT
, MAX(DECODE(C.XML_ELEMENT
, 'VAPORDENSITY'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) VAPORDENSITY
, MAX(DECODE(C.XML_ELEMENT
, 'VAPORPRESSURE1TEMPUNIT'
, G.MEANING)) VAPORPRESSURE1TEMPUNIT
, MAX(DECODE(C.XML_ELEMENT
, 'VAPORPRESSURE1TEMP'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) VAPORPRESSURE1TEMP
, MAX(DECODE(C.XML_ELEMENT
, 'VAPORPRESSURE1'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) VAPORPRESSURE1
, MAX(DECODE(C.XML_ELEMENT
, 'VOC'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) VOC
, MAX(DECODE(C.XML_ELEMENT
, 'VOLATILEWEIGHT'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) VOLATILEWEIGHT
, MAX(DECODE(C.XML_ELEMENT
, 'VOLATILEVOLUME'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) VOLATILEVOLUME
, MAX(DECODE(C.XML_ELEMENT
, 'DECOMPOSITIONTEMP1UNIT'
, G.MEANING)) DECOMPOSITIONTEMP1UNIT
, MAX(DECODE(C.XML_ELEMENT
, 'DECOMPOSITIONTEMP1'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) DECOMPOSITIONTEMP1
, MAX(DECODE(C.XML_ELEMENT
, 'HAZARDOUSPOLYMERIZATION'
, G.MEANING)) HAZARDOUSPOLYMERIZATION
, MAX(DECODE(C.XML_ELEMENT
, 'STRONGOXIDIZER'
, G.MEANING)) STRONGOXIDIZER
, MAX(DECODE(C.XML_ELEMENT
, 'TRADESECRETEXPIREDATE'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'D'
, TO_CHAR(A.DATE_VALUE)))) TRADESECRETEXPIREDATE
, MAX(DECODE(C.XML_ELEMENT
, 'TRADESECRETREGISTRATION'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) TRADESECRETREGISTRATION
, MAX(DECODE(C.XML_ELEMENT
, 'UNNUMBER'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) UNNUMBER
, MAX(DECODE(C.XML_ELEMENT
, 'CLASSIFICATIONTYPE'
, G.MEANING)) CLASSIFICATIONTYPE
, MAX(DECODE(C.XML_ELEMENT
, 'UNSUBSIDIARYRISK'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) UNSUBSIDIARYRISK
, MAX(DECODE(C.XML_ELEMENT
, 'TRANSPORTREGULATIONPACKGROUP'
, G.MEANING)) TRANSPORTREGULATIONPACKGROUP
, MAX(DECODE(C.XML_ELEMENT
, 'EMERGENCYRESPNUMBER'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) EMERGENCYRESPNUMBER
, MAX(DECODE(C.XML_ELEMENT
, 'EMERGENCYSTORAGECODE'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) EMERGENCYSTORAGECODE
, MAX(DECODE(C.XML_ELEMENT
, 'KEMMLERNO'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) KEMMLERNO
, MAX(DECODE(C.XML_ELEMENT
, 'IMDGCLASS'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) IMDGCLASS
, MAX(DECODE(C.XML_ELEMENT
, 'MARINEPOLLUTANT'
, G.MEANING)) MARINEPOLLUTANT
, MAX(DECODE(C.XML_ELEMENT
, 'ADRRIDNUMBER'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) ADRRIDNUMBER
, MAX(DECODE(C.XML_ELEMENT
, 'ADRITEMCODE'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) ADRITEMCODE
, MAX(DECODE(C.XML_ELEMENT
, 'ADNRWATERWAY'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) ADNRWATERWAY
, MAX(DECODE(C.XML_ELEMENT
, 'HAZCHEMCODE'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) HAZCHEMCODE
, MAX(DECODE(C.XML_ELEMENT
, 'CHEMTRECNUMBER'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) CHEMTRECNUMBER
, MAX(DECODE(C.XML_ELEMENT
, 'CANUTECNUMBER'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) CANUTECNUMBER
, MAX(DECODE(C.XML_ELEMENT
, 'EPAREGISTRATIONNUMBER'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'A'
, A.ALPHA_VALUE))) EPAREGISTRATIONNUMBER
, MAX(DECODE(C.XML_ELEMENT
, 'SUPPLIERNUMBER'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'N'
, TO_CHAR(A.NUMBER_VALUE)))) SUPPLIERNUMBER
, MAX(DECODE(C.XML_ELEMENT
, 'SUPPLIERREVISIONDATE'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'D'
, TO_CHAR(A.DATE_VALUE)))) SUPPLIERREVISIONDATE
, MAX(DECODE(C.XML_ELEMENT
, 'SUPPLIERDATEPREPARED'
, DECODE(B.PROPERTY_TYPE_INDICATOR
, 'D'
, TO_CHAR(A.DATE_VALUE)))) SUPPLIERDATEPREPARED
FROM GR_INV_ITEM_PROPERTIES A
, GR_PROPERTIES_B B
, GR_XML_PROPERTIES_MAP C
, GR_PROPERTY_VALUES_TL G
WHERE A.LABEL_CODE = C.FIELD_NAME_CODE
AND (A.PROPERTY_ID = B.PROPERTY_ID
AND A.PROPERTY_ID = C.PROPERTY_ID
AND B.PROPERTY_ID = C.PROPERTY_ID
AND B.PROPERTY_TYPE_INDICATOR IN ('N'
, 'D'
, 'A')) OR ( B.PROPERTY_TYPE_INDICATOR = 'F'
AND G.LANGUAGE = USERENV('LANG')
AND A.PROPERTY_ID = G.PROPERTY_ID
AND B.PROPERTY_ID = G.PROPERTY_ID
AND C.PROPERTY_ID = G.PROPERTY_ID
AND A.ALPHA_VALUE = G.VALUE) GROUP BY A.ORGANIZATION_ID
, A.INVENTORY_ITEM_ID

Columns