APPS.ZPB_DC_WF SQL Statements

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

Line: 79

	   SELECT value
	   INTO l_template_name
	   FROM zpb_task_parameters
	   WHERE task_id = l_task_id
	   AND name = 'TEMPLATE_NAME';

Line: 91

	   SELECT value
	   INTO l_wait_for_review
	   FROM zpb_task_parameters
	   WHERE task_id = l_task_id
	   AND name = 'TEMPLATE_WAIT_FOR_REVIEW';

Line: 152

	  SELECT fnd.user_name
	  INTO l_from_name
	  FROM	fnd_user fnd,zpb_dc_objects obj
	  WHERE fnd.user_id = obj.object_user_id
	  AND obj.generate_template_task_id = l_task_id
	  AND obj.object_type = 'M';

Line: 165

	  SELECT value
	  INTO l_notify
  	  FROM zpb_task_parameters
      WHERE task_id = l_task_id
	  AND name = 'NOTIFICATION_RECIPIENT_TYPE';

Line: 235

	  SELECT template_id
	  INTO l_template_id
	  FROM zpb_dc_objects
	  WHERE ac_instance_id = l_instance_id
	  AND object_type = 'M'
	  AND generate_template_task_id = l_task_id;

Line: 248

	  SELECT review_complete_flag
	  INTO l_review_complete_flag
	  FROM zpb_dc_objects
	  WHERE template_id = l_template_id
	  AND object_type = 'M';

Line: 333

	   SELECT value
	   INTO l_ac_template_id
       FROM zpb_task_parameters
       WHERE task_id = l_task_id
       AND name = 'DISTRIBUTION_TEMPLATE_ID';

Line: 339

	   SELECT template_id, template_name
	   INTO l_template_id, l_template_name
	   FROM zpb_dc_objects
	   WHERE ac_instance_id = l_instance_id
	   AND ac_template_id = l_ac_template_id
	   AND object_type = 'M';

Line: 373

	   SELECT nvl(fnd.description,fnd.user_name)
	   INTO l_object_user_name
	   FROM	fnd_user fnd, zpb_dc_objects obj
	   WHERE fnd.user_id = obj.object_user_id
	   AND obj.template_id = l_template_id
	   AND obj.object_type = 'M';

Line: 387

	   SELECT value
	   INTO l_distribute_message
	   FROM zpb_task_parameters
	   WHERE task_id = l_task_id
	   AND name = 'DISTRIBUTION_MESSAGE';

Line: 510

	     SELECT value
	     INTO l_ac_template_id
         FROM zpb_task_parameters
         WHERE task_id = l_task_id
         AND name = 'DISTRIBUTION_TEMPLATE_ID';

Line: 516

	     SELECT object_user_id
	     INTO l_object_user_id
	     FROM zpb_dc_objects
	     WHERE ac_instance_id = l_instance_id
	     AND ac_template_id = l_ac_template_id
	     AND object_type = 'M';

Line: 523

		 SELECT template_id, template_name
		 INTO l_template_id, l_template_name
		 FROM zpb_dc_objects
		 WHERE object_id = l_object_id;

Line: 528

	     SELECT object_user_id
	     INTO l_object_user_id
	     FROM zpb_dc_objects
	     WHERE template_id = l_template_id
	     AND object_type = 'M';

Line: 552

	   SELECT user_name
	   INTO l_from_name
	   FROM	fnd_user
	   WHERE user_id = l_object_user_id;

Line: 671

	SELECT ZPB_DC_WF_PROCESSES_S.nextval
	INTO l_sequence
	FROM dual;

Line: 675

	SELECT template_name
	INTO l_template_name
	FROM zpb_dc_objects
	WHERE object_id = p_object_id;

Line: 753

    l_parameter_list.delete;

Line: 884

	   SELECT nvl(fnd.description,fnd.user_name)
	   INTO l_object_user_name
	   FROM	fnd_user fnd,zpb_dc_objects obj
	   WHERE fnd.user_id = obj.object_user_id
	   AND obj.object_id = l_object_id;

Line: 898

	   SELECT fnd.user_name
	   INTO l_from_name
	   FROM	fnd_user fnd,zpb_dc_objects obj
	   WHERE fnd.user_id = obj.object_user_id
	   AND obj.object_id = l_object_id;

