ZSP_ODM_REVN

Details

Schema: FUSION
Object owner: ZSP
Object type: VIEW

Columns

Name
REVN_WID SALES_ACCOUNT_ID CUSTOMER_WID INTEGRATION_ID PROD_WID INVENTORY_ITEM_ID INVENTORY_ORG_ID PROD_GROUP_ID CURR_SSTAGE_WID BUDGETED_FLG CHAMPION_FLG CHANNEL_TYPE SALES_METHOD REVENUE SALE_PRICE TIME_TO_CLOSE REVN_CLOSED_DT STATUS_CATEGORY STATUS_CD OPTY_SOURCE SALES_CREDIT_TYP REVN_CREATION_DT REVN_STATUS PRIMARY_FLAG OPTY_ID CRM_CONVERSION_RATE SPLIT_TYPE_CODE RECUR_TYPE_CODE OPTY_SALES_STG OPTY_SALES_METHOD OPTY_SALES_STG_STATUS REVN_CLOSED_OPTY_LINE_REVN OPTY_OPEN_DT OPTY_CLOSED_DT

Name

REVN_WID

SALES_ACCOUNT_ID

CUSTOMER_WID

INTEGRATION_ID

PROD_WID

INVENTORY_ITEM_ID

INVENTORY_ORG_ID

PROD_GROUP_ID

CURR_SSTAGE_WID

BUDGETED_FLG

CHAMPION_FLG

CHANNEL_TYPE

SALES_METHOD

REVENUE

SALE_PRICE

TIME_TO_CLOSE

REVN_CLOSED_DT

STATUS_CATEGORY

STATUS_CD

OPTY_SOURCE

SALES_CREDIT_TYP

REVN_CREATION_DT

REVN_STATUS

PRIMARY_FLAG

OPTY_ID

CRM_CONVERSION_RATE

SPLIT_TYPE_CODE

RECUR_TYPE_CODE

OPTY_SALES_STG

OPTY_SALES_METHOD

OPTY_SALES_STG_STATUS

REVN_CLOSED_OPTY_LINE_REVN

OPTY_OPEN_DT

OPTY_CLOSED_DT

Query

SQL_Statement
SELECT r.revn_id revn_wid, r.sales_account_id sales_account_id, r.CUST_PARTY_ID customer_wid, r.revn_id integration_id, CASE WHEN r.INVENTORY_ITEM_ID IS NULL THEN r.prod_group_id ELSE r.INVENTORY_ITEM_ID END prod_wid, r.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, r.INVENTORY_ORG_ID INVENTORY_ORG_ID, r.prod_group_id prod_group_id, o.CURR_STG_ID CURR_SSTAGE_WID, o.BDGTED_FLG BUDGETED_FLG, o.CHAMPION_FLAG CHAMPION_FLG, o.CHANNEL_TYPE_CD CHANNEL_TYPE, D.DESC_TEXT SALES_METHOD, CASE WHEN r.REVN_AMT_CURCY_CODE = NVL(r.CRM_CURCY_CODE,r.REVN_AMT_CURCY_CODE) THEN r.REVN_AMT ELSE r.REVN_AMTNVL(r.CRM_CONVERSION_RATE,1) END revenue, CASE WHEN (r.qty IS NOT NULL AND r.qty > 0) THEN ( CASE WHEN r.REVN_AMT_CURCY_CODE = NVL(r.CRM_CURCY_CODE,r.REVN_AMT_CURCY_CODE) THEN r.REVN_AMT ELSE r.REVN_AMTNVL(r.CRM_CONVERSION_RATE,1) END /r.qty) ELSE NULL END sale_price, CASE WHEN r.ACTUAL_CLOSE_DATE > to_date(TO_CHAR(r.revn_creation_date,'mmddyyyy'),'mm/dd/yyyy') THEN to_number(r.ACTUAL_CLOSE_DATE - to_date(TO_CHAR(r.revn_creation_date,'mmddyyyy'),'mm/dd/yyyy')) ELSE to_number(NULL) END time_to_close, r.ACTUAL_CLOSE_DATE REVN_CLOSED_DT, r.STATUS_CATEGORY STATUS_CATEGORY, o.status_cd STATUS_CD, o.pr_source_code OPTY_SOURCE, r.sales_credit_type_code SALES_CREDIT_TYP, r.revn_creation_date REVN_CREATION_DT, r.status_code REVN_STATUS, r.PRIMARY_FLAG PRIMARY_FLAG, r.OPTY_ID OPTY_ID, r.CRM_CONVERSION_RATE CRM_CONVERSION_RATE, r.SPLIT_TYPE_CODE SPLIT_TYPE_CODE, r.RECUR_TYPE_CODE RECUR_TYPE_CODE, E.NAME OPTY_SALES_STG, D.NAME OPTY_SALES_METHOD, E.STAGE_STATUS_CD OPTY_SALES_STG_STATUS, CASE WHEN r.REVN_AMT_CURCY_CODE = NVL(r.CRM_CURCY_CODE,r.REVN_AMT_CURCY_CODE) THEN r.REVN_AMT ELSE r.REVN_AMT*NVL(r.CRM_CONVERSION_RATE,1) END REVN_CLOSED_OPTY_LINE_REVN, o.opty_creation_date OPTY_OPEN_DT, (SELECT MOO_REVN.ACTUAL_CLOSE_DATE FROM MOO_REVN WHERE REVN_ID =o.SUM_REVN_ID ) OPTY_CLOSED_DT FROM moo_revn r, moo_opty o, MOO_SALES_METHOD_VL D, MOO_STG_VL E WHERE r.opty_id = o.opty_id AND o.SALES_METHOD_ID =D.SALES_METHOD_ID(+) AND o.CURR_STG_ID = E.STG_ID(+) AND (r.INVENTORY_ITEM_ID IS NOT NULL OR r.PROD_GROUP_ID IS NOT NULL) AND r.revn_amt IS NOT NULL AND r.revn_amt <> 0 AND r.PRIMARY_FLAG = 'N' AND r.SALES_CREDIT_TYPE_CODE = 'QUOTA' AND r.OPTY_ID IS NOT NULL AND r.CRM_CONVERSION_RATE IS NOT NULL AND (r.split_type_code IS NULL OR r.SPLIT_TYPE_CODE <> 'PARENTSPLIT') AND (r.recur_type_code IS NULL OR r.RECUR_TYPE_CODE <> 'PARENTRECUR')

