eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OKL_SUBSIDY_POOL_RPT_PVT dependencies on OKL_SUBSIDY_POOLS_B

Line 637: FUNCTION get_parent_record ( p_parent_id IN okl_subsidy_pools_b.id%TYPE )

633: -- Version : 1.0
634: -- History : 08-Mar-2005 ABINDAL created.
635: -- End of comments
636: ---------------------------------------------------------------------------
637: FUNCTION get_parent_record ( p_parent_id IN okl_subsidy_pools_b.id%TYPE )
638:
639: RETURN okl_sub_pool_rec
640:
641: IS

Line 643: CURSOR c_parent_summary(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS

639: RETURN okl_sub_pool_rec
640:
641: IS
642:
643: CURSOR c_parent_summary(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
644: SELECT id,
645: subsidy_pool_name,
646: pool_type_code,
647: currency_code,

Line 651: FROM okl_subsidy_pools_b

647: currency_code,
648: currency_conversion_type,
649: reporting_pool_limit,
650: effective_from_date
651: FROM okl_subsidy_pools_b
652: WHERE id = cp_pool_id
653: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
654: else
655: (case when exists (select 'x'

Line 657: where subsidy_pool_id = okl_subsidy_pools_b.id

653: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
654: else
655: (case when exists (select 'x'
656: from okl_subsidies_v
657: where subsidy_pool_id = okl_subsidy_pools_b.id
658: and org_id <> mo_global.get_current_org_id()) then 0
659: else 1 end)
660: end));
661:

Line 700: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS

696: IS
697:
698: -- Cursor fetches all the records, which are children of a given pool till the pool,
699: -- does not have any more children.
700: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
701: SELECT id ,
702: currency_code,
703: currency_conversion_type
704: FROM okl_subsidy_pools_b pool

Line 704: FROM okl_subsidy_pools_b pool

700: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
701: SELECT id ,
702: currency_code,
703: currency_conversion_type
704: FROM okl_subsidy_pools_b pool
705: WHERE pool_type_code = 'BUDGET'
706: CONNECT BY PRIOR id = subsidy_pool_id
707: START WITH id = cp_pool_id;
708:

Line 709: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;

705: WHERE pool_type_code = 'BUDGET'
706: CONNECT BY PRIOR id = subsidy_pool_id
707: START WITH id = cp_pool_id;
708:
709: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
710: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
711: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
712: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
713: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;

Line 710: l_budget okl_subsidy_pools_b.total_budgets%TYPE;

706: CONNECT BY PRIOR id = subsidy_pool_id
707: START WITH id = cp_pool_id;
708:
709: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
710: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
711: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
712: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
713: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
714: l_conv_rate NUMBER;

Line 713: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;

709: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
710: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
711: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
712: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
713: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
714: l_conv_rate NUMBER;
715: l_Pool_Name_len CONSTANT NUMBER DEFAULT 30;
716: l_Pool_Type_len CONSTANT NUMBER DEFAULT 30;
717: l_Currency_Code_len CONSTANT NUMBER DEFAULT 15;

Line 722: l_reporting_limit okl_subsidy_pools_b.reporting_pool_limit%TYPE ;

718: l_Pool_Limit_len CONSTANT NUMBER DEFAULT 20;
719: l_Budget_len CONSTANT NUMBER DEFAULT 20;
720: l_Remaining_Balance_len CONSTANT NUMBER DEFAULT 20;
721: l_total_length CONSTANT NUMBER DEFAULT 152;
722: l_reporting_limit okl_subsidy_pools_b.reporting_pool_limit%TYPE ;
723: l_api_name CONSTANT VARCHAR2(30) := 'print_parent_record';
724: l_msg_count NUMBER ;
725: l_msg_data VARCHAR2(2000);
726: l_return_status VARCHAR2(1);

Line 954: FUNCTION get_child_record ( p_pool_id IN okl_subsidy_pools_b.id%TYPE )

950: -- Version : 1.0
951: -- History : 08-Mar-2005 ABINDAL created.
952: -- End of comments
953: ---------------------------------------------------------------------------
954: FUNCTION get_child_record ( p_pool_id IN okl_subsidy_pools_b.id%TYPE )
955:
956: RETURN subsidy_pool_tbl_type
957:
958: IS

Line 969: FROM okl_subsidy_pools_b

965: currency_code,
966: currency_conversion_type,
967: reporting_pool_limit,
968: effective_from_date
969: FROM okl_subsidy_pools_b
970: WHERE subsidy_pool_id = cp_pool_id
971: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
972: else
973: (case when exists (select 'x'

Line 975: where subsidy_pool_id = okl_subsidy_pools_b.id

971: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
972: else
973: (case when exists (select 'x'
974: from okl_subsidies_v
975: where subsidy_pool_id = okl_subsidy_pools_b.id
976: and org_id <> mo_global.get_current_org_id()) then 0
977: else 1 end)
978: end))
979: ORDER BY subsidy_pool_name;

Line 1020: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS

1016: x_msg_count OUT NOCOPY NUMBER ,
1017: x_msg_data OUT NOCOPY VARCHAR2 )
1018: IS
1019:
1020: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
1021: SELECT id ,
1022: currency_code,
1023: currency_conversion_type
1024: FROM okl_subsidy_pools_b pool

Line 1024: FROM okl_subsidy_pools_b pool

1020: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
1021: SELECT id ,
1022: currency_code,
1023: currency_conversion_type
1024: FROM okl_subsidy_pools_b pool
1025: WHERE pool_type_code = 'BUDGET'
1026: CONNECT BY PRIOR id = subsidy_pool_id
1027: START WITH id = cp_pool_id;
1028:

Line 1029: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;

1025: WHERE pool_type_code = 'BUDGET'
1026: CONNECT BY PRIOR id = subsidy_pool_id
1027: START WITH id = cp_pool_id;
1028:
1029: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
1030: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE;
1031: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
1032: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE;
1033: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;

Line 1031: l_budget okl_subsidy_pools_b.total_budgets%TYPE;

1027: START WITH id = cp_pool_id;
1028:
1029: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
1030: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE;
1031: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
1032: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE;
1033: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
1034: l_conv_rate NUMBER;
1035: l_Pool_Name_len CONSTANT NUMBER DEFAULT 30;

Line 1033: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;

1029: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
1030: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE;
1031: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
1032: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE;
1033: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
1034: l_conv_rate NUMBER;
1035: l_Pool_Name_len CONSTANT NUMBER DEFAULT 30;
1036: l_Pool_Type_len CONSTANT NUMBER DEFAULT 30;
1037: l_Currency_Code_len CONSTANT NUMBER DEFAULT 15;

Line 1043: l_reporting_limit okl_subsidy_pools_b.reporting_pool_limit%TYPE DEFAULT NULL;

1039: l_Budget_len CONSTANT NUMBER DEFAULT 20;
1040: l_Remaining_Balance_len CONSTANT NUMBER DEFAULT 20;
1041: l_total_length CONSTANT NUMBER DEFAULT 152;
1042: i NUMBER;
1043: l_reporting_limit okl_subsidy_pools_b.reporting_pool_limit%TYPE DEFAULT NULL;
1044: l_api_name CONSTANT VARCHAR2(30) := 'print_child_record';
1045: l_msg_count NUMBER;
1046: l_msg_data VARCHAR2(2000);
1047: l_return_status VARCHAR2(1);

Line 1281: p_pool_id IN okl_subsidy_pools_b.id%TYPE,

1277: -- End of comments
1278: -------------------------------------------------------------------------------
1279: PROCEDURE POOL_ASSOC_REPORT(x_errbuf OUT NOCOPY VARCHAR2,
1280: x_retcode OUT NOCOPY NUMBER,
1281: p_pool_id IN okl_subsidy_pools_b.id%TYPE,
1282: p_date IN VARCHAR2)
1283:
1284: IS
1285:

Line 1295: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE ;

1291: j NUMBER ;
1292: k NUMBER ;
1293: l_count NUMBER ;
1294: l_pool_rec okl_sub_pool_rec;
1295: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE ;
1296: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
1297: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
1298: l_api_name CONSTANT VARCHAR2(30) := 'POOL_ASSOC_REPORT';
1299: l_msg_count NUMBER;

Line 1297: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;

1293: l_count NUMBER ;
1294: l_pool_rec okl_sub_pool_rec;
1295: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE ;
1296: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
1297: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
1298: l_api_name CONSTANT VARCHAR2(30) := 'POOL_ASSOC_REPORT';
1299: l_msg_count NUMBER;
1300: l_msg_data VARCHAR2(2000);
1301: l_return_status VARCHAR2(1);

Line 1539: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS

1535: x_msg_data OUT NOCOPY VARCHAR2 )
1536: IS
1537:
1538: -- Cursor to fetch all the children pools of a subsidy pool entered.
1539: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
1540: SELECT id ,
1541: currency_code
1542: FROM okl_subsidy_pools_v pool
1543: WHERE pool_type_code = 'BUDGET'

