> Michael, that's really all I'm looking for is the ability to pass the > detailed (custom, not system defined) error message back to > Delta. If >Delta truly only allows a simple return code, I can't pass > something helpful such as "GLN code not found for > 1234567890123", for >example.
Oh, it's not either a DBMS message (eg an SQLCode) OR a 'Delta' message... it's an *application* message! I also can't believe Delta only allows "a simple return code" ... that is, it MUST be able to return numbers and strings, as in .. Output = DB ("Select GLN_code from GLN_table where Key = '123456789' ") Well, from my Oracle procedure... CREATE OR REPLACE FUNCTION f_EnsureEdiEnabled (UserSegNo IN VARCHAR2, UserCustno IN VARCHAR2, EdiTrancode IN VARCHAR2) return VARCHAR2 AS iCount NUMBER (11,0); iTpNo NUMBER(11,0); iSegNo NUMBER(11,0); iCustNo NUMBER(11,0); zExtIDQual VARCHAR2 (4); zExtId VARCHAR2 (16); uCustNo VARCHAR2(24); uSegNo VARCHAR2(12); zEdiCd VARCHAR2(4); zMsg VARCHAR2 (500); -- what gets returned as function value zErrItem VARCHAR2 (100); -- to hold err detail before return pragma autonomous_transaction; -- required to do commit here BEGIN -- assign constants for the web order partner zExtIdQual := 'ZZ'; zExtId := 'WEBORDERS'; -- see if this cust us already active for this trancode at this segment select Count(*) INTO iCount from prod.cust_seg_edi cse, prod.cust c, prod.seg s Where c.cust_no = cse.cust_no and s.seg_no = cse.seg_no and cse.edi_cd = EdiTranCode and c.user_cust_no = UserCustNo and s.user_seg_no = UserSegNo and cse.edi_status = 'ACTIVE' ; -- DBMS_OUTPUT.put_line ('Count returns ' || :icount); zMsg := 'COUNT ' || to_Char (iCount); IF icount < 1 -- not found = not active THEN -- get the numeric trd_prt_no, cust_no and seg no for insert zErrItem := 'INVALID CUSTOMER NUMBER ' || UserCustno; select cust_no into iCustno from prod.cust where user_cust_no = UserCustNo ; zErrITem := 'INVALID BUSINESS SEGMENT ' || UserSegNo; select seg_no into iSegNo from prod.seg where user_seg_no = UserSegNo; /* find tp number assume there is at least one 850R set up and only one TP number using 'ZZ' 'WEBORDERS' */ zErrItem := ' NO TRADING PARTNER FOUND ACTIVE FOR SEGMENT ' || UserSegNo || ' TRANSACTION ' || EDITranCode || ' QUAL/ID ' || zExtIDQual ||'/'|| zExtId ; -- this one is OK getting correct value (=2) select distinct trd_prtnr_no into iTpNo from prod.cust_seg_edi where ext_id_qual = zExtIdQual and ext_id = zExtId and edi_cd = EdiTrancode and seg_no = iSegno; -- commit to see if that releases DB for update? --commit; -- insert a row for this cust_no, seg_no with Web Order defaults... INSERT INTO prod.cust_seg_edi (cust_no, edi_standard, edi_cd, ext_id_qual, ext_id, seg_no, trd_prtnr_no, edi_status ) values (icustNo, 'X12', EdiTranCode, zExtIdQual, zExtId, iSegNo, iTPNO, 'ACTIVE'); -- -14552 error:ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML -- unless #pragma autonomous_transaction is used. COMMIT; zMsg := 'YES CUST/SEG/TRANSACTION ADDED'; RETURN zMsg; ELSE -- count is one (1) or more (should never be more!) zMsg := 'YES ALREADY ACTIVE'; return zMsg; END IF; --If count was 1 or more EXCEPTION WHEN NO_DATA_FOUND THEN zMsg := 'NO ' || zErrITem; return zMsg; WHEN TOO_MANY_ROWS THEN zMsg := 'NO TP# Non-Unique for EXT_ID + QUAL' ; return zMsg; WHEN INVALID_NUMBER THEN zMsg := 'NO INVALID_NUMBER value somewhere ' ; return zMsg; WHEN VALUE_ERROR THEN zMsg := ' VALUE ERROR'; return zMsg; WHEN DUP_VAL_ON_INDEX THEN zMsg := 'NO DUP VAL ON INDEX WHEN ADDING (should not happen)'; return zMsg; WHEN OTHERS THEN zMsg := 'NO INSERT FAILED with SQLCode '|| to_Char(SQLCODE) || SQLERRM; return zMsg; END f_ensureEdiEnabled; -- REM END OF FILE; This returns either "YES " + reason or "NO " + reason, depending if a certain value is found in the DB , succesfully added to the DB, or a DBMS error occurred rued. ------------------------------------ ... Please use the following Message Identifiers as your subject prefix: <SALES>, <JOBS>, <LIST>, <TECH>, <MISC>, <EVENT>, <OFF-TOPIC> Job postings are welcome, but for job postings or requests for work: <JOBS> IS REQUIRED in the subject line as a prefix.Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/EDI-L/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/EDI-L/join (Yahoo! ID required) <*> To change settings via email: edi-l-dig...@yahoogroups.com edi-l-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: edi-l-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/