SQL_Statement

SELECT r.revn_id revn_wid,

r.sales_account_id sales_account_id,

r.CUST_PARTY_ID customer_wid,

r.revn_id integration_id,

CASE

WHEN r.INVENTORY_ITEM_ID IS NULL

THEN r.prod_group_id

ELSE r.INVENTORY_ITEM_ID

END prod_wid,

r.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,

r.INVENTORY_ORG_ID INVENTORY_ORG_ID,

r.prod_group_id prod_group_id,

o.CURR_STG_ID CURR_SSTAGE_WID,

o.BDGTED_FLG BUDGETED_FLG,

o.CHAMPION_FLAG CHAMPION_FLG,

o.CHANNEL_TYPE_CD CHANNEL_TYPE,

D.DESC_TEXT SALES_METHOD,

CASE

WHEN r.REVN_AMT_CURCY_CODE = NVL(r.CRM_CURCY_CODE,r.REVN_AMT_CURCY_CODE)

THEN r.REVN_AMT

ELSE r.REVN_AMT*NVL(r.CRM_CONVERSION_RATE,1)

END revenue,

CASE

WHEN (r.qty IS NOT NULL

AND r.qty > 0)

THEN (

CASE

WHEN r.REVN_AMT_CURCY_CODE = NVL(r.CRM_CURCY_CODE,r.REVN_AMT_CURCY_CODE)

THEN r.REVN_AMT

ELSE r.REVN_AMT*NVL(r.CRM_CONVERSION_RATE,1)

END /r.qty)

ELSE NULL

END sale_price,

CASE

WHEN r.ACTUAL_CLOSE_DATE > to_date(TO_CHAR(r.revn_creation_date,'mmddyyyy'),'mm/dd/yyyy')

THEN to_number(r.ACTUAL_CLOSE_DATE - to_date(TO_CHAR(r.revn_creation_date,'mmddyyyy'),'mm/dd/yyyy'))

ELSE to_number(NULL)

END time_to_close,

r.ACTUAL_CLOSE_DATE REVN_CLOSED_DT,

r.STATUS_CATEGORY STATUS_CATEGORY,

o.status_cd STATUS_CD,

o.pr_source_code OPTY_SOURCE,

r.sales_credit_type_code SALES_CREDIT_TYP,

r.revn_creation_date REVN_CREATION_DT,

r.status_code REVN_STATUS,

r.PRIMARY_FLAG PRIMARY_FLAG,

r.OPTY_ID OPTY_ID,

r.CRM_CONVERSION_RATE CRM_CONVERSION_RATE,

r.SPLIT_TYPE_CODE SPLIT_TYPE_CODE,

r.RECUR_TYPE_CODE RECUR_TYPE_CODE,

E.NAME OPTY_SALES_STG,

D.NAME OPTY_SALES_METHOD,

E.STAGE_STATUS_CD OPTY_SALES_STG_STATUS,

CASE

WHEN r.REVN_AMT_CURCY_CODE = NVL(r.CRM_CURCY_CODE,r.REVN_AMT_CURCY_CODE)

THEN r.REVN_AMT

ELSE r.REVN_AMT*NVL(r.CRM_CONVERSION_RATE,1)

END REVN_CLOSED_OPTY_LINE_REVN,

o.opty_creation_date OPTY_OPEN_DT,

(SELECT MOO_REVN.ACTUAL_CLOSE_DATE

FROM MOO_REVN

WHERE REVN_ID =o.SUM_REVN_ID

) OPTY_CLOSED_DT

FROM moo_revn r,

moo_opty o,

MOO_SALES_METHOD_VL D,

MOO_STG_VL E

WHERE r.opty_id = o.opty_id

AND o.SALES_METHOD_ID =D.SALES_METHOD_ID(+)

AND o.CURR_STG_ID = E.STG_ID(+)

AND (r.INVENTORY_ITEM_ID IS NOT NULL

OR r.PROD_GROUP_ID IS NOT NULL)

AND r.revn_amt IS NOT NULL

AND r.revn_amt <> 0

AND r.PRIMARY_FLAG = 'N'

AND r.SALES_CREDIT_TYPE_CODE = 'QUOTA'

AND r.OPTY_ID IS NOT NULL

AND r.CRM_CONVERSION_RATE IS NOT NULL

AND (r.split_type_code IS NULL

OR r.SPLIT_TYPE_CODE <> 'PARENTSPLIT')

AND (r.recur_type_code IS NULL

OR r.RECUR_TYPE_CODE <> 'PARENTRECUR')