APPS.RLM_EXTINTERFACE_SV SQL Statements

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

Line: 30

  g_oe_line_tbl.delete;

Line: 104

SELECT  acct_site.ece_tp_location_code
FROM    hz_cust_acct_sites acct_site ,
        hz_cust_site_uses_all cust_site
WHERE   cust_site.site_use_id = P_SHIP_TO_ORG_ID
AND     cust_site.site_use_code = 'SHIP_TO'
AND     cust_site.cust_acct_site_id = acct_site.cust_acct_site_id;

Line: 129

        rlm_core_sv.dlog(k_DEBUG,'starting insert oe lines');

Line: 166

     SELECT hsecs INTO l_start_time from v$timer;

Line: 169

     l_line_tbl.delete;

Line: 336

     SELECT hsecs INTO l_end_time from v$timer;

Line: 375

     InsertOMMessages(x_header_id,
                      x_Op_tab(1).customer_item_id,
                      x_msg_count,rlm_message_sv.k_warn_level,
                      x_token,
                      x_msg_name);

Line: 396

       InsertOMMessages(x_header_id,
                        x_Op_tab(1).customer_item_id,
                        x_msg_count,rlm_message_sv.k_error_level,
                        x_token,
                        x_msg_name);

Line: 412

             UPDATE rlm_schedule_lines_all
	     SET process_status = rlm_core_sv.k_PS_ERROR
             WHERE line_id = l_oe_line_tbl_out(s).source_document_line_id;

Line: 418

                UPDATE rlm_interface_lines_all
	        SET process_status = rlm_core_sv.k_PS_ERROR
                WHERE line_id =
                 (
                  SELECT interface_line_id
                  FROM rlm_schedule_lines_all
                  WHERE line_id = l_oe_line_tbl_out(s).source_document_line_id
                 );

Line: 428

                   rlm_core_sv.dlog(k_DEBUG, 'No of Interface Lines updated', SQL%ROWCOUNT);

Line: 444

             UPDATE rlm_schedule_lines_all
	     SET process_status = rlm_core_sv.k_PS_PROCESSED
             WHERE line_id = l_oe_line_tbl_out(s).source_document_line_id;

Line: 450

                UPDATE rlm_interface_lines_all
	        SET process_status = rlm_core_sv.k_PS_PROCESSED
                WHERE line_id =
                 (
                  SELECT interface_line_id
                  FROM rlm_schedule_lines_all
                  WHERE line_id = l_oe_line_tbl_out(s).source_document_line_id
                 );

Line: 460

                   rlm_core_sv.dlog(k_DEBUG, 'No of Interface Lines updated', SQL%ROWCOUNT);

Line: 545

          PROCEDURE    InsertOMMessages

===========================================================================*/

PROCEDURE InsertOMMessages(x_header_id IN NUMBER,
                           x_customer_item_id IN NUMBER,
                           x_msg_count  IN NUMBER,
                           x_msg_level  IN VARCHAR2,
                           x_token IN VARCHAR2,
                           x_msg_name IN VARCHAR2)
IS
  --
  x_msg                          VARCHAR2(4000);

Line: 589

     rlm_core_sv.dpush(k_SDEBUG,'InsertOMMessages');

Line: 624

      SELECT ordered_item
      INTO   v_item_name
      FROM   oe_order_lines_all
      WHERE  line_id = oe_msg_pub.g_msg_tbl(i).line_id;

Line: 687

                 SELECT interface_line_id, header_id, order_header_id, industry_attribute2
                 INTO v_interface_line_id, v_schedule_header_id, v_order_header_id, v_request_date
                 FROM rlm_schedule_lines
                 WHERE line_id = l_source_document_line_id;

Line: 766

      rlm_core_sv.dlog(k_DEBUG, 'Inserting RLM_PROCESS_ORDER_ERROR_E msg');

Line: 799

END InsertOMMessages;

