[Home] [Help]
694: -- PROCEDURE
695: -- refresh_netaccrual_products
696: --
697: -- PURPOSE
698: -- Refreshes offer and product denorm table ozf_activity_products
699: -- for NETACCRUAL offers.
700: --
701: -- PARAMETERS
702: -- p_list_header_id: qp_list_header_id of the offer
700: --
701: -- PARAMETERS
702: -- p_list_header_id: qp_list_header_id of the offer
703: -- DESCRIPTION
704: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
705: ----------------------------------------------------------------------
706: PROCEDURE refresh_netaccrual_products(
707: p_api_version IN NUMBER,
708: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
919: -- PROCEDURE
920: -- refresh_volume_products
921: --
922: -- PURPOSE
923: -- Refreshes offer and product denorm table ozf_activity_products
924: -- for NETACCRUAL offers.
925: --
926: -- PARAMETERS
927: -- p_list_header_id: qp_list_header_id of the offer
925: --
926: -- PARAMETERS
927: -- p_list_header_id: qp_list_header_id of the offer
928: -- DESCRIPTION
929: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
930: ----------------------------------------------------------------------
931: PROCEDURE refresh_volume_products(
932: p_api_version IN NUMBER,
933: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1834: -- PROCEDURE
1835: -- refresh_lumpsum_products
1836: --
1837: -- PURPOSE
1838: -- Refreshes offer and product denorm table ozf_activity_products
1839: -- for LUMPSUM and SCAN_DATA offers.
1840: --
1841: -- PARAMETERS
1842: -- p_list_header_id: qp_list_header_id of the offer
1840: --
1841: -- PARAMETERS
1842: -- p_list_header_id: qp_list_header_id of the offer
1843: -- DESCRIPTION
1844: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
1845: ----------------------------------------------------------------------
1846: PROCEDURE refresh_lumpsum_products(
1847: p_api_version IN NUMBER,
1848: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2050: -- PROCEDURE
2051: -- refresh_products
2052: --
2053: -- PURPOSE
2054: -- Refreshes offer and product denorm table ozf_activity_products.
2055: --
2056: -- PARAMETERS
2057: -- p_list_header_id: qp_list_header_id of the offer
2058: -- DESCRIPTION
2055: --
2056: -- PARAMETERS
2057: -- p_list_header_id: qp_list_header_id of the offer
2058: -- DESCRIPTION
2059: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
2060: ----------------------------------------------------------------------
2061: PROCEDURE refresh_products(
2062: p_api_version IN NUMBER,
2063: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2347: SELECT 'Y'
2348: FROM DUAL
2349: WHERE EXISTS (
2350: SELECT 1
2351: FROM ozf_activity_products
2352: WHERE last_update_date > l_date
2353: AND object_id = l_id and object_class = 'OFFR'
2354: );
2355:
2354: );
2355:
2356: CURSOR c_refreshed_offers(l_date DATE) IS
2357: SELECT distinct object_id offer_id, af.forecast_uom_code, oap.currency_code curr_code
2358: from ozf_activity_products oap,
2359: ozf_act_forecasts_all af
2360: where oap.creation_date > l_date
2361: and oap.object_class = 'OFFR'
2362: and af.act_fcast_used_by_id(+) = oap.object_id
2786: DELETE FROM ozf_activity_customers
2787: WHERE object_class = 'OFFR'
2788: and object_id = p_offer_id;
2789:
2790: DELETE FROM ozf_activity_products
2791: WHERE object_class = 'OFFR'
2792: and object_id = p_offer_id;
2793:
2794: DELETE FROM ozf_activity_customers_temp
2794: DELETE FROM ozf_activity_customers_temp
2795: WHERE object_class = 'OFFR'
2796: and object_id = p_offer_id;
2797:
2798: DELETE FROM ozf_activity_products_temp
2799: WHERE object_class = 'OFFR'
2800: and object_id = p_offer_id;
2801: else
2802: DELETE FROM ozf_activity_customers
2801: else
2802: DELETE FROM ozf_activity_customers
2803: WHERE object_class = 'OFFR';
2804:
2805: DELETE FROM ozf_activity_products
2806: WHERE object_class = 'OFFR';
2807:
2808: DELETE FROM ozf_activity_customers_temp
2809: WHERE object_class = 'OFFR';
2807:
2808: DELETE FROM ozf_activity_customers_temp
2809: WHERE object_class = 'OFFR';
2810:
2811: DELETE FROM ozf_activity_products_temp
2812: WHERE object_class = 'OFFR';
2813: end if;
2814:
2815: end if;
2829: DELETE FROM ozf_activity_customers_temp
2830: WHERE object_class = 'OFFR'
2831: AND object_id = i.object_id ;
2832:
2833: DELETE FROM ozf_activity_products_temp
2834: WHERE object_class = 'OFFR'
2835: AND object_id = i.object_id ;
2836:
2837: DELETE FROM ozf_activity_customers
2837: DELETE FROM ozf_activity_customers
2838: WHERE object_class = 'OFFR'
2839: AND object_id = i.object_id;
2840:
2841: DELETE FROM ozf_activity_products
2842: WHERE object_class = 'OFFR'
2843: AND object_id = i.object_id ;
2844: */
2845: IF i.object_status IN ('CANCELLED', 'TERMINATED', 'CLOSED') THEN
2978: --=========================================================================================--
2979: product_denormed := 'N';
2980: FOR x IN c_list_lines(i.object_id) LOOP
2981: FND_DSQL.init;
2982: FND_DSQL.add_text('INSERT INTO ozf_activity_products_temp(');
2983: FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
2984: FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
2985: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
2986: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3147: end if;
3148: end if;
3149:
3150: -- update offer forecast and offer values.
3151: update ozf_activity_products_temp
3152: set forecast_units = s_forecast_units,
3153: forecast_revenue = s_forecast_revenue,
3154: forecast_costs = s_forecast_costs,
3155: forecast_roi = s_forecast_roi,
3322: << GROUP_END2 >>
3323: write_conc_log('end insert party incremental: ' || z.qnum);
3324: END LOOP;
3325: -- products have to be denormed as associated offers are changed
3326: DELETE FROM ozf_activity_products
3327: WHERE object_id = i.object_id and object_class = 'OFFR';
3328:
3329: FOR x IN c_list_lines(i.object_id) LOOP
3330: FND_DSQL.init;
3327: WHERE object_id = i.object_id and object_class = 'OFFR';
3328:
3329: FOR x IN c_list_lines(i.object_id) LOOP
3330: FND_DSQL.init;
3331: FND_DSQL.add_text('INSERT INTO ozf_activity_products(');
3332: FND_DSQL.add_text('activity_product_id,creation_date,created_by,last_update_date,last_updated_by,');
3333: FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
3334: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3335: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3334: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3335: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3336: -- FND_DSQL.add_text('discount_line_id,apply_discount,include_volume,item,item_type) ');
3337: FND_DSQL.add_text('items_category,item,item_type) ');
3338: FND_DSQL.add_text('SELECT ozf_activity_products_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
3339: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
3340: FND_DSQL.add_bind(i.confidential_flag);
3341: FND_DSQL.add_text(',');
3342: FND_DSQL.add_bind(i.custom_setup_id);
3401: FND_DSQL.add_bind(x.lline_id);
3402: FND_DSQL.add_text(' and product_attribute <> ''PRICING_ATTRIBUTE1'' AND excluder_flag = ''N'')');
3403:
3404: /*
3405: DELETE FROM ozf_activity_products -- delete rows that will be refreshed
3406: WHERE object_id = i.object_id and object_class = 'OFFR';
3407: */
3408:
3409: l_denorm_csr := DBMS_SQL.open_cursor;
3537: FETCH c_product_denormed INTO l_dummy;
3538: CLOSE c_product_denormed;
3539:
3540: IF l_dummy IS NULL THEN
3541: DELETE FROM ozf_activity_products -- delete rows that will be refreshed
3542: WHERE object_id = i.object_id and object_class = 'OFFR';
3543:
3544: FOR x IN c_list_lines(i.object_id) LOOP
3545:
3543:
3544: FOR x IN c_list_lines(i.object_id) LOOP
3545:
3546: FND_DSQL.init;
3547: FND_DSQL.add_text('INSERT INTO ozf_activity_products(');
3548: FND_DSQL.add_text('activity_product_id,creation_date,created_by,last_update_date,last_updated_by,');
3549: FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
3550: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3551: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3550: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3551: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3552: -- FND_DSQL.add_text('discount_line_id,apply_discount,include_volume,item,item_type) ');
3553: FND_DSQL.add_text('items_category,item,item_type) ');
3554: FND_DSQL.add_text('SELECT ozf_activity_products_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
3555: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
3556: FND_DSQL.add_bind(i.confidential_flag);
3557: FND_DSQL.add_text(',');
3558: FND_DSQL.add_bind(i.custom_setup_id);
3617: FND_DSQL.add_bind(x.lline_id);
3618: FND_DSQL.add_text(' and product_attribute <> ''PRICING_ATTRIBUTE1'' AND excluder_flag = ''N'')');
3619:
3620:
3621: DELETE FROM ozf_activity_products -- delete rows that will be refreshed
3622: WHERE object_id = i.object_id and object_class = 'OFFR';
3623:
3624: l_denorm_csr := DBMS_SQL.open_cursor;
3625: FND_DSQL.set_cursor(l_denorm_csr);
3714: s_forecast_roi := (sy_forecast_revenue - sy_forecast_costs)/sy_forecast_costs;
3715: end if;
3716: end if;
3717:
3718: update ozf_activity_products
3719: set forecast_units = s_forecast_units,
3720: forecast_revenue = s_forecast_revenue,
3721: forecast_costs = s_forecast_costs,
3722: forecast_roi = s_forecast_roi,
3786: s_forecast_roi := (sy_forecast_revenue - sy_forecast_costs)/sy_forecast_costs;
3787: end if;
3788: end if;
3789:
3790: update ozf_activity_products
3791: set forecast_units = s_forecast_units,
3792: forecast_revenue = s_forecast_revenue,
3793: forecast_costs = s_forecast_costs,
3794: forecast_roi = s_forecast_roi,
3844: s_actual_roi := (sy_actual_revenue - sy_actual_costs)/sy_actual_costs;
3845: end if;
3846: end if;
3847:
3848: update ozf_activity_products
3849: set actual_units = s_actual_units,
3850: actual_revenue = s_actual_revenue,
3851: actual_costs = s_actual_costs,
3852: actual_roi = s_actual_roi,
3877: AND o.qp_list_header_id = l.list_header_id
3878: )
3879: AND object_class = 'OFFR';
3880:
3881: DELETE FROM ozf_activity_products
3882: WHERE object_id IN (
3883: SELECT l.list_header_id
3884: FROM ozf_offers o, qp_list_headers l
3885: WHERE o.status_code IN ('CANCELLED', 'TERMINATED', 'CLOSED')
3895: WHERE O.STATUS_CODE IN ('CANCELLED', 'TERMINATED', 'CLOSED') AND
3896: O.QP_LIST_HEADER_ID = L.LIST_HEADER_ID and
3897: b.object_id = l.list_header_id ) AND OBJECT_CLASS = 'OFFR';
3898:
3899: DELETE FROM OZF_ACTIVITY_PRODUCTS b
3900: WHERE
3901: exists ( SELECT L.LIST_HEADER_ID
3902: FROM OZF_OFFERS O, QP_LIST_HEADERS L
3903: WHERE O.STATUS_CODE IN ('CANCELLED', 'TERMINATED', 'CLOSED') AND
3995: QP_QUALIFIER_ID,
3996: QP_QUALIFIER_GROUP
3997: FROM ozf_activity_customers_temp;
3998:
3999: ozf_utility_pvt.write_conc_log('-- Populating ozf_activity_products -- ');
4000:
4001: INSERT INTO ozf_activity_products
4002: (activity_product_id,
4003: OBJECT_ID,
3997: FROM ozf_activity_customers_temp;
3998:
3999: ozf_utility_pvt.write_conc_log('-- Populating ozf_activity_products -- ');
4000:
4001: INSERT INTO ozf_activity_products
4002: (activity_product_id,
4003: OBJECT_ID,
4004: OBJECT_TYPE,
4005: OBJECT_STATUS,
4043: ACTUAL_UOM,
4044: LIST_PRICE,
4045: DISCOUNT,
4046: ITEMS_CATEGORY)
4047: SELECT ozf_activity_products_s.nextval,
4048: OBJECT_ID,
4049: OBJECT_TYPE,
4050: OBJECT_STATUS,
4051: OBJECT_CLASS,
4088: ACTUAL_UOM,
4089: LIST_PRICE,
4090: DISCOUNT,
4091: ITEMS_CATEGORY
4092: FROM ozf_activity_products_temp;
4093:
4094: END IF;
4095: --utl_file.fflush( a );
4096: --utl_file.fclose( a );
4338: xy_actual_revenue := xy_actual_revenue + y_actual_revenue;
4339: xy_actual_costs := xy_actual_costs + y_actual_costs;
4340:
4341: -- update the actual values for the offer/product
4342: update ozf_activity_products_temp
4343: set actual_product_units = t_conv_actual_units,
4344: actual_product_revenue = l_actual_revenue,
4345: actual_product_costs = l_actual_costs,
4346: actual_product_roi = l_actual_roi,
4529: xy_forecast_revenue := xy_forecast_revenue + y_forecast_revenue;
4530: xy_forecast_costs := xy_forecast_costs + y_forecast_costs;
4531:
4532: -- update the forecast values for the offer/product
4533: update ozf_activity_products_temp
4534: set forecast_product_units = t_forecast_units,
4535: forecast_product_revenue = l_forecast_revenue,
4536: forecast_product_costs = l_forecast_costs,
4537: forecast_product_roi = l_forecast_roi,
4655: AND ask_for_flag = 'Y'
4656: AND object_class = 'OFFR'
4657: INTERSECT
4658: SELECT object_id
4659: FROM ozf_activity_products
4660: WHERE item = l_product
4661: --AND item_type = 'PRODUCT' --fixed bug 7289857
4662: AND object_class = 'OFFR'
4663: AND active_flag = 'Y'
4688: -- p_list_header_id: list_header_id of the offer
4689: -- x_party_stmt: party statement for the offer
4690: -- x_product_stmt: product statement for the offer
4691: -- DESCRIPTION
4692: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
4693: ----------------------------------------------------------------------
4694: PROCEDURE get_party_product_stmt(
4695: p_api_version IN NUMBER,
4696: p_init_msg_list IN VARCHAR2 := FND_API.g_false,