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

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

Line: 144


        SELECT rate_based_flag
        INTO l_rate_based_flag
        FROM pa_resource_assignments
        WHERE resource_assignment_id = p_tgt_res_asg_id;

Line: 169


    SELECT  /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
            DECODE(l_currency_flag,
                'PC', l_pc_currency_code,
                'TC', txn_currency_code,
                'PFC',l_pfc_currency_code),
            SUM(NVL(total_plan_quantity,0)),
            SUM(DECODE(l_currency_flag,
                        'PC',NVL(total_pc_raw_cost,0),
                        'TC',NVL(total_txn_raw_cost,0),
                        'PFC',NVL(total_pfc_raw_cost,0))),
            SUM(DECODE(l_currency_flag,
                        'PC', NVL(total_pc_burdened_cost,0),
                        'TC', NVL(total_txn_burdened_cost,0),
                        'PFC', NVL(total_pfc_burdened_cost,0))),
            SUM(DECODE(l_currency_flag,
                        'PC', NVL(total_pc_revenue,0),
                        'TC', NVL(total_txn_revenue,0),
                        'PFC', NVL(total_pfc_revenue,0)))
    BULK COLLECT INTO
            l_tot_currency_code_tab,
            l_tot_quantity_tab,
            l_tot_raw_cost_tab,
            l_tot_brdn_cost_tab,
            l_tot_revenue_tab
    FROM PA_FP_CALC_AMT_TMP2
    WHERE resource_assignment_id = p_src_res_asg_id
    AND transaction_source_code = p_etc_source_code
    GROUP BY DECODE(l_currency_flag, 'PC', l_pc_currency_code,
                                     'TC', txn_currency_code,
                                     'PFC',l_pfc_currency_code);

Line: 214


        SELECT  /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N2)*/
                NVL(SUM(NVL(total_plan_quantity,0)),0),
                NVL(DECODE(l_currency_flag, 'TC', SUM(NVL(total_txn_raw_cost,0)),
                                            'PC', SUM(NVL(total_pc_raw_cost,0)),
                                            'PFC', SUM(NVL(total_pfc_raw_cost,0))),0),
                NVL(DECODE(l_currency_flag, 'TC', SUM(NVL(total_txn_burdened_cost,0)),
                                            'PC', SUM(NVL(total_pc_burdened_cost,0)),
                                            'PFC', SUM(NVL(total_pfc_burdened_cost,0))),0),
                NVL(DECODE(l_currency_flag, 'TC', SUM(NVL(total_txn_revenue,0)),
                                            'PC', SUM(NVL(total_pc_revenue,0)),
                                            'PFC', SUM(NVL(total_pfc_revenue,0))),0),
                NVL(SUM(NVL(total_pc_raw_cost,0)),0),
                NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
                NVL(SUM(NVL(total_pc_revenue,0)),0),
                NVL(SUM(NVL(total_pfc_raw_cost,0)),0),
                NVL(SUM(NVL(total_pfc_burdened_cost,0)),0),
                NVL(SUM(NVL(total_pfc_revenue,0)),0)
        INTO    l_txn_rate_quantity,
                l_txn_rate_raw_cost,
                l_txn_rate_brdn_cost,
                l_txn_rate_revenue,
                l_pc_rate_raw_cost,
                l_pc_rate_brdn_cost,
                l_pc_rate_revenue,
                l_pfc_rate_raw_cost,
                l_pfc_rate_brdn_cost,
                l_pfc_rate_revenue
        FROM pa_fp_calc_amt_tmp2
        WHERE resource_assignment_id = p_src_res_asg_id
        AND DECODE(l_currency_flag, 'TC', txn_currency_code,
            'PC', l_tot_currency_code_tab(i),
            'PFC', l_tot_currency_code_tab(i)) = l_tot_currency_code_tab(i)
        AND transaction_source_code IN ('FINANCIAL_PLAN','WORKPLAN_RESOURCES');

Line: 289


    /* If commitment is not included, record is inserted directly as
       'ETC' record, if commitment is to be considered, record is
       inserted as 'TOTAL_ETC' for further processing. */
    IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
        l_transaction_source_code := 'TOTAL_ETC';

