題目一：
請依第一頁結構圖利用套用
Template
的方式建立料號
ITEM-A,
ITEM-B, ITEM-C, ITEM-D, ITEM-E, ITEM-F, ITEM-G並
assign to
TYO Org，並使用SQL語法抓取
TYO
Org料號資料，需有以下欄位:
Organization_Id, Organization_Code, Inventory_item_id, Item,
Item_Description, Primary_UOM, Item_Type, Item_Status, Make_or_Buy,
Supply_Type, Include_in_Rollup (25%)
select
mtl.inventory_item_id
as
Inventory_item_id,
mtl.segment1
as
item,
mtl.description
as
item_Description,
mtl.primary_unit_of_measure
as
Primary_UOM,
mtl.item_type
as
Item_Type,
mtl.inventory_item_status_code
as
Item_Status,
msi.PLANNING_MAKE_BUY_CODE
as
Make_or_Buy,
msi.WIP_SUPPLY_TYPE
as
SUPPLY_TYPE,
msi.DEFAULT_INCLUDE_IN_ROLLUP_FLAG
as
Include_in_Rollup
from
MTL_SYSTEM_ITEMS_B MTL, fnd_user MAN, MTL_SYSTEM_ITEMS_FVL MSI
where
MTL.created_by = MAN.user_id
and
MAN.user_name = 'Y16019'
and
mtl.inventory_item_id = msi.INVENTORY_ITEM_ID
題目二：請於TYO
Org建立以下資訊：(25%)
建立倉別
SUB-A 儲位
SUB-A-1
設定料號單位轉換
ITEM-D
100pcs=1box
於倉別
SUB-A 儲位
SUB-A-1雜收
ITEM-A,
ITEM-B, ITEM-C, ITEM-D, ITEM-E, ITEM-F, ITEM-G 各1000
於倉別
SUB-A 儲位
SUB-A-1將
ITEM-D
轉倉至 TYO-MR1
儲位A101
共1box，請使用
box 為交易單位
題目三：
請使用SQL語法，抓取第2題的在
SUB-A倉所有料號彙總庫存，需有以下欄位:
Organization_Code, Item, Item_Description, Item_Revision,
Subinventory, Locator, Onhand_Qty (25%)
select
Organization_Code,
Item,
Item_Description,
Revision,
Subinventory_code,
Locator,
On_hand
FROM
MTL_ONHAND_TOTAL_MWB_V
WHERE
1=1
AND
locator_id = '17274'
AND
subinventory_code = 'SUB-A2-19'
AND
organization_id = '96'
題目四：
請使用
SQL
語法，抓取第2題所發生的交易，以及各交易對應的Account
Distributions。需有以下欄位:
Organization_code, Item, Revision, Subinventory, Locator,
Transfer_Org, Transfer_Subinventory, Transfer_Locator, Primary_Qty,
Transaction_Date, Transaction_Type (25%)
SELECT
MTRA.ORGANIZATION_ID,
MTRA.REVISION,
mtl.segment1,
MTRA.SUBINVENTORY_CODE,
MTRA.LOCATOR_ID,
MTRA.TRANSFER_ORGANIZATION_ID,
MTRA.TRANSFER_SUBINVENTORY,
MTRA.TRANSFER_LOCATOR_ID,
MTRA.PRIMARY_QUANTITY,
MTRA.TRANSACTION_DATE,
MTRA.TRANSACTION_TYPE_ID
FROM
MTL_MATERIAL_TRANSACTIONS MTRA, MTL_SYSTEM_ITEMS_B MTL, fnd_user MAN
WHERE
MTRA.TRANSACTION_ACTION_ID NOT
IN
(24,30)
and
(( MTRA.ORGANIZATION_ID = 96
)
and
( MTRA.transaction_date between
to_date('15-06-2016
00:00:00','DD-MM-YYYY
HH24:MI:SS')
AND
to_date('16-06-2016
23:59:59','DD-MM-YYYY
HH24:MI:SS')
)
and
(MTRA.LOGICAL_TRANSACTION=2
OR
MTRA.LOGICAL_TRANSACTION IS
NULL
))
and
MTL.inventory_item_id = MTRA.Inventory_Item_Id
and
MTL.created_by = MAN.user_id
and
MAN.user_name = 'Y16019'
order
by
MTRA.TRANSACTION_DATE DESC,
MTRA.TRANSACTION_ID DESC,
MTRA.SUBINVENTORY_CODE DESC
