APPS.OE_ORDER_IMPORT_WF SQL Statements

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

Line: 4

PROCEDURE OEOI_SELECTOR
( p_itemtype   in     varchar2,
  p_itemkey    in     varchar2,
  p_actid      in     number,
  p_funcmode   in     varchar2,
  p_x_result   in out NOCOPY /* file.sql.39 change */ varchar2
)
IS
  l_user_id             NUMBER;

Line: 26

      oe_debug_pub.add(  'ENTERING OEOI_SELECTOR PROCEDURE' ) ;

Line: 61

  /* Delete Following before check
    l_org_id := wf_engine.GetItemAttrNumber
                          (itemtype => p_itemtype,
                           itemkey  => p_itemkey,
                           aname    => 'ORG_ID');

Line: 68

        Select  org_id
        Into    l_org_id
        From    oe_headers_interface
        Where   orig_sys_document_ref = p_itemkey
        And     order_source_id       = '20';

Line: 117

   WF_CORE.Context('OE_ORDER_IMPORT_WF', 'OEOI_SELECTOR',
                    p_itemtype, p_itemkey, p_actid, p_funcmode);

Line: 121

END OEOI_SELECTOR;

Line: 123

PROCEDURE OESO_SELECTOR
( p_itemtype   in     varchar2,
  p_itemkey    in     varchar2,
  p_actid      in     number,
  p_funcmode   in     varchar2,
  p_x_result   in out NOCOPY /* file.sql.39 change */ varchar2
)
IS
  l_user_id             NUMBER;

Line: 145

      oe_debug_pub.add(  'ENTERING OESO_SELECTOR PROCEDURE' ) ;

Line: 202

   WF_CORE.Context('OE_ORDER_SHOW_SO', 'OESO_SELECTOR',
                    p_itemtype, p_itemkey, p_actid, p_funcmode);

Line: 206

END OESO_SELECTOR;

Line: 209

PROCEDURE OEOA_SELECTOR
( p_itemtype   in     varchar2,
  p_itemkey    in     varchar2,
  p_actid      in     number,
  p_funcmode   in     varchar2,
  p_x_result   in out NOCOPY /* file.sql.39 change */ varchar2
)
IS
  l_user_id             NUMBER;

Line: 231

      oe_debug_pub.add(  'ENTERING OEOA_SELECTOR PROCEDURE' ) ;

Line: 303

   WF_CORE.Context('OE_ORDER_IMPORT_WF', 'OEOA_SELECTOR',
                    p_itemtype, p_itemkey, p_actid, p_funcmode);

Line: 307

END OEOA_SELECTOR;

Line: 320

  Update WF_ITEMS
  Set    END_DATE = sysdate - .01
  Where  ITEM_TYPE = p_itemtype
  And    ITEM_KEY  = p_itemkey;

Line: 513

    Select request_id
    Into   l_request_id
    From   fnd_concurrent_requests
    Where  parent_request_id = l_request_id;

Line: 574

    Select  request_id
    Into    l_request_id
    From    oe_headers_interface
    Where   order_source_id       =  20
    And     orig_sys_document_ref = l_orig_sys_document_ref
    And     request_id            = nvl(l_request_id, request_id)
    And     decode(l_customer_key_profile, 'Y',
	    nvl(sold_to_org_id,                  -999), 1)
            = decode(l_customer_key_profile, 'Y',
	    nvl(l_sold_to_org_id,                -999), 1)
    And nvl(  change_sequence,		' ')
      = nvl(l_change_sequence,		' ')
    And     xml_transaction_type_code = l_xml_transaction_type_code
    And     error_flag            = 'Y';

Line: 882

     OE_MSG_PUB.update_msg_context(
        p_header_id            => l_header_id
     );

