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_PLAN_REVENUE SQL Statements

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

Line: 45


SELECT b.rate,b.markup_percentage,b.bill_rate_unit,b.rate_currency_code
FROM   pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
WHERE  sch.bill_rate_sch_id  = p_res_class_rate_sch_id
AND    b.bill_rate_sch_id = sch.bill_rate_sch_id
AND    sch.schedule_type = 'RESOURCE_CLASS'
AND    trunc(p_item_date)     BETWEEN trunc(b.start_date_active)        AND trunc(NVL(b.end_date_active ,p_item_date))
AND    b.res_class_organization_id = p_res_class_org_id
AND    b.resource_class_code  = p_resource_class_code;

Line: 58


SELECT b.rate,b.markup_percentage,b.bill_rate_unit,b.rate_currency_code
From   pa_std_bill_rate_schedules_all sch,pa_bill_rates_all  b,
       (	select   organization_id_parent PARENT_ORGANIZATION_ID,level parent_level
			from     per_org_structure_elements
			where    org_structure_version_id = p_proj_org_version_id
			connect by prior  organization_id_parent=organization_id_child
			and prior   org_structure_version_id = org_structure_version_id
			start with  organization_id_child=p_res_class_org_id
			and         org_structure_version_id=p_proj_org_version_id) org
Where  sch.bill_rate_sch_id  = p_res_class_rate_sch_id
AND    b.bill_rate_sch_id = sch.bill_rate_sch_id
AND    sch.schedule_type = 'RESOURCE_CLASS'
AND    trunc(p_item_date)  BETWEEN trunc(b.start_date_active)     AND trunc(NVL(b.end_date_active, p_item_date))
AND    b.res_class_organization_id = org.PARENT_ORGANIZATION_ID
AND    b.resource_class_code  = p_resource_class_code
order by org.parent_level ;

Line: 105


     select i. PROJ_ORG_STRUCTURE_VERSION_ID
     into l_PROJ_ORG_STRUCT_VERSION_ID
     from  pa_implementations_all i
     where   NVL(i.org_id,-99) = NVL(p_org_id,-99);

Line: 176


select expenditure_type,unit_of_measure
from pa_expenditure_types;

Line: 765


		SELECT NVL(res.RESOURCE_CLASS_FLAG,'Y') INTO l_resource_class_flag
                FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT, pa_resource_lists_all_bg res
                WHERE BV.BUDGET_VERSION_ID = p_budget_version_id
                AND OPT.PROJECT_ID = BV.PROJECT_ID
                AND OPT.FIN_PLAN_TYPE_ID = BV.FIN_PLAN_TYPE_ID
                AND OPT.FIN_PLAN_VERSION_ID = p_budget_version_id
		AND BV.PROJECT_ID = p_project_id
                AND res.resource_list_id = DECODE(BV.VERSION_TYPE,'COST', OPT.COST_RESOURCE_LIST_ID,
                         'REVENUE',OPT.REVENUE_RESOURCE_LIST_ID,
                         'ALL',OPT.ALL_RESOURCE_LIST_ID);

Line: 1610


        SELECT p_revenue_override_rate  b_rate,
                PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((p_revenue_override_rate * p_quantity), p_override_currency_code)  r_revenue,
                p_override_currency_code
          INTO l_txn_bill_rate,l_raw_revenue,l_txn_curr_code
	  FROM dual;

Line: 2103


				    SELECT DECODE  (p_uom,'DOLLARS',1, DECODE(p_uom,l_class_org_uom,l_class_org_rate  * NVL(p_cost_rate_multiplier,1),null)) b_rate,
                        DECODE  (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(p_quantity ,l_class_org_rate_curr_code)
                                               ,DECODE(p_uom,l_class_org_uom,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(l_class_org_rate  * NVL(p_cost_rate_multiplier,1)
                                                                       * p_quantity, l_class_org_rate_curr_code),null)
                               ) r_cost,
                       l_class_org_rate_curr_code rate_currency_code
		            FROM dual;

