[Home] [Help]
71:
72:
73: cursor chk_ra_for_bl_exists_csr(c_budget_version_id pa_budget_versions.budget_version_id%type) is
74: select 'Y' from dual
75: where exists (select 'Y' from pa_budget_lines bl
76: where bl.budget_version_id = c_budget_version_id
77: group by resource_assignment_id
78: having count(*) > 1);
79:
100: ,c_budget_version_id pa_budget_versions.budget_version_id%type
101: ,c_period_set_name gl_sets_of_books.period_set_name%type) is
102: select 'Y' from dual
103: where exists ( select 'Y'
104: from pa_budget_lines bl
105: ,gl_periods gl
106: where bl.budget_version_id = c_budget_version_id
107: and gl.period_type = c_period_type
108: and gl.period_set_name = c_period_set_name
125: sum(txn_raw_cost) sum_txn_raw_cost,
126: sum(txn_burdened_cost) sum_txn_burdened_cost,
127: sum(txn_revenue) sum_txn_revenue,
128: resource_assignment_id,txn_currency_code
129: from pa_budget_lines
130: where budget_version_id = c_budget_version_id
131: group by resource_assignment_id, txn_currency_code ;
132:
133: cursor get_res_assign_id_csr(c_budget_version_id pa_budget_lines.budget_version_id%type) is
129: from pa_budget_lines
130: where budget_version_id = c_budget_version_id
131: group by resource_assignment_id, txn_currency_code ;
132:
133: cursor get_res_assign_id_csr(c_budget_version_id pa_budget_lines.budget_version_id%type) is
134: select resource_assignment_id,planning_start_date from pa_resource_assignments
135: where budget_version_id = c_budget_version_id;
136:
137: -- bug 3673111, 14-JUL-04, jwhite --------------------------------------------------------
152:
153: -- Bug 4299635. The amounts are rounded to atmost 5 digits. This is done to make sure that the amounts
154: -- for date range budgets are correctly upgraded to periodic budgets. PC/PFC amounts need not be rounded
155: -- since they will re-derived by MC api which is called in PAFPUPGB.pls
156: cursor get_budget_lines_csr(l_budget_version_id pa_budget_lines.budget_version_id%type,l_res_assign_id pa_resource_assignments.resource_assignment_id%type
157: ,l_org_id pa_projects_all.org_id%type,l_per_type pa_implementations_all.pa_period_type%type,l_period_set_name gl_sets_of_books.period_set_name%type) is
158: select
159: rs.resource_assignment_id resource_assignment_id,
160: rs.txn_currency_code txn_currency_code,
209: --for a planning txn
210: DECODE(least(bl.start_date, gl.start_date),
211: gl.start_date,1,
212: 0) tot_amt_factor
213: from pa_budget_lines bl,gl_periods gl, pa_resource_assignments ra
214: where bl.budget_version_id = l_budget_version_id
215: and ra.resource_assignment_id = l_res_assign_id
216: and bl.resource_assignment_id = l_res_assign_id
217: and gl.period_type = l_per_type
254: l_accounted_per_type pa_implementations_all.pa_period_type%type;
255: l_min_date date;
256: g_upgrade_mode varchar2(100);
257: l_counter number := 0;
258: l_attribute_category pa_budget_lines.attribute_category%type;
259: l_attribute1 pa_budget_lines.attribute1%type;
260: l_attribute2 pa_budget_lines.attribute2 %type;
261: l_attribute3 pa_budget_lines.attribute3%type;
262: l_attribute4 pa_budget_lines.attribute4%type;
255: l_min_date date;
256: g_upgrade_mode varchar2(100);
257: l_counter number := 0;
258: l_attribute_category pa_budget_lines.attribute_category%type;
259: l_attribute1 pa_budget_lines.attribute1%type;
260: l_attribute2 pa_budget_lines.attribute2 %type;
261: l_attribute3 pa_budget_lines.attribute3%type;
262: l_attribute4 pa_budget_lines.attribute4%type;
263: l_attribute5 pa_budget_lines.attribute5%type;
256: g_upgrade_mode varchar2(100);
257: l_counter number := 0;
258: l_attribute_category pa_budget_lines.attribute_category%type;
259: l_attribute1 pa_budget_lines.attribute1%type;
260: l_attribute2 pa_budget_lines.attribute2 %type;
261: l_attribute3 pa_budget_lines.attribute3%type;
262: l_attribute4 pa_budget_lines.attribute4%type;
263: l_attribute5 pa_budget_lines.attribute5%type;
264: l_attribute6 pa_budget_lines.attribute6%type;
257: l_counter number := 0;
258: l_attribute_category pa_budget_lines.attribute_category%type;
259: l_attribute1 pa_budget_lines.attribute1%type;
260: l_attribute2 pa_budget_lines.attribute2 %type;
261: l_attribute3 pa_budget_lines.attribute3%type;
262: l_attribute4 pa_budget_lines.attribute4%type;
263: l_attribute5 pa_budget_lines.attribute5%type;
264: l_attribute6 pa_budget_lines.attribute6%type;
265: l_attribute7 pa_budget_lines.attribute7%type;
258: l_attribute_category pa_budget_lines.attribute_category%type;
259: l_attribute1 pa_budget_lines.attribute1%type;
260: l_attribute2 pa_budget_lines.attribute2 %type;
261: l_attribute3 pa_budget_lines.attribute3%type;
262: l_attribute4 pa_budget_lines.attribute4%type;
263: l_attribute5 pa_budget_lines.attribute5%type;
264: l_attribute6 pa_budget_lines.attribute6%type;
265: l_attribute7 pa_budget_lines.attribute7%type;
266: l_attribute8 pa_budget_lines.attribute8%type;
259: l_attribute1 pa_budget_lines.attribute1%type;
260: l_attribute2 pa_budget_lines.attribute2 %type;
261: l_attribute3 pa_budget_lines.attribute3%type;
262: l_attribute4 pa_budget_lines.attribute4%type;
263: l_attribute5 pa_budget_lines.attribute5%type;
264: l_attribute6 pa_budget_lines.attribute6%type;
265: l_attribute7 pa_budget_lines.attribute7%type;
266: l_attribute8 pa_budget_lines.attribute8%type;
267: l_attribute9 pa_budget_lines.attribute9%type;
260: l_attribute2 pa_budget_lines.attribute2 %type;
261: l_attribute3 pa_budget_lines.attribute3%type;
262: l_attribute4 pa_budget_lines.attribute4%type;
263: l_attribute5 pa_budget_lines.attribute5%type;
264: l_attribute6 pa_budget_lines.attribute6%type;
265: l_attribute7 pa_budget_lines.attribute7%type;
266: l_attribute8 pa_budget_lines.attribute8%type;
267: l_attribute9 pa_budget_lines.attribute9%type;
268: l_attribute10 pa_budget_lines.attribute10%type;
261: l_attribute3 pa_budget_lines.attribute3%type;
262: l_attribute4 pa_budget_lines.attribute4%type;
263: l_attribute5 pa_budget_lines.attribute5%type;
264: l_attribute6 pa_budget_lines.attribute6%type;
265: l_attribute7 pa_budget_lines.attribute7%type;
266: l_attribute8 pa_budget_lines.attribute8%type;
267: l_attribute9 pa_budget_lines.attribute9%type;
268: l_attribute10 pa_budget_lines.attribute10%type;
269: l_attribute11 pa_budget_lines.attribute11%type;
262: l_attribute4 pa_budget_lines.attribute4%type;
263: l_attribute5 pa_budget_lines.attribute5%type;
264: l_attribute6 pa_budget_lines.attribute6%type;
265: l_attribute7 pa_budget_lines.attribute7%type;
266: l_attribute8 pa_budget_lines.attribute8%type;
267: l_attribute9 pa_budget_lines.attribute9%type;
268: l_attribute10 pa_budget_lines.attribute10%type;
269: l_attribute11 pa_budget_lines.attribute11%type;
270: l_attribute12 pa_budget_lines.attribute12 %type;
263: l_attribute5 pa_budget_lines.attribute5%type;
264: l_attribute6 pa_budget_lines.attribute6%type;
265: l_attribute7 pa_budget_lines.attribute7%type;
266: l_attribute8 pa_budget_lines.attribute8%type;
267: l_attribute9 pa_budget_lines.attribute9%type;
268: l_attribute10 pa_budget_lines.attribute10%type;
269: l_attribute11 pa_budget_lines.attribute11%type;
270: l_attribute12 pa_budget_lines.attribute12 %type;
271: l_attribute13 pa_budget_lines.attribute13%type;
264: l_attribute6 pa_budget_lines.attribute6%type;
265: l_attribute7 pa_budget_lines.attribute7%type;
266: l_attribute8 pa_budget_lines.attribute8%type;
267: l_attribute9 pa_budget_lines.attribute9%type;
268: l_attribute10 pa_budget_lines.attribute10%type;
269: l_attribute11 pa_budget_lines.attribute11%type;
270: l_attribute12 pa_budget_lines.attribute12 %type;
271: l_attribute13 pa_budget_lines.attribute13%type;
272: l_attribute14 pa_budget_lines.attribute14%type;
265: l_attribute7 pa_budget_lines.attribute7%type;
266: l_attribute8 pa_budget_lines.attribute8%type;
267: l_attribute9 pa_budget_lines.attribute9%type;
268: l_attribute10 pa_budget_lines.attribute10%type;
269: l_attribute11 pa_budget_lines.attribute11%type;
270: l_attribute12 pa_budget_lines.attribute12 %type;
271: l_attribute13 pa_budget_lines.attribute13%type;
272: l_attribute14 pa_budget_lines.attribute14%type;
273: l_attribute15 pa_budget_lines.attribute15%type ;
266: l_attribute8 pa_budget_lines.attribute8%type;
267: l_attribute9 pa_budget_lines.attribute9%type;
268: l_attribute10 pa_budget_lines.attribute10%type;
269: l_attribute11 pa_budget_lines.attribute11%type;
270: l_attribute12 pa_budget_lines.attribute12 %type;
271: l_attribute13 pa_budget_lines.attribute13%type;
272: l_attribute14 pa_budget_lines.attribute14%type;
273: l_attribute15 pa_budget_lines.attribute15%type ;
274: l_plan_ver_exists varchar2(1) := 'Y';
267: l_attribute9 pa_budget_lines.attribute9%type;
268: l_attribute10 pa_budget_lines.attribute10%type;
269: l_attribute11 pa_budget_lines.attribute11%type;
270: l_attribute12 pa_budget_lines.attribute12 %type;
271: l_attribute13 pa_budget_lines.attribute13%type;
272: l_attribute14 pa_budget_lines.attribute14%type;
273: l_attribute15 pa_budget_lines.attribute15%type ;
274: l_plan_ver_exists varchar2(1) := 'Y';
275: l_debug_mode varchar2(30);
268: l_attribute10 pa_budget_lines.attribute10%type;
269: l_attribute11 pa_budget_lines.attribute11%type;
270: l_attribute12 pa_budget_lines.attribute12 %type;
271: l_attribute13 pa_budget_lines.attribute13%type;
272: l_attribute14 pa_budget_lines.attribute14%type;
273: l_attribute15 pa_budget_lines.attribute15%type ;
274: l_plan_ver_exists varchar2(1) := 'Y';
275: l_debug_mode varchar2(30);
276: l_module_name VARCHAR2(100):= 'pa.plsql.PA_DATE_RANGE_PKG';
269: l_attribute11 pa_budget_lines.attribute11%type;
270: l_attribute12 pa_budget_lines.attribute12 %type;
271: l_attribute13 pa_budget_lines.attribute13%type;
272: l_attribute14 pa_budget_lines.attribute14%type;
273: l_attribute15 pa_budget_lines.attribute15%type ;
274: l_plan_ver_exists varchar2(1) := 'Y';
275: l_debug_mode varchar2(30);
276: l_module_name VARCHAR2(100):= 'pa.plsql.PA_DATE_RANGE_PKG';
277: l_msg_index_out NUMBER;
562: begin
563:
564: SELECT min(start_date)
565: INTO l_project_start_date
566: FROM pa_budget_lines
567: WHERE budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
568:
569: /* Following "if" takes care of case when there no budget lines for the budget verison
570: or
718: end if;
719: -- Loop for each budget line for the given version
720: for l_get_non_time_multi_csr in get_non_time_multi_csr(l_get_elig_bud_ver_csr.budget_version_id)
721: loop
722: insert into pa_budget_lines_m_upg_dtrange
723: (
724: resource_assignment_id,
725: start_date,
726: last_update_date,
896: txn_burdened_cost ,
897: txn_revenue,
898: bucketing_period_code,
899: budget_line_id ,
900: budget_version_id from pa_budget_lines where
901: resource_assignment_id = l_get_non_time_multi_csr.resource_assignment_id
902: and txn_currency_code = l_get_non_time_multi_csr.txn_currency_code
903: and budget_version_id = l_get_elig_bud_ver_csr.budget_version_id);
904:
901: resource_assignment_id = l_get_non_time_multi_csr.resource_assignment_id
902: and txn_currency_code = l_get_non_time_multi_csr.txn_currency_code
903: and budget_version_id = l_get_elig_bud_ver_csr.budget_version_id);
904:
905: delete from pa_budget_lines
906: where resource_assignment_id = l_get_non_time_multi_csr.resource_assignment_id
907: and txn_currency_code = l_get_non_time_multi_csr.txn_currency_code
908: and start_date <> l_get_non_time_multi_csr.min_date
909: and budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
907: and txn_currency_code = l_get_non_time_multi_csr.txn_currency_code
908: and start_date <> l_get_non_time_multi_csr.min_date
909: and budget_version_id = l_get_elig_bud_ver_csr.budget_version_id;
910:
911: update pa_budget_lines
912: set start_date = l_get_non_time_multi_csr.min_date,
913: end_date = l_get_non_time_multi_csr.max_date,
914: quantity = l_get_non_time_multi_csr.sum_quantity,
915: raw_cost = l_get_non_time_multi_csr.sum_raw_cost,
979: l_attribute12 ,
980: l_attribute13,
981: l_attribute14 ,
982: l_attribute15
983: from pa_budget_lines where
984: start_date = l_get_res_assign_id_csr.planning_start_date
985: and resource_assignment_id = l_get_res_assign_id_csr.resource_assignment_id;
986:
987: exception
1014: pa_debug.g_err_stage := 'Inserting into Backup Table';
1015: pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1016: end if;
1017:
1018: insert into pa_budget_lines_m_upg_dtrange
1019: (
1020: resource_assignment_id,
1021: start_date,
1022: last_update_date,
1192: txn_burdened_cost ,
1193: txn_revenue,
1194: bucketing_period_code,
1195: budget_line_id ,
1196: budget_version_id from pa_budget_lines where
1197: budget_version_id = l_get_elig_bud_ver_csr.budget_version_id
1198: and resource_assignment_id = l_get_res_assign_id_csr.resource_assignment_id;
1199:
1200: l_counter := 0;
1246: -- 1) Moved statement "l_get_budget_lines_tbl.delete;" out of loop so ALL records could be stored
1247: -- for subsequent processing. If inside loop, then only last record stored.
1248: --
1249: -- 2) For the start/end dates, changed the source to the GL start/end dates. This is necessary
1250: -- to store the period start and end dates corresponding to the Period_Name on the pa_budget_lines.
1251: --
1252: -- 3) Added PERIOD_NAME to table array for the subsequent insert statement
1253: --
1254:
1403:
1404:
1405: -- Loop to traverse through the array or records.
1406: IF l_debug_mode = 'Y' THEN
1407: pa_debug.g_err_stage := 'Deleting from pa_budget_lines table';
1408: pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1409: end if;
1410: for i in l_get_budget_lines_tbl.first .. l_get_budget_lines_tbl.last
1411: loop
1408: pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1409: end if;
1410: for i in l_get_budget_lines_tbl.first .. l_get_budget_lines_tbl.last
1411: loop
1412: delete pa_budget_lines
1413: where resource_assignment_id = l_get_res_assign_id_csr.resource_assignment_id and
1414: budget_version_id = l_get_elig_bud_ver_csr.budget_version_id and
1415: txn_currency_code = l_get_budget_lines_tbl(i).txn_currency_code;
1416: end loop;
1419: -- Loop to traverse through the array or records for insertion.
1420: for i in l_get_budget_lines_tbl.first .. l_get_budget_lines_tbl.last
1421: loop
1422:
1423: insert into pa_budget_lines(
1424: last_update_date,
1425: last_updated_by,
1426: creation_date,
1427: created_by,
1483: l_get_budget_lines_tbl(i).spr_project_revenue,
1484: l_get_budget_lines_tbl(i).spr_txn_raw_cost,
1485: l_get_budget_lines_tbl(i).spr_txn_burdened_cost,
1486: l_get_budget_lines_tbl(i).spr_txn_revenue,
1487: pa_budget_lines_s.nextval,
1488: l_get_elig_bud_ver_csr.budget_version_id,
1489: l_get_budget_lines_tbl(i).PERIOD_NAME, /* bug 3673111 */
1490: --Bug 4046524.Columns included for this bug start here
1491: l_get_elig_bud_ver_csr.project_currency_code,
1521: end loop;
1522:
1523: -- Loop to traverse through the array or records for insertion ends.
1524: IF l_debug_mode = 'Y' THEN
1525: pa_debug.g_err_stage := 'Updating pa_budget_lines table';
1526: pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1527: end if;
1528: update pa_budget_lines
1529: set
1524: IF l_debug_mode = 'Y' THEN
1525: pa_debug.g_err_stage := 'Updating pa_budget_lines table';
1526: pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1527: end if;
1528: update pa_budget_lines
1529: set
1530: attribute_category = l_attribute_category ,
1531: attribute1 = l_attribute1 ,
1532: attribute2 = l_attribute2 ,
1678: gl.start_date,decode(least(bl.end_date,gl.end_date),
1679: gl.end_date,gl.end_date-bl.start_date+1,
1680: bl.end_date,bl.end_date-bl.start_date+1))
1681: )),0)-(bl.end_date-bl.start_date+1)) factor
1682: FROM pa_budget_lines bl,
1683: (SELECT gl.start_date start_date,
1684: gl.end_date end_date,
1685: gl.period_name period_name
1686: FROM gl_periods gl, pa_implementations_all pi, gl_sets_of_books sob