3032: Process_Mode IN VARCHAR2)
3033: IS
3034: CURSOR c1 is
3035: select hold_source_id
3036: from oe_hold_sources
3037: where hold_entity_id in (select m.duplicate_site_id
3038: from ra_customer_merges m
3039: where m.process_flag = 'Y'
3040: and m.request_id = req_id
3043: for update nowait;
3044:
3045: CURSOR c2 is
3046: select hold_source_id
3047: from oe_hold_sources
3048: where hold_entity_id in (select m.duplicate_site_id
3049: from ra_customer_merges m
3050: where m.process_flag = 'Y'
3051: and m.request_id = req_id
3055:
3056:
3057: CURSOR c3 is
3058: select hold_source_id
3059: from oe_hold_sources
3060: where hold_entity_id in (select m.duplicate_id
3061: from ra_customer_merges m
3062: where m.process_flag = 'Y'
3063: and m.request_id = req_id
3076: IF l_debug_level > 0 THEN
3077: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_HOLD_MERGE' ) ;
3078: END IF;
3079: /*-----------------------------+
3080: | OE_HOLD_SOURCES |
3081: +-----------------------------*/
3082: /* both customer and site level */
3083:
3084: IF( process_mode = 'LOCK' ) THEN
3082: /* both customer and site level */
3083:
3084: IF( process_mode = 'LOCK' ) THEN
3085: IF l_debug_level > 0 THEN
3086: oe_debug_pub.add( 'LOCKING TABLE OE_HOLD_SOURCES' ) ;
3087: END IF;
3088: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3089: -- arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
3090:
3085: IF l_debug_level > 0 THEN
3086: oe_debug_pub.add( 'LOCKING TABLE OE_HOLD_SOURCES' ) ;
3087: END IF;
3088: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3089: -- arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
3090:
3091: open c1;
3092: close c1;
3093:
3104: IF l_debug_level > 0 THEN
3105: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
3106: END IF;
3107: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3108: -- arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
3109: IF l_debug_level > 0 THEN
3110: oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE S' ) ;
3111: END IF;
3112:
3106: END IF;
3107: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3108: -- arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
3109: IF l_debug_level > 0 THEN
3110: oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE S' ) ;
3111: END IF;
3112:
3113: UPDATE OE_HOLD_SOURCES a
3114: set (hold_entity_id) = (select distinct m.customer_site_id
3109: IF l_debug_level > 0 THEN
3110: oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE S' ) ;
3111: END IF;
3112:
3113: UPDATE OE_HOLD_SOURCES a
3114: set (hold_entity_id) = (select distinct m.customer_site_id
3115: from ra_customer_merges m
3116: where a.hold_entity_id =
3117: m.duplicate_site_id
3144: END IF;
3145: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3146: -- arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
3147: IF l_debug_level > 0 THEN
3148: oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE B' ) ;
3149: END IF;
3150:
3151: UPDATE OE_HOLD_SOURCES a
3152: set (hold_entity_id) = (select distinct m.customer_site_id
3147: IF l_debug_level > 0 THEN
3148: oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE B' ) ;
3149: END IF;
3150:
3151: UPDATE OE_HOLD_SOURCES a
3152: set (hold_entity_id) = (select distinct m.customer_site_id
3153: from ra_customer_merges m
3154: where a.hold_entity_id =
3155:
3183: END IF;
3184: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3185: -- arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
3186: IF l_debug_level > 0 THEN
3187: oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE C' ) ;
3188: END IF;
3189:
3190: UPDATE OE_HOLD_SOURCES a
3191: set hold_entity_id = (select distinct m.customer_id
3186: IF l_debug_level > 0 THEN
3187: oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE C' ) ;
3188: END IF;
3189:
3190: UPDATE OE_HOLD_SOURCES a
3191: set hold_entity_id = (select distinct m.customer_id
3192: from ra_customer_merges m
3193: where a.hold_entity_id =
3194: m.duplicate_id