VIEW: APPS.PAY_BALANCE_VALUES_V

Source

View Text - Preformatted

SELECT distinct paa.assignment_action_id, paa.assignment_id, paa.payroll_action_id, ppa.effective_date, ppa.payroll_id, pbt.balance_type_id, pbt.balance_name, pbd.balance_dimension_id, pbd.dimension_name, pbd.database_item_suffix, pbd.description dimension_description, pdb.defined_balance_id, pbd.route_id, pay_balance_pkg.get_value(pdb.defined_balance_id, paa.assignment_action_id, paa.tax_unit_id, pac_jur.context_value, to_number(pac_source.context_value), pac_source_text.context_value, pac_tax.context_value, ppa.effective_date, 'FALSE', 'FALSE', pac_source_text2.context_value, null, to_number(pac_time_def.context_value), fnd_date.canonical_to_date(pac_bal_date_def.context_value), null, to_number(pac_org_entry.context_value)) value, decode(pay_balance_pkg.chk_context(pac_tax.context_id,pbd.route_id),'Y', pac_tax.context_value,NULL) tax_group, decode(pay_balance_pkg.chk_context(pac_jur.context_id,pbd.route_id),'Y', pac_jur.context_value,NULL) jurisdiction, decode(pay_balance_pkg.chk_context(pac_source.context_id,pbd.route_id),'Y', to_number(pac_source.context_value),NULL) source_id, decode(pay_balance_pkg.chk_context(pac_source_text.context_id,pbd.route_id),'Y', pac_source_text.context_value,NULL) source_text, decode(pay_balance_pkg.chk_context(pac_org_entry.context_id,pbd.route_id),'Y', to_number(pac_org_entry.context_value),NULL) original_entry_id, decode(pay_balance_pkg.chk_context(pac_source_text2.context_id,pbd.route_id),'Y', pac_source_text2.context_value,NULL) source_text2, decode(pay_balance_pkg.chk_context(pac_time_def.context_id, pbd.route_id), 'Y', to_number(pac_time_def.context_value), NULL) time_definition_id, decode(pay_balance_pkg.chk_context(pac_bal_date_def.context_id, pbd.route_id), 'Y', fnd_date.canonical_to_date(pac_bal_date_def.context_value), NULL) balance_date, ppa.business_group_id FROM pay_payroll_actions ppa, pay_balance_types pbt, pay_balance_dimensions pbd, pay_defined_balances pdb, pay_assignment_actions paa, hr_organization_information org_info, (SELECT pac_jur.assignment_action_id, pac_jur.context_value, pac_jur.context_id FROM pay_action_contexts pac_jur, ff_contexts fc_jur WHERE fc_jur.context_name = 'TAX_GROUP' AND fc_jur.context_id = pac_jur.context_id) pac_tax, (SELECT pac_jur.assignment_action_id, pac_jur.context_value, pac_jur.context_id FROM pay_action_contexts pac_jur, ff_contexts fc_jur WHERE fc_jur.context_name = 'JURISDICTION_CODE' AND fc_jur.context_id = pac_jur.context_id) pac_jur, (SELECT pac_jur.assignment_action_id, pac_jur.context_value, pac_jur.context_id FROM pay_action_contexts pac_jur, ff_contexts fc_jur WHERE fc_jur.context_name = 'SOURCE_ID' AND fc_jur.context_id = pac_jur.context_id) pac_source, (SELECT pac_jur.assignment_action_id, pac_jur.context_value, pac_jur.context_id FROM pay_action_contexts pac_jur, ff_contexts fc_jur WHERE fc_jur.context_name = 'SOURCE_TEXT' AND fc_jur.context_id = pac_jur.context_id) pac_source_text, (SELECT pac_jur.assignment_action_id, pac_jur.context_value, pac_jur.context_id FROM pay_action_contexts pac_jur, ff_contexts fc_jur WHERE fc_jur.context_name = 'SOURCE_TEXT2' AND fc_jur.context_id = pac_jur.context_id) pac_source_text2, (SELECT pac_jur.assignment_action_id, pac_jur.context_value, pac_jur.context_id FROM pay_action_contexts pac_jur, ff_contexts fc_jur WHERE fc_jur.context_name = 'ORIGINAL_ENTRY_ID' AND fc_jur.context_id = pac_jur.context_id) pac_org_entry, (SELECT pac_jur.assignment_action_id, pac_jur.context_value, pac_jur.context_id FROM pay_action_contexts pac_jur, ff_contexts fc_jur WHERE fc_jur.context_name = 'TIME_DEFINITION_ID' AND fc_jur.context_id = pac_jur.context_id) pac_time_def, (SELECT pac_jur.assignment_action_id, pac_jur.context_value, pac_jur.context_id FROM pay_action_contexts pac_jur, ff_contexts fc_jur WHERE fc_jur.context_name = 'BALANCE_DATE' AND fc_jur.context_id = pac_jur.context_id) pac_bal_date_def WHERE paa.payroll_action_id = ppa.payroll_action_id AND pdb.balance_type_id = pbt.balance_type_id AND pdb.balance_dimension_id = pbd.balance_dimension_id AND paa.assignment_action_id = pac_jur.assignment_action_id (+) AND paa.assignment_action_id = pac_source.assignment_action_id (+) AND paa.assignment_action_id = pac_source_text.assignment_action_id (+) AND paa.assignment_action_id = pac_org_entry.assignment_action_id (+) AND paa.assignment_action_id = pac_tax.assignment_action_id (+) AND paa.assignment_action_id = pac_source_text2.assignment_action_id (+) AND paa.assignment_action_id = pac_time_def.assignment_action_id(+) AND paa.assignment_action_id = pac_bal_date_def.assignment_action_id(+) AND ppa.business_group_id = org_info.organization_id AND org_info.org_information_context = 'Business Group Information' AND org_info.org_information9 != 'US' AND EXISTS (SELECT '' FROM pay_balance_feeds_f pbf, pay_run_result_values prrv, pay_run_results prr WHERE prr.assignment_action_id = paa.assignment_action_id AND prr.run_result_id = prrv.run_result_id AND prrv.input_value_id = pbf.input_value_id AND pbf.balance_type_id = pbt.balance_type_id AND ppa.effective_date BETWEEN pbf.effective_start_date AND pbf.effective_end_date )

