APPS.CSD_PROCESS_UTIL SQL Statements

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

Line: 80

    Select Nvl(ctt.no_charge_flag,'N')
    into l_no_chg_flag
    from cs_txn_billing_types ctbt,
	    cs_transaction_types ctt
    where ctbt.transaction_type_id = ctt.transaction_type_id
    and   ctbt.txn_billing_type_id = p_txn_billing_type_id;

Line: 112

   IF (p_action not in ('CREATE','UPDATE','DELETE')) THEN
    FND_MESSAGE.SET_NAME('CSD','CSD_API_INV_ACTION');

Line: 147

 select 'X'
 into l_dummy
 from cs_incidents_all_b
 where incident_id = p_incident_id;

Line: 175

 select 'X'
 into l_dummy
 from csd_repair_types_vl
 where repair_type_id = p_repair_type_id
 and   sysdate between nvl(start_date_active,sysdate)
 and nvl(end_date_active, sysdate);

Line: 201

 select 'X'
 into l_dummy
 from wip_discrete_jobs
 where wip_entity_id = p_wip_entity_id;

Line: 225

 select 'X'
 into l_dummy
 from csd_repair_order_groups
 where repair_group_id = p_repair_group_id;

Line: 249

 select 'x'
 into l_dummy
 from  bom_calendar_dates
 where calendar_date = p_date;

Line: 278

 select 'X'
 into l_dummy
 from  mtl_system_items
 where inventory_item_id = p_inventory_item_id
 and   organization_id   = l_org_id
 and   sysdate between nvl(start_date_active,sysdate)
 and nvl(end_date_active, sysdate);

Line: 306

 select 'X'
 into l_dummy
 from  mtl_units_of_measure_vl
 where uom_code = p_unit_of_measure
 and   sysdate between nvl(creation_date,sysdate)
 and nvl(disable_date, sysdate);

Line: 514

select
  incident_id
into x_incident_id
from csd_repairs
where repair_line_id = p_repair_line_id
 and  ((date_closed is null) OR (date_closed > sysdate));

Line: 589

     select tbt.txn_billing_type_id
     from cs_transaction_types_b tt,
          cs_txn_billing_types tbt,
          cs_billing_type_categories cbtc,
          cs_txn_billing_oetxn_all tb,
          oe_transaction_types_vl oeh,
          oe_transaction_types_vl oel
     where
         tt.transaction_type_id = l_transaction_type_id
     and nvl(tt.depot_repair_flag,'N')='Y'
     and tt.transaction_type_id=tbt.transaction_type_id
     and tbt.billing_type = l_billing_type
     and tbt.txn_billing_type_id=tb.txn_billing_type_id
	and  tb.org_id = l_operating_unit  /*Operating unit */
     and tb.order_type_id=oeh.transaction_type_id
     and tb.line_type_id=oel.transaction_type_id
     and sysdate between nvl(cbtc.start_date_active, sysdate) and nvl(cbtc.end_date_active,sysdate)
     and (sysdate) between nvl(tt.start_date_active,(sysdate)) and nvl(tt.end_date_active,(sysdate))
     and cbtc.billing_type = tbt.billing_type
     and nvl(cbtc.billing_category, '-999') ='M';

Line: 654

     select MATERIAL_BILLABLE_FLAG
     into   l_billing_type
     from  mtl_system_items_B
     where inventory_item_id = p_inventory_item_id
     and   organization_id   = l_org_id
     and   sysdate between nvl(start_date_active,sysdate)
     and nvl(end_date_active, sysdate);

Line: 678

     select tbt.transaction_type_id
     into   l_transaction_type_id
     from   cs_txn_billing_types tbt
     where txn_billing_type_id = p_txn_billing_type_id;

Line: 813

   select src_return_reqd
         ,non_src_return_reqd
   from csi_ib_txn_types a,
        cs_txn_billing_types b
   where a.cs_transaction_type_id = b.transaction_type_id
   and  b.txn_billing_type_id = p_txn_billing_type_id;

Line: 821

  SELECT
    crt.repair_type_ref,
    cr.auto_process_rma,
    crt.interface_to_om_flag,
    crt.book_sales_order_flag,
    cr.inventory_item_id,
    cr.item_revision,
    cr.contract_line_id,
    cr.unit_of_measure,
    crt.cps_txn_billing_type_id ,
    crt.cpr_txn_billing_type_id ,
    crt.ls_txn_billing_type_id  ,
    crt.lr_txn_billing_type_id  ,
    cr.price_list_header_id    ,
    crt.business_process_id,
    cr.incident_id,
    cr.default_po_num,   -- swai bug fix 4535829
    cr.inventory_org_id, -- inv_org_change Vijay, 3/20/06
    cr.project_id,
    cr.task_id,
    cr.unit_number,
    crt.third_rma_txn_billing_type_id,
    crt.third_ship_txn_billing_type_id,
    crt.third_party_flag
  FROM csd_repairs cr,
       csd_repair_types_vl crt
  where cr.repair_type_id = crt.repair_type_id
  and   cr.repair_line_id = p_rep_line_id;

