APPS.PON_TEAM_SCORING_UTIL_PVT SQL Statements

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

Line: 51

 l_stage := '20: updates begin here';

Line: 58

  l_stage := '30: update auction header';

Line: 60

  UPDATE pon_auction_headers_all
  SET    scoring_lock_date = SYSDATE
	   ,scoring_lock_tp_contact_id = p_tpc_id
         ,last_update_date = SYSDATE
	   ,last_updated_by = fnd_global.user_id
  WHERE  auction_header_id = p_auction_header_id
  AND    scoring_lock_date IS NULL;

Line: 72

  l_stage := '35: update the pon bid attribute values table';

Line: 77

   SELECT
      ptmas.auction_header_id
      ,ptmas.bid_number
      ,paa.attribute_name
      ,paa.datatype
      ,null value
      ,SYSDATE creation_date
      ,fnd_global.user_id created_by
      ,SYSDATE last_update_date
      ,fnd_global.user_id updated_by
      ,AVG(ptmas.score) score
      ,ptmas.attribute_sequence_number
      ,paa.attr_level
      ,paa.attr_max_score
	  ,paa.weight
      ,paa.attr_group_seq_number
      ,paa.attr_disp_seq_number
      ,null old_value
   FROM pon_team_member_attr_scores ptmas
      ,pon_auction_attributes paa
      ,pon_team_member_bid_scores ptmbs
      ,pon_bid_headers pbh
      ,pon_auction_headers_all paha
      ,pon_auction_sections pas
   WHERE  ptmas.auction_header_id = p_auction_header_id
      AND ptmas.auction_header_id = paa.auction_header_id
      AND paa.line_number = -1
      AND paa.attribute_list_id = -1
      AND paa.scoring_method = 'MANUAL'
      AND NVL(paa.display_only_flag, 'N') = 'N'
      AND ptmas.attribute_sequence_number = paa.sequence_number
      AND ptmbs.bid_number = ptmas.bid_number
      AND ptmbs.user_id = ptmas.user_id
      AND ptmbs.score_status = 'SUBMIT'
      AND ptmas.score IS NOT NULL
      AND pbh.auction_header_id = ptmas.auction_header_id
      AND ptmas.bid_number = pbh.bid_number
      AND pbh.bid_status = 'ACTIVE'
      AND paha.auction_header_id = paa.auction_header_id
      AND pas.auction_header_id = paa.auction_header_id
      AND pas.attr_group_seq_number = paa.attr_group_seq_number
      AND pas.section_name = paa.section_name
      AND (NVL(paha.two_part_flag,'N') = 'N' OR
          (paha.two_part_flag = 'Y' AND paha.sealed_auction_status = 'LOCKED') OR
          (paha.two_part_flag = 'Y' AND paha.sealed_auction_status <> 'LOCKED' AND pas.two_part_section_type = 'COMMERCIAL'))
   GROUP BY ptmas.auction_header_id
      ,ptmas.bid_number
      ,ptmas.attribute_sequence_number
      ,paa.attribute_name
      ,paa.datatype
      ,SYSDATE
      ,fnd_global.user_id
      ,SYSDATE
      ,fnd_global.user_id
      ,paa.attr_level
      ,paa.attr_max_score
      ,paa.weight
      ,paa.attr_group_seq_number
      ,paa.attr_disp_seq_number
   )ptmavg
   ON
   (ptmavg.auction_header_id = pbav.auction_header_id
   AND ptmavg.bid_number = pbav.bid_number
   AND ptmavg.attribute_name = pbav.attribute_name
   AND pbav.auction_line_number = -1
   )
   WHEN MATCHED
   THEN
 UPDATE --update score and weighted score on required and optional attributes
   SET pbav.score = ptmavg.score
       ,pbav.weighted_score = ((ptmavg.score*ptmavg.weight)/NVL(ptmavg.attr_max_score, 0))
   WHEN NOT MATCHED
   THEN
 INSERT -- internal attributes
   (
      pbav.auction_header_id
      ,pbav.auction_line_number
      ,pbav.bid_number
      ,pbav.line_number
      ,pbav.attribute_name
      ,pbav.datatype
      ,pbav.value
      ,pbav.creation_date
      ,pbav.created_by
      ,pbav.last_update_date
      ,pbav.last_updated_by
      ,pbav.score
      ,pbav.sequence_number
      ,pbav.attr_level
      ,pbav.weighted_score
      ,pbav.attr_group_seq_number
      ,pbav.attr_disp_seq_number
      ,pbav.old_value
   )
   VALUES
   (
      ptmavg.auction_header_id
      ,-1
      ,ptmavg.bid_number
      ,-1
      ,ptmavg.attribute_name
      ,ptmavg.datatype
      ,null
      ,SYSDATE -- creation_date
      ,fnd_global.user_id -- created_by
      ,SYSDATE -- last_update_date
      ,fnd_global.user_id -- updated_by
      ,ptmavg.score -- Calculated member Average Score
      ,ptmavg.attribute_sequence_number
      ,ptmavg.attr_level
      ,((ptmavg.score*ptmavg.weight)/NVL(ptmavg.attr_max_score, 0)) -- calculated weighted score
      ,ptmavg.attr_group_seq_number
      ,ptmavg.attr_disp_seq_number
      ,null
   );

