ENGINATICSSYSTEM_ADMINISTRATORSYSADMINE811F942597A2075E0530100007FF4F2Blitz Upload ModeYselect xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all
select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual
US8E2FF36EDF2E79D2E0530100007F1FF2INV 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 items8E2FF36EDE9C79D2E0530100007F1FF2INV 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_codeUS3A3524047B27AEF2E06362FB0905BD30select
null action_,
null status_,
null message_,
null modified_columns_,
:p_upload_type upload_type,
to_number(null) source_line_id,
to_number(null) source_header_id,
:p_trx_type transaction_type,
:p_trx_source source,
x.*
from
(
select
mp.organization_code organization,
msiv.concatenated_segments item,
ccg.cost_group cost_group,
:p_trx_date transaction_date,
msiv.description description,
cql.item_cost item_average_cost,
--
clcdv.level_type_dsp cost_element_level,
clcdv.cost_element,
--
to_number(null) new_average_cost,
to_number(null) percentage_change,
to_number(null) value_change,
--
cql.layer_quantity valued_quantity,
decode(:p_upload_type,'D',clcdv.item_cost,cql.item_cost) old_average_cost,
round(cql.layer_quantity * decode(:p_upload_type,'D',clcdv.item_cost,cql.item_cost),2) old_valuation,
--
to_number(null) adjusted_average_cost,
to_number(null) adjusted_valuation,
to_number(null) valuation_change,
--
null material_adj_account,
null material_overhead_adj_account,
null resource_adj_account,
null outside_processing_adj_account,
null overhead_adj_account,
:p_default_adj_account default_adj_account,
--
null reason,
null reference,
--
clcdv.cost_element_id,
clcdv.level_type,
0 upload_row
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_quantity_layers cql,
cst_layer_cost_details_v clcdv,
cst_cost_groups ccg
where
mp.organization_id = msiv.organization_id 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
mp.primary_cost_method = 2 and
msiv.organization_id = cql.organization_id and
msiv.inventory_item_id = cql.inventory_item_id and
msiv.inventory_item_flag = 'Y' and
msiv.inventory_asset_flag = 'Y' and
msiv.costing_enabled_flag = 'Y' and
decode(:p_upload_type,'D',cql.layer_id,null) = clcdv.layer_id(+) and
(:p_upload_type = 'S' or clcdv.cost_element_id is not null) and
cql.cost_group_id = ccg.cost_group_id and
(
(ccg.cost_group_id = mp.default_cost_group_id) or
( nvl(ccg.cost_group_type,1) in (1,3) and
exists
(select
'EXISTS'
from
cst_cost_group_accounts ccga
where
ccga.cost_group_id = ccg.cost_group_id and
ccga.organization_id = mp.organization_id
)
)
) and
1=1
union
-- for new items with no costing layers take from the cst_item_costs/cst_item_cost_details
select
mp.organization_code organization,
msiv.concatenated_segments item,
ccg.cost_group cost_group,
:p_trx_date transaction_date,
msiv.description description,
cic.item_cost item_average_cost,
--
cicdv.level_type_dsp cost_element_level,
cicdv.cost_element,
--
to_number(null) new_average_cost,
to_number(null) percentage_change,
to_number(null) value_change,
--
0 valued_quantity,
decode(:p_upload_type,'D',cicdv.item_cost,cic.item_cost) old_average_cost,
0 old_valuation,
--
to_number(null) adjusted_average_cost,
to_number(null) adjusted_valuation,
to_number(null) valuation_change,
--
null material_adj_account,
null material_overhead_adj_account,
null resource_adj_account,
null outside_processing_adj_account,
null overhead_adj_account,
null default_adj_account,
--
null reason,
null reference,
--
cicdv.cost_element_id,
cicdv.level_type,
0 upload_row
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_item_costs cic,
cst_item_cost_details_v cicdv,
cst_cost_groups ccg
where
mp.organization_id = msiv.organization_id 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
mp.primary_cost_method = 2 and
msiv.organization_id = cic.organization_id and
msiv.inventory_item_id = cic.inventory_item_id and
msiv.inventory_item_flag = 'Y' and
msiv.inventory_asset_flag = 'Y' and
msiv.costing_enabled_flag = 'Y' and
not exists (select null from cst_quantity_layers cql where cql.organization_id = msiv.organization_id and cql.inventory_item_id = msiv.inventory_item_id) and
mp.primary_cost_method = cic.cost_type_id and
decode(:p_upload_type,'D',cic.organization_id,null) = cicdv.organization_id(+) and
decode(:p_upload_type,'D',cic.inventory_item_id,null) = cicdv.inventory_item_id(+) and
decode(:p_upload_type,'D',cic.cost_type_id,null) = cicdv.cost_type_id(+) and
(:p_upload_type = 'S' or cicdv.cost_element_id is not null) and
mp.default_cost_group_id = ccg.cost_group_id and
1=1
) xYUXXEN_CST_UPLOADAxxen_cst_upload.upload_avg_cost_upd_trxxxen_cst_upload.process_avg_cost_upd_trxxxen_cst_upload.validate_avg_cost_upd_trx
( p_upload_type => :$column$.upload_type
, p_cost_element_level => :$column$.cost_element_level
, p_cost_element => :$column$.cost_element
, p_new_average_cost => :$column$.new_average_cost
, p_percentage_change => :$column$.percentage_change
, p_value_change => :$column$.value_change
)USCST Item Average Cost UploadCST Item Average Cost Upload
============================
This upload supports the uploading of item average costs for organizations using the Average Costing Method.
The upload delivers equivalent functionality to the Average Cost Update form in Oracle EBS
(At this time the upload does not support value change quantity adjustments).
Upload Mode:
Use the “Create” upload mode to generate an empty excel into which you can paste the average cost updates to be uploaded.
Use the “Create, Update” upload mode if you wish to download the current average costs for a specified set of items into the excel for updating. Additional entries (rows) can also be created in the generated excel.
Upload Type:
The upload supports updating average costs at the (summary) item level or at the (detailed) elemental cost level. This is determined by the Upload Type report parameter.
If updating the average cost at the item level, any change made to the item average cost is spread to all cost elements and levels in the same proportion as existed prior to the update.
If updating the average cost at the elemental cost level, you only need to adjust and upload the specific cost elements/levels that require a change. It is not necessary to upload the average costs for all cost elements/levels for the item. The upload will only adjust the costs for the cost elements/levels uploaded and leave the others unchanged. The total item average cost will be updated based on the total of the item’s elemental costs.
For uploads at the item cost level, the Cost Element Level Type (This, Previous) and the Cost Element columns must be left blank.
For uploads at the elemental cost level, the Cost Element Level Type (This, Previous) and the Cost Element columns must be populated in the upload excel.
To adjust the average price for an item or cost element you must enter one and one only of either of the following columns
- New Average Cost: the average cost of the item or cost element will be set to this value. It must be a positive value
- Percentage Change: the average cost of the item or cost element will be changed by this +/- percentage
- Value Change: the +/- amount by which the onhand inventory value should be changed. The average cost will be calculated based on the dividing the new inventory value by the quantity onhand. You can only perform a value change for items with onhand quantity.
Default Adjustment Account
The default adjustment account is used for the inventory revaluations. You can override the default adjustment account for each cost element within the upload excel if required. Otherwise leave the adjustment accounts in the upload excel blank, or you can remove them from the report template so they do not show.
Informational Columns.
The upload excel by default contains some information columns. If you do not require these columns they can be removed from the template being used so they do not show.
The following columns are informational only
- Description: the item description
- Item Average Cost: the item’s current average cost
- Valued Quantity: the quantity on hand
- Old Average Cost: the item or cost element/level current average cost
- Old Valuation: the current valuation of inventory onhand for the item or cost element/level
- Adjusted Average Cost: the item or cost element/level new average cost based
- Adjusted Valuation: the new valuation of inventory onhand for the item or cost element/level
- Valuation Change: the change in inventory valuation for the item or cost element/level
EnginaticsR12 onlyUpload1=1:p_default_adj_account:p_trx_date:p_trx_source:p_trx_type:p_upload_type1-101=1:p_coa=:p_coaNumberselect
ood.chart_of_accounts_id
from
org_organization_definitions ood
where
(:$flex$.organization_code is not null and xxen_util.contains(:$flex$.organization_code,ood.organization_code) = 'Y')
or
(:$flex$.organization_code is null and
ood.organization_code =
coalesce(
(select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')),
(select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id and rownum=1)
)
)USChart of Accounts ID2101=1:p_upload_mode like '%' || xxen_upload.action_updateLOVBlitz Upload ModeE811F942597A2075E0530100007FF4F2Yselect xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all
select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual
C,UYUSUpload Mode320:p_upload_typeLOV customYselect
x.id,
x.value,
x.description
from
(select 'D' id, 'Detail - Elemental Costs' value, 'Update average cost for each Cost Element. Item cost will be recalculated from the Cost Elements' description from dual
union
select 'S' id, 'Summary - Item Costs' value, 'Update average cost for the Item. Item Cost changes will be apportioned over the Cost Elements' description from dual
) x
order by
x.valueYUSUpload Type425:p_trx_typeLOV customYselect
mttv.transaction_type_name value,
mttv.description description
from
mtl_trx_types_view mttv
where
mttv.transaction_action_id = 24 and
(mttv.transaction_source_type_id = 13 or mttv.transaction_source_type_id >= 100)
order by
valueselect mttv.transaction_type_name from mtl_trx_types_view mttv where mttv.transaction_type_id = 80YUSTransaction Type530:p_trx_dateDateTimesysdateYUSTransaction Date650:p_default_sourceCharUSTransaction Source Name7551=1:p_default_acc_alias=:p_default_acc_aliasLOV customYselect
fsfa.alias_name value,
fsfa.concatenated_segments || ' (' || flvv.description || ')' description
from
fnd_shorthand_flex_aliases fsfa,
gl_code_combinations_kfv gcck,
fnd_lookup_values_vl flvv
where
fsfa.application_id=101 and
fsfa.id_flex_code='GL#' and
fsfa.id_flex_num=:$flex$.Chart_of_Accounts_ID and
fsfa.enabled_flag='Y' and
trunc(sysdate) between nvl(fsfa.start_date_active,trunc(sysdate)) and nvl(fsfa.end_date_active,trunc(sysdate)) and
gcck.chart_of_accounts_id=:$flex$.Chart_of_Accounts_ID and
gcck.concatenated_segments=fsfa.concatenated_segments and
gcck.enabled_flag='Y' and
gcck.summary_flag='N' and
trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and
flvv.lookup_type='ACCOUNT TYPE' and
flvv.view_application_id=101 and
flvv.lookup_code=gcck.gl_account_type
order by valueUSDefault Adjustment Account Alias860:p_default_adj_accountLOV customYselect
gcck.concatenated_segments value,
xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description
from
gl_code_combinations_kfv gcck,
fnd_lookup_values_vl flvv
where
gcck.chart_of_accounts_id=:$flex$.Chart_of_Accounts_ID and
gcck.enabled_flag = 'Y' and
gcck.summary_flag = 'N' and
trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and
gcck.detail_posting_allowed = 'Y' and
flvv.lookup_type = 'ACCOUNT TYPE' and
flvv.view_application_id = 101 and
flvv.lookup_code = gcck.gl_account_type and
(:$flex$.Default_Adjustment_Account_Alias is null or
gcck.concatenated_segments in
(select
fsfa.concatenated_segments
from
fnd_shorthand_flex_aliases fsfa
where
fsfa.application_id=101 and
fsfa.id_flex_code='GL#' and
fsfa.id_flex_num=:$flex$.Chart_of_Accounts_ID and
fsfa.enabled_flag='Y' and
trunc(sysdate) between nvl(fsfa.start_date_active,trunc(sysdate)) and nvl(fsfa.end_date_active,trunc(sysdate)) and
fsfa.alias_name = :$flex$.Default_Adjustment_Account_Alias
)
)
order by
valueselect
fsfa.concatenated_segments
from
fnd_shorthand_flex_aliases fsfa
where
fsfa.application_id=101 and
fsfa.id_flex_code='GL#' and
fsfa.id_flex_num=:$flex$.Chart_of_Accounts_ID and
fsfa.enabled_flag='Y' and
trunc(sysdate) between nvl(fsfa.start_date_active,trunc(sysdate)) and nvl(fsfa.end_date_active,trunc(sysdate)) and
fsfa.alias_name = :$flex$.Default_Adjustment_Account_AliasYUSDefault Adjustment Account9801=1mp.organization_code=:p_organization_codeLOV customYselect
oav.organization_code value,
oav.organization_name description
from
org_access_view oav,
mtl_parameters mp
where
oav.organization_id = mp.organization_id and
oav.resp_application_id=fnd_global.resp_appl_id and
oav.responsibility_id=fnd_global.resp_id and
mp.primary_cost_method = 2 -- average costing
order by
oav.organization_codeselect mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') and mp.primary_cost_method = 2YUSOrganization Code10901=1ccg.cost_group=:p_cost_groupLOV customYselect
ccgv.cost_group value,
ccgv.description || decode(ccgv.cost_group_id,mp.default_cost_group_id,' (Default)',null) description
from
cst_cost_groups_v ccgv,
mtl_parameters mp
where
xxen_util.contains(:$flex$.organization_code,mp.organization_code) = 'Y' and
mp.organization_id = nvl(ccgv.organization_id,mp.organization_id) and
(
mp.default_cost_group_id = ccgv.cost_group_id or
( nvl(ccgv.cost_group_type,1) in (1,3) and
exists
(select
'EXISTS'
from
cst_cost_group_accounts ccga
where
ccga.cost_group_id = ccgv.cost_group_id and
ccga.organization_id = mp.organization_id
)
)
) and
nvl(ccgv.cost_group_type,1) <> 2 and
nvl(ccgv.disable_date, sysdate+1) >= sysdate
order by
ccgv.cost_groupselect
ccgv.cost_group
from
cst_cost_groups_v ccgv,
mtl_parameters mp
where
mp.organization_code = :$flex$.organization_code and
mp.organization_id = nvl(ccgv.organization_id,mp.organization_id) and
(mp.default_cost_group_id = ccgv.cost_group_id) and
nvl(cost_group_type,1) <> 2 and
nvl(disable_date, sysdate+1) >= sysdateUSCost Group111001=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 Set121101=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_segmentsUSCategory131201=1msiv.concatenated_segments=:p_itemLOV customYYselect 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.inventory_item_flag = 'Y' and
msiv.inventory_asset_flag = 'Y' and
msiv.costing_enabled_flag = 'Y'
order by
msiv.concatenated_segmentsUSItem141301=1upper(msiv.concatenated_segments) like upper(:p_item_like)CharUSItem Like151401=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 Description161501=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 Type171601=1msiv.inventory_item_status_code not in (select mis.inventory_item_status_code from mtl_item_status mis where mis.inventory_item_status_code_tl=:p_item_status)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 Statuses181701=1msiv.planning_make_buy_code=xxen_util.lookup_code(:p_make_or_buy,'MTL_PLANNING_MAKE_BUY',700)LOV customYselect
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
order by
flvv.meaningUSMake or Buy191801=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 dualselect
ppx.full_name
from
fnd_user fu,
per_people_x ppx
where
1=2 and
fu.user_id=fnd_global.user_id and
fu.employee_id=ppx.person_id and
ppx.current_employee_flag='Y' and
ppx.person_id in (select pa.agent_id from po_agents pa)USBuyer201901=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_codeUSPlanner:$flex$.Chart_of_Accounts_IDChart of Accounts IDDefault Adjustment Account:$flex$.Chart_of_Accounts_IDChart of Accounts IDDefault Adjustment Account Alias:$flex$.Default_Adjustment_Account_AliasDefault Adjustment Account AliasDefault Adjustment Account:$flex$.ItemItemExcluded Item Statuses:$flex$.category_setCategory SetCategory:$flex$.organization_codeOrganization CodeChart of Accounts ID:$flex$.organization_codeOrganization CodeCost Group:$flex$.organization_codeOrganization CodeExcluded Item Statuses:$flex$.organization_codeOrganization CodeItem:$flex$.organization_codeOrganization CodeItem Description:$flex$.organization_codeOrganization CodePlannerDetail - Elemental Costs TemplateUpdate an items elemental costs. The Item's total cost will be recalculated from the elmental costs.ERIC.CLEGG15ADJUSTED_AVERAGE_COST16ADJUSTED_VALUATION8COST_ELEMENTY7COST_ELEMENT_LEVELY3COST_GROUP5DESCRIPTION2ITEM6ITEM_AVERAGE_COST9NEW_AVERAGE_COST13OLD_AVERAGE_COST14OLD_VALUATION1ORGANIZATION10PERCENTAGE_CHANGE18REASON19REFERENCE4TRANSACTION_DATE17VALUATION_CHANGE12VALUED_QUANTITY11VALUE_CHANGEUpload ModeCUpload TypeDSSiteSummary - Item Costs TemplateUpdate an items total cost. The changes to an items total cost will be apportioned over the existing cost elementsERIC.CLEGG12ADJUSTED_AVERAGE_COST13ADJUSTED_VALUATION3COST_GROUP5DESCRIPTION2ITEM6NEW_AVERAGE_COST10OLD_AVERAGE_COST11OLD_VALUATION1ORGANIZATION7PERCENTAGE_CHANGE15REASON16REFERENCE4TRANSACTION_DATE14VALUATION_CHANGE9VALUED_QUANTITY8VALUE_CHANGEUpload ModeCUpload TypeSSSiteDetail - Elemental Costs Template1ACTION_Charvarchar22STATUS_Charvarchar23MESSAGE_Charvarchar24MODIFIED_COLUMNS_Charvarchar2Y5UPLOAD_TYPECharvarchar2:$flex$.upload_typeYYY6SOURCE_LINE_IDNumbernumberY7SOURCE_HEADER_IDNumbernumberYY8TRANSACTION_TYPELOV customvarchar2Yselect
mttv.transaction_type_id id,
mttv.transaction_type_name value,
mttv.description description
from
mtl_trx_types_view mttv
where
mttv.transaction_action_id = 24 and
(mttv.transaction_source_type_id = 13 or mttv.transaction_source_type_id >= 100)
order by
value:$flex$.transaction_typeYYY9SOURCECharvarchar2:$flex$.Transaction_Source_NameY10ORGANIZATIONLOV customvarchar2Yselect
ood.organization_id id,
mp.organization_code value,
ood.organization_name description
from
mtl_parameters mp,
org_organization_definitions ood
where
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
mp.organization_id=ood.organization_id and
nvl(ood.disable_date,sysdate)>=sysdate and
mp.primary_cost_method = 2
order by
mp.organization_code:$flex$.organization_codeYY11ITEMLOV customvarchar2YYselect
msiv.inventory_item_id id,
msiv.concatenated_segments value,
msiv.description||' ('||mp.organization_code||')' description
from
mtl_parameters mp,
mtl_system_items_vl msiv
where
mp.organization_code=:$column$.organization and
mp.organization_id=msiv.organization_id and
msiv.inventory_item_flag = 'Y' and
msiv.inventory_asset_flag = 'Y' and
msiv.costing_enabled_flag = 'Y'
order by
msiv.concatenated_segmentsYY12COST_GROUPLOV customvarchar2Yselect
ccgv.cost_group_id id,
ccgv.cost_group value,
ccgv.description || decode(ccgv.cost_group_id,mp.default_cost_group_id,' (Default)',null) description
from
cst_cost_groups_v ccgv,
mtl_parameters mp
where
mp.organization_code = :$column$.organization and
mp.organization_id = nvl(ccgv.organization_id,mp.organization_id) and
(
mp.default_cost_group_id = ccgv.cost_group_id or
( nvl(ccgv.cost_group_type,1) in (1,3) and
exists
(select
'EXISTS'
from
cst_cost_group_accounts ccga
where
ccga.cost_group_id = ccgv.cost_group_id and
ccga.organization_id = mp.organization_id
)
)
) and
nvl(ccgv.cost_group_type,1) <> 2 and
nvl(ccgv.disable_date, sysdate+1) >= sysdate
order by
ccgv.cost_groupselect
ccgv.cost_group
from
cst_cost_groups_v ccgv,
mtl_parameters mp
where
mp.organization_code = :$column$.organization and
mp.organization_id = nvl(ccgv.organization_id,mp.organization_id) and
mp.default_cost_group_id = ccgv.cost_group_idYY13TRANSACTION_DATEDatedate:$flex$.transaction_dateY14DESCRIPTIONLOV customvarchar2Yselect
msiv.description value,
null description
from
mtl_parameters mp,
mtl_system_items_vl msiv
where
mp.organization_code=:$column$.organization and
mp.organization_id=msiv.organization_id and
msiv.concatenated_segments=:$column$.itemselect
msiv.description description
from
mtl_parameters mp,
mtl_system_items_vl msiv
where
mp.organization_code=:$column$.organization and
mp.organization_id=msiv.organization_id and
msiv.concatenated_segments=:$column$.itemItem Description - Informational Only15ITEM_AVERAGE_COSTNumbernumberselect
cql.item_cost
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_groups ccg,
cst_quantity_layers cql
where
mp.organization_code = :$column$.organization and
mp.organization_id = msiv.organization_id and
msiv.concatenated_segments = :$column$.item and
mp.organization_id = nvl(ccg.organization_id,mp.organization_id) and
ccg.cost_group = :$column$.cost_group and
msiv.organization_id = cql.organization_id and
msiv.inventory_item_id = cql.inventory_item_id and
ccg.cost_group_id = cql.cost_group_idItem Current Average Cost - Informational Only16COST_ELEMENT_LEVELLOV customvarchar2Yselect
ml.lookup_code id,
ml.meaning value,
null description
from
mfg_lookups ml
where
ml.lookup_type = 'CST_LEVEL' and
:$column$.upload_type = 'D'
order by
valueOnly required for Elemental Cost UpdatesY17COST_ELEMENTLOV customvarchar2Yselect
cce.cost_element_id id,
cce.cost_element value,
null description
from
cst_cost_elements cce
where
:$column$.upload_type = 'D'
order by
valueOnly required for Elemental Cost UpdatesY18NEW_AVERAGE_COSTNumbernumberEnter a New Average Cost. This value cannot be negative.
You must enter either a New Average Cost, Percentage Change, or a Value Change19PERCENTAGE_CHANGENumbernumberEnter a percentage change in the average cost. The average cost is updated by this percentage value.
You must enter either a New Average Cost, Percentage Change, or a Value Change20VALUE_CHANGENumbernumberEnter the amount to increase or decrease the current on-hand inventory value.
To decrease the value, enter a negative amount.
However, you cannot enter a value that drives the inventory value negative.
You must enter either a New Average Cost, Percentage Change, or a Value Change21VALUED_QUANTITYNumbernumberselect
cql.layer_quantity
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_groups ccg,
cst_quantity_layers cql
where
mp.organization_code = :$column$.organization and
mp.organization_id = msiv.organization_id and
msiv.concatenated_segments = :$column$.item and
mp.organization_id = nvl(ccg.organization_id,mp.organization_id) and
ccg.cost_group = :$column$.cost_group and
msiv.organization_id = cql.organization_id and
msiv.inventory_item_id = cql.inventory_item_id and
ccg.cost_group_id = cql.cost_group_idCurrent Onhand Quantity - Informational Only22OLD_AVERAGE_COSTNumbernumberselect
decode(:$column$.upload_type,'D',clcdv.item_cost,cql.item_cost)
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_groups ccg,
cst_quantity_layers cql,
cst_layer_cost_details_v clcdv
where
mp.organization_code = :$column$.organization and
mp.organization_id = msiv.organization_id and
msiv.concatenated_segments = :$column$.item and
mp.organization_id = nvl(ccg.organization_id,mp.organization_id) and
ccg.cost_group = :$column$.cost_group and
msiv.organization_id = cql.organization_id and
msiv.inventory_item_id = cql.inventory_item_id and
ccg.cost_group_id = cql.cost_group_id and
decode(:$column$.upload_type,'D',cql.layer_id,null) = clcdv.layer_id(+) and
(clcdv.layer_id is null or (clcdv.level_type_dsp = :$column$.cost_element_level and clcdv.cost_element = :$column$.cost_element))Current Average Cost - Informational Only23OLD_VALUATIONNumbernumberselect
round(cql.layer_quantity * decode(:$column$.upload_type,'D',clcdv.item_cost,cql.item_cost),2)
from
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_groups ccg,
cst_quantity_layers cql,
cst_layer_cost_details_v clcdv
where
mp.organization_code = :$column$.organization and
mp.organization_id = msiv.organization_id and
msiv.concatenated_segments = :$column$.item and
mp.organization_id = nvl(ccg.organization_id,mp.organization_id) and
ccg.cost_group = :$column$.cost_group and
msiv.organization_id = cql.organization_id and
msiv.inventory_item_id = cql.inventory_item_id and
ccg.cost_group_id = cql.cost_group_id and
decode(:$column$.upload_type,'D',cql.layer_id,null) = clcdv.layer_id(+) and
(clcdv.layer_id is null or (clcdv.level_type_dsp = :$column$.cost_element_level and clcdv.cost_element = :$column$.cost_element))Current Valuation - Informational Only24ADJUSTED_AVERAGE_COSTNumbernumberselect
case
when :$column$.new_average_cost is not null then round(:$column$.new_average_cost,5)
when :$column$.percentage_change is not null then round(nvl(:$column$.old_average_cost,0) * (1 + :$column$.percentage_change/100),5)
when :$column$.value_change is not null and nvl(:$column$.valued_quantity,0) = 0 then 0
when :$column$.value_change is not null and nvl(:$column$.valued_quantity,0) != 0 then round((nvl(:$column$.old_valuation,0) + :$column$.value_change)/:$column$.valued_quantity,5)
else null
end
from dualNew Average Cost - Informational Only25ADJUSTED_VALUATIONNumbernumberselect
case
when :$column$.new_average_cost is not null then round(:$column$.valued_quantity * :$column$.new_average_cost,2)
when :$column$.percentage_change is not null then round(:$column$.valued_quantity * (nvl(:$column$.old_average_cost,0) * (1+:$column$.percentage_change/100)),2)
when :$column$.value_change is not null then round(nvl(:$column$.old_valuation,0) + :$column$.value_change,2)
else null
end
from dualNew Valuation - Informational Only26VALUATION_CHANGENumbernumberselect
case
when :$column$.new_average_cost is not null then round((:$column$.valued_quantity * :$column$.new_average_cost) - :$column$.old_valuation,2)
when :$column$.percentage_change is not null then round((:$column$.valued_quantity * (nvl(:$column$.old_average_cost,0) * (1+:$column$.percentage_change/100))) - :$column$.old_valuation,2)
when :$column$.value_change is not null then round(:$column$.value_change,2)
else null
end
from dualValution Change - Informational Only27MATERIAL_ADJ_ACCOUNTLOV customvarchar2Yselect
gcck.code_combination_id id,
gcck.concatenated_segments value,
xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description
from
org_organization_definitions ood,
gl_code_combinations_kfv gcck,
fnd_lookup_values_vl flvv
where
ood.organization_code=:$column$.organization and
gcck.chart_of_accounts_id=ood.chart_of_accounts_id and
gcck.enabled_flag = 'Y' and
gcck.summary_flag = 'N' and
trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and
gcck.detail_posting_allowed = 'Y' and
flvv.lookup_type = 'ACCOUNT TYPE' and
flvv.view_application_id = 101 and
flvv.lookup_code = gcck.gl_account_type
order by
valueOnly required if you need to override the Default Adjustment Account specified in the report parameters28MATERIAL_OVERHEAD_ADJ_ACCOUNTLOV customvarchar2Yselect
gcck.code_combination_id id,
gcck.concatenated_segments value,
xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description
from
org_organization_definitions ood,
gl_code_combinations_kfv gcck,
fnd_lookup_values_vl flvv
where
ood.organization_code=:$column$.organization and
gcck.chart_of_accounts_id=ood.chart_of_accounts_id and
gcck.enabled_flag = 'Y' and
gcck.summary_flag = 'N' and
trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and
gcck.detail_posting_allowed = 'Y' and
flvv.lookup_type = 'ACCOUNT TYPE' and
flvv.view_application_id = 101 and
flvv.lookup_code = gcck.gl_account_type
order by
valueOnly required if you need to override the Default Adjustment Account specified in the report parameters29RESOURCE_ADJ_ACCOUNTLOV customvarchar2Yselect
gcck.code_combination_id id,
gcck.concatenated_segments value,
xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description
from
org_organization_definitions ood,
gl_code_combinations_kfv gcck,
fnd_lookup_values_vl flvv
where
ood.organization_code=:$column$.organization and
gcck.chart_of_accounts_id=ood.chart_of_accounts_id and
gcck.enabled_flag = 'Y' and
gcck.summary_flag = 'N' and
trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and
gcck.detail_posting_allowed = 'Y' and
flvv.lookup_type = 'ACCOUNT TYPE' and
flvv.view_application_id = 101 and
flvv.lookup_code = gcck.gl_account_type
order by
valueOnly required if you need to override the Default Adjustment Account specified in the report parameters30OUTSIDE_PROCESSING_ADJ_ACCOUNTLOV customvarchar2Yselect
gcck.code_combination_id id,
gcck.concatenated_segments value,
xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description
from
org_organization_definitions ood,
gl_code_combinations_kfv gcck,
fnd_lookup_values_vl flvv
where
ood.organization_code=:$column$.organization and
gcck.chart_of_accounts_id=ood.chart_of_accounts_id and
gcck.enabled_flag = 'Y' and
gcck.summary_flag = 'N' and
trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and
gcck.detail_posting_allowed = 'Y' and
flvv.lookup_type = 'ACCOUNT TYPE' and
flvv.view_application_id = 101 and
flvv.lookup_code = gcck.gl_account_type
order by
valueOnly required if you need to override the Default Adjustment Account specified in the report parameters31OVERHEAD_ADJ_ACCOUNTLOV customvarchar2Yselect
gcck.code_combination_id id,
gcck.concatenated_segments value,
xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description
from
org_organization_definitions ood,
gl_code_combinations_kfv gcck,
fnd_lookup_values_vl flvv
where
ood.organization_code=:$column$.organization and
gcck.chart_of_accounts_id=ood.chart_of_accounts_id and
gcck.enabled_flag = 'Y' and
gcck.summary_flag = 'N' and
trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and
gcck.detail_posting_allowed = 'Y' and
flvv.lookup_type = 'ACCOUNT TYPE' and
flvv.view_application_id = 101 and
flvv.lookup_code = gcck.gl_account_type
order by
valueOnly required if you need to override the Default Adjustment Account specified in the report parameters32DEFAULT_ADJ_ACCOUNTLOV customvarchar2Yselect
gcck.code_combination_id id,
gcck.concatenated_segments value,
xxen_util.segments_description(gcck.code_combination_id) || ' (' || flvv.description || ')' description
from
org_organization_definitions ood,
gl_code_combinations_kfv gcck,
fnd_lookup_values_vl flvv
where
ood.organization_code=:$column$.organization and
gcck.chart_of_accounts_id=ood.chart_of_accounts_id and
gcck.enabled_flag = 'Y' and
gcck.summary_flag = 'N' and
trunc(sysdate) between nvl(gcck.start_date_active,trunc(sysdate)) and nvl(gcck.end_date_active,trunc(sysdate)) and
gcck.detail_posting_allowed = 'Y' and
flvv.lookup_type = 'ACCOUNT TYPE' and
flvv.view_application_id = 101 and
flvv.lookup_code = gcck.gl_account_type
order by
value:$flex$.Default_Adjustment_AccountY33REASONLOV customvarchar2Yselect
mtr.reason_id id,
mtr.reason_name value,
mtr.description description
from
mtl_transaction_reasons mtr
where
nvl(mtr.disable_date, sysdate+1) > sysdate and
( nvl(fnd_profile.value_wnps('INV_TRANS_REASON_SECURITY'), 'N') = 'N' or
mtr.reason_id in
(select
mtrs.reason_id
from
mtl_trans_reason_security mtrs
where
(mtrs.transaction_type_id = (select mttv.transaction_type_id from mtl_trx_types_view mttv where mttv.transaction_type_name = :$column$.transaction_type) or
nvl(mtrs.transaction_type_id,-1) = -1
) and
(mtrs.responsibility_id = fnd_global.resp_id or
nvl(mtrs.responsibility_id,-1) = -1)
)
)
order by
value34REFERENCECharvarchar235COST_ELEMENT_IDNumbernumberY36LEVEL_TYPENumbernumberY37UPLOAD_ROWNumbernumberYU2status_outCharSTATUS_CODE_U3message_outCharMESSAGE_U4p_source_line_idoutNumberSOURCE_LINE_IDU5p_source_header_idoutNumberSOURCE_HEADER_IDU6p_upload_typeinCharUPLOAD_TYPEU7p_transaction_type_idinNumberTRANSACTION_TYPEU8p_transaction_dateinDateTRANSACTION_DATEU9p_transaction_source_nameinCharSOURCEU10p_organization_idinNumberORGANIZATIONU11p_inventory_item_idinNumberITEMU12p_cost_group_idinNumberCOST_GROUPU13p_cost_element_level_typeinNumberCOST_ELEMENT_LEVELU14p_cost_element_idinNumberCOST_ELEMENTU15p_new_average_costinNumberNEW_AVERAGE_COSTU16p_value_changeinNumberVALUE_CHANGEU17p_percentage_changeinNumberPERCENTAGE_CHANGEU18p_default_adj_accountinNumberDEFAULT_ADJ_ACCOUNTU19p_material_accountinNumberMATERIAL_ADJ_ACCOUNTU20p_material_overhead_accountinNumberMATERIAL_OVERHEAD_ADJ_ACCOUNTU21p_resource_accountinNumberRESOURCE_ADJ_ACCOUNTU22p_outside_processing_accountinNumberOUTSIDE_PROCESSING_ADJ_ACCOUNTU23p_overhead_accountinNumberOVERHEAD_ADJ_ACCOUNTU24p_reason_idinNumberREASONU25p_transaction_referenceinCharREFERENCEU26p_valued_quantityinNumberVALUED_QUANTITYU27p_old_average_costinNumberOLD_AVERAGE_COSTU28p_old_valuationinNumberOLD_VALUATIONU29p_adjusted_average_costinNumberADJUSTED_AVERAGE_COSTU30p_adjusted_valuationinNumberADJUSTED_VALUATIONU31p_valuation_changeinNumberVALUATION_CHANGEP1status_outCharSTATUS_CODE_P2message_outCharMESSAGE_P3p_source_header_idinNumberSOURCE_HEADER_IDError SQLselect
xu.action_,
xxen_upload.status_meaning(xxen_upload.status_error) status_,
coalesce(
(select mti.error_code|| nvl2(mti.error_code,nvl2(mti.error_explanation,': ',''),'') || mti.error_explanation from mtl_transactions_interface mti where mti.source_code=xxen_cst_upload.avg_cost_upd_trx_source_code and xu.source_header_id=mti.source_header_id and xu.source_line_id=mti.source_line_id and rownum=1),
xu.message_,
'Record was not processed but no error was returned') message_,
xu.modified_columns_,
xu.upload_type,
xu.source_line_id,
xu.source_header_id,
xu.transaction_type,
xu.source,
xu.organization,
xu.item,
xu.cost_group,
xu.transaction_date,
xu.description,
xu.item_average_cost,
xu.cost_element_level,
xu.cost_element,
xu.new_average_cost,
xu.percentage_change,
xu.value_change,
xu.valued_quantity,
xu.old_average_cost,
xu.old_valuation,
xu.adjusted_average_cost,
xu.adjusted_valuation,
xu.valuation_change,
xu.material_adj_account,
xu.material_overhead_adj_account,
xu.resource_adj_account,
xu.outside_processing_adj_account,
xu.overhead_adj_account,
xu.default_adj_account,
xu.reason,
xu.reference,
xu.cost_element_id,
xu.level_type,
xu.upload_row
from
xxen_cst_item_average_c_1504_u xu
where
nvl(xu.status_code_,'E')<>'S' or
(xu.source_line_id, xu.source_header_id) in (select mti.source_line_id, mti.source_header_id from mtl_transactions_interface mti where mti.source_code=xxen_cst_upload.avg_cost_upd_trx_source_code)
Order Byupload_row,
organization,
item,
cost_group,
level_type,
cost_element_id
Success SQLselect /*+ push_pred(mctcdv_sum) */
xu.action_,
xu.status_,
xu.message_,
xu.modified_columns_,
xu.upload_type,
xu.source_header_id,
xu.source_line_id,
--
(select mttv.transaction_type_name from mtl_trx_types_view mttv where mttv.transaction_type_id = mmt.transaction_type_id) transaction_type,
mmt.transaction_source_name source,
--
mp.organization_code organization,
msiv.concatenated_segments item,
ccg.cost_group cost_group,
mmt.transaction_date transaction_date,
msiv.description description,
case
when mmt.costed_flag is null then nvl(cql.item_cost,0)
else (select
sum(case
when mctcdv.new_average_cost is not null then mctcdv.new_average_cost
when mctcdv.percentage_change is not null then nvl(mctcdv.item_cost,nvl(clcdv.item_cost,0)) * (1 + mctcdv.percentage_change / 100)
when mctcdv.value_change is not null and nvl(cql.layer_quantity,0) != 0 then ((nvl(mctcdv.item_cost,nvl(clcdv.item_cost,0)) * cql.layer_quantity) + mctcdv.value_change) / cql.layer_quantity
else 0
end
)
from
mtl_cst_txn_cost_details_v mctcdv,
cst_quantity_layers cql,
cst_layer_cost_details_v clcdv
where
mctcdv.transaction_id = mmt.transaction_id and
mctcdv.organization_id = cql.organization_id (+) and
mctcdv.inventory_item_id = cql.inventory_item_id (+) and
mmt.cost_group_id = cql.cost_group_id (+) and
cql.layer_id = clcdv.layer_id (+) and
mctcdv.cost_element_id = clcdv.cost_element_id (+) and
mctcdv.level_type = clcdv.level_type (+)
)
end item_average_cost,
--
xu.cost_element_level,
xu.cost_element,
--
case when xu.new_average_cost is not null then decode(xu.upload_type,'D',mctcdv.new_average_cost,mctcdv_sum.new_average_cost) end new_average_cost,
case when xu.percentage_change is not null then decode(xu.upload_type,'D',mctcdv.percentage_change,mmt.percentage_change) end percentage_change,
case when xu.value_change is not null then decode(xu.upload_type,'D',mctcdv.value_change,mmt.value_change) end value_change,
--
nvl(cql.layer_quantity,0) valued_quantity,
decode(xu.upload_type,'D',nvl(mctcdv.item_cost,0),decode(mmt.costed_flag,null,mmt.prior_cost,nvl(cql.item_cost,0))) old_average_cost,
round(nvl(cql.layer_quantity,0) * decode(xu.upload_type,'D',nvl(mctcdv.item_cost,0),decode(mmt.costed_flag,null,mmt.prior_cost,nvl(cql.item_cost,0))),2) old_valuation,
--
case
when mmt.costed_flag is null then decode(xu.upload_type,'D',clcdv.item_cost,nvl(cql.item_cost,0))
when xu.new_average_cost is not null then decode(xu.upload_type,'D',mctcdv.new_average_cost,mctcdv_sum.new_average_cost)
when xu.percentage_change is not null then decode(xu.upload_type,'D',nvl(mctcdv.item_cost,0),decode(mmt.costed_flag,null,mmt.prior_cost,nvl(cql.item_cost,0))) * (1 + decode(xu.upload_type,'D',mctcdv.percentage_change,mmt.percentage_change) / 100)
when xu.value_change is not null and nvl(cql.layer_quantity,0) = 0 then 0
when xu.value_change is not null then ((cql.layer_quantity * decode(xu.upload_type,'D',nvl(mctcdv.item_cost,0),decode(mmt.costed_flag,null,mmt.prior_cost,nvl(cql.item_cost,0)))) + decode(xu.upload_type,'D',mctcdv.value_change,mmt.value_change)) / cql.layer_quantity
else null
end adjusted_average_cost,
case
when mmt.costed_flag is null then round(nvl(cql.layer_quantity,0) * decode(xu.upload_type,'D',clcdv.item_cost,nvl(cql.item_cost,0)),2)
when xu.new_average_cost is not null then round(nvl(cql.layer_quantity,0) * decode(xu.upload_type,'D',mctcdv.new_average_cost,mctcdv_sum.new_average_cost),2)
when xu.percentage_change is not null then round(nvl(cql.layer_quantity,0) * (decode(xu.upload_type,'D',nvl(mctcdv.item_cost,0),decode(mmt.costed_flag,null,mmt.prior_cost,nvl(cql.item_cost,0))) * (1 + decode(xu.upload_type,'D',mctcdv.percentage_change,mmt.percentage_change)/100)),2)
when xu.value_change is not null and nvl(cql.layer_quantity,0) = 0 then 0
when xu.value_change is not null then round(nvl(cql.layer_quantity,0) * (((cql.layer_quantity * decode(xu.upload_type,'D',nvl(mctcdv.item_cost,0),decode(mmt.costed_flag,null,mmt.prior_cost,nvl(cql.item_cost,0)))) + decode(xu.upload_type,'D',mctcdv.value_change,mmt.value_change)) / cql.layer_quantity),2)
else null
end adjusted_valuation,
case
when mmt.costed_flag is null then round(nvl(cql.layer_quantity,0) * decode(xu.upload_type,'D',clcdv.item_cost,nvl(cql.item_cost,0)),2) - nvl(xu.old_valuation,0)
when xu.new_average_cost is not null then round(nvl(cql.layer_quantity,0) * decode(xu.upload_type,'D',mctcdv.new_average_cost,mctcdv_sum.new_average_cost),2) - nvl(xu.old_valuation,0)
when xu.percentage_change is not null then round(nvl(cql.layer_quantity,0) * (decode(xu.upload_type,'D',nvl(mctcdv.item_cost,0),decode(mmt.costed_flag,null,mmt.prior_cost,nvl(cql.item_cost,0))) * (1 + decode(xu.upload_type,'D',mctcdv.percentage_change,mmt.percentage_change)/100)),2) - nvl(xu.old_valuation,0)
when xu.value_change is not null and nvl(cql.layer_quantity,0) = 0 then 0
when xu.value_change is not null then round(nvl(cql.layer_quantity,0) * (((cql.layer_quantity * decode(xu.upload_type,'D',nvl(mctcdv.item_cost,0),decode(mmt.costed_flag,null,mmt.prior_cost,nvl(cql.item_cost,0)))) + decode(xu.upload_type,'D',mctcdv.value_change,mmt.value_change)) / cql.layer_quantity),2) - nvl(xu.old_valuation,0)
else null
end valuation_change,
--
(select gcck.concatenated_segments from gl_code_combinations_kfv gcck where gcck.code_combination_id = mmt.material_account) material_adj_account,
(select gcck.concatenated_segments from gl_code_combinations_kfv gcck where gcck.code_combination_id = mmt.material_overhead_account) material_overhead_adj_account,
(select gcck.concatenated_segments from gl_code_combinations_kfv gcck where gcck.code_combination_id = mmt.resource_account) resource_adj_account,
(select gcck.concatenated_segments from gl_code_combinations_kfv gcck where gcck.code_combination_id = mmt.outside_processing_account) outside_processing_adj_account,
(select gcck.concatenated_segments from gl_code_combinations_kfv gcck where gcck.code_combination_id = mmt.overhead_account) overhead_adj_account,
xu.default_adj_account,
--
(select mtr.reason_name from mtl_transaction_reasons mtr where mtr.reason_id = mmt.reason_id) reason,
mmt.transaction_reference reference,
--
clcdv.cost_element_id,
clcdv.level_type,
xu.upload_row
from
xxen_cst_item_average_c_1504_u xu,
mtl_material_transactions mmt,
(select
mctcdv.transaction_id,
sum(mctcdv.new_average_cost) new_average_cost
from
mtl_cst_txn_cost_details_v mctcdv
group by
mctcdv.transaction_id
) mctcdv_sum,
mtl_cst_txn_cost_details_v mctcdv,
cst_quantity_layers cql,
cst_layer_cost_details_v clcdv,
mtl_parameters mp,
mtl_system_items_vl msiv,
cst_cost_groups ccg
where
xu.status_code_=xxen_upload.status_success and
--
xu.transaction_date = mmt.transaction_date and
xu.source_line_id = mmt.source_line_id and
mmt.source_code = xxen_cst_upload.avg_cost_upd_trx_source_code and
--
mmt.transaction_id = mctcdv_sum.transaction_id and
decode(xu.upload_type,'D',mmt.transaction_id,null) = mctcdv.transaction_id(+) and
xu.cost_element_level = mctcdv.level_type_dsp (+) and
xu.cost_element = mctcdv.cost_element (+) and
--
mmt.organization_id = cql.organization_id (+) and
mmt.inventory_item_id = cql.inventory_item_id (+) and
mmt.cost_group_id = cql.cost_group_id (+) and
--
decode(xu.upload_type,'D',cql.layer_id,null) = clcdv.layer_id(+) and
xu.cost_element_level = clcdv.level_type_dsp (+) and
xu.cost_element = clcdv.cost_element (+) and
--
mmt.organization_id = mp.organization_id and
mmt.organization_id = msiv.organization_id and
mmt.inventory_item_id = msiv.inventory_item_id and
mmt.cost_group_id = ccg.cost_group_id
Default value:$column$.new_average_costNEW_AVERAGE_COSTADJUSTED_AVERAGE_COSTDefault value:$column$.old_average_costOLD_AVERAGE_COSTADJUSTED_AVERAGE_COSTDefault value:$column$.old_valuationOLD_VALUATIONADJUSTED_AVERAGE_COSTDefault value:$column$.percentage_changePERCENTAGE_CHANGEADJUSTED_AVERAGE_COSTDefault value:$column$.valued_quantityVALUED_QUANTITYADJUSTED_AVERAGE_COSTDefault value:$column$.value_changeVALUE_CHANGEADJUSTED_AVERAGE_COSTDefault value:$column$.new_average_costNEW_AVERAGE_COSTADJUSTED_VALUATIONDefault value:$column$.old_average_costOLD_AVERAGE_COSTADJUSTED_VALUATIONDefault value:$column$.old_valuationOLD_VALUATIONADJUSTED_VALUATIONDefault value:$column$.percentage_changePERCENTAGE_CHANGEADJUSTED_VALUATIONDefault value:$column$.valued_quantityVALUED_QUANTITYADJUSTED_VALUATIONDefault value:$column$.value_changeVALUE_CHANGEADJUSTED_VALUATIONLOV query:$column$.upload_typeUPLOAD_TYPECOST_ELEMENTLOV query:$column$.upload_typeUPLOAD_TYPECOST_ELEMENT_LEVELDefault value:$column$.organizationORGANIZATIONCOST_GROUPLOV query:$column$.organizationORGANIZATIONCOST_GROUPLOV query:$column$.organizationORGANIZATIONDEFAULT_ADJ_ACCOUNTDefault value:$flex$.Default_Adjustment_AccountDefault Adjustment Account|:p_default_adj_account|DEFAULT_ADJ_ACCOUNTDefault value:$column$.itemITEMDESCRIPTIONLOV query:$column$.itemITEMDESCRIPTIONDefault value:$column$.organizationORGANIZATIONDESCRIPTIONLOV query:$column$.organizationORGANIZATIONDESCRIPTIONLOV query:$column$.organizationORGANIZATIONITEMDefault value:$column$.cost_groupCOST_GROUPITEM_AVERAGE_COSTDefault value:$column$.itemITEMITEM_AVERAGE_COSTDefault value:$column$.organizationORGANIZATIONITEM_AVERAGE_COSTLOV query:$column$.organizationORGANIZATIONMATERIAL_ADJ_ACCOUNTLOV query:$column$.organizationORGANIZATIONMATERIAL_OVERHEAD_ADJ_ACCOUNTDefault value:$column$.cost_elementCOST_ELEMENTOLD_AVERAGE_COSTDefault value:$column$.cost_element_levelCOST_ELEMENT_LEVELOLD_AVERAGE_COSTDefault value:$column$.cost_groupCOST_GROUPOLD_AVERAGE_COSTDefault value:$column$.itemITEMOLD_AVERAGE_COSTDefault value:$column$.organizationORGANIZATIONOLD_AVERAGE_COSTDefault value:$column$.upload_typeUPLOAD_TYPEOLD_AVERAGE_COSTDefault value:$column$.cost_elementCOST_ELEMENTOLD_VALUATIONDefault value:$column$.cost_element_levelCOST_ELEMENT_LEVELOLD_VALUATIONDefault value:$column$.cost_groupCOST_GROUPOLD_VALUATIONDefault value:$column$.itemITEMOLD_VALUATIONDefault value:$column$.organizationORGANIZATIONOLD_VALUATIONDefault value:$column$.upload_typeUPLOAD_TYPEOLD_VALUATIONDefault value:$flex$.organization_codeOrganization Code|1=1|ORGANIZATIONLOV query:$column$.organizationORGANIZATIONOUTSIDE_PROCESSING_ADJ_ACCOUNTLOV query:$column$.organizationORGANIZATIONOVERHEAD_ADJ_ACCOUNTLOV query:$column$.transaction_typeTRANSACTION_TYPEREASONLOV query:$column$.organizationORGANIZATIONRESOURCE_ADJ_ACCOUNTDefault value:$flex$.Transaction_Source_NameTransaction Source Name|:p_default_source|SOURCEDefault value:$flex$.transaction_dateTransaction Date|:p_trx_date|TRANSACTION_DATEDefault value:$flex$.transaction_typeTransaction Type|:p_trx_type|TRANSACTION_TYPEDefault value:$flex$.upload_typeUpload Type|:p_upload_type|UPLOAD_TYPEDefault value:$column$.new_average_costNEW_AVERAGE_COSTVALUATION_CHANGEDefault value:$column$.old_average_costOLD_AVERAGE_COSTVALUATION_CHANGEDefault value:$column$.old_valuationOLD_VALUATIONVALUATION_CHANGEDefault value:$column$.percentage_changePERCENTAGE_CHANGEVALUATION_CHANGEDefault value:$column$.valued_quantityVALUED_QUANTITYVALUATION_CHANGEDefault value:$column$.value_changeVALUE_CHANGEVALUATION_CHANGEDefault value:$column$.cost_groupCOST_GROUPVALUED_QUANTITYDefault value:$column$.itemITEMVALUED_QUANTITYDefault value:$column$.organizationORGANIZATIONVALUED_QUANTITYExcel validation:$column$.cost_elementCOST_ELEMENTExcel validation:$column$.cost_element_levelCOST_ELEMENT_LEVELExcel validation:$column$.new_average_costNEW_AVERAGE_COSTValue to id query:$column$.organizationORGANIZATIONp_cost_group_idValue to id query:$column$.organizationORGANIZATIONp_default_adj_accountValue to id query:$column$.organizationORGANIZATIONp_inventory_item_idValue to id query:$column$.organizationORGANIZATIONp_material_accountValue to id query:$column$.organizationORGANIZATIONp_material_overhead_accountValue to id query:$column$.organizationORGANIZATIONp_outside_processing_accountValue to id query:$column$.organizationORGANIZATIONp_overhead_accountValue to id query:$column$.organizationORGANIZATIONp_resource_accountExcel validation:$column$.percentage_changePERCENTAGE_CHANGEValue to id query:$column$.transaction_typeTRANSACTION_TYPEp_reason_idValue to id query:$column$.upload_typeUPLOAD_TYPEp_cost_element_idValue to id query:$column$.upload_typeUPLOAD_TYPEp_cost_element_level_typeExcel validation:$column$.upload_typeUPLOAD_TYPEExcel validation:$column$.value_changeVALUE_CHANGE