Product: | CSC - Customer Care |
---|---|
Description: | This view is used to perfom serarch operation on the plans module. |
Implementation/DBA Data: | APPS.CSC_PLAN_SEARCH_V |
SELECT HZP.PARTY_ID PARTY_ID
, CUP.PLAN_ID PLAN_ID
, CUP.PLAN_NAME PLAN_NAME
, HZP.PARTY_NAME PARTY_NAME
, HZP.PARTY_NUMBER PARTY_NUMBER
, HZCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, HZCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, HZCA.ACCOUNT_NAME ACCOUNT_NAME
, CUP.PLAN_STATUS_CODE PLAN_STATUS_CODE
, CUP.PLAN_STATUS_MEANING PLAN_STATUS
, CUP.START_DATE_ACTIVE START_DATE_ACTIVE
, CUP.END_DATE_ACTIVE END_DATE_ACTIVE
, CUP.PLAN_GROUP_CODE PLAN_GROUP_CODE
, CUP.GROUP_NAME GROUP_NAME
, CUP.USE_FOR_CUST_ACCOUNT USE_FOR_CUST_ACCOUNT
, CUP.END_USER_TYPE END_USER_TYPE
, CUP.END_USER_TYPE_MEANING END_USER_TYPE_MEANING
, CUP.CUSTOMIZED_PLAN CUSTOMIZED_PLAN
FROM HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
, CSC_CUST_PLANS_V CUP
WHERE HZCA.PARTY_ID = CUP.PARTY_ID (+) AND HZCA.CUST_ACCOUNT_ID = CUP.CUST_ACCOUNT_ID (+)
AND HZP.PARTY_ID = HZCA.PARTY_ID (+)
AND HZP.STATUS = 'A' UNION SELECT HZP.PARTY_ID PARTY_ID
, CUP.PLAN_ID PLAN_ID
, CUP.PLAN_NAME PLAN_NAME
, HZP.PARTY_NAME PARTY_NAME
, HZP.PARTY_NUMBER PARTY_NUMBER
, HZCA.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, HZCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, HZCA.ACCOUNT_NAME ACCOUNT_NAME
, CUP.PLAN_STATUS_CODE PLAN_STATUS_CODE
, CUP.PLAN_STATUS_MEANING PLAN_STATUS
, CUP.START_DATE_ACTIVE START_DATE_ACTIVE
, CUP.END_DATE_ACTIVE END_DATE_ACTIVE
, CUP.PLAN_GROUP_CODE PLAN_GROUP_CODE
, CUP.GROUP_NAME GROUP_NAME
, CUP.USE_FOR_CUST_ACCOUNT USE_FOR_CUST_ACCOUNT
, CUP.END_USER_TYPE END_USER_TYPE
, CUP.END_USER_TYPE_MEANING END_USER_TYPE_MEANING
, CUP.CUSTOMIZED_PLAN CUSTOMIZED_PLAN
FROM HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
, CSC_CUST_PLANS_V CUP
WHERE HZP.PARTY_ID = CUP.PARTY_ID (+) AND CUP.PARTY_ID = HZCA.PARTY_ID (+)
AND CUP.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID (+)
AND HZP.STATUS = 'A'