How to Export Order in CSV or XLS file in SAP Commerce Cloud/SAP Hybris Application?

 In Commerce/Hybris OOTB, support export API, to export the job via utility job.

Impex export is also one of the most common ways of doing the export of order data type into CSV, we only need to put the column name of order data type which we need to export.

Export script can be easily identified from backoffice -> scripting tool -> export button, search for Insert_Update Order clause and put in HAC to export.

1.      Go to Backoffice-> System -> Tools -> Script Generator -> Click on Generate button.

è We will get all the scripts syntax for export

1.      Copy all and choose the required syntax as we need the impex for order.

2.      Syntax:

# ---- Extension: core ---- Type: Order ----

"#% impex.setTargetFile( ""Order.csv"" );"

insert_update Order;&Item;Europe1PriceFactory_UDG(code,itemtype(code));Europe1PriceFactory_UPG(code,itemtype(code));Europe1PriceFactory_UTG(code,itemtype(code));Unit(uid);appliedCouponCodes;appliedVoucherCodes;billingTime(code);calculated;cartIdReference;code[unique=true];consentReference;creationtime[forceWrite=true,dateformat=dd.MM.yyyy hh:mm:ss];currency(isocode)[allownull=true];date[allownull=true,dateformat=dd.MM.yyyy hh:mm:ss];deliveryAddress(&Item);deliveryCost;deliveryMode(code);deliveryStatus(code,itemtype(code));description;discountsIncludeDeliveryCost[allownull=true];discountsIncludePaymentCost[allownull=true];entryGroups;expirationTime[dateformat=dd.MM.yyyy hh:mm:ss];exportStatus(code,itemtype(code));fraudulent;globalDiscountValues;globalDiscountValuesInternal;grossWeight;guid;isCompleteDelivery;isProcessedByCronJob;isSpecialPrice;language(isocode);locale;modifiedtime[dateformat=dd.MM.yyyy hh:mm:ss];name;netWeight;net[allownull=true];originalVersion(code,versionID)[forceWrite=true];owner(&Item)[allownull=true,forceWrite=true];parent(code);paymentAddress(&Item);paymentCost;paymentInfo(&Item);paymentMode(code);paymentStatus(code,itemtype(code));paymentTermValue;paymentType(code,itemtype(code));placedBy(uid);potentiallyFraudulent;previousDeliveryMode(code);punchOutOrder;purchaseOrderNumber;quoteDiscountValuesInternal;quoteExpirationDate[dateformat=dd.MM.yyyy hh:mm:ss];quoteReference(code,version);requestedDeliveryDate;salesApplication(code,itemtype(code));sapGoodsIssueDate[dateformat=dd.MM.yyyy hh:mm:ss];sapLogicalSystem;sapPlantCode;sapRejectionReason;sapSalesOrganization(distributionChannel,division,salesOrganization);sapSystemType(code,itemtype(code));schedulingCronJob(code);site(cxConfig(code),uid);specialComment;status(code,itemtype(code));statusInfo;store(uid);subtotal;totalDiscounts;totalPrice;totalTax;totalTaxValues;totalTaxValuesInternal;user(uid)[allownull=true];versionID[forceWrite=true,unique=true];volume;volumeUnit;volumeUnitIso;weightUnit;weightUnitIso;workflow(code)

"#% impex.exportItems( ""Order"" , false );"

 

Here it contains all the attributes of the order table.

• .      To export the order data for a few/required attributes with query

Try Syntax as: 

"#% impex.setTargetFile( ""Order.csv"" );"

INSERT_UPDATE Order; pk[unique=true]; code[unique=true];

"#% impex.exportItemsFlexibleSearch( ""select {o.pk}, {o.code} from {Order as o}"");"

  First of all export Order with OriginalVersionId, versionId and without  delivery address

So Till now, only order are exported, but we have order to delivery address relation as well, as the part of data migration activity we need to export Order with Delivery address, so we can import it back into different commerce application.

1.      To Export Orders with DeliveryAddress:-

·         First export

•               B2BUnit, 
•               B2BCustomer, 
•               Address, and then 
•               Orders.

·         As the Delivery address is creating a new field for each order. so update the order with a delivery address

NOTE:  If we import delivery address first it will give error because of owner . Owner is either unit, customer or order itself.

Syntax:  

"#% impex.setTargetFile( ""B2BUnit.csv"" );"

insert_update B2BUnit;&Item;uid[unique=true,allownull=true]

"#% impex.exportItemsFlexibleSearch( ""select {pk} from {B2BUnit}"");"

"#% impex.setTargetFile( ""B2BCustomer.csv"" );"

insert_update B2BCustomer;&Item;uid[unique=true,allownull=true]

"#% impex.exportItemsFlexibleSearch( ""select {pk} from {B2BCustomer}"");"

"#% impex.setTargetFile( ""Address.csv"" );"

insert Address;&pk;appartment;billingAddress[allownull=true];boxno;building;cellphone;company;contactAddress[allownull=true];country(isocode);dateOfBirth[dateformat=dd.MM.yyyy hh:mm:ss];department;district;duplicate;email;fax;firstname;gender(code,itemtype(code));isTemporaryAddress;lastname;latitude;line1;line2;longitude;middlename;middlename2;modifiedtime[dateformat=dd.MM.yyyy hh:mm:ss];original(&pk)[forceWrite=true];phone1;phone2;pobox;postalcode;publicKey;region(country(isocode),isocode);remarks;sapAddressUUID;sapAddressUsage;sapAddressUsageCounter;sapCustomerID;sapMessageFunction;shippingAddress[allownull=true];state;streetname;streetnumber;title(code);town;unloadingAddress[allownull=true];url;visibleInAddressBook;zipbox;owner(&Item)[forceWrite=true]

"#% impex.exportItems( ""Address"" , false );"