SQL & PL/SQL

You must have single record for matching criteria in the USING clause, so combinations of PAY_RANGE_START_DATE_KEY, AA_PERSON_NATURAL_KEY, AA_PERSON_ASSIGNMENT_KEY, SCHEDULE_LINE_ID need to be unique to avoid this error. Your ROW_NUMBER() clause does not ensure that, as you also have SRC_CREATED_DATE, SRC_LAST_UPDATE_DATE as part of the partition.

Hi, @karthik-cm
Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.
If you can show what the problem is using commonly available tables (like the tables in the Oracle-supplied SCOTT schema) then you don't need to post any sample data; just the results and explanation.
Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

You're using ROW_NUMBER to guarantee that rn = 1 for only 1 row in for each distinct combination of 6 columns, but the ON clause only references 4 of those 6 columns. Depending on your requirements, you need to change the analytic PARTITION BY clause or the ON conditions (or both) so that they reference exactly the same columns.

Hi, Karthik.CM
Correction:
Depending on your requirements, you need to change the analytic PARTITION BY clause or the ON conditions (or both) so that they reference exactly the same columns.
They don't need to be exactly the same. It's okay if the the ON condition has more columns than the PARTITION BY clause, but not vice-versa.
For example, you're doing something analogous to this:

MERGE INTO emp  dst
USING (
      WITH  got_rn  AS
	 (
		SELECT  deptno, job
		, 	sal * 1.1	AS new_sal
		,	ROW_NUMBER () OVER ( PARTITION BY deptno, job
				   	     ORDER BY 	  hiredate
				   	   ) AS rn
		FROM	emp
	 )
	 SELECT deptno, job, new_sal
	 FROM	 got_rn
	 WHERE	 rn = 1
    )  src
ON  (  dst.deptno = src.deptno
    )
WHEN MATCHED THEN UPDATE
SET dst.sal = src.new_sal
;

The statement above can raise the ORA-30926 error if there are multiple jobs in the same department.
The statement below will not raise the ORA-30926 error:

MERGE INTO emp  dst
USING (
      WITH  got_rn  AS
	 (
		SELECT  deptno, job
		, 	sal * 1.1	AS new_sal
		,	ROW_NUMBER () OVER ( PARTITION BY deptno
				   	     ORDER BY 	  hiredate
				   	  ) AS rn
		FROM	emp
	 )
	 SELECT deptno, job, new_sal
	 FROM	 got_rn
	 WHERE	 rn = 1
    )  src
ON  (  dst.deptno = src.deptno
   AND dst.job   = src.job
   )
WHEN MATCHED THEN UPDATE
SET dst.sal = src.new_sal
;

The ON condition uses more columns than the PARTITION BY clause, but everything that is in the PARTITION BY clause is also in the ON condition.

@frank-kulash I have somehow fixed the unstable row issue, but now the query performance is taking a very long time can you please give some suggestions on it, This is my query and I have also added the explain plan.

MERGE
/*+ parallel(A) enable_parallel_dml*/
INTO
(
SELECT
PAY_RANGE_START_DATE_KEY,
AA_PERSON_NATURAL_KEY,
AA_PERSON_ASSIGNMENT_KEY,
SCHEDULE_LINE_ID,
SRC_CREATED_DATE,
SRC_LAST_UPDATE_DATE
FROM
EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP
)
A USING
(
SELECT
pay_range_start_date_key,
aa_person_natural_key,
aa_person_assignment_key,
schedule_line_id,
MAX(src_created_date) src_created_date,
MAX(src_last_update_date) src_last_update_date
FROM
EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP_FRS_356
GROUP BY
pay_range_start_date_key,
aa_person_natural_key,
aa_person_assignment_key,
schedule_line_id
)
B ON
(
A.PAY_RANGE_START_DATE_KEY = B.PAY_RANGE_START_DATE_KEY AND
A.AA_PERSON_NATURAL_KEY = B.AA_PERSON_NATURAL_KEY AND
A.AA_PERSON_ASSIGNMENT_KEY = B.AA_PERSON_ASSIGNMENT_KEY AND
A.SCHEDULE_LINE_ID = B.SCHEDULE_LINE_ID
)
WHEN MATCHED THEN
UPDATE
SET
A.SRC_CREATED_DATE = B.SRC_CREATED_DATE,
A.SRC_LAST_UPDATE_DATE = B.SRC_LAST_UPDATE_DATE
WHERE
A.SRC_CREATED_DATE <> B.SRC_CREATED_DATE
OR A.SRC_LAST_UPDATE_DATE <> B.SRC_LAST_UPDATE_DATE;
COMMIT;