[Home] [Help]
202: )
203: IS
204: CURSOR c_routing_number_id IS
205: SELECT routing_number_id
206: FROM xnp_routing_numbers
207: WHERE routing_number = p_routing_number
208: AND active_flag = 'Y';
209:
210: BEGIN
1497:
1498: CURSOR c_routing_id_exists (l_routing_number IN NUMBER,
1499: l_sp_id IN NUMBER) IS
1500: SELECT routing_number_id
1501: FROM xnp_routing_numbers
1502: WHERE routing_number = l_routing_number
1503: AND sp_id = l_sp_id
1504: AND interconnect_type = 'LRN' ;
1505:
1606: FETCH c_routing_id_exists INTO l_routing_number_id ;
1607:
1608: IF c_routing_id_exists%NOTFOUND THEN
1609:
1610: -- Create a routing record in the XNP_ROUTING_NUMBERS table
1611:
1612: INSERT INTO XNP_ROUTING_NUMBERS
1613: (
1614: ROUTING_NUMBER_ID ,
1608: IF c_routing_id_exists%NOTFOUND THEN
1609:
1610: -- Create a routing record in the XNP_ROUTING_NUMBERS table
1611:
1612: INSERT INTO XNP_ROUTING_NUMBERS
1613: (
1614: ROUTING_NUMBER_ID ,
1615: OBJECT_REFERENCE ,
1616: INTERCONNECT_TYPE ,
1625: LAST_UPDATE_LOGIN
1626: )
1627: VALUES
1628: (
1629: XNP_ROUTING_NUMBERS_S.nextval ,
1630: p_RECIPIENT_SP_ID||'-'||p_ROUTING_NUMBER||'-'||'LRN',
1631: 'LRN' ,
1632: p_RECIPIENT_SP_ID ,
1633: p_ROUTING_NUMBER ,
5141: l_rownum NUMBER := 0;
5142:
5143: CURSOR c_RN_OWNER IS
5144: SELECT DISTINCT sp_id, rownum
5145: FROM xnp_routing_numbers rn ,
5146: xnp_sv_sms sms
5147: WHERE rn.routing_number_id = sms.routing_number_id
5148: AND sms.subscription_tn BETWEEN l_starting_number AND l_ending_number;
5149:
5767: l_dummy VARCHAR2(1):=Null;
5768:
5769: CURSOR c_rn_for_recipient IS
5770: SELECT '1'
5771: FROM XNP_ROUTING_NUMBERS
5772: WHERE routing_number_id = p_ROUTING_NUMBER_ID
5773: AND sp_id = p_RECIPIENT_SP_ID
5774: AND active_flag='Y';
5775: