[Home] [Help]
3: PROCEDURE create_budget_detail(
4: l_proposal_id IGW_budget_periods.proposal_id%TYPE
5: ,l_version_id IGW_budget_periods.version_id%TYPE
6: ,l_budget_period_id IGW_budget_periods.budget_period_id%TYPE
7: ,l_line_item_id IGW_budget_details.line_item_id%TYPE
8: ,l_expenditure_type IGW_budget_details.expenditure_type%TYPE
9: ,l_budget_category_code IGW_budget_details.budget_category_code%TYPE
10: ,l_expenditure_category_flag IGW_budget_details.expenditure_category_flag%TYPE
11: ,l_line_item_description IGW_budget_details.line_item_description%TYPE
4: l_proposal_id IGW_budget_periods.proposal_id%TYPE
5: ,l_version_id IGW_budget_periods.version_id%TYPE
6: ,l_budget_period_id IGW_budget_periods.budget_period_id%TYPE
7: ,l_line_item_id IGW_budget_details.line_item_id%TYPE
8: ,l_expenditure_type IGW_budget_details.expenditure_type%TYPE
9: ,l_budget_category_code IGW_budget_details.budget_category_code%TYPE
10: ,l_expenditure_category_flag IGW_budget_details.expenditure_category_flag%TYPE
11: ,l_line_item_description IGW_budget_details.line_item_description%TYPE
12: ,l_based_on_line_item IGW_budget_details.based_on_line_item%TYPE
5: ,l_version_id IGW_budget_periods.version_id%TYPE
6: ,l_budget_period_id IGW_budget_periods.budget_period_id%TYPE
7: ,l_line_item_id IGW_budget_details.line_item_id%TYPE
8: ,l_expenditure_type IGW_budget_details.expenditure_type%TYPE
9: ,l_budget_category_code IGW_budget_details.budget_category_code%TYPE
10: ,l_expenditure_category_flag IGW_budget_details.expenditure_category_flag%TYPE
11: ,l_line_item_description IGW_budget_details.line_item_description%TYPE
12: ,l_based_on_line_item IGW_budget_details.based_on_line_item%TYPE
13: ,l_line_item_cost NUMBER
6: ,l_budget_period_id IGW_budget_periods.budget_period_id%TYPE
7: ,l_line_item_id IGW_budget_details.line_item_id%TYPE
8: ,l_expenditure_type IGW_budget_details.expenditure_type%TYPE
9: ,l_budget_category_code IGW_budget_details.budget_category_code%TYPE
10: ,l_expenditure_category_flag IGW_budget_details.expenditure_category_flag%TYPE
11: ,l_line_item_description IGW_budget_details.line_item_description%TYPE
12: ,l_based_on_line_item IGW_budget_details.based_on_line_item%TYPE
13: ,l_line_item_cost NUMBER
14: ,l_cost_sharing_amount NUMBER
7: ,l_line_item_id IGW_budget_details.line_item_id%TYPE
8: ,l_expenditure_type IGW_budget_details.expenditure_type%TYPE
9: ,l_budget_category_code IGW_budget_details.budget_category_code%TYPE
10: ,l_expenditure_category_flag IGW_budget_details.expenditure_category_flag%TYPE
11: ,l_line_item_description IGW_budget_details.line_item_description%TYPE
12: ,l_based_on_line_item IGW_budget_details.based_on_line_item%TYPE
13: ,l_line_item_cost NUMBER
14: ,l_cost_sharing_amount NUMBER
15: ,l_underrecovery_amount NUMBER
8: ,l_expenditure_type IGW_budget_details.expenditure_type%TYPE
9: ,l_budget_category_code IGW_budget_details.budget_category_code%TYPE
10: ,l_expenditure_category_flag IGW_budget_details.expenditure_category_flag%TYPE
11: ,l_line_item_description IGW_budget_details.line_item_description%TYPE
12: ,l_based_on_line_item IGW_budget_details.based_on_line_item%TYPE
13: ,l_line_item_cost NUMBER
14: ,l_cost_sharing_amount NUMBER
15: ,l_underrecovery_amount NUMBER
16: ,l_apply_inflation_flag VARCHAR2
13: ,l_line_item_cost NUMBER
14: ,l_cost_sharing_amount NUMBER
15: ,l_underrecovery_amount NUMBER
16: ,l_apply_inflation_flag VARCHAR2
17: ,l_budget_justification IGW_budget_details.budget_justification%TYPE
18: ,l_location_code VARCHAR2) is
19: BEGIN
20: insert into IGW_budget_details(
21: proposal_id
16: ,l_apply_inflation_flag VARCHAR2
17: ,l_budget_justification IGW_budget_details.budget_justification%TYPE
18: ,l_location_code VARCHAR2) is
19: BEGIN
20: insert into IGW_budget_details(
21: proposal_id
22: ,version_id
23: ,budget_period_id
24: ,line_item_id
42: values (
43: l_proposal_id
44: ,l_version_id
45: ,l_budget_period_id
46: ,igw_budget_details_s.nextval
47: ,l_expenditure_type
48: ,l_budget_category_code
49: ,l_expenditure_category_flag
50: ,l_line_item_description
109: ,p_apply_rate_flag VARCHAR2
110: ,p_calculated_cost NUMBER
111: ,p_calculated_cost_sharing NUMBER) is
112: BEGIN
113: insert into igw_budget_details_cal_amts (
114: proposal_id
115: ,version_id
116: ,budget_period_id
117: ,line_item_id
193: , pbd.apply_inflation_flag
194: , pbd.budget_justification
195: , pbd.location_code
196: , et.personnel_attached_flag
197: from igw_budget_details pbd
198: , igw_budget_expenditures_v et
199: where pbd.expenditure_type = et.budget_expenditure
200: and pbd.expenditure_category_flag = et.expenditure_category_flag
201: and pbd.proposal_id = p_proposal_id
288: fnd_msg_pub.initialize;
289: begin
290: select '1'
291: into l_value
292: from igw_budget_details pbd
293: where pbd.proposal_id = p_proposal_id
294: and pbd.version_id = p_version_id
295: and pbd.budget_period_id <> 1
296: and rownum < 2;
334: if rec_budget_details.personnel_attached_flag = 'N' then
335: BEGIN
336: select apply_rate_flag
337: into l_apply_rate_flag_oh
338: from igw_budget_details_cal_amts
339: where line_item_id = rec_budget_details.line_item_id;
340: EXCEPTION
341: when no_data_found then null;
342: End;
492: ,rec_budget_details.budget_justification
493: ,rec_budget_details.location_code);
494:
495: if l_rate_class_id_oh is not null and l_rate_type_id_oh is not null then
496: insert into igw_budget_details_cal_amts (
497: proposal_id
498: ,version_id
499: ,budget_period_id
500: ,line_item_id
511: values (
512: rec_budget_details.proposal_id
513: ,rec_budget_details.version_id
514: ,l_budget_period_id
515: ,igw_budget_details_s.currval
516: ,l_rate_class_id_oh
517: ,l_rate_type_id_oh
518: ,l_apply_rate_flag_oh
519: ,nvl(l_oh_value_ov,0)
605: ,rec_budget_details.budget_justification
606: ,rec_budget_details.location_code);
607:
608: if l_rate_class_id_oh is not null and l_rate_type_id_oh is not null then
609: insert into igw_budget_details_cal_amts (
610: proposal_id
611: ,version_id
612: ,budget_period_id
613: ,line_item_id
624: values (
625: rec_budget_details.proposal_id
626: ,rec_budget_details.version_id
627: ,l_budget_period_id
628: ,igw_budget_details_s.currval
629: ,l_rate_class_id_oh
630: ,l_rate_type_id_oh
631: ,l_apply_rate_flag_oh
632: ,0
638: ,fnd_global.login_id);
639: end if;
640:
641: if l_rate_class_id_eb is not null and l_rate_type_id_eb is not null then
642: insert into igw_budget_details_cal_amts (
643: proposal_id
644: ,version_id
645: ,budget_period_id
646: ,line_item_id
657: values (
658: rec_budget_details.proposal_id
659: ,rec_budget_details.version_id
660: ,l_budget_period_id
661: ,igw_budget_details_s.currval
662: ,l_rate_class_id_eb
663: ,l_rate_type_id_eb
664: ,l_apply_rate_flag_eb
665: ,0
867: igw_budget_personnel_s.nextval
868: ,rec_budget_details.proposal_id
869: ,rec_budget_details.version_id
870: ,l_budget_period_id
871: ,igw_budget_details_s.currval
872: ,rec_budget_personnel.person_id
873: ,rec_budget_personnel.party_id
874: ,l_personnel_start_date
875: ,l_personnel_end_date
908: ,rec_budget_personnel.cost_sharing_percent/100 * l_eb_value_ov);
909: end if;
910: END LOOP; --for rec_personnel_budget
911: end if;
912: select igw_budget_details_s.currval
913: into l_line_item_seq
914: from dual;
915:
916:
913: into l_line_item_seq
914: from dual;
915:
916:
917: update igw_budget_details_cal_amts pdc
918: set pdc.calculated_cost =
919: (select nvl(sum(ppc.calculated_cost),0)
920: from igw_budget_personnel_cal_amts ppc
921: , igw_budget_personnel_details ppd
918: set pdc.calculated_cost =
919: (select nvl(sum(ppc.calculated_cost),0)
920: from igw_budget_personnel_cal_amts ppc
921: , igw_budget_personnel_details ppd
922: , igw_budget_details pd
923: where pd.line_item_id = ppd.line_item_id
924: and ppd.budget_personnel_detail_id =ppc.budget_personnel_detail_id
925: and pd.proposal_id = p_proposal_id
926: and pd.version_id = p_version_id
931: , pdc.calculated_cost_sharing =
932: (select nvl(sum(ppc.calculated_cost_sharing),0)
933: from igw_budget_personnel_cal_amts ppc
934: , igw_budget_personnel_details ppd
935: , igw_budget_details pd
936: where pd.line_item_id = ppd.line_item_id
937: and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
938: and pd.proposal_id = p_proposal_id
939: and pd.version_id = p_version_id
942: and ppc.rate_class_id = pdc.rate_class_id
943: and ppc.rate_type_id = pdc.rate_type_id)
944: where pdc.line_item_id = l_line_item_seq;
945:
946: update igw_budget_details pdb
947: set line_item_cost =
948: (select nvl(sum(salary_requested),0)
949: from igw_budget_personnel_details ppd
950: where ppd.line_item_id = l_line_item_seq)
967: , nvl(sum(underrecovery_amount),0)
968: into l_direct_cost1
969: , l_cost_share1
970: , l_underrecovery
971: from igw_budget_details
972: where proposal_id = p_proposal_id
973: and version_id = p_version_id
974: and budget_period_id = i+1;
975:
976: select nvl(sum(calculated_cost_sharing),0)
977: , nvl(sum(calculated_cost),0)
978: into l_cost_share2
979: , l_indirect_cost
980: from igw_budget_details_cal_amts pc
981: where proposal_id = p_proposal_id
982: and version_id = p_version_id
983: and budget_period_id = i+1
984: and pc.rate_class_id = ( select pr.rate_class_id
988: select nvl(sum(calculated_cost_sharing),0)
989: , nvl(sum(calculated_cost),0)
990: into l_cost_share3
991: , l_direct_cost2
992: from igw_budget_details_cal_amts pc
993: where proposal_id = p_proposal_id
994: and version_id = p_version_id
995: and budget_period_id = i+1
996: and pc.rate_class_id = ( select pr.rate_class_id
1109: , pbd.apply_inflation_flag
1110: , pbd.budget_justification
1111: , pbd.location_code
1112: , et.personnel_attached_flag
1113: from igw_budget_details pbd
1114: , igw_budget_expenditures_v et
1115: where pbd.expenditure_type = et.budget_expenditure
1116: and pbd.expenditure_category_flag = et.expenditure_category_flag
1117: and pbd.line_item_id = p_line_item_id;
1229: end if;
1230: BEGIN
1231: select apply_rate_flag
1232: into l_apply_rate_flag_oh
1233: from igw_budget_details_cal_amts
1234: where line_item_id = rec_budget_details.line_item_id;
1235: EXCEPTION
1236: when no_data_found then null;
1237: End;
1238:
1239: BEGIN
1240: select distinct based_on_line_item
1241: into l_dummy_based_on
1242: from igw_budget_details
1243: where based_on_line_item = rec_budget_details.line_item_id;
1244: EXCEPTION
1245: when no_data_found then null;
1246: End;
1246: End;
1247:
1248:
1249: if l_dummy_based_on is not null then
1250: delete from igw_budget_details_cal_amts
1251: where line_item_id IN (select line_item_id
1252: from igw_budget_details pd
1253: where pd.based_on_line_item = l_dummy_based_on);
1254:
1248:
1249: if l_dummy_based_on is not null then
1250: delete from igw_budget_details_cal_amts
1251: where line_item_id IN (select line_item_id
1252: from igw_budget_details pd
1253: where pd.based_on_line_item = l_dummy_based_on);
1254:
1255: delete from igw_budget_details
1256: where based_on_line_item = l_dummy_based_on;
1251: where line_item_id IN (select line_item_id
1252: from igw_budget_details pd
1253: where pd.based_on_line_item = l_dummy_based_on);
1254:
1255: delete from igw_budget_details
1256: where based_on_line_item = l_dummy_based_on;
1257: end if;
1258:
1259: l_input_amount := rec_budget_details.line_item_cost;
1366:
1367:
1368:
1369: if l_rate_class_id_oh is not null and l_rate_type_id_oh is not null then
1370: insert into igw_budget_details_cal_amts (
1371: proposal_id
1372: ,version_id
1373: ,budget_period_id
1374: ,line_item_id
1385: values (
1386: rec_budget_details.proposal_id
1387: ,rec_budget_details.version_id
1388: ,i
1389: ,igw_budget_details_s.currval
1390: ,l_rate_class_id_oh
1391: ,l_rate_type_id_oh
1392: ,l_apply_rate_flag_oh
1393: ,l_oh_value
1435: END;
1436: BEGIN
1437: select distinct based_on_line_item
1438: into l_dummy_based_on
1439: from igw_budget_details
1440: where based_on_line_item = rec_budget_details.line_item_id;
1441:
1442: EXCEPTION
1443: when no_data_found then null;
1446: if l_dummy_based_on is not null then
1447:
1448: delete from igw_budget_personnel_cal_amts
1449: where budget_personnel_detail_id IN (select budget_personnel_detail_id
1450: from igw_budget_details pd
1451: , igw_budget_personnel_details ppd
1452: where pd.line_item_id = ppd.line_item_id
1453: and pd.based_on_line_item = l_dummy_based_on);
1454:
1453: and pd.based_on_line_item = l_dummy_based_on);
1454:
1455: delete from igw_budget_personnel_details
1456: where line_item_id IN (select line_item_id
1457: from igw_budget_details pd
1458: where pd.based_on_line_item = l_dummy_based_on);
1459:
1460: delete from igw_budget_details_cal_amts
1461: where line_item_id IN (select line_item_id
1456: where line_item_id IN (select line_item_id
1457: from igw_budget_details pd
1458: where pd.based_on_line_item = l_dummy_based_on);
1459:
1460: delete from igw_budget_details_cal_amts
1461: where line_item_id IN (select line_item_id
1462: from igw_budget_details pd
1463: where pd.based_on_line_item = l_dummy_based_on);
1464:
1458: where pd.based_on_line_item = l_dummy_based_on);
1459:
1460: delete from igw_budget_details_cal_amts
1461: where line_item_id IN (select line_item_id
1462: from igw_budget_details pd
1463: where pd.based_on_line_item = l_dummy_based_on);
1464:
1465: delete from igw_budget_details
1466: where based_on_line_item = l_dummy_based_on;
1461: where line_item_id IN (select line_item_id
1462: from igw_budget_details pd
1463: where pd.based_on_line_item = l_dummy_based_on);
1464:
1465: delete from igw_budget_details
1466: where based_on_line_item = l_dummy_based_on;
1467: end if;
1468:
1469: for i in (p_budget_period_id+1) .. (p_budget_period_id+l_no_of_periods)
1503: ,rec_budget_details.budget_justification
1504: ,rec_budget_details.location_code);
1505:
1506: if l_rate_class_id_oh is not null and l_rate_type_id_oh is not null then
1507: insert into igw_budget_details_cal_amts (
1508: proposal_id
1509: ,version_id
1510: ,budget_period_id
1511: ,line_item_id
1522: values (
1523: rec_budget_details.proposal_id
1524: ,rec_budget_details.version_id
1525: ,i
1526: ,igw_budget_details_s.currval
1527: ,l_rate_class_id_oh
1528: ,l_rate_type_id_oh
1529: ,'Y'
1530: ,0
1536: ,fnd_global.login_id);
1537: end if;
1538:
1539: if l_rate_class_id_eb is not null and l_rate_type_id_eb is not null then
1540: insert into igw_budget_details_cal_amts (
1541: proposal_id
1542: ,version_id
1543: ,budget_period_id
1544: ,line_item_id
1555: values (
1556: rec_budget_details.proposal_id
1557: ,rec_budget_details.version_id
1558: ,i
1559: ,igw_budget_details_s.currval
1560: ,l_rate_class_id_eb
1561: ,l_rate_type_id_eb
1562: ,'Y'
1563: ,0
1717: igw_budget_personnel_s.nextval
1718: ,rec_budget_details.proposal_id
1719: ,rec_budget_details.version_id
1720: ,i
1721: ,igw_budget_details_s.currval
1722: ,rec_budget_personnel.person_id
1723: ,rec_budget_personnel.party_id
1724: ,l_personnel_start_date
1725: ,l_personnel_end_date
1760:
1761: END LOOP; --for rec_personnel_budget
1762: end if;
1763:
1764: select igw_budget_details_s.currval
1765: into l_line_item_seq
1766: from dual;
1767:
1768: update igw_budget_details_cal_amts pdc
1764: select igw_budget_details_s.currval
1765: into l_line_item_seq
1766: from dual;
1767:
1768: update igw_budget_details_cal_amts pdc
1769: set pdc.calculated_cost =
1770: (select nvl(sum(ppc.calculated_cost),0)
1771: from igw_budget_personnel_cal_amts ppc
1772: , igw_budget_personnel_details ppd
1769: set pdc.calculated_cost =
1770: (select nvl(sum(ppc.calculated_cost),0)
1771: from igw_budget_personnel_cal_amts ppc
1772: , igw_budget_personnel_details ppd
1773: , igw_budget_details pd
1774: where pd.line_item_id = ppd.line_item_id
1775: and ppd.budget_personnel_detail_id =ppc.budget_personnel_detail_id
1776: and pd.proposal_id = p_proposal_id
1777: and pd.version_id = p_version_id
1782: , pdc.calculated_cost_sharing =
1783: (select nvl(sum(ppc.calculated_cost_sharing),0)
1784: from igw_budget_personnel_cal_amts ppc
1785: , igw_budget_personnel_details ppd
1786: , igw_budget_details pd
1787: where pd.line_item_id = ppd.line_item_id
1788: and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
1789: and pd.proposal_id = p_proposal_id
1790: and pd.version_id = p_version_id
1794: and ppc.rate_type_id = pdc.rate_type_id)
1795: where pdc.line_item_id = l_line_item_seq;
1796:
1797:
1798: update igw_budget_details pdb
1799: set line_item_cost =
1800: (select nvl(sum(salary_requested),0)
1801: from igw_budget_personnel_details ppd
1802: where ppd.line_item_id = l_line_item_seq)
1819: , nvl(sum(underrecovery_amount),0)
1820: into l_direct_cost1
1821: , l_cost_share1
1822: , l_underrecovery
1823: from igw_budget_details
1824: where proposal_id = p_proposal_id
1825: and version_id = p_version_id
1826: and budget_period_id = i;
1827:
1828: select nvl(sum(calculated_cost_sharing),0)
1829: , nvl(sum(calculated_cost),0)
1830: into l_cost_share2
1831: , l_indirect_cost
1832: from igw_budget_details_cal_amts pc
1833: where proposal_id = p_proposal_id
1834: and version_id = p_version_id
1835: and budget_period_id = i
1836: and pc.rate_class_id = ( select pr.rate_class_id
1840: select nvl(sum(calculated_cost_sharing),0)
1841: , nvl(sum(calculated_cost),0)
1842: into l_cost_share3
1843: , l_direct_cost2
1844: from igw_budget_details_cal_amts pc
1845: where proposal_id = p_proposal_id
1846: and version_id = p_version_id
1847: and budget_period_id = i
1848: and pc.rate_class_id = ( select pr.rate_class_id
1970:
1971: begin
1972: select calculated_cost
1973: into l_calculated_cost
1974: from igw_budget_details_cal_amts
1975: where line_item_id = p_line_item_id;
1976: exception
1977: when no_data_found then null;
1978: end;
2056: , version_id
2057: , budget_period_id
2058: , expenditure_category_flag
2059: , expenditure_type
2060: from igw_budget_details
2061: where line_item_id = p_line_item_id;
2062: Begin
2063: x_return_status := 'S';
2064:
2166: RAISE FND_API.G_EXC_ERROR;
2167: End if;
2168:
2169:
2170: update igw_budget_details
2171: set line_item_cost = nvl(l_new_line_item_cost, line_item_cost)
2172: where line_item_id = p_line_item_id;
2173:
2174: update igw_budget_details_cal_amts
2170: update igw_budget_details
2171: set line_item_cost = nvl(l_new_line_item_cost, line_item_cost)
2172: where line_item_id = p_line_item_id;
2173:
2174: update igw_budget_details_cal_amts
2175: set calculated_cost = nvl(l_new_calculated_cost, calculated_cost)
2176: where line_item_id = p_line_item_id;
2177:
2178:
2216: l_msg_index_out NUMBER;
2217:
2218: cursor c_line_item_cost is
2219: select line_item_cost
2220: from igw_budget_details
2221: where line_item_id = p_line_item_id;
2222:
2223: Begin
2224: x_return_status := 'S';