[Home] [Help]
96: -1,
97: -1 );
98: END LOOP; --c_cust_prod
99:
100: -- for R12, insert records into OZF_RES_CUST_PROD table
101: DELETE from OZF_RES_CUST_PROD;
102:
103: INSERT INTO OZF_RES_CUST_PROD
104: (SELECT distinct
97: -1 );
98: END LOOP; --c_cust_prod
99:
100: -- for R12, insert records into OZF_RES_CUST_PROD table
101: DELETE from OZF_RES_CUST_PROD;
102:
103: INSERT INTO OZF_RES_CUST_PROD
104: (SELECT distinct
105: fund.owner RESOURCE_ID,
99:
100: -- for R12, insert records into OZF_RES_CUST_PROD table
101: DELETE from OZF_RES_CUST_PROD;
102:
103: INSERT INTO OZF_RES_CUST_PROD
104: (SELECT distinct
105: fund.owner RESOURCE_ID,
106: acct.parent_party_id PARTY_ID,
107: acct.cust_account_id CUST_ACCOUNT_ID,
1602: l_current_year_time_id;
1603: CLOSE period_time_id_csr;
1604:
1605: -- inanaiah: bug 4912723 - delete all the records otherwise target info for cancelled quotas
1606: -- will exist in ozf_dashb_daily_kpi and thus get populated into OZF_RES_CUST_PROD_FACTS.
1607: -- Removed the deletion based on report_date and resource_id in insert_kpi proc.
1608: DELETE FROM ozf_dashb_daily_kpi
1609: WHERE report_date = p_report_date;
1610:
2186: p_data => x_msg_data);
2187:
2188: END update_quota_sales_info;
2189:
2190: -- Added in R12 - Used to populate the OZF_RES_CUST_PROD_FACTS table
2191: PROCEDURE populate_res_cust_prod_facts (
2192: p_api_version IN NUMBER,
2193: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2194: p_report_date IN DATE,
2216: RAISE FND_API.g_exc_unexpected_error;
2217: END IF;
2218: x_return_status := FND_API.g_ret_sts_success;
2219:
2220: DELETE FROM OZF_RES_CUST_PROD_FACTS
2221: WHERE report_date = p_report_date;
2222:
2223: INSERT INTO OZF_RES_CUST_PROD_FACTS
2224: (SELECT OZF_RES_CUST_PROD_FACTS_S.nextval,
2219:
2220: DELETE FROM OZF_RES_CUST_PROD_FACTS
2221: WHERE report_date = p_report_date;
2222:
2223: INSERT INTO OZF_RES_CUST_PROD_FACTS
2224: (SELECT OZF_RES_CUST_PROD_FACTS_S.nextval,
2225: resource_id,
2226: report_date,
2227: fact_row_for,
2220: DELETE FROM OZF_RES_CUST_PROD_FACTS
2221: WHERE report_date = p_report_date;
2222:
2223: INSERT INTO OZF_RES_CUST_PROD_FACTS
2224: (SELECT OZF_RES_CUST_PROD_FACTS_S.nextval,
2225: resource_id,
2226: report_date,
2227: fact_row_for,
2228: party_id,
2673: -- inanaiah: updating PERIOD_QUOTA, QTR_QUOTA, YEAR_QUOTA that was set to 0
2674: -- in the above insert stmt. This is done as part of bug 4887783 fix.
2675:
2676: -- PARTY
2677: UPDATE ozf_res_cust_prod_facts outer
2678: Set (PERIOD_QUOTA, QTR_QUOTA, YEAR_QUOTA)
2679: =
2680: (
2681: SELECT
2696: WHERE outer.fact_row_for = 'PARTY'
2697: AND outer.report_date = p_report_date;
2698:
2699: -- BILL_TO
2700: UPDATE ozf_res_cust_prod_facts outer
2701: Set (PERIOD_QUOTA, QTR_QUOTA, YEAR_QUOTA)
2702: =
2703: (
2704: SELECT
2719: WHERE outer.fact_row_for = 'BILL_TO'
2720: AND outer.report_date = p_report_date;
2721:
2722: -- SHIP_TO
2723: UPDATE ozf_res_cust_prod_facts outer
2724: Set (PERIOD_QUOTA, QTR_QUOTA, YEAR_QUOTA)
2725: =
2726: (
2727: SELECT
2763: p_count => x_msg_count,
2764: p_data => x_msg_data);
2765: END populate_res_cust_prod_facts;
2766:
2767: -- for R12, proc to get the budget and claims info and update the ozf_res_cust_prod_facts
2768: PROCEDURE refresh_budget_and_claims_info (
2769: p_api_version IN NUMBER,
2770: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2771: p_report_date IN DATE,
2794: END IF;
2795: x_return_status := FND_API.g_ret_sts_success;
2796:
2797: --YTD budget amounts for 'PARTY'
2798: UPDATE ozf_res_cust_prod_facts outer
2799: Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
2800: =
2801: (
2802: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
2803: NVL(SUM(earned_amt),0) tot_earned,
2804: NVL(SUM(paid_amt),0) tot_paid
2805: FROM ozf_time_day a,
2806: ozf_cust_fund_summary_mv b,
2807: ozf_res_cust_prod c
2808: WHERE c.resource_id = outer.resource_id
2809: AND a.report_date = outer.report_date
2810: AND a.ent_year_id = b.time_id
2811: AND b.status_code = 'ACTIVE'
2837: WHERE outer.fact_row_for = 'PARTY'
2838: AND outer.report_date = p_report_date ;
2839:
2840: --YTD amounts for 'BILL_TO'
2841: Update ozf_res_cust_prod_facts outer
2842: Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
2843: =
2844: (
2845: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
2846: NVL(SUM(earned_amt),0) tot_earned,
2847: NVL(SUM(paid_amt),0) tot_paid
2848: FROM ozf_time_day a,
2849: ozf_cust_fund_summary_mv b,
2850: ozf_res_cust_prod c
2851: WHERE c.resource_id = outer.resource_id
2852: AND a.report_date = outer.report_date
2853: AND a.ent_year_id = b.time_id
2854: AND b.status_code = 'ACTIVE'
2882: WHERE outer.fact_row_for = 'BILL_TO'
2883: AND outer.report_date = p_report_date ;
2884:
2885: --YTD amounts for 'SHIP_TO'
2886: Update ozf_res_cust_prod_facts outer
2887: Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
2888: =
2889: (
2890: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
2891: NVL(SUM(earned_amt),0) tot_earned,
2892: NVL(SUM(paid_amt),0) tot_paid
2893: FROM ozf_time_day a,
2894: ozf_cust_fund_summary_mv b,
2895: ozf_res_cust_prod c
2896: WHERE c.resource_id = outer.resource_id
2897: AND a.report_date = outer.report_date
2898: AND a.ent_year_id = b.time_id
2899: AND b.status_code = 'ACTIVE'
2921: WHERE outer.fact_row_for = 'SHIP_TO'
2922: AND outer.report_date = p_report_date ;
2923:
2924: --QTD amounts for 'PARTY'
2925: Update ozf_res_cust_prod_facts outer
2926: Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
2927: =
2928: (
2929: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
2930: NVL(SUM(earned_amt),0) tot_earned,
2931: NVL(SUM(paid_amt),0) tot_paid
2932: FROM ozf_time_day a,
2933: ozf_cust_fund_summary_mv b,
2934: ozf_res_cust_prod c
2935: WHERE c.resource_id = outer.resource_id
2936: AND a.report_date = outer.report_date
2937: AND a.ent_qtr_id = b.time_id
2938: AND b.status_code = 'ACTIVE'
2964: WHERE outer.fact_row_for = 'PARTY'
2965: AND outer.report_date = p_report_date ;
2966:
2967: --QTD amounts for 'BILL_TO'
2968: Update ozf_res_cust_prod_facts outer
2969: Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
2970: =
2971: (
2972: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
2973: NVL(SUM(earned_amt),0) tot_earned,
2974: NVL(SUM(paid_amt),0) tot_paid
2975: FROM ozf_time_day a,
2976: ozf_cust_fund_summary_mv b,
2977: ozf_res_cust_prod c
2978: WHERE c.resource_id = outer.resource_id
2979: AND a.report_date = outer.report_date
2980: AND a.ent_qtr_id = b.time_id
2981: AND b.status_code = 'ACTIVE'
3009: WHERE outer.fact_row_for = 'BILL_TO'
3010: AND outer.report_date = p_report_date ;
3011:
3012: --QTD amounts for 'SHIP_TO'
3013: Update ozf_res_cust_prod_facts outer
3014: Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
3015: =
3016: (
3017: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3018: NVL(SUM(earned_amt),0) tot_earned,
3019: NVL(SUM(paid_amt),0) tot_paid
3020: FROM ozf_time_day a,
3021: ozf_cust_fund_summary_mv b,
3022: ozf_res_cust_prod c
3023: WHERE c.resource_id = outer.resource_id
3024: AND a.report_date = outer.report_date
3025: AND a.ent_qtr_id = b.time_id
3026: AND b.status_code = 'ACTIVE'
3049: WHERE outer.fact_row_for = 'SHIP_TO'
3050: AND outer.report_date = p_report_date;
3051:
3052: --MTD amounts for 'PARTY'
3053: Update ozf_res_cust_prod_facts outer
3054: Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
3055: =
3056: (
3057: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3058: NVL(SUM(earned_amt),0) tot_earned,
3059: NVL(SUM(paid_amt),0) tot_paid
3060: FROM ozf_time_day a,
3061: ozf_cust_fund_summary_mv b,
3062: ozf_res_cust_prod c
3063: WHERE c.resource_id = outer.resource_id
3064: AND a.report_date = outer.report_date
3065: AND a.ent_period_id = b.time_id
3066: AND b.status_code = 'ACTIVE'
3092: WHERE outer.fact_row_for = 'PARTY'
3093: AND outer.report_date = p_report_date;
3094:
3095: --MTD amounts for 'BILL_TO'
3096: Update ozf_res_cust_prod_facts outer
3097: Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
3098: =
3099: (
3100: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3101: NVL(SUM(earned_amt),0) tot_earned,
3102: NVL(SUM(paid_amt),0) tot_paid
3103: FROM ozf_time_day a,
3104: ozf_cust_fund_summary_mv b,
3105: ozf_res_cust_prod c
3106: WHERE c.resource_id = outer.resource_id
3107: AND a.report_date = outer.report_date
3108: AND a.ent_period_id = b.time_id
3109: AND b.status_code = 'ACTIVE'
3137: WHERE outer.fact_row_for = 'BILL_TO'
3138: AND outer.report_date = p_report_date ;
3139:
3140: --MTD amounts for 'SHIP_TO'
3141: Update ozf_res_cust_prod_facts outer
3142: Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
3143: =
3144: (
3145: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3146: NVL(SUM(earned_amt),0) tot_earned,
3147: NVL(SUM(paid_amt),0) tot_paid
3148: FROM ozf_time_day a,
3149: ozf_cust_fund_summary_mv b,
3150: ozf_res_cust_prod c
3151: WHERE c.resource_id = outer.resource_id
3152: AND a.report_date = outer.report_date
3153: AND a.ent_period_id = b.time_id
3154: AND b.status_code = 'ACTIVE'
3177: WHERE outer.fact_row_for = 'SHIP_TO'
3178: AND outer.report_date = p_report_date ;
3179:
3180: --UNPAID amount for 'PARTY'
3181: Update ozf_res_cust_prod_facts outer
3182: Set (FUND_unpaid)
3183: =
3184: (
3185: SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3183: =
3184: (
3185: SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3186: FROM ozf_cust_fund_summary_mv b,
3187: ozf_res_cust_prod c
3188: WHERE c.resource_id = outer.resource_id
3189: AND b.time_id = -1
3190: AND b.period_type_id = 256
3191: AND b.status_code = 'ACTIVE'
3217: WHERE outer.fact_row_for = 'PARTY'
3218: AND outer.report_date = p_report_date ;
3219:
3220: --UNPAID amount for 'BILL_TO'
3221: Update ozf_res_cust_prod_facts outer
3222: Set (fund_unpaid)
3223: =
3224: (
3225: SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3223: =
3224: (
3225: SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3226: FROM ozf_cust_fund_summary_mv b,
3227: ozf_res_cust_prod c
3228: WHERE c.resource_id = outer.resource_id
3229: AND b.time_id = -1
3230: AND b.period_type_id = 256
3231: AND b.status_code = 'ACTIVE'
3259: WHERE outer.fact_row_for = 'BILL_TO'
3260: AND outer.report_date = p_report_date ;
3261:
3262: --UNPAID amount for 'SHIP_TO'
3263: Update ozf_res_cust_prod_facts outer
3264: Set (fund_unpaid)
3265: =
3266: (
3267: SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3265: =
3266: (
3267: SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3268: FROM ozf_cust_fund_summary_mv b,
3269: ozf_res_cust_prod c
3270: WHERE c.resource_id = outer.resource_id
3271: AND b.time_id = -1
3272: AND b.period_type_id = 256
3273: AND b.status_code = 'ACTIVE'
3296: WHERE outer.fact_row_for = 'SHIP_TO'
3297: AND outer.report_date = p_report_date ;
3298:
3299: --YTD budget amount for 'PRODUCT'
3300: Update ozf_res_cust_prod_facts outer
3301: Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
3302: =
3303: (
3304: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3305: NVL(SUM(earned_amt),0) tot_earned,
3306: NVL(SUM(paid_amt),0) tot_paid
3307: FROM ozf_time_day a,
3308: ozf_cust_fund_summary_mv b,
3309: ozf_res_cust_prod c
3310: WHERE c.resource_id = outer.resource_id
3311: AND a.report_date = outer.report_date
3312: AND a.ent_year_id = b.time_id
3313: AND b.status_code = 'ACTIVE'
3326: WHERE outer.fact_row_for = 'PRODUCT'
3327: AND outer.report_date = p_report_date ;
3328:
3329: --QTD budget amount for 'PRODUCT'
3330: Update ozf_res_cust_prod_facts outer
3331: Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
3332: =
3333: (
3334: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3335: NVL(SUM(earned_amt),0) tot_earned,
3336: NVL(SUM(paid_amt),0) tot_paid
3337: FROM ozf_time_day a,
3338: ozf_cust_fund_summary_mv b,
3339: ozf_res_cust_prod c
3340: WHERE c.resource_id = outer.resource_id
3341: AND a.report_date = outer.report_date
3342: AND a.ent_qtr_id = b.time_id
3343: AND b.status_code = 'ACTIVE'
3356: WHERE outer.fact_row_for = 'PRODUCT'
3357: AND outer.report_date = p_report_date ;
3358:
3359: --MTD budget amount for 'PRODUCT'
3360: Update ozf_res_cust_prod_facts outer
3361: Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
3362: =
3363: (
3364: SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3365: NVL(SUM(earned_amt),0) tot_earned,
3366: NVL(SUM(paid_amt),0) tot_paid
3367: FROM ozf_time_day a,
3368: ozf_cust_fund_summary_mv b,
3369: ozf_res_cust_prod c
3370: WHERE c.resource_id = outer.resource_id
3371: AND a.report_date = outer.report_date
3372: AND a.ent_period_id = b.time_id
3373: AND b.status_code = 'ACTIVE'
3386: WHERE outer.fact_row_for = 'PRODUCT'
3387: AND outer.report_date = p_report_date ;
3388:
3389: --Unpaid budget amount for 'PRODUCT'
3390: Update ozf_res_cust_prod_facts outer
3391: Set (fund_unpaid)
3392: =
3393: (
3394: SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3392: =
3393: (
3394: SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3395: FROM ozf_cust_fund_summary_mv b,
3396: ozf_res_cust_prod c
3397: WHERE c.resource_id = outer.resource_id
3398: AND b.time_id = -1
3399: AND b.period_type_id = 256
3400: AND b.status_code = 'ACTIVE'
3413: WHERE outer.fact_row_for = 'PRODUCT'
3414: AND outer.report_date = p_report_date ;
3415:
3416: --Open Claims amount for 'PARTY'
3417: Update ozf_res_cust_prod_facts outer
3418: Set (OPEN_CLAIMS)
3419: =
3420: (
3421: SELECT NVL(SUM(amount_remaining),0)
3419: =
3420: (
3421: SELECT NVL(SUM(amount_remaining),0)
3422: FROM ozf_claims_all b,
3423: (SELECT DISTINCT resource_id, party_id, cust_account_id, ship_to_site_use_id FROM ozf_res_cust_prod) c
3424: WHERE c.resource_id = outer.resource_id
3425: AND c.party_id = outer.party_id
3426: AND b.cust_account_id = c.cust_account_id
3427: AND b.claim_date <= outer.report_date
3437: WHERE outer.fact_row_for = 'PARTY'
3438: AND outer.report_date = p_report_date ;
3439:
3440: --Open Claims amount for 'BILL_TO'
3441: Update ozf_res_cust_prod_facts outer
3442: Set (OPEN_CLAIMS)
3443: =
3444: (
3445: SELECT NVL(SUM(amount_remaining),0)
3443: =
3444: (
3445: SELECT NVL(SUM(amount_remaining),0)
3446: FROM ozf_claims_all b,
3447: (SELECT DISTINCT resource_id, party_id, cust_account_id, bill_to_site_use_id, ship_to_site_use_id FROM ozf_res_cust_prod) c
3448: WHERE c.resource_id = outer.resource_id
3449: AND c.party_id = outer.party_id
3450: AND c.bill_to_site_use_id = outer.bill_to_site_use_id
3451: AND b.cust_billto_acct_site_id = outer.bill_to_site_use_id
3463: WHERE outer.fact_row_for = 'BILL_TO'
3464: AND outer.report_date = p_report_date ;
3465:
3466: --Open Claims amount for 'SHIP_TO'
3467: Update ozf_res_cust_prod_facts outer
3468: Set (OPEN_CLAIMS)
3469: =
3470: (
3471: SELECT NVL(SUM(amount_remaining),0)
3469: =
3470: (
3471: SELECT NVL(SUM(amount_remaining),0)
3472: FROM ozf_claims_all b,
3473: (SELECT DISTINCT resource_id, party_id, cust_account_id, bill_to_site_use_id, ship_to_site_use_id FROM ozf_res_cust_prod) c
3474: WHERE c.resource_id = outer.resource_id
3475: AND c.party_id = outer.party_id
3476: AND c.bill_to_site_use_id = outer.bill_to_site_use_id
3477: AND b.cust_billto_acct_site_id = outer.bill_to_site_use_id