Line 1547: l_budget okl_subsidy_pools_b.total_budgets%TYPE;

1543: WHERE pool_type_code = 'BUDGET'
1544: CONNECT BY PRIOR id = subsidy_pool_id
1545: START WITH id = cp_pool_id;
1546:
1547: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
1548: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
1549: l_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
1550: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
1551: l_Pool_Name_len CONSTANT NUMBER DEFAULT 30;

Line 1550: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;

1546:
1547: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
1548: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
1549: l_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
1550: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
1551: l_Pool_Name_len CONSTANT NUMBER DEFAULT 30;
1552: l_Pool_Type_len CONSTANT NUMBER DEFAULT 30;
1553: l_Currency_Code_len CONSTANT NUMBER DEFAULT 15;
1554: l_Pool_Limit_len CONSTANT NUMBER DEFAULT 20;

Line 1602: -- from the okl_subsidy_pools_b table and calculate the total transaction amount

1598: RAISE OKL_API.G_EXCEPTION_ERROR;
1599: END IF;
1600:
1601: -- If subsidy pool type is "Budget" then pick the values for total budgets
1602: -- from the okl_subsidy_pools_b table and calculate the total transaction amount
1603: -- from the okl_trx_subsidy_pools table.
1604: IF (p_pool_rec.pool_type_code = 'BUDGET') THEN
1605: -- Parent pool header with the parent pool name.
1606: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_total_length, ' ' ));

