ECC G-Invoicing, Projects: G-Invoicing

➜ Back to Library
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 ...
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
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
Download
Blitz Report In Action
Blitz Report™