APPS.PA_EFC_BIL SQL Statements

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

Line: 23

          SELECT f.budget_type_code budget_type_code,
                 NVL(SUM(f.allocated_amount),0) tot_amt
          FROM   pa_project_fundings f
          WHERE  f.project_id = pl_project_id
	    AND  f.agreement_id = pl_agreement_id
	    AND  nvl(f.task_id,-99) = nvl(pl_task_id,-99)
       GROUP BY  f.budget_type_code;

Line: 59

   		SELECT 	NVL(SUM(dri.amount),0) dri_amount
	  	INTO 	pl_accr_rev
           	FROM 	pa_draft_revenue_items dri,
               	 	pa_draft_revenues_all dr
          	WHERE   dri.project_id = dr.project_id
            	  AND 	dri.draft_revenue_num = dr.draft_revenue_num
            	  AND   ( NVL(p_task_id,0) = 0
                          OR dri.task_id = p_task_id )
                  AND dr.project_id = p_project_id
                  AND dr.agreement_id = p_agreement_id;

Line: 76

       		SELECT 	sum(dii.amount) dii_amount
	 	  INTO 	pl_billed
         	  FROM 	pa_draft_invoice_items dii,
                	pa_draft_invoices_all di
         	 WHERE 	dii.project_id = di.project_id
           	   AND 	dii.draft_invoice_num = di.draft_invoice_num
           	   AND 	di.project_id = p_project_id
           	   AND 	di.agreement_id = p_agreement_id
           	   AND 	dii.invoice_line_type <> 'RETENTION';

Line: 90

    		SELECT 	round(sum(dii.amount * (1 -
                    	( nvl(di.retention_percentage,0)/100 )) ),2) dii_amount
		  INTO 	pl_billed
                  FROM 	pa_draft_invoice_items dii,
                	pa_draft_invoices_all di
         	 WHERE 	dii.project_id = di.project_id
           	   AND 	dii.draft_invoice_num = di.draft_invoice_num
           	   AND 	dii.task_id = p_task_id
           	   AND 	dii.invoice_line_type <> 'RETENTION'
           	   AND 	di.project_id = p_project_id
           	   AND 	di.agreement_id = p_agreement_id;

Line: 144

   |     Procedure to update the adjusted amount in    project funding table   |
   |     and summary project funding efc table 				       |
   ----------------------------------------------------------------------------*/

PROCEDURE Update_Adjusted_Amount (p_project_id		IN	NUMBER,
				  p_agreement_id	IN	NUMBER,
				  p_task_id		IN	NUMBER,
				  p_adjusted		IN	NUMBER) IS

-- get the latest funding id to update the adjusted amount

CURSOR cur_adj	IS
	SELECT f.project_funding_id project_funding_id
	  FROM pa_project_fundings f
	WHERE  f.project_id = p_project_id
	  AND  f.agreement_id = p_agreement_id
          AND  NVL(f.task_id,-99)= NVL(p_task_id,-99)
	  AND  f.budget_type_code = 'BASELINE'
     ORDER BY  creation_date DESC;

Line: 165

	update_flag   	BOOLEAN:= FALSE;

Line: 175

			-- update the Project Funding record

				UPDATE pa_project_fundings pf
			   	   SET pf.allocated_amount=(pf.allocated_amount+p_adjusted)
			 	 WHERE pf.project_funding_id = rec_adj.project_funding_id;

Line: 181

			-- update the Project Funding EFC record

				UPDATE pa_project_fundings_efc pfefc
			   	   SET pfefc.adjusted_amount=p_adjusted
			 	 WHERE pfefc.project_funding_id = rec_adj.project_funding_id;

Line: 187

				  update_flag :=  TRUE;

Line: 189

				EXIT WHEN (update_flag);

Line: 197

	-- update the Summary Project Funding EFC record

		UPDATE pa_summary_proj_fundings_efc
		   SET adjusted_amount	= p_adjusted
		 WHERE project_id 	= p_project_id
		   AND NVL(task_id,-99) = NVL(p_task_id,-99)
		   AND agreement_id 	= p_agreement_id;

Line: 207

END Update_Adjusted_Amount ;