SELECT OEH.ORG_ID
,OEL.SHIP_FROM_ORG_ID
,OEH.ORDER_NUMBER
,OEL.LINE_NUMBER
,OEL.INVENTORY_ITEM_ID
,NULL--MSI.SEGMENT1
,NULL--MSI.DESCRIPTION ITEM_DESC
,NULL--MSI.ITEM_TYPE
,NULL--OEL.ORDER_QUANTITY_UOM UOM
,NULL--MCB.SEGMENT4 COST_CENTER
,NULL--MCB.SEGMENT2 AUX
,NULL--MCB.SEGMENT3 CUSTOMS_DUTY
,TO_CHAR(DECODE(OEL.ACTUAL_SHIPMENT_DATE,NULL,OEL.FULFILLMENT_DATE,OEL.ACTUAL_SHIPMENT_DATE),'YYYY-MM') SHIPPED_MONTH
,NVL(DECODE(OEL.LINE_CATEGORY_CODE,'RETURN',-1 * OEL.SHIPPED_QUANTITY,OEL.SHIPPED_QUANTITY),0) SHIPPED_QTY
,OEH.TRANSACTIONAL_CURR_CODE CURRENCY
,GDRCNY.CONVERSION_RATE CNY_RATE
,GDRUSD.CONVERSION_RATE USD_RATE
,NVL(OEL.UNIT_SELLING_PRICE,0) * NVL(DECODE(OEL.LINE_CATEGORY_CODE,'RETURN',-1 * OEL.SHIPPED_QUANTITY,OEL.SHIPPED_QUANTITY),0) * NVL(GDRCNY.CONVERSION_RATE,0) CNY_AMOUNT
,NVL(OEL.UNIT_SELLING_PRICE,0) * NVL(DECODE(OEL.LINE_CATEGORY_CODE,'RETURN',-1 * OEL.SHIPPED_QUANTITY,OEL.SHIPPED_QUANTITY),0) * NVL(GDRCNY.CONVERSION_RATE,0) * NVL(GDRUSD.CONVERSION_RATE,0) USD_AMOUNT
,0--CNY_SUM_AMT
,0--USD_SUM_AMT
FROM OE_ORDER_HEADERS_ALL OEH
,OE_ORDER_LINES_ALL OEL
,GL_DAILY_RATES GDRCNY
,GL_DAILY_RATES GDRUSD
WHERE OEH.HEADER_ID = OEL.HEADER_ID
AND OEH.BOOKED_FLAG = 'Y'
AND NVL(OEH.CANCELLED_FLAG,'N') = 'N'
AND NVL(OEL.CANCELLED_FLAG,'N')='N'
AND OEH.ORG_ID = 243
AND NVL(OEL.SHIPPED_QUANTITY,0)>0
AND TRUNC(OEH.ORDERED_DATE) = TRUNC(GDRCNY.CONVERSION_DATE)
AND (OEH.TRANSACTIONAL_CURR_CODE = GDRCNY.FROM_CURRENCY)
AND GDRCNY.TO_CURRENCY = 'CNY'
AND NVL(OEH.CONVERSION_TYPE_CODE,'Corporate') = GDRCNY.CONVERSION_TYPE
AND TRUNC(OEL.CREATION_DATE) = TRUNC(GDRUSD.CONVERSION_DATE)
AND GDRUSD.FROM_CURRENCY = 'CNY'
AND GDRUSD.TO_CURRENCY = 'USD'
AND NVL(OEH.CONVERSION_TYPE_CODE,'Corporate') = GDRUSD.CONVERSION_TYPE
UNION ALL
SELECT OEH.ORG_ID
,OEL.SHIP_FROM_ORG_ID
,OEH.ORDER_NUMBER
,OEL.LINE_NUMBER
,OEL.INVENTORY_ITEM_ID
,NULL--MSI.SEGMENT1
,NULL--MSI.DESCRIPTION ITEM_DESC
,NULL--MSI.ITEM_TYPE
,NULL--OEL.ORDER_QUANTITY_UOM UOM
,NULL--MCB.SEGMENT4 COST_CENTER
,NULL--MCB.SEGMENT2 AUX
,NULL--MCB.SEGMENT3 CUSTOMS_DUTY
,TO_CHAR(DECODE(OEL.ACTUAL_SHIPMENT_DATE,NULL,OEL.FULFILLMENT_DATE,OEL.ACTUAL_SHIPMENT_DATE),'YYYY-MM') SHIPPED_MONTH
,NVL(DECODE(OEL.LINE_CATEGORY_CODE,'RETURN',-1 * OEL.SHIPPED_QUANTITY,OEL.SHIPPED_QUANTITY),0) SHIPPED_QTY
,OEH.TRANSACTIONAL_CURR_CODE CURRENCY
,1 CNY_RATE
,GDRUSD.CONVERSION_RATE USD_RATE
,NVL(OEL.UNIT_SELLING_PRICE,0) * NVL(DECODE(OEL.LINE_CATEGORY_CODE,'RETURN',-1 * OEL.SHIPPED_QUANTITY,OEL.SHIPPED_QUANTITY),0) CNY_AMOUNT
,NVL(OEL.UNIT_SELLING_PRICE,0) * NVL(DECODE(OEL.LINE_CATEGORY_CODE,'RETURN',-1 * OEL.SHIPPED_QUANTITY,OEL.SHIPPED_QUANTITY),0) * NVL(GDRUSD.CONVERSION_RATE,0) USD_AMOUNT
,0--CNY_SUM_AMT
,0--USD_SUM_AMT
FROM OE_ORDER_HEADERS_ALL OEH
,OE_ORDER_LINES_ALL OEL
,GL_DAILY_RATES GDRUSD
WHERE OEH.HEADER_ID = OEL.HEADER_ID
AND OEH.BOOKED_FLAG = 'Y'
AND NVL(OEH.CANCELLED_FLAG,'N') = 'N'
AND NVL(OEL.CANCELLED_FLAG,'N')='N'
AND OEH.ORG_ID = 243
AND NVL(OEL.SHIPPED_QUANTITY,0)>0
AND OEH.TRANSACTIONAL_CURR_CODE = 'CNY'
AND TRUNC(OEL.CREATION_DATE) = TRUNC(GDRUSD.CONVERSION_DATE)
AND GDRUSD.FROM_CURRENCY = 'CNY'
AND GDRUSD.TO_CURRENCY = 'USD'
AND NVL(OEH.CONVERSION_TYPE_CODE,'Corporate') = GDRUSD.CONVERSION_TYPE[@more@]