Home Explore Help
Register Sign In
GlobalCube
/
C11
3
0
Fork 0
Files Issues 0 Pull Requests 0 Wiki
Tree: f6fedc58f3
Branches Tags
C11
/
System
/
OPTIMA
/
SQL
/
batch
/
ORDER_LINE.bat

ORDER_LINE.bat 4.9 KB
History Raw

123456789 
  1. @echo off 
    2. 

  2. rem ==ORDER_LINE==
    3. 

  3. del C:\GlobalCube\System\OPTIMA\SQL\temp\OPTIMA\ORDER_LINE*.* /Q /F >nul 2>nul 
    4. 

  4. sqlcmd.exe -S localhost\GLOBALCUBE -d OPTIMA -U sa -P Mffu3011# -p -Q "TRUNCATE TABLE [import].[ORDER_LINE]" 
    5. 

  5. rem Nur in Quelle: SPLIT_AM_SUB2;DEPOSIT_TYPE;ORDER_LINETYPE_4;STOCK;REPAIR_POS;DISCOUNT_CODE;REQUISITION_NUMBER;LINE_STATUS;WORKSHOP_AREA;SALES_UNIT_ALT;BACK_REG_CODE;MISC_5;KIT_DELETE;PURCH_ORDER_NUMBER;END_TIME;POLICY_ORDER_LINE;PERSON_CODE;QTY_SOLD_NEGATIVE;INV_TOTAL_NUMBER;PURCH_LINE_NUMBER;SPEC_PURCH_PRICE;KIT_MISC_DISC_PER;JOB_CODE;FLAT_SEARCH_6;TAX_C_U;REPEAT_REPAIR;LOCATION;KIT_FIXED_PRICE;STATUS;FLAT_SEARCH_2;KIT_PRINT_SPLIT;TAX_DRAWN;PROMOTION_NUMBER;EXCHANGE_PART;INSPECTION_1;CCC_10_CHILD_2;USED_TIME_PAY_REPAIR;STATISTIC_CODE;STOCK_SHOP_LIST;LINE_COMMENT;KIT_WORK_DISC_PER;FLAT_SEARCH_8;RESULTING_LINE;CREATE_MSC;CLAIM_NUMBER;ECA_CONTRACT_CODE;ORIG_PROD_CODE;DISCOUNT_PERC;VAT_RATE_CODE;DEDUCTABLE;BUYERS_REFERENCE;BATTERY_SERIAL_NO;MENU_TYPE;KIT_PARTS_DISC_PER;LINE_WORKSHOP;CCC_10_CHILD_1;CLAIM_TROUBLE_CD;ORIG_COST_PRICE;PRE_PLANNED_ORDER;PART_GROUP;EXCHANGE_RETURN_CODE;TEXT_200;JOB_CODE_DESCR;INSPECTION_2;MISC_3;FLAT_SEARCH_5;SORT_LINE_NUMBER;START_TIME;TXT_OL;RE_PURCHASE_PRICE;ACCUMULATED_MENU_CHANGES;CCC_10_CHILD_3;PRINT_INVOICE;KIT_GROUP;CCC;RESULTING_LINE_1;CAMPAIGN_CODE;OIL;BATTERY_EXCHANGE;USED_TIME_TYPE_2;TIME_CODE;FLAT_SEARCH_9;REPAIR_LOCATION_CODE;SPLIT_AM_SUB3;CLAIM_CODE_IND;EXCLUDED_FROM_GAIN_CALC;RECOMMENDED_PRICE;INTERNAL_TRANSFER;LINE_TEXTLINE_2;ORIG_LINE_NO;GROSS_DISCOUNT;KIT_TEXT;FUNCTION_CODE;MISC_1;EXTERNAL_ORIGIN;PROMOTION_DISCOUNT_PCT;CCC_10_CHILD_5;KIT_ONE_LINEPRICE;INV_TIME_COST;END_DATE;CCC_10_CHILD_4;CCC_CATEGORY;FLAT_SEARCH_4;W_MAIN_REPAIR_NO;CCC_2;MARKETING_CAMPAIGN_ID;HANDLER;FIXED_PRICE_REPAIR;STATE_CODE;QUANTITY_DISC_CODE;LINE_TEXTLINE_1;TIME_CODE_2;PURCH_ORDER_STATUS;REPAIR_GROUP_TYPE;CASE_NO;REPAIR_CODE_2;DISCOUNT_ALLOWANCE;BACK_ORDER_TEXT_LINE;KIT_CATALOG_CODE;PURCH_ORDER_SUPPLIER_CODE;INCOMPLETE_LINE_2;SMALL_ACCESSORIES;KIT_CODE;REDUCTION_CODE_2;N_MAIN_PART_NO;EXTERNAL_STOCK;RESULTING_LINE_2;USED_TIME_TYPE_1;CUSTOMER_COMPLAINT;COST_PRICE_WORK;SUPPLIER_CODE;EXTERNAL_PICK_IN_PROGRESS;TYPE_OL;DELIVERY_DATE;SPECIAL_ORDER_ITEM;TAX_CODE_1;CCC_10_PARENT;WORK_TYPE_CODE;SPLIT_AM_MAIN;TRANSACT_DATE;TIME_RATE;LINE_CODE;KEY_PROD_CODE;CONV_FLAG;INCOMPLETE_LINE;LINE_TEXT_CODE;MISC_2;ALTERNATIVE_PRICE;DISCOUNT_PERC_2;COST_PRICE_TYPE_2;QTY_OWNERSHIP;ORDER_QUANTITY_ALT_UNIT;SALES_PRICE_INCL;CLASS_RULE;WORKSHOP_MODEL;MISC_6;ADD_COST_CODE;PRICE_A_G;PROGRAM;ORIG_ORDER_NO;FACTOR_CODE;MARKETING_RESPONSE_CODE;REDUCTION_POS;DEDUCTIBLE_VAT;ORDER_LINETYPE_2;ORDER_LINE_TRANSM;WUST_CODE;KEY_MAKE_CD;REPLACE_CODE;CLAIM_TROUBLE_CD_OLD;SALES_PRICE;ORIG_MAKE_CD;SPLIT_TYPE;REPAIR_CODE_ACCU;ORIG_PROD_GROUP;PICKING_LIST_NO;FLAT_SEARCH_7;REQUESTED_QUANTITY;BACK_ORDER;FLAT_SEARCH_3;AUTOMATIC_CREATED;ECA_SERVICE_CODE;COST_PRICE_TYPE_1;LINE_MISC_ADDS_PER;SPLIT_AM_SUB1;SPECIAL_PRICE;PAYER_PERCENTAGE;PICKED;CASH_ACCOUNT;R_DELIVERY_DATE;CLAIM_RETURN_CODE;SALES_UNIT_STD;DISCOUNT_TABLE;SPLIT_CODE_2;LOCATION_2;BACK_ORDER_CD;ORDER_LINETYPE_3;KIT_ONE_LINE;CONSIGNMENT_CODE;COSTPRICE_TAX;RATE_PRICE_CODE;KIT_FIXED_PRICE_CD;ORIG_CREATION_CODE;SPECIAL_LOCK_NO;TIME_CODE_1;WORKSHOP_TEAM;STDPRICE_2;LINE_MISC_ADDS;WORK_DATE;KIT_PRICE_DIFF;LINE_REFERENCE;LINE_GROUP_CODE;SPLIT_CODE;REQUISITION_NUM;MISC_4;LINE_TEXTLINE_3;DECIMAL_INDICATOR;TRANSACTION_CODE_2;AMOUNT_OL;CREATE_AFTER_RC;LOCATION_3;REDUCTION_FACTOR_1
    6. 

  6. bcp "SELECT T1.[ORDER_NUMBER], T1.[LINE_NUMBER], T1.[ORDER_LINETYPE], T1.[REDUCTION_CODE], T1.[REDUCTION_AMOUNT], T1.[MECHANIC_CODE], T1.[SALESMAN], T1.[DISCOUNT], T1.[STDPRICE], T1.[LINES_NET_VALUE], T1.[PROD_CODE], T1.[MAKE_CD], T1.[PRODUCT_GROUP], T1.[PROD_NAME], T1.[ORDER_QUANTITY], T1.[DELIVERY_QUANTITY], T1.[LINE_COSTS], T1.[REPAIR_CODE], T1.[REPAIR_GROUP], T1.[REPAIR_NAME], T1.[USED_TIME], T1.[EST_TIME], T1.[INV_TIME], T1.[USED_TIME_INT], T1.[EST_TIME_INT], T1.[INV_TIME_INT], T1.[MAKE_TIME_UNIT], T1.[timestamp], T1.[UNIQUE_IDENT], '1' as \"Client_DB\" FROM [dbo].[ORDER_LINE] T1 INNER JOIN [dbo].[ORDER_HEADER] T2 ON T1.[ORDER_NUMBER] = T2.[ORDER_NUMBER] WHERE T2.[INVOICE_DATE] >= '01.01.2019' OR T2.[INVOICE_DATE] = '01.01.1800'" queryout "C:\GlobalCube\System\OPTIMA\SQL\temp\OPTIMA\ORDER_LINE_1.csv" -S DADE053821 -d deop01 -U sa -P sybjdc -c -C 65001 -e "C:\GlobalCube\System\OPTIMA\SQL\temp\OPTIMA\ORDER_LINE_1.queryout.log" > "C:\GlobalCube\System\OPTIMA\SQL\temp\OPTIMA\ORDER_LINE_1.bcp1.log" 
    7. 

  7. type "C:\GlobalCube\System\OPTIMA\SQL\temp\OPTIMA\ORDER_LINE_1.bcp1.log" | findstr -v "1000" 
    8. 

  8. bcp [import].[ORDER_LINE] in "C:\GlobalCube\System\OPTIMA\SQL\temp\OPTIMA\ORDER_LINE_1.csv" -S localhost\GLOBALCUBE -d OPTIMA -U sa -P Mffu3011# -c -C 65001 -m 1000 -e "C:\GlobalCube\System\OPTIMA\SQL\temp\OPTIMA\ORDER_LINE_1.in.log" > "C:\GlobalCube\System\OPTIMA\SQL\temp\OPTIMA\ORDER_LINE_1.bcp2.log" 
    9. 

  9. type "C:\GlobalCube\System\OPTIMA\SQL\temp\OPTIMA\ORDER_LINE_1.bcp2.log" | findstr -v "1000" 
    10.