Line: 852

  SELECT
    revision
  FROM mtl_item_revisions
  where inventory_item_id  = p_inv_item_id
  and  organization_id    = p_org_id;

Line: 861

  Select
    serial_number_control_code,
    comms_nl_trackable_flag
  from mtl_system_items_kfv
  where inventory_item_id = p_inv_item_id
  and organization_id     = p_org_id;

Line: 874

  select chr_id from okc_k_lines_b
  where id = p_contract_line_id;

Line: 880

    select distinct
           hp.party_site_id
      from hz_party_sites_v hp,
           hz_parties hz,
           hz_cust_acct_sites_all hca,
           hz_cust_site_uses_all hcsu
     where hcsu.site_use_code = p_site_use_type
      and  hp.status = 'A'
      and  hcsu.status = 'A'
      and  hp.party_id = hz.party_id
      and  hp.party_id = p_party_id
      and  hca.party_site_id = hp.party_site_id
      and  hca.cust_account_id = p_account_id
      and  hcsu.cust_acct_site_id = hca.cust_acct_site_id
      and  hca.org_id = p_org_id
      and  hcsu.primary_flag = 'Y'
      and rownum = 1;

Line: 917

  x_prod_txn_tbl.delete;

Line: 1379

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 1381

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 1383

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 1539

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 1541

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 1543

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 1746

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 1748

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 1750

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 1915

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 1917

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 1919

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 2056

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 2058

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 2060

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 2250

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 2252

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 2254

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 2386

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 2388

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 2390

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 2581

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 2583

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 2585

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 2723

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 2725

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 2727

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 2902

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 2904

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 2906

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 3031

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 3033

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 3035

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 3231

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 3233

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 3235

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 3305

            select party_id
            into x_prod_txn_tbl(l_index).bill_to_party_id
            from hz_cust_accounts
            where cust_account_id = l_default_val_num;

Line: 3423

        x_prod_txn_tbl(l_index).last_update_date            := sysdate          ;

Line: 3425

        x_prod_txn_tbl(l_index).last_updated_by             := FND_GLOBAL.USER_ID;

Line: 3427

        x_prod_txn_tbl(l_index).last_update_login           := FND_GLOBAL.USER_ID;

Line: 3516

            select party_id
            into x_prod_txn_tbl(l_index).bill_to_party_id
            from hz_cust_accounts
            where cust_account_id = l_default_val_num;

Line: 3567

  SELECT
    quantity,
    customer_product_id,
    serial_number
  FROM csd_repairs
  where repair_line_id = p_rep_line_id;

Line: 3599

 select 'X'
 into l_dummy
 from csd_repairs
 where repair_line_id = p_repair_line_id;

Line: 3626

 select 'X'
 into l_dummy
 from csd_repair_estimate
 where repair_estimate_id = p_estimate_id;

Line: 3651

 select 'X'
 into l_dummy
 from csd_repair_estimate_lines
 where repair_estimate_line_id = p_estimate_line_id;

Line: 3672

 select 'X'
 into l_dummy
 from  fnd_lookups
 where lookup_type = 'CSD_PROD_ACTION_TYPE'
  and  lookup_code = p_action_type;

Line: 3693

 select 'X'
 into l_dummy
 from  fnd_lookups
 where lookup_type = 'CSD_PRODUCT_ACTION_CODE'
  and  lookup_code = p_action_code;

Line: 3729

 select b.org_id
 into l_org_id
 from csd_repairs a,
      cs_incidents_all_b b
 where a.incident_id    = b.incident_id
  and  a.repair_line_id = p_repair_line_id;

Line: 3811

Select
     sum(ced.after_warranty_cost)
into l_amount
from csd_repair_estimate cre,
	csd_repair_estimate_lines crel,
	cs_estimate_details ced,
	cs_txn_billing_types ctbt
where cre.repair_estimate_id = crel.repair_estimate_id
  and crel.estimate_detail_id = ced.estimate_detail_id
  and ced.txn_billing_type_id = ctbt.txn_billing_type_id
  and cre.repair_line_id     = p_repair_line_id
  and ctbt.billing_type      = 'M';

Line: 3826

