<hibernate-mapping schema="REBATE" default-lazy="false">
   <class name="com.XXX.service.report.vo.ReportData" table="TEMP_REPORT">
              
       <property name="invoiceNumber" column="INVOICE_NUMBER"/>
      <property name="invoiceDate" column="INVOICE_DATE"/>
      <property name="distributorId" column="DISTRIBUTOR_ID"/>
      <property name="salesOrderDate" column="INVOICE_ORDER_DATE"/>
      <property name="invoiceRecievedDate" column="INVOICE_RECEIVED_ON"/>
      <property name="materialId" column="MATERIAL_ID"/>
      <property name="shipmentQuantity" column="SHIPMENT_QUANTITY"/>
      <property name="unitPrice" column="INVOICE_UNIT_PRICE"/>
      <property name="suggestedPrice" column="SUGGESTED_UNIT_PRICE"/>
      <property name="rebatePerUnit" column="REBATE_PER_UNIT"/>
      <property name="totalRebate" column="TOTAL_REBATE_AMOUNT"/>
      <property name="rebateProgramName" column="REBATE_PROGRAM_NAME"/>
      <property name="paymentDate" column="PAYMENT_DATE"/>
      <property name="creditMemoNo" column="CREDIT_MEMO_NUMBER"/>
      <property name="rebateStatus" column="REBATE_STATUS"/>
   </class>
   <query name = "getCommercialReport">
      from ReportData reportData where reportData.distributorId = :distributorId
   </query>
</hibernate-mapping>   

create view temp_report (
INVOICE_NUMBER,
INVOICE_DATE,
DISTRIBUTOR_ID,
INVOICE_ORDER_DATE,
INVOICE_RECEIVED_ON,
MATERIAL_ID,
--MATERIAL_NAME,
SHIPMENT_QUANTITY,
INVOICE_UNIT_PRICE,
SUGGESTED_UNIT_PRICE,
REBATE_PER_UNIT,
TOTAL_REBATE_AMOUNT,
REBATE_PROGRAM_NAME,
PAYMENT_DATE,
CREDIT_MEMO_NUMBER,
REBATE_STATUS
)as
Select
A.INVOICE_NUMBER,
A.INVOICE_DATE ,
A.CHANNEL_PARTNER,
B.SALES_ORDER_DATE ,
B.INVOICE_LOAD_DATE,
C.MATERIAL_ID,
--D.MATERIAL_DESC,
C.SHIPMENT_QUANTITY,
C.UNIT_PRICE,
E.SUGGESTED_PRICE,
case when C.SHIPMENT_QUANTITY != 0 then C.REBATE_AMOUNT/C.SHIPMENT_QUANTITY
     when C.SHIPMENT_QUANTITY = 0 then 0
     end,
C.REBATE_AMOUNT,
F.PROGRAM_NAME,
A.DATE_TO_BE_PAID,
A.BATCH_ID,
case when A.PAID_FLAG = 'InProcess' then 'payments pending to be processed'
     when A.PAID_FLAG = 'Submitted' then 'payments submitted'
     end
from
REBATE_PAYMENT A,
INVOICE_TRACK_DETAIL B,
INVOICE_LINE_ITEM C,
--DIM_SAP_MATERIAL_VIEW D,
PROGRAM_MATERIAL E,
REBATE_PROGRAM F
where
A.DETAIL_TRACK_ID = B.ID and
--C.MATERIAL_ID = D.MATERIAL_SAP_ID and
A.ID = C.REBATE_PAYMENT_ID and
C.MATERIAL_ID = E.MATERIAL_ID and
A.REBATE_PROGRAMID = E.REBATE_PROGRAMID and
A.REBATE_PROGRAMID = F.ID
union
select
A.INVOICE_NUMBER,
A.INVOICE_DATE,
A.CHANNEL_PARTNER,
E.SALES_ORDER_DATE,
E.INVOICE_LOAD_DATE,
A.MATERIAL_ID,
--B.MATERIAL_DESC,
A.SHIPMENT_QUANTITY,
A.UNIT_PRICE,
C.SUGGESTED_PRICE,
null,
null,
D.PROGRAM_NAME,
null,
null,
F.ERROR_DESCRIPTION
from
FAILED_REBATE A,
--Left outer Join DIM_SAP_MATERIAL_VIEW B on A.MATERIAL_ID = B.MATERIAL_SAP_ID
PROGRAM_MATERIAL C,
REBATE_PROGRAM D,
INVOICE_TRACK_DETAIL E,
REBATE_ERRORS F
where
A.DETAIL_TRACK_ID = E.ID and
A.ERROR_ID = F.ERROR_ID and
A.MATERIAL_ID = C.MATERIAL_ID and
A.PROGRAM_ID = C.REBATE_PROGRAMID and
A.PROGRAM_ID = D.ID