[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ID,
CLE_ID,
CHR_ID,
RTY_CODE,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM Okc_K_Rel_Objs
WHERE okc_k_rel_objs.id = p_id;
l_crj_rec.LAST_UPDATED_BY,
l_crj_rec.LAST_UPDATE_DATE,
l_crj_rec.LAST_UPDATE_LOGIN,
l_crj_rec.ATTRIBUTE_CATEGORY,
l_crj_rec.ATTRIBUTE1,
l_crj_rec.ATTRIBUTE2,
l_crj_rec.ATTRIBUTE3,
l_crj_rec.ATTRIBUTE4,
l_crj_rec.ATTRIBUTE5,
l_crj_rec.ATTRIBUTE6,
l_crj_rec.ATTRIBUTE7,
l_crj_rec.ATTRIBUTE8,
l_crj_rec.ATTRIBUTE9,
l_crj_rec.ATTRIBUTE10,
l_crj_rec.ATTRIBUTE11,
l_crj_rec.ATTRIBUTE12,
l_crj_rec.ATTRIBUTE13,
l_crj_rec.ATTRIBUTE14,
l_crj_rec.ATTRIBUTE15;
SELECT
ID,
OBJECT_VERSION_NUMBER,
CLE_ID,
CHR_ID,
RTY_CODE,
OBJECT1_ID1,
OBJECT1_ID2,
JTOT_OBJECT1_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM Okc_K_Rel_Objs
WHERE okc_k_rel_objs.id = p_id;
l_crjv_rec.LAST_UPDATED_BY,
l_crjv_rec.LAST_UPDATE_DATE,
l_crjv_rec.LAST_UPDATE_LOGIN;
IF (l_crjv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
l_crjv_rec.last_updated_by := NULL;
IF (l_crjv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
l_crjv_rec.last_update_date := NULL;
IF (l_crjv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
l_crjv_rec.last_update_login := NULL;
SELECT 'x' from okc_k_headers_b
where id=p_crjv_rec.object1_id1;
SELECT 'x' from okc_k_lines_b
where id=p_crjv_rec.object1_id1;
--SELECT
-- LOOKUP_TYPE,
-- LOOKUP_CODE,
-- MEANING,
-- DESCRIPTION,
-- ENABLED_FLAG,
-- START_DATE_ACTIVE,
-- END_DATE_ACTIVE
--FROM Fnd_Lookups
--WHERE fnd_lookups.lookup_code = p_lookup_code;
SELECT
ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
CHR_ID,
CLE_ID,
LSE_ID,
LINE_NUMBER,
STS_CODE,
DISPLAY_SEQUENCE,
TRN_CODE,
DNZ_CHR_ID,
COMMENTS,
ITEM_DESCRIPTION,
HIDDEN_IND,
PRICE_NEGOTIATED,
PRICE_LEVEL_IND,
INVOICE_LINE_LEVEL_IND,
DPAS_RATING,
BLOCK23TEXT,
EXCEPTION_YN,
TEMPLATE_USED,
DATE_TERMINATED,
NAME,
START_DATE,
END_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
PRICE_TYPE,
CURRENCY_CODE,
LAST_UPDATE_LOGIN
FROM Okc_K_Lines_V
WHERE okc_k_lines_v.id = p_id;
SELECT
ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
CHR_ID_RESPONSE,
CHR_ID_AWARD,
STS_CODE,
QCL_ID,
SCS_CODE,
CONTRACT_NUMBER,
CURRENCY_CODE,
CONTRACT_NUMBER_MODIFIER,
ARCHIVED_YN,
DELETED_YN,
CUST_PO_NUMBER_REQ_YN,
PRE_PAY_REQ_YN,
CUST_PO_NUMBER,
SHORT_DESCRIPTION,
COMMENTS,
DESCRIPTION,
DPAS_RATING,
COGNOMEN,
TEMPLATE_YN,
TEMPLATE_USED,
DATE_APPROVED,
DATETIME_CANCELLED,
AUTO_RENEW_DAYS,
DATE_ISSUED,
DATETIME_RESPONDED,
NON_RESPONSE_REASON,
NON_RESPONSE_EXPLAIN,
RFP_TYPE,
CHR_TYPE,
KEEP_ON_MAIL_LIST,
SET_ASIDE_REASON,
SET_ASIDE_PERCENT,
RESPONSE_COPIES_REQ,
DATE_CLOSE_PROJECTED,
DATETIME_PROPOSED,
DATE_SIGNED,
DATE_TERMINATED,
DATE_RENEWED,
TRN_CODE,
START_DATE,
END_DATE,
AUTHORING_ORG_ID,
BUY_OR_SELL,
ISSUE_OR_RECEIVE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM Okc_K_Headers_V
WHERE okc_k_headers_v.id = p_id;
SELECT --replaced the SQL for Bug 3094073
id
FROM
okc_k_rel_objs o
WHERE ( nvl(o.chr_id, -99) = nvl(p_crjv_rec.chr_id, -99)
and (-- lines same (or both null)
o.cle_id = p_crjv_rec.cle_id
or (-- both null
o.cle_id is null
and p_crjv_rec.cle_id is null
)
)
and o.rty_code = p_crjv_rec.rty_code
and o.jtot_object1_code = p_crjv_rec.jtot_object1_code
and o.object1_id1 = p_crjv_rec.object1_id1
and (-- object id same (or both null)
o.object1_id2 = p_crjv_rec.object1_id2
or (-- both null
o.object1_id2 is null
and p_crjv_rec.object1_id2 is null
)
)
) ;
SELECT
id
FROM
okc_k_rel_objs o
WHERE (-- all data same
(-- headers same (or both null)
o.chr_id = p_crjv_rec.chr_id
or (-- both null
o.chr_id is null
and p_crjv_rec.chr_id is null
)
)
and (-- lines same (or both null)
o.cle_id = p_crjv_rec.cle_id
or (-- both null
o.cle_id is null
and p_crjv_rec.cle_id is null
)
)
--and o.rty_code = p_crjv_rec.rty_code
and o.rty_code = 'CONTRACTNEGOTIATESQUOTE'
and o.jtot_object1_code = p_crjv_rec.jtot_object1_code
)
;
SELECT
id
FROM
okc_k_rel_objs o
WHERE (-- all data same
o.rty_code = p_crjv_rec.rty_code
-- and o.rty_code <> 'CONTRACTNEGOTIATESQUOTE' Bug# 1255862
and o.rty_code not in ('CONTRACTNEGOTIATESQUOTE', 'CONTRACTSERVICESORDER')
and o.jtot_object1_code = p_crjv_rec.jtot_object1_code
and o.object1_id1 = p_crjv_rec.object1_id1
and (-- object id same (or both null)
o.object1_id2 = p_crjv_rec.object1_id2
or (-- both null
o.object1_id2 is null
and p_crjv_rec.object1_id2 is null
)
)
)
;
p_to.last_updated_by := p_from.last_updated_by;
p_to.last_update_date := p_from.last_update_date;
p_to.last_update_login := p_from.last_update_login;
p_to.last_updated_by := p_from.last_updated_by;
p_to.last_update_date := p_from.last_update_date;
p_to.last_update_login := p_from.last_update_login;
PROCEDURE insert_row(
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_crj_rec IN crj_rec_type,
x_crj_rec OUT NOCOPY crj_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'OBJS_insert_row';
okc_util.print_trace(5, '>START - OKC_CRJ_PVT.INSERT_ROW -');
INSERT INTO OKC_K_REL_OBJS(
id,
cle_id,
chr_id,
rty_code,
object1_id1,
object1_id2,
JTOT_OBJECT1_CODE,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
VALUES (
l_crj_rec.id,
l_crj_rec.cle_id,
l_crj_rec.chr_id,
l_crj_rec.rty_code,
l_crj_rec.object1_id1,
l_crj_rec.object1_id2,
l_crj_rec.JTOT_OBJECT1_CODE,
l_crj_rec.object_version_number,
l_crj_rec.created_by,
l_crj_rec.creation_date,
l_crj_rec.last_updated_by,
l_crj_rec.last_update_date,
l_crj_rec.last_update_login,
l_crj_rec.attribute_category,
l_crj_rec.attribute1,
l_crj_rec.attribute2,
l_crj_rec.attribute3,
l_crj_rec.attribute4,
l_crj_rec.attribute5,
l_crj_rec.attribute6,
l_crj_rec.attribute7,
l_crj_rec.attribute8,
l_crj_rec.attribute9,
l_crj_rec.attribute10,
l_crj_rec.attribute11,
l_crj_rec.attribute12,
l_crj_rec.attribute13,
l_crj_rec.attribute14,
l_crj_rec.attribute15);
okc_util.print_trace(5, 'Insertion into OKC_K_REL_OBJS:');
okc_util.print_trace(5, '
END insert_row;
PROCEDURE insert_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_crjv_rec IN crjv_rec_type,
x_crjv_rec OUT NOCOPY crjv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
l_crjv_rec.LAST_UPDATE_DATE := l_crjv_rec.CREATION_DATE;
l_crjv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_crjv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
okc_util.print_trace(4, '>START - OKC_CRJ_PVT.INSERT_ROW -');
okc_util.print_trace(5, 'before insert');
insert_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_crj_rec,
lx_crj_rec
);
okc_util.print_trace(4, '
END insert_row;
PROCEDURE insert_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_crjv_tbl IN crjv_tbl_type,
x_crjv_tbl OUT NOCOPY crjv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
insert_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_crjv_rec => p_crjv_tbl(i),
x_crjv_rec => x_crjv_tbl(i));
END insert_row;
SELECT OBJECT_VERSION_NUMBER
FROM OKC_K_REL_OBJS
WHERE ID = p_crj_rec.id
AND OBJECT_VERSION_NUMBER = p_crj_rec.object_version_number
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
SELECT OBJECT_VERSION_NUMBER
FROM OKC_K_REL_OBJS
WHERE ID = p_crj_rec.id;
OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
PROCEDURE update_row(
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_crj_rec IN crj_rec_type,
x_crj_rec OUT NOCOPY crj_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'OBJS_update_row';
IF (x_crj_rec.last_updated_by = OKC_API.G_MISS_NUM)
THEN
x_crj_rec.last_updated_by := l_crj_rec.last_updated_by;
IF (x_crj_rec.last_update_date = OKC_API.G_MISS_DATE)
THEN
x_crj_rec.last_update_date := l_crj_rec.last_update_date;
IF (x_crj_rec.last_update_login = OKC_API.G_MISS_NUM)
THEN
x_crj_rec.last_update_login := l_crj_rec.last_update_login;
UPDATE OKC_K_REL_OBJS
SET CLE_ID = l_def_crj_rec.cle_id,
CHR_ID = l_def_crj_rec.chr_id,
RTY_CODE = l_def_crj_rec.rty_code,
OBJECT1_ID1 = l_def_crj_rec.object1_id1,
OBJECT1_ID2 = l_def_crj_rec.object1_id2,
JTOT_OBJECT1_CODE = l_def_crj_rec.JTOT_OBJECT1_CODE,
OBJECT_VERSION_NUMBER = l_def_crj_rec.object_version_number,
CREATED_BY = l_def_crj_rec.created_by,
CREATION_DATE = l_def_crj_rec.creation_date,
LAST_UPDATED_BY = l_def_crj_rec.last_updated_by,
LAST_UPDATE_DATE = l_def_crj_rec.last_update_date,
LAST_UPDATE_LOGIN = l_def_crj_rec.last_update_login,
ATTRIBUTE_CATEGORY = l_def_crj_rec.attribute_category,
ATTRIBUTE1 = l_def_crj_rec.attribute1,
ATTRIBUTE2 = l_def_crj_rec.attribute2,
ATTRIBUTE3 = l_def_crj_rec.attribute3,
ATTRIBUTE4 = l_def_crj_rec.attribute4,
ATTRIBUTE5 = l_def_crj_rec.attribute5,
ATTRIBUTE6 = l_def_crj_rec.attribute6,
ATTRIBUTE7 = l_def_crj_rec.attribute7,
ATTRIBUTE8 = l_def_crj_rec.attribute8,
ATTRIBUTE9 = l_def_crj_rec.attribute9,
ATTRIBUTE10 = l_def_crj_rec.attribute10,
ATTRIBUTE11 = l_def_crj_rec.attribute11,
ATTRIBUTE12 = l_def_crj_rec.attribute12,
ATTRIBUTE13 = l_def_crj_rec.attribute13,
ATTRIBUTE14 = l_def_crj_rec.attribute14,
ATTRIBUTE15 = l_def_crj_rec.attribute15
WHERE ID = l_def_crj_rec.id;
END update_row;
PROCEDURE update_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_crjv_rec IN crjv_rec_type,
x_crjv_rec OUT NOCOPY crjv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
l_crjv_rec.LAST_UPDATE_DATE := SYSDATE;
l_crjv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_crjv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
IF (x_crjv_rec.last_updated_by = OKC_API.G_MISS_NUM)
THEN
x_crjv_rec.last_updated_by := l_crjv_rec.last_updated_by;
IF (x_crjv_rec.last_update_date = OKC_API.G_MISS_DATE)
THEN
x_crjv_rec.last_update_date := l_crjv_rec.last_update_date;
IF (x_crjv_rec.last_update_login = OKC_API.G_MISS_NUM)
THEN
x_crjv_rec.last_update_login := l_crjv_rec.last_update_login;
update_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_crj_rec,
lx_crj_rec
);
END update_row;
PROCEDURE update_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_crjv_tbl IN crjv_tbl_type,
x_crjv_tbl OUT NOCOPY crjv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
update_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_crjv_rec => p_crjv_tbl(i),
x_crjv_rec => x_crjv_tbl(i));
END update_row;
PROCEDURE delete_row(
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_crj_rec IN crj_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'OBJS_delete_row';
DELETE FROM OKC_K_REL_OBJS
WHERE ID = l_crj_rec.id;
END delete_row;
PROCEDURE delete_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_crjv_rec IN crjv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
delete_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_crj_rec
);
END delete_row;
PROCEDURE delete_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_crjv_tbl IN crjv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
delete_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_crjv_rec => p_crjv_tbl(i));
END delete_row;
SELECT
id
FROM
okc_k_rel_objs o
WHERE (
o.rty_code = 'QUOTERENEWSCONTRACT'
and o.jtot_object1_code = 'OKX_QUOTEHEAD'
and o.object1_id1 = p_crjv_rec.object1_id1
and (
o.object1_id2 = p_crjv_rec.object1_id2
or (
p_crjv_rec.object1_id2 is null
and o.object1_id2 is null
)
)
)
;
quotes are versioned with the same number and the rel probably not updated so use code below not above
*/
CURSOR renew_rel_for_quote
(
p_crjv_rec crjv_rec_type
) IS
SELECT
id
FROM
okc_k_rel_objs o
WHERE (-- object with right type and relationship codes
o.rty_code = 'QUOTERENEWSCONTRACT'
and o.jtot_object1_code = 'OKX_QUOTEHEAD'
and exists (-- another quote (or same) with same number as object
select
1
from
okx_quote_headers_v q1
,okx_quote_headers_v q2
where (
(-- q1 is passed in
q1.id1 = p_crjv_rec.object1_id1
and (
q1.id2 = p_crjv_rec.object1_id2
or (
p_crjv_rec.object1_id2 is null
and q1.id2 = '#'
)
)
)
and (-- q2 has same num as q1
q1.quote_number = q2.quote_number
)
and (-- q2 is the obj rel we're looking for
q2.id1 = o.object1_id1
and (
q2.id2 = o.object1_id2
or (
o.object1_id2 is null
and q2.id2 = '#'
)
)
)
)
)
)
;
SELECT
id
FROM
okc_k_rel_objs o
WHERE (
o.rty_code = 'ORDERRENEWSCONTRACT'
and o.jtot_object1_code = 'OKX_ORDERHEAD'
and o.object1_id1 = p_crjv_rec.object1_id1
and (
o.object1_id2 = p_crjv_rec.object1_id2
or (
p_crjv_rec.object1_id2 is null
and o.object1_id2 is null
)
)
)
;
SELECT
id1
,id2
FROM
okx_quote_headers_v q
WHERE
(
q.order_id = p_crjv_rec.object1_id1
/* q.order_id1 = p_crjv_rec.object1_id1
and (
q.order_id2 = p_crjv_rec.object1_id2
or (
p_crjv_rec.object1_id2 is null
and q.order_id2 = '#'
)
)
*/ )
;
SELECT
id
FROM
okc_k_rel_objs o
WHERE (-- object with right type and relationship codes
o.rty_code = 'QUOTESUBJECTCONTRACT'
and o.jtot_object1_code = 'OKX_QUOTEHEAD'
and exists (-- another quote (or same) with same number as object
select
1
from
okx_quote_headers_v q1
,okx_quote_headers_v q2
where (
(-- q1 is passed in
q1.id1 = p_crjv_rec.object1_id1
and (
q1.id2 = p_crjv_rec.object1_id2
or (
p_crjv_rec.object1_id2 is null
and q1.id2 = '#'
)
)
)
and (-- q2 has same num as q1
q1.quote_number = q2.quote_number
)
and (-- q2 is the obj rel we're looking for
q2.id1 = o.object1_id1
and (
q2.id2 = o.object1_id2
or (
o.object1_id2 is null
and q2.id2 = '#'
)
)
)
)
)
)
;
SELECT
id
FROM
okc_k_rel_objs o
WHERE (
o.rty_code = 'ORDERSUBJECTCONTRACT'
and o.jtot_object1_code = 'OKX_ORDERHEAD'
and o.object1_id1 = p_crjv_rec.object1_id1
and (
o.object1_id2 = p_crjv_rec.object1_id2
or (
p_crjv_rec.object1_id2 is null
and o.object1_id2 is null
)
)
)
;
SELECT
id1
,id2
FROM
okx_quote_headers_v q
WHERE
(
q.order_id = p_crjv_rec.object1_id1
/* q.order_id1 = p_crjv_rec.object1_id1
and (
q.order_id2 = p_crjv_rec.object1_id2
or (
p_crjv_rec.object1_id2 is null
and q.order_id2 = '#'
)
)
*/ )
;
SELECT
id
,chr_id
FROM
okc_k_rel_objs o
WHERE (-- object with right type and relationship codes
o.rty_code = 'QUOTERENEWSCONTRACT'
and o.jtot_object1_code = 'OKX_QUOTEHEAD'
and exists (-- another quote (or same) with same number as object
select
1
from
okx_quote_headers_v q1
,okx_quote_headers_v q2
where (
(-- q1 is passed in
q1.id1 = p_crjv_rec.object1_id1
and (
q1.id2 = p_crjv_rec.object1_id2
or (
p_crjv_rec.object1_id2 is null
and q1.id2 = '#'
)
)
)
and (-- q2 has same num as q1
q1.quote_number = q2.quote_number
)
and (-- q2 is the obj rel we're looking for
q2.id1 = o.object1_id1
and (
q2.id2 = o.object1_id2
or (
o.object1_id2 is null
and q2.id2 = '#'
)
)
)
)
)
)
;
SELECT
object1_id1
,object1_id2
FROM
okc_k_rel_objs o
WHERE
(
o.chr_id = r_renew_rel_for_quote.chr_id
and o.rty_code = 'CONTRACTSERVICESORDER'
)
;
SELECT
FROM_TABLE
,WHERE_CLAUSE
FROM
JTF_OBJECTS_B
WHERE
OBJECT_CODE = p_object_code
;
l_sql_stmt := 'SELECT '
|| ' 1 '
|| ' FROM '
|| l_from_table
|| ' WHERE '
|| ' ( '
|| 'ID1 = :id1'
|| ' AND '
-- || ' ( '
|| ' ID2 = :id2'
/* || ' or '
|| ' ( '
|| ' ID2 = ''#'' '
|| ' AND '
|| ' :id2 is null '
|| ' )'
|| ' )'
*/ || ' )';
PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,p_crjv_tbl crjv_tbl_type) IS
l_tabsize NUMBER := p_crjv_tbl.COUNT;
in_last_updated_by OKC_DATATYPES.NumberTabTyp;
in_last_update_date OKC_DATATYPES.DateTabTyp;
in_last_update_login OKC_DATATYPES.NumberTabTyp;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_update_date DATE := SYSDATE;
l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
in_last_updated_by (j) := l_last_updated_by;
in_last_update_date (j) := l_last_update_date;
in_last_update_login (j) := l_last_update_login;
INSERT
INTO OKC_K_REL_OBJS
(
id,
cle_id,
chr_id,
rty_code,
object1_id1,
object1_id2,
jtot_object1_code,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
VALUES (
in_id(i),
in_cle_id(i),
in_chr_id(i),
in_rty_code(i),
in_object1_id1(i),
in_object1_id2(i),
in_jtot_object1_code(i),
in_object_version_number(i),
in_created_by(i),
in_creation_date(i),
in_last_updated_by(i),
in_last_update_date(i),
in_last_update_login(i),
in_attribute_category(i),
in_attribute1(i),
in_attribute2(i),
in_attribute3(i),
in_attribute4(i),
in_attribute5(i),
in_attribute6(i),
in_attribute7(i),
in_attribute8(i),
in_attribute9(i),
in_attribute10(i),
in_attribute11(i),
in_attribute12(i),
in_attribute13(i),
in_attribute14(i),
in_attribute15(i)
);
END INSERT_ROW_UPG;