SAP Tables: VBAK / VBAP / VBPA

tmumaw — Wed, 24 Jun 2009 20:58:16 GMT

Here is my code.......

////=================================================================================================

//// [TABLE NAME: Billing Document Header] ([SAP DB TABLE NAME: VBRK])

////=================================================================================================

// Table: VBRK - Billing Document Header

[VBRK]:

Load

// Key fields:

[VBELN] as [BillingDocument_Key],

// [VBELN] & '/' & [KUNAG] as [BillingDocument_KeyCust],

// [BELNR] as [%DocumentNumber_Key],

// [KUNAG] as [Customer],

// Additional fields:

Date(FKDAT /* + $(OffsetNumber) */ ) as [Invoice Date],

Year(FKDAT /* + $(OffsetNumber) */ ) as InvoiceYear,

Year(FKDAT /* + $(OffsetNumber) */ ) as Year,

Month(FKDAT /* + $(OffsetNumber) */ ) as Month,

Num(Month(FKDAT /* + $(OffsetNumber) */ )) as Month_key,

Day(FKDAT /* + $(OffsetNumber) */ ) as InvoiceDay,

Week(FKDAT /* + $(OffsetNumber) */ ) as Week,

Weekday(FKDAT /* + $(OffsetNumber) */ ) as InvoiceWeekday,

Month(FKDAT) & '-' & Year(FKDAT) as InvoiceMonthYear,

Year(FKDAT) & Num(Month(FKDAT),'00') as YearMonth,

date(monthstart(FKDAT), 'MMM-YYYY') AS LogicalMonthYear,

Year2Date(FKDAT)*-1 as CurYTDFlag,

Year2date(FKDAT,-1)*-1 as LastYTDFlag,

Year2Date(FKDAT,-6)*-1 as CurYTDFlag_DEMO_IDES, // For Demo Purpose SAP System QT1 - IDES Environment (International Demo & Education System)

Year2Date(FKDAT,-7)*-1 as LastYTDFlag_DEMO_IDES, // For Demo Purpose SAP System QT1 - IDES Environment (International Demo & Education System)

1 as [Billing Counter],

// Renamed fields:

[FKDAT] as [Billing Date],

[WAERK] as [Document Currency],

[VBELN] as [Invoice Number],

[FKART] as [Billing Type]

// SAP's Orginal Source fields:

// [VKORG] as [Sales Org._VKORG],

// [VTWEG] as [Distr. Channel_VTWEG],

// [MANDT] as [Client_MANDT],

// [KAPPL] as [Application_KAPPL],

// [TAXK1] as [TaxClass1-Cust._TAXK1],

// [PLTYP] as [Price List_PLTYP],

// [WAERK] as [Doc. currency_WAERK],

// [SPART] as [Division_SPART],

// [LAND1] as [DestinationCtry_LAND1],

// [KONDA] as [Price group_KONDA],

// [INCO1] as [Incoterms_INCO1],

// [INCO2] as [Incoterms 2_INCO2],

// [BUKRS] as [Company Code_BUKRS],

// [REGIO] as [Region_REGIO],

// [COUNC] as [County code_COUNC],

// [TAXK2] as [TaxClass2-Cust._TAXK2],

// [CITYC] as [City code_CITYC],

// [TAXK3] as [TaxClass3-Cust._TAXK3],

// [KDGRP] as [Customer group_KDGRP],

// [KUNAG] as [Sold-to party_KUNAG],

// [KUNRG] as [Payer_KUNRG],

// [TAXK4] as [TaxClass4-Cust._TAXK4],

// [J_1AFITP] as [Tax type_J_1AFITP],

// [AEDAT] as [Changed on_AEDAT],

// [ERDAT] as [Created on_ERDAT],

// [ERNAM] as [Created by_ERNAM],

// [BELNR] as [Document Number_BELNR],

// [LOGSYS] as [Logical System_LOGSYS],

// [BUPLA] as [Business place_BUPLA],

// [GJAHR] as [Fiscal Year_GJAHR],

// [VBUND] as [Trading Partner_VBUND],

// [KURRF_DAT] as [Translation dte_KURRF_DAT],

// [XBLNR] as [Reference_XBLNR],

// [FKDAT] as [Billing date_FKDAT],

// [ERZET] as [Time_ERZET],

// [LCNUM] as [Financ.doc.no._LCNUM],

// [AKWAE] as [LettOfCredCrcy_AKWAE],

// [ZTERM] as [Payt terms_ZTERM],

// [ZUONR] as [Assignment_ZUONR],

// [KNUMV] as [Doc. condition_KNUMV],

