VIEW: APPS.PAY_PDT_BATCH_LINES

Source

View Text - Preformatted

SELECT /* Join result set of previous view to hr_lookups. In 4 cases, ** pay_pdt_batch_lines requires lookup_code whereas pay_batch_lines ** stores meaning. Convert 4 varchars to numbers. Column amount ** is mapped to the first non-null value from 6 possible input ** value names. */ bl.batch_id , bl.line_id , bl.assignment_number , bl.adjustment_type_code , fnd_number.canonical_to_number( NVL( bl.amt_amount, NVL( bl.amt_percentage, NVL( bl.amt_net_amount, NVL( bl.amt_pay_value, NVL( bl.amt_replace_amt, bl.amt_addl_amt ) ) ) ) ) ) amount , bl.apply_this_period , bl.cost_center_code , bl.element_name , bl.from_date , fnd_number.canonical_to_number( bl.hourly_rate ) hourly_rate , fnd_number.canonical_to_number( bl.hours_worked ) hours_worked , h1.lookup_code inc_asc_balance , bl.labor_dist_code , bl.line_status , bl.locality_worked , bl.new_salary , bl.pay_effective_date , bl.pcnt_increase , bl.rate_code , fnd_number.canonical_to_number( bl.rate_multiple ) rate_multiple , bl.rating_code , h2.lookup_code separate_check_flag , bl.shift_type , bl.state_worked , h3.lookup_code tax_separately_flag , bl.to_date , h4.lookup_code vol_ded_proc_ovd , bl.cost_allocation_keyflex_id , bl.concatenated_segments , bl.segment1 , bl.segment2 , bl.segment3 , bl.segment4 , bl.segment5 , bl.segment6 , bl.segment7 , bl.segment8 , bl.segment9 , bl.segment10 , bl.segment11 , bl.segment12 , bl.segment13 , bl.segment14 , bl.segment15 , bl.segment16 , bl.segment17 , bl.segment18 , bl.segment19 , bl.segment20 , bl.segment21 , bl.segment22 , bl.segment23 , bl.segment24 , bl.segment25 , bl.segment26 , bl.segment27 , bl.segment28 , bl.segment29 , bl.segment30 FROM pay_pdt_batch_lines_v3 bl , hr_lookups h1 , hr_lookups h2 , hr_lookups h3 , hr_lookups h4 WHERE bl.inc_asc_balance = h1.meaning(+) AND h1.lookup_type(+) = 'YES_NO' AND bl.separate_check_flag = h2.meaning(+) AND h2.lookup_type(+) = 'YES_NO' AND bl.tax_separately_flag = h3.meaning(+) AND h3.lookup_type(+) = 'YES_NO' AND bl.vol_ded_proc_ovd = h4.meaning(+) AND h4.lookup_type(+) = 'US_DEDUCTION_PROCESSING'

View Text - HTML Formatted

SELECT /* JOIN RESULT SET OF PREVIOUS VIEW TO HR_LOOKUPS. IN 4 CASES
, ** PAY_PDT_BATCH_LINES REQUIRES LOOKUP_CODE WHEREAS PAY_BATCH_LINES ** STORES MEANING. CONVERT 4 VARCHARS TO NUMBERS. COLUMN AMOUNT ** IS MAPPED TO THE FIRST NON-NULL VALUE
FROM 6 POSSIBLE INPUT ** VALUE NAMES. */ BL.BATCH_ID
, BL.LINE_ID
, BL.ASSIGNMENT_NUMBER
, BL.ADJUSTMENT_TYPE_CODE
, FND_NUMBER.CANONICAL_TO_NUMBER( NVL( BL.AMT_AMOUNT
, NVL( BL.AMT_PERCENTAGE
, NVL( BL.AMT_NET_AMOUNT
, NVL( BL.AMT_PAY_VALUE
, NVL( BL.AMT_REPLACE_AMT
, BL.AMT_ADDL_AMT ) ) ) ) ) ) AMOUNT
, BL.APPLY_THIS_PERIOD
, BL.COST_CENTER_CODE
, BL.ELEMENT_NAME
, BL.FROM_DATE
, FND_NUMBER.CANONICAL_TO_NUMBER( BL.HOURLY_RATE ) HOURLY_RATE
, FND_NUMBER.CANONICAL_TO_NUMBER( BL.HOURS_WORKED ) HOURS_WORKED
, H1.LOOKUP_CODE INC_ASC_BALANCE
, BL.LABOR_DIST_CODE
, BL.LINE_STATUS
, BL.LOCALITY_WORKED
, BL.NEW_SALARY
, BL.PAY_EFFECTIVE_DATE
, BL.PCNT_INCREASE
, BL.RATE_CODE
, FND_NUMBER.CANONICAL_TO_NUMBER( BL.RATE_MULTIPLE ) RATE_MULTIPLE
, BL.RATING_CODE
, H2.LOOKUP_CODE SEPARATE_CHECK_FLAG
, BL.SHIFT_TYPE
, BL.STATE_WORKED
, H3.LOOKUP_CODE TAX_SEPARATELY_FLAG
, BL.TO_DATE
, H4.LOOKUP_CODE VOL_DED_PROC_OVD
, BL.COST_ALLOCATION_KEYFLEX_ID
, BL.CONCATENATED_SEGMENTS
, BL.SEGMENT1
, BL.SEGMENT2
, BL.SEGMENT3
, BL.SEGMENT4
, BL.SEGMENT5
, BL.SEGMENT6
, BL.SEGMENT7
, BL.SEGMENT8
, BL.SEGMENT9
, BL.SEGMENT10
, BL.SEGMENT11
, BL.SEGMENT12
, BL.SEGMENT13
, BL.SEGMENT14
, BL.SEGMENT15
, BL.SEGMENT16
, BL.SEGMENT17
, BL.SEGMENT18
, BL.SEGMENT19
, BL.SEGMENT20
, BL.SEGMENT21
, BL.SEGMENT22
, BL.SEGMENT23
, BL.SEGMENT24
, BL.SEGMENT25
, BL.SEGMENT26
, BL.SEGMENT27
, BL.SEGMENT28
, BL.SEGMENT29
, BL.SEGMENT30
FROM PAY_PDT_BATCH_LINES_V3 BL
, HR_LOOKUPS H1
, HR_LOOKUPS H2
, HR_LOOKUPS H3
, HR_LOOKUPS H4
WHERE BL.INC_ASC_BALANCE = H1.MEANING(+)
AND H1.LOOKUP_TYPE(+) = 'YES_NO'
AND BL.SEPARATE_CHECK_FLAG = H2.MEANING(+)
AND H2.LOOKUP_TYPE(+) = 'YES_NO'
AND BL.TAX_SEPARATELY_FLAG = H3.MEANING(+)
AND H3.LOOKUP_TYPE(+) = 'YES_NO'
AND BL.VOL_DED_PROC_OVD = H4.MEANING(+)
AND H4.LOOKUP_TYPE(+) = 'US_DEDUCTION_PROCESSING'