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;