Select
     sum(ced.after_warranty_cost)
into l_amount
from csd_repair_estimate cre,
	csd_repair_estimate_lines crel,
	cs_estimate_details ced,
	cs_txn_billing_types ctbt
where cre.repair_estimate_id = crel.repair_estimate_id
  and crel.estimate_detail_id = ced.estimate_detail_id
  and ced.txn_billing_type_id = ctbt.txn_billing_type_id
  and cre.repair_line_id     = p_repair_line_id
  and ctbt.billing_type      = 'L';

Line: 3841

Select
     sum(ced.after_warranty_cost)
into l_amount
from csd_repair_estimate cre,
	csd_repair_estimate_lines crel,
	cs_estimate_details ced,
	cs_txn_billing_types ctbt
where cre.repair_estimate_id = crel.repair_estimate_id
  and crel.estimate_detail_id = ced.estimate_detail_id
  and ced.txn_billing_type_id = ctbt.txn_billing_type_id
  and cre.repair_line_id     = p_repair_line_id
  and ctbt.billing_type      = 'E';

Line: 3856

Select
     sum(ced.after_warranty_cost)
into l_amount
from csd_repair_estimate cre,
	csd_repair_estimate_lines crel,
	cs_estimate_details ced
where cre.repair_estimate_id = crel.repair_estimate_id
  and crel.estimate_detail_id = ced.estimate_detail_id
  and cre.repair_line_id     = p_repair_line_id ;

Line: 3886

