[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ID
FROM oks_stream_levels_v
WHERE cle_id = p_cle_id;
SELECT 'x'
FROM oks_stream_levels_v sll, oks_level_elements lvl
WHERE lvl.rul_id = sll.ID AND sll.cle_id = p_cle_id;
SELECT oh.header_id, oh.order_number
FROM oe_order_lines_all ol, oe_order_headers_all oh
WHERE ol.line_id = (p_ordlineid)
AND oh.org_id = okc_context.get_okc_org_id
AND oh.header_id = ol.header_id;
SELECT org_id, ship_from_org_id, sold_from_org_id,
NVL (fulfilled_quantity, 0) fqty,
service_reference_line_id rolineid, header_id
FROM okx_order_lines_v
WHERE id1 = (p_ordlineid);
SELECT 'x'
FROM oks_reprocessing
WHERE order_line_id = p_ordlineid;
SELECT comms_nl_trackable_flag
FROM mtl_system_items_b
WHERE inventory_item_id = (SELECT inventory_item_id
FROM oe_order_lines_all
WHERE line_id = l_ref_order_line_id)
AND organization_id = l_organization_id
AND serviceable_product_flag = 'Y' ; /*BUG6181908 -- FP Bug#6006309*/
Select mtl.comms_nl_trackable_flag
From mtl_system_items_b mtl
,csi_item_instances csi
Where csi.instance_id = l_ref_order_line_id
and mtl.inventory_item_id = csi.inventory_item_id
and mtl.organization_id = l_organization_id
and mtl. serviceable_product_flag = 'Y';
fnd_file.put_line (fnd_file.LOG, 'No lines to insert');
SAVEPOINT before_insert;
oks_rep_pub.insert_row
(p_api_version => 1.0,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_repv_rec => l_repv_rec,
x_repv_rec => l_out_repv_rec
);
SELECT INSTR (l_msg_data,
'ORA-00001',
1,
1
)
INTO index1
FROM DUAL;
'oks_rep_pub.insert_row: '
|| l_msg_data
);
ROLLBACK TO before_insert;
ROLLBACK TO before_insert;
SELECT object_version_number
FROM oks_reprocessing_v
WHERE ID = p_id;
oks_rep_pub.update_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_repv_rec => l_repv_rec,
x_repv_rec => l_new_repv_rec
);
'Update Row : Return Status = ' || l_return_status
);
'Update Row Error : ' || l_msg_data
);
SELECT csi.instance_id cp_id, csi.inventory_item_id, mtl.NAME NAME,
mtl.description description, csi.quantity quantity,
csi.unit_of_measure uom_code
FROM csi_item_instances csi, okx_system_items_v mtl
WHERE csi.instance_id = cpid
AND mtl.inventory_item_id = csi.inventory_item_id
AND ROWNUM < 2;
SELECT org_id, ship_from_org_id, sold_from_org_id,
NVL (fulfilled_quantity, 0) fqty,
service_reference_line_id rolineid, header_id
FROM okx_order_lines_v
WHERE id1 = (p_ordlineid);
SELECT rel.cle_id, rel.chr_id
FROM okc_k_rel_objs_v rel, okc_k_lines_b line
WHERE rel.object1_id1 = TO_CHAR (p_ordlineid)
AND rel.jtot_object1_code = 'OKX_ORDERLINE'
AND line.ID = rel.cle_id
AND line.lse_id IN (9, 25);
SELECT oc.object1_id1, oc.cro_code
FROM oks_k_order_contacts_v oc, oks_k_order_details_v od
WHERE oc.cod_id = od.ID AND od.order_line_id1 = p_line_id;
SELECT csi.instance_id cp_id
FROM csi_item_instances csi, csi_instance_statuses st
WHERE csi.last_oe_order_line_id = p_ordlineid
AND NVL (st.service_order_allowed_flag, 'N') = 'Y'
AND csi.instance_status_id = st.instance_status_id;
SELECT csi.instance_id cp_id
FROM csi_item_instances csi
,csi_instance_statuses st
,mtl_system_items_b mtl
WHERE csi.last_oe_order_line_id = p_ordlineid
AND csi.instance_status_id = st.instance_status_id
AND mtl.inventory_item_id = csi.inventory_item_id
AND mtl.organization_id = c_organization_id
AND Nvl(st.service_order_allowed_flag,'N') = 'Y'
AND mtl.comms_nl_trackable_flag = 'Y'
AND mtl.serviceable_product_flag = 'Y' ;
SELECT master_organization_id
FROM oe_system_parameters_all
WHERE org_id = p_org_id;
SELECT service_reference_type_code, service_reference_line_id
FROM okx_order_lines_v
WHERE id1 = p_ordline_id;
SELECT cle_id
FROM okc_k_lines_b
WHERE ID = p_subline_id;
SELECT pricing_quantity
FROM oe_order_lines_all
WHERE line_id=p_ordline_id;*/
SELECT ordered_quantity
FROM oe_order_lines_all
WHERE line_id=p_ordline_id;
l_update_line VARCHAR2 (1);
SELECT NAME
FROM okx_parties_v
WHERE id1 = l_k_header_rec.party_id;
SELECT csi.install_date, ol.actual_shipment_date,
mtl.service_starting_delay
FROM csi_item_instances csi,
oe_order_lines_all ol,
okx_system_items_v mtl
WHERE csi.instance_id = p_cp_id
AND csi.last_oe_order_line_id = ol.line_id
AND csi.inventory_item_id = mtl.id1
AND ROWNUM < 2;
SELECT scs_code
FROM okc_k_headers_v
WHERE ID = p_chr_id;
Select sv.ste_code,
kh.contract_number||Decode(kh.contract_number_modifier,NULL,NULL,
' - '||kh.contract_number_modifier) k_number
From OKC_STATUSES_V sv,
OKC_K_HEADERS_V kh
Where kh.id = p_chr_id
and kh.sts_code = sv.CODE;
SELECT inventory_item_id
FROM oe_order_lines --mmadhavi for MOAC
WHERE line_id = p_ordline_id;
SELECT start_date, end_date
FROM okc_k_lines_b
WHERE ID = p_id;
SELECT start_date, end_date, sts_code
FROM okc_k_headers_b
WHERE ID = p_id;
SELECT instance_number
FROM csi_item_instances
WHERE instance_id = p_cp_id;
SELECT
SUM(NVL(slines.tax_amount,0)) tax_amount
FROM
okc_k_lines_b clines
,oks_k_lines_b slines
WHERE
clines.dnz_chr_id = p_chr_id
and clines.cle_id = p_line_id
and clines.id = slines.cle_id;
SELECT
SUM(NVL(slines.tax_amount,0)) tax_amount
FROM
okc_k_lines_b clines
,oks_k_lines_b slines
WHERE
clines.dnz_chr_id = p_chr_id
AND clines.lse_id IN (1, 19, 46) ---Modified for ER 16039680
AND clines.id = slines.cle_id;
SELECT b.contract_item_type_code
FROM csi_item_instances csi,oe_order_lines_all ol,
mtl_system_items_b_kfv b,
mtl_system_items_tl t
WHERE csi.last_oe_order_line_id=ol.line_id
AND csi.instance_id = l_instance_id
AND b.inventory_item_id = ol.inventory_item_id
AND b.inventory_item_id = t.inventory_item_id
AND b.organization_id = t.organization_id
AND t.LANGUAGE = USERENV ('LANG')
AND b.organization_id = okc_context.get_okc_organization_id;
SELECT okro.chr_id
FROM okc_k_rel_objs okro,oe_order_lines_all ol,csi_item_instances csi
WHERE ol.line_id=okro.object1_id1
AND csi.last_oe_order_line_id=ol.line_id
AND csi.instance_id=p_instance_id
AND ol.header_id=p_order_header_id;
SELECT Nvl(SERVICE_BILL_OPTION_CODE,'FBOM'),SERVICE_BILL_PROFILE_ID,ordered_quantity
FROM oe_order_lines_all
WHERE line_id=p_order_line_id;
is select nvl(price_negotiated,0) from okc_k_lines_b where id = c_line_id;
x_upd_tbl.DELETE;
l_contact_tbl.DELETE;
l_salescredit_tbl_in.DELETE;
'Inserting error msg ...'
|| x_upd_tbl (1).ERROR_TEXT
);
x_update_line => l_update_line,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_update_line = 'Y' THEN
l_eff_line_upd_flag := 'Y';
oks_ins_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_insv_rec => l_insthist_rec,
x_insv_rec => x_insthist_rec
);
oks_ihd_pvt.insert_row (p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_ihdv_rec => l_inst_dtls_rec,
x_ihdv_rec => x_inst_dtls_rec
);
UPDATE oks_k_lines_b
SET toplvl_price_qty = (SELECT Sum(quantity)
FROM OKS_SUBSCR_ELEMENTS_V
WHERE DNZ_CLE_ID=l_lineid),
toplvl_uom_code = (SELECT uom_code
FROM okc_k_items
WHERE cle_id=l_lineid)
WHERE cle_id=l_lineid;
UPDATE okc_k_lines_b
SET price_negotiated =l_negotiated_amount,
price_unit =l_list_price
WHERE id=l_lineid;
UPDATE okc_k_lines_b
SET price_negotiated =
(SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
FROM okc_k_lines_b
WHERE cle_id = l_lineid AND dnz_chr_id = l_chrid)
WHERE ID = l_lineid;
UPDATE okc_k_headers_all_b --mmadhavi _all for MOAC
SET estimated_amount =
(SELECT NVL (SUM (NVL (price_negotiated, 0)), 0)
FROM okc_k_lines_b
WHERE dnz_chr_id = l_chrid AND lse_id IN (1, 19, 46)) ---Modified for ER16039680
WHERE ID = l_chrid;
UPDATE oks_k_lines_b
SET tax_amount = l_line_tax_amount
WHERE cle_id = l_lineid;
UPDATE oks_k_headers_b
SET tax_amount = l_header_tax_amount
WHERE chr_id = l_chrid;
SELECT date_end INTO l_date_end FROM oks_level_elements WHERE cle_id=l_lineid and date_start =l_k_line_rec.srv_sdt;
l_update_line := NVL(l_eff_line_upd_flag,l_update_line);
IF l_update_line = 'Y'
THEN
oks_bill_sch.update_om_sll_date
(p_top_line_id => l_lineid,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
'IBNEW :- Update_OM_SLL_Date '
|| l_return_status
);
' OKS_BILL_SCH.UPDATE_OM_SLL_DATE(Return status = '
|| l_return_status
|| ')'
);
SELECT ID, order_id, order_line_id, success_flag, source_flag,
rep.order_number
FROM oks_reprocessing_v rep, oe_order_headers oh
WHERE success_flag = 'R'
AND rep.order_id = oh.header_id
AND conc_request_id IS NULL;
SELECT ID, order_id, order_line_id, success_flag, source_flag,
rep.order_number
FROM oks_reprocessing_v rep, oe_order_headers oh
WHERE reprocess_yn = 'Y' --success_flag IN ('R','N')
AND rep.order_id = oh.header_id
AND conc_request_id IS NULL;
SELECT ID, order_id, order_line_id, success_flag, source_flag,
rep.order_number
FROM oks_reprocessing_v rep, oe_order_headers oh
WHERE NVL (success_flag, 'E') IN ('E', 'N') ---IN ('R','N','E')
AND rep.order_id = oh.header_id
AND conc_request_id IS NULL;
SELECT ol.line_id, NVL (fulfilled_quantity, 0),
service_reference_type_code, service_reference_line_id
FROM oe_order_lines_all ol, oe_order_headers oh
WHERE oh.header_id = ol.header_id
AND oh.order_number = p_ord_num;
Select Nvl(FULFILLED_QUANTITY,0),SERVICE_REFERENCE_TYPE_CODE,SERVICE_REFERENCE_LINE_ID
From oe_order_lines_all
Where line_id = p_ord_line_id
and header_id IN (select header_id from oe_order_headers_all where org_id = okc_context.get_okc_org_id);
SELECT 'x'
FROM oks_reprocessing rep, oe_order_headers hdr
WHERE rep.order_line_id = p_ordline_id
AND rep.order_id = hdr.header_id;
'More lines ..Inserting..'
);
oks_rep_pub.insert_row
(p_api_version => 1.0,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_repv_rec => l_repv_rec,
x_repv_rec => l_out_repv_rec
);
'Insert status = '
|| l_return_status
);
oks_rep_pub.insert_row
(p_api_version => 1.0,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_repv_rec => l_repv_rec,
x_repv_rec => l_out_repv_rec
);
'oks_rep_pub.insert_row : return status => '
|| l_return_status
);
'Insert status ...'
|| l_return_status
);
l_upd_tbl.DELETE;
l_cp_tbl.DELETE;
oks_rep_pub.insert_row
(p_api_version => 1.0,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_repv_rec => l_upd_tbl
(i),
x_repv_rec => l_out_repv_rec
);
'ORDER_REPROCESS.Insert_row'
|| l_msg_data
);
oks_rep_pub.insert_row
(p_api_version => 1.0,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_repv_rec => l_upd_tbl
(i),
x_repv_rec => l_out_repv_rec
);
'ORDER_REPROCESS.Insert_row'
|| l_msg_data
);
SELECT ID
FROM oks_reprocessing_v
WHERE success_flag = 'S';
oks_rep_pub.delete_row (p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_repv_rec => l_del_rec
);
SELECT oh.header_id, oh.order_number
FROM oe_order_lines_all ol, oe_order_headers_all oh
WHERE ol.line_id = (p_ordlineid)
AND oh.org_id = okc_context.get_okc_org_id
AND oh.header_id = ol.header_id;
SELECT org_id, ship_from_org_id, sold_from_org_id,
NVL (fulfilled_quantity, 0) fqty,
service_reference_line_id rolineid, header_id
FROM okx_order_lines_v
WHERE id1 = (p_ordlineid);
SELECT 'x'
FROM oks_reprocessing
WHERE order_line_id = p_ordlineid;
SELECT comms_nl_trackable_flag
FROM mtl_system_items_b
WHERE inventory_item_id = (SELECT inventory_item_id
FROM oe_order_lines_all
WHERE line_id = l_ref_order_line_id)
AND organization_id = l_organization_id;
fnd_file.put_line (fnd_file.LOG, 'No lines to insert');
SAVEPOINT before_insert;
oks_rep_pub.insert_row
(p_api_version => 1.0,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_repv_rec => l_repv_rec,
x_repv_rec => l_out_repv_rec
);
SELECT INSTR (l_msg_data,
'ORA-00001',
1,
1
)
INTO index1
FROM DUAL;
'oks_rep_pub.insert_row: '
|| l_msg_data
);
ROLLBACK TO before_insert;
ROLLBACK TO before_insert;
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;
SELECT instance_number
FROM csi_item_instances
WHERE instance_id = p_cp_id;
SELECT okhb.start_date,okhb.end_date,okcl.cust_acct_id,okcl.sts_code
FROM okc_k_headers_all_b okhb,okc_k_lines_b okcl
WHERE okhb.id=okcl.chr_id
AND okcl.id=p_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_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||')');