VIEW: APPS.PAY_GB_YEAR_END_VALUES_V

Source

View Text - Preformatted

SELECT act.assignment_action_id , act.assignment_id , act.payroll_action_id , pay_gb_eoy_archive.canonical_to_date(fai1.VALUE) effective_end_date , decode(aic1.context,'1','Y','0','N','2','M') reportable , aic2.context ni_category_code , aic3.context scon , pay_gb_eoy_archive.get_arch_num(fai1.context1,'X_NI_'|| decode(aic3.context,NULL, NULL,'SCON_')|| 'ABLE_ET', aic1.context,aic2.context,aic3.context) NI_ABLE_ET , pay_gb_eoy_archive.get_arch_num(fai1.context1,'X_NI_'|| decode(aic3.context,NULL,NULL,'SCON_')|| 'ABLE_EET', aic1.context,aic2.context,aic3.context) NI_ABLE_EET , pay_gb_eoy_archive.get_arch_num(fai1.context1,'X_NI_'|| decode(aic3.context,NULL, NULL,'SCON_')|| 'ABLE_LEL', aic1.context,aic2.context,aic3.context) NI_ABLE_LEL , pay_gb_eoy_archive.get_arch_num(fai1.context1,'X_NI_'|| decode(aic3.context,NULL, NULL,'SCON_')|| 'ABLE_UEL', aic1.context,aic2.context,aic3.context) NI_ABLE_UEL , pay_gb_eoy_archive.get_arch_num(fai1.context1,'X_NI_'|| decode(aic3.context,NULL, NULL,'SCON_')|| 'EARNINGS', aic1.context,aic2.context,aic3.context) EARNINGS , pay_gb_eoy_archive.get_arch_num(fai1.context1,'X_NI_'|| decode(aic3.context,NULL, NULL,'SCON_')|| 'EMPLOYEES_CONTRIBUTIONS', aic1.context,aic2.context,aic3.context) EMPLOYEES_CONTRIBUTIONS , pay_gb_eoy_archive.get_arch_num(fai1.context1,'X_NI_'|| decode(aic3.context,NULL,NULL,'SCON_')|| 'EMPLOYERS_REBATE' , aic1.context,aic2.context,aic3.context) EMPLOYERS_REBATE , pay_gb_eoy_archive.get_arch_num(fai1.context1,'X_NI_'|| decode(aic3.context,NULL,NULL,'SCON_')||'EMPLOYEES_REBATE', aic1.context,aic2.context,aic3.context) EMPLOYEES_REBATE , pay_gb_eoy_archive.get_arch_str(fai1.context1,'X_NI_'|| decode(aic3.context,NULL, NULL,'SCON_')|| 'REFUND', aic1.context,aic2.context,aic3.context) NI_REFUND , pay_gb_eoy_archive.canonical_to_number(fai2.value) TOTAL_CONTRIBUTIONS , pay_gb_eoy_archive.get_arch_num(fai1.context1,'X_NI_'|| decode(aic3.context,NULL, NULL,'SCON_')|| 'ABLE_AUEL', aic1.context,aic2.context,aic3.context) NI_ABLE_AUEL , pay_gb_eoy_archive.get_arch_num(fai1.context1,'X_NI_'|| decode(aic3.context,NULL, NULL,'SCON_')|| 'ABLE_UAP', aic1.context,aic2.context,aic3.context) NI_ABLE_UAP FROM ff_archive_item_contexts aic3, ff_archive_item_contexts aic2, ff_archive_item_contexts aic1, ff_archive_items fai2, ff_user_entities fue2, ff_archive_items fai1, ff_user_entities fue1, pay_assignment_actions act WHERE act.assignment_action_id = fai1.context1 AND act.assignment_action_id = fai2.context1 AND fue1.user_entity_name = 'X_EFFECTIVE_END_DATE' AND fue2.user_entity_name like 'X_NI_%TOTAL_CONTRIBUTIONS' AND fue1.legislation_code = 'GB' AND fue1.business_group_id IS NULL AND fue2.legislation_code = 'GB' AND fue2.business_group_id IS NULL AND fue1.user_entity_id = fai1.user_entity_id + 0 AND fue2.user_entity_id = fai2.user_entity_id + 0 AND aic1.archive_item_id = fai2.archive_item_id AND aic2.archive_item_id = fai2.archive_item_id AND aic3.archive_item_id (+) = fai2.archive_item_id AND aic1.sequence_no = 1 AND aic2.sequence_no = 2 AND aic3.sequence_no (+) = 3

View Text - HTML Formatted

