Red Hat Bugzilla – Bug 22990
Customer Number lookup is incorrect
Last modified: 2007-04-18 12:30:24 EDT
After adding an item to the internal store's shopping cart and clicking 'Checkout', one is presented with this screen: http://www.redhat.com/apps/internalstore/login.html It asks for a web user name or customer number (the two available searches on company name and contact name appear broken). The customer number should be querying WEB_USER.ORACLE_CUSTOMER_NUMBER, but after trying a few Oracle Customer Numbers, it seems that WEB_USER.ORACLE_CUSTOMER_ID is actually being queried here. Example: jscottco ORACLE_CUSTOMER_NUMBER: 199907 This value returns web user 'rtietje', ORACLE_CUSTOMER_ID = 199907, ORACLE_CUSTOMER_NUMBER = 99676 Example: jscottco ORACLE_CUSTOMER_ID: 300206 This value matched 'jscottco' This lookup needs to be pointed at the right WEB_USER column.
An explanation of ORACLE_CUSTOMER_NUMBER and ORACLE_CUSTOMER_ID. Oracle will generally have a "public" id and a "private" id for any object within the system. In this case, ORACLE_CUSTOMER_NUMBER corresponds to the public customer number that we can use to talk about a customer. It's the number we give the customer freely, since the search functions in the Oracle Applications use it as a key. ORACLE_CUSTOMER_ID corresponds to the private Oracle id for a customer. We don't give this number to customers since it doesn't really mean anything except to the underlying relational structure. We can't search on it in the Applications. It's for "Oracle-only Use". Does that help? The point of the story is that anytime we're showing customer numbers to the outside, or making any sort of relation between a web customer and a customer in Oracle, we talk about ORACLE_CUSTOMER_NUMBER. In terms of the bug I've reported, something inside the internal store code is matching against ORACLE_CUSTOMER_ID instead of ORACLE_CUSTOMER_NUMBER. Every customer id is unique within the set, and every customer number is unique within the set, but the two sets are _NOT_ unique. There's the problem. One customer's number can be another customer's id, and vice versa.
Sorry, you also asked about how these columns get populated in the web tables: ORACLE_CUSTOMER_NUMBER and ORACLE_CUSTOMER_ID (and ORACLE_CONTACT_ID as well) are currently assigned in Oracle ERP/CRM at time of customer import from the web. Then some fancy scripts push these ids back to the web and updates the web_user table with the results. Otherwise the ids are null (we've changed NULL to be -1 for the purposes of these three columns).