[Home] [Help]
5620: BEGIN
5621:
5622: EXECUTE IMMEDIATE
5623:
5624: 'UPDATE IEC_G_RETURN_ENTRIES A
5625:
5626: SET ( A.SUBSET_ID
5627:
5628: , A.DO_NOT_USE_FLAG
5716: BEGIN
5717:
5718: EXECUTE IMMEDIATE
5719:
5720: 'DELETE IEC_G_RETURN_ENTRIES A
5721:
5722: WHERE A.LIST_HEADER_ID = :list_id
5723:
5724: AND A.LIST_ENTRY_ID IN
5756: BEGIN
5757:
5758: EXECUTE IMMEDIATE
5759:
5760: 'INSERT INTO IEC_G_RETURN_ENTRIES
5761:
5762: ( RETURNS_ID
5763:
5764: , CAMPAIGN_ID
5820: , LAST_UPDATE_DATE
5821:
5822: , LAST_UPDATE_LOGIN )
5823:
5824: SELECT IEC_G_RETURN_ENTRIES_S.NEXTVAL
5825:
5826: , :campaign_id
5827:
5828: , :campaign_schedule_id
5886: FROM IEC_VAL_ENTRY_CACHE
5887:
5888: WHERE DO_NOT_USE_FLAG = ''N''
5889:
5890: AND LIST_ENTRY_ID NOT IN (SELECT LIST_ENTRY_ID FROM IEC_G_RETURN_ENTRIES WHERE LIST_HEADER_ID = :list_id)'
5891:
5892: USING p_campaign_id, p_campaign_schedule_id, p_source_type_view, p_list_id, l_user_id, l_login_id, l_login_id, p_list_id;
5893:
5894:
8290: BEGIN
8291:
8292:
8293:
8294: -- Delete specified records in IEC_G_RETURN_ENTRIES
8295:
8296: -- Retrieving information to clean up other tables
8297:
8298: DELETE IEC_G_RETURN_ENTRIES A
8294: -- Delete specified records in IEC_G_RETURN_ENTRIES
8295:
8296: -- Retrieving information to clean up other tables
8297:
8298: DELETE IEC_G_RETURN_ENTRIES A
8299:
8300: WHERE A.LIST_HEADER_ID = p_list_id
8301:
8302: AND A.RETURNS_ID IN (SELECT * FROM TABLE(CAST(p_returns_id_col AS SYSTEM.NUMBER_TBL_TYPE)))
8454: SELECT A.RETURNS_ID
8455:
8456: BULK COLLECT INTO :returns_id_col
8457:
8458: FROM IEC_G_RETURN_ENTRIES A
8459:
8460: WHERE A.LIST_HEADER_ID = :list_id
8461:
8462: AND NOT EXISTS (SELECT LIST_ENTRY_ID
8674: SELECT A.RETURNS_ID, A.LIST_ENTRY_ID
8675:
8676: BULK COLLECT INTO :returns_id_col, :list_entry_id_col
8677:
8678: FROM IEC_G_RETURN_ENTRIES A
8679:
8680: WHERE A.LIST_HEADER_ID = :list_id
8681:
8682: AND A.DO_NOT_USE_FLAG = ''Y'';
10839: A.CONTACT_POINT_ID_S5, A.TIME_ZONE_S5, A.PHONE_COUNTRY_CODE_S5, A.PHONE_AREA_CODE_S5, A.PHONE_NUMBER_S5, A.RAW_PHONE_NUMBER_S5, A.REASON_CODE_S5, A.PHONE_LINE_TYPE_S5, NULL,
10840:
10841: A.CONTACT_POINT_ID_S6, A.TIME_ZONE_S6, A.PHONE_COUNTRY_CODE_S6, A.PHONE_AREA_CODE_S6, A.PHONE_NUMBER_S6, A.RAW_PHONE_NUMBER_S6, A.REASON_CODE_S6, A.PHONE_LINE_TYPE_S6, NULL
10842:
10843: FROM ' || l_source_type_view || ' A, IEC_G_RETURN_ENTRIES B
10844:
10845: WHERE A.LIST_HEADER_ID = B.LIST_HEADER_ID(+)
10846:
10847: AND A.LIST_ENTRY_ID = B.LIST_ENTRY_ID(+)
11480: SELECT RETURNS_ID, LIST_ENTRY_ID, LIST_HEADER_ID, ITM_CC_TZ_ID, DO_NOT_USE_FLAG
11481:
11482: BULK COLLECT INTO :returns_id_col, :list_entry_id_col, :list_id_col, :callable_zone_id_col, :do_not_use_col
11483:
11484: FROM IEC_G_RETURN_ENTRIES
11485:
11486: WHERE RETURNS_ID IN (SELECT * FROM TABLE(CAST(:p_returns_id_col AS SYSTEM.NUMBER_TBL_TYPE)));
11487:
11488: END;'
11510: 'BEGIN
11511:
11512: FORALL i IN :first .. :last
11513:
11514: UPDATE IEC_G_RETURN_ENTRIES A
11515:
11516: SET A.DO_NOT_USE_FLAG = ''Y''
11517:
11518: , A.DO_NOT_USE_REASON = 5
11592: SELECT A.RETURNS_ID
11593:
11594: BULK COLLECT INTO :returns_id_col
11595:
11596: FROM IEC_G_RETURN_ENTRIES A
11597:
11598: WHERE A.RETURNS_ID IN (SELECT * FROM TABLE(CAST(:p_returns_id_col AS SYSTEM.NUMBER_TBL_TYPE)))
11599:
11600: AND A.LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID
11724: , 6, ''_S6''
11725:
11726: , ''_S1'')
11727:
11728: FROM IEC_G_RETURN_ENTRIES
11729:
11730: WHERE RETURNS_ID = :returns_id'
11731:
11732: INTO l_callback_flag_col(i)
11750: LOOP
11751:
11752: EXECUTE IMMEDIATE
11753:
11754: 'UPDATE IEC_G_RETURN_ENTRIES
11755:
11756: SET CALLBACK_FLAG = :callback_flag
11757:
11758: , NEXT_CALL_TIME = :next_call_time
11954: A.CONTACT_POINT_ID_S5, A.TIME_ZONE_S5, A.PHONE_COUNTRY_CODE_S5, A.PHONE_AREA_CODE_S5, A.PHONE_NUMBER_S5, A.RAW_PHONE_NUMBER_S5, A.REASON_CODE_S5, A.PHONE_LINE_TYPE_S5, NULL,
11955:
11956: A.CONTACT_POINT_ID_S6, A.TIME_ZONE_S6, A.PHONE_COUNTRY_CODE_S6, A.PHONE_AREA_CODE_S6, A.PHONE_NUMBER_S6, A.RAW_PHONE_NUMBER_S6, A.REASON_CODE_S6, A.PHONE_LINE_TYPE_S6, NULL
11957:
11958: FROM ' || l_source_type_view || ' A, IEC_G_RETURN_ENTRIES B
11959:
11960: WHERE A.LIST_HEADER_ID = B.LIST_HEADER_ID(+)
11961:
11962: AND A.LIST_ENTRY_ID = B.LIST_ENTRY_ID(+)
12659: SELECT RETURNS_ID
12660:
12661: BULK COLLECT INTO l_from_returns_id_col
12662:
12663: FROM IEC_G_RETURN_ENTRIES
12664:
12665: WHERE LIST_HEADER_ID = p_from_list_id
12666:
12667: AND LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID
12769: SELECT A.RETURNS_ID, A.LIST_HEADER_ID, A.LIST_ENTRY_ID, A.CAMPAIGN_SCHEDULE_ID, B.PARTY_ID
12770:
12771: BULK COLLECT INTO l_from_returns_id_col, l_from_list_id_col, l_from_list_entry_id_col, l_from_schedule_id_col, l_party_id_col
12772:
12773: FROM IEC_G_RETURN_ENTRIES A, AMS_LIST_ENTRIES B
12774:
12775: WHERE A.LIST_HEADER_ID = B.LIST_HEADER_ID
12776:
12777: AND A.LIST_ENTRY_ID = B.LIST_ENTRY_ID
13067: CURSOR c1(src_list_id NUMBER, dest_list_id NUMBER) IS
13068:
13069: SELECT B.RETURNS_ID
13070:
13071: FROM IEC_G_RETURN_ENTRIES B
13072:
13073: WHERE B.LIST_HEADER_ID = src_list_id
13074:
13075: AND B.LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID
13479: CURSOR c1(from_list_id NUMBER) IS
13480:
13481: SELECT B.RETURNS_ID, B.LIST_ENTRY_ID, B.LIST_HEADER_ID
13482:
13483: FROM IEC_G_RETURN_ENTRIES B
13484:
13485: WHERE B.LIST_HEADER_ID = from_list_id
13486:
13487: AND B.LIST_ENTRY_ID IN (SELECT LIST_ENTRY_ID
15130:
15131:
15132: EXECUTE IMMEDIATE
15133:
15134: 'UPDATE IEC_G_RETURN_ENTRIES
15135:
15136: SET CALLBACK_FLAG = ''Y''
15137:
15138: , NEXT_CALL_TIME = :callback_time
16400: Update_CallableZones(p_list_id);
16401:
16402:
16403:
16404: -- Update AMS_LIST_ENTRIES and IEC_G_RETURN_ENTRIES
16405:
16406: EXECUTE IMMEDIATE
16407:
16408: 'BEGIN
16448: EXECUTE IMMEDIATE
16449:
16450: 'BEGIN
16451:
16452: UPDATE IEC_G_RETURN_ENTRIES A
16453:
16454: SET ( SUBSET_ID
16455:
16456: , ITM_CC_TZ_ID