Commit fails on transaction processing for F4211FSEditLine

[Jeff Dhaene]

I am having an odd problem that maybI am having an odd problem that perhaps someone with more experience can shed some light.
I have created 2 business functions, the first calls the F4211FSBeginDoc MBF and sets up some header related information where as the second calls the F4211FSEditLine MBF and adds in detailed lines.
Now if i call these two BF in a report without transaction processing, all works fine and my information is saved in my F42UI01, F42UI11, F42UI12, F40UI74 as it should.
If i run this with transaction processing it works most of the time with the odd exception and this is where my problem lies.
So here is what I do, I call my Header BF that works great and comes back successful then for my EditLine BF I call it only once for one part number and it always comes back successful but pending the part number, when I run the commit function it fails.

Here is how our parts are set up followed whats unique to the part for it to fail.
We have no base price, our base price is the first adjustment line in Advance pricing with with a sequence number of 200.
We have our freight as an adjustment line of sequence 400. our freight has no price but instead has a free good attached to it with the freight price on that free good.
On our base price adjustment line we also have free goods attached to it.

Now the only times the commit fails is if the amount of free goods attached on the base adjustment line is an even number, so if we have 2,4,6 etc free goods on the base adjustment line then it fails, if we have 1,3, 5 free goods on it then it commits successful. I have a lot of testing to make sure this is the case, i’ve tried changing the order of the free goods, the quantity etc… and come to this result every time.
This seems to be a real odd problem and I hope someone can shed some light for me. If you require any additional info, please let me know. Maybe theres a flag on the F4211FSEditLine im passing in wrong? But on the side why does it work for the others.
So once again if theres any additional info youd like to see, let me know.
Thanks
Jeff

Oh and to further this, here is the debug text i get when the commit fails:
Apr 02 09:25:34 ** 3432/3700 Entering JDB_SetUserOption (Option No = 2 , Setting = 0)
Apr 02 09:25:34 ** 3432/3700 Entering JDB_CommitUser
Apr 02 09:25:34 ** 3432/3700 Entering JDB_PrepareToCommitUser
Apr 02 09:25:34 ** 3432/3700 Exiting JDB_PrepareToCommitUser with Failure
Apr 02 09:25:34 ** 3432/3700 Rolling Back Transaction : 202833930_3432_3700_218319224 Commit Point Number : 1
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=0286A4B8 conn=0AFC0BE0
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=0286A4B8 conn=03F85F70
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=028C3F38 conn=0B0117D8
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=0286A4B8 conn=0B01F220
Apr 02 09:25:34 ** 3432/3700 Starting Commit Point No : 2 for Transaction ID : 202833930_3432_3700_218319224z
Apr 02 09:25:34 ** 3432/3700 Exiting JDB_CommitUser
Apr 02 09:25:34 ** 3432/3700 Entering JDB_FreeUser
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=0286A4B8 conn=0AFC0BE0
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=0286A4B8 conn=03F85F70
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=028C3F38 conn=0B0117D8
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=0286A4B8 conn=0B01F220
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=0286A4B8 conn=0AFC0BE0
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=0286A4B8 conn=03F85F70
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=028C3F38 conn=0B0117D8
Apr 02 09:25:34 ** 3432/3700 ORACLE DBRollbackCon envr=0286A4B8 conn=0B01F220
Apr 02 09:25:34 ** 3432/3700 ORACLE DBCommitCon envr=0286A4B8 conn=0AFC0BE0 (implicit for free connect)
Apr 02 09:25:34 ** 3432/3700 ORACLE DBCommitCon envr=0286A4B8 conn=03F85F70 (implicit for free connect)
Apr 02 09:25:34 ** 3432/3700 ORACLE DBCommitCon envr=028C3F38 conn=0B0117D8 (implicit for free connect)
Apr 02 09:25:34 ** 3432/3700 ORACLE DBCommitCon envr=0286A4B8 conn=0B01F220 (implicit for free connect)



*** One last note, if i have transaction processing turned off, it works 100% and saves all data in temp UI tables. ***

 

[Jeff Dhaene]

