[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT quote_to_email_id
FROM oks_k_headers_b
WHERE chr_id = p_chr_id;
SELECT email_address
FROM okx_contact_points_v
WHERE contact_point_id = p_contactpoint_id;
SELECT r.object1_id1 AS party_id
FROM okc_k_party_roles_b r,
hz_parties p
WHERE p.party_id = r.object1_id1
AND r.jtot_object1_code = 'OKX_PARTY'
AND r.rle_code IN
('CUSTOMER', 'SUBSCRIBER')
-- gets only the CUSTOMER /SUBSCRIBER
AND r.cle_id IS NULL
AND r.chr_id = p_chr_id;
SELECT distinct hp.party_id
FROM okc_k_party_roles_b okpr,
hz_parties hp,
--NPALEPU
--08-AUG-2005
--TCA Project
--Replaced hz_party_relationships table with hz_relationships table
-- hz_party_relationships hpr --
hz_relationships hpr
--END NPALEPU
WHERE okpr.rle_code = 'CUSTOMER'
AND jtot_object1_code = 'OKX_PARTY'
AND okpr.object1_id1 = hp.party_id
AND hpr.object_id = hp.party_id
AND okpr.dnz_chr_id = p_chr_id;
SELECT hzr.party_id
FROM oks_k_headers_b kh,
--NPALEPU
--08-AUG-2005
--TCA Project
--Replaced hz_party_relationships table with hz_relationships table and ra_hcontacts with OKS_RA_HCONTACTS_V
--Replaced hzr.party_relationship_id column with hzr.relationship_id column and added new conditions
--ra_hcontacts rah,
hz_party_relationships hzr
WHERE kh.chr_id = p_chr_id
AND kh.quote_to_contact_id = rah.contact_id
AND rah.party_relationship_id = hzr.party_relationship_id;
SELECT ks.person_party_id,
ks.quote_to_contact_id
FROM oks_k_headers_b ks
WHERE ks.chr_id = p_chr_id;
SELECT hzp.party_id
FROM hz_cust_account_roles car,
hz_relationships rln,
hz_parties hzp
WHERE car.cust_account_role_id = p_quote_to_contact_id
AND car.party_id = rln.party_id
AND rln.subject_id = hzp.party_id
AND car.role_type = 'CONTACT'
AND rln.directional_flag = 'F'
AND rln.content_source_type = 'USER_ENTERED';
UPDATE fnd_user record with person_party_id from step 1 above
If person_party_id in fnd_user is NOT the same as person_party_id from step 1 above
RAISE error here
If person_party_id in fnd_user = person_party_id from step 1 above
we are fine, do nothing
Case B: Record NOT found in fnd_user
Call FND_USER_PKG.TestUserName
--@ TestUserName() returns:
--@ USER_OK_CREATE constant pls_integer := 0;
FND_USER_RESP_GROUPS_API.insert_assignment with responsibility as 'OKS_ERN_WEB'
ELSE -- l_test_user <> 0 ,3
-- error, raise exception
RAISE FND_API.G_EXC_ERROR;
SELECT user_id,
person_party_id
FROM fnd_user
WHERE UPPER (user_name) = p_user_name
AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
AND NVL (end_date, SYSDATE);
SELECT user_id
FROM fnd_user_resp_groups
WHERE user_id = p_user_id
AND responsibility_id = p_responsibility_id
AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
AND NVL (end_date, SYSDATE);
SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = p_resp_key
AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
AND NVL (end_date, SYSDATE);
SELECT security_group_id
FROM fnd_security_groups
WHERE security_group_key = p_security_grp_key;
SELECT CONCAT
(DBMS_RANDOM.STRING
('l',
(NVL
(fnd_profile.VALUE ('SIGNON_PASSWORD_LENGTH'),
7) -
3
)),
ROUND (DBMS_RANDOM.VALUE (100, 999)))
FROM DUAL;
SELECT hzp.party_id
FROM hz_cust_account_roles car,
hz_relationships rln,
hz_parties hzp,
oks_k_headers_b ks
WHERE ks.quote_to_contact_id = car.cust_account_role_id
AND car.party_id = rln.party_id
AND rln.subject_id = hzp.party_id
AND car.role_type = 'CONTACT'
AND rln.directional_flag = 'F'
AND rln.content_source_type = 'USER_ENTERED'
AND ks.chr_id = p_contract_id;
fnd_user_resp_groups_api.insert_assignment
(user_id => l_user_id,
responsibility_id => l_responsibility_id,
responsibility_application_id => 515,
security_group_id => l_security_grp_id,
description => 'Electronic renewals User',
start_date => SYSDATE,
end_date => NULL
);
fnd_user_pkg.updateuserparty
(x_user_name => UPPER
(TRIM
(p_user_name)),
x_owner => 'CUST',
x_person_party_id => l_qtc_person_party_id
);
PROCEDURE update_contract_status (
p_chr_id IN VARCHAR2,
p_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30)
:= 'update_contract_status';
SELECT s.ste_code
FROM okc_k_headers_all_b k,
okc_statuses_b s
WHERE k.sts_code = s.code
AND k.id = p_chr_id;
oks_auto_reminder.update_contract_status
(p_chr_id => TO_NUMBER
(p_chr_id),
p_status => p_status,
x_return_status => x_return_status
);
PROCEDURE update_contract_status (
p_chr_id IN NUMBER,
p_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
:= 'update_contract_status';
SELECT ID,
sts_code
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id;
IS SELECT ID,
sts_code
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND (DATE_TERMINATED IS NULL AND DATE_CANCELLED IS NULL);
SELECT STS_CODE
FROM OKC_K_HEADERS_ALL_B okck,
OKC_STATUSES_B sts
WHERE id = p_chr_id
AND sts.code = okck.sts_code;
SELECT STE_CODE INTO lcode FROM okc_statuses_b WHERE code = pcode;
'OKC_CONTRACT_PUB.update_contract_header(p_chr_id=' ||
p_chr_id ||
')'
);
okc_contract_pub.update_contract_header
(p_api_version => l_api_version,
p_init_msg_list => okc_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => okc_api.g_true,
p_chrv_rec => l_chrv_rec,
x_chrv_rec => x_chrv_rec
);
OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS (
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
p_init_msg_list => G_FALSE,
p_id => p_chr_id,
p_new_sts_code => p_status,
p_old_sts_code => l_old_sts,
p_canc_reason_code => 'NFC',
p_comments => 'Automatic Cancellation of Contract',
p_term_cancel_source => 'CUSTOMER',
p_date_cancelled => SYSDATE,
p_validate_status => 'N') ;
UPDATE oks_k_headers_b
SET renewal_status = 'QUOTE_CNCLD'
WHERE chr_id = p_chr_id;
okc_contract_pub.update_contract_header
(p_api_version => l_api_version,
p_init_msg_list => okc_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => okc_api.g_false,
p_chrv_rec => l_chrv_rec,
x_chrv_rec => x_chrv_rec
);
'OKC_CONTRACT_PUB.update_contract_header(x_return_status= ' ||
l_return_status ||
' l_msg_count =' ||
l_msg_count ||
')'
);
'OKC_CONTRACT_PUB.update_contract_line - Loop begin'
);
okc_contract_pub.update_contract_line
(p_api_version => l_api_version,
p_init_msg_list => okc_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => okc_api.g_false,
p_clev_rec => l_clev_rec,
x_clev_rec => x_clev_rec
);
cancellation is taken care at header level by OKS_CHANGE_STATUS_PVT.UPDATE_HEADER_STATUS api
l_clev_rec.date_cancelled := SYSDATE;
okc_contract_pub.update_contract_line
(p_api_version => l_api_version,
p_init_msg_list => okc_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => okc_api.g_true,
p_clev_rec => l_clev_rec,
x_clev_rec => x_clev_rec
);
okc_contract_pub.update_contract_line
(p_api_version => l_api_version,
p_init_msg_list => okc_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_restricted_update => okc_api.g_false,
p_clev_rec => l_clev_rec,
x_clev_rec => x_clev_rec
);
'OKC_CONTRACT_PUB.update_contract_line - loop end'
);
END update_contract_status;
PROCEDURE update_renewal_status (
p_chr_id IN NUMBER,
p_renewal_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30)
:= 'update_renewal_status';
oks_contract_hdr_pub.update_header (p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_khrv_rec => l_khdr_rec_in,
x_khrv_rec => l_khdr_rec_out,
p_validate_yn => 'N'
);
SELECT mtl.NAME
FROM okx_system_items_v mtl,
okx_cust_prod_v cp
WHERE mtl.id1 = cp.inventory_item_id
AND mtl.organization_id = p_inv_org_id
AND cp.customer_product_id = p_object_id;
SELECT NAME
FROM okx_parties_v
WHERE id1 = p_object_id
AND id2 = '#';
SELECT NAME
FROM okx_systems_v
WHERE id1 = p_object_id
AND id2 = '#';
SELECT pricing_attribute3
FROM cs_customer_products_all
WHERE customer_product_id = p_object_id;
SELECT NAME
FROM okx_system_items_v mtl,
okc_k_items itm
WHERE mtl.id1 = itm.object1_id1
AND mtl.id2 = itm.object1_id2
AND itm.cle_id = p_line_id
AND itm.dnz_chr_id = p_contract_id;
SELECT pricing_attribute5
FROM cs_customer_products_all
WHERE customer_product_id = p_object_id;
SELECT pricing_attribute4
FROM cs_customer_products_all
WHERE customer_product_id = p_object_id;
SELECT object1_id1
FROM okc_contacts ct
WHERE ct.cro_code = 'BILLING'
AND ct.dnz_chr_id = p_contract_id;
SELECT NAME
FROM okx_party_contacts_v
WHERE id1 = l_id;
SELECT phone_area_code ||
'-' ||
phone_number
FROM okx_contact_points_v
WHERE owner_table_id = p_id
AND owner_table_name = 'HZ_PARTIES'
AND contact_point_type = 'PHONE'
AND phone_number IS NOT NULL
ORDER BY DECODE (phone_line_type,
'GEN', 1,
'OFFICE', 2,
'PHONE', 3,
'Direct Phone', 4,
5
);
SELECT object1_id1
FROM okc_contacts ct
WHERE ct.cro_code = 'BILLING'
AND ct.dnz_chr_id = p_contract_id;
SELECT phone_area_code ||
'-' ||
phone_number
FROM okx_contact_points_v
WHERE owner_table_id = p_id
AND owner_table_name = 'HZ_PARTIES'
AND contact_point_type IN ('PHONE', 'FAX')
AND phone_line_type = 'FAX';
SELECT object1_id1
FROM okc_contacts ct
WHERE ct.cro_code = 'BILLING'
AND ct.dnz_chr_id = p_contract_id;
SELECT hcp.email_address
FROM hz_contact_points hcp,
okc_contacts kc
WHERE hcp.owner_table_id = kc.object1_id1
AND kc.cro_code = 'BILLING'
AND hcp.contact_point_type='EMAIL'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcp.content_source_type = 'USER_ENTERED'
AND hcp.status = 'A'
AND kc.dnz_chr_id= p_contract_id
ORDER BY hcp.primary_flag desc;
SELECT contract_number,
contract_number_modifier
FROM okc_k_headers_all_b
WHERE ID = p_chr_id;
SELECT rsc.resource_id resource_id
FROM jtf_rs_resource_extns rsc,
fnd_user u
WHERE u.user_id = rsc.user_id
AND u.user_id = p_user_id;
SELECT kh.ID,
kh.contract_number,
kh.contract_number_modifier,
kh.start_date,
kh.end_date,
kh.sts_code
FROM okc_k_headers_b kh
WHERE kh.ID = p_chr_id;
SELECT message_template_id,
report_duration,
report_period,
template_set_type,
report_id,
attachment_name,
sts_code
FROM oks_report_templates
WHERE template_set_id = l_template_set
AND ( DECODE (process_code,
'B', 'B',
'X'
) = 'B'
OR process_code = p_process_code
)
AND ( DECODE (applies_to,
'B', 'B',
'X'
) = 'B'
OR applies_to = p_applies_to
)
AND report_period <> l_period
AND report_duration <> 0
AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
AND NVL (end_date, SYSDATE);
SELECT message_template_id,
template_set_type,
report_id,
attachment_name,
sts_code
FROM oks_report_templates
WHERE template_set_id = p_template_set
AND report_duration = p_duration
AND report_period = p_period
AND ( DECODE (process_code,
'B', 'B',
'X'
) = 'B'
OR process_code = p_process_code
)
AND ( DECODE (applies_to,
'B', 'B',
'X'
) = 'B'
OR applies_to = p_applies_to
)
AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
AND NVL (end_date, SYSDATE);
SELECT message_template_id,
template_set_type,
report_id,
attachment_name,
sts_code
FROM oks_report_templates
WHERE template_set_id = p_template_set
AND report_duration = p_duration
AND ( DECODE (process_code,
'B', 'B',
'X'
) = 'B'
OR process_code = p_process_code
)
AND ( DECODE (applies_to,
'B', 'B',
'X'
) = 'B'
OR applies_to = p_applies_to
)
AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
AND NVL (end_date, SYSDATE);
SELECT source_email
FROM jtf_rs_resource_extns
WHERE user_id = p_user_id;
SELECT DECODE (renewal_type_used,NULL,'N','R'), wf_item_key
FROM oks_k_headers_b
WHERE chr_id = p_chr_id;
SELECT template_name
FROM xdo_templates_vl
WHERE template_id=p_attachment_template_id;
select 'Y'
from oks_k_headers_b
where chr_id = p_chr_id
and RENEWAL_TYPE_USED = 'ERN'
and date_accepted is not null;
UPDATE oks_k_headers_b record with the person_party_id for quote to contact id
Step 2: Check if record exists in fnd_user for the above person_party_id (filter expired records here)
-- 1 or more Record Exists
If record found then
take the first hit record and put the user name and password in the email notification
check if this user has the responsibility' Electronic Renewal'
If yes then return
else
add responsibility and return
end if;
FND_USER_RESP_GROUPS_API.insert_assignment with responsibility as 'OKS_ERN_WEB'
ELSE -- l_test_user <> 0 ,3
-- error, raise exception
RAISE FND_API.G_EXC_ERROR;
UPDATE fnd_user record with person_party_id from step 1 above
CASE 2: person_party_id of fnd_user DOES NOT MATCH the person_party_id from step 1 above
(logic introduced as part of bugfix for 58983305)
CASE i: If the value of the profile option OKS Overide SSO Behaviour is Y then
Update the person_party_id of oks_k_headers_b and continue publishing
CASE ii:If the value of the profile option OKS Overide SSO Behaviour is N
RAISE error here
CASE 3: person_party_id of fnd_user MATCHES the person_party_id from step 1 above
we are fine, do nothing
Case Expired
fnd_user exists and is expired , raise exception
*/
PROCEDURE create_sso_user (
p_user_name IN VARCHAR2,
p_contract_id IN NUMBER,
x_user_name OUT NOCOPY VARCHAR2,
x_password OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
)
AS
l_api_name CONSTANT VARCHAR2 (30)
:= 'create_sso_user';
SELECT ks.person_party_id,
ks.quote_to_contact_id
FROM oks_k_headers_b ks
WHERE ks.chr_id = p_contract_id;
SELECT hzp.party_id
FROM hz_cust_account_roles car,
hz_relationships rln,
hz_parties hzp
WHERE car.cust_account_role_id = p_quote_to_contact_id
AND car.party_id = rln.party_id
AND rln.subject_id = hzp.party_id
AND car.role_type = 'CONTACT'
AND rln.directional_flag = 'F'
AND rln.content_source_type = 'USER_ENTERED';
SELECT user_id,
user_name,
encrypted_user_password
FROM fnd_user
WHERE SYSDATE BETWEEN start_date AND NVL (end_date, SYSDATE +
1)
AND person_party_id = p_person_party_id;
SELECT f.person_party_id,
f.start_date,
f.end_date,
f.encrypted_user_password
FROM fnd_user f
WHERE f.user_name = p_user_name;
SELECT responsibility_id
FROM fnd_responsibility
WHERE responsibility_key = p_resp_key
AND SYSDATE BETWEEN NVL (start_date, SYSDATE)
AND NVL (end_date, SYSDATE);
SELECT security_group_id
FROM fnd_security_groups
WHERE security_group_key = p_security_grp_key;
SELECT party_name
FROM hz_parties
WHERE party_id = p_party_id;
SELECT p.party_name , p.party_id
FROM hz_relationships r , hz_parties p
WHERE p.party_id = r.object_id
AND r.subject_type='PERSON'
AND r.object_type='ORGANIZATION'
AND r.subject_id = p_party_id;
UPDATE oks_k_headers_b
SET person_party_id = l_person_party_id,
object_version_number = object_version_number +
1,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE chr_id = p_contract_id;
UPDATE okc_k_vers_numbers
SET minor_version = minor_version +
1,
object_version_number = object_version_number +
1,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE chr_id = p_contract_id;
fnd_user_resp_groups_api.insert_assignment
(user_id => l_user_id,
responsibility_id => l_responsibility_id,
responsibility_application_id => 515,
security_group_id => l_security_grp_id,
description => 'Electronic renewals User',
start_date => SYSDATE,
end_date => NULL
);
fnd_user_resp_groups_api.insert_assignment
(user_id => l_user_id,
responsibility_id => l_responsibility_id,
responsibility_application_id => 515,
security_group_id => l_security_grp_id,
description => 'Electronic renewals User',
start_date => SYSDATE,
end_date => NULL
);
UPDATE fnd_user
SET person_party_id = l_person_party_id
WHERE user_name = UPPER (TRIM (p_user_name));
UPDATE oks_k_headers_b
SET person_party_id =l_fnd_person_party_id
WHERE chr_id=p_contract_id;
fnd_file.put_line (fnd_file.LOG,'OKS Person Party ID updated to:'||l_fnd_person_party_id);