All Inventories Value Report - Standard Report

最近有做一张报表，需要用到All Inventories Values Report这张Oracle Standard Report的Receiving Value，现将代码记录如下:

 

SELECT

SUM(RECEIVE_QTY.RAW_QTY) RAW_QTY,
SUM(RECEIVE_QTY.RAW_VALUE_QTY * RECEIVE_QTY.UNIT_PRICE *
DECODE(RECEIVE_QTY.CURRENCY_CODE, 'USD' , 1 , RECEIVE_QTY.RATE)) RAW_VALUE,
RECEIVE_QTY.ORGANIZATION_ID,
RECEIVE_QTY.ITEM_NUMBER,
RECEIVE_QTY.INVENTORY_ITEM_ID,
RECEIVE_QTY.ORIGINAL_SOURCE_TYPE

FROM (SELECT DECODE(RCT.TRANSACTION_TYPE,
'RECEIVE' ,
RCT.PRIMARY_QUANTITY,
'RETURN TO RECEIVING' ,
RCT.PRIMARY_QUANTITY,
'DELIVER' ,
-RCT.PRIMARY_QUANTITY,
'RETURN TO VENDOR' ,
-RCT.PRIMARY_QUANTITY,
0 ) RAW_QTY,

DECODE(RCT.TRANSACTION_TYPE,
'RECEIVE' ,
RCT.QUANTITY,
'RETURN TO RECEIVING' ,
RCT.QUANTITY,
'DELIVER' ,
-RCT.QUANTITY,
'RETURN TO VENDOR' ,
-RCT.QUANTITY,
0 ) RAW_VALUE_QTY,

POL.UNIT_PRICE,
POH.CURRENCY_CODE,
POH.RATE_TYPE,
POH.RATE_DATE,
POH.RATE,

POL.PO_LINE_ID,
POL.ITEM_ID,
RCT.TRANSACTION_ID,

MSI.ORGANIZATION_ID,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1 ITEM_NUMBER,
RCT.TRANSACTION_DATE,

'Receiving' ORIGINAL_SOURCE_TYPE
FROM APPS.RCV_TRANSACTIONS RCT,
APPS.PO_LINES_ALL POL,
APPS.PO_HEADERS_ALL POH,
APPS.MTL_SYSTEM_ITEMS_B MSI

WHERE POH.ORG_ID = 81
AND MSI.ORGANIZATION_ID = 82
AND RCT.PO_LINE_ID = POL.PO_LINE_ID
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID) RECEIVE_QTY

GROUP BY RECEIVE_QTY.ORGANIZATION_ID,
RECEIVE_QTY.ITEM_NUMBER,
RECEIVE_QTY.INVENTORY_ITEM_ID,
ORIGINAL_SOURCE_TYPE

 

Note: 在实际开发的过程中，针对Receiving Value的值还进行了相关测试，发现并不是取standard cost，而是通过单价以及汇率综合得到。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/206955/viewspace-765547/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/206955/viewspace-765547/