APPS.OZF_UTILITY_PVT SQL Statements

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

Line: 200

   l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);

Line: 294

    l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || p_lookup_table_name;

Line: 341

      SELECT 1 FROM fnd_lookup_values lkup
        WHERE lkup.LOOKUP_TYPE = p_lookup_type
          AND lkup.LOOKUP_CODE = p_lookup_code
          AND lkup.view_application_id = p_view_app_id
          AND lkup.ENABLED_FLAG = 'Y'
          AND lkup.language = USERENV('LANG')
          AND lkup.security_group_id = to_number(decode(substrb(userenv('CLIENT_INFO'),55,1
                                                               ), ' ', '0'
                                                                 , NULL, '0'
                                                                 , substrb(userenv('CLIENT_INFO'),55,10
                                                                          )
                                                        )
                                                 );

Line: 395

   l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);

Line: 578

   SELECT ams_act_logs_s.NEXTVAL,
          ams_act_logs_transaction_id_s.NEXTVAL
     FROM DUAL;

Line: 583

   SELECT rowid
     FROM ams_act_logs
    WHERE activity_log_id = l_my_log_id;

Line: 605

   INSERT INTO ams_act_logs (
      activity_log_id
      -- standard who columns
      ,last_update_date
      ,last_updated_by
      ,creation_date
      ,created_by
      ,last_update_login
      ,object_version_number
      ,act_log_used_by_id
      ,arc_act_log_used_by
      ,log_transaction_id
      ,log_message_text
      ,log_message_level
      ,log_message_type
      ,description
      ,budget_id
      ,threshold_id
      ,notification_creation_date
   )
   VALUES (
       NVL(p_activity_log_id,l_act_log_id)
      -- standard who columns
      ,SYSDATE
      ,FND_GLOBAL.User_Id
      ,SYSDATE
      ,FND_GLOBAL.User_Id
      ,FND_GLOBAL.Conc_Login_Id
      ,1                 -- Object Version Number
      ,p_log_used_by_id
      ,p_arc_log_used_by
      ,NVL(p_transaction_id,l_log_tran_id)
      ,p_msg_data
      ,p_msg_level
      ,p_msg_type
      ,p_desc
      ,p_budget_id
      ,p_threshold_id
      ,p_notification_creat_date
   ) ;

Line: 803

   SELECT source_code,source_code_for_id INTO x_source_code,x_source_id
     FROM ams_source_codes
    WHERE arc_source_code_for = UPPER(p_activity_type)
      AND source_code_for_id  = UPPER(p_activity_id);

Line: 853

   SELECT campaign_name
     FROM ams_campaigns_vl
    WHERE campaign_id = p_object_id;

Line: 859

  SELECT c.campaign_name
  FROM   ams_campaigns_vl c,
         ams_campaign_schedules s
  WHERE s.campaign_schedule_id = p_object_id
  AND   s.campaign_id          = c.campaign_id;

Line: 866

   SELECT deliverable_name
     FROM ams_deliverables_vl
    WHERE deliverable_id = p_object_id;

Line: 871

   SELECT event_header_name
     FROM ams_event_headers_vl
    WHERE event_header_id = p_object_id;

Line: 876

   SELECT event_offer_name
     FROM ams_event_offers_vl
    WHERE event_offer_id = p_object_id;

Line: 881

      SELECT short_name
      FROM   ozf_funds_all_vl
      WHERE  fund_id = p_object_id;

Line: 886

      SELECT party_name
      FROM   hz_parties
      WHERE  party_id = p_object_id;

Line: 1025

      SELECT meaning
      FROM   ozf_lookups
      WHERE  lookup_type = UPPER (p_lookup_type)
      AND    lookup_code = UPPER (p_lookup_code);

Line: 1070

select NAME
 from  HZ_TIMEZONES_VL
 where TIMEZONE_ID = l_time_id;

Line: 1117

select NAME
 from  HZ_TIMEZONES_VL
 where TIMEZONE_ID = l_time_id;

Line: 1345

      SELECT meaning
      FROM   ozf_lookups
      WHERE  lookup_type = UPPER (p_lookup_type)
      AND    lookup_code = UPPER (p_lookup_code);

Line: 1376

      SELECT full_name
      FROM   ams_jtf_rs_emp_v
      WHERE  resource_id = p_resource_id;

Line: 1411

   SELECT area2_code
   FROM   jtf_loc_hierarchies_vl
   WHERE  location_hierarchy_id = p_country_id;

Line: 1416

   SELECT B.area2_code
   FROM   ams_campaigns_vl A, jtf_loc_hierarchies_vl B
   WHERE  A.campaign_id = p_object_id
   AND    A.city_id = B.location_hierarchy_id;

Line: 1466

      SELECT padded_concatenated_segments
      FROM   mtl_system_items_kfv
      WHERE  inventory_item_id = p_prod_id
      AND    organization_id = p_org_id;

Line: 1472

      SELECT DISTINCT padded_concatenated_segments
      FROM   mtl_system_items_kfv
      WHERE  inventory_item_id = p_prod_id;

Line: 1477

      SELECT description
      FROM   mtl_categories_v
      WHERE  category_id = p_prod_id;

Line: 1526

   SELECT qlh.name
   FROM   qp_list_headers_vl qlh, qp_list_lines qll
   WHERE  qll.list_header_id = qlh.list_header_id
   AND    qll.list_line_id = p_price_list_line_id;

Line: 1557

   SELECT unit_of_measure
   FROM   mtl_units_of_measure
   WHERE  uom_code = p_uom_code;

Line: 1588

   SELECT meaning
   FROM   qp_lookups
   WHERE  lookup_type = UPPER(p_lookup_type)
   AND    lookup_code = UPPER(p_lookup_code);

Line: 1619

      SELECT resource_id
      FROM   ams_jtf_rs_emp_v
      WHERE  user_id = p_user_id;

Line: 1723

   SELECT system_status_code
   FROM   ams_user_statuses_vl
   WHERE  user_status_id = p_user_status_id
   AND    enabled_flag = 'Y';

Line: 1756

   SELECT user_status_id
   FROM   ams_user_statuses_vl
   WHERE  system_status_type = p_status_type
   AND    system_status_code = p_status_code
   AND    default_flag = 'Y'
   AND    enabled_flag = 'Y';

Line: 1798

   SELECT theme_approval_flag, budget_approval_flag
   FROM   ams_status_order_rules
   WHERE  system_status_type = l_status_type
   AND    current_status_code = l_old_status_code
   AND    next_status_code = l_new_status_code;

Line: 1857

   SELECT attr_available_flag
   FROM   ams_custom_setup_attr
   WHERE  custom_setup_id = p_custom_setup_id
   -- Following line is added by ptendulk on 19-Jun-2001
   AND    object_attribute = p_approval_type ;

Line: 1905

   SELECT theme_approval_flag, budget_approval_flag
   FROM   ams_status_order_rules
   WHERE  system_status_type = l_status_type
   AND    current_status_code = l_old_status_code
   AND    next_status_code = l_new_status_code;

Line: 1990

      SELECT   gs.set_of_books_id
              ,gs.currency_code
      FROM     gl_sets_of_books gs
              ,org_organization_definitions org
      WHERE  gs.mrc_sob_type_code = 'P'
         AND org.set_of_books_id = gs.set_of_books_id
         AND org.operating_unit = p_org_id;

Line: 2001

      SELECT gs.set_of_books_id
      ,      gs.currency_code
      FROM   gl_sets_of_books gs
      ,      ozf_sys_parameters_all org
      WHERE  org.set_of_books_id = gs.set_of_books_id
      AND    NVL(org.org_id, -99) = NVL(p_org_id, -99);

