[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
p_state,
p_depth,
Meaning,
p_icon,
To_char(lookup_code) -- mfg_lookups still has it as a number
FROM mfg_lookups
WHERE LOOKUP_TYPE = 'MRP_NODE_TYPE'
AND LOOKUP_CODE = To_char(p_lookup_code);
select CHAR1 into v_demand_class
from mrp_atp_details_temp
where record_type = 3
and pegging_id = p_pegging_id
and session_id = p_session_id;
' SELECT count(1) '||
' FROM mrp_atp_schedule_temp mast '||
' WHERE mast.session_id = :p_session_id '||
' AND exception'||col_num||' = 1';
SELECT
Decode(lookup_code,
0, Substr(meaning, 1,4),
1, Substr(meaning, 1,13),
2, Substr(meaning, 1,10),
3, Substr(meaning, 1,4),
10, Substr(meaning, 1,6),
15, Substr(meaning, 1,11),
30, Substr(meaning, 1,4),
35, Substr(meaning, 1,5),
40, Substr(meaning, 1,6),
45, Substr(meaning, 1,3),
50, Substr(meaning, 1,4),
60, Substr(meaning, 1,3),
70, Substr(meaning, 1,16),
75, Substr(meaning, 1,16),
80, Substr(meaning, 1,10),
85, Substr(meaning, 1,10),
90, Substr(meaning, 1,11),
100,Substr(meaning, 1,6),
110,Substr(meaning, 1,16),
111,Substr(meaning, 1,24), -- Material Constraint
112,Substr(meaning, 1,30), -- PTF constraint
113, Substr(meaning, 1,40), -- Manufacturing Constraint
114, Substr(meaning,1,36), -- Purchasing Constraint
115, Substr(meaning,1,34), -- Transfer Constraint
116, Substr(meaning,1,24), -- Resource Constraint
117, Substr(meaning,1,24), -- Calendar Constraint
119, Substr(meaning,1,40) -- Product Family Demand Spread
)
bulk collect INTO owb_tree.lookups
FROM mfg_lookups
WHERE
(lookup_type = 'MRP_SOURCE_TYPE' AND lookup_code IN ( 0, 1, 2, 3))
OR (lookup_type = 'MRP_ATP_FORM_TYPE' AND lookup_code IN
(10, 15, 30, 35, 40, 45, 50, 60, 70, 75, 80, 85, 90, 100, 111,110,112,113,114,115,116, 117,119))
ORDER BY lookup_code;
p_nodes.DELETE(1);
SELECT
Decode(inventory_item_name, NULL,
Decode(department_code, NULL,leaf_node,
Decode(supply_demand_type,1,
Decode(p_expand_level, next_level, collapsed,
all_levels, expanded,
constraint_level, decode(constrained_path, NULL, collapsed, expanded)
)
,leaf_node)),
-- Only TR, source are 1st lev
Decode(supply_demand_type,1,
Decode(p_expand_level, next_level, collapsed, all_levels, expanded,
constraint_level,decode(constrained_path, NULL, collapsed, expanded)),
Decode(source_type, NULL, leaf_node, 0, leaf_node, 3,
Decode(p_expand_level, next_level, collapsed, all_levels, expanded,
constraint_level, decode(constrained_path, NULL, collapsed, expanded)),
-- It will be collapsed if it is not a source_type node
-- (i.e. make etc)
-- It will be collapsed if it is a BUY from source
-- It will also be leaf_node for null or ATP
Decode(p_expand_level, next_level, collapsed,
all_levels, expanded,
constraint_level, decode(constrained_path, NULL, collapsed, expanded))))),
LEVEL-1,
Decode(inventory_item_name, NULL,
-- when it is null
Decode(department_code, NULL,
(ship_method||' - '||ROUND(supply_demand_quantity,6)||' '||uom_code||' - '
||from_organization_code
||Decode(from_location_code, NULL, '', '('||from_location_code||')')
||' '||owb_tree.lookups(10)||' '
||to_organization_code
||Decode(to_location_code, NULL, '', '('||to_location_code||')')),
-- dept is not null
Decode(resource_code, NULL,
-- Line
owb_tree.lookups(18)||'-'||department_code,
-- Resource
owb_tree.lookups(8)||'-'||department_code||':'||owb_tree.lookups(7)||'-'||resource_code)
||' '||owb_tree.lookups(11)||' '||ROUND(supply_demand_quantity,6)||' '||uom_code||' '||owb_tree.lookups(12)||' '||fnd_date.date_to_displaydate(supply_demand_date)),
-- when item_name is not null
inventory_item_name ||' -'||Decode(number1,1, owb_tree.lookups(19)||' ',' ')
||Decode(source_type, 1, owb_tree.lookups(2),2, owb_tree.lookups(3), 3, owb_tree.lookups(4),
0, owb_tree.lookups(1))
||' '||Decode(source_type, 3, supplier_name, nvl(supplier_name,organization_code)) ||
' '||supplier_site_name||
' '||owb_tree.lookups(11)||' '||ROUND(supply_demand_quantity,6)||' '||owb_tree.lookups(12)||' '
||fnd_date.date_to_displaydate(supply_demand_date)
) || ' '
||decode(constraint_type,null,null,
1, '{'||owb_tree.lookups(20)||'}',
2, '{'||owb_tree.lookups(21)||' '||fnd_date.date_to_displaydate(constraint_date)||'}',
3, '{'||owb_tree.lookups(22)||' ' ||fnd_date.date_to_displaydate(constraint_date)||'}',
4, '{'||owb_tree.lookups(23)||' '||fnd_date.date_to_displaydate(constraint_date)||'}',
5, '{'||owb_tree.lookups(24)||' ' || fnd_date.date_to_displaydate(constraint_date)||'}',
6, '{'||owb_tree.lookups(25)||'}',
7 , '{'||owb_tree.lookups(26)||'}')
||decode(char1,null, null, ' ('||get_cust_hier_string(char1)||')'),
decode(supply_demand_type,1, ICON_DEMAND,
decode(inventory_item_name, NULL,
decode(constraint_type,NULL,ICON_RESOURCE_CAP, -- Dept Res
ICON_RESOURCE_CAP_CRIT),
decode(source_type,0,
decode(constraint_type, NULL,ICON_RESOURCE_CAP,
ICON_RESOURCE_CAP_CRIT),
decode(constrained_path,NULL,ICON_RESOURCE_CAP,
ICON_RESOURCE_CAP_CRIT))
)
),
pegging_id data
bulk collect INTO
p_nodes.state,
p_nodes.depth,
p_nodes.label,
p_nodes.icon,
p_nodes.data
FROM mrp_atp_details_temp
where nonatp_flag is NULL
start WITH
session_id = p_session_id
AND record_type = 3
AND end_pegging_id = p_end_pegging_id
AND nvl(p_current_pegging_id, Nvl(parent_pegging_id, -1))
= Decode(p_current_pegging_id,NULL, -1, pegging_id)
connect by
PRIOR session_id = session_id
AND PRIOR record_type = record_type
AND PRIOR pegging_id = parent_pegging_id
AND PRIOR end_pegging_id = end_pegging_id
AND Decode(p_expand_level, NEXT_LEVEL, Nvl(p_current_pegging_id,-1),ALL_LEVELS, 1, CONSTRAINT_LEVEL,2)
= Decode(p_expand_level, NEXT_LEVEL, Nvl(parent_pegging_id, -1), ALL_LEVELS, 1,CONSTRAINT_LEVEL,2)
ORDER BY pegging_id;
SELECT
Decode(inventory_item_name, NULL,
Decode(department_code, NULL,leaf_node,
Decode(supply_demand_type,1,
Decode(p_expand_level, next_level, collapsed,
all_levels, expanded,
constraint_level,
decode(constrained_path, NULL, collapsed, expanded))
,leaf_node)),
-- Only TR, source are 1st lev
Decode(supply_demand_type,1,
Decode(p_expand_level, next_level, collapsed, all_levels, expanded,constraint_level,decode(constrained_path, NULL, collapsed, expanded)),
Decode(source_type, NULL, leaf_node, 0, leaf_node, 3,
Decode(p_expand_level, next_level, collapsed, all_levels, expanded,
constraint_level,
decode(constrained_path, NULL, collapsed, expanded)),
-- It will be collapsed if it is not a source_type node
-- (i.e. make etc)
-- It will be collapsed if it is a BUY from source
-- It will also be leaf_node for null or ATP
Decode(p_expand_level, next_level, collapsed,
all_levels, expanded,
constraint_level,
decode(constrained_path,NULL,collapsed, expanded))))),
DECODE(LEVEL, 1, 0, LEVEL/2),
Decode(inventory_item_name, NULL,
-- when it is null
Decode(department_code, NULL,
(ship_method||' - '||ROUND(supply_demand_quantity,6)||' '||uom_code||' - '
||from_organization_code
||Decode(from_location_code, NULL, '', '('||from_location_code||')')
||' '||owb_tree.lookups(10)||' '
||to_organization_code
||Decode(to_location_code, NULL, '', '('||to_location_code||')')),
-- dept is not null
Decode(resource_code, NULL,
-- Line
owb_tree.lookups(18)||'-'||department_code,
-- Resource
owb_tree.lookups(8)||'-'||department_code||':'||owb_tree.lookups(7)||'-'||resource_code)
||' '||owb_tree.lookups(11)||' '||ROUND(supply_demand_quantity,6)||' '||uom_code||' '||owb_tree.lookups(12)||' '||fnd_date.date_to_displaydate(supply_demand_date)),
-- when item_name is not null
inventory_item_name ||' -'||Decode(number1,1, owb_tree.lookups(19)||' ',' ')
||Decode(source_type, 1, owb_tree.lookups(2),2, owb_tree.lookups(3), 3, owb_tree.lookups(4),
0, owb_tree.lookups(1))
||' '||Decode(source_type, 3, supplier_name, nvl(supplier_name,organization_code))||
' '||supplier_site_name||
' '||owb_tree.lookups(11)||' '||ROUND(supply_demand_quantity,6)||' '||owb_tree.lookups(12)||' '
||fnd_date.date_to_displaydate(supply_demand_date)
) ||
' ' ||decode(constraint_type,null,null,
1 , '{'||owb_tree.lookups(20)||' '||fnd_date.date_to_displaydate(constraint_date) ||'}',
2, '{'||owb_tree.lookups(21)||' ' ||fnd_date.date_to_displaydate(constraint_date)||'}', 3, '{'||owb_tree.lookups(22)||' ' ||fnd_date.date_to_displaydate(constraint_date)||'}',
4, '{'||owb_tree.lookups(23)||' ' ||fnd_date.date_to_displaydate(constraint_date)||'}',
5, '{'||owb_tree.lookups(24)||' '||fnd_date.date_to_displaydate(constraint_date) ||'}',
6, '{'||owb_tree.lookups(25) || '}',
7 , '{'||owb_tree.lookups(26)||'}')
||decode(char1,null, null, ' ('||get_cust_hier_string(char1)||')'),
decode(supply_demand_type,1, ICON_DEMAND,
decode(inventory_item_name, NULL,
decode(constraint_type,NULL,ICON_RESOURCE_CAP, -- Dept Res
ICON_RESOURCE_CAP_CRIT),
decode(source_type,0,
decode(constraint_type, NULL,ICON_RESOURCE_CAP,
ICON_RESOURCE_CAP_CRIT),
decode(constrained_path,NULL,ICON_RESOURCE_CAP,
ICON_RESOURCE_CAP_CRIT))
)
),
pegging_id data
bulk collect INTO
p_nodes.state,
p_nodes.depth,
p_nodes.label,
p_nodes.icon,
p_nodes.data
FROM mrp_atp_details_temp
where supply_demand_type = Decode(parent_pegging_id, NULL, 1,2)
/*and decode(p_current_pegging_id, NULL, -1 , atp_level) = decode(p_current_pegging_id , NULL, -1, dummy)*/
start WITH
session_id = p_session_id
AND record_type = 3
AND end_pegging_id = p_end_pegging_id
AND nvl(p_current_pegging_id, Nvl(parent_pegging_id, -1)) = Decode(p_current_pegging_id,NULL, -1, pegging_id)
connect by
PRIOR session_id = session_id
AND PRIOR record_type = record_type
AND PRIOR pegging_id = parent_pegging_id
AND PRIOR end_pegging_id = end_pegging_id
/* AND Decode(p_expand_level, NEXT_LEVEL, decode(p_current_pegging_id,NULL,-1,-2),ALL_LEVELS, 1,CONSTRAINT_LEVEL,2) = Decode(p_expand_level,
NEXT_LEVEL, decode(parent_pegging_id,NULL, -1, -2), ALL_LEVELS, 1,CONSTRAINT_LEVEL,2)*/
/* AND Decode(p_expand_level, NEXT_LEVEL, Nvl(p_current_pegging_id,-1),ALL_LEVELS, 1) = Decode(p_expand_level, NEXT_LEVEL, Nvl(parent_pegging_id, -1), ALL_LEVELS, 1)*/
ORDER BY pegging_id;