PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 11/20/2021 9:58 AM by  Richard
How to tie DBWHWHS to DBBLCOR
 0 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Richard
Consultant
Private
New Member
(1 points)
New Member
Posts:1


Send Message:

--
11/20/2021 9:58 AM

    I am new to Lawson and have been tasked to write an SQL that lists shipments with other data.  Not knowing much about Lawson this is what I've come up with;

     SELECT

    WHS.DBWHS_SHIPMENT_NBR
    ,WHS.DBWHS_DOC_NBR
    ,WHS.DBWHS_VENDOR
    ,WHS.DBWHS_CARRIER
    ,COR.DBCOR_ORDER_NBR
    ,COR.DBCOR_ORDER_TYPE
    ,WHL.DBWHL_DESCRIPTION
    ---,' ' AS sggroup
    ---,' ' AS sold_to-pt
    ,COR.DBCOR_CUST_PO_NBR
    ---, ' ' as fol-doc
    ,WHS.DBWHS_BOL_NBR
    ,WHS.DBWHS_WEIGHT
    ,WHS.DBWHS_CARTONS
    ,WHS.DBWHS_SHIP_DATE
    ,WHS.DBWHS_TRANS_ID
    ---,' ' as proc-id
    ,WHS.DBWHS_METHOD
    ,SHP.DBSHP_CUSTOMER
    ,SHP.DBSHP_NAME
    ,SHP.DBSHP_NAME
    ,SHP.DBSHP_ADDR1
    ,SHP.DBSHP_ADDR2
    ,SHP.DBSHP_CITY
    ,SHP.DBSHP_STATE
    ,SHP.DBSHP_ZIP
    ---,DBBLSHP.''COST CENTER
    ,SPC.DBSPC_COMMENT
    ---,'' as whn
    ,WDH.DBWDH_DESTINATION
    ,WDH.DBWDH_DEST_NAME
    ,SHP.DBSHP_TAX_CODE ---,' ' as tax_juris
    ,WDH.DBWDH_DEST_STATE
    ,WDH.DBWDH_DEST_CNTRY
    ,WDH.DBWDH_DEST_ZIP
    ,WHS.DBWHS_CARRIER
    ,VEN.DBVEN_VENDOR_VNAME---,'v' as carrier name
    ,WHS.DBWHS_FREIGHT_CODE---', as incoterm code
    ---,' ' as incoterm description
    ,WHS.DBWHS_FRT_CHG_CURR
    ,ARH.DBARH_TRAN_AMT
    ---,' ' as acct
    ,ACM.DBACM_CREDIT_LIM
    ---,' ' as pint
    ,WHS.DBWHS_COMPANY
    ,COR.DBCOR_ORDER_DATE
    ---,WDM.DBWDM_UPDATE_DATE
    ,WHS.DBWHS_STATUS
    ,WHS.DBWHS_SHIP_DATE
    ---,'LAW' as ERP-IDENTIFIER

    FROM LAWDBF7B.DBWHWHS WHS

    LEFT JOIN LAWDBF7B.DBWHWHL WHL ON WHS.DBWHS_COMPANY=WHL.DBWHL_COMPANY AND WHS.DBWHS_DOC_NBR=WHL.DBWHL_DOC_NBR
    LEFT JOIN LAWDBF7B.DBWHWDH WDH ON WHS.DBWHS_COMPANY=WDH.DBWDH_COMPANY AND WHS.DBWHS_DOC_NBR=WDH.DBWDH_DOC_NBR
    LEFT JOIN LAWDBF7B.DBBLSHP SHP ON WHS.DBWHS_COMPANY=SHP.DBSHP_COMPANY AND WHS.DBWHS_CUST_SHIP_TO=SHP.DBSHP_SHIP_TO

    LEFT JOIN LAWDBF7B.DBBLSPC SPC ON  SHP.DBSHP_CUSTOMER=SPC.DBSPC_CUSTOMER AND SHP.DBSHP_SHIP_TO=SPC.DBSPC_SHIP_TO 
    LEFT JOIN LAWDBF7B.DBARARH ARH ON ARH.DBARH_COMPANY = SHP.DBSHP_COMPANY AND ARH.DBARH_CUSTOMER=SHP.DBSHP_CUSTOMER
    LEFT JOIN LAWDBF7B.DBARACM ACM ON ACM.DBACM_COMPANY=SHP.DBSHP_COMPANY AND ACM.DBACM_CUSTOMER=SHP.DBSHP_CUSTOMER
    LEFT JOIN LAWDBF7B.DBBLCOR COR ON COR.DBCOR_COMPANY=SHP.DBSHP_COMPANY AND COR.DBCOR_CUSTOMER=SHP.DBSHP_CUSTOMER
    LEFT JOIN LAWDBF7B.DBBLCOL COL ON COL.DBCOL_COMPANY = COR.DBCOR_COMPANY AND COL.DBCOL_ORDER_NBR = COR.DBCOR_ORDER_NBR AND COL.DBCOL_LINE_NBR = WHL.DBWHL_LINE_NBR


    LEFT JOIN LAWDBF7B.DBAPVEN VEN ON  VEN.DBVEN_VENDOR = COL.DBCOL_VENDOR

    WHERE WHS.DBWHS_SHIP_DATE >='20210901' AND WHS.DBWHS_STATUS = '3' AND VEN.DBVEN_VEN_CLASS = 'FRT'
    This runs for a long time and does not produce the desired results.  Can anyone guide me as to what I am doing wrong?

    Thanks much!

    Rich

    You are not authorized to post a reply.