[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',attribute2
,gre_name)
,attribute24 --UOM
,sum(value1)
,sum(value2)
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
FROM pay_us_rpt_totals
WHERE business_group_id=cp_business_group_id
AND cp_csr='CSR'
AND session_id=cp_session_id
GROUP BY decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute2,
gre_name)
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute24
;
SELECT attribute24
,sum(value1)
,sum(value2)
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
FROM pay_us_rpt_totals
WHERE business_group_id=cp_business_group_id
AND cp_csr='CSR'
AND session_id=cp_session_id
GROUP BY
attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute24
;
SELECT decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute2,
gre_name)
,attribute24 --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 attribute1=cp_total_flag
GROUP BY decode(upper(cp_sort_order1), 'PAYROLL NAME',attribute2,
gre_name)
,attribute24;
SELECT attribute24 --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 attribute1=cp_total_flag
GROUP BY attribute24;
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
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
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'')';
/*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) values
(lv_session_id -- session ID is passed
,p_business_group_id
,lv_gre_name
,ln_credit_amount
,ln_debit_amount
,'CSR' -- denotes that the record is for Costing Summary Report
,lv_payroll_name
,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_concatenated_segments
,lv_uom);
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 attribute1='CSR';