Line: 975

       InsertOMMessages(x_Key_rec.req_rec.header_id,x_Key_rec.req_rec.customer_item_id,x_msg_count,
                        x_msg_level, x_token, x_msg_name);

Line: 1009

       InsertOMMessages(x_Key_rec.req_rec.header_id,x_Key_rec.req_rec.customer_item_id,x_msg_count,
                        x_msg_level,x_token, x_msg_name);

Line: 1534

      AND x_Op_rec.operation =  OE_GLOBALS.G_OPR_UPDATE) THEN
    --
    x_oe_line_rec.operation         := x_Op_rec.operation;

Line: 1550

    IF  x_Op_rec.operation =  OE_GLOBALS.G_OPR_UPDATE THEN
       --
       x_oe_line_rec.change_reason     := 'EDI CANCELLATION';

Line: 1658

    x_oe_line_rec.last_updated_by   := FND_GLOBAL.USER_ID;

Line: 1659

    x_oe_line_rec.last_update_date  := SYSDATE;

Line: 1660

    x_oe_line_rec.last_update_login := FND_GLOBAL.LOGIN_ID;

Line: 1810

    x_oe_line_rec.program_update_date    := SYSDATE;

Line: 1865

       rlm_core_sv.dlog(k_DEBUG,'last_updated_by',x_oe_line_rec.last_updated_by );  --Bugfix 9223338

Line: 1866

       rlm_core_sv.dlog(k_DEBUG,'last_update_date',x_oe_line_rec.last_update_date); --Bugfix 9223338

Line: 1998

   SELECT flow_status_code
   INTO v_status_code
   FROM oe_order_lines_all
   where line_id = x_OrderLineId;

Line: 2008

      SELECT a.meaning
      INTO v_line_status
      FROM oe_lookups a, oe_order_lines_all b
      WHERE a.lookup_type like 'LINE_FLOW_STATUS'
      AND a.lookup_code = b.flow_status_code
      AND b.line_id = x_OrderLineId;

Line: 2017

      SELECT sum(decode(released_status, 'Y', 1, 0)), sum(1)
      INTO v_released_count, v_total_count
      FROM wsh_delivery_details
      WHERE source_line_id   = x_OrderLineId
      AND   source_code      = 'OE'
      AND   released_status  <> 'D';

Line: 2026

         SELECT meaning
         INTO v_line_status
         FROM fnd_lookup_values lv
         WHERE lookup_type = 'LINE_FLOW_STATUS'
         AND lookup_code = 'PICKED'
         AND LANGUAGE = userenv('LANG')
         AND VIEW_APPLICATION_ID = 660
         AND SECURITY_GROUP_ID =
         fnd_global.Lookup_Security_Group(lv.lookup_type,
                                      lv.view_application_id);

Line: 2039

         SELECT meaning
         INTO v_line_status
         FROM fnd_lookup_values lv
         WHERE lookup_type = 'LINE_FLOW_STATUS'
         AND lookup_code = 'PICKED_PARTIAL'
         AND LANGUAGE = userenv('LANG')
         AND VIEW_APPLICATION_ID = 660
         AND SECURITY_GROUP_ID =
                fnd_global.Lookup_Security_Group(lv.lookup_type,
                                                 lv.view_application_id);

Line: 2052

         SELECT meaning
         INTO v_line_status
         FROM fnd_lookup_values lv
         WHERE lookup_type = 'LINE_FLOW_STATUS'
         AND lookup_code = v_status_code
         AND LANGUAGE = userenv('LANG')
         AND VIEW_APPLICATION_ID = 660
         AND SECURITY_GROUP_ID =
                fnd_global.Lookup_Security_Group(lv.lookup_type,
                                                 lv.view_application_id);

Line: 2097

    select	location
    into	v_location
    from	HZ_CUST_SITE_USES_ALL
    where	site_use_code = 'SHIP_TO'
    and		site_use_id = x_OrgId;