Line: 2133


		      SELECT DECODE  (p_uom,'DOLLARS', 1,DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_cost_rate_multiplier,1),null)) b_rate,
                       DECODE  (p_uom,'DOLLARS', PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(p_quantity ,b.rate_currency_code)
                                               ,DECODE(p_uom,b.bill_rate_unit,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_cost_rate_multiplier,1)
                                                       * p_quantity, b.rate_currency_code),null)
                               ) r_cost,
                       DECODE(p_uom,'DOLLARS', b.rate_currency_code,b.rate_currency_code) rate_currency_code
		      FROM   pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
              WHERE sch.bill_rate_sch_id=p_cost_res_class_rate_sch_id
              AND   sch.bill_rate_sch_id=b.bill_rate_sch_id
              AND   b.resource_class_code = p_resource_class
              AND   sch.schedule_type = 'RESOURCE_CLASS'
              AND   b.res_class_organization_id IS NULL
              AND   trunc(p_item_date)
              BETWEEN trunc(b.start_date_active)
                  AND NVL(trunc(b.end_date_active),trunc(p_item_date));

Line: 2441


			   SELECT DECODE  (p_uom,'DOLLARS',NULL,DECODE(p_uom,l_class_org_uom,l_class_org_rate  * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
                       DECODE  (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +l_class_org_markup)
   	                                	               * (l_raw_cost / 100), l_inter_txn_curr_code) --4194214
                                               ,DECODE(p_uom,l_class_org_uom,decode(l_class_org_rate,null,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +l_class_org_markup) * (l_raw_cost / 100), l_inter_txn_curr_code),
					       PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(l_class_org_rate  * NVL(p_bill_rate_multiplier,1)
                                                                               * p_quantity, l_class_org_rate_curr_code))
									      ,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +l_class_org_markup)
   	                                	                               * (l_raw_cost / 100), l_inter_txn_curr_code) --4194214
							)
                               ) r_revenue,
                       DECODE(p_uom,'DOLLARS',l_inter_txn_curr_code,DECODE(p_uom,l_class_org_uom,decode(l_class_org_rate,null,l_inter_txn_curr_code,l_class_org_rate_curr_code),l_inter_txn_curr_code)  ) rate_currency_code,
		       DECODE  (p_uom,'DOLLARS',l_class_org_markup,DECODE(p_uom,l_class_org_uom,decode(l_class_org_rate,null,l_class_org_markup,NULL),l_class_org_markup))  markup
		       FROM dual;

Line: 2481


			   SELECT DECODE  (p_uom,'DOLLARS',NULL,DECODE(p_uom,b.bill_rate_unit, b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
                       DECODE  (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
     * (l_raw_cost / 100), l_inter_txn_curr_code)--4194214
                                               ,DECODE(p_uom,b.bill_rate_unit,decode(b.rate,null,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)     * (l_raw_cost / 100), l_inter_txn_curr_code),
					       PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
                                                 * p_quantity, b.rate_currency_code))
					    ,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
   	                                	               * (l_raw_cost / 100), l_inter_txn_curr_code)--4194214

						      )
                               ) r_revenue,
                       DECODE(p_uom,'DOLLARS',l_inter_txn_curr_code,DECODE(p_uom,b.bill_rate_unit,decode(b.rate,null,l_inter_txn_curr_code,b.rate_currency_code),l_inter_txn_curr_code)  ) rate_currency_code,
                     --  DECODE(p_uom,'DOLLARS', b.rate_currency_code,b.rate_currency_code ) rate_currency_code, --4194214
		       DECODE  (p_uom,'DOLLARS',b.markup_percentage,DECODE(p_uom,b.bill_rate_unit,decode(b.rate,null,b.markup_percentage,NULL),b.markup_percentage))  markup
		       FROM   pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
               WHERE sch.bill_rate_sch_id=p_rev_res_class_rate_sch_id
               AND   b.bill_rate_sch_id = sch.bill_rate_sch_id
               AND   b.resource_class_code = p_resource_class
               AND   sch.schedule_type = 'RESOURCE_CLASS'
               AND   b.res_class_organization_id IS NULL
               AND   trunc(p_item_date)
               BETWEEN trunc(b.start_date_active)
                AND NVL(trunc(b.end_date_active),trunc(p_item_date));

