[Home] [Help]
1437: -- At least one of the last three procedures should be called each time
1438: -- when a report related to record counts is generated.
1439: --
1440: -- PROCEDURE UPDATE_ITEM_CC_TZS_COUNTS updates the Available and
1441: -- Unavailable counts in IEC_G_MKTG_ITEM_CC_TZS
1442: --
1443: PROCEDURE UPDATE_ITEM_CC_TZS_COUNTS (
1444: X_ITM_CC_TZ_ID IN NUMBER,
1445: X_STATUS OUT NOCOPY VARCHAR2
1455: -- Get Unavailable Record Count First
1456: BEGIN
1457: SELECT NVL(COUNT(*), 0)
1458: INTO L_RECORD_UNAVAIL_COUNT
1459: FROM iec_g_return_entries a, IEC_G_MKTG_ITEM_CC_TZS C
1460: WHERE C.ITM_CC_TZ_ID = a.itm_cc_tz_id
1461: AND a.itm_cc_tz_id = X_ITM_CC_TZ_ID
1462: AND NVL(a.DO_NOT_USE_FLAG, 'N') = 'N'
1463: AND (C.CALLABLE_FLAG IS NULL
1483:
1484: BEGIN
1485: SELECT A.CALLABLE_FLAG, A.LAST_CALLABLE_TIME
1486: INTO L_CALLABLE_FLAG, L_LAST_CALLABLE_TIME
1487: FROM IEC_G_MKTG_ITEM_CC_TZS A
1488: WHERE A.ITM_CC_TZ_ID = X_ITM_CC_TZ_ID;
1489:
1490: EXCEPTION
1491: WHEN NO_DATA_FOUND THEN
1512: --
1513: BEGIN
1514: SELECT NVL(COUNT(*), 0)
1515: INTO L_RECORD_AVAIL_COUNT
1516: FROM iec_g_return_entries a, IEC_G_MKTG_ITEM_CC_TZS C
1517: WHERE C.ITM_CC_TZ_ID = a.itm_cc_tz_id
1518: AND a.itm_cc_tz_id = X_ITM_CC_TZ_ID
1519: AND NVL(a.DO_NOT_USE_FLAG, 'N') = 'N'
1520: AND (C.CALLABLE_FLAG = 'Y' AND C.LAST_CALLABLE_TIME > SYSDATE
1528: L_RECORD_AVAIL_COUNT := 0;
1529: END;
1530: END IF;
1531:
1532: -- Update IEC_G_MKTG_ITEM_CC_TZS with new Available and Unavailable counts.
1533: -- Some information updated by the servers are recorded for future reference
1534: -- in order to determine if a scan of records is needed.
1535: --
1536: BEGIN
1533: -- Some information updated by the servers are recorded for future reference
1534: -- in order to determine if a scan of records is needed.
1535: --
1536: BEGIN
1537: UPDATE IEC_G_MKTG_ITEM_CC_TZS
1538: SET
1539: ORG_CALLABLE_FLAG = CALLABLE_FLAG,
1540: ORG_LAST_UPDATE_DATE = LAST_UPDATE_DATE,
1541: ORG_LAST_CALLABLE_TIME = LAST_CALLABLE_TIME,
1573: -- The rules for checking all campaigns at the same time are
1574: -- the following:
1575: --
1576: -- 1) COUNT_LAST_UPDATE_DATE is NULL, which
1577: -- happens when the IEC_G_MKTG_ITEM_CC_TZS
1578: -- is updated the first time.
1579: --
1580: -- 2) IEC_G_MKTG_ITEM_CC_TZS has been updated
1581: -- by the Central server or the Dial server
1576: -- 1) COUNT_LAST_UPDATE_DATE is NULL, which
1577: -- happens when the IEC_G_MKTG_ITEM_CC_TZS
1578: -- is updated the first time.
1579: --
1580: -- 2) IEC_G_MKTG_ITEM_CC_TZS has been updated
1581: -- by the Central server or the Dial server
1582: --
1583: -- 3) The CALLABLE_FLAG, LAST_CALLABLE_TIME
1584: -- or RECORD_COUNT has been changed since
1603: --
1604: --
1605: cursor check_all_camp_cursor is
1606: SELECT A.ITM_CC_TZ_ID
1607: FROM IEC_G_MKTG_ITEM_CC_TZS A
1608: WHERE COUNT_LAST_UPDATE_DATE is NULL
1609: OR A.COUNT_LAST_UPDATE_DATE < A.LAST_UPDATE_DATE
1610: OR A.ORG_LAST_UPDATE_DATE <> A.LAST_UPDATE_DATE
1611: OR A.ORG_CALLABLE_FLAG <> A.CALLABLE_FLAG
1671: --
1672:
1673: cursor check_one_camp_cursor(X_CAMPAIGN_ID IN NUMBER) is
1674: SELECT A.ITM_CC_TZ_ID
1675: FROM IEC_G_MKTG_ITEM_CC_TZS A
1676: WHERE A.CAMPAIGN_SCHEDULE_ID in
1677: (SELECT UNIQUE C.SCHEDULE_ID
1678: FROM IEC_G_SCHEDULES_V c
1679: WHERE C.CAMPAIGN_ID = X_CAMPAIGN_ID)
1737: --
1738:
1739: cursor check_schedule_cursor(X_SCHEDULE_ID IN NUMBER) is
1740: SELECT A.ITM_CC_TZ_ID
1741: FROM IEC_G_MKTG_ITEM_CC_TZS A
1742: WHERE A.CAMPAIGN_SCHEDULE_ID = X_SCHEDULE_ID
1743: AND (A.COUNT_LAST_UPDATE_DATE is NULL
1744: OR A.COUNT_LAST_UPDATE_DATE < A.LAST_UPDATE_DATE
1745: OR A.ORG_LAST_UPDATE_DATE <> A.LAST_UPDATE_DATE