[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sql_validation_1,
sql_validation_2,
sql_validation_3,
sql_validation_4,
sql_validation_5,
sql_validation_6,
sql_validation_7,
sql_validation_8,
condition_name_column,
condition_id_column
FROM ozf_denorm_queries
WHERE context = p_context
AND attribute = p_attribute
AND query_for = p_type
AND active_flag = 'Y'
AND LAST_UPDATE_DATE = (
SELECT MAX(LAST_UPDATE_DATE)
FROM ozf_denorm_queries
WHERE context = p_context
AND attribute = p_attribute
AND query_for = p_type
AND active_flag = 'Y');
FND_DSQL.add_text('SELECT null items_category, TO_NUMBER(');
l_stmt_1 := 'select null items_category, ' || substr(l_stmt_1,7);
l_stmt_1 := 'select '|| l_category || ' items_category, ' || substr(l_stmt_1,7);
l_stmt_1 := 'select '||
l_qualifier_id ||
' qp_qualifier_id, ' ||
l_qualifier_group||
' qp_qualifier_group, ' ||
substr(l_stmt_1,l_start_position);
PROCEDURE insert_excl_prod(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_context IN VARCHAR2,
p_attribute IN VARCHAR2,
p_attr_value IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR c_prod_stmt IS
SELECT sql_validation_1 || sql_validation_2 || sql_validation_3 || sql_validation_4 || sql_validation_5 || sql_validation_6 || sql_validation_7 || sql_validation_8, condition_id_column
FROM ozf_denorm_queries
WHERE context = p_context
AND attribute = p_attribute
AND query_for = 'PROD'
AND active_flag = 'Y'
AND LAST_UPDATE_DATE = (
SELECT MAX(LAST_UPDATE_DATE)
FROM ozf_denorm_queries
WHERE context = p_context
AND attribute = p_attribute
AND query_for = 'PROD'
AND active_flag = 'Y');
l_api_name CONSTANT VARCHAR2(30) := 'insert_excl_prod';
INSERT INTO ozf_search_selections_t(attribute_value, attribute_id) VALUES(p_attr_value, p_attribute);
l_prod_stmt := 'INSERT INTO ozf_search_selections_t(attribute_value, attribute_id) ' || l_prod_stmt;
write_conc_log('-- insert_excl_prod failed - '|| SQLERRM || ' ' );
END insert_excl_prod;
SELECT sql_validation_1,
sql_validation_2,
sql_validation_3,
sql_validation_4,
sql_validation_5,
sql_validation_6,
sql_validation_7,
sql_validation_8,
condition_name_column,
condition_id_column
FROM ozf_denorm_queries
WHERE context = p_context
AND attribute = p_attribute
AND query_for = p_type
AND active_flag = 'Y'
AND LAST_UPDATE_DATE = (
SELECT MAX(LAST_UPDATE_DATE)
FROM ozf_denorm_queries
WHERE context = p_context
AND attribute = p_attribute
AND query_for = p_type
AND active_flag = 'Y');
FND_DSQL.add_text('SELECT TO_NUMBER(');
l_stmt_1 := 'select '||
p_qualifier_id ||
' qp_qualifier_id ' ||
p_qualifier_group||
' qp_qualifier_group ' ||
substr(l_stmt_1,7);
l_stmt_1 := 'select '||
p_discount_line_id ||
' discount_line_id ' ||
p_apply_discount||
' apply_discount ' ||
p_include_volume||
' include_volume ' ||
l_category || 'items_category' ||
substr(l_stmt_1,7);
SELECT offer_id
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT COUNT(*)
FROM ozf_offer_qualifiers
WHERE offer_id = l_offer_id
AND qualifier_id = nvl(p_qnum,qualifier_id)
AND active_flag = 'Y';
SELECT qualifier_id
FROM ozf_offer_qualifiers
WHERE offer_id = l_offer_id
AND qualifier_id = nvl(p_qnum,qualifier_id)
AND active_flag = 'Y';
SELECT NVL(qualifier_context,
DECODE(qualifier_attribute,
'BUYER', 'CUSTOMER_GROUP',
'CUSTOMER_BILL_TO', 'CUSTOMER',
'CUSTOMER', 'CUSTOMER',
'LIST', 'CUSTOMER_GROUP',
'SEGMENT', 'CUSTOMER_GROUP',
'TERRITORY', 'TERRITORY',
'SHIP_TO', 'CUSTOMER')) qualifier_context,
DECODE(qualifier_attribute,
'BUYER', 'QUALIFIER_ATTRIBUTE3',
'CUSTOMER_BILL_TO', 'QUALIFIER_ATTRIBUTE14',
'CUSTOMER', 'QUALIFIER_ATTRIBUTE2',
'LIST', 'QUALIFIER_ATTRIBUTE1',
'SEGMENT', 'QUALIFIER_ATTRIBUTE2',
'TERRITORY', 'QUALIFIER_ATTRIBUTE1',
'SHIP_TO', 'QUALIFIER_ATTRIBUTE11',
qualifier_attribute) qualifier_attribute,
qualifier_attr_value,
'=' comparison_operator_code
FROM ozf_offer_qualifiers
WHERE qualifier_id = p_qualifier_id;
FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
SELECT off_discount_product_id,
product_id,
product_level
FROM ozf_offer_discount_products
WHERE offer_id = l_used_by_id
AND off_discount_product_id = nvl(p_lline_id, off_discount_product_id)
AND excluder_flag = 'N';
SELECT off_discount_product_id,
product_id,
product_level
FROM ozf_offer_discount_products
WHERE offer_id = l_used_by_id
AND parent_off_disc_prod_id = l_product_id
AND excluder_flag = 'Y';
SELECT COUNT(*)
FROM ozf_offer_discount_products
WHERE offer_id = l_used_by_id
AND off_discount_product_id = nvl(p_lline_id, off_discount_product_id)
AND excluder_flag = 'N';
SELECT COUNT(*)
FROM ozf_offer_discount_products
WHERE offer_id = l_used_by_id
AND parent_off_disc_prod_id = l_product_id
AND excluder_flag = 'Y';
SELECT offer_id
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_search_selections_t';
insert_excl_prod(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_context => l_context,
p_attribute => l_context_attr,
p_attr_value => l_prod_attr_val,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT offer_discount_line_id
FROM ozf_offer_discount_lines
WHERE offer_id = l_offer_id
AND offer_discount_line_id = NVL(p_lline_id, offer_discount_line_id)
AND tier_type = 'PBH';
SELECT COUNT(*)
FROM ozf_offer_discount_lines
WHERE offer_id = l_offer_id
AND offer_discount_line_id = NVL(p_lline_id, offer_discount_line_id)
AND tier_type = 'PBH';
SELECT off_discount_product_id,
product_context,
product_attribute,
product_attr_value,
offer_discount_line_id
FROM ozf_offer_discount_products
WHERE offer_id = l_offer_id
AND offer_discount_line_id = l_offer_discount_line_id
AND excluder_flag = 'N';
SELECT off_discount_product_id,
product_context,
product_attribute,
product_attr_value,
offer_discount_line_id
FROM ozf_offer_discount_products
WHERE offer_id = l_offer_id
AND offer_discount_line_id = l_offer_discount_line_id
AND excluder_flag = 'Y';
SELECT COUNT(*)
FROM ozf_offer_discount_products
WHERE offer_id = l_offer_id
AND offer_discount_line_id = l_offer_discount_line_id
AND excluder_flag = 'N';
SELECT COUNT(*)
FROM ozf_offer_discount_products
WHERE offer_id = l_offer_id
AND offer_discount_line_id = l_offer_discount_line_id
AND excluder_flag = 'Y';
SELECT offer_id
FROM ozf_offers
WHERE qp_list_header_id = l_list_header_id;
EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_search_selections_t';
insert_excl_prod(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_context => k.product_context,
p_attribute => k.product_attribute,
p_attr_value => k.product_attr_value,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT 'Y'
FROM ozf_offers
WHERE qualifier_type IS NOT NULL
AND qualifier_id IS NOT NULL
AND qp_list_header_id = p_list_header_id;
SELECT DECODE(qualifier_type, 'BUYER', 'CUSTOMER_GROUP',
'CUSTOMER', 'CUSTOMER',
'CUSTOMER_BILL_TO', 'CUSTOMER',
'SHIP_TO', 'CUSTOMER') qualifier_context,
DECODE(qualifier_type, 'BUYER', 'QUALIFIER_ATTRIBUTE3',
'CUSTOMER', 'QUALIFIER_ATTRIBUTE2',
'CUSTOMER_BILL_TO', 'QUALIFIER_ATTRIBUTE14',
'SHIP_TO', 'QUALIFIER_ATTRIBUTE11') qualifier_attribute,
qualifier_id qualifier_attr_value
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
SELECT COUNT(*)
FROM qp_qualifiers a,
ozf_denorm_queries b
WHERE a.list_header_id = p_list_header_id
--AND a.active_flag = 'Y'
AND a.qualifier_grouping_no = NVL(p_qnum, a.qualifier_grouping_no)
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND b.query_for = 'ELIG';
SELECT a.qualifier_id,
a.qualifier_grouping_no
FROM qp_qualifiers a,
ozf_denorm_queries b
WHERE a.list_header_id = p_list_header_id
--AND a.active_flag = 'Y'
AND a.qualifier_grouping_no = NVL(p_qnum, a.qualifier_grouping_no)
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND b.query_for = 'ELIG';
SELECT a.qualifier_context,
a.qualifier_attribute,
a.qualifier_attr_value,
a.comparison_operator_code,
a.qualifier_id
FROM qp_qualifiers a,
ozf_denorm_queries b
WHERE a.list_header_id = p_list_header_id
AND a.qualifier_grouping_no = l_grouping_no
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND a.qualifier_context <> 'SOLD_BY'
AND b.query_for = 'ELIG';
SELECT a.qualifier_context,
a.qualifier_attribute,
a.qualifier_attr_value,
a.comparison_operator_code,
a.qualifier_id
FROM qp_qualifiers a,
ozf_denorm_queries b
WHERE a.list_header_id = p_list_header_id
AND a.qualifier_grouping_no = l_grouping_no
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND a.qualifier_context = 'SOLD_BY'
AND a.qualifier_attribute <> 'QUALIFIER_ATTRIBUTE1'
AND b.query_for = 'ELIG';
SELECT COUNT(*)
FROM qp_qualifiers a, ozf_denorm_queries b
WHERE a.list_header_id = p_list_header_id
AND a.qualifier_context <> 'SOLD_BY'
AND a.qualifier_grouping_no = l_grouping_no
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND b.query_for = 'ELIG';
SELECT COUNT(*)
FROM qp_qualifiers a, ozf_denorm_queries b
WHERE a.list_header_id = p_list_header_id
AND a.qualifier_context = 'SOLD_BY'
AND a.qualifier_grouping_no = l_grouping_no
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND a.qualifier_attribute <> 'QUALIFIER_ATTRIBUTE1'
AND b.query_for = 'ELIG';
FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
SELECT DISTINCT a.qualifier_grouping_no
FROM qp_qualifiers a, ozf_denorm_queries b
WHERE a.list_header_id = p_list_header_id
AND a.qualifier_grouping_no = NVL(p_qnum, a.qualifier_grouping_no)
AND a.list_line_id = -1
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND b.query_for = 'ELIG';
SELECT a.qualifier_context,
a.qualifier_attribute,
a.qualifier_attr_value,
a.qualifier_attr_value_to,
a.comparison_operator_code,
a.qualifier_id
FROM qp_qualifiers a,ozf_denorm_queries b
WHERE a.list_header_id = p_list_header_id
AND a.qualifier_grouping_no = l_grouping_no
AND a.list_line_id = -1 -- dont pick up line level qualifier
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND b.query_for = 'ELIG';
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 1
FROM qp_qualifiers
WHERE list_header_id = p_list_header_id
AND list_line_id = -1
AND (qualifier_context,qualifier_attribute) IN
(SELECT DISTINCT context,attribute
FROM ozf_denorm_queries
WHERE query_for = 'ELIG'
AND active_flag = 'Y'));
SELECT COUNT(DISTINCT a.qualifier_grouping_no)
FROM qp_qualifiers a, ozf_denorm_queries b
WHERE a.list_header_id = p_list_header_id
AND a.qualifier_grouping_no = NVL(p_qnum,a.qualifier_grouping_no)
AND a.list_line_id = -1
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND b.query_for = 'ELIG';
SELECT COUNT(*)
FROM qp_qualifiers a, ozf_denorm_queries b
WHERE list_header_id = p_list_header_id
AND qualifier_grouping_no = l_grouping_no
AND list_line_id = -1
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND b.query_for = 'ELIG';
FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
SELECT activity_product_id,
inventory_item_id,
category_id,
level_type_code
FROM ams_act_products
WHERE act_product_used_by_id = l_used_by_id
AND activity_product_id = NVL(p_lline_id, activity_product_id)
AND arc_act_product_used_by = 'OFFR'
AND excluded_flag = 'N'
AND organization_id = l_org_id;
SELECT inventory_item_id,
category_id,
level_type_code
FROM ams_act_products
WHERE act_product_used_by_id = l_used_by_id
AND arc_act_product_used_by = 'PROD'
AND excluded_flag = 'Y'
AND organization_id = l_org_id;
SELECT COUNT(*)
FROM ams_act_products
WHERE act_product_used_by_id = l_used_by_id
AND activity_product_id = NVL(p_lline_id, activity_product_id)
AND arc_act_product_used_by = 'OFFR'
AND excluded_flag = 'N'
AND organization_id = l_org_id;
SELECT COUNT(*)
FROM ams_act_products
WHERE act_product_used_by_id = l_used_by_id
AND arc_act_product_used_by = 'PROD'
AND excluded_flag = 'Y'
AND organization_id = l_org_id;
EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_search_selections_t';
insert_excl_prod(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_context => l_context,
p_attribute => l_context_attr,
p_attr_value => l_prod_attr_val,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT DISTINCT list_line_id
FROM qp_modifier_summary_v
WHERE list_header_id = p_list_header_id
AND list_line_id = NVL(p_lline_id , list_line_id)
AND (end_date_active IS NULL
OR end_date_active >= SYSDATE);
SELECT product_attribute_context,
product_attribute,
product_attr_value
FROM qp_pricing_attributes
WHERE list_header_id = p_list_header_id
AND list_line_id = l_list_line_id
AND excluder_flag = l_excluder_flag;
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT offer_type
FROM ozf_offers
WHERE offer_id = p_list_header_id;
SELECT COUNT(DISTINCT list_line_id)
FROM qp_modifier_summary_v
WHERE list_header_id = p_list_header_id
and list_line_id = nvl(p_lline_id, list_line_id)
AND (end_date_active IS NULL
OR end_date_active >= SYSDATE);
SELECT COUNT(*)
FROM qp_pricing_attributes
WHERE list_header_id = p_list_header_id
AND list_line_id = l_list_line_id
AND excluder_flag = 'Y';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ozf_search_selections_t';
insert_excl_prod(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_context => j.product_attribute_context,
p_attribute => j.product_attribute,
p_attr_value => j.product_attr_value,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM ozf_activity_customers
WHERE last_update_date > l_date
AND object_id = l_id and object_class = 'OFFR'
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM ozf_activity_products
WHERE last_update_date > l_date
AND object_id = l_id and object_class = 'OFFR'
);
SELECT distinct object_id offer_id, af.forecast_uom_code, oap.currency_code curr_code
from ozf_activity_products oap,
ozf_act_forecasts_all af
where oap.creation_date > l_date
and oap.object_class = 'OFFR'
and af.act_fcast_used_by_id(+) = oap.object_id
and af.arc_act_fcast_used_by(+) = oap.object_class
and af.freeze_flag(+) = 'Y';
SELECT o.qp_list_header_id object_id,
o.offer_type object_type,
o.status_code object_status,
'OFFR' object_class,
l.description object_desc,
ao.act_offer_used_by_id parent_id,
ao.arc_act_offer_used_by parent_class,
ct.campaign_name parent_desc,
l.ask_for_flag,
DECODE(o.status_code, 'ACTIVE', 'Y', 'N') active_flag,--l.active_flag,
o.offer_code source_code,
o.activity_media_id,
l.start_date_active start_date,
l.end_date_active end_date,
o.confidential_flag,
o.custom_setup_id,
af.forecast_uom_code,
o.fund_request_curr_code curr_code
FROM ozf_offers o,
qp_list_headers l,
ozf_act_offers ao,
ams_campaigns_vl ct,
ozf_act_forecasts_all af
WHERE o.qp_list_header_id = NVL(p_offer_id,o.qp_list_header_id)
and o.qp_list_header_id = l.list_header_id
and ao.qp_list_header_id(+) = decode(o.reusable,'N', o.qp_list_header_id)
and ao.arc_act_offer_used_by(+) = 'CAMP'
and ao.act_offer_used_by_id = ct.campaign_id(+)
and af.act_fcast_used_by_id(+) = l.list_header_id
and af.arc_act_fcast_used_by(+) = 'OFFR'
and af.freeze_flag(+) = 'Y';
SELECT 'Y'
FROM ozf_offers
WHERE qp_list_header_id = l_list_header_id
AND (last_update_date > l_date OR qualifier_deleted = 'Y');
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM qp_qualifiers
WHERE list_header_id = l_list_header_id
AND (
last_update_date > l_date -- changed qualifiers
OR ( -- changed lists
qualifier_context = 'CUSTOMER_GROUP'
AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE1'
AND qualifier_attr_value IN (
SELECT list_header_id
FROM ams_list_entries
WHERE last_update_date > l_date
)
)
OR ( -- changed segments
qualifier_context = 'CUSTOMER_GROUP'
AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE2'
AND qualifier_attr_value IN (
SELECT ams_party_market_segment_id
FROM ams_party_market_segments
WHERE last_update_date > l_date
)
)
)
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM qp_pricing_attributes
WHERE last_update_date > l_date
AND list_header_id = l_list_header_id
);
select distinct adj.list_header_id offer_id,
af.forecast_uom_code
from oe_price_adjustments adj,
oe_order_lines line,
ozf_act_forecasts_all af
where adj.line_id = line.line_id
and line.open_flag = 'N'
and line.cancelled_flag = 'N'
and line.actual_shipment_date > l_date
and af.act_fcast_used_by_id(+) = adj.list_header_id
and af.arc_act_fcast_used_by(+) = 'OFFR'
and af.freeze_flag(+) = 'Y';
select act_fcast_used_by_id offer_id,
forecast_uom_code
from ozf_act_forecasts_all
where last_update_date > l_date
and arc_act_fcast_used_by = 'OFFR'
and freeze_flag(+) = 'Y';
select primary_uom_code
from MTL_SYSTEM_ITEMS_B
where inventory_item_id = l_inventory_item_id
and organization_id = l_org_id
and enabled_flag = 'Y';
SELECT 'ITEM' product_attribute_context,
'PRICING_ATTRIBUTE1' product_attribute,
line.inventory_item_id product_attr_value,
sum(NVL(line.shipped_quantity, line.ordered_quantity)) actual_units,
sum(( NVL(line.shipped_quantity, line.ordered_quantity))
* line.unit_list_price) actual_amount,
adj.arithmetic_operator,
adj.operand,
CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL) cost,
line.order_quantity_uom,
head.transactional_curr_code order_currency,
NVL(line.actual_shipment_date, line.request_date) trans_date
FROM oe_price_adjustments adj,
oe_order_lines_all line,
oe_order_headers_all head
WHERE adj.list_header_id = l_offer_id
AND adj.line_id = line.line_id
AND line.open_flag = 'N'
AND line.cancelled_flag = 'N'
AND line.header_id = head.header_id
group by line.inventory_item_id,
adj.arithmetic_operator,
adj.operand,
CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL),
line.order_quantity_uom,
head.transactional_curr_code,
NVL(line.actual_shipment_date, line.request_date);
SELECT 'ITEM' product_attribute_context,
'PRICING_ATTRIBUTE1' product_attribute,
line.inventory_item_id product_attr_value,
sum(NVL(line.shipped_quantity, line.ordered_quantity)) actual_units,
sum(( NVL(line.shipped_quantity, line.ordered_quantity))
* line.unit_list_price) actual_amount,
adj.arithmetic_operator,
adj.operand,
--CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL) cost,
line.order_quantity_uom,
head.transactional_curr_code order_currency,
NVL(line.actual_shipment_date, line.request_date) trans_date
FROM oe_price_adjustments adj,
oe_order_lines_all line,
oe_order_headers_all head
WHERE adj.list_header_id = l_offer_id
AND adj.line_id = line.line_id
AND line.open_flag = 'N'
AND line.cancelled_flag = 'N'
AND line.header_id = head.header_id
group by line.inventory_item_id,
adj.arithmetic_operator,
adj.operand,
--CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL),
line.order_quantity_uom,
head.transactional_curr_code,
NVL(line.actual_shipment_date, line.request_date);
SELECT fc.price_list_id,
fm.fact_value forecast_units,
fp.product_attribute_context,
fp.product_attribute,
fp.product_attr_value,
fc.forecast_uom_code uom,
CST_COST_API.get_item_cost(1, fp.product_attr_value, l_org_id, NULL,NULL) cost,
ql.arithmetic_operator,
ql.operand,
ao.transaction_currency_code,
ao.fund_request_curr_code transaction_currency_code
FROM
ozf_act_forecasts_all fc,
ozf_act_metric_facts_all fm,
ozf_forecast_dimentions fp,
qp_pricing_attributes qa,
qp_list_lines ql,
ozf_offers ao
WHERE fp.obj_id = l_offer_id
and fp.obj_type = 'OFFR'
and fc.act_fcast_used_by_id = fp.obj_id
and fc.last_scenario_id = (select max(last_scenario_id)
from ozf_act_forecasts_all
where act_fcast_used_by_id = l_offer_id
and freeze_flag = 'Y')
and fm.act_metric_used_by_id = fc.forecast_id
and fm.arc_act_metric_used_by = 'FCST'
and fm.fact_type = 'PRODUCT'
and fm.fact_reference = fp.forecast_dimention_id
and qa.list_header_id = fp.obj_id
and qa.product_attribute_context = fp.product_attribute_context
and qa.product_attribute = fp.product_attribute
and qa.product_attr_value = fp.product_attr_value
and ql.list_line_id = qa.list_line_id
and ql.list_header_id = qa.list_header_id
and ao.qp_list_header_id = fp.obj_id;
SELECT CQL.item_cost cost
FROM cst_quantity_layers CQL,
mtl_parameters MP
WHERE CQL.inventory_item_id = l_inv_item_id AND
CQL.organization_id = l_org_id AND
CQL.cost_group_id = MP.default_cost_group_id AND
MP.organization_id = CQL.organization_id;
SELECT DISTINCT list_line_id lline_id
FROM qp_modifier_summary_v a, ozf_offers b
WHERE a.list_header_id = ll_list_header_id
AND b.qp_list_header_id = a.list_header_id
AND b.offer_type <> 'VOLUME_OFFER'
AND (a.end_date_active IS NULL
OR a.end_date_active >= SYSDATE)
UNION
SELECT off_discount_product_id lline_id
FROM ozf_offer_discount_products a, ozf_offers b
WHERE b.qp_list_header_id = ll_list_header_id
AND a.offer_id = b.offer_id
and b.offer_type = 'NET_ACCRUAL'
AND a.excluder_flag = 'N'
AND (a.end_date_active IS NULL
OR a.end_date_active >= SYSDATE)
UNION
SELECT activity_product_id lline_id
FROM ams_act_products
WHERE act_product_used_by_id = ll_list_header_id
AND arc_act_product_used_by = 'OFFR'
AND excluded_flag = 'N'
UNION
SELECT distinct offer_discount_line_id lline_id
FROM ozf_offer_discount_lines a, ozf_offers b
WHERE b.qp_list_header_id = ll_list_header_id
AND a.offer_id = b.offer_id
AND b.offer_type = 'VOLUME_OFFER'
and a.tier_type = 'PBH'
AND (a.end_date_active IS NULL
OR a.end_date_active >= SYSDATE)
;
SELECT COUNT(DISTINCT list_line_id)
FROM qp_modifier_summary_v
WHERE list_header_id = ll_list_header_id
AND (end_date_active IS NULL
OR end_date_active >= SYSDATE);
select qnum from
(
SELECT DISTINCT a.qualifier_grouping_no qnum
FROM qp_qualifiers a, ozf_denorm_queries b
WHERE a.list_header_id = ll_list_header_id
AND a.list_line_id = -1
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND b.query_for = 'ELIG'
UNION
SELECT a.qualifier_id qnum
FROM ozf_offer_qualifiers a, ozf_offers b
WHERE b.qp_list_header_id = ll_list_header_id
AND a.offer_id = b.offer_id
AND a.active_flag = 'Y'
UNION
SELECT qualifier_id qnum
FROM ozf_offers
WHERE qp_list_header_id = ll_list_header_id
AND offer_type in ('SCAN_DATA', 'LUMPSUM')
UNION
select -99 qnum
FROM dual
) order by qnum desc;
SELECT COUNT(DISTINCT a.qualifier_grouping_no)
FROM qp_qualifiers a, ozf_denorm_queries b
WHERE a.list_header_id = ll_list_header_id
AND a.list_line_id = -1
AND a.qualifier_context = b.context
AND a.qualifier_attribute = b.attribute
AND b.query_for = 'ELIG'
UNION
SELECT count(a.qualifier_id)
FROM ozf_offer_qualifiers a, ozf_offers b
WHERE b.qp_list_header_id = ll_list_header_id
AND a.offer_id = b.offer_id
AND a.active_flag = 'Y';
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 1
FROM qp_qualifiers
WHERE list_header_id = ll_list_header_id
AND list_line_id = -1
AND (qualifier_context,qualifier_attribute) IN
(SELECT DISTINCT context,attribute
FROM ozf_denorm_queries
WHERE query_for = 'ELIG'
AND active_flag = 'Y'));
DELETE FROM ozf_activity_customers
WHERE object_class = 'OFFR'
and object_id = p_offer_id;
DELETE FROM ozf_activity_products
WHERE object_class = 'OFFR'
and object_id = p_offer_id;
DELETE FROM ozf_activity_customers_temp
WHERE object_class = 'OFFR'
and object_id = p_offer_id;
DELETE FROM ozf_activity_products_temp
WHERE object_class = 'OFFR'
and object_id = p_offer_id;
DELETE FROM ozf_activity_customers
WHERE object_class = 'OFFR';
DELETE FROM ozf_activity_products
WHERE object_class = 'OFFR';
DELETE FROM ozf_activity_customers_temp
WHERE object_class = 'OFFR';
DELETE FROM ozf_activity_products_temp
WHERE object_class = 'OFFR';
DELETE FROM ozf_activity_customers_temp
WHERE object_class = 'OFFR'
AND object_id = i.object_id ;
DELETE FROM ozf_activity_products_temp
WHERE object_class = 'OFFR'
AND object_id = i.object_id ;
DELETE FROM ozf_activity_customers
WHERE object_class = 'OFFR'
AND object_id = i.object_id;
DELETE FROM ozf_activity_products
WHERE object_class = 'OFFR'
AND object_id = i.object_id ;
GOTO END_INSERT;
FND_DSQL.add_text('INSERT INTO ozf_activity_customers_temp(');
FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,last_update_login,');
FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group,-1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
UPDATE ozf_offers
SET qualifier_deleted = 'N'
WHERE qp_list_header_id = i.object_id;
write_conc_log('end insert party fresh denorm: ' || z.qnum);
FND_DSQL.add_text('INSERT INTO ozf_activity_products_temp(');
FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
FND_DSQL.add_text(' UNION ALL SELECT distinct null items_category, to_number(decode(product_attr_value,''ALL'',''-9999'',product_attr_value)) product_id, ');
/*---------- Start forecast update ----------------*/
--ozf_utility_pvt.write_conc_log(' Start forecast');
update ozf_activity_products_temp
set forecast_units = s_forecast_units,
forecast_revenue = s_forecast_revenue,
forecast_costs = s_forecast_costs,
forecast_roi = s_forecast_roi,
forecast_uom = i.forecast_uom_code,
actual_units = s_actual_units,
actual_revenue = s_actual_revenue,
actual_costs = s_actual_costs,
actual_roi = s_actual_roi,
actual_uom = l_uom_code
where object_id = i.object_id
and object_class = 'OFFR';
update ozf_activity_customers_temp
set forecast_units = s_forecast_units,
forecast_revenue = s_forecast_revenue,
forecast_costs = s_forecast_costs,
forecast_roi = s_forecast_roi,
forecast_uom = i.forecast_uom_code,
actual_units = s_actual_units,
actual_revenue = s_actual_revenue,
actual_costs = s_actual_costs,
actual_roi = s_actual_roi,
actual_uom = l_uom_code
where object_id = i.object_id
and object_class = 'OFFR';
/*---------- End forecast update ----------------*/
END IF;
<< END_INSERT >>
ozf_utility_pvt.write_conc_log('-- Done for Offer Id : '|| i.object_id );
DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
WHERE object_id = i.object_id and object_class = 'OFFR';
FND_DSQL.add_text('INSERT INTO ozf_activity_customers(');
FND_DSQL.add_text('activity_customer_id,creation_date,created_by,last_update_date,last_updated_by,');
FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
FND_DSQL.add_text('SELECT ozf_activity_customers_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
FND_DSQL.add_text(' UNION select -1 qp_qualifier_id,-1 qp_qualifier_group,-1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
WHERE object_id = i.object_id and object_class = 'OFFR';
UPDATE ozf_offers
SET qualifier_deleted = 'N'
WHERE qp_list_header_id = i.object_id;
write_conc_log('end insert party incremental: ' || z.qnum);
DELETE FROM ozf_activity_products
WHERE object_id = i.object_id and object_class = 'OFFR';
FND_DSQL.add_text('INSERT INTO ozf_activity_products(');
FND_DSQL.add_text('activity_product_id,creation_date,created_by,last_update_date,last_updated_by,');
FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
FND_DSQL.add_text('SELECT ozf_activity_products_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
FND_DSQL.add_text(' UNION ALL SELECT distinct null items_category, to_number(decode(product_attr_value,''ALL'',''-9999'',product_attr_value)) product_id, ');
DELETE FROM ozf_activity_products -- delete rows that will be refreshed
WHERE object_id = i.object_id and object_class = 'OFFR';
DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
WHERE object_id = i.object_id and object_class = 'OFFR';
FND_DSQL.add_text('INSERT INTO ozf_activity_customers(');
FND_DSQL.add_text('activity_customer_id,creation_date,created_by,last_update_date,last_updated_by,');
FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
FND_DSQL.add_text('SELECT ozf_activity_customers_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group,-1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
DELETE FROM ozf_activity_customers -- delete rows that will be refreshed
WHERE object_id = i.object_id and object_class = 'OFFR';
write_conc_log('end insert party changed qualifier: ' || z.qnum);
DELETE FROM ozf_activity_products -- delete rows that will be refreshed
WHERE object_id = i.object_id and object_class = 'OFFR';
FND_DSQL.add_text('INSERT INTO ozf_activity_products(');
FND_DSQL.add_text('activity_product_id,creation_date,created_by,last_update_date,last_updated_by,');
FND_DSQL.add_text('last_update_login,confidential_flag,custom_setup_id,');
FND_DSQL.add_text('SELECT ozf_activity_products_s.nextval,SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
FND_DSQL.add_text(' UNION ALL SELECT distinct null items_category, to_number(decode(product_attr_value,''ALL'',''-9999'',product_attr_value)) product_id, ');
DELETE FROM ozf_activity_products -- delete rows that will be refreshed
WHERE object_id = i.object_id and object_class = 'OFFR';
update ozf_activity_products
set forecast_units = s_forecast_units,
forecast_revenue = s_forecast_revenue,
forecast_costs = s_forecast_costs,
forecast_roi = s_forecast_roi,
forecast_uom = i.forecast_uom_code,
actual_units = s_actual_units,
actual_revenue = s_actual_revenue,
actual_costs = s_actual_costs,
actual_roi = s_actual_roi,
actual_uom = l_uom_code
where object_id = i.offer_id
and object_class = 'OFFR';
update ozf_activity_customers
set forecast_units = s_forecast_units,
forecast_revenue = s_forecast_revenue,
forecast_costs = s_forecast_costs,
forecast_roi = s_forecast_roi,
forecast_uom = i.forecast_uom_code,
actual_units = s_actual_units,
actual_revenue = s_actual_revenue,
actual_costs = s_actual_costs,
actual_roi = s_actual_roi,
actual_uom = l_uom_code
where object_id = i.offer_id
and object_class = 'OFFR';
update ozf_activity_products
set forecast_units = s_forecast_units,
forecast_revenue = s_forecast_revenue,
forecast_costs = s_forecast_costs,
forecast_roi = s_forecast_roi,
forecast_uom = i.forecast_uom_code
where object_id = i.offer_id
and object_class = 'OFFR';
update ozf_activity_customers
set forecast_units = s_forecast_units,
forecast_revenue = s_forecast_revenue,
forecast_costs = s_forecast_costs,
forecast_roi = s_forecast_roi,
forecast_uom = i.forecast_uom_code
where object_id = i.offer_id
and object_class = 'OFFR';
update ozf_activity_products
set actual_units = s_actual_units,
actual_revenue = s_actual_revenue,
actual_costs = s_actual_costs,
actual_roi = s_actual_roi,
actual_uom = l_uom_code
where object_id = i.offer_id
and object_class = 'OFFR';
update ozf_activity_customers
set forecast_units = s_forecast_units,
forecast_revenue = s_forecast_revenue,
forecast_costs = s_forecast_costs,
forecast_roi = s_forecast_roi,
forecast_uom = i.forecast_uom_code
where object_id = i.offer_id
and object_class = 'OFFR';
DELETE FROM ozf_activity_customers
WHERE object_id IN (
SELECT l.list_header_id
FROM ozf_offers o, qp_list_headers l
WHERE o.status_code IN ('CANCELLED', 'TERMINATED', 'CLOSED')
AND o.qp_list_header_id = l.list_header_id
)
AND object_class = 'OFFR';
DELETE FROM ozf_activity_products
WHERE object_id IN (
SELECT l.list_header_id
FROM ozf_offers o, qp_list_headers l
WHERE o.status_code IN ('CANCELLED', 'TERMINATED', 'CLOSED')
AND o.qp_list_header_id = l.list_header_id
)
AND object_class = 'OFFR';
DELETE FROM OZF_ACTIVITY_CUSTOMERS b
WHERE
exists ( SELECT L.LIST_HEADER_ID
FROM OZF_OFFERS O, QP_LIST_HEADERS L
WHERE O.STATUS_CODE IN ('CANCELLED', 'TERMINATED', 'CLOSED') AND
O.QP_LIST_HEADER_ID = L.LIST_HEADER_ID and
b.object_id = l.list_header_id ) AND OBJECT_CLASS = 'OFFR';
DELETE FROM OZF_ACTIVITY_PRODUCTS b
WHERE
exists ( SELECT L.LIST_HEADER_ID
FROM OZF_OFFERS O, QP_LIST_HEADERS L
WHERE O.STATUS_CODE IN ('CANCELLED', 'TERMINATED', 'CLOSED') AND
O.QP_LIST_HEADER_ID = L.LIST_HEADER_ID and
b.object_id = l.list_header_id ) AND OBJECT_CLASS = 'OFFR';
SELECT i.index_tablespace INTO l_index_tablespace
FROM fnd_product_installations i, fnd_application a
WHERE a.application_short_name = 'AMS'
AND a.application_id = i.application_id;
INSERT INTO ozf_activity_customers
(activity_customer_id,OBJECT_ID,
OBJECT_TYPE,
OBJECT_STATUS,
OBJECT_CLASS,
PARENT_ID,
PARENT_CLASS,
PARENT_DESC,
ASK_FOR_FLAG,
ACTIVE_FLAG,
SOURCE_CODE,
CURRENCY_CODE,
MARKETING_MEDIUM_ID,
START_DATE,
END_DATE,
PARTY_ID,
CUST_ACCOUNT_ID,
CUST_ACCT_SITE_ID,
SITE_USE_CODE,
SITE_USE_ID,
QUALIFIER_CONTEXT,
QUALIFIER_ATTRIBUTE,
FORECAST_UNITS,
FORECAST_REVENUE,
FORECAST_COSTS,
FORECAST_ROI,
ACTUAL_UNITS,
ACTUAL_REVENUE,
ACTUAL_COSTS,
ACTUAL_ROI,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CONFIDENTIAL_FLAG,
CUSTOM_SETUP_ID,
QP_QUALIFIER_ID,
QP_QUALIFIER_GROUP)
SELECT ozf_activity_customers_s.nextval,OBJECT_ID,
OBJECT_TYPE,
OBJECT_STATUS,
OBJECT_CLASS,
PARENT_ID,
PARENT_CLASS,
PARENT_DESC,
ASK_FOR_FLAG,
ACTIVE_FLAG,
SOURCE_CODE,
CURRENCY_CODE,
MARKETING_MEDIUM_ID,
START_DATE,
END_DATE,
PARTY_ID,
CUST_ACCOUNT_ID,
CUST_ACCT_SITE_ID,
SITE_USE_CODE,
SITE_USE_ID,
QUALIFIER_CONTEXT,
QUALIFIER_ATTRIBUTE,
FORECAST_UNITS,
FORECAST_REVENUE,
FORECAST_COSTS,
FORECAST_ROI,
ACTUAL_UNITS,
ACTUAL_REVENUE,
ACTUAL_COSTS,
ACTUAL_ROI,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CONFIDENTIAL_FLAG,
CUSTOM_SETUP_ID,
QP_QUALIFIER_ID,
QP_QUALIFIER_GROUP
FROM ozf_activity_customers_temp;
INSERT INTO ozf_activity_products
(activity_product_id,
OBJECT_ID,
OBJECT_TYPE,
OBJECT_STATUS,
OBJECT_CLASS,
PARENT_ID,
PARENT_CLASS,
PARENT_DESC,
ASK_FOR_FLAG,
ACTIVE_FLAG,
SOURCE_CODE,
CURRENCY_CODE,
MARKETING_MEDIUM_ID,
START_DATE,
END_DATE,
ITEM,
ITEM_TYPE,
FORECAST_UNITS,
FORECAST_REVENUE,
FORECAST_COSTS,
FORECAST_ROI,
ACTUAL_UNITS,
ACTUAL_REVENUE,
ACTUAL_COSTS,
ACTUAL_ROI,
FORECAST_PRODUCT_UNITS,
FORECAST_PRODUCT_REVENUE,
FORECAST_PRODUCT_COSTS,
FORECAST_PRODUCT_ROI,
ACTUAL_PRODUCT_UNITS,
ACTUAL_PRODUCT_REVENUE,
ACTUAL_PRODUCT_COSTS,
ACTUAL_PRODUCT_ROI,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CONFIDENTIAL_FLAG,
CUSTOM_SETUP_ID,
FORECAST_UOM,
ACTUAL_UOM,
LIST_PRICE,
DISCOUNT,
ITEMS_CATEGORY)
SELECT ozf_activity_products_s.nextval,
OBJECT_ID,
OBJECT_TYPE,
OBJECT_STATUS,
OBJECT_CLASS,
PARENT_ID,
PARENT_CLASS,
PARENT_DESC,
ASK_FOR_FLAG,
ACTIVE_FLAG,
SOURCE_CODE,
CURRENCY_CODE,
MARKETING_MEDIUM_ID,
START_DATE,
END_DATE,
ITEM,
ITEM_TYPE,
FORECAST_UNITS,
FORECAST_REVENUE,
FORECAST_COSTS,
FORECAST_ROI,
ACTUAL_UNITS,
ACTUAL_REVENUE,
ACTUAL_COSTS,
ACTUAL_ROI,
FORECAST_PRODUCT_UNITS,
FORECAST_PRODUCT_REVENUE,
FORECAST_PRODUCT_COSTS,
FORECAST_PRODUCT_ROI,
ACTUAL_PRODUCT_UNITS,
ACTUAL_PRODUCT_REVENUE,
ACTUAL_PRODUCT_COSTS,
ACTUAL_PRODUCT_ROI,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CONFIDENTIAL_FLAG,
CUSTOM_SETUP_ID,
FORECAST_UOM,
ACTUAL_UOM,
LIST_PRICE,
DISCOUNT,
ITEMS_CATEGORY
FROM ozf_activity_products_temp;
SELECT 'ITEM' product_attribute_context,
'PRICING_ATTRIBUTE1' product_attribute,
line.inventory_item_id product_attr_value,
sum(NVL(line.shipped_quantity, line.ordered_quantity)) actual_units,
sum(( NVL(line.shipped_quantity, line.ordered_quantity))* line.unit_list_price) actual_amount,
adj.arithmetic_operator,
adj.operand,
CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL) cost,
line.order_quantity_uom,
head.transactional_curr_code order_currency,
NVL(line.actual_shipment_date,line.request_date) trans_date
FROM oe_price_adjustments adj,
oe_order_lines_all line,
oe_order_headers_all head
WHERE adj.list_header_id = l_offer_id
AND adj.line_id = line.line_id
AND line.open_flag = 'N'
AND line.cancelled_flag = 'N'
AND line.header_id = head.header_id
GROUP BY line.inventory_item_id,
adj.arithmetic_operator,
adj.operand,
CST_COST_API.get_item_cost(1, line.inventory_item_id, l_org_id, NULL,NULL),
line.order_quantity_uom,
head.transactional_curr_code,
NVL(line.actual_shipment_date,line.request_date);
select primary_uom_code
from MTL_SYSTEM_ITEMS_B
where inventory_item_id = l_inventory_item_id
and organization_id = l_org_id
and enabled_flag = 'Y';
SELECT CQL.item_cost cost
FROM cst_quantity_layers CQL,
mtl_parameters MP
WHERE CQL.inventory_item_id = l_inv_item_id AND
CQL.organization_id = l_org_id AND
CQL.cost_group_id = MP.default_cost_group_id AND
MP.organization_id = CQL.organization_id;
update ozf_activity_products_temp
set actual_product_units = t_conv_actual_units,
actual_product_revenue = l_actual_revenue,
actual_product_costs = l_actual_costs,
actual_product_roi = l_actual_roi,
actual_uom = p_uom_code,
discount = l_discount
where object_id = p_offer_id
and object_class = 'OFFR'
and item = j.product_attr_value
and item_type = j.product_attribute;
SELECT fc.price_list_id,
fm.fact_value forecast_units,
fp.product_attribute_context,
fp.product_attribute,
fp.product_attr_value,
fc.forecast_uom_code uom,
CST_COST_API.get_item_cost(1, fp.product_attr_value, l_org_id, NULL,NULL) cost,
ql.arithmetic_operator,
ql.operand,
ao.transaction_currency_code,
fc.forecast_id
FROM
ozf_act_forecasts_all fc,
ozf_act_metric_facts_all fm,
ozf_forecast_dimentions fp,
qp_pricing_attributes qa,
qp_list_lines ql,
ozf_offers ao
WHERE fp.obj_id = l_offer_id
and fp.obj_type = 'OFFR'
and fc.act_fcast_used_by_id = fp.obj_id
and fc.last_scenario_id = (select max(last_scenario_id)
from ozf_act_forecasts_all
where act_fcast_used_by_id = l_offer_id
and freeze_flag = 'Y')
and fm.act_metric_used_by_id = fc.forecast_id
and fm.arc_act_metric_used_by = 'FCST'
and fm.fact_type = 'PRODUCT'
and fm.fact_reference = fp.forecast_dimention_id
and qa.list_header_id = fp.obj_id
and qa.product_attribute_context = fp.product_attribute_context
and qa.product_attribute = fp.product_attribute
and qa.product_attr_value = fp.product_attr_value
and ql.list_line_id = qa.list_line_id
and ql.list_header_id = qa.list_header_id
and ao.qp_list_header_id = fp.obj_id;
select primary_uom_code
from MTL_SYSTEM_ITEMS_B
where inventory_item_id = l_inventory_item_id
and organization_id = l_org_id
and enabled_flag = 'Y';
update ozf_activity_products_temp
set forecast_product_units = t_forecast_units,
forecast_product_revenue = l_forecast_revenue,
forecast_product_costs = l_forecast_costs,
forecast_product_roi = l_forecast_roi,
forecast_uom = p_forecast_uom_code,
list_price = l_list_price,
discount = l_discount
where object_id = p_offer_id
and object_class = 'OFFR'
and item = j.product_attr_value
and item_type = j.product_attribute;
SELECT distinct(1)
FROM ozf_activity_customers
WHERE (party_id = l_party
OR party_id = -1)
AND object_id = l_offer
AND object_class = 'OFFR'
AND active_flag = 'Y'
AND ask_for_flag = 'Y'
AND (start_date <= TRUNC(SYSDATE)
OR start_date IS NULL)
AND (end_date >= TRUNC(SYSDATE)
OR end_date IS NULL);
SELECT object_id
FROM (SELECT distinct object_id
FROM ozf_activity_customers
WHERE (party_id = l_party OR party_id = -1)
AND active_flag = 'Y'
AND ask_for_flag = 'Y'
AND object_class = 'OFFR'
INTERSECT
SELECT object_id
FROM ozf_activity_products
WHERE item = l_product
--AND item_type = 'PRODUCT' --fixed bug 7289857
AND object_class = 'OFFR'
AND active_flag = 'Y'
AND ask_for_flag = 'Y');
x_party_stmt := 'select distinct(party_id) from ('||x_party_stmt||' )';
x_product_stmt := 'select distinct(product_id) from ('||x_product_stmt||' )';