ENGINATICSSYSTEM_ADMINISTRATORSYSADMIN92C63F4C20A21E85E053BB6B63585CC8CST Cost TypeYselect
cct.cost_type value,
cct.description
from
cst_cost_types cct
where
nvl(cct.disable_date,sysdate)>=sysdate and
(cct.organization_id is null or
cct.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
)
order by
cct.cost_typeUSEF4BB9F32810B1A4E0530100007F390CCST Item Cost Upload - Basis Type LOVYselect
ml.lookup_code id,
ml.meaning value,
nvl(ml.description,ml.meaning) || decode(ml.lookup_code,br.default_basis_type,' (Default)','') description
from
mtl_parameters mp,
cst_cost_elements cce,
bom_resources br,
mfg_lookups ml
where
mp.organization_code = :$column$.organization_code and
cce.cost_element = :$column$.cost_element and
br.organization_id = mp.organization_id and
br.cost_element_id = cce.cost_element_id and
br.resource_code = :$column$.sub_element and
ml.lookup_type = 'CST_BASIS_SHORT' and
( (cce.cost_element_id in (1,3,4,5,6) and ml.lookup_code in (1,2)) or
(cce.cost_element_id = 2 and ml.lookup_code between 1 and 6)
)
order by
ml.meaning
US089F6C142DA509B9E0630100007FC56CCST Make or BuyYselect
flvv.meaning value,
null description
from
fnd_lookup_values_vl flvv
where
flvv.lookup_type='MTL_PLANNING_MAKE_BUY' and
flvv.view_application_id=700 and
flvv.security_group_id=0 and
flvv.enabled_flag='Y'
order by
flvv.meaningUS8E2FF36EDF2E79D2E0530100007F1FF2INV Category (restricted)Yselect
mck.concatenated_segments value,
mct.description
from
mtl_category_sets_v mcsv,
mtl_categories_kfv mck,
mtl_categories_tl mct
where
mcsv.category_set_name=:$flex$.category_set and
mcsv.structure_id=mck.structure_id and
(mcsv.category_set_id, mck.category_id) in (select mic.category_set_id, mic.category_id from mtl_item_categories mic where mic.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')) and
mck.category_id=mct.category_id and
mct.language=userenv('lang')
order by
mck.concatenated_segmentsARفئة عنصر المخزون ، مقيدة بمجموعة الفئة المحددة السابقة والفئات التي تحتوي على عنصر واحد على الأقل معين في MFG_ORGANIZATION_ID الحاليDKategorie des Inventarartikels, eingeschränkt auf den zuvor ausgewählten Kategoriensatz und auf Kategorien, denen mindestens ein Artikel in der aktuellen MFG_ORGANIZATION_ID zugeordnet istECategoría de artículo de inventario, restringida al conjunto de categorías seleccionadas anteriormente y a las categorías que tienen al menos un artículo asignado en el MFG_ORGANIZATION_ID actualFCatégorie d'article d'inventaire, limitée à l'ensemble des catégories sélectionnées précédemment et aux catégories auxquelles au moins un article est attribué dans l'ID_ORGANISATION_FMG actuelleICategoria di articolo dell'inventario, limitata al precedente set di categorie selezionato e alle categorie che hanno almeno un articolo assegnato nel MFG_ORGANIZATION_ID correnteJAインベントリ項目カテゴリ、前に選択されたカテゴリセットに制限され、現在のMFG_ORGANIZATION_IDに少なくとも1つの項目が割り当てられているカテゴリに制限されています。KO재고 품목 범주, 이전에 선택한 범주 세트 및 현재 MFG_ORGANIZATION_ID에 할당 된 품목이 하나 이상있는 범주로 제한됨PTBCategoria de item de inventário, restrita ao conjunto de categorias selecionadas anteriormente e às categorias que têm pelo menos um item atribuído no MFG_ORGANIZATION_ID atualRUКатегория инвентаризационных элементов, ограниченная ранее выбранной категорией и категориями, которым в текущем идентификаторе MFG_ORGANIZATION_ID присвоен хотя бы один элемент.SLagerkategori, begränsad till tidigare vald kategorisats och till kategorier som har åtminstone ett objekt tilldelat i den aktuella MFG_ORGANIZATION_IDTREnvanter öğesi kategorisi, önceki seçilen kategori grubuyla ve geçerli MFG_ORGANIZATION_ID'de atanmış en az bir öğeye sahip kategorilerle sınırlıdırUSInventory item category, restricted to previous selected category set and to categories which have at least one item assigned in the current MFG_ORGANIZATION_IDZHS库存项目类别，仅限于以前选定的类别集和在当前MFG_ORGANIZATION_ID中至少有一个项目的类别。8E2FF36EDF2D79D2E0530100007F1FF2INV Category Set (restricted)Yselect
mcsv.category_set_name value,
mcsv.description
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_id in (select mic.category_set_id from mtl_item_categories mic where mic.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID'))
order by
mcsv.category_set_nameUSCategory sets having at least one assigned inv itemsEC5CECA520510FC8E0530100007F5EB3INV Item Number (costing enabled only items)YYselect distinct
msiv.concatenated_segments value,
msiv.description
from
mtl_system_items_vl msiv,
mtl_parameters mp
where
msiv.organization_id = mp.organization_id and
(xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or
(:$flex$.organization_code is null and
mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
)
) and
(msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y')
order by
msiv.concatenated_segmentsUS8E2FF36EDE9C79D2E0530100007F1FF2INV Item StatusYselect
misv.inventory_item_status_code_tl value,
misv.description
from
mtl_item_status_vl misv
order by
misv.inventory_item_status_code_tlUS8E2FF36EDE8579D2E0530100007F1FF2INV Item TypeYselect
flvv.meaning value,
flvv.lookup_code description
from
fnd_lookup_values_vl flvv
where
flvv.enabled_flag='Y' and
flvv.lookup_type='ITEM_TYPE' and
flvv.view_application_id=3 and
flvv.security_group_id=0
order by
flvv.lookup_codeUS8E2FF36EDEB279D2E0530100007F1FF2INV Organization CodeYselect
ood.organization_code value,
ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description
from
org_organization_definitions ood,
mtl_parameters mp,
mtl_parameters mp0,
hr_all_organization_units haou,
hr_locations_all hla,
fnd_territories_vl ftv
where
ood.organization_id=mp.organization_id and
mp.organization_id<>mp.master_organization_id and
(
ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or
not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
ood.operating_unit in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
) and
(:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and
(:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and
nvl(ood.disable_date,sysdate)>=sysdate and
mp.master_organization_id=mp0.organization_id and
ood.organization_id=haou.organization_id(+) and
haou.location_id=hla.location_id(+) and
hla.country=ftv.territory_code(+)
order by
ood.organization_codeARجميع أكواد تنظيم المخزون النشط باستثناء المؤسسات الرئيسية ، مقيدة بعرض الوصول إلى مؤسسة المستخدم الحاليDAlle aktiven Inventar-Organisationscodes mit Ausnahme von Master-Organisationen, beschränkt auf die Org-Zugriffsansicht des aktuellen BenutzersETodos los códigos de organización de inventario activos, excluyendo las organizaciones maestras, restringidos a la vista de acceso a la organización del usuario actualFTous les codes d'organisation de l'inventaire actif, à l'exclusion des organisations maîtresses, limités à la vue d'accès à l'organisation de l'utilisateur actuelITutti i codici di organizzazione dell'inventario attivi, escluse le organizzazioni master, limitati alla vista di accesso all'org dell'utente correnteJAすべてのアクティブなインベントリの組織コードは、マスター組織を除いて、現在のユーザーの組織のアクセスビューに制限されます。KO현재 사용자의 조직 액세스보기로 제한되는 마스터 조직을 제외한 모든 활성 재고 조직 코드PTBTodos os códigos ativos de organização de inventário, excluindo organizações mestre, restritos à visão de acesso org do usuário atualRUВсе активные инвентаризационные организационные коды, исключая основные организации, ограниченные текущим представлением доступа пользователя к оргструктуре.SAlla aktiva lagerorganisationskoder exklusive huvudorganisationer, begränsade till den aktuella användarens organisationsåtkomstvyTRAna organizasyonlar hariç tüm aktif envanter organizasyon kodları, mevcut kullanıcının organizasyon erişim görünümü ile sınırlıdırUSAll active inventory organization codes excluding master organizations, restricted to the current user's org access viewZHS所有活动的清单组织代码，不包括主组织，仅限于当前用户的组织访问视图。8E2FF36EDEA679D2E0530100007F1FF2YesYselect 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dualUSF30AA5A78BFB75AEE0530100007F31BFselect
case when nvl2(:p_autopopulate_upload_status,'Y','N') = 'Y' then xxen_upload.action_meaning(xxen_upload.action_update) else null end action_,
case when nvl2(:p_autopopulate_upload_status,'Y','N') = 'Y' then xxen_upload.status_meaning(xxen_upload.status_new) else null end status_,
case when nvl2(:p_autopopulate_upload_status,'Y','N') = 'Y' then xxen_util.description('U_EXCEL_MSG_VALIDATION_PENDING','XXEN_REPORT_TRANSLATIONS',0) else null end message_,
to_number(null) request_id_,
null modified_columns_,
null row_id,
-- Item
cct_t.cost_type target_cost_type,
mp.organization_code organization_code,
msiv.concatenated_segments item,
msiv.description item_description,
(select misv.inventory_item_status_code_tl from mtl_item_status_vl misv where misv.inventory_item_status_code = msiv.inventory_item_status_code) item_status,
xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_or_buy,
xxen_util.meaning(cic.inventory_asset_flag,'SYS_YES_NO',700) inventory_asset,
xxen_util.meaning(cic.based_on_rollup_flag,'CST_BONROLLUP_VAL',700) based_on_rollup,
coalesce(cic.lot_size,msiv.std_lot_size,1) lot_size,
coalesce(cic.shrinkage_rate,msiv.shrinkage_rate,0) shrinkage_rate,
-- Item Cost Element Details
cdcv.cost_source_type,
cdcv.cost_element,
cdcv.resource_code sub_element,
cdcv.usage_rate_or_amount,
cdcv.basis,
cdcv.activity,
cdcv.activity_units,
cdcv.item_units,
-- other enterable fields
cdcv.operation_seq_num,
cdcv.department,
-- display only - calculated by Item Cost Import
cdcv.basis_factor,
cdcv.net_yield_or_shrinkage_factor,
cdcv.resource_rate,
cdcv.item_cost element_unit_cost,
-- Item Cost Summary Amounts
cic.material_cost item_material_cost,
cic.material_overhead_cost item_material_overhead_cost,
cic.resource_cost item_resource_cost,
cic.outside_processing_cost item_outside_processing_cost,
cic.overhead_cost item_overhead_cost,
cic.item_cost item_cost,
cic.unburdened_cost item_unburdened_cost,
cic.burden_cost item_burden_cost,
to_number(null) group_id,
1 level_type,
1 rollup_source_type,
1 process_flag,
:p_upload_mode upload_mode,
:p_do_cost_rollup rollup_costs,
:p_rollup_range rollup_type,
:p_rollup_option rollup_option,
:p_rollup_inc_unimp_ecn_flag rollup_unimpl_ecos
--
from
cst_cost_types cct_s,
cst_cost_types cct_t,
cst_item_costs cic,
cst_detail_cost_view cdcv,
mtl_system_items_vl msiv,
mtl_parameters mp
where
1=1 and
:p_upload_mode = :p_upload_mode and
mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
--
cct_s.cost_type = :p_source_cost_type and
cct_t.cost_type = :p_target_cost_type and
--
cct_s.cost_type_id = cic.cost_type_id and
cic.cost_type_id = cdcv.cost_type_id (+) and
cic.organization_id = cdcv.organization_id (+) and
cic.inventory_item_id = cdcv.inventory_item_id (+) and
cic.organization_id = mp.organization_id and
cic.organization_id = msiv.organization_id and
cic.inventory_item_id = msiv.inventory_item_id and
(nvl(:p_exclude_rolled_up_items,'N')<>'Y' or cic.based_on_rollup_flag<>1) and
(cdcv.cost_source_type = xxen_util.meaning('1','CST_SOURCE_TYPE',700) or
(nvl(:p_exclude_no_cost_iterms,'N') != 'Y' and
not exists
(select
null
from
cst_detail_cost_view cdcv2
where
cdcv2.cost_type_id = cic.cost_type_id and
cdcv2.organization_id = cic.organization_id and
cdcv2.inventory_item_id = cic.inventory_item_id
)
)
)Added Exclude Rolled Up Items parameter to filter out items with based_on_rollup_flagYU:Rollup_Costs is null or (:Rollup_Costs is not null and :Rollup_Type is not null and :Rollup_Option is not null)Axxen_cst_upload.insert_cost_interfacexxen_cst_upload.submit_cost_importUSCST Item Standard Cost UploadCST Item Standard Cost Upload
=============================
This upload can be used to
- Upload New Item Costs
- Download the current Item Costs, Update, and Upload the amended item costs for a specified Cost Type
- Download the current item Costs from one Source cost type, update, and upload the amended item costs to a different Target Cost Type
NOTE:
You can only upload costs to cost types that are not flagged as frozen and are flagged as updateable. Typically, this upload is used for updating cost types using the standard costing method.
Optionally, the upload can perform a Cost Rollup after the Item Costs have been imported. The Cost Rollup can be performed for
- Specific Items - a cost rollup will be done for only those items for which costs have been uploaded
- All Items - in this mode the upload will submit the 'Supply Chain Cost Rollup - Print Report' concurrent request to rollup all items within each organization costs are uploaded to
NOTE:
The Item Cost Interface only support importing ‘This Level’ costs. As such, this upload does not support the direct upload of rolled up costs
Parameters
==========
Target Cost Type (Required) - The Cost Type to which the Item Costs are to be uploaded to.
Mode (Required, Default: Remove and replace cost information) -
The Item Cost Update Mode
- Remove and replace cost information
- Insert new Cost Information Only
Auto Populate Upload Columns (Default: Yes) -
Applies to downloaded Item Cost Entries only. If set to Yes, the downloaded records will be flagged ready for upload, even if though no changes have been made.
If left blank, the downloaded records will only be flagged for upload when the user amends any data against a record.
In this case, it is important to remember where an Item Cost has multiple cost elements, then all the cost element records for that item would need to be amended to flag them for update. Only records flagged for update are uploaded. If all cost elements for an item are to be uploaded, even if some do not require any amendment, then the user must trigger the record to be uploaded by making a change against the record.
Source Cost Type
Optionally specify the source Cost Type from which to download the current Item Costs. For the scenarios where you want to amend the current items costs for a Cost Type or want to copy the current item costs for a Cost Type to a different Target Cost Type.
Rollup Costs - set to Yes to do a cost rollup after the costs are uploaded
Rollup Type - Rollup specific Items or All Items
Rollup Option - Single level rollup or Full cost rollup
Organization Code - Optionally select the Organization(s) for which you want to download the Item Costs for
Additional Parameters - The additional parameters are used to optionally restrict the items to be downloaded.
Notes on the Upload
===================
The generated upload Excel contains one record per elemental item cost.
The following columns in the Excel are display only and are populated initially on download, and then refreshed in the results excel after the upload is performed as they calculated during the Item Cost Import
- Basis Factor
- Net Yield or Shrinkage Factor
- Element Unit Cost
- Item Material Cost - the summary Item Level Material Cost.
- Item Material Overhead Cost - the summary Item Level Material Overhead Cost.
- Item Resource Cost - the Summary Item Level Resource Cost.
- Item Outside Processing Cost - the summary Item Level Outside Processing Cost.
- Item Overhead Cost - the summary Item Level Overhead Cost.
- Item Cost - the summary Item Level Item Cost.
- Item Unburdened Cost - the summary Item Level Unburdened Cost.
- Item Burden Cost - the summary Item Level Burden Cost.
Rollup Type and Rollup Option must be specified in order to Rollup CostsEnginaticsR12 onlyUpload1=1:p_autopopulate_upload_status:p_do_cost_rollup:p_exclude_no_cost_iterms:p_exclude_rolled_up_items:p_rollup_inc_unimp_ecn_flag:p_rollup_option:p_rollup_range:p_source_cost_type:p_target_cost_type:p_upload_mode1-10:p_rollup_inc_unimp_ecn_flagLOV customYselect
lookup_code id,
meaning value,
null description
from
mfg_lookups
where
lookup_type='SYS_YES_NO' and
:$flex$.Rollup_Costs = 'Y'
order by value2USRollup Unimplemented ECOs210:p_target_cost_typeLOV customYselect
cct.cost_type value,
cct.description description
from
cst_cost_types cct
where
nvl(cct.disable_date,sysdate+1)>sysdate and
cct.costing_method_type = 1 and
cct.allow_updates_flag=1 and
cct.frozen_standard_flag!=1 and
(cct.organization_id is null or
cct.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
)
order by cost_typeYUSTarget Cost Type320:p_upload_modeLOV OracleCST_SRS_RUN_OPTYselect
lookup_code id,
meaning value,
null description
from
mfg_lookups
where lookup_type='CST_RUN_OPTION'
order by value,description2YUSMode430:p_autopopulate_upload_statusLOVYes8E2FF36EDEA679D2E0530100007F1FF2Yselect 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dualYUSAuto Populate Upload Columns540:p_source_cost_typeLOVCST Cost Type92C63F4C20A21E85E053BB6B63585CC8Yselect
cct.cost_type value,
cct.description
from
cst_cost_types cct
where
nvl(cct.disable_date,sysdate)>=sysdate and
(cct.organization_id is null or
cct.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
)
order by
cct.cost_typeUSSource Cost Type650:p_do_cost_rollupLOVYes8E2FF36EDEA679D2E0530100007F1FF2Yselect 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dualUSRollup Costs?When set to Yes, a cost rollup will be performed after the item costs are imported.760:p_rollup_rangeLOV customYselect
lookup_code id,
meaning value,
case lookup_code
when 1 then 'All Items'
when 2 then 'Updated Items Only'
end description
from
mfg_lookups
where lookup_type='CST_ITEM_RANGE'
and lookup_code in (1,2) and
:$flex$.Rollup_Costs = 'Y'
order by value2USRollup TypeAll Items: a Cost Rollup concurrent request will be submitted for all items in the organization. Specific Item: a Cost Rollup will be performed only for those items for which items costs are uploaded.870:p_rollup_optionLOV customYselect
lookup_code id,
meaning value,
null description
from
mfg_lookups
where lookup_type='CST_ROLLUP_TYPE' and
:$flex$.Rollup_Costs = 'Y'
order by value,description2USRollup Option9801=1mp.organization_code=:p_organization_codeLOV customYselect
ood.organization_code value,
ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description
from
org_organization_definitions ood,
mtl_parameters mp,
mtl_parameters mp0,
hr_all_organization_units haou,
hr_locations_all hla,
fnd_territories_vl ftv
where
ood.organization_id=mp.organization_id and
mp.organization_id<>mp.master_organization_id and
ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
nvl(ood.disable_date,sysdate)>=sysdate and
mp.master_organization_id=mp0.organization_id and
ood.organization_id=haou.organization_id(+) and
haou.location_id=hla.location_id(+) and
hla.country=ftv.territory_code(+) and
exists (select null from cst_cost_types cct where cct.cost_type = :$flex$.target_cost_type and nvl(cct.organization_id,ood.organization_id) = ood.organization_id)
order by
ood.organization_codeUSOrganization Code10901=1(msiv.organization_id, msiv.inventory_item_id) in
(
select
mic.organization_id,
mic.inventory_item_id
from
mtl_category_sets_v mcsv,
mtl_item_categories mic
where
mcsv.category_set_name=:p_category_set_name and
mcsv.category_set_id=mic.category_set_id
)LOVINV Category Set (restricted)8E2FF36EDF2D79D2E0530100007F1FF2Yselect
mcsv.category_set_name value,
mcsv.description
from
mtl_category_sets_vl mcsv
where
mcsv.category_set_id in (select mic.category_set_id from mtl_item_categories mic where mic.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID'))
order by
mcsv.category_set_nameselect category_set_name from mtl_default_sets_view where functional_area_id = 5USCategory Set111001=1(msiv.organization_id, msiv.inventory_item_id) in
(
select
mic.organization_id,
mic.inventory_item_id
from
mtl_category_sets_v mcsv,
mtl_categories_kfv mck,
mtl_item_categories mic
where
mck.concatenated_segments=:p_category and
mcsv.category_set_name=:p_category_set_name and
mcsv.category_set_id=mic.category_set_id and
mcsv.structure_id=mck.structure_id and
mck.category_id=mic.category_id
)LOVINV Category (restricted)8E2FF36EDF2E79D2E0530100007F1FF2Yselect
mck.concatenated_segments value,
mct.description
from
mtl_category_sets_v mcsv,
mtl_categories_kfv mck,
mtl_categories_tl mct
where
mcsv.category_set_name=:$flex$.category_set and
mcsv.structure_id=mck.structure_id and
(mcsv.category_set_id, mck.category_id) in (select mic.category_set_id, mic.category_id from mtl_item_categories mic where mic.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')) and
mck.category_id=mct.category_id and
mct.language=userenv('lang')
order by
mck.concatenated_segmentsUSCategory121101=1msiv.concatenated_segments=:p_item_numberLOVINV Item Number (costing enabled only items)EC5CECA520510FC8E0530100007F5EB3YYselect distinct
msiv.concatenated_segments value,
msiv.description
from
mtl_system_items_vl msiv,
mtl_parameters mp
where
msiv.organization_id = mp.organization_id and
(xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or
(:$flex$.organization_code is null and
mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
)
) and
(msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y')
order by
msiv.concatenated_segmentsUSItem131201=1upper(msiv.concatenated_segments) like upper(:p_item_like)CharUSItem Like141301=1upper(msiv.description) like upper(:p_item_desc_like)LOV customYselect distinct
msiv.description value,
msiv.concatenated_segments description
from
mtl_system_items_vl msiv,
mtl_parameters mp
where
msiv.organization_id = mp.organization_id and
(xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' or
(:$flex$.organization_code is null and
mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
)
) and
(msiv.costing_enabled_flag='Y' or mp.process_enabled_flag='Y')
order by
msiv.concatenated_segmentsUSItem Description151401=1msiv.item_type=xxen_util.lookup_code(:p_item_type,'ITEM_TYPE',3)LOVINV Item Type8E2FF36EDE8579D2E0530100007F1FF2Yselect
flvv.meaning value,
flvv.lookup_code description
from
fnd_lookup_values_vl flvv
where
flvv.enabled_flag='Y' and
flvv.lookup_type='ITEM_TYPE' and
flvv.view_application_id=3 and
flvv.security_group_id=0
order by
flvv.lookup_codeUSItem Type161501=1msiv.inventory_item_status_code not in
(select
misv2.inventory_item_status_code
from
mtl_item_status_vl misv2
where
misv2.inventory_item_status_code_tl = :p_item_status_to_exclude
)LOVINV Item Status8E2FF36EDE9C79D2E0530100007F1FF2Yselect
misv.inventory_item_status_code_tl value,
misv.description
from
mtl_item_status_vl misv
order by
misv.inventory_item_status_code_tlselect
misv.inventory_item_status_code_tl
from
mtl_parameters mp,
bom_parameters bp,
mtl_item_status_vl misv
where
mp.organization_id = bp.organization_id and
bp.bom_delete_status_code = misv.inventory_item_status_code and
((:$flex$.organization_code is null and mp.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID')) or
xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y'
) and
:$flex$.Item is nullUSExcluded Item Statuses171601=1msiv.planning_make_buy_code=xxen_util.lookup_code(:p_make_or_buy,'MTL_PLANNING_MAKE_BUY',700)LOVCST Make or Buy089F6C142DA509B9E0630100007FC56CYselect
flvv.meaning value,
null description
from
fnd_lookup_values_vl flvv
where
flvv.lookup_type='MTL_PLANNING_MAKE_BUY' and
flvv.view_application_id=700 and
flvv.security_group_id=0 and
flvv.enabled_flag='Y'
order by
flvv.meaningUSMake or Buy181701=1msiv.buyer_id=:p_buyer_idLOV customYselect
x.*
from
(
select
ppx.person_id id,
ppx.full_name value,
ppx.employee_number description
from
per_people_x ppx
where
ppx.current_employee_flag='Y' and
ppx.person_id in (select pa.agent_id from po_agents pa where sysdate between nvl(pa.start_date_active,sysdate) and nvl(pa.end_date_active,sysdate))
order by
ppx.full_name
) x
union all
select null id, '<null>' value, null description from dualUSBuyer191801=1msiv.planner_code=:p_planner_codeLOV customYselect distinct
mp.planner_code value,
mp.description
from
mtl_planners mp
where
nvl(mp.disable_date,sysdate+1)>=sysdate and
(:$flex$.organization_code is null or mp.organization_id in (select mp.organization_id from mtl_parameters mp where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y'))
order by
mp.planner_codeUSPlanner20190:p_exclude_no_cost_itermsLOVYes8E2FF36EDEA679D2E0530100007F1FF2Yselect 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dualYUSExclude Items with no Cost Details21200:p_exclude_rolled_up_itemsLOVYes8E2FF36EDEA679D2E0530100007F1FF2Yselect 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dualYUSExclude Rolled Up Items:$flex$.ItemItemExcluded Item Statuses:$flex$.Rollup_CostsRollup Costs?Rollup Option:$flex$.Rollup_CostsRollup Costs?Rollup Type:$flex$.Rollup_CostsRollup Costs?Rollup Unimplemented ECOs:$flex$.category_setCategory SetCategory:$flex$.organization_codeOrganization CodeExcluded Item Statuses:$flex$.organization_codeOrganization CodeItem:$flex$.organization_codeOrganization CodeItem Description:$flex$.organization_codeOrganization CodePlanner:$flex$.target_cost_typeTarget Cost TypeOrganization Code1ACTION_Charvarchar22STATUS_Charvarchar23MESSAGE_Charvarchar24REQUEST_ID_NumbernumberY5MODIFIED_COLUMNS_Charvarchar2Y6ROW_IDCharvarchar2Y7TARGET_COST_TYPELOV customvarchar2Yselect
cct.cost_type value,
cct.description description
from
cst_cost_types cct
where nvl(cct.disable_date,sysdate+1)>sysdate and
cct.costing_method_type = 1 and
cct.allow_updates_flag=1 and
cct.frozen_standard_flag!=1 and
(cct.organization_id is null or
cct.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
)
order by cost_typeselect
cost_type_id id,
cost_type value
from
cst_cost_types:$flex$.target_cost_typeYY8ORGANIZATION_CODELOVvarchar2INV Organization Code8E2FF36EDEB279D2E0530100007F1FF2Yselect
ood.organization_code value,
ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description
from
org_organization_definitions ood,
mtl_parameters mp,
mtl_parameters mp0,
hr_all_organization_units haou,
hr_locations_all hla,
fnd_territories_vl ftv
where
ood.organization_id=mp.organization_id and
mp.organization_id<>mp.master_organization_id and
(
ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or
not exists (select null from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
ood.operating_unit in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
) and
(:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and
(:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and
nvl(ood.disable_date,sysdate)>=sysdate and
mp.master_organization_id=mp0.organization_id and
ood.organization_id=haou.organization_id(+) and
haou.location_id=hla.location_id(+) and
hla.country=ftv.territory_code(+)
order by
ood.organization_codeYY9ITEMLOV customvarchar2YYselect
msiv.concatenated_segments value,
msiv.description
from
mtl_system_items_vl msiv,
mtl_parameters mp
where
msiv.organization_id=mp.organization_id and
mp.organization_code = :$column$.organization_code and
msiv.costing_enabled_flag='Y'
order by
msiv.concatenated_segmentsYY10ITEM_DESCRIPTIONLOV customvarchar2Yselect
msiv.description value,
msiv.concatenated_segments description
from
mtl_system_items_vl msiv,
mtl_parameters mp
where
msiv.organization_id=mp.organization_id and
mp.organization_code = :$column$.organization_code and
msiv.concatenated_segments = :$column$.item and
msiv.costing_enabled_flag='Y'
order by
msiv.description,
msiv.concatenated_segmentsselect
msiv.description
from
mtl_system_items_vl msiv,
mtl_parameters mp
where
msiv.organization_id=mp.organization_id and
mp.organization_code = :$column$.organization_code and
msiv.concatenated_segments = :$column$.item and
msiv.costing_enabled_flag='Y'Y11ITEM_STATUSLOV customvarchar2Yselect
misv.inventory_item_status_code_tl value,
null description
from
mtl_system_items_vl msiv,
mtl_parameters mp,
mtl_item_status_vl misv
where
msiv.organization_id=mp.organization_id and
msiv.inventory_item_status_code = misv.inventory_item_status_code and
mp.organization_code = :$column$.organization_code and
msiv.concatenated_segments = :$column$.item and
msiv.costing_enabled_flag='Y'
order by
msiv.description,
msiv.concatenated_segmentsselect
misv.inventory_item_status_code_tl
from
mtl_system_items_vl msiv,
mtl_parameters mp,
mtl_item_status_vl misv
where
msiv.organization_id=mp.organization_id and
msiv.inventory_item_status_code = misv.inventory_item_status_code and
mp.organization_code = :$column$.organization_code and
msiv.concatenated_segments = :$column$.item and
msiv.costing_enabled_flag='Y'12MAKE_OR_BUYLOV customvarchar2Yselect
xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) value,
null description
from mtl_system_items_vl msiv
where msiv.organization_id=
(select min(mp.master_organization_id) master_organization_id
from mtl_parameters mp
where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
) and
msiv.costing_enabled_flag = 'Y' and
msiv.concatenated_segments = :$column$.item
order by
msiv.description,
msiv.concatenated_segmentsselect xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700)
from mtl_system_items_vl msiv
where msiv.organization_id=
(select min(mp.master_organization_id) master_organization_id
from mtl_parameters mp
where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null
and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)
) and
msiv.costing_enabled_flag = 'Y' and
msiv.concatenated_segments = :$column$.itemY13INVENTORY_ASSETLOV customvarchar2Yselect
lookup_code id,
meaning value,
null description
from
mfg_lookups
where
lookup_type = 'SYS_YES_NO'
order by value
select
ml.lookup_code id,
ml.meaning value
from
mfg_lookups ml
where
ml.lookup_type = 'SYS_YES_NO'
select coalesce(
(
select
xxen_util.meaning(cic.inventory_asset_flag,'SYS_YES_NO',700)
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_types cct,
cst_item_costs cic
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item and
cct.cost_type = :$flex$.source_cost_type and
cic.organization_id = msiv.organization_id and
cic.inventory_item_id = msiv.inventory_item_id and
cic.cost_type_id = cct.cost_type_id
),
(
select
xxen_util.meaning(cic.inventory_asset_flag,'SYS_YES_NO',700)
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_types cct,
cst_item_costs cic
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item and
cct.cost_type = :$flex$.target_cost_type and
cic.organization_id = msiv.organization_id and
cic.inventory_item_id = msiv.inventory_item_id and
cic.cost_type_id = cct.default_cost_type_id
),
(
select
xxen_util.meaning(decode(msiv.inventory_asset_flag,'Y',1,2),'SYS_YES_NO',700)
from
mtl_parameters mp,
mtl_system_items_vl msiv
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item
)
) inventory_asset_flag
from
dualY14BASED_ON_ROLLUPLOV customvarchar2Yselect
lookup_code id,
meaning value,
null description
from
mfg_lookups
where
lookup_type = 'CST_BONROLLUP_VAL' and
lookup_code != 3
order by valueselect
ml.lookup_code id,
ml.meaning value
from
mfg_lookups ml
where
ml.lookup_type = 'SYS_YES_NO'
select coalesce(
(
select
xxen_util.meaning(nvl(cic.based_on_rollup_flag,2),'SYS_YES_NO',700)
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_types cct,
cst_item_costs cic
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item and
cct.cost_type = :$flex$.source_cost_type and
cic.organization_id = msiv.organization_id and
cic.inventory_item_id = msiv.inventory_item_id and
cic.cost_type_id = cct.cost_type_id
),
(
select
xxen_util.meaning(nvl(cic.based_on_rollup_flag,2),'SYS_YES_NO',700)
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_types cct,
cst_item_costs cic
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item and
cct.cost_type = :$flex$.target_cost_type and
cic.organization_id = msiv.organization_id and
cic.inventory_item_id = msiv.inventory_item_id and
cic.cost_type_id = cct.default_cost_type_id
),
(
select
xxen_util.meaning(decode(nvl(msiv.shrinkage_rate,0),0,2,1),'SYS_YES_NO',700)
from
mtl_parameters mp,
mtl_system_items_vl msiv
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item
)
) inventory_asset_flag
from
dual15LOT_SIZENumbernumberselect coalesce(
(
select
nvl(cic.lot_size,1)
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_types cct,
cst_item_costs cic
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item and
cct.cost_type = :$flex$.source_cost_type and
cic.organization_id = msiv.organization_id and
cic.inventory_item_id = msiv.inventory_item_id and
cic.cost_type_id = cct.cost_type_id
),
(
select
nvl(cic.lot_size,1)
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_types cct,
cst_item_costs cic
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item and
cct.cost_type = :$flex$.target_cost_type and
cic.organization_id = msiv.organization_id and
cic.inventory_item_id = msiv.inventory_item_id and
cic.cost_type_id = cct.default_cost_type_id
),
(
select
nvl(msiv.std_lot_size,1)
from
mtl_parameters mp,
mtl_system_items_vl msiv
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item
)
) inventory_asset_flag
from
dual16SHRINKAGE_RATENumbernumberselect coalesce(
(
select
nvl(cic.shrinkage_rate,0)
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_types cct,
cst_item_costs cic
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item and
cct.cost_type = :$flex$.source_cost_type and
cic.organization_id = msiv.organization_id and
cic.inventory_item_id = msiv.inventory_item_id and
cic.cost_type_id = cct.cost_type_id
),
(
select
nvl(cic.shrinkage_rate,0)
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_types cct,
cst_item_costs cic
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item and
cct.cost_type = :$flex$.target_cost_type and
cic.organization_id = msiv.organization_id and
cic.inventory_item_id = msiv.inventory_item_id and
cic.cost_type_id = cct.default_cost_type_id
),
(
select
nvl(msiv.shrinkage_rate,0)
from
mtl_parameters mp,
mtl_system_items_vl msiv
where
mp.organization_code = :$flex$.organization_code and
msiv.organization_id = mp.organization_id and
msiv.concatenated_segments = :$column$.item
)
) inventory_asset_flag
from
dual17COST_SOURCE_TYPECharvarchar2Y18COST_ELEMENTLOV customvarchar2select
cce.cost_element value,
cce.description description
from
cst_cost_elements cce
order by
cce.cost_element
YY19SUB_ELEMENTLOV customvarchar2Yselect distinct
br.resource_code value,
br.description description
from
mtl_parameters mp,
cst_cost_elements cce,
bom_resources br
where
mp.organization_code = :$column$.organization_code and
cce.cost_element = :$column$.cost_element and
br.organization_id = mp.organization_id and
br.cost_element_id = cce.cost_element_id and
nvl(allow_costs_flag,1) = 1 and
(br.cost_element_id not in (3,4) or br.functional_currency_flag = 1) and
nvl(br.disable_date,sysdate)>=sysdate
order by
br.resource_code20USAGE_RATE_OR_AMOUNTNumbernumberY21BASISLOVvarchar2CST Item Cost Upload - Basis Type LOVEF4BB9F32810B1A4E0530100007F390CYselect
ml.lookup_code id,
ml.meaning value,
nvl(ml.description,ml.meaning) || decode(ml.lookup_code,br.default_basis_type,' (Default)','') description
from
mtl_parameters mp,
cst_cost_elements cce,
bom_resources br,
mfg_lookups ml
where
mp.organization_code = :$column$.organization_code and
cce.cost_element = :$column$.cost_element and
br.organization_id = mp.organization_id and
br.cost_element_id = cce.cost_element_id and
br.resource_code = :$column$.sub_element and
ml.lookup_type = 'CST_BASIS_SHORT' and
( (cce.cost_element_id in (1,3,4,5,6) and ml.lookup_code in (1,2)) or
(cce.cost_element_id = 2 and ml.lookup_code between 1 and 6)
)
order by
ml.meaning
select
ml.lookup_code id,
ml.meaning value
from
mfg_lookups ml
where
ml.lookup_type = 'CST_BASIS_SHORT'22ACTIVITYLOV customvarchar2Yselect distinct
ca.activity value,
ca.description description
from
mtl_parameters mp,
cst_activities ca
where
mp.organization_code = :$column$.organization_code and
nvl(ca.organization_id,mp.organization_id) = mp.organization_id and
nvl(ca.disable_date,sysdate)>=sysdate
order by
ca.activity23ACTIVITY_UNITSNumbernumber24ITEM_UNITSNumbernumber25OPERATION_SEQ_NUMLOV customnumberYselect
x.*
from
(
select
bos.operation_seq_num id,
bos.operation_seq_num value,
bos.operation_seq_num description
from
bom_operation_sequences bos,
bom_operational_routings bor,
bom_bill_of_materials bbom,
mtl_system_items_vl msiv,
mtl_parameters mp
where
bos.routing_sequence_id = bor.common_routing_sequence_id and
bor.organization_id = mp.organization_id and
bor.organization_id = bbom.organization_id and
bor.assembly_item_id = bbom.assembly_item_id and
bor.assembly_item_id = msiv.inventory_item_id and
bor.organization_id = msiv.organization_id and
msiv.concatenated_segments = :$column$.item and
mp.organization_code=:$column$.organization_code and
nvl(bos.disable_date, trunc(sysdate)+1) >= sysdate and
nvl(bos.operation_type,1) = 1 and
bor.alternate_routing_designator is null and
bbom.assembly_type = 1 and -- BOM
nvl(bbom.effectivity_control,1) <= 3
union
select
1,
1,
1
from
dual
where
:$column$.item = :$column$.item
) xselect
x.*
from
(
select
bos.operation_seq_num id,
bos.operation_seq_num value,
bos.operation_seq_num description
from
bom_operation_sequences bos,
bom_operational_routings bor,
bom_bill_of_materials bbom,
mtl_system_items_vl msiv,
mtl_parameters mp
where
bos.routing_sequence_id = bor.common_routing_sequence_id and
bor.organization_id = mp.organization_id and
bor.organization_id = bbom.organization_id and
bor.assembly_item_id = bbom.assembly_item_id and
bor.assembly_item_id = msiv.inventory_item_id and
bor.organization_id = msiv.organization_id and
msiv.concatenated_segments = :$column$.item and
mp.organization_code=:$column$.organization_code and
nvl(bos.disable_date, trunc(sysdate)+1) >= sysdate and
nvl(bos.operation_type,1) = 1 and
bor.alternate_routing_designator is null and
bbom.assembly_type = 1 and -- BOM
nvl(bbom.effectivity_control,1) <= 3
union
select
1,
1,
1
from
dual
where
:$column$.item = :$column$.item
) x26DEPARTMENTLOV customvarchar2Yselect distinct
bd.department_code value,
bd.description description
from
mtl_parameters mp,
bom_departments bd
where
mp.organization_code = :$column$.organization_code and
bd.organization_id = mp.organization_id and
nvl(bd.disable_date,sysdate)>=sysdate
order by
bd.department_code27BASIS_FACTORNumbernumber28NET_YIELD_OR_SHRINKAGE_FACTORNumbernumber29RESOURCE_RATENumbernumber30ELEMENT_UNIT_COSTNumbernumber31ITEM_MATERIAL_COSTNumbernumber32ITEM_MATERIAL_OVERHEAD_COSTNumbernumber33ITEM_RESOURCE_COSTNumbernumber34ITEM_OUTSIDE_PROCESSING_COSTNumbernumber35ITEM_OVERHEAD_COSTNumbernumber36ITEM_COSTNumbernumber37ITEM_UNBURDENED_COSTNumbernumber38ITEM_BURDEN_COSTNumbernumber39GROUP_IDNumbernumberY40LEVEL_TYPENumbernumber1Y41ROLLUP_SOURCE_TYPENumbernumber1Y42PROCESS_FLAGNumbernumber1Y43UPLOAD_MODECharvarchar2:$flex$.modeYY44ROLLUP_COSTSCharvarchar2:$flex$.rollup_costsYY45ROLLUP_TYPECharvarchar2:$flex$.rollup_typeYY46ROLLUP_OPTIONCharvarchar2:$flex$.rollup_optionYY47ROLLUP_UNIMPL_ECOSCharvarchar2YU1p_inventory_item_idinNumberU2p_cost_type_idinNumberTARGET_COST_TYPEU3p_last_update_dateinDateU4p_last_updated_byinNumberU5p_creation_dateinDateU6p_created_byinNumberU7p_last_update_logininNumberU8p_group_idinNumberU9p_organization_idinNumberU10p_operation_sequence_idinNumberU11p_operation_seq_numinNumberOPERATION_SEQ_NUMU12p_department_idinNumberU13p_level_typeinNumberLEVEL_TYPEU14p_activity_idinNumberU15p_resource_seq_numinNumberU16p_resource_idinNumberU17p_resource_rateinNumberU18p_item_unitsinNumberITEM_UNITSU19p_activity_unitsinNumberACTIVITY_UNITSU20p_usage_rate_or_amountinNumberUSAGE_RATE_OR_AMOUNTU21p_basis_typeinNumberBASISU22p_basis_resource_idinNumberU23p_basis_factorinNumberU24p_net_yield_or_shrinkage_factinNumberU25p_item_costinNumberU26p_cost_element_idinNumberU27p_rollup_source_typeinNumberROLLUP_SOURCE_TYPEU28p_activity_contextinCharU29p_request_idinNumberU30p_organization_codeinCharORGANIZATION_CODEU31p_cost_typeinCharTARGET_COST_TYPEvalueU32p_inventory_iteminCharITEMU33p_departmentinCharDEPARTMENTU34p_activityinCharACTIVITYU35p_resource_codeinCharSUB_ELEMENTU36p_basis_resource_codeinCharU37p_cost_elementinCharCOST_ELEMENTU38p_error_typeinNumberU39p_program_application_idinNumberU40p_program_idinNumberU41p_program_update_dateinDateU42p_attribute_categoryinCharU43p_attribute1inCharU44p_attribute2inCharU45p_attribute3inCharU46p_attribute4inCharU47p_attribute5inCharU48p_attribute6inCharU49p_attribute7inCharU50p_attribute8inCharU51p_attribute9inCharU52p_attribute10inCharU53p_attribute11inCharU54p_attribute12inCharU55p_attribute13inCharU56p_attribute14inCharU57p_attribute15inCharU58p_transaction_idinNumberU59p_process_flaginNumberPROCESS_FLAGU60p_item_numberinCharU61p_transaction_typeinCharU62p_yielded_costinNumberU63p_error_codeinCharU64p_error_explanationinCharU65p_lot_sizeinNumberLOT_SIZEvalueU66p_based_on_rollup_flaginNumberBASED_ON_ROLLUPU67p_shrinkage_rateinNumberSHRINKAGE_RATEvalueU68p_inventory_asset_flaginNumberINVENTORY_ASSETU69p_error_flaginCharU70p_group_descriptioninCharU72p_row_idin/outCharROW_IDU73request_id_outNumberREQUEST_ID_U74status_outCharSTATUS_CODE_U75message_outCharMESSAGE_P1p_cost_typeinCharTARGET_COST_TYPEvalueP2p_upload_modeinNumberUPLOAD_MODEP3p_group_idinNumberGROUP_IDP4p_do_cost_rollupinCharYROLLUP_COSTSP5p_rollup_rangeinNumberYROLLUP_TYPEP6p_rollup_optioninNumberYROLLUP_OPTIONP7p_rollup_inc_unimp_ecn_flaginNumberYROLLUP_UNIMPL_ECOSP8request_id_outNumberREQUEST_ID_P9status_outCharSTATUS_CODE_P10message_outCharMESSAGE_Error SQLselect
xu.action_,
xxen_upload.status_meaning(xxen_upload.status_error) status_,
case when cicdi.error_flag = 'E'
then cicdi.error_explanation
else nvl(xu.message_,'Post-Processing Error')
end message_,
xu.request_id_,
xu.modified_columns_,
xu.row_id,
xu.target_cost_type,
xu.organization_code,
xu. item,
xu.item_description,
xu.item_status,
xu.make_or_buy,
nvl(xxen_util.meaning(cicdi.inventory_asset_flag,'SYS_YES_NO',700),xu.inventory_asset ) inventory_asset,
nvl(xxen_util.meaning(cicdi.based_on_rollup_flag,'CST_BONROLLUP_VAL',700),xu.based_on_rollup ) based_on_rollup,
nvl(cicdi.lot_size,xu.lot_size) lot_size,
nvl(cicdi.shrinkage_rate,xu.shrinkage_rate) shrinkage_rate,
nvl(xxen_util.meaning(cicdi.rollup_source_type,'CST_SOURCE_TYPE',700),xu.cost_source_type) cost_source_type,
nvl(cicdi.cost_element,xu.cost_element) cost_element,
nvl(cicdi.resource_code,xu.sub_element) sub_element,
nvl(cicdi.usage_rate_or_amount,xu.usage_rate_or_amount) usage_rate_or_amount,
nvl(xxen_util.meaning(cicdi.basis_type,'CST_BASIS_SHORT',700),xu.basis) basis,
nvl(cicdi.activity,xu.activity) activity,
nvl(cicdi.activity_units,xu.activity_units) activity_units,
nvl(cicdi.item_units,xu.item_units) item_units,
nvl(cicdi.operation_seq_num,xu.operation_seq_num) operation_seq_num,
nvl(cicdi.department,xu.department) department,
nvl(cicdi.basis_factor,xu.basis_factor) basis_factor,
nvl(cicdi.net_yield_or_shrinkage_factor,xu.net_yield_or_shrinkage_factor) net_yield_or_shrinkage_factor,
nvl(cicdi.resource_rate,xu.resource_rate) resource_rate,
nvl(cicdi.item_cost,xu.element_unit_cost) element_unit_cost,
to_number(null) item_material_cost,
to_number(null) item_material_overhead_cost,
to_number(null) item_resource_cost,
to_number(null) item_outside_processing_cost,
to_number(null) item_overhead_cost,
to_number(null) item_cost,
to_number(null) item_unburdened_cost,
to_number(null) item_burden_cost,
xu.group_id,
xu.level_type,
xu.rollup_source_type,
xu.process_flag,
xu.upload_mode,
xu.rollup_costs,
xu.rollup_type,
xu.rollup_option,
xu.rollup_unimpl_ecos
from
xxen_cst_item_standard_3838_u xu,
cst_item_cst_dtls_interface cicdi
where
chartorowid(xu.row_id)=cicdi.rowid(+) and
cicdi.error_flag(+)='E' and
(xu.status_code_=xxen_upload.status_error or cicdi.error_flag='E')Order Byx.target_cost_type,
x.organization_code,
x.item,
xxen_util.lookup_code(x.cost_source_type,'CST_SOURCE_TYPE',700),
x.cost_element,
x.sub_elementSuccess SQLselect
xxen_upload.action_meaning(xxen_upload.action_update) action_,
xxen_upload.status_meaning(xxen_upload.status_success) status_,
xu.message_,
cic.request_id request_id_,
null modified_columns_,
null row_id,
cct_t.cost_type target_cost_type,
mp.organization_code,
msiv.concatenated_segments item,
msiv.description item_description,
(select misv.inventory_item_status_code_tl from mtl_item_status_vl misv where misv.inventory_item_status_code = msiv.inventory_item_status_code) item_status,
xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_or_buy,
xxen_util.meaning(cic.inventory_asset_flag,'SYS_YES_NO',700) inventory_asset,
xxen_util.meaning(cic.based_on_rollup_flag,'CST_BONROLLUP_VAL',700) based_on_rollup,
cic.lot_size,
cic.shrinkage_rate,
cdcv.cost_source_type,
cdcv.cost_element,
cdcv.resource_code sub_element,
cdcv.usage_rate_or_amount,
cdcv.basis,
cdcv.activity,
cdcv.activity_units,
cdcv.item_units,
cdcv.operation_seq_num,
cdcv.department,
cdcv.basis_factor,
cdcv.net_yield_or_shrinkage_factor,
cdcv.resource_rate,
cdcv.item_cost element_unit_cost,
cic.material_cost item_material_cost,
cic.material_overhead_cost item_material_overhead_cost,
cic.resource_cost item_resource_cost,
cic.outside_processing_cost item_outside_processing_cost,
cic.overhead_cost item_overhead_cost,
cic.item_cost item_cost,
cic.unburdened_cost item_unburdened_cost,
cic.burden_cost item_burden_cost,
to_number(null) group_id,
1 level_type,
1 rollup_source_type,
1 process_flag,
:p_upload_mode upload_mode,
:p_do_cost_rollup rollup_costs,
:p_rollup_range rollup_type,
:p_rollup_option rollup_option,
:p_rollup_inc_unimp_ecn_flag rollup_unimpl_ecos
from
(select distinct
xu.request_id_,
case when xu.message_ like 'Warning%' then xu.message_ else null end message_
from
xxen_cst_item_standard_3838_u xu
where
xu.status_code_ = xxen_upload.status_success and
xu.request_id_ is not null
) xu,
cst_cost_types cct_t,
cst_item_costs cic,
cst_detail_cost_view cdcv,
mtl_system_items_vl msiv,
mtl_parameters mp
where
cct_t.cost_type = :p_target_cost_type and
cct_t.cost_type_id = cic.cost_type_id and
cic.cost_type_id = cdcv.cost_type_id and
cic.organization_id = cdcv.organization_id and
cic.inventory_item_id = cdcv.inventory_item_id and
cdcv.cost_source_type = xxen_util.meaning('1','CST_SOURCE_TYPE',700) and
cic.organization_id = mp.organization_id and
cic.organization_id = msiv.organization_id and
cic.inventory_item_id = msiv.inventory_item_id and
exists
(select
null
from
fnd_concurrent_requests fcr
where
fcr.request_id = cic.request_id and
(xu.request_id_ = fcr.request_id or -- for specific item cost rollup
xu.request_id_ = fcr.parent_request_id -- for cost import and/or all items cost rollup
)
)LOV query:$column$.organization_codeORGANIZATION_CODEACTIVITYDefault value:$column$.itemITEMBASED_ON_ROLLUPDefault value:$flex$.organization_codeOrganization Code|1=1|BASED_ON_ROLLUPDefault value:$flex$.source_cost_typeSource Cost Type|:p_source_cost_type|BASED_ON_ROLLUPDefault value:$flex$.target_cost_typeTarget Cost Type|:p_target_cost_type|BASED_ON_ROLLUPLOV query:$column$.cost_elementCOST_ELEMENTBASISLOV query:$column$.organization_codeORGANIZATION_CODEBASISLOV query:$column$.sub_elementSUB_ELEMENTBASISLOV query:$column$.organization_codeORGANIZATION_CODEDEPARTMENTDefault value:$column$.itemITEMINVENTORY_ASSETDefault value:$flex$.organization_codeOrganization Code|1=1|INVENTORY_ASSETDefault value:$flex$.source_cost_typeSource Cost Type|:p_source_cost_type|INVENTORY_ASSETDefault value:$flex$.target_cost_typeTarget Cost Type|:p_target_cost_type|INVENTORY_ASSETLOV query:$column$.organization_codeORGANIZATION_CODEITEMDefault value:$column$.itemITEMITEM_DESCRIPTIONLOV query:$column$.itemITEMITEM_DESCRIPTIONLOV query:$column$.organization_codeORGANIZATION_CODEITEM_DESCRIPTIONDefault value:$column$.organization_codeORGANIZATION_CODEITEM_DESCRIPTIONDefault value:$column$.itemITEMITEM_STATUSLOV query:$column$.itemITEMITEM_STATUSDefault value:$column$.organization_codeORGANIZATION_CODEITEM_STATUSLOV query:$column$.organization_codeORGANIZATION_CODEITEM_STATUSDefault value:$column$.itemITEMLOT_SIZEDefault value:$flex$.organization_codeOrganization Code|1=1|LOT_SIZEDefault value:$flex$.source_cost_typeSource Cost Type|:p_source_cost_type|LOT_SIZEDefault value:$flex$.target_cost_typeTarget Cost Type|:p_target_cost_type|LOT_SIZEDefault value:$column$.itemITEMMAKE_OR_BUYLOV query:$column$.itemITEMMAKE_OR_BUYLOV query:$column$.itemITEMOPERATION_SEQ_NUMLOV query:$column$.organization_codeORGANIZATION_CODEOPERATION_SEQ_NUMDefault value:$flex$.rollup_costsRollup Costs?|:p_do_cost_rollup|ROLLUP_COSTSDefault value:$flex$.rollup_optionRollup Option|:p_rollup_option|ROLLUP_OPTIONDefault value:$flex$.rollup_typeRollup Type|:p_rollup_range|ROLLUP_TYPEDefault value:$column$.itemITEMSHRINKAGE_RATEDefault value:$flex$.organization_codeOrganization Code|1=1|SHRINKAGE_RATEDefault value:$flex$.source_cost_typeSource Cost Type|:p_source_cost_type|SHRINKAGE_RATEDefault value:$flex$.target_cost_typeTarget Cost Type|:p_target_cost_type|SHRINKAGE_RATELOV query:$column$.cost_elementCOST_ELEMENTSUB_ELEMENTLOV query:$column$.organization_codeORGANIZATION_CODESUB_ELEMENTDefault value:$flex$.target_cost_typeTarget Cost Type|:p_target_cost_type|TARGET_COST_TYPEDefault value:$flex$.modeMode|:p_upload_mode|UPLOAD_MODEValue to id query:$column$.itemITEMp_operation_seq_numValue to id query:$column$.organization_codeORGANIZATION_CODEp_operation_seq_num