45: AS
46:
47: L_CALLABLE_FLAG IEC_G_MKTG_ITEM_CC_TZS.CALLABLE_FLAG%TYPE;
48: L_STILL_CALLABLE BINARY_INTEGER;
49: L_CALLBACK_FLAG IEC_G_RETURN_ENTRIES.CALLBACK_FLAG%TYPE;
50: L_CHECKED_OUT_FLAG IEC_G_RETURN_ENTRIES.RECORD_OUT_FLAG%TYPE;
51: L_CALLBACK_EXPIRATION BINARY_INTEGER;
52: L_STATUS_CODE IEC_G_LIST_SUBSETS.STATUS_CODE%TYPE;
53: L_GROUP_COUNT NUMBER;
46:
47: L_CALLABLE_FLAG IEC_G_MKTG_ITEM_CC_TZS.CALLABLE_FLAG%TYPE;
48: L_STILL_CALLABLE BINARY_INTEGER;
49: L_CALLBACK_FLAG IEC_G_RETURN_ENTRIES.CALLBACK_FLAG%TYPE;
50: L_CHECKED_OUT_FLAG IEC_G_RETURN_ENTRIES.RECORD_OUT_FLAG%TYPE;
51: L_CALLBACK_EXPIRATION BINARY_INTEGER;
52: L_STATUS_CODE IEC_G_LIST_SUBSETS.STATUS_CODE%TYPE;
53: L_GROUP_COUNT NUMBER;
54:
69: , decode(sign(nvl(NEXT_CALL_TIME, SYSDATE) - SYSDATE), 0, 0, 1, 1, 0)
70: , a.record_out_flag
71: , c.status_code
72: , count(*)
73: from iec_g_return_entries a
74: , iec_g_mktg_item_Cc_tzs b
75: , iec_g_list_subsets c
76: where a.list_header_id = L_LIST_ID
77: and a.list_header_id = c.list_header_id
264: ----------------------------------------------------------------
265: BEGIN
266:
267: FORALL j IN P_RETURNS_ID_TAB.FIRST..P_RETURNS_ID_TAB.LAST
268: UPDATE IEC_G_RETURN_ENTRIES
269: SET RECORD_OUT_FLAG = 'Y'
270: , CHECKOUT_ACTION_ID = P_SERVER_ID
271: , RECORD_RELEASE_TIME = SYSDATE
272: , LAST_UPDATE_DATE = SYSDATE
292: select
293: d.list_entry_id,
294: d.returns_id,
295: c.priority
296: from iec_g_return_entries d
297: , iec_g_list_subsets c
298: where itm_cc_tz_id in
299: ( select itm_cc_tz_id
300: from iec_g_mktg_item_cc_tzs
320: l_index BINARY_INTEGER := 0;
321: l_callback_count BINARY_INTEGER := 0;
322: l_returns_id_tab SYSTEM.NUMBER_TBL_TYPE;
323:
324: l_list_entry_id IEC_G_RETURN_ENTRIES.LIST_ENTRY_ID%TYPE;
325: l_subset_priority IEC_G_LIST_SUBSETS.PRIORITY%TYPE;
326: l_returns_id IEC_G_RETURN_ENTRIES.RETURNS_ID%TYPE;
327: l_callable_flag VARCHAR2(1);
328: l_return_code VARCHAR2(1);
322: l_returns_id_tab SYSTEM.NUMBER_TBL_TYPE;
323:
324: l_list_entry_id IEC_G_RETURN_ENTRIES.LIST_ENTRY_ID%TYPE;
325: l_subset_priority IEC_G_LIST_SUBSETS.PRIORITY%TYPE;
326: l_returns_id IEC_G_RETURN_ENTRIES.RETURNS_ID%TYPE;
327: l_callable_flag VARCHAR2(1);
328: l_return_code VARCHAR2(1);
329: l_error_code NUMBER;
330:
465: select
466: d.list_entry_id,
467: d.returns_id,
468: c.priority
469: from iec_g_return_entries d
470: , iec_g_list_subsets c
471: where itm_cc_tz_id in
472: ( select itm_cc_tz_id
473: from iec_g_mktg_item_cc_tzs
494: l_index BINARY_INTEGER := 0;
495: l_callback_count BINARY_INTEGER := 0;
496: l_returns_id_tab SYSTEM.NUMBER_TBL_TYPE := SYSTEM.NUMBER_TBL_TYPE();
497:
498: l_list_entry_id IEC_G_RETURN_ENTRIES.LIST_ENTRY_ID%TYPE;
499: l_subset_priority IEC_G_LIST_SUBSETS.PRIORITY%TYPE;
500: l_returns_id IEC_G_RETURN_ENTRIES.RETURNS_ID%TYPE;
501: l_callable_flag VARCHAR2(1);
502: l_return_code VARCHAR2(1);
496: l_returns_id_tab SYSTEM.NUMBER_TBL_TYPE := SYSTEM.NUMBER_TBL_TYPE();
497:
498: l_list_entry_id IEC_G_RETURN_ENTRIES.LIST_ENTRY_ID%TYPE;
499: l_subset_priority IEC_G_LIST_SUBSETS.PRIORITY%TYPE;
500: l_returns_id IEC_G_RETURN_ENTRIES.RETURNS_ID%TYPE;
501: l_callable_flag VARCHAR2(1);
502: l_return_code VARCHAR2(1);
503: l_error_code NUMBER;
504:
636: )
637: AS
638:
639: CURSOR l_entry_query_cursor(L_SUBSET_ID NUMBER) IS
640: select /*+ index ( iec_g_return_entries, iec_g_return_entries_n8 ) */ list_entry_id,
641: returns_id
642: from iec_g_return_entries
643: where itm_cc_tz_id in
644: ( select itm_cc_tz_id
638:
639: CURSOR l_entry_query_cursor(L_SUBSET_ID NUMBER) IS
640: select /*+ index ( iec_g_return_entries, iec_g_return_entries_n8 ) */ list_entry_id,
641: returns_id
642: from iec_g_return_entries
643: where itm_cc_tz_id in
644: ( select itm_cc_tz_id
645: from iec_g_mktg_item_cc_tzs
646: where subset_id = L_SUBSET_ID