Line: 2130

	select	loc.address1
	into	v_address1
	from	HZ_CUST_SITE_USES_ALL cust_site,
		HZ_PARTY_SITES PARTY_SITE,
		HZ_LOCATIONS LOC,
		HZ_CUST_ACCT_SITES_ALL		 ACCT_SITE
	where	cust_site.site_use_code = 'SHIP_TO'
	and	cust_site.site_use_id = x_OrgId
	and	cust_site.CUST_ACCT_SITE_ID = acct_site.CUST_ACCT_SITE_ID
	AND	ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
	AND	LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID;

Line: 2169

	SELECT	ETG.tp_group_code
	FROM	ece_tp_headers ETH,
		ece_tp_group ETG,
		HZ_CUST_SITE_USES_ALL cust_site,
		HZ_CUST_ACCT_SITES ACCT_SITE
	WHERE	ACCT_SITE.CUST_ACCOUNT_ID = x_Op_rec.customer_id
	AND	cust_site.site_use_id = x_Op_rec.ship_to_org_id
	and	cust_site.CUST_ACCT_SITE_ID = acct_site.CUST_ACCT_SITE_ID
	AND	ETH.tp_header_id = ACCT_SITE.tp_header_id
	AND	ETG.tp_group_id = ETH.tp_group_id
	AND	cust_site.site_use_code = 'SHIP_TO';

Line: 2197

	SELECT	ACCT_SITE.ece_tp_location_code
	INTO	x_ship_to_ece_locn_code
	FROM	HZ_CUST_ACCT_SITES ACCT_SITE ,
		HZ_CUST_SITE_USES_ALL CUST_SITE
	WHERE	cust_site.site_use_id = x_Op_rec.ship_to_org_id
	AND	cust_site.site_use_code = 'SHIP_TO'
	AND   	CUST_SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID;

Line: 2213

	SELECT	ACCT_SITE.ece_tp_location_code
	INTO	x_bill_to_ece_locn_code
	FROM	HZ_CUST_ACCT_SITES ACCT_SITE ,
		HZ_CUST_SITE_USES_ALL CUST_SITE
	WHERE	cust_site.site_use_id = x_Op_rec.invoice_to_org_id
	AND	cust_site.site_use_code = 'BILL_TO'
	AND   	CUST_SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID;

Line: 2239

	SELECT	ACCT_SITE.ece_tp_location_code
	INTO	x_inter_ship_to_ece_locn_code
	FROM	HZ_CUST_ACCT_SITES ACCT_SITE ,
		HZ_CUST_SITE_USES_ALL CUST_SITE
	WHERE	cust_site.site_use_id = x_Op_rec.intmed_ship_to_org_id
	AND	cust_site.site_use_code = 'SHIP_TO'
	AND   	CUST_SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID;

Line: 2266

	 SELECT account_number
	 INTO   x_customer_number
	 FROM   HZ_CUST_ACCOUNTS CUST_ACCT
	 WHERE 	CUST_ACCT.CUST_ACCOUNT_ID = x_Op_rec.Customer_Id;

Line: 2333

  v_select_clause     VARCHAR2(32000);

Line: 2355

  v_select_clause :=  'SELECT SUM(NVL(o.shipped_quantity,0))
                       FROM oe_order_lines o';

Line: 2362

      SELECT effective_start_date, effective_end_date
      INTO l_effective_start_date, l_effective_end_date
      FROM rlm_blanket_rso
      WHERE blanket_number = x_Group_rec.blanket_number
      AND rso_hdr_id = x_Group_rec.order_header_id;

Line: 2375

	       	           ' (SELECT rbr.rso_hdr_id FROM rlm_blanket_rso rbr,oe_order_headers oh WHERE rbr.blanket_number = '||x_Group_rec.blanket_number||
                       '  AND oh.header_id = rbr.rso_hdr_id AND oh.open_flag = '||'''Y'''||')';

Line: 3245

  v_final_sql := v_select_clause||v_where_clause;

Line: 3263

  g_wheretab.delete;