Mar 26, 2010

Different Report Queries...


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:

OraApps Search

Custom Search

Search This Blog