[Home] [Help]
6: G_WF_EVT_SUBSIDY_POOL_REJECTED CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.subsidy_pool.pool_rejected';
7: G_WF_ITM_SUB_POOL_ID CONSTANT VARCHAR2(30) := 'SUBSIDY_POOL_ID';
8:
9: -- Cursor to fetch the record with the passed id.
10: CURSOR c_get_pool_id (cp_pool_id IN okl_subsidy_pools_b.id%type) IS
11: SELECT id
12: ,object_version_number
13: ,sfwt_flag
14: ,pool_type_code
96: -- Start of comments
97: --
98: -- Procedure Name : create_sub_pool
99: -- Description : procedure for inserting the records in
100: -- table OKL_SUBSIDY_POOLS_B AND OKL_SUBSIDY_POOLS_TL
101: -- Business Rules : This procedure creates a subsidy pool with the status "new"
102: -- in the table OKL_SUBSIDY_POOLS_B.
103: -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
104: -- x_msg_data, p_sub_pool_rec, x_sub_pool_rec.
98: -- Procedure Name : create_sub_pool
99: -- Description : procedure for inserting the records in
100: -- table OKL_SUBSIDY_POOLS_B AND OKL_SUBSIDY_POOLS_TL
101: -- Business Rules : This procedure creates a subsidy pool with the status "new"
102: -- in the table OKL_SUBSIDY_POOLS_B.
103: -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
104: -- x_msg_data, p_sub_pool_rec, x_sub_pool_rec.
105: -- Version : 1.0
106: -- End of comments
121: L_DEBUG_ENABLED VARCHAR2(10);
122: L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
123: IS_DEBUG_PROCEDURE_ON BOOLEAN;
124: l_sub_pool_rec subsidy_pool_rec ;
125: l_effective_to_date OKL_SUBSIDY_POOLS_B.EFFECTIVE_TO_DATE%TYPE ;
126: l_total_budgets OKL_SUBSIDY_POOLS_B.TOTAL_BUDGETS%TYPE ;
127: l_system_date DATE ;
128: l_api_version NUMBER ;
129: l_init_msg_list VARCHAR2(1) ;
122: L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
123: IS_DEBUG_PROCEDURE_ON BOOLEAN;
124: l_sub_pool_rec subsidy_pool_rec ;
125: l_effective_to_date OKL_SUBSIDY_POOLS_B.EFFECTIVE_TO_DATE%TYPE ;
126: l_total_budgets OKL_SUBSIDY_POOLS_B.TOTAL_BUDGETS%TYPE ;
127: l_system_date DATE ;
128: l_api_version NUMBER ;
129: l_init_msg_list VARCHAR2(1) ;
130: l_return_status VARCHAR2(1);
186: l_total_budgets := 0;
187: END IF;
188: l_sub_pool_rec.total_budgets := l_total_budgets;
189:
190: -- TAPI call to create a record for subsidy pool in table OKL_SUBSIDY_POOLS_B.
191: okl_sip_pvt.insert_row( l_api_version,
192: l_init_msg_list,
193: l_return_status,
194: l_msg_count,
256: -- Start of comments
257: --
258: -- Procedure Name : update_sub_pool
259: -- Description : procedure for updating the records in
260: -- table OKL_SUBSIDY_POOLS_B AND OKL_SUBSIDY_POOLS_TL
261: -- Business Rules : Procedure to update the subsidy pool.
262: -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
263: -- x_msg_data, p_sub_pool_rec, x_sub_pool_rec.
264: -- Version : 1.0
280: L_DEBUG_ENABLED VARCHAR2(10);
281: L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
282: IS_DEBUG_PROCEDURE_ON BOOLEAN;
283: l_sub_pool_rec subsidy_pool_rec ;
284: l_effective_to_date OKL_SUBSIDY_POOLS_B.EFFECTIVE_TO_DATE%TYPE ;
285: l_db_effective_to OKL_SUBSIDY_POOLS_B.EFFECTIVE_TO_DATE%TYPE ;
286: l_trx_date OKL_TRX_SUBSIDY_POOLS.TRX_DATE%TYPE;
287: l_status_code OKL_SUBSIDY_POOLS_B.DECISION_STATUS_CODE%TYPE ;
288: l_system_date DATE;
281: L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
282: IS_DEBUG_PROCEDURE_ON BOOLEAN;
283: l_sub_pool_rec subsidy_pool_rec ;
284: l_effective_to_date OKL_SUBSIDY_POOLS_B.EFFECTIVE_TO_DATE%TYPE ;
285: l_db_effective_to OKL_SUBSIDY_POOLS_B.EFFECTIVE_TO_DATE%TYPE ;
286: l_trx_date OKL_TRX_SUBSIDY_POOLS.TRX_DATE%TYPE;
287: l_status_code OKL_SUBSIDY_POOLS_B.DECISION_STATUS_CODE%TYPE ;
288: l_system_date DATE;
289: l_api_version NUMBER ;
283: l_sub_pool_rec subsidy_pool_rec ;
284: l_effective_to_date OKL_SUBSIDY_POOLS_B.EFFECTIVE_TO_DATE%TYPE ;
285: l_db_effective_to OKL_SUBSIDY_POOLS_B.EFFECTIVE_TO_DATE%TYPE ;
286: l_trx_date OKL_TRX_SUBSIDY_POOLS.TRX_DATE%TYPE;
287: l_status_code OKL_SUBSIDY_POOLS_B.DECISION_STATUS_CODE%TYPE ;
288: l_system_date DATE;
289: l_api_version NUMBER ;
290: l_init_msg_list VARCHAR2(1) ;
291: l_return_status VARCHAR2(1);
292: l_msg_count NUMBER ;
293: l_msg_data VARCHAR2(2000);
294: l_api_name CONSTANT VARCHAR2(30) := 'update_sub_pool';
295: l_dummy VARCHAR2(1);
296: l_pool_name okl_subsidy_pools_b.subsidy_pool_name%TYPE ;
297: -------------------
298: -- DECLARE Cursors
299: -------------------
300: -- cursor to fetch the effective to date of the pool id passed.
297: -------------------
298: -- DECLARE Cursors
299: -------------------
300: -- cursor to fetch the effective to date of the pool id passed.
301: CURSOR c_get_to_date (cp_pool_id IN okl_subsidy_pools_b.id%type) IS
302: SELECT effective_to_date
303: FROM okl_subsidy_pools_v
304: WHERE id = cp_pool_id;
305:
303: FROM okl_subsidy_pools_v
304: WHERE id = cp_pool_id;
305:
306: -- Cursor to check whether the subsidies dates overlap with the subsidy pool dates.
307: CURSOR c_get_subsidy_date (cp_effective_to okl_subsidy_pools_b.effective_to_date%TYPE, cp_pool_id okl_subsidy_pools_B.id%TYPE) IS
308: SELECT 'X'
309: FROM okl_subsidy_pools_v pool, okl_subsidies_b sub
310: WHERE sub.subsidy_pool_id = pool.id
311: AND pool.id = cp_pool_id
314: OR (sub.effective_from_date BETWEEN pool.effective_from_date AND cp_effective_to))
315: );
316:
317: -- Cursor for fetching the transaction date for the subsidy pool.
318: CURSOR c_get_trx_date (cp_pool_id IN okl_subsidy_pools_b.id%type) IS
319: SELECT max(TRUNC(trx_date))
320: FROM okl_trx_subsidy_pools
321: WHERE subsidy_pool_id = cp_pool_id;
322:
423: CLOSE c_get_trx_date;
424: END IF;
425: END IF;
426:
427: -- TAPI call to update the subsidy pool in table OKL_SUBSIDY_POOLS_B.
428: okl_sip_pvt.update_row(l_api_version,
429: l_init_msg_list,
430: l_return_status,
431: l_msg_count,
492: -- Start of comments
493: --
494: -- Procedure Name : expire_sub_pool
495: -- Description : procedure for validating that if the records exist in the
496: -- table OKL_SUBSIDY_POOLS_B then set its status to expire.
497: -- Business Rules : This procedure sets the pool status to "expire"and this is
498: -- an autonomous transaction.
499: -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
500: -- x_msg_data, p_subsidy_pool_id.
506: p_init_msg_list IN VARCHAR2,
507: x_return_status OUT NOCOPY VARCHAR2,
508: x_msg_count OUT NOCOPY NUMBER,
509: x_msg_data OUT NOCOPY VARCHAR2,
510: p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE) IS
511: ---------------------------
512: -- DECLARE Local Variables
513: ---------------------------
514: L_MODULE CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_PVT.EXPIRE_SUB_POOL';
515: L_DEBUG_ENABLED VARCHAR2(10);
516: L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
517: IS_DEBUG_PROCEDURE_ON BOOLEAN;
518: l_sub_pool_rec subsidy_pool_rec;
519: l_subsidy_pool_id OKL_SUBSIDY_POOLS_B.ID%TYPE ;
520: x_sub_pool_rec subsidy_pool_rec;
521: l_row_found BOOLEAN ;
522: l_api_version NUMBER ;
523: l_init_msg_list VARCHAR2(1) ;
642: -- Start of comments
643: --
644: -- Procedure Name : update_total_budget
645: -- Description : procedure for updating the total budget amount
646: -- table OKL_SUBSIDY_POOLS_B.
647: -- Business Rules : As soon as any of the budget line attached to a subsisy pool gets
648: -- approved this procedure is called to update the total budgets of the pool
649: -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
650: -- x_msg_data, p_subsidy_pool_id,p_total_budget_amt.
656: p_init_msg_list IN VARCHAR2,
657: x_return_status OUT NOCOPY VARCHAR2,
658: x_msg_count OUT NOCOPY NUMBER,
659: x_msg_data OUT NOCOPY VARCHAR2,
660: p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE,
661: p_total_budget_amt IN okl_subsidy_pools_b.total_budgets%TYPE ) IS
662: ---------------------------
663: -- DECLARE Local Variables
664: ---------------------------
657: x_return_status OUT NOCOPY VARCHAR2,
658: x_msg_count OUT NOCOPY NUMBER,
659: x_msg_data OUT NOCOPY VARCHAR2,
660: p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE,
661: p_total_budget_amt IN okl_subsidy_pools_b.total_budgets%TYPE ) IS
662: ---------------------------
663: -- DECLARE Local Variables
664: ---------------------------
665: L_MODULE CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_SUBSIDY_POOL_PVT.UPDATE_TOTAL_BUDGET';
666: L_DEBUG_ENABLED VARCHAR2(10);
667: L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
668: IS_DEBUG_PROCEDURE_ON BOOLEAN;
669: l_sub_pool_rec subsidy_pool_rec;
670: l_subsidy_pool_id OKL_SUBSIDY_POOLS_B.ID%TYPE ;
671: x_sub_pool_rec subsidy_pool_rec;
672: l_row_found BOOLEAN ;
673: l_api_version NUMBER ;
674: l_init_msg_list VARCHAR2(1) ;
787: -- Start of comments
788: --
789: -- Procedure Name : update_subsidy_amount
790: -- Description : procedure for updating the total subsidy amount
791: -- table OKL_SUBSIDY_POOLS_B.
792: -- Business Rules : subsidy amount is updated when the contract is booked, rebooked or a
793: -- quote is created, or a contract is reversed.
794: -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
795: -- x_msg_data, p_subsidy_pool_id, p_total_subsidy_amt.
801: p_init_msg_list IN VARCHAR2,
802: x_return_status OUT NOCOPY VARCHAR2,
803: x_msg_count OUT NOCOPY NUMBER,
804: x_msg_data OUT NOCOPY VARCHAR2,
805: p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE,
806: p_total_subsidy_amt IN okl_subsidy_pools_b.total_subsidy_amount%TYPE) IS
807:
808: ---------------------------
809: -- DECLARE Local Variables
802: x_return_status OUT NOCOPY VARCHAR2,
803: x_msg_count OUT NOCOPY NUMBER,
804: x_msg_data OUT NOCOPY VARCHAR2,
805: p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE,
806: p_total_subsidy_amt IN okl_subsidy_pools_b.total_subsidy_amount%TYPE) IS
807:
808: ---------------------------
809: -- DECLARE Local Variables
810: ---------------------------
812: L_DEBUG_ENABLED VARCHAR2(10);
813: L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
814: IS_DEBUG_PROCEDURE_ON BOOLEAN;
815: l_sub_pool_rec subsidy_pool_rec;
816: l_subsidy_pool_id OKL_SUBSIDY_POOLS_B.ID%TYPE ;
817: x_sub_pool_rec subsidy_pool_rec;
818: l_row_found BOOLEAN ;
819: l_api_version NUMBER ;
820: l_init_msg_list VARCHAR2(1);
932: -- Start of comments
933: --
934: -- Procedure Name : set_decision_status_code
935: -- Description : procedure for updating the decision status code
936: -- table OKL_SUBSIDY_POOLS_B.
937: -- Business Rules : Procedure sets the decision_status_code to the value passed to this procedure.
938: -- this is a status of a pool.
939: -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
940: -- x_msg_data, p_subsidy_pool_id, p_total_subsidy_amt,p_decision_status_code.
946: p_init_msg_list IN VARCHAR2,
947: x_return_status OUT NOCOPY VARCHAR2,
948: x_msg_count OUT NOCOPY NUMBER,
949: x_msg_data OUT NOCOPY VARCHAR2,
950: p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE,
951: p_decision_status_code IN OUT NOCOPY okl_subsidy_pools_b.decision_status_code%TYPE) IS
952:
953: ---------------------------
954: -- DECLARE Local Variables
947: x_return_status OUT NOCOPY VARCHAR2,
948: x_msg_count OUT NOCOPY NUMBER,
949: x_msg_data OUT NOCOPY VARCHAR2,
950: p_subsidy_pool_id IN okl_subsidy_pools_b.id%TYPE,
951: p_decision_status_code IN OUT NOCOPY okl_subsidy_pools_b.decision_status_code%TYPE) IS
952:
953: ---------------------------
954: -- DECLARE Local Variables
955: ---------------------------
957: L_DEBUG_ENABLED VARCHAR2(10);
958: L_LEVEL_PROCEDURE fnd_log_messages.log_level%TYPE;
959: IS_DEBUG_PROCEDURE_ON BOOLEAN;
960: l_sub_pool_rec subsidy_pool_rec;
961: l_subsidy_pool_id OKL_SUBSIDY_POOLS_B.ID%TYPE ;
962: x_sub_pool_rec subsidy_pool_rec;
963: l_row_found BOOLEAN ;
964: l_api_version NUMBER ;
965: l_init_msg_list VARCHAR2(1) ;
1104: -- Start of comments
1105: --
1106: -- Procedure Name : validate_sub_pool
1107: -- Description : procedure for validating the records in
1108: -- table OKL_SUBSIDY_POOLS_B AND OKL_SUBSIDY_POOLS_TL
1109: -- Business Rules : Validates the record passed to it.
1110: -- Parameters : p_api_version, p_init_msg_list, x_return_status, x_msg_count,
1111: -- x_msg_data, p_sub_pool_rec.
1112: -- Version : 1.0