APPS.IBC_ASSOCIATIONS_GRP SQL Statements

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

Line: 39

        SELECT association_id
	FROM IBC_ASSOCIATIONS
	WHERE association_type_code = p_assoc_type_code
        AND associated_object_val1 = p_assoc_object1
        AND NVL(associated_object_val2, '0') = NVL(p_assoc_object2, '0')
        AND NVL(associated_object_val3, '0') = NVL(p_assoc_object3, '0')
        AND NVL(associated_object_val4, '0') = NVL(p_assoc_object4, '0')
        AND NVL(associated_object_val5, '0') = NVL(p_assoc_object5, '0')
	AND content_item_id = p_content_item_id
	AND NVL(citem_version_id, '0') = NVL(p_citem_version_id, '0');

Line: 122

       Ibc_Associations_Pkg.insert_row (
	     px_association_id          => l_assoc_id
	    ,p_content_item_id          => p_content_item_id
            ,p_citem_version_id         => p_citem_version_id
	    ,p_association_type_code 	=> p_assoc_type_code
            ,p_associated_object_val1 	=> p_assoc_object1
            ,p_associated_object_val2 	=> p_assoc_object2
            ,p_associated_object_val3 	=> p_assoc_object3
            ,p_associated_object_val4 	=> p_assoc_object4
            ,p_associated_object_val5 	=> p_assoc_object5
            ,p_object_version_number 	=> G_OBJ_VERSION_DEFAULT
            ,x_rowid  			=> l_row_id
       );

Line: 192

PROCEDURE Delete_Association (
	p_api_version			IN    	NUMBER,
        p_init_msg_list			IN    	VARCHAR2,
	p_commit			IN	VARCHAR2,
	p_assoc_type_code		IN	VARCHAR2,
	p_assoc_object1			IN	VARCHAR2,
	p_assoc_object2			IN	VARCHAR2,
	p_assoc_object3			IN	VARCHAR2,
	p_assoc_object4			IN	VARCHAR2,
	p_assoc_object5			IN	VARCHAR2,
	p_content_item_id		IN	NUMBER,
	x_return_status			OUT NOCOPY   	VARCHAR2,
        x_msg_count			OUT NOCOPY    	NUMBER,
        x_msg_data			OUT NOCOPY   	VARCHAR2
) AS
        --******** local variable for standards **********
        l_api_name              CONSTANT VARCHAR2(30) := 'Delete_Association';

Line: 215

        SELECT association_id
	FROM IBC_ASSOCIATIONS
	WHERE association_type_code = p_assoc_type_code
        AND associated_object_val1 = p_assoc_object1
        AND NVL(associated_object_val2, '0') = NVL(p_assoc_object2, '0')
        AND NVL(associated_object_val3, '0') = NVL(p_assoc_object3, '0')
        AND NVL(associated_object_val4, '0') = NVL(p_assoc_object4, '0')
        AND NVL(associated_object_val5, '0') = NVL(p_assoc_object5, '0')
	AND content_item_id = p_content_item_id;

Line: 263

       Ibc_Associations_Pkg.delete_row(
            p_association_id => l_assoc_id
       );

Line: 277

            ,p_description   => 'Deleted association of type '|| p_assoc_type_code || ' and content item id ' || p_content_item_id
       );

Line: 308

END Delete_Association;

Line: 319

PROCEDURE Update_Association (
	p_api_version			IN    	NUMBER,
        p_init_msg_list			IN    	VARCHAR2,
	p_commit			IN	VARCHAR2,
	p_assoc_type_code		IN	VARCHAR2,
	p_assoc_object1			IN	VARCHAR2,
	p_assoc_object2			IN	VARCHAR2,
	p_assoc_object3			IN	VARCHAR2,
	p_assoc_object4			IN	VARCHAR2,
	p_assoc_object5			IN	VARCHAR2,
	p_old_citem_id			IN	NUMBER,
	p_new_citem_id			IN	NUMBER,
	p_new_citem_ver_id		IN	NUMBER,
	x_return_status			OUT NOCOPY   	VARCHAR2,
        x_msg_count			OUT NOCOPY    	NUMBER,
        x_msg_data			OUT NOCOPY   	VARCHAR2
) AS
        --******** local variable for standards **********
        l_api_name              CONSTANT VARCHAR2(30) := 'Update_Association';

