[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TO_CHAR
(oks_extwar_util_pvt.round_currency_amt
((NVL (ch.estimated_amount, 0) +
NVL (sh.tax_amount, 0)
),
ch.currency_code),
fnd_currency.get_format_mask (ch.currency_code, 50)) ||
' ' ||
ch.currency_code AS amount
FROM okc_k_headers_all_b ch,
oks_k_headers_b sh
WHERE ch.ID = sh.chr_id
AND ch.ID = p_chr_id;
SELECT TO_CHAR
(oks_extwar_util_pvt.round_currency_amt
(NVL (ch.estimated_amount,
0),
ch.currency_code),
fnd_currency.get_format_mask (ch.currency_code, 50)) ||
' ' ||
ch.currency_code AS amount
FROM okc_k_headers_all_b ch
WHERE ch.ID = p_chr_id;
SELECT TO_CHAR
(oks_extwar_util_pvt.round_currency_amt
(NVL (sh.tax_amount, 0), ch.currency_code),
fnd_currency.get_format_mask (ch.currency_code, 50)) ||
' ' ||
ch.currency_code AS amount
FROM okc_k_headers_all_b ch,
oks_k_headers_b sh
WHERE ch.ID = sh.chr_id
AND ch.ID = p_chr_id;
SELECT p.party_name AS customer_name
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 o.NAME AS vendor_name
FROM okc_k_party_roles_b r,
hr_all_organization_units o
WHERE o.organization_id = r.object1_id1
AND r.jtot_object1_code = 'OKX_OPERUNIT'
AND r.rle_code IN
('VENDOR', 'MERCHANT') -- gets only the VENDOR / MERCHANT
AND r.cle_id IS NULL
AND r.chr_id = p_chr_id;
SELECT 'Y'
FROM fnd_new_messages
WHERE message_name = 'OKS_CUST_ACCEPT_CLAUSE'
AND language_code = USERENV ('LANG')
AND regexp_like (MESSAGE_TEXT,
'CUSTOMER_NAME',
'c'
);
SELECT 'Y'
FROM fnd_new_messages
WHERE message_name = 'OKS_CUST_ACCEPT_CLAUSE'
AND language_code = USERENV ('LANG')
AND regexp_like (MESSAGE_TEXT,
'VENDOR_NAME',
'c'
);
SELECT p.party_name AS customer_name
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 o.NAME AS vendor_name
FROM okc_k_party_roles_b r,
hr_all_organization_units o
WHERE o.organization_id = r.object1_id1
AND r.jtot_object1_code = 'OKX_OPERUNIT'
AND r.rle_code IN
('VENDOR', 'MERCHANT') -- gets only the VENDOR / MERCHANT
AND r.cle_id IS NULL
AND r.chr_id = p_chr_id;
SELECT 'Y'
FROM fnd_new_messages
WHERE message_name = 'OKS_CUST_DECLINE_CLAUSE'
AND language_code = USERENV ('LANG')
AND regexp_like (MESSAGE_TEXT,
'CUSTOMER_NAME',
'c'
);
SELECT 'Y'
FROM fnd_new_messages
WHERE message_name = 'OKS_CUST_DECLINE_CLAUSE'
AND language_code = USERENV ('LANG')
AND regexp_like (MESSAGE_TEXT,
'VENDOR_NAME',
'c'
);
SELECT o.NAME AS vendor_name
FROM okc_k_party_roles_b r,
hr_all_organization_units o
WHERE o.organization_id = r.object1_id1
AND r.jtot_object1_code = 'OKX_OPERUNIT'
AND r.rle_code = 'VENDOR' -- gets only the VENDOR
AND r.cle_id IS NULL
AND r.chr_id = p_chr_id;
SELECT p.party_name AS customer_name
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.dnz_chr_id = p_chr_id; /* changed chr_id to dnz_chr_id for
SELECT object1_id1
FROM okc_k_party_roles_b
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL
AND jtot_object1_code = 'OKX_PARTY'
AND rle_code = 'CUSTOMER';
SELECT authoring_org_id
FROM okc_k_headers_all_b
WHERE ID = p_chr_id;
SELECT res.resource_name AS salesrep_name
FROM okc_k_headers_all_b khr,
okc_contacts ct,
jtf_rs_salesreps srp,
jtf_rs_resource_extns_vl res
WHERE khr.ID = ct.dnz_chr_id
AND ct.object1_id1 = srp.salesrep_id
AND srp.resource_id = res.resource_id
AND srp.org_id = khr.authoring_org_id
AND ct.jtot_object1_code='OKX_SALEPERS' --bug 6243682
AND res.CATEGORY IN
('EMPLOYEE', 'OTHER', 'PARTY', 'PARTNER', 'SUPPLIER_CONTACT')
-- AND srp.email_address IS NOT NULL -- bug 4918198
AND res.user_name IS NOT NULL -- Salesrep MUST be a FND USER
AND khr.ID = p_chr_id;
SELECT per.full_name help_desk_name
FROM jtf_rs_resource_extns jtfrse,
oks_k_defaults gcd,
per_all_people_f per
WHERE jtfrse.user_id = gcd.user_id
AND per.person_id = jtfrse.source_id
AND per.effective_start_date = (SELECT MAX (a.effective_start_date)
FROM per_all_people_f a
WHERE a.person_id = per.person_id)
AND gcd.cdt_type = 'SDT'
AND gcd.jtot_object_code = 'OKX_PARTY'
AND jtfrse.category = 'EMPLOYEE'
AND gcd.segment_id1 = p_k_party_id;
SELECT hd.help_desk_name
FROM oks_k_defaults gcd,
oks_help_desk_v hd
WHERE gcd.cdt_type = 'SDT'
AND gcd.jtot_object_code = 'OKX_PARTY'
AND gcd.user_id = hd.user_id
-- AND gcd.email_address IS NOT NULL
AND gcd.segment_id1 = p_k_party_id;
SELECT per.full_name help_desk_name
FROM jtf_rs_resource_extns jtfrse,
oks_k_defaults gcd,
per_all_people_f per
WHERE jtfrse.user_id = gcd.user_id
AND per.person_id = jtfrse.source_id
AND per.effective_start_date = (SELECT MAX (a.effective_start_date)
FROM per_all_people_f a
WHERE a.person_id = per.person_id)
AND gcd.cdt_type = 'SDT'
AND gcd.jtot_object_code = 'OKX_OPERUNIT'
AND jtfrse.category = 'EMPLOYEE'
AND gcd.segment_id1 = p_k_org_id;
SELECT hd.help_desk_name
FROM oks_k_defaults gcd,
oks_help_desk_v hd
WHERE gcd.cdt_type = 'SDT'
AND gcd.jtot_object_code = 'OKX_OPERUNIT'
AND gcd.user_id = hd.user_id
-- AND gcd.email_address IS NOT NULL
AND gcd.segment_id1 = p_k_org_id;
SELECT per.full_name help_desk_name
FROM jtf_rs_resource_extns jtfrse,
oks_k_defaults gcd,
per_all_people_f per
WHERE jtfrse.user_id = gcd.user_id
AND per.person_id = jtfrse.source_id
AND per.effective_start_date = (SELECT MAX (a.effective_start_date)
FROM per_all_people_f a
WHERE a.person_id = per.person_id)
AND gcd.cdt_type = 'MDT'
AND jtfrse.category = 'EMPLOYEE';
SELECT hd.help_desk_name
FROM oks_k_defaults gcd,
oks_help_desk_v hd
WHERE gcd.user_id = hd.user_id
AND gcd.cdt_type = 'MDT';
SELECT p.party_name AS customer_name,
r.object1_id1 AS party_id,
ca.cust_account_id AS customer_account_id
FROM okc_k_party_roles_b r,
hz_parties p,
hz_cust_accounts ca
WHERE p.party_id = r.object1_id1
AND ca.party_id = p.party_id
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 kc.contract_number ||
DECODE (kc.contract_number_modifier,
NULL, NULL,
'-'
) ||
kc.contract_number_modifier AS contract_number
FROM okc_k_headers_all_b kc
WHERE kc.ID = p_chr_id;
SELECT short_description
FROM okc_time_code_units_v
WHERE uom_code = p_code
AND active_flag = 'Y';
SELECT ibyt.card_number ||
' , ' ||
ibyt.card_issuer_name
/*modified by cgopinee for PA-DSS one off strategy*/
||' , ' ||
decode(encrypted,'A','',TO_CHAR(TO_DATE(ibyt.card_expirydate), 'MM/YYYY')) AS cc_number
FROM iby_trxn_extensions_v ibyt
WHERE ibyt.trxn_extension_id = p_trxn_extension_id;
SELECT ibyt.instrument_security_code AS cc_cvv2
FROM iby_trxn_extensions_v ibyt
WHERE ibyt.trxn_extension_id = p_trxn_extension_id;
SELECT k.currency_code ||
' = ' ||
f.NAME
FROM okc_k_headers_all_b k,
fnd_currencies_tl f
WHERE k.currency_code = f.currency_code
AND f.LANGUAGE = USERENV ('LANG')
AND k.ID = p_chr_id;
PROCEDURE update_payment_details (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_chr_id IN NUMBER,
p_payment_type IN VARCHAR2,
p_payment_details IN VARCHAR2,
p_party_id IN NUMBER,
p_cust_account_id IN NUMBER,
p_card_number IN VARCHAR2 DEFAULT NULL,
p_expiration_month IN VARCHAR2 DEFAULT NULL,
p_expiration_year IN VARCHAR2 DEFAULT NULL,
p_cvv_code IN VARCHAR2 DEFAULT NULL,
p_instr_assignment_id IN NUMBER DEFAULT NULL,
p_old_txn_entension_id IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
) AS
l_api_version CONSTANT NUMBER := 1;
:= 'update_payment_details';
SELECT st.party_site_id
FROM okc_k_headers_all_b okc,
hz_cust_site_uses_all su,
hz_cust_acct_sites_all sa,
hz_party_sites st
WHERE okc.bill_to_site_use_id = su.site_use_id
AND su.cust_acct_site_id = sa.cust_acct_site_id
AND sa.party_site_id = st.party_site_id
AND okc.ID = p_chr_id;
SELECT LAST_DAY (TO_DATE (p_month ||
'/' ||
p_year, 'MM/YYYY'))
FROM DUAL;
delete_transaction_extension (p_chr_id => p_chr_id,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
UPDATE oks_k_headers_b
SET payment_type = p_payment_type,
trxn_extension_id = l_trxn_extension_id,
commitment_id = NULL,
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_chr_id;
UPDATE okc_k_headers_all_b
SET cust_po_number = NULL,
payment_instruction_type = NULL,
cust_po_number_req_yn = 'N',
payment_term_id =
NVL (x_rnrl_rec.payment_terms_id1, payment_term_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 ID = p_chr_id;
UPDATE oks_k_headers_b
SET payment_type = p_payment_type,
commitment_id = p_payment_details,
trxn_extension_id = NULL,
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_chr_id;
UPDATE okc_k_headers_all_b
SET cust_po_number = NULL,
payment_instruction_type = NULL,
cust_po_number_req_yn = 'N',
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 ID = p_chr_id;
UPDATE oks_k_headers_b
SET payment_type = NULL,
trxn_extension_id = NULL,
commitment_id = NULL,
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_chr_id;
UPDATE okc_k_headers_all_b
SET cust_po_number = p_payment_details,
payment_instruction_type = p_payment_type,
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 ID = p_chr_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_chr_id;
END update_payment_details;
SELECT (NVL (ch.estimated_amount, 0) + NVL (sh.tax_amount, 0) ) AS amount,
ch.currency_code AS currency_code,
ch.payment_instruction_type AS instrument_type,
sh.payment_type AS payment_type
FROM okc_k_headers_all_b ch,
oks_k_headers_b sh
WHERE ch.ID = sh.chr_id
AND ch.ID = p_chr_id;
SELECT payment_type
FROM oks_k_headers_b
WHERE chr_id = p_chr_id;
SELECT LOOKUP_CODE
FROM fnd_lookups
WHERE lookup_type='OKS_OA_PAYMENT_TYPES'
AND INSTR (l_valid_payments,(lookup_code)) <> 0
AND ENABLED_FLAG<>'N'
AND Nvl(END_DATE_ACTIVE,SYSDATE)>= SYSDATE ;
'150: ***** Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
);
iby_fndcpt_trxn_pub.delete_transaction_extension
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payer => l_payer,
p_payer_equivalency => iby_fndcpt_common_pub.g_payer_equiv_full,
p_entity_id => p_old_txn_entension_id,
x_response => l_response
);
dbms_output.put_line('After Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
'200: ***** After Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
);
'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
iby will NOT allow txn extn to be deleted if there are any authorizations against the txn extn id
In OKS QA check, we get a authorization from iby to validate credit card
If QA check is run on the contract, then delete will fail and iby will return an error
We will ignore Error from iby when delete txn is called.
fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
SELECT srp.email_address AS email_address,
res.user_name AS username
FROM okc_k_headers_all_b khr,
okc_contacts ct,
jtf_rs_salesreps srp,
jtf_rs_resource_extns res
WHERE khr.ID = ct.dnz_chr_id
AND ct.object1_id1 = srp.salesrep_id
AND srp.resource_id = res.resource_id
AND srp.org_id = khr.authoring_org_id
AND ct.jtot_object1_code='OKX_SALEPERS' --bug 6243682
AND res.CATEGORY IN
('EMPLOYEE', 'OTHER', 'PARTY', 'PARTNER', 'SUPPLIER_CONTACT')
-- AND srp.email_address IS NOT NULL -- bug 4918198
AND res.user_name IS NOT NULL -- Salesrep MUST BE a FND USER
AND khr.ID = p_chr_id;
SELECT gcd.email_address,
fnd.user_name
FROM oks_k_defaults gcd,
fnd_user fnd
WHERE gcd.user_id = fnd.user_id
AND gcd.cdt_type = 'SDT'
AND gcd.jtot_object_code = 'OKX_PARTY'
-- AND gcd.email_address IS NOT NULL -- bug 4918198
AND gcd.segment_id1 = p_k_party_id;
SELECT gcd.email_address,
fnd.user_name
FROM oks_k_defaults gcd,
fnd_user fnd
WHERE gcd.user_id = fnd.user_id
AND gcd.cdt_type = 'SDT'
AND gcd.jtot_object_code = 'OKX_OPERUNIT'
-- AND gcd.email_address IS NOT NULL -- bug 4918198
AND gcd.segment_id1 = p_k_org_id;
SELECT gcd.email_address,
fnd.user_name
FROM oks_k_defaults gcd,
fnd_user fnd
WHERE gcd.user_id = fnd.user_id
AND gcd.cdt_type = 'MDT';
PROCEDURE delete_transaction_extension (
p_chr_id IN NUMBER,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
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)
:= 'delete_transaction_extension';
SELECT oks.trxn_extension_id,
ca.cust_account_id,
ca.party_id
FROM okc_k_headers_all_b okc,
oks_k_headers_b oks,
hz_cust_site_uses_all su,
hz_cust_acct_sites_all sa,
hz_cust_accounts_all ca
WHERE oks.chr_id = okc.ID
AND okc.bill_to_site_use_id = su.site_use_id
AND su.cust_acct_site_id = sa.cust_acct_site_id
AND sa.cust_account_id = ca.cust_account_id
AND oks.trxn_extension_id IS NOT NULL
AND okc.ID = p_chr_id;
'150: ***** Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
);
iby_fndcpt_trxn_pub.delete_transaction_extension
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_payer => l_payer,
p_payer_equivalency => iby_fndcpt_common_pub.g_payer_equiv_full,
p_entity_id => l_trxn_extension_id,
x_response => l_response
);
'200: ***** After Calling IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension *****'
);
'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
iby will NOT allow txn extn to be deleted if there are any authorizations against the txn extn id
In OKS QA check, we get a authorization from iby to validate credit card
If QA check is run on the contract, then delete will fail and iby will return an error
We will ignore Error from iby when delete txn is called.
fnd_message.set_name (g_app_name, 'OKS_IBY_API_ERROR');
'IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension');
END delete_transaction_extension;