Trouble with agent that working on LOCATION - CREATED event

[ineborako]

I create an agent that listen to the "Location - created" event.
The main action is direct update sql in case when the created location has role BILL-TO. In this case my direct update sql action must to write into the SERVPROV table a record with an equal GID and XID as in LOCATION record.

1) Assign variable $LOCATION_GID_OR_NODATA
Saved query text is:

SELECT NVL((SELECT l.location_gid
FROM location l, location_role_profile lrp
WHERE l.location_gid = lrp.location_gid
AND lrp.location_role_gid = 'BILL TO'
AND ROWNUM = 1
AND l.location_gid = ? ),
'NO_DATA')
FROM DUAL
2) IF $LOCATION_GID_OR_NODATA<>NO_DATA
3)DIRECT UPDATE SQL
The text of the query is:

insert into servprov
(servprov_gid,
servprov_xid,
auto_payment_flag,
is_dispatch_by_region,
allow_tender,
is_accept_spot_bids,
is_accept_broadcast_tenders,
is_localize_broadcast_contact,
do_conditional_accepts,
is_internal_nvocc,
is_accept_by_ssu_allowed,
is_copy_inv_delta_back_to_ship,
invoicing_process)
values
($LOCATION_GID_OR_NODATA,
$LOCATION_GID_OR_NODATA, //let it will be so
'N','N','N','N','N','N','N','N','N','N','S')
4) END IF

If I set for the "Location - created" event option "Before Persist" checked then agent is run but it get Java Null Pointer Exception

If I do not set option "Before Persist" - then my agent does not started in general.

Please help me anyone.

[LourensGlog]

Re: Trouble with agent that working on LOCATION - CREATED event

I will not question (yet...) why you want to create a service provide like this, but one clear error is the missing domain_name in your insert statement.

[ineborako]

Re: Trouble with agent that working on LOCATION - CREATED event

To LourensGlog: Thanks for attention to my problem.
You may delete the direct update sql statement but the trouble do not get away.
Yes, I know that the direct update sql is not correct. But when agent run - it never go to this line, because it stopped on assign variable action.

[LourensGlog]

Re: Trouble with agent that working on LOCATION - CREATED event

Next time attach the system logfile which will help narrow down the problem.

Your saved query SQL for the variable assignment looks fine, however you may want to clean it up a bit as OTM does not always like line breaks and the ? character:

SELECT NVL ((SELECT location_gid FROM location_role_profile lrp WHERE location_role_gid = 'BILL TO' AND ROWNUM = 1 AND location_gid = $gid ),'NO_DATA') FROM DUAL

[ineborako]

Re: Trouble with agent that working on LOCATION - CREATED event

To LourensGlog
I tryed $gid inplace ? too. But I think when the record is not in database at the moment (restriction before persist) then saved query must have an exception

[ineborako]

Re: Trouble with agent that working on LOCATION - CREATED event

The problem exists only on one of the our domain. Other is fine. We try to restart OTM but after restarting situation has not changed

[LourensGlog]

Re: Trouble with agent that working on LOCATION - CREATED event

Can you upload the logfiles for both domains? This may help in finding the cause

[ineborako]

Re: Trouble with agent that working on LOCATION - CREATED event

I can send log from only one domain where agent work fine. Because on another domain there is no execution of the agent with Unchecked restriction
Before Persist and log is clear.
Last list of the actions of the agent is:

ASSIGN VARIABLE: $CUSTOMER_GID, LOCATION_BILL_TO
ASSIGN VARIABLE: $CUSTOMER_XID, LOCATION_BILL_TO_XID
ASSIGN VARIABLE: $DOMAIN, LOCATION_DOMAIN
IF: $CUSTOMER_GID<>NO_VALUE
DIRECT SQL UPDATE
ENDIF

