8: --
9:
10: FUNCTION get_entity_name
11: (
12: p_entity_id IN pqh_bdgt_pool_realloctions.entity_id%TYPE,
13: p_entity_type IN pqh_budget_pools.entity_type%TYPE
14: )
15: RETURN VARCHAR IS
16: /*
36: FUNCTION GET_PRD_REALLOC_RESERVED_AMT
37: (
38: p_budget_period_id IN pqh_budget_periods.budget_period_id%TYPE,
39: p_budget_unit_id IN pqh_budgets.budget_unit1_id%TYPE,
40: p_transaction_type IN pqh_bdgt_pool_realloctions.transaction_type%TYPE DEFAULT 'DD',
41: p_approval_status IN varchar2,
42: p_amount_type IN varchar2 ,
43: p_entity_type IN varchar2,
44: p_entity_id IN NUMBER,
70: CURSOR csr_donor_amt(p_approval_status IN Varchar2) IS
71: SELECT NVL(sum(trnxamt.reallocation_amt),0), NVL(sum(trnxamt.reserved_amt),0)
72: FROM pqh_budget_pools fld,
73: pqh_budget_pools trnx,
74: pqh_bdgt_pool_realloctions trnxdtl,
75: pqh_bdgt_pool_realloctions trnxamt
76: WHERE fld.pool_id = trnx.parent_pool_id
77: AND fld.approval_status=p_approval_status
78: AND fld.budget_unit_id = p_budget_unit_id
71: SELECT NVL(sum(trnxamt.reallocation_amt),0), NVL(sum(trnxamt.reserved_amt),0)
72: FROM pqh_budget_pools fld,
73: pqh_budget_pools trnx,
74: pqh_bdgt_pool_realloctions trnxdtl,
75: pqh_bdgt_pool_realloctions trnxamt
76: WHERE fld.pool_id = trnx.parent_pool_id
77: AND fld.approval_status=p_approval_status
78: AND fld.budget_unit_id = p_budget_unit_id
79: AND fld.entity_type = p_entity_type
86: CURSOR csr_receiver_amt(p_approval_status IN varchar2) IS
87: SELECT NVL(sum(trnxamt.reallocation_amt),0), NVL(sum(trnxamt.reserved_amt),0)
88: FROM pqh_budget_pools fld,
89: pqh_budget_pools trnx,
90: pqh_bdgt_pool_realloctions trnxdtl,
91: pqh_bdgt_pool_realloctions trnxamt
92: WHERE fld.pool_id = trnx.parent_pool_id
93: AND fld.budget_unit_id = p_budget_unit_id
94: AND fld.entity_type = p_entity_type
87: SELECT NVL(sum(trnxamt.reallocation_amt),0), NVL(sum(trnxamt.reserved_amt),0)
88: FROM pqh_budget_pools fld,
89: pqh_budget_pools trnx,
90: pqh_bdgt_pool_realloctions trnxdtl,
91: pqh_bdgt_pool_realloctions trnxamt
92: WHERE fld.pool_id = trnx.parent_pool_id
93: AND fld.budget_unit_id = p_budget_unit_id
94: AND fld.entity_type = p_entity_type
95: AND trnx.pool_id = trnxdtl.pool_id
143: FUNCTION GET_DTL_REALLOC_RESERVED_AMT
144: (
145: p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
146: p_budget_unit_id IN pqh_budgets.budget_unit1_id%TYPE,
147: p_transaction_type IN pqh_bdgt_pool_realloctions.transaction_type%TYPE DEFAULT 'DD',
148: p_approval_status IN varchar2,
149: p_amount_type IN varchar2,
150: p_entity_type IN varchar2,
151: p_entity_id IN number,
182: CURSOR csr_donor_amt(p_approval_status in varchar2) IS
183: SELECT NVL(SUM(trnxamt.reallocation_amt),0), NVL(SUM(trnxamt.reserved_amt),0)
184: FROM pqh_budget_pools fld,
185: pqh_budget_pools trnx,
186: pqh_bdgt_pool_realloctions trnxdtl,
187: pqh_bdgt_pool_realloctions trnxamt
188: WHERE fld.budget_unit_id = p_budget_unit_id
189: AND fld.entity_type = p_entity_type
190: AND fld.approval_status = p_approval_status
183: SELECT NVL(SUM(trnxamt.reallocation_amt),0), NVL(SUM(trnxamt.reserved_amt),0)
184: FROM pqh_budget_pools fld,
185: pqh_budget_pools trnx,
186: pqh_bdgt_pool_realloctions trnxdtl,
187: pqh_bdgt_pool_realloctions trnxamt
188: WHERE fld.budget_unit_id = p_budget_unit_id
189: AND fld.entity_type = p_entity_type
190: AND fld.approval_status = p_approval_status
191: AND fld.pool_id = trnx.parent_pool_id
197: CURSOR csr_receiver_amt(p_approval_status in varchar2) IS
198: SELECT NVL(SUM(trnxamt.reallocation_amt),0), NVL(SUM(trnxamt.reserved_amt),0)
199: FROM pqh_budget_pools fld,
200: pqh_budget_pools trnx,
201: pqh_bdgt_pool_realloctions trnxdtl,
202: pqh_bdgt_pool_realloctions trnxamt
203: WHERE fld.budget_unit_id = p_budget_unit_id
204: AND fld.entity_type = p_entity_type
205: AND fld.approval_status = p_approval_status
198: SELECT NVL(SUM(trnxamt.reallocation_amt),0), NVL(SUM(trnxamt.reserved_amt),0)
199: FROM pqh_budget_pools fld,
200: pqh_budget_pools trnx,
201: pqh_bdgt_pool_realloctions trnxdtl,
202: pqh_bdgt_pool_realloctions trnxamt
203: WHERE fld.budget_unit_id = p_budget_unit_id
204: AND fld.entity_type = p_entity_type
205: AND fld.approval_status = p_approval_status
206: AND fld.pool_id = trnx.parent_pool_id
252:
253:
254: FUNCTION GET_TRNX_LEVEL_TRANS_AMT
255: (
256: p_transaction_id IN pqh_bdgt_pool_realloctions.reallocation_id%TYPE,
257: p_txn_amt_balance_flag IN varchar2
258: ) RETURN NUMBER IS
259:
260: /*
273: */
274: CURSOR csr_tranaction_amt (p_transaction_type in varchar)
275: IS
276: Select nvl(sum(trnxamt.reallocation_amt),0)
277: From pqh_bdgt_pool_realloctions trnxamt,
278: pqh_bdgt_pool_realloctions trnxdtl
279: where trnxamt.txn_detail_id = trnxdtl.reallocation_id
280: and trnxdtl.pool_id= p_transaction_id
281: and trnxamt.transaction_type = p_transaction_type;
274: CURSOR csr_tranaction_amt (p_transaction_type in varchar)
275: IS
276: Select nvl(sum(trnxamt.reallocation_amt),0)
277: From pqh_bdgt_pool_realloctions trnxamt,
278: pqh_bdgt_pool_realloctions trnxdtl
279: where trnxamt.txn_detail_id = trnxdtl.reallocation_id
280: and trnxdtl.pool_id= p_transaction_id
281: and trnxamt.transaction_type = p_transaction_type;
282:
358: End GET_FOLDER_LEVEL_TRANS_AMT;
359:
360: FUNCTION GET_TRNX_DNR_REVR_COUNT
361: (
362: p_transaction_id IN pqh_bdgt_pool_realloctions.reallocation_id%TYPE,
363: p_transaction_type IN pqh_bdgt_pool_realloctions.transaction_type%TYPE
364: ) RETURN NUMBER IS
365:
366: /*
359:
360: FUNCTION GET_TRNX_DNR_REVR_COUNT
361: (
362: p_transaction_id IN pqh_bdgt_pool_realloctions.reallocation_id%TYPE,
363: p_transaction_type IN pqh_bdgt_pool_realloctions.transaction_type%TYPE
364: ) RETURN NUMBER IS
365:
366: /*
367: This function retuns the number of Donors/Receivers for a given Transaction
374:
375: CURSOR csr_dnr_revr_count
376: IS
377: Select count(*)
378: From pqh_bdgt_pool_realloctions
379: Where pool_id = p_transaction_id
380: And transaction_type = p_transaction_type;
381:
382: l_count number;
399: ---------------------------CHK_RECV_EXISTS-----------------------------
400: --
401: PROCEDURE CHK_RECV_EXISTS
402: (
403: p_trans_id IN pqh_bdgt_pool_realloctions.pool_id%TYPE,
404: p_entity_id IN pqh_bdgt_pool_realloctions.entity_id%TYPE,
405: p_detail_id OUT NOCOPY pqh_bdgt_pool_realloctions.reallocation_id%TYPE
406: )
407: /*
400: --
401: PROCEDURE CHK_RECV_EXISTS
402: (
403: p_trans_id IN pqh_bdgt_pool_realloctions.pool_id%TYPE,
404: p_entity_id IN pqh_bdgt_pool_realloctions.entity_id%TYPE,
405: p_detail_id OUT NOCOPY pqh_bdgt_pool_realloctions.reallocation_id%TYPE
406: )
407: /*
408: This function checks whether a receiver exists in a Transaction or not
401: PROCEDURE CHK_RECV_EXISTS
402: (
403: p_trans_id IN pqh_bdgt_pool_realloctions.pool_id%TYPE,
404: p_entity_id IN pqh_bdgt_pool_realloctions.entity_id%TYPE,
405: p_detail_id OUT NOCOPY pqh_bdgt_pool_realloctions.reallocation_id%TYPE
406: )
407: /*
408: This function checks whether a receiver exists in a Transaction or not
409: If it exists then it returns the reallocation_id else returns -1
483: ---------------------------GET_TRNX_LEVEL_RESERVED_AMT-----------------------------
484: --
485: FUNCTION GET_TRNX_LEVEL_RESERVED_AMT
486: (
487: p_transaction_id IN pqh_bdgt_pool_realloctions.reallocation_id%TYPE,
488: p_transaction_type IN pqh_bdgt_pool_realloctions.transaction_type%TYPE DEFAULT 'DD'
489: ) RETURN NUMBER IS
490:
491: /*
484: --
485: FUNCTION GET_TRNX_LEVEL_RESERVED_AMT
486: (
487: p_transaction_id IN pqh_bdgt_pool_realloctions.reallocation_id%TYPE,
488: p_transaction_type IN pqh_bdgt_pool_realloctions.transaction_type%TYPE DEFAULT 'DD'
489: ) RETURN NUMBER IS
490:
491: /*
492: This function returns Transaction/Reallocation Amount for a given Transation Id.
499: */
500: CURSOR csr_tranaction_amt
501: IS
502: Select nvl(sum(trnxamt.reserved_amt),0)
503: From pqh_bdgt_pool_realloctions trnxamt,
504: pqh_bdgt_pool_realloctions trnxdtl
505: where trnxamt.txn_detail_id = trnxdtl.reallocation_id
506: and trnxdtl.pool_id= p_transaction_id
507: and trnxamt.transaction_type = p_transaction_type;
500: CURSOR csr_tranaction_amt
501: IS
502: Select nvl(sum(trnxamt.reserved_amt),0)
503: From pqh_bdgt_pool_realloctions trnxamt,
504: pqh_bdgt_pool_realloctions trnxdtl
505: where trnxamt.txn_detail_id = trnxdtl.reallocation_id
506: and trnxdtl.pool_id= p_transaction_id
507: and trnxamt.transaction_type = p_transaction_type;
508: