APPS.AMS_RUNTIME_CAMP_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 121

   SELECT aao.activity_offer_id,aao.qp_list_header_id,aao.act_offer_used_by_id
   FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
   WHERE aao.arc_act_offer_used_by = 'CSCH' AND aao.qp_list_header_id = qlhv.list_header_id
   AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
   AND qlhv.active_flag = 'Y' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
   AND qlhv.ask_for_flag= 'Y'  AND csch.status_code= 'ACTIVE'  AND csch.activity_id=40 AND aao.activity_offer_id = l_activity_id;

Line: 233

   ' SELECT acsb.schedule_id
        FROM ams_campaign_schedules_b acsb
        WHERE acsb.status_code = ''ACTIVE''
              AND acsb.active_flag = ''Y''
              AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
              AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
              AND acsb.schedule_id IN
	      (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_sched_lst AS JTF_NUMBER_TABLE)) t)';

Line: 245

   ' SELECT acsb.schedule_id
        FROM ams_campaign_schedules_b acsb,
             ams_lookups LO
        WHERE acsb.status_code = ''ACTIVE''
              AND acsb.active_flag = ''Y''
              AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
              AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
              AND LO.lookup_type = ''AMS_PRIORITY''
              AND NVL(acsb.priority,''LOW'') = LO.lookup_code
              AND acsb.schedule_id IN
	      (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_sched_lst AS JTF_NUMBER_TABLE)) t)';

Line: 265

          select L.list_used_by_id
          from ams_act_lists L
          where L.list_used_by_id = acsb.schedule_id
                AND L.list_used_by = ''CSCH''
        )
        AND
        NOT EXISTS
        (
          select S.act_market_segment_used_by_id
          from ams_act_market_segments S
          where S.act_market_segment_used_by_id = acsb.schedule_id
          AND S.arc_act_market_segment_used_by = ''CSCH''
        )
      )
      OR
      (
        EXISTS
        (
          SELECT L2.list_used_by_id
          FROM ams_act_lists L2
          WHERE L2.list_used_by_id = acsb.schedule_id
                AND L2.list_used_by = ''CSCH''
                AND L2.list_act_type = ''TARGET''
                AND NOT EXISTS (
                  SELECT E2.list_header_id
                  FROM ams_list_entries E2
                  WHERE E2.list_header_id = L2.list_header_id
                )
         )
       )
       OR
       (
         EXISTS
         (
           SELECT L3.list_used_by_id
           FROM ams_act_lists L3
           WHERE L3.list_used_by_id = acsb.schedule_id
           AND L3.list_used_by = ''CSCH''
           AND L3.list_act_type = ''TARGET''
           AND EXISTS (
              SELECT E3.list_header_id
              FROM ams_list_entries E3
              WHERE E3.list_header_id = L3.list_header_id
	      AND E3.party_id = :party_id1
           )
         )
       )
       OR
       (
         EXISTS
         (
           SELECT S.act_market_segment_used_by_id
           FROM ams_act_market_segments S
           WHERE S.act_market_segment_used_by_id = acsb.schedule_id
                AND S.arc_act_market_segment_used_by = ''CSCH''
                AND EXISTS (
                   SELECT P2.market_segment_id
                   FROM ams_party_market_segments P2
                   WHERE S.market_segment_id = P2.market_segment_id
                   AND P2.party_id = :party_id2
                )
         )
       )
     )';

Line: 603

   ' SELECT acsb.schedule_id
        FROM ams_campaign_schedules_b acsb,
             ams_iba_cpn_items_denorm D
        WHERE acsb.schedule_id = D.object_used_by_id
              AND acsb.status_code = ''ACTIVE''
              AND acsb.active_flag = ''Y''
              AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
              AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
              AND D.item_id IN (';

Line: 614

   ' SELECT acsb.schedule_id
        FROM ams_campaign_schedules_b acsb,
             ams_iba_cpn_items_denorm D,
             ams_lookups LO
        WHERE acsb.schedule_id = D.object_used_by_id
              AND acsb.status_code = ''ACTIVE''
              AND acsb.active_flag = ''Y''
              AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
              AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
              AND LO.lookup_type = ''AMS_PRIORITY''
              AND NVL(acsb.priority,''LOW'') = LO.lookup_code
              AND D.item_id IN (';

Line: 633

          select L.list_used_by_id
          from ams_act_lists L
          where L.list_used_by_id = acsb.schedule_id
                AND L.list_used_by = ''CSCH''
        )
        AND
        NOT EXISTS
        (
          select S.act_market_segment_used_by_id
          from ams_act_market_segments S
          where S.act_market_segment_used_by_id = acsb.schedule_id
                AND S.arc_act_market_segment_used_by = ''CSCH''
        )
      )
      OR
      (
        EXISTS
        (
          SELECT L2.list_used_by_id
          FROM ams_act_lists L2
          WHERE L2.list_used_by_id = acsb.schedule_id
                AND L2.list_used_by = ''CSCH''
                AND L2.list_act_type = ''TARGET''
                AND NOT EXISTS (
                  SELECT E2.list_header_id
                  FROM ams_list_entries E2
                  WHERE E2.list_header_id = L2.list_header_id
                )
         )
       )
       OR
       (
         EXISTS
         (
           SELECT L3.list_used_by_id
           FROM ams_act_lists L3
           WHERE L3.list_used_by_id = acsb.schedule_id
           AND L3.list_used_by = ''CSCH''
           AND L3.list_act_type = ''TARGET''
           AND EXISTS (
              SELECT E3.list_header_id
              FROM ams_list_entries E3
              WHERE E3.list_header_id = L3.list_header_id
	      AND E3.party_id = :party_id1
           )
         )
       )
       OR
       (
         EXISTS
         (
           SELECT S.act_market_segment_used_by_id
           FROM ams_act_market_segments S
           WHERE S.act_market_segment_used_by_id = acsb.schedule_id
                AND S.arc_act_market_segment_used_by = ''CSCH''
                AND EXISTS (
                   SELECT P2.market_segment_id
                   FROM ams_party_market_segments P2
                   WHERE S.market_segment_id = P2.market_segment_id
                   AND P2.party_id = :party_id2
                )
         )
       )
     )';

Line: 758

     select inventory_item_id
     bulk collect into l_quote_prod_lst
     from aso_quote_lines_all_v
     where quote_header_id = p_quote_id;

Line: 988

   ' SELECT acsb.schedule_id
        FROM ams_campaign_schedules_b acsb,
             ams_iba_cpn_items_denorm D
        WHERE acsb.schedule_id = D.object_used_by_id
              AND D.object_used_by_type = ''CSCH''
              AND acsb.status_code = ''ACTIVE''
              AND acsb.active_flag = ''Y''
              AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
              AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
              AND D.item_id IN (';

Line: 1000

   ' SELECT acsb.schedule_id
        FROM ams_campaign_schedules_b acsb,
             ams_iba_cpn_items_denorm D,
             ams_lookups LO
        WHERE acsb.schedule_id = D.object_used_by_id
              AND D.object_used_by_type = ''CSCH''
              AND acsb.status_code = ''ACTIVE''
              AND acsb.active_flag = ''Y''
              AND NVL(acsb.start_date_time,SYSDATE) <= SYSDATE
              AND NVL(acsb.end_date_time,SYSDATE) >= SYSDATE
              AND LO.lookup_type = ''AMS_PRIORITY''
              AND NVL(acsb.priority,''LOW'') = LO.lookup_code
              AND D.item_id IN (';

Line: 1020

          select L.list_used_by_id
          from ams_act_lists L
          where L.list_used_by_id = acsb.schedule_id
                AND L.list_used_by = ''CSCH''
        )
        AND
        NOT EXISTS
        (
          select S.act_market_segment_used_by_id
          from ams_act_market_segments S
          where S.act_market_segment_used_by_id = acsb.schedule_id
                AND S.arc_act_market_segment_used_by = ''CSCH''
        )
      )
      OR
      (
        EXISTS
        (
          SELECT L2.list_used_by_id
          FROM ams_act_lists L2
          WHERE L2.list_used_by_id = acsb.schedule_id
                AND L2.list_used_by = ''CSCH''
                AND L2.list_act_type = ''TARGET''
                AND NOT EXISTS (
                  SELECT E2.list_header_id
                  FROM ams_list_entries E2
                  WHERE E2.list_header_id = L2.list_header_id
                )
         )
       )
       OR
       (
         EXISTS
         (
           SELECT L3.list_used_by_id
           FROM ams_act_lists L3
           WHERE L3.list_used_by_id = acsb.schedule_id
           AND L3.list_used_by = ''CSCH''
           AND L3.list_act_type = ''TARGET''
           AND EXISTS (
              SELECT E3.list_header_id
              FROM ams_list_entries E3
              WHERE E3.list_header_id = L3.list_header_id
	      AND E3.party_id = :party_id1
           )
         )
       )
       OR
       (
         EXISTS
         (
           SELECT S.act_market_segment_used_by_id
           FROM ams_act_market_segments S
           WHERE S.act_market_segment_used_by_id = acsb.schedule_id
                AND S.arc_act_market_segment_used_by = ''CSCH''
                AND EXISTS (
                   SELECT P2.market_segment_id
                   FROM ams_party_market_segments P2
                   WHERE S.market_segment_id = P2.market_segment_id
                   AND P2.party_id = :party_id2
                )
         )
       )
     )';