/* select b.business_process_id
 into l_bus_process_id
 from cs_incidents_all_b a,
      cs_incident_types_b b
 where a.incident_type_id = b.incident_type_id
  and  a.incident_id      = p_incident_id;

Line: 3894

 select t.business_process_id
 into l_bus_process_id
 from csd_repairs r,
      csd_repair_types_b t
 where r.repair_line_id = p_repair_line_id
 and   r.repair_type_id = t.repair_type_id;

Line: 3924

  select SRC_RETURN_REQD
        ,NON_SRC_RETURN_REQD
  from csi_ib_txn_types a,
       cs_txn_billing_types b
  where a.cs_transaction_type_id = b.transaction_type_id
  and  b.txn_billing_type_id = p_txn_billing_type_id;

Line: 4057

select b.line_type_id,
       c.line_order_category_code
 into  x_line_type_id,
       x_line_category_code
from cs_txn_billing_types a,
     CS_TXN_BILLING_OETXN_ALL b,
     cs_transaction_types_vl c
where a.txn_billing_type_id = b.txn_billing_type_id
 and  a.transaction_type_id = c.transaction_type_id
 and  a.txn_billing_type_id = p_txn_billing_type_id
 and  b.org_id              = p_org_id;

Line: 4093

  Select count(*)
  into l_rejected_quantity
  from csd_repairs
  where upper(approval_status) = 'R'
  and repair_group_id = p_repair_group_id;

Line: 4116

 select 'X'
 into l_dummy
 from csd_product_transactions
 where product_transaction_id = p_prod_txn_id;

Line: 4143

SELECT
  a.repair_mode,
  a.repair_type_id,
  b.repair_line_id,
  c.quantity_required
FROM csd_repairs a,
     csd_product_transactions b,
     cs_estimate_details c
where a.repair_line_id = b.repair_line_id
 and  b.estimate_detail_id = c.estimate_detail_id
 and  b.product_transaction_id = p_prod_txn_id;

Line: 4173

	SELECT a.repair_mode,
	       a.repair_type_id,
  		   b.repair_line_id,
           c.quantity_required
	INTO l_repair_mode,
	     l_repair_type_id,
		 l_repair_line_id,
		 l_prod_txn_qty
	FROM csd_repairs a,
     csd_product_transactions b,
     cs_estimate_details c
    where a.repair_line_id = b.repair_line_id
       and  b.estimate_detail_id = c.estimate_detail_id
       and  b.product_transaction_id = p_prod_txn_id ;

Line: 4217

     select sum(quantity_completed)
      into  l_qty_completed
      from csd_repair_job_xref
     where repair_line_id = l_repair_line_id;

Line: 4253

      Select count(*)
       into l_count
      from jtf_tasks_vl
      where source_object_type_code = 'DR'
       and  source_object_id = l_repair_line_id
       and  task_status_id not in (7,8,9,11);

Line: 4295

SELECT
  abs(sum(b.quantity_required))
FROM csd_product_transactions a,
     cs_estimate_details b
where a.estimate_detail_id = b.estimate_detail_id
 and  a.action_code    = 'CUST_PROD'
 and a.prod_txn_status <> 'CANCELLED'
 and  a.action_type    = p_action_type
 and  a.repair_line_id = p_repair_line_id;

Line: 4306

SELECT
      quantity
FROM  csd_repairs
WHERE repair_line_id = p_repair_line_id;

Line: 4375

 select serial_number_control_code
 into l_serial_code
 from mtl_system_items
 where inventory_item_id = p_inv_item_id
 and   organization_id   = cs_std.get_item_valdn_orgzn_id;

Line: 4660

         SELECT select_id, select_name, from_table, where_clause
           FROM jtf_objects_b
          WHERE object_code = l_object_type_code;

Line: 4664

      l_id_column      jtf_objects_b.select_id%TYPE;

Line: 4665

      l_name_column    jtf_objects_b.select_name%TYPE;

Line: 4683

		select substr(p_object_type_code,1,3)
            into l_code
		  from sys.dual;

Line: 4718

      SELECT DECODE (l_where_clause, NULL, '  ', l_where_clause || ' AND
')
        INTO
             l_where_clause
        FROM dual;

Line: 4723

      sql_stmt := ' SELECT ' ||
                  l_name_column ||
                  ' from ' ||
                  l_from_clause ||
                  '  where ' ||
                  l_where_clause ||
                  l_id_column ||
                  ' = :object_id ';

Line: 4805

        SELECT price_list_header_id
          FROM csd_repair_types_b
         WHERE repair_type_id = p_repair_type_id;

Line: 4882

     SELECT business_process_id
     INTO l_bus_process_id
     FROM csd_repair_types_b
     WHERE repair_type_id = p_repair_type_id;

Line: 4917

    SELECT contract_service_id
    INTO l_sr_contract_line_id
    FROM cs_incidents
    WHERE incident_id = p_incident_id;

Line: 5656

  Select multi_org_flag
  into l_multiorg_enabled
  from FND_PRODUCT_GROUPS;

Line: 5694

	  SELECT gl.currency_code
	  INTO l_currency_code
	  FROM gl_sets_of_books gl, hr_operating_units hr
	  WHERE hr.set_of_books_id = gl.set_of_books_id
	  AND hr.organization_id= p_org_id;

Line: 5730

  SELECT currency_code
  INTO l_pl_curr_code
  FROM qp_list_headers_b
  WHERE list_header_id = p_price_list_id;

Line: 5772

          Select max(ced.order_header_id)
          into  l_add_to_order_id
          from  csd_repairs dr,
                cs_estimate_details ced,
                oe_order_headers_all ooh,
                oe_order_types_v oot,
                cs_incidents_all_b sr               -- swai: bug 5931926
          where dr.repair_line_id = p_repair_line_id
          and  ced.incident_id = dr.incident_id
          and  ced.order_header_id is not null
          and  ooh.open_flag = 'Y'
          and  nvl(ooh.cancelled_flag,'N') = 'N'
          and  ooh.header_id = ced.order_header_id
          and  ooh.transactional_curr_code = dr.currency_code
          and  (ooh.cust_po_number = nvl(dr.default_po_num,ooh.cust_po_number)
               or ooh.cust_po_number is null)
          and  oot.order_type_id = ooh.order_type_id
          and  oot.order_category_code in ('MIXED','RETURN')
          and  ced.interface_to_oe_flag = 'Y'
          and  ooh.sold_to_org_id = sr.account_id  -- swai: bug 5931926
          and  sr.incident_id = dr.incident_id;    -- swai: bug 5931926

Line: 5807

          Select max(ced.order_header_id)
          into  l_add_to_order_id
          from  csd_repairs dr,
                cs_estimate_details ced,
                oe_order_headers_all ooh,
                oe_order_types_v oot,
                cs_incidents_all_b sr               -- swai: bug 5931926
          where dr.repair_line_id = p_repair_line_id
          and  ced.incident_id = dr.incident_id
          and  ced.order_header_id is not null
          and  ooh.open_flag = 'Y'
          and  nvl(ooh.cancelled_flag,'N') = 'N'
          and  ooh.header_id = ced.order_header_id
          and  ooh.transactional_curr_code = dr.currency_code
          and  (ooh.cust_po_number = nvl(dr.default_po_num,ooh.cust_po_number)
               or ooh.cust_po_number is null)
          and  oot.order_type_id = ooh.order_type_id
          and  oot.order_category_code in ('MIXED','ORDER')
          and  ced.interface_to_oe_flag = 'Y'
          and  ooh.sold_to_org_id = sr.account_id  -- swai: bug 5931926
          and  sr.incident_id = dr.incident_id;    -- swai: bug 5931926