// [KURST] as [Exch. Rate Type_KURST],

// [VBTYP] as [Document cat._VBTYP],

// [KIDNO] as [Payment Ref._KIDNO],

// [VBELN] as [Billing Doc._VBELN],

// [FKART] as [Billing Type_FKART],

// [KKBER] as [Cred.contr.area_KKBER],

// [VSBED] as [Shp.Cond._VSBED],

// [VKONT] as [Char12_VKONT],

// [NRZAS] as [Char12_NRZAS],

// [BZIRK] as [Sales district_BZIRK],

// [MSCHL] as [Dunning key_MSCHL],

// [MANSP] as [Dunn. block_MANSP],

// [MABER] as [Dunning Area_MABER],

// [NUMPG] as [Number of Pages_NUMPG],

// [BVTYP] as [Part.bank type_BVTYP],

// [STCEG] as [VAT reg.no._STCEG],

// [XEGDR] as [EU triang. deal_XEGDR],

// [POPER] as [Posting Period_POPER],

// [ZLSCH] as [Pmt Method_ZLSCH],

// [KTGRD] as [AcctAssgGr_KTGRD],

// [NETWR] as [Net value_NETWR],

// [FKTYP] as [BillingCategory_FKTYP],

// [VALDT] as [Fixed val.date_VALDT],

// [LANDTX] as [Tax depart. cty_LANDTX],

// [CMWAE] as [Currency_CMWAE],

// [EXNUM] as [For.tr.data no._EXNUM],

// [KNKLI] as [Credit account_KNKLI],

// [KURRF] as [Exch.rate-acct._KURRF],

// [TAXK5] as [TaxClass5-Cust._TAXK5],

// [TAXK6] as [TaxClass6-Cust._TAXK6],

// [TAXK7] as [TaxClass7-Cust._TAXK7],

// [TAXK8] as [TaxClass8-Cust._TAXK8],

// [TAXK9] as [TaxClass9-Cust._TAXK9],

// [FKSTO] as [Cancelled_FKSTO],

// [KNUMA] as [Agreement_KNUMA],

// [MWSBK] as [Tax amount_MWSBK],

// [STAFO] as [Update group_STAFO],

// [VALTG] as [Add.value days_VALTG],

// [STCEG_L] as [CtrySlsTxNo._STCEG_L],

// [MRNKZ] as [SubsInvProcess_MRNKZ],

// [STWAE] as [Stats Currency_STWAE],

// [EXPKZ] as [Export_EXPKZ],

// [BSTNK_VF] as [PO number_BSTNK_VF],

// [CMKUF] as [Exchange rate_CMKUF],

// [CPKUR] as [Set exchange rt_CPKUR],

// [HITYP_PR] as [HierTypePricing_HITYP_PR],

// [KALSM] as [Pric. procedure_KALSM],

// [RFBSK] as [Posting Status_RFBSK],

// [ZUKRI] as [Comb.criteria_ZUKRI],

// [FKART_RL] as [Inv.list type_FKART_RL],

// [FKDAT_RL] as [Billing date_FKDAT_RL],

// [SFAKN] as [Canclld bill.dc_SFAKN],

// [AKKUR] as [LettOfCredRate_AKKUR],

// [RPLNR] as [Paym.ca.pl.no._RPLNR],

// [FKART_AB] as [AccrualBillType_FKART_AB],

// [FKK_DOCSTAT] as [Add. Fin.Status_FKK_DOCSTAT],

// [STCEG_H] as [OriginSlsTxNo._STCEG_H]

;

//SQL Select * FROM VBRK WHERE FKDAT > '$(vYear)'

SQL Select * FROM VBRK WHERE VBELN = '0097450292'

;

//Store Data to QVD File:VBRK.qvd

store VBRK into qvd\VBRK_BillingDocument_Header$(vQVD_version).qvd;

//Drop table

drop table VBRK;

// Table: VBRP - Billing Document Item

[VBRP]:

Load

// Key fields:

// [VBELN] as [BillingDocument_Key],

[VBELN] & '/' & [POSNR] as [BillingDocumentItem_Key],

1 as [Billing Item Counter],

// Renamed fields:

[MATNR] as [%MaterialNumber_Key],

[PRCTR] as [VBRP Profit Center],

[NETWR] as [Sales Amount],

[NETWR] as [Net value],

[FKLMG] as [Billed Quantity in SKU],

[PRSDT] as [Pricing date],

[KVGR1] as [Customer Group 1],

[KVGR2] as [Customer Group 2],

[KVGR3] as [Customer Group 3],

[KVGR4] as [Customer Group 4],

