397: CURSOR C_get_current_resource IS
398: SELECT res.resource_id
399: FROM jtf_rs_resource_extns res
400: WHERE res.category IN ('EMPLOYEE', 'PARTY')
401: AND res.user_id = fnd_global.user_id;
402:
403: CURSOR c_get_group_id (c_resource_id NUMBER, c_rs_group_member VARCHAR2,
404: c_sales VARCHAR2, c_telesales VARCHAR2,
405: c_fieldsales VARCHAR2, c_prm VARCHAR2, c_y VARCHAR2) IS
638: l_sales_lead_line_id := NULL;
639: AS_SALES_LEAD_LINES_PKG.Sales_Lead_Line_Insert_Row(
640: px_SALES_LEAD_LINE_ID => l_sales_lead_line_id,
641: p_LAST_UPDATE_DATE => SYSDATE,
642: p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
643: p_CREATION_DATE => SYSDATE,
644: p_CREATED_BY => FND_GLOBAL.USER_ID,
645: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
646: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
640: px_SALES_LEAD_LINE_ID => l_sales_lead_line_id,
641: p_LAST_UPDATE_DATE => SYSDATE,
642: p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
643: p_CREATION_DATE => SYSDATE,
644: p_CREATED_BY => FND_GLOBAL.USER_ID,
645: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
646: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
647: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
648: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
641: p_LAST_UPDATE_DATE => SYSDATE,
642: p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
643: p_CREATION_DATE => SYSDATE,
644: p_CREATED_BY => FND_GLOBAL.USER_ID,
645: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
646: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
647: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
648: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
649: p_PROGRAM_UPDATE_DATE => SYSDATE,
642: p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
643: p_CREATION_DATE => SYSDATE,
644: p_CREATED_BY => FND_GLOBAL.USER_ID,
645: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
646: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
647: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
648: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
649: p_PROGRAM_UPDATE_DATE => SYSDATE,
650: p_SALES_LEAD_ID => l_sales_lead_id,
643: p_CREATION_DATE => SYSDATE,
644: p_CREATED_BY => FND_GLOBAL.USER_ID,
645: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
646: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
647: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
648: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
649: p_PROGRAM_UPDATE_DATE => SYSDATE,
650: p_SALES_LEAD_ID => l_sales_lead_id,
651: p_STATUS_CODE => NULL, -- ???
644: p_CREATED_BY => FND_GLOBAL.USER_ID,
645: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
646: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
647: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
648: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
649: p_PROGRAM_UPDATE_DATE => SYSDATE,
650: p_SALES_LEAD_ID => l_sales_lead_id,
651: p_STATUS_CODE => NULL, -- ???
652: p_CATEGORY_ID => l_category_id,
685: IF l_check_rerun IS NOT NULL
686: THEN
687: UPDATE as_sales_leads
688: SET last_update_date = SYSDATE,
689: last_updated_by = fnd_global.user_id,
690: last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
691: qualified_flag = 'N',
692: lead_rank_id = NULL,
693: channel_code = NULL,
686: THEN
687: UPDATE as_sales_leads
688: SET last_update_date = SYSDATE,
689: last_updated_by = fnd_global.user_id,
690: last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
691: qualified_flag = 'N',
692: lead_rank_id = NULL,
693: channel_code = NULL,
694: interaction_score = NVL(interaction_score, 0)
750: -- Lead doesn't need to be reprocessed, update
751: -- interaction_score
752: UPDATE as_sales_leads
753: SET last_update_date = SYSDATE,
754: last_updated_by = fnd_global.user_id,
755: last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
756: interaction_score = NVL(interaction_score, 0)
757: + l_interaction_score
758: WHERE sales_lead_id = l_sales_lead_id;
751: -- interaction_score
752: UPDATE as_sales_leads
753: SET last_update_date = SYSDATE,
754: last_updated_by = fnd_global.user_id,
755: last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
756: interaction_score = NVL(interaction_score, 0)
757: + l_interaction_score
758: WHERE sales_lead_id = l_sales_lead_id;
759:
766: p_INTERACTION_ID => l_interaction_id_tbl(i),
767: p_IMPORT_INTERFACE_ID => NULL,
768: p_SALES_LEAD_ID => l_sales_lead_id,
769: p_CREATION_DATE => SYSDATE,
770: p_CREATED_BY => fnd_global.user_id,
771: p_LAST_UPDATE_DATE => SYSDATE,
772: p_LAST_UPDATED_BY => fnd_global.user_id,
773: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
774: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
768: p_SALES_LEAD_ID => l_sales_lead_id,
769: p_CREATION_DATE => SYSDATE,
770: p_CREATED_BY => fnd_global.user_id,
771: p_LAST_UPDATE_DATE => SYSDATE,
772: p_LAST_UPDATED_BY => fnd_global.user_id,
773: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
774: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
775: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
776: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
769: p_CREATION_DATE => SYSDATE,
770: p_CREATED_BY => fnd_global.user_id,
771: p_LAST_UPDATE_DATE => SYSDATE,
772: p_LAST_UPDATED_BY => fnd_global.user_id,
773: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
774: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
775: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
776: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
777: p_PROGRAM_UPDATE_DATE => SYSDATE,
770: p_CREATED_BY => fnd_global.user_id,
771: p_LAST_UPDATE_DATE => SYSDATE,
772: p_LAST_UPDATED_BY => fnd_global.user_id,
773: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
774: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
775: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
776: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
777: p_PROGRAM_UPDATE_DATE => SYSDATE,
778: p_OBJECT_VERSION_NUMBER => 1,
771: p_LAST_UPDATE_DATE => SYSDATE,
772: p_LAST_UPDATED_BY => fnd_global.user_id,
773: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
774: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
775: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
776: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
777: p_PROGRAM_UPDATE_DATE => SYSDATE,
778: p_OBJECT_VERSION_NUMBER => 1,
779: p_SCORE => l_interaction_score);
772: p_LAST_UPDATED_BY => fnd_global.user_id,
773: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
774: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
775: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
776: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
777: p_PROGRAM_UPDATE_DATE => SYSDATE,
778: p_OBJECT_VERSION_NUMBER => 1,
779: p_SCORE => l_interaction_score);
780: ELSE
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)
822: + l_interaction_score,
823: promotion_id = l_source_code_id_tbl(i),
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)
822: + l_interaction_score,
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)
832: + l_interaction_score,
833: batch_id = l_batch_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)
832: + l_interaction_score,
833: batch_id = l_batch_id
834: WHERE import_interface_id = l_import_interface_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',
854: l_source_code_id_tbl(i), l_customer_id_tbl(i),
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',
854: l_source_code_id_tbl(i), l_customer_id_tbl(i),
855: l_customer_id_tbl(i), l_address_id_tbl(i),
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',
854: l_source_code_id_tbl(i), l_customer_id_tbl(i),
855: l_customer_id_tbl(i), l_address_id_tbl(i),
856: l_address_id_tbl(i), 'INTERACTION', l_batch_id,
881: QUANTITY
882: ) VALUES (
883: AS_IMP_LINES_INTERFACE_S.nextval,
884: l_import_interface_id, SYSDATE,
885: fnd_global.user_id, SYSDATE,
886: fnd_global.user_id, FND_GLOBAL.CONC_LOGIN_ID,
887: FND_GLOBAL.Conc_Request_Id, FND_GLOBAL.Prog_Appl_Id,
888: FND_GLOBAL.Conc_Program_Id, SYSDATE,
889: l_source_code_id_tbl(i), l_category_id,
882: ) VALUES (
883: AS_IMP_LINES_INTERFACE_S.nextval,
884: l_import_interface_id, SYSDATE,
885: fnd_global.user_id, SYSDATE,
886: fnd_global.user_id, FND_GLOBAL.CONC_LOGIN_ID,
887: FND_GLOBAL.Conc_Request_Id, FND_GLOBAL.Prog_Appl_Id,
888: FND_GLOBAL.Conc_Program_Id, SYSDATE,
889: l_source_code_id_tbl(i), l_category_id,
890: l_inventory_item_id, l_organization_id, l_uom_code,
883: AS_IMP_LINES_INTERFACE_S.nextval,
884: l_import_interface_id, SYSDATE,
885: fnd_global.user_id, SYSDATE,
886: fnd_global.user_id, FND_GLOBAL.CONC_LOGIN_ID,
887: FND_GLOBAL.Conc_Request_Id, FND_GLOBAL.Prog_Appl_Id,
888: FND_GLOBAL.Conc_Program_Id, SYSDATE,
889: l_source_code_id_tbl(i), l_category_id,
890: l_inventory_item_id, l_organization_id, l_uom_code,
891: l_quantity)
884: l_import_interface_id, SYSDATE,
885: fnd_global.user_id, SYSDATE,
886: fnd_global.user_id, FND_GLOBAL.CONC_LOGIN_ID,
887: FND_GLOBAL.Conc_Request_Id, FND_GLOBAL.Prog_Appl_Id,
888: FND_GLOBAL.Conc_Program_Id, SYSDATE,
889: l_source_code_id_tbl(i), l_category_id,
890: l_inventory_item_id, l_organization_id, l_uom_code,
891: l_quantity)
892: RETURNING IMP_LINES_INTERFACE_ID INTO
903: p_INTERACTION_ID => l_interaction_id_tbl(i),
904: p_IMPORT_INTERFACE_ID => l_import_interface_id,
905: p_SALES_LEAD_ID => NULL,
906: p_CREATION_DATE => SYSDATE,
907: p_CREATED_BY => fnd_global.user_id,
908: p_LAST_UPDATE_DATE => SYSDATE,
909: p_LAST_UPDATED_BY => fnd_global.user_id,
910: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
911: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
905: p_SALES_LEAD_ID => NULL,
906: p_CREATION_DATE => SYSDATE,
907: p_CREATED_BY => fnd_global.user_id,
908: p_LAST_UPDATE_DATE => SYSDATE,
909: p_LAST_UPDATED_BY => fnd_global.user_id,
910: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
911: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
912: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
913: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
906: p_CREATION_DATE => SYSDATE,
907: p_CREATED_BY => fnd_global.user_id,
908: p_LAST_UPDATE_DATE => SYSDATE,
909: p_LAST_UPDATED_BY => fnd_global.user_id,
910: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
911: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
912: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
913: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
914: p_PROGRAM_UPDATE_DATE => SYSDATE,
907: p_CREATED_BY => fnd_global.user_id,
908: p_LAST_UPDATE_DATE => SYSDATE,
909: p_LAST_UPDATED_BY => fnd_global.user_id,
910: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
911: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
912: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
913: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
914: p_PROGRAM_UPDATE_DATE => SYSDATE,
915: p_OBJECT_VERSION_NUMBER => 1,
908: p_LAST_UPDATE_DATE => SYSDATE,
909: p_LAST_UPDATED_BY => fnd_global.user_id,
910: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
911: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
912: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
913: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
914: p_PROGRAM_UPDATE_DATE => SYSDATE,
915: p_OBJECT_VERSION_NUMBER => 1,
916: p_SCORE => l_interaction_score);
909: p_LAST_UPDATED_BY => fnd_global.user_id,
910: p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
911: p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id,
912: p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id,
913: p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id,
914: p_PROGRAM_UPDATE_DATE => SYSDATE,
915: p_OBJECT_VERSION_NUMBER => 1,
916: p_SCORE => l_interaction_score);
917: END IF; -- sales_lead_id is NULL or not
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'
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;