APPS.PA_BILLING_PROCESS_PKG SQL Statements

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

Line: 17

	SELECT p.*
	FROM   pa_projects p, pa_project_types t
	WHERE ((segment1 BETWEEN pfromproj AND ptoproj)
	OR project_id =pproject_id)
	AND p.project_type = t.project_type
        AND t.project_type_class_code = 'CONTRACT';

Line: 25

	SELECT *
	FROM   pa_tasks a
	WHERE  project_id=cproject_id
	AND (
	        (task_id=top_task_id
	         AND ready_to_distribute_flag ='Y'
	        )
	     OR (chargeable_flag ='Y'
	         AND EXISTS ( SELECT null
		              FROM   pa_tasks b
		              WHERE  b.task_id                  = a.top_task_id
			      AND    b.ready_to_distribute_flag = 'Y'
			     )
		  )
	     );

Line: 42

	SELECT /*+ INDEX(pf pa_summary_project_fundings_u1)*/ 1
	FROM pa_summary_project_fundings spf
	WHERE spf.project_id = cproject_id
	AND nvl(spf.revproc_baselined_amount, 0) <> 0;

Line: 49

	SELECT 1
	FROM pa_draft_revenues dr
	WHERE dr.project_id = cproject_id
	AND dr.released_date IS NULL
	AND dr.generation_error_flag = decode(pmass_gen, 1, 'Y',dr.generation_error_flag);

Line: 121

			    SELECT 1
 			    INTO   temp
			    FROM dual
			    WHERE EXISTS(
					SELECT 1
					FROM  pa_expenditure_items_all ei
					WHERE ei.project_id		  = Rtask.project_id
					AND   ei.task_id		  = Rtask.task_id
					AND   ei.cost_distributed_flag    = 'Y'
					AND   ei.revenue_distributed_flag = 'N'
					AND   ei.expenditure_item_date    <= acc_thru_dt
					AND EXISTS(    SELECT NULL
							FROM  pa_cust_rev_dist_lines crdl,pa_draft_revenues drx1
							WHERE ei.project_id = crdl.project_id
							AND  ((ei.expenditure_item_id = crdl.expenditure_item_id)
								OR  (ei.adjusted_expenditure_item_id IS NOT NULL
									AND ei.adjusted_expenditure_item_id = crdl.expenditure_item_id))
							AND NVL(crdl.reversed_flag,'N')   = 'N'
							AND NVL(crdl.line_num_reversed,0) = 0
							AND drx1.project_id		  = crdl.project_id
							AND drx1.draft_revenue_num        = crdl.draft_revenue_num
							AND DECODE(drx1.generation_error_flag,mass_gen,decode(drx1.released_date,NULL,1,0),0)
								= DECODE(drx1.released_date,NULL,1,0)
							UNION ALL
							SELECT 1
							FROM   pa_expenditure_items ei2
							WHERE  ei2.project_id               = ei.project_id
							AND    ei2.expenditure_item_id      = ei.adjusted_expenditure_item_id
							AND    ei2.revenue_distributed_flag = 'Y'
							AND    ei2.raw_revenue              =  0
						  )
				        );

Line: 171

				   SELECT 1
				   INTO   temp
				   FROM dual
				   WHERE
				   EXISTS(
					   SELECT 1
					   FROM pa_events ev,
					        pa_cust_event_rev_dist_lines erdl,
					        pa_draft_revenues drx
					   WHERE ev.project_id                  = Rtask.project_id
					   AND   ev.task_id                     = Rtask.task_id
					   AND ((ev.revenue_distributed_flag = 'N'
					         AND ev.completion_date <= acc_thru_dt)
					      OR (distribution_rule = 'COST'
					          AND   ev.revenue_distributed_flag    = 'Y'
					          AND   ev.completion_date             > acc_thru_dt
                                                  )
                                                 )
					   AND   nvl(ev.revenue_hold_flag, 'N') = 'N'
					   AND   (decode(nvl(ev.bill_trans_rev_amount, 0), 0,
					 	 decode(nvl(ev.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
					   AND   erdl.project_id                = ev.project_id
					   AND   erdl.event_num		     = ev.event_num
					   AND   erdl.task_id		     = ev.task_id
					   AND   nvl(erdl.reversed_flag,'N')    = 'N'
					   AND   erdl.line_num_reversed IS NULL
					   AND   drx.project_id		     = erdl.project_id
					   AND   drx.draft_revenue_num	     = erdl.draft_revenue_num
					   AND   decode(drx.generation_error_flag,mass_gen,decode(drx.released_date,NULL,1,0),0)
							    = decode(drx.released_date,NULL,1,0)
					 );

Line: 228

			    SELECT 1
			    INTO   temp
			    FROM   dual
   			    WHERE  EXISTS(
					SELECT 1
					FROM pa_events ev,
					     pa_cust_event_rev_dist_lines erdl,
					     pa_draft_revenues drx
					WHERE ev.project_id                  = Rproj.project_id
					AND   ev.task_id IS NULL
				        AND ((ev.revenue_distributed_flag = 'N'
					         AND ev.completion_date <= acc_thru_dt)
					      OR (distribution_rule = 'COST'
					          AND   ev.revenue_distributed_flag    = 'Y'
					          AND   ev.completion_date             > acc_thru_dt
                                                  )
                                                )
					AND   nvl(ev.revenue_hold_flag, 'N') = 'N'
					AND   (decode(nvl(ev.bill_trans_rev_amount, 0), 0,
						 decode(nvl(ev.zero_revenue_amount_flag, 'N'), 'Y', 1, 0),1) = 1)
					AND   erdl.project_id                = ev.project_id
					AND   erdl.task_id IS NULL
					AND   erdl.event_num		     = ev.event_num
					AND   nvl(erdl.reversed_flag,'N')    = 'N'
					AND   erdl.line_num_reversed IS NULL
					AND   drx.project_id		     = erdl.project_id
					AND   drx.draft_revenue_num	     = erdl.draft_revenue_num
					AND   decode(drx.generation_error_flag,mass_gen,decode(drx.released_date,NULL,1,0),0)
				       		    = decode(drx.released_date,NULL,1,0)
				      );

Line: 276

			    SELECT 1
			    INTO   temp
			    FROM dual
			    WHERE EXISTS(
					SELECT 1
					FROM pa_billing_extensions be,
					     pa_billing_assignments bea
					WHERE bea.active_flag        = 'Y'
					AND bea.billing_extension_id = be.billing_extension_id
					AND (bea.project_id  = Rproj.project_id
					     OR bea.project_type = Rproj.project_type
					     OR bea.distribution_rule = Rproj.distribution_rule)
					AND be.calling_process in ('Revenue','Both')
					AND be.call_after_adj_flag    = 'Y'
					AND be.trx_independent_flag   = 'Y'
					);

Line: 306

		     PA_MCB_INVOICE_PKG.log_message('pa_process_rev_adj:Inserting project id :'||Rproj.project_id);

Line: 308

		  INSERT INTO PA_BILLING_PROCESS_GT(project_id,request_id,process)
		  VALUES(Rproj.project_id,g_request_id,'REV_ADJ');

Line: 310

		 --insert record
		END IF;