SQL Script to identify transactions eligible for GMS Grants Billing

The output of the below script will provide you the details of PA expenditure transactions which are eligible for the creation of Grants Invoice.

The programs "GMS: Generate Draft Invoices for Range Of Awards" or "GMS: Generate Draft Invoices for Single Award" will fetch this data to create a Grants Invoice. Its a very useful script to diagnose why the above mentioned programs did not create a Grants Invoice.


Select ga.award_number, p.segment1 project_number
       , t3.task_number, ei.expenditure_item_id
       , adl.adl_line_num, adl.raw_cost, ei.expenditure_type
       , nvl(ei.override_to_organization_id,e.incurred_by_organization_id) Exp_Org
       , ei.expenditure_item_date, adl.cdl_line_num
from pa_expenditure_items_all ei, pa_expenditures_all e
   , pa_expenditure_types et, gms_award_distributions adl
   , pa_projects_all p, pa_project_types_all pt
   , pa_tasks t3, pa_tasks t5, gms_awards_all ga
where ga.award_number = '&Enter_award_number'
  and ga.award_id = adl.award_id
  and ei.expenditure_item_id = adl.expenditure_item_id
  and ((adl.line_num_reversed is null and adl.reversed_flag is null
        and ei.cost_distributed_flag='Y') or
       ((adl.line_num_reversed is not null or adl.reversed_flag is not null)
         and adl.cost_distributed_flag = 'Y'))
  and nvl(adl.billable_flag,'N')='Y'
  and ei.system_linkage_function <> 'BTC'
  and nvl(adl.revenue_distributed_flag,'N') <> 'Y'
  and adl.document_type = 'EXP'
  and adl.adl_status = 'A'
  and adl.fc_status = 'A'
  and ei.expenditure_type = et.expenditure_type
  and e.expenditure_id = ei.expenditure_id
  and ei.task_id = t3.task_id
  and t3.top_task_id = t5.task_id
  and t5.ready_to_distribute_flag = 'Y'
  and t3.project_id = p.project_id
  and p.project_status_code <> 'CLOSED'
  and pt.project_type = p.project_type
  and pt.direct_flag = 'N'
  and exists ( select 1
               from gms_summary_project_fundings gspf, gms_installments gi
               where gi.award_id = adl.award_id
                 and gspf.installment_id = gi.installment_id
                 and nvl(gi.billable_flag,'N') = 'Y'
                 and gspf.project_id = adl.project_id
                 and trunc(ei.expenditure_item_date) <= trunc(gi.end_date_active)
                 and (gspf.task_id is NULL or
                      gspf.task_id = adl.task_id or
                      gspf.task_id = t3.top_task_id)

              );