Line 1780: PROCEDURE print_transaction_summary (p_pool_id IN okl_subsidy_pools_b.id%TYPE,

1776: -- Version : 1.0
1777: -- History : 08-Mar-2005 ABINDAL created.
1778: -- End of comments
1779: ---------------------------------------------------------------------------
1780: PROCEDURE print_transaction_summary (p_pool_id IN okl_subsidy_pools_b.id%TYPE,
1781: p_from_date IN DATE,
1782: p_to_date IN DATE,
1783: p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,
1784: p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,

Line 1783: p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,

1779: ---------------------------------------------------------------------------
1780: PROCEDURE print_transaction_summary (p_pool_id IN okl_subsidy_pools_b.id%TYPE,
1781: p_from_date IN DATE,
1782: p_to_date IN DATE,
1783: p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,
1784: p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,
1785: p_conv_type IN okl_subsidy_pools_b.currency_conversion_type%TYPE,
1786: x_return_status OUT NOCOPY VARCHAR2,
1787: x_msg_count OUT NOCOPY NUMBER ,

Line 1784: p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,

1780: PROCEDURE print_transaction_summary (p_pool_id IN okl_subsidy_pools_b.id%TYPE,
1781: p_from_date IN DATE,
1782: p_to_date IN DATE,
1783: p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,
1784: p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,
1785: p_conv_type IN okl_subsidy_pools_b.currency_conversion_type%TYPE,
1786: x_return_status OUT NOCOPY VARCHAR2,
1787: x_msg_count OUT NOCOPY NUMBER ,
1788: x_msg_data OUT NOCOPY VARCHAR2 )

Line 1785: p_conv_type IN okl_subsidy_pools_b.currency_conversion_type%TYPE,

1781: p_from_date IN DATE,
1782: p_to_date IN DATE,
1783: p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,
1784: p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,
1785: p_conv_type IN okl_subsidy_pools_b.currency_conversion_type%TYPE,
1786: x_return_status OUT NOCOPY VARCHAR2,
1787: x_msg_count OUT NOCOPY NUMBER ,
1788: x_msg_data OUT NOCOPY VARCHAR2 )
1789: IS

Line 1792: CURSOR c_transaction_detail(cp_pool_id okl_subsidy_pools_b.id%TYPE, cp_from_date DATE, cp_to_date DATE) IS

1788: x_msg_data OUT NOCOPY VARCHAR2 )
1789: IS
1790:
1791: -- cursor to fetch all the transactions details for the subsidy pool between the dates entered by user.
1792: CURSOR c_transaction_detail(cp_pool_id okl_subsidy_pools_b.id%TYPE, cp_from_date DATE, cp_to_date DATE) IS
1793: SELECT flk1.meaning trx_reason,
1794: --START:14-DEC-05 cklee - Fixed bug#4884558 |
1795: -- khr.contract_number,
1796: (case

Line 1851: FROM okl_subsidy_pools_b

1847: AND sub.id = pool.subsidy_id
1848: -- AND TRUNC(source_trx_date) >= NVL(TRUNC(cp_from_date),TRUNC(source_trx_date))
1849: -- AND TRUNC(source_trx_date) <= NVL(TRUNC(cp_to_date), TRUNC(source_trx_date))
1850: AND pool.subsidy_pool_id IN ( SELECT id
1851: FROM okl_subsidy_pools_b
1852: WHERE pool_type_code = 'BUDGET'
1853: CONNECT BY PRIOR id = subsidy_pool_id
1854: START WITH id = cp_pool_id
1855: )

Line 2131: p_pool_id IN okl_subsidy_pools_b.id%TYPE,

2127: -------------------------------------------------------------------------------
2128:
2129: PROCEDURE POOL_RECONC_REPORT(x_errbuf OUT NOCOPY VARCHAR2,
2130: x_retcode OUT NOCOPY NUMBER,
2131: p_pool_id IN okl_subsidy_pools_b.id%TYPE,
2132: p_from_date IN VARCHAR2,
2133: p_to_date IN VARCHAR2)
2134: IS
2135:

Line 2307: p_currency IN okl_subsidy_pools_b.currency_code%TYPE,

2303: -- End of comments
2304: ---------------------------------------------------------------------------
2305: PROCEDURE print_atlimit_detail (p_percent IN NUMBER,
2306: p_remaining IN NUMBER,
2307: p_currency IN okl_subsidy_pools_b.currency_code%TYPE,
2308: p_date IN DATE,
2309: p_days IN NUMBER,
2310: x_return_status OUT NOCOPY VARCHAR2,
2311: x_msg_count OUT NOCOPY NUMBER ,

Line 2327: FROM okl_subsidy_pools_b

2323: case when NVL(total_budgets,0) = 0 then 0
2324: else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
2325: effective_to_date,
2326: decision_status_code
2327: FROM okl_subsidy_pools_b
2328: WHERE pool_type_code = 'BUDGET'
2329: AND decision_status_code = 'ACTIVE'
2330: AND CASE WHEN NVL(total_budgets,0) = 0 THEN 0
2331: ELSE ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets END <= cp_percent

Line 2336: where subsidy_pool_id = okl_subsidy_pools_b.id

2332: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
2333: else
2334: (case when exists (select 'x'
2335: from okl_subsidies_v
2336: where subsidy_pool_id = okl_subsidy_pools_b.id
2337: and org_id <> mo_global.get_current_org_id()) then 0
2338: else 1 end)
2339: end));
2340:

Line 2343: CURSOR c_get_budget(cp_remaining NUMBER, cp_currency okl_subsidy_pools_b.currency_code%TYPE) IS

2339: end));
2340:
2341: -- Cursor to fetch all the subsidy pools whose reamining budget is
2342: -- less than or equal to the specified remaining budget.
2343: CURSOR c_get_budget(cp_remaining NUMBER, cp_currency okl_subsidy_pools_b.currency_code%TYPE) IS
2344: SELECT id,
2345: subsidy_pool_name,
2346: currency_code,
2347: total_budgets,

