4: -- function to retrieve new and old customer id's.
5: --
6: FUNCTION RETRIEVE_AGMT_CUSTOMER_ID(
7: set_no IN NUMBER,
8: var_agmt_customer_id IN pa_agreements.customer_id%TYPE,
9: agmt_new_customer_id OUT NOCOPY pa_agreements.customer_id%TYPE,/*file.sql.39*/
10: agmt_old_customer_id OUT NOCOPY pa_agreements.customer_id%TYPE /*file.sql.39*/)
11: RETURN BOOLEAN IS
12: BEGIN
5: --
6: FUNCTION RETRIEVE_AGMT_CUSTOMER_ID(
7: set_no IN NUMBER,
8: var_agmt_customer_id IN pa_agreements.customer_id%TYPE,
9: agmt_new_customer_id OUT NOCOPY pa_agreements.customer_id%TYPE,/*file.sql.39*/
10: agmt_old_customer_id OUT NOCOPY pa_agreements.customer_id%TYPE /*file.sql.39*/)
11: RETURN BOOLEAN IS
12: BEGIN
13: --
6: FUNCTION RETRIEVE_AGMT_CUSTOMER_ID(
7: set_no IN NUMBER,
8: var_agmt_customer_id IN pa_agreements.customer_id%TYPE,
9: agmt_new_customer_id OUT NOCOPY pa_agreements.customer_id%TYPE,/*file.sql.39*/
10: agmt_old_customer_id OUT NOCOPY pa_agreements.customer_id%TYPE /*file.sql.39*/)
11: RETURN BOOLEAN IS
12: BEGIN
13: --
14: agmt_new_customer_id := NULL;
30: RETURN FALSE;
31: END RETRIEVE_AGMT_CUSTOMER_ID;
32: --
33: FUNCTION CHECK_FOR_DUPLICATE_INDEX(
34: agmt_new_customer_id IN pa_agreements.customer_id%TYPE,
35: var_agmt_agreement_num IN pa_agreements.agreement_num%TYPE,
36: var_agmt_agreement_type IN pa_agreements.agreement_type%TYPE )
37: --
38: RETURN BOOLEAN IS
31: END RETRIEVE_AGMT_CUSTOMER_ID;
32: --
33: FUNCTION CHECK_FOR_DUPLICATE_INDEX(
34: agmt_new_customer_id IN pa_agreements.customer_id%TYPE,
35: var_agmt_agreement_num IN pa_agreements.agreement_num%TYPE,
36: var_agmt_agreement_type IN pa_agreements.agreement_type%TYPE )
37: --
38: RETURN BOOLEAN IS
39: dummy_customer_id pa_agreements.customer_id%TYPE;
32: --
33: FUNCTION CHECK_FOR_DUPLICATE_INDEX(
34: agmt_new_customer_id IN pa_agreements.customer_id%TYPE,
35: var_agmt_agreement_num IN pa_agreements.agreement_num%TYPE,
36: var_agmt_agreement_type IN pa_agreements.agreement_type%TYPE )
37: --
38: RETURN BOOLEAN IS
39: dummy_customer_id pa_agreements.customer_id%TYPE;
40: dummy_agreement_num pa_agreements.agreement_num%TYPE;
35: var_agmt_agreement_num IN pa_agreements.agreement_num%TYPE,
36: var_agmt_agreement_type IN pa_agreements.agreement_type%TYPE )
37: --
38: RETURN BOOLEAN IS
39: dummy_customer_id pa_agreements.customer_id%TYPE;
40: dummy_agreement_num pa_agreements.agreement_num%TYPE;
41: dummy_agreement_type pa_agreements.agreement_type%TYPE;
42: --
43: BEGIN
36: var_agmt_agreement_type IN pa_agreements.agreement_type%TYPE )
37: --
38: RETURN BOOLEAN IS
39: dummy_customer_id pa_agreements.customer_id%TYPE;
40: dummy_agreement_num pa_agreements.agreement_num%TYPE;
41: dummy_agreement_type pa_agreements.agreement_type%TYPE;
42: --
43: BEGIN
44: --
37: --
38: RETURN BOOLEAN IS
39: dummy_customer_id pa_agreements.customer_id%TYPE;
40: dummy_agreement_num pa_agreements.agreement_num%TYPE;
41: dummy_agreement_type pa_agreements.agreement_type%TYPE;
42: --
43: BEGIN
44: --
45: SELECT CUSTOMER_ID, AGREEMENT_NUM, AGREEMENT_TYPE
43: BEGIN
44: --
45: SELECT CUSTOMER_ID, AGREEMENT_NUM, AGREEMENT_TYPE
46: INTO dummy_customer_id, dummy_agreement_num, dummy_agreement_type
47: FROM PA_AGREEMENTS PA
48: WHERE PA.CUSTOMER_ID = agmt_new_customer_id
49: AND PA.AGREEMENT_NUM = var_agmt_agreement_num
50: AND PA.AGREEMENT_TYPE = var_agmt_agreement_type;
51: --
60: --
61: END CHECK_FOR_DUPLICATE_INDEX;
62: --
63: FUNCTION UPDATE_FOR_DUPLICATE_INDEX(
64: agmt_new_customer_id IN pa_agreements.customer_id%TYPE,
65: var_agmt_agreement_num IN pa_agreements.agreement_num%TYPE,
66: var_agmt_agreement_id IN pa_agreements.agreement_id%TYPE,
67: var_agmt_agreement_type IN pa_agreements.agreement_type%TYPE,
68: seq_index IN NUMBER,
61: END CHECK_FOR_DUPLICATE_INDEX;
62: --
63: FUNCTION UPDATE_FOR_DUPLICATE_INDEX(
64: agmt_new_customer_id IN pa_agreements.customer_id%TYPE,
65: var_agmt_agreement_num IN pa_agreements.agreement_num%TYPE,
66: var_agmt_agreement_id IN pa_agreements.agreement_id%TYPE,
67: var_agmt_agreement_type IN pa_agreements.agreement_type%TYPE,
68: seq_index IN NUMBER,
69: duplicate_index_value OUT NOCOPY BOOLEAN ,/*file.sql.39*/
62: --
63: FUNCTION UPDATE_FOR_DUPLICATE_INDEX(
64: agmt_new_customer_id IN pa_agreements.customer_id%TYPE,
65: var_agmt_agreement_num IN pa_agreements.agreement_num%TYPE,
66: var_agmt_agreement_id IN pa_agreements.agreement_id%TYPE,
67: var_agmt_agreement_type IN pa_agreements.agreement_type%TYPE,
68: seq_index IN NUMBER,
69: duplicate_index_value OUT NOCOPY BOOLEAN ,/*file.sql.39*/
70: request_id IN Number,
63: FUNCTION UPDATE_FOR_DUPLICATE_INDEX(
64: agmt_new_customer_id IN pa_agreements.customer_id%TYPE,
65: var_agmt_agreement_num IN pa_agreements.agreement_num%TYPE,
66: var_agmt_agreement_id IN pa_agreements.agreement_id%TYPE,
67: var_agmt_agreement_type IN pa_agreements.agreement_type%TYPE,
68: seq_index IN NUMBER,
69: duplicate_index_value OUT NOCOPY BOOLEAN ,/*file.sql.39*/
70: request_id IN Number,
71: cust_merge_head_id IN ra_customer_merges.CUSTOMER_MERGE_HEADER_ID%TYPE)/*Added for TCA AUDIT */
80: final_agreement_num VARCHAR2(20);
81: */
82:
83: /* Code change for enhancement 1593520 */
84: final_agreement_num PA_AGREEMENTS.agreement_num%TYPE;
85: trunc_agreement_num PA_AGREEMENTS.agreement_num%TYPE;
86: /* till here */
87: --
88: BEGIN
81: */
82:
83: /* Code change for enhancement 1593520 */
84: final_agreement_num PA_AGREEMENTS.agreement_num%TYPE;
85: trunc_agreement_num PA_AGREEMENTS.agreement_num%TYPE;
86: /* till here */
87: --
88: BEGIN
89: --
129: /* Added for TCA audit */
130: IF pap_cmerge.g_audit_profile = 'Y' THEN
131: --
132: -- It inserts the data into HZ_CUSTOMER_MERGE_LOG table
133: -- for PA_AGREEMENTS table and stamps the new agreement
134: -- number and old agreement number.
135: --
136: INSERT INTO hz_customer_merge_log
137: ( MERGE_LOG_ID,
150: VALUES(
151: HZ_CUSTOMER_MERGE_LOG_S.nextval,
152: cust_merge_head_id,
153: request_id,
154: 'PA_AGREEMENTS_ALL',
155: var_agmt_agreement_id,
156: var_agmt_agreement_num,
157: final_agreement_num,
158: 'U',
163: hz_utility_pub.LAST_UPDATED_BY);
164: END IF;
165: /* End of TCA Audit */
166:
167: UPDATE PA_AGREEMENTS PA -- bug 3891382.
168: SET PA.AGREEMENT_NUM = final_agreement_num
169: WHERE PA.CUSTOMER_ID = agmt_new_customer_id
170: AND PA.AGREEMENT_NUM = var_agmt_agreement_num
171: AND PA.AGREEMENT_TYPE = var_agmt_agreement_type;
183: RETURN FALSE;
184: --
185: END UPDATE_FOR_DUPLICATE_INDEX;
186: --
187: PROCEDURE MERGE_PA_AGREEMENTS ( req_id IN NUMBER, set_no IN NUMBER ) IS
188: CURSOR cursor_2 IS SELECT DISTINCT AG.CUSTOMER_ID,
189: ag.AGREEMENT_ID,
190: ag.AGREEMENT_NUM,
191: ag.AGREEMENT_TYPE,
190: ag.AGREEMENT_NUM,
191: ag.AGREEMENT_TYPE,
192: RACM.CUSTOMER_ID,
193: RACM.CUSTOMER_MERGE_HEADER_ID
194: FROM pa_agreements ag,ra_customer_merges RACM --bug3891382
195: WHERE RACM.DUPLICATE_ID = AG.CUSTOMER_ID
196: AND RACM.PROCESS_FLAG = 'N'
197: AND RACM.SET_NUMBER = set_no
198: AND RACM.CUSTOMER_ID <> RACM.DUPLICATE_ID;
197: AND RACM.SET_NUMBER = set_no
198: AND RACM.CUSTOMER_ID <> RACM.DUPLICATE_ID;
199: --
200:
201: var_agmt_customer_id pa_agreements.customer_id%TYPE;
202: var_racm_customer_id ra_customer_merges.customer_id%TYPE;/*Added for TCA AUDIT */
203: var_cust_merge_header_id ra_customer_merges.CUSTOMER_MERGE_HEADER_ID%TYPE;/*Added for TCA AUDIT */
204: var_agmt_agreement_id pa_agreements.agreement_id%TYPE; /* Added for Tca Audit*/
205: var_agmt_agreement_num pa_agreements.agreement_num%TYPE;
200:
201: var_agmt_customer_id pa_agreements.customer_id%TYPE;
202: var_racm_customer_id ra_customer_merges.customer_id%TYPE;/*Added for TCA AUDIT */
203: var_cust_merge_header_id ra_customer_merges.CUSTOMER_MERGE_HEADER_ID%TYPE;/*Added for TCA AUDIT */
204: var_agmt_agreement_id pa_agreements.agreement_id%TYPE; /* Added for Tca Audit*/
205: var_agmt_agreement_num pa_agreements.agreement_num%TYPE;
206: var_agmt_agreement_type pa_agreements.agreement_type%TYPE;
207: -- agmt_new_customer_id pa_agreements.customer_id%TYPE;
208: -- agmt_old_customer_id pa_agreements.customer_id%TYPE;
201: var_agmt_customer_id pa_agreements.customer_id%TYPE;
202: var_racm_customer_id ra_customer_merges.customer_id%TYPE;/*Added for TCA AUDIT */
203: var_cust_merge_header_id ra_customer_merges.CUSTOMER_MERGE_HEADER_ID%TYPE;/*Added for TCA AUDIT */
204: var_agmt_agreement_id pa_agreements.agreement_id%TYPE; /* Added for Tca Audit*/
205: var_agmt_agreement_num pa_agreements.agreement_num%TYPE;
206: var_agmt_agreement_type pa_agreements.agreement_type%TYPE;
207: -- agmt_new_customer_id pa_agreements.customer_id%TYPE;
208: -- agmt_old_customer_id pa_agreements.customer_id%TYPE;
209: data_found BOOLEAN;
202: var_racm_customer_id ra_customer_merges.customer_id%TYPE;/*Added for TCA AUDIT */
203: var_cust_merge_header_id ra_customer_merges.CUSTOMER_MERGE_HEADER_ID%TYPE;/*Added for TCA AUDIT */
204: var_agmt_agreement_id pa_agreements.agreement_id%TYPE; /* Added for Tca Audit*/
205: var_agmt_agreement_num pa_agreements.agreement_num%TYPE;
206: var_agmt_agreement_type pa_agreements.agreement_type%TYPE;
207: -- agmt_new_customer_id pa_agreements.customer_id%TYPE;
208: -- agmt_old_customer_id pa_agreements.customer_id%TYPE;
209: data_found BOOLEAN;
210: duplicate_index_value BOOLEAN;
203: var_cust_merge_header_id ra_customer_merges.CUSTOMER_MERGE_HEADER_ID%TYPE;/*Added for TCA AUDIT */
204: var_agmt_agreement_id pa_agreements.agreement_id%TYPE; /* Added for Tca Audit*/
205: var_agmt_agreement_num pa_agreements.agreement_num%TYPE;
206: var_agmt_agreement_type pa_agreements.agreement_type%TYPE;
207: -- agmt_new_customer_id pa_agreements.customer_id%TYPE;
208: -- agmt_old_customer_id pa_agreements.customer_id%TYPE;
209: data_found BOOLEAN;
210: duplicate_index_value BOOLEAN;
211: seq_index NUMBER;
204: var_agmt_agreement_id pa_agreements.agreement_id%TYPE; /* Added for Tca Audit*/
205: var_agmt_agreement_num pa_agreements.agreement_num%TYPE;
206: var_agmt_agreement_type pa_agreements.agreement_type%TYPE;
207: -- agmt_new_customer_id pa_agreements.customer_id%TYPE;
208: -- agmt_old_customer_id pa_agreements.customer_id%TYPE;
209: data_found BOOLEAN;
210: duplicate_index_value BOOLEAN;
211: seq_index NUMBER;
212: out_of_limit EXCEPTION;
215: BEGIN
216: --
217: -- update log file to indicate the module being executed.
218: --
219: ARP_MESSAGE.SET_LINE( 'PAP_CMERGE_BB2.MERGE_PA_AGREEMENTS()+' );
220: --
221: -- update log file to indicate the table being updated.
222: --
223: ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
220: --
221: -- update log file to indicate the table being updated.
222: --
223: ARP_MESSAGE.SET_NAME( 'AR', 'AR_UPDATING_TABLE' );
224: ARP_MESSAGE.SET_TOKEN( 'TABLE_NAME', 'PA_AGREEMENTS' );
225: --
226: OPEN cursor_2;
227: --
228: LOOP
226: OPEN cursor_2;
227: --
228: LOOP
229: --
230: -- fetch each row from pa_agreements table.
231: --
232: FETCH cursor_2 INTO var_agmt_customer_id,
233: var_agmt_agreement_id,
234: var_agmt_agreement_num,
282: --
283: IF pap_cmerge.g_audit_profile = 'Y' THEN
284: --
285: -- Inserts data into HZ_CUSTOMER_MERGE_LOG table for
286: -- PA_AGREEMENTS table and stamps agreement id with old
287: -- and new customer id .
288:
289: INSERT INTO hz_customer_merge_log
290: ( MERGE_LOG_ID,
303: VALUES(
304: HZ_CUSTOMER_MERGE_LOG_S.nextval,
305: var_cust_merge_header_id,
306: req_id,
307: 'PA_AGREEMENTS', -- bug 3891382.
308: var_agmt_agreement_id,
309: var_agmt_customer_id,
310: var_racm_customer_id,
311: 'U',
317: END IF;
318:
319: /*End of TCA audit*/
320:
321: UPDATE pa_agreements PA -- bug 3891382.
322: SET CUSTOMER_ID = var_racm_customer_id,
323: LAST_UPDATE_DATE = SYSDATE,
324: LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
325: LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
344: total_record_upd_count := 0;
345: --
346: -- update log file to indicate successful exit of this module.
347: --
348: ARP_MESSAGE.SET_LINE( 'PAP_CMERGE_BB2.MERGE_PA_AGREEMENTS()-' );
349: --
350: EXCEPTION
351: --
352: WHEN out_of_limit THEN
350: EXCEPTION
351: --
352: WHEN out_of_limit THEN
353: ARP_MESSAGE.SET_ERROR( 'Duplicate agreement number exceeding 99' );
354: ARP_MESSAGE.SET_ERROR( 'PAP_CMERGE_BB1.MERGE_PA_AGREEMENTS' );
355: RAISE;
356: WHEN OTHERS THEN
357: ARP_MESSAGE.SET_ERROR( 'PAP_CMERGE_BB1.MERGE_PA_AGREEMENTS' );
358: RAISE;
353: ARP_MESSAGE.SET_ERROR( 'Duplicate agreement number exceeding 99' );
354: ARP_MESSAGE.SET_ERROR( 'PAP_CMERGE_BB1.MERGE_PA_AGREEMENTS' );
355: RAISE;
356: WHEN OTHERS THEN
357: ARP_MESSAGE.SET_ERROR( 'PAP_CMERGE_BB1.MERGE_PA_AGREEMENTS' );
358: RAISE;
359: --
360: END MERGE_PA_AGREEMENTS;
361: --
356: WHEN OTHERS THEN
357: ARP_MESSAGE.SET_ERROR( 'PAP_CMERGE_BB1.MERGE_PA_AGREEMENTS' );
358: RAISE;
359: --
360: END MERGE_PA_AGREEMENTS;
361: --
362: END PAP_CMERGE_BB1;