Line: 298


    /* Insert total ETC amounts */
    FORALL i IN 1..l_tot_currency_code_tab.count
        INSERT INTO PA_FP_CALC_AMT_TMP2 (
            RESOURCE_ASSIGNMENT_ID,
            TARGET_RES_ASG_ID,
            ETC_CURRENCY_CODE,
            ETC_PLAN_QUANTITY,
            ETC_TXN_RAW_COST,
            ETC_TXN_BURDENED_COST,
            ETC_TXN_REVENUE,
            ETC_PC_RAW_COST,
            ETC_PC_BURDENED_COST,
            ETC_PC_REVENUE,
            ETC_PFC_RAW_COST,
            ETC_PFC_BURDENED_COST,
            ETC_PFC_REVENUE,
            TRANSACTION_SOURCE_CODE )
        VALUES (
            P_SRC_RES_ASG_ID,
            P_TGT_RES_ASG_ID,
            l_tot_currency_code_tab(i),
            l_etc_quantity_tab(i),
            l_etc_quantity_tab(i) * l_txn_raw_cost_rate_tab(i),
            l_etc_quantity_tab(i) * l_txn_brdn_cost_rate_tab(i),
            l_etc_quantity_tab(i) * l_txn_revenue_rate_tab(i),
            l_etc_quantity_tab(i) * l_pc_raw_cost_rate_tab(i),
            l_etc_quantity_tab(i) * l_pc_brdn_cost_rate_tab(i),
            l_etc_quantity_tab(i) * l_pc_revenue_rate_tab(i),
            l_etc_quantity_tab(i) * l_pfc_raw_cost_rate_tab(i),
            l_etc_quantity_tab(i) * l_pfc_brdn_cost_rate_tab(i),
            l_etc_quantity_tab(i) * l_pfc_revenue_rate_tab(i),
            l_transaction_source_code );

Line: 450


  /*Added for Bulk insert at version level*/
  l_blk_src_res_asg_id_tab      PA_PLSQL_DATATYPES.IdTabTyp;

Line: 488


      l_tot_currency_code_tab.delete;

Line: 489


      l_tot_quantity_tab.delete;

Line: 490


      l_tot_raw_cost_tab.delete;

Line: 491


      l_tot_brdn_cost_tab.delete;

Line: 492


      l_tot_revenue_tab.delete;

Line: 494


      l_txn_raw_cost_rate_tab.delete;

Line: 495


      l_txn_brdn_cost_rate_tab.delete;

Line: 496


      l_txn_revenue_rate_tab.delete;

Line: 497


      l_pc_raw_cost_rate_tab.delete;

Line: 498


      l_pc_brdn_cost_rate_tab.delete;

Line: 499


      l_pc_revenue_rate_tab.delete;

Line: 500


      l_pfc_raw_cost_rate_tab.delete;

Line: 501


      l_pfc_brdn_cost_rate_tab.delete;

Line: 502


      l_pfc_revenue_rate_tab.delete;

Line: 504


      l_etc_quantity_tab.delete;

Line: 572


          SELECT rate_based_flag
          INTO l_rate_based_flag
          FROM pa_resource_assignments
          WHERE resource_assignment_id = p_tgt_res_asg_id_tab(main_loop);

Line: 596


      SELECT  /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
              DECODE(l_currency_flag,
                  'PC', l_pc_currency_code,
                  'TC', txn_currency_code,
                  'PFC',l_pfc_currency_code),
              SUM(NVL(total_plan_quantity,0)),
              SUM(DECODE(l_currency_flag,
                          'PC',NVL(total_pc_raw_cost,0),
                          'TC',NVL(total_txn_raw_cost,0),
                          'PFC',NVL(total_pfc_raw_cost,0))),
              SUM(DECODE(l_currency_flag,
                          'PC', NVL(total_pc_burdened_cost,0),
                          'TC', NVL(total_txn_burdened_cost,0),
                          'PFC', NVL(total_pfc_burdened_cost,0))),
              SUM(DECODE(l_currency_flag,
                          'PC', NVL(total_pc_revenue,0),
                          'TC', NVL(total_txn_revenue,0),
                          'PFC', NVL(total_pfc_revenue,0)))
      BULK COLLECT INTO
              l_tot_currency_code_tab,
              l_tot_quantity_tab,
              l_tot_raw_cost_tab,
              l_tot_brdn_cost_tab,
              l_tot_revenue_tab
      FROM PA_FP_CALC_AMT_TMP2
      WHERE resource_assignment_id = p_src_res_asg_id_tab(main_loop)
      AND transaction_source_code = p_etc_source_code_tab(main_loop)
      GROUP BY DECODE(l_currency_flag, 'PC', l_pc_currency_code,
                                       'TC', txn_currency_code,
                                       'PFC',l_pfc_currency_code);

