VIEW: APPS.GR_PHYSICAL_PROPERTY_XML

Source

View Text - Preformatted

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

View Text - HTML Formatted

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