Line 2353: FROM okl_subsidy_pools_b

2349: case when NVL(total_budgets,0) = 0 then 0
2350: else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
2351: effective_to_date,
2352: decision_status_code
2353: FROM okl_subsidy_pools_b
2354: WHERE pool_type_code = 'BUDGET'
2355: AND decision_status_code = 'ACTIVE'
2356: AND NVL(total_budgets - NVL(total_subsidy_amount,0),0) <= cp_remaining
2357: AND currency_code = cp_currency

Line 2362: where subsidy_pool_id = okl_subsidy_pools_b.id

2358: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
2359: else
2360: (case when exists (select 'x'
2361: from okl_subsidies_v
2362: where subsidy_pool_id = okl_subsidy_pools_b.id
2363: and org_id <> mo_global.get_current_org_id()) then 0
2364: else 1 end)
2365: end));
2366:

Line 2378: FROM okl_subsidy_pools_b

2374: NVL(total_budgets - NVL(total_subsidy_amount,0),0) remaining_balance,
2375: case when NVL(total_budgets,0) = 0 then 0
2376: else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
2377: effective_to_date
2378: FROM okl_subsidy_pools_b
2379: WHERE pool_type_code = 'BUDGET'
2380: AND decision_status_code = 'ACTIVE'
2381: AND TRUNC(effective_to_date) >= TRUNC(SYSDATE)
2382: AND TRUNC(effective_to_date) <= TRUNC(cp_date)

Line 2387: where subsidy_pool_id = okl_subsidy_pools_b.id

2383: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
2384: else
2385: (case when exists (select 'x'
2386: from okl_subsidies_v
2387: where subsidy_pool_id = okl_subsidy_pools_b.id
2388: and org_id <> mo_global.get_current_org_id()) then 0
2389: else 1 end)
2390: end));
2391:

Line 2404: FROM okl_subsidy_pools_b

