[Home] [Help]
149: CLOSE c_status_code;
150:
151: IF l_status_code IS NULL THEN
152: x_return_status := fnd_api.g_ret_sts_error;
153: Ozf_utility_pvt.error_message('OZF_FUND_BAD_USER_STATUS');
154: END IF;
155:
156: x_status_code := l_status_code;
157: END handle_fund_status;
187: CLOSE c_user_status_id;
188:
189: IF l_user_status_id IS NULL THEN
190: x_return_status := fnd_api.g_ret_sts_error;
191: Ozf_utility_pvt.error_message('OZF_FUND_BAD_USER_STATUS');
192: END IF;
193:
194: x_user_status_id := l_user_status_id;
195: END get_user_status;
242:
243: x_code := SUBSTRB(l_par_number || l_count, 1, 30);
244:
245: --asylvia Fixed bug 5073532
246: WHILE ozf_utility_pvt.check_uniqueness('ozf_funds_all_b'
247: ,'fund_number = ''' || x_code || '''') =
248: fnd_api.g_false LOOP
249: l_count := l_count + 1 ;
250: x_code :=SUBSTRB(l_par_number || l_count, 1, 30);
292: END IF;
293: END IF;
294:
295: IF G_DEBUG THEN
296: ozf_utility_pvt.debug_message('p_event_type is :'||p_event_type || ' Fund Id is :'||p_object_id );
297: END IF;
298:
299: wf_event.AddParameterToList(p_name => 'P_FUND_ID',
300: p_value => p_object_id,
300: p_value => p_object_id,
301: p_parameterlist => l_parameter_list);
302:
303: IF G_DEBUG THEN
304: ozf_utility_pvt.debug_message('Item Key is :'||l_item_key);
305: END IF;
306:
307: wf_event.raise( p_event_name =>l_event_name,
308: p_event_key => l_item_key,
311:
312: EXCEPTION
313: WHEN OTHERS THEN
314: RAISE Fnd_Api.g_exc_error;
315: ozf_utility_pvt.debug_message('Exception in raising business event');
316: END;
317:
318:
319: ---------------------------------------------------------------------
399: BEGIN
400: --------------------- initialize -----------------------
401: SAVEPOINT create_fund;
402: IF G_DEBUG THEN
403: ozf_utility_pvt.debug_message(l_full_name || ': start');
404: END IF;
405: x_return_status := fnd_api.g_ret_sts_success;
406:
407: IF fnd_api.to_boolean(p_init_msg_list) THEN
468: END IF;
469:
470: ----------------------- validate -----------------------
471: IF G_DEBUG THEN
472: ozf_utility_pvt.debug_message(l_full_name || ': validate');
473: END IF;
474: validate_fund(
475: p_api_version => l_api_version
476: ,p_init_msg_list => p_init_msg_list
488: END IF;
489:
490: -------------------------- insert --------------------------
491: IF G_DEBUG THEN
492: ozf_utility_pvt.debug_message(l_full_name || ': insert');
493: END IF;
494:
495: IF l_fund_rec.fund_id IS NULL THEN
496: LOOP
505: END IF;
506:
507:
508: IF G_DEBUG THEN
509: ozf_utility_pvt.debug_message(l_full_name || ': get_category');
510: END IF;
511:
512: -- kdass 09-NOV-05 Bug 4618523
513: /*
548:
549: -- Calculate rollup amount columns, added by feliu 02/08/02
550: IF l_fund_rec.original_budget IS NOT NULL
551: AND l_fund_rec.original_budget <> fnd_api.g_miss_num THEN
552: Ozf_utility_pvt.convert_currency(
553: x_return_status => l_return_status
554: ,p_from_currency => l_fund_rec.currency_code_tc
555: ,p_to_currency => g_universal_currency
556: ,p_from_amount => l_fund_rec.original_budget
873:
874: ------------------------- finish -------------------------------
875: x_fund_id := l_fund_rec.fund_id;
876: IF G_DEBUG THEN
877: ozf_utility_pvt.debug_message(l_full_name || ': insert object attribute');
878: END IF;
879:
880: -- If the fund_status is 'ACTIVE', we need to create a record in th ACT_BUDGET table for the holdback amount
881: -- fixed bug for validation level and p_commit
917: --- Insert a record in OZF_MC_TRANSACTIONS_ALL IN functional currency
918: -- so that we have the functional currency amounts
919: -- The exchange_rate_type is picked up by the MC_TRAnSACTIONS API from proile
920: IF G_DEBUG THEN
921: ozf_utility_pvt.debug_message(l_full_name || ': insert Functional currency record');
922: END IF;
923: -- Populate the record variable
924: l_mc_transaction_rec.source_object_name := 'FUND';
925: l_mc_transaction_rec.source_object_id := l_fund_rec.fund_id;
965: -- changing status from 'DRAFT or 'REJECTED' to 'ACTIVE or ON_HOLD is
966: -- equivalent to submitting for approval.
967: -- Approval submission child fund
968: IF G_DEBUG THEN
969: ozf_utility_pvt.debug_message(l_full_name || 'owner' || p_fund_rec.owner);
970: END IF;
971:
972: ozf_fund_request_apr_pvt.create_fund_request(
973: p_commit => fnd_api.g_false
1000: END IF ;
1001: --added by mpande
1002: -- insert a access for the owner
1003: IF G_DEBUG THEN
1004: ozf_utility_pvt.debug_message(l_full_name || ': insert access owner');
1005: END IF;
1006:
1007: IF l_fund_rec.fund_id IS NOT NULL THEN
1008:
1033: --added by mpande
1034: -- if parent id is not null during creation , a row is created in the ams_act_access to give access
1035: -- to the owner of the parent fund to this child
1036: IF G_DEBUG THEN
1037: ozf_utility_pvt.debug_message(l_full_name || ': insert access parent');
1038: END IF;
1039:
1040: IF l_fund_rec.parent_fund_id IS NOT NULL THEN
1041: -- added updating rollup columns by feliu.
1104:
1105: END IF;
1106: -- mpadne 10/14/2002 for 11.5.9
1107: IF G_DEBUG THEN
1108: OZF_UTILITY_PVT.DEBUG_MESSAGE('ACCRUAL BASIS ='||L_FUND_REC.accrual_basis);
1109: END IF;
1110: IF p_fund_rec.fund_type = 'FULLY_ACCRUED' AND p_fund_rec.plan_id is null THEN
1111: ozf_fundrules_pvt.process_accrual (
1112: p_fund_rec => l_fund_rec
1133: p_encoded => fnd_api.g_false
1134: ,p_count => x_msg_count
1135: ,p_data => x_msg_data);
1136: IF G_DEBUG THEN
1137: ozf_utility_pvt.debug_message(l_full_name || ': end');
1138: END IF;
1139:
1140: EXCEPTION
1141: WHEN fnd_api.g_exc_error THEN
1191: BEGIN
1192: --------------------- initialize -----------------------
1193: SAVEPOINT delete_fund;
1194: IF G_DEBUG THEN
1195: ozf_utility_pvt.debug_message(l_full_name || ': start');
1196: END IF;
1197:
1198: IF fnd_api.to_boolean(p_init_msg_list) THEN
1199: fnd_msg_pub.initialize;
1205:
1206: x_return_status := fnd_api.g_ret_sts_success;
1207: ------------------------ delete ------------------------
1208: IF G_DEBUG THEN
1209: ozf_utility_pvt.debug_message(l_full_name || ': delete');
1210: END IF;
1211:
1212: DELETE
1213: FROM ozf_funds_all_b
1245: p_encoded => fnd_api.g_false
1246: ,p_count => x_msg_count
1247: ,p_data => x_msg_data);
1248: IF G_DEBUG THEN
1249: ozf_utility_pvt.debug_message(l_full_name || ': end');
1250: END IF;
1251: EXCEPTION
1252: WHEN fnd_api.g_exc_error THEN
1253: ROLLBACK TO delete_fund;
1317: FOR UPDATE OF fund_id NOWAIT;
1318: BEGIN
1319: -------------------- initialize ------------------------
1320: IF G_DEBUG THEN
1321: ozf_utility_pvt.debug_message(l_full_name || ': start');
1322: END IF;
1323:
1324: IF fnd_api.to_boolean(p_init_msg_list) THEN
1325: fnd_msg_pub.initialize;
1331:
1332: x_return_status := fnd_api.g_ret_sts_success;
1333: ------------------------ lock -------------------------
1334: IF G_DEBUG THEN
1335: ozf_utility_pvt.debug_message(l_full_name || ': lock');
1336: END IF;
1337: OPEN c_fund_b;
1338: FETCH c_fund_b INTO l_fund_id;
1339:
1356: p_encoded => fnd_api.g_false
1357: ,p_count => x_msg_count
1358: ,p_data => x_msg_data);
1359: IF G_DEBUG THEN
1360: ozf_utility_pvt.debug_message(l_full_name || ': end');
1361: END IF;
1362: EXCEPTION
1363: WHEN ozf_utility_pvt.resource_locked THEN
1364: x_return_status := fnd_api.g_ret_sts_error;
1359: IF G_DEBUG THEN
1360: ozf_utility_pvt.debug_message(l_full_name || ': end');
1361: END IF;
1362: EXCEPTION
1363: WHEN ozf_utility_pvt.resource_locked THEN
1364: x_return_status := fnd_api.g_ret_sts_error;
1365:
1366: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1367: fnd_message.set_name('OZF', 'OZF_API_RESOURCE_LOCKED');
1610: BEGIN
1611: -------------------- initialize -------------------------
1612: SAVEPOINT update_fund;
1613: IF G_DEBUG THEN
1614: ozf_utility_pvt.debug_message(l_full_name || ': start');
1615: END IF;
1616:
1617: IF fnd_api.to_boolean(p_init_msg_list) THEN
1618: fnd_msg_pub.initialize;
1641:
1642: l_fund_rec.status_code := l_status_code;
1643: ELSIF p_fund_rec.status_code <> fnd_api.g_miss_char THEN
1644: IF G_DEBUG THEN
1645: ozf_utility_pvt.debug_message(l_full_name || 'debug' || p_fund_rec.status_code);
1646: END IF;
1647: get_user_status(
1648: p_status_code => p_fund_rec.status_code
1649: ,x_user_status_id => l_fund_rec.user_status_id
1656: END IF;
1657: --- this we need because there could be some records in the database with no user status
1658: ELSE
1659: IF G_DEBUG THEN
1660: ozf_utility_pvt.debug_message(l_full_name || 'in else ');
1661: END IF;
1662:
1663: IF l_fund_rec.user_status_id IS NOT NULL THEN
1664: handle_fund_status(
1698: FETCH c_old_status INTO l_old_status, l_old_parent_fund_id,l_old_user_status_id;
1699: CLOSE c_old_status;
1700: ----------------------- validate ----------------------
1701: IF G_DEBUG THEN
1702: ozf_utility_pvt.debug_message(l_full_name || ': validate');
1703: END IF;
1704: -- replace g_miss_char/num/date with current column values
1705: --added by mpande 27th JULY-2000
1706: ---if the fund is active and the currency_code_tc passed is different than the fund_currency_code
1713: CLOSE c_old_curr;
1714: ----dbms_output.put_line ('OLD CURR = '||l_old_curr);
1715: ----dbms_output.put_line ('PASSED CURR = '||p_fund_rec.currency_code_tc);
1716: IF G_DEBUG THEN
1717: ozf_utility_pvt.debug_message(l_full_name || ': convert currrency');
1718: END IF;
1719: -- changed the status call here to check for the changed status
1720: IF l_fund_rec.status_code <> 'DRAFT' AND l_old_status <> 'DRAFT'
1721: AND l_old_curr <> l_fund_rec.currency_code_tc THEN
1724:
1725: --nirprasa, added for bug 7425189
1726: IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1727: AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1728: Ozf_utility_pvt.convert_currency(
1729: x_return_status => l_return_status
1730: ,p_from_currency => p_fund_rec.currency_code_tc
1731: ,p_to_currency => l_old_curr
1732: ,p_conv_date => p_fund_rec.exchange_rate_date
1733: ,p_from_amount => p_fund_rec.original_budget
1734: ,x_to_amount => l_fund_rec.original_budget
1735: ,x_rate => l_rate);
1736: ELSE
1737: Ozf_utility_pvt.convert_currency(
1738: x_return_status => l_return_status
1739: ,p_from_currency => p_fund_rec.currency_code_tc
1740: ,p_to_currency => l_old_curr
1741: ,p_from_amount => p_fund_rec.original_budget
1758: AND p_fund_rec.transfered_in_amt <> fnd_api.g_miss_num THEN
1759: --nirprasa, added for bug 7425189
1760: IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1761: AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1762: Ozf_utility_pvt.convert_currency(
1763: x_return_status => l_return_status
1764: ,p_from_currency => p_fund_rec.currency_code_tc
1765: ,p_to_currency => l_old_curr
1766: ,p_conv_date => p_fund_rec.exchange_rate_date
1767: ,p_from_amount => p_fund_rec.transfered_in_amt
1768: ,x_to_amount => l_fund_rec.transfered_in_amt
1769: ,x_rate => l_rate);
1770: ELSE
1771: Ozf_utility_pvt.convert_currency(
1772: x_return_status => l_return_status
1773: ,p_from_currency => p_fund_rec.currency_code_tc
1774: ,p_to_currency => l_old_curr
1775: ,p_from_amount => p_fund_rec.transfered_in_amt
1791:
1792: --nirprasa, added for bug 7425189
1793: IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1794: AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1795: Ozf_utility_pvt.convert_currency(
1796: x_return_status => l_return_status
1797: ,p_from_currency => p_fund_rec.currency_code_tc
1798: ,p_to_currency => l_old_curr
1799: ,p_conv_date => p_fund_rec.exchange_rate_date
1800: ,p_from_amount => p_fund_rec.transfered_in_amt
1801: ,x_to_amount => l_fund_rec.transfered_in_amt
1802: ,x_rate => l_rate);
1803: ELSE
1804: Ozf_utility_pvt.convert_currency(
1805: x_return_status => l_return_status
1806: ,p_from_currency => p_fund_rec.currency_code_tc
1807: ,p_to_currency => l_old_curr
1808: ,p_from_amount => p_fund_rec.transfered_in_amt
1824:
1825: --nirprasa, added for bug 7425189
1826: IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1827: AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1828: Ozf_utility_pvt.convert_currency(
1829: x_return_status => l_return_status
1830: ,p_from_currency => p_fund_rec.currency_code_tc
1831: ,p_to_currency => l_old_curr
1832: ,p_conv_date => p_fund_rec.exchange_rate_date
1833: ,p_from_amount => p_fund_rec.planned_amt
1834: ,x_to_amount => l_fund_rec.planned_amt
1835: ,x_rate => l_rate);
1836: ELSE
1837: Ozf_utility_pvt.convert_currency(
1838: x_return_status => l_return_status
1839: ,p_from_currency => p_fund_rec.currency_code_tc
1840: ,p_to_currency => l_old_curr
1841: ,p_from_amount => p_fund_rec.planned_amt
1859:
1860: --nirprasa, added for bug 7425189
1861: IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1862: AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1863: Ozf_utility_pvt.convert_currency(
1864: x_return_status => l_return_status
1865: ,p_from_currency => p_fund_rec.currency_code_tc
1866: ,p_to_currency => l_old_curr
1867: ,p_conv_date => p_fund_rec.exchange_rate_date
1868: ,p_from_amount => p_fund_rec.utilized_amt
1869: ,x_to_amount => l_fund_rec.utilized_amt
1870: ,x_rate => l_rate);
1871: ELSE
1872: Ozf_utility_pvt.convert_currency(
1873: x_return_status => l_return_status
1874: ,p_from_currency => p_fund_rec.currency_code_tc
1875: ,p_to_currency => l_old_curr
1876: ,p_from_amount => p_fund_rec.utilized_amt
1892:
1893: --nirprasa, added for bug 7425189
1894: IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1895: AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1896: Ozf_utility_pvt.convert_currency(
1897: x_return_status => l_return_status
1898: ,p_from_currency => p_fund_rec.currency_code_tc
1899: ,p_to_currency => l_old_curr
1900: ,p_conv_date => p_fund_rec.exchange_rate_date
1901: ,p_from_amount => p_fund_rec.earned_amt
1902: ,x_to_amount => l_fund_rec.earned_amt
1903: ,x_rate => l_rate);
1904: ELSE
1905: Ozf_utility_pvt.convert_currency(
1906: x_return_status => l_return_status
1907: ,p_from_currency => p_fund_rec.currency_code_tc
1908: ,p_to_currency => l_old_curr
1909: ,p_from_amount => p_fund_rec.earned_amt
1925:
1926: --nirprasa, added for bug 7425189
1927: IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1928: AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1929: Ozf_utility_pvt.convert_currency(
1930: x_return_status => l_return_status
1931: ,p_from_currency => p_fund_rec.currency_code_tc
1932: ,p_to_currency => l_old_curr
1933: ,p_conv_date => p_fund_rec.exchange_rate_date
1934: ,p_from_amount => p_fund_rec.committed_amt
1935: ,x_to_amount => l_fund_rec.committed_amt
1936: ,x_rate => l_rate);
1937: ELSE
1938: Ozf_utility_pvt.convert_currency(
1939: x_return_status => l_return_status
1940: ,p_from_currency => p_fund_rec.currency_code_tc
1941: ,p_to_currency => l_old_curr
1942: ,p_from_amount => p_fund_rec.committed_amt
1958:
1959: --nirprasa, added for bug 7425189
1960: IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
1961: AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
1962: Ozf_utility_pvt.convert_currency(
1963: x_return_status => l_return_status
1964: ,p_from_currency => p_fund_rec.currency_code_tc
1965: ,p_to_currency => l_old_curr
1966: ,p_conv_date => p_fund_rec.exchange_rate_date
1967: ,p_from_amount => p_fund_rec.paid_amt
1968: ,x_to_amount => l_fund_rec.paid_amt
1969: ,x_rate => l_rate);
1970: ELSE
1971: Ozf_utility_pvt.convert_currency(
1972: x_return_status => l_return_status
1973: ,p_from_currency => p_fund_rec.currency_code_tc
1974: ,p_to_currency => l_old_curr
1975: ,p_from_amount => p_fund_rec.paid_amt
2044: END IF;
2045:
2046: -- inter-entity level
2047: IF G_DEBUG THEN
2048: ozf_utility_pvt.debug_message(l_full_name || ': check inter-entity');
2049: END IF;
2050:
2051: IF p_validation_level >= jtf_plsql_api.g_valid_level_inter_entity THEN
2052: check_fund_inter_entity(
2063: END IF;
2064:
2065: -------------------------- update --------------------
2066: IF G_DEBUG THEN
2067: ozf_utility_pvt.debug_message(l_full_name || ': update');
2068:
2069: ozf_utility_pvt.debug_message(l_full_name || ': get_category');
2070: END IF;
2071:
2065: -------------------------- update --------------------
2066: IF G_DEBUG THEN
2067: ozf_utility_pvt.debug_message(l_full_name || ': update');
2068:
2069: ozf_utility_pvt.debug_message(l_full_name || ': get_category');
2070: END IF;
2071:
2072: -- kdass 09-NOV-05 Bug 4618523
2073: /*
2089: END IF;
2090: */
2091:
2092: IF G_DEBUG THEN
2093: ozf_utility_pvt.debug_message(l_full_name || 'before update status' ||l_fund_rec.original_budget);
2094: END IF;
2095: -- perform all status related updation and if it is successful then only update the fund record
2096: -- Update fund could be called by other APIS in active status to update earned amt or committed amt .
2097: -- We do allow them to update the record then
2182: THEN
2183: --nirprasa, added for bug 7425189
2184: IF p_fund_rec.description IN (l_fund_reconc_msg,l_act_bud_cst_msg)
2185: AND p_fund_rec.exchange_rate_date IS NOT NULL THEN
2186: Ozf_utility_pvt.convert_currency(
2187: x_return_status => l_return_status
2188: ,p_from_currency => l_fund_rec.currency_code_tc
2189: ,p_to_currency => g_universal_currency
2190: ,p_conv_date => p_fund_rec.exchange_rate_date
2191: ,p_from_amount => l_fund_rec.original_budget
2192: ,x_to_amount => l_original_budget
2193: ,x_rate => l_rate);
2194: ELSE
2195: Ozf_utility_pvt.convert_currency(
2196: x_return_status => l_return_status
2197: ,p_from_currency => l_fund_rec.currency_code_tc
2198: ,p_to_currency => g_universal_currency
2199: ,p_from_amount => l_fund_rec.original_budget
2204:
2205: --l_rollup_original_budget := l_original_budget - NVL(l_or_original_budget,0);
2206: --l_fund_rec.rollup_original_budget := l_original_budget;
2207:
2208: l_fund_rec.rollup_original_budget := ozf_utility_pvt.CurrRound((l_fund_rec.original_budget - NVL(l_old_original_budget,0)) * l_rate
2209: ,g_universal_currency) + NVL(l_or_original_budget,0);
2210: l_rollup_original_budget := l_fund_rec.rollup_original_budget - NVL(l_or_original_budget,0);
2211:
2212:
2221: IF l_fund_rec.transfered_in_amt IS NOT NULL
2222: AND l_rate is NOT NULL
2223: AND l_fund_rec.transfered_in_amt <> NVL(l_old_transfered_in_amt,0) THEN
2224:
2225: l_fund_rec.rollup_transfered_in_amt := ozf_utility_pvt.CurrRound((l_fund_rec.transfered_in_amt - NVL(l_old_transfered_in_amt,0)) * l_rate
2226: ,g_universal_currency) + NVL(l_or_transfered_in_amt,0);
2227: l_rollup_transfered_in_amt := l_fund_rec.rollup_transfered_in_amt - NVL(l_or_transfered_in_amt,0);
2228:
2229: END IF;
2231: IF l_fund_rec.transfered_out_amt IS NOT NULL
2232: AND l_rate is NOT NULL
2233: AND l_fund_rec.transfered_out_amt <> NVL(l_old_transfered_out_amt,0) THEN
2234:
2235: l_fund_rec.rollup_transfered_out_amt := ozf_utility_pvt.CurrRound((l_fund_rec.transfered_out_amt-NVL(l_old_transfered_out_amt,0)) * l_rate
2236: ,g_universal_currency) + NVL(l_or_transfered_out_amt,0);
2237: l_rollup_transfered_out_amt := l_fund_rec.rollup_transfered_out_amt - NVL(l_or_transfered_out_amt,0);
2238:
2239: END IF;
2243: --Bug Fix 4087106, Rollup holdback amt not updated when manually updated.
2244: IF l_rate is NOT NULL
2245: AND NVL(l_fund_rec.holdback_amt,0) <> NVL(l_old_holdback_amt,0) THEN
2246:
2247: l_fund_rec.rollup_holdback_amt := ozf_utility_pvt.CurrRound((NVL(l_fund_rec.holdback_amt,0)-NVL(l_old_holdback_amt,0)) * l_rate
2248: ,g_universal_currency)+ NVL(l_or_holdback_amt,0);
2249: l_rollup_holdback_amt := l_fund_rec.rollup_holdback_amt - NVL(l_or_holdback_amt,0);
2250:
2251: END IF;
2254: IF l_fund_rec.planned_amt IS NOT NULL
2255: AND l_rate is NOT NULL
2256: AND l_fund_rec.planned_amt <> NVL(l_old_planned_amt,0) THEN
2257:
2258: l_fund_rec.rollup_planned_amt := ozf_utility_pvt.CurrRound((l_fund_rec.planned_amt-NVL(l_old_planned_amt,0)) * l_rate
2259: ,g_universal_currency)+ NVL(l_or_planned_amt,0);
2260: l_rollup_planned_amt := l_fund_rec.rollup_planned_amt - NVL(l_or_planned_amt,0);
2261:
2262: END IF;
2264: IF l_fund_rec.committed_amt IS NOT NULL
2265: AND l_rate is NOT NULL
2266: AND l_fund_rec.committed_amt <> NVL(l_old_committed_amt,0) THEN
2267:
2268: l_fund_rec.rollup_committed_amt := ozf_utility_pvt.CurrRound((l_fund_rec.committed_amt-NVL(l_old_committed_amt,0)) * l_rate
2269: ,g_universal_currency) + NVL(l_or_committed_amt,0);
2270: l_rollup_committed_amt :=l_fund_rec.rollup_committed_amt - NVL(l_or_committed_amt,0);
2271:
2272: END IF;
2274: IF l_fund_rec.utilized_amt IS NOT NULL
2275: AND l_rate is NOT NULL
2276: AND l_fund_rec.utilized_amt <> NVL(l_old_utilized_amt,0) THEN
2277:
2278: l_fund_rec.rollup_utilized_amt := ozf_utility_pvt.CurrRound((l_fund_rec.utilized_amt-NVL(l_old_utilized_amt,0)) * l_rate
2279: ,g_universal_currency) + NVL(l_or_utilized_amt,0);
2280: l_rollup_utilized_amt := l_fund_rec.rollup_utilized_amt - NVL(l_or_utilized_amt,0);
2281:
2282: END IF;
2284: IF l_fund_rec.earned_amt IS NOT NULL
2285: AND l_rate is NOT NULL
2286: AND l_fund_rec.earned_amt <> NVL(l_old_earned_amt,0) THEN
2287:
2288: l_fund_rec.rollup_earned_amt := ozf_utility_pvt.CurrRound((l_fund_rec.earned_amt-NVL(l_old_earned_amt,0)) * l_rate
2289: ,g_universal_currency) + NVL(l_or_earned_amt,0);
2290: l_rollup_earned_amt := l_fund_rec.rollup_earned_amt - NVL(l_or_earned_amt,0);
2291:
2292: END IF;
2295: IF l_fund_rec.paid_amt IS NOT NULL
2296: AND l_rate is NOT NULL
2297: AND l_fund_rec.paid_amt <> NVL(l_old_paid_amt,0) THEN
2298:
2299: l_fund_rec.rollup_paid_amt := ozf_utility_pvt.CurrRound((l_fund_rec.paid_amt-NVL(l_old_paid_amt,0)) * l_rate
2300: ,g_universal_currency) +NVL(l_or_paid_amt,0);
2301: l_rollup_paid_amt := l_fund_rec.rollup_paid_amt - NVL(l_or_paid_amt,0);
2302:
2303: END IF;
2305: IF l_fund_rec.recal_committed IS NOT NULL
2306: AND l_rate is NOT NULL
2307: AND l_fund_rec.recal_committed <> NVL(l_old_recal_committed,0) THEN
2308:
2309: l_fund_rec.rollup_recal_committed := ozf_utility_pvt.CurrRound((l_fund_rec.recal_committed-NVL(l_old_recal_committed,0)) * l_rate
2310: ,g_universal_currency) +NVL(l_or_recal_committed,0);
2311: l_rollup_recal_committed :=l_fund_rec.rollup_recal_committed - NVL(l_or_recal_committed,0);
2312:
2313: END IF;
2647: ,p_approval_for_id => p_fund_rec.fund_id
2648: /* yzhao: Jan 16 2005 fix bug 4943323(4912954) pass in correct requester id
2649: ,p_requester_id => l_fund_rec.owner
2650: */
2651: ,p_requester_id => ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id)
2652: ,p_requested_amount => l_child_request_amt
2653: ,p_approval_fm => 'FUND'
2654: ,p_approval_fm_id => l_fund_rec.parent_fund_id
2655: ,p_transfer_type => 'REQUEST'
2671: END IF;
2672: END IF;
2673: -- call when it is submitting for approval
2674: IF l_submit_budget_approval = FND_API.g_true THEN
2675: l_reject_status_id :=ozf_utility_pvt.get_default_user_status(
2676: l_status_type
2677: ,'REJECTED');
2678:
2679: AMS_GEN_APPROVAL_PVT.StartProcess
2686: ,p_reject_stat_id =>l_reject_status_id
2687: /* yzhao: Jan 16 2005 fix bug 4943323(4912954) pass in correct requester id
2688: ,p_requester_userid =>l_fund_rec.owner
2689: */
2690: ,p_requester_userid =>ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id)
2691: ,p_notes_from_requester =>l_fund_rec.description
2692: ,p_workflowprocess => l_workflow_process
2693: ,p_item_type => l_item_type);
2694:
2711: -- if owner is changing update acesss
2712: IF p_fund_rec.owner <> fnd_api.g_miss_num
2713: AND l_owner <> p_fund_rec.owner THEN
2714: IF G_DEBUG THEN
2715: ozf_utility_pvt.debug_message(l_full_name || ': insert access owner');
2716: END IF;
2717: /* commented by feliu on 05/13/2003 to fix bug 2969498;
2718: OPEN c_access(p_fund_rec.fund_id);
2719: FETCH c_access INTO l_act_access_id, l_acc_obj_ver_num;
2828: -- from proile
2829: -- update the transaction table on all cases.
2830:
2831: IF G_DEBUG THEN
2832: ozf_utility_pvt.debug_message(l_full_name ||': insert FC record' ||l_fund_rec.currency_code_tc);
2833: END IF;
2834: ozf_mc_transactions_pvt.init_transaction_rec(x_mc_transactions_rec => l_mc_transaction_rec);
2835: -- Populate the record variable
2836: l_mc_transaction_rec.mc_record_id := l_mc_record_id;
2887: p_encoded => fnd_api.g_false
2888: ,p_count => x_msg_count
2889: ,p_data => x_msg_data);
2890: IF G_DEBUG THEN
2891: ozf_utility_pvt.debug_message(l_full_name || ': end');
2892: END IF;
2893: EXCEPTION
2894: WHEN fnd_api.g_exc_error THEN
2895: ROLLBACK TO update_fund;
2944: l_return_status VARCHAR2(1);
2945: BEGIN
2946: ----------------------- initialize --------------------
2947: IF G_DEBUG THEN
2948: ozf_utility_pvt.debug_message(l_full_name || ': start');
2949: END IF;
2950:
2951: IF fnd_api.to_boolean(p_init_msg_list) THEN
2952: fnd_msg_pub.initialize;
2958:
2959: x_return_status := fnd_api.g_ret_sts_success;
2960: ---------------------- validate ------------------------
2961: IF G_DEBUG THEN
2962: ozf_utility_pvt.debug_message(l_full_name || ': check items');
2963: END IF;
2964:
2965: IF p_validation_level >= jtf_plsql_api.g_valid_level_item THEN
2966: ----dbms_output.put_line ('Calling Check_Fund_Items from validate');
2977: END IF;
2978: END IF;
2979:
2980: IF G_DEBUG THEN
2981: ozf_utility_pvt.debug_message(l_full_name || ': check record');
2982: END IF;
2983:
2984: IF p_validation_level >= jtf_plsql_api.g_valid_level_record THEN
2985: check_fund_record(
2996: END IF;
2997:
2998: -- added on 01/20/2001 for all inter entity validations Mumu Pande
2999: IF G_DEBUG THEN
3000: ozf_utility_pvt.debug_message(l_full_name || ': check inter-entity');
3001: END IF;
3002:
3003: IF p_validation_level >= jtf_plsql_api.g_valid_level_inter_entity THEN
3004: check_fund_inter_entity(
3019: p_encoded => fnd_api.g_false
3020: ,p_count => x_msg_count
3021: ,p_data => x_msg_data);
3022: IF G_DEBUG THEN
3023: ozf_utility_pvt.debug_message(l_full_name || ': end');
3024: END IF;
3025: EXCEPTION
3026: WHEN fnd_api.g_exc_error THEN
3027: x_return_status := fnd_api.g_ret_sts_error;
3219: -- For Create_Fund, when fund_id is passed in, we need to
3220: -- check if this fund_id is unique.
3221: IF p_validation_mode = jtf_plsql_api.g_create
3222: AND p_fund_rec.fund_id IS NOT NULL THEN
3223: IF ozf_utility_pvt.check_uniqueness(
3224: 'ozf_funds_all_vl'
3225: ,'fund_id = ' || p_fund_rec.fund_id) =
3226: fnd_api.g_false THEN
3227: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3241: -- Check if fund_number is unique. Need to handle create and
3242: -- update differently.
3243: IF p_validation_mode = jtf_plsql_api.g_create THEN
3244: /*l_valid_flag :=
3245: ozf_utility_pvt.check_uniqueness(
3246: 'ozf_funds_all_vl'
3247: ,'fund_number = ''' || p_fund_rec.fund_number || '''');*/
3248: OPEN c_prog_fund_number_create;
3249: FETCH c_prog_fund_number_create INTO l_valid_flag;
3249: FETCH c_prog_fund_number_create INTO l_valid_flag;
3250: CLOSE c_prog_fund_number_create;
3251: ELSE
3252: /* l_valid_flag :=
3253: ozf_utility_pvt.check_uniqueness(
3254: 'ozf_funds_all_vl'
3255: ,'fund_number = ''' ||
3256: p_fund_rec.fund_number ||
3257: ''' AND fund_id <> ' ||
3280: /*
3281: IF p_fund_rec.fund_type = 'FULLY_ACCRUED' THEN
3282: IF p_validation_mode = jtf_plsql_api.g_create THEN
3283: l_valid_flag :=
3284: ozf_utility_pvt.check_uniqueness(
3285: 'ozf_funds_all_vl'
3286: ,'short_name = ''' || p_fund_rec.short_name || '''');
3287: ELSE
3288: l_valid_flag :=
3285: 'ozf_funds_all_vl'
3286: ,'short_name = ''' || p_fund_rec.short_name || '''');
3287: ELSE
3288: l_valid_flag :=
3289: ozf_utility_pvt.check_uniqueness(
3290: 'ozf_funds_all_vl'
3291: ,'short_name = ''' ||
3292: p_fund_rec.short_name ||
3293: ''' AND fund_id <> ' ||
3328: x_return_status := fnd_api.g_ret_sts_success;
3329:
3330: ----------------------- user_status_id ------------------------
3331: IF p_fund_rec.user_status_id <> fnd_api.g_miss_num THEN
3332: IF ozf_utility_pvt.check_fk_exists(
3333: 'ams_user_statuses_vl'
3334: ,'user_status_id'
3335: ,p_fund_rec.user_status_id) =
3336: fnd_api.g_false THEN
3348: ----------------------- category_id ------------------------
3349: -- mkothari - Bug 4701105 - start ----
3350: IF p_fund_rec.fund_type <> 'QUOTA' THEN
3351: IF p_fund_rec.category_id <> fnd_api.g_miss_num THEN
3352: IF ozf_utility_pvt.check_fk_exists(
3353: 'ams_categories_vl'
3354: ,'category_id'
3355: ,p_fund_rec.category_id) =
3356: fnd_api.g_false THEN
3386: x_return_status := fnd_api.g_ret_sts_success;
3387:
3388: ----------------------- fund_type ------------------------
3389: IF p_fund_rec.fund_type <> fnd_api.g_miss_char THEN
3390: IF ozf_utility_pvt.check_lookup_exists(
3391: p_lookup_table_name => 'OZF_LOOKUPS'
3392: ,p_lookup_type => 'OZF_FUND_TYPE'
3393: ,p_lookup_code => p_fund_rec.fund_type) =
3394: fnd_api.g_false THEN
3408:
3409:
3410: ----------------------- status_code ------------------------
3411: IF p_fund_rec.status_code <> fnd_api.g_miss_char THEN
3412: IF ozf_utility_pvt.check_lookup_exists(
3413: p_lookup_table_name => 'OZF_LOOKUPS'
3414: ,p_lookup_type => 'OZF_FUND_STATUS'
3415: ,p_lookup_code => p_fund_rec.status_code) =
3416: fnd_api.g_false THEN
3449:
3450: ----------------------- liability_flag ------------------------
3451: IF p_fund_rec.liability_flag <> fnd_api.g_miss_char
3452: AND p_fund_rec.liability_flag IS NOT NULL THEN
3453: IF ozf_utility_pvt.is_y_or_n(p_fund_rec.liability_flag) = fnd_api.g_false THEN
3454: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3455: fnd_message.set_name('OZF', 'OZF_FUND_BAD_LIABILITY_FLAG');
3456: fnd_msg_pub.add;
3457: END IF;
3463:
3464: ----------------------- budget_flag ------------------------
3465: IF p_fund_rec.budget_flag <> fnd_api.g_miss_char
3466: AND p_fund_rec.budget_flag IS NOT NULL THEN
3467: IF ozf_utility_pvt.is_y_or_n(p_fund_rec.budget_flag) = fnd_api.g_false THEN
3468: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3469: IF p_fund_rec.fund_type = 'QUOTA' THEN
3470: fnd_message.set_name('OZF', 'OZF_TP_BAD_QUOTA_FLAG');
3471: ELSE
3481:
3482: ----------------------- earned_flag ------------------------
3483: IF p_fund_rec.earned_flag <> fnd_api.g_miss_char
3484: AND p_fund_rec.earned_flag IS NOT NULL THEN
3485: IF ozf_utility_pvt.is_y_or_n(p_fund_rec.earned_flag) = fnd_api.g_false THEN
3486: IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
3487: fnd_message.set_name('OZF', 'OZF_FUND_BAD_EARNED_FLAG');
3488: fnd_msg_pub.add;
3489: END IF;
3842:
3843: -- Check for update validations
3844: IF p_mode = jtf_plsql_api.g_update THEN
3845: -- mpande added on Sep 11 for giving update access to owner and persons who have access with edit metric flag = 'Y'
3846: l_resource_id := ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id);
3847:
3848: IF G_DEBUG THEN
3849: ozf_utility_pvt.debug_message('resource'||ams_access_pvt.check_update_access(p_complete_rec.fund_id, 'FUND', l_resource_id, 'USER'));
3850: END IF;
3845: -- mpande added on Sep 11 for giving update access to owner and persons who have access with edit metric flag = 'Y'
3846: l_resource_id := ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id);
3847:
3848: IF G_DEBUG THEN
3849: ozf_utility_pvt.debug_message('resource'||ams_access_pvt.check_update_access(p_complete_rec.fund_id, 'FUND', l_resource_id, 'USER'));
3850: END IF;
3851:
3852: IF l_resource_id <> -1 THEN
3853: IF ams_access_pvt.check_update_access(
5028: FND_MSG_PUB.initialize;
5029: END IF;
5030:
5031: IF G_DEBUG THEN
5032: OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
5033: END IF;
5034:
5035: -- Initialize API return status to SUCCESS
5036: x_return_status := FND_API.G_RET_STS_SUCCESS;
5272:
5273:
5274: -- Debug Message
5275: IF G_DEBUG THEN
5276: OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' end');
5277: END IF;
5278:
5279: -- Standard call to get message count and if count is 1, get message info.
5280: FND_MSG_PUB.Count_And_Get (
5377: FND_MSG_PUB.initialize;
5378: END IF;
5379:
5380: IF G_DEBUG THEN
5381: OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
5382: END IF;
5383:
5384: -- Initialize API return status to SUCCESS
5385: x_return_status := FND_API.G_RET_STS_SUCCESS;
5418: p_encoded => fnd_api.g_false
5419: ,p_count => x_msg_count
5420: ,p_data => x_msg_data);
5421: IF G_DEBUG THEN
5422: ozf_utility_pvt.debug_message( l_api_name || ': end');
5423: END IF;
5424: EXCEPTION
5425: WHEN fnd_api.g_exc_error THEN
5426: x_return_status := fnd_api.g_ret_sts_error;
5526: FND_MSG_PUB.initialize;
5527: END IF;
5528:
5529: IF G_DEBUG THEN
5530: OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' start');
5531: END IF;
5532:
5533: FOR fund IN c_parent
5534: LOOP
5608: p_encoded => fnd_api.g_false
5609: ,p_count => x_msg_count
5610: ,p_data => x_msg_data);
5611: IF G_DEBUG THEN
5612: ozf_utility_pvt.debug_message( l_api_name ||': end');
5613: END IF;
5614:
5615: EXCEPTION
5616: WHEN fnd_api.g_exc_error THEN