Disable the user ID

1. Delete the Link Between LN User and DB User(If exists) from session ttdba0510m000 and do Convert to Run Time
2. Delete DB User for that user(if exists) from session ttdba0515m000 and do Convert to Run Time
3. Delete the User Id from LN User Data from session ttaad2500m000 and do Convert to Run Time
4. Delete the User Id from Active Directory(For Windows Server) - This step is not mandatory

Important DLLs to handle Multi Currency environment

tcemmdll5000 - Home Currency functions
tcemmdll5010 - Multi Currency Operations
tcemmdll5015 - Multi Currency Operations (only for tf)

Split 1 Excel file into multiple Excel files based on data.

Requirement:
1. We have Sales Data dump with columns like- Order Number, Position, Customer, Item Name, Order Status.
2. We must create separate files for each Customer from this dump.

So lets say we create a file with the file name as <Customer Code>.xlsx and store all the records for that Customer into this new excel file. Similarly we create multiple files. Thus a new file created for each unique Customer.

Solution:
 I created the following Excel VBA Macro on the excel where full Sales Data dump is stored.

Sub details()
     Dim sh1 As Worksheet
     Dim newsh1 As Worksheet
     Dim newwb As Workbook
     Dim i, totalrows As Integer
     Dim j As Integer
     Dim newfile As String
     Dim sortcol As String
     Dim folderpath As String
     
     sortcol = InputBox("Enter the Column Number based on which u want to split the Excel. Eg. A")
     folderpath = InputBox("Enter path to store split files. Eg. C:\folderName\")
     
     Set sh1 = ThisWorkbook.Sheets("Sheet1")
     sh1.Range(sortcol + "1").Sort Key1:=sh1.Range(sortcol + "1"), Order1:=xlAscending, Header:=xlNo
     totalrows = sh1.Range(sortcol + "1", sh1.Range(sortcol + "1").End(xlDown)).Rows.Count
     newfile = ""
     j = 0
     
     For i = 1 To totalrows

        If newfile = sh1.Range(sortcol + CStr(i)) Then
               j = j + 1
               newsh1.Range("A" + CStr(j) + ":Z" + CStr(j)) = sh1.Range("A" + CStr(i) + ":Z" + CStr(i)).Value
        Else
               If newfile <> "" Then
                     newwb.Save
                     newwb.Close
               End If
               newfile = sh1.Range(sortcol + CStr(i)).Value
               Set newwb = Workbooks.Add
               j = 0
               With newwb
                 .SaveAs Filename:=folderpath + newfile + ".xlsx"
                 Set newsh1 = .Sheets("Sheet1")
               End With
               
               j = j + 1
               newsh1.Range("A" + CStr(j) + ":Z" + CStr(j)) = sh1.Range("A" + CStr(i) + ":Z" + CStr(i)).Value
        End If
     Next i
    If newfile <> "" Then
        newwb.Save
        newwb.Close
    End If
  End Sub
      

This macro is dynamic enough to handle any similar excel requirement of yours.
Usage-
1. The Sales Data dump excel file should not have any column headers.
2. When you run this. You will be asked to enter the Column name based on what you want to split the excel file. So in our case I will enter value "C" as my Customer Code is present in Cth column.
3. Then it will ask for the folder name where you want the newly generated split files to be stored.
I entered C:\temp\

Sql Server datetime timezone handling

SQL Server made modifications in 2005 onward where the internal timezone is saved in UTC. This was largely due to geo-replication and HA projectors involving log shipping, and having the log shipping times saved in different time zones made it impossible for the old method to restore them.
Thus, saving everything internally in UTC time allowed SQL Server to work well globally. This is one of the reasons why daylight savings is kind of a pain to deal with in Windows, because other MS products such as Outlook also save the date/time internally as UTC and create a offset that needs to be patched.
I work in a company where we have thousands of servers (not MS SQL Servers though, but all kinds of servers) spread out all across the world, and if we didn't specifically force everything to go by UTC, we would all go insane very quickly.

UTC is generally not very meaningful to a user.  You could theoretically convert the times to the end user’s local time zone.

A DATETIMEOFFSET gives you the ability to store local time and UTC time in one field. This allows for very simple and efficient reporting in local or UTC time without the need to process the data for display in any way.

These are the two most common requirements -
1. local time for local reports and
2. UTC time for group reports.

The local time is stored in the DATETIME portion of the DATETIMEOFFSET and the OFFSET from UTC is stored in the OFFSET portion, thus conversion is simple and, since it requires no knowledge of the timezone the data came from, can all be done at database level.

If you don't require times down to milliseconds, e.g. just to minutes or seconds, you can use DATETIMEOFFSET(0). The DATETIMEOFFSET field will then only require 8 bytes of storage - the same as a DATETIME.

Using a DATETIMEOFFSET rather than a UTC DATETIME therefore gives more flexibility, efficiency and simplicity for reporting.

Example:

SELECT t_odat As 'Order Date stored in SQL Server', CONVERT(datetime,

               SWITCHOFFSET(CONVERT(datetimeoffset,

                                    t_odat),

                            DATENAME(TzOffset, SYSDATETIMEOFFSET())))

       AS 'Order Date In LN'

from ttdsls401101

Production Planning

Bill Of Material: A (1) --- B(1). (A  - Manufactured item , B – Purchase item)

Routing: Operation for A/Unit – 1 Hour

Supply Time for B – 10 Days

Safety Time for B – 5 Days. (That means this item must be available 5 days in advance before start of the production).

Sales Order for A – 100 quantity – Planned Delivery Date – 30.01.2014

In the above case, after the planning run system gives two orders. 1) Planned production order for ‘A’ 2) Planned purchase order for ‘B’.