Line: 2169

      SELECT   gs.set_of_books_id
              ,gs.currency_code
      FROM     gl_sets_of_books gs
              ,org_organization_definitions org
      WHERE  org.set_of_books_id = gs.set_of_books_id
         AND org.operating_unit = p_org_id;

Line: 2179

      SELECT  gs.set_of_books_id
      ,       gs.currency_code
      FROM   gl_sets_of_books gs
      ,      ozf_sys_parameters_all org
      WHERE  org.set_of_books_id = gs.set_of_books_id
      AND    NVL(org.org_id, -99) = NVL(p_org_id, -99);

Line: 2325

       SELECT ledger_id, org_id
       FROM   ozf_funds_all_b
       WHERE  fund_id = p_fund_id;

Line: 2331

       SELECT cat.ledger_id
       FROM   ozf_funds_all_b fund, ams_categories_b cat
       WHERE  fund_id = p_fund_id
       AND    fund.category_id = cat.category_id;

Line: 2417

         SELECT org_id
         FROM   ozf_offers
         WHERE  qp_list_header_id = p_list_header_id;

Line: 2423

         SELECT orig_org_id
         FROM   qp_list_headers_b
         WHERE  list_header_id = p_list_header_id;

Line: 2429

         SELECT budget_source_id, budget_source_type
         FROM ozf_act_budgets
         WHERE act_budget_used_by_id=p_list_header_id;

Line: 2434

         SELECT budget_source_id
         FROM ozf_act_budgets
         WHERE act_budget_used_by_id = p_campaign_id;

Line: 2569

      SELECT currency_code
      FROM   gl_ledgers_public_v
      WHERE  ledger_id = p_ledger_id;

Line: 2779

   SELECT employee_id , user_id, category
   FROM ams_jtf_rs_emp_v
   WHERE resource_id = p_resource_id ;

Line: 2934

   SELECT 1 FROM DUAL
   WHERE EXISTS (SELECT * FROM ams_status_order_rules
                 WHERE current_status_code = p_current_status
                 AND   next_status_code = p_next_status
                 AND   system_status_type = p_status_type ) ;

Line: 3033

SELECT  deliverable_name
FROM    ams_deliverables_vl
WHERE   deliverable_id = p_delv_id;

Line: 3098

           SELECT user_id
           FROM   ams_jtf_rs_emp_v
           WHERE  resource_id = p_resource_id;

Line: 3130

SELECT hp.party_name FROM hz_parties hp , hz_cust_accounts hca
WHERE hca.party_id = hp.party_id
AND hca.status = 'A'
AND hca.cust_account_id = p_cust_account_id;

Line: 3159

SELECT name FROM ams_user_statuses_vl where user_status_id = p_user_status_id
AND system_status_type = p_system_status_type;

Line: 3184

SELECT name FROM fnd_currencies_vl WHERE currency_code = p_currency_code;

Line: 3210

SELECT media_name FROM ams_media_vl
WHERE media_id = p_media_id;

Line: 3238

SELECT meaning FROM fnd_lookups WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;

Line: 3268

   select rr.role_id
   from jtf_rs_role_relations rr
   ,    jtf_rs_roles_vl rl
   where rr.role_id = rl.role_id
   and rl.role_type_code = 'SALES'
   and rl.role_code = 'SALES_REP'
   and rr.delete_flag = 'N'
   and TRUNC(sysdate) between TRUNC(rr.start_date_active)
                          and TRUNC(nvl(rr.end_date_active,sysdate))
   and rr.role_resource_id = cv_resource_id;

Line: 3308

    SELECT SUM(NVL(plan_curr_committed_amt,0))
    FROM ozf_object_fund_summary
    WHERE object_id = list_header_id
    AND object_type = 'OFFR';

Line: 3313

