[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pqt.DETAIL_QUERY_ID query_id,mpq.query_type
FROM MSC_PQ_TYPES pqt,
msc_personal_queries mpq
WHERE pqt.query_id = p_query_id
AND mpq.query_id = pqt.DETAIL_QUERY_ID;
SELECT 1
FROM MSC_PQ_TYPES pqt
WHERE pqt.query_id = p_query_id
AND pqt.DETAIL_QUERY_ID IS NULL;
SELECT curr_plan_type
FROM msc_plans
WHERE plan_id = v_plan_id;
SELECT transaction_id
FROM msc_orders_v
WHERE plan_id = p_plan_id
AND sr_instance_id = p_instance_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND trunc(new_due_date) >= trunc(p_from_date)
AND trunc(new_due_date) < trunc(p_to_date);*/
SELECT sup.transaction_id
--,sup.new_schedule_date new_due_date
FROM msc_supplies sup,
msc_system_items msi ,
msc_item_categories mic
WHERE mic.sr_instance_id = sup.sr_instance_id
AND mic.organization_id = sup.organization_id
AND mic.inventory_item_id = sup.inventory_item_id
AND
sup.plan_id = msi.plan_id
AND sup.sr_instance_id = msi.sr_instance_id
AND sup.organization_id = msi.organization_id
AND sup.inventory_item_id = msi.inventory_item_id
AND
sup.plan_id = p_plan_id
AND sup.sr_instance_id = p_instance_id
AND sup.organization_id = p_organization_id
AND sup.inventory_item_id = p_inventory_item_id
AND sup.new_schedule_date >= p_from_date
AND sup.new_schedule_date < p_to_date;
SELECT dem.demand_id transaction_id
--,dem.using_assembly_demand_date new_due_date
FROM msc_demands dem,
msc_system_items msi,
msc_item_categories mic
WHERE mic.sr_instance_id = dem.sr_instance_id
AND mic.organization_id = dem.organization_id
AND mic.inventory_item_id = dem.inventory_item_id
AND
dem.plan_id = msi.plan_id
AND dem.sr_instance_id = msi.sr_instance_id
AND dem.organization_id = msi.organization_id
AND dem.inventory_item_id = msi.inventory_item_id
AND dem.origination_type <> 52
AND
dem.plan_id = p_plan_id
AND dem.sr_instance_id = p_instance_id
AND dem.organization_id = p_organization_id
AND dem.inventory_item_id = p_inventory_item_id
AND dem.using_assembly_demand_date >= p_from_date
AND dem.using_assembly_demand_date < p_to_date;
SELECT mso.demand_id transaction_id
--,mso.requirement_date new_due_date
FROM msc_sales_orders mso,
msc_system_items msi ,
msc_item_categories mic
WHERE mso.sr_instance_id = mic.sr_instance_id
AND mso.inventory_item_id = mic.inventory_item_id
AND mso.organization_id = mic.organization_id
AND
mso.sr_instance_id = msi.sr_instance_id
AND mso.inventory_item_id = msi.inventory_item_id
AND mso.organization_id = msi.organization_id
AND mso.reservation_type = 1
--AND msi.plan_id = -1
AND
msi.plan_id = p_plan_id
AND mso.sr_instance_id = p_instance_id
AND mso.organization_id = p_organization_id
AND mso.inventory_item_id = p_inventory_item_id
AND mso.requirement_date >= p_from_date
AND mso.requirement_date < p_to_date;
SELECT jro.transaction_id
--,jro.reco_date_required new_due_date
FROM msc_job_requirement_ops jro,
msc_system_items msi ,
msc_item_categories mic
WHERE mic.sr_instance_id = jro.sr_instance_id
AND mic.organization_id = jro.organization_id
AND mic.inventory_item_id = jro.component_item_id
--AND jro.plan_id = -1
AND jro.plan_id = msi.plan_id
AND jro.sr_instance_id = msi.sr_instance_id
AND jro.organization_id = msi.organization_id
AND jro.component_item_id = msi.inventory_item_id
AND
jro.plan_id = p_plan_id
AND jro.sr_instance_id = p_instance_id
AND jro.organization_id = p_organization_id
AND jro.component_item_id = p_inventory_item_id
AND jro.reco_date_required >= p_from_date
AND jro.reco_date_required < p_to_date;
SELECT sup.transaction_id
--,nvl(sup.new_ship_date,sup.new_schedule_date) new_due_date
FROM msc_supplies sup,
msc_system_items msi ,
msc_item_categories mic,
msc_plans mp
WHERE mic.sr_instance_id = sup.sr_instance_id
AND mic.organization_id = sup.organization_id
AND mic.inventory_item_id = sup.inventory_item_id
AND
sup.plan_id = msi.plan_id
AND sup.sr_instance_id = msi.sr_instance_id
AND sup.organization_id = msi.organization_id
AND sup.inventory_item_id = msi.inventory_item_id
AND mp.plan_id = sup.plan_id
AND mp.plan_type = 5
AND sup.order_type = 51
AND
sup.plan_id = p_plan_id
AND sup.sr_instance_id = p_instance_id
AND sup.organization_id = p_organization_id
AND sup.inventory_item_id = p_inventory_item_id
AND nvl(sup.new_ship_date,sup.new_schedule_date) >= p_from_date
AND nvl(sup.new_ship_date,sup.new_schedule_date) < p_to_date;
SELECT dem.demand_id transaction_id
--,nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date) new_due_date
FROM msc_demands dem,
msc_plans mp,
msc_system_items msi ,
msc_item_categories mic
WHERE mic.sr_instance_id = dem.sr_instance_id
AND mic.organization_id = dem.organization_id
AND mic.inventory_item_id = dem.inventory_item_id
AND
dem.plan_id = msi.plan_id
AND dem.sr_instance_id = msi.sr_instance_id
AND dem.organization_id = msi.organization_id
AND dem.inventory_item_id = msi.inventory_item_id
AND mp.plan_id = dem.plan_id
AND mp.plan_type = 5
AND (( dem.origination_type = 1
AND dem.source_organization_id <> dem.organization_id )
OR
( dem.origination_type = 30
AND dem.demand_source_type = 8 ))
AND
dem.plan_id = p_plan_id
AND dem.sr_instance_id = p_instance_id
AND dem.organization_id = p_organization_id
AND dem.inventory_item_id = p_inventory_item_id
AND nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date) >= p_from_date
AND nvl(dem.planned_inbound_due_date , dem.using_assembly_demand_date) < p_to_date;
SELECT dem.demand_id transaction_id
--,nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date) new_due_date
FROM msc_demands dem,
msc_plans mp,
msc_system_items msi ,
msc_item_categories mic
WHERE mic.sr_instance_id = dem.sr_instance_id
AND mic.organization_id = dem.organization_id
AND mic.inventory_item_id = dem.inventory_item_id
AND
dem.plan_id = msi.plan_id
AND dem.sr_instance_id = msi.sr_instance_id
AND dem.organization_id = msi.organization_id
AND dem.inventory_item_id = msi.inventory_item_id
AND mp.plan_id = dem.plan_id
AND mp.plan_type = 5
AND dem.origination_type = 30
AND dem.demand_source_type = 8
AND
dem.plan_id = p_plan_id
AND dem.sr_instance_id = p_instance_id
AND dem.organization_id = p_organization_id
AND dem.inventory_item_id = p_inventory_item_id
AND nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date) >= p_from_date
AND nvl(dem.old_using_assembly_demand_date , dem.using_assembly_demand_date) < p_to_date;
SELECT distinct supply_id
FROM msc_supplier_requirements
WHERE plan_id = p_plan_id
AND sr_instance_id = p_sr_instance_id
AND supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id
AND inventory_item_id = p_inventory_item_id
AND trunc(consumption_date) = trunc(p_consumption_date);
SELECT demand_id
FROM msc_demands
WHERE plan_id = p_plan_id
AND sr_instance_id = p_sr_instance_id
AND group_id IN (SELECT group_id
FROM msc_demands
WHERE plan_id = p_plan_id
AND sr_instance_id = p_sr_instance_id
AND demand_id = p_demand_id);
SELECT md.demand_id
FROM msc_exception_details med,
msc_demands md
WHERE med.plan_id = md.plan_id
AND med.exception_type = 84
AND med.exception_detail_id = p_excp_id
AND med.plan_id = p_plan_id
AND ( med.number1= md.demand_id
OR med.number1 = md.original_demand_id);
SELECT supply_id
FROM msc_resource_requirements
WHERE plan_id = p_plan_id
AND sr_instance_id = p_inst_id
AND organization_id = p_org_id
AND department_id = p_dept_id
AND resource_id = p_res_id;
SELECT supply_id,transaction_id
FROM msc_resource_requirements
WHERE plan_id = p_plan_id
AND sr_instance_id = p_inst_id
AND organization_id = p_org_id
AND department_id = p_dept_id
AND resource_id = p_res_id
AND supply_id = p_supply_id
AND nvl(operation_seq_num,-1) = nvl(p_op_seq, nvl(operation_seq_num,-1))
AND nvl(resource_seq_num,-1) = nvl(p_res_seq, nvl(resource_seq_num,-1))
AND parent_id = 2;
SELECT supply_id
FROM msc_resource_requirements
WHERE plan_id = p_plan_id
AND sr_instance_id = p_inst_id
AND organization_id = p_org_id
AND department_id = p_dept_id
AND resource_id = p_res_id
AND ( ( trunc(start_date) >= p_from_date
AND NVL(trunc(end_date),p_to_date) <= p_to_date)
OR ( p_from_date BETWEEN trunc(start_date)
AND NVL(trunc(end_date),p_to_date))
OR ( p_to_date BETWEEN trunc(start_date) AND NVL(trunc(end_date),p_to_date))
OR ( trunc(start_date) <= p_from_date
AND NVL(trunc(end_date),p_to_date) >= p_to_date) );
SELECT supply_id
FROM msc_resource_requirements r,
msc_supplies s,
msc_system_items i
WHERE r.plan_id = p_plan_id
AND r.sr_instance_id = p_inst_id
AND r.organization_id = p_org_id
AND r.department_id = p_dept_id
AND r.resource_id = p_res_id
AND s.plan_id = r.plan_id
AND s.transaction_id = r.supply_id
AND s.plan_id = i.plan_id
AND s.sr_instance_id = i.sr_instance_id
AND s.organization_id = i.organization_id
AND s.inventory_item_id = i.inventory_item_id
AND trunc(s.need_by_date - (i.fixed_lead_time + (i.variable_lead_time*s.new_order_quantity)))
BETWEEN p_from_date AND p_to_date;
SELECT transaction_id
FROM msc_full_pegging
WHERE plan_id = p_plan_id
AND end_pegging_id = p_end_peg_id;
SELECT trunc(bkt_start_date), trunc(bkt_end_date)
FROM msc_plan_buckets
WHERE plan_id = p_plan_id
AND p_date between bkt_start_date and bkt_end_date;
l_sql_stmt := ' SELECT exception_id , sr_instance_Id, organization_id, '||
' inventory_item_id, supplier_id , supplier_site_id, '||
' transaction_id , demand_id , exception_type, '||
' from_date , to_date, budget_violation_date, '||
' department_id , resource_id, end_pegging_id, '||
' operation_seq_num, resource_seq_num '||
' FROM msc_exception_details_v med '||
' WHERE med.plan_id = :plan_id '||
' AND nvl(med.category_set_id,2) = :category_set_id ';
mrp_exception_details.g_resource_req_rows_selected := mrp_exception_details.g_resource_req_rows_selected + 1;
v_insert_stmt VARCHAR2(2000);
INSERT INTO MSC_FORM_QUERY
(QUERY_ID, NUMBER1, NUMBER2, NUMBER3,
LAST_UPDATE_DATE, LAST_UPDATED_BY ,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN )
VALUES (g_query_id, g_obj_sequence_id, g_sequence_id, l_list(i),
SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.user_id,
fnd_global.login_id);
INSERT INTO MSC_FORM_QUERY
(QUERY_ID, NUMBER1, NUMBER2, NUMBER4,
LAST_UPDATE_DATE, LAST_UPDATED_BY ,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN )
VALUES (g_query_id, g_obj_sequence_id, g_sequence_id, l_item_list(i),
SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.user_id,
fnd_global.login_id);
SELECT field_name,
field_type,
condition,
low_value,
high_value,
hidden_from_field,
data_set,
source_type,
object_type,
lov_type,
sequence,
object_sequence_id
FROM msc_selection_criteria_v
WHERE folder_id = p_query_id
AND active_flag = 1
AND condition IS NOT NULL
AND source_type = p_source_type
AND object_type = p_object_type;
SELECT COUNT(*)
FROM msc_pq_types
WHERE query_id = p_query_id
AND source_type = p_source_type
AND object_type = p_object_type
AND NVL(and_or_flag,1) = 1;
SELECT field_name,
field_type,
condition,
DECODE(field_name , 'PLANNING_MAKE_BUY_CODE',
hidden_from_field, low_value ) low_value,
high_value,
hidden_from_field,
data_set,
source_type,
object_type,
lov_type,
sequence,
object_sequence_id
FROM msc_selection_criteria_v
WHERE folder_id = p_query_id
AND active_flag = 1
AND condition IS NOT NULL
ORDER BY source_type, object_type, field_name;
SELECT field_name,
field_type,
condition,
low_value,
high_value,
hidden_from_field,
data_set,
source_type,
object_type,
lov_type,
sequence,
object_sequence_id
FROM msc_selection_criteria_v
WHERE folder_id = p_query_id
AND active_flag = 1
AND condition IS NOT NULL
AND source_type = p_source_type
AND object_type = p_object_type;
SELECT DISTINCT object_type
FROM msc_selection_criteria_v
WHERE folder_id = p_query_id
AND active_flag = 1
AND source_type = p_source_type;
SELECT count(*)
FROM msc_personal_queries
WHERE query_id = p_query_id
AND NVL(and_or_flag,1) = 1;
SELECT COUNT(*)
FROM msc_pq_types
WHERE query_id = p_query_id
AND source_type = p_source_type
AND object_type = p_object_type
AND NVL(and_or_flag,1) = 1;
SELECT decode(field_name,'CUSTOMER_SITE','CUSTOMER_SITE_NAME',field_name) field_name,
field_type,
condition,
low_value,
high_value,
hidden_from_field,
data_set,
source_type,
object_type,
lov_type,
sequence,
object_sequence_id
FROM msc_selection_criteria_v
WHERE folder_id = p_query_id
AND active_flag = 1
AND condition IS NOT NULL
AND source_type = p_source_type
AND object_type = p_object_type
AND object_sequence_id = p_sequence_id;
SELECT object_type,
source_type,
sequence_id,
and_or_flag
FROM msc_pq_types
WHERE query_id = p_query_id
AND active_flag = 1
ORDER BY sequence_id;
SELECT msc.folder_object,
mav.sequence,
mav.object_sequence,
mav.field_name,
DECODE(msc.field_type,
'DATE', fnd_date.date_to_displaydate(
fnd_date.canonical_to_date(mav.or_values)),
'NUMBER', DECODE(mc.lov_type,
1, TO_CHAR(
fnd_number.canonical_to_number(mav.or_values)),
mav.or_values),
mav.or_values) or_values,
mav.hidden_values
FROM msc_among_values mav,
msc_selection_criteria msc,
msc_criteria mc
WHERE mav.folder_id = p_folder_id
AND msc.folder_id=mav.folder_id
AND mc.folder_object =msc.folder_object
AND mc.field_name = msc.field_name
AND msc.sequence = mav.sequence
AND nvl(msc.object_sequence_id,-1) = nvl(mav.object_sequence,-1);
CURSOR c_delete IS
SELECT distinct field_name
FROM msc_among_values
WHERE folder_id = p_folder_id;
/*OPEN c_delete;
FETCH c_delete into l_name;
EXIT WHEN c_delete%notfound;
delete_rows(l_name);
CLOSE c_delete;*/
v_delete_stmt VARCHAR2(2000);
/*v_excp_str := ' SELECT DISTINCT '||p_query_id||', '||p_obj_sequence_id||', '||p_sequence_id ||
', med.TRANSACTION_ID, med.INVENTORY_ITEM_ID ' ||
', med.SR_INSTANCE_ID,med.ORGANIZATION_ID ' ||
', SYSDATE, fnd_global.user_id,SYSDATE, fnd_global.user_id, fnd_global.login_id'||
' FROM MSC_EXCEPTION_DETAILS_V med ';*/
/*v_excp_str := ' SELECT DISTINCT '||p_query_id||', '||p_obj_sequence_id||', '||p_sequence_id ||
', med.TRANSACTION_ID, med.INVENTORY_ITEM_ID ' ||
', SYSDATE, fnd_global.user_id,SYSDATE, fnd_global.user_id, fnd_global.login_id'||
' FROM MSC_EXCEPTION_DETAILS_V med ';*/
/*v_insert_stmt := ' INSERT INTO MSC_FORM_QUERY (QUERY_ID,'||
' NUMBER1,NUMBER2,CHAR1,NUMBER3,' ||
' NUMBER4,NUMBER5,' ||
' LAST_UPDATE_DATE, LAST_UPDATED_BY , ' ||
' CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN )';*/
/*v_insert_stmt := ' INSERT INTO MSC_FORM_QUERY (QUERY_ID,'||
' NUMBER1,NUMBER2,NUMBER3,NUMBER4,' ||
' LAST_UPDATE_DATE, LAST_UPDATED_BY , ' ||
' CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN )';*/
v_delete_stmt := 'DELETE MSC_FORM_QUERY WHERE QUERY_ID = :p_query_id'
||' AND NUMBER1 = :p_obj_sequence_id AND NUMBER2 = :p_sequence_id';
execute immediate v_delete_stmt using p_query_id, p_obj_sequence_id, p_sequence_id;
' TRANSACTION_ID IN (SELECT NUMBER3 FROM MSC_FORM_QUERY '||
' WHERE QUERY_ID = '||p_query_id ||
' AND NUMBER1 = '||p_obj_sequence_id||
' AND NUMBER2 = '|| p_sequence_id ||
' AND NUMBER3 IS NOT NULL' ||')' ||
' OR '||
' INVENTORY_ITEM_ID '||
' IN (SELECT NUMBER4 FROM MSC_FORM_QUERY '||
' WHERE QUERY_ID = '||p_query_id||
' AND NUMBER1 = '||p_obj_sequence_id||
' AND NUMBER2 = '|| p_sequence_id||
' AND NUMBER4 IS NOT NULL)'||
')';
' TRANSACTION_ID IN (SELECT NUMBER3 FROM MSC_FORM_QUERY '||
' WHERE QUERY_ID = '||p_query_id ||
' AND NUMBER1 = '||p_obj_sequence_id||
' AND NUMBER2 = '|| p_sequence_id ||
' AND NUMBER3 IS NOT NULL' ||')' ||
')';
g_among_values.delete;
PROCEDURE delete_rows(p_field_name in varchar2) IS
total_rows NUMBER;
deleted_rows NUMBER:=0;
IF g_among_values.exists(counter - deleted_rows) THEN
l_cur_field_name := g_among_values(counter - deleted_rows).FIELD_NAME;
g_among_values.delete(counter - deleted_rows);
deleted_rows :=deleted_rows+1;
END delete_rows;
SELECT count(*)
FROM msc_selection_criteria msc,
msc_personal_queries mpq
WHERE (mpq.query_id = msc.folder_id
AND mpq.query_id = p_query_id and msc.active_flag=1)
AND ( ( mpq.query_type = 1
AND field_name in ('BUYER_NAME', 'ITEM_SEGMENTS',
'ABC_CLASS_NAME','CATEGORY',
'ORGANIZATION_CODE', 'PLANNER_CODE') ) --item
OR( mpq.query_type = 2
AND field_name in ('RESOURCE_CODE', 'ORGANIZATION_CODE', 'DEPARTMENT_LINE_CODE') ) --res
OR( (mpq.query_type = 4 and source_type = 1)
AND ( field_name like '%ORGANIZATION_CODE%'
OR field_name like '%PLANNER_CODE%'
OR field_name like '%RESOURCE_CODE%'
OR field_name like '%DEPARTMENT_CODE%'
OR field_name like '%ITEM_SEGMENTS%'
OR field_name like '%CATEGORY_NAME%'
OR field_name like '%ITEM_NAME%') ) --excp
OR ( (mpq.query_type = 4 and source_type = 2))
OR ( mpq.query_type = 5
AND field_name in ('ORGANIZATION_CODE', 'ITEM_NAME',
'CATEGORY_NAME', 'SUPPLIER_NAME',
'BUYER_NAME', 'PLANNER_CODE') ) --supplier
OR (mpq.query_type = 6)); --loads
SELECT mpt.source_type, mpt.object_type, mpt.sequence_id
FROM msc_pq_types mpt
WHERE mpt.query_id = p_query_id
AND mpt.active_flag = 1;
SELECT COUNT(*)
FROM msc_selection_criteria msc,
msc_pq_types mpt,
msc_personal_queries mpq
WHERE (mpq.query_id = mpt.query_id
AND mpq.query_id = p_query_id
AND mpt.active_flag = 1
AND msc.folder_id = mpt.query_id
AND msc.source_type = mpt.source_type
AND msc.object_type = mpt.object_type
AND msc.object_sequence_id = mpt.sequence_id
AND msc.active_flag=1
AND mpt.source_type = P_source_type
AND mpt.object_type = P_object_type
AND mpt.sequence_id = P_sequence_id)
AND ( msc.field_name like '%ORGANIZATION_CODE%'
OR msc.field_name like '%PLANNER_CODE%'
OR msc.field_name like '%ITEM_SEGMENTS%'
OR msc.field_name like '%CATEGORY_NAME%'
OR msc.field_name like '%ITEM_NAME%');--orders
SELECT count(*)
FROM msc_selection_criteria msc,
msc_personal_queries mpq
WHERE mpq.query_id = msc.folder_id
AND mpq.query_id = p_query_id
AND msc.active_flag = 1
AND mpq.query_type = 10
AND (( source_type = 1
AND ( field_name like '%ORGANIZATION_CODE%'
OR field_name like '%PLANNER_CODE%'
OR field_name like '%RESOURCE_CODE%'
OR field_name like '%DEPARTMENT_CODE%'
OR field_name like '%ITEM_SEGMENTS%'
OR field_name like '%CATEGORY_NAME%'
OR field_name like '%ITEM_NAME%') ) --excp
OR source_type = 2);
SELECT distinct field_name
FROM msc_selection_criteria
WHERE folder_id = p_query_id
AND NVL(count_by,2) = 1;
PROCEDURE delete_from_results_table(p_query_id IN NUMBER,
p_plan_id IN NUMBER) IS
BEGIN
--KSA_DEBUG(SYSDATE,' p_query_id <> '||p_query_id,'delete_from_results_table');
DELETE msc_pq_results
WHERE query_id = p_query_id
AND plan_id = p_plan_id ;
END delete_from_results_table;
SELECT query_name
FROM msc_personal_queries
WHERE query_id = p_query;
SELECT compile_designator
FROM msc_plans
WHERE plan_id = -1;
delete_from_results_table(p_query_id,
p_plan_id);
SELECT DECODE(apps_ver,3,NVL(allow_release_flag ,2),
4,NVL(allow_release_flag ,2),1) allow_release_flag,
instance_code
FROM msc_apps_instances
WHERE instance_id = nvl(p_sr_instance_id,-1);
SELECT plq.query_Id,pq.query_type
FROM msc_plan_queries plq,
msc_personal_queries pq
WHERE plq.plan_id = p_plan_Id
AND plq.query_id = pq.query_id;
SELECT sr_instance_id,organization_id, organization_code
FROM msc_orders_v
WHERE plan_id = p_plan_Id
AND p_transaction_Id = p_transaction_Id;
SELECT AUTO_RELEASE_METHOD
FROM msc_plans
WHERE plan_id = p_plan_Id;
l_stmt := 'SELECT DISTINCT TRANSACTION_ID FROM MSC_PQ_RESULTS '||
'WHERE QUERy_ID IN '||l_lIST;
SELECT plq.query_Id,QUERY_TYPE
FROM msc_plan_queries plq,
msc_personal_queries pq
WHERE plq.plan_id = p_plan_Id
AND plq.query_id = pq.query_id
AND pq.query_type = 10;
SELECT nvl(pqt.DETAIL_QUERY_ID, pqt.query_id) query_id
FROM MSC_PQ_TYPES pqt
WHERE pqt.query_id = p_query_id
and pqt.PRIORITY = p_priority;
select distinct priority
from msc_pq_results
where plan_id = p_plan_id
and query_id = p_query_id
and SUMMARY_DATA = 1;
update msc_pq_results
set DETAIL_QUERY_ID = l_query_id
where plan_id = p_plan_id
and query_id = p_query_id
and SUMMARY_DATA = 1
and priority = rec_top_actions.priority;
p_select_clause OUT NOCOPY VARCHAR2,
p_from_clause OUT NOCOPY VARCHAR2,
p_where_clause OUT NOCOPY VARCHAR2,
P_column_prompts OUT NOCOPY VARCHAR2,
p_select_clause_dmd OUT NOCOPY VARCHAR2,
p_from_clause_dmd OUT NOCOPY VARCHAR2,
p_where_clause_dmd OUT NOCOPY VARCHAR2,
p_return_code OUT NOCOPY VARCHAR2
)
IS
/***Filter***/
CURSOR c_criteria (p_folder_object VARCHAR2)
IS
SELECT field_name ,
data_set
FROM msc_selection_criteria_v
WHERE folder_id=p_query_id
AND active_flag =1
AND condition IS NOT NULL
AND folder_object =p_folder_object
ORDER BY field_name ,
data_set ;
SELECT DISTINCT FOLDER_objecT
FROM msc_selection_criteria_v
WHERE folder_id=p_query_id
AND active_flag =1
AND FOLDER_objecT LIKE 'MSC_WRKSH_FLTR%'
AND condition IS NOT NULL
ORDER BY FOLDER_objecT;
SELECT mc.folder_object,
mc.field_name ,
mc.sql_statement ,
mc.sql_statement2 ,
fl.meaning
FROM msc_selection_criteria_v mc,
fnd_lookups fl
WHERE mc.folder_id=p_query_id
AND mc.active_flag =1
AND mc.condition IS NULL
AND folder_object =p_folder_object
AND mc.field_name =fl.lookup_code
AND mc.folder_object =fl.lookup_type
ORDER BY mc.folder_object ,
mc.field_name ;
SELECT DISTINCT FOLDER_objecT
FROM msc_selection_criteria_v
WHERE folder_id=p_query_id
AND active_flag =1
AND FOLDER_objecT LIKE 'MSC_WRKSH_RSLT%'
AND condition IS NULL
ORDER BY FOLDER_objecT;
SELECT field_name ,
hidden_from_field,
from_field,folder_object
FROM msc_selection_criteria
WHERE folder_id=p_query_id
AND active_flag =1
AND condition IS NULL
AND folder_object ='MSC_WRKSH_FLTR_OTHER'
ORDER BY field_name ;
select_clause_segment VARCHAR2(32000):= NULL;
select_clause_dmd VARCHAR2(32000):= NULL;
select_clause_dmd2 VARCHAR2(32000):= NULL;
/***select**/
FOR c_f_criteria_row_2 IN c_folder_criteria_2
LOOP
FOR c_criteria_row_2 IN c_criteria_2(c_f_criteria_row_2.fOLDER_object)
LOOP
IF j=1 THEN
NULL;
select_clause_segment:=' SELECT ';
select_clause_dmd:=' UNION ALL SELECT ';
select_clause_segment :=select_clause_segment||'|'||'|'||g_delimiter||'|'||'|';
select_clause_dmd :=select_clause_dmd||'|'||'|'||g_delimiter||'|'||'|';
select_clause_segment :=select_clause_segment||l_sql_statement_2;
select_clause_dmd :=select_clause_dmd||l_sql_statement_dmd;
/***select end**/
/***Other start***/
FOR c_criteria_other_row IN c_criteria_other
LOOP
l_field_name_3 := c_criteria_other_row.field_name ;
/***Common joins for Result or Filter tab selection of entity*/
IF entity_segment LIKE '%ITEM%' THEN
IF l_item_flag ='N' THEN
from_clause_segment :=from_clause_segment||',msc_system_items i';
/***Common joins for Result or Filter tab selection of entity- end*/
where_clause_dmd2:=where_clause;
/***select demands**/
IF (entity_segment NOT LIKE '%DEMAND%') and (entity_segment NOT LIKE '%WHERE_USED%') AND
(entity_segment <> 'ITEM:') and (entity_segment <> 'ENDASSEMBLY:') then
IF where_clause IS NOT NULL THEN
where_clause_dmd2:=REPLACE(REPLACE(where_clause_dmd2,'sup.FIRM_PLANNED_TYPE','decode(dmd.ORG_FIRM_FLAG,1,1,3,2,2)'),'sup.NEW_SCHEDULE_DATE','dmd.USING_ASSEMBLY_DEMAND_DATE');
p_select_clause_dmd :=select_clause_dmd;
p_select_clause_dmd :=NULL;
p_select_clause :=select_clause_segment;
IF LTRIM(select_clause_segment,' ') IS NULL THEN
l_return_code:=l_return_code||' MSC_RESULTLAYOUT_MSG';
select_clause VARCHAR2(32000) :=NULL;
select_clause_dmd VARCHAR2(32000) :=NULL;
SELECT COMPILE_DESIGNATOR INTO plan_name FROM MSC_PLANS WHERE plan_id=l_plan_id;
SELECT COUNT(1) INTO count_row FROM fnd_lobs WHERE file_name=filename;
DELETE FROM fnd_lobs WHERE file_name=filename;
select count(1) into count_row1 from msc_plan_queries
where plan_id=l_plan_id
and query_id=l_query_id;
l_return_code:=l_return_code||' Error getting file id (for delete) in msc_plan_queries for file name '|| filename||' '||SQLERRM;
update msc_plan_queries
set file_id=NULL,
LAST_UPDATED_BY=fnd_global.user_id,
LAST_UPDATE_DATE =SYSDATE,
LAST_UPDATE_LOGIN=fnd_global.user_id
where plan_id=l_plan_id
and query_id=l_query_id;
l_return_code:=l_return_code||' Error updating file id (for delete) in msc_plan_queries for file name '|| filename||' '||SQLERRM;
select_clause,
from_clause,
where_clause,
prompts_clause,
select_clause_dmd,
from_clause_dmd,
where_clause_dmd,
l_return_code);
IF select_clause_dmd IS NOT NULL AND from_clause_dmd IS NOT NULL AND where_clause_dmd IS NOT NULL THEN
query_stmt:=select_clause||' '||from_clause||' '||where_clause||' '||select_clause_dmd||' '||from_clause_dmd||' '||where_clause_dmd;
query_stmt:=select_clause||' '||from_clause||' '||where_clause;
SELECT NVL(file_id,-123) INTO wsh_id FROM fnd_lobs WHERE file_name=filename;
select count(1) into count_row1 from msc_plan_queries
where plan_id=l_plan_id
and query_id=l_query_id;
update msc_plan_queries
set file_id=wsh_id,
LAST_UPDATED_BY=fnd_global.user_id,
LAST_UPDATE_DATE =SYSDATE,
LAST_UPDATE_LOGIN=fnd_global.user_id
where plan_id=l_plan_id
and query_id=l_query_id;
INSERT INTO msc_plan_queries
(PLAN_ID,
QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FILE_ID )
VALUES (l_plan_id,
l_query_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
wsh_id);
l_return_code:=l_return_code||' Error Inserting record in msc_plan_queries for file name '|| filename||' '||SQLERRM;
SELECT plq.query_Id,QUERY_TYPE,pq.query_name
FROM msc_plan_queries plq,
msc_personal_queries pq
WHERE plq.plan_id = p_plan_Id
AND plq.query_id = pq.query_id
AND pq.query_type = 11;
SELECT field_name,
field_type,
condition,
DECODE(field_name , 'PLANNING_MAKE_BUY_CODE',
hidden_from_field, low_value ) low_value,
high_value,
hidden_from_field,
data_set,
source_type,
object_type,
lov_type,
sequence,
object_sequence_id
FROM msc_selection_criteria_v
WHERE folder_id = p_query_id
AND active_flag = 1
AND condition IS NOT NULL
AND folder_object LIKE 'MSC_WRKSH_FLTR%'
AND data_set is NOT NULL
ORDER BY source_type, object_type, field_name;
SELECT count(*)
FROM msc_personal_queries
WHERE query_id = p_query_id
AND NVL(and_or_flag,1) = 1;