GMF OPM Lot Cost Detail
Description
Categories: BI Publisher
Imported from BI Publisher
Description: OPM Lot Cost Detail Report
Application: Process Manufacturing Financials
Source: OPM Lot Cost Detail Report (XML)
Short Name: GMFLCDTL_XML
DB package: GMF_GMFLCDTL_XMLP_PKG
Description: OPM Lot Cost Detail Report
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 |
|
Char | |
P_TO_ITEM |
|
Char | |
From Lot |
|
LOV Oracle | |
To Lot |
|
LOV Oracle | |
From Item Cost Category |
|
Char | |
To Item Cost Category |
|
Char | |
Currency |
|
LOV Oracle | |
Exchange Rate |
|
Number |