SELECT SUM(AMOUNT)
FROM(
SELECT  uti.plan_curr_amount amount
FROM ozf_funds_utilized_all_b uti, ozf_act_budgets act
WHERE uti.utilization_type ='REQUEST'
AND component_type = 'OFFR'
AND component_id = list_header_id
AND act.activity_budget_id = uti.ams_activity_budget_id
AND act.recal_flag is NULL
UNION ALL
SELECT 0-uti.plan_curr_amount amount
FROM ozf_funds_utilized_all_b uti, ozf_act_budgets act
WHERE uti.utilization_type ='TRANSFER'
AND plan_type = 'OFFR'
AND plan_id = list_header_id
AND act.activity_budget_id = uti.ams_activity_budget_id
AND act.recal_flag is NULL);

Line: 3359

    SELECT SUM(NVL(plan_curr_recal_committed_amt,0))
    FROM ozf_object_fund_summary
    WHERE object_id = list_header_id
    AND object_type = 'OFFR';

Line: 3364

  SELECT SUM(AMOUNT)
FROM(
SELECT  uti.plan_curr_amount amount
FROM ozf_funds_utilized_all_b uti
WHERE uti.utilization_type ='REQUEST'
AND component_type = 'OFFR'
AND component_id = list_header_id
UNION ALL
SELECT 0-uti.plan_curr_amount amount
FROM ozf_funds_utilized_all_b uti
WHERE uti.utilization_type ='TRANSFER'
AND plan_type = 'OFFR'
AND plan_id = list_header_id);

Line: 3396

    SELECT SUM(NVL(plan_curr_utilized_amt,0))
    FROM ozf_object_fund_summary
    WHERE object_id = list_header_id
    AND object_type = 'OFFR';

Line: 3402

  SELECT SUM(uti.plan_curr_amount)
FROM ozf_funds_utilized_all_vl uti
WHERE uti.utilization_type IN
('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL')
AND plan_type = 'OFFR'
AND plan_id = list_header_id;

Line: 3437

    SELECT SUM(NVL(plan_curr_earned_amt,0))
    FROM ozf_object_fund_summary
    WHERE object_id = list_header_id
    AND object_type = 'OFFR';

Line: 3442

  SELECT SUM(uti.plan_curr_amount)
FROM ozf_funds_utilized_all_vl uti
WHERE uti.utilization_type IN
('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL')
AND plan_type = 'OFFR'
AND plan_id = p_list_header_id
AND gl_posted_flag NOT in('N','F');

Line: 3478

    SELECT SUM(NVL(plan_curr_paid_amt,0))
    FROM ozf_object_fund_summary
    WHERE object_id = list_header_id
    AND object_type = 'OFFR';

Line: 3485

 SELECT SUM(AMOUNT) FROM
(SELECT SUM(plan_curr_amount - NVL(plan_curr_amount_remaining,0)) amount
FROM ozf_funds_utilized_all_b util
WHERE utilization_type = 'UTILIZED'
AND NVL(util.gl_posted_flag,'Y') = 'Y'
AND plan_type = 'OFFR'
AND plan_id =  list_header_id
UNION AlL
SELECT cuti.plan_curr_amount  amount
FROM ozf_funds_utilized_all_b util,
     ozf_claim_lines_util_all cuti,
     ozf_claim_lines_all cln,
     ozf_claims_all cla
WHERE util.utilization_id(+) = cuti.utilization_id
AND util.utilization_type IN ('ACCRUAL','ADJUSTMENT', 'CHARGEBACK', 'LEAD_ACCRUAL')
AND cuti.claim_line_id = cln.claim_line_id
AND cln.claim_id = cla.claim_id
AND cla.status_code = 'CLOSED'
AND util.plan_type = 'OFFR'
AND util.plan_id =  list_header_id);

Line: 3526

  SELECT name
  FROM   hr_operating_units
  WHERE  organization_id = p_org_id;

Line: 3532

 SELECT name
 FROM hr_all_organization_units_tl
 WHERE organization_id = p_org_id
 AND language = userenv('LANG');

Line: 3564

      SELECT ak.attribute_label_long
      FROM ak_attributes_vl ak
      WHERE ak.attribute_code = upper(cp_attributeCode)
      AND   ak.attribute_application_id = cp_applicationId;