VIEW: APPS.PAY_CA_WCB_GRP_LEVEL_V

Source

View Text - Preformatted

SELECT pwav.account_number , pwav.province , pwr.rate_id , pwav.business_group_id , pwr.code , pwr.rate , pwr.description , pbs.balance_set_name , pbs.attribute_1 base_bal_name_taxable , pbs.attribute_2 base_bal_name_liability , pbs.attribute_3 base_bal_name_excess , pbttl_1.balance_name tl_bal_name_taxable , pbttl_2.balance_name tl_bal_name_liability , pbttl_3.balance_name tl_bal_name_excess , pbs.attribute_4 dimension , pbs.legislation_code , fs.effective_date , pay_ca_group_level_bal_pkg.ca_group_level_balance( pbs.attribute_1 ,'PYDATE' ,fs.effective_date ,'' ,pwr.rate_id ,'' ,'' ) PYDATE_TAXABLE , pay_ca_group_level_bal_pkg.ca_group_level_balance( pbs.attribute_2 ,'PYDATE' ,fs.effective_date ,'' ,pwr.rate_id ,'' ,'' ) PYDATE_LIABILITY , pay_ca_group_level_bal_pkg.ca_group_level_balance( pbs.attribute_3 ,'PYDATE' ,fs.effective_date ,'' ,pwr.rate_id ,'' ,'' ) PYDATE_EXCESS ,decode(pbs.attribute_4,NULL,0, pay_ca_group_level_bal_pkg.ca_group_level_balance( pbs.attribute_1 ,pbs.attribute_4 ,fs.effective_date ,'' ,pwr.rate_id ,'' ,'' )) OTD_TAXABLE ,decode(pbs.attribute_4,NULL,0, pay_ca_group_level_bal_pkg.ca_group_level_balance( pbs.attribute_2 ,pbs.attribute_4 ,fs.effective_date ,'' ,pwr.rate_id ,'' ,'' )) OTD_LIABILITY ,decode(pbs.attribute_4,NULL,0, pay_ca_group_level_bal_pkg.ca_group_level_balance( pbs.attribute_3 ,pbs.attribute_4 ,fs.effective_date ,'' ,pwr.rate_id ,'' ,'' )) OTD_EXCESS FROM pay_wci_accounts_v pwav , pay_wci_rates pwr , pay_balance_sets pbs , fnd_sessions fs , pay_balance_types pbt_1 , pay_balance_types_tl pbttl_1 , pay_balance_types pbt_2 , pay_balance_types_tl pbttl_2 , pay_balance_types pbt_3 , pay_balance_types_tl pbttl_3 WHERE pwav.account_id = pwr.account_id and fs.session_id = userenv('sessionid') and pbs.balance_set_name like 'WCB_SUM%' and pbs.legislation_code = 'CA' and pbt_1.balance_type_id = pbttl_1.balance_type_id and pbs.attribute_1 = pbt_1.balance_name and pbttl_1.language = userenv('LANG') and pbt_2.balance_type_id = pbttl_2.balance_type_id and pbs.attribute_2 = pbt_2.balance_name and pbttl_2.language = userenv('LANG') and pbt_3.balance_type_id = pbttl_3.balance_type_id and pbs.attribute_3 = pbt_3.balance_name and pbttl_3.language = userenv('LANG') and exists (select 1 from pay_action_contexts pac , ff_contexts fc where to_char(pwr.rate_id) = pac.context_value and pac.context_id = fc.context_id and fc.context_name = 'SOURCE_ID')

View Text - HTML Formatted