[KVGR5] as [Customer Group 5],

[MATKL] as [Material Group 1],

[MVGR2] as [Material Group 2],

[MVGR3] as [Material Group 3],

[MVGR4] as [Material Group 4],

[MVGR5] as [Material Group 5],

[KURSK] as [Exchange Rate],

[UMVKN] as [Denominatr],

[UMVKZ] as [Numerator],

[KZWI1] as [Inv Gross],

[KZWI2] as [Inv Discounts],

[KZWI3] as [Inv Emp Discounts],

[KZWI4] as [Inv Sales Tax],

[KZWI5] as [Inv Notification Chrgs],

[KZWI6] as [Inv Misc Chrgs],

[MWSBP] as [Tax Amount],

[SKTOF] as [Cash Discount],

[POSNR] as [VBRP Item],

[PSTYV] as [VBRP Item Category],

[POSAR] as [VBRP Item Type],

[VBELN] as [VBRP Invoice]

// SAP's Orginal Source fields:

// [MATKL] as [Material Group_MATKL],

// [WERKS] as [Plant_WERKS],

// [MATNR] as [Material_MATNR],

// [MANDT] as [Client_MANDT],

// [ALAND] as [Country_ALAND],

// [TAXM1] as [Tax class. mat._TAXM1],

// [MWSKZ] as [Tax code_MWSKZ],

// [SPART] as [Division_SPART],

// [EANNR] as [EAN number_EANNR],

// [KOKRS] as [CO Area_KOKRS],

// [KOSTL] as [Cost Center_KOSTL],

// [KONDM] as [Mat.pricing grp_KONDM],

// [GSBER] as [Business Area_GSBER],

// [J_1AREGIO] as [Region_J_1AREGIO],

// [TAXM2] as [TaxClass2-Matl._TAXM2],

// [TAXM3] as [TaxClass3-Matl._TAXM3],

// [TXJCD] as [Jurisdict. Code_TXJCD],

// [VKORG_AUFT] as [SOrg of order_VKORG_AUFT],

// [EAN11] as [EAN/UPC_EAN11],

// [VRKME] as [Sales unit_VRKME],

// [POSNR] as [Item_POSNR],

// [UPMAT] as [MnItm PrRefMatl_UPMAT],

// [UKONM] as [MnItem MatPrcGr_UKONM],

// [PRCTR] as [Profit Center_PRCTR],

// [BEMOT] as [AcctIndicator_BEMOT],

// [KNUMA_AG] as [Sales deal_KNUMA_AG],

// [VKBUR] as [Sales office_VKBUR],

// [VKGRP] as [Sales group_VKGRP],

// [FKBER] as [Functional Area_FKBER],

// [FONDS] as [Fund_FONDS],

// [GRANT_NBR] as [Grant_GRANT_NBR],

// [AUBEL] as [Sales Document_AUBEL],

// [AUPOS] as [Item_AUPOS],

// [WKREG] as [Region-dlv.plnt_WKREG],

// [VGBEL] as [Reference doc._VGBEL],

// [VGBEL_EX] as [Reference doc._VGBEL_EX],

// [VGPOS] as [Reference item_VGPOS],

// [VGPOS_EX] as [Reference item_VGPOS_EX],

// [TAXM4] as [TaxClass4-Matl._TAXM4],

// [J_1ATXREL] as [Tax rel. class._J_1ATXREL],

// [VSTEL] as [Shipping Point_VSTEL],

// [ERDAT] as [Created on_ERDAT],

// [ERNAM] as [Created by_ERNAM],

// [AUFNR] as [Order_AUFNR],

// [PAOBJNR] as [Profit. segment_PAOBJNR],

// [PS_PSP_PNR] as [WBS Element_PS_PSP_PNR],

// [LOGSYS] as [Logical System_LOGSYS],

// [BWTAR] as [Valuation Type_BWTAR],

// [FISTL] as [Funds Center_FISTL],

// [MEINS] as [Base Unit_MEINS],

// [SGTXT] as [Text_SGTXT],

// [KURSK_DAT] as [Translation dte_KURSK_DAT],

// [VERTN] as [Contract Number_VERTN],

// [CHARG] as [Batch_CHARG],

// [ERZET] as [Time_ERZET],

// [LGORT] as [StorageLocation_LGORT],

// [SERNR] as [BOM expl.number_SERNR],

// [FPLNR] as [Billing plan no_FPLNR],

// [VERTT] as [Contract Type_VERTT],

// [AUTYP] as [Document cat._AUTYP],

// [AKTNR] as [Promotion_AKTNR],

// [ARKTX] as [Description_ARKTX],