·         Planned Production Order Planned Finish date = Sales Order Planned delivery date. As there is no safety time mentioned for A, both the dates are same.

·         According to manufacturing time it will take around 4 days to complete the production of 100 units. SO the planned start date for A is 26.01.14.

·         As the production is starting on 26.01.14, the raw material must be available by that time. So the required date for B is 26.01.14.

·         But there is a safety time for 4 days. That means, the raw material must be available before its required date. So the planned finish date is 22.01.14. If at all there is safety time, the required date will be same as planned finish date.

BAAN/LN DLL to Print Amount in Words

tcmcs.dll0006.decode

void tcmcs.dll0006.decode( domain tcamnt amount, boolean decimals, ref domain tcmcs.s130m decode0 mb, ref domain tcmcs.st65m decode1 mb, ref domain tcmcs.st65m decode2 mb, ref domain tcmcs.st65m decode3 mb, domain tclang i.lang )

Expl.:
Pre: -
Post: -
Input: amount to be decoded, maximum 11+2
 decimals required (boolean)
Output:
 - decode0  Decoded amount of 130 chrs.
 - decode1  Same decoded amount in two strings of 65 chars
 - decode2  "
 - decode3  Decode of the decimals

SO Sequencing

To add to my earlier Blog on PO Sequencing, I did some study on when all the SO sequence is generated and what  is the Significance of “Order Line Type” for the SO Line Sequences.

This is what I found-

Note: There is no “Back Orders Allowed” checkbox in SO Parameters. You can only Control if Back Orders is confirmed Automatically with “Confirm Back Order Automatically” flag in SO Parameters.

Generally when there is only one sequence associated with a SO Line, the sequence number of SO Line is 0  with Order Line as tdsls.oltp.detail.

Sequence	Order Line Type	Ordered Quantity	Delivered Quantity
0	tdsls.oltp.detail	5	0

Sequences can be generated in 2 ways-

1. Split Delivery Line in the Sales Order Planned Delivery Lines (tdsls4101m100) session. Main Sequence with Line Type as tdsls.oltp.total and its sub seqences with Line Type as tdsls.oltp.detail (s)

2. Partial Delivery of SO Line, were new sequence gets generated with Order Line Type as tdsls.oltp.backorder

Split Delivery Line in the Sales Order Planned Delivery Lines (tdsls4101m100):

As soon as you split the SO Line two more sequences are added to your SO Line, making the total sequences to be 3 (i.e.  0, 1, 2)

Sequence	Delivery Type	Order Line Type	Ordered Quantity	Delivered Quantity
0	not.applicable	tdsls.oltp.total	5	0
1	warehousing	tdsls.oltp.detail	4	0
2	warehousing	tdsls.oltp.detail	1	0

Now you can do “Print SO Acknowledgements”/”Release SO to Warehousing” for each tdsls.oltp.detail sequence separately.

Now let’s Partially Ship 3 qty from sequence 1

Sequence	Delivery Type	Order Line Type	Ordered Quantity	Delivered Quantity	Back Order Qty	Backorder Confirmed?
0	not.applicable	tdsls.oltp.total	5	3	1	No
1	warehousing	tdsls.oltp.detail	4	3	1	No
2	warehousing	tdsls.oltp.detail	1	0	0	No

Now we have to Confirm the Back Order against sequence 1, so that it can be received. When Back Order is confirmed, the data in the system is as follows:

Sequence	Delivery Type	Order Line Type	Ordered Quantity	Delivered Quantity	Back Order Qty	Backorder Confirmed?	Linked Sequence
0	not.applicable	tdsls.oltp.total	5	3	1	No	0
1	warehousing	tdsls.oltp.detail	4	3	1	Yes	3
2	warehousing	tdsls.oltp.detail	1	0	0	No	0
3	warehousing	tdsls.oltp.backorder	1	0	0	No	0

Now I ship Sequence 3.

Sequence	Delivery Type	Order Line Type	Ordered Quantity	Delivered Quantity	Back Order Qty	Backorder Confirmed?	Linked Sequence
0	not.applicable	tdsls.oltp.total	5	4	0	No	0
1	warehousing	tdsls.oltp.detail	4	3	1	Yes	3
2	warehousing	tdsls.oltp.detail	1	0	0	No	0
3	warehousing	tdsls.oltp.backorder	1	1	0	No	0

***********************************************************************************

Let’s See how sequencing works for Partial Delivery of SO Line, were new sequence gets generated with Order Line Type as tdsls.oltp.backorder:

Sequence	Order Line Type	Ordered Quantity	Delivered Quantity
0	tdsls.oltp.detail	3	0

I ship 2 Qty:

Sequence	Delivery Type	Order Line Type	Ordered Quantity	Delivered Quantity	Back Order Qty	Backorder Confirmed?	Linked Sequence
0	warehousing	tdsls.oltp.detail	3	2	1	No	0

I Confirm the Back Order:

Sequence	Delivery Type	Order Line Type	Ordered Quantity	Delivered Quantity	Back Order Qty	Backorder Confirmed?	Linked Sequence
0	warehousing	tdsls.oltp.detail	3	2	1	Yes	1
1	warehousing	tdsls.oltp.backorder	1	0	0	No	0

I Shipped The 1 Qty for Sequence 1

Sequence	Delivery Type	Order Line Type	Ordered Quantity	Delivered Quantity	Back Order Qty	Backorder Confirmed?	Linked Sequence
0	warehousing	tdsls.oltp.detail	3	2	1	Yes	1
1	warehousing	tdsls.oltp.backorder	1	1	0	No	0