Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

BP CODE GENRATION PROBLEM FOR OUT OF INDIA CUSTOMER

Hello

I create following Query for atomatic genration for customer,Lead,Vendor Code & it work properly for Country India but when i select Other Country then code can't generate so give me solution for this query

if ($[OCRD.CardType] IN ('C')) AND ($[CRD1.Country] = 'IN' OR

$[CRD1.Country] IS NULL OR

$[CRD1.Country] = '')

select 'C'substring(replace(replace(replace($[ocrd.cardname],',',''),' ',''),'.',''),1,2) case when substring($[CRD1.City],1,3)<>'' then substring($[CRD1.City],1,3) else 'NSK' end+ case when substring($[CRD1.State],1,2)<>'' then substring($[CRD1.State],1,2) else 'MH' end +

(case when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('C')),'0'))+1)=1 then

'000'convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('C')),'0'))1))

when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('C')),'0'))+1)=2 then

'00'convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('C')),'0'))1))

when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('C')),'0'))+1)=3 then

'0'convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('C')),'0'))1))

when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('C')),'0'))+1)=4 then

convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('C')),'0'))+1)) end)

else if ($[OCRD.CardType] IN ('L')) AND ($[CRD1.Country] = 'IN' OR

$[CRD1.Country] IS NULL OR

$[CRD1.Country] = '')

select 'L'substring(replace(replace(replace($[ocrd.cardname],',',''),' ',''),'.',''),1,2) case when substring($[CRD1.City],1,3)<>'' then substring($[CRD1.City],1,3) else 'NSK' end+ case when substring($[CRD1.State],1,2)<>'' then substring($[CRD1.State],1,2) else 'MH' end +

(case when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('L')),'0'))+1)=1 then

'000'convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('L')),'0'))1))

when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('L')),'0'))+1)=2 then

'00'convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('L')),'0'))1))

when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('L')),'0'))+1)=3 then

'0'convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('L')),'0'))1))

when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('L')),'0'))+1)=4 then

convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('L')),'0'))+1)) end)

else if ($[OCRD.CardType] IN ('S')) AND ($[CRD1.Country] = 'IN' OR

$[CRD1.Country] IS NULL OR

$[CRD1.Country] = '')

select 'S'substring(replace(replace(replace($[ocrd.cardname],',',''),' ',''),'.',''),1,2) case when substring($[CRD1.City],1,3)<>'' then substring($[CRD1.City],1,3) else 'NSK' end+ case when substring($[CRD1.State],1,2)<>'' then substring($[CRD1.State],1,2) else 'MH' end +

(case when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('S')),'0'))+1)=1 then

'000'convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('S')),'0'))1))

when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('S')),'0'))+1)=2 then

'00'convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('S')),'0'))1))

when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('S')),'0'))+1)=3 then

'0'convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('S')),'0'))1))

when len(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('S')),'0'))+1)=4 then

convert(varchar,(convert(decimal,isnull((select max(convert(decimal,isnull(substring(cardcode,9,4),0))) from ocrd where substring(cardcode,9,4)<>'' and cardtype in ('S')),'0'))+1)) end)

Former Member
Not what you were looking for? View more on this topic or Ask a question