Line: 2756


   SELECT
       /* DECODE  (p_uom,'DOLLARS',NULL, b.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
                   DECODE  (p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
                              	               * (l_raw_cost / 100), p_txn_currency_code)
                                          ,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
                                                    * p_quantity, b.rate_currency_code)
                            ) r_revenue,
                       DECODE(p_uom,'DOLLARS', p_txn_currency_code	,b.rate_currency_code ) rate_currency_code,
        DECODE  (p_uom,'DOLLARS',b.markup_percentage,NULL)  markup
*/
     DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
     DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
            * (l_raw_cost / 100),p_txn_currency_code),
                 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
                      b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
                        PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
                          PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
                       )
             ) r_revenue,
       DECODE(p_uom,'DOLLARS',p_txn_currency_code,DECODE(p_uom,b.bill_rate_unit, DECODE(b.rate,NULL,p_txn_currency_code,b.rate_currency_code),
              p_txn_currency_code)  ) rate_currency_code, -- 4194214
       DECODE(p_uom,'DOLLARS',b.markup_percentage,DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,b.markup_percentage,NULL),b.markup_percentage))  markup
    FROM   pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
    WHERE sch.bill_rate_sch_id=p_plan_rev_emp_rate_sch_id
    AND b.bill_rate_sch_id = sch.bill_rate_sch_id
    AND    sch.schedule_type = 'EMPLOYEE'
    AND b.person_id = p_person_id
    AND   trunc(p_item_date)
    BETWEEN trunc(b.start_date_active)
      AND NVL(trunc(b.end_date_active),trunc(p_item_date));

Line: 2789


   SELECT
     DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
     DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
            * (l_raw_cost / 100),p_txn_currency_code),
                 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
                      b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
                        PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
                          PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
                       )
             ) r_revenue,
       DECODE(p_uom,'DOLLARS',p_txn_currency_code,DECODE(p_uom,b.bill_rate_unit, DECODE(b.rate,NULL,p_txn_currency_code,b.rate_currency_code),
              p_txn_currency_code)  ) rate_currency_code, -- 4194214
       DECODE(p_uom,'DOLLARS',b.markup_percentage,DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,b.markup_percentage,NULL),b.markup_percentage))  markup
    FROM   pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b --, per_assignments_f pa
    WHERE sch.bill_rate_sch_id  = p_plan_rev_job_rate_sch_id
    AND b.bill_rate_sch_id = sch.bill_rate_sch_id
    AND sch.schedule_type = 'JOB'
     AND b.job_id = l_dest_job_id -- p_job_id  Modified for bug 5952621
    AND   trunc(p_item_date)
    BETWEEN trunc(b.start_date_active)
      AND NVL(trunc(b.end_date_active),trunc(p_item_date));

Line: 2812


   SELECT
     DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
     DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
            * (l_raw_cost / 100),p_txn_currency_code),
                 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
                      b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
                        PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
                          PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
                       )
             ) r_revenue,
       DECODE(p_uom,'DOLLARS',p_txn_currency_code,DECODE(p_uom,b.bill_rate_unit, DECODE(b.rate,NULL,p_txn_currency_code,b.rate_currency_code),
              p_txn_currency_code)  ) rate_currency_code, -- 4194214
       DECODE(p_uom,'DOLLARS',b.markup_percentage,DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,b.markup_percentage,NULL),b.markup_percentage))  markup
    FROM  pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
    WHERE sch.bill_rate_sch_id  = p_plan_rev_nlr_rate_sch_id
    AND b.bill_rate_sch_id = sch.bill_rate_sch_id
    AND   b.expenditure_type = p_expenditure_type
    AND   b.non_labor_resource = p_non_labor_resource
    AND sch.schedule_type = 'NON-LABOR'
    AND   trunc(p_item_date)
    BETWEEN trunc(b.start_date_active)
      AND NVL(trunc(b.end_date_active),trunc(p_item_date));

