ECC G-Invoicing, Projects: G-Invoicing

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<