Line: 346

        SELECT association_id
	FROM IBC_ASSOCIATIONS
	WHERE association_type_code = p_assoc_type_code
        AND associated_object_val1 = p_assoc_object1
        AND NVL(associated_object_val2, '0') = NVL(p_assoc_object2, '0')
        AND NVL(associated_object_val3, '0') = NVL(p_assoc_object3, '0')
        AND NVL(associated_object_val4, '0') = NVL(p_assoc_object4, '0')
        AND NVL(associated_object_val5, '0') = NVL(p_assoc_object5, '0')
	AND content_item_id = l_content_item_id;

Line: 433

       Ibc_Associations_Pkg.update_row (
	p_association_id		=>	l_assoc_id
	,p_content_item_id		=>	p_new_citem_id
	,p_citem_version_id		=>	p_new_citem_ver_id
       );

Line: 441

            p_activity       => Ibc_Utilities_Pvt.G_ALA_UPDATE
            ,p_parent_value  => p_new_citem_id
            ,p_object_type   => Ibc_Utilities_Pvt.G_ALO_ASSOCIATION
            ,p_object_value1 => p_assoc_object1
            ,p_object_value2 => p_assoc_object2
            ,p_object_value3 => p_assoc_object3
            ,p_object_value4 => p_assoc_object4
            ,p_object_value5 => p_assoc_object5
            ,p_description   => 'Updated association of type '|| p_assoc_type_code ||
                                ' old citem id: ' || p_old_citem_id ||
				' new citem id: ' || p_new_citem_id ||
				' new version id: ' ||  p_new_citem_ver_id
       );

Line: 483

END Update_Association;

Line: 578

SELECT A.COLUMN_VALUE content_item_id
FROM TABLE(CAST(p_old_content_item_ids AS JTF_NUMBER_TABLE)) A
MINUS
SELECT content_item_id FROM IBC_CONTENT_ITEMS C;

Line: 584

SELECT A.COLUMN_VALUE content_item_id
FROM TABLE(CAST(p_new_content_item_ids AS JTF_NUMBER_TABLE)) A
MINUS
SELECT content_item_id FROM IBC_CONTENT_ITEMS C;

Line: 590

SELECT A.COLUMN_VALUE citem_version_id
FROM TABLE(CAST(p_old_citem_version_ids AS JTF_NUMBER_TABLE)) A
MINUS
SELECT citem_version_id FROM IBC_CITEM_VERSIONS_B C;

Line: 596

SELECT A.COLUMN_VALUE citem_version_id
FROM TABLE(CAST(p_new_citem_version_ids AS JTF_NUMBER_TABLE)) A
MINUS
SELECT citem_version_id FROM IBC_CITEM_VERSIONS_B C;

Line: 603

SELECT A.COLUMN_VALUE association_type_code
FROM TABLE(CAST(p_assoc_type_codes AS JTF_VARCHAR2_TABLE_100)) A
MINUS
SELECT association_type_code FROM IBC_ASSOCIATION_TYPES_B C;

Line: 757

	--  Update
	--
   	--DBMS_OUT NOCOPYPUT.put_line('Begin Successful....');

