LOY_AGGR_PT_BAL_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 BALANCE

Query

SQL_Statement

SELECT COALESCE(AI.PROGRAM_ID, RI.PROGRAM_ID) PROGRAM_ID, COALESCE(AI.MEMBER_ID, RI.MEMBER_ID) MEMBER_ID, COALESCE(AI.PROMO_ID,RI.PROMO_ID) PROMO_ID, COALESCE(AI.ATTRIB_DEFN_ID,RI.ATTRB_DEFN_ID) ATTRIB_DEFN_ID, COALESCE(AI.PARTNER_ID,RI.PARTNER_ID) PARTNER_ID, COALESCE(AI.PROD_ID,RI.PROD_ID) PROD_ID, COALESCE(AI.ACCSUM,0) - COALESCE((RI.S),0) Balance from ( SELECT MEM.PROGRAM_ID, A.MEMBER_ID, A.ATTRIB_DEFN_ID, B.PARTNER_ID, B.PROD_ID, A.PROMO_ID, COALESCE(SUM( CASE WHEN (A.EXPIRATION_DT >= sysdate or A.EXPIRATION_DT IS NULL) THEN A.ACCRUALED_VALUE ELSE DECODE(EXPIRED_VALUE, 0, NVL(A.USED_VALUE,0), A.ACCRUALED_VALUE - A.EXPIRED_VALUE) END ),0) ACCSUM FROM LOY_ACRL_ITM A INNER JOIN LOY_MEMBER MEM ON (A.MEMBER_ID = MEM.LOY_MEMBER_ID) LEFT JOIN LOY_TXN B ON (A.TXN_ID = B.LOY_TXN_ID) WHERE (A.RET_EFF_DT IS NULL OR A.RET_EFF_DT <= sysdate) GROUP BY MEM.PROGRAM_ID,A.ATTRIB_DEFN_ID, A.MEMBER_ID, B.PARTNER_ID, B.PROD_ID, A.PROMO_ID ) AI FULL OUTER JOIN ( select COALESCE(SUM(RDM.VALUE),0) S, MEM.PROGRAM_ID, RDM.ATTRB_DEFN_ID, RDM.MEMBER_ID, B.PARTNER_ID, B.PROD_ID, RDM.PROMO_ID from LOY_RDM_ITM RDM INNER JOIN LOY_MEMBER MEM ON (RDM.MEMBER_ID = MEM.LOY_MEMBER_ID) LEFT JOIN LOY_TXN B ON (RDM.TXN_ID = B.LOY_TXN_ID) where RDM.CREATION_DATE <= sysdate and RDM.type_code <> 'ORA_POINT_EXP' group by MEM.PROGRAM_ID, RDM.ATTRB_DEFN_ID, RDM.MEMBER_ID, B.PARTNER_ID, B.PROD_ID, RDM.PROMO_ID ) RI ON ( AI.PROGRAM_ID = RI.PROGRAM_ID AND AI.ATTRIB_DEFN_ID = RI.ATTRB_DEFN_ID AND AI.MEMBER_ID = RI.MEMBER_ID AND AI.PARTNER_ID = RI.PARTNER_ID AND AI.PROD_ID = RI.PROD_ID AND AI.PROMO_ID = RI.PROMO_ID)