VIEW: APPS.PAY_PDT_BATCH_LINES_V1

Source

View Text - Preformatted

SELECT /* First create a view that shows an elements input values in the same order ** as they are held in pay_batch_lines. ie value_1 to value_15 */ ROWNUM-b.min_rownum+1 iv_seq , a.name , a.business_group_id , a.legislation_code , a.batch_line_id , a.cost_allocation_keyflex_id , a.element_type_id , a.assignment_id , a.batch_id , a.batch_line_status , a.assignment_number , a.batch_sequence , a.concatenated_segments , a.effective_date , a.element_name , a.entry_type , a.reason , a.segment1 , a.segment2 , a.segment3 , a.segment4 , a.segment5 , a.segment6 , a.segment7 , a.segment8 , a.segment9 , a.segment10 , a.segment11 , a.segment12 , a.segment13 , a.segment14 , a.segment15 , a.segment16 , a.segment17 , a.segment18 , a.segment19 , a.segment20 , a.segment21 , a.segment22 , a.segment23 , a.segment24 , a.segment25 , a.segment26 , a.segment27 , a.segment28 , a.segment29 , a.segment30 , a.value_1 , a.value_2 , a.value_3 , a.value_4 , a.value_5 , a.value_6 , a.value_7 , a.value_8 , a.value_9 , a.value_10 , a.value_11 , a.value_12 , a.value_13 , a.value_14 , a.value_15 , a.attribute_category , a.attribute1 , a.attribute2 , a.attribute3 , a.attribute4 , a.attribute5 , a.attribute6 , a.attribute7 , a.attribute8 , a.attribute9 , a.attribute10 , a.attribute11 , a.attribute12 , a.attribute13 , a.attribute14 , a.attribute15 , a.attribute16 , a.attribute17 , a.attribute18 , a.attribute19 , a.attribute20 FROM ( SELECT et.element_name , et.element_type_id , iv.name , bh.business_group_id , et.legislation_code , bl.batch_line_id , bl.cost_allocation_keyflex_id , bl.assignment_id , bl.batch_id , bl.batch_line_status , bl.assignment_number , bl.batch_sequence , bl.concatenated_segments , bl.effective_date , bl.entry_type , bl.reason , 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 , bl.value_1 , bl.value_2 , bl.value_3 , bl.value_4 , bl.value_5 , bl.value_6 , bl.value_7 , bl.value_8 , bl.value_9 , bl.value_10 , bl.value_11 , bl.value_12 , bl.value_13 , bl.value_14 , bl.value_15 , bl.attribute_category , bl.attribute1 , bl.attribute2 , bl.attribute3 , bl.attribute4 , bl.attribute5 , bl.attribute6 , bl.attribute7 , bl.attribute8 , bl.attribute9 , bl.attribute10 , bl.attribute11 , bl.attribute12 , bl.attribute13 , bl.attribute14 , bl.attribute15 , bl.attribute16 , bl.attribute17 , bl.attribute18 , bl.attribute19 , bl.attribute20 FROM pay_element_types_f et , pay_input_values_f iv , pay_batch_lines bl , pay_batch_headers bh , per_business_groups bg WHERE et.element_type_id = iv.element_type_id AND ( ((bl.element_type_id IS NOT NULL) AND ( et.element_type_id = bl.element_type_id)) OR ((bl.element_name IS NOT NULL) AND ( et.element_name = bl.element_name )) ) AND bh.batch_id = bl.batch_id AND bg.business_group_id = bh.business_group_id AND bl.effective_date BETWEEN iv.effective_start_date AND iv.effective_end_date AND bl.effective_date BETWEEN iv.effective_start_date AND iv.effective_end_date AND ( ( iv.business_group_id IS NULL AND iv.legislation_code = bg.legislation_code ) OR iv.business_group_id = bh.business_group_id ) AND bh.batch_name = 'Batch '||TO_CHAR( bh.batch_id ) ORDER BY bl.batch_line_id, et.element_type_id, iv.display_sequence, iv.name ) a, ( SELECT MIN( ROWNUM ) min_rownum , element_type_id , batch_line_id FROM ( SELECT et.element_type_id , iv.name , bl.batch_line_id FROM pay_element_types_f et , pay_input_values_f iv , pay_batch_lines bl , pay_batch_headers bh , per_business_groups bg WHERE et.element_type_id = iv.element_type_id AND ( ((bl.element_type_id IS NOT NULL) AND ( et.element_type_id = bl.element_type_id)) OR ((bl.element_name IS NOT NULL) AND ( et.element_name = bl.element_name )) ) AND bh.batch_id = bl.batch_id AND bg.business_group_id = bh.business_group_id AND bl.effective_date BETWEEN iv.effective_start_date AND iv.effective_end_date AND bl.effective_date BETWEEN iv.effective_start_date AND iv.effective_end_date AND ( ( iv.business_group_id IS NULL AND iv.legislation_code = bg.legislation_code ) OR iv.business_group_id = bh.business_group_id ) AND bh.batch_name = 'Batch '||TO_CHAR( bh.batch_id ) ORDER BY bl.batch_line_id, et.element_type_id, iv.display_sequence, iv.name ) GROUP BY element_type_id, batch_line_id ) b WHERE a.element_type_id = b.element_type_id AND a.batch_line_id = b.batch_line_id

