Hello All:
I am have some problems doing my left join. I have 2 tables from SAP (VBAK (Sales Order Header), and VBAP(Sales Order Line Detail). I am trying to do a left join on the document number. It keeps giving me errors saying "VBAP_Temp table not found". Thanks in advance. Here is my code:
//**********************************************************************************
// Table: VBAK - Sales Order Document Header
//**********************************************************************************
[VBAK]: // Sales Document: Header Data
Load
[VBELN] as [%SalesOrderDocument_Key],
[VKORG] as [Sales Organization_VKORG],
[VTWEG] as [Distribution Channel_VTWEG],
[SPART] as [Division_SPART],
[VBTYP] as [SD document categ._VBTYP],
[AUART] as [Sales Document Type_AUART],
[KURST] as [Exchange Rate Type_KURST],
[VBELN] as [Sales Document_VBELN],
[KUNNR] as [Sold-to party_KUNNR],
[VKBUR] as [Sales Office_VKBUR],
[VKGRP] as [Sales Group_VKGRP],
[AUFNR] as [Order_AUFNR],
[AUGRU] as [Order reason_AUGRU],
[WAERK] as [Document Currency_WAERK],
[GUEBG] as [Valid-from date_GUEBG],
[GUEEN] as [Valid-to date_GUEEN],
[STWAE] as [Statistics currency_STWAE],
[KVGR1] as [Customer group 1_KVGR1],
[KVGR2] as [Customer group 2_KVGR2],
[KVGR3] as [Customer group 3_KVGR3],
[KVGR4] as [Customer group 4_KVGR4],
[KVGR5] as [Customer group 5_KVGR5];
// [KOSTL] as [Cost Center_KOSTL],
// [NETWR] as [Net value_NETWR],
// [VGBEL] as [Reference document_VGBEL],
// [ERNAM] as [Created by_ERNAM],
// [ERZET] as [Time_ERZET],
// [ERDAT] as [Created on_ERDAT],
// [AEDAT] as [Changed on_AEDAT],
// [KNUMA] as [Agreement_KNUMA],
// [BSTNK] as [Purchase order no._BSTNK],
// [BSTDK] as [Purchase order date_BSTDK],
// [STAFO] as [Update group (stats)_STAFO],
// [ANGDT] as [Quotation valid from_ANGDT],
// [BNDDT] as [Quotation valid to_BNDDT],
// [AUDAT] as [Document Date_AUDAT],
// [TRVOG] as [Transaction group_TRVOG],
// [GWLDT] as [Guarantee_GWLDT],
// [SUBMI] as [Collective number_SUBMI],
// [LIFSK] as [Delivery block_LIFSK],
// [FAKSK] as [Billing block_FAKSK],
// [GSBER] as [Business Area_GSBER],
// [GSKST] as [Business area_GSKST],
// [KNUMV] as [Doc. condition no._KNUMV],
// [VDATU] as [Requested deliv.date_VDATU],
// [VPRGR] as [Prop.date type_VPRGR],
// [AUTLF] as [Complete delivery_AUTLF],
// [VBKLA] as [Original system_VBKLA],
// [VBKLT] as [Indicator_VBKLT],
// [KALSM] as [Pricing procedure_KALSM],
// [VSBED] as [Shipping Conditions_VSBED],
// [FKARA] as [Order-rel.bill.type_FKARA],
// [AWAHR] as [Probability_AWAHR],
// [KTEXT] as [Description_KTEXT],
// [BSARK] as [Purchase order type_BSARK],
// [BSTZD] as [Supplement_BSTZD],
// [IHREZ] as [Your Reference_IHREZ],
// [BNAME] as [Name_BNAME],
// [TELF1] as [Telephone_TELF1],
// [MAHZA] as [Number of contacts_MAHZA],
// [MAHDT] as [Last contact date_MAHDT],
// [KOKRS] as [Controlling Area_KOKRS],
// [PS_PSP_PNR] as [WBS Element_PS_PSP_PNR],
// [KKBER] as [Credit control area_KKBER],
// [KNKLI] as [Credit account_KNKLI],
// [GRUPP] as [Cust.cred.group_GRUPP],
// [SBGRP] as [Credit rep.group_SBGRP],
// [CTLPC] as [Risk category_CTLPC],
// [CMWAE] as [Currency_CMWAE],
// [CMFRE] as [Release date_CMFRE],
// [CMNUP] as [Next check_CMNUP],
// [CMNGV] as [Next date_CMNGV],
// [AMTBL] as [Rel. credit value_AMTBL],
// [HITYP_PR] as [HierarchyTypePricing_HITYP_PR],
// [ABRVW] as [Usage_ABRVW],
// [ABDIS] as [MRP for DlvSchType_ABDIS],
// [OBJNR] as [Object no.header_OBJNR],
// [BUKRS_VF] as [CCode to be billed_BUKRS_VF],
// [TAXK1] as [Alt.tax classific._TAXK1],
// [TAXK2] as [TaxClass2-Cust._TAXK2],
// [TAXK3] as [TaxClass3-Cust._TAXK3],
// [TAXK4] as [TaxClass4-Cust._TAXK4],
// [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],
// [XBLNR] as [Reference_XBLNR],
// [ZUONR] as [Assignment_ZUONR],
// [VGTYP] as [Preceding doc.categ._VGTYP],
// [KALSM_CH] as [Search procedure_KALSM_CH],
// [AGRZR] as [Accrual period_AGRZR],
// [QMNUM] as [Notification_QMNUM],
// [VBELN_GRP] as [Master contract_VBELN_GRP],
// [SCHEME_GRP] as [Group Ref. Procedure_SCHEME_GRP],
// [ABRUF_PART] as [Check partner auth._ABRUF_PART],
// [ABHOD] as [Pick up date_ABHOD],
// [ABHOV] as [Pick up time_ABHOV],
// [ABHOB] as [Pick up time_ABHOB],
// [RPLNR] as [Paym.card plan no._RPLNR],
// [VZEIT] as [Requested dely time_VZEIT],
// [STCEG_L] as [Tax dest. country_STCEG_L],
// [LANDTX] as [Tax depart. country_LANDTX],
// [XEGDR] as [EU triangular deal_XEGDR],
// [ENQUEUE_GRP] as [_ENQUEUE_GRP],
// [DAT_FZAU] as [CmlQtyDate_DAT_FZAU],
// [FMBDAT] as [Material Avail. Date_FMBDAT],
// [VSNMR_V] as [Version_VSNMR_V],
// [CONT_DG] as [Contains DG_CONT_DG],
// [PROLI] as [DG mgmt profile_PROLI],
// [MTLAUR] as [Target Incom_MTLAUR],
// [PHASE] as [Sales Phase_PHASE],
// [HANDLE] as [Int.ID_HANDLE],
// [KALCD] as [Proc. Camp.Determin._KALCD],
// [LOGSYSB] as [Logical system_LOGSYSB],
// [CRM_GUID] as [Char 70_CRM_GUID],
// [SWENR] as [Business entity_SWENR],
// [SMENR] as [Unit Number_SMENR],
// [STAGE] as [Construction stage_STAGE],
// [HB_CONT_REASON] as [Contingency reason_HB_CONT_REASON],
// [HB_EXPDATE] as [Expiration date_HB_EXPDATE],
// [HB_RESDATE] as [Resolution date_HB_RESDATE],
// [MULTI] as [Multiple Promotions_MULTI],
// [ZZDT_DOORHANG] as [Date of Door Hanger_ZZDT_DOORHANG],
// [ZZDT_POSTCARD] as [Date of Post Card Mailing_ZZDT_POSTCARD],
// [ZZDT_MAILED] as [Date Mailed_ZZDT_MAILED],
// [ZZDT_FOLLOWUP] as [Follow-Up Mailing Date_ZZDT_FOLLOWUP];
SQL Select * FROM VBAK where GUEBG >= '$(vYear)' and AUART = '$(vDocument)';
store VBAK into qvd\VBAK.qvd;
//**********************************************************************************
// Table: VBAP - Sales Order Line Item Detail
//**********************************************************************************
[VBAPTEMP]:
SQL Select *
FROM VBAP
WHERE VBELN in
(Select VBELN from VBAK where GUEBG >= '$(vYear)' and AUART = '$(vDocument)');
[VBAP_Temp]: // Sales Document: Item Data
Load
[VBELN] as [%SalesOrderDocument_Key],
[VBELN] & '/' & [POSNR] as [%SalesOrderDocumentItem_Key],
[MATNR] as [Material_MATNR],
[WERKS] as [Plant_WERKS],
[NETWR] as [Net value_NETWR],
[GSBER] as [Business Area_GSBER],
[KOSTL] as [Cost Center_KOSTL],
[POSNR] as [Sales Document Item_POSNR],
[POSAR] as [Item type_POSAR],
[ABGRU] as [Reason for rejection_ABGRU],
[KWMENG] as [Order Quantity_KWMENG],
[PRCTR] as [Profit Center_PRCTR],
[NETPR] as [Net price_NETPR],
[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],
[STCUR] as [Exchange rate stats._STCUR]
// [VBELN] as [Sales Document_VBELN],
// [WAERK] as [Document Currency_WAERK],
// [SPART] as [Division_SPART],
// [KPEIN] as [Pricing unit_KPEIN],
// [KMEIN] as [Unit of measure_KMEIN],
// [ERNAM] as [Created by_ERNAM],
// [ERZET] as [Time_ERZET],
// [ERDAT] as [Created on_ERDAT],
// [AEDAT] as [Changed on_AEDAT],
// [BRGEW] as [Gross weight_BRGEW],
// [NTGEW] as [Net weight_NTGEW],
// [LGORT] as [Storage Location_LGORT],
// [MEINS] as [Base Unit of Measure_MEINS],
// [VRKME] as [Sales unit_VRKME],
// [UMVKZ] as [Numerator_UMVKZ],
// [UMVKN] as [Denominator_UMVKN],
// [VBELV] as [Originating document_VBELV],
// [POSNV] as [Item_POSNV],
// [VGBEL] as [Reference document_VGBEL],
// [VGPOS] as [Reference item_VGPOS],
// [PSTYV] as [Item category_PSTYV],
// [VSTEL] as [Shipping Point/Receiving Pt_VSTEL],
// [AUFNR] as [Order_AUFNR],
// [STAFO] as [Update group (stats)_STAFO],
// [KNUMA_PI] as [Promotion_KNUMA_PI],
// [KNUMA_AG] as [Sales deal_KNUMA_AG],
// [PS_PSP_PNR] as [WBS Element_PS_PSP_PNR],
// [VGTYP] as [Preceding doc.categ._VGTYP],
// [MATWA] as [Material entered_MATWA],
// [PMATN] as [Pricing Ref. Matl_PMATN],
// [CHARG] as [Batch_CHARG],
// [MATKL] as [Material Group_MATKL],
// [ARKTX] as [Description_ARKTX],
// [LFREL] as [Itm relev.for deliv._LFREL],
// [FKREL] as [Relevant for Billing_FKREL],
// [UEPOS] as [Higher-level item_UEPOS],
// [GRPOS] as [Alternative to item_GRPOS],
// [PRODH] as [Product hierarchy_PRODH],
// [ZWERT] as [OA Target Value_ZWERT],
// [ZMENG] as [Target quantity_ZMENG],
// [ZIEME] as [Target quantity UoM_ZIEME],
// [UMZIZ] as [Conversion factor_UMZIZ],
// [UMZIN] as [Conversion factor_UMZIN],
// [SMENG] as [Scale quantity_SMENG],
// [ABLFZ] as [Rounding qty_ABLFZ],
// [ABDAT] as [Reconciliation Date_ABDAT],
// [ABSFZ] as [Absolute deviation_ABSFZ],
// [POSEX] as [Purchase order item_POSEX],
// [KDMAT] as [Customer Material_KDMAT],
// [KBVER] as [Deviation in percent_KBVER],
// [KEVER] as [Deviation days_KEVER],
// [VKGRU] as [Repair: Classification of Items_VKGRU],
// [VKAUS] as [Usage_VKAUS],
// [GRKOR] as [Delivery group_GRKOR],
// [FMENG] as [Quantity is Fixed_FMENG],
// [UEBTK] as [Unlimited tolerance_UEBTK],
// [UEBTO] as [Overdeliv. Tolerance_UEBTO],
// [UNTTO] as [Underdel. Tolerance_UNTTO],
// [FAKSP] as [Billing block_FAKSP],
// [ATPKZ] as [Replacement part_ATPKZ],
// [RKFKF] as [Billing meth. CO/PPC_RKFKF],
// [ANTLF] as [Max.Part.Deliveries_ANTLF],
// [KZTLF] as [Part.dlv./item_KZTLF],
// [CHSPL] as [Batch split allowed_CHSPL],
// [LSMENG] as [Required deliv. qty_LSMENG],
// [KBMENG] as [Cumul.confirmed qty_KBMENG],
// [KLMENG] as [Cumul.confirmed qty_KLMENG],
// [GEWEI] as [Weight unit_GEWEI],
// [VOLUM] as [Volume_VOLUM],
// [VOLEH] as [Volume unit_VOLEH],
// [VOREF] as [Complete reference_VOREF],
// [UPFLU] as [Update document flow_UPFLU],
// [ERLRE] as [Completion rule_ERLRE],
// [LPRIO] as [Delivery Priority_LPRIO],
// [ROUTE] as [Route_ROUTE],
// [STKEY] as [BOM origin_STKEY],
// [STDAT] as [BOM key date_STDAT],
// [STLNR] as [Bill of material_STLNR],
// [STPOS] as [Bill of material item number_STPOS],
// [AWAHR] as [Order probability_AWAHR],
// [TAXM1] as [Tax classification_TAXM1],
// [TAXM2] as [Tax classification_TAXM2],
// [TAXM3] as [Tax classification_TAXM3],
// [TAXM4] as [Tax classification_TAXM4],
// [TAXM5] as [Tax classification_TAXM5],
// [TAXM6] as [Tax classification_TAXM6],
// [TAXM7] as [Tax classification_TAXM7],
// [TAXM8] as [Tax classification_TAXM8],
// [TAXM9] as [Tax classification_TAXM9],
// [VBEAF] as [Fixed process. time_VBEAF],
// [VBEAV] as [Var. processing time_VBEAV],
// [VGREF] as [Preceding document resulted from ref._VGREF],
// [SHKZG] as [Returns_SHKZG],
// [SKTOF] as [Cash discount_SKTOF],
// [MTVFP] as [Availability check_MTVFP],
// [SUMBD] as [Requirements totals_SUMBD],
// [KONDM] as [Material pricing grp_KONDM],
// [KTGRM] as [Acct assignment grp_KTGRM],
// [BONUS] as [Volume rebate group_BONUS],
// [PROVG] as [Commission group_PROVG],
// [EANNR] as [EAN number_EANNR],
// [PRSOK] as [Pricing_PRSOK],
// [BWTAR] as [Valuation Type_BWTAR],
// [BWTEX] as [Separate valuation_BWTEX],
// [XCHPF] as [Batch management_XCHPF],
// [XCHAR] as [Batch management_XCHAR],
// [LFMNG] as [Minimum delivery qty_LFMNG],
// [WAVWR] as [Cost_WAVWR],
// [EAN11] as [EAN/UPC_EAN11],
// [FIXMG] as [Fixed date and qty_FIXMG],
// [MVGR1] as [Material group 1_MVGR1],
// [MVGR2] as [Material group 2_MVGR2],
// [MVGR3] as [Material group 3_MVGR3],
// [MVGR4] as [Material group 4_MVGR4],
// [MVGR5] as [Material group 5_MVGR5],
// [KMPMG] as [Component quantity_KMPMG],
// [SUGRD] as [Sustitution Reason_SUGRD],
// [SOBKZ] as [Special Stock_SOBKZ],
// [VPZUO] as [Allocation indicat._VPZUO],
// [PAOBJNR] as [Profitab. Segmt No._PAOBJNR],
// [VPMAT] as [Planning material_VPMAT],
// [VPWRK] as [Planning plant_VPWRK],
// [PRBME] as [Product group unit_PRBME],
// [UMREF] as [Cnversion_UMREF],
// [KNTTP] as [Acct Assignment Cat._KNTTP],
// [KZVBR] as [Consumption_KZVBR],
// [SERNR] as [BOM explosion number_SERNR],
// [OBJNR] as [Object no.Item_OBJNR],
// [ABGRS] as [Results Analysis Key_ABGRS],
// [BEDAE] as [Requirements type_BEDAE],
// [CMPRE] as [Credit price_CMPRE],
// [CMTFG] as [Partial release_CMTFG],
// [CMPNT] as [Credit funct. active_CMPNT],
// [CMKUA] as [Cred.data exch.rate_CMKUA],
// [CUOBJ] as [Configuration_CUOBJ],
// [CUOBJ_CH] as [Internal object no._CUOBJ_CH],
// [CEPOK] as [Expected price_CEPOK],
// [KOUPD] as [Condition update_KOUPD],
// [SERAIL] as [Serial no. profile_SERAIL],
// [ANZSN] as [No.serial numbers_ANZSN],
// [NACHL] as [No GR posted by cst._NACHL],
// [MAGRV] as [Matl Grp Pack.Matls_MAGRV],
// [MPROK] as [Manual price_MPROK],
// [PROSA] as [Mat.determ.active_PROSA],
// [UEPVW] as [Usage of HL item_UEPVW],
// [KALNR] as [Cost Estimate Number_KALNR],
// [KLVAR] as [Costing Variant_KLVAR],
// [SPOSN] as [Item Number_SPOSN],
// [KOWRR] as [Statistical value_KOWRR],
// [STADAT] as [Statistics date_STADAT],
// [EXART] as [Bus.transaction type_EXART],
// [PREFE] as [Preference auth._PREFE],
// [KNUMH] as [No.cond.rec. batch_KNUMH],
// [CLINT] as [Internal class no._CLINT],
// [CHMVS] as [Quantity proposal_CHMVS],
// [STLTY] as [BOM category_STLTY],
// [STLKN] as [Item node_STLKN],
// [STPOZ] as [Counter_STPOZ],
// [STMAN] as [Inconsistent confign_STMAN],
// [ZSCHL_K] as [Overhead key_ZSCHL_K],
// [KALSM_K] as [Costing Sheet_KALSM_K],
// [KALVAR] as [Costing Variant_KALVAR],
// [KOSCH] as [Product allocation_KOSCH],
// [UPMAT] as [MnItm PrcRefMatl_UPMAT],
// [UKONM] as [MnItem MatPricingGrp_UKONM],
// [MFRGR] as [Material freight grp_MFRGR],
// [PLAVO] as [PlanDelSchedInstruct_PLAVO],
// [KANNR] as [Sequence number_KANNR],
// [CMPRE_FLT] as [Credit price_CMPRE_FLT],
// [ABFOR] as [Paymt guarantee form_ABFOR],
// [ABGES] as [Guaranteed_ABGES],
// [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],
// [WKTNR] as [Value contract no._WKTNR],
// [WKTPS] as [Value contract item_WKTPS],
// [SKOPF] as [Module_SKOPF],
// [KZBWS] as [Spec. Stk Valuation_KZBWS],
// [WGRU1] as [Material group 1_WGRU1],
// [WGRU2] as [Material group 2_WGRU2],
// [KZFME] as [Leading UoM_KZFME],
// [LSTANR] as [Free goods delivery control_LSTANR],
// [TECHS] as [Standard Variant_TECHS],
// [MWSBP] as [Tax amount_MWSBP],
// [BERID] as [MRP Area_BERID],
// [FISTL] as [Funds Center_FISTL],
// [FKBER] as [Functional Area_FKBER],
// [GRANT_NBR] as [Grant_GRANT_NBR],
// [FERC_IND] as [Regulatory indicator_FERC_IND],
// [FONDS] as [Fund_FONDS],
// [J_1BTAXLW4] as [COFINS Law_J_1BTAXLW4],
// [J_1BTAXLW5] as [PIS Law_J_1BTAXLW5],
// [J_1BTAXLW3] as [ISS Law_J_1BTAXLW3],
// [PCTRF] as [Profit Center Billing_PCTRF],
// [LOGSYS_EXT] as [Logical system_LOGSYS_EXT]
RESIDENT VBAPTEMP
WHERE exists([%SalesOrderDocument_Key], [VBELN]);
[VBAP_Temp]:
Left Join (VBAP_Temp)
LOAD [%SalesOrderDocument_Key]
RESIDENT VBAK;
//DROP TABLE [VBAK];
//DROP TABLE VBAPTEMP, VBAP_Temp;
