8257: SELECT chr.id khr_id
8258: ,chr.contract_number contract_number
8259: ,COUNT(fa_dep.asset_id) no_of_assets
8260: FROM fa_deprn_summary fa_dep
8261: ,fa_books fa_books
8262: ,okc_k_headers_all_b chr
8263: WHERE -- Predicates on fa_deprn_summary
8264: fa_dep.book_type_code = p_book_type_code
8265: AND fa_dep.period_counter = p_period_counter
8266: AND fa_dep.deprn_source_code = 'DEPRN'
8267: AND fa_dep.deprn_run_id IS NOT NULL
8268: AND ( fa_dep.deprn_run_id > p_max_deprn_run_id OR
8269: p_max_deprn_run_id IS NULL )
8270: -- Predicates on fa_books
8271: AND fa_books.transaction_header_id_out IS NULL
8272: AND fa_books.date_ineffective IS NULL
8273: AND fa_books.contract_id IS NOT NULL
8274: -- Join Conditions between fa_deprn_summary and fa_books
8267: AND fa_dep.deprn_run_id IS NOT NULL
8268: AND ( fa_dep.deprn_run_id > p_max_deprn_run_id OR
8269: p_max_deprn_run_id IS NULL )
8270: -- Predicates on fa_books
8271: AND fa_books.transaction_header_id_out IS NULL
8272: AND fa_books.date_ineffective IS NULL
8273: AND fa_books.contract_id IS NOT NULL
8274: -- Join Conditions between fa_deprn_summary and fa_books
8275: AND fa_dep.asset_id = fa_books.asset_id
8268: AND ( fa_dep.deprn_run_id > p_max_deprn_run_id OR
8269: p_max_deprn_run_id IS NULL )
8270: -- Predicates on fa_books
8271: AND fa_books.transaction_header_id_out IS NULL
8272: AND fa_books.date_ineffective IS NULL
8273: AND fa_books.contract_id IS NOT NULL
8274: -- Join Conditions between fa_deprn_summary and fa_books
8275: AND fa_dep.asset_id = fa_books.asset_id
8276: AND fa_dep.book_type_code = fa_books.book_type_code
8269: p_max_deprn_run_id IS NULL )
8270: -- Predicates on fa_books
8271: AND fa_books.transaction_header_id_out IS NULL
8272: AND fa_books.date_ineffective IS NULL
8273: AND fa_books.contract_id IS NOT NULL
8274: -- Join Conditions between fa_deprn_summary and fa_books
8275: AND fa_dep.asset_id = fa_books.asset_id
8276: AND fa_dep.book_type_code = fa_books.book_type_code
8277: -- Join conditions between fa_books and okc_k_headers_all_b
8270: -- Predicates on fa_books
8271: AND fa_books.transaction_header_id_out IS NULL
8272: AND fa_books.date_ineffective IS NULL
8273: AND fa_books.contract_id IS NOT NULL
8274: -- Join Conditions between fa_deprn_summary and fa_books
8275: AND fa_dep.asset_id = fa_books.asset_id
8276: AND fa_dep.book_type_code = fa_books.book_type_code
8277: -- Join conditions between fa_books and okc_k_headers_all_b
8278: AND fa_books.contract_id = chr.id
8271: AND fa_books.transaction_header_id_out IS NULL
8272: AND fa_books.date_ineffective IS NULL
8273: AND fa_books.contract_id IS NOT NULL
8274: -- Join Conditions between fa_deprn_summary and fa_books
8275: AND fa_dep.asset_id = fa_books.asset_id
8276: AND fa_dep.book_type_code = fa_books.book_type_code
8277: -- Join conditions between fa_books and okc_k_headers_all_b
8278: AND fa_books.contract_id = chr.id
8279: GROUP BY chr.id, CHR.contract_number
8272: AND fa_books.date_ineffective IS NULL
8273: AND fa_books.contract_id IS NOT NULL
8274: -- Join Conditions between fa_deprn_summary and fa_books
8275: AND fa_dep.asset_id = fa_books.asset_id
8276: AND fa_dep.book_type_code = fa_books.book_type_code
8277: -- Join conditions between fa_books and okc_k_headers_all_b
8278: AND fa_books.contract_id = chr.id
8279: GROUP BY chr.id, CHR.contract_number
8280: ORDER BY COUNT(fa_dep.asset_id) DESC;
8273: AND fa_books.contract_id IS NOT NULL
8274: -- Join Conditions between fa_deprn_summary and fa_books
8275: AND fa_dep.asset_id = fa_books.asset_id
8276: AND fa_dep.book_type_code = fa_books.book_type_code
8277: -- Join conditions between fa_books and okc_k_headers_all_b
8278: AND fa_books.contract_id = chr.id
8279: GROUP BY chr.id, CHR.contract_number
8280: ORDER BY COUNT(fa_dep.asset_id) DESC;
8281:
8274: -- Join Conditions between fa_deprn_summary and fa_books
8275: AND fa_dep.asset_id = fa_books.asset_id
8276: AND fa_dep.book_type_code = fa_books.book_type_code
8277: -- Join conditions between fa_books and okc_k_headers_all_b
8278: AND fa_books.contract_id = chr.id
8279: GROUP BY chr.id, CHR.contract_number
8280: ORDER BY COUNT(fa_dep.asset_id) DESC;
8281:
8282: -- Cursor to fetch the Maximum Deprecation ID from the FA Extension Line Table