Thursday 14 June 2012

querys

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 (+)

Script To find Oracle API's for all module

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'PA_%API%'
order by
a.owner, a.name;

FORM COMPILING STEPS CUSTOM.PLL IN ORACLE APPS

frmcmp_batch module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special

po report query

SELECT
  O.NAME "OPERATING_UNIT_NAME",
  PH.SEGMENT1 "PO_REV_NUM",
  PH.PO_HEADER_ID, 
  PH.TYPE_LOOKUP_CODE "TYPE",
  PH.CURRENCY_CODE,
  PH.AUTHORIZATION_STATUS "STATUS",
  PV.VENDOR_SITE_CODE,
  V.VENDOR_NAME "SUPPLIER_NAME",
  LOC1.LOCATION_CODE "SHIP_TO_LOC",
  LOC2.LOCATION_CODE "BILL_TO_LOC",
  (PL1.QUANTITY*PL1.UNIT_PRICE) "TOTAL",
--LINES INFORMATION 
  PL1.LINE_NUM,
  PL1.PURCHASE_BASIS "TYPE",
  I.SEGMENT1 "ITEM",
  PL1.ITEM_REVISION,
  C.SEGMENT1||C.SEGMENT1 "CATEGORY",
  PL1.ITEM_DESCRIPTION, 
  PL1.QUANTITY,
  PL.UNIT_MEAS_LOOKUP_CODE,
  PL1.UNIT_PRICE, 
  PL.NEED_BY_DATE,
  PL.SHIPMENT_TYPE,
  PL.SHIPMENT_NUM,
  M.ORGANIZATION_CODE "SHP_ORG_CODE",
  LOC1.LOCATION_CODE "SHIPMENT_LOC",
  PL.UNIT_MEAS_LOOKUP_CODE "SHP_UOM",
  PL.QUANTITY "SHP_QUANTITY",
  (PL.QUANTITY*PL.PRICE_OVERRIDE) "SHP_AMOUNT", 
  D.DISTRIBUTION_NUM,
  D.DESTINATION_TYPE_CODE, 
  GL.CONCATENATED_SEGMENTS,
  D.QUANTITY_ORDERED "DIS_QUANTITY",
  P.FULL_NAME "REQUESTER NAME",
  I.INVENTORY_ITEM_ID,
  C.CATEGORY_ID
FROM
  --PO_HEADERS
  HR_ORGANIZATION_UNITS O,
  po_headers_all PH,
  PO_VENDORS V,
  PO_VENDOR_SITES_ALL PV,
  HR_LOCATIONS_ALL_TL LOC1,
  HR_LOCATIONS_ALL_TL LOC2,
  --PO_LINES
  po_line_locations_all PL,
  PO_LINES_ALL PL1,
  MTL_SYSTEM_ITEMS_B I,
  MTL_CATEGORIES_B C,
  MTL_PARAMETERS M,
  PO_DISTRIBUTIONS_ALL D,
  GL_CODE_COMBINATIONS_KFV GL,
  PER_ALL_PEOPLE_F P
WHERE PH.VENDOR_SITE_ID=PV.VENDOR_SITE_ID 
  AND PH.SHIP_TO_LOCATION_ID=PV.SHIP_TO_LOCATION_ID 
  AND PH.VENDOR_ID=V.VENDOR_ID
  AND LOC1.LOCATION_ID=PH.SHIP_TO_LOCATION_ID
  AND LOC2.LOCATION_ID=PV.BILL_TO_LOCATION_ID 
  AND PH.PO_HEADER_ID=PL1.PO_HEADER_ID
    --LINES 
  AND PL1.ITEM_ID=I.INVENTORY_ITEM_ID
  AND PL.PO_HEADER_ID=PL1.PO_HEADER_ID
  AND PL.PO_LINE_ID=PL1.PO_LINE_ID
  AND PL1.CATEGORY_ID=C.CATEGORY_ID
  --ORG_CODE
  AND M.ORGANIZATION_ID=PL.SHIP_TO_ORGANIZATION_ID
  --AND O.ORGANIZATION_ID=M.ORGANIZATION_ID
  --AND C.ORGANIZATION_ID=M.ORGANIZATION_ID
  AND O.ORGANIZATION_ID=PH.ORG_ID
  --DISTRIBTION
  AND D.PO_HEADER_ID=PL.PO_HEADER_ID
  AND D.PO_LINE_ID=PL.PO_LINE_ID
  AND D.LINE_LOCATION_ID=PL.LINE_LOCATION_ID
  AND GL.CODE_COMBINATION_ID=D.CODE_COMBINATION_ID
  AND D.DELIVER_TO_PERSON_ID=P.PERSON_ID
  AND PH.SEGMENT1 like 'PO_NUM%'
  AND O.NAME LIKE 'Vision%';

PO_DISTRIBUTIONS 2 HRMS AND GL

 


PO_DISTRIBUTIONS_ALL

