[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** the selected payroll or GRE
** Purpose : This procedure is used to get the concatenated
** title information for the third table that
** contains consolidated costing totals for the
** selected payroll or GRE
************************************************************/
PROCEDURE formated_cons_totals_header1(p_sort_order1 in VARCHAR2
,p_output_file_type in VARCHAR2
,p_static_label out nocopy VARCHAR2
)
IS
lv_format VARCHAR2(32000);
** the selected payroll or GRE
** Purpose : This procedure is used to get the concatenated
** data values for the third table that
** contains consolidated costing totals for the
** selected payroll or GRE
************************************************************/
PROCEDURE formated_cons_totals1(p_gre_or_payroll in VARCHAR2
,p_uom in VARCHAR2
,p_credit_amount in NUMBER
,p_debit_amount in NUMBER
,p_output_file_type in VARCHAR2
,p_static_data out nocopy VARCHAR2
) IS
lv_format VARCHAR2(32000);
SELECT name
FROM hr_organization_units
WHERE organization_id=cp_organization_id;
SELECT payroll_name
FROM pay_payrolls_f
WHERE payroll_id = cp_payroll_id;
SELECT consolidation_set_name
FROM pay_consolidation_sets
WHERE consolidation_set_id=cp_consolidation_set_id;
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id=cp_payroll_action_id;
SELECT nvl(hr_general.decode_lookup('PAY_PAYRPCBR',cp_cost_type),' ')
FROM dual;
SELECT org_information7
FROM hr_organization_information hoi
WHERE organization_id = cp_business_group_id
and org_information_context = 'Business Group Information';
SELECT segment_name, application_column_name
FROM fnd_id_flex_segments
WHERE id_flex_code = 'COST'
and id_flex_num = cp_id_flex_num
and enabled_flag = 'Y'
and display_flag = 'Y'
ORDER BY segment_num;
SELECT userenv('sessionid')
FROM dual;
SELECT decode(upper(cp_sort_order1),'PAYROLL NAME',attribute32
,gre_name)
,attribute34 --UOM
,sum(value1)
,sum(value2)
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
FROM pay_us_rpt_totals
WHERE business_group_id=cp_business_group_id
and attribute31=cp_csr
and session_id=cp_session_id
GROUP BY decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute32,
gre_name)
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,attribute34
order by
attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,attribute34
;
SELECT attribute34 --UOM
,sum(value1)
,sum(value2)
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
FROM pay_us_rpt_totals
WHERE business_group_id=cp_business_group_id
AND attribute31=cp_csr
AND session_id=cp_session_id
GROUP BY
attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,attribute34
order by
attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,attribute34
;
SELECT decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute32,
gre_name)
,attribute34 --UOM
,sum(value1)
,sum(value2)
FROM pay_us_rpt_totals
WHERE session_id=cp_session_id
AND business_group_id=cp_business_group_id
AND attribute31=cp_total_flag
GROUP BY decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute32,
gre_name)
,attribute34;
SELECT attribute34 --UOM
,sum(value1)
,sum(value2)
FROM pay_us_rpt_totals
WHERE session_id=cp_session_id
AND business_group_id=cp_business_group_id
AND attribute31=cp_total_flag
GROUP BY attribute34;
c_query := 'SELECT
pcd.payroll_name
,pcd.gre_name
,pcd.input_value_name
,pcd.uom
,sum(pcd.credit_amount)
,sum(pcd.debit_amount)
,pcd.cost_type
,pcd.concatenated_segments
,pcd.segment1
,pcd.segment2
,pcd.segment3
,pcd.segment4
,pcd.segment5
,pcd.segment6
,pcd.segment7
,pcd.segment8
,pcd.segment9
,pcd.segment10
,pcd.segment11
,pcd.segment12
,pcd.segment13
,pcd.segment14
,pcd.segment15
,pcd.segment16
,pcd.segment17
,pcd.segment18
,pcd.segment19
,pcd.segment20
,pcd.segment21
,pcd.segment22
,pcd.segment23
,pcd.segment24
,pcd.segment25
,pcd.segment26
,pcd.segment27
,pcd.segment28
,pcd.segment29
,pcd.segment30
FROM pay_costing_details_v pcd
WHERE
pcd.effective_date between :cp_start_date and :cp_end_date
' || c_clause1 || '
and pcd.business_group_id = :cp_business_group_id
GROUP BY pcd.payroll_name,pcd.gre_name
,pcd.input_value_name
,pcd.uom,pcd.cost_type
,pcd.concatenated_segments
,pcd.segment1
,pcd.segment2
,pcd.segment3
,pcd.segment4
,pcd.segment5
,pcd.segment6
,pcd.segment7
,pcd.segment8
,pcd.segment9
,pcd.segment10
,pcd.segment11
,pcd.segment12
,pcd.segment13
,pcd.segment14
,pcd.segment15
,pcd.segment16
,pcd.segment17
,pcd.segment18
,pcd.segment19
,pcd.segment20
,pcd.segment21
,pcd.segment22
,pcd.segment23
,pcd.segment24
,pcd.segment25
,pcd.segment26
,pcd.segment27
,pcd.segment28
,pcd.segment29
,pcd.segment30
ORDER BY pcd.cost_type
,decode (upper(:cp_sort_order1), ''PAYROLL NAME'', pcd.payroll_name,
pcd.gre_name)
,decode(upper(:cp_sort_order2), ''GRE'', pcd.gre_name,''PAYROLL NAME'',
pcd.payroll_name,''X'')
,pcd.segment1
,pcd.segment2
,pcd.segment3
,pcd.segment4
,pcd.segment5
,pcd.segment6
,pcd.segment7
,pcd.segment8
,pcd.segment9
,pcd.segment10
,pcd.segment11
,pcd.segment12
,pcd.segment13
,pcd.segment14
,pcd.segment15
,pcd.segment16
,pcd.segment17
,pcd.segment18
,pcd.segment19
,pcd.segment20
,pcd.segment21
,pcd.segment22
,pcd.segment23
,pcd.segment24
,pcd.segment25
,pcd.segment26
,pcd.segment27
,pcd.segment28
,pcd.segment29
,pcd.segment30';
/*insert into pay_us_rpt_totals*/
/*sackumar : this data is used in the report for geting the other section
here atrributes1 to 30 used to store the values of the Segments 1 to 30
and Attribute31 = 'CSR'
Attribute32 = Payroll Name
Attribute33 = Concatenated Segments Value
Attribute34 = UOM
*/
insert into pay_us_rpt_totals(session_id,business_group_id,gre_name,value1 ,value2 ,attribute1,attribute2
,attribute3,attribute4,attribute5,attribute6,attribute7,attribute8
,attribute9,attribute10,attribute11,attribute12,attribute13
,attribute14,attribute15,attribute16,attribute17,attribute18
,attribute19,attribute20,attribute21,attribute22,attribute23
,attribute24,attribute25,attribute26,attribute27,attribute28
,attribute29,attribute30,attribute31,attribute32,attribute33
,attribute34) values
(lv_session_id -- session ID is passed
,p_business_group_id
,lv_gre_name
,ln_credit_amount
,ln_debit_amount
,lv_segment1
,lv_segment2
,lv_segment3
,lv_segment4
,lv_segment5
,lv_segment6
,lv_segment7
,lv_segment8
,lv_segment9
,lv_segment10
,lv_segment11
,lv_segment12
,lv_segment13
,lv_segment14
,lv_segment15
,lv_segment16
,lv_segment17
,lv_segment18
,lv_segment19
,lv_segment20
,lv_segment21
,lv_segment22
,lv_segment23
,lv_segment24
,lv_segment25
,lv_segment26
,lv_segment27
,lv_segment28
,lv_segment29
,lv_segment30
,'CSR' --attribute31 -- denotes that the record is for Costing Summary Report
,lv_payroll_name --attribute32
,lv_concatenated_segments --attribute33
,lv_uom --attribute34
);
DELETE FROM pay_us_rpt_totals where attribute31='CSR';