[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(1)
FROM dpp_transaction_headers_all dpp
WHERE NOT EXISTS
(SELECT dep.transaction_header_id
FROM dpp_execution_processes dep
WHERE dep.transaction_header_id = dpp.transaction_header_id);
select distinct vendor_id, vendor_site_id, org_id
from dpp_transaction_headers_all dtha
where not exists (select supp_trade_profile_id
from ozf_supp_trd_prfls_all ostpa
where ostpa.supplier_id = dtha.vendor_id
and ostpa.supplier_site_id = dtha.vendor_site_id
and ostpa.org_id = dtha.org_id);
select aps.vendor_id, aps.vendor_name, apss.vendor_site_id, apss.vendor_site_code,
apss.org_id, hr.name
from ap_suppliers aps, ap_supplier_sites_all apss, hr_operating_units hr
where aps.vendor_id = p_vendor_id
and aps.vendor_id = apss.vendor_id
and apss.vendor_site_id = p_vendor_site_id
and apss.org_id = p_org_id
and apss.org_id = hr.organization_id;
SELECT DISTINCT org_id
FROM dpp_transaction_headers_all;
SELECT COUNT(1)
FROM ozf_process_setup_all
WHERE nvl(supp_trade_profile_id,0) = 0
AND enabled_flag = 'Y'
AND org_id = p_org_id;
select hr.name
from hr_operating_units hr
where hr.organization_id = p_org_id;
PROCEDURE insertExecutionProcesses
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validation_level IN NUMBER := fnd_api.g_valid_level_full
, x_return_status OUT nocopy VARCHAR2
, x_msg_count OUT nocopy NUMBER
, x_msg_data OUT nocopy VARCHAR2
)
AS
l_api_name constant VARCHAR2(30) := 'insertExecutionProcesses';
SELECT transaction_header_id
FROM dpp_transaction_headers_all dpp
WHERE NOT EXISTS
(SELECT dep.transaction_header_id
FROM dpp_execution_processes dep
WHERE dep.transaction_header_id = dpp.transaction_header_id);
SELECT ostpa.supp_trade_profile_id, dtha.org_id
FROM dpp_transaction_headers_all dtha, ozf_supp_trd_prfls_all ostpa
WHERE dtha.transaction_header_id = p_transaction_header_id
AND dtha.vendor_id = ostpa.supplier_id
AND dtha.vendor_site_id = ostpa.supplier_site_id
AND dtha.org_id = ostpa.org_id;
SELECT COUNT(1)
FROM OZF_PROCESS_SETUP_ALL
WHERE nvl(supp_trade_profile_id,0) = nvl(p_supp_trade_profile_id,0)
AND enabled_flag = 'Y'
AND org_id = p_org_id;
SELECT dppl.lookup_code
FROM dpp_lookups dppl,
ozf_process_setup_all opsa
WHERE dppl.lookup_type = 'DPP_EXECUTION_PROCESSES'
AND dppl.tag is not null
AND nvl(opsa.supp_trade_profile_id,0) = nvl(p_supp_trd_prf_id,0)
AND opsa.enabled_flag = 'Y'
AND opsa.org_id = p_org_id
AND dppl.lookup_code = opsa.process_code;
fnd_file.put_line(fnd_file.log, ' Begin insertExecutionProcesses ' );
INSERT INTO DPP_EXECUTION_PROCESSES (process_code,
transaction_header_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (process_codes(idx),
l_transaction_header_id,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id
);
fnd_file.put_line(fnd_file.log,'Exception while fetching the process code and inserting into DPP_EXECUTION_PROCESSES: ' || SQLERRM);
fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.insertExecutionProcesses');
END insertExecutionProcesses;
select transaction_header_id, transaction_number
from dpp_transaction_headers_all
where transaction_status IN ( 'APPROVED' , 'REJECTED' , 'PENDING_APPROVAL' )
and trunc(effective_start_date) > trunc(sysdate);
--Delete the approval access
delete from dpp_approval_access
where object_id in ( select transaction_header_id
from dpp_transaction_headers_all
where transaction_status IN ( 'APPROVED' , 'REJECTED' , 'PENDING_APPROVAL' )
and trunc(effective_start_date) > trunc(sysdate) );
fnd_file.put_line(fnd_file.log, ' Transactions approval entries have been deleted from DPP_APPROVAL_ACCESS' );
PROCEDURE update_status
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validation_level IN NUMBER := fnd_api.g_valid_level_full
, x_return_status OUT nocopy VARCHAR2
, x_msg_count OUT nocopy NUMBER
, x_msg_data OUT nocopy VARCHAR2
)
AS
l_api_name constant VARCHAR2(30) := 'update_status';
fnd_file.put_line(fnd_file.log, ' Begin update_status ' );
UPDATE dpp_transaction_headers_all
SET transaction_status = 'APPROVED',
object_version_number = object_version_number +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE transaction_status = 'ACTIVE';
UPDATE dpp_transaction_headers_all
SET transaction_status = 'PENDING_ADJUSTMENT',
object_version_number = object_version_number +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE transaction_status = 'NEW'
AND TRUNC(effective_start_date) <= TRUNC(SYSDATE);
UPDATE dpp_transaction_headers_all
SET transaction_status = 'ACTIVE',
object_version_number = object_version_number +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE transaction_status = 'NEW'
AND TRUNC(effective_start_date) > TRUNC(SYSDATE);
UPDATE dpp_transaction_headers_all
SET transaction_status = 'ACTIVE',
object_version_number = object_version_number +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE transaction_status = 'APPROVED'
AND TRUNC(effective_start_date) > TRUNC(SYSDATE);
UPDATE dpp_transaction_headers_all
SET transaction_status = 'ACTIVE',
object_version_number = object_version_number +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE transaction_status = 'REJECTED'
AND TRUNC(effective_start_date) > TRUNC(SYSDATE);
UPDATE dpp_transaction_headers_all
SET transaction_status = 'ACTIVE',
object_version_number = object_version_number +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id,
request_id = l_request_id,
program_application_id = l_program_application_id,
program_id = l_program_id,
program_update_date = sysdate
WHERE transaction_status = 'PENDING_APPROVAL'
AND TRUNC(effective_start_date) > TRUNC(SYSDATE);
fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.update_status');
END update_status;
PROCEDURE update_transaction_status(
errbuf OUT NOCOPY VARCHAR2
, retcode OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_transaction_status';
SAVEPOINT update_transaction_status;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert Execution Processes');
InsertExecutionProcesses(
p_api_version => l_api_version
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
, p_validation_level => l_validation_level
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
) ;
fnd_file.put_line(fnd_file.log, ' Insert Execution Processes. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update status');
update_status(
p_api_version => l_api_version
, p_init_msg_list => l_init_msg_list
, p_commit => l_commit
, p_validation_level => l_validation_level
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
) ;
fnd_file.put_line(fnd_file.log, ' Update status. Return Status: ' || l_return_status || ' Error Msg: ' || l_msg_data);
ROLLBACK TO update_transaction_status;
ROLLBACK TO update_transaction_status;
ROLLBACK TO update_transaction_status;
fnd_message.set_token('ROUTINE', 'DPP_MIG_ADJ_PARA_APPROVAL_PVT.update_transaction_status');
END update_transaction_status;