Use latest completed pipeline for scan result policy comparison

What does this MR do and why?

This change updates latest_pipeline_for_target_branch to latest_completed_pipeline_for_target_branch to consider the latest completed pipeline and also consider pipeline with security_orchestration_policy source based on the discussion in https://gitlab.com/gitlab-org/gitlab/-/issues/413097#note_1406484728

The reason we change this is that if a pipeline is running on an MR's target branch, the comparison logic for scan result policies provides inconsistent result because of comparing the security findings against a running pipeline.

Addresses #414017 (closed)

Database query plan

SELECT
    "ci_pipelines"."id",
    "ci_pipelines"."ref",
    "ci_pipelines"."sha",
    "ci_pipelines"."before_sha",
    "ci_pipelines"."created_at",
    "ci_pipelines"."updated_at",
    "ci_pipelines"."tag",
    "ci_pipelines"."yaml_errors",
    "ci_pipelines"."committed_at",
    "ci_pipelines"."project_id",
    "ci_pipelines"."status",
    "ci_pipelines"."started_at",
    "ci_pipelines"."finished_at",
    "ci_pipelines"."duration",
    "ci_pipelines"."user_id",
    "ci_pipelines"."lock_version",
    "ci_pipelines"."auto_canceled_by_id",
    "ci_pipelines"."pipeline_schedule_id",
    "ci_pipelines"."source",
    "ci_pipelines"."config_source",
    "ci_pipelines"."protected",
    "ci_pipelines"."failure_reason",
    "ci_pipelines"."iid",
    "ci_pipelines"."merge_request_id",
    "ci_pipelines"."source_sha",
    "ci_pipelines"."target_sha",
    "ci_pipelines"."external_pull_request_id",
    "ci_pipelines"."ci_ref_id",
    "ci_pipelines"."locked",
    "ci_pipelines"."partition_id" 
FROM
    "ci_pipelines" 
WHERE
    "ci_pipelines"."project_id" = 278964 
    AND (
        "ci_pipelines"."source" IN (
            1, 2, 3, 4, 5, 6, 7, 8, 10, 11, 15
        ) 
        OR "ci_pipelines"."source" IS NULL
    ) 
    AND (
        "ci_pipelines"."status" IN (
            'success','failed','canceled','skipped'
        )
    ) 
    AND "ci_pipelines"."ref" = 'master' 
ORDER BY
    "ci_pipelines"."id" DESC LIMIT 1

Plan console.postgres.ai

 Limit  (cost=0.70..2.22 rows=1 width=350) (actual time=109.106..109.109 rows=1 loops=1)
   Buffers: shared read=15 dirtied=5
   I/O Timings: read=99.998 write=0.000
   ->  Index Scan using index_ci_pipelines_on_project_idandrefandiddesc on public.ci_pipelines  (cost=0.70..474318.20 rows=312595 width=350) (actual time=109.103..109.104 rows=1 loops=1)
         Index Cond: ((ci_pipelines.project_id = 278964) AND ((ci_pipelines.ref)::text = 'master'::text))
         Filter: (((ci_pipelines.status)::text = ANY ('{success,failed,canceled,skipped}'::text[])) AND ((ci_pipelines.source = ANY ('{1,2,3,4,5,6,7,8,10,11,15}'::integer[])) OR (ci_pipelines.source IS NULL)))
         Rows Removed by Filter: 8
         Buffers: shared read=15 dirtied=5
         I/O Timings: read=99.998 write=0.000

Time: 120.542 ms
  - planning: 11.341 ms
  - execution: 109.201 ms
    - I/O read: 99.998 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 0 from the buffer pool
  - reads: 15 (~120.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 5 (~40.00 KiB)
  - writes: 0

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.