Oracle Fusion Maintenance Cloud - Maintenance Asset Details Query

 SELECT 

  'KEY' AS "KEY",

ROWNUM SR_NO,

       CAB.ASSET_ID,

       CAB.ASSET_NUMBER,

       CAV.DESCRIPTION,

       (SELECT ASSET_NUMBER

          FROM CSE_ASSETS_B

         WHERE ASSET_ID = (SELECT PARENT_ASSET_ID

                             FROM CSE_AS_BUILT_RELATIONS_V

                            WHERE CHILD_ASSET_ID = CAB.ASSET_ID AND ROWNUM=1))

          PARENT_ASSET,

       (SELECT 

ESI.ITEM_NUMBER

FROM

EGP_SYSTEM_ITEMS_B ESI,

   EGP_SYSTEM_ITEMS_TL ESIT

WHERE

ESI.INVENTORY_ITEM_ID = ESIT.INVENTORY_ITEM_ID

AND ESI.ORGANIZATION_ID = ESIT.ORGANIZATION_ID

AND ESI.INVENTORY_ITEM_ID = CAB.ITEM_ID

AND ESIT.ORGANIZATION_ID=CAB.ITEM_ORGANIZATION_ID

)ITEM_NUMBER,

       (SELECT 

ESIT.DESCRIPTION

FROM

EGP_SYSTEM_ITEMS_B ESI,

   EGP_SYSTEM_ITEMS_TL ESIT

WHERE

ESI.INVENTORY_ITEM_ID = ESIT.INVENTORY_ITEM_ID

AND ESI.ORGANIZATION_ID = ESIT.ORGANIZATION_ID

AND ESI.INVENTORY_ITEM_ID = CAB.ITEM_ID

AND ESIT.ORGANIZATION_ID=CAB.ITEM_ORGANIZATION_ID

)     IETEM_DESC,

       CAB.SERIAL_NUMBER,

       (SELECT NAME

          FROM HR_ORGANIZATION_UNITS

         WHERE ORGANIZATION_ID=(select ITEM_ORGANIZATION_ID

from

CSE_ASSETS_HIST where TRANSACTED_ASSET_ID =

(select MIN(TRANSACTED_ASSET_ID) from CSE_ASSETS_HIST where asset_id = CAB.asset_id)) )

          MAINTANENCE_ORG,                                   --MAINTENANCE ORG

       (SELECT NAME

          FROM HR_ORGANIZATION_UNITS

         WHERE ORGANIZATION_ID = CAB.ITEM_ORGANIZATION_ID)

          OPERATING_ORG,                                       --OPERATING ORG

       CAB.CURRENT_LOCATION_CONTEXT,                          -- LOCATION TYPE

       (SELECT HP.ADDRESS1

        || ', '

        || HP.ADDRESS1

        || ', '

        || HP.ADDRESS3

        || ', '

        || HP.CITY

        || ', '

        || HP.COUNTRY FROM HZ_PARTIES HP WHERE HP.PARTY_ID=CAB.CONTACT_ID )

          LOCATION,                                               -- LOCATION,

       CAB.CONTACT_ID,

       (SELECT HP.PARTY_NAME FROM HZ_PARTIES HP WHERE HP.PARTY_ID=CAB.CONTACT_ID) CONTACT,                                     

       TO_CHAR(CAB.CREATION_DATE,'DD-MM-YY HH:MI:SS') CREATION_DATE,                              

       TO_CHAR(CAB.ACTIVE_END_DATE,'DD-MM-YY HH:MI:SS') ACTIVE_END_DATE,                                  

       CAB.ATTRIBUTE_CHAR1 ASSET_STATUS

  FROM CSE_ASSETS_B CAB,

       CSE_ASSETS_VL CAV

 WHERE     CAB.ASSET_ID = CAV.ASSET_ID

 AND ((TRUNC(CAB.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) OR TRUNC(CAB.LAST_UPDATE_DATE)= TRUNC(SYSDATE))

Oracle Fusion Maintenance Cloud - Maintenance Asset Meter Details Extract Query

 

SELECT

'KEY' AS "KEY",

 ROWNUM SR_NO,

       CAB.ASSET_ID,

       CAB.ASSET_NUMBER,

       CAV.DESCRIPTION Asset_Description,

   (SELECT NAME

          FROM HR_ORGANIZATION_UNITS

         WHERE ORGANIZATION_ID = CAB.ITEM_ORGANIZATION_ID)

          OPERATING_ORG,

  CAB.ATTRIBUTE_CHAR2 Primary_Custodian,

CM.METER_ID, 

CM.METER_DEFINITION_ID,

CMDT.METER_NAME,

CMDB.METER_CODE,

CMDB.UOM_CODE,

CM.ACTIVE_START_DATE,

CM.ACTIVE_END_DATE,

CMDB.INITIAL_READING_VALUE,

(select READING_DATE from CSE_METER_READINGS where meter_id=CM.METER_ID and INITIAL_FLAG='Y') Intial_Reading_Date,

(select READING_VALUE from CSE_METER_READINGS where METER_ID=CM.METER_ID and METER_READING_ID

= (select MAX(METER_READING_ID) from CSE_METER_READINGS where meter_id=CM.METER_ID )

)Last_Reading,

(select READING_DATE from CSE_METER_READINGS where METER_ID=CM.METER_ID and METER_READING_ID

= (select MAX(METER_READING_ID) from CSE_METER_READINGS where meter_id=CM.METER_ID )

)Last_Reading_Date,

(select READING_VALUE from CSE_METER_READINGS where METER_ID=CM.METER_ID and METER_READING_ID

= (select MAX(METER_READING_ID) from CSE_METER_READINGS where meter_id=CM.METER_ID )

)Life_to_date_Reading,

(select WORK_ORDER_NUMBER 

from MNT_WORK_ORDERS_V where WORK_ORDER_ID =

(select WORK_ORDER_ID from CSE_METER_READINGS where METER_ID=CM.METER_ID and METER_READING_ID

= (select MAX(METER_READING_ID) from CSE_METER_READINGS where meter_id=CM.METER_ID )

) )Work_Order,

CM.DAILY_UTILIZATION_RATE Base_Utilisation_Rate,

decode(CM.ALLOW_IN_MAINT_PROGRAM_FLAG,'Y','YES','NO') Allow_Maintenance,

CM.CREATED_BY Recorded_By

FROM CSE_ASSETS_B CAB,

       CSE_ASSETS_VL CAV,

  CSE_METER_DEFINITIONS_B CMDB,

  CSE_METER_DEFINITIONS_TL CMDT,

  CSE_METERS CM

 WHERE     CAB.ASSET_ID = CAV.ASSET_ID

 AND CMDB.METER_DEFINITION_ID=CMDT.METER_DEFINITION_ID

 AND CMDT.LANGUAGE='US'

 AND CM.METER_DEFINITION_ID=CMDB.METER_DEFINITION_ID

 AND CM.meter_object_id=CAB.ASSET_ID

 AND ((TRUNC(CAB.LAST_UPDATE_DATE) BETWEEN TRUNC(:P_START_DT) AND TRUNC(:P_END_DT)) OR TRUNC(CAB.LAST_UPDATE_DATE)= TRUNC(SYSDATE))

Oracle ETEXT Template Report Output showing special Character ‘??’ or UTF-8 symbol in the report

Problem:

We face an issue when we extract Item details using etext template .txt file. We check that the following item includes a special character.

Original Item: DC24V M27×1.5-A

Output Report Item Text: DC24V M27ױ.5-B

Resolution:

When we design a RTF template at the time we need to define <OUTPUT CHARACTER SET> Property  ‘UTF-8’

After printing this Etext template you need to update on the application and try to run the report and check.

Now report giving property output as expected.

Output: