[Home] [Help]
634: 'And c.status = ''ACTIVE'' ' ||
635: 'And nvl(c.expiration_date, sysdate+1) > sysdate ' ||
636: 'And not exists ( ' ||
637: 'select 1 ' ||
638: 'from amv_c_chl_item_match match ' ||
639: 'where match.channel_id = c.channel_id ' ||
640: 'and match.item_id = :item_id ' ||
641: 'and match.table_name_code = '''||G_MATCH_ITEM_TABLE || ''') ';
642: IF (l_content_type_id IS NULL OR
1003: 'And item.item_type != ''MESSAGE_ITEM'' ' ||
1004: 'And nvl(item.expiration_date,sysdate+1)>sysdate ' ||
1005: 'And not exists ( ' ||
1006: 'select 1 ' ||
1007: 'from amv_c_chl_item_match match ' ||
1008: 'where match.channel_id = chan.channel_id ' ||
1009: 'and match.item_id = item.item_id ' ||
1010: 'and table_name_code = ''' || G_MATCH_ITEM_TABLE
1011: || ''' ) ';
1152: --
1153: CURSOR Get_ChlItemApprovalStatus_csr IS
1154: Select
1155: approval_status_type
1156: From amv_c_chl_item_match
1157: Where item_id = p_item_id
1158: And channel_id = p_channel_id
1159: And table_name_code = G_MATCH_ITEM_TABLE;
1160: --
1655: l_temp_id NUMBER := FND_API.G_MISS_NUM;
1656: --
1657: CURSOR Check_ExistMatch IS
1658: Select CHANNEL_ITEM_ID
1659: From AMV_C_CHL_ITEM_MATCH
1660: Where TABLE_NAME_CODE = p_table_name_code
1661: And ITEM_ID = p_item_id
1662: And CHANNEL_ID = p_channel_id
1663: ;
1673: CURSOR Get_ChannelInfo_csr IS
1674: select
1675: pub_need_approval_flag,
1676: channel_category_id,
1677: AMV_C_CHL_ITEM_MATCH_S.nextval,
1678: sysdate
1679: from amv_c_channels_b
1680: Where channel_id = p_channel_id;
1681: --
1680: Where channel_id = p_channel_id;
1681: --
1682: CURSOR Get_IdAndDate_csr IS
1683: select
1684: AMV_C_CHL_ITEM_MATCH_S.nextval,
1685: sysdate
1686: from dual;
1687: --
1688: CURSOR Get_Resourceid_csr IS
1788: RAISE FND_API.G_EXC_ERROR;
1789: END IF;
1790: CLOSE Get_OwnerIDAndName_csr;
1791: --
1792: Insert Into AMV_C_CHL_ITEM_MATCH
1793: (
1794: CHANNEL_ITEM_ID,
1795: OBJECT_VERSION_NUMBER,
1796: LAST_UPDATE_DATE,
1850: --
1851: OPEN Get_IdAndDate_csr;
1852: FETCH Get_IdAndDate_csr Into l_match_id, l_current_date;
1853: CLOSE Get_IdAndDate_csr;
1854: Insert Into AMV_C_CHL_ITEM_MATCH
1855: (
1856: CHANNEL_ITEM_ID,
1857: OBJECT_VERSION_NUMBER,
1858: LAST_UPDATE_DATE,
1978: ,cv_item_id NUMBER
1979: ,cv_territory_id NUMBER
1980: ) IS
1981: SELECT item_id
1982: FROM amv_c_chl_item_match
1983: WHERE table_name_code = cv_table_name_code
1984: AND item_id = cv_item_id
1985: AND territory_id = cv_territory_id
1986: AND channel_id IS NULL
1991: ,cv_item_id NUMBER
1992: ,cv_category_id NUMBER
1993: ) IS
1994: SELECT item_id
1995: FROM amv_c_chl_item_match
1996: WHERE table_name_code = cv_table_name_code
1997: AND item_id = cv_item_id
1998: AND channel_category_id = cv_category_id
1999: AND channel_id IS NULL;
2000:
2001:
2002: CURSOR Check_ExistMatch IS
2003: Select CHANNEL_ITEM_ID
2004: From AMV_C_CHL_ITEM_MATCH
2005: Where TABLE_NAME_CODE = p_table_name_code
2006: And ITEM_ID = p_item_id
2007: And CHANNEL_ID = p_channel_id
2008: ;
2018: CURSOR Get_ChannelInfo_csr IS
2019: select
2020: pub_need_approval_flag,
2021: channel_category_id,
2022: AMV_C_CHL_ITEM_MATCH_S.nextval,
2023: sysdate
2024: from amv_c_channels_b
2025: Where channel_id = p_channel_id;
2026: --
2025: Where channel_id = p_channel_id;
2026: --
2027: CURSOR Get_IdAndDate_csr IS
2028: select
2029: AMV_C_CHL_ITEM_MATCH_S.nextval,
2030: sysdate
2031: from dual;
2032: --
2033: BEGIN
2097: --DBMS_OUTPUT.PUT_LINE('Hello');
2098: --DBMS_OUTPUT.PUT_LINE('p_channel_id = '||NVL(p_channel_id,0));
2099: --DBMS_OUTPUT.PUT_LINE('p_item_id = '||to_char(p_item_id));
2100: -- Create the match record
2101: INSERT INTO AMV_C_CHL_ITEM_MATCH
2102: (CHANNEL_ITEM_ID
2103: ,OBJECT_VERSION_NUMBER
2104: ,LAST_UPDATE_DATE
2105: ,LAST_UPDATED_BY
2188: RAISE FND_API.G_EXC_ERROR;
2189: END IF;
2190: CLOSE Get_OwnerIDAndName_csr;
2191: --
2192: Insert Into AMV_C_CHL_ITEM_MATCH
2193: (
2194: CHANNEL_ITEM_ID,
2195: OBJECT_VERSION_NUMBER,
2196: LAST_UPDATE_DATE,
2263: --
2264: OPEN Get_IdAndDate_csr;
2265: FETCH Get_IdAndDate_csr Into l_match_id, l_current_date;
2266: CLOSE Get_IdAndDate_csr;
2267: Insert Into AMV_C_CHL_ITEM_MATCH
2268: (
2269: CHANNEL_ITEM_ID,
2270: OBJECT_VERSION_NUMBER,
2271: LAST_UPDATE_DATE,
2393: CURSOR Get_ChannelInfo_csr IS
2394: select
2395: pub_need_approval_flag,
2396: channel_category_id,
2397: AMV_C_CHL_ITEM_MATCH_S.nextval,
2398: sysdate
2399: from amv_c_channels_b
2400: Where channel_id = p_channel_id;
2401: --
2400: Where channel_id = p_channel_id;
2401: --
2402: CURSOR Get_IdAndDate_csr IS
2403: select
2404: AMV_C_CHL_ITEM_MATCH_S.nextval,
2405: sysdate
2406: from dual;
2407: --
2408: BEGIN
2447:
2448: IF p_territory_id <> FND_API.G_MISS_NUM
2449: AND p_territory_id IS NOT NULL THEN
2450:
2451: DELETE FROM amv_c_chl_item_match
2452: WHERE territory_id = p_territory_id
2453: AND item_id = p_item_id
2454: AND table_name_code = p_table_name_code
2455: AND channel_id IS NULL
2458: -- Proceed with the regular logic
2459: IF (p_category_id = FND_API.G_MISS_NUM) THEN
2460: --
2461: -- delete from channel
2462: DELETE FROM amv_c_chl_item_match
2463: WHERE channel_id = p_channel_id
2464: AND item_id = p_item_id
2465: AND table_name_code = p_table_name_code;
2466: --
2474: END IF;
2475: RAISE FND_API.G_EXC_ERROR;
2476: END IF;
2477: --
2478: DELETE FROM amv_c_chl_item_match
2479: WHERE channel_category_id = p_category_id
2480: AND item_id = p_item_id
2481: AND table_name_code = p_table_name_code
2482: AND channel_id is null;
2672: --
2673: CURSOR c_territory_item IS
2674: SELECT DISTINCT
2675: item_id
2676: FROM amv_c_chl_item_match
2677: WHERE territory_id = p_terr_id
2678: AND channel_id IS NULL
2679: AND channel_category_id IS NULL
2680: AND table_name_code = p_table_name_code;