eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_BILL_PCT SQL Statements

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

Line: 128


  	PA_MCB_INVOICE_PKG.log_message('Before pa_billing_extn_params_v select pa_bill_pct.calc_pct_comp_amt  :');

Line: 133


     SELECT default_cost_plan_type_id,default_rev_plan_type_id
     INTO l_cost_plan_type_id,l_rev_plan_type_id
     FROM pa_billing_extn_params_v;

Line: 292


	/** public api to insert event **/

IF g1_debug_mode  = 'Y' THEN
	PA_MCB_INVOICE_PKG.log_message('rev part inside pa_bill_pct.calc_pct_comp_amt event desc :'||Event_Description);

Line: 296


	PA_MCB_INVOICE_PKG.log_message('rev part Before calling insert_event inside  pa_bill_pct.calc_pct_comp_amt  Revenue :');

Line: 298


    	pa_billing_pub.insert_event (
			X_rev_amt => Revenue,
			X_bill_amt => 0,
                       	X_event_description => event_description,
                        X_audit_amount1 => amount_left,
                        X_audit_amount2 => revenue_amount,
                        X_audit_amount3 => budget_revenue,
                        X_audit_amount4 => event_revenue,
			X_audit_amount5 => Percent_Complete,
                        X_audit_cost_budget_type_code => l_cost_budget_type_code,
                        X_audit_rev_budget_type_code => l_rev_budget_type_code,
                        X_audit_cost_plan_type_id     => l_cost_plan_type_id, /* Added for fin plan impact */
                        X_audit_rev_plan_type_id      => l_rev_plan_type_id,  /* Added for fin plan impact */
			X_error_message => l_error_message,
			X_status	=> l_status
			);

Line: 316


	PA_MCB_INVOICE_PKG.log_message('Rev part After calling insert_event inside  pa_bill_pct.calc_pct_comp_amt  Revenue -> status :'||l_status);

Line: 377


	PA_MCB_INVOICE_PKG.log_message('Inv part Before insert event  inside  pa_bill_pct.calc_pct_comp_amt  Invoice :');

Line: 379


    	pa_billing_pub.insert_event (
			X_rev_amt => 0,
			X_bill_amt => Invoice,
			X_event_description => Event_Description,
                        X_audit_amount1 => amount_left,
                        X_audit_amount2 => invoice_amount,
                        X_audit_amount3 => budget_revenue,
                        X_audit_amount4 => event_invoice,
			X_audit_amount5 => Percent_Complete,
                        X_audit_cost_budget_type_code => l_cost_budget_type_code,
                        X_audit_rev_budget_type_code => l_rev_budget_type_code,
                        X_audit_cost_plan_type_id     => l_cost_plan_type_id, /* Added for fin plan impact */
                        X_audit_rev_plan_type_id      => l_rev_plan_type_id,  /* Added for fin plan impact */
			X_error_message => l_error_message,
			X_status	=> l_status
			);

Line: 397


	PA_MCB_INVOICE_PKG.log_message('Inv partinsert event  inside  pa_bill_pct.calc_pct_comp_amt  Invoice -> status :'||l_status);

Line: 483


   SELECT  (DECODE(revenue_hold_flag, 'Y' , 0 ,DECODE(et.event_type_classification,
	   'WRITE OFF',-1 * nvl(bill_trans_rev_amount,0),
	   'RLZED_LOSSES',-1 * nvl(bill_trans_rev_amount,0),
           NVL(bill_trans_rev_amount,0)))) trans_rev_amount,
           (DECODE(bill_hold_flag, 'Y' , 0 , DECODE(et.event_type_classification,'INVOICE REDUCTION', -1 * nvl(bill_trans_bill_amount,0),
           NVL(bill_trans_bill_amount,0)))) trans_bill_amount,e.bill_trans_currency_code,e.projfunc_currency_code,
           e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
FROM	pa_events e,
	pa_event_types et
