[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure UpdateWorklistDetails(
p_wi_instance_id IN NUMBER,
p_parameter_name IN VARCHAR2,
p_is_value_evaluated IN VARCHAR2,
p_parameter_value IN VARCHAR2,
p_parameter_ref_value IN VARCHAR2);
Procedure UpdateFaDetails(p_fa_instance_id IN NUMBER,
p_parameter_name IN VARCHAR2,
p_evaluated_flag IN VARCHAR2,
p_parameter_value IN VARCHAR2,
p_parameter_ref_value IN VARCHAR2);
select
wpr.parameter_name,
is_value_evaluated,
decode(parameter_value,NULL,wpr.default_value,wdl.parameter_value) param_value,
parameter_ref_value
from
xdp_wi_parameters wpr,
xdp_worklist_details wdl
where
wpr.parameter_name = wdl.parameter_name and
wpr.workitem_id = wdl.workitem_id and
wdl.workitem_instance_id = p_wi_instance_id;
select
provisioning_date
into
lv_prov_date
from
XDP_FULFILL_WORKLIST
where
workitem_instance_id = p_wi_instance_id;
cursor c_update_prov_date is
select status_code status
from XDP_FULFILL_WORKLIST
where workitem_instance_id = p_wi_instance_id
for update of provisioning_date nowait;
SavePoint UpdateProvDate;
FOR v_update_prov_date in c_update_prov_date LOOP
if v_update_prov_date.status NOT in ('IN PROGRESS','SUCCESS','SUCCESS_WITH_OVERRIDE','CANCELED') then
update XDP_FULFILL_WORKLIST
set last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
provisioning_date = p_prov_date
where current of c_update_prov_date;
rollback to UpdateProvDate;
XDPCORE.error_context( 'WI', p_wi_instance_id, 'XDP_WI_UPDATE_NOT_ALLOWED', lv_message_params);
rollback to UpdateProvDate;
select
fpr.parameter_name,
is_value_evaluated,
decode(parameter_value,NULL,fpr.default_value,fdl.parameter_value) param_value,
parameter_ref_value
from
xdp_fa_parameters fpr,
xdp_fa_details fdl
where
fpr.fulfillment_action_id = fdl.fulfillment_action_id and
fpr.parameter_name = fdl.parameter_name and
fdl.fa_instance_id = p_fa_instance_id;
select
fwt.order_id,
fwt.line_item_id,
frt.workitem_instance_id,
fpr.parameter_name,
NVL(log_in_audit_trail_flag,'Y') log_flag,
evaluation_procedure,
fpr.default_value,
frt.fulfillment_action_id
from
xdp_fa_runtime_list frt,
xdp_fa_parameters fpr,
XDP_FULFILL_WORKLIST fwt
where
frt.fa_instance_id = p_fa_instance_id and
frt.workitem_instance_id = fwt.workitem_instance_id and
frt.fulfillment_action_id = fpr.fulfillment_action_id and
fpr.parameter_name = p_parameter_name;
select order_parameter_value
from xdp_order_parameters
where
order_id = p_order_id and
order_parameter_name = p_parameter_name;
select
order_parameter_name parameter_name,
order_parameter_value parameter_value
from
xdp_order_parameters
where
order_id = p_order_id;
update xdp_order_parameters
set
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
order_parameter_value = p_parameter_value
where
order_id = p_order_id and
order_parameter_name = p_parameter_name;
select parameter_value
from XDP_ORDER_LINEITEM_DETS
where
line_item_id = p_line_item_id and
line_parameter_name = p_parameter_name;
select parameter_reference_value
from XDP_ORDER_LINEITEM_DETS
where
line_item_id = p_line_item_id and
line_parameter_name = p_parameter_name;
Update an existing parameter value for a given line
*******************************************/
PROCEDURE Set_LINE_PARAM_Value(
p_line_item_id IN NUMBER,
p_parameter_name IN VARCHAR2,
p_parameter_value IN VARCHAR2,
p_parameter_reference_value IN VARCHAR2 DEFAULT NULL)
IS
lv_exists varchar2(1);
update XDP_ORDER_LINEITEM_DETS
set
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
parameter_value = p_parameter_value,
parameter_reference_value = p_parameter_reference_value
where
line_item_id = p_line_item_id and
line_parameter_name = p_parameter_name;
select
fan.fe_attribute_name fe_attribute_name,
decode(fe_attribute_value,NULL,default_value,fe_attribute_value) fe_attribute_value,
fan.conceal_data conceal_data
from
xdp_fe_attribute_def fan,
(select fe_attribute_id, fe_attribute_value
from
XDP_FE_ATTRIBUTE_VAL fae,
XDP_FE_GENERIC_CONFIG fge
where
fae.fe_generic_config_id = fge.fe_generic_config_id and
fge.fe_id = l_fe_id and
fge.fe_sw_gen_lookup_id = l_gen_lookup_id ) fae2
where
fan.fe_sw_gen_lookup_id = l_gen_lookup_id and
fan.fe_attribute_id = fae2.fe_attribute_id(+);
select
fan.fe_attribute_name fe_attribute_name,
decode(fe_attribute_value,NULL,default_value,fe_attribute_value) fe_attribute_value,
fan.conceal_data conceal_data
from
xdp_fe_attribute_def fan,
(select fe_attribute_id, fe_attribute_value
from
XDP_FE_ATTRIBUTE_VAL fae,
XDP_FE_GENERIC_CONFIG fge
where
fae.fe_generic_config_id = fge.fe_generic_config_id and
fge.fe_id = l_fe_id and
fge.fe_sw_gen_lookup_id = l_gen_lookup_id ) fae2
where
fan.fe_sw_gen_lookup_id = l_gen_lookup_id and
fan.fe_attribute_id = fae2.fe_attribute_id(+);
select
fwt.workitem_instance_id,
fwt.workitem_id,
wim.workitem_name,
fwt.wi_sequence,
fwt.status_code,
fwt.priority,
fwt.line_item_id,
fwt.line_number,
fwt.provisioning_date,
NVL(fwt.error_ref_id,0) error_ref_id
from
XDP_FULFILL_WORKLIST fwt,
xdp_workitems wim
where
fwt.order_id = p_sdp_order_id and
fwt.workitem_id = wim.workitem_id;
select
frt.fa_instance_id,
fan.fulfillment_action,
frt.status_code,
frt.fulfillment_action_id,
frt.priority,
frt.provisioning_sequence,
NVL(frt.error_ref_id, 0) error_ref_id
from
xdp_fa_runtime_list frt,
XDP_FULFILL_ACTIONS fan
where
frt.workitem_instance_id = p_wi_instance_id and
frt.fulfillment_action_id = fan.fulfillment_action_id;
select
fcl.fa_instance_id,
fcl.fe_command_seq ,
fan.fulfillment_action,
frt.fulfillment_action_id ,
fcl.command_sent ,
fcl.command_sent_date ,
fcl.response ,
fcl.response_date ,
fcl.USER_RESPONSE ,
fcl.msg_id ,
fcl.provisioning_procedure,
fcl.fulfillment_element_name
from
xdp_fa_runtime_list frt,
XDP_FULFILL_ACTIONS fan,
xdp_fe_cmd_aud_trails fcl
where
frt.fa_instance_id = fcl.fa_instance_id and
frt.fulfillment_action_id = fan.fulfillment_action_id and
fcl.fa_instance_id = p_fa_instance_id;
PROCEDURE update_timer_status
(
p_reference_id IN VARCHAR2
,p_timer_message_code IN VARCHAR2
,p_status IN VARCHAR2
,x_error_code OUT NOCOPY NUMBER
,x_error_message OUT NOCOPY VARCHAR2
)
IS
BEGIN
--- Call the get_timer_status api from timer core
--- Returns the status and timer_id
xnp_timer_core.update_timer_status
(p_reference_id => p_reference_id
,p_timer_message_code => p_timer_message_code
,p_status => p_status
,x_error_code => x_error_code
,x_error_message => x_error_message
);
END update_timer_status;
select ORDER_REF_NAME,
ORDER_REF_VALUE,
SP_ORDER_NUMBER
--SP_USERID
into lv_ref_name,
lv_ref_val,
lv_sp_order
--,lv_sp_uid
from xdp_order_headers
where order_id = p_order_id;
update xdp_order_headers
set
order_ref_name = NVL(p_order_ref_name,lv_ref_name),
order_ref_value = NVL(p_order_ref_value,lv_ref_val),
sp_order_number = NVL(p_sp_order_number,lv_sp_order),
--sp_userid = NVL(p_sp_user_id,lv_sp_uid),
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
where order_id = p_order_id;
SELECT parameter_value
FROM
xdp_worklist_details wd,
xdp_fulfill_worklist fw,
xdp_wi_parameters wp
WHERE FW.line_item_id=p_line_item_id
AND FW.workitem_instance_id = wd.workitem_instance_id
AND wd.workitem_id = wp.workitem_id
AND wd.parameter_name = wp.parameter_name --to do
AND wp.parameter_name = p_parameter_name
AND parameter_value is not null
ORDER BY wd.creation_date desc;
SELECT line_parameter_name, PARAMETER_VALUE
FROM XDP_ORDER_LINEITEM_DETS
WHERE LINE_ITEM_ID = p_line_item_id
AND PARAMETER_VALUE IS NULL
FOR UPDATE OF PARAMETER_VALUE NOWAIT;
UPDATE XDP_ORDER_LINEITEM_DETS SET PARAMETER_VALUE = l_parameter_value
WHERE CURRENT OF c_line_item;
select
is_value_evaluated,
modified_flag,
parameter_value,
parameter_ref_value,
workitem_id
from xdp_worklist_details
where workitem_instance_id = p_wi_instance_id
and parameter_name = p_parameter_name;
select
wpr.workitem_id,
wpr.evaluation_procedure,
wpr.evaluation_mode,
wpr.default_value,
NVL(wpr.log_in_audit_trail_flag,'Y') audit_trail_flag
from
xdp_wi_parameters wpr,
xdp_worklist_details wdl
where
wpr.workitem_id = wdl.workitem_id and
wpr.parameter_name = p_parameter_name and
wdl.workitem_instance_id = p_wi_instance_id;
select
evaluation_procedure,
NVL(log_in_audit_trail_flag,'Y') audit_trail_flag,
default_value,
parameter_value,
parameter_ref_value,
frt.fulfillment_action_id
from
xdp_fa_parameters fpr,
xdp_fa_details frt
where
fpr.parameter_name = frt.parameter_name and
fpr.fulfillment_action_id = frt.fulfillment_action_id and
fpr.parameter_name = p_parameter_name and
frt.fa_instance_id = p_fa_instance_id;
select 'Y' yahoo
from dual
where exists(
select 'x' from
xdp_worklist_details
where workitem_instance_id = p_wi_instance_id and
parameter_name = p_parameter_name);
select 'Y' yahoo
from dual
where exists(
select 'x' from
xdp_fa_details
where fa_instance_id = p_fa_instance_id and
parameter_name = p_parameter_name);
select 'Y' yahoo
from dual
where exists(
select 'x'
from xdp_order_parameters
where order_id = p_order_id and
order_parameter_name = p_parameter_name);
select 'Y' yahoo
from dual
where exists(
select 'x'
from XDP_ORDER_LINEITEM_DETS
where line_item_id = p_line_item_id and
line_parameter_name = p_parameter_name);
insert into xdp_order_parameters
(order_id,
order_parameter_name,
order_parameter_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values
( p_order_id,
p_parameter_name,
p_parameter_value,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
insert into XDP_ORDER_LINEITEM_DETS
( line_item_id,
line_parameter_name,
parameter_value,
parameter_reference_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values
( p_line_item_id,
p_parameter_name,
p_parameter_value,
p_parameter_reference_value,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
SELECT
workitem_id INTO l_workitem_id
FROM
xdp_fulfill_worklist
WHERE
workitem_instance_id = p_wi_instance_id;
INSERT INTO xdp_worklist_details
(
workitem_instance_id,
parameter_name,
workitem_id,
is_value_evaluated,
parameter_value,
parameter_ref_value,
modified_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)VALUES
(
p_wi_instance_id,
p_parameter_name,
l_workitem_id,
p_is_value_evaluated,
p_parameter_value,
p_parameter_ref_value,
'N',
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
Procedure UpdateWorklistDetails(
p_wi_instance_id IN NUMBER,
p_parameter_name IN VARCHAR2,
p_is_value_evaluated IN VARCHAR2,
p_parameter_value IN VARCHAR2,
p_parameter_ref_value IN VARCHAR2)
IS
BEGIN
UPDATE xdp_worklist_details
SET
parameter_value = p_parameter_value ,
parameter_ref_value = p_parameter_ref_value,
is_value_evaluated = p_is_value_evaluated,
modified_flag = 'Y',
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE
workitem_instance_id = p_wi_instance_id AND
parameter_name = p_parameter_name;
END UpdateWorklistDetails;
insert into xdp_fa_details
( fa_instance_id,
parameter_name,
fulfillment_action_id,
is_value_evaluated,
parameter_value,
parameter_ref_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
values
( p_fa_instance_id,
p_parameter_name,
p_fa_id,
p_is_value_evaluated,
p_parameter_value,
p_parameter_ref_value,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
Procedure UpdateFaDetails(p_fa_instance_id IN NUMBER,
p_parameter_name IN VARCHAR2,
p_evaluated_flag IN VARCHAR2,
p_parameter_value IN VARCHAR2,
p_parameter_ref_value IN VARCHAR2)
is
begin
update xdp_fa_details
set
parameter_value = UpdateFaDetails.p_parameter_value,
parameter_ref_value = UpdateFaDetails.p_parameter_ref_value,
is_value_evaluated = UpdateFaDetails.p_evaluated_flag,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
where
fa_instance_id = UpdateFaDetails.p_fa_instance_id and
parameter_name = UpdateFaDetails.p_parameter_name;
end UpdateFaDetails;
select order_id,line_item_id
into lv_order_id, lv_line_item_id
from XDP_FULFILL_WORKLIST
where workitem_instance_id = p_wi_instance_id;
SELECT oli.wf_item_type, oli.wf_item_key
FROM xdp_order_line_items oli, xdp_fulfill_worklist fwl
WHERE oli.line_item_id = fwl.line_item_id
AND fwl.workitem_instance_id = p_wi_instance_id;
UpdateWorklistDetails(
p_wi_instance_id => p_wi_instance_id,
p_parameter_name => p_parameter_name,
p_is_value_evaluated => p_is_value_evaluated,
p_parameter_value => p_parameter_value,
p_parameter_ref_value => p_parameter_ref_value
);
select order_id, frt.workitem_instance_id,fwt.line_item_id
from
XDP_FULFILL_WORKLIST fwt,
xdp_fa_runtime_list frt
where
fwt.workitem_instance_id = frt.workitem_instance_id and
frt.fa_instance_id = p_fa_instance_id;
UpdateFaDetails
(p_fa_instance_id => SetFAParamValue.p_fa_instance_id,
p_parameter_name => SetFAParamValue.p_parameter_name,
p_evaluated_flag => lv_eval_flag,
p_parameter_value => lv_eval_val,
p_parameter_ref_value => lv_eval_ref_val);
select
fet.fE_ID,
fet.fetype_id,
fee.fulfillment_element_type,
fsp.sw_generic,
fsp.adapter_type,
fsp.fe_sw_gen_lookup_id,
decode(fge.sw_start_proc,NULL,fsp.sw_start_proc,fge.sw_start_proc) connect_proc,
decode(fge.sw_exit_proc,NULL,fsp.sw_exit_proc,fge.sw_exit_proc) disconnect_proc
from
XDP_FES fet,
XDP_FE_GENERIC_CONFIG fge,
XDP_FE_TYPES fee,
XDP_FE_SW_GEN_LOOKUP fsp
where
fet.fulfillment_element_name = p_fe and
fet.FE_ID = fge.FE_ID and
fet.fetype_id = fee.fetype_id and
fge.FE_SW_GEN_LOOKUP_ID = fsp.FE_SW_GEN_LOOKUP_ID and
fge.START_DATE = (
select MAX( FGE2.START_DATE )
from XDP_FE_GENERIC_CONFIG fge2
where fge2.FE_ID= fet.FE_ID and
lv_date >= fge2.START_DATE and
lv_date <= NVL(fge2.END_DATE,lv_date));
select
fet.fulfillment_element_name,
fet.fetype_id,
fee.fulfillment_element_type,
fsp.sw_generic,
fsp.adapter_type,
fsp.fe_sw_gen_lookup_id,
decode(fge.sw_start_proc,NULL,fsp.sw_start_proc,fge.sw_start_proc) connect_proc,
decode(fge.sw_exit_proc,NULL,fsp.sw_exit_proc,fge.sw_exit_proc) disconnect_proc
from
XDP_FES fet,
XDP_FE_GENERIC_CONFIG fge,
XDP_FE_TYPES fee,
XDP_FE_SW_GEN_LOOKUP fsp
where
fet.fe_id = p_fe_id and
fet.FE_ID = fge.FE_ID and
fet.fetype_id = fee.fetype_id and
fge.FE_SW_GEN_LOOKUP_ID = fsp.FE_SW_GEN_LOOKUP_ID and
fge.START_DATE = (
select MAX( FGE2.START_DATE )
from XDP_FE_GENERIC_CONFIG fge2
where fge2.FE_ID= fet.FE_ID and
lv_date >= fge2.START_DATE and
lv_date <= NVL(fge2.END_DATE,lv_date));
select
decode(fe_attribute_value,NULL,default_value,fe_attribute_value) attr_val,
fan.conceal_data
from
xdp_fe_attribute_def fan,
(select fe_attribute_id, fe_attribute_value
from
XDP_FE_ATTRIBUTE_VAL fae,
XDP_FE_GENERIC_CONFIG fge
where
fae.fe_generic_config_id = fge.fe_generic_config_id and
fge.fe_id = GetAttrVal.p_fe_id and
fge.fe_sw_gen_lookup_id = GetAttrVal.p_fe_sw_gen_lookup ) fae2
where
fan.fe_attribute_name = GetAttrVal.p_attribute_name and
fan.fe_sw_gen_lookup_id = GetAttrVal.p_fe_sw_gen_lookup and
fan.fe_attribute_id = fae2.fe_attribute_id(+);
pv_FeAttributeList.delete;
SELECT
wpr.parameter_name,
xfw.order_id,
xfw.line_item_id,
xfw.workitem_id,
wpr.evaluation_procedure,
wpr.evaluation_mode
FROM
xdp_fulfill_worklist xfw,
xdp_wi_parameters wpr
WHERE
xfw.workitem_instance_id = p_wi_instance_id
AND xfw.workitem_id = wpr.workitem_id
AND wpr.evaluation_procedure is not null
AND wpr.evaluation_mode = pv_evalModeWIStart
ORDER BY wpr.evaluation_seq;
SELECT parameter_value, parameter_ref_value, is_value_evaluated
INTO l_param_val,l_param_ref_val, l_is_value_evaluated
FROM xdp_worklist_details
WHERE workitem_instance_id = p_wi_instance_id
AND parameter_name = v_GetAllWIParams.parameter_name;
pv_FeAttributeList.delete;