Hi Community,
I need to get details from line part of screen 'inventory part availability planning'.
But when I search for the details from the view showed in system info. I get minimum details.
seems like it’s a temporary view.
view in system info is - ORDER_SUPP_DEM_EXT_TMP_VIEW
Where can I get full details (the view name), please help.
Best Regards,
Hari
Hi,
this is, like you say, a temporary view and the view name is the one you have found. It is generated when you search for a specific part and holds the data for a limited time. Data is fetched and calculated from several other views.
If you check the debugger, the select for the in tab All looks like this:
SELECT
date_required,
order_supply_demand_type,
status_desc,
qty_supply,
qty_demand,
qty_reserved,
qty_pegged,
qty_short,
NULL,
&ao.order_supply_demand_api.get_qty_plannable_fast_tmp__(:p0,
:p1,
:p2,
:p3,
trunc(date_required),
:p4,
:p5,
'ORDER_SUPPLY_DEMAND_EXT',
'FALSE',
'FALSE',
:p6),
NULL,
&ao.order_supply_demand_api.get_qty_plannable_fast_tmp__(:p7,
:p8,
:p9,
:p10,
trunc(date_required),
:p11,
:p12,
'ORDER_SUPPLY_DEMAND_EXT',
'TRUE',
'FALSE',
:p13),
NULL,
&ao.order_supply_demand_api.get_qty_plannable_fast_tmp__(:p14,
:p15,
:p16,
:p17,
trunc(date_required),
:p18,
:p19,
'ORDER_SUPPLY_DEMAND_EXT',
'FALSE',
'TRUE',
:p20),
NULL,
&ao.order_supply_demand_api.get_qty_plannable_fast_tmp__(:p21,
:p22,
:p23,
:p24,
trunc(date_required),
:p25,
:p26,
'ORDER_SUPPLY_DEMAND_EXT',
'TRUE',
'TRUE',
:p27),
&ao.order_supply_demand_api.get_qty_plannable_fast_tmp__(:p28,
:p29,
:p30,
:p31,
trunc(date_required),
:p32,
:p33,
'CUSTORD_SUPPLY_DEMAND',
'TRUE',
'FALSE',
:p34),
order_no,
line_no,
rel_no,
line_item_no,
info,
condition_code,
project_id,
&ao.project_api.get_name(project_id),
&ao.activity_api.get_sub_project_id(activity_seq),
&ao.activity_api.get_sub_project_description(activity_seq),
&ao.activity_api.get_activity_no(activity_seq),
&ao.activity_api.get_description(activity_seq),
activity_seq,
part_no,
contract
FROM
&ao.order_supp_dem_ext_tmp_view
WHERE
snapshot_id = :p35
ORDER BY
date_required,
decode(qty_demand, 0, 0, 1),
order_noMeaning for a lot of the columns, an API is used to fetch and calculate data. They all seem to be found in either table ORDER_SUPPLY_DEMAND_EXT or CUSTORD_SUPPLY_DEMAND.
Hi
I checked this myself. You are correct. The view is ORDER_SUPP_DEM_EXT_TMP_VIEW. This is built using supply_demand_info_tmp table.
The query used for the view is
SELECT order_no order_no,
line_no line_no,
rel_no rel_no,
line_item_no line_item_no,
order_supply_demand_type order_supply_demand_type,
part_no part_no,
contract contract,
configuration_id configuration_id,
qty_supply qty_supply,
qty_demand qty_demand,
qty_short qty_short,
qty_pegged qty_pegged,
qty_reserved qty_reserved,
condition_code condition_code,
status_code status_code,
date_required date_required,
project_id project_id,
activity_seq activity_seq,
info info,
status_desc status_desc,
DECODE(project_id, '*', 'TRUE', 'FALSE') include_standard,
DECODE(project_id, '*', 'FALSE', 'TRUE') include_project,
snapshot_id snapshot_id,
rowid objid
FROM supply_demand_info_tmp
WHERE (EXISTS (SELECT 1 FROM user_allowed_site_pub WHERE site = contract) OR
EXISTS (SELECT 1 FROM user_allowed_supply_site_pub WHERE supply_site = contract))
AND supply_demand_source = 'ORDER_SUPPLY_DEMAND_EXT'
ORDER BY date_required, DECODE(qty_demand,0,0,1), order_no, line_no, rel_no, line_item_no, order_supply_demand_type, project_id
WITH READ ONLY;
Best Regards,
Charana
Hello,
I also confirm. I think the only way to recreate the temp data is to do the similar SQL logic that is done when populating the data, because in this situation there is no option to create an event for the temp table - no table to select for the event:
but I’m not sure if it will be possible without customization.
Hello everybody,
just did a quick look by myself and also confirm.
On my customers ORACLE DB the table is empty (at the moment).
Quite astonishing: most of all the tables end with _TAB. This one with _TMP.
I’m very interested about the code which fills the table temporarily!
We had similar issues in other data structure and we would love to learn how IFS is “ticking”.
All the best
Michael
Hello everybody,
just did a quick look by myself and also confirm.
On my customers ORACLE DB the table is empty (at the moment).
Quite astonishing: most of all the tables end with _TAB. This one with _TMP.
I’m very interested about the code which fills the table temporarily!
We had similar issues in other data structure and we would love to learn how IFS is “ticking”.
All the best
Michael
Thank you Michael
Hello,
I also confirm. I think the only way to recreate the temp data is to do the similar SQL logic that is done when populating the data, because in this situation there is no option to create an event for the temp table - no table to select for the event:
but I’m not sure if it will be possible without customization.
Thank you Pobocha
Hi
I checked this myself. You are correct. The view is ORDER_SUPP_DEM_EXT_TMP_VIEW. This is built using supply_demand_info_tmp table.
The query used for the view is
SELECT order_no order_no,
line_no line_no,
rel_no rel_no,
line_item_no line_item_no,
order_supply_demand_type order_supply_demand_type,
part_no part_no,
contract contract,
configuration_id configuration_id,
qty_supply qty_supply,
qty_demand qty_demand,
qty_short qty_short,
qty_pegged qty_pegged,
qty_reserved qty_reserved,
condition_code condition_code,
status_code status_code,
date_required date_required,
project_id project_id,
activity_seq activity_seq,
info info,
status_desc status_desc,
DECODE(project_id, '*', 'TRUE', 'FALSE') include_standard,
DECODE(project_id, '*', 'FALSE', 'TRUE') include_project,
snapshot_id snapshot_id,
rowid objid
FROM supply_demand_info_tmp
WHERE (EXISTS (SELECT 1 FROM user_allowed_site_pub WHERE site = contract) OR
EXISTS (SELECT 1 FROM user_allowed_supply_site_pub WHERE supply_site = contract))
AND supply_demand_source = 'ORDER_SUPPLY_DEMAND_EXT'
ORDER BY date_required, DECODE(qty_demand,0,0,1), order_no, line_no, rel_no, line_item_no, order_supply_demand_type, project_id
WITH READ ONLY;
Best Regards,
Charana
Thank you Charana
Hi,
this is, like you say, a temporary view and the view name is the one you have found. It is generated when you search for a specific part and holds the data for a limited time. Data is fetched and calculated from several other views.
If you check the debugger, the select for the in tab All looks like this:
SELECT
date_required,
order_supply_demand_type,
status_desc,
qty_supply,
qty_demand,
qty_reserved,
qty_pegged,
qty_short,
NULL,
&ao.order_supply_demand_api.get_qty_plannable_fast_tmp__(:p0,
:p1,
:p2,
:p3,
trunc(date_required),
:p4,
:p5,
'ORDER_SUPPLY_DEMAND_EXT',
'FALSE',
'FALSE',
:p6),
NULL,
&ao.order_supply_demand_api.get_qty_plannable_fast_tmp__(:p7,
:p8,
:p9,
:p10,
trunc(date_required),
:p11,
:p12,
'ORDER_SUPPLY_DEMAND_EXT',
'TRUE',
'FALSE',
:p13),
NULL,
&ao.order_supply_demand_api.get_qty_plannable_fast_tmp__(:p14,
:p15,
:p16,
:p17,
trunc(date_required),
:p18,
:p19,
'ORDER_SUPPLY_DEMAND_EXT',
'FALSE',
'TRUE',
:p20),
NULL,
&ao.order_supply_demand_api.get_qty_plannable_fast_tmp__(:p21,
:p22,
:p23,
:p24,
trunc(date_required),
:p25,
:p26,
'ORDER_SUPPLY_DEMAND_EXT',
'TRUE',
'TRUE',
:p27),
&ao.order_supply_demand_api.get_qty_plannable_fast_tmp__(:p28,
:p29,
:p30,
:p31,
trunc(date_required),
:p32,
:p33,
'CUSTORD_SUPPLY_DEMAND',
'TRUE',
'FALSE',
:p34),
order_no,
line_no,
rel_no,
line_item_no,
info,
condition_code,
project_id,
&ao.project_api.get_name(project_id),
&ao.activity_api.get_sub_project_id(activity_seq),
&ao.activity_api.get_sub_project_description(activity_seq),
&ao.activity_api.get_activity_no(activity_seq),
&ao.activity_api.get_description(activity_seq),
activity_seq,
part_no,
contract
FROM
&ao.order_supp_dem_ext_tmp_view
WHERE
snapshot_id = :p35
ORDER BY
date_required,
decode(qty_demand, 0, 0, 1),
order_noMeaning for a lot of the columns, an API is used to fetch and calculate data. They all seem to be found in either table ORDER_SUPPLY_DEMAND_EXT or CUSTORD_SUPPLY_DEMAND.
Thank you Eljo
Hi
I checked this myself. You are correct. The view is ORDER_SUPP_DEM_EXT_TMP_VIEW. This is built using supply_demand_info_tmp table.
The query used for the view is
SELECT order_no order_no,
line_no line_no,
rel_no rel_no,
line_item_no line_item_no,
order_supply_demand_type order_supply_demand_type,
part_no part_no,
contract contract,
configuration_id configuration_id,
qty_supply qty_supply,
qty_demand qty_demand,
qty_short qty_short,
qty_pegged qty_pegged,
qty_reserved qty_reserved,
condition_code condition_code,
status_code status_code,
date_required date_required,
project_id project_id,
activity_seq activity_seq,
info info,
status_desc status_desc,
DECODE(project_id, '*', 'TRUE', 'FALSE') include_standard,
DECODE(project_id, '*', 'FALSE', 'TRUE') include_project,
snapshot_id snapshot_id,
rowid objid
FROM supply_demand_info_tmp
WHERE (EXISTS (SELECT 1 FROM user_allowed_site_pub WHERE site = contract) OR
EXISTS (SELECT 1 FROM user_allowed_supply_site_pub WHERE supply_site = contract))
AND supply_demand_source = 'ORDER_SUPPLY_DEMAND_EXT'
ORDER BY date_required, DECODE(qty_demand,0,0,1), order_no, line_no, rel_no, line_item_no, order_supply_demand_type, project_id
WITH READ ONLY;
Best Regards,
Charana
Thank you Charana
Hello everybody,
just did a quick look by myself and also confirm.
On my customers ORACLE DB the table is empty (at the moment).
Quite astonishing: most of all the tables end with _TAB. This one with _TMP.
I’m very interested about the code which fills the table temporarily!
We had similar issues in other data structure and we would love to learn how IFS is “ticking”.
All the best
Michael
SELECT *
FROM ALL_source
WHERE UPPER(text) LIKE UPPER('%supply_demand_info_tmp%')Then you could see that the table is being populated by ORDER_SUPPLY_DEMAND_API
Have fun! :)
Hi Marcel,
great stuff. Tried it and after 4 minutes get the same results you screenshoted.
Meanwhile we worked around the TMP problem.
Found other sources where we can show holiday “forecast”.
Looking forward to read more from you.
Thanks again.
Michael
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.