ITPub博客

首页 > Linux操作系统 > Linux操作系统 > All Inventories Value Report - Standard Report

All Inventories Value Report - Standard Report

原创 Linux操作系统 作者:starhot 时间:2013-07-05 08:17:30 0 删除 编辑
最近有做一张报表,需要用到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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-01-18

  • 博文量
    9
  • 访问量
    25668