GMF OPM Lot Cost Detail

Description
Categories: BI Publisher
Columns: Item Number, Organization Id, Org Code, Item Desc, Lot Number, Lot Desc, Cost Level, Component Code, Analysis Code, Component Cost ...
Application: Process Manufacturing Financials
Source: OPM Lot Cost Detail Report (XML)
Short Name: GMFLCDTL_XML
DB package: GMF_GMFLCDTL_XMLP_PKG
SELECT item_number, 
        organization_id,
        organization_code,
        item_desc, 
        lot_number, 
        lot_desc, 
        cost_level, 
        component_code, 
        analysis_code,
        component_cost, 
        uom, 
        sequence,
        component_id,
        component_desc,
        test
FROM
(SELECT DISTINCT iim.item_number item_number,
        lc.organization_id,
        mp.organization_code,
        iim.DESCRIPTION item_desc,
        lm.lot_number lot_number,
        lm.DESCRIPTION lot_Desc,
        decode(lcd.cost_level,0,'Unit Cost',1,'Lower Level Cost','None') cost_level,
        cmm.cost_cmpntcls_code component_code,
        lcd.cost_analysis_code analysis_code,
        lcd.component_cost * :p_exchange_rate component_cost,
        iim.primary_uom_code uom,
        cmm.sort_sequence sequence,
        cmm.cost_cmpntcls_id component_id,
        cmm.cost_cmpntcls_desc component_desc,
        decode(nvl(lc.final_cost_flag,0), 1, null, 0, '***') test,
        rank() over (Partition by lc.inventory_item_id, lc.organization_id, lc.lot_number, lc.cost_mthd_code
        order by lc.cost_date desc, lc.header_id desc) lot_cost_rank
 FROM   mtl_item_flexfields iim,
        gmf_lot_costs lc,
        gmf_lot_cost_details lcd,
        cm_cmpt_mst cmm,
        mtl_lot_numbers lm,
        mtl_parameters mp,
        hr_organization_information hoi,
        mtl_categories_kfv mckfv,
        mtl_item_categories mic,
       gmd_org_access_vw oa
  WHERE
       lc.cost_type_id = :p_cost_type_id 
   AND  hoi.org_information2 = :p_legal_entity_id
   AND  iim.inventory_item_id = lm.inventory_item_id 
   AND  iim.organization_id = lc.organization_id
   AND  lm.organization_id = lc.organization_id                     
   AND  lm.lot_number = lc.lot_number
   AND  iim.inventory_item_id = lc.inventory_item_id
   AND  cmm.cost_cmpntcls_id = lcd.cost_cmpntcls_id
   AND  lcd.delete_mark = 0
   AND  lc.delete_mark = 0
   AND  lc.header_id = lcd.header_id
   AND  hoi.org_information_context = 'Accounting Information'
   AND  hoi.organization_id = lc.organization_id
   AND  mp.organization_id = lc.organization_id
   AND mic.inventory_item_id(+) = iim.inventory_item_id 
   AND mic.organization_id(+) = iim.organization_id 
   AND mic.category_id = mckfv.category_id(+)
   and mckfv.structure_id(+) = :p_structure_id
   AND iim.organization_id = oa.organization_id
        &organizationcp
        &itemcp
        &CostCategoryCP
        &LotCP
ORDER BY 1,2,4,9,5,6)
WHERE lot_cost_rank = 1
Parameter Name SQL text Validation
Legal Entity
 
LOV Oracle
Cost Type
 
LOV Oracle
From Organization
 
LOV Oracle
To Organization
 
LOV Oracle
P_FROM_ITEM
 
P_TO_ITEM
 
From Lot
 
LOV Oracle
To Lot
 
LOV Oracle
From Item Cost Category
 
To Item Cost Category
 
Currency
 
LOV Oracle
Exchange Rate
 
Number
Dummy Structure ID
 
LOV Oracle
Organization Context
 
Number
Non Block sql
 
LOV Oracle
Sy All