[Home] [Help]
239: l_gen_src_code PA_PROJ_FP_OPTIONS.GEN_COST_SRC_CODE%TYPE;
240: l_cost_based_all_from_sp_flag VARCHAR2(1);
241:
242: -- Bug 4549862: GEN_COST_BASED_REVENUE expects the BILLABLE_FLAG and
243: -- BUDGET_LINE_ID columns of the PA_FP_ROLLUP_TMP to be populated.
244:
245: -- Added l_bl_id_counter to track unique budget_line_id values for
246: -- the PA_FP_ROLLUP_TMP table. Initialize it to the MAX budget_line_id
247: -- value in the temp table to pick up where the Staffing Plan API left
242: -- Bug 4549862: GEN_COST_BASED_REVENUE expects the BILLABLE_FLAG and
243: -- BUDGET_LINE_ID columns of the PA_FP_ROLLUP_TMP to be populated.
244:
245: -- Added l_bl_id_counter to track unique budget_line_id values for
246: -- the PA_FP_ROLLUP_TMP table. Initialize it to the MAX budget_line_id
247: -- value in the temp table to pick up where the Staffing Plan API left
248: -- off, and increment by 1 prior to each Insert to the temp table.
249: -- Note: these are not valid budget_line_id values in pa_budget_lines.
250: -- Rather, we are using the column to index records for processing of
254: -- The billable flag value will be based on target task billability.
255: l_billable_flag VARCHAR2(1);
256:
257: -- Bug 4549862: Whenever Commitments map to a resource having rejection
258: -- code data (either in PA_FP_ROLLUP_TMP when the source is Staffing Plan,
259: -- or in PA_BUDGET_LINES for other cases), a generic error message will be
260: -- added once to the error stack. For each such commitment, the relevant
261: -- rejection code values should be added to the error stack. After all
262: -- Commitments have been processed, an error should be raised. This flag
335: l_cost_based_all_from_sp_flag := 'N';
336: END IF;
337:
338: -- Bug 4549862: The budget_line_id counter will keep track of the next
339: -- unique budget_line_id in the PA_FP_ROLLUP_TMP table. Initialize it
340: -- with the max rolup_id value in the table. Prior to each Insert
341: -- to the temp table, increment the counter by 1.
342: IF l_cost_based_all_from_sp_flag = 'Y' THEN
343: BEGIN
341: -- to the temp table, increment the counter by 1.
342: IF l_cost_based_all_from_sp_flag = 'Y' THEN
343: BEGIN
344: SELECT MAX(budget_line_id) INTO l_bl_id_counter
345: FROM pa_fp_rollup_tmp;
346:
347: IF l_bl_id_counter IS NULL THEN
348: l_bl_id_counter := 0;
349: END IF;
762: -- for timephased versions, get budget line rejection codes.
763: --
764: -- When l_cost_based_all_from_sp_flag = 'N', check PA_BUDGET_LINES
765: -- for rejection codes. When l_cost_based_all_from_sp_flag = 'Y',
766: -- check PA_FP_ROLLUP_TMP for rejection codes instead.
767: -- We are only interested in checking the raw cost, burden cost,
768: -- pc/pfc currency conversion rejection codes.
769:
770: IF l_cost_based_all_from_sp_flag = 'N' THEN
824: l_cost_rej_code_tab,
825: l_burden_rej_code_tab,
826: l_pc_cur_conv_rej_code_tab,
827: l_pfc_cur_conv_rej_code_tab
828: FROM pa_fp_rollup_tmp
829: WHERE resource_assignment_id = l_res_asg_id(i)
830: AND txn_currency_code = l_currency_code(i)
831: AND start_date = l_bl_start_date
832: AND ( cost_rejection_code is not null OR
856: l_cost_rej_code_tab,
857: l_burden_rej_code_tab,
858: l_pc_cur_conv_rej_code_tab,
859: l_pfc_cur_conv_rej_code_tab
860: FROM pa_fp_rollup_tmp
861: WHERE resource_assignment_id = l_res_asg_id(i)
862: AND txn_currency_code = l_currency_code(i)
863: AND ( cost_rejection_code is not null OR
864: burden_rejection_code is not null OR
973:
974: -- Bug 4549862: For the Cost-based All version from Staffing Plan
975: -- flow, we do not need to check if a budget line exists for the
976: -- current commitment being processed, since we will be inserting
977: -- the commitment data into the PA_FP_ROLLUP_TMP table instead of
978: -- pa_budget_lines for further processing. At the same time, we
979: -- still want the l_budget_lines_exist flag initialized as 'N' so
980: -- that logic for updating dates for None time phased versions is
981: -- still executed properly.
1101: END IF; -- None timephase check
1102:
1103:
1104: -- Bug 4549862: For the Cost-based All version from Staffing Plan
1105: -- flow, Insert commitment data into the PA_FP_ROLLUP_TMP table
1106: -- instead of pa_budget_lines for further processing by the Cost-
1107: -- based Revenue Generation API, which will propagate the data to
1108: -- the budget lines. For all other flows, proceed with existing
1109: -- logic for Insert/Update to the budget lines.
1119: pa_resource_assignments ra
1120: WHERE ra.resource_assignment_id = l_res_asg_id(i)
1121: AND ra.task_id = ta.task_id (+);
1122:
1123: INSERT INTO pa_fp_rollup_tmp(
1124: RESOURCE_ASSIGNMENT_ID,
1125: START_DATE,
1126: END_DATE,
1127: PERIOD_NAME,
1448: | Txn Curency Code, Budget Line Start Date) combination, we may have more |
1449: | than 1 commitment. In this case, we will have multiple records in the |
1450: | l_cal_ tables for the combination. To ensure that we only pass 1 record |
1451: | per such combination to the Calculate API, we have the following logic: |
1452: | 1. Populate PA_FP_ROLLUP_TMP with Commitment Amounts and the l_cal_ |
1453: | pl/sql table Index where the amounts came from. We use ROLLUP_ID to |
1454: | store the Index value. |
1455: | 2. Fetch Aggregated Commitment Amounts using Group By. |
1456: | Earlier, when we populated the l_cal_ tables, we made sure that the |
1469: END LOOP;
1470:
1471: -- We need to sort the pl/sql records by (RA id, currency code, start date)
1472: -- The pa_fp_calc_amt_tmp1 table should be unused now; use it to sort.
1473: DELETE PA_FP_ROLLUP_TMP;
1474: FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1475: INSERT INTO PA_FP_ROLLUP_TMP (
1476: ROLLUP_ID, -- l_cal_ table index value
1477: RESOURCE_ASSIGNMENT_ID,
1471: -- We need to sort the pl/sql records by (RA id, currency code, start date)
1472: -- The pa_fp_calc_amt_tmp1 table should be unused now; use it to sort.
1473: DELETE PA_FP_ROLLUP_TMP;
1474: FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1475: INSERT INTO PA_FP_ROLLUP_TMP (
1476: ROLLUP_ID, -- l_cal_ table index value
1477: RESOURCE_ASSIGNMENT_ID,
1478: TXN_CURRENCY_CODE,
1479: START_DATE,
1521: l_src_revenue_tab,
1522: l_cost_rate_override_tab,
1523: l_b_cost_rate_override_tab,
1524: l_bill_rate_override_tab
1525: FROM PA_FP_ROLLUP_TMP
1526: GROUP BY RESOURCE_ASSIGNMENT_ID,
1527: TXN_CURRENCY_CODE,
1528: START_DATE;
1529:
1566: | one table for Resource Assignment Ids, and one table for Currency Codes. |
1567: +==========================================================================*/
1568:
1569: -- We need to get distinct (RA id, currency code) pairs.
1570: -- The pa_fp_rollup_tmp table should be unused now; use it.
1571: DELETE PA_FP_ROLLUP_TMP;
1572: FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1573: INSERT INTO PA_FP_ROLLUP_TMP (
1574: RESOURCE_ASSIGNMENT_ID,
1567: +==========================================================================*/
1568:
1569: -- We need to get distinct (RA id, currency code) pairs.
1570: -- The pa_fp_rollup_tmp table should be unused now; use it.
1571: DELETE PA_FP_ROLLUP_TMP;
1572: FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1573: INSERT INTO PA_FP_ROLLUP_TMP (
1574: RESOURCE_ASSIGNMENT_ID,
1575: TXN_CURRENCY_CODE ) -- txn_currency_code
1569: -- We need to get distinct (RA id, currency code) pairs.
1570: -- The pa_fp_rollup_tmp table should be unused now; use it.
1571: DELETE PA_FP_ROLLUP_TMP;
1572: FORALL i IN 1..l_cal_tgt_res_asg_id_tab.count
1573: INSERT INTO PA_FP_ROLLUP_TMP (
1574: RESOURCE_ASSIGNMENT_ID,
1575: TXN_CURRENCY_CODE ) -- txn_currency_code
1576: VALUES (
1577: l_cal_tgt_res_asg_id_tab(i),
1582: TXN_CURRENCY_CODE
1583: BULK COLLECT
1584: INTO l_tgt_res_asg_id_tab,
1585: l_txn_currency_code_tab
1586: FROM PA_FP_ROLLUP_TMP;
1587:
1588: END IF; -- population of Calculate API pl/sql tables
1589:
1590:
1702: -- A. are billable
1703: -- B. do not have records in pa_fp_gen_rate_tmp for the
1704: -- (Target Resource Assignment Id, Txn Currency) combo.
1705: -- C. have planning resources in the source financial plan.
1706: -- The pa_fp_rollup_tmp table should be unused now, so use it
1707: -- to do the above processing logic in bulk.
1708:
1709: DELETE PA_FP_ROLLUP_TMP;
1710: FORALL i IN 1..l_tgt_res_asg_id_tab.count
1705: -- C. have planning resources in the source financial plan.
1706: -- The pa_fp_rollup_tmp table should be unused now, so use it
1707: -- to do the above processing logic in bulk.
1708:
1709: DELETE PA_FP_ROLLUP_TMP;
1710: FORALL i IN 1..l_tgt_res_asg_id_tab.count
1711: INSERT INTO PA_FP_ROLLUP_TMP (
1712: RESOURCE_ASSIGNMENT_ID,
1713: TXN_CURRENCY_CODE )
1707: -- to do the above processing logic in bulk.
1708:
1709: DELETE PA_FP_ROLLUP_TMP;
1710: FORALL i IN 1..l_tgt_res_asg_id_tab.count
1711: INSERT INTO PA_FP_ROLLUP_TMP (
1712: RESOURCE_ASSIGNMENT_ID,
1713: TXN_CURRENCY_CODE )
1714: VALUES (
1715: l_tgt_res_asg_id_tab(i),
1732: l_sr_txn_currency_code_tab
1733: FROM pa_resource_assignments src_ra,
1734: pa_resource_assignments tgt_ra,
1735: pa_tasks ta,
1736: pa_fp_rollup_tmp cmt
1737: WHERE tgt_ra.resource_assignment_id = cmt.resource_assignment_id
1738: AND ta.task_id (+) = NVL(tgt_ra.task_id,0) -- A. check billability
1739: AND NVL(ta.billable_flag,'Y') = 'Y' -- A. check billability
1740: AND src_ra.task_id = tgt_ra.task_id
1758: l_sr_tgt_ra_id_tab,
1759: l_sr_txn_currency_code_tab
1760: FROM pa_fp_calc_amt_tmp1 tmp1,
1761: pa_tasks ta,
1762: pa_fp_rollup_tmp cmt
1763: WHERE tmp1.transaction_source_code = 'FINANCIAL_PLAN' -- C. check finplan
1764: AND tmp1.target_res_asg_id = cmt.resource_assignment_id
1765: AND ta.task_id (+) = NVL(tmp1.task_id,0) -- A. check billability
1766: AND NVL(ta.billable_flag,'Y') = 'Y' -- A. check billability