View Text - HTML Formatted

SELECT /* FIRST CREATE A VIEW THAT SHOWS AN ELEMENTS INPUT VALUES IN THE SAME ORDER ** AS THEY ARE HELD IN PAY_BATCH_LINES. IE VALUE_1 TO VALUE_15 */ ROWNUM-B.MIN_ROWNUM+1 IV_SEQ
, A.NAME
, A.BUSINESS_GROUP_ID
, A.LEGISLATION_CODE
, A.BATCH_LINE_ID
, A.COST_ALLOCATION_KEYFLEX_ID
, A.ELEMENT_TYPE_ID
, A.ASSIGNMENT_ID
, A.BATCH_ID
, A.BATCH_LINE_STATUS
, A.ASSIGNMENT_NUMBER
, A.BATCH_SEQUENCE
, A.CONCATENATED_SEGMENTS
, A.EFFECTIVE_DATE
, A.ELEMENT_NAME
, A.ENTRY_TYPE
, A.REASON
, A.SEGMENT1
, A.SEGMENT2
, A.SEGMENT3
, A.SEGMENT4
, A.SEGMENT5
, A.SEGMENT6
, A.SEGMENT7
, A.SEGMENT8
, A.SEGMENT9
, A.SEGMENT10
, A.SEGMENT11
, A.SEGMENT12
, A.SEGMENT13
, A.SEGMENT14
, A.SEGMENT15
, A.SEGMENT16
, A.SEGMENT17
, A.SEGMENT18
, A.SEGMENT19
, A.SEGMENT20
, A.SEGMENT21
, A.SEGMENT22
, A.SEGMENT23
, A.SEGMENT24
, A.SEGMENT25
, A.SEGMENT26
, A.SEGMENT27
, A.SEGMENT28
, A.SEGMENT29
, A.SEGMENT30
, A.VALUE_1
, A.VALUE_2
, A.VALUE_3
, A.VALUE_4
, A.VALUE_5
, A.VALUE_6
, A.VALUE_7
, A.VALUE_8
, A.VALUE_9
, A.VALUE_10
, A.VALUE_11
, A.VALUE_12
, A.VALUE_13
, A.VALUE_14
, A.VALUE_15
, A.ATTRIBUTE_CATEGORY
, A.ATTRIBUTE1
, A.ATTRIBUTE2
, A.ATTRIBUTE3
, A.ATTRIBUTE4
, A.ATTRIBUTE5
, A.ATTRIBUTE6
, A.ATTRIBUTE7
, A.ATTRIBUTE8
, A.ATTRIBUTE9
, A.ATTRIBUTE10
, A.ATTRIBUTE11
, A.ATTRIBUTE12
, A.ATTRIBUTE13
, A.ATTRIBUTE14
, A.ATTRIBUTE15
, A.ATTRIBUTE16
, A.ATTRIBUTE17
, A.ATTRIBUTE18
, A.ATTRIBUTE19
, A.ATTRIBUTE20
FROM ( SELECT ET.ELEMENT_NAME
, ET.ELEMENT_TYPE_ID
, IV.NAME
, BH.BUSINESS_GROUP_ID
, ET.LEGISLATION_CODE
, BL.BATCH_LINE_ID
, BL.COST_ALLOCATION_KEYFLEX_ID
, BL.ASSIGNMENT_ID
, BL.BATCH_ID
, BL.BATCH_LINE_STATUS
, BL.ASSIGNMENT_NUMBER
, BL.BATCH_SEQUENCE
, BL.CONCATENATED_SEGMENTS
, BL.EFFECTIVE_DATE
, BL.ENTRY_TYPE
, BL.REASON
, 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
, BL.VALUE_1
, BL.VALUE_2
, BL.VALUE_3
, BL.VALUE_4
, BL.VALUE_5
, BL.VALUE_6
, BL.VALUE_7
, BL.VALUE_8
, BL.VALUE_9
, BL.VALUE_10
, BL.VALUE_11
, BL.VALUE_12
, BL.VALUE_13
, BL.VALUE_14
, BL.VALUE_15
, BL.ATTRIBUTE_CATEGORY
, BL.ATTRIBUTE1
, BL.ATTRIBUTE2
, BL.ATTRIBUTE3
, BL.ATTRIBUTE4
, BL.ATTRIBUTE5
, BL.ATTRIBUTE6
, BL.ATTRIBUTE7
, BL.ATTRIBUTE8
, BL.ATTRIBUTE9
, BL.ATTRIBUTE10
, BL.ATTRIBUTE11
, BL.ATTRIBUTE12
, BL.ATTRIBUTE13
, BL.ATTRIBUTE14
, BL.ATTRIBUTE15
, BL.ATTRIBUTE16
, BL.ATTRIBUTE17
, BL.ATTRIBUTE18
, BL.ATTRIBUTE19
, BL.ATTRIBUTE20
FROM PAY_ELEMENT_TYPES_F ET
, PAY_INPUT_VALUES_F IV
, PAY_BATCH_LINES BL
, PAY_BATCH_HEADERS BH
, PER_BUSINESS_GROUPS BG
WHERE ET.ELEMENT_TYPE_ID = IV.ELEMENT_TYPE_ID
AND ( ((BL.ELEMENT_TYPE_ID IS NOT NULL)
AND ( ET.ELEMENT_TYPE_ID = BL.ELEMENT_TYPE_ID)) OR ((BL.ELEMENT_NAME IS NOT NULL)
AND ( ET.ELEMENT_NAME = BL.ELEMENT_NAME )) )
AND BH.BATCH_ID = BL.BATCH_ID
AND BG.BUSINESS_GROUP_ID = BH.BUSINESS_GROUP_ID
AND BL.EFFECTIVE_DATE BETWEEN IV.EFFECTIVE_START_DATE
AND IV.EFFECTIVE_END_DATE
AND BL.EFFECTIVE_DATE BETWEEN IV.EFFECTIVE_START_DATE
AND IV.EFFECTIVE_END_DATE
AND ( ( IV.BUSINESS_GROUP_ID IS NULL
AND IV.LEGISLATION_CODE = BG.LEGISLATION_CODE ) OR IV.BUSINESS_GROUP_ID = BH.BUSINESS_GROUP_ID )
AND BH.BATCH_NAME = 'BATCH '||TO_CHAR( BH.BATCH_ID ) ORDER BY BL.BATCH_LINE_ID
, ET.ELEMENT_TYPE_ID
, IV.DISPLAY_SEQUENCE
, IV.NAME ) A
, ( SELECT MIN( ROWNUM ) MIN_ROWNUM
, ELEMENT_TYPE_ID
, BATCH_LINE_ID
FROM ( SELECT ET.ELEMENT_TYPE_ID
, IV.NAME
, BL.BATCH_LINE_ID
FROM PAY_ELEMENT_TYPES_F ET
, PAY_INPUT_VALUES_F IV
, PAY_BATCH_LINES BL
, PAY_BATCH_HEADERS BH
, PER_BUSINESS_GROUPS BG
WHERE ET.ELEMENT_TYPE_ID = IV.ELEMENT_TYPE_ID
AND ( ((BL.ELEMENT_TYPE_ID IS NOT NULL)
AND ( ET.ELEMENT_TYPE_ID = BL.ELEMENT_TYPE_ID)) OR ((BL.ELEMENT_NAME IS NOT NULL)
AND ( ET.ELEMENT_NAME = BL.ELEMENT_NAME )) )
AND BH.BATCH_ID = BL.BATCH_ID
AND BG.BUSINESS_GROUP_ID = BH.BUSINESS_GROUP_ID
AND BL.EFFECTIVE_DATE BETWEEN IV.EFFECTIVE_START_DATE
AND IV.EFFECTIVE_END_DATE
AND BL.EFFECTIVE_DATE BETWEEN IV.EFFECTIVE_START_DATE
AND IV.EFFECTIVE_END_DATE
AND ( ( IV.BUSINESS_GROUP_ID IS NULL
AND IV.LEGISLATION_CODE = BG.LEGISLATION_CODE ) OR IV.BUSINESS_GROUP_ID = BH.BUSINESS_GROUP_ID )
AND BH.BATCH_NAME = 'BATCH '||TO_CHAR( BH.BATCH_ID ) ORDER BY BL.BATCH_LINE_ID
, ET.ELEMENT_TYPE_ID
, IV.DISPLAY_SEQUENCE
, IV.NAME ) GROUP BY ELEMENT_TYPE_ID
, BATCH_LINE_ID ) B
WHERE A.ELEMENT_TYPE_ID = B.ELEMENT_TYPE_ID
AND A.BATCH_LINE_ID = B.BATCH_LINE_ID