SELECT
PDA.PO_DISTRIBUTION_ID,
PDA.DISTRIBUTION_NUM             "DISTRIBUTION NUM",
PDA.DESTINATION_TYPE_CODE      "DESTINATION TYPE",
PAPF.FULL_NAME              "DELIVER TO PERSON OR REQUESTOR",
HL.LOCATION_CODE              "DELIVER TO LOCATION",
PDA.DESTINATION_SUBINVENTORY        "DESTINATION SUBINVENTORY",
PDA.QUANTITY_ORDERED          "QUANTITY_ORDERED",
GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5             "CHARGE_ACCOUNT"
FROM
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA,
PER_ALL_PEOPLE_F PAPF,
HR_LOCATIONS HL,
GL_CODE_COMBINATIONS GCC
WHERE
PDA.LINE_LOCATION_ID = '72079'                 AND
PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID         AND
PAPF.PERSON_ID = PDA.DELIVER_TO_PERSON_ID         AND
HL.LOCATION_ID =PDA.DELIVER_TO_LOCATION_ID         AND

PO_LINE_LOCATIONS 2 HRMS

PO_LINE_LOCATIONS_ALL

SELECT
     PLLA.LINE_LOCATION_ID,
     PLLA.SHIPMENT_NUM                   "SHIPMENT NUM",
     OOD.ORGANIZATION_CODE                  "SHIP TO ORG CODE",
     HL.LOCATION_CODE                       "SHIP TO LOCATION CODE",
     PLLA.UNIT_MEAS_LOOKUP_CODE              "UOM",
     PLLA.QUANTITY                        "QUANTITY",
     PLLA.PROMISED_DATE                    "PROMISED DATE",
     PLLA.NEED_BY_DATE                        "NEED BY DATE"
FROM
     PO_LINES_ALL PLA,
     PO_LINE_LOCATIONS_ALL PLLA,
     ORG_ORGANIZATION_DEFINITIONS OOD,
     HR_LOCATIONS HL
WHERE
     PLA.PO_LINE_ID = '37703'                       AND
     PLA.PO_LINE_ID = PLLA.PO_LINE_ID                    AND
     PLLA.SHIP_TO_ORGANIZATION_ID = OOD.ORGANIZATION_ID AND
     PLLA.SHIP_TO_LOCATION_ID = HL.LOCATION_ID
 

PO_LINES 2 MTL -- INVENTORY

PO_LINES_ALL 

SELECT
    PLA.PO_LINE_ID,
    PLA.LINE_NUM                     "LINE NUM",
    MS.SEGMENT1                       "ITEM NAME",
    PLA.ITEM_REVISION                  "ITEM REVISION",
    MC.SEGMENT1                       "ITEM CATEGORY",
    PLA.ITEM_DESCRIPTION                "ITEM DESCRIPTION",
    PLA.UNIT_MEAS_LOOKUP_CODE          "UOM",
    PLA.UNIT_PRICE * PLA.QUANTITY          "AMOUNT"
FROM
    PO_HEADERS_ALL PHA,
    PO_LINES_ALL PLA,
    MTL_SYSTEM_ITEMS_B MS,
    MTL_CATEGORIES_B MC
WHERE
    MS.ORGANIZATION_ID = '204'                   AND
    PHA.PO_HEADER_ID = '32495'                   AND
    PHA.PO_HEADER_ID = PLA.PO_HEADER_ID           AND
    PLA.ITEM_ID = MS.INVENTORY_ITEM_ID          AND
    PLA.CATEGORY_ID = MC.CATEGORY_ID
 

PO_HEADERS 2 HRMS

PO_HEADERS_ALL

SELECT
    PHA.PO_HEADER_ID,
    PHA.SEGMENT1                     "PO NUM",
    PHA.TYPE_LOOKUP_CODE                 "TYPE",
    TO_CHAR(PHA.CREATION_DATE,'DD/MON/YY')            "PO DATE",
    PV.VENDOR_NAME                              "SUPPLIER NAME",
    PVSA.VENDOR_SITE_CODE                   "SUPPLIER SITE",
    PVC.FIRST_NAME                          "SUPPLIER CONTACT",
    HLA1.LOCATION_CODE                "SHIP TO LOCATION",
    HLA2.LOCATION_CODE                "BILL TO LOCATION",
    PAPF.FULL_NAME                    "BUYER",
    PHA.AUTHORIZATION_STATUS            "STATUS",
    PHA.COMMENTS                    "DESCRIPTION"
FROM
    PO_HEADERS_ALL PHA,
    PO_VENDORS PV,
    PO_VENDOR_SITES_ALL PVSA,
    PO_VENDOR_CONTACTS PVC,
    HR_LOCATIONS HLA1,
    HR_LOCATIONS HLA2,
    PER_ALL_PEOPLE_F PAPF
WHERE
    PHA.SEGMENT1 = 4420                               AND
    PHA.VENDOR_ID = PV.VENDOR_ID                  AND
    PHA.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID           AND
    PHA.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID      AND
    PHA.SHIP_TO_LOCATION_ID = HLA1.LOCATION_ID            AND
    PHA.BILL_TO_LOCATION_ID = HLA2.LOCATION_ID            AND
    PHA.AGENT_ID = PAPF.PERSON_ID