> 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:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
