Skip to main content

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_no

Meaning 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 @Harikrishnangr


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 @Harikrishnangr


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_no

Meaning 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 @Harikrishnangr


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

@Michael Kaiser you could do a query on ALL_SOURCE view:

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


 


Reply