Line: 997

	     SELECT obj.template_id
	     FROM zpb_task_parameters param,
		      zpb_dc_objects obj
	     WHERE param.task_id = l_task_id
	     AND param.name = 'SUBMISSION_TEMPLATE_ID'
		 AND to_number(param.value) = obj.ac_template_id
		 AND obj.status <> 'SUBMITTED_TO_SHARED'
		 AND obj.object_type = 'M' -- consistently choose M record
      )
	  LOOP
	    l_count := l_count + 1;

Line: 1031

	  SELECT min(value)
	  INTO l_ac_template_id
      FROM zpb_task_parameters
      WHERE task_id = l_task_id
      AND name = 'SUBMISSION_TEMPLATE_ID';

Line: 1037

	  SELECT object_user_id
	  INTO l_object_user_id
	  FROM zpb_dc_objects
	  WHERE ac_instance_id = l_instance_id
	  AND ac_template_id = l_ac_template_id
	  AND object_type = 'M';

Line: 1044

	  SELECT user_name
	  INTO l_from_name
	  FROM	fnd_user
	  WHERE user_id = l_object_user_id;

Line: 1126

	  SELECT count(*)
	  INTO l_ws_count
	  FROM zpb_dc_objects
	  WHERE template_id = l_template_id
	  AND object_type = 'W';

Line: 1135

	      SELECT distribution_method,
                 approval_required_flag,
		     multiple_submissions_flag
          FROM zpb_dc_objects
          WHERE template_id = l_template_id
	      AND object_type = 'M' )
	  LOOP
	    l_distribution_method := worksheet_rec.distribution_method;

Line: 1153

		  SELECT count(*)
		  INTO l_ws_status_count
		  FROM zpb_dc_objects, fnd_user
		  WHERE object_user_id = user_id
		  AND template_id = l_template_id
		  AND object_type = 'W'
		  AND status NOT IN ('SUBMITTED_TO_SHARED')
		  AND (end_date is null OR end_date > sysdate);

Line: 1164

	        resultout := 'COMPLETE:UPDATE_STATUS';

Line: 1279

	  UPDATE zpb_dc_objects
	  SET status = 'SUBMITTED_TO_SHARED',
	      freeze_flag = 'Y',
		  LAST_UPDATED_BY =  fnd_global.USER_ID,
		  LAST_UPDATE_DATE = SYSDATE,
		  LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
	  WHERE template_id = l_template_id
	  AND object_type in ('M','E','C');

Line: 1336

	  SELECT count(*)
	  INTO l_count
	  FROM zpb_task_parameters param,
		   zpb_dc_objects obj
	  WHERE param.task_id = l_task_id
	  AND param.name = 'SUBMISSION_TEMPLATE_ID'
      AND to_number(param.value) = obj.ac_template_id
	  AND obj.status <> 'SUBMITTED_TO_SHARED'
          AND obj.ac_instance_id = l_instance_id
	  AND obj.object_type = 'M'; -- consistently choose M record

Line: 1430

	SELECT template_id,
	       object_type,
		   template_name,
		   object_user_id,
		   distributor_user_id,
		   distribution_method,
		   multiple_submissions_flag,
		   approval_required_flag
	INTO l_template_id,
	     l_object_type,
		 l_template_name,
		 l_object_user_id,
		 l_distributor_id,
		 l_distribution_method,
		 l_multiple_submissions_flag,
		 l_approval_required_flag
	FROM zpb_dc_objects
	WHERE object_id = p_object_id;

Line: 1449

	SELECT object_user_id
    INTO l_bpo_id
    FROM zpb_dc_objects
    WHERE template_id = l_template_id
    AND object_type = 'M';

Line: 1468

	  UPDATE zpb_dc_objects
	  SET status = 'SUBMITTED',
	      submission_date = sysdate,
	      submitted_by = l_object_user_id,
          LAST_UPDATED_BY =  fnd_global.USER_ID,
		  LAST_UPDATE_DATE = SYSDATE,
 		  LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
	  WHERE template_id = l_template_id
	  AND object_type in ('M','E','C');

Line: 1481

		UPDATE zpb_dc_objects
	        SET status = 'SUBMITTED',
 		  submission_date = sysdate,
	        submitted_by = l_object_user_id,
                approver_user_id = l_approver_id,
		  create_approval_measures_flag = 'Y',
		  delete_approval_measures_flag = 'N',
		  LAST_UPDATED_BY	 = fnd_global.USER_ID,
		  LAST_UPDATE_DATE  = SYSDATE,
		  LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
		WHERE object_id = p_object_id;

