#!/usr/bin/perl

use strict;
use warnings;
use SQL::Statement;
#  my $sql    = "
#     SELECT a FROM b JOIN c WHERE c=? AND e=7 ORDER BY f DESC LIMIT 5
+,2
#  ";

 my $sql = "
/* ******************** NAT OTIF *************************************
+****** */
/* This query creates the measures file for NAT OTIF cube */
/* This query has been modified to get data from MCSI_ORD_LN_MONTHLY *
+/
/* The output names of the fields have been changed to match the old v
+ersion and the cube names  6/10/04 */
/* New field ORDERED_LATE added 12/16/04 */
Select

REF_DT,
SHIP_TO_CUST_ID,
EXT_MATL_GRP_ID,
STK_CLASS_ID,
MATL_ID,
FACILITY_ID,
BRAND_ID,
QTY_UNIT_MEAS_ID,
ABC_SLS_DMAN_IND,
DELIV_PRTY_ID,

NET_DUE_SFR_QTY,    
NET_DUE_CFR_QTY,
COMMITTED_LATE,
COMMIT_ONTIME_DUE_CURR_QTY,
RELEASED_ONTIME,
BLOCK_991,
BLOCK_021,
OLD_BLOCK_99,
OLD_BLOCK_02CREDIT,
ORDERED_LATE,
NO_DELIVERY_NOTE,
SHIPPED_LATE,
COMMIT_ONTIME_SHIPPED_ONTIME,
RELEASED_LATE_SHIPPED_ONTIME,
COMMIT_LATE_SHIPPED_ONTIME,
OTIF_QTY_SHIPPED_ONTIME,
SHIPPED_ONTIME_SFR_QTY,


