LOY_AGGR_PT_EXP_V

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_EXPIRED

Query

SQL_Statement

SELECT MEM.PROGRAM_ID, ACRL.MEMBER_ID, ACRL.PROMO_ID, ACRL.ATTRIB_DEFN_ID, TXN.PARTNER_ID, TXN.PROD_ID, NVL(SUM( CASE WHEN ACRL.STATUS_CODE <> 'Expired' AND ACRL.STATUS_CODE <> 'ORA_LOY_EXPIRED' THEN ACCRUALED_VALUE - nvl(USED_VALUE,0) ELSE EXPIRED_VALUE END ),0) POINTS_EXPIRED FROM LOY_ACRL_ITM ACRL LEFT 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 WHERE ACRL.EXPIRATION_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 ACRL.EXPIRATION_DT <= SYSDATE GROUP BY MEM.PROGRAM_ID, ACRL.MEMBER_ID, ACRL.PROMO_ID, ACRL.ATTRIB_DEFN_ID, TXN.PARTNER_ID, TXN.PROD_ID UNION ALL SELECT MEM.PROGRAM_ID, MEMBER_ID, PROMO_ID, ATTRDEFN_B_ID, PARTNER_ID, PROD_ID, POINTS_EXPIRED 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_EXPIRED IS NOT NULL