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:

 

How to resolve BIP Report Excel Output column shrinkage issue

How to resolve BIP Report Excel Output column shrinkage issue

 

Issue Description:

I have designed RTF report and at time of generating report output in excel format, In output table column automatically shrink, Which will not be accepted as per standard practice because the user will not resize the table column when they use on daily basis.

 

I am getting output like this format which is not as per the standard.

Resolution:

For solving this table column shrinkage issue we have to set RTF table property.

This is my RTF file format.

Step1:

Now select the table and  right-click and select table property.

Select Table > Right Click > Select Table Property

Step2:

Select the column tab > Enter Preferred width: 15% > Ok.

 

Step4:

Now save the RTF file and upload your template and check. Now excel output will come in proper format.