[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select value value, substr(value,1,instr(value,',')-1) subvalue
from v$parameter where name = 'utl_file_dir';
select org_id
from oe_order_headers_all
where header_id = l_order_header_id;
l_update_order boolean := false;
select 1 from dual where exists
(select /*+ CARDINALITY(t 10) */ 1
from oe_order_lines_all,(SELECT * FROM TABLE (CAST(cv_order_header_id AS JTF_NUMBER_TABLE))) t
where header_id = t.column_value
and (unit_list_price is null or
unit_selling_price is null or price_list_id is null));
l_update_order := true;
IF(not(l_update_order)) THEN
l_transaction_type_id := to_number(FND_PROFILE.Value('PV_ORDER_TRANSACTION_TYPE_ID'));
select party_id
into l_party_id
from hz_party_sites
where party_site_id = p_party_site_id
and party_id in (p_partner_party_id, p_contact_party_id)
and status = 'A';
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
/*l_header_out_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT /*+ CARDINALITY(erequests 10) */ pvpp.inventory_item_id, pver.enrl_request_id, pver.order_header_id, pver.object_version_number
FROM PV_PARTNER_PROGRAM_B pvpp, PV_PG_ENRL_REQUESTS pver,
(Select column_value from table (CAST(p_enrl_req_id AS JTF_NUMBER_TABLE))) erequests
WHERE pver.enrl_request_id = erequests.column_value
and pver.PROGRAM_ID = pvpp.program_id
and pver.custom_setup_id in (7004, 7005)
and (pver.order_header_id is null
OR (pver.order_header_id is not null and pver.payment_status_code <> 'AUTHORIZED_PAYMENT'));
PV_Pg_Enrl_Requests_PVT.Update_Pg_Enrl_Requests
(
p_api_version_number => p_api_version_number,
p_init_msg_list => Fnd_Api.G_FALSE,
p_commit => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_enrl_request_rec => l_enrl_req_rec
);
SELECT pvpp.inventory_item_id
FROM PV_PARTNER_PROGRAM_B pvpp
WHERE program_id = cv_program_id;
PV_PG_INVITE_HEADERS_PVT.update_invite_headers
(
p_api_version_number => p_api_version_number,
p_init_msg_list => Fnd_Api.G_FALSE,
p_commit => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_invite_headers_rec => l_invite_headers_rec
);
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_header_out_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
SELECT /*+ CARDINALITY(t 10) */ pver.order_header_id
FROM PV_PG_ENRL_REQUESTS pver,
(Select * from table (CAST(p_enrl_req_id AS JTF_NUMBER_TABLE))) t
WHERE pver.enrl_request_id = t.column_value
and pver.order_header_id is not null
and pver.payment_status_code <> 'AUTHORIZED_PAYMENT';
select party_site_id
into x_party_site_id
from hz_party_sites
where party_id = p_contact_party_id
and location_id = p_location_id
and status = 'A';
SELECT /*+ cardinality( T 10 ) */ ((oeol.unit_selling_price*oeol.ordered_quantity)+nvl(oeol.tax_value,0)) payment_amount, oeoh.transactional_curr_code currency,
oeoh.header_id
FROM oe_order_headers_all oeoh, oe_order_Lines_all oeol,
(SELECT * FROM TABLE (CAST(cv_order_header_id_tbl AS JTF_NUMBER_TABLE))) T
WHERE oeoh.header_id = T.column_value
AND oeoh.header_id = oeol.header_id;
select hzp.party_type,
hzs.location_id
into l_party_type,
l_location_id
from hz_parties hzp,
hz_party_sites hzs
where hzs.party_site_id = p_payment_method_rec.cc_stmt_party_site_id
and hzp.party_id = hzs.party_id;
PVX_UTILITY_PVT.debug_message('In ' || l_api_name || ' before the IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension call');
IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
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_UPWARD,
p_entity_id => x_enrl_info(x.header_id).trxn_extension_id,
x_response => lx_response
);
PVX_UTILITY_PVT.debug_message('In ' || l_api_name || ' x_return_status from IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension : ' || x_return_status);
PVX_UTILITY_PVT.debug_message('In ' || l_api_name || ' x_msg_count from IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension : ' || x_msg_count);
PVX_UTILITY_PVT.debug_message('In ' || l_api_name || ' x_msg_data from IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension : ' || x_msg_data);
PVX_UTILITY_PVT.debug_message('In ' || l_api_name || ' after the IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension call');
* If not, go ahead and create one, otherwise just update it
*/
-- Debug Message
IF (PV_DEBUG_HIGH_ON) THEN
PVX_UTILITY_PVT.debug_message('In ' || l_api_name || ' before the IF (x_enrl_info(x.header_id).trxn_extension_id IS NULL)');
select nvl(authorized_flag,'N')
into l_skip_auth
from iby_trxn_extensions_v
where trxn_extension_id = lx_trxn_extension_id;
PVX_UTILITY_PVT.debug_message('In ' || l_api_name || ' before the IBY_FNDCPT_TRXN_PUB.Update_Transaction_Extension call');
-- Only update transaction extension if successful authorization hasn't already happened (online or offline)
IF (l_skip_auth = 'N') THEN
IBY_FNDCPT_TRXN_PUB.Update_Transaction_Extension
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
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_UPWARD,
p_pmt_channel => 'CREDIT_CARD',
p_instr_assignment => lx_instr_assign_id,
p_trxn_attribs => l_trxn_attribs,
p_entity_id => lx_trxn_extension_id,
x_response => lx_response
);
PVX_UTILITY_PVT.debug_message('In ' || l_api_name || ' x_return_status from IBY_FNDCPT_TRXN_PUB.Update_Transaction_Extension : ' || x_return_status);
PVX_UTILITY_PVT.debug_message('In ' || l_api_name || ' x_msg_count from IBY_FNDCPT_TRXN_PUB.Update_Transaction_Extension : ' || x_msg_count);
PVX_UTILITY_PVT.debug_message('In ' || l_api_name || ' x_msg_data from IBY_FNDCPT_TRXN_PUB.Update_Transaction_Extension : ' || x_msg_data);
PVX_UTILITY_PVT.debug_message('In ' || l_api_name || ' after the IBY_FNDCPT_TRXN_PUB.Update_Transaction_Extension call');
* We still need to update the enrollment with the transaction extension
*/
IF ((x_Return_Status <> FND_API.G_RET_STS_SUCCESS) OR (lx_response.Result_Code <> 'AUTH_SUCCESS')) THEN
l_is_authorized := 'N';
SELECT /*+ CARDINALITY(t 10) */ pver.order_header_id, pver.enrl_request_id,
pver.object_version_number, pver.partner_id, pver.trxn_extension_id
FROM PV_PG_ENRL_REQUESTS pver,
(Select * from table (CAST(cv_enrl_req_id AS JTF_NUMBER_TABLE))) t
WHERE pver.enrl_request_id = t.column_value
and pver.custom_setup_id in (7004, 7005)
and pver.order_header_id is not null
and pver.payment_status_code <> 'AUTHORIZED_PAYMENT';
* For credit cards only, we will be updated the transaction extension id
* in the enrollment requests table regardless of whether the credit card
* authorization is sucessful
*/
for i in lx_enrl_info.FIRST..lx_enrl_info.LAST loop
l_enrl_req_rec.object_version_number := lx_enrl_info(i).object_version_number;
PVX_UTILITY_PVT.debug_message('API: ' || l_api_name|| ' Before update pv_pg_enrl_request');
PV_Pg_Enrl_Requests_PVT.Update_Pg_Enrl_Requests
(
p_api_version_number => p_api_version_number,
p_init_msg_list => Fnd_Api.G_FALSE,
p_commit => Fnd_Api.G_TRUE, -- we will always commit the new trxn extensions generated
p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_enrl_request_rec => l_enrl_req_rec
);
l_header_rec.operation := OE_GLOBALS.g_OPR_UPDATE;
l_header_rec.last_update_date := SYSDATE;
l_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
PVX_UTILITY_PVT.debug_message('API: ' || l_api_name|| ' Before update pv_pg_enrl_request');
PV_Pg_Enrl_Requests_PVT.Update_Pg_Enrl_Requests
(
p_api_version_number => p_api_version_number,
p_init_msg_list => Fnd_Api.G_FALSE,
p_commit => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_enrl_request_rec => l_enrl_req_rec
);
select payment_amount,
transactional_curr_code
into l_payment_amount,
l_trans_currency
from oe_order_headers_all
where header_id = lx_enrl_info(i).order_header_id;
select meaning
into l_pmnt_mode_mean
from PV_LOOKUPS
where lookup_type = 'PV_PAYMENT_TYPE'
and lookup_code = p_payment_method_rec.payment_type_code;
SELECT pvih.order_header_id, pvih.invite_header_id, pvih.object_version_number,
pvih.partner_id, pvih.trxn_extension_id
FROM PV_PG_INVITE_HEADERS_B pvih
WHERE pvih.invite_header_id = cv_invite_header_id
and pvih.order_header_id is not null;
PV_Pg_Invite_Headers_PVT.Update_Invite_Headers
(
p_api_version_number => p_api_version_number,
p_init_msg_list => Fnd_Api.G_FALSE,
p_commit => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_invite_headers_rec => l_invite_header_rec
);
l_header_rec.operation := OE_GLOBALS.g_OPR_UPDATE;
l_header_rec.last_update_date := SYSDATE;
l_header_rec.last_updated_by := FND_GLOBAL.USER_ID;