GMF Item Costs

select
'To be Done' a$master_organization_name, -- need to check if this should be concatenated
ood.organization_name a$organization_name, -- need to check if this should be concatenated
decode(costs.burden_ind,0,'N',1,'Y') burden_flag,
pstat.calendar_code calendar_code,
clhdl.calendar_desc calendar_description,
costs.cost_analysis_code cost_analysis_code,
codes.cost_analysis_desc cost_analysis_description,
typel.cost_cmpntcls_desc cost_component_class,
type.cost_cmpntcls_code cost_component_class_code,
decode(costs.rollover_ind,0,'N','Y') cost_frozen_flag,
decode (costs.cost_level,0,'This Level','Lower Level') cost_level,
mthd.cost_mthd_code cost_type_code,
mthd.cost_mthd_desc cost_type_description,
fpolc.base_currency_code currency_code_base,
mstk1.inventory_item_id item$inventory_item_id,
mstk1.organization_id item$organization_id,
mstk1.segment1 item$sv$item,
costs.cmpnt_cost item_cost,
decode(costs.delete_mark,0,'N',1,'Y') item_cost_mfd_flag,
pitml.description item_description,
pitml.long_description item_long_description,
pitem.primary_uom_code item_uom,
decode(costs.organization_id,'1002','305','1003','241','1004','242','1005',
'243','1006','244','1007','245','1017','249','1262','043','1263','045','1264',
'044','1265','042','1266','041','1282','213','1283','212','1284','313','1285',
'206','1286','306','1302','901','1303','902','1362','101','1382','261','1402',
'308','1422','215','1442','063','1462','263','1463','264','1464','265','1465',
'266','1482','903','1502','064','1522','216','1523','217','1524','218','1525',
'272','1526','309','1527','904','1542','273','1562','251','1563','252','1602',
'310','1603','311','1622','219','163','401','1642','274','1643','275','1662',
'277','1682','005','1683','361','1702','278','382','000','383','001','384',
'302'
,'468','301','469','303','748','304',costs.organization_id) organization_code,
pstat.period_code period_code,
cldtl.period_desc period_description,
cldtl.end_date period_end_date,
cldtl.start_date period_start_date,
decode(type.usage_ind,'1','Material','2','Overhead','3'
,'Resource','4','Expense Alloc','5','Std Cost Adj',type.usage_ind) usage_type
from
gmf_fiscal_policies fpolc,
cm_cldr_hdr_tl clhdl,
cm_cldr_dtl cldtl,
gmf_period_statuses pstat,
cm_mthd_mst mthd,
cm_cmpt_mst_tl typel,
cm_cmpt_mst_b type,
cm_alys_mst codes,
--noetix_sys.xxk_sys_item mstk1,
mtl_system_items_b mstk1,
mtl_system_items_tl pitml,
mtl_system_items_b pitem,
cm_cmpt_dtl costs,
--noetix_sys.gmfg0_orgn_acl_map_base xmap,
org_organization_definitions ood,
hr_all_organization_units haou,
hr_organization_information hoi
where 
2=2 and
hoi.org_information_context= 'Accounting Information' and
hoi.organization_id=ood.organization_id and
haou.organization_id=to_number(hoi.org_information3) and 
costs.organization_id=ood.organization_id
and pitem.inventory_item_id=costs.inventory_item_id
and pitem.organization_id=costs.organization_id
and pitml.inventory_item_id (+)=pitem.inventory_item_id
and pitml.organization_id (+)=pitem.organization_id
and pitml.language (+) like noetix_env_pkg.get_language
and pstat.period_id=costs.period_id
and cldtl.calendar_code=pstat.calendar_code
and cldtl.period_code=pstat.period_code
and clhdl.calendar_code=cldtl.calendar_code
and clhdl.language (+) like noetix_env_pkg.get_language
and mthd.cost_type_id=costs.cost_type_id
and type.cost_cmpntcls_id=costs.cost_cmpntcls_id
and typel.cost_cmpntcls_id (+)=type.cost_cmpntcls_id
and typel.language (+) like noetix_env_pkg.get_language
and codes.cost_analysis_code=costs.cost_analysis_code
and fpolc.legal_entity_id (+)=pstat.legal_entity_id
and pitem.inventory_item_id=mstk1.inventory_item_id
and pitem.organization_id=mstk1.organization_id