Line: 1493

	      UPDATE zpb_dc_objects
		   SET status = 'SUBMITTED',
		   freeze_flag = 'Y',
		   submission_date = sysdate,
		   submitted_by = l_object_user_id,
		   approver_user_id = l_approver_id,
		   create_approval_measures_flag = 'Y',
		   delete_approval_measures_flag = 'N', --3834999--
		   LAST_UPDATED_BY	 = fnd_global.USER_ID,
		   LAST_UPDATE_DATE  = SYSDATE,
		   LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
		WHERE object_id = p_object_id;

Line: 1509

	SELECT ZPB_DC_WF_PROCESSES_S.nextval
	INTO l_sequence
	FROM dual;

Line: 1572

    l_parameter_list.delete;

Line: 1638

	  SELECT distribution_method, approval_required_flag
	  INTO l_distribution_method, l_approval_required
	  FROM zpb_dc_objects
	  WHERE object_id = l_object_id;

Line: 1722

 	  SELECT template_name
 	  INTO l_template_name
 	  FROM zpb_dc_objects
 	  WHERE template_id = l_template_id
 	  AND object_type = 'M';

Line: 1730

	  SELECT nvl(fnd.description,fnd.user_name), object_user_id
	  INTO l_object_user_name, l_object_user_id
	  FROM zpb_dc_objects, fnd_user fnd
	  WHERE object_id = l_object_id
	  AND object_user_id = fnd.user_id;

Line: 1737

	  SELECT fnd.user_name
	  INTO l_from_name
	  FROM fnd_user fnd, zpb_dc_objects
	  WHERE object_id = l_object_id
	  AND object_user_id = fnd.user_id;

Line: 1751

	      SELECT u.user_name as user_name
	      FROM zpb_dc_objects obj, fnd_user u
	      WHERE obj.template_id = l_template_id
		  AND obj.object_type = 'W'
		  AND obj.status NOT IN ('SUBMITTED','FROZEN','APPROVED','SUBMITTED_TO_SHARED')
		  AND obj.object_user_id = u.user_id
		  AND (u.end_date is null OR u.end_date > sysdate))
	  LOOP
        l_frzn_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_frzn_rolename, frzn_rec.user_name);

Line: 1764

	      SELECT u.user_name as user_name
	      FROM zpb_dc_objects obj, fnd_user u
	      WHERE obj.template_id = l_template_id
		  AND obj.object_type in ('W','C')
		  AND obj.object_user_id = u.user_id)
	  LOOP
        l_all_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_all_rolename, all_rec.user_name);

Line: 1774

	  UPDATE zpb_dc_objects
	  SET status = 'SUBMITTED_TO_SHARED',
	      submission_date = sysdate,
	      submitted_by = l_object_user_id
	  WHERE template_id = l_template_id
	  AND object_type in ('M','E','C');

Line: 1783

	  UPDATE zpb_dc_objects
	  SET status = 'FROZEN'
	  WHERE template_id = l_template_id
	  AND OBJECT_TYPE = 'W'
	  AND status not in ('SUBMITTED','FROZEN','APPROVED','SUBMITTED_TO_SHARED');

Line: 1790

	  UPDATE zpb_dc_objects
	  SET freeze_flag = 'Y',
 		  LAST_UPDATED_BY =  fnd_global.USER_ID,
		  LAST_UPDATE_DATE = SYSDATE,
 		  LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
	  WHERE template_id = l_template_id;

Line: 1883

    SELECT object_user_id
    FROM
    (SELECT distributor_user_id, object_user_id
    	FROM zpb_dc_objects
        WHERE template_id = l_template_id
        AND object_type = 'W'
    )
    START with distributor_user_id = l_object_user_id
    CONNECT by prior object_user_id = distributor_user_id;

Line: 1914

	  SELECT distribution_method,
			 distributor_user_id,
			 object_user_id
      INTO l_distribution_method,
		   l_distributor_id,
		   l_object_user_id
	  FROM zpb_dc_objects
      WHERE object_id = l_object_id;

Line: 1931

            UPDATE zpb_dc_objects
	        SET status = 'FROZEN',
                freeze_flag = 'Y',
                LAST_UPDATED_BY	 = fnd_global.USER_ID,
                LAST_UPDATE_DATE  = SYSDATE,
                LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
	        WHERE template_id = l_template_id
	        AND object_user_id = l_freeze_user_id;