2400: case when NVL(total_budgets,0) = 0 then 0
2401: else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
2402: effective_to_date,
2403: trunc(effective_to_date) - trunc(sysdate) remaining_days
2404: FROM okl_subsidy_pools_b
2405: WHERE pool_type_code = 'BUDGET'
2406: AND decision_status_code = 'ACTIVE'
2407: AND trunc(effective_to_date) - trunc(sysdate) between 0 and cp_days
2408: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1

Line 2412: where subsidy_pool_id = okl_subsidy_pools_b.id

2408: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
2409: else
2410: (case when exists (select 'x'
2411: from okl_subsidies_v
2412: where subsidy_pool_id = okl_subsidy_pools_b.id
2413: and org_id <> mo_global.get_current_org_id()) then 0
2414: else 1 end)
2415: end));
2416:

Line 2437: l_decision_status_code okl_subsidy_pools_b.decision_status_code%TYPE;

2433: l_return_status VARCHAR2(1);
2434: l_msg_count NUMBER;
2435: l_msg_data VARCHAR2(2000);
2436: l_api_name CONSTANT VARCHAR2(30) := 'print_atlimit_detail';
2437: l_decision_status_code okl_subsidy_pools_b.decision_status_code%TYPE;
2438:
2439: BEGIN
2440:
2441: l_debug_enabled := okl_debug_pub.check_log_enabled;

Line 2687: p_currency IN okl_subsidy_pools_b.currency_code%TYPE,

2683: PROCEDURE POOL_ATLIMIT_REPORT(x_errbuf OUT NOCOPY VARCHAR2,
2684: x_retcode OUT NOCOPY NUMBER,
2685: p_percent IN NUMBER,
2686: p_remaining IN NUMBER,
2687: p_currency IN okl_subsidy_pools_b.currency_code%TYPE,
2688: p_end_date IN VARCHAR2,
2689: p_days IN NUMBER )
2690: IS
2691:

Line 2870: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS

2866: IS
2867:
2868: -- Cursor fetches all the records, which are children of a given pool till the pool,
2869: -- does not have any more children.
2870: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
2871: SELECT id ,
2872: currency_code,
2873: currency_conversion_type
2874: FROM okl_subsidy_pools_b pool

Line 2874: FROM okl_subsidy_pools_b pool

2870: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
2871: SELECT id ,
2872: currency_code,
2873: currency_conversion_type
2874: FROM okl_subsidy_pools_b pool
2875: WHERE pool_type_code = 'BUDGET'
2876: CONNECT BY PRIOR id = subsidy_pool_id
2877: START WITH id = cp_pool_id;
2878:

Line 2882: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;

2878:
2879: x_errbuf VARCHAR2(2000);
2880: x_retcode NUMBER;
2881: x_pool_dtl_rec pool_dtl_rec_type;
2882: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
2883: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
2884: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
2885: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
2886: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;

Line 2883: l_budget okl_subsidy_pools_b.total_budgets%TYPE;

2879: x_errbuf VARCHAR2(2000);
2880: x_retcode NUMBER;
2881: x_pool_dtl_rec pool_dtl_rec_type;
2882: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
2883: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
2884: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
2885: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
2886: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
2887: l_conv_rate NUMBER;

Line 2886: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;

2882: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE;
2883: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
2884: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
2885: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
2886: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
2887: l_conv_rate NUMBER;
2888: l_reporting_limit okl_subsidy_pools_b.reporting_pool_limit%TYPE ;
2889: l_api_name CONSTANT VARCHAR2(30) := 'GET_POOL_AMOUNTS';
2890: l_msg_count NUMBER ;

Line 2888: l_reporting_limit okl_subsidy_pools_b.reporting_pool_limit%TYPE ;

2884: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
2885: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
2886: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
2887: l_conv_rate NUMBER;
2888: l_reporting_limit okl_subsidy_pools_b.reporting_pool_limit%TYPE ;
2889: l_api_name CONSTANT VARCHAR2(30) := 'GET_POOL_AMOUNTS';
2890: l_msg_count NUMBER ;
2891: l_msg_data VARCHAR2(2000);
2892: l_return_status VARCHAR2(1);