SELECT PWAV.ACCOUNT_NUMBER
, PWAV.PROVINCE
, PWR.RATE_ID
, PWAV.BUSINESS_GROUP_ID
, PWR.CODE
, PWR.RATE
, PWR.DESCRIPTION
, PBS.BALANCE_SET_NAME
, PBS.ATTRIBUTE_1 BASE_BAL_NAME_TAXABLE
, PBS.ATTRIBUTE_2 BASE_BAL_NAME_LIABILITY
, PBS.ATTRIBUTE_3 BASE_BAL_NAME_EXCESS
, PBTTL_1.BALANCE_NAME TL_BAL_NAME_TAXABLE
, PBTTL_2.BALANCE_NAME TL_BAL_NAME_LIABILITY
, PBTTL_3.BALANCE_NAME TL_BAL_NAME_EXCESS
, PBS.ATTRIBUTE_4 DIMENSION
, PBS.LEGISLATION_CODE
, FS.EFFECTIVE_DATE
, PAY_CA_GROUP_LEVEL_BAL_PKG.CA_GROUP_LEVEL_BALANCE( PBS.ATTRIBUTE_1
, 'PYDATE'
, FS.EFFECTIVE_DATE
, ''
, PWR.RATE_ID
, ''
, '' ) PYDATE_TAXABLE
, PAY_CA_GROUP_LEVEL_BAL_PKG.CA_GROUP_LEVEL_BALANCE( PBS.ATTRIBUTE_2
, 'PYDATE'
, FS.EFFECTIVE_DATE
, ''
, PWR.RATE_ID
, ''
, '' ) PYDATE_LIABILITY
, PAY_CA_GROUP_LEVEL_BAL_PKG.CA_GROUP_LEVEL_BALANCE( PBS.ATTRIBUTE_3
, 'PYDATE'
, FS.EFFECTIVE_DATE
, ''
, PWR.RATE_ID
, ''
, '' ) PYDATE_EXCESS
, DECODE(PBS.ATTRIBUTE_4
, NULL
, 0
, PAY_CA_GROUP_LEVEL_BAL_PKG.CA_GROUP_LEVEL_BALANCE( PBS.ATTRIBUTE_1
, PBS.ATTRIBUTE_4
, FS.EFFECTIVE_DATE
, ''
, PWR.RATE_ID
, ''
, '' )) OTD_TAXABLE
, DECODE(PBS.ATTRIBUTE_4
, NULL
, 0
, PAY_CA_GROUP_LEVEL_BAL_PKG.CA_GROUP_LEVEL_BALANCE( PBS.ATTRIBUTE_2
, PBS.ATTRIBUTE_4
, FS.EFFECTIVE_DATE
, ''
, PWR.RATE_ID
, ''
, '' )) OTD_LIABILITY
, DECODE(PBS.ATTRIBUTE_4
, NULL
, 0
, PAY_CA_GROUP_LEVEL_BAL_PKG.CA_GROUP_LEVEL_BALANCE( PBS.ATTRIBUTE_3
, PBS.ATTRIBUTE_4
, FS.EFFECTIVE_DATE
, ''
, PWR.RATE_ID
, ''
, '' )) OTD_EXCESS
FROM PAY_WCI_ACCOUNTS_V PWAV
, PAY_WCI_RATES PWR
, PAY_BALANCE_SETS PBS
, FND_SESSIONS FS
, PAY_BALANCE_TYPES PBT_1
, PAY_BALANCE_TYPES_TL PBTTL_1
, PAY_BALANCE_TYPES PBT_2
, PAY_BALANCE_TYPES_TL PBTTL_2
, PAY_BALANCE_TYPES PBT_3
, PAY_BALANCE_TYPES_TL PBTTL_3
WHERE PWAV.ACCOUNT_ID = PWR.ACCOUNT_ID
AND FS.SESSION_ID = USERENV('SESSIONID')
AND PBS.BALANCE_SET_NAME LIKE 'WCB_SUM%'
AND PBS.LEGISLATION_CODE = 'CA'
AND PBT_1.BALANCE_TYPE_ID = PBTTL_1.BALANCE_TYPE_ID
AND PBS.ATTRIBUTE_1 = PBT_1.BALANCE_NAME
AND PBTTL_1.LANGUAGE = USERENV('LANG')
AND PBT_2.BALANCE_TYPE_ID = PBTTL_2.BALANCE_TYPE_ID
AND PBS.ATTRIBUTE_2 = PBT_2.BALANCE_NAME
AND PBTTL_2.LANGUAGE = USERENV('LANG')
AND PBT_3.BALANCE_TYPE_ID = PBTTL_3.BALANCE_TYPE_ID
AND PBS.ATTRIBUTE_3 = PBT_3.BALANCE_NAME
AND PBTTL_3.LANGUAGE = USERENV('LANG')
AND EXISTS (SELECT 1
FROM PAY_ACTION_CONTEXTS PAC
, FF_CONTEXTS FC
WHERE TO_CHAR(PWR.RATE_ID) = PAC.CONTEXT_VALUE
AND PAC.CONTEXT_ID = FC.CONTEXT_ID
AND FC.CONTEXT_NAME = 'SOURCE_ID')