[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Okl_Rep_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_repv_rec,
x_repv_rec);
PROCEDURE update_report(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repv_rec IN repv_rec_type,
x_repv_rec OUT NOCOPY repv_rec_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'UPDATE_REPORT';
Okl_Rep_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_repv_rec,
x_repv_rec);
END update_report;
PROCEDURE delete_report(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repv_rec IN repv_rec_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'DELETE_REPORT';
Okl_Rep_Pvt.delete_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_repv_rec);
END delete_report;
SELECT REPORT_ID
,NAME
,CHART_OF_ACCOUNTS_ID
,BOOK_CLASSIFICATION_CODE
,LEDGER_ID
,REPORT_CATEGORY_CODE
,REPORT_TYPE_CODE
,ACTIVITY_CODE
,STATUS_CODE
,DESCRIPTION
,EFFECTIVE_FROM_DATE
,EFFECTIVE_TO_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
FROM OKL_REPORTS_V
WHERE REPORT_ID = p_rep_id;
,l_repv_rec.last_updated_by
,l_repv_rec.last_update_date
,l_repv_rec.last_update_login
,l_repv_rec.language
,l_repv_rec.source_lang
,l_repv_rec.sfwt_flag;
Okl_Rep_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_repv_rec,
x_repv_rec);
SELECT REPORT_ID
,NAME
,CHART_OF_ACCOUNTS_ID
,BOOK_CLASSIFICATION_CODE
,LEDGER_ID
,REPORT_CATEGORY_CODE
,REPORT_TYPE_CODE
,ACTIVITY_CODE
,STATUS_CODE
,DESCRIPTION
,EFFECTIVE_FROM_DATE
,EFFECTIVE_TO_DATE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LANGUAGE
,SOURCE_LANG
,SFWT_FLAG
FROM OKL_REPORTS_V
WHERE REPORT_ID = p_rep_id;
,l_repv_rec.last_updated_by
,l_repv_rec.last_update_date
,l_repv_rec.last_update_login
,l_repv_rec.language
,l_repv_rec.source_lang
,l_repv_rec.sfwt_flag;
Okl_Rep_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_repv_rec,
x_repv_rec);
Okl_Rep_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_repv_tbl,
x_repv_tbl);
PROCEDURE update_report(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repv_tbl IN repv_tbl_type,
x_repv_tbl OUT NOCOPY repv_tbl_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'UPDATE_REPORT';
Okl_Rep_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_repv_tbl,
x_repv_tbl);
END update_report;
PROCEDURE delete_report(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repv_tbl IN repv_tbl_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'DELETE_REPORT';
Okl_Rep_Pvt.delete_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_repv_tbl);
END delete_report;
Okl_Rpp_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rpp_rec,
x_rpp_rec);
PROCEDURE update_report_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rpp_rec IN rpp_rec_type,
x_rpp_rec OUT NOCOPY rpp_rec_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'UPDATE_REPORT_PARAMETERS';
Okl_Rpp_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rpp_rec,
x_rpp_rec);
END update_report_parameters;
PROCEDURE delete_report_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rpp_rec IN rpp_rec_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'DELETE_REPORT_PARAMETERS';
Okl_Rpp_Pvt.delete_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rpp_rec);
END delete_report_parameters;
Okl_Rpp_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rpp_tbl,
x_rpp_tbl);
PROCEDURE update_report_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rpp_tbl IN rpp_tbl_type,
x_rpp_tbl OUT NOCOPY rpp_tbl_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'UPDATE_REPORT_PARAMETERS';
Okl_Rpp_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rpp_tbl,
x_rpp_tbl);
END update_report_parameters;
PROCEDURE delete_report_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rpp_tbl IN rpp_tbl_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'DELETE_REPORT_PARAMETERS';
Okl_Rpp_Pvt.delete_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rpp_tbl);
END delete_report_parameters;
Okl_Rap_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rap_rec,
x_rap_rec);
PROCEDURE update_report_acc_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rap_rec IN rap_rec_type,
x_rap_rec OUT NOCOPY rap_rec_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'UPDATE_REPORT_ACC_PARAMS';
Okl_Rap_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rap_rec,
x_rap_rec);
END update_report_acc_parameters;
PROCEDURE delete_report_acc_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rap_rec IN rap_rec_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'DELETE_REPORT_ACC_PARAMS';
Okl_Rap_Pvt.delete_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rap_rec);
END delete_report_acc_parameters;
Okl_Rap_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rap_tbl,
x_rap_tbl);
PROCEDURE update_report_acc_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rap_tbl IN rap_tbl_type,
x_rap_tbl OUT NOCOPY rap_tbl_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'UPDATE_REPORT_ACC_PARAMS';
Okl_Rap_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rap_tbl,
x_rap_tbl);
END update_report_acc_parameters;
PROCEDURE delete_report_acc_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rap_tbl IN rap_tbl_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'DELETE_REPORT_ACC_PARAMS';
Okl_Rap_Pvt.delete_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rap_tbl);
END delete_report_acc_parameters;
Okl_Rsp_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rps_rec,
x_rps_rec);
PROCEDURE update_report_strm_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rps_rec IN rps_rec_type,
x_rps_rec OUT NOCOPY rps_rec_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'UPDATE_REPORT_STRM_PARAMS';
Okl_Rsp_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rps_rec,
x_rps_rec);
END update_report_strm_parameters;
PROCEDURE delete_report_strm_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rps_rec IN rps_rec_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'DELETE_REPORT_STRM_PARAMS';
Okl_Rsp_Pvt.delete_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rps_rec);
END delete_report_strm_parameters;
Okl_Rsp_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rps_tbl,
x_rps_tbl);
PROCEDURE update_report_strm_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rps_tbl IN rps_tbl_type,
x_rps_tbl OUT NOCOPY rps_tbl_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'UPDATE_REPORT_STRM_PARAMS';
Okl_Rsp_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rps_tbl,
x_rps_tbl);
END update_report_strm_parameters;
PROCEDURE delete_report_strm_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rps_tbl IN rps_tbl_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'DELETE_REPORT_STRM_PARAMS';
Okl_Rsp_Pvt.delete_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rps_tbl);
END delete_report_strm_parameters;
Okl_Rtp_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rtp_rec,
x_rtp_rec);
PROCEDURE update_report_trx_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rtp_rec IN rtp_rec_type,
x_rtp_rec OUT NOCOPY rtp_rec_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'UPDATE_REPORT_TRX_PARAMS';
Okl_Rtp_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rtp_rec,
x_rtp_rec);
END update_report_trx_parameters;
PROCEDURE delete_report_trx_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rtp_rec IN rtp_rec_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'DELETE_REPORT_TRX_PARAMS';
Okl_Rtp_Pvt.delete_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rtp_rec);
END delete_report_trx_parameters;
Okl_Rtp_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rtp_tbl,
x_rtp_tbl);
PROCEDURE update_report_trx_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rtp_tbl IN rtp_tbl_type,
x_rtp_tbl OUT NOCOPY rtp_tbl_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'UPDATE_REPORT_TRX_PARAMS';
Okl_Rtp_Pvt.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rtp_tbl,
x_rtp_tbl);
END update_report_trx_parameters;
PROCEDURE delete_report_trx_parameters(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rtp_tbl IN rtp_tbl_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'DELETE_REPORT_TRX_PARAMS';
Okl_Rtp_Pvt.delete_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_rtp_tbl);
END delete_report_trx_parameters;
SELECT flex.flex_value Account_Number
FROM fnd_id_flex_segments s,
fnd_segment_attribute_values sav,
fnd_flex_values_vl flex
WHERE s.application_id = 101 -- GL Application ID
AND s.id_flex_code = 'GL#'
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = 101
AND sav.id_flex_code = 'GL#'
AND sav.id_flex_num = s.id_flex_num
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = 'GL_ACCOUNT'
AND s.flex_value_set_id = flex.flex_value_set_id
AND s.id_flex_num = p_coa_id
AND flex.flex_value_id = p_segment_id;
SELECT 1
FROM OKL_REPORT_PARAMETERS
WHERE PARAMETER_ID = p_param_id;
SELECT 1
FROM OKL_REPORT_ACC_PARAMS
WHERE ACC_PARAMETER_ID = p_acc_param_id;
SELECT 1
FROM OKL_REPORT_STREAM_PARAMS
WHERE STREAM_PARAMETER_ID = p_strm_param_id;
SELECT 1
FROM OKL_REPORT_TRX_PARAMS
WHERE TRX_PARAMETER_ID = p_trx_param_id;
PROCEDURE update_report(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_repv_rec IN repv_rec_type,
x_repv_rec OUT NOCOPY repv_rec_type,
p_rpp_tbl IN rpp_tbl_type,
x_rpp_tbl OUT NOCOPY rpp_tbl_type,
p_rap_tbl IN rap_tbl_type,
x_rap_tbl OUT NOCOPY rap_tbl_type,
p_rps_tbl IN rps_tbl_type,
x_rps_tbl OUT NOCOPY rps_tbl_type,
p_rtp_tbl IN rtp_tbl_type,
x_rtp_tbl OUT NOCOPY rtp_tbl_type
) IS
l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(30) := 'UPDATE_REPORT';
update_report(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
p_repv_rec,
x_repv_rec);
update_report_parameters(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_upd_rpp_tbl,
x_upd_rpp_tbl);
delete_report_parameters(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_del_rpp_tbl);
update_report_acc_parameters(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_upd_rap_tbl,
x_upd_rap_tbl);
update_report_strm_parameters(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_upd_rps_tbl,
x_upd_rps_tbl);
update_report_trx_parameters(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_upd_rtp_tbl,
x_upd_rtp_tbl);
END update_report;
PROCEDURE insert_gt(p_contract_number IN okc_k_headers_all_b.contract_number%TYPE,
p_party_name in hz_parties.party_name%TYPE,
p_account_number IN hz_cust_accounts.account_number%TYPE ,
p_cust_site_name IN okx_cust_sites_v.description%TYPE,
p_inv_msg IN fnd_new_messages.message_text%TYPE,
p_value IN VARCHAR2
) IS
-------------------------------------------------------------------------------
-- PROCEDURE insert_gt
-------------------------------------------------------------------------------
-- Start of comments
--
-- Procedure Name : insert_gt
-- Description : Inserts into the global temporary table.
-- Business Rules :
-- Parameters :
-- Version : 1.0
-- History : 31-Jan-2007 schodava created.
-- End of comments
-------------------------------------------------------------------------------
BEGIN
INSERT INTO okl_g_reports_gt
(value1_text,
value2_text,
value3_text,
value4_text,
value5_text,
value6_text)
VALUES
(p_contract_number,
p_party_name,
p_account_number,
p_cust_site_name,
p_inv_msg,
p_value);
-- Inserting GL Concatenated segment value into GT table
-- Created a new procedure insert_gt for inserting into gt table
-- Introduced contract line level bank account validation
-- Contract Line Level validation now overrides header level
-- End of comments
-------------------------------------------------------------------------------
IS
-- Streams cursor
CURSOR c_streams IS
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name sty_name,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code,
khl.pdt_id pdt_id
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <=
TRUNC((NVL (p_to_bill_date, SYSDATE) + okl_stream_billing_pvt.get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND stm.sty_id = sty.id
AND sty.billable_yn = 'Y'
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED')
AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
AND khl.id = khr.id
AND stm.khr_id = khl.id
AND khl.deal_type IS NOT NULL
AND khr.sts_code = khs.code
AND khs.ste_code = 'ACTIVE'
AND stm.kle_id = kle.id (+)
AND kle.sts_code = kls.code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED' , 'EXPIRED')
ORDER BY 1,2,3;
SELECT cs.cust_acct_site_id
FROM okc_k_headers_v khr
, okx_cust_site_uses_v cs
, hz_customer_profiles cp
WHERE khr.id = cp_khr_id
AND khr.bill_to_site_use_id = cs.id1
AND khr.bill_to_site_use_id = cp.site_use_id(+);
SELECT cust_acct_id,
authoring_org_id
FROM okc_k_headers_v
WHERE id = p_khr_id;
SELECT name,
hca.account_number
FROM okx_parties_v hp,
hz_cust_accounts hca
WHERE hp.id1 = hca.party_id
AND hca.cust_account_id = cp_cust_acct_id;
SELECT description
FROM okx_cust_sites_v
WHERE cust_acct_site_id = cp_cust_acct_site_id;
SELECT object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LABILL' AND
rgp.dnz_chr_id = rgp.chr_id AND
rul.rule_information_category = 'LAPMTH' AND
rgp.dnz_chr_id = p_khr_id;
SELECT object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LABILL' AND
rgp.cle_id = p_kle_id AND
rul.rule_information_category = 'LAPMTH' AND
rgp.dnz_chr_id = p_khr_id
UNION
SELECT rul.object1_id1
FROM okc_k_lines_b cle
, okc_k_items_v item
, okc_k_lines_b linked_asset
, OKC_RULES_B rul
, Okc_rule_groups_B rgp
WHERE cle.dnz_chr_id = p_khr_id AND
cle.id = p_kle_id AND
cle.chr_id IS NULL AND
cle.id = item.cle_id AND
item.object1_id1 = linked_asset.id AND
linked_asset.id = rgp.cle_id AND
linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
rgp.rgd_code = 'LABILL' AND
rul.rgp_id = rgp.id AND
rul.rule_information_category = 'LAPMTH';
SELECT c.receipt_method_id,
c.name
FROM okx_receipt_methods_v c
WHERE c.id1 = p_cust_rct_mthd;
SELECT C.CREATION_METHOD_CODE
FROM AR_RECEIPT_METHODS M,
AR_RECEIPT_CLASSES C
WHERE M.RECEIPT_CLASS_ID = C.RECEIPT_CLASS_ID AND
M.receipt_method_id = p_rct_method_id;
SELECT cs.cust_acct_site_id
FROM okc_k_headers_b khr
, okx_cust_site_uses_v cs
, okc_k_lines_b cle
, hz_customer_profiles cp
WHERE khr.id = p_khr_id
AND cle.dnz_chr_id = khr.id
AND cle.chr_id IS NOT NULL
AND cle.id = p_kle_id
AND cle.BILL_TO_SITE_USE_ID = cs.id1
AND khr.bill_to_site_use_id = cp.site_use_id(+)
UNION
SELECT cs.cust_acct_site_id
FROM okc_k_headers_b khr
, okc_k_lines_b cle
, okc_k_items item
, okc_k_lines_b linked_asset
, okx_cust_site_uses_v cs
, hz_customer_profiles cp
WHERE khr.id = p_khr_id
AND cle.dnz_chr_id = khr.id
AND cle.id = p_kle_id
AND cle.chr_id IS NULL
AND cle.id = item.cle_id
AND item.object1_id1 = linked_asset.id
AND linked_asset.BILL_TO_SITE_USE_ID = cs.id1
AND khr.bill_to_site_use_id = cp.site_use_id(+);
SELECT 'X'
FROM ra_cust_receipt_methods CPM
WHERE cpm.customer_id = cp_customer_id
AND cpm.receipt_method_id = cp_receipt_method_id
AND cp_trx_date BETWEEN NVL(CPM.START_DATE, cp_trx_date)
AND NVL(CPM.END_DATE, cp_trx_date);
SELECT 'X'
FROM AR_RECEIPT_METHODS CPM
WHERE cpm.receipt_method_id = cp_receipt_method_id
AND cp_trx_date BETWEEN NVL(CPM.START_DATE, cp_trx_date)
AND NVL(CPM.END_DATE, cp_trx_date);
SELECT 'X'
FROM hz_cust_acct_sites acct_site,
hz_cust_site_uses site_use
WHERE acct_site.cust_acct_site_id = cp_cust_accountsite_id
AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id
AND site_use.site_use_code = 'BILL_TO'
AND acct_site.status = 'A'
AND site_use.status = 'A';
SELECT DECODE (SIGN ( TRUNC (SYSDATE) - pym_instr.start_date),-1, 'I',
DECODE (SIGN ( TRUNC (SYSDATE)- NVL (pym_instr.end_date, TRUNC (SYSDATE))),1, 'I','A')) instr_status,
bnk.bank_account_number,
DECODE (SIGN ( TRUNC (SYSDATE)- NVL (bnk.start_date, TRUNC (SYSDATE))), -1, 'I',
DECODE (SIGN ( TRUNC (SYSDATE)- NVL (bnk.end_date, TRUNC (SYSDATE))), 1, 'I','A') ) bnk_status
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
iby_pmt_instr_uses_all pym_instr,
iby_ext_bank_accounts_v bnk
WHERE rul.rgp_id = rgp.id
AND rgp.rgd_code = 'LABILL'
AND rgp.dnz_chr_id = rgp.chr_id
AND rgp.dnz_chr_id = cp_khr_id
AND rul.rule_information_category = 'LABACC'
AND rul.object1_id1 = pym_instr.instrument_payment_use_id
AND pym_instr.instrument_id = bnk.bank_account_id
AND pym_instr.instrument_type = 'BANKACCOUNT';
SELECT object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.cle_id = cp_kle_id AND
rgp.rgd_code = 'LABILL' AND
rul.rule_information_category = 'LABACC' AND
rgp.dnz_chr_id = cp_khr_id
UNION
SELECT rul.object1_id1
FROM okc_k_lines_b cle
, okc_k_items_v item
, okc_k_lines_b linked_asset
, OKC_RULES_B rul
, Okc_rule_groups_B rgp
WHERE cle.dnz_chr_id = cp_khr_id AND
cle.id = cp_kle_id AND
cle.chr_id IS NULL AND
cle.id = item.cle_id AND
item.object1_id1 = linked_asset.id AND
linked_asset.id = rgp.cle_id AND
linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
rgp.rgd_code = 'LABILL' AND
rul.rgp_id = rgp.id AND
rul.rule_information_category = 'LABACC';
SELECT DECODE (SIGN ( TRUNC (SYSDATE) - pym_instr.start_date),-1, 'I',
DECODE (SIGN ( TRUNC (SYSDATE)- NVL (pym_instr.end_date, TRUNC (SYSDATE))),1, 'I','A')) instr_status,
bnk.bank_account_number,
DECODE (SIGN ( TRUNC (SYSDATE)- NVL (bnk.start_date, TRUNC (SYSDATE))), -1, 'I',
DECODE (SIGN ( TRUNC (SYSDATE)- NVL (bnk.end_date, TRUNC (SYSDATE))), 1, 'I','A') ) bnk_status
FROM iby_pmt_instr_uses_all pym_instr,
iby_ext_bank_accounts_v bnk
WHERE pym_instr.instrument_payment_use_id = cp_instrument_payment_use_id
AND pym_instr.instrument_id = bnk.bank_account_id
AND pym_instr.instrument_type = 'BANKACCOUNT';
SELECT aetl.code_combination_id,
glv.concatenated_segments
FROM okl_ae_tmpt_lnes aetl,
okl_ae_templates aet,
okl_ae_tmpt_sets aes,
okl_products pdt,
okl_trx_types_b trx,
gl_code_combinations_kfv glv
WHERE aetl.avl_id = aet.id
AND aet.aes_id = aes.id
AND aes.id = pdt.aes_id
AND pdt.id = cp_pdt_id
AND aet.sty_id = cp_sty_id
AND aet.try_id = trx.id
AND trx.aep_code IN ('CREDIT_MEMO', 'BILLING','ADJUSTMENTS')
AND aetl.code_combination_id = glv.code_combination_id;
SELECT 'x'
FROM gl_code_combinations
WHERE code_combination_id = cp_ccid
AND enabled_flag = 'Y'
AND cp_bill_date BETWEEN NVL(start_date_active,cp_bill_date)
AND NVL(end_date_active, cp_bill_date);
bill_tbl.DELETE;
-- insert into gt table
insert_gt(bill_tbl(k).contract_number,
l_party_name,
l_account_number,
l_cust_site_name,
l_inv_bill_to_site_msg,
l_cust_site_name);
-- insert into gt table
insert_gt(bill_tbl(k).contract_number,
l_party_name,
l_account_number,
l_cust_site_name,
l_inv_pmt_method_msg,
l_ext_line_receipt_method_name);
-- insert into gt table
insert_gt(bill_tbl(k).contract_number,
l_party_name,
l_account_number,
l_cust_site_name,
l_inv_bank_account_msg,
l_validate_bank_account.bank_account_number);
-- insert into gt table
insert_gt(bill_tbl(k).contract_number,
l_party_name,
l_account_number,
l_cust_site_name,
l_inv_bill_to_site_msg,
l_cust_site_name);
-- insert into gt table
insert_gt(bill_tbl(k).contract_number,
l_party_name,
l_account_number,
l_cust_site_name,
l_inv_pmt_method_msg,
l_ext_receipt_method_name);
-- insert into gt table
insert_gt(bill_tbl(k).contract_number,
l_party_name,
l_account_number,
l_cust_site_name,
l_inv_bank_account_msg,
l_validate_k_bank_account.bank_account_number);
-- insert into gt table
insert_gt(bill_tbl(k).contract_number,
l_party_name,
l_account_number,
l_cust_site_name,
l_inv_ccid_msg,
l_ccid.concatenated_segments);