SELECT ACT.ASSIGNMENT_ACTION_ID
, ACT.ASSIGNMENT_ID
, ACT.PAYROLL_ACTION_ID
, PAY_GB_EOY_ARCHIVE.CANONICAL_TO_DATE(FAI1.VALUE) EFFECTIVE_END_DATE
, DECODE(AIC1.CONTEXT
, '1'
, 'Y'
, '0'
, 'N'
, '2'
, 'M') REPORTABLE
, AIC2.CONTEXT NI_CATEGORY_CODE
, AIC3.CONTEXT SCON
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI1.CONTEXT1
, 'X_NI_'|| DECODE(AIC3.CONTEXT
, NULL
, NULL
, 'SCON_')|| 'ABLE_ET'
, AIC1.CONTEXT
, AIC2.CONTEXT
, AIC3.CONTEXT) NI_ABLE_ET
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI1.CONTEXT1
, 'X_NI_'|| DECODE(AIC3.CONTEXT
, NULL
, NULL
, 'SCON_')|| 'ABLE_EET'
, AIC1.CONTEXT
, AIC2.CONTEXT
, AIC3.CONTEXT) NI_ABLE_EET
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI1.CONTEXT1
, 'X_NI_'|| DECODE(AIC3.CONTEXT
, NULL
, NULL
, 'SCON_')|| 'ABLE_LEL'
, AIC1.CONTEXT
, AIC2.CONTEXT
, AIC3.CONTEXT) NI_ABLE_LEL
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI1.CONTEXT1
, 'X_NI_'|| DECODE(AIC3.CONTEXT
, NULL
, NULL
, 'SCON_')|| 'ABLE_UEL'
, AIC1.CONTEXT
, AIC2.CONTEXT
, AIC3.CONTEXT) NI_ABLE_UEL
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI1.CONTEXT1
, 'X_NI_'|| DECODE(AIC3.CONTEXT
, NULL
, NULL
, 'SCON_')|| 'EARNINGS'
, AIC1.CONTEXT
, AIC2.CONTEXT
, AIC3.CONTEXT) EARNINGS
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI1.CONTEXT1
, 'X_NI_'|| DECODE(AIC3.CONTEXT
, NULL
, NULL
, 'SCON_')|| 'EMPLOYEES_CONTRIBUTIONS'
, AIC1.CONTEXT
, AIC2.CONTEXT
, AIC3.CONTEXT) EMPLOYEES_CONTRIBUTIONS
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI1.CONTEXT1
, 'X_NI_'|| DECODE(AIC3.CONTEXT
, NULL
, NULL
, 'SCON_')|| 'EMPLOYERS_REBATE'
, AIC1.CONTEXT
, AIC2.CONTEXT
, AIC3.CONTEXT) EMPLOYERS_REBATE
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI1.CONTEXT1
, 'X_NI_'|| DECODE(AIC3.CONTEXT
, NULL
, NULL
, 'SCON_')||'EMPLOYEES_REBATE'
, AIC1.CONTEXT
, AIC2.CONTEXT
, AIC3.CONTEXT) EMPLOYEES_REBATE
, PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(FAI1.CONTEXT1
, 'X_NI_'|| DECODE(AIC3.CONTEXT
, NULL
, NULL
, 'SCON_')|| 'REFUND'
, AIC1.CONTEXT
, AIC2.CONTEXT
, AIC3.CONTEXT) NI_REFUND
, PAY_GB_EOY_ARCHIVE.CANONICAL_TO_NUMBER(FAI2.VALUE) TOTAL_CONTRIBUTIONS
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI1.CONTEXT1
, 'X_NI_'|| DECODE(AIC3.CONTEXT
, NULL
, NULL
, 'SCON_')|| 'ABLE_AUEL'
, AIC1.CONTEXT
, AIC2.CONTEXT
, AIC3.CONTEXT) NI_ABLE_AUEL
, PAY_GB_EOY_ARCHIVE.GET_ARCH_NUM(FAI1.CONTEXT1
, 'X_NI_'|| DECODE(AIC3.CONTEXT
, NULL
, NULL
, 'SCON_')|| 'ABLE_UAP'
, AIC1.CONTEXT
, AIC2.CONTEXT
, AIC3.CONTEXT) NI_ABLE_UAP
FROM FF_ARCHIVE_ITEM_CONTEXTS AIC3
, FF_ARCHIVE_ITEM_CONTEXTS AIC2
, FF_ARCHIVE_ITEM_CONTEXTS AIC1
, FF_ARCHIVE_ITEMS FAI2
, FF_USER_ENTITIES FUE2
, FF_ARCHIVE_ITEMS FAI1
, FF_USER_ENTITIES FUE1
, PAY_ASSIGNMENT_ACTIONS ACT
WHERE ACT.ASSIGNMENT_ACTION_ID = FAI1.CONTEXT1
AND ACT.ASSIGNMENT_ACTION_ID = FAI2.CONTEXT1
AND FUE1.USER_ENTITY_NAME = 'X_EFFECTIVE_END_DATE'
AND FUE2.USER_ENTITY_NAME LIKE 'X_NI_%TOTAL_CONTRIBUTIONS'
AND FUE1.LEGISLATION_CODE = 'GB'
AND FUE1.BUSINESS_GROUP_ID IS NULL
AND FUE2.LEGISLATION_CODE = 'GB'
AND FUE2.BUSINESS_GROUP_ID IS NULL
AND FUE1.USER_ENTITY_ID = FAI1.USER_ENTITY_ID + 0
AND FUE2.USER_ENTITY_ID = FAI2.USER_ENTITY_ID + 0
AND AIC1.ARCHIVE_ITEM_ID = FAI2.ARCHIVE_ITEM_ID
AND AIC2.ARCHIVE_ITEM_ID = FAI2.ARCHIVE_ITEM_ID
AND AIC3.ARCHIVE_ITEM_ID (+) = FAI2.ARCHIVE_ITEM_ID
AND AIC1.SEQUENCE_NO = 1
AND AIC2.SEQUENCE_NO = 2
AND AIC3.SEQUENCE_NO (+) = 3