1. ITEM QUANTITY VALUES CURRENCY
SELECT V.TOTAL_QOH, V.ITEM_DESCRIPTION,V.ORGANIZATION_NAME,M.ORGANIZATION_ID,M.SUBINVENTORY_CODE ,
NVL(C.CURRENCY_CODE,'USD')
FROM MTL_ONHAND_ITEMS_V V,MTL_ONHAND_QUANTITIES M, MTL_MATERIAL_TRANSACTIONS C
WHERE V.ORGANIZATION_ID=M.ORGANIZATION_ID
AND C.SUBINVENTORY_CODE = M.SUBINVENTORY_CODE
And
2. INTRANSIT REPORT
select
T.INVOICED_FLAG, T.ACTUAL_COST , T.TRANSACTION_DATE ,T.TRANSACTION_QUANTITY, T.INVENTORY_ITEM_ID , T.SUBINVENTORY_CODE
from mtl_material_transactions T, MTL_ONHAND_QUANTITIES O
where
T.SUBINVENTORY_CODE = 'INTRANSIT'
AND T.INVENTORY_ITEM_ID NOT IN O.INVENTORY_ITEM_ID
3. replenished item quantities to be ordered
SELECT b.SECONDARY_INVENTORY,t.total_qoh,
b.MINIMUM_ORDER_QUANTITY, b.FIXED_LOT_MULTIPLE, nvl(t.total_qoh - b.MIN_MINMAX_QUANTITY,0) QUANT_TO_BE_ORDERED
, s.description
FROM MTL_SYSTEM_ITEMS_B s, MTL_ITEM_SUB_INVENTORIES b, MTL_ONHAND_items_v t
WHERE PURCHASING_ITEM_FLAG ='Y'
AND SHIPPABLE_ITEM_FLAG ='Y'
and INVOICEABLE_ITEM_FLAG='Y'
and b.SECONDARY_INVENTORY = 'Consumable'
and s.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID
and t.ORGANIZATION_ID= b.ORGANIZATION_ID
4. organisation wise , subinventory code wise DATE WISE ALSO
CREATE OR REPLACE VIEW E108_INVENTORY
AS
select HR.organization_id, HR.name ,TR.transaction_quantity,
TR.transaction_date, TR.inventory_item_id, TR.subinventory_code,
TR.actual_cost from mtl_material_transactions TR ,hr_all_organization_units HR
WHERE TR.ORGANIZATION_ID= HR.ORGANIZATION_ID
AND Tr.ORGANIZATION_ID= HR.ORGANIZATION_ID
5. organisation wise , subinventory code wise and product wise also
CREATE OR REPLACE VIEW E108_INVENTORY1
AS
select IT.INVENTORY_ITEM_ID,IT.ITEM_DESCRIPTION, IT.PADDED_CONCATENATED_SEGMENTS,
HR.organization_id, HR.ORGANIZATION_NAME ,TR.transaction_quantity,
TR.transaction_date, TR.subinventory_code,
TR.actual_cost
from mtl_material_transactions TR ,MTL_ORGANIZATIONS HR,
mtl_onhand_items_v it
WHERE TR.ORGANIZATION_ID= HR.ORGANIZATION_ID
AND IT.ORGANIZATION_CODE= HR.ORGANIZATION_CODE
AND ROWNUM <=500
6. STATEMENT OF EXPENSES INCURRED IN PARTICULAR GL ENTRY ACCOUNT
CREATE OR REPLACE VIEW E108_EXP_BAL1 AS
select
GL.CHART_OF_ACCOUNTS_ID, GL.ACCOUNT_TYPE, GL.SEGMENT12 ACCOUNT_DEP,
GL.SEGMENT14 COMPANY, GL.SEGMENT16 DEPARTMENT, BL.CURRENCY_CODE,
BL.PERIOD_NET_DR ,BL.PERIOD_NET_CR ,substr(a.description,1,50) descr
, HE.PERIOD_NAME
FROM
gl_code_combinations GL ,
GL_BALANCES BL,
fnd_flex_value_sets c ,
fnd_flex_values b ,
fnd_flex_values_tl a,
GL_JE_HEADERS HE
where GL.CODE_COMBINATION_ID =BL.CODE_COMBINATION_ID
AND GL.CHART_OF_ACCOUNTS_ID=51389
AND b.flex_value_set_id = c.flex_value_set_id
and a.flex_value_id = b.flex_value_id
AND HE.SET_OF_BOOKS_ID=BL.SET_OF_BOOKS_ID
and a.language = 'US'
and c.FLEX_VALUE_SET_NAME like 'Operations Account'
AND B.FLEX_VALUE= GL.SEGMENT12;
7. VENDOR STATEMENT NOT COMPLETE
SELECT * O.TOTAL_QOH FROM MTL_SUPPLIER_SITES_V S , MTL_ONHAND_ITEMS_V O
WHERE S.ORGANIZATION_ID = O.ORGANIZATION_ID
8 Multiple View Multiple Organization Quantity Report
SELECT
&p_item_flex c_item_flex,
o.org_report_order report_order,
mp.organization_code organization_code,
msi.primary_uom_code uom_code,
msi.description item_description,
moh.inventory_item_id item_id,
moh.organization_id org_id,
msi.inventory_asset_flag asset_item,
&p_rev_col item_revision,
to_number(&p_avail_type) nettable,
to_number(&p_asset_inv) asset,
round(moh.item_cost,:C_extended_precision) item_cost,
sum(moh.transaction_quantity) item_qty
FROM
MTL_SYSTEM_ITEMS msi,
MTL_ONHAND_QTY_COST_V moh,
MTL_ORG_REPORT_TEMP O,
&p_sub_inv
WHERE
&P_WHERE_item
and o.report_id = :p_report_id
and o.organization_id in &P_report_orgids
and moh.organization_id in &P_report_orgids
and mp.organization_id = o.organization_id
and mp.organization_id = msi.organization_id
and msi.inventory_item_id = moh.inventory_item_id
and msi.organization_id = moh.organization_id
&p_sub_clause
&p_net_asset
GROUP BY
&p_item_flex,
o.org_report_order,
mp.organization_code,
msi.primary_uom_code,
msi.description,
moh.inventory_item_id,
msi.inventory_asset_flag,
&P_rev_col,
&p_avail_type,
&p_asset_inv,
moh.item_cost,
moh.organization_id
UNION ALL
SELECT
&p_item_flex c_item_flex,
o.org_report_order report_order,
mp.organization_code organization_code,
msi.primary_uom_code uom_code,
msi.description item_description,
t.item_id item_id,
t.intransit_owning_org_id org_id,
msi.inventory_asset_flag asset_item,
t.item_revision item_revision,
9 nettable,
9 asset,
round(to_number(&p_cst_item_cost),:C_extended_precision) item_cost,
--sum(decode(t.intransit_owning_org_id,t.from_organization_id, Bug #1316350
sum(decode(t.unit_of_measure, msi.primary_unit_of_measure,
t.quantity, t.to_org_primary_quantity)) item_qty
FROM
MTL_SYSTEM_ITEMS msi,
MTL_SUPPLY t ,
&p_gl_cost_tab
MTL_PARAMETERS MP
WHERE
&P_WHERE_item
and o.report_id = :p_report_id
and t.intransit_owning_org_id = o.organization_id
and &p_intrans_col
and msi.organization_id = t.intransit_owning_org_id
and mp.organization_id = msi.organization_id
and msi.inventory_item_id = t.item_id
&p_and_cost_clause
GROUP BY
&p_item_flex,
o.org_report_order,
mp.organization_code,
msi.primary_uom_code,
msi.description,
msi.inventory_asset_flag,
t.item_id,
t.intransit_owning_org_id,
t.item_revision,
&p_cst_item_cost
select ood.organization_name org_name,
ood.organization_code org_code,
gl.currency_code currency_code,
c.precision standard_precision,
nvl(c.extended_precision,c.precision) extended_precision
from org_organization_definitions ood
,mtl_org_report_temp o,
gl_sets_of_books gl,
fnd_currencies c
where ood.organization_id = o.organization_id
and o.report_id = :p_report_id
and ood.set_of_books_id = gl.set_of_books_id
and gl.currency_code = c.currency_code (+)
No comments:
Post a Comment