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.OZF_OFFR_ELIG_PROD_DENORM_PVT dependencies on OZF_ACTIVITY_PRODUCTS

Line 701: -- Refreshes offer and product denorm table ozf_activity_products

697: -- PROCEDURE
698: -- refresh_netaccrual_products
699: --
700: -- PURPOSE
701: -- Refreshes offer and product denorm table ozf_activity_products
702: -- for NETACCRUAL offers.
703: --
704: -- PARAMETERS
705: -- p_list_header_id: qp_list_header_id of the offer

Line 707: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products

703: --
704: -- PARAMETERS
705: -- p_list_header_id: qp_list_header_id of the offer
706: -- DESCRIPTION
707: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
708: ----------------------------------------------------------------------
709: PROCEDURE refresh_netaccrual_products(
710: p_api_version IN NUMBER,
711: p_init_msg_list IN VARCHAR2 := FND_API.g_false,

Line 914: -- Refreshes offer and product denorm table ozf_activity_products

910: -- PROCEDURE
911: -- refresh_volume_products
912: --
913: -- PURPOSE
914: -- Refreshes offer and product denorm table ozf_activity_products
915: -- for NETACCRUAL offers.
916: --
917: -- PARAMETERS
918: -- p_list_header_id: qp_list_header_id of the offer

Line 920: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products

916: --
917: -- PARAMETERS
918: -- p_list_header_id: qp_list_header_id of the offer
919: -- DESCRIPTION
920: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
921: ----------------------------------------------------------------------
922: PROCEDURE refresh_volume_products(
923: p_api_version IN NUMBER,
924: p_init_msg_list IN VARCHAR2 := FND_API.g_false,

Line 1815: -- Refreshes offer and product denorm table ozf_activity_products

1811: -- PROCEDURE
1812: -- refresh_lumpsum_products
1813: --
1814: -- PURPOSE
1815: -- Refreshes offer and product denorm table ozf_activity_products
1816: -- for LUMPSUM and SCAN_DATA offers.
1817: --
1818: -- PARAMETERS
1819: -- p_list_header_id: qp_list_header_id of the offer

Line 1821: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products

1817: --
1818: -- PARAMETERS
1819: -- p_list_header_id: qp_list_header_id of the offer
1820: -- DESCRIPTION
1821: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
1822: ----------------------------------------------------------------------
1823: PROCEDURE refresh_lumpsum_products(
1824: p_api_version IN NUMBER,
1825: p_init_msg_list IN VARCHAR2 := FND_API.g_false,

Line 2020: -- Refreshes offer and product denorm table ozf_activity_products.

2016: -- PROCEDURE
2017: -- refresh_products
2018: --
2019: -- PURPOSE
2020: -- Refreshes offer and product denorm table ozf_activity_products.
2021: --
2022: -- PARAMETERS
2023: -- p_list_header_id: qp_list_header_id of the offer
2024: -- DESCRIPTION

Line 2025: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products

2021: --
2022: -- PARAMETERS
2023: -- p_list_header_id: qp_list_header_id of the offer
2024: -- DESCRIPTION
2025: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
2026: ----------------------------------------------------------------------
2027: PROCEDURE refresh_products(
2028: p_api_version IN NUMBER,
2029: p_init_msg_list IN VARCHAR2 := FND_API.g_false,

Line 2294: FROM ozf_activity_products

2290: SELECT 'Y'
2291: FROM DUAL
2292: WHERE EXISTS (
2293: SELECT 1
2294: FROM ozf_activity_products
2295: WHERE last_update_date > l_date
2296: AND object_id = l_id and object_class = 'OFFR'
2297: );
2298:

Line 2301: from ozf_activity_products oap,

2297: );
2298:
2299: CURSOR c_refreshed_offers(l_date DATE) IS
2300: SELECT distinct object_id offer_id, af.forecast_uom_code, oap.currency_code curr_code
2301: from ozf_activity_products oap,
2302: ozf_act_forecasts_all af
2303: where oap.creation_date > l_date
2304: and oap.object_class = 'OFFR'
2305: and af.act_fcast_used_by_id(+) = oap.object_id

Line 2733: DELETE FROM ozf_activity_products

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
2734: WHERE object_class = 'OFFR'
2735: and object_id = p_offer_id;
2736:
2737: DELETE FROM ozf_activity_customers_temp

Line 2741: DELETE FROM ozf_activity_products_temp

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
2742: WHERE object_class = 'OFFR'
2743: and object_id = p_offer_id;
2744: else
2745: DELETE FROM ozf_activity_customers

Line 2748: DELETE FROM ozf_activity_products

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';
2750:
2751: DELETE FROM ozf_activity_customers_temp
2752: WHERE object_class = 'OFFR';

Line 2754: DELETE FROM ozf_activity_products_temp

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';
2756: end if;
2757:
2758: end if;

Line 2776: DELETE FROM ozf_activity_products_temp

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
2777: WHERE object_class = 'OFFR'
2778: AND object_id = i.object_id ;
2779:
2780: DELETE FROM ozf_activity_customers

Line 2784: DELETE FROM ozf_activity_products

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
2785: WHERE object_class = 'OFFR'
2786: AND object_id = i.object_id ;
2787: */
2788: IF i.object_status IN ('CANCELLED', 'TERMINATED', 'CLOSED') THEN

Line 2923: FND_DSQL.add_text('INSERT INTO ozf_activity_products_temp(');

2919: --=========================================================================================--
2920: product_denormed := 'N';
2921: FOR x IN c_list_lines(i.object_id) LOOP
2922: FND_DSQL.init;
2923: FND_DSQL.add_text('INSERT INTO ozf_activity_products_temp(');
2924: FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
2925: FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
2926: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
2927: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');

Line 3091: update ozf_activity_products_temp

3087: end if;
3088: end if;
3089:
3090: -- update offer forecast and offer values.
3091: update ozf_activity_products_temp
3092: set forecast_units = s_forecast_units,
3093: forecast_revenue = s_forecast_revenue,
3094: forecast_costs = s_forecast_costs,
3095: forecast_roi = s_forecast_roi,

Line 3266: DELETE FROM ozf_activity_products

3262: << GROUP_END2 >>
3263: write_conc_log('end insert party incremental: ' || z.qnum);
3264: END LOOP;
3265: -- products have to be denormed as associated offers are changed
3266: DELETE FROM ozf_activity_products
3267: WHERE object_id = i.object_id and object_class = 'OFFR';
3268:
3269: FOR x IN c_list_lines(i.object_id) LOOP
3270: FND_DSQL.init;

Line 3271: FND_DSQL.add_text('INSERT INTO ozf_activity_products(');

3267: WHERE object_id = i.object_id and object_class = 'OFFR';
3268:
3269: FOR x IN c_list_lines(i.object_id) LOOP
3270: FND_DSQL.init;
3271: FND_DSQL.add_text('INSERT INTO ozf_activity_products(');
3272: FND_DSQL.add_text('activity_product_id,creation_date,created_by,last_update_date,last_updated_by,');
3273: FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
3274: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3275: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');

Line 3278: FND_DSQL.add_text('SELECT ozf_activity_products_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');

3274: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3275: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3276: -- FND_DSQL.add_text('discount_line_id,apply_discount,include_volume,item,item_type) ');
3277: FND_DSQL.add_text('items_category,item,item_type) ');
3278: FND_DSQL.add_text('SELECT ozf_activity_products_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
3279: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
3280: FND_DSQL.add_bind(i.confidential_flag);
3281: FND_DSQL.add_text(',');
3282: FND_DSQL.add_bind(i.custom_setup_id);

Line 3345: DELETE FROM ozf_activity_products -- delete rows that will be refreshed

3341: FND_DSQL.add_bind(x.lline_id);
3342: FND_DSQL.add_text(' and product_attribute <> ''PRICING_ATTRIBUTE1'' AND excluder_flag = ''N'')');
3343:
3344: /*
3345: DELETE FROM ozf_activity_products -- delete rows that will be refreshed
3346: WHERE object_id = i.object_id and object_class = 'OFFR';
3347: */
3348:
3349: l_denorm_csr := DBMS_SQL.open_cursor;

Line 3481: DELETE FROM ozf_activity_products -- delete rows that will be refreshed

3477: FETCH c_product_denormed INTO l_dummy;
3478: CLOSE c_product_denormed;
3479:
3480: IF l_dummy IS NULL THEN
3481: DELETE FROM ozf_activity_products -- delete rows that will be refreshed
3482: WHERE object_id = i.object_id and object_class = 'OFFR';
3483:
3484: FOR x IN c_list_lines(i.object_id) LOOP
3485:

Line 3487: FND_DSQL.add_text('INSERT INTO ozf_activity_products(');

3483:
3484: FOR x IN c_list_lines(i.object_id) LOOP
3485:
3486: FND_DSQL.init;
3487: FND_DSQL.add_text('INSERT INTO ozf_activity_products(');
3488: FND_DSQL.add_text('activity_product_id,creation_date,created_by,last_update_date,last_updated_by,');
3489: FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
3490: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3491: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');

Line 3494: FND_DSQL.add_text('SELECT ozf_activity_products_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');

3490: FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
3491: FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,currency_code,marketing_medium_id,start_date,end_date,');
3492: -- FND_DSQL.add_text('discount_line_id,apply_discount,include_volume,item,item_type) ');
3493: FND_DSQL.add_text('items_category,item,item_type) ');
3494: FND_DSQL.add_text('SELECT ozf_activity_products_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
3495: FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,');
3496: FND_DSQL.add_bind(i.confidential_flag);
3497: FND_DSQL.add_text(',');
3498: FND_DSQL.add_bind(i.custom_setup_id);

Line 3561: DELETE FROM ozf_activity_products -- delete rows that will be refreshed

3557: FND_DSQL.add_bind(x.lline_id);
3558: FND_DSQL.add_text(' and product_attribute <> ''PRICING_ATTRIBUTE1'' AND excluder_flag = ''N'')');
3559:
3560:
3561: DELETE FROM ozf_activity_products -- delete rows that will be refreshed
3562: WHERE object_id = i.object_id and object_class = 'OFFR';
3563:
3564: l_denorm_csr := DBMS_SQL.open_cursor;
3565: FND_DSQL.set_cursor(l_denorm_csr);

Line 3658: update ozf_activity_products

3654: s_forecast_roi := (sy_forecast_revenue - sy_forecast_costs)/sy_forecast_costs;
3655: end if;
3656: end if;
3657:
3658: update ozf_activity_products
3659: set forecast_units = s_forecast_units,
3660: forecast_revenue = s_forecast_revenue,
3661: forecast_costs = s_forecast_costs,
3662: forecast_roi = s_forecast_roi,

Line 3730: update ozf_activity_products

3726: s_forecast_roi := (sy_forecast_revenue - sy_forecast_costs)/sy_forecast_costs;
3727: end if;
3728: end if;
3729:
3730: update ozf_activity_products
3731: set forecast_units = s_forecast_units,
3732: forecast_revenue = s_forecast_revenue,
3733: forecast_costs = s_forecast_costs,
3734: forecast_roi = s_forecast_roi,

Line 3788: update ozf_activity_products

3784: s_actual_roi := (sy_actual_revenue - sy_actual_costs)/sy_actual_costs;
3785: end if;
3786: end if;
3787:
3788: update ozf_activity_products
3789: set actual_units = s_actual_units,
3790: actual_revenue = s_actual_revenue,
3791: actual_costs = s_actual_costs,
3792: actual_roi = s_actual_roi,

Line 3821: DELETE FROM ozf_activity_products

3817: AND o.qp_list_header_id = l.list_header_id
3818: )
3819: AND object_class = 'OFFR';
3820:
3821: DELETE FROM ozf_activity_products
3822: WHERE object_id IN (
3823: SELECT l.list_header_id
3824: FROM ozf_offers o, qp_list_headers l
3825: WHERE o.status_code IN ('CANCELLED', 'TERMINATED', 'CLOSED')

Line 3839: DELETE FROM OZF_ACTIVITY_PRODUCTS b

3835: WHERE O.STATUS_CODE IN ('CANCELLED', 'TERMINATED', 'CLOSED') AND
3836: O.QP_LIST_HEADER_ID = L.LIST_HEADER_ID and
3837: b.object_id = l.list_header_id ) AND OBJECT_CLASS = 'OFFR';
3838:
3839: DELETE FROM OZF_ACTIVITY_PRODUCTS b
3840: WHERE
3841: exists ( SELECT L.LIST_HEADER_ID
3842: FROM OZF_OFFERS O, QP_LIST_HEADERS L
3843: WHERE O.STATUS_CODE IN ('CANCELLED', 'TERMINATED', 'CLOSED') AND

Line 3939: ozf_utility_pvt.write_conc_log('-- Populating ozf_activity_products -- ');

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
3942: (activity_product_id,
3943: OBJECT_ID,

Line 3941: INSERT INTO ozf_activity_products

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
3942: (activity_product_id,
3943: OBJECT_ID,
3944: OBJECT_TYPE,
3945: OBJECT_STATUS,

Line 3987: SELECT ozf_activity_products_s.nextval,

3983: ACTUAL_UOM,
3984: LIST_PRICE,
3985: DISCOUNT,
3986: ITEMS_CATEGORY)
3987: SELECT ozf_activity_products_s.nextval,
3988: OBJECT_ID,
3989: OBJECT_TYPE,
3990: OBJECT_STATUS,
3991: OBJECT_CLASS,

Line 4032: FROM ozf_activity_products_temp;

4028: ACTUAL_UOM,
4029: LIST_PRICE,
4030: DISCOUNT,
4031: ITEMS_CATEGORY
4032: FROM ozf_activity_products_temp;
4033:
4034: END IF;
4035: --utl_file.fflush( a );
4036: --utl_file.fclose( a );

Line 4279: update ozf_activity_products_temp

4275: xy_actual_revenue := xy_actual_revenue + y_actual_revenue;
4276: xy_actual_costs := xy_actual_costs + y_actual_costs;
4277:
4278: -- update the actual values for the offer/product
4279: update ozf_activity_products_temp
4280: set actual_product_units = t_conv_actual_units,
4281: actual_product_revenue = l_actual_revenue,
4282: actual_product_costs = l_actual_costs,
4283: actual_product_roi = l_actual_roi,

Line 4470: update ozf_activity_products_temp

4466: xy_forecast_revenue := xy_forecast_revenue + y_forecast_revenue;
4467: xy_forecast_costs := xy_forecast_costs + y_forecast_costs;
4468:
4469: -- update the forecast values for the offer/product
4470: update ozf_activity_products_temp
4471: set forecast_product_units = t_forecast_units,
4472: forecast_product_revenue = l_forecast_revenue,
4473: forecast_product_costs = l_forecast_costs,
4474: forecast_product_roi = l_forecast_roi,

Line 4596: FROM ozf_activity_products

4592: AND ask_for_flag = 'Y'
4593: AND object_class = 'OFFR'
4594: INTERSECT
4595: SELECT object_id
4596: FROM ozf_activity_products
4597: WHERE item = l_product
4598: --AND item_type = 'PRODUCT' --fixed bug 7289857
4599: AND object_class = 'OFFR'
4600: AND active_flag = 'Y'

Line 4629: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products

4625: -- p_list_header_id: list_header_id of the offer
4626: -- x_party_stmt: party statement for the offer
4627: -- x_product_stmt: product statement for the offer
4628: -- DESCRIPTION
4629: -- This procedure calls get_sql, builds SQL statment for product and refresh ozf_activity_products
4630: ----------------------------------------------------------------------
4631: PROCEDURE get_party_product_stmt(
4632: p_api_version IN NUMBER,
4633: p_init_msg_list IN VARCHAR2 := FND_API.g_false,