APPS.OE_CREDIT_WF SQL Statements

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

Line: 8

   the order is not being updated.
2) Decides on the credit check rule to use.
3) Calls the mainline procedure in the credit check utility.
----------------------------------------------------------------------------*/

procedure OE_CHECK_AVAILABLE_CREDIT(
    itemtype  in varchar2,
    itemkey   in varchar2,
    actid     in number,
    funcmode  in varchar2,
    resultout in out nocopy varchar2)
is
l_return_status 	VARCHAR2(30);	-- checks the return status of the called procedures

Line: 190

   	     SELECT count(*)
             INTO l_hold_count
             FROM OE_ORDER_HOLDS H, OE_HOLD_SOURCES S
             WHERE H.HEADER_ID = l_header_id
           --  AND H.LINE_ID IS NULL
             AND H.HOLD_RELEASE_ID IS NULL
             AND H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
             AND S.HOLD_ID = 1
             AND S.HOLD_ENTITY_CODE = 'O'
             AND S.HOLD_ENTITY_ID = l_header_id
             AND S.RELEASED_FLAG = 'N';

Line: 350

    SELECT 	ORDER_NUMBER
    INTO	l_order_number
    FROM	OE_ORDER_HEADERS
    WHERE	header_id = l_header_id;

Line: 434

        SELECT wfas.item_type, wfas.item_key, wpa.activity_name, wfas.activity_status
  	FROM wf_item_activity_statuses wfas, wf_process_activities wpa
  	WHERE wpa.activity_name IN ('OE_CREDIT_HOLD_NTF','OE_HOLD_BLOCK','WAIT_FOR_NTF_RESULT')
              AND wfas.process_activity = wpa.instance_id
  		AND wfas.activity_status = 'NOTIFIED'
 	         AND wfas.item_type = 'OEOH'
                 AND wpa.activity_item_type = 'OEOH'
                 and wfas.item_key = to_char(l_hold_entity_id)
         UNION
        SELECT wfas.item_type, wfas.item_key, wpa.activity_name, wfas.activity_status
  	FROM wf_item_activity_statuses wfas, wf_process_activities wpa
  	WHERE wpa.activity_name IN ('OE_CREDIT_HOLD_NTF','OE_HOLD_BLOCK','WAIT_FOR_NTF_RESULT')
              AND wfas.process_activity = wpa.instance_id
  		AND wfas.activity_status = 'NOTIFIED'
                 AND wpa.activity_item_type = 'OEOL'
 	         AND  wfas.item_type = 'OEOL' and wfas.item_key
                          in (select line_id
                              from oe_order_lines_all L
                              where L.header_id = l_hold_entity_id);

Line: 485

             SELECT count(*)
             INTO l_hold_count
             FROM OE_ORDER_HOLDS H, OE_HOLD_SOURCES S
             WHERE H.HEADER_ID = l_hold_entity_id
           --  AND H.LINE_ID IS NULL
             AND H.HOLD_RELEASE_ID IS NULL
             AND H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
             AND S.HOLD_ID = 1
             AND S.HOLD_ENTITY_CODE = 'O'
             AND S.HOLD_ENTITY_ID = l_hold_entity_id
             AND S.RELEASED_FLAG = 'N';

Line: 624

	When the order is being updated and the credit has to be
	re-evaluated, this process suspends the previous credit hold
	notifications (if any).
---------------------------------------------------------------------*/
procedure OE_WAIT_HOLD_NTF(
    itemtype  in varchar2,
    itemkey   in varchar2,
    actid     in number,
    funcmode  in varchar2,
    resultout in out nocopy varchar2)
is
l_header_id              NUMBER;

Line: 639

        SELECT wfas.item_type, wfas.item_key
  	FROM   wf_item_activity_statuses wfas, wf_process_activities wpa
  	WHERE  wfas.item_type='OEOH'
               AND wfas.process_activity = wpa.instance_id
               AND wpa.activity_item_type='OEOH'
               AND  wpa.activity_name = 'OE_CREDIT_HOLD_NTF'
  		AND wfas.activity_status = 'NOTIFIED'
 	        AND wfas.item_key = to_char(l_header_id)
        UNION
         SELECT wfas.item_type, wfas.item_key
  	FROM   wf_item_activity_statuses wfas
               , wf_process_activities wpa
               , oe_order_lines_all ol
  	WHERE  wfas.item_type='OEOL'
               AND wfas.process_activity = wpa.instance_id
               AND wpa.activity_item_type='OEOL'
               AND  wpa.activity_name = 'OE_CREDIT_HOLD_NTF'
  		AND wfas.activity_status = 'NOTIFIED'
 	        AND wfas.item_key  = ol.line_id
                AND ol.header_id = l_header_id;

Line: 699

   	 SELECT ORDER_NUMBER
   	 INTO	l_order_number
   	 FROM	OE_ORDER_HEADERS
   	 WHERE	header_id = l_header_id;

Line: 807

       SELECT header_id
       FROM oe_order_lines
       WHERE line_id = to_number(itemkey);

Line: 856

    SELECT ohr.HOLD_RELEASE_ID
    FROM OE_ORDER_HOLDS H, OE_HOLD_SOURCES S
    ,oe_hold_releases ohr
    WHERE H.HEADER_ID = header_id
    AND H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
    AND H.HOLD_RELEASE_ID IS NOT NULL
    AND S.HOLD_ID = 1
    AND S.HOLD_ENTITY_CODE = 'O'
    AND S.HOLD_ENTITY_ID = header_id
    AND S.RELEASED_FLAG ='Y'
    AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
    ORDER BY ohr.creation_date DESC;

Line: 871

      /*     SELECT NVL(MAX(H.HOLD_RELEASE_ID),0)
      FROM OE_ORDER_HOLDS H, OE_HOLD_SOURCES S
      WHERE H.HEADER_ID = header_id
      AND H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
      AND H.HOLD_RELEASE_ID IS NOT NULL
      AND S.HOLD_ID = 1
      AND S.HOLD_ENTITY_CODE = 'O'
      AND S.HOLD_ENTITY_ID = header_id
      AND S.RELEASED_FLAG ='Y';*/

Line: 902

           SELECT count(*)
           INTO l_manual_release
           FROM OE_HOLD_RELEASES
           WHERE HOLD_RELEASE_ID = l_hold_release_id
           AND RELEASE_REASON_CODE <> 'PASS_CREDIT'
           AND CREATED_BY <> 1;

Line: 939

       SELECT count(*)
       FROM wf_item_activity_statuses wfas, wf_process_activities wpa
       WHERE  wfas.item_type = 'OEOL'
          AND wfas.process_activity = wpa.instance_id
          AND wpa.activity_item_type = 'OEOL'
          AND wfas.item_key IN ( SELECT to_char(line_id)
      			FROM  oe_order_lines_all
      			WHERE header_id = l_header_id)
          AND wpa.activity_name = 'PICK_RELEASE';

Line: 950

	SELECT count(*)
	FROM   wf_activity_transitions atr
	WHERE  atr.from_process_activity = l_fromact_id
	AND    result_code = 'APPROVED'
	AND    to_process_activity IN
                (SELECT pa.instance_id
 		 FROM wf_process_activities pa
 		 WHERE pa.activity_name = 'PICK_RELEASE'
                   AND pa.activity_item_type = itemtype);