LOY_AGGR_PT_RDM

Details

Schema: FUSION
Object owner: LOY
Object type: VIEW

Columns

Name
PROGRAM_ID MEMBER_ID PROMO_ID ATTRIB_DEFN_ID PARTNER_ID PROD_ID POINTS_REDEEMED

Name

PROGRAM_ID

MEMBER_ID

PROMO_ID

ATTRIB_DEFN_ID

PARTNER_ID

PROD_ID

POINTS_REDEEMED

Query

SQL_Statement
SELECT COALESCE(ALL_RDM.PROGRAM_ID, RDM_CANC.PROGRAM_ID) PROGRAM_ID, COALESCE(ALL_RDM.MEMBER_ID, RDM_CANC.MEMBER_ID) MEMBER_ID, COALESCE(ALL_RDM.PROMO_ID, RDM_CANC.PROMO_ID) PROMO_ID, COALESCE(ALL_RDM.ATTRIB_DEFN_ID, RDM_CANC.ATTRIB_DEFN_ID) ATTRIB_DEFN_ID, COALESCE(ALL_RDM.PARTNER_ID, RDM_CANC.PARTNER_ID) PARTNER_ID, COALESCE(ALL_RDM.PROD_ID, RDM_CANC.PROD_ID) PROD_ID, NVL(ALL_RDM.ALL_RDMS,0) - NVL(RDM_CANC.RDM_CNCS,0) POINTS_REDEEMED FROM ( SELECT MEM.PROGRAM_ID, RDM.MEMBER_ID, RDM.PROMO_ID, RDM.ATTRB_DEFN_ID ATTRIB_DEFN_ID, TXN.PARTNER_ID, TXN.PROD_ID, SUM(VALUE) ALL_RDMS FROM LOY_RDM_ITM RDM LEFT JOIN LOY_TXN TXN ON RDM.TXN_ID = TXN.LOY_TXN_ID INNER JOIN LOY_MEMBER MEM ON RDM.MEMBER_ID = MEM.LOY_MEMBER_ID WHERE RDM.CREATION_DATE >= COALESCE( ( SELECT PERIOD_END_DATE +1 FROM LOY_AGGR_POINT_PERIOD WHERE PERIOD_TYPE = 'W' AND RECENT_FLAG = 'Y'), (select LOY_POINT_AGGREGATION.GET_BEGINNING_DATE from dual), SYSDATE ) AND RDM.TYPE_CODE <> 'ORA_POINT_EXP' AND (TXN.LOY_TXN_ID IS NULL OR TXN.SUB_TYPE_CODE <> 'ORA_RED_CNCL') GROUP BY MEM.PROGRAM_ID, RDM.MEMBER_ID, RDM.PROMO_ID, RDM.ATTRB_DEFN_ID, TXN.PARTNER_ID, TXN.PROD_ID ) ALL_RDM FULL OUTER JOIN ( SELECT MEM.PROGRAM_ID, ACRL.MEMBER_ID, ACRL.PROMO_ID, ACRL.ATTRIB_DEFN_ID, TXN.PARTNER_ID, TXN.PROD_ID, SUM(ACCRUALED_VALUE) RDM_CNCS FROM LOY_ACRL_ITM ACRL INNER JOIN LOY_TXN TXN ON ACRL.TXN_ID = TXN.LOY_TXN_ID INNER JOIN LOY_MEMBER MEM ON ACRL.MEMBER_ID = MEM.LOY_MEMBER_ID INNER JOIN LOY_TXN CANCELTXN ON CANCELTXN.LOY_TXN_ID = TXN.CANCELLED_TXN_ID WHERE ACRL.PROCESS_DT >= COALESCE( ( SELECT PERIOD_END_DATE +1 FROM LOY_AGGR_POINT_PERIOD WHERE PERIOD_TYPE = 'W' AND RECENT_FLAG = 'Y'), (select LOY_POINT_AGGREGATION.GET_BEGINNING_DATE from dual), SYSDATE ) AND TXN.SUB_TYPE_CODE = 'ORA_ACC_CNCL' AND CANCELTXN.SUB_TYPE_CODE <> 'ORA_RED_CNCL' GROUP BY MEM.PROGRAM_ID, ACRL.MEMBER_ID, ACRL.PROMO_ID, ACRL.ATTRIB_DEFN_ID, TXN.PARTNER_ID, TXN.PROD_ID ) RDM_CANC ON ALL_RDM.PROGRAM_ID = RDM_CANC.PROGRAM_ID AND ALL_RDM.MEMBER_ID = RDM_CANC.MEMBER_ID AND ALL_RDM.PROMO_ID = RDM_CANC.PROMO_ID AND ALL_RDM.ATTRIB_DEFN_ID = RDM_CANC.ATTRIB_DEFN_ID AND ALL_RDM.PROD_ID = RDM_CANC.PROD_ID UNION ALL SELECT MEM.PROGRAM_ID, MEMBER_ID, PROMO_ID, ATTRDEFN_B_ID, PARTNER_ID, PROD_ID, POINTS_REDEEMED FROM LOY_AGGR_POINT_PERIOD PRD JOIN LOY_AGGR_POINT PT ON (PRD.AGGR_PERIOD_TYPE = PT.PERIOD_TYPE AND PRD.AGGR_PERIOD_CODE = PT.PERIOD_CODE) JOIN LOY_MEMBER MEM ON (MEM.LOY_MEMBER_ID = PT.MEMBER_ID) WHERE PRD.PERIOD_TYPE = 'W' AND PRD.RECENT_FLAG = 'Y' AND PT.POINTS_REDEEMED IS NOT NULL