((Case When Main.DELIV_PRTY_ID = '99' Then (Main.OLD_BLOCK_99 - Main.O
+rdered_Late) Else 0 End)) As BLOCK_99,
((Case When Main.DELIV_PRTY_ID <> '99' Then (Main.OLD_BLOCK_02CREDIT -
+ Main.Ordered_Late) Else 0 End)) As BLOCK_02CREDIT
From
(
select 
    REF_DT As REF_DT,
    substr(SHIP_TO_CUST_ID,3,8) As SHIP_TO_CUST_ID,
    OVER_ALL.EXT_MATL_GRP_ID As EXT_MATL_GRP_ID,
    OVER_ALL.STK_CLASS_ID  As STK_CLASS_ID,
    Substr(OVER_ALL.MATL_ID,11,8) As MATL_ID,
    FACILITY_ID As FACILITY_ID,
    OVER_ALL.BRAND_ID As BRAND_ID,
    QTY_UNIT_MEAS_ID As QTY_UNIT_MEAS_ID,
    (Case When MAT.ABC_SLS_DMAN_IND > '' then MAT.ABC_SLS_DMAN_IND els
+e 'Blank' end) As ABC_SLS_DMAN_IND,
    OVER_ALL.DELIV_PRTY_ID As DELIV_PRTY_ID,

    COALESCE(Sum(OVER_ALL.NET_DUE_SFR_QTY),0) As NET_DUE_SFR_QTY,    
    COALESCE(Sum(OVER_ALL.NET_DUE_CFR_QTY),0) As NET_DUE_CFR_QTY,
    Sum(NET_DUE_CFR_QTY - COMMIT_ONTIME_DUE_CURR_QTY) As COMMITTED_LAT
+E,
    COALESCE(Sum(COMMIT_ONTIME_DUE_CURR_QTY),0) As COMMIT_ONTIME_DUE_C
+URR_QTY,
    COALESCE(Sum(Released_Ontime),0) As RELEASED_ONTIME,
    COALESCE(Sum((case when OVER_ALL.DELIV_PRTY_ID = '99' then (OVER_A
+LL.COMMIT_ONTIME_DUE_CURR_QTY - OVER_ALL.Released_Ontime) else 0 end)
+),0) As BLOCK_991,
    COALESCE(Sum((case when OVER_ALL.DELIV_PRTY_ID <> '99' then (OVER_
+ALL.COMMIT_ONTIME_DUE_CURR_QTY - OVER_ALL.Released_Ontime) else 0 end
+)),0) As BLOCK_021,
    ((case when BLOCK_991 < 0 then 0 else BLOCK_991 end)) As OLD_BLOCK
+_99,
    ((case when BLOCK_021 < 0 then 0 else BLOCK_021 end)) As OLD_BLOCK
+_02CREDIT,
    COALESCE(Sum(OVER_ALL.Ordered_Late),0) As ORDERED_LATE,
    COALESCE(Sum(No_Delivery_note),0) As NO_DELIVERY_NOTE,
    COALESCE(Sum(Shipped_Late_Qty),0) As SHIPPED_LATE,
    COALESCE(Sum(Commit_Ontime_Shipped_Ontime),0) As COMMIT_ONTIME_SHI
+PPED_ONTIME,
    COALESCE(Sum(Released_Late_Shipped_Ontime),0) As RELEASED_LATE_SHI
+PPED_ONTIME,
    COALESCE(Sum(Commit_Late_Shipped_Ontime),0) As COMMIT_LATE_SHIPPED
+_ONTIME,
    COALESCE(Sum(OTIF_Qty_Shipped_Ontime),0) As OTIF_QTY_SHIPPED_ONTIM
+E,
    COALESCE(Sum(SHIPPED_ONTIME_SFR_QTY),0) As SHIPPED_ONTIME_SFR_QTY
From
    /* ***************************************** BEGINNING OF OVER_ALL
+ ************************************* */
    (Select 
        OS.REF_DT As REF_DT,
        OS.SHIP_TO_CUST_ID As SHIP_TO_CUST_ID,
        OS.EXT_MATL_GRP_ID As EXT_MATL_GRP_ID,
        OS.STK_CLASS_ID  As STK_CLASS_ID,
        OS.MATL_ID As MATL_ID,
        OS.QTY_UNIT_MEAS_ID As QTY_UNIT_MEAS_ID,
        OS.FACILITY_ID As FACILITY_ID,
        OS.BRAND_ID As BRAND_ID,
        OS.ORDER_ID As ORDER_ID,
        OS.ORDER_LINE_NBR As ORDER_LINE_NBR,
        OS.DELIV_PRTY_ID As DELIV_PRTY_ID,
        Max(OS.NET_DUE_CFR_QTY)  As NET_DUE_CFR_QTY,
        Max(OS.NET_DUE_SFR_QTY)  As NET_DUE_SFR_QTY,
        ((Case when Max(OS.COMMIT_ONTIME_DUE_CURR_QTY) > Max(OS.NET_DU
+E_CFR_QTY) then Max(OS.NET_DUE_CFR_QTY) else Max(OS.COMMIT_ONTIME_DUE
+_CURR_QTY) end)) As COMMIT_ONTIME_DUE_CURR_QTY,
        Sum(OS.Released_Ontime)  As Released_Ontime,
        Sum(OS.Ordered_Late) As Ordered_Late,
        Sum((case when OS.COMMIT_ONTIME_DUE_CURR_QTY - OS.Released_Ont
+ime > 0 and OS.DELIV_NOTE_CREA_DT >= OS.REQ_SHIP_DT And OS.GOODS_ISS_
+DT - OS.REQ_SHIP_DT <= 1 then OS.QTY_TO_SHIP else 0 end)) As Released
+_Late_Shipped_Ontime1,
        Sum(OS.Shipped_Late_Qty) As Shipped_Late_Qty,
        Sum(OS.Commit_Ontime_Shipped_Ontime) As Commit_Ontime_Shipped_
+Ontime,
        Sum(OS.Released_Late_Shipped_Ontime) As Released_Late_Shipped_
+Ontime,
        Max(OS.Commit_Late_Shipped_Ontime) As Commit_Late_Shipped_Onti
+me,
        Sum(OS.OTIF_Qty_Shipped_Ontime) As OTIF_Qty_Shipped_Ontime,
        Max(OS.SHIPPED_ONTIME_SFR_QTY) As SHIPPED_ONTIME_SFR_QTY,
     Max((case when OS.COMMIT_ONTIME_DUE_CURR_QTY > OS.SHIPPED_ONTIME_
+SFR_QTY And OS.DELIV_NOTE_CREA_DT Is Null then (OS.COMMIT_ONTIME_DUE_
+CURR_QTY -  OS.SHIPPED_ONTIME_SFR_QTY) else 0 end)) As No_Delivery_no
+te
     From
     (Select * From
/* ********************************************************** Orders *
+******************************************************************* *
+/
          (Select 
          OL.REF_DT As REF_DT,
          OL.SHIP_TO_CUST_ID as SHIP_TO_CUST_ID,
          OL.MATL_ID As MATL_ID,
(Case When OL.PROD_GRP_NBR in ('0082', '0083') Then 'AIRCRAFT RETREADS
+' Else OL.EXT_MATL_GRP_ID End)  As EXT_MATL_GRP_ID,
OL.STK_CLASS_ID  As STK_CLASS_ID,
          OL.QTY_UNIT_MEAS_ID As QTY_UNIT_MEAS_ID,
          OL.FACILITY_ID As FACILITY_ID,
          OL.BRAND_ID As BRAND_ID,
          OL.ORDER_ID As ORDER_ID,
          OL.ORDER_LINE_NBR As ORDER_LINE_NBR,
          Sum(OL.NET_DUE_CFR_QTY) As NET_DUE_CFR_QTY,
          Sum(OL.NET_DUE_SFR_QTY) As NET_DUE_SFR_QTY,
          Sum(OL.NET_DUE_CFR_QTY) - sum(OL.COMMIT_ONTIME_DUE_CURR_QTY)
+ As QTY_COMMIT_LATE_DUE_CUR,
          Sum(OL.COMMIT_ONTIME_DUE_CURR_QTY) As COMMIT_ONTIME_DUE_CURR
+_QTY,
          Sum(OL.SHIPPED_ONTIME_SFR_QTY) As SHIPPED_ONTIME_SFR_QTY,
          Sum(OL.NET_DUE_SFR_QTY - OL.SHIPPED_ONTIME_SFR_QTY) As QTY_S
+HP_LATE
          From
          GDYR_BI_VWS.MCSI_ORD_LN_MONTHLY OL
          Where
          OL.NET_DUE_CURR_QTY > 0 And

(OL.REF_DT = (DATE-1) - EXTRACT(DAY FROM (DATE-1))  OR OL.REF_DT = DAT
+E -1)  And 

(OL.EXT_MATL_GRP_ID = 'TIRE'  or (OL.MKT_GRP_NBR = '0028' and OL.PROD_
+GRP_NBR in ('0082', '0083'))) 
          Group by    1,2,3,4,5,6,7,8,9,10) ORDERS
     LEFT OUTER JOIN
/* ********************************************************** Shipment
+s *******************************************************************
+* */
          (Select 
          DL.ORDER_ID As ORDER_ID1,
          DL.ORDER_LINE_NBR As ORDER_LINE_NBR1,
          DL.DELIV_ID As DELIV_ID,
          DL.DELIV_LINE_NBR As DELIV_LINE_NBR,
          DE.DELIV_NOTE_CREA_DT As DELIV_NOTE_CREA_DT,
          DE.GOODS_ISS_DT As GOODS_ISS_DT,
          DE.DELIV_PRTY_ID As DELIV_PRTY_ID,
          O.DELIV_BLK_IND As DELIV_BLK_IND,
          SCH.REQ_SHIP_DT As REQ_SHIP_DT,
          Sum(DL.QTY_TO_SHIP) As QTY_TO_SHIP,

          Sum((case when DE.DELIV_NOTE_CREA_DT Is Null then OL.SHIPPED
+_ONTIME_SFR_QTY else (case when SCH.REQ_SHIP_DT > DE.DELIV_NOTE_CREA_
+DT and SCH.REQ_SHIP_DT < date-2  then  DL.QTY_TO_SHIP else 0 end ) en
+d)) As Released_Ontime,
      Sum((Case When O.ORDER_DT >= SCH.REQ_SHIP_DT Then DL.QTY_TO_SHIP
+ End)) As Ordered_Late,
          Sum((case when DE.GOODS_ISS_DT - SCH.REQ_SHIP_DT > 1 and DE.
+DELIV_NOTE_CREA_DT < SCH.REQ_SHIP_DT  then DL.QTY_TO_SHIP else 0 end)
+) As Shipped_Late_Qty,
          Released_Ontime - Shipped_Late_Qty As Commit_Ontime_Shipped_
+Ontime,
          Max((case when OL.COMMIT_ONTIME_DUE_CURR_QTY - ((case when D
+E.DELIV_NOTE_CREA_DT Is Null then OL.SHIPPED_ONTIME_SFR_QTY else (cas
+e when SCH.REQ_SHIP_DT > DE.DELIV_NOTE_CREA_DT and SCH.REQ_SHIP_DT < 
+date-2  then  DL.QTY_TO_SHIP else 0 end ) end)) > 0 and DE.DELIV_NOTE
+_CREA_DT >= SCH.REQ_SHIP_DT And DE.GOODS_ISS_DT - SCH.REQ_SHIP_DT <= 
+1 then DL.QTY_TO_SHIP else 0 end)) As Released_Late_Shipped_Ontime,
          Sum((case when OL.COMMIT_ONTIME_DUE_CURR_QTY <= OL.SHIPPED_O
+NTIME_SFR_QTY Then OL.SHIPPED_ONTIME_SFR_QTY - OL.COMMIT_ONTIME_DUE_C
+URR_QTY Else 0 End)) As Commit_Late_Shipped_Ontime,
          Sum(OL.SHIPPED_ONTIME_SFR_QTY) As True_Shipped_Ontime,
          (Commit_Ontime_Shipped_Ontime + Commit_Late_Shipped_Ontime +
+ Released_Late_Shipped_Ontime) As OTIF_Qty_Shipped_Ontime
          From
          GDYR_BI_VWS.ORDERS O,
          GDYR_BI_VWS.MCSI_ORD_LN_MONTHLY OL,
          GDYR_BI_VWS.DELIV_LINE DL,
          GDYR_BI_VWS.DELIV DE,
          GDYR_BI_VWS.SCHED_LINE SCH
          where
          DL.ORDER_ID = OL.ORDER_ID And
          DL.ORDER_LINE_NBR = OL.ORDER_LINE_NBR And
          DE.DELIV_ID = DL.DELIV_ID And
          DE.SBU_ID = DL.SBU_ID And
          DE.SBU_ID = SCH.SBU_ID And
          DE.SBU_ID = O.SBU_ID And
          DE.SBU_ID = 2 And
          DE.BUS_DT = DL.BUS_DT And
          DE.BUS_DT = SCH.BUS_DT And
          DE.BUS_DT = O.BUS_DT And
          DE.BUS_DT = current_date And
          DE.EXP_DT = DL.EXP_DT And
          DE.EXP_DT = SCH.EXP_DT And
          DE.EXP_DT = O.EXP_DT And
          DE.EXP_DT = '5555-12-31' And
          DL.ORDER_ID = SCH.ORDER_ID And
          DL.ORDER_LINE_NBR = SCH.ORDER_LINE_NBR And
          DL.ORDER_ID = O.ORDER_ID And
          OL.NET_DUE_CURR_QTY > 0 And

(OL.REF_DT = (DATE-1) - EXTRACT(DAY FROM (DATE-1))  OR OL.REF_DT = DAT
+E -1)  And 

(OL.EXT_MATL_GRP_ID = 'TIRE'  or (OL.MKT_GRP_NBR = '0028' and OL.PROD_
+GRP_NBR in ('0082', '0083'))) 

          Group by 1,2,3,4,5,6,7,8,9) SHIPMENTS        
/*   *****************************************************************
+***************************************** */
     ON
     ORDERS.ORDER_ID = SHIPMENTS.ORDER_ID1  And
     ORDERS.ORDER_LINE_NBR = SHIPMENTS.ORDER_LINE_NBR1)  OS
     group by 1,2,3,4,5,6,7,8,9,10,11) OVER_ALL,
GDYR_BI_VWS.MATL MAT
Where 
OVER_ALL.MATL_ID = MAT.MATL_ID
and MAT.BUS_DT = Date
And MAT.SBU_ID = 2
And MAT.EXP_DT = '5555-12-31'
Group by 1,2,3,4,5,6,7,8,9,10
) Main