Line 3080: CURSOR get_subsidy_pools(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS

3076: -------------------------------------------------------------------------------
3077: FUNCTION XML_POOL_ASSOC_REPORT RETURN BOOLEAN
3078: IS
3079:
3080: CURSOR get_subsidy_pools(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
3081: SELECT nvl(parent.id,chld.id) parent_id,
3082: nvl(parent.subsidy_pool_name,chld.subsidy_pool_name) parent_subsidy_pool_name,
3083: LEVEL,
3084: decode(parent.id,NULL,parent.id,chld.id) chld_id,

Line 3086: FROM okl_subsidy_pools_b parent,

3082: nvl(parent.subsidy_pool_name,chld.subsidy_pool_name) parent_subsidy_pool_name,
3083: LEVEL,
3084: decode(parent.id,NULL,parent.id,chld.id) chld_id,
3085: decode(parent.subsidy_pool_name,NULL,parent.subsidy_pool_name,chld.subsidy_pool_name) chld_subsidy_pool_name
3086: FROM okl_subsidy_pools_b parent,
3087: okl_subsidy_pools_b chld
3088: WHERE chld.pool_type_code IN('BUDGET', 'REPORTING')
3089: AND parent.id(+) = chld.subsidy_pool_id
3090: CONNECT BY PRIOR chld.id = chld.subsidy_pool_id START WITH chld.id = cp_pool_id

Line 3087: okl_subsidy_pools_b chld

3083: LEVEL,
3084: decode(parent.id,NULL,parent.id,chld.id) chld_id,
3085: decode(parent.subsidy_pool_name,NULL,parent.subsidy_pool_name,chld.subsidy_pool_name) chld_subsidy_pool_name
3086: FROM okl_subsidy_pools_b parent,
3087: okl_subsidy_pools_b chld
3088: WHERE chld.pool_type_code IN('BUDGET', 'REPORTING')
3089: AND parent.id(+) = chld.subsidy_pool_id
3090: CONNECT BY PRIOR chld.id = chld.subsidy_pool_id START WITH chld.id = cp_pool_id
3091: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1

Line 3102: CURSOR get_pool_dtls(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS

3098: end))
3099: ORDER BY LEVEL,
3100: parent.subsidy_pool_name;
3101:
3102: CURSOR get_pool_dtls(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
3103: SELECT id,
3104: subsidy_pool_name,
3105: pool_type_code,
3106: currency_code,

Line 3110: FROM okl_subsidy_pools_b

3106: currency_code,
3107: currency_conversion_type,
3108: reporting_pool_limit,
3109: effective_from_date
3110: FROM okl_subsidy_pools_b
3111: WHERE id = cp_pool_id;
3112:
3113: x_errbuf VARCHAR2(1000);
3114: x_retcode NUMBER;

Line 3122: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE ;

3118: l_from_date DATE;
3119: l_pool_rec okl_sub_pool_rec;
3120: l_chld_pool_rec okl_sub_pool_rec;
3121: l_input_pool_rec okl_sub_pool_rec;
3122: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE ;
3123: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
3124: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
3125: l_api_name CONSTANT VARCHAR2(30) := 'XML_POOL_ASSOC_REPORT';
3126: l_msg_count NUMBER;

Line 3124: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;

3120: l_chld_pool_rec okl_sub_pool_rec;
3121: l_input_pool_rec okl_sub_pool_rec;
3122: l_total_budget okl_subsidy_pools_b.total_budgets%TYPE ;
3123: l_trx_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
3124: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
3125: l_api_name CONSTANT VARCHAR2(30) := 'XML_POOL_ASSOC_REPORT';
3126: l_msg_count NUMBER;
3127: l_msg_data VARCHAR2(2000);
3128: l_return_status VARCHAR2(1);

Line 3329: FROM okl_subsidy_pools_b

3325: case when NVL(total_budgets,0) = 0 then 0
3326: else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
3327: effective_to_date,
3328: decision_status_code
3329: FROM okl_subsidy_pools_b
3330: WHERE pool_type_code = 'BUDGET'
3331: AND decision_status_code = 'ACTIVE'
3332: AND CASE WHEN NVL(total_budgets,0) = 0 THEN 0
3333: ELSE ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets END <= cp_percent

Line 3338: where subsidy_pool_id = okl_subsidy_pools_b.id

3334: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
3335: else
3336: (case when exists (select 'x'
3337: from okl_subsidies_v
3338: where subsidy_pool_id = okl_subsidy_pools_b.id
3339: and org_id <> mo_global.get_current_org_id()) then 0
3340: else 1 end)
3341: end));
3342:

Line 3345: CURSOR c_get_budget(cp_remaining NUMBER, cp_currency okl_subsidy_pools_b.currency_code%TYPE) IS

3341: end));
3342:
3343: -- Cursor to fetch all the subsidy pools whose reamining budget is
3344: -- less than or equal to the specified remaining budget.
3345: CURSOR c_get_budget(cp_remaining NUMBER, cp_currency okl_subsidy_pools_b.currency_code%TYPE) IS
3346: SELECT id,
3347: subsidy_pool_name,
3348: currency_code,
3349: total_budgets,

