PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 02/03/2016 2:08 PM by  JonA
Freight Data Output to Text File
 3 Replies
Sort:
You are not authorized to post a reply.
Author Messages
JonA
Private
Private
Veteran Member
(2766 points)
Veteran Member
Posts:972


Send Message:

--
01/18/2016 9:18 AM
    I need someone well versed in SQL to check my script for syntax and structure. I can read scripts and understand for the most part what a script was designed to do but this is the first one I've built. I created this one by starting with Crystal and reverse engineering other examples and lots of Google searches. The purpose of this script is to create a text file of freight expense history (we use AOC codes) for the past 7 days and file name should be MIDMIC-COMP-YYYYMMDD.txt where YYMMDD is the file creation date, (MIDMIC-COMP-20160108.txt for example).

    column dt new_val x
    select to_char(sysdate,'yyyymmdd') from dual;
    spool /lawson/lawprod/law/prod/work/optifreight/MIDMIC-COMP-&x.txt

    select
    ‘MIDMIC’ -- "Billing Group Short Name"
    ICLOCATION.PO_NAME, -- "Facility Name"
    APINVOICE.PO_NUMBER, -- "Purchase Order Number"
    APINVOICE.INVOICE, -- "Invoice Number"
    APINVOICE.VENDOR, -- "Vendor Number"
    coalesce (APVENLOC.VENDOR_VNAME, APVENMAST.VENDOR_VNAME), -- "Vendor Name"
    APDISTRIB.ORIG_TRAN_AMT, -- "Freight Charge"
    PURCHORDER.LOCATION, -- "Facility Code"
    APDISTRIB.DIS_ACCT_UNIT, -- "Cost Center"
    GLNAMES.DESCRIPTION, -- "Department Name"
    ICLOCATION.PO_ADDR1, -- "Facility Address 1"
    ICLOCATION.PO_ADDR2, -- "Facility Address 2"
    ICLOCATION.PO_CITY_ADDR5, -- "Facility City"
    ICLOCATION.PO_STATE_PROV, -- "Facility State"
    ICLOCATION.PO_POSTAL_CD, -- "Facility Zip"
    APINVOICE.BASE_TOT_PMT, -- "Total Invoice Amount"
    APINVOICE.CREATE_DATE, -- "Invoice Date"
    APVENLOC.VEND_ACCT, -- "Vendor Account Number"

    from PRODLAW.GLNAMES GLNAMES
    inner join PRODLAW.APDISTRIB APDISTRIB
    on GLNAMES.COMPANY=APDISTRIB.COMPANY
    and GLNAMES.ACCT_UNIT=APDISTRIB.DIS_ACCT_UNIT
    inner join PRODLAW.APVENMAST APVENMAST
    on APINVOICE.VENDOR = APVENMAST.VENDOR
    inner join PRODLAW.APINVOICE APINVOICE
    on APDISTRIB.COMPANY=APINVOICE.COMPANY
    and APDISTRIB.VENDOR=APINVOICE.VENDOR
    and APDISTRIB.INVOICE=APINVOICE.INVOICE
    and APDISTRIB.SUFFIX=APINVOICE.SUFFIX
    left outer join PRODLAW.APVENLOC APVENLOC
    on APINVOICE.VENDOR=APVENLOC.VENDOR
    and APINVOICE.PURCH_FR_LOC=APVENLOC.LOCATION_CODE
    inner join PRODLAW.ICLOCATION ICLOCATION
    on PURCHORDER.COMPANY =ICLOCATION.COMPANY
    and PURCHORDER.LOCATION =ICLOCATION.LOCATION
    INNER JOIN PRODLAW.PURCHORDER PURCHORDER
    on APINVOICE.COMPANY=PURCHORDER.COMPANY
    and APINVOICE.PO_CODE=PURCHORDER.PO_CODE
    and APINVOICE.PO_NUMBER=PURCHORDER.PO_NUMBER

    where
    APINVOICE.CREATE_DATE>=sysdate -7
    and APINVOICE.PO_NUMBER>' '
    and APDISTRIB.PO_AOC_CODE in ('FT','ON','2D','3D')

    spool off;
    Jon Athey - Supply Chain Analyst - Materials Management - MidMichigan Health
    JonA
    Private
    Private
    Veteran Member
    (2766 points)
    Veteran Member
    Posts:972


    Send Message:

    --
    01/26/2016 11:49 AM
    Figured it out. The FROM statement was a little out of order. Should be...

    from PRODLAW.GLNAMES GLNAMES
    inner join PRODLAW.APDISTRIB APDISTRIB
    on GLNAMES.COMPANY=APDISTRIB.COMPANY
    and GLNAMES.ACCT_UNIT=APDISTRIB.DIS_ACCT_UNIT
    inner join PRODLAW.APVENMAST APVENMAST
    inner join PRODLAW.APINVOICE APINVOICE
    on APVENMAST.VENDOR=APINVOICE.VENDOR
    on APDISTRIB.COMPANY=APINVOICE.COMPANY
    and APDISTRIB.VENDOR=APINVOICE.VENDOR
    and APDISTRIB.INVOICE=APINVOICE.INVOICE
    and APDISTRIB.SUFFIX=APINVOICE.SUFFIX
    left outer join PRODLAW.APVENLOC APVENLOC
    on APINVOICE.VENDOR=APVENLOC.VENDOR
    and APINVOICE.PURCH_FR_LOC=APVENLOC.LOCATION_CODE
    inner join PRODLAW.ICLOCATION ICLOCATION
    inner join PRODLAW.PURCHORDER PURCHORDER
    on ICLOCATION.COMPANY=PURCHORDER.COMPANY
    and ICLOCATION.LOCATION=PURCHORDER.LOCATION
    on APINVOICE.COMPANY=PURCHORDER.COMPANY
    and APINVOICE.PO_CODE=PURCHORDER.PO_CODE
    and APINVOICE.PO_NUMBER=PURCHORDER.PO_NUMBER
    Jon Athey - Supply Chain Analyst - Materials Management - MidMichigan Health
    Donna
    Private
    Private
    Veteran Member
    (259 points)
    Veteran Member
    Posts:107


    Send Message:

    --
    02/03/2016 2:03 PM
    I am happy that you resolved your question but I wondered if you found a Lawson resource who is well versed in SQL?
    JonA
    Private
    Private
    Veteran Member
    (2766 points)
    Veteran Member
    Posts:972


    Send Message:

    --
    02/03/2016 2:08 PM
    Not yet. Our Lawson DBA helped me out. I hoped to find someone here in the forums but no takers.
    Jon Athey - Supply Chain Analyst - Materials Management - MidMichigan Health
    You are not authorized to post a reply.