// [BRGEW] as [Gross weight_BRGEW],

// [GEWEI] as [Weight Unit_GEWEI],

// [VOLEH] as [Volume unit_VOLEH],

// [VOLUM] as [Volume_VOLUM],

// [VBELN] as [Billing Doc._VBELN],

// [NTGEW] as [Net weight_NTGEW],

// [PRODH] as [Prod.hierarchy_PRODH],

// [WMINR] as [Catalog_WMINR],

// [J_1ADTYP] as [Distr. Type_J_1ADTYP],

// [J_1AGICD] as [Activity Code_J_1AGICD],

// [KTGRM] as [Acct asgnmt grp_KTGRM],

// [AUGRU_AUFT] as [Order reason_AUGRU_AUFT],

// [MVGR1] as [MaterialGroup 1_MVGR1],

// [KNUMA_PI] as [Promotion_KNUMA_PI],

// [BONUS] as [Vol. rebate grp_BONUS],

// [FPLTR] as [Item_FPLTR],

// [LMENG] as [Required qty_LMENG],

// [NETWR] as [Net value_NETWR],

// [CUOBJ_CH] as [Int.object no._CUOBJ_CH],

// [FBUDA] as [Serv.rendered_FBUDA],

// [FKIMG] as [Billed qty_FKIMG],

// [FKLMG] as [Bill.qty in SKU_FKLMG],

// [PROVG] as [Commission grp_PROVG],

// [PRSDT] as [Pricing date_PRSDT],

// [KVGR1] as [Customer grp 1_KVGR1],

// [KVGR2] as [Customer grp 2_KVGR2],

// [KVGR3] as [Customer grp 3_KVGR3],

// [KVGR4] as [Customer grp 4_KVGR4],

// [KVGR5] as [Customer grp 5_KVGR5],

// [MVGR2] as [MaterialGroup 2_MVGR2],

// [MVGR3] as [MaterialGroup 3_MVGR3],

// [MVGR4] as [MaterialGroup 4_MVGR4],

// [MVGR5] as [MaterialGroup 5_MVGR5],

// [KURSK] as [Exchange Rate_KURSK],

// [UMVKN] as [Denominatr_UMVKN],

// [UMVKZ] as [Numerator_UMVKZ],

// [VGTYP] as [Prec.doc.categ._VGTYP],

// [BONBA] as [Rebate basis_BONBA],

// [BRTWR] as [Gross value_BRTWR],

// [SKFBP] as [Csh.disc.bas_SKFBP],

// [KOWRR] as [Statist.value_KOWRR],

// [KZWI1] as [Subtotal 1_KZWI1],

// [KZWI2] as [Subtotal 2_KZWI2],

// [KZWI3] as [Subtotal 3_KZWI3],

// [KZWI4] as [Subtotal 4_KZWI4],

// [KZWI5] as [Subtotal 5_KZWI5],

// [KZWI6] as [Subtotal 6_KZWI6],

// [MWSBP] as [Tax amount_MWSBP],

// [SKTOF] as [Cash discount_SKTOF],

// [STAFO] as [Update group_STAFO],

// [STCUR] as [Exch.rate stats_STCUR],

// [UEPOS] as [Higher-lev.item_UEPOS],

// [XCHAR] as [Batches_XCHAR],

// [PSTYV] as [Item category_PSTYV],

// [POSAR] as [Item type_POSAR],

// [UECHA] as [HghLevItmBatch_UECHA],

// [J_1AINDXP] as [Inflation index_J_1AINDXP],

// [MATWA] as [MaterialEntered_MATWA],

// [ATPKZ] as [Replacemt part_ATPKZ],

// [AUREF] as [SalesDocumentRefer_AUREF],

// [CMPNT] as [Credit active_CMPNT],

// [CMPRE] as [Credit price_CMPRE],

// [CUOBJ] as [Configuration_CUOBJ],

// [KOUPD] as [Condit.update_KOUPD],

// [PMATN] as [Pr. ref. matl_PMATN],

// [POSPA] as [Partner item_POSPA],

// [PRSFD] as [Pricing_PRSFD],

// [SHKZG] as [Returns_SHKZG],

// [SMENG] as [Scale quantity_SMENG],

// [SPARA] as [Division_SPARA],

// [TAXM5] as [TaxClass5-Matl._TAXM5],

// [TAXM6] as [TaxClass6-Matl._TAXM6],

// [TAXM7] as [TaxClass7-Matl._TAXM7],

// [TAXM8] as [TaxClass8-Matl._TAXM8],

// [TAXM9] as [TaxClass9-Matl._TAXM9],

