Hello.
I need to know if the envisaged solution below is possible :
In a BPM, I want to use, as first step a call of a stored procedure that make some update tasks on a database.
I try to use a JDBC Sender Adapter.
I want If possible to use a synchronous call because I must want to know if updates processes are ok using output parameters of procedure.
Then a second step will be to get delta data in an extraction table.
Is it possible to get output parameter of procedure in XI ?
Is it possible to call a procedure with parameters ?
If it is not possible : What are my solutions ?
Database : Oracle
System : PI 7.0
Code of this procedure :
CREATE OR REPLACE PROCEDURE NC_POC_TRAITEMENT ( p_error OUT BOOLEAN, p_msg OUT VARCHAR2 )
AS
version_no VARCHAR2(30):= '2.09';
-- Gestion des erreurs
-- ===================
l_error boolean;
l_msg varchar2(350);
BEGIN
p_error := false;
-- __________________________________________________________
-- **********************************************************
-- First part of process : Update NC_POC_TRACKWISE_EXTRACTION
-- ==========================================================
BEGIN
BEGIN
-- *******************************
-- Empty extraction table
-- *******************************
-- prompt 'Delete all data in table NC_POC_TRACKWISE_EXTRACTION'
delete from GEN$ZIN117.NC_POC_TRACKWISE_EXTRACTION;
EXCEPTION
WHEN OTHERS THEN
l_msg := 'Error during 1st part : Delete all entries of NC_POC_TRACKWISE_EXTRACTION table'||SQLCODE;
RAISE;
END;
BEGIN
-- *************************************************
-- Inserts deletion instructions to extraction table
-- *************************************************
-- prompt ¿Inserts deletion instructions in table NC_POC_TRACKWISE_EXTRACTION'
insert into GEN$ZIN117.NC_POC_TRACKWISE_EXTRACTION
select PR_ID,
DIVISION,
GRID_SEQ_NO,
LOT_CONCERNE,
DECISION,
CODE_STATUT,
'Annule',
CODE_TYPE,
TYPE,
DATE_DE_FIN_PREVUE,
DATE_CREATION,
COORDINATEUR,
OBJET,
'S',
'SAP,ILOTS',
DATE_CLOTURE
from GEN$ZIN117.NC_POC_TRACKWISE_REFERENCE
where (PR_ID, LOT_CONCERNE) not in (
select PR_ID,
LOT_CONCERNE
from GEN$ZIN117.V_NC_LOT_TRACKWISE
);
EXCEPTION
WHEN OTHERS THEN
l_msg := 'Error during 1st part : Insert deletion instructions in NC_POC_TRACKWISE_EXTRACTION table'||SQLCODE;
RAISE;
END;
BEGIN
-- *****************************************************
-- Inserts ¿SAP update¿ instructions to extraction table
-- *****************************************************
-- prompt ¿Inserts ¿SAP update¿ instructions in table NC_POC_TRACKWISE_EXTRACTION'
insert into GEN$ZIN117.NC_POC_TRACKWISE_EXTRACTION
select PR_ID,
DIVISION,
GRID_SEQ_NO,
LOT_CONCERNE,
DECISION,
CODE_STATUT,
STATUT,
CODE_TYPE,
TYPE,
DATE_DE_FIN_PREVUE,
DATE_CREATION,
COORDINATEUR,
OBJET,
'M',
'SAP',
DATE_CLOTURE
from GEN$ZIN117.V_NC_LOT_TRACKWISE V
where exists (
select PR_ID,
LOT_CONCERNE
from GEN$ZIN117.NC_POC_TRACKWISE_REFERENCE R
where R.PR_ID = V.PR_ID
and R.LOT_CONCERNE = V.LOT_CONCERNE
and ( R.STATUT <> V.STATUT
or R.CODE_TYPE <> V.CODE_TYPE
or R.DATE_DE_FIN_PREVUE <> V.DATE_DE_FIN_PREVUE
or R.DIVISION <> V.DIVISION)
);
EXCEPTION
WHEN OTHERS THEN
l_msg := 'Error during 1st part : Insert "SAP" update instructions in NC_POC_TRACKWISE_EXTRACTION table'||SQLCODE;
RAISE;
END;
BEGIN
-- ******************************************************
-- Inserts ¿Ilot update¿ instructions to extraction table
-- ******************************************************
-- prompt ¿Inserts ¿Ilot update¿ instructions in table NC_POC_TRACKWISE_EXTRACTION'
insert into GEN$ZIN117.NC_POC_TRACKWISE_EXTRACTION
select PR_ID,
DIVISION,
GRID_SEQ_NO,
LOT_CONCERNE,
DECISION,
CODE_STATUT,
STATUT,
CODE_TYPE,
TYPE,
DATE_DE_FIN_PREVUE,
DATE_CREATION,
COORDINATEUR,
OBJET,
'M',
'ILOTS',
DATE_CLOTURE
from GEN$ZIN117.V_NC_LOT_TRACKWISE V
where exists ( select PR_ID, LOT_CONCERNE
from GEN$ZIN117.NC_POC_TRACKWISE_REFERENCE R
where R.PR_ID = V.PR_ID
and R.LOT_CONCERNE =V.LOT_CONCERNE
and ( R.STATUT <> V.STATUT
or R.CODE_TYPE <> V.CODE_TYPE
or R.DATE_DE_FIN_PREVUE <> V.DATE_DE_FIN_PREVUE
or R.DATE_CREATION <> V.DATE_CREATION
or R.DECISION <> V.DECISION
or R.COORDINATEUR <> V.COORDINATEUR
or R.OBJET <> V.OBJET
)
);
EXCEPTION
WHEN OTHERS THEN
l_msg := 'Error during 1st part : Insert "Ilots" update instructions in NC_POC_TRACKWISE_EXTRACTION table'||SQLCODE;
RAISE;
END;
BEGIN
-- **************************************************
-- Inserts insertion instructions to extraction table
-- **************************************************
-- prompt 'Inserts insertion instructions in table NC_POC_TRACKWISE_EXTRACTION
insert into GEN$ZIN117.NC_POC_TRACKWISE_EXTRACTION
select PR_ID,
DIVISION,
GRID_SEQ_NO,
LOT_CONCERNE,
DECISION,
CODE_STATUT,
STATUT,
CODE_TYPE,
TYPE,
DATE_DE_FIN_PREVUE,
DATE_CREATION,
COORDINATEUR,
OBJET,
'C' ,
'SAP,ILOTS',
DATE_CLOTURE
from GEN$ZIN117.V_NC_LOT_TRACKWISE
where (PR_ID, LOT_CONCERNE) not in (
select PR_ID,
LOT_CONCERNE
from GEN$ZIN117.NC_POC_TRACKWISE_REFERENCE
);
EXCEPTION
WHEN OTHERS THEN
l_msg := 'Error during 1st part : Insert globale update instructions in NC_POC_TRACKWISE_EXTRACTION table'||SQLCODE;
RAISE;
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
p_error := true;
p_msg := l_msg;
END;
-- __________________________________________________________
-- **********************************************************
-- Second part of process : Update NC_POC_TRACKWISE_REFERENCE
-- ==========================================================
IF p_error = false THEN
BEGIN
BEGIN
-- ********************************************
-- Update Reference table with extraction table
-- ********************************************
-- 1) Deletions to do
-- *********************************************
-- prompt 'Update reference table (Deletions)'
delete from GEN$ZIN117.NC_POC_TRACKWISE_REFERENCE
where (PR_ID,LOT_CONCERNE) in ( select PR_ID,
LOT_CONCERNE
from GEN$ZIN117.NC_POC_TRACKWISE_EXTRACTION
where CODE_ACTION='S'
);
EXCEPTION
WHEN OTHERS THEN
l_msg := 'Error during 2nd part : Delete old entries in NC_POC_TRACKWISE_REFERENCE table'||SQLCODE;
RAISE;
END;
BEGIN
-- *********************************************
-- 2) Insertions to do
-- *********************************************
-- prompt 'Update reference table (Insertions)'
insert into GEN$ZIN117.NC_POC_TRACKWISE_REFERENCE
select PR_ID,
DIVISION,
GRID_SEQ_NO,
LOT_CONCERNE,
DECISION,
CODE_STATUT,
STATUT,
CODE_TYPE,
TYPE,
DATE_DE_FIN_PREVUE,
DATE_CREATION,
COORDINATEUR,
OBJET,
DATE_CLOTURE
from GEN$ZIN117.NC_POC_TRACKWISE_EXTRACTION
where CODE_ACTION='C';
EXCEPTION
WHEN OTHERS THEN
l_msg := 'Error during 2nd part : Create new entries in NC_POC_TRACKWISE_REFERENCE table'||SQLCODE;
RAISE;
END;
BEGIN
-- *********************************************
-- 3) Update from SAP to do
-- *********************************************
-- prompt 'Update reference table (Updates from SAP)'
update GEN$ZIN117.NC_POC_TRACKWISE_REFERENCE R
set ( R.STATUT,
R.CODE_TYPE,
R.DATE_DE_FIN_PREVUE,
R.DIVISION
) = ( select E.STATUT,
E.CODE_TYPE,
E.DATE_DE_FIN_PREVUE,
E.DIVISION
from GEN$ZIN117.NC_POC_TRACKWISE_EXTRACTION E
where E.PR_ID = R.PR_ID
and E.LOT_CONCERNE = R.LOT_CONCERNE
and E.CODE_ACTION = 'M'
and E.CODE_CIBLE = 'SAP'
)
where (R.PR_ID, R.LOT_CONCERNE) in ( select PR_ID,
LOT_CONCERNE
from GEN$ZIN117.NC_POC_TRACKWISE_EXTRACTION
where CODE_ACTION = 'M'
and CODE_CIBLE = 'SAP'
);
EXCEPTION
WHEN OTHERS THEN
l_msg := 'Error during 2nd part : Update SAP modification in SAP in NC_POC_TRACKWISE_REFERENCE table'||SQLCODE;
RAISE;
END;
BEGIN
-- *********************************************
-- 4) Update from Ilot to do
-- *********************************************
-- prompt 'Update reference table (Updates from Ilot)'
update GEN$ZIN117.NC_POC_TRACKWISE_REFERENCE R
set ( R.STATUT,
R.CODE_TYPE,
R.DATE_DE_FIN_PREVUE,
R.DATE_CREATION,
R.DECISION,
R.COORDINATEUR,
R.OBJET
) = ( select E.STATUT,
E.CODE_TYPE,
E.DATE_DE_FIN_PREVUE,
E.DATE_CREATION,
E.DECISION,
E.COORDINATEUR,
E.OBJET
from GEN$ZIN117.NC_POC_TRACKWISE_EXTRACTION E
where E.PR_ID = R.PR_ID
and E.LOT_CONCERNE = R.LOT_CONCERNE
and E.CODE_ACTION = 'M'
and E.CODE_CIBLE = 'ILOTS'
)
where (R.PR_ID,R.LOT_CONCERNE) in ( select PR_ID,
LOT_CONCERNE
from GEN$ZIN117.NC_POC_TRACKWISE_EXTRACTION
where CODE_ACTION = 'M'
and CODE_CIBLE = 'ILOTS'
);
EXCEPTION
WHEN OTHERS THEN
l_msg := 'Error during 2nd part : Update Ilot modification in SAP in NC_POC_TRACKWISE_REFERENCE table'||SQLCODE;
RAISE;
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
p_error := true;
p_msg := l_msg;
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_error := true;
p_msg := 'Un-detailed Error';
END;
/[/code]
Message was edited by:
Jean-Charles CARRET
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.