[Home] [Help]
240: l_gen_src_code PA_PROJ_FP_OPTIONS.GEN_COST_SRC_CODE%TYPE;
241: l_cost_based_all_from_sp_flag VARCHAR2(1);
242:
243: -- Bug 4549862: GEN_COST_BASED_REVENUE expects the BILLABLE_FLAG and
244: -- BUDGET_LINE_ID columns of the PA_FP_ROLLUP_TMP to be populated.
245:
246: -- Added l_bl_id_counter to track unique budget_line_id values for
247: -- the PA_FP_ROLLUP_TMP table. Initialize it to the MAX budget_line_id
248: -- value in the temp table to pick up where the Staffing Plan API left
243: -- Bug 4549862: GEN_COST_BASED_REVENUE expects the BILLABLE_FLAG and
244: -- BUDGET_LINE_ID columns of the PA_FP_ROLLUP_TMP to be populated.
245:
246: -- Added l_bl_id_counter to track unique budget_line_id values for
247: -- the PA_FP_ROLLUP_TMP table. Initialize it to the MAX budget_line_id
248: -- value in the temp table to pick up where the Staffing Plan API left
249: -- off, and increment by 1 prior to each Insert to the temp table.
250: -- Note: these are not valid budget_line_id values in pa_budget_lines.
251: -- Rather, we are using the column to index records for processing of
255: -- The billable flag value will be based on target task billability.
256: l_billable_flag VARCHAR2(1);
257:
258: -- Bug 4549862: Whenever Commitments map to a resource having rejection
259: -- code data (either in PA_FP_ROLLUP_TMP when the source is Staffing Plan,
260: -- or in PA_BUDGET_LINES for other cases), a generic error message will be
261: -- added once to the error stack. For each such commitment, the relevant
262: -- rejection code values should be added to the error stack. After all
263: -- Commitments have been processed, an error should be raised. This flag
336: l_cost_based_all_from_sp_flag := 'N';
337: END IF;
338:
339: -- Bug 4549862: The budget_line_id counter will keep track of the next
340: -- unique budget_line_id in the PA_FP_ROLLUP_TMP table. Initialize it
341: -- with the max rolup_id value in the table. Prior to each Insert
342: -- to the temp table, increment the counter by 1.
343: IF l_cost_based_all_from_sp_flag = 'Y' THEN
344: BEGIN
342: -- to the temp table, increment the counter by 1.
343: IF l_cost_based_all_from_sp_flag = 'Y' THEN
344: BEGIN
345: SELECT MAX(budget_line_id) INTO l_bl_id_counter
346: FROM pa_fp_rollup_tmp;
347:
348: IF l_bl_id_counter IS NULL THEN
349: l_bl_id_counter := 0;
350: END IF;
770: -- for timephased versions, get budget line rejection codes.
771: --
772: -- When l_cost_based_all_from_sp_flag = 'N', check PA_BUDGET_LINES
773: -- for rejection codes. When l_cost_based_all_from_sp_flag = 'Y',
774: -- check PA_FP_ROLLUP_TMP for rejection codes instead.
775: -- We are only interested in checking the raw cost, burden cost,
776: -- pc/pfc currency conversion rejection codes.
777:
778: IF l_cost_based_all_from_sp_flag = 'N' THEN
832: l_cost_rej_code_tab,
833: l_burden_rej_code_tab,
834: l_pc_cur_conv_rej_code_tab,
835: l_pfc_cur_conv_rej_code_tab
836: FROM pa_fp_rollup_tmp
837: WHERE resource_assignment_id = l_res_asg_id(i)
838: AND txn_currency_code = l_currency_code(i)
839: AND start_date = l_bl_start_date
840: AND ( cost_rejection_code is not null OR
864: l_cost_rej_code_tab,
865: l_burden_rej_code_tab,
866: l_pc_cur_conv_rej_code_tab,
867: l_pfc_cur_conv_rej_code_tab
868: FROM pa_fp_rollup_tmp
869: WHERE resource_assignment_id = l_res_asg_id(i)
870: AND txn_currency_code = l_currency_code(i)
871: AND ( cost_rejection_code is not null OR
872: burden_rejection_code is not null OR
981:
982: -- Bug 4549862: For the Cost-based All version from Staffing Plan
983: -- flow, we do not need to check if a budget line exists for the
984: -- current commitment being processed, since we will be inserting
985: -- the commitment data into the PA_FP_ROLLUP_TMP table instead of
986: -- pa_budget_lines for further processing. At the same time, we
987: -- still want the l_budget_lines_exist flag initialized as 'N' so
988: -- that logic for updating dates for None time phased versions is
989: -- still executed properly.
1109: END IF; -- None timephase check
1110:
1111:
1112: -- Bug 4549862: For the Cost-based All version from Staffing Plan
1113: -- flow, Insert commitment data into the PA_FP_ROLLUP_TMP table
1114: -- instead of pa_budget_lines for further processing by the Cost-
1115: -- based Revenue Generation API, which will propagate the data to
1116: -- the budget lines. For all other flows, proceed with existing
1117: -- logic for Insert/Update to the budget lines.
1127: pa_resource_assignments ra
1128: WHERE ra.resource_assignment_id = l_res_asg_id(i)
1129: AND ra.task_id = ta.task_id (+);
1130:
1131: INSERT INTO pa_fp_rollup_tmp(
1132: RESOURCE_ASSIGNMENT_ID,
1133: START_DATE,
1134: END_DATE,
1135: PERIOD_NAME,
1456: | Txn Curency Code, Budget Line Start Date) combination, we may have more |
1457: | than 1 commitment. In this case, we will have multiple records in the |
1458: | l_cal_ tables for the combination. To ensure that we only pass 1 record |
1459: | per such combination to the Calculate API, we have the following logic: |
1460: | 1. Populate PA_FP_ROLLUP_TMP with Commitment Amounts and the l_cal_ |
1461: | pl/sql table Index where the amounts came from. We use ROLLUP_ID to |
1462: | store the Index value. |
1463: | 2. Fetch Aggregated Commitment Amounts using Group By. |
1464: | Earlier, when we populated the l_cal_ tables, we made sure that the |
1477: END LOOP;
1478:
1479: -- We need to sort the pl/sql records by (RA id, currency code, start date)
1480: -- The pa_fp_calc_amt_tmp1 table should be unused now; use it to sort.
1481: DELETE PA_FP_ROLLUP_TMP;
1482: FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1483: INSERT INTO PA_FP_ROLLUP_TMP (
1484: ROLLUP_ID, -- l_cal_ table index value
1485: RESOURCE_ASSIGNMENT_ID,
1479: -- We need to sort the pl/sql records by (RA id, currency code, start date)
1480: -- The pa_fp_calc_amt_tmp1 table should be unused now; use it to sort.
1481: DELETE PA_FP_ROLLUP_TMP;
1482: FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1483: INSERT INTO PA_FP_ROLLUP_TMP (
1484: ROLLUP_ID, -- l_cal_ table index value
1485: RESOURCE_ASSIGNMENT_ID,
1486: TXN_CURRENCY_CODE,
1487: START_DATE,
1529: l_src_revenue_tab,
1530: l_cost_rate_override_tab,
1531: l_b_cost_rate_override_tab,
1532: l_bill_rate_override_tab
1533: FROM PA_FP_ROLLUP_TMP
1534: GROUP BY RESOURCE_ASSIGNMENT_ID,
1535: TXN_CURRENCY_CODE,
1536: START_DATE;
1537:
1574: | one table for Resource Assignment Ids, and one table for Currency Codes. |
1575: +==========================================================================*/
1576:
1577: -- We need to get distinct (RA id, currency code) pairs.
1578: -- The pa_fp_rollup_tmp table should be unused now; use it.
1579: DELETE PA_FP_ROLLUP_TMP;
1580: FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1581: INSERT INTO PA_FP_ROLLUP_TMP (
1582: RESOURCE_ASSIGNMENT_ID,
1575: +==========================================================================*/
1576:
1577: -- We need to get distinct (RA id, currency code) pairs.
1578: -- The pa_fp_rollup_tmp table should be unused now; use it.
1579: DELETE PA_FP_ROLLUP_TMP;
1580: FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1581: INSERT INTO PA_FP_ROLLUP_TMP (
1582: RESOURCE_ASSIGNMENT_ID,
1583: TXN_CURRENCY_CODE ) -- txn_currency_code
1577: -- We need to get distinct (RA id, currency code) pairs.
1578: -- The pa_fp_rollup_tmp table should be unused now; use it.
1579: DELETE PA_FP_ROLLUP_TMP;
1580: FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1581: INSERT INTO PA_FP_ROLLUP_TMP (
1582: RESOURCE_ASSIGNMENT_ID,
1583: TXN_CURRENCY_CODE ) -- txn_currency_code
1584: VALUES (
1585: l_cal_tgt_res_asg_id_tab(i),
1590: TXN_CURRENCY_CODE
1591: BULK COLLECT
1592: INTO l_tgt_res_asg_id_tab,
1593: l_txn_currency_code_tab
1594: FROM PA_FP_ROLLUP_TMP;
1595:
1596: END IF; -- population of Calculate API pl/sql tables
1597:
1598:
1710: -- A. are billable
1711: -- B. do not have records in pa_fp_gen_rate_tmp for the
1712: -- (Target Resource Assignment Id, Txn Currency) combo.
1713: -- C. have planning resources in the source financial plan.
1714: -- The pa_fp_rollup_tmp table should be unused now, so use it
1715: -- to do the above processing logic in bulk.
1716:
1717: DELETE PA_FP_ROLLUP_TMP;
1718: FORALL i IN 1..l_tgt_res_asg_id_tab.count
1713: -- C. have planning resources in the source financial plan.
1714: -- The pa_fp_rollup_tmp table should be unused now, so use it
1715: -- to do the above processing logic in bulk.
1716:
1717: DELETE PA_FP_ROLLUP_TMP;
1718: FORALL i IN 1..l_tgt_res_asg_id_tab.count
1719: INSERT INTO PA_FP_ROLLUP_TMP (
1720: RESOURCE_ASSIGNMENT_ID,
1721: TXN_CURRENCY_CODE )
1715: -- to do the above processing logic in bulk.
1716:
1717: DELETE PA_FP_ROLLUP_TMP;
1718: FORALL i IN 1..l_tgt_res_asg_id_tab.count
1719: INSERT INTO PA_FP_ROLLUP_TMP (
1720: RESOURCE_ASSIGNMENT_ID,
1721: TXN_CURRENCY_CODE )
1722: VALUES (
1723: l_tgt_res_asg_id_tab(i),
1740: l_sr_txn_currency_code_tab
1741: FROM pa_resource_assignments src_ra,
1742: pa_resource_assignments tgt_ra,
1743: pa_tasks ta,
1744: pa_fp_rollup_tmp cmt
1745: WHERE tgt_ra.resource_assignment_id = cmt.resource_assignment_id
1746: AND ta.task_id (+) = NVL(tgt_ra.task_id,0) -- A. check billability
1747: AND NVL(ta.billable_flag,'Y') = 'Y' -- A. check billability
1748: AND src_ra.task_id = tgt_ra.task_id
1766: l_sr_tgt_ra_id_tab,
1767: l_sr_txn_currency_code_tab
1768: FROM pa_fp_calc_amt_tmp1 tmp1,
1769: pa_tasks ta,
1770: pa_fp_rollup_tmp cmt
1771: WHERE tmp1.transaction_source_code = 'FINANCIAL_PLAN' -- C. check finplan
1772: AND tmp1.target_res_asg_id = cmt.resource_assignment_id
1773: AND ta.task_id (+) = NVL(tmp1.task_id,0) -- A. check billability
1774: AND NVL(ta.billable_flag,'Y') = 'Y' -- A. check billability