Line: 762

	    DELETE FROM IBC_ASSOCIATIONS
		WHERE ROWID IN (
		SELECT A.ROWID FROM IBC_ASSOCIATIONS A,
		(
		-- The below Select Statement Returns all the rows that will be updated
		-- in the following Update Statement Which moves an Association from old_content_item_id
		-- to the New Content Item id.
		-- When moved if the New Content Item Id already has this association we don't want to error
		-- OUT NOCOPY but merge the two row. Which means we will have to delete one row.
		-- All the rows that r going to be updated
		-- Make sure that the new row is not a Duplicate in the table
		SELECT
          	 association_type_code
            ,associated_object_val1
            ,associated_object_val2
            ,associated_object_val3
            ,associated_object_val4
			,associated_object_val5
		  FROM IBC_ASSOCIATIONS
		  WHERE CONTENT_ITEM_ID    	 = p_old_content_item_ids(i)
          AND  citem_version_id = NVL(p_old_citem_version_ids(i),citem_version_id)
		  AND association_type_code  = p_assoc_type_codes(i)
          AND (associated_object_val1 = p_assoc_objects1(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1)
          AND (associated_object_val2 = p_assoc_objects2(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects2(i),NULL,NVL(associated_object_val2,'1')) = '1')
          AND (associated_object_val3 = p_assoc_objects3(i)	OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects3(i),NULL,NVL(associated_object_val3,'1')) = '1')
          AND (associated_object_val4 = p_assoc_objects4(i)	OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects4(i),NULL,NVL(associated_object_val4,'1')) = '1')
          AND (associated_object_val5 = p_assoc_objects5(i)	OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects5(i),NULL,NVL(associated_object_val5,'1')) = '1')
		  ) B
		  WHERE a.association_type_code = b.association_type_code
		  AND a.associated_object_val1 = b.associated_object_val1
          AND NVL(a.associated_object_val2,'1') =  NVL(b.associated_object_val2,'1')
          AND NVL(a.associated_object_val3,'1') =  NVL(b.associated_object_val3,'1')
          AND NVL(a.associated_object_val4,'1') =  NVL(b.associated_object_val4,'1')
          AND NVL(a.associated_object_val5,'1') =  NVL(b.associated_object_val5,'1')
		  AND a.CONTENT_ITEM_ID = p_new_content_item_ids(i)
          AND citem_version_id = NVL(p_new_citem_version_ids(i),citem_version_id)
		  -- By Mistake if the user passes the same old and new Content Item Id and citem ver id
		  -- then Delete should not happen.
		  AND NOT ( (p_new_content_item_ids(i) = p_old_content_item_ids(i)
                     AND
                     p_new_citem_version_ids(i) IS NULL AND p_old_citem_version_ids(i) IS NULL
                     )
                     OR
                     (p_new_content_item_ids(i) = p_old_content_item_ids(i)
                      AND
                      p_new_citem_version_ids(i) = p_old_citem_version_ids(i)
                     )
                   )
         );

Line: 815

	    UPDATE IBC_ASSOCIATIONS SET
          	 CONTENT_ITEM_ID = p_new_content_item_ids(i)
            ,CITEM_VERSION_ID = p_new_citem_version_ids(i)
            ,OBJECT_VERSION_NUMBER 	 = 1
            ,LAST_UPDATE_DATE 	   	 = SYSDATE
            ,LAST_UPDATED_BY 	  	 = Fnd_Global.user_id
            ,LAST_UPDATE_LOGIN 	   	 = Fnd_Global.login_id
		  WHERE CONTENT_ITEM_ID    	 = p_old_content_item_ids(i)
          AND citem_version_id = NVL(p_old_citem_version_ids(i),citem_version_id)
		  AND association_type_code  = p_assoc_type_codes(i)
          AND (associated_object_val1 = p_assoc_objects1(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1)
          AND (associated_object_val2 = p_assoc_objects2(i) OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects2(i),NULL,NVL(associated_object_val2,'1')) = '1')
          AND (associated_object_val3 = p_assoc_objects3(i)	OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects3(i),NULL,NVL(associated_object_val3,'1')) = '1')
          AND (associated_object_val4 = p_assoc_objects4(i)	OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects4(i),NULL,NVL(associated_object_val4,'1')) = '1')
          AND (associated_object_val5 = p_assoc_objects5(i)	OR DECODE(p_assoc_objects1(i),NULL,1) = 1 OR DECODE(p_assoc_objects5(i),NULL,NVL(associated_object_val5,'1')) = '1');