Line: 2836


   SELECT
     DECODE(p_uom,'DOLLARS',NULL, DECODE(p_uom,b.bill_rate_unit,b.rate * NVL(p_bill_rate_multiplier,1),NULL)) b_rate,
     DECODE(p_uom,'DOLLARS',PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
            * (l_raw_cost / 100),p_txn_currency_code),
                 DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 +
                      b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code),
                        PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity, b.rate_currency_code)),
                          PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage) * (l_raw_cost / 100), p_txn_currency_code)
                       )
             ) r_revenue,
       DECODE(p_uom,'DOLLARS',p_txn_currency_code,DECODE(p_uom,b.bill_rate_unit, DECODE(b.rate,NULL,p_txn_currency_code,b.rate_currency_code),
              p_txn_currency_code)  ) rate_currency_code, -- 4194214
       DECODE(p_uom,'DOLLARS',b.markup_percentage,DECODE(p_uom,b.bill_rate_unit,DECODE(b.rate,NULL,b.markup_percentage,NULL),b.markup_percentage))  markup
    FROM  pa_std_bill_rate_schedules_all sch,pa_bill_rates_all b
    WHERE sch.bill_rate_sch_id  = p_plan_rev_nlr_rate_sch_id
    AND b.bill_rate_sch_id = sch.bill_rate_sch_id
    AND   b.expenditure_type = p_expenditure_type
    AND sch.schedule_type = 'NON-LABOR'
    AND   b.non_labor_resource IS NULL
    AND   trunc(p_item_date)
    BETWEEN trunc(b.start_date_active)
    AND NVL(trunc(b.end_date_active),trunc(p_item_date));

Line: 2860


    select rate,rate_currency_code
    from PA_EMP_BILL_RATE_OVERRIDES
    where project_id = p_project_id
    and person_id = p_person_id;

Line: 2867


     select rate,rate_currency_code
     from pa_job_bill_rate_overrides
     where project_id = p_project_id
     and job_id = p_job_id;

Line: 2904


         SELECT p_revenue_override_rate  b_rate,
              PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((p_revenue_override_rate * p_quantity), p_override_currency_code)  r_revenue,
              p_override_currency_code
          INTO l_txn_bill_rate,l_raw_revenue,l_txn_curr_code
	  FROM dual;

Line: 2996


          select job_group_id
          into l_job_group_id
          from pa_std_bill_rate_schedules_all
          where bill_rate_sch_id = p_plan_rev_job_rate_sch_id;

Line: 3678


	    SELECT default_raw_cost, DEF_RAW_COST_CURRENCY_CODE INTO  l_cost_rate, l_txn_curr_code
				FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT
				WHERE BV.BUDGET_VERSION_ID      = p_budget_version_id
				AND OPT.PROJECT_ID            = BV.PROJECT_ID
				AND OPT.FIN_PLAN_TYPE_ID      = BV.FIN_PLAN_TYPE_ID
				AND OPT.FIN_PLAN_VERSION_ID   = p_budget_version_id
				AND BV.PROJECT_ID = p_project_id;

Line: 3692


	    SELECT DEF_RAW_COST_CURRENCY_CODE INTO  l_txn_curr_code
				FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT
				WHERE BV.BUDGET_VERSION_ID      = p_budget_version_id
				AND OPT.PROJECT_ID            = BV.PROJECT_ID
				AND OPT.FIN_PLAN_TYPE_ID      = BV.FIN_PLAN_TYPE_ID
				AND OPT.FIN_PLAN_VERSION_ID   = p_budget_version_id
				AND BV.PROJECT_ID = p_project_id;

Line: 3862


	      SELECT DEFAULT_BILL_RATE, DEF_BILL_RATE_CURRENCY_CODE,DEF_MARKUP_PERCENTAGE
	      INTO  l_bill_rate, l_bill_txn_curr_code, l_markup
				FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT
				WHERE BV.BUDGET_VERSION_ID      = p_budget_version_id
				AND OPT.PROJECT_ID            = BV.PROJECT_ID
				AND OPT.FIN_PLAN_TYPE_ID      = BV.FIN_PLAN_TYPE_ID
				AND OPT.FIN_PLAN_VERSION_ID   = p_budget_version_id
				AND BV.PROJECT_ID = p_project_id;

Line: 3878


	      SELECT DEF_BILL_RATE_CURRENCY_CODE,DEF_MARKUP_PERCENTAGE
	      INTO  l_bill_txn_curr_code, l_markup
				FROM PA_BUDGET_VERSIONS BV, PA_PROJ_FP_OPTIONS OPT
				WHERE BV.BUDGET_VERSION_ID      = p_budget_version_id
				AND OPT.PROJECT_ID            = BV.PROJECT_ID
				AND OPT.FIN_PLAN_TYPE_ID      = BV.FIN_PLAN_TYPE_ID
				AND OPT.FIN_PLAN_VERSION_ID   = p_budget_version_id
				AND BV.PROJECT_ID = p_project_id;