[Home] [Help]
4: --
5: -- HISTORY
6: -- 06/17/2003 SOLIN Created.
7: -- 11/05/2003 SOLIN Bug 3240753
8: -- Pass party_id, party_site_id to as_import_interface
9: -- 02/23/2004 SOLIN Bug 3454115
10: -- Add "AND aap.level_type_code = 'FAMILY'" in cursor
11: -- C_Get_Category
12: -- 04/27/2004 SOLIN Bug 3584079, 3583298
428: CURSOR C_Match_Reponse1(c_lead_interaction_lookback NUMBER,
429: c_customer_id NUMBER,
430: c_contact_party_id NUMBER) IS
431: SELECT imp.import_interface_id--, NVL(imp.interaction_score, 0)
432: FROM as_import_interface imp
433: WHERE imp.creation_date > (SYSDATE - c_lead_interaction_lookback)
434: AND imp.party_id = c_customer_id
435: AND imp.contact_party_id = c_contact_party_id
436: AND imp.sales_lead_id IS NULL
440:
441: CURSOR C_Match_Reponse2(c_lead_interaction_lookback NUMBER,
442: c_customer_id NUMBER) IS
443: SELECT imp.import_interface_id--, NVL(imp.interaction_score, 0)
444: FROM as_import_interface imp
445: WHERE imp.creation_date > (SYSDATE - c_lead_interaction_lookback)
446: AND imp.party_id = c_customer_id
447: AND imp.sales_lead_id IS NULL
448: AND imp.source_system = 'INTERACTION' -- new in 11.5.10
456: ORDER BY il.score desc;
457:
458: CURSOR C_Get_Run_Import_Flag(c_batch_id NUMBER) IS
459: SELECT 'Y'
460: FROM as_import_interface
461: WHERE batch_id = c_batch_id
462: AND source_system = 'INTERACTION';
463: BEGIN
464: g_debug_flag := p_debug_mode;
778: p_OBJECT_VERSION_NUMBER => 1,
779: p_SCORE => l_interaction_score);
780: ELSE
781: -- This interaction can't match any lead.
782: -- Find matched record in as_import_interface, if no open lead
783: -- matched
784: l_import_interface_id := NULL;
785:
786: IF FND_PROFILE.Value('AS_INTR_MATCH_B2B_LEAD_CONTACT') = 'Y' AND
813: IF l_interaction_score > l_highest_score
814: THEN
815: -- All interactions for this response have score lower
816: -- than that of this interaction.
817: UPDATE as_import_interface
818: SET last_update_date = SYSDATE,
819: last_updated_by = fnd_global.user_id,
820: last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
821: interaction_score = NVL(interaction_score, 0)
823: promotion_id = l_source_code_id_tbl(i),
824: batch_id = l_batch_id
825: WHERE import_interface_id = l_import_interface_id;
826: ELSE
827: UPDATE as_import_interface
828: SET last_update_date = SYSDATE,
829: last_updated_by = fnd_global.user_id,
830: last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
831: interaction_score = NVL(interaction_score, 0)
833: batch_id = l_batch_id
834: WHERE import_interface_id = l_import_interface_id;
835: END IF;
836: ELSE
837: INSERT INTO AS_IMPORT_INTERFACE(
838: IMPORT_INTERFACE_ID, LAST_UPDATE_DATE,
839: LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
840: LAST_UPDATE_LOGIN, REQUEST_ID,
841: PROGRAM_APPLICATION_ID, PROGRAM_ID,
845: PARTY_SITE_ID, SOURCE_SYSTEM, BATCH_ID,
846: REL_PARTY_ID, CONTACT_PARTY_ID,
847: INTERACTION_SCORE, SOURCE_PRIMARY_REFERENCE
848: ) VALUES (
849: AS_IMPORT_INTERFACE_S.nextval, SYSDATE,
850: fnd_global.user_id, SYSDATE, fnd_global.user_id,
851: FND_GLOBAL.CONC_LOGIN_ID, FND_GLOBAL.Conc_Request_Id,
852: FND_GLOBAL.Prog_Appl_Id, FND_GLOBAL.Conc_Program_Id,
853: SYSDATE, 'LEAD_LOAD', SYSDATE, 'NEW',
923: -- engine can match this interaction again next time.
924: l_interaction_score_threshold :=
925: NVL(TO_NUMBER(FND_PROFILE.Value('AS_INTERACTION_SCORE_THRESHOLD')), 0);
926: AML_DEBUG('intr score threshold: ' || l_interaction_score_threshold);
927: UPDATE as_import_interface
928: SET last_update_date = SYSDATE,
929: last_updated_by = fnd_global.user_id,
930: last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
931: batch_id = NULL
932: WHERE batch_id = l_batch_id
933: AND source_system = 'INTERACTION'
934: AND interaction_score < l_interaction_score_threshold;
935:
936: -- Only if there is one or more records in as_import_interface,
937: -- then we launch lead import program.
938: l_run_import_flag := 'N';
939: OPEN C_Get_Run_Import_Flag(l_batch_id);
940: FETCH C_Get_Run_Import_Flag INTO l_run_import_flag;