APPS.GMS_REPORT_SF272 SQL Statements

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

Line: 4

Procedure INSERT_GMS_272_HISTORY(X_Award_Id IN NUMBER
				,X_Document_Number IN VARCHAR2
				,X_Total_Actuals IN NUMBER
				,X_Cash_On_Hand_Beginning IN NUMBER
	 			,X_Version IN NUMBER
				,X_Status_Code IN VARCHAR2
				,X_Report_Start_Date IN DATE
				,X_Report_End_Date   IN DATE
				,X_Err_Code OUT NOCOPY VARCHAR2
				,X_Err_Stage OUT NOCOPY VARCHAR2) IS


Begin

 Begin
 INSERT INTO GMS_272_HISTORY
 (
AWARD_ID
,VERSION
,STATUS_CODE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,DOCUMENT_NUMBER
,RUN_DATE
,REPORT_START_DATE
,REPORT_END_DATE
,LETTER_OF_CREDIT
,LAST_PAYMENT_VOUCHER_NUMBER
,PAYMENT_VOUCHER_CREDIT
,TREASURY_CHECK
,CASH_IN_HAND_BEGIN
,LETTER_OF_CREDIT_WITHDRAWLS
,TREASURY_CHECK_PAYMENTS
,TOTAL_RECEIPTS
,TOTAL_CASH_AVAIABLE
,GROSS_DISBURSEMENTS
,FEDERAL_SHARE_INCOME
,NET_DISBURSEMENTS
,ADJUSTMENTS_PRIOR_PERIODS
,CASH_IN_HAND_END
,DAYS
,INTEREST_INCOME
,ADV_TO_SUBGRANTEES
,REMARKS
   )
values
(
 X_Award_Id
,X_Version
,X_Status_Code
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.login_id
,X_Document_Number
,SYSDATE
,X_Report_Start_Date
,X_Report_End_Date
,0
,0
,0
,0
,X_Cash_On_Hand_Beginning
,0
,0
,0
,0
,X_Total_Actuals
,0
,0
,0
,0
,0
,0
,0
,NULL
 );

Line: 92

               FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_REPORT_272: INSERT_GMS_272_HISTORY');

Line: 102

End INSERT_GMS_272_HISTORY;

Line: 120

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: 143

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: 167

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: 183

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: 214

  select
  funding_source_award_number
  into
  X_Document_Number
  from
  GMS_AWARDS
  where
  award_id = X_Award_Id;

Line: 251

	select nvl(CASH_IN_HAND_END,0)
		 into X_Cash_On_hand_beginning
	from gms_272_history
	where award_id = X_Award_Id
        and status_code = 'F'
	  and version = (select max(version) from gms_272_history gh
				where gh.award_id = X_Award_Id
				  and status_code = 'F');

Line: 265

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

Line: 274

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

Line: 277

  INSERT_GMS_272_HISTORY(X_Award_Id
			,X_Document_Number
			,X_Total_actuals
			,X_Cash_On_Hand_Beginning
			,X_Version
			,X_Status_Code
			,L_Report_Start_Date --Bug 2357578
			,L_Report_End_Date --Bug 2357578
			,X_Err_Code
			,X_Err_Buff);

Line: 292

   INSERT_GMS_272_HISTORY(X_Award_Id
                        ,X_Document_Number
                        ,X_Total_Actuals
				,X_Cash_On_Hand_Beginning
                        ,X_Version
                        ,X_Status_Code
                        ,L_Report_Start_Date --Bug 2357578
                        ,L_Report_End_Date --Bug 2357578
                        ,X_Err_Code
                        ,X_Err_Buff);