ECC G-Invoicing, Projects: G-Invoicing

select
x.*
from
(
 select * from (
 select
     phase1.gtc_number
    || '~'
    || phase1.order_number
    || '~'
    || phase1.order_line_number
    || '~'
    || phase1.order_schedule_number
    || '~'
    || phase2.perf_header_id
    || '~'
    || phase2.perf_detail_id
    || '~'
    || phase2.fund_flag ecc_spec_id
    ,phase1.*,
     phase2.*
 from (   -- g_pa_ecc_ginv_sql_p1 [start]
 select 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' then 'Yes'
			else
			  'No'
        end accepted_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
        case
            when o.link_agreement_id is not null and o.link_project_id is null then
                'Yes'
             else
                'No'
         end pending_proj_creation,
       '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 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' 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 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' 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,
 
        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,
		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,
        (
            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,
 
        sum(case
            when o.order_process_status in ('NEW_REC', 'MOD_REC') then
                 nvl(ols.order_schedule_amount,0)
            else
                0
        end) over (partition by g.gtc_id) 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,
		nvl(gv.total_estimated_amount, 0) -
			(sum(case
				when o.order_process_status in ('NEW_REC', 'MOD_REC') then
					 nvl(ols.order_schedule_amount,0)
				else
					0
			end) over (partition by g.gtc_id)) 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,
        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,
 
		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
 
    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
		null pending_proj_creation,
       '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 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 proj_last_update_date,
        null agreement_num,
 
/*
        (
            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.ser_agency_loc_code = g.ser_agency_location_code
        ) currency_code,
*/
 
        (
            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))
        ) 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,
 
		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,
		null req_group_id,
		null req_group_name,
		null ser_group_id,
		null ser_group_name
 
    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
) phase1
  ,   -- g_pa_ecc_ginv_sql_p2 [start]
  (
select
  --(max(nvl(q.order_schedule_amount_perf,0)) over (partition by q.pobg_id)) - nvl(accepted_perf,0) balance_funding
  --nvl(q.order_schedule_amount_perf,0) - nvl(accepted_perf,0) balance_funding
  nvl(q.order_schedule_amount_perf,0) - sum(nvl(accepted_perf,0)) over (partition by q.pobg_id)  balance_funding
  ,q.*
from
(select
    a.*
    ,b.*
	,ar.receipt_method
	,ar.receipt_number
	,ar.ar_receipt_amount
from
(
select
    ols.order_schedule_amount order_schedule_amount_perf
    ,o.order_number order_number_perf
    ,ol.order_line_number order_line_number_perf
    ,ols.order_schedule_number order_schedule_number_perf
    ,'Yes' advance_required
    ,nvl(pe.advance_amount,0) advance_amount
    ,'Advance Receipt' fund_flag
    ,nvl(pe.advance_amount,0) fund_value
    ,0 advance_pending
    ,null advance_pending_flag
    ,pe.proj_element_id
    ,o.order_id order_id_perf
    ,ol.order_line_id order_line_id_perf
    ,ols.order_line_schedule_id order_line_schedule_id_perf
    ,pe.last_update_date pe_last_update_date
from
     pa_proj_elements pe
     ,pa_agreement_versions av
     ,pa_igt_order o
     ,pa_igt_order_line ol
     ,pa_igt_order_line_sched ols
where o.order_id = ol.order_id
and   ol.order_line_id = ols.order_line_id
and   o.order_id = ols.order_id
and   ols.link_pobg_id = pe.proj_element_id
and   o.link_agreement_id = av.agreement_id
and   av.status = 'A'
and   av.current_flag = 'Y'
and   nvl(av.advance_required, 'N') = 'Y'
union all
select
    ols.order_schedule_amount order_schedule_amount_perf
    ,o.order_number order_number_perf
    ,ol.order_line_number order_line_number_perf
    ,ols.order_schedule_number order_schedule_number_perf
    ,'Yes' advance_required
    ,nvl(pe.advance_amount,0) advance_amount
    ,'Advance Pending' fund_flag
    ,nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0) fund_value
    ,nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0) advance_pending
    ,case
        when (nvl(ols.order_schedule_amount,0) - nvl(pe.advance_amount,0)) > 0 then 'Yes'
        else null
    end advance_pending_flag
    ,pe.proj_element_id
    ,o.order_id order_id_perf
    ,ol.order_line_id order_line_id_perf
    ,ols.order_line_schedule_id order_line_schedule_id_perf
    ,pe.last_update_date pe_last_update_date
from
     pa_proj_elements pe
     ,pa_agreement_versions av
     ,pa_igt_order o
     ,pa_igt_order_line ol
     ,pa_igt_order_line_sched ols
where o.order_id = ol.order_id
and   ol.order_line_id = ols.order_line_id
and   o.order_id = ols.order_id
and   ols.link_pobg_id = pe.proj_element_id
and   o.link_agreement_id = av.agreement_id
and   av.status = 'A'
and   av.current_flag = 'Y'
and   nvl(av.advance_required, 'N') = 'Y'
union all
select
    ols.order_schedule_amount order_schedule_amount_perf
    ,o.order_number order_number_perf
    ,ol.order_line_number order_line_number_perf
    ,ols.order_schedule_number order_schedule_number_perf
    ,'No' advance_required
    ,nvl(pe.advance_amount,0) advance_amount
    ,'Non-Advance' fund_flag
    ,nvl(ols.order_schedule_amount,0) fund_value
    ,0 advance_pending
    ,null advance_pending_flag
    ,pe.proj_element_id
    ,o.order_id order_id_perf
    ,ol.order_line_id order_line_id_perf
    ,ols.order_line_schedule_id order_line_schedule_id_perf
    ,pe.last_update_date pe_last_update_date
from
     pa_proj_elements pe
     ,pa_agreement_versions av
     ,pa_igt_order o
     ,pa_igt_order_line ol
     ,pa_igt_order_line_sched ols
where o.order_id = ol.order_id
and   ol.order_line_id = ols.order_line_id
and   o.order_id = ols.order_id
and   ols.link_pobg_id = pe.proj_element_id
and   o.link_agreement_id = av.agreement_id
and   av.status = 'A'
and   av.current_flag = 'Y'
and   nvl(av.advance_required, 'N') = 'N'
) a
,  (select
   -- deffered perf pending  014
    sum(case
        when a.perfor