Calling OOTB post to wip procedure via gel script is not yielding the result

[Agin Vadath]

Dear members,

Am using SP to call our OOTB SP for wip posting. Now we are converting the custom SP used to make the call in to Gel script. 

my code snippet is below. Gel script is completing without any issue, but wip posting is not happening. Could anyone please help me to resolve this issue?

"
<sql:query escapeText="0" var="date">
<![CDATA[
select to_date('1990-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') date1, to_date('2999-12-31 00:00:00','yyyy-mm-dd hh24:mi:ss') date2 from dual ]]>

</sql:query>
<core:set value="${date.rows[0].date1}" var="dt1" />
<core:set value="${date.rows[0].date2}" var="dt2" />
<sql:update >
<![CDATA[
call PAC_PRC_POSTTOWIPS_SP ('admin', '${dt1}', '${dt2}', 'All', 'All', 12, 12, 'L', 'M', 'Q','X', 12, 12, 0, 12, 12, 12)
]]>
</sql:update>"

Regards,
Agin

[Paul Schofield]

For SQL Server the syntax is 

<sql:update>
EXEC Z_MY_CUSTOM_SP ?
<sql:param value="prior"/>
</sql:update>

For Oracle the syntax is

<sql:update>
CALL Z_MY_CUSTOM_SP(?)
<sql:param value="prior"/>
</sql:update>

For SQL Server you might need to qualify the SP with the schema name  of niku as follows

<sql:update>
EXEC niku.Z_MY_CUSTOM_SP ?
<sql:param value="prior"/>
</sql:update>

These examples are from the 15.9.1 documentation page 2970.

Also see : https://knowledge.broadcom.com/external/article/56513/command-to-execute-stored-procedure-from.html

The "niku" qualification is from here:

https://community.broadcom.com/communities/community-home/digestviewer/viewthread?MID=824136

[Paul Schofield]

It could also be an issue with the parameters. Looking at the code the batch parameter is decoded like this:

IF INSTR( batch, 'ALL') = 0 THEN
BEGIN
bSelectiveBatches := 1;
END;

You have the parameter in mixed case, but Oracle (which I assume you are on from the code snippet) is case sensitive in character matching. 

Similarly you have 'All' for codetype but the code checks for the following:

IF codetype = 'Batches' THEN
cExternalBatchID := code;
ELSE
cExternalBatchID := NULL;
END IF;

IF codetype = 'Locations' THEN
cLocationID := code;
ELSE
cLocationID := NULL;
END IF;

IF codetype = 'Clients' THEN
cCOMPANY_CODE := code;
END IF;

IF codetype = 'Projects' THEN
BEGIN
iSubVal := INSTR( code, ':');
cProject := SUBSTR(code, iSubVal + 1);
END;
ELSE
cProject := NULL;
END IF;

IF codetype = 'Employees' THEN
cResource_Code := code;
ELSE
cResource_Code := NULL;
END IF;

IF codetype = 'IncurredBy' THEN
cIncurredBy := code ;
ELSE
cIncurredBy := NULL ;
END IF ;

IF INSTR( batch, 'BATCH') = 0 THEN
iSourceModuleExclude := 2;
ELSE
BEGIN
IF INSTR( batch, 'ALL') = 0 THEN
BEGIN
bSelectiveBatches := 1;
END;
END IF;
END;
END IF;

in_clntcode also looks a bit strange. It's specified as 12 but it's actually a company code:

cCOMPANY_CODE := RTRIM(in_clntcode);

Looking at the select statement too...

I'd get rid of it, as it's not producing what you want:


Just hard code the dates as 1990-01-01 and 2999-12-31 as strings in the call.


Can you post the original call (pre-GEL) that was working?

[Agin Vadath]

Hi Paul,

Thanks for the quick turn around.

We are in Oracle DB. We have enabled the trace and see that null value is referred as 12, hence replaced null values with 12. GEL process  wants all the parameters of the SP to be keyed in to get the process to complete. 

We are calling the OOTB SP from our custom SP. Please see the code snippet used in our custom SP.

PAC_PRC_POSTTOWIPS_SP(
batchuser=>'admin',
in_datef=>'1990-01-01 00:00:00',
in_datet=>'2999-12-31 00:00:00',
codetype=>'All',
in_code=>'All',
in_clntcode=>null,
labor=>'L',
material=>'M',
equipment=>'Q',
expense=>'X',
poc=>null,
batch=>null,
recalculate_currency=>0
);
We will hard coded the dates and update you the result. 

Regards,
Agin

[Paul Schofield]

Hello again,

NULL should be specified as NULL (not 12 as that is most certainly not a null value). Replace the dates with the hard-coded values, and replace the 12's with NULL.

Good luck!

Paul

[Agin Vadath]

Thanks a ton  @Paul Schofield

It works. Here i hardcoded the dates and replaced 12 as null.

My code now looks like below.

<sql:update >
<![CDATA[
call PAC_PRC_POSTTOWIPS_SP ('admin', '1990-01-01 00:00:00', '2999-12-31 00:00:00', 'All', 'All', null, null, 'L', 'M', 'Q','X', null, null, 0, null, null, null)
]]>


</sql:update>​

[Paul Schofield]

Bingo!