Line: 227

PROCEDURE delete_member_scores(
          p_api_version              IN  NUMBER
	    ,p_auction_header_id       IN
 			pon_auction_headers_all.auction_header_id%TYPE
          ,p_team_id             	 IN  pon_scoring_teams.team_id%TYPE
	    ,p_user_id                 IN  fnd_user.user_id%TYPE
     	    ,x_return_status           OUT NOCOPY VARCHAR2
          ,x_msg_data                OUT NOCOPY VARCHAR2
          ,x_msg_count               OUT NOCOPY NUMBER
          )IS

l_api_name    CONSTANT VARCHAR2(30) := 'DELTE_MEMBER_SCORES';

Line: 261

 l_stage := '20: deletes begin here';

Line: 271

	DELETE FROM pon_team_member_attr_scores
	WHERE       auction_header_id = p_auction_header_id
	AND         user_id = p_user_id
      AND         attribute_sequence_number IN
 		(SELECT 	paa.sequence_number
 		FROM 		pon_auction_attributes paa
 				,pon_auction_sections pas
 				,pon_scoring_team_sections psts
 			WHERE 	paa.auction_header_id = pas.auction_header_id
 			AND		paa.attr_group_seq_number = pas.attr_group_seq_number
 			AND		pas.auction_header_id = psts.auction_header_id
 			AND		pas.section_id = psts.section_id
 			AND 		psts.team_id = p_team_id);

Line: 289

	-- delete that bid and user from the pon_team_member_bid_scores
	DELETE FROM 	pon_team_member_bid_scores ptmbs
	WHERE			ptmbs.auction_header_id = p_auction_header_id
	AND			ptmbs.user_id = p_user_id
	AND	NOT EXISTS
		(SELECT 	'x'
	 	FROM 		pon_team_member_attr_scores ptmas
		WHERE		ptmas.auction_header_id = ptmbs.auction_header_id
		AND 		ptmas.user_id = ptmbs.user_id);

Line: 333

PROCEDURE delete_team_scores(
          p_api_version              IN  NUMBER
	    ,p_auction_header_id       IN
 			pon_auction_headers_all.auction_header_id%TYPE
	    ,p_team_id                 IN pon_scoring_teams.team_id%TYPE
     	    ,x_return_status           OUT NOCOPY VARCHAR2
          ,x_msg_data                OUT NOCOPY VARCHAR2
          ,x_msg_count               OUT NOCOPY NUMBER
          )IS

l_api_name    CONSTANT VARCHAR2(30) := 'DELETE_TEAM_SCORES';

Line: 366

 l_stage := '20: deletes begin here';

Line: 372

	-- delete child record
	DELETE FROM pon_team_member_attr_scores ptmas
	WHERE       ptmas.auction_header_id = p_auction_header_id
	AND         (user_id, attribute_sequence_number) IN
			(SELECT	pstm.user_id, paa.sequence_number
 			FROM 		pon_auction_attributes paa
 					,pon_auction_sections pas
		 			,pon_scoring_team_sections psts
            	            		,pon_scoring_team_members pstm
			WHERE 	paa.auction_header_id = pas.auction_header_id
		 	AND		paa.attr_group_seq_number = pas.attr_group_seq_number
			AND		paa.attribute_list_id = -1
			AND		paa.line_number = -1
		 	AND		pas.auction_header_id = psts.auction_header_id
		 	AND		pas.section_id = psts.section_id
		 	AND 		psts.team_id = p_team_id
                        AND             pstm.team_id = psts.team_id
			AND 		pstm.auction_header_id = psts.auction_header_id);

Line: 396

	-- delete that bid and user from the pon_team_member_bid_scores
	DELETE
	FROM 	pon_team_member_bid_scores ptmbs
	WHERE 	ptmbs.auction_header_id = p_auction_header_id
    	AND 	ptmbs.user_id IN
    		(
    		SELECT -- for all users of this team
        		user_id
    		FROM 	pon_scoring_team_members pstm
    		WHERE 	pstm.auction_header_id = ptmbs.auction_header_id
        	AND 	pstm.team_id = p_team_id
    		)
    	AND NOT EXISTS -- where there is no row for a bid in the child table
    		(
    		SELECT
        		'x'
    		FROM 	pon_team_member_attr_scores ptmas
    		WHERE 	ptmas.auction_header_id = ptmbs.auction_header_id
        	AND 	ptmas.bid_number = ptmbs.bid_number
        	AND 	ptmas.user_id = ptmbs.user_id
    		);

