164: |
165: | PROCEDURE
166: | merge_claim_lines
167: | DESCRIPTION :
168: | Account merge procedure for the table, ozf_claim_lines
169: |
170: | NOTES:
171: |
172: |--------------------------------------------------------------*/
181: INDEX BY BINARY_INTEGER;
182: MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
183:
184: TYPE claim_line_id_LIST_TYPE IS TABLE OF
185: ozf_claim_lines.claim_line_id%TYPE
186: INDEX BY BINARY_INTEGER;
187: PRIMARY_KEY_ID1_LIST claim_line_id_LIST_TYPE;
188:
189: TYPE rel_cust_account_id_LIST_TYPE IS TABLE OF
186: INDEX BY BINARY_INTEGER;
187: PRIMARY_KEY_ID1_LIST claim_line_id_LIST_TYPE;
188:
189: TYPE rel_cust_account_id_LIST_TYPE IS TABLE OF
190: ozf_claim_lines.related_cust_account_id%TYPE
191: INDEX BY BINARY_INTEGER;
192: NUM_COL1_ORIG_LIST rel_cust_account_id_LIST_TYPE;
193: NUM_COL1_NEW_LIST rel_cust_account_id_LIST_TYPE;
194:
192: NUM_COL1_ORIG_LIST rel_cust_account_id_LIST_TYPE;
193: NUM_COL1_NEW_LIST rel_cust_account_id_LIST_TYPE;
194:
195: TYPE buy_grp_cust_acct_id_LIST_TYPE IS TABLE OF
196: ozf_claim_lines.buy_group_cust_account_id%TYPE
197: INDEX BY BINARY_INTEGER;
198: NUM_COL2_ORIG_LIST buy_grp_cust_acct_id_LIST_TYPE;
199: NUM_COL2_NEW_LIST buy_grp_cust_acct_id_LIST_TYPE;
200:
203: SELECT distinct CUSTOMER_MERGE_HEADER_ID
204: ,claim_line_id
205: ,related_cust_account_id
206: ,buy_group_cust_account_id
207: FROM ozf_claim_lines yt, ra_customer_merges m
208: WHERE (
209: yt.related_cust_account_id = m.DUPLICATE_ID
210: OR yt.buy_group_cust_account_id = m.DUPLICATE_ID
211: ) AND m.process_flag = 'N'
217: IF process_mode='LOCK' THEN
218: NULL;
219: ELSE
220: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
221: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_claim_lines',FALSE);
222: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
223: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
224:
225: open merged_records;
258: LAST_UPDATE_LOGIN,
259: LAST_UPDATE_DATE,
260: LAST_UPDATED_BY
261: ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
262: 'ozf_claim_lines',
263: MERGE_HEADER_ID_LIST(I),
264: PRIMARY_KEY_ID1_LIST(I),
265: NUM_COL1_ORIG_LIST(I),
266: NUM_COL1_NEW_LIST(I),
275: hz_utility_pub.LAST_UPDATED_BY
276: );
277:
278: END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
279: UPDATE ozf_claim_lines yt SET
280: related_cust_account_id=NUM_COL1_NEW_LIST(I)
281: ,buy_group_cust_account_id=NUM_COL2_NEW_LIST(I)
282: , LAST_UPDATE_DATE=SYSDATE
283: , last_updated_by=arp_standard.profile.user_id
308: |
309: | PROCEDURE
310: | merge_claim_lines_hist
311: | DESCRIPTION :
312: | Account merge procedure for the table, ozf_claim_lines_hist
313: |
314: | NOTES:
315: |
316: |--------------------------------------------------------------*/
325: INDEX BY BINARY_INTEGER;
326: MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
327:
328: TYPE claim_line_hist_id_LIST_TYPE IS TABLE OF
329: ozf_claim_lines_hist.claim_line_history_id%TYPE
330: INDEX BY BINARY_INTEGER;
331: PRIMARY_KEY_ID1_LIST claim_line_hist_id_LIST_TYPE;
332:
333: TYPE rel_cust_account_id_LIST_TYPE IS TABLE OF
330: INDEX BY BINARY_INTEGER;
331: PRIMARY_KEY_ID1_LIST claim_line_hist_id_LIST_TYPE;
332:
333: TYPE rel_cust_account_id_LIST_TYPE IS TABLE OF
334: ozf_claim_lines_hist.related_cust_account_id%TYPE
335: INDEX BY BINARY_INTEGER;
336: NUM_COL1_ORIG_LIST rel_cust_account_id_LIST_TYPE;
337: NUM_COL1_NEW_LIST rel_cust_account_id_LIST_TYPE;
338:
336: NUM_COL1_ORIG_LIST rel_cust_account_id_LIST_TYPE;
337: NUM_COL1_NEW_LIST rel_cust_account_id_LIST_TYPE;
338:
339: TYPE buy_grp_cust_acct_id_LIST_TYPE IS TABLE OF
340: ozf_claim_lines_hist.buy_group_cust_account_id%TYPE
341: INDEX BY BINARY_INTEGER;
342: NUM_COL2_ORIG_LIST buy_grp_cust_acct_id_LIST_TYPE;
343: NUM_COL2_NEW_LIST buy_grp_cust_acct_id_LIST_TYPE;
344:
347: SELECT distinct CUSTOMER_MERGE_HEADER_ID
348: ,claim_line_history_id
349: ,related_cust_account_id
350: ,buy_group_cust_account_id
351: FROM ozf_claim_lines_hist yt, ra_customer_merges m
352: WHERE (
353: yt.related_cust_account_id = m.DUPLICATE_ID
354: OR yt.buy_group_cust_account_id = m.DUPLICATE_ID
355: ) AND m.process_flag = 'N'
361: IF process_mode='LOCK' THEN
362: NULL;
363: ELSE
364: ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
365: ARP_MESSAGE.SET_TOKEN('TABLE_NAME','ozf_claim_lines_hist',FALSE);
366: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
367: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
368:
369: open merged_records;
402: LAST_UPDATE_LOGIN,
403: LAST_UPDATE_DATE,
404: LAST_UPDATED_BY
405: ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
406: 'ozf_claim_lines_hist',
407: MERGE_HEADER_ID_LIST(I),
408: PRIMARY_KEY_ID1_LIST(I),
409: NUM_COL1_ORIG_LIST(I),
410: NUM_COL1_NEW_LIST(I),
419: hz_utility_pub.LAST_UPDATED_BY
420: );
421:
422: END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
423: UPDATE ozf_claim_lines_hist yt SET
424: related_cust_account_id=NUM_COL1_NEW_LIST(I)
425: ,buy_group_cust_account_id=NUM_COL2_NEW_LIST(I)
426: , LAST_UPDATE_DATE=SYSDATE
427: , last_updated_by=arp_standard.profile.user_id