Line: 1986

	  SELECT approver_type
	  INTO l_approver_type
	  FROM zpb_dc_objects
	  WHERE object_id = l_object_id;

Line: 2054

	SELECT fnd.user_name
	FROM zpb_dc_objects obj, fnd_user fnd
	WHERE obj.object_id = l_object_id
	AND obj.approver_user_id = fnd.user_id;

Line: 2060

	SELECT fnd.user_name
	FROM zpb_dc_objects obj, fnd_user fnd
	WHERE obj.object_id = l_object_id
	AND obj.object_user_id = fnd.user_id;

Line: 2066

	SELECT nvl(fnd.description, fnd.user_name)
	FROM zpb_dc_objects obj, fnd_user fnd
	WHERE obj.object_id = l_object_id
	AND obj.approver_user_id = fnd.user_id;

Line: 2072

	SELECT nvl(fnd.description, fnd.user_name)
	FROM zpb_dc_objects obj, fnd_user fnd
	WHERE obj.object_id = l_object_id
	AND obj.object_user_id = fnd.user_id;

Line: 2095

	  SELECT template_name
	  INTO l_template_name
	  FROM zpb_dc_objects
	  WHERE object_id = l_object_id;

Line: 2119

	    SELECT fnd.user_name
	    INTO l_approver
	    FROM  zpb_dc_objects obj, fnd_user fnd
	    WHERE obj.object_id = l_object_id
	    AND obj.distributor_user_id = fnd.user_id;

Line: 2126

	      SELECT fnd.user_name
		  INTO l_approver
		  FROM zpb_dc_objects obj, fnd_user fnd
		  WHERE obj.template_id = l_template_id
		  AND obj.object_type = 'M'
		  AND obj.object_user_id = fnd.user_id;

Line: 2135

      l_appr_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_appr_rolename, l_approver);

Line: 2146

      l_subtr_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_subtr_rolename, l_submitter);

Line: 2160

	    SELECT nvl(fnd.description, fnd.user_name)
	    INTO l_approver_for_msg
	    FROM  zpb_dc_objects obj, fnd_user fnd
	    WHERE obj.object_id = l_object_id
	    AND obj.distributor_user_id = fnd.user_id;

Line: 2167

	      SELECT nvl(fnd.description, fnd.user_name)
		  INTO l_approver_for_msg
		  FROM zpb_dc_objects obj, fnd_user fnd
		  WHERE obj.template_id = l_template_id
		  AND obj.object_type = 'M'
		  AND obj.object_user_id = fnd.user_id;

Line: 2194

	  SELECT fnd.user_name
	  INTO l_from_name
	  FROM fnd_user fnd, zpb_dc_objects obj
	  WHERE obj.object_id = l_object_id
	  AND obj.object_user_id = fnd.user_id;

Line: 2212

	      SELECT u.user_name as user_name
	      FROM zpb_dc_objects obj, fnd_user u
	      WHERE obj.template_id = l_template_id
		  AND obj.object_type = 'W'
		  AND obj.status = 'FROZEN'
		  AND obj.object_user_id = u.user_id)
	  LOOP
            l_frozen_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_frozen_rolename, frozen_user_rec.user_name);

Line: 2256

  PROCEDURE update_aw(
    itemtype    IN varchar2,
	itemkey     IN varchar2,
	actid       IN number,
	funcmode    IN varchar2,
    resultout   OUT nocopy varchar2
	)
  IS
	l_req_id                NUMBER;

Line: 2309

	  SELECT object_user_id, template_name
	  INTO l_object_user_id, l_template_name
	  FROM zpb_dc_objects
	  WHERE object_id = l_object_id;

Line: 2367

      WF_CORE.CONTEXT('zpb_dc_wf.update_aw: no data found', itemtype, itemkey, to_char(actid), funcmode);

Line: 2371

      WF_CORE.CONTEXT('zpb_dc_wf.update_aw', itemtype, itemkey, to_char(actid), funcmode);

Line: 2373

  END update_aw;

Line: 2375

  PROCEDURE check_update_aw_type (
    itemtype    IN varchar2,
	itemkey     IN varchar2,
	actid       IN number,
	funcmode    IN varchar2,
    resultout   OUT nocopy varchar2
	)
  IS
	l_object_type         VARCHAR2(10);

Line: 2412

      WF_CORE.CONTEXT('zpb_dc_wf.check_update_aw_type: no data found', itemtype, itemkey, to_char(actid), funcmode);

