SAP VBAK / VBAP Extract

tmumaw — Tue, 26 Jan 2010 16:23:18 GMT

Here is my issue. I am trying to extract from VBAK all sales documents where AUART = 'ZRRC' or 'ZBPC' and GUEBG >= '20050101'. This extract works fine. The problem is I only want to extract from VBAP where I have a match from VBAK. For some reason it's not returning the correct VBAP records. Here is the script I am using.

Thanks

Thom

[VBAK]:
Load
[VKORG] as [Sales Organization],
[VTWEG] as [Dist Ch],
[VBTYP] as [Doc Cat],
[AUART] as [Sales Document Type],
[VGBEL] as [Reference document],
[KURST] as [Exchange Rate Type],
[VBELN], //as [Sales Document_VBELN],
[KUNNR] as [Sold To],
[VKBUR] as [Sales Office],
[VKGRP] as [Sales Group],
[ANGDT] as [Quote Valid From],
[BNDDT] as [Quote Valid To],
[AUDAT] as [Document Date],
[TRVOG] as [Trans Grp],
[AUGRU] as [Order Reason],
[FAKSK] as [Billing Block],
[NETWR] as [Contract Net Value],
[WAERK] as [Doc Curr],
[GUEBG] as [Valid From Date],
[GUEEN] as [Valid To Date],
[KNUMV] as [Doc Cond No.],
[KALSM] as [Pricing Proceedure],
[FKARA] as [Order Rel Bill Type],
[KTEXT] as [Description],
[BNAME] as [Name_BNAME],
[TELF1] as [Phone No.],
[KOSTL] as [Cost Center Hdr],
[STWAE] as [Statistics Curr],
[KVGR1] as [Cust Grp1],
[KVGR2] as [Cust Grp2],
[KVGR3] as [Cust Grp3],
[KVGR4] as [Cust Grp4],
[KVGR5] as [Cust Grp5],
[KOKRS] as [Controlling Area],
[PS_PSP_PNR] as [WBS Element],
[KNKLI] as [Credit Acct],
[OBJNR] as [Object No. Hdr],
[TAXK1] as [Alt Tax Class],
[TAXK2] as [Tax Class2],
[TAXK3] as [Tax Class3],
[TAXK4] as [Tax Class4],
[TAXK5] as [Tax Class5],
[TAXK6] as [Tax Class6],
[TAXK7] as [Tax Class7],
[TAXK8] as [Tax Class8],
[TAXK9] as [Tax Class9],
[ZZDT_DOORHANG] as [Door Hanger],
[ZZDT_POSTCARD] as [Post Card Mailing],
[ZZDT_MAILED] as [Mailed],
[ZZDT_FOLLOWUP] as [Follow-Up Date]
;
SQL Select * from VBAK where AUART = 'ZRRC' or AUART = 'ZBPC' and GUEBG >= '$(vYear)'
;
Store VBAK into QVD\VBAK.QVD
;

[VBAPTEMP]:
SQL Select *
FROM VBAP
WHERE MATNR <> '000000000000000951' and KZWI1 <> 0
(Select VBELN from VBAK where AUART = 'ZRRC' or AUART = 'ZBPC' and GUEBG >= '$(vYear)')
;

[VBAP_Temp]: // Sales Document: Item Data
Load
[MATNR] as [Material],
[SPART] as [Div],
[KMEIN] as [UOM],
[MEINS] as [Base UOM],
[VRKME] as [Sales Unit],
[VBELV] as [Originating Doc],
[VGBEL] as [Quote],
[VGPOS] as [Quote Line],
[PSTYV] as [Item Category],
[VBELN],
[WERKS] as [Plant],
[NETWR] as [Net Value],
[WAERK] as [DocCurr],
[KOSTL] as [Cost Center],
[PS_PSP_PNR] as [WBS Element Line],
[POSNR] as [Contract Line],
[MATKL] as [Material Grp],
[ABGRU] as [Reject Reason],
[ZMENG] as [QTY],
[FAKSP] as [Billing Block Line],
[TAXM1] as [Tax Class 1],
[TAXM2] as [Tax Class 2],
[TAXM3] as [Tax Class 3],
[TAXM4] as [Tax Class 4],
[TAXM5] as [Tax Class 5],
[TAXM6] as [Tax Class 6],
[TAXM7] as [Tax Class 7],
[TAXM8] as [Tax Class 8],
[TAXM9] as [Tax Class 9],
[KZWI1] as [Subtotal 1],
[KZWI2] as [Subtotal 2],
[KZWI3] as [Subtotal 3],
[KZWI4] as [Subtotal 4],
[KZWI5] as [Subtotal 5],
[KZWI6] as [Subtotal 6],
[STCUR] as [Exchange Rate],
[PRCTR] as [Profit Center],
[MVGR1] as [MatGrp1],
[MVGR2] as [MatGrp2],
[MVGR3] as [MatGrp3],
[MVGR4] as [MatGrp4],
[MVGR5] as [MatGrp5],
[OBJNR] as [Object No.Item],
[VKAUS] as [TPLR],
[FKBER] as [Functional Area]
RESIDENT VBAPTEMP
WHERE exists([VBELN], [VBELN])
;

//Drop table VBAP_Temp;
Drop table VBAPTEMP;

