Home   Home

Only In The Eyes of the Beholder

by James Zealy

select
mk.market_cd,
mk.market_desc,
mk.lmg_pgm_desc,
mk.supp_short_name,
bi.inventory_cnt,
ei.phys_cnt,
sr.tot_qty_rcvd,
vg.totnbr_ng,
vg.totnbr_pck_ng,
RG.TOTREP_NBR_PCK,
eh.pt_hrs,
nv.cnt_venue,
vg.totnbr_pk_drop,
vg.totnbr_pck_pd,
vg.totnbr_bs,
vg.totnbr_pck_bs,
vg.totnbr_pck_samp,
ad.adj_qty_sign
from
(SELECT
SUPP_LMG_PGM.SUPPLIER_ID,
SUPP_LMG_PGM.LMG_PGM_ID,
SUPP_LMG_PGM.MARKET_CD,
MARKET.MARKET_DESC,
LMG_PGM.LMG_PGM_DESC,
EMD_SUPPLIER.SUPP_SHORT_NAME
FROM
SDBVENUE.MARKET MARKET INNER JOIN SDBVENUE.SUPP_LMG_PGM SUPP_LMG_PGM ON MARKET.MARKET_CD = SUPP_LMG_PGM.MARKET_CD
INNER JOIN SDBVENUE.LMG_PGM LMG_PGM ON SUPP_LMG_PGM.LMG_PGM_ID = LMG_PGM.LMG_PGM_ID AND
SUPP_LMG_PGM.SUPPLIER_ID = LMG_PGM.SUPPLIER
INNER JOIN SDBVENUE.EMD_SUPPLIER EMD_SUPPLIER ON LMG_PGM.SUPPLIER = EMD_SUPPLIER.SUPPLIER_ID
WHERE
SUPP_LMG_PGM.SUPPLIER_ID = {?Supplier} AND
SUPP_LMG_PGM.LMG_PGM_ID = {?Program}
AND MARKET.RESTRICT_CPN_IND <> 'C') mk
left outer join
(SELECT
GRATIS_PIT_INV.MARKET_CD,
GRATIS_PIT_INV.LMG_PGM_ID,
GRATIS_PIT_INV.SUPPLIER,
GRATIS_PIT_INV.PERIOD_ENDING_DT,
sum(GRATIS_PIT_INV.INVENTORY_CNT) inventory_cnt,
MARKET.MARKET_DESC
FROM
SDBVENUE.GRATIS_PIT_INV GRATIS_PIT_INV
JOIN
SDBVENUE.ITEM ITEM
ON
GRATIS_PIT_INV.ITEM_NUM = ITEM.ITEM_NUM
JOIN
SDBVENUE.GROUP GRP
ON
ITEM.GROUP_CD = GRP.GROUP_CD
JOIN
SDBVENUE.GROUP_TYPE GROUP_TYPE
ON
GRP.GROUP_TYPE_CD = GROUP_TYPE.GROUP_TYPE_CD
JOIN
SDBVENUE.MARKET MARKET
ON
GRATIS_PIT_INV.MARKET_CD = MARKET.MARKET_CD
WHERE
GRATIS_PIT_INV.LMG_PGM_ID = {?Program} AND
GRATIS_PIT_INV.SUPPLIER = {?Supplier} AND
GROUP_TYPE.GROUP_TYPE_CD = 2 AND
ITEM.ITEM_TYPE_CD <> 6 AND
GRATIS_PIT_INV.PERIOD_ENDING_DT >= date({?grdate}) - dayofyear(date({?grdate}) - 1 day) days AND
DATE(GRATIS_PIT_INV.PERIOD_ENDING_DT) < date({?grdate}) - dayofyear(date({?grdate}) -8 days) days
group by
GRATIS_PIT_INV.MARKET_CD,
GRATIS_PIT_INV.LMG_PGM_ID,
GRATIS_PIT_INV.SUPPLIER,
GRATIS_PIT_INV.PERIOD_ENDING_DT,
MARKET.MARKET_DESC) bi
on mk.market_cd=bi.market_cd
left join
(SELECT
GRATIS_PIT_INV.MARKET_CD,
GRATIS_PIT_INV.LMG_PGM_ID,
GRATIS_PIT_INV.SUPPLIER,
GRATIS_PIT_INV.PERIOD_ENDING_DT,
sum(GRATIS_PIT_INV.INVENTORY_CNT) phys_cnt,
MARKET.MARKET_DESC
FROM
SDBVENUE.GRATIS_PIT_INV GRATIS_PIT_INV
JOIN
SDBVENUE.ITEM ITEM
ON
GRATIS_PIT_INV.ITEM_NUM = ITEM.ITEM_NUM
JOIN
SDBVENUE.GROUP GRP
ON
ITEM.GROUP_CD = GRP.GROUP_CD
JOIN
SDBVENUE.GROUP_TYPE GROUP_TYPE
ON
GRP.GROUP_TYPE_CD = GROUP_TYPE.GROUP_TYPE_CD
JOIN
SDBVENUE.MARKET MARKET
ON
GRATIS_PIT_INV.MARKET_CD = MARKET.MARKET_CD
WHERE
GRATIS_PIT_INV.LMG_PGM_ID = {?Program} AND
GRATIS_PIT_INV.SUPPLIER = {?Supplier} AND
GROUP_TYPE.GROUP_TYPE_CD = 2 AND
ITEM.ITEM_TYPE_CD <> 6 AND
GRATIS_PIT_INV.PERIOD_ENDING_DT > date({?grdate})-7 days AND
DATE(GRATIS_PIT_INV.PERIOD_ENDING_DT) <= {?grdate}
group by
GRATIS_PIT_INV.MARKET_CD,
GRATIS_PIT_INV.LMG_PGM_ID,
GRATIS_PIT_INV.PERIOD_ENDING_DT,
GRATIS_PIT_INV.SUPPLIER,
MARKET.MARKET_DESC) ei
on mk.market_cd=ei.market_cd
left join
(SELECT
GRATIS_SHIPMT_RCV.MARKET_CD,
GRATIS_SHIPMT_RCV.LMG_PGM_ID,
GRATIS_SHIPMT_RCV.SUPPLIER,
sum(GRATIS_SHIPMT_RCV.QTY_RECEIVED) tot_qty_rcvd,
ITEM.ITEM_TYPE_CD
FROM
SDBVENUE.GRATIS_SHIPMT_RCV GRATIS_SHIPMT_RCV
JOIN
SDBVENUE.ITEM ITEM
ON
GRATIS_SHIPMT_RCV.ITEM_NUM = ITEM.ITEM_NUM
WHERE
GRATIS_SHIPMT_RCV.PERIOD_ENDING_DT >
date(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01') +
(case
when dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')<= 3
then 3-dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')
else 10-dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')
end) days
AND GRATIS_SHIPMT_RCV.PERIOD_ENDING_DT <= {?grdate} and
ITEM.ITEM_TYPE_CD <> 6 AND
GRATIS_SHIPMT_RCV.LMG_PGM_ID = {?Program} AND
GRATIS_SHIPMT_RCV.SUPPLIER = {?Supplier}
group by
GRATIS_SHIPMT_RCV.MARKET_CD,
GRATIS_SHIPMT_RCV.LMG_PGM_ID,
GRATIS_SHIPMT_RCV.SUPPLIER,
ITEM.ITEM_TYPE_CD ) sr
on mk.market_cd=sr.market_cd
left join
(SELECT
VENUE_GRATIS.MARKET_CD,
VENUE_GRATIS.LMG_PGM_ID,
VENUE_GRATIS.SUPPLIER,
sum(VENUE_GRATIS.NBR_NAME_GEN) totnbr_ng,
sum(VENUE_GRATIS.NBR_PCK_NAME_GEN) totnbr_pck_ng,
sum(VENUE_GRATIS.NBR_PACK_DROP) totnbr_pk_drop,
sum(VENUE_GRATIS.NBR_PCK_PACK_DROP) totnbr_pck_pd,
sum(VENUE_GRATIS.NBR_BARSTAFF) totnbr_bs,
sum(VENUE_GRATIS.NBR_PCK_BARSTAFF) totnbr_pck_bs,
sum(VENUE_GRATIS.NBR_PCK_STICK_SAMP) totnbr_pck_samp,
ITEM.ITEM_TYPE_CD
FROM
SDBVENUE.VENUE_GRATIS VENUE_GRATIS
JOIN
SDBVENUE.ITEM ITEM
ON
VENUE_GRATIS.ITEM_NUM = ITEM.ITEM_NUM
WHERE
VENUE_GRATIS.VISIT_DATE >
date(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01') +
(case
when dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')<= 3
then 3-dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')
else 10-dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')
end) days
AND VENUE_GRATIS.VISIT_DATE <= {?grdate} and
ITEM.ITEM_TYPE_CD <> 6 AND
VENUE_GRATIS.LMG_PGM_ID = {?Program} AND
VENUE_GRATIS.SUPPLIER = {?Supplier}
group by
VENUE_GRATIS.MARKET_CD,
VENUE_GRATIS.LMG_PGM_ID,
VENUE_GRATIS.SUPPLIER,
ITEM.ITEM_TYPE_CD) vg
on mk.market_cd=vg.market_cd
LEFT JOIN
(SELECT
REP_GRATIS.MARKET_CD,
REP_GRATIS.LMG_PGM_ID,
REP_GRATIS.SUPPLIER,
SUM(REP_GRATIS.NBR_PCK) TOTREP_NBR_PCK,
ITEM.ITEM_TYPE_CD
FROM
SDBVENUE.REP_GRATIS REP_GRATIS
JOIN
SDBVENUE.ITEM ITEM
ON
REP_GRATIS.ITEM_NUM = ITEM.ITEM_NUM
WHERE
REP_GRATIS.VISIT_DT >
date(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01') +
(case
when dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')<= 3
then 3-dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')
else 10-dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')
end) days
AND REP_GRATIS.VISIT_DT <= {?grdate} and
ITEM.ITEM_TYPE_CD <> 6 AND
REP_GRATIS.LMG_PGM_ID = {?Program} AND
REP_GRATIS.SUPPLIER = {?sUPPLIER}
group by
REP_GRATIS.MARKET_CD,
REP_GRATIS.LMG_PGM_ID,
REP_GRATIS.SUPPLIER,
ITEM.ITEM_TYPE_CD
) rg
ON mk.market_cd=rg.market_cd
left join
(SELECT
GRATIS_ADJUSTMNT.MARKET_CD,
GRATIS_ADJUSTMNT.LMG_PGM_ID,
GRATIS_ADJUSTMNT.SUPPLIER,
sum( case
when ADJUSTMENT_REASON.ADJ_OPERAND = '-'
then -1 * GRATIS_ADJUSTMNT.ADJ_QTY
else GRATIS_ADJUSTMNT.ADJ_QTY
end) adj_qty_sign,
ITEM.ITEM_TYPE_CD
FROM
SDBVENUE.GRATIS_ADJUSTMNT GRATIS_ADJUSTMNT
JOIN
SDBVENUE.ADJUSTMENT_REASON ADJUSTMENT_REASON
ON
GRATIS_ADJUSTMNT.ADJ_REASON_CD = ADJUSTMENT_REASON.ADJ_REASON_CD
JOIN
SDBVENUE.ITEM ITEM
ON
GRATIS_ADJUSTMNT.ITEM_NUM = ITEM.ITEM_NUM
WHERE
GRATIS_ADJUSTMNT.PERIOD_ENDING_DT >
date(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01') +
(case
when dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')<= 3
then 3-dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')
else 10-dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')
end) days
AND GRATIS_ADJUSTMNT.PERIOD_ENDING_DT <= {?grdate} and
ITEM.ITEM_TYPE_CD <> 6 AND
GRATIS_ADJUSTMNT.LMG_PGM_ID = {?Program} AND
GRATIS_ADJUSTMNT.SUPPLIER = {?Supplier}
group by
GRATIS_ADJUSTMNT.MARKET_CD,
GRATIS_ADJUSTMNT.LMG_PGM_ID,
GRATIS_ADJUSTMNT.SUPPLIER,
ITEM.ITEM_TYPE_CD) ad
on mk.market_cd=ad.market_cd
left join
(SELECT
EMP_HOURS.MARKET_CD,
EMP_HOURS.LMG_PGM,
EMP_HOURS.SUPPLIER,
SUM (EMP_HOURS.TOTAL_HOURS) as pt_hrs
FROM
SDBVENUE.EMP_HOURS EMP_HOURS
WHERE
EMP_HOURS.LMG_PGM = {?Program} AND
EMP_HOURS.SUPPLIER = {?Supplier} and
EMP_HOURS.VISIT_DT >
date(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01') +
(case
when dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')<= 3
then 3-dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')
else 10-dayofweek(substr(digits(year(date({?grdate}))),7,4)||'-01'||'-01')
end) days
AND EMP_HOURS.VISIT_DT <= {?grdate}
GROUP BY
EMP_HOURS.MARKET_CD,
EMP_HOURS.LMG_PGM,
EMP_HOURS.SUPPLIER) eh
on mk.market_cd=eh.market_cd
left join
(SELECT
PROFILE.MARKET_CD,
COUNT (PROFILE.VENUE_ID) as cnt_venue
FROM
SDBVENUE.PROFILE PROFILE
INNER JOIN SDBVENUE.PROFILE_LMG_PGM PROFILE_LMG_PGM
ON PROFILE.VENUE_ID = PROFILE_LMG_PGM.VENUE_ID
WHERE
PROFILE_LMG_PGM.LMG_PGM_ID = {?Program} AND
PROFILE_LMG_PGM.SUPPLIER = {?Supplier} AND
PROFILE_LMG_PGM.SIGNUP_STATUS_CODE = 2 AND
PROFILE_LMG_PGM.ROW_STATUS = 'A'
GROUP BY
PROFILE.MARKET_CD) nv
on mk.market_cd=nv.market_cd

10/24/2006

Author's Note: Sometimes beauty is only in the Eyes of the beholder.

Posted on 10/24/2006
Copyright © 2024 James Zealy

Return to the Previous Page
 

pathetic.org Version 7.3.2 May 2004 Terms and Conditions of Use 0 member(s) and 2 visitor(s) online
All works Copyright © 2024 their respective authors. Page Generated In 1 Second(s)