[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_MODE_UPDATE CONSTANT VARCHAR2(10) := 'UPDATE';
SELECT per.full_name
FROM fnd_user fu,
per_all_people_f per
WHERE fu.user_id = p_prev_con_admin_id
AND per.person_id = fu.employee_id;
SELECT name
FROM okc_bus_doc_types_tl
WHERE document_type = p_doc_type
AND language = userenv('LANG');
SELECT meaning
FROM fnd_lookups
WHERE lookup_code = p_doc_type
AND lookup_type = 'OKC_REP_DOC_TYPE_GROUPS';
SELECT party_name
FROM hz_parties
WHERE party_id = p_cust_id;
SELECT name
FROM hr_all_organization_units
WHERE organization_id = p_org_id;
SELECT jtf_res.resource_name
FROM jtf_rs_salesreps s,
jtf_rs_resource_extns_vl jtf_res
WHERE s.salesrep_id = p_salesrep_id
AND s.resource_id = jtf_res.resource_id;
SELECT name
FROM oe_transaction_types_tl
WHERE transaction_type_id = p_order_type_id
AND language = userenv('LANG');
SELECT group_name
FROM jtf_rs_groups_tl
WHERE group_id = p_sales_group_id
AND language = userenv('LANG');
IF (p_mode = G_MODE_UPDATE) THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_UPD_CON_ADMIN_ACT') || ': '|| OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ACT_UPDATE'));
FND_FILE.PUT_LINE(FND_FILE.LOG, OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_UPD_CON_ADMIN_ACT') || ': '|| OKC_TERMS_UTIL_PVT.get_message(G_APP_NAME, 'OKC_REP_SW_ACT_UPDATE'));
PROCEDURE update_con_admin_manager(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_doc_type IN VARCHAR2,
p_cust_id IN NUMBER,
p_prev_con_admin_id IN NUMBER,
p_salesrep_id IN NUMBER,
p_sales_group_id IN NUMBER,
p_org_id IN NUMBER,
p_order_type_id IN NUMBER,
p_new_con_admin_user_id IN NUMBER,
p_new_con_admin_name IN VARCHAR2,
p_mode IN VARCHAR2,
p_con_admin_from IN VARCHAR2
)IS
l_api_name VARCHAR2(30);
CURSOR selected_bsa_csr IS
SELECT header.header_id AS contract_id,
header.order_number AS contract_number,
customer.party_name AS customer,
doc_type.name AS document_type,
hr.name AS operating_unit,
t.contract_admin_id AS contract_admin_id
FROM oe_blanket_headers header,
okc_template_usages t,
hz_parties customer,
hz_cust_accounts hzc,
okc_bus_doc_types_tl doc_type,
hr_all_organization_units_tl hr
WHERE t.document_type = 'B'
AND header.header_id = t.document_id
AND header.sold_to_org_id = hzc.cust_account_id (+)
AND hzc.party_id = customer.party_id (+)
AND (p_cust_id IS NULL OR customer.party_id = p_cust_id)
AND (p_prev_con_admin_id IS NULL OR t.contract_admin_id = p_prev_con_admin_id)
AND (p_salesrep_id IS NULL OR header.salesrep_id = p_salesrep_id)
AND (p_org_id IS NULL OR header.org_id = p_org_id)
AND (p_order_type_id IS NULL OR header.order_type_id = p_order_type_id)
AND doc_type.document_type = t.document_type
AND doc_type.language = USERENV('LANG')
AND hr.organization_id = header.org_id
AND hr.language = USERENV('LANG')
AND (p_new_con_admin_user_id IS NULL OR
t.contract_admin_id is null OR
t.contract_admin_id <> p_new_con_admin_user_id);
CURSOR selected_so_csr IS
SELECT header.header_id AS contract_id,
header.order_number AS contract_number,
customer.party_name customer,
doc_type.name AS document_type,
hr.name AS operating_unit,
t.contract_admin_id AS contract_admin_id
FROM oe_order_headers header,
okc_template_usages t,
hz_parties customer,
hz_cust_accounts hzc,
okc_bus_doc_types_tl doc_type,
hr_all_organization_units_tl hr
WHERE t.document_type = 'O'
AND header.header_id = t.document_id
AND header.sold_to_org_id = hzc.cust_account_id (+)
AND hzc.party_id = customer.party_id (+)
AND (p_cust_id IS NULL OR customer.party_id = p_cust_id)
AND (p_prev_con_admin_id IS NULL OR t.contract_admin_id = p_prev_con_admin_id)
AND (p_salesrep_id IS NULL OR header.salesrep_id = p_salesrep_id)
AND (p_org_id IS NULL OR header.org_id = p_org_id)
AND (p_order_type_id IS NULL OR header.order_type_id = p_order_type_id)
AND doc_type.document_type = t.document_type
AND doc_type.language = USERENV('LANG')
AND hr.organization_id = header.org_id
AND hr.language = USERENV('LANG')
AND (p_new_con_admin_user_id IS NULL OR
t.contract_admin_id is null OR
t.contract_admin_id <> p_new_con_admin_user_id);
CURSOR selected_quote_csr IS
SELECT header.quote_header_id AS contract_id,
header.quote_number AS contract_number,
customer.party_name AS customer,
doc_type.name document_type,
hr.name AS operating_unit,
t.contract_admin_id AS contract_admin_id
FROM aso_quote_headers header,
okc_template_usages t,
hz_parties customer,
okc_bus_doc_types_tl doc_type,
aso_quote_statuses_vl quote_status,
hr_all_organization_units_tl hr
WHERE t.document_type = 'QUOTE'
AND header.quote_header_id = t.document_id
AND header.max_version_flag = 'Y'
AND (p_cust_id IS NULL OR header.cust_party_id = p_cust_id)
AND (p_prev_con_admin_id IS NULL OR t.contract_admin_id = p_prev_con_admin_id)
AND (p_salesrep_id IS NULL OR header.resource_id = (SELECT resource_id
FROM jtf_rs_salesreps
WHERE salesrep_id = p_salesrep_id))
AND (p_org_id IS NULL OR header.org_id = p_org_id)
AND (p_order_type_id IS NULL OR header.order_type_id = p_order_type_id)
AND (p_sales_group_id IS NULL OR header.resource_grp_id = p_sales_group_id)
AND customer.party_id (+) = header.cust_party_id
AND doc_type.document_type = t.document_type
AND doc_type.language = USERENV('LANG')
AND quote_status.quote_status_id = header.quote_status_id
AND quote_status.status_code <> 'ORDER SUBMITTED'
AND hr.organization_id = header.org_id
AND hr.language = USERENV('LANG')
AND (p_new_con_admin_user_id IS NULL OR
t.contract_admin_id is null OR
t.contract_admin_id <> p_new_con_admin_user_id);
CURSOR selected_rep_csr IS
SELECT header.contract_id AS contract_id,
header.contract_number AS contract_number,
doc_type_tl.name AS document_type,
hr.name AS operating_unit,
header.owner_id AS contract_admin_id
FROM okc_rep_contracts header,
Hr_all_organization_units_tl hr,
Okc_bus_doc_types_b doc_type_b,
Okc_bus_doc_types_tl doc_type_tl
WHERE (p_cust_id IS NULL OR EXISTS (
SELECT con_parties.party_id
FROM okc_rep_contract_parties con_parties
WHERE header.contract_id = con_parties.contract_id
AND con_parties.party_id = p_cust_id
AND (con_parties.party_role_code = 'PARTNER_ORG' OR
con_parties.party_role_code = 'CUSTOMER_ORG')))
AND (p_prev_con_admin_id IS NULL OR header.owner_id = p_prev_con_admin_id)
AND (p_org_id IS NULL OR header.org_id = p_org_id)
AND doc_type_b.document_type = header.contract_type
AND doc_type_b.intent IN ('S', 'O')
AND doc_type_tl.document_type = header.contract_type
AND doc_type_tl.language = USERENV('LANG')
AND hr.organization_id = header.org_id
AND hr.language = USERENV('LANG')
AND (p_new_con_admin_user_id IS NULL OR
header.owner_id IS NULL OR
header.owner_id <> p_new_con_admin_user_id);
SELECT hz.party_name
FROM okc_rep_contract_parties con_party,
hz_parties hz
WHERE con_party.contract_id = p_contract_id
AND (con_party.party_role_code = 'CUSTOMER_ORG' OR
con_party.party_role_code = 'PARTNER_ORG')
AND con_party.party_id = hz.party_id;
SELECT nvl(per.full_name, fu.user_name)
FROM fnd_user fu,
per_all_people_f per
WHERE fu.user_id = p_user_id
AND per.person_id = fu.employee_id;
SELECT 1
FROM fnd_user fu,
per_all_people_f per
WHERE fu.user_id = p_con_admin_id
AND per.person_id = fu.employee_id
AND sysdate between per.effective_start_date AND nvl(per.effective_end_date, sysdate);
TYPE selected_bsa_tbl IS TABLE OF selected_bsa_csr%ROWTYPE;
TYPE selected_so_tbl IS TABLE OF selected_so_csr%ROWTYPE;
TYPE selected_quote_tbl IS TABLE OF selected_quote_csr%ROWTYPE;
TYPE selected_rep_tbl IS TABLE OF selected_rep_csr%ROWTYPE;
selected_bsa selected_bsa_tbl;
selected_so selected_so_tbl;
selected_quote selected_quote_tbl;
selected_rep selected_rep_tbl;
l_selected_doc_ids OKC_TERMS_UTIL_PVT.doc_ids_tbl;
l_selected_doc_types OKC_TERMS_UTIL_PVT.doc_types_tbl;
selected_rep_con_ids NumList;
l_api_name := 'update_con_admin_manager';
'Entered OKC_REP_UPD_CON_ADMIN_PVT.update_con_admin_manager');
OPEN selected_bsa_csr;
FETCH selected_bsa_csr BULK COLLECT INTO selected_bsa
LIMIT l_batch_size;
EXIT WHEN selected_bsa.COUNT = 0;
FOR i IN 1..NVL(selected_bsa.LAST, -1) LOOP
-- Increment record index
l_rec_index := l_rec_index + 1;
IF(p_mode = G_MODE_UPDATE) THEN
l_selected_doc_ids(l_doc_index) := selected_bsa(i).contract_id;
l_selected_doc_types(l_doc_index) := G_DOC_TYPE_BSA;
IF (selected_bsa(i).contract_admin_id IS NOT NULL) THEN
-- Get current Contract Administrator name
OPEN con_admin_name_csr(selected_bsa(i).contract_admin_id);
p_con_number => selected_bsa(i).contract_number,
p_cust_name => selected_bsa(i).customer,
p_doc_type_name => selected_bsa(i).document_type,
p_current_con_admin=> l_current_con_admin_name,
p_new_con_admin => NULL,
p_operating_unit => selected_bsa(i).operating_unit,
p_msg_type => l_msg_type,
p_msg_code => l_msg_code,
p_doc_index => l_rec_index);
CLOSE selected_bsa_csr;
OPEN selected_so_csr;
FETCH selected_so_csr BULK COLLECT INTO selected_so
LIMIT l_batch_size;
EXIT WHEN selected_so.COUNT = 0;
FOR i IN 1..NVL(selected_so.LAST, -1) LOOP
-- Increment record index
l_rec_index := l_rec_index + 1;
IF(p_mode = G_MODE_UPDATE) THEN
l_selected_doc_ids(l_doc_index) := selected_so(i).contract_id;
l_selected_doc_types(l_doc_index) := G_DOC_TYPE_SO;
IF (selected_so(i).contract_admin_id IS NOT NULL) THEN
-- Get current Contract Administrator name
OPEN con_admin_name_csr(selected_so(i).contract_admin_id);
p_con_number => selected_so(i).contract_number,
p_cust_name => selected_so(i).customer,
p_doc_type_name => selected_so(i).document_type,
p_current_con_admin=> l_current_con_admin_name,
p_new_con_admin => NULL,
p_operating_unit => selected_so(i).operating_unit,
p_msg_type => l_msg_type,
p_msg_code => l_msg_code,
p_doc_index => l_rec_index );
CLOSE selected_so_csr;
OPEN selected_quote_csr;
FETCH selected_quote_csr BULK COLLECT INTO selected_quote
LIMIT l_batch_size;
EXIT WHEN selected_quote.COUNT = 0;
FOR i IN 1..NVL(selected_quote.LAST, -1) LOOP
l_msg_code := NULL;
p_doc_id => selected_quote(i).contract_id,
p_doc_type => G_DOC_TYPE_QUOTE,
x_new_con_admin_user_id => l_new_con_admin_user_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
IF(l_new_con_admin_user_id = selected_quote(i).contract_admin_id) 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,
'Current and the new Contract Administrators are same');
END IF; -- End of l_new_con_admin_user_id = selected_quote(i).contract_admin_id
IF(p_mode = G_MODE_UPDATE) THEN
l_selected_doc_ids(l_doc_index) := selected_quote(i).contract_id;
l_selected_doc_types(l_doc_index) := G_DOC_TYPE_QUOTE;
IF (selected_quote(i).contract_admin_id IS NOT NULL) THEN
-- Get current Contract Administrator name
OPEN con_admin_name_csr(selected_quote(i).contract_admin_id);
p_con_number => selected_quote(i).contract_number,
p_cust_name => selected_quote(i).customer,
p_doc_type_name => selected_quote(i).document_type,
p_current_con_admin=> l_current_con_admin_name,
p_new_con_admin => l_new_con_admin_user_name,
p_operating_unit => selected_quote(i).operating_unit,
p_msg_type => l_msg_type,
p_msg_code => l_msg_code,
p_doc_index => l_rec_index );
CLOSE selected_quote_csr;
IF(p_mode = G_MODE_UPDATE AND
l_selected_doc_ids.LAST > 0) 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,
'Calling OKC_TERMS_UTIL_PVT.update_contract_admin');
OKC_TERMS_UTIL_PVT.update_contract_admin(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_doc_ids_tbl => l_selected_doc_ids,
p_doc_types_tbl => l_selected_doc_types,
p_new_con_admin_user_ids_tbl => l_new_con_admin_user_ids,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_succ_doc_count := l_succ_doc_count + l_selected_doc_ids.COUNT;
OPEN selected_rep_csr;
FETCH selected_rep_csr BULK COLLECT INTO selected_rep
LIMIT l_batch_size;
EXIT WHEN selected_rep.COUNT = 0;
FOR i IN 1..NVL(selected_rep.LAST, -1) LOOP
-- Increment record index
l_rec_index := l_rec_index + 1;
selected_rep_con_ids(i) := selected_rep(i).contract_id;
OPEN cust_names_csr(selected_rep(i).contract_id);
OPEN con_admin_name_csr(selected_rep(i).contract_admin_id);
p_con_number => selected_rep(i).contract_number,
p_cust_name => l_cust_names,
p_doc_type_name => selected_rep(i).document_type,
p_current_con_admin=> l_current_con_admin_name,
p_new_con_admin => NULL,
p_operating_unit => selected_rep(i).operating_unit,
p_msg_type => l_msg_type,
p_msg_code => l_msg_code,
p_doc_index => l_rec_index );
IF (p_mode = G_MODE_UPDATE) 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,
'Updating Latest version of Repository Contract with new Contract Administrator');
FORALL i IN NVL(selected_rep_con_ids.FIRST,0)..NVL(selected_rep_con_ids.LAST,-1)
UPDATE okc_rep_contracts_all
SET owner_id = p_new_con_admin_user_id
WHERE contract_id = selected_rep_con_ids(i);
FORALL i IN NVL(selected_rep_con_ids.FIRST,0)..NVL(selected_rep_con_ids.LAST,-1)
UPDATE okc_rep_contract_vers
SET owner_id = p_new_con_admin_user_id
WHERE contract_id = selected_rep_con_ids(i);
l_succ_doc_count := l_succ_doc_count + selected_rep_con_ids.COUNT;
END IF; -- p_mode = G_MODE_UPDATE
CLOSE selected_rep_csr;
IF(p_mode = 'UPDATE') THEN
COMMIT;
'Leaving OKC_REP_UPD_CON_ADMIN_PVT.update_con_admin_manager');
'Leaving update_con_admin_manager because of EXCEPTION: ' || sqlerrm);
'Leaving update_con_admin_manager because of EXCEPTION: ' ||
sqlerrm);
IF (selected_bsa_csr%ISOPEN) THEN
CLOSE selected_bsa_csr ;
IF (selected_so_csr%ISOPEN) THEN
CLOSE selected_so_csr ;
IF (selected_quote_csr%ISOPEN) THEN
CLOSE selected_quote_csr ;
IF (selected_rep_csr%ISOPEN) THEN
CLOSE selected_rep_csr ;
END update_con_admin_manager;