Line: 1437

   l_offer_stmt VARCHAR2(2500) := 'Select qlhv.list_header_id
    FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch
    WHERE aao.arc_act_offer_used_by = ''CSCH'' AND aao.qp_list_header_id = qlhv.list_header_id
    and aao.qp_list_header_id in (SELECT COLUMN_VALUE FROM TABLE(CAST(:l_in_offer_lst AS JTF_NUMBER_TABLE)) )
    AND aao.act_offer_used_by_id = csch.schedule_id
    AND qlhv.active_flag =''Y'' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1)
    and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
    AND qlhv.ask_for_flag = ''Y'' AND csch.status_code = ''ACTIVE'' AND csch.activity_id=40';

Line: 1712

		--finds the position of the Offer Id to be inserted
		pos := l_offer_lst2.COUNT+1;

Line: 1894

   l_offer_stmt VARCHAR2(2500) := 'Select qlhv.list_header_id
    FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch
    WHERE aao.arc_act_offer_used_by = ''CSCH'' AND aao.qp_list_header_id = qlhv.list_header_id
    and aao.qp_list_header_id in (SELECT COLUMN_VALUE FROM TABLE(CAST(:l_in_offer_lst AS JTF_NUMBER_TABLE)) )
    AND aao.act_offer_used_by_id = csch.schedule_id
    AND qlhv.active_flag =''Y'' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1)
    and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
    AND qlhv.ask_for_flag = ''Y'' AND csch.status_code = ''ACTIVE'' AND csch.activity_id=40';

Line: 1915

   select aao.activity_offer_id,qlhv.list_header_id
   FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
   WHERE aao.arc_act_offer_used_by = 'CSCH' AND aao.qp_list_header_id = qlhv.list_header_id
   AND aao.act_offer_used_by_id = csch.schedule_id  AND camp.campaign_id = csch.campaign_id
   AND qlhv.active_flag = 'Y' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
   AND qlhv.ask_for_flag= 'Y' AND csch.status_code= 'ACTIVE'
   AND csch.activity_id=40  AND qlhv.list_header_id = l_qp_list_header_id;

Line: 1980

      select inventory_item_id
      bulk collect into l_quote_prod_lst
      from aso_quote_lines_all_v
      where quote_header_id = p_quote_id;

Line: 2305

   l_offer_stmt VARCHAR2(2000) := 'SELECT qlhv.list_header_id
      FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
      WHERE aao.arc_act_offer_used_by = ''CSCH'' AND aao.qp_list_header_id = qlhv.list_header_id
      AND aao.act_offer_used_by_id = csch.schedule_id AND camp.campaign_id = csch.campaign_id
      AND qlhv.active_flag = ''Y'' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
      AND qlhv.ask_for_flag= ''Y'' AND csch.status_code= ''ACTIVE''  AND csch.activity_id=40
      AND qlhv.list_header_id IN (';

Line: 2314

   SELECT aao.activity_offer_id,qlhv.list_header_id
   FROM ozf_act_offers aao,qp_list_headers_b qlhv,ams_campaign_schedules_b csch,ams_campaigns_all_b camp
   WHERE aao.arc_act_offer_used_by = 'CSCH' AND aao.qp_list_header_id = qlhv.list_header_id
   AND aao.act_offer_used_by_id = csch.schedule_id  AND camp.campaign_id = csch.campaign_id
   AND qlhv.active_flag = 'Y' AND TRUNC(sysdate) BETWEEN NVL(TRUNC(qlhv.start_date_active),TRUNC(sysdate)-1) and NVL(TRUNC(qlhv.end_date_active),trunc(sysdate)+1)
   AND qlhv.ask_for_flag= 'Y'  AND qlhv.source_system_code = 'AMS' AND csch.status_code= 'ACTIVE'
   AND csch.activity_id=40  AND qlhv.list_header_id = l_qp_list_header_id;