Extract DESADV to CSV

[Tan Tran]

Hi,

I'm extracting an DESADV into separate CSV files (one for header and one for lines)

CSV #1 - contain only header column:

1. 'RFF','C506.1153'

2. 'NAD','3035'

3. 'GIN','C208#1.7402#1

CSV #2 - contain lines column:

1. 'LIN','1082'

2. 'LIN','C212.7140'

3. 'QTY','C186.6060'

So far so good except when I ingest 2 csv to sql database into 2 separated tables called "Header" and "Lines", I can't figure out how to reference lines to header via GIN column.

For example,  each GIN can contains multiple Lines.

Can someone share idea how can I achieve this goal?

Thank you,

Tan.T

[Eppye Bots]

what is the goal exactly?

a shipment contains ShippingUnits; each shipping unit can contain multiple items. (Some sectors use only one item per shipping unit)

what is the goal of the referencing?

kind regards, Henk-Jan Ebbers

--
You received this message because you are subscribed to the Google Groups "Bots Open Source EDI Translator" group.
To unsubscribe from this group and stop receiving emails from it, send an email to botsmail+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/botsmail/4cecb40e-9df1-42b9-a3e6-fed5e5087f3en%40googlegroups.com.

[Tan Tran]

Hi Henk-Jan,

The goal is we're inserting each ASN into 2 tables (header and lines) and these records are inner join by GIN. We have an application that scan printed SSCC label and update certain columns in header / lines table.

So I'm hoping I can:

CSV #1 - header column:

1. GIN

CSV #2 - contain lines column:

1. GIN

CSV #1 inner join CSV #2 ON GIN

Thank you,

Tan.T

[Eppye Bots]

not sure I understand.

header = message, message has an unique ID. Do not think a SSCC/ShippingUNitID is useful in the header?

but:

the unique key to your 'lines' table is

kind regards, Henk-Jan Ebbers

To view this discussion on the web visit https://groups.google.com/d/msgid/botsmail/808f48ab-322f-45f6-a5c1-2e7c0175643an%40googlegroups.com.

[Tan Tran]

Hi Henk-Jan,

It makes more sense when someone hold a scanner and scan a box with printed SSCC label in front.

Application pickups this SSCC and lookup up Header table and pull OrderID, InvoiceID and List of Items (barcode + quantity).

User perform some action (check-in etc...) on these records and submit. These will update header / lines table such as Check-in date etc...

Thank you

Tan.T

[Eppye Bots]

what is the key to header table?

messageID or SSCC?

kind regards, Henk-Jan Ebbers

To view this discussion on the web visit https://groups.google.com/d/msgid/botsmail/c58f2ae9-16ea-4984-a6b0-2f2c82e809bdn%40googlegroups.com.

[Tan Tran]

SSCC

[Eppye Bots]

than why do you call the table 'header'? ;=))))

so.... key of 'header' table is SSCC....

use SSCC in line table to refer to 'header' table.

does that make sense/help?

kind regards, Henk-Jan Ebbers

To view this discussion on the web visit https://groups.google.com/d/msgid/botsmail/fc47f21d-c091-4f4f-bf4b-788261fb5240n%40googlegroups.com.

[Tan Tran]

Hi Henk-Jan,

That's exactly I'm aiming for. Joining 2 tables via SSCC

When I loop through LIN, it does not find GIN. GIN only appears in PCI

Can you shed me light how I can extract GIN at LIN loop? or other way to archieve the goal (SSCC on both table)

Thank you,

Tan.T

[Eppye Bots]

loop over CPS.

- extra PAC-GIN

- extract LINs

kind regards, Henk-Jan Ebbers

To view this discussion on the web visit https://groups.google.com/d/msgid/botsmail/9ac1ae6a-535a-4c76-9dd6-9eb98f457ee6n%40googlegroups.com.

[Tan Tran]

BTW, Header and Lines term I used is more related retail transaction, not EDI.

These table schema is designed by a retail sector. Hence, it's name Header and Lines of a sale transaction.

[Tan Tran]

In a single loop or 2 separated loop?

[Eppye Bots]

yes, but in order we have ONE header, multiple lines.

in invoice: one header, multiple lines.

now DESADV has multiple headers, where one header represents a Shipping Unit.

I hope you understand the confusion.

kind regards, Henk-Jan Ebbers

To view this discussion on the web visit https://groups.google.com/d/msgid/botsmail/ae7c0adf-e55b-4340-8c8d-d3605cc402d2n%40googlegroups.com.

[Tan Tran]

Hi Henk-Jan,

Loop CPS - total 3 iteration because only 3 GIN.

  Extract all GIN

  LIN - only 3 Lines because only 3 iteration.

How can i extract all lines belong to each GIN?

[Eppye Bots]

something like:

for CPS in inn.getloop(UNH.CPS):

    SSCC = CPS.get(PAC.PCI.GIN)

    for LIN in CPS.getloop(CPS.LIN)

probably you will have to skip first CPS.

(assuming this is a fairly 'standard' retail DESADV)

kind regards, Henk-Jan Ebbers

To view this discussion on the web visit https://groups.google.com/d/msgid/botsmail/8d6e7563-6d83-4ff4-bb75-b4c1e7d93dabn%40googlegroups.com.

[Tan Tran]

Thank you, Henk-Jan.

I'll give that a shot.

[Tan Tran]

Hi Henk-Jan,

Thank you very much for your guidance.

I'm able to include GIN in each LIN record when extracting into csv.

Tan.T