SQL_Statement

SELECT COALESCE(ALL_RDM.PROGRAM_ID, RDM_CANC.PROGRAM_ID) PROGRAM_ID,

COALESCE(ALL_RDM.MEMBER_ID, RDM_CANC.MEMBER_ID) MEMBER_ID,

COALESCE(ALL_RDM.PROMO_ID, RDM_CANC.PROMO_ID) PROMO_ID,

COALESCE(ALL_RDM.ATTRIB_DEFN_ID, RDM_CANC.ATTRIB_DEFN_ID) ATTRIB_DEFN_ID,

COALESCE(ALL_RDM.PARTNER_ID, RDM_CANC.PARTNER_ID) PARTNER_ID,

COALESCE(ALL_RDM.PROD_ID, RDM_CANC.PROD_ID) PROD_ID,

NVL(ALL_RDM.ALL_RDMS,0) - NVL(RDM_CANC.RDM_CNCS,0) POINTS_REDEEMED

FROM

(

SELECT MEM.PROGRAM_ID,

RDM.MEMBER_ID,

RDM.PROMO_ID,

RDM.ATTRB_DEFN_ID ATTRIB_DEFN_ID,

TXN.PARTNER_ID,

TXN.PROD_ID,

SUM(VALUE) ALL_RDMS

FROM LOY_RDM_ITM RDM

LEFT JOIN LOY_TXN TXN

ON RDM.TXN_ID = TXN.LOY_TXN_ID

INNER JOIN LOY_MEMBER MEM

ON RDM.MEMBER_ID = MEM.LOY_MEMBER_ID

WHERE RDM.CREATION_DATE >= COALESCE( ( SELECT PERIOD_END_DATE +1 FROM LOY_AGGR_POINT_PERIOD WHERE PERIOD_TYPE = 'W' AND RECENT_FLAG = 'Y'),

(select LOY_POINT_AGGREGATION.GET_BEGINNING_DATE from dual), SYSDATE )

AND RDM.TYPE_CODE <> 'ORA_POINT_EXP'

AND (TXN.LOY_TXN_ID IS NULL OR TXN.SUB_TYPE_CODE <> 'ORA_RED_CNCL')

GROUP BY

MEM.PROGRAM_ID,

RDM.MEMBER_ID,

RDM.PROMO_ID,

RDM.ATTRB_DEFN_ID,

TXN.PARTNER_ID,

TXN.PROD_ID

) ALL_RDM

FULL OUTER JOIN

(

SELECT

MEM.PROGRAM_ID,

ACRL.MEMBER_ID,

ACRL.PROMO_ID,

ACRL.ATTRIB_DEFN_ID,

TXN.PARTNER_ID,

TXN.PROD_ID,

SUM(ACCRUALED_VALUE) RDM_CNCS

FROM LOY_ACRL_ITM ACRL

INNER JOIN LOY_TXN TXN

ON ACRL.TXN_ID = TXN.LOY_TXN_ID

INNER JOIN LOY_MEMBER MEM

ON ACRL.MEMBER_ID = MEM.LOY_MEMBER_ID

INNER JOIN LOY_TXN CANCELTXN

ON CANCELTXN.LOY_TXN_ID = TXN.CANCELLED_TXN_ID

WHERE ACRL.PROCESS_DT >= COALESCE( ( SELECT PERIOD_END_DATE +1 FROM LOY_AGGR_POINT_PERIOD WHERE PERIOD_TYPE = 'W' AND RECENT_FLAG = 'Y'),

(select LOY_POINT_AGGREGATION.GET_BEGINNING_DATE from dual), SYSDATE )

AND TXN.SUB_TYPE_CODE = 'ORA_ACC_CNCL'

AND CANCELTXN.SUB_TYPE_CODE <> 'ORA_RED_CNCL'

GROUP BY

MEM.PROGRAM_ID,

ACRL.MEMBER_ID,

ACRL.PROMO_ID,

ACRL.ATTRIB_DEFN_ID,

TXN.PARTNER_ID,

TXN.PROD_ID

) RDM_CANC

ON ALL_RDM.PROGRAM_ID = RDM_CANC.PROGRAM_ID

AND ALL_RDM.MEMBER_ID = RDM_CANC.MEMBER_ID

AND ALL_RDM.PROMO_ID = RDM_CANC.PROMO_ID

AND ALL_RDM.ATTRIB_DEFN_ID = RDM_CANC.ATTRIB_DEFN_ID

AND ALL_RDM.PROD_ID = RDM_CANC.PROD_ID

UNION ALL

SELECT MEM.PROGRAM_ID,

MEMBER_ID,

PROMO_ID,

ATTRDEFN_B_ID,

PARTNER_ID,

PROD_ID,

POINTS_REDEEMED

FROM LOY_AGGR_POINT_PERIOD PRD JOIN LOY_AGGR_POINT PT

ON (PRD.AGGR_PERIOD_TYPE = PT.PERIOD_TYPE AND PRD.AGGR_PERIOD_CODE = PT.PERIOD_CODE) JOIN LOY_MEMBER MEM

ON (MEM.LOY_MEMBER_ID = PT.MEMBER_ID)

WHERE PRD.PERIOD_TYPE = 'W' AND PRD.RECENT_FLAG = 'Y' AND PT.POINTS_REDEEMED IS NOT NULL