[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select KL.dnz_chr_id, KL.start_date, NVL(KL.date_terminated - 1,KL.end_date) end_date,
KI.number_of_items, KI.uom_code,
NVL(MTL.contract_item_type_code,'NON-SUB'), MTL.coverage_schedule_id, MTL.comms_nl_trackable_flag
From okc_k_lines_b KL,
okc_k_items KI,
mtl_system_items MTL
Where KL.id = p_cle_id
and KI.cle_id = p_cle_id
and MTL.inventory_item_id = TO_NUMBER(KI.object1_id1)
and MTL.organization_id = TO_NUMBER(KI.object1_id2);
Select name,
description,
cle_id,
dnz_chr_id,
subscription_type,
media_type,
frequency,
fulfillment_channel,
comments,
status,
item_type
From oks_subscr_header_v
Where id = p_template_id;
'oks_subscr_hdr_pub.insert_row');
OKS_SUBSCR_HDR_PUB.insert_row
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_schv_tbl => l_hdr_tbl_in,
x_schv_tbl => l_hdr_tbl_out
);
'oks_subscr_hdr_pub.insert_row(x_return_status = '||x_return_status||')');
'oks_subscr_ptrns_pub.insert_row');
OKS_SUBSCR_PTRNS_PUB.insert_row
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scpv_tbl => l_ptrns_tbl_in,
x_scpv_tbl => l_ptrns_tbl_out
);
'oks_subscr_ptrns_pub.insert_row(x_return_status = '||x_return_status||')');
'oks_subscr_elems_pub.insert_row');
OKS_SUBSCR_ELEMS_PUB.insert_row
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scev_tbl => l_elems_tbl_in,
x_scev_tbl => l_elems_tbl_out
);
'oks_subscr_elems_pub.insert_row(x_return_status = '||x_return_status||')');
Select KL.dnz_chr_id, KL.start_date, NVL(KL.date_terminated - 1,KL.end_date) end_date,
KL.price_negotiated, KI.number_of_items, KI.uom_code,
NVL(MTL.contract_item_type_code,'NON-SUB')
From okc_k_lines_b KL,
okc_k_items KI,
mtl_system_items MTL
Where KL.id = p_cle_id
and KI.cle_id = p_cle_id
and MTL.inventory_item_id = TO_NUMBER(KI.object1_id1)
and MTL.organization_id = TO_NUMBER(KI.object1_id2);
Select id,
instance_id,
frequency,
fulfillment_channel,
offset
From oks_subscr_header_b
Where cle_id = p_cle_id;
Select NVL(amount,0)/quantity unitprice, uom_code
From oks_subscr_elements
Where dnz_cle_id = p_cle_id
And rownum < 2;
Select id, object_version_number, year, month, week, week_day, day
From oks_subscr_patterns
Where dnz_cle_id = p_cle_id;
Select id, start_date, end_date, quantity, uom_code,
om_interface_date, order_header_id, object_version_number
From oks_subscr_elements
Where dnz_cle_id = p_cle_id;
Select Sum(NVL(quantity,0))
From oks_subscr_elements
Where dnz_cle_id = p_cle_id;
procedure create_insert_rec Is
begin
ins_idx := ins_idx + 1;
end create_insert_rec;
procedure create_update_rec Is
begin
upd_idx := upd_idx + 1;
l_elems_tbl_upd_in(upd_idx).last_updated_by := OKC_API.G_MISS_NUM;
l_elems_tbl_upd_in(upd_idx).last_update_date := OKC_API.G_MISS_DATE;
l_elems_tbl_upd_in(upd_idx).last_update_login := OKC_API.G_MISS_NUM;
end create_update_rec;
procedure create_delete_rec Is
begin
del_idx := del_idx + 1;
end create_delete_rec;
'start date changed. the single record pattern will be updated');
'oks_subscr_ptrns_pub.update_row(p_scpv_tbl(1).id = '||l_ptrns_tbl_in(1).id||')');
OKS_SUBSCR_PTRNS_PUB.update_row
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scpv_tbl => l_ptrns_tbl_in,
x_scpv_tbl => l_ptrns_tbl_out
);
'oks_subscr_ptrns_pub.update_row(x_return_status = '||x_return_status||')');
'marking element for update: period start date - '
||to_char(l_delivery_tbl(i).start_date,'DD-MON-YYYY')
);
create_update_rec;
tmp_elem_tbl.DELETE(idx);
'not marking element for update since shipped: period start date - '
||to_char(l_delivery_tbl(i).start_date,'DD-MON-YYYY')
);
'marking element for insert: period start date - '
||to_char(l_delivery_tbl(i).start_date,'DD-MON-YYYY')
);
create_insert_rec;
'marking element for delete: period start date - '
||to_char(tmp_elem_tbl(i).start_date,'DD-MON-YYYY')
);
create_delete_rec;
'not marking element for delete since shipped: period start date - '
||to_char(tmp_elem_tbl(i).start_date,'DD-MON-YYYY')
);
'oks_subscr_elems_pub.delete_row');
OKS_SUBSCR_ELEMS_PUB.delete_row
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scev_tbl => l_elems_tbl_del_in
);
'oks_subscr_elems_pub.delete_row(x_return_status = '||x_return_status||')');
'oks_subscr_elems_pub.update_row');
OKS_SUBSCR_ELEMS_PUB.update_row
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scev_tbl => l_elems_tbl_upd_in,
x_scev_tbl => l_elems_tbl_out
);
'oks_subscr_elems_pub.update_row(x_return_status = '||x_return_status||')');
'oks_subscr_elems_pub.insert_row');
OKS_SUBSCR_ELEMS_PUB.insert_row
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scev_tbl => l_elems_tbl_ins_in,
x_scev_tbl => l_elems_tbl_out
);
'oks_subscr_elems_pub.insert_row(x_return_status = '||x_return_status||')');
SELECT id,item_type
FROM oks_subscr_header_b
WHERE cle_id = p_cle_id;
UPDATE oks_subscr_header_b
SET instance_id = l_instance_id
WHERE id = l_osh_id;
Select dnz_chr_id
From okc_k_lines_b
Where id = p_target_cle_id
And lse_id = 46;
Select id
From okc_k_lines_b
Where id = p_source_cle_id
And lse_id = 46;
select name, description, instance_id, subscription_type, media_type, frequency,
fulfillment_channel, offset, comments, item_type
from oks_subscr_header_v
where cle_id = p_source_cle_id;
'oks_subscr_hdr_pub.insert_row');
OKS_SUBSCR_HDR_PUB.insert_row
( p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_schv_tbl => l_schv_tbl_in,
x_schv_tbl => l_schv_tbl_out);
'oks_subscr_hdr_pub.insert_row(x_return_status = '||x_return_status||')');
select year, month, week, week_day, day
from oks_subscr_patterns
where dnz_cle_id = p_source_cle_id;
'oks_subscr_ptrns_pub.insert_row');
OKS_SUBSCR_PTRNS_PUB.insert_row
( p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scpv_tbl => l_scpv_tbl_in,
x_scpv_tbl => l_scpv_tbl_out
);
'oks_subscr_ptrns_pub.insert_row(x_return_status = '||x_return_status||')');
Procedure delete_cii Is
Cursor schv_cur Is
Select id, instance_id, fulfillment_channel
From oks_subscr_header_b
Where cle_id = p_cle_id;
Select Null
From oks_subscr_header_b
Where instance_id = l_instance_id
And cle_id <> p_cle_id;
fnd_log.string(FND_LOG.LEVEL_STATEMENT,G_MODULE_CURRENT||'.undo_subscription.delete_cii.osh_details',
'Osh Id = '||l_osh_id||' ,Fulfillment Channel = '||l_ff_chan);
OKS_AUTH_UTIL_PUB.delete_cii_for_subscription
( p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_id => p_cle_id
);
End delete_cii;
Procedure delete_osh Is
l_schv_tbl OKS_SUBSCR_HDR_PUB.schv_tbl_type;
fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_osh.external_call.before',
'oks_subscr_hdr_pub.delete_row(p_schv_tbl(1).id = '||l_schv_tbl(1).id||')');
OKS_SUBSCR_HDR_PUB.delete_row
( p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_schv_tbl => l_schv_tbl
);
fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_osh.external_call.after',
'oks_subscr_hdr_pub.delete_row(x_return_status = '||x_return_status||')');
End delete_osh;
Procedure delete_osp Is
Cursor scpv_cur Is
Select id
From oks_subscr_patterns
Where dnz_cle_id = p_cle_id;
fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_osp.external_call.before',
'oks_subscr_ptrns_pub.delete_row(p_scpv_tbl.COUNT = '||i||')');
OKS_SUBSCR_PTRNS_PUB.delete_row
( p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scpv_tbl => l_scpv_tbl
);
fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_osp.external_call.after',
'oks_subscr_ptrns_pub.delete_row(x_return_status = '||x_return_status||')');
End delete_osp;
Procedure delete_ose Is
Cursor scev_cur Is
Select id
From oks_subscr_elements
Where dnz_cle_id = p_cle_id;
fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_ose.external_call.before',
'oks_subscr_elems_pub.delete_row(p_scev_tbl.COUNT = '||i||')');
OKS_SUBSCR_ELEMS_PUB.delete_row
( p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scev_tbl => l_scev_tbl
);
fnd_log.string(FND_LOG.LEVEL_EVENT,G_MODULE_CURRENT||'.undo_subscription.delete_ose.external_call.after',
'oks_subscr_elems_pub.delete_row(x_return_status = '||x_return_status||')');
End delete_ose;
delete_cii; -- (Delete the item instance if one exists and) Get the OSH.id and fulfillment channel
delete_ose; -- delete the subscription elements
delete_osp; -- delete the subscription patterns
delete_osh; -- delete the subscription header (tangible or intangible)
l_tab.delete(j);
l_tab.delete(j);
Select number_of_items, uom_code
From okc_k_items
Where cle_id = p_cle_id;
Select item_type, frequency
From oks_subscr_header_b
Where cle_id = p_cle_id;
Select sum(quantity)
From oks_subscr_elements
Where dnz_cle_id = p_cle_id;
Select start_date, end_date
From okc_k_lines_b
Where id = p_cle_id;
SELECT dnz_chr_id
FROM okc_k_lines_b
WHERE id = p_cle_id;
Select start_date, end_date, price_negotiated
From okc_k_lines_b
Where id = p_cle_id
And lse_id = 46;
Select item_type, frequency
From oks_subscr_header_b
Where cle_id = p_cle_id;
Select sum(amount)
From oks_subscr_elements
Where dnz_cle_id = p_cle_id
And (start_date < p_termn_date Or order_header_id Is Not Null);
SELECT dnz_chr_id
FROM okc_k_lines_b
WHERE id = p_cle_id;
Select item_type From oks_subscr_header_b Where cle_id = p_cle_id;
Select uom_code
From okc_time_code_units_v
Where tce_code = p_tce
And quantity = p_qty
And active_flag = 'Y';