Line: 2416

      WF_CORE.CONTEXT('zpb_dc_wf.check_update_aw_type', itemtype, itemkey, to_char(actid), funcmode);

Line: 2418

  END check_update_aw_type;

Line: 2489

	SELECT nvl(description,user_name)
	INTO l_approver_for_msg
	FROM fnd_user
	WHERE user_id= p_approver_user_id;

Line: 2494

	SELECT user_name
	INTO l_from_name
	FROM fnd_user
	WHERE user_id= p_approver_user_id;

Line: 2499

	SELECT obj.template_name, fnd.user_name, obj.template_id
	INTO l_template_name, l_submitter, l_template_id
	FROM zpb_dc_objects obj, fnd_user fnd
	WHERE obj.object_id = p_object_id
	AND obj.object_user_id = fnd.user_id;

Line: 2505

	SELECT ZPB_DC_WF_PROCESSES_S.nextval
	INTO l_sequence
	FROM dual;

Line: 2536

    l_subtr_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_subtr_rolename, l_submitter);

Line: 2575

    l_parameter_list.delete;

Line: 2695

	SELECT ZPB_DC_WF_PROCESSES_S.nextval
	INTO l_item_key
	FROM dual;

Line: 2699

	SELECT nvl(description,user_name)
	INTO l_approver_for_msg
	FROM fnd_user
	WHERE user_id= p_approver_user_id;

Line: 2704

	SELECT obj.template_name, fnd.user_name, template_id
	INTO l_template_name, l_submitter, l_template_id
	FROM zpb_dc_objects obj, fnd_user fnd
	WHERE obj.object_id = p_object_id
	AND obj.object_user_id = fnd.user_id;

Line: 2711

	SELECT user_name
	INTO l_from_name
	FROM fnd_user
	WHERE user_id= p_approver_user_id;

Line: 2716

	SELECT ZPB_DC_WF_PROCESSES_S.nextval
	INTO l_sequence
	FROM dual;

Line: 2747

    l_subtr_rolename := zpb_wf_ntf.update_Role_with_Shadows(l_subtr_rolename, l_submitter);

Line: 2786

    l_parameter_list.delete;

Line: 2859

	  --If multiple submission are allowed  - only update status
	  --else update status and freeze flag
	  SELECT multiple_submissions_flag
	  INTO l_multiple_submissions_flag
	  FROM zpb_dc_objects
	  WHERE object_id = l_object_id;

Line: 2868

             UPDATE zpb_dc_objects
  	     SET status = 'SUBMITTED_TO_SHARED',
 	      freeze_flag = 'Y',
	      LAST_UPDATED_BY =  fnd_global.USER_ID,
	      LAST_UPDATE_DATE = SYSDATE,
	      LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
	     WHERE object_id = l_object_id;

Line: 2876

	     UPDATE zpb_dc_objects
	     SET status = 'SUBMITTED_TO_SHARED',
	      LAST_UPDATED_BY =  fnd_global.USER_ID,
	      LAST_UPDATE_DATE = SYSDATE,
	      LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
	     WHERE object_id = l_object_id;

Line: 2933

  	  SELECT task.item_key
  	  INTO l_item_key
	  FROM zpb_dc_objects obj,zpb_analysis_cycle_tasks task,zpb_task_parameters param
	  WHERE obj.object_id = l_object_id
	  AND task.analysis_cycle_id = obj.ac_instance_id
	  AND task.task_id = param.task_id
	  AND param.name = 'SUBMISSION_TEMPLATE_ID'
	  AND obj.ac_template_id = to_number(param.value)
	  AND task.wf_process_name = 'MANAGE_SUBMISSION';

Line: 2992

    	  SELECT template_id
    	  INTO l_template_id
    	  FROM zpb_dc_objects
    	  WHERE object_id = l_object_id;

Line: 2998

    	  SELECT count(*)
	  INTO l_ws_status_count
	  FROM zpb_dc_objects obj , fnd_user usr
	  WHERE obj.object_user_id = usr.user_id
        AND l_template_id = obj.template_id
	  AND obj.object_type = 'W'
	  AND obj.status NOT IN ('SUBMITTED_TO_SHARED')
	  AND (usr.end_date is null OR usr.end_date > sysdate);

Line: 3008

	  SELECT multiple_submissions_flag
	  INTO l_multiple_submissions_flag
	  FROM zpb_dc_objects
    	  WHERE object_id = l_object_id;