Line: 673


          SELECT  /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N2)*/
                  NVL(SUM(NVL(total_plan_quantity,0)),0),
                  NVL(DECODE(l_currency_flag, 'TC', SUM(NVL(total_txn_raw_cost,0)),
                                              'PC', SUM(NVL(total_pc_raw_cost,0)),
                                              'PFC', SUM(NVL(total_pfc_raw_cost,0))),0),
                  NVL(DECODE(l_currency_flag, 'TC', SUM(NVL(total_txn_burdened_cost,0)),
                                              'PC', SUM(NVL(total_pc_burdened_cost,0)),
                                              'PFC', SUM(NVL(total_pfc_burdened_cost,0))),0),
                  NVL(DECODE(l_currency_flag, 'TC', SUM(NVL(total_txn_revenue,0)),
                                              'PC', SUM(NVL(total_pc_revenue,0)),
                                              'PFC', SUM(NVL(total_pfc_revenue,0))),0),
                  NVL(SUM(NVL(total_pc_raw_cost,0)),0),
                  NVL(SUM(NVL(total_pc_burdened_cost,0)),0),
                  NVL(SUM(NVL(total_pc_revenue,0)),0),
                  NVL(SUM(NVL(total_pfc_raw_cost,0)),0),
                  NVL(SUM(NVL(total_pfc_burdened_cost,0)),0),
                  NVL(SUM(NVL(total_pfc_revenue,0)),0)
          INTO    l_txn_rate_quantity,
                  l_txn_rate_raw_cost,
                  l_txn_rate_brdn_cost,
                  l_txn_rate_revenue,
                  l_pc_rate_raw_cost,
                  l_pc_rate_brdn_cost,
                  l_pc_rate_revenue,
                  l_pfc_rate_raw_cost,
                  l_pfc_rate_brdn_cost,
                  l_pfc_rate_revenue
          FROM pa_fp_calc_amt_tmp2
          WHERE resource_assignment_id = p_src_res_asg_id_tab(main_loop)
          AND DECODE(l_currency_flag, 'TC', txn_currency_code,
              'PC', l_tot_currency_code_tab(i),
              'PFC', l_tot_currency_code_tab(i)) = l_tot_currency_code_tab(i)
          AND transaction_source_code IN ('FINANCIAL_PLAN','WORKPLAN_RESOURCES');

Line: 786


    /* If commitment is not included, record is inserted directly as
       'ETC' record, if commitment is to be considered, record is
       inserted as 'TOTAL_ETC' for further processing. */
    IF P_FP_COLS_TGT_REC.X_GEN_INCL_OPEN_COMM_FLAG = 'Y' THEN
        l_transaction_source_code := 'TOTAL_ETC';

Line: 795


    /* Insert total ETC amounts */
    FORALL i IN 1..l_blk_tot_currency_code_tab.count
        INSERT INTO PA_FP_CALC_AMT_TMP2 (
            RESOURCE_ASSIGNMENT_ID,
            TARGET_RES_ASG_ID,
            ETC_CURRENCY_CODE,
            ETC_PLAN_QUANTITY,
            ETC_TXN_RAW_COST,
            ETC_TXN_BURDENED_COST,
            ETC_TXN_REVENUE,
            ETC_PC_RAW_COST,
            ETC_PC_BURDENED_COST,
            ETC_PC_REVENUE,
            ETC_PFC_RAW_COST,
            ETC_PFC_BURDENED_COST,
            ETC_PFC_REVENUE,
            TRANSACTION_SOURCE_CODE )
        VALUES (
            l_blk_src_res_asg_id_tab(i),
            l_blk_tgt_res_asg_id_tab(i),
            l_blk_tot_currency_code_tab(i),
            l_blk_etc_quantity_tab(i),
            l_blk_etc_txn_rcost_tab(i),
            l_blk_etc_txn_bcost_tab(i),
            l_blk_etc_txn_revenue_tab(i),
            l_blk_etc_pc_rcost_tab(i),
            l_blk_etc_pc_bcost_tab(i),
            l_blk_etc_pc_revenue_tab(i),
            l_blk_etc_pfc_rcost_tab(i),
            l_blk_etc_pfc_bcost_tab(i),
            l_blk_etc_pfc_revenue_tab(i),
            l_transaction_source_code );