[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_CHILD_RECORD_EXISTS CONSTANT VARCHAR2(200) := 'OKC_CANNOT_DELETE_MASTER';
SELECT 'x'
FROM FND_CURRENCIES_VL
WHERE currency_code = p_chrv_rec.currency_code
AND SYSDATE BETWEEN nvl(start_date_active, SYSDATE)
AND nvl(end_date_active, SYSDATE);
SELECT 'x'
--npalepu 08-11-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM OKC_K_HEADERS_B */
FROM OKC_K_HEADERS_ALL_B
--end npalepu
WHERE id = p_chrv_rec.chr_id_response;
SELECT 'x'
--npalepu 08-11-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM OKC_K_HEADERS_B */
FROM OKC_K_HEADERS_ALL_B
--end npalepu
WHERE id = p_chrv_rec.chr_id_award;
SELECT 'x'
FROM Okc_Statuses_B
WHERE okc_statuses_B.code = p_code;
SELECT 'x'
FROM OKC_QA_CHECK_LISTS_B
WHERE ID = p_chrv_rec.qcl_id;
SELECT 'x'
FROM Okc_Subclasses_B
WHERE okc_subclasses_b.code = p_code;
PROCEDURE validate_deleted_yn(x_return_status OUT NOCOPY VARCHAR2,
p_chrv_rec IN chrv_rec_type) IS
BEGIN
IF (l_debug = 'Y') THEN
okc_debug.Set_Indentation('OKC_CHR_PVT');
okc_debug.LOG('5000: Entered validate_deleted_yn', 2);
IF (p_chrv_rec.deleted_yn = OKC_API.G_MISS_CHAR OR
p_chrv_rec.deleted_yn IS NULL)
THEN
OKC_API.SET_MESSAGE(p_app_name => g_app_name,
p_msg_name => g_required_value,
p_token1 => g_col_name_token,
p_token1_value => 'deleted_yn');
IF (upper(p_chrv_rec.deleted_yn) NOT IN ('Y', 'N')) THEN
OKC_API.SET_MESSAGE(p_app_name => g_app_name,
p_msg_name => g_invalid_value,
p_token1 => g_col_name_token,
p_token1_value => 'deleted_yn');
okc_debug.LOG('5100: Exiting validate_deleted_yn', 2);
okc_debug.LOG('5200: Exiting validate_deleted_yn:G_EXCEPTION_HALT_VALIDATION Exception', 2);
okc_debug.LOG('5300: Exiting validate_deleted_yn:OTHERS Exception', 2);
END validate_deleted_yn;
SELECT 'x'
FROM FND_CURRENCIES_VL
WHERE currency_code = p_chrv_rec.currency_code_renewed
AND SYSDATE BETWEEN nvl(start_date_active, SYSDATE)
AND nvl(end_date_active, SYSDATE);
select 'x'
from okx_list_headers_v
WHERE id1 = p_chrv_rec.price_list_id
and status = 'A'
and currency_code = p_chrv_rec.currency_code
and sysdate between nvl(start_date_active,sysdate)
and nvl(end_date_active,sysdate);
SELECT 'x'
FROM okx_list_headers_v
WHERE id1 = p_chrv_rec.price_list_id
AND ((status = 'A' AND p_chrv_rec.pricing_date IS NULL) OR
(p_chrv_rec.pricing_date IS NOT NULL
AND p_chrv_rec.pricing_date BETWEEN
nvl(start_date_active, p_chrv_rec.pricing_date)
AND nvl(end_date_active, p_chrv_rec.pricing_date)))
AND currency_code = p_chrv_rec.currency_code;
SELECT 'x'
FROM FND_LOOKUPS
WHERE lookup_code = p_chrv_rec.approval_type
AND (lookup_type = 'OKS_REN_ONLINE_APPROVAL'
OR lookup_type = 'OKS_REN_MANUAL_APPROVAL')
AND SYSDATE BETWEEN nvl(start_date_active, SYSDATE)
AND nvl(end_date_active, SYSDATE)
AND enabled_flag = 'Y';
SELECT okc_k_headers_b_s.NEXTVAL
FROM dual;
/* The following delete and update statements are commented out */
/* as a quick workaround to fix the time-consuming table handler issue */
/* Eventually we'll need to turn them into a separate fix_language procedure */
/*
DELETE FROM OKC_K_HEADERS_TL T
WHERE NOT EXISTS (
SELECT NULL
FROM OKC_K_HEADERS_B B
WHERE B.ID = T.ID
);
UPDATE OKC_K_HEADERS_TL T SET (
SHORT_DESCRIPTION,
COMMENTS,
DESCRIPTION,
COGNOMEN,
NON_RESPONSE_REASON,
NON_RESPONSE_EXPLAIN,
SET_ASIDE_REASON) = (SELECT
B.SHORT_DESCRIPTION,
B.COMMENTS,
B.DESCRIPTION,
B.COGNOMEN,
B.NON_RESPONSE_REASON,
B.NON_RESPONSE_EXPLAIN,
B.SET_ASIDE_REASON
FROM OKC_K_HEADERS_TL B
WHERE B.ID = T.ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.ID,
T.LANGUAGE)
IN (SELECT
SUBT.ID,
SUBT.LANGUAGE
FROM OKC_K_HEADERS_TL SUBB, OKC_K_HEADERS_TL SUBT
WHERE SUBB.ID = SUBT.ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
OR SUBB.COMMENTS <> SUBT.COMMENTS
OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
OR SUBB.COGNOMEN <> SUBT.COGNOMEN
OR SUBB.NON_RESPONSE_REASON <> SUBT.NON_RESPONSE_REASON
OR SUBB.NON_RESPONSE_EXPLAIN <> SUBT.NON_RESPONSE_EXPLAIN
OR SUBB.SET_ASIDE_REASON <> SUBT.SET_ASIDE_REASON
OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
OR (SUBB.SHORT_DESCRIPTION IS NOT NULL AND SUBT.SHORT_DESCRIPTION IS NULL)
OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
OR (SUBB.COGNOMEN IS NULL AND SUBT.COGNOMEN IS NOT NULL)
OR (SUBB.COGNOMEN IS NOT NULL AND SUBT.COGNOMEN IS NULL)
OR (SUBB.NON_RESPONSE_REASON IS NULL AND SUBT.NON_RESPONSE_REASON IS NOT NULL)
OR (SUBB.NON_RESPONSE_REASON IS NOT NULL AND SUBT.NON_RESPONSE_REASON IS NULL)
OR (SUBB.NON_RESPONSE_EXPLAIN IS NULL AND SUBT.NON_RESPONSE_EXPLAIN IS NOT NULL)
OR (SUBB.NON_RESPONSE_EXPLAIN IS NOT NULL AND SUBT.NON_RESPONSE_EXPLAIN IS NULL)
OR (SUBB.SET_ASIDE_REASON IS NULL AND SUBT.SET_ASIDE_REASON IS NOT NULL)
OR (SUBB.SET_ASIDE_REASON IS NOT NULL AND SUBT.SET_ASIDE_REASON IS NULL)
));
/* Modifying Insert as per performance guidelines given in bug 3723874 */
INSERT /*+ append parallel(tt) */ INTO OKC_K_HEADERS_TL tt(
ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
SHORT_DESCRIPTION,
COMMENTS,
DESCRIPTION,
COGNOMEN,
NON_RESPONSE_REASON,
NON_RESPONSE_EXPLAIN,
SET_ASIDE_REASON,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT /*+ parallel(v) parallel(t) use_nl(t) */ v. * FROM
(SELECT /*+ no_merge ordered parallel(b) */
B.ID,
L.LANGUAGE_CODE,
B.SOURCE_LANG,
B.SFWT_FLAG,
B.SHORT_DESCRIPTION,
B.COMMENTS,
B.DESCRIPTION,
B.COGNOMEN,
B.NON_RESPONSE_REASON,
B.NON_RESPONSE_EXPLAIN,
B.SET_ASIDE_REASON,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN
FROM OKC_K_HEADERS_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
) v, OKC_K_HEADERS_TL t
WHERE t.ID( + ) = v.ID
AND t.language( + ) = v.LANGUAGE_CODE
AND t.id IS NULL;
/* Commenting delete and update for bug 3723874 */
/*
DELETE FROM OKC_K_HEADERS_TLH T
WHERE NOT EXISTS (
SELECT NULL
FROM OKC_K_HEADERS_BH B
WHERE B.ID = T.ID
AND B.MAJOR_VERSION = T.MAJOR_VERSION
);
UPDATE OKC_K_HEADERS_TLH T SET (
SHORT_DESCRIPTION,
COMMENTS,
DESCRIPTION,
COGNOMEN,
NON_RESPONSE_REASON,
NON_RESPONSE_EXPLAIN,
SET_ASIDE_REASON) = (SELECT
B.SHORT_DESCRIPTION,
B.COMMENTS,
B.DESCRIPTION,
B.COGNOMEN,
B.NON_RESPONSE_REASON,
B.NON_RESPONSE_EXPLAIN,
B.SET_ASIDE_REASON
FROM OKC_K_HEADERS_TLH B
WHERE B.ID = T.ID
AND B.MAJOR_VERSION = T.MAJOR_VERSION
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.ID,
T.MAJOR_VERSION,
T.LANGUAGE)
IN (SELECT
SUBT.ID,
SUBT.MAJOR_VERSION,
SUBT.LANGUAGE
FROM OKC_K_HEADERS_TLH SUBB, OKC_K_HEADERS_TLH SUBT
WHERE SUBB.ID = SUBT.ID
AND SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
OR SUBB.COMMENTS <> SUBT.COMMENTS
OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
OR SUBB.COGNOMEN <> SUBT.COGNOMEN
OR SUBB.NON_RESPONSE_REASON <> SUBT.NON_RESPONSE_REASON
OR SUBB.NON_RESPONSE_EXPLAIN <> SUBT.NON_RESPONSE_EXPLAIN
OR SUBB.SET_ASIDE_REASON <> SUBT.SET_ASIDE_REASON
OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
OR (SUBB.SHORT_DESCRIPTION IS NOT NULL AND SUBT.SHORT_DESCRIPTION IS NULL)
OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
OR (SUBB.COGNOMEN IS NULL AND SUBT.COGNOMEN IS NOT NULL)
OR (SUBB.COGNOMEN IS NOT NULL AND SUBT.COGNOMEN IS NULL)
OR (SUBB.NON_RESPONSE_REASON IS NULL AND SUBT.NON_RESPONSE_REASON IS NOT NULL)
OR (SUBB.NON_RESPONSE_REASON IS NOT NULL AND SUBT.NON_RESPONSE_REASON IS NULL)
OR (SUBB.NON_RESPONSE_EXPLAIN IS NULL AND SUBT.NON_RESPONSE_EXPLAIN IS NOT NULL)
OR (SUBB.NON_RESPONSE_EXPLAIN IS NOT NULL AND SUBT.NON_RESPONSE_EXPLAIN IS NULL)
OR (SUBB.SET_ASIDE_REASON IS NULL AND SUBT.SET_ASIDE_REASON IS NOT NULL)
OR (SUBB.SET_ASIDE_REASON IS NOT NULL AND SUBT.SET_ASIDE_REASON IS NULL)
));
/* Modifying Insert as per performance guidelines given in bug 3723874 */
INSERT /*+ append parallel(tt) */ INTO OKC_K_HEADERS_TLH tt(
ID,
LANGUAGE,
MAJOR_VERSION,
SOURCE_LANG,
SFWT_FLAG,
SHORT_DESCRIPTION,
COMMENTS,
DESCRIPTION,
COGNOMEN,
NON_RESPONSE_REASON,
NON_RESPONSE_EXPLAIN,
SET_ASIDE_REASON,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT /*+ parallel(v) parallel(t) use_nl(t) */ v. * FROM
(SELECT /*+ no_merge ordered parallel(b) */
B.ID,
L.LANGUAGE_CODE,
B.MAJOR_VERSION,
B.SOURCE_LANG,
B.SFWT_FLAG,
B.SHORT_DESCRIPTION,
B.COMMENTS,
B.DESCRIPTION,
B.COGNOMEN,
B.NON_RESPONSE_REASON,
B.NON_RESPONSE_EXPLAIN,
B.SET_ASIDE_REASON,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN
FROM OKC_K_HEADERS_TLH B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
) v, OKC_K_HEADERS_TLH t
WHERE T.ID( + ) = v.ID
AND T.MAJOR_VERSION( + ) = v.MAJOR_VERSION
AND T.LANGUAGE( + ) = v.LANGUAGE_CODE
AND t.id IS NULL;
SELECT
ID,
CONTRACT_NUMBER,
AUTHORING_ORG_ID,
-- ORG_ID, --mmadhavi added for MOAC
CONTRACT_NUMBER_MODIFIER,
CHR_ID_RESPONSE,
CHR_ID_AWARD,
INV_ORGANIZATION_ID,
STS_CODE,
QCL_ID,
SCS_CODE,
TRN_CODE,
CURRENCY_CODE,
ARCHIVED_YN,
DELETED_YN,
TEMPLATE_YN,
CHR_TYPE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CUST_PO_NUMBER_REQ_YN,
PRE_PAY_REQ_YN,
CUST_PO_NUMBER,
DPAS_RATING,
TEMPLATE_USED,
DATE_APPROVED,
DATETIME_CANCELLED,
AUTO_RENEW_DAYS,
DATE_ISSUED,
DATETIME_RESPONDED,
RFP_TYPE,
KEEP_ON_MAIL_LIST,
SET_ASIDE_PERCENT,
RESPONSE_COPIES_REQ,
DATE_CLOSE_PROJECTED,
DATETIME_PROPOSED,
DATE_SIGNED,
DATE_TERMINATED,
DATE_RENEWED,
START_DATE,
END_DATE,
BUY_OR_SELL,
ISSUE_OR_RECEIVE,
ESTIMATED_AMOUNT,
ESTIMATED_AMOUNT_RENEWED,
CURRENCY_CODE_RENEWED,
LAST_UPDATE_LOGIN,
UPG_ORIG_SYSTEM_REF,
UPG_ORIG_SYSTEM_REF_ID,
APPLICATION_ID,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
PROGRAM_ID,
REQUEST_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
PRICE_LIST_ID,
PRICING_DATE,
SIGN_BY_DATE,
TOTAL_LINE_LIST_PRICE,
USER_ESTIMATED_AMOUNT,
GOVERNING_CONTRACT_YN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
-- new colums to replace rules
CONVERSION_TYPE,
CONVERSION_RATE,
CONVERSION_RATE_DATE,
CONVERSION_EURO_RATE,
CUST_ACCT_ID,
BILL_TO_SITE_USE_ID,
INV_RULE_ID,
RENEWAL_TYPE_CODE,
RENEWAL_NOTIFY_TO,
RENEWAL_END_DATE,
SHIP_TO_SITE_USE_ID,
PAYMENT_TERM_ID,
DOCUMENT_ID,
-- R12 Data Model Changes 4485150 Start
APPROVAL_TYPE,
TERM_CANCEL_SOURCE,
PAYMENT_INSTRUCTION_TYPE,
ORG_ID, --mmadhavi added for MOAC
-- R12 Data Model Changes 4485150 End
CANCELLED_AMOUNT -- LLC
FROM Okc_K_Headers_All_B --mmadhavi changed to _ALL for MOAC
WHERE okc_k_headers_all_b.id = p_id;
l_chr_rec.DELETED_YN,
l_chr_rec.TEMPLATE_YN,
l_chr_rec.CHR_TYPE,
l_chr_rec.OBJECT_VERSION_NUMBER,
l_chr_rec.CREATED_BY,
l_chr_rec.CREATION_DATE,
l_chr_rec.LAST_UPDATED_BY,
l_chr_rec.LAST_UPDATE_DATE,
l_chr_rec.CUST_PO_NUMBER_REQ_YN,
l_chr_rec.PRE_PAY_REQ_YN,
l_chr_rec.CUST_PO_NUMBER,
l_chr_rec.DPAS_RATING,
l_chr_rec.TEMPLATE_USED,
l_chr_rec.DATE_APPROVED,
l_chr_rec.DATETIME_CANCELLED,
l_chr_rec.AUTO_RENEW_DAYS,
l_chr_rec.DATE_ISSUED,
l_chr_rec.DATETIME_RESPONDED,
l_chr_rec.RFP_TYPE,
l_chr_rec.KEEP_ON_MAIL_LIST,
l_chr_rec.SET_ASIDE_PERCENT,
l_chr_rec.RESPONSE_COPIES_REQ,
l_chr_rec.DATE_CLOSE_PROJECTED,
l_chr_rec.DATETIME_PROPOSED,
l_chr_rec.DATE_SIGNED,
l_chr_rec.DATE_TERMINATED,
l_chr_rec.DATE_RENEWED,
l_chr_rec.START_DATE,
l_chr_rec.END_DATE,
l_chr_rec.BUY_OR_SELL,
l_chr_rec.ISSUE_OR_RECEIVE,
l_chr_rec.ESTIMATED_AMOUNT,
l_chr_rec.ESTIMATED_AMOUNT_RENEWED,
l_chr_rec.CURRENCY_CODE_RENEWED,
l_chr_rec.LAST_UPDATE_LOGIN,
l_chr_rec.UPG_ORIG_SYSTEM_REF,
l_chr_rec.UPG_ORIG_SYSTEM_REF_ID,
l_chr_rec.APPLICATION_ID,
l_chr_rec.ORIG_SYSTEM_SOURCE_CODE,
l_chr_rec.ORIG_SYSTEM_ID1,
l_chr_rec.ORIG_SYSTEM_REFERENCE1,
l_chr_rec.PROGRAM_ID,
l_chr_rec.REQUEST_ID,
l_chr_rec.PROGRAM_UPDATE_DATE,
l_chr_rec.PROGRAM_APPLICATION_ID,
l_chr_rec.PRICE_LIST_ID,
l_chr_rec.PRICING_DATE,
l_chr_rec.SIGN_BY_DATE,
l_chr_rec.TOTAL_LINE_LIST_PRICE,
l_chr_rec.USER_ESTIMATED_AMOUNT,
l_chr_rec.GOVERNING_CONTRACT_YN,
l_chr_rec.ATTRIBUTE_CATEGORY,
l_chr_rec.ATTRIBUTE1,
l_chr_rec.ATTRIBUTE2,
l_chr_rec.ATTRIBUTE3,
l_chr_rec.ATTRIBUTE4,
l_chr_rec.ATTRIBUTE5,
l_chr_rec.ATTRIBUTE6,
l_chr_rec.ATTRIBUTE7,
l_chr_rec.ATTRIBUTE8,
l_chr_rec.ATTRIBUTE9,
l_chr_rec.ATTRIBUTE10,
l_chr_rec.ATTRIBUTE11,
l_chr_rec.ATTRIBUTE12,
l_chr_rec.ATTRIBUTE13,
l_chr_rec.ATTRIBUTE14,
l_chr_rec.ATTRIBUTE15,
--new columns to replace rules
l_chr_rec.CONVERSION_TYPE,
l_chr_rec.CONVERSION_RATE,
l_chr_rec.CONVERSION_RATE_DATE,
l_chr_rec.CONVERSION_EURO_RATE,
l_chr_rec.CUST_ACCT_ID,
l_chr_rec.BILL_TO_SITE_USE_ID,
l_chr_rec.INV_RULE_ID,
l_chr_rec.RENEWAL_TYPE_CODE,
l_chr_rec.RENEWAL_NOTIFY_TO,
l_chr_rec.RENEWAL_END_DATE,
l_chr_rec.SHIP_TO_SITE_USE_ID,
l_chr_rec.PAYMENT_TERM_ID,
l_chr_rec.DOCUMENT_ID,
-- R12 Data Model Changes 4485150 Start
l_chr_rec.APPROVAL_TYPE,
l_chr_rec.TERM_CANCEL_SOURCE,
l_chr_rec.PAYMENT_INSTRUCTION_TYPE,
l_chr_rec.ORG_ID, --mmadhavi added for MOAC
l_chr_rec.CANCELLED_AMOUNT -- LLC
-- R12 Data Model Changes 4485150 End
;
SELECT
ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
SHORT_DESCRIPTION,
COMMENTS,
DESCRIPTION,
COGNOMEN,
NON_RESPONSE_REASON,
NON_RESPONSE_EXPLAIN,
SET_ASIDE_REASON,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM Okc_K_Headers_Tl
WHERE okc_k_headers_tl.id = p_id
AND okc_k_headers_tl.language = p_language;
l_okc_k_headers_tl_rec.LAST_UPDATED_BY,
l_okc_k_headers_tl_rec.LAST_UPDATE_DATE,
l_okc_k_headers_tl_rec.LAST_UPDATE_LOGIN;
SELECT
CHRB.ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
CHR_ID_RESPONSE,
CHR_ID_AWARD,
INV_ORGANIZATION_ID,
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,
-- ORG_ID, --mmadhavi added for MOAC
BUY_OR_SELL,
ISSUE_OR_RECEIVE,
ESTIMATED_AMOUNT,
ESTIMATED_AMOUNT_RENEWED,
CURRENCY_CODE_RENEWED,
UPG_ORIG_SYSTEM_REF,
UPG_ORIG_SYSTEM_REF_ID,
APPLICATION_ID,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
PROGRAM_ID,
REQUEST_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
PRICE_LIST_ID,
PRICING_DATE,
SIGN_BY_DATE,
TOTAL_LINE_LIST_PRICE,
USER_ESTIMATED_AMOUNT,
GOVERNING_CONTRACT_YN,
CONVERSION_TYPE,
CONVERSION_RATE,
CONVERSION_RATE_DATE,
CONVERSION_EURO_RATE,
CUST_ACCT_ID,
BILL_TO_SITE_USE_ID,
INV_RULE_ID,
RENEWAL_TYPE_CODE,
RENEWAL_NOTIFY_TO,
RENEWAL_END_DATE,
SHIP_TO_SITE_USE_ID,
PAYMENT_TERM_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CHRB.CREATED_BY,
CHRB.CREATION_DATE,
CHRB.LAST_UPDATED_BY,
CHRB.LAST_UPDATE_DATE,
CHRB.LAST_UPDATE_LOGIN,
DOCUMENT_ID,
-- R12 Data Model Changes 4485150 Start
APPROVAL_TYPE,
TERM_CANCEL_SOURCE,
PAYMENT_INSTRUCTION_TYPE,
ORG_ID, --mmadhavi added for MOAC
CANCELLED_AMOUNT -- LLC
-- R12 Data Model Changes 4485150 End
FROM OKC_K_HEADERS_ALL_B CHRB,
OKC_K_HEADERS_TL CHRT
WHERE CHRB.ID = p_id
AND CHRB.ID = CHRT.ID
AND CHRT.LANGUAGE = USERENV('LANG');
l_chrv_rec.DELETED_YN,
l_chrv_rec.CUST_PO_NUMBER_REQ_YN,
l_chrv_rec.PRE_PAY_REQ_YN,
l_chrv_rec.CUST_PO_NUMBER,
l_chrv_rec.SHORT_DESCRIPTION,
l_chrv_rec.COMMENTS,
l_chrv_rec.DESCRIPTION,
l_chrv_rec.DPAS_RATING,
l_chrv_rec.COGNOMEN,
l_chrv_rec.TEMPLATE_YN,
l_chrv_rec.TEMPLATE_USED,
l_chrv_rec.DATE_APPROVED,
l_chrv_rec.DATETIME_CANCELLED,
l_chrv_rec.AUTO_RENEW_DAYS,
l_chrv_rec.DATE_ISSUED,
l_chrv_rec.DATETIME_RESPONDED,
l_chrv_rec.NON_RESPONSE_REASON,
l_chrv_rec.NON_RESPONSE_EXPLAIN,
l_chrv_rec.RFP_TYPE,
l_chrv_rec.CHR_TYPE,
l_chrv_rec.KEEP_ON_MAIL_LIST,
l_chrv_rec.SET_ASIDE_REASON,
l_chrv_rec.SET_ASIDE_PERCENT,
l_chrv_rec.RESPONSE_COPIES_REQ,
l_chrv_rec.DATE_CLOSE_PROJECTED,
l_chrv_rec.DATETIME_PROPOSED,
l_chrv_rec.DATE_SIGNED,
l_chrv_rec.DATE_TERMINATED,
l_chrv_rec.DATE_RENEWED,
l_chrv_rec.TRN_CODE,
l_chrv_rec.START_DATE,
l_chrv_rec.END_DATE,
l_chrv_rec.AUTHORING_ORG_ID,
-- l_chrv_rec.ORG_ID, --mmadhavi added for MOAC
l_chrv_rec.BUY_OR_SELL,
l_chrv_rec.ISSUE_OR_RECEIVE,
l_chrv_rec.ESTIMATED_AMOUNT,
l_chrv_rec.ESTIMATED_AMOUNT_RENEWED,
l_chrv_rec.CURRENCY_CODE_RENEWED,
l_chrv_rec.UPG_ORIG_SYSTEM_REF,
l_chrv_rec.UPG_ORIG_SYSTEM_REF_ID,
l_chrv_rec.APPLICATION_ID,
l_chrv_rec.ORIG_SYSTEM_SOURCE_CODE,
l_chrv_rec.ORIG_SYSTEM_ID1,
l_chrv_rec.ORIG_SYSTEM_REFERENCE1,
l_chrv_rec.program_id,
l_chrv_rec.request_id,
l_chrv_rec.program_update_date,
l_chrv_rec.program_application_id,
l_chrv_rec.price_list_id,
l_chrv_rec.pricing_date,
l_chrv_rec.sign_by_date,
l_chrv_rec.total_line_list_price,
l_chrv_rec.USER_ESTIMATED_AMOUNT,
l_chrv_rec.GOVERNING_CONTRACT_YN,
--new columns to replace rules
l_chrv_rec.CONVERSION_TYPE,
l_chrv_rec.CONVERSION_RATE,
l_chrv_rec.CONVERSION_RATE_DATE,
l_chrv_rec.CONVERSION_EURO_RATE,
l_chrv_rec.CUST_ACCT_ID,
l_chrv_rec.BILL_TO_SITE_USE_ID,
l_chrv_rec.INV_RULE_ID,
l_chrv_rec.RENEWAL_TYPE_CODE,
l_chrv_rec.RENEWAL_NOTIFY_TO,
l_chrv_rec.RENEWAL_END_DATE,
l_chrv_rec.SHIP_TO_SITE_USE_ID,
l_chrv_rec.PAYMENT_TERM_ID,
--
l_chrv_rec.ATTRIBUTE_CATEGORY,
l_chrv_rec.ATTRIBUTE1,
l_chrv_rec.ATTRIBUTE2,
l_chrv_rec.ATTRIBUTE3,
l_chrv_rec.ATTRIBUTE4,
l_chrv_rec.ATTRIBUTE5,
l_chrv_rec.ATTRIBUTE6,
l_chrv_rec.ATTRIBUTE7,
l_chrv_rec.ATTRIBUTE8,
l_chrv_rec.ATTRIBUTE9,
l_chrv_rec.ATTRIBUTE10,
l_chrv_rec.ATTRIBUTE11,
l_chrv_rec.ATTRIBUTE12,
l_chrv_rec.ATTRIBUTE13,
l_chrv_rec.ATTRIBUTE14,
l_chrv_rec.ATTRIBUTE15,
l_chrv_rec.CREATED_BY,
l_chrv_rec.CREATION_DATE,
l_chrv_rec.LAST_UPDATED_BY,
l_chrv_rec.LAST_UPDATE_DATE,
l_chrv_rec.LAST_UPDATE_LOGIN,
l_chrv_rec.DOCUMENT_ID,
-- R12 Data Model Changes 4485150 End
l_chrv_rec.APPROVAL_TYPE,
l_chrv_rec.TERM_CANCEL_SOURCE,
l_chrv_rec.PAYMENT_INSTRUCTION_TYPE,
l_chrv_rec.ORG_ID, --mmadhavi added for MOAC
l_chrv_rec.CANCELLED_AMOUNT -- LLC
-- R12 Data Model Changes 4485150 End
;
IF (l_chrv_rec.deleted_yn = OKC_API.G_MISS_CHAR) THEN
l_chrv_rec.deleted_yn := NULL;
IF (l_chrv_rec.program_update_date = OKC_API.G_MISS_DATE) THEN
l_chrv_rec.program_update_date := NULL;
IF (l_chrv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
l_chrv_rec.last_updated_by := NULL;
IF (l_chrv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
l_chrv_rec.last_update_date := NULL;
IF (l_chrv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
l_chrv_rec.last_update_login := NULL;
validate_deleted_yn
(x_return_status => l_return_status,
p_chrv_rec => p_chrv_rec);
SELECT ste_code
FROM okc_statuses_b
WHERE code = p_code;
SELECT 'x'
--npalepu 08-11-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM okc_k_headers_b */
FROM okc_k_headers_all_b
--end npalepu
WHERE contract_number = p_chrv_rec.contract_number
AND contract_number_modifier IS NULL
AND id <> nvl(p_chrv_rec.id, - 99999);
SELECT 'x'
--npalepu 08-11-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM okc_k_headers_b */
FROM okc_k_headers_all_b
--end npalepu
WHERE contract_number = p_chrv_rec.contract_number
AND contract_number_modifier = p_chrv_rec.contract_number_modifier
AND id <> nvl(p_chrv_rec.id, - 99999);
SELECT 'x'
--npalepu 08-11-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM okc_k_headers_b */
FROM okc_k_headers_all_b
--end npalepu
WHERE contract_number = p_chrv_rec.contract_number
AND contract_number_modifier IS NULL
AND id <> nvl(p_chrv_rec.id, - 99999);
SELECT 'x'
--npalepu 08-11-2005 modified for bug # 4691662.
--Replaced table okc_k_headers_b with headers_All_b table
/* FROM okc_k_headers_b */
FROM okc_k_headers_all_b
--end npalepu
WHERE contract_number = p_chrv_rec.contract_number
AND contract_number_modifier = p_chrv_rec.contract_number_modifier
AND id <> nvl(p_chrv_rec.id, - 99999);
p_to.deleted_yn := p_from.deleted_yn;
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.program_update_date := p_from.program_update_date;
p_to.deleted_yn := p_from.deleted_yn;
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.program_update_date := p_from.program_update_date;
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_chr_rec IN chr_rec_type,
x_chr_rec OUT NOCOPY chr_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
okc_debug.LOG('13700: Entered insert_row', 2);
INSERT INTO OKC_K_HEADERS_ALL_B( --mmadhavi changed to _ALL for MOAC
id,
contract_number,
authoring_org_id,
org_id, --mmadhavi added for MOAC
contract_number_modifier,
chr_id_response,
chr_id_award,
INV_ORGANIZATION_ID,
sts_code,
qcl_id,
scs_code,
trn_code,
currency_code,
archived_yn,
deleted_yn,
template_yn,
chr_type,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
cust_po_number_req_yn,
pre_pay_req_yn,
cust_po_number,
dpas_rating,
template_used,
date_approved,
datetime_cancelled,
auto_renew_days,
date_issued,
datetime_responded,
rfp_type,
keep_on_mail_list,
set_aside_percent,
response_copies_req,
date_close_projected,
datetime_proposed,
date_signed,
date_terminated,
date_renewed,
start_date,
end_date,
buy_or_sell,
issue_or_receive,
estimated_amount,
estimated_amount_renewed,
currency_code_renewed,
last_update_login,
upg_orig_system_ref,
upg_orig_system_ref_id,
application_id,
orig_system_source_code,
orig_system_id1,
orig_system_reference1,
program_id,
request_id,
program_update_date,
program_application_id,
price_list_id,
pricing_date,
sign_by_date,
total_line_list_price,
USER_ESTIMATED_AMOUNT,
GOVERNING_CONTRACT_YN,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
-- new columns to replace rules
conversion_type,
conversion_rate,
conversion_rate_date,
conversion_euro_rate,
cust_acct_id,
bill_to_site_use_id,
inv_rule_id,
renewal_type_code,
renewal_notify_to,
renewal_end_date,
ship_to_site_use_id,
payment_term_id,
document_id,
-- R12 Data Model Changes 4485150 Start
approval_type,
term_cancel_source,
payment_instruction_type,
-- R12 Data Model Changes 4485150 End
billed_at_source
)
VALUES (
l_chr_rec.id,
l_chr_rec.contract_number,
--l_chr_rec.authoring_org_id,
l_chr_rec.authoring_org_id,
l_chr_rec.org_id, --mmadhavi added for MOAC
l_chr_rec.contract_number_modifier,
l_chr_rec.chr_id_response,
l_chr_rec.chr_id_award,
l_chr_rec.INV_ORGANIZATION_ID,
l_chr_rec.sts_code,
l_chr_rec.qcl_id,
l_chr_rec.scs_code,
l_chr_rec.trn_code,
l_chr_rec.currency_code,
l_chr_rec.archived_yn,
l_chr_rec.deleted_yn,
l_chr_rec.template_yn,
l_chr_rec.chr_type,
l_chr_rec.object_version_number,
l_chr_rec.created_by,
l_chr_rec.creation_date,
l_chr_rec.last_updated_by,
l_chr_rec.last_update_date,
l_chr_rec.cust_po_number_req_yn,
l_chr_rec.pre_pay_req_yn,
l_chr_rec.cust_po_number,
l_chr_rec.dpas_rating,
l_chr_rec.template_used,
l_chr_rec.date_approved,
l_chr_rec.datetime_cancelled,
l_chr_rec.auto_renew_days,
l_chr_rec.date_issued,
l_chr_rec.datetime_responded,
l_chr_rec.rfp_type,
l_chr_rec.keep_on_mail_list,
l_chr_rec.set_aside_percent,
l_chr_rec.response_copies_req,
l_chr_rec.date_close_projected,
l_chr_rec.datetime_proposed,
l_chr_rec.date_signed,
l_chr_rec.date_terminated,
l_chr_rec.date_renewed,
l_chr_rec.start_date,
l_chr_rec.end_date,
l_chr_rec.buy_or_sell,
l_chr_rec.issue_or_receive,
l_chr_rec.estimated_amount,
l_chr_rec.estimated_amount_renewed,
l_chr_rec.currency_code_renewed,
l_chr_rec.last_update_login,
l_chr_rec.upg_orig_system_ref,
l_chr_rec.upg_orig_system_ref_id,
l_chr_rec.application_id,
l_chr_rec.orig_system_source_code,
l_chr_rec.orig_system_id1,
l_chr_rec.orig_system_reference1,
decode(FND_GLOBAL.CONC_PROGRAM_ID, - 1, NULL, FND_GLOBAL.CONC_PROGRAM_ID),
decode(FND_GLOBAL.CONC_REQUEST_ID, - 1, NULL, FND_GLOBAL.CONC_REQUEST_ID),
decode(FND_GLOBAL.CONC_REQUEST_ID, - 1, NULL, SYSDATE),
decode(FND_GLOBAL.PROG_APPL_ID, - 1, NULL, FND_GLOBAL.PROG_APPL_ID),
l_chr_rec.price_list_id,
l_chr_rec.pricing_date,
l_chr_rec.sign_by_date,
l_chr_rec.total_line_list_price,
l_chr_rec.USER_ESTIMATED_AMOUNT,
l_chr_rec.GOVERNING_CONTRACT_YN,
l_chr_rec.attribute_category,
l_chr_rec.attribute1,
l_chr_rec.attribute2,
l_chr_rec.attribute3,
l_chr_rec.attribute4,
l_chr_rec.attribute5,
l_chr_rec.attribute6,
l_chr_rec.attribute7,
l_chr_rec.attribute8,
l_chr_rec.attribute9,
l_chr_rec.attribute10,
l_chr_rec.attribute11,
l_chr_rec.attribute12,
l_chr_rec.attribute13,
l_chr_rec.attribute14,
l_chr_rec.attribute15,
-- new columns to replace rules
l_chr_rec.conversion_type,
l_chr_rec.conversion_rate,
l_chr_rec.conversion_rate_date,
l_chr_rec.conversion_euro_rate,
l_chr_rec.cust_acct_id,
l_chr_rec.bill_to_site_use_id,
l_chr_rec.inv_rule_id,
l_chr_rec.renewal_type_code,
l_chr_rec.renewal_notify_to,
l_chr_rec.renewal_end_date,
l_chr_rec.ship_to_site_use_id,
l_chr_rec.payment_term_id,
l_chr_rec.id,
-- R12 Data Model Changes 4485150 Start
l_chr_rec.approval_type,
l_chr_rec.term_cancel_source,
l_chr_rec.payment_instruction_type,
-- R12 Data Model Changes 4485150 End
l_chr_rec.billed_at_source
);
okc_debug.LOG('13800: Exiting insert_row', 2);
okc_debug.LOG('13900: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('14000: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('14100: Exiting insert_row:OTHERS Exception', 2);
END insert_row;
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_okc_k_headers_tl_rec IN okc_k_headers_tl_rec_type,
x_okc_k_headers_tl_rec OUT NOCOPY okc_k_headers_tl_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
SELECT *
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN ('I', 'B');
okc_debug.LOG('14300: Entered insert_row', 2);
INSERT INTO OKC_K_HEADERS_TL(
id,
language,
source_lang,
sfwt_flag,
short_description,
comments,
description,
cognomen,
non_response_reason,
non_response_explain,
set_aside_reason,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (
l_okc_k_headers_tl_rec.id,
l_okc_k_headers_tl_rec.language,
l_okc_k_headers_tl_rec.source_lang,
l_okc_k_headers_tl_rec.sfwt_flag,
l_okc_k_headers_tl_rec.short_description,
l_okc_k_headers_tl_rec.comments,
l_okc_k_headers_tl_rec.description,
l_okc_k_headers_tl_rec.cognomen,
l_okc_k_headers_tl_rec.non_response_reason,
l_okc_k_headers_tl_rec.non_response_explain,
l_okc_k_headers_tl_rec.set_aside_reason,
l_okc_k_headers_tl_rec.created_by,
l_okc_k_headers_tl_rec.creation_date,
l_okc_k_headers_tl_rec.last_updated_by,
l_okc_k_headers_tl_rec.last_update_date,
l_okc_k_headers_tl_rec.last_update_login);
okc_debug.LOG('14400: Exiting insert_row', 2);
okc_debug.LOG('14500: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('14600: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('14700: Exiting insert_row:OTHERS Exception', 2);
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_chrv_rec IN chrv_rec_type,
x_chrv_rec OUT NOCOPY chrv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
l_chrv_rec.LAST_UPDATE_DATE := l_chrv_rec.CREATION_DATE;
l_chrv_rec.LAST_UPDATED_BY := NVL(OKC_ASYNC_PVT.G_USER_ID, FND_GLOBAL.USER_ID);
l_chrv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
SELECT application_id
FROM okc_classes_b cls, okc_subclasses_b scs
WHERE cls.code = scs.cls_code
AND scs.code = p_scs_code;
x_chrv_rec.DELETED_YN := UPPER(x_chrv_rec.DELETED_YN);
okc_debug.LOG('15000: Entered insert_row', 2);
insert_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_chr_rec,
lx_chr_rec
);
insert_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_okc_k_headers_tl_rec,
lx_okc_k_headers_tl_rec
);
okc_debug.LOG('15100: Exiting insert_row', 2);
okc_debug.LOG('15200: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('15300: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('15400: Exiting insert_row:OTHERS Exception', 2);
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_chrv_tbl IN chrv_tbl_type,
x_chrv_tbl OUT NOCOPY chrv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
okc_debug.LOG('15500: Entered insert_row', 2);
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_chrv_rec => l_chrv_rec,
x_chrv_rec => x_chrv_tbl(i));
okc_debug.LOG('15600: Exiting insert_row', 2);
okc_debug.LOG('15700: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('15800: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('15900: Exiting insert_row:OTHERS Exception', 2);
END insert_row;
SELECT OBJECT_VERSION_NUMBER
FROM OKC_K_HEADERS_ALL_B --mmadhavi changed to _ALL for MOAC
WHERE ID = p_chr_rec.id
AND OBJECT_VERSION_NUMBER = p_chr_rec.object_version_number
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
SELECT OBJECT_VERSION_NUMBER
FROM OKC_K_HEADERS_ALL_B --mmadhavi changed to _ALL for MOAC
WHERE ID = p_chr_rec.id;
OKC_API.set_message(G_APP_NAME, 'OKC_FORM_RECORD_DELETED');
OKC_API.set_message(G_FND_APP, G_RECORD_LOGICALLY_DELETED);
SELECT *
FROM OKC_K_HEADERS_TL
WHERE ID = p_okc_k_headers_tl_rec.id
FOR UPDATE NOWAIT;
OKC_API.set_message(G_APP_NAME, 'OKC_FORM_RECORD_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_restricted_update IN VARCHAR2,
p_chr_rec IN chr_rec_type,
x_chr_rec OUT NOCOPY chr_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
IF (x_chr_rec.deleted_yn = OKC_API.G_MISS_CHAR)
THEN
x_chr_rec.deleted_yn := l_chr_rec.deleted_yn;
IF (x_chr_rec.last_updated_by = OKC_API.G_MISS_NUM)
THEN
x_chr_rec.last_updated_by := l_chr_rec.last_updated_by;
IF (x_chr_rec.last_update_date = OKC_API.G_MISS_DATE)
THEN
x_chr_rec.last_update_date := l_chr_rec.last_update_date;
IF (x_chr_rec.last_update_login = OKC_API.G_MISS_NUM)
THEN
x_chr_rec.last_update_login := l_chr_rec.last_update_login;
IF (x_chr_rec.program_update_date = OKC_API.G_MISS_DATE)
THEN
x_chr_rec.program_update_date := l_chr_rec.program_update_date;
okc_debug.LOG('18800: Entered update_row', 2);
UPDATE OKC_K_HEADERS_ALL_B --mmadhavi changed to _ALL for MOAC
SET CONTRACT_NUMBER = l_def_chr_rec.contract_number,
--AUTHORING_ORG_ID = l_def_chr_rec.authoring_org_id,
CONTRACT_NUMBER_MODIFIER = l_def_chr_rec.contract_number_modifier,
CHR_ID_RESPONSE = l_def_chr_rec.chr_id_response,
CHR_ID_AWARD = l_def_chr_rec.chr_id_award,
--INV_ORGANIZATION_ID = l_def_chr_rec.INV_ORGANIZATION_ID,
STS_CODE = l_def_chr_rec.sts_code,
QCL_ID = l_def_chr_rec.qcl_id,
--SCS_CODE = l_def_chr_rec.scs_code,
TRN_CODE = l_def_chr_rec.trn_code,
CURRENCY_CODE = l_def_chr_rec.currency_code,
ARCHIVED_YN = l_def_chr_rec.archived_yn,
DELETED_YN = l_def_chr_rec.deleted_yn,
TEMPLATE_YN = l_def_chr_rec.template_yn,
--CHR_TYPE = l_def_chr_rec.chr_type,
OBJECT_VERSION_NUMBER = l_def_chr_rec.object_version_number,
--CREATED_BY = l_def_chr_rec.created_by,
--CREATION_DATE = l_def_chr_rec.creation_date,
LAST_UPDATED_BY = l_def_chr_rec.last_updated_by,
LAST_UPDATE_DATE = l_def_chr_rec.last_update_date,
CUST_PO_NUMBER_REQ_YN = l_def_chr_rec.cust_po_number_req_yn,
PRE_PAY_REQ_YN = l_def_chr_rec.pre_pay_req_yn,
CUST_PO_NUMBER = l_def_chr_rec.cust_po_number,
DPAS_RATING = l_def_chr_rec.dpas_rating,
TEMPLATE_USED = l_def_chr_rec.template_used,
DATE_APPROVED = l_def_chr_rec.date_approved,
DATETIME_CANCELLED = l_def_chr_rec.datetime_cancelled,
AUTO_RENEW_DAYS = l_def_chr_rec.auto_renew_days,
DATE_ISSUED = l_def_chr_rec.date_issued,
DATETIME_RESPONDED = l_def_chr_rec.datetime_responded,
RFP_TYPE = l_def_chr_rec.rfp_type,
KEEP_ON_MAIL_LIST = l_def_chr_rec.keep_on_mail_list,
SET_ASIDE_PERCENT = l_def_chr_rec.set_aside_percent,
RESPONSE_COPIES_REQ = l_def_chr_rec.response_copies_req,
DATE_CLOSE_PROJECTED = l_def_chr_rec.date_close_projected,
DATETIME_PROPOSED = l_def_chr_rec.datetime_proposed,
DATE_SIGNED = l_def_chr_rec.date_signed,
DATE_TERMINATED = l_def_chr_rec.date_terminated,
DATE_RENEWED = l_def_chr_rec.date_renewed,
START_DATE = l_def_chr_rec.start_date,
END_DATE = l_def_chr_rec.end_date,
BUY_OR_SELL = l_def_chr_rec.buy_or_sell,
ISSUE_OR_RECEIVE = l_def_chr_rec.issue_or_receive,
ESTIMATED_AMOUNT = l_def_chr_rec.estimated_amount,
ESTIMATED_AMOUNT_RENEWED = l_def_chr_rec.estimated_amount_renewed,
CURRENCY_CODE_RENEWED = l_def_chr_rec.currency_code_renewed,
LAST_UPDATE_LOGIN = l_def_chr_rec.last_update_login,
UPG_ORIG_SYSTEM_REF = l_def_chr_rec.upg_orig_system_ref,
UPG_ORIG_SYSTEM_REF_ID = l_def_chr_rec.upg_orig_system_ref_id,
--------APPLICATION_ID = l_def_chr_rec.application_id,
ORIG_SYSTEM_SOURCE_CODE = l_def_chr_rec.orig_system_source_code,
ORIG_SYSTEM_ID1 = l_def_chr_rec.orig_system_id1,
ORIG_SYSTEM_REFERENCE1 = l_def_chr_rec.orig_system_reference1,
PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID, - 1, NULL, FND_GLOBAL.CONC_PROGRAM_ID), l_def_chr_rec.program_id),
REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID, - 1, NULL, FND_GLOBAL.CONC_REQUEST_ID), l_def_chr_rec.request_id),
PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID, - 1, NULL, SYSDATE), NULL, l_def_chr_rec.program_update_date, SYSDATE),
PROGRAM_APPLICATION_ID = NVL(decode(FND_GLOBAL.PROG_APPL_ID, - 1, NULL, FND_GLOBAL.PROG_APPL_ID), l_def_chr_rec.program_application_id),
PRICE_LIST_ID = l_def_chr_rec.price_list_id,
PRICING_DATE = l_def_chr_rec.pricing_date,
SIGN_BY_DATE = l_def_chr_rec.sign_by_date,
TOTAL_LINE_LIST_PRICE = l_def_chr_rec.total_line_list_price,
USER_ESTIMATED_AMOUNT = l_def_chr_rec.user_estimated_amount,
GOVERNING_CONTRACT_YN = l_def_chr_rec.governing_contract_yn,
ATTRIBUTE_CATEGORY = l_def_chr_rec.attribute_category,
ATTRIBUTE1 = l_def_chr_rec.attribute1,
ATTRIBUTE2 = l_def_chr_rec.attribute2,
ATTRIBUTE3 = l_def_chr_rec.attribute3,
ATTRIBUTE4 = l_def_chr_rec.attribute4,
ATTRIBUTE5 = l_def_chr_rec.attribute5,
ATTRIBUTE6 = l_def_chr_rec.attribute6,
ATTRIBUTE7 = l_def_chr_rec.attribute7,
ATTRIBUTE8 = l_def_chr_rec.attribute8,
ATTRIBUTE9 = l_def_chr_rec.attribute9,
ATTRIBUTE10 = l_def_chr_rec.attribute10,
ATTRIBUTE11 = l_def_chr_rec.attribute11,
ATTRIBUTE12 = l_def_chr_rec.attribute12,
ATTRIBUTE13 = l_def_chr_rec.attribute13,
ATTRIBUTE14 = l_def_chr_rec.attribute14,
ATTRIBUTE15 = l_def_chr_rec.attribute15,
--new columns to replace rules
conversion_type = l_def_chr_rec. conversion_type,
conversion_rate = l_def_chr_rec. conversion_rate,
conversion_rate_date = l_def_chr_rec. conversion_rate_date,
conversion_euro_rate = l_def_chr_rec. conversion_euro_rate,
cust_acct_id = l_def_chr_rec.cust_acct_id,
bill_to_site_use_id = l_def_chr_rec.bill_to_site_use_id,
inv_rule_id = l_def_chr_rec.inv_rule_id,
renewal_type_code = l_def_chr_rec.renewal_type_code,
renewal_notify_to = l_def_chr_rec.renewal_notify_to,
renewal_end_date = l_def_chr_rec.renewal_end_date,
ship_to_site_use_id = l_def_chr_rec.ship_to_site_use_id,
payment_term_id = l_def_chr_rec.payment_term_id,
-- R12 Data Model Changes 4485150 End
approval_type = l_def_chr_rec.approval_type,
term_cancel_source = l_def_chr_rec.term_cancel_source,
payment_instruction_type = l_def_chr_rec.payment_instruction_type,
cancelled_amount = l_def_chr_rec.cancelled_amount -- LLC
-- R12 Data Model Changes 4485150 End
WHERE ID = l_def_chr_rec.id;
okc_debug.LOG('18900: Exiting update_row', 2);
okc_debug.LOG('19000: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('19100: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('19200: Exiting update_row:OTHERS Exception', 2);
END update_row;
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_okc_k_headers_tl_rec IN okc_k_headers_tl_rec_type,
x_okc_k_headers_tl_rec OUT NOCOPY okc_k_headers_tl_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
IF (x_okc_k_headers_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
THEN
x_okc_k_headers_tl_rec.last_updated_by := l_okc_k_headers_tl_rec.last_updated_by;
IF (x_okc_k_headers_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
THEN
x_okc_k_headers_tl_rec.last_update_date := l_okc_k_headers_tl_rec.last_update_date;
IF (x_okc_k_headers_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
THEN
x_okc_k_headers_tl_rec.last_update_login := l_okc_k_headers_tl_rec.last_update_login;
okc_debug.LOG('19500: Entered update_row', 2);
UPDATE OKC_K_HEADERS_TL
SET SHORT_DESCRIPTION = l_def_okc_k_headers_tl_rec.short_description,
COMMENTS = l_def_okc_k_headers_tl_rec.comments,
DESCRIPTION = l_def_okc_k_headers_tl_rec.description,
COGNOMEN = l_def_okc_k_headers_tl_rec.cognomen,
NON_RESPONSE_REASON = l_def_okc_k_headers_tl_rec.non_response_reason,
NON_RESPONSE_EXPLAIN = l_def_okc_k_headers_tl_rec.non_response_explain,
SET_ASIDE_REASON = l_def_okc_k_headers_tl_rec.set_aside_reason,
SOURCE_LANG = l_def_okc_k_headers_tl_rec.source_lang,
--CREATED_BY = l_def_okc_k_headers_tl_rec.created_by,
--CREATION_DATE = l_def_okc_k_headers_tl_rec.creation_date,
LAST_UPDATED_BY = l_def_okc_k_headers_tl_rec.last_updated_by,
LAST_UPDATE_DATE = l_def_okc_k_headers_tl_rec.last_update_date,
LAST_UPDATE_LOGIN = l_def_okc_k_headers_tl_rec.last_update_login
WHERE ID = l_def_okc_k_headers_tl_rec.id
AND USERENV('LANG') IN (SOURCE_LANG, LANGUAGE);
UPDATE OKC_K_HEADERS_TL
SET SFWT_FLAG = 'Y'
WHERE ID = l_def_okc_k_headers_tl_rec.id
AND SOURCE_LANG <> USERENV('LANG');
okc_debug.LOG('19600: Exiting update_row', 2);
okc_debug.LOG('19700: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('19800: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('19900: Exiting update_row:OTHERS Exception', 2);
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_restricted_update IN VARCHAR2,
p_chrv_rec IN chrv_rec_type,
x_chrv_rec OUT NOCOPY chrv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
l_chrv_rec.LAST_UPDATE_DATE := SYSDATE;
l_chrv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_chrv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
IF (x_chrv_rec.deleted_yn = OKC_API.G_MISS_CHAR)
THEN
x_chrv_rec.deleted_yn := l_chrv_rec.deleted_yn;
IF (x_chrv_rec.program_update_date = OKC_API.G_MISS_DATE)
THEN
x_chrv_rec.program_update_date := l_chrv_rec.program_update_date;
IF (x_chrv_rec.last_updated_by = OKC_API.G_MISS_NUM)
THEN
x_chrv_rec.last_updated_by := l_chrv_rec.last_updated_by;
IF (x_chrv_rec.last_update_date = OKC_API.G_MISS_DATE)
THEN
x_chrv_rec.last_update_date := l_chrv_rec.last_update_date;
IF (x_chrv_rec.last_update_login = OKC_API.G_MISS_NUM)
THEN
x_chrv_rec.last_update_login := l_chrv_rec.last_update_login;
x_chrv_rec.DELETED_YN := UPPER(x_chrv_rec.DELETED_YN);
-- If contract is cancelled, update datetime_cancelled
IF (Get_Status_Type(x_chrv_rec.STS_CODE) = 'CANCELLED' AND
(x_chrv_rec.datetime_cancelled = OKC_API.G_MISS_DATE OR
x_chrv_rec.datetime_cancelled IS NULL) )
THEN
x_chrv_rec.datetime_cancelled := SYSDATE;
okc_debug.LOG('20300: Entered update_row', 2);
update_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_okc_k_headers_tl_rec,
lx_okc_k_headers_tl_rec
);
update_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_restricted_update,
l_chr_rec,
lx_chr_rec
);
okc_debug.LOG('20400: Exiting update_row', 2);
okc_debug.LOG('20500: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('20600: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('20700: Exiting update_row:OTHERS Exception', 2);
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_restricted_update IN VARCHAR2,
p_chrv_tbl IN chrv_tbl_type,
x_chrv_tbl OUT NOCOPY chrv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
okc_debug.LOG('20800: Entered update_row', 2);
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_restricted_update => p_restricted_update,
p_chrv_rec => p_chrv_tbl(i),
x_chrv_rec => x_chrv_tbl(i));
okc_debug.LOG('20900: Exiting update_row', 2);
okc_debug.LOG('21000: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('21100: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('21200: Exiting update_row:OTHERS Exception', 2);
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_chr_rec IN chr_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
okc_debug.LOG('21300: Entered delete_row', 2);
DELETE FROM OKC_K_HEADERS_ALL_B --mmadhavi changed to _ALL for MOAC
WHERE ID = l_chr_rec.id;
okc_debug.LOG('21400: Exiting delete_row', 2);
okc_debug.LOG('21500: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('21600: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('21700: Exiting delete_row:OTHERS Exception', 2);
END delete_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_okc_k_headers_tl_rec IN okc_k_headers_tl_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
okc_debug.LOG('21900: Entered delete_row', 2);
DELETE FROM OKC_K_HEADERS_TL
WHERE ID = l_okc_k_headers_tl_rec.id;
okc_debug.LOG('22000: Exiting delete_row', 2);
okc_debug.LOG('22100: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('22200: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('22300: Exiting delete_row:OTHERS Exception', 2);
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_chrv_rec IN chrv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
okc_debug.LOG('22400: Entered delete_row', 2);
delete_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_okc_k_headers_tl_rec
);
delete_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_chr_rec
);
okc_debug.LOG('22500: Exiting delete_row', 2);
okc_debug.LOG('22600: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('22700: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('22800: Exiting delete_row:OTHERS Exception', 2);
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_chrv_tbl IN chrv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
SELECT COUNT(1)
FROM OKC_K_LINES_B
WHERE chr_id = p_chrv_tbl(i).id;
okc_debug.LOG('22900: Entered delete_row', 2);
-- delete only if there are no detail records
IF (l_dummy_val = 0) THEN
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_chrv_rec => p_chrv_tbl(i));
okc_debug.LOG('23000: Exiting delete_row', 2);
okc_debug.LOG('23100: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
okc_debug.LOG('23200: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
okc_debug.LOG('23300: Exiting delete_row:OTHERS Exception', 2);
END delete_row;
PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2, p_chrv_tbl chrv_tbl_type) IS
l_tabsize NUMBER := p_chrv_tbl.COUNT;
in_deleted_yn OKC_DATATYPES.Var3TabTyp;
in_program_update_date OKC_DATATYPES.DateTabTyp;
in_last_updated_by OKC_DATATYPES.NumberTabTyp;
in_last_update_date OKC_DATATYPES.DateTabTyp;
in_last_update_login OKC_DATATYPES.NumberTabTyp;
okc_debug.LOG('23400: Entered INSERT_ROW_UPG', 2);
in_deleted_yn (j) := p_chrv_tbl(i).deleted_yn;
in_program_update_date (j) := p_chrv_tbl(i).program_update_date;
in_last_updated_by (j) := p_chrv_tbl(i).last_updated_by;
in_last_update_date (j) := p_chrv_tbl(i).last_update_date;
in_last_update_login (j) := p_chrv_tbl(i).last_update_login;
INSERT
INTO OKC_K_HEADERS_ALL_B --mmadhavi changed to _ALL for MOAC
(
id,
-- GCHADHA --
-- BUG 3941485 --
document_id,
-- END GCHADHA --
contract_number,
authoring_org_id,
org_id, --mmadhavi added for MOAC
contract_number_modifier,
chr_id_response,
chr_id_award,
sts_code,
qcl_id,
scs_code,
trn_code,
currency_code,
archived_yn,
deleted_yn,
template_yn,
chr_type,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
cust_po_number_req_yn,
pre_pay_req_yn,
cust_po_number,
dpas_rating,
template_used,
date_approved,
datetime_cancelled,
auto_renew_days,
date_issued,
datetime_responded,
rfp_type,
keep_on_mail_list,
set_aside_percent,
response_copies_req,
date_close_projected,
datetime_proposed,
date_signed,
date_terminated,
date_renewed,
start_date,
end_date,
buy_or_sell,
issue_or_receive,
last_update_login,
upg_orig_system_ref,
upg_orig_system_ref_id,
application_id,
orig_system_source_code,
orig_system_id1,
orig_system_reference1,
program_id,
request_id,
program_update_date,
program_application_id,
price_list_id,
pricing_date,
sign_by_date,
total_line_list_price,
user_estimated_amount,
governing_contract_yn,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
estimated_amount,
inv_organization_id,
estimated_amount_renewed,
currency_code_renewed,
-- new columns to replace rules
conversion_type,
conversion_rate,
conversion_rate_date,
conversion_euro_rate,
cust_acct_id,
bill_to_site_use_id,
inv_rule_id,
renewal_type_code,
renewal_notify_to,
renewal_end_date,
ship_to_site_use_id,
payment_term_id,
-- R12 Data Model Changes 4485150 End
approval_type,
term_cancel_source,
payment_instruction_type
-- R12 Data Model Changes 4485150 End
)
VALUES (
in_id(i),
-- GCHADHA --
-- 3941485 --
in_document_id(i),
-- END GCHADHA --
in_contract_number(i),
in_authoring_org_id(i),
in_org_id(i), --mmadhavi added for MOAC
in_contract_number_modifier(i),
in_chr_id_response(i),
in_chr_id_award(i),
in_sts_code(i),
in_qcl_id(i),
in_scs_code(i),
in_trn_code(i),
in_currency_code(i),
in_archived_yn(i),
in_deleted_yn(i),
in_template_yn(i),
in_chr_type(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_cust_po_number_req_yn(i),
in_pre_pay_req_yn(i),
in_cust_po_number(i),
in_dpas_rating(i),
in_template_used(i),
in_date_approved(i),
in_datetime_cancelled(i),
in_auto_renew_days(i),
in_date_issued(i),
in_datetime_responded(i),
in_rfp_type(i),
in_keep_on_mail_list(i),
in_set_aside_percent(i),
in_response_copies_req(i),
in_date_close_projected(i),
in_datetime_proposed(i),
in_date_signed(i),
in_date_terminated(i),
in_date_renewed(i),
in_start_date(i),
in_end_date(i),
in_buy_or_sell(i),
in_issue_or_receive(i),
in_last_update_login(i),
in_upg_orig_system_ref(i),
in_upg_orig_system_ref_id(i),
in_application_id(i),
in_orig_system_source_code(i),
in_orig_system_id1(i),
in_orig_system_reference1(i),
in_program_id(i),
in_request_id(i),
in_program_update_date(i),
in_program_application_id(i),
in_price_list_id(i),
in_pricing_date(i),
in_sign_by_date(i),
in_total_line_list_price(i),
in_user_estimated_amount(i),
in_governing_contract_yn(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),
in_estimated_amount(i),
in_inv_organization_id(i),
in_estimated_amount_renewed(i),
in_currency_code_renewed(i),
--new columns to replace rules
in_conversion_type(i),
in_conversion_rate(i),
in_conversion_rate_date(i),
in_conversion_euro_rate(i),
in_cust_acct_id(i),
in_bill_to_site_use_id(i),
in_inv_rule_id(i),
in_renewal_type_code(i),
in_renewal_notify_to(i),
in_renewal_end_date(i),
in_ship_to_site_use_id(i),
in_payment_term_id(i),
-- R12 Data Model Changes 4485150 End
in_approval_type(i),
in_term_cancel_source(i),
in_payment_instruction_type(i)
-- R12 Data Model Changes 4485150 End
);
INSERT INTO OKC_K_HEADERS_TL(
id,
language,
source_lang,
sfwt_flag,
short_description,
comments,
description,
cognomen,
non_response_reason,
non_response_explain,
set_aside_reason,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (
in_id(i),
OKC_UTIL.g_language_code(lang_i),
l_source_lang,
in_sfwt_flag(i),
in_short_description(i),
in_comments(i),
in_description(i),
in_cognomen(i),
in_non_response_reason(i),
in_non_response_explain(i),
in_set_aside_reason(i),
in_created_by(i),
in_creation_date(i),
in_last_updated_by(i),
in_last_update_date(i),
in_last_update_login(i)
);
INSERT
INTO OKC_K_VERS_NUMBERS
(
chr_id,
major_version,
minor_version,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (
in_id(i),
0,
0,
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)
);
okc_debug.LOG('23500: Exiting INSERT_ROW_UPG', 2);
okc_debug.LOG('23600: Exiting INSERT_ROW_UPG:OTHERS Exception', 2);
END INSERT_ROW_UPG;
INSERT INTO okc_k_headers_all_bh --mmadhavi changed to _ALL for MOAC
(
major_version,
id,
contract_number,
authoring_org_id,
org_id, --mmadhavi added for MOAC
contract_number_modifier,
chr_id_response,
chr_id_award,
sts_code,
qcl_id,
scs_code,
trn_code,
currency_code,
archived_yn,
deleted_yn,
template_yn,
chr_type,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
cust_po_number_req_yn,
pre_pay_req_yn,
cust_po_number,
dpas_rating,
template_used,
date_approved,
datetime_cancelled,
auto_renew_days,
date_issued,
datetime_responded,
rfp_type,
keep_on_mail_list,
set_aside_percent,
response_copies_req,
date_close_projected,
datetime_proposed,
date_signed,
date_terminated,
date_renewed,
start_date,
end_date,
buy_or_sell,
issue_or_receive,
last_update_login,
application_id,
orig_system_source_code,
orig_system_id1,
orig_system_reference1,
upg_orig_system_ref,
upg_orig_system_ref_id,
program_id,
request_id,
program_update_date,
program_application_id,
price_list_id,
pricing_date,
sign_by_date,
total_line_list_price,
user_estimated_amount,
governing_contract_yn,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
estimated_amount,
inv_organization_id,
currency_code_renewed,
estimated_amount_renewed,
-- new columns to replace rules
conversion_type,
conversion_rate,
conversion_rate_date,
conversion_euro_rate,
cust_acct_id,
bill_to_site_use_id,
inv_rule_id,
renewal_type_code,
renewal_notify_to,
renewal_end_date,
ship_to_site_use_id,
payment_term_id,
document_id,
-- R12 Data Model Changes 4485150 End
approval_type,
term_cancel_source,
payment_instruction_type,
-- R12 Data Model Changes 4485150 End
cancelled_amount -- LLC
)
SELECT
p_major_version,
id,
contract_number,
authoring_org_id,
org_id, --mmadhavi added for MOAC
contract_number_modifier,
chr_id_response,
chr_id_award,
sts_code,
qcl_id,
scs_code,
trn_code,
currency_code,
archived_yn,
deleted_yn,
template_yn,
chr_type,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
cust_po_number_req_yn,
pre_pay_req_yn,
cust_po_number,
dpas_rating,
template_used,
date_approved,
datetime_cancelled,
auto_renew_days,
date_issued,
datetime_responded,
rfp_type,
keep_on_mail_list,
set_aside_percent,
response_copies_req,
date_close_projected,
datetime_proposed,
date_signed,
date_terminated,
date_renewed,
start_date,
end_date,
buy_or_sell,
issue_or_receive,
last_update_login,
application_id,
orig_system_source_code,
orig_system_id1,
orig_system_reference1,
upg_orig_system_ref,
upg_orig_system_ref_id,
program_id,
request_id,
program_update_date,
program_application_id,
price_list_id,
pricing_date,
sign_by_date,
total_line_list_price,
user_estimated_amount,
governing_contract_yn,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
estimated_amount,
inv_organization_id,
currency_code_renewed,
estimated_amount_renewed,
-- new columns to replace rules
conversion_type,
conversion_rate,
conversion_rate_date,
conversion_euro_rate,
cust_acct_id,
bill_to_site_use_id,
inv_rule_id,
renewal_type_code,
renewal_notify_to,
renewal_end_date,
ship_to_site_use_id,
payment_term_id,
document_id,
-- R12 Data Model Changes 4485150 Start
approval_type,
term_cancel_source,
payment_instruction_type,
cancelled_amount -- LLC
-- R12 Data Model Changes 4485150 End
FROM okc_k_headers_all_b --mamdhavi changed to _ALL for MOAC
WHERE id = p_chr_id;
INSERT INTO okc_k_headers_tlh
(
major_version,
id,
language,
source_lang,
sfwt_flag,
short_description,
comments,
description,
cognomen,
non_response_reason,
non_response_explain,
set_aside_reason,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT
p_major_version,
id,
language,
source_lang,
sfwt_flag,
short_description,
comments,
description,
cognomen,
non_response_reason,
non_response_explain,
set_aside_reason,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
FROM okc_k_headers_tl
WHERE id = p_chr_id;
INSERT INTO okc_k_headers_tl
(
id,
language,
source_lang,
sfwt_flag,
short_description,
comments,
description,
cognomen,
non_response_reason,
non_response_explain,
set_aside_reason,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT
id,
language,
source_lang,
sfwt_flag,
short_description,
comments,
description,
cognomen,
non_response_reason,
non_response_explain,
set_aside_reason,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
FROM okc_k_headers_tlh
WHERE id = p_chr_id
AND major_version = p_major_version;
INSERT INTO okc_k_headers_all_b --mmadhavi changed to _ALL for MOAC
(
id,
contract_number,
authoring_org_id,
org_id, --mmadhavi added for MOAC
contract_number_modifier,
chr_id_response,
chr_id_award,
sts_code,
qcl_id,
scs_code,
trn_code,
currency_code,
archived_yn,
deleted_yn,
template_yn,
chr_type,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
cust_po_number_req_yn,
pre_pay_req_yn,
cust_po_number,
dpas_rating,
template_used,
date_approved,
datetime_cancelled,
auto_renew_days,
date_issued,
datetime_responded,
rfp_type,
keep_on_mail_list,
set_aside_percent,
response_copies_req,
date_close_projected,
datetime_proposed,
date_signed,
date_terminated,
date_renewed,
start_date,
end_date,
buy_or_sell,
issue_or_receive,
last_update_login,
application_id,
orig_system_source_code,
orig_system_id1,
orig_system_reference1,
upg_orig_system_ref,
upg_orig_system_ref_id,
program_id,
request_id,
program_update_date,
program_application_id,
price_list_id,
pricing_date,
sign_by_date,
total_line_list_price,
user_estimated_amount,
governing_contract_yn,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
estimated_amount,
inv_organization_id,
currency_code_renewed,
estimated_amount_renewed,
-- new columns to replace rules
conversion_type,
conversion_rate,
conversion_rate_date,
conversion_euro_rate,
cust_acct_id,
bill_to_site_use_id,
inv_rule_id,
renewal_type_code,
renewal_notify_to,
renewal_end_date,
ship_to_site_use_id,
payment_term_id,
document_id,
-- R12 Data Model Changes 4485150 Start
approval_type,
term_cancel_source,
payment_instruction_type,
cancelled_amount -- LLC
-- R12 Data Model Changes 4485150 Start
)
SELECT
id,
contract_number,
authoring_org_id,
org_id, --mmadhavi added for MOAC
contract_number_modifier,
chr_id_response,
chr_id_award,
sts_code,
qcl_id,
scs_code,
trn_code,
currency_code,
archived_yn,
deleted_yn,
template_yn,
chr_type,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
cust_po_number_req_yn,
pre_pay_req_yn,
cust_po_number,
dpas_rating,
template_used,
date_approved,
datetime_cancelled,
auto_renew_days,
date_issued,
datetime_responded,
rfp_type,
keep_on_mail_list,
set_aside_percent,
response_copies_req,
date_close_projected,
datetime_proposed,
date_signed,
date_terminated,
date_renewed,
start_date,
end_date,
buy_or_sell,
issue_or_receive,
last_update_login,
application_id,
orig_system_source_code,
orig_system_id1,
orig_system_reference1,
upg_orig_system_ref,
upg_orig_system_ref_id,
program_id,
request_id,
program_update_date,
program_application_id,
price_list_id,
pricing_date,
sign_by_date,
total_line_list_price,
user_estimated_amount,
governing_contract_yn,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
estimated_amount,
inv_organization_id,
currency_code_renewed,
estimated_amount_renewed,
conversion_type,
conversion_rate,
conversion_rate_date,
conversion_euro_rate,
cust_acct_id,
bill_to_site_use_id,
inv_rule_id,
renewal_type_code,
renewal_notify_to,
renewal_end_date,
ship_to_site_use_id,
payment_term_id,
document_id,
-- R12 data Model Changes 4485150 Start
approval_type,
term_cancel_source,
payment_instruction_type,
cancelled_amount -- LLC
-- R12 data Model Changes 4485150 End
FROM okc_k_headers_all_bh --mmadhavi changed to _ALL for MOAC
WHERE id = p_chr_id
AND major_version = p_major_version;