Line: 831

   	--DBMS_OUT NOCOPYPUT.put_line('Update Successful....');

Line: 832

	--Will insert them.

	BEGIN

	FOR i IN p_old_content_item_ids.FIRST..p_old_content_item_ids.LAST
		LOOP
		  IF SQL%BULK_ROWCOUNT(i) = 0 AND p_old_content_item_ids(i) IS NULL THEN
		  	 BEGIN

		   	  Ibc_Associations_Pkg.insert_row (
	     			  px_association_id          => l_assoc_id
	    			  ,p_content_item_id         => p_new_content_item_ids(i)
                      ,p_citem_version_id        => p_new_citem_version_ids(i)
	    			  ,p_association_type_code 	 => p_assoc_type_codes(i)
                      ,p_associated_object_val1  => p_assoc_objects1(i)
                      ,p_associated_object_val2  => p_assoc_objects2(i)
                      ,p_associated_object_val3  => p_assoc_objects3(i)
                      ,p_associated_object_val4  => p_assoc_objects4(i)
                      ,p_associated_object_val5  => p_assoc_objects5(i)
            		  ,p_object_version_number 	 => G_OBJ_VERSION_DEFAULT
            		  ,x_rowid  				 => l_rowid
					);

Line: 855

						-- If the User tries to Insert a Duplicate
						-- this exception will be thrown 'cos there
						-- is a Unique Index.
						-- Ignore and proceed with the next Insert
 						NULL;

Line: 970

   SELECT ci.citem_ver_id
   FROM ibc_citems_v ci
   WHERE ci.CITEM_ID=l_citem_id;

Line: 1096

	 -- Insert into table
	 l_assoc_id := NULL;

Line: 1098

         Ibc_Associations_Pkg.insert_row (
	     px_association_id          => l_assoc_id
	    ,p_content_item_id          => l_content_item_id
        ,p_citem_version_id         => l_citem_version_id
	    ,p_association_type_code 	=> l_assoc_type_code
            ,p_associated_object_val1 	=> l_assoc_object1
            ,p_associated_object_val2 	=> l_assoc_object2
            ,p_associated_object_val3 	=> l_assoc_object3
            ,p_associated_object_val4 	=> l_assoc_object4
            ,p_associated_object_val5 	=> l_assoc_object5
            ,p_object_version_number 	=> G_OBJ_VERSION_DEFAULT
            ,x_rowid  			=> l_row_id
         );

Line: 1160

PROCEDURE Delete_Associations (
	p_api_version			IN    	NUMBER,
        p_init_msg_list			IN    	VARCHAR2,
	p_commit			IN	VARCHAR2,
	p_content_item_ids		IN	JTF_NUMBER_TABLE,
	p_assoc_type_codes		IN	JTF_VARCHAR2_TABLE_100,
	p_assoc_objects1		IN	JTF_VARCHAR2_TABLE_300,
	p_assoc_objects2		IN	JTF_VARCHAR2_TABLE_300,
	p_assoc_objects3		IN	JTF_VARCHAR2_TABLE_300,
	p_assoc_objects4		IN	JTF_VARCHAR2_TABLE_300,
	p_assoc_objects5		IN	JTF_VARCHAR2_TABLE_300,
	x_return_status			OUT NOCOPY   	VARCHAR2,
        x_msg_count			OUT NOCOPY    	NUMBER,
        x_msg_data			OUT NOCOPY   	VARCHAR2
) AS
BEGIN
  Delete_Associations (
	p_api_version			=> p_api_version,
    p_init_msg_list			=> p_init_msg_list,
	p_commit			    => p_commit,
	p_content_item_ids		=> p_content_item_ids,
    p_citem_version_ids     => NULL,
	p_assoc_type_codes		=> p_assoc_type_codes,
	p_assoc_objects1		=> p_assoc_objects1,
	p_assoc_objects2		=> p_assoc_objects2,
	p_assoc_objects3		=> p_assoc_objects3,
	p_assoc_objects4		=> p_assoc_objects4,
	p_assoc_objects5		=> p_assoc_objects5,
	x_return_status			=> x_return_status,
    x_msg_count		 	    => x_msg_count,
    x_msg_data			    => x_msg_data
  );

