[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name,
organization_id
FROM hr_operating_units
WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID; --MOAC- Concurrent request
SELECT DISTINCT chrb.currency_code
FROM okl_strm_elements ele,
okl_streams stm,
okl_strm_type_b sty,
okl_k_headers khr,
okc_k_headers_b chrb,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
AND chrb.authoring_org_id = p_org_id
AND chrb.id = stm.khr_id
AND chrb.scs_code IN ('LEASE', 'LOAN')
AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
AND chrb.id = khr.id
AND khr.deal_type IS NOT NULL
AND khs.code = chrb.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.sty_id = sty.id
AND stm.id = ele.stm_id
AND sty.billable_yn = 'Y'
AND stm.say_code <> 'WORK'
AND stm.purpose_code IS NULL
ORDER BY chrb.currency_code DESC;
SELECT SUM(ele.amount) amount
FROM okl_strm_elements ele,
okl_streams stm,
okl_strm_type_b sty,
okl_k_headers khr,
okc_k_headers_b chrb,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
AND chrb.authoring_org_id = p_org_id
AND chrb.currency_code = p_curr_code
AND chrb.id = stm.khr_id
AND chrb.scs_code IN ('LEASE', 'LOAN')
AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
AND chrb.id = khr.id
AND khr.deal_type IS NOT NULL
AND khs.code = chrb.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.sty_id = sty.id
AND stm.id = ele.stm_id
AND sty.billable_yn = 'Y'
AND stm.say_code <> 'WORK'
AND stm.purpose_code IS NULL;
SELECT SUM(ste.amount) amount
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.currency_code = p_curr_code
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NOT NULL
AND stm.say_code IN ('CURR','HIST')
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y';
SELECT SUM(ste.amount) amount
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.currency_code = p_curr_code
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'N'
AND stm.say_code IN ('HIST')
AND stm.purpose_code IS NULL
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y';
SELECT SUM(ste.amount) amount
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.currency_code = p_curr_code
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.khr_id = khr.id
AND stm.id = ste.stm_id
AND ste.amount <> 0
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND stm.purpose_code IS NULL
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y';
SELECT khr.currency_code currency_code,
fnd.meaning book_class,
pdt.name product_name,
SUM(ste.amount) amount
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_products pdt,
fnd_lookup_values fnd,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.khr_id = khr.id
AND stm.id = ste.stm_id
AND ste.amount <> 0
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND stm.purpose_code IS NULL
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND fnd.lookup_code = khl.deal_type
AND fnd.lookup_type = 'OKL_BOOK_CLASS'
AND fnd.LANGUAGE = USERENV('LANG')
AND khl.pdt_id = pdt.id(+)
GROUP BY khr.currency_code,
fnd.meaning,
pdt.name
ORDER BY 1 DESC ;
SELECT chrb.currency_code currency_code,
chrb.contract_number contract_number,
'Y' total_billable_streams,
'N' billed_streams,
'N' cancelled_streams,
'N' unbilled_streams,
SUM(ele.amount) amount
FROM okl_strm_elements ele,
okl_streams stm,
okl_strm_type_b sty,
okl_k_headers khr,
okc_k_headers_b chrb,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
AND chrb.authoring_org_id = p_org_id
AND chrb.id = stm.khr_id
AND chrb.scs_code IN ('LEASE', 'LOAN')
AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
AND chrb.id = khr.id
AND khr.deal_type IS NOT NULL
AND khs.code = chrb.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.sty_id = sty.id
AND stm.id = ele.stm_id
AND sty.billable_yn = 'Y'
AND stm.say_code <> 'WORK'
AND stm.purpose_code IS NULL
GROUP BY chrb.contract_number,
chrb.currency_code
UNION
SELECT khr.currency_code currency_code,
khr.contract_number contract_number,
'N' total_billable_streams,
'Y' billed_streams,
'N' cancelled_streams,
'N' unbilled_streams,
SUM(ste.amount) billed_streams
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NOT NULL
AND stm.say_code IN ('CURR','HIST')
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
GROUP BY khr.contract_number,
khr.currency_code
UNION
SELECT khr.currency_code currency_code,
khr.contract_number contract_number,
'N' total_billable_streams,
'N' billed_streams,
'Y' cancelled_streams,
'N' unbilled_streams,
SUM(ste.amount)
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'N'
AND stm.say_code IN ('HIST')
AND stm.purpose_code IS NULL
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
GROUP BY khr.contract_number,
khr.currency_code
UNION
SELECT khr.currency_code currency_code,
khr.contract_number contract_number,
'N' total_billable_streams,
'N' billed_streams,
'N' cancelled_streams,
'Y' unbilled_streams,
SUM(ste.amount)
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.khr_id = khr.id
AND stm.id = ste.stm_id
AND ste.amount <> 0
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND stm.purpose_code IS NULL
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
GROUP BY khr.contract_number,
khr.currency_code
ORDER BY 1 DESC;
SELECT name,
organization_id
FROM hr_operating_units
WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID; --MOAC- Concurrent request
SELECT DISTINCT chrb.currency_code
FROM okl_strm_elements ele,
okl_streams stm,
okl_strm_type_b sty,
okl_k_headers khr,
okc_k_headers_b chrb,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
AND chrb.authoring_org_id = p_org_id
AND chrb.id = stm.khr_id
AND chrb.scs_code IN ('LEASE', 'LOAN')
AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
AND chrb.id = khr.id
AND khr.deal_type IS NOT NULL
AND khs.code = chrb.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.sty_id = sty.id
AND stm.id = ele.stm_id
AND sty.billable_yn = 'Y'
AND stm.say_code <> 'WORK'
AND stm.purpose_code IS NULL
ORDER BY chrb.currency_code DESC;
SELECT SUM(ele.amount) amount
FROM okl_strm_elements ele,
okl_streams stm,
okl_strm_type_b sty,
okl_k_headers khr,
okc_k_headers_b chrb,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
AND chrb.authoring_org_id = p_org_id
AND chrb.currency_code = p_curr_code
AND chrb.id = stm.khr_id
AND chrb.scs_code IN ('LEASE', 'LOAN')
AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
AND chrb.id = khr.id
AND khr.deal_type IS NOT NULL
AND khs.code = chrb.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.sty_id = sty.id
AND stm.id = ele.stm_id
AND sty.billable_yn = 'Y'
AND stm.say_code <> 'WORK'
AND stm.purpose_code IS NULL;
SELECT SUM(ste.amount) amount
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.currency_code = p_curr_code
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NOT NULL
AND stm.say_code IN ('CURR','HIST')
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y';
SELECT SUM(ste.amount) amount
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.currency_code = p_curr_code
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'N'
AND stm.say_code IN ('HIST')
AND stm.purpose_code IS NULL
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y';
SELECT SUM(ste.amount) amount
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.currency_code = p_curr_code
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.khr_id = khr.id
AND stm.id = ste.stm_id
AND ste.amount <> 0
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND stm.purpose_code IS NULL
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y';
SELECT khr.currency_code currency_code,
fnd.meaning book_class,
pdt.name product_name,
SUM(ste.amount) amount
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_products pdt,
fnd_lookup_values fnd,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.khr_id = khr.id
AND stm.id = ste.stm_id
AND ste.amount <> 0
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND stm.purpose_code IS NULL
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND fnd.lookup_code = khl.deal_type
AND fnd.lookup_type = 'OKL_BOOK_CLASS'
AND fnd.LANGUAGE = USERENV('LANG')
AND khl.pdt_id = pdt.id(+)
GROUP BY khr.currency_code,
fnd.meaning,
pdt.name
ORDER BY 1 DESC ;
SELECT chrb.currency_code currency_code,
chrb.contract_number contract_number,
'Y' total_billable_streams,
'N' billed_streams,
'N' cancelled_streams,
'N' unbilled_streams,
SUM(ele.amount) amount
FROM okl_strm_elements ele,
okl_streams stm,
okl_strm_type_b sty,
okl_k_headers khr,
okc_k_headers_b chrb,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE chrb.contract_number = NVL(p_contract_number,chrb.contract_number)
AND chrb.end_date <= NVL(p_end_date,chrb.end_date)
AND chrb.authoring_org_id = p_org_id
AND chrb.id = stm.khr_id
AND chrb.scs_code IN ('LEASE', 'LOAN')
AND chrb.sts_code IN ( 'BOOKED','EVERGREEN')
AND chrb.id = khr.id
AND khr.deal_type IS NOT NULL
AND khs.code = chrb.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.sty_id = sty.id
AND stm.id = ele.stm_id
AND sty.billable_yn = 'Y'
AND stm.say_code <> 'WORK'
AND stm.purpose_code IS NULL
GROUP BY chrb.contract_number,
chrb.currency_code
UNION
SELECT khr.currency_code currency_code,
khr.contract_number contract_number,
'N' total_billable_streams,
'Y' billed_streams,
'N' cancelled_streams,
'N' unbilled_streams,
SUM(ste.amount) billed_streams
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NOT NULL
AND stm.say_code IN ('CURR','HIST')
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
GROUP BY khr.contract_number,
khr.currency_code
UNION
SELECT khr.currency_code currency_code,
khr.contract_number contract_number,
'N' total_billable_streams,
'N' billed_streams,
'Y' cancelled_streams,
'N' unbilled_streams,
SUM(ste.amount)
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'N'
AND stm.say_code IN ('HIST')
AND stm.purpose_code IS NULL
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
GROUP BY khr.contract_number,
khr.currency_code
UNION
SELECT khr.currency_code currency_code,
khr.contract_number contract_number,
'N' total_billable_streams,
'N' billed_streams,
'N' cancelled_streams,
'Y' unbilled_streams,
SUM(ste.amount)
FROM okl_strm_type_v sty,
okl_strm_elements ste,
okl_streams stm,
okc_statuses_b khs,
okc_statuses_b kls,
okl_k_headers khl,
okc_k_lines_b kle,
okc_k_headers_b khr
WHERE khr.contract_number = NVL(p_contract_number,khr.contract_number)
AND khr.end_date <= NVL(p_end_date,khr.end_date)
AND khr.authoring_org_id = p_org_id
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id(+) = stm.kle_id
AND kls.code(+) = kle.sts_code
AND stm.khr_id = khr.id
AND stm.id = ste.stm_id
AND ste.amount <> 0
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND stm.purpose_code IS NULL
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
GROUP BY khr.contract_number,
khr.currency_code
ORDER BY 1 DESC;
INSERT INTO
OKL_G_REPORTS_GT(VALUE1_TEXT,
VALUE2_TEXT,
VALUE3_TEXT,
VALUE4_TEXT,
VALUE5_TEXT,
VALUE6_TEXT,
VALUE7_TEXT,
VALUE8_TEXT,
VALUE9_TEXT)
VALUES
('SUMMARY',
lt_bill_smry_gt_tbl(i).currency_code,
lt_bill_smry_gt_tbl(i).total_strm,
lt_bill_smry_gt_tbl(i).bill_total_strm,
lt_bill_smry_gt_tbl(i).diff1_total_strm,
lt_bill_smry_gt_tbl(i).cancel_total_strm,
lt_bill_smry_gt_tbl(i).diff2_total_strm,
lt_bill_smry_gt_tbl(i).clobal_total_strm,
lt_bill_smry_gt_tbl(i).main_diff_total
);
INSERT INTO
OKL_G_REPORTS_GT(VALUE1_TEXT,
VALUE2_TEXT,
VALUE3_TEXT,
VALUE4_TEXT,
VALUE5_TEXT,
VALUE1_NUM)
VALUES
('UNBILLED_DTLS',
lt_unbill_gt_tbl(i).currency_code,
lt_unbill_gt_tbl(i).book_class,
lt_unbill_gt_tbl(i).product_name,
okl_accounting_util.format_amount(lt_unbill_gt_tbl(i).amount,lt_unbill_gt_tbl(i).currency_code),
lt_unbill_gt_tbl(i).amount
);
INSERT INTO
OKL_G_REPORTS_GT(VALUE1_TEXT,
VALUE2_TEXT,
VALUE3_TEXT,
VALUE4_TEXT,
VALUE5_TEXT,
VALUE6_TEXT,
VALUE7_TEXT,
VALUE8_TEXT,
VALUE1_NUM,
VALUE2_NUM,
VALUE3_NUM,
VALUE4_NUM,
VALUE5_NUM)
VALUES
('DIFF_DTLS',
lt_diff_gt_tbl(i).currency_code,
lt_diff_gt_tbl(i).contract_number,
okl_accounting_util.format_amount(lt_diff_gt_tbl(i).total_billable_streams,lt_diff_gt_tbl(i).currency_code),
okl_accounting_util.format_amount(lt_diff_gt_tbl(i).billed_streams,lt_diff_gt_tbl(i).currency_code),
okl_accounting_util.format_amount(lt_diff_gt_tbl(i).cancelled_streams,lt_diff_gt_tbl(i).currency_code),
okl_accounting_util.format_amount(lt_diff_gt_tbl(i).unbilled_streams,lt_diff_gt_tbl(i).currency_code),
okl_accounting_util.format_amount(lt_diff_gt_tbl(i).diff_amount,lt_diff_gt_tbl(i).currency_code),
lt_diff_gt_tbl(i).total_billable_streams,
lt_diff_gt_tbl(i).billed_streams,
lt_diff_gt_tbl(i).cancelled_streams,
lt_diff_gt_tbl(i).unbilled_streams,
lt_diff_gt_tbl(i).diff_amount
);