APPS.CN_OVER_ASSIGN_PVT SQL Statements

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

Line: 6

G_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 7

G_LAST_UPDATED_BY           NUMBER  := fnd_global.user_id;

Line: 10

G_LAST_UPDATE_LOGIN         NUMBER  := fnd_global.login_id;

Line: 33

	 SELECT Trunc(start_date) start_date,
	   Nvl(Trunc(end_date),p_end_date) end_date
	   FROM cn_srp_role_dtls_v
	   WHERE srp_role_id = p_srp_role_id;

Line: 41

	    SELECT Nvl(amount,0) amount,Nvl(planning_amt,0) planning_amt,Nvl(prorated_amount,0) prorated_amount
	      FROM cn_srp_quota_cates
	      WHERE srp_role_id = c_srp_role_id
	      AND quota_category_id = c_quota_category_id;

Line: 117

	  SELECT comp_group_id,
	    Greatest(Trunc(start_date_active), c_start_date) start_date,
	    Least(Nvl(Trunc(end_date_active),c_end_date), c_end_date) end_date
	  FROM cn_qm_group_hier
	    WHERE parent_comp_group_id = c_parent_comp_group_id
	    AND   Greatest(Trunc(start_date_active), c_start_date) <=
	    Least(Nvl(Trunc(end_date_active),c_end_date), c_end_date)
	    ;

Line: 129

	   SELECT
	     msg.qm_mgr_srp_group_id ,
	     msg.srp_id,
	     msg.resource_id ,
	     msg.comp_group_id ,
	     msg.srp_role_id,
	     Greatest(Trunc(msg.start_date_active), c_start_date) start_date,
	     Least(Nvl(Trunc(msg.end_date_active),c_end_date),
		   c_end_date) end_date,
	     msg.manager_flag,
	     msg.member_flag
	     FROM
	     cn_qm_mgr_srp_groups msg
	     WHERE
	     msg.comp_group_id = c_comp_group_id
	     AND Greatest(Trunc(msg.start_date_active), c_start_date) <=
	     Least(Nvl(Trunc(msg.end_date_active),c_end_date), c_end_date)
	     AND msg.srp_id <> c_srp_id
	     ORDER BY msg.manager_flag
	     ;

Line: 153

	  SELECT srp_role_id,
	    Greatest(c_start_date,Trunc(start_date)) start_date,
	    Least(Nvl(Trunc(end_date),c_end_date),c_end_date) end_date
	    FROM cn_srp_role_dtls_v
	    WHERE srp_id = c_srp_id
        AND role_model_id is null -- "CHANGED FOR MODELING IMPACT"
	    AND overlay_flag = 'N'
	    AND Greatest(Trunc(start_date),c_start_date) <=
	    Least(Nvl(Trunc(end_date),c_end_date),c_end_date);

Line: 182

		 SELECT 1 INTO l_check
		   FROM cn_srp_role_dtls
		   WHERE srp_role_id = eachdirect.srp_role_id
		   AND org_code = p_org_code
		   AND overlay_flag = 'N'
		   ;

Line: 236

     SELECT Greatest(l_street_exist,l_direct_exist)
       INTO x_child_street_exist
       FROM dual;

Line: 304

	SELECT srp_role_id,srp_id,role_id,
	  Trunc(start_date_active) start_date,
	  Trunc(end_date_active) end_date,comp_group_id ,
	  manager_flag,member_flag ,group_name
	  FROM cn_qm_mgr_srp_groups
	  WHERE qm_mgr_srp_group_id = p_qm_mgr_srp_group_id ;

Line: 317

	  SELECT msg.srp_role_id,
	    (Least(Nvl(Trunc(msg.end_date_active),c_end_date), c_end_date) -
	     Greatest(Trunc(msg.start_date_active), c_start_date)) prorate_rate
	    FROM cn_qm_mgr_srp_groups msg
	    WHERE msg.manager_flag = 'Y'
	    AND   msg.comp_group_id = c_comp_group_id
	    AND  Greatest(Trunc(msg.start_date_active), c_start_date) <=
	    Least(Nvl(Trunc(msg.end_date_active),c_end_date), c_end_date)
	    ;

