[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NULL
FROM okc_bus_doc_types_b
WHERE document_type = p_contract_type
AND (INSTR( FND_PROFILE.VALUE('OKC_REP_INTENTS'), intent) <> 0
OR FND_PROFILE.VALUE('OKC_REP_INTENTS') IS NULL);
SELECT
h.org_id
FROM
po_headers_all h
,okc_template_usages t
WHERE h.po_header_id = t.document_id
AND t.document_type IN ('PA_BLANKET','PA_CONTRACT','PO_STANDARD')
AND t.document_type = p_contract_type
AND h.po_header_id = p_contract_id;
SELECT
h.org_id
FROM
pon_auction_headers_all h
,okc_template_usages t
WHERE h.auction_header_id = t.document_id
AND t.document_type IN ('AUCTION','RFI','RFQ')
AND t.document_type = p_contract_type
AND h.auction_header_id = p_contract_id;
SELECT
h.org_id
FROM
oe_blanket_headers_all h
,okc_template_usages t
WHERE h.header_id = t.document_id
AND t.document_type = 'B'
AND t.document_type = p_contract_type
AND h.header_id = p_contract_id;
SELECT
h.org_id
FROM
oe_order_headers_all h
,okc_template_usages t
WHERE h.header_id = t.document_id
AND t.document_type = 'O'
AND t.document_type = p_contract_type
AND h.header_id = p_contract_id;
SELECT
h.org_id
FROM
aso_quote_headers_all h
,okc_template_usages t
WHERE h.quote_header_id = t.document_id
AND t.document_type = 'QUOTE'
AND t.document_type = p_contract_type
AND h.quote_header_id = p_contract_id;
SELECT
NULL
FROM
aso_quote_headers_all h
WHERE h.quote_header_id = p_contract_id
AND get_quote_access(
(SELECT s.resource_id
FROM jtf_rs_salesreps s
WHERE s.person_id = fnd_global.employee_id()),
h.quote_number) <> 'NONE';
SELECT organization_id
FROM mo_glob_org_access_tmp
WHERE organization_id = p_org_id;
SELECT NULL
FROM okc_bus_doc_types_b
WHERE document_type = p_contract_type
AND ((is_sales_workbench() = 'N'
AND (INSTR( FND_PROFILE.VALUE('OKC_REP_INTENTS'), intent) <> 0
OR FND_PROFILE.VALUE('OKC_REP_INTENTS') IS NULL)
)
OR (is_sales_workbench() = 'Y'
AND intent IN ('S', 'O')
)
);
SELECT org_id,
owner_id,
use_acl_flag,
contract_type,
contract_status_code,
esignature_required
FROM okc_rep_contracts_all
WHERE contract_id = p_contract_id;
SELECT usr.user_id
FROM okc_rep_party_contacts c, fnd_user usr
WHERE c.contract_id = p_contract_id
AND c.esignature_type = 'SELF'
AND usr.employee_id = c.contact_id;
SELECT
NULL
FROM
jtf_rs_groups_denorm d
,jtf_rs_group_members m
,jtf_rs_resource_extns e
,fnd_grants g
,fnd_objects o
WHERE d.parent_group_id = g.parameter2
AND TRUNC(SYSDATE)
BETWEEN d.start_date_active
AND NVL(d.end_date_active,TRUNC(SYSDATE))
and d.group_id = m.group_id
and m.delete_flag <> 'Y'
and e.resource_id = m.resource_id
and g.object_id = o.object_id
AND o.obj_name = G_OBJECT_NAME
AND g.grantee_type = G_FND_GRANTEE_TYPE_GROUP
AND g.instance_pk1_value = p_contract_id1
AND e.user_id = FND_GLOBAL.user_id()
AND (
(g.parameter3 = G_FND_GRANTS_UPDATE_ACCESS AND p_function_name IN (G_SELECT_ACCESS_LEVEL, G_UPDATE_ACCESS_LEVEL))
OR (g.parameter3 = G_FND_GRANTS_VIEW_ACCESS AND p_function_name = G_SELECT_ACCESS_LEVEL)
)
UNION ALL
SELECT
NULL
FROM
fnd_grants g
,fnd_objects o
WHERE g.object_id = o.object_id
AND o.obj_name = G_OBJECT_NAME
AND g.grantee_type = G_FND_GRANTEE_TYPE_USER
AND g.instance_pk1_value = p_contract_id2
AND (
(g.grantee_key = p_grantee_key AND (g.grantee_orig_system = p_grantee_orig_system OR g.grantee_orig_system = 'JRES_IND')) -- for R12 functionality
OR (g.grantee_key = FND_GLOBAL.user_name() AND g.grantee_orig_system = 'PER') -- for 11.5 backward compatibility
)
AND (
(g.parameter3 = G_FND_GRANTS_UPDATE_ACCESS AND p_function_name IN (G_SELECT_ACCESS_LEVEL, G_UPDATE_ACCESS_LEVEL))
OR (g.parameter3 = G_FND_GRANTS_VIEW_ACCESS AND p_function_name = G_SELECT_ACCESS_LEVEL)
);
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE user_id = FND_GLOBAL.user_id();
---for pending approval documents, need to check if user falls in the list of approvers, then give them access to view/update contract details
IF (l_status_code = 'PENDING_APPROVAL') THEN
IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name, 'checking whether user is present in approvers list');
---for pending signature documents, need to check if user falls in the list of igners, then give them access to view/update contract details
IF (l_status_code = 'PENDING_SIGNATURE' and l_sign_type = 'E') THEN
IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name, 'checking whether user is present in signers list');
IF ( p_function_name = G_UPDATE_ACCESS_LEVEL AND NOT( FND_FUNCTION.TEST(G_FUNC_OKC_REP_ADMINISTRATOR,'Y') OR FND_FUNCTION.TEST(G_FUNC_OKC_REP_USER_FUNC,'Y') ) ) THEN
IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_STATEMENT ,G_MODULE||l_api_name,
'returning FALSE, queried for UPDATE access, not an Admin, not a User, must be a Viewer');
UPDATE okc_rep_contracts_all
SET contract_status_code = p_status_code,
contract_last_update_date = sysdate,
contract_last_updated_by = FND_GLOBAL.user_id()
WHERE contract_id = p_contract_id
AND contract_version_num = p_contract_version;
'Before inserting a row into OKC_REP_CON_STATUS_HIST');
INSERT INTO OKC_REP_CON_STATUS_HIST(
contract_id,
contract_version_num,
status_code,
status_change_date,
changed_by_user_id,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(
p_contract_id,
p_contract_version,
p_status_code,
sysdate,
l_user_id,
1,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id);
'After inserting a row into OKC_REP_CON_STATUS_HIST');
'Before inserting a row into OKC_REP_CON_APPROVALS');
INSERT INTO OKC_REP_CON_APPROVALS(
contract_id,
contract_version_num,
action_code,
user_id,
action_date,
notes,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
forward_user_id)
VALUES(
p_contract_id,
p_contract_version,
p_action_code,
p_user_id,
sysdate,
p_note,
1,
p_user_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_forward_user_id);
'After inserting a row into OKC_REP_CON_APPROVALS');
'Before inserting a row into OKC_REP_SIGNATURE_DETAILS');
INSERT INTO OKC_REP_SIGNATURE_DETAILS(
contract_id,
sequence_level,
contact_id,
party_id,
party_role_code,
contract_version_num,
action_code,
user_id,
SIGNED_DATE,
notes,
object_version_number,
source,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
forward_user_id)
VALUES(
p_contract_id,
p_sequence_id,
p_contact_id,
p_party_id,
p_party_role_code,
p_contract_version,
p_action_code,
p_user_id,
sysdate,
p_note,
1,
'W',
p_user_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_forward_user_id);
'After inserting a row into OKC_REP_SIGNATURE_DETAILS');
SELECT resp_party_code
FROM okc_resp_parties_tl
WHERE UPPER(name) = UPPER(p_party_role_txt)
AND language = USERENV('LANG')
AND document_type_class = 'REPOSITORY';
SELECT 1
FROM okc_resp_parties_b
WHERE resp_party_code = p_party_role_code
AND intent = p_intent
AND document_type_class = 'REPOSITORY';
SELECT organization_id
FROM hr_all_organization_units
WHERE UPPER(name) = UPPER(p_party_name);
SELECT party_id
FROM hz_parties
WHERE UPPER(party_name) = UPPER(p_party_name)
AND party_type IN ('ORGANIZATION', 'PERSON'); /*--10334886: Added person party Type*/
SELECT vendor_id
FROM po_vendors
WHERE UPPER(vendor_name) = UPPER(p_party_name);*/
SELECT ap.vendor_id
FROM ap_suppliers ap,
hz_parties hp
WHERE ap.party_id = hp.party_id
AND UPPER(hp.party_name) = UPPER(p_party_name);
SELECT 1
FROM okc_rep_contract_parties
WHERE contract_id = p_contract_id
AND party_id = p_party_id
AND party_role_code IN (p_party_role_code, l_party_role_code2);
SELECT 1
FROM okc_rep_imp_parties_t
WHERE imp_contract_id = p_contract_id
AND party_id = p_party_id
AND party_role_code = p_party_role_code;
SELECT 1
FROM hr_all_organization_units
WHERE UPPER(name) = UPPER(p_party_name)
AND organization_id = p_party_id;
SELECT 1
FROM hz_parties
WHERE UPPER(party_name) = UPPER(p_party_name)
AND party_id = p_party_id;
SELECT 1
FROM po_vendors
WHERE UPPER(vendor_name) = UPPER(p_party_name)
AND vendor_id = p_party_id; */
SELECT 1
FROM ap_suppliers ap,
hz_parties hp
WHERE UPPER(ap.vendor_name) = UPPER(p_party_name)
AND hp.party_id = ap.party_id
AND ap.vendor_id = p_party_id;
SELECT 1
FROM hz_party_sites
WHERE party_id = p_party_id
AND party_site_id = p_location_id;
SELECT 1
FROM po_vendor_sites_all
WHERE vendor_id = p_party_id
AND vendor_site_id = p_location_id;
SELECT 1
FROM okc_rep_party_contacts
WHERE contract_id = p_contract_id
AND party_id = p_party_id
AND party_role_code = p_party_role_code
AND contact_id = p_contact_id
AND contact_role_id = p_contact_role_id;
SELECT 1
FROM per_workforce_v
WHERE person_id = p_contact_id
AND full_name = p_contact_name;
SELECT 1
FROM hz_relationships hr,
hz_parties hz,
hz_parties hz1
WHERE hr.party_id = p_contact_id
AND hr.subject_type = 'PERSON'
AND hr.object_type = 'ORGANIZATION'
AND hr.object_table_name = 'HZ_PARTIES'
AND hr.object_id = p_party_id
AND hr.relationship_code = 'CONTACT_OF'
AND hz.party_id = p_contact_id
AND hz1.party_id = hr.subject_id
AND hz1.party_name = p_contact_name;
SELECT 1
FROM po_vendor_contacts pvc,
Po_vendor_sites_all pvs
WHERE pvs.vendor_id = p_party_id
AND pvc.vendor_site_id = pvs.vendor_site_id
AND pvc.vendor_contact_id = p_contact_id;
SELECT OKC_REP_IMP_ERRORS_T_S.NEXTVAL
FROM DUAL;
INSERT INTO OKC_REP_IMP_ERRORS_T(
IMP_ERROR_ID,
IMP_CONTRACT_ID,
ERROR_OBJECT_TYPE,
ERROR_OBJECT_ID,
ERROR_MESSAGE,
CREATION_DATE,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
run_id)
VALUES(
l_imp_error_id,
p_contract_id,
p_error_obj_type,
p_error_obj_id,
p_error_msg_txt,
sysdate,
p_program_id,
p_program_login_id,
p_program_app_id,
null,
p_run_id
);
SELECT IMP_DOCUMENT_ID,
IMP_CONTRACT_ID,
DOCUMENT_INDEX,
FILE_NAME,
DOCUMENT_DESC,
CATEGORY_NAME_TXT,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
FROM OKC_REP_IMP_DOCUMENTS_T
WHERE run_id = p_run_id
AND VALID_FLAG in ('Y', 'U');
select cat.name
from fnd_document_categories cat,
fnd_document_categories_tl cattl
where UPPER(cattl.user_name) = UPPER(p_category_name)
and cat.category_id = cattl.category_id
and cat.name like 'OKC_REPO_%'
and (cat.start_date_active is null OR trunc(cat.start_date_active) <= trunc(sysdate))
and (cat.end_date_active is null OR trunc(cat.end_date_active) >= trunc(sysdate))
and language = userenv('LANG');
UPDATE OKC_REP_IMP_DOCUMENTS_T
SET CATEGORY_CODE = l_category_code,
VALID_FLAG = l_valid_flag
where imp_document_id = l_import_documents_rec.imp_document_id;
UPDATE OKC_REP_IMP_DOCUMENTS_T
SET VALID_FLAG = l_valid_flag
where imp_document_id = l_import_documents_rec.imp_document_id;
SELECT IMP_PARTY_ID,
IMP_CONTRACT_ID,
PARTY_INDEX,
SIGNED_BY_TXT,
SIGNED_DATE,
PARTY_NAME_TXT,
PARTY_ROLE_TXT,
VALID_FLAG,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID
FROM OKC_REP_IMP_PARTIES_T
WHERE run_id = p_run_id
AND Nvl(VALID_FLAG, 'U') IN ('U', 'Y');
SELECT INTENT
FROM OKC_BUS_DOC_TYPES_V bus_doc,
OKC_REP_IMP_CONTRACTS_T temp
WHERE bus_doc.name = temp.contract_type_txt
AND temp.imp_contract_id = p_imp_contract_id
AND bus_doc.document_type_class = 'REPOSITORY';
SELECT LOOKUP_CODE
FROM FND_LOOKUPS fnd, OKC_REP_IMP_CONTRACTS_T con
WHERE con.imp_contract_id = p_imp_contract_id
AND fnd.LOOKUP_TYPE = 'OKC_REP_CONTRACT_STATUSES'
AND fnd.LOOKUP_CODE IN ('SIGNED', 'APPROVED', 'DRAFT')
AND UPPER(fnd.MEANING) = UPPER(con.CONTRACT_STATUS_TXT);
UPDATE OKC_REP_IMP_CONTRACTS_T
SET VALID_FLAG = 'N'
WHERE IMP_CONTRACT_ID = l_import_parties_rec.imp_contract_id;
UPDATE OKC_REP_IMP_PARTIES_T
SET
PARTY_ID = l_party_id,
PARTY_ROLE_CODE = l_party_role_code,
--SIGNED_DATE = l_signed_date,
VALID_FLAG = l_valid_flag
WHERE IMP_PARTY_ID = l_import_parties_rec.imp_party_id;
UPDATE OKC_REP_IMP_PARTIES_T
SET
VALID_FLAG = l_valid_flag
WHERE IMP_PARTY_ID = l_import_parties_rec.imp_party_id;
SELECT OKC_REP_CONTRACTS_ALL_S1.NEXTVAL
FROM DUAL;
SELECT OKC_REP_CONTRACTS_ALL_S2.NEXTVAL
FROM DUAL;
SELECT OKC_REP_CONTRACTS_ALL_S3.NEXTVAL
FROM DUAL;
SELECT OKC_REP_CONTRACTS_ALL_S4.NEXTVAL
FROM DUAL;
SELECT OKC_REP_CONTRACTS_ALL_S5.NEXTVAL
FROM DUAL;
SELECT CONTRACT_NUMBER
FROM OKC_REP_CONTRACTS_ALL
WHERE UPPER(CONTRACT_NUMBER) = UPPER(p_contract_number)
AND UPPER(CONTRACT_TYPE) = UPPER(p_contract_type)
AND org_id= p_org_id
UNION
SELECT CONTRACT_NUMBER
FROM OKC_REP_IMP_CONTRACTS_T
WHERE UPPER(CONTRACT_NUMBER) = UPPER(p_contract_number)
AND UPPER(CONTRACT_TYPE_TXT) = UPPER(p_contract_type_txt)
AND Upper(ORG_NAME)= p_org_name
AND IMP_CONTRACT_ID <> p_imp_contract_id
--fix issue#7 in bug 4107212, add the following where clause
AND VALID_FLAG <> 'N';
SELECT IMP_CONTRACT_ID,
CONTRACT_NUMBER,
CONTRACT_NAME,
DESCRIPTION,
VERSION_COMMENTS,
CONTRACT_EFFECTIVE_DATE,
CONTRACT_EXPIRATION_DATE,
CURRENCY_CODE,
CONTRACT_AMOUNT,
ORG_NAME,
OWNER_USER_NAME,
PHYSICAL_LOCATION,
KEYWORDS,
CONTRACT_TYPE_TXT,
AUTHORING_PARTY_TXT,
CONTRACT_STATUS_TXT,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
overall_risk_code,
overall_risk_txt,
cancellation_comments,
cancellation_date,
termination_comments,
termination_date,
expire_ntf_flag,
expire_ntf_period,
notify_contact_role_id,
notify_contact_role_txt,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
reference_document_type,
reference_document_number,
reference_document_id,
run_id,
source_code
FROM
OKC_REP_IMP_CONTRACTS_T
WHERE
run_id = p_run_id
AND Nvl(VALID_FLAG, 'U') IN ('U', 'Y');
SELECT LOOKUP_CODE FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKC_REP_CONTRACT_STATUSES'
AND LOOKUP_CODE IN ('SIGNED')
AND UPPER(MEANING) = UPPER(p_status_txt);
SELECT LOOKUP_CODE FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKC_REP_CONTRACT_STATUSES'
AND LOOKUP_CODE IN ('SIGNED', 'DRAFT', 'TERMINATED')
AND UPPER(MEANING) = UPPER(p_status_txt);
SELECT DOCUMENT_TYPE
FROM OKC_BUS_DOC_TYPES_V
WHERE DOCUMENT_TYPE_CLASS = 'REPOSITORY'
AND UPPER(NAME) = UPPER(p_type_txt);
SELECT INTENT
FROM OKC_BUS_DOC_TYPES_V bus_doc,
OKC_REP_IMP_CONTRACTS_T temp
WHERE bus_doc.name = temp.contract_type_txt
AND temp.imp_contract_id = p_imp_contract_id
AND bus_doc.document_type_class = 'REPOSITORY';
SELECT ORGANIZATION_ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE UPPER(NAME) = UPPER(p_org_name)
AND mo_global.check_access(ORGANIZATION_ID) = 'Y';
SELECT FND_USER.USER_ID
FROM FND_USER,
PER_PEOPLE_F
WHERE FND_USER.EMPLOYEE_ID = PER_PEOPLE_F.PERSON_ID
AND UPPER(FND_USER.USER_NAME) = UPPER(p_owner_name)
AND SYSDATE >= EFFECTIVE_START_DATE
AND SYSDATE <= EFFECTIVE_END_DATE;
SELECT CURRENCY_CODE
FROM FND_CURRENCIES
WHERE UPPER(CURRENCY_CODE) = UPPER(p_currency_code)
AND ENABLED_FLAG = 'Y';
SELECT LOOKUP_CODE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKC_AUTHORING_PARTY'
AND UPPER(MEANING) = UPPER(p_authoring_party_txt);
SELECT PARTY_ROLE_CODE, PARTY_NAME_TXT
FROM OKC_REP_IMP_PARTIES_T
WHERE IMP_CONTRACT_ID = p_contract_id;
SELECT VALID_FLAG, IMP_PARTY_ID
FROM OKC_REP_IMP_PARTIES_T
WHERE IMP_CONTRACT_ID = p_contract_id;
SELECT VALID_FLAG, IMP_DOCUMENT_ID
FROM OKC_REP_IMP_DOCUMENTS_T
WHERE IMP_CONTRACT_ID = p_contract_id;
SELECT LOOKUP_CODE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'OKC_RISK_LEVELS'
AND UPPER(MEANING) = UPPER(p_overall_risk_txt);
SELECT contact_role_id
FROM okc_rep_contact_roles_tl
WHERE Upper(name) = Upper(p_notify_contact_role_txt)
AND LANGUAGE = userenv('LANG');
select auto_num_enabled_yn from okc_bus_doc_types_b where document_type = p_doc_type ;
UPDATE OKC_REP_IMP_PARTIES_T
SET CONTRACT_ID = l_contract_id
WHERE IMP_CONTRACT_ID = l_import_contracts_rec.imp_contract_id;
UPDATE OKC_REP_IMP_DOCUMENTS_T
SET CONTRACT_ID = l_contract_id
WHERE IMP_CONTRACT_ID = l_import_contracts_rec.imp_contract_id;
UPDATE OKC_REP_IMP_CONTRACTS_T
SET
CONTRACT_ID = l_contract_id,
CONTRACT_NUMBER = l_contract_number,
CONTRACT_STATUS_CODE = l_status_code,
CONTRACT_TYPE = l_contract_type,
AUTHORING_PARTY_CODE = l_authoring_party_code,
ORG_ID = l_org_id,
OWNER_USER_ID = l_owner_user_id,
--CONTRACT_EFFECTIVE_DATE = l_effective_date,
--CONTRACT_EXPIRATION_DATE = l_expiration_date,
CONTRACT_AMOUNT = l_amount,
notify_contact_role_id = l_notify_contact_role_id,
overall_risk_code = l_overall_risk_code,
latest_signed_ver_number = Decode(l_status_code, 'SIGNED', 1),
VALID_FLAG = l_valid_flag
WHERE IMP_CONTRACT_ID = l_import_contracts_rec.imp_contract_id;
UPDATE okc_rep_imp_risks_t
SET
business_document_id = l_contract_id
WHERE IMP_CONTRACT_ID = l_import_contracts_rec.imp_contract_id;
UPDATE OKC_REP_IMP_CONTRACTS_T
SET
VALID_FLAG = l_valid_flag
WHERE IMP_CONTRACT_ID = l_import_contracts_rec.imp_contract_id;
PROCEDURE insert_prod_data (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_run_id IN NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_number_inserted OUT NOCOPY NUMBER)
IS
l_api_name CONSTANT VARCHAR2(30):='insert_prod_data';
l_number_inserted NUMBER;
l_insert_batch_size NUMBER;
CURSOR number_inserted_csr IS
SELECT COUNT(contract_id)
FROM okc_rep_contracts_all
WHERE run_id = p_run_id;
SELECT
MIN(contract_id) AS min_contract_id,
MAX(contract_id) AS max_contract_id,
COUNT(contract_id)
FROM okc_rep_imp_contracts_t
WHERE run_id = p_run_id
AND valid_flag = 'Y';
FND_FILE.PUT_LINE(FND_FILE.LOG, '***** BEGIN insert_prod_data *****');
l_insert_batch_size := 50;
l_number_inserted := 0;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_insert_batch_size = ' || l_insert_batch_size);
'Inserting contracts into production table...');
SAVEPOINT before_insert_contracts;
FND_FILE.PUT_LINE(FND_FILE.LOG, '***** SAVEPOINT before_insert_contracts *****');
INSERT INTO okc_rep_contracts_all
(contract_id,
contract_version_num,
contract_name,
contract_number,
contract_desc,
contract_type,
contract_status_code,
version_comments,
org_id,
authoring_party_code,
owner_id,
contract_effective_date,
contract_expiration_date,
currency_code,
amount,
keywords,
physical_location,
source_language,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id,
latest_signed_ver_number,
orig_system_reference_code,
orig_system_reference_id1,
orig_system_reference_id2,
overall_risk_code,
cancellation_comments,
cancellation_date,
termination_comments,
termination_date,
expire_ntf_flag,
expire_ntf_period,
notify_contact_role_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
reference_document_type,
reference_document_number,
reference_document_id,
run_id,
source_code
)
SELECT
contract_id,
1, --contract_version_num,
contract_name,
contract_number,
description,
contract_type,
contract_status_code,
version_comments,
org_id,
authoring_party_code,
owner_user_id,
TO_DATE(contract_effective_date, G_IMP_DATE_FORMAT),
TO_DATE(contract_expiration_date, G_IMP_DATE_FORMAT),
currency_code,
contract_amount,
keywords,
physical_location,
USERENV('LANG'),--source_language,
1, --object_version_number,
FND_GLOBAL.USER_ID, --created_by,
SYSDATE, --creation_date,
FND_GLOBAL.USER_ID, --last_updated_by,
SYSDATE, --last_update_date,
FND_GLOBAL.USER_ID, --last_update_login,
program_id,
program_login_id,
program_application_id,
request_id,
latest_signed_ver_number,
orig_system_reference_code,
orig_system_reference_id1,
orig_system_reference_id2,
overall_risk_code,
cancellation_comments,
cancellation_date,
termination_comments,
termination_date,
expire_ntf_flag,
expire_ntf_period,
notify_contact_role_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
reference_document_type,
reference_document_number,
reference_document_id,
run_id,
source_code
FROM okc_rep_imp_contracts_t
WHERE run_id = p_run_id
AND valid_flag = 'Y'
AND contract_id >= l_start_contract_id
AND contract_id < l_start_contract_id + l_insert_batch_size;
FND_FILE.PUT_LINE(FND_FILE.LOG, '***** INSERT INTO okc_rep_contracts_all *****');
INSERT INTO okc_rep_contract_parties
(
contract_id,
party_role_code,
party_id,
--signed_by,
--signed_date,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
SELECT
contract_id,
party_role_code,
party_id,
--signed_by_txt,
--TO_DATE(signed_date, G_IMP_DATE_FORMAT),
1, --object_version_number,
FND_GLOBAL.USER_ID, --CREATED_BY,
SYSDATE, --CREATION_DATE,
FND_GLOBAL.USER_ID, --LAST_UPDATED_BY,
SYSDATE, --LAST_UPDATE_DATE,
FND_GLOBAL.USER_ID, --LAST_UPDATE_LOGIN,
program_id,
program_login_id,
program_application_id,
request_id
FROM okc_rep_imp_parties_t
WHERE valid_flag = 'Y'
AND run_id = p_run_id
AND contract_id IS NOT NULL
AND contract_id >= l_start_contract_id
AND contract_id < l_start_contract_id + l_insert_batch_size;
FND_FILE.PUT_LINE(FND_FILE.LOG, '***** INSERT INTO okc_rep_contract_parties *****');
INSERT INTO OKC_REP_SIGNATURE_DETAILS
(
contract_id,
contract_version_num,
party_role_code,
party_id,
signed_by,
signed_date,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
SELECT
sig.contract_id,
1, -- contract_version_num
sig.party_role_code,
sig.party_id,
sig.signed_by_txt,
TO_DATE(sig.signed_date, g_imp_date_format),
1, --object_version_number,
FND_GLOBAL.USER_ID, --created_by,
SYSDATE, --creation_date,
FND_GLOBAL.USER_ID, --last_updated_by,
SYSDATE, --last_update_date,
FND_GLOBAL.USER_ID, --last_update_login,
sig.program_id,
sig.program_login_id,
sig.program_application_id,
sig.request_id
FROM okc_rep_imp_parties_t sig, okc_rep_imp_contracts_t con
WHERE sig.valid_flag = 'Y'
AND sig.run_id = p_run_id
AND sig.contract_id IS NOT NULL
AND con.contract_status_code = 'SIGNED'
AND sig.contract_id >= l_start_contract_id
AND sig.contract_id < l_start_contract_id + l_insert_batch_size
AND sig.contract_id = con.contract_id;
FND_FILE.PUT_LINE(FND_FILE.LOG, '***** INSERT INTO OKC_REP_SIGNATURE_DETAILS *****');
INSERT INTO okc_rep_con_status_hist
(
contract_id,
contract_version_num,
status_code,
status_change_date,
changed_by_user_id,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT
contract_id,
contract_version_num,
contract_status_code,
SYSDATE,
FND_GLOBAL.USER_ID,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
FROM okc_rep_contracts_all
WHERE run_id = p_run_id
AND created_by = FND_GLOBAL.USER_ID
AND contract_id >= l_start_contract_id
AND contract_id < l_start_contract_id + l_insert_batch_size;
FND_FILE.PUT_LINE(FND_FILE.LOG, '***** INSERT INTO okc_rep_con_status_hist *****');
INSERT INTO okc_rep_party_contacts
( contract_id,
party_id,
party_role_code,
contact_id,
contact_role_id,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
SELECT
contract_id,
party_id,
party_role_code,
contact_id,
contact_role_id,
1, --object_version_number,
FND_GLOBAL.USER_ID, --CREATED_BY,
SYSDATE, --CREATION_DATE,
FND_GLOBAL.USER_ID, --LAST_UPDATED_BY,
SYSDATE, --LAST_UPDATE_DATE,
FND_GLOBAL.USER_ID, --LAST_UPDATE_LOGIN,
program_id,
program_login_id,
program_application_id,
request_id
FROM okc_rep_imp_contacts_t
WHERE run_id = p_run_id
AND valid_flag = 'Y'
--AND created_by = FND_GLOBAL.USER_ID
AND contract_id >= l_start_contract_id
AND contract_id < l_start_contract_id + l_insert_batch_size;
FND_FILE.PUT_LINE(FND_FILE.LOG, '***** INSERT INTO okc_rep_party_contacts *****');
INSERT INTO okc_contract_risks
(business_document_type,
business_document_id,
business_document_version,
risk_event_id,
probability_code,
impact_code,
comments,
risk_occurred_flag,
occurrence_date,
object_version_number,
-- created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id
)
SELECT
business_document_type,
business_document_id,
business_document_version,
risk_event_id,
probability_code,
risk_impact_code,
risk_comments,
risk_occured_YN,
risk_occurence_date,
1,
--created_by,
SYSDATE, --CREATION_DATE,
FND_GLOBAL.USER_ID, --LAST_UPDATED_BY,
SYSDATE, --LAST_UPDATE_DATE,
FND_GLOBAL.USER_ID, --LAST_UPDATE_LOGIN,
program_id,
program_login_id,
program_application_id,
request_id
FROM okc_rep_imp_risks_t
WHERE run_id = p_run_id
AND valid_flag = 'Y'
--AND created_by = FND_GLOBAL.USER_ID
AND business_document_id >= l_start_contract_id
AND business_document_id < l_start_contract_id + l_insert_batch_size;
FND_FILE.PUT_LINE(FND_FILE.LOG, '***** INSERT INTO okc_contract_risks *****');
l_start_contract_id := l_start_contract_id + l_insert_batch_size;
x_number_inserted := 0;
ROLLBACK TO before_insert_contracts;
FND_FILE.PUT_LINE(FND_FILE.LOG, '***** ROLLBACK TO before_insert_contracts *****');
UPDATE okc_rep_imp_documents_t
SET valid_flag = 'N'
WHERE run_id = p_run_id
AND contract_id >= l_start_contract_id
AND contract_id < l_start_contract_id + l_insert_batch_size;
FND_FILE.PUT_LINE(FND_FILE.LOG, '***** UPDATE okc_rep_imp_documents_t SET valid_flag = N *****');
OPEN number_inserted_csr;
FETCH number_inserted_csr INTO l_number_inserted;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'number_inserted_csr%ROWCOUNT = ' || number_inserted_csr%ROWCOUNT);
CLOSE number_inserted_csr;
x_number_inserted := l_number_inserted;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'x_number_inserted = ' || x_number_inserted);
FND_FILE.PUT_LINE(FND_FILE.LOG, '***** END insert_prod_data *****');
IF (number_inserted_csr%ISOPEN) THEN
CLOSE number_inserted_csr ;
x_number_inserted := 0;
IF (number_inserted_csr%ISOPEN) THEN
CLOSE number_inserted_csr ;
x_number_inserted := 0;
IF (number_inserted_csr%ISOPEN) THEN
CLOSE number_inserted_csr ;
x_number_inserted := 0;
END insert_prod_data;
PROCEDURE validate_and_insert_contracts(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_run_id IN NUMBER,
p_call_source IN VARCHAR2 DEFAULT NULL,
p_validate_only IN VARCHAR2 DEFAULT 'N',
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_number_inserted OUT NOCOPY NUMBER)
IS
l_api_name CONSTANT VARCHAR2(30):='validate_contracts';
'Finished validating Contracts. Now we will insert valid headers and parties into production tables.');
insert_prod_data(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
p_run_id => p_run_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_number_inserted => x_number_inserted);
END validate_and_insert_contracts;
PROCEDURE delete_import_contract(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_contract_id IN NUMBER,
p_imp_document_id IN NUMBER,
p_error_msg_txt IN VARCHAR2,
p_program_id IN NUMBER,
p_program_login_id IN NUMBER,
p_program_app_id IN NUMBER,
p_run_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name VARCHAR2(30);
l_api_name := 'delete_import_contract';
'Entered OKC_REP_UTIL_PVT.delete_import_contract');
okc_rep_contract_process_pvt.delete_contract(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_contract_id => p_contract_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
'Called okc_rep_contract_process_pvt.delete_contract');
UPDATE OKC_REP_IMP_CONTRACTS_T
SET valid_flag = 'N'
WHERE CONTRACT_ID = p_contract_id;
'Updated valid_flag to N');
'Inserted error into okc_rep_imp_errors_t');
'Leaving OKC_REP_UTIL_PVT.delete_import_contract');
'Leaving delete_import_contract:FND_API.G_EXC_ERROR Exception');
'Leaving delete_import_contract:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
'Leaving delete_import_contract because of EXCEPTION: ' || sqlerrm);
END delete_import_contract;
SELECT CONTRACT_NUMBER,
CONTRACT_NAME,
CONTRACT_STATUS_TXT,
CONTRACT_TYPE_TXT,
CONTRACT_EFFECTIVE_DATE,
CONTRACT_EXPIRATION_DATE,
ORG_NAME,
OWNER_USER_NAME,
CURRENCY_CODE,
CONTRACT_AMOUNT,
AUTHORING_PARTY_TXT,
PHYSICAL_LOCATION,
KEYWORDS,
DESCRIPTION,
VERSION_COMMENTS,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2
FROM OKC_REP_IMP_CONTRACTS_T
WHERE IMP_CONTRACT_ID = p_imp_contract_id;
SELECT
PARTY_INDEX,
PARTY_NAME_TXT,
PARTY_ROLE_TXT,
SIGNED_BY_TXT,
SIGNED_DATE
FROM OKC_REP_IMP_PARTIES_T
WHERE IMP_CONTRACT_ID = p_imp_contract_id
ORDER BY PARTY_INDEX;
SELECT
DOCUMENT_INDEX,
FILE_NAME,
CATEGORY_NAME_TXT,
DOCUMENT_DESC
FROM OKC_REP_IMP_DOCUMENTS_T
WHERE IMP_CONTRACT_ID = p_imp_contract_id
ORDER BY DOCUMENT_INDEX;
SELECT
ERROR_MESSAGE
FROM OKC_REP_IMP_ERRORS_T
WHERE IMP_CONTRACT_ID = p_imp_contract_id;
select meaning from fnd_lookup_values
where lookup_type = 'OKC_REP_IMP_TEMPL_ATTRIBUTES'
and LANGUAGE = userenv('LANG')
and VIEW_APPLICATION_ID = 0
and SECURITY_GROUP_ID = fnd_global.lookup_security_group('OKC_REP_IMP_TEMPL_ATTRIBUTES', VIEW_APPLICATION_ID)
and enabled_flag = 'Y'
order by to_number(tag);
SELECT contact_id
FROM OKC_REP_PARTY_CONTACTS
WHERE contract_id = p_document_id
AND party_id = p_external_party_id
AND party_role_code = G_PARTY_ROLE_SUPPLIER;
SELECT email_address
FROM po_vendor_contacts pvc
WHERE pvc.vendor_contact_id = l_contact_id;
SELECT contact_id
FROM OKC_REP_PARTY_CONTACTS
WHERE contract_id = p_document_id
AND party_id = p_external_party_id
AND party_role_code = p_external_party_role;
SELECT email_address
FROM hz_contact_points cp
WHERE cp.owner_table_id = l_contact_id
AND cp.owner_table_name='HZ_PARTIES'
AND cp.contact_point_type = 'EMAIL';
SELECT contract_id
FROM okc_rep_contracts_all
WHERE contract_id=p_doc_id;
SELECT contract_id
FROM okc_rep_contracts_all
WHERE contract_id=p_doc_id
AND contract_status_code in (G_STATUS_REJECTED, G_STATUS_DRAFT);
DELETE FROM okc_rep_recent_contracts c
WHERE c.last_visited_date < SYSDATE - p_num_days;
FUNCTION can_update RETURN VARCHAR2
IS
l_api_name VARCHAR2(10);
l_can_update VARCHAR2(1);
l_api_name := 'can_update';
l_can_update := 'N';
'Entered Function OKC_REP_UTIL_PVT.can_update');
l_can_update := 'Y';
'OKC_REP_UTIL_PVT.check_contract_access returns l_can_update as : '
|| l_can_update);
'Leaving Function l_can_update');
RETURN l_can_update;
'Leaving Function can_update because of EXCEPTION: '||sqlerrm);
RETURN l_can_update ;
END can_update;
PROCEDURE insert_new_vendor_contact(
p_vendor_site_id IN NUMBER,
p_contract_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_area_code IN VARCHAR2,
p_phone IN VARCHAR2,
p_email_address IN VARCHAR2,
x_vendor_contact_id OUT NOCOPY NUMBER)
IS
l_api_name VARCHAR2(32);
SELECT org_id
FROM okc_rep_contracts_all
WHERE contract_id = p_contract_id;
SELECT party_id
FROM OKC_REP_CONTRACT_PARTIES
WHERE contract_id=okc_contract_id AND
party_location_id=party_rep_location_id;
l_api_name := 'insert_new_vendor_contact';
'Entering OKC_REP_UTIL_PVT.insert_new_vendor_contact');
'Leaving OKC_REP_UTIL_PVT.insert_new_vendor_contact');
'Leaving insert_new_vendor_contact because of EXCEPTION: ' || SQLERRM);
END insert_new_vendor_contact;
SELECT contract_desc,
use_acl_flag,
expire_ntf_flag,
expire_ntf_period,
notify_contact_role_id
FROM okc_rep_contracts_all
WHERE contract_id = p_contract_id;
UPDATE okc_rep_contract_vers
SET contract_desc = l_desc,
use_acl_flag = l_use_acl_flag,
expire_ntf_flag = l_expire_ntf_flag,
expire_ntf_period = l_expire_ntf_period,
notify_contact_role_id = l_ntf_contact_role_id
WHERE contract_id = p_contract_id;
select status, archived_yn into l_status_code, l_archived_yn
from OKC_REP_DOC_VERSIONS_V
where document_id = p_contract_id
and document_version = p_version_number;
l_access := G_SALES_QUOTE_UPDATE_ACCESS;
SELECT Nvl((SELECT 'Y' FROM okc_template_usages WHERE document_type = p_contract_type AND ROWNUM =1),'N') INTO x_disable_contract_terms_yn FROM dual;
SELECT Nvl((SELECT 'Y' FROM okc_rep_contracts_all WHERE contract_type = p_contract_type AND ROWNUM =1),'N') INTO x_disable_subentities_yn FROM dual;
SELECT IMP_CONTACT_ID,
IMP_PARTY_ID,
IMP_CONTRACT_ID,
contact_id,
CONTACT_INDEX,
PARTY_NAME_TXT,
PARTY_ROLE_TXT,
contact_name,
contact_role_txt,
VALID_FLAG,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
RUN_ID
FROM okc_rep_imp_contacts_t
WHERE RUN_ID = p_run_id
AND Nvl(VALID_FLAG,'U') IN ('U', 'Y');
SELECT party_id, party_role_code
FROM okc_rep_imp_parties_t
WHERE Upper(party_role_txt) = Upper(p_party_role_txt)
AND Upper(party_name_txt) = Upper(p_party_name_txt)
AND imp_contract_id = p_imp_contract_id;
SELECT b.contact_role_id
FROM okc_rep_contact_roles_b b,
okc_rep_contact_roles_tl tl
WHERE Upper(tl.name) = Upper(p_contact_role_txt)
AND b.contact_role_id = tl.contact_role_id;
UPDATE okc_rep_imp_contacts_t
SET
PARTY_ID = l_party_id,
PARTY_ROLE_CODE = l_party_role_code,
contact_role_id = l_contact_role_id,
VALID_FLAG = l_valid_flag
WHERE IMP_CONTACT_ID = l_import_contacts_rec.imp_contact_id;
UPDATE okc_rep_imp_contacts_t
SET
VALID_FLAG = l_valid_flag
WHERE IMP_CONTACT_ID = l_import_contacts_rec.imp_contact_id;
SELECT IMP_CONTRACT_ID,
risk_index,
risk_event_txt,
probability_txt,
risk_impact_txt,
risk_comments,
risk_occured_YN,
risk_occurence_date,
--created_by
VALID_FLAG,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
RUN_ID
FROM okc_rep_imp_risks_t
WHERE RUN_ID = p_run_id
AND Nvl(VALID_FLAG,'U') IN ('U', 'Y');
SELECT RISK_EVENT_ID
FROM okc_risk_events_tl
WHERE Upper(name) = Upper(p_risk_event_txt)
AND LANGUAGE = userenv('LANG');
SELECT lookup_code FROM fnd_lookups
WHERE lookup_type = 'OKC_RISK_LEVELS'
AND Upper(meaning) = Upper(p_risk_txt);
SELECT contract_id, contract_type, contract_version_num
FROM OKC_REP_IMP_CONTRACTS_T
WHERE imp_contract_id = p_imp_contract_id;
UPDATE okc_rep_imp_risks_t
SET
business_document_type = l_business_document_type,
business_document_id = l_business_document_id,
business_document_version = l_business_document_version,
risk_event_id = l_risk_event_id,
probability_code = l_risk_prob_code,
risk_impact_code = l_risk_impact_code,
VALID_FLAG = l_valid_flag
WHERE IMP_CONTRACT_ID = l_import_risks_rec.imp_contract_id
AND risk_event_txt = l_import_risks_rec.risk_event_txt;
UPDATE okc_rep_imp_risks_t
SET
VALID_FLAG = l_valid_flag
WHERE IMP_CONTRACT_ID = l_import_risks_rec.imp_contract_id
AND risk_event_txt = l_import_risks_rec.risk_event_txt;