[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TEMPLATE_NAME
INTO l_template_name
FROM XDO_TEMPLATES_TL
WHERE APPLICATION_SHORT_NAME= P_App_Short_Name
AND TEMPLATE_CODE= P_Template_Code
AND LANGUAGE=userenv('LANG');
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 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 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;
pay_prl_xml_utils.gXMLTable.DELETE;
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*/
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
);
pay_prl_xml_utils.gXMLTable.delete;
pay_prl_xml_utils.gXMLTable.delete;
pay_prl_xml_utils.gXMLTable.delete;
pay_prl_xml_utils.gXMLTable.delete;
pay_prl_xml_utils.gXMLTable.delete;
pay_prl_xml_utils.gXMLTable.delete;
pay_prl_xml_utils.gXMLTable.delete;
pay_prl_xml_utils.gXMLTable.delete;
pay_prl_xml_utils.gXMLTable.delete;
DELETE FROM pay_us_rpt_totals where attribute31='CSR';