[Home] [Help]
12: -- History
13: -------------------------------------------------------------------------------------------
14: -- 01-FEB-01 tivanov Created.
15: -- 29-OCT-01 tivanov Added account merge for task's saved search
16: -- in jtf_perz_query_param
17: -- 29-APR-02 sanjeev choudhary Changed the update calls for bug 2288291
18: -- Customer merge issue
19: -- 13-AUG-02 chanik jang Added statments to log the change information
20: -- for the bug 2465855
339: RAISE;
340: END merge_audits;
341:
342: ----------------------------------------------------------------------
343: -- merge_perz(): merge parameter_value in jtf_perz_query_param table.
344: ----------------------------------------------------------------------
345: PROCEDURE merge_perz (
346: p_request_id IN NUMBER,
347: p_set_number IN NUMBER,
353: INDEX BY BINARY_INTEGER;
354: MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
355:
356: TYPE QUERY_PARAM_ID_LIST_TYPE IS TABLE OF
357: JTF_PERZ_QUERY_PARAM.QUERY_PARAM_ID%TYPE
358: INDEX BY BINARY_INTEGER;
359: PRIMARY_KEY_ID1_LIST QUERY_PARAM_ID_LIST_TYPE;
360: PRIMARY_KEY_ID2_LIST QUERY_PARAM_ID_LIST_TYPE;
361:
359: PRIMARY_KEY_ID1_LIST QUERY_PARAM_ID_LIST_TYPE;
360: PRIMARY_KEY_ID2_LIST QUERY_PARAM_ID_LIST_TYPE;
361:
362: TYPE PARAMETER_VALUE_LIST_TYPE IS TABLE OF
363: JTF_PERZ_QUERY_PARAM.PARAMETER_VALUE%TYPE
364: INDEX BY BINARY_INTEGER;
365: VCHAR_COL1_ORIG_LIST PARAMETER_VALUE_LIST_TYPE;
366: VCHAR_COL1_NEW_LIST PARAMETER_VALUE_LIST_TYPE;
367: VCHAR_CN_ORIG_LIST PARAMETER_VALUE_LIST_TYPE;
372: ,yt.QUERY_PARAM_ID
373: ,yt.PARAMETER_VALUE
374: ,cn.QUERY_PARAM_ID
375: ,cn.PARAMETER_VALUE
376: FROM JTF_PERZ_QUERY_PARAM yt
377: , JTF_PERZ_QUERY_PARAM cn
378: , ra_customer_merges m
379: WHERE yt.PARAMETER_VALUE = to_char(m.DUPLICATE_ID)
380: AND m.process_flag = 'N'
373: ,yt.PARAMETER_VALUE
374: ,cn.QUERY_PARAM_ID
375: ,cn.PARAMETER_VALUE
376: FROM JTF_PERZ_QUERY_PARAM yt
377: , JTF_PERZ_QUERY_PARAM cn
378: , ra_customer_merges m
379: WHERE yt.PARAMETER_VALUE = to_char(m.DUPLICATE_ID)
380: AND m.process_flag = 'N'
381: AND m.request_id = p_request_id
384: AND cn.parameter_name = 'CUSTOMER_NAME'
385: AND yt.parameter_name = 'CUSTOMER_ID'
386: AND yt.query_id IN (SELECT q.query_id
387: FROM jtf_perz_query q,
388: jtf_perz_query_param p
389: WHERE q.query_type= 'JTF_TASK'
390: AND q.application_id = 690
391: AND p.query_id = q.query_id
392: AND p.parameter_name = 'CUSTOMER'
397: IF p_process_mode='LOCK' THEN
398: NULL;
399: ELSE
400: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
401: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','JTF_PERZ_QUERY_PARAM',FALSE);
402:
403: HZ_ACCT_MERGE_UTIL.load_set(p_set_number, p_request_id);
404: load_acct_number_set(p_set_number, p_request_id);
405:
444: LAST_UPDATE_DATE,
445: LAST_UPDATED_BY
446: ) VALUES (
447: HZ_CUSTOMER_MERGE_LOG_s.nextval,
448: 'JTF_PERZ_QUERY_PARAM',
449: MERGE_HEADER_ID_LIST(I),
450: PRIMARY_KEY_ID1_LIST(I),
451: VCHAR_COL1_ORIG_LIST(I),
452: VCHAR_COL1_NEW_LIST(I),
476: LAST_UPDATE_DATE,
477: LAST_UPDATED_BY
478: ) VALUES (
479: HZ_CUSTOMER_MERGE_LOG_s.nextval,
480: 'JTF_PERZ_QUERY_PARAM',
481: MERGE_HEADER_ID_LIST(I),
482: PRIMARY_KEY_ID2_LIST(I),
483: VCHAR_CN_ORIG_LIST(I),
484: VCHAR_CN_NEW_LIST(I),
493: END IF;
494:
495: FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
496: -- Update account id
497: UPDATE JTF_PERZ_QUERY_PARAM yt SET
498: PARAMETER_VALUE=VCHAR_COL1_NEW_LIST(I)
499: , LAST_UPDATE_DATE=SYSDATE
500: , last_updated_by=arp_standard.profile.user_id
501: , last_update_login=arp_standard.profile.last_update_login
503: l_count := l_count + SQL%ROWCOUNT;
504:
505: FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
506: -- Update account number
507: UPDATE JTF_PERZ_QUERY_PARAM yt SET
508: PARAMETER_VALUE=VCHAR_CN_NEW_LIST(I)
509: , LAST_UPDATE_DATE=SYSDATE
510: , last_updated_by=arp_standard.profile.user_id
511: , last_update_login=arp_standard.profile.last_update_login