[Home] [Help]
85: -- x_return_status OUT VARCHAR2
86: --
87: -- NOTES
88: -- This API will create utlizations for camps, schedules,events,offers.event_sche
89: -- in the ozf_Act_budgets table and ozf_Fund_utlized_vl table.
90: -- This API should be called from the cost module of OM only
91: -- HISTORY
92: -- 04/27/2001 Mumu Pande Create.
93: ---------------------------------------------------------------------
108: CURSOR c_parent_source IS
109: SELECT parent_src_id, parent_currency, SUM (amount) total_amount
110: FROM (SELECT a1.parent_source_id parent_src_id, a1.parent_src_curr parent_currency
111: ,NVL (SUM (a1.parent_src_apprvd_amt), 0) amount
112: FROM ozf_act_budgets a1
113: WHERE a1.act_budget_used_by_id = p_budget_used_by_id
114: AND a1.arc_act_budget_used_by = p_budget_used_by_type
115: AND a1.status_code = 'APPROVED'
116: AND a1.transfer_type <> 'UTILIZED'
117: GROUP BY a1.parent_source_id, a1.parent_src_curr
118: UNION
119: SELECT a2.parent_source_id parent_src_id, a2.parent_src_curr parent_currency
120: ,-NVL (SUM (a2.parent_src_apprvd_amt), 0) amount
121: FROM ozf_act_budgets a2
122: WHERE a2.budget_source_id = p_budget_used_by_id
123: AND a2.budget_source_type = p_budget_used_by_type
124: AND a2.status_code = 'APPROVED'
125: GROUP BY a2.parent_source_id, a2.parent_src_curr)
165: SELECT activity_budget_id
166: ,object_version_number
167: ,approved_amount
168: ,parent_src_apprvd_amt
169: FROM ozf_act_budgets
170: WHERE act_budget_used_by_id = p_used_by_id
171: AND arc_act_budget_used_by = p_used_by_type
172:
173: -- AND parent_source_id = p_parent_src_id
600: -- NAME
601: -- Create Fund Utilization
602: -- PURPOSE
603: -- Create utilizations for the utlized amount of that activity
604: -- called only from ozf_Act_budgets API for utlized amount creation
605: -- HISTORY
606: -- 02/23/2001 mpande CREATED
607: -- 02/28/02 feliu Added condition for manual adjustment.
608: ---------------------------------------------------------------------
649: := ozf_actbudgets_pvt.g_miss_act_util_rec
650: ,x_utilized_amount OUT NOCOPY NUMBER
651: ) IS
652: l_use_fund_staging_tables VARCHAR2(1):= 'F';
653: lx_ozf_act_budgets_tbl OZF_UTILITY_PVT.ozf_act_budgets_table;
654: lx_ozf_funds_old_rectype OZF_UTILITY_PVT.ozf_funds_all_b_rectype;
655: lx_ozf_funds_new_tbl OZF_UTILITY_PVT.ozf_funds_table;
656: l_batch_type VARCHAR2(30);
657: BEGIN
663: ,p_act_util_rec => p_act_util_rec
664: ,x_utilized_amount => x_utilized_amount
665: ,p_batch_type => l_batch_type
666: ,p_use_fund_staging_tables => l_use_fund_staging_tables
667: ,px_ozf_act_budgets_tbl => lx_ozf_act_budgets_tbl
668: ,px_ozf_funds_old_rectype => lx_ozf_funds_old_rectype
669: ,px_ozf_funds_new_tbl => lx_ozf_funds_new_tbl -- For TPA Parallel Execution ER - 9614703
670: );
671: END create_fund_utilization;
680: ,x_utilized_amount OUT NOCOPY NUMBER
681: ,x_utilization_id OUT NOCOPY NUMBER
682: ) IS
683: l_use_fund_staging_tables VARCHAR2(1):= 'F';
684: lx_ozf_act_budgets_tbl OZF_UTILITY_PVT.ozf_act_budgets_table;
685: lx_ozf_funds_old_rectype OZF_UTILITY_PVT.ozf_funds_all_b_rectype;
686: lx_ozf_funds_new_tbl OZF_UTILITY_PVT.ozf_funds_table;
687: l_batch_type VARCHAR2(30);
688: BEGIN
694: ,p_act_util_rec => p_act_util_rec
695: ,x_utilized_amount => x_utilized_amount
696: ,p_batch_type => l_batch_type
697: ,p_use_fund_staging_tables => l_use_fund_staging_tables
698: ,px_ozf_act_budgets_tbl => lx_ozf_act_budgets_tbl
699: ,px_ozf_funds_old_rectype => lx_ozf_funds_old_rectype
700: ,px_ozf_funds_new_tbl => lx_ozf_funds_new_tbl -- For TPA Parallel Execution ER - 9614703
701: ,x_utilization_id => x_utilization_id
702: );
711: := ozf_actbudgets_pvt.g_miss_act_util_rec
712: ,x_utilized_amount OUT NOCOPY NUMBER
713: ,p_batch_type IN VARCHAR2
714: ,p_use_fund_staging_tables IN VARCHAR2
715: ,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
716: ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
717: ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table
718: ) IS
719: l_utilization_id NUMBER;
728: ,p_act_util_rec => p_act_util_rec
729: ,x_utilized_amount => x_utilized_amount
730: ,p_batch_type => p_batch_type
731: ,p_use_fund_staging_tables => p_use_fund_staging_tables
732: ,px_ozf_act_budgets_tbl => px_ozf_act_budgets_tbl
733: ,px_ozf_funds_old_rectype => px_ozf_funds_old_rectype
734: ,px_ozf_funds_new_tbl => px_ozf_funds_new_tbl -- For TPA Parallel Execution ER - 9614703
735: ,x_utilization_id => l_utilization_id
736: );
742: -- NAME
743: -- Create Fund Utilization
744: -- PURPOSE
745: -- Create utilizations for the utlized amount of that activity
746: -- called only from ozf_Act_budgets API for utlized amount creation
747: -- HISTORY
748: -- 02/23/2001 mpande CREATED
749: -- 02/28/02 feliu Added condition for manual adjustment.
750: -- 06/21/2004 yzhao Added x_utilized_amount to return actual utilized amount
747: -- HISTORY
748: -- 02/23/2001 mpande CREATED
749: -- 02/28/02 feliu Added condition for manual adjustment.
750: -- 06/21/2004 yzhao Added x_utilized_amount to return actual utilized amount
751: -- 27/10/2009 muthsubr Added px_ozf_act_budgets_tbl, p_use_fund_staging_tables
752: -- px_ozf_funds_old_rectype, px_ozf_funds_new_rectype
753: -- for bug#8867381.
754: -- 29/11/2010 muthsubr Altered px_ozf_funds_new_rectype to px_ozf_funds_new_tbl
755: -- for TPA Parallel Execution ERBug#9614703.
764: := ozf_actbudgets_pvt.g_miss_act_util_rec
765: ,x_utilized_amount OUT NOCOPY NUMBER
766: ,p_batch_type IN VARCHAR2
767: ,p_use_fund_staging_tables IN VARCHAR2
768: ,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
769: ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
770: ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table
771: ,x_utilization_id OUT NOCOPY NUMBER
772: ) IS
850: and status= 'A';
851:
852: -- rimehrot, bug fix 4030022
853: CURSOR c_check_budget_request(p_offer_id IN NUMBER, p_fund_id IN NUMBER) IS
854: SELECT 'X' FROM ozf_act_budgets
855: WHERE act_budget_used_by_id = p_offer_id
856: AND budget_source_id = p_fund_id
857: AND status_code ='APPROVED'
858: AND transfer_type = 'REQUEST';
1006: ELSIF p_act_budget_rec.parent_source_id IS NULL
1007: OR p_act_budget_rec.parent_source_id = fnd_api.g_miss_num THEN
1008: --Added for bug 7030415 , For fixed budget
1009: --nirprasa,12.2 adjustment_net_accrual flow comes to this else condition.
1010: --While updating ozf_act_budgets 'UTILIZED' recs, p_act_util_rec.plan_curr_amount
1011: --holds the amount in plan currency. SO send this amount for conversion to budget's curr.
1012: IF G_DEBUG THEN
1013: ozf_utility_pvt.debug_message('budget_source_type '|| p_act_budget_rec.budget_source_type);
1014: ozf_utility_pvt.debug_message('p_act_budget_rec.approved_amount '|| p_act_budget_rec.approved_amount);
1390: l_util_rec.discount_amount := p_act_util_rec.discount_amount;
1391: l_util_rec.discount_amount_currency_code := p_act_util_rec.discount_amount_currency_code;
1392:
1393: -- For Bug#8867381, introduced p_use_fund_staging_tables for staging table impln of chargeback flow
1394: -- and px_ozf_act_budgets_tbl for PLSQL table impln of storing budgets value.
1395: -- and px_ozf_funds_old_rectype for taking the funds old values
1396: -- For TPA Parallel Execution ERBug#9614703
1397: -- and changed px_ozf_funds_new_rectype to px_ozf_funds_new_tbl.
1398:
1407: ,p_utilization_rec=> l_util_rec
1408: ,x_utilization_id=> x_utilization_id --l_util_id
1409: ,p_batch_type => p_batch_type
1410: ,p_use_fund_staging_tables => p_use_fund_staging_tables
1411: ,px_ozf_act_budgets_tbl => px_ozf_act_budgets_tbl
1412: ,px_ozf_funds_old_rectype => px_ozf_funds_old_rectype
1413: ,px_ozf_funds_new_tbl => px_ozf_funds_new_tbl -- For TPA Parallel Execution ER - 9614703
1414: );
1415:
1595: AND object_type = 'OFFR';
1596:
1597: /* Fix for the bug#3047142
1598: SELECT SUM(DECODE(transfer_type,'REQUEST', NVL(approved_amount,0),NVL(0-approved_original_amount,0)))
1599: FROM ozf_act_budgets
1600: WHERE status_code = 'APPROVED'
1601: AND ((arc_act_budget_used_by = 'OFFR' AND act_budget_used_by_id = l_id and transfer_type ='REQUEST')
1602: OR (budget_source_type = 'OFFR' AND budget_source_id = l_id and transfer_type ='TRANSFER'));
1603:
1601: AND ((arc_act_budget_used_by = 'OFFR' AND act_budget_used_by_id = l_id and transfer_type ='REQUEST')
1602: OR (budget_source_type = 'OFFR' AND budget_source_id = l_id and transfer_type ='TRANSFER'));
1603:
1604: /* SELECT SUM(DECODE(transfer_type,'REQUEST', NVL(approved_amount,0),NVL(0-approved_original_amount,0)))
1605: FROM ozf_act_budgets
1606: WHERE arc_act_budget_used_by = 'OFFR'
1607: AND act_budget_used_by_id = l_id;
1608: */
1609:
1728: -- ,p_act_budgets_rec
1729: -- ,x_act_budget_id
1730: -- x_utilized_amount : actual utilized amount when success
1731: -- p_use_fund_staging_tables for staging table impln of chargeback flow (For Bug#8867381)
1732: -- px_ozf_act_budgets_tbl for PLSQL table impln of storing budgets value.
1733: -- px_ozf_funds_old_rectype for taking the funds old values
1734: -- px_ozf_funds_new_tbl for taking the new values after updation. (For TPA Parallel Execution ER Bug#9614703)
1735: -- NOTES
1736: -- HISTORY
1746: x_act_budget_id OUT NOCOPY NUMBER,
1747: x_utilized_amount OUT NOCOPY NUMBER, -- added yzhao Jun 21, 2004
1748: p_batch_type IN VARCHAR2,
1749: p_use_fund_staging_tables IN VARCHAR2,
1750: px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table,
1751: px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype,
1752: px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table
1753: ) IS
1754: l_utilization_id NUMBER;
1764: ,x_act_budget_id => x_act_budget_id
1765: ,x_utilized_amount => x_utilized_amount
1766: ,p_batch_type => p_batch_type
1767: ,p_use_fund_staging_tables => p_use_fund_staging_tables
1768: ,px_ozf_act_budgets_tbl => px_ozf_act_budgets_tbl
1769: ,px_ozf_funds_old_rectype => px_ozf_funds_old_rectype
1770: ,px_ozf_funds_new_tbl => px_ozf_funds_new_tbl
1771: ,x_utilization_id => l_utilization_id);
1772:
1781: x_act_budget_id OUT NOCOPY NUMBER,
1782: x_utilized_amount OUT NOCOPY NUMBER, -- added yzhao Jun 21, 2004
1783: p_batch_type IN VARCHAR2,
1784: p_use_fund_staging_tables IN VARCHAR2,
1785: px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table,
1786: px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype,
1787: px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table,
1788: x_utilization_id OUT NOCOPY NUMBER
1789: ) IS
1788: x_utilization_id OUT NOCOPY NUMBER
1789: ) IS
1790: CURSOR c_act_util_rec (p_used_by_id IN NUMBER, p_used_by_type IN VARCHAR2) IS
1791: SELECT activity_budget_id, object_version_number, approved_amount
1792: FROM ozf_act_budgets
1793: WHERE act_budget_used_by_id = p_used_by_id
1794: AND arc_act_budget_used_by = p_used_by_type
1795: AND transfer_type = 'UTILIZED';
1796: CURSOR c_offer_type (p_offer_id IN NUMBER) IS
1831:
1832: -- For Bug#8867381
1833: CURSOR old_budgets_csr(p_act_budget_id IN NUMBER) IS
1834: SELECT REQUEST_AMOUNT, APPROVED_AMOUNT, APPROVED_ORIGINAL_AMOUNT, APPROVED_AMOUNT_FC,
1835: PARENT_SRC_APPRVD_AMT, SRC_CURR_REQUEST_AMT FROM OZF_ACT_BUDGETS
1836: WHERE ACTIVITY_BUDGET_ID = p_act_budget_id;
1837:
1838: BEGIN
1839: x_return_status := fnd_api.g_ret_sts_success;
1876: l_budget_request_rec.requester_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
1877: END IF;
1878:
1879: -- For Bug#8867381, introduced p_use_fund_staging_tables for staging table impln of chargeback flow
1880: -- and px_ozf_act_budgets_tbl for PLSQL table impln of storing budgets value.
1881: -- and px_ozf_funds_old_rectype for taking the funds old values
1882: -- For TPA Parallel Execution ERBug#9614703
1883: -- and changed px_ozf_funds_new_rectype to px_ozf_funds_new_tbl.
1884:
1897: x_utilized_amount => l_utilized_amount,
1898: p_resale_batch_id => l_act_util_rec.reference_id,
1899: p_batch_type => p_batch_type,
1900: p_use_fund_staging_tables => p_use_fund_staging_tables,
1901: px_ozf_act_budgets_tbl => px_ozf_act_budgets_tbl,
1902: px_ozf_funds_old_rectype => px_ozf_funds_old_rectype,
1903: px_ozf_funds_new_tbl => px_ozf_funds_new_tbl, -- For TPA Parallel Execution ERBug#9614703
1904: x_utilization_id => x_utilization_id); --nirprasa - added for Bug 9383565
1905:
1950: x_utilized_amount => x_utilized_amount, -- yzhao: 06/21/2004 added for chargeback
1951: x_utilization_id => x_utilization_id --nirprasa - added for Bug 9383565
1952: );*/
1953: /* Bug#9614703 update to ozf_funds_all_tl table was happening during creation of act_budget
1954: records. So we need to send p_use_fund_staging_tables flag and px_ozf_act_budgets_tbl rec
1955: in this flow as well. Hence used the overloaded procedure.
1956: */
1957: ozf_actbudgets_pvt.create_act_budgets (
1958: p_api_version=> l_api_version,
1969: x_utilized_amount => x_utilized_amount, -- yzhao: 06/21/2004 added for chargeback
1970: p_resale_batch_id => l_act_util_rec.reference_id,
1971: p_batch_type => p_batch_type,
1972: p_use_fund_staging_tables => p_use_fund_staging_tables,
1973: px_ozf_act_budgets_tbl => px_ozf_act_budgets_tbl,
1974: px_ozf_funds_old_rectype => px_ozf_funds_old_rectype,
1975: px_ozf_funds_new_tbl => px_ozf_funds_new_tbl,
1976: x_utilization_id => x_utilization_id --nirprasa - added for Bug 9383565
1977: );
2080: );
2081: */
2082:
2083: -- For Bug#8867381, introduced p_use_fund_staging_tables for staging table impln of chargeback flow
2084: -- and px_ozf_act_budgets_tbl for PLSQL table impln of storing budgets value.
2085: -- and px_ozf_funds_old_rectype for taking the funds old values
2086: -- For TPA Parallel Execution ERBug#9614703
2087: -- and changed px_ozf_funds_new_rectype to px_ozf_funds_new_tbl.
2088:
2105: p_act_util_rec => l_act_util_rec,
2106: x_utilized_amount => x_utilized_amount,
2107: p_batch_type => p_batch_type,
2108: p_use_fund_staging_tables => p_use_fund_staging_tables,
2109: px_ozf_act_budgets_tbl => px_ozf_act_budgets_tbl,
2110: px_ozf_funds_old_rectype => px_ozf_funds_old_rectype,
2111: px_ozf_funds_new_tbl => px_ozf_funds_new_tbl, -- For TPA Parallel Execution ERBug#9614703
2112: x_utilization_id => x_utilization_id --nirprasa - added for Bug 9383565
2113: );
2229: p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type,
2230: x_act_budget_id OUT NOCOPY NUMBER,
2231: x_utilized_amount OUT NOCOPY NUMBER
2232: ) IS
2233: lx_ozf_act_budgets_tbl OZF_UTILITY_PVT.ozf_act_budgets_table;
2234: lx_ozf_funds_old_rectype OZF_UTILITY_PVT.ozf_funds_all_b_rectype;
2235: lx_ozf_funds_new_tbl OZF_UTILITY_PVT.ozf_funds_table;
2236: l_batch_type VARCHAR2(30);
2237: BEGIN
2243: p_act_util_rec => p_act_util_rec,
2244: x_act_budget_id => x_act_budget_id,
2245: x_utilized_amount => x_utilized_amount,
2246: p_batch_type => l_batch_type,
2247: px_ozf_act_budgets_tbl => lx_ozf_act_budgets_tbl,
2248: px_ozf_funds_old_rectype => lx_ozf_funds_old_rectype,
2249: px_ozf_funds_new_tbl => lx_ozf_funds_new_tbl -- For TPA Parallel Execution ER - 9614703
2250: );
2251: END process_act_budgets;
2260: x_act_budget_id OUT NOCOPY NUMBER,
2261: x_utilized_amount OUT NOCOPY NUMBER,
2262: x_utilization_id OUT NOCOPY NUMBER
2263: ) IS
2264: lx_ozf_act_budgets_tbl OZF_UTILITY_PVT.ozf_act_budgets_table;
2265: lx_ozf_funds_old_rectype OZF_UTILITY_PVT.ozf_funds_all_b_rectype;
2266: lx_ozf_funds_new_tbl OZF_UTILITY_PVT.ozf_funds_table;
2267: l_batch_type VARCHAR2(30);
2268: BEGIN
2274: p_act_util_rec => p_act_util_rec,
2275: x_act_budget_id => x_act_budget_id,
2276: x_utilized_amount => x_utilized_amount,
2277: p_batch_type => l_batch_type,
2278: px_ozf_act_budgets_tbl => lx_ozf_act_budgets_tbl,
2279: px_ozf_funds_old_rectype => lx_ozf_funds_old_rectype,
2280: px_ozf_funds_new_tbl => lx_ozf_funds_new_tbl, -- For TPA Parallel Execution ER - 9614703
2281: x_utilization_id => x_utilization_id
2282: );