[Home] [Help]
68: -- 06/26/2005 Ribha fixed bug 4173825 - get adjusted_amount from oe_price_adjustments
69: -- 06/26/2005 Ribha fixed bug 4417084 - for partial return order
70: -- 07/27/2005 Feliu add enhancement for R12 to insert order info to ozf_sales_transaction table.
71: -- 08/01/2005 Ribha R12: populate universal currency amount in ozf_funds_utilized_all_b
72: -- 08/02/2005 Ribha R12: populate new table ozf_object_fund_summary
73: -- 09/21/2005 Ribha Bug Fix 4619156
74: -- 10/28/2005 Ribha fixed bug 4676217 (same as 3697213 in 1159)
75: -- 12/23/2005 kdass Bug 4778995 - removed columns month_id/quarter_id/year_id
76: -- 03/31/2006 kdass fixed bug 5101720
186: -- rimehrot, for R12: query from the new table
187:
188: CURSOR c_budget (p_src_id IN NUMBER) IS
189: SELECT fund_id parent_source_id, committed_amt total_amount , fund_currency parent_curr
190: FROM ozf_object_fund_summary
191: WHERE object_type = 'OFFR'
192: AND object_id = p_src_id
193: --AND NVL(committed_amt, 0) <> 0
194: ORDER BY fund_id;
209: l_converted_amt NUMBER;
210: l_count1 NUMBER :=0;
211: l_total_amount1 NUMBER :=0;
212:
213: TYPE parentIdType IS TABLE OF ozf_object_fund_summary.fund_id%TYPE;
214: TYPE amountType IS TABLE OF ozf_object_fund_summary.committed_amt%TYPE;
215: TYPE currencyType IS TABLE OF ozf_object_fund_summary.fund_currency%TYPE;
216: TYPE fraction_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
217: l_parent_id_tbl parentIdType;
210: l_count1 NUMBER :=0;
211: l_total_amount1 NUMBER :=0;
212:
213: TYPE parentIdType IS TABLE OF ozf_object_fund_summary.fund_id%TYPE;
214: TYPE amountType IS TABLE OF ozf_object_fund_summary.committed_amt%TYPE;
215: TYPE currencyType IS TABLE OF ozf_object_fund_summary.fund_currency%TYPE;
216: TYPE fraction_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
217: l_parent_id_tbl parentIdType;
218: l_total_amount_tbl amountType;
211: l_total_amount1 NUMBER :=0;
212:
213: TYPE parentIdType IS TABLE OF ozf_object_fund_summary.fund_id%TYPE;
214: TYPE amountType IS TABLE OF ozf_object_fund_summary.committed_amt%TYPE;
215: TYPE currencyType IS TABLE OF ozf_object_fund_summary.fund_currency%TYPE;
216: TYPE fraction_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
217: l_parent_id_tbl parentIdType;
218: l_total_amount_tbl amountType;
219: l_parent_curr_tbl currencyType;
231: WHERE plan_id = p_src_id;
232:
233: /* yzhao: 10/03/2003 fix bug 3156515 - PROMOTIONAL GOODS OFFER EXCEEDS THE BUDGET AMOUNT
234: get utilized amount.
235: -- rimehrot, commented for R12: use ozf_object_fund_summary table directly.
236: CURSOR c_get_utilized_amount(p_offer_id IN NUMBER, p_fund_id IN NUMBER) IS
237: SELECT SUM(NVL(a2.amount, 0)) amount
238: FROM ozf_funds_utilized_all_b a2
239: WHERE a2.plan_id = p_offer_id
240: AND a2.plan_type = 'OFFR'
241: AND a2.fund_id = p_fund_id
242: AND a2.utilization_type NOT IN ('REQUEST', 'TRANSFER', 'SALES_ACCRUAL');
243: */
244: -- rimehrot, for R12: use ozf_object_fund_summary directly to get utilized amount.
245: CURSOR c_get_utilized_amount(p_offer_id IN NUMBER, p_fund_id IN NUMBER) IS
246: SELECT utilized_amt
247: FROM ozf_object_fund_summary
248: WHERE fund_id = p_fund_id
243: */
244: -- rimehrot, for R12: use ozf_object_fund_summary directly to get utilized amount.
245: CURSOR c_get_utilized_amount(p_offer_id IN NUMBER, p_fund_id IN NUMBER) IS
246: SELECT utilized_amt
247: FROM ozf_object_fund_summary
248: WHERE fund_id = p_fund_id
249: AND object_type = 'OFFR'
250: AND object_id = p_offer_id;
251:
1062: SELECT request_header_id
1063: FROM ozf_request_headers_all_b
1064: WHERE offer_id =p_list_header_id;
1065:
1066: -- rimehrot: for R12 update ozf_object_fund_summary table
1067: CURSOR c_get_objfundsum_rec(p_object_type IN VARCHAR2, p_object_id IN NUMBER, p_fund_id IN NUMBER) IS
1068: SELECT objfundsum_id
1069: , object_version_number
1070: , committed_amt
1081: , univ_curr_recal_committed_amt
1082: , univ_curr_utilized_amt
1083: , univ_curr_earned_amt
1084: , univ_curr_paid_amt
1085: FROM ozf_object_fund_summary
1086: WHERE object_type = p_object_type
1087: AND object_id = p_object_id
1088: AND fund_id = p_fund_id;
1089:
1871: RAISE fnd_api.g_exc_error;
1872: END IF;
1873: CLOSE c_mc_trans;
1874: */
1875: -- rimehrot changed for R12, Populate new table ozf_object_fund_summary
1876: -- rimehrot: component_id/type is the destination. Will always be equal to plan_id/type in this case
1877: l_objfundsum_rec := NULL;
1878: OPEN c_get_objfundsum_rec(l_utilization_rec.component_type
1879: , l_utilization_rec.component_id
1910: l_new_orig_amt := NVL (l_utilization_rec.univ_curr_amount, 0);
1911: l_new_utilized_amt := NVL (l_utilization_rec.univ_curr_amount, 0);
1912: l_new_paid_amt := 0;
1913:
1914: -- rimehrot changed for R12, Populate utilized/committed/recal_committed in ozf_object_fund_summary
1915: l_objfundsum_rec.utilized_amt := NVL(l_objfundsum_rec.utilized_amt, 0) + NVL(l_utilization_rec.amount, 0);
1916: l_objfundsum_rec.plan_curr_utilized_amt := NVL(l_objfundsum_rec.plan_curr_utilized_amt, 0)
1917: --nirprasa,use new plan currency column
1918: -- + NVL(l_utilization_rec.plan_curr_amount, 0);*/
2089: AND object_version_number = fund.object_version_number;
2090: END LOOP;
2091: END IF;
2092:
2093: -- rimehrot: for R12, create or update in ozf_object_fund_summary
2094: IF l_objfundsum_rec.objfundsum_id IS NULL THEN
2095: l_objfundsum_rec.fund_id := l_utilization_rec.fund_id;
2096: l_objfundsum_rec.fund_currency := l_utilization_rec.currency_code;
2097: l_objfundsum_rec.object_type := l_utilization_rec.component_type;
5861: FROM ozf_funds_all_b
5862: connect by prior parent_fund_id =fund_id
5863: start with fund_id = p_fund_id;
5864:
5865: -- rimehrot: for R12 update ozf_object_fund_summary table
5866: CURSOR c_get_objfundsum_rec(p_object_type IN VARCHAR2, p_object_id IN NUMBER, p_fund_id IN NUMBER) IS
5867: SELECT objfundsum_id
5868: , object_version_number
5869: , earned_amt
5871: , plan_curr_earned_amt
5872: , plan_curr_paid_amt
5873: , univ_curr_earned_amt
5874: , univ_curr_paid_amt
5875: FROM ozf_object_fund_summary
5876: WHERE object_type = p_object_type
5877: AND object_id = p_object_id
5878: AND fund_id = p_fund_id;
5879:
6045: IF l_gl_posted_flag = G_GL_FLAG_NOLIAB THEN
6046: l_orig_amt := p_util_amount;
6047: l_rollup_orig_amt := l_new_univ_amt;
6048: ELSE
6049: -- rimehrot changed for R12, Populate new table ozf_object_fund_summary
6050: l_objfundsum_rec := NULL;
6051: OPEN c_get_objfundsum_rec(p_util_plan_type
6052: , p_util_plan_id
6053: , p_util_fund_id);
6076:
6077: l_earned_amt := p_util_amount;
6078: l_rollup_earned_amt := l_new_univ_amt;
6079:
6080: -- rimehrot: for R12, populate paid/earned columns in ozf_object_fund_summary
6081: ozf_utility_pvt.write_conc_log('l_objfundsum_rec.earned_amt ' || l_objfundsum_rec.earned_amt);
6082: ozf_utility_pvt.write_conc_log('p_util_amount ' || p_util_amount);
6083: ozf_utility_pvt.write_conc_log('l_objfundsum_rec.plan_curr_earned_amt ' || l_objfundsum_rec.plan_curr_earned_amt);
6084: ozf_utility_pvt.write_conc_log('p_util_plan_amount ' || p_util_plan_amount);
6741: , plan_curr_paid_amt
6742: , univ_curr_utilized_amt
6743: , univ_curr_earned_amt
6744: , univ_curr_paid_amt
6745: FROM ozf_object_fund_summary
6746: WHERE object_type = p_object_type
6747: AND object_id = p_object_id
6748: AND fund_id = p_fund_id;
6749: