4022: RETURN VARCHAR2 IS
4023: l_line_number NUMBER;
4024: l_ship_addr_xml varchar2(4000);
4025: parser xmlparser.parser;
4026: xmlDoc xmldom.DOMDocument;
4027:
4028: nodeList xmldom.DOMNodeList;
4029: len number;
4030: node xmldom.DOMNode;
4024: l_ship_addr_xml varchar2(4000);
4025: parser xmlparser.parser;
4026: xmlDoc xmldom.DOMDocument;
4027:
4028: nodeList xmldom.DOMNodeList;
4029: len number;
4030: node xmldom.DOMNode;
4031: childNode xmldom.DOMNode;
4032: l_pop_address varchar2(4000);
4026: xmlDoc xmldom.DOMDocument;
4027:
4028: nodeList xmldom.DOMNodeList;
4029: len number;
4030: node xmldom.DOMNode;
4031: childNode xmldom.DOMNode;
4032: l_pop_address varchar2(4000);
4033: BEGIN
4034: select line_number into l_line_number from pon_auction_item_prices_all
4027:
4028: nodeList xmldom.DOMNodeList;
4029: len number;
4030: node xmldom.DOMNode;
4031: childNode xmldom.DOMNode;
4032: l_pop_address varchar2(4000);
4033: BEGIN
4034: select line_number into l_line_number from pon_auction_item_prices_all
4035: where auction_header_id = p_auction_header_id AND Nvl(clm_info_flag,'N') = 'N' AND rownum < 2;
4048: xmlparser.parseBuffer(parser,l_ship_addr_xml);
4049:
4050: xmlDoc := xmlparser.getDocument(parser);
4051:
4052: nodeList := xmldom.getElementsByTagName(xmlDoc,'*');
4053: len := xmldom.getLength(nodeList);
4054: l_pop_address := NULL;
4055:
4056: for i in 0..len-1
4049:
4050: xmlDoc := xmlparser.getDocument(parser);
4051:
4052: nodeList := xmldom.getElementsByTagName(xmlDoc,'*');
4053: len := xmldom.getLength(nodeList);
4054: l_pop_address := NULL;
4055:
4056: for i in 0..len-1
4057: loop
4054: l_pop_address := NULL;
4055:
4056: for i in 0..len-1
4057: loop
4058: node := xmldom.item(nodeList,i);
4059: childNode := xmldom.getFirstChild(node);
4060:
4061: if ( xmldom.getNodeName(node) = 'LOCATION_NAME'
4062: or xmldom.getNodeName(node) = 'ADDRESS_LINE_1'
4055:
4056: for i in 0..len-1
4057: loop
4058: node := xmldom.item(nodeList,i);
4059: childNode := xmldom.getFirstChild(node);
4060:
4061: if ( xmldom.getNodeName(node) = 'LOCATION_NAME'
4062: or xmldom.getNodeName(node) = 'ADDRESS_LINE_1'
4063: or xmldom.getNodeName(node) = 'ADDRESS_LINE_2'
4057: loop
4058: node := xmldom.item(nodeList,i);
4059: childNode := xmldom.getFirstChild(node);
4060:
4061: if ( xmldom.getNodeName(node) = 'LOCATION_NAME'
4062: or xmldom.getNodeName(node) = 'ADDRESS_LINE_1'
4063: or xmldom.getNodeName(node) = 'ADDRESS_LINE_2'
4064: or xmldom.getNodeName(node) = 'ADDRESS_LINE_3'
4065: or xmldom.getNodeName(node) = 'REGION1_DESC'
4058: node := xmldom.item(nodeList,i);
4059: childNode := xmldom.getFirstChild(node);
4060:
4061: if ( xmldom.getNodeName(node) = 'LOCATION_NAME'
4062: or xmldom.getNodeName(node) = 'ADDRESS_LINE_1'
4063: or xmldom.getNodeName(node) = 'ADDRESS_LINE_2'
4064: or xmldom.getNodeName(node) = 'ADDRESS_LINE_3'
4065: or xmldom.getNodeName(node) = 'REGION1_DESC'
4066: or xmldom.getNodeName(node) = 'REGION2_DESC'
4059: childNode := xmldom.getFirstChild(node);
4060:
4061: if ( xmldom.getNodeName(node) = 'LOCATION_NAME'
4062: or xmldom.getNodeName(node) = 'ADDRESS_LINE_1'
4063: or xmldom.getNodeName(node) = 'ADDRESS_LINE_2'
4064: or xmldom.getNodeName(node) = 'ADDRESS_LINE_3'
4065: or xmldom.getNodeName(node) = 'REGION1_DESC'
4066: or xmldom.getNodeName(node) = 'REGION2_DESC'
4067: ) THEN
4060:
4061: if ( xmldom.getNodeName(node) = 'LOCATION_NAME'
4062: or xmldom.getNodeName(node) = 'ADDRESS_LINE_1'
4063: or xmldom.getNodeName(node) = 'ADDRESS_LINE_2'
4064: or xmldom.getNodeName(node) = 'ADDRESS_LINE_3'
4065: or xmldom.getNodeName(node) = 'REGION1_DESC'
4066: or xmldom.getNodeName(node) = 'REGION2_DESC'
4067: ) THEN
4068: if ( l_pop_address is null )
4061: if ( xmldom.getNodeName(node) = 'LOCATION_NAME'
4062: or xmldom.getNodeName(node) = 'ADDRESS_LINE_1'
4063: or xmldom.getNodeName(node) = 'ADDRESS_LINE_2'
4064: or xmldom.getNodeName(node) = 'ADDRESS_LINE_3'
4065: or xmldom.getNodeName(node) = 'REGION1_DESC'
4066: or xmldom.getNodeName(node) = 'REGION2_DESC'
4067: ) THEN
4068: if ( l_pop_address is null )
4069: then
4062: or xmldom.getNodeName(node) = 'ADDRESS_LINE_1'
4063: or xmldom.getNodeName(node) = 'ADDRESS_LINE_2'
4064: or xmldom.getNodeName(node) = 'ADDRESS_LINE_3'
4065: or xmldom.getNodeName(node) = 'REGION1_DESC'
4066: or xmldom.getNodeName(node) = 'REGION2_DESC'
4067: ) THEN
4068: if ( l_pop_address is null )
4069: then
4070: l_pop_address := '';
4070: l_pop_address := '';
4071: else
4072: l_pop_address := l_pop_address || ', ';
4073: end if;
4074: l_pop_address := l_pop_address || xmldom.getNodeValue(childNode);
4075: end if;
4076: end loop;
4077: return l_pop_address;
4078: exception
4137: FUNCTION get_uda_fed_email(p_auction_header_id IN NUMBER)
4138: RETURN VARCHAR2 IS
4139: l_contact_xml varchar2(4000);
4140: parser xmlparser.parser;
4141: xmlDoc xmldom.DOMDocument;
4142:
4143: nodeList xmldom.DOMNodeList;
4144: len number;
4145: node xmldom.DOMNode;
4139: l_contact_xml varchar2(4000);
4140: parser xmlparser.parser;
4141: xmlDoc xmldom.DOMDocument;
4142:
4143: nodeList xmldom.DOMNodeList;
4144: len number;
4145: node xmldom.DOMNode;
4146: childNode xmldom.DOMNode;
4147: l_email_address varchar2(4000);
4141: xmlDoc xmldom.DOMDocument;
4142:
4143: nodeList xmldom.DOMNodeList;
4144: len number;
4145: node xmldom.DOMNode;
4146: childNode xmldom.DOMNode;
4147: l_email_address varchar2(4000);
4148:
4149: BEGIN
4142:
4143: nodeList xmldom.DOMNodeList;
4144: len number;
4145: node xmldom.DOMNode;
4146: childNode xmldom.DOMNode;
4147: l_email_address varchar2(4000);
4148:
4149: BEGIN
4150:
4159: xmlparser.parseBuffer(parser,l_contact_xml);
4160:
4161: xmlDoc := xmlparser.getDocument(parser);
4162:
4163: nodeList := xmldom.getElementsByTagName(xmlDoc,'*');
4164: len := xmldom.getLength(nodeList);
4165: l_email_address := NULL;
4166:
4167: for i in 0..len-1
4160:
4161: xmlDoc := xmlparser.getDocument(parser);
4162:
4163: nodeList := xmldom.getElementsByTagName(xmlDoc,'*');
4164: len := xmldom.getLength(nodeList);
4165: l_email_address := NULL;
4166:
4167: for i in 0..len-1
4168: loop
4165: l_email_address := NULL;
4166:
4167: for i in 0..len-1
4168: loop
4169: node := xmldom.item(nodeList,i);
4170: childNode := xmldom.getFirstChild(node);
4171:
4172: IF(xmldom.getNodeName(node) = 'ISSUING_OFFICE_CTEMAIL') THEN
4173: l_email_address := xmldom.getNodeValue(childNode);
4166:
4167: for i in 0..len-1
4168: loop
4169: node := xmldom.item(nodeList,i);
4170: childNode := xmldom.getFirstChild(node);
4171:
4172: IF(xmldom.getNodeName(node) = 'ISSUING_OFFICE_CTEMAIL') THEN
4173: l_email_address := xmldom.getNodeValue(childNode);
4174: END IF;
4168: loop
4169: node := xmldom.item(nodeList,i);
4170: childNode := xmldom.getFirstChild(node);
4171:
4172: IF(xmldom.getNodeName(node) = 'ISSUING_OFFICE_CTEMAIL') THEN
4173: l_email_address := xmldom.getNodeValue(childNode);
4174: END IF;
4175:
4176:
4169: node := xmldom.item(nodeList,i);
4170: childNode := xmldom.getFirstChild(node);
4171:
4172: IF(xmldom.getNodeName(node) = 'ISSUING_OFFICE_CTEMAIL') THEN
4173: l_email_address := xmldom.getNodeValue(childNode);
4174: END IF;
4175:
4176:
4177: end loop;