Freight Data Output to Text File

Sort:
You are not authorized to post a reply.
Author
Messages
JonA
Veteran Member
Posts: 1162
Veteran Member
    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 - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
    JonA
    Veteran Member
    Posts: 1162
    Veteran Member
      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 - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
      Donna
      Veteran Member
      Posts: 110
      Veteran Member
        I am happy that you resolved your question but I wondered if you found a Lawson resource who is well versed in SQL?
        JonA
        Veteran Member
        Posts: 1162
        Veteran Member
          Not yet. Our Lawson DBA helped me out. I hoped to find someone here in the forums but no takers.
          Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
          You are not authorized to post a reply.