Line: 329

	  SELECT sqc.quota_category_id,
	    SUM(Nvl(sqc.amount,0) * c_prorate_rate) amount,
	    SUM(Nvl(sqc.planning_amt,0) * c_prorate_rate) planning_amt,
   	    SUM(Nvl(sqc.prorated_amount,0) * c_prorate_rate) prorated_amount
	    FROM cn_srp_quota_cates sqc,cn_quota_categories qc
	    WHERE sqc.quota_category_id = qc.quota_category_id
	    AND qc.TYPE = 'VAR_QUOTA'
	    AND sqc.srp_role_id = c_srp_role_id
	    GROUP BY sqc.quota_category_id
	    ;

Line: 342

	  SELECT comp_group_id,
	    Greatest(Trunc(start_date_active), c_start_date) start_date,
	    Least(Nvl(Trunc(end_date_active),c_end_date), c_end_date) end_date
	  FROM cn_qm_group_hier
	    WHERE parent_comp_group_id = c_parent_comp_group_id
	    AND   Greatest(Trunc(start_date_active), c_start_date) <=
	    Least(Nvl(Trunc(end_date_active),c_end_date), c_end_date)
	    ;

Line: 354

	   SELECT
	     msg.qm_mgr_srp_group_id ,
	     msg.srp_id,
	     msg.resource_id ,
	     msg.comp_group_id ,
	     msg.srp_role_id,
	     Greatest(Trunc(msg.start_date_active), c_start_date) start_date,
	     Least(Nvl(Trunc(msg.end_date_active),c_end_date),
		   c_end_date) end_date,
	     msg.manager_flag,
	     msg.member_flag
	     FROM
	     cn_qm_mgr_srp_groups msg
	     WHERE
	     msg.comp_group_id = c_comp_group_id
	     AND Greatest(Trunc(msg.start_date_active), c_start_date) <=
	     Least(Nvl(Trunc(msg.end_date_active),c_end_date), c_end_date)
	     AND msg.srp_id <> c_srp_id
	     ORDER BY msg.manager_flag
	     ;

Line: 378

	  SELECT srp_role_id,
	    Greatest(c_start_date,Trunc(start_date)) start_date,
	    Least(Nvl(Trunc(end_date),c_end_date),c_end_date) end_date
	    FROM cn_srp_role_dtls_v
	    WHERE srp_id = c_srp_id
        AND role_model_id is null -- "CHANGED FOR MODELING IMPACT"
	    AND overlay_flag = 'N'
	    AND Greatest(Trunc(start_date),c_start_date) <=
	    Least(Nvl(Trunc(end_date),c_end_date),c_end_date);

Line: 441

	   SELECT
	     (eachmgr.prorate_rate+1)/
	     (Nvl(Trunc(srd.end_date),l_srp_info.end_date) + 1 -
	     Trunc(srd.start_date))  prorate_rate
	     INTO eachmgr.prorate_rate
	     FROM cn_srp_role_dtls_v srd
	     WHERE srd.srp_role_id = eachmgr.srp_role_id
	     ;

Line: 456

		SELECT 1 INTO l_check
		  FROM cn_srp_role_dtls
		  WHERE srp_role_id = eachmgr.srp_role_id
		  AND org_code = p_org_code
		  ;

Line: 522

		 SELECT 1 INTO l_check
		   FROM cn_srp_role_dtls
		   WHERE srp_role_id = eachdirect.srp_role_id
		   AND org_code = p_org_code
		   AND overlay_flag = 'N'
		   ;

Line: 570

		     SELECT 1 INTO l_check
		       FROM cn_srp_role_dtls
		       WHERE srp_role_id = eachdirect.srp_role_id
		       AND org_code = p_org_code
		       AND overlay_flag = 'N'
		       ;