The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT '1'
FROM okc_k_party_roles_b cpl
WHERE cpl.chr_id = p_chr_id
AND cpl.rle_code='OKL_VENDOR'
AND cpl.object1_id1 = p_vendor_id;
SELECT '1'
FROM okc_k_party_roles_b cpl
WHERE cpl.chr_id = p_chr_id
AND cpl.rle_code='OKL_VENDOR'
AND cpl.object1_id1 = p_vendor_id;
SELECT cle.id,
cle.start_date
FROM okc_k_lines_v cle,
OKL_K_LINES kle,
okc_line_styles_b lse
WHERE cle.id = kle.id
AND lse.id = cle.lse_id
AND cle.dnz_chr_id = p_chr_id
--AND lse.lty_code IN ('FREE_FORM1','SOLD_SERVICE')
AND lse.lty_code = 'SOLD_SERVICE'
AND cle.sts_code NOT IN ('ABANDONED','CANCELLED','EXPIRED','TERMINATED');
SELECT id
FROM okc_k_party_roles_b
WHERE dnz_chr_id = p_chr_id
AND cle_id = p_cle_id
AND rle_code='OKL_VENDOR'
AND object1_id1 = p_vendor_id;
SELECT start_date
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT '1'
FROM OKL_PARTY_PAYMENT_HDR
WHERE dnz_chr_id = p_chr_id
AND NVL(cle_id,-1) = NVL(p_cle_id,-1)
AND passthru_term = p_passthru_term;
OKL_LLA_UTIL_PVT.check_line_update_allowed
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_id => p_pphv_rec.cle_id);
Okl_Ldb_Pvt.insert_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_pphv_rec,
x_pphv_rec);
-- Need to change contract status to INCOMPLETE when create/update ppy
-- cascade edit status on to lines
okl_contract_status_pub.cascade_lease_status_edit
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id => l_pphv_rec.dnz_chr_id);
PROCEDURE delete_party_payment_hdr(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pphv_rec IN pphv_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PARTY_PAYMENT_HDR';
SELECT cle_id
FROM okl_party_payment_hdr pph
WHERE pph.id = p_pph_id;
OKL_LLA_UTIL_PVT.check_line_update_allowed
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_id => l_pph_rec.cle_id);
Okl_Ldb_Pvt.delete_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_pphv_rec);
END delete_party_payment_hdr;
PROCEDURE delete_party_payment_hdr(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pphv_tbl IN pphv_tbl_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PARTY_PAYMENT_HDR';
delete_party_payment_hdr(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_pphv_tbl(i));
END delete_party_payment_hdr;
PROCEDURE update_party_payment_hdr(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pphv_rec IN pphv_rec_type,
x_pphv_rec OUT NOCOPY pphv_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PARTY_PAYMENT_HDR';
SELECT cle_id
FROM okl_party_payment_hdr pph
WHERE pph.id = p_pph_id;
OKL_LLA_UTIL_PVT.check_line_update_allowed
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_id => l_pph_rec.cle_id);
Okl_Ldb_Pvt.update_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_pphv_rec,
x_pphv_rec);
-- Need to change contract status to INCOMPLETE when create/update ppy
-- cascade edit status on to lines
okl_contract_status_pub.cascade_lease_status_edit
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id => x_pphv_rec.dnz_chr_id);
END update_party_payment_hdr;
PROCEDURE update_party_payment_hdr(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_pphv_tbl IN pphv_tbl_type,
x_pphv_tbl OUT NOCOPY pphv_tbl_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PARTY_PAYMENT_HDR';
update_party_payment_hdr(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_pphv_tbl(i),
x_pphv_tbl(i));
END update_party_payment_hdr;
SELECT kle.amount,
kle.id
FROM okc_k_lines_b cle,
OKL_K_LINES kle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_chr_id
AND cle.chr_id = p_chr_id
AND kle.id = cle.id
AND cle.lse_id = lse.id
AND lse.lty_code IN ('FEE', 'SOLD_SERVICE');
SELECT pyd.disbursement_basis,
pyd.disbursement_fixed_amount,
pyd.disbursement_percent,
pyd.vendor_id
FROM OKL_PARTY_PAYMENT_HDR pph, OKL_PARTY_PAYMENT_DTLS pyd
WHERE pph.dnz_chr_id = p_chr_id
AND pph.cle_id = p_cle_id
AND pph.id = pyd.payment_hdr_id;
SELECT DISTINCT(pph.cle_id),pph.payout_basis,lse.lty_code
FROM okc_line_styles_b lse, okc_k_lines_b cle, OKL_PARTY_PAYMENT_HDR pph
WHERE lse.id = cle.lse_id
AND lse.lty_code = 'FEE'
AND cle.id= pph.cle_id
AND pph.dnz_chr_id =p_chr_id;
SELECT kle.amount,
kle.id
FROM okc_k_lines_b cle,
OKL_K_LINES kle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_chr_id
AND cle.chr_id = p_chr_id
AND kle.id = cle.id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FEE';
SELECT TO_NUMBER(sll.rule_information3) periods,
TO_NUMBER(sll.rule_information6) amount,
TO_NUMBER(sll.rule_information8) stub_amount
FROM okc_rules_b sll,
okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LALEVL'
AND sll.rule_information_category = 'LASLL'
AND sll.rgp_id = rgp.id;
SELECT DISTINCT(pph.cle_id),pph.payout_basis
FROM okc_line_styles_b lse, okc_k_lines_b cle, OKL_PARTY_PAYMENT_HDR pph
WHERE lse.id = cle.lse_id
AND lse.lty_code = 'SOLD_SERVICE'
AND cle.id= pph.cle_id
AND pph.dnz_chr_id =p_chr_id;
SELECT TO_NUMBER(sll.rule_information6) amount
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LALEVL'
AND slh.rgp_id = rgp.id
AND slh.rule_information_category = 'LASLH'
AND sll.object2_id1 = slh.id
AND sll.rule_information_category = 'LASLL'
AND sll.rgp_id = rgp.id;
SELECT pph.dnz_chr_id,
pph.cle_id,
pph.ppl_id,
pph.passthru_term,
pph.passthru_stream_type_id,
pph.passthru_start_date,
pph.payout_basis,
pph.payout_basis_formula,
pph.effective_from,
pph.effective_to,
pyd.id,
pyd.cpl_id,
pyd.vendor_id,
pyd.pay_site_id,
pyd.payment_term_id,
pyd.payment_method_code,
pyd.pay_group_code,
pyd.payment_hdr_id,
pyd.payment_basis,
pyd.payment_start_date,
pyd.payment_frequency,
pyd.remit_days,
pyd.disbursement_basis,
pyd.disbursement_fixed_amount,
pyd.disbursement_percent,
pyd.processing_fee_basis,
pyd.processing_fee_fixed_amount,
pyd.processing_fee_percent,
--pyd.processing_fee_formula,
--pyd.include_in_yield_flag,
pyd.attribute_category,
pyd.attribute1,
pyd.attribute2,
pyd.attribute3,
pyd.attribute4,
pyd.attribute5,
pyd.attribute6,
pyd.attribute7,
pyd.attribute8,
pyd.attribute9,
pyd.attribute10,
pyd.attribute11,
pyd.attribute12,
pyd.attribute13,
pyd.attribute14,
pyd.attribute15
FROM OKL_PARTY_PAYMENT_HDR pph,
OKL_PARTY_PAYMENT_DTLS pyd
WHERE pph.dnz_chr_id = p_chr_id
AND pph.id = pyd.payment_hdr_id;
SELECT 'Y'
FROM okc_k_party_roles_b cplb
WHERE id = p_cpl_id
AND object1_id1 = TO_CHAR(p_vendor_id);
SELECT 'Y'
FROM OKL_PARTY_PAYMENT_HDR
WHERE id = p_payment_hdr_id;
SELECT passthru_term
FROM OKL_PARTY_PAYMENT_HDR
WHERE id = p_hdr_id;
SELECT '1'
FROM OKL_PARTY_PAYMENT_DTLS
WHERE payment_hdr_id = p_payment_hdr_id
AND vendor_id = p_vendor_id;
SELECT distinct(pph.dnz_chr_id)
FROM okl_party_payment_dtls ppy, okl_party_payment_hdr pph
WHERE ppy.payment_hdr_id = p_payment_hdr_id
AND ppy.payment_hdr_id = pph.id;
SELECT cle_id
FROM okl_party_payment_hdr pph
WHERE pph.id = p_pph_id;
OKL_LLA_UTIL_PVT.check_line_update_allowed
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_id => l_pph_rec.cle_id);
Okl_Pyd_Pvt.insert_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_ppydv_rec,
x_ppydv_rec);
-- Need to change contract status to INCOMPLETE when create/update ppy
-- cascade edit status on to lines
IF (p_ppydv_rec.payment_hdr_id IS NOT NULL) THEN
OPEN chr_id_csr (p_ppydv_rec.payment_hdr_id);
PROCEDURE delete_party_payment_dtls(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ppydv_rec IN ppydv_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PARTY_PAYMENT_DTLS';
SELECT pph.cle_id
FROM okl_party_payment_hdr pph,
okl_party_payment_dtls pyd
WHERE pyd.id = p_pyd_id
AND pph.id = pyd.payment_hdr_id;
OKL_LLA_UTIL_PVT.check_line_update_allowed
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_id => l_pyd_rec.cle_id);
Okl_Pyd_Pvt.delete_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_ppydv_rec);
END delete_party_payment_dtls;
PROCEDURE delete_party_payment_dtls(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ppydv_tbl IN ppydv_tbl_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PARTY_PAYMENT_DTLS';
delete_party_payment_dtls(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_ppydv_tbl(i));
END delete_party_payment_dtls;
PROCEDURE update_party_payment_dtls(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ppydv_rec IN ppydv_rec_type,
x_ppydv_rec OUT NOCOPY ppydv_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PARTY_PAYMENT_DTLS';
SELECT distinct(pph.dnz_chr_id)
FROM okl_party_payment_dtls ppy, okl_party_payment_hdr pph
WHERE ppy.payment_hdr_id = p_payment_hdr_id
AND ppy.payment_hdr_id = pph.id;
SELECT pph.cle_id
FROM okl_party_payment_hdr pph,
okl_party_payment_dtls pyd
WHERE pyd.id = p_pyd_id
AND pph.id = pyd.payment_hdr_id;
OKL_LLA_UTIL_PVT.check_line_update_allowed
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cle_id => l_pyd_rec.cle_id);
Okl_Pyd_Pvt.update_row(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_ppydv_rec,
x_ppydv_rec);
-- Need to change contract status to INCOMPLETE when create/update ppy
-- cascade edit status on to lines
IF (p_ppydv_rec.payment_hdr_id IS NOT NULL) THEN
OPEN chr_id_csr (x_ppydv_rec.payment_hdr_id);
END update_party_payment_dtls;
PROCEDURE update_party_payment_dtls(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ppydv_tbl IN ppydv_tbl_type,
x_ppydv_tbl OUT NOCOPY ppydv_tbl_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PARTY_PAYMENT_DTLS';
update_party_payment_dtls(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_ppydv_tbl(i),
x_ppydv_tbl(i));
END update_party_payment_dtls;