/*  ********************************** For Test Only *****************
+*************************************************
And OL.SHIP_TO_CUST_ID = '0000600243'

And OL.MATL_ID = '000000000000117074'

OWN_CUST_ID = '00A0006024' And
OL.SHIP_TO_CUST_ID = '0000130408' And
OL.MATL_ID = '000000000000003305' And

OL.REF_DT in ('2003-01-31', '2003-02-28', '2003-03-31', '2003-04-30', 
+'2003-05-31', '2003-06-30', '2003-07-31', '2003-08-31', '2003-09-30',
+ '2003-10-31', '2003-11-30', '2003-12-31', '2004-01-31', '2004-02-29'
+, '2004-03-31', '2004-04-30', '2004-05-31', '2004-06-30', '2004-07-14
+')  And

OL.REF_DT in ('2004-05-31', '2004-06-30')  And

OL.REF_DT = '2004-11-30' And
OWN_CUST_ID = '00A0006024' And

(OL.REF_DT = (DATE-1) - EXTRACT(DAY FROM (DATE-1))  OR OL.REF_DT = DAT
+E -1)  And 
OWN_CUST_ID in ('00A0003047', '00A0006024', '00A0007029', '00A0008051'
+) And
********************************** For Test Only *********************
+********************************************* */ 
";
 my $parser = SQL::Parser->new();
 $parser->{RaiseError}=1;
 $parser->{PrintError}=0;
# $parser->parse("LOAD 'MyLib::MySyntax' ");
 my $stmt = SQL::Statement->new($sql,$parser);
 printf "Command             %s\n",$stmt->command;
 printf "Num of Placeholders %s\n",scalar $stmt->params;
 printf "Columns             %s\n",join',',map{$_->name}$stmt->columns
+;
 printf "Tables              %s\n",join',',$stmt->tables;
 printf "Where operator      %s\n",join',',$stmt->where->op;
 printf "Limit               %s\n",$stmt->limit;
 printf "Offset              %s\n",$stmt->offset;
 printf "Order Columns       %s\n",join',',map{$_->column}$stmt->order
+;
 __END__
[download]