ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN EEE75CEB4A1C6AD8E05362FB09051F8B select x.* from ( select ginv.* from ( select case when a.agreement_status = 'Approved' and a.order_process_status in ('FUND_CHK_PENDING', 'MOD_FUND_CHK_PENDING') then 'Pending Funds Check' when decode (count(a.pou_order_id) over(partition by a.order_id), 0, 'No', 'Yes') = 'Yes' then 'Modified' when decode (count(a.pou_order_id) over(partition by a.order_id), 0, 'No', 'Yes') = 'No' and agreement_status = 'Approved' and a.order_process_status not in ('PENDING_CLOSED', 'CLOSED') then 'Approved' when a.rejected_ord = 'Yes' then 'Rejected' when a.new_ord = 'Yes' then 'New' when a.pending_appr_submition_ord = 'Yes' then 'Pending approval submission' when a.pending_approval_ord = 'Yes' then 'Pending approval' /* when a.failed_pull_ord = 'Yes' then 'Failed pull' when a.failed_push_ord = 'Yes' then 'Failed push' */ when a.pending_close_ord = 'Yes' then 'Pending close' end internal_process_status, decode (count(a.pou_order_id) over(partition by a.order_id), 0, 'No', 'Yes') order_mod_uptake_ord, case when decode (count(a.pou_order_id) over(partition by a.order_id), 0, 'No', 'Yes') = 'No' and agreement_status = 'Approved' and a.order_process_status not in ('PENDING_CLOSED', 'CLOSED') then 'Yes' else 'No' end accepted_ord, sum(case when a.adv_ord_yn = 'Yes' and a.ols_rownum = 1 then a.advance_receipt else null end) over (partition by a.order_id) adv_receipt_amt_order_lvl, a.* from ( WITH a AS ( SELECT o.order_id order_id, o.link_project_id pou_o_link_project_id, ols.order_line_schedule_id, ols.link_pobg_id, ols.link_project_id pou_ols_link_project_id, element_version_id, order_schedule_amount FROM pa_proj_elem_ver_structure ppevs, pa_proj_structure_types ppst, pa_igt_order_line_sched ols, pa_igt_order o WHERE ppevs.status_code = 'STRUCTURE_WORKING' AND ppst.structure_type_id = 10 AND ppst.proj_element_id = ppevs.proj_element_id AND ols.link_project_id = ppevs.project_id and o.order_id = ols.order_id ), b as ( SELECT a.order_id pou_order_id, a.pou_o_link_project_id, a.pou_ols_link_project_id, a.order_line_schedule_id, a.order_schedule_amount, ppev.project_id, ppev.proj_element_id, ppev.element_version_id, nvl(ppev.qty, 0) * nvl(ppev.rate, 0) structure_working_amt FROM pa_proj_element_versions ppev, pa_proj_elements ppe, a WHERE ppe.project_id = ppev.project_id and ppe.proj_element_id = a.link_pobg_id AND ppe.project_id = a.pou_ols_link_project_id AND ppe.object_type = 'PA_POBG_LINES' AND ppev.parent_structure_version_id = a.element_version_id AND ppev.proj_element_id = ppe.proj_element_id AND nvl(a.order_schedule_amount, 0) - ( nvl(ppev.qty, 0) * nvl(ppev.rate, 0) ) <> 0 union all select o.order_id pou_order_id, o.link_project_id pou_o_link_project_id, ols.link_project_id pou_ols_link_project_id, order_line_schedule_id, order_schedule_amount, null project_id, null proj_element_id, null element_version_id, null structure_working_amt from pa_igt_order o, pa_igt_order_line_sched ols where o.order_id = ols.order_id and o.link_project_id is not null and ols.link_project_id is null ) select g.gtc_number || '~' || o.order_number || '~' || ol.order_line_number || '~' || ols.order_schedule_number || '~' || nvl(pd.perf_detail_id, 0) ecc_spec_id, -- Phase 1 [start] case when o.link_agreement_id is not null and o.link_project_id is null then 'Yes' else 'No' end pending_proj_creation, b.pou_order_id, b.structure_working_amt, ols.order_schedule_amount, ols.pobg_published_amount, case when o.link_project_id is null and o.order_rejection_code is null and o.order_status_code <> 'REJ' then nvl(ols.order_schedule_amount, 0) else 0 end new_ord_amt, case when o.link_project_id is not null and agr.status = 'W' then nvl(ols.order_schedule_amount, 0) else 0 end pending_appr_submition_ord_amt, case when o.link_project_id is not null and agr.status = 'S' and nvl(o.order_status_code, 'NEW') <> 'CLZ' then nvl(ols.order_schedule_amount, 0) else 0 end pending_approval_ord_amt, case when agr.status = 'A' and o.order_process_status not in ('PENDING_CLOSED', 'CLOSED') then nvl(ols.order_schedule_amount, 0) else 0 end accepted_ord_amt, case when (o.order_status_code = 'REJ') or (agr.status = 'W' and o.order_process_status in ('NEW_PUSH_PENDING', 'NEW_REJ', 'MOD_PUSH_PENDING', 'MOD_REJ' ) ) then nvl(ols.order_schedule_amount, 0) else 0 end rejected_ord_amt, case when o.order_rejection_code is not null then nvl(ols.order_schedule_amount, 0) else 0 end failed_pull_ord_amt, case when o.order_process_status in ('NEW_PUSH_ERROR', 'MOD_PUSH_ERROR') then nvl(ols.order_schedule_amount, 0) else 0 end failed_push_ord_amt, case when b.order_line_schedule_id is not null then nvl(ols.order_schedule_amount, 0) else 0 end order_mod_uptake_amt, --- phase 2 changes [start] case when (o.order_status_code = 'CLZ' and o.order_process_status = 'PENDING_CLOSED') then nvl(ols.order_schedule_amount, 0) else 0 end pending_close_ord_amt, --- phase 2 changes [end] case when o.link_project_id is null and o.order_rejection_code is null and o.order_status_code <> 'REJ' then 'Yes' else 'No' end new_ord, case when o.link_project_id is not null and agr.status = 'W' then 'Yes' else 'No' end pending_appr_submition_ord, case when o.link_project_id is not null and agr.status = 'S' and nvl(o.order_status_code, 'NEW') <> 'CLZ' then 'Yes' else 'No' end pending_approval_ord, case when (o.order_status_code = 'REJ') or (agr.status = 'W' and o.order_process_status in ('NEW_PUSH_PENDING', 'NEW_REJ', 'MOD_PUSH_PENDING', 'MOD_REJ' ) ) then 'Yes' else 'No' end rejected_ord, case when o.order_rejection_code is not null then 'Yes' else 'No' end failed_pull_ord, case when o.order_process_status in ('NEW_PUSH_ERROR', 'MOD_PUSH_ERROR') then 'Yes' else 'No' end failed_push_ord, case when (o.order_status_code = 'CLZ' and o.order_process_status = 'PENDING_CLOSED') then 'Yes' else 'No' end pending_close_ord, decode(agr.status, 'W', 'Work in Progress', 'S', 'Submitted', 'A', 'Approved') agreement_status, p.name project_name, p.segment1 project_number, p.last_update_date proj_last_update_date, agr.agreement_num, case when agr.agreement_id is not null then ( SELECT glsb.currency_code FROM gl_sets_of_books glsb, pa_implementations_all i WHERE i.set_of_books_id = glsb.set_of_books_id and i.org_id = agr.org_id ) else ( SELECT glsb.currency_code FROM gl_sets_of_books glsb, pa_implementations_all i WHERE i.set_of_books_id = glsb.set_of_books_id and i.org_id = (select org_id from igt_document_requests where igt_request_id = (select max(igt_request_id) from igt_document_requests where igt_document_id = g.gtc_number)) ) end currency_code, ( SELECT COUNT(order_id) FROM pa_igt_order WHERE gtc_id = o.gtc_id AND (o.order_rejection_code is null or (order_process_status NOT IN ('REJ_NEW_AGR', 'REJ_MOD_AGR')) ) ) no_of_orders_associated, ols.sa_tas ser_agency_tas, ( SELECT treasury_symbol FROM fv_tp_treasury_symbols WHERE nvl(sub_level_prefix_code, 'N') = nvl(ols.ra_sublevel_prefix_code, 'N') AND nvl(dept_transfer, 'N') = nvl(ols.ra_alloc_trnsfr_agency_id, 'N') AND nvl(agency_id, 'N') = nvl(ols.requesting_agency_id, 'N') AND nvl(established_fiscal_yr, - 1) = nvl(ols.ra_begning_prd_of_avail, - 1) AND nvl(end_year_avail, - 1) = nvl(ols.ra_ending_prd_of_avail, - 1) AND nvl(availability_type_code, 'N') = nvl(ols.ra_availability_type_code, 'N') AND nvl(main_acct_code, 'N') = nvl(ols.ra_main_account_code, 'N') AND nvl(sub_acct_code, 'N') = nvl(ols.ra_sub_account_code, 'N') ) req_agency_tas, g.gtc_id, g.gtc_number, g.req_agency_id, g.ser_agency_id, g.req_agency_location_code, g.ser_agency_location_code, g.ser_agency_agrmt_tracking_id, pa_ecc_igt_pkg.get_lookup_values('IGT_YES_NO', g.assisted_acquisitions_ind) assisted_acquisitions_ind, g.agreement_start_date, pa_ecc_igt_pkg.get_lookup_values('IGT_YES_NO', g.advance_payment_ind) advance_payment_ind, g.req_agency_agrmt_tracking_id, g.status_code, pa_ecc_igt_pkg.get_lookup_values('PA_IGT_GTC_STATUS', g.status_code) status_code_disp, g.comments, g.creation_date g_creation_date, g.created_by g_created_by, g.last_update_date g_last_update_date, g.last_updated_by g_last_updated_by, g.last_update_login g_last_update_login, decode(g.order_originating_ind,'S', 'Servicing Agency', 'Requesting Agency') g_order_originating_ind, gv.gtc_versioning_id, gv.gtc_modification_number, gv.agreement_end_date, pa_ecc_igt_pkg.get_lookup_values('PA_IGT_AGREEMENT_TYPE', gv.agreement_type_code) agreement_type_code, pa_ecc_igt_pkg.get_lookup_values('IGT_YES_NO', gv.enfr_tot_remaining_amount_ind) enfr_tot_remaining_amount_ind, gv.total_direct_cost_amount, gv.total_ovhd_fees_chrg_amount, gv.total_estimated_amount, nvl(gv.total_estimated_amount, 0) - nvl(gv.total_remaining_amount, 0) gtc_ordered_value, gv.ovhd_fees_chrg_amount_expln, gv.ra_scope, gv.ra_roles, gv.sa_roles, gv.restrictions, gv.termination_days, gv.ra_assist_acqi_authorization, gv.sa_assist_acqi_authorization, gv.ra_clauses, gv.sa_clauses, gv.ra_ini_approver_full_name, gv.ra_ini_approver_contact_title, gv.ra_ini_approver_phone_number, gv.ra_ini_approver_fax_number, gv.ra_ini_approver_email_address, gv.ra_ini_approver_signed_date, gv.ra_sec_approver_full_name, gv.ra_sec_approver_contact_title, gv.ra_sec_approver_phone_number, gv.ra_sec_approver_fax_number, gv.ra_sec_approver_email_address, gv.ra_sec_approver_signed_date, gv.sa_ini_approver_full_name, gv.sa_ini_approver_contact_title, gv.sa_ini_approver_phone_number, gv.sa_ini_approver_fax_number, gv.sa_ini_approver_email_address, gv.sa_ini_approver_signed_date, gv.sa_sec_approver_full_name, gv.sa_sec_approver_contact_title, gv.sa_sec_approver_phone_number, gv.sa_sec_approver_fax_number, gv.sa_sec_approver_email_address, gv.sa_sec_approver_signed_date, gv.ra_business_unit, gv.ra_cost_center, gv.ra_department_id, gv.sa_business_unit, gv.sa_cost_center, gv.sa_department_id, gv.gtc_title, gv.total_remaining_amount, gv.gtc_prepered_by_full_name, gv.gtc_prepared_by_phone_number, gv.gtc_prepared_by_email_address, gv.gtc_rejection_reason, gv.creation_date gv_creation_date, gv.created_by gv_created_by, gv.last_update_date gv_last_update_date, gv.last_updated_by gv_last_updated_by, gv.last_update_login gv_last_update_login, o.order_id, o.link_agreement_id, o.link_project_id, pa_ecc_igt_pkg.get_lookup_values('PA_IGT_ACCEPTANCE_POINT', o.acceptance_point_code) acceptance_point_code, pa_ecc_igt_pkg.get_lookup_values('PA_IGT_BILLING_FREQUENCY', o.recurring_freq_code) recurring_freq_code, o.billing_freq_explanation, pa_ecc_igt_pkg.get_lookup_values('PA_IGT_BUY_SELL_INDICATOR', o.buy_sell_indicator) buy_sell_indicator, o.constructive_receipt_days, o.order_acceptance_date, o.order_creation_date, o.order_number, pa_ecc_igt_pkg.get_lookup_values('PA_IGT_ORDER_STATUS', o.order_status_code) order_status_code, o.req_agency_loc_code, ( SELECT customer_name FROM pa_customers_v WHERE customer_number = o.req_agency_loc_code ) req_agency_loc_name, o.req_agency_ord_track_id, o.ser_agency_loc_code, o.ser_agency_ord_track_id, o.perf_period_start_date, o.perf_period_end_date, o.total_advance_amount, o.total_net_order_amount, o.total_order_amount, case when o.order_process_status in ('NEW_PUSH_ERROR', 'MOD_PUSH_ERROR') then (select error_desc from igt_errors_v iev where igt_request_id = (select max(igt_request_id) from igt_document_requests where igt_document_id = o.order_number and igt_request_type = 'UPDATE_ORDER' and igt_request_mode = 'PUSH') and rownum = 1) when o.order_rejection_code is not null then FND_MESSAGE.GET_STRING ('PA', o.order_rejection_code) end order_rejection_code, o.creation_date o_creation_date, o.created_by o_created_by, o.last_update_date o_last_update_date, o.last_updated_by o_last_updated_by, o.last_update_login o_last_update_login, o.order_process_status, decode(o.order_originating_ind,'S', 'Servicing Agency', 'Requesting Agency') o_order_originating_ind, ol.order_line_id, ol.item_code, ol.item_description, ol.order_line_number, ol.order_line_advance_amount, pa_ecc_igt_pkg.get_lookup_values('PA_IGT_ORDER_LINE_STATUS', ol.order_line_status_code) order_line_status_code, ol.order_line_net_due_amount, ol.order_line_quantity, ol.unit_of_measure, ol.total_order_line_amount, pa_ecc_igt_pkg.get_lookup_values('IGT_TRUE_FALSE', ol.capitalized_asset_ind) capitalized_asset_ind, pa_ecc_igt_pkg.get_lookup_values('IGT_TRUE_FALSE', ol.uid_required_ind) uid_required_ind, ol.product_service_ind, pa_ecc_igt_pkg.get_lookup_values('PA_IGT_TYPE_OF_REQUIREMENTS', ol.severable_nonseverable_code) severable_nonseverable_code , ol.unit_of_measure_desc, ol.creation_date ol_creation_date, ol.created_by ol_created_by, ol.last_update_date ol_last_update_date, ol.last_updated_by ol_last_updated_by, ol.last_update_login ol_last_update_login, ols.order_line_schedule_id, pa_ecc_igt_pkg.get_lookup_values('IGT_YES_NO', ols.advance_payment_indicator) advance_payment_indicator, pa_ecc_igt_pkg.get_lookup_values('PA_IGT_ORDER_LINE_SCHED_STATUS', ols.order_schedule_status_code) order_schedule_status_code , --ols.order_schedule_amount, ols.order_schedule_quantity, ols.order_schedule_unit_price, ols.order_schedule_number, ols.sa_activity_id, ols.sa_project_id, pa_ecc_igt_pkg.get_lookup_values('PA_IGT_REIMBURSABLE_FLAG', ols.sa_reimbursable_flag_indicator) sa_reimbursable_flag_indicator , ols.ra_bus_event_type_code, ols.ra_availability_type_code, ols.requesting_agency_id, ols.ra_alloc_trnsfr_agency_id, ols.ra_begning_prd_of_avail, ols.ra_ending_prd_of_avail, ols.ra_main_account_code, ols.ra_sublevel_prefix_code, ols.ra_sub_account_code, ols.link_project_id ols_link_project_id, ols.link_pobg_id, ols.creation_date ols_creation_date, ols.created_by ols_created_by, ols.last_update_date ols_last_update_date, ols.last_updated_by ols_last_updated_by, ols.last_update_login ols_last_update_login, ols.sa_bus_event_type_code, ols.sa_availability_type_code, ols.servicing_agency_id, ols.sa_alloc_trnsfr_agency_id, ols.sa_begning_prd_of_avail, ols.sa_ending_prd_of_avail, ols.sa_main_account_code, ols.sa_sublevel_prefix_code, ols.sa_sub_account_code, null sum_gtc_ordered_value, null sum_remaining_amount, null req_alc_code, null ser_alc_code, null req_subord_grp_name, null ser_subord_grp_name, gv.req_org_grp_id, gv.req_org_grp_name, gv.ser_org_grp_id, gv.ser_org_grp_name, gv.req_dii, gv.ser_dii, o.req_group_id, o.req_group_name, o.ser_group_id, o.ser_group_name, -- Phase 1 [end] -- funding stuff [start] --agr.status, --agr.current_flag, case when nvl(agr.advance_required, 'N') = 'Y' then 'Yes' else 'No' end adv_ord_yn, --pe.advance_amount, pe.last_update_date pe_last_update_date, case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'Y' and (nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0)) > 0 then nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0) when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'Y' and (nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0)) <= 0 then nvl(pe.advance_amount,0) when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'N' then --and (nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0)) <= 0 then nvl(ols.order_schedule_amount,0) end fund_value , case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'Y' and (nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0)) > 0 then 'Advance Pending' when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'Y' and (nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0)) <= 0 then 'Advance Receipt' when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'N' then --and (nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0)) <= 0 then 'Non Advance' end fund_flag ,case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'Y' and pd.performance_type = '548' and upper(pd.perf_process_status) in ('SUBMITTED', 'SUCCESS', 'RECEIPT FAILED', 'RECEIPT SUCCESS') then 'Yes' end advance_performance ,case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'Y' and ols.order_schedule_status_code <> 'C' and nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0) > 0 then 'Yes' end advance_pending_flag /* ,case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'Y' then nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0) end advance_pending_amount */ ,nvl(ols.order_schedule_amount,0) - sum(case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required,'N') = 'Y' and pd.performance_type = '548' and upper(pd.perf_process_status) in ('SUBMITTED', 'SUCCESS', 'RECEIPT FAILED', 'RECEIPT SUCCESS') then nvl(pd.transfer_amt,0) else 0 end) over (partition by ols.order_line_schedule_id) advance_pending_amount ,ols.order_schedule_amount - sum(case when upper(pd.perf_process_status) in ('RECEIPT SUCCESS') then nvl(pd.transfer_amt,0) else 0 end) over (partition by ols.order_line_schedule_id) balance_funding ,case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'N' then ols.order_schedule_amount end non_advance_funding --,nvl(pe.advance_amount,0) advance_receipt ,nvl(o.total_advance_amount,0) advance_receipt ,row_number() over (partition by ols.order_line_schedule_id order by ols.order_line_schedule_id) ols_rownum ,sum(case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'Y' and pd.performance_type = '548' and pd.perf_header_id is not null then nvl(pd.transfer_amt,0) end) over (partition by o.order_id)adv_perf_amt_order_lvl ,sum(case when pd.performance_type = '548' and ph.perf_process_status = 'Failed' then nvl(pd.transfer_amt,0) end) over (partition by o.order_id) adv_failed_amt_order_lvl -- Funding stuff [end] -- Perf stuff [start] ,ph.perf_header_id ,pd.perf_detail_id ,pd.error_desc perf_detail_error_desc ,pd.pobg_id ,pd.cash_receipt_id ,pd.performance_type --perf_type_disp ,case pd.performance_type when '035' then 'Delivery' when '050' then 'Accepted' when '548' then 'Advance' when '014' then 'Deferred' end perf_type ,ph.perf_number ,pd.perf_detail_number ,ph.perf_number_reference ,pd.perf_detail_number_reference ,pd.quantity ,pd.transfer_amt perf_amt ,pd.total_amt ,pd.transfer_amt ,'undelivered balance' undelivered_balance -- this value to be derived. this will be on hold for time being ,ph.status perf_status ,ph.accounting_period ,ph.prep_by_name ,ph.prep_by_phone ,ph.prep_by_email ,case when ph.perf_process_status in ('Success', 'SUCCESS') then 'Success' when ph.perf_process_status in ('SUBMITTED', 'Submitted') then 'Submitted' else ph.perf_process_status end perf_process_status ,case when pd.performance_type = '014' and pd.perf_header_id is not null then pd.transfer_amt end revenue_amt ,ph.last_update_date ph_last_update_date ,pd.last_update_date pd_last_update_date ,case when pd.performance_type = '014' and pd.perf_header_id is null then 'Yes' --pd.transfer_amt end def_perf_pend_sub ,case when pd.performance_type = '035' and pd.perf_header_id is null then 'Yes' --pd.transfer_amt end del_perf_pend_sub ,case when pd.performance_type = '035' and pd.perf_header_id is not null then 'Yes' --pd.transfer_amt end del_perf_submitted -- charts [end] ,case when pd.performance_type = '548' and ph.perf_process_status = 'Failed' then 'Yes' end advance_failed ,case when pd.performance_type = '035' and ph.perf_process_status = 'Failed' then 'Yes' end delivery_failed ,case when pd.performance_type = '014' and ph.perf_process_status = 'Failed' then 'Yes' end deferred_failed -- Failed Perf [Start] -- charts [start] ,sum(case when nvl(agr.advance_required,'N') = 'Y' and pd.performance_type = '035' and pd.perf_header_id is not null then nvl(pd.transfer_amt,0) end) over (partition by ols.order_line_schedule_id) ADV_DEL_PERF ,case when nvl(agr.advance_required,'N') = 'Y' then ols.order_schedule_amount - sum(case when pd.performance_type = '035' and pd.perf_header_id is not null then nvl(pd.transfer_amt,0) else 0 end) over (partition by ols.order_line_schedule_id) end ADV_PEND_DEL ,sum(case when nvl(agr.advance_required,'N') = 'N' AND pd.performance_type = '035' and pd.perf_header_id is not null then nvl(pd.transfer_amt,0) end) over (partition by ols.order_line_schedule_id) NON_ADV_DEL_PERF ,sum(case when nvl(agr.advance_required,'N') = 'N' AND pd.performance_type = '014' and pd.perf_header_id is not null then nvl(pd.transfer_amt,0) end) over (partition by ols.order_line_schedule_id) REVENUE_AT_SCHEDULE_LVL ,sum(case when nvl(agr.advance_required,'N') = 'N' AND pd.performance_type = '014' and pd.perf_header_id is not null then nvl(pd.transfer_amt,0) end) over (partition by ols.order_line_schedule_id) - sum(case when nvl(agr.advance_required,'N') = 'N' AND pd.performance_type = '035' and pd.perf_header_id is not null then nvl(pd.transfer_amt,0) end) over (partition by ols.order_line_schedule_id) DEF_PERF -- Failed Perf [End] -- Performance information in result tables rollups [start] -- deffered pending ,sum(case when pd.performance_type = '014' and pd.perf_header_id is null then nvl(pd.transfer_amt,0) end) over (partition by o.order_id) DEF_PERF_PEND_SUB_ORD ,sum(case when pd.performance_type = '014' and pd.perf_header_id is null then nvl(pd.transfer_amt,0) end) over (partition by ol.order_line_id) DEF_PERF_PEND_SUB_ORD_LINE ,sum(case when pd.performance_type = '014' and pd.perf_header_id is null then nvl(pd.transfer_amt,0) end) over (partition by ols.order_line_schedule_id) DEF_PERF_PEND_SUB_ORD_SCH -- Accrued deferred Perf ,null DEF_PERF_ACCRUED_ORD ,null DEF_PERF_ACCRUED_ORD_LINE ,null DEF_PERF_ACCRUED_ORD_SCH -- delivery pending submission ,sum(case when pd.performance_type = '035' and pd.perf_header_id is null then nvl(pd.transfer_amt,0) end) over (partition by o.order_id) DEL_PERF_PEND_SUB_ORD ,sum(case when pd.performance_type = '035' and pd.perf_header_id is null then nvl(pd.transfer_amt,0) end) over (partition by ol.order_line_id) DEL_PERF_PEND_SUB_ORD_LINE ,sum(case when pd.performance_type = '035' and pd.perf_header_id is null then nvl(pd.transfer_amt,0) end) over (partition by ols.order_line_schedule_id) DEL_PERF_PEND_SUB_ORD_SCH -- delivery submitted ,sum(case when pd.performance_type = '035' and pd.perf_header_id is not null then nvl(pd.transfer_amt,0) end) over (partition by o.order_id) DEL_PERF_SUBMITTED_ORD ,sum(case when pd.performance_type = '035' and pd.perf_header_id is not null then nvl(pd.transfer_amt,0) end) over (partition by ol.order_line_id) DEL_PERF_SUBMITTED_ORD_LINE ,sum(case when pd.performance_type = '035' and pd.perf_header_id is not null then nvl(pd.transfer_amt,0) end) over (partition by ols.order_line_schedule_id) DEL_PERF_SUBMITTED_ORD_SCH -- Advance pending submission ,nvl(ols.order_schedule_amount,0) - sum(case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required,'N') = 'Y' and pd.performance_type = '548' and upper(pd.perf_process_status) in ('SUBMITTED', 'SUCCESS', 'RECEIPT FAILED', 'RECEIPT SUCCESS') then nvl(pd.transfer_amt,0) else 0 end) over (partition by o.order_id) ADV_PERF_PEND_SUB_ORD ,nvl(ols.order_schedule_amount,0) - sum(case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required,'N') = 'Y' and pd.performance_type = '548' and upper(pd.perf_process_status) in ('SUBMITTED', 'SUCCESS', 'RECEIPT FAILED', 'RECEIPT SUCCESS') then nvl(pd.transfer_amt,0) else 0 end) over (partition by ol.order_line_id) ADV_PERF_PEND_SUB_ORD_LINE ,nvl(ols.order_schedule_amount,0) - sum(case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required,'N') = 'Y' and pd.performance_type = '548' and upper(pd.perf_process_status) in ('SUBMITTED', 'SUCCESS', 'RECEIPT FAILED', 'RECEIPT SUCCESS') then nvl(pd.transfer_amt,0) else 0 end) over (partition by ols.order_line_schedule_id) ADV_PERF_PEND_SUB_ORD_SCH -- Advance submitted ,sum(case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'Y' and pd.performance_type = '548' and upper(pd.perf_process_status) in ('SUBMITTED', 'SUCCESS', 'RECEIPT FAILED', 'RECEIPT SUCCESS') then nvl(pd.transfer_amt,0) else 0 end) over (partition by o.order_id) ADV_PERF_SUBMITTED_ORD ,sum(case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'Y' and pd.performance_type = '548' and upper(pd.perf_process_status) in ('SUBMITTED', 'SUCCESS', 'RECEIPT FAILED', 'RECEIPT SUCCESS') then nvl(pd.transfer_amt,0) else 0 end) over (partition by ol.order_line_id) ADV_PERF_SUBMITTED_ORD_LINE ,sum(case when agr.status = 'A' and agr.current_flag = 'Y' and nvl(agr.advance_required, 'N') = 'Y' and pd.performance_type = '548' and upper(pd.perf_process_status) in ('SUBMITTED', 'SUCCESS', 'RECEIPT FAILED', 'RECEIPT SUCCESS') then nvl(pd.transfer_amt,0) else 0 end) over (partition by ols.order_line_schedule_id) ADV_PERF_SUBMITTED_ORD_SCH -- Accepted perf ,sum(case when pd.performance_type = '050' then nvl(pd.transfer_amt,0) end) over (partition by o.order_id) ACC_PERF_ORD ,sum(case when pd.performance_type = '050' then nvl(pd.transfer_amt,0) end) over (partition by ol.order_line_id) ACC_PERF_ORD_LINE ,sum(case when pd.performance_type = '050' then nvl(pd.transfer_amt,0) end) over (partition by ols.order_line_schedule_id) ACC_PERF_ORD_SCH -- Performance information in result tables [end] ,(select max(ar.receipt_amount) from pa_agreement_receipts ar where ar.cash_receipt_id = pd.cash_receipt_id and ar.pobg_id = pd.pobg_id and action = 'A') amount_applied ,(select cr.receipt_number from ar_cash_receipts_all cr where cr.cash_receipt_id = pd.cash_receipt_id) receipt_number ,(select rm.name from ar_receipt_methods rm ,ar_cash_receipts_all cr where cr.receipt_method_id = rm.receipt_method_id and cr.cash_receipt_id = pd.cash_receipt_id) receipt_method -- Perf stuff [end] from b, pa_igt_gtc_base g, ( SELECT CASE WHEN MAX(a.gtc_versioning_id) OVER( PARTITION BY a.gtc_id ) = a.gtc_versioning_id THEN 1 ELSE 0 END max_modification_number, a.* FROM pa_igt_gtc_versioning a ) gv, pa_igt_order o, pa_igt_order_line ol, pa_igt_order_line_sched ols, pa_projects_all p, pa_agreement_versions agr ,pa_proj_elements pe ,(select pd.perf_detail_id, pd.pobg_id from (select distinct perf_header_id from (select max(x.perf_header_id) perf_header_id from pa_proj_ginv_perf_detail x, pa_proj_ginv_perf_header y where x.perf_header_id = y.perf_header_id and x.performance_type = '014' group by y.accounting_period, x.project_id, x.pobg_id ) ) a ,pa_proj_ginv_perf_header ph ,pa_proj_ginv_perf_detail pd where a.perf_header_id = pd.perf_header_id and ph.perf_header_id = pd.perf_header_id union all select pd.perf_detail_id, pd.pobg_id from pa_proj_ginv_perf_detail pd, pa_proj_ginv_perf_header ph where ph.perf_header_id (+) = pd.perf_header_id and nvl(pd.quantity,0) <> 0 and not (pd.performance_type = '014' and pd.perf_header_id is not null) ) z, pa_proj_ginv_perf_header ph, pa_proj_ginv_perf_detail pd where g.gtc_id = gv.gtc_id AND g.status_code = 'REC' AND gv.max_modification_number = 1 AND g.gtc_id = o.gtc_id (+) AND o.order_id = ol.order_id (+) AND ol.order_line_id = ols.order_line_id (+) AND ols.order_line_schedule_id = b.order_line_schedule_id(+) -- ols.link_project_id and ols.link_pobg_id = pe.proj_element_id (+) AND o.link_project_id = p.project_id (+) AND o.link_agreement_id = agr.agreement_id (+) AND agr.current_flag (+) = 'Y' --AND o.order_status_code (+) <> 'CLZ' and ols.link_pobg_id = z.pobg_id (+) and z.perf_detail_id = pd.perf_detail_id (+) and pd.perf_header_id = ph.perf_header_id (+) ) a ) ginv ) x where 2=2 Y AR ECC تحرير الفواتير, المشروعات: تحرير فواتير حكومية Imported from Enterprise Command Center Dataset Key: IGT_ORDER Query Procedure: pa_ecc_igt_pkg.load_full_igt_data Security Procedure: PA_ECC_IGT_DATASECURITY_PKG.GetFilterAttributeValues E ECC G-Invoicing, Proyectos: G-Invoicing Imported from Enterprise Command Center Dataset Key: IGT_ORDER Query Procedure: pa_ecc_igt_pkg.load_full_igt_data Security Procedure: PA_ECC_IGT_DATASECURITY_PKG.GetFilterAttributeValues F ECC G-Invoicing, Projets : G-Invoicing Imported from Enterprise Command Center Dataset Key: IGT_ORDER Query Procedure: pa_ecc_igt_pkg.load_full_igt_data Security Procedure: PA_ECC_IGT_DATASECURITY_PKG.GetFilterAttributeValues JA ECC G-請求, プロジェクト: G-請求 Imported from Enterprise Command Center Dataset Key: IGT_ORDER Query Procedure: pa_ecc_igt_pkg.load_full_igt_data Security Procedure: PA_ECC_IGT_DATASECURITY_PKG.GetFilterAttributeValues KO ECC G-Invoicing, 프로젝트: G-Invoicing Imported from Enterprise Command Center Dataset Key: IGT_ORDER Query Procedure: pa_ecc_igt_pkg.load_full_igt_data Security Procedure: PA_ECC_IGT_DATASECURITY_PKG.GetFilterAttributeValues RU ECC G-Invoicing, Проекты: G-Invoicing Imported from Enterprise Command Center Dataset Key: IGT_ORDER Query Procedure: pa_ecc_igt_pkg.load_full_igt_data Security Procedure: PA_ECC_IGT_DATASECURITY_PKG.GetFilterAttributeValues S ECC G-Invoicing, Projekt: G-Invoicing Imported from Enterprise Command Center Dataset Key: IGT_ORDER Query Procedure: pa_ecc_igt_pkg.load_full_igt_data Security Procedure: PA_ECC_IGT_DATASECURITY_PKG.GetFilterAttributeValues TR ECC G-Faturalama, Projeler: G-Faturalama Imported from Enterprise Command Center Dataset Key: IGT_ORDER Query Procedure: pa_ecc_igt_pkg.load_full_igt_data Security Procedure: PA_ECC_IGT_DATASECURITY_PKG.GetFilterAttributeValues US ECC G-Invoicing, Projects: G-Invoicing Imported from Enterprise Command Center Dataset Key: IGT_ORDER Query Procedure: pa_ecc_igt_pkg.load_full_igt_data Security Procedure: PA_ECC_IGT_DATASECURITY_PKG.GetFilterAttributeValues Enterprise Command Center