VIEW: APPS.CSC_PLAN_SEARCH_V

Source

View Text - Preformatted

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, arl.description account_status, 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, ar_lookups arl where hzca.party_id = cup.party_id (+) and hzca.cust_account_id = cup.cust_account_id (+) and hzp.party_id = hzca.party_id (+) and hzca.status = arl.lookup_code and ( hzca.status = 'A' or hzca.status = 'I') and arl.lookup_type ='CODE_STATUS' 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, null, 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 hzca.status is null

View Text - HTML Formatted

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
, ARL.DESCRIPTION ACCOUNT_STATUS
, 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
, AR_LOOKUPS ARL
WHERE HZCA.PARTY_ID = CUP.PARTY_ID (+)
AND HZCA.CUST_ACCOUNT_ID = CUP.CUST_ACCOUNT_ID (+)
AND HZP.PARTY_ID = HZCA.PARTY_ID (+)
AND HZCA.STATUS = ARL.LOOKUP_CODE
AND ( HZCA.STATUS = 'A' OR HZCA.STATUS = 'I')
AND ARL.LOOKUP_TYPE ='CODE_STATUS' 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
, NULL
, 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 HZCA.STATUS IS NULL