// [UVALL] as [General_UVALL],

// [UVPRS] as [Pricing_UVPRS],

// [WAVWR] as [Cost_WAVWR],

// [WKCOU] as [County-dlv.plnt_WKCOU],

// [WKCTY] as [City-dlv.plant_WKCTY],

// [ABRVW] as [Use_ABRVW],

// [VKAUS] as [Use_VKAUS],

// [CMPRE_FLT] as [Credit price_CMPRE_FLT],

// [FAREG] as [Billing rule_FAREG],

// [J_1ARFZ] as [Reas.0 VAT_J_1ARFZ],

// [J_1BCFOP] as [CFOP_J_1BCFOP],

// [J_1BTAXLW1] as [ICMS law_J_1BTAXLW1],

// [J_1BTAXLW2] as [IPI law_J_1BTAXLW2],

// [J_1BTXSDC] as [Tax code_J_1BTXSDC],

// [KDKG1] as [Condition grp 1_KDKG1],

// [KDKG2] as [Condition grp 2_KDKG2],

// [KDKG3] as [Condition grp 3_KDKG3],

// [KDKG4] as [Condition grp 4_KDKG4],

// [KDKG5] as [Condition grp 5_KDKG5],

// [ABRBG] as [AcctSettleStart_ABRBG],

// [POSNV] as [Item_POSNV],

// [VBELV] as [Originating doc_VBELV],

// [PREFE] as [Preference_PREFE],

// [AKKUR] as [LettOfCredRate_AKKUR],

// [ABFOR] as [PaytGuarantForm_ABFOR],

// [ABGES] as [Guaranteed_ABGES],

// [KZFME] as [Leading UoM_KZFME],

// [PROSA] as [MatDetermActive_PROSA],

// [STADAT] as [Statistics date_STADAT],

// [UEPVW] as [Usage HL item_UEPVW],

// [WKTNR] as [Value contract no._WKTNR],

// [WKTPS] as [Val. cont.item_WKTPS],

// [DELCO] as [Delivery time_DELCO],

// [J_1AIDATEP] as [Index base date_J_1AIDATEP],

// [RPLNR] as [Paym.ca.pl.no._RPLNR],

// [RRREL] as [Rev. recognition_RRREL],

// [MPROK] as [Manual price_MPROK],

// [WGRU1] as [MatGroup 1_WGRU1],

// [WGRU2] as [MatGroup 2_WGRU2],

// [BZIRK_AUFT] as [SlsDist.Order_BZIRK_AUFT],

// [KDGRP_AUFT] as [CustGrpSlsOrder_KDGRP_AUFT],

// [KONDA_AUFT] as [PriceGroupOrder_KONDA_AUFT],

// [LLAND_AUFT] as [DestCtryOrder_LLAND_AUFT],

// [PLTYP_AUFT] as [Price list ord._PLTYP_AUFT],

// [REGIO_AUFT] as [Region order_REGIO_AUFT],

// [VGTYP_EX] as [Category_VGTYP_EX],

// [VTWEG_AUFT] as [DistChanOrder_VTWEG_AUFT],

// [YYCALLID] as [Call Center_YYCALLID]

;

//SQL Select * FROM VBRP WHERE ERDAT > '$(vYear)'

SQL Select * FROM VBRP WHERE VBELN = '0097450292'

;

//Store Data to QVD File:VBRP.qvd

store VBRP into qvd\VBRP_BillingDocument_Item$(vQVD_version).qvd;

//Drop table

drop table VBRP;

[VBPA]:

Load

// [VBELN] as [BillingDocument_Key],

[VBELN] & '/' & [POSNR] as [BillingDocumentItem_Key],

[POSNR] as [POSNR],

[KUNNR] as [Customer],

[PARVW] as [Partner],

[LIFNR] as [Vendor],

[PERNR] as [Sales Associate],

[KNREF] as [Partner Desc],

[PARNR] as [Contact Person]

;

SQL Select VBELN POSNR KUNNR PARVW LIFNR PERNR KNREF PARNR from VBPA

WHERE VBELN = '0097450292'

;

//Store Data to QVD File:VBPA.qvd

store VBPA into qvd\VBPA_Partners$(vQVD_version).qvd;

//Drop table

drop table VBPA;

topic SAP Tables: VBAK / VBAP / VBPA in QlikView

SAP Tables: VBAK / VBAP / VBPA

SAP Tables: VBAK / VBAP / VBPA

SAP Tables: VBAK / VBAP / VBPA

SAP Tables: VBAK / VBAP / VBPA

SAP Tables: VBAK / VBAP / VBPA