Line 3355: FROM okl_subsidy_pools_b

3351: case when NVL(total_budgets,0) = 0 then 0
3352: else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
3353: effective_to_date,
3354: decision_status_code
3355: FROM okl_subsidy_pools_b
3356: WHERE pool_type_code = 'BUDGET'
3357: AND decision_status_code = 'ACTIVE'
3358: AND NVL(total_budgets - NVL(total_subsidy_amount,0),0) <= cp_remaining
3359: AND currency_code = cp_currency

Line 3364: where subsidy_pool_id = okl_subsidy_pools_b.id

3360: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
3361: else
3362: (case when exists (select 'x'
3363: from okl_subsidies_v
3364: where subsidy_pool_id = okl_subsidy_pools_b.id
3365: and org_id <> mo_global.get_current_org_id()) then 0
3366: else 1 end)
3367: end));
3368:

Line 3380: FROM okl_subsidy_pools_b

3376: NVL(total_budgets - NVL(total_subsidy_amount,0),0) remaining_balance,
3377: case when NVL(total_budgets,0) = 0 then 0
3378: else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
3379: effective_to_date
3380: FROM okl_subsidy_pools_b
3381: WHERE pool_type_code = 'BUDGET'
3382: AND decision_status_code = 'ACTIVE'
3383: AND TRUNC(effective_to_date) >= TRUNC(SYSDATE)
3384: AND TRUNC(effective_to_date) <= TRUNC(cp_date)

Line 3389: where subsidy_pool_id = okl_subsidy_pools_b.id

3385: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
3386: else
3387: (case when exists (select 'x'
3388: from okl_subsidies_v
3389: where subsidy_pool_id = okl_subsidy_pools_b.id
3390: and org_id <> mo_global.get_current_org_id()) then 0
3391: else 1 end)
3392: end));
3393:

Line 3406: FROM okl_subsidy_pools_b

3402: case when NVL(total_budgets,0) = 0 then 0
3403: else ((total_budgets - NVL(total_subsidy_amount,0)) * 100) /total_budgets end percent_remaining,
3404: effective_to_date,
3405: trunc(effective_to_date) - trunc(sysdate) remaining_days
3406: FROM okl_subsidy_pools_b
3407: WHERE pool_type_code = 'BUDGET'
3408: AND decision_status_code = 'ACTIVE'
3409: AND trunc(effective_to_date) - trunc(sysdate) between 0 and cp_days
3410: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1

Line 3414: where subsidy_pool_id = okl_subsidy_pools_b.id

3410: AND ( 1 = (case when nvl(fnd_profile.value('OKLSUBPOOLGLOBALACCESS'),'N') = 'Y' then 1
3411: else
3412: (case when exists (select 'x'
3413: from okl_subsidies_v
3414: where subsidy_pool_id = okl_subsidy_pools_b.id
3415: and org_id <> mo_global.get_current_org_id()) then 0
3416: else 1 end)
3417: end));
3418:

Line 3437: l_decision_status_code okl_subsidy_pools_b.decision_status_code%TYPE;

3433: l_return_status VARCHAR2(1);
3434: l_msg_count NUMBER;
3435: l_msg_data VARCHAR2(2000);
3436: l_api_name CONSTANT VARCHAR2(30) := 'xml_print_atlimit_detail';
3437: l_decision_status_code okl_subsidy_pools_b.decision_status_code%TYPE;
3438:
3439: BEGIN
3440:
3441: l_debug_enabled := okl_debug_pub.check_log_enabled;

Line 3763: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS

3759: x_msg_data OUT NOCOPY VARCHAR2 )
3760: IS
3761:
3762: -- Cursor to fetch all the children pools of a subsidy pool entered.
3763: CURSOR get_amounts(cp_pool_id okl_subsidy_pools_b.id%TYPE) IS
3764: SELECT id ,
3765: currency_code
3766: FROM okl_subsidy_pools_v pool
3767: WHERE pool_type_code = 'BUDGET'

Line 3773: l_budget okl_subsidy_pools_b.total_budgets%TYPE;

3769: START WITH id = cp_pool_id;
3770:
3771: x_errbuf VARCHAR2(1000);
3772: x_retcode NUMBER;
3773: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
3774: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
3775: l_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
3776: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
3777: l_api_name CONSTANT VARCHAR2(30) := 'xml_print_pool_summary';

Line 3776: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;