Line: 1195

END Delete_Associations;

Line: 1199

PROCEDURE Delete_Associations (
	p_api_version			IN    	NUMBER,
    p_init_msg_list			IN    	VARCHAR2,
	p_commit			    IN	VARCHAR2,
	p_content_item_ids		IN	JTF_NUMBER_TABLE,
    p_citem_version_ids     IN  JTF_NUMBER_TABLE,
	p_assoc_type_codes		IN	JTF_VARCHAR2_TABLE_100,
	p_assoc_objects1		IN	JTF_VARCHAR2_TABLE_300,
	p_assoc_objects2		IN	JTF_VARCHAR2_TABLE_300,
	p_assoc_objects3		IN	JTF_VARCHAR2_TABLE_300,
	p_assoc_objects4		IN	JTF_VARCHAR2_TABLE_300,
	p_assoc_objects5		IN	JTF_VARCHAR2_TABLE_300,
	x_return_status			OUT NOCOPY   	VARCHAR2,
    x_msg_count			    OUT NOCOPY    	NUMBER,
    x_msg_data			    OUT NOCOPY   	VARCHAR2
) AS
        --******** local variable for standards **********
        l_api_name              CONSTANT VARCHAR2(30)   := 'Delete_Associations';

Line: 1233

        SELECT association_id
	FROM IBC_ASSOCIATIONS
        WHERE content_item_id = l_content_item_id
        AND citem_version_id = NVL(l_citem_version_id,citem_version_id)
        AND association_type_code = l_assoc_type_code
        AND associated_object_val1 = l_assoc_object1
        AND NVL(associated_object_val2, '0') = NVL(l_assoc_object2, '0')
        AND NVL(associated_object_val3, '0') = NVL(l_assoc_object3, '0')
        AND NVL(associated_object_val4, '0') = NVL(l_assoc_object4, '0')
        AND NVL(associated_object_val5, '0') = NVL(l_assoc_object5, '0');

Line: 1247

      SAVEPOINT DELETE_ASSOCIATIONS_PT;

Line: 1320

	 -- Delete Entry
	 Ibc_Associations_Pkg.delete_row(
            p_association_id => l_temp_assoc_id
         );

Line: 1351

       ROLLBACK TO DELETE_ASSOCIATIONS_PT;

Line: 1356

       ROLLBACK TO DELETE_ASSOCIATIONS_PT;

Line: 1361

       ROLLBACK TO DELETE_ASSOCIATIONS_PT;

Line: 1369

END Delete_Associations;

Line: 1464

	SELECT a.ASSOCIATION_TYPE_CODE, a.ASSOCIATED_OBJECT_VAL1, a.ASSOCIATED_OBJECT_VAL2,
	       a.ASSOCIATED_OBJECT_VAL3, a.ASSOCIATED_OBJECT_VAL4, a.ASSOCIATED_OBJECT_VAL5,
	       t.CALL_BACK_PKG
	FROM IBC_ASSOCIATIONS a, IBC_ASSOCIATION_TYPES_B t
	WHERE a.CONTENT_ITEM_ID = p_content_item_id AND
          a.citem_version_id = NVL(p_citem_version_id,a.citem_version_id) AND
	      a.ASSOCIATION_TYPE_CODE = t.ASSOCIATION_TYPE_CODE;

Line: 1473

	SELECT CALL_BACK_PKG
	FROM IBC_ASSOCIATION_TYPES_B
	WHERE ASSOCIATION_TYPE_CODE = l_assoc_type_code;

Line: 1713

    SELECT call_back_pkg
      FROM ibc_association_types_b
     WHERE association_type_code = p_assoc_type_code;