[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT code
INTO l_code
FROM xnp_geo_areas_b
WHERE geo_area_id = id;
SELECT 1
INTO v_invalid_status
FROM dual
WHERE EXISTS
(SELECT 'X'
FROM xnp_sv_status_types_b
WHERE status_type_code = v_default_porting_status
AND active_flag = 'Y');
SELECT 1
INTO v_invalid_sp_name
FROM dual
WHERE EXISTS
(SELECT 'X'
FROM xnp_service_providers
WHERE code = v_local_sp_name
AND active_flag = 'Y');
SELECT pi , count(*) cnt,
decode (pi,
'INQUIRY', decode (count(*), 0, gc_WARNING,gc_OK),
decode(count(*), 0, gc_ERROR,gc_OK)) result_type
FROM
(SELECT flv.lookup_code pi, sst.status_type_code stc
FROM xnp_sv_status_types_b sst, fnd_lookup_values flv
WHERE flv.lookup_code = sst.phase_indicator (+)
AND flv.lookup_type = 'XNP_PHASE_INDICATOR'
AND flv.enabled_flag = 'Y'
AND sst.active_flag = 'Y')
GROUP BY pi;
SELECT gho.child_geo_area_id child,
gho.parent_geo_area_id parent
FROM xnp_geo_hierarchy gho
WHERE NOT EXISTS
(SELECT 'X'
FROM xnp_geo_hierarchy ghi
WHERE ghi.child_geo_area_id = gho.child_geo_area_id
AND ghi.parent_geo_area_id = gho.parent_geo_area_id
-- START WITH parent_geo_area_id = 0
START WITH parent_geo_area_id = (select geo_area_id from xnp_geo_areas_b
where geo_area_type_code = 'REGION' and
code = 'WORLD')
CONNECT BY PRIOR child_geo_area_id = parent_geo_area_id);
SELECT fulfillment_action_id id, fulfillment_action name
FROM xdp_fulfill_actions fa
WHERE NOT EXISTS
(SELECT 1
FROM xdp_fa_fulfillment_proc fap
WHERE fap.fulfillment_action_id = fa.fulfillment_action_id);
SELECT fulfillment_action_id id, fulfillment_action name
FROM xdp_fulfill_actions fa
WHERE EXISTS
(SELECT 1
FROM xdp_fa_fulfillment_proc fap
WHERE fap.fulfillment_action_id = fa.fulfillment_action_id)
AND NOT EXISTS
(SELECT 1
FROM xdp_fa_fulfillment_proc fap ,
xdp_fes fes,
xdp_fe_sw_gen_lookup fgl
WHERE fap.fulfillment_action_id
= fa.fulfillment_action_id
AND fap.fe_sw_gen_lookup_id
= fgl.fe_sw_gen_lookup_id
AND fgl.fetype_id = fes.fetype_id
AND SYSDATE BETWEEN fes.valid_date
AND nvl(fes.invalid_date, gc_max_date));
SELECT fe_id id, fulfillment_element_name name,
to_char(invalid_date, gc_DATE_FORMAT) valid_date,
DECODE(invalid_date, null, gc_NULL,
to_char(invalid_date, gc_DATE_FORMAT)) invalid_date
FROM xdp_fes
WHERE invalid_date < SYSDATE;
SELECT fe.fe_id id, fulfillment_element_name name,
to_char(valid_date, gc_DATE_FORMAT) valid_date,
DECODE(invalid_date, null, gc_NULL,
to_char(invalid_date, gc_DATE_FORMAT)) invalid_date
FROM xdp_fes fe, xdp_fe_generic_config fgc
WHERE fe.fe_id = fgc.fe_id
AND NOT
(fgc.start_date BETWEEN
fe.valid_date and nvl(fe.invalid_date, gc_max_date)
AND
nvl(fgc.end_date, gc_max_date) BETWEEN
fe.valid_date and nvl(fe.invalid_date, gc_max_date));
SELECT fe.fe_id id , fe.fulfillment_element_name name
FROM xdp_fes fe
WHERE NOT EXISTS
(SELECT 1
FROM xdp_fe_generic_config fgc
WHERE fe.fe_id = fgc.fe_id
AND SYSDATE BETWEEN fgc.start_date
AND nvl(fgc.end_date, gc_max_date)
);
SELECT fe.fe_id id, fe.fulfillment_element_name name
FROM xdp_fes fe
WHERE NOT EXISTS
(SELECT 1
FROM xdp_adapter_reg ar
WHERE fe.fe_id = ar.fe_id);
SELECT fet.fetype_id id, fet.fulfillment_element_type name
FROM xdp_fe_types fet
WHERE NOT EXISTS
(SELECT 1
FROM xdp_fe_sw_gen_lookup sgl
WHERE fet.fetype_id = sgl.fetype_id
);
SELECT fgc.fe_id,
nvl(fgc.sw_start_proc, gc_NULL) sw_start_proc,
nvl(fgc.sw_exit_proc, gc_NULL) sw_exit_proc
FROM xdp_fe_generic_config fgc
WHERE EXISTS
(SELECT 1
FROM xdp_fes fes
WHERE fes.fe_id = fgc.fe_id
AND fetype_id IN (SELECT fetype_id
FROM xdp_fe_types
WHERE fulfillment_element_type
IN (gc_FILE
, gc_JSCRIPT
, gc_INTERACTIVE)))
AND (fgc.sw_start_proc IS NULL
OR
fgc.sw_exit_proc IS NULL);
SELECT sp_id, code, sp_type, name
FROM xnp_service_providers sp
WHERE NOT EXISTS
(SELECT 1
FROM xnp_sp_adapters spa
WHERE sp.sp_id = spa.sp_id);
SELECT number_range_id, starting_number, ending_number
FROM xnp_number_ranges
WHERE geo_indicator = gc_GEO
AND geo_area_id IS NULL;
SELECT number_range_id, starting_number,ending_number
FROM xnp_number_ranges
WHERE geo_indicator <> gc_GEO
AND geo_area_id IS NOT NULL;
SELECT number_range_id, starting_number, ending_number,
to_char(effective_date, gc_DATE_FORMAT) effective_date,
nvl(active_flag, gc_NULL) active_flag
FROM xnp_number_ranges
WHERE effective_date > SYSDATE
OR nvl(active_flag,'N') <> 'Y';
SELECT number_range_id, starting_number, ending_number,
assigned_sp_id, owning_sp_id, pooled_flag
FROM xnp_number_ranges
WHERE pooled_flag = 'N'
AND assigned_sp_id <> owning_sp_id;
SELECT number_range_id, starting_number, ending_number
FROM xnp_number_ranges
WHERE pooled_flag = 'Y'
AND assigned_sp_id IS NULL;
SELECT number_range_id,
starting_number, ending_number, ported_indicator
FROM xnp_number_ranges
WHERE ported_indicator NOT IN ('NON_PORTED', 'NON_PORTABLE');
SELECT snr.number_range_id, nr.starting_number, nr.ending_number
FROM xnp_served_num_ranges snr, xnp_number_ranges nr
WHERE NOT EXISTS
(SELECT 'X'
FROM xdp_fes fes
WHERE fes.fe_id = snr.fe_id
AND SYSDATE
BETWEEN fes.valid_date and NVL(fes.invalid_date,SYSDATE)
)
AND nr.number_range_id = snr.number_range_id;
SELECT number_range_id,
starting_number, ending_number
FROM xnp_number_ranges;
SELECT service_id id , service_name name,
to_char(valid_date, gc_DATE_FORMAT) valid_date,
DECODE(invalid_date, NULL, gc_NULL,
to_char(invalid_date, gc_DATE_FORMAT)) invalid_date
FROM xdp_services
WHERE invalid_date < SYSDATE;
SELECT service_id id, service_name name
FROM xdp_services s
WHERE NOT EXISTS
(SELECT 1
FROM xdp_service_val_acts sva
WHERE sva.service_id = s.service_id
AND SYSDATE
BETWEEN sva.valid_date
AND nvl(sva.invalid_date,gc_max_date)
);
SELECT sva.service_id id, s.service_name name
FROM xdp_service_val_acts sva, xdp_services s
WHERE sva.service_id = s.service_id
AND sva.wi_mapping_type = gc_DYNAMIC
AND sva.wi_mapping_proc is NULL;
SELECT mtl.inventory_item_id id
, mtl.organization_id organization_id
, mtl.concatenated_segments name
FROM xdp_service_val_acts sva
, mtl_system_items_vl mtl
WHERE sva.inventory_item_id = mtl.inventory_item_id
AND sva.organization_id = mtl.organization_id
AND sva.wi_mapping_type = gc_DYNAMIC
AND sva.wi_mapping_proc is NULL;
SELECT package_id id, package_name name,
to_char(valid_date, gc_DATE_FORMAT) valid_date,
DECODE(invalid_date, NULL, gc_NULL,
to_char(invalid_date, gc_DATE_FORMAT)) invalid_date
FROM xdp_service_packages
WHERE invalid_date < SYSDATE;
SELECT package_id id, package_name name
FROM xdp_service_packages sp
WHERE NOT EXISTS
(SELECT 1
FROM xdp_service_pkg_det spd, xdp_services sv
WHERE spd.package_id = spd.package_id
AND spd.service_id = sv.service_id
AND SYSDATE BETWEEN sv.valid_date
AND nvl(sv.invalid_date,gc_max_date)
);
SELECT workitem_id id, workitem_name name,
to_char(valid_date, gc_DATE_FORMAT) valid_date,
DECODE(invalid_date, NULL, gc_NULL,
to_char(invalid_date, gc_DATE_FORMAT)) invalid_date
FROM xdp_workitems
WHERE invalid_date < SYSDATE;
SELECT workitem_id id, workitem_name name
FROM xdp_workitems wi
WHERE wi.wi_type_code = gc_STATIC
AND NOT EXISTS
(SELECT 1
FROM xdp_wi_fa_mapping wfa
WHERE wfa.workitem_id = wi.workitem_id);
SELECT tp.timer_publisher_id, tp.source_message_code, tp.timer_message_code
FROM xnp_timer_publishers tp
WHERE NOT EXISTS
(SELECT 1
FROM xnp_msg_acks ma
WHERE ma.source_msg_code = tp.source_message_code
AND ma.ack_msg_code = tp.timer_message_code );
SELECT msg_code
FROM xnp_msg_types_b
WHERE status = 'UNCOMPILED';
SELECT distinct proc_name name
FROM xdp_proc_body pb, user_objects uo
WHERE uo.object_name = substr(pb.proc_name, 1, (INSTR(pb.proc_name,'.')-1))
AND uo.object_type in ('PACKAGE','PACKAGE BODY')
AND uo.status = 'INVALID';
SELECT 'XNP_CVU_WF_LHS_NOT_VALID_ELEM'
INTO v_lhs_msg
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM xnp_msg_elements me
WHERE me.name = p_LHS
AND me.parameter_flag = 'Y'
AND me.msg_code = p_message_code);
SELECT 'XNP_CVU_WF_RHS_NOTVALID_PARAM'
INTO v_rhs_msg
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM fnd_lookups flk
WHERE flk.lookup_code = p_RHS
AND flk.lookup_type = 'CSI_EXTEND_ATTRIB_POOL');
SELECT 'XNP_CVU_WF_RHS_NOTVALID_PARAM'
INTO v_rhs_msg
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM xdp_parameter_pool pol
WHERE pol.parameter_name = p_RHS);
SELECT 'XNP_CVU_WF_NOT_VALID_EVT_TYPE'
INTO v_no_such_evt
FROM dual
WHERE NOT EXISTS
(SELECT 1
FROM xnp_msg_types_b me
WHERE me.msg_code = p_message_code);
SELECT count(*)
INTO v_no_elements
FROM xnp_msg_elements me
WHERE me.parameter_flag = 'Y'
AND me.msg_code = p_message_code;
SELECT pat.process_name process_name,
pat.process_item_type process_item_type,
max(pat.process_version) process_version
FROM wf_process_activities pat, xdp_workitems wi
WHERE
-- xdp_workitem - process
pat.process_name = wi.user_wf_process_name
AND pat.process_item_type = wi.user_wf_item_type
-- Only work flow work items
AND wi.wi_type_code = gc_WORKFLOW
GROUP BY pat.process_item_type, pat.process_name;
SELECT pat.instance_id, pat.process_name,
pat.process_item_type, pat.process_version,
atv.name attribute_name,
atv.text_value attribute_value, pat.activity_name
FROM wf_process_activities pat, wf_activities ac, wf_activity_attr_values atv
WHERE
-- highest version for the process
pat.process_name = p_process_name
AND pat.process_item_type = p_process_item_type
AND pat.process_version = p_process_version
-- process to activites
AND ac.name = pat.activity_name
AND ac.item_type = pat.activity_item_type
-- should consider only the max version AND ac.version = pat.process_version
-- alternatively less cleaner but easier approach is to
-- pick up the record with end_date is null
-- AND ac.version = pat.process_version
AND ac.end_date IS NULL
--<
AND pat.instance_id = atv.process_activity_id
-- Only for PUBLISH_EVENT and SEND_MESSAGE functions
AND ac.function IN ('XNP_WF_STANDARD.PUBLISH_EVENT',
'XNP_WF_STANDARD.SEND_MESSAGE')
-- Parameter attributes
AND atv.name IN (gc_PARAM_LIST, gc_EVENT_TYPE)
ORDER BY pat.instance_id
, pat.process_name
, pat.process_item_type
, pat.process_version
, DECODE(atv.name, gc_EVENT_TYPE, 1, 2)
, atv.text_value;
SELECT DISTINCT wi.user_wf_item_type item_type , workitem_name
FROM xdp_workitems wi
WHERE wi.wi_type_code = 'WORKFLOW'
AND wi.user_wf_item_type IS NOT NULL
AND ( NOT EXISTS
(SELECT 1
FROM wf_item_attributes ia
WHERE ia.name = 'ORDER_ID'
AND ia.item_type = wi.user_wf_item_type
)
OR NOT EXISTS
(SELECT 1
FROM wf_item_attributes ia
WHERE ia.name = 'WORKITEM_INSTANCE_ID'
AND ia.item_type = wi.user_wf_item_type
)
OR NOT EXISTS
(SELECT 1
FROM wf_item_attributes ia
WHERE ia.name = 'LINE_ITEM_ID'
AND ia.item_type = wi.user_wf_item_type
)
);
SELECT COUNT(*)
INTO v_count
FROM wf_process_activities pat
WHERE pat.process_name = p_process_name
AND pat.process_item_type = p_process_item_type
AND pat.process_version = p_process_version
AND pat.activity_name = p_act_name;
SELECT text_value
INTO v_text_value
FROM wf_activity_attr_values
WHERE process_activity_id = p_instance_id
AND NAME = gc_FA_NAME;
SELECT count(*)
INTO v_count
FROM xdp_fulfill_actions
WHERE fulfillment_action = v_text_value;
SELECT text_value
INTO v_text_value
FROM wf_activity_attr_values
WHERE process_activity_id = p_instance_id
AND NAME = gc_FE_NAME;
SELECT count(*)
INTO v_count
FROM xdp_fes
WHERE fulfillment_element_name = v_text_value;
SELECT pat.process_item_type, pat.process_name, max(pat.process_version) process_version
FROM wf_process_activities pat, xdp_workitems wi
WHERE
-- xdp_workitem - process
pat.process_name = wi.user_wf_process_name
AND pat.process_item_type = wi.user_wf_item_type
-- Only work flow work items
AND wi.wi_type_code = gc_WORKFLOW
GROUP BY pat.process_item_type, pat.process_name;
SELECT pat.instance_id
FROM wf_process_activities pat
WHERE pat.process_name = p_process_name
AND pat.process_item_type = p_process_item_type
AND pat.process_version = p_process_version
AND pat.activity_name = gc_EXECUTE_FA;
ActivityExists(gc_COMPLETE_WI_UPDATE_STATUS,
rec.process_name,
rec.process_item_type,
rec.process_version);
SELECT pat.process_name process_name,
pat.process_item_type process_item_type,
max(pat.process_version) process_version
FROM wf_process_activities pat, xdp_workitems wi
WHERE
-- xdp_workitem - process
pat.process_name = wi.user_wf_process_name
AND pat.process_item_type = wi.user_wf_item_type
-- Only work flow work items
AND wi.wi_type_code = gc_WORKFLOW
GROUP BY pat.process_item_type, pat.process_name;
SELECT pa.instance_id, pa.process_item_type,
pa.process_name, pa.process_version, ats.result_code
FROM wf_process_activities pa,
wf_activity_transitions ats
WHERE
-- process_activities
pa.process_name = p_process_name
AND pa.process_item_type = p_process_item_type
AND pa.process_version = p_process_version
-- process_activities -> activity_transitions
AND ats.from_process_activity = pa.instance_id
-- filters
AND pa.activity_name = gc_SUBSCRIBE_TO_BUSS_EVTS
AND ats.result_code <> '*'
AND NOT EXISTS
(
SELECT 1
FROM wf_activity_attr_values ack_aav
WHERE ack_aav.process_activity_id = pa.instance_id
AND ack_aav.name = gc_EVENT_TYPE
AND ack_aav.text_value = ats.result_code)
ORDER BY pa.process_item_type, pa.process_name, pa.process_version;
SELECT pat.process_name process_name,
pat.process_item_type process_item_type,
max(pat.process_version) process_version
FROM wf_process_activities pat, xdp_workitems wi
WHERE
-- xdp_workitem - process
pat.process_name = wi.user_wf_process_name
AND pat.process_item_type = wi.user_wf_item_type
-- Only work flow work items
AND wi.wi_type_code = gc_WORKFLOW
GROUP BY pat.process_item_type, pat.process_name;
SELECT pa.instance_id, pa.process_item_type,
pa.process_name, pa.process_version, ats.result_code
FROM wf_process_activities pa,
wf_activity_transitions ats
WHERE
-- process_activities
pa.process_name = p_process_name
AND pa.process_item_type = p_process_item_type
AND pa.process_version = p_process_version
-- process_activities -> activity_transitions
AND ats.from_process_activity = pa.instance_id
-- filters
AND pa.activity_name = gc_SUBSCRIBE_TO_ACKS
AND ats.result_code <> '*'
AND NOT EXISTS
(
SELECT 1
FROM wf_activity_attr_values ack_aav, xnp_msg_acks ack_xma
WHERE
-- Join with the outer SQL
ack_aav.process_activity_id = pa.instance_id
AND ack_xma.ack_msg_code = ats.result_code
-- filter for attribute name
AND ack_aav.name = gc_EVENT_TYPE
-- activity_attr_values -> xnp_msg_acks
AND ack_aav.text_value = ack_xma.source_msg_code)
ORDER BY pa.process_item_type, pa.process_name, pa.process_version;
SELECT count(*)
INTO v_cnt
FROM fnd_lookups fl
WHERE lookup_type = p_fnd_lookup_type
AND NOT EXISTS
(SELECT 1
FROM wf_lookups wl
WHERE substr(wl.lookup_code,1,29) = substr(fl.lookup_code,1, 29)
AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(fl.meaning, 1,75),'#')
AND nvl(substr(wl.description,1,239),'#') = nvl(substr(fl.description, 1, 239),'#')
AND wl.lookup_type = p_wf_lookup_type);
SELECT count(*)
INTO v_cnt
FROM xdp_fes_vl fe
WHERE NOT EXISTS
(SELECT 1
FROM wf_lookups wl
WHERE substr(wl.lookup_code,1,29) = substr(fe.fulfillment_element_name,1, 29)
AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(fe.display_name, 1,75),'#')
AND nvl(substr(wl.description,1,239),'#') = nvl(substr(fe.description, 1, 239),'#')
AND wl.lookup_type = 'FE_NAME');
SELECT count(*)
INTO v_cnt
FROM xdp_fulfill_actions_vl fa
WHERE NOT EXISTS
(SELECT 1
FROM wf_lookups wl
WHERE substr(wl.lookup_code,1,29) = substr(fa.fulfillment_action,1, 29)
AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(fa.display_name, 1,75),'#')
AND nvl(substr(wl.description,1,239),'#') = nvl(substr(fa.description, 1, 239),'#')
AND wl.lookup_type = 'FA_NAME');
SELECT count(*)
INTO v_cnt
FROM xnp_msg_types_vl mt
WHERE NOT EXISTS
(SELECT 1
FROM wf_lookups wl
WHERE substr(wl.lookup_code,1,29) = substr(mt.msg_code,1, 29)
AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(mt.display_name, 1,75),'#')
AND nvl(substr(wl.description,1,239),'#') = nvl(substr(mt.description, 1, 239),'#')
AND wl.lookup_type = 'MESSAGE_TYPE');
SELECT count(*)
INTO v_cnt
FROM xnp_msg_types_vl mt
WHERE msg_type = 'TIMER'
AND NOT EXISTS
(SELECT 1
FROM wf_lookups wl
WHERE substr(wl.lookup_code,1,29) = substr(mt.msg_code,1, 29)
AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(mt.display_name, 1,75),'#')
AND nvl(substr(wl.description,1,239),'#') = nvl(substr(mt.description, 1, 239),'#')
AND wl.lookup_type = 'TIMER_NAMES');
SELECT count(*)
INTO v_cnt
FROM fnd_new_messages nm
WHERE message_name like 'X%_NOTFN_%'
AND NOT EXISTS
(SELECT 1
FROM wf_lookups wl
WHERE substr(wl.lookup_code,1,29) = substr(nm.message_name,1, 29)
AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(nm.description, 1,75),'#')
AND nvl(substr(wl.description,1,239),'#') = nvl(substr(nm.description, 1, 239),'#')
AND wl.lookup_type = 'CUSTOMIZED_NOTN_MESSAGES');
SELECT count(*)
INTO v_cnt
FROM xnp_sv_status_types_vl sst
WHERE NOT EXISTS
(SELECT 1
FROM wf_lookups wl
WHERE substr(wl.lookup_code,1,29) = substr(sst.status_type_code,1, 29)
AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(sst.display_name, 1,75),'#')
AND nvl(substr(wl.description,1,239),'#') = nvl(substr(sst.description, 1, 239),'#')
AND wl.lookup_type = 'STATUS');
SELECT count(*)
INTO v_cnt
FROM xdp_workitems_vl wi
WHERE NOT EXISTS
(SELECT 1
FROM wf_lookups wl
WHERE substr(wl.lookup_code,1,29) = substr(wi.workitem_name,1, 29)
AND nvl(substr(wl.meaning,1,75),'#') = nvl(substr(wi.display_name, 1,75),'#')
AND nvl(substr(wl.description,1,239),'#') = nvl(substr(wi.description, 1, 239),'#')
AND wl.lookup_type = 'WORKITEM');