ECC G-Invoicing, Projects: G-Invoicing
Description
Categories: Enterprise Command Center
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
Run
ECC G-Invoicing, Projects: G-Invoicing and other Oracle EBS reports with Blitz Report™ on our demo environment
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< |