[Home] [Help]
116: (SELECT
117: c1.fund_id fund_id,
118: c1.week_date week_date,
119: SUM(nvl(fdsp.commited_amt,0)) commited_amt,
120: SUM(nvl(fdsp.standard_discount,0)+nvl(fdsp.market_expense,0)+nvl(fdsp.accrual,0)) utilized_amt,
121: SUM(nvl(fdsp.planned_amt,0)) planned,
122: SUM(nvl(fdsp.paid_amt,0)) paid
123: FROM bim_r_fdsp_daily_facts fdsp,
124: (SELECT fund_id fund_id,
267: (select fund_id fund_id,
268: SUM(nvl(commited_amt,0)) commited_amt,
269: SUM(nvl(planned_amt,0)) planned,
270: SUM(nvl(paid_amt,0)) paid,
271: SUM(nvl(standard_discount,0)+nvl(market_expense,0)+nvl(accrual,0)) utilized_amt,
272: transaction_create_date week_date
273: from bim_r_fdsp_daily_facts
274: WHERE fund_id = x.fund_id
275: AND transaction_create_date = x.week_date
443: FROM bim_r_fund_balance ba,
444: bim_r_fund_daily_facts a,
445: (SELECT fund_id fund_id,
446: SUM(nvl(commited_amt,0)) commited_amt,
447: SUM(nvl(standard_discount,0)+nvl(accrual,0)+
448: nvl(market_expense,0)) utilized,
449: SUM(nvl(planned_amt,0)) planned,
450: SUM(nvl(paid_amt,0)) paid,
451: transaction_create_date week_date
499: a.fis_year
500: FROM bim_r_fund_daily_facts a,
501: (SELECT fund_id fund_id,
502: SUM(nvl(commited_amt,0)) commited_amt,
503: SUM(nvl(standard_discount,0)+nvl(accrual,0)+
504: nvl(market_expense,0)) utilized,
505: SUM(nvl(planned_amt,0)) planned,
506: SUM(nvl(paid_amt,0)) paid,
507: transaction_create_date week_date
548: bim_r_fund_balance ba,
549: bim_r_fund_daily_facts a,
550: (select fund_id fund_id,
551: sum(nvl(commited_amt,0)) commited_amt,
552: SUM(nvl(standard_discount,0)+nvl(accrual,0)+
553: nvl(market_expense,0)) utilized,
554: SUM(nvl(planned_amt,0)) planned,
555: SUM(nvl(paid_amt,0)) paid,
556: transaction_create_date week_date
622:
623: BEGIN
624: l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
625:
626: -- Standard call to check for call compatibility.
627: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
628: p_api_version_number,
629: l_api_name,
630: G_PKG_NAME)
732: RAISE FND_API.g_exc_unexpected_error;
733: END IF;
734: END IF;
735:
736: --Standard check of commit
737: IF FND_API.To_Boolean (p_commit) THEN
738: COMMIT WORK;
739: END IF;
740:
737: IF FND_API.To_Boolean (p_commit) THEN
738: COMMIT WORK;
739: END IF;
740:
741: -- Standard call to get message count and IF count IS 1, get message info.
742: FND_msg_PUB.Count_And_Get
743: (p_count => x_msg_count,
744: p_data => x_msg_data
745: );
748: fnd_file.put_line(fnd_file.log,fnd_message.get);
749: EXCEPTION
750: WHEN FND_API.G_EXC_ERROR THEN
751: x_return_status := FND_API.G_RET_STS_ERROR;
752: -- Standard call to get message count and IF count=1, get the message
753: FND_msg_PUB.Count_And_Get (
754: -- p_encoded => FND_API.G_FALSE,
755: p_count => x_msg_count,
756: p_data => x_msg_data );
755: p_count => x_msg_count,
756: p_data => x_msg_data );
757: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
758: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
759: -- Standard call to get message count and IF count=1, get the message
760: FND_msg_PUB.Count_And_Get (
761: --p_encoded => FND_API.G_FALSE,
762: p_count => x_msg_count,
763: p_data => x_msg_data
768: IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
769: THEN
770: FND_msg_PUB.Add_Exc_msg( G_PKG_NAME,l_api_name);
771: END IF;
772: -- Standard call to get message count and IF count=1, get the message
773: FND_msg_PUB.Count_And_Get (
774: -- p_encoded => FND_API.G_FALSE,
775: p_count => x_msg_count,
776: p_data => x_msg_data
907:
908: BEGIN
909: l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
910:
911: -- Standard call to check for call compatibility.
912: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
913: l_api_version_number,
914: l_api_name,
915: G_PKG_NAME)
1556: ,last_update_login
1557: ,fund_id
1558: ,business_unit_id
1559: ,util_org_id
1560: ,standard_discount
1561: ,accrual
1562: ,market_expense
1563: ,commited_amt
1564: ,planned_amt
1577: -1,
1578: act_util.fund_id,
1579: 0,
1580: 0,
1581: act_util.standard_discount,
1582: act_util.accrual,
1583: act_util.market_expense,
1584: act_util.commited_amt,
1585: act_util.planned_amt,
1594: object_type object_type,
1595: creation_date creation_date,
1596: SUM(nvl(planned_amt,0)) planned_amt,
1597: SUM(nvl(commited_amt,0)) commited_amt,
1598: SUM(nvl(standard_discount,0)) standard_discount,
1599: SUM(nvl(accrual,0)) accrual,
1600: SUM(nvl(market_expense,0)) market_expense,
1601: SUM(nvl(paid_amt,0)) paid_amt
1602: FROM (
1605: arc_act_budget_used_by object_type,
1606: trunc(nvl(request_date,creation_date)) creation_date,
1607: SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
1608: 0 commited_amt,
1609: 0 standard_discount,
1610: 0 accrual,
1611: 0 market_expense,
1612: 0 paid_amt
1613: FROM ozf_act_budgets
1624: trunc(nvl(approval_date,last_update_date)) creation_date,
1625: --0-SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
1626: 0 planned_amt,
1627: SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
1628: 0 standard_discount,
1629: 0 accrual,
1630: 0 market_expense,
1631: 0 paid_amt
1632: FROM ozf_act_budgets
1642: budget_source_type object_type,
1643: trunc(nvl(approval_date,last_update_date)) creation_date,
1644: 0 planned_amt,
1645: 0-SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
1646: 0 standard_discount,
1647: 0 accrual,
1648: 0 market_expense,
1649: 0 paid_amt
1650: FROM ozf_act_budgets
1660: plan_type object_type,
1661: trunc(creation_date) creation_date,
1662: 0 planned_amt,
1663: 0 commited_amt,
1664: SUM(decode(component_type,'OFFR',decode(utilization_type, 'UTILIZED',convert_currency(currency_code,nvl(amount,0)), 0),0)) standard_discount,
1665: SUM(decode(component_type,'OFFR', decode(utilization_type, 'ACCRUAL', convert_currency(currency_code,nvl(amount,0)), 0),0) +
1666: decode(component_type,'OFFR', decode(utilization_type, 'ADJUSTMENT', convert_currency(currency_code,nvl(amount,0)), 0),0)) accrual,
1667: SUM(decode(component_type,'OFFR',0, decode(utilization_type, 'UTILIZED', convert_currency(currency_code,nvl(amount,0)), 0))) market_expense,
1668: sum(decode(component_type,'OFFR',0,convert_currency(currency_code,(nvl(amount,0)-NVL(amount_remaining,0))))) paid_amt
1702: ,last_update_login
1703: ,fund_id
1704: ,business_unit_id
1705: ,util_org_id
1706: ,standard_discount
1707: ,accrual
1708: ,market_expense
1709: ,commited_amt
1710: ,planned_amt
1727: l_user_id,
1728: inner.fund_id,
1729: inner.business_unit_id,
1730: inner.org_id,
1731: inner.standard_discount,
1732: inner.accrual,
1733: inner.market_expense,
1734: inner.commited_amt,
1735: inner.planned_amt,
1754: ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
1755: FROM (SELECT AD.fund_id fund_id,
1756: U.business_unit_id business_unit_id,
1757: U.org_id org_id,
1758: NVL(AU.standard_discount,0) standard_discount,
1759: NVL(AU.accrual,0) accrual,
1760: NVL(AU.market_expense,0) market_expense,
1761: NVL(AU.commited_amt,0) commited_amt,
1762: NVL(AU.planned_amt,0) planned_amt,
1902: ,last_update_login
1903: ,fund_id
1904: ,business_unit_id
1905: ,util_org_id
1906: ,standard_discount
1907: ,accrual
1908: ,market_expense
1909: ,commited_amt
1910: ,planned_amt
1927: l_user_id,
1928: inner.fund_id,
1929: inner.business_unit_id,
1930: inner.org_id,
1931: inner.standard_discount,
1932: inner.accrual,
1933: inner.market_expense,
1934: inner.commited_amt,
1935: inner.planned_amt,
1954: ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
1955: FROM (SELECT AD.fund_id fund_id,
1956: U.business_unit_id business_unit_id,
1957: U.org_id org_id,
1958: NVL(AU.standard_discount,0) standard_discount,
1959: NVL(AU.accrual,0) accrual,
1960: NVL(AU.market_expense,0) market_expense,
1961: NVL(AU.commited_amt,0) commited_amt,
1962: NVL(AU.planned_amt,0) planned_amt,
2350: ,last_update_login
2351: ,fund_id
2352: ,business_unit_id
2353: ,util_org_id
2354: ,standard_discount
2355: ,accrual
2356: ,market_expense
2357: ,commited_amt
2358: ,planned_amt
2375: ,l_user_id
2376: ,inner.fund_id
2377: ,inner.business_unit_id
2378: ,inner.util_org_id
2379: ,inner.standard_discount
2380: ,inner.accrual
2381: ,inner.market_expense
2382: ,inner.commited_amt
2383: ,inner.planned_amt
2395: ,business_unit_id business_unit_id
2396: ,util_org_id util_org_id
2397: ,object_id object_id
2398: ,object_type object_type
2399: ,SUM(standard_discount) standard_discount
2400: ,SUM(accrual) accrual
2401: ,SUM(market_expense) market_expense
2402: ,SUM(commited_amt) commited_amt
2403: ,SUM(planned_amt) planned_amt
2540:
2541: BEGIN
2542: l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
2543:
2544: -- Standard call to check for call compatibility.
2545: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2546: l_api_version_number,
2547: l_api_name,
2548: G_PKG_NAME)
3307: ,last_update_login
3308: ,fund_id
3309: ,business_unit_id
3310: ,util_org_id
3311: ,standard_discount
3312: ,accrual
3313: ,market_expense
3314: ,commited_amt
3315: ,planned_amt
3328: -1,
3329: act_util.fund_id,
3330: 0,
3331: 0,
3332: act_util.standard_discount,
3333: act_util.accrual,
3334: act_util.market_expense,
3335: act_util.commited_amt,
3336: act_util.planned_amt,
3345: object_type object_type,
3346: creation_date creation_date,
3347: SUM(nvl(planned_amt,0)) planned_amt,
3348: SUM(nvl(commited_amt,0)) commited_amt,
3349: SUM(nvl(standard_discount,0)) standard_discount,
3350: SUM(nvl(accrual,0)) accrual,
3351: SUM(nvl(market_expense,0)) market_expense,
3352: SUM(nvl(paid_amt,0)) paid_amt
3353: FROM (
3356: arc_act_budget_used_by object_type,
3357: trunc(nvl(request_date,creation_date)) creation_date,
3358: SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
3359: 0 commited_amt,
3360: 0 standard_discount,
3361: 0 accrual,
3362: 0 market_expense,
3363: 0 paid_amt
3364: FROM ozf_act_budgets
3374: arc_act_budget_used_by object_type,
3375: trunc(nvl(approval_date,last_update_date)) creation_date,
3376: 0-SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
3377: SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
3378: 0 standard_discount,
3379: 0 accrual,
3380: 0 market_expense,
3381: 0 paid_amt
3382: FROM ozf_act_budgets
3392: budget_source_type object_type,
3393: trunc(nvl(approval_date,last_update_date)) creation_date,
3394: 0 planned_amt,
3395: 0-SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
3396: 0 standard_discount,
3397: 0 accrual,
3398: 0 market_expense,
3399: 0 paid_amt
3400: FROM ozf_act_budgets
3410: plan_type object_type,
3411: trunc(creation_date) creation_date,
3412: 0 planned_amt,
3413: 0 commited_amt,
3414: SUM(decode(component_type,'OFFR',decode(utilization_type, 'UTILIZED',convert_currency(currency_code,nvl(amount,0)), 0),0)) standard_discount,
3415: SUM(decode(component_type,'OFFR', decode(utilization_type, 'ACCRUAL', convert_currency(currency_code,nvl(amount,0)), 0),0) +
3416: decode(component_type,'OFFR', decode(utilization_type, 'ADJUSTMENT', convert_currency(currency_code,nvl(amount,0)), 0),0)) accrual,
3417: SUM(decode(component_type,'OFFR',0, decode(utilization_type, 'UTILIZED', convert_currency(currency_code,nvl(amount,0)), 0))) market_expense,
3418: sum(decode(component_type,'OFFR',0,convert_currency(currency_code,(nvl(amount,0)-NVL(amount_remaining,0))))) paid_amt
3451: ,last_update_login
3452: ,fund_id
3453: ,business_unit_id
3454: ,util_org_id
3455: ,standard_discount
3456: ,accrual
3457: ,market_expense
3458: ,commited_amt
3459: ,planned_amt
3476: l_user_id,
3477: inner.fund_id,
3478: inner.business_unit_id,
3479: inner.org_id,
3480: inner.standard_discount,
3481: inner.accrual,
3482: inner.market_expense,
3483: inner.commited_amt,
3484: inner.planned_amt,
3503: ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
3504: FROM (SELECT AD.fund_id fund_id,
3505: U.business_unit_id business_unit_id,
3506: U.org_id org_id,
3507: NVL(AU.standard_discount,0) standard_discount,
3508: NVL(AU.accrual,0) accrual,
3509: NVL(AU.market_expense,0) market_expense,
3510: NVL(AU.commited_amt,0) commited_amt,
3511: NVL(AU.planned_amt,0) planned_amt,
3646: ,last_update_login
3647: ,fund_id
3648: ,business_unit_id
3649: ,util_org_id
3650: ,standard_discount
3651: ,accrual
3652: ,market_expense
3653: ,commited_amt
3654: ,planned_amt
3671: l_user_id,
3672: inner.fund_id,
3673: inner.business_unit_id,
3674: inner.org_id,
3675: inner.standard_discount,
3676: inner.accrual,
3677: inner.market_expense,
3678: inner.commited_amt,
3679: inner.planned_amt,
3698: ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
3699: FROM (SELECT AD.fund_id fund_id,
3700: U.business_unit_id business_unit_id,
3701: U.org_id org_id,
3702: NVL(AU.standard_discount,0) standard_discount,
3703: NVL(AU.accrual,0) accrual,
3704: NVL(AU.market_expense,0) market_expense,
3705: NVL(AU.commited_amt,0) commited_amt,
3706: NVL(AU.planned_amt,0) planned_amt,
3841: ,last_update_login
3842: ,fund_id
3843: ,business_unit_id
3844: ,util_org_id
3845: ,standard_discount
3846: ,accrual
3847: ,market_expense
3848: ,commited_amt
3849: ,planned_amt
3934: ,last_update_login
3935: ,fund_id
3936: ,business_unit_id
3937: ,util_org_id
3938: ,standard_discount
3939: ,accrual
3940: ,market_expense
3941: ,commited_amt
3942: ,planned_amt
4262: ,last_update_login
4263: ,fund_id
4264: ,business_unit_id
4265: ,util_org_id
4266: ,standard_discount
4267: ,accrual
4268: ,market_expense
4269: ,commited_amt
4270: ,planned_amt
4287: ,l_user_id
4288: ,inner.fund_id
4289: ,inner.business_unit_id
4290: ,inner.util_org_id
4291: ,inner.standard_discount
4292: ,inner.accrual
4293: ,inner.market_expense
4294: ,inner.commited_amt
4295: ,inner.planned_amt
4307: ,business_unit_id business_unit_id
4308: ,util_org_id util_org_id
4309: ,object_id object_id
4310: ,object_type object_type
4311: ,SUM(standard_discount) standard_discount
4312: ,SUM(accrual) accrual
4313: ,SUM(market_expense) market_expense
4314: ,SUM(commited_amt) commited_amt
4315: ,SUM(planned_amt) planned_amt