VIEW: APPS.PAY_INDICATOR_ENTRIES_V

Source

View Text - Preformatted

SELECT /*View used by block ADVANCE in Advance Pay form, PAYWSAHP, to retrieve indicator*/ /*element entries for the assignment. */ /*It will show advanced periods as indicated below: */ /* pay period1 holiday period1 pay period2 holiday period2 */ /* |-----------| |-----------------| |-----------| |-----------------| */ /* | */ /*session date1 */ /* | */ /* session date2 */ /* | */ /* session date3 */ /* | */ /* session date4 */ /* | */ /* session date5 | */ /* session date6 */ /*session date1,2,3,4: show pay/holiday period1 and 2 */ /*session date5: show pay/holiday period2 */ /*session date6: no periods shown */ /* Note: This view handles empty queries only due to the existing */ /* to_date statements. Therefore the corresponding form does */ /* not support QBE. */ /*+ ORDERED */ distinct /*Legislation Code.*/ plr1.legislation_code, /* Assignment_id.*/ peef.assignment_id, /* Pay Advance Indicator entry context info.*/ peef.element_entry_id, peef.effective_start_date, /* Payment Period End Date.*/ peef.effective_end_date, /* Advance Pay Start Date.*/ fnd_date.canonical_to_date(peevf1.screen_entry_value), /* Advance Pay End Date.*/ fnd_date.canonical_to_date(peevf2.screen_entry_value), /* Advanced Amount.*/ pay_advance_pay_pkg.advance_amount ( fnd_date.canonical_to_date (peevf1.screen_entry_value), peef.element_entry_id, peef.assignment_id ), /* Processed Flag.*/ decode(pay_advance_pay_pkg.advance_amount ( fnd_date.canonical_to_date (peevf1.screen_entry_value), peef.element_entry_id, peef.assignment_id ) ,null,'N','Y'), /* Created by.*/ peef.created_by, /* Creation date.*/ peef.creation_date, /* Last updated by.*/ peef.last_updated_by, /* Last update date.*/ peef.last_update_date, /* Last update login.*/ peef.last_update_login, /* Row id.*/ peef.rowid from pay_legislation_rules plr1, pay_legislation_rules plr2, per_assignments_f paf, pay_element_entries_f peef, pay_element_entry_values_f peevf1, pay_input_values_f pivf1, pay_input_values_f pivf2, fnd_sessions fs, pay_element_entry_values_f peevf2 where /* includes fixes: 634019*/ /* Legislation: */ plr1.rule_type = 'PAI_START_DATE' and plr2.legislation_code = plr1.legislation_code and plr2.rule_type = 'PAI_END_DATE' and /* Input Values/Element Types joins, */ pivf1.input_value_id = fnd_number.canonical_to_number(plr1.rule_mode) and pivf2.input_value_id = fnd_number.canonical_to_number(plr2.rule_mode) and peef.assignment_id = paf.assignment_id and peef.effective_start_date between paf.effective_start_date and paf.effective_end_date and peef.effective_end_date between paf.effective_start_date and paf.effective_end_date and peef.entry_type = 'E' and /* - Pay Advance Indicator, Start Date :*/ peevf1.element_entry_id = peef.element_entry_id and peevf1.effective_start_date = peef.effective_start_date and peevf1.effective_end_date = peef.effective_end_date and peevf1.input_value_id = pivf1.input_value_id and /* - Pay Advance Indicator, End Date: */ peevf2.element_entry_id = peef.element_entry_id and peevf2.effective_start_date = peef.effective_start_date and peevf2.effective_end_date = peef.effective_end_date and peevf2.input_value_id = pivf2.input_value_id and /* enforce date conditions described in comments above: */ fs.session_id = userenv('sessionid') and fnd_date.canonical_to_date (peevf2.screen_entry_value) >= fs.effective_date

View Text - HTML Formatted

