Linking Pick list and Delivery note

[mgregur]

Hi experts,

I have a quite intricate use case where I need to link a Delivery note to Picklists which were the base for that Delivery note. Due to some restrictions, the standard process was modified so that certain data is written on the Picklist, and which needs to be also visible on Delivery note printout.

Currently, we're linking as follows:

SELECT 
	FROM
        OPKL A0
        INNER JOIN PKL1 A1 ON A0."AbsEntry" = A1."AbsEntry"
        INNER JOIN DLN1 A2 ON A0."AbsEntry" = A2."PickIdNo" AND A1."OrderEntry" = A2."BaseEntry" AND A1."OrderLine" = A2."BaseLine" AND A1."BaseObject" = A2."BaseType"<br>

But, if we have a situation where one line from Sales Order is split into two picklists, and then those two are used to create one Delivery note, in DLN1 only one value of "PickIdNo" is recorded. This means that we cannot get all needed information.

I already checked the standard tables (DLN1, RDR1, OINM, OIVL, OILM, OITL) and none of them contains this information. Does anyone have some idea where or how this could be linked? Maybe kothandaraman.nagarajan ankit.chauhan1 diego.lother ?

Thank you,

Matija

[LoHa]

Depending on how you work. (auto delnote or not)- A Field in the Documents where the picklist number can be stored

[LoHa]

Hi Matija,

perhabs you could compare last updatedate (pkl closing) with date from delnote?

regards Lothar

[mgregur]

Hi,

again, thank you for the effort. The problem is that usually three shipments are made for the same customer on the same day, and each has similar items on it. So any "fuzzy" linking (e.g. ItemCode, DocDate, CardCode) is not possible because there will be several documents with the same date for the same customer and with the same items, but with different information from Picklists.

BR,

Matija

[LoHa]

Hi Matija,

quick and dirty,.... try this

/*Getting all DelNotes and SalesOrder*/
WITH BaseDoc AS 
(
SELECT
 [DLN1].[DocEntry]
,[DLN1].[BaseEntry]
,[DLN1].[BaseLine]
,[DLN1].[BaseType]
,[DLN1].[ItemCode]
FROM
[DLN1]
WHERE
[DLN1].[BaseEntry] = (SELECT Distinct BaseEntry FROM DLN1 WHERE DocEntry = 105595) /*Enter DocEntry From DelNote*/
)
/*Getting Picklists*/
SELECT
DISTINCT
[PKL1].[AbsEntry]
FROM
[PKL1]
INNER JOIN [BaseDoc] ON [BaseDoc].[BaseEntry] = [PKL1].[OrderEntry] AND [BaseDoc].[BaseLine] = [PKL1].[OrderLine] AND [PKL1].[BaseObject] = 17

regards

Lothar

[mgregur]

Hi Lothar,

thank you for trying, but this does not work. It gives back ALL Picklists for that Sales Order, same as the query I'm using. But I need the information which exactly Picklists are used for that certain Delivery note.

BR,

Matija

[LoHa]

Hi Matija,

I know that system behavior, because there is only one field in the lines for that and no table (same with target-document).

You have to create your join from pkl to rdr with docentry/object/linenum

regards Lothar

[mgregur]

Hi Lothar,

system behaviour is the same, since ORDR only records one Picklist. So that does not help.

I even thought that maybe it is written in changelog (several instances), but it's not. This I find specially problematic, as one line can have successive deliveries (e.g. monthly) and system does not record any changes to ORDR/RDR1, as if it does a SQL update instead of recording every new document instance with new PickIdNo.

BR,

Matija

[LoHa]

Hi Matija,

the problem is, that there are no connections between dln and pkl.

You can only see the basedocument in pkl.

I think you have to go from dln to rdr and then into pkl.

Perhabs the ODLN.DataSource gives you a hint where it is from

regards Lothar

[mgregur]

Hi Lothar,

DataSource does not help, neither does returning to Order for this information. The main problem is exactly in the part where you have several Picklists packed to one document line. You have a link to Picklist (DLN1.PickIdNo) on the line, but system only records one Picklist, no matter how many there are.

BR,

Matija