[Home] [Help]
525: -- PROCEDURE
526: -- refresh_netaccrual_parties
527: --
528: -- PURPOSE
529: -- Refreshes offer and party denorm table ozf_activity_customers.
530: --
531: -- PARAMETERS
532: -- p_list_header_id: qp_list_header_id of the offer
533: --
531: -- PARAMETERS
532: -- p_list_header_id: qp_list_header_id of the offer
533: --
534: -- DESCRIPTION
535: -- This procedure calls get_sql, builds SQL statment for parties and refresh ozf_activity_customers
536: ---------------------------------------------------------------------
537: PROCEDURE refresh_netaccrual_parties(
538: p_api_version IN NUMBER,
539: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1285: -- PROCEDURE
1286: -- refresh_volume_parties
1287: --
1288: -- PURPOSE
1289: -- Refreshes offer and party denorm table ozf_activity_customers.
1290: --
1291: -- PARAMETERS
1292: -- p_list_header_id: qp_list_header_id of the offer
1293: --
1291: -- PARAMETERS
1292: -- p_list_header_id: qp_list_header_id of the offer
1293: --
1294: -- DESCRIPTION
1295: -- This procedure calls get_sql, builds SQL statment for parties and refresh ozf_activity_customers
1296: ---------------------------------------------------------------------
1297: PROCEDURE refresh_volume_parties(
1298: p_api_version IN NUMBER,
1299: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1545: -- PROCEDURE
1546: -- refresh_parties
1547: --
1548: -- PURPOSE
1549: -- Refreshes offer and party denorm table ozf_activity_customers.
1550: --
1551: -- PARAMETERS
1552: -- p_list_header_id: qp_list_header_id of the offer
1553: --
1551: -- PARAMETERS
1552: -- p_list_header_id: qp_list_header_id of the offer
1553: --
1554: -- DESCRIPTION
1555: -- This procedure calls get_sql, builds SQL statment for parties and refresh ozf_activity_customers
1556: ---------------------------------------------------------------------
1557: PROCEDURE refresh_parties(
1558: p_api_version IN NUMBER,
1559: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2280: SELECT 'Y'
2281: FROM DUAL
2282: WHERE EXISTS (
2283: SELECT 1
2284: FROM ozf_activity_customers
2285: WHERE last_update_date > l_date
2286: AND object_id = l_id and object_class = 'OFFR'
2287: );
2288:
2725:
2726: write_conc_log('-- Deleting Temp and Denorm Tables --');
2727:
2728: if p_offer_id is not null then
2729: DELETE FROM ozf_activity_customers
2730: WHERE object_class = 'OFFR'
2731: and object_id = p_offer_id;
2732:
2733: DELETE FROM ozf_activity_products
2733: DELETE FROM ozf_activity_products
2734: WHERE object_class = 'OFFR'
2735: and object_id = p_offer_id;
2736:
2737: DELETE FROM ozf_activity_customers_temp
2738: WHERE object_class = 'OFFR'
2739: and object_id = p_offer_id;
2740:
2741: DELETE FROM ozf_activity_products_temp
2741: DELETE FROM ozf_activity_products_temp
2742: WHERE object_class = 'OFFR'
2743: and object_id = p_offer_id;
2744: else
2745: DELETE FROM ozf_activity_customers
2746: WHERE object_class = 'OFFR';
2747:
2748: DELETE FROM ozf_activity_products
2749: WHERE object_class = 'OFFR';
2747:
2748: DELETE FROM ozf_activity_products
2749: WHERE object_class = 'OFFR';
2750:
2751: DELETE FROM ozf_activity_customers_temp
2752: WHERE object_class = 'OFFR';
2753:
2754: DELETE FROM ozf_activity_products_temp
2755: WHERE object_class = 'OFFR';
2768: -- denorm parties
2769:
2770: ozf_utility_pvt.write_conc_log('-- Deleting Temp and Denorm Tables --');
2771: /*
2772: DELETE FROM ozf_activity_customers_temp
2773: WHERE object_class = 'OFFR'
2774: AND object_id = i.object_id ;
2775:
2776: DELETE FROM ozf_activity_products_temp
2776: DELETE FROM ozf_activity_products_temp
2777: WHERE object_class = 'OFFR'
2778: AND object_id = i.object_id ;
2779:
2780: DELETE FROM ozf_activity_customers
2781: WHERE object_class = 'OFFR'
2782: AND object_id = i.object_id;
2783:
2784: DELETE FROM ozf_activity_products
2799: if z.qnum = -99 AND l_group_count > 0 then
2800: goto GROUP_END1;
2801: end if;
2802: FND_DSQL.init;
2803: FND_DSQL.add_text('INSERT INTO ozf_activity_customers_temp(');
2804: FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,last_update_login,');
2805: FND_DSQL.add_text('confidential_flag,custom_setup_id,');
2806: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,');
2807: FND_DSQL.add_text('parent_id,parent_class,parent_desc,');
3102: where object_id = i.object_id
3103: and object_class = 'OFFR';
3104:
3105: -- update offer forecast and offer values.
3106: update ozf_activity_customers_temp
3107: set forecast_units = s_forecast_units,
3108: forecast_revenue = s_forecast_revenue,
3109: forecast_costs = s_forecast_costs,
3110: forecast_roi = s_forecast_roi,
3147:
3148: IF l_offer_changed IS NOT NULL THEN -- offer changed
3149: --write_log('OCHAN-Offer Id: '|| i.object_id || ' has changed.');
3150: -- parties have to be denormed as associated offers are changed
3151: DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
3152: WHERE object_id = i.object_id and object_class = 'OFFR';
3153:
3154: l_group_count := 0;
3155: FOR z IN c_groups(i.object_id)
3158: goto GROUP_END2;
3159: end if;
3160:
3161: FND_DSQL.init;
3162: FND_DSQL.add_text('INSERT INTO ozf_activity_customers(');
3163: FND_DSQL.add_text('activity_customer_id,creation_date,created_by,last_update_date,last_updated_by,');
3164: FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
3165: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3166: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3167: FND_DSQL.add_text('qp_qualifier_id,qp_qualifier_group,party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
3168: -- FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
3169: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
3170:
3171: FND_DSQL.add_text('SELECT ozf_activity_customers_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
3172: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
3173: FND_DSQL.add_bind(i.confidential_flag);
3174: FND_DSQL.add_text(',');
3175: FND_DSQL.add_bind(i.custom_setup_id);
3242: FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
3243: FND_DSQL.add_text(')');
3244:
3245: /*
3246: DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
3247: WHERE object_id = i.object_id and object_class = 'OFFR';
3248: */
3249: l_denorm_csr := DBMS_SQL.open_cursor;
3250: FND_DSQL.set_cursor(l_denorm_csr);
3364: CLOSE c_party_denormed;
3365:
3366: IF l_dummy IS NULL THEN
3367:
3368: DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
3369: WHERE object_id = i.object_id and object_class = 'OFFR';
3370:
3371: l_group_count := 0;
3372: FOR z IN c_groups(i.object_id)
3374: if z.qnum = -99 AND l_group_count > 0 then
3375: goto GROUP_END3;
3376: end if;
3377: FND_DSQL.init;
3378: FND_DSQL.add_text('INSERT INTO ozf_activity_customers(');
3379: FND_DSQL.add_text('activity_customer_id,creation_date,created_by,last_update_date,last_updated_by,');
3380: FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
3381: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3382: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3382: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3383: FND_DSQL.add_text('qp_qualifier_id, qp_qualifier_group,party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
3384: -- FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
3385: FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
3386: FND_DSQL.add_text('SELECT ozf_activity_customers_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
3387: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
3388: FND_DSQL.add_bind(i.confidential_flag);
3389: FND_DSQL.add_text(',');
3390: FND_DSQL.add_bind(i.custom_setup_id);
3453: FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'',''ORDER'') ');
3454: FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
3455: FND_DSQL.add_text(')');
3456: /*
3457: DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
3458: WHERE object_id = i.object_id and object_class = 'OFFR';
3459: */
3460: l_denorm_csr := DBMS_SQL.open_cursor;
3461: FND_DSQL.set_cursor(l_denorm_csr);
3668: actual_uom = l_uom_code
3669: where object_id = i.offer_id
3670: and object_class = 'OFFR';
3671:
3672: update ozf_activity_customers
3673: set forecast_units = s_forecast_units,
3674: forecast_revenue = s_forecast_revenue,
3675: forecast_costs = s_forecast_costs,
3676: forecast_roi = s_forecast_roi,
3735: forecast_uom = i.forecast_uom_code
3736: where object_id = i.offer_id
3737: and object_class = 'OFFR';
3738:
3739: update ozf_activity_customers
3740: set forecast_units = s_forecast_units,
3741: forecast_revenue = s_forecast_revenue,
3742: forecast_costs = s_forecast_costs,
3743: forecast_roi = s_forecast_roi,
3793: actual_uom = l_uom_code
3794: where object_id = i.offer_id
3795: and object_class = 'OFFR';
3796:
3797: update ozf_activity_customers
3798: set forecast_units = s_forecast_units,
3799: forecast_revenue = s_forecast_revenue,
3800: forecast_costs = s_forecast_costs,
3801: forecast_roi = s_forecast_roi,
3808: --write_log('After c_incremental_actual_values loop' );
3809: END IF;
3810:
3811: /*
3812: DELETE FROM ozf_activity_customers
3813: WHERE object_id IN (
3814: SELECT l.list_header_id
3815: FROM ozf_offers o, qp_list_headers l
3816: WHERE o.status_code IN ('CANCELLED', 'TERMINATED', 'CLOSED')
3827: )
3828: AND object_class = 'OFFR';
3829: */
3830:
3831: DELETE FROM OZF_ACTIVITY_CUSTOMERS b
3832: WHERE
3833: exists ( SELECT L.LIST_HEADER_ID
3834: FROM OZF_OFFERS O, QP_LIST_HEADERS L
3835: WHERE O.STATUS_CODE IN ('CANCELLED', 'TERMINATED', 'CLOSED') AND
3854: WHERE a.application_short_name = 'AMS'
3855: AND a.application_id = i.application_id;
3856: */
3857:
3858: ozf_utility_pvt.write_conc_log('-- Populating ozf_activity_customers -- ');
3859:
3860: INSERT INTO ozf_activity_customers
3861: (activity_customer_id,OBJECT_ID,
3862: OBJECT_TYPE,
3856: */
3857:
3858: ozf_utility_pvt.write_conc_log('-- Populating ozf_activity_customers -- ');
3859:
3860: INSERT INTO ozf_activity_customers
3861: (activity_customer_id,OBJECT_ID,
3862: OBJECT_TYPE,
3863: OBJECT_STATUS,
3864: OBJECT_CLASS,
3895: CONFIDENTIAL_FLAG,
3896: CUSTOM_SETUP_ID,
3897: QP_QUALIFIER_ID,
3898: QP_QUALIFIER_GROUP)
3899: SELECT ozf_activity_customers_s.nextval,OBJECT_ID,
3900: OBJECT_TYPE,
3901: OBJECT_STATUS,
3902: OBJECT_CLASS,
3903: PARENT_ID,
3933: CONFIDENTIAL_FLAG,
3934: CUSTOM_SETUP_ID,
3935: QP_QUALIFIER_ID,
3936: QP_QUALIFIER_GROUP
3937: FROM ozf_activity_customers_temp;
3938:
3939: ozf_utility_pvt.write_conc_log('-- Populating ozf_activity_products -- ');
3940:
3941: INSERT INTO ozf_activity_products
4518: IS
4519:
4520: CURSOR c_offer(l_party NUMBER, l_offer NUMBER) IS
4521: SELECT distinct(1)
4522: FROM ozf_activity_customers
4523: WHERE (party_id = l_party
4524: OR party_id = -1)
4525: AND object_id = l_offer
4526: AND object_class = 'OFFR'
4585:
4586: CURSOR c_offer(l_party NUMBER, l_product NUMBER) IS
4587: SELECT object_id
4588: FROM (SELECT distinct object_id
4589: FROM ozf_activity_customers
4590: WHERE (party_id = l_party OR party_id = -1)
4591: AND active_flag = 'Y'
4592: AND ask_for_flag = 'Y'
4593: AND object_class = 'OFFR'