SELECT /*VIEW USED BY BLOCK ADVANCE IN ADVANCE PAY FORM
, PAYWSAHP
, TO RETRIEVE INDICATOR*/ /*ELEMENT ENTRIES FOR THE ASSIGNMENT. */ /*IT WILL SHOW ADVANCED PERIODS AS INDICATED BELOW: */ /* PAY PERIOD1 HOLIDAY PERIOD1 PAY PERIOD2 HOLIDAY PERIOD2 */ /* |-----------| |-----------------| |-----------| |-----------------| */ /* | */ /*SESSION DATE1 */ /* | */ /* SESSION DATE2 */ /* | */ /* SESSION DATE3 */ /* | */ /* SESSION DATE4 */ /* | */ /* SESSION DATE5 | */ /* SESSION DATE6 */ /*SESSION DATE1
, 2
, 3
, 4: SHOW PAY/HOLIDAY PERIOD1
AND 2 */ /*SESSION DATE5: SHOW PAY/HOLIDAY PERIOD2 */ /*SESSION DATE6: NO PERIODS SHOWN */ /* NOTE: THIS VIEW HANDLES EMPTY QUERIES ONLY DUE TO THE EXISTING */ /* TO_DATE STATEMENTS. THEREFORE THE CORRESPONDING FORM DOES */ /* NOT SUPPORT QBE. */ /*+ ORDERED */ DISTINCT /*LEGISLATION CODE.*/ PLR1.LEGISLATION_CODE
, /* ASSIGNMENT_ID.*/ PEEF.ASSIGNMENT_ID
, /* PAY ADVANCE INDICATOR ENTRY CONTEXT INFO.*/ PEEF.ELEMENT_ENTRY_ID
, PEEF.EFFECTIVE_START_DATE
, /* PAYMENT PERIOD END DATE.*/ PEEF.EFFECTIVE_END_DATE
, /* ADVANCE PAY START DATE.*/ FND_DATE.CANONICAL_TO_DATE(PEEVF1.SCREEN_ENTRY_VALUE)
, /* ADVANCE PAY END DATE.*/ FND_DATE.CANONICAL_TO_DATE(PEEVF2.SCREEN_ENTRY_VALUE)
, /* ADVANCED AMOUNT.*/ PAY_ADVANCE_PAY_PKG.ADVANCE_AMOUNT ( FND_DATE.CANONICAL_TO_DATE (PEEVF1.SCREEN_ENTRY_VALUE)
, PEEF.ELEMENT_ENTRY_ID
, PEEF.ASSIGNMENT_ID )
, /* PROCESSED FLAG.*/ DECODE(PAY_ADVANCE_PAY_PKG.ADVANCE_AMOUNT ( FND_DATE.CANONICAL_TO_DATE (PEEVF1.SCREEN_ENTRY_VALUE)
, PEEF.ELEMENT_ENTRY_ID
, PEEF.ASSIGNMENT_ID )
, NULL
, 'N'
, 'Y')
, /* CREATED BY.*/ PEEF.CREATED_BY
, /* CREATION DATE.*/ PEEF.CREATION_DATE
, /* LAST UPDATED BY.*/ PEEF.LAST_UPDATED_BY
, /* LAST UPDATE DATE.*/ PEEF.LAST_UPDATE_DATE
, /* LAST UPDATE LOGIN.*/ PEEF.LAST_UPDATE_LOGIN
, /* ROW ID.*/ PEEF.ROWID
FROM PAY_LEGISLATION_RULES PLR1
, PAY_LEGISLATION_RULES PLR2
, PER_ASSIGNMENTS_F PAF
, PAY_ELEMENT_ENTRIES_F PEEF
, PAY_ELEMENT_ENTRY_VALUES_F PEEVF1
, PAY_INPUT_VALUES_F PIVF1
, PAY_INPUT_VALUES_F PIVF2
, FND_SESSIONS FS
, PAY_ELEMENT_ENTRY_VALUES_F PEEVF2
WHERE /* INCLUDES FIXES: 634019*/ /* LEGISLATION: */ PLR1.RULE_TYPE = 'PAI_START_DATE'
AND PLR2.LEGISLATION_CODE = PLR1.LEGISLATION_CODE
AND PLR2.RULE_TYPE = 'PAI_END_DATE'
AND /* INPUT VALUES/ELEMENT TYPES JOINS
, */ PIVF1.INPUT_VALUE_ID = FND_NUMBER.CANONICAL_TO_NUMBER(PLR1.RULE_MODE)
AND PIVF2.INPUT_VALUE_ID = FND_NUMBER.CANONICAL_TO_NUMBER(PLR2.RULE_MODE)
AND PEEF.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PEEF.EFFECTIVE_START_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PEEF.EFFECTIVE_END_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PEEF.ENTRY_TYPE = 'E'
AND /* - PAY ADVANCE INDICATOR
, START DATE :*/ PEEVF1.ELEMENT_ENTRY_ID = PEEF.ELEMENT_ENTRY_ID
AND PEEVF1.EFFECTIVE_START_DATE = PEEF.EFFECTIVE_START_DATE
AND PEEVF1.EFFECTIVE_END_DATE = PEEF.EFFECTIVE_END_DATE
AND PEEVF1.INPUT_VALUE_ID = PIVF1.INPUT_VALUE_ID
AND /* - PAY ADVANCE INDICATOR
, END DATE: */ PEEVF2.ELEMENT_ENTRY_ID = PEEF.ELEMENT_ENTRY_ID
AND PEEVF2.EFFECTIVE_START_DATE = PEEF.EFFECTIVE_START_DATE
AND PEEVF2.EFFECTIVE_END_DATE = PEEF.EFFECTIVE_END_DATE
AND PEEVF2.INPUT_VALUE_ID = PIVF2.INPUT_VALUE_ID
AND /* ENFORCE DATE CONDITIONS DESCRIBED IN COMMENTS ABOVE: */ FS.SESSION_ID = USERENV('SESSIONID')
AND FND_DATE.CANONICAL_TO_DATE (PEEVF2.SCREEN_ENTRY_VALUE) >= FS.EFFECTIVE_DATE