SAP VBAK / VBAP Extract

tmumaw — Wed, 27 Jan 2010 15:16:18 GMT

Solved the problem.....Here is my solution:

[VBAK]:

Load

[VKORG] as [Sales Organization],

[VTWEG] as [Dist Ch],

[VBTYP] as [Doc Cat],

[AUART] as [Sales Document Type],

[VGBEL] as [Reference document],

[KURST] as [Exchange Rate Type],

[VBELN], //as [Sales Document_VBELN],

[KUNNR] as [Sold To],

[VKBUR] as [Sales Office],

[VKGRP] as [Sales Group],

[ANGDT] as [Quote Valid From],

[BNDDT] as [Quote Valid To],

[AUDAT] as [Document Date],

[TRVOG] as [Trans Grp],

[AUGRU] as [Order Reason],

[FAKSK] as [Billing Block],

[NETWR] as [Contract Net Value],

[WAERK] as [Doc Curr],

[GUEBG] as [Valid From Date],

[GUEEN] as [Valid To Date],

[KNUMV] as [Doc Cond No.],

[KALSM] as [Pricing Proceedure],

[FKARA] as [Order Rel Bill Type],

[KTEXT] as [Description],

[BNAME] as [Name_BNAME],

[TELF1] as [Phone No.],

[KOSTL] as [Cost Center Hdr],

[STWAE] as [Statistics Curr],

[KVGR1] as [Cust Grp1],

[KVGR2] as [Cust Grp2],

[KVGR3] as [Cust Grp3],

[KVGR4] as [Cust Grp4],

[KVGR5] as [Cust Grp5],

[KOKRS] as [Controlling Area],

[PS_PSP_PNR] as [WBS Element],

[KNKLI] as [Credit Acct],

[OBJNR] as [Object No. Hdr],

[TAXK1] as [Alt Tax Class],

[TAXK2] as [Tax Class2],

[TAXK3] as [Tax Class3],

[TAXK4] as [Tax Class4],

[TAXK5] as [Tax Class5],

[TAXK6] as [Tax Class6],

[TAXK7] as [Tax Class7],

[TAXK8] as [Tax Class8],

[TAXK9] as [Tax Class9],

[ZZDT_DOORHANG] as [Door Hanger],

[ZZDT_POSTCARD] as [Post Card Mailing],

[ZZDT_MAILED] as [Mailed],

[ZZDT_FOLLOWUP] as [Follow-Up Date]

;

SQL Select * from VBAK where (AUART = 'ZRRC' or AUART = 'ZBPC') and

GUEBG >= '$(vYear)' and GUEBG <> ''

;

Store VBAK into QVD\VBAK.QVD

;

[VBAPTEMP]:

SQL Select *

FROM VBAP

WHERE MATNR <> '000000000000000951' and KZWI1 <> 0

AND VBELN in (Select VBELN from VBAK where (AUART = 'ZRRC' or AUART = 'ZBPC') and

GUEBG >= '$(vYear)' AND GUEBG <> '')

;

[VBAP_Temp]: // Sales Document: Item Data

Load

[MATNR] as [Material],

[SPART] as [Div],

[KMEIN] as [UOM],

[MEINS] as [Base UOM],

[VRKME] as [Sales Unit],

[VBELV] as [Originating Doc],

[VGBEL] as [Quote],

[VGPOS] as [Quote Line],

[PSTYV] as [Item Category],

[VBELN],

[WERKS] as [Plant],

[NETWR] as [Net Value],

[WAERK] as [DocCurr],

[KOSTL] as [Cost Center],

[PS_PSP_PNR] as [WBS Element Line],

[POSNR] as [Contract Line],

[MATKL] as [Material Grp],

[ABGRU] as [Reject Reason],

[ZMENG] as [QTY],

[FAKSP] as [Billing Block Line],

[TAXM1] as [Tax Class 1],

[TAXM2] as [Tax Class 2],

[TAXM3] as [Tax Class 3],

[TAXM4] as [Tax Class 4],

[TAXM5] as [Tax Class 5],

[TAXM6] as [Tax Class 6],

[TAXM7] as [Tax Class 7],

[TAXM8] as [Tax Class 8],

[TAXM9] as [Tax Class 9],

[KZWI1] as [Subtotal 1],

[KZWI2] as [Subtotal 2],

[KZWI3] as [Subtotal 3],

[KZWI4] as [Subtotal 4],

[KZWI5] as [Subtotal 5],

[KZWI6] as [Subtotal 6],

[STCUR] as [Exchange Rate],

[PRCTR] as [Profit Center],

[MVGR1] as [MatGrp1],

[MVGR2] as [MatGrp2],

[MVGR3] as [MatGrp3],

[MVGR4] as [MatGrp4],

[MVGR5] as [MatGrp5],

[OBJNR] as [Object No.Item],

[VKAUS] as [TPLR],

[FKBER] as [Functional Area]

RESIDENT VBAPTEMP

WHERE exists([VBELN], [VBELN])

;

Drop table VBAPTEMP;

Left join (VBAK)

Load *

Resident VBAP_Temp;

Drop table VBAP_Temp;

topic SAP VBAK / VBAP Extract in QlikView

SAP VBAK / VBAP Extract

SAP VBAK / VBAP Extract