Line: 894

     Select sold_to_org_id, ship_to_org_id
     into   l_party_id, l_party_site_id
     from   oe_headers_interface
     where  orig_sys_document_ref = l_orig_sys_document_ref
     And    xml_transaction_type_code = l_xml_transaction_type_code
     And    request_id            = l_request_id
     And    order_source_id       = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;

Line: 908

     Select header_id, sold_to_org_id
     Into   l_header_id, l_party_id
     From   oe_order_headers
     Where  header_id = l_header_id
     And    order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;

Line: 930

          oe_debug_pub.add(  'SELECT OF THE DATA FAILED FOR THE DOCUMENT' ) ;

Line: 949

      oe_debug_pub.add(  'BEFORE SELECT FOR THE ACCOUNT SITE AND PARTY ID' ) ;

Line: 952

  SELECT /* MOAC_SQL_CHANGE */ a.cust_acct_site_id, a.party_site_id, c.party_id
  Into   l_cust_acct_site_id, l_party_site_id,  l_party_id
  From   hz_cust_acct_sites_all a, hz_cust_site_uses_all b, hz_cust_accounts c
  Where  a.cust_acct_site_id = b.cust_acct_site_id
  And    a.cust_account_id   = l_party_id
  And    a.cust_account_id   = c.cust_account_id
/*  And     NVL(a.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),
           1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
           NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),
           ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) */
  And    a.org_id = l_org_id
  And    b.site_use_code = 'SOLD_TO'
  And    b.primary_flag = 'Y'
  And    b.status = 'A'
  And    a.status ='A';  --bug 2752321

Line: 969

      oe_debug_pub.add(  'AFTER SELECT FOR THE ACCOUNT SITE AND PARTY ID AND PARTY_SITE_ID' || L_CUST_ACCT_SITE_ID || ' & ' || L_PARTY_ID || ' & ' || L_PARTY_SITE_ID ) ;

Line: 1151

  insert into alok values('222');

Line: 1152

  insert into alok values(fnd_profile.value('CONC_REQUEST_ID'));

Line: 1236

       Select header_id, xml_message_id, sold_to_org_id, ship_to_org_id
       Into   l_header_id, l_document_id, l_party_id, l_party_site_id
       From   oe_order_headers
       Where  order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID
       And    orig_sys_document_ref = l_orig_sys_document_ref
       And decode(l_customer_key_profile, 'Y',
	   nvl(sold_to_org_id,                  -999), 1)
         = decode(l_customer_key_profile, 'Y',
	   nvl(l_sold_to_org_id,                -999), 1);

Line: 1248

       Select xml_message_id, sold_to_org_id, ship_to_org_id
       into   l_document_id, l_party_id, l_party_site_id
       from   oe_headers_interface
       where  order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID
       And    orig_sys_document_ref = l_orig_sys_document_ref
       And    decode(l_customer_key_profile, 'Y',
	      nvl(sold_to_org_id,                  -999), 1)
              = decode(l_customer_key_profile, 'Y',
              nvl(l_sold_to_org_id,                -999), 1)
       And    nvl(change_sequence,                    ' ')
              = nvl(l_change_sequence,               ' ')
       And    xml_transaction_type_code = l_xml_transaction_type_code
       And    request_id = l_request_id;

Line: 1265

    Select header_id, xml_message_id, sold_to_org_id, ship_to_org_id
    Into   l_header_id, l_document_id, l_party_id, l_party_site_id
    From   oe_order_headers
    Where  header_id = p_itemkey
    And    order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;

Line: 1274

     OE_MSG_PUB.update_msg_context(
        p_header_id            => l_header_id
        );

Line: 1629

  SELECT  wias.ACTIVITY_STATUS, wias.ACTIVITY_RESULT_CODE, wias.PROCESS_ACTIVITY
  INTO    x_activity_status_code, x_activity_result, x_activity_id
  FROM    WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
  WHERE   wias.ITEM_KEY         = p_itemkey
  AND     wias.ITEM_TYPE        = p_itemtype
  AND     wpa.ACTIVITY_NAME     = p_activity_name
  AND     wias.PROCESS_ACTIVITY = wpa.INSTANCE_ID;

