[Home] [Help]
45: -- as NUMBER
46: -- 10-Jul-2002 musman Modified the procedure check_new_status_change, checking for
47: -- CAPL instead of TAPL if the object type is 'DELV'
48: -- 19-Dec-2002 mayjain Added get_install_info
49: -- 13-Oct-2003 yzhao get_qual_table_name_and_pk() for FUND, changed from ozf_fund_details_v to ozf_funds_all_b
50: -- Wed Oct 29 2003:3/44 PM RSSHARMA Added function get_party_name . Changed get_product_name to include FAMILY in Product level since the lookup
51: -- for product level for lumpsum , scandata and Net Accrual Offers has product level codes as PRODUCT AND FAMILY
52: -- Fri Jan 23 2004:3/20 PM RSSHARMA Added the following fuctions
53: -- 1. get_fnd_lookup_meaning
73: -- 10-May-2010 BKUNJAN Bug 9666046 - TST1213:UNABLE TO POST THE ENTRIES TOGL DUE TO ERROR IN CREATE ACCOUNTING CC JOB
74: -- 15-Sep-2010 BKUNJAN Reverting the changes made via bug 9666046
75: -- 05-01-2011 muthsubr For TPA Parallel Execution ER Bug#9614703
76: -- 19-01-2011 nirprasa Bug#9614703 - All funds and act_budget records will be inserted to staging tables.
77: -- These four tables ozf_funds_all_b, ozf_act_budgets, ozf_funds_all_tl and ozf_mc_transactions_all will be updated
78: -- via concurrent prgm 'OZF: Update Budgets from Interface Tables'.
79: -- 7/4/2012 nepanda Bug 13744476 - datafix: 700+ offers stuck in budget pending validation
80: -- 13-12-2012 BKUNJAN Bug 15952083 - ORACLE TRADE MANAGEMENT CREATE ACCOUNTING HAS ERROR - ERROR IN REVENUE ACCT CREA
81: ---------------------------------------------------------------------
752: ELSIF (p_sys_qual ='FCST') THEN --added tdonohoe 04/24/2000
753: x_table_name := 'OZF_ACT_FORECASTS_ALL';
754: x_pk_name := 'FORECAST_ID';
755: ELSIF (p_sys_qual ='FUND') THEN --added rchahal 06/28/2000
756: -- yzhao 10/13/2003 fix bug 3167497 changed from OZF_FUND_DETAILS_V to ozf_funds_all_b
757: x_table_name := 'OZF_FUNDS_ALL_B'; -- yzhao: 'OZF_FUND_DETAILS_V';
758: x_pk_name := 'FUND_ID';
759: ELSIF (p_sys_qual ='PRIC') THEN --added skarumur 12/17/2000
760: x_table_name := 'AMS_PRICE_LIST_ATTRIBUTES';
753: x_table_name := 'OZF_ACT_FORECASTS_ALL';
754: x_pk_name := 'FORECAST_ID';
755: ELSIF (p_sys_qual ='FUND') THEN --added rchahal 06/28/2000
756: -- yzhao 10/13/2003 fix bug 3167497 changed from OZF_FUND_DETAILS_V to ozf_funds_all_b
757: x_table_name := 'OZF_FUNDS_ALL_B'; -- yzhao: 'OZF_FUND_DETAILS_V';
758: x_pk_name := 'FUND_ID';
759: ELSIF (p_sys_qual ='PRIC') THEN --added skarumur 12/17/2000
760: x_table_name := 'AMS_PRICE_LIST_ATTRIBUTES';
761: x_pk_name := 'QP_LIST_HEADER_ID';
2355: l_ledger_name VARCHAR2(30) := NULL;
2356:
2357: CURSOR c_fund_ledger IS
2358: SELECT ledger_id
2359: FROM ozf_funds_all_b
2360: WHERE fund_id = p_fund_id;
2361:
2362: BEGIN
2363: x_ledger_id := NULL;
3879:
3880:
3881: ---------------------------------------------------------------------
3882: -- PROCEDURE
3883: -- UPDATE_OZF_FUNDS_ALL_B
3884: --
3885: -- PURPOSE
3886: -- This procedure for updating fund main tables in one shot with values
3887: -- from new record type px_ozf_funds_new_tbl for chargeback & TPA flow only.
3891: -- NOTES
3892: -- MUTHSUBR FOR BUG#8867381
3893: -- MUTHSUBR For TPA Parallel Execution ER Bug#9614703
3894: ---------------------------------------------------------------------
3895: PROCEDURE UPDATE_OZF_FUNDS_ALL_B (
3896: p_batch_id IN NUMBER,
3897: x_return_status OUT NOCOPY VARCHAR2,
3898: x_fund_update_status OUT NOCOPY VARCHAR2,
3899: px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table,
3899: px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table,
3900: p_batch_type IN VARCHAR2 -- For JMS ER
3901: )
3902: IS
3903: l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_OZF_FUNDS_ALL_B';
3904: l_api_version_number CONSTANT NUMBER := 1.0;
3905: l_api_version CONSTANT NUMBER := 1.0;
3906: l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
3907: x_msg_count NUMBER;
3977: ROLLUP_EARNED_AMT,
3978: ROLLUP_PAID_AMT,
3979: UTILIZED_AMT,
3980: ROLLUP_UTILIZED_AMT
3981: FROM OZF_FUNDS_ALL_B
3982: WHERE FUND_ID = p_fund_id;*/
3983:
3984: BEGIN
3985:
3982: WHERE FUND_ID = p_fund_id;*/
3983:
3984: BEGIN
3985:
3986: SAVEPOINT UPDATE_OZF_FUNDS_ALL_B;
3987:
3988: IF G_DEBUG THEN
3989: OZF_UTILITY_PVT.debug_message(l_api_name||': Start');
3990: OZF_UTILITY_PVT.debug_message('px_ozf_funds_new_tbl.COUNT: ' || px_ozf_funds_new_tbl.COUNT);
4008: OZF_UTILITY_PVT.debug_message('Earned Amount: ' || px_ozf_funds_new_tbl(i).EARNED_AMT);
4009: END IF;
4010:
4011: /*Bug#9614703
4012: All fund records will get inserted into OZF_FUNDS_ALL_B_INT table and processed to
4013: main table via concurrent prgm 'OZF: Update Budgets from Interface Tables'*/
4014:
4015: /*OPEN c_mc_record(l_fund_id);
4016: FETCH c_mc_record INTO l_mc_record_id, l_mc_obj_number;
4027: l_rollup_earned_amt,
4028: l_rollup_paid_amt, l_utilized_amt, l_rollup_utilized_amt;
4029: CLOSE funds_rollup_csr;
4030:
4031: -- Updating the main table OZF_FUNDS_ALL_B using the new record type for chargeback flow only
4032:
4033: IF G_DEBUG THEN
4034: OZF_UTILITY_PVT.debug_message('updating OZF_FUNDS_ALL_B');
4035: END IF;
4030:
4031: -- Updating the main table OZF_FUNDS_ALL_B using the new record type for chargeback flow only
4032:
4033: IF G_DEBUG THEN
4034: OZF_UTILITY_PVT.debug_message('updating OZF_FUNDS_ALL_B');
4035: END IF;
4036:
4037: UPDATE OZF_FUNDS_ALL_B
4038: SET
4033: IF G_DEBUG THEN
4034: OZF_UTILITY_PVT.debug_message('updating OZF_FUNDS_ALL_B');
4035: END IF;
4036:
4037: UPDATE OZF_FUNDS_ALL_B
4038: SET
4039: BUDGET_AMOUNT_TC = NVL(BUDGET_AMOUNT_TC,0) + NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_TC,0),
4040: BUDGET_AMOUNT_FC = NVL(BUDGET_AMOUNT_FC,0) + NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_FC,0),
4041: AVAILABLE_AMOUNT = NVL(AVAILABLE_AMOUNT,0) + NVL(px_ozf_funds_new_tbl(i).AVAILABLE_AMOUNT,0),
4108: l_fund_rec.rollup_paid_amt := NVL(l_rollup_paid_amt,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_PAID_AMT,0);
4109: l_fund_rec.rollup_utilized_amt := NVL(l_rollup_utilized_amt,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_UTILIZED_AMT,0);
4110:
4111: IF G_DEBUG THEN
4112: OZF_UTILITY_PVT.debug_message('Updating update_rollup_amount from UPDATE_OZF_FUNDS_ALL_B for batch_id='||p_batch_id);
4113: END IF;
4114:
4115: ozf_funds_pvt.update_rollup_amount(
4116: p_api_version => l_api_version
4147: l_mc_transaction_rec.amount_column9 := NVL(l_utilized_amt,0) + NVL(px_ozf_funds_new_tbl(i).UTILIZED_AMT,0);
4148:
4149:
4150: IF G_DEBUG THEN
4151: OZF_UTILITY_PVT.debug_message('Updating update_mc_transactions from UPDATE_OZF_FUNDS_ALL_B for batch_id'||p_batch_id);
4152: END IF;
4153:
4154: ozf_mc_transactions_pvt.update_mc_transactions(
4155: p_api_version => l_api_version
4171: */
4172:
4173: insrt_count := insrt_count + 1;
4174:
4175: INSERT INTO OZF_FUNDS_ALL_B_INT
4176: (
4177: RESALE_BATCH_ID,
4178: BATCH_TYPE,
4179: REQUEST_ID,
4241: -- END IF;
4242: END LOOP; -- End of (px_ozf_funds_new_tbl loop)
4243:
4244: IF G_DEBUG THEN
4245: ozf_utility_pvt.write_conc_log('insrt_count of OZF_FUNDS_ALL_B_INT: ' || insrt_count);
4246: END IF;
4247:
4248:
4249: --IF insrt_count > 0 THEN
4274: p_count => x_msg_count,
4275: p_data => x_msg_data
4276: );
4277: WHEN FND_API.g_exc_error THEN
4278: ROLLBACK TO UPDATE_OZF_FUNDS_ALL_B;
4279: x_return_status := FND_API.G_RET_STS_ERROR;
4280: FND_MSG_PUB.Count_And_Get (
4281: p_encoded => FND_API.G_FALSE,
4282: p_count => x_msg_count,
4282: p_count => x_msg_count,
4283: p_data => x_msg_data
4284: );
4285: WHEN FND_API.g_exc_unexpected_error THEN
4286: ROLLBACK TO UPDATE_OZF_FUNDS_ALL_B;
4287: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4288: FND_MSG_PUB.Count_And_Get (
4289: p_encoded => FND_API.G_FALSE,
4290: p_count => x_msg_count,
4290: p_count => x_msg_count,
4291: p_data => x_msg_data
4292: );
4293: WHEN OTHERS THEN
4294: ROLLBACK TO UPDATE_OZF_FUNDS_ALL_B;
4295: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4296: IF OZF_UNEXP_ERROR THEN
4297: FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4298: END IF;
4301: p_count => x_msg_count,
4302: p_data => x_msg_data
4303: );
4304:
4305: END UPDATE_OZF_FUNDS_ALL_B;
4306:
4307: ---------------------------------------------------------------------
4308: -- FUNCTION
4309: -- convert_currency