APPS.GMS_REPORT_SF270 SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 4

Procedure INSERT_GMS_270_HISTORY(X_Award_Id IN NUMBER
				,X_Document_Number IN VARCHAR2
				,X_Total_Program_Outlays IN NUMBER
	 			,X_Version IN NUMBER
				,X_Status_Code IN VARCHAR2
				,X_Report_Start_Date IN DATE
				,X_Report_End_Date   IN DATE
				--,X_Payee_Address_Id IN NUMBER Bug 2537999
				,X_Err_Code OUT NOCOPY VARCHAR2
				,X_Err_Stage OUT NOCOPY VARCHAR2) IS

X_Cum_Program_Income NUMBER := 0;

Line: 22

 INSERT INTO GMS_270_HISTORY
 (
  AWARD_ID
 ,VERSION
 ,STATUS_CODE
 ,RUN_DATE
 ,DOCUMENT_NUMBER
 ,ACCOUNTING_BASIS
 ,PAYMENT_TYPE
 ,PAYMENT_SCHEDULE
 ,REPORT_START_DATE
 ,REPORT_END_DATE
 ,CREATION_DATE
 ,CREATED_BY
 ,LAST_UPDATE_DATE
 ,LAST_UPDATED_BY
 ,LAST_UPDATE_LOGIN
 ,TOT_PRG_OUTLAY
 ,CUM_PRG_INCOME
 ,NET_CASH_OUTLAY
 ,NON_FED_SHARE
-- ,PAYEE_ADDRESS_ID
 ,REMARKS
   )
values
(
 X_Award_Id
,X_Version
,X_Status_Code
,SYSDATE
,X_Document_Number
,'A'
,'A'
,'F'
,X_Report_Start_Date
,X_Report_End_Date
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,X_Total_Program_Outlays
,X_Cum_Program_Income
,X_Net_Cash_Outlays
,X_Non_Fed_Share
--,X_Payee_Address_Id
,NULL
 );

Line: 77

               FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_REPORT_270: INSERT_GMS_270_HISTORY');

Line: 87

End INSERT_GMS_270_HISTORY;

Line: 99

Select SUM(nvl(c.amount,0))
  from pa_expenditure_items_all ei,
       pa_cost_distribution_lines_all c,
       gms_award_distributions g
where c.gl_date  between X_Report_Start_Date and  X_Report_End_Date
  and c.expenditure_item_id       = ei.expenditure_item_id
  and g.award_id                  = X_Award_Id
  and g.document_type             = 'EXP'
  and g.adl_line_num              = 1
  and g.adl_status                = 'A'
  and g.expenditure_item_id       = c.expenditure_item_id
  and c.line_type                 = 'R'
  and ei.system_linkage_function <> 'BTC'  -- Put the correct code for system linkage function
  and ei.project_id               in ( select gbv.project_id
                                           from gms_budget_versions gbv
					  where gbv.budget_type_code     = 'AC'
					    and gbv.budget_status_code   in ('S','W' )
					    and gbv.award_id             = X_Award_Id )
  ;

Line: 124

Select sum(nvl(bv.burden_cost,0))
  from pa_expenditure_items_all       ei,
       pa_cost_distribution_lines_all c,
       gms_cdl_burden_detail_v        bv
where c.gl_date  between X_Report_Start_Date and  X_Report_End_Date
  and c.expenditure_item_id       = ei.expenditure_item_id
  and bv.award_id                 = X_Award_Id
  and c.line_type                 = 'R'
  and ei.system_linkage_function <> 'BTC'  -- Put the correct code for system linkage function
  and bv.expenditure_item_id      = c.expenditure_item_id
  and bv.line_num                 = c.line_num
  and bv.project_id               = ei.project_id
  and ei.project_id               in  ( select gbv.project_id
                                           from gms_budget_versions gbv
                                          where gbv.budget_type_code     = 'AC'
                                            and gbv.budget_status_code   in ('S','W' )
                                            and gbv.award_id             = X_Award_Id )
  and bv.project_id               in  ( select gbv.project_id
                                           from gms_budget_versions gbv
                                          where gbv.budget_type_code     = 'AC'
                                            and gbv.budget_status_code   in ('S','W' )
                                            and gbv.award_id             = X_Award_Id ) ;

Line: 148

Select sum(nvl(bv.burden_cost,0))
FROM    gms_cdl_burden_detail_v        bv,
	gms_budget_versions gbv
WHERE bv.gl_date  between X_Report_Start_Date and  X_Report_End_Date
  and bv.award_id                 = X_Award_Id
  and bv.line_type                 = 'R'
  and bv.system_linkage_function <> 'BTC'  -- Put the correct code for system linkage function
  and gbv.budget_type_code     = 'AC'
  and gbv.budget_status_code   in ('S','W' )
  and gbv.award_id             = X_Award_Id
  and bv.project_id              =  gbv.project_id;

Line: 164

SELECT GREATEST(X_Report_Start_Date, start_date_active),
       LEAST(X_Report_End_Date, end_date_active)
FROM gms_awards
WHERE award_id = X_Award_Id;

Line: 202

  select
  funding_source_award_number
  into
  X_Document_Number
  from
  GMS_AWARDS
  where
  award_id = X_Award_Id;

Line: 267

       /* Getting the latest version to be inserted */
       select (nvl(max(version),0) +1)
       into
       X_Version
       from gms_270_history
       where award_id = X_Award_Id
       and status_code = 'O';

Line: 276

/* Inserting TWO Rows in GMS_270_HISTORY Table, one with a status of 'O' and one with a status of 'D' */
 Begin
   X_Status_Code := 'O';

Line: 279

  INSERT_GMS_270_HISTORY(X_Award_Id
			,X_Document_Number
			,X_Total_Program_Outlays
			,X_Version
			,X_Status_Code
			,L_Report_Start_Date --Bug 2357578
			,L_Report_End_Date --Bug 2357578
		      --  ,X_Payee_Address_Id
			,X_Err_Code
			,X_Err_Buff);

Line: 294

   INSERT_GMS_270_HISTORY(X_Award_Id
                        ,X_Document_Number
                        ,X_Total_Program_Outlays
                        ,X_Version
                        ,X_Status_Code
                        ,L_Report_Start_Date --Bug 2357578
                        ,L_Report_End_Date --Bug 2357578
		--	,X_Payee_Address_Id
                        ,X_Err_Code
                        ,X_Err_Buff);