Line: 452

PROCEDURE delete_subjective_scores(
          p_api_version              IN  NUMBER
	    ,p_auction_header_id       IN
 			pon_auction_headers_all.auction_header_id%TYPE
     	    ,x_return_status           OUT NOCOPY VARCHAR2
          ,x_msg_data                OUT NOCOPY VARCHAR2
          ,x_msg_count               OUT NOCOPY NUMBER
          )IS

l_api_name    CONSTANT VARCHAR2(30) := 'DELETE_SUBJECTIVE_SCORES';

Line: 484

 l_stage := '20: deletes begin here';

Line: 491

	-- update them to zero for this auction for all active bids
	-- so that teams can start scoring
	UPDATE 	pon_bid_attribute_values pbav
	SET	score = 0,
		weighted_score = 0
	WHERE   pbav.auction_header_id = p_auction_header_id
	AND	pbav.auction_line_number = -1
	AND     attribute_name IN
			(SELECT -- only header attributes that are scored manually
				paa.attribute_name
			FROM	pon_auction_attributes paa
			WHERE	paa.auction_header_id = pbav.auction_header_id
			AND	paa.line_number = -1
			AND	paa.attribute_list_id = -1
			AND 	paa.scoring_method = 'MANUAL')
	AND 	pbav.bid_number IN
			(SELECT --only active bids for this auction
				pbh.bid_number
			FROM	pon_bid_headers pbh
			WHERE	pbh.auction_header_id = pbav.auction_header_id
			AND	pbh.bid_status = 'ACTIVE');

Line: 545

PROCEDURE delete_section_assignment(
          p_api_version              IN  NUMBER
	      ,p_auction_header_id       IN  pon_scoring_team_sections.auction_header_id%TYPE
          ,p_section_id              IN  pon_scoring_team_sections.section_id%TYPE
     	  ,x_return_status           OUT NOCOPY VARCHAR2
          ,x_msg_data                OUT NOCOPY VARCHAR2
          ,x_msg_count               OUT NOCOPY NUMBER
          )IS

l_api_name    CONSTANT VARCHAR2(30) := 'DELETE_SECTION_ASSIGNMENT';

Line: 577

 l_stage := '20: delete begins here';

Line: 584

 DELETE FROM pon_scoring_team_sections
 WHERE 		 auction_header_id = p_auction_header_id
 AND		 section_id = p_section_id;

Line: 655

 l_stage := '20: updates begin here';

Line: 662

  l_stage := '30: update auction header';

Line: 664

  UPDATE pon_auction_headers_all
  SET     scoring_lock_date = null
         ,scoring_lock_tp_contact_id = p_tpc_id
         ,last_update_date = SYSDATE
         ,last_updated_by = fnd_global.user_id
  WHERE  auction_header_id = p_auction_header_id
  AND    scoring_lock_date IS NOT NULL;

Line: 676

  l_stage := '35: update the pon bid attribute values table';

Line: 681

  UPDATE pon_bid_attribute_values
  SET      score           = NULL
         ,internal_note    = NULL
	 ,weighted_score   = NULL
	 ,last_update_date = SYSDATE
	 ,last_updated_by  = fnd_global.user_id
  WHERE  auction_header_id = p_auction_header_id
  AND    line_number       = -1
  AND    sequence_number IN
	 (SELECT paa.sequence_number
          FROM   pon_auction_attributes paa, pon_auction_headers_all paha, pon_auction_sections pas
          WHERE  paa.auction_header_id =  p_auction_header_id
          AND    paa.attribute_list_id = -1
          AND    paa.line_number       = -1
          AND    paa.scoring_method    = 'MANUAL'
          AND    NVL(paa.display_only_flag, 'N') = 'N'  -- display only attributes are not scored
          AND    paha.auction_header_id = paa.auction_header_id
          AND    pas.auction_header_id = paa.auction_header_id
          AND    pas.attr_group_seq_number = paa.attr_group_seq_number
          AND    pas.section_name = paa.section_name
          AND    (NVL(paha.two_part_flag,'N') = 'N' OR  -- Non 2 Stage negotiations
                  (paha.two_part_flag = 'Y' AND paha.sealed_auction_status = 'LOCKED') OR -- 2 Stage negotiations in technical phase
                  (paha.two_part_flag = 'Y' AND paha.sealed_auction_status <> 'LOCKED' AND pas.two_part_section_type = 'COMMERCIAL'))); --2 stage negotiations in commercial phase will clear only commercial scores

Line: 705

  l_stage := '40: update the pon bid headers table';

Line: 707

  UPDATE pon_bid_headers
  SET	 score_overriden_flag = NULL
         ,score_overriden_date = NULL
         ,score_override_tp_contact_id = NULL
	 ,last_update_date     = SYSDATE
	 ,last_updated_by      = fnd_global.user_id
  WHERE  auction_header_id = p_auction_header_id;