• Solutions
• Data Quality
• dspCompose
• Sample Stored Procedure For Comparison Approval

Sample Stored Procedure For Comparison Approval

These sample stored procedures are original and modified code for the CompareIns stored procedure that must be registered to the Template Role Event rule. Refer to Register the CompareIns Stored Procedure for more information.

A user modifies the original code as needed for each client’s business needs.

As shown in the sample original code of the stored procedure (below), the commented section that starts after the “BEGIN” statement lists the guidelines and the SQL Code that must be added to the modified code between the “BEGIN” and “END” statements.

If a comparison approval for a request uses partial approvals, the user should include additional code in the modified stored procedure.

This code displays below the comment

--Delete logic needed to support partial approval  

in the modified stored procedure sample below.

Original Sample

 

USE[dspCompose_Data]

GO

/****** Object:  StoredProcedure [dbo].[webAB_BDC_MM02Compare1CompareIns]    Script Date: 8/8/2013 11:15:32 AM ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERPROCEDURE[dbo].[webAB_BDC_MM02Compare1CompareIns]

@RequestIDint

 AS

BEGIN

/* This Procedure is used for populating the ComparisonApproval page for a Template.

*

*  This procedure must be modified to correctly populate table ttAB_BDC_MM02Compare1Compare.

*  ttAB_BDC_MM02Compare1Compare should contain one record for each corresponding record

*  in the ttAB_BDC_MM02Compare1 table.

*

*  Records in ttAB_BDC_MM02Compare1Compare should contain the values currently in SAP

*  for the data inserted into ttAB_BDC_MM02Compare1.

*

*  The following are guidelines for modifying this procedure:

*  1) Delete any records from ttAB_BDC_MM02Compare1Compare for the current Request.

*  2) Delete any records from ttAB_BDC_MM02Compare1Compare where ID = an ID retrieved by the

*       select statement.  When requests are automatically created by partial rejection of

*       another request, ID values will be duplicates of IDs inserted for the original request.

*  3) Remove the SELECT statement delivered in this procedure.

*  4) Insert into ttAB_BDC_MM02Compare1Compare by joining ttAB_BDC_MM02Compare1 with

*     all source tables or views originally used to populate ttAB_BDC_MM02Compare1.

*     – Key fields should be populated from ttAB_BDC_MM02Compare1Compare.

*     – Data fields should be populated from the original source(s).

*     – Limit the Select/Insert to the current RequestID.

*/

SELECT'‘WHERE 1 = 0

END

 

Modified Sample

 

USE[dspCompose_Data]

GO

/****** Object:  StoredProcedure [dbo].[webAB_BDC_MM02Compare1CompareIns]    Script Date: 8/14/2013 2:18:42 PM ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERPROCEDURE[dbo].[webAB_BDC_MM02Compare1CompareIns]

@RequestIDint

 AS

BEGIN

/* This Procedure is used for populating the ComparisonApproval page for a Template.

*

*  This procedure must be modified to correctly populate table ttAB_BDC_MM02Compare1Compare.

*  ttAB_BDC_MM02Compare1Compare should contain one record for each corresponding record

*  in the ttAB_BDC_MM02Compare1 table.

*

*  Records in ttAB_BDC_MM02Compare1Compare should contain the values currently in SAP

*  for the data inserted into ttAB_BDC_MM02Compare1.

*

*  The following are guidelines for modifying this procedure:

*  1) Delete any records from ttAB_BDC_MM02Compare1Compare for the current Request.

*  2) Delete any records from ttAB_BDC_MM02Compare1Compare where ID = an ID retrieved by the

*        select statement.  When requests are automatically created by partial rejection of

*        another request, ID values will be duplicates of IDs inserted for the original request.

*  3) Remove the SELECT statement delivered in this procedure.

*  4) Insert into ttAB_BDC_MM02Compare1Compare by joining ttAB_BDC_MM02Compare1 with

*     all source tables or views originally used to populate ttAB_BDC_MM02Compare1.

*     – Key fields should be populated from ttAB_BDC_MM02Compare1Compare.

*     – Data fields should be populated from the original source(s).

*     – Limit the Select/Insert to the current RequestID.

*/

DELETEFROM  ttAB_BDC_MM02Compare1CompareWHERE RequestID= @RequestID

 

--Delete logic needed to support partial approval 
DELETE FROM   ttAB_BDC_MM02Compare1Compare

WHEREIDIN(SELECTIDFROMttAB_BDC_MM02Compare1WHERERequestID

@RequestID)

 

INSERTINTOttAB_BDC_MM02Compare1Compare

([ID],

[RequestID],

[RMMG1-MATNR],

[MARA-BISMT],

[MARA-EXTWG],

[MARA-LABOR],

[MARA-BEGRU],

[MARA-BRGEW],

[MARA-GEWEI],

[MARA-NTGEW],

[MARA-WRKST])

 

SELECT

dbo.ttAB_BDC_MM02Compare1.ID,

dbo.ttAB_BDC_MM02Compare1.RequestID,

dbo.ttAB_BDC_MM02Compare1.[RMMG1-MATNR],

dgSAP.dbo.MARA.BISMT,

dgSAP.dbo.MARA.EXTWG,

dgSAP.dbo.MARA.LABOR,

dgSAP.dbo.MARA.BEGRU,

dgSAP.dbo.MARA.BRGEW,

dgSAP.dbo.MARA.GEWEI,

dgSAP.dbo.MARA.NTGEW,

dgSAP.dbo.MARA.WRKST

FROM  dbo.ttAB_BDC_MM02Compare1INNERJOINdgSAP.dbo.MARAONdbo.ttAB_BDC_MM02Compare1.[RMMG1-MATNR]=dgSAP.dbo.MARA.MATNR

WHEREdbo.ttAB_BDC_MM02Compare1.RequestID=@RequestID

 

END