PFCSIG Updating Sybase Table using Sybase System 11 (Novell) & PB 6.5

[Ravindra Harve]

I have the following stored procedure which works perfectly all right when its run using WISQL. But when the stored procedure is executed from PB 6.5, the output gives me the next sequential number, but it dosen't insert rows into GRN_HEADER, GRN_DETAILS_STOCK and dosen't update the control file.

////// PB Global Function ////

gf_grn_proc (in PB 6.5)
DECLARE grnnoproc PROCEDURE for dbo.proc_get_po_dtls @nipo_no = :as_grn_no, @outgrn = :ls_output ;
Execute grnnoproc ;
FETCH grnnoproc into :ls_output
CLOSE grnnoproc;
Commit using SQLCA ;
 

/// Sybase Stored Procedure ( Nested Stored Procedure)

CREATE PROCEDURE dbo.proc_get_po_dtls(
@nipo_no varchar(8),
@outgrn  varchar(8) output
)
WITH RECOMPILE
AS
BEGIN
DECLARE @tran_level int
DECLARE @po_seq_no varchar(8)
DECLARE @oput varchar(8)
select @tran_level = @@trancount
select @po_seq_no = po_header.po_seq_no from po_header
where po_header.po_number = @nipo_no
insert  grn_header ( po_number,supplier_code, po_seq_no)
select  po_header.po_number, po_header.supplier_code,
po_header.po_seq_no
from po_header
where po_header.po_number = @nipo_no
select @tran_level = @@trancount
insert grn_details_stock
(grn_details_stock.part_code,grn_details_stock.qty,
grn_details_stock.uom,grn_details_stock.po_seq_no )
select  po_details_stock.part_code,
po_details_stock.quantity,
po_details_stock.uom,po_details_stock.po_seq_number
from po_details_stock
where po_details_stock.po_seq_number = @po_seq_no
exec genno 'grn_seq_no', @oput output
update grn_header set seq_no = @oput
where grn_header.po_number = @nipo_no and grn_header.seq_no
= 'N'
update grn_details_stock set grn_seq_number = @oput
where grn_details_stock.po_seq_no = @po_seq_no and
grn_details_stock.grn_seq_number = 'N'
if (@tran_level = 0)
begin
commit tran
select @outgrn = @oput
select @outgrn
return
end
else
begin
rollback tran
return
end
END
 

Thanks in Advance
Regards
Ravi
[EMAIL PROTECTED]