WHERE	e.event_type = et.event_type
AND	e.project_id = X2_project_id
AND	nvl(e.task_id,0) = nvl(X2_task_id, nvl(e.task_id,0))
AND	e.completion_date <= nvl(X2_accrue_through_date, sysdate)
AND	NOT EXISTS (	select '1'
		    	from	pa_billing_assignments bea,
				pa_billing_extensions be
			where	be.billing_extension_id = bea.billing_extension_id
			and	bea.billing_assignment_id = e.billing_assignment_id
			and	be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt');

Line: 510


/* SELECT 	sum(decode(et.event_type_classification,
		'WRITE OFF',	-1 * nvl(revenue_amount,0),
				nvl(revenue_amount,0))),
	sum(decode(et.event_type_classification,
		'INVOICE REDUCTION', -1 * nvl(bill_amount,0),
				nvl(bill_amount,0)))
INTO	X2_revenue_amount,
	X2_invoice_amount
FROM	pa_events e,
	pa_event_types et
WHERE	e.event_type = et.event_type
AND	e.project_id = X2_project_id
AND	nvl(e.task_id,0) = nvl(X2_task_id, nvl(e.task_id,0))
AND	e.completion_date <= nvl(X2_accrue_through_date, sysdate)
AND	NOT EXISTS (	select '1'
		    	from	pa_billing_assignments bea,
				pa_billing_extensions be
			where	be.billing_extension_id = bea.billing_extension_id
			and	bea.billing_assignment_id = e.billing_assignment_id
			and	be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'); */

Line: 665


        SELECT  NVL(e.bill_trans_rev_amount,0) trans_rev_amount,e.bill_trans_currency_code,
                e.projfunc_currency_code,e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
	FROM 	pa_events e,
		pa_billing_assignments bea,
		pa_billing_extensions be
	where	be.billing_extension_id = bea.billing_extension_id
	and	e.project_id = X2_project_id
	and    	nvl(e.task_id,0) =
			decode(X2_task_id,
				NULL, 	nvl(e.task_id,0), X2_task_id )
	and	bea.billing_assignment_id = e.billing_assignment_id
	and	be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
	and	e.revenue_distributed_flag||'' = 'N';

Line: 686


SELECT sum(nvl(dri.projfunc_revenue_amount,0))
INTO   accrued_pctrev
FROM   pa_draft_revenue_items dri
WHERE  dri.project_id = X2_project_id
AND    nvl(dri.task_id,0) = decode(X2_task_id, NULL, nvl(dri.task_id,0), X2_task_id )
AND    (EXISTS (	select '1'
			from 	pa_cust_event_rev_dist_lines erdl,
				pa_events e,
				pa_billing_assignments bea,
				pa_billing_extensions be
			where	be.billing_extension_id = bea.billing_extension_id
			and	bea.billing_assignment_id = e.billing_assignment_id
			and	e.project_id = erdl.project_id
			and	e.event_num = erdl.event_num
			and	nvl(e.task_id,0) = nvl(erdl.task_id, 0)
			and	erdl.project_id = dri.project_id
			and	erdl.draft_revenue_num = dri.draft_revenue_num
			and	erdl.draft_revenue_item_line_num = dri.line_num
			and	be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt')
       OR dri.revenue_source like 'Expenditure%');

Line: 716


	SELECT 	sum(nvl(e.revenue_amount,0))
	INTO	pending_pctrev
	FROM 	pa_events e,
		pa_billing_assignments bea,
		pa_billing_extensions be
	where	be.billing_extension_id = bea.billing_extension_id
	and	e.project_id = X2_project_id
	and    	nvl(e.task_id,0) =
			decode(X2_task_id,
				NULL, 	nvl(e.task_id,0), X2_task_id )
	and	bea.billing_assignment_id = e.billing_assignment_id
	and	be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
	and	e.revenue_distributed_flag||'' = 'N'; */

Line: 856


    SELECT NVL(e.bill_trans_bill_amount,0) trans_bill_amount,e.bill_trans_currency_code,e.projfunc_currency_code,
           e.projfunc_rate_type,e.projfunc_rate_date,e.projfunc_exchange_rate
    FROM 	pa_events e,
	        pa_billing_assignments bea,
	        pa_billing_extensions be
    WHERE	be.billing_extension_id = bea.billing_extension_id
    AND	bea.billing_assignment_id = e.billing_assignment_id
    AND	e.project_id = X2_project_id
    AND	be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
    AND	nvl(e.task_id,0) = decode(X2_task_id,
		    NULL, nvl(e.task_id,0), X2_task_id)
    AND	NOT EXISTS
	        (select 'billed'
	         from   pa_draft_invoice_items pdii
	         where 	pdii.project_id = e.project_id
	         and  	pdii.event_num = e.event_num
	         and 	nvl(pdii.task_id,0) = nvl(e.task_id,0));

Line: 884


SELECT	sum(nvl(e.bill_amount,0))
INTO	pending_ccinv
from 	pa_events e,
	pa_billing_assignments bea,
	pa_billing_extensions be
where	be.billing_extension_id = bea.billing_extension_id
and	bea.billing_assignment_id = e.billing_assignment_id
and	e.project_id = X2_project_id
and	be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt'
and	nvl(e.task_id,0) = decode(X2_task_id,
					NULL, nvl(e.task_id,0), X2_task_id)
and	NOT EXISTS
	(select 'billed'
	 from   pa_draft_invoice_items pdii
	 where 	pdii.project_id = e.project_id
	 and  	pdii.event_num = e.event_num
	 and 	nvl(pdii.task_id,0) = nvl(e.task_id,0)); */

Line: 970


  SELECT sum(nvl(dii.projfunc_bill_amount,0))
  INTO   billed_ccinv
  FROM	 pa_draft_invoice_items dii
  WHERE  dii.project_id = X2_project_id
  AND    (EXISTS 	(select '1'
			from 	pa_events e,
				pa_billing_assignments bea,
				pa_billing_extensions be
			where	be.billing_extension_id = bea.billing_extension_id
			and	bea.billing_assignment_id = e.billing_assignment_id
			and	dii.project_id = e.project_id
			and	dii.event_num = e.event_num
			and	nvl(dii.event_task_id,0) = nvl(e.task_id,0)
			and	be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt')
          OR EXISTS (	select 	'1'
		   	from 	pa_cust_rev_dist_lines erdl
			where 	erdl.project_id = dii.project_id
			and	erdl.draft_invoice_num = dii.draft_invoice_num
			and	erdl.draft_invoice_item_line_num = dii.line_num));

Line: 998


  SELECT sum(nvl(rdl.projfunc_bill_amount,0))
  INTO	task_billed_ccinv
  FROM	pa_cust_rev_dist_lines rdl,
	pa_expenditure_items_all ei,
	pa_tasks t
  WHERE	ei.task_id = t.task_id
  AND   ei.Project_ID = t.Project_ID -- Perf Bug 2695332
  AND	ei.expenditure_item_id = rdl.expenditure_item_id
  AND	rdl.project_id = X2_project_id
  AND	t.top_task_id = X2_task_id
  AND	rdl.draft_invoice_num IS NOT NULL;

Line: 1014


  SELECT sum(nvl(pdii.projfunc_bill_amount,0))
  INTO   task_billed_ev_ccinv
  FROM   pa_draft_invoice_items pdii
  WHERE  pdii.event_task_id = X2_task_id
  AND    pdii.Project_ID = X2_Project_ID -- Perf Bug 2695332
  AND    EXISTS (select '1'
			from 	pa_events e,
				pa_billing_assignments bea,
				pa_billing_extensions be
			where	be.billing_extension_id = bea.billing_extension_id
			and	bea.billing_assignment_id = e.billing_assignment_id
			and	pdii.project_id = e.project_id
			and	pdii.event_num = e.event_num
			and	pdii.event_task_id = e.task_id
			and	be.procedure_name = 'pa_bill_pct.calc_pct_comp_amt');

Line: 1063


SELECT  NVL(completed_percentage,0)
FROM	PA_PERCENT_COMPLETES_FIN_V ppc
WHERE
ppc.project_id = X2_project_id AND
nvl(ppc.task_id,0) = nvl(X2_task_id,0 )
AND	ppc.date_computed
        =
         (SELECT max(date_computed)
          from PA_PERCENT_COMPLETES_FIN_V
          where date_computed <=  nvl(X2_accrue_through_date, sysdate)
          and   project_id = X2_project_id
          and   nvl(task_id,0) = nvl(X2_task_id,0)
         )
ORDER BY creation_date desc
;

Line: 1081


SELECT  NVL(completed_percentage,0)
FROM    PA_PERCENT_COMPLETES_FIN_V ppc
WHERE
ppc.project_id = X2_project_id
And nvl(ppc.task_id,0) = nvl(X2_task_id,0 )
And ppc.date_computed <= nvl(X2_accrue_through_date, sysdate)
And ppc.percent_complete_id  = ( Select max(ppcx.percent_complete_id)
                                 from PA_PERCENT_COMPLETES_FIN_V ppcx
                                 where project_id = X2_project_id
                                 and   nvl(task_id,0) = nvl(X2_task_id,0)
                                 and   ppcx.date_computed = (
                                           Select max(ppcy.date_computed)
                                           from   PA_PERCENT_COMPLETES_FIN_V ppcy
                                           Where  ppcy.date_computed <=  nvl(X2_accrue_through_date, sysdate)
                                           and    ppcy.project_id = X2_project_id
                                           and    nvl(ppcy.task_id,0) = nvl(X2_task_id,0)));

Line: 1173


      SELECT  DECODE(P_rev_budget_type_code,NULL,default_rev_budget_type_code,P_rev_budget_type_code),
              DECODE(P_rev_plan_type_id,NULL,default_rev_plan_type_id,
                   P_rev_plan_type_id) /* Added for fin plan type id */
      INTO    l_rev_budget_type_code,
              l_rev_plan_type_id
      FROM     pa_billing_extensions
      WHERE    billing_extension_id=pa_billing.GetBillingExtensionId;

Line: 1189


  /* Added this select for Fin plan Impact */
  BEGIN
   SELECT  'x'
   INTO    dummy
   FROM    dual
   WHERE   EXISTS( SELECT *
                   FROM pa_fin_plan_types_b f
                   WHERE f.fin_plan_type_id=l_rev_plan_type_id );

Line: 1206


      SELECT  'x'
      INTO    dummy
      FROM    pa_budget_types
      WHERE   budget_type_code = l_rev_budget_type_code
      AND     budget_amount_code = 'R';

Line: 1220


  /* Added this select for Fin plan Impact */
  BEGIN

   SELECT v.budget_version_id
   INTO   l_rev_plan_version_id
   FROM   pa_budget_versions v
   WHERE  v.project_id = X2_project_id
   AND    v.current_flag = 'Y'
   AND    v.budget_status_code           = 'B'
   AND    v.fin_plan_type_id             = l_rev_plan_type_id
   AND    v.version_type IN ('REVENUE','ALL');

Line: 1239


     SELECT budget_version_id
     INTO   l_rev_budget_version_id
     FROM   pa_budget_versions pbv
     WHERE  project_id = X2_project_id
     AND    budget_type_code = l_rev_budget_type_code
     AND    budget_status_code = 'B'
     AND    current_flag = 'Y';

Line: 1313


	pa_billing_pub.insert_message
        (X_inserting_procedure_name =>'pa_billing_pct.get_rev_budget_amount',
	 X_attribute2 => l_rev_budget_type_code,
	 X_message => status,
         X_error_message=>err_msg,
         X_status=>err_status);