Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

(Back to S-FS Formatted Searches SQL Posted to Wiki Listing - S-FS Formatted Searches SQL Posted to Wiki   )

TYPE: Frequently Requested SQL

AUTHOR NAME: Neetu Dhami

PURPOSE AND BACKGROUND:  This SQL was created in 2007A SP01 PL08.  This SQL is used to autogenerate BP Card code based on Card Type for e.g. Card code for Leed is generated with Prefix L, similary C for customer and S for Suppliers.

Copy and Paste this SQL:

--S-FS S-FS  Autogenerate Card Code Based on Card Type

--DESCRIPTION:  Formatted Search used in Business Partner Master Data window on CardCode Field.  SQL populates the CardCode automatically and can be trigged based on card type.

--AUTHOR(s):
--Version 1 Neetu Dhami 24 May 2011

DECLARE @cardType CHAR(1)
SET @cardType = $[OCRD.CardType]
SELECT
 CASE @cardType
  WHEN 'C' THEN (SELECT 'C' + REPLACE(STR(MAX(CAST(SUBSTRING(T1.CardCode, 2, LEN(T1.CardCode)) AS INTEGER)) + 1, 7), ' ', 0)  FROM OCRD T1 WHERE T1.CardType = 'C')
  WHEN 'S' THEN (SELECT 'S' + REPLACE(STR(MAX(CAST(SUBSTRING(T1.CardCode, 2, LEN(T1.CardCode)) AS INTEGER)) + 1, 7), ' ', 0)  FROM OCRD T1 WHERE T1.CardType = 'S')
  WHEN 'L' THEN (SELECT 'L' + REPLACE(STR(MAX(CAST(SUBSTRING(T1.CardCode, 2, LEN(T1.CardCode)) AS INTEGER)) + 1, 7), ' ', 0)  FROM OCRD T1 WHERE T1.CardType = 'L')
  ELSE ''
 END

  • No labels