The log of the agent execution is:
2009-07-16 18:02:51.794233171DebugAgentStarting agent CRP2.AAG_LOCATION_TO_SERVPROV (18596475) in response to LocationCreated: location=CRP2.IN-TEST, dataSource=USER, beforePersist=false, children= (as null) [lifetime - 2]2009-07-16 18:02:51.797233171DebugAgentPerforming action Assign: variableName=$CUSTOMER_GID, savedQueryGid=CRP2.LOCATION_BILL_TO (AAG_LOCATION_TO_SERVPROV) as CRP2 (18596475); was Assign: variableName=$CUSTOMER_GID, savedQueryGid=CRP2.LOCATION_BILL_TO [agentUtility - 2]2009-07-16 18:02:51.802233171DebugAgentComplete action Assign: variableName=$CUSTOMER_GID, savedQueryGid=CRP2.LOCATION_BILL_TO (AAG_LOCATION_TO_SERVPROV) (18596475) [agentUtility - 2]2009-07-16 18:02:51.803233171DebugAgentPublishing topic Assign: variableName=$CUSTOMER_XID, savedQueryGid=CRP2.LOCATION_BILL_TO_XID (AAG_LOCATION_TO_SERVPROV) on queue agentUtility, group null [agentUtility - 2]2009-07-16 18:02:51.805233171DebugAgentPerforming action Assign: variableName=$CUSTOMER_XID, savedQueryGid=CRP2.LOCATION_BILL_TO_XID (AAG_LOCATION_TO_SERVPROV) as CRP2 (18596475); was Assign: variableName=$CUSTOMER_XID, savedQueryGid=CRP2.LOCATION_BILL_TO_XID [agentUtility - 3]2009-07-16 18:02:51.819233171DebugAgentComplete action Assign: variableName=$CUSTOMER_XID, savedQueryGid=CRP2.LOCATION_BILL_TO_XID (AAG_LOCATION_TO_SERVPROV) (18596475) [agentUtility - 3]2009-07-16 18:02:51.82233171DebugAgentPublishing topic Assign: variableName=$DOMAIN, savedQueryGid=CRP2.LOCATION_DOMAIN (AAG_LOCATION_TO_SERVPROV) on queue agentUtility, group null [agentUtility - 3]2009-07-16 18:02:51.823233171DebugAgentPerforming action Assign: variableName=$DOMAIN, savedQueryGid=CRP2.LOCATION_DOMAIN (AAG_LOCATION_TO_SERVPROV) as CRP2 (18596475); was Assign: variableName=$DOMAIN, savedQueryGid=CRP2.LOCATION_DOMAIN [agentUtility - 4]2009-07-16 18:02:51.83233171DebugAgentComplete action Assign: variableName=$DOMAIN, savedQueryGid=CRP2.LOCATION_DOMAIN (AAG_LOCATION_TO_SERVPROV) (18596475) [agentUtility - 4]2009-07-16 18:02:51.831233171DebugAgentPublishing topic If: savedCondition=null, threshold=null, expression=CRP2.IN-TEST<>NO_VALUE, forceTrue=false (AAG_LOCATION_TO_SERVPROV) on queue agentUtility, group null [agentUtility - 4]2009-07-16 18:02:51.834233171DebugAgentPerforming action If: savedCondition=null, threshold=null, expression=CRP2.IN-TEST<>NO_VALUE, forceTrue=false (AAG_LOCATION_TO_SERVPROV) as CRP2 (18596475); was If: savedCondition=null, threshold=null, expression=$CUSTOMER_GID<>NO_VALUE, forceTrue=false [agentUtility - 1]2009-07-16 18:02:51.835233171DebugAgentAction If returns true [agentUtility - 1]2009-07-16 18:02:51.837233171DebugAgentPerforming action DBUpdate: sqlStatement=insert into servprov (servprov_gid, servprov_xid, auto_payment_flag, is_dispatch_by_region, allow_tender, is_accept_spot_bids, is_accept_broadcast_tenders, is_localize_broadcast_contact, do_conditional_accepts, is_internal_nvocc, is_accept_by_ssu_allowed, is_copy_inv_delta_back_to_ship, invoicing_process, domain_name) values ($CUSTOMER_GID, $CUSTOMER_XID, 'N','N','N','N','N','N','N','N','N','N','S',$DOMAI N), statementType=Plain SQL Statement, refreshCache=true (AAG_LOCATION_TO_SERVPROV) as CRP2 (18596475); was DBUpdate: sqlStatement=insert into servprov (servprov_gid, servprov_xid, auto_payment_flag, is_dispatch_by_region, allow_tender, is_accept_spot_bids, is_accept_broadcast_tenders, is_localize_broadcast_contact, do_conditional_accepts, is_internal_nvocc, is_accept_by_ssu_allowed, is_copy_inv_delta_back_to_ship, invoicing_process, domain_name) values ($CUSTOMER_GID, $CUSTOMER_XID, 'N','N','N','N','N','N','N','N','N','N','S',$DOMAI N), statementType=Plain SQL Statement, refreshCache=true [agentUtility - 2]Performing action DBUpdate: sqlStatement=insert into servprov2009-07-16 18:02:51.888233171DebugPlanningRateGeoFinderSessio n.clearRateGeoCache(): Invoked. [agentUtility - 2]2009-07-16 18:02:51.888233171DebugPlanningRateGeoFinderSessio n.clearPreferredRatesCache(): Invoked. [agentUtility - 2]2009-07-16 18:02:51.888233171DebugPlanningRateGeoFinderSessio n.clearRgsWithZoneProfilesCache(): Invoked. [agentUtility - 2]2009-07-16 18:02:51.89233171DebugAgentComplete action DBUpdate: sqlStatement=insert into servprov (servprov_gid, servprov_xid, auto_payment_flag, is_dispatch_by_region, allow_tender, is_accept_spot_bids, is_accept_broadcast_tenders, is_localize_broadcast_contact, do_conditional_accepts, is_internal_nvocc, is_accept_by_ssu_allowed, is_copy_inv_delta_back_to_ship, invoicing_process, domain_name) values ($CUSTOMER_GID, $CUSTOMER_XID, 'N','N','N','N','N','N','N','N','N','N','S',$DOMAI N), statementType=Plain SQL Statement, refreshCache=true (AAG_LOCATION_TO_SERVPROV) (18596475) [agentUtility - 2]Complete action DBUpdate: sqlStatement=insert into servprov2009-07-16 18:02:51.892233171DebugAgentComplete action If: savedCondition=null, threshold=null, expression=CRP2.IN-TEST<>NO_VALUE, forceTrue=false (AAG_LOCATION_TO_SERVPROV) (18596475) [agentUtility - 2]2009-07-16 18:02:51.893233171DebugAgentCompleting agent CRP2.AAG_LOCATION_TO_SERVPROV (18596475) [agentUtility - 2]

[ineborako]

Re: Trouble with agent that working on LOCATION - CREATED event

The problem is solved by running http://<otmdomain>/GC3/glog.webserver.cache.CacheDiagServlet/
and clicking on "clear all"

[LourensGlog]

Re: Trouble with agent that working on LOCATION - CREATED event

Do I understand that all problems are solved now?

Just a note: I don't think this agent will work with before persist, as in the variable assignment you try to read the location role profile of the location. However, by this time the location (and the role) are not yet stored in the database. Why would you want this to be a before persist agent anyway?

Lourens

[ineborako]

Re: Trouble with agent that working on LOCATION - CREATED event

Yes, Lourens, problem is solved. Thanks you for the help.
I understand that it is necessary to do update action after persist. Before persist is correct only for in-memory data. But there are situations when you do as correctly - and nothing turns out. And then I start to touch all variants - may be suddenly will work correctly as it is necessary. At us it refers to dances with a drum.