8372: SELECT chr.id khr_id
8373: ,chr.contract_number contract_number
8374: ,COUNT(fa_dep.asset_id) no_of_assets
8375: FROM fa_deprn_summary fa_dep
8376: ,fa_books fa_books
8377: ,okc_k_headers_all_b chr
8378: WHERE -- Predicates on fa_deprn_summary
8379: fa_dep.book_type_code = p_book_type_code
8380: AND fa_dep.period_counter = p_period_counter
8381: AND fa_dep.deprn_source_code = 'DEPRN'
8382: AND fa_dep.deprn_run_id IS NOT NULL
8383: AND ( fa_dep.deprn_run_id > p_max_deprn_run_id OR
8384: p_max_deprn_run_id IS NULL )
8385: -- Predicates on fa_books
8386: AND fa_books.transaction_header_id_out IS NULL
8387: AND fa_books.date_ineffective IS NULL
8388: AND fa_books.contract_id IS NOT NULL
8389: -- Join Conditions between fa_deprn_summary and fa_books
8382: AND fa_dep.deprn_run_id IS NOT NULL
8383: AND ( fa_dep.deprn_run_id > p_max_deprn_run_id OR
8384: p_max_deprn_run_id IS NULL )
8385: -- Predicates on fa_books
8386: AND fa_books.transaction_header_id_out IS NULL
8387: AND fa_books.date_ineffective IS NULL
8388: AND fa_books.contract_id IS NOT NULL
8389: -- Join Conditions between fa_deprn_summary and fa_books
8390: AND fa_dep.asset_id = fa_books.asset_id
8383: AND ( fa_dep.deprn_run_id > p_max_deprn_run_id OR
8384: p_max_deprn_run_id IS NULL )
8385: -- Predicates on fa_books
8386: AND fa_books.transaction_header_id_out IS NULL
8387: AND fa_books.date_ineffective IS NULL
8388: AND fa_books.contract_id IS NOT NULL
8389: -- Join Conditions between fa_deprn_summary and fa_books
8390: AND fa_dep.asset_id = fa_books.asset_id
8391: AND fa_dep.book_type_code = fa_books.book_type_code
8384: p_max_deprn_run_id IS NULL )
8385: -- Predicates on fa_books
8386: AND fa_books.transaction_header_id_out IS NULL
8387: AND fa_books.date_ineffective IS NULL
8388: AND fa_books.contract_id IS NOT NULL
8389: -- Join Conditions between fa_deprn_summary and fa_books
8390: AND fa_dep.asset_id = fa_books.asset_id
8391: AND fa_dep.book_type_code = fa_books.book_type_code
8392: -- Join conditions between fa_books and okc_k_headers_all_b
8385: -- Predicates on fa_books
8386: AND fa_books.transaction_header_id_out IS NULL
8387: AND fa_books.date_ineffective IS NULL
8388: AND fa_books.contract_id IS NOT NULL
8389: -- Join Conditions between fa_deprn_summary and fa_books
8390: AND fa_dep.asset_id = fa_books.asset_id
8391: AND fa_dep.book_type_code = fa_books.book_type_code
8392: -- Join conditions between fa_books and okc_k_headers_all_b
8393: AND fa_books.contract_id = chr.id
8386: AND fa_books.transaction_header_id_out IS NULL
8387: AND fa_books.date_ineffective IS NULL
8388: AND fa_books.contract_id IS NOT NULL
8389: -- Join Conditions between fa_deprn_summary and fa_books
8390: AND fa_dep.asset_id = fa_books.asset_id
8391: AND fa_dep.book_type_code = fa_books.book_type_code
8392: -- Join conditions between fa_books and okc_k_headers_all_b
8393: AND fa_books.contract_id = chr.id
8394: GROUP BY chr.id, CHR.contract_number
8387: AND fa_books.date_ineffective IS NULL
8388: AND fa_books.contract_id IS NOT NULL
8389: -- Join Conditions between fa_deprn_summary and fa_books
8390: AND fa_dep.asset_id = fa_books.asset_id
8391: AND fa_dep.book_type_code = fa_books.book_type_code
8392: -- Join conditions between fa_books and okc_k_headers_all_b
8393: AND fa_books.contract_id = chr.id
8394: GROUP BY chr.id, CHR.contract_number
8395: ORDER BY COUNT(fa_dep.asset_id) DESC;
8388: AND fa_books.contract_id IS NOT NULL
8389: -- Join Conditions between fa_deprn_summary and fa_books
8390: AND fa_dep.asset_id = fa_books.asset_id
8391: AND fa_dep.book_type_code = fa_books.book_type_code
8392: -- Join conditions between fa_books and okc_k_headers_all_b
8393: AND fa_books.contract_id = chr.id
8394: GROUP BY chr.id, CHR.contract_number
8395: ORDER BY COUNT(fa_dep.asset_id) DESC;
8396:
8389: -- Join Conditions between fa_deprn_summary and fa_books
8390: AND fa_dep.asset_id = fa_books.asset_id
8391: AND fa_dep.book_type_code = fa_books.book_type_code
8392: -- Join conditions between fa_books and okc_k_headers_all_b
8393: AND fa_books.contract_id = chr.id
8394: GROUP BY chr.id, CHR.contract_number
8395: ORDER BY COUNT(fa_dep.asset_id) DESC;
8396:
8397: -- Cursor to fetch the Maximum Deprecation ID from the FA Extension Line Table