3772: x_retcode NUMBER;
3773: l_budget okl_subsidy_pools_b.total_budgets%TYPE;
3774: l_trx_amt okl_trx_subsidy_pools.trx_amount%TYPE ;
3775: l_amount okl_trx_subsidy_pools.trx_amount%TYPE ;
3776: l_remaining_balance okl_subsidy_pools_b.total_budgets%TYPE;
3777: l_api_name CONSTANT VARCHAR2(30) := 'xml_print_pool_summary';
3778: l_msg_count NUMBER;
3779: l_msg_data VARCHAR2(2000);
3780: l_return_status VARCHAR2(1);

Line 3820: -- from the okl_subsidy_pools_b table and calculate the total transaction amount

3816: RAISE OKL_API.G_EXCEPTION_ERROR;
3817: END IF;
3818:
3819: -- If subsidy pool type is "Budget" then pick the values for total budgets
3820: -- from the okl_subsidy_pools_b table and calculate the total transaction amount
3821: -- from the okl_trx_subsidy_pools table.
3822: IF (p_pool_rec.pool_type_code = 'BUDGET') THEN
3823: -- Parent pool header with the parent pool name.
3824: -- Calculate the total budgets for subsidy pool till the date specified.

Line 3972: PROCEDURE xml_print_transaction_summary (p_pool_id IN okl_subsidy_pools_b.id%TYPE,

3968: -- Version : 1.0
3969: -- History : 03-Jan-2007 UDHENUKO created.
3970: -- End of comments
3971: ---------------------------------------------------------------------------
3972: PROCEDURE xml_print_transaction_summary (p_pool_id IN okl_subsidy_pools_b.id%TYPE,
3973: p_from_date IN DATE,
3974: p_to_date IN DATE,
3975: p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,
3976: p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,

Line 3975: p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,

3971: ---------------------------------------------------------------------------
3972: PROCEDURE xml_print_transaction_summary (p_pool_id IN okl_subsidy_pools_b.id%TYPE,
3973: p_from_date IN DATE,
3974: p_to_date IN DATE,
3975: p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,
3976: p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,
3977: p_conv_type IN okl_subsidy_pools_b.currency_conversion_type%TYPE,
3978: x_return_status OUT NOCOPY VARCHAR2,
3979: x_msg_count OUT NOCOPY NUMBER ,

Line 3976: p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,

3972: PROCEDURE xml_print_transaction_summary (p_pool_id IN okl_subsidy_pools_b.id%TYPE,
3973: p_from_date IN DATE,
3974: p_to_date IN DATE,
3975: p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,
3976: p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,
3977: p_conv_type IN okl_subsidy_pools_b.currency_conversion_type%TYPE,
3978: x_return_status OUT NOCOPY VARCHAR2,
3979: x_msg_count OUT NOCOPY NUMBER ,
3980: x_msg_data OUT NOCOPY VARCHAR2 )

Line 3977: p_conv_type IN okl_subsidy_pools_b.currency_conversion_type%TYPE,

3973: p_from_date IN DATE,
3974: p_to_date IN DATE,
3975: p_pool_type IN okl_subsidy_pools_b.pool_type_code%TYPE,
3976: p_pool_currency IN okl_subsidy_pools_b.currency_code%TYPE,
3977: p_conv_type IN okl_subsidy_pools_b.currency_conversion_type%TYPE,
3978: x_return_status OUT NOCOPY VARCHAR2,
3979: x_msg_count OUT NOCOPY NUMBER ,
3980: x_msg_data OUT NOCOPY VARCHAR2 )
3981: IS

Line 3984: CURSOR c_transaction_detail(cp_pool_id okl_subsidy_pools_b.id%TYPE, cp_from_date DATE, cp_to_date DATE) IS

3980: x_msg_data OUT NOCOPY VARCHAR2 )
3981: IS
3982:
3983: -- cursor to fetch all the transactions details for the subsidy pool between the dates entered by user.
3984: CURSOR c_transaction_detail(cp_pool_id okl_subsidy_pools_b.id%TYPE, cp_from_date DATE, cp_to_date DATE) IS
3985: SELECT flk1.meaning trx_reason,
3986: (case
3987: when pool.source_type_code = 'LEASE_CONTRACT' then
3988: (select khr.contract_number

Line 4025: FROM okl_subsidy_pools_b

4021: AND flk1.lookup_code = pool.trx_reason_code
4022: AND vend.vendor_id = pool.vendor_id
4023: AND sub.id = pool.subsidy_id
4024: AND pool.subsidy_pool_id IN ( SELECT id
4025: FROM okl_subsidy_pools_b
4026: WHERE pool_type_code = 'BUDGET'
4027: CONNECT BY PRIOR id = subsidy_pool_id
4028: START WITH id = cp_pool_id
4029: )