[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_XDP_ORDER_STATUS
(p_order_id IN NUMBER ,
p_status IN VARCHAR2,
p_caller_name IN VARCHAR2,
return_code OUT NOCOPY NUMBER,
error_description OUT NOCOPY VARCHAR2) ;
PROCEDURE UPDATE_XDP_ORDER_LINE_STATUS
(p_order_id IN NUMBER,
p_lineitem_id IN NUMBER ,
p_status IN VARCHAR2,
p_caller_name IN VARCHAR2,
return_code OUT NOCOPY NUMBER,
error_description OUT NOCOPY VARCHAR2) ;
PROCEDURE UPDATE_XDP_WI_INSTANCE_STATUS
(p_order_id IN NUMBER,
p_wi_instance_id IN NUMBER ,
p_status IN VARCHAR2,
p_caller_name IN VARCHAR2,
return_code OUT NOCOPY NUMBER,
error_description OUT NOCOPY VARCHAR2) ;
PROCEDURE UPDATE_XDP_FA_INSTANCE_STATUS
(p_fa_instance_id IN NUMBER ,
p_status IN VARCHAR2,
p_caller_name IN VARCHAR2,
return_code OUT NOCOPY NUMBER,
error_description OUT NOCOPY VARCHAR2) ;
SELECT XDP_ORDER_HEADERS_S.NextVal
INTO l_order_id
FROM dual;
SELECT
workitem_name
INTO
l_workitem_name
FROM
xdp_workitems
WHERE
workitem_id = p_workitem_id;
SELECT 'Y'
FROM xdp_fulfill_worklist fw,
xdp_fa_runtime_list fr
WHERE fw.order_id = p_order_id
AND fw.workitem_instance_id = fr.workitem_instance_id
AND fr.status_code IN ('IN PROGRESS');
SELECT count(*)
INTO l_count
FROM FND_LOOKUP_VALUES
WHERE UPPER(lookup_code) = UPPER(p_order_type)
AND lookup_type = 'XDP_HA_ORDER_TYPES';
SELECT order_id
INTO lv_id
FROM xdp_order_headers
WHERE external_order_number = (p_order_number) and
external_order_version = (p_order_version);
SELECT order_id
INTO lv_id
FROM xdp_order_headers
WHERE external_order_number = (p_order_number) and
external_order_version IS NULL;
SELECT related_order_id
FROM xdp_order_relationships
WHERE order_id = p_sdp_order_id
AND order_relationship = 'IS_PREREQUISITE_OF';
SELECT line_number
FROM xdp_order_line_items xoli, xdp_order_headers xoh
WHERE xoli.order_id = xoh.order_id
AND xoh.order_source = 'OE_ORDER_HEADERS_ALL'
AND xoh.order_id = p_order_number;
SELECT status_code,
msgid,
order_type
INTO lv_state,
lv_msg_id,
lv_order_type
FROM xdp_order_headers
WHERE order_id = p_sdp_order_id;
SELECT status_code
INTO l_status_code
FROM xdp_order_headers
WHERE order_id = p_order_id
FOR UPDATE NOWAIT ;
dbg_msg := ('Aquired Lock on XDP_ORDER_HEADERS Calling UPDATE_XDP_ORDER_STATUS ');
UPDATE_XDP_ORDER_STATUS
(p_order_id => p_order_id,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code,
error_description => error_description);
dbg_msg := ('Completed UPDATE_XDP_ORDER_STATUS Calling UPDATE_XDP_ORDER_LINE_STATUS ');
UPDATE_XDP_ORDER_LINE_STATUS
(p_order_id => p_order_id ,
p_lineitem_id => null ,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code ,
error_description => error_description );
dbg_msg := ('Completed UPDATE_XDP_ORDER_LINE_STATUS Calling UPDATE_XDP_WI_INSTANCE_STATUS ');
UPDATE_XDP_WI_INSTANCE_STATUS
(
p_order_id => p_order_id ,
p_wi_instance_id => null,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code,
error_description => error_description );
dbg_msg := ('Completed UPDATE_XDP_WI_INSTANCE_STATUS ');
dbg_msg := ('Aquired Lock on XDP_ORDER_PROC_QUEUE Calling UPDATE_XDP_ORDER_STATUS ');
UPDATE_XDP_ORDER_STATUS
(p_order_id => p_sdp_order_id,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code ,
error_description => error_description );
dbg_msg := ('Completed UPDATE_XDP_ORDER_STATUS Calling UPDATE_XDP_ORDER_LINE_STATUS ');
UPDATE_XDP_ORDER_LINE_STATUS
(p_order_id => p_sdp_order_id ,
p_lineitem_id => null ,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code,
error_description => error_description);
dbg_msg := ('Completed UPDATE_XDP_ORDER_LINE_STATUS Calling UPDATE_XDP_WI_INSTANCE_STATUS ');
UPDATE_XDP_WI_INSTANCE_STATUS
(p_order_id => p_sdp_order_id ,
p_wi_instance_id => null ,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code,
error_description => error_description );
SELECT wf_item_type, wf_item_key
INTO l_main_wf_item_type, l_main_wf_item_key
FROM xdp_order_headers
WHERE order_id = p_sdp_order_id;
dbg_msg := ('Completed UPDATE_XDP_WI_INSTANCE_STATUS ');
SELECT fa_instance_id,
frt.msgid,
frt.status_code ,
frt.wf_item_type,
frt.wf_item_key
FROM xdp_fa_runtime_list frt,
xdp_fulfill_worklist fwt
WHERE fwt.order_id = l_order_id and
fwt.workitem_instance_id = frt.workitem_instance_id;
SELECT workitem_instance_id,
wf_item_type,
wf_item_key,
status_code,
msgid
FROM xdp_fulfill_worklist
WHERE order_id = l_order_id;
SELECT line_item_id,
wf_item_key,
wf_item_type,
status_code
FROM xdp_order_line_items
WHERE order_id = l_order_id
ORDER By is_package_flag;
SELECT order_id ,
wf_item_type,
wf_item_key,
status_code,
msgid
INTO lv_id,
lv_item_type,
lv_item_key,
lv_state,
lv_msg_id
FROM xdp_order_headers
WHERE order_id = p_sdp_order_id
FOR UPDATE of XDP_ORDER_HEADERS.STATUS_CODE NOWAIT;
SELECT fr.status_code
INTO lv_state
FROM xdp_fa_runtime_list fr
WHERE fa_instance_id = c_fa_rec.fa_instance_id
FOR UPDATE OF fr.status_code NOWAIT;
SELECT fw.status_code
INTO lv_state
FROM xdp_fulfill_worklist fw
WHERE workitem_instance_id = c_wi_rec.workitem_instance_id
FOR UPDATE OF fw.status_code NOWAIT;
SELECT status_code
INTO lv_state
FROM xdp_order_line_items
WHERE line_item_id = c_line_rec.line_item_id
FOR UPDATE OF STATUS_CODE NOWAIT;
dbg_msg := ('Calling UPDATE_XDP_ORDER_LINE_STATUS for Line : '||c_line_rec.line_item_id);
UPDATE_XDP_ORDER_LINE_STATUS
(p_order_id => null,
p_lineitem_id => c_line_rec.line_item_id,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code ,
error_description => error_description );
SELECT oh.status_code --- ?? Do I need to lock it again ?
INTO lv_state
FROM xdp_order_headers oh
WHERE oh.order_id = p_sdp_order_id
FOR UPDATE OF oh.status_code NOWAIT;
dbg_msg := ('Calling UPDATE_XDP_ORDER_STATUS' );
UPDATE_XDP_ORDER_STATUS
(p_order_id => p_sdp_order_id,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code,
error_description => error_description );
dbg_msg := ('Completed UPDATE_XDP_ORDER_STATUS Calling ABORT_ORDER_WORKFLOWS' );
SELECT fa_instance_id,
frt.msgid
FROM xdp_fa_runtime_list frt,
xdp_fulfill_worklist fwt
WHERE fwt.order_id = l_order_id and
fwt.workitem_instance_id = frt.workitem_instance_id;
SELECT workitem_instance_id,
msgid
FROM xdp_fulfill_worklist
WHERE order_id = l_order_id;
SELECT line_item_id
FROM xdp_order_line_items
WHERE order_id = l_order_id
ORDER By is_package_flag;
SELECT order_id ,
wf_item_type,
wf_item_key,
status_code,
msgid
INTO lv_id,
lv_item_type,
lv_item_key,
lv_state,
lv_msg_id
FROM xdp_order_headers
WHERE order_id = p_sdp_order_id
FOR UPDATE of XDP_ORDER_HEADERS.STATUS_CODE NOWAIT;
-- Simply update the state to CANCELED
update xdp_order_headers
set last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = 'CANCELED',
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
where order_id = p_sdp_order_id;
update xdp_order_line_items
set last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = 'CANCELED',
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
where order_id = p_sdp_order_id;
update XDP_FULFILL_WORKLIST
set last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = 'CANCELED',
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
where order_id = p_sdp_order_id;
select status_code into lv_state
from xdp_fa_runtime_list
where fa_instance_id = lv_fa_rec.fa_instance_id;
select status_code into lv_state
from XDP_FULFILL_WORKLIST
where workitem_instance_id = lv_wi_rec.workitem_instance_id;
select status_code
into lv_state
from xdp_order_line_items
where line_item_id = lv_line_rec.line_item_id
for update of status_code;
update xdp_order_line_items
set
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = 'CANCELED',
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
where line_item_id = lv_line_rec.line_item_id;
select status_code
into lv_state
from xdp_order_headers
where order_id = p_sdp_order_id
FOR UPDATE of XDP_ORDER_HEADERS.STATUS_CODE ;
update xdp_order_headers
set last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = 'CANCELED',
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
where order_id = p_sdp_order_id;
update XDP_FULFILL_WORKLIST
set last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = 'CANCELED',
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
where workitem_instance_id = p_wi_instance_id;
SELECT status_code
INTO lv_state
FROM xdp_fulfill_worklist
WHERE workitem_instance_id = p_wi_instance_id
FOR UPDATE of status_code;
update XDP_FULFILL_WORKLIST
set last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = 'CANCELED',
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
where workitem_instance_id = p_wi_instance_id;
update xdp_fa_runtime_list
set last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = 'CANCELED',
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
where fa_instance_id = p_fa_instance_id;
select status_code,
wf_item_type,
wf_item_key
into lv_state,
lv_item_type,
lv_item_key
from xdp_fa_runtime_list
where fa_instance_id = p_fa_instance_id
for update of status_code;
update xdp_fa_runtime_list
set last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = 'CANCELED',
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
where fa_instance_id = p_fa_instance_id;
select job_id into lv_id
from xdp_adapter_job_queue
where
wf_item_type = lv_item_type and
wf_item_key = lv_item_key
for update nowait ;
delete from xdp_adapter_job_queue
where job_id = lv_id ;
-- Since we already updated the state, we will
-- do nothing and let the process to handle
-- itself.
rollback to lv_job_tag;
select level,
item_type,
item_key,
DECODE(begin_date,NULL,'N','Y') is_active_flag,
DECODE(end_date,NULL,'N','Y') is_completed_flag
from wf_items_v
start with
item_type = l_type and item_key = l_key
connect by parent_item_type = prior item_type and
parent_item_key = prior item_key
order by level desc;
select wf_item_type,wf_item_key
into lv_type, lv_key
from xdp_order_headers
where order_id = p_sdp_order_id;
select
status_code,
null,
completion_date,
cancel_provisioning_date
into
x_status,
x_state ,
x_completion_date,
x_cancellation_date
from
XDP_ORDER_HEADERS
WHERE
order_id = p_sdp_order_id;
select
status_code,
null,
completion_date,
cancel_provisioning_date
into
x_status,
x_state,
x_completion_date,
x_cancellation_date
from
XDP_ORDER_HEADERS
WHERE
external_order_number = (p_order_number) and
NVL(external_order_version,'-1') = NVL((p_order_version),'-1') ;
select
status_code,
null,
completion_date,
cancel_provisioning_date
into
x_status,
x_state ,
x_completion_date,
x_cancellation_date
from XDP_ORDER_LINE_ITEMS
WHERE order_id = p_sdp_order_id and
line_number = p_line_number ;
select
olm.status_code,
null,
olm.completion_date,
olm.cancel_provisioning_date
into
x_status,
x_state ,
x_completion_date,
x_cancellation_date
from
XDP_ORDER_LINE_ITEMS olm,
XDP_ORDER_HEADERS ohr
WHERE
olm.order_id = ohr.order_id and
ohr.external_order_number = (p_order_number) and
NVL(ohr.external_order_version,'-1') = NVL((p_order_version) ,'-1') and
olm.line_number = p_line_number ;
select
olm.status_code,
olm.completion_date,
olm.cancel_provisioning_date
from
XDP_ORDER_LINE_ITEMS olm,
XDP_ORDER_HEADERS ohr
WHERE
olm.order_id = ohr.order_id and
ohr.external_order_number = (p_order_number) and
NVL(ohr.external_order_version,'-1') = NVL((p_order_version) ,'-1') and
olm.line_item_name = p_line_item_name ;
SELECT line_item_id
INTO l_line_item_id
FROM xdp_order_line_items
WHERE order_id = p_order_id
AND NVL(LINE_NUMBER,0) = NVL(p_line_number,0);
SELECT Order_parameter_name,
order_parameter_value
FROM xdp_order_parameters
WHERE order_id = p_order_id;
SELECT order_id,
status_code,
external_order_number,
external_order_version,
actual_provisioning_date,
completion_date
INTO x_order_status.order_id,
x_order_status.order_status,
x_order_status.order_number,
x_order_status.order_version,
x_order_status.actual_fulfillment_date,
x_order_status.completion_date
FROM XDP_ORDER_HEADERS
WHERE order_id = p_order_id;
select order_parameter_name,order_parameter_value
from xdp_order_parameters
where order_id = p_order_id;
SELECT
external_order_number,
external_order_version,
provisioning_date,
priority,
jeopardy_enabled_flag,
-- execution_mode,
-- account_number,
cust_account_id,
due_date,
customer_required_date,
customer_name,
customer_id,
telephone_number,
order_type,
order_source,
org_id,
related_order_id,
previous_order_id,
next_order_id,
order_ref_name,
order_ref_value,
order_comment,
order_id,
status_code,
completion_date,
actual_provisioning_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20
INTO
x_order_header.order_number,
x_order_header.order_version,
x_order_header.required_fulfillment_date,
x_order_header.priority,
x_order_header.jeopardy_enabled_flag,
-- x_order_header.execution_mode,
-- x_order_header.account_number,
x_order_header.cust_account_id,
x_order_header.due_date,
x_order_header.customer_required_date,
x_order_header.customer_name,
x_order_header.customer_id,
x_order_header.telephone_number,
x_order_header.order_type,
x_order_header.order_source,
x_order_header.org_id,
x_order_header.related_order_id,
x_order_header.previous_order_id,
x_order_header.next_order_id,
x_order_header.order_ref_name,
x_order_header.order_ref_value,
x_order_header.order_comments,
x_order_header.order_id,
x_order_header.order_status,
x_order_header.completion_date,
x_order_header.actual_fulfillment_date,
x_order_header.attribute_category,
x_order_header.attribute1,
x_order_header.attribute2,
x_order_header.attribute3,
x_order_header.attribute4,
x_order_header.attribute5,
x_order_header.attribute6,
x_order_header.attribute7,
x_order_header.attribute8,
x_order_header.attribute9,
x_order_header.attribute10,
x_order_header.attribute11,
x_order_header.attribute12,
x_order_header.attribute13,
x_order_header.attribute14,
x_order_header.attribute15,
x_order_header.attribute16,
x_order_header.attribute17,
x_order_header.attribute18,
x_order_header.attribute19,
x_order_header.attribute20
FROM XDP_ORDER_HEADERS
WHERE order_id = p_order_id;
SELECT account_number INTO x_order_header.account_number
FROM HZ_CUST_ACCOUNTS WHERE cust_account_id = x_order_header.cust_account_id;
SELECT
line_number,
line_source,
inventory_item_id,
line_item_name,
version,
line_item_action_code,
organization_id,
site_use_id,
ib_source,
ib_source_id,
provisioning_date,
provisioning_required_flag,
line_sequence, -- not provisioning_sequence
bundle_id,
bundle_sequence,
priority,
due_date,
jeopardy_enabled_flag,
customer_required_date,
starting_number,
ending_number,
line_item_id,
status_code, -- not line_status
completion_date,
actual_provisioning_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20
FROM
xdp_order_line_items
WHERE
order_id = p_order_id
AND
is_virtual_line_flag = 'N';
SELECT line_parameter_name,parameter_value,parameter_reference_value
FROM xdp_order_lineitem_dets
WHERE line_item_id = p_lineitem;
SELECT organization_code into x_line_list(lv_count).organization_code
FROM MTL_PARAMETERS WHERE organization_id = lv_line_rec.organization_id;
SELECT order_id into l_order_id
FROM XDP_ORDER_HEADERS
WHERE EXTERNAL_ORDER_NUMBER = (p_order_number)
AND NVL(EXTERNAL_ORDER_VERSION,'-1') = NVL((p_order_version),'-1') ;
SELECT order_id into l_order_id
FROM XDP_ORDER_HEADERS
WHERE EXTERNAL_ORDER_NUMBER = (p_order_number)
AND NVL(EXTERNAL_ORDER_VERSION,'-1') = NVL((p_order_version),'-1') ;
SELECT fa_instance_id
INTO lv_fa_instance_id
FROM xdp_adapter_job_queue
WHERE fa_instance_id = p_fa_instance_id
AND wf_item_type = p_fa_wf_item_type
AND wf_item_key = p_fa_wf_item_key
FOR UPDATE NOWAIT;
DELETE from xdp_adapter_job_queue
WHERE fa_instance_id = p_fa_instance_id
AND wf_item_type = p_fa_wf_item_type
AND wf_item_key = p_fa_wf_item_key;
dbg_msg := ('Deleted From XDP_ADAPTER_JOB_QUEUE Calling UPDATE_XDP_FA_INSTANCE_STATUS ');
UPDATE_XDP_FA_INSTANCE_STATUS
(p_fa_instance_id => p_fa_instance_id,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code ,
error_description => error_description );
dbg_msg := ('Aquired Lock on XDP_WF_CHANNEL_Q Calling UPDATE_XDP_FA_INSTANCE_STATUS');
UPDATE_XDP_FA_INSTANCE_STATUS
(p_fa_instance_id => p_fa_instance_id,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code ,
error_description => error_description );
dbg_msg := ('Aquired Lock on XDP_FA_QUEUE Calling UPDATE_XDP_FA_INSTANCE_STATUS ');
UPDATE_XDP_FA_INSTANCE_STATUS
(p_fa_instance_id => p_fa_instance_id,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code ,
error_description => error_description );
dbg_msg := ('Completed UPDATE_XDP_FA_INSTANCE_STATUS ');
dbg_msg := ('FA Status is '||p_status||' Calling UPDATE_XDP_FA_INSTANCE_STATUS ');
UPDATE_XDP_FA_INSTANCE_STATUS
(p_fa_instance_id => p_fa_instance_id,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code ,
error_description => error_description );
dbg_msg := ('Completed UPDATE_XDP_FA_INSTANCE_STATUS ');
dbg_msg := ('Workitem Status is :' ||p_status||' Calling UPDATE_XDP_WI_INSTANCE_STATUS ');
UPDATE_XDP_WI_INSTANCE_STATUS
(p_order_id => null,
p_wi_instance_id => p_wi_instance_id,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code,
error_description => error_description );
dbg_msg := ('Workitem Removed From XDP_WORKITEM_QUEUE Calling UPDATE_XDP_WI_INSTANCE_STATUS ');
UPDATE_XDP_WI_INSTANCE_STATUS
(p_order_id => null,
p_wi_instance_id => p_wi_instance_id,
p_status => 'CANCELED',
p_caller_name => p_caller_name,
return_code => return_code,
error_description => error_description );
PROCEDURE update_xdp_order_status
(p_order_id IN NUMBER ,
p_status IN VARCHAR2,
p_caller_name IN VARCHAR2,
return_code OUT NOCOPY NUMBER,
error_description OUT NOCOPY VARCHAR2) IS
BEGIN
IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_ORDER_STATUS')) THEN
dbg_msg := ('Procedure XDP_INTERFACES.UPDATE_XDP_ORDER_STATUS begins.');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_ORDER_STATUS', dbg_msg);
UPDATE xdp_order_headers
SET last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = p_status,
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
WHERE order_id = p_order_id ;
FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB. UPDATE_XDP_ORDER_STATUS');
END UPDATE_XDP_ORDER_STATUS;
PROCEDURE UPDATE_XDP_ORDER_LINE_STATUS
(p_order_id IN NUMBER,
p_lineitem_id IN NUMBER ,
p_status IN VARCHAR2,
p_caller_name IN VARCHAR2,
return_code OUT NOCOPY NUMBER,
error_description OUT NOCOPY VARCHAR2) IS
BEGIN
IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_ORDER_LINE_STATUS')) THEN
dbg_msg := ('Procedure XDP_INTERFACES.UPDATE_XDP_ORDER_LINE_STATUS begins.');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_ORDER_LINE_STATUS', dbg_msg);
UPDATE xdp_order_line_items
SET last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = p_status,
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
WHERE order_id = NVL(p_order_id ,order_id)
AND line_item_id = NVL(p_lineitem_id , line_item_id) ;
FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.UPDATE_XDP_ORDER_LINE_STATUS');
END UPDATE_XDP_ORDER_LINE_STATUS;
PROCEDURE UPDATE_XDP_WI_INSTANCE_STATUS
(p_order_id IN NUMBER,
p_wi_instance_id IN NUMBER ,
p_status IN VARCHAR2,
p_caller_name IN VARCHAR2,
return_code OUT NOCOPY NUMBER,
error_description OUT NOCOPY VARCHAR2) IS
BEGIN
IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_WI_INSTANCE_STATUS')) THEN
dbg_msg := ('Procedure XDP_INTERFACES.UPDATE_XDP_WI_INSTANCE_STATUS begins.');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_WI_INSTANCE_STATUS', dbg_msg);
UPDATE xdp_fulfill_worklist
SET last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = p_status,
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
WHERE order_id = NVL(p_order_id , order_id )
AND workitem_instance_id = NVL(p_wi_instance_id,workitem_instance_id);
FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.UPDATE_XDP_WI_INSTANCE_STATUS');
END UPDATE_XDP_WI_INSTANCE_STATUS;
PROCEDURE UPDATE_XDP_FA_INSTANCE_STATUS
(p_fa_instance_id IN NUMBER ,
p_status IN VARCHAR2,
p_caller_name IN VARCHAR2,
return_code OUT NOCOPY NUMBER,
error_description OUT NOCOPY VARCHAR2) IS
BEGIN
IF((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
IF (FND_LOG.TEST(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_FA_INSTANCE_STATUS')) THEN
dbg_msg := ('Procedure XDP_INTERFACES.UPDATE_XDP_FA_INSTANCE_STATUS begins.');
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'xdp.plsql.XDP_INTERFACES.UPDATE_XDP_FA_INSTANCE_STATUS', dbg_msg);
UPDATE xdp_fa_runtime_list
SET last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID,
status_code = p_status,
canceled_by = p_caller_name,
cancel_provisioning_date = sysdate
WHERE fa_instance_id = p_fa_instance_id;
FND_MESSAGE.SET_TOKEN('API_NAME', 'XDPINTFB.UPDATE_XDP_FA_INSTANCE_STATUS');
END UPDATE_XDP_FA_INSTANCE_STATUS;
UPDATE xnp_msgs
SET msg_status = 'CANCELED',
last_update_date = SYSDATE
WHERE order_id = p_order_id
AND msg_status = 'READY';
SELECT line_item_id
INTO l_line_item_id
FROM xdp_order_line_items xoli
WHERE xoli.order_id = p_order_id
AND xoli.line_number = p_line_number;
SELECT line_item_id
INTO l_line_item_id
FROM xdp_order_line_items xoli
WHERE xoli.order_id = p_order_id
AND xoli.line_number = p_line_number;