Upon further investigation i found that its failing because the F4211FSEditLinePreProcess is being called twice on the freight line which is doing 2 inserts to the F42UI12 with the same values.
It looks like I4205120_MaintainFreeGoodItems is calling it twice within the while loop
Heres the basic of the while loop:
if (cMode != 'D'){
memset((void *)&ds4200310J, (int)'\0', sizeof(ds4200310J));
idFetchReturn = JDEDB_FAILED;
while (idFetchReturn == JDEDB_FAILED)
{
if (idJDBReturn == JDEDB_PASSED)
{
.
.
.

}


and here is the if statement getting called in the while loop:

if ((!IsStringBlank(ds4500200.szGLClass)) || (!IsStringBlank(ds4200310J.szSubledger))) {
strcpy((char *)ds4200310J.szComputerID,
(const char *)lpdsGetControl->szCMComputerID);
MathCopy(&ds4200310J.mnJobNo, &lpdsGetControl->mnCMJobNo);
MathCopy(&ds4200310J.mnWFLineNo, &ds4500200.mnWFLineNo);
strcpy((char *)ds4200310J.szGlClass, (const char *)ds4500200.szGLClass);
ds4200310J.cUseCacheOrWF_EV01 = lpdsGetFreeGood->cCMWriteToWFFlag;

idReturnValue = jdeCallObject("F4211FSEditLinePreProcess",
NULL, lpBhvrCom,lpVoid,(LPVOID)&ds4200310J,
(CALLMAP *)NULL,(int)0,(char *)NULL,
(char *)NULL,(int)0);
}


so for some reason im guessing that if statement should only get hit once or the while loop should only get executed once. Ill have to further investigate to figure out why its occuring more then once. but in the mean time, if anyone happens to have any good info it would be apprecieated.
Thanks
Jeff

 

[Jeff Dhaene]

Heres my error i get:


4208/5792 Fri Mar 30 15:48:56 2007 DBPERFRQ.C386
OCI0000178 - Unable to execute - INSERT INTO CRPDTA.F42UI12 (ZDCTID, ZDJOBS, ZDLNIX, ZDKCOO, ZDDOCO, ZDDCTO, ZDLNID, ZDOPDJ, ZDPPDJ, ZDTHGD, ZDTHRP, ZDFRGD, ZDFRMP, ZDEMCU, ZDSRP1, ZDSRP2, ZDSRP3, ZDSRP4, ZDSRP5, ZDPRP1, ZDPRP2, ZDPRP3, ZDPRP4, ZDPRP5, ZDINMG, ZDPTC, ZDDTBS, ZDRYIN, ZDTRDC, ZDPRGR, ZDCADC, ZDKCO, ZDDOC, ZDDCT, ZDPRMO, ZDDFTN, ZDTXA1, ZDEXR1, ZDPRIO, ZDBACK, ZDSBAL, ZDLOB, ZDEUSE, ZDDTYS, ZDCDCD, ZDNTR, ZDANBY, ZDCARS, ZDMOT, ZDCOT, ZDROUT, ZDSTOP, ZDZON, ZDAFT, ZDFRTH, ZDFRTC, ZDSHCM, ZDSHCN, ZDSERN, ZDGLC, ZDSTTS, ZDSLCM, ZDSLSM, ZDSLC2, ZDSLM2, ZDACOM, ZDRCD, ZDGWUM, ZDOMCU, ZDLT, ZDSBL, ZDSBLT, ZDLCOD, ZDSWMS, ZDUNCD, ZDCRMD, ZDURCD, ZDURDT, ZDURAT, ZDURAB, ZDURRF, ZDFRAT, ZDMCU, ZDITM, ZDLOCN, ZDLOTN, ZDUOM, ZDSOQS, ZDSOBK, ZDSONE, ZDOTQY, ZDFFG1, ZDFFG2, ZDFFU2) VALUES BND1,:BND2,:BND3,:BND4,:BND5,:BND6,:BND7,:BND8,:BND9,:BND10,:BND11,:BND12,:BND13,:BND14,:BND15,:BND16,:BND17,:BND18,:BND19,:BND20,:BND21,:BND22,:BND23,:BND24,:BND25,:BND26,:BND27,:BND28,:BND29,:BND30,:BND31,:BND32,:BND33,:BND34,:BND35,:BND36,:BND37,:BND38,:BND39,:BND40,:BND41,:BND42,:BND43,:BND44,:BND45,:BND46,:BND47,:BND48,:BND49,:BND50,:BND51,:BND52,:BND53,:BND54,:BND55,:BND56,:BND57,:BND58,:BND59,:BND60,:BND61,:BND62,:BND63,:BND64,:BND65,:BND66,:BND67,:BND68,:BND69,:BND70,:BND71,:BND72,:BND73,:BND74,:BND75,:BND76,:BND77,:BND78,:BND79,:BND80,:BND81,:BND82,:BND83,:BND84,:BND85,:BND86,:BND87,:BND88,:BND89,:BND90,:BND91,:BND92,:BND93,:BND94)

4208/5792 Fri Mar 30 15:48:56 2007 DBPERFRQ.C393
OCI0000179 - Error - ORA-00001: unique constraint (CRPDTA.F42UI12_PK) violated

4208/5792 Fri Mar 30 15:48:56 2007 JDB_DRVM.C938
JDB9900401 - Failed to execute db request

4208/5792 Fri Mar 30 15:48:56 2007 JDB_EXET.C5218
JDB3400009 - Failed to perform Insert for F42UI12

4208/5792 Fri Mar 30 15:48:56 2007 JDB_RST.C1099
JDB9900307 - Failed to find table specifications

4208/5792 Fri Mar 30 15:48:56 2007 JDB_RQ1.C1464
JDB3100007 - Failed to get valid table specifications

4208/5792 Fri Mar 30 15:48:57 2007 JDB_EXET.C6091
JDB9900849 - Failed to commit user


and for some reason the F4211FSEditLinePreProcess is getting called twice, still no ideas why.

 

[Null Value]

Jeff,

The 'I4205120_MaintainFreeGoodItems' is perhaps calling the 'F4211FSEditLinePreProcess' as many times as is needed. My best guess is that its either the LNIX or LNID values that are duplicate.

At the point of insert to the F42UI12 table (will be a JDB_InsertTable statement in B4200311 BSFN) set a break point and check the values for each iteration. The primary key order for F42UI12 is CTID + JOBS + LNIX + DOCO + DCTO + KCOO + LNID and I'd place my bets on the LNIX value first followed by LNID.

Regards,

Shailesh

 

[Jeff Dhaene]

Ok so after more digging around i found where everthing is screwing up and have a fix some data so it will work. However i dont believe the data was bad how it was before so im curious if any one can help tell me why it does this.

When adding 1 part say PartX to a sales order it has 2 price adjustments,
1. seq 200 WGBASE which is the base price with a GLC = ' '
2. seq 401 WGFRTRAT which is a freight line with a GLC = 'FT60'


the first one has 2 free good items to add to the Sales order
the second adjustment line has no base price(no dollar value on the adjustment) but it has a FREIGHT item as a free good added on it.

now when processing this, from the code i said before (CalculateSOMBFPriceCost -> I4205120_MaintainFreeGoodItems)

this gets the free good item then eventually comes to this code


if (cMode != 'D'){
memset((void *)&ds4200310J, (int)'\0', sizeof(ds4200310J));
idFetchReturn = JDEDB_FAILED;
********************************************************************
Note this while statement
********************************************************************

while (idFetchReturn == JDEDB_FAILED) {
if (idJDBReturn == JDEDB_PASSED){

....../** does more unimportant stuff then gets to select statement **/

jdeNIDcpy(lpSelectF40UI74[0].Item1.szDict, NID_OSEQ);
jdeNIDcpy(lpSelectF40UI74[0].Item1.szTable, NID_F40UI74);
lpSelectF40UI74[0].Item1.idInstance = (ID)0;
jdeNIDcpy(lpSelectF40UI74[0].Item2.szDict, "");
jdeNIDcpy(lpSelectF40UI74[0].Item2.szTable, "");
lpSelectF40UI74[0].Item2.idInstance = (ID)0;
lpSelectF40UI74[0].lpValue = (void *)&mnLastRetrievedSequenceNo;
lpSelectF40UI74[0].nValues = (short)1;
lpSelectF40UI74[0].nAndOr = JDEDB_ANDOR_AND;
lpSelectF40UI74[0].nCmp = JDEDB_CMP_GT;

.... /** more stuff */
idFetchReturn = JDB_Fetch(hRequest, (LPVOID)&dsF40UI74,(int)NULL);

*******************************************************************
Now the interesting part here is this mnLastRetrievedSequenceNo fields it starts as a blank.
so in the where clause there is this statement : "AND ( AWOSEQ > 0.000000 AND AWFGY = 'Y' ) "
Keep note of this ill get back to it later now later on in the code it does the following
*******************************************************************

if (idFetchReturn == JDEDB_PASSED){
MathCopy(&mnLastRetrievedSequenceNo, &dsF40UI74.awoseq);
}
else{
MathCopy(&mnLastRetrievedSequenceNo, &mnZero);
}

********************************************************************
Note that if it doesnt find any results it sets the sequence number to 0 for the next pass
And then later it does this
********************************************************************

if ((!IsStringBlank(ds4500200.szGLClass)) || (!IsStringBlank(ds4200310J.szSubledger)))
{
strcpy((char *)ds4200310J.szComputerID,
(const char *)lpdsGetControl->szCMComputerID);
MathCopy(&ds4200310J.mnJobNo, &lpdsGetControl->mnCMJobNo);
MathCopy(&ds4200310J.mnWFLineNo, &ds4500200.mnWFLineNo);
strcpy((char *)ds4200310J.szGlClass, (const char *)ds4500200.szGLClass);
ds4200310J.cUseCacheOrWF_EV01 = lpdsGetFreeGood->cCMWriteToWFFlag;
********************************************************************
note that this calls the F4211FSEditLinePreProcess and writes a line in the F42UI12 table
********************************************************************
idReturnValue = jdeCallObject("F4211FSEditLinePreProcess",
NULL, lpBhvrCom,lpVoid,(LPVOID)&ds4200310J,
(CALLMAP *)NULL,(int)0,(char *)NULL,
(char *)NULL,(int)0);
}
********************************************************************
then it goes back to the while statement, if the select statement did not find any results it goes back in the loop.
********************************************************************


--------------------------------------------------------------------------------------------------------------------------
so to recap whats going on:
Pass 1 (first free good item which is on WGBASE price adjustment)
- mnLastRetrievedSequenceNo = blank
- the where clause in select statement contains : "AND ( AWOSEQ > 0.000000 AND AWFGY = 'Y' ) "
- the fectch line comes back as successful
- mnLastRetrievedSequenceNo gets updated to 200 (dsF40UI74.awoseq from fetch)
- ds4500200.szGLClass = blank since price adjustment lines GLC = blank
*this means the if statement is not satisfied and it wont get called
-while loop fails since fetch is successful
Pass 2(second free good item which is on WGBASE price adjustment)
- mnLastRetrievedSequenceNo = 200
- the where clause in select statement contains : "AND ( AWOSEQ > 200.000000 AND AWFGY = 'Y' ) "
- the fectch line comes back as successful
- mnLastRetrievedSequenceNo gets updated to 401 (dsF40UI74.awoseq from fetch)
- ds4500200.szGLClass = blank since price adjustment lines GLC = blank
*this means the if statement is not satisfied and it wont get called
-while loop fails since fetch is successful
Pass 3(freight free good item which is on WGFRTRAT price adjustment)
- mnLastRetrievedSequenceNo = 401
- the where clause in select statement contains : "AND ( AWOSEQ > 401.000000 AND AWFGY = 'Y' ) "
- the fectch line comes back as ***FAILED***
- mnLastRetrievedSequenceNo gets updated to 0 {MathCopy(&mnLastRetrievedSequenceNo, &mnZero);}
- ds4500200.szGLClass = FT60 since price adjustment lines GLC = FT60
*this means the if statement IS satisfied and calls F4211FSEditLinePreProcess
-while loop gets called again since while (idFetchReturn == JDEDB_FAILED) is true
Pass for 3 second time in while loop
- mnLastRetrievedSequenceNo = 0
- the where clause in select statement contains : "AND ( AWOSEQ >0.000000 AND AWFGY = 'Y' ) "
- the fectch line comes back as successful
- mnLastRetrievedSequenceNo gets updated to 200 (dsF40UI74.awoseq from fetch)
- ds4500200.szGLClass = FT60 since price adjustment lines GLC = FT60
*this means the if statement IS satisfied and calls F4211FSEditLinePreProcess
*** this means it tries to write the same F42UI12 record in DB which isn't successful and causes all my problems
-while loop fails since fetch is successful

--------------------------------------------------------------------------------------------------------------------------
So in other words the mnLastRetrievedSequenceNo cycles from 0 to 200 to 401 and if happens to be 401 and the freegood happens to have a gl class code not being blank or 0, it will write a line in the F42UI12 and cycle in the whlie loop a second time and fail.


really i think what makes sense to me is this line:
lpSelectF40UI74[0].nCmp = JDEDB_CMP_GT;
should be a greater then or equal for the sequence number
not sure though cause i havent tested that.