The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_action_summary (
p_arc_object_for IN VARCHAR2,
p_object_for_id IN NUMBER,
p_seeded_data_source IN BOOLEAN,
x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE update_source_target (
p_object_type IN VARCHAR2,
p_object_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE get_insert_fields (
p_select_object_type IN VARCHAR2,
p_select_object_id IN NUMBER,
p_workbook_owner IN VARCHAR2,
p_workbook_name IN VARCHAR2,
p_worksheet_name IN VARCHAR2,
x_insert_fields OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_pk_field OUT NOCOPY VARCHAR2
);
p_select_object_type IN VARCHAR2,
p_select_object_id IN NUMBER,
p_workbook_owner IN VARCHAR2,
p_workbook_name IN VARCHAR2,
p_worksheet_name IN VARCHAR2,
p_is_b2b_custprof IN BOOLEAN,
x_from_clause OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
);
p_select_object_type IN VARCHAR2,
p_select_object_id IN NUMBER,
p_workbook_owner IN VARCHAR2,
p_workbook_name IN VARCHAR2,
p_worksheet_name IN VARCHAR2,
p_is_b2b_custprof IN BOOLEAN,
x_where_clause OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE validate_selection_status (
p_model_id IN NUMBER,
p_model_type IN VARCHAR2,
p_workbook_owner IN VARCHAR2,
p_workbook_name IN VARCHAR2,
p_worksheet_name IN VARCHAR2,
p_select_object_type IN VARCHAR2,
p_select_object_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE validate_product_selections (
p_model_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
);
PROCEDURE Preview_Selections (
p_arc_object IN VARCHAR2,
p_object_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Preview Selections';
SELECT target_id
FROM ams_dm_models_all_b
WHERE model_id = p_model_id
;
SELECT model.target_id
FROM ams_dm_scores_all_b score, ams_dm_models_all_b model
WHERE score.score_id = p_score_id
AND model.model_id = score.model_id
;
update_action_summary (
p_arc_object_for => p_arc_object,
p_object_for_id => p_object_id,
p_seeded_data_source => l_seeded_data_source,
x_return_status => l_return_status
);
p_msg_data => L_API_NAME || ': After update_action_summary Status= ' || l_return_status
);
END Preview_Selections;
PROCEDURE Aggregate_Selections (
p_arc_object IN VARCHAR2,
p_object_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Aggregate Selections';
Preview_Selections (
p_arc_object => p_arc_object,
p_object_id => p_object_id,
x_return_status => x_return_status
);
update_source_target (
p_object_type => p_arc_object,
p_object_id => p_object_id,
x_return_status => x_return_status
);
END Aggregate_Selections;
SELECT arc_incl_object_from,
incl_object_id
FROM ams_list_select_actions
WHERE arc_action_used_by = p_arc_object_for
AND action_used_by_id = p_object_for_id
;
SELECT workbook_name,
worksheet_name,
workbook_owner_name
FROM ams_discoverer_sql
WHERE discoverer_sql_id = p_discoverer_sql_id
;
SELECT target.data_source_id , target.target_id
FROM ams_dm_models_all_b model, ams_dm_targets_b target
WHERE model.model_id = p_model_id
AND target.target_id = model.target_id
;
SELECT target.data_source_id , target.target_id
FROM ams_dm_scores_all_b score, ams_dm_models_all_b model, ams_dm_targets_b target
WHERE score.score_id = p_score_id
AND model.model_id = score.model_id
AND target.target_id = model.target_id
;
SELECT model_type
FROM ams_dm_models_vl
WHERE model_id=p_model_id
;
SELECT model_id, model_type
FROM ams_dm_models_vl
WHERE model_id=(select model_id from ams_dm_scores_vl where score_id=p_scor_id)
;
l_insert_clause VARCHAR2(16000);
l_insert_fields VARCHAR2(16000);
l_insert_string VARCHAR2(32000);
l_insert_clause := 'INSERT INTO ams_dm_target_stg_gt (arc_object_used_by, ';
l_insert_clause := l_insert_clause || 'object_used_by_id, arc_object, ';
l_insert_clause := l_insert_clause || 'object_id, party_id) SELECT ';
l_insert_clause := 'INSERT INTO ams_dm_org_contacts (arc_object_used_by, ';
l_insert_clause := l_insert_clause || 'object_used_by_id, arc_object, ';
l_insert_clause := l_insert_clause || 'object_id, party_id, org_party_id) SELECT ';
validate_product_selections (
p_model_id => l_model_id,
x_return_status => x_return_status
);
validate_selection_status (
p_model_id => l_model_id,
p_model_type => l_model_type,
p_workbook_owner => l_workbook_rec.workbook_owner_name,
p_workbook_name => l_workbook_rec.workbook_name,
p_worksheet_name => l_workbook_rec.worksheet_name,
p_select_object_type => l_object_rec.arc_incl_object_from,
p_select_object_id => l_object_rec.incl_object_id,
x_return_status => x_return_status
);
get_insert_fields (
p_select_object_type => l_object_rec.arc_incl_object_from,
p_select_object_id => l_object_rec.incl_object_id,
p_workbook_owner => l_workbook_rec.workbook_owner_name,
p_workbook_name => l_workbook_rec.workbook_name,
p_worksheet_name => l_workbook_rec.worksheet_name,
x_insert_fields => l_insert_fields,
x_return_status => x_return_status,
x_pk_field => l_pk_field
);
p_select_object_type => l_object_rec.arc_incl_object_from,
p_select_object_id => l_object_rec.incl_object_id,
p_workbook_owner => l_workbook_rec.workbook_owner_name,
p_workbook_name => l_workbook_rec.workbook_name,
p_worksheet_name => l_workbook_rec.worksheet_name,
p_is_b2b_custprof => l_is_b2b_custprof,
x_from_clause => l_from_clause,
x_return_status => x_return_status
);
p_select_object_type => l_object_rec.arc_incl_object_from,
p_select_object_id => l_object_rec.incl_object_id,
p_workbook_owner => l_workbook_rec.workbook_owner_name,
p_workbook_name => l_workbook_rec.workbook_name,
p_worksheet_name => l_workbook_rec.worksheet_name,
p_is_b2b_custprof => l_is_b2b_custprof,
x_where_clause => l_where_clause,
x_return_status => x_return_status
);
l_insert_fields := l_insert_fields || ', hpr.object_id ';
l_sql_statement := l_insert_clause || l_insert_fields ||
' FROM ' || l_from_clause;
EXECUTE IMMEDIATE 'INSERT INTO ams_dm_target_stg_gt(arc_object_used_by,object_used_by_id,arc_object,object_id,party_id)
SELECT arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id
FROM ams_dm_org_contacts GROUP BY arc_object_used_by,object_used_by_id,arc_object,object_id,org_party_id';
get_insert_fields (
p_select_object_type => G_ALTERNATIVE_DATA_SOURCE,
p_select_object_id => l_data_source_id,
p_workbook_owner => l_workbook_rec.workbook_owner_name,
p_workbook_name => l_workbook_rec.workbook_name,
p_worksheet_name => l_workbook_rec.worksheet_name,
x_insert_fields => l_insert_fields,
x_return_status => x_return_status,
x_pk_field => l_pk_field
);
p_select_object_type => G_ALTERNATIVE_DATA_SOURCE,
p_select_object_id => l_target_id,
p_workbook_owner => l_workbook_rec.workbook_owner_name,
p_workbook_name => l_workbook_rec.workbook_name,
p_worksheet_name => l_workbook_rec.worksheet_name,
p_is_b2b_custprof => l_is_b2b_custprof,
x_from_clause => l_from_clause,
x_return_status => x_return_status
);
p_select_object_type => G_ALTERNATIVE_DATA_SOURCE,
p_select_object_id => l_data_source_id,
p_workbook_owner => l_workbook_rec.workbook_owner_name,
p_workbook_name => l_workbook_rec.workbook_name,
p_worksheet_name => l_workbook_rec.worksheet_name,
p_is_b2b_custprof => l_is_b2b_custprof,
x_where_clause => l_where_clause,
x_return_status => x_return_status
);
l_sql_statement := l_insert_clause || l_insert_fields ||
' FROM ' || l_from_clause;
SELECT workbook_name,
worksheet_name,
workbook_owner_name
FROM ams_discoverer_sql
WHERE discoverer_sql_id = p_discoverer_sql_id
;
'INSERT INTO ams_dm_target_stg_gt ' ||
'(arc_object_used_by, object_used_by_id, arc_object, object_id, party_id) ' ||
'SELECT :arc_object' || ', :object_id' || ', :disco_wb, :disco_id' || ', ' || l_source_pk_field ||
' ' || l_from_and_where
USING p_arc_object_for, p_object_for_id, 'DIWB', p_discoverer_sql_id;
SELECT model_type
FROM ams_dm_models_vl
WHERE model_id=p_model_id
;
SELECT model_id
FROM ams_dm_scores_all_b
WHERE score_id = p_score_id
;
DELETE FROM ams_dm_org_contacts WHERE ARC_OBJECT_USED_BY = p_arc_object_for AND OBJECT_USED_BY_ID = p_object_for_id;
SELECT a.source_type_code,
a.source_object_name || '.' || a.source_object_pk_field
FROM ams_list_src_types a , ams_discoverer_sql b
WHERE a.master_source_type_flag = 'Y'
AND a.enabled_flag = 'Y'
AND b.workbook_owner_name = p_workbook_owner_name
AND b.workbook_name = p_workbook_name
AND b.worksheet_name = p_worksheet_name
AND a.source_type_code = b.source_type_code
;
SELECT list_action_type, arc_incl_object_from, incl_object_id
FROM ams_list_select_actions
WHERE arc_action_used_by = p_arc_object_for
AND action_used_by_id = p_object_for_id
ORDER BY order_number
;
SELECT target_positive_value
FROM ams_dm_models_all_b
WHERE model_id = p_model_id
;
SELECT COUNT(*),
NVL (SUM (DECODE (target_value, l_target_value, 1, 0)), 0)
-- FROM ams_dm_inter_source_stg
FROM ams_dm_int_src_stg_gt
WHERE arc_object_used_by = p_arc_object_for
AND object_used_by_id = p_object_for_id
AND enabled_flag = 'Y'
;
SELECT status_code
FROM ams_dm_models_all_b
WHERE model_id = p_model_id;
SELECT status_code
FROM ams_dm_scores_all_b
WHERE score_id = p_score_id;
SELECT model_id
FROM ams_dm_scores_all_b
WHERE score_id = p_score_id
;
SELECT model_type
FROM ams_dm_models_vl
WHERE model_id=p_model_id
;
DELETE /*+ index(AMS_DM_SOURCE AMS_DM_SOURCE_U2) */ FROM ams_dm_source
WHERE arc_used_for_object = p_arc_object_for
AND used_for_object_id = p_object_for_id;
UPDATE ams_dm_models_all_b
SET total_records = 0,
total_positives = 0
WHERE model_id = p_object_for_id
;
UPDATE ams_dm_scores_all_b
SET total_records = 0,
total_positives = 0
WHERE score_id = p_object_for_id
;
INSERT INTO ams_dm_int_src_stg_gt (
arc_object_used_by,
object_used_by_id,
party_id,
enabled_flag,
random_generated_num,
target_value
)
SELECT p_arc_object_for
, p_object_for_id
, t.party_id
, 'N'
, NULL
, DECODE (t.target_flag, 'Y', l_target_value, '0')
-- FROM ams_dm_target_stg t, HZ_PARTIES p
FROM ams_dm_target_stg_gt t, HZ_PARTIES p
WHERE t.arc_object_used_by = p_arc_object_for
AND t.object_used_by_id = p_object_for_id
AND t.arc_object = l_sources_rec.arc_incl_object_from
AND t.object_id = l_sources_rec.incl_object_id
AND t.party_id = p.party_id
AND p.party_type = l_party_type;
INSERT INTO ams_dm_int_src_stg_gt (
arc_object_used_by,
object_used_by_id,
party_id,
enabled_flag,
random_generated_num,
target_value
)
SELECT p_arc_object_for
, p_object_for_id
, t.party_id
, 'N'
, NULL
, DECODE (t.target_flag, 'Y', l_target_value, '0')
-- FROM ams_dm_target_stg t , HZ_PARTIES p
FROM ams_dm_target_stg_gt t , HZ_PARTIES p
WHERE t.arc_object_used_by = p_arc_object_for
AND t.object_used_by_id = p_object_for_id
AND t.arc_object = l_sources_rec.arc_incl_object_from
AND t.object_id = l_sources_rec.incl_object_id
AND t.party_id = p.party_id
AND p.party_type = l_party_type
AND NOT EXISTS (SELECT 1
-- FROM ams_dm_inter_source_stg i
FROM ams_dm_int_src_stg_gt i
WHERE i.arc_object_used_by = t.arc_object_used_by
AND i.object_used_by_id = t.object_used_by_id
AND i.party_id = t.party_id)
;
DELETE FROM ams_dm_int_src_stg_gt i
WHERE arc_object_used_by = p_arc_object_for
AND object_used_by_id = p_object_for_id
AND EXISTS (SELECT 1
FROM ams_dm_target_stg_gt t -- ams_dm_target_stg t
WHERE t.arc_object_used_by = p_arc_object_for
AND t.object_used_by_id = p_object_for_id
AND t.arc_object = l_sources_rec.arc_incl_object_from
AND t.object_id = l_sources_rec.incl_object_id
AND t.party_id = i.party_id)
;
DELETE FROM ams_dm_int_src_stg_gt i
WHERE arc_object_used_by = p_arc_object_for
AND object_used_by_id = p_object_for_id
AND NOT EXISTS (SELECT 1
-- FROM ams_dm_target_stg t
FROM ams_dm_target_stg_gt t
WHERE t.arc_object_used_by = p_arc_object_for
AND t.object_used_by_id = p_object_for_id
AND t.arc_object = l_sources_rec.arc_incl_object_from
AND t.object_id = l_sources_rec.incl_object_id
AND t.party_id = i.party_id)
;
INSERT INTO ams_dm_int_src_stg_gt (
arc_object_used_by,
object_used_by_id,
party_id,
enabled_flag,
random_generated_num,
target_value
)
SELECT p_arc_object_for
, p_object_for_id
, party_id
, 'N'
, NULL
, DECODE (target_flag, 'Y', l_target_value, '0')
-- FROM ams_dm_target_stg
FROM ams_dm_target_stg_gt
WHERE arc_object_used_by = p_arc_object_for
AND object_used_by_id = p_object_for_id
;
INSERT INTO ams_dm_source (
source_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
arc_used_for_object,
used_for_object_id,
party_id,
target_value
)
SELECT ams_dm_source_s.NEXTVAL,
SYSDATE,
l_concUserId,
SYSDATE,
l_userId,
l_concUserId,
1,
p_arc_object_for,
p_object_for_id,
party_id,
target_value
-- FROM ams_dm_inter_source_stg
FROM ams_dm_int_src_stg_gt
WHERE arc_object_used_by = p_arc_object_for
AND object_used_by_id = p_object_for_id
AND enabled_flag = 'Y'
;
UPDATE ams_dm_models_all_b
SET total_records = l_total_records
, total_positives = l_total_positives
WHERE model_id = p_object_for_id
;
AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_MODEL_SELECTIONS_EMPTY');
UPDATE ams_dm_scores_all_b
SET total_records = l_row_count
WHERE score_id = p_object_for_id
;
AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_SCORE_SELECTIONS_EMPTY');
DELETE FROM ams_dm_org_contacts
-- WHERE org_party_id NOT IN (SELECT distinct party_id from ams_dm_inter_source_stg WHERE arc_object_used_by = p_arc_object_for AND
WHERE org_party_id NOT IN (SELECT distinct party_id from ams_dm_int_src_stg_gt WHERE arc_object_used_by = p_arc_object_for AND
object_used_by_id = p_object_for_id);
SELECT sql_string
FROM ams_discoverer_sql
WHERE workbook_owner_name = p_workbook_owner_name
AND workbook_name = p_workbook_name
AND worksheet_name = p_worksheet_name
AND sequence_order >= p_str_num
ORDER BY sequence_order
;
SELECT m.model_id
, m.model_type
, m.target_positive_value
, t.target_id
, t.data_source_id
FROM ams_dm_models_all_b m, ams_dm_targets_b t
WHERE m.model_id = p_model_id
AND t.target_id = m.target_id
;
SELECT field.source_column_name
, source1.source_object_name
, source2.source_object_name
, source2.source_object_pk_field
, source1.source_object_name||decode(UPPER(source1.remote_flag),'Y','@'||source1.database_link,'')
, source2.source_object_name||decode(UPPER(source2.remote_flag),'Y','@'||source2.database_link,'')
, target.data_source_id
, target.target_source_id
, field.enabled_flag
FROM ams_dm_targets_b target, ams_list_src_fields field, ams_list_src_types source1, ams_list_src_types source2
WHERE target.target_id = p_target_id
AND field.list_source_field_id = source_field_id
AND source2.list_source_type_id = target.data_source_id
AND source1.list_source_type_id = target.target_source_id
;
UPDATE ams_dm_target_stg_gt t
SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
FROM dual
-- choang - 21-nov-2003 - bug 3275817
-- changed to not exists and having clause
WHERE NOT EXISTS (SELECT 1
-- changed rosharma 19-jun-2003 bug # 3004453
--FROM ams_dm_party_details_time p
--WHERE p.party_id = t.party_id
--AND p.tot_num_order_3_months < 1
FROM bic_party_summ p , hz_relationships hpr
WHERE hpr.party_id = t.party_id
AND hpr.status = 'A'
AND hpr.subject_table_name = 'HZ_PARTIES'
AND hpr.object_table_name = 'HZ_PARTIES'
AND hpr.directional_flag = 'F'
AND hpr.relationship_code IN ('CONTACT_OF' , 'EMPLOYEE_OF')
AND (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
AND p.party_id = hpr.object_id --the org's party id
HAVING SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, p.period_start_date)) - 3),1,0,p.order_num)) >= 1
-- end change rosharma 19-jun-2003 bug # 3004453
)
)
WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
AND object_used_by_id = p_model_id;
UPDATE ams_dm_target_stg_gt t
SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
FROM dual
-- choang - 21-nov-2003 - bug 3275817
-- changed to not exists and having clause
WHERE NOT EXISTS (SELECT 1
-- changed rosharma 19-jun-2003 bug # 3004453
--FROM ams_dm_party_details_time p
--WHERE p.party_id = t.party_id
--AND p.tot_num_order_3_months < 1
FROM bic_party_summ p
WHERE p.party_id = t.party_id
HAVING SUM(DECODE(SIGN(ROUND(MONTHS_BETWEEN(l_date, p.period_start_date)) - 3),1,0,p.order_num)) >= 1
-- end change rosharma 19-jun-2003 bug # 3004453
)
)
WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
AND object_used_by_id = p_model_id;
UPDATE ams_dm_target_stg_gt t
SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
FROM dual
WHERE EXISTS (SELECT 1
FROM ams_campaign_schedules_b c, jtf_ih_interactions i,
jtf_ih_results_b r, ams_list_select_actions l
WHERE c.schedule_id = l.incl_object_id
AND l.arc_action_used_by = t.arc_object_used_by
AND l.action_used_by_id = t.object_used_by_id
AND i.party_id = t.party_id
AND i.source_code = c.source_code
AND r.result_id = i.result_id
AND r.positive_response_flag = 'Y'
))
WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
AND object_used_by_id = p_model_id;
l_sql := 'UPDATE ams_dm_target_stg_gt t';
l_sql := l_sql || ' SET t.target_flag = (SELECT ''' || L_POSITIVE_TARGET_VALUE || '''';
l_sql := l_sql || ' FROM dual WHERE EXISTS (SELECT 1';
UPDATE ams_dm_target_stg_gt t
SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
FROM dual
WHERE EXISTS (SELECT 1
FROM AMS_ACT_PRODUCTS aa, OE_ORDER_HEADERS_ALL oh, OE_ORDER_LINES_ALL ol,
hz_cust_account_roles hcr, hz_relationships hpr
WHERE aa.ARC_ACT_PRODUCT_USED_BY = 'MODL'
and aa.ACT_PRODUCT_USED_BY_ID = p_model_id
and ol.INVENTORY_ITEM_ID IN
(SELECT DISTINCT ic.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES ic
WHERE ic.CATEGORY_ID = aa.CATEGORY_ID
AND (ic.INVENTORY_ITEM_ID = aa.INVENTORY_ITEM_ID OR aa.INVENTORY_ITEM_ID IS NULL))
--and (oh.ORG_ID = aa.ORGANIZATION_ID OR aa.ORGANIZATION_ID IS NULL)
--and ol.ORG_ID = nvl(aa.ORGANIZATION_ID,ol.ORG_ID)
and oh.SHIP_TO_CONTACT_ID = hcr.CUST_ACCOUNT_ROLE_ID
and hcr.PARTY_ID=hpr.PARTY_ID
and hpr.party_id = t.party_id
and hpr.status = 'A'
and hpr.subject_table_name = 'HZ_PARTIES'
and hpr.object_table_name = 'HZ_PARTIES'
and hpr.directional_flag = 'F'
and hpr.relationship_code IN ('CONTACT_OF','EMPLOYEE_OF')
and (SYSDATE BETWEEN hpr.start_date and NVL(hpr.end_date,SYSDATE))
and oh.header_id = ol.header_id
and nvl(oh.cancelled_flag,'N') <> 'Y'
and nvl(oh.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
and nvl(ol.cancelled_flag,'N') <> 'Y'
and nvl(ol.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
and oh.order_category_code <> 'RETURN'
and ol.line_category_code <> 'RETURN'
)
)
WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
AND object_used_by_id = p_model_id;
UPDATE ams_dm_target_stg_gt t
SET target_flag = (SELECT L_POSITIVE_TARGET_VALUE
FROM dual
WHERE EXISTS (SELECT 1
FROM AMS_ACT_PRODUCTS aa,
OE_ORDER_HEADERS_ALL oh,
OE_ORDER_LINES_ALL ol,
--hz_cust_site_uses_all hcsu,
--hz_cust_acct_sites_all hcs,
HZ_CUST_ACCOUNTS hc
WHERE aa.ARC_ACT_PRODUCT_USED_BY = 'MODL'
and aa.ACT_PRODUCT_USED_BY_ID = p_model_id
and ol.INVENTORY_ITEM_ID IN
(SELECT DISTINCT ic.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES ic
WHERE ic.CATEGORY_ID = aa.CATEGORY_ID
AND (ic.INVENTORY_ITEM_ID = aa.INVENTORY_ITEM_ID OR aa.INVENTORY_ITEM_ID IS NULL))
--and (oh.ORG_ID = aa.ORGANIZATION_ID OR aa.ORGANIZATION_ID IS NULL)
--and ol.ORG_ID = nvl(aa.ORGANIZATION_ID,ol.ORG_ID)
--and oh.SHIP_TO_ORG_ID = hcsu.site_use_id
--and hcsu.cust_acct_site_id = hcs.cust_acct_site_id
--and hcs.cust_account_id = hc.cust_account_id
and oh.SOLD_TO_ORG_ID = hc.cust_account_id
and hc.PARTY_ID = t.party_id
and oh.header_id = ol.header_id
and nvl(oh.cancelled_flag,'N') <> 'Y'
and nvl(oh.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
and nvl(ol.cancelled_flag,'N') <> 'Y'
and nvl(ol.FLOW_STATUS_CODE,'N') <> 'CANCELLED'
and oh.order_category_code <> 'RETURN'
and ol.line_category_code <> 'RETURN'
)
)
WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
AND object_used_by_id = p_model_id;
l_sql := 'UPDATE ams_dm_target_stg_gt t';
l_sql := l_sql || ' SET t.target_flag = (SELECT ''' || L_POSITIVE_TARGET_VALUE || '''';
UPDATE ams_dm_target_stg_gt
SET target_flag = L_NEGATIVE_TARGET_VALUE
WHERE arc_object_used_by = G_OBJECT_TYPE_MODEL
AND object_used_by_id = p_model_id
AND target_flag IS NULL;
PROCEDURE update_action_summary (
p_arc_object_for IN VARCHAR2,
p_object_for_id IN NUMBER,
p_seeded_data_source IN BOOLEAN,
x_return_status OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Update Select Actions Summary';
SELECT list_select_action_id, object_version_number
FROM ams_list_select_actions
WHERE arc_action_used_by = p_arc_object_for
AND action_used_by_id = p_object_for_id
AND arc_incl_object_from = p_arc_incl_object
AND incl_object_id = p_incl_object_id
;
SELECT arc_object,
object_id,
COUNT(*) total_selected, -- total selected
SUM (DECODE (target_flag, 'Y', 1, 0)) total_targeted
-- FROM ams_dm_target_stg
FROM ams_dm_target_stg_gt
WHERE arc_object_used_by = p_arc_object_for
AND object_used_by_id = p_object_for_id
GROUP BY arc_object, object_id
;
FETCH c_action_version INTO l_action_rec.list_select_action_id, l_action_rec.object_version_number;
l_action_rec.no_of_rows_used := l_summary_rec.total_selected;
AMS_ListAction_PVT.Update_ListAction (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_rec => l_action_rec
);
END update_action_summary;
PROCEDURE update_source_target (
p_object_type IN VARCHAR2,
p_object_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Update Source Target';
SELECT m.model_id
, m.model_type
, m.target_positive_value
, t.target_id
, t.data_source_id
FROM ams_dm_models_all_b m, ams_dm_targets_b t
WHERE m.model_id = p_model_id
AND t.target_id = m.target_id
;
SELECT m.model_id
, m.model_type
, m.target_positive_value
, t.target_id
, t.data_source_id
FROM ams_dm_models_all_b m, ams_dm_scores_all_b s, ams_dm_targets_b t
WHERE m.model_id = s.model_id
AND s.score_id = p_score_id
AND t.target_id = m.target_id
;
UPDATE /*+ index(t AMS_DM_SOURCE_U2) */ ams_dm_source t
SET target_value = (SELECT L_POSITIVE_TARGET_VALUE
FROM dual
WHERE EXISTS (SELECT 1
FROM hz_cust_accounts c, hz_suspension_activity s
WHERE c.party_id = t.party_id
AND s.cust_account_id = c.cust_account_id
AND s.action_type = L_NON_LOYAL_CODE
))
WHERE arc_used_for_object = p_object_type
AND used_for_object_id = p_object_id;
UPDATE /*+ index(t AMS_DM_SOURCE_U2) */ ams_dm_source t
SET target_value = (SELECT L_POSITIVE_TARGET_VALUE
FROM dual
WHERE EXISTS (SELECT 1
FROM ams_campaign_schedules_b c,
jtf_ih_interactions i,
ams_list_select_actions l
WHERE c.schedule_id = l.incl_object_id
AND l.arc_action_used_by = t.arc_used_for_object
AND l.action_used_by_id = t.used_for_object_id
AND i.party_id = t.party_id
AND i.source_code = c.source_code
/*** enable this code when positive responses are captured
WHERE EXISTS (SELECT 1
FROM ams_campaign_schedules_b c, jtf_ih_interactions i,
jtf_ih_results_b r, ams_list_select_actions l
WHERE c.schedule_id = l.incl_object_id
AND l.arc_action_used_by = t.arc_used_for_object
AND l.action_used_by_id = t.used_for_object_id
AND i.party_id = t.party_id
AND i.source_code = c.source_code
AND r.result_id = i.result_id
AND r.positive_response_flag = 'Y'
***/
))
WHERE arc_used_for_object = p_object_type
AND used_for_object_id = p_object_id;
UPDATE /*+ index(AMS_DM_SOURCE AMS_DM_SOURCE_U2) */ ams_dm_source
SET target_value = L_NEGATIVE_TARGET_VALUE
WHERE arc_used_for_object = p_object_type
AND used_for_object_id = p_object_id
AND target_value IS NULL;
END update_source_target;
Preview_Selections (
p_arc_object => p_arc_object,
p_object_id => p_object_id,
x_return_status => l_return_status
);
Aggregate_Selections (
p_arc_object => p_arc_object,
p_object_id => p_object_id,
x_return_status => l_return_status
);
L_SELECTION_TYPE_STANDARD CONSTANT VARCHAR2(30) := 'STANDARD';
L_SELECTION_TYPE_NTH_ROW CONSTANT VARCHAR2(30) := 'NTH_RECORD';
L_SELECTION_TYPE_PCT CONSTANT VARCHAR2(30) := 'RANDOM';
l_row_selection_type VARCHAR2(30);
SELECT min_records,
max_records,
row_selection_type,
every_nth_row,
pct_random
FROM ams_dm_models_all_b
WHERE model_id = p_model_id
;
SELECT min_records,
max_records,
row_selection_type,
every_nth_row,
pct_random
FROM ams_dm_scores_all_b
WHERE score_id = p_score_id
;
SELECT COUNT(*)
-- FROM ams_dm_inter_source_stg
FROM ams_dm_int_src_stg_gt
WHERE arc_object_used_by = p_arc_object
AND object_used_by_id = p_object_id
;
FETCH c_model_details INTO l_min_records, l_max_records, l_row_selection_type, l_every_nth_row, l_pct_random;
FETCH c_score_details INTO l_min_records, l_max_records, l_row_selection_type, l_every_nth_row, l_pct_random;
IF l_row_selection_type = L_SELECTION_TYPE_STANDARD THEN
-- UPDATE ams_dm_inter_source_stg
UPDATE ams_dm_int_src_stg_gt
SET enabled_flag = 'Y'
WHERE arc_object_used_by = p_arc_object_for
AND object_used_by_id = p_object_for_id
AND rownum <= NVL (l_max_records, l_total_records)
;
ELSIF l_row_selection_type = L_SELECTION_TYPE_NTH_ROW THEN
randomize_nth_rows (
p_arc_object_for => p_arc_object_for,
p_object_for_id => p_object_for_id,
p_min_rows => l_min_records,
p_max_rows => l_max_records,
p_total_rows => l_total_records,
p_every_nth_row => l_every_nth_row,
x_return_status => x_return_status
);
ELSIF l_row_selection_type = L_SELECTION_TYPE_PCT THEN
randomize_by_pct (
p_arc_object_for => p_arc_object_for,
p_object_for_id => p_object_for_id,
p_min_rows => l_min_records,
p_max_rows => l_max_records,
p_total_rows => l_total_records,
p_pct_random => l_pct_random,
x_return_status => x_return_status
);
AMS_Utility_PVT.error_message ('AMS_DM_BAD_SELECTION_TYPE', 'SELECTION_TYPE', l_row_selection_type);
SELECT party_id
-- FROM ams_dm_inter_source_stg
FROM ams_dm_int_src_stg_gt
WHERE arc_object_used_by = p_arc_object
AND object_used_by_id = p_object_id
ORDER BY random_generated_num
;
UPDATE ams_dm_int_src_stg_gt
SET random_generated_num = DBMS_RANDOM.random
WHERE arc_object_used_by = p_arc_object_for
AND object_used_by_id = p_object_for_id
;
UPDATE ams_dm_int_src_stg_gt
SET enabled_flag = 'Y'
WHERE arc_object_used_by = p_arc_object_for
AND object_used_by_id = p_object_for_id
AND party_id = l_object_ids(i);
UPDATE ams_dm_int_src_stg_gt
SET enabled_flag = DECODE (MOD (rownum, p_every_nth_row), 0, 'Y', 'N')
WHERE arc_object_used_by = p_arc_object_for
AND object_used_by_id = p_object_for_id
;
UPDATE ams_dm_int_src_stg_gt
SET enabled_flag = DECODE (SIGN (l_local_max_rows - rownum), -1, 'N', 'Y')
WHERE arc_object_used_by = p_arc_object_for
AND object_used_by_id = p_object_for_id
AND enabled_flag = 'Y'
;
PROCEDURE get_insert_fields (
p_select_object_type IN VARCHAR2,
p_select_object_id IN NUMBER,
p_workbook_owner IN VARCHAR2,
p_workbook_name IN VARCHAR2,
p_worksheet_name IN VARCHAR2,
x_insert_fields OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_pk_field OUT NOCOPY VARCHAR2
)
IS
CURSOR c_pk_field (p_list_source_type_id IN NUMBER) IS
SELECT source_object_name || '.' || source_object_pk_field
FROM ams_list_src_types
WHERE list_source_type_id = p_list_source_type_id
;
x_insert_fields := ':p_object_type, :p_object_id , :p_select_object_type, ';
x_insert_fields := x_insert_fields || ':p_select_object_id , ';
IF p_select_object_type = G_ALTERNATIVE_DATA_SOURCE THEN
OPEN c_pk_field (p_select_object_id);
x_insert_fields := x_insert_fields || l_source_pk_field;
ELSIF p_select_object_type = 'LIST' THEN
x_insert_fields := x_insert_fields || 'e.party_id';
ELSIF p_select_object_type = 'CSCH' THEN
x_insert_fields := x_insert_fields || 'e.party_id';
ELSIF p_select_object_type = 'CELL' THEN
x_insert_fields := x_insert_fields || 'aps.party_id';
ELSIF p_select_object_type = 'DIWB' THEN
get_customer_field (
p_workbook_owner_name => p_workbook_owner,
p_workbook_name => p_workbook_name,
p_worksheet_name => p_worksheet_name,
x_customer_field => l_source_pk_field,
x_return_status => x_return_status
);
x_insert_fields := x_insert_fields || l_source_pk_field;
END get_insert_fields;
p_select_object_type IN VARCHAR2,
p_select_object_id IN NUMBER,
p_workbook_owner IN VARCHAR2,
p_workbook_name IN VARCHAR2,
p_worksheet_name IN VARCHAR2,
p_is_b2b_custprof IN BOOLEAN,
x_from_clause OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'get_from_clause';
SELECT a.source_object_name||decode(UPPER(a.remote_flag),'Y','@'||a.database_link,'')
FROM ams_list_src_types a, ams_dm_targets_b b
WHERE a.list_source_type_id = b.data_source_id
AND b.target_id = p_target_id
UNION
SELECT a.source_object_name||decode(UPPER(a.remote_flag),'Y','@'||a.database_link,'')
FROM ams_list_src_types a, ams_dm_target_sources b
WHERE a.list_source_type_id = b.data_source_id
AND a.enabled_flag = 'Y'
AND b.target_id = p_target_id
AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs c,ams_dm_targets_b d
WHERE d.target_id = p_target_id
AND c.MASTER_SOURCE_TYPE_ID = d.data_source_id
AND c.SUB_SOURCE_TYPE_ID = b.data_source_id
AND c.enabled_flag = 'Y')
;
IF p_select_object_type = G_ALTERNATIVE_DATA_SOURCE THEN
x_from_clause := '';
OPEN c_source_object (p_select_object_id);
ELSIF p_select_object_type = 'LIST' THEN
IF p_is_b2b_custprof THEN
x_from_clause := 'ams_list_entries e';
ELSIF p_select_object_type = 'CSCH' THEN
x_from_clause := 'ams_act_lists l, ams_list_entries e';
ELSIF p_select_object_type = 'CELL' THEN
x_from_clause := 'ams_party_market_segments aps';
ELSIF p_select_object_type = 'DIWB' THEN
get_from_sql (
p_workbook_owner_name => p_workbook_owner,
p_workbook_name => p_workbook_name,
p_worksheet_name => p_worksheet_name,
x_from_sql => l_from_and_where,
x_found => l_found,
x_return_status => x_return_status
);
p_select_object_type IN VARCHAR2,
p_select_object_id IN NUMBER,
p_workbook_owner IN VARCHAR2,
p_workbook_name IN VARCHAR2,
p_worksheet_name IN VARCHAR2,
p_is_b2b_custprof IN BOOLEAN,
x_where_clause OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'get_where_clause';
SELECT ds.source_object_name || '.' || field.source_column_name
FROM ams_dm_models_all_b model, ams_dm_targets_b target, ams_list_src_fields field , ams_list_src_types ds
WHERE model.model_id = p_model_id
AND target.target_id = model.target_id
AND field.list_source_field_id = target.source_field_id
AND ds.list_source_type_id = target.target_source_id
;
SELECT source.data_source_id
FROM ams_dm_models_all_b model, ams_dm_target_sources source, ams_list_src_types lst
WHERE model.model_id = p_model_id
AND source.target_id = model.target_id
AND lst.list_source_type_id = source.data_source_id
AND lst.enabled_flag = 'Y'
AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs c,ams_dm_targets_b d
WHERE d.target_id = model.target_id
AND c.MASTER_SOURCE_TYPE_ID = d.data_source_id
AND c.SUB_SOURCE_TYPE_ID = source.data_source_id
AND c.enabled_flag = 'Y')
;
SELECT source.data_source_id
FROM ams_dm_scores_all_b score, ams_dm_models_all_b model, ams_dm_target_sources source, ams_list_src_types lst
WHERE score.score_id = p_score_id
AND model.model_id = score.model_id
AND source.target_id = model.target_id
AND lst.list_source_type_id = source.data_source_id
AND lst.enabled_flag = 'Y'
AND EXISTS (SELECT 1 FROM ams_list_src_type_assocs c,ams_dm_targets_b d
WHERE d.target_id = model.target_id
AND c.MASTER_SOURCE_TYPE_ID = d.data_source_id
AND c.SUB_SOURCE_TYPE_ID = source.data_source_id
AND c.enabled_flag = 'Y')
;
IF p_select_object_type = G_ALTERNATIVE_DATA_SOURCE THEN
IF p_object_type = G_OBJECT_TYPE_MODEL THEN
OPEN c_target_field (p_object_id);
p_data_source_id => p_select_object_id,
x_filter => l_perz_filter,
x_return_status => x_return_status
);
get_related_ds_condition ( p_master_ds_id => p_select_object_id,
p_child_ds_id => l_child_ds_id,
x_sql_stmt => l_relation_cond);
get_related_ds_condition ( p_master_ds_id => p_select_object_id,
p_child_ds_id => l_child_ds_id,
x_sql_stmt => l_relation_cond);
ELSIF p_select_object_type = 'LIST' THEN
IF p_is_b2b_custprof THEN
x_where_clause := 'e.list_header_id = :list_header_id ' ||
'AND e.enabled_flag = ''Y''';
ELSIF p_select_object_type = 'CSCH' THEN
x_where_clause := 'l.list_used_by = ''CSCH'' ' ||
'AND l.list_used_by_id = :incl_object_id ' ||
'AND l.list_act_type = ''TARGET'' ' ||
'AND e.list_header_id = l.list_header_id ' ||
'AND e.enabled_flag = ''Y''';
ELSIF p_select_object_type = 'CELL' THEN
x_where_clause := 'aps.market_segment_id = :object_id';
ELSIF p_select_object_type = 'DIWB' THEN
get_wb_filter (
p_workbook_owner => p_workbook_owner,
p_workbook_name => p_workbook_name,
p_worksheet_name => p_worksheet_name,
x_filter => l_from_and_where,
x_return_status => x_return_status
);
SELECT target_value,
target_operator,
range_value
FROM ams_dm_target_values_b
WHERE target_id = p_target_id;
SELECT d.SOURCE_CATEGORY
FROM ams_dm_models_all_b m,ams_dm_targets_b t,ams_list_src_types d
WHERE m.model_id = p_model_id
AND m.target_id = t.target_id
AND t.DATA_SOURCE_ID=d.LIST_SOURCE_TYPE_ID;
SELECT a.SOURCE_OBJECT_NAME as CHILD_SOURCE,
b.SUB_SOURCE_TYPE_PK_COLUMN as CHILD_COLUMN,
c.SOURCE_OBJECT_NAME as PARENT_SOURCE,
NVL(b.MASTER_SOURCE_TYPE_PK_COLUMN , c.SOURCE_OBJECT_PK_FIELD) as PARENT_COLUMN
FROM ams_list_src_types a, ams_list_src_type_assocs b, ams_list_src_types c
where b.MASTER_SOURCE_TYPE_ID = p_master_ds_id
and b.SUB_SOURCE_TYPE_ID = p_child_ds_id
and b.ENABLED_FLAG = 'Y'
and a.LIST_SOURCE_TYPE_ID=b.SUB_SOURCE_TYPE_ID
and c.LIST_SOURCE_TYPE_ID = b.MASTER_SOURCE_TYPE_ID
and a.enabled_flag = 'Y'
and c.enabled_flag = 'Y'
;
SELECT 1
FROM ams_dm_targets_b t,ams_list_src_types d
WHERE t.target_id = p_target_id
AND t.DATA_SOURCE_ID=d.LIST_SOURCE_TYPE_ID
AND d.SOURCE_TYPE_CODE = 'AMS_DM_PARTY_ATTRIBUTES_V'
UNION
SELECT 1
FROM ams_dm_target_sources t,ams_list_src_types d
WHERE t.target_id = p_target_id
AND t.DATA_SOURCE_ID=d.LIST_SOURCE_TYPE_ID
AND d.SOURCE_TYPE_CODE = 'AMS_DM_PARTY_ATTRIBUTES_V'
;
SELECT d.source_type_code
FROM ams_dm_models_all_b m,ams_dm_targets_b t,ams_list_src_types d
WHERE m.model_id = p_model_id
AND m.target_id = t.target_id
AND t.data_source_id=d.list_source_type_id;
PROCEDURE validate_selection_status (
p_model_id IN NUMBER,
p_model_type IN VARCHAR2,
p_workbook_owner IN VARCHAR2,
p_workbook_name IN VARCHAR2,
p_worksheet_name IN VARCHAR2,
p_select_object_type IN VARCHAR2,
p_select_object_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
L_API_NAME VARCHAR2(30) := 'Validate Selection Status';
SELECT list_name, status_code
FROM ams_list_headers_vl
WHERE list_header_id = p_list_id
;
SELECT cell_name, status_code
FROM ams_cells_vl
WHERE cell_id = p_cell_id
;
SELECT sql_string, sequence_order
FROM ams_discoverer_sql
WHERE workbook_name = p_workbook_name
AND worksheet_name = p_worksheet_name
AND workbook_owner_name = p_workbook_owner_name
ORDER BY sequence_order;
IF p_select_object_type = 'LIST' THEN
OPEN c_valid_list (p_select_object_id);
AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Selected List Name : '|| l_list_name || ', Status : ' || l_status_code );
ELSIF p_select_object_type = 'CELL' THEN
OPEN c_valid_cell (p_select_object_id);
AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Selected Segment Name : '|| l_cell_name || ', Status : ' || l_status_code );
ELSIF p_select_object_type = 'DIWB' THEN
IF (AMS_DEBUG_HIGH_ON) THEN
AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Checking Workbook : '|| p_workbook_name || '::' || p_worksheet_name );
END validate_selection_status;
PROCEDURE validate_product_selections (
p_model_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
L_API_NAME VARCHAR2(60) := 'Validate Product Affinity Selection Status';
SELECT a.category_id, a.inventory_item_id, a.organization_id, a.category_set_id, b.padded_concatenated_segments
FROM ams_act_products a, mtl_system_items_kfv b
WHERE a.ARC_ACT_PRODUCT_USED_BY = 'MODL'
AND a.ACT_PRODUCT_USED_BY_ID = p_model_id
AND a.inventory_item_id = b.inventory_item_id(+)
AND a.organization_id = b.organization_id(+)
AND a.category_set_id in (select distinct category_set_id from ENI_PROD_DEN_HRCHY_PARENTS_V)
;
SELECT 1
FROM ENI_PROD_DEN_HRCHY_PARENTS_V
WHERE category_id = p_cat_id
AND (disable_date IS NULL OR disable_date > SYSDATE)
;
SELECT 1
FROM mtl_system_items_kfv items, mtl_item_categories_v cats
WHERE cats.category_id = p_cat_id
AND cats.inventory_item_id = p_prod_id
AND items.inventory_item_id = p_prod_id
AND UPPER(items.INVENTORY_ITEM_STATUS_CODE) <> 'INACTIVE'
AND items.organization_id = p_org_id
AND cats.organization_id = p_org_id
AND cats.category_set_id = p_cat_set_id
;
AMS_Utility_PVT.debug_message (L_API_NAME || ' :: Raising error...No categories/products selected.');
END validate_product_selections;