[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
140: -- rimehrot, for R12: query from the new table
141:
142: CURSOR c_budget (p_src_id IN NUMBER) IS
143: SELECT fund_id parent_source_id, committed_amt total_amount , fund_currency parent_curr
144: FROM ozf_object_fund_summary
145: WHERE object_type = 'OFFR'
146: AND object_id = p_src_id
147: --AND NVL(committed_amt, 0) <> 0
148: ORDER BY fund_id;
161: l_eligible_total_amount NUMBER := 0;
162: l_eligible_flag BOOLEAN := false;
163: l_converted_amt NUMBER;
164:
165: TYPE parentIdType IS TABLE OF ozf_object_fund_summary.fund_id%TYPE;
166: TYPE amountType IS TABLE OF ozf_object_fund_summary.committed_amt%TYPE;
167: TYPE currencyType IS TABLE OF ozf_object_fund_summary.fund_currency%TYPE;
168: TYPE fraction_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
169: l_parent_id_tbl parentIdType;
162: l_eligible_flag BOOLEAN := false;
163: l_converted_amt NUMBER;
164:
165: TYPE parentIdType IS TABLE OF ozf_object_fund_summary.fund_id%TYPE;
166: TYPE amountType IS TABLE OF ozf_object_fund_summary.committed_amt%TYPE;
167: TYPE currencyType IS TABLE OF ozf_object_fund_summary.fund_currency%TYPE;
168: TYPE fraction_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
169: l_parent_id_tbl parentIdType;
170: l_total_amount_tbl amountType;
163: l_converted_amt NUMBER;
164:
165: TYPE parentIdType IS TABLE OF ozf_object_fund_summary.fund_id%TYPE;
166: TYPE amountType IS TABLE OF ozf_object_fund_summary.committed_amt%TYPE;
167: TYPE currencyType IS TABLE OF ozf_object_fund_summary.fund_currency%TYPE;
168: TYPE fraction_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
169: l_parent_id_tbl parentIdType;
170: l_total_amount_tbl amountType;
171: l_parent_curr_tbl currencyType;
183: WHERE plan_id = p_src_id;
184:
185: /* yzhao: 10/03/2003 fix bug 3156515 - PROMOTIONAL GOODS OFFER EXCEEDS THE BUDGET AMOUNT
186: get utilized amount.
187: -- rimehrot, commented for R12: use ozf_object_fund_summary table directly.
188: CURSOR c_get_utilized_amount(p_offer_id IN NUMBER, p_fund_id IN NUMBER) IS
189: SELECT SUM(NVL(a2.amount, 0)) amount
190: FROM ozf_funds_utilized_all_b a2
191: WHERE a2.plan_id = p_offer_id
192: AND a2.plan_type = 'OFFR'
193: AND a2.fund_id = p_fund_id
194: AND a2.utilization_type NOT IN ('REQUEST', 'TRANSFER', 'SALES_ACCRUAL');
195: */
196: -- rimehrot, for R12: use ozf_object_fund_summary directly to get utilized amount.
197: CURSOR c_get_utilized_amount(p_offer_id IN NUMBER, p_fund_id IN NUMBER) IS
198: SELECT utilized_amt
199: FROM ozf_object_fund_summary
200: WHERE fund_id = p_fund_id
195: */
196: -- rimehrot, for R12: use ozf_object_fund_summary directly to get utilized amount.
197: CURSOR c_get_utilized_amount(p_offer_id IN NUMBER, p_fund_id IN NUMBER) IS
198: SELECT utilized_amt
199: FROM ozf_object_fund_summary
200: WHERE fund_id = p_fund_id
201: AND object_type = 'OFFR'
202: AND object_id = p_offer_id;
203:
907: SELECT request_header_id
908: FROM ozf_request_headers_all_b
909: WHERE offer_id =p_list_header_id;
910:
911: -- rimehrot: for R12 update ozf_object_fund_summary table
912: CURSOR c_get_objfundsum_rec(p_object_type IN VARCHAR2, p_object_id IN NUMBER, p_fund_id IN NUMBER) IS
913: SELECT objfundsum_id
914: , object_version_number
915: , committed_amt
926: , univ_curr_recal_committed_amt
927: , univ_curr_utilized_amt
928: , univ_curr_earned_amt
929: , univ_curr_paid_amt
930: FROM ozf_object_fund_summary
931: WHERE object_type = p_object_type
932: AND object_id = p_object_id
933: AND fund_id = p_fund_id;
934:
1465: RAISE fnd_api.g_exc_error;
1466: END IF;
1467: CLOSE c_mc_trans;
1468: */
1469: -- rimehrot changed for R12, Populate new table ozf_object_fund_summary
1470: -- rimehrot: component_id/type is the destination. Will always be equal to plan_id/type in this case
1471: l_objfundsum_rec := NULL;
1472: OPEN c_get_objfundsum_rec(l_utilization_rec.component_type
1473: , l_utilization_rec.component_id
1504: l_new_orig_amt := NVL (l_utilization_rec.univ_curr_amount, 0);
1505: l_new_utilized_amt := NVL (l_utilization_rec.univ_curr_amount, 0);
1506: l_new_paid_amt := 0;
1507:
1508: -- rimehrot changed for R12, Populate utilized/committed/recal_committed in ozf_object_fund_summary
1509: l_objfundsum_rec.utilized_amt := NVL(l_objfundsum_rec.utilized_amt, 0) + NVL(l_utilization_rec.amount, 0);
1510: l_objfundsum_rec.plan_curr_utilized_amt := NVL(l_objfundsum_rec.plan_curr_utilized_amt, 0)
1511: + NVL(l_utilization_rec.plan_curr_amount, 0);
1512: l_objfundsum_rec.univ_curr_utilized_amt := NVL(l_objfundsum_rec.univ_curr_utilized_amt, 0)
1680: AND object_version_number = fund.object_version_number;
1681: END LOOP;
1682: END IF;
1683:
1684: -- rimehrot: for R12, create or update in ozf_object_fund_summary
1685: IF l_objfundsum_rec.objfundsum_id IS NULL THEN
1686: l_objfundsum_rec.fund_id := l_utilization_rec.fund_id;
1687: l_objfundsum_rec.fund_currency := l_utilization_rec.currency_code;
1688: l_objfundsum_rec.object_type := l_utilization_rec.component_type;
4623: FROM ozf_funds_all_b
4624: connect by prior parent_fund_id =fund_id
4625: start with fund_id = p_fund_id;
4626:
4627: -- rimehrot: for R12 update ozf_object_fund_summary table
4628: CURSOR c_get_objfundsum_rec(p_object_type IN VARCHAR2, p_object_id IN NUMBER, p_fund_id IN NUMBER) IS
4629: SELECT objfundsum_id
4630: , object_version_number
4631: , earned_amt
4633: , plan_curr_earned_amt
4634: , plan_curr_paid_amt
4635: , univ_curr_earned_amt
4636: , univ_curr_paid_amt
4637: FROM ozf_object_fund_summary
4638: WHERE object_type = p_object_type
4639: AND object_id = p_object_id
4640: AND fund_id = p_fund_id;
4641:
4795: IF l_gl_posted_flag = G_GL_FLAG_NOLIAB THEN
4796: l_orig_amt := p_util_amount;
4797: l_rollup_orig_amt := l_new_univ_amt;
4798: ELSE
4799: -- rimehrot changed for R12, Populate new table ozf_object_fund_summary
4800: l_objfundsum_rec := NULL;
4801: OPEN c_get_objfundsum_rec(p_util_plan_type
4802: , p_util_plan_id
4803: , p_util_fund_id);
4826:
4827: l_earned_amt := p_util_amount;
4828: l_rollup_earned_amt := l_new_univ_amt;
4829:
4830: -- rimehrot: for R12, populate paid/earned columns in ozf_object_fund_summary
4831: l_objfundsum_rec.earned_amt := NVL(l_objfundsum_rec.earned_amt, 0) + NVL(p_util_amount, 0);
4832: l_objfundsum_rec.plan_curr_earned_amt := NVL(l_objfundsum_rec.plan_curr_earned_amt, 0)
4833: + NVL(p_util_plan_amount, 0);
4834: l_objfundsum_rec.univ_curr_earned_amt := NVL(l_objfundsum_rec.univ_curr_earned_amt, 0)