[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select code, meaning
from okc_statuses_v
where ste_code = p_status_type
and default_yn = 'Y';
FUNCTION Update_Minor_Version(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
l_api_version NUMBER := 1;
OKC_CVM_PVT.update_row(
l_api_version,
l_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_cvmv_rec,
x_out_rec);
PROCEDURE update_contract_header(
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_chrv_rec IN OKC_CHR_PVT.chrv_rec_type,
x_chrv_rec OUT NOCOPY OKC_CHR_PVT.chrv_rec_type) IS
BEGIN
x_return_status := OKC_API.G_RET_STS_SUCCESS;
UPDATE OKC_K_HEADERS_B
SET STS_CODE = p_chrv_rec.sts_code,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),PROGRAM_ID),
REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),REQUEST_ID),
PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,SYSDATE),
PROGRAM_APPLICATION_ID = NVL(decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),PROGRAM_APPLICATION_ID),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE ID = p_chrv_rec.id;
x_return_status := Update_Minor_Version(p_chrv_rec.id);
END update_contract_header;
PROCEDURE update_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_update_minor_version IN VARCHAR2 ,
p_contract_number IN VARCHAR2,
p_contract_number_modifier IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_clev_rec IN OKC_CLE_PVT.clev_rec_type,
x_clev_rec OUT NOCOPY OKC_CLE_PVT.clev_rec_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Contract_Line';
UPDATE OKC_K_LINES_B
SET STS_CODE = p_clev_rec.sts_code,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),PROGRAM_ID),
REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),REQUEST_ID),
PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,SYSDATE),
PROGRAM_APPLICATION_ID = NVL(decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),PROGRAM_APPLICATION_ID),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE ID = p_clev_rec.id;
If p_update_minor_version ='Y' Then
x_return_status := Update_Minor_Version(p_clev_rec.dnz_chr_id);
END update_contract_line;
p_update_minor_version IN VARCHAR2 DEFAULT 'Y',
x_return_status OUT NOCOPY VARCHAR2) IS
C number := 0;
SELECT chr.contract_number,
chr.contract_number_modifier,
line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
fnd.meaning TERMINATION_REASON,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status,
fnd_lookups fnd,
okc_k_headers_b chr,
okc_subclasses_b scs
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = chr.id
AND chr.scs_code = scs.code
AND scs.cls_code <> 'OKL'
AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
AND ((p_from_k is NULL) or (chr.CONTRACT_NUMBER >= p_from_k ))
AND ((p_to_k is NULL) or (chr.CONTRACT_NUMBER <= p_to_k ))
AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
AND status.ste_code IN ('ACTIVE','HOLD','SIGNED')
AND line.date_terminated <= trunc(sysdate) + 0.99999
AND LINE.date_terminated >= trunc(l_last_rundate)
AND line.trn_code = fnd.lookup_code
AND fnd.LOOKUP_TYPE= 'OKC_TERMINATION_REASON';
SELECT
CHR.CONTRACT_NUMBER, CHR.CONTRACT_NUMBER_MODIFIER, LINE.ID,
LINE.OBJECT_VERSION_NUMBER, LINE.STS_CODE, LINE.DATE_TERMINATED, LINE.START_DATE,
LINE.END_DATE, LINE.LINE_NUMBER, LINE.PRICE_NEGOTIATED, LINE.DNZ_CHR_ID,
FND.MEANING TERMINATION_REASON, STSB.CODE, STSB.STE_CODE, STST.MEANING
FROM OKC_K_LINES_B LINE,
OKC_STATUSES_TL stst,
OKC_STATUSES_B stsb,
FND_LOOKUPS FND,
OKC_K_HEADERS_B CHR,
OKC_SUBCLASSES_B SCS
WHERE LINE.STS_CODE = STST.CODE
AND LINE.DNZ_CHR_ID = CHR.ID
AND stst.code = stsb.code
AND STST.LANGUAGE = USERENV('LANG')
AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
AND CHR.CONTRACT_NUMBER >= p_from_k
AND CHR.CONTRACT_NUMBER <= p_to_k
AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
--BUG 4915692 Gchadha
-- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
-- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
AND stsb.ste_code IN ('ACTIVE','SIGNED')
--END BUG 4915692 Gchadha
AND LINE.DATE_TERMINATED <= TRUNC(SYSDATE) + 0.99999
AND LINE.DATE_TERMINATED >= TRUNC(l_last_rundate)
AND LINE.TRN_CODE = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code); -- new
SELECT
CHR.CONTRACT_NUMBER, CHR.CONTRACT_NUMBER_MODIFIER, LINE.ID,
LINE.OBJECT_VERSION_NUMBER, LINE.STS_CODE, LINE.DATE_TERMINATED, LINE.START_DATE,
LINE.END_DATE, LINE.LINE_NUMBER, LINE.PRICE_NEGOTIATED, LINE.DNZ_CHR_ID,
FND.MEANING TERMINATION_REASON, STSB.CODE, STSB.STE_CODE, STST.MEANING
FROM OKC_K_LINES_B LINE,
OKC_STATUSES_TL stst,
OKC_STATUSES_B stsb,
FND_LOOKUPS FND,
OKC_K_HEADERS_B CHR,
OKC_SUBCLASSES_B SCS
WHERE LINE.STS_CODE = STST.CODE
AND LINE.DNZ_CHR_ID = CHR.ID
AND stst.code = stsb.code
AND STST.LANGUAGE = USERENV('LANG')
AND chr.scs_code = p_scs_code
--BUG 4915692 Gchadha
-- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
-- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
AND stsb.ste_code IN ('ACTIVE','SIGNED')
--BUG 4915692 Gchadha
AND LINE.DATE_TERMINATED <= TRUNC(SYSDATE) + 0.99999
AND LINE.DATE_TERMINATED >= TRUNC(l_last_rundate)
AND LINE.TRN_CODE = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code); -- new
SELECT
CHR.CONTRACT_NUMBER, CHR.CONTRACT_NUMBER_MODIFIER, LINE.ID,
LINE.OBJECT_VERSION_NUMBER, LINE.STS_CODE, LINE.DATE_TERMINATED, LINE.START_DATE,
LINE.END_DATE, LINE.LINE_NUMBER, LINE.PRICE_NEGOTIATED, LINE.DNZ_CHR_ID,
FND.MEANING TERMINATION_REASON, STSB.CODE, STSB.STE_CODE, STST.MEANING
FROM OKC_K_LINES_B LINE,
OKC_STATUSES_TL stst,
OKC_STATUSES_B stsb,
FND_LOOKUPS FND,
OKC_K_HEADERS_B CHR,
OKC_SUBCLASSES_B SCS
WHERE LINE.STS_CODE = STST.CODE
AND LINE.DNZ_CHR_ID = CHR.ID
AND stst.code = stsb.code
AND STST.LANGUAGE = USERENV('LANG')
-- BUG 4915692 Gchadha
-- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
-- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
AND stsb.ste_code IN ('ACTIVE','SIGNED')
-- BUG 4915692 Gchadha
AND LINE.DATE_TERMINATED <= TRUNC(SYSDATE) + 0.99999
AND LINE.DATE_TERMINATED >= TRUNC(l_last_rundate)
AND LINE.TRN_CODE = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code); -- new
SELECT
chr.contract_number,
chr.contract_number_modifier,
line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
stsb.CODE,
stsb.STE_CODE,
stst.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_TL stst,
OKC_STATUSES_B stsb,
OKC_K_HEADERS_B chr
-- okc_subclasses_b scs
WHERE line.STS_CODE = stst.CODE
AND line.dnz_chr_id = chr.id
AND stst.code = stsb.code
AND STST.LANGUAGE = USERENV('LANG')
AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
AND CHR.CONTRACT_NUMBER >= p_from_k
AND CHR.CONTRACT_NUMBER <= p_to_k
AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
-- BUG 4915692 Gchadha
-- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
-- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
AND STSB.STE_CODE IN ('ACTIVE','SIGNED')
--BUG 4915692 Gchadha
--
-- Bug 2672565 - Removed time component and changed from <= to <
--
--AND line.end_date <= trunc(sysdate) + 0.99999
AND line.end_date < trunc(sysdate)
AND line.end_date >= trunc(l_last_rundate)
AND (line.date_terminated IS NULL
OR line.date_terminated >= trunc(sysdate))
AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code);
SELECT
chr.contract_number,
chr.contract_number_modifier,
line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
stsb.CODE,
stsb.STE_CODE,
stst.meaning
FROM OKC_K_LINES_B line,
-- OKC_STATUSES_V status,
OKC_STATUSES_TL stst,
OKC_STATUSES_B stsb,
OKC_K_HEADERS_B chr
-- okc_subclasses_b scs
WHERE line.STS_CODE = stst.CODE
AND line.dnz_chr_id = chr.id
AND stst.code = stsb.code
AND STST.LANGUAGE = USERENV('LANG')
AND chr.scs_code = p_scs_code
-- Bug 4915692 --
-- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
-- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
AND STSB.STE_CODE IN ('ACTIVE','SIGNED')
-- Bug 4915692 --
--
-- Bug 2672565 - Removed time component and changed from <= to <
--
--AND line.end_date <= trunc(sysdate) + 0.99999
AND line.end_date < trunc(sysdate)
AND line.end_date >= trunc(l_last_rundate)
AND (line.date_terminated IS NULL
OR line.date_terminated >= trunc(sysdate))
AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code);
SELECT
chr.contract_number,
chr.contract_number_modifier,
line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
stsb.CODE,
stsb.STE_CODE,
stst.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_TL stst,
OKC_STATUSES_B stsb,
OKC_K_HEADERS_B chr
-- okc_subclasses_b scs
WHERE line.STS_CODE = stst.CODE
AND line.dnz_chr_id = chr.id
AND stst.code = stsb.code
AND STST.LANGUAGE = USERENV('LANG')
-- Bug 4915692 --
--AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
AND STSB.STE_CODE IN ('ACTIVE','SIGNED')
-- Bug 4915692 --
--
-- Bug 2672565 - Removed time component and changed from <= to <
--
--AND line.end_date <= trunc(sysdate) + 0.99999
AND line.end_date < trunc(sysdate)
AND line.end_date >= trunc(l_last_rundate)
AND (line.date_terminated IS NULL
OR line.date_terminated >= trunc(sysdate))
AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code);
SELECT chr.contract_number,
chr.contract_number_modifier,
line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status,
OKC_STATUSES_B status1,
okc_k_headers_b chr,
okc_subclasses_b scs
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = chr.id
AND chr.scs_code = scs.code
AND scs.cls_code <> 'OKL'
AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
AND ((p_from_k is NULL) or (chr.CONTRACT_NUMBER >= p_from_k ))
AND ((p_to_k is NULL) or (chr.CONTRACT_NUMBER <= p_to_k ))
AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
AND status.ste_code = 'SIGNED'
AND chr.STS_CODE = status1.CODE
AND status1.ste_code = 'ACTIVE'
AND line.start_date >= trunc(l_last_rundate) AND
line.start_date <= trunc(sysdate) + 0.99999
AND (line.date_terminated IS NULL
or line.date_terminated >= trunc(sysdate));
SELECT /*+leading(CHR) USE_NL(CHR SCS LINE FND.LV STATUS.STSB STATUS.STST))*/
chr.contract_number,
chr.contract_number_modifier,
line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status,
OKC_STATUSES_B status1,
okc_k_headers_b chr,
okc_subclasses_b scs
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = chr.id
AND chr.scs_code = scs.code
AND scs.cls_code <> 'OKL'
AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
AND chr.CONTRACT_NUMBER >= p_from_k
AND chr.CONTRACT_NUMBER <= p_to_k
AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
AND status.ste_code = 'SIGNED'
AND chr.STS_CODE = status1.CODE
AND status1.ste_code = 'ACTIVE'
AND line.start_date >= trunc(l_last_rundate)
AND line.start_date <= trunc(sysdate) + 0.99999
AND (line.date_terminated IS NULL
or line.date_terminated >= trunc(sysdate));
SELECT /*+leading(SCS) USE_NL(SCS LINE CHR FND.LV STATUS.STSB STATUS.STST))*/
chr.contract_number,
chr.contract_number_modifier,
line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status,
OKC_STATUSES_B status1,
okc_k_headers_b chr,
okc_subclasses_b scs
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = chr.id
AND chr.scs_code = scs.code
AND scs.cls_code <> 'OKL'
AND CHR.SCS_CODE = p_scs_code
AND status.ste_code = 'SIGNED'
AND chr.STS_CODE = status1.CODE
AND status1.ste_code = 'ACTIVE'
AND line.start_date >= trunc(l_last_rundate)
AND line.start_date <= trunc(sysdate) + 0.99999
AND (line.date_terminated IS NULL
or line.date_terminated >= trunc(sysdate));
SELECT /*+leading(LINE) USE_NL(LINE CHR SCS FND.LV STATUS.STSB STATUS.STST))*/
chr.contract_number,
chr.contract_number_modifier,
line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status,
OKC_STATUSES_B status1,
okc_k_headers_b chr,
okc_subclasses_b scs
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = chr.id
AND chr.scs_code = scs.code
AND scs.cls_code <> 'OKL'
AND status.ste_code = 'SIGNED'
AND chr.STS_CODE = status1.CODE
AND status1.ste_code = 'ACTIVE'
AND line.start_date >= trunc(l_last_rundate)
AND line.start_date <= trunc(sysdate) + 0.99999
AND (line.date_terminated IS NULL
or line.date_terminated >= trunc(sysdate));
SELECT /*+leading(CHR) USE_NL(CHR SCS LINE FND.LV STATUS.STSB STATUS.STST))*/
chr.contract_number,
chr.contract_number_modifier,
line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status,
OKC_STATUSES_B status1,
okc_k_headers_b chr,
okc_subclasses_b scs
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = chr.id
AND chr.scs_code = scs.code
AND scs.cls_code <> 'OKL'
AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
AND chr.CONTRACT_NUMBER >= p_from_k
AND chr.CONTRACT_NUMBER <= p_to_k
AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
AND status.ste_code = 'ACTIVE'
AND chr.STS_CODE = status1.CODE
AND status1.ste_code = 'SIGNED'
AND line.start_date >= trunc(l_last_rundate)
AND line.start_date >= trunc(sysdate) + 0.99999
AND (line.date_terminated IS NULL
or line.date_terminated >= trunc(sysdate));
SELECT /*+leading(SCS) USE_NL(SCS LINE CHR FND.LV STATUS.STSB STATUS.STST))*/
chr.contract_number,
chr.contract_number_modifier,
line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status,
OKC_STATUSES_B status1,
okc_k_headers_b chr,
okc_subclasses_b scs
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = chr.id
AND chr.scs_code = scs.code
AND scs.cls_code <> 'OKL'
AND CHR.SCS_CODE = p_scs_code
AND status.ste_code = 'ACTIVE'
AND chr.STS_CODE = status1.CODE
AND status1.ste_code = 'SIGNED'
AND line.start_date >= trunc(l_last_rundate)
AND line.start_date >= trunc(sysdate) + 0.99999
AND (line.date_terminated IS NULL
or line.date_terminated >= trunc(sysdate));
SELECT /*+leading(LINE) USE_NL(LINE CHR SCS FND.LV STATUS.STSB STATUS.STST))*/
chr.contract_number,
chr.contract_number_modifier,
line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status,
OKC_STATUSES_B status1,
okc_k_headers_b chr,
okc_subclasses_b scs
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = chr.id
AND chr.scs_code = scs.code
AND scs.cls_code <> 'OKL'
AND status.ste_code = 'ACTIVE'
AND chr.STS_CODE = status1.CODE
AND status1.ste_code = 'SIGNED'
AND line.start_date >= trunc(l_last_rundate)
AND line.start_date >= trunc(sysdate) + 0.99999
AND (line.date_terminated IS NULL
or line.date_terminated >= trunc(sysdate));
SELECT line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
fnd.meaning TERMINATION_REASON,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status,
fnd_lookups fnd
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = p_kid
--BUG 4915692 --
--AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
--Bug 4915692 --
AND line.date_terminated <= trunc(sysdate) + 0.99999
AND LINE.date_terminated >= trunc(l_last_rundate)
AND line.trn_code = fnd.lookup_code
AND fnd.LOOKUP_TYPE= 'OKC_TERMINATION_REASON';
SELECT line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = p_kid
--BUG 4915692 --
--AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
-- Bug 4915692 --
--
-- Bug 2672565 - Removed time component and changed <= to <
--AND line.end_date <= trunc(sysdate) + 0.99999
--
AND line.end_date < trunc(sysdate)
AND line.end_date >= trunc(l_last_rundate)
AND (line.date_terminated IS NULL
OR line.date_terminated >= trunc(sysdate));
SELECT line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = p_kid
AND status.ste_code = 'SIGNED'
AND line.start_date >= trunc(l_last_rundate) AND
line.start_date <= trunc(sysdate) + 0.99999
AND (line.date_terminated IS NULL
or line.date_terminated >= trunc(sysdate));
SELECT line.ID ,
line.OBJECT_VERSION_NUMBER,
line.STS_CODE,
line.DATE_TERMINATED ,
line.START_DATE ,
line.END_DATE,
line.LINE_NUMBER,
line.PRICE_NEGOTIATED,
line.dnz_chr_id,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_LINES_B line,
OKC_STATUSES_V status
WHERE line.STS_CODE = status.CODE
AND line.dnz_chr_id = p_kid
AND status.ste_code = 'ACTIVE'
AND line.start_date >= trunc(l_last_rundate) AND
line.start_date >= trunc(sysdate) + 0.99999
AND (line.date_terminated IS NULL
or line.date_terminated >= trunc(sysdate));
update_contract_line (
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
p_contract_number => p_term_line_rec.contract_number,
p_contract_number_modifier => p_term_line_rec.contract_number_modifier,
p_update_minor_version => p_update_minor_version,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_cle_rec,
x_clev_rec => x_cle_rec);
update_contract_line(
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
p_update_minor_version => p_update_minor_version,
p_contract_number => p_exp_line_rec.contract_number,
p_contract_number_modifier => p_exp_line_rec.contract_number_modifier,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_cle_rec,
x_clev_rec => x_cle_rec);
update_contract_line (
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
p_update_minor_version => p_update_minor_version,
p_contract_number => p_active_line_rec.contract_number,
p_contract_number_modifier => p_active_line_rec.contract_number_modifier,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_cle_rec,
x_clev_rec => x_cle_rec);
update_contract_line (
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
p_update_minor_version => p_update_minor_version,
p_contract_number => p_signed_line_rec.contract_number,
p_contract_number_modifier => p_signed_line_rec.contract_number_modifier,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_cle_rec,
x_clev_rec => x_cle_rec);
Procedure delete_table_type IS
BEGIN
l_Contract_number_tbl.delete;
l_Contract_number_modifier_tbl.delete;
l_Id_tbl.delete;
l_Object_version_number_tbl.delete;
l_sts_code_tbl.delete;
l_date_terminated_tbl.delete;
l_start_date_tbl.delete;
l_end_date_tbl.delete;
l_line_number_tbl.delete;
l_price_negotiated_tbl.delete;
l_dnz_chr_id_tbl.delete;
l_termination_reason_tbl.delete;
l_code_tbl.delete;
l_ste_code_tbl.delete;
l_meaning_tbl.delete;
END delete_table_type;
delete_table_type;
delete_table_type;
delete_table_type;
delete_table_type;
SELECT
hdr.ID,
hdr.OBJECT_VERSION_NUMBER,
hdr.STS_CODE,
hdr.CONTRACT_NUMBER,
hdr.CONTRACT_NUMBER_MODIFIER,
hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
' - '||hdr.contract_number_modifier) K_N_W_M,
hdr.DATE_TERMINATED,
hdr.TRN_CODE,
hdr.START_DATE,
hdr.END_DATE,
hdr.SCS_CODE,
hdr.ESTIMATED_AMOUNT,
scs.CLS_CODE,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_HEADERS_B hdr,
OKC_STATUSES_V status,
OKC_SUBCLASSES_B scs
WHERE hdr.STS_CODE = status.CODE
AND scs.code = hdr.scs_code
and scs.cls_code <> 'OKL'
and ((p_category is NULL) or (scs.CODE = p_category))
and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
and hdr.STS_CODE <> 'QA_HOLD'
and status.ste_code = 'SIGNED'
AND (hdr.date_terminated IS NULL
or hdr.date_terminated >= trunc(sysdate))
AND hdr.start_date <= trunc(sysdate)+0.99999
AND hdr.start_date >= trunc(l_last_rundate);
SELECT
hdr.ID,
hdr.OBJECT_VERSION_NUMBER,
hdr.STS_CODE,
hdr.CONTRACT_NUMBER,
hdr.CONTRACT_NUMBER_MODIFIER,
hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
' - '||hdr.contract_number_modifier) K_N_W_M,
hdr.DATE_TERMINATED,
hdr.TRN_CODE,
hdr.START_DATE,
hdr.END_DATE,
hdr.SCS_CODE,
hdr.ESTIMATED_AMOUNT,
scs.CLS_CODE,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_HEADERS_B hdr,
OKC_STATUSES_V status,
OKC_SUBCLASSES_B scs
WHERE hdr.STS_CODE = status.CODE
AND scs.code = hdr.scs_code
and scs.cls_code <> 'OKL'
and ((p_category is NULL) or (scs.CODE = p_category))
and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
and hdr.STS_CODE <> 'QA_HOLD'
and status.ste_code = 'ACTIVE'
AND (hdr.date_terminated IS NULL
or hdr.date_terminated >= trunc(sysdate))
AND hdr.start_date >= trunc(sysdate)+0.99999
AND hdr.start_date >= trunc(l_last_rundate);
SELECT /*+ leading(hdr) no_merge(status) use_hash(hdr scs status.stsb status.stst) */
hdr.ID,
hdr.OBJECT_VERSION_NUMBER,
hdr.STS_CODE,
hdr.CONTRACT_NUMBER,
hdr.CONTRACT_NUMBER_MODIFIER,
hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
' - '||hdr.contract_number_modifier) K_N_W_M,
hdr.DATE_TERMINATED,
hdr.TRN_CODE,
hdr.START_DATE,
hdr.END_DATE,
hdr.SCS_CODE,
hdr.ESTIMATED_AMOUNT,
scs.CLS_CODE,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_HEADERS_B hdr,
OKC_STATUSES_V status,
OKC_SUBCLASSES_B scs
WHERE hdr.STS_CODE = status.CODE
AND scs.code = hdr.scs_code
and scs.cls_code <> 'OKL'
and ((p_category is NULL) or (scs.CODE = p_category))
and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
and hdr.STS_CODE <> 'QA_HOLD'
-- and status.ste_code in ('ACTIVE','SIGNED','HOLD') -- <> 'EXPIRED'
and status.ste_code in ('ACTIVE','SIGNED') -- Bug 4915692 --
AND hdr.end_date >= trunc(l_last_rundate)
--
-- Bug 2672565 - Removed time component and changed from <= to <
--and hdr.end_date <= trunc(sysdate)+0.99999
--
and hdr.end_date < trunc(sysdate)
AND (hdr.date_terminated IS NULL
or hdr.date_terminated >= trunc(sysdate));
SELECT
hdr.ID,
hdr.OBJECT_VERSION_NUMBER,
hdr.STS_CODE,
hdr.CONTRACT_NUMBER,
hdr.CONTRACT_NUMBER_MODIFIER,
hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
' - '||hdr.contract_number_modifier) K_N_W_M,
hdr.DATE_TERMINATED,
hdr.TRN_CODE,
hdr.START_DATE,
hdr.END_DATE,
hdr.SCS_CODE,
hdr.ESTIMATED_AMOUNT,
fnd.meaning TERMINATION_REASON,
scs.CLS_CODE,
status.CODE,
status.STE_CODE,
status.meaning
FROM OKC_K_HEADERS_B hdr,
OKC_STATUSES_V status,
FND_LOOKUPS fnd,
OKC_SUBCLASSES_B scs
WHERE hdr.STS_CODE = status.CODE
AND scs.code = hdr.scs_code
and scs.cls_code <> 'OKL'
and ((p_category is NULL) or (scs.CODE = p_category))
and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
and hdr.STS_CODE <> 'QA_HOLD'
-- and status.ste_code IN ('ACTIVE','HOLD','SIGNED')
and status.ste_code IN ('ACTIVE','SIGNED') -- Bug 4915692
and hdr.trn_code = fnd.lookup_code
and fnd.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
and hdr.date_terminated >= trunc(l_last_rundate)
and hdr.date_terminated <= trunc(sysdate)+0.99999;
update_contract_header (
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_rec => l_chr_rec,
x_chrv_rec => i_chr_rec);
p_update_minor_version =>'N',
x_return_status => l_return_status);
update_contract_header (
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_rec => l_chr_rec,
x_chrv_rec => i_chr_rec);
p_update_minor_version =>'N',
x_return_status => l_return_status);
update_contract_header (
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_rec => l_chr_rec,
x_chrv_rec => i_chr_rec);
p_update_minor_version =>'N',
x_return_status => l_return_status);
update_contract_header (
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chrv_rec => l_chr_rec,
x_chrv_rec => i_chr_rec);
p_update_minor_version =>'N',
x_return_status => l_return_status);
,p_update_minor_version =>'Y'
,x_return_status => l_return_status );