Line: 1685

    SELECT order_source_id, orig_sys_document_ref, sold_to_org_id, change_sequence, order_number, order_type_id, org_id
      INTO l_order_source_id, l_orig_sys_document_ref, l_sold_to_org_id, l_change_sequence, l_order_number, l_order_type_id, l_org_id
      FROM oe_order_headers
     WHERE header_id = p_itemkey;

Line: 1856

         select orig_sys_document_ref, sold_to_org_id, change_sequence
         into   l_orig_sys_document_ref, l_customer_number, l_change_sequence
         from oe_order_headers
         where header_id = l_header_id;

Line: 2025

    select confirmation
    into l_confirmation
    from ecx_oag_cbod_v
    where document_id = l_message_id;

Line: 2132

  select oe_xml_message_seq_s.nextval
  into l_event_key_num
  from dual;

Line: 2290

      OE_MSG_PUB.update_msg_context(
        p_header_id            => l_header_id
        );

Line: 2300

         Select orig_sys_document_ref
         Into l_orig_sys_document_ref
         From oe_order_headers
         Where header_id = l_header_id;

Line: 2305

      OE_MSG_PUB.update_msg_context(
        p_orig_sys_document_ref            => l_orig_sys_document_ref
        );

Line: 2353

      OE_MSG_PUB.update_msg_context(
        p_orig_sys_document_ref           => l_orig_sys_document_ref
        );

Line: 2388

    OE_MSG_PUB.update_msg_context(
       p_orig_sys_document_ref           => l_orig_sys_document_ref
        );

Line: 2421

      OE_MSG_PUB.update_msg_context(
        p_change_sequence           => l_change_sequence
        );

Line: 2441

       Select order_number, order_type_id, header_id
       Into l_document_num, l_order_type_id, l_header_id
       From oe_order_headers
       Where orig_sys_document_ref = l_orig_sys_document_ref
       And decode(l_customer_key_profile, 'Y',
	   nvl(sold_to_org_id,                  -999), 1)
         = decode(l_customer_key_profile, 'Y',
	   nvl(l_sold_to_org_id,                -999), 1)
       And order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;

Line: 2453

      OE_MSG_PUB.update_msg_context(
        p_header_id            => l_header_id
        );

Line: 2470

              oe_debug_pub.add(  'EXCEPTION IN RAISE_EVENT_XMLINT...SELECTING DOCUMENT NUMBER FOR ' || P_ITEMTYPE ) ;

Line: 2500

       Select order_number, order_type_id, header_id
       Into l_document_num, l_order_type_id, l_header_id
       From oe_order_headers
       Where orig_sys_document_ref = l_orig_sys_document_ref
       And decode(l_customer_key_profile, 'Y',
	   nvl(sold_to_org_id,                  -999), 1)
         = decode(l_customer_key_profile, 'Y',
           nvl(l_sold_to_org_id,                -999), 1)
       And order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;

Line: 2512

           OE_MSG_PUB.update_msg_context(
              p_header_id            => l_header_id
           );

Line: 2532

             oe_debug_pub.add(  'EXCEPTION IN RAISE_EVENT_XMLINT...SELECTING DOCUMENT NUMBER' ) ;

Line: 2618

         SELECT standard_desc
         INTO l_standard_desc
         FROM ecx_standards_vl
         WHERE standard_code = 'ROSETTANET';

Line: 2816

  SELECT standard_code
  INTO l_standard_code
  FROM ecx_tp_details_v
  WHERE tp_header_id = (SELECT tp_header_id FROM ecx_tp_headers
                        WHERE party_id = l_party_id
                        AND party_site_id = l_party_site_id
                        AND party_type = 'C')
  AND transaction_type ='ONT'
  AND transaction_subtype = 'POA';