View Text - HTML Formatted

SELECT DISTINCT PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PAA.PAYROLL_ACTION_ID
, PPA.EFFECTIVE_DATE
, PPA.PAYROLL_ID
, PBT.BALANCE_TYPE_ID
, PBT.BALANCE_NAME
, PBD.BALANCE_DIMENSION_ID
, PBD.DIMENSION_NAME
, PBD.DATABASE_ITEM_SUFFIX
, PBD.DESCRIPTION DIMENSION_DESCRIPTION
, PDB.DEFINED_BALANCE_ID
, PBD.ROUTE_ID
, PAY_BALANCE_PKG.GET_VALUE(PDB.DEFINED_BALANCE_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, PAC_JUR.CONTEXT_VALUE
, TO_NUMBER(PAC_SOURCE.CONTEXT_VALUE)
, PAC_SOURCE_TEXT.CONTEXT_VALUE
, PAC_TAX.CONTEXT_VALUE
, PPA.EFFECTIVE_DATE
, 'FALSE'
, 'FALSE'
, PAC_SOURCE_TEXT2.CONTEXT_VALUE
, NULL
, TO_NUMBER(PAC_TIME_DEF.CONTEXT_VALUE)
, FND_DATE.CANONICAL_TO_DATE(PAC_BAL_DATE_DEF.CONTEXT_VALUE)
, NULL
, TO_NUMBER(PAC_ORG_ENTRY.CONTEXT_VALUE)) VALUE
, DECODE(PAY_BALANCE_PKG.CHK_CONTEXT(PAC_TAX.CONTEXT_ID
, PBD.ROUTE_ID)
, 'Y'
, PAC_TAX.CONTEXT_VALUE
, NULL) TAX_GROUP
, DECODE(PAY_BALANCE_PKG.CHK_CONTEXT(PAC_JUR.CONTEXT_ID
, PBD.ROUTE_ID)
, 'Y'
, PAC_JUR.CONTEXT_VALUE
, NULL) JURISDICTION
, DECODE(PAY_BALANCE_PKG.CHK_CONTEXT(PAC_SOURCE.CONTEXT_ID
, PBD.ROUTE_ID)
, 'Y'
, TO_NUMBER(PAC_SOURCE.CONTEXT_VALUE)
, NULL) SOURCE_ID
, DECODE(PAY_BALANCE_PKG.CHK_CONTEXT(PAC_SOURCE_TEXT.CONTEXT_ID
, PBD.ROUTE_ID)
, 'Y'
, PAC_SOURCE_TEXT.CONTEXT_VALUE
, NULL) SOURCE_TEXT
, DECODE(PAY_BALANCE_PKG.CHK_CONTEXT(PAC_ORG_ENTRY.CONTEXT_ID
, PBD.ROUTE_ID)
, 'Y'
, TO_NUMBER(PAC_ORG_ENTRY.CONTEXT_VALUE)
, NULL) ORIGINAL_ENTRY_ID
, DECODE(PAY_BALANCE_PKG.CHK_CONTEXT(PAC_SOURCE_TEXT2.CONTEXT_ID
, PBD.ROUTE_ID)
, 'Y'
, PAC_SOURCE_TEXT2.CONTEXT_VALUE
, NULL) SOURCE_TEXT2
, DECODE(PAY_BALANCE_PKG.CHK_CONTEXT(PAC_TIME_DEF.CONTEXT_ID
, PBD.ROUTE_ID)
, 'Y'
, TO_NUMBER(PAC_TIME_DEF.CONTEXT_VALUE)
, NULL) TIME_DEFINITION_ID
, DECODE(PAY_BALANCE_PKG.CHK_CONTEXT(PAC_BAL_DATE_DEF.CONTEXT_ID
, PBD.ROUTE_ID)
, 'Y'
, FND_DATE.CANONICAL_TO_DATE(PAC_BAL_DATE_DEF.CONTEXT_VALUE)
, NULL) BALANCE_DATE
, PPA.BUSINESS_GROUP_ID
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_BALANCE_TYPES PBT
, PAY_BALANCE_DIMENSIONS PBD
, PAY_DEFINED_BALANCES PDB
, PAY_ASSIGNMENT_ACTIONS PAA
, HR_ORGANIZATION_INFORMATION ORG_INFO
, (SELECT PAC_JUR.ASSIGNMENT_ACTION_ID
, PAC_JUR.CONTEXT_VALUE
, PAC_JUR.CONTEXT_ID
FROM PAY_ACTION_CONTEXTS PAC_JUR
, FF_CONTEXTS FC_JUR
WHERE FC_JUR.CONTEXT_NAME = 'TAX_GROUP'
AND FC_JUR.CONTEXT_ID = PAC_JUR.CONTEXT_ID) PAC_TAX
, (SELECT PAC_JUR.ASSIGNMENT_ACTION_ID
, PAC_JUR.CONTEXT_VALUE
, PAC_JUR.CONTEXT_ID
FROM PAY_ACTION_CONTEXTS PAC_JUR
, FF_CONTEXTS FC_JUR
WHERE FC_JUR.CONTEXT_NAME = 'JURISDICTION_CODE'
AND FC_JUR.CONTEXT_ID = PAC_JUR.CONTEXT_ID) PAC_JUR
, (SELECT PAC_JUR.ASSIGNMENT_ACTION_ID
, PAC_JUR.CONTEXT_VALUE
, PAC_JUR.CONTEXT_ID
FROM PAY_ACTION_CONTEXTS PAC_JUR
, FF_CONTEXTS FC_JUR
WHERE FC_JUR.CONTEXT_NAME = 'SOURCE_ID'
AND FC_JUR.CONTEXT_ID = PAC_JUR.CONTEXT_ID) PAC_SOURCE
, (SELECT PAC_JUR.ASSIGNMENT_ACTION_ID
, PAC_JUR.CONTEXT_VALUE
, PAC_JUR.CONTEXT_ID
FROM PAY_ACTION_CONTEXTS PAC_JUR
, FF_CONTEXTS FC_JUR
WHERE FC_JUR.CONTEXT_NAME = 'SOURCE_TEXT'
AND FC_JUR.CONTEXT_ID = PAC_JUR.CONTEXT_ID) PAC_SOURCE_TEXT
, (SELECT PAC_JUR.ASSIGNMENT_ACTION_ID
, PAC_JUR.CONTEXT_VALUE
, PAC_JUR.CONTEXT_ID
FROM PAY_ACTION_CONTEXTS PAC_JUR
, FF_CONTEXTS FC_JUR
WHERE FC_JUR.CONTEXT_NAME = 'SOURCE_TEXT2'
AND FC_JUR.CONTEXT_ID = PAC_JUR.CONTEXT_ID) PAC_SOURCE_TEXT2
, (SELECT PAC_JUR.ASSIGNMENT_ACTION_ID
, PAC_JUR.CONTEXT_VALUE
, PAC_JUR.CONTEXT_ID
FROM PAY_ACTION_CONTEXTS PAC_JUR
, FF_CONTEXTS FC_JUR
WHERE FC_JUR.CONTEXT_NAME = 'ORIGINAL_ENTRY_ID'
AND FC_JUR.CONTEXT_ID = PAC_JUR.CONTEXT_ID) PAC_ORG_ENTRY
, (SELECT PAC_JUR.ASSIGNMENT_ACTION_ID
, PAC_JUR.CONTEXT_VALUE
, PAC_JUR.CONTEXT_ID
FROM PAY_ACTION_CONTEXTS PAC_JUR
, FF_CONTEXTS FC_JUR
WHERE FC_JUR.CONTEXT_NAME = 'TIME_DEFINITION_ID'
AND FC_JUR.CONTEXT_ID = PAC_JUR.CONTEXT_ID) PAC_TIME_DEF
, (SELECT PAC_JUR.ASSIGNMENT_ACTION_ID
, PAC_JUR.CONTEXT_VALUE
, PAC_JUR.CONTEXT_ID
FROM PAY_ACTION_CONTEXTS PAC_JUR
, FF_CONTEXTS FC_JUR
WHERE FC_JUR.CONTEXT_NAME = 'BALANCE_DATE'
AND FC_JUR.CONTEXT_ID = PAC_JUR.CONTEXT_ID) PAC_BAL_DATE_DEF
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PAC_JUR.ASSIGNMENT_ACTION_ID (+)
AND PAA.ASSIGNMENT_ACTION_ID = PAC_SOURCE.ASSIGNMENT_ACTION_ID (+)
AND PAA.ASSIGNMENT_ACTION_ID = PAC_SOURCE_TEXT.ASSIGNMENT_ACTION_ID (+)
AND PAA.ASSIGNMENT_ACTION_ID = PAC_ORG_ENTRY.ASSIGNMENT_ACTION_ID (+)
AND PAA.ASSIGNMENT_ACTION_ID = PAC_TAX.ASSIGNMENT_ACTION_ID (+)
AND PAA.ASSIGNMENT_ACTION_ID = PAC_SOURCE_TEXT2.ASSIGNMENT_ACTION_ID (+)
AND PAA.ASSIGNMENT_ACTION_ID = PAC_TIME_DEF.ASSIGNMENT_ACTION_ID(+)
AND PAA.ASSIGNMENT_ACTION_ID = PAC_BAL_DATE_DEF.ASSIGNMENT_ACTION_ID(+)
AND PPA.BUSINESS_GROUP_ID = ORG_INFO.ORGANIZATION_ID
AND ORG_INFO.ORG_INFORMATION_CONTEXT = 'BUSINESS GROUP INFORMATION'
AND ORG_INFO.ORG_INFORMATION9 != 'US'
AND EXISTS (SELECT ''
FROM PAY_BALANCE_FEEDS_F PBF
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
WHERE PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRRV.INPUT_VALUE_ID = PBF.INPUT_VALUE_ID
AND PBF.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN PBF.EFFECTIVE_START_DATE
AND PBF.EFFECTIVE_END_DATE )