For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
WITH t AS ( select target_employee.first_name||' ' ||target_employee.last_name as requestername, target_employee.ge_id as requestergeid, target_attendee.first_name||' ' ||target_attendee.last_name as attendeename, target_attendee.attendee_type_flag as attendeetyflg, target_attendee.us_go_attendee_flag as usgoflg, target_attendee.comp_govt_agency_dept as atcomp, target_attendee.country as atcountry, target_aggregation_attendee.request_type as requesttype, target_pa_request.pa_request_id as parequest, target_pa_request.event_date as eventdate, target_pa_request.event_description as eventdesc, target_pa_request.created_date as requestdate, target_pa_request.status as status, target_aggregation_attendee.status, target_aggregation_attendee.be_aggregation_approved_amount as approved_amount, target_aggregation_attendee.level_id, target_aggregation_attendee.be_aggregation_pend_amount as req_amount from target_attendee, target_employee, target_pa_request, target_aggregation_attendee where target_attendee.attendee_id=target_aggregation_attendee.attendee_id and target_employee.ge_id=target_aggregation_attendee.requestor_id and target_aggregation_attendee.pa_request_id=target_pa_request.pa_request_id and to_char(target_aggregation_attendee.created_date,'yyyy')=to_char(sysdate,'yyyy') and target_aggregation_attendee.pa_request_id='nam1421' ) SELECT PAREQUEST,STATUS_1,SUM(approved_amount) approved_amount,LEVEL_ID,AVG(req_amount) req_amount FROM t GROUP BY PAREQUEST,LEVEL_ID,STATUS_1;
SELECT pa_request_id, Max (req_amount) KEEP (DENSE_RANK FIRST ORDER BY level_id) req_amount, Max (CASE WHEN status = 'APPROVED' THEN approved_amount END) approved_amount FROM target_aggregate GROUP BY pa_request_id
select approved_amount from target_aggregate where status='APPROVED' AND PA_REQUEST_ID=&your_input;
SELECT target_employee.first_name ||' ' ||target_employee.last_name AS Requestername, target_employee.ge_id AS requesterGEID, target_attendee.first_name ||' ' ||target_attendee.last_name AS AttendeeName, target_attendee.attendee_type_flag AS Attendeetyflg, target_attendee.us_go_attendee_flag AS usgoflg, target_attendee.comp_govt_agency_dept AS Atcomp, target_attendee.country AS atcountry, target_aggregation_attendee.request_type AS requesttype , target_pa_request.pa_request_id AS parequest, target_pa_request.event_date AS eventdate, target_pa_request.event_description AS eventdesc, target_pa_request.created_date AS requestdate , target_pa_request.status AS status, target_aggregation_attendee.status, target_aggregation_attendee.be_aggregation_approved_amount AS approved_amount, target_aggregation_attendee.level_id, target_aggregation_attendee.be_aggregation_pend_amount AS req_amount FROM target_attendee, target_employee, target_pa_request, target_aggregation_attendee WHERE target_attendee.attendee_id = target_aggregation_attendee.attendee_id AND target_employee.ge_id = target_aggregation_attendee.requestor_id AND target_aggregation_attendee.pa_request_id = target_pa_request.pa_request_id AND To_char(target_aggregation_attendee.created_date, 'YYYY') = To_char(SYSDATE, 'YYYY') AND target_aggregation_attendee.pa_request_id = 'NAM1421';
SELECT pa_request_id, Max (req_amount) KEEP (DENSE_RANK FIRST ORDER BY level_id) req_amount, Min (level_id) level_id, Max (CASE WHEN status = 'APPROVED' THEN approved_amount END) approved_amount FROM target_aggregate GROUP BY pa_request_id