select 'To be Done' a$master_organization_name, -- need to check if this should be concatenated ood.organization_name a$organization_name, -- need to check if this should be concatenated decode(costs.burden_ind,0,'N',1,'Y') burden_flag, pstat.calendar_code calendar_code, clhdl.calendar_desc calendar_description, costs.cost_analysis_code cost_analysis_code, codes.cost_analysis_desc cost_analysis_description, typel.cost_cmpntcls_desc cost_component_class, type.cost_cmpntcls_code cost_component_class_code, decode(costs.rollover_ind,0,'N','Y') cost_frozen_flag, decode (costs.cost_level,0,'This Level','Lower Level') cost_level, mthd.cost_mthd_code cost_type_code, mthd.cost_mthd_desc cost_type_description, fpolc.base_currency_code currency_code_base, mstk1.inventory_item_id item$inventory_item_id, mstk1.organization_id item$organization_id, mstk1.segment1 item$sv$item, costs.cmpnt_cost item_cost, decode(costs.delete_mark,0,'N',1,'Y') item_cost_mfd_flag, pitml.description item_description, pitml.long_description item_long_description, pitem.primary_uom_code item_uom, decode(costs.organization_id,'1002','305','1003','241','1004','242','1005', '243','1006','244','1007','245','1017','249','1262','043','1263','045','1264', '044','1265','042','1266','041','1282','213','1283','212','1284','313','1285', '206','1286','306','1302','901','1303','902','1362','101','1382','261','1402', '308','1422','215','1442','063','1462','263','1463','264','1464','265','1465', '266','1482','903','1502','064','1522','216','1523','217','1524','218','1525', '272','1526','309','1527','904','1542','273','1562','251','1563','252','1602', '310','1603','311','1622','219','163','401','1642','274','1643','275','1662', '277','1682','005','1683','361','1702','278','382','000','383','001','384', '302' ,'468','301','469','303','748','304',costs.organization_id) organization_code, pstat.period_code period_code, cldtl.period_desc period_description, cldtl.end_date period_end_date, cldtl.start_date period_start_date, decode(type.usage_ind,'1','Material','2','Overhead','3' ,'Resource','4','Expense Alloc','5','Std Cost Adj',type.usage_ind) usage_type from gmf_fiscal_policies fpolc, cm_cldr_hdr_tl clhdl, cm_cldr_dtl cldtl, gmf_period_statuses pstat, cm_mthd_mst mthd, cm_cmpt_mst_tl typel, cm_cmpt_mst_b type, cm_alys_mst codes, --noetix_sys.xxk_sys_item mstk1, mtl_system_items_b mstk1, mtl_system_items_tl pitml, mtl_system_items_b pitem, cm_cmpt_dtl costs, --noetix_sys.gmfg0_orgn_acl_map_base xmap, org_organization_definitions ood, hr_all_organization_units haou, hr_organization_information hoi where 2=2 and hoi.org_information_context= 'Accounting Information' and hoi.organization_id=ood.organization_id and haou.organization_id=to_number(hoi.org_information3) and costs.organization_id=ood.organization_id and pitem.inventory_item_id=costs.inventory_item_id and pitem.organization_id=costs.organization_id and pitml.inventory_item_id (+)=pitem.inventory_item_id and pitml.organization_id (+)=pitem.organization_id and pitml.language (+) like noetix_env_pkg.get_language and pstat.period_id=costs.period_id and cldtl.calendar_code=pstat.calendar_code and cldtl.period_code=pstat.period_code and clhdl.calendar_code=cldtl.calendar_code and clhdl.language (+) like noetix_env_pkg.get_language and mthd.cost_type_id=costs.cost_type_id and type.cost_cmpntcls_id=costs.cost_cmpntcls_id and typel.cost_cmpntcls_id (+)=type.cost_cmpntcls_id and typel.language (+) like noetix_env_pkg.get_language and codes.cost_analysis_code=costs.cost_analysis_code and fpolc.legal_entity_id (+)=pstat.legal_entity_id and pitem.inventory_item_id=mstk1.inventory_item_id and pitem.organization_id=mstk1.organization_id