ECC G-Invoicing, Projects: G-Invoicing
Description
Categories: Enterprise Command Center
Columns: Order Mod Uptake Ord, Distinct Gtc, Gtc Metric Driver, Pou Order Id, Structure Working Amt, Order Schedule Amount, Pobg Published Amount, New Ord Amt, Pending Appr Submition Ord Amt, Pending Approval Ord Amt ...
Columns: Order Mod Uptake Ord, Distinct Gtc, Gtc Metric Driver, Pou Order Id, Structure Working Amt, Order Schedule Amount, Pobg Published Amount, New Ord Amt, Pending Appr Submition Ord Amt, Pending Approval Ord Amt ...
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
Dataset Key: IGT_ORDER
Query Procedure: pa_ecc_igt_pkg.load_full_igt_data
Security Procedure: PA_ECC_IGT_DATASECURITY_PKG.GetFilterAttributeValues
select x.* from ( select * from ( select decode (count(a.pou_order_id) over(partition by a.order_id), 0, 'No', 'Yes') order_mod_uptake_ord, decode (min(a.gtc_metric_driver) over (partition by a.gtc_id), a.gtc_metric_driver, 'Yes', 'No') distinct_gtc, 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 '9' || lpad(g.gtc_id, 10, '0') || lpad(gv.gtc_versioning_id, 10, '0') || lpad(o.order_id,10, '0') || lpad(ol.order_line_id, 10, '0') || lpad(ols.order_line_schedule_id, 10, '0') gtc_metric_driver, 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 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' then nvl(ols.order_schedule_amount, 0) else 0 end pending_approval_ord_amt, case when agr.status = 'A' 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, case when o.link_project_id is null and o.order_rejection_code is null 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' then 'Yes' else 'No' end pending_approval_ord, case when agr.status = 'A' then 'Yes' else 'No' end accepted_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, decode(agr.status, 'W', 'Work in Progress', 'S', 'Submitted', 'A', 'Approved') agreement_status, g.gtc_number || '~' || o.order_number || '~' || ol.order_line_number || '~' || ols.order_schedule_number ecc_spec_id, p.name project_name, p.segment1 project_number, agr.agreement_num, ( SELECT gl.currency_code FROM gl_sets_of_books gl, pa_implementations_all i WHERE i.set_of_books_id = gl.set_of_books_id AND i.ser_agency_loc_code = o.ser_agency_loc_code ) 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, ( SELECT treasury_symbol FROM fv_treasury_symbols WHERE nvl(sub_level_prefix_code, 'N') = nvl(ols.sa_sublevel_prefix_code, 'N') AND nvl(dept_transfer, 'N') = nvl(ols.sa_alloc_trnsfr_agency_id, 'N') AND nvl(department_id, 'N') = nvl(ols.servicing_agency_id, 'N') AND nvl(established_fiscal_yr, - 1) = nvl(ols.sa_begning_prd_of_avail, - 1) AND nvl(end_year_avail, - 1) = nvl(ols.sa_ending_prd_of_avail, - 1) AND nvl(availability_type_code, 'N') = nvl(ols.sa_availability_type_code, 'N') AND nvl(fund_group_code, 'N') = nvl(ols.sa_main_account_code, 'N') AND nvl(tafs_sub_acct, 'N') = nvl(ols.sa_sub_account_code, 'N') AND Nvl(set_of_books_id, -1) = nvl((select set_of_books_id from pa_implementations_all where ser_agency_loc_code = o.ser_agency_loc_code), -1) ) 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, 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, FND_MESSAGE.GET_STRING ('PA', o.order_rejection_code) 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, 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_TRUE_FALSE', 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 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 WHERE g.gtc_id = gv.gtc_id AND gv.max_modification_number = 1 AND g.gtc_id = o.gtc_id AND o.order_id = ol.order_id AND b.order_line_schedule_id(+) = ols.order_line_schedule_id -- ols.link_project_id AND ol.order_line_id = ols.order_line_id AND p.project_id (+) = o.link_project_id AND agr.agreement_id (+) = o.link_agreement_id AND agr.current_flag = 'Y' AND nvl(o.order_status_code, 'CLZ') != 'CLZ' union all SELECT '9' || lpad(g.gtc_id, 10, '0') || lpad(gv.gtc_versioning_id, 10, '0') gtc_metric_driver, null pou_order_id, null structure_working_amt, null order_schedule_amount, null pobg_published_amount, null new_ord_amt, null pending_appr_submition_ord_amt, null pending_approval_ord_amt, null accepted_ord_amt, null rejected_ord_amt, null failed_pull_ord_amt, null failed_push_ord_amt, null order_mod_uptake_amt, null new_ord, null pending_appr_submition_ord, null pending_approval_ord, null accepted_ord, null rejected_ord, null failed_pull_ord, null failed_push_ord, null agreement_status, g.gtc_number ecc_spec_id, null project_name, null project_number, null agreement_num, ( SELECT gl.currency_code FROM gl_sets_of_books gl, pa_implementations_all i WHERE i.set_of_books_id = gl.set_of_books_id AND i.ser_agency_loc_code = g.ser_agency_location_code ) currency_code, null no_of_orders_associated, null ser_agency_tas, null 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, 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, null order_id, null link_agreement_id, null link_project_id, null acceptance_point_code, null recurring_freq_code, null billing_freq_explanation, null buy_sell_indicator, null constructive_receipt_days, null order_acceptance_date, null order_creation_date, null order_number, null order_status_code, null req_agency_loc_code, null req_agency_loc_name, null req_agency_ord_track_id, null ser_agency_loc_code, null ser_agency_ord_track_id, null perf_period_start_date, null perf_period_end_date, null total_advance_amount, null total_net_order_amount, null total_order_amount, null order_rejection_code, null o_creation_date, null o_created_by, null o_last_update_date, null o_last_updated_by, null o_last_update_login, null order_process_status, null order_line_id, null item_code, null item_description, null order_line_number, null order_line_advance_amount, null order_line_status_code, null order_line_net_due_amount, null order_line_quantity, null unit_of_measure, null total_order_line_amount, null capitalized_asset_ind, null uid_required_ind, null product_service_ind, null severable_nonseverable_code , null unit_of_measure_desc, null ol_creation_date, null ol_created_by, null ol_last_update_date, null ol_last_updated_by, null ol_last_update_login, null order_line_schedule_id, null advance_payment_indicator, null order_schedule_status_code , --null order_schedule_amount, null order_schedule_quantity, null order_schedule_unit_price, null order_schedule_number, null sa_activity_id, null sa_project_id, null sa_reimbursable_flag_indicator , null ra_bus_event_type_code, null ra_availability_type_code, null requesting_agency_id, null ra_alloc_trnsfr_agency_id, null ra_begning_prd_of_avail, null ra_ending_prd_of_avail, null ra_main_account_code, null ra_sublevel_prefix_code, null ra_sub_account_code, null ols_link_project_id, null link_pobg_id, null ols_creation_date, null ols_created_by, null ols_last_update_date, null ols_last_updated_by, null ols_last_update_login, null sa_bus_event_type_code, null sa_availability_type_code, null servicing_agency_id, null sa_alloc_trnsfr_agency_id, null sa_begning_prd_of_avail, null sa_ending_prd_of_avail, null sa_main_account_code, null sa_sublevel_prefix_code, null sa_sub_account_code, null sum_gtc_ordered_value, null sum_remaining_amount FROM 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 WHERE g.gtc_id = gv.gtc_id AND gv.max_modification_number = 1 AND g.status_code = 'REC' AND g.gtc_id = o.gtc_id (+) and o.gtc_id is null ) a ) ) x where 2=2 |