[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT HOU.name
FROM HR_OPERATING_UNITS HOU
WHERE HOU.organization_id = p_org_id;
SELECT CHR.id,
CHR.contract_number va_number,
CHR.START_DATE,
CHR.end_date,
CHR.sts_code, -- Should be ACTIVE
CHR.scs_code -- Should be PROGRAM or OPERATING
FROM OKC_K_HEADERS_B CHR,
OKL_K_HEADERS KHR
WHERE CHR.id = p_va_id
AND CHR.id = KHR.id;
SELECT TRN.tsu_code
FROM OKL_TRX_CONTRACTS TRN
WHERE TRN.khr_id = p_va_id
--rkuttiya added for 12.1.1 Multi GAAP
AND TRN.representation_type = 'PRIMARY'
--
AND TRN.tcn_type = 'IAT';
PROCEDURE pop_or_insert_transaction(
p_va_rec IN va_rec_type,
p_sys_date IN DATE,
p_validate_success IN VARCHAR2,
x_trn_already_yn OUT NOCOPY VARCHAR2,
px_tcnv_rec IN OUT NOCOPY tcnv_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
-- Get the trn if exists
CURSOR get_trn_csr ( p_ia_id IN NUMBER ) IS
SELECT TRN.id,
TRN.trx_number,
TRN.tsu_code,
TRN.tcn_type,
TRN.try_id,
TRN.khr_id,
TRN.tmt_validated_yn,
TRN.tmt_accounting_entries_yn,
TRN.tmt_contract_updated_yn,
TRN.tmt_recycle_yn,
TRN.tmt_generic_flag1_yn,
TRN.tmt_generic_flag2_yn,
TRN.tmt_generic_flag3_yn
FROM OKL_TRX_CONTRACTS TRN
WHERE TRN.khr_id = p_ia_id
--rkuttiya added for 12.1.1 Multi GAAP
AND TRN.representation_type = 'PRIMARY'
--
AND TRN.tcn_type = 'IAT';
'OKL_AM_TERMNT_VENDOR_PRG_PVT.pop_or_insert_transaction.',
'Begin(+)');
SAVEPOINT pop_insert_trn_trx;
lx_tcnv_rec.tmt_contract_updated_yn := get_trn_rec.tmt_contract_updated_yn;
-- Insert TRN if not exists
-- *************
IF lx_tcnv_rec.id IS NULL
OR lx_tcnv_rec.id = G_MISS_NUM THEN
-- Get the Transaction Id
OKL_AM_UTIL_PVT.get_transaction_id (
p_try_name => 'Termination',
x_return_status => l_return_status,
x_try_id => l_try_id);
'OKL_AM_TERMNT_VENDOR_PRG_PVT.pop_or_insert_transaction.',
'End(-)');
ROLLBACK TO pop_insert_trn_trx;
'OKL_AM_TERMNT_VENDOR_PRG_PVT.pop_or_insert_transaction.',
'EXP - G_EXCEPTION_ERROR');
ROLLBACK TO pop_insert_trn_trx;
'OKL_AM_TERMNT_VENDOR_PRG_PVT.pop_or_insert_transaction.',
'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
ROLLBACK TO pop_insert_trn_trx;
'OKL_AM_TERMNT_VENDOR_PRG_PVT.pop_or_insert_transaction.',
'EXP - OTHERS');
END pop_or_insert_transaction;
PROCEDURE update_transaction(
p_va_rec IN va_rec_type,
p_status IN VARCHAR2,
p_step IN VARCHAR2,
px_tcnv_rec IN OUT NOCOPY tcnv_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
'Begin(+)');
SAVEPOINT update_transaction_trx;
IF p_step = 'tmt_contract_updated_yn' THEN -- If last step
IF p_status = G_RET_STS_SUCCESS THEN
lp_tcnv_rec.tsu_code := 'PROCESSED';
IF p_step = 'tmt_contract_updated_yn' THEN
lp_tcnv_rec.tmt_contract_updated_yn := l_status;
OKL_TRX_CONTRACTS_PUB.update_trx_contracts(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_tcnv_rec => lp_tcnv_rec,
x_tcnv_rec => lx_tcnv_rec);
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
'l_return_status '||l_return_status);
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
'End(-)');
ROLLBACK TO update_transaction_trx;
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
'EXP - G_EXCEPTION_ERROR');
ROLLBACK TO update_transaction_trx;
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
ROLLBACK TO update_transaction_trx;
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_transaction.',
'EXP - OTHERS');
END update_transaction;
PROCEDURE update_vendor_prg(
p_va_rec IN va_rec_type,
p_control_flag IN VARCHAR2,
px_va_rec IN OUT NOCOPY va_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
'Begin(+)');
SAVEPOINT update_vpa_trx;
OKL_CONTRACT_PUB.update_contract_header(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_rec);
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
'l_return_status '||l_return_status);
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
'End(-)');
ROLLBACK TO update_vpa_trx;
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
'EXP - G_EXCEPTION_ERROR');
ROLLBACK TO update_vpa_trx;
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
ROLLBACK TO update_vpa_trx;
'OKL_AM_TERMNT_VENDOR_PRG_PVT.update_vendor_prg.',
'EXP - OTHERS');
END update_vendor_prg;
l_update_status VARCHAR2(3);
SELECT sysdate INTO l_sys_date FROM DUAL;
pop_or_insert_transaction(
p_va_rec => l_va_rec,
p_sys_date => l_sys_date,
p_validate_success => l_overall_status,
x_trn_already_yn => l_trn_already_yn,
px_tcnv_rec => l_tcnv_rec,
x_return_status => l_return_status);
'pop_or_insert_transaction = '||l_return_status );
update_transaction(
p_va_rec => l_va_rec,
p_status => l_validate_status,
p_step => l_step,
px_tcnv_rec => l_tcnv_rec,
x_return_status => l_return_status);
'update_transaction = '||l_return_status ||
'l_step = '||l_step );
update_vendor_prg(
p_va_rec => p_va_rec,
p_control_flag => p_control_flag,
px_va_rec => l_va_rec,
x_return_status => l_return_status);
'update_vendor_prg = '||l_return_status );
l_step := 'tmt_contract_updated_yn';
l_update_status := l_return_status;
update_transaction(
p_va_rec => l_va_rec,
p_status => l_update_status,
p_step => l_step,
px_tcnv_rec => l_tcnv_rec,
x_return_status => l_return_status);
'update_transaction = '||l_return_status ||
'l_step = '||l_step );
SELECT CHR.id,
CHR.contract_number va_number
FROM OKC_K_HEADERS_B CHR
WHERE CHR.scs_code IN ('PROGRAM','OPERATING') -- VPAs
AND CHR.sts_code = 'ACTIVE' -- ACTIVE
AND CHR.date_terminated